<?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.cftech.common.typeappform.dao.TypeAppFormMapper">

    <resultMap id="resultMap" type="com.cftech.common.typeappform.model.TypeAppForm">
        <id column="id" property="id"/>
        <result column="openid" property="openid"/>
        <result column="org" property="org"/>
        <result column="department" property="department"/>
        <result column="cost_center" property="costCenter"/>
        <result column="address" property="address"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="description" property="description"/>
        <result column="create_by" property="createBy"/>
        <result column="update_by" property="updateBy"/>
        <result column="serial_number" property="serialNumber"/>
        <result column="number" property="number"/>
        <result column="approver" property="approver"/>
        <result column="name" property="name"/>
        <result column="items" property="items"/>
        <result column="typeform" property="typeform"/>
        <result column="issuing_state" property="issuingState"/>
        <result column="legal_entity" property="legalEntity"/>
        <result column="remarks" property="remarks"/>
        <result column="use_id" property="useId"/>
        <result column="app_price" property="appPrice"/>
        <result column="pdfpath" property="pdfpath"/>
        <result column="dname" property="dname"/>
        <result column="aname" property="aname"/>
        <result column="uname" property="uname"/>
        <result column="grant_source" property="grantSource"/>
        <result column="total_price" property="totalPrice"/>
        <result column="dis_total_price" property="disTotalPrice"/>
        <result column="total_logistics_price" property="totalLogisticsPrice"/>
        <result column="dis_logistics_price" property="disLogisticsPrice"/>
        <result column="grant_remarks" property="grantRemarks"/>
        <result column="dis_grant_remarks" property="disGrantRemarks"/>
        <result column="logistics_order_info" property="logisticsOrderInfo"/>
        <result column="dis_logistics_order_info" property="disLogisticsOrderInfo"/>
        <result column="is_cancel" property="isCancel"/>
        <result column="manager_submit_remarks" property="managerSubmitRemarks"/>
        <result column="c_items" property="cItems"/>
        <result column="manager_all_evaluate" property="managerAllEvaluate"/>
        <result column="manager_quality_evaluate" property="managerQualityEvaluate"/>
        <result column="manager_logistics_evaluate" property="managerLogisticsEvaluate"/>
        <result column="manager_evaluate_remarks" property="managerEvaluateRemarks"/>
        <result column="apply_all_evaluate" property="applyAllEvaluate"/>
        <result column="apply_quality_evaluate" property="applyQualityEvaluate"/>
        <result column="apply_logistics_evaluate" property="applyLogisticsEvaluate"/>
        <result column="apply_evaluate_remarks" property="applyEvaluateRemarks"/>
        <result column="manager_quality_time" property="managerQualityTime"/>
        <result column="apply_quality_time" property="applyQualityTime"/>
        <result column="manager_confirm_time" property="managerConfirmTime"/>
    </resultMap>

    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>

    <sql id="sqlColumns">
                                    id,
                                                openid,
                                                org,
                                                department,
                                                cost_center,
                                                address,
                                                accounts_id,
                                                del_flag,
                                                status,
                                                create_time,
                                                update_time,
                                                description,
                                                create_by,
                                                update_by,
                                                serial_number,
                                                number,
                                                approver,
                                                items,
                                                typeform,
                                                total_price,
                                                name,
                                                issuing_state,
                                                legal_entity,
                                                remarks,
                                                use_id,app_price,pdfpath,dname,aname,uname,grant_source,dis_total_price,
                                                total_logistics_price,dis_logistics_price,grant_remarks,dis_grant_remarks,
                                                logistics_order_info,dis_logistics_order_info,is_cancel,manager_submit_remarks,c_items,
                                                manager_all_evaluate,manager_quality_evaluate,manager_logistics_evaluate,manager_evaluate_remarks,
                                                apply_all_evaluate,apply_quality_evaluate,apply_logistics_evaluate,apply_evaluate_remarks,
                                                manager_quality_time,apply_quality_time,manager_confirm_time
                        </sql>


    <insert id="save" parameterType="com.cftech.common.typeappform.model.TypeAppForm" useGeneratedKeys="true"
            keyProperty="id">
        insert into wx_mp_common_appform
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{openid, jdbcType=VARCHAR},
        #{org, jdbcType=VARCHAR},
        #{department, jdbcType=BIGINT },
        #{costCenter, jdbcType=VARCHAR },
        #{address, jdbcType=VARCHAR },
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT},
        #{serialNumber, jdbcType=BIGINT},
        #{number, jdbcType=VARCHAR},
        #{approver, jdbcType=BIGINT},
        #{items, jdbcType=CLOB},
        #{typeform, jdbcType=BIGINT},
        #{totalPrice, jdbcType=VARCHAR},#{name},#{issuingState},#{legalEntity},#{remarks},
        #{useId},#{appPrice},#{pdfpath},#{dname},#{aname},#{uname},#{grantSource},#{disTotalPrice}
        ,#{totalLogisticsPrice},#{disLogisticsPrice},#{grantRemarks},#{disGrantRemarks},#{logisticsOrderInfo},#{disLogisticsOrderInfo}
        ,#{isCancel},#{managerSubmitRemarks},#{cItems},#{managerAllEvaluate},#{managerQualityEvaluate},#{managerLogisticsEvaluate},#{managerEvaluateRemarks},
        #{applyAllEvaluate},#{applyQualityEvaluate},#{applyLogisticsEvaluate},#{applyEvaluateRemarks},
        #{managerQualityTime},#{applyQualityTime},#{managerConfirmTime}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM wx_mp_common_appform t
        WHERE t.id=#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_common_appform ca
        left join wx_mp_common_typeform ct on ct.id = ca.typeform
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        ca.*,g.*
        FROM wx_mp_common_appform ca
        left join wx_mp_common_typeform ct on ct.id = ca.typeform
        left join (select appform,manager_grant_time managerGrantTime,dis_grant_time disGrantTime,delivery_time allGrantTime
        from wx_mp_common_grantitems group by appform) g on g.appform = ca.id
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="countServer" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_common_appform ca
        left join wx_mp_common_typeform ct on ct.id = ca.typeform
        <include refid="sqlWhere"/>
        <if test="isDisRole!=null and isDisRole=='0'.toString()">
            and (ca.grant_source=0 or ca.grant_source=2)
        </if>
    </select>

    <select id="fetchSearchByPageServer" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        ca.*
        FROM wx_mp_common_appform ca
        left join wx_mp_common_typeform ct on ct.id = ca.typeform
        <include refid="sqlWhere"/>
        <if test="isDisRole!=null and isDisRole=='0'.toString()">
            and (ca.grant_source=0 or ca.grant_source=2)
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <update id="update" parameterType="com.cftech.common.typeappform.model.TypeAppForm">
        update wx_mp_common_appform
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="openid != null">
                openid = #{openid, jdbcType=VARCHAR},
            </if>
            <if test="org != null">
                org = #{org, jdbcType=VARCHAR},
            </if>
            <if test="department != null">
                department = #{department, jdbcType=BIGINT },
            </if>
            <if test="costCenter != null">
                cost_center = #{costCenter, jdbcType=VARCHAR },
            </if>
            <if test="address != null">
                address = #{address, jdbcType=VARCHAR },
            </if>
            <if test="accountsId != null">
                accounts_id = #{accountsId, jdbcType=BIGINT},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag, jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status, jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            <if test="description != null">
                description = #{description, jdbcType=VARCHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy, jdbcType=BIGINT},
            </if>
            <if test="updateBy != null">
                update_by = #{updateBy, jdbcType=BIGINT},
            </if>
            <if test="serialNumber != null">
                serial_number = #{serialNumber, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="updateBy != null">
                approver = #{approver, jdbcType=BIGINT},
            </if>
            <if test="items != null">
                items = #{items, jdbcType=CLOB},
            </if>
            <if test="typeform != null">
                typeform = #{typeform, jdbcType=BIGINT},
            </if>
            <if test="totalPrice != null">
                total_price = #{totalPrice, jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                name = #{name, jdbcType=VARCHAR},
            </if>
            <if test="issuingState != null">
                issuing_state = #{issuingState, jdbcType=VARCHAR},
            </if>
            <if test="legalEntity != null">
                legal_entity = #{legalEntity, jdbcType=VARCHAR},
            </if>
            <if test="remarks != null">
                remarks = #{remarks, jdbcType=VARCHAR},
            </if>
            <if test="useId != null">
                use_id = #{useId, jdbcType=BIGINT},
            </if>
            <if test="appPrice != null">
                app_price = #{appPrice, jdbcType=VARCHAR},
            </if>
            <if test="pdfpath != null">
                pdfpath = #{pdfpath, jdbcType=VARCHAR},
            </if>

            <if test="dname != null">
                dname = #{dname, jdbcType=VARCHAR},
            </if>
            <if test="aname != null">
                aname = #{aname, jdbcType=VARCHAR},
            </if>
            <if test="uname != null">
                uname = #{uname, jdbcType=VARCHAR},
            </if>
            <if test="grantSource !=null">
                grant_source = #{grantSource,jdbcType=VARCHAR},
            </if>
            <if test="disTotalPrice !=null">
                dis_total_price = #{disTotalPrice,jdbcType=VARCHAR},
            </if>
            <if test="totalLogisticsPrice !=null">
                total_logistics_price = #{totalLogisticsPrice,jdbcType=VARCHAR},
            </if>
            <if test="disLogisticsPrice !=null">
                dis_logistics_price = #{disLogisticsPrice,jdbcType=VARCHAR},
            </if>
            <if test="grantRemarks!=null">
                grant_remarks = #{grantRemarks,jdbcType=VARCHAR},
            </if>
            <if test="disGrantRemarks!=null">
                dis_grant_remarks = #{disGrantRemarks,jdbcType=VARCHAR},
            </if>
            <if test="logisticsOrderInfo !=null">
                logistics_order_info = #{logisticsOrderInfo,jdbcType=VARCHAR},
            </if>
            <if test="disLogisticsOrderInfo !=null">
                dis_logistics_order_info = #{disLogisticsOrderInfo,jdbcType=VARCHAR},
            </if>
            <if test="isCancel !=null">
                is_cancel = #{isCancel,jdbcType=VARCHAR},
            </if>
            <if test="managerSubmitRemarks !=null">
                manager_submit_remarks = #{managerSubmitRemarks,jdbcType=VARCHAR},
            </if>
            <if test="cItems!=null">
                c_items = #{cItems},
            </if>
            <if test="managerAllEvaluate!=null">
                manager_all_evaluate = #{managerAllEvaluate},
            </if>
            <if test="managerQualityEvaluate!=null">
                manager_quality_evaluate = #{managerQualityEvaluate},
            </if>
            <if test="managerLogisticsEvaluate!=null">
                manager_logistics_evaluate = #{managerLogisticsEvaluate},
            </if>
            <if test="managerEvaluateRemarks!=null">
                manager_evaluate_remarks = #{managerEvaluateRemarks},
            </if>
            <if test="applyAllEvaluate!=null">
                apply_all_evaluate = #{applyAllEvaluate},
            </if>
            <if test="applyQualityEvaluate!=null">
                apply_quality_evaluate = #{applyQualityEvaluate},
            </if>
            <if test="applyLogisticsEvaluate!=null">
                apply_logistics_evaluate = #{applyLogisticsEvaluate},
            </if>
            <if test="applyEvaluateRemarks!=null">
                apply_evaluate_remarks = #{applyEvaluateRemarks},
            </if>
            <if test="managerQualityTime!=null">
                manager_quality_time = #{managerQualityTime},
            </if>
            <if test="applyQualityTime!=null">
                apply_quality_time = #{applyQualityTime},
            </if>
            <if test="managerConfirmTime!=null">
                manager_confirm_time = #{managerConfirmTime},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

    <update id="delete" parameterType="java.lang.Long">
        update wx_mp_common_appform set del_flag=1 where id=#{id,jdbcType=BIGINT}
    </update>

    <select id="getAppitemsGrantReport" parameterType="java.util.Map" resultType="com.cftech.common.typeappform.model.AppitemsGrantReport">
         SELECT
         ca.number,
        ca.name,
        ca.authentic_stock stock,
        ca.price,
        ROUND(ca.authentic_stock * ca.price,2) stockPrice,
        SUM(IFNULL(cg.reality_grant_count,0)) grantCount,
        ROUND(SUM(IFNULL(cg.reality_grant_count,0))*ca.price,2) grantPrice,
        ca.authentic_stock+SUM(IFNULL(cg.reality_grant_count,0)) count,
        ROUND((ca.authentic_stock+SUM(IFNULL(cg.reality_grant_count,0)))*ca.price,2) countPrice
        FROM
        wx_mp_common_appitems ca
        LEFT JOIN wx_mp_common_grantitems cg ON ca.id = cg.appitem
        where ca.typeform = #{typeform}
        group by ca.id;
    </select>


    <select id="getSumGrantPrice" parameterType="java.util.Map" resultType="java.lang.String">
        select  sum(app_price) from wx_mp_common_appform
        <include refid="sqlWhere"/>
        <if test="type.toString()=='0'.toString()">
            and
            cost_center NOT IN
            <foreach item="item" index="index" collection="costCenters" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="type.toString()=='1'.toString()">
            and
            cost_center IN
            <foreach item="item" index="index" collection="costCenters" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>
</mapper>
