<?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.order.dao.OrderMapper">

    <resultMap id="resultMap" type="com.cftech.order.model.Order">
        <id column="id" property="id"/>
        <result column="consult_id" property="consultId"/>
        <result column="number" property="number"/>
        <result column="member_id" property="memberId"/>
        <result column="service_id" property="serviceId"/>
        <result column="doctor_id" property="doctorId"/>
        <result column="openid" property="openid"/>
        <result column="pay_status" property="payStatus"/>
        <result column="pay_amount" property="payAmount"/>
        <result column="order_amount" property="orderAmount"/>
        <result column="total_amount" property="totalAmount"/>
        <result column="pay_time" property="payTime"/>
        <result column="trade_no" property="tradeNo"/>
        <result column="remarks" property="remarks"/>
        <result column="order_cancel" property="orderCancel"/>
        <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="confirm" property="confirm"/>
        <result column="drugs_num" property="drugsNum"/>
        <result column="product_number" property="productNumber"/>
        <result column="product_name" property="productName"/>
        <result column="courier_number" property="courierNumber"/>
        <result column="address_id" property="addressId"/>
        <result column="clerk_id" property="clerkId"/>
        <result column="address_des" property="addressDes"/>
    </resultMap>

    <resultMap id="resultMapList" type="com.cftech.order.model.Order">
        <id column="id" property="id"/>
        <result column="consult_id" property="consultId"/>
        <result column="openid" property="openid"/>
        <result column="number" property="number"/>
        <result column="member_id" property="memberId"/>
        <result column="service_id" property="serviceId"/>
        <result column="doctor_id" property="doctorId"/>
        <result column="openid" property="openid"/>
        <result column="pay_status" property="payStatus"/>
        <result column="pay_amount" property="payAmount"/>
        <result column="order_amount" property="orderAmount"/>
        <result column="total_amount" property="totalAmount"/>
        <result column="pay_time" property="payTime"/>
        <result column="trade_no" property="tradeNo"/>
        <result column="remarks" property="remarks"/>
        <result column="order_cancel" property="orderCancel"/>
        <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="confirm" property="confirm"/>
        <result column="drugs_num" property="drugsNum"/>
        <result column="product_number" property="productNumber"/>
        <result column="product_name" property="productName"/>
        <result column="address_id" property="addressId"/>
        <result column="courier_number" property="courierNumber"/>
        <result column="address_des" property="addressDes"/>
        <result column="consultCode" property="consultCode"/>
        <result column="nickName" property="nickName"/>
        <collection property="list" ofType="com.cftech.order.model.ProductVO">
            <result column="product_name" property="productName"/>
        </collection>
    </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">
        o.id,
        o.openid,
        t.product_number,
        o.consult_id,
        t.product_name,
        o.number,
        o.pay_amount,
        o.order_amount,
        o.total_amount,
        o.pay_status,
        o.order_cancel,
        o.trade_no,
        o.description,
        o.STATUS,
        o.create_time,
        o.update_time,
        o.confirm,
        d.drugs_num,
        o.courier_number,
        o.address_des,
        t.id as productId
    </sql>

    <sql id="sqlColumnFrom">
        o.id,
        o.consult_id AS consultId,
        o.number,
        o.pay_amount AS payAmount,
        o.order_amount AS orderAmount,
        o.pay_status AS payStatus,
        o.order_cancel AS orderCancel,
        o.trade_no AS tradeNo,
        o.description,
        o.STATUS,
        o.create_time AS createTime,
        o.confirm,
        o.courier_number AS courierNumber,
        o.address_des
    </sql>

    <sql id="sqlColumnProduct">
        d.id,
        d.order_id,
        t.product_number AS productNumber,
        t.product_name AS productName,
        t.id AS productId,
        d.drugs_num AS drugsNum,
        d.price,
        d.amount
     </sql>

    <sql id="Column">
        order_id,
        order_code,
        drugs_id,
        drugs_num,
        openid,
        price,
        amount,
        drugs_code,
        drugs_sku,
        accounts_id
    </sql>


    <insert id="save" parameterType="com.cftech.order.model.Order" useGeneratedKeys="true"
            keyProperty="id">
        insert into t_order
        (
            `id`,
            `number`,
            `openid`,
            `pay_status`,
            `pay_amount`,
            `order_amount`,
            `total_amount`,
            `pay_time`,
            `trade_no`,
            `courier_number`,
            `confirm`,
            `order_cancel`,
            `remarks`,
            `accounts_id`,
            `del_flag`,
            `status`,
            `create_time`,
            `update_time`,
            `description`,
            `create_by`,
            `update_by`,
            `consult_id`,
            `address_id`,
            `member_id`,
            `service_id`,
            `doctor_id`,
            `clerk_id`,
            address_des
        )
        values
        (
            #{id, jdbcType=BIGINT},
            #{number, jdbcType=VARCHAR},
            #{openid, jdbcType=VARCHAR},
            #{payStatus, jdbcType=BIGINT},
            #{payAmount, jdbcType=DECIMAL},
            #{orderAmount, jdbcType=DECIMAL},
            #{totalAmount, jdbcType=DECIMAL},
            now(),
            #{tradeNo, jdbcType=VARCHAR},
            #{courierNumber, jdbcType=VARCHAR},
            #{confirm, jdbcType=VARCHAR},
            #{orderCancel, jdbcType=VARCHAR},
            #{remarks, jdbcType=VARCHAR},
            #{accountsId, jdbcType=BIGINT},
            #{delFlag, jdbcType=TINYINT},
            #{status, jdbcType=VARCHAR},
            now(),
            now(),
            #{description, jdbcType=VARCHAR},
            #{createBy, jdbcType=BIGINT},
            #{updateBy, jdbcType=BIGINT},
            #{consultId, jdbcType=BIGINT},
            #{addressId, jdbcType=BIGINT},
            #{memberId, jdbcType=BIGINT},
            #{serviceId, jdbcType=BIGINT},
            #{doctorId, jdbcType=BIGINT},
            #{clerkId, jdbcType=BIGINT},
            #{addressDes, jdbcType=VARCHAR}
        )
    </insert>
    <update id="deleteAll" >
          update t_order_details set del_flag=1 where id=#{id}
    </update>


    <select id="fetchId" parameterType="java.lang.Long" resultType="com.cftech.order.model.OrderFromVO">
        SELECT
        <include refid="sqlColumnFrom"/>
        from
        t_order o
        LEFT JOIN t_shipping_address s ON o.address_id = s.id
        LEFT JOIN area a ON s.province_id = a.areaid
        LEFT JOIN area b ON s.city_id = b.areaid
        LEFT JOIN area c ON s.area_id = c.areaid
        WHERE o.id=#{id}
    </select>

    <select id="fetchProductId" parameterType="java.lang.Long" resultType="com.cftech.order.model.OrderFromVO">
        SELECT
        <include refid="sqlColumnProduct"/>
        from
        t_order_details d
        LEFT JOIN t_order o ON d.order_id =o.id
        LEFT JOIN t_aidea_product t ON d.drugs_id = t.id
        WHERE
        d.order_id =#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT IFNULL(SUM(col), 0)
        FROM (
            SELECT COUNT(1) col
            FROM t_order o
            LEFT JOIN t_aidea_consult_sheet cs ON o.consult_id = cs.id
            LEFT JOIN t_shipping_address s ON o.address_id = s.id
            LEFT JOIN t_order_details d ON o.id = d.order_id
            LEFT JOIN t_aidea_product t ON d.drugs_id = t.id
            <include refid="sqlWhere"/>
            <if test="userid != null"> AND (o.service_id = ${userid} or o.doctor_id =${userid} or o.clerk_id = ${userid}) </if>
            GROUP BY o.id ) duals
    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMapList">
        SELECT
        <include refid="sqlColumns"/>, cs.consult_id consultCode, CONVERT(AES_DECRYPT(f.nickname,'aideakey') USING UTF8) nickName
        FROM t_order o
        LEFT JOIN t_aidea_consult_sheet cs ON o.consult_id = cs.id
        LEFT JOIN t_shipping_address s ON o.address_id = s.id
        LEFT JOIN t_order_details d ON o.id = d.order_id
        LEFT JOIN t_aidea_product t ON d.drugs_id = t.id
        LEFT JOIN wx_mp_fanss f ON f.openid = o.openid
        <include refid="sqlWhere"/>
        <if test="userid != null"> AND (o.service_id = ${userid} or o.doctor_id =${userid} or o.clerk_id = ${userid}) </if>
        GROUP BY o.id
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="listProduct" resultType="com.cftech.order.model.ProductOrder">
        SELECT id, product_name as productName,price FROM t_aidea_product where del_flag = 0
    </select>

    <update id="update" parameterType="com.cftech.order.model.Order">
        update t_order
        <set>
            <if test="id != null">
                id = #{id, jdbcType=BIGINT},
            </if>
            <if test="consultId != null">
                consult_id = #{consultId, jdbcType=BIGINT},
            </if>
            <if test="number != null">
                number = #{number, jdbcType=VARCHAR},
            </if>
            <if test="memberId != null">
                member_id = #{memberId, jdbcType=BIGINT},
            </if>
            <if test="serviceId != null">
                service_id = #{serviceId, jdbcType=BIGINT},
            </if>
            <if test="doctorId != null">
                doctor_id = #{doctorId, jdbcType=BIGINT},
            </if>
            <if test="openid != null">
                openid = #{openid, jdbcType=VARCHAR},
            </if>
            <if test="payStatus != null">
                pay_status = #{payStatus, jdbcType=BIGINT},
            </if>
            <if test="payAmount != null">
                pay_amount = #{payAmount, jdbcType=DECIMAL},
            </if>
            <if test="orderAmount != null">
                order_amount = #{orderAmount, jdbcType=DECIMAL},
            </if>
            <if test="totalAmount != null">
                total_amount = #{totalAmount, jdbcType=DECIMAL},
            </if>
            <if test="payTime != null">
                pay_time = #{payTime, jdbcType=TIMESTAMP},
            </if>
            <if test="tradeNo != null">
                trade_no = #{tradeNo, jdbcType=VARCHAR},
            </if>
            <if test="courierNumber != null">
                courier_number = #{courierNumber, jdbcType=VARCHAR},
            </if>
            <if test="confirm != null">
                confirm = #{confirm, jdbcType=VARCHAR},
            </if>
            <if test="remarks != null">
                remarks = #{remarks, jdbcType=VARCHAR},
            </if>
            <if test="orderCancel != null">
                order_cancel = #{orderCancel, 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="consultId != null">
                consult_id = #{consultId, jdbcType=BIGINT},
            </if>
            <if test="addressId != null">
                address_id = #{addressId, jdbcType=BIGINT},
            </if>
            <if test="memberId != null">
                member_id = #{memberId, jdbcType=BIGINT},
            </if>
            <if test="serviceId != null">
                service_id = #{serviceId, jdbcType=BIGINT},
            </if>
            <if test="doctorId != null">
                doctor_id = #{doctorId, jdbcType=BIGINT},
            </if>
            <if test="clerkId != null">
                clerk_id = #{clerkId, jdbcType=BIGINT},
            </if>
            <if test="addressDes != null">
                address_des = #{addressDes, jdbcType=VARCHAR}
            </if>
        </set>
        where id=#{id,jdbcType=BIGINT}
    </update>

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

    <select id="fetchProduct" resultType="com.cftech.order.model.ProductDto" parameterType="java.util.Map">
       SELECT * FROM t_aidea_product WHERE  id = #{productId} AND accounts_id=#{accountsId} AND del_flag = 0
    </select>

    <update id="updateDetill" parameterType="java.util.Map">
        update t_order_details
        <set>
            <if test="productId != null">
                drugs_id = #{productId},
            </if>
            <if test="drugsNum != null">
                drugs_num = #{drugsNum},
            </if>
            <if test="price != null">
                price = #{price},
            </if>
            <if test="amount != null">
                amount = #{amount}
            </if>

        </set>
        where order_id =#{id} and id = #{idDetail}
    </update>

    <select id="fetcheDeta" parameterType="java.util.Map" resultType="java.lang.Integer">
        select *  from t_order_details where order_id =#{id} and  accounts_id=#{accountsId} and drugs_id =#{productId}  and del_flag = 0
    </select>

    <insert id="saveDetill" parameterType="com.cftech.order.model.OrderDetailDto">
        insert into t_order_details
        (
        <include refid="Column"/>
        )
        values
        (
        #{orderId},
        #{orderCode},
        #{drugsId},
        #{drugsNum},
        #{openid},
        #{price},
        #{amount},
        #{drugsCode},
        #{drugsSku},
        #{accountsId}
        )
    </insert>

    <select id="fetchOrder" resultType="com.cftech.order.model.Order" parameterType="java.lang.Long">
        select  * from t_order where id= #{id} and del_flag = 0
    </select>

    <update id="updateOrder" parameterType="java.util.Map">
        update t_order
        <set>
            <if test="orderAmount!=null">
                order_amount = #{orderAmount}
            </if>
        </set>
        where id= #{id}
    </update>

    <update id="updateStatus" parameterType="java.util.Map">
        update t_order
        <set>
            <if test="id!=null">
                status = #{status}
            </if>
        </set>
        where id= #{id}
    </update>

    <select id="orderFall" resultType="com.cftech.order.model.OrderMobile">
        SELECT
            t.id,
            t.number,
            t.pay_status AS payStatus,
            t.total_amount AS totalAmount,
            t.pay_time AS payTime,
            t.trade_no AS tradeNo,
            t.courier_number AS courierNumber,
            t.order_cancel AS orderCancel,
            t.status,
            DATE_FORMAT(t.create_time,'%Y-%m-%d %h:%i:%s')  AS createTime,
            s.address_name AS addressName,
            a.areaname AS province,
            b.areaname AS city,
            c.areaname AS area,
            s.phone,
            s.address
        FROM
          t_order t
        LEFT JOIN t_order_details d  ON  t.id =d.order_id
        LEFT JOIN t_aidea_product p  ON  d.drugs_id = p.id
        LEFT JOIN t_shipping_address s ON t.address_id = s.id
        LEFT JOIN area a ON s.province_id = a.areaid
        LEFT JOIN area b ON s.city_id = b.areaid
        LEFT JOIN area c ON s.area_id = c.areaid
        WHERE t.del_flag=0
        <if test="id != null">
            AND t.id = #{id}
        </if>
        <if test="openId != null">
            AND t.openid = #{openId}
        </if>
        <if test="value != null and value != ''">
            AND (t.number LIKE '%${value}%' OR p.product_name LIKE '%${value}%')
        </if>
        <choose>
            <when test="status != null and status == 1 ">
                AND (t.status = 0 OR t.status = 1)
            </when>
            <when test="status != null and status == 3 ">
                AND (t.status = 2 OR t.status = 3)
            </when>
            <when test="status != null ">
                AND t.status = #{status}
            </when>
            <otherwise></otherwise>
        </choose>
        GROUP BY t.id
        ORDER BY t.create_time DESC
    </select>

    <select id="orderDetill" resultType="com.cftech.order.model.ProductMobile">
        SELECT
            p.product_name AS productName,
            t.amount,
            p.is_rs AS isrs,
            p.product_img AS productImg,
            p.format
            FROM t_order_details t LEFT JOIN t_aidea_product p ON t.drugs_id = p.id
        WHERE t.del_flag = 0
        <if test="orderId!=null">
            and t.order_id =#{orderId}
        </if>
    </select>

    <select id="orderSize" resultType="com.cftech.order.model.OrderSizeVO" >
        SELECT
            SUM(status = 0 OR status = 1) AS waitPay,
            SUM(status = 2 OR status = 3) AS waitExpress,
            SUM(status=4) AS completed,
            SUM(status=5) AS cancelled
        FROM t_order WHERE del_flag = 0 AND openid = #{openid}

    </select>

    <select id="orderList" resultType="com.cftech.order.model.OrderMobile" parameterType="com.cftech.order.model.OrderMobileDto">
        SELECT
        t.id,
        t.number,
        t.pay_status AS payStatus,
        t.total_amount AS totalAmount,
        t.pay_time AS payTime,
        t.trade_no AS tradeNo,
        t.courier_number AS courierNumber,
        t.order_cancel AS orderCancel,
        t.status,
        t.create_time AS createTime,
        s.address_name AS addressName,
        a.areaname AS province,
        b.areaname AS city,
        c.areaname AS area,
        s.phone,
        s.address
        FROM
        t_order t LEFT JOIN t_shipping_address s ON t.openid = s.open_id AND s.whether = 1
        LEFT JOIN t_order_details d  ON  t.id =d.order_id
        LEFT JOIN t_aidea_product p  ON  d.drugs_id = p.id
        LEFT JOIN area a ON s.province_id = a.areaid
        LEFT JOIN area b ON s.city_id = b.areaid
        LEFT JOIN area c ON s.area_id = c.areaid
        WHERE t.del_flag=0
        <if test="openId !=null and openId!= ''">
            AND t.openid =#{openId}
        </if>

        <if test="searchName!=null and searchName !=''">
            AND CONCAT(IFNULL(`t.number`,''), IFNULL(`p.product_name`,'')) LIKE '%${searchName}%'
        </if>
        <if test="status!=null ">
            AND t.status = #{status}
        </if>
        ORDER BY t.create_time DESC
        <if test="page!=null and pageSize !='' and pageSize!=null and page !=''">
        limit #{page},#{pageSize}
        </if>
    </select>

</mapper>