CUCU2/src/custom/restcontroller/webmanage/saletool/store/StoreHelper.java

package restcontroller.webmanage.saletool.store;

import beans.parameter.Parameter;
import common.BaseClass;
import common.Controller;
import common.YosException;
import common.data.*;

import java.util.ArrayList;

public class StoreHelper extends BaseClass {

    public StoreHelper() {
    }

    //查询门店
    public static RowsMap getRoleRowsMap(Controller controller, String[] ids) throws YosException {
        QuerySQL roleQuery = SQLFactory.createQuerySQL(controller, "sys_userrole"
                , "userid").setTableAlias("t1");
        roleQuery.addJoinTable(JOINTYPE.left, "sys_role", "t2", "t2.roleid=t1.roleid and t2.siteid=t1.siteid"
                , "rolename", "roleid");
        roleQuery.setWhere("userid", ids);
        return roleQuery.query().toRowsMap("userid");
    }

    //查询门店
    public static RowsMap getStoreRowsMap(Controller controller, String[] ids) throws YosException {
        QuerySQL storeQuery = SQLFactory.createQuerySQL(controller, "sa_store"
                , "leader_hrid", "storename","sa_storeid").setTableAlias("t1");
        storeQuery.addJoinTable(JOINTYPE.inner, "sa_store_hr", "t2", "t2.sa_storeid=t1.sa_storeid and t2.siteid=t1.siteid", "sys_enterprise_hrid");
        storeQuery.setWhere("t2.sys_enterprise_hrid", ids);
        return storeQuery.query().toRowsMap("sys_enterprise_hrid");
    }

    public static Long getUserid(Controller controller, String phonenumber) throws YosException {
        Rows rows = controller.dbConnect.runSqlQuery("select * from sys_users where phonenumber='" + phonenumber + "'");
        if (rows.isNotEmpty()) {
            return rows.get(0).getLong("userid");
        }
        return 0L;
    }

    public static Long getUserid(Controller controller, Long sys_enterprise_hrid) throws YosException {
        Rows rows = controller.dbConnect.runSqlQuery("SELECT * from sys_enterprise_hr WHERE sys_enterprise_hrid=" + sys_enterprise_hrid + " and siteid='" + controller.siteid + "'");
        if (rows.isNotEmpty()) {
            return rows.get(0).getLong("userid");
        }
        return 0L;
    }

    public static InsertSQL getRoleInsertSQL(Controller controller, Long userid, Long roleid) throws YosException {
        InsertSQL adduserrole = SQLFactory.createInsertSQL(controller, "sys_userrole");
        adduserrole.setValue("userid", userid);
        adduserrole.setValue("userroleid", controller.createTableID("sys_userrole"));
        adduserrole.setValue("roleid", roleid);
        adduserrole.setValue("siteid", controller.siteid);
        adduserrole.setWhere("not exists (select * from sys_userrole where userid=" + userid + " and roleid=" + roleid + ")");
        return adduserrole;
    }

    public static InsertSQL getStoreHrInsertSQL(Controller controller, Long sys_enterprise_hrid, Long sa_storeid) throws YosException {
        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, "sa_store_hr");
        insertSQL.setSiteid(controller.siteid);
        insertSQL.setUniqueid(controller.createTableID("sa_store_hr"));
        insertSQL.setValue("sys_enterpriseid", controller.sys_enterpriseid);
        insertSQL.setValue("sa_storeid", sa_storeid);
        insertSQL.setValue("sys_enterprise_hrid", sys_enterprise_hrid);
        insertSQL.setWhere("not exists(select 1 from sa_store_hr where sys_enterprise_hrid=" + sys_enterprise_hrid + " and sa_storeid=" + sa_storeid + " and siteid='" + controller.siteid + "')");
        return insertSQL;
    }

    //改为经销商员工类型
    public void updateUserType22(Controller controller) throws YosException {
        updateUserType(controller, 22);

        ArrayList<Long> deleteroles = dbConnect.runSqlQuery("SELECT visitor_roleid from sys_wechatapp WHERE (systemclient='"
                + controller.systemclient + "' or systemclient_auth='" + controller.systemclient + "' ) and  siteid='" + controller.siteid + "'  and isvisitor=1").toArrayList("visitor_roleid", new ArrayList<>());

        if (deleteroles.size() > 0) {
            deleteUserRole(controller, deleteroles);
        }

        Rows rows = dbConnect.runSqlQuery("select * from sys_role where rolename='" + Parameter.get("storerolename") + "' and siteid='" + controller.siteid + "'");
        if (rows.isNotEmpty()) {
            insertUserRole(controller, rows.get(0).getLong("roleid"));
        }
    }

    public void updateUserType1(Controller controller) throws YosException {
        updateUserType(controller, 1);

        ArrayList<Long> deleteroles = dbConnect.runSqlQuery("SELECT visitor_roleid from sys_wechatapp WHERE (systemclient='"
                + controller.systemclient + "' or systemclient_auth='" + controller.systemclient + "' ) and  siteid='" + controller.siteid + "'  and isvisitor=1").toArrayList("visitor_roleid", new ArrayList<>());

        if (deleteroles.size() > 0) {
            deleteUserRole(controller, deleteroles);
        }

        Rows rows = dbConnect.runSqlQuery("select * from sys_role where issystem=1 and usertype=1 and siteid='" + controller.siteid + "'");
        if (rows.isNotEmpty()) {
            insertUserRole(controller, rows.get(0).getLong("roleid"));
        }
    }

    public void updateUserType99(Controller controller) throws YosException {
        updateUserType(controller, 99);

        ArrayList<Long> deleteroles = dbConnect.runSqlQuery("select * from sys_role where rolename='" + Parameter.get("storerolename") + "' and siteid='" + controller.siteid + "'").toArrayList("roleid", new ArrayList<>());

        if (deleteroles.size() > 0) {
            deleteUserRole(controller, deleteroles);
        }
        Rows rows = dbConnect.runSqlQuery("SELECT visitor_roleid from sys_wechatapp WHERE (systemclient='" + controller.systemclient + "' or systemclient_auth='" + controller.systemclient + "' ) and  siteid='" + controller.siteid + "'  and isvisitor=1");

        if (rows.isNotEmpty()) {
            insertUserRole(controller, rows.get(0).getLong("visitor_roleid"));
        }
    }


    public void updateUserType(Controller controller, int usertype) throws YosException {
        UpdateSQL updateSQL = SQLFactory.createUpdateSQL(controller, "sys_usersite");
        updateSQL.setSiteid(controller.siteid);
        updateSQL.setWhere("userid", controller.userid);
        updateSQL.setValue("usertype", usertype);
        updateSQL.update();
    }

    public void deleteUserRole(Controller controller, ArrayList<Long> deleteroles) throws YosException {
        DeleteSQL deleteSQL = SQLFactory.createDeleteSQL(controller, "sys_userrole");
        deleteSQL.setSiteid(controller.siteid);
        deleteSQL.setWhere("userid", controller.userid);
        deleteSQL.setWhere("roleid", deleteroles);
        deleteSQL.delete();
    }

    public void insertUserRole(Controller controller, Long roleid) throws YosException {
        InsertSQL insertSQL = SQLFactory.createInsertSQL(controller, "sys_userrole");
        insertSQL.setSiteid(controller.siteid);
        insertSQL.setUniqueid(createTableID("sys_userrole"));
        insertSQL.setValue("userid", controller.userid);
        insertSQL.setValue("roleid", roleid);
        insertSQL.insert();
    }

    public static Rows queryStores(Controller controller, String longitude, String latitude) throws YosException {
        Rows temprows = new Rows();
        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_store").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_hr", "t2", "t2.sys_enterprise_hrid=t1.leader_hrid and t2.siteid=t1.siteid"
                , "name", "phonenumber", "userid");
        querySQL.addQueryFields("distance", "st_distance_sphere(point(longitude,latitude),point('" + longitude + "','" + latitude + "'))");
        querySQL.setWhere("status", "审核");
        querySQL.setOrderBy("distance");
        Rows rows = querySQL.query();
        if (rows.isNotEmpty()) {
            temprows.add(rows.get(0));
        }
        return temprows;
    }

    public static Rows queryStores(Controller controller, Long sa_storeid, String longitude, String latitude) throws YosException {
        Rows temprows = new Rows();
        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_store").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_hr", "t2", "t2.sys_enterprise_hrid=t1.leader_hrid and t2.siteid=t1.siteid"
                , "name", "phonenumber", "userid");
        querySQL.addQueryFields("distance", "st_distance_sphere(point(longitude,latitude),point('" + longitude + "','" + latitude + "'))");
        querySQL.setWhere("status", "审核");
        querySQL.setWhere("sa_storeid", sa_storeid);
        querySQL.setOrderBy("distance");
        Rows rows = querySQL.query();
        if (rows.isNotEmpty()) {
            temprows.add(rows.get(0));
        }
        return temprows;
    }

    //查询经销商的门店信息
    public static Rows queryAgentsStores(Controller controller, long sys_enterprise_hrid, String longitude, String latitude) throws YosException {
        QuerySQL querySQL = SQLFactory.createQuerySQL(controller, "sa_store").setTableAlias("t1");
        querySQL.addJoinTable(JOINTYPE.left, "sys_enterprise_hr", "t2", "t2.sys_enterprise_hrid=t1.leader_hrid and t2.siteid=t1.siteid"
                , "name", "phonenumber", "userid");
        querySQL.addJoinTable(JOINTYPE.left, "sa_store_hr", "t3", "t3.sa_storeid=t1.sa_storeid and t3.siteid=t1.siteid");
        querySQL.addQueryFields("distance", "st_distance_sphere(point(longitude,latitude),point('" + longitude + "','" + latitude + "'))");
        querySQL.setWhere("t3.sys_enterprise_hrid", sys_enterprise_hrid);
        querySQL.setWhere("status", "审核");
        querySQL.setOrderBy("distance");
        return querySQL.query();
    }

}