kly_api/说明/快来优 数据解析.sql

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;