public DataTable GetUserRegRelationList(string userId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from Rent_user_dept_relationship where t_wu_user_id=" + userId);
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0]);
}
public DataTable GetDeviceDetailInfo(string deviceId)
{
string sql = "select * from Rent_Locks where ID=" + deviceId;
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
return(dt);
}
public DataTable GetAllMessages()
{
string sql = "select top 5 * from t_web_text order by wt_time desc ";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
return(dt);
}
public string GetRentNo(string RDID)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select LDShortName,LDName from Rent_District WITH (NOLOCK) where LDID='" + RDID + "'");
DataTable rentTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0];
string ldName = rentTable.Rows[0]["LDName"].ToString();
string shortName = rentTable.Rows[0]["LDShortName"].ToString();
StringBuilder sqlStrId = new StringBuilder();
sqlStrId.Append("select top 1 substring(RentNo,charindex('-',RentNo)+1 ,len(RentNo)) as [LatestNo] from Rent_Rent where RDName='" + RDID + "' order by RID desc");
DataTable rentTable1 = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStrId.ToString())).Tables[0];
if (rentTable1 != null && rentTable1.Rows.Count > 0)
{
int maxRentNo = Convert.ToInt32(rentTable1.Rows[0]["LatestNo"]) + 1;
return(shortName + "-" + maxRentNo.ToString());
}
else
{
return(shortName + "-10000001");
}
}
public DataTable GetCummunityList()
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from Rent_Road");
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0]);
}
public DataTable GetCFUserList(string deptId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from cf_user where DeptId=" + deptId);
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0]);
}
//up_SignetInternet_RegDeptSelectAll
public DataTable GetRegDeptList()
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from Rent_PoliceStation where parentID>0");
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0]);
}
public DataTable GetDeptList()
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from CF_Department");
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString())).Tables[0]);
}
public DataTable GetStatisticTimeData(string category, string type, string year, string month, string day, string psName, string userId)
{
DataTable dataTable = null;
SqlParameter[] parameters =
{
new SqlParameter("@statistictype", SqlDbType.VarChar, 50),
new SqlParameter("@statisticyear", SqlDbType.VarChar, 50),
new SqlParameter("@statisticmonth", SqlDbType.VarChar, 50),
new SqlParameter("@statisticday", SqlDbType.VarChar, 50),
new SqlParameter("@psname", SqlDbType.NVarChar, 50),
new SqlParameter("@userId", SqlDbType.NVarChar, 50)
};
parameters[0].Value = type;
parameters[1].Value = year;
parameters[2].Value = month;
parameters[3].Value = day;
parameters[4].Value = psName;
parameters[5].Value = userId;
if (!string.IsNullOrEmpty(category) || category == "0")
{
dataTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(CommandType.StoredProcedure, "up_RentReport_RentStatisticByPoliceStation", parameters)).Tables[0];
}
else
{
dataTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(CommandType.StoredProcedure, "up_RentReport_RentHouseStatisticByPoliceStation", parameters)).Tables[0];
}
return(dataTable);
}
public bool IsExistsSameAddressRent(string rdName, string rsName, string rrName, string rDoor)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select 1 from Rent_Rent where RDName=@rdName AND RSName=@rsName AND RRName=@rrName and RDoor=@rDoor");
SqlParameter[] parameters =
{
new SqlParameter("@rdName", SqlDbType.NVarChar, 50),
new SqlParameter("@rsName", SqlDbType.NVarChar, 50),
new SqlParameter("@rrName", SqlDbType.NVarChar, 50),
new SqlParameter("@rDoor", SqlDbType.NVarChar, 50),
};
parameters[0].Value = rdName;
parameters[1].Value = rsName;
parameters[2].Value = rrName;
parameters[3].Value = rDoor;
DataTable rentTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)).Tables[0];
if (rentTable != null && rentTable.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 用户充值
/// </summary>
/// <param name="idcard"></param>
/// <param name="fee"></param>
public string DepositUserWallet(string idcard, string fee)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
ret.Add("ret", "0");
string sql = "select * from v_CF_User_View where IDCard='" + idcard + "'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
string userId = dt.Rows[0]["UserID"].ToString();
sql = "select * from CF_User_Extenal where UserID=" + dt.Rows[0]["UserID"].ToString();
dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
string feeRate = ConfigurationManager.AppSettings["FeeRate"];
if (dt.Rows.Count > 0)
{
decimal wallet = decimal.Parse(dt.Rows[0]["Wallet"].ToString()) + decimal.Parse(fee);
sql = "update CF_User_Extenal set wallet = " + wallet.ToString() + " where ExternalID=" + dt.Rows[0]["ExternalID"].ToString();
MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
ret.Add("fee", wallet.ToString());
}
else
{
decimal wallet = decimal.Parse(fee);
sql = "Insert into CF_User_Extenal values (" + userId + "," + wallet.ToString() + ",0,'','','')";
MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
ret.Add("fee", wallet.ToString());
}
return(JSONHelper.ToJson(ret));
}
/// <summary>
/// 添加密码到电子锁
/// </summary>
/// <param name="rentNO"></param>
/// <param name="pass"></param>
/// <param name="startdate"></param>
/// <param name="enddate"></param>
/// <param name="phone"></param>
/// <returns></returns>
public string AddPassword(string rentNO, string pass, string startdate, string enddate, string phone)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
string sql = "select * from Rent_Locks where rentNo='" + rentNO + "' and Status='0'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["DeviceType"].ToString() == "1") //新锁
{
NewLockManager managerNew = new NewLockManager();
string r = managerNew.GetPostInterface(dt.Rows[0]["DeviceID"].ToString(), phone, "2", pass, startdate, enddate); //设置临时密码
ret = JSONHelper.FromJson <Dictionary <string, string> >(r);
}
else //旧锁
{
LockManager manager = new LockManager();
manager.AddPassword(dt.Rows[0]["DeviceID"].ToString(), pass, startdate, enddate);
string r = manager.UpdatePassengerInfoToDevice(dt.Rows[0]["DeviceID"].ToString(), "", "", "", pass, DateTime.Parse(startdate).ToString("yyyyMMddHHmm").Substring(2, 10), DateTime.Parse(enddate).ToString("yyyyMMddHHmm").Substring(2, 10), "3");
ret = JSONHelper.FromJson <Dictionary <string, string> >(r);
}
}
else
{
ret.Add("ret", "1");
ret.Add("msg", "未发现智能锁信息，无法添加密码");
}
return(JSONHelper.ToJson(ret));
}
/// <summary>
/// 是否可以用钱包支付
/// </summary>
/// <param name="idCard"></param>
/// <param name="fee"></param>
/// <returns></returns>
public string CanPayFromWallet(string idCard, string fee)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
try
{
string sql = "select * from v_CF_User_View where IDCard='" + idCard + "'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (!dt.Rows[0].IsNull("Wallet") && decimal.Parse(dt.Rows[0]["Wallet"].ToString()) >= decimal.Parse(fee) * 2)
{
ret.Add("ret", "0");
ret.Add("msg", "Success");
}
else
{
ret.Add("ret", "1");
ret.Add("msg", "余额不足，账户中需有两倍以上要缴纳的费用，才可以使用钱包支付！");
}
}
catch (Exception ex)
{
ret.Add("ret", "1");
ret.Add("msg", ex.Message);
}
return(JSONHelper.ToJson(ret));
}
public DataTable GetDataTable(string sql)
{
SqlParameter[] parameters =
{
};
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sql, parameters)).Tables[0]);
}
public DataTable GetRentAttribute(string id)
{
string sql = "select * from Rent_RentAttribute where RRAIsActive=0 and RRAID=" + id;
DataSet ds = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
return(ds.Tables[0]);
}
public RentAttribute(int rraId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("SELECT * FROM Rent_RentAttribute WHERE RRAID=@RRAID");
SqlParameter[] parameters =
{
new SqlParameter("@RRAID", SqlDbType.Int),
};
parameters[0].Value = rraId;
DataTable rentTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)).Tables[0];
if (rentTable != null && rentTable.Rows.Count > 0)
{
m_RRAID = Convert.ToInt16(rentTable.Rows[0]["RRAID"]);
m_RentNo = rentTable.Rows[0]["RentNo"].ToString();
m_RRAContactName = rentTable.Rows[0]["RRAContactName"].ToString();
m_RRAContactTel = rentTable.Rows[0]["RRAContactTel"].ToString();
m_RRANationName = rentTable.Rows[0]["RRANationName"].ToString();
m_RRAIDCard = rentTable.Rows[0]["RRAIDCard"].ToString();
m_RRentPrice = Convert.ToDecimal(rentTable.Rows[0]["RRentPrice"]);
m_RRAContactProvince = rentTable.Rows[0]["RRAContactProvince"].ToString();
m_RRAStartDate = DateTime.Parse(rentTable.Rows[0]["RRAStartDate"].ToString());
m_RRAEndDate = DateTime.Parse(rentTable.Rows[0]["RRAEndDate"].ToString());
if (!rentTable.Rows[0].IsNull("RRARealEndDate"))
{
m_RRARealEndDate = DateTime.Parse(rentTable.Rows[0]["RRARealEndDate"].ToString());
}
RRACheckOutPerson = rentTable.Rows[0]["RRACheckOutPerson"].ToString();
RRACheckOutReason = rentTable.Rows[0]["RRACheckOutReason"].ToString();
m_RRADescription = rentTable.Rows[0]["RRADescription"].ToString();
m_RRACreatedBy = rentTable.Rows[0]["RRACreatedBy"].ToString();
m_RRACreatedDate = DateTime.Parse(rentTable.Rows[0]["RRACreatedDate"].ToString());
m_RRAModifiedBy = rentTable.Rows[0]["RRAModifiedBy"].ToString();
if (rentTable.Rows[0]["RRAStatus"] != null)
{
m_Status = rentTable.Rows[0]["RRAStatus"].ToString();
}
if (rentTable.Rows[0]["RRAModifiedDate"] == null || string.IsNullOrEmpty(rentTable.Rows[0]["RRAModifiedDate"].ToString()))
{
m_RRAModifiedDate = null;
}
else
{
m_RRAModifiedDate = DateTime.Parse(rentTable.Rows[0]["RRAModifiedDate"].ToString());
}
m_RRAIsActive = Convert.ToBoolean(rentTable.Rows[0]["RRAIsActive"].ToString());
}
string sql = "select * from Rent_RentAttribute_External where RRAID=" + rraId;
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (dt.Rows.Count > 0)
{
m_tenantId = dt.Rows[0]["HZTenantID"].ToString();
}
}
/// <summary>
/// 获取随行人员
/// </summary>
/// <param name="rraId"></param>
/// <returns></returns>
public DataTable GetRetinues(string rraId)
{
string sql = "select * from Rent_Retinues where RRAID = '" + rraId + "'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
return(dt);
}
public AttachmentInfo(int attachmentId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from t_signet_attachment where sa_attachment_id=@attachmentId");
SqlParameter[] parameters =
{
new SqlParameter("@attachmentId", SqlDbType.BigInt),
};
parameters[0].Value = attachmentId;
DataTable attachmentTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)).Tables[0];
if (attachmentTable != null && attachmentTable.Rows.Count > 0)
{
m_attachmentId = attachmentId;
m_signetId = attachmentTable.Rows[0]["sa_signet_id"].ToString();
m_attachmentTitle = attachmentTable.Rows[0]["sa_attachment_description"].ToString();
m_attachmentDemo = attachmentTable.Rows[0]["sa_attachment_demo"].ToString();
m_createDate = DateTime.Parse(attachmentTable.Rows[0]["sa_attachment_date"].ToString());
if (!attachmentTable.Rows[0].IsNull("sa_attachment_mark"))
{
m_fileInfo = (byte[])attachmentTable.Rows[0]["sa_attachment_mark"];
FileStream fileStream = new FileStream(System.Web.HttpContext.Current.Server.MapPath("~") + "\\temp_" + attachmentId.ToString() + ".jpg", FileMode.Create, FileAccess.ReadWrite);
fileStream.Write(m_fileInfo, 0, m_fileInfo.Length);
fileStream.Flush();
fileStream.Dispose();
}
}
}
//企业接口权限检索
public bool GetIsInterfacePermissions(string number, string userId)
{
string sql = "select * from Lock_Enterprise_Dock where UserId='" + userId + "'and EnterpriseStatus='1'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (dt.Rows.Count <= 0)
{
return(false);
}
else
{
if (dt.Rows[0]["memo1"].ToString() == string.Empty)
{
return(false);
}
else
{
string[] bit = dt.Rows[0]["memo1"].ToString().Split('|');//用|进行分割
bool exists = ((IList)bit).Contains(number);
if (exists)
{
return(true);
}
else
{
return(false);
}
}
}
}
public DataTable GetMessageByPoliceStation()
{
string sql = "select * from t_web_text where wt_serial_id not in(select messageid from t_web_text_relationship )" +
" union all" +
" select t_web_text.* from t_web_text " +
" inner join t_web_text_relationship r on r.MessageID = t_web_text.wt_serial_id and r.RelatedID in(" + LigerRM.Common.SysContext.CurrentAreaIDs + ")";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
return(dt);
}
public CorporationInfo(string corpId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select *,t_area.ar_area_name from t_corporation inner join t_corp_class on co_corp_class = cc_corp_class inner join t_general_code on gc_id = co_type and gc_code_group = 'CT' left join t_area on t_area.ar_area_id = co_area_id where co_corp_id=@corpId");
SqlParameter[] parameters =
{
new SqlParameter("@corpId", SqlDbType.VarChar, 12),
};
parameters[0].Value = corpId;
DataTable corpTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)).Tables[0];
if (corpTable != null && corpTable.Rows.Count > 0)
{
m_corpId = corpTable.Rows[0]["co_corp_id"].ToString();
m_region = m_corpId.Substring(0, 6);
m_corpName = corpTable.Rows[0]["co_corp_name"].ToString();
m_sortId = corpTable.Rows[0]["co_sort_id"].ToString();
m_aliasName = corpTable.Rows[0]["co_alias_name"].ToString();
m_fullName = corpTable.Rows[0]["co_full_name"].ToString();
m_corpClass = corpTable.Rows[0]["co_corp_class"].ToString();
m_corpClassName = corpTable.Rows[0]["cc_description"].ToString();
m_corpType = corpTable.Rows[0]["co_type"].ToString();
m_corpTypeName = corpTable.Rows[0]["gc_name"].ToString();
m_areaId = corpTable.Rows[0]["co_area_id"].ToString();
m_areaName = corpTable.Rows[0]["ar_area_name"].ToString();
m_bossName = corpTable.Rows[0]["co_boss"].ToString();
m_bossIDCard = corpTable.Rows[0]["co_boss_idcard"].ToString();
m_address = corpTable.Rows[0]["co_address"].ToString();
m_linker = corpTable.Rows[0]["co_linker"].ToString();
m_linkway = corpTable.Rows[0]["co_link_way"].ToString();
m_postCode = corpTable.Rows[0]["co_post_code"].ToString();
m_taxno = corpTable.Rows[0]["co_tax_no"].ToString();
m_accountNo = corpTable.Rows[0]["co_account_no"].ToString();
m_otherNo = corpTable.Rows[0]["co_other_no"].ToString();
m_passWord = corpTable.Rows[0]["co_password"].ToString();
m_creator = corpTable.Rows[0]["co_creator"].ToString();
m_createDate = DateTime.Parse(corpTable.Rows[0]["co_create_date"].ToString());
m_bizNo = corpTable.Rows[0]["co_biz_id"].ToString();
m_cancellor = corpTable.Rows[0]["co_who_cancel"].ToString();
if (corpTable.Rows[0]["co_cancel_date"] == null || string.IsNullOrEmpty(corpTable.Rows[0]["co_cancel_date"].ToString()))
{
m_cancelDate = null;
}
else
{
m_cancelDate = DateTime.Parse(corpTable.Rows[0]["co_cancel_date"].ToString());
}
m_cancelType = corpTable.Rows[0]["co_cancel_type"].ToString();
m_cancelReason = corpTable.Rows[0]["co_cancel_reason"].ToString();
m_status = corpTable.Rows[0]["co_status"].ToString();
m_memo = corpTable.Rows[0]["co_memo"].ToString();
}
}
public string AddRentAttribute(RentAttribute rentAttribute)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
//strSql1.Append(" Update Rent_Rent set IsAvailable=1");
//strSql1.Append(" where RentNo = '" + rentAttribute.RentNo + "'");
//Update
strSql.Append("INSERT INTO Rent_RentAttribute ([RentNo],[RRAContactName],[RRAContactTel],[RRANationName],[RRAIDCard]," +
"[RRentPrice],[RRAContactProvince],[RRAStartDate],[RRAEndDate],[RRARealEndDate],[RRACheckOutPerson],[RRACheckOutReason],[RRADescription],[RRACreatedBy]," +
"[RRACreatedDate],[RRAModifiedBy],[RRAModifiedDate],[RRAIsActive],[RRAStatus],[AppId],[Body],[MchId],[TradeNO],[TotalFee],[PrepayID]) values (");
strSql.Append(" '" + rentAttribute.RentNo + "',");
strSql.Append(" '" + rentAttribute.RRAContactName + "',");
strSql.Append(" '" + rentAttribute.RRAContactTel + "',");
strSql.Append(" '" + rentAttribute.RRANationName + "',");
strSql.Append(" '" + rentAttribute.RRAIDCard + "',");
strSql.Append(" '" + rentAttribute.RRentPrice + "', ");
strSql.Append(" '" + rentAttribute.RRAContactProvince + "' ,");
strSql.Append(" '" + rentAttribute.RRAStartDate + "','" + rentAttribute.RRAEndDate + "',null,'','',");
strSql.Append(" '" + rentAttribute.RRADescription + "',");
strSql.Append(" '" + rentAttribute.RRACreatedBy + "',");
strSql.Append(" '" + rentAttribute.RRACreatedDate.ToString("yyyy-MM-dd HH:mm:ss") + "', ");
strSql.Append("'" + rentAttribute.RRACreatedBy + "','" + rentAttribute.RRACreatedDate.ToString("yyyy-MM-dd HH:mm:ss") + "', 0 ,'" + rentAttribute.Status + "','','','','',0,''");
strSql.Append(" )");
strSql.Append(" select @@identity");
List <SqlCommand> listSQL = new List <SqlCommand>();
listSQL.Add(MySQLHelper.CreateCommand(strSql.ToString()));
DataSet ds = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(strSql.ToString()));
string sql = "select top 1 * from Rent_RentAttribute where RentNo = '" + rentAttribute.RentNo + "' order by RRAID desc";
DataTable dt2 = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
SMS.CommonServices service = new SMS.CommonServices();
string approveMsg = ConfigurationManager.AppSettings["ApproveMessage"].ToString();
RentInfo info = new RentInfo(rentAttribute.RentNo);
service.SendMsg(info.ROwnerTel, approveMsg);
CFUserInfo userInfo = new CFUserInfo(info.RIDCard, false);
LockManager manager = new LockManager();
manager.SendMessageToDevice(userInfo.DeviceID, approveMsg, dt2.Rows[0]["RRAID"].ToString(), userInfo.IDCard);
SysLogHelper.AddLog(rentAttribute.RRACreatedBy, "增加租赁信息ID：" + rentAttribute.RentNo, "增加-租赁信息");
return(dt2.Rows[0]["RRAID"].ToString());
}
public DataSet GetSignetMark(string signetId)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select * from t_signet_mark where signetid=@signetId");
SqlParameter[] parameters =
{
new SqlParameter("@signetId", SqlDbType.VarChar, 50),
};
parameters[0].Value = signetId;
return(MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)));
}
public RentAttribute(string RentNo)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("SELECT TOP 1 * FROM Rent_RentAttribute rra WITH (NOLOCK) INNER JOIN Rent_Rent rr WITH (NOLOCK) ON rra.RentNo=rr.RentNO WHERE rra.RentNo=@RentNo and rr.IsAvailable=1 order by RRAID DESC, RRACreatedDate desc");
SqlParameter[] parameters =
{
new SqlParameter("@RentNo", SqlDbType.NVarChar, 20),
};
parameters[0].Value = RentNo;
DataTable rentTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlStr.ToString(), parameters)).Tables[0];
if (rentTable != null && rentTable.Rows.Count > 0)
{
m_RRAID = Convert.ToInt16(rentTable.Rows[0]["RRAID"]);
m_RentNo = rentTable.Rows[0]["RentNo"].ToString();
m_RRAContactName = rentTable.Rows[0]["RRAContactName"].ToString();
m_RRAContactTel = rentTable.Rows[0]["RRAContactTel"].ToString();
m_RRANationName = rentTable.Rows[0]["RRANationName"].ToString();
m_RRAIDCard = rentTable.Rows[0]["RRAIDCard"].ToString();
m_RRentPrice = Convert.ToDecimal(rentTable.Rows[0]["RRentPrice"]);
m_RRAContactProvince = rentTable.Rows[0]["RRAContactProvince"].ToString();
m_RRAStartDate = DateTime.Parse(rentTable.Rows[0]["RRAStartDate"].ToString());
m_RRAEndDate = DateTime.Parse(rentTable.Rows[0]["RRAEndDate"].ToString());
if (!rentTable.Rows[0].IsNull("RRARealEndDate"))
{
m_RRARealEndDate = DateTime.Parse(rentTable.Rows[0]["RRARealEndDate"].ToString());
}
RRACheckOutPerson = rentTable.Rows[0]["RRACheckOutPerson"].ToString();
RRACheckOutReason = rentTable.Rows[0]["RRACheckOutReason"].ToString();
m_RRADescription = rentTable.Rows[0]["RRADescription"].ToString();
m_RRACreatedBy = rentTable.Rows[0]["RRACreatedBy"].ToString();
m_RRACreatedDate = DateTime.Parse(rentTable.Rows[0]["RRACreatedDate"].ToString());
m_RRAModifiedBy = rentTable.Rows[0]["RRAModifiedBy"].ToString();
if (rentTable.Rows[0]["RRAStatus"] == null)
{
m_Status = rentTable.Rows[0]["RRAStatus"].ToString();
}
if (rentTable.Rows[0]["RRAModifiedDate"] == null || string.IsNullOrEmpty(rentTable.Rows[0]["RRAModifiedDate"].ToString()))
{
m_RRAModifiedDate = null;
}
else
{
m_RRAModifiedDate = DateTime.Parse(rentTable.Rows[0]["RRAModifiedDate"].ToString());
}
m_RRAIsActive = Convert.ToBoolean(rentTable.Rows[0]["RRAIsActive"].ToString());
}
}
public void DeleteICCard(string id)
{
string sql = "select * from Rent_Locks_ICCards where ID=" + id;
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
sql = "update Rent_Locks_ICCards set IsValid='0' where ID=" + id;
MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
string s = string.Empty;
LockServices.IhzbAttenServiceservice client = new LockServices.IhzbAttenServiceservice();
string str = dt.Rows[0]["LockID"].ToString().PadRight(16, ' ') + dt.Rows[0]["ICCard"].ToString().PadRight(10, ' ') + s.PadRight(18, ' ') + s.PadRight(13, ' ') + s.PadRight(6, ' ') + DateTime.Parse(dt.Rows[0]["StartDate"].ToString()).ToString("yyyyMMddHHmm").Substring(2, 10) + DateTime.Parse(dt.Rows[0]["EndDate"].ToString()).ToString("yyyyMMddHHmm").Substring(2, 10) + "2";
string ret = client.hzb_SetPassengerInfo(99, "02500262", str);
}
public DataTable GetStatisticData(string category, string sDate, string eDate, string regDept, string userId)
{
DataTable dataTable = null;
switch (category)
{
case "AllCorp":
SqlParameter[] parameters =
{
new SqlParameter("@adt_from_date", SqlDbType.VarChar, 50),
new SqlParameter("@adt_to_date", SqlDbType.VarChar, 50),
new SqlParameter("@regDept", SqlDbType.VarChar, 50)
};
parameters[0].Value = sDate;
parameters[1].Value = eDate;
parameters[2].Value = regDept;
dataTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(CommandType.StoredProcedure, "up_PostInternet_PostStatisticAllByCorp", parameters)).Tables[0];
break;
case "AllPoliceStation":
SqlParameter[] parameters1 =
{
new SqlParameter("@adt_from_date", SqlDbType.VarChar, 50),
new SqlParameter("@adt_to_date", SqlDbType.VarChar, 50),
new SqlParameter("@psname", SqlDbType.VarChar, 50),
new SqlParameter("@userId", SqlDbType.VarChar, 50)
};
parameters1[0].Value = sDate;
parameters1[1].Value = eDate;
parameters1[2].Value = regDept;
parameters1[3].Value = userId;
dataTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(CommandType.StoredProcedure, "up_RentReport_RentInfoStatisticAllByPoliceStation", parameters1)).Tables[0];
break;
case "AllPoliceHouse":
SqlParameter[] parameters2 =
{
new SqlParameter("@adt_from_date", SqlDbType.VarChar, 50),
new SqlParameter("@adt_to_date", SqlDbType.VarChar, 50),
new SqlParameter("@psname", SqlDbType.VarChar, 50),
new SqlParameter("@userId", SqlDbType.VarChar, 50)
};
parameters2[0].Value = sDate;
parameters2[1].Value = eDate;
parameters2[2].Value = regDept;
parameters2[3].Value = userId;
dataTable = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(CommandType.StoredProcedure, "up_RentReport_RentInfoStatisticAllByPoliceHouseStation", parameters2)).Tables[0];
break;
}
return(dataTable);
}
/// <summary>
/// 通过sql返回list实体对象
/// 泛型
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="Sql">sql</param>
/// <returns></returns>
public List <T> GetList <T>(string Sql) where T : new()
{
DataSet ds = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(Sql));
List <T> lists = new List <T>();
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
lists.Add(PutVal(new T(), row));
}
}
return(lists);
}
/// <summary>
/// 删除密码和IC卡
/// </summary>
/// <param name="aarId"></param>
/// <returns></returns>
public string ClearPasswordToLock(string aarId)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
RentAttribute a = new RentAttribute(int.Parse(aarId));
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand("select * from Rent_Locks where rentno='" + a.RentNo + "'")).Tables[0];
if (dt.Rows.Count > 0)
{
string returnStr1 = string.Empty;
if (dt.Rows[0]["DeviceType"].ToString() == "1") //新锁
{
NewLockManager managerNew = new NewLockManager();
//清空临时卡
string iCCard = managerNew.delAction(a.RRAContactTel, "临时卡片", a.RRAStartDate.ToString(), a.RRAEndDate.ToString(), dt.Rows[0]["DeviceID"].ToString(), "1");
//清空临时密码
string password = managerNew.delAction(a.RRAContactTel, "临时密码", a.RRAStartDate.ToString(), a.RRAEndDate.ToString(), dt.Rows[0]["DeviceID"].ToString(), "1");
Dictionary <string, object> retIC = new Dictionary <string, object>();
retIC = JSONHelper.FromJson <Dictionary <string, object> >(iCCard);
Dictionary <string, object> retPass = new Dictionary <string, object>();
retPass = JSONHelper.FromJson <Dictionary <string, object> >(password);
if (retIC["ret"].ToString() == "1" || retPass["ret"].ToString() == "1")
{
returnStr1 = "ICCard:" + retIC["msg"].ToString() + "Pass:"******"msg"].ToString();
}
}
else //旧锁
{
string sql = "select * from Rent_Locks_ICCards where LockID='" + dt.Rows[0]["DeviceID"].ToString() + "' and StartDate='" + a.RRAStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and EndDate='" + a.RRAEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
LockManager manager = new LockManager();
DataTable dt1 = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
foreach (DataRow row in dt1.Rows)
{
string returnStr = manager.UpdatePassengerInfoToDevice(dt.Rows[0]["DeviceID"].ToString(), row["ICCard"].ToString(), "", "", "", a.RRAStartDate.ToString("yyyyMMddHHmm").Substring(2, 10), a.RRAEndDate.ToString("yyyyMMddHHmm").Substring(2, 10), "2");
}
returnStr1 = manager.UpdatePassengerInfoToDevice(dt.Rows[0]["DeviceID"].ToString(), "", "", "", a.RRANationName, a.RRAStartDate.ToString("yyyyMMddHHmm").Substring(2, 10), a.RRAEndDate.ToString("yyyyMMddHHmm").Substring(2, 10), "4");
}
dic.Add("ret", "0");
dic.Add("msg", returnStr1);
}
else
{
dic.Add("ret", "1");
dic.Add("msg", "未发现智能锁信息，无法添加密码");
}
return(JSONHelper.ToJson(dic));
}
public void UpdateRentExternal(RentInfo rentInfo, bool isService)
{
string sql = "select * from Rent_External where rent_No='" + rentInfo.RentNo + "'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (dt.Rows.Count > 0)
{
sql = "update Rent_External set HZHouseID='" + rentInfo.RHouseID.ToString() + "',HZRentID='" + rentInfo.RRentID + "',HZUploadDate='" + DateTime.Now.ToString() + "',RentRealOwner='" + rentInfo.RRealOwner + "',RentRealOwnerID='" + rentInfo.RRealIDCard + "',RentRealOwnerPhone='" + rentInfo.RRealOwnerTel + "' where rent_No='" + rentInfo.RentNo + "'";
}
else
{
sql = "insert into Rent_External values ('" + rentInfo.RentNo + "','','" + rentInfo.RHouseID + "','" + rentInfo.RRentID + "','" + DateTime.Now.ToString() + "','" + rentInfo.ROwner + "','" + rentInfo.RIDCard + "','" + rentInfo.ROwnerTel + "')";
}
MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
}
public void UpdateRentExternal(RentInfo rentInfo)
{
string sql = "select * from Rent_External where rent_No='" + rentInfo.RentNo + "'";
DataTable dt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql)).Tables[0];
if (dt.Rows.Count > 0)
{
sql = "insert into Rent_External values ('" + rentInfo.RentNo + "','','" + rentInfo.RHouseID + "','" + rentInfo.RRentID + "','" + DateTime.Now.ToString() + "','" + rentInfo.ROwner + "','" + rentInfo.RIDCard + "','" + rentInfo.ROwnerTel + "')";
}
else
{
sql = "insert into Rent_External values ('" + rentInfo.RentNo + "','','','',null,'" + rentInfo.ROwner + "','" + rentInfo.RIDCard + "','" + rentInfo.ROwnerTel + "')";
}
MySQLHelper.ExecuteNonQuery(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sql));
}
