CUCU2/src/custom/restcontroller/saletool/notice/notice.java

package restcontroller.saletool.notice;

import beans.attachment.Attachment;
import beans.parameter.Parameter;
import beans.remind.Remind;
import com.alibaba.fastjson.JSONObject;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.data.*;
import restcontroller.R;
import utility.email.EmailContent;

import java.util.ArrayList;

@API(title = "通告")
public class notice extends Controller {


    public notice(JSONObject content) throws YosException {
        super(content);
    }

    @API(title = "通告列表", apiversion = R.ID20221111090904.v1.class)
    public String queryNoticeList() throws YosException {
        String where = " 1=1 ";
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where = where + " and t1.title like'%" + whereObject.getString("condition") + "%'";
            }
            if (whereObject.containsKey("sat_notice_classid") && !"".equals(whereObject.getString("sat_notice_classid"))) {
                where = where + " and t1.sat_notice_classid = " + whereObject.getString("sat_notice_classid");
            }
            if (whereObject.containsKey("isread") && !"".equals(whereObject.getString("isread"))) {
                //已读
                if (whereObject.getLongValue("isread") == 0) {
                    where = where + " and t3.confirmed =0";
                }
                //已知
                if (whereObject.getLongValue("isread") == 1) {
                    where = where + " and t3.confirmed =1";
                }
                //未读
                if (whereObject.getLongValue("isread") == 2) {
                    where = where + " and t3.confirmed is null";
                }
            }
        }


        if (pageSorting.equals("''")) {
            pageSorting = "t1.checkdate desc";
        }

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sat_notice"
                        , "sat_noticeid", "title", "checkdate", "status")
                .setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sat_notice_class", "t2", "t1.sat_notice_classid = t2.sat_notice_classid and t2.siteid = t1.siteid",
                "classname");

        querySQL.addJoinTable(JOINTYPE.left, "sat_notice_read", "t3", "t3.siteid = t1.siteid AND t3.sat_noticeid = t1.sat_noticeid and t3.createuserid =" + userid);

        querySQL.addQueryFields("confirmed", "(CASE WHEN  (t3.confirmed is null) THEN 2 ELSE t3.confirmed END)");
        querySQL.setWhere("status","发布");
        querySQL.setSiteid(siteid);
        querySQL.setWhere(where);
        querySQL.setPage(pageSize, pageNumber).setOrderBy(pageSorting).setDataAuth(true);
        Rows rows = querySQL.query();


//        ArrayList<Long> ids = rows.toArrayList("sat_noticeid", new ArrayList<Long>());
//        // 封面cover
//        RowsMap coverRowsMap = getAttachmentUrl("sat_notice", ids, "cover");
//        // 附件
//        RowsMap RowsMap = getAttachmentUrl("sat_notice", ids);
        for (Row row : rows) {
//            Rows Rows = RowsMap.get(row.getString("sat_noticeid"));
//            if (Rows.isEmpty()) {
//                row.put("attinfos", new Rows());
//            } else {
//                row.put("attinfos", Rows);
//            }
//            Rows coverRows = coverRowsMap.get(row.getString("sat_noticeid"));
//            if (coverRows.isEmpty()) {
//                row.put("cover", "");
//            } else {
//                row.put("cover", coverRows.get(0).getString("url"));
//            }
            if (row.getLong("confirmed") == 0) {
                row.putIfAbsent("readstatus", "已读");
            }
            if (row.getLong("confirmed") == 1) {
                row.putIfAbsent("readstatus", "已知");
            }
            if (row.getLong("confirmed") == 2) {
                row.putIfAbsent("readstatus", "未读");
            }
        }

        JSONObject object = new JSONObject();
        object.put("readNum", rows.getTotalRows());


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


    @API(title = "确认已知", apiversion = R.ID20240320152902.v1.class)
    public String confirmed() throws YosException {
        Long sat_noticeid = content.getLong("sat_noticeid");

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sat_notice_read");
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("confirmed", 1);
        updateSQL.setWhere("sat_noticeid", sat_noticeid);
        updateSQL.setWhere("createuserid", userid);
        updateSQL.update();

        return getSucReturnObject().toString();
    }

    @API(title = "发送邮件", apiversion = R.ID20240320153302.v1.class)
    public String sendMail() throws Exception {
        Long sat_noticeid = content.getLong("sat_noticeid");
        String email = content.getStringValue("email");

        if (!Parameter.get(siteid, "remind_mail").equalsIgnoreCase("1")) {
            return getErrReturnObject().setErrMsg("邮件功能未开启").toString();
        }

        Rows rows = dbConnect.runSqlQuery("SELECT * from sat_notice where sat_noticeid=" + sat_noticeid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("数据不存在").toString();
        }

        Rows rowsAtt = Attachment.get(this, "sat_notice", sat_noticeid);

        EmailContent emailContent = new EmailContent();
        emailContent.addText(rows.get(0).getString("content"));
        for (Row rowAtt : rowsAtt) {
            emailContent.addFile(rowAtt.getString("url"));
        }
        Remind remind = new Remind(siteid);
        remind.setTitle(rows.get(0).getString("title"));
        remind.setToemail(email);
        remind.setContent(emailContent);
        remind.sendByMail();


        return getSucReturnObject().toString();
    }

    @API(title = "通告详情")
    public String queryNoticeMain() throws YosException {
        Long sat_noticeid = content.getLong("sat_noticeid");
        // 新增记录
        addReadRecord(sat_noticeid);
        SQLFactory sqlFactory = new SQLFactory(this, "通告详情查询");
        sqlFactory.addParameter("sat_noticeid", sat_noticeid);
        sqlFactory.addParameter("userid", userid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        // 附件
        ArrayList<Long> ids = rows.toArrayList("sat_noticeid", new ArrayList<Long>());
        RowsMap attRowsMap = getAttachmentUrl("sat_notice", ids);
        for (Row row : rows) {
            Rows Rows = attRowsMap.get(row.getString("sat_noticeid"));
            if (Rows.isEmpty()) {
                row.put("attinfos", new Rows());
            } else {
                row.put("attinfos", Rows);
            }
        }
        return getSucReturnObject().setData(rows.get(0)).toString();
    }

    /**
     * 查询阅读记录(通告留言和打分)
     *
     * @return
     */
    @API(title = "查询阅读记录")
    public String queryReadRecord() throws YosException {
        Long sat_noticeid = content.getLong("sat_noticeid");
        Rows rows = dbConnect
                .runSqlQuery("select t2.usertype,t3.position,t1.createdate,t1.createby,t1.leavemessage,t1.score,(SELECT count(*) FROM sat_notice_read WHERE  siteid ='" + siteid + "' AND sat_noticeid = '" + sat_noticeid + "' and score>0) evaluatecount FROM sat_notice_read t1\n" +
                        "   left join sys_usersite t2 on t1.createuserid=t2.userid and t1.siteid=t2.siteid\n" +
                        "		 left join sys_enterprise_hr t3 on t1.createuserid=t3.userid and t1.siteid=t3.siteid\n" +
                        " WHERE t1.siteid = '" + siteid + "' AND t1.sat_noticeid ='" + sat_noticeid + "' AND t1.createuserid = '" + userid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("阅读记录不存在").toString();
        }
        return getSucReturnObject().setData(rows).toString();
    }

    /**
     * 更新阅读记录(通告留言和打分)
     *
     * @return
     */
    @API(title = "更新阅读记录")
    public String updateReadRecord() throws YosException {
        Long sat_noticeid = content.getLong("sat_noticeid");
        int score = content.getInteger("score");
        String leavemessage = content.getString("leavemessage");
        Rows rows = dbConnect.runSqlQuery("select sat_notice_readid FROM sat_notice_read WHERE siteid = '" + siteid
                + "' AND sat_noticeid = '" + sat_noticeid + "' AND createuserid = '" + userid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("阅读记录不存在，无法更新").toString();
        }
        SQLFactory sqlFactory = new SQLFactory(this, "阅读记录更新");
        sqlFactory.addParameter("leavemessage", leavemessage);
        sqlFactory.addParameter("score", score);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sat_noticeid", sat_noticeid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("hrid", hrid);
        sqlFactory.addParameter("sa_agentsid", userInfo.getAgentID());
        dbConnect.runSqlUpdate(sqlFactory);
        return getSucReturnObject().toString();
    }

    @API(title = "更新下载附件记录")
    public String updateDownloadRecord() throws YosException {
        Long sat_noticeid = content.getLong("sat_noticeid");
        dbConnect.runSqlUpdate("UPDATE sat_notice_read SET isdownloadfile=1 WHERE siteid = '" + siteid
                + "' AND sat_noticeid = '" + sat_noticeid + "' AND createuserid = '" + userid + "'");
        return getSucReturnObject().toString();
    }

    // 添加阅读记录
    public void addReadRecord(Long sat_noticeid) throws YosException {
        // 新增浏览次数
        dbConnect.runSqlUpdate("UPDATE sat_notice SET readcount=readcount+1 WHERE sat_noticeid='" + sat_noticeid + "'");
        // 查询当前用户的阅读记录是否存在
        String sql = "SELECT sat_notice_readid FROM sat_notice_read WHERE siteid = '" + siteid
                + "' AND sat_noticeid = '" + sat_noticeid + "' AND createuserid = '" + userid + "'";
        Rows rows = dbConnect.runSqlQuery(sql);
        String result = "false";
        if (rows.isEmpty()) {
            SQLFactory sqlFactory = new SQLFactory(this, "阅读记录新增");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sat_notice_readid", createTableID("sat_notice_read"));
            sqlFactory.addParameter("createby", username);
            sqlFactory.addParameter("changeby", username);
            sqlFactory.addParameter("sat_noticeid", sat_noticeid);
            sqlFactory.addParameter("createuserid", userid);
            sqlFactory.addParameter("hrid", hrid);
            sqlFactory.addParameter("sa_agentsid", userInfo.getAgentID());
            dbConnect.runSqlUpdate(sqlFactory);
        } else {
            SQLFactory sqlFactory = new SQLFactory(this, "阅读记录次数更新");
            sqlFactory.addParameter("siteid", siteid);
            sqlFactory.addParameter("sat_noticeid", sat_noticeid);
            sqlFactory.addParameter("userid", userid);
            sqlFactory.addParameter("hrid", hrid);
            sqlFactory.addParameter("sa_agentsid", userInfo.getAgentID());
            dbConnect.runSqlUpdate(sqlFactory);
        }

    }

}