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); }