package restcontroller.webmanage.sale.promotion; import beans.brand.Brand; import beans.data.BatchDeleteErr; import beans.datacontrllog.DataContrlLog; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import common.Controller; import common.YosException; import common.annotation.API; import common.annotation.CACHEING; import common.annotation.CACHEING_CLEAN; import common.data.*; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.xssf.usermodel.*; import restcontroller.R; import restcontroller.webmanage.sale.itemgroup.itemgroup; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.List; public class promotionItems extends Controller { public promotionItems(JSONObject arg0) throws YosException { super(arg0); // TODO Auto-generated constructor stub } @API(title = "商品列表查询", apiversion = R.ID20230102144603.v1.class) @CACHEING public String queryItemgList() throws YosException { /* * 过滤条件设置 */ String where = " 1=1 "; String where1 = " 1=1 "; Long sa_promotionid = content.getLong("sa_promotionid"); if (!beans.order.Order.getDefaultIsRepeatValue(siteid, "促销订单")) { where=where+" and t1.itemid not in (select itemid from sa_promotion_items where sa_promotionid="+sa_promotionid+") "; } if (content.containsKey("where")) { JSONObject whereObject = content.getJSONObject("where"); if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) { where = where + "and (t1.itemname like'%" + whereObject.getString("condition") + "%'" + " or t1.model like'%" + whereObject.getString("condition") + "%'" + " or t1.itemno like'%" + whereObject.getString("condition") + "%'" + ")"; } if (whereObject.containsKey("tradefield") && !whereObject.getJSONArray("tradefield").isEmpty()) { String where2 = " 1=1 "; if (whereObject.getJSONArray("tradefield").size() > 0) { for (Object object : whereObject.getJSONArray("tradefield")) { String str = String.valueOf(object); where2 = where2 + " or tradefield ='" + str + "' "; } } where = where + ("and t1.itemid in (SELECT itemid from plm_item_tradefield WHERE (" + where2 + ") and siteid = '" + siteid + "')"); } } JSONArray itemclassids = content.getJSONArray("itemclassids"); if (itemclassids.size() > 0) { String sql = " and t1.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + itemclassids + " and siteid='" + siteid + "')"; sql = sql.replace("[", "(").replace("]", ")"); where = where + sql; } Rows tradefieldrows = dbConnect.runSqlQuery("select tradefield from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid); JSONArray tradefield = new JSONArray(); if (!tradefieldrows.isEmpty()) { if (!StringUtils.isBlank(tradefieldrows.get(0).getString("tradefield"))) { tradefield = JSONArray.parseArray(tradefieldrows.get(0).getString("tradefield")); } } if (tradefield != null && !tradefield.isEmpty()) { for (Object object : tradefield) { String str = String.valueOf(object); where1 = where1 + " or tradefield ='" + str + "' "; } } // String hrid = content.getString("hrid"); SQLFactory sqlFactory = new SQLFactory(this, "商品查询", pageSize, pageNumber, pageSorting); sqlFactory.addParameter_SQL("where", where); sqlFactory.addParameter_SQL("where1", where1); sqlFactory.addParameter("sa_promotionid", sa_promotionid); sqlFactory.addParameter("siteid", siteid); //System.out.println(sqlFactory.getSQL()); Rows rows = dbConnect.runSqlQuery(sqlFactory); // 附件 ArrayList ids = rows.toArrayList("itemid", new ArrayList<>()); RowsMap attRowsMap = getAttachmentUrl("plm_item", ids); RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids); 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"); for (Row row : rows) { row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows())); row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows())); row.put("itemclass", itemclassRowsMap.get(row.getString("itemid"))); } return getSucReturnObject().setData(rows).toString(); } @API(title = "促销方案商品新增更新", apiversion = R.ID20230102142403.v1.class,intervaltime = 200) @CACHEING_CLEAN(apiversions = {R.ID20230102142503.v1.class, R.ID20230102144603.v1.class, R.ID20230102163103.v1.class}) public String insertormodify_promotionItems() throws YosException { Long sa_promotionid = content.getLong("sa_promotionid"); Long sa_promotion_itemgroupid = content.getLong("sa_promotion_itemgroupid"); JSONArray iteminfos = content.getJSONArray("iteminfos"); ArrayList sqlList = new ArrayList<>(); Rows rowscount = dbConnect .runSqlQuery("select status,sa_promotionid from sa_promotion where sa_promotionid=" + sa_promotionid); Rows itempricerows = dbConnect .runSqlQuery(" select pricegrade,price,itemid from sa_itemprice where siteid='" + siteid + "'"); RowsMap itempricerowsMap = itempricerows.toRowsMap("itemid"); if (!rowscount.isEmpty()) { if (!rowscount.get(0).getString("status").equals("新建")) { return getErrReturnObject().setErrMsg("非新建状态的促销方案无法新增修改").toString(); } } int i = 0; long[] sa_promotion_itemsid = createTableID("sa_promotion_items", iteminfos.size()); for (Object obj : iteminfos) { JSONObject iteminfo = (JSONObject) obj; Rows itemrows = dbConnect.runSqlQuery("select itemid,orderminqty,orderaddqty,itemno from plm_item where siteid='" + siteid + "'"); RowsMap itemrowsMap = itemrows.toRowsMap("itemid"); if (iteminfo.getLong("sa_promotion_itemsid") <= 0 || dbConnect .runSqlQuery("select sa_promotion_itemsid from sa_promotion_items where sa_promotion_itemsid=" + iteminfo.getLong("sa_promotion_itemsid")) .isEmpty()) { SQLFactory saleFactory = new SQLFactory(this, "促销方案商品新增"); saleFactory.addParameter("siteid", siteid); saleFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid[i]); saleFactory.addParameter("sa_promotionid", sa_promotionid); saleFactory.addParameter("sa_promotion_itemgroupid", sa_promotion_itemgroupid); saleFactory.addParameter("groupqty", iteminfo.getLong("groupqty")); saleFactory.addParameter("itemid", iteminfo.getLong("itemid")); saleFactory.addParameter("itemno", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getString("itemno")); if (itemrowsMap.containsKey(iteminfo.getString("itemid"))) { saleFactory.addParameter("orderaddqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderaddqty")); if (itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("packageqty").compareTo(BigDecimal.ZERO) > 0) { saleFactory.addParameter("orderminqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("packageqty")); } else { saleFactory.addParameter("orderminqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderminqty")); } } else { saleFactory.addParameter("orderaddqty", 1); saleFactory.addParameter("orderminqty", 1); } saleFactory.addParameter("islimit", iteminfo.getLong("islimit")); saleFactory.addParameter("signaturecode", iteminfo.getStringValue("signaturecode")); sqlList.add(saleFactory.getSQL()); Rows rows = itempricerowsMap.get(iteminfo.getLong("itemid")); if (!rows.isEmpty()) { for (Row row : rows) { SQLFactory itempriceFactory = new SQLFactory(this, "促销方案商品价格新增"); itempriceFactory.addParameter("siteid", siteid); itempriceFactory.addParameter("sa_promotion_itempriceid", createTableID("sa_promotion_itemprice")); itempriceFactory.addParameter("sa_promotionid", sa_promotionid); itempriceFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid[i]); itempriceFactory.addParameter("itemid", row.getLong("itemid")); itempriceFactory.addParameter("pricegrade", row.getBigDecimal("pricegrade")); itempriceFactory.addParameter("oldprice", row.getBigDecimal("price")); itempriceFactory.addParameter("price", row.getBigDecimal("price")); sqlList.add(itempriceFactory.getSQL()); } } i++; } else { SQLFactory saleFactory = new SQLFactory(this, "促销方案商品更新"); saleFactory.addParameter("sa_promotion_itemsid", iteminfo.getLong("sa_promotion_itemsid")); // saleFactory.addParameter("itemno", iteminfo.getString("itemno")); saleFactory.addParameter("siteid", siteid); saleFactory.addParameter("groupqty", iteminfo.getLong("groupqty")); saleFactory.addParameter("itemid", iteminfo.getLong("itemid")); saleFactory.addParameter("orderaddqty", iteminfo.getBigDecimal("orderaddqty")); saleFactory.addParameter("orderminqty", iteminfo.getBigDecimal("orderminqty")); saleFactory.addParameter("islimit", iteminfo.getLong("islimit")); saleFactory.addParameter("isonsale", iteminfo.getBooleanValue("isonsale")); saleFactory.addParameter("signaturecode", iteminfo.getStringValue("signaturecode")); sqlList.add(saleFactory.getSQL()); } } dbConnect.runSqlUpdate(sqlList); return queryPromotionItemsList(); } /** * 促销方案商品上下架 * * @return */ @API(title = "促销方案商品上下架", apiversion = R.ID20230927135803.v1.class) @CACHEING_CLEAN(apiClass = {promotionItems.class, promotion.class,restcontroller.sale.promotion.promotion.class}) public String release() throws YosException { ArrayList sqlList = new ArrayList<>(); JSONArray sa_promotion_itemsids = content.getJSONArray("sa_promotion_itemsids"); boolean isonsale = content.getBooleanValue("isonsale"); List list = sa_promotion_itemsids.toJavaList(Long.class); list.add(0l); Long[] stringArray = list.toArray(new Long[0]); SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组上架"); if(isonsale){ sqlFactoryupdate = new SQLFactory(this, "促销方案商品上架"); }else{ sqlFactoryupdate = new SQLFactory(this, "促销方案商品下架"); } sqlFactoryupdate.addParameter("siteid", siteid); sqlFactoryupdate.addParameter_in("sa_promotion_itemsids", stringArray); sqlList.add(sqlFactoryupdate.getSQL()); // //发送消息 // for (String id : stringArray) { // sendMsg(Long.parseLong(id)); // } String sql = "select t1.itemid,t1.orderminqty,t1.orderaddqty,t1.itemname,t2.sa_promotionid from plm_item t1 inner join sa_promotion_items t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t2.sa_promotion_itemsid in " + sa_promotion_itemsids; sql = sql.replace("[", "(").replace("]", ")"); Rows itemrows = dbConnect.runSqlQuery(sql); for (Row row :itemrows){ if(isonsale){ sqlList.add(DataContrlLog.createLog(this, "sa_promotion", row.getLong("sa_promotionid"), "促销方案商品【"+row.getString("itemname")+"】由"+username+"上架", "促销方案商品上下架").getSQL()); }else{ sqlList.add(DataContrlLog.createLog(this, "sa_promotion", row.getLong("sa_promotionid"), "促销方案商品【"+row.getString("itemname")+"】由"+username+"下架" , "促销方案商品上下架").getSQL()); } } dbConnect.runSqlUpdate(sqlList); return getSucReturnObject().toString(); } @API(title = "促销方案商品列表", apiversion = R.ID20230102142503.v1.class) @CACHEING public String queryPromotionItemsList() throws YosException { /* * 过滤条件设置 */ 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.itemname like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' "); where.append("or t2.model like'%").append(whereObject.getString("condition")).append("%' "); where.append(")"); } } Long sa_promotionid = content.getLong("sa_promotionid"); Long sa_promotion_itemgroupid = content.getLong("sa_promotion_itemgroupid"); // SQLFactory sqlFactory = new SQLFactory(this, "促销方案商品查询", pageSize, pageNumber, pageSorting); // sqlFactory.addParameter_SQL("where", where); // sqlFactory.addParameter("sa_promotionid", sa_promotionid); // sqlFactory.addParameter("sa_promotion_itemgroupid", sa_promotion_itemgroupid); // sqlFactory.addParameter("siteid", siteid); // Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); // QuerySQL querySQL = queryPromotionItemList(where.toString(),sa_promotionid,sa_promotion_itemgroupid); querySQL.setPage(pageSize, pageNumber); querySQL.setOrderBy(pageSorting); Rows rows = querySQL.query(); ArrayList ids = rows.toArrayList("itemid", new ArrayList<>()); // 附件 RowsMap attRowsMap = getAttachmentUrl("plm_item", ids); for (Row row : rows) { row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows())); } return getSucReturnObject().setData(rows).toString(); } //查询发货单列表 public QuerySQL queryPromotionItemList(String where,long sa_promotionid,long sa_promotion_itemgroupid) throws YosException { QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_promotion_items","sa_promotion_itemsid", "siteid","sa_promotionid","itemid","itemid","orderminqty","orderaddqty","saledqty","islimit","isonsale","sa_promotion_itemgroupid","groupqty","signaturecode"); querySQL.setTableAlias("t1"); querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid and t1.siteid = t2.siteid", "itemno", "itemname","spec","model"); querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t3", "t2.unitid = t3.unitid and t2.siteid = t3.siteid", "unitname"); querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_itemprice", "t4", "t1.sa_promotion_itemsid = t4.sa_promotion_itemsid and t1.sa_promotion_itemsid = t4.sa_promotion_itemsid and t1.siteid = t4.siteid and t4.pricegrade = 1"); querySQL.addQueryFields("oldprice1", "ifnull(t4.oldprice, 0)"); querySQL.addQueryFields("price1", "ifnull(t4.price, 0)"); querySQL.addQueryFields("sa_promotion_itempriceid1", "ifnull(t4.sa_promotion_itempriceid, 0)"); querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_itemprice", "t5", "t1.sa_promotion_itemsid = t5.sa_promotion_itemsid and t1.sa_promotion_itemsid = t5.sa_promotion_itemsid and t1.siteid = t5.siteid and t5.pricegrade = 2"); querySQL.addQueryFields("oldprice2", "ifnull(t5.oldprice, 0)"); querySQL.addQueryFields("price2", "ifnull(t5.price, 0)"); querySQL.addQueryFields("sa_promotion_itempriceid2", "ifnull(t5.sa_promotion_itempriceid, 0)"); querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_itemprice", "t6", "t1.sa_promotion_itemsid = t6.sa_promotion_itemsid and t1.sa_promotion_itemsid = t6.sa_promotion_itemsid and t1.siteid = t6.siteid and t6.pricegrade = 3"); querySQL.addQueryFields("oldprice3", "ifnull(t6.oldprice, 0)"); querySQL.addQueryFields("price3", "ifnull(t6.price, 0)"); querySQL.addQueryFields("sa_promotion_itempriceid3", "ifnull(t6.sa_promotion_itempriceid, 0)"); querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_itemprice", "t7", "t1.sa_promotion_itemsid = t7.sa_promotion_itemsid and t1.sa_promotion_itemsid = t7.sa_promotion_itemsid and t1.siteid = t7.siteid and t7.pricegrade = 4"); querySQL.addQueryFields("oldprice4", "ifnull(t7.oldprice, 0)"); querySQL.addQueryFields("price4", "ifnull(t7.price, 0)"); querySQL.addQueryFields("sa_promotion_itempriceid4", "ifnull(t7.sa_promotion_itempriceid, 0)"); querySQL.addJoinTable(JOINTYPE.left, "sa_promotion_itemprice", "t8", "t1.sa_promotion_itemsid = t8.sa_promotion_itemsid and t1.sa_promotion_itemsid = t8.sa_promotion_itemsid and t1.siteid = t8.siteid and t8.pricegrade = 5"); querySQL.addQueryFields("oldprice5", "ifnull(t8.oldprice, 0)"); querySQL.addQueryFields("price5", "ifnull(t8.price, 0)"); querySQL.addQueryFields("sa_promotion_itempriceid5", "ifnull(t8.sa_promotion_itempriceid, 0)"); querySQL.setWhere("t1.siteid", siteid); querySQL.setWhere("t1.sa_promotionid",sa_promotionid); querySQL.setWhere("t1.sa_promotion_itemgroupid",sa_promotion_itemgroupid); querySQL.setWhere(where); return querySQL; } @API(title = "删除明细", apiversion = R.ID20230102142603.v1.class) @CACHEING_CLEAN(apiversions = {R.ID20230102142503.v1.class, R.ID20230102144603.v1.class, R.ID20230102163103.v1.class}) public String deletemx() throws YosException { JSONArray sa_promotion_itemsids = content.getJSONArray("sa_promotion_itemsids"); BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_promotion_itemsids.size()); for (Object o : sa_promotion_itemsids) { long sa_promotion_itemsid = Long.parseLong(o.toString()); Rows RowsStatus = dbConnect.runSqlQuery( "select t1.sa_promotion_itemsid,t2.status,t1.sa_promotionid from sa_promotion_items t1 left join sa_promotion t2 on t1.sa_promotionid=t2.sa_promotionid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.sa_promotion_itemsid='" + sa_promotion_itemsid + "'"); if (RowsStatus.isNotEmpty()) { if (!RowsStatus.get(0).getString("status").equals("新建")) { batchDeleteErr.addErr(sa_promotion_itemsid, "非新建状态的促销方案商品无法删除"); continue; } } ArrayList list = new ArrayList<>(); SQLFactory deletesql = new SQLFactory("sql:delete from sa_promotion_items where siteid='" + siteid + "' and sa_promotion_itemsid=" + sa_promotion_itemsid); list.add(deletesql.getSQL()); list.add("delete from sa_promotion_itemprice where siteid='" + siteid + "' and sa_promotion_itemsid=" + sa_promotion_itemsid); dbConnect.runSqlUpdate(list); } return batchDeleteErr.getReturnObject().toString(); } @API(title = "促销活动商品导入模板",apiversion = R.ID20230510153404.v1.class) public String downloadPromotionExcel() throws YosException { ExcelFactory excelFactory = new ExcelFactory("促销活动商品导入"); XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1"); XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook(); CellStyle style = xssfFWorkbook.createCellStyle(); // 设置为文本格式，防止身份证号变成科学计数法 DataFormat format = xssfFWorkbook.createDataFormat(); style.setDataFormat(format.getFormat("@")); // 对单独某一列进行样式赋值，第一个参数为列数，第二个参数为样式 sheet.setDefaultColumnStyle(0, style); ExportExcel.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽 XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook); XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook); XSSFCellStyle titleCellStyle3 = ExportExcel.createBodyCellStyle(xssfFWorkbook); XSSFCellStyle titleCellStyle4 = ExportExcel.createTitleCellStyle3(xssfFWorkbook); ExportExcel.batchDetail(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3,titleCellStyle4, xssfFWorkbook);// 写入标题 Rows aa = uploadExcelToObs(excelFactory); String url = ""; if (!aa.isEmpty()) { url = aa.get(0).getString("url"); } return getSucReturnObject().setData(url).toString(); } @API(title = "促销活动商品导入",apiversion = R.ID20230510153504.v1.class) @CACHEING_CLEAN(apiversions = {R.ID20230102142503.v1.class, R.ID20230102144603.v1.class,R.ID20230102163103.v1.class}) public String uploadPromotionItems() throws YosException { Long sa_promotionid = content.getLong("sa_promotionid"); Rows promotion = dbConnect.runSqlQuery("select status from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid); if (promotion.isEmpty()) { return getErrReturnObject().setErrMsg("无效促销活动").toString(); } else if (!promotion.get(0).getString("status").equals("新建")) { return getErrReturnObject().setErrMsg("非新建状态促销活动无法导入").toString(); } ExcelFactory e; try { // 华为云 e = getPostExcelFactory(content.getLong("attachmentid")); // 本地 //e = getPostExcelFactory(); ArrayList keys = new ArrayList<>(); ArrayList sqllist = new ArrayList<>(); keys.add("itemno"); keys.add("orderminqty"); keys.add("orderaddqty"); keys.add("groupqty"); keys.add("price1"); keys.add("price2"); keys.add("price3"); keys.add("price4"); keys.add("price5"); keys.add("signaturecode"); HashMap cellMap = new HashMap<>(); cellMap.put("itemno", CellType.STRING); cellMap.put("orderminqty", CellType.STRING); cellMap.put("orderaddqty", CellType.STRING); cellMap.put("groupqty", CellType.STRING); cellMap.put("price1", CellType.STRING); cellMap.put("price2", CellType.STRING); cellMap.put("price3", CellType.STRING); cellMap.put("price4", CellType.STRING); cellMap.put("price5", CellType.STRING); cellMap.put("signaturecode", CellType.STRING); int i = 0; int a = 0; Rows rows = e.getSheetRows(0, keys,cellMap, 3); boolean iserr = false; Rows rowserr = new Rows(); Rows rowssuc = new Rows(); ArrayList itemnoList = rows.toArrayList("itemno"); SQLFactory sqlFactory = new SQLFactory(this, "商品等级价格查询"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter_in("itemno", itemnoList); RowsMap itemRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("itemno"); ArrayList inItemnoList = dbConnect.runSqlQuery("select t2.itemno from sa_promotion_items t1 inner join plm_item t2 on t1.siteid=t2.siteid and t1.itemid=t2.itemid where t1.siteid='" + siteid + "' and t1.sa_promotionid=" + sa_promotionid).toArrayList("itemno"); for (Row row : rows) { String itemno = row.getString("itemno"); String orderminqty = row.getString("orderminqty"); String orderaddqty = row.getString("orderaddqty"); String groupqty = row.getString("groupqty"); String price1 = row.getString("price1"); String price2 = row.getString("price2"); String price3 = row.getString("price3"); String price4 = row.getString("price4"); String price5 = row.getString("price5"); String signaturecode = row.getString("signaturecode"); if (StringUtils.isEmpty(itemno)) { iserr = true; row.put("msg", "产品编码不能为空"); rowserr.add(row); continue; } if (!itemRowsMap.containsKey(itemno)) { iserr = true; row.put("msg", "产品不存在"); rowserr.add(row); continue; } if(inItemnoList.contains(itemno)){ iserr = true; row.put("msg", "产品已存在促销活动"); rowserr.add(row); continue; } if(!isNumeric(price1)){ iserr = true; row.put("msg", "促销价格1不为数字格式，请检查"); rowserr.add(row); continue; } if(!isNumeric(price2)){ iserr = true; row.put("msg", "促销价格2不为数字格式，请检查"); rowserr.add(row); continue; } if(!isNumeric(price3)){ iserr = true; row.put("msg", "促销价格3不为数字格式，请检查"); rowserr.add(row); continue; } if(!isNumeric(price4)){ iserr = true; row.put("msg", "促销价格4不为数字格式，请检查"); rowserr.add(row); continue; } if(!isNumeric(price5)){ iserr = true; row.put("msg", "促销价格5不为数字格式，请检查"); rowserr.add(row); continue; } Row item = itemRowsMap.get(itemno).get(0); row.put("itemid", item.getLong("itemid")); if (StringUtils.isEmpty(orderminqty) || (new BigDecimal(orderminqty)).compareTo(BigDecimal.ZERO)==0) { row.put("orderminqty", item.getDouble("orderminqty")); } if (StringUtils.isEmpty(orderaddqty) || (new BigDecimal(orderaddqty)).compareTo(BigDecimal.ZERO)==0) { row.put("orderaddqty", item.getDouble("orderaddqty")); } if (StringUtils.isEmpty(groupqty) || (new BigDecimal(groupqty)).compareTo(BigDecimal.ZERO)==0) { row.put("islimit", "0"); }else { row.put("islimit", "1"); } if (StringUtils.isEmpty(price1) || (new BigDecimal(price1)).compareTo(BigDecimal.ZERO)==0) { row.put("price1", item.getDouble("price1")); } if (StringUtils.isEmpty(price2) || (new BigDecimal(price2)).compareTo(BigDecimal.ZERO)==0) { row.put("price2", item.getDouble("price2")); } if (StringUtils.isEmpty(price3) || (new BigDecimal(price3)).compareTo(BigDecimal.ZERO)==0) { row.put("price3", item.getDouble("price3")); } if (StringUtils.isEmpty(price4) || (new BigDecimal(price4)).compareTo(BigDecimal.ZERO)==0) { row.put("price4", item.getDouble("price4")); } if (StringUtils.isEmpty(price5) || (new BigDecimal(price5)).compareTo(BigDecimal.ZERO)==0) { row.put("price5", item.getDouble("price5")); } row.put("oldprice1", item.getDouble("price1")); row.put("oldprice2", item.getDouble("price2")); row.put("oldprice3", item.getDouble("price3")); row.put("oldprice4", item.getDouble("price4")); row.put("oldprice5", item.getDouble("price5")); rowssuc.add(row); } if (iserr) { ExcelFactory excelFactory = new ExcelFactory("促销活动导入错误信息"); HashMap map = new HashMap(); map.put("itemno", "产品编码"); map.put("orderminqty", "起订量"); map.put("orderaddqty", "增量"); map.put("groupqty", "限购数"); map.put("price1", "价格一活动价"); map.put("price2", "价格二活动价"); map.put("price3", "价格三活动价"); map.put("price4", "价格四活动价"); map.put("price5", "价格五活动价"); map.put("signaturecode", "特征码"); map.put("msg", "错误信息"); ArrayList colNameList = new ArrayList(); HashMap keytypemap = new HashMap(); colNameList.add("itemno"); colNameList.add("orderminqty"); colNameList.add("orderaddqty"); colNameList.add("groupqty"); colNameList.add("price1"); colNameList.add("price2"); colNameList.add("price3"); colNameList.add("price4"); colNameList.add("price5"); colNameList.add("signaturecode"); colNameList.add("msg"); keytypemap.put("itemno", String.class); keytypemap.put("orderminqty", String.class); keytypemap.put("orderaddqty", String.class); keytypemap.put("groupqty", String.class); keytypemap.put("price1", String.class); keytypemap.put("price2", String.class); keytypemap.put("price3", String.class); keytypemap.put("price4", String.class); keytypemap.put("price5", String.class); keytypemap.put("signaturecode", String.class); keytypemap.put("msg", String.class); rowserr.setFieldList(colNameList); rowserr.setFieldTypeMap(keytypemap); addSheet(excelFactory, "Sheet1", rowserr, map); Rows aa = uploadExcelToObs(excelFactory); String url = ""; if (!aa.isEmpty()) { url = aa.get(0).getString("url"); } return getSucReturnObject().setData(url).toString(); } long[] sa_promotion_itemsids = createTableID("sa_promotion_items", rowssuc.size()); long[] sa_promotion_itempriceids = createTableID("sa_promotion_itemprice", rowssuc.size() * 5); if (!rowssuc.isEmpty()) { for (Row row : rowssuc) { long sa_promotion_itemsid = sa_promotion_itemsids[i]; sqlFactory = new SQLFactory(this, "促销方案商品新增"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid); sqlFactory.addParameter("sa_promotionid", sa_promotionid); sqlFactory.addParameter("sa_promotion_itemgroupid", 0); sqlFactory.addParameter("groupqty", row.getString("groupqty")); sqlFactory.addParameter("itemid", row.getString("itemid")); sqlFactory.addParameter("itemno", row.getString("itemno")); sqlFactory.addParameter("orderaddqty", row.getString("orderaddqty")); sqlFactory.addParameter("orderminqty", row.getString("orderminqty")); sqlFactory.addParameter("islimit", row.getString("islimit")); sqlFactory.addParameter("signaturecode", row.getString("signaturecode")); sqllist.add(sqlFactory.getSQL()); for (int o = 1; o <= 5; o++) { long sa_promotion_itempriceid = sa_promotion_itempriceids[a]; sqlFactory = new SQLFactory(this, "促销方案商品价格新增"); sqlFactory.addParameter("siteid", siteid); sqlFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid); sqlFactory.addParameter("sa_promotion_itempriceid", sa_promotion_itempriceid); sqlFactory.addParameter("sa_promotionid", sa_promotionid); sqlFactory.addParameter("itemid", row.getString("itemid")); sqlFactory.addParameter("pricegrade", o); sqlFactory.addParameter("oldprice", row.getDouble("oldprice" + o)); sqlFactory.addParameter("price", row.getDouble("price" + o)); sqllist.add(sqlFactory.getSQL()); a++; } i++; } } if (!sqllist.isEmpty()) { sqllist.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "导入", "促销活动商品导入成功").getSQL()); dbConnect.runSqlUpdate(sqllist); } } catch (Exception e1) { e1.printStackTrace(); return getErrReturnObject().setErrMsg(e1.getMessage()).toString(); } return getSucReturnObject().toString(); } public XSSFSheet addSheet(ExcelFactory excelFactory, String sheetname, Rows datarows, HashMap titlemap) { ArrayList keylist = datarows.getFieldList(); XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet(sheetname); XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook(); XSSFCellStyle xssfCellStyle1 = xssfFWorkbook.createCellStyle(); XSSFFont font = xssfFWorkbook.createFont(); font.setColor((short) 0xa); font.setFontHeightInPoints((short) 12); font.setBold(true); xssfCellStyle1.setFont(font); ExportExcel.setBatchDetailSheetColumn2(sheet);// 设置工作薄列宽 XSSFCellStyle titleCellStyle1 = ExportExcel.createTitleCellStyle1(xssfFWorkbook); XSSFCellStyle titleCellStyle2 = ExportExcel.createTitleCellStyle2(xssfFWorkbook); XSSFCellStyle titleCellStyle3 = ExportExcel.createTitleCellStyle3(xssfFWorkbook); ExportExcel.batchDetailErr(sheet, titleCellStyle1, titleCellStyle2, titleCellStyle3, xssfFWorkbook);// 写入标题 for (int n = 0; n < datarows.size(); n++) { Row row = datarows.get(n); XSSFRow datarow = sheet.createRow(n + 3); for (int i1 = 0; i1 < keylist.size(); i1++) { Class fieldclazztype = datarows.getFieldMeta(keylist.get(i1)).getFieldtype(); if (fieldclazztype == Integer.class) { datarow.createCell(i1).setCellValue(row.getInteger((String) keylist.get(i1)).intValue()); } else if (fieldclazztype == Long.class) { datarow.createCell(i1).setCellValue(row.getLong((String) keylist.get(i1))); } else if (fieldclazztype == Float.class) { datarow.createCell(i1).setCellValue(row.getFloat((String) keylist.get(i1))); } else if (fieldclazztype == Double.class) { datarow.createCell(i1).setCellValue(row.getDouble((String) keylist.get(i1))); } else { datarow.createCell(i1).setCellValue(row.getString((String) keylist.get(i1))); } if (i1 == 10) { datarow.getCell(i1).setCellStyle(xssfCellStyle1); } } } return sheet; } public static boolean isNumeric(String strNum) { if (strNum == null) { return false; } return strNum.matches("-?\\d+(\\.\\d+)?"); } }