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.datateam.DataTeam;
import beans.salearea.SaleArea;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
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 restcontroller.R;
import restcontroller.webmanage.sale.serviceorder.tools.HttpURLRest;
import restcontroller.webmanage.sale.serviceorder.tools.VerificationManage;
import restcontroller.webmanage.sale.workorder.workpresetInfo;
import utility.sms.Sms;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
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 (content.containsKey("sys_enterpriseid")) {
            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");
        long sa_customersid = content.getLongValue("sa_customersid");

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

        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);
        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 DATE_FORMAT(t1.submitdate , '%Y-%m-%d')='").append(whereObject.getString("submitdate")).append("'");
            }

            if (whereObject.containsKey("phonenumber") && !"".equals(whereObject.getString("phonenumber"))) {
                where.append(" and t1.scenecontactphonenumber ='").append(whereObject.getString("phonenumber")).append("'");
            }
        }
        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();
        return getSucReturnObject().setData(rows).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 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();
            }
        }

        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.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 = content.getStringValue("customerphone");
        if (!Pattern.matches("^1([358][0-9]|4[579]|66|7[0135678]|9[89])[0-9]{8}$", customerphone)) {
            return getErrReturnObject().setErrMsg("手机格式有误").toString();
        }
        Rows rows = dbConnect.runSqlQuery("select scenecontactphonenumber from sa_serviceorder where scenecontactphonenumber='" + customerphone + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("您的手机号暂无使用权限！").toString();
        }
        String verificationCode = createVerificationCode();
        String result = null;
        try {
            result = sendMessage_0(customerphone, verificationCode);
        } catch (Exception e) {
            return getErrReturnObject().setErrMsg(e.getMessage()).toString();
        }
        message_map.put(customerphone, new VerificationManage(verificationCode));
        Row row = new Row();
        row.put("code", verificationCode);
        row.put("msg", result);
        return getSucReturnObject().setData(row).toString();
    }

    /**
     * 验证码校验
     **/
    @API(title = "发送手机验证码", apiversion = R.ID20230215173203.v1.class, accesstoken = false)
    public String checkVerificationCode() throws YosException {
        String customerphone = content.getStringValue("customerphone");
        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(row.getLong("passcheck"));
                        workpresetInfo1.setQuestionedit(row.getLong("questionedit"));
                        workpresetInfo1.setQuestionoption(row.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", "application/json");
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("account", "YZM1357742");
        jsonObject.put("password", "cBLqr5kRMVc541");
        jsonObject.put("msg", "【Banninger】您好，您的验证码是" + 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;
    }

}