package cn.ksource.web.facade.supplier;

import cn.ksource.beans.SUPPLIER_MODIFY_RECORD;
import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.page.PageInfo;
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.DateUtil;
import cn.ksource.core.util.StringUtil;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * Created by chenlong
 * Date：2017/5/17
 * time：8:59
 */
@Service
public class SupplierFacadeImpl implements SupplierFacade{

    @Resource
    private BaseDao baseDao;

    @Override
    public PageInfo getSupplierListData(PageInfo pageInfo, Map<String, String> params) {
        String sql = getSupplierListSupport(params);
        return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
    }

    @Override
    public Integer getSupplierListCount(Map<String, String> params) {
        String sql = " select count(*) from ( " + getSupplierListSupport(params) + " ) t";
        return baseDao.queryForInteger(sql,params);
    }

    String getSupplierListSupport(Map<String, String> params) {
        StringBuilder sql = new StringBuilder(" select a.id,a.state,a.supplier_name,a.contacts,a.contacts_tel,a.province,a.city,a.county, " +
                " a.create_name,a.gmt_create,b.cate_num  from supplier_info a ," +
                " ( select count(*) cate_num,supplier_id from supplier_spare_part  group by supplier_id ) b " +
                " where a.id = b.supplier_id  ");
        //供应商名称
        if(StringUtil.isNotBlank(params.get("supplier_name"))){
            sql.append(" and a.supplier_name like :supplier_name ");
            params.put("supplier_name", "%" + params.get("supplier_name") + "%");
        }

        //联系人
        if(StringUtil.isNotBlank(params.get("contacts"))){
            sql.append(" and a.contacts like :contacts ");
            params.put("contacts", "%" + params.get("contacts") + "%");
        }
         sql.append(" order by a.state desc,a.gmt_create desc ");
        return sql.toString();
    }

    @Override
    public void editSupplier(Map<String, String> params) {
        String id = params.get("id");
        params.put("gmt_create", DateUtil.getCurrentDate14().toString());
        params.put("gmt_modified", DateUtil.getCurrentDate14().toString());

        if(StringUtil.isBlank(params.get("gmt_register"))){
            params.put("gmt_register", null);
        }

        StringBuilder sql = new StringBuilder();
        if(StringUtil.isBlank(id)){//新增
            id = StringUtil.getUUID();
            params.put("id", id);
            sql.append(" insert into supplier_info (id,supplier_name,gmt_register,legal_person,office_tel,email,contacts,contacts_tel,province,province_id,city,city_id,county,county_id,address,note,create_id,create_name,gmt_create,gmt_modified,state) ");
            sql.append(" values (:id,:supplier_name,:gmt_register,:legal_person,:office_tel,:email,:contacts,:contacts_tel,:province,:province_id,:city,:city_id,:county,:county_id,:address,:note,:create_id,:create_name,:gmt_create,:gmt_modified,1) ");
        }else{
            sql.append(" update supplier_info set supplier_name=:supplier_name,gmt_register=:gmt_register,legal_person=:legal_person,office_tel=:office_tel," +
                    "email=:email,contacts=:contacts,contacts_tel=:contacts_tel,province=:province,province_id=:province_id,city=:city,city_id=:city_id,county=:county,county_id=:county_id," +
                    "address=:address,note=:note,gmt_modified=:gmt_modified where id=:id  ");
        }

        //保存基本信息
        baseDao.execute(sql.toString(), params);
        SUPPLIER_MODIFY_RECORD supplier_modify_record=new SUPPLIER_MODIFY_RECORD();
        supplier_modify_record.setModify_id(params.get("create_id"));
        supplier_modify_record.setSupplier_id(id);
        supplier_modify_record.setModify_name(params.get("create_name"));
        supplier_modify_record.setGmt_modify(DateUtil.getCurrentDate14());
        supplier_modify_record.setModify_type(Integer.valueOf(params.get("type")));
        supplier_modify_record.insert();
        //删除关联供货清单
        baseDao.execute("delete from supplier_spare_part where supplier_id=:id ", params);
        //添加供货信息
        String cateIds = params.get("cate_id");
        String offerPrices = params.get("offer_price");
        if(StringUtil.isNotBlank(cateIds)){
            sql.setLength(0);
            sql.append(" insert into supplier_spare_part (id,cate_id,supplier_id,offer_price,state ) values (:id,:cate_id,:supplier_id,:offer_price,1 ) ");

            List<SqlParameter> paramList = new ArrayList<SqlParameter>();
            String[] cateIdAry = cateIds.split(",");
            String[] offerPriceAry = offerPrices.split(",");
            for (int i = 0; i < cateIdAry.length; i++) {
                SqlParameter p = new SqlParameter();
                p.addValue("id", StringUtil.getUUID())
                    .addValue("cate_id",cateIdAry[i])
                    .addValue("inventory_order_id",id)
                    .addValue("supplier_id",params.get("id"))
                    .addValue("offer_price",offerPriceAry[i]);
                paramList.add(p);
            }
            baseDao.executeBatch(sql.toString(),paramList);
        }
    }

    @Override
    public List<Map> getCateList(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("select a.id,a.lv1_name,a.lv2_name,a.lv3_name,a.code,(select code from cmdb_ci_category where id=a.lv1_id) code1, (select code from cmdb_ci_category where id=a.lv2_id)  code2 from cmdb_ci_category a where a.cate_type=2 and a.lv=3 and a.state=1 ");
        if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("cate_name")))) {
            sql.append(" and a.name like :cate_name ");
            params.put("cate_name", "%" + params.get("cate_name") + "%");
        }
        if (StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("code")))) {
            sql.append(" and a.code like :code ");
            params.put("code", "%" + params.get("code") + "%");
        }
        if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(params.get("cateIds")))){
            sql.append(" and a.id not in (:cateIds) ");
            params.put("cateIds", Arrays.asList(ConvertUtil.obj2StrBlank(params.get("cateIds")).split(",")));
        }

        return baseDao.queryForList(sql.toString(), params);
    }

    @Override
    public Map getSupplierInfo(String id) {
        SqlParameter param = new SqlParameter("id", id);
        StringBuilder sql = new StringBuilder(" select * from supplier_info where id=:id ");
        Map info = baseDao.queryForMap(sql.toString(), param);

        sql.setLength(0);
        sql.append(" select a.*,b.lv1_name,b.lv2_name,b.lv3_name,b.code,(select code from cmdb_ci_category where id=b.lv1_id) code1, (select code from cmdb_ci_category where id=b.lv2_id) code2 from supplier_spare_part a,cmdb_ci_category b where a.cate_id=b.id and a.supplier_id=:id order by a.id ");
        List<Map> list = baseDao.queryForList(sql.toString(), param);
        info.put("list", list);

        return info;
    }

    @Override
    public String  delSupplier(String id,String state) {

    	String sqlString="select count(*) from  SPARE_PART where SUPPLIER_ID=:supplier_id and state in(1,2,3,4,5)";
    	Map paramMap = new HashMap();
    	paramMap.put("supplier_id", id);
    	Integer aInteger=baseDao.queryForInteger(sqlString, paramMap);
    	if(aInteger!=0){
    		return "0";
    	}else if(state.equals("0")){
    		 baseDao.execute(" update supplier_info set state=0 where id=:id ", new SqlParameter("id", id));
    		 return "1";
    	}else if(state.equals("1")){
            baseDao.execute(" update supplier_info set state=1 where id=:id ", new SqlParameter("id", id));
            return "1";
        }
       return "1";
    }

    @Override
    public PageInfo getSparePartListData(PageInfo pageInfo, Map<String, String> params) {
        String sql = getSparePartListSupport(params);
        return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
    }

    @Override
    public Integer getSparePartListCount(Map<String, String> params) {
        String sql = " select count(*) from ( " + getSparePartListSupport(params) + " ) t";
        return baseDao.queryForInteger(sql,params);
    }

    String getSparePartListSupport(Map<String, String> params) {
        StringBuilder sql = new StringBuilder(" select a.id,a.supplier_name,b.offer_price,c.lv1_name,c.lv2_name,c.lv3_name,c.code,(select code from cmdb_ci_category where id=c.lv1_id) code1,(select code from cmdb_ci_category where id=c.lv2_id) code2," +
                " a.gmt_modified,a.gmt_create from supplier_info a ," +
                " supplier_spare_part b,cmdb_ci_category c " +
                " where a.id = b.supplier_id and b.cate_id = c.id and c.cate_type=2 and a.state=1 and b.state=1 and c.state=1 ");
        //供应商名称
        if(StringUtil.isNotBlank(params.get("supplier_name"))){
            sql.append(" and a.supplier_name like :supplier_name ");
            params.put("supplier_name", "%" + params.get("supplier_name") + "%");
        }

        //备件分类
        if(StringUtil.isNotBlank(params.get("cate_id"))){
            sql.append(" and c.id like :cate_id ");
            params.put("cate_id", "%" + params.get("cate_id") + "%");
        }

        //分类编码
        if(StringUtil.isNotBlank(params.get("code"))){
            sql.append(" and c.code like :code ");
            params.put("code", "%" + params.get("code") + "%");
        }

        //开始日期
        if(StringUtil.isNotBlank(params.get("startDate"))){
            sql.append(" and a.gmt_create >= :startDate ");
            params.put("startDate", params.get("startDate") + "000000");
        }
        //结束日期
        if(StringUtil.isNotBlank(params.get("endDate"))){
            sql.append(" and a.gmt_create <= :endDate ");
            params.put("endDate", params.get("endDate") + "666666");
        }

        sql.append(" order by a.gmt_create desc ");

        return sql.toString();
    }

    @Override
    public PageInfo getSparePartSearchListData(PageInfo pageInfo, Map<String, String> params) {
        String sql = getSparePartSearchListSupport(params);
        return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
    }

    @Override
    public Integer getSparePartSearchListCount(Map<String, String> params) {
        String sql = " select count(*) from ( " + getSparePartSearchListSupport(params) + " ) t";
        return baseDao.queryForInteger(sql,params);
    }

    String getSparePartSearchListSupport(Map<String, String> params) {
        StringBuilder sql = new StringBuilder(" select if(warranty_end>date_format(now(),'%Y%m%d'),1,2) over_flag,a.warranty_end,a.id,b.lv1_name,b.lv2_name,b.lv3_name,a.name,a.asset_no,a.entity_no,a.model_no,a.state, " +
                " a.location,a.gmt_create  from spare_part a ,cmdb_ci_category b " +
                " where a.cate_id = b.id and b.cate_type=2  and b.state=1 ");

        //备件名称
        if(StringUtil.isNotBlank(params.get("name"))){
            sql.append(" and a.name like :name ");
            params.put("name", "%" + params.get("name") + "%");
        }

        //备件状态
        if(StringUtil.isNotBlank(params.get("state"))){
            sql.append(" and a.state = :state ");
        }else{
            sql.append(" and a.state in (1,2,3) ");
        }
//        //备件可用状态
//        if(StringUtil.isNotBlank(params.get("ky_state"))){
//            sql.append(" and a.ky_state = :ky_state ");
//        }

        //资产编码
        if(StringUtil.isNotBlank(params.get("asset_no"))){
            sql.append(" and a.asset_no like :asset_no ");
            params.put("asset_no", "%" + params.get("asset_no") + "%");
        }

        //备件分类
        if(StringUtil.isNotBlank(params.get("cate_id"))){
            sql.append(" and b.id like :cate_id ");
            params.put("cate_id", "%" + params.get("cate_id") + "%");
        }

        //实物编码
        if(StringUtil.isNotBlank(params.get("entity_no"))){
            sql.append(" and a.entity_no like :entity_no ");
            params.put("entity_no", "%" + params.get("entity_no") + "%");
        }

        //开始日期
        if(StringUtil.isNotBlank(params.get("startDate"))){
            sql.append(" and a.gmt_create >= :startDate ");
            params.put("startDate", params.get("startDate") + "000000");
        }
        //结束日期
        if(StringUtil.isNotBlank(params.get("endDate"))){
            sql.append(" and a.gmt_create <= :endDate ");
            params.put("endDate", params.get("endDate") + "666666");
        }

        Long curDate = DateUtil.getCurrentDate8();
        params.put("curDate", curDate.toString());

        //是否过保
        if(StringUtil.isNotBlank(params.get("overFlag"))){
            if(params.get("overFlag").equals("1")){//未过保
                sql.append(" and a.warranty_end >= :curDate ");
            }else{//已过保
                sql.append(" and a.warranty_end <= :curDate ");
            }
        }

        //过保查询结束时间
        if(StringUtil.isNotBlank(params.get("overEndDate"))){
            sql.append(" and a.warranty_end <= :overEndDate ");
        }

        //过保状态
        if(StringUtil.isNotBlank(params.get("overState"))){
            String overState = params.get("overState");
            if(overState.equals("oneMonth")){//一月内过保
                sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 1 month),'%Y%m%d') ");
            }else if(overState.equals("twoMonth")){//两月内过保
                sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 2 month),'%Y%m%d') ");
            }else if(overState.equals("threeMonth")){//三月内过保
                sql.append(" and a.warranty_end <= DATE_FORMAT(date_add(:curDate,interval 3 month),'%Y%m%d') ");
            }
        }
        sql.append(" order by a.gmt_create desc ");
        return sql.toString();
    }

    @Override
    public PageInfo getFinanceListData(PageInfo pageInfo, Map<String, String> params) {
        String sql = getFinanceSupport(params);
        return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
    }

    @Override
    public Integer getFinanceListCount(Map<String, String> params) {
        String sql = " select count(*) from ( " + getFinanceSupport(params) + " ) t";
        return baseDao.queryForInteger(sql,params);
    }

    String getFinanceSupport(Map<String, String> params) {


        StringBuilder sql = new StringBuilder(" select d.id,d.customer_name,d.short_name,d.ywjl_name,IFNULL(f.use_num,0) use_num,IFNULL(f.sum_money,0) sum_money " +
                " from sc_partner_customer_info d left join (  " +
                "   select c.cus_id,count(*) use_num,ifnull(sum(a.sale_price),0) sum_money" +
                "   from spare_part a,spare_part_apply_entity b,cmdb_ci_base c " +
                "   where a.id = b.entity_id and b.ci_id = c.id and b.state=2 ");

        //开始时间
        if(StringUtil.isNotBlank(params.get("startDate"))){
            sql.append(" and b.gmt_usage >= :startDate ");
            params.put("startDate", params.get("startDate")+"000000");
        }

        //结束时间
        if(StringUtil.isNotBlank(params.get("endDate"))){
            sql.append(" and b.gmt_usage <= :endDate ");
            params.put("endDate", params.get("endDate")+"666666");
        }

        sql.append(" group by c.cus_id ) f on f.cus_id = d.id  where 1=1");

        //项目名称
        if(StringUtil.isNotBlank(params.get("customer_id"))){
            sql.append(" and d.id=:customer_id ");
            params.put("customer_id", params.get("customer_id"));
        }

        //运维经理
        if(StringUtil.isNotBlank(params.get("ywjl_name"))){
            sql.append(" and d.ywjl_name like :ywjl_name ");
            params.put("ywjl_name", "%" + params.get("ywjl_name") + "%");
        }

        sql.append(" and d.customer_state=1 order by sum_money desc ");

        return sql.toString();
    }


    @Override
    public PageInfo getReplaceInfoListData(PageInfo pageInfo, Map<String, String> params) {
        String sql = getReplaceInfoListSupport(params);
        return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
    }

    @Override
    public Integer getReplaceInfoListCount(Map<String, String> params) {
        String sql = " select count(*) from ( " + getReplaceInfoListSupport(params) + " ) t";
        return baseDao.queryForInteger(sql,params);
    }

    String getReplaceInfoListSupport(Map<String, String> params) {

        StringBuilder sql = new StringBuilder(" select d.id order_id,c.id ci_id,a.id spare_id,c.ciname,e.lv1_name,e.lv2_name,e.lv3_name,a.entity_no,a.name,a.asset_no,a.model_no,d.gmt_create " +
                " from spare_part a,spare_part_apply_entity b,cmdb_ci_base c ,spare_part_apply d,cmdb_ci_category e " +
                " where a.id = b.entity_id and b.ci_id = c.id and b.order_id=d.id and b.state=2 and a.cate_id =e.id  ");


        //项目
        if(StringUtil.isNotBlank(params.get("cus_id"))){
            sql.append(" and c.cus_id = :cus_id ");
        }

        //备件实物编码
        if(StringUtil.isNotBlank(params.get("entity_no"))){
            sql.append(" and a.entity_no like :entity_no ");
            params.put("entity_no", "%" + params.get("entity_no") + "%");
        }

        //备件名称
        if(StringUtil.isNotBlank(params.get("name"))){
            sql.append(" and a.name like :name ");
            params.put("name", "%" + params.get("name") + "%");
        }

        //配置名称
        if(StringUtil.isNotBlank(params.get("ci_name"))){
            sql.append(" and c.ciname like :ci_name ");
            params.put("ci_name", "%" + params.get("ci_name") + "%");
        }

        sql.append(" order by a.gmt_create desc ");
        return sql.toString();
    }

	@Override
	public List<Map> getCateGorySpareListData(Map<String, String> params) {
		String sql = this.getCateGorySpareList(params);
		return baseDao.queryForList(sql,params);
	}

	@Override
	public Integer getCateGorySpareListCount(Map<String, String> params) {
		String sql = "select count(*) from ( "+this.getCateGorySpareList(params)+ " ) t";
		return baseDao.queryForInteger(sql, params);
	}

	// 获得备件分类列表查询条件
	private String getCateGorySpareList(Map<String,String> params){

		StringBuilder sql = new StringBuilder("SELECT b.id,b.LV1_NAME,b.LV2_NAME,b.LV3_NAME,b.CODE,(select code from cmdb_ci_category where id=b.LV1_id) code1,(select code from cmdb_ci_category where id=b.LV2_id) code2 ,count(*) SL FROM "
			+" cmdb_ci_category b left join spare_part a on b.ID = a.CATE_ID"
			+" WHERE  a.state = 1 and b.state = 1 and  b.cate_type = 2 ");
		String cate_id = params.get("cate_id");	//备件分类
		String code = params.get("code");	//分类编号
		String cateIds = params.get("cateIds");	//分类编号
		  if (StringUtil.notEmpty(cateIds)) {
	            String[] cateId_s = cateIds.split(",");
	            if (cateId_s.length == 1) {
	            	sql.append(" AND b.id != :cmdb_ci_category_id");
	            	params.put("cmdb_ci_category_id", cateId_s[0]);
	            } else {
	            	sql.append(" AND b.id not IN (");
	                for (int i = 0; i < cateId_s.length; i++) {
	                	sql.append(" :pri" + i);
	                	sql.append(",");
	                	params.put("pri" + i, cateId_s[i]);
	                }
	                sql.deleteCharAt(sql.lastIndexOf(","));
	                sql.append(")");
	            }
	        }

		 if (StringUtil.isNotBlank(cate_id)) {
	           sql.append(" and b.ID =:bjfl ");
	           params.put("bjfl",cate_id );
	      }

		 if (StringUtil.isNotBlank(code)) {
	           sql.append(" and b.code like :flbm ");
	           params.put("flbm","%"+code+"%" );
	      }
		 sql.append(" group by b.id,b.LV1_id,b.LV2_id,b.LV1_NAME, b.LV2_NAME, b.LV3_NAME, b.CODE");
		 return sql.toString();

	}

    @Override
    public Map getReplaceDetail(Map<String,String> params) {
	    //查询申请单信息
        StringBuilder sql = new StringBuilder(" select a.order_code,a.create_name,a.gmt_create,a.order_name,a.apply_reason," +
                " n.current_dealer_name deal_name,dealtime deal_time " +
                " from spare_part_apply a,workflow_base b,workflow_node n " +
                " where a.flow_id=b.id and b.current_node_id = n.id and a.id=:order_id ");
        Map info = baseDao.queryForMap(sql.toString(), params);
        //查询备件信息
        sql.setLength(0);
        sql.append(" select a.entity_no,a.name spare_name,a.asset_no,a.model_no,b.lv1_name,b.lv2_name,b.lv3_name,a.sale_price,a.location " +
                " from spare_part a,cmdb_ci_category b " +
                " where a.cate_id =b.id and a.id=:spare_id ");
        info.putAll(baseDao.queryForMap(sql.toString(), params));
        //查询配置信息
        sql.setLength(0);
        sql.append(" select ciname,searchcode,lv1_name cate1_name,lv2_name cate2_name,lv3_name cate3_name,position from cmdb_ci_base where id=:ci_id ");
        info.putAll(baseDao.queryForMap(sql.toString(), params));

        return info;
    }

	@Override
	public List<Map> getSparePartOverageSearchListData(
			Map<String, String> params) {
		 StringBuilder sql = new StringBuilder(" select if(warranty_end>date_format(now(),'%Y%m%d'),1,2) over_flag,a.warranty_end,a.id,b.lv1_name,b.lv2_name,b.lv3_name,a.name,a.asset_no,a.entity_no,a.model_no,a.state, " +
	                " a.location,a.gmt_create  from spare_part a ,cmdb_ci_category b " +
	                " where a.cate_id = b.id and b.cate_type=2  and b.state=1 and a.id=:id");
	        params.put("id", params.get("id"));
	        return baseDao.queryForList(sql.toString(), params);
	}

	/**
	 * 供应商处理记录
	 * @param id
	 * @return
	 */
	   @Override
	    public List querySupplierModifyRecord(String id) {
           String sqlString="select * from SUPPLIER_MODIFY_RECORD where supplier_id=:id ORDER BY GMT_MODIFY DESC";
           Map map=new HashMap();
           map.put("id", id);
           List list=baseDao.queryForList(sqlString,map);
	       return list;
	    }

	@Override
	public void delSparePart(Map param) {
		String sql="update spare_part set ky_state=:flag where id=:id";
		baseDao.execute(sql, param);
	}
}