select tdwk.*, btr.biz_traderid as tdwk_biz_traderid, btr.title as recruit_title, btr.salary as btr_salary, btr.feetotal as btr_feetotal, btr.plat_fetchmoney as btr_plat_fetchmoney, btr.sharer_fetchmoney as btr_sharer_fetchmoney, btr.begin_datetime as btr_begindatetime, btr.finish_datetime as btr_finishdatetime, btr.linker_phone as btr_linker_phone, btr.begin_hour as btr_begin_hour, btr.finish_hour as btr_finish_hour, bt.company_name as bt_company_name, bt.linker_gender as bt_linker_gender, btbu.mobile as btbu_mobile, (case when ifnull(bt2.true_name,'')<>'' then bt2.true_name else bt2.nickname end) as bt2_true_name, wk.id as wkid, (case when wk.true_name<>'' then wk.true_name else wk.nickname end) as wk_name, bu.mobile as bu_mobile, wk.profile_photo as wkprofile_photo, wk.idcard as wk_idcard, (case when lwk.true_name<>'' then lwk.true_name else lwk.nickname end) as lwk_name, lbu.mobile as lbu_mobile, lwk.profile_photo as lwkprofile_photo, swk.id as swkid, (case when swk.true_name<>'' then swk.true_name else swk.nickname end) as swk_name, sbu.mobile as sbu_mobile, wkapa.id as wkapa_id,wkapa.deleted as wkapa_deleted, wkapa.result_level as wk_appraiseLevel, wkapa.result_desc as wk_appraiseRemark, tdapa.id as tdapa_id, tdapa.deleted as tdapa_deleted, tdapa.result_level as td_appraiseLevel, tdapa.result_desc as td_appraiseRemark, ( select concat(brd.id,'-',brd.begin_datetime,'-',brd.finish_datetime,'-',bt.id,'-',bu.id,'-',bu.mobile,'-',bt.true_name) from biz_recruit_delegate brd left join biz_trader2 bt on bt.id=brd.biz_trader2id left join base_user bu on bu.id=bt.base_userid where brd.biz_recruitid=tdwk.biz_recruitid and brd.finish_datetime>:verify_datetime limit 1 ) as delegate_info, tdwkat2.`biz_catalog` as tdwk_settlecatalog from biz_todowork tdwk left join biz_todoworkteam tdwkt on tdwkt.id=tdwk.workteamid left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid left join biz_trader bt on bt.id=btr.biz_traderid left join base_user btbu on btbu.id=bt.base_userid left join biz_trader2 bt2 on bt2.id=tdwk.settled_userid left join biz_worker lwk on lwk.id=tdwkt.leader_workerid left join base_user lbu on lbu.id=lwk.base_userid left join biz_worker wk on wk.id=tdwk.biz_workerid left join base_user bu on bu.id=wk.base_userid left join biz_worker swk on swk.id=tdwk.share_workerid left join base_user sbu on sbu.id=swk.base_userid left join biz_appraisal wkapa on wkapa.biz_catalog=1 and wkapa.biz_id=tdwk.id left join biz_appraisal tdapa on tdapa.biz_catalog=2 and tdapa.biz_id=tdwk.id left join biz_trader_account tdwkat1 on tdwk.`status`=5 and tdwkat1.`status`=1 and tdwkat1.`type`=0 and tdwkat1.biz_id=tdwk.id and (tdwkat1.`biz_catalog`=1 or tdwkat1.`biz_catalog`=4) left join biz_trader_account tdwkat2 on tdwk.`status`=5 and tdwkat2.`status`=1 and tdwkat2.`type`=1 and tdwkat2.biz_id=tdwkat1.id and (tdwkat2.`biz_catalog`=6 or tdwkat2.`biz_catalog`=7) where tdwk.id=:tdwkId #biz_trader_recruit 工作岗位表 #biz_todowork 招工表 #biz_todoworkteam 招工队伍表 #biz_trader 商户表 #base_user 基本用户表 #biz_trader2 商户联系人属性表 #biz_worker 工人表 #biz_appraisal 评价表 select btr.*, bt.company_name bt_companyname, bu.mobile as bt_mobile,bt.catalog as bt_catalog, bt.certif_confirmed as bt_certif_confirmed, bt.profile_photo as bt_profile_photo , bt.linker_gender as bt_linker_gender , bt.company_desc as bt_company_desc, bt.nickname as bt_nickname, bt.linker_name as bt_linker_name , bt.company_addr as bt_company_addr, (select concat(id,'-',workteam_mode,'-',workteamid,'-',teamstatus,'-',`status`) from biz_todowork tdwk where tdwk.biz_recruitid=btr.id and tdwk.biz_workerid=:biz_workerid order by id desc limit 1 ) as lasttodowork, (select ifnull(avg(result_level),0) from biz_appraisal bapr where bapr.biz_catalog=1 and bapr.toerid=btr.biz_traderid ) as trader_appraisal_avg, (select count(*) from biz_todowork btdw where btdw.biz_recruitid=btr.id and ( btdw.`status`>=2 ) ) as now_worker_total, (select count(*) from biz_todowork btdw where btdw.biz_recruitid=btr.id and ( btdw.`status`>=1 ) ) as now_worker_total000, (select count(*) from biz_todowork btdw where btdw.biz_recruitid=btr.id and ( btdw.`status`=-1 or btdw.`status`=-3 ) ) as num_abnormal_total, ( select concat(brd.id,'-',brd.begin_datetime,'-',brd.finish_datetime,'-',bt.id,'-',bu.id,'-',bu.mobile,'-',bt.true_name) from biz_recruit_delegate brd left join biz_trader2 bt on bt.id=brd.biz_trader2id left join base_user bu on bu.id=bt.base_userid where brd.biz_recruitid=:recruitid and brd.finish_datetime>:verify_datetime limit 1) as delegate_info from biz_trader_recruit btr left join biz_trader bt on bt.id=btr.biz_traderid left join base_user bu on bu.id=bt.base_userid where btr.id=:recruitid select btr.*, btr.id as btr_id, tdwk.*, (case when (tdwk.`status`=4 and 1662524303-ifnull(tdwk.worked_finishtime,0)>24*3600) then 1 else 0 end) as overtimePay , tdwkt.id as tdwkt_id,tdwkt.`status` as tdwkt_status , btr.`status` as recruit_status, btr.createtime as recruit_createtime, btr.updatetime as recruit_updatetime,btr.need_total as btr_need_total , btr.title as recruit_title, btr.begin_datetime as btr_begindatetime, btr.finish_datetime as btr_finishdatetime , btr.begin_hour as btr_begin_hour, btr.finish_hour as btr_finish_hour, btr.work_addr1 as btr_work_addr1, btr.feeper as btr_feeper, btr.feetotal as btr_feetotal , btr.settle_catalog as btr_settle_catalog, btr.age_mode as btr_age_mode, btr.gender_mode as btr_gender_mode, btr.meals_providing as btr_meals_providing , btr.salary as btr_salary, btr.feetotal as btr_feetotal, btr.settle_mode as btr_settle_mode , bt.company_name as bt_company_name, btbu.mobile as btbu_mobile , (case when ifnull(bt22.true_name,'')<>'' then bt22.true_name else bt22.nickname end) as bt22_true_name , wk.id as wkid, (case when wk.true_name<>'' then wk.true_name else wk.nickname end) as wk_name, bu.mobile as bu_mobile, wk.profile_photo as wkprofile_photo , wk.realname_handled as wk_realname_handled, wk.gender as wk_gender, wk.idcard as wk_idcard, wk.birthaddr as wk_birthaddr, wk.birthday as wk_birthday , lwk.id as lwkid, (case when lwk.true_name<>'' then lwk.true_name else lwk.nickname end) as lwk_name, lbu.mobile as lbu_mobile, lwk.profile_photo as lwkprofile_photo , concat(brd.id,'-',brd.begin_datetime,'-',brd.finish_datetime,'-',bt2.id,'-',bt2bu.id,'-',bt2bu.mobile,'-',bt2.true_name) as delegate_info , wkapa.id as wkapa_id, wkapa.deleted as wkapa_deleted, wkapa.result_level as wk_appraiseLevel, wkapa.result_desc as wk_appraiseRemark , tdapa.id as tdapa_id, tdapa.deleted as tdapa_deleted, tdapa.result_level as td_appraiseLevel, tdapa.result_desc as td_appraiseRemark , tdwkat2.`biz_catalog` as tdwk_settlecatalog , (select count(id) from biz_todowork where status=1 and biz_recruitid=btr.id) as tdwk_count_status1 , (select count(id) from biz_todowork where status=2 and biz_recruitid=btr.id) as tdwk_count_status2 , (select count(id) from biz_todowork where status=4 and biz_recruitid=btr.id) as tdwk_count_status4 , (select count(id) from biz_todowork where status=5 and biz_recruitid=btr.id) as tdwk_count_status5 from biz_trader_recruit btr left join biz_todowork tdwk on btr.id=tdwk.biz_recruitid left join biz_todoworkteam tdwkt on tdwkt.id=tdwk.workteamid left join biz_trader bt on bt.id=btr.biz_traderid left join base_user btbu on btbu.id=bt.base_userid left join biz_trader2 bt22 on bt22.id=tdwk.settled_userid left join biz_worker lwk on lwk.id=tdwkt.leader_workerid left join base_user lbu on lbu.id=lwk.base_userid left join biz_worker wk on wk.id=tdwk.biz_workerid left join base_user bu on bu.id=wk.base_userid left join biz_recruit_delegate brd on brd.biz_recruitid=tdwk.biz_recruitid and brd.finish_datetime>1662524303 left join biz_trader2 bt2 on bt2.id=brd.biz_trader2id left join base_user bt2bu on bt2bu.id=bt2.base_userid left join biz_appraisal wkapa on wkapa.biz_catalog=1 and wkapa.biz_id=tdwk.id left join biz_appraisal tdapa on tdapa.biz_catalog=2 and tdapa.biz_id=tdwk.id left join biz_trader_account tdwkat1 on tdwk.`status`=5 and tdwkat1.`status`=1 and tdwkat1.`type`=0 and tdwkat1.biz_id=tdwk.id and (tdwkat1.`biz_catalog`=1 or tdwkat1.`biz_catalog`=4) left join biz_trader_account tdwkat2 on tdwk.`status`=5 and tdwkat2.`status`=1 and tdwkat2.`type`=1 and tdwkat2.biz_id=tdwkat1.id and (tdwkat2.`biz_catalog`=6 or tdwkat2.`biz_catalog`=7) where 1=1 and 1=1 and tdwk.status != 0 and tdwk.biz_recruitid=735 and ((tdwk.workteam_mode<=0 and tdwk.`status`=1) or (tdwk.workteam_mode=1 and tdwkt.`status`=3)) and btr.biz_traderid=144 order by tdwk.updatetime desc,tdwk.id desc set @handle_id=0; select @handle_id:=id from biz_trader_account where biz_traderid=:biz_traderid and `type`=1 and `biz_catalog`=8 and `biz_id`=:bizid and `status`=1 limit 1; insert into biz_trader_account ( `biz_traderid`, `createtime`,`type`, `money`, `status`, `biz_catalog`,`biz_id`, remark ) select :biz_traderid , :nowtime , 1 , :feetotal, 1, 8 , :bizid, :accremark2 from dual where ifnull(@handle_id,0)=0; update biz_todowork set updatetime=:nowtime,`status`=2 ,remark=concat(:bizremark,ifnull(remark,'')) where id=:bizid; insert into biz_worker_account ( `biz_workerid`, `createtime`,`type`, `money`, `status`, `biz_catalog`,`biz_id`, remark ) select tdwk.biz_workerid , :nowtime , 0 , tdwk.enroll_premoney, 1, 7 , tdwk.id, :cancel_remark from biz_todowork tdwk left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid where tdwk.id<>:bizid and tdwk.biz_workerid=:biz_workerid and tdwk.`status`=1 and( (tdwk.todo_begindate>=:plandoDateStart and tdwk.todo_begindate<=:plandoDateFinish) or(tdwk.todo_begindate>=:workDateStart and tdwk.todo_begindate<=:workDateFinish) or( btr.settle_catalog<=0 and btr.finish_datetime>btr.begin_datetime and ( (btr.begin_datetime>=:workDateStart and btr.begin_datetime<=:workDateFinish) or(btr.begin_datetime<=:workDateStart and btr.finish_datetime>=:workDateFinish) or(btr.finish_datetime>=:workDateStart and btr.finish_datetime<=:workDateFinish) )) ); update biz_todowork tdwk left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid set tdwk.updatetime=:nowtime, tdwk.`status`=-4, tdwk.`teamstatus`=-4 , tdwk.remark=concat(:cancel_remark,ifnull(tdwk.remark,'')), tdwk.teamremark=concat(:cancel_remark,ifnull(tdwk.teamremark,'')) where tdwk.id<>:bizid and tdwk.biz_workerid=:biz_workerid and tdwk.`status`=1 and( (tdwk.todo_begindate>=:plandoDateStart and tdwk.todo_begindate<=:plandoDateFinish) or(tdwk.todo_begindate>=:workDateStart and tdwk.todo_begindate<=:workDateFinish) or( btr.settle_catalog<=0 and btr.finish_datetime>btr.begin_datetime and ( (btr.begin_datetime>=:workDateStart and btr.begin_datetime<=:workDateFinish) or(btr.begin_datetime<=:workDateStart and btr.finish_datetime>=:workDateFinish) or(btr.finish_datetime>=:workDateStart and btr.finish_datetime<=:workDateFinish) )) ) ; insert into biz_message_queue( createtime, biz_catalog, biz_id) select :nowtime, 0, :bizid from dual where 1=1; select btr.* ,bt.company_name,btbu.mobile as btbu_mobile ,count(bwa.id) as work_total,sum(bwa.money) as money_total from biz_worker_account bwa inner join biz_trader_account bta on bta.id=bwa.biz_id inner join biz_todowork tdwk on tdwk.id=bta.biz_id left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid left join biz_trader bt on bt.id=btr.biz_traderid left join base_user btbu on btbu.id=bt.base_userid where bwa.type=0 and bwa.`status`=1 and bwa.biz_catalog=1 and bwa.`deleted`=0 and 1=1 and bwa.biz_workerid=334 group by tdwk.biz_recruitid order by money_total desc limit 0,1000 select tdwk.* from biz_todowork tdwk left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid left join biz_todoworkteam tdwkt on tdwkt.id=tdwk.workteamid where tdwk.id<>:nowWorkId and tdwk.biz_workerid=:biz_workerid and ifnull(tdwk.`status`,0)>=2 and ifnull(tdwk.`status`,0)!=5 and( (tdwk.todo_begindate>=:plandoDateStart and tdwk.todo_begindate<=:plandoDateFinish) or(:nowSettleCatalog=0 and tdwk.todo_begindate>=:workDateStart and tdwk.todo_begindate<=:workDateFinish) or( btr.settle_catalog<=0 and btr.finish_datetime>btr.begin_datetime and ( (btr.begin_datetime>=:workDateStart and btr.begin_datetime<=:workDateFinish) or(btr.begin_datetime<=:workDateStart and btr.finish_datetime>=:workDateFinish) or(btr.finish_datetime>=:workDateStart and btr.finish_datetime<=:workDateFinish) )) ) select btr.* ,bt.company_name,btbu.mobile as btbu_mobile ,count(bwa.id) as work_total,sum(bwa.money) as money_total from biz_worker_account bwa inner join biz_todowork tdwk on tdwk.id=bwa.biz_id left join biz_trader_recruit btr on btr.id=tdwk.biz_recruitid left join biz_trader bt on bt.id=btr.biz_traderid left join base_user btbu on btbu.id=bt.base_userid where bwa.type=0 and bwa.`status`=1 and bwa.biz_catalog=1 and bwa.`deleted`=0 and 1=1 and bwa.biz_workerid=334 group by tdwk.biz_recruitid order by money_total desc limit 0,1000 select btr.* ,(select count(*) from biz_todowork btdw where btdw.biz_recruitid=:id and btdw.`status`=1) as main_worker_total1"; //总待录用数 ,(select count(*) from biz_todowork btdw where btdw.biz_recruitid=:id and ( btdw.`status`=-1 or btdw.`status`=-3 or btdw.`status`>=2 )) as main_worker_total2"; //有效人次数 $sqlTemp .= " ,(select count(*) from biz_todowork btdw where btdw.biz_recruitid=:id and btdw.`status`=1 "; //当日待审数 $sqlTemp .= " and btdw.todo_begindate>=:workDateStart and btdw.todo_begindate<=:workDateFinish ) as date_worker_total1"; $sqlTemp .= " ,(select count(*) from biz_todowork btdw where btdw.biz_recruitid=:id and ( btdw.`status`=-1 or btdw.`status`=-3 or btdw.`status`>=2 ) "; //当日有效人次数 $sqlTemp .= " and btdw.todo_begindate>=:workDateStart and btdw.todo_begindate<=:workDateFinish ) as date_worker_total2"; $sqlTemp .= " ,(select count(*) from biz_todowork btdw where btdw.biz_recruitid=:id and ( btdw.`status`=-1 or btdw.`status`=-3 or btdw.`status`>=2 ) "; $sqlTemp .= " and btdw.todo_begindate<:todayStart ) as history_worker_total"; //历史有效人次数 $sqlTemp .= " from biz_trader_recruit btr "; $sqlTemp .= " where btr.id=:id; #main_worker_total1 总待录用数 #main_worker_total2 有效人次数 #date_worker_total1 当日待审数 select btr.*,btr.id as btr_id, tdwk.*, (case when (tdwk.`status`=4 and 1665985227-ifnull(tdwk.worked_finishtime,0)>24*3600) then 1 else 0 end) as overtimePay , tdwkt.id as tdwkt_id, tdwkt.`status` as tdwkt_status , btr.`status` as recruit_status, btr.createtime as recruit_createtime, btr.updatetime as recruit_updatetime, btr.need_total as btr_need_total , btr.title as recruit_title, btr.begin_datetime as btr_begindatetime, btr.finish_datetime as btr_finishdatetime , btr.begin_hour as btr_begin_hour, btr.finish_hour as btr_finish_hour, btr.work_addr1 as btr_work_addr1, btr.feeper as btr_feeper, btr.feetotal as btr_feetotal , btr.settle_catalog as btr_settle_catalog, btr.age_mode as btr_age_mode, btr.gender_mode as btr_gender_mode, btr.meals_providing as btr_meals_providing , btr.salary as btr_salary, btr.feetotal as btr_feetotal, btr.settle_mode as btr_settle_mode , bt.company_name as bt_company_name, btbu.mobile as btbu_mobile , (case when ifnull(bt22.true_name,'')<>'' then bt22.true_name else bt22.nickname end) as bt22_true_name , wk.id as wkid, (case when wk.true_name<>'' then wk.true_name else wk.nickname end) as wk_name, bu.mobile as bu_mobile, wk.profile_photo as wkprofile_photo , wk.realname_handled as wk_realname_handled, wk.gender as wk_gender, wk.idcard as wk_idcard, wk.birthaddr as wk_birthaddr, wk.birthday as wk_birthday , lwk.id as lwkid, (case when lwk.true_name<>'' then lwk.true_name else lwk.nickname end) as lwk_name, lbu.mobile as lbu_mobile, lwk.profile_photo as lwkprofile_photo , concat(brd.id,'-',brd.begin_datetime,'-',brd.finish_datetime,'-',bt2.id,'-',bt2bu.id,'-',bt2bu.mobile,'-',bt2.true_name) as delegate_info , wkapa.id as wkapa_id, wkapa.deleted as wkapa_deleted, wkapa.result_level as wk_appraiseLevel, wkapa.result_desc as wk_appraiseRemark , tdapa.id as tdapa_id, tdapa.deleted as tdapa_deleted, tdapa.result_level as td_appraiseLevel, tdapa.result_desc as td_appraiseRemark , tdwkat2.`biz_catalog` as tdwk_settlecatalog , (select count(id) from biz_todowork where status=1 and biz_recruitid=btr.id) as tdwk_count_status1 , (select count(id) from biz_todowork where status=2 and biz_recruitid=btr.id) as tdwk_count_status2 , (select count(id) from biz_todowork where status=4 and biz_recruitid=btr.id) as tdwk_count_status4 , (select count(id) from biz_todowork where status=5 and biz_recruitid=btr.id) as tdwk_count_status5 from biz_trader_recruit btr left join biz_todowork tdwk on btr.id=tdwk.biz_recruitid left join biz_todoworkteam tdwkt on tdwkt.id=tdwk.workteamid left join biz_trader bt on bt.id=btr.biz_traderid left join base_user btbu on btbu.id=bt.base_userid left join biz_trader2 bt22 on bt22.id=tdwk.settled_userid left join biz_worker lwk on lwk.id=tdwkt.leader_workerid left join base_user lbu on lbu.id=lwk.base_userid left join biz_worker wk on wk.id=tdwk.biz_workerid left join base_user bu on bu.id=wk.base_userid left join biz_recruit_delegate brd on brd.biz_recruitid=tdwk.biz_recruitid and brd.finish_datetime>1665985227 left join biz_trader2 bt2 on bt2.id=brd.biz_trader2id left join base_user bt2bu on bt2bu.id=bt2.base_userid left join biz_appraisal wkapa on wkapa.biz_catalog=1 and wkapa.biz_id=tdwk.id left join biz_appraisal tdapa on tdapa.biz_catalog=2 and tdapa.biz_id=tdwk.id left join biz_trader_account tdwkat1 on tdwk.`status`=5 and tdwkat1.`status`=1 and tdwkat1.`type`=0 and tdwkat1.biz_id=tdwk.id and (tdwkat1.`biz_catalog`=1 or tdwkat1.`biz_catalog`=4) left join biz_trader_account tdwkat2 on tdwk.`status`=5 and tdwkat2.`status`=1 and tdwkat2.`type`=1 and tdwkat2.biz_id=tdwkat1.id and (tdwkat2.`biz_catalog`=6 or tdwkat2.`biz_catalog`=7) where 1=1 and 1=1 and tdwk.status != 0 and tdwk.`status`=90 order by tdwk.updatetime desc,tdwk.id desc "