md_YOS/src/custom/restcontroller/webmanage/sale/serviceorder/serviceorder.java

package restcontroller.webmanage.sale.serviceorder;

import beans.data.BatchDeleteErr;
import beans.datacontrllog.DataContrlLog;
import beans.datatag.DataTag;
import beans.datateam.DataTeam;
import beans.parameter.Parameter;
import beans.salearea.SaleArea;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import common.BaseClass;
import common.Controller;
import common.YosException;
import common.annotation.API;
import common.annotation.CACHEING;
import common.annotation.CACHEING_CLEAN;
import common.crm.bean.warrantycard;
import common.data.*;

import org.apache.commons.lang.StringUtils;
import org.apache.cxf.configuration.security.DNConstraintsType;
import org.glassfish.grizzly.utils.StringFilter;
import restcontroller.R;
import restcontroller.webmanage.executorService.Executor;
import restcontroller.webmanage.sale.serviceorder.tools.HttpURLRest;
import restcontroller.webmanage.sale.serviceorder.tools.VerificationManage;
import restcontroller.webmanage.sale.workorder.workorder;
import restcontroller.webmanage.sale.workorder.workpresetInfo;
import utility.sms.Sms;

import java.util.*;
import java.util.regex.Pattern;

/**
 * 服务申请单
 */
@API(title = "服务申请单管理")
public class serviceorder extends Controller {

    public static HashMap<String, VerificationManage> message_map = new HashMap<>();

    public serviceorder(JSONObject arg0) throws YosException {
        super(arg0);
        // TODO Auto-generated constructor stub
    }

    @API(title = "服务申请单新增更新", apiversion = R.ID20230206091403.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230206091603.v1.class, R.ID20230206091703.v1.class, R.ID20230206101303.v1.class, R.ID20230217100703.v1.class, R.ID20230217133003.v1.class})
    public String insertormodify_serviceorder() throws YosException {
        ArrayList<String> sqlList = new ArrayList<>();
        // 表名
        String tableName = "sa_serviceorder";
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        String servicetype = content.getString("servicetype"); //服务分类
        if (sys_enterpriseid <= 0) {
            sys_enterpriseid = content.getLong("sys_enterpriseid");
        }
        String remarks = content.getStringValue("remarks");
        String class1 = content.getStringValue("class1");
        String class2 = content.getStringValue("class2");
        String reason = content.getStringValue("reason");
        String warrantystatus = content.getStringValue("warrantystatus");
        String province = content.getStringValue("province");
        String city = content.getStringValue("city");
        String county = content.getStringValue("county");
        String address = content.getStringValue("address");
        String scenecontact = content.getStringValue("scenecontact");
        String scenecontactrole = content.getStringValue("scenecontactrole");
        String scenecontactphonenumber = content.getStringValue("scenecontactphonenumber");
        String billdate = content.getStringValue("billdate");
        String begdate = content.getStringValue("begdate");
        String enddate = content.getStringValue("enddate");
        long saler_hrid = content.getLongValue("saler_hrid");
        boolean isonlinesales = content.getBooleanValue("isonlinesales");
        boolean isallowance = content.getBooleanValue("isallowance");

        long sys_enterpriseid_service = content.getLongValue("sys_enterpriseid_service");

        String sku = content.getStringValue("sku");
        String cardno = content.getStringValue("cardno");
        long itemid = content.getLongValue("itemid");
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单新增");

        if (content.containsKey("servicetype")) {
            if (content.getString("servicetype").equals("安装")) {
                if (StringUtils.isBlank(sku)) {
                    //return getErrReturnObject().setErrMsg("安装服务单需添加序列号").toString();
                }
                if (dbConnect.runSqlQuery("select * from sa_warrantycard where sku='" + sku + "' and siteid='" + siteid + "'").isNotEmpty()) {
                    DataTag.createSystemTag(this, "sa_serviceorder", sa_serviceorderid, "重复安装");
                    //return getErrReturnObject().setErrMsg("该序列号已安装,无法再次安装").toString();
                }
            }
        }
        String initiator="";
        Rows userrows = dbConnect.runSqlQuery("select t2.usertype,t1.phonenumber from  sys_users t1 inner join sys_usersite t2 on t1.userid = t2.userid where t2.siteid = '" + siteid + "' and t1.userid=" + userid);
        long usertype = 0;

        if (userrows.isNotEmpty()) {
            usertype = userrows.get(0).getLong("usertype");
        }
        if(usertype==99){
            initiator="消费者";
        }else if(usertype==21 || usertype==22){
            initiator="经销商";
        }else{
            initiator="总部";
        }
        if (sa_serviceorderid <= 0 || dbConnect.runSqlQuery(
                        "select sa_serviceorderid from sa_serviceorder where sa_serviceorderid=" + sa_serviceorderid)
                .isEmpty()) {
            sa_serviceorderid = createTableID(tableName);
            sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
            if (saler_hrid != 0) {
                sqlFactory.addParameter("saler_hrid", saler_hrid);
            } else {
                Rows hrRows = dbConnect.runSqlQuery("select hrid from sys_enterprise_tradefield where siteid='" + siteid + "' and sys_enterpriseid=" + sys_enterpriseid);
                if (hrRows.isEmpty()) {
                    sqlFactory.addParameter("saler_hrid", saler_hrid);
                } else {
                    sqlFactory.addParameter("saler_hrid", hrRows.get(0).getLong("hrid"));
                }
            }
            sqlList.add(
                    DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "新增", "服务申请单新增成功").getSQL());
        } else {
            Rows rows = dbConnect.runSqlQuery(
                    "SELECT status,sys_enterpriseid,sa_orderid from sa_serviceorder WHERE sa_serviceorderid = "
                            + sa_serviceorderid);
            if (rows.isNotEmpty()) {
                if (rows.get(0).getString("status").equals("新建") || rows.get(0).getString("status").equals("待分配")) {
                    sqlFactory = new SQLFactory(this, "服务申请单更新");
                    sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
                    sqlList.add(DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "更新", "服务申请单更新成功")
                            .getSQL());
                } else {
                    return getErrReturnObject().setErrMsg("该状态下无法编辑").toString();
                }

            } else {
                return getErrReturnObject().setErrMsg("该服务申请单不存在").toString();
            }

        }
        sqlFactory.addParameter("saler_hrid", saler_hrid);
        sqlFactory.addParameter("servicetype", servicetype);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_serviceorderid", sa_serviceorderid);
        sqlFactory.addParameter("sa_orderid", 0);
        sqlFactory.addParameter("remarks", remarks);
        sqlFactory.addParameter("class1", class1);
        sqlFactory.addParameter("class2", class2);
        sqlFactory.addParameter("reason", reason);
        sqlFactory.addParameter("province", province);
        sqlFactory.addParameter("city", city);
        sqlFactory.addParameter("county", county);
        sqlFactory.addParameter("address", address);
        sqlFactory.addParameter("scenecontact", scenecontact);
        sqlFactory.addParameter("scenecontactrole", scenecontactrole);
        sqlFactory.addParameter("scenecontactphonenumber", scenecontactphonenumber);
        // 服务申请单号createBillCode("serviceorder")
        sqlFactory.addParameter("billno", createBillCode("serviceorder"));
        sqlFactory.addParameter("billdate", billdate.equals("") ? "null" : billdate);
        sqlFactory.addParameter("begdate", begdate.equals("") ? "null" : begdate);
        sqlFactory.addParameter("enddate", enddate.equals("") ? "null" : enddate);

        sqlFactory.addParameter("name", content.getStringValue("name"));
        sqlFactory.addParameter("phonenumber", content.getStringValue("phonenumber"));
        sqlFactory.addParameter("sa_customersid", content.getStringValue("sa_customersid"));
        sqlFactory.addParameter("isonlinesales",isonlinesales);
        sqlFactory.addParameter("isallowance",isallowance);

        sqlFactory.addParameter("initiator",initiator);



        sqlFactory.addParameter("sys_enterpriseid_service",sys_enterpriseid_service);


        sqlList.add(sqlFactory.getSQL());

        InsertSQL insertSQL = SQLFactory.createInsertSQL(this, "sa_serviceorderitems");
        insertSQL.setUniqueid(createTableID("sa_serviceorderitems"));
        insertSQL.setSiteid(siteid);
        insertSQL.setValue("sa_serviceorderid", sa_serviceorderid);
        insertSQL.setValue("itemid", itemid);
        insertSQL.setValue("sku", sku);
        insertSQL.setValue("cardno", cardno);
        insertSQL.setValue("reason", reason);
        insertSQL.setValue("warrantystatus", warrantystatus);
        sqlList.add(insertSQL.getSQL());

        dbConnect.runSqlUpdate("delete from sa_serviceorderitems where siteid='" + siteid + "' and sa_serviceorderid=" + sa_serviceorderid);
        dbConnect.runSqlUpdate(sqlList);


        content.put("sa_serviceorderid", sa_serviceorderid);

        return queryserviceorderMain();
    }

    @API(title = "更新是否补贴", apiversion = R.ID2025121517474303.v1.class)
    public String cheangeisallowance() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        boolean isallowance = content.getBooleanValue("isallowance");
        dbConnect.runSqlUpdate("update sa_serviceorder set isallowance="+isallowance+" where sa_serviceorderid="+sa_serviceorderid);
        return getSucReturnObject().toString();
    }


    /**
     * 服务分类查询
     **/
    @API(title = "服务分类查询", apiversion = R.ID20230206112003.v1.class)
    public String servicetypeList() throws YosException {
        Rows rows = new Rows();
        Row row1 = new Row();
        row1.put("value", "维修");
        Row row2 = new Row();
        row2.put("value", "安装");
        Row row3 = new Row();
        row3.put("value", "清洗");
        rows.add(row1);
        rows.add(row2);
        rows.add(row3);

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

    }


    @API(title = "可选择序列号列表查询", apiversion = R.ID2025082210142103.v1.class)
    @CACHEING
    public String queryskuList() throws YosException {

        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLong("sys_enterpriseid");
        }



        /*
         * 过滤条件设置
         */
        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.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.sku like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("sku") && !"".equals(whereObject.getString("sku"))) {
                where.append(" and(");
                where.append("t1.sku ='").append(whereObject.getString("sku")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("isvoid") && !"".equals(whereObject.getString("isvoid"))) {
                where.append(" and(");
                where.append("t1.isvoid ='").append(whereObject.getString("isvoid")).append("' ");
                where.append(")");
            }
            if (whereObject.containsKey("isinstalled") && !"".equals(whereObject.getString("isinstalled"))) {
                where.append(" and(");
                where.append(" case when ifnull(t1.cardno,'')='' then '未安装' else '已安装' end ='").append(whereObject.getString("isinstalled")).append("' ");
                where.append(")");
            }
        }

        SQLFactory sqlFactory = new SQLFactory(this, "序列号查询");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter",  "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'","*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        // 默认商品图片
        Rows defaultImageRows = beans.Item.Item.getItemdefaultImage(this);
        // 附件
        ArrayList<Long> ids = rows.toArrayList("itemid", new ArrayList<>());
        RowsMap attRowsMap = getAttachmentUrl("plm_item", ids);

        RowsMap warrantycardRowsMap = SQLFactory.createQuerySQL(this, "sa_warrantycard", "sku","name", "phonenumber","address","sa_customersid","province","city","county").setWhere("sku", rows.toArrayList("sku")).query().toRowsMap("sku");

        for (Row row : rows) {
            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 sku = row.getString("sku");
            if (warrantycardRowsMap.containsKey(sku) && warrantycardRowsMap.get(sku).isNotEmpty()) {
                Row warrantycardRow = warrantycardRowsMap.get(sku).get(0);
                row.put("name", warrantycardRow.getString("name"));
                row.put("phonenumber", warrantycardRow.getString("phonenumber"));
                row.put("address", warrantycardRow.getString("address"));
                row.put("sa_customersid", warrantycardRow.getLong("sa_customersid"));
                row.put("province", warrantycardRow.getString("province"));
                row.put("city", warrantycardRow.getString("city"));
                row.put("county", warrantycardRow.getString("county"));
            }else{
                row.put("name", "");
                row.put("phonenumber", "");
                row.put("address","");
                row.put("sa_customersid", "");
                row.put("province", "");
                row.put("city", "");
                row.put("county", "");
            }

        }

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

    @API(title = "可选择订单列表查询", apiversion = R.ID20230206091503.v1.class)
    @CACHEING
    public String queryCheckOrderList() throws YosException {

        if (content.containsKey("sys_enterpriseid")) {
            sys_enterpriseid = content.getLong("sys_enterpriseid");
        }


        /*
         * 过滤条件设置
         */
        StringBuffer where = new StringBuffer(" 1=1 ");
//		if(content.getBooleanValue("istool")) {
//			where.append(" and t1.type ='工具借用单' ");
//		}else {
//			where.append(" and t1.type !='工具借用单' ");
//		}
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("sonum") && !"".equals(whereObject.getString("sonum"))) {
                where.append(" and t1.sonum ='").append(whereObject.getString("sonum")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.checkdate >='").append(whereObject.getString("begindate")).append("' ");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.checkdate <='").append(whereObject.getString("enddate")).append("' ");
            }
        }
//		String hrid = content.getString("hrid");
        SQLFactory sqlFactory = new SQLFactory(this, "审核订单列表查询");
        sqlFactory.addParameter_SQL("where", where);
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'", "*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();

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

    @API(title = "服务申请单详情", apiversion = R.ID20230206091603.v1.class)
    @CACHEING
    public String queryserviceorderMain() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单详情查询");
        sqlFactory.addParameter("sa_serviceorderid", sa_serviceorderid);
        sqlFactory.addParameter("siteid", siteid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        ArrayList<Long> ids = rows.toArrayList("sa_serviceorderid", new ArrayList<>());
        HashMap<Long, ArrayList<String>> tagRows = DataTag.queryTag(this, "sa_serviceorder", ids, true);
        for (Row row : rows) {
            if (tagRows.get(row.getLong("sa_serviceorderid")) != null) {
                row.put("tag", tagRows.get(row.getLong("sa_serviceorderid")));
            } else {
                row.put("tag", new ArrayList<String>());
            }

        }

        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
//		if(!row.isEmpty()) {
//			//服务申请单关联物料查询
//			SQLFactory sqlFactorytitem = new SQLFactory(this, "服务申请单关联物料查询");
//			sqlFactorytitem.addParameter("sa_serviceorderid", sa_serviceorderid);
//			sqlFactorytitem.addParameter("siteid", siteid);
//			row.put("titems",dbConnect.runSqlQuery(sqlFactorytitem)); 
//			//服务申请单关联工单查询
//			SQLFactory sqlFactoryworkorder = new SQLFactory(this, "服务申请单关联工单查询");
//			sqlFactoryworkorder.addParameter("sa_serviceorderid", sa_serviceorderid);
//			sqlFactoryworkorder.addParameter("siteid", siteid);
//			row.put("workorder",dbConnect.runSqlQuery(sqlFactoryworkorder));  	
//		}
        Rows backreasonRows = dbConnect.runSqlQuery("select backreason from sys_dataextend where ownertable='sa_serviceorder' and siteid='" + siteid + "' and  backreason  is not null and backreason <>'' and ownerid=" + sa_serviceorderid + " order by changedate desc");
        if (!backreasonRows.isEmpty()) {
            row.put("backreason", backreasonRows.get(0).getString("backreason"));
        } else {
            row.put("backreason", "");
        }


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

    @API(title = "服务申请单详情（不验证token））", apiversion = R.ID20230217133003.v1.class, accesstoken = false)
    public String queryserviceorderMainWithoutToken() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单详情查询2");
        sqlFactory.addParameter("sa_serviceorderid", sa_serviceorderid);
        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        Row row = rows.isNotEmpty() ? rows.get(0) : new Row();
        if (!row.isEmpty()) {
            //服务申请单关联物料查询
            SQLFactory sqlFactorytitem = new SQLFactory(this, "服务申请单关联物料查询");
            sqlFactorytitem.addParameter("sa_serviceorderid", sa_serviceorderid);
            row.put("titems", dbConnect.runSqlQuery(sqlFactorytitem));
            //服务申请单关联工单查询
            SQLFactory sqlFactoryworkorder = new SQLFactory(this, "服务申请单关联工单查询");
            sqlFactoryworkorder.addParameter("sa_serviceorderid", sa_serviceorderid);
            row.put("workorder", dbConnect.runSqlQuery(sqlFactoryworkorder));
        }
        Rows backreasonRows = dbConnect.runSqlQuery("select backreason from sys_dataextend where ownertable='sa_serviceorder' and  backreason  is not null and backreason <>'' and ownerid=" + sa_serviceorderid + " order by changedate desc");
        if (!backreasonRows.isEmpty()) {
            row.put("backreason", backreasonRows.get(0).getString("backreason"));
        } else {
            row.put("backreason", "");
        }

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

    @API(title = "服务申请单关联工单查询", apiversion = R.ID20230207154203.v1.class)
    @CACHEING
    public String queryserviceorderWorkorderList() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        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.billno like'%").append(whereObject.getString("condition")).append("%' ");
//				where.append("or t3.sonum like'%").append(whereObject.getString("condition")).append("%' ");
//				where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
//				where.append("or t1.reason like'%").append(whereObject.getString("condition")).append("%' ");
//				where.append(")");
//			}
//			if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
//				where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
//			}
//		}
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单关联工单查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("sa_serviceorderid", sa_serviceorderid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'", "*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "查询服务申请单列表", apiversion = R.ID20230206091703.v1.class)
    @CACHEING
    public String queryserviceorderList() 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.servicetype like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.reason like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.class1 like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("class1") && !"".equals(whereObject.getString("class1"))) {
                where.append(" and t1.class1 ='").append(whereObject.getString("class1")).append("' ");
            }
            if (whereObject.containsKey("servicetype") && !"".equals(whereObject.getString("servicetype"))) {
                where.append(" and t1.servicetype ='").append(whereObject.getString("servicetype")).append("' ");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.submitdate >='").append(whereObject.getString("begindate")).append("'");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.submitdate <='").append(whereObject.getString("enddate")).append("'");
            }
            if (whereObject.containsKey("submitdate") && !"".equals(whereObject.getString("submitdate"))) {
                where.append(" and t1.submitdate >='").append(whereObject.getString("submitdate")).append("'");
                where.append(" and t1.submitdate <='").append(getDate_Str(whereObject.getDate("submitdate"), 1)).append("'");
            }

            if (whereObject.containsKey("phonenumber") && !"".equals(whereObject.getString("phonenumber"))) {
                where.append(" and t1.scenecontactphonenumber ='").append(whereObject.getString("phonenumber")).append("'");
            }
            if (whereObject.containsKey("customerphonenumber") && !"".equals(whereObject.getString("customerphonenumber"))) {
                where.append(" and t1.phonenumber ='").append(whereObject.getString("customerphonenumber")).append("'");
            }
            if (whereObject.containsKey("sa_customersid") && !"".equals(whereObject.getString("sa_customersid"))) {
                where.append(" and t1.sa_customersid ='").append(whereObject.getString("sa_customersid")).append("'");
            }
            if (whereObject.containsKey("isonlinesales") && !"".equals(whereObject.getString("isonlinesales"))) {
                where.append(" and t1.isonlinesales ='").append(whereObject.getString("isonlinesales")).append("' ");
            }
            if (whereObject.containsKey("createuserid") && !"".equals(whereObject.getString("createuserid"))) {
                where.append(" and t1.createuserid ='").append(whereObject.getString("createuserid")).append("'");
            }

            if (whereObject.getBooleanValue("todaysubmitCount")) {
                where.append(" and t1.status != '新建' and DATE(t1.submitdate) = CURDATE() ");
            }
            if (whereObject.getBooleanValue("todayazsubmitCount")) {
                where.append(" and t1.status != '新建' and t10.type='安装' and DATE(t1.submitdate) = CURDATE() ");
            }
            if (whereObject.getBooleanValue("todaywxsubmitCount")) {
                where.append(" and t1.status != '新建' and t10.type='维修' and DATE(t1.submitdate) = CURDATE() ");
            }
            if (whereObject.getBooleanValue("todayqxsubmitCount")) {
                where.append(" and t1.status != '新建' and t10.type='清洗' and DATE(t1.submitdate) = CURDATE() ");
            }
            if (whereObject.getBooleanValue("tobeassignedCount")) {
                where.append(" and t1.status = '待分配' ");
            }
            if (whereObject.getBooleanValue("tobeassignedCount24")) {
                where.append(" and t1.status = '待分配' and t1.submitdate < DATE_SUB(NOW(), INTERVAL 24 HOUR) ");
            }
            if (whereObject.getBooleanValue("pendingCount")) {
                where.append(" and t1.status = '待受理' ");
            }
            if (whereObject.getBooleanValue("pendingCount24")) {
                where.append(" and t1.status = '待受理' and t1.allocationdate < DATE_SUB(NOW(), INTERVAL 24 HOUR) ");
            }
            if (whereObject.getBooleanValue("refuseacceptCount")) {
                where.append(" and t1.status = '拒绝受理' ");
            }
            if (whereObject.getBooleanValue("tobeservedCount")) {
                where.append(" and t1.status = '待服务' ");
            }
            if (whereObject.getBooleanValue("inserviceCount")) {
                where.append(" and t1.status = '服务中' ");
            }

        }
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单列表查询");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'", "*");
        querySQL.setPage(pageSize, pageNumber);

        Rows rows = querySQL.query();




        ArrayList<Long> ids = rows.toArrayList("sa_serviceorderid", new ArrayList<>());
        HashMap<Long, ArrayList<String>> tagRows = DataTag.queryTag(this, "sa_serviceorder", ids, true);
        for (Row row : rows) {
            if (tagRows.get(row.getLong("sa_serviceorderid")) != null) {
                row.put("tag", tagRows.get(row.getLong("sa_serviceorderid")));
            } else {
                row.put("tag", new ArrayList<String>());
            }

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

    @API(title = "服务申请单状态统计", apiversion = R.ID2025111809442603.v1.class)
    public String queryserviceorderStatusList() throws YosException {

        SQLFactory sqlFactory2 = new SQLFactory(this, "服务申请单状态统计");
        sqlFactory2.addParameter("siteid", siteid);
        sqlFactory2.addParameter_SQL("where", " 1=1 ");
        Rows rows_total = dbConnect.runSqlQuery(sqlFactory2.getSQL());

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


    @API(title = "查询服务申请单列表（不验证token）", apiversion = R.ID20230217100703.v1.class, accesstoken = false)
    public String queryserviceorderListWithoutToken() throws YosException {
        StringBuffer where = new StringBuffer(" 1=1 ");
        boolean isadmin = content.getBooleanValue("isadmin");
        if (!isadmin) {
            if (usertype == 1) {
                ArrayList<Long> sa_saleareaidsList = new ArrayList<>();
                sa_saleareaidsList.addAll(userInfo.getSaleAreaIds());
                sa_saleareaidsList.addAll(SaleArea.getSubSaleAreaIds(this, userInfo.getSaleAreaIds()));

                String where2 = " and t1.sys_enterpriseid in (select sys_enterpriseid from sys_enterprise_tradefield where siteid='" + siteid + "' and sa_saleareaid in " + sa_saleareaidsList + ")";
                where2 = where2.replace("[", "(").replace("]", ")");
                where.append(where2);
            } else if (usertype == 21 || usertype == 22) {
                where.append(" and t1.sys_enterpriseid=" + sys_enterpriseid + " ");
            }
        }
        if (content.containsKey("where")) {
            JSONObject whereObject = content.getJSONObject("where");
            if (whereObject.containsKey("condition") && !"".equals(whereObject.getString("condition"))) {
                where.append(" and(");
                where.append("t1.billno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t3.sonum like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.enterprisename like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.reason like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
            if (whereObject.containsKey("status") && !"".equals(whereObject.getString("status"))) {
                where.append(" and t1.status ='").append(whereObject.getString("status")).append("' ");
            }
            if (whereObject.containsKey("begindate") && !"".equals(whereObject.getString("begindate"))) {
                where.append(" and t1.submitdate >='").append(whereObject.getString("begindate")).append("'");
            }
            if (whereObject.containsKey("enddate") && !"".equals(whereObject.getString("enddate"))) {
                where.append(" and t1.submitdate <='").append(whereObject.getString("enddate")).append("'");
            }
            if (whereObject.containsKey("phonenumber") && !"".equals(whereObject.getString("phonenumber"))) {
                where.append(" and t1.scenecontactphonenumber ='").append(whereObject.getString("phonenumber")).append("'");
            }
        }
        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单列表查询2");
        //sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sys_site_parameter", "sys_site_parameterid");
        querySQL.setTableAlias("t0");
        querySQL.addJoinTable(JOINTYPE.right, sqlFactory, "t1", "t0.siteid='111'", "*");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }


    @API(title = "工单模板查询", apiversion = R.ID20230206155803.v1.class)
    @CACHEING
    public String queryworkerordertemplate() throws YosException {

        Rows rows = dbConnect.runSqlQuery(
                "select sa_workorder_templateid,name,type from sa_workorder_template where siteid='" + siteid + "' and isused=1");
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "服务申请单转工单", apiversion = R.ID20230207091003.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230206091603.v1.class, R.ID20230206091703.v1.class, R.ID20230206101303.v1.class, R.ID20230208140103.v1.class, R.ID20230208140203.v1.class,
            R.ID20230209091103.v1.class, R.ID20230207154203.v1.class, R.ID20230217100703.v1.class, R.ID20230217133003.v1.class})
    public String serviceorderToWorkorder() throws YosException {

        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        String type = content.getString("type");
        JSONArray projectlearders = content.getJSONArray("projectlearders");
        Long sa_workorder_templateid = content.getLongValue("sa_workorder_templateid");
        Rows rows = dbConnect
                .runSqlQuery("select * from sa_serviceorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");

        Rows workorderRows = dbConnect
                .runSqlQuery("select sa_workorderid,status,billno,sa_serviceorderid from sa_workorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("此服务单不存在").toString();
        } else {
            if (!rows.get(0).getString("status").equals("待受理")) {
                return getErrReturnObject().setErrMsg("非待受理状态无法生成工单").toString();
            }
            Rows serviceorderitems = dbConnect.runSqlQuery("select sku from sa_serviceorderitems where sa_serviceorderid=" + sa_serviceorderid);
            if (serviceorderitems.isNotEmpty() && type.equals("安装")) {
                if (StringUtils.isNotBlank(serviceorderitems.get(0).getString("sku"))) {
                    if (dbConnect.runSqlQuery("select * from sa_warrantycard where sku='" + serviceorderitems.get(0).getString("sku") + "' and siteid='" + siteid + "' and isvoid=0").isNotEmpty()) {
                        return getErrReturnObject().setErrMsg("该序列号已安装,无法再次安装").toString();
                    }
                }

            }

        }

        if (!workorderRows.isEmpty()) {
            for (Row row : workorderRows) {
                if (row.getString("type").equals(type) && !row.getString("status").equals("作废")) {
                    return getErrReturnObject().setErrMsg("已存在" + type + "类型的工单，无法继续生成此类型的工单").toString();
                }
            }

        }

        ArrayList<String> sqlList = new ArrayList<>();
        long sa_workorderid = createTableID("sa_workorder");
        SQLFactory sqlFactory = new SQLFactory(this, "服务工单新增");
        String billno = createBillCode("workorder");
        sqlFactory.addParameter("siteid", siteid);
        sqlFactory.addParameter("type", type);
        sqlFactory.addParameter("sa_serviceorderid", sa_serviceorderid);
        sqlFactory.addParameter("billno", billno);
        sqlFactory.addParameter("sys_enterpriseid", rows.get(0).getString("sys_enterpriseid"));
        sqlFactory.addParameter("sa_workorderid", sa_workorderid);
        sqlFactory.addParameter("reason", rows.get(0).getString("reason"));
        sqlFactory.addParameter("address", rows.get(0).getString("address"));
        sqlFactory.addParameter("isouritem", 1);
        sqlFactory.addParameter("startdate", StringUtils.isBlank(rows.get(0).getString("begdate")) ? (StringUtils.isBlank(rows.get(0).getString("billdate")) ? "NULL" : rows.get(0).getString("billdate")) : rows.get(0).getString("begdate"));
        sqlFactory.addParameter("scenecontact", rows.get(0).getString("scenecontact"));
        sqlFactory.addParameter("scenecontactrole", rows.get(0).getString("scenecontactrole"));
        sqlFactory.addParameter("scenecontactphonenumber", rows.get(0).getString("scenecontactphonenumber"));
        sqlFactory.addParameter("remarks", rows.get(0).getString("remarks"));
        sqlFactory.addParameter("status", "待接单");
        sqlFactory.addParameter("userid", userid);
        sqlFactory.addParameter("username", username);
        sqlFactory.addParameter("sa_workorder_templateid", sa_workorder_templateid);

        SQLFactory factory = new SQLFactory(this, "查询订单收货日期");
        factory.addParameter("sa_orderid", rows.get(0).getLong("sa_orderid"));
        factory.addParameter("siteid", siteid);
        Rows rowsreceive = dbConnect.runSqlQuery(factory.getSQL());
        if (!rowsreceive.isEmpty()) {
            //System.out.println(888);
            Date receivedate = rowsreceive.get(0).getDate("receivedate");
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.YEAR, -2);
            Date date = calendar.getTime();
            if (date.before(receivedate)) {
                sqlFactory.addParameter("inqualityguaranteeperiod", 1);
            } else {
                sqlFactory.addParameter("inqualityguaranteeperiod", 0);
            }
        } else {
            sqlFactory.addParameter("inqualityguaranteeperiod", 0);
        }

        sqlList.add("delete from sa_workorder_node where sa_workorderid=" + sa_workorderid);
        sqlList.addAll(getAddWorkorderNodeSql(sa_workorder_templateid, sa_workorderid));


        sqlList.add(sqlFactory.getSQL());
        sqlList.add("update sa_serviceorder set status='待服务' where sa_serviceorderid=" + sa_serviceorderid);
        if (projectlearders != null && !projectlearders.isEmpty()) {
            sqlList.add("delete from sys_datateam where ownertable='sa_workorder' and ownerid="
                    + sa_workorderid + " and siteid='" + siteid + "'");
            for (Object obj : projectlearders) {
                sqlList.addAll(DataTeam.createTeamSQL(this, "sa_workorder", sa_workorderid, (int) obj));
            }
        }

        dbConnect.runSqlUpdate(sqlList);
        //发送短信
        Rows serviceorderRows = dbConnect.runSqlQuery("select t2.enterprisename,t1.billno,t1.submitby,t1.remarks from sa_serviceorder t1 left join sys_enterprise t2 on t1.sys_enterpriseid=t2.sys_enterpriseid and t1.siteid=t2.siteid where t1.sa_serviceorderid ='" + sa_serviceorderid + "' and  t1.siteid='" + siteid + "'");

        for (Object obj : projectlearders) {
            int projectlearder = (int) obj;
            Rows workersRows = dbConnect.runSqlQuery("select  t3.phonenumber from sys_hr t1 left join sys_users t3 on t1.userid = t3.userid left join sys_hr t4 on t1.siteid = t4.siteid and t1.reporthrid = t4.hrid where t1.siteid = '" + siteid + "' and t1.isworker=1  and t1.userid=" + projectlearder);
            if (!workersRows.isEmpty()) {
                if (StringUtils.isNotBlank(workersRows.get(0).getString("phonenumber"))) {
                    Sms sms = new Sms();
                    sms.sendout(Sms.SmsType.Notice, siteid, workersRows.get(0).getString("phonenumber"), "工单号：" + billno + "已指派请处理，客户名称：" + (StringUtils.isBlank(serviceorderRows.get(0).getString("enterprisename")) ? "" : serviceorderRows.get(0).getString("enterprisename"))
                            + ",提交人：" + serviceorderRows.get(0).getString("submitby") + "，备注：" + serviceorderRows.get(0).getString("remarks"));
                }
            }

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

    @API(title = "重新指派工单", apiversion = R.ID2025101710510903.v1.class)
    @CACHEING_CLEAN(apiversions = {serviceorder.class, workorder.class, restcontroller.sale.serviceorder.serviceorder.class})
    public String reAssigningWorkorder() throws YosException {
        Long sa_workorderid = content.getLong("sa_workorderid");
        JSONArray projectlearders = content.getJSONArray("projectlearders");
        boolean ismanage = content.getBooleanValue("ismanage");
        ArrayList<String> sqlList = new ArrayList<>();
        Rows rows = dbConnect
                .runSqlQuery("select status,billno from sa_workorder where sa_workorderid ='"
                        + sa_workorderid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (row.getString("status").equals("已完工")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的工单为已完工状态，无法重新指派工单")
                        .toString();
            }
        }
        if (!ismanage) {
            Rows datateamrows = dbConnect.runSqlQuery("select * from sys_datateam where ownertable='sa_workorder' and ownerid="
                    + sa_workorderid + " and siteid='" + siteid + "'");
            if (datateamrows.isNotEmpty()) {
                return getErrReturnObject().setErrMsg("该任务已由其他同事负责推进").toString();
            }
        }

        if (rows.get(0).getString("status").equals("提交")) {
            sqlList.add("update sa_workorder set status='进行中' where sa_workorderid ='"
                    + sa_workorderid + "' and  siteid='" + siteid + "'");
        } else {
            sqlList.add("update sa_workorder set status='待接单' where sa_workorderid ='"
                    + sa_workorderid + "' and  siteid='" + siteid + "'");
        }

        sqlList.add("delete from sys_datateam where ownertable='sa_workorder' and ownerid="
                + sa_workorderid + " and siteid='" + siteid + "'");
        if (projectlearders != null && !projectlearders.isEmpty()) {
            for (Object obj : projectlearders) {
                sqlList.addAll(DataTeam.createTeamSQL(this, "sa_workorder", sa_workorderid, (int) obj));
            }
        }
        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "删除", apiversion = R.ID20230206091803.v1.class)
    @CACHEING_CLEAN(apiversions = {R.ID20230206091603.v1.class, R.ID20230206091703.v1.class, R.ID20230206101303.v1.class, R.ID20230217100703.v1.class, R.ID20230217133003.v1.class})
    public String delete() throws YosException {

        JSONArray sa_serviceorderids = content.getJSONArray("sa_serviceorderids");
        BatchDeleteErr batchDeleteErr = BatchDeleteErr.create(this, sa_serviceorderids.size());
        for (Object o : sa_serviceorderids) {
            long sa_serviceorderid = Long.parseLong(o.toString());
            Rows RowsStatus = dbConnect
                    .runSqlQuery("select t3.sa_warrantycardid from sa_serviceorder t1 left join sa_workorder t2 on t1.sa_serviceorderid=t2.sa_serviceorderid left join sa_warrantycard t3 on t3.sa_workorderid=t2.sa_workorderid  where t1.siteid='" + siteid
                            + "' and t1.sa_serviceorderid='" + sa_serviceorderid + "' and ifnull(t3.cardno,'')!=''");

            if (RowsStatus.isNotEmpty()) {
                if (RowsStatus.isNotEmpty()) {
                    batchDeleteErr.addErr(sa_serviceorderid, "服务申请单已生成保修卡，不允许删除");
                    continue;
                }
            }



            dbConnect.runSqlUpdate("delete from sa_serviceorder where siteid='" + siteid + "' and sa_serviceorderid="
                    + sa_serviceorderid);
            dbConnect.runSqlUpdate("delete from sa_workorder where siteid='" + siteid + "' and sa_serviceorderid="
                    + sa_serviceorderid);
        }
        return batchDeleteErr.getReturnObject().toString();

    }

    @API(title = "作废服务申请单", apiversion = R.ID2025121519311203.v1.class)
    @CACHEING_CLEAN(apiClass = {workorder.class, serviceorder.class, warrantycard.class})
    public String cancelServiceOrder() throws YosException {
        long sa_serviceorderid = content.getLong("sa_serviceorderid");
        String voidreason =content.getStringValue("voidreason");

        Rows userrows = dbConnect.runSqlQuery("select t2.usertype from  sys_users t1 inner join sys_usersite t2 on t1.userid = t2.userid where t2.siteid = '" + siteid + "' and t1.userid=" + userid);
        long usertype = 0;
        if (userrows.isNotEmpty()) {
            usertype = userrows.get(0).getLong("usertype");
        }

        ArrayList<String> sqllist = new ArrayList<>();
        Rows workorderrows = dbConnect.runSqlQuery("select sa_workorderid from sa_workorder where sa_serviceorderid ='"
                + sa_serviceorderid + "' and  siteid='" + siteid + "' and status!='作废'");
        sqllist.add("update sa_serviceorder set voidreason='"+voidreason+"',status='作废' where sa_serviceorderid="+sa_serviceorderid);
        sqllist.add(DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "作废", "服务申请单作废成功")
                .getSQL());
        if(workorderrows.isNotEmpty()){
            long sa_workorderid= workorderrows.get(0).getLong("sa_workorderid");
            voidreason="【服务申请单作废】"+voidreason;
            sqllist.add("update sa_workorder set voidreason='"+voidreason+"',status='作废' where sa_workorderid="+sa_workorderid);
            sqllist.add(DataContrlLog.createLog(this, "sa_workorder", sa_workorderid, "作废", "工单作废成功")
                    .getSQL());
            Rows warrantycardrows = dbConnect.runSqlQuery("select sa_warrantycardid from sa_warrantycard where sa_workorderid ='"
                    + sa_workorderid + "' and  siteid='" + siteid + "' and isvoid=0");
            if(warrantycardrows.isNotEmpty()){
                if (usertype != 0 && usertype!=1) {
                    return getErrReturnObject().setErrMsg("因存在生效中的保修卡，此操作暂时无法执行，请先联系总部完成保修卡作废流程。").toString();
                }
                sqllist.add("update sa_warrantycard set voiddate=CURRENT_TIME,voidreason='"+voidreason+"',isvoid=1 where sa_warrantycardid="+warrantycardrows.get(0).getLong("sa_warrantycardid"));
                sqllist.add(DataContrlLog.createLog(this, "sa_warrantycard", warrantycardrows.get(0).getLong("sa_warrantycardid"), "作废", "保修卡作废成功")
                        .getSQL());
            }

        }

        dbConnect.runSqlUpdate(sqllist);

        return getSucReturnObject().toString();
    }

    @API(title = "撤回分配服务申请单", apiversion = R.ID2025121818251203.v1.class)
    @CACHEING_CLEAN(apiClass = {workorder.class, serviceorder.class, warrantycard.class})
    public String chehuiServiceOrder() throws YosException {
        long sa_serviceorderid = content.getLong("sa_serviceorderid");
        ArrayList<String> sqllist = new ArrayList<>();

        Rows serviceorderRows = dbConnect.runSqlQuery("select status,billno from sa_serviceorder where sa_serviceorderid ='"
                + sa_serviceorderid + "' and  siteid='" + siteid + "'");
        if(serviceorderRows.isNotEmpty()){
            if(!(serviceorderRows.get(0).getString("status").equals("待受理") || serviceorderRows.get(0).getString("status").equals("待服务")  || serviceorderRows.get(0).getString("status").equals("服务中"))){
                return getErrReturnObject().setErrMsg("非待受理/待服务/服务中状态的服务申请单无法撤回分配").toString();
            }
            sqllist.add("update sa_serviceorder set sys_enterpriseid_service=0,status='待分配',allocationdate=null,allocationby='' where sa_serviceorderid="+sa_serviceorderid);
            sqllist.add(DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "撤回分配", "服务申请单撤回分配成功")
                    .getSQL());
            Rows workorderrows = dbConnect.runSqlQuery("select sa_workorderid from sa_workorder where sa_serviceorderid ='"
                    + sa_serviceorderid + "' and  siteid='" + siteid + "' and status!='作废'");
            if(workorderrows.isNotEmpty()){
                long sa_workorderid= workorderrows.get(0).getLong("sa_workorderid");
                sqllist.add("update sa_workorder set voidreason='服务申请单撤回分配',status='作废' where sa_workorderid="+sa_workorderid);
                sqllist.add(DataContrlLog.createLog(this, "sa_workorder", sa_workorderid, "作废", "工单作废成功")
                        .getSQL());
            }

        }else{
            return getErrReturnObject().setErrMsg("服务申请单不存在").toString();
        }

        dbConnect.runSqlUpdate(sqllist);

        return getSucReturnObject().toString();
    }



    @API(title = "发送手机验证码", apiversion = R.ID20230215173103.v1.class, accesstoken = false)
    public String sendMessage() throws YosException {
        String customerphone = "";
        long sa_workorderid = content.getLong("sa_workorderid");
        Rows rows = dbConnect.runSqlQuery("select t1.type,t1.verificationcode,t1.verificationdate,t2.* from sa_workorder t1 inner join sa_serviceorder t2 on t1.sa_serviceorderid=t2.sa_serviceorderid and t1.siteid=t2.siteid where t1.sa_workorderid=" + sa_workorderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该工单不存在").toString();
        }
        long createuserid = rows.get(0).getLong("createuserid");
        String type = rows.get(0).getString("type");
        Rows userrows = dbConnect.runSqlQuery("select t2.usertype,t1.phonenumber from  sys_users t1 inner join sys_usersite t2 on t1.userid = t2.userid where t2.siteid = '" + siteid + "' and t1.userid=" + createuserid);
        long usertype = 0;
        if (userrows.isNotEmpty()) {
            usertype = userrows.get(0).getLong("usertype");
        } else {
            //return getErrReturnObject().setErrMsg("工单创建人信息有误").toString();
        }
        if (usertype == 99) {
            customerphone = userrows.get(0).getString("phonenumber");
        } else {
            if (type.equals("安装")) {
                customerphone = rows.get(0).getString("scenecontactphonenumber");
            }
        }
        String verificationCode = "";
        if (StringUtils.isBlank(rows.get(0).getString("verificationcode"))) {
            verificationCode = createVerificationCode();
        } else {
            verificationCode = rows.get(0).getString("verificationcode");
        }

        dbConnect.runSqlUpdate("update sa_workorder set verificationcode='" + verificationCode + "' where sa_workorderid=" + sa_workorderid);
        Rows wxserviceRows = dbConnect.runSqlQuery("select t2.userid from sys_wechatapp_openids t1 inner join sys_users t2 on t1.userid=t2.userid where t2.phonenumber='" + customerphone + "'");
        Row row = new Row();
        if (wxserviceRows.isEmpty()) {
            ArrayList<Long> userids = wxserviceRows.toArrayList("userid", new ArrayList<>());
            JSONObject extradata = new JSONObject();
            extradata.put("extraUserList", userids);
            Executor.sendEml(this, "verificationcode_push", sa_workorderid, siteid, extradata);
            if (!BaseClass.isdebug() && !Arrays.asList(Parameter.getString("system_invalidate_phonenumber").split(",")).contains(customerphone)) {
                row.put("code", 1);
                row.put("msg", "验证码已发送，请注意查收！");
            }
        } else {
            if (!BaseClass.isdebug() && !Arrays.asList(Parameter.getString("system_invalidate_phonenumber").split(",")).contains(customerphone)) {
                if (StringUtils.isNotBlank("customerphone")) {
                    if (StringUtils.isNotBlank(rows.get(0).getString("verificationdate"))) {
                        if (isMoreThanOneHourUsingCalendar(rows.get(0).getDate("verificationdate"), new Date())) {
                            Sms sms = new Sms();
                            sms.sendout(Sms.SmsType.Notice, customerphone, "尊敬的用户您好，您的工单服务已完成，完工验证码是" + verificationCode + "。");
                            message_map.put(customerphone, new VerificationManage(verificationCode));
                            dbConnect.runSqlUpdate("update sa_workorder set verificationdate=CURRENT_TIME where sa_workorderid=" + sa_workorderid);
                        }
                    } else {
                        Sms sms = new Sms();
                        sms.sendout(Sms.SmsType.Notice, customerphone, "尊敬的用户您好，您的工单服务已完成，完工验证码是" + verificationCode + "。");
                        message_map.put(customerphone, new VerificationManage(verificationCode));
                        dbConnect.runSqlUpdate("update sa_workorder set verificationdate=CURRENT_TIME where sa_workorderid=" + sa_workorderid);
                    }

                }
                row.put("code", 1);
                row.put("msg", "手机验证码已发送，请注意查收！");
            } else {
                row.put("code", 1);
                row.put("msg", "手机验证码为:" + verificationCode);
            }
        }
        return getSucReturnObject().setData(row).toString();
    }

    /**
     * 验证码校验
     **/
    @API(title = "验证码校验", apiversion = R.ID20230215173203.v1.class, accesstoken = false)
    public String checkVerificationCode() throws YosException {
        String customerphone = "";
        long sa_workorderid = content.getLong("sa_workorderid");
        Rows rows = dbConnect.runSqlQuery("select t1.type,t2.* from sa_workorder t1 inner join sa_serviceorder t2 on t1.sa_serviceorderid=t2.sa_serviceorderid and t1.siteid=t2.siteid where t1.sa_workorderid=" + sa_workorderid);
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该工单不存在").toString();
        }
        long createuserid = rows.get(0).getLong("createuserid");
        String type = rows.get(0).getString("type");
        Rows userrows = dbConnect.runSqlQuery("select t2.usertype,t1.phonenumber from  sys_users t1 inner join sys_usersite t2 on t1.userid = t2.userid where t2.siteid = '" + siteid + "' and t1.userid=" + createuserid);
        long usertype = 0;
        if (userrows.isNotEmpty()) {
            usertype = userrows.get(0).getLong("usertype");
        } else {
            //return getErrReturnObject().setErrMsg("工单创建人信息有误").toString();
        }

        if (usertype == 99) {
            customerphone = userrows.get(0).getString("phonenumber");
        } else {
            if (type.equals("安装")) {
                customerphone = rows.get(0).getString("scenecontactphonenumber");
            }
        }
        String code = content.getStringValue("code");
        boolean isgeneralcompletioncode = Parameter.getBoolean(siteid, "isgeneralcompletioncode");
        String generalcompletioncode = Parameter.getString(siteid, "generalcompletioncode");
        if (isgeneralcompletioncode) {
            if (!code.equals(generalcompletioncode)) {
                if (!message_map.containsKey(customerphone) || !message_map.get(customerphone).check(code)) {
                    return getErrReturnObject().setErrMsg("验证码错误或者过期").toString();
                }
            }
        } else {
            if (!message_map.containsKey(customerphone) || !message_map.get(customerphone).check(code)) {
                return getErrReturnObject().setErrMsg("验证码错误或者过期").toString();
            }
        }
        return getSucReturnObject().toString();
    }

    private ArrayList<String> getAddWorkorderNodeSql(long sa_workorder_templateid, long sa_workorderid)
            throws YosException {
        ArrayList sqList = new ArrayList<String>();
        Rows rows = dbConnect.runSqlQuery(
                "select  t1.sa_workorder_template_worksid,t1.sequence,t1.parentid,t2.* from sa_workorder_template_works t1 inner join sa_workpreset t2 on t1.siteid=t2.siteid and t1.sa_workpresetid=t2.sa_workpresetid  where t1.siteid='"
                        + siteid + "' and t1.sa_workorder_templateid =" + sa_workorder_templateid + " order by t1.sequence asc");
        RowsMap rowsMap = rows.toRowsMap("parentid");
        if (!rowsMap.get(0).isEmpty()) {
            for (Row row : rowsMap.get(0)) {
                workpresetInfo workpresetInfo = new workpresetInfo();
                workpresetInfo.setWorkname(row.getString("workname"));
                workpresetInfo.setRemarks(row.getString("remarks"));
                workpresetInfo.setAdditem(row.getLong("additem"));
                workpresetInfo.setAddperson(row.getLong("addperson"));
                workpresetInfo.setAmountpay(row.getLong("amountpay"));
                workpresetInfo.setConfirm(row.getLong("confirm"));
                workpresetInfo.setConfirm_options(JSONArray.parseArray(row.getString("confirm_options")));
                workpresetInfo.setContractupload(row.getLong("contractupload"));
                workpresetInfo.setFileupload(row.getLong("fileupload"));
                workpresetInfo.setItemtype(row.getString("itemtype"));
                workpresetInfo.setRequired(row.getLong("required"));
                workpresetInfo.setTextedit(row.getLong("textedit"));
                workpresetInfo.setSignature(row.getLong("signature"));
                workpresetInfo.setPasscheck(row.getLong("passcheck"));
                workpresetInfo.setQuestionedit(row.getLong("questionedit"));
                workpresetInfo.setQuestionoption(row.getString("questionoption"));
                String workpresetjson = JSONObject.toJSONString(workpresetInfo);
                SQLFactory sqlFactory = new SQLFactory(this, "服务工单流程节点新增");
                long sa_workorder_nodeid = createTableID("sa_workorder_node");
                sqlFactory.addParameter("sa_workorder_nodeid", sa_workorder_nodeid);
                sqlFactory.addParameter("sa_workorderid", sa_workorderid);
                sqlFactory.addParameter("siteid", siteid);
                sqlFactory.addParameter("parentid", 0);
                sqlFactory.addParameter("workpresetjson", workpresetjson);
                sqlFactory.addParameter("sequence", row.getLong("sequence"));
                sqlFactory.addParameter("userid", userid);
                sqlFactory.addParameter("username", username);
                sqList.add(sqlFactory.getSQL());
                if (!rowsMap.get(row.getLong("sa_workorder_template_worksid")).isEmpty()) {
                    for (Row row2 : rowsMap.get(row.getLong("sa_workorder_template_worksid"))) {
                        workpresetInfo workpresetInfo1 = new workpresetInfo();
                        workpresetInfo1.setWorkname(row2.getString("workname"));
                        workpresetInfo1.setRemarks(row2.getString("remarks"));
                        workpresetInfo1.setAdditem(row2.getLong("additem"));
                        workpresetInfo1.setAddperson(row2.getLong("addperson"));
                        workpresetInfo1.setAmountpay(row2.getLong("amountpay"));
                        workpresetInfo1.setConfirm(row2.getLong("confirm"));
                        workpresetInfo1.setConfirm_options(JSONArray.parseArray(row2.getString("confirm_options")));
                        workpresetInfo1.setContractupload(row2.getLong("contractupload"));
                        workpresetInfo1.setFileupload(row2.getLong("fileupload"));
                        workpresetInfo1.setItemtype(row2.getString("itemtype"));
                        workpresetInfo1.setRequired(row2.getLong("required"));
                        workpresetInfo1.setTextedit(row2.getLong("textedit"));
                        workpresetInfo1.setSignature(row2.getLong("signature"));
                        workpresetInfo1.setPasscheck(row2.getLong("passcheck"));
                        workpresetInfo1.setQuestionedit(row2.getLong("questionedit"));
                        workpresetInfo1.setQuestionoption(row2.getString("questionoption"));
                        String workpresetjson1 = JSONObject.toJSONString(workpresetInfo1);
                        SQLFactory sqlFactory1 = new SQLFactory(this, "服务工单流程节点新增");
                        sqlFactory1.addParameter("sa_workorder_nodeid", createTableID("sa_workorder_node"));
                        sqlFactory1.addParameter("sa_workorderid", sa_workorderid);
                        sqlFactory1.addParameter("siteid", siteid);
                        sqlFactory1.addParameter("parentid", sa_workorder_nodeid);
                        sqlFactory1.addParameter("workpresetjson", workpresetjson1);
                        sqlFactory1.addParameter("sequence", row2.getLong("sequence"));
                        sqlFactory1.addParameter("userid", userid);
                        sqlFactory1.addParameter("username", username);
                        sqList.add(sqlFactory1.getSQL());
                    }
                }
            }
        }
        return sqList;

    }

//    private String sendMessage_0(String phone, String verificationCode) throws Exception {
//        HttpURLRest rest = new HttpURLRest("http://smssh1.253.com/msg/v1/send/json");
//        rest.addHeader("Content-type", "applica分配tion/json");
//        JSONObject jsonObject = new JSONObject();
//        jsonObject.put("account", "N3147701");
//        jsonObject.put("password", "4ClGyab6fv6a87");
//        jsonObject.put("msg", "【浙江美大】您好，您的验证码是" + verificationCode);
//        jsonObject.put("phone", phone);
//        rest.addRawBody(jsonObject.toString());
//        String s = rest.post();

    /// /        PaoSetRemote verCodehistory = getpaoset("VerCodehistory");
    /// /        PaoRemote remote = verCodehistory.addAtEnd();
    /// /        remote.setValue("phone",phone);
    /// /        remote.setValue("msg","【Banninger】您好，您的验证码是"+verificationCode);
    /// /        remote.setValue("content",s);
    /// /        verCodehistory.save();
//        return s;
//    }
    public static String createVerificationCode() {
        String s = "";
        while (s.length() < 6)
            s += (int) (Math.random() * 10);
        return s;
    }

    /**
     * 方法2：使用 Calendar 计算是否超过一小时
     */
    public boolean isMoreThanOneHourUsingCalendar(Date date1, Date date2) {
        Calendar cal1 = Calendar.getInstance();
        Calendar cal2 = Calendar.getInstance();
        cal1.setTime(date1);
        cal2.setTime(date2);

        // 计算相差的毫秒数
        long diffInMillis = Math.abs(cal2.getTimeInMillis() - cal1.getTimeInMillis());
        return diffInMillis >= 3600000L;
    }

}