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

package restcontroller.webmanage.sale.promotion;

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


import beans.accountbalance.Accountbalance;
import beans.accountbalance.CashbillEntity;
import beans.itemclass.ItemClass;
import common.BaseClass;
import common.data.*;
import org.apache.commons.lang.StringUtils;

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

import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import restcontroller.R;
import restcontroller.webmanage.sale.rebateaccount.rebateaccounthead;

@API(title = "促销方案")
public class promotion extends Controller {

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

    @API(title = "促销方案新增更新", apiversion = R.ID20221230144503.v1.class, intervaltime = 200)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String insertormodify_promotion() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名
        String tableName = "sa_promotion";
        Long sa_promotionid = content.getLong("sa_promotionid");
        Long sa_accountclassid = content.getLong("sa_accountclassid");
        Long sa_brandid = content.getLong("sa_brandid");
        String promname = content.getStringValue("promname");
        String remarks = content.getStringValue("remarks");
        String type = content.getStringValue("type");
        //String tradefield = content.getStringValue("tradefield");
        JSONArray tradefield = content.getJSONArray("tradefield");
        BigDecimal orderminqty = content.getBigDecimal("orderminqty");
        BigDecimal orderaddqty = content.getBigDecimal("orderaddqty");
        BigDecimal orderminamount = content.getBigDecimal("orderminamount");
        String begdate = content.getStringValue("begdate");
        String enddate = content.getStringValue("enddate");
        Long authmethod = content.getLongValue("authmethod");
        boolean isauth = content.getBooleanValue("isauth");
        BigDecimal associationamount = content.getBigDecimalValue("associationamount");
        Long associationaccountclassid = content.getLongValue("associationaccountclassid");
        BigDecimal rebateratio = content.getBigDecimalValue("rebateratio");
        String packagetype = content.getStringValue("packagetype");
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案新增");

        if (sa_promotionid <= 0 || dbConnect
                .runSqlQuery("select sa_promotionid from sa_promotion where sa_promotionid=" + sa_promotionid)
                .isEmpty()) {
            sa_promotionid = createTableID(tableName);
            sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "新增", "促销方案新增成功").getSQL());
        } else {
            Rows rows = dbConnect
                    .runSqlQuery("SELECT status,authmethod,rebateratio from sa_promotion WHERE sa_promotionid = " + sa_promotionid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建")) {
                    sqlFactory = new SQLFactory(this, "促销方案更新");
                    sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "更新", "促销方案更新成功").getSQL());
                    if (rows.get(0).getLong("authmethod") != authmethod) {
                        if (dbConnect.runSqlQuery("select * from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid).isNotEmpty()) {
                            return getErrReturnObject().setErrMsg("已有授权经销商，无法更改授权方式").toString();
                        }
                        sqlList.add("delete from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
                    }
                    if (rebateratio.compareTo(rows.get(0).getBigDecimal("rebateratio")) != 0) {
                        sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "促销方案由返点比例:" + rows.get(0).getBigDecimal("rebateratio") + "修改为返点比例:" + rebateratio, "促销方案修改返点比例").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_promotionid", sa_promotionid);
        // 促销方案号createBillCode("promotionbillno")
        sqlFactory.addParameter("promnum", createBillCode("promotionbill"));
        sqlFactory.addParameter("promname", promname);
        sqlFactory.addParameter("sa_accountclassid", sa_accountclassid);
        sqlFactory.addParameter("sa_brandid", sa_brandid);
        sqlFactory.addParameter("type", type);
        sqlFactory.addParameter("packagetype", packagetype);
        sqlFactory.addParameter("tradefield", tradefield.toJSONString());
        sqlFactory.addParameter("orderminqty", orderminqty);
        sqlFactory.addParameter("orderaddqty", orderaddqty);
        sqlFactory.addParameter("orderminamount", orderminamount);
        sqlFactory.addParameter("sa_openpromotionid", content.getIntValue("sa_openpromotionid"));
        sqlFactory.addParameter("openamount", content.getBigDecimalValue("openamount"));
        sqlFactory.addParameter("isrelevancepromotion", content.getLongValue("isrelevancepromotion"));

        sqlFactory.addParameter("begdate", begdate);
        sqlFactory.addParameter("enddate", enddate);
        sqlFactory.addParameter("authmethod", authmethod);
        sqlFactory.addParameter("isauth", isauth);
        sqlFactory.addParameter("associationamount", associationamount);
        sqlFactory.addParameter("associationaccountclassid", associationaccountclassid);
        sqlFactory.addParameter("rebateratio", rebateratio);

        // 备注说明
        sqlFactory.addParameter("remarks", remarks);
        sqlList.add(sqlFactory.getSQL());
        dbConnect.runSqlUpdate(sqlList);

        content.put("sa_promotionid", sa_promotionid);

        return querypromotionMain();
    }

    @API(title = "促销方案详情", apiversion = R.ID20221230144703.v1.class)
    @CACHEING
    public String querypromotionMain() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案详情查询");
        sqlFactory.addParameter("sa_promotionid", sa_promotionid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        //附件
        ArrayList<Long> ids = rows.toArrayList("sa_promotionid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("sa_promotion", ids);
        row.put("attinfos", attRowsMap.getOrDefault(row.getString("sa_promotionid"), new Rows()));
        if (!StringUtils.isBlank(row.getString("tradefield"))) {
            row.put("tradefield", JSONArray.parseArray(row.getString("tradefield")));
        } else {
            row.put("tradefield", new JSONArray());
        }
        return getSucReturnObject().setData(row).toString();
    }

    @API(title = "促销方案商品上下架", apiversion = R.ID2024052710133903.v1.class)
    public String releases() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        JSONArray itemids = content.getJSONArray("itemids");
        boolean isonsale = content.getBooleanValue("isonsale");
        List<Long> list = itemids.toJavaList(Long.class);
        list.add(0l);
        Long[] stringArray = list.toArray(new Long[0]);

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "商品组上架");
        if(isonsale){
            sqlFactoryupdate = new SQLFactory(this, "促销方案商品上架_通过商品");
        }else{
            sqlFactoryupdate = new SQLFactory(this, "促销方案商品下架_通过商品");
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter_in("itemids", stringArray);
        sqlList.add(sqlFactoryupdate.getSQL());
//        //发送消息
//        for (String id : stringArray) {
//            sendMsg(Long.parseLong(id));
//        }
        String sql = "select t1.itemid,t1.orderminqty,t1.orderaddqty,t1.itemname,t2.sa_promotionid from plm_item t1 inner join sa_promotion_items t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t2.itemid in " + itemids;
        sql = sql.replace("[", "(").replace("]", ")");
        Rows itemrows = dbConnect.runSqlQuery(sql);
        for (Row row :itemrows){
            if(isonsale){
                sqlList.add(DataContrlLog.createLog(this, "sa_promotion", row.getLong("sa_promotionid"), "促销方案商品【"+row.getString("itemname")+"】由"+username+"上架", "促销方案商品上下架").getSQL());
            }else{
                sqlList.add(DataContrlLog.createLog(this, "sa_promotion", row.getLong("sa_promotionid"), "促销方案商品【"+row.getString("itemname")+"】由"+username+"下架" , "促销方案商品上下架").getSQL());
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "查询所有促销方案商品列表", apiversion = R.ID2024052710185203.v1.class)
    @CACHEING
    public String queryItemList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.spec like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.standards like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.color like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("isonsale") && !"".equals(whereObject.getString("isonsale"))) {
                where.append(" and(");
                where.append("t1.isonsale ='").append(whereObject.getString("isonsale")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("isservice") && !"".equals(whereObject.getString("isservice"))) {
                where.append(" and(");
                where.append("t1.isservice ='").append(whereObject.getString("isservice")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and(");
                where.append("t1.status ='").append(whereObject.getString("status")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("isonsalebutnotingroup") && !"".equals(whereObject.getString("isonsalebutnotingroup"))) {
                if (whereObject.getBooleanValue("isonsalebutnotingroup")) {
                    where.append(" and(");
                    where.append("t1.isonsale =1 and t1.itemid not in (select t1.itemid from sa_itemgroupmx t1 inner join sa_itemgroup t2 on t1.sa_itemgroupid=t2.sa_itemgroupid and t1.siteid=t2.siteid where t2.isonsale=1 )");
                    where.append(")");
                }
            }
            if (whereObject.containsKey("hasprice") && !"".equals(whereObject.getString("hasprice"))) {
                if (whereObject.getBooleanValue("hasprice")) {
                    where.append(" and(");
                    where.append(" exists(select * from sa_itemprice where sa_itemprice.siteid=t1.siteid and sa_itemprice.itemid=t1.itemid and price>0)");
                    where.append(")");
                }

            }

            if (whereObject.containsKey("itemclassid") && !"".equals(whereObject.getString("itemclassid"))) {
                ArrayList<Long> list = ItemClass.getSubItemClassIds(this, whereObject.getLong("itemclassid"));
                list.add(whereObject.getLong("itemclassid"));
                String sql = " and t1.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + list + "  and siteid='" + siteid + "')";
                sql = sql.replace("[", "(").replace("]", ")");
                where.append(sql);
            }

            if (whereObject.containsKey("tradefield") && !whereObject.getString("tradefield").isEmpty()) {
                where.append(" and exists(");
                where.append(" select 1 from plm_item_tradefield t3 where t3.siteid=t1.siteid and t3.itemid=t1.itemid and t3.tradefield='").append(whereObject.getString("tradefield")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("itemclass") && !whereObject.getString("itemclass").isEmpty()) {
                String itemclass = whereObject.getString("itemclass");

                where.append(" and t1.itemid in (");
                where.append(" SELECT t1.itemid FROM sa_itemsaleclass t1 INNER JOIN plm_itemclass t2 ON t2.itemclassid=t1.itemclassid AND t2.siteid=t1.siteid WHERE t2.itemclassname LIKE '%" + itemclass + "%' ");
                where.append(")");
            }

        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item");
        querySQL.addJoinTable(BaseClass.JOINTYPE.inner, "(select DISTINCT t1.itemid,t1.siteid from sa_promotion_items t1 inner join sa_promotion t2 on t1.sa_promotionid=t2.sa_promotionid and t1.siteid=t2.siteid)", "t2", "t1.itemid=t2.itemid and t1.siteid=t2.siteid ");
        querySQL.setWhere(where);
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setTableAlias("t1");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "一键复制促销方案", apiversion = R.ID20230713101303.v1.class)
    @CACHEING_CLEAN(apiClass = {promotion.class, promotionSalearea.class, promotionAuth.class, promotionItems.class, promotionItemPrice.class})
    public String copyPromotion() throws YosException {

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

        ArrayList<String> sqlList = new ArrayList<>();

        Rows rows = dbConnect.runSqlQuery("SELECT * from sa_promotion  WHERE sa_promotionid = " + sa_promotionid + " and siteid = '" + siteid + "'");

        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("促销方案不存在").toString();
        }
        Row row = rows.get(0);

        Long sa_promotion_copy = createTableID("sa_promotion");
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案新增");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_promotionid", sa_promotion_copy);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        //促销方案名称
        sqlFactory.addParameter("promname", row.getString("promname") + "复制");
        sqlFactory.addParameter("promnum", createBillCode("promotionbill"));
        sqlFactory.addParameter("sa_accountclassid", row.getLong("sa_accountclassid"));
        sqlFactory.addParameter("sa_brandid", row.getLong("sa_brandid"));
        sqlFactory.addParameter("type", row.getString("type"));
        sqlFactory.addParameter("tradefield", row.getString("tradefield"));
        sqlFactory.addParameter("orderminqty", row.getBigDecimal("orderminqty"));
        sqlFactory.addParameter("orderaddqty", row.getBigDecimal("orderaddqty"));
        sqlFactory.addParameter("orderminamount", row.getBigDecimal("orderminamount"));
        sqlFactory.addParameter("sa_openpromotionid", row.getLong("sa_openpromotionid"));
        sqlFactory.addParameter("openamount", row.getBigDecimal("openamount"));
        sqlFactory.addParameter("isrelevancepromotion", row.getString("isrelevancepromotion"));
        sqlFactory.addParameter("associationamount", row.getString("associationamount"));
        sqlFactory.addParameter("associationaccountclassid", row.getString("associationaccountclassid"));
        sqlFactory.addParameter("begdate", row.getString("begdate"));
        sqlFactory.addParameter("enddate", row.getString("enddate"));
        sqlFactory.addParameter("authmethod", row.getString("authmethod"));
        sqlFactory.addParameter("associationamount", 0);
        sqlFactory.addParameter("associationaccountclassid", 0);
        sqlFactory.addParameter("rebateratio", 0);
        sqlFactory.addParameter("packagetype", row.getString("packagetype"));
        // 备注说明
        sqlFactory.addParameter("remarks", row.getString("remarks"));
        sqlList.add(sqlFactory.getSQL());

        //复制商品
        Rows promotionitemsRows = dbConnect.runSqlQuery("SELECT * from sa_promotion_items WHERE sa_promotionid = " + sa_promotionid + " and siteid = '" + siteid + "'");
        //复制商品
        Rows promotionitemspriceRows = dbConnect.runSqlQuery("SELECT * from sa_promotion_itemprice WHERE sa_promotionid = " + sa_promotionid + " and siteid = '" + siteid + "'");
        RowsMap promotionitemspriceRowsMap = promotionitemspriceRows.toRowsMap("sa_promotion_itemsid");

        for (Row promotionitemsRow : promotionitemsRows) {
            sqlFactory = new SQLFactory(this, "促销方案商品新增");
            sqlFactory.addParameter("siteid", siteid);
            long sa_promotion_itemsidcopy = createTableID("sa_promotion_items");
            sqlFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsidcopy);
            sqlFactory.addParameter("sa_promotionid", sa_promotion_copy);
            sqlFactory.addParameter("sa_promotion_itemgroupid", promotionitemsRow.getLong("sa_promotion_itemgroupid"));
            sqlFactory.addParameter("groupqty", promotionitemsRow.getBigDecimal("groupqty"));
            sqlFactory.addParameter("itemid", promotionitemsRow.getLong("itemid"));
            sqlFactory.addParameter("orderaddqty", promotionitemsRow.getBigDecimal("orderaddqty"));
            sqlFactory.addParameter("orderminqty", promotionitemsRow.getBigDecimal("orderminqty"));
            sqlFactory.addParameter("islimit", promotionitemsRow.getLong("islimit"));
            sqlFactory.addParameter("signaturecode", promotionitemsRow.getString("signaturecode"));
            sqlFactory.addParameter("packagetypemx", promotionitemsRow.getString("packagetypemx"));
            sqlFactory.addParameter("itemno", promotionitemsRow.getString("itemno"));
            sqlList.add(sqlFactory.getSQL());

            if (promotionitemspriceRowsMap.containsKey(promotionitemsRow.getString("sa_promotion_itemsid")) && promotionitemspriceRowsMap.get(promotionitemsRow.getString("sa_promotion_itemsid")).isNotEmpty()) {
                for (Row rowdetail : promotionitemspriceRowsMap.get(promotionitemsRow.getString("sa_promotion_itemsid"))) {
                    SQLFactory itempriceFactory = new SQLFactory(this, "促销方案商品价格新增");
                    itempriceFactory.addParameter("siteid", siteid);
                    itempriceFactory.addParameter("sa_promotion_itempriceid",
                            createTableID("sa_promotion_itemprice"));
                    itempriceFactory.addParameter("sa_promotionid", sa_promotion_copy);
                    itempriceFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsidcopy);
                    itempriceFactory.addParameter("itemid", rowdetail.getLong("itemid"));
                    itempriceFactory.addParameter("pricegrade", rowdetail.getBigDecimal("pricegrade"));
                    itempriceFactory.addParameter("oldprice", rowdetail.getBigDecimal("oldprice"));
                    itempriceFactory.addParameter("price", rowdetail.getBigDecimal("price"));
                    sqlList.add(itempriceFactory.getSQL());
                }
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        content.put("sa_promotionid", sa_promotion_copy);

        return querypromotionMain();
    }

    @API(title = "查询促销方案列表", apiversion = R.ID20221230144803.v1.class)
    @CACHEING
    public String querypromotionList() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        StringBuffer wherebegindate = new StringBuffer(" 1=1 ");
        StringBuffer whereenddate = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.promnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.promname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.remarks like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                wherebegindate.append(" and DATE_FORMAT(t1.begdate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
                whereenddate.append(" and DATE_FORMAT(t1.enddate, '%Y-%m-%d') >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                wherebegindate.append(" and DATE_FORMAT(t1.begdate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
                whereenddate.append(" and DATE_FORMAT(t1.enddate, '%Y-%m-%d') <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
        where.append("and ((").append(wherebegindate).append(") or (").append(whereenddate).append("))");
//        SQLFactory sqlFactory = new SQLFactory(this, "促销方案列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = queryPomotionList(where.toString());
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

        //附件
        ArrayList<Long> ids = rows.toArrayList("sa_promotionid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("sa_promotion", ids);
        for (Row row : rows) {
            if (!StringUtils.isBlank(row.getString("tradefield"))) {
                row.put("tradefield", JSONArray.parseArray(row.getString("tradefield")));
            } else {
                row.put("tradefield", new JSONArray());
            }
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("sa_promotionid"), new Rows()));
        }

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

    public QuerySQL queryPomotionList(String where) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_promotion");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_brand", "t2", "t1.sa_brandid = t2.sa_brandid AND t1.siteid = t2.siteid",
                "brandname");
        querySQL.addJoinTable(JOINTYPE.left, "sa_accountclass", "t3", "t1.sa_accountclassid = t3.sa_accountclassid AND t1.siteid = t3.siteid",
                "accountno", "accountname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where);
        return querySQL;
    }

    @API(title = "查询促销方案销售排行(经销商)", apiversion = R.ID20231007101703.v1.class)
    @CACHEING
    public String queryAgentSalesRanking() throws YosException {
        long sa_promotionid = content.getLongValue("sa_promotionid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.abbreviation like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案销售排行查询(经销商)", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_promotionid", sa_promotionid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


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


    @API(title = "查询促销方案销售排行(商品)", apiversion = R.ID20231007101803.v1.class)
    @CACHEING
    public String queryItemSalesRanking() throws YosException {
        long sa_promotionid = content.getLongValue("sa_promotionid");
        StringBuffer where = new StringBuffer(" 1=1 ");
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案销售排行查询(商品)", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_promotionid", sa_promotionid);
        sqlFactory.addParameter_SQL("where", where);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);


//        //附件
//        ArrayList<Long> ids = rows.toArrayList("sa_promotionid", new ArrayList<>());
//        RowsMap attRowsMap = getAttachmentUrl("sa_promotion", ids);
//        for (Row row : rows) {
//            if (!StringUtils.isBlank(row.getString("tradefield"))) {
//                row.put("tradefield", JSONArray.parseArray(row.getString("tradefield")));
//            } else {
//                row.put("tradefield", new JSONArray());
//            }
//            row.put("attinfos", attRowsMap.getOrDefault(row.getString("sa_promotionid"), new Rows()));
//        }

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

    @API(title = "删除", apiversion = R.ID20221230144903.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String delete() throws YosException {

        ArrayList<String> sqlList = new ArrayList<>();
        JSONArray sa_promotionids = content.getJSONArray("sa_promotionids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_promotionids.size());
        for (Object o : sa_promotionids) {
            long sa_promotionid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery("select sa_promotionid,status from sa_promotion where siteid='"
                    + siteid + "' and sa_promotionid='" + sa_promotionid + "'");

            Rows Rowsdetail = dbConnect.runSqlQuery("select sa_promotion_authid from sa_promotion_auth where siteid='"
                    + siteid + "' and sa_promotionid='" + sa_promotionid + "' and saledamount>0");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_promotionid, "非新建状态的促销方案无法删除");
                    continue;
                }
            }
            if (Rowsdetail.isNotEmpty()) {
                batchDeleteErr.addErr(sa_promotionid, "存在已购金额的授权经销商禁止删除促销活动");
                continue;
            }
            sqlList.add("delete from sa_promotion where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
            sqlList.add(
                    "delete from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
            sqlList.add("delete from sa_promotion_itemgroup where siteid='" + siteid + "' and sa_promotionid="
                    + sa_promotionid);
            sqlList.add("delete from sa_promotion_itemprice where siteid='" + siteid + "' and sa_promotionid="
                    + sa_promotionid);
            sqlList.add("delete from sa_promotion_items where siteid='" + siteid + "' and sa_promotionid="
                    + sa_promotionid);
            dbConnect.runSqlUpdate(sqlList);
        }
        return batchDeleteErr.getReturnObject().toString();

    }

    @API(title = "审核", apiversion = R.ID20221230145003.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String check() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");

        Rows rows = dbConnect
                .runSqlQuery("select sa_promotionid,status,promnum,promname,type,associationaccountclassid,associationamount from sa_promotion where sa_promotionid ='"
                        + sa_promotionid + "' and  siteid='" + siteid + "'");
        Rows rowsdetails = dbConnect
                .runSqlQuery("select t2.itemno,t2.itemname,ifnull(t1.orderminqty,0) orderminqty, ifnull(t1.orderaddqty,0) orderaddqty from sa_promotion_items t1 left join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.sa_promotionid ='"
                        + sa_promotionid + "' and  t1.siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该促销方案不存在").toString();
        } else {
            if (!rows.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("promnum") + "】的促销方案为非新建状态，无法审核")
                        .toString();
            }
            if (rows.get(0).getString("type").equals("返利促销")) {
                if (rows.get(0).getLong("associationaccountclassid") == 0 || rows.get(0).getBigDecimal("associationamount").compareTo(BigDecimal.ZERO) == 0) {
                    return getErrReturnObject().setErrMsg("返利促销必须选择关联支付账户和关联支付金额").toString();
                }
                Rows islimitRows = dbConnect.runSqlQuery("select ifnull(islimit,0) islimit,count(1) from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid + " group by ifnull(islimit,0)");
                if (islimitRows.isEmpty()) {
                    return getErrReturnObject().setErrMsg("返利促销必须授权经销商").toString();
                } else if (islimitRows.size() > 1 || (islimitRows.size() == 1 && !islimitRows.get(0).getBoolean("islimit"))) {
                    return getErrReturnObject().setErrMsg("返利促销授权的经销商必须都为限购").toString();
                }
            }
            if (rows.get(0).getString("type").equals("套餐活动")) {
                if (StringUtils.isBlank(rows.get(0).getString("packagetype"))) {
                    return getErrReturnObject().setErrMsg("套餐活动的方案必须选择套餐类型").toString();
                }
                Rows promotionItemsRows = dbConnect.runSqlQuery("select ifnull(t1.packagetypemx,'kong') packagetypemx,count(1) count,min(t2.itemno) itemno from sa_promotion_items t1 inner join plm_item t2 on t1.itemid=t2.itemid and t1.siteid=t2.siteid where t1.siteid='"+siteid+"' and t1.sa_promotionid="+sa_promotionid+" group by ifnull(t1.packagetypemx,'kong')");
                for(Row row : promotionItemsRows){
                    if(row.getString("packagetypemx").equals("kong")){
                        return getErrReturnObject().setErrMsg("品号："+row.getString("itemno")+"未设置套餐分类，无法审核").toString();
                    }
                }
                for(Row row : promotionItemsRows){
                    if(row.getLong("count")>100){
                        return getErrReturnObject().setErrMsg("套餐名："+row.getString("packagetypemx")+" 商品添加数量已超上限（同一套餐最多只能添加100个商品）").toString();
                    }
                }

            }
            for (Row row:rowsdetails) {
                if(row.getBigDecimal("orderminqty").compareTo(BigDecimal.ZERO)==0 || row.getBigDecimal("orderaddqty").compareTo(BigDecimal.ZERO)==0){
                    return getErrReturnObject().setErrMsg("该促销方案【"+row.getString("itemno")+"】商品起订量或增量为0，无法审核").toString();
                }
            }




        }

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "促销方案审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_promotionid", sa_promotionid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "审核", "促销方案审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();

    }

    @API(title = "反审核", apiversion = R.ID20221230145103.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String uncheck() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");

        Rows rows = dbConnect
                .runSqlQuery("select sa_promotionid,status,promnum,promname from sa_promotion where sa_promotionid ='"
                        + sa_promotionid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该促销方案不存在").toString();
        } else {
            if (!rows.get(0).getString("status").equals("审核")) {
                return getErrReturnObject().setErrMsg("单号为：【" + rows.get(0).getString("promnum") + "】的促销方案为非审核状态，无法反审核")
                        .toString();
            }
//            if(!dbConnect.runSqlQuery("select * from sa_order where sa_promotionid="+sa_promotionid+" and siteid='"+siteid+"'").isEmpty()){
//                return getErrReturnObject().setErrMsg("存在已使用方案的订单，无法反审核")
//                        .toString();
//            }
        }

        ArrayList<String> sqlList = new ArrayList<>();

        SQLFactory sqlFactoryupdate = new SQLFactory(this, "促销方案反审核");
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_promotionid", sa_promotionid);
        sqlFactoryupdate.addParameter("checkby", username);
        sqlList.add(sqlFactoryupdate.getSQL());
        sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "反审核", "促销方案反审核成功").getSQL());
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "发布反发布", apiversion = R.ID20221230145203.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String recheck() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");
        boolean isrecheck = content.getBooleanValue("isrecheck");
        Rows rows = dbConnect
                .runSqlQuery("select sa_promotionid,status,promnum from sa_promotion where sa_promotionid ='"
                        + sa_promotionid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (isrecheck) {
                if (!row.getString("status").equals("审核")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("promnum") + "】的促销方案为非审核状态，无法发布")
                            .toString();
                }
            } else {
                if (!row.getString("status").equals("发布")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("promnum") + "】的促销方案为非发布状态，无法反发布")
                            .toString();
                }
                Rows orderRows = dbConnect.runSqlQuery("select * from sa_order  where sa_promotionid="+sa_promotionid+" and siteid='"+siteid+"'");
                if(orderRows.isNotEmpty()){
                    return getErrReturnObject().setErrMsg("该方案已存在订单，无法取消发布")
                            .toString();
                }
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactoryupdate;
        if (isrecheck) {
            sqlFactoryupdate = new SQLFactory(this, "促销方案发布");
            sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "发布", "促销方案发布成功").getSQL());
        } else {
            sqlFactoryupdate = new SQLFactory(this, "促销方案反发布");
            sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "反发布", "促销方案反发布成功").getSQL());
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_promotionid", sa_promotionid);
        sqlFactoryupdate.addParameter("sendby", username);
        sqlList.add(sqlFactoryupdate.getSQL());

        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "关闭反关闭", apiversion = R.ID20230102095403.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20221230144803.v1.class, R.ID20220103140003.v1.class})
    public String close() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");
        boolean isclose = content.getBooleanValue("isclose");
        Rows rows = dbConnect
                .runSqlQuery("select sa_promotionid,status,promnum from sa_promotion where sa_promotionid ='"
                        + sa_promotionid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (isclose) {
                if (!row.getString("status").equals("发布")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("promnum") + "】的促销方案为非发布状态，无法关闭")
                            .toString();
                }
            } else {
                if (!row.getString("status").equals("关闭")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("promnum") + "】的促销方案为非关闭状态，无法反关闭")
                            .toString();
                }
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactoryupdate;
        if (isclose) {
            sqlFactoryupdate = new SQLFactory(this, "促销方案关闭");
            sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "关闭", "促销方案关闭成功").getSQL());
        } else {
            sqlFactoryupdate = new SQLFactory(this, "促销方案反关闭");
            sqlList.add(DataContrlLog.createLog(this, "sa_promotion", sa_promotionid, "反关闭", "促销方案反关闭成功").getSQL());
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_promotionid", sa_promotionid);
        sqlFactoryupdate.addParameter("closeby", username);
        sqlList.add(sqlFactoryupdate.getSQL());

        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "切换授权方式", apiversion = R.ID20230509100504.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20221230144703.v1.class, R.ID20220103140003.v1.class})
    public String updateAuthMethod() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");
        Rows rows = dbConnect.runSqlQuery("select sa_promotionid,status,authmethod from sa_promotion where sa_promotionid ='" + sa_promotionid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("无效促销方案").toString();
        }
        if (!rows.get(0).getString("status").equals("新建")) {
            return getErrReturnObject().setErrMsg("非新建状态不能修改授权方式").toString();
        }
        if (dbConnect.runSqlQuery("select * from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid).isNotEmpty()) {
            return getErrReturnObject().setErrMsg("已有授权经销商，无法更改授权方式").toString();
        }
        ArrayList<String> sqlList = new ArrayList<>();
        sqlList.add("delete from sa_promotion_auth where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
        if (rows.get(0).getLong("authmethod") == 0) {
            sqlList.add("update sa_promotion set authmethod=1 where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
        } else {
            sqlList.add("update sa_promotion set authmethod=0 where siteid='" + siteid + "' and sa_promotionid=" + sa_promotionid);
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "结算", apiversion = R.ID20230826091303.v1.class)
    @CACHEING_CLEAN(apiClass = {promotionAuth.class, promotion.class})
    public String settlement() throws YosException {
        Long sa_promotion_authid = content.getLong("sa_promotion_authid");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows promotionauthRows = dbConnect.runSqlQuery(
                "select t1.*,t2.type,t2.rebateratio,t2.promname,t2.promnum from sa_promotion_auth t1 inner join sa_promotion t2 on t1.siteid=t2.siteid and t1.sa_promotionid=t2.sa_promotionid where t1.sa_promotion_authid ='"
                        + sa_promotion_authid + "' and  t1.siteid='" + siteid + "'");
        if (promotionauthRows.isNotEmpty()) {
            if (!promotionauthRows.get(0).getString("type").equals("返利促销")) {
                return getErrReturnObject().setErrMsg("返利促销方案才能返利结算").toString();
            }
            if (promotionauthRows.get(0).getBoolean("settlestatus")) {
                return getErrReturnObject().setErrMsg("该经销商结算已经结算成功，无法再次结算").toString();
            }
        } else {
            return getErrReturnObject().setErrMsg("该记录不存在").toString();
        }
        long sa_promotionid = promotionauthRows.get(0).getLong("sa_promotionid");
        long sys_enterpriseid = promotionauthRows.get(0).getLong("sys_enterpriseid");
        BigDecimal saledamount = promotionauthRows.get(0).getBigDecimal("saledamount");
        BigDecimal rebateratio = promotionauthRows.get(0).getBigDecimal("rebateratio");
        System.out.println(saledamount);
        System.out.println(rebateratio);
        String agentnum = "";
        Rows enterpriserows = dbConnect.runSqlQuery("select * from sa_agents where sys_enterpriseid ='" + sys_enterpriseid + "' and siteid='" + siteid + "'");
        if (enterpriserows.isNotEmpty()) {
            agentnum = enterpriserows.get(0).getString("agentnum");
        }
        //查询经销商未关闭的促销订单
        Rows rows = dbConnect.runSqlQuery("select * from  sa_order where type ='促销订单' and deleted!=1 and  sa_promotionid=" + sa_promotionid + " and sys_enterpriseid ='" + sys_enterpriseid + "'  and status!='新建' and  status not in('关闭','手工关闭') and siteid='" + siteid + "'");
        Boolean sendRemind = false;
        Long sa_cashbillid = 0L;
        if (rows.isNotEmpty()) {
            return getErrReturnObject().setErrMsg("存在未关闭的返利促销订单，无法结算").toString();
        } else {
            Rows accountclassrows = dbConnect.runSqlQuery("select sa_accountclassid from  sa_accountclass where  accountname ='返利金账户' and siteid='" + siteid + "'");
            if (accountclassrows.isNotEmpty()) {
                long sa_accountclassid = accountclassrows.get(0).getLong("sa_accountclassid");
                CashbillEntity entity = new CashbillEntity();
                entity.setAmount(saledamount.multiply(rebateratio));
                entity.setOwnerid(sa_promotion_authid);
                entity.setOwnertable("sa_promotion_auth");
                entity.setSource("返利");
                entity.setSourcenote("经销商【" + agentnum + "】，【" + promotionauthRows.get(0).getString("promname") + "】方案返利促销");
                entity.setRemarks("由活动单号" + promotionauthRows.get(0).getString("promnum") + "结算时生成");
                JSONObject cashbillIncome = Accountbalance.createCashbillIncome(this, sys_enterpriseid, sa_accountclassid, entity, true, true);
                sqlList.addAll(cashbillIncome.getJSONArray("sqlList").toJavaList(String.class));
                sa_cashbillid = cashbillIncome.getLong("sa_cashbillid");
                sendRemind = true;
                sqlList.add("update sa_promotion_auth set settlestatus=1 where sa_promotion_authid=" + sa_promotion_authid + " and siteid='" + siteid + "'");
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        if (sendRemind) {
            Accountbalance.remindSend(this, sys_enterpriseid, sa_cashbillid);
        }
        return getSucReturnObject().toString();

    }
}