private AnnounceQueryCondition getCondition()
{
var condition = new AnnounceQueryCondition()
{
Title = this.txtTitle.Text,
PublishAccount = this.txtPublishPerson.Text
};
if (!string.IsNullOrWhiteSpace(this.txtStartDate.Text))
{
if (!string.IsNullOrWhiteSpace(this.txtEndDate.Text))
{
condition.PublishTime = new Core.Range <DateTime?>(DateTime.Parse(this.txtStartDate.Text), DateTime.Parse(this.txtEndDate.Text).AddDays(1).AddTicks(-1));
}
else
{
condition.PublishTime = new Core.Range <DateTime?>(DateTime.Parse(this.txtStartDate.Text), null);
}
}
else
{
if (!string.IsNullOrWhiteSpace(this.txtEndDate.Text))
{
condition.PublishTime = new Core.Range <DateTime?>(null, DateTime.Parse(this.txtEndDate.Text));
}
}
if (!string.IsNullOrWhiteSpace(this.dropAuditStatus.SelectedValue))
{
condition.AduiteStatus = (AduiteStatus)int.Parse(this.dropAuditStatus.SelectedValue);
}
return(condition);
}
/// <summary>
/// 公告列表查看
/// </summary>
/// <param name="announce"></param>
/// <param name="pagination"></param>
/// <returns></returns>
public object TerraceAnnounceQuery(AnnounceQueryCondition announce, Pagination pagination)
{
if (pagination == null)
{
pagination = Pagination.Default;
}
return(AnnounceService.Query(new Guid(), announce));
}
public IEnumerable <DataTransferObject.Announce.AnnounceListView> QueryAnnounceList(Guid company, AnnounceQueryCondition condition, Pagination pagination)
{
var result = new List <DataTransferObject.Announce.AnnounceListView>();
var fields = "[Id],[Title],[PublishTime],[PublishAccount],[AnnounceType],[AduiteStatus],[AnnounceScope]";
var catelog = "[dbo].[T_Announce]";
var orderbyFiled = "[PublishTime] DESC";
var where = new StringBuilder();
using (var dbOperator = new DbOperator(Provider, ConnectionString))
{
where.AppendFormat("[Company] = '{0}' AND ", company);
if (!string.IsNullOrWhiteSpace(condition.Title))
{
where.AppendFormat("[Title]= '{0}' AND ", condition.Title.Trim());
}
if (!string.IsNullOrWhiteSpace(condition.PublishAccount))
{
where.AppendFormat("[PublishAccount]= '{0}' AND ", condition.PublishAccount.Trim());
}
if (condition.AduiteStatus.HasValue)
{
where.AppendFormat("[AduiteStatus]= {0} AND ", (int)condition.AduiteStatus);
}
if (condition.PublishTime.Lower.HasValue)
{
where.AppendFormat("[PublishTime] > '{0}' AND ", condition.PublishTime.Lower.Value);
}
if (condition.PublishTime.Upper.HasValue)
{
where.AppendFormat("[PublishTime] < '{0}' AND ", condition.PublishTime.Upper.Value.AddDays(1).AddMilliseconds(-3));
}
if (where.Length > 0)
{
where.Remove(where.Length - 5, 5);
}
dbOperator.AddParameter("@iField", fields);
dbOperator.AddParameter("@iCatelog", catelog);
dbOperator.AddParameter("@iCondition", where.ToString());
dbOperator.AddParameter("@iOrderBy", orderbyFiled);
dbOperator.AddParameter("@iPagesize", pagination.PageSize);
dbOperator.AddParameter("@iPageIndex", pagination.PageIndex);
dbOperator.AddParameter("@iGetCount", pagination.GetRowCount);
var totalCount = dbOperator.AddParameter("@oTotalCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (var reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure))
{
while (reader.Read())
{
result.Add(new AnnounceListView()
{
Id = reader.GetGuid(0),
Title = reader.GetString(1),
PublishTime = reader.GetDateTime(2),
PublishAccount = reader.GetString(3),
AnnounceLevel = (AnnounceLevel)reader.GetInt32(4),
AduiteStatus = (AduiteStatus)reader.GetInt32(5),
AnnounceScope = (AnnounceScope)reader.GetByte(6)
});
}
}
if (pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
/// <summary>
/// 公司查询公告信息列表
/// </summary>
/// <param name="company">公司Id</param>
/// <param name="condition">查询条件</param>
/// <returns></returns>
public static IEnumerable <AnnounceListView> Query(Guid company, AnnounceQueryCondition condition)
{
var repository = Factory.CreateAnnounceReposity();
return(repository.QueryAnnounceList(company, condition));
}
public IEnumerable <DataTransferObject.Announce.AnnounceListView> QueryAnnounceList(Guid company, AnnounceQueryCondition condition)
{
var result = new List <DataTransferObject.Announce.AnnounceListView>();
string sql = "SELECT [Id],[Title],[PublishTime],[PublishAccount],[AnnounceType],[AduiteStatus],[AnnounceScope] FROM [dbo].[T_Announce] WHERE [Company]=@Company";
using (var dbOperator = new DbOperator(Provider, ConnectionString)) {
StringBuilder strWhere = new StringBuilder();
dbOperator.AddParameter("Company", company);
if (!string.IsNullOrWhiteSpace(condition.Title))
{
strWhere.Append(" AND [Title]=@Title");
dbOperator.AddParameter("Title", condition.Title);
}
if (!string.IsNullOrWhiteSpace(condition.PublishAccount))
{
strWhere.Append(" AND [PublishAccount]=@PublishAccount");
dbOperator.AddParameter("PublishAccount", condition.PublishAccount);
}
if (condition.AduiteStatus.HasValue)
{
strWhere.Append(" AND [AduiteStatus]=@AduiteStatus");
dbOperator.AddParameter("AduiteStatus", (int)condition.AduiteStatus);
}
if (condition.PublishTime.Lower.HasValue)
{
strWhere.Append(" AND [PublishTime]>@PublishLowerTime");
dbOperator.AddParameter("PublishLowerTime", condition.PublishTime.Lower.Value.Date);
}
if (condition.PublishTime.Upper.HasValue)
{
strWhere.Append(" AND [PublishTime]<@PublishUpperTime");
dbOperator.AddParameter("PublishUpperTime", condition.PublishTime.Upper.Value.Date);
}
if (strWhere.Length > 0)
{
sql += strWhere;
}
using (var reader = dbOperator.ExecuteReader(sql)) {
while (reader.Read())
{
DataTransferObject.Announce.AnnounceListView view = new DataTransferObject.Announce.AnnounceListView();
view.Id = reader.GetGuid(0);
view.Title = reader.GetString(1);
view.PublishTime = reader.GetDateTime(2);
view.PublishAccount = reader.GetString(3);
view.AnnounceLevel = (AnnounceLevel)reader.GetInt32(4);
view.AduiteStatus = (AduiteStatus)reader.GetInt32(5);
view.AnnounceScope = (AnnounceScope)reader.GetByte(6);
result.Add(view);
}
}
}
return(result);
}
