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.data.*;

import org.apache.commons.lang.StringUtils;
import org.apache.cxf.configuration.security.DNConstraintsType;
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 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");

        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();
                }
            }
        }
        if (content.containsKey("phonenumber") && !"".equals(content.getString("phonenumber"))) {
            if (!Pattern.matches("^1([358][0-9]|4[579]|66|7[0135678]|9[89])[0-9]{8}$", content.getString("phonenumber"))) {
                return getErrReturnObject().setErrMsg("手机格式有误").toString();
            }
        }


        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("新建")) {
                    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"));

        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);
        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.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("t2.itemname like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t2.itemno like'%").append(whereObject.getString("condition")).append("%' ");
                where.append("or t1.sku like'%").append(whereObject.getString("condition")).append("%' ");
                where.append(")");
            }
        }
//		String hrid = content.getString("hrid");
//        SQLFactory sqlFactory = new SQLFactory(this, "审核订单列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter_SQL("where", where);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL(false));
        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_itemsku", "sku");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t2", "t1.itemid=t2.itemid and t1.siteid=t2.siteid", "itemid", "itemno", "itemname", "model", "spec");
        querySQL.addJoinTable(JOINTYPE.left, "plm_unit", "t3", "t3.unitid=t2.unitid and t3.siteid=t2.siteid", "unitname");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t4", "t4.sa_agentsid=t1.sa_agentsid and t4.siteid=t1.siteid", "agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t5", "t5.sys_enterpriseid=t4.sys_enterpriseid and t5.siteid=t4.siteid", "contact", "phonenumber", "address");
        querySQL.addJoinTable(JOINTYPE.left, "sa_warrantycard", "t6", "t6.sku=t1.sku and t6.siteid=t1.siteid", "cardno", "begdate", "enddate");
        querySQL.addJoinTable(JOINTYPE.left, "sa_customers", "t7", "t6.sa_customersid=t7.sa_customersid and t6.siteid=t7.siteid", "sa_customersid");
        querySQL.addQueryFields("customername", "t7.name");
        querySQL.addQueryFields("customerphonenumber", "t7.phonenumber");

        querySQL.setSiteid(siteid);
        querySQL.setWhere("t1.sa_agentsid in (select sa_agentsid from sa_agents where  sys_enterpriseid=" + sys_enterpriseid + ")");
        querySQL.setWhere(where.toString());
        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);

        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);
            }
        }

        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("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()){
                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 sa_serviceorderid,status from sa_serviceorder where siteid='" + siteid
                            + "' and sa_serviceorderid='" + sa_serviceorderid + "'");

            if (RowsStatus.isNotEmpty()) {
                if (!RowsStatus.get(0).getString("status").equals("新建")) {
                    //batchDeleteErr.addErr(sa_serviceorderid, "非新建状态的服务申请单无法删除");
                    continue;
                }
            }

            dbConnect.runSqlUpdate("delete from sa_serviceorder where siteid='" + siteid + "' and sa_serviceorderid="
                    + sa_serviceorderid);
        }
        return batchDeleteErr.getReturnObject().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");
            }
        }
        if(StringUtils.isNotBlank(customerphone)){
            if (!Pattern.matches("^1([358][0-9]|4[579]|66|7[0135678]|9[89])[0-9]{8}$", customerphone)) {
                return getErrReturnObject().setErrMsg("手机格式有误").toString();
            }
        }
        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");
        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;
    }
}