<?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.waybill.dao.WaybillMapper">

    <resultMap id="resultMap" type="com.cftech.waybill.model.Waybill">
        <id column="id" property="id"/>
        <result column="consult_id" property="consultId"/>
        <result column="order_id" property="orderId"/>
        <result column="number" property="number"/>
        <result column="member_id" property="memberId"/>
        <result column="open_id" property="openId"/>
        <result column="storage_manage" property="storageManage"/>
        <result column="waybill_no" property="waybillNo"/>
        <result column="language" property="language"/>
        <result column="monthly_card" property="monthlyCard"/>
        <result column="express_type_id" property="expressTypeId"/>
        <result column="temperature_range" property="temperatureRange"/>
        <result column="contact" property="contact"/>
        <result column="mobile" property="mobile"/>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="county" property="county"/>
        <result column="address" property="address"/>
        <result column="address_id" property="addressId"/>
        <result column="send_express_date" property="sendExpressDate"/>
        <result column="accept_express_date" property="acceptExpressDate"/>
        <result column="waybill_remark" property="waybillRemark"/>
        <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="route_des" property="routeDes"/>
        <result column="filter_result" property="filterResult"/>
        <result column="dest_code" property="destCode"/>
        <result column="origin_code" property="originCode"/>
    </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,
        consult_id,
        order_id,
        number,
        member_id,
        open_id,
        storage_manage,
        waybill_no,
        language,
        monthly_card,
        express_type_id,
        temperature_range,
        contact,
        mobile,
        province,
        city,
        county,
        address,
        address_id,
        send_express_date,
        accept_express_date,
        waybill_remark,
        accounts_id,
        del_flag,
        status,
        create_time,
        update_time,
        description,
        create_by,
        update_by,
        filter_result,
        dest_code,
        origin_code
    </sql>

    <sql id="insertSqlColumns">
        id,
        consult_id,
        order_id,
        number,
        member_id,
        open_id,
        storage_manage,
        waybill_no,
        language,
        monthly_card,
        express_type_id,
        temperature_range,
        contact,
        mobile,
        province,
        city,
        county,
        address,
        address_id,
        send_express_date,
        accept_express_date,
        waybill_remark,
        accounts_id,
        del_flag,
        status,
        create_time,
        update_time,
        description,
        create_by,
        update_by,
        route_des,
        filter_result,
        dest_code,
        origin_code
    </sql>


    <insert id="save" parameterType="com.cftech.waybill.model.Waybill" useGeneratedKeys="true"
            keyProperty="id">
        insert into t_aidea_waybill
        (
        <include refid="insertSqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{consultId, jdbcType=BIGINT},
        #{orderId, jdbcType=BIGINT},
        #{number, jdbcType=VARCHAR},
        #{memberId, jdbcType=VARCHAR},
        #{openId, jdbcType=VARCHAR},
        #{storageManage, jdbcType=VARCHAR},
        #{waybillNo, jdbcType=VARCHAR},
        #{language, jdbcType=VARCHAR},
        #{monthlyCard, jdbcType=VARCHAR},
        #{expressTypeId, jdbcType=VARCHAR},
        #{temperatureRange, jdbcType=VARCHAR},
        #{contact, jdbcType=VARCHAR},
        #{mobile, jdbcType=VARCHAR},
        #{province, jdbcType=VARCHAR},
        #{city, jdbcType=VARCHAR},
        #{county, jdbcType=VARCHAR},
        #{address, jdbcType=VARCHAR},
        #{addressId, jdbcType=VARCHAR},
        #{sendExpressDate, jdbcType=TIMESTAMP},
        #{acceptExpressDate, jdbcType=TIMESTAMP},
        #{waybillRemark, jdbcType=VARCHAR},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT},
        #{routeDes, jdbcType=VARCHAR},
        #{filterResult, jdbcType=VARCHAR},
        #{destCode, jdbcType=VARCHAR},
        #{originCode, jdbcType=VARCHAR}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultType="com.cftech.waybill.model.Waybill">
        SELECT
            t.id,
            t.consult_id consultId,
            t.order_id orderId,
            t.number,
            t.member_id memberId,
            t.open_id openId,
            t.storage_manage storageManage,
            t.waybill_no waybillNo,
            t.language,
            t.monthly_card monthlyCard,
            t.express_type_id expressTypeId,
            t.temperature_range temperatureRange,
            t.contact,
            t.mobile,
            t.province,
            t.city,
            t.county,
            t.address,
            t.address_id addressId,
            t.send_express_date sendExpressDate,
            t.accept_express_date acceptExpressDate,
            t.waybill_remark waybillRemark,
            t.accounts_id accountsId,
            t.del_flag delFlag,
            t.status,
            t.create_time createTime,
            t.update_time updateTime,
            t.description,
            t.create_by,
            t.update_by,
            o.number orderCode,
            od.drugs_num drugsNum,
            od.drugs_name drugsName,
            od.drugs_code drugsCode,
            od.price,
            AES_DECRYPT(f.nickname, 'aideakey') nickName,
            cs.consult_id consultSheetCode,
            qyu.name storageManageName,
            t.route_des routeDes,
            t.filter_result filterResult,
            t.dest_code destCode,
            t.origin_code originCode
        FROM t_aidea_waybill t
        LEFT JOIN t_aidea_consult_sheet cs ON cs.id = t.consult_id
        LEFT JOIN t_order o ON t.order_id = o.id
        LEFT JOIN t_order_details od ON o.id = od.order_id
        LEFT JOIN wx_mp_fanss f ON f.openid = t.open_id AND f.delflag = '0'
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON qyu.id = u.userid
        WHERE t.id = #{id}
    </select>

    <select id="fetchByWayBillId" parameterType="java.lang.Long" resultType="com.cftech.waybill.model.Waybill">
        SELECT
            t.id,
            t.consult_id consultId,
            t.order_id orderId,
            t.number,
            t.member_id memberId,
            t.open_id openId,
            t.storage_manage storageManage,
            t.waybill_no waybillNo,
            t.language,
            t.monthly_card monthlyCard,
            t.express_type_id expressTypeId,
            t.temperature_range temperatureRange,
            t.contact,
            t.mobile,
            t.province,
            t.city,
            t.county,
            t.address,
            t.address_id addressId,
            t.send_express_date sendExpressDate,
            t.accept_express_date acceptExpressDate,
            t.waybill_remark waybillRemark,
            t.accounts_id accountsId,
            t.del_flag delFlag,
            t.status,
            t.create_time createTime,
            t.update_time updateTime,
            t.description,
            t.create_by,
            t.update_by,
            o.number orderCode,
            od.drugs_num drugsNum,
            od.drugs_name drugsName,
            od.drugs_code drugsCode,
            od.price,
            AES_DECRYPT(f.nickname, 'aideakey') nickName,
            cs.consult_id consultSheetCode,
            CASE WHEN t.storage_manage = '1' THEN 'admin' ELSE qyu.name END storageManageName,
            t.route_des routeDes,
            t.filter_result filterResult,
            t.dest_code destCode,
            t.origin_code originCode
        FROM t_aidea_waybill t
        LEFT JOIN t_aidea_consult_sheet cs ON cs.id = t.consult_id
        LEFT JOIN t_order o ON t.order_id = o.id
        LEFT JOIN t_order_details od ON o.id = od.order_id
        LEFT JOIN wx_mp_fanss f ON f.openid = t.open_id AND f.delflag = '0'
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON qyu.id = u.userid
        WHERE t.id = #{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM t_aidea_waybill t
        LEFT JOIN t_aidea_consult_sheet cs ON cs.id = t.consult_id
        LEFT JOIN t_order o ON t.order_id = o.id
        LEFT JOIN t_order_details od ON o.id = od.order_id
        LEFT JOIN wx_mp_fanss f ON f.openid = t.open_id AND f.delflag = '0'
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON qyu.id = u.userid
        <include refid="sqlWhere"/>
    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultType="com.cftech.waybill.model.Waybill">
        SELECT
            t.id,
            t.consult_id consultId,
            t.order_id orderId,
            t.number,
            t.member_id memberId,
            t.open_id openId,
            t.storage_manage storageManage,
            t.waybill_no waybillNo,
            t.language,
            t.monthly_card monthlyCard,
            t.express_type_id expressTypeId,
            t.temperature_range temperatureRange,
            t.contact,
            t.mobile,
            t.province,
            t.city,
            t.county,
            t.address,
            t.address_id addressId,
            t.send_express_date sendExpressDate,
            t.accept_express_date acceptExpressDate,
            t.waybill_remark waybillRemark,
            t.accounts_id accountsId,
            t.del_flag delFlag,
            t.status,
            t.create_time createTime,
            t.update_time updateTime,
            t.description,
            t.create_by,
            t.update_by,
            o.number orderCode,
            od.drugs_num drugsNum,
            od.drugs_name drugsName,
            od.drugs_code drugsCode,
            od.price,
            AES_DECRYPT(f.nickname, 'aideakey') nickName,
            cs.consult_id consultSheetCode,
            qyu.name storageManageName,
            t.route_des routeDes,
            t.filter_result filterResult,
            t.dest_code destCode,
            t.origin_code originCode
        FROM t_aidea_waybill t
        LEFT JOIN t_aidea_consult_sheet cs ON cs.id = t.consult_id
        LEFT JOIN t_order o ON t.order_id = o.id
        LEFT JOIN t_order_details od ON o.id = od.order_id
        LEFT JOIN wx_mp_fanss f ON f.openid = t.open_id AND f.delflag = '0'
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON qyu.id = u.userid
        <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.waybill.model.Waybill">
        update t_aidea_waybill
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="consultId != null">
                consult_id = #{consultId, jdbcType=BIGINT},
            </if>
            <if test="orderId != null">
                order_id = #{orderId, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="memberId != null">
                member_id = #{memberId, jdbcType=VARCHAR},
            </if>
            <if test="openId != null">
                open_id = #{openId, jdbcType=VARCHAR},
            </if>
            <if test="storageManage != null">
                storage_manage = #{storageManage, jdbcType=VARCHAR},
            </if>
            <if test="waybillNo != null">
                waybill_no = #{waybillNo, jdbcType=VARCHAR},
            </if>
            <if test="language != null">
                language = #{language, jdbcType=VARCHAR},
            </if>
            <if test="monthlyCard != null">
                monthly_card = #{monthlyCard, jdbcType=VARCHAR},
            </if>
            <if test="expressTypeId != null">
                express_type_id = #{expressTypeId, jdbcType=VARCHAR},
            </if>
            <if test="temperatureRange != null">
                temperature_range = #{temperatureRange, jdbcType=VARCHAR},
            </if>
            <if test="contact != null">
                contact = #{contact, jdbcType=VARCHAR},
            </if>
            <if test="mobile != null">
                mobile = #{mobile, jdbcType=VARCHAR},
            </if>
            <if test="province != null">
                province = #{province, jdbcType=VARCHAR},
            </if>
            <if test="city != null">
                city = #{city, jdbcType=VARCHAR},
            </if>
            <if test="county != null">
                county = #{county, jdbcType=VARCHAR},
            </if>
            <if test="address != null">
                address = #{address, jdbcType=VARCHAR},
            </if>
            <if test="addressId != null">
                address_id = #{addressId, jdbcType=VARCHAR},
            </if>
            <if test="sendExpressDate != null">
                send_express_date = #{sendExpressDate, jdbcType=TIMESTAMP},
            </if>
            <if test="acceptExpressDate != null">
                accept_express_date = #{acceptExpressDate, jdbcType=TIMESTAMP},
            </if>
            <if test="waybillRemark != null">
                waybill_remark = #{waybillRemark, 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="routeDes != null">
                route_des = #{routeDes, jdbcType=BIGINT},
            </if>
            <if test="filterResult != null">
                filter_result = #{filterResult, jdbcType=VARCHAR},
            </if>
            <if test="originCode != null">
                origin_code = #{originCode, jdbcType=VARCHAR},
            </if>
            <if test="destCode != null">
                dest_code = #{destCode, jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id, jdbcType=BIGINT}
    </update>

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