public void Asp2(DataList gv, AspNetPager pager, UserHolidayEntity mo)
{
DataBase db = new DataBase();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[0].Value = mo.UserID;
string comstr = "with UserHolidayInfo as\r\n (\r\n select UserHoliday.*,(case when RecordState=1 then '未读' when RecordState=2 then '已读,认同加分' else '已读,不认同加分' end ) as CheckState,Users.UserName as UserName,ChangDes,Row_Number() over(order by UserHolidayID) as row_number from Users,UserHoliday,UserIntegraDetail where UserHoliday.UserID=Users.UserID,Users.UserID=UserIntegraDetail.UserID";
// string comstr = "with WrongProblemInfo as\r\n (\r\n select WrongProblem.*,(case when RecordState=1 then '教师未读' when RecordState=2 then '教师已读,认同加分' else '教师已读,不认同加分' end ) as CheckState,Problems.ProblemName as ProblemName,Problems.ProblemDes as ProblemDes,Users.UserName as UserName,Row_Number() over(order by WrongProblemID) as row_number from Problems,Users,WrongProblem where WrongProblem.UserID=Users.UserID and WrongProblem.ProblemID=Problems.ProblemID";
string comstr2 = "select count(*) from Users,UserHoliday where UserHoliday.UserID=Users.UserID ";
if (mo.UserID != null && mo.UserID != "")
{
comstr += " and UserHoliday.UserID=@UserID";
comstr2 += " and UserHoliday.UserID=@UserID";
}
comstr += " )select * from UserHolidayInfo where row_number>{0} and row_number<={1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public DataTable GetAll()
{
DataBase db = new DataBase();
string comstr = "select DepartmentID,DepartmentName,DepartmentDes,X,Y,Z from Department";
return(db.GetDataTable(comstr));
}
public DataTable GetAll()
{
DataBase db = new DataBase();
string comstr = "select users.* from users where 1=1";
return(db.GetDataTable(comstr));
}
public void Asp(GridView gv, AspNetPager pager, UserIntegralDetailEntity mo)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@DetailID", SqlDbType.Int);
param[0].Value = mo.DetailID;
param[1] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[1].Value = mo.UserID;
string comstr = "with IntegralInfo as\r\n (\r\n select ui.DetailID as DetailID,ui.UserID as UserID,ui.IntegralChange as IntegralChange,ui.ChangeDes as ChangeDes,us.UserName as UserName,Row_Number() over(order by DetailID) as row_number from UserIntegralDetail ui,Users us where ui.UserID=us.UserID";
string comstr2 = "select count(*) from UserIntegralDetail ui,Users us where ui.UserID=us.UserID";
if (mo.DetailID != 0)
{
comstr += " and ui.DetailID=@DetailID";
comstr2 += " and ui.DetailID=@DetailID";
}
if (mo.UserID != null && mo.UserID != "")
{
comstr += " and ui.UserID=@UserID";
comstr2 += " and ui.UserID=@UserID";
}
comstr += " ) select * from IntegralInfo where row_number>{0} and row_number<{1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public DataTable GetAll(PaperTypeEntity en)
{
DataBase db = new DataBase();
string sql = "select PTID,PTName,PTDesc from PaperType";
return(db.GetDataTable(sql));
}
public DataTable GetAll()
{
string sql = "select PPID,PRKID,PTID,Name,PICPath,SelfResource,UpdateTime,PPDesc,ProblemNum from ProblemPaper";
DataBase db = new DataBase();
return(db.GetDataTable(sql));
}
public void Asp_Holiday(GridView gv, AspNetPager pager, UserEntity en)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[0].Value = en.UserID;
param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
param[1].Value = en.UserName;
string comstr = "with UsersInfo as(select users.*,\r\n (\r\n select count(*) from UserHoliday where UserHoliday.UserID=users.UserID and UserHoliday.RecordState=1) as num,\r\n Row_Number() over(order by users.UserID) as row_number from users where 1=1";
string comstr2 = "select count(*) from users where 1=1 \t";
if (en.UserID != null && en.UserID != "")
{
comstr += " and UserID=@UserID";
comstr2 += " and UserID=@UserID";
}
if (en.UserName != null && en.UserName != "")
{
comstr += " and UserName Like '%'+@UserName+'%'";
comstr2 += " and UserName Like '%'+@UserName+'%'";
}
comstr += " )select * from UsersInfo where row_number>{0} and row_number<={1};";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public DataTable GetAll()
{
DataBase db = new DataBase();
string comstr = "select PowerID,PowerName,PowerDes from Powers";
return(db.GetDataTable(comstr));
}
public void Asp(GridView gv, AspNetPager pager, DepartmentEntity en)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@DepartmentName", SqlDbType.VarChar, 100)
};
param[0].Value = en.DepartmentName;
DataBase db = new DataBase();
string comstr = "with DepartmentsInfo as\r\n(select *,Row_Number() over(order by DepartmentID) as row_number from Department\twhere 1=1";
// string comstr="with DepartmentsInfo as\r\n(select *,Row_Number() over(order by DepartmentID) as row_number from Department\twhere 1=1";
string comstr2 = "select count(*) from Department\twhere 1=1";
// string comstr2 = "select count(*) from Department\twhere 1=1";
if (en.DepartmentName != null && en.DepartmentName != "")
{
comstr += " and DepartmentName Like '%'+@DepartmentName+'%'";
comstr2 += " and DepartmentName Like '%'+@DepartmentName+'%'";
}
comstr += " )select * from DepartmentsInfo where row_number>{0} and row_number<={1}";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public DataTable GetAll()
{
DataBase db = new DataBase();
string sql = "select * from UserIntegralDetail";
return(db.GetDataTable(sql));
}
public DataTable select(ManagerEnitity en)
{
DataBase db = new DataBase();
string comstr = "select ManagerName from Manager where ManagerID=@ManagerID";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ManagerID", SqlDbType.VarChar, 50);
param[0].Value = en.ManagerID;
return(db.GetDataTable(comstr, param));
}
public DataTable select(ClientEntity en)
{
DataBase db = new DataBase();
string comstr = "select ClientName from Client where ClientID=@ClientID";
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@ClientID", SqlDbType.VarChar, 50);
param[0].Value = en.ClientID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByID(PowerEntity en)
{
DataBase db = new DataBase();
string comstr = "select PowerID,PowerName,PowerDes from Powers where PowerID=@PowerID";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("PowerID", SqlDbType.Int, 4)
};
param[0].Value = en.PowerID;
return(db.GetDataTable(comstr, param));
}
public void Asp(GridView gv, AspNetPager pager)
{
DataBase db = new DataBase();
string comstr = "with PowersInfo as \r\n(\r\n\tselect *,Row_Number() over(order by PowerID ) as row_number from Powers \r\n)\r\nselect * from PowersInfo where row_number>{0} and row_number<={1}";
string comstr2 = "select count(*) from Powers";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2));
gv.DataSource = db.GetDataTable(comstr);
gv.DataBind();
}
public DataTable GetByID(DepartmentEntity en)
{
DataBase db = new DataBase();
string comstr = "select DepartmentName,DepartmentDes,X,Y,Z from Department where DepartmentID=@DepartmentID";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@DepartmentID", SqlDbType.Int, 4)
};
param[0].Value = en.DepartmentID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByID(PagesEnitity en)
{
DataBase db = new DataBase();
string comstr = "select p.pageDes,p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl from pages p where p.id=@ID";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@ID", SqlDbType.Int, 4)
};
param[0].Value = en.Id;
return(db.GetDataTable(comstr, param));
}
public void Asp(DataList gv, AspNetPager pager)
{
DataBase db = new DataBase();
string sql = @"with OrderTable as(select ItemID,ParentID,ItemName,ItemContent,ItemSort,ItemKind,ItemUrl,IsTop,IsHome,NewsSort,DisplayNum,DepartID,Row_Number() over(order by ItemID,ItemSort)as row_number from Item) select * from OrderTable where row_number>{0} and row_number<{1}";
string sql1 = "select count(*) from Item";
sql = string.Format(sql, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(sql1));
gv.DataSource = db.GetDataTable(sql);
gv.DataBind();
}
public DataTable GetByID(ManagerEnitity en)
{
DataBase db = new DataBase();
string comstr = "select Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n,Powers.PowerName,Department.DepartmentName\tfrom Manager,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID\r\nand ManagerID=@ManagerID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ManagerID", SqlDbType.VarChar, 500)
};
param[0].Value = en.ManagerID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetProblemNum(ProjectPaperEntity en)
{
DataBase db = new DataBase();
string sql = "select ProblemNum from ProblemPaper where PPID=@PPID";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@PPID", SqlDbType.Int)
};
para[0].Value = en.PPID;
return(db.GetDataTable(sql, para));
}
public DataTable GetByID(ManagerPagePowerEnitity en)
{
DataBase db = new DataBase();
string comstr = "select p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl from pages p ,ManagerPagePower tpp,Manager t where tpp.id=p.id and tpp.managerid=t.managerid and tpp.TPP_ID=@ID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@TPP_ID", SqlDbType.Int, 4)
};
param[0].Value = en.Tpp_ID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByManager(ManagerPagePowerEnitity en)
{
DataBase db = new DataBase();
string comstr = "select pagesort as id,pagename as title ,pageurl as url,pid as ParentID from Managerpagepower tpp,pages p where p.id=tpp.id and tpp.Managerid=@ManagerID order by p.pagesort ";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ManagerID", SqlDbType.VarChar, 50)
};
param[0].Value = en.ManagerID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetAllByManager(ManagerPagePowerEnitity en)
{
DataBase db = new DataBase();
string comstr = "select p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl ,(select count(*) from pages pp where pp.parentid=p.id )as ChildCount ,tpp.managerid from managerpagepower tpp,pages p where p.id=tpp.id and tpp.Managerid=@ManagerID order by p.pagesort ";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ManagerID", SqlDbType.VarChar, 50)
};
param[0].Value = en.ManagerID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByID(UserEntity en)
{
DataBase db = new DataBase();
string comstr = "select users.*,DepartmentName from users,Department where userid=@UserID and users.DepartmentID= Department.DepartmentID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UserID", SqlDbType.VarChar, 50)
};
param[0].Value = en.UserID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByParent(PagesEnitity en)
{
DataBase db = new DataBase();
string comstr = "select p.pageDes,p.id,p.pagesort,p.parentid,p.pid,p.pagename,p.pageurl ,(select count(*) from pages pp where pp.parentid=p.id )as ChildCount from pages p where p.parentid=@ParentID order by p.pagesort";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ParentID", SqlDbType.Int, 4)
};
param[0].Value = en.ParentID;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByUrl(PagesEnitity en)
{
DataBase db = new DataBase();
string comstr = "select p.pageDes from pages p where p.PageUrl=@PageUrl";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@PageUrl", SqlDbType.VarChar, 200)
};
param[0].Value = en.PageURL;
return(db.GetDataTable(comstr, param));
}
public DataTable GetByID(ClientEntity en)
{
DataBase db = new DataBase();
string comstr = "select Client.ClientID,ClientName,ClientPwd,ClientImage,ClientDes\r\nfrom Client\r\nwhere ClientID=@ClientID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ClientID", SqlDbType.VarChar, 500)
};
param[0].Value = en.ClientID;
return(db.GetDataTable(comstr, param));
}
public void Asp(GridView gv, AspNetPager pager, ManagerEnitity en)
{
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@ManagerID", SqlDbType.VarChar, 50);
param[0].Value = en.ManagerID;
param[1] = new SqlParameter("@DepartmentID", SqlDbType.Int, 4);
param[1].Value = en.DepartmentID;
param[2] = new SqlParameter("@PowerID", SqlDbType.Int, 4);
param[2].Value = en.PowerID;
param[3] = new SqlParameter("@ManagerName", SqlDbType.VarChar, 100);
param[3].Value = en.ManagerName;
/*row_number() over (order by '字段名')是
* 微软最新发布的MSSQL2005,对TSQL进行了小规模的加强 按照字段名进行排序，可以实现数据分页功能
*
*/
string comstr = "with ManagerInfo as\r\n(\r\nselect Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n ,Powers.PowerName,Department.DepartmentName\t,\r\nRow_Number() over(order by ManagerID) as row_number\tfrom Manager ,Powers,Department\r\nwhere\tManager.PowerID=powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID";
// string comstr = "with ManagerInfo as\r\n(\r\nselect Manager.ManagerID,Manager.DepartmentID,Manager.PowerID,ManagerName,ManagerPwd,ManagerImage,ManagerDes\r\n,Powers.PowerName,Department.DepartmentName\t,\r\nRow_Number() over(order by ManagerID) as row_number\tfrom Manager,Powers,Department\r\nwhere\tManager.PowerID=powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID)";
// string comstr2 = "select count(*) from Manager,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\t and Manager.DepartmentID=Department.DepartmentID";
string comstr2 = "select count(*)\tfrom Manager ,Powers,Department\r\nwhere\tManager.PowerID=Powers.PowerID\tand Manager.DepartmentID=Department.DepartmentID";
if (en.DepartmentID != 0)
{
comstr += " and Manager.DepartmentID=@DepartmentID";
comstr2 += " and Manager.DepartmentID=@DepartmentID";
}
if (en.PowerID != 0)
{
comstr += " and Manager.PowerID=@PowerID";
comstr2 += " and Manager.PowerID=@PowerID";
}
if (en.ManagerID != "" && en.ManagerID != null)
{
comstr += " and ManagerID=@ManagerID";
comstr2 += " and ManagerID=@ManagerID";
}
if (en.ManagerName != "" && en.ManagerName != null)
{
comstr += " and ManagerName Like '%'+@ManagerName+'%'";
comstr2 += " and ManagerName Like '%'+ManagerName+'%'";
}
comstr += " )select * from ManagerInfo where row_number>{0} and row_number<={1};";
comstr = string.Format(comstr, (pager.CurrentPageIndex - 1) * pager.PageSize, pager.CurrentPageIndex * pager.PageSize);
DataBase db = new DataBase();
pager.RecordCount = Convert.ToInt32(db.ExecuteValue(comstr2, param));
gv.DataSource = db.GetDataTable(comstr, param);
gv.DataBind();
}
public DataTable GetById(ProjectPaperEntity en)
{
DataBase db = new DataBase();
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@PPID", SqlDbType.Int, 4)
};
param[0].Value = en.PPID;
string sql = "select ProjectPaper.*,PaperType.PTName from ProjectPaper,PaperType where PPID=@PPID and PaperType.PTID=ProjectPaper.PTID";
return(db.GetDataTable(sql, param));
}
public DataTable GetById(RecordEntity en)
{
DataBase db = new DataBase();
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@RecordID", SqlDbType.Int, 4)
};
param[0].Value = en.RecordID;
string sql = "select Record.* from Record where RecordID=@RecordID ";
return(db.GetDataTable(sql, param));
}
public DataTable GetByID(UserIntegralDetailEntity mo)
{
DataBase db = new DataBase();
string sql = "select ui.DetailID as DetailID,ui.UserID as UserID,ui.IntegralChange as IntegralChange,ui.ChangeDes as ChangeDes,us.UserName as UserName from UserIntegralDetail ui,Users us where ui.UserID=us.UserID";
if (mo.DetailID != 0)
{
sql += " and ui.DetailID=@DetailID";
}
if (mo.UserID != null && mo.UserID != "")
{
sql += " and ui.UserID=@UserID";
}
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@DetailID", SqlDbType.Int);
param[0].Value = mo.DetailID;
param[1] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
param[1].Value = mo.UserID;
return(db.GetDataTable(sql, param));
}