<?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.sunxung.factoring.mapper.invoicemanage.InvoiceBasicInfoMapper">
	<resultMap type="com.sunxung.factoring.entity.project.vo.BusinessProcessingTaskVo" id="baseResultMap">
		<result property="id" column="id" />
		<result property="businessNumber" column="business_number" />
		<result property="customerName" column="enterprise_name" />
		<result property="businessName" column="business_name" />
		<result property="openAmount" column="open_amount" />
		<result property="gmtModified" column="gmt_modified" />
		<result property="supplierName" column="supplierName" />
		<result property="platformName" column="platform_name" />
		<association property="status" column="invoice_status" resultMap="dictionaryResult"/>
		<association property="amountKind" column="amount_kind" resultMap="kindDictionaryResult" />
	</resultMap>

	<resultMap type="InvoiceBasicInfo" id="listVoResult">
		<result property="id" column="id" />
		<result property="businessNumber" column="business_number" />
		<result property="customerName" column="enterprise_name" />
		<result property="businessName" column="business_name" />
		<result property="openAmount" column="open_amount" />
		<result property="gmtModified" column="gmt_modified" />
		<result property="supplierName" column="supplierName" />
		<result property="tradingPlatformName" column="platform_name" />
		<association property="invoiceStatusDic" column="invoice_status" resultMap="dictionaryResult"/>
		<association property="amountKindDic" column="amount_kind" resultMap="kindDictionaryResult" />
	</resultMap>

	<resultMap id="dictionaryResult" type="Dictionary">
		<result property="id" column="did" />
		<result property="name" column="dname" />
		<result property="code" column="dcode" />
	</resultMap>

	<resultMap id="typeDictionaryResult" type="Dictionary">
		<result property="id" column="tid" />
		<result property="name" column="tname" />
		<result property="code" column="tcode" />
	</resultMap>

	<resultMap id="kindDictionaryResult" type="Dictionary">
		<result property="id" column="kid" />
		<result property="name" column="kname" />
		<result property="code" column="kcode" />
	</resultMap>

	<resultMap id="userResult" type="User">
		<result property="id" column="uid" />
		<result property="name" column="uname" />
	</resultMap>

	<select id="getListInfo" parameterType="java.lang.Long" resultMap="listVoResult">
		SELECT DISTINCT
			t1.id,
			t1.business_number ,
			t3.corporate_name enterprise_name ,
		    t2.project_name business_name,
			t7.corporate_name supplierName,
			t1.amount_kind,
			t1.open_amount,
			t1.invoice_status,
			t1.gmt_modified,
			t4.id did,
			t4.code dcode,
			t4.name dname,
			t5.id kid,
			t5.code kcode,
			t5.name kname,
			t8.platform_name
		FROM
			c_invoice_basic_info t1
				LEFT JOIN c_project_information t2 ON t1.business_number = t2.business_number
				LEFT JOIN c_enterprise t3 ON t1.enterprise_customer_info_id = t3.id
				LEFT JOIN t_dictionary t4 ON t1.invoice_status = t4.id
				LEFT JOIN t_dictionary t5 ON t1.amount_kind= t5.id
				LEFT JOIN c_supplier_info t6 ON t2.c_supplier_info_id = t6.id
				LEFT JOIN c_enterprise t7 ON t6.c_enterprise_id = t7.id
		left join trading_platform t8 on t1.trading_platform_id = t8.id
		WHERE t1.id = #{id}
		ORDER BY t1.gmt_modified Desc
	</select>

	<select id="findMyCompletedList" parameterType="com.sunxung.factoring.entity.project.vo.SearchBusinessProcessingTask" resultMap="baseResultMap">
		SELECT DISTINCT
			t2.id,
			t2.business_number ,
			t4.enterprise_name ,
		    t3.business_name,
			t2.amount_kind,
		    t2.open_amount,
			t2.invoice_status,
			t2.gmt_modified,
			t5.id did,
			t5.code dcode,
			t5.name dname,
			t6.id kid,
			t6.code kcode,
			t6.name kname
		FROM
			business_processing_log t1
			LEFT JOIN invoice_basic_info t2 ON t1.busness_key = t2.id
			LEFT JOIN business_basic_info t3 ON t2.business_number = t3.business_number
			LEFT JOIN enterprise_customer_info t4 ON t2.enterprise_customer_info_id = t4.id
			LEFT JOIN t_dictionary t5 ON t5.id = t2.invoice_status
			LEFT JOIN t_dictionary t6 ON t6.id = t2.amount_kind
		<where>
			t1.business_type = 'invoiceManager' AND t1.end_time IS NOT NULL
			<if test="userId != null">
				AND t1.assignee_id = #{userId}
			</if>
			<if test="fastSearch != null and fastSearch != '' ">
				AND (t3.business_number LIKE concat('%',#{fastSearch},'%') OR t3.business_name LIKE concat('%',#{fastSearch},'%') OR t4.enterprise_name LIKE concat('%',#{fastSearch},'%'))
			</if>
		</where>
		GROUP BY t1.busness_key,t2.gmt_create
		ORDER BY t2.gmt_modified DESC
	</select>

	<select id="findAllList" parameterType="com.sunxung.factoring.entity.project.vo.SearchBusinessProcessingTask" resultMap="baseResultMap">
		SELECT DISTINCT
		t2.id,
		t2.business_number ,
		t3.project_name business_name,
		t4.corporate_name enterprise_name ,
		t2.amount_kind,
		t2.open_amount,
		t2.invoice_status,
		t2.gmt_modified,
		t5.id did,
		t5.code dcode,
		t5.name dname,
		t6.id kid,
		t6.code kcode,
		t6.name kname,
		t8.corporate_name supplierName,
		t9.platform_name
		FROM
		business_processing_log t1
		LEFT JOIN c_invoice_basic_info t2 ON t1.busness_key = t2.id
		LEFT JOIN c_project_information t3 ON t2.business_number = t3.business_number
		LEFT JOIN c_enterprise t4 ON t2.enterprise_customer_info_id = t4.id
		LEFT JOIN t_dictionary t5 ON t5.id = t2.invoice_status
		LEFT JOIN t_dictionary t6 ON t6.id = t2.amount_kind
		LEFT JOIN c_supplier_info t7 ON t3.c_supplier_info_id = t7.id
		LEFT JOIN c_enterprise t8 ON t7.c_enterprise_id = t8.id
		left join trading_platform t9 on t2.trading_platform_id = t9.id
		<where>
			t1.business_type = 'invoiceManager'
			<if test="userIds != null and userIds.size()>0">
				AND t1.assignee_id in
				<foreach collection="userIds" index="index" item="item"  open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="businessNo != null and businessNo != ''">
				AND t2.business_number = #{businessNo}
			</if>
			<if test="fastSearch != null and fastSearch != '' ">
				AND (t8.corporate_name LIKE concat('%',#{fastSearch},'%') OR t3.project_name LIKE concat('%',#{fastSearch},'%') OR t4.corporate_name LIKE concat('%',#{fastSearch},'%'))
			</if>
			<if test="status != null and status != ''">
				AND t5.code = #{status}
			</if>
		</where>
		GROUP BY t1.busness_key,t2.gmt_create
		ORDER BY t2.gmt_modified DESC
	</select>
	<select id="saleSelectGoodsOfDiscount" resultType="SettlementGoodsInfo">
		SELECT
			t1.*,
			t3.goods_name,
			t3.goods_type,
			t3.goods_unit
		FROM
			settlement_goods_info t1
				LEFT JOIN settlement_basic_info t2 ON t2.id = t1.settlement_basic_info_id
				LEFT JOIN acceptance_goods_info t3 ON t3.id = t1.acceptance_goods_info_id
		WHERE t2.settlement_status  in (select id from t_dictionary where code in ('bill_need_sign','bill_sign')) and t2.business_number = #{businessNumber}
	</select>
	<select id="saleCalc" parameterType="com.sunxung.factoring.entity.invoicemanage.vo.SearchOpenQuantityVo" resultType="java.math.BigDecimal">
	select
		IFNULL(sum(c.open_quantity), 0)
	from sale_invoice_goods c , sale_invoice_info d
	where c.sale_invoice_info_id = d.id and d.invoice_status != #{invoiceStatus} and c.business_number = #{businessNumber}
		 and c.amount_kind = #{amountKind} and c.acceptance_business_goods_id = #{acceptanceGoodsId}
	</select>

	<select id="saleSelectSettlementOfPayment"
			resultType="com.sunxung.factoring.entity.settlement.SettlementBasicInfo" >
		select
			DISTINCT t1.*
		from settlement_basic_info t1
				 LEFT JOIN enterprise_customer_info t5 ON t1.supplier_id = t5.id
				 LEFT JOIN settlement_goods_info t2 ON t1.id = t2.settlement_basic_info_id
			LEFT JOIN acceptance_goods_info t3 ON t3.id = t2.acceptance_goods_info_id
			LEFT JOIN
		    (
			SELECT
				IFNULL( sum( c.open_quantity ), 0 ) num,
				c.settlement_goods_info_id
			FROM
				invoice_goods_info c
					left join invoice_basic_info d on d.id=c.invoice_basic_info_id
			WHERE
					d.invoice_status in ( SELECT id FROM t_dictionary WHERE CODE in ('invoice_create','invoice_pass' ) )
			GROUP BY
				c.settlement_goods_info_id
			) t4 on  t2.id = t4.settlement_goods_info_id
		WHERE
			t1.business_number = #{businessNumber}
		AND t1.settlement_status in (select id from t_dictionary where code in ('bill_need_sign','bill_sign'))
		AND t3.goods_quantity >  IFNULL(t4.num,0)
	</select>
	<select id="saleSelectSettlementOfPaymentGoods"
			resultType="com.sunxung.factoring.entity.invoicemanage.InvoiceGoodsInfo">
		select
			t1.id settlement_basic_info_id ,
		    t1.settlement_number,
			t3.goods_name,
			t3.goods_factory,
			t3.goods_type,
			t3.goods_unit,
			t3.goods_quantity,
			t2.id settlement_goods_info_id,
			t2.sales_price,
			t2.sales_total_price,
			IFNULL(t4.num,0) usedNum
		from settlement_basic_info t1
				 LEFT JOIN enterprise_customer_info t5 ON t1.supplier_id = t5.id
				 LEFT JOIN settlement_goods_info t2 ON t1.id = t2.settlement_basic_info_id
				 LEFT JOIN acceptance_goods_info t3 ON t3.id = t2.acceptance_goods_info_id
				 LEFT JOIN
			 (
				 SELECT
					 IFNULL( sum( c.open_quantity ), 0 ) num,
					 c.settlement_goods_info_id
				 FROM
					 invoice_goods_info c
					     left join invoice_basic_info d on d.id=c.invoice_basic_info_id
				 WHERE
						 d.invoice_status in ( SELECT id FROM t_dictionary WHERE CODE in ('invoice_create','invoice_pass' ) )
				 GROUP BY
					 c.settlement_goods_info_id
			 ) t4 on  t2.id = t4.settlement_goods_info_id
		WHERE
			t1.business_number = #{businessNumber}
		  AND t1.settlement_status in (select id from t_dictionary where code in ('bill_need_sign','bill_sign'))
		  AND t3.goods_quantity >  IFNULL(t4.num,0)
	</select>
</mapper>
