package com.bailuntec.balancesheet.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;

@Repository
public interface DcInventoryBalanceMapper {
    /**
     * 插入库存明细
     * @param lastDay
     */
    @Insert("insert into dc_inventory_balance(warehouse_code,warehouse_name,hq_type,balance_rmb,statistical_time,is_transfer)" +
            " select ds.warehouse_code,w.warehouse_name,w.hq_type,sum(ds.unit_price * (ds.usable_stock + ds.occupy_stock) * r.exchange_rate),#{lastDay},0" +
            " from dc_daily_stock ds left join dc_base_sku s on ds.bailun_sku = s.bailun_sku" +
            " left join dc_base_warehouse w on ds.warehouse_code = w.warehouse_code" +
            " left join dc_exchange_rate r on s.currency = r.init_curreny" +
            " where ds.record_time = #{lastDay} and r.final_curreny = 'CNY' and r.record_time = #{lastDay}" +
            " group by ds.warehouse_code,w.warehouse_name,w.hq_type")
    void insertInventoryBalance(@Param("lastDay")Date lastDay);

    /**
     * 插入调拨在途明细
     * @param lastDay
     */
    @Insert("insert into dc_inventory_balance(warehouse_code,warehouse_name,hq_type,balance_rmb,statistical_time,is_transfer,transfer_rmb,transfer_plan_rmb,purchase_rmb)" +
            " select t.warehouse_code,w.warehouse_name,w.hq_type,sum((s.unit_price * t.quantity_transfer * r.exchange_rate) + (s.unit_price * t.quantity_purchase * r.exchange_rate))," +
            "#{lastDay},1,sum(s.unit_price * (t.quantity_transfer - t.quantity_transfer_plan) * r.exchange_rate),sum(s.unit_price * t.quantity_transfer_plan * r.exchange_rate)," +
            "sum(s.unit_price * t.quantity_purchase * r.exchange_rate)" +
            " from dc_daily_transit t left join dc_base_sku s on t.bailun_sku = s.bailun_sku" +
            " left join dc_base_warehouse w on t.warehouse_code = w.warehouse_code" +
            " left join dc_exchange_rate r on s.currency = r.init_curreny" +
            " where t.record_time = #{lastDay} and r.final_curreny = 'CNY' and r.record_time = #{lastDay}" +
            " group by t.warehouse_code,w.warehouse_name,w.hq_type")
    void insertTransferInventoryBalance(@Param("lastDay")Date lastDay);

    /**
     * 获取存货总额
     * @param lastDay
     * @return
     */
    @Select("select sum(balance_rmb) from dc_inventory_balance where statistical_time = #{lastDay}")
    BigDecimal getInventoryBalanceSum(@Param("lastDay")Date lastDay);

    /**
     * 获取FBA在库金额
     * @param lastDay
     * @return
     */
    @Select("select sum(balance_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type='FBA仓' and is_transfer=0")
    BigDecimal getFbaInventoryAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取非FBA在库金额
     * @param lastDay
     * @return
     */
    @Select("select sum(balance_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type<>'FBA仓' and is_transfer=0")
    BigDecimal getNotFbaInventoryAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取FBA在途金额(调拨+采购)
     * @param lastDay
     * @return
     */
    @Select("select sum(balance_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type='FBA仓' and is_transfer=1")
    BigDecimal getFbaTransferAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取非FBA在途金额(调拨+采购)
     * @param lastDay
     * @return
     */
    @Select("select sum(balance_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type<>'FBA仓' and is_transfer=1")
    BigDecimal getNotFbaTransferAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取FBA在途金额(调拨不包含计划)
     * @param lastDay
     * @return
     */
    @Select("select sum(transfer_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type='FBA仓' and is_transfer=1")
    BigDecimal getFbaTransferAllotAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取非FBA在途金额(调拨不包含计划)
     * @param lastDay
     * @return
     */
    @Select("select sum(transfer_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type<>'FBA仓' and is_transfer=1")
    BigDecimal getNotFbaTransferAllotAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取FBA调拨计划在途金额
     * @param lastDay
     * @return
     */
    @Select("select sum(transfer_plan_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type='FBA仓' and is_transfer=1")
    BigDecimal getFbaTransferPlanAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取非FBA调拨计划在途金额
     * @param lastDay
     * @return
     */
    @Select("select sum(transfer_plan_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type<>'FBA仓' and is_transfer=1")
    BigDecimal getNotFbaTransferPlanAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取FBA采购在途金额
     * @param lastDay
     * @return
     */
    @Select("select sum(purchase_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type='FBA仓' and is_transfer=1")
    BigDecimal getFbaPurchaseAmountSum(@Param("lastDay")Date lastDay);

    /**
     * 获取非FBA采购在途金额
     * @param lastDay
     * @return
     */
    @Select("select sum(purchase_rmb) from dc_inventory_balance where statistical_time = #{lastDay} and hq_type<>'FBA仓' and is_transfer=1")
    BigDecimal getNotFbaPurchaseAmountSum(@Param("lastDay")Date lastDay);


    /*@Select("select sum(b.balance_rmb) from dc_inventory_balance b,dc_inventory_company_relation r" +
            " where b.warehouse_code = r.warehouse_code and r.company_value = #{companyValue} and b.statistical_time = #{lastDay}")
    BigDecimal getInventoryBalanceSum(@Param("companyValue") Integer companyValue, @Param("lastDay")Date lastDay);*/
}
