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

package restcontroller.sale.serviceorder;

import beans.datacontrllog.DataContrlLog;
import beans.dataextend.DataExtend;
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.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.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("' ");
            }
        }
//        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","enterprisename","abbreviation","phonenumber");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t3", "t2.sys_enterpriseid = t3.sys_enterpriseid AND t2.siteid = t3.siteid","agentnum");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise", "t4", "t1.sys_enterpriseid_service = t4.sys_enterpriseid AND t1.siteid = t4.siteid","enterprisename","abbreviation","phonenumber");
        querySQL.addJoinTable(JOINTYPE.left, "sa_agents", "t5", "t4.sys_enterpriseid = t5.sys_enterpriseid AND t4.siteid = t5.siteid","agentnum");
        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.setSiteid(siteid);
        querySQL.setWhere("(t1.sys_enterpriseid="+sys_enterpriseid+" or t1.sys_enterpriseid_service="+sys_enterpriseid+")");
        querySQL.setWhere(where.toString());
        querySQL.setPage(pageSize, pageNumber);
        querySQL.setOrderBy(pageSorting);
        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 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+"'");
                     System.out.println(serviceagents.get(0).getString("city"));
                     System.out.println(rows.get(0).getString("city"));
                     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());
                     }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());
                     }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());
                     }
                 }
             }
            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("待分配")){
            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.setDateValue("allocationdate");
        updateSQL.setValue("allocationby", username);

        dbConnect.runSqlUpdate(updateSQL.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");
        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();
            }
        }
        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());


        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("拒绝受理")) {
                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();
    }

}