Examples with SqlQuery cn.cerc.jdb.mysql.SqlQuery used on opensource projects

Example 21 with SqlQuery

use of cn.cerc.jdb.mysql.SqlQuery in project summer-bean by cn-cerc.

the class AppSessionRestore method byToken.

public boolean byToken() throws ServiceException {
    Record headIn = getDataIn().getHead();
    DataValidateException.stopRun("token不允许为空", !headIn.hasValue("token"));
    String token = headIn.getString("token");
    SqlQuery cdsCurrent = new SqlQuery(this);
    cdsCurrent.add("select CorpNo_,UserID_,LoginTime_,Account_ as UserCode_,Language_ ");
    cdsCurrent.add("from %s", SystemTable.get(SystemTable.getCurrentUser));
    cdsCurrent.add("where loginID_= '%s' ", token);
    cdsCurrent.open();
    if (cdsCurrent.eof()) {
        log.warn(String.format("token %s 没有找到！", token));
        CustomHandle sess = (CustomHandle) this.getProperty(null);
        sess.setProperty(Application.token, null);
        return false;
    }
    String userId = cdsCurrent.getString("UserID_");
    SqlQuery cdsUser = new SqlQuery(this);
    cdsUser.add("select ID_,Code_,DiyRole_,RoleCode_,CorpNo_, Name_ as UserName_,ProxyUsers_");
    cdsUser.add("from %s", SystemTable.get(SystemTable.getUserInfo), userId);
    cdsUser.add("where ID_='%s'", userId);
    cdsUser.open();
    if (cdsUser.eof()) {
        log.warn(String.format("userId %s 没有找到！", userId));
        CustomHandle sess = (CustomHandle) this.getProperty(null);
        sess.setProperty(Application.token, null);
        return false;
    }
    Record headOut = getDataOut().getHead();
    headOut.setField("LoginTime_", cdsCurrent.getDateTime("LoginTime_"));
    headOut.setField("Language_", cdsCurrent.getString("Language_"));
    copyData(cdsUser, headOut);
    return true;
}

Example 22 with SqlQuery

use of cn.cerc.jdb.mysql.SqlQuery in project summer-bean by cn-cerc.

the class SvrBookOption method execute.

@Override
public IStatus execute(DataSet dataIn, DataSet dataOut) throws ServiceException {
    Record head = dataIn.getHead();
    SqlQuery ds = new SqlQuery(this);
    ds.add("select Value_ from %s ", SystemTable.get(SystemTable.getBookOptions));
    ds.add("where CorpNo_ = '%s' and Code_ = '%s'", this.getCorpNo(), head.getString("Code_"));
    ds.open();
    dataOut.appendDataSet(ds);
    return success();
}

Example 23 with SqlQuery

use of cn.cerc.jdb.mysql.SqlQuery in project summer-mis by cn-cerc.

the class SvrUserLogin method verifyMachine.

// 若返回值为 true，表示已校验，否则表示需要进行认证
public boolean verifyMachine() throws SecurityCheckException, DataValidateException {
    Record headIn = getDataIn().getHead();
    DataValidateException.stopRun(R.asString(this, "设备ID不允许为空"), !headIn.hasValue("deviceId"));
    String deviceId = headIn.getString("deviceId");
    // 校验帐号的可用状态
    SqlQuery cdsUser = new SqlQuery(this);
    cdsUser.add("select * from %s ", SystemTable.get(SystemTable.getUserInfo));
    cdsUser.add("where Code_='%s' ", getUserCode());
    cdsUser.open();
    DataValidateException.stopRun(String.format(R.asString(this, "没有找到用户帐号 %s"), getUserCode()), cdsUser.eof());
    DataValidateException.stopRun(R.asString(this, "您现登录的帐号已被停止使用，请您联系客服启用后再重新登录"), cdsUser.getInt("Enabled_") < 1);
    // 校验设备码的可用状态
    SqlQuery cdsVer = new SqlQuery(this);
    cdsVer.add("select * from %s", SystemTable.get(SystemTable.getDeviceVerify));
    cdsVer.add("where UserCode_='%s' and MachineCode_='%s'", getUserCode(), deviceId);
    cdsVer.open();
    DataValidateException.stopRun(String.format(R.asString(this, "系统出错(id=%s)，请您重新进入系统"), deviceId), cdsVer.eof());
    if (cdsVer.getInt("Used_") == 1) {
        return true;
    }
    // 未通过则需要检查验证码
    DataValidateException.stopRun(R.asString(this, "验证码不允许为空"), !headIn.hasValue("verifyCode"));
    String verifyCode = headIn.getString("verifyCode");
    if (cdsVer.getInt("Used_") == 2) {
        throw new SecurityCheckException(R.asString(this, "您正在使用的这台设备，被管理员设置为禁止登入系统！"));
    }
    // 更新认证码
    if (!verifyCode.equals(cdsVer.getString("VerifyCode_"))) {
        updateVerifyCode(cdsVer, verifyCode, cdsUser);
    }
    cdsVer.edit();
    cdsVer.setField("Used_", 1);
    cdsVer.setField("FirstTime_", TDateTime.Now());
    cdsVer.post();
    cdsUser.edit();
    cdsUser.setField("VerifyTimes_", 0);
    cdsUser.post();
    return true;
}

Example 24 with SqlQuery

use of cn.cerc.jdb.mysql.SqlQuery in project summer-mis by cn-cerc.

the class SvrUserLogin method updateVerifyCode.

private void updateVerifyCode(SqlQuery dataVer, String verifyCode, SqlQuery cdsUser) {
    SqlQuery cdsVer = new SqlQuery(this);
    cdsVer.add("select * from %s", SystemTable.get(SystemTable.getDeviceVerify));
    cdsVer.add("where VerifyCode_='%s'", verifyCode);
    cdsVer.open();
    if (cdsVer.eof()) {
        cdsUser.edit();
        // 停用帐号
        if (cdsUser.getInt("VerifyTimes_") == 6) {
            cdsUser.setField("Enabled_", 0);
            cdsUser.post();
            throw new RuntimeException(R.asString(this, "您输入验证码的错误次数已超出规定次数，现账号已被自动停用，若需启用，请您联系客服处理"));
        } else {
            cdsUser.setField("VerifyTimes_", cdsUser.getInt("VerifyTimes_") + 1);
            cdsUser.post();
            throw new RuntimeException(String.format(R.asString(this, "没有找到验证码 %s"), verifyCode));
        }
    }
    String machineCode = cdsVer.getString("MachineCode_");
    if (machineCode == null || "".equals(machineCode)) {
        // 先将此验证码的认证记录删除
        cdsVer.delete();
        // 再将该认证码替换掉之前自动生成的认证码
        dataVer.edit();
        dataVer.setField("VerifyCode_", verifyCode);
        dataVer.post();
    } else {
        throw new RuntimeException("您输入的验证码有误，请重新输入！");
    }
}

Example 25 with SqlQuery

use of cn.cerc.jdb.mysql.SqlQuery in project summer-mis by cn-cerc.

the class SvrUserLogin method Check.

/*
     * 用户登录入口
     */
@Webfunc
public boolean Check() throws SecurityCheckException {
    Record headIn = getDataIn().getHead();
    getDataOut().getHead().setField("errorNo", 0);
    String deviceId = headIn.getString("MachineID_");
    // 判断是否为浏览器登陆
    if (Application.webclient.equals(deviceId)) {
        throw new SecurityCheckException("系统不支持使用web浏览器登录，请使用客户端登录系统！");
    }
    String device_name = "";
    if (headIn.hasValue("ClientName_")) {
        device_name = headIn.getString("ClientName_");
    } else {
        device_name = "unknow";
    }
    CustomHandle sess = (CustomHandle) this.getProperty(null);
    if (headIn.exists("ClientIP_")) {
        sess.setProperty(Application.clientIP, headIn.getString("ClientIP_"));
    } else {
        sess.setProperty(Application.clientIP, "0.0.0.0");
    }
    // 开始进行用户验证
    String userCode = headIn.getString("Account_");
    if (userCode.equals("")) {
        throw new SecurityCheckException("用户帐号不允许为空！");
    }
    SqlQuery dsUser = new SqlQuery(this);
    dsUser.add("select UID_,CorpNo_,ID_,Code_,Name_,Mobile_,DeptCode_,Enabled_,Password_,BelongAccount_,");
    dsUser.add("VerifyTimes_,Encrypt_,SecurityLevel_,SecurityMachine_,PCMachine1_,PCMachine2_,");
    dsUser.add("PCMachine3_,RoleCode_,DiyRole_ from %s where Code_='%s'", SystemTable.get(SystemTable.getUserInfo), userCode);
    dsUser.open();
    if (dsUser.eof()) {
        throw new SecurityCheckException(String.format("该帐号(%s)并不存在，禁止登录！", userCode));
    }
    String corpNo = dsUser.getString("CorpNo_");
    BookInfoRecord buff = MemoryBookInfo.get(this, corpNo);
    if (buff == null) {
        throw new SecurityCheckException(String.format("没有找到注册的帐套  %s ", corpNo));
    }
    boolean YGLogin = buff.getCorpType() == BookVersion.ctFree.ordinal();
    if (buff.getStatus() == 3) {
        throw new SecurityCheckException("对不起，您的账套处于暂停录入状态，禁止登录！若需启用，请您联系客服处理！");
    }
    if (buff.getStatus() == 4) {
        throw new SecurityCheckException("对不起，您的帐套已过期，请联系客服续费！");
    }
    if (dsUser.getInt("Enabled_") < 1 && dsUser.getInt("VerifyTimes_") == 6) {
        throw new SecurityCheckException(String.format("该帐号(%s)因输入错误密码或验证码次数达到6次，已被自动停用，禁止登录！若需启用，请您联系客服处理！", userCode));
    }
    if (dsUser.getInt("Enabled_") < 1) {
        throw new SecurityCheckException(String.format("该帐号(%s)被暂停使用，禁止登录！若需启用，请您联系客服处理！", userCode));
    }
    // 判断此帐号是否为附属帐号
    if (dsUser.getString("BelongAccount_") != null && !"".equals(dsUser.getString("BelongAccount_"))) {
        throw new SecurityCheckException(String.format("该帐号已被设置为附属帐号，不允许登录，请使用主帐号 %s 登录系统！", dsUser.getString("BelongAccount_")));
    }
    // 取得认证密码，若是微信入口进入，则免密码录入
    String password = headIn.getString("Password_");
    if (password == null || "".equals(password)) {
        if ("".equals(dsUser.getString("Mobile_"))) {
            throw new RuntimeException("您没有登记手机号，请您输入密码进行登陆！");
        } else {
            getDataOut().getHead().setField("Mobile_", dsUser.getString("Mobile_"));
            throw new RuntimeException("用户密码不允许为空！");
        }
    }
    // 检查设备码
    enrollMachineInfo(dsUser.getString("CorpNo_"), userCode, deviceId, device_name);
    if (dsUser.getBoolean("Encrypt_")) {
        if (!headIn.exists("wx") && !"000000".equals(password)) {
            password = MD5.get(dsUser.getString("Code_") + password);
        }
    }
    if (!isAutoLogin(userCode, deviceId) && !"000000".equals(password)) {
        if (!dsUser.getString("Password_").equals(password)) {
            dsUser.edit();
            if (dsUser.getInt("VerifyTimes_") == 6) {
                // 该账号设置停用
                dsUser.setField("Enabled_", 0);
                dsUser.post();
                throw new RuntimeException("您输入密码的错误次数已超出规定次数，现账号已被自动停用，若需启用，请您联系客服处理！");
            } else {
                dsUser.setField("VerifyTimes_", dsUser.getInt("VerifyTimes_") + 1);
                dsUser.post();
                if (dsUser.getInt("VerifyTimes_") > 3) {
                    throw new SecurityCheckException(String.format("您输入密码的错误次数已达 %d 次，输错超过6次时，您的账号将被自动停用！", dsUser.getInt("VerifyTimes_")));
                } else {
                    throw new SecurityCheckException("您的登录密码错误，禁止登录！");
                }
            }
        }
    }
    // 当前设备是否已被停用
    if (!isStopUsed(userCode, deviceId)) {
        throw new SecurityCheckException("您的当前设备已被停用，禁止登录，请联系管理员恢复启用！");
    }
    try (Transaction tx = new Transaction(this)) {
        String sql = String.format("update %s set LastTime_=now() where UserCode_='%s' and MachineCode_='%s' and Used_=1", SystemTable.get(SystemTable.getDeviceVerify), userCode, deviceId);
        getConnection().execute(sql);
        // 若该账套是待安装，则改为已启用
        SqlQuery dsCorp = new SqlQuery(this);
        dsCorp.add("select * from %s ", SystemTable.get(SystemTable.getBookInfo));
        dsCorp.add("where CorpNo_='%s' and Status_=1 ", corpNo);
        dsCorp.open();
        if (!dsCorp.eof()) {
            dsCorp.edit();
            dsCorp.setField("Status_", 2);
            dsCorp.post();
            MemoryBookInfo.clear(corpNo);
        }
        sess.setProperty(Application.token, GuidFixStr(newGuid()));
        sess.setProperty(Application.userId, dsUser.getString("ID_"));
        sess.setProperty(Application.bookNo, dsUser.getString("CorpNo_"));
        sess.setProperty(Application.userCode, dsUser.getString("Code_"));
        if (dsUser.getBoolean("DiyRole_")) {
            sess.setProperty(Application.roleCode, dsUser.getString("Code_"));
        } else {
            sess.setProperty(Application.roleCode, dsUser.getString("RoleCode_"));
        }
        // 更新当前用户总数
        updateCurrentUser(device_name, headIn.getString("Screen_"), headIn.getString("Language_"));
        try (MemoryBuffer Buff = new MemoryBuffer(BufferType.getSessionInfo, (String) getProperty(Application.userId), deviceId)) {
            Buff.setField("UserID_", getProperty(Application.userId));
            Buff.setField("UserCode_", getUserCode());
            Buff.setField("UserName_", getUserName());
            Buff.setField("LoginTime_", sess.getProperty(Application.loginTime));
            Buff.setField("YGUser", YGLogin);
            Buff.setField("VerifyMachine", false);
        }
        // 返回值于前台
        getDataOut().getHead().setField("SessionID_", getProperty(Application.token));
        getDataOut().getHead().setField("UserID_", getProperty(Application.userId));
        getDataOut().getHead().setField("UserCode_", getUserCode());
        getDataOut().getHead().setField("CorpNo_", handle.getCorpNo());
        getDataOut().getHead().setField("YGUser", YGLogin);
        // 验证成功，将验证次数赋值为0
        dsUser.edit();
        dsUser.setField("VerifyTimes_", 0);
        dsUser.post();
        tx.commit();
        return true;
    }
}