sqlcollections.cs

企业内部的短信交流管理平台。 设计详细

		/// <summary>
		/// 按编号查询公告
		/// </summary>
		public const string SQL_AFFICHES_SELECTBYID="SELECT dbo.Affiches.*, dbo.Users.userName FROM dbo.Affiches inner JOIN dbo.Users on dbo.Users.userId=dbo.Affiches.sendId where aId=@aId";


		/// <summary>
		/// 添加照片
		/// </summary>
		public const string SQL_PHOTOS_INSERT="insert into Photos values(@pPath,@pDesc,@abId,@isPublic)";

		/// <summary>
		/// 删除照片
		/// </summary>
		public const string SQL_PHOTOS_DELETE="delete Photos where pId=@pId";

		/// <summary>
		/// 删除某个用户的照片
		/// </summary>
		public const string SQL_PHOTOS_DELETEBYUSER="delete Photos where abId in (select abId from Album where userId=@userId)";

		/// <summary>
		/// 删除某个相册下的所有照片
		/// </summary>
		public const string SQL_PHOTOS_DELETEBYALBUM="delete Photos where abId=@abId";

		/// <summary>
		/// 修改照片信息
		/// </summary>
		public const string SQL_PHOTOS_UPDATE="update Photos set pPath=@pPath,pDesc=@pDesc,abId=@abId,isPublic=@isPublic where pId=@pId";

		/// <summary>
		/// 改变某张照片的公开状态
		/// </summary>
		public const string SQL_PHOTOS_UPDATEPUBLIC="update Photos set isPublic=@isPublic where pId=@pId";

		/// <summary>
		/// 条件分页查照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTWHERE="s_photos_SelectWhere";

		/// <summary>
		/// 按编号查询照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTBYID="select * from Photos where pId=@pId";

		/// <summary>
		/// 查询某张照片的上一张照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTBYIDUP="select top 1 * from Photos where pId<@pId and abId=@abId order by pId desc";

		/// <summary>
		/// 查询某张照片的下一张照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTBYIDNEXT="select top 1 * from Photos where pId>@pId and abId=@abId order by pId asc";

		/// <summary>
		/// 查询某个相册下的照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTBYALBUM="select * from Photos where abId=@abId";

		/// <summary>
		/// 按用户查询照片
		/// </summary>
		public const string SQL_PHOTOS_SELECTBYUSER="select * from Photos where abId in(select abId from Album where userId=@userId)";

		/// <summary>
		/// 添加权限
		/// </summary>
		public const string SQL_POPEDOM_INSERT="insert into Popedom values(@pdName,@pdPath)";

		/// <summary>
		/// 删除权限
		/// </summary>
		public const string SQL_POPEDOM_DELETE="delete Popedom where pdId=@pdId";

		/// <summary>
		/// 修改权限
		/// </summary>
		public const string SQL_POPEDOM_UPDATE="update Popedom set pdName=@pdName,pdPath=@pdPath where pId=@pId";

		/// <summary>
		/// 查询权限
		/// </summary>
		public const string SQL_POPEDOM_SELECT="select * from Popedom";

		/// <summary>
		/// 查询页面具有的权限
		/// </summary>
		public const string SQL_POPEDOM_SELECTBYPAGE="select * from Popedom where pdPath like @pdPath";

		/// <summary>
		/// 添加收信箱记录
		/// </summary>
		public const string SQL_RECIVEBOX_INSERT="insert into ReciveBox values(@ReciveuserId,@conId,@senduserId,@isDel,@isRead,@reciveTime)";

		/// <summary>
		/// 删除收信箱记录
		/// </summary>
		public const string SQL_RECIVEBOX_DELETE="delete ReciveBox where ReciveId=@ReciveId";

		/// <summary>
		/// 删除某个用户收件箱的记录
		/// </summary>
		public const string SQL_RECIVEBOX_DELETEBYUSER="delete ReciveBox where ReciveuserId=@ReciveuserId";

		/// <summary>
		/// 根据收信人查询收到信笺
		/// </summary>
		public const string SQL_RECIVEBOX_SELECTBYSENDER="SELECT dbo.ReciveBox.*, dbo.Users.userName, dbo.Contents.Content FROM dbo.Contents INNER JOIN dbo.ReciveBox ON dbo.Contents.conId = dbo.ReciveBox.conId inner JOIN dbo.Users on dbo.Users.userId=dbo.ReciveBox.senduserId where dbo.ReciveBox.ReciveuserId=@ReciveuserId and isDel=0";

		/// <summary>
		/// 查询某个用户的未读消息
		/// </summary>
		public const string SQL_RECIVEBOX_SELECTNOREAD="SELECT dbo.ReciveBox.*, dbo.Users.userName, dbo.Contents.Content FROM dbo.Contents INNER JOIN dbo.ReciveBox ON dbo.Contents.conId = dbo.ReciveBox.conId inner JOIN dbo.Users on dbo.Users.userId=dbo.ReciveBox.senduserId where dbo.ReciveBox.ReciveuserId=@ReciveuserId and dbo.ReciveBox.isRead=0 and isDel=0";

		/// <summary>
		/// 查询回收站的信息
		/// </summary>
		public const string SQL_RECIVEBOX_SELECTUSERALLNOTDEL="SELECT dbo.ReciveBox.*, dbo.Users.userName, dbo.Contents.Content FROM dbo.Contents INNER JOIN dbo.ReciveBox ON dbo.Contents.conId = dbo.ReciveBox.conId inner JOIN dbo.Users on dbo.Users.userId=dbo.ReciveBox.senduserId where dbo.ReciveBox.ReciveuserId=@ReciveuserId and isDel=1";
	
		/// <summary>
		/// 将信息放入回收站
		/// </summary>
		public const string SQL_RECIVEBOX_USERISDEL="update ReciveBox set isDel=1 where ReciveId=@ReciveId";

		/// <summary>
		/// 读取未读信息
		/// </summary>
		public const string SQL_RECIVEBOX_READMESSAGE="update ReciveBox set isRead=1 where ReciveId=@ReciveId";

		/// <summary>
		/// 添加发信箱
		/// </summary>
		public const string SQL_SENDBOX_INSERT="insert into SendBox values(@senderId,@conId,@SendTime,@ReciveUsers)";

		/// <summary>
		/// 删除发信箱记录
		/// </summary>
		public const string SQL_SENDBOX_DELETE="delete SendBox where sendId=@sendId";

		/// <summary>
		/// 删除某个用户的发信记录
		/// </summary>
		public const string SQL_SENDBOX_DELETEBYUSER="delete SendBox where senderId=@senderId";

		/// <summary>
		/// 根据发送人查看发送信笺
		/// </summary>
		public const string SQL_SENDBOX_SELECTBYSENDER="SELECT dbo.SendBox.*, dbo.Users.userName, dbo.Contents.Content FROM dbo.Contents INNER JOIN dbo.SendBox ON dbo.Contents.conId = dbo.SendBox.conId inner JOIN dbo.Users on dbo.Users.userId=dbo.SendBox.senderId where dbo.SendBox.senderId=@senderId";

		/// <summary>
		/// 添加用户权限关系
		/// </summary>
		public const string SQL_USERANDPOPEDOM_INSERT="insert into UserAndPopedom values(@userId,@pdId)";

		/// <summary>
		/// 删除用户权限关系
		/// </summary>
		public const string SQL_USERANDPOPEDOM_DELETE="delete UserAndPopedom where uapId=@uapId";

		/// <summary>
		/// 删除某个用户的权限关系
		/// </summary>
		public const string SQL_USERANDPOPEDOM_DELETEBYUSER="delete UserAndPopedom where userId=@userId";


		/// <summary>
		/// 修改用户权限关系
		/// </summary>
		public const string SQL_USERANDPOPEDOM_UPDATE="update UserAndPopedom set userId=@userId,pdId=@pdId where uapId=@uapId";

		/// <summary>
		/// 查询某个用户的权限
		/// </summary>
		public const string SQL_USERANDPOPEDOM_SELECTMY="SELECT * from  dbo.UserAndPopedom where userId=@userId";

		/// <summary>
		/// 添加用户
		/// </summary>
		public const string SQL_USERS_INSERT="insert into Users values(@userName,@userPwd,@userBir,@userTel,@userSex,@userAge,@userEmail,@userTrueName)";

		/// <summary>
		/// 删除用户
		/// </summary>
		public const string SQL_USERS_DELETE="delete Users where userId=@userId";

		/// <summary>
		/// 修改用户
		/// </summary>
		public const string SQL_USERS_UPDATE="update Users set userName=@userName,userPwd=@userPwd,userBir=@userBir,userTel=@userTel,userSex=@userSex,userAge=@userAge,userEmail=@userEmail,userTrueName=@userTrueName where userId=@userId";

		/// <summary>
		/// 查询用户
		/// </summary>
		public const string SQL_USERS_SELECT="select * from Users";

		/// <summary>
		/// 按编号查询用户
		/// </summary>
		public const string SQL_USERS_SELECTBYID="select * from Users where userId=@userId";

		/// <summary>
		/// 用户登录
		/// </summary>
		public const string SQL_USERS_LOGIN="select * from Users where userName=@userName and userPwd=@userPwd";

		/// <summary>
		/// 查询发件表中和某个用户关联的内容表
		/// </summary>
		public const string SQL_SENDBOX_SELECTCONTENTBYUSER="select conId from SendBox  where senderId=@senderId";

		/// <summary>
		/// 查询收件表中和某个用户关联的内容表
		/// </summary>
		public const string SQL_RECIVEBOX_SELECTCONTENTBYUSER="select conId from ReciveBox  where ReciveuserId=@ReciveuserId";
	}
}