/// <summary>
/// 搜索
/// </summary>
/// <param name="key">搜索值</param>
/// <param name="page">页数 1开始</param>
/// <param name="row">行数 1开始</param>
/// <returns></returns>
public static DataSet PagerRowByKey(string key, int page, int row)
{
int top = 12 * (page - 1) + (row - 1) * 4;
sqltext = "select top 4 [DesImg1Url] as 描述图片, [GID] as 商品ID,[Name] as 商品名称,SUBSTRING([Name],0,7) as 小标题,[SCPrice] as 市场价,[CXPrice] as 促销价 from [dbo].[YC_Goods] where [GID] not in(select top " + top + " GID from YC_Goods where YC_Goods.Name Like '%" + key + "%' order by GID desc) AND YC_Goods.Name Like '%" + key + "%' order by [GID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 首页的三行。每行数据的查询
/// </summary>
/// <param name="RowIndex"></param>
/// <returns></returns>
public static DataSet PagerIndexPage(int RowIndex)
{
///每行数量是4这里写死了的
///利用子查询进行
sqltext = " select top 4 [GID] as 商品ID,[DesImg1Url] as 描述图片,[Name] as 商品名称,SUBSTRING([Name],0,7) as 小标题,[SCPrice] as 市场价,[CXPrice] as 促销价 from [dbo].[YC_Goods] where [GID] not in(select top (4*" + RowIndex + ") GID from YC_Goods order by GID desc) order by [GID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//根据ID查询员工信息(精确查找)
public static DataSet selectOneByUID(UserInfo userinfo)
{
sqltext = "select a.uid as 员工编号,a.jobid as 职位ID,a.name as 员工名字,a.sex as 性别,a.birthday as 生日,a.qq as QQ号码,a.phone as 手机号, b.did as 部门编号,b.jobname as 职务,a.address 地址,a.enable 是否禁用 from UserInfo as a inner join departjob as b on a.jobid=b.jobid where a.uid='" + userinfo.Uid + "';";
DataSet st = SQLHELPER.ExecuteDataSet(sqltext);
return(st);
}
/// <summary>
/// 分页分行子查询
/// </summary>
/// <param name="GTID">商品分类ID</param>
/// <param name="page">当前页数从1开始</param>
/// <param name="row">当前行数从1开始（1-3）</param>
/// <returns></returns>
public static DataSet PagerRowByGTID(int GTID, int page, int row)
{
/*分页分行查询
* 每一页3行，每行4个
* 一页十二个
* 子查询top语句 是 top (当前页数-1)*12+4*(当前行数-1)
*/
int top = (page - 1) * 12 + 4 * (row - 1);
sqltext = "select top 4 [DesImg1Url] as 描述图片, [GID] as 商品ID,[Name] as 商品名称,SUBSTRING([Name],0,7) as 小标题,[SCPrice] as 市场价,[CXPrice] as 促销价 from [dbo].[YC_Goods] where [GID] not in(select top " + top + " GID from YC_Goods where GTID ='" + GTID + "' order by GID desc) AND GTID ='" + GTID + "' order by [GID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询最新的一条 返回
public static YC_Website selectLastestOne()
{
sqltext = " select top 1 * from [dbo].[YC_WebSite] order by [WID] desc";
DataTable dt = SQLHELPER.ExecuteDataSet(sqltext).Tables[0];
DataRow dr = dt.Rows[0];
YC_Website wb = new YC_Website();
wb.Wname = dr["WName"].ToString();
wb.Logourl = dr["LogoUrl"].ToString();
wb.Lunboimgurl = dr["LunboImgUrl"].ToString();
wb.Companydescription = dr["CompanyDescription"].ToString();
wb.Ourteam = dr["OurTeam"].ToString();
wb.Contactus = dr["ContactUs"].ToString();
wb.ContactUsWeChatImage = dr["ContactUsWeChatImage"].ToString();
wb.TodayMovieUrl = dr["TodayMovieUrl"].ToString();
return(wb);
}
/// <summary>
/// 查询一个收货地址信息 通过收货地址ID
/// </summary>
/// <param name="aid"></param>
/// <returns></returns>
public static DataSet SelectOneByAId(int aid)
{
sqltext = " select * from [dbo].[YC_Address] where AID='" + aid + "'";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询所有待收货订单
public static DataSet selectAllUnFinishedOrder()
{
sqltext = " select isClosed,YC_User.Name as 购买用户, [dbo].[YC_Order].[OID] as 订单ID, [dbo].[YC_Goods].[Name] as 商品名称,[Price] as 付款金额,[dbo].[YC_Order].[Address] as 收货地址 ,[BuyTime] as 购买时间,[dbo].[YC_Order].SendType as 发货方式,[dbo].[YC_Order].WayNumber as 运单号 from YC_User, [dbo].[YC_Order],[dbo].[YC_Goods] where YC_User.UID=YC_Order.UID and [dbo].[YC_Order].[GID]=[dbo].[YC_Goods].[GID] AND [dbo].[YC_Order].[SendTime] is not null AND [dbo].[YC_Order].[FinishedTime] is null AND isClosed is null order by [dbo].[YC_Order].OID desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 查询某一个用户的所有订单
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public static DataSet selectOneOrder(int uid)
{
sqltext = " select [dbo].[YC_Order].[OID] as 订单ID, [dbo].[YC_Goods].[Name] as 商品名称,[Price] as 付款金额,[dbo].[YC_Order].[Address] as 收货地址 ,[BuyTime] as 购买时间 from [dbo].[YC_Order],[dbo].[YC_Goods] where [dbo].[YC_Order].[GID]=[dbo].[YC_Goods].[GID] AND [dbo].[YC_Order].UID='" + uid + "' order by [dbo].[YC_Order].OID desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询所有商品类型
public static DataSet selectAll()
{
sqltext = " select * from [dbo].[YC_GoodType] order by [GTID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询我的未完成日程
public static DataSet selectUnFinishedBYUID(int uid)
{
sqltext = " select * from [dbo].[richeng] where ([isFinished]='0' or [isFinished] is null) and [uid]='" + uid + "' order by [time] asc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
} //查询所有
public static DataSet all()
{
sqltext = " select * from [dbo].[Gongggao] order by gid desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询某一部门下的所有未审核的记录
public static DataSet selectAllunSHBydid(int did)
{
sqltext = "select [dbo].[gongwen].[qid] as 公文ID,[dbo].[gongwen].[season] as 详情,[dbo].[gongwen].[time] as 时间,[isaccept] as 是否同意 from [dbo].[gongwen] ,[dbo].[UserInfo], [dbo].[department],[dbo].[departjob] where [dbo].[UserInfo].uid=[dbo].[gongwen].uid and [dbo].[UserInfo].jobid= [dbo].[departjob].jobid and [dbo].[departjob].did = [dbo].[department].did and [dbo].[department].did='" + did + "' and [dbo].[gongwen].isaccept='0' order by qid desc";
return
(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询某一个人当月的工资
public static DataSet selectone(int uid)
{
sqltext = "select [dbo].[departjob].dx as 底薪, sum(0.05*[yj]) as 提成,[dbo].[departjob].dx+sum(0.05*[yj]) as 总工资,[dbo].[UserInfo].name as 员工名称 from [dbo].[departjob],[dbo].[UserInfo],[dbo].[Jxgl] where [dbo].[UserInfo].jobid=[dbo].[departjob].jobid and [dbo].[Jxgl].uid=[dbo].[UserInfo].uid and month([Jxgl].time)=MONTH(GETDATE()) and [dbo].[UserInfo].uid='" + uid + "' group by [dbo].[UserInfo].name,[dbo].[departjob].dx";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//模糊查询 所有用户
public static DataSet selectAllLike(string key)
{
sqltext = "select * from [dbo].[YC_User] where [Name] LIKE '%" + key + "%'or [NickName] LIKE '%" + key + "%' order by [UID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询一个用户的信息
public static DataSet selectOneByuid(int uid)
{
sqltext = "select * from [dbo].[YC_User] where UID=" + uid + "";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//根据uid查询 所有公文
public static DataSet selectAllGwByuid(int UID)
{
sqltext = "select * from [gongwen] where uid='" + UID + "'";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询所有未审核公文(所有部门)
public static DataSet selectAllunaccept()
{
sqltext = "select [dbo].[gongwen].[qid] as 公文ID,[dbo].[gongwen].[season] as 详情,[dbo].[gongwen].[time] as 时间,[isaccept] as 是否同意 from [dbo].[gongwen] ,[dbo].[UserInfo] where [dbo].[UserInfo].uid=[dbo].[gongwen].uid and [dbo].[gongwen].isaccept='0' order by qid desc";
return
(SQLHELPER.ExecuteDataSet(sqltext));
}
//迟到次数统计
public static DataSet signinTimeOut()
{
sqltext = " select [dbo].[UserInfo].[uid] as 员工ID, [dbo].[UserInfo].[name] as 员工名称, count(*) as 迟到次数 from [dbo].[SignIn],[dbo].[UserInfo] where ([dbo].[UserInfo].uid = [dbo].[SignIn].uid) and( (DATEPART(hour,time)=8 and DATEPART(MINUTE,time)>50) or (DATEPART(hour,time)>=9) )and type=1 and DATEPART(MONTH,time)=DATEPART(month,getdate()) group by [dbo].[UserInfo].[uid] , [dbo].[UserInfo].[name]";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询一个
public static DataSet selectLastOne()
{
sqltext = " select top 1 * from [dbo].[Gongggao] order by [gid] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//出勤排行
public static DataSet countcq()
{
sqltext = " select [dbo].[UserInfo].[uid] as 员工ID,count(*) 出勤次数,[dbo].[UserInfo].name as 员工姓名 from [dbo].[SignIn],[dbo].[UserInfo] where [dbo].[SignIn].uid=[dbo].[UserInfo].uid and [SignIn].type=1 group by [dbo].[UserInfo].[uid],[dbo].[UserInfo].name order by count(*) desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//销量饼状图
public static DataSet pieCount()
{
sqltext = "select count(*) as 销量,[dbo].[YC_Goods].[Name] as 名称 from [dbo].[YC_Goods],[dbo].[YC_Order] where [dbo].[YC_Goods].GID=[dbo].[YC_Order].GID group by [dbo].[YC_Goods].[Name] order by count(*) desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//查询所有用户的通讯录
public static DataTable selectallComm()
{
sqltext = "select [name] as 员工名称,[phone] as 手机号码 from [dbo].[UserInfo] order by uid asc";
return(SQLHELPER.ExecuteDataSet(sqltext).Tables[0]);
}
//查询我的所有日程
public static DataSet selectAllByUid(int uid)
{
sqltext = " select * from [dbo].[richeng] where [uid]='" + uid + "' order by [time] asc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//模糊查询通讯录
public static DataTable selectCommLike(string key)
{
sqltext = "select [name] as 员工名称,[phone] as 手机号码 from [dbo].[UserInfo] where name like '%" + key + "%' order by uid asc";
return(SQLHELPER.ExecuteDataSet(sqltext).Tables[0]);
}
/// <summary>
/// 通过订单ID查询订单(多表查询，订单表和商品表)
/// </summary>
/// <param name="oid"></param>
/// <returns></returns>
public static DataSet selectOneOrderByOID(int oid)
{
sqltext = " select YC_Order.Price as 价格,YC_Order.Count as 数量,YC_Order.Address as 收货地址,YC_Order.GoodSize as 尺寸,YC_Order.BuyTime as 拍下时间,YC_Goods.Name as 商品名称 ,YC_Goods.DesImg1Url as 图片 from [dbo].[YC_Goods],[dbo].[YC_Order] where [dbo].[YC_Goods].GID=[dbo].[YC_Order].GID AND YC_Order.OID='" + oid + "'";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 查询自己资料
public static DataSet selectMYInfo(int uid)
{
sqltext = " select [uid],[name],[sex],[birthday],[qq],[phone],[address],[idCard],[headImgUrl] from [dbo].[UserInfo] where uid='" + uid + "'";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 查询所有未发货订单([dbo].[YC_Order].[SendTime] is null)
/// </summary>
/// <returns></returns>
public static DataSet selectAllUnSendOrder()
{
sqltext = " select isClosed, YC_User.Name as 购买用户, [dbo].[YC_Order].[OID] as 订单ID, [dbo].[YC_Goods].[Name] as 商品名称,[Price] as 付款金额,[dbo].[YC_Order].[Address] as 收货地址 ,[BuyTime] as 购买时间 from [dbo].[YC_User], [dbo].[YC_Order],[dbo].[YC_Goods] where [dbo].[YC_Order].[GID]=[dbo].[YC_Goods].[GID] AND [dbo].[YC_Order].[SendTime] is null AND [dbo].[YC_User].[UID]= [dbo].[YC_Order].[UID] AND isClosed is null order by [dbo].[YC_Order].OID desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
//职位表的所有信息
public static DataSet selectall()
{
sqltext = "select *from departjob";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 查询某一个用户的所有待收货订单
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public static DataSet selectOneUnFinishedOrder(int uid)
{
sqltext = " select isClosed,[dbo].[YC_Order].[OID] as 订单ID, [dbo].[YC_Goods].[Name] as 商品名称,[Price] as 付款金额,[dbo].[YC_Order].[Address] as 收货地址 ,[BuyTime] as 购买时间 from [dbo].[YC_Order],[dbo].[YC_Goods] where [dbo].[YC_Order].[GID]=[dbo].[YC_Goods].[GID] AND [dbo].[YC_Order].[SendTime] is not null AND [dbo].[YC_Order].[FinishedTime] is null AND [dbo].[YC_Order].UID='" + uid + "' AND isClosed is null order by [dbo].[YC_Order].OID desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}
/// <summary>
/// 查询我的所有收货地址
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public static DataSet selectAllAddress(int uid)
{
sqltext = " select * from [dbo].[YC_Address] where [UID] ='" + uid + "' order by [AID] desc";
return(SQLHELPER.ExecuteDataSet(sqltext));
}