<?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.zfire.mall.manager.commonmapper.front.CustomerFrontMapper">
	<update id="updateBatch">
   UPDATE customer_code_check a
        JOIN customer_code b ON a.bar_code = b.bar_code
        set
        a.sales_company = b.sales_company,
        a.material_code =b.material_code,
        a.specification =b.specification,
        a.material_type =b.material_type,
        a.consigner_time =b.consigner_time_v2,
        a.delivery_time =b.delivery_time_v2,
        a.order_number =b.order_number,
        a.receipt_company =b.receipt_company,
        a.address =b.address,
        a.warehouse =b.warehouse,
        a.type =b.type,
        a.sales_company_code =b.sales_company_code,
        a.verification_status =b.verification_status,
        a.customer_number =b.customer_number,
        a.customer_name =b.customer_name,
        a.customer_phone =b.customer_phone,
        a.verification_status =1
				where a.batch = #{batch}
	</update>


	<select id="stockList" resultType="com.zfire.mall.manager.bean.front.CustomerStockBean">
      	SELECT
			id,
			customer_number,
			customer_id,
			customer_name,
			material_old_number,
			material_code,
			material_name,
			specification,
			category_id,
			category_number,
			category_name,
			create_time,
			update_time,
			front_stock_num,
			customer_stock_num,
			lock_front_stock_num,
			lock_customer_stock_num,
		front_stock_num + lock_front_stock_num as allFrontStockNum,
		customer_stock_num + lock_customer_stock_num as allLockCustomerStockNum

		FROM
			customer_stock
			${ex.query}
		<if test="customers != null and customers.size > 0">
			and customer_number in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		${ex.orderBy}
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by create_time desc
		</if>
    </select>
	<select id="listCustomerAcc" resultType="com.zfire.mall.manager.bean.front.CustomerStockOrderBean">
		SELECT
			cso.id,
			cso.examine_status,
			case when cso.examine_status = 'OK' then '审核通过'
			when cso.examine_status = 'SAVE' then '保存'
			when cso.examine_status = 'WAIT' then '提审'
			when cso.examine_status = 'FAIL' then '驳回' else '关闭' end as examine_status_name,
			cso.customer_number,
			cso.customer_name,
			cso.customer_id,
			csa.category_id,
			csa.category_name,
			csa.category_number,
			csa.material_id,
			csa.material_code,
			csa.material_name,
			csa.material_old_number,
			csa.change_time,
			csa.direct_flag,
			if(csa.direct_flag = 1,'增加','减少') as direct_flag_name,
			csa.stock_change_qty,
			csa.stock_close_qty,
			csa.stock_cur_qty,
			csa.specification
		FROM
			customer_stock_order cso
			JOIN customer_stock_order_item csa ON cso.id = csa.order_id
			${ex.query}
		<if test="customerId != null and customerId !=''">
			and cso.customer_id = #{customerId}
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by cso.create_time desc
		</if>
			  ${ex.orderBy}
	</select>
    <select id="listStockAcc" resultType="com.zfire.mall.manager.bean.front.CustomerStockAccBean">
		SELECT
			id,
			customer_id,
		change_time,
			customer_number,
			customer_name,
			category_id,
			category_name,
			material_id,
			material_code,
			material_name,
			material_old_number,
			specification,
			direct_flag,
		IF
			( direct_flag = 1, '增加', '减少' ) AS direct_Flag_name,
			stock_cur_qty,
			stock_close_qty,
			stock_change_qty,
			ref,
			ref_type,
			notes
		FROM
			customer_stock_acc
		${ex.query}
		<if test="customerId != null and customerId !=''">
			and customer_id = #{customerId}
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by change_time desc,direct_flag asc
		</if>
		${ex.orderBy}
	</select>
    <select id="listTransfer" resultType="com.zfire.mall.manager.bean.front.CustomerTransferOrderBean">
		SELECT
		a.id,
		a.customer_number,
		a.customer_id,
		a.customer_name,
		a.transfer_time,
		a.create_time,
		a.update_time,
		a.stock_type,
		a.notes,
		a.status,
		a.create_by,
		a.update_by,
		a.sync_status,
		a.transfer_customer_name,
		a.transfer_customer_number,
		a.transfer_customer_id,
		b.material_old_number,
		b.material_code,
		b.material_name,
		b.material_id,
		b.qty,
		b.specification

		FROM
		customer_transfer_order a
		join customer_transfer_order_item b on a.id= b.transfer_order_id
		${ex.query}
		<if test="customers != null and customers.size > 0">
			and (a.customer_number in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
			or a.transfer_customer_number in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
			)
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
    <select id="listCostBill" resultType="com.zfire.mall.manager.bean.front.CostBillListVO">
		SELECT
		a.*
		FROM
		cost_bill a
		${ex.query}
		<if test="customerNumber != null and  customerNumber != ''">
			and a.merchant_name =#{customerNumber}
		</if>
		<if test="customerNumberS != null and customerNumberS.size > 0">
			and a.merchant_name in
			<foreach item="item" index="index" collection="customerNumberS" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
    <select id="listV2" resultType="com.zfire.mall.manager.bean.front.CustomerCodeVO">
		SELECT
		a.*
		FROM
		customer_code a
		${ex.query}
		and a.type  = 'QIANZHI'
		<if test="customers != null and customers.size > 0">
			and a.sales_company in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
	<select id="listCustomer" resultType="com.zfire.mall.manager.bean.front.CustomerCodeCustomerVO">
		SELECT
		a.*
		FROM
		customer_code a
		${ex.query}
		and a.type  = 'CUSTOMER'
		<if test="customers != null and customers.size > 0">
			and a.sales_company in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
	<select id="listCustomerCheck" resultType="com.zfire.mall.manager.bean.front.CustomerCodeCustomerCheckVO">
		SELECT
		a.*
		FROM
		customer_code_check a
		${ex.query}

		<if test="customers != null and customers.size > 0">
			and a.sales_company in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
    <select id="listDaCang" resultType="com.zfire.mall.manager.bean.front.CustomerCodeVO">
		SELECT
		a.*
		FROM
		customer_code a
		${ex.query}
		and a.type  = 'DACANG'
		<if test="customers != null and customers.size > 0">
			and a.sales_company in
			<foreach item="item" index="index" collection="customers" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>

		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>

	<select id="listMerchant" resultType="com.zfire.mall.manager.bean.front.CostMerchantListVO">
		SELECT
		a.*
		FROM
		cost_merchant a
		${ex.query}
		<if test="customerNumber != null and  customerNumber != ''">
			and a.merchant_name =#{customerNumber}
		</if>
		<if test="customerNumberS != null and customerNumberS.size > 0">
			and a.merchant_name in
			<foreach item="item" index="index" collection="customerNumberS" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
	<select id="listSales" resultType="com.zfire.mall.manager.bean.front.CostSalesListVO">
		SELECT
		a.*
		FROM
		cost_sales a
		${ex.query}
		<if test="customerNumber != null and  customerNumber != ''">
			and a.merchant_name =#{customerNumber}
		</if>
		<if test="customerNumberS != null and customerNumberS.size > 0">
			and a.merchant_name in
			<foreach item="item" index="index" collection="customerNumberS" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
    <select id="listTicket" resultType="com.zfire.mall.manager.bean.front.CostTicketListVO">
				SELECT
		a.*,
		b.id as itemId,
		b.time,
		b.material_name,
		b.material_code,
		b.material_specification,
		b.material_type,
		b.material_number,
		b.order_code as itemOrderCode,
		b.return_order_code,
		b.merchant_code,
		b.merchant_name,
		b.belong_company_type,
		b.order_type,
		b.customer_name,
		b.customer_phone,
		b.warehouse_code,
		b.warehouse_name,
		b.receipt_address,
		b.order_type_detail,
		b.material_cost_type,
		b.coefficient,
		b.subsidy_coefficient,
		b.unload_upstairs_coefficient,
		b.unit_price,
		b.return_order_cost,
		b.merchant_cost,
		b.allowance_cost,
		b.unload_upstairs_cost,
		b.total_cost,
		b.order_date,
		b.error_result,
		b.is_account_checking,
		b.is_audit,
		b.is_unload,
		b.is_upstairs,
		b.is_real_unload,
		b.is_real_upstairs,
		b.is_finish,
		b.last_bill_code,
		b.merchan_check_code,
		b.sales_check_code,
		b.belong_company_cost_rule,
		b.material_price,
		b.is_delete,
		b.bill_code
		FROM
		cost_ticket a
		join cost_ticket_bill b on a.ticket_code = b.ticket_code
		${ex.query}
		<if test="customerNumber != null and  customerNumber != ''">
			and b.merchant_name =#{customerNumber}
		</if>
		<if test="customerNumberS != null and customerNumberS.size > 0">
			and b.merchant_name in
			<foreach item="item" index="index" collection="customerNumberS" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
	<select id="listCode" resultType="com.zfire.mall.manager.bean.front.CustomerCodeInstallVO">
		SELECT
		a.*
		FROM
		customer_code_install a
		${ex.query}
		<if test="customerNumberS != null and customerNumberS.size > 0">
			and a.sales_company in
			<foreach item="item" index="index" collection="customerNumberS" open="(" separator=","
					 close=")">
				#{item}
			</foreach>
		</if>
		<if test="ex.orderBy == null or  ex.orderBy == ''">
			order by a.create_time desc
		</if>
		${ex.orderBy}
	</select>
</mapper>

