<?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.dxhy.jxpt.baseService.cpyAuthInvoice.dao.CpyRecordInvoiceDao" >

	<resultMap id="BaseResultMap"
			   type="com.dxhy.api.model.cpy.cpyInvoice.TDxRecordInvoiceCpyVo">
		<result column="invoice_code" property="invoiceCode"/>
		<result column="invoice_no" property="invoiceNo"/>
		<result column="eleInvoiceNo" property="eleInvoiceNo"/>
		<result column="kprq" property="kprq"/>
		<result column="invoice_type" property="invoiceType"/>
		<result column="gfsh" property="gfsh"/>
		<result column="gfmc" property="gfmc"/>
		<result column="xf_tax_no" property="xfsh"/>
		<result column="xf_name" property="xfmc"/>
		<result column="invoice_amount" property="invoiceAmount"/>
		<result column="tax_amount" property="taxAmount"/>
		<result column="dk_tax_amount" property="dkTaxAmount"/>
		<result column="invoice_status" property="invoiceStatus"/>
		<result column="deptId" property="deptId"/>
		<result column="glzt" property="glzt"/>
		<result column="uuid" property="uuid"/>
		<result column="bdk_status" property="bdkStatus"/>
		<result column="export_mark" property="exportMark"/>
		<result column="scan_id" property="scanId"/>
		<result column="in_account_status" property="inAccountStatus"/>
		<result column="settle_source" property="settleSource"/>
		<result column="settlement_no" property="settlementNo"/>
		<result column="settlement_status" property="settlementStatus"/>
		<result column="voucher_number" property="voucherNumber"/>
		<result column="total_amount" property="totalAmount"/>
		<result column="lq_tspz" property="lqTspz"/>
		<result column="lq_hzsdbs" property="lqHzsdbs"/>
		<result column="lq_cpyycbs" property="lqCpyycbs"/>
		<result column="lq_xfsyt" property="lqXfsyt"/>
		<result column="is_red" property="isRed"/>


		<result column="gxclzt" property="gxclzt"/>
		<result column="gxsbyy" property="gxsbyy"/>
		<result column="czsj" property="czsj"/>
		<result column="czr" property="czr"/>
		<result column="czr_name" property="czrName"/>

	</resultMap>

    <resultMap id="BbfpzhcxhjResultMap"
               type="com.dxhy.api.model.cpy.cpyInvoice.TDxRecordInvoiceCpyVo">
        <result column="hjsl" property="hjsl"/>
        <result column="hjje" property="hjje"/>
        <result column="hjse" property="hjse"/>
    </resultMap>

	<!--成品油勾选页面合计-->
    <select id="selectAuthPageListHj" databaseId="mysql" resultType="com.dxhy.api.model.cpy.cpyInvoice.TDxRecordInvoiceCpyVo" resultMap="BbfpzhcxhjResultMap" >
		select
        count(1) hjsl,
		ifnull(sum(t.invoice_amount),0) hjje,
		ifnull(sum(t.tax_amount),0) hjse
        from t_dx_invoice_cpy_gx cpygx left join t_dx_record_invoice t on t.uuid = cpygx.uuid

		<include refid="page_where_mysql"/>


	</select>

	<!--勾选列表-->
	<select id="selectAuthPageList" databaseId="mysql"  parameterType="java.util.Map"
			resultMap="BaseResultMap">
		select
		t.invoice_code,t.invoice_no,t.ele_invoice_no eleInvoiceNo,DATE_FORMAT(t.invoice_date,'%Y-%m-%d') kprq,t.gf_tax_no
		gfsh,t.gf_name gfmc,
		t.xf_tax_no,t.xf_name,t.tax_amount,t.dk_tax_amount,t.invoice_status,t.invoice_amount,DATE_FORMAT(t.rzh_date,'%Y-%m-%d')
		rzh_date ,DATE_FORMAT(t.qs_date,'%Y-%m-%d') qs_date,t.rzh_type,t.dept_id deptId,
		t.gx_user_name,t.rzh_belong_date
		dqskssq,t.auth_status,t.qs_type,t.qs_status,t.confirm_user,t.qs_name,t.rzh_yesorno,
		t.invoice_type , t.dept_name deptName,t.glzt
		glzt,t.uuid,t.bdk_status,t.scan_id,t.in_account_status,t.settle_source,
		t.settlement_no,t.is_red,
		t.settlement_status,t.voucher_number,t.total_amount,t.lq_hzsdbs,lq_cpyycbs,lq_xfsyt,lq_tspz,
		cpygx.gxclzt,
		cpygx.gxsbyy,
		DATE_FORMAT(cpygx.czsj,'%Y-%m-%d') czsj,
		cpygx.czr,
		cpygx.czr_name
		from t_dx_invoice_cpy_gx cpygx left join t_dx_record_invoice t
		on t.uuid = cpygx.uuid

		<include refid="page_where_mysql"/>

		order by cpygx.czsj desc
	</select>
    <select id="selectCountStatusPage" resultType="com.dxhy.api.model.db.TdxInvoiceCpyGxEntity">
        select
        cpygx.gxclzt
        from t_dx_invoice_cpy_gx cpygx left join t_dx_record_invoice t
		on t.uuid = cpygx.uuid
		where
        t.company = #{company}
        <if test="gfsh!=null and gfsh!='' ">
            and t.gf_tax_no in
            <foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        and cpygx.gxclzt in ('1','2','3')
    </select>

    <sql  id="page_where_mysql">
		<where>
			t.company = #{company}
			and t.gf_tax_no in
			<foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>

			<!--tabNum   未勾选0  勾选中1  已勾选2  勾选失败3 全部4-->
			<if test='tabNum != null and tabNum != "" and tabNum=="0" '>
				and cpygx.gxclzt = '0' and (t.lq_xfsyt is null or t.lq_xfsyt = '00')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="1" '>
				and cpygx.gxclzt in ('1','2')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="2" '>
				and cpygx.gxclzt = '4'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="3" '>
				and cpygx.gxclzt = '3'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="4" '>
				and cpygx.gxclzt in ('0','1','2','3','4')
			</if>

			<if test="kpksrq!=null and kpksrq!='' ">
				and t.invoice_date &gt;= #{kpksrq}
			</if>
			<if test="kpjsrq!=null and kpjsrq!='' ">
				and t.invoice_date &lt;= #{kpjsrq}
			</if>
			<if test="fpdm != null and fpdm != '' and fpdm != 'null' ">
				and t.invoice_code = #{fpdm}
			</if>
			<if test="fphm != null and fphm != '' and fphm != 'null' ">
				and t.invoice_no = #{fphm}
			</if>
			<if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
				and t.ele_invoice_no = #{eleInvoiceNo}
			</if>

			<if test="invoiceTypes!=null and invoiceTypes!='' ">
				and t.invoice_type in
				<foreach collection="invoiceTypes" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="fpzts!=null and fpzts!='' ">
				and t.invoice_status in
				<foreach collection="fpzts" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="glzts!=null and glzts!='' ">
				and t.glzt in
				<foreach collection="glzts" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="hzsdbs!=null and hzsdbs!='' and hzsdbs!='null'">
				and t.lq_hzsdbs = #{hzsdbs}
			</if>
			<if test="cpyycbs!=null and cpyycbs!='' and cpyycbs!='null'">
				and t.lq_cpyycbs = #{cpyycbs}
			</if>
		</where>
	</sql>



	<!-- oracle部分 -->
	<!--成品油勾选页面合计-->
	<select id="selectAuthPageListHj" databaseId="oracle" resultType="com.dxhy.api.model.cpy.cpyInvoice.TDxRecordInvoiceCpyVo" resultMap="BbfpzhcxhjResultMap" >
		select
		count(1) hjsl,
		ifnull(sum(t.invoice_amount),0) hjje,
		ifnull(sum(t.tax_amount),0) hjse
		from t_dx_invoice_cpy_gx cpygx left join t_dx_record_invoice t on t.uuid = cpygx.uuid

		<include refid="page_where_oracle"/>


	</select>

	<!--勾选列表-->
	<select id="selectAuthPageList" databaseId="oracle"  parameterType="java.util.Map"
			resultMap="BaseResultMap">
		select
		t.invoice_code,t.invoice_no,t.ele_invoice_no eleInvoiceNo,DATE_FORMAT(t.invoice_date,'%Y-%m-%d') kprq,t.gf_tax_no
		gfsh,t.gf_name gfmc,
		t.xf_tax_no,t.xf_name,t.tax_amount,t.dk_tax_amount,t.invoice_status,t.invoice_amount,DATE_FORMAT(t.rzh_date,'%Y-%m-%d')
		rzh_date ,DATE_FORMAT(t.qs_date,'%Y-%m-%d') qs_date,t.rzh_type,t.dept_id deptId,
		t.gx_user_name,t.rzh_belong_date
		dqskssq,t.auth_status,t.qs_type,t.qs_status,t.confirm_user,t.qs_name,t.rzh_yesorno,
		t.invoice_type , t.dept_name deptName,t.glzt
		glzt,t.uuid,t.bdk_status,t.scan_id,t.in_account_status,t.settle_source,
		t.settlement_no,t.is_red,
		t.settlement_status,t.voucher_number,t.total_amount,t.lq_hzsdbs,lq_cpyycbs,lq_xfsyt,lq_tspz,
		cpygx.gxclzt,
		cpygx.gxsbyy,
		DATE_FORMAT(cpygx.czsj,'%Y-%m-%d') czsj,
		cpygx.czr,
		cpygx.czr_name
		from t_dx_invoice_cpy_gx cpygx left join t_dx_record_invoice t
		on t.uuid = cpygx.uuid

		<include refid="page_where_oracle"/>

		order by cpygx.czsj desc
	</select>



	<sql  id="page_where_oracle">
		<where>
			t.company = #{company}
			and t.gf_tax_no in
			<foreach collection="gfsh" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>

			<!--tabNum   未勾选0  勾选中1  已勾选2  勾选失败3 全部4-->
			<if test='tabNum != null and tabNum != "" and tabNum=="0" '>
				and cpygx.gxclzt = '0' and (t.lq_xfsyt is null or t.lq_xfsyt = '00')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="1" '>
				and cpygx.gxclzt in ('1','2')
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="2" '>
				and cpygx.gxclzt = '4'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="3" '>
				and cpygx.gxclzt = '3'
			</if>
			<if test='tabNum != null and tabNum != "" and tabNum=="4" '>
				and cpygx.gxclzt in ('0','1','2','3','4')
			</if>

			<if test="kpksrq!=null and kpksrq!='' ">
				and t.invoice_date &gt;= #{kpksrq}
			</if>
			<if test="kpjsrq!=null and kpjsrq!='' ">
				and t.invoice_date &lt;= #{kpjsrq}
			</if>
			<if test="fpdm != null and fpdm != '' and fpdm != 'null' ">
				and t.invoice_code = #{fpdm}
			</if>
			<if test="fphm != null and fphm != '' and fphm != 'null' ">
				and t.invoice_no = #{fphm}
			</if>
			<if test="eleInvoiceNo!=null and eleInvoiceNo!='' and eleInvoiceNo!='null'">
				and t.ele_invoice_no = #{eleInvoiceNo}
			</if>

			<if test="invoiceTypes!=null and invoiceTypes!='' ">
				and t.invoice_type in
				<foreach collection="invoiceTypes" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="fpzts!=null and fpzts!='' ">
				and t.invoice_status in
				<foreach collection="fpzts" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="glzts!=null and glzts!='' ">
				and t.glzt in
				<foreach collection="glzts" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="hzsdbs!=null and hzsdbs!='' and hzsdbs!='null'">
				and t.lq_hzsdbs = #{hzsdbs}
			</if>
			<if test="cpyycbs!=null and cpyycbs!='' and cpyycbs!='null'">
				and t.lq_cpyycbs = #{cpyycbs}
			</if>
		</where>
	</sql>

</mapper>