<?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.zhyc.module.sale.mapper.SxSheepSaleMapper">

    <resultMap type="SxSheepSale" id="SxSheepSaleResult">
        <result property="id" column="id" />
        <result property="bsManageTags" column="bs_manage_tags" />
        <result property="sheepfoldId" column="sheepfold_id" />
        <result property="variety" column="variety" />
        <result property="sheepName" column="sheep_name" />
        <result property="gender" column="gender" />
        <result property="monthAge" column="month_age" />
        <result property="parity" column="parity" />
        <result property="breed" column="breed" />
        <result property="postLambingDay" column="post_lambing_day" />
        <result property="lactationDay" column="lactation_day" />
        <result property="lambingDay" column="lambing_day" />
        <result property="eventType" column="event_type" />
        <result property="saleDate" column="sale_date" />
        <result property="pricingMethod" column="pricing_method" />
        <result property="unitPrice" column="unit_price" />
        <result property="totalPrice" column="total_price" />
        <result property="totalWeight" column="total_weight" />
        <result property="avgWeight" column="avg_weight" />
        <result property="avgPricePerSheep" column="avg_price_per_sheep" />
        <result property="saleType" column="sale_type" />
        <result property="diseaseType" column="disease_type" />
        <result property="secondaryReason" column="secondary_reason" />
        <result property="groupCode" column="group_code" />
        <result property="customerId" column="customer_id" />
        <result property="salesPersonId" column="sales_person_id" />
        <result property="quarantineNo" column="quarantine_no" />
        <result property="approvalNo" column="approval_no" />
        <result property="technicianId" column="technician_id" />
        <result property="handlerId" column="handler_id" />
        <result property="createdBy" column="created_by" />
        <result property="createdAt" column="created_at" />
        <result property="remark" column="remark" />
        <result property="userId" column="user_id" />
        <result property="deptId" column="dept_id" />

        <result property="customerName" column="customer_name" />
        <result property="customerPhone" column="customer_phone" />
        <result property="customerAddress" column="customer_address" />
        <result property="customerArea" column="customer_area" />
        <result property="salesPersonName" column="sales_person_name" />
        <result property="technicianName" column="technician_name" />
        <result property="handlerName" column="handler_name" />
        <result property="createdByName" column="created_by_name" />
        <result property="sheepfoldName" column="sheepfold_name" />
        <result property="currentWeight" column="current_weight" />
    </resultMap>

    <sql id="selectSxSheepSaleVo">
        select s.id, s.bs_manage_tags, s.sheepfold_id, s.variety, s.sheep_name, s.gender, s.month_age, s.parity, s.breed, s.post_lambing_day, s.lactation_day, s.lambing_day, s.event_type, s.sale_date, s.pricing_method, s.unit_price, s.total_price, s.total_weight, s.avg_weight, s.avg_price_per_sheep, s.sale_type, s.disease_type, s.secondary_reason, s.group_code, s.customer_id, s.sales_person_id, s.quarantine_no, s.approval_no, s.technician_id, s.handler_id, s.created_by, s.created_at, s.remark, s.user_id, s.dept_id
        from sx_sheep_sale s
    </sql>

    <select id="selectSxSheepSaleList" parameterType="SxSheepSale" resultMap="SxSheepSaleResult">
        SELECT
        s.*,
        c.name AS customer_name,
        c.phone AS customer_phone,
        c.address AS customer_address,
        CONCAT(IFNULL(c.province,''), IFNULL(c.city,''), IFNULL(c.district,'')) AS customer_area,
        u_sale.nick_name AS sales_person_name,
        u_tech.nick_name AS technician_name,
        u_hand.nick_name AS handler_name,
        u_create.nick_name AS created_by_name,
        sf.sheepfold_name AS sheepfold_name
        FROM sx_sheep_sale s
        LEFT JOIN sx_customer c ON s.customer_id = c.id
        LEFT JOIN sys_user u_sale ON s.sales_person_id = u_sale.user_id
        LEFT JOIN sys_user u_tech ON s.technician_id = u_tech.user_id
        LEFT JOIN sys_user u_hand ON s.handler_id = u_hand.user_id
        LEFT JOIN sys_user u_create ON s.created_by = u_create.user_id
        LEFT JOIN da_sheepfold sf ON s.sheepfold_id = sf.id
        <where>
            <if test="bsManageTagsList != null and bsManageTagsList.size() > 0">
                AND s.bs_manage_tags IN
                <foreach collection="bsManageTagsList" item="tag" open="(" separator="," close=")">
                    #{tag}
                </foreach>
            </if>
            <if test="(bsManageTagsList == null or bsManageTagsList.size() == 0) and bsManageTags != null and bsManageTags != ''">
                AND s.bs_manage_tags LIKE CONCAT('%', #{bsManageTags}, '%')
            </if>

            <if test="sheepfoldId != null "> AND s.sheepfold_id = #{sheepfoldId}</if>
            <if test="variety != null and variety != ''"> AND s.variety = #{variety}</if>
            <if test="sheepName != null and sheepName != ''"> AND s.sheep_name = #{sheepName}</if>
            <if test="gender != null and gender != ''"> AND s.gender = #{gender}</if>
            <if test="breed != null and breed != ''"> AND s.breed = #{breed}</if>
            <if test="saleDate != null"> AND s.sale_date = #{saleDate}</if>
            <if test="saleType != null and saleType != ''"> AND s.sale_type = #{saleType}</if>
            <if test="pricingMethod != null and pricingMethod != ''"> AND s.pricing_method = #{pricingMethod}</if>
            <if test="groupCode != null and groupCode != ''"> AND s.group_code = #{groupCode}</if>

            <if test="approvalNo != null and approvalNo != ''">
                AND s.approval_no LIKE CONCAT('%', #{approvalNo}, '%')
            </if>

            <if test="customerName != null and customerName != ''"> AND c.name LIKE CONCAT('%', #{customerName}, '%')</if>
            <if test="salesPersonId != null"> AND s.sales_person_id = #{salesPersonId}</if>
            <if test="customerArea != null and customerArea != ''">
                AND (c.province LIKE CONCAT('%', #{customerArea}, '%')
                OR c.city LIKE CONCAT('%', #{customerArea}, '%')
                OR c.district LIKE CONCAT('%', #{customerArea}, '%'))
            </if>

            ${params.dataScope}
        </where>
        ORDER BY s.sale_date DESC, s.id DESC
    </select>

    <select id="selectSxSheepSaleById" parameterType="Long" resultMap="SxSheepSaleResult">
        SELECT
            s.*,
            c.name AS customer_name,
            c.phone AS customer_phone,
            c.address AS customer_address,
            CONCAT(IFNULL(c.province,''), IFNULL(c.city,''), IFNULL(c.district,'')) AS customer_area,
            u_sale.nick_name AS sales_person_name,
            u_tech.nick_name AS technician_name,
            u_hand.nick_name AS handler_name,
            u_create.nick_name AS created_by_name,
            sf.sheepfold_name AS sheepfold_name
        FROM sx_sheep_sale s
                 LEFT JOIN sx_customer c ON s.customer_id = c.id
                 LEFT JOIN sys_user u_sale ON s.sales_person_id = u_sale.user_id
                 LEFT JOIN sys_user u_tech ON s.technician_id = u_tech.user_id
                 LEFT JOIN sys_user u_hand ON s.handler_id = u_hand.user_id
                 LEFT JOIN sys_user u_create ON s.created_by = u_create.user_id
                 LEFT JOIN da_sheepfold sf ON s.sheepfold_id = sf.id
        WHERE s.id = #{id}
    </select>

    <sql id="selectSheepFileVo">
        select
            bs_manage_tags,
            variety,
            name as sheep_name,
            gender,
            month_age,
            parity,
            breed,
            post_lambing_day,
            lactation_day,
            lambing_day,
            sheepfold_id,
            current_weight
        from sheep_file
    </sql>

    <select id="selectSheepInfoByTag" parameterType="String" resultMap="SxSheepSaleResult">
        <include refid="selectSheepFileVo"/>
        where bs_manage_tags = #{bsManageTags}
    </select>

    <select id="selectSheepTagsBySheepfoldId" parameterType="Long" resultType="String">
        SELECT bs_manage_tags
        FROM sheep_file
        WHERE sheepfold_id = #{sheepfoldId}
    </select>

    <select id="searchEarNumbers" resultType="java.lang.String">
        SELECT DISTINCT bs_manage_tags
        FROM sheep_file
        WHERE bs_manage_tags LIKE CONCAT(#{query}, '%')
        ORDER BY bs_manage_tags
            LIMIT 50
    </select>

    <insert id="insertSxSheepSale" parameterType="SxSheepSale" useGeneratedKeys="true" keyProperty="id">
        insert into sx_sheep_sale
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bsManageTags != null and bsManageTags != ''">bs_manage_tags,</if>
            <if test="sheepfoldId != null">sheepfold_id,</if>
            <if test="variety != null and variety != ''">variety,</if>
            <if test="sheepName != null and sheepName != ''">sheep_name,</if>
            <if test="gender != null and gender != ''">gender,</if>
            <if test="monthAge != null">month_age,</if>
            <if test="parity != null">parity,</if>
            <if test="breed != null and breed != ''">breed,</if>
            <if test="postLambingDay != null">post_lambing_day,</if>
            <if test="lactationDay != null">lactation_day,</if>
            <if test="lambingDay != null">lambing_day,</if>
            <if test="eventType != null and eventType != ''">event_type,</if>
            <if test="saleDate != null">sale_date,</if>
            <if test="pricingMethod != null and pricingMethod != ''">pricing_method,</if>
            <if test="unitPrice != null">unit_price,</if>
            <if test="totalPrice != null">total_price,</if>
            <if test="totalWeight != null">total_weight,</if>
            <if test="avgWeight != null">avg_weight,</if>
            <if test="avgPricePerSheep != null">avg_price_per_sheep,</if>
            <if test="saleType != null and saleType != ''">sale_type,</if>
            <if test="diseaseType != null and diseaseType != ''">disease_type,</if>
            <if test="secondaryReason != null and secondaryReason != ''">secondary_reason,</if>
            <if test="groupCode != null and groupCode != ''">group_code,</if>
            <if test="customerId != null">customer_id,</if>
            <if test="salesPersonId != null">sales_person_id,</if>
            <if test="quarantineNo != null and quarantineNo != ''">quarantine_no,</if>
            <if test="approvalNo != null and approvalNo != ''">approval_no,</if>
            <if test="technicianId != null">technician_id,</if>
            <if test="handlerId != null">handler_id,</if>
            <if test="createdBy != null">created_by,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="remark != null">remark,</if>
            <if test="userId != null">user_id,</if>
            <if test="deptId != null">dept_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bsManageTags != null and bsManageTags != ''">#{bsManageTags},</if>
            <if test="sheepfoldId != null">#{sheepfoldId},</if>
            <if test="variety != null and variety != ''">#{variety},</if>
            <if test="sheepName != null and sheepName != ''">#{sheepName},</if>
            <if test="gender != null and gender != ''">#{gender},</if>
            <if test="monthAge != null">#{monthAge},</if>
            <if test="parity != null">#{parity},</if>
            <if test="breed != null and breed != ''">#{breed},</if>
            <if test="postLambingDay != null">#{postLambingDay},</if>
            <if test="lactationDay != null">#{lactationDay},</if>
            <if test="lambingDay != null">#{lambingDay},</if>
            <if test="eventType != null and eventType != ''">#{eventType},</if>
            <if test="saleDate != null">#{saleDate},</if>
            <if test="pricingMethod != null and pricingMethod != ''">#{pricingMethod},</if>
            <if test="unitPrice != null">#{unitPrice},</if>
            <if test="totalPrice != null">#{totalPrice},</if>
            <if test="totalWeight != null">#{totalWeight},</if>
            <if test="avgWeight != null">#{avgWeight},</if>
            <if test="avgPricePerSheep != null">#{avgPricePerSheep},</if>
            <if test="saleType != null and saleType != ''">#{saleType},</if>
            <if test="diseaseType != null and diseaseType != ''">#{diseaseType},</if>
            <if test="secondaryReason != null and secondaryReason != ''">#{secondaryReason},</if>
            <if test="groupCode != null and groupCode != ''">#{groupCode},</if>
            <if test="customerId != null">#{customerId},</if>
            <if test="salesPersonId != null">#{salesPersonId},</if>
            <if test="quarantineNo != null and quarantineNo != ''">#{quarantineNo},</if>
            <if test="approvalNo != null and approvalNo != ''">#{approvalNo},</if>
            <if test="technicianId != null">#{technicianId},</if>
            <if test="handlerId != null">#{handlerId},</if>
            <if test="createdBy != null">#{createdBy},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="remark != null">#{remark},</if>
            <if test="userId != null">#{userId},</if>
            <if test="deptId != null">#{deptId},</if>
        </trim>
    </insert>

    <update id="updateSxSheepSale" parameterType="SxSheepSale">
        update sx_sheep_sale
        <trim prefix="SET" suffixOverrides=",">
            <if test="bsManageTags != null and bsManageTags != ''">bs_manage_tags = #{bsManageTags},</if>
            <if test="sheepfoldId != null">sheepfold_id = #{sheepfoldId},</if>
            <if test="variety != null and variety != ''">variety = #{variety},</if>
            <if test="sheepName != null and sheepName != ''">sheep_name = #{sheepName},</if>
            <if test="gender != null and gender != ''">gender = #{gender},</if>
            <if test="monthAge != null">month_age = #{monthAge},</if>
            <if test="parity != null">parity = #{parity},</if>
            <if test="breed != null and breed != ''">breed = #{breed},</if>
            <if test="postLambingDay != null">post_lambing_day = #{postLambingDay},</if>
            <if test="lactationDay != null">lactation_day = #{lactationDay},</if>
            <if test="lambingDay != null">lambing_day = #{lambingDay},</if>
            <if test="eventType != null and eventType != ''">event_type = #{eventType},</if>
            <if test="saleDate != null">sale_date = #{saleDate},</if>
            <if test="pricingMethod != null and pricingMethod != ''">pricing_method = #{pricingMethod},</if>
            <if test="unitPrice != null">unit_price = #{unitPrice},</if>
            <if test="totalPrice != null">total_price = #{totalPrice},</if>
            <if test="totalWeight != null">total_weight = #{totalWeight},</if>
            <if test="avgWeight != null">avg_weight = #{avgWeight},</if>
            <if test="avgPricePerSheep != null">avg_price_per_sheep = #{avgPricePerSheep},</if>
            <if test="saleType != null and saleType != ''">sale_type = #{saleType},</if>
            <if test="diseaseType != null and diseaseType != ''">disease_type = #{diseaseType},</if>
            <if test="secondaryReason != null and secondaryReason != ''">secondary_reason = #{secondaryReason},</if>
            <if test="groupCode != null and groupCode != ''">group_code = #{groupCode},</if>
            <if test="customerId != null">customer_id = #{customerId},</if>
            <if test="salesPersonId != null">sales_person_id = #{salesPersonId},</if>
            <if test="quarantineNo != null and quarantineNo != ''">quarantine_no = #{quarantineNo},</if>
            <if test="approvalNo != null and approvalNo != ''">approval_no = #{approvalNo},</if>
            <if test="technicianId != null">technician_id = #{technicianId},</if>
            <if test="handlerId != null">handler_id = #{handlerId},</if>
            <if test="createdBy != null">created_by = #{createdBy},</if>
            <if test="createdAt != null">created_at = #{createdAt},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSxSheepSaleById" parameterType="Long">
        delete from sx_sheep_sale where id = #{id}
    </delete>

    <delete id="deleteSxSheepSaleByIds" parameterType="String">
        delete from sx_sheep_sale where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

</mapper>