cucu_YOS/src/custom/restcontroller/webmanage/sale/invbal/invbal.java

package restcontroller.webmanage.sale.invbal;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import beans.itemclass.ItemClass;
import beans.parameter.Parameter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import beans.invbal.Invbal;
import com.sun.tools.internal.xjc.reader.gbind.ElementSets;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING_CLEAN;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import common.data.db.DBConnect;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;
import utility.ERPDocking;

@API(title = "库存管理")
public class invbal extends Controller {

	public invbal(JSONObject content) throws YosException {
		super(content);
		// TODO Auto-generated constructor stub
	}

	public static HashMap<String, Long> itemhashmap = new HashMap<>(16);
	public static HashMap<Long, String> itemnomap = new HashMap<>(16);
	
	
    @API(title = "从erp新增更新库存", apiversion = R.ID20230222203603.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230222203803.class})
	public String caculate() throws YosException {
		JSONArray itemids = content.getJSONArray("itemids");
		boolean rightnow = content.getBooleanValue("rightnow");
		JSONArray erpitemnos = new JSONArray();
		if (itemnomap.isEmpty()) {
			Rows rows = dbConnect
					.runSqlQuery("select itemid,erpitemno from plm_itemextend where siteid='" + siteid + "'");
			for (Row row : rows) {
				itemnomap.put(row.getLong("itemid"), row.getString("erpitemno"));
			}
		}

		for (Object object : itemids) {
			long itemid =((Integer)object).longValue();
			if (itemnomap.containsKey(itemid)) {
				erpitemnos.add(itemnomap.get(itemid));
			} else {
				Rows rows = dbConnect.runSqlQuery("select erpitemno from plm_itemextend where itemid='" + itemid
						+ "' and siteid='" + siteid + "'");
				if (!rows.isEmpty()) {
					erpitemnos.add(rows.get(0).getString("erpitemno"));
					itemnomap.put(itemid, rows.get(0).getString("erpitemno"));
				}
			}
		}
		 Invbal.caculate_erpitemno(this, erpitemnos, rightnow);
		 return getSucReturnObject().toString();
	}
    
    @API(title = "从erp新增更新库存(站点全部)", apiversion = R.ID20230222203703.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230222203803.class})
    public  String caculateAll() throws YosException {
		if (itemnomap.isEmpty()) {
			Rows rows = dbConnect
					.runSqlQuery("select itemid,erpitemno from plm_itemextend where siteid='" + siteid + "'");
			for (Row row : rows) {
				itemnomap.put(row.getLong("itemid"), row.getString("erpitemno"));
			}
		}
		Invbal.caculate_erpitemno(this);
		 return getSucReturnObject().toString();
	}
    
    
    @API(title = "查询库存列表", apiversion = R.ID20230222203803.v1.class)
    public String queryInvbalList() throws YosException {
    	boolean istotal = content.getBooleanValue("istotal");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.erpitemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.erpitemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory;
        if(istotal) {
        	 sqlFactory = new SQLFactory(this, "销售库存列表查询", pageSize, pageNumber, pageSorting);
        }else {
        	 sqlFactory = new SQLFactory(this, "即时库存列表查询", pageSize, pageNumber, pageSorting);
		}
        
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        return getSucReturnObject().setData(rows).toString();
    }

	@API(title = "查询Erp库存列表", apiversion = R.ID20230408091703.v1.class)
	public String queryErpInvbalList() throws YosException {
		String itemno = content.getStringValue("itemno");
		String itemname = content.getStringValue("itemname");
		String agentinfo = content.getStringValue("agentinfo");
		boolean all = content.getBooleanValue("all");
		JSONArray itemclassids = content.getJSONArray("itemclassids");
		String where ="1=2";
		SQLFactory sqlFactory =new SQLFactory(this,"订单明细未发商品列表", pageSize, pageNumber, pageSorting);
		if(!StringUtils.isBlank(itemno)){
			sqlFactory=new SQLFactory(this,"商品列表", pageSize, pageNumber, pageSorting);
			where=where+" or t2.itemno like '%"+itemno+"%'";
		}
		if(!StringUtils.isBlank(itemname)){
			sqlFactory=new SQLFactory(this,"商品列表", pageSize, pageNumber, pageSorting);
			where=where+" or t2.itemname like '%"+itemname+"%' ";
		}
		if (itemclassids.size() > 0) {
			sqlFactory=new SQLFactory(this,"商品列表", pageSize, pageNumber, pageSorting);
			ArrayList<Long> itemclassList = new ArrayList<Long>();
			for (Object object : itemclassids) {
				//System.out.println(row.getLong("itemclassid"));

				long itemclassid = Long.valueOf(String.valueOf(object));
				itemclassList.add(itemclassid);
				itemclassList.addAll(ItemClass.getSubItemClassIds(this,itemclassid));
			}
			String sql = " or t2.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + itemclassList + "  and siteid='" + siteid + "')";
			sql = sql.replace("[", "(").replace("]", ")");
			where=where+sql;
		}
		if(all){
			where=where+" or 1=1 ";
		}
		if(!StringUtils.isBlank(agentinfo)){
			sqlFactory =new SQLFactory(this,"订单明细未发商品列表", pageSize, pageNumber, pageSorting);
			where ="1=2";
			where=where+" or t6.agentnum like '%"+agentinfo+"%' or t5.enterprisename like '%"+agentinfo+"%' ";
		}
		sqlFactory.addParameter("siteid", siteid);
		sqlFactory.addParameter_SQL("where", where);
		 System.out.println(sqlFactory.getSQL());
		Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
		Rows rowsitemclass = dbConnect.runSqlQuery(" select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 LEFT JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='" + siteid + "'");
		RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");

		RowsMap rowsMap =rows.toRowsMap("itemno");


		SQLFactory sqlFactory1 =new SQLFactory(this,"商品发货数量汇总");
		sqlFactory1.addParameter("siteid", siteid);
		where=" 1=1 ";
		if(!StringUtils.isBlank(agentinfo)){
			where=where+" and t6.agentnum like '%"+agentinfo+"%' or t5.enterprisename like '%"+agentinfo+"%' ";
		}
		sqlFactory1.addParameter_SQL("where", where);
		Rows sumQtyRows = dbConnect.runSqlQuery(sqlFactory1);
		RowsMap sumQtyRowsMap= sumQtyRows.toRowsMap("itemno");

        SQLFactory sqlFactory2 =new SQLFactory(this,"商品未发货数量汇总");
        sqlFactory2.addParameter("siteid", siteid);
		where=" 1=1 ";
		if(!StringUtils.isBlank(agentinfo)){
			where=where+" and t6.agentnum like '%"+agentinfo+"%' or t5.enterprisename like '%"+agentinfo+"%' ";
		}
		sqlFactory2.addParameter_SQL("where", where);
        Rows sumUnQtyRows = dbConnect.runSqlQuery(sqlFactory2);
        RowsMap sumUnQtyRowsMap= sumUnQtyRows.toRowsMap("itemno");


		ERPDocking erpDocking =new ERPDocking();
		JSONArray jsonArray =new JSONArray();
		if(rows.toJsonArray("itemno").size()!=0){
			if (Parameter.get("system.ccerp_dockswitch").equalsIgnoreCase("true")) {
				jsonArray=erpDocking.getErpIcinvbalRows(200000, 1,rows.toJsonArray("itemno"));
			}
		}

		if(!jsonArray.isEmpty()){
			for (Object object:jsonArray) {
				JSONObject jsonObject =(JSONObject)object;
				if(rowsMap.containsKey(jsonObject.getString("fitemno"))){
					if(rowsMap.get(jsonObject.getString("fitemno")).isNotEmpty()){
						rowsMap.get(jsonObject.getString("fitemno")).get(0).put("invbalqty", jsonObject.getBigDecimalValue("fqty"));
				}
				}
			}
		}
		for (Row row:rows) {
			String itemclass=itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().substring(1,itemclassRowsMap.get(row.getString("itemid")).toJsonArray("itemclassname").toString().length()-1);
			row.put("itemclass", itemclass.replaceAll("\"", ""));
			if(sumQtyRowsMap.get(row.getString("itemno")).isNotEmpty()){
				row.put("unsoldqty", sumQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty"));
			}else {
				row.put("unsoldqty", BigDecimal.ZERO);
			}
			if(sumUnQtyRowsMap.get(row.getString("itemno")).isNotEmpty()){
				row.put("undelqty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("undeliqty"));
				row.put("qty", sumUnQtyRowsMap.get(row.getString("itemno")).get(0).getBigDecimal("qty"));
			}else {
				row.put("undelqty", BigDecimal.ZERO);
				row.put("qty", BigDecimal.ZERO);
			}
			if(!row.containsKey("invbalqty")){
				row.put("invbalqty",BigDecimal.ZERO);
			}

		}
		return getSucReturnObject().setData(rows).toString();
	}
	@API(title = "查询指定商品对应的未发货订单明细", apiversion = R.ID20230408101803.v1.class)
	public String queryOrderDetailList() throws YosException {
		String itemno = content.getString("itemno");
		StringBuffer where = new StringBuffer(" 1=1 ");
		if (content.containsKey("where")) {
			JSONObject whereObject = content.getJSONObject("where");
			if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
				where.append(" and(");
				where.append("t5.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
				where.append("or t6.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
				where.append(")");
			}
		}
		SQLFactory sqlFactory =new SQLFactory(this,"未发货订单明细列表", pageSize, pageNumber, pageSorting);
		sqlFactory.addParameter("siteid", siteid);
		sqlFactory.addParameter("itemno", itemno);
		sqlFactory.addParameter_SQL("where", where);
		Rows rows = dbConnect.runSqlQuery(sqlFactory);
		return getSucReturnObject().setData(rows).toString();
	}

}