<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gree.mall.miniapp.commonmapper.DailyReportMapper">


    <select id="issueDayList" resultType="com.gree.mall.miniapp.bean.settle.repair.DailyReportBean">
         select
            salary_no,
            month 'issueMonth',
            issue_time ,
            repair_total_amount ,
            residual_insurance_cost + emp_insurance_cost 'totalEmpCost',
            withhold_total_cost ,
            withhold_cost ,
            reduce_cost ,
            incr_decr_cost ,
            issue_cost
        from
	        settle_daily_issue_summary_record
        <where>

            <if test="workerNumber !='' and workerNumber !=null">
                and service_number = #{workerNumber}
            </if>

            <if test="date !='' and date !=null">
                and  issue_time_fmt = #{date}
            </if>

            <if test="status !=null and status !=''">
                and  status = #{status}
            </if>
            <if test="install != null">
                and install = #{install}
            </if>

        </where>


    </select>


    <select id="queryMyOrderList" resultType="com.gree.mall.miniapp.bean.settle.repair.DailyWorkerIssueSalaryBean">
        select
          c.*,b.bank_account ,b.bank_account_name,deposit_bank,a.issue_time ,a.issue_by,a.salary_no,a.status 'issueStatus'
        from settle_daily_issue_summary_record a
        join user aa on a.service_number = aa.worker_number AND aa.apply_type = 'WORKER'
        join settle_daily_bank_account b  on  aa.id_card = b.idcard
        join settle_daily_import_summary_item c  on a.id  = c.issue_salary_id

        <where>

            <if test="id != null and id != ''">
                 and  c.id = #{id}
            </if>

            <if test="workerNumber != null and workerNumber != ''">
                and  a.service_number = #{workerNumber}
            </if>
            <if test="salaryNo != null and salaryNo != ''">
                and  a.salary_no = #{salaryNo}
            </if>

            <if test="q != null and q != ''">
                and (c.user_name like concat('%',#{q},'%') or c.dispatch_order_no like concat('%',#{q},'%') )
            </if>
        </where>

    </select>

    <select id="queryMyOrderMonthList" resultType="com.gree.mall.miniapp.bean.settle.repair.DailyWorkerIssueSalaryBean">
        select
        c.*,b.bank_account ,b.bank_account_name,deposit_bank,a.issue_time ,a.issue_by,a.salary_no,a.status 'issueStatus'
        from settle_daily_issue_summary_record a
        join user aa on a.service_number = aa.worker_number AND aa.apply_type = 'WORKER'
        join settle_daily_bank_account b  on  aa.id_card = b.idcard
        join settle_daily_import_summary_item c  on a.id  = c.issue_salary_id

        <where>
            <if test="id != null and id != ''">
                and  c.id = #{id}
            </if>

            <if test="workerNumber != null and workerNumber != ''">
                and  a.service_number = #{workerNumber}
            </if>
            <if test="summaryBatchNo != null and summaryBatchNo != ''">
                and  a.summary_month_batch_no = #{summaryBatchNo}
            </if>

            <if test="q != null and q != ''">
                and (c.user_name like concat('%',#{q},'%') or c.dispatch_order_no like concat('%',#{q},'%') )
            </if>
        </where>

    </select>

    <select id="notIssueList" resultType="com.gree.mall.miniapp.bean.settle.repair.DailyWorkerIssueSalaryBean">
        select
            c.*,b.bank_account ,b.bank_account_name,deposit_bank,a.issue_time ,a.issue_by,a.salary_no,if(a.status = 2,2,1) 'issueStatus'
        from settle_daily_issue_summary_record a
        join user aa on a.service_number = aa.worker_number AND aa.apply_type = 'WORKER'
        join settle_daily_bank_account b  on  aa.id_card = b.idcard
        join settle_daily_import_summary_item c  on a.id  = c.issue_salary_id
       <where>
        <if test="install != null">
            and a.install = #{install}
        </if>
        <if test="status != null and status.size()> 0">
            and a.status in
            <foreach collection="status" item="statusItem" index="index"
                     open="(" close=")" separator=",">
                #{statusItem}
            </foreach>
        </if>

        <if test="id != null and id != ''">
            and  c.id = #{id}
        </if>
        <if test="workerNumber != null and workerNumber != ''">
            and  c.worker_number = #{workerNumber}
        </if>

        <if test="q != null and q != ''">
            and (c.user_name like concat('%',#{q},'%') or c.dispatch_order_no like concat('%',#{q},'%') )
        </if>
            and c.summary_status = 1
       </where>

        UNION
        select
            c.*,
            b.bank_account ,
            b.bank_account_name,
            deposit_bank,
            '' issue_time ,
            '' issue_by,
            '' salary_no,
            1 'issueStatus'
        from
            settle_daily_import_summary_item c
        join user aa on c.worker_number = aa.worker_number AND aa.apply_type = 'WORKER'
        join settle_daily_bank_account b  on  aa.id_card = b.idcard
        where
            c.summary_status != 1
        <if test="id != null and id != ''">
            and  c.id = #{id}
        </if>
        <if test="workerNumber != null and workerNumber != ''">
            and  c.worker_number = #{workerNumber}
        </if>

        <if test="q != null and q != ''">
            and (c.user_name like concat('%',#{q},'%') or c.dispatch_order_no like concat('%',#{q},'%') )
        </if>

    </select>


</mapper>