CUCU2/src/custom/restcontroller/webmanage/sale/promotion/promotionItems.java

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<Long> 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<String> 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,packageqty,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"))) {
                    if(iteminfo.getBigDecimalValue("packageqty").compareTo(BigDecimal.ZERO)<=0){
                        saleFactory.addParameter("packageqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("packageqty"));
                    }else {
                        saleFactory.addParameter("packageqty", iteminfo.getBigDecimalValue("packageqty"));
                    }
                    if(itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderaddqty").compareTo(BigDecimal.ZERO)<=0){
                        saleFactory.addParameter("orderaddqty", 1);
                    }else{
                        saleFactory.addParameter("orderaddqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderaddqty"));
                    }
                    if(itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderminqty").compareTo(BigDecimal.ZERO)<=0){
                        saleFactory.addParameter("orderminqty", 1);
                    }else{
                        saleFactory.addParameter("orderminqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderminqty"));
                    }

                } else {
                    saleFactory.addParameter("orderaddqty", 1);
                    saleFactory.addParameter("orderminqty", 1);
                    saleFactory.addParameter("packageqty", 1);
                }
                saleFactory.addParameter("islimit", iteminfo.getLong("islimit"));
                saleFactory.addParameter("signaturecode", iteminfo.getStringValue("signaturecode"));
                saleFactory.addParameter("packagetypemx", iteminfo.getStringValue("packagetypemx"));

                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"));
                saleFactory.addParameter("packagetypemx", iteminfo.getStringValue("packagetypemx"));
                saleFactory.addParameter("packageqty", iteminfo.getBigDecimalValue("packageqty"));
                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<String> sqlList = new ArrayList<>();

        JSONArray sa_promotion_itemsids = content.getJSONArray("sa_promotion_itemsids");
        boolean isonsale = content.getBooleanValue("isonsale");
        List<Long> 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,t3.issyncitemonsale from plm_item t1 inner join sa_promotion_items t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid inner join sa_promotion t3 on t2.sa_promotionid=t3.sa_promotionid and t2.siteid=t3.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{
                if(row.getBoolean("issyncitemonsale")){
                    sqlList.add("update plm_item set isonsale=0 where itemid="+row.getLong("itemid"));
                }
                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(")");
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where.append(" and (t1.isonsale ='" + whereObject.getString("isonsale") + "') ");
            }

        }
        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","packagetypemx","packageqty");
        querySQL.setTableAlias("t1");


        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid = t2.itemid and t1.siteid = t2.siteid",
                "itemno", "itemname","spec","model","standards");
        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.addQueryFields("item_isonsale", "t2.isonsale");

        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<String> 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<String> keys = new ArrayList<>();
            ArrayList<String> 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<String, CellType> 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<String> 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<String> 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(groupqty)){
                    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"));
                row.put("packageqty", item.getString("packageqty"));
                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<String, String> map = new HashMap<String, String>();
                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<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                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"));
                    sqlFactory.addParameter("packagetypemx", "");
                    sqlFactory.addParameter("packageqty",  row.getString("packageqty"));
                    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<String, String> titlemap) {
        ArrayList<String> 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)));
                } 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+)?");
    }
}