public IList<Core.Business.Picture> GetAllPicture()
{
IList<Core.Business.Picture> picturelist = new List<Core.Business.Picture>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
SqlDataReader reader = sql.ExecuteSPReader("USP_Picture_SelectAll");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
long id = reader.GetInt64(0);
picture = Core.Business.Picture.Load(id);
/*
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
*/
picture.MarkOld();
picturelist.Add(picture);
}
reader.Close();
}
return picturelist;
}
public List<Core.Business.Picture> GetAllPicture(Core.Business.Album album, Core.PagingInfo pagingInfo)
{
List<Core.Business.Picture> picturelist = new List<Core.Business.Picture>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@Tables", SqlDbType.VarChar, "Picture");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Id desc");
sql.AddParameter("@PageNumber", SqlDbType.Int, pagingInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pagingInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "[Id],[AlbumId],[DateCreated],[Name],[Remark],[BigPath],[MiddlePath],[SmallPath]");
sql.AddParameter("@Filter", SqlDbType.VarChar, "[AlbumId] = " + album.Id);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
long id = reader.GetInt64(0);
picture = Core.Business.Picture.Load(id);
/*
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
*/
picture.MarkOld();
picturelist.Add(picture);
}
reader.Close();
}
return picturelist;
}
public Core.Business.Picture Select(long id)
{
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@Id", SqlDbType.BigInt, id);
SqlDataReader reader = sql.ExecuteSPReader("USP_Picture_Select_By_Id");
if (reader != null && !reader.IsClosed && reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
reader.Close();
return picture;
}
else
{
if (reader != null && !reader.IsClosed)
reader.Close();
return null;
}
}
public List<CY.UME.Core.Business.Picture> SearchPicture(string AEInstanceId, string AEType, string PEAccountName, string PEActivityId, string PEActPId, string PicName, DateTime minDate, DateTime maxDate, CY.UME.Core.PagingInfo pageInfo)
{
List<Core.Business.Picture> picturelist = new List<Core.Business.Picture>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
#region ����
StringBuilder strFilter = new StringBuilder();
strFilter.Append(" Picture.Id = PictureExtend.Id AND PictureExtend.AccountId = Account.Id AND Picture.AlbumId=Album.Id and Album.Id = AlbumExtend.Id");
if (PEAccountName.Length >= 1)
{
strFilter.Append(" AND Account.Name LIKE '%" + PEAccountName + "%' ");
}
if (PEActivityId.Length >= 1)
{
strFilter.Append(" AND PictureExtend.ActivityId ='" + PEActivityId + "'");
}
if (PEActPId == "1" || PEActPId == "0")
{
strFilter.Append(" AND PictureExtend.ActivityPicId ='" + PEActPId + "'");
}
if (PicName.Length > 0)
{
strFilter.Append(" AND Picture.Name LIKE '%" + PicName + "%' ");
}
if (AEInstanceId.Length >= 1)
{
strFilter.Append(" And AlbumExtend.Type='" + AEType + "'");
}
if (AEInstanceId.Length >= 1)
{
strFilter.Append(" and AlbumExtend.InstanceId='" + AEInstanceId + "'");
}
strFilter.Append("AND Picture.DateCreated BETWEEN '" + minDate + "' AND '" + maxDate + "'");
#endregion
sql.AddParameter("@Tables", SqlDbType.VarChar, "Picture,PictureExtend,Account,Album,AlbumExtend");
sql.AddParameter("@PK", SqlDbType.VarChar, "Picture.Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Picture.Id DESC");
sql.AddParameter("@PageNumber", SqlDbType.Int, pageInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pageInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "Picture.Id,Picture.AlbumId,Picture.DateCreated,Picture.Name,Picture.Remark,Picture.BigPath,Picture.MiddlePath,Picture.SmallPath");
sql.AddParameter("@Filter", SqlDbType.VarChar, strFilter.ToString());
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
long id = reader.GetInt64(0);
picture = Core.Business.Picture.Load(id);
picture.MarkOld();
picturelist.Add(picture);
}
reader.Close();
}
return picturelist;
}
public List<CY.UME.Core.Business.Picture> GetPicturesOrderByCommentNumDesc(CY.UME.Core.Business.Album al, int count)
{
List<Core.Business.Picture> piclist = new List<Core.Business.Picture>();
if (al == null)
{
return piclist;
}
SqlServerUtility sql = new SqlServerUtility(connectionString);
sql.AddParameter("@Count", SqlDbType.Int, count);
sql.AddParameter("@AlbumId", SqlDbType.BigInt, al.Id);
string sqlGetPic = "select top(@Count) Picture.Id,Picture.AlbumId,Picture.DateCreated,Picture.Name,Picture.Remark,Picture.BigPath,Picture.MiddlePath,Picture.SmallPath, (select count(0) from pictureComment where instanceId=Picture.Id) a from Picture where albumId=@AlbumId order by a desc";
SqlDataReader reader = sql.ExecuteSqlReader(sqlGetPic);
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
picture.MarkOld();
piclist.Add(picture);
}
reader.Close();
}
return piclist;
}
// ����������ơ���Ƭ���ơ���ʱ��λ�ȡ�û�ͼƬ ��̨����
public List<Core.Business.Picture> GetPicturesByWhere(string AccountName, CY.UME.Core.PagingInfo pagingInfo, string AlbumName, string PictureName, string BeginTime, string EndTime)
{
List<Core.Business.Picture> picturelist = new List<Core.Business.Picture>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
String strFilter = SetUserPictureFilter(AccountName, AlbumName, PictureName, BeginTime, EndTime);
sql.AddParameter("@Tables", SqlDbType.VarChar, "Picture,Album,Account");
sql.AddParameter("@PK", SqlDbType.VarChar, "Picture.Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Picture.Id DESC");
sql.AddParameter("@PageNumber", SqlDbType.Int, pagingInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pagingInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "Picture.Id,Picture.AlbumId,Picture.DateCreated,Picture.Name,Picture.Remark,Picture.BigPath,Picture.MiddlePath,Picture.SmallPath");
sql.AddParameter("@Filter", SqlDbType.VarChar, strFilter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
picture.MarkOld();
picturelist.Add(picture);
}
reader.Close();
}
return picturelist;
}
// Ⱥ��ͼƬ ��̨����
public IList<Core.Business.Picture> GetGroupPicture(string GroupName, string ActivityId, string AccountName, string PictureName, string BeginTime, string EndTime, CY.UME.Core.PagingInfo pagingInfo)
{
List<Core.Business.Picture> picturelist = new List<Core.Business.Picture>();
SqlServerUtility sql = new SqlServerUtility(connectionString);
String strFilter = SetGroupPictureFilter(GroupName, ActivityId, AccountName, PictureName, BeginTime, EndTime);
/*
sql.AddParameter("@Tables", SqlDbType.VarChar, "[PictureExtend],[Picture],[Account],[Group]");
sql.AddParameter("@PK", SqlDbType.VarChar, "Picture.Id");
sql.AddParameter("@Sort", SqlDbType.VarChar, "Picture.Id DESC");
sql.AddParameter("@PageNumber", SqlDbType.Int, pagingInfo.CurrentPage);
sql.AddParameter("@PageSize", SqlDbType.Int, pagingInfo.PageSize);
sql.AddParameter("@Fields", SqlDbType.VarChar, "Picture.Id,Picture.AlbumId,Picture.DateCreated,Picture.Name,Picture.Remark,Picture.BigPath,Picture.MiddlePath,Picture.SmallPath");
sql.AddParameter("@Filter", SqlDbType.VarChar, strFilter);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");*/
String Sort = "[Picture].[DateCreated]";
String GroupBy = " GROUP BY ";
String Column = " Picture.Id,Picture.AlbumId,Picture.DateCreated,Picture.Name,Picture.Remark,Picture.BigPath,Picture.MiddlePath,Picture.SmallPath ";
int pageSize = int.MaxValue;
int pageNumber = 1;
if (pagingInfo != null)
{
pageSize = pagingInfo.PageSize;
pageNumber = pagingInfo.CurrentPage;
}
sql.AddParameter("@PageNum", SqlDbType.Int, pageSize);
sql.AddParameter("@Num", SqlDbType.Int, pageSize * (pageNumber - 1));
string SqlGetUC = "SELECT Top (@PageNum) " + Column;
SqlGetUC += "FROM [PictureExtend],[Picture],[Account],[Group] ";
SqlGetUC += "WHERE [Picture].[Id] NOT IN (";
SqlGetUC += "SELECT Top (@Num) [Picture].[Id] ";
SqlGetUC += "FROM [PictureExtend],[Picture],[Account],[Group] ";
SqlGetUC += "WHERE ";
SqlGetUC += strFilter + " " + GroupBy + Column + " ORDER BY " + Sort + ")";
SqlGetUC += " and " + strFilter + " " + GroupBy + Column + "ORDER BY " + Sort;
SqlDataReader reader = sql.ExecuteSqlReader(SqlGetUC);
if (reader != null)
{
while (reader.Read())
{
Core.Business.Picture picture = new Core.Business.Picture();
if (!reader.IsDBNull(0)) picture.Id = reader.GetInt64(0);
if (!reader.IsDBNull(1)) picture.AlbumId = reader.GetInt64(1);
if (!reader.IsDBNull(2)) picture.DateCreated = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) picture.Name = reader.GetString(3);
if (!reader.IsDBNull(4)) picture.Remark = reader.GetString(4);
if (!reader.IsDBNull(5)) picture.BigPath = reader.GetString(5);
if (!reader.IsDBNull(6)) picture.MiddlePath = reader.GetString(6);
if (!reader.IsDBNull(7)) picture.SmallPath = reader.GetString(7);
picture.MarkOld();
picturelist.Add(picture);
}
reader.Close();
}
return picturelist;
}