<?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"/>
    </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,
        CONVERT( AES_DECRYPT(t.contact, 'aideakey') USING UTF8) contact,
        CONVERT( AES_DECRYPT(t.mobile, 'aideakey') USING UTF8) mobile,
        CONVERT( AES_DECRYPT(t.province, 'aideakey') USING UTF8) province,
        CONVERT( AES_DECRYPT(t.city, 'aideakey') USING UTF8) city,
        CONVERT( AES_DECRYPT(t.county, 'aideakey') USING UTF8) county,
        CONVERT( AES_DECRYPT(t.address, 'aideakey') USING UTF8) 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
    </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
    </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},
        AES_ENCRYPT(#{contact, jdbcType=VARBINARY}, 'aideakey'),
        AES_ENCRYPT(#{mobile, jdbcType=VARBINARY}, 'aideakey'),
        AES_ENCRYPT(#{province, jdbcType=VARBINARY}, 'aideakey'),
        AES_ENCRYPT(#{city, jdbcType=VARBINARY}, 'aideakey'),
        AES_ENCRYPT(#{county, jdbcType=VARBINARY}, 'aideakey'),
        AES_ENCRYPT(#{address, jdbcType=VARBINARY}, 'aideakey'),
        #{addressId, jdbcType=VARCHAR},
        now(),
        now(),
        #{waybillRemark, jdbcType=VARCHAR},
        #{accountsId, jdbcType=BIGINT},
        #{delFlag, jdbcType=TINYINT},
        #{status, jdbcType=VARCHAR},
        now(),
        now(),
        #{description, jdbcType=VARCHAR},
        #{createBy, jdbcType=BIGINT},
        #{updateBy, jdbcType=BIGINT}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM t_aidea_waybill t
        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,
            CONVERT( AES_DECRYPT(t.contact, 'aideakey') USING UTF8) contact,
            CONVERT( AES_DECRYPT(t.mobile, 'aideakey') USING UTF8) mobile,
            CONVERT( AES_DECRYPT(t.province, 'aideakey') USING UTF8) province,
            CONVERT( AES_DECRYPT(t.city, 'aideakey') USING UTF8) city,
            CONVERT( AES_DECRYPT(t.county, 'aideakey') USING UTF8) county,
            CONVERT( AES_DECRYPT(t.address, 'aideakey') USING UTF8) address,
            t.address_id addressId,
            DATE_FORMAT(t.send_express_date, '%Y-%m-%d %H:%i') sendExpressDateStr,
            DATE_FORMAT(t.accept_express_date, '%Y-%m-%d %H:%i') acceptExpressDateStr,
            t.waybill_remark waybillRemark,
            t.accounts_id accountsId,
            t.del_flag delFlag,
            t.status,
            t.create_time createTimeStr,
            t.update_time updateTimeStr,
            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(m.name, 'aideakey') memberName,
            cs.consult_id consultSheetCode,
            qyu.name storageManageName
        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_member m ON t.member_id = m.id
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON u.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
        <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,
            CONVERT( AES_DECRYPT(t.contact, 'aideakey') USING UTF8) contact,
            CONVERT( AES_DECRYPT(t.mobile, 'aideakey') USING UTF8) mobile,
            CONVERT( AES_DECRYPT(t.province, 'aideakey') USING UTF8) province,
            CONVERT( AES_DECRYPT(t.city, 'aideakey') USING UTF8) city,
            CONVERT( AES_DECRYPT(t.county, 'aideakey') USING UTF8) county,
            CONVERT( AES_DECRYPT(t.address, 'aideakey') USING UTF8) address,
            t.address_id addressId,
            DATE_FORMAT(t.send_express_date, '%Y-%m-%d %H:%i') sendExpressDateStr,
            DATE_FORMAT(t.accept_express_date, '%Y-%m-%d %H:%i') acceptExpressDateStr,
            t.waybill_remark waybillRemark,
            t.accounts_id accountsId,
            t.del_flag delFlag,
            t.status,
            DATE_FORMAT(t.create_time, '%Y-%m-%d %H:%i') createTimeStr,
            DATE_FORMAT(t.update_time, '%Y-%m-%d %H:%i') updateTimeStr,
            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(m.name, 'aideakey') memberName,
            cs.consult_id consultSheetCode,
            qyu.name storageManageName
        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_member m ON t.member_id = m.id
        LEFT JOIN user u ON u.id = t.storage_manage
        LEFT JOIN t_qyuser qyu ON u.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 = AES_ENCRYPT(#{contact, jdbcType=VARBINARY}, 'aideakey'),
            </if>
            <if test="mobile != null">
                mobile = AES_ENCRYPT(#{mobile, jdbcType=VARBINARY}, 'aideakey'),
            </if>
            <if test="province != null">
                province = AES_ENCRYPT(#{province, jdbcType=VARBINARY}, 'aideakey'),
            </if>
            <if test="city != null">
                city = AES_ENCRYPT(#{city, jdbcType=VARBINARY}, 'aideakey'),
            </if>
            <if test="county != null">
                county = AES_ENCRYPT(#{county, jdbcType=VARBINARY}, 'aideakey'),
            </if>
            <if test="address != null">
                address = AES_ENCRYPT(#{address, jdbcType=VARBINARY}, 'aideakey'),
            </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>
        </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>