/// <summary>
/// 後台動態發布系統列表
/// </summary>
/// <param name="moduleId">moduleID</param>
/// <param name="orgId">機關編號</param>
/// <param name="pClassId">第一層編號</param>
/// <param name="title">標題關鍵字</param>
/// <param name="sortMethed">排序方式</param>
/// <param name="litimDate">是否顯示開始時間與結束時間</param>
/// <param name="pageSize">一頁幾筆</param>
/// <param name="currentPage">第幾頁</param>
/// <returns>DataTable</returns>
public DataTable GetMasterProductsList(string moduleId, string classID, string subject, bool IsShowEnable, SortMethed sortMethed, int pageSize, int currentPage)
{
string commandString = File.ReadAllText(HttpContext.Current.Server.MapPath("~/App_Code/SQL/GetMasterProductsList.sql"));
if (IsShowEnable) //顯示只顯示上架產品
{
commandString = commandString.Replace("where 1=1", "where 1=1 AND Products.enable=1");
}
if (sortMethed == SortMethed.OrderByCounts) //排序方式
{
commandString = commandString.Replace("ORDER BY Products.initDate desc", "ORDER BY Products.counts desc");
}
//搜尋字串
string searchString = "";
if (!string.IsNullOrEmpty(moduleId))
{
searchString += " and Products.moduleID=@moduleID ";
}
if (!string.IsNullOrEmpty(classID))
{
searchString += " and Products.classID=@classID ";
}
if (!string.IsNullOrEmpty(subject))
{
searchString += " and (Products.[subject] LIKE '%' + @subject + '%') ";;
}
commandString = commandString.Replace("/*--where begin --*/", string.Format("/*--where begin --*/ {0}{1}", Environment.NewLine, searchString));
SqlCommand sqlCommand = new SqlCommand(commandString, _connection);
//處裡參數
sqlCommand.Parameters.Add("@moduleID", SqlDbType.NVarChar);
sqlCommand.Parameters["@moduleID"].Value = moduleId;
sqlCommand.Parameters.Add("@start", SqlDbType.Int);
sqlCommand.Parameters["@start"].Value = ((currentPage - 1) * pageSize) + 1;
sqlCommand.Parameters.Add("@end", SqlDbType.Int);
sqlCommand.Parameters["@end"].Value = currentPage * pageSize;
sqlCommand.Parameters.Add("@classID", SqlDbType.NVarChar, 10);
sqlCommand.Parameters["@classID"].Value = classID;
sqlCommand.Parameters.Add("@subject", SqlDbType.NVarChar);
sqlCommand.Parameters["@subject"].Value = subject;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill((dataTable));
return(dataTable);
}
public int GetPublishListCount(string moduleId, string orgId, string pClassId, string title, string poster, SortMethed sortMethed, bool litimDate)
{
string commandString = File.ReadAllText(HttpContext.Current.Server.MapPath("~/App_Code/SQL/GetPublishListCount.sql"));
if (litimDate) //顯示開始時間與結束時間
{
commandString = commandString.Replace("where 1=1", "where 1=1 AND (GetDate() Between startDate AND DATEADD(DAY,1,endDate))");
}
if (sortMethed == SortMethed.OrderBylistNum) //排序方式
{
commandString = commandString.Replace("ORDER BY ModulePublish.initDate desc", "ORDER BY ModulePublish.listNum asc");
}
//搜尋字串
string searchString = "";
if (!string.IsNullOrEmpty(moduleId))
{
searchString += " and ModulePublish.moduleID=@moduleID ";
}
if (!string.IsNullOrEmpty(orgId))
{
searchString += " and CHARINDEX(@orgID,ModulePublish.OrgID)<>0 ";
}
if (!string.IsNullOrEmpty(pClassId))
{
searchString += " and ModulePublish.classID=@pClassID ";
}
if (!string.IsNullOrEmpty(title))
{
searchString += " and ([title] LIKE '%' + @title + '%') ";;
}
if (!string.IsNullOrEmpty(poster))
{
searchString += " and ModulePublish.poster=@poster ";
}
commandString = commandString.Replace("/*--where begin --*/", string.Format("/*--where begin --*/ {0}{1}", Environment.NewLine, searchString));
SqlCommand sqlCommand = new SqlCommand(commandString, _connection);
//處裡參數
sqlCommand.Parameters.Add("@moduleID", SqlDbType.NVarChar);
sqlCommand.Parameters["@moduleID"].Value = moduleId;
sqlCommand.Parameters.Add("@orgID", SqlDbType.NVarChar);
sqlCommand.Parameters["@orgID"].Value = orgId;
sqlCommand.Parameters.Add("@pclassID", SqlDbType.NVarChar, 10);
sqlCommand.Parameters["@pclassID"].Value = pClassId;
sqlCommand.Parameters.Add("@title", SqlDbType.NVarChar);
sqlCommand.Parameters["@title"].Value = title;
sqlCommand.Parameters.Add("@poster", SqlDbType.NVarChar);
sqlCommand.Parameters["@poster"].Value = poster;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill((dataTable));
return(dataTable.Rows.Count > 0 ? Convert.ToInt32(dataTable.Rows[0][0].ToString()) : 0);
}
