CUCU2/src/custom/restcontroller/webmanage/sale/order/OrderImport.java

package restcontroller.webmanage.sale.order;

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

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.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import beans.datacontrllog.DataContrlLog;
import beans.itemprice.ItemPrice;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING_CLEAN;
import restcontroller.R;
import restcontroller.system.attachment.Attachment;
import utility.tools.Math;

import static beans.order.Order.getOrderRows;

public class OrderImport extends Controller {

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

    /**
     * 下载订单导入单模板
     *
     * @return
     */
    @API(title = "下载订单导入单模板", apiversion = R.ID20230227194703.v1.class)
    public String downloadOrderExcel() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrder");

        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();

    }

    /**
     * 下载订单导入单模板
     *
     * @return
     */
    @API(title = "下载订单导入单模板", apiversion = R.ID20230308155703.v1.class)
    public String downloadOrderExcelSpecial() throws YosException {
        ExcelFactory excelFactory = new ExcelFactory("ImportTemplateForOrder");

        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);
        ExportExcelSpecial.setBatchDetailSheetColumn1(sheet);// 设置工作薄列宽
        XSSFCellStyle titleCellStyle1 = ExportExcelSpecial.createTitleCellStyle1(xssfFWorkbook);
        XSSFCellStyle titleCellStyle2 = ExportExcelSpecial.createTitleCellStyle2(xssfFWorkbook);
        XSSFCellStyle titleCellStyle3 = ExportExcelSpecial.createBodyCellStyle(xssfFWorkbook);
        XSSFCellStyle titleCellStyle4 = ExportExcelSpecial.createTitleCellStyle3(xssfFWorkbook);
        ExportExcelSpecial.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();

    }

    /**
     * 导入订单
     *
     * @return
     */
    @API(title = "导入订单明细", apiversion = R.ID20230227194803.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String uploadOrderMxByExcel() throws YosException {

        long sa_orderid = content.getLongValue("sa_orderid");
        long sa_projectid = 0;
        Rows orderRows = getOrderRows(this, sa_orderid);
        Rows sa_orderitemsrows = dbConnect
                .runSqlQuery("select itemid from sa_orderitems where sa_orderid=" + sa_orderid);
        ArrayList<Long> itemids = sa_orderitemsrows.toArrayList("itemid", new ArrayList<>());
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在，无法导入明细").toString();
        } else {
            if (!orderRows.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("非新建状态下的订单无法导入明细").toString();
            }
        }
        long sa_contractid = orderRows.get(0).getLong("sa_contractid");
        sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
        String type = orderRows.get(0).getString("type");

        // select t1.userid,t1.siteid,min(t2.discountrate) discountrate from sys_hr t1
        // inner join sa_salearea_hr t2 on t1.hrid=t2.hrid and t1.siteid=t2.siteid group
        // by t1.userid,t1.siteid

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

        content.put("ownertable", "sa_order");
        content.put("ownerid", sa_orderid);
        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();

            // 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("qty");
            keys.add("customproperties");
            keys.add("length");
            keys.add("width");
            keys.add("remarks");

            HashMap<String, CellType> cellmap = new HashMap<>();
            cellmap.put("itemno", CellType.STRING);

            Rows rows = e.getSheetRows(0, keys, cellmap, 3);
            int a = 1;
            int i = 0;
            // String msg = "";
            boolean iserr = false;
            Rows rowserr = new Rows();
            Rows rowssuc = new Rows();
            for (Row row : rows) {

                if (StringUtils.isEmpty(row.getString("itemno")) || StringUtils.isEmpty(row.getString("qty"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号，数量不能为空");
                    rowserr.add(row);
                    // rows.remove(row);
                    // msg = "手机号，省市县及来源不能为空";
                } else {
                    if (sa_projectid > 0) {
                        Rows itemsRows = dbConnect.runSqlQuery(
                                "select t1.itemid from sa_project_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.deleted=0 and t1.sa_projectid='"
                                        + sa_projectid + "' and t2.itemno='" + row.getString("itemno")
                                        + "' and t1.siteid='" + siteid + "'");
                        if (!itemsRows.isEmpty()) {
                            if(!isNumeric(row.getString("qty"))){
                                iserr = true;
                                row.put("msg", "数量不为数字格式，请检查");
                                rowserr.add(row);
                            } else if(!isNumeric(row.getString("length"))){
                                iserr = true;
                                row.put("msg", "长度不为数字格式，请检查");
                                rowserr.add(row);
                            } else if(!isNumeric(row.getString("width"))){
                                iserr = true;
                                row.put("msg", "宽度不为数字格式，请检查");
                                rowserr.add(row);
                            }else if(itemsRows.get(0).getBoolean("iscustomsize") && itemsRows.get(0).getLong("pricingmetod")==1 && itemsRows.get(0).getString("spec").equals("custom")){
                                if((StringUtils.isBlank(row.getString("length"))?0:row.getDouble("length"))==0){
                                    iserr = true;
                                    row.put("msg", "此商品必须填写长宽");
                                    rowserr.add(row);
                                }
                                if((StringUtils.isBlank(row.getString("width"))?0:row.getDouble("width"))==0){
                                    iserr = true;
                                    row.put("msg", "此商品必须填写长宽");
                                    rowserr.add(row);
                                }
                            }else{
                                row.put("itemid", itemsRows.get(0).getString("itemid"));
                                rowssuc.add(row);
                            }
                        } else {
                            iserr = true;
                            row.put("msg", "错误信息:该报价单所属项目中不存在商品编号为" + row.getString("itemno") + "的商品");
                            rowserr.add(row);
                        }

                    } else {
                        Rows itemsRows = dbConnect.runSqlQuery("select itemid,iscustomsize,pricingmetod,spec from plm_item where itemno='"
                                + row.getString("itemno").trim() + "' and siteid='" + siteid + "'");
                        if (!itemsRows.isEmpty()) {
                            if(!isNumeric(row.getString("qty"))){
                                iserr = true;
                                row.put("msg", "数量不为数字格式，请检查");
                                rowserr.add(row);
                            } else if(!isNumeric(StringUtils.isBlank(row.getString("length"))?"0":row.getString("length"))){
                                iserr = true;
                                row.put("msg", "长度不为数字格式，请检查");
                                rowserr.add(row);
                            } else if(!isNumeric(StringUtils.isBlank(row.getString("width"))?"0":row.getString("width"))){
                                iserr = true;
                                row.put("msg", "宽度不为数字格式，请检查");
                                rowserr.add(row);
                            }else if(itemsRows.get(0).getBoolean("iscustomsize") && itemsRows.get(0).getLong("pricingmetod")==1 && itemsRows.get(0).getString("spec").equals("custom")){
                                if((StringUtils.isBlank(row.getString("length"))?0:row.getDouble("length"))==0){
                                    iserr = true;
                                    row.put("msg", "此商品必须填写长宽");
                                    rowserr.add(row);
                                }else if((StringUtils.isBlank(row.getString("width"))?0:row.getDouble("width"))==0){
                                    iserr = true;
                                    row.put("msg", "此商品必须填写长宽");
                                    rowserr.add(row);
                                }else{
                                    row.put("itemid", itemsRows.get(0).getString("itemid"));
                                    rowssuc.add(row);

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

                }
            }
            long[] sa_orderitemsid = createTableID("sa_orderitems", rowssuc.size());
            if (!rowssuc.isEmpty()) {
                SQLFactory sqlFactoryupload = null;
                for (Row row : rowssuc) {
                    BigDecimal qty = BigDecimal.valueOf(row.getDouble("qty"));
                    if (!itemids.isEmpty()) {
                        if (itemids.contains(row.getLong("itemid"))) {
                            sqlFactoryupload = new SQLFactory(this, "订单商品明细_更新");
                            Rows sa_orderitemsids = dbConnect
                                    .runSqlQuery("select sa_orderitemsid from sa_orderitems where sa_orderid="
                                            + sa_orderid + " and itemid=" + row.getLong("itemid"));
                            sqlFactoryupload.addParameter("sa_orderitemsid",
                                    sa_orderitemsids.get(0).getLong("sa_orderitemsid"));
                        } else {
                            sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
                            sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
                        }
                    } else {
                        sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
                        sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
                    }
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("sa_orderid", sa_orderid);
                    sqlFactoryupload.addParameter("rowno", getRowNo(sa_orderid));
                    sqlFactoryupload.addParameter("itemid", row.getLong("itemid"));
                    sqlFactoryupload.addParameter("remarks", row.getString("remarks"));

                    sqlFactoryupload.addParameter("sa_promotion_itemsid", 0);
                    // 商品信息
                    Row itemRow = getItemRow(row.getLong("itemid"));
                    JSONObject item = new JSONObject();
                    item.put("itemid", row.getString("itemid"));

                    if(itemRow.getBoolean("iscustomsize")){
                        item.put("length", row.getString("length"));
                        item.put("width", row.getString("width"));
                        sqlFactoryupload.addParameter("length", row.getDouble("length"));
                        sqlFactoryupload.addParameter("width",  row.getDouble("width"));
                        sqlFactoryupload.addParameter("customproperties",  row.getString("customproperties"));
                    }else{
                        item.put("length", itemRow.getString("length"));
                        item.put("width", itemRow.getString("width"));
                        sqlFactoryupload.addParameter("length", itemRow.getDouble("length"));
                        sqlFactoryupload.addParameter("width",  itemRow.getDouble("width"));
                        sqlFactoryupload.addParameter("customproperties",  "");
                    }


                    BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
                    if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
                        conversionrate = BigDecimal.valueOf(1);
                    }
                    sqlFactoryupload.addParameter("itemno", itemRow.getString("itemno"));
                    sqlFactoryupload.addParameter("itemname", itemRow.getString("itemname"));
                    sqlFactoryupload.addParameter("model", itemRow.getString("model"));
                    sqlFactoryupload.addParameter("unit", itemRow.getString("unit"));
                    sqlFactoryupload.addParameter("auxunit", itemRow.getString("auxunit"));
                    sqlFactoryupload.addParameter("batchcontrol", itemRow.getLong("batchcontrol"));
                    sqlFactoryupload.addParameter("delivery", itemRow.getLong("delivery"));
                    sqlFactoryupload.addParameter("needdate", "null");
                    sqlFactoryupload.addParameter("deliverydate", "null");
                    sqlFactoryupload.addParameter("conversionrate", conversionrate);
                    sqlFactoryupload.addParameter("stockid", 0);
                    sqlFactoryupload.addParameter("position", 0);
                    sqlFactoryupload.addParameter("batchno", "");
                    sqlFactoryupload.addParameter("stockno", "");


                    // 订购数量
                    sqlFactoryupload.addParameter("qty", qty);
                    // 辅助单位数量
                    sqlFactoryupload.addParameter("auxqty", qty.divide(conversionrate));
                    BigDecimal price;
                    // 价格
                    ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
                    // 判断单价是否高于系统的单价
                    // 价格
                    BigDecimal defaultprice;
                    OrderItemsHelper orderItemsHelper = new OrderItemsHelper(this);
                    //批量查询商品信息
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("itemid", row.getLong("itemid"));
                    jsonArray=new JSONArray();
                    jsonArray.add(jsonObject);
                    RowsMap itemRowsMap = orderItemsHelper.getItemRowsMap(jsonArray);
                    BigDecimal saleprice  = orderItemsHelper.getSalePrice(orderRows.get(0), itemPrice,item,itemRowsMap);
                    switch (type) {
                        case "项目订单":
                            defaultprice = itemPrice.getContractprice(sa_contractid);
                            break;
                        case "工具订单":
                            defaultprice = itemPrice.getMarketprice();
                            break;
                        default:
                            if(itemRow.getBoolean("iscustomsize") && itemRow.getLong("pricingmetod")==1 && itemRow.getString("spec").equals("custom")){
                                logger.info("price:"+itemPrice.getContractprice());
                                logger.info("length:"+new BigDecimal(itemRow.getString("length")));
                                logger.info("width:"+new BigDecimal(itemRow.getString("width")));
                                defaultprice = itemPrice.getContractprice().multiply(new BigDecimal(itemRow.getString("length"))).multiply(new BigDecimal(itemRow.getString("width"))).divide(new BigDecimal("1000000"), 4, BigDecimal.ROUND_HALF_UP);
                            }else{
                                defaultprice = itemPrice.getContractprice();
                            }

                    }

                    // 单价,折后价（元）,取合同价
                    sqlFactoryupload.addParameter("defaultprice", defaultprice);
                    // 金额,折后金额（元）
                    sqlFactoryupload.addParameter("defaultamount", defaultprice.multiply(qty));
                    // 牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
                    sqlFactoryupload.addParameter("marketprice", itemPrice.getMarketprice());
                    sqlFactoryupload.addParameter("price", defaultprice);
                    // 折前金额（元）
                    sqlFactoryupload.addParameter("amount", defaultprice.multiply(qty));
                    sqlFactoryupload.addParameter("saleprice", saleprice);

                    sqllist.add(sqlFactoryupload.getSQL());
                    i++;
                }
            }
            if (sqllist != null && !sqllist.isEmpty()) {
                sqllist.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "导入", "订单明细导入成功").getSQL());
                dbConnect.runSqlUpdate(sqllist);
                // 重新排序
                updateRowNo(sa_orderid);

                new OrderItems(content).updateAccountclassinfos(sa_orderid);
                new OrderItemsHelper(this).executeDataFunction(sa_orderid);
                //生成摘要
                SQLFactory   sqlFactory = new SQLFactory(this, "查询定制费用统计");
                sqlFactory.addParameter("sa_orderid", sa_orderid);
                Rows tempRows = dbConnect.runSqlQuery(sqlFactory);
                if (tempRows.isNotEmpty()) {
                    String remarks = StringUtils.join(tempRows.toArray("remarks"), ";");
                    dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='" + remarks + "' WHERE sa_orderid='" + sa_orderid + "'");

                } else {
                    dbConnect.runSqlUpdate("UPDATE sa_order set abstract ='' WHERE sa_orderid='" + sa_orderid + "'");
                }
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sa_ordererr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "商品编号");
                map.put("qty", "数量");
                map.put("customproperties", "定制信息");
                map.put("length", "长度");
                map.put("width", "宽度");
                map.put("remarks", "备注");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("qty");
                colNameList.add("customproperties");
                colNameList.add("length");
                colNameList.add("width");
                colNameList.add("remarks");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("qty", String.class);
                keytypemap.put("customproperties", String.class);
                keytypemap.put("length", String.class);
                keytypemap.put("width", String.class);
                keytypemap.put("remarks", 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();
            }
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            // dbConnect.runSqlUpdate("delete from sa_order where sa_orderid=" +
            // sa_orderid);
            e1.printStackTrace();
            return getErrReturnObject().setErrMsg(e1.getMessage()).toString();
        }
        return getSucReturnObject().toString();
    }

    /**
     * 导入订单
     *
     * @return
     */
    @API(title = "导入订单明细", apiversion = R.ID20230308155803.v1.class)
    @CACHEING_CLEAN(apiClass = {Order.class, OrderItems.class, restcontroller.sale.order.Order.class})
    public String uploadOrderMxByExcelSpecial() throws YosException {

        long sa_orderid = content.getLongValue("sa_orderid");

        //通过版本更新订单表头数据
        beans.order.Order.updateOrderWithVersion(this);

        long sa_projectid = 0;
        Rows orderRows = getOrderRows(this, sa_orderid);
        Rows sa_orderitemsrows = dbConnect
                .runSqlQuery("select itemid from sa_orderitems where sa_orderid=" + sa_orderid);
        ArrayList<Long> itemids = sa_orderitemsrows.toArrayList("itemid", new ArrayList<>());
        if (orderRows.isEmpty()) {
            return getErrReturnObject().setErrMsg("订单不存在，无法导入明细").toString();
        } else {
            if (!orderRows.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("非新建状态下的订单无法导入明细").toString();
            }
        }
        long sa_contractid = orderRows.get(0).getLong("sa_contractid");
        sys_enterpriseid = orderRows.get(0).getLong("sys_enterpriseid");
        String type = orderRows.get(0).getString("type");

        // select t1.userid,t1.siteid,min(t2.discountrate) discountrate from sys_hr t1
        // inner join sa_salearea_hr t2 on t1.hrid=t2.hrid and t1.siteid=t2.siteid group
        // by t1.userid,t1.siteid

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

        content.put("ownertable", "sa_order");
        content.put("ownerid", sa_orderid);
        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();

            // 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("qty");
            keys.add("defaultprice");

            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();
            for (Row row : rows) {

                if (StringUtils.isEmpty(row.getString("itemno")) || StringUtils.isEmpty(row.getString("qty")) || StringUtils.isEmpty(row.getString("defaultprice"))) {
                    iserr = true;
                    row.put("msg", "错误信息:商品编号，数量，折前价不能为空");
                    rowserr.add(row);
                    // rows.remove(row);
                    // msg = "手机号，省市县及来源不能为空";
                } else {
                    if (sa_projectid > 0) {
                        Rows itemsRows = dbConnect.runSqlQuery(
                                "select t1.itemid from sa_project_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid  where t1.deleted=0 and t1.sa_projectid='"
                                        + sa_projectid + "' and t2.itemno='" + row.getString("itemno")
                                        + "' and t1.siteid='" + siteid + "'");
                        if (!itemsRows.isEmpty()) {
                            row.put("itemid", itemsRows.get(0).getString("itemid"));
                            rowssuc.add(row);
                        } else {
                            iserr = true;
                            row.put("msg", "错误信息:该报价单所属项目中不存在商品编号为" + row.getString("itemno") + "的商品");
                            rowserr.add(row);
                        }

                    } else {
                        Rows itemsRows = dbConnect.runSqlQuery("select itemid from plm_item where itemno='"
                                + row.getString("itemno").trim() + "' and siteid='" + siteid + "'");
                        if (!itemsRows.isEmpty()) {
                            row.put("itemid", itemsRows.get(0).getString("itemid"));
                            rowssuc.add(row);
                        } else {
                            iserr = true;
                            row.put("msg", "错误信息:不存在商品编号为" + row.getString("itemno").trim() + "的商品");
                            rowserr.add(row);
                        }
                    }

                }
            }
            long[] sa_orderitemsid = createTableID("sa_orderitems", rowssuc.size());
            if (!rowssuc.isEmpty()) {
                SQLFactory sqlFactoryupload = null;
                for (Row row : rowssuc) {
                    BigDecimal qty = BigDecimal.valueOf(row.getDouble("qty"));
                    BigDecimal defaultprice = BigDecimal.valueOf(row.getDouble("defaultprice"));
                    if (!itemids.isEmpty()) {
                        if (itemids.contains(row.getLong("itemid"))) {
                            sqlFactoryupload = new SQLFactory(this, "订单商品明细_更新");
                            Rows sa_orderitemsids = dbConnect
                                    .runSqlQuery("select sa_orderitemsid from sa_orderitems where sa_orderid="
                                            + sa_orderid + " and itemid=" + row.getLong("itemid"));
                            sqlFactoryupload.addParameter("sa_orderitemsid",
                                    sa_orderitemsids.get(0).getLong("sa_orderitemsid"));
                        } else {
                            sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
                            sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
                        }
                    } else {
                        sqlFactoryupload = new SQLFactory(this, "订单商品明细_新增");
                        sqlFactoryupload.addParameter("sa_orderitemsid", sa_orderitemsid[i]);
                    }
                    sqlFactoryupload.addParameter("siteid", siteid);
                    sqlFactoryupload.addParameter("userid", userid);
                    sqlFactoryupload.addParameter("username", username);
                    sqlFactoryupload.addParameter("sa_orderid", sa_orderid);
                    sqlFactoryupload.addParameter("rowno", getRowNo(sa_orderid));
                    sqlFactoryupload.addParameter("itemid", row.getLong("itemid"));
                    sqlFactoryupload.addParameter("remarks",  row.getString("remarks"));
                    sqlFactoryupload.addParameter("customproperties",  row.getString("customproperties"));
                    sqlFactoryupload.addParameter("width",  0);
                    sqlFactoryupload.addParameter("length",  0);
                    sqlFactoryupload.addParameter("sa_promotion_itemsid", 0);

                    // 商品信息
                    Row itemRow = getItemRow(row.getLong("itemid"));
                    BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
                    if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
                        conversionrate = BigDecimal.valueOf(1);
                    }
                    sqlFactoryupload.addParameter("itemno", itemRow.getString("itemno"));
                    sqlFactoryupload.addParameter("itemname", itemRow.getString("itemname"));
                    sqlFactoryupload.addParameter("model", itemRow.getString("model"));
                    sqlFactoryupload.addParameter("unit", itemRow.getString("unit"));
                    sqlFactoryupload.addParameter("auxunit", itemRow.getString("auxunit"));
                    sqlFactoryupload.addParameter("batchcontrol", itemRow.getLong("batchcontrol"));
                    sqlFactoryupload.addParameter("delivery", itemRow.getLong("delivery"));
                    sqlFactoryupload.addParameter("needdate", "null");
                    sqlFactoryupload.addParameter("deliverydate", "null");
                    sqlFactoryupload.addParameter("conversionrate", conversionrate);
                    sqlFactoryupload.addParameter("stockid", 0);
                    sqlFactoryupload.addParameter("position", 0);
                    sqlFactoryupload.addParameter("batchno", "");

                    // 订购数量
                    sqlFactoryupload.addParameter("qty", qty);
                    // 辅助单位数量
                    sqlFactoryupload.addParameter("auxqty", qty.divide(conversionrate));
                    BigDecimal price;
                    // 价格
                    ItemPrice itemPrice = ItemPrice.getItemPrice(this, sys_enterpriseid, row.getLong("itemid"));
                    // 判断单价是否高于系统的单价
                    // 价格
//					switch (type) {
//					case "项目订单":
//						defaultprice = itemPrice.getContractprice(sa_contractid);
//						break;
//					case "工具订单":
//						defaultprice = itemPrice.getMarketprice();
//						break;
//					default:
//						defaultprice = itemPrice.getContractprice();
//					}

                    // 单价,折后价（元）,取合同价
                    sqlFactoryupload.addParameter("defaultprice", defaultprice);
                    // 金额,折后金额（元）
                    sqlFactoryupload.addParameter("defaultamount", defaultprice.multiply(qty));
                    // 牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
                    sqlFactoryupload.addParameter("marketprice", itemPrice.getMarketprice());
                    sqlFactoryupload.addParameter("price", defaultprice);
                    // 折前金额（元）
                    sqlFactoryupload.addParameter("amount", defaultprice.multiply(qty));
                    sqllist.add(sqlFactoryupload.getSQL());
                    i++;
                }
            }
            if (sqllist != null && !sqllist.isEmpty()) {
                sqllist.add(DataContrlLog.createLog(this, "sa_order", sa_orderid, "导入", "订单明细导入成功").getSQL());
                dbConnect.runSqlUpdate(sqllist);
                // 重新排序
                updateRowNo(sa_orderid);
            }
            if (iserr) {
                ExcelFactory excelFactory = new ExcelFactory("sa_ordererr");
                HashMap<String, String> map = new HashMap<String, String>();
                map.put("itemno", "商品编号");
                map.put("qty", "数量");
                map.put("msg", "错误信息");
                ArrayList<String> colNameList = new ArrayList<String>();
                HashMap<String, Class> keytypemap = new HashMap<String, Class>();
                colNameList.add("itemno");
                colNameList.add("qty");
                colNameList.add("msg");
                keytypemap.put("itemno", String.class);
                keytypemap.put("qty", 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();
            }
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            // dbConnect.runSqlUpdate("delete from sa_order where sa_orderid=" +
            // sa_orderid);
            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)).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 == 2) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    public XSSFSheet addSheetSpecial(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 == 3) {
                    datarow.getCell(i1).setCellStyle(xssfCellStyle1);
                }
            }
        }
        return sheet;
    }

    /**
     * 获取当前订单的最大行号
     *
     * @param sa_orderid
     * @return
     * @throws YosException
     */
    public Long getRowNo(Long sa_orderid) throws YosException {
        Rows rows = dbConnect.runSqlQuery("SELECT COUNT(0) count from sa_orderitems WHERE sa_orderid = " + sa_orderid
                + " AND siteid = '" + siteid + "'");
        return rows.get(0).getLong("count") + 1;
    }

    /**
     * 重新对商品行排序
     *
     * @param sa_orderid
     * @throws YosException
     */
    public void updateRowNo(Long sa_orderid) throws YosException {
        String sql = "SELECT sa_orderitemsid from sa_orderitems WHERE sa_orderid= " + sa_orderid + "  and siteid = '"
                + siteid + "' ORDER  BY createdate ";
        ArrayList<Long> sa_orderitemsids = dbConnect.runSqlQuery(sql).toArrayList("sa_orderitemsid", new ArrayList<>());
        if (sa_orderitemsids.size() > 0) {
            int rowno = 1;
            StringBuffer where = new StringBuffer("");
            for (Long id : sa_orderitemsids) {
                where.append(" WHEN " + id + " THEN " + rowno);
                rowno++;
            }

            SQLFactory sqlFactory = new SQLFactory(this, "更新行号");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter_SQL("where", where);
            sqlFactory.addParameter_in("sa_orderitemsid", sa_orderitemsids);
            sql = sqlFactory.getSQL(false);
            dbConnect.runSqlUpdate(sql);
        }

    }

    /**
     * 查询商品信息
     *
     * @param itemid
     * @return
     * @throws YosException
     */
    public Row getItemRow(Long itemid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "查询商品信息");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("itemid", itemid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
        return rows.isNotEmpty() ? rows.get(0) : new Row();

    }

    public static boolean isNumeric(String strNum) {
        if (strNum == null) {
            return false;
        }
        return strNum.matches("-?\\d+(\\.\\d+)?");
    }

}