package com.fs.his.mapper;

import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.fs.company.param.FsStoreStatisticsParam;
import com.fs.company.vo.FsStoreOrderStatisticsVO;
import com.fs.his.domain.FsStoreAfterSales;
import com.fs.his.domain.FsStoreAfterSalesItem;
import com.fs.his.domain.FsStoreAfterSalesLogs;
import com.fs.his.param.FsStoreAfterSalesListUParam;
import com.fs.his.param.FsStoreAfterSalesParam;
import com.fs.his.vo.*;
import lombok.Data;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 售后记录Mapper接口
 *
 * @author fs
 * @date 2023-06-13
 */
public interface FsStoreAfterSalesMapper
{
    /**
     * 查询售后记录
     *
     * @param id 售后记录主键
     * @return 售后记录
     */
    public FsStoreAfterSales selectFsStoreAfterSalesById(Long id);

    /**
     * 查询售后记录列表
     *
     * @param fsStoreAfterSales 售后记录
     * @return 售后记录集合
     */
    public List<FsStoreAfterSales> selectFsStoreAfterSalesList(FsStoreAfterSales fsStoreAfterSales);

    /**
     * 新增售后记录
     *
     * @param fsStoreAfterSales 售后记录
     * @return 结果
     */
    public int insertFsStoreAfterSales(FsStoreAfterSales fsStoreAfterSales);

    /**
     * 修改售后记录
     *
     * @param fsStoreAfterSales 售后记录
     * @return 结果
     */
    public int updateFsStoreAfterSales(FsStoreAfterSales fsStoreAfterSales);

    /**
     * 删除售后记录
     *
     * @param id 售后记录主键
     * @return 结果
     */
    public int deleteFsStoreAfterSalesById(Long id);

    /**
     * 批量删除售后记录
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteFsStoreAfterSalesByIds(Long[] ids);

    @Select({"<script> " +
            "select so.*,st.store_name,us.nick_name,us.phone,fso.order_code,fso.delivery_sn as delivery_id,c.company_name,cu.nick_name as company_user_nick_name,fpo.package_name FROM fs_store_after_sales so " +
            " LEFT JOIN fs_store st ON so.store_id =st.store_id " +
            " LEFT JOIN fs_user us ON us.user_id=so.user_id " +
            " LEFT JOIN fs_store_order fso ON fso.order_id=so.order_id " +
            " left join company c on so.company_id = c.company_id " +
            " left join fs_package_order fpo on fso.package_order_id = fpo.order_id " +
            " left join company_user cu on so.company_user_id = cu.user_id   WHERE so.is_del=0 "+
            "    <if test=\"maps.storeId != null \"> and so.store_id = #{maps.storeId}</if>\n" +
            "    <if test=\"maps.refundAmount != null \"> and so.refund_amount = #{maps.refundAmount}</if>\n" +
            "    <if test=\"maps.refundType != null \"> and so.refund_type = #{maps.refundType}</if>\n" +
            "    <if test=\"maps.reasons != null  and maps.reasons != ''\"> and so.reasons = #{maps.reasons}</if>\n" +
            "    <if test=\"maps.explains != null  and maps.explains != ''\"> and so.explains = #{maps.explains}</if>\n" +
            "    <if test=\"maps.deliveryCode != null  and maps.deliveryCode != ''\"> and so.delivery_code = #{maps.deliveryCode}</if>\n" +
            "    <if test=\"maps.deliverySn != null  and maps.deliverySn != ''\"> and so.delivery_sn = #{maps.deliverySn}</if>\n" +
            "    <if test=\"maps.deliveryName != null  and maps.deliveryName != ''\"> and so.delivery_name like concat('%', #{maps.deliveryName}, '%')</if>\n" +
            "    <if test=\"maps.status != null \"> and so.status = #{maps.status}</if>\n" +
            "    <if test=\"maps.salesStatus != null \"> and so.sales_status = #{maps.salesStatus}</if>\n" +
            "    <if test=\"maps.phone != null \"> and us.phone = #{maps.phone}</if>\n" +
            "    <if test=\"maps.moble != null \"> and fso.user_phone = #{maps.moble}</if>\n" +
            "    <if test=\"maps.orderStatus != null \"> and so.order_status = #{maps.orderStatus}</if>\n" +
            "    <if test=\"maps.isDel != null  and isDel != ''\"> and so.is_del = #{maps.isDel}</if>\n" +
            "    <if test=\"maps.userId != null \"> and so.user_id = #{maps.userId}</if>\n" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(so.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(so.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=  \"\" '> " +
            " and cu.nick_name like concat( #{maps.companyUserNickName}, '%') " +
            "</if>" +
            "    <if test=\"maps.consignee != null  and maps.consignee != ''\"> and so.consignee = #{maps.consignee}</if>\n" +
            "    <if test=\"maps.phoneNumber != null  and maps.phoneNumber != ''\"> and so.phone_number = #{maps.phoneNumber}</if>\n" +
            "    <if test=\"maps.address != null  and maps.address != ''\"> and so.address = #{maps.address}</if>\n" +
            "<if test = 'maps.companyId != null  and   maps.companyId != \"-1\" '> " +
            "and so.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = ' maps.companyId == \"-1\" '> " +
            "and so.company_id is null " +
            "</if>" +
            "    <if test=\"maps.companyUserId != null \"> and so.company_user_id = #{maps.companyUserId}</if>\n" +
            "    <if test=\"maps.isPackage != null \"> and so.is_package = #{maps.isPackage}</if>"+
            "    <if test=\"maps.orderCode != null \"> and fso.order_code= #{maps.orderCode}</if>"+
            "<if test = 'maps.deptId != null    '> " +
            "  AND (so.dept_id = #{maps.deptId} OR so.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            " order by so.create_time desc "+
            "</script>"})
    List<FsStoreAfterSalesListVO> selectFsStoreAfterSalesListVO(@Param("maps") FsStoreAfterSalesParam fsStoreAfterSales);

    @Select("select * FROM fs_store_after_sales_item WHERE after_sales_id =#{id}")
    List<FsStoreAfterSalesItem> selectFsStoreAfterSalesItemById(Long id);
    @Select("select a.*,c.company_name,s.store_name FROM fs_store_after_sales_logs a left join company c on c.company_id = a.company_id " +
            " left join fs_store s on s.store_id = a.store_id WHERE a.store_after_sales_id =#{id}")
    List<FsStoreAfterSalesLogsVO> selectFsStoreAfterSalesLogById(Long id);

    @Select(" SELECT order_id FROM fs_store_order WHERE order_code =#{id}")
    Long selectFsOrderIdByCode(Long id);

    @Select({"<script> " +
            "select so.*,st.store_name,us.nick_name,us.phone,fso.order_code,fso.delivery_sn as delivery_id,fpo.package_name ,c.company_name,cu.nick_name as company_user_nick_name FROM fs_store_after_sales so " +
            " LEFT JOIN fs_store st ON so.store_id =st.store_id " +
            " LEFT JOIN fs_user us ON us.user_id=so.user_id " +
            " LEFT JOIN fs_store_order fso ON fso.order_id=so.order_id  "+
            " left join company c on so.company_id = c.company_id " +
            " left join fs_package_order fpo on fso.package_order_id = fpo.order_id " +
            " left join company_user cu on so.company_user_id = cu.user_id   WHERE so.is_del=0 "+
            "    <if test=\"maps.storeId != null \"> and so.store_id = #{maps.storeId}</if>\n" +
            "    <if test=\"maps.refundAmount != null \"> and so.refund_amount = #{maps.refundAmount}</if>\n" +
            "    <if test=\"maps.refundType != null \"> and so.refund_type = #{maps.refundType}</if>\n" +
            "    <if test=\"maps.reasons != null  and maps.reasons != ''\"> and so.reasons = #{maps.reasons}</if>\n" +
            "    <if test=\"maps.explains != null  and maps.explains != ''\"> and so.explains = #{maps.explains}</if>\n" +
            "    <if test=\"maps.deliveryCode != null  and maps.deliveryCode != ''\"> and so.delivery_code = #{maps.deliveryCode}</if>\n" +
            "    <if test=\"maps.deliverySn != null  and maps.deliverySn != ''\"> and so.delivery_sn = #{maps.deliverySn}</if>\n" +
            "    <if test=\"maps.deliveryName != null  and maps.deliveryName != ''\"> and so.delivery_name like concat('%', #{maps.deliveryName}, '%')</if>\n" +
            "    <if test=\"maps.status != null \"> and so.status = #{maps.status}</if>\n" +
            "    <if test=\"maps.salesStatus != null \"> and so.sales_status = #{maps.salesStatus}</if>\n" +
            "    <if test=\"maps.phone != null \"> and us.phone = #{maps.phone}</if>\n" +
            "    <if test=\"maps.orderStatus != null \"> and so.order_status = #{maps.orderStatus}</if>\n" +
            "    <if test=\"maps.isDel != null  and isDel != ''\"> and so.is_del = #{maps.isDel}</if>\n" +
            "    <if test=\"maps.userId != null \"> and so.user_id = #{maps.userId}</if>\n" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(so.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(so.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=  \"\" '> " +
            " and cu.nick_name like concat(#{maps.companyUserNickName}, '%') " +
            "</if>" +
            "    <if test=\"maps.consignee != null  and maps.consignee != ''\"> and so.consignee = #{maps.consignee}</if>\n" +
            "    <if test=\"maps.phoneNumber != null  and maps.phoneNumber != ''\"> and so.phone_number = #{maps.phoneNumber}</if>\n" +
            "    <if test=\"maps.address != null  and maps.address != ''\"> and so.address = #{maps.address}</if>\n" +
            "<if test = 'maps.companyId != null  and   maps.companyId != \"-1\" '> " +
            "and so.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = ' maps.companyId == \"-1\" '> " +
            "and so.company_id is null " +
            "</if>" +
            "    <if test=\"maps.companyUserId != null \"> and so.company_user_id = #{maps.companyUserId}</if>\n" +
            "    <if test=\"maps.isPackage != null \"> and so.is_package = #{maps.isPackage}</if>"+
            "    <if test=\"maps.orderCode != null \"> and fso.order_code= #{maps.orderCode}</if>"+
            "<if test = 'maps.deptId != null    '> " +
            "  AND (so.dept_id = #{maps.deptId} OR so.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            " order by so.id desc "+
            "</script>"})
    List<FsStoreAfterSalesExcelVO> selectFsStoreAfterSalesExcelListVO(@Param("maps") FsStoreAfterSalesParam fsStoreAfterSales);
    @Select("select so.*,st.store_name,us.nick_name,us.phone,fso.order_code FROM fs_store_after_sales so LEFT JOIN fs_store st ON so.store_id =st.store_id LEFT JOIN fs_user us ON us.user_id=so.user_id LEFT JOIN fs_store_order fso ON fso.order_id=so.order_id WHERE so.id=#{id} ")
    FsStoreAfterSalesVO selectFsStoreAfterSalesVOById(Long id);
    @Select("select * from fs_store_after_sales WHERE  sales_status=0 and  status=0 and  order_status!=4  OR (order_status=4 AND create_time < DATE_SUB(NOW(), INTERVAL 24 HOUR) and  sales_status=0 and  status=0 )")
    List<FsStoreAfterSales> selectFsStoreAfterSalesByDoAudit();
    @Select({"<script> " +
            "select s.*,o.order_code  from fs_store_after_sales s left join fs_store_order o on o.order_id=s.order_id  " +
            "where s.is_del=0 " +
            "<if test = 'maps.status != null and maps.status ==1   '> " +
            "and s.sales_status = 0 " +
            "</if>" +
            "<if test = 'maps.status != null and maps.status ==2   '> " +
            "and s.sales_status = 3 " +
            "</if>" +
            "<if test = 'maps.userId != null'> " +
            "and s.user_id = #{maps.userId} " +
            "</if>" +
            "order by s.create_time desc "+
            "</script>"})
    List<FsStoreAfterSalesListUVO> selectFsStoreAfterSalesListUVO(@Param("maps") FsStoreAfterSalesListUParam param);
    @Select({"<script> " +
            "select count(1) FROM fs_store_after_sales so " +
            " LEFT JOIN fs_store st ON so.store_id =st.store_id " +
            " LEFT JOIN fs_user us ON us.user_id=so.user_id " +
            " LEFT JOIN fs_store_order fso ON fso.order_id=so.order_id  "+
            " left join company c on so.company_id = c.company_id " +
            " left join fs_package_order fpo on fso.package_order_id = fpo.order_id " +
            " left join company_user cu on so.company_user_id = cu.user_id   WHERE so.is_del=0 "+
            "    <if test=\"maps.storeId != null \"> and so.store_id = #{maps.storeId}</if>\n" +
            "    <if test=\"maps.refundAmount != null \"> and so.refund_amount = #{maps.refundAmount}</if>\n" +
            "    <if test=\"maps.refundType != null \"> and so.refund_type = #{maps.refundType}</if>\n" +
            "    <if test=\"maps.reasons != null  and maps.reasons != ''\"> and so.reasons = #{maps.reasons}</if>\n" +
            "    <if test=\"maps.explains != null  and maps.explains != ''\"> and so.explains = #{maps.explains}</if>\n" +
            "    <if test=\"maps.deliveryCode != null  and maps.deliveryCode != ''\"> and so.delivery_code = #{maps.deliveryCode}</if>\n" +
            "    <if test=\"maps.deliverySn != null  and maps.deliverySn != ''\"> and so.delivery_sn = #{maps.deliverySn}</if>\n" +
            "    <if test=\"maps.deliveryName != null  and maps.deliveryName != ''\"> and so.delivery_name like concat('%', #{maps.deliveryName}, '%')</if>\n" +
            "    <if test=\"maps.status != null \"> and so.status = #{maps.status}</if>\n" +
            "    <if test=\"maps.salesStatus != null \"> and so.sales_status = #{maps.salesStatus}</if>\n" +
            "    <if test=\"maps.phone != null \"> and us.phone = #{maps.phone}</if>\n" +
            "    <if test=\"maps.orderStatus != null \"> and so.order_status = #{maps.orderStatus}</if>\n" +
            "    <if test=\"maps.isDel != null  and isDel != ''\"> and so.is_del = #{maps.isDel}</if>\n" +
            "    <if test=\"maps.userId != null \"> and so.user_id = #{maps.userId}</if>\n" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(so.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(so.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=  \"\" '> " +
            " and cu.nick_name like concat( #{maps.companyUserNickName}, '%') " +
            "</if>" +
            "    <if test=\"maps.consignee != null  and maps.consignee != ''\"> and so.consignee = #{maps.consignee}</if>\n" +
            "    <if test=\"maps.phoneNumber != null  and maps.phoneNumber != ''\"> and so.phone_number = #{maps.phoneNumber}</if>\n" +
            "    <if test=\"maps.address != null  and maps.address != ''\"> and so.address = #{maps.address}</if>\n" +
            "<if test = 'maps.companyId != null  and   maps.companyId != \"-1\" '> " +
            "and so.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = ' maps.companyId == \"-1\" '> " +
            "and so.company_id is null " +
            "</if>" +
            "    <if test=\"maps.companyUserId != null \"> and so.company_user_id = #{maps.companyUserId}</if>\n" +
            "    <if test=\"maps.isPackage != null \"> and so.is_package = #{maps.isPackage}</if>"+
            "    <if test=\"maps.orderCode != null \"> and fso.order_code= #{maps.orderCode}</if>"+
            "<if test = 'maps.deptId != null    '> " +
            "  AND (so.dept_id = #{maps.deptId} OR so.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            " order by so.id desc "+
            "</script>"})
    Long selectFsStoreAfterSalesExcelListVOCount(@Param("maps") FsStoreAfterSalesParam fsStoreAfterSales);

    @Select({"<script> " +
            "select u.nick_name, count(o.order_id) as order_count,sum(IFNULL(o.refund_amount,0)) as pay_price  " +
            "from  fs_store_after_sales o " +
            "left join company_user u on o.company_user_id=u.user_id  " +
            "left join fs_store_order so on so.order_id=o.order_id  " +
            "where o.status &gt; 1 " +
            "and o.status = 4 and o.sales_status = 3" + //退款完成的订单
            "<if test = 'maps.type != null and maps.type ==1 '> " +
            "and TO_DAYS(o.create_time) = TO_DAYS(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==2 '> " +
            "and TO_DAYS(NOW()) - TO_DAYS(o.create_time) &lt;= 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==3 '> " +
            "and YEARWEEK(DATE_FORMAT(o.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==4 '> " +
            "and YEARWEEK(DATE_FORMAT(o.create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==5 '> " +
            "and DATE_FORMAT(o.create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==6 '> " +
            "and PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(o.create_time,'%Y%m')) = 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==7 '> " +
            "and QUARTER(o.create_time) = QUARTER(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==8 '> " +
            "and QUARTER(o.create_time) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==9 '> " +
            "and YEAR(o.create_time)=YEAR(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==10 '> " +
            "and  YEAR(o.create_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))" +
            "</if>" +
            "<if test = 'maps.startTime != null and maps.startTime != \"\" '> " +
            "AND date_format(so.create_time,'%y%m%d') &gt;= date_format(#{maps.startTime},'%y%m%d')"+
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "AND date_format(so.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "and  o.`company_user_id` IN " +
            "<foreach  item='item' index='index' collection='maps.users' open='(' separator=',' close=')'> #{item}    </foreach>"+
            " group by o.company_user_id "+
            "</script>"})
    List<FsStoreOrderStatisticsVO> selectFsPackageOrderStatisticsList(@Param("maps")FsStoreStatisticsParam param);

    List<JSONObject> selectFsStoreAfterSales(Map<String, Object> map);
}
