md_YOS/src/custom/restcontroller/webmanage/sale/bankstatement/CreateBankstatement.java

package restcontroller.webmanage.sale.bankstatement;

import beans.remind.Remind;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.ServiceController;
import common.YosException;
import common.data.Rows;
import common.data.SQLFactory;
import utility.email.EmailContent;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.LinkedHashSet;

public class CreateBankstatement {
    /**
     * @param controller
     * @param jsonArray
     * @param siteid
     * @return
     * @throws YosException
     */
    public static JSONObject createBankstatement(ServiceController controller, JSONArray jsonArray, String siteid) throws YosException {
        ArrayList<String> tranflowList = controller.dbConnect.runSqlQuery("select tranflow from sa_bankstatement where siteid='" + siteid + "' and banktype='农行'").toArrayList("tranflow");
        ArrayList<String> sqlList = new ArrayList<>();
        ArrayList<Long> idList = new ArrayList<>();
        for (Object o : jsonArray) {
            JSONObject jsonObject = (JSONObject) o;
            String tranflow = jsonObject.getString("TimeStab");
            if (!tranflowList.contains(tranflow)) {
                String trantime = jsonObject.getString("TrDate");
                String PostScript = jsonObject.getString("PostScript");
                long sa_bankstatementid = controller.createTableID("sa_bankstatement");
                BigDecimal amount = jsonObject.getBigDecimal("Amt");
                SQLFactory sqlFactory = new SQLFactory(controller, "银企直联流水账新增");
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("sa_bankstatementid", sa_bankstatementid);
                sqlFactory.addParameter("banktype", "农行");
                if (jsonObject.containsKey("accname")) {
                    sqlFactory.addParameter("accname", jsonObject.getString("AccName"));
                } else {
                    sqlFactory.addParameter("accname", "");
                }
                sqlFactory.addParameter("accno", jsonObject.getString("AccNo"));
                sqlFactory.addParameter("tranflow", tranflow);
                sqlFactory.addParameter("oppbkname", jsonObject.getString("OppBkName"));
                sqlFactory.addParameter("oppname", jsonObject.getString("OppName"));
                sqlFactory.addParameter("oppaccno", jsonObject.getString("OppAccNo"));
                sqlFactory.addParameter("amount", amount);
                sqlFactory.addParameter("trantime", trantime);
                sqlFactory.addParameter("notes", jsonObject.getString("Abs"));
                sqlFactory.addParameter("remarks", PostScript);
                sqlFactory.addParameter("response", jsonObject.toString());
                sqlFactory.addParameter("xncardno", "");
                sqlFactory.addParameter("sys_enterpriseid", 0);
                sqlFactory.addParameter("sa_cashbillid", 0);
                sqlFactory.addParameter("status", "未入账");
                sqlFactory.addParameter("username", "");
                sqlFactory.addParameter("createdate", LocalDateTime.now());
                String POSTSCRIPT = jsonObject.getString("PostScript");
                POSTSCRIPT = POSTSCRIPT.trim();
                if (!POSTSCRIPT.equals("") && POSTSCRIPT.length() >= 19) {
                    String xncardnoabc = POSTSCRIPT.substring(0, 19);
                    Rows enterprise = controller.dbConnect.runSqlQuery("select t2.sys_enterpriseid from sa_bankabcxccard t1 inner join sa_agents t2 on t1.siteid=t2.siteid and t1.sa_agentsid=t2.sa_agentsid where t1.siteid='" + siteid + "' and t1.xncardnoabc='" + xncardnoabc + "' and t1.isused=1");
                    if (enterprise.isNotEmpty() && enterprise.size() == 1) {
                        long sys_enterpriseid = enterprise.get(0).getLong("sys_enterpriseid");
                        sqlFactory.addParameter("xncardno", xncardnoabc);
                        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                        JSONObject createcashbill = CreateCashbill.createcashbill(controller, sys_enterpriseid, amount, siteid, sa_bankstatementid, "农行", jsonObject.getStringValue("OppName"));
                        if (createcashbill != null) {
                            sqlFactory.addParameter("status", "已入账(系统)");
                            long sa_cashbillid = createcashbill.getLong("sa_cashbillid");
                            sqlFactory.addParameter("sa_cashbillid", sa_cashbillid);
                            sqlList.add(sqlFactory.getSQL());
                            sqlList.addAll((ArrayList<String>) createcashbill.get("sql"));
                        } else {
                            sqlList.add(sqlFactory.getSQL());
                        }
                    } else {
                        sqlList.add(sqlFactory.getSQL());
                    }
                } else {
                    sqlList.add(sqlFactory.getSQL());
                }
                idList.add(sa_bankstatementid);
            }
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("sqlList", sqlList);
        jsonObject.put("idList", idList);
        return jsonObject;
    }

    public static void autoRemind(ServiceController controller, String remindname, long dataid, String siteid) throws YosException {
        Rows sys_remind_configsRows = controller.dbConnect.runSqlQuery("select t2.ownertable,t1.remindtitle,t1.remindmode,t1.content,t1.isrole,t1.roleids,t1.isuser,t1.userids,t1.issql,t1.sqlstr from sys_remind_configs t1 " +
                "inner join sys_remind_config t2 on t1.sys_remind_configid=t2.sys_remind_configid where siteid='" + siteid + "' and t2.remindname='" + remindname + "'");
        if (sys_remind_configsRows.isNotEmpty()) {
            String ownertable = sys_remind_configsRows.get(0).getString("ownertable");
            String remindtitle = sys_remind_configsRows.get(0).getString("remindtitle");
            JSONArray remindmodeArray = sys_remind_configsRows.get(0).getJSONArray("remindmode");
            String content = sys_remind_configsRows.get(0).getString("content");
            boolean isrole = sys_remind_configsRows.get(0).getBoolean("isrole");
            boolean isuser = sys_remind_configsRows.get(0).getBoolean("isuser");
            boolean issql = sys_remind_configsRows.get(0).getBoolean("issql");

            LinkedHashSet<Long> useridList = new LinkedHashSet<>();
            if (isrole) {
                JSONArray roleids = sys_remind_configsRows.get(0).getJSONArray("roleids");
                for (Object o : roleids) {
                    useridList.addAll(controller.dbConnect.runSqlQuery("select userid from sys_userrole where siteid='" + siteid + "' and roleid=" + o).toArrayList("userid", new ArrayList<>()));
                }
            }
            if (isuser) {
                JSONArray userids = sys_remind_configsRows.get(0).getJSONArray("userids");
                for (Object o : userids) {
                    useridList.add(Long.parseLong(o.toString()));
                }
            }
            if (issql) {
                String sqlstr = sys_remind_configsRows.get(0).getString("sqlstr");
                if (sqlstr.contains(":")) {
                    Rows datarows = controller.dbConnect.runSqlQuery("select * from " + ownertable + " where " + controller.getuniquecolumnname(ownertable) + "=" + dataid);
                    if (datarows.isNotEmpty()) {
                        for (String key : datarows.get(0).keySet()) {
                            if (sqlstr.contains(":" + key)) {
                                sqlstr = sqlstr.replace(":" + key, "'" + datarows.get(0).getString(key) + "'");
                            }
                        }
                    }
                }
                try {
                    useridList.addAll(controller.dbConnect.runSqlQuery(sqlstr).toArrayList("userid", new ArrayList<>()));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            if (content.contains(":")) {
                Rows datarows = controller.dbConnect.runSqlQuery("select * from " + ownertable + " where " + controller.getuniquecolumnname(ownertable) + "=" + dataid);
                if (datarows.isNotEmpty()) {
                    for (String key : datarows.get(0).keySet()) {
                        if (content.contains(":" + key)) {
                            content = content.replace(":" + key, "'" + datarows.get(0).getString(key) + "'");
                        }
                    }
                }
            }
            int max = 10;
            while (content.contains("{") && content.contains("}")) {
                int start = content.indexOf("{");
                int end = content.indexOf("}");
                String parm = content.substring(start, end + 1);
                String sql = parm.replace("{", "").replace("}", "");
                Rows rows = controller.dbConnect.runSqlQuery(sql);
                if (rows.size() != 1 || rows.getFieldList().size() != 1) {
                    break;
                } else {
                    String value = rows.get(0).getString(rows.getFieldList().get(0));
                    content = content.replace(parm, value);
                }
                if (max-- == 0) {
                    break;
                }
            }

            for (Object o : remindmodeArray) {
                String remindmode = (String) o;
                switch (remindmode) {
                    case "邮件": {
                        for (Long userid : useridList) {
                            ArrayList<String> emailList = controller.dbConnect.runSqlQuery("select distinct email from ( SELECT userid,email FROM sys_hr where siteid='" + siteid + "' and userid='" + userid + "'and email like'%@%'and LENGTH(email)>2 union all select userid,email from sys_enterprise_hr where siteid='" + siteid + "' and userid='" + userid + "'and email like'%@%'and LENGTH(email)>2)t").toArrayList("email");
                            try {
                                EmailContent emailContent = new EmailContent();
                                emailContent.addText(content);
                                Remind remind = new Remind(siteid);
                                remind.setTitle(remindtitle);
                                remind.setToemail(emailList);
                                remind.setContent(emailContent);
                                remind.sendByMail();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                        break;
                    }
                    case "短信": {
                        for (Long userid : useridList) {
                            ArrayList<String> phonenumberList = controller.dbConnect.runSqlQuery("select phonenumber from sys_users where userid=" + userid).toArrayList("phonenumber");
                            try {
                                Remind remind = new Remind(siteid);
                                remind.setContent(content);
                                remind.setTophonenumber(phonenumberList);
                                remind.sendBySms();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                        break;
                    }
                    case "弹框": {
                        for (Long userid : useridList) {
                            ArrayList<String> phonenumberList = controller.dbConnect.runSqlQuery("select phonenumber from sys_users where userid=" + userid).toArrayList("phonenumber");
                            try {
                                Remind remind = new Remind(siteid);
                                remind.setTitle(remindtitle);
                                remind.setContent(content);
                                remind.setTouserid(userid);
                                remind.setTophonenumber(phonenumberList);
                                remind.sendByDialogMsg();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                        break;
                    }
                }
            }
        }
    }
}