cucu_YOS/src/custom/restcontroller/webmanage/sale/promotion/promotionItems.java

package restcontroller.webmanage.sale.promotion;

import beans.brand.Brand;
import beans.data.BatchDeleteErr;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.data.Row;
import common.data.Rows;
import common.data.RowsMap;
import common.data.SQLFactory;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.util.ArrayList;

public class promotionItems extends Controller {

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

    @API(title = "商品列表查询", apiversion = R.ID20230102144603.v1.class)
    @CACHEING
    public String queryItemgList() throws YosException {
        /*
         * 过滤条件设置
         */
        String where = " 1=1 ";
        String where1 = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + "and  (t1.itemname like'%" + whereObject.getString("condition") + "%'"
                        + " or t1.model like'%" + whereObject.getString("condition") + "%'" + " or t1.spec like'%"
                        + whereObject.getString("condition") + "%'" + ")";
            }

            if (whereObject.containsKey("tradefield") && !whereObject.getJSONArray("tradefield").isEmpty()) {
                String where2 =" 1=1 ";
                if ( whereObject.getJSONArray("tradefield").size() > 0) {
                    for (Object object:whereObject.getJSONArray("tradefield")) {
                        String str = String.valueOf(object);
                        where2=where2 +" or tradefield ='"+str+"' ";
                    }
                }
                where=where+("and t1.itemid in (SELECT itemid from plm_item_tradefield WHERE ("+where2+") and siteid = '"+siteid+"')");
            }

        }
        JSONArray itemclassids = content.getJSONArray("itemclassids");
        if (itemclassids.size() > 0) {
            String sql = " and t1.itemid in ( SELECT itemid from sa_itemsaleclass WHERE itemclassid IN " + itemclassids + "  and siteid='" + siteid + "')";
            sql = sql.replace("[", "(").replace("]", ")");
            where=where+sql;
        }
        Long sa_promotionid = content.getLong("sa_promotionid");
        Rows tradefieldrows =dbConnect.runSqlQuery("select tradefield from sa_promotion where siteid='"+siteid+"' and sa_promotionid="+sa_promotionid);
        JSONArray tradefield=new JSONArray();
        if(!tradefieldrows.isEmpty()){
            if (!StringUtils.isBlank(tradefieldrows.get(0).getString("tradefield"))) {
                tradefield=JSONArray.parseArray(tradefieldrows.get(0).getString("tradefield"));
            }
        }
        if(tradefield!=null && !tradefield.isEmpty()){
            for (Object object:tradefield) {
                String str = String.valueOf(object);
                where1=where1 +" or tradefield ='"+str+"' ";
            }
        }

//			String hrid = content.getString("hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "商品查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter_SQL("where1", where1);
        sqlFactory.addParameter("sa_promotionid", sa_promotionid);
        sqlFactory.addParameter("siteid", siteid);
         System.out.println(sqlFactory.getSQL());
        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        // 附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        RowsMap brandRowsMap = Brand.getBrandRowsMap(this, ids);
        Rows rowsitemclass = dbConnect.runSqlQuery(
                " select  t7.itemclassname,t6.itemid,t8.brandname from sa_itemsaleclass t6 LEFT JOIN plm_itemclass t7 ON t7.itemclassid = t6.itemclassid AND t7.siteid = t6.siteid LEFT JOIN sa_brand t8 ON t8.sa_brandid = t7.sa_brandid AND t8.siteid = t7.siteid where t6.siteid='"
                        + siteid + "'");
        RowsMap itemclassRowsMap = rowsitemclass.toRowsMap("itemid");
        for (Row row : rows) {
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("brand", brandRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            row.put("itemclass", itemclassRowsMap.get(row.getString("itemid")));
        }

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

    @API(title = "促销方案商品新增更新", apiversion = R.ID20230102142403.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230102142503.v1.class, R.ID20230102144603.v1.class,
            R.ID20230102163103.v1.class})
    public String insertormodify_promotionItems() throws YosException {
        Long sa_promotionid = content.getLong("sa_promotionid");
        Long sa_promotion_itemgroupid = content.getLong("sa_promotion_itemgroupid");
        JSONArray iteminfos = content.getJSONArray("iteminfos");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rowscount = dbConnect
                .runSqlQuery("select status,sa_promotionid from sa_promotion where sa_promotionid=" + sa_promotionid);

        Rows itempricerows = dbConnect
                .runSqlQuery(" select pricegrade,price,itemid from sa_itemprice where siteid='" + siteid + "'");
        RowsMap itempricerowsMap = itempricerows.toRowsMap("itemid");
        if (!rowscount.isEmpty()) {
            if (!rowscount.get(0).getString("status").equals("新建")) {
                return getErrReturnObject().setErrMsg("非新建状态的促销方案无法新增修改").toString();
            }
        }
        int i = 0;
        long[] sa_promotion_itemsid = createTableID("sa_promotion_items", iteminfos.size());
        for (Object obj : iteminfos) {
            JSONObject iteminfo = (JSONObject) obj;
            Rows itemrows = dbConnect.runSqlQuery("select itemid,orderminqty,orderaddqty from plm_item where siteid='"+siteid+"'");
            RowsMap itemrowsMap = itemrows.toRowsMap("itemid");
            if (iteminfo.getLong("sa_promotion_itemsid") <= 0 || dbConnect
                    .runSqlQuery("select sa_promotion_itemsid from sa_promotion_items where sa_promotion_itemsid="
                            + iteminfo.getLong("sa_promotion_itemsid"))
                    .isEmpty()) {
                SQLFactory saleFactory = new SQLFactory(this, "促销方案商品新增");
                saleFactory.addParameter("siteid", siteid);
                saleFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid[i]);
                saleFactory.addParameter("sa_promotionid", sa_promotionid);
                saleFactory.addParameter("sa_promotion_itemgroupid", sa_promotion_itemgroupid);
                saleFactory.addParameter("groupqty", iteminfo.getLong("groupqty"));
                saleFactory.addParameter("itemid", iteminfo.getLong("itemid"));
                if(itemrowsMap.containsKey(iteminfo.getString("itemid"))){
                    saleFactory.addParameter("orderaddqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderaddqty"));
                    saleFactory.addParameter("orderminqty", itemrowsMap.get(iteminfo.getString("itemid")).get(0).getBigDecimal("orderminqty"));
                }else {
                    saleFactory.addParameter("orderaddqty", 1);
                    saleFactory.addParameter("orderminqty", 1);
                }
                saleFactory.addParameter("islimit", iteminfo.getLong("islimit"));
                sqlList.add(saleFactory.getSQL());
                Rows rows = itempricerowsMap.get(iteminfo.getLong("itemid"));
                if (!rows.isEmpty()) {
                    for (Row row : rows) {
                        SQLFactory itempriceFactory = new SQLFactory(this, "促销方案商品价格新增");
                        itempriceFactory.addParameter("siteid", siteid);
                        itempriceFactory.addParameter("sa_promotion_itempriceid",
                                createTableID("sa_promotion_itemprice"));
                        itempriceFactory.addParameter("sa_promotionid", sa_promotionid);
                        itempriceFactory.addParameter("sa_promotion_itemsid", sa_promotion_itemsid[i]);
                        itempriceFactory.addParameter("itemid", row.getLong("itemid"));
                        itempriceFactory.addParameter("pricegrade", row.getBigDecimal("pricegrade"));
                        itempriceFactory.addParameter("oldprice", row.getBigDecimal("price"));
                        itempriceFactory.addParameter("price", row.getBigDecimal("price"));
                        sqlList.add(itempriceFactory.getSQL());
                    }
                }
                i++;
            } else {
                SQLFactory saleFactory = new SQLFactory(this, "促销方案商品更新");
                saleFactory.addParameter("sa_promotion_itemsid", iteminfo.getLong("sa_promotion_itemsid"));
                // saleFactory.addParameter("itemno", iteminfo.getString("itemno"));
                saleFactory.addParameter("siteid", siteid);
                saleFactory.addParameter("groupqty", iteminfo.getLong("groupqty"));
                saleFactory.addParameter("itemid", iteminfo.getLong("itemid"));
                saleFactory.addParameter("orderaddqty", iteminfo.getBigDecimal("orderaddqty"));
                saleFactory.addParameter("orderminqty", iteminfo.getBigDecimal("orderminqty"));
                saleFactory.addParameter("islimit", iteminfo.getLong("islimit"));
                saleFactory.addParameter("isonsale", iteminfo.getBooleanValue("isonsale"));
                sqlList.add(saleFactory.getSQL());
            }

        }
        dbConnect.runSqlUpdate(sqlList);
        return queryPromotionItemsList();
    }

    @API(title = "促销方案商品列表", apiversion = R.ID20230102142503.v1.class)
    @CACHEING
    public String queryPromotionItemsList() 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("t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.model like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        Long sa_promotionid = content.getLong("sa_promotionid");
        Long sa_promotion_itemgroupid = content.getLong("sa_promotion_itemgroupid");
        SQLFactory sqlFactory = new SQLFactory(this, "促销方案商品查询", pageSize, pageNumber, pageSorting);
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("sa_promotionid", sa_promotionid);
        sqlFactory.addParameter("sa_promotion_itemgroupid", sa_promotion_itemgroupid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        ArrayList ids = rows.toArrayList("itemid", new ArrayList<>());
        // 附件
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        for (Row row : rows) {
            row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
        }

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

    @API(title = "删除明细", apiversion = R.ID20230102142603.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230102142503.v1.class, R.ID20230102144603.v1.class,
            R.ID20230102163103.v1.class})
    public String deletemx() throws YosException {

        JSONArray sa_promotion_itemsids = content.getJSONArray("sa_promotion_itemsids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_promotion_itemsids.size());
        for (Object o : sa_promotion_itemsids) {
            long sa_promotion_itemsid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect.runSqlQuery(
                    "select t1.sa_promotion_itemsid,t2.status,t1.sa_promotionid from sa_promotion_items t1 left join sa_promotion t2 on t1.sa_promotionid=t2.sa_promotionid and t1.siteid=t2.siteid where t1.siteid='"
                            + siteid + "' and t1.sa_promotion_itemsid='" + sa_promotion_itemsid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    batchDeleteErr.addErr(sa_promotion_itemsid, "非新建状态的促销方案商品无法删除");
                    continue;
                }
            }
            ArrayList<String> list = new ArrayList<>();
            SQLFactory deletesql = new SQLFactory("sql:delete from sa_promotion_items where siteid='" + siteid
                    + "' and sa_promotion_itemsid=" + sa_promotion_itemsid);
            list.add(deletesql.getSQL());
            list.add("delete from sa_promotion_itemprice where siteid='" + siteid + "' and sa_promotion_itemsid="
                    + sa_promotion_itemsid);
            dbConnect.runSqlUpdate(list);
        }
        return batchDeleteErr.getReturnObject().toString();
    }
}