package com.consum.base.service.impl;

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

import org.apache.commons.compress.utils.Lists;
import org.springframework.stereotype.Service;

import com.consum.base.core.utils.MapUtils;
import com.consum.base.core.utils.MapperUtil;
import com.consum.base.pojo.query.LWhGoodsStatisQry;
import com.consum.base.pojo.response.GoodsStatisticsInfoVO;
import com.consum.base.service.LWhGoodsStatisticsService;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.jdbc.service.BaseServiceImpl;

/**
 * @ClassName LWhGoodsStatisticsServiceImpl
 * @Author cy
 * @Date 2023/11/21
 * @Description
 * @Version 1.0
 **/
@Service
public class LWhGoodsStatisticsServiceImpl extends BaseServiceImpl implements LWhGoodsStatisticsService {
    private String GET_WHGOODS_STATIS_LIST_COLUMN =
        "SELECT tCangKu.org_name,tCangKu.WAREHOUSE_NAME,goodsTemp.GOODS_CODE,tKuCun.GOODS_TEMPLATE_NAME,tKuCun.BASE_GOODS_MODELS_NAME,CASE tKuCun.COST_TYPE WHEN 1 THEN 'A类' WHEN 2 THEN 'B类' WHEN 3 THEN 'C类' END AS cost_Type,tKuCun.zai_ku_num,tKuCun.diao_bo_num,tKuCun.bao_fei_num FROM ";
    private String GET_WHGOODS_STATIS_LIST_T_CANGKU =
        "(SELECT tenant.id tenantId,tenant.NAME AS org_name,ware.*FROM fin_sys_tenant tenant right JOIN (SELECT 0 WAREHOUSE_TYPE,id,AGENCY_ID,WAREHOUSE_NAME FROM BASE_WAREHOUSE UNION ALL SELECT 1 WAREHOUSE_TYPE,id,TENANT_ID AGENCY_ID,`NAME` WAREHOUSE_NAME FROM fin_sys_tenant_department) ware ON tenant.id=ware.AGENCY_ID ";
    private String GET_WHGOODS_STATIS_LIST_T_CANGKU_WHERE = "where 1=1";
    private String GET_WHGOODS_STATIS_LIST_T_CANGKU_END = ") tCangKu LEFT JOIN";
    private String GET_WHGOODS_STATIS_LIST_T_KUCUN =
        "(SELECT WAREHOUSE_TYPE,WAREHOUSE_ID,BASE_GOODS_TEMPLATE_ID,GOODS_TEMPLATE_NAME,COST_TYPE,BASE_GOODS_MODELS_ID,BASE_GOODS_MODELS_NAME,COUNT(CASE WHEN goods.STATES BETWEEN 0 AND 2 THEN 1 END) AS total_num,count(CASE WHEN goods.STATES=1 OR goods.STATES=2 THEN 1 END) zai_ku_num,count(CASE WHEN goods.STATES=0 THEN 1 END) diao_bo_num,count(CASE WHEN goods.STATES=3 THEN 1 END) bao_fei_num FROM l_wh_goods goods ";
    private String GET_WHGOODS_STATIS_LIST_T_KUCUN_WHERE = "where 1=1";
    private String GET_WHGOODS_STATIS_LIST_T_KUCUN_GROUP_BY =
        " GROUP BY WAREHOUSE_TYPE,WAREHOUSE_ID,BASE_GOODS_TEMPLATE_ID,GOODS_TEMPLATE_NAME,BASE_GOODS_MODELS_ID,BASE_GOODS_MODELS_NAME,COST_TYPE) tKuCun ON tCangKu.WAREHOUSE_TYPE=tKuCun.WAREHOUSE_TYPE and tCangKu.id = tKuCun.WAREHOUSE_ID LEFT JOIN BASE_GOODS_TEMPLATE goodsTemp ON goodsTemp.id=tKuCun.BASE_GOODS_TEMPLATE_ID ";
    private String GET_WHGOODS_STATIS_LIST_T_END_WHERE = " where 1=1";

    @Override
    public List getWhGoodsStatisList(LWhGoodsStatisQry param) {

        List<GoodsStatisticsInfoVO> result = Lists.newArrayList();

        StringBuilder sqlColumn = new StringBuilder(GET_WHGOODS_STATIS_LIST_COLUMN);
        StringBuilder sqlCangKu = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU);
        StringBuilder sqlCangKuWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU_WHERE);
        StringBuilder sqlCangKuEnd = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_CANGKU_END);
        StringBuilder sqlKuCun = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN);
        StringBuilder sqlKuCunWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN_WHERE);
        StringBuilder sqlKuCunGoupBy = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_KUCUN_GROUP_BY);
        StringBuilder sqlEndWhere = new StringBuilder(GET_WHGOODS_STATIS_LIST_T_END_WHERE);

        HashMap<String, Object> paramts = new HashMap<>();
        // 机构
        if (param.getAgencyId() != null) {
            // sqlCangKuWhere.append(" AND left(tenant.id, length(:lengthAgencyId)) = :agencyId");
            sqlCangKuWhere.append(" AND tenant.id = :agencyId");
            // paramts.put("lengthAgencyId", param.getAgencyId());
            paramts.put("agencyId", param.getAgencyId());
        }
        // 部门
        if (param.getDepartmentId() != null) {
            sqlCangKuWhere.append(" AND ware.WAREHOUSE_TYPE=1 and ware.id = :departmentId");
            paramts.put("departmentId", param.getDepartmentId());
        }
        // 仓库
        if (param.getBaseWarehouseId() != null) {
            sqlCangKuWhere.append(" AND ware.WAREHOUSE_TYPE =0 and ware.id = :warehouseId");
            paramts.put("warehouseId", param.getBaseWarehouseId());
        }
        // 物品名称
        if (StringUtils.isNotEmpty(param.getGoodsTemplateName())) {
            sqlKuCunWhere.append(" AND goods.GOODS_TEMPLATE_NAME like :goodsTemplateName");
            paramts.put("goodsTemplateName",
                StringUtils.CHAR_PERCENT + param.getGoodsTemplateName() + StringUtils.CHAR_PERCENT);
        }
        if (param.getGoodsTemplateId() != null) {
            sqlKuCunWhere.append(" AND goods.BASE_GOODS_TEMPLATE_ID=:goodsTemplateId");
            paramts.put("goodsTemplateId", param.getGoodsTemplateId());
        }
        // 规格型号
        if (param.getBaseGoodsModelsId() != null) {
            sqlKuCunWhere.append(" AND goods.BASE_GOODS_MODELS_ID=:baseGoodsModelsId");
            paramts.put("baseGoodsModelsId", param.getBaseGoodsModelsId());
        }
        // 价值类型
        if (param.getCostType() != null) {
            // 将数字转换为对应的字符
            char costType = (char)('A' + param.getCostType() - 1);
            sqlKuCunWhere.append(" AND goods.COST_TYPE=':costType'");
            paramts.put("costType", costType);
        }

        // // 操作时间
        // if (param.getDealTimeStart() != null) {
        // sqlEnd.append(" and flow.DEAL_TIME >=:dealTimeStart ");
        // paramts.put("dealTimeStart", param.getDealTimeStart() * 1000000);
        // }
        // if (param.getDealTimeEnd() != null) {
        // sqlEnd.append(" and flow.DEAL_TIME <:dealTimeEnd ");
        // paramts.put("dealTimeEnd", param.getDealTimeEnd() * 1000000 + 240000);
        // }
        sqlEndWhere.append(" ORDER BY tKuCun.total_num desc,tCangKu.tenantId asc");

        StringBuilder sql = new StringBuilder();
        sql.append(sqlColumn).append(sqlCangKu).append(sqlCangKuWhere).append(sqlCangKuEnd).append(sqlKuCun)
            .append(sqlKuCunWhere).append(sqlKuCunGoupBy).append(sqlEndWhere);

        List<Map<String, Object>> select = select(sql.toString(), paramts, new MapperUtil());
        select.forEach(item -> {
            GoodsStatisticsInfoVO goodsStatisticsInfoVO = MapUtils.convertMapToObj(item, GoodsStatisticsInfoVO.class);
            // 统计总数改为：在库数量+报废数量
            if (goodsStatisticsInfoVO.getZaiKuNum() != null && goodsStatisticsInfoVO.getBaoFeiNum() != null) {
                goodsStatisticsInfoVO
                    .setTotalNum(goodsStatisticsInfoVO.getZaiKuNum() + goodsStatisticsInfoVO.getBaoFeiNum());
            }
            result.add(goodsStatisticsInfoVO);
        });
        return result;
    }
}