//获取锁编号 //newLockID 新的锁Id //type 输出字符串 public string getDeviceID(string newLockID, string type) { string outInfo = string.Empty; string sql = "select * from Rent_NewLock_Process where NewLockID='" + newLockID + "'"; DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0]; if (dt.Rows.Count > 0) { return(dt.Rows[0][type].ToString()); } else { return(""); } }
protected string delFamilyUrl = "https://smart.miitzc.com/tmall/delFamily"; //删除家庭 //请求远程接口 1 永久密码 2 临时密码 3 永久卡和永久身份证 4 临时卡和临时身份证 //时间为时间格式 永久接口 时间都相同 public string GetPostInterface(string familyName, string userId, string type, string pwd, string startTime, string endTime) { Dictionary <string, string> ret = new Dictionary <string, string>(); //密码设置 Dictionary <string, string> setRet = new Dictionary <string, string>(); string getPostInfo = string.Empty; string sql = string.Empty; string table = string.Empty; string menu = string.Empty; string sqlPassIC = string.Empty; setRet.Add("appKey", appKey); setRet.Add("appSecret", appSecret); setRet.Add("familyName", familyName); setRet.Add("username", accountOpening); setRet.Add("userid", userId); setRet.Add("startTime", GetCreatetime(DateTime.Parse(startTime))); //获取时间戳 setRet.Add("endTime", GetCreatetime(DateTime.Parse(endTime))); //获取时间戳 //永久时间判断 if (type == "1" || type == "3") { if (DateTime.Parse(startTime) != DateTime.Parse(endTime)) { ret.Add("ret", "1"); ret.Add("msg", "录入永久密码或卡片开始时间和结束时间需相同!"); return(JSONHelper.ToJson(ret)); } } //判断锁是否冻结(锁冻结不能录入) sql = "select * from Rent_Locks where DeviceID='" + familyName + "'"; DataTable dtLock = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0]; if (dtLock.Rows[0]["Status"].ToString() == "3") { ret.Add("ret", "1"); ret.Add("msg", "该锁已经被冻结,不能进行录入!"); return(JSONHelper.ToJson(ret)); } switch (type) { case "1": //永久密码 menu = "永久密码请求"; setRet.Add("pwd", pwd); getPostInfo = LigerRM.Common.WebRequestHelper.WebRequestPoster.JsonHttpPost(setPwdUrl, JSONHelper.ToJson(setRet), "parameters"); sql = "insert into Rent_Locks_Password values ('" + familyName + "','" + pwd + "','" + startTime + "','" + endTime + "','0','1')SELECT SCOPE_IDENTITY() as ID"; break; case "2": //临时密码 menu = "临时密码请求"; setRet.Add("pwd", pwd); getPostInfo = LigerRM.Common.WebRequestHelper.WebRequestPoster.JsonHttpPost(setPwdTimeUrl, JSONHelper.ToJson(setRet), "parameters"); sql = "insert into Rent_Locks_Password values ('" + familyName + "','" + pwd + "','" + startTime + "','" + endTime + "','0','1')SELECT SCOPE_IDENTITY() as ID"; break; case "3": //永久卡和身份证 menu = "永久卡或身份证请求"; setRet.Add("cardId", pwd); //卡号和身份证 getPostInfo = LigerRM.Common.WebRequestHelper.WebRequestPoster.JsonHttpPost(setCardUrl, JSONHelper.ToJson(setRet), "parameters"); sql = "insert into Rent_Locks_ICCards values ('" + familyName + "','', '" + startTime + "','" + endTime + "','0','1')SELECT SCOPE_IDENTITY() as ID"; break; case "4": //临时卡和身份证 menu = "临时卡或身份证请求"; setRet.Add("cardId", pwd); //卡号和身份证 getPostInfo = LigerRM.Common.WebRequestHelper.WebRequestPoster.JsonHttpPost(setCardTimeUrl, JSONHelper.ToJson(setRet), "parameters"); sql = "insert into Rent_Locks_ICCards values ('" + familyName + "','', '" + startTime + "','" + endTime + "','0','1')SELECT SCOPE_IDENTITY() as ID"; break; } Dictionary <string, object> returnInfo = new Dictionary <string, object>(); returnInfo = JSONHelper.FromJson <Dictionary <string, object> >(getPostInfo); if (returnInfo["success"].ToString() == "True") { //校验是否多次提交 (临时卡片多次校验) 永久不需要 时间时刻变化 if (type == "4") { string sqlUser = "******" + userId + "'and LockID='" + familyName + "' and StartDate='" + startTime + "' and EndDate='" + endTime + "'and IsValid='1' and Status='0'"; DataTable dt1 = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sqlUser)).Tables[0]; if (dt1.Rows.Count > 0) { ret.Add("ret", "0"); ret.Add("msg", returnInfo["msg"].ToString()); AddLockNewLog(familyName, menu, returnInfo["msg"].ToString()); //增加新锁的log请求返回日志 return(JSONHelper.ToJson(ret)); } } //数据插入 DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0]; if (type == "1" || type == "2") { table = "Rent_lock_User_Password"; sqlPassIC = "'" + Guid.NewGuid().ToString() + "','" + userId + "','" + dt.Rows[0]["ID"].ToString() + "', ''"; } else { table = "Rent_Lock_User_ICCards"; sqlPassIC = "'" + Guid.NewGuid().ToString() + "','" + userId + "','" + dt.Rows[0]["ID"].ToString() + "'"; } sql = "insert into " + table + " values (" + sqlPassIC + ")"; MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)); ret.Add("ret", "0"); ret.Add("msg", returnInfo["msg"].ToString()); } else { ret.Add("ret", "1"); ret.Add("msg", returnInfo["msg"].ToString()); } AddLockNewLog(familyName, menu, returnInfo["msg"].ToString()); //增加新锁的log请求返回日志 return(JSONHelper.ToJson(ret)); }
public DataTable GetEvaluateList(string objectId) { string sql = "select * from Rent_Evaluate where EvaluateObject='" + objectId + "'"; return(MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0]); }
//删除卡、密码 //type 永久密码、临时密码、永久卡片、临时卡片 //recoverDelType 1 删除 2 解冻 3 冻结 //时间为时间格式 public string delAction(string userId, string type, string startTime, string endTime, string lockId, string recoverDelType) { string url = string.Empty; string sqlSuffix = string.Empty;//确认数据后缀 //string sqlDelSuffix = string.Empty; //删除,冻结,解冻后缀 string operateType = string.Empty; if (recoverDelType == "1") //删除 { url = delCardPass; sqlSuffix = "IsValid='1'"; //sqlDelSuffix = "IsValid='0'"; operateType = "删除"; } else if (recoverDelType == "2") //解冻 { url = recoverPwdICCard; sqlSuffix = "Status='1'"; //sqlDelSuffix = "Status='0'"; } else //冻结 { url = delCardPass; sqlSuffix = "Status='0'"; //sqlDelSuffix = "Status='1'"; operateType = "冻结"; } Dictionary <string, string> ret = new Dictionary <string, string>(); string ICCardPass = string.Empty; string tableUIP = string.Empty; string tableIP = string.Empty; string fieldIP = string.Empty; string firldICCardPass = string.Empty; string isExistence = string.Empty; string setICCardPass = string.Empty; string ID = string.Empty; if (type == "临时卡片" || type == "永久卡片") { tableUIP = "v_RentICCard_view"; //tableIP = "Rent_Locks_ICCards"; fieldIP = "ICCardId"; firldICCardPass = "******"; setICCardPass = "******"; isExistence = "ICCard"; } else { tableUIP = "v_RentPass_view"; //tableIP = "Rent_Locks_Password"; fieldIP = "PassId"; firldICCardPass = "******"; setICCardPass = "******"; isExistence = "IsAdd"; } //确定数据库ICCard或者临时密码 string sqlUser = "******" + tableUIP + " where UserId='" + userId + "'and LockID='" + lockId + "' and StartDate='" + startTime + "' and EndDate='" + endTime + "' and " + sqlSuffix + ""; DataTable dt1 = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sqlUser)).Tables[0]; if (dt1.Rows.Count > 0) { if (recoverDelType != "1" && dt1.Rows[0]["IsValid"].ToString() == "0") { ret.Add("ret", "1"); ret.Add("msg", "该密码已被删除,不能进行操作"); return(JSONHelper.ToJson(ret)); } if (string.Empty == dt1.Rows[0][isExistence].ToString().Trim()) // 判断密码和卡片是否被录入 { ret.Add("ret", "1"); ret.Add("msg", "该信息尚未被录入锁中,请重新添加!"); return(JSONHelper.ToJson(ret)); } ICCardPass = dt1.Rows[0][firldICCardPass].ToString().Trim(); ID = dt1.Rows[0]["ID"].ToString().Trim(); } if (string.Empty == ICCardPass) { ret.Add("ret", "0"); ret.Add("msg", "暂无信息"); return(JSONHelper.ToJson(ret)); } Dictionary <string, string> delRet = new Dictionary <string, string>(); delRet.Add("appKey", appKey); delRet.Add("appSecret", appSecret); delRet.Add("userid", userId); delRet.Add("type", type); delRet.Add("startTime", GetCreatetime(DateTime.Parse(startTime))); delRet.Add("endTime", GetCreatetime(DateTime.Parse(endTime))); delRet.Add(setICCardPass, ICCardPass); if (recoverDelType != "2") { delRet.Add("operate", operateType); } string json = JSONHelper.ToJson(delRet); string getPostInfo = LigerRM.Common.WebRequestHelper.WebRequestPoster.JsonHttpPost(url, json, "parameters"); //调用远程 Dictionary <string, object> returnInfo = new Dictionary <string, object>(); returnInfo = JSONHelper.FromJson <Dictionary <string, object> >(getPostInfo); if (returnInfo["success"].ToString() == "True") { //修改数据库删除状态,冻结状态 IsValid 0 删除 1 不删除 Status 0 启用 1 禁用 //string sqlDel = "update " + tableIP + " set " + sqlDelSuffix + " where ID=" + ID; //MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sqlDel)); ret.Add("ret", "0"); ret.Add("msg", returnInfo["msg"].ToString()); } else { ret.Add("ret", "1"); ret.Add("msg", returnInfo["msg"].ToString()); } AddLockNewLog(lockId, type, returnInfo["msg"].ToString()); //增加新锁的log请求返回日志 return(JSONHelper.ToJson(ret)); }
/// <summary> /// 添加上传日志 /// </summary> /// <param name="rentNo"></param> /// <param name="rraid"></param> /// <param name="returnStr"></param> /// <param name="status">0:成功,1:失败</param> /// <param name="memo"></param> public void AddUploadLog(string rentNo, string rraid, string returnStr, string status, string memo) { string sql = "Insert into Rent_Upload_Log values ('" + rentNo + "'," + rraid.ToString() + ",'" + returnStr + "','" + status + "','" + DateTime.Now.ToString() + "','" + memo + "')"; MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)); }