CUCU2/src/custom/restcontroller/webmanage/sale/itempriceadjust/ItempriceadjustItemprice.java

package restcontroller.webmanage.sale.itempriceadjust;

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.system.attachment.Attachment;

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

@API(title = "商品调价单商品价格明细")
public class ItempriceadjustItemprice extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public ItempriceadjustItemprice(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "商品调价单商品明细新增或更新", apiversion = R.ID20221025095104.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(apiversions = {R.ID20221025160904.v1.class})
    public String insertOrUpdate_sa_itempriceadjust_itemprice() throws YosException {
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        Rows sa_itempriceadjustrow = dbConnect.runSqlQuery("select sa_itempriceadjustid,status,billno from sa_itempriceadjust where sa_itempriceadjustid=" + sa_itempriceadjustid + " and siteid='" + siteid + "'");
        if (sa_itempriceadjustid <= 0 || sa_itempriceadjustrow.isEmpty()) {
            return getErrReturnObject().setErrMsg("未选择商品调价单").toString();
        }
        if (sa_itempriceadjustrow.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("审核中无法更改").toString();
        }
        JSONArray sa_itempriceadjust_items = content.getJSONArray("sa_itempriceadjust_items");
        Rows items = dbConnect.runSqlQuery("select sa_itempriceadjust_itemsid,itemid,rowno from sa_itempriceadjust_items where siteid = '" + siteid + "' and sa_itempriceadjustid = " + sa_itempriceadjustid);
        RowsMap itemRowsMap = items.toRowsMap("itemid");
        ArrayList<String> itempriceadjustItemsidList = items.toArrayList("sa_itempriceadjust_itemsid");

        RowsMap itempriceRowsMap = dbConnect.runSqlQuery("select itemid,pricegrade,price from sa_itemprice where siteid = '" + siteid + "'").toRowsMap("itemid");

        RowsMap item = dbConnect.runSqlQuery("select itemid,itemno,marketprice from plm_item where siteid='" + siteid + "'").toRowsMap("itemid");

        RowsMap itempriceadjustItempriceRowsMap = dbConnect.runSqlQuery("select sa_itempriceadjust_itemsid from sa_itempriceadjust_itemprice where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid).toRowsMap("sa_itempriceadjust_itemsid");
        ArrayList<String> sqlList = new ArrayList();
        Long a = 0L;
        if (!items.isEmpty()) {
            a = items.getLastRow().getLong("rowno");
        }
        for (Object o : sa_itempriceadjust_items) {
            JSONObject jsonObject = (JSONObject) o;
            if (!item.containsKey(jsonObject.getString("itemid"))) {
                continue;
            }
            SQLFactory sqlFactory = new SQLFactory(this, "商品调价单商品明细新增");
            Long sa_itempriceadjust_itemsid = jsonObject.getLong("sa_itempriceadjust_itemsid");
            if (itemRowsMap.containsKey(jsonObject.getString("itemid"))) {
                sa_itempriceadjust_itemsid = itemRowsMap.get(jsonObject.getString("itemid")).get(0).getLong("sa_itempriceadjust_itemsid");
            }
            if (sa_itempriceadjust_itemsid <= 0 || !itempriceadjustItemsidList.contains(sa_itempriceadjust_itemsid.toString())) {
                sa_itempriceadjust_itemsid = createTableID("sa_itempriceadjust_items");
                DataContrlLog.createLog(this, "sa_itempriceadjust_items", sa_itempriceadjust_itemsid, "新增", "新增:" + sa_itempriceadjust_itemsid);
                a++;
                sqlFactory.addParameter("rowno", a);
            } else {
                sqlFactory = new SQLFactory(this, "商品调价单商品明细更新");
                DataContrlLog.createLog(this, "sa_itempriceadjust_items", sa_itempriceadjust_itemsid, "更新", "更新:" + sa_itempriceadjust_itemsid);
            }
            sqlFactory.addParameter("itemid", jsonObject.getLong("itemid"));
            sqlFactory.addParameter("billno", sa_itempriceadjustrow.get(0).getString("billno"));
            sqlFactory.addParameter("itemno", item.get(jsonObject.getString("itemid")).get(0).getString("itemno"));
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
            sqlFactory.addParameter("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsid);
            sqlFactory.addParameter("remarks", jsonObject.getString("remarks"));
            sqlFactory.addParameter("oldmarketprice", item.get(jsonObject.getString("itemid")).get(0).getBigDecimal("marketprice"));
            sqlFactory.addParameter("marketprice", item.get(jsonObject.getString("itemid")).get(0).getBigDecimal("marketprice"));
            sqlList.add(sqlFactory.getSQL());
            if (!itempriceadjustItempriceRowsMap.containsKey(sa_itempriceadjust_itemsid.toString())) {
                if (itempriceRowsMap.containsKey(jsonObject.getString("itemid"))) {
                    Rows itemprice = itempriceRowsMap.get(jsonObject.getString("itemid"));
                    for (Row row : itemprice) {
                        sqlFactory = new SQLFactory(this, "商品调价单商品价格明细新增");
                        long sa_itempriceadjust_itempriceid = createTableID("sa_itempriceadjust_itemprice");

                        sqlFactory.addParameter("sa_itempriceadjust_itempriceid", sa_itempriceadjust_itempriceid);
                        sqlFactory.addParameter("siteid", siteid);
                        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
                        sqlFactory.addParameter("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsid);
                        sqlFactory.addParameter("pricegrade", row.getLong("pricegrade"));
                        sqlFactory.addParameter("oldprice", row.getBigDecimal("price"));
                        sqlFactory.addParameter("price", row.getBigDecimal("price"));
                        sqlList.add(sqlFactory.getSQL());
                    }
                }
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "商品调价单商品明细列表查询", apiversion = R.ID20221025160904.v1.class)
    @CACHEING
    public String query() throws YosException {
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        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 (").append("t1.remarks like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or ").append("t7.itemno like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or ").append("t7.itemname like '%").append(whereObject.getString("condition")).append("%')");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "商品调价单商品明细列表情查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        for (Row row : rows) {
            row.put("oldprice1", row.getBigDecimal("oldprice1"));
            row.put("price1", row.getBigDecimal("price1"));
            row.put("oldprice2", row.getBigDecimal("oldprice2"));
            row.put("price2", row.getBigDecimal("price2"));
            row.put("oldprice3", row.getBigDecimal("oldprice3"));
            row.put("price3", row.getBigDecimal("price3"));
            row.put("oldprice4", row.getBigDecimal("oldprice4"));
            row.put("price4", row.getBigDecimal("price4"));
            row.put("oldprice5", row.getBigDecimal("oldprice5"));
            row.put("price5", row.getBigDecimal("price5"));
        }
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "商品调价单商品价格明细新增或更新", apiversion = R.ID20221025165904.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221025160904.v1.class})
    public String update() throws YosException {
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        ArrayList<String> sqlList = new ArrayList<>();
        JSONArray sa_itempriceadjust_itemprice = content.getJSONArray("sa_itempriceadjust_itemprice");
        if (dbConnect.runSqlQuery("select status from sa_itempriceadjust where sa_itempriceadjustid="
                + sa_itempriceadjustid
                + " and siteid='"
                + siteid
                + "'").get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("审核中无法更改").toString();
        }

        RowsMap itempriceRowsMap = dbConnect.runSqlQuery("select sa_itempriceadjust_itemsid,pricegrade from sa_itempriceadjust_itemprice where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid).toRowsMap("sa_itempriceadjust_itemsid");
        String sa_itempriceadjust_itemsid = content.getString("sa_itempriceadjust_itemsid");
        BigDecimal marketprice = content.getBigDecimal("marketprice");
        sqlList.add("update sa_itempriceadjust_items set marketprice=" + marketprice + " where siteid='" + siteid + "' and sa_itempriceadjust_itemsid=" + sa_itempriceadjust_itemsid);
        for (Object object : sa_itempriceadjust_itemprice) {
            JSONObject jsonObject = (JSONObject) object;
            BigDecimal price;
            try {
                price = jsonObject.getBigDecimal("price");
            } catch (NumberFormatException e) {
                return getErrReturnObject().setErrMsg("价格填写格式异常").toString();
            }
            if (price.compareTo(new BigDecimal("0")) == -1) {
                price = new BigDecimal("0");
            }
            Long pricegrade = jsonObject.getLong("pricegrade");
            SQLFactory sqlFactory = new SQLFactory(this, "商品调价单商品价格明细新增");
            if (!itempriceRowsMap.containsKey(sa_itempriceadjust_itemsid) ||
                    !itempriceRowsMap.get(sa_itempriceadjust_itemsid).toArrayList("pricegrade").contains(pricegrade.toString())) {
                Long sa_itempriceadjust_itempriceid = createTableID("sa_itempriceadjust_itemprice");
                sqlFactory.addParameter("sa_itempriceadjust_itempriceid", sa_itempriceadjust_itempriceid);
                sqlFactory.addParameter("oldprice", 0);
            } else {
                sqlFactory = new SQLFactory(this, "商品调价单商品价格明细更新");
            }
            sqlFactory.addParameter("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsid);
            sqlFactory.addParameter("price", price);
            sqlFactory.addParameter("pricegrade", pricegrade);
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
            sqlList.add(sqlFactory.getSQL());
            String sql = DataContrlLog.createLog(this, "sa_itempriceadjust_itemprice", Long.parseLong(sa_itempriceadjust_itemsid), "更新", "商品调价单商品价格更新:价格等级" + pricegrade).getSQL();
            sqlList.add(sql);
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "删除商品价格调整单商品明细", apiversion = R.ID20221025095404.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221025160904.v1.class})
    public String delete() throws YosException {
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        JSONArray sa_itempriceadjust_itemsids = content.getJSONArray("sa_itempriceadjust_itemsids");
        Rows rows = dbConnect.runSqlQuery("select status from sa_itempriceadjust where sa_itempriceadjustid=" + sa_itempriceadjustid + " and siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("无效调价单").toString();
        }
        if (rows.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("审核中无法删除").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactory = new SQLFactory(this, "商品价格调整单商品明细删除");
        sqlFactory.addParameter_in("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsids.toArray());
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        sqlFactory.addParameter("siteid", siteid);
        sqlList.add(sqlFactory.getSQL());
        sqlFactory = new SQLFactory(this, "商品价格调整单商品价格明细删除");
        sqlFactory.addParameter_in("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsids.toArray());
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        sqlFactory.addParameter("siteid", siteid);
        sqlList.add(sqlFactory.getSQL());
        for (Object o : sa_itempriceadjust_itemsids) {
            String sql = "update sa_itempriceadjust_items set rowno=rowno-1 where siteid = '" + siteid + "' and sa_itempriceadjust_itemsid > " + o;
            sqlList.add(sql);
            sql = DataContrlLog.createLog(this, "sa_itempriceadjust_itemprice", Long.parseLong(o.toString()), "更新", "商品调价单商品删除:" + o).getSQL();
            sqlList.add(sql);
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().setData("删除成功").toString();
    }

    @API(title = "导入商品调价单", apiversion = R.ID20221026142804.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221024163504.v1.class, R.ID20221025160904.v1.class})
    public String importItempriceadjust() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_itempriceadjustid = createTableID("sa_itempriceadjust");
        String billno = createBillCode("itempriceadjust");
        SQLFactory sqlFactory = new SQLFactory(this, "商品价格调整单新增");
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("billno", billno);
        sqlFactory.addParameter("remarks", "");
        dbConnect.runSqlUpdate(sqlFactory.getSQL());

        JSONArray jsonArray = new JSONArray();
        jsonArray.add(content.getLong("attachmentid"));

        content.put("ownertable", "sa_itempriceadjust");
        content.put("ownerid", sa_itempriceadjustid);
        content.put("usetype", "default");
        content.put("attachmentids", jsonArray);
        Attachment attachment = new Attachment(content);
        attachment.createFileLink();

        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("remarks");
            keys.add("price1");
            keys.add("price2");
            keys.add("price3");
            keys.add("price4");
            keys.add("price5");

            HashMap<String, CellType> cellMap = new HashMap<>();
            cellMap.put("itemno", CellType.STRING);
            cellMap.put("remarks", 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);
            Rows rows = e.getSheetRows(0, keys, cellMap, 3);

            ArrayList<String> itemnoList = rows.toArrayList("itemno", false);
            itemnoList.add("-1");

            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemno", "marketprice");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("itemno", itemnoList);
            RowsMap itemnoRowsMap = querySQL.query().toRowsMap("itemno");
            for (Row row : rows) {
                if (StringUtils.isEmpty(row.getString("itemno"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号不能为空");
                    rowserr.add(row);
                    continue;
                } else {
                    String itemno = row.getString("itemno");
                    if (itemnoList.indexOf(itemno) != itemnoList.lastIndexOf(itemno)) {
                        iserr = true;
                        row.put("msg", "错误信息:导入文件存在相同编码的商品");
                        rowserr.add(row);
                        continue;
                    }
                    if (itemnoRowsMap.containsKey(itemno)) {
                        row.put("itemid", itemnoRowsMap.get(itemno).get(0).getLong("itemid"));
                        row.put("oldmarketprice", itemnoRowsMap.get(itemno).get(0).getBigDecimal("marketprice"));
                    } else {
                        iserr = true;
                        row.put("msg", "错误信息:不存在商品编号为" + itemno + "的商品");
                        rowserr.add(row);
                        continue;
                    }
                }
                if (StringUtils.isEmpty(row.getString("price1")) ||
                        StringUtils.isEmpty(row.getString("price2")) ||
                        StringUtils.isEmpty(row.getString("price3")) ||
                        StringUtils.isEmpty(row.getString("price4")) ||
                        StringUtils.isEmpty(row.getString("price5"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品调整价不能为空");
                    rowserr.add(row);
                    continue;
                }
                rowssuc.add(row);

            }

            if (!rowssuc.isEmpty()) {
                sqlFactory = new SQLFactory(this, "查询指定商品原价");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter_in("itemids", rowssuc.toArray("itemid"));
                RowsMap itemPriceRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap("itemid");
                Long rowno = 0L;
                for (Row row : rowssuc) {
                    Long sa_itempriceadjust_itemsid = createTableID("sa_itempriceadjust_items");
                    SQLFactory sqlFactoryupload = new SQLFactory(this, "商品调价单商品明细新增");
                    sqlFactoryupload.addParameter("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsid);
                    sqlFactoryupload.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
                    sqlFactoryupload.addParameter("remarks", row.getString("remarks"));
                    rowno++;
                    sqlFactoryupload.addParameter("rowno", rowno);
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("itemid", row.getString("itemid"));
                    sqlFactoryupload.addParameter("billno", billno);
                    sqlFactoryupload.addParameter("itemno", row.getString("itemno"));
                    sqlFactoryupload.addParameter("oldmarketprice", row.getBigDecimal("oldmarketprice"));
                    sqlFactoryupload.addParameter("marketprice", row.getBigDecimal("oldmarketprice"));
                    sqllist.add(sqlFactoryupload.getSQL());

                    RowsMap pricegrade = new RowsMap();
                    if (itemPriceRowsMap.containsKey(row.getString("itemid"))) {
                        Rows itemPriceRows = itemPriceRowsMap.get(row.getString("itemid"));
                        pricegrade = itemPriceRows.toRowsMap("pricegrade");
                    }
                    for (int i = 1; i <= 5; i++) {
//                        if (pricegrade.containsKey(String.valueOf(i)) || !row.getString("price" + i).equals("0")) {
                        sqlFactoryupload = new SQLFactory(this, "商品调价单商品价格明细新增");
                        long sa_itempriceadjust_itempriceid = createTableID("sa_itempriceadjust_itemprice");
                        sqlFactoryupload.addParameter("sa_itempriceadjust_itempriceid", sa_itempriceadjust_itempriceid);
                        sqlFactoryupload.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
                        sqlFactoryupload.addParameter("sa_itempriceadjust_itemsid", sa_itempriceadjust_itemsid);
                        sqlFactoryupload.addParameter("siteid", siteid);
                        sqlFactoryupload.addParameter("pricegrade", i);
                        BigDecimal oldprice = new BigDecimal("0");

                        if (pricegrade.containsKey(String.valueOf(i))) {
                            oldprice = pricegrade.get(String.valueOf(i)).get(0).getBigDecimal("price");
                        }
                        sqlFactoryupload.addParameter("oldprice", oldprice);

                        BigDecimal price = new BigDecimal(row.getString("price" + i));
                        sqlFactoryupload.addParameter("price", price);
                        sqllist.add(sqlFactoryupload.getSQL());
//                        }

                    }
                }
            }
            if (!sqllist.isEmpty()) {
                String sql = DataContrlLog.createLog(this, "sa_itempriceadjust", sa_itempriceadjustid, "新增", "商品调价单导入:" + sa_itempriceadjustid).getSQL();
                sqlList.add(sql);
                dbConnect.runSqlUpdate(sqllist);
            } else {
                dbConnect.runSqlUpdate("delete from sa_itempriceadjust where sa_itempriceadjustid=" + sa_itempriceadjustid);
            }

            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("调价单导入错误信息");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "商品编号");
                map.put("remarks", "备注");
                map.put("price1", "调整价1");
                map.put("price2", "调整价2");
                map.put("price3", "调整价3");
                map.put("price4", "调整价4");
                map.put("price5", "调整价5");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("remarks");
                colNameList.add("price1");
                colNameList.add("price2");
                colNameList.add("price3");
                colNameList.add("price4");
                colNameList.add("price5");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("remarks", String.class);
                keytypemap.put("price1", BigDecimal.class);
                keytypemap.put("price2", BigDecimal.class);
                keytypemap.put("price3", BigDecimal.class);
                keytypemap.put("price4", BigDecimal.class);
                keytypemap.put("price5", BigDecimal.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();
            }
        } catch (Exception e1) {
            dbConnect.runSqlUpdate("delete from sa_itempriceadjust where sa_itempriceadjustid=" + sa_itempriceadjustid);
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    /**
     * 下载商品调价单导入模板
     *
     * @return
     */
    @API(title = "下载商品调价单导入模板", apiversion = R.ID20221026152404.v1.class)
    public String downloadItempriceadjustExcel() 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);
        sheet.setDefaultColumnStyle(2, style);
        sheet.setDefaultColumnStyle(3, style);
        sheet.setDefaultColumnStyle(4, style);
        sheet.setDefaultColumnStyle(5, style);
        sheet.setDefaultColumnStyle(6, 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.ID20221027095504.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221025160904.v1.class})
    public String itempriceadjustAides() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        Long manner = content.getLong("manner"); // 调价方式 0 按百分比 1 定额
        Long cardinality = content.getLong("cardinality"); //调价规则 0 下调 1上浮
        BigDecimal coefficient = content.getBigDecimal("coefficient"); //系数 下调的金额
        Rows rows = dbConnect.runSqlQuery("select status from sa_itempriceadjust where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("无效调价单").toString();
        }
        if (rows.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("审核状态无法快速调价").toString();
        }
        if (manner == 0 && cardinality == 0) {
            sqlList.add("update sa_itempriceadjust_itemprice set price=oldprice*(1-" + coefficient + ") where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid + " and oldprice>0");
            sqlList.add("update sa_itempriceadjust_items set marketprice=oldmarketprice*(1-" + coefficient + ") where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid + " and oldmarketprice>0");
        } else if (manner == 1 && cardinality == 0) {
            sqlList.add("update sa_itempriceadjust_itemprice set price=if(oldprice-" + coefficient + ">0,oldprice-" + coefficient + ",0) where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
            sqlList.add("update sa_itempriceadjust_items set marketprice=if(oldmarketprice-" + coefficient + ">0,oldmarketprice-" + coefficient + ",0) where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
        } else if (manner == 0 && cardinality == 1) {
            sqlList.add("update sa_itempriceadjust_itemprice set price=oldprice*(1+" + coefficient + ") where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid + " and oldprice>0");
            sqlList.add("update sa_itempriceadjust_items set marketprice=oldmarketprice*(1+" + coefficient + ") where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid + " and oldmarketprice>0");
        } else if (manner == 1 && cardinality == 1) {
            sqlList.add("update sa_itempriceadjust_itemprice set price=oldprice+" + coefficient + " where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
            sqlList.add("update sa_itempriceadjust_items set marketprice=oldmarketprice+" + coefficient + " where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
        }
        String sql = DataContrlLog.createLog(this, "sa_itempriceadjust", sa_itempriceadjustid, "修改", "商品调价:" + sa_itempriceadjustid).getSQL();
        sqlList.add(sql);
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "查询可添加的商品", apiversion = R.ID20221028154004.v1.class)
    public String queryItem() throws YosException {
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        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 (").append("t1.itemno like '%").append(whereObject.getString("condition")).append("%'");
                where.append(" or ").append("t1.itemname like '%").append(whereObject.getString("condition")).append("%')");
            }
            if (whereObject.containsKey("itemclassid") && 0 != whereObject.getLong("itemclassid")) {
                where.append("and t3.itemclassid in((with recursive itemclass as (select itemclassid from plm_itemclass where siteid =  'YOSTEST2' and itemclassid =").append(whereObject.getLong("itemclassid"))
                        .append(" union all select t2.itemclassid from itemclass t1 inner join plm_itemclass t2 on t1.itemclassid = t2.parentid and t2.siteid =  'YOSTEST2') select itemclassid from itemclass))");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "查询可添加商品", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL());
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "刷新原价", apiversion = R.ID202212003011904.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221025160904.v1.class})
    public String refreshPrice() throws YosException {
        ArrayList<String> sqlList = new ArrayList();
        Long sa_itempriceadjustid = content.getLong("sa_itempriceadjustid");
        Rows rows = dbConnect.runSqlQuery("select status from sa_itempriceadjust where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("无效调价单").toString();
        }
        if (rows.get(0).getString("status").equals("审核")) {
            return getErrReturnObject().setErrMsg("审核状态不能刷新原价").toString();
        }

        RowsMap itemRowsMap = dbConnect.runSqlQuery("select sa_itempriceadjust_itemsid,itemid,oldmarketprice from sa_itempriceadjust_items where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid).toRowsMap("itemid");
        String replace = itemRowsMap.keySet().toString().replace("[", "(").replace("]", ")");
        RowsMap itemidRowsMap = dbConnect.runSqlQuery("select itemid,itemno,marketprice from plm_item where siteid='" + siteid + "' and itemid in " + replace).toRowsMap("itemid");
        for (String itemid : itemidRowsMap.keySet()) {
            if (itemidRowsMap.get(itemid).get(0).getBigDecimal("marketprice").compareTo(itemRowsMap.get(itemid).get(0).getBigDecimal("oldmarketprice")) != 0) {
                sqlList.add("update sa_itempriceadjust_items set oldmarketprice=" + itemidRowsMap.get(itemid).get(0).getBigDecimal("marketprice") + " where siteid='" + siteid + "' and sa_itempriceadjustid=" + sa_itempriceadjustid + " and itemid=" + itemid);
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "查询指定商品原价");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_in("itemids", itemRowsMap.keySet().toArray());
        RowsMap itemOldpriceRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap(new String[]{"itemid", "pricegrade"});

        sqlFactory = new SQLFactory(this, "商品调价单商品价格列表查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
        RowsMap itempriceadjustOldPriceRowsMap = dbConnect.runSqlQuery(sqlFactory.getSQL()).toRowsMap(new String[]{"itemid", "pricegrade"});
        Set<String> itemPricegradeSet = itemOldpriceRowsMap.keySet();
        for (String itemPricegrade : itemPricegradeSet) {
            Row row1 = itemOldpriceRowsMap.get(itemPricegrade).get(0);
            BigDecimal price = row1.getBigDecimal("price");
            if (itempriceadjustOldPriceRowsMap.containsKey(itemPricegrade)) {
                BigDecimal oldprice = itempriceadjustOldPriceRowsMap.get(itemPricegrade).get(0).getBigDecimal("oldprice");
                if (oldprice.compareTo(price) != 0) {
                    sqlList.add("update sa_itempriceadjust_itemprice set oldprice=" + price + " where siteid='" + siteid + "' and sa_itempriceadjust_itempriceid=" + itempriceadjustOldPriceRowsMap.get(itemPricegrade).get(0).getLong("sa_itempriceadjust_itempriceid"));
                }
            } else {
                sqlFactory = new SQLFactory(this, "商品调价单商品价格明细新增");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_itempriceadjust_itempriceid", createTableID("sa_itempriceadjust_itemprice"));
                sqlFactory.addParameter("sa_itempriceadjustid", sa_itempriceadjustid);
                sqlFactory.addParameter("sa_itempriceadjust_itemsid", itemRowsMap.get(row1.getString("itemid")).get(0).getLong("sa_itempriceadjust_itemsid"));
                sqlFactory.addParameter("pricegrade", row1.getLong("pricegrade"));
                sqlFactory.addParameter("price", price);
                sqlFactory.addParameter("oldprice", price);
                sqlList.add(sqlFactory.getSQL());
            }
        }
        String sql = DataContrlLog.createLog(this, "sa_itempriceadjust", sa_itempriceadjustid, "更新", "刷新原价:" + sa_itempriceadjustid).getSQL();
        sqlList.add(sql);
        dbConnect.runSqlUpdate(sqlList);
        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)).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 == 7) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }
}