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 (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("( select click.ID,re.Name as Name,re.CreateTime,click.CreateUID,DATEDIFF(S,'1970-01-01 00:00:00', ClickTime) - 8 * 3600 as ClickTime,CASE postfix WHEN '' THEN 'file' else right(postfix,LEN(postfix) - 1) end as postfix1 from ClickDetail click inner join ResourcesInfo Re on click.ResourcesID=re.ID and click.ClickType=3)", where, " ClickTime desc", 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 u.*,r.RoleName from UserInfo u
left join RoleInfo r on u.RoleId=r.Id where u.IsDelete=0 ");
if (ht.ContainsKey("LoginName") && !string.IsNullOrEmpty(ht["LoginName"].ToString()))
{
sbSql4org.Append(" and u.LoginName=@LoginName ");
pms.Add(new SqlParameter("@LoginName", ht["LoginName"].ToString()));
}
if (ht.ContainsKey("LoginPassword") && !string.IsNullOrEmpty(ht["LoginPassword"].ToString()))
{
sbSql4org.Append(" and u.LoginPassword=@LoginPassword ");
pms.Add(new SqlParameter("@LoginPassword", ht["LoginPassword"].ToString()));
}
return(SQLHelp.GetListByPage("(" + sbSql4org.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount));
}
catch (Exception ex)
{
//写入日志
//throw;
return(null);
}
}
public DataTable GetAdvertData(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append("select a.*,p.Name from Advertising a left join [dbo].[PortalTreeData] p on a.[MenuId]=p.Id where 1=1");
if (ht.ContainsKey("MenuId") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["MenuId"])))
{
sbSql4org.Append(" and a.MenuId in (" + ht["MenuId"].ToString() + ")");
// List.Add(new SqlParameter("@MenuId", ht["MenuId"].ToString()));
}
sbSql4org.Append(" and a.IsDelete=@IsDelete");
List.Add(new SqlParameter("@IsDelete", ((int)SysStatus.正常).ToString()));
sbSql4org.Append(" and p.IsDelete=@IsDelete2");
List.Add(new SqlParameter("@IsDelete2", ((int)SysStatus.正常).ToString()));
sbSql4org.Append(" and (p.BeforeAfter=" + ((int)BeforeAfter.前台展示) + " or p.BeforeAfter=" + ((int)BeforeAfter.前后台展示) + ") ");
if (ht.ContainsKey("isPush") && !string.IsNullOrWhiteSpace(ht["isPush"].SafeToString()))
{
sbSql4org.Append(" and a.isPush=@isPush");
List.Add(new SqlParameter("@isPush", ht["isPush"].SafeToString()));
}
//if (ht.ContainsKey("MenuId") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["MenuId"])))
// sbSql4org.Append(" Order by a.SortId desc ,a.CreateTime desc ");
//else
// sbSql4org.Append(" Order by a.MenuId asc,a.SortId desc ,a.CreateTime desc ");
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
public DataTable GetLeftNavigationMenu(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
sbSql4org.Append("select * from PortalTreeData where 1=1");
if (ht.ContainsKey("Display") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["Display"])))
{
sbSql4org.Append(" and [Display]=@Display");
List.Add(new SqlParameter("@Display", ht["Display"].ToString()));
}
if (ht.ContainsKey("IsDelete") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["IsDelete"])))
{
sbSql4org.Append(" and IsDelete=@IsDelete");
List.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
if (ht.ContainsKey("BeforeAfter") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["BeforeAfter"])))
{
sbSql4org.Append(" and (BeforeAfter=@BeforeAfter or BeforeAfter=" + (int)BeforeAfter.前后台展示 + ")");
List.Add(new SqlParameter("@BeforeAfter", ht["BeforeAfter"].ToString()));
}
string sys = System.Configuration.ConfigurationManager.AppSettings["SystemWeb"];
if (sys == "YQYZ")
{
sbSql4org.Append(" and ([Name]!='风采展示') ");
}
sbSql4org.Append(" Order by SortId desc,CreateTime asc");
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
return(dt);
}
catch (Exception ex)
{
return(null);
}
}
public int OperDocument(List <Sys_Document> pathlist, string edit_PathId, int relationid)
{
int result = 0;
string str = "";
List <SqlParameter> op_pms = new List <SqlParameter>();
if (pathlist.Count() > 0)
{
op_pms.Add(new SqlParameter("@RelationId", relationid));
for (int i = 0; i < pathlist.Count; i++)
{
Sys_Document item = pathlist[i];
str += "insert into Sys_Document(Type,RelationId,Name,Url,CreateUID) values(@Type" + i + ",@RelationId,@Name" + i + ",@Url" + i + ",@CreateUID" + i + ");";
op_pms.Add(new SqlParameter("@Type" + i, item.Type));
op_pms.Add(new SqlParameter("@Name" + i, item.Name));
op_pms.Add(new SqlParameter("@Url" + i, item.Url));
op_pms.Add(new SqlParameter("@CreateUID" + i, item.CreateUID));
}
}
if (!string.IsNullOrEmpty(edit_PathId))
{
string[] eids = edit_PathId.Split(',');
StringBuilder strFirst = new StringBuilder();
foreach (string id in eids)
{
strFirst.Append("@id" + id.ToString() + ",");
op_pms.Add(new SqlParameter("@id" + id.ToString(), id));
}
str = str + string.Format("update Sys_Document set IsDelete=1 where id in({0});", strFirst.ToString().TrimEnd(','));
}
if (!string.IsNullOrEmpty(str))
{
result = SQLHelp.ExecuteNonQuery(str, CommandType.Text, op_pms.ToArray());
}
return(result);
}
/// <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, string Where)
{
Where += " and IsDelete=0";
RowCount = 0;
DataTable dt = new DataTable();
try
{
int StartIndex = 0;
int EndIndex = 0;
string CourseID = ht["CourseID"].SafeToString();
string Pid = ht["Pid"].SafeToString();
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
if (CourseID.Length > 0)
{
Where += " and CourseID=" + CourseID;
}
if (Pid.Length > 0)
{
Pid = Pid.Substring(Pid.LastIndexOf("|") + 1, Pid.Length - Pid.LastIndexOf("|") - 1);
Where += " and Pid=" + Pid;
}
dt = SQLHelp.GetListByPage((string)ht["TableName"], Where, " Sort asc,ID desc", 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 act.*,DATEDIFF(S,'1970-01-01 00:00:00', act.CreateTime) - 8 * 3600 as CreateTime_Stamp
,build.Name as BuildName,room.Name as RoomName,room.PicURL
,(select count(1) from func_split(act.JoinMembers,',')) as MemCount
,case when convert(varchar(10),getdate(),21) between convert(varchar(10),act.StartTime,21) and convert(varchar(10),act.EndTime,21) then 1
when convert(varchar(10),getdate(),21)>convert(varchar(10),act.EndTime,21) then 2 else 0 end as ActStatus");
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
sbSql4org.Append(@" ,case when room.ManagerNo = @LoginUID then 1 else 0 end as IsLeader
,(select count(1) from func_split(act.JoinMembers,',') where value=@LoginUID) as IsJoin ");
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
}
sbSql4org.Append(@" from Dorm_Activity act
left join Dorm_Room room on room.Id=act.RoomId
left join Dorm_Building build on room.BuildId = build.Id where 1=1 and act.IsDelete=0 ");
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and act.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and act.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("RoomId") && !string.IsNullOrEmpty(ht["RoomId"].ToString()))
{
sbSql4org.Append(" and act.RoomId=@RoomId ");
pms.Add(new SqlParameter("@RoomId", ht["RoomId"].ToString()));
}
if (ht.ContainsKey("ActStatus") && !string.IsNullOrEmpty(ht["ActStatus"].ToString()))
{
if (ht["ActStatus"].ToString() == "ing")
{
sbSql4org.Append(" and convert(varchar(10),getdate(),21) between convert(varchar(10),act.StartTime,21) and convert(varchar(10),act.EndTime,21) ");
}
else
{
sbSql4org.Append(" and convert(varchar(10),getdate(),21)>convert(varchar(10),act.EndTime,21) ");
}
}
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["IsStatistic"].SafeToString() == "0")
{
str.Append(@"select a.*,b.Name,m.Major_Name,u.Name as CreateName,r.Score as UnitScore,l.name as GName
from TPM_RewardUserInfo a
inner join UserInfo b on a.UserNo=b.UniqueNo
left join Major m on b.Major_ID=m.Id
left join UserInfo u on a.CreateUID=u.UniqueNo
left join TPM_AcheiveRewardInfo ri on ri.Id=a.RIId and ri.IsDelete=0
left join TPM_AcheiveLevel l on ri.gpid=l.Id
left join TPM_RewardInfo r on r.Id=ri.Rid
where a.IsDelete=0");
}
else if (ht["IsStatistic"].SafeToString() == "1") //统计
{
if (ht.ContainsKey("AllScore"))
{
str.Append("select isnull(sum(a.Score),0) as AllScore ");
}
else
{
str.Append("select a.UserNo,b.Name,m.Major_Name,isnull(sum(a.Score),0) as SumScore");
}
str.Append(@" from TPM_RewardUserInfo a
inner join UserInfo b on a.UserNo=b.UniqueNo
left join Major m on b.Major_ID=m.Id
left join TPM_AcheiveRewardInfo ri on ri.Id=a.RIId and ri.IsDelete=0
where a.IsDelete=0 and a.RIId!=0
");
}
else
{
str.Append(@"select a.*,b.Name,m.Major_Name,u.Name as CreateName,ri.Year,r.Score as UnitScore,l.name as GName
,al.Name as GidName,uu.Name as ResponsName,case when ri.GPid=6 then bk.Name when ri.GPid=4 then uu.Name else ri.Name end as AchiveName
,(select STUFF((select ',' + CAST(Major_Name AS NVARCHAR(MAX)) from Major where Id in(select value from func_split(ri.DepartMent,',')) FOR xml path('')), 1, 1, '')) as Dep_Major_Name
from TPM_RewardUserInfo a
inner join UserInfo b on a.UserNo=b.UniqueNo
left join Major m on b.Major_ID=m.Id
left join UserInfo u on a.CreateUID=u.UniqueNo
left join TPM_AcheiveRewardInfo ri on ri.Id=a.RIId and ri.IsDelete=0
left join UserInfo uu on ri.ResponsMan=uu.UniqueNo
left join TPM_AcheiveLevel l on ri.gpid=l.Id
left join TPM_AcheiveLevel al on al.Id=ri.Gid
left join TPM_RewardInfo r on r.Id=ri.Rid
left join TPM_BookStory bk on ri.bookid=bk.id where a.IsDelete=0");
}
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and a.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].SafeToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and b.Name like '%" + ht["Name"].SafeToString() + "%'");
}
if (ht.ContainsKey("RIId") && !string.IsNullOrEmpty(ht["RIId"].SafeToString()))
{
str.Append(" and a.RIId=@RIId ");
pms.Add(new SqlParameter("@RIId", ht["RIId"].SafeToString()));
}
if (ht.ContainsKey("Static_RIId") && !string.IsNullOrEmpty(ht["Static_RIId"].SafeToString()))
{
str.Append(" and a.RIId!=0 ");
}
if (ht.ContainsKey("BookId") && !string.IsNullOrEmpty(ht["BookId"].SafeToString()))
{
str.Append(" and a.BookId=@BookId ");
pms.Add(new SqlParameter("@BookId", ht["BookId"].SafeToString()));
}
if (ht.ContainsKey("DepartMent") && !string.IsNullOrEmpty(ht["DepartMent"].SafeToString()))
{
str.Append(" and b.Major_ID=@Major_ID ");
pms.Add(new SqlParameter("@Major_ID", ht["DepartMent"].SafeToString()));
}
if (ht.ContainsKey("BeginTime") && !string.IsNullOrEmpty(ht["BeginTime"].SafeToString()))
{
str.Append(" and convert(varchar(4),ri.Year,21)>=convert(varchar(4),@BeginTime,21) ");
pms.Add(new SqlParameter("@BeginTime", ht["BeginTime"].SafeToString()));
}
if (ht.ContainsKey("EndTime") && !string.IsNullOrEmpty(ht["EndTime"].SafeToString()))
{
str.Append(" and convert(varchar(4),ri.Year,21)<=convert(varchar(4),@EndTime,21)");
pms.Add(new SqlParameter("@EndTime", ht["EndTime"].SafeToString()));
}
if (ht.ContainsKey("UserNos") && !string.IsNullOrEmpty(ht["UserNos"].SafeToString()))
{
StringBuilder strFirst = new StringBuilder();
string[] UserNos = ht["UserNos"].SafeToString().Split(',');
for (int i = 0; i < UserNos.Length; i++)
{
strFirst.Append("@UserNo" + i + ",");
pms.Add(new SqlParameter("@UserNo" + i, UserNos[i]));
}
str.Append(string.Format(" and a.UserNo in({0})", strFirst.ToString().TrimEnd(',')));
}
if (ht.ContainsKey("Status_Com") && !string.IsNullOrEmpty(ht["Status_Com"].SafeToString()))
{
str.Append(" and ri.Status" + ht["Status_Com"].SafeToString());
}
string orderBy = "ULevel asc,Sort";
if (ht.ContainsKey("AllScore"))
{
orderBy = "AllScore";
}
else if (ht["IsStatistic"].SafeToString() == "1")
{
str.Append(" group by a.UserNo,b.Name,m.Major_Name ");
orderBy = "SumScore desc,Major_Name";
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, orderBy, StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
/// <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;
string PhotoPre = ConfigHelper.GetConfigString("PhotoPre");
if (ht.ContainsKey("IsStu") && !string.IsNullOrEmpty(ht["IsStu"].SafeToString()))
{
if (ht["IsStu"].SafeToString().ToUpper() == "FALSE")//老师
{
str.Append(@"select userInfo.Id,UniqueNo,UserType,userInfo.Name,userInfo.Email,Nickname,Sex,Phone,Birthday,LoginName,Password,IDCard,HeadPic,RegisterOrg,AuthenType,Address,Remarks,userInfo.CreateUID,userInfo.EditUID,IsEnable,userInfo.IsDelete,CheckMsg,'"
+ PhotoPre + "' +HeadPic as AbsHeadPic,org.Name as OrgName,b.HeadteacherNO from Sys_UserInfo userInfo inner join Org_Mechanism org on userInfo.RegisterOrg=org.OrganNo left join Sys_ClassInfo b on userInfo.UniqueNo=b.HeadteacherNO where userInfo.UserType <>2");
if (ht.ContainsKey("OrgNo") && !string.IsNullOrEmpty(ht["OrgNo"].SafeToString()))
{
GetAllOrgNo(ht["OrgNo"].SafeToString());
string orgArry = "'" + sb.ToString().Replace(",", "','") + "'";
str.Append(" and userInfo.RegisterOrg in (" + orgArry + ")");
}
if (ht.ContainsKey("TeaNo") && !string.IsNullOrEmpty(ht["TeaNo"].SafeToString()))
{
str.Append(" and userInfo.RegisterOrg in (select OrganNo from Org_Mechanism where LegalUID='" + ht["TeaNo"] + "')");
}
if (ht.ContainsKey("HeadteacherNO") && !string.IsNullOrEmpty(ht["HeadteacherNO"].SafeToString()))
{
str.Append(" and UniqueNo in (select distinct HeadteacherNO from Sys_ClassInfo)");
}
}
else//查询学生信息
{
str.Append(@"select userInfo.Id,UniqueNo,UserType,userInfo.Name,userInfo.Email,Nickname,Sex,Phone,Birthday,LoginName,Password,IDCard,HeadPic,RegisterOrg,AuthenType,Address,Remarks,userInfo.CreateUID,userInfo.EditUID,IsEnable,userInfo.IsDelete,CheckMsg,'" + PhotoPre +
"' +HeadPic as AbsHeadPic,org.ClassName as OrgName,grade.GradeName,b.HeadteacherNO from Sys_UserInfo userInfo inner join Sys_ClassInfo org on userInfo.RegisterOrg=org.ClassNo inner join Grad_Class_rel rel on org.ClassNO=rel.ClassNO inner join Sys_GradeInfo grade on grade.ID=rel.GradeID left join Sys_ClassInfo b on userInfo.UniqueNo=b.HeadteacherNO where 1=1");
str.Append(" and userInfo.UserType =2 ");
string AcademicId = ht["AcademicId"].SafeToString();
if (AcademicId == "0")
{
AcademicId = GetCurrentTerm();
}
if (ht.ContainsKey("OrgNo") && !string.IsNullOrEmpty(ht["OrgNo"].SafeToString()))
{
str.Append(" and userInfo.RegisterOrg in (select ClassNo from Grad_Class_rel where SectionID=" +
AcademicId + " and gradeid=" + ht["OrgNo"] + " union select '" + ht["OrgNo"] + "' as ClassNo)");
}
if (ht.ContainsKey("AcademicId") && !string.IsNullOrEmpty(ht["AcademicId"].SafeToString()))
{
str.Append(" and rel.SectionID=" + AcademicId + " and userInfo.RegisterOrg in (select ClassNo from Grad_Class_rel where SectionID=" + AcademicId + ")");
}
if (ht.ContainsKey("TeaNo") && !string.IsNullOrEmpty(ht["TeaNo"].SafeToString()))
{
str.Append(" and userInfo.RegisterOrg in (select ClassNo from Sys_ClassInfo where HeadteacherNO='" + ht["TeaNo"] + "')");
}
}
}
else
{
str.Append(@"select userInfo.Id,UniqueNo,UserType,userInfo.Name,userInfo.Email,Nickname,Sex,Phone,Birthday,LoginName,Password,IDCard,HeadPic,RegisterOrg,AuthenType,Address,Remarks,userInfo.CreateUID,userInfo.EditUID,IsEnable,userInfo.IsDelete,CheckMsg,'"
+ PhotoPre + "' +HeadPic as AbsHeadPic from Sys_UserInfo userInfo where 1=1");
}
if (ht.ContainsKey("ID") && !string.IsNullOrEmpty(ht["ID"].SafeToString()))
{
str.Append(" and userInfo.ID = " + ht["ID"].SafeToString());
}
if (ht.ContainsKey("Status") && !string.IsNullOrEmpty(ht["Status"].SafeToString()))
{
str.Append(" and userInfo.IsEnable=" + ht["Status"].SafeToString());
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and userInfo.Name like '%" + ht["Name"].SafeToString() + "%'");
}
if (ht.ContainsKey("Phone") && !string.IsNullOrEmpty(ht["Phone"].SafeToString()))
{
str.Append(" and userInfo.Phone = '" + ht["Phone"].SafeToString() + "'");
}
if (ht.ContainsKey("AuthenType") && !string.IsNullOrEmpty(ht["AuthenType"].SafeToString()))
{
str.Append(" and userInfo.AuthenType in (" + ht["AuthenType"].SafeToString() + ")");
}
if (ht.ContainsKey("LoginName") && !string.IsNullOrWhiteSpace(ht["LoginName"].SafeToString()))
{
str.Append(" and userInfo.LoginName='" + ht["LoginName"].SafeToString() + "'");
}
if (ht.ContainsKey("Password") && !string.IsNullOrWhiteSpace(ht["Password"].SafeToString()))
{
str.Append(" and userInfo.Password='******'");
}
if (ht.ContainsKey("Key") && !string.IsNullOrWhiteSpace(ht["Key"].SafeToString()))
{
str.Append(" and (userInfo.Name like '%" + ht["Key"].SafeToString() + "%' or userInfo.LoginName like '%" + ht["Key"].SafeToString() + "%')");
}
if (ht.ContainsKey("LoginName") && !string.IsNullOrEmpty(ht["LoginName"].SafeToString()))
{
str.Append(" and userInfo.LoginName = '" + ht["LoginName"].SafeToString() + "'");
}
if (ht.ContainsKey("NoFeedBack") && !string.IsNullOrEmpty(ht["NoFeedBack"].SafeToString()))
{
str.Append(" and userInfo.UniqueNo not in (select StuNo from FeedBack_StuList where Status=0)");
}
if (ht.ContainsKey("IDCard") && !string.IsNullOrWhiteSpace(ht["IDCard"].SafeToString()))
{
StringBuilder strFirst = new StringBuilder();
string[] IDCards = ht["IDCard"].SafeToString().Split(',');
for (int i = 0; i < IDCards.Length; i++)
{
strFirst.Append("@IDCard" + i + ",");
pms.Add(new SqlParameter("@IDCard" + i, IDCards[i]));
}
str.Append(string.Format(" and userInfo.IDCard {1} in({0})", strFirst.ToString().TrimEnd(','), ht["JoinNoConn"].SafeToString()));
//str.Append(" and userInfo.IDCard='" + ht["IDCard"].SafeToString() + "'");
}
if (ht.ContainsKey("equalsName") && !string.IsNullOrWhiteSpace(ht["equalsName"].SafeToString()))
{
str.Append(" and userInfo.Name = '" + ht["equalsName"].SafeToString() + "'");
}
if (ht.ContainsKey("KaNo") && !string.IsNullOrWhiteSpace(ht["KaNo"].SafeToString()))
{
str.Append(" and userInfo.KaNo='" + ht["KaNo"].SafeToString() + "'");
}
if (ht.Contains("UniqueNo") && !string.IsNullOrWhiteSpace(ht["UniqueNo"].SafeToString()))
{
StringBuilder strFirst = new StringBuilder();
string[] UniqueNos = ht["UniqueNo"].SafeToString().Split(',');
for (int i = 0; i < UniqueNos.Length; i++)
{
strFirst.Append("@UniqueNo" + i + ",");
pms.Add(new SqlParameter("@UniqueNo" + i, UniqueNos[i]));
}
str.Append(string.Format(" and userInfo.UniqueNo {1} in({0})", strFirst.ToString().TrimEnd(','), ht["JoinNoConn"].SafeToString()));
}
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 int AddRewardDash(int Id, int Award)
{
string str = "update TPM_RewardInfo set AddAward=AddAward+" + Award + " where Id=" + Id;
return(SQLHelp.ExecuteNonQuery(str, CommandType.Text, null));
}
public DataTable GetDataInfo(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
if (ht.ContainsKey("isAll") && Convert.ToBoolean(ht["isAll"]) == true)
{
sbSql4org.Append(@"select [Id]
,[Description]
,[MenuId]
,[IsDelete]
,[CreateTime]
,[Creator]
,[ClickNum]
,[ImageUrl]
,[ModelType]
,[FileName]
,[FilePath]
,[SortId]
,[isPush]
FROM [dbo].[Advertising] where 1=1 ");
}
else
{
sbSql4org.Append("select * from Advertising where 1=1 ");
}
if (ht.ContainsKey("MenuIds") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["MenuIds"])))
{
string ids = string.Empty;
if (ht.ContainsKey("ChildId") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["ChildId"])))
{
ids = ht["MenuIds"].ToString();
sbSql4org.Append(" and [MenuId] in (" + ids + ")");
}
else
{
ids = GetMenuInfoByParentID(Convert.ToInt32(ht["MenuIds"]));
sbSql4org.Append(" and [MenuId] in (" + ids + ")");
}
}
if (ht.ContainsKey("isPush") && !string.IsNullOrWhiteSpace(ht["isPush"].SafeToString()))
{
sbSql4org.Append(" and isPush=" + ht["isPush"].SafeToString());
}
if (ht.ContainsKey("IsDelete") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["IsDelete"])))
{
sbSql4org.Append(" and IsDelete=@IsDelete");
List.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
if (ht.ContainsKey("ImgUrl") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["ImgUrl"])))
{
sbSql4org.Append(" and (ImageUrl is not null and ImageUrl!='')");
}
if (ht.ContainsKey("NotItem") && !string.IsNullOrWhiteSpace(Convert.ToString(ht["NotItem"])))
{
sbSql4org.Append(" and ([MenuId] not in (" + ht["NotItem"].ToString() + "))");
}
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, List.ToArray());
return(dt);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(null);
}
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
List <SqlParameter> pms = new List <SqlParameter>();
try
{
StringBuilder str = new StringBuilder();
str.Append(@"select a.*,uu.Name as ResponsName,u.Name as CreateName,l.name as GName,al.Name as GidName,
case when a.GPid=6 then bk.Name else a.Name end as AchiveName,
l.Type as AchieveType,ll.Name as LevelName,r.Name as RewadName,
(select STUFF((select ',' + CAST(Major_Name AS NVARCHAR(MAX)) from Major where Id in(select value from func_split(a.DepartMent,',')) FOR xml path('')), 1, 1, '')) as Major_Name,
case when a.GPid=1 then isnull((select Score from TMP_RewardRank where a.Sort=Id),0) else r.Score end as TotalScore,
r.ScoreType,r.Award,r.AddAward,bk.Name as BookName,bk.BookType,case when bk.BookType=1 then '无' else bk.ISBN end as ISBN ");
if (ht.ContainsKey("LoginMajor_ID") && !string.IsNullOrEmpty(ht["LoginMajor_ID"].SafeToString()))
{
str.Append(@",(select count(1) from TPM_RewardUserInfo ruser left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete=0 and ruser.RIId=a.Id and u.Major_ID=@LoginMajor_ID) MajorCount ");
pms.Add(new SqlParameter("@LoginMajor_ID", ht["LoginMajor_ID"].SafeToString()));
}
str.Append(@" from TPM_AcheiveRewardInfo a
inner join UserInfo u on a.CreateUid=u.UniqueNo
inner join UserInfo uu on a.ResponsMan=uu.UniqueNo
left join Major m on a.DepartMent=m.Id
inner join TPM_AcheiveLevel l on a.gpid=l.Id
left join TPM_AcheiveLevel al on al.Id=a.Gid
inner join TPM_RewardLevel ll on a.Lid=ll.Id
inner join TPM_RewardInfo r on a.Rid=r.Id
left join TPM_BookStory bk on a.bookid=bk.id where a.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Status") && !string.IsNullOrEmpty(ht["Status"].SafeToString()))
{
str.Append(" and a.Status in (" + ht["Status"].SafeToString() + ")");
}
if (ht.ContainsKey("Status_Com") && !string.IsNullOrEmpty(ht["Status_Com"].SafeToString()))
{
str.Append(" and a.Status" + ht["Status_Com"].SafeToString());
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and a.Name like '%" + ht["Name"].SafeToString() + "%'");
}
if (ht.ContainsKey("ResponName") && !string.IsNullOrEmpty(ht["ResponName"].SafeToString()))
{
str.Append(" and uu.Name like '%" + ht["ResponName"].SafeToString() + "%'"); //负责人名称
}
if (ht.ContainsKey("MyUno") && !string.IsNullOrEmpty(ht["MyUno"].SafeToString()))
{
str.Append(" and (a.ResponsMan = '" + ht["MyUno"].SafeToString() + "' or (a.Id in(select distinct RIId from TPM_RewardUserInfo where IsDelete = 0 and UserNo = '" + ht["MyUno"].SafeToString() + "')))");
}
if (ht.ContainsKey("CreateUID") && !string.IsNullOrEmpty(ht["CreateUID"].SafeToString()))
{
str.Append(" and a.CreateUID =@CreateUID ");
pms.Add(new SqlParameter("@CreateUID", ht["CreateUID"].SafeToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and a.Id =@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].SafeToString()));
}
if (ht.ContainsKey("GPid") && !string.IsNullOrEmpty(ht["GPid"].SafeToString()))
{
str.Append(" and a.GPid =@GPid ");
pms.Add(new SqlParameter("@GPid", ht["GPid"].SafeToString()));
}
if (ht.ContainsKey("Gid") && !string.IsNullOrEmpty(ht["Gid"].SafeToString()))
{
str.Append(" and a.Gid=@Gid ");
pms.Add(new SqlParameter("@Gid", ht["Gid"].SafeToString()));
}
if (ht.ContainsKey("BookId") && !string.IsNullOrEmpty(ht["BookId"].SafeToString()))
{
str.Append(" and a.BookId =@BookId ");
pms.Add(new SqlParameter("@BookId", ht["BookId"].SafeToString()));
}
if (ht.ContainsKey("DepartMent") && !string.IsNullOrEmpty(ht["DepartMent"].SafeToString()))
{
str.Append(" and a.DepartMent =@DepartMent ");
pms.Add(new SqlParameter("@DepartMent", ht["DepartMent"].SafeToString()));
}
if (ht.ContainsKey("BeginTime") && !string.IsNullOrEmpty(ht["BeginTime"].SafeToString()))
{
str.Append(" and a.CreateTime > '" + ht["BeginTime"].SafeToString() + "'");
}
if (ht.ContainsKey("EndTime") && !string.IsNullOrEmpty(ht["EndTime"].SafeToString()))
{
str.Append(" and a.CreateTime < '" + ht["EndTime"].SafeToString() + "'");
}
if (ht.ContainsKey("Major_ID") && !string.IsNullOrEmpty(ht["Major_ID"].SafeToString()))
{
str.Append(@" and a.Id in(select distinct ruser.RIId from TPM_RewardUserInfo ruser
left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete = 0 and ruser.RIId!= 0 and u.Major_ID=@Major_ID)");
pms.Add(new SqlParameter("@Major_ID", ht["Major_ID"].SafeToString()));
}
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 a.*,u.Name as EditName,maj.Major_Name as MEditorDepart_Name
,(select count(1) from TPM_AcheiveRewardInfo where IsDelete=0 and Status>=3 and BookId=a.Id)PrizeCount ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("LoginMajor_ID") && !string.IsNullOrEmpty(ht["LoginMajor_ID"].SafeToString()))
{
str.Append(@",(select count(1) from TPM_RewardUserInfo ruser left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete=0 and ruser.BookId!=0 and ruser.BookId=a.Id and u.Major_ID=@LoginMajor_ID) MajorCount ");
pms.Add(new SqlParameter("@LoginMajor_ID", ht["LoginMajor_ID"].SafeToString()));
}
str.Append(@" from TPM_BookStory a
left join UserInfo u on a.MEditor=u.UniqueNo
left join Major maj on a.MEditorDepart=maj.Id
where a.IsDelete=0 ");
if (ht.ContainsKey("Status") && !string.IsNullOrEmpty(ht["Status"].SafeToString()))
{
str.Append(" and a.Status in (" + ht["Status"].SafeToString() + ")");
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and a.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].SafeToString()));
}
if (ht.ContainsKey("BookType") && !string.IsNullOrEmpty(ht["BookType"].SafeToString()))
{
str.Append(" and BookType=@BookType ");
pms.Add(new SqlParameter("@BookType", ht["BookType"].SafeToString()));
}
if (ht.ContainsKey("IsPlanBook") && !string.IsNullOrEmpty(ht["IsPlanBook"].SafeToString()))
{
str.Append(" and a.IsPlanBook=@IsPlanBook ");
pms.Add(new SqlParameter("@IsPlanBook", ht["IsPlanBook"].SafeToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and (a.ISBN like '%" + ht["Name"].SafeToString() + "%' or a.Name like '%" + ht["Name"].SafeToString() + "%' or u.Name like '%" + ht["Name"].SafeToString() + "%')");
}
if (ht.ContainsKey("Author_SelfNo") && !string.IsNullOrEmpty(ht["Author_SelfNo"].SafeToString()))//自己创建或者是主编、参编
{
str.Append(@" and ((a.CreateUID=@Author_SelfNo and a.Status in(0,1)) or (
a.Id in(select distinct BookId from TPM_RewardUserInfo where IsDelete=0 and UserNo=@Author_SelfNo) and a.Status in(2,3)))");
pms.Add(new SqlParameter("@Author_SelfNo", ht["Author_SelfNo"].SafeToString()));
}
if (ht.ContainsKey("AuthorNo") && !string.IsNullOrEmpty(ht["AuthorNo"].SafeToString()))//自己是主编、参编
{
str.Append(@" and a.Id in(select distinct BookId from TPM_RewardUserInfo where IsDelete=0 and UserNo=@AuthorNo) ");
pms.Add(new SqlParameter("@AuthorNo", ht["AuthorNo"].SafeToString()));
}
if (ht.ContainsKey("Major_ID") && !string.IsNullOrEmpty(ht["Major_ID"].SafeToString()))
{
str.Append(@" and a.Id in(select distinct ruser.BookId from TPM_RewardUserInfo ruser
left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete = 0 and ruser.BookId != 0 and u.Major_ID=@Major_ID)");
pms.Add(new SqlParameter("@Major_ID", ht["Major_ID"].SafeToString()));
}
if (ht.ContainsKey("IdentifyCol") && !string.IsNullOrEmpty(ht["IdentifyCol"].SafeToString()))
{
str.Append(" and a.IdentifyCol=@IdentifyCol ");
pms.Add(new SqlParameter("@IdentifyCol", ht["IdentifyCol"].SafeToString()));
}
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
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"select act.*,depart.Name as DepartName,depart.PicURL
,DATEDIFF(S,'1970-01-01 00:00:00', act.CreateTime) - 8 * 3600 as CreateTime_Stamp
,case when convert(varchar(10),getdate(),21) between convert(varchar(10),act.StartTime,21) and convert(varchar(10),act.EndTime,21) then 1
when convert(varchar(10),getdate(),21)>convert(varchar(10),act.EndTime,21) then 2 else 0 end as ActStatus");
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
sbSql4org.Append(@" ,case when depart.LeaderNo = @LoginUID or depart.SecondLeaderNo = @LoginUID then 1 else 0 end as IsLeader
,(select count(1) from func_split(act.JoinMembers,',') where value=@LoginUID) as IsJoin ");
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
}
sbSql4org.Append(@" from Acti_Activity act
left join Acti_DepartInfo depart on depart.Id=act.DepartId where 1=1 and act.IsDelete=0 ");
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and act.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and act.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("DepartId") && !string.IsNullOrEmpty(ht["DepartId"].ToString()))
{
sbSql4org.Append(" and act.DepartId=@DepartId ");
pms.Add(new SqlParameter("@DepartId", ht["DepartId"].ToString()));
}
if (ht.ContainsKey("ActStatus") && !string.IsNullOrEmpty(ht["ActStatus"].ToString()))
{
if (ht["ActStatus"].ToString() == "ing")
{
sbSql4org.Append(" and convert(varchar(10),getdate(),21) between convert(varchar(10),act.ActStartTime,21) and convert(varchar(10),act.ActEndTime,21) ");
}
else
{
sbSql4org.Append(" and convert(varchar(10),getdate(),21)>convert(varchar(10),act.ActEndTime,21) ");
}
}
//部长/副部长
if (ht.ContainsKey("OSLeaderNo") && !string.IsNullOrEmpty(ht["OSLeaderNo"].ToString()))
{
sbSql4org.Append(" and (depart.LeaderNo=@OSLeaderNo or depart.SecondLeaderNo=@OSLeaderNo) ");
pms.Add(new SqlParameter("@OSLeaderNo", ht["OSLeaderNo"].ToString()));
}
//我的部门下的活动
if (ht.ContainsKey("MyUserNo") && !string.IsNullOrEmpty(ht["MyUserNo"].ToString()))
{
sbSql4org.Append(" and (depart.Id in(select distinct DepartId from Acti_DepartMember where MemberNo=@MyUserNo) or (depart.LeaderNo=@MyUserNo or depart.SecondLeaderNo=@MyUserNo)) ");
pms.Add(new SqlParameter("@MyUserNo", ht["MyUserNo"].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>();
int StartIndex = 0;
int EndIndex = 0;
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
try
{
bool isMember = ht["IsMember"].ToString() == "1";
bool isOnlyBase = ht["IsOnlyBase"].ToString() != "0";
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"SELECT A.Id,A.Name,A.ActivityId ");
if (isOnlyBase)
{
sbSql4org.Append(@" " + (isMember ? ",B.JoinMember" : "") + ",act.Name as ActivityName,act.DepartId,depart.Name as DepartName,depart.PicURL ");
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
sbSql4org.Append(@" ,case when depart.LeaderNo = @LoginUID or depart.SecondLeaderNo = @LoginUID then 1 else 0 end as IsLeader
,(select count(1) from func_split(A.JoinMembers,',') where value=@LoginUID) as IsJoin ");
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
}
}
if (isMember)
{
sbSql4org.Append(@" FROM (
SELECT StrXml = CONVERT(XML, '<root><v>'+REPLACE(JoinMembers,',','</v><v>')+'</v></root>'),
Id,Name,ActivityId from Acti_Project where IsDelete=0
) A
OUTER APPLY
(SELECT JoinMember = N.v.value('.','nvarchar(40)')
FROM A.StrXml.nodes('/root/v') N(v)
) B ");
}
else
{
sbSql4org.Append(@" FROM (SELECT Id,Name,ActivityId from Acti_Project where IsDelete=0) A ");
}
if (isOnlyBase)
{
sbSql4org.Append(@" left join Acti_Activity act on A.ActivityId=act.Id
left join Acti_DepartInfo depart on depart.Id=act.DepartId ");
}
sbSql4org.Append(@" where 1=1 ");
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and A.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and A.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("ActivityId") && !string.IsNullOrEmpty(ht["ActivityId"].ToString()))
{
sbSql4org.Append(" and A.ActivityId=@ActivityId ");
pms.Add(new SqlParameter("@ActivityId", ht["DepartId"].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>();
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();
bool isOnlyBase = ht["IsOnlyBase"].ToString() != "0";
sbSql4org.Append(@"select distinct depart.* ");
if (isOnlyBase)
{
sbSql4org.Append(@",(select count(1) from Acti_DepartMember where DepartId=depart.Id) as MemCount
,(select count(1) from Com_NewInfo where type=0 and RelationId=depart.Id) as NewCount
,(select count(1) from Acti_RecruitApply app
left join Acti_Activity act on app.ActivityId=act.Id where DepartId=depart.Id and app.ExamStatus=1 )as ExamApplyCount ");
}
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
sbSql4org.Append(@" ,(select count(1) from Acti_DepartMember where DepartId=depart.Id and MemberNo=@LoginUID) as IsMember
,case when depart.LeaderNo = @LoginUID or depart.SecondLeaderNo = @LoginUID then 1 else 0 end as IsLeader");
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
}
sbSql4org.Append(@" from Acti_DepartInfo depart ");
sbSql4org.Append(@" where 1=1 ");
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and depart.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (ht.ContainsKey("ParentId") && !string.IsNullOrEmpty(ht["ParentId"].ToString()))
{
sbSql4org.Append(" and depart.ParentId=@ParentId ");
pms.Add(new SqlParameter("@ParentId", ht["ParentId"].ToString()));
}
if (ht.ContainsKey("IsDelete") && !string.IsNullOrEmpty(ht["IsDelete"].ToString()))
{
sbSql4org.Append(" and depart.IsDelete=@IsDelete ");
pms.Add(new SqlParameter("@IsDelete", ht["IsDelete"].ToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and depart.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("MyUserNo") && !string.IsNullOrEmpty(ht["MyUserNo"].ToString()))
{
sbSql4org.Append(" and (depart.Id in(select distinct DepartId from Acti_DepartMember where MemberNo=@MyUserNo) or depart.LeaderNo=@MyUserNo or depart.SecondLeaderNo=@MyUserNo) ");
pms.Add(new SqlParameter("@MyUserNo", ht["MyUserNo"].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();
str.Append(@" select r_bat.*,r_bat.Status as AuditStatus,b.Name as CreateName,ba.Year as BatYear,ba.Name as BatName
,isnull((select sum(AllotMoney) from TPM_AllotReward where BatchDetail_Id=r_bat.Id and IsDelete=0),0)HasAllot ");
if (ht["IsOnlyBase"].SafeToString() == "1") //查询关联表
{
string IsAllotUser = ht["IsAllotUser"].SafeToString();
str.Append(@" ,uu.Name as ResponsName,al.Name as GidName,a.Year,l.Type as AchieveType
,case when a.GPid=6 then bk.Name when a.GPid=4 then uu.Name else a.Name end as AchiveName
,(select STUFF((select ',' + CAST(Major_Name AS NVARCHAR(MAX)) from Major where Id in(select value from func_split(a.DepartMent,',')) FOR xml path('')), 1, 1, '')) as Major_Name ");
if (IsAllotUser == "1")
{
str.Append(" ,au.Name as RUserName,allot.AllotMoney ");
}
str.Append(@" from TPM_RewardBatchDetail r_bat
inner join TPM_RewardBatch ba on r_bat.RewardBatch_Id=ba.Id
left join UserInfo b on r_bat.CreateUID=b.UniqueNo
left join TPM_AcheiveRewardInfo a on r_bat.Acheive_Id=a.Id
left join UserInfo uu on a.ResponsMan=uu.UniqueNo
left join TPM_AcheiveLevel l on a.gpid=l.Id
left join TPM_AcheiveLevel al on al.Id=a.Gid
left join TPM_BookStory bk on a.bookid=bk.id ");
if (IsAllotUser == "1")
{
str.Append(@" inner join TPM_AllotReward allot on allot.BatchDetail_Id=r_bat.Id and allot.IsDelete=0
left join TPM_RewardUserInfo ruser on ruser.Id=allot.RewardUser_Id
left join UserInfo au on au.UniqueNo=ruser.UserNo " );
}
if (ht.ContainsKey("AchiveName") && !string.IsNullOrEmpty(ht["AchiveName"].SafeToString()))
{
Where = " and AchiveName like N'%' + @AchiveName + '%'";
pms.Add(new SqlParameter("@AchiveName", ht["AchiveName"].ToString()));
}
}
else //只查询基础表
{
str.Append(@" from TPM_RewardBatchDetail r_bat
inner join TPM_RewardBatch ba on r_bat.RewardBatch_Id=ba.Id
left join UserInfo b on r_bat.CreateUID=b.UniqueNo ");
}
str.Append(@" where r_bat.IsDelete=0 and ba.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("RewardBatch_Id") && !string.IsNullOrEmpty(ht["RewardBatch_Id"].SafeToString()))
{
str.Append(" and r_bat.RewardBatch_Id=@RewardBatch_Id ");
pms.Add(new SqlParameter("@RewardBatch_Id", ht["RewardBatch_Id"].ToString()));
}
if (ht.ContainsKey("IsMoneyAllot") && !string.IsNullOrEmpty(ht["IsMoneyAllot"].SafeToString()))
{
str.Append(" and ba.IsMoneyAllot=@IsMoneyAllot ");
pms.Add(new SqlParameter("@IsMoneyAllot", ht["IsMoneyAllot"].ToString()));
}
if (ht.ContainsKey("Acheive_Id") && !string.IsNullOrEmpty(ht["Acheive_Id"].SafeToString()))
{
str.Append(" and r_bat.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 r_bat.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("GPid") && !string.IsNullOrEmpty(ht["GPid"].SafeToString()))
{
str.Append(" and a.GPid=@GPid ");
pms.Add(new SqlParameter("@GPid", ht["GPid"].SafeToString()));
}
if (ht.ContainsKey("Gid") && !string.IsNullOrEmpty(ht["Gid"].SafeToString()))
{
str.Append(" and a.Gid=@Gid ");
pms.Add(new SqlParameter("@Gid", ht["Gid"].SafeToString()));
}
if (ht.ContainsKey("Year") && !string.IsNullOrEmpty(ht["Year"].SafeToString()))
{
str.Append(" and a.Year like N'%' + @Year + '%'");
pms.Add(new SqlParameter("@Year", ht["Year"].ToString().Replace("年", "")));
}
if (ht.ContainsKey("AuditStatus") && !string.IsNullOrEmpty(ht["AuditStatus"].SafeToString()))
{
str.Append(" and r_bat.Status " + 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, string where)
{
RowCount = 0;
DataTable dt = new DataTable();
try
{
int StartIndex = 0;
int EndIndex = 0;
string OrderBy = "";
string DocName = ht["DocName"].SafeToString();
string IsFolder = ht["IsFolder"].SafeToString();
string Pid = ht["Pid"].SafeToString();
string IDS = ht["IDS"].SafeToString();
string ID = ht["ID"].SafeToString();
string Time = ht["CreateTime"].SafeToString();
string CreateUID = ht["CreateUID"].SafeToString();
string Where = " and CreateUID='" + CreateUID + "'";
string Postfixs = ht["Postfixs"].SafeToString();
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
if (ID.Length > 0)
{
Where += " and ID=" + ID;
}
if (Postfixs.Length > 0)
{
Where += " and postfix in (" + GetPostfixs(int.Parse(Postfixs)) + ")";
if (Pid.Length > 0 && Pid != "0")
{
Where += " and Pid=" + Pid;
}
}
else
{
if (Pid.Length > 0)
{
Where += " and Pid=" + Pid;
}
}
if (DocName.Length > 0)
{
Where += " and Name like '%" + DocName + "%'";
}
if (IDS.Length > 0)
{
Where += " and ID in (" + IDS + ")";
}
if (IsFolder.SafeToString().Length > 0)
{
Where += " and IsFolder = " + IsFolder;
}
if (Time.Length > 0)
{
if (Convert.ToDateTime(Time) > DateTime.Now.AddMonths(-7))
{
Where += " and CreateTime> '" + Time + "'";
}
}
if (ht.ContainsKey("OrderBy") && !string.IsNullOrEmpty(ht["OrderBy"].SafeToString()))
{
OrderBy = ht["OrderBy"].SafeToString();
}
dt = SQLHelp.GetListByPage((string)ht["TableName"], Where, OrderBy, StartIndex, EndIndex, IsPage, null, out RowCount,
"ID,Name+postfix as Name,Pid,FileUrl,CreateTime,EditTime,FileSize,code,postfix,IsFolder,CASE postfix WHEN '' THEN 'file' else right(postfix,LEN(postfix) - 1) end as postfix1");
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public virtual bool GetInfoById(T entity, int id)
{
string sql = "select * from " + entity + "where ID=" + id;
return(SQLHelp.ExecuteNonQuery(sql, System.Data.CommandType.Text, null) > 0);
}
public override DataTable GetListByPage(Hashtable ht, out int RowCount, bool IsPage = true, string Where = "")
{
RowCount = 0;
DataTable dt = new DataTable();
List <SqlParameter> pms = new List <SqlParameter>();
try
{
StringBuilder str = new StringBuilder();
str.Append(@" select *,
case when Status<7 then Status
when Status>=7 and 1 not in(select value from func_split(IsMoneyAllot,',')) then 7
when Status>=7 and 1 in (select value from func_split(IsMoneyAllot,',')) then
case when 2 in(select value from func_split(AuditSts,',')) then 11
when 0 in (select value from func_split(AuditSts,',')) then 9
when 10 in(select value from func_split(AuditSts,',')) then 8
when 1 in (select value from func_split(AuditSts,',')) then 10 else 12 end end as ComStatus
from ( ");
str.Append(@" select a.*,uu.Name as ResponsName,u.Name as CreateName,l.name as GName,al.Name as GidName,
case when a.GPid=6 then bk.Name when a.GPid=4 then uu.Name else a.Name end as AchiveName,
l.Type as AchieveType,ll.Name as LevelName,r.Name as RewadName,
(select STUFF((select ',' + CAST(Major_Name AS NVARCHAR(MAX)) from Major where Id in(select value from func_split(a.DepartMent,',')) FOR xml path('')), 1, 1, '')) as Major_Name,
case when a.GPid=1 then isnull((ran.Score),0) else r.Score end as TotalScore,ran.Name RankName,
r.ScoreType,bk.Name as BookName,bk.BookType,case when bk.BookType=1 then '无' else bk.ISBN end as ISBN
,STUFF((select ',' + CAST(IsMoneyAllot AS NVARCHAR(MAX)) from TPM_RewardBatchDetail aud
inner join TPM_RewardBatch r_bat on r_bat.Id=aud.RewardBatch_Id and r_bat.IsDelete=0
where aud.IsDelete=0 and r_bat.IsMoneyAllot=1 and aud.Acheive_Id=a.Id FOR xml path('')), 1, 1, '') as IsMoneyAllot
,STUFF((select ',' + CAST(isnull(aud.Status,10) AS NVARCHAR(MAX)) from TPM_RewardBatchDetail aud
inner join TPM_RewardBatch r_bat on r_bat.Id=aud.RewardBatch_Id and r_bat.IsDelete=0
where aud.IsDelete=0 and r_bat.IsMoneyAllot=1 and aud.Acheive_Id=a.Id FOR xml path('')), 1, 1, '') as AuditSts ");
if (ht.ContainsKey("LoginMajor_ID") && !string.IsNullOrEmpty(ht["LoginMajor_ID"].SafeToString()))
{
str.Append(@",(select count(1) from TPM_RewardUserInfo ruser left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete=0 and ruser.RIId=a.Id and u.Major_ID=@LoginMajor_ID) MajorCount ");
pms.Add(new SqlParameter("@LoginMajor_ID", ht["LoginMajor_ID"].SafeToString()));
}
if (ht.ContainsKey("MyUno") && !string.IsNullOrEmpty(ht["MyUno"].SafeToString()))
{
str.Append(" ,case when a.Status>6 then (select top 1 Score from TPM_RewardUserInfo where RIId=a.Id and UserNo=@MyUno) else NULL end as SelfScore ");
}
str.Append(@" from TPM_AcheiveRewardInfo a
inner join UserInfo u on a.CreateUID=u.UniqueNo
left join UserInfo uu on a.ResponsMan=uu.UniqueNo
left join Major m on a.DepartMent=m.Id
left join TPM_AcheiveLevel l on a.gpid=l.Id
left join TPM_AcheiveLevel al on al.Id=a.Gid
left join TPM_RewardLevel ll on a.Lid=ll.Id
left join TPM_RewardInfo r on a.Rid=r.Id
left join TMP_RewardRank ran on a.Sort=ran.Id
left join TPM_BookStory bk on a.bookid=bk.id where a.IsDelete=0 ");
int StartIndex = 0;
int EndIndex = 0;
if (ht.ContainsKey("Status") && !string.IsNullOrEmpty(ht["Status"].SafeToString()))
{
str.Append(" and a.Status in (" + ht["Status"].SafeToString() + ")");
}
if (ht.ContainsKey("Status_Com") && !string.IsNullOrEmpty(ht["Status_Com"].SafeToString()))
{
str.Append(" and a.Status" + ht["Status_Com"].SafeToString());
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].SafeToString()))
{
str.Append(" and a.Name like '%" + ht["Name"].SafeToString() + "%'");
}
if (ht.ContainsKey("ResponName") && !string.IsNullOrEmpty(ht["ResponName"].SafeToString()))
{
str.Append(" and uu.Name like '%" + ht["ResponName"].SafeToString() + "%'"); //负责人名称
}
if (ht.ContainsKey("MyUno") && !string.IsNullOrEmpty(ht["MyUno"].SafeToString()))
{
str.Append(" and (a.ResponsMan=@MyUno or (a.Status>6 and a.Id in(select distinct RIId from TPM_RewardUserInfo where IsDelete = 0 and UserNo=@MyUno)))");
pms.Add(new SqlParameter("@MyUno", ht["MyUno"].SafeToString()));
}
if (ht.ContainsKey("CreateUID") && !string.IsNullOrEmpty(ht["CreateUID"].SafeToString()))
{
str.Append(" and a.CreateUID =@CreateUID ");
pms.Add(new SqlParameter("@CreateUID", ht["CreateUID"].SafeToString()));
}
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].SafeToString()))
{
str.Append(" and a.Id =@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].SafeToString()));
}
if (ht.ContainsKey("GPid") && !string.IsNullOrEmpty(ht["GPid"].SafeToString()))
{
str.Append(" and a.GPid =@GPid ");
pms.Add(new SqlParameter("@GPid", ht["GPid"].SafeToString()));
}
if (ht.ContainsKey("Gid") && !string.IsNullOrEmpty(ht["Gid"].SafeToString()))
{
str.Append(" and a.Gid=@Gid ");
pms.Add(new SqlParameter("@Gid", ht["Gid"].SafeToString()));
}
if (ht.ContainsKey("Year") && !string.IsNullOrEmpty(ht["Year"].SafeToString()))
{
str.Append(" and a.Year like N'%' + @Year + '%'");
pms.Add(new SqlParameter("@Year", ht["Year"].ToString().Replace("年", "")));
}
if (ht.ContainsKey("BookId") && !string.IsNullOrEmpty(ht["BookId"].SafeToString()))
{
str.Append(" and a.BookId =@BookId ");
pms.Add(new SqlParameter("@BookId", ht["BookId"].SafeToString()));
}
if (ht.ContainsKey("DepartMent") && !string.IsNullOrEmpty(ht["DepartMent"].SafeToString()))
{
str.Append(" and a.DepartMent =@DepartMent ");
pms.Add(new SqlParameter("@DepartMent", ht["DepartMent"].SafeToString()));
}
if (ht.ContainsKey("BeginTime") && !string.IsNullOrEmpty(ht["BeginTime"].SafeToString()))
{
str.Append(" and a.CreateTime > '" + ht["BeginTime"].SafeToString() + "'");
}
if (ht.ContainsKey("EndTime") && !string.IsNullOrEmpty(ht["EndTime"].SafeToString()))
{
str.Append(" and a.CreateTime < '" + ht["EndTime"].SafeToString() + "'");
}
if (ht.ContainsKey("Major_ID") && !string.IsNullOrEmpty(ht["Major_ID"].SafeToString()))
{
str.Append(@" and a.Id in(select distinct ruser.RIId from TPM_RewardUserInfo ruser
left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete = 0 and ruser.RIId!= 0 and u.Major_ID=@Major_ID)");
pms.Add(new SqlParameter("@Major_ID", ht["Major_ID"].SafeToString()));
}
if (ht.ContainsKey("AuditMajor_ID") && !string.IsNullOrEmpty(ht["AuditMajor_ID"].SafeToString())) //业绩审核处的查询
{
str.Append(@" and ((a.GPid=2 and a.Status=1 and a.TwoAudit_Status in(" + ht["TwoAudit_Status"].SafeToString() + ")) or (a.GPid!=2 and a.Status=1) or a.Status=5 or a.Id in(select distinct Acheive_Id from TPM_RewardBatchDetail where IsDelete=0 and Status=1))");
str.Append(@" and (a.GPid in(" + ht["Level_AllIds"].SafeToString() + ") or (a.GPid in (" + ht["Level_DepartIds"].SafeToString() + @") and a.Id in(select distinct ruser.RIId from TPM_RewardUserInfo ruser
left join UserInfo u on ruser.UserNo = u.UniqueNo
where ruser.IsDelete = 0 and ruser.RIId!= 0 and u.Major_ID=@AuditMajor_ID))) ");
pms.Add(new SqlParameter("@AuditMajor_ID", ht["AuditMajor_ID"].SafeToString()));
}
if (ht.ContainsKey("MyAch_LoginUID") && !string.IsNullOrEmpty(ht["MyAch_LoginUID"].SafeToString())) //我的业绩处的查询
{
str.Append(@" and ((a.GPid!=2 and a.Status>2) or (a.GPid=2 and ((a.CreateUID=@MyAch_LoginUID) or (a.CreateUID!=@MyAch_LoginUID and a.Status>0)))) ");
pms.Add(new SqlParameter("@MyAch_LoginUID", ht["MyAch_LoginUID"].SafeToString()));
}
if (ht.ContainsKey("Respon_LoginUID") && !string.IsNullOrEmpty(ht["Respon_LoginUID"].SafeToString())) //首页统计信息-负责人待审核
{
str.Append(@" and a.ResponsMan=@Respon_LoginUID and ((a.Status=1 and a.GPid=2) or a.Status=5 or a.Id in(select distinct Acheive_Id from TPM_RewardBatchDetail where IsDelete=0 and Status=1))");
pms.Add(new SqlParameter("@Respon_LoginUID", ht["Respon_LoginUID"].SafeToString()));
}
if (ht.ContainsKey("MyIndex_LoginUID") && !string.IsNullOrEmpty(ht["MyIndex_LoginUID"].SafeToString()))//首页统计信息-我的业绩
{
str.Append(" and a.Status>6 and a.Id in(select distinct RIId from TPM_RewardUserInfo where IsDelete =0 and UserNo=@MyIndex_LoginUID)");
pms.Add(new SqlParameter("@MyIndex_LoginUID", ht["MyIndex_LoginUID"].SafeToString()));
}
if (ht.ContainsKey("RewardBatch_Id") && !string.IsNullOrEmpty(ht["RewardBatch_Id"].SafeToString()))//奖金批次id
{
str.Append(@" and a.Id not in(select distinct Acheive_Id from TPM_RewardBatchDetail
where IsDelete = 0 and RewardBatch_Id=@RewardBatch_Id)");
pms.Add(new SqlParameter("@RewardBatch_Id", ht["RewardBatch_Id"].SafeToString()));
}
if (IsPage)
{
StartIndex = Convert.ToInt32(ht["StartIndex"].ToString());
EndIndex = Convert.ToInt32(ht["EndIndex"].ToString());
}
str.Append(@" ) Temp ");
if (ht.ContainsKey("AchiveName") && !string.IsNullOrEmpty(ht["AchiveName"].SafeToString()))
{
Where = " and AchiveName like N'%' + @AchiveName + '%'";
pms.Add(new SqlParameter("@AchiveName", ht["AchiveName"].ToString()));
}
dt = SQLHelp.GetListByPage("(" + str.ToString() + ")", Where, "", StartIndex, EndIndex, IsPage, pms.ToArray(), out RowCount);
}
catch (Exception ex)
{
LogService.WriteErrorLog(ex.Message);
}
return(dt);
}
public int UpdateAdertSort(Hashtable ht)
{
try
{
StringBuilder sbSql4org = new StringBuilder();
StringBuilder sbSql4org_up = new StringBuilder();
List <SqlParameter> List = new List <SqlParameter>();
string opertion = ht["upOrdown"].ToString();
//int mid = Convert.ToInt32(ht["MenuId"]);
//string mids = GetAdvertMenuIds(mid);
//if (string.IsNullOrWhiteSpace(mids)) return -999;
int id = Convert.ToInt32(ht["Id"]);
int sid = Convert.ToInt32(ht["SortId"]);
string mwhere = string.Empty;
if (opertion == "up")//SortID值变大 找下一条
{
if (!string.IsNullOrWhiteSpace(ht["MenuId"].SafeToString()))
{
mwhere += " and MenuId in (" + ht["MenuId"].SafeToString() + ")";
}
if (ht.ContainsKey("isPush") && !string.IsNullOrWhiteSpace(ht["isPush"].SafeToString()))
{
mwhere += " and isPush=" + ht["isPush"].SafeToString();
}
// 求的下一级
sbSql4org.Append(@";with TB as (select top 999999 *,row_number() over(order by SortId desc) as rowid from Advertising where IsDelete=@IsDelete " + mwhere + " order by SortId desc)"
+ " select * from tb where rowid=(select rowid-1 from TB where Id=@id " + mwhere + ")");
//sbSql4org.Append("select top 1 * from Advertising where MenuId in (" + mids + ") and IsDelete=@IsDelete and id>@id order by SortId asc");
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@IsDelete", ((int)SysStatus.正常).ToString()),
new SqlParameter("@id", id)
};
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, param);
if (dt == null || dt.Rows.Count == 0)
{
return(-1);///已经是最大值
}
else
{
sbSql4org_up.Append("update Advertising set SortId=@SortId where Id=@CID;update Advertising set SortId=@PSortId where Id=@PCID");
SqlParameter[] param2 = new SqlParameter[]
{
new SqlParameter("@SortId", dt.Rows[0]["SortId"]),
new SqlParameter("@CID", id),
new SqlParameter("@PSortId", sid),
new SqlParameter("@PCID", dt.Rows[0]["Id"])
};
int number = SQLHelp.ExecuteNonQuery(sbSql4org_up.ToString(), CommandType.Text, param2);
return(number);
}
}
else if (opertion == "down")
{
if (!string.IsNullOrWhiteSpace(ht["MenuId"].SafeToString()))
{
mwhere += " and MenuId in (" + ht["MenuId"].SafeToString() + ")";
}
if (ht.ContainsKey("isPush") && !string.IsNullOrWhiteSpace(ht["isPush"].SafeToString()))
{
mwhere += " and isPush=" + ht["isPush"].SafeToString();
}
sbSql4org.Append(@"with TB as (select top 999999 *,row_number() over(order by SortId desc) as rowid from Advertising where IsDelete=@IsDelete " + mwhere + " order by SortId desc)"
+ " select * from tb where rowid=(select rowid+1 from TB where Id=@id" + mwhere + ")");
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@IsDelete", ((int)SysStatus.正常).ToString()),
new SqlParameter("@id", id)
};
DataTable dt = SQLHelp.ExecuteDataTable(sbSql4org.ToString(), CommandType.Text, param);
if (dt == null || dt.Rows.Count == 0)
{
return(-2);///已经是最小值
}
else
{
sbSql4org_up.Append("update Advertising set SortId=@SortId where Id=@CID;update Advertising set SortId=@PSortId where Id=@PCID");
SqlParameter[] param2 = new SqlParameter[]
{
new SqlParameter("@SortId", sid),
new SqlParameter("@CID", dt.Rows[0]["Id"]),
new SqlParameter("@PSortId", dt.Rows[0]["SortId"]),
new SqlParameter("@PCID", id)
};
int number = SQLHelp.ExecuteNonQuery(sbSql4org_up.ToString(), CommandType.Text, param2);
return(number);
}
}
return(-999);
}
catch (Exception)
{
return(-999);
}
}
public string AddAcheiveRewardInfo(TPM_AcheiveRewardInfo entity)
{
List <SqlParameter> param = new List <SqlParameter>()
{
new SqlParameter("@Id", entity.Id),
new SqlParameter("@Name", entity.Name),
new SqlParameter("@GPid", entity.GPid),
new SqlParameter("@TeaUNo", entity.TeaUNo),
new SqlParameter("@Year", entity.Year),
new SqlParameter("@ResponsMan", entity.ResponsMan),
new SqlParameter("@DepartMent", entity.DepartMent),
new SqlParameter("@FileEdionNo", entity.FileEdionNo),
new SqlParameter("@FileNames", entity.FileNames),
new SqlParameter("@DefindDepart", entity.DefindDepart),
new SqlParameter("@FileInfo", entity.FileInfo),
new SqlParameter("@Status", entity.Status),
new SqlParameter("@TwoAudit_Status", entity.TwoAudit_Status),
new SqlParameter("@CreateUID", entity.CreateUID)
};
if (entity.Gid == 0)
{
param.Add(new SqlParameter("@Gid", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@Gid", entity.Gid));
}
if (entity.BookId == 0)
{
param.Add(new SqlParameter("@BookId", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@BookId", entity.BookId));
}
if (entity.Lid == 0)
{
param.Add(new SqlParameter("@Lid", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@Lid", entity.Lid));
}
if (entity.Rid == 0)
{
param.Add(new SqlParameter("@Rid", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@Rid", entity.Rid));
}
if (entity.Sort == 0)
{
param.Add(new SqlParameter("@Sort", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@Sort", entity.Sort));
}
if (entity.DefindDate == null)
{
param.Add(new SqlParameter("@DefindDate", DBNull.Value));
}
else
{
param.Add(new SqlParameter("@DefindDate", entity.DefindDate));
}
object obj = SQLHelp.ExecuteScalar("TPM_AddAcheiveRewardInfo", CommandType.StoredProcedure, param.ToArray());
return(obj.ToString());
}
/*
#region 判断用户存在
* public bool IsExistUser(string IDCard)
* {
* bool Flag = false;
* string str = "select count(1) from Sys_UserInfo where IDCard='" + IDCard + "'";
* object Count = SQLHelp.ExecuteScalar(str, CommandType.Text, null);
* if (Count.ToString() != "0")
* {
* Flag = true;
* }
* return Flag;
* }
#endregion
*/
#region 数据导入
/// <summary>
/// 数据导入
/// </summary>
/// <returns></returns>
public string ImportUser(string FilePath, string OrgNo)
{
string result = "";
ExcelHelper excelHelp = new ExcelHelper();
try
{
DataTable dt = excelHelp.ExcelToDataTable(FilePath);
bool Flag = Import(dt);
if (Flag)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@OrgNo", OrgNo)
};
object obj = SQLHelp.ExecuteScalar("ImportUserInfo", CommandType.StoredProcedure, param);
result = obj.ToString();
}
else
{
result = "数据导入失败";
}
/*StringBuilder FileR = new StringBuilder();
*
* int SucNum = 0;
* int ErrNum = 0;
* for (int i = 0; i < dt.Rows.Count; i++)
* {
* DataRow dr = dt.Rows[i];
* try
* {
* string IDCard = dr["身份证号"].SafeToString().Trim();
* string Name = dr["姓名"].SafeToString().Trim();
* string RegisterOrg = dr["组织机构号"].ToString().Trim();//组织机构号
* string InsertReslt = "";
* string Password = dr["密码"].SafeToString();
* Byte IsEnable= Convert.ToByte(dr["状态"].ToString().Trim());//状态
* string Phone = dr["电话"].ToString().Trim();//电话
* //不存在添加
* Sys_UserInfo userInfo = new Sys_UserInfo();
* userInfo.IDCard = dr["身份证号"].ToString().Trim();//登录名
* userInfo.LoginName = dr["登录账号"].ToString().Trim();//登录账号
* userInfo.RegisterOrg = RegisterOrg;
* userInfo.Password = EncryptHelper.Md5By32(Password);//密码
* userInfo.IsEnable = IsEnable;
* userInfo.Name = dr["姓名"].ToString().Trim();//姓名
* if (dr["性别"].ToString().Trim() == "男")
* {
* userInfo.Sex = 0;//性别
* }
* else
* {
* userInfo.Sex = 1;//性别
* }
* userInfo.Address = dr["家庭住址"].ToString().Trim();//家庭住址
* userInfo.Phone = Phone;//电话
* userInfo.Nickname = dr["昵称"].ToString().Trim();//昵称
* string UType = dr["用户类型"].SafeToString();
* if (UType.Length > 0)
* {
* userInfo.UserType = Convert.ToByte(UType);//用户类型
* }
* else
* {
* userInfo.UserType = 0;
* }
* string Birthday = dr["出生日期"].SafeToString();
* if (Birthday.Length > 0)
* {
* try
* {
* userInfo.Birthday = Convert.ToDateTime(Birthday);
* }
* catch (Exception ex)
* {
* userInfo.Birthday = null;
* }
* }
* else
* {
* userInfo.Birthday = DateTime.Now;
* }
* userInfo.Remarks = dr["简介"].ToString().Trim();//简介
* userInfo.AuthenType = 1;
* userInfo.IsDelete = 0;//是否删除
* userInfo.HeadPic = "";
* userInfo.CreateUID = "";
* if (string.IsNullOrWhiteSpace(IDCard) && string.IsNullOrWhiteSpace(Name) && string.IsNullOrWhiteSpace(UType) && string.IsNullOrWhiteSpace(Phone)
* && string.IsNullOrWhiteSpace(RegisterOrg) && string.IsNullOrWhiteSpace(Birthday) && string.IsNullOrWhiteSpace(IsEnable.ToString()))
* {
* continue;
* }
* InsertReslt = AddUserInfo(userInfo);
* //}
* if (InsertReslt == "")
* {
* SucNum++;
* }
* else
* {
* ErrNum++;
* FileR.Append("行号：" + (i + 1).ToString() + "失败原因：" + InsertReslt + ",\n");
* }
* }
* catch (Exception)
* {
* ErrNum++;
* FileR.Append((i + 1).ToString() + ",");
* }
* }
* result = "成功" + SucNum + "条，失败" + ErrNum + "条";
*
* if (FileR.Length != 0)
* {
* result += "\n失败日志：\n" + FileR.ToString();
* }*/
}
catch (Exception ex)
{
result = ex.Message;
}
return(result);
}
/// <summary>
/// 获得事务对象
/// </summary>
/// <returns></returns>
public SqlTransaction GetTran()
{
return(SQLHelp.BeginTransaction());
}
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();
bool isType = ht.ContainsKey("Type") && !string.IsNullOrEmpty(ht["Type"].ToString());
bool isOnlyBase = ht["IsOnlyBase"].ToString() != "0";
sbSql4org.Append(@"select new.* ");
if (isOnlyBase)
{
sbSql4org.Append(@",DATEDIFF(S,'1970-01-01 00:00:00', new.CreateTime) - 8 * 3600 as CreateTime_Stamp,isnull(Co.CommontCount,0) as CommontCount
,isnull((select top 1 CreateUID from Com_NewComment where [NewId]=new.Id order by CreateTime desc),'暂无回复') as LastComUID
,DATEDIFF(S,'1970-01-01 00:00:00', (select top 1 CreateTime from Com_NewComment where [NewId]=new.Id order by CreateTime desc)) - 8 * 3600 as LastComTime ");
}
if (isType)
{
if (ht["Type"].ToString() == "2")
{
sbSql4org.Append(" ,build.Name as BuildName ");
}
else
{
sbSql4org.Append(" ,rel.PicURL ");
}
sbSql4org.Append(" ,rel.Name as RelationName ");
if (ht.ContainsKey("LoginUID") && !string.IsNullOrEmpty(ht["LoginUID"].ToString()))
{
if (ht["Type"].ToString() != "2")
{
sbSql4org.Append(@",case when rel.LeaderNo = @LoginUID or rel.SecondLeaderNo = @LoginUID then 1 else 0 end as IsLeader");
}
else
{
sbSql4org.Append(@",case when rel.ManagerNo = @LoginUID then 1 else 0 end as IsLeader");
}
pms.Add(new SqlParameter("@LoginUID", ht["LoginUID"].ToString()));
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(",(select count(1) from Com_GoodClick where type=0 and RelationId=new.Id and IsDelete=0 and CreateUID=@LoginUID) as IsGoodClick");
}
}
}
sbSql4org.Append(@" from Com_NewInfo new ");
if (isOnlyBase)
{
sbSql4org.Append(@" left join (select [NewId],count(1) as CommontCount from Com_NewComment group by [NewId])Co on new.Id=Co.[NewId] ");
}
if (isType) //0社团；1部门；2宿舍
{
if (ht["Type"].ToString() == "0")
{
sbSql4org.Append(" left join Asso_Info rel on rel.Id=new.RelationId ");
}
else if (ht["Type"].ToString() == "1")
{
sbSql4org.Append(" left join Acti_DepartInfo rel on rel.Id=new.RelationId ");
}
else
{
sbSql4org.Append(" left join Dorm_Room rel on rel.Id=new.RelationId left join Dorm_Building build on rel.BuildId = build.Id ");
}
}
sbSql4org.Append(@" where 1=1 and new.IsDelete=0 ");
if (ht.ContainsKey("Id") && !string.IsNullOrEmpty(ht["Id"].ToString()))
{
sbSql4org.Append(" and new.Id=@Id ");
pms.Add(new SqlParameter("@Id", ht["Id"].ToString()));
}
if (ht.ContainsKey("Name") && !string.IsNullOrEmpty(ht["Name"].ToString()))
{
sbSql4org.Append(" and new.Name like N'%' + @Name + '%' ");
pms.Add(new SqlParameter("@Name", ht["Name"].ToString()));
}
if (isType)
{
sbSql4org.Append(" and new.Type=@Type ");
pms.Add(new SqlParameter("@Type", ht["Type"].ToString()));
}
if (ht.ContainsKey("RelationId") && !string.IsNullOrEmpty(ht["RelationId"].ToString()))
{
sbSql4org.Append(" and new.RelationId=@RelationId ");
pms.Add(new SqlParameter("@RelationId", ht["RelationId"].ToString()));
}
if (ht.ContainsKey("NewType") && !string.IsNullOrEmpty(ht["NewType"].ToString()))
{
sbSql4org.Append(" and new.NewType=@NewType ");
pms.Add(new SqlParameter("@NewType", ht["NewType"].ToString()));
if (ht["NewType"].ToString() == "1" && ht.ContainsKey("IsRecruit") && !string.IsNullOrEmpty(ht["IsRecruit"].ToString()))
{
sbSql4org.Append(" and new.RelationId!=0 ");
}
}
if (ht.ContainsKey("IsElite") && !string.IsNullOrEmpty(ht["IsElite"].ToString()))
{
sbSql4org.Append(" and new.IsElite=@IsElite ");
pms.Add(new SqlParameter("@IsElite", ht["IsElite"].ToString()));
}
if (ht.ContainsKey("CreateUID") && !string.IsNullOrEmpty(ht["CreateUID"].ToString()))
{
sbSql4org.Append(" and new.CreateUID=@CreateUID ");
pms.Add(new SqlParameter("@CreateUID", ht["CreateUID"].ToString()));
}
if (ht.ContainsKey("CommontCount") && !string.IsNullOrEmpty(ht["CommontCount"].ToString()))
{
sbSql4org.Append(" and isnull(Co.CommontCount,0)=@CommontCount ");
pms.Add(new SqlParameter("@CommontCount", ht["CommontCount"].ToString()));
}
string orderby = "IsTop desc,T.Id desc ";
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 DataTable Qurry(string sql)
{
return(SQLHelp.ExcuteQurry(sql));
}
private void AddUnSubmitWorkMessage()
{
try
{
string sql = @"select work.Id,work.CouseID,work.Name,co.CourceType from Course_Work work
left join Course co on work.CouseID=co.ID
where work.IsDelete=0 and convert(varchar(10),dateadd(d,1,work.EndTime),21)= convert(varchar(10),getdate(),21) ";
DataTable workDt = SQLHelp.ExecuteDataTable(sql, CommandType.Text, null);//获取可能需要发通知的作业
if (workDt != null && workDt.Rows.Count > 0)
{
StringBuilder sbSql4org = new StringBuilder();
sbSql4org.Append(@"INSERT INTO System_Message (Title,Contents,[Type],IsDelete,CreateTime,Creator,Receiver,Href,Status,isSend,ReceiverEmail,CreatorName,ReceiverName,Timing,FilePath)
values(@Title,@Contents,@Type,@IsDelete,@CreateTime,@Creator,@Receiver,@Href,@Status,@isSend,@ReceiverEmail,@CreatorName,@ReceiverName,@Timing,@FilePath)");
foreach (DataRow row in workDt.Rows)
{
DataTable studyDt = StudyTheCourseStu(row["CouseID"].ToString(), row["CourceType"].ToString());//获取学习这门课的学生
List <DataRow> rtnDt = (from dic in studyDt.AsEnumerable() select dic).ToList();
#region 根据作业id获取提交作业的学生
string corrsql = @"select CreateUID from Course_WorkCorrectRel where WorkId=@WorkId ";
List <SqlParameter> pms = new List <SqlParameter>();
pms.Add(new SqlParameter("@WorkId", row["Id"].ToString()));
DataTable corrDt = SQLHelp.ExecuteDataTable(corrsql, CommandType.Text, pms.ToArray());
#endregion
if (corrDt.Rows.Count > 0)
{
string[] corrArray = corrDt.AsEnumerable().Select(corrRow => corrRow["CreateUID"].ToString()).ToArray();
rtnDt = (from dic in studyDt.AsEnumerable()
where corrArray.Contains(dic["IDCard"].ToString()) == false
select dic).ToList();
}
if (rtnDt.Count > 0) //未提交作业的学生
{
string type = "8", title = "未提交作业";
string href = "/OnlineLearning/StuLessonDetail.aspx?itemid=" + row["CouseID"].ToString() + "&nav_index=4&relname=&flag=1&tabconid=" + row["Id"].ToString();
string contents = "您未在规定时间内提交作业——" + row["Name"].ToString() + "<br/><h3><a href=" + href + ">点击此处可查看详细信息</a></h3>";
for (int i = 0; i < rtnDt.Count; i++)
{
List <SqlParameter> spList = new List <SqlParameter>();
spList.Add(new SqlParameter("@Title", title));
spList.Add(new SqlParameter("@Contents", contents));
spList.Add(new SqlParameter("@Type", type));
spList.Add(new SqlParameter("@Receiver", rtnDt[i]["IDCard"].ToString()));
spList.Add(new SqlParameter("@Href", href));
#region 判断通知是否已存在
string isexistSql = @"select count(1) from System_Message where Title=@Title and Contents=@Contents
and [Type]=@Type and Receiver=@Receiver and Href=@Href ";
int est_count = Convert.ToInt32(SQLHelp.ExecuteScalar(isexistSql, CommandType.Text, spList.ToArray()));
#endregion
if (est_count == 0) //不存在
{
spList.Add(new SqlParameter("@IsDelete", ((int)SysStatus.正常).ToString()));
spList.Add(new SqlParameter("@CreateTime", DateTime.Now));
spList.Add(new SqlParameter("@Creator", ""));
spList.Add(new SqlParameter("@Status", ((int)MessageStatus.未读).ToString()));
spList.Add(new SqlParameter("@isSend", ((int)isSend.未发送).ToString()));
spList.Add(new SqlParameter("@ReceiverEmail", ""));
spList.Add(new SqlParameter("@CreatorName", ""));
spList.Add(new SqlParameter("@ReceiverName", rtnDt[i]["Name"].ToString()));
spList.Add(new SqlParameter("@Timing", "0"));
spList.Add(new SqlParameter("@FilePath", ""));
int number = SQLHelp.ExecuteNonQuery(sbSql4org.ToString(), CommandType.Text, spList.ToArray());
}
}
}
}
}
}
catch (Exception ex)
{
_logger.Info("AddUnSubmitWorkMessageJob：" + ex.Message);
}
}
public void Update(string sql)
{
SQLHelp.ExcuteUpdate(sql);
}
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 app.*,asso.Name as AssoName from Asso_Apply app
left join Asso_Info asso on asso.Id=app.AssoId ");
sbSql4org.Append(@" where 1=1 ");
//1待审核，2审核通过，3审核拒绝
if (ht.ContainsKey("ExamStatus") && !string.IsNullOrEmpty(ht["ExamStatus"].ToString()))
{
sbSql4org.Append(" and app.ExamStatus=@ExamStatus ");
pms.Add(new SqlParameter("@ExamStatus", ht["ExamStatus"].ToString()));
}
//1入团申请，2退团申请
if (ht.ContainsKey("ApplyType") && !string.IsNullOrEmpty(ht["ApplyType"].ToString()))
{
sbSql4org.Append(" and app.ApplyType=@ApplyType ");
pms.Add(new SqlParameter("@ApplyType", ht["ApplyType"].ToString()));
}
//申请人
if (ht.ContainsKey("ApplyUserNo") && !string.IsNullOrEmpty(ht["ApplyUserNo"].ToString()))
{
sbSql4org.Append(" and app.ApplyUserNo=@ApplyUserNo ");
pms.Add(new SqlParameter("@ApplyUserNo", ht["ApplyUserNo"].ToString()));
}
//社团长/副社团长
if (ht.ContainsKey("OSLeaderNo") && !string.IsNullOrEmpty(ht["OSLeaderNo"].ToString()))
{
sbSql4org.Append(" and (asso.LeaderNo=@OSLeaderNo or asso.SecondLeaderNo=@OSLeaderNo) ");
pms.Add(new SqlParameter("@OSLeaderNo", ht["OSLeaderNo"].ToString()));
}
//社团id
if (ht.ContainsKey("AssoId") && !string.IsNullOrEmpty(ht["AssoId"].ToString()))
{
sbSql4org.Append(" and app.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);
}
}
//添加课程
public int AddCourse(string courseName, string courseNO)
{
string sql = string.Format("insert into t_course values('{0}','{1}','{2}')", courseNO, this.ID, courseName);
return(SQLHelp.ExcuteUpdate(sql));
}
