package com.teenysoft.t8.queryCenter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.hibernate.Query;
import com.boarsoft.common.Util;
import com.boarsoft.common.biz.SimpleBizImpl;
import com.boarsoft.common.util.BeanUtil;
import com.boarsoft.common.util.JsonUtil;
import com.teenysoft.common.bean.StatData;
import com.teenysoft.t8.bean.ModuleUtil;
import com.teenysoft.t8.bean.PayAnalysisBean;
import com.teenysoft.t8.entity.Account;
import com.teenysoft.t8.entity.BasClients;
import com.teenysoft.t8.entity.BasProducts;
import com.teenysoft.t8.entity.BasProductsBarcode;
import com.teenysoft.t8.entity.BasProductsUnitPrice;
import com.teenysoft.t8.entity.BasRegion;
import com.teenysoft.t8.entity.BasStorageData;
import com.teenysoft.t8.entity.BatchNoTrackingQuery;
import com.teenysoft.t8.entity.BillAnalysisBean;
import com.teenysoft.t8.entity.BillBuy;
import com.teenysoft.t8.entity.BillIdx;
import com.teenysoft.t8.entity.CustomerSalesSortDetail;
import com.teenysoft.t8.entity.Dept;
import com.teenysoft.t8.entity.Employee;
import com.teenysoft.t8.entity.GoodsSaleBatchSummaryReport;
import com.teenysoft.t8.entity.Location;
import com.teenysoft.t8.entity.ProductsType;
import com.teenysoft.t8.entity.PurchaseOrderStatReport;
import com.teenysoft.t8.entity.PurchaseSalesTypeAnalysisReport;
import com.teenysoft.t8.entity.QuoteBillsQueryReport;
import com.teenysoft.t8.entity.StockDetailAccountReport;
import com.teenysoft.t8.entity.StockStatusDistribute;
import com.teenysoft.t8.entity.StockStatusDistributeTR;
import com.teenysoft.t8.entity.SysMonthlyBalancesInfo;
public class QueryCenterBizImpl extends SimpleBizImpl implements QueryCenterBiz {
/**
* 商品采购订单统计
*/
@SuppressWarnings("unchecked")
public String productPurchaseOrdersStat(Map<String, Object> session, List<Object> ll, int print,
String cid, String pid, String eid, String inputId, String did, String sid, String orderType,
String orderQueryType, int unitType, String priceType, String startDate, String endDate,
String parentId, String listWay, int pageNo, int pageSize) {
String namedSQL = "productOrdersStatSQL";
String result = null;
try{
if (orderQueryType.equals("purchase")) {
namedSQL = "productOrdersStatSQL";
} else {
namedSQL = "productSaleOrdersStatSQL";
}
Query query = dao.createNamedSQLQuery(namedSQL);
String sql = query.getQueryString();
String folderCount = "";
if (Util.strIsEmpty(cid)) {
cid = "%";
}
if (Util.strIsEmpty(pid)) {
pid = "%";
}
if (Util.strIsEmpty(eid)) {
eid = "%";
}
if (Util.strIsEmpty(inputId)) {
inputId = "%";
}
if (Util.strIsEmpty(did)) {
did = "%";
}
if (Util.strIsEmpty(sid)) {
sid = "%";
}
if (Util.strIsEmpty(inputId)) {
cid = "%";
}
if (orderType.equals("allOrder")) {
orderType = "in (0,1,2,3,4)";
} else if (orderType.equals("doneOrder")) {
orderType = "<>0";
} else if (orderType.equals("notDoneOrder")) {
orderType = "=0";
}
if (listWay.equals("showAll")) {
folderCount = "-1";
parentId = "%";
} else {
folderCount = "%";
if (Util.strIsEmpty(parentId)) {
parentId = "root";
}
}
sql = sql.replaceAll("cid", cid);
sql = sql.replaceAll("pid", pid);
sql = sql.replaceAll("eid", eid);
sql = sql.replaceAll("inputId", inputId);
sql = sql.replaceAll("did", did);
sql = sql.replaceAll("sid", sid);
sql = sql.replaceAll("orderType", orderType);
sql = sql.replaceAll("startDate", startDate);
sql = sql.replaceAll("endDate", endDate);
sql = sql.replaceAll("folderCount", folderCount);
sql = sql.replaceAll("parentId", parentId);
List<Object[]> l;
if (print == 1)
l = dao.listBySQL(sql);
else
l = dao.listBySQL(sql, pageNo, pageSize);
for (Object[] o : l) {
for (int i = 0; i < o.length; i++) {
if (o[i] == null) {
o[i] = "";
}
}
PurchaseOrderStatReport p = new PurchaseOrderStatReport();
p.setProduct((BasProducts) dao.get(BasProducts.class, (String)o[0]));
p.setUnit1Name((String)o[17]);
p.setUnit1Ratio(Util.object2double(o[18], 0));
p.setUnit2Name((String)o[19]);
p.setUnit2Ratio(Util.object2double(o[20], 0));
p.setUnit3Name((String)o[21]);
p.setUnit3Ratio(Util.object2double(o[22], 0));
p.setUnit4Name((String)o[23]);
p.setUnit4Ratio(Util.object2double(o[24], 0));
p.setOrderPrice(Util.object2double(o[25], 0));
p.setOrderQty(Util.object2double(o[26], 0));
p.setDoneQty(Util.object2double(o[27], 0));
p.setNotDoneQty(Util.object2double(o[28], 0));
p.setBeyondQty(Util.object2double(o[29], 0));
p.setForcedQty(Util.object2double(o[30], 0));
p.setStockQty(Util.object2double(o[37], 0));
// 根据不同的单位显示明细商品数量
if (Util.object2int(o[14], 0) == -1) {
double ratio = 1;
if (unitType == 2 && p.getUnit2Ratio() > 0) {
ratio = p.getUnit2Ratio();
} else if (unitType == 3 && p.getUnit3Ratio() > 0) {
ratio = p.getUnit3Ratio();
} else if (unitType == 4 && p.getUnit4Ratio() > 0) {
ratio = p.getUnit4Ratio();
}
p.setOrderQty(Util.object2double(o[26], 0) / ratio);
p.setDoneQty(Util.object2double(o[27], 0) / ratio);
p.setNotDoneQty(Util.object2double(o[28], 0) / ratio);
p.setBeyondQty(Util.object2double(o[29], 0) / ratio);
p.setForcedQty(Util.object2double(o[30], 0) / ratio);
p.setStockQty(Util.object2double(o[37], 0) / ratio);
}
// 根据不同价格方式显示金额,默认为含税单价,需要根据条件转换税后单价
p.setOrderTotal(Util.object2double(o[31], 0));
p.setOrderPrice(p.getOrderTotal() / p.getOrderQty());
p.setDoneTotal(Util.object2double(o[32], 0));
p.setNotDoneTotal(Util.object2double(o[33], 0));
p.setBeyondTotal(Util.object2double(o[34], 0));
p.setForcedTotal(Util.object2double(o[35], 0));
p.setRetailTotal(Util.object2double(o[36], 0));
if (priceType.equals("discountPrice")) {
p.setOrderPrice(Util.object2double(o[38], 0));
p.setOrderTotal(Util.object2double(o[39], 0));
p.setDoneTotal(Util.object2double(o[40], 0));
p.setNotDoneTotal(Util.object2double(o[41], 0));
p.setBeyondTotal(Util.object2double(o[42], 0));
}
p.getProduct().setBarCodes(null);
p.getProduct().setUnitPrices(null);
ll.add(p);
}
// 打印时返回null
if (print != 1){
String json = JsonUtil.from(ll, new String[] { "barCodes", "cacheSum", "unitPrices" });
int total = Util.object2int(dao.getValueBySQL(String.format("select count(*) from (%1$s)vw",sql)),0);
int pageCount = total % pageSize == 0 ? total/pageSize:total/pageSize+ 1;;
result = String.format("{pageNo:'%1$s',pageSize:'%2$s',pageCount:'%3$s',total:'%4$s','list':%5$s}",
pageNo,pageSize,pageCount,total,json.trim());
}
}catch(Exception e){
log.error(e,e);
}
return result;
}
/**
* 按供应商采购订单统计
*/
@SuppressWarnings("unchecked")
public String clientPurchaseOrdersStat(List<Object> ll, int print, String cid, String pid, String eid,
String inputId, String did, String sid, String orderType, String orderQueryType,
String priceType, String startDate, String endDate, String parentId, String listWay,
int pageNo, int pageSize) {
String namedSQL = "clientOrdersStatSQL";
String result = null;
try{
if (orderQueryType.equals("purchase")) {
namedSQL = "clientOrdersStatSQL";
} else {
namedSQL = "clientSalesOrdersStatSQL";
}
Query query = dao.createNamedSQLQu