<?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.sckw.payment.dao.KwpLedgerLogisticsMapper">
    <resultMap id="BaseResultMap" type="com.sckw.payment.model.KwpLedgerLogistics">
        <!--@mbg.generated-->
        <!--@Table kwp_ledger_logistics-->
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="ent_id" jdbcType="BIGINT" property="entId"/>
        <result column="l_ledger_no" jdbcType="VARCHAR" property="lLedgerNo"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="tax_rate" jdbcType="INTEGER" property="taxRate"/>
        <result column="trading" jdbcType="INTEGER" property="trading"/>
        <result column="total_price" jdbcType="DECIMAL" property="totalPrice"/>
        <result column="ex_tax_price" jdbcType="DECIMAL" property="exTaxPrice"/>
        <result column="settle_price" jdbcType="DECIMAL" property="settlePrice"/>
        <result column="actual_price" jdbcType="DECIMAL" property="actualPrice"/>
        <result column="audit_user" jdbcType="VARCHAR" property="auditUser"/>
        <result column="audit_phone" jdbcType="VARCHAR" property="auditPhone"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="generate_time" jdbcType="TIMESTAMP" property="generateTime"/>
        <result column="receipt_time" jdbcType="TIMESTAMP" property="receiptTime"/>
        <result column="order_count" jdbcType="INTEGER" property="orderCount"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="create_by" jdbcType="BIGINT" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_by" jdbcType="BIGINT" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="del_flag" jdbcType="INTEGER" property="delFlag"/>
    </resultMap>
    <sql id="Base_Column_List">
        <!--@mbg.generated-->
        id,
        ent_id,
        l_ledger_no,
        `name`,
        start_time,
        end_time,
        tax_rate,
        trading,
        total_price,
        ex_tax_price,
        settle_price,
        actual_price,
        audit_user,
        audit_phone,
        url,
        generate_time,
        receipt_time,
        order_count,
        remark,
        `status`,
        create_by,
        create_time,
        update_by,
        update_time,
        del_flag
    </sql>

    <select id="shipperSelect" resultType="com.sckw.payment.model.dto.LedgerShipperDto">
        select kll.id,
               kll.l_ledger_no   lLedgerNo,
               kll.name,
               kll.start_time    startTime,
               kll.end_time      endTime,
               kll.tax_rate      taxRate,
               kll.trading,
               kll.total_price   totalPrice,
               kll.ex_tax_price  exTaxPrice,
               kll.settle_price  settlePrice,
               kll.actual_price  actualPrice,
               kll.audit_phone   auditPhone,
               kll.audit_user    auditUser,
               kll.url,
               kll.generate_time generateTime,
               kll.receipt_time  receiptTime,
               kll.remark,
               kll.status,
               kllu.contacts,
               kllu.phone,
               kllu.firm_name    firmName,
               kll.order_count   orderCount
        from kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        <where>
            kll.del_flag = 0
              and kllu.unit_type = 1
              and kllu.ent_id = #{logisticsReq.entId,jdbcType=BIGINT}
            <if test="logisticsReq.status != null">
                and kll.status = #{logisticsReq.status}
            </if>
            <if test="logisticsReq.trading != null">
                and kll.trading = #{logisticsReq.trading,jdbcType=INTEGER}
            </if>
            <if test="logisticsReq.startCreateTime != null and logisticsReq.startCreateTime != '' and logisticsReq.endCreateTime != null and logisticsReq.endCreateTime != ''">
                and kll.generate_time between #{logisticsReq.startCreateTime,jdbcType=TIMESTAMP}
                    and #{logisticsReq.endCreateTime,jdbcType=TIMESTAMP}
            </if>
            <if test="logisticsReq.keywords != null and logisticsReq.keywords != ''">
                and (
                            kll.l_ledger_no like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                        or kllu.firm_name like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                        or kllu.contacts like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                    )
            </if>
        </where>
        order by kll.generate_time desc
    </select>
    <select id="carrierSelect" resultType="com.sckw.payment.model.dto.LedgerCarrierDto">
        select kll.id,
               kll.l_ledger_no   lLedgerNo,
               kll.name,
               kll.start_time    startTime,
               kll.end_time      endTime,
               kll.tax_rate      taxRate,
               kll.trading,
               kll.total_price   totalPrice,
               kll.ex_tax_price  exTaxPrice,
               kll.settle_price  settlePrice,
               kll.actual_price  actualPrice,
               kll.audit_phone   auditPhone,
               kll.audit_user    auditUser,
               kll.url,
               kll.generate_time generateTime,
               kll.receipt_time  receiptTime,
               kll.remark,
               kll.status,
               kllu.contacts,
               kllu.phone,
               kllu.firm_name    firmName,
               kll.order_count   orderCount
        from kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        <where>
            kll.del_flag = 0
              and kllu.unit_type = 2
              and kllu.ent_id = #{logisticsReq.entId,jdbcType=BIGINT}
            <if test="logisticsReq.status != null">
                and kll.status = #{logisticsReq.status}
            </if>
            <if test="logisticsReq.trading != null">
                and kll.trading = #{logisticsReq.trading,jdbcType=INTEGER}
            </if>
            <if test="logisticsReq.startCreateTime != null and logisticsReq.endCreateTime != null">
                and kll.generate_time between #{logisticsReq.startCreateTime,jdbcType=TIMESTAMP}
                    and #{logisticsReq.endCreateTime,jdbcType=TIMESTAMP}
            </if>
            <if test="logisticsReq.keywords != null and logisticsReq.keywords != ''">
                and (
                            kll.l_ledger_no like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                        or kllu.firm_name like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                        or kllu.contacts like concat('%', #{logisticsReq.keywords,jdbcType=VARCHAR}, '%')
                    )
            </if>
        </where>
        order by kll.generate_time desc
    </select>

    <select id="countOrder" resultType="java.util.Map">
        SELECT count(1)                           "0",
               count(IF(kll.status = 1, 1, NULL)) "1",
               count(IF(kll.status = 2, 1, NULL)) "2",
               count(IF(kll.status = 3, 1, NULL)) "3",
               count(IF(kll.status = 4, 1, NULL)) "4",
               count(IF(kll.status = 5, 1, NULL)) "5",
               count(IF(kll.status = 6, 1, NULL)) "6"
        FROM kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        WHERE kll.del_flag = 0
          and kllu.unit_type = #{unitType,jdbcType=INTEGER}
          and kllu.ent_id = #{entId,jdbcType=BIGINT}
    </select>

    <select id="selectCarrierIds" resultType="com.sckw.payment.model.dto.LedgerCarrierDto">
        select kll.id,
               kll.l_ledger_no   lLedgerNo,
               kll.name,
               kll.start_time    startTime,
               kll.end_time      endTime,
               kll.tax_rate      taxRate,
               kll.trading,
               kll.total_price   totalPrice,
               kll.ex_tax_price  exTaxPrice,
               kll.settle_price  settlePrice,
               kll.actual_price  actualPrice,
               kll.audit_phone   auditPhone,
               kll.audit_user    auditUser,
               kll.url,
               kll.generate_time generateTime,
               kll.receipt_time  receiptTime,
               kll.remark,
               kll.status,
               kllu.contacts,
               kllu.phone,
               kllu.firm_name    firmName,
               kll.order_count   orderCount
        from kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        <where>
            kll.del_flag = 0
              and kllu.unit_type = 1
              and kll.id in
            <foreach collection="ids" item="id" close=")" open="(" separator=",">
                #{id,jdbcType=BIGINT}
            </foreach>
        </where>
        order by kll.generate_time desc
    </select>
    <select id="selectShipperIds" resultType="com.sckw.payment.model.dto.LedgerShipperDto">
        select kll.id,
               kll.l_ledger_no   lLedgerNo,
               kll.name,
               kll.start_time    startTime,
               kll.end_time      endTime,
               kll.tax_rate      taxRate,
               kll.trading,
               kll.total_price   totalPrice,
               kll.ex_tax_price  exTaxPrice,
               kll.settle_price  settlePrice,
               kll.actual_price  actualPrice,
               kll.audit_phone   auditPhone,
               kll.audit_user    auditUser,
               kll.url,
               kll.generate_time generateTime,
               kll.receipt_time  receiptTime,
               kll.remark,
               kll.status,
               kllu.contacts,
               kllu.phone,
               kllu.firm_name    firmName,
               kll.order_count   orderCount
        from kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        <where>
            kll.del_flag = 0
              and kllu.unit_type = 1
              and kll.id in
            <foreach collection="ids" item="id" close=")" open="(" separator=",">
                #{id,jdbcType=BIGINT}
            </foreach>
        </where>
        order by kll.generate_time desc
    </select>
    <select id="selectId" resultType="com.sckw.payment.model.dto.LedgerLogisticsDto">
        select kll.id,
               kll.l_ledger_no   lLedgerNo,
               kll.name,
               kll.start_time    startTime,
               kll.end_time      endTime,
               kll.tax_rate      taxRate,
               kll.trading,
               kll.total_price   totalPrice,
               kll.ex_tax_price  exTaxPrice,
               kll.settle_price  settlePrice,
               kll.actual_price  actualPrice,
               kll.audit_phone   auditPhone,
               kll.audit_user    auditUser,
               kll.url,
               kll.generate_time generateTime,
               kll.receipt_time  receiptTime,
               kll.remark,
               kll.status,
               kllu.contacts,
               kllu.phone,
               kllu.firm_name    firmName,
               kll.order_count   orderCount
        from kwp_ledger_logistics kll
                 inner join kwp_ledger_logistics_unit kllu on kll.id = kllu.l_ledger_id and kllu.del_flag = 0
        <where>
            kll.del_flag = 0
              and kllu.unit_type = 1
              and kll.id = #{id}
        </where>
    </select>
</mapper>