<?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.brand.apply.dao.BrandApplyMapper">

    <resultMap id="resultMap" type="com.cftech.brand.apply.model.BrandApply">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        <result column="name" property="name"/>
        <result column="department_id" property="departmentId"/>
        <result column="category_id" property="categoryId"/>
        <result column="cost_center" property="costCenter"/>
        <result column="require_specify" property="requireSpecify"/>
        <result column="line_manager" property="lineManager"/>
        <result column="line_manager_email" property="lineManagerEmail"/>
        <result column="is_material_object" property="isMaterialObject"/>
        <result column="delivery_date" property="deliveryDate"/>
        <result column="distribution_class" property="distributionClass"/>
        <result column="emp_id" property="empId"/>
        <result column="contact_emp_id" property="contactEmpId"/>
        <result column="supplier_id" property="supplierId"/>
        <result column="man_hour" property="manHour"/>
        <result column="material_detail" property="materialDetail"/>
        <result column="quoted_price" property="quotedPrice"/>
        <result column="status" property="status"/>
        <result column="accounts_id" property="accountsId"/>
        <result column="del_flag" property="delFlag"/>
        <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="legal_entity" property="legalEntity"/>
        <result column="close_reason" property="closeReason"/>
        <result column="actual_delivery_date" property="actualDeliveryDate"/>
        <result column="inner_cost" property="innerCost"/>
        <result column="outer_cost" property="outerCost"/>
        <result column="total_cost" property="totalCost"/>
        <result column="is_score" property="isScore"/>
        <result column="design_quanlity_score" property="designQuanlityScore"/>
        <result column="make_quanlity_score" property="makeQuanlityScore"/>
        <result column="manager_feedback" property="managerFeedback"/>
        <result column="service_score" property="serviceScore"/>
        <result column="feedback" property="feedback"/>

        <result column="estimate_internal_costs" property="estimateInternalCosts"/>
        <result column="hourly_rate" property="hourlyRate"/>
        <result column="estimate_external_costs" property="estimateExternalCosts"/>
        <result column="intended_delivery_date" property="intendedDeliveryDate"/>
        <result column="open_id" property="openId"/>
        <result column="audit_id" property="auditId"/>
        <result column="estimated_workload" property="estimatedWorkload"/>
        <result column="manager_remark" property="managerRemark"/>
        <result column="product_name" property="productName"/>
        <result column="product_format" property="productFormat"/>
        <result column="product_label" property="productLabel"/>
        <result column="file_prefix" property="filePrefix"/>
    </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,
                                                number,
                                                name,
                                                email,
                                                department_id,
                                                category_id,
                                                cost_center,
                                                require_specify,
                                                line_manager,
                                                line_manager_email,
                                                is_material_object,
                                                delivery_date,
                                                distribution_class,
                                                emp_id,
                                                contact_emp_id,
                                                supplier_id,
                                                man_hour,
                                                material_detail,
                                                quoted_price,
                                                status,
                                                accounts_id,
                                                del_flag,
                                                create_time,
                                                update_time,
                                                description,
                                                create_by,
                                                update_by,
                                                legal_entity,
                                                close_reason,
                                                actual_delivery_date,
                                                inner_cost,
                                                outer_cost,
                                                total_cost,
                                                is_score,
                                                design_quanlity_score,
                                                make_quanlity_score,
                                                manager_feedback,
                                                service_score,
                                                feedback,
                                                estimate_internal_costs,
                                                hourly_rate,
                                                estimate_external_costs,
                                                intended_delivery_date,
                                                open_id,
                                                audit_id,
                                                estimated_workload,
                                                manager_remark,
                                                product_name,
                                                product_format,
                                                product_label,
                                                file_prefix
                        </sql>


    <insert id="save" parameterType="com.cftech.brand.apply.model.BrandApply" useGeneratedKeys="true"
            keyProperty="id">
        insert into t_schaeffler_brand_apply
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{number, jdbcType=VARCHAR},
        #{name, jdbcType=VARCHAR},
        #{email,jdbcType=VARCHAR},
        #{departmentId, jdbcType=BIGINT},
        #{categoryId, jdbcType=BIGINT},
        #{costCenter, jdbcType=VARCHAR},
        #{requireSpecify, jdbcType=VARCHAR},
        #{lineManager, jdbcType=VARCHAR},
        #{lineManagerEmail, jdbcType=VARCHAR},
        #{isMaterialObject, jdbcType=VARCHAR},
        #{deliveryDate, jdbcType=TIMESTAMP},
        #{distributionClass, jdbcType=VARCHAR},
        #{empId, jdbcType=BIGINT},
        #{contactEmpId,jdbcType=BIGINT},
        #{supplierId, jdbcType=BIGINT},
        #{manHour, jdbcType=DOUBLE},
        #{materialDetail, jdbcType=VARCHAR},
        #{quotedPrice, jdbcType=DOUBLE},
        #{status, jdbcType=VARCHAR},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT},
        #{legalEntity,jdbcType=VARCHAR},
        #{closeReason,jdbcType=VARCHAR},
        #{actualDeliveryDate,jdbcType=VARCHAR},
        #{innerCost,jdbcType=VARCHAR},
        #{outerCost,jdbcType=VARCHAR},
        #{totalCost,jdbcType=VARCHAR},
        #{isScore,jdbcType=VARCHAR},
        #{designQuanlityScore,jdbcType=VARCHAR},
        #{makeQuanlityScore,jdbcType=VARCHAR},
        #{managerFeedback,jdbcType=VARCHAR},
        #{serviceScore,jdbcType=VARCHAR},
        #{feedback,jdbcType=VARCHAR},
        #{estimateInternalCosts,jdbcType=DOUBLE},
        #{hourlyRate,jdbcType=DOUBLE},
        #{estimateExternalCosts,jdbcType=DOUBLE},
        #{intendedDeliveryDate,jdbcType=TIMESTAMP},
        #{openId,jdbcType=VARCHAR},
        #{auditId,jdbcType=BIGINT},
        #{estimatedWorkload,jdbcType=VARCHAR},
        #{managerRemark,jdbcType=VARCHAR},
        #{productName,jdbcType=VARCHAR},
        #{productFormat,jdbcType=VARCHAR},
        #{productLabel,jdbcType=VARCHAR},
        #{filePrefix,jdbcType=VARCHAR}
        )
    </insert>

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

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM t_schaeffler_brand_apply t
        <include refid="sqlWhere"/>

    </select>

    <select id="countVo" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM t_schaeffler_brand_apply t
        inner join t_schaeffler_brand_category c on c.id=t.category_id
        left join t_schaeffler_brand_supplier s on s.id=t.supplier_id
        left join t_schaeffler_brand_emp e on e.id=t.emp_id
        left join t_schaeffler_brand_emp ce on ce.id=t.contact_emp_id
        inner join t_schaeffler_brand_department d on d.id=t.department_id
        left join t_schaeffler_brand_manager m on m.id=t.audit_id
        <include refid="sqlWhere"/>

    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM t_schaeffler_brand_apply t
        <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="fetchSearchByPageVo" parameterType="java.util.Map" resultType="com.cftech.brand.apply.model.BrandApplyVo">
        select t.id,t.number,t.name,t.email,t.cost_center costCenter,t.department_id departmentId,t.line_manager lineManager,t.line_manager_email lineManagerEmail,
        t.category_id categoryId,t.require_specify requireSpecify,t.is_material_object isMaterialObject,t.delivery_date deliveryDate,t.description,t.status,
        t.legal_entity legalEntity,t.distribution_class distributionClass,t.emp_id empId,t.supplier_id supplierId,t.man_hour manHour,t.quoted_price quotedPrice,
        t.description,t.delivery_date deliveryDate,t.create_time createTime,d.name departmentName,
        c.name categoryName,s.name supplierName,e.name empName,ce.contact_information contactInformation,ce.name contactEmpName,
        t.estimate_internal_costs estimateInternalCosts,t.hourly_rate hourlyRate,t.estimate_external_costs estimateExternalCosts,
        t.intended_delivery_date intendedDeliveryDate,t.open_id openId,m.name auditName,
        t.design_quanlity_score designQuanlityScore,t.make_quanlity_score makeQuanlityScore,t.manager_feedback managerFeedback,
        t.service_score serviceScore,t.feedback,t.inner_cost innerCost,t.outer_cost outerCost,t.total_cost totalCost,
        t.actual_delivery_date actualDeliveryDate,t.estimated_workload estimatedWorkload,
        t.manager_remark managerRemark,
        t.product_name productName,t.product_format productFormat, t.product_label productLabel, t.file_prefix filePrefix
        from t_schaeffler_brand_apply t
        inner join t_schaeffler_brand_category c on c.id=t.category_id
        left join t_schaeffler_brand_supplier s on s.id=t.supplier_id
        left join t_schaeffler_brand_emp e on e.id=t.emp_id
        left join t_schaeffler_brand_emp ce on ce.id=t.contact_emp_id
        inner join t_schaeffler_brand_department d on d.id=t.department_id
        left join t_schaeffler_brand_manager m on m.id=t.audit_id
        <include refid="sqlWhere"/>
        <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.brand.apply.model.BrandApply">
        update t_schaeffler_brand_apply
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                name = #{name, jdbcType=VARCHAR},
            </if>
            <if test="departmentId != null">
                department_id = #{departmentId, jdbcType=BIGINT},
            </if>
            <if test="categoryId != null">
                category_id = #{categoryId, jdbcType=BIGINT},
            </if>
            <if test="costCenter != null">
                cost_center = #{costCenter, jdbcType=VARCHAR},
            </if>
            <if test="requireSpecify != null">
                require_specify = #{requireSpecify, jdbcType=VARCHAR},
            </if>
            <if test="lineManager != null">
                line_manager = #{lineManager, jdbcType=VARCHAR},
            </if>
            <if test="lineManagerEmail != null">
                line_manager_email = #{lineManagerEmail, jdbcType=VARCHAR},
            </if>
            <if test="isMaterialObject != null">
                is_material_object = #{isMaterialObject, jdbcType=VARCHAR},
            </if>
            <if test="deliveryDate != null">
                delivery_date = #{deliveryDate, jdbcType=TIMESTAMP},
            </if>
            <if test="distributionClass != null">
                distribution_class = #{distributionClass, jdbcType=VARCHAR},
            </if>
            <if test="empId != null">
                emp_id = #{empId, jdbcType=BIGINT},
            </if>
            <if test="contactEmpId!=null">
                contact_emp_id = #{contactEmpId,jdbcType=BIGINT},
            </if>
            <if test="supplierId != null">
                supplier_id = #{supplierId, jdbcType=BIGINT},
            </if>
            <if test="manHour != null">
                man_hour = #{manHour, jdbcType=DOUBLE},
            </if>
            <if test="materialDetail != null">
                material_detail = #{materialDetail, jdbcType=VARCHAR},
            </if>
            <if test="quotedPrice != null">
                quoted_price = #{quotedPrice, jdbcType=DOUBLE},
            </if>
            <if test="status != null">
                status = #{status, 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="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="legalEntity != null">
                legal_entity = #{legalEntity, jdbcType=BIGINT},
            </if>
            <if test="closeReason != null">
                close_reason = #{closeReason,jdbcType=VARCHAR},
            </if>
            <if test="actualDeliveryDate != null">
                actual_delivery_date = #{actualDeliveryDate,jdbcType=VARCHAR},
            </if>
            <if test="innerCost != null">
                inner_cost = #{innerCost,jdbcType=VARCHAR},
            </if>
            <if test="outerCost != null">
                outer_cost = #{outerCost,jdbcType=VARCHAR},
            </if>
            <if test="totalCost != null">
                total_cost = #{totalCost,jdbcType=VARCHAR},
            </if>
            <if test="isScore != null">
                is_score = #{isScore,jdbcType=VARCHAR},
            </if>
            <if test="designQuanlityScore != null">
                design_quanlity_score = #{designQuanlityScore,jdbcType=VARCHAR},
            </if>
            <if test="makeQuanlityScore != null">
                make_quanlity_score = #{makeQuanlityScore,jdbcType=VARCHAR},
            </if>
            <if test="serviceScore != null">
                service_score = #{serviceScore,jdbcType=VARCHAR},
            </if>
            <if test="feedback != null">
                feedback = #{feedback,jdbcType=VARCHAR},
            </if>
            <if test="estimateInternalCosts != null">
                estimate_internal_costs = #{estimateInternalCosts, jdbcType=DOUBLE},
            </if>
            <if test="hourlyRate != null">
                hourly_rate = #{hourlyRate, jdbcType=DOUBLE},
            </if>
            <if test="estimateExternalCosts != null">
                estimate_external_costs = #{estimateExternalCosts, jdbcType=DOUBLE},
            </if>
            <if test="intendedDeliveryDate != null">
                intended_delivery_date = #{intendedDeliveryDate, jdbcType=TIMESTAMP},
            </if>
            <if test="openId!=null">
                open_id = #{openId,jdbcType=VARCHAR},
            </if>
            <if test="auditId!=null">
                audit_id = #{auditId,jdbcType=BIGINT},
            </if>
            <if test="estimatedWorkload!=null">
                estimated_workload =#{estimatedWorkload,jdbcType=VARCHAR},
            </if>
            <if test="managerRemark!=null">
                manager_remark = #{managerRemark,jdbcType=VARCHAR},
            </if>
            <if test="productName!=null">
                product_name = #{productName,jdbcType=VARCHAR},
            </if>
            <if test="productFormat!=null">
                product_format = #{productFormat,jdbcType=VARCHAR},
            </if>
            <if test="productLabel!=null">
                product_label = #{productLabel,jdbcType=VARCHAR},
            </if>
            <if test="filePrefix!=null">
                file_prefix = #{filePrefix,jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

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

    <resultMap  id="permResultMap" type="com.cftech.brand.apply.model.BrandApplyVo">
        <id property="id" column="id"/>
        <collection property="details" column="id" select="selectChildByApplyId"/>
    </resultMap>

    <select id="selectChildByApplyId" parameterType="String" resultType="com.cftech.brand.apply.detail.model.BrandApplyDetailVo">
        select d.id detailId,d.apply_id applyId,d.material_id materialId,m.name materialName,d.quantity
        FROM t_schaeffler_brand_apply_detail d
        inner join t_schaeffler_brand_material m on m.id=d.material_id
        where d.del_flag='0' and d.apply_id=#{id}
    </select>

    <select id="getApplyData" parameterType="java.util.Map" resultMap="permResultMap">
        select t.id id,t.number,t.name,t.email,t.cost_center costCenter,t.department_id departmentId,t.line_manager lineManager,t.line_manager_email lineManagerEmail,
        t.category_id categoryId,t.require_specify requireSpecify,t.is_material_object isMaterialObject,t.delivery_date deliveryDate,t.description,t.status,
        t.legal_entity legalEntity,t.distribution_class distributionClass,t.emp_id empId,t.supplier_id supplierId,t.man_hour manHour,t.quoted_price quotedPrice,
        t.create_time createTime,
        c.name categoryName,s.name supplierName,e.name empName,ce.contact_information contactInformation,ce.name contactEmpName,
        d.name departmentName,t.estimate_internal_costs estimateInternalCosts,t.hourly_rate hourlyRate,t.estimate_external_costs estimateExternalCosts,
        t.intended_delivery_date intendedDeliveryDate,t.open_id openId,m.name auditName,
        t.design_quanlity_score designQuanlityScore,t.make_quanlity_score makeQuanlityScore,t.manager_feedback managerFeedback,
        t.service_score serviceScore,t.feedback,t.inner_cost innerCost,t.outer_cost outerCost,t.total_cost totalCost,
        t.actual_delivery_date actualDeliveryDate,t.estimated_workload estimatedWorkload,
        t.manager_remark managerRemark,t.audit_id auditId,
        t.product_name productName,t.product_format productFormat, t.product_label productLabel, t.file_prefix filePrefix
        from t_schaeffler_brand_apply t
        inner join t_schaeffler_brand_category c on c.id=t.category_id
        left join t_schaeffler_brand_supplier s on s.id=t.supplier_id
        left join t_schaeffler_brand_emp e on e.id=t.emp_id
        left join t_schaeffler_brand_emp ce on ce.id=t.contact_emp_id
        inner join t_schaeffler_brand_department d on d.id=t.department_id
        left join t_schaeffler_brand_manager m on m.id=t.audit_id
        <include refid="sqlWhere"/>
        <if test="openId!=null">
             and (t.open_id=#{openId} or m.open_id=#{openId})
        </if>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

</mapper>