CUCU2/src/custom/restcontroller/webmanage/users/visitors.java

package restcontroller.webmanage.users;

import beans.attachment.Attachment;
import beans.datatag.DataTag;
import com.alibaba.fastjson.JSONObject;
import com.mysql.cj.x.protobuf.MysqlxCrud;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import org.apache.commons.lang.StringUtils;
import restcontroller.R;

import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.HashMap;

import static restcontroller.webmanage.sale.enterprise.enterprise.validatePhoneNumber;

/**
 * 营销宝，我的，游客
 */
public class visitors extends Controller {
    /**
     * 构造函数
     *
     * @param content
     */
    public visitors(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "【游客，工作台】个人信息", apiversion = R.ID20240510104102.v1.class)
    public String visitorsInfo() throws YosException {
        Row row = new Row();
        //统计我的访问店铺数量
        Rows rows = dbConnect.runSqlQuery("SELECT count(*) count from sa_store_history WHERE userid=" + userid + " and siteid='" + siteid + "'");
        row.put("count_historystore", rows.get(0).getLong("count"));
        //预约
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sa_appointment WHERE createuserid=" + userid + " and siteid='" + siteid + "'");
        row.put("count_appointment",  rows.get(0).getLong("count"));
        //地址
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sys_enterprise_contacts WHERE createuserid=" + userid + " and siteid='" + siteid + "' and deleted=0");
        row.put("count_address", rows.get(0).getLong("count"));
        //收藏
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sys_datacollect WHERE userid=" + userid + " and siteid='" + siteid + "' and type=1");
        row.put("count_collect", rows.get(0).getLong("count"));
        //订单
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sa_custorder WHERE createuserid=" + userid + " and siteid='" + siteid + "' and deleted=0");
        row.put("count_order", rows.get(0).getLong("count"));
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sys_phonebook WHERE sys_phonebookgroupid in (SELECT sys_phonebookgroupid from sys_phonebookgroup WHERE classid=1) and siteid='"+siteid+"'");
        //  通讯录
        row.put("count_addressbook",  rows.get(0).getLong("count"));
        //考试成绩
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sat_courseware_test WHERE userid=" + userid + " and siteid='" + siteid + "' ");
        row.put("count_coursewaretest", rows.get(0).getLong("count"));
        //  问卷调查
        row.put("count_questions", 0);
        rows = dbConnect.runSqlQuery("SELECT count(*) count from sa_feedback WHERE changeuserid=" + userid + " and siteid='" + siteid + "' ");
        // 意见反馈
        row.put("count_feedback",  rows.get(0).getLong("count"));

        row.putAll(getUser(userid));
        row.put("headpic", getHeadPic(userid));
        row.put("attinfos", Attachment.get(this, "sys_users", userid, "headportrait"));
        //查询角色
        Rows rolenameRows = dbConnect.runSqlQuery("select rolename from sys_userrole t1 " +
                "inner join sys_role t2 ON t2.roleid=t1.roleid and t2.siteid=t1.siteid " +
                "WHERE t1.userid=" + userid + " and t1.siteid='" + siteid + "'");
        row.put("rolenames", StringUtils.join(rolenameRows.toArray("rolename"), ","));
        //加入团队时间
        LocalDate startDate = LocalDate.parse(row.getString("createdate").substring(0, 10));
        LocalDate endDate = LocalDate.now();
        long days = ChronoUnit.DAYS.between(startDate, endDate);
        row.put("joindays", days);
        //签到时间
        Rows signRows = dbConnect.runSqlQuery("SELECT count(DISTINCT t1.sat_sharematerialid) count from sat_sharematerial_read t1 " +
                "inner join sat_sharematerial t2 ON t2.sat_sharematerialid=t1.sat_sharematerialid " +
                "WHERE t2.classid=1 and t1.createuserid=" + userid + " and t1.type=0 and t1.siteid='" + siteid + "'");
        row.put("signdays", signRows.get(0).getLong("count"));

        Rows storerows = dbConnect.runSqlQuery("SELECT t1.*from sa_store t1 \n" +
                "inner join sa_store_hr t2 on t2.sa_storeid=t1.sa_storeid and t2.siteid=t1.siteid\n" +
                "inner join sys_enterprise_hr t3 ON t3.sys_enterprise_hrid=t2.sys_enterprise_hrid and t3.siteid=t2.siteid\n" +
                "WHERE t1.sys_enterpriseid=" + sys_enterpriseid + " and t1.siteid='" + siteid + "' and t3.userid='" + userid + "'");
        row.put("stores", storerows);


        row.put("areaname", userInfo.getAreaName(this));
        row.put("sys_enterpriseid", sys_enterpriseid);

        row.put("enterprise_hr", userInfo.getEnterprise_HrRow());

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

    @API(title = "名片信息", apiversion = R.ID20240514161502.v1.class)
    public String cardInfo() throws YosException {
        Long tempuserid = content.getLongValue("userid");

        //插入浏览记录
        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sys_datacollect");
        insertSQL.setUniqueid(createTableID("sys_datacollect"));
        insertSQL.setSiteid(siteid);
        insertSQL.setValue("userid", userid);
        insertSQL.setValue("ownertable", "sys_users");
        insertSQL.setValue("ownerid", tempuserid);
        insertSQL.setValue("type", 3);
        insertSQL.insert();


        Row row = getUser(tempuserid);
        Row enterpriseRow = getEnterpriseHr(tempuserid);
        String enterprisename = "";
        if (!enterpriseRow.isEmpty()) {
            enterprisename = enterpriseRow.getString("enterprisename");
        }
        row.put("enterprisename", enterprisename);
        row.put("headpic", getHeadPic(tempuserid));
        //查询角色
        Rows rolenameRows = dbConnect.runSqlQuery("select rolename from sys_userrole t1 " +
                "inner join sys_role t2 ON t2.roleid=t1.roleid and t2.siteid=t1.siteid " +
                "WHERE t1.userid=" + tempuserid + " and t1.siteid='" + siteid + "'");
        row.put("rolenames", StringUtils.join(rolenameRows.toArray("rolename"), ","));

        Rows attinfoRows = Attachment.get(this, "sys_users", tempuserid);
        row.put("attinfos", attinfoRows);

        //标签
        ArrayList<String> tagList = DataTag.queryTag(this, "sys_users", tempuserid, false);
        //系统标签
        tagList.addAll(DataTag.queryTag(this, "sys_users", tempuserid, true));
        row.put("tag", tagList);

        Rows rows = dbConnect.runSqlQuery("SELECT count(*) count from sys_datacollect WHERE  siteid='" + siteid + "' and type=2 and ownertable='sys_users' and ownerid='" + tempuserid + "'");
        row.put("count_like", rows.get(0).getLong("count"));

        rows = dbConnect.runSqlQuery("SELECT count(*) count from sys_datacollect WHERE  siteid='" + siteid + "' and type=3 and ownertable='sys_users' and ownerid='" + tempuserid + "'");
        row.put("count_read", rows.get(0).getLong("count"));

        if (dbConnect.runSqlQuery("SELECT * from sys_datacollect WHERE  siteid='" + siteid + "' and type=2 and ownertable='sys_users' and ownerid='" + tempuserid + "' and userid=" + userid).isNotEmpty()) {
            row.put("islike", 1);
        } else {
            row.put("islike", 0);
        }


        rows = dbConnect.runSqlQuery("SELECT userid,max(createdate) createdate from sys_datacollect WHERE  siteid='" + siteid + "' and type=3 and ownertable='sys_users' and ownerid='" + tempuserid + "' GROUP  BY userid LIMIT 3");

        for (Row readrow : rows) {
            readrow.put("headpic", getHeadPic(readrow.getLong("userid")));
        }
        row.put("readusers", rows);

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

    @API(title = "更新游客信息", apiversion = R.ID20240511151602.v1.class)
    public String updatevisitorsInfo() throws YosException {
        String phonenumber = content.getStringValue("phonenumber");

        if (!content.getStringValue("phonenumber").isEmpty()) {
            boolean isValidPhoneNumber = validatePhoneNumber(phonenumber);
            if (!isValidPhoneNumber) {
                return getErrReturnObject().setErrMsg("请输入正确的手机号").toString();
            }

            if (dbConnect.runSqlQuery("select *from sys_users where phonenumber='" + phonenumber + "' and userid!='" + userid + "'").isNotEmpty()) {
                return getErrReturnObject().setErrMsg("存在重复的手机号信息").toString();
            }
        }

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sys_users");
        updateSQL.setUniqueid(userid);
        updateSQL.setValue("name", content.getStringValue("name"));
        updateSQL.setValue("phonenumber", content.getStringValue("phonenumber"));
        updateSQL.setValue("email", content.getStringValue("email"));
        updateSQL.setValue("address", content.getStringValue("address"));
        updateSQL.setValue("remarks", content.getStringValue("remarks"));
        updateSQL.update();


        return visitorsInfo();

    }

    @API(title = "全局搜索", apiversion = R.ID20240513085802.v1.class)
    public String search() throws YosException {

        //1:产品：来源单品、案例-电器系统
        //2:效果图：来源效果图管理；
        //3:课件：来源商学院授权可查看的课程中的课件；
        //4:资料：来源装备资源库授权可查看的资料；
        int type = content.getIntValue("type");

        Rows rows = new Rows();
        if (type == 1) {
            rows = searchFads();
        }
        if (type == 2) {
            rows = searchRenderings();
        }
        if (type == 3) {
            rows = searchCoursewaredetail();
        }
        if (type == 4) {
            rows = searchEquipmentResourceLibrary();
        }


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

    public Rows searchFads() 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(")");
            }
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_fad", "sa_fadid").setTableAlias("t1");
        querySQL.setWhere("isonsale", 1);
        querySQL.setWhere("classid", 1);
        querySQL.addQueryFields("title", "t1.name");
        querySQL.setWhere(where.toString());
        querySQL.setSiteid(siteid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        RowsMap RowsMap = getAttachmentUrl("sa_fad", rows.toArrayList("sa_fadid", new ArrayList<>()));
        for (Row row : rows) {
            Rows attRows = RowsMap.getOrDefault(row.getString("sa_fadid"), new Rows());
            attRows.sortby("sequence", "linksid");
            row.put("attinfos", attRows);
        }
        return rows;
    }

    public Rows searchRenderings() 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.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_sharematerial", "sat_sharematerialid", "title").setTableAlias("t1");
        querySQL.setWhere("classid", 4);
        querySQL.setWhere("status", "发布");
        querySQL.setWhere(where.toString());
        querySQL.setSiteid(siteid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        Rows rows = querySQL.query();
        RowsMap RowsMap = getAttachmentUrl("sat_sharematerial", rows.toArrayList("sat_sharematerialid", new ArrayList<>()));
        for (Row row : rows) {
            Rows attRows = RowsMap.getOrDefault(row.getString("sat_sharematerialid"), new Rows());
            attRows.sortby("sequence", "linksid");
            row.put("attinfos", attRows);
        }
        return rows;
    }

    //课件
    public Rows searchCoursewaredetail() 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.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_courseware", "sat_coursewareid").setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("status", "发布");
        querySQL.setDataAuth(true);
        Rows rows = querySQL.query();

        ArrayList<Long> sat_coursewareids = rows.toArrayList("sat_coursewareid", new ArrayList<>());


        querySQL = SQLFactory.createQuerySQL(this, "sat_coursewaredetail", "sat_coursewaredetailid", "title").setTableAlias("t1");
        querySQL.setWhere("sat_coursewareid", sat_coursewareids);
        querySQL.setWhere("status", "上架");
        querySQL.setWhere(where.toString());
        querySQL.setSiteid(siteid);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting);
        rows = querySQL.query();
        RowsMap RowsMap = getAttachmentUrl("sat_coursewaredetail", rows.toArrayList("sat_coursewaredetailid", new ArrayList<>()));
        for (Row row : rows) {
            Rows attRows = RowsMap.getOrDefault(row.getString("sat_coursewaredetailid"), new Rows());
            attRows.sortby("sequence", "linksid");
            row.put("attinfos", attRows);
        }
        return rows;
    }

    public Rows searchEquipmentResourceLibrary() 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.title like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_sharematerial", "sat_sharematerialid", "title").setTableAlias("t1");
        querySQL.setSiteid(siteid);
        querySQL.setWhere("classid", 3);
        querySQL.setWhere("status", "发布");
        querySQL.setWhere(where.toString());
        querySQL.setOrderBy(pageSorting).setPage(pageSize, pageNumber);
        querySQL.setDataAuth(true);
        Rows rows = querySQL.query();

        RowsMap RowsMap = getAttachmentUrl("sat_sharematerial", rows.toArrayList("sat_sharematerialid", new ArrayList<>()));
        for (Row row : rows) {
            Rows attRows = RowsMap.getOrDefault(row.getString("sat_sharematerialid"), new Rows());
            attRows.sortby("sequence", "linksid");
            row.put("attinfos", attRows);
        }
        return rows;
    }


}