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

package restcontroller.sale.serviceorder;

import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
import com.alibaba.fastjson2.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.executorService.Executor;
import utility.sms.Sms;

import java.util.ArrayList;

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

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


    @API(title = "查询服务申请单列表", apiversion = R.ID20230206101303.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(getDate_Str(whereObject.getDate("enddate"), 1)).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("'");
            }
        }
//        SQLFactory sqlFactory = new SQLFactory(this, "服务申请单列表查询", pageSize, pageNumber, pageSorting);
//        sqlFactory.addParameter("sys_enterpriseid", sys_enterpriseid);
//        sqlFactory.addParameter("siteid", siteid);
//        sqlFactory.addParameter_SQL("where", where);
//        Rows rows = dbConnect.runSqlQuery(sqlFactory);

        QuerySQL querySQL = SQLFactory.createQuerySQL(this, "sa_serviceorder", "*");
        querySQL.setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t2", "t1.sys_enterpriseid = t2.sys_enterpriseid AND t1.siteid = t2.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t2.sys_enterpriseid = t3.sys_enterpriseid AND t2.siteid = t3.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t4", "t1.sys_enterpriseid_service = t4.sys_enterpriseid AND t1.siteid = t4.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t5", "t4.sys_enterpriseid = t5.sys_enterpriseid AND t4.siteid = t5.siteid");
        querySQL.addJoinTable(JOINTYPE.left, "(SELECT * FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY sa_serviceorderid ORDER BY sa_serviceorderitemsid ASC) AS rowno FROM sa_serviceorderitems) t WHERE rowno = 1)",
                "t8", "t8.sa_serviceorderid=t1.sa_serviceorderid and t8.siteid=t1.siteid", "itemid", "sku", "cardno");
        querySQL.addJoinTable(JOINTYPE.left, "plm_item", "t9", "t9.itemid=t8.itemid and t9.siteid=t8.siteid", "itemno", "itemname", "model", "spec");
        querySQL.addQueryFields("serviceenterprisename", "t4.enterprisename");
        querySQL.addQueryFields("serviceabbreviation", "t4.abbreviation");
        querySQL.addQueryFields("servicephonenumber", "t4.phonenumber");
        querySQL.addQueryFields("serviceagentnum", "t5.agentnum");

        querySQL.addQueryFields("jxs_enterprisename", "t2.enterprisename");
        querySQL.addQueryFields("jxs_abbreviation", "t2.abbreviation");
        querySQL.addQueryFields("jxs_phonenumber", "t2.phonenumber");
        querySQL.addQueryFields("jxsagentnum", "t3.agentnum");
        
        querySQL.setSiteid(siteid);
        querySQL.setWhere("(t1.sys_enterpriseid=" + sys_enterpriseid + " or t1.sys_enterpriseid_service=" + sys_enterpriseid + ")");
        querySQL.setWhere(where.toString());
        querySQL.setOrderBy("case when t1.status='新建' then 1 when t1.status='提交' then 2 when t1.status='待分配' then 3 when t1.status='待受理' then 4 when t1.status='拒绝受理' then 5 when t1.status='待服务' then 6  when t1.status='服务中' then 7 when t1.status='已完结' then 8 else 9 end,t1.createdate desc");
        querySQL.setPage(pageSize, pageNumber);
        Rows rows = querySQL.query();
        return getSucReturnObject().setData(rows).toString();
    }

    @API(title = "提交", apiversion = R.ID20230206101403.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 sumbit() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        boolean issumbit = true;
        Rows rows = dbConnect
                .runSqlQuery("select sa_serviceorderid,status,billno,sys_enterpriseid,province,city,county from sa_serviceorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (issumbit) {
                if (!row.getString("status").equals("新建")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的服务申请单为非新建状态，无法提交")
                            .toString();
                }
            } else {
                if (StringUtils.isBlank(content.getString("backreason"))) {
                    return getErrReturnObject().setErrMsg("退回原因为空不可退回").toString();
                }
                if (!row.getString("status").equals("待指派")) {
                    return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的服务申请单为非待指派状态，无法退回")
                            .toString();
                }
            }

        }
        ArrayList<String> sqlList = new ArrayList<>();
        SQLFactory sqlFactoryupdate;
        if (issumbit) {
            sqlFactoryupdate = new SQLFactory(this, "服务申请单提交");
            sqlList.add(
                    DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "提交", "服务申请单提交成功").getSQL());
        } else {
            sqlFactoryupdate = new SQLFactory(this, "服务申请单反提交");
            sqlList.add(
                    DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "反提交", "服务申请单退回成功").getSQL());
            // 退回原因
            ArrayList<Long> sa_serviceorderids = new ArrayList<>();
            sa_serviceorderids.add(sa_serviceorderid);
            DataExtend.createBackReasonSql(this, "sa_serviceorder", sa_serviceorderids,
                    content.getStringValue("backreason"));
        }
        sqlFactoryupdate.addParameter("siteid", siteid);
        sqlFactoryupdate.addParameter("sa_serviceorderid", sa_serviceorderid);
        sqlFactoryupdate.addParameter("submitby", username);
        sqlList.add(sqlFactoryupdate.getSQL());

        if (issumbit) {
            Rows agents = dbConnect.runSqlQuery("select * from sa_agents where status=1 and siteid='" + siteid + "' and sys_enterpriseid=" + rows.get(0).getLong("sys_enterpriseid"));
            if (agents.isNotEmpty()) {
                if (agents.get(0).getBoolean("isservice")) {
                    sqlList.add("update sa_serviceorder set status='待受理',sys_enterpriseid_service=" + agents.get(0).getLong("sys_enterpriseid") + " where sa_serviceorderid=" + sa_serviceorderid);
                } else {
                    Rows serviceagents = dbConnect.runSqlQuery("select t1.sys_enterpriseid,t2.province,t2.city,t2.county from sa_agents t1 inner join sa_agents_salescope  t2  on  t1.sa_agentsid=t2.sa_agentsid and t1.siteid=t2.siteid where t1.siteid='" + siteid + "' and t1.isservice=1");
                    RowsMap countyRowsMap = serviceagents.toRowsMap("county");
                    RowsMap cityRowsMap = serviceagents.toRowsMap("city");
                    RowsMap provinceRowsMap = serviceagents.toRowsMap("province");
                    if (countyRowsMap.containsKey(rows.get(0).getString("county"))) {
                        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
                        updateSQL.setUniqueid(sa_serviceorderid);
                        updateSQL.setSiteid(siteid);
                        updateSQL.setValue("status", "待受理");
                        updateSQL.setValue("sys_enterpriseid_service", countyRowsMap.get(rows.get(0).getString("county")).get(0).getLong("sys_enterpriseid"));
                        updateSQL.setDateValue("allocationdate");
                        updateSQL.setValue("allocationby", username);
                        sqlList.add(updateSQL.getSQL());
                        sqlList.add(
                                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "自动分配", "服务申请单自动分配成功").getSQL());
                    } else if (cityRowsMap.containsKey(rows.get(0).getString("city"))) {
                        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
                        updateSQL.setUniqueid(sa_serviceorderid);
                        updateSQL.setSiteid(siteid);
                        updateSQL.setValue("status", "待受理");
                        updateSQL.setValue("sys_enterpriseid_service", cityRowsMap.get(rows.get(0).getString("city")).get(0).getLong("sys_enterpriseid"));
                        updateSQL.setDateValue("allocationdate");
                        updateSQL.setValue("allocationby", username);
                        sqlList.add(updateSQL.getSQL());
                        sqlList.add(
                                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "自动分配", "服务申请单自动分配成功").getSQL());
                    } else if (provinceRowsMap.containsKey(rows.get(0).getString("province"))) {
                        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
                        updateSQL.setUniqueid(sa_serviceorderid);
                        updateSQL.setSiteid(siteid);
                        updateSQL.setValue("status", "待受理");
                        updateSQL.setValue("sys_enterpriseid_service", provinceRowsMap.get(rows.get(0).getString("province")).get(0).getLong("sys_enterpriseid"));
                        updateSQL.setDateValue("allocationdate");
                        updateSQL.setValue("allocationby", username);
                        sqlList.add(updateSQL.getSQL());
                        sqlList.add(
                                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "自动分配", "服务申请单自动分配成功").getSQL());
                    }
                }
            }
            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.isleader=1");
            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 + "'");
            if (!workersRows.isEmpty()) {
                for (Row row : workersRows) {
                    Sms sms = new Sms();
                    if (StringUtils.isNotBlank(row.getString("phonenumber"))) {
                        sms.sendout(Sms.SmsType.Notice, siteid, row.getString("phonenumber"),
                                "服务单号：" + serviceorderRows.get(0).getString("billno") + "已指派请处理，客户名称："
                                        + (StringUtils.isBlank(serviceorderRows.get(0).getString("enterprisename")) ? ""
                                        : serviceorderRows.get(0).getString("enterprisename"))
                                        + ",提交人：" + serviceorderRows.get(0).getString("submitby") + "，备注："
                                        + serviceorderRows.get(0).getString("remarks"));
                    }
                }
            }
            Executor.sendEml(this, "serviceordersub", sa_serviceorderid, siteid);
        }

        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

    @API(title = "手工分配服务商", apiversion = R.ID2025071710525303.v1.class)
    public String assignserviceagentbyhand() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        sys_enterpriseid = content.getLong("sys_enterpriseid");
        Rows rows = dbConnect
                .runSqlQuery("select sa_serviceorderid,status,billno,sys_enterpriseid from sa_serviceorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");
        if (rows.isEmpty()) {
            return getErrReturnObject().setErrMsg("该服务申请单不存在").toString();
        }
        if (!rows.get(0).getString("status").equals("待分配") && !rows.get(0).getString("status").equals("手工完结") && !rows.get(0).getString("status").equals("拒绝受理")) {
            return getErrReturnObject().setErrMsg("非待分配或手工完结或拒绝受理的服务申请单无法分配服务商").toString();
        }
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
        updateSQL.setUniqueid(sa_serviceorderid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", "待受理");
        updateSQL.setValue("sys_enterpriseid_service", sys_enterpriseid);
        updateSQL.setValue("refusereason", "");
        updateSQL.setValue("refuseremarks", "");
        updateSQL.setDateValue("allocationdate");
        updateSQL.setValue("allocationby", username);

        dbConnect.runSqlUpdate(updateSQL.getSQL());
        dbConnect.runSqlUpdate(
                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "手工分配", "服务申请单手工分配成功").getSQL());
        return getSucReturnObject().toString();
    }


    @API(title = "拒绝受理", apiversion = R.ID2025072409011703.v1.class)
    @CACHEING_CLEAN(apiversions = {serviceorder.class, restcontroller.webmanage.sale.serviceorder.serviceorder.class})
    public String refuse() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        String refusereason = content.getStringValue("refusereason");
        String refuseremarks = content.getStringValue("refuseremarks");
        Rows rows = dbConnect
                .runSqlQuery("select sa_serviceorderid,status,billno,sys_enterpriseid,province,city,county from sa_serviceorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");

        for (Row row : rows) {
            if (!row.getString("status").equals("待受理")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的服务申请单为非待受理状态，无法拒绝受理")
                        .toString();
            }
            if (StringUtils.isBlank(refusereason)) {
                return getErrReturnObject().setErrMsg("拒绝原因为空，无法拒绝受理")
                        .toString();
            }
        }
        ArrayList<String> sqlList = new ArrayList<>();

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
        updateSQL.setUniqueid(sa_serviceorderid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", "拒绝受理");
        updateSQL.setValue("refusereason", refusereason);
        updateSQL.setValue("refuseremarks", refuseremarks);

        updateSQL.setValue("changeby", username);
        updateSQL.setDateValue("changedate");
        sqlList.add(updateSQL.getSQL());
        sqlList.add(
                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "拒绝受理", "服务申请单拒绝受理成功").getSQL());


        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }


    @API(title = "完结服务单", apiversion = R.ID2025072409161803.v1.class)
    @CACHEING_CLEAN(apiversions = {serviceorder.class, restcontroller.webmanage.sale.serviceorder.serviceorder.class})
    public String wanjie() throws YosException {
        Long sa_serviceorderid = content.getLong("sa_serviceorderid");
        Rows rows = dbConnect
                .runSqlQuery("select sa_serviceorderid,status,billno,sys_enterpriseid,province,city,county from sa_serviceorder where sa_serviceorderid ='"
                        + sa_serviceorderid + "' and  siteid='" + siteid + "'");
        for (Row row : rows) {
            if (!row.getString("status").equals("待分配") && !row.getString("status").equals("拒绝受理") && !row.getString("status").equals("待受理")) {
                return getErrReturnObject().setErrMsg("单号为：【" + row.getString("billno") + "】的服务申请单为非待分配或拒绝受理或待受理状态，无法完结服务单")
                        .toString();
            }
            if (StringUtils.isBlank(content.getString("backreason"))) {
                return getErrReturnObject().setErrMsg("完结原因（拒绝原因）为空不可完结").toString();
            }
        }
        ArrayList<String> sqlList = new ArrayList<>();

        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(this, "sa_serviceorder");
        updateSQL.setUniqueid(sa_serviceorderid);
        updateSQL.setSiteid(siteid);
        updateSQL.setValue("status", "已完结");
        updateSQL.setValue("changeby", username);
        updateSQL.setDateValue("changedate");
        sqlList.add(updateSQL.getSQL());
        sqlList.add(
                DataContrlLog.createLog(this, "sa_serviceorder", sa_serviceorderid, "完结", "服务申请单完结成功").getSQL());

        // 退回原因
        ArrayList<Long> sa_serviceorderids = new ArrayList<>();
        sa_serviceorderids.add(sa_serviceorderid);
        DataExtend.createBackReasonSql(this, "sa_serviceorder", sa_serviceorderids,
                content.getStringValue("backreason"));

        dbConnect.runSqlUpdate(sqlList);
        return getSucReturnObject().toString();
    }

}