package com.uas.platform.b2b.openapi.service.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.uas.platform.b2b.core.util.DateUtils;
import com.uas.platform.b2b.dao.CommonDao;
import com.uas.platform.b2b.data.support.DS;
import com.uas.platform.b2b.data.support.DSUtils;
import com.uas.platform.b2b.data.support.SpObserver;
import com.uas.platform.b2b.openapi.model.Forecast;
import com.uas.platform.b2b.openapi.model.IO;
import com.uas.platform.b2b.openapi.model.Sale;
import com.uas.platform.b2b.openapi.model.Stock;
import com.uas.platform.b2b.openapi.model.TEMP_DATA;
import com.uas.platform.b2b.openapi.model.TEMP_FORECAST;
import com.uas.platform.b2b.openapi.model.TEMP_IO;
import com.uas.platform.b2b.openapi.model.TEMP_SALE;
import com.uas.platform.b2b.openapi.service.CustDataService;

/**
 * 使用UAS系统的客户的数据
 * 
 * @author yingp
 * 
 */
@Service
public class UASCustDataService implements CustDataService {

	@Autowired
	private CommonDao commonDao;

	private static final String manageDataSource = "manageDataSource";

	/**
	 * 按UU查找客户账套的连接参数
	 * 
	 * @param uu
	 * @return
	 */
	private DS getDSFromManage(long uu) {
		String currDs = SpObserver.getSp();
		try {
			SpObserver.putSp(manageDataSource);
			return commonDao
					.queryBean(
							"select uu as id,case when platform = 'SAAS' then '10.10.100.200' else db_host end as host,case when platform = 'SAAS' then 1521 else db_port end as port,db_user as username,db_pwd as password from masters where (platform = 'SAAS' or (platform = 'ERP' and install_type = 'uas1.0' and db_host is not null and db_port is not null and db_user is not null and db_pwd is not null)) and uu=?",
							DS.class, uu);
		} finally {
			SpObserver.putSp(currDs);
		}
	}

	@Override
	public List<Stock> findProductStockInfo(long custId, String brands) {
		DS ds = getDSFromManage(custId);
		// 先校验是否可以连接
		if (ds != null && DSUtils.isConnectable(commonDao.getJdbcTemplate(), ds)) {
			String currDs = SpObserver.getSp();
			try {
				// 切换到客户账套
				SpObserver.putSp(ds.getId());
				// A,B,C格式的数据改为'A','B','C'格式
				final String brandsWithFix = StringUtils.collectionToDelimitedString(Arrays.asList(brands.split(",")), ",", "'", "'");
				int nowMonth = DateUtils.getYearmonth(new Date());
				// 修改为按物料分组，防止物料本身的原厂型号或规格定义重复
				return commonDao
						.query("select brand,org_item,item_desc,item_spec,item_unit,sum(stock) as stock,sum(beginStock) as beginStock from (select pr_brand as brand,ifnull(pr_orispeccode,pr_spec) as org_item,pr_detail as item_desc,pr_spec as item_spec,pr_unit as item_unit,po_onhand as stock,(select pm_beginqty from v$productmonth where pr_code=pm_prodcode and pm_yearmonth=?) as beginStock from productonhand left join product on po_prodcode=pr_code where po_onhand > 0 and pr_brand in ("
								+ brandsWithFix
								+ ")) group by brand,org_item,item_desc,item_spec,item_unit order by brand,org_item,item_desc,item_spec,item_unit",
								Stock.class, nowMonth);
			} finally {
				SpObserver.putSp(currDs);
			}
		}
		return new ArrayList<Stock>();
	}

	@Override
	public List<Sale> findSales(long custId, String brands, String code, String startDate) {
		DS ds = getDSFromManage(custId);
		// 先校验是否可以连接
		if (ds != null && DSUtils.isConnectable(commonDao.getJdbcTemplate(), ds)) {
			String currDs = SpObserver.getSp();
			try {
				// 切换到客户账套
				SpObserver.putSp(ds.getId());
				// A,B,C格式的数据改为'A','B','C'格式
				final String brandsWithFix = StringUtils.collectionToDelimitedString(Arrays.asList(brands.split(",")), ",", "'", "'");
				StringBuffer sql = new StringBuffer(
						"select pr_brand as brand,ifnull(pr_orispeccode,pr_spec) as org_item,pr_detail as item_desc,pr_spec as item_spec,pr_unit as item_unit,to_char(sa_date,'yyyy-mm-dd') as \"DATE\",sd_qty as qty from sale left join saledetail on sa_id=sd_said left join product on sd_prodcode=pr_code where ");
				sql.append("sa_statuscode='AUDITED' and sa_date >= to_date('").append(startDate).append("','yyyy-mm-dd')");
				if (StringUtils.hasText(code)) {
                    sql.append(" and pr_orispeccode='").append(code).append("'");
                }
				sql.append(" and pr_brand in (").append(brandsWithFix).append(")");
				sql.append(" order by pr_brand,pr_spec,sa_date,sd_detno");
				return TEMP_SALE.reduce(commonDao.query(sql.toString(), TEMP_SALE.class));
			} finally {
				SpObserver.putSp(currDs);
			}
		}
		return new ArrayList<Sale>();
	}

	@Override
	public List<IO> findIOs(long custId, String brands, String code, String startDate) {
		DS ds = getDSFromManage(custId);
		// 先校验是否可以连接
		if (ds != null && DSUtils.isConnectable(commonDao.getJdbcTemplate(), ds)) {
			String currDs = SpObserver.getSp();
			try {
				// 切换到客户账套
				SpObserver.putSp(ds.getId());
				// A,B,C格式的数据改为'A','B','C'格式
				final String brandsWithFix = StringUtils.collectionToDelimitedString(Arrays.asList(brands.split(",")), ",", "'", "'");
				// {@code startDate}所处期间
				int month = DateUtils.getYearmonth(startDate);
				// 修改为按物料分组求和，防止物料本身的原厂型号或规格定义重复
				StringBuffer sql = new StringBuffer(
						"select case when ifnull(pd_inqty,0)<>0 then null else cu_kind end as end_item_fld,pr_brand as brand,ifnull(pr_orispeccode,pr_spec) as org_item,pr_detail as item_desc,pr_spec as item_spec,pr_unit as item_unit,to_char(pi_date,'yyyy-mm-dd') as \"DATE\",ifnull(pd_inqty,0)+ifnull(pd_outqty,0) as qty,case when ifnull(pd_inqty,0)<>0 then 'in' else 'out' end as type,pd_ordercode as po,to_char((case when pi_class in ('出货单','销售退货单') then (select sa_date from sale where sa_code=pd_ordercode) when pi_class in ('采购验收单','采购验退单') then (select pu_date from purchase where pu_code=pd_ordercode) end),'yyyy-mm-dd') as po_date,(select ifnull(sum(pm_beginqty),0) from v$productmonth left join product p2 on p2.pr_code=pm_prodcode where p2.pr_spec=p1.pr_spec and p2.pr_detail=p1.pr_detail and p2.pr_brand=p1.pr_brand and ifnull(p2.pr_orispeccode,' ')=ifnull(p1.pr_orispeccode,' ') and ifnull(p2.pr_unit,' ')=ifnull(p1.pr_unit,' ') and pm_yearmonth=?) as beginStock from prodinout left join prodiodetail on pi_id=pd_piid left join customer on pi_cardcode=cu_code left join product p1 on pd_prodcode=p1.pr_code where ");
				sql.append("pi_statuscode='POSTED' and pi_date >= to_date('").append(startDate).append("','yyyy-mm-dd')");
				if (StringUtils.hasText(code)) {
                    sql.append(" and pr_orispeccode='").append(code).append("'");
                }
				sql.append(" and pr_brand in (").append(brandsWithFix).append(")");
				sql.append(" order by pr_brand,pr_spec,pi_date,pd_pdno");
				// 无io部分
				StringBuffer noioSql = new StringBuffer(
						"select brand,code,title,spec,unit,sum(beginStock) as beginStock from (select pr_brand as brand,ifnull(pr_orispeccode,pr_spec) as code,pr_detail as title,pr_spec as spec,pr_unit as unit,pm_beginqty as beginStock from v$productmonth left join product on pm_prodcode=pr_code where pm_yearmonth=? and pm_beginqty > 0");
				noioSql.append(" and pr_brand in (").append(brandsWithFix).append(")");
				noioSql.append(" and not exists (select 1 from prodinout left join prodiodetail on pi_id=pd_piid where to_char(pi_date,'yyyymm')=pm_yearmonth and pi_statuscode='POSTED' and pd_prodcode=pm_prodcode))");
				noioSql.append(" group by brand,code,title,spec,unit order by brand,code,title,spec,unit");

				return TEMP_IO.reduce(commonDao.query(sql.toString(), TEMP_IO.class, month),
						commonDao.query(noioSql.toString(), TEMP_DATA.class, month));
			} finally {
				SpObserver.putSp(currDs);
			}
		}
		return new ArrayList<IO>();
	}

	@Override
	public List<Forecast> findForecasts(long custId, String brands, String code, String startDate) {
		DS ds = getDSFromManage(custId);
		// 先校验是否可以连接
		if (ds != null && DSUtils.isConnectable(commonDao.getJdbcTemplate(), ds)) {
			String currDs = SpObserver.getSp();
			try {
				// 切换到客户账套
				SpObserver.putSp(ds.getId());
				// A,B,C格式的数据改为'A','B','C'格式
				final String brandsWithFix = StringUtils.collectionToDelimitedString(Arrays.asList(brands.split(",")), ",", "'", "'");
				StringBuffer sql = new StringBuffer(
						"select pr_brand as brand,ifnull(pr_orispeccode,pr_spec) as org_item,pr_detail as item_desc,pr_spec as item_spec,pr_unit as item_unit,to_char(sf_date,'yyyy-mm-dd') as \"DATE\",sd_qty as qty,to_char(sd_needdate,'yyyy-mm-dd') as needDate,to_char(sd_enddate,'yyyy-mm-dd') as endDate from saleforecast left join saleforecastdetail on sf_id=sd_sfid left join customer on sd_custcode=cu_code left join product on sd_prodcode=pr_code where ");
				sql.append("sf_statuscode='AUDITED' and sf_date >= to_date('").append(startDate).append("','yyyy-mm-dd')");
				if (StringUtils.hasText(code)) {
                    sql.append(" and pr_orispeccode='").append(code).append("'");
                }
				sql.append(" and pr_brand in (").append(brandsWithFix).append(")");
				sql.append(" order by pr_brand,pr_spec,sf_date,sd_detno");
				return TEMP_FORECAST.reduce(commonDao.query(sql.toString(), TEMP_FORECAST.class));
			} finally {
				SpObserver.putSp(currDs);
			}
		}
		return new ArrayList<Forecast>();
	}

}
