CUCU2/src/custom/restcontroller/webmanage/saletool/fad/Fad.java

package restcontroller.webmanage.saletool.fad;

import beans.attachment.Attachment;
import beans.brand.Brand;
import beans.itemclass.ItemClass;
import beans.time.Time;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.util.ArrayList;
import java.util.LinkedHashSet;

/**
 * 单品管理
 */
public class Fad extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Fad(JSONObject content) throws YosException {
        super(content);
    }

    String sa_fad = "sa_fad";

    @API(title = "单品新增或编辑", apiversion = R.ID20240418140902.v1.class)
    public String insertOrUpdate() throws YosException {
        Long sa_fadid = content.getLongValue("sa_fadid");
        Long itemid = content.getLongValue("itemid");
        int isonsale = content.getIntValue("isonsale");
        JSONArray sa_fadclassids = new JSONArray();
        if (content.containsKey("sa_fadclassids")) {
            sa_fadclassids = content.getJSONArray("sa_fadclassids");
            for (Object obj : sa_fadclassids) {
                if (!(obj instanceof JSONArray)) {
                    return getErrReturnObject().setErrMsg("分类格式不正确").toString();
                }
            }
        }

        if (sa_fadid <= 0) {
            sa_fadid = createTableID(sa_fad);
            InsertSQL sqlFactory = SQLFactory.createInsertSQL(this, sa_fad);
            sqlFactory.setSiteid(siteid);
            sqlFactory.setUniqueid(sa_fadid);
            sqlFactory.setValue("name", content.getStringValue("name"));
            sqlFactory.setValue("model", content.getStringValue("model"));
            sqlFactory.setValue("spec", content.getStringValue("spec"));
            sqlFactory.setValue("candownload", content.getBooleanValue("candownload"));
            sqlFactory.setValue("outurl", content.getStringValue("outurl", true));
            sqlFactory.setValue("isnew", content.getBooleanValue("isnew"));
            sqlFactory.setValue("sequence", content.getLongValue("sequence"));
            sqlFactory.setValue("content", content.getStringValue("contentstr", true));
            sqlFactory.setValue("price", content.getBigDecimalValue("price"));
            sqlFactory.setValue("offsaledate", content.getStringValue("offsaledate", true, "null"));
            sqlFactory.setValue("sa_fadclassids", sa_fadclassids);
            sqlFactory.setValue("classid", 1);
            sqlFactory.insert();
            content.put("sa_fadid", sa_fadid);
        } else {
            UpdateSQL sqlFactory = SQLFactory.createUpdateSQL(this, sa_fad);
            sqlFactory.setUniqueid(sa_fadid);
            sqlFactory.setSiteid(siteid);
            sqlFactory.setValue("name", content.getStringValue("name"));
            sqlFactory.setValue("model", content.getStringValue("model"));
            sqlFactory.setValue("spec", content.getStringValue("spec"));
            sqlFactory.setValue("candownload", content.getBooleanValue("candownload"));
            sqlFactory.setValue("outurl", content.getStringValue("outurl", true));
            sqlFactory.setValue("isnew", content.getBooleanValue("isnew"));
            sqlFactory.setValue("sequence", content.getLongValue("sequence"));
            sqlFactory.setValue("content", content.getStringValue("contentstr", true));
            sqlFactory.setValue("price", content.getBigDecimalValue("price"));
            sqlFactory.setValue("offsaledate", content.getStringValue("offsaledate", true, "null"));
            sqlFactory.setValue("sa_fadclassids", sa_fadclassids);
            sqlFactory.update();
        }

        //上下架
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sa_fad);
        updateSQL.setSiteid(siteid);
        updateSQL.setWhere("sa_fadid", sa_fadid);
        updateSQL.setValue("isonsale", isonsale);
        updateSQL.setValue("onsaledate", isonsale == 1 ? Time.getDateTime_Str() : "null");
        updateSQL.setValue("onsaleby", isonsale == 1 ? username : "null");
        updateSQL.update();

        //关联商品
        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_fad_link");
        insertSQL.setSiteid(siteid);
        insertSQL.setUniqueid(createTableID("sa_fad_link"));
        insertSQL.setValue("itemid", itemid);
        insertSQL.setValue("sa_fadid", sa_fadid);
        insertSQL.setWhere("not exists(select 1 from  sa_fad_link where itemid=" + itemid + " and sa_fadid=" + sa_fadid + " )");
        insertSQL.insert();

        //插入图片
        Rows rows = Attachment.get(this, "plm_item", itemid, "cover");
        if (rows.isEmpty()) {
            rows = Attachment.get(this, "plm_item", itemid, "default");
        }
        if (rows.isNotEmpty()) {
            insertSQL = SQLFactory.createInsertSQL(this, "sys_attachment_links");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(createTableID("sys_attachment_links"));
            insertSQL.setValue("usetype", sa_fad);
            insertSQL.setValue("attachmentid", rows.get(0).getLong("attachmentid"));
            insertSQL.setValue("sequence", 1);
            insertSQL.setValue("ownertable", sa_fad);
            insertSQL.setValue("ownerid", sa_fadid);
            insertSQL.setWhere("not exists(select 1 from  sys_attachment_links where attachmentid=" + rows.get(0).getLong("attachmentid") + " and ownertable='sa_fad' and siteid='" + siteid + "' and ownerid=" + sa_fadid + " )");
            insertSQL.insert();
        }

        return detail();
    }

    @API(title = "单品详情", apiversion = R.ID20240418141002.v1.class,accesstoken = false)
    public String detail() throws YosException {
        Long sa_fadid = content.getLongValue("sa_fadid");
        String ownertable = sa_fad;

        Rows fadrows = dbConnect.runSqlQuery("select * from sa_fad where sa_fadid=" + sa_fadid);
        if (fadrows.isNotEmpty()) {
            siteid = fadrows.get(0).getString("siteid");
        }

        dbConnect.runSqlUpdate("UPDATE sa_fad SET readcount=readcount+1 WHERE sa_fadid="+sa_fadid);

        QuerySQL querySQ = SQLFactory.createQuerySQL(this, sa_fad)
                .setTableAlias("t1");
        querySQ.addJoinTable(JOINTYPE.left, "sys_datacollect", "t2", "t2.siteid=t1.siteid and t2.ownertable='sa_fad' and t2.type=1 and t2.ownerid=t1.sa_fadid and t2.userid='" + userid + "'");
        querySQ.addQueryFields("iscollect", "CASE WHEN t2.sys_datacollectid>0 THEN 1 ELSE 0 END");
        querySQ.setSiteid(siteid);
        querySQ.setWhere("sa_fadid", sa_fadid);
        Rows rows = querySQ.query();

        Row detailRow = rows.isNotEmpty() ? rows.get(0) : new Row();


        Rows attRows = getAttachmentUrl(sa_fad, sa_fadid);
        attRows.sortby("sequence","linksid");
        detailRow.put("attinfos", attRows);
        detailRow.put("appleturl", FadHelper.getAppletUrl(this, "FadUrl", "单品小程序链接") + sa_fadid);


        QuerySQL attachmentQuery = SQLFactory.createQuerySQL(this, "sys_attachment_links").setTableAlias("t1");
        attachmentQuery.setSiteid(detailRow.getString("siteid"));
        attachmentQuery.setWhere("ownertable", ownertable);
        attachmentQuery.setWhere("ownerid", sa_fadid);
        attachmentQuery.setWhere("usetype", ownertable);
        attachmentQuery.setOrderBy("t1.sequence,t1.linksid");
        Rows attachmentRows = attachmentQuery.query();


        ArrayList<Long> ids = attachmentRows.toArrayList("attachmentid", new ArrayList<>());

        RowsMap attRowsMap = Attachment.get(this, ids).toRowsMap("attachmentid");

        for (Row row : attachmentRows) {
            Rows attPicRows = new Rows();
            Rows tempAttRows = attRowsMap.getOrDefault(row.getString("attachmentid"), new Rows());
            for (Row tempAttRow : tempAttRows) {
                if (tempAttRow.getString("usetype").equals(ownertable)) {
                    attPicRows.add(tempAttRow);
                }
            }
            row.put("attinfos", attPicRows);
        }

        detailRow.put("attinfos_pic", attachmentRows);
        detailRow.putIfAbsent("offsaledate", "");
        detailRow.putIfAbsent("onsaledate", "");
        detailRow.put("classnames", getClassnames(detailRow.getJSONArray("sa_fadclassids"), false));


        detailRow.put("mainclassnames", getClassnames(detailRow.getJSONArray("sa_fadclassids"), true));


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

    @API(title = "单品删除", apiversion = R.ID20240418141102.v1.class)
    public String delete() throws YosException {
        JSONArray sa_fadids = content.getJSONArray("sa_fadids");
        if (sa_fadids.size() == 0) {
            return getErrReturnObject().setErrMsg("请选择要删除的数据").toString();
        }
        DeleteSQL sqlFactory = SQLFactory.createDeleteSQL(this, sa_fad);
        sqlFactory.setSiteid(siteid);
        sqlFactory.setWhere("sa_fadid", sa_fadids.toArray());
        sqlFactory.delete();

        return getSucReturnObject().toString();
    }

    @API(title = "单品上下架", apiversion = R.ID20240418141202.v1.class)
    public String isonsale() throws YosException {
        JSONArray sa_fadids = content.getJSONArray("sa_fadids");
        int isonsale = content.getIntValue("isonsale");

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, sa_fad);
        updateSQL.setSiteid(siteid);
        updateSQL.setWhere("sa_fadid", sa_fadids);
        updateSQL.setValue("isonsale", isonsale);
        updateSQL.setValue("onsaledate", isonsale == 0 ? "null" : Time.getDateTime_Str());
        updateSQL.setValue("onsaleby", isonsale == 0 ? "null" : username);
        updateSQL.update();

        return getSucReturnObject().toString();
    }

    @API(title = "单品列表", apiversion = R.ID20240418141302.v1.class)
    public String list() 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.name 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(")");
            }

            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("begindate_create") && !"".equals(whereObject.getString("begindate_create"))) {
                where.append(" and (");
                where.append("t1.createdate >='").append(whereObject.getString("begindate_create")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate_create") && !"".equals(whereObject.getString("enddate_create"))) {
                where.append(" and (");
                where.append("t1.createdate <='").append(whereObject.getString("enddate_create")).append(" 23:59:59' ");
                where.append(")");
            }
            if (whereObject.containsKey("begindate_onsale") && !"".equals(whereObject.getString("begindate_onsale"))) {
                where.append(" and (");
                where.append("t1.onsaledate >='").append(whereObject.getString("begindate_onsale")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("enddate_onsale") && !"".equals(whereObject.getString("enddate_onsale"))) {
                where.append(" and (");
                where.append("t1.onsaledate <='").append(whereObject.getString("enddate_onsale")).append(" 23:59:59' ");
                where.append(")");
            }

            if (whereObject.containsKey("sa_fadclassids") && !"".equals(whereObject.getString("sa_fadclassids"))) {
                JSONArray sa_fadclassids = whereObject.getJSONArray("sa_fadclassids");
                if (sa_fadclassids.size() > 0) {
                    where.append(" and (1=2");
                    for (Object obj : sa_fadclassids) {
                        JSONArray array = (JSONArray) obj;
                        for (Object obj2 : array) {
                            where.append(" or (");
                            where.append("JSON_CONTAINS(t1.sa_fadclassids,'" + obj2 + "')");
                            where.append(")");
                        }
                    }
                    where.append(")");
                }
            }

            if (whereObject.containsKey("iscollect") && !"".equals(whereObject.getString("iscollect"))) {
                if (whereObject.getString("iscollect").equals("1")) {
                    where.append(" and (");
                    where.append(" t1.sa_fadid in (SELECT ownerid from sys_datacollect WHERE ownertable='sa_fad' and type=1 and siteid='" + siteid + "' and userid=" + userid + ")");
                    where.append(")");
                }


            }


        }


        QuerySQL querySQL = SQLFactory.createQuerySQL(this, sa_fad,
                        "sa_fadid", "name", "isonsale", "model", "spec", "price", "sequence", "createby", "createdate", "onsaledate")
                .setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("classid",1);
        querySQL.setWhere(where.toString());
        querySQL.setOrderBy(pageSorting).setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();


        ArrayList<Long> ids = rows.toArrayList("sa_fadid", new ArrayList<>());
        // 附件
        RowsMap RowsMap = getAttachmentUrl(sa_fad, ids);
        for (Row row : rows) {
            Rows attRows = RowsMap.getOrDefault(row.getString("sa_fadid"), new Rows());
            attRows.sortby("sequence","linksid");
            row.put("attinfos", attRows);
            row.put("appleturl", FadHelper.getAppletUrl(this, "FadUrl", "单品小程序链接") + row.getString("sa_fadid"));
            row.putIfAbsent("onsaledate", "");
        }


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

    @API(title = "选择商品列表", apiversion = R.ID20240418141402.v1.class)
    public String chooseItemlist() 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.color like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        Long sa_fadid = content.getLongValue("sa_fadid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item"
                , "itemid", "itemname", "itemno", "model", "spec", "color", "marketprice", "standards");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemclass", "t2", "t2.itemclassid = t1.marketingcategory AND t2.siteid = t1.siteid",
                "itemclassname");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("not exists(select 1 from  sa_fad_link where sa_fadid=" + sa_fadid + " and itemid=t1.itemid and siteid='" + siteid + "'  )");
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);

        Rows rows = querySQL.query();
        // 默认商品图片
        Rows defaultImageRows = getAttachmentUrl("system", (long) 1, "defaultImage");
        // 附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMapCover = getAttachmentUrl("plm_item", ids, "cover");
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        // 商品领域
        RowsMap tradefieldRowsMap = beans.Item.Item.getTradefieldRowsMap(this, ids);

        RowsMap itemclassRowsMap = ItemClass.getAllItemClassRowsMap(this, ids);
        for (Row row : rows) {
            if (attRowsMapCover.getOrDefault(row.getString("itemid"), new Rows()).isNotEmpty()) {
                row.put("attinfos", attRowsMapCover.getOrDefault(row.getString("itemid"), new Rows()));
            } else if (attRowsMap.getOrDefault(row.getString("itemid"), new Rows()).isNotEmpty()) {
                row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            } else {
                row.put("attinfos", defaultImageRows);
            }
            String[] tradefield = tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()).toArray("tradefield");
            row.put("tradefield", StringUtils.join(tradefield, ","));

            String[] itemclass = itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()).toArray("itemclassname");
            row.put("itemclass", StringUtils.join(itemclass, ","));
        }


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

    @API(title = "关联商品列表", apiversion = R.ID20240418141502.v1.class)
    public String relateItemlist() 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.color like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        Long sa_fadid = content.getLongValue("sa_fadid");

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "plm_item"
                , "itemid", "itemname", "itemno", "model", "spec", "color", "marketprice", "standards");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_itemclass", "t2", "t2.itemclassid = t1.marketingcategory AND t2.siteid = t1.siteid",
                "itemclassname");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_fad_link", "t3", "t3.itemid = t1.itemid AND t3.siteid = t1.siteid",
                "sa_fad_linkid");
        querySQL.setWhere("t1.siteid", siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("t3.sa_fadid",sa_fadid);
        querySQL.setOrderBy(pageSorting);
        querySQL.setPage(pageSize, pageNumber);

        Rows rows = querySQL.query();
        // 默认商品图片
        Rows defaultImageRows = getAttachmentUrl("system", (long) 1, "defaultImage");
        // 附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMapCover = getAttachmentUrl("plm_item", ids, "cover");
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);
        // 商品领域
        RowsMap tradefieldRowsMap = beans.Item.Item.getTradefieldRowsMap(this, ids);

        RowsMap itemclassRowsMap = ItemClass.getAllItemClassRowsMap(this, ids);
        for (Row row : rows) {
            if (attRowsMapCover.getOrDefault(row.getString("itemid"), new Rows()).isNotEmpty()) {
                row.put("attinfos", attRowsMapCover.getOrDefault(row.getString("itemid"), new Rows()));
            } else if (attRowsMap.getOrDefault(row.getString("itemid"), new Rows()).isNotEmpty()) {
                row.put("attinfos", attRowsMap.getOrDefault(row.getString("itemid"), new Rows()));
            } else {
                row.put("attinfos", defaultImageRows);
            }
            String[] tradefield = tradefieldRowsMap.getOrDefault(row.getString("itemid"), new Rows()).toArray("tradefield");
            row.put("tradefield", StringUtils.join(tradefield, ","));

            String[] itemclass = itemclassRowsMap.getOrDefault(row.getString("itemid"), new Rows()).toArray("itemclassname");
            row.put("itemclass", StringUtils.join(itemclass, ","));
        }


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

    @API(title = "关联商品", apiversion = R.ID20240418141602.v1.class)
    public String relateItem() throws YosException {
        Long sa_fadid = content.getLongValue("sa_fadid");
        JSONArray itemids = content.getJSONArray("itemids");

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

        for (Object obj : itemids) {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_fad_link");
            insertSQL.setSiteid(siteid);
            insertSQL.setUniqueid(createTableID("sa_fad_link"));
            insertSQL.setValue("itemid", obj);
            insertSQL.setValue("sa_fadid", sa_fadid);
            sqlList.add(insertSQL.getSQL());
        }


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

    @API(title = "删除商品", apiversion = R.ID20240418141702.v1.class)
    public String deleteItem() throws YosException {
        JSONArray sa_fad_linkids = content.getJSONArray("sa_fad_linkids");

        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(this, "sa_fad_link");
        deleteSQL.setSiteid(siteid);
        deleteSQL.setWhere("sa_fad_linkid", sa_fad_linkids);
        deleteSQL.delete();
        return getSucReturnObject().toString();
    }

    public String getClassnames(JSONArray sa_fadclassids, boolean isMain) throws YosException {
        //[[1,2,3,6],[1,2]]
        RowsMap rowsMap = dbConnect.runSqlQuery("SELECT sa_fadclassid,classname from sa_fadclass WHERE siteid='" + siteid + "'").toRowsMap("sa_fadclassid");
        LinkedHashSet<String> classnames = new LinkedHashSet<>();
        for (Object object : sa_fadclassids) {
            ArrayList<String> temp = new ArrayList<>();
            if (object instanceof JSONArray) {
                JSONArray array = (JSONArray) object;
                for (Object obj : array) {
                    temp.add(rowsMap.get(obj.toString()).get(0).getString("classname"));
                }
            }
            if (isMain) {
                classnames.add(temp.get(0));
            } else {
                classnames.add(String.join("-", temp));
            }
        }


        return String.join(";", classnames);
    }


}