CUCU2/src/custom/restcontroller/webmanage/saletool/store/Store.java

package restcontroller.webmanage.saletool.store;

import beans.attachment.Attachment;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import restcontroller.R;

import java.util.ArrayList;

/**
 * 门店小程序(企业人员)
 */
public class Store extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public Store(JSONObject content) throws YosException {
        super(content);
    }

    Controller controller = this;
    String sa_store = "sa_store";

    //查询当前企业人员授权的经销商列表
    @API(title = "查询授权的经销商列表", apiversion = R.ID20240411133702.v1.class)
    public String agentList() 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.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.agentnum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.province like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.city like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.county like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.address like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        Rows saleareaRows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr WHERE hrid=" + hrid + " and siteid='" + siteid + "'");
        ArrayList<Long> sa_saleareaids = saleareaRows.toArrayList("sa_saleareaid", new ArrayList<>());
        sa_saleareaids.addAll(SaleArea.getSubSaleAreaIds(this, sa_saleareaids));
        if (sa_saleareaids.size() == 0) {
            sa_saleareaids.add(-1L);
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sys_enterprise"
                        , "sys_enterpriseid", "enterprisename", "contact", "phonenumber", "province", "city", "county", "address")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t2", "t2.sys_enterpriseid=t1.sys_enterpriseid and t2.siteid=t1.siteid"
                , "agentnum", "status");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where.toString());
        querySQL.setWhere("EXISTS(SELECT 1 from sys_enterprise_tradefield WHERE sys_enterpriseid=t1.sys_enterpriseid and sa_saleareaid in " + sa_saleareaids.toString().replace("[", "(").replace("]", ")") + ")");
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        if(rows.isEmpty()){
            return getSucReturnObject().setData(rows).toString();
        }

        ArrayList<Long> ids=rows.toArrayList("sys_enterpriseid",new ArrayList<>());
        ids.add(-1L);

        RowsMap rowsMap = dbConnect.runSqlQuery("SELECT sys_enterpriseid,count(sys_enterprise_hrid) count from sys_enterprise_hr WHERE siteid='" + siteid + "' and sys_enterpriseid in " + ids.toString().replace("[", "(").replace("]", ")")  + " GROUP by sys_enterpriseid").toRowsMap("sys_enterpriseid");

        for (Row row : rows) {
            row.put("detailaddress", row.getString("province") + row.getString("city") + row.getString("county") + row.getString("address"));
            row.put("areanames", userInfo.getAreaName(this, row.getLong("sys_enterpriseid")));
            Rows countRows = rowsMap.getOrDefault(row.getString("sys_enterpriseid"), new Rows());
            row.put("hrcount", countRows.isNotEmpty() ? countRows.get(0).getLong("count") : 0);

        }


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

    @API(title = "待审核的门店列表", apiversion = R.ID20240411141602.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.storename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.name like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

//            if (whereObject.containsKey("storetype") && !"".equals(whereObject.getString("storetype"))) {
//                where.append(" and (");
//                where.append("t1.storetype='").append(whereObject.getString("storetype")).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(")");
//            }
        }

        Rows saleareaRows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr WHERE hrid=" + hrid + " and siteid='" + siteid + "'");
        ArrayList<Long> sa_saleareaids = saleareaRows.toArrayList("sa_saleareaid", new ArrayList<>());
        sa_saleareaids.addAll(SaleArea.getSubSaleAreaIds(this, sa_saleareaids));


        QuerySQL listQuery = SQLFactory.createQuerySQL(controller, sa_store)
                .setTableAlias("t1");
        listQuery.addJoinTable(JOINTYPE.left, "sys_enterprise_hr", "t2", "t2.sys_enterprise_hrid=t1.leader_hrid and t2.siteid=t1.siteid"
                , "name", "phonenumber");
        listQuery.addJoinTable(JOINTYPE.left, "sys_users", "t3", "t3.userid=t2.userid "
                , "accountno");
        listQuery.addJoinTable(JOINTYPE.left, "sys_enterprise", "t4", "t4.sys_enterpriseid=t1.sys_enterpriseid and t4.siteid=t1.siteid"
                , "enterprisename");
        listQuery.setWhere("EXISTS(SELECT 1 from sys_enterprise_tradefield WHERE sys_enterpriseid=t1.sys_enterpriseid and sa_saleareaid in " + sa_saleareaids.toString().replace("[", "(").replace("]", ")") + ")");
        listQuery.setWhere("status", "提交");
        listQuery.setWhere(where.toString());
        listQuery.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = listQuery.query();

        RowsMap attinfoRows = Attachment.get(controller, sa_store, rows.toArrayList("sa_storeid", new ArrayList<>()));
        for (Row row : rows) {

            row.put("attinfos", attinfoRows.getOrDefault(row.getString("sa_storeid"), new Rows()));
            row.put("detailaddress", row.getString("province") + row.getString("city") + row.getString("county") + row.getString("address"));
        }


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

    @API(title = "查询授权的经销商的团队人员列表", apiversion = R.ID20240411142602.v1.class)
    public String teamList() 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.phonenumber like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }

        }

        Rows saleareaRows = dbConnect.runSqlQuery("SELECT sa_saleareaid from sa_salearea_hr WHERE hrid=" + hrid + " and siteid='" + siteid + "'");
        ArrayList<Long> sa_saleareaids = saleareaRows.toArrayList("sa_saleareaid", new ArrayList<>());
        sa_saleareaids.addAll(SaleArea.getSubSaleAreaIds(this, sa_saleareaids));

        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sys_enterprise_hr"
                        , "sys_enterprise_hrid", "name", "position", "phonenumber", "isleader", "userid")
                .setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("EXISTS(SELECT 1 from sys_enterprise_tradefield WHERE sys_enterpriseid=t1.sys_enterpriseid and sa_saleareaid in " + sa_saleareaids.toString().replace("[", "(").replace("]", ")") + ")");
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        //查询角色
        RowsMap roleRowsMap = StoreHelper.getRoleRowsMap(controller, rows.toArray("userid"));
        //查询门店
        RowsMap storeRowsMap = StoreHelper.getStoreRowsMap(controller, rows.toArray("sys_enterprise_hrid"));

        for (Row row : rows) {
            row.put("headpic", getHeadPic(row.getLong("userid")));
            Rows roleRows = roleRowsMap.getOrDefault(row.getString("userid"), new Rows());
            row.put("rolenames", String.join(",", roleRows.toArrayList("rolename")));

            Rows storeRows = storeRowsMap.getOrDefault(row.getString("sys_enterprise_hrid"), new Rows());
            row.put("storenames", String.join(",", storeRows.toArrayList("storename")));

        }


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

    }


    @API(title = "小程序门店首页", apiversion = R.ID20240416153202.v1.class)
    public String appletHome() throws YosException {

        String longitude = content.getStringValue("longitude");
        String latitude = content.getStringValue("latitude");
        Long sa_storeid = content.getLongValue("sa_storeid");
        Rows rows;

        if (sa_storeid > 0) {
            rows = StoreHelper.queryStores(this, sa_storeid, longitude, latitude);
        } else {
            if (usertype == 21 || usertype == 22) {
                Long sys_enterprise_hrid = getEnterpriseHr(userid).getLong("sys_enterprise_hrid");
                rows = StoreHelper.queryAgentsStores(this, sys_enterprise_hrid, longitude, latitude);
                if (rows.isEmpty()) {
                    rows = StoreHelper.queryStores(this, longitude, latitude);
                }
            } else {
                rows = StoreHelper.queryStores(this, longitude, latitude);
            }
        }


        for (Row row : rows) {
            row.put("distance", String.format("%.2f", row.getDouble("distance")));
            row.put("attinfos", Attachment.get(this, "sa_store", row.getLong("sa_storeid")));
            row.put("headpic", getHeadPic(row.getLong("userid")));
            addHistory(row.getLong("sa_storeid"));
        }


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


    public void addHistory(Long sa_storeid) throws YosException {

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_store_history");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("userid", userid);
        querySQL.setWhere("sa_storeid", sa_storeid);
        Rows rows = querySQL.query();
        if (rows.isNotEmpty()) {
            UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_store_history");
            updateSQL.setSiteid(siteid);
            updateSQL.setValue("userid", userid);
            updateSQL.setWhere("userid", userid);
            updateSQL.setWhere("sa_storeid", sa_storeid);
            System.err.println(updateSQL.getSQL());
            updateSQL.update();
        } else {
            InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_store_history");
            insertSQL.setUniqueid(createTableID("sa_store_history"));
            insertSQL.setSiteid(siteid);
            insertSQL.setValue("userid", userid);
            insertSQL.setValue("sa_storeid", sa_storeid);
            insertSQL.insert();
        }

    }


    @API(title = "查询历史足迹", apiversion = R.ID20240416162002.v1.class)
    public String getHistory() throws YosException {

        String longitude = content.getStringValue("longitude");
        String latitude = content.getStringValue("latitude");

        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_store")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_hr", "t2", "t2.sys_enterprise_hrid=t1.leader_hrid and t2.siteid=t1.siteid"
                , "name", "phonenumber", "userid");
        querySQL.addJoinTable(JOINTYPE.inner, "sa_store_history", "t3", "t3.sa_storeid=t1.sa_storeid and t3.siteid=t1.siteid");
        querySQL.addQueryFields("distance", "st_distance_sphere(point(longitude,latitude),point('" + longitude + "','" + latitude + "'))");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("t3.userid", userid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();

        RowsMap attinfoRows = Attachment.get(controller, sa_store, rows.toArrayList("sa_storeid", new ArrayList<>()));

        for (Row row : rows) {
            row.put("attinfos", attinfoRows.getOrDefault(row.getString("sa_storeid"), new Rows()));
            row.put("distance", String.format("%.2f", row.getDouble("distance")));
            row.put("headpic", getHeadPic(row.getLong("userid")));
        }


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