///////////////////////////////// 试用专用表 ////////////////////////////////
// 创建试用数据表
function sqlCreatTestDataBase()
    var 返回数组,ret
    ret = sqlitesqlarray("regtest","create table IF NOT EXISTS testManager(id integer primary key autoincrement,pcid varchar(100),begintime varchar(100),endtime varchar(100),num varchar(10));",返回数组)//创建表
    if(!ret)
        sendtextmsg("创建表失败:"& getlasterror(1))
        return false
    else
        return true
    end
end

// 判断试用者是否可以验证通过
function sqlCheckTesterLogin(pcID)
    var sqlstr = strformat("select * from testManager where pcid='%s';",pcID)
    var 返回数组,ret,retmsg="登录失败:未知错误"
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    if(返回数组 != null)
        sendtextmsg("Select = "&返回数组)
        
        var num = 返回数组[0]["num"]
        sendtextmsg("试用次数="&num)
        if(num > 100 )
            retmsg = "失败:试用次数达到最大值"
            sendtextmsg("失败:试用次数达到最大值")
            return "false"
        end
        
        num++
        var begintime = timenow()
        var endtime = timeadd("n",20,begintime)
        
        var update = strformat("update testManager set num='%s',begintime='%s',endtime='%s' where pcid='%s';",num,begintime,endtime,pcID)
        sendtextmsg("SQL: "&update)
        ret = sqlitesqlarray("regtest",update,返回数组)
        retmsg = "true"
    else
        var begintime = timenow()
        var endtime = timeadd("n",20,begintime)
        
        var insert = strformat("insert into testManager(pcid,begintime,endtime,num) values('%s','%s','%s','1')",pcID,begintime,endtime)
        sendtextmsg("SQL: "&insert)
        ret = sqlitesqlarray("regtest",insert,返回数组)
        retmsg = "true"
    end
    
    return retmsg
end

// 查询剩下的试用时间
function sql查询试用时间(pcid)
    // 参数格式: 机器码
    var sqlstr = strformat("select * from testManager where pcid='%s';",pcid)
    var 返回数组,ret,retMsg
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    
    if(返回数组 == null)
        sendtextmsg("Error:"&getlasterror(1))
        retMsg = "-1"
    else
        var begintime = 返回数组[0]["begintime"]
        var endtime = 返回数组[0]["endtime"]
        var nowtime = timenow()
        var min = timediff("n",nowtime,endtime)
        sendtextmsg("leave Time: "&min)
        retMsg = min
    end
    
    return retMsg
end


///////////////////////////////// 注册码专用表 ////////////////////////////////
// 创建数据库
function sqlCreatDataBase()
    var 返回数组,ret
    ret = sqlitesqlarray("regtest","create table IF NOT EXISTS regManager(id integer primary key autoincrement,regcode varchar(100),begintime varchar(100),endtime varchar(100),bindDevicesName varchar(100),isbind varchar(10),isFrozen varchar(10),type varchar(10),runType varchar(10));",返回数组)//创建表
    if(!ret)
        sendtextmsg("创建表失败:"& getlasterror(1))
        return false
    else
        return true
    end
end


// 生成注册码功能
function sqlCreateRegCode(param)
    var timestr = gettickcount()
    var md5str = md5(timestr)
    var retArray,inserText
    select(param)
        case "ji"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','ji','false');",md5str)
        
        case "mouth"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','mouth','false');",md5str)
        
        case "week"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','week','false');",md5str)
        
        case "day"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','day','false');",md5str)
    end
    
    var ret = sqlitesqlarray("regtest",inserText,retArray)
    if(ret)
        var retMsg = "生成注册码["&md5str&"]成功"
        sendtextmsg("生成注册码["&md5str&"]成功")
        return retMsg
    else
        var retMsg = "生成注册码失败"
        sendtextmsg("生成注册码["&md5str&"]失败")
        return retMsg
    end
end

function sql创建新注册码(param)
    var timestr = gettickcount()
    var md5str = md5(timestr)
    var retArray = null,inserText
    select(param)
        case "ji"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','ji','false');",md5str)
        
        case "mouth"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','mouth','false');",md5str)
        
        case "week"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','week','false');",md5str)
        
        case "day"
        inserText = strformat("insert into regManager(regcode,begintime,endtime,bindDevicesName,isbind,isFrozen,type,runType) values('%s','','','','false','false','day','false');",md5str)
    end
    
    var ret = sqlitesqljson("regtest",inserText,retArray)
    var selectStr = strformat("select * from regManager where regcode='%s';",md5str)
    ret = sqlitesqljson("regtest",selectStr,retArray)
    if(ret)
        sendtextmsg("生成注册码["&md5str&"]成功")
        return retArray
    else
        sendtextmsg("生成注册码["&md5str&"]失败")
        return retArray
    end
end

// 判断登录者是否可以验证通过
function sqlCheckLoginInfo(pcID,regKey)
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    var 返回数组,ret,retmsg="登录失败:未知错误"
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    sendtextmsg("sqlCheckLoginInfo: " & 返回数组)
    if(返回数组 != null)
        // 判断注册码是否冻结了
        if(返回数组[0]["isFrozen"] == "true")
            retmsg = "登录失败:注册码已经冻结"
            return retmsg
        end
        
        // 判断注册码是否已经绑定了本机器
        var bindDevicesName = 返回数组[0]["bindDevicesName"]
        
        // 第一次登录
        if(bindDevicesName == "")
            var begintime = timenow()
            var endtime = 0
            
            select(返回数组[0]["type"])
                case "ji"
                endtime = timeadd("m",3,begintime)
                
                case "mouth"
                endtime = timeadd("m",1,begintime)
                
                case "week"
                endtime = timeadd("d",7,begintime)
                
                case "day"
                endtime = timeadd("d",1,begintime)
            end
            
            var update = strformat("update regManager set bindDevicesName='%s',begintime='%s',endtime='%s',isbind='true',runType='true' where regcode='%s';",pcID,begintime,endtime,regKey)
            sendtextmsg("SQL: "&update)
            ret = sqlitesqlarray("regtest",update,返回数组)
            
            var min = timediff("n",begintime,endtime)
            if(min>0)
                retmsg = "登录成功:剩余时间=" & min & "分钟"
            end
            
        elseif(bindDevicesName != "" && bindDevicesName == pcID)  // 机器码相同的话,就直接返回剩余时间
            var begintime = 返回数组[0]["begintime"]
            var endtime = 返回数组[0]["endtime"]
            var nowtime = timenow()
            var min = timediff("n",nowtime,endtime)
            if(min>0)
                retmsg = "登录成功:剩余时间=" & min & "分钟"
            else
                retmsg = "登录失败:注册码已经过期"
            end
            
        elseif(bindDevicesName != "" && bindDevicesName != pcID)  // 机器码不同的就判断是否绑定了
            var isBind = 返回数组[0]["isbind"]
            if(isBind == "true")
                retmsg = "登录失败:注册码已经绑定其他机器"
            else
                var update = strformat("update regManager set bindDevicesName='%s',isbind='true' where regcode='%s';",pcID,regKey)
                sendtextmsg("更换绑定机器码: "&update)
                ret = sqlitesqlarray("regtest",update,返回数组)
                
                var endtime = 返回数组[0]["endtime"]
                var nowtime = timenow()
                var min = timediff("n",nowtime,endtime)
                if(min>0)
                    retmsg = "登录成功:剩余时间=" & min & "分钟"
                else
                    retmsg = "登录失败:注册码已经过期"
                end
            end
        end
    else
        sendtextmsg("查询失败:"&getlasterror(1))
        retmsg = "登录失败:注册码不存在"
    end
    
    return retmsg
end

// 查询注册码使用时间
function sql查询注册码使用时间(pcID,regKey)
    
    var sqlstr = strformat("select * from regManager where regcode='%s'",regKey)
    var 返回数组,ret,retMsg
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    if(返回数组 == null)
        retMsg = "查询失败:查询失败."
    elseif(返回数组[0]["isFrozen"] == "true")
        retMsg = "查询失败:注册码已经冻结."
    else
        sendtextmsg("isFrozen:"&返回数组[0]["isFrozen"])
        var begintime = 返回数组[0]["begintime"]
        var endtime = 返回数组[0]["endtime"]
        var nowtime = timenow()
        var min = timediff("n",nowtime,endtime)
        
        retMsg = min
    end
    
    return retMsg
end

// 返回注册码信息
function sql获取注册码信息()
    
    var sqlstr = "select regcode from regManager"
    var 返回数组,ret
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    return 返回数组
    
end

// 返回注册码信息
function sql获取注册码所有信息()
    
    var sqlstr = "select * from regManager"
    var 返回数组,ret
    ret = sqlitesqljson("regtest",sqlstr,返回数组)
    return 返回数组
    
end

// 解绑注册码
function sql解绑注册码(regKey)
    var 返回数组,ret
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    ret = sqlitesqljson("regtest",sqlstr,返回数组)
    if(ret && 返回数组 != null)
        sqlstr = strformat("update regManager set isbind='false' where regcode='%s';",regKey)    
        ret = sqlitesqljson("regtest",sqlstr,返回数组)
        sendtextmsg("sql解绑注册码")
        return true
    else
        sendtextmsg("sql解绑注册码")
        return false
    end    
end

// 冻结注册码
function sql冻结注册码(regKey)
    var 返回数组,ret
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    ret = sqlitesqljson("regtest",sqlstr,返回数组)
    if(ret && 返回数组 != null)
        sqlstr = strformat("update regManager set isFrozen='true' where regcode='%s';",regKey)
        ret = sqlitesqljson("regtest",sqlstr,返回数组)
        sendtextmsg("sql冻结注册码")
        return true
    else
        sendtextmsg("sql冻结注册码")
        return false
    end
end

// 解冻注册码
function sql解冻注册码(regKey)
    var 返回数组,ret
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    ret = sqlitesqljson("regtest",sqlstr,返回数组)
    if(ret && 返回数组 != null)
        sqlstr = strformat("update regManager set isFrozen='false' where regcode='%s';",regKey)
        ret = sqlitesqljson("regtest",sqlstr,返回数组)
        sendtextmsg("sql解冻注册码")
        return true
    else
        sendtextmsg("sql解冻注册码")
        return false
    end
end

// 删除注册码
function sql删除注册码(regKey)
    var 返回数组,ret
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    ret = sqlitesqljson("regtest",sqlstr,返回数组)
    if(ret && 返回数组 != null)
        sqlstr = strformat("delete from regManager where regcode='%s';",regKey)
        ret = sqlitesqljson("regtest",sqlstr,返回数组)
        sendtextmsg("sql删除注册码")
        return true
    else
        sendtextmsg("sql删除注册码")
        return false
    end
end

// sql续费注册码
function sql续费注册码(regKey,type)
    var 返回数组,ret
    var sqlstr = strformat("select * from regManager where regcode='%s';",regKey)
    ret = sqlitesqlarray("regtest",sqlstr,返回数组)
    if(ret && 返回数组 != null)
        var end1Time1 = 返回数组[0]["endtime"]
        var endtime = 0
        select(type)
            case "ji"
            endtime = timeadd("m",3,end1Time1)
            
            case "mouth"
            endtime = timeadd("m",1,end1Time1)
            
            case "week"
            endtime = timeadd("d",7,end1Time1)
            
            case "day"
            endtime = timeadd("d",1,end1Time1)
        end
        sqlstr = strformat("update regManager set endtime='%s' where regcode='%s';",endtime,regKey)
        sendtextmsg(sqlstr)
        ret = sqlitesqljson("regtest",sqlstr,返回数组)
        sendtextmsg("sql续费注册码成功")
        return true
    else
        sendtextmsg("sql续费注册码成功")
        return false
    end
end
