public static DataSet SearchProductTopic(TopicSearchCondition SearchCondition)
{
//DataSet ds = new DataSet();
string sql = @"@select Topic_Master.SysNo as SysNo ,Topic_Master.ReferenceType as ReferenceType,
Topic_Master.ReferenceSysNo as ReferenceSysNo,
Product.ProductName as ProductName, Customer.CustomerName as CustomerName,Customer.CustomerID as CustomerID,
Topic_Master.TotalRemarkCount as TotalRemarkCount,Topic_Master.TotalUsefulRemarkCount as TotalUsefulRemarkCount,Topic_Master.TotalComplainCount as TotalComplainCount,
Topic_Master.CreateDate as CreateDate , Topic_Master.Title as Title , Topic_Master.TopicContent as TopicContent ,
Topic_Master.Status as Status , Sys_User.UserName as UpdateUserName , Topic_Master.LastEditDate as LastEditDate , Product.ProductID as ProductID,
Topic_Master.TopicType as TopicType,Topic_Master.IsTop as IsTop,Topic_Master.IsDigest as IsDigest,Topic_Master.Score as Score,
Topic_Master.CreateCustomerSysNo as CreateCustomerSysNo,Topic_Master.LastEditUserSysNo as LastEditUserSysNo,Topic_Master.LastEditDate as LastEditDate,
(select count(*) FROM Topic_Reply (NOLOCK) WHERE Topic_Reply.TopicSysNo=Topic_Master.SysNo) as ReplyCount,
(select count(*) FROM Topic_Image (NOLOCK) WHERE Topic_Image.TopicSysNo=Topic_Master.SysNo) as ImageCount
from Topic_Master (NOLOCK)
inner join Customer (NOLOCK) on Customer.SysNo = Topic_Master.CreateCustomerSysNo
inner join Product (NOLOCK) on Product.SysNo = Topic_Master.ReferenceSysNo
left join Sys_User (NOLOCK) on Sys_User.SysNo = Topic_Master.LastEditUserSysNo
where 1=1 @sqlPar order by Topic_Master.CreateDate desc";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@DateFrom", SqlDbType.DateTime),
new SqlParameter("@DateTo", SqlDbType.DateTime),
new SqlParameter("@ProductSysNo", SqlDbType.Int),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@PMSysNo", SqlDbType.Int),
new SqlParameter("@ProductStatus", SqlDbType.Int),
new SqlParameter("@IsDigest", SqlDbType.Int),
new SqlParameter("@IsTop", SqlDbType.Int),
new SqlParameter("@TopicType", SqlDbType.Int)
};
paras[0].Value = DateTime.Today;
paras[1].Value = DateTime.Today;
paras[2].Value = 0;
paras[3].Value = 0;
paras[4].Value = 0;
paras[5].Value = 0;
paras[6].Value = 0;
paras[7].Value = 0;
paras[8].Value = 0;
StringBuilder sb = new StringBuilder();
if (SearchCondition.DateFrom != null)
{
sb.Append(" and Topic_Master.CreateDate>= @DateFrom ");
paras[0].Value = SearchCondition.DateFrom;
}
if (SearchCondition.DateTo != null)
{
sb.Append(" and Topic_Master.CreateDate<= @DateTo ");
paras[1].Value = SearchCondition.DateTo.Value.AddDays(1).AddSeconds(-1);
}
if (SearchCondition.ProductSysNo != null)
{
sb.Append(" and Topic_Master.ReferenceSysNo=@ProductSysNo ");
paras[2].Value = SearchCondition.ProductSysNo;
}
if (SearchCondition.Status != null)
{
sb.Append(" and Topic_Master.Status=@Status ");
paras[3].Value = SearchCondition.Status;
}
if (SearchCondition.ProductStatus != null)
{
sb.Append(" and Product.Status = @ProductStatus ");
paras[5].Value = SearchCondition.ProductStatus;
}
if (SearchCondition.IsDigest != null)
{
sb.Append(" and Topic_Master.IsDigest=@IsDigest ");
paras[6].Value = (SearchCondition.IsDigest == true) ? 1 : 0;
}
if (SearchCondition.IsTop != null)
{
sb.Append(" and Topic_Master.IsTop=@IsTop ");
paras[7].Value = (SearchCondition.IsTop == true) ? 1 : 0;
}
if (SearchCondition.TypeOfTopic != null)
{
sb.Append(" and Topic_Master.TopicType=@TopicType ");
paras[8].Value = SearchCondition.TypeOfTopic;
}
if (SearchCondition.CustomerId != string.Empty)
sb.Append(" and Customer.CustomerID='" + SearchCondition.CustomerId + "'");
if (SearchCondition.ScoreSign != string.Empty)
sb.Append(" and Topic_Master.Score" + SearchCondition.ScoreSign);
if (SearchCondition.IsComplain != null)
{
if (SearchCondition.IsComplain == true)
sb.Append(" and Topic_Master.TotalComplainCount>0 ");
if (SearchCondition.IsComplain == false)
sb.Append(" and Topic_Master.TotalComplainCount=0 ");
}
if (SearchCondition.InEmpty == true)
sql = sql.Replace("@select", "select top 50 ");
else
sql = sql.Replace("@select", "select ");
sql = sql.Replace("@sqlPar", sb.ToString());
return SqlHelper.ExecuteDataSet(sql, paras);
}
public static DataSet SearchProductTopics(TopicSearchCondition SearchCondition)
{
return TopicDac.SearchProductTopic(SearchCondition);
}
