package nc.bs.pu.wgpt.process;

import java.util.ArrayList;
import java.util.List;

import nc.bs.dao.BaseDAO;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.net.servlet.IReturnStatus;
import nc.util.uap.wgpt.WuYeWgptUtil;
import nc.vo.esb.ResultEsbVO;
import nc.vo.esb.ResultVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDouble;
import nc.vo.tmpub.util.MathUtil;
import nc.vo.wgpt.log.WuYeWgptLogVO;
import uap.json.JSONObject;
/**
 * 【网购平台->NC】新加的类，采购平台查询库存量
 * @author 符耀铭
 * @time 2019-4-29
 *
 */
public class QueryInventoryByConditionProcess {

	
	public String queryInventoryByCondition(JSONObject jsonobj) throws BusinessException{

		String rsdata = null;
		String nc_id = null;
		Object orderno = null;
		ResultEsbVO<ResultVO> esb = new ResultEsbVO<ResultVO>();
		ResultVO result = new ResultVO();
		result.setResult("");
		esb.setResultInfo(result);
		try {
			if (jsonobj == null || "".equals(jsonobj)) {// 无Post提交值
				result.setCode(IReturnStatus.FAILING);// 失败
				result.setMessage("Post所提交请求Json格式错误或者无值!");
				WuYeWgptUtil.createResultEsbVO(esb);
				esb.getEsbInfo().setReturnStatus(IReturnStatus.ESB_FAILING);
				esb.getEsbInfo().setReturnMsg("失败");
				esb.setResultInfo(result);
			} else {
				JSONObject reqJsonObject=jsonobj.getJSONObject("requestInfo");
				Object skucode =reqJsonObject.get("skucode");
				Object orgcode=reqJsonObject.get("orgcode");
				Object materialcode =reqJsonObject.get("materialcode");
				StringBuffer errStringBuffer=new StringBuffer();
				
				if(null==skucode||"".equals(skucode.toString().trim())){
					errStringBuffer.append("sku码不能为空!");
				}
				
				if(null==orgcode||"".equals(orgcode.toString().trim())){
					errStringBuffer.append("组织不能为空!");
				}
				
				if(null==materialcode||"".equals(materialcode.toString().trim())){
					errStringBuffer.append("物料编码不能为空!");
				}
				
				if(errStringBuffer.length()>0){
					throw new Exception(errStringBuffer.toString());
				}
				
				BaseDAO baseDAO=new BaseDAO();
				UFDouble incomingSum=UFDouble.ZERO_DBL;
				UFDouble outboundSum=UFDouble.ZERO_DBL;
				 List list=new ArrayList();
				//采购入库单
				String purchaseinSql="select pb.nassistnum\n" +
								"  from ic_purchasein_h ph\n" + 
								" inner join ic_purchasein_b pb\n" + 
								"    on ph.cgeneralhid = pb.cgeneralhid\n" + 
								" left join org_orgs org\n" + 
								"    on ph.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on pb.cmaterialvid = ma.pk_material\n"+
								" where ph.fbillflag =3 and nvl(ph.dr,0)=0 and nvl(pb.dr,0)=0 and  org.code =? and pb.vbdef19=? and ma.code=?";
				SQLParameter sqlParameter=new SQLParameter();
				sqlParameter.addParam(orgcode);
				sqlParameter.addParam(skucode);
				sqlParameter.addParam(materialcode);
				list =(List) baseDAO.executeQuery(purchaseinSql,sqlParameter, new ColumnListProcessor());
				
				incomingSum=incomingSum.add(addNumSum(list));
				
				purchaseinSql="select itsb.nassistnum\n" +
								"  from ic_transin_h itsh\n" + 
								" inner join ic_transin_b itsb\n" + 
								"    on itsh.cgeneralhid = itsb.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on itsh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on itsb.cmaterialvid = ma.pk_material\n"+
								" where itsh.fbillflag =3 and nvl(itsh.dr,0)=0 and nvl(itsb.dr,0)=0 and  org.code =? and itsb.vbdef19=? and ma.code=?";
				list =(List) baseDAO.executeQuery(purchaseinSql,sqlParameter, new ColumnListProcessor());
				
				incomingSum=incomingSum.add(addNumSum(list));

				purchaseinSql="select fdb.nassistnum\n" +
								"  from ic_finprodin_h fdh\n" + 
								" inner join ic_finprodin_b fdb\n" + 
								"    on fdh.cgeneralhid = fdb.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on fdh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on fdb.cmaterialvid = ma.pk_material\n"+
								" where  fdh.fbillflag =3 and nvl(fdh.dr,0)=0 and nvl(fdb.dr,0)=0 and  org.code =? and fdb.vbdef19=? and ma.code=?";
				list =(List) baseDAO.executeQuery(purchaseinSql,sqlParameter, new ColumnListProcessor());
				
				incomingSum=incomingSum.add(addNumSum(list));
				
				purchaseinSql="select glb.nassistnum\n" +
								"  from ic_generalin_h glh\n" + 
								" inner join ic_generalin_b glb\n" + 
								"    on glh.cgeneralhid = glb.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on glh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on glb.cmaterialvid = ma.pk_material\n"+
								" where glh.fbillflag=3  and nvl(glh.dr,0)=0 and nvl(glb.dr,0)=0 and  org.code =? and glb.vbdef19=? and ma.code=?";
				
				list =(List) baseDAO.executeQuery(purchaseinSql,sqlParameter, new ColumnListProcessor());
				
				incomingSum=incomingSum.add(addNumSum(list));
				
				String generaloutSql="select spb.nassistnum\n" +
								"  from ic_sapply_h sph\n" + 
								" inner join ic_sapply_b spb\n" + 
								"    on sph.cgeneralhid = spb.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on sph.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on spb.cmaterialvid = ma.pk_material\n"+
								" where sph.fbillflag=3 and nvl(sph.dr,0)=0 and nvl(spb.dr,0)=0 and  org.code =? and spb.vbdef16=? and ma.code=?";
				list =(List) baseDAO.executeQuery(generaloutSql,sqlParameter, new ColumnListProcessor());
				
				outboundSum=outboundSum.add(addNumSum(list));
				
				generaloutSql="select tob.nassistnum\n" +
								"  from ic_transout_h toh\n" + 
								" inner join ic_transout_b tob\n" + 
								"    on toh.cgeneralhid = tob.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on toh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on tob.cmaterialvid = ma.pk_material\n"+
								" where toh.fbillflag =3  and nvl(toh.dr,0)=0 and nvl(tob.dr,0)=0 and  org.code =? and tob.vbdef16=? and ma.code=?";
				list =(List) baseDAO.executeQuery(generaloutSql,sqlParameter, new ColumnListProcessor());
				
				outboundSum=outboundSum.add(addNumSum(list));
				
				generaloutSql="select mib.nassistnum\n" +
								"  from ic_material_h mih\n" + 
								" inner join ic_material_b mib\n" + 
								"    on mih.cgeneralhid = mib.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on mih.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on mib.cmaterialvid = ma.pk_material\n"+
								" where mih.fbillflag =3  and nvl(mih.dr,0)=0 and nvl(mib.dr,0)=0 and  org.code =?  and  mib.vbdef19=? and ma.code=?";
				list =(List) baseDAO.executeQuery(generaloutSql,sqlParameter, new ColumnListProcessor());
				
				outboundSum=outboundSum.add(addNumSum(list));
				
				generaloutSql="select gob.nassistnum\n" +
								"  from ic_generalout_h goh\n" + 
								" inner join ic_generalout_b gob\n" + 
								"    on goh.cgeneralhid = gob.cgeneralhid\n" + 
								" inner join org_orgs org\n" + 
								"    on goh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on gob.cmaterialvid = ma.pk_material\n"+
								" where goh.fbillflag =3  and nvl(goh.dr,0)=0 and nvl(gob.dr,0)=0 and  org.code =?  and  gob.vbdef16=? and ma.code=?";
				list =(List) baseDAO.executeQuery(generaloutSql,sqlParameter, new ColumnListProcessor());
				
				outboundSum=outboundSum.add(addNumSum(list));

				generaloutSql="select bob.nassistnum\n" +
								"  from ic_borrowout_h boh\n" + 
								" inner join ic_borrowout_b bob\n" + 
								"    on boh.cgeneralhid = bob.cgeneralhid\n" + 
								"  left join org_orgs org\n" + 
								"    on boh.pk_org = org.pk_org\n" + 
								" left join bd_material ma \n" +
								"    on bob.cmaterialvid = ma.pk_material\n"+
								" where boh.fbillflag =3 and nvl(boh.dr,0)=0 and nvl(bob.dr,0)=0 and  org.code =?  and  bob.vbdef16=? and ma.code=?";
				
				list =(List) baseDAO.executeQuery(generaloutSql,sqlParameter, new ColumnListProcessor());
				
				outboundSum=outboundSum.add(addNumSum(list));
				
				result.setResult(incomingSum.sub(outboundSum));
				
				result.setCode(IReturnStatus.SUCCEED);// 成功
				result.setMessage("查询物料库存量成功！");

				WuYeWgptUtil.createResultEsbVO(esb);
				esb.setResultInfo(result);
			}
		} catch (Exception e) {
			result.setCode(IReturnStatus.ERROR);// 异常
			result.setError(WuYeWgptUtil.getExceptionDetail(e));
			result.setMessage(result.getError());
			WuYeWgptUtil.createResultEsbVO(esb);// 初始化响应对象
			esb.getEsbInfo().setReturnStatus(IReturnStatus.ESB_ERROR);
			esb.getEsbInfo().setReturnMsg("异常");
			esb.setResultInfo(result);// 设置响应内容
		} finally {
			rsdata = com.alibaba.fastjson.JSONObject.toJSONString(esb);

			WuYeWgptLogVO logVO = new WuYeWgptLogVO();
			logVO.setNc_id(nc_id);
			logVO.setOther_id(orderno + "");
			logVO.setBillno("");
			logVO.setTranstypecode("");
			logVO.setItfcode("WGPT-NC-004");
			logVO.setItfname("【网购平台->NC】查询物料库存量");
			logVO.setBilldata(jsonobj == null ? "" : jsonobj.toString());
			logVO.setRsdata(rsdata);
			logVO.setMsg(null);
			logVO.setIssuccess(IReturnStatus.SUCCEED.equals(result.getCode()) ? "Y" : "N");

			WuYeWgptUtil.getIWuYeWgptLogService().insertLog_RequiresNew(logVO);
		}
		return rsdata;
	}
	
	public UFDouble addNumSum(List list){
		int len =list.size();
		UFDouble sumDouble=UFDouble.ZERO_DBL;
		for(int i=0;i<len;i++){
			Object num =list.get(i);
			sumDouble=sumDouble.add(MathUtil.getUFDouble(num));
		}
		return sumDouble;
	}
}
