public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select entity.* from View_AllEntity entity ");
sbSql4org.Append(" where 1=1 ");
if (ht.ContainsKey("EntityName") && !string.IsNullOrEmpty(ht["EntityName"].ToString()))
{
sbSql4org.Append(" and entity.EntityName like N'%' + @EntityName + '%' ");
pms.Add(new SqlParameter("@EntityName", ht["EntityName"].ToString()));
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select *
,(select count(1) from TPM_AcheiveRewardInfo where IsDelete=0 and (Status=5 or Status>6) and Rid=R_Info.Id)ScoreCount
from TPM_RewardInfo R_Info where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("LID") && !string.IsNullOrEmpty(ht["LID"].SafeToString()))
{
str.Append(" and LID=" + ht["LID"].SafeToString());
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and Id=" + ht["Id"].SafeToString());
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "Sort", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select room.*,build.Name as BuildName
,(select count(1) from Dorm_RoomStuList where RoomId=room.Id) as MemCount
,(select count(1) from Com_NewInfo where type=2 and RelationId=room.Id) as NewCount ");
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
sbSql4org.Append(@" ,case when room.ManagerNo = @LoginUID then 1 else 0 end as IsLeader");
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
}
sbSql4org.Append(@" from Dorm_Room room
left join Dorm_Building build on room.BuildId=build.Id ");
sbSql4org.Append(@" where 1=1 ");
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and room.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("IsDelete") && !string.IsNullOrEmpty(ht["IsDelete"].ToString()))
{
sbSql4org.Append(" and room.IsDelete=@IsDelete ");
pms.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and room.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("BuildId") && !string.IsNullOrEmpty(ht["BuildId"].ToString()))
{
sbSql4org.Append(" and room.BuildId=@BuildId ");
pms.Add(new SqlParameter("@BuildId", ht["BuildId"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public virtual DataTable GetListByPage(Hashtable ht, string fileds, out int RowCount, bool IsPage = true, string Where = "")// string TableName, string strWhere, string orderby, int startIndex, int endIndex, SqlParameter[] parms4org)
{
RowCount = 0;
SqlParameter[] parms4org = { };
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
string order = "";
if (ht.ContainsKey("Order") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["Order"])))
{
order = ht["Order"].ToString();
}
DataTable dt = SQLHelp.GetListByPage((string)ht["TableName"], fileds, Where, order, StartIndex, EndIndex, IsPage, parms4org, out RowCount);
//DataTable dt = SQLHelp.GetListByPage(ht, parms4org);
return(dt);
}
catch (Exception ex)
{
LogHelper.Error(ex);
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select * from TPM_AcheiveLevel where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Pid") && !string.IsNullOrEmpty(ht["Pid"].SafeToString()))
{
str.Append(" and Pid=" + ht["Pid"].SafeToString());
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and Id=" + ht["Id"].SafeToString());
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "Sort", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select a.*,b.Name as VersionName,c.Name as SubName,d.Name as MajorName from Edu_Book a inner join Edu_BookVersion b on a.VersionID=b.ID inner join Edu_SubJect c on a.SubID=c.ID inner join Edu_MajorInfo d on d.ID=a.MajorID where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and a.Name like '%" + ht["Name"].SafeToString() + "%'");
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
int StartIndex = 0;
int EndIndex = 0;
if (ht["IsPage"].SafeToString().Length > 0)
{
IsPage = Convert.ToBoolean(ht["IsPage"]);
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].SafeToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].SafeToString());
}
StringBuilder sb = new StringBuilder();
sb.Append("select distinct ID,Name from Book_Version where 1=1 ");
if (ht.ContainsKey("SubjectName") && !string.IsNullOrEmpty(ht["SubjectName"].SafeToString()))
{
sb.Append(" and ID in (select VersionID from Book_Subject where Name='" + ht["SubjectName"] + "')");
}
dt = SQLHelp.GetListByPage("(" + sb.ToString() + ")", where, "", StartIndex, EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
bool isroleid = ht.ContainsKey("RoleId");
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select mi.*,(select count(1) from Sys_MenuInfo where Pid=mi.Id) as ChildCount ");
if (isroleid)
{
sbSql4org.Append(@" ,ISNULL(rm.MenuId,0) as ischeck,
(select STUFF((select ','+CAST(ISNULL(relbtn.MenuId,0)AS NVARCHAR(MAX))+'|'+CAST(mbtn.Id AS NVARCHAR(MAX))+'|'+ CAST(Name AS NVARCHAR(MAX)) from Sys_MenuInfo mbtn
left join Sys_RoleOfMenu relbtn on mbtn.Id = relbtn.MenuId and relbtn.RoleId=@RoleId where Pid = mi.Id and IsMenu = 1 ORDER By Name FOR xml path('')), 1, 1, '')) as ButtonField ");
pms.Add(new SqlParameter("@RoleId", ht["RoleId"].ToString()));
}
sbSql4org.Append(@" from Sys_MenuInfo mi ");
if (isroleid)
{
sbSql4org.Append(" left join Sys_RoleOfMenu rm on mi.Id=rm.MenuId and rm.RoleId=@RoleId ");
}
sbSql4org.Append(@" where 1=1 and mi.IsShow!=0 ");
if (ht.ContainsKey("Pid") && !string.IsNullOrEmpty(ht["Pid"].ToString()))
{
sbSql4org.Append(" and mi.Pid=@Pid ");
pms.Add(new SqlParameter("@Pid", ht["Pid"].ToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and mi.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("IsMenu") && !string.IsNullOrEmpty(ht["IsMenu"].ToString()))
{
sbSql4org.Append(" and mi.IsMenu=@IsMenu ");
pms.Add(new SqlParameter("@IsMenu", ht["IsMenu"].ToString()));
}
if (ht.ContainsKey("IsShow") && !string.IsNullOrEmpty(ht["IsShow"].ToString()))
{
sbSql4org.Append(" and mi.IsShow=@IsShow ");
pms.Add(new SqlParameter("@IsShow", ht["IsShow"].ToString()));
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, "Sort", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@" select r_bat.*,(select count(1) from TPM_AuditReward where IsDelete=0 and Status in(1,3) and RewardBatch_Id=r_bat.Id)UseCount ");
if (ht["IsOnlyBase"].SafeToString() == "1") //查询关联表
{
str.Append(@" ,info.Id AchieveId,isnull(aud.Id,0) AuditId,isnull(aud.Status,0)AuditStatus
,isnull((select sum(AllotMoney) from TPM_AllotReward where Audit_Id=aud.Id),0)HasAllot
from TPM_RewardBatch r_bat
left join TPM_AcheiveRewardInfo info on r_bat.Reward_Id=info.Rid and info.IsDelete=0
left join TPM_AuditReward aud on r_bat.Id=aud.RewardBatch_Id and info.Id=aud.Acheive_Id and aud.IsDelete=0 ");
}
else //只查询基础表
{
str.Append(@" from TPM_RewardBatch r_bat ");
}
str.Append(@" where r_bat.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Reward_Id") && !string.IsNullOrEmpty(ht["Reward_Id"].SafeToString()))
{
str.Append(" and r_bat.Reward_Id=@Reward_Id ");
pms.Add(new SqlParameter("@Reward_Id", ht["Reward_Id"].ToString()));
}
if (ht.ContainsKey("AchieveId") && !string.IsNullOrEmpty(ht["AchieveId"].SafeToString()))
{
str.Append(" and info.Id=@AchieveId ");
pms.Add(new SqlParameter("@AchieveId", ht["AchieveId"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and r_bat.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("AuditStatus") && !string.IsNullOrEmpty(ht["AuditStatus"].SafeToString()))
{
str.Append(" and isnull(aud.Status,0) " + ht["AuditStatus"].ToString());
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
string orderby = "Id";
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int rowCount, bool IsPage = true, string Where = "")
{
List <SqlParameter> pms = new List <SqlParameter>();
rowCount = 0;
try
{
StringBuilder sbSql4org = new StringBuilder();
if (ht.ContainsKey("sel_Type") && !string.IsNullOrEmpty(ht["sel_Type"].ToString()))
{
if (ht["sel_Type"].ToString() == "学生")
{
sbSql4org.Append(@"select distinct U.Id,U.LoginName,U.Name,U.Sex,U.IDCard,'学生' as UserType from Plat_Student U where U.IDCard != '00000000000000000X' and IsDelete=0");
}
else
{
sbSql4org.Append(@"select distinct U.Id,U.LoginName,U.Name,U.Sex,U.IDCard,'普通用户' as UserType from Plat_Teacher U where U.IDCard != '00000000000000000X' and IsDelete=0");
}
}
else
{
sbSql4org.Append(@"select distinct U.* from(
select uinfo.Id,uinfo.LoginName,uinfo.Name,uinfo.Sex,uinfo.IDCard,'普通用户' as UserType from Plat_Teacher uinfo
union
select uinfo.Id,uinfo.LoginName,uinfo.Name,uinfo.Sex,uinfo.IDCard,'学生' as UserType from Plat_Student uinfo
) U
where U.IDCard != '00000000000000000X' ");
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and U.Name like N'%'+@Name+'%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("RoleId") && !string.IsNullOrEmpty(ht["RoleId"].ToString()))
{
sbSql4org.Append(" and U.IDCard ");
if (ht.ContainsKey("JoinStr") && !string.IsNullOrEmpty(ht["JoinStr"].ToString()))
{
sbSql4org.Append(ht["JoinStr"].ToString());
}
else
{
sbSql4org.Append(" in ");
}
sbSql4org.Append(" (select UserIDCard from Plat_RoleOfUser where RoleId=@RoleId) ");
pms.Add(new SqlParameter("@RoleId", ht["RoleId"].ToString()));
}
DataTable dt = SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", "", "", Convert.ToInt32(ht["StartIndex"].ToString()), Convert.ToInt32(ht["EndIndex"].ToString()), IsPage, pms.ToArray(), out rowCount);
return(dt);
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
/// <summary>
/// 分页获取用户访问记录信息
/// </summary>
/// <param name="ht"></param>
/// <param name="RowCount"></param>
/// <param name="IsPage"></param>
/// <param name="Where"></param>
/// <returns></returns>
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
int StartIndex = 0;
int EndIndex = 0;
str.Append(@"select * from UserSkimLog where 1=1 ");
if (ht.ContainsKey("WebSite") && !string.IsNullOrEmpty(ht["WebSite"].SafeToString()))
{
str.Append(" and WebSite = " + ht["WebSite"].SafeToString());
}
if (ht.ContainsKey("MinLong") && !string.IsNullOrEmpty(ht["MinLong"].SafeToString()))
{
str.Append(" and SkinLong>" + ht["MinLong"].SafeToString());
}
if (ht.ContainsKey("MaxLong") && !string.IsNullOrEmpty(ht["MaxLong"].SafeToString()))
{
str.Append(" and SkinLong <" + ht["MaxLong"].SafeToString());
}
if (ht.ContainsKey("MinTime") && !string.IsNullOrEmpty(ht["MinTime"].SafeToString()))
{
str.Append(" and CreateTime> '" + ht["MinTime"].SafeToString() + "'");
}
if (ht.ContainsKey("MaxTime") && !string.IsNullOrEmpty(ht["MaxTime"].SafeToString()))
{
str.Append(" and CreateTime< '" + ht["MaxTime"].SafeToString() + "'");
}
if (ht.ContainsKey("ToUrl") && !string.IsNullOrWhiteSpace(ht["ToUrl"].SafeToString()))
{
str.Append(" and ToUrl like '%" + ht["ToUrl"].SafeToString() + "%'");
}
if (ht.ContainsKey("UserName") && !string.IsNullOrWhiteSpace(ht["UserName"].SafeToString()))
{
str.Append(" and UserName='******'");
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@" select allot.*,aud.Acheive_Id,aud.RewardBatch_Id,aud.Status
,ba.Year as BatYear,ba.Name as BatName
from TPM_AllotReward allot
left join TPM_RewardBatchDetail aud on allot.BatchDetail_Id=aud.Id
left join TPM_RewardBatch ba on aud.RewardBatch_Id=ba.Id
where allot.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("RewardBatch_Id") && !string.IsNullOrEmpty(ht["RewardBatch_Id"].SafeToString()))
{
str.Append(" and aud.RewardBatch_Id=@RewardBatch_Id ");
pms.Add(new SqlParameter("@RewardBatch_Id", ht["RewardBatch_Id"].ToString()));
}
if (ht.ContainsKey("BatchDetail_Id") && !string.IsNullOrEmpty(ht["BatchDetail_Id"].SafeToString()))
{
str.Append(" and allot.BatchDetail_Id=@BatchDetail_Id ");
pms.Add(new SqlParameter("@BatchDetail_Id", ht["BatchDetail_Id"].ToString()));
}
if (ht.ContainsKey("Acheive_Id") && !string.IsNullOrEmpty(ht["Acheive_Id"].SafeToString()))
{
str.Append(" and aud.Acheive_Id=@Acheive_Id ");
pms.Add(new SqlParameter("@Acheive_Id", ht["Acheive_Id"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and allot.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("No_Status") && !string.IsNullOrEmpty(ht["No_Status"].SafeToString()))
{
str.Append(" and aud.Status!=@No_Status");
pms.Add(new SqlParameter("@No_Status", ht["No_Status"].ToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@" select rec.*,rea.EditReason from TPM_ModifyRecord rec
left join TPM_ModifyReason rea on rec.Reason_Id=rea.Id and rea.IsDelete=0
where rec.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Acheive_Id") && !string.IsNullOrEmpty(ht["Acheive_Id"].SafeToString()))
{
str.Append(" and rec.Acheive_Id=@Acheive_Id ");
pms.Add(new SqlParameter("@Acheive_Id", ht["Acheive_Id"].ToString()));
}
if (ht.ContainsKey("ModifyUID") && !string.IsNullOrEmpty(ht["ModifyUID"].SafeToString()))
{
str.Append(" and rec.ModifyUID=@ModifyUID ");
pms.Add(new SqlParameter("@ModifyUID", ht["ModifyUID"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and rec.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Type") && !string.IsNullOrEmpty(ht["Type"].SafeToString()))
{
str.Append(" and rec.Type=@Type ");
pms.Add(new SqlParameter("@Type", ht["Type"].ToString()));
}
if (ht.ContainsKey("RelationId") && !string.IsNullOrEmpty(ht["RelationId"].SafeToString()))
{
str.Append(" and rec.RelationId=@RelationId ");
pms.Add(new SqlParameter("@RelationId", ht["RelationId"].ToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
string orderby = "Id";
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select fav.* from Com_Favorites fav ");
sbSql4org.Append(@" where 1=1 and fav.IsDelete=0 ");
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and fav.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
//0社团；1部门；2宿舍
if (ht.ContainsKey("Type") && !string.IsNullOrEmpty(ht["Type"].ToString()))
{
sbSql4org.Append(" and fav.Type=@Type ");
pms.Add(new SqlParameter("@Type", ht["Type"].ToString()));
}
if (ht.ContainsKey("RelationId") && !string.IsNullOrEmpty(ht["RelationId"].ToString()))
{
sbSql4org.Append(" and fav.RelationId=@RelationId ");
pms.Add(new SqlParameter("@RelationId", ht["RelationId"].ToString()));
}
if (ht.ContainsKey("CreateUID") && !string.IsNullOrEmpty(ht["CreateUID"].ToString()))
{
sbSql4org.Append(" and fav.CreateUID=@CreateUID ");
pms.Add(new SqlParameter("@CreateUID", ht["CreateUID"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select a.*,b.Academic,c.GradeName,r.SectionID,c.ID as GID,uinfo.Name as TeaName from Sys_ClassInfo a inner join Grad_Class_rel R on a.ClassNO=R.ClassNo
inner join Sys_StudySection b on R.SectionID=b.Id inner join Sys_GradeInfo c
on R.GradeID=c.ID left join Sys_UserInfo uinfo on a.HeadteacherNO=uinfo.UniqueNo where 1=1");
int StartIndex = 0;
int EndIndex = 0;
string AcademicId = "";
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and a.ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("AcademicId") && !string.IsNullOrEmpty(ht["AcademicId"].SafeToString()))
{
AcademicId = ht["AcademicId"].SafeToString();
}
else
{
AcademicId = dal.GetCurrentTerm();
}
str.Append(" and R.SectionID = " + AcademicId + " and c.AcademicId = " + AcademicId);
if (ht.ContainsKey("ClassName") && !string.IsNullOrEmpty(ht["ClassName"].SafeToString()))
{
str.Append(" and a.ClassName like '%" + ht["ClassName"].SafeToString() + "%'");
}
if (ht.ContainsKey("HeadteacherNO") && !string.IsNullOrEmpty(ht["HeadteacherNO"].SafeToString()))
{
str.Append(" and HeadteacherNO like '%" + ht["HeadteacherNO"].SafeToString() + "%'");
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
string DocName = (string)ht["DocName"];
string GroupName = ht["GroupName"].SafeToString();
string Postfixs = ht["Postfixs"].SafeToString();
//string CatagoryID = ht["CatagoryID"].SafeToString();
string ChapterID = ht["ChapterID"].SafeToString();
string IDCard = ht["IDCard"].SafeToString();
int StartIndex = 0;
int EndIndex = 0;
StringBuilder sb = new StringBuilder();
sb.Append("select re.ID,Name+postfix as Name,FileUrl,re.CreateTime,FileSize,FileGroup,postfix,detail.ClickNum,CASE postfix WHEN '' THEN 'file' else right(postfix,LEN(postfix) - 1) end as postfix1 from ResourcesInfo re left join ClickDetail detail on re.ID=detail.ResourcesID and detail.ClickType=2 and detail.CreateUID='" +
IDCard + "' where 1=1 ");
if (DocName.SafeToString().Length > 0)
{
sb.Append(" and re.Name like '%" + DocName + "%'");
}
if (GroupName.Length > 0)
{
sb.Append(" and FileGroup = '" + GroupName + "'");
}
if (Postfixs.Length > 0)
{
sb.Append(" and postfix in (" + GetPostfixs(int.Parse(Postfixs)) + ")");
}
if (ChapterID.Length > 0)
{
sb.Append(" and re.ChapterID=" + ChapterID);
}
//else if (ChapterID.Length == 0 && CatagoryID.Length > 0)
//{
// sb.Append(" and '|'+re.CatagoryID+'|' like '|" + CatagoryID + "|%'");
//}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + sb.ToString() + ")", where, "", StartIndex, EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return dt;
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@" select r_bat.*,b.Name as CreateName,(select count(1) from TPM_RewardBatchDetail where IsDelete=0 and Status in(1,3) and RewardBatch_Id=r_bat.Id)UseCount
,(select ISNULL(sum(Money),0) from TPM_RewardBatchDetail where IsDelete=0 and RewardBatch_Id=r_bat.Id)UseMoney ");
str.Append(@" from TPM_RewardBatch r_bat
left join UserInfo b on r_bat.CreateUID=b.UniqueNo where r_bat.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and r_bat.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Year") && !string.IsNullOrEmpty(ht["Year"].SafeToString()))
{
str.Append(" and r_bat.Year like N'%' + @Year + '%'");
pms.Add(new SqlParameter("@Year", ht["Year"].ToString().Replace("年", "")));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and r_bat.Name like N'%' + @Name + '%'");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("IsMoneyAllot") && !string.IsNullOrEmpty(ht["IsMoneyAllot"].SafeToString()))
{
str.Append(" and r_bat.IsMoneyAllot=@IsMoneyAllot ");
pms.Add(new SqlParameter("@IsMoneyAllot", ht["IsMoneyAllot"].ToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
string orderby = "Id";
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select a.*,b.Academic from Sys_GradeInfo a inner join Sys_StudySection b on a.AcademicId=b.id where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and a.ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("Major") && !string.IsNullOrEmpty(ht["Major"].SafeToString()))
{
str.Append(" and a.MajorID = " + ht["Major"].SafeToString());
}
if (ht.ContainsKey("AcademicId") && !string.IsNullOrEmpty(ht["AcademicId"].SafeToString()))
{
str.Append(" and a.AcademicId = " + ht["AcademicId"].SafeToString());
}
else
{
str.Append(" and a.AcademicId = " + GetCurrentTerm());
}
if (ht.ContainsKey("GradeName") && !string.IsNullOrEmpty(ht["GradeName"].SafeToString()))
{
str.Append(" and a.GradeName like '%" + ht["GradeName"].SafeToString() + "%'");
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
{/*
#region 分页获取模块信息重写
* /// <summary>
* /// 分页获取模块信息重写
* /// </summary>
* /// <param name="ht"></param>
* /// <param name="IsPage"></param>
* /// <param name="where"></param>
* /// <returns></returns>
* public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
* {
* RowCount = 0;
* DataTable dt = new DataTable();
* try
* {
* StringBuilder str = new StringBuilder();
* str.Append(@"select model.*,catory.Name as CatogryName,catory.ID as CatogryID,catory.SortNum from ModelManage model inner join ModelCatogory catory on model.ModelType=catory.ID where catory.Status=1");
*
* int StartIndex = 0;
* int EndIndex = 0;
* string Where = "";
* if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
* {
* str.Append(" and model.ID = " + ht["ID"].SafeToString());
* }
* if (ht.ContainsKey("ModelType") && !string.IsNullOrEmpty(ht["ModelType"].SafeToString()))
* {
* str.Append(" and model.ModelType=" + ht["ModelType"].SafeToString());
* }
* if (ht.ContainsKey("IDCard") && !string.IsNullOrEmpty(ht["IDCard"].SafeToString()))
* {
* str.Append(" and model.ID " + ht["IsShow"] + " (select ModelID from User_Model_Rel where UserIDCard='" + ht["IDCard"] + "')");
* }
* if (IsPage)
* {
* StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
* EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
* }
* dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, " SortNum,ID", StartIndex, EndIndex, IsPage, null, out RowCount);
* }
* catch (Exception ex)
* {
* LogService.WriteErrorLog(ex.Message);
* }
* return dt;
* }
#endregion
* */
#region 分页获取模块信息重写
/// <summary>
/// 分页获取模块信息重写
/// </summary>
/// <param name="ht"></param>
/// <param name="IsPage"></param>
/// <param name="where"></param>
/// <returns></returns>
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
//str.Append(@"select model.*,catory.Name as CatogryName,catory.ID as CatogryID,catory.SortNum from ModelManage model inner join ModelCatogory catory on model.ModelType=catory.ID where catory.Status=1");
str.Append(@"select distinct model.*,(select count(1) from ModelManage where Pid=model.Id) as ChildCount,catory.Name as CatogryName,catory.ID as CatogryID,catory.SortNum from Plat_RoleOfUser ru inner join Plat_RoleOfMenu rm on ru.RoleId=rm.RoleId inner join ModelManage model on model.Id=rm.MenuId
inner join ModelCatogory catory on model.ModelType=catory.ID where catory.Status=1 and model.Id is not null");// and model.Pid=0");
int StartIndex = 0;
int EndIndex = 0;
string Where = "";
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and model.ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("Pid") && !string.IsNullOrEmpty(ht["Pid"].SafeToString()))
{
str.Append(" and model.Pid = " + ht["Pid"].SafeToString());
}
if (ht.ContainsKey("ModelType") && !string.IsNullOrEmpty(ht["ModelType"].SafeToString()))
{
str.Append(" and model.ModelType=" + ht["ModelType"].SafeToString());
}
if (ht.ContainsKey("IDCard") && !string.IsNullOrEmpty(ht["IDCard"].SafeToString()))
{
str.Append(" and ru.UserIDCard='" + ht["IDCard"] + "'");
if (ht.ContainsKey("IsShow") && !string.IsNullOrEmpty(ht["IsShow"].SafeToString()))
{
str.Append(" and model.ID " + ht["IsShow"] + " (select ModelID from User_Model_Rel where UserIDCard='" + ht["IDCard"] + "')");
}
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, " SortNum,OrderNum,ID", StartIndex, EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
/// <summary>
/// 分页获取课程信息重写
/// </summary>
/// <param name="ht"></param>
/// <param name="IsPage"></param>
/// <param name="where"></param>
/// <returns></returns>
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select * from Course a where 1=1");
int StartIndex = 0;
int EndIndex = 0;
string Where = "";
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and a.ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("OperSymbol") && !string.IsNullOrEmpty(ht["OperSymbol"].SafeToString()))
{
str.Append(" and a.EndTime" + ht["OperSymbol"].SafeToString() + "'" + DateTime.Now + "'");
}
if (ht.ContainsKey("StudyTerm") && !string.IsNullOrEmpty(ht["StudyTerm"].SafeToString()))
{
str.Append(" and a.StudyTerm=" + ht["StudyTerm"].SafeToString());
}
if (ht.ContainsKey("CourseType") && !string.IsNullOrEmpty(ht["CourseType"].SafeToString()))
{
str.Append(" and a.CourceType=" + ht["CourseType"].SafeToString() + "");
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and a.Name like '%" + ht["Name"].SafeToString() + "%'");
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return dt;
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select log.* from Sys_LogInfo log ");
sbSql4org.Append(" where 1=1 ");
if (ht.ContainsKey("OperationMsg") && !string.IsNullOrEmpty(ht["OperationMsg"].ToString()))
{
sbSql4org.Append(" and log.OperationMsg like N'%' + @OperationMsg + '%' ");
pms.Add(new SqlParameter("@OperationMsg", ht["OperationMsg"].ToString()));
}
if (ht.ContainsKey("LogType") && !string.IsNullOrEmpty(ht["LogType"].ToString()))
{
sbSql4org.Append(" and log.LogType=@LogType ");
pms.Add(new SqlParameter("@LogType", ht["LogType"].ToString()));
}
if (ht.ContainsKey("OperationObj") && !string.IsNullOrEmpty(ht["OperationObj"].ToString()))
{
sbSql4org.Append(" and log.OperationObj=@OperationObj ");
pms.Add(new SqlParameter("@OperationObj", ht["OperationObj"].ToString()));
}
if (ht.ContainsKey("SysAccountNo") && !string.IsNullOrEmpty(ht["SysAccountNo"].ToString()))
{
sbSql4org.Append(" and log.AccountNo=@SysAccountNo ");
pms.Add(new SqlParameter("@SysAccountNo", ht["SysAccountNo"].ToString()));
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select * from TPM_RewardLevel where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("EID") && !string.IsNullOrEmpty(ht["EID"].SafeToString()))
{
str.Append(" and EID=" + ht["EID"].SafeToString());
}
if (ht.ContainsKey("LID") && !string.IsNullOrEmpty(ht["LID"].SafeToString()))
{
if (!ht.ContainsKey("EID"))
{
str.Append(" and EID in (select id from TPM_RewardEdition where lid=" + ht["LID"].SafeToString());
if (ht.ContainsKey("DefindDate"))
{
str.Append(" and convert(varchar(10),'" + ht["DefindDate"].SafeToString() + "',21) between convert(varchar(10),BeginTime,21) and convert(varchar(10),EndTime,21) ");
}
str.Append(")");
}
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and Id=" + ht["Id"].SafeToString());
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "Sort", StartIndex,
EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select temp.* from Com_EvaTemp temp ");
sbSql4org.Append(@" where 1=1 and temp.IsDelete=0 ");
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and temp.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("Cycle") && !string.IsNullOrEmpty(ht["Cycle"].ToString()))
{
sbSql4org.Append(" and temp.Cycle=@Cycle ");
pms.Add(new SqlParameter("@Cycle", ht["Cycle"].ToString()));
}
if (ht.ContainsKey("ApplyRange") && !string.IsNullOrEmpty(ht["ApplyRange"].ToString()))
{
sbSql4org.Append(" and temp.ApplyRange=@ApplyRange ");
pms.Add(new SqlParameter("@ApplyRange", ht["ApplyRange"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
int StartIndex = 0;
int EndIndex = 0;
if (ht["IsPage"].SafeToString().Length > 0)
{
IsPage = Convert.ToBoolean(ht["IsPage"]);
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].SafeToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].SafeToString());
}
StringBuilder sb = new StringBuilder();
if (ht.ContainsKey("Distinct") && !string.IsNullOrEmpty(ht["Distinct"].SafeToString()))
{
sb.Append("select distinct ID,Name from Book_Subject where 1=1 ");
}
else
{
sb.Append("select sub.ID,sub.Name as SubJectName,grd.Name as GradeName,ver.Name as VersionName,sub.CreateTime from Book_Grade grd inner join Book_Subject sub on grd.ID=sub.GradeID inner join Book_Version ver on sub.VersionID=ver.ID where 1=1 ");
}
if (ht.ContainsKey("VersionID") && !string.IsNullOrEmpty(ht["VersionID"].SafeToString()))
{
sb.Append(" and VersionID=" + ht["VersionID"]);
}
if (ht.ContainsKey("GradeID") && !string.IsNullOrEmpty(ht["GradeID"].SafeToString()))
{
sb.Append(" and GradeID=" + ht["GradeID"]);
}
dt = SQLHelp.GetListByPage("(" + sb.ToString() + ")", where, "", StartIndex, EndIndex, IsPage, null, out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select mem.*,build.Name as BuildName,room.Name as RoomName,'舍员' as Position
from Dorm_RoomStuList mem
left join Dorm_Room room on room.Id=mem.RoomId
left join Dorm_Building build on room.BuildId = build.Id ");
sbSql4org.Append(@" where 1=1 ");
if (ht.ContainsKey("RoomId") && !string.IsNullOrEmpty(ht["RoomId"].ToString()))
{
sbSql4org.Append(" and mem.RoomId=@RoomId ");
pms.Add(new SqlParameter("@RoomId", ht["RoomId"].ToString()));
}
if (ht.ContainsKey("NewMemerDay") && !string.IsNullOrEmpty(ht["NewMemerDay"].ToString()))
{
sbSql4org.Append(" and DateDiff(dd,mem.CreateTime,getdate())<=@NewMemerDay ");
pms.Add(new SqlParameter("@NewMemerDay", ht["NewMemerDay"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
if (ht["IsAward"].SafeToString() == "1")
{
str.Append(@"select * from TMP_RewardRank trank where trank.IsDelete=0 ");
}
else
{
str.Append(@" select trank.*
,(select count(1) from TPM_AcheiveRewardInfo where IsDelete=0 and sort=trank.Id) as UseCount
from TMP_RewardRank trank where trank.IsDelete=0 ");
}
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("RId") && !string.IsNullOrEmpty(ht["RId"].SafeToString()))
{
str.Append(" and trank.RId=@RId ");
pms.Add(new SqlParameter("@RId", ht["RId"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and trank.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "RankNum desc", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select inter.*,lalog.LastOperationTime,isnull(lalog.VisitCount,0) as VisitCount
from Sys_Interface inter
left join (select Operation,max(CreateTime) as LastOperationTime,count(1) as VisitCount from Sys_LogInfo group by Operation)lalog on inter.Name=lalog.Operation ");
sbSql4org.Append(@" where 1=1 ");
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and inter.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("IsDelete") && !string.IsNullOrEmpty(ht["IsDelete"].ToString()))
{
sbSql4org.Append(" and inter.IsDelete=@IsDelete ");
pms.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select * from Sys_StudySection where 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("Status") && !string.IsNullOrEmpty(ht["Status"].SafeToString()))
{
str.Append(" and IsDelete = " + ht["Status"].SafeToString());
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex,
EndIndex, IsPage, null, out RowCount);
dt.Columns.Add("PeriodName");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["PeriodName"] = dt.Rows[i]["PeriodIDs"].SafeToString().Replace("1", "小学").Replace("2", "初中").Replace("3", "高中");
}
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
DataTable dt = new DataTable();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select sdo.*
from Sys_Document sdo
where sdo.IsDelete=0 and 1=1");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and sdo.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Type") && !string.IsNullOrEmpty(ht["Type"].SafeToString()))
{
str.Append(" and sdo.Type=@Type ");
pms.Add(new SqlParameter("@Type", ht["Type"].ToString()));
}
if (ht.ContainsKey("RelationId") && !string.IsNullOrEmpty(ht["RelationId"].SafeToString()))
{
str.Append(" and sdo.RelationId=@RelationId ");
pms.Add(new SqlParameter("@RelationId", ht["RelationId"].ToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
List <SqlParameter> pms = new List <SqlParameter>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
bool isAssoId = ht.ContainsKey("AssoId") && !string.IsNullOrEmpty(ht["AssoId"].ToString());
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select lea.*
,case when lea.OutgoingTime is null then (select STUFF((select ';' + CAST(OSLeaderNo AS NVARCHAR(MAX)) from Asso_HisLeader where Type=1
and AssoId=lea.AssoId and (OutgoingTime is null or convert(varchar(10),OfficeTime,21)>=convert(varchar(10),lea.OfficeTime,21) or convert(varchar(10),OutgoingTime,21)>=convert(varchar(10),lea.OfficeTime,21))FOR xml path('')), 1, 1, ''))
else (select STUFF((select ';' + CAST(OSLeaderNo AS NVARCHAR(MAX)) from Asso_HisLeader where Type=1 and AssoId=lea.AssoId
and (convert(varchar(10),OfficeTime,21) between convert(varchar(10),lea.OfficeTime,21) and convert(varchar(10),lea.OutgoingTime,21) or convert(varchar(10),OutgoingTime,21) between convert(varchar(10),lea.OfficeTime,21) and convert(varchar(10),lea.OutgoingTime,21))FOR xml path('')), 1, 1, '')) end as SecondLeaderNo ");
sbSql4org.Append(@" from Asso_HisLeader lea where lea.Type=0 ");
if (isAssoId)
{
sbSql4org.Append(" and lea.AssoId=@AssoId ");
pms.Add(new SqlParameter("@AssoId", ht["AssoId"].ToString()));
}
string orderby = "";
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].ToString()))
{
orderby = ht["OrderBy"].ToString();
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, orderby, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}