md_YOS/src/custom/restcontroller/webmanage/sale/order/OrderItemsHelper.java

package restcontroller.webmanage.sale.order;

import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.itemclass.ItemClass;
import beans.itemprice.ItemPrice;
import beans.order.Order;
import beans.parameter.Parameter;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.BaseClass;
import common.Controller;
import common.DataFunction;
import common.YosException;
import common.data.*;
import org.apache.commons.lang.StringUtils;

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

public class OrderItemsHelper extends BaseClass {

    Controller controller;
    JSONObject content;

    public OrderItemsHelper(Controller controller) {
        this.controller = controller;
        this.content = controller.content;
    }

    public void isAccountCanUsed(Long sa_accountclassid) throws YosException {
        if (dbConnect.runSqlQuery("select * from sa_accountclass  WHERE isused=1 and isorder=1 and sa_accountclassid=" + sa_accountclassid).isEmpty()) {
            throw new YosException(false, "当资金账户未启用或者不支持订货");
        }

    }

    public long getAccountClassId(String type) throws YosException {

        Long sa_accountclassid = content.getLong("sa_accountclassid");


        if (controller.siteid.equalsIgnoreCase("CCYOSG")) {
            if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("GC工程")) {
                return 69L;
            }
            if ((type.equals("标准订单") || type.equals("特殊订单")) && content.getString("tradefield").equals("整装工程")) {
                return 96L;
            }
        } else {
            if ((type.equals("标准订单") || type.equals("特殊订单")) && sa_accountclassid > 0) {
                return sa_accountclassid;
            }
        }
        if (type.equals("促销订单")) {
            Rows accountclassRows = dbConnect.runSqlQuery("select sa_accountclassid from sa_promotion where siteid='" + controller.siteid + "' and sa_promotionid=" + content.getLongValue("sa_promotionid"));
            if (!accountclassRows.isEmpty()) {
                return accountclassRows.get(0).getLong("sa_accountclassid");
            } else {
                return beans.order.Order.getDefaultAccount(controller.siteid, type);

            }
        }
        return beans.order.Order.getDefaultAccount(controller.siteid, type);

    }


    //执行是否自动返利结算
    public void executeAutoRebate(Long sa_orderid, Long sys_enterpriseid) throws YosException {

        Rows orderRows = Order.getOrderRows(controller, sa_orderid);
        if (orderRows.get(0).getString("type").equals("促销订单")) {
            return;
        }


        //是否自动返利结算
        String isrebate = Parameter.getString(controller.siteid, "isrebate");
        if (!isrebate.equals("1")) {
            return;
        }
        //查询有效期内，余额大于0 的经销商装修补贴返利
        String sql = "select * from sa_agentdecorationtrebate WHERE sys_enterpriseid=" + sys_enterpriseid + " and siteid='" + controller.siteid + "' and begindate <= current_date and enddate>=current_date and balance>0";
        Rows agentdecorationtrebatRows = dbConnect.runSqlQuery(sql);
        if (agentdecorationtrebatRows.isEmpty()) {
            return;
        }
        //查询装修返利方案
        Rows rebateschemeRows = dbConnect.runSqlQuery("select * from sa_rebatescheme where siteid='" + controller.siteid + "'");
        if (rebateschemeRows.isEmpty()) {
            return;
        }
        ArrayList<String> sqlList = new ArrayList<>();
        Long sa_rebateschemeid = rebateschemeRows.get(0).getLong("sa_rebateschemeid");
        Long sa_accountclassid = rebateschemeRows.get(0).getLong("sa_accountclassid");

        Rows rebatecategorys = dbConnect.runSqlQuery("select itemclassid from sa_rebatecategory where siteid='" + controller.siteid + "' and sa_rebateschemeid=" + sa_rebateschemeid);
        Rows rebateunproducts = dbConnect.runSqlQuery("select itemid from sa_rebateunproducts where siteid='" + controller.siteid + "' and sa_rebateschemeid=" + sa_rebateschemeid);
        Rows rebateunjoinproducts = dbConnect.runSqlQuery("select * from sa_rebateunjoinproducts where siteid='" +  controller.siteid + "' and sa_rebateschemeid=" + sa_rebateschemeid);

        ArrayList<Long> itemclsids = rebatecategorys.toArrayList("itemclassid", new ArrayList<Long>());

        itemclsids.addAll(ItemClass.getSubItemClassIds(controller, itemclsids));
        itemclsids.add(0l);
        ArrayList<Long> itemids = rebateunproducts.toArrayList("itemid", new ArrayList<Long>());
        itemids.add(0l);
        ArrayList<Long> joinitemids = rebateunjoinproducts.toArrayList("itemid", new ArrayList<Long>());
        String where =" 1=1 ";
        if(!joinitemids.isEmpty()){
            where=where+" and t1.itemid in"+joinitemids;
        }
        SQLFactory sqlFactory = new SQLFactory(controller, "查询返利数据");
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter_in("itemclassid", itemclsids);
        sqlFactory.addParameter_in("itemid", itemids);
        sqlFactory.addParameter_SQL("where", where);

        Rows rows = controller.dbConnect.runSqlQuery(sqlFactory);
        BigDecimal saorderamount = BigDecimal.ZERO;
        for (Row row : rows) {
            saorderamount = saorderamount.add(row.getBigDecimal("amount"));
        }
        //查询定制费用
        sql = "SELECT ifnull(sum(costamount),0) costamount from sa_order_cost WHERE sa_orderid=" + sa_orderid;
        BigDecimal costamount = dbConnect.runSqlQuery(0, sql).getBigDecimal("costamount");
        saorderamount = saorderamount.add(costamount);


        BigDecimal balance = agentdecorationtrebatRows.get(0).getBigDecimal("balance");
        BigDecimal brate = agentdecorationtrebatRows.get(0).getBigDecimal("brate");


        BigDecimal approvedamount = BigDecimal.ZERO;
        if (balance.compareTo(saorderamount.multiply(brate)) >= 0) {
            approvedamount = saorderamount.multiply(brate);
        } else if (balance.compareTo(BigDecimal.ZERO) > 0) {
            approvedamount = balance;
        }
//        System.out.println(saorderamount);
//        System.out.println(approvedamount);

        if (approvedamount.compareTo(BigDecimal.ZERO) > 0) {
            ArrayList<Long> sa_orderitemsids = rows.toArrayList("sa_orderitemsid", new ArrayList<>());
            sa_orderitemsids.add(0l);

            //更新订单明细的装修返利标志
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(controller, "sa_orderitems");
            updateSQL.setWhere("sa_orderitemsid", sa_orderitemsids);
            updateSQL.setValue("decorationrebateflag", "1");
            sqlList.add(updateSQL.getSQL());

            //更新sa_agentdecorationtrebate
            updateSQL = SQLFactory.createUpdateSQL(controller, "sa_agentdecorationtrebate");
            updateSQL.setWhere("sys_enterpriseid", sys_enterpriseid);
            updateSQL.setSiteid(controller.siteid);
            updateSQL.setValue("balance", balance.subtract(approvedamount));
            sqlList.add(updateSQL.getSQL());

            //生成收入凭证
            CashbillEntity entity = new CashbillEntity();
            entity.setAmount(approvedamount);
            entity.setDiscountamountamount(BigDecimal.ZERO);
            entity.setOwnerid(sa_orderid);
            entity.setOwnertable("sa_order");
            entity.setSource("订单自动返利");
            entity.setSourcenote(orderRows.get(0).getString("sonum"));
            entity.setRemarks(orderRows.get(0).getString("sonum") + "订单自动返利");
            entity.setType("公司支持");
            entity.setTypemx("装修返利");
            JSONObject cashbillIncome = Accountbalance.createCashbillIncome(controller, sys_enterpriseid, sa_accountclassid, entity, true, true);
            sqlList.addAll(cashbillIncome.getJSONArray("sqlList").toJavaList(String.class));

            dbConnect.runSqlUpdate(sqlList);
        }
    }

    public JSONArray toJsonArray(String column, List<Row> list) {
        JSONArray array = new JSONArray();
        array.add("0");
        for (Row row : list) {
            Object value = row.get(column);
            array.add(value);
        }
        return array;
    }

    public RowsMap CalculateAmount(ArrayList<Long> itemclsids, Long sys_enterpriseid,
                                   String itemids) throws YosException {
        Rows rows = null;
        itemclsids.addAll(ItemClass.getSubItemClassIds(controller, itemclsids));
        itemclsids.add(0l);
        if (itemids.equals("()")) {
            rows = dbConnect.runSqlQuery("select t2.closedate,t2.sys_enterpriseid,sum(if(t1.isclose,t1.amount,t1.price*(t1.qty-t1.undeliqty))) amount from sa_orderitems t1 \n" +
                    "inner join sa_order t2 on  t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid\n" +
                    "inner join plm_item t3 on  t1.itemid=t3.itemid and t1.siteid=t3.siteid where t2.type !='促销订单' and ifnull( t2.closedate,'')!='' and t2.sa_accountclassid!=0 and t2.sys_enterpriseid =" + sys_enterpriseid + "  and t2.status in('关闭') and t1.itemid in(select itemid from sa_itemsaleclass where itemclassid in" + itemclsids.toString().replace("[", "(").replace("]", ")") + " ) and t1.DECORATIONREBATEFLAG=0 and t1.siteid='" + controller.siteid + "' group by t2.sys_enterpriseid,t2.closedate");

        } else {
            rows = dbConnect.runSqlQuery("select  t2.closedate,t2.sys_enterpriseid,sum(if(t1.isclose,t1.amount,t1.price*(t1.qty-t1.undeliqty))) amount from sa_orderitems t1 \n" +
                    "inner join sa_order t2 on  t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid\n" +
                    "inner join plm_item t3 on  t1.itemid=t3.itemid and t1.siteid=t3.siteid where t2.type !='促销订单' and ifnull( t2.closedate,'')!='' and t2.sa_accountclassid!=0 and t2.sys_enterpriseid =" + sys_enterpriseid + "  and t2.status in('关闭') and t1.itemid not in" + itemids + " and  t1.itemid in(select itemid from sa_itemsaleclass where itemclassid in" + itemclsids.toString().replace("[", "(").replace("]", ")") + " )   and t1.DECORATIONREBATEFLAG=0 and t1.siteid='" + controller.siteid + "' group by t2.sys_enterpriseid,t2.closedate");

        }
        return rows.toRowsMap("sys_enterpriseid");


    }

    public RowsMap QuerySaorderNum(ArrayList<Long> itemclsids, Long sys_enterpriseid,
                                   String itemids) throws YosException {
        Rows rows = null;
        itemclsids.addAll(ItemClass.getSubItemClassIds(controller, itemclsids));
        itemclsids.add(0l);
        if (itemids.equals("()")) {
            rows = dbConnect.runSqlQuery("select t2.closedate,t2.sys_enterpriseid,t2.sa_orderid,SUM(if(t1.isclose,t1.amount,t1.price*(t1.qty-t1.undeliqty))) amount from sa_orderitems t1 \n" +
                    "inner join sa_order t2 on  t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid\n" +
                    "inner join plm_item t3 on  t1.itemid=t3.itemid and t1.siteid=t3.siteid where t2.type !='促销订单' and ifnull( t2.closedate,'')!='' and t2.sa_accountclassid!=0 and t2.sys_enterpriseid =" + sys_enterpriseid + "  and t2.status in('关闭') and t1.itemid in(select itemid from sa_itemsaleclass where itemclassid in" + itemclsids.toString().replace("[", "(").replace("]", ")") + " )  and t1.DECORATIONREBATEFLAG=0 and t1.siteid='" + controller.siteid + "' group by t2.sa_orderid,t2.sys_enterpriseid,t2.closedate");

        } else {
            rows = dbConnect.runSqlQuery("select t2.closedate,t2.sys_enterpriseid,t2.sa_orderid,SUM(if(t1.isclose,t1.amount,t1.price*(t1.qty-t1.undeliqty))) amount from sa_orderitems t1 \n" +
                    "inner join sa_order t2 on  t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid\n" +
                    "inner join plm_item t3 on  t1.itemid=t3.itemid and t1.siteid=t3.siteid where t2.type !='促销订单' and ifnull( t2.closedate,'')!='' and t2.sa_accountclassid!=0 and t2.sys_enterpriseid =" + sys_enterpriseid + "  and t2.status in('关闭') and t1.itemid not in" + itemids + " and  t1.itemid in(select itemid from sa_itemsaleclass where itemclassid in" + itemclsids.toString().replace("[", "(").replace("]", ")") + " )  and t1.DECORATIONREBATEFLAG=0 and t1.siteid='" + controller.siteid + "' group by t2.sa_orderid,t2.sys_enterpriseid,t2.closedate");


        }
        return rows.toRowsMap("sys_enterpriseid");

    }

    public RowsMap QuerySaorderDetailId(ArrayList<Long> itemclsids, Long sys_enterpriseid,
                                        String itemids) throws YosException {
        Rows rows = null;
        itemclsids.addAll(ItemClass.getSubItemClassIds(controller, itemclsids));
        itemclsids.add(0l);
        String sql = "select t2.closedate,t2.sys_enterpriseid,t1.sa_orderitemsid  from sa_orderitems t1 " +
                "inner join sa_order t2 on  t1.sa_orderid=t2.sa_orderid and t1.siteid=t2.siteid " +
                "inner join plm_item t3 on  t1.itemid=t3.itemid and t1.siteid=t3.siteid " +
                "where t2.type !='促销订单' " +
                "and ifnull( t2.closedate,'')!='' " +
                "and t2.sa_accountclassid!=0 and t2.sys_enterpriseid =" + sys_enterpriseid + "  " +
                "and t2.status in('关闭') " +
                "and t1.itemid in(select itemid from sa_itemsaleclass where itemclassid in" + itemclsids.toString().replace("[", "(").replace("]", ")") + " )  " +
                "and t1.DECORATIONREBATEFLAG=0 and t1.siteid='" + controller.siteid + "' ";
        if (!itemids.equals("()")) {
            sql = sql + " and t1.itemid not in" + itemids;
        }
        sql = sql + "group by t2.sys_enterpriseid,t2.closedate,t1.sa_orderitemsid";
        System.err.println(sql);
        rows = dbConnect.runSqlQuery(sql);
        return rows.toRowsMap("sys_enterpriseid");


    }

//    //创建返利结算单表头
//    public InsertSQL getrebateaccounthead(Long sa_rebateaccountheadid) throws YosException {
//        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, "sa_rebateaccounthead");
//        insertSQL.setUniqueid(sa_rebateaccountheadid).setSiteid(controller.siteid);
//        insertSQL.setValue("billdate", getDate_Str());
//        insertSQL.setValue("status", "新建");
//        insertSQL.setValue("billno", controller.createBillCode("rebateaccountheadbill"));
//        return insertSQL;
//    }
//
//    //创建返利结算单表体
//    public InsertSQL getrebateaccount(Long sa_rebateaccountid, Long sa_rebateaccountheadid, Long sys_enterpriseid) throws YosException {
//        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, "sa_rebateaccount");
//        insertSQL.setUniqueid(sa_rebateaccountid).setSiteid(controller.siteid);
//        //核准金额
//        insertSQL.setValue("approvedamount", 10);
//        //装修返利金额
//        insertSQL.setValue("rebateamount", 10);
//        insertSQL.setValue("sa_rebateaccountheadid", sa_rebateaccountheadid);
//        //合作企业档案ID
//        insertSQL.setValue("sys_enterpriseid", sys_enterpriseid);
//        //行号
//        insertSQL.setValue("rowno", 1);
//        //累计订购返利商品金额
//        insertSQL.setValue("accumulatedamount", 10);
//        return insertSQL;
//    }
//
//    //创建返利结算单表体
//    public InsertSQL getrebateaccountdetail(Long sa_orderid, Long sa_rebateaccountid) throws YosException {
//        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, "sa_rebateaccountdetail");
//        insertSQL.setUniqueid(createTableID("sa_rebateaccountdetail")).setSiteid(controller.siteid);
//        //核准金额
//        insertSQL.setValue("sa_orderid", sa_orderid);
//        //装修返利金额
//        insertSQL.setValue("sa_rebateaccountid", sa_rebateaccountid);
//        insertSQL.setValue("rateamount", 10);
//        //合作企业档案ID
//        insertSQL.setValue("amount", 10);
//        return insertSQL;
//    }

    //查询并执行商品费用方案方法
    public void executeDataFunction(Long sa_orderid) throws YosException {
        SQLFactory sqlFactory = new SQLFactory(this, "查询商品费用方案方法");
        sqlFactory.addParameter("siteid", controller.siteid);
        sqlFactory.addParameter("sa_orderid", sa_orderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        if(rows.isEmpty()){
            //清空费用表
            DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(controller, "sa_order_cost");
            deleteSQL.setWhere("sa_orderid", sa_orderid);
            deleteSQL.delete();
        }
        if(rows.isNotEmpty()){
            DataFunction df = DataFunction.get(controller, rows.get(0).getString("functionname"));
            df.addParameter("sa_orderid", sa_orderid);
            df.action();
        }
    }


    public void checkOffOrderItems(Long sa_orderid, String tablename, Long sa_promotionid) throws YosException {
        String sql = "select t2.itemid from sa_orderitems t1 inner join " + tablename + " t2 on t1.siteid=t2.siteid and t1.itemid=t2.itemid where t1.sa_orderid="
                + sa_orderid + " and t1.siteid='" + controller.siteid + "' and t2.isonsale!=1";
        if (tablename.equals("sa_promotion_items")) {
            sql = sql + " and t2.sa_promotionid=" + sa_promotionid;
        }

        Rows iteminfos = dbConnect.runSqlQuery(sql);
        if (iteminfos.isNotEmpty()) {
            Row itemRow = dbConnect.runSqlQuery(0, "SELECT itemno from plm_item WHERE itemid=" + iteminfos.get(0).getString("itemid"));
            //throw new YosException(false, "检测到品号" + itemRow.getString("itemno") + "未上架，请删除后重试");
        }
    }

    public void checkOffOrderItems(String tablename, Long itemid, Long sa_promotionid) throws YosException {
        String sql = "select t1.itemid from " + tablename + " t1  where t1.siteid='" + controller.siteid + "' and t1.isonsale!=1 and t1.itemid=" + itemid;
        if (tablename.equals("sa_promotion_items")) {
            sql = sql + " and t1.sa_promotionid=" + sa_promotionid;
        }
        Rows iteminfos = dbConnect.runSqlQuery(sql);
        if (iteminfos.isNotEmpty()) {
            Row itemRow = dbConnect.runSqlQuery(0, "SELECT itemno from plm_item WHERE itemid=" + iteminfos.get(0).getString("itemid"));
            //throw new YosException(false, "检测到品号" + itemRow.getString("itemno") + "未上架，请删除后重试");
        }
    }

    /**
     * 是否符合商品起订量和增量判断
     *
     * @param type
     * @param itemRowsMap
     * @param item
     * @param itemRow
     * @param promotionitems
     * @throws YosException
     */
    public void compareQty(String type, RowsMap itemRowsMap, JSONObject item, Row itemRow, Rows promotionitems) throws YosException {
        BigDecimal qty = item.getBigDecimal("qty");
        String itemid = item.getString("itemid");
        Rows itemRows = itemRowsMap.getOrDefault(itemid, new Rows());
        if (qty.compareTo(BigDecimal.ZERO) == 0) {
            throw new YosException(false, "品号为" + itemRows.get(0).getString("itemno") + "的商品的添加数量为0，不允许添加");
        }

        if (!type.equals("促销订单")) {
            if (!type.equals("特殊订单")) {
                if (itemRowsMap.containsKey(itemid)) {

                    if (itemRows.isNotEmpty()) {
                        BigDecimal orderaddqty = itemRows.get(0).getBigDecimal("orderaddqty");
                        BigDecimal orderminqty = itemRows.get(0).getBigDecimal("orderminqty");
                        if (orderaddqty.compareTo(BigDecimal.ZERO) != 0) {
                            if (((qty.subtract(orderminqty)).remainder(orderaddqty)).compareTo(BigDecimal.ZERO) != 0 || qty.compareTo(orderminqty) < 0) {
                                throw new YosException(false, "品号为" + itemRows.get(0).getString("itemno") + "的商品数量不符合该商品的起订量和增量");
                            }
                        }
                    }
                }
            } else {
                if (qty.compareTo(BigDecimal.ZERO) <= 0) {
                    throw new YosException(false, "品号为" + itemRows.get(0).getString("itemno") + "的商品数量必须大于0");
                }
            }
        }
        RowsMap pRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
        long sa_promotionid =0;
        if(promotionitems.isNotEmpty()){
             sa_promotionid = promotionitems.get(0).getLong("sa_promotionid");
        }
        if (type.equals("促销订单")) {
            if (pRowsMap.containsKey(item.getString("sa_promotion_itemsid"))) {
                String saPromotionItemsid = item.getString("sa_promotion_itemsid");
                if (pRowsMap.get(saPromotionItemsid).isNotEmpty()) {
                    BigDecimal saledqty = pRowsMap.get(saPromotionItemsid).get(0).getBigDecimal("saledqty");
                    BigDecimal groupqty = pRowsMap.get(saPromotionItemsid).get(0).getBigDecimal("groupqty");
                    BigDecimal orderaddqty = pRowsMap.get(saPromotionItemsid).get(0).getBigDecimal("orderaddqty");
                    BigDecimal orderminqty = pRowsMap.get(saPromotionItemsid).get(0).getBigDecimal("orderminqty");
                    boolean islimit = pRowsMap.get(saPromotionItemsid).get(0).getBoolean("islimit");
                    Rows rows =dbConnect.runSqlQuery("select * from sa_promotion where sa_promotionid="+sa_promotionid);
                    if(rows.isNotEmpty()){
                        if(!rows.get(0).getString("type").equals("套餐活动")){
                            if (orderaddqty.compareTo(BigDecimal.ZERO) != 0) {
                                if (((qty.subtract(orderminqty)).remainder(orderaddqty)).compareTo(BigDecimal.ZERO) != 0 || qty.compareTo(orderminqty) < 0) {
                                    throw new YosException(false, "品号为" + itemRowsMap.get(itemid).get(0).getString("itemno") + "的商品数量不符合该商品的起订量和增量");
                                }
                            }
                        }
                    }

                    if (islimit) {
                        if (groupqty.compareTo(saledqty.add(qty)) < 0) {
                            throw new YosException(false, "品号：" + itemRow.getString("itemno") + "已超过限购数量：" + groupqty.subtract(saledqty));
                        }
                    }
                }
            }
        }
    }


    /**
     * 获取定制属性
     *
     * @param itemRowsMap
     * @param item
     * @return
     * @throws YosException
     */
    public JSONArray getCustomProperties(RowsMap itemRowsMap, JSONObject item) throws YosException {

        JSONArray customproperties = new JSONArray();

        Rows customschemeRows = dbConnect.runSqlQuery("select * from sa_customscheme_items where siteid='" + controller.siteid + "'");
        RowsMap customschemeRowsMap = customschemeRows.toRowsMap("sa_customschemeid");

        String itemid = item.getString("itemid");
        if (itemRowsMap.containsKey(itemid)) {
            if (itemRowsMap.get(itemid).isNotEmpty()) {
                if (itemRowsMap.get(itemid).get(0).getLong("sa_customschemeid") > 0 && !itemRowsMap.get(itemid).get(0).getBoolean("iscustomsize")) {
//                    System.out.println(0000);
                    if (customschemeRowsMap.containsKey(itemRowsMap.get(itemid).get(0).getString("sa_customschemeid"))) {
//                        System.out.println(111111);
                        for (Row customschemeItem : (customschemeRowsMap.get(itemRowsMap.get(itemid).get(0).getString("sa_customschemeid")))) {
//                            System.out.println(customschemeItem.getString("description"));
//                            System.out.println(2222);
                            customproperties.add(customschemeItem.getString("description") + ":" + itemRowsMap.get(itemid).get(0).getString(customschemeItem.getString("value")));
                        }
                    }
                } else if (itemRowsMap.get(itemid).get(0).getBoolean("iscustomsize")) {
//                    System.out.println(3333);
                    if (customschemeRowsMap.containsKey(itemRowsMap.get(itemid).get(0).getString("sa_customschemeid"))) {
//                        System.out.println(4444);
                        for (Row customschemeItem : (customschemeRowsMap.get(itemRowsMap.get(itemid).get(0).getString("sa_customschemeid")))) {
//                            System.out.println(5555);
//                            System.out.println((customschemeRowsMap.get(itemRowsMap.get(itemid).get(0).getString("sa_customschemeid"))).size());
                            if (!customschemeItem.getString("value").equals("spec")) {
//                                customproperties.add(customschemeItem.getString("description") + ":" + itemRowsMap.get(itemid).get(0).getString(customschemeItem.getString("value")));
                            } else {
                                customproperties.add(getcustomproperties(item));
                            }

                        }
                    } else {
                        customproperties.add(getcustomproperties(item));
                    }
                }

            }

        }

        return customproperties;
    }


    public String getcustomproperties(JSONObject item) {
        String material = item.getStringValue("custommaterial");
        String colors = item.getStringValue("customcolors");
        String cheek = item.getStringValue("customcheek");
        String length = item.getStringValue("length", false, "0");
        String width = item.getStringValue("width", false, "0");

        String str = "";
        if (!StringUtils.isBlank(material) && !material.equals("0")) {
            str = "选项:" + material;
        }
        if (!StringUtils.isBlank(colors) && !colors.equals("0")) {
            if (str.isEmpty()) {
                str = "颜色:" + colors;
            } else {
                str = str + ";" + "颜色:" + colors;
            }

        }
        if (!StringUtils.isBlank(cheek) && !cheek.equals("0")) {
            if (str.isEmpty()) {
                str = "工艺:" + cheek;
            } else {
                str = str + ";" + "工艺:" + cheek;
            }

        }
        if (!length.equals("0") && !width.equals("0")) {
            String size = "尺寸:" + length + "×" + width;
            if (str.isEmpty()) {
                str = size;
            } else {
                str = str + ";" + size;
            }
        }
        return str;
    }

    /**
     * 获取定制尺寸信息
     *
     * @param itemRowsMap
     * @param item
     * @return
     * @throws YosException
     */
    public String getCustomSpec(RowsMap itemRowsMap, JSONObject item) throws YosException {

        String spec = "";
        Rows customschemeRows = dbConnect.runSqlQuery("select * from sa_customscheme_items where siteid='" + controller.siteid + "'");
        RowsMap customschemeRowsMap = customschemeRows.toRowsMap("sa_customschemeid");

        String itemid = item.getString("itemid");
        if (itemRowsMap.containsKey(itemid)) {
            if (itemRowsMap.get(itemid).isNotEmpty()) {
                if (!itemRowsMap.get(itemid).get(0).getBoolean("iscustomsize")) {
                    spec = itemRowsMap.get(itemid).get(0).getString("spec");
                } else {
                    spec = item.getStringValue("length") + "*" + item.getStringValue("width");
                }

            }

        }

        return spec;
    }

    /**
     * 批量查询商品信息
     *
     * @param items
     * @return
     * @throws YosException
     */
    public RowsMap getItemRowsMap(JSONArray items) throws YosException {
        ArrayList<Long> itemids = new ArrayList<>();
        for (Object obj : items) {
            JSONObject item = (JSONObject) obj;
            itemids.add(item.getLong("itemid"));
        }
        if (itemids.size() == 0) {
            itemids.add(-1L);
        }

        String sql = "select t1.*,t2.material from plm_item t1 left join plm_itemextend t2 ON t2.itemid = t1.itemid AND t2.siteid = t1.siteid where t1.siteid='" + controller.siteid + "' and t1.itemid in  " + itemids;
        sql = sql.replace("[", "(").replace("]", ")");
        return dbConnect.runSqlQuery(sql).toRowsMap("itemid");
    }

    public UpdateSQL getUpdateSql() throws YosException {
        System.err.println("123");
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(controller, "sa_order");
        if (controller.content.containsKey("sa_order_v")) {
            updateSQL.setDataVersion(content.getIntValue("sa_order_v"));
        }
        updateSQL.setValue("changeby", controller.username);
        updateSQL.setWhere("sa_orderid", content.getLongValue("sa_orderid"));
        updateSQL.setWhere("siteid", controller.siteid);

        System.err.println(updateSQL.getSQL());

        return updateSQL;

    }


    String sa_orderitems = "sa_orderitems";

    public InsertSQL getInsertSQL(JSONArray customproperties, long sa_orderitemsid, JSONObject item, Row itemRow, String type,
                                  BigDecimal defaultprice, BigDecimal price, BigDecimal saleprice, BigDecimal marketprice,
                                  String spec, Rows promotionitems,long width,long length,BigDecimal custamount,BigDecimal rwpricerate,BigDecimal pricerate) throws YosException {
        Long itemid = item.getLong("itemid");
        BigDecimal qty = item.getBigDecimal("qty");
        RowsMap promotionitemsRowsMap = promotionitems.toRowsMap("sa_promotion_itemsid");
        String signaturecode = "";
        if (type.equals("促销订单")) {
            if (promotionitemsRowsMap.containsKey(item.getString("sa_promotion_itemsid"))) {
                if (promotionitemsRowsMap.get(item.getString("sa_promotion_itemsid")).isNotEmpty()) {
                    signaturecode = promotionitemsRowsMap.get(item.getString("sa_promotion_itemsid")).get(0).getString("signaturecode");
                }
            }
        }

        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, sa_orderitems);
        insertSQL.setValue("customproperties", customproperties.toJSONString().replace("[", "").replace("]", "").replace("\"", ""));
        insertSQL.setValue("siteid", controller.siteid);
        insertSQL.setValue("sa_orderitemsid", sa_orderitemsid);
        insertSQL.setValue("sa_orderid", content.getLongValue("sa_orderid"));
        insertSQL.setValue("rowno", 1);
        insertSQL.setValue("itemid", itemid);
        insertSQL.setValue("remarks", item.getStringValue("remarks") + signaturecode);
        insertSQL.setValue("itemno", itemRow.getString("itemno"));
        insertSQL.setValue("itemname", itemRow.getString("itemname"));
        insertSQL.setValue("model", itemRow.getString("model"));
        insertSQL.setValue("unit", itemRow.getString("unit"));
        insertSQL.setValue("auxunit", itemRow.getString("auxunit"));
        insertSQL.setValue("batchcontrol", itemRow.getLong("batchcontrol"));
        insertSQL.setValue("stockno", item.getOrDefault("stockno", "null"));
        insertSQL.setValue("position", item.getOrDefault("position", "null"));
        insertSQL.setValue("batchno", item.getOrDefault("batchno", "null"));
        insertSQL.setValue("spec", spec);
        insertSQL.setValue("custamount",custamount);
        insertSQL.setValue("rwpricerate",rwpricerate);
        insertSQL.setValue("pricerate",pricerate);
        insertSQL.setValue("sa_promotion_itemsid", item.getLongValue("sa_promotion_itemsid"));
        insertSQL.setValue("delivery", item.getStringValue("delivery"));
        insertSQL.setValue("cardno", item.getStringValue("cardno"));
        insertSQL.setValue("sa_workorderid", item.getLongValue("sa_workorderid"));

        String deliverydate = item.getStringValue("deliverydate");
        Rows rows = dbConnect.runSqlQuery("SELECT deliverydate,defaultprice from sa_orderitems WHERE sa_orderitemsid =" + sa_orderitemsid);
        if (deliverydate.isEmpty()) {
            if (rows.isNotEmpty()) {
                deliverydate = rows.get(0).getString("deliverydate");
            } else {
                deliverydate = "null";
            }
        }
        String needdate = item.getStringValue("needdate");
        insertSQL.setValue("needdate", needdate.equals("") ? "null" : needdate);
        insertSQL.setValue("deliverydate", deliverydate.equals("") ? "null" : deliverydate);
        BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");

        if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
            conversionrate = BigDecimal.valueOf(1);
        }
        insertSQL.setValue("conversionrate", conversionrate);
        //订购数量
        insertSQL.setValue("qty", qty);
        insertSQL.setValue("undeliqty", qty);
        insertSQL.setValue("deliedqty", 0);
        //辅助单位数量
        insertSQL.setValue("auxqty", qty.divide(conversionrate));
        if (type.equals("特殊订单")) {
            if (item.containsKey("defaultprice")) {
                if (item.getBigDecimal("price").compareTo(BigDecimal.ZERO) > 0 && item.getBigDecimal("amount").compareTo(BigDecimal.ZERO) <= 0) {
                    insertSQL.setValue("price", item.getBigDecimal("price"));
                    //折前金额（元）
                    insertSQL.setValue("amount", price.multiply(qty));
                } else if (item.getBigDecimal("price").compareTo(BigDecimal.ZERO) <= 0 && item.getBigDecimal("amount").compareTo(BigDecimal.ZERO) > 0) {
                    insertSQL.setValue("price", item.getBigDecimal("amount").divide(qty, 6, BigDecimal.ROUND_HALF_UP));
                    //折前金额（元）
                    insertSQL.setValue("amount", item.getBigDecimal("amount"));
                } else {
                    insertSQL.setValue("price", price);
                    //折前金额（元）
                    insertSQL.setValue("amount", price.multiply(qty));
                }
            } else {
                insertSQL.setValue("price", price);
                //折前金额（元）
                insertSQL.setValue("amount", price.multiply(qty));
            }
        } else {
            insertSQL.setValue("price", price);
            //折前金额（元）
            insertSQL.setValue("amount", price.multiply(qty));
        }
        //单价,折后价（元）,取合同价
        insertSQL.setValue("defaultprice", defaultprice);
        insertSQL.setValue("saleprice", saleprice);

        //金额,折后金额（元）
        insertSQL.setValue("defaultamount", defaultprice.multiply(qty));
        //牌价、市场价（元）,标准订单牌价取商品价格，项目订单取合同里的牌价
        insertSQL.setValue("marketprice", marketprice);

        insertSQL.setValue("width", width);

        insertSQL.setValue("length", length);

        if(type.equals("配件订单")){
            if(StringUtils.isBlank(item.getStringValue("cardno"))){
                insertSQL.setValue("canoffqty", qty);
            }else{
                Rows aftersalesbomrows = dbConnect.runSqlQuery("select t2.sa_aftersalesbomid from sa_warrantycard t1 inner join sa_aftersalesbom_items t2 on t1.itemid=t2.itemid inner join sa_aftersalesbom t3 on t2.sa_aftersalesbomid=t3.sa_aftersalesbomid where t1.cardno='"+item.getStringValue("cardno")+"'");
                if(aftersalesbomrows.isNotEmpty()){
                    Rows aftersalesbompjrows = dbConnect.runSqlQuery("select qty from sa_aftersalesbom_pjs t1 where t1.sa_aftersalesbomid="+aftersalesbomrows.get(0).getString("sa_aftersalesbomid")+" and t1.itemid="+itemid);
                    if(aftersalesbompjrows.isNotEmpty()){
                        insertSQL.setValue("canoffqty", aftersalesbompjrows.get(0).getBigDecimal("qty").compareTo(qty)>0?qty:aftersalesbompjrows.get(0).getBigDecimal("qty"));
                    }else{
                        insertSQL.setValue("canoffqty", 1);
                    }
                }else{
                    insertSQL.setValue("canoffqty", 1);
                }
            }

        }else{
            insertSQL.setValue("canoffqty", 0);
        }

        return insertSQL;
    }

    public UpdateSQL getUpdateSQL(long sa_orderitemsid, JSONObject item, Row itemRow, String type, BigDecimal defaultprice, BigDecimal price, BigDecimal saleprice,long width,long length) throws YosException {
        BigDecimal qty = item.getBigDecimal("qty");

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(controller, sa_orderitems);
        //订购数量
        updateSQL.setValue("qty", qty);
        //辅助单位数量
        BigDecimal conversionrate = itemRow.getBigDecimal("conversionrate");
        if (conversionrate.compareTo(BigDecimal.ZERO) <= 0) {
            conversionrate = BigDecimal.valueOf(1);
        }
        updateSQL.setValue("itemid", item.getLongValue("itemid"));
        updateSQL.setValue("itemno", itemRow.getString("itemno"));
        updateSQL.setValue("itemname", itemRow.getString("itemname"));
        updateSQL.setValue("model", itemRow.getString("model"));
        updateSQL.setValue("unit", itemRow.getString("unit"));
        updateSQL.setValue("auxqty", qty.divide(conversionrate));
        updateSQL.setValue("undeliqty", qty);
        updateSQL.setValue("remarks", item.getStringValue("remarks"));
        updateSQL.setValue("stockno", item.getOrDefault("stockno", "null"));
        updateSQL.setValue("position", item.getOrDefault("position", "null"));
        updateSQL.setValue("batchno", item.getOrDefault("batchno", "null"));
        updateSQL.setValue("custamount", item.getBigDecimal("custamount"));
        updateSQL.setValue("delivery", item.getStringValue("delivery"));
        updateSQL.setValue("cardno", item.getStringValue("cardno"));
        updateSQL.setValue("sa_workorderid", item.getLongValue("sa_workorderid"));

        String deliverydate = item.getStringValue("deliverydate");
        Rows rows = dbConnect.runSqlQuery("SELECT deliverydate,defaultprice from sa_orderitems WHERE sa_orderitemsid =" + sa_orderitemsid);
        if (deliverydate.isEmpty()) {
            if (rows.isNotEmpty()) {
                deliverydate = rows.get(0).getString("deliverydate");
            } else {
                deliverydate = "null";
            }
        }
        String needdate = item.getStringValue("needdate");
        updateSQL.setValue("needdate", needdate.equals("") ? "null" : needdate);
        updateSQL.setValue("deliverydate", deliverydate.equals("") ? "null" : deliverydate);


        if (type.equals("特殊订单")) {
            if (item.containsKey("defaultprice")) {
                if (item.getBigDecimal("price").compareTo(BigDecimal.ZERO) > 0 && item.getBigDecimal("amount").compareTo(BigDecimal.ZERO) <= 0) {
                    updateSQL.setValue("price", item.getBigDecimal("price"));
                    //折前金额（元）
                    updateSQL.setValue("amount", price.multiply(qty));
                } else if (item.getBigDecimal("price").compareTo(BigDecimal.ZERO) <= 0 && item.getBigDecimal("amount").compareTo(BigDecimal.ZERO) > 0) {
                    updateSQL.setValue("price", item.getBigDecimal("amount").divide(qty, 6, BigDecimal.ROUND_HALF_UP));
                    //折前金额（元）
                    updateSQL.setValue("amount", item.getBigDecimal("amount"));
                } else {
                    updateSQL.setValue("price", price);
                    //折前金额（元）
                    updateSQL.setValue("amount", price.multiply(qty));
                }
            } else {
                updateSQL.setValue("price", price);
                //折前金额（元）
                updateSQL.setValue("amount", price.multiply(qty));
            }
        } else {
            updateSQL.setValue("price", price);
            //折前金额（元）
            updateSQL.setValue("amount", price.multiply(qty));
        }
        //单价,折后价（元）,取合同价
        updateSQL.setValue("defaultprice", defaultprice);
        //金额,折后金额（元）
        updateSQL.setValue("defaultamount", defaultprice.multiply(qty));
        updateSQL.setValue("saleprice", saleprice);

        updateSQL.setValue("width", width);

        updateSQL.setValue("length", length);

        updateSQL.setWhere("siteid", controller.siteid);
        updateSQL.setWhere("sa_orderitemsid", sa_orderitemsid);

        return updateSQL;
    }

    public BigDecimal getDefaultprice(ItemPrice itemPrice) throws YosException {
        return itemPrice.getGraderateprice();

    }

    public BigDecimal getPrice(Row orderRow, ItemPrice itemPrice, JSONObject item) throws YosException {
        String type = orderRow.getString("type");
        Long sa_promotionid = orderRow.getLong("sa_promotionid");
        BigDecimal price;
        switch (type) {
            case "促销订单":
                price = itemPrice.getPromotionPrice(sa_promotionid,item.getLong("sa_promotion_itemsid"));
                break;
            default:
                price = itemPrice.getGraderateprice();
        }

        return price;
    }

    public BigDecimal getSalePrice(Row orderRow, ItemPrice itemPrice,JSONObject item,RowsMap itemRowsMap) throws YosException {
        BigDecimal price = itemPrice.getGraderateprice();
        if (itemRowsMap.containsKey(item.getString("itemid"))) {
            if (itemRowsMap.get(item.getString("itemid")).isNotEmpty()) {
                if (itemRowsMap.get(item.getString("itemid")).get(0).getBoolean("iscustomsize") && itemRowsMap.get(item.getString("itemid")).get(0).getLong("pricingmetod") == 1) {
                    price = price.multiply(item.getBigDecimal("length").multiply(item.getBigDecimal("width")).divide(new BigDecimal("1000000"), 4, BigDecimal.ROUND_HALF_UP));
                }

            }
        }


        return price;
    }

}