<?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.jeeplus.modules.workinvoicedetail.dao.WorkInvoiceDetailDao">
    
	<sql id="workInvoiceDetailColumns">
		a.id AS "id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.code AS "code",
		a.number AS "number",
		a.total_money AS "totalMoney",
		a.tax AS "tax",
		a.tax_money AS "taxMoney",
		a.tax_rate AS "taxRate",
		a.income_money AS "incomeMoney",
		a.date AS "date",
		a.return_id AS "returnId",
		w1.ext AS "invoiceExt",
		w1.invoice_type AS "invoiceInvoiceType",
		w2.name AS "invoiceClientName",
		w1.orUnicode AS "invoiceOrUnicode",
		w1.content AS "invoiceContent",
		w1.remarks AS "invoiceRemarks",
		a.state AS "state",
		a.back_sign AS "backSign",
		a.back_number AS "backNumber",
		a.back_code AS "backCode",
		w1.number AS "invocieNumber",
		w1.drawer_name AS "invoiceDrawerName",
		s2.top_company AS "invoiceOfficeName",
		w3.contract_num AS "contractNum",
		w3.name AS "contractName",
		w4.name AS "contractClientName",
		a.status AS "status",
		a.return_number AS "returnNumber",
		a.return_reason AS "returnReason",
		a.return_cancle AS "returnCancle",
		a.return_sign AS "returnSign",
		a.return_date AS "returnDate",
		a.return_user AS "returnUser",
		a.process_instance_id AS "processInstanceId",
		a.sign AS "sign",
		a.company_id AS "comId",
		a.office_id AS "officeId",
		a.ainvoice_id AS "ainvoiceId",
		a.invoice_id AS "invoiceId",
		w2.name As "workInvoice.client.name",
		s2.top_company AS "workInvoice.office.name",
		wir.return_number AS "returnNumbers",
		a.settle_flag AS "settleFlag"
	</sql>

	<sql id="workInvoiceDetailColumn">
		a.id AS "id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.number AS "number",
		a.total_money AS "totalMoney",
		a.tax AS "tax",
		a.return_id AS "returnId",
		a.tax_money AS "taxMoney",
		a.tax_rate AS "taxRate",
		a.income_money AS "incomeMoney",
		a.state AS "state",
		a.back_sign AS "backSign",
		a.back_number AS "backNumber",
		a.back_code AS "backCode",
		a.date AS "date",
		a.code AS "code",
		a.status AS "status",
		a.return_number AS "returnNumber",
		a.return_reason AS "returnReason",
		a.return_cancle AS "returnCancle",
		a.return_sign AS "returnSign",
		a.return_date AS "returnDate",
		a.return_user AS "returnUser",
		a.process_instance_id AS "processInstanceId",
		a.sign AS "sign",
		a.ainvoice_id AS "ainvoiceId",
		a.invoice_id AS "invoiceId",
		a.settle_flag AS "settleFlag"
	</sql>
	
	<sql id="workInvoiceDetailJoins">
		left join work_invoice w1 on w1.id = a.invoice_id
		LEFT JOIN work_client_info w2 ON  w2.id = w1.client_id
		left join sys_office s2 on s2.id = w1.office_id
		left join project_records p on p.id = w1.project_id
		left join work_contract_info w3 on w3.id = p.contract_id
		left join work_client_info w4 on w4.id = w3.client_id
		left join sys_office o ON o.id = a.office_id
		left join sys_office s ON s.id = a.company_id
		left join work_invoicereturn wir on wir.invoice_id =a.return_id
	</sql>
	
    
	<select id="get" resultType="WorkInvoiceDetail" >
		SELECT 
			<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="WorkInvoiceDetail" >
		SELECT 
			<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} and a.back_sign !='冲销'
			<if test="beginContractDate != null and endContractDate != null and beginContractDate != '' and endContractDate != ''">
				AND a.date BETWEEN #{beginContractDate} AND #{endContractDate}
			</if>
			<if test="number != null and number != ''">
				AND a.number = #{number}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="invoiceId != null and invoiceId != ''">
				AND a.invoice_id = #{invoiceId}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>

	<select id="findListByContractInfo" resultType="WorkInvoiceDetail" >
		SELECT
		<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			AND w3.id = #{id}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>

	<select id="findListReturn" resultType="WorkInvoiceDetail" >
		SELECT
		DISTINCT
		<include refid="workInvoiceDetailColumn"/>
		FROM
		work_invoice_detail a
		left join sys_office o ON o.id = a.office_id
		left join sys_office s ON s.id = a.company_id
		LEFT JOIN work_invoice w ON w.id = a.invoice_id
		LEFT JOIN work_invoicereturn wir on a.id = wir.invoice_id
		LEFT JOIN project_records p ON w.project_id = p.id
		LEFT JOIN work_project_user wp ON p.id = wp.project_id
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} and a.income_money = '0' and (wir.status is null or wir.status = '5') and w.invoice_type is not null
			and w.invoice_state = '5'
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="backSign != null and backSign != ''">
				AND a.back_sign = #{backSign}
			</if>
			<if test="createBy != null and createBy.id != null and createBy.id != ''">
				AND w.create_by = #{createBy.id}
			</if>
			<if test="createBy != null and createBy.id != null and createBy.id != ''">
				AND wp.user_id = #{createBy.id}
			</if>
			/*${sqlMap.dsf}*/
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>

	<select id="afindList" resultType="WorkInvoiceDetail" >
		SELECT
			a.id AS "id",
			a.create_by AS "createBy.id",
			a.create_date AS "createDate",
			a.update_by AS "updateBy.id",
			a.update_date AS "updateDate",
			a.remarks AS "remarks",
			a.del_flag AS "delFlag",
			a.number AS "number",
			a.total_money AS "totalMoney",
			a.tax AS "tax",
			a.tax_money AS "taxMoney",
			a.tax_rate AS "taxRate",
			a.income_money AS "incomeMoney",
			a.state AS "state",
			a.back_sign AS "backSign",
			a.back_number AS "backNumber",
			a.back_code AS "backCode",
			a.return_id AS "returnId",
			a.date AS "date",
			a.code AS "code",
			a.status AS "status",
			a.return_number AS "returnNumber",
			a.return_reason AS "returnReason",
			a.return_cancle AS "returnCancle",
			a.return_sign AS "returnSign",
			a.return_date AS "returnDate",
			a.return_user AS "returnUser",
			a.process_instance_id AS "processInstanceId",
			a.sign AS "sign",
			w2.name As "workInvoice.client.name",
			s2.name AS "workInvoice.office.name",
			a.company_id AS "comId",
			a.office_id AS "officeId",
			a.ainvoice_id AS "ainvoiceId",
			a.invoice_id AS "invoiceId"
		FROM work_invoice_detail a
			left join work_invoicealter w1 on w1.id = a.ainvoice_id
			LEFT JOIN work_client_info w2 ON  w2.id = w1.client_id
			left join sys_office s2 on s2.id = w1.office_id
			JOIN sys_office o ON o.id = a.office_id
			JOIN sys_office s ON s.id = a.company_id
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="number != null and number != ''">
				AND a.number = #{number}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="ainvoiceId != null and ainvoiceId != ''">
				AND a.ainvoice_id = #{ainvoiceId}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>

	<select id="findLists" resultType="WorkInvoiceDetail" >
		SELECT
		<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL} and w1.del_flag = 0 and w2.del_flag = 0 and p.del_flag = 0 and w3.del_flag = 0 and w4.del_flag = 0
			and w1.invoice_type is not null
			<if test="beginContractDate != null and endContractDate != null and beginContractDate != '' and endContractDate != ''">
				AND a.date BETWEEN #{beginContractDate} AND #{endContractDate}
			</if>
			<if test="workInvoice !=null and workInvoice.client != null and workInvoice.client.name != null and workInvoice.client.name != ''">
				AND w2.name LIKE
				<if test="dbName == 'oracle'">'%'||#{workInvoice.client.name}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{workInvoice.client.name}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{workInvoice.client.name},'%')</if>
			</if>
			<if test="invoiceDrawerName !=null and invoiceDrawerName != ''">
				AND w1.drawer_name LIKE
				<if test="dbName == 'oracle'">'%'||#{invoiceDrawerName}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{invoiceDrawerName}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{invoiceDrawerName},'%')</if>
			</if>
			<if test="invoiceOfficeName !=null and invoiceOfficeName != ''">
				AND s2.name LIKE
				<if test="dbName == 'oracle'">'%'||#{invoiceOfficeName}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{invoiceOfficeName}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{invoiceOfficeName},'%')</if>
			</if>
			<if test="contractName !=null and contractName != ''">
				AND w3.name LIKE
				<if test="dbName == 'oracle'">'%'||#{contractName}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{contractName}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{contractName},'%')</if>
			</if>
			<if test="number != null and number != ''">
				AND a.number = #{number}
			</if>
			<if test="state != null and state != ''">
				AND a.state = #{state}
			</if>
			<if test="invoiceInvoiceType != null and invoiceInvoiceType != ''">
				AND w1.invoice_type = #{invoiceInvoiceType}
			</if>
			<if test="status != null and status != ''">
				AND a.status = #{status}
			</if>
			<if test="invoiceId != null and invoiceId != ''">
				AND a.invoice_id = #{invoiceId}
			</if>
			${sqlMap.dsf}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="WorkInvoiceDetail" >
		SELECT 
			<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>

	<select id="findByCode" resultType="WorkInvoiceDetail" >
		SELECT
		<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			and a.code = #{code} and a.number = #{number}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO work_invoice_detail(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			number,
			total_money,
			tax,
			tax_money,
			tax_rate,
			income_money,
			state,
			back_sign,
			back_number,
			back_code,
			date,
			code,
			status,
			return_number,
			return_reason,
			return_cancle,
			return_sign,
			return_date,
			return_user,
			process_instance_id,
			sign,
			company_id,
			office_id,
			ainvoice_id,
			invoice_id,
			return_id,
			settle_flag
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{number},
			#{totalMoney},
			#{tax},
			#{taxMoney},
			#{taxRate},
			#{incomeMoney},
			#{state},
			#{backSign},
			#{backNumber},
			#{backCode},
			#{date},
			#{code},
			#{status},
			#{returnNumber},
			#{returnReason},
			#{returnCancle},
			#{returnSign},
			#{returnDate},
			#{returnUser},
			#{processInstanceId},
			#{sign},
			#{comId},
			#{officeId},
			#{ainvoiceId},
			#{invoiceId},
			#{returnId},
			#{settleFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE work_invoice_detail SET 	
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			number = #{number},
			total_money = #{totalMoney},
			tax = #{tax},
			tax_money = #{taxMoney},
			tax_rate = #{taxRate},
			income_money = #{incomeMoney},
			state = #{state},
			back_sign = #{backSign},
			back_number = #{backNumber},
			back_code = #{backCode},
			date = #{date},
			code = #{code},
			status = #{status},
			return_number = #{returnNumber},
			return_reason = #{returnReason},
			return_cancle = #{returnCancle},
			return_sign = #{returnSign},
			return_date = #{returnDate},
			return_user = #{returnUser},
			process_instance_id = #{processInstanceId},
			sign = #{sign},
			company_id = #{comId},
			office_id=#{officeId},
			ainvoice_id = #{ainvoiceId},
			invoice_id = #{invoiceId},
			return_id = #{returnId},
			settle_flag = #{settleFlag}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM work_invoice_detail
		WHERE id = #{id}
	</update>

	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE work_invoice_detail SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>

	<update id="updateProcessInstanceId">
		UPDATE work_invoice_detail SET
		process_instance_id = #{processInstanceId}
		WHERE id = #{id}
	</update>

	<update id="updateByCode">
		UPDATE work_invoice_detail SET
		code = #{code},
		number = #{number}
		WHERE id = #{id}
	</update>

	<!-- 获取流程ID -->
	<select id="getByProcessInstanceId" resultType="WorkInvoiceDetail">
		SELECT
		<include refid="workInvoiceDetailColumns"/>
		FROM work_invoice_detail a
		<include refid="workInvoiceDetailJoins"/>
		WHERE a.process_instance_id = #{processInstanceId}
	</select>

	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="WorkInvoiceDetail" statementType="STATEMENT">
		select * FROM work_invoice_detail  where ${propertyName} = '${value}'
	</select>

    <select id="findListWithDept" resultType="WorkInvoiceDetail" >
        SELECT
        <include refid="workInvoiceDetailColumn"/>
        FROM work_invoice wi
          LEFT JOIN  work_invoice_detail a ON wi.id=a.invoice_id
        LEFT JOIN work_invoicereturn ir ON ir.invoice_id = a.id
        <where>
            <if test="comId!=null and comId!=''">
                AND wi.company_id = #{comId}
            </if>
            <if test="officeId!=null and officeId!=''">
                AND wi.office_id = #{officeId}
            </if>
            <if test="number != null and number != ''">
                AND a.number = #{number}
            </if>
            <if test="state != null and state != ''">
                AND a.state = #{state}
            </if>
            <!--<if test="backSign != null and backSign != ''">-->
                <!--AND a.back_sign = #{backSign}-->
            <!--</if>-->
            <if test="exclusiveIds!=null">
                AND a.id NOT IN
                <foreach collection="exclusiveIds" item="exid" index="index"
                         open="(" close=")" separator=",">
                    #{exid}
                </foreach>
            </if>
			AND wi.invoice_state = '5'
--             AND a.total_money+0 > a.income_money+0
            AND wi.del_flag = #{DEL_FLAG_NORMAL}
            AND a.del_flag = #{DEL_FLAG_NORMAL}
            AND ir.id IS NULL
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <select id="findPageByCompany" resultType="WorkInvoiceDetail" >
        SELECT
        <include refid="workInvoiceDetailColumn"/>
        FROM work_invoice wi
        LEFT JOIN  work_invoice_detail a ON wi.id=a.invoice_id
        LEFT JOIN sys_office o ON o.id = wi.office_id
        <where>
            <if test="comId!=null and comId!=''">
                AND wi.company_id = #{comId} AND wi.ext = '1'
            </if>
            <choose>
                <when test="currentUser.branchOffice!=null and currentUser.branchOffice.id!=null and currentUser.branchOffice.id!=''">
                    AND find_in_set(#{currentUser.branchOffice.id},o.parent_ids)
                </when>
                <otherwise>
                    AND find_in_set(#{currentUser.company.id},o.parent_ids) AND (o.branch_office IS NULL || o.branch_office ='')
                </otherwise>
            </choose>
            <if test="exclusiveIds!=null">
                AND a.id NOT IN
                <foreach collection="exclusiveIds" item="exid" index="index"
                         open="(" close=")" separator=",">
                    #{exid}
                </foreach>
            </if>
            AND (a.settle_flag = '0' or a.settle_flag is NULL )
            AND wi.del_flag = #{DEL_FLAG_NORMAL}
            AND a.del_flag = #{DEL_FLAG_NORMAL}
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <update id="updateSettleFlag">
        UPDATE work_invoice_detail SET
		settle_flag = #{settleFlag}
		WHERE id = #{id}
    </update>

	<select id="countByNumber" resultType="int">
		SELECT count(1) from work_invoice_detail WHERE  number = #{number}
	</select>

	<select id="countByNumAndComp" resultType="int">
		SELECT count(1) from work_invoice_detail WHERE  number = #{invoiceNum} AND company_id = #{comId}
	</select>
</mapper>