<?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.jianshui.income.mapper.InvoiceCheckRecordMapper">

    <resultMap type="com.jianshui.income.domain.entity.InvoiceCheckRecord" id="InvoiceCheckRecordResult">
        <result property="id"    column="id"    />
        <result property="companyId"    column="company_id"    />
        <result property="pch"    column="pch"    />
        <result property="cyjg"    column="cyjg"    />
        <result property="cyjgxx"    column="cyjgxx"    />
        <result property="fpzl"    column="fpzl"    />
        <result property="fpdm"    column="fpdm"    />
        <result property="fphm"    column="fphm"    />
        <result property="cycs"    column="cycs"    />
        <result property="xhfmc"    column="xhfmc"    />
        <result property="xhfsbh"    column="xhfsbh"    />
        <result property="xhfdzdh"    column="xhfdzdh"    />
        <result property="xhfyhzh"    column="xhfyhzh"    />
        <result property="gmfsbh"    column="gmfsbh"    />
        <result property="gmfmc"    column="gmfmc"    />
        <result property="gmfdzdh"    column="gmfdzdh"    />
        <result property="gmfyhzh"    column="gmfyhzh"    />
        <result property="kprq"    column="kprq"    />
        <result property="fpje"    column="fpje"    />
        <result property="fpse"    column="fpse"    />
        <result property="jshj"    column="jshj"    />
        <result property="bz"    column="bz"    />
        <result property="jqbh"    column="jqbh"    />
        <result property="kpr"    column="kpr"    />
        <result property="skr"    column="skr"    />
        <result property="fhr"    column="fhr"    />
        <result property="jym"    column="jym"    />
        <result property="zfbz"    column="zfbz"    />
        <result property="status"    column="status"    />
        <result property="createdAt"    column="created_at"    />
        <result property="dqskssq"    column="dqskssq"    />
        <result property="gxjzr"    column="gxjzr"    />
        <result property="gxrqq"    column="gxrqq"    />
        <result property="gxrqz"    column="gxrqz"    />
        <result property="fpzt"    column="fpzt"    />
        <result property="rzzt"    column="rzzt"    />
        <result property="rzrq"    column="rzrq"    />
        <result property="rzsq"    column="rzsq"    />
        <result property="rzlx"    column="rzlx"    />
        <result property="xxly"    column="xxly"    />
        <result property="yxse"    column="yxse"    />
        <result property="yqkgxbz"    column="yqkgxbz"    />
        <result property="glzt"    column="glzt"    />
        <result property="rzclzt"    column="rzclzt"    />
        <result property="sbyy"    column="sbyy"    />
        <result property="mxbh"    column="mxbh"    />
        <result property="hwmc"    column="hwmc"    />
        <result property="je"    column="je"    />
        <result property="spsl"    column="spsl"    />
        <result property="sl"    column="sl"    />
        <result property="se"    column="se"    />
        <result property="hsdj"    column="hsdj"    />
        <result property="hsje"    column="hsje"    />
        <result property="bhsdj"    column="bhsdj"    />
        <result property="ggxh"    column="ggxh"    />
        <result property="jldw"    column="jldw"    />
    </resultMap>

    <sql id="selectInvoiceCheckVo">
        select id, company_id, pch, cyjg, cyjgxx, fpzl, fpdm, fphm, cycs, xhfmc, xhfsbh, xhfdzdh, xhfyhzh, gmfsbh, gmfmc, gmfdzdh, gmfyhzh, kprq, fpje, fpse, jshj, bz, jqbh, kpr, skr, fhr, jym, zfbz, status, created_at, dqskssq, gxjzr, gxrqq, gxrqz, fpzt, rzzt, rzrq, rzsq, rzlx, xxly, yxse, yqkgxbz, glzt, rzclzt, sbyy from invoice_check
    </sql>

    <select id="selectInvoiceCheckList" parameterType="com.jianshui.income.domain.entity.InvoiceCheckRecord" resultMap="InvoiceCheckRecordResult">
        SELECT maxd.*,count(1) as cycs
        FROM(
        SELECT distinct a.id ,a.company_id,a.fpdm,a.fphm,a.cyjgxx,a.fpzl,a.fpzt,
        a.jshj,a.fpse,a.fpje,a.kprq,a.xhfmc,a.xhfsbh,a.gmfmc,a.created_at,a.gmfsbh
        FROM `invoice_check` a
        <where>
            <if test="companyId != null  and companyId != ''"> and company_id = #{companyId}</if>
            <if test="fpdm != null  and fpdm != ''"> and fpdm = #{fpdm}</if>
            <if test="fphm != null  and fphm != ''"> and fphm = #{fphm}</if>
            <if test="createdAt != null "> and DATEDIFF(created_at, #{createdAt} ) =0 </if>
            <if test="fpzl != null "> and fpzl = #{fpzl}</if>
            <!-- 数据范围过滤 -->

            <if test="params != null  and params.begintime != null   and params.begintime != '' "><!-- 开始时间检索 -->
                and date_format(created_at,'%y%m%d') &gt;= date_format(#{params.begintime},'%y%m%d')
            </if>
            <if test="params != null  and params.endtime != null  and params.endtime != ''"><!-- 结束时间检索 -->
                and date_format(created_at,'%y%m%d') &lt;= date_format(#{params.endtime},'%y%m%d')
            </if>
        </where>
        order by created_at desc) maxd
        GROUP BY company_id,fpdm,fphm
        order by created_at desc
    </select>
    <select id="selectExportInvoiceCheckList" parameterType="com.jianshui.income.domain.entity.InvoiceCheckRecord" resultMap="InvoiceCheckRecordResult">
        SELECT a.id, a.company_id, a.fpdm, a.fphm, a.cyjgxx, a.fpzl, a.fpzt, a.jshj, a.fpse, a.fpje, a.kprq, a.xhfmc, a.xhfsbh, a.gmfmc, a.created_at
        , a.gmfsbh, a.cycs, b.mxbh, b.hwmc, b.je, b.spsl, b.sl, b.se, b.hsdj, b.hsje, b.bhsdj, b.ggxh, b.jldw
        FROM (
        SELECT id, company_id, fpdm, fphm, cyjgxx, fpzl, fpzt, jshj, fpse, fpje, kprq, xhfmc, xhfsbh, gmfmc, created_at, gmfsbh, COUNT(1) AS cycs
        FROM invoice_check
        <where>
            <if test="companyId != null  and companyId != ''"> and company_id = #{companyId}</if>
            <if test="fpdm != null  and fpdm != ''"> and fpdm = #{fpdm}</if>
            <if test="fphm != null  and fphm != ''"> and fphm = #{fphm}</if>
            <if test="createdAt != null "> and DATEDIFF(created_at, #{createdAt} ) =0 </if>
            <if test="fpzl != null "> and fpzl = #{fpzl}</if>
            <!-- 数据范围过滤 -->

            <if test="params != null  and params.begintime != null   and params.begintime != '' "><!-- 开始时间检索 -->
                and date_format(created_at,'%y%m%d') &gt;= date_format(#{params.begintime},'%y%m%d')
            </if>
            <if test="params != null  and params.endtime != null  and params.endtime != ''"><!-- 结束时间检索 -->
                and date_format(created_at,'%y%m%d') &lt;= date_format(#{params.endtime},'%y%m%d')
            </if>
        </where>
        GROUP BY company_id,fpdm,fphm ) a
        LEFT JOIN invoice_check_detail b ON a.id = b.invoice_check_id
        ORDER BY a.created_at DESC
    </select>


    <select id="selectInvoiceCheckById" parameterType="String" resultMap="InvoiceCheckRecordResult">
        <include refid="selectInvoiceCheckVo"/>
        where id = #{id}
    </select>

    <insert id="insertInvoiceCheck" parameterType="com.jianshui.income.domain.entity.InvoiceCheckRecord" useGeneratedKeys="true" keyProperty="id">
        insert into invoice_check
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="companyId != null">company_id,</if>
            <if test="pch != null">pch,</if>
            <if test="cyjg != null">cyjg,</if>
            <if test="cyjgxx != null">cyjgxx,</if>
            <if test="fpzl != null and fpzl != ''">fpzl,</if>
            <if test="fpdm != null and fpdm != ''">fpdm,</if>
            <if test="fphm != null and fphm != ''">fphm,</if>
            <if test="cycs != null">cycs,</if>
            <if test="xhfmc != null">xhfmc,</if>
            <if test="xhfsbh != null">xhfsbh,</if>
            <if test="xhfdzdh != null">xhfdzdh,</if>
            <if test="xhfyhzh != null">xhfyhzh,</if>
            <if test="gmfsbh != null">gmfsbh,</if>
            <if test="gmfmc != null">gmfmc,</if>
            <if test="gmfdzdh != null">gmfdzdh,</if>
            <if test="gmfyhzh != null">gmfyhzh,</if>
            <if test="kprq != null">kprq,</if>
            <if test="fpje != null">fpje,</if>
            <if test="fpse != null">fpse,</if>
            <if test="jshj != null">jshj,</if>
            <if test="bz != null">bz,</if>
            <if test="jqbh != null">jqbh,</if>
            <if test="kpr != null">kpr,</if>
            <if test="skr != null">skr,</if>
            <if test="fhr != null">fhr,</if>
            <if test="jym != null">jym,</if>
            <if test="zfbz != null">zfbz,</if>
            <if test="status != null">status,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="dqskssq != null">dqskssq,</if>
            <if test="gxjzr != null">gxjzr,</if>
            <if test="gxrqq != null">gxrqq,</if>
            <if test="gxrqz != null">gxrqz,</if>
            <if test="fpzt != null">fpzt,</if>
            <if test="rzzt != null">rzzt,</if>
            <if test="rzrq != null">rzrq,</if>
            <if test="rzsq != null">rzsq,</if>
            <if test="rzlx != null">rzlx,</if>
            <if test="xxly != null">xxly,</if>
            <if test="yxse != null">yxse,</if>
            <if test="yqkgxbz != null">yqkgxbz,</if>
            <if test="glzt != null">glzt,</if>
            <if test="rzclzt != null">rzclzt,</if>
            <if test="sbyy != null">sbyy,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="companyId != null">#{companyId},</if>
            <if test="pch != null">#{pch},</if>
            <if test="cyjg != null">#{cyjg},</if>
            <if test="cyjgxx != null">#{cyjgxx},</if>
            <if test="fpzl != null and fpzl != ''">#{fpzl},</if>
            <if test="fpdm != null and fpdm != ''">#{fpdm},</if>
            <if test="fphm != null and fphm != ''">#{fphm},</if>
            <if test="cycs != null">#{cycs},</if>
            <if test="xhfmc != null">#{xhfmc},</if>
            <if test="xhfsbh != null">#{xhfsbh},</if>
            <if test="xhfdzdh != null">#{xhfdzdh},</if>
            <if test="xhfyhzh != null">#{xhfyhzh},</if>
            <if test="gmfsbh != null">#{gmfsbh},</if>
            <if test="gmfmc != null">#{gmfmc},</if>
            <if test="gmfdzdh != null">#{gmfdzdh},</if>
            <if test="gmfyhzh != null">#{gmfyhzh},</if>
            <if test="kprq != null">#{kprq},</if>
            <if test="fpje != null">#{fpje},</if>
            <if test="fpse != null">#{fpse},</if>
            <if test="jshj != null">#{jshj},</if>
            <if test="bz != null">#{bz},</if>
            <if test="jqbh != null">#{jqbh},</if>
            <if test="kpr != null">#{kpr},</if>
            <if test="skr != null">#{skr},</if>
            <if test="fhr != null">#{fhr},</if>
            <if test="jym != null">#{jym},</if>
            <if test="zfbz != null">#{zfbz},</if>
            <if test="status != null">#{status},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="dqskssq != null">#{dqskssq},</if>
            <if test="gxjzr != null">#{gxjzr},</if>
            <if test="gxrqq != null">#{gxrqq},</if>
            <if test="gxrqz != null">#{gxrqz},</if>
            <if test="fpzt != null">#{fpzt},</if>
            <if test="rzzt != null">#{rzzt},</if>
            <if test="rzrq != null">#{rzrq},</if>
            <if test="rzsq != null">#{rzsq},</if>
            <if test="rzlx != null">#{rzlx},</if>
            <if test="xxly != null">#{xxly},</if>
            <if test="yxse != null">#{yxse},</if>
            <if test="yqkgxbz != null">#{yqkgxbz},</if>
            <if test="glzt != null">#{glzt},</if>
            <if test="rzclzt != null">#{rzclzt},</if>
            <if test="sbyy != null">#{sbyy},</if>
         </trim>
    </insert>

    <update id="updateInvoiceCheck" parameterType="com.jianshui.income.domain.entity.InvoiceCheckRecord">
        update invoice_check
        <trim prefix="SET" suffixOverrides=",">
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="pch != null">pch = #{pch},</if>
            <if test="cyjg != null">cyjg = #{cyjg},</if>
            <if test="cyjgxx != null">cyjgxx = #{cyjgxx},</if>
            <if test="fpzl != null and fpzl != ''">fpzl = #{fpzl},</if>
            <if test="fpdm != null and fpdm != ''">fpdm = #{fpdm},</if>
            <if test="fphm != null and fphm != ''">fphm = #{fphm},</if>
            <if test="cycs != null">cycs = #{cycs},</if>
            <if test="xhfmc != null">xhfmc = #{xhfmc},</if>
            <if test="xhfsbh != null">xhfsbh = #{xhfsbh},</if>
            <if test="xhfdzdh != null">xhfdzdh = #{xhfdzdh},</if>
            <if test="xhfyhzh != null">xhfyhzh = #{xhfyhzh},</if>
            <if test="gmfsbh != null">gmfsbh = #{gmfsbh},</if>
            <if test="gmfmc != null">gmfmc = #{gmfmc},</if>
            <if test="gmfdzdh != null">gmfdzdh = #{gmfdzdh},</if>
            <if test="gmfyhzh != null">gmfyhzh = #{gmfyhzh},</if>
            <if test="kprq != null">kprq = #{kprq},</if>
            <if test="fpje != null">fpje = #{fpje},</if>
            <if test="fpse != null">fpse = #{fpse},</if>
            <if test="jshj != null">jshj = #{jshj},</if>
            <if test="bz != null">bz = #{bz},</if>
            <if test="jqbh != null">jqbh = #{jqbh},</if>
            <if test="kpr != null">kpr = #{kpr},</if>
            <if test="skr != null">skr = #{skr},</if>
            <if test="fhr != null">fhr = #{fhr},</if>
            <if test="jym != null">jym = #{jym},</if>
            <if test="zfbz != null">zfbz = #{zfbz},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createdAt != null">created_at = #{createdAt},</if>
            <if test="dqskssq != null">dqskssq = #{dqskssq},</if>
            <if test="gxjzr != null">gxjzr = #{gxjzr},</if>
            <if test="gxrqq != null">gxrqq = #{gxrqq},</if>
            <if test="gxrqz != null">gxrqz = #{gxrqz},</if>
            <if test="fpzt != null">fpzt = #{fpzt},</if>
            <if test="rzzt != null">rzzt = #{rzzt},</if>
            <if test="rzrq != null">rzrq = #{rzrq},</if>
            <if test="rzsq != null">rzsq = #{rzsq},</if>
            <if test="rzlx != null">rzlx = #{rzlx},</if>
            <if test="xxly != null">xxly = #{xxly},</if>
            <if test="yxse != null">yxse = #{yxse},</if>
            <if test="yqkgxbz != null">yqkgxbz = #{yqkgxbz},</if>
            <if test="glzt != null">glzt = #{glzt},</if>
            <if test="rzclzt != null">rzclzt = #{rzclzt},</if>
            <if test="sbyy != null">sbyy = #{sbyy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteInvoiceCheckById" parameterType="Long">
        delete from invoice_check where id = #{id}
    </delete>

    <delete id="deleteInvoiceCheckByIds" parameterType="Long">
        delete from invoice_check where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectInvoiceCheckByTime" parameterType="com.jianshui.income.domain.entity.InvoiceCheckRecord" resultMap="InvoiceCheckRecordResult">
        <include refid="selectInvoiceCheckVo"/>
        <where>
            <if test="companyId != null">company_id = #{companyId}</if>
            <!-- 数据范围过滤 -->
            <if test="params != null  "><!-- 开始时间检索 -->
                and date_format(created_at,'%y%m%d') &gt;= date_format(#{params.begintime},'%y%m%d')
            </if>
            <if test="params != null "><!-- 结束时间检索 -->
                and date_format(created_at,'%y%m%d') &lt;= date_format(#{params.endtime},'%y%m%d')
            </if>
            <if test="fpzl != null "> and fpzl = #{fpzl}</if>
        </where>
    </select>

</mapper>
