//登陆操作
public static int Login(UserInfo user)
{
sqltext = "SELECT COUNT(*) FROM UserInfo Where uid='" + user.Uid + "' AND password='******'AND enable='1'";
int i = (int)SQLHELPER.ExecuteScalar(sqltext);
return(i);
}
//职位表假删除
public static int jobdeletefalse(UserInfo uf)
{
sqltext = "update UserInfo set enable='0' where uid='" + uf.Uid + "'";
int i = (int)SQLHELPER.ExecuteNonQuery(sqltext);
return(i);
}
//根据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="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));
}
/// <summary>
/// 发送邮件
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static object sendEmai(ycEmail email)
{
sqltext = "insert into [dbo].[ycEmail]([Title],[detail],[sendtime],[writer],[receiver],[isRead],[isdelete],[isOwner])values('" + email.Title + "','" + email.Detail + "',getdate(),'" + email.Writer + "','" + email.Receiver + "','0','0','0')";
SQLHELPER.ExecuteNonQuery(sqltext);
sqltext = "insert into [dbo].[ycEmail]([Title],[detail],[sendtime],[writer],[receiver],[isRead],[isdelete],[isOwner])values('" + email.Title + "','" + email.Detail + "',getdate(),'" + email.Writer + "','" + email.Receiver + "','0','0','1')";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
/// <summary>
/// 查询学生信息通过ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataSet selectStuById(int id)
{
SqlParameter [] param =
{
new SqlParameter("@id", DbType.Int32)
};
param[0].Value = id;
return(SQLHELPER.ExecuteDataSetBYStoredProc("selectstubyid", param));
}
//判断是否是管理员
public static object isAdmin(int uid)
{
if (uid == 1)
{
return(1);
}
sqltext = " select [Admin] from [dbo].[YC_User] where [UID]=" + uid + "";
return(SQLHELPER.ExecuteScalar(sqltext));
}
/// <summary>
/// 登录
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int login(UserInfo entity)
{
string sqltext = "select COUNT(*) from dbo.UserInfo where Name=@name and PassWord=@pwd";
SqlParameter[] parameters =
{
new SqlParameter("@name", entity.Name),
new SqlParameter("@pwd", entity.PassWord)
};
return(Convert.ToInt32(SQLHELPER.ExecuteScalar(sqltext, parameters)));
}
/// <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));
}
/// <summary>
/// 添加学生
/// </summary>
/// <param name="stu"></param>
/// <returns></returns>
public static int Add(Student stu)
{
SqlParameter[] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@name", SqlDbType.NVarChar, 20),
new SqlParameter("@address", SqlDbType.NVarChar, 20)
};
param[0].Value = stu.StudnetNo;
param[1].Value = stu.StudentName;
param[2].Value = stu.Address;
////
return(SQLHELPER.ExecuteNoQueryBYStoredProc("createstu", param));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="stu"></param>
public static int Update(Student stu)
{
SqlParameter [] param =
{
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@name", SqlDbType.NVarChar, 20),
new SqlParameter("@phone", SqlDbType.NVarChar, 20),
new SqlParameter("@address", SqlDbType.NVarChar, 20)
};
param[0].Value = stu.StudnetNo;
param[1].Value = stu.StudentName;
param[2].Value = stu.Phone;
param[3].Value = "四川成都";
return(SQLHELPER.ExecuteNoQueryBYStoredProc("updatenamephoneaddressbyid", param));
}
public int add(UserInfo entity)
{
string sqlstr = " insert into dbo.UserInfo( Name, NickName, PassWord, Sex, Birthday, Address, EMail)VALUES(@Name,@NickName,@Password,@Sex,@Birthday,@Address,@Email)";
SqlParameter[] parameters =
{
new SqlParameter("@Name", entity.Name),
new SqlParameter("@NickName", entity.NickName),
new SqlParameter("@Password", entity.PassWord),
new SqlParameter("@Sex", entity.Sex),
new SqlParameter("@Birthday", entity.Birthday),
new SqlParameter("@Address", entity.Address),
new SqlParameter("@Email", entity.EMail)
};
return(SQLHELPER.ExecuteNonQuery(sqlstr, parameters));
}
//查询最新的一条 返回
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>
/// 关闭交易
/// </summary>
/// <param name="oid"></param>
/// <returns></returns>
public static object Close(int oid)
{
sqltext = " update [dbo].[YC_Order] set [isClosed]='1' where [OID]='" + oid + "'";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
/// <summary>
/// 生成订单 下单
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public static object GenerateOrder(YC_Order order)
{
sqltext = " INSERT INTO [dbo].[YC_Order]([GID],[UID],[Price],[Count],[SendType],[Address],[BuyTime],[SendTime],[FinishedTime],[GoodSize],[WayNumber])values('" + order.GID + "', '" + order.UID + "', '" + order.Price + "', '" + order.Count + "', '', '" + order.Address + "', convert(char(10),getdate(),120)+' '+Convert(varchar(8),Getdate(),108), null, null, '" + order.GoodSize + "', '" + order.WayNumber + "'); ";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
/// <summary>
/// 查询订单是否已关闭 是返回1
/// </summary>
/// <param name="oid"></param>
/// <returns></returns>
public static object isClosed(int oid)
{
sqltext = " select count(*) from [dbo].[YC_Order] where [isClosed]='1' and [OID]='" + oid + "'";
return(SQLHELPER.ExecuteScalar(sqltext));
}
/// <summary>
/// 查询订单是否已完成 是返回1 否返回0
/// </summary>
/// <param name="oid"></param>
/// <returns></returns>
public static object isFinished(int oid)
{
sqltext = " select count(*) from [dbo].[YC_Order] where [OID]='" + oid + "' and FinishedTime is not NULL";
return(SQLHELPER.ExecuteScalar(sqltext));
}
/// <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));
}
//删除商品类型
public static object delete(int id)
{
sqltext = " delete from [dbo].[YC_GoodType] where [GTID]='" + id + "'";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
//查询所有商品类型
public static DataSet selectAll()
{
sqltext = " select * from [dbo].[YC_GoodType] order by [GTID] desc";
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));
}
/// <summary>
/// 发货（修改状态为 发货，填写发货方式，运单号,订单Id）
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public static object sendOrder(YC_Order order)
{
sqltext = "update [dbo].[YC_Order] set SendType='" + order.Sendtype + "',SendTime=convert(char(10),getdate(),120)+' '+Convert(varchar(8),Getdate(),108),WayNumber='" + order.WayNumber + "' where OID='" + order.OID + "'";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
/// <summary>
/// 确认收收货 订单完成，设置FinishedTime
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public static object OrderFinished(YC_Order order)
{
sqltext = " update [dbo].[YC_Order] set [FinishedTime]=(convert(char(10),getdate(),120)+' '+Convert(varchar(8),Getdate(),108)) where OID='" + order.OID + "'";
return(SQLHELPER.ExecuteNonQuery(sqltext));
}
//增加商品类型
public static object add(YC_GoodType gt)
{
sqltext = " insert into [dbo].[YC_GoodType]([Name],[Description])values('" + gt.Name + "','" + gt.Description + "')";
return(SQLHELPER.ExecuteNonQuery(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 object deleteByRID(int rid)
{
sqltext = " delete from [dbo].[richeng] where [rid]='" + rid + "'";
return(SQLHELPER.ExecuteNonQuery(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));
}
//查询日程详情
public static object selectDetailByRID(int rid)
{
sqltext = "select [detail] from [dbo].[richeng] where [rid]='" + rid + "'";
return(SQLHELPER.ExecuteScalar(sqltext));
}