//获取锁编号
//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));
}
