package com.fs.company.mapper;

import java.math.BigDecimal;
import java.util.List;
import com.fs.company.domain.CompanyMoneyLogs;
import com.fs.company.domain.CompanyRedPackage;
import com.fs.company.param.CompanyMoneyLogsParam;
import com.fs.company.param.CompanyStoreOrderMoneyLogsListParam;
import com.fs.company.vo.*;
import com.fs.his.param.FsCompanyMoneyLogsExportParam;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 企业账户记录Mapper接口
 *
 * @author fs
 * @date 2021-10-04
 */
public interface CompanyMoneyLogsMapper
{
    /**
     * 查询企业账户记录
     *
     * @param logsId 企业账户记录ID
     * @return 企业账户记录
     */
    public CompanyMoneyLogs selectCompanyMoneyLogsById(Long logsId);

    /**
     * 查询企业账户记录列表
     *
     * @param companyMoneyLogs 企业账户记录
     * @return 企业账户记录集合
     */
    public List<CompanyMoneyLogs> selectCompanyMoneyLogsList(CompanyMoneyLogs companyMoneyLogs);

    /**
     * 新增企业账户记录
     *
     * @param companyMoneyLogs 企业账户记录
     * @return 结果
     */
    public int insertCompanyMoneyLogs(CompanyMoneyLogs companyMoneyLogs);

    /**
     * 修改企业账户记录
     *
     * @param companyMoneyLogs 企业账户记录
     * @return 结果
     */
    public int updateCompanyMoneyLogs(CompanyMoneyLogs companyMoneyLogs);

    /**
     * 删除企业账户记录
     *
     * @param logsId 企业账户记录ID
     * @return 结果
     */
    public int deleteCompanyMoneyLogsById(Long logsId);

    /**
     * 批量删除企业账户记录
     *
     * @param logsIds 需要删除的数据ID
     * @return 结果
     */
    public int deleteCompanyMoneyLogsByIds(Long[] logsIds);

    @Select({"<script> " +
            "select l.*,c.company_name " +
            "from company_money_logs l left join company c on c.company_id=l.company_id   " +
            "where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.businessId != null  '> " +
            "and l.business_id = #{maps.businessId}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})

    List<CompanyMoneyLogsVO> selectCompanyMoneyLogsVOList(@Param("maps") CompanyMoneyLogsParam companyMoneyLogs);


    @Select({"<script> " +
            "select l.*,c.company_name, " +
            "CASE WHEN l.type = 0 THEN o.order_code WHEN l.type = 1 THEN lo.order_code ELSE NULL END as order_code, " +
            "CASE WHEN l.type = 0 THEN o.user_name WHEN l.type = 1 THEN lo.user_name ELSE NULL END as real_name, " +
            "CASE WHEN l.type = 0 THEN o.user_phone WHEN l.type = 1 THEN lo.user_phone ELSE NULL END as user_phone, " +
            "p.bank_transaction_id,p.pay_type_code,p.pay_code " +
            "from company_money_logs l left join company c on c.company_id=l.company_id   " +
            "left join fs_store_order_scrm o on (o.id=CAST(l.business_id AS UNSIGNED) and l.type=0) " +
            "left join live_order lo on (lo.order_id=CAST(l.business_id AS UNSIGNED) and l.type=1) " +
            "left join fs_store_payment_scrm p on (o.order_code=p.business_code and p.`status`=1 and l.type=0) "+
            "where  (l.logs_type=3 || l.logs_type=4 || l.logs_type=5 || l.logs_type=6 || l.logs_type=13 || l.logs_type=14)  " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.businessId != null  '> " +
            "and l.business_id = #{maps.businessId}" +
            "</if>" +
            "<if test = 'maps.type != null  '> " +
            "and l.type = #{maps.type}" +
            "</if>" +
            "<if test = 'maps.orderCode != null and maps.orderCode != \"\"  '> " +
            "and ((l.type=0 and o.order_code = #{maps.orderCode}) or (l.type=1 and lo.order_code = #{maps.orderCode}))" +
            "</if>" +
            "<if test = 'maps.tradeCode != null and maps.tradeCode != \"\"  '> " +
            "and p.bank_transaction_id = #{maps.tradeCode}" +
            "</if>" +
            "<if test = 'maps.payCode != null and maps.payCode != \"\"  '> " +
            "and p.pay_code = #{maps.payCode}" +
            "</if>" +
            "<if test = 'maps.userPhone != null and maps.userPhone != \"\"  '> " +
            "and ((l.type=0 and o.user_phone = #{maps.userPhone}) or (l.type=1 and lo.user_phone = #{maps.userPhone}))" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})
    List<CompanyMoneyLogsVO> selectCompanyMoneyLogsMallVOList(@Param("maps")  CompanyMoneyLogsParam companyMoneyLogs);

    @Select({"<script> " +
            "select l.*,c.company_name ,p.bank_transaction_id,p.pay_type_code " +
            "from company_money_logs l left join company c on c.company_id=l.company_id   " +
            "left join fs_store_payment p on p.payment_id=l.business_id "+
            "where  (l.logs_type=8 || l.logs_type=9)  " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.businessId != null  '> " +
            "and l.business_id = #{maps.businessId}" +
            "</if>" +

            "<if test = 'maps.payCode != null and maps.payCode != \"\"  '> " +
            "and p.pay_code = #{maps.payCode}" +
            "</if>" +
            "<if test = 'maps.tradeCode != null and maps.tradeCode != \"\"  '> " +
            "and p.bank_transaction_id = #{maps.tradeCode}" +
            "</if>" +

            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc limit 30000 " +
            "</script>"})
    List<CompanyMoneyLogsVO> selectCompanyMoneyLogsMallVO1List(@Param("maps") CompanyMoneyLogsParam companyMoneyLogs);




    @Select({"<script> " +
            "select ifnull(sum(l.money),0) " +
            "from company_money_logs l left join company c on c.company_id=l.company_id " +
            "where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "</script>"})
    BigDecimal selectCompanyTotalMoneys(@Param("maps")CompanyMoneyLogsParam companyMoneyLogs);

    @Select("select * from company_money_logs where business_id=#{id} and logs_type=#{type} limit 1  ")
    CompanyMoneyLogs selectCompanyMoneyLogsByOrderId(@Param("id") Long id,@Param("type")Integer type);

    @Delete("delete from company_money_logs where company_id=#{companyId}")
    int deleteCompanyMoneyLogsByCompanyId(Long companyId);

//    @Select({"<script> " +
//            "select l.*,c.company_name from company_money_logs l left join company c on c.company_id=l.company_id" +
//            " where 1=1 " +
//            "<if test = 'maps.companyId != null  '> " +
//            "and l.company_id = #{maps.companyId}" +
//            "</if>" +
//            "<if test = 'maps.logsType != null  '> " +
//            "and l.logs_type = #{maps.logsType}" +
//            "</if>" +
//            "<if test = 'maps.createTimeList != null    '> " +
//            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
//            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
//            "</if>" +
//            "order by l.logs_id desc " +
//            "</script>"})
    @Select({"<script> " +
            "select l.*," +
            "CASE WHEN cu.nick_name IS NOT NULL AND cd.dept_name IS NOT NULL THEN CONCAT(c.company_name, '-', cu.nick_name, '-', cd.dept_name) " +
            "WHEN cu.nick_name IS NOT NULL THEN CONCAT(c.company_name, '-', cu.nick_name) " +
            "ELSE c.company_name END as company_name," +
            "o.order_code,s.store_name,o.delivery_sn as delivery_id,p.pay_code,p.pay_type_code,o.package_name,o.package_second_name,io.order_sn,o.user_name,cu.nick_name as company_user_name,cd.dept_name," +
            "CONCAT_WS('-', cu.nick_name, cd.dept_name) as company_team," +
            "CASE WHEN l.logs_type=15 THEN ABS(l.money) ELSE NULL END as red_packet_consume," +
            "CASE WHEN l.logs_type IN (1,3) THEN CONCAT('充值金额:', l.money, '元') ELSE NULL END as recharge_detail \n" +
            "            from company_money_logs l  " +
            "left join company c on c.company_id=l.company_id " +
            "left join fs_store_order_scrm o on (o.id=l.business_id and (l.logs_type=3 or  l.logs_type=4 or l.logs_type=5 or  l.logs_type=6 or  l.logs_type=13 or  l.logs_type=14))   " +
            "left join company_user cu on  cu.user_id = o.company_user_id   " +
            "left join company_dept cd on cd.dept_id = cu.dept_id   " +
            "left join fs_store_scrm s on (o.store_id=s.store_id and (l.logs_type=3 or  l.logs_type=4 or l.logs_type=5 or  l.logs_type=6 or  l.logs_type=13 or  l.logs_type=14))   " +
            "left join fs_store_payment_scrm p on ( p.`status`=1 and p.business_code=o.order_code and (l.logs_type=3 or  l.logs_type=4 or l.logs_type=5 or  l.logs_type=6 or  l.logs_type=13 or  l.logs_type=14))  " +
            "left join fs_inquiry_order io on (io.order_id=l.business_id and (l.logs_type=12)) "+
            " where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})
    List<CompanyMoneyLogsExportVO> selectCompanyMoneyLogsExportVOList(@Param("maps") FsCompanyMoneyLogsExportParam companyMoneyLogs);
//    @Select({"<script> " +
//            "select l.*,c.company_name   FROM company_money_logs l " +
//            " LEFT JOIN company c ON c.company_id= l.company_id " +
//            " where (l.logs_type=3||l.logs_type=4||l.logs_type=5||l.logs_type=6 ||  l.logs_type=13 ||  l.logs_type=14)" +
//            "<if test = 'maps.companyId != null  '> " +
//            "and l.company_id = #{maps.companyId}" +
//            "</if>" +
//            "<if test = 'maps.createTimeList != null    '> " +
//            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
//            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
//            "</if>" +
//            "order by l.logs_id desc" +
//            "</script>"})
    @Select({"<script> " +
            "select l.*,c.company_name,cd.dept_name,cu.nick_name as company_user_name, " +
            "CASE WHEN l.type = 0 THEN o.order_code WHEN l.type = 1 THEN lo.order_code ELSE NULL END as order_code, " +
            "CASE WHEN l.type = 0 THEN o.real_name WHEN l.type = 1 THEN lo.user_name ELSE NULL END as user_name, " +
            "CASE WHEN l.type = 0 THEN o.user_phone WHEN l.type = 1 THEN lo.user_phone ELSE NULL END as user_phone, " +
            "CASE WHEN l.type = 0 THEN o.user_address WHEN l.type = 1 THEN lo.user_address ELSE NULL END as user_address, " +
            "CASE WHEN l.type = 0 THEN o.total_price WHEN l.type = 1 THEN lo.total_price ELSE NULL END as total_price, " +
            "CASE WHEN l.type = 0 THEN o.pay_money WHEN l.type = 1 THEN lo.pay_money ELSE NULL END as pay_money, " +
            "CASE WHEN l.type = 0 THEN o.pay_delivery WHEN l.type = 1 THEN lo.pay_delivery ELSE NULL END as pay_delivery, " +
            "CASE WHEN l.type = 0 THEN CAST(o.status AS CHAR) WHEN l.type = 1 THEN CAST(lo.status AS CHAR) ELSE NULL END as status, " +
            "CASE WHEN l.type = 0 THEN o.delivery_sn WHEN l.type = 1 THEN lo.delivery_sn ELSE NULL END as delivery_sn, " +
            "CASE WHEN l.type = 0 THEN CAST(o.delivery_pay_status AS CHAR) WHEN l.type = 1 THEN CAST(lo.delivery_pay_status AS CHAR) ELSE NULL END as delivery_pay_status, " +
            "CASE WHEN l.type = 0 THEN o.delivery_time WHEN l.type = 1 THEN lo.delivery_time ELSE NULL END as delivery_time, " +
            "CASE WHEN l.type = 0 THEN o.delivery_pay_time WHEN l.type = 1 THEN lo.delivery_pay_time ELSE NULL END as delivery_pay_time, " +
            "CASE WHEN l.type = 0 THEN null WHEN l.type = 1 THEN NULL ELSE NULL END as package_name, " +
            "CASE WHEN l.type = 0 THEN null WHEN l.type = 1 THEN NULL ELSE NULL END as package_second_name, " +
            "CASE WHEN l.type = 0 THEN null WHEN l.type = 1 THEN lo.pay_type ELSE NULL END as pay_type " +
            "FROM company_money_logs l " +
            "LEFT JOIN fs_store_order_scrm o ON (o.id=CAST(l.business_id AS UNSIGNED) and o.company_id=l.company_id and l.type=0) " +
            "LEFT JOIN live_order lo ON (lo.order_id=CAST(l.business_id AS UNSIGNED) and lo.company_id=l.company_id and l.type=1) " +
            "LEFT JOIN company c ON c.company_id= l.company_id " +
            "LEFT JOIN company_user cu ON (cu.user_id=CASE WHEN l.type=0 THEN o.company_user_id WHEN l.type=1 THEN lo.company_user_id ELSE NULL END) " +
            "LEFT JOIN company_dept cd ON cd.dept_id=cu.dept_id   " +
            "LEFT JOIN fs_store_payment_scrm p ON (p.business_code=o.order_code and p.status=1 and l.type=0) " +
            "where (l.logs_type=3||l.logs_type=4||l.logs_type=5||l.logs_type=6 ||  l.logs_type=13 ||  l.logs_type=14)" +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.orderType != null  '> " +
            "and l.type = #{maps.orderType}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc LIMIT 30000" +
            "</script>"})
    List<CompanyMoneyLogsExport1VO> selectCompanyMoneyLogsExport1VOList(@Param("maps") FsCompanyMoneyLogsExportParam companyMoneyLogs);

    @Select({"<script> " +
            "select l.*,o.*,c.company_name,cd.dept_name FROM company_money_logs l LEFT JOIN fs_store_payment o ON o.payment_id=l.business_id and o.company_id =l.company_id  LEFT JOIN company c ON c.company_id= l.company_id LEFT JOIN company_user cu ON cu.user_id=o.company_user_id LEFT JOIN company_dept cd ON cd.dept_id=cu.dept_id   where (l.logs_type=8||l.logs_type=9)" +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})
    List<CompanyMoneyLogsExport2VO> selectCompanyMoneyLogsExport2VOList(@Param("maps")CompanyMoneyLogs companyMoneyLogs);

    @Select("select l.* from company_money_logs l where l.company_id=#{companyId} and logs_id >#{minId} order by l.logs_id asc " )
    List<CompanyMoneyLogs> selectCompanyMoneyLogsByCompanyId(@Param("companyId") Long companyId,@Param("minId")  String minId);

    @Select({"<script> " +
            "select l.*,c.company_name " +
            "from company_money_logs l left join company c on c.company_id=l.company_id   " +
            "where (l.logs_type=3 or  l.logs_type=4 or l.logs_type=5 or  l.logs_type=6 or l.logs_type=13 or  l.logs_type=14) " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.businessId != null  '> " +
            "and l.business_id = #{maps.businessId}" +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})
    List<CompanyStoreOrderMoneyLogsVO> selectCompanyStoreOrderMoneyLogsList(@Param("maps")CompanyStoreOrderMoneyLogsListParam moneyLogsMap);


    @Select("select * FROM company_money_logs where (logs_type=3 or logs_type=5) AND business_id=#{id}")
    List<CompanyMoneyLogs> selectCompanyMoneyByOrderId(Long id);

    @Select("select * FROM company_money_logs where (logs_type=4 or logs_type=6) AND business_id=#{id}")
    List<CompanyMoneyLogs> selectCompanyMoneyAllTypeByOrderId(Long id);

    @Select("select * FROM company_money_logs where logs_type=12 AND business_id=#{id} limit 1")
    CompanyMoneyLogs selectCompanyMoneyByInquiry(Long id);

    /**
     * 批量更新公司金额日志
     * @param list 日志列表
     */
    void batchUpdateCompanyMoneyLogs(@Param("list") List<CompanyMoneyLogs> list);

    /**
     * 查询所有分佣日志（logsType = 3）
     * @return 分佣日志列表
     */
    @Select("SELECT * FROM company_money_logs WHERE logs_type = 3")
    List<CompanyMoneyLogs> selectAllCommissionLogs();

    /**
     * 查询所有退佣日志（logsType = 4 或 5）
     * @return 退佣日志列表
     */
    @Select("SELECT * FROM company_money_logs WHERE logs_type = 4 OR logs_type = 5")
    List<CompanyMoneyLogs> selectAllRefundLogs();
    @Select({"<script> " +
            "select  count(1) from company_money_logs l" +
            " where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.logsType != null  '> " +
            "and l.logs_type = #{maps.logsType}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "</script>"})
    Integer selectCompanyMoneyLogsExportCounts(@Param("maps") FsCompanyMoneyLogsExportParam companyMoneyLogs);


    @Select({"<script> " +
            "select count(1)  FROM company_money_logs l " +
            " where (l.logs_type=3||l.logs_type=4||l.logs_type=5||l.logs_type=6 ||  l.logs_type=13 ||  l.logs_type=14)" +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.orderType != null  '> " +
            "and l.type = #{maps.orderType}" +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            "</script>"})
    Integer selectCompanyMoneyLogsExport1Counts(@Param("maps")FsCompanyMoneyLogsExportParam param);

    List<CompanyRedPackage> selectCompanyRedPackageListVO(CompanyRedPackage companyRedPackage);
}
