/// <summary>
/// 列表详情
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <VersionInfo> GetVersionList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY vi.CreateTime DESC) PageNum,* FROM VersionInfo vi) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageSize * pageIndex;
sql += " SELECT COUNT(0) FROM VersionInfo vi";
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql);
DataSetConvert convert = new DataSetConvert(ds);
List <VersionInfo> list = convert.Get_ListModel <VersionInfo>();
return(new PagedList <VersionInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "SystemDAL.GetVersionList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}
public PagedList <MacInfo> GetList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtMac = dic["AP_MAC"];
string txtCOLLECTION_EQUIPMENT_ID = dic["COLLECTION_EQUIPMENT_ID"];
string BeginTime = dic["BeginTime"];
string EndTime = dic["EndTime"];
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(txtMac))
{
whereSql += " AND AP_MAC LIKE '%" + txtMac + "%'";
}
if (!string.IsNullOrEmpty(txtCOLLECTION_EQUIPMENT_ID))
{
whereSql += " AND COLLECTION_EQUIPMENT_ID LIKE '%" + txtCOLLECTION_EQUIPMENT_ID + "%'";
}
if (!string.IsNullOrEmpty(BeginTime))
{
whereSql += " AND CreateTime >= '" + BeginTime + "'";
}
if (!string.IsNullOrEmpty(EndTime))
{
whereSql += " AND CreateTime <= '" + EndTime + "'";
}
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY CreateTime DESC) PageNum, * FROM DevInfo " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM DevInfo " + whereSql;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
DataSetConvert convert = new DataSetConvert(ds);
List <MacInfo> list = convert.Get_ListModel <MacInfo>();
return(new PagedList <MacInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "MacDAL.GetList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}
/// <summary>
/// 通过角色获取角色名称
/// </summary>
/// <param name="RoleId"></param>
/// <returns></returns>
public Sys_UserPowerInfo GetUserRoleName(int Jid)
{
string sql = "SELECT * FROM Sys_RoleInfo WHERE Id=@id";
DataSetConvert convert = new DataSetConvert(SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, new SqlParameter("@id", Jid)));
//return convert.Get_SingleModel<Sys_UserPowerInfo>();
Sys_UserPowerInfo sysu = SqlHelper.ExecuteModel <Sys_UserPowerInfo>(sql, new SqlParameter("@id", Jid));
return(sysu);
}
/// <summary>
/// 查询安全厂商列表管理信息
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <SecurityOrg> GetAQList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string ORGNAME = dic["ORGNAME"];
string ORGCODE = dic["ORGCODE"];
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(ORGNAME))
{
whereSql += " AND SECURITY_SOFTWARE_ORGNAME LIKE '%" + ORGNAME + "%'";
}
if (!string.IsNullOrEmpty(ORGCODE))
{
whereSql += " AND SECURITY_SOFTWARE_ORGCODE LIKE '%" + ORGCODE + "%'";
}
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY ID DESC) PageNum ,* FROM SecurityOrg " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
sql = " SELECT COUNT(0) from SecurityOrg " + whereSql;
int result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.DBConnStr, CommandType.Text, sql, param));
DataSetConvert convert = new DataSetConvert(ds);
List <SecurityOrg> list = convert.Get_ListModel <SecurityOrg>();
return(new PagedList <SecurityOrg>(list, pageIndex, pageSize, result));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "HBHtmlDAL.GetAQList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}
/// <summary>
/// 获取角色列表
/// </summary>
/// <returns></returns>
public List <Sys_UserPowerInfo> GetRoleList(int userType)
{
string sql = "SELECT * FROM Sys_RoleInfo where id IN (SELECT DISTINCT jid FROM Sys_RelaInfo) and UserType=" + userType;
//if (userType == 8)
// sql += " and UserType in (8,7,6,4,2,3)";
//else if (userType == 7)
// sql += " and UserType in (7,6,4,2,3)";
//else if (userType == 6)
// sql += " and UserType in (6,4,2,3)";
//else if (userType == 4)
// sql += " and UserType in (4,2,3)";
//else if (userType == 2)
// sql += " and UserType in (2,3)";
//else if (userType == 3)
// sql += " and UserType in (3)";
DataSetConvert convert = new DataSetConvert(SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql));
return(convert.Get_ListModel <Sys_UserPowerInfo>());
}
/// <summary>
/// 日志列表
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <OPLog> GetList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtName = dic["txtName"];
string selName = dic["selName"].ToString();
string txtIP = dic["txtAdderss"];
string selIP = dic["selAdderss"].ToString();
string beginTime = dic["CreateTime"];
string endTime = dic["EndTime"];
string what = dic["txtWhat"];
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE 1=1 ";
if (selName == "1" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND u.UserName LIKE '%" + txtName + "%'";
}
else if (selName == "2" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND u.UserName =@UserName";
listParam.Add(new SqlParameter("@UserName", txtName));
}
if (selIP == "1" && !string.IsNullOrEmpty(txtIP))
{
whereSql += " AND l.IpAddress LIKE '%" + txtIP + "%'";
}
else if (selIP == "2" && !string.IsNullOrEmpty(txtIP))
{
whereSql += " AND l.IpAddress =@IpAddress";
listParam.Add(new SqlParameter("@IpAddress", txtIP));
}
if (!string.IsNullOrEmpty(beginTime) && !string.IsNullOrEmpty(endTime))
{
whereSql += " AND l.CreateTime BETWEEN '" + beginTime + "' AND '" + endTime + "' ";
}
else
{
if (!string.IsNullOrEmpty(beginTime))
{
whereSql += " AND l.CreateTime > '" + beginTime + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
whereSql += " AND l.CreateTime < '" + endTime + "'";
}
}
if (!string.IsNullOrEmpty(what))
{
whereSql += " AND l.What LIKE '%" + ChangeValue.Base64Str(1, what) + "%'";
listParam.Add(new SqlParameter("@What", ChangeValue.Base64Str(1, what)));
}
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY l.ID DESC) PageNum ,l.*,u.UserName,l.What ShowWhat FROM OPLog l with(nolock) LEFT JOIN UserInfo u ON u.ID = l.UID " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM OPLog l LEFT JOIN UserInfo u ON u.ID = l.UID " + whereSql;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
DataSetConvert convert = new DataSetConvert(ds);
List <OPLog> list = convert.Get_ListModel <OPLog>();
return(new PagedList <OPLog>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "OPLogDAL.GetList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}
/// <summary>
/// DAL公用方法
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public object GetPersonWaringListDal(Dictionary <string, string> dic)
{
string sql = "";
string wheresql = " WHERE ci.CaseType=" + dic["CaseType"];
if (Convert.ToInt32(dic["UserType"]) != 1)
{
wheresql += " AND ci.Uid= " + dic["UserID"];
}
if (Convert.ToInt32(dic["selWaringType"]) != 0)
{
if (Convert.ToInt32(dic["selWaringType"]) == 1)
{
wheresql += " AND wi.WaringType=" + dic["selWaringType"];
}
else
{
wheresql += " AND wi.WaringType=" + dic["selWaringType"];
}
}
List <SqlParameter> ListParam = new List <SqlParameter>();
//类型1.人员报警，2.即时通讯报警，3.CCIC报警，4人员报警导出，5.即时通讯报警导出 ,6.CCIC报警导出
int ExcelType = Convert.ToInt32(dic["ExcelType"]);
//审计任务名称
if (dic.ContainsKey("selTaskName"))
{
if (!string.IsNullOrEmpty(dic["txtTaskName"]))
{
if (Convert.ToInt32(dic["selTaskName"]) == 1)
{
wheresql += " AND ci.TaskName LIKE @txtTaskName ";
ListParam.Add(new SqlParameter("@txtTaskName", "%" + dic["txtTaskName"] + "%"));
}
else
{
wheresql += " AND ci.TaskName = @txtTaskName ";
ListParam.Add(new SqlParameter("@txtTaskName", dic["txtTaskName"]));
}
}
}
//场所名称
if (dic.ContainsKey("selName"))
{
if (!string.IsNullOrEmpty(dic["txtName"]))
{
if (Convert.ToInt32(dic["selName"]) == 1)
{
wheresql += " AND nbi.PLACE_NAME LIKE @txtName ";
ListParam.Add(new SqlParameter("@txtName", "%" + dic["txtName"] + "%"));
}
else
{
wheresql += " AND nbi.PLACE_NAME =@txtName ";
ListParam.Add(new SqlParameter("@txtName", dic["txtName"]));
}
}
}
//手机号码
if (dic.ContainsKey("selMobile"))
{
if (!string.IsNullOrEmpty(dic["txtMobile"]))
{
if (Convert.ToInt32(dic["selMobile"]) == 1)
{
wheresql += " AND ci.CaseValue LIKE @txtMobile AND ci.CaseItem=1";
ListParam.Add(new SqlParameter("@txtMobile", "%" + dic["txtMobile"] + "%"));
}
else
{
wheresql += " AND ci.CaseValue = @txtMobile AND ci.CaseItem=1";
ListParam.Add(new SqlParameter("@txtMobile", dic["txtMobile"]));
}
}
}
//MAC地址
if (dic.ContainsKey("selMac"))
{
if (!string.IsNullOrEmpty(dic["txtMac"]))
{
if (Convert.ToInt32(dic["selMac"]) == 1)
{
wheresql += " AND ci.CaseValue LIKE @txtMac AND ci.CaseItem=2";
ListParam.Add(new SqlParameter("@txtMac", "%" + dic["txtMac"] + "%"));
}
else
{
wheresql += " AND ci.CaseValue = @txtMac AND ci.CaseItem=2";
ListParam.Add(new SqlParameter("@txtMac", dic["txtMac"]));
}
}
}
//虚拟身份标识(即时通讯标识)
if (dic.ContainsKey("selQQ"))
{
if (!string.IsNullOrEmpty(dic["txtQQ"]))
{
if (Convert.ToInt32(dic["selQQ"]) == 1)
{
wheresql += " AND ci.CaseValue LIKE @txtQQ";
ListParam.Add(new SqlParameter("@txtQQ", "%" + dic["txtQQ"] + "%"));
}
else
{
wheresql += " AND ci.CaseValue = @txtQQ";
ListParam.Add(new SqlParameter("@txtQQ", dic["txtQQ"]));
}
}
}
//IMEI
if (dic.ContainsKey("selIMEI"))
{
if (!string.IsNullOrEmpty(dic["txtIMEI"]))
{
if (Convert.ToInt32(dic["selIMEI"]) == 1)
{
wheresql += " AND ci.CaseValue LIKE @txtIMEI AND ci.CaseItem NOT IN(1,2)";
ListParam.Add(new SqlParameter("@txtIMEI", "%" + dic["txtIMEI"] + "%"));
}
else
{
wheresql += " AND ci.CaseValue = @txtIMEI AND ci.CaseItem NOT IN(1,2)";
ListParam.Add(new SqlParameter("@txtIMEI", "%" + dic["txtIMEI"] + "%"));
}
}
}
if (!string.IsNullOrEmpty(dic["keyWord"]))
{
if (dic["selkeyWord"] == "1")
{
wheresql += " AND ci.CaseValue LIKE @keyWord ";
ListParam.Add(new SqlParameter("@keyWord", "%" + dic["keyWord"] + "%"));
}
else
{
wheresql += " AND ci.CaseValue = @keyWord ";
ListParam.Add(new SqlParameter("@keyWord", dic["keyWord"]));
}
}
//IMSI
//if (dic.ContainsKey(dic["selIMSI"]))
//{
// if (!string.IsNullOrEmpty(dic["txtIMSI"]))
// {
// if (Convert.ToInt32(dic["selIMSI"]) == 1)
// {
// }
// else
// {
// }
// }
//}
//开始时间
if (!string.IsNullOrEmpty(dic["txtStartTime"]))
{
wheresql += " AND wi.Createtime>=@txtStartTime ";
ListParam.Add(new SqlParameter("@txtStartTime ", dic["txtStartTime"]));
}
//结束时间
if (!string.IsNullOrEmpty(dic["txtEndTime"]))
{
wheresql += " AND wi.Createtime<=@txtEndTime ";
ListParam.Add(new SqlParameter("@TxtEndTime", dic["txtEndTime"]));
}
SqlParameter[] param = ListParam.ToArray();
if (ExcelType == 1 || ExcelType == 3)
{
int pageIndex = Convert.ToInt32(dic["PageIndex"]);
int pageSize = Convert.ToInt32(dic["PageSize"]);
sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC)PageNum,* FROM (SELECT DISTINCT wi.*,ci.TaskName,ci.CaseType,ci.CaseItem,ci.CaseValue,ci.WMail,ci.NETWORK_APP,nbi.PLACE_NAME,nbi.SITE_ADDRESS FROM WaringInfo wi LEFT JOIN CasesInfo ci ON wi.Cid=ci.ID LEFT JOIN NetBarInfo nbi ON wi.NETBAR_ID=nbi.ID " + wheresql + ")table1)PageNum WHERE PageNum.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
sql = " SELECT COUNT(DISTINCT wi.ID) FROM WaringInfo wi LEFT JOIN CasesInfo ci ON wi.Cid=ci.ID LEFT JOIN NetBarInfo nbi ON wi.NETBAR_ID=nbi.ID " + wheresql;
int result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.DBConnStr, CommandType.Text, sql, param));
DataSetConvert convert = new DataSetConvert(ds);
List <BKBJ.WarningInfo> list = convert.Get_ListModel <BKBJ.WarningInfo>();
return(new PagedList <BKBJ.WarningInfo>(list, pageIndex, pageSize, result));
}
sql = " SELECT DISTINCT wi.*,ci.TaskName,ci.CaseType,ci.CaseItem,ci.CaseValue,ci.WMail,ci.NETWORK_APP,nbi.PLACE_NAME,nbi.SITE_ADDRESS FROM WaringInfo wi LEFT JOIN CasesInfo ci ON wi.Cid=ci.ID LEFT JOIN NetBarInfo nbi ON wi.NETBAR_ID=nbi.ID " + wheresql;
DataSet ex = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
return(ex);
}
public PagedList <Video3CInfo> GetVideo3CList(Dictionary <string, string> dic)
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string UserType = dic["UserType"].ToString(); //用户类型
string AreaId = dic["AreaId"].ToString(); //用户区域
string whereSql = " where 1=1 ";
List <SqlParameter> listParam = new List <SqlParameter>();
if (UserType == "6")
{
whereSql += " AND nbi.ProID=" + AreaId;
}
else if (UserType == "4")
{
whereSql += " AND nbi.CityID=" + AreaId;
}
else if (UserType == "2")
{
whereSql += " AND nbi.Aid=" + AreaId;
}
if (!string.IsNullOrEmpty(dic["txtMac"]))
{
whereSql += " and di.AP_MAC like @DeviceMac";
listParam.Add(new SqlParameter("@DeviceMac", "%" + dic["txtMac"] + "%"));
}
if (!string.IsNullOrEmpty(dic["txtAPName"]))
{
whereSql += " and di.APName like @APName";
listParam.Add(new SqlParameter("@APName", "%" + dic["txtAPName"] + "%"));
}
if (!string.IsNullOrEmpty(dic["txtLName"]))
{
whereSql += " and nbi.PLACE_NAME like @Name";
listParam.Add(new SqlParameter("@Name", "%" + dic["txtLName"] + "%"));
}
if (!string.IsNullOrEmpty(dic["txtNETBAR_WACODE"]))
{
whereSql += " and nbi.NETBAR_WACODE like @NETBAR_WACODE";
listParam.Add(new SqlParameter("@NETBAR_WACODE", "%" + dic["txtNETBAR_WACODE"] + "%"));
}
if (!string.IsNullOrEmpty(dic["txtBeginTime"]))
{
whereSql += " and vc.BeginTime >= @BeginTime"; listParam.Add(new SqlParameter("@BeginTime", dic["txtBeginTime"]));
}
if (!string.IsNullOrEmpty(dic["txtEndTime"]))
{
whereSql += " and vc.BeginTime <= @EndTime"; listParam.Add(new SqlParameter("@EndTime", dic["txtEndTime"]));
}
string sql = "select * from (SELECT ROW_NUMBER() OVER ( ORDER BY vc.ID DESC) PageNum, vc.*,nbi.PLACE_NAME LName,di.APName FROM Video3CInfo vc LEFT JOIN NetBarInfo nbi ON vc.NETBAR_ID=nbi.ID LEFT JOIN DevInfo di ON di.ID=vc.DevAP_ID " + whereSql;
sql += ") temp where temp.PageNum between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize;
sql += " select count(0) FROM Video3CInfo vc LEFT JOIN NetBarInfo nbi ON vc.NETBAR_ID=nbi.ID LEFT JOIN DevInfo di ON di.ID=vc.DevAP_ID " + whereSql;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, listParam.ToArray());
List <Video3CInfo> list = new DataSetConvert(ds).Get_ListModel <Video3CInfo>();
return(new PagedList <Video3CInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
/// <summary>
/// 警区列表
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <ScenicInfo> GetScenicInfoList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtName = dic["txtName"];
string selName = dic["selName"].ToString();
string txtMobile = dic["txtMobile"];
string selMobile = dic["selMobile"].ToString();
string txtAddress = dic["txtAddress"];
string selAddress = dic["selAddress"].ToString();
string txtContact = dic["txtContact"];
string selContact = dic["selContact"].ToString();
int pid = Convert.ToInt32(dic["PId"]);
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE s.Valid=1 ";
if (selName == "1" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND s.SName LIKE '%" + txtName + "%'";
}
if (selName == "2" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND s.SName=@Name";
listParam.Add(new SqlParameter("@Name", txtName));
}
if (selMobile == "1" && !string.IsNullOrEmpty(txtMobile))
{
whereSql += " AND s.Mobile LIKE '%" + txtMobile + "%'";
}
if (selMobile == "2" && !string.IsNullOrEmpty(txtMobile))
{
whereSql += " AND s.Mobile=@Mobile";
listParam.Add(new SqlParameter("@Mobile", txtMobile));
}
if (selAddress == "1" && !string.IsNullOrEmpty(txtAddress))
{
whereSql += " AND s.Address LIKE '%" + txtAddress + "%'";
}
if (selAddress == "2" && !string.IsNullOrEmpty(txtAddress))
{
whereSql += " AND s.Address=@Address";
listParam.Add(new SqlParameter("@Address", txtAddress));
}
if (selContact == "1" && !string.IsNullOrEmpty(txtContact))
{
whereSql += " AND s.Contact LIKE '%" + txtContact + "%'";
}
if (selContact == "2" && !string.IsNullOrEmpty(txtContact))
{
whereSql += " AND s.Contact=@Contact";
listParam.Add(new SqlParameter("@Contact", txtContact));
}
whereSql += " AND s.PID=@PID";
listParam.Add(new SqlParameter("@PID", pid));
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY s.CreateTime DESC) PageNum, s.* FROM ScenicInfo s " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM ScenicInfo s " + whereSql;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
DataSetConvert convert = new DataSetConvert(ds);
List <ScenicInfo> list = convert.Get_ListModel <ScenicInfo>();
return(new PagedList <ScenicInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "SystemDAL.GetScenicInfoList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}
/// <summary>
///列表详细
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <AreaInfo> GetAreaManage(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtName = dic["txtName"];
string selName = dic["selName"].ToString();
string CityType = dic["CityType"].ToString();
string ProId = dic.ContainsKey("ProId") ? dic["ProId"].ToString() : "";
string Cid = dic.ContainsKey("Cid") ? dic["Cid"].ToString() : "";
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE ai.CityType=" + CityType;
if (ProId != "")
{
whereSql += " AND ai.Pid = " + ProId;
}
if (Cid != "")
{
whereSql += " AND ai.Pid = " + Cid;
}
if (selName == "1" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND ai.Area LIKE '%" + txtName + "%'";
}
if (selName == "2" && !string.IsNullOrEmpty(txtName))
{
whereSql += " AND ai.Area=@Name";
listParam.Add(new SqlParameter("@Name", txtName));
}
SqlParameter[] param = listParam.ToArray();
//if (CityType == "2")
//{
// sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY ai.ID DESC) PageNum,ai.ID, ai.Area, ai.CreateTime, ai.City_code, ai.CityType from AreaInfo ai " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
// sql += " SELECT COUNT(0) from AreaInfo ai " + whereSql;
//}
//else
//{
// sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY ai.ID DESC) PageNum,ai2.Area PName,ai.ID,ai.Area,ai.CreateTime,ai.Pid,ai.ProID,ai.City_code FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
// sql += " SELECT COUNT(0) FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql;
//}
if (CityType == "2")
{
sql = "SELECT* FROM (SELECT ROW_NUMBER() OVER(ORDER BY ai.ID DESC) PageNum, ai.ID, ai.Area, ai.CreateTime, ai.City_code,ai.CityType,(SELECT COUNT(0) FROM AreaInfo AS ai1 WHERE ai1.Pid = ai.ID)Num from AreaInfo ai " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) from AreaInfo ai " + whereSql;
}
else if (CityType == "1")
{
sql = "SELECT* FROM (SELECT ROW_NUMBER() OVER(ORDER BY ai.ID DESC) PageNum, ai2.Area PName,ai.ID,ai.Area,ai.CreateTime,ai.Pid,ai.ProID,ai.City_code,(SELECT COUNT(0) FROM AreaInfo AS ai1 WHERE ai1.Pid = ai.ID)Num FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql;
}
else if (CityType == "0")
{
sql = "SELECT* FROM (SELECT ROW_NUMBER() OVER(ORDER BY ai.ID DESC) PageNum,ai2.Area PName,ai.ID, ai.Area, ai.CreateTime, ai.City_code,(SELECT COUNT(0) FROM PoliceInfo AS pi1 WHERE pi1.Aid = ai.ID AND pi1.Valid = 1)Num FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
sql += " SELECT COUNT(0) FROM AreaInfo ai LEFT JOIN AreaInfo ai2 on ai.Pid=ai2.ID " + whereSql;
}
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
DataSetConvert convert = new DataSetConvert(ds);
List <AreaInfo> list = convert.Get_ListModel <AreaInfo>();
return(new PagedList <AreaInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0])));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询数据输出列表管理信息
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public PagedList <OutPutInfo> GetList(Dictionary <string, string> dic)
{
string sql = "";
try
{
int pageIndex = Convert.ToInt32(dic["pageIndex"]);
int pageSize = Convert.ToInt32(dic["pageSize"]);
string txtUserId = dic["UserId"];
string txtContact = dic["Contact"];
string txtQQ = dic["QQ"];
string txtDestination = dic["Destination"];
string selPattern = dic["Pattern"];
List <SqlParameter> listParam = new List <SqlParameter>();
string whereSql = " WHERE 1=1 ";
if (!string.IsNullOrEmpty(txtUserId))
{
whereSql += " AND UserId LIKE '%" + txtUserId + "%'";
}
if (!string.IsNullOrEmpty(txtContact))
{
whereSql += " AND Contact LIKE '%" + txtContact + "%'";
}
if (!string.IsNullOrEmpty(txtQQ))
{
whereSql += " AND QQ LIKE '%" + txtQQ + "%'";
}
if (!string.IsNullOrEmpty(txtDestination))
{
whereSql += " AND Destination LIKE '%" + txtDestination + "%'";
}
if (selPattern != "0")
{
whereSql += " AND Pattern = @Pattern ";
listParam.Add(new SqlParameter("@Pattern", selPattern));
}
SqlParameter[] param = listParam.ToArray();
sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY EstablishTime DESC) PageNum ,* FROM OutPutInfo " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize;
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param);
sql = " SELECT COUNT(0) from OutPutInfo " + whereSql;
int result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.DBConnStr, CommandType.Text, sql, param));
DataSetConvert convert = new DataSetConvert(ds);
List <OutPutInfo> list = convert.Get_ListModel <OutPutInfo>();
return(new PagedList <OutPutInfo>(list, pageIndex, pageSize, result));
}
catch (Exception ex)
{
Logger.ErrorLog(ex, new Dictionary <string, string>()
{
{ "Function", "HBHtmlDAL.GetList(Dictionary<string,string> dic)" },
{ "SQL", sql }
});
throw ex;
}
}