md_YOS/src/custom/restcontroller/webmanage/sale/toolscode/toolscode.java

package restcontroller.webmanage.sale.toolscode;

import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING_CLEAN;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import restcontroller.R;

import java.util.ArrayList;
import java.util.HashMap;

public class toolscode extends Controller {

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

    /**
     * 下载工具序列号模板
     *
     * @return
     */
    @API(title = "下载工具序列号模板", apiversion = R.ID20230114142403.v1.class)
    public String downloadToolscodeExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForToolscode");

        XSSFSheet sheet = excelFactory.getXssfWorkbook().createSheet("Sheet1");
        XSSFWorkbook xssfFWorkbook = excelFactory.getXssfWorkbook();
        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 = saveToExcelAttachment(excelFactory);
        String url = "";
        if (!aa.isEmpty()) {
            url = aa.get(0).getString("url");
        }
        return getSucReturnObject().setData(url).toString();

    }

    /**
     * 导入工具序列号
     *
     * @return
     */
    @API(title = "导入工具序列号", apiversion = R.ID20230114142503.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221215100903.v1.class, R.ID20221215101003.v1.class})
    public String uploadToolscodeByExcel() throws YosException {

        long itemid = 0;


//		JSONArray jsonArray = new JSONArray();
//		jsonArray.add(content.getLong("attachmentid"));
//
//		content.put("ownertable", "sa_itemsku");
//		content.put("ownerid", sa_itemskuid);
//		content.put("usetype", "default");
//		content.put("attachmentids", jsonArray);
//		Attachment attachment = new Attachment(content);
//		attachment.createFileLink();

        ExcelFactory e;
        try {

            // 华为云
            e = getExcelAttachment(content.getLong("attachmentid"));
            // 本地
            //e = getPostExcelFactory();

            // Rows keyRow = dbConnect.runSqlQuery("select fagentnum,faddress from tagents
            // where 1=2");
            // Rows row1 = e.getSheetRows(1, keyRow, 1);
            ArrayList<String> keys = new ArrayList<>();
            ArrayList<String> sqllist = new ArrayList<>();
            keys.add("itemno");
            keys.add("sku");

            Rows rows = e.getSheetRows(0, keys, 3);
            int a = 1;
            int i = 0;
            // String msg = "";
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item", "itemid", "itemno");
            querySQL.setTableAlias("t1");
            querySQL.setSiteid(siteid);
            querySQL.setWhere("t1.status='审核'");
            querySQL.setWhere("t1.itemno", rows.toArrayList("itemno"));
            Rows itemRows = querySQL.query();
            RowsMap itemRowsMap = itemRows.toRowsMap("itemno");

            QuerySQL skuquerySQL = SQLFactory.createQuerySQL(this, "sa_itemsku", "sku");
            skuquerySQL.setTableAlias("t1");
            skuquerySQL.setSiteid(siteid);
            skuquerySQL.setWhere("t1.sku", rows.toArrayList("sku"));
            Rows skuRows = skuquerySQL.query();
            RowsMap skuRowsMap = skuRows.toRowsMap("sku");
            for (Row row : rows) {

                if (StringUtils.isEmpty(row.getString("itemno")) || StringUtils.isEmpty(row.getString("sku"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号，序列号不能为空");
                    rowserr.add(row);
                    // rows.remove(row);
                    // msg = "手机号，省市县及来源不能为空";
                } else {
                    if (itemRowsMap.containsKey(row.getString("itemno"))) {
                        if (skuRowsMap.containsKey(row.getString("sku"))) {
                            iserr = true;
                            row.put("msg", "错误信息:序列号" + row.getString("sku") + "已存在");
                            rowserr.add(row);
                        } else {
                            row.put("itemid", itemRowsMap.get(row.getString("itemno")).get(0).getString("itemid"));
                            rowssuc.add(row);
                        }

                    } else {
                        iserr = true;
                        row.put("msg", "错误信息:不存在商品编号为" + row.getString("itemno") + "的商品");
                        rowserr.add(row);
                    }

                }
            }
            if (!rowssuc.isEmpty()) {
                SQLFactory sqlFactoryupload = null;
                for (Row row : rowssuc) {
                    Long sa_itemskuid = createTableID("sa_itemsku");
                    sqlFactoryupload = new SQLFactory(this, "商品序列号档案新增");
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("itemid", row.getString("itemid"));
                    sqlFactoryupload.addParameter("sku", row.getString("sku"));
                    sqlFactoryupload.addParameter("sa_itemskuid", sa_itemskuid);
                    sqlFactoryupload.addParameter("islimit", 0);
                    sqlFactoryupload.addParameter("sys_enterpriseid", 0);
                    sqlFactoryupload.addParameter("stockid", 0);
                    sqlFactoryupload.addParameter("isyj", 0);

                    sqllist.add(sqlFactoryupload.getSQL());
                    sqllist.add(DataContrlLog.createLog(this, "sa_itemsku", sa_itemskuid, "导入", "商品序列号导入成功").getSQL());
                    i++;
                }
            }
            if (sqllist != null && !sqllist.isEmpty()) {
                dbConnect.runSqlUpdate(sqllist);
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sa_quotedpriceerr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "商品编号");
                map.put("sku", "序列号");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("sku");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("sku", String.class);
                keytypemap.put("msg", String.class);
                rowserr.setFieldList(colNameList);
                rowserr.setFieldTypeMap(keytypemap);
                addSheet(excelFactory, "Sheet1", rowserr, map);

                Rows aa = saveToExcelAttachment(excelFactory);
                String url = "";
                if (!aa.isEmpty()) {
                    url = aa.get(0).getString("url");
                }
                return getSucReturnObject().setData(url).toString();
            }

        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();

    }

    @API(title = "商品序列号档案新增更新", apiversion = R.ID20221215100803.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221215100903.v1.class, R.ID20221215101003.v1.class})
    public String insertormodify_toolscode() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名
        String tableName = "sa_itemsku";
        Long sa_itemskuid = content.getLong("sa_itemskuid");
        Long sa_agentsid = content.getLong("sa_agentsid");
        Long stockid = content.getLong("stockid");
        // Long sys_enterpriseid = content.getLong("sys_enterpriseid");
        // Long sa_logiscompid = content.getLong("sa_logiscompid");
        // Long rec_contactsid = content.getLongValue("rec_contactsid");
        long itemid = content.getLong("itemid");
        String sku = content.getString("sku");
        boolean islimit = content.getBooleanValue("islimit");
        boolean isyj = content.getBooleanValue("isyj");
        SQLFactory sqlFactory = new SQLFactory(this, "商品序列号档案新增");

        if (sa_itemskuid <= 0 || dbConnect.runSqlQuery("select sa_itemskuid from sa_itemsku where sa_itemskuid=" + sa_itemskuid).isEmpty()) {
            Rows rowscode = dbConnect.runSqlQuery("select sa_itemskuid from sa_itemsku where siteid='" + siteid + "' and sku='" + sku + "'");
            if (!rowscode.isEmpty()) {
                return getErrReturnObject().setErrMsg("该序列号已被使用").toString();
            }
            sa_itemskuid = createTableID(tableName);
            sqlFactory.addParameter("sa_agentsid", sa_agentsid);
            sqlFactory.addParameter("itemid", itemid);
            sqlFactory.addParameter("stockid", stockid);
            sqlFactory.addParameter("islimit", islimit);
            sqlFactory.addParameter("isyj", isyj);
            sqlFactory.addParameter("sku", sku);
            sqlList.add(DataContrlLog.createLog(this, "sa_itemsku", sa_itemskuid, "新增", "商品序列号档案新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery("SELECT status,sa_itemskuid,sa_agentsid,stockid,itemid from sa_itemsku WHERE sa_itemskuid = " + sa_itemskuid);
            Rows rowscode = dbConnect.runSqlQuery("select sa_itemskuid from sa_itemsku where siteid='" + siteid + "' and sku='" + sku + "' and sa_itemskuid !=" + sa_itemskuid);
            if (!rowscode.isEmpty()) {
                return getErrReturnObject().setErrMsg("该序列号已被使用").toString();
            }
            if (rows.isNotEmpty()) {
                if (!rows.get(0).getBoolean("isused")) {
                    long oldsa_agentsid = 0;
                    long newsa_agentsid = 0;
                    long olditemid = 0;
                    long newitemid = 0;
                    long oldstockid = 0;
                    long newstockid = 0;

                    sqlFactory = new SQLFactory(this, "商品序列号档案更新");
                    sqlFactory.addParameter("sa_agentsid", sa_agentsid);
                    sqlFactory.addParameter("islimit", islimit);
                    sqlFactory.addParameter("itemid", itemid);
                    sqlFactory.addParameter("isyj", isyj);
                    sqlFactory.addParameter("stockid", stockid);
                    sqlFactory.addParameter("sku", sku);
                    sqlList.add(DataContrlLog.createLog(this, "sa_itemsku", sa_itemskuid, "更新", "商品序列号档案更新成功").getSQL());
                    if (sa_agentsid != rows.get(0).getLong("sa_agentsid")) {
                        newsa_agentsid = sa_agentsid;
                        oldsa_agentsid = rows.get(0).getLong("sa_agentsid");
                    }
                    if (itemid != rows.get(0).getLong("itemid")) {
                        newitemid = itemid;
                        olditemid = rows.get(0).getLong("itemid");
                        if (rows.get(0).getLong("wmsuploadflag") == 2) {
                            sqlList.add("update sa_itemsku set wmsuploadflag=1 where sa_itemskuid=" + sa_itemskuid + " and siteid='" + siteid + "'");
                        }
                        if (StringUtils.isBlank(content.getStringValue("changenotes"))) {
                            return getErrReturnObject().setErrMsg("变更商品信息时，需填写变更原因").toString();
                        }
                    }
                    if (stockid != rows.get(0).getLong("stockid")) {
                        newstockid = stockid;
                        oldstockid = rows.get(0).getLong("stockid");
                    }
                    if (newstockid != 0 || newitemid != 0 || newsa_agentsid != 0) {
                        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_itemsku_change");
                        insertSQL.setValue("siteid", siteid);
                        insertSQL.setValue("sa_itemsku_changeid", createTableID("sa_itemsku_change"));
                        insertSQL.setValue("sa_itemskuid", sa_itemskuid);
                        insertSQL.setValue("sa_agentsid", oldsa_agentsid);
                        insertSQL.setValue("sa_agentsidnew", newsa_agentsid);

                        insertSQL.setValue("itemid", olditemid);
                        insertSQL.setValue("itemidnew", newitemid);

                        insertSQL.setValue("stockid", oldstockid);
                        insertSQL.setValue("stockidnew", newstockid);
                        insertSQL.setValue("changenotes", "");
                        if (newitemid != 0) {
                            insertSQL.setValue("changenotes", content.getStringValue("changenotes"));
                        }

                        insertSQL.setDateValue("changedate");
                        insertSQL.setValue("changeby", username);
                        sqlList.add(insertSQL.getSQL());
                    }

                } else {
                    return getErrReturnObject().setErrMsg("非在库状态下无法编辑").toString();
                }

            } else {
                return getErrReturnObject().setErrMsg("该商品序列号档案不存在").toString();
            }

        }

        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_itemskuid", sa_itemskuid);
        sqlList.add(sqlFactory.getSQL());
        dbConnect.runSqlUpdate(sqlList);

        content.put("sa_itemskuid", sa_itemskuid);

        return queryToolscodeMain();
    }

    @API(title = "商品序列号档案详情", apiversion = R.ID20221215100903.v1.class)
    public String queryToolscodeMain() throws YosException {
        Long sa_itemskuid = content.getLong("sa_itemskuid");
        SQLFactory sqlFactory = new SQLFactory(this, "商品序列号档案详情查询");
        sqlFactory.addParameter("sa_itemskuid", sa_itemskuid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();

        return getSucReturnObject().setData(row).toString();
    }

    @API(title = "查询商品序列号档案列表", apiversion = R.ID20221215101003.v1.class)
    public String queryToolscodeList() throws YosException {
        QuerySQL skuQuery = SQLFactory.createQuerySQL(this, "sa_itemsku").setTableAlias("t1");
        skuQuery.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid=t2.itemid", "itemid", "itemno", "itemname", "spec", "model", "topclassnum");
        skuQuery.addJoinTable(JOINTYPE.left, "plm_unit", "t3", "t2.unitid=t3.unitid");
        skuQuery.addJoinTable(JOINTYPE.left, "st_stock", "t4", "t4.stockno=t2.stockno");
        skuQuery.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t1.sys_enterpriseid=t5.sys_enterpriseid", "t5.enterprisename");
        skuQuery.addJoinTable(JOINTYPE.left, "sa_agents", "t6", "t1.sys_enterpriseid=t6.sys_enterpriseid", "t6.agentnum");
        skuQuery.addJoinTable(JOINTYPE.left, "st_stock", "t7", "t1.stockid=t7.stockid", "stockno", "stockname");

        skuQuery.addQueryFields("itemstockno", "t2.stockno");
        skuQuery.addQueryFields("itemstockname", "t4.stockname");
        skuQuery.addQueryFields("unit", "t2.unitname");

        skuQuery.setWhere("siteid", siteid);
        skuQuery.setPage(pageSize, pageNumber);
        skuQuery.setOrderBy("sa_itemskuid desc");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (!"".equals(whereObject.getStringValue("condition"))) {
                String condition = whereObject.getString("condition");
                skuQuery.setWhere("( t1.sku like'%" + condition + "%'" + "or exists(select * from plm_item where plm_item.itemid=t1.itemid and (plm_item.itemno like'%" + condition + "%'or plm_item.itemname like'%" + condition + "%'))" + "or exists(select * from sa_agents where sa_agents.sa_agentsid=t1.sa_agentsid and sa_agents.agentnum like'%" + condition + "%')" + ")");
            }
            if (!"".equals(whereObject.getStringValue("status"))) {
                skuQuery.setWhere("t1.status", whereObject.getString("status"));
            }
            if (!"".equals(whereObject.getStringValue("isused"))) {
                skuQuery.setWhere("t1.isused", whereObject.getBooleanValue("isused"));
            }
        }
        Rows skuRows = skuQuery.query();
        if (skuRows.isNotEmpty()) {
            RowsMap skuChangeRowsMap = SQLFactory.createQuerySQL(this, "sa_itemsku_change", "sa_itemskuid", "changenotes").setWhere("sa_itemskuid", skuRows.toArrayList("sa_itemskuid")).setOrderBy("sa_itemsku_changeid desc").query().toRowsMap("sa_itemskuid");
            for (Row skuRow : skuRows) {
                String sa_itemskuid = skuRow.getString("sa_itemskuid");
                if (skuChangeRowsMap.containsKey(sa_itemskuid) && skuChangeRowsMap.get(sa_itemskuid).isNotEmpty()) {
                    Row changeNoteRow = skuChangeRowsMap.get(sa_itemskuid).get(0);
                    skuRow.put("changenotes", changeNoteRow.getString("changenotes"));
                }
            }
        }
        return getSucReturnObject().setData(skuRows).toString();
    }

    @API(title = "查询序列号变更记录", apiversion = R.ID2025021910425003.v1.class)
    public String queryToolscodeChangeNotesList() throws YosException {
        Long sa_itemskuid = content.getLong("sa_itemskuid");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_itemsku_change", "*");
        querySQL.setTableAlias("t1");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setSiteid(siteid);
        querySQL.setWhere("sa_itemskuid", sa_itemskuid);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询序列号使用记录", apiversion = R.ID2025101809551503.v1.class)
    public String querySkuUsedList() throws YosException {
        String sku = content.getString("sku");
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sku_use_view", "*");
        querySQL.setTableAlias("t1");
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setWhere("t1.sku", sku);
        querySQL.setOrderBy("t1.billdate asc");
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "删除", apiversion = R.ID20221215101103.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221215101003.v1.class, R.ID20221215100903.v1.class})
    public String delete() throws YosException {

        JSONArray sa_itemskuids = content.getJSONArray("sa_itemskuids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_itemskuids.size());
        for (Object o : sa_itemskuids) {
            long sa_itemskuid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select sa_itemskuid,status from sa_itemsku where siteid='" + siteid + "' and sa_itemskuid='" + sa_itemskuid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (RowsStatus.get(0).getBoolean("isused")) {
                    batchDeleteErr.addErr(sa_itemskuid, "已使用的商品序列号无法删除");
                    continue;
                }
            }

            dbConnect.runSqlUpdate("delete from sa_itemsku where siteid='" + siteid + "' and sa_itemskuid=" + sa_itemskuid);
        }
        return batchDeleteErr.getReturnObject().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 == 2) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

}