public DataTable ListBulkUpdate(BulkUpdateSearch updateSearch)
{
var skipRows = (updateSearch.CurrentPage - 1) * updateSearch.PageSize;
var cmd = GetWhereStatement(updateSearch);
var sql = @"WITH CTE AS (SELECT COUNT(distinct bue.SysId) AS totalcount FROM [ScheduleEvent].[dbo].BulkUpdateEvent BUE
LEFT JOIN [ScheduleEvent].[dbo].ProductDateUpdate PDU ON BUE.SYSID = PDU.EventId AND PDU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductSortUpdate PSOU ON BUE.SYSID = PSOU.EventId AND PSOU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductPreOrderUpdate PPU ON BUE.SYSID = PPU.EventId AND PPU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductStatusUpdate PSU ON BUE.SYSID = PSU.EventId AND PSU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].PRODUCTEVENTUPDATE PEU ON BUE.SYSID = PEU.EventId AND PEU.STATUS =1
WHERE BUE.STATUS>0 {0} ) ,
DETAILS AS(SELECT distinct BUE.*,
CASE WHEN BUE.Status=1 THEN N'未執行'
WHEN BUE.Status=2 THEN N'已執行'
WHEN BUE.Status=3 THEN N'已排除' END AS 'StatusText'
FROM [ScheduleEvent].[dbo].BulkUpdateEvent BUE
LEFT JOIN [ScheduleEvent].[dbo].PRODUCTEVENTUPDATE PEU ON BUE.SYSID = PEU.EventId AND PEU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductDateUpdate PDU ON BUE.SYSID = PDU.EventId AND PDU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductSortUpdate PSOU ON BUE.SYSID = PSOU.EventId AND PSOU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductPreOrderUpdate PPU ON BUE.SYSID = PPU.EventId AND PPU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductStatusUpdate PSU ON BUE.SYSID = PSU.EventId AND PSU.STATUS =1
WHERE BUE.STATUS>0 {0} )
SELECT * FROM CTE, DETAILS
ORDER BY CDate DESC
OFFSET " + skipRows + " ROWS FETCH NEXT " + updateSearch.PageSize + " ROWS ONLY ";
cmd.CommandText = string.Format(sql, cmd.CommandText);
var dt = SqlDbmanager.queryBySql(cmd);
return(dt);
}
public DataTable GetExportData(BulkUpdateSearch updateSearch)
{
var sql = @"SELECT
CASE WHEN EVENTTYPE = 1 THEN PDU.ProductId
WHEN EVENTTYPE =2 THEN PEU.ProductId
WHEN EVENTTYPE =3 THEN PSOU.ProductId
WHEN EVENTTYPE =4 THEN PSU.ProductId
WHEN EVENTTYPE =5 THEN PPU.ProductId END AS [商品ID],
EventName AS [Event名稱],
ExecuteStartDate [執行日期],
ExecuteEndDate [排除日期],
PDU.StartDate [販售開始時間],
PDU.EndDate [販售結束時間],
PEU.EventNameCN [中文補充標題說明],
PEU.EventNameen [英文文補充標題說明],
PSOU.ProductSort [品牌館排序],
PSU.ProductStatus [商品狀態],
CASE WHEN PPU.PreOrderStatus=1 THEN '是' WHEN PPU.PreOrderStatus=0 THEN '否' END AS [是否開啟預購],
PPU.PreOrderQuantity [可預購最高數量]
FROM [ScheduleEvent].[dbo].BULKUPDATEEVENT BUE
LEFT JOIN [ScheduleEvent].[dbo].PRODUCTEVENTUPDATE PEU ON BUE.SYSID = PEU.EventId AND PEU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductDateUpdate PDU ON BUE.SYSID = PDU.EventId AND PDU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductSortUpdate PSOU ON BUE.SYSID = PSOU.EventId AND PSOU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductPreOrderUpdate PPU ON BUE.SYSID = PPU.EventId AND PPU.STATUS =1
LEFT JOIN [ScheduleEvent].[dbo].ProductStatusUpdate PSU ON BUE.SYSID = PSU.EventId AND PSU.STATUS =1
WHERE BUE.STATUS>0";
var cmdsearch = GetWhereStatement(updateSearch);
cmdsearch.CommandText = sql + cmdsearch.CommandText;
var dt = SqlDbmanager.queryBySql(cmdsearch);
return(dt);
}
private SqlCommand GetWhereStatement(BulkUpdateSearch updateSearch)
{
var cmd = new SqlCommand();
if (updateSearch.ExecuteStartDate.HasValue && updateSearch.ExecuteStartDate > DateTime.MinValue)
{
cmd.CommandText += " AND BUE.ExecuteStartDate>=@SDate";
cmd.Parameters.Add(SafeSQL.CreateInputParam("@SDate", SqlDbType.DateTime, updateSearch.ExecuteStartDate));
}
if (updateSearch.ExecuteEndDate.HasValue && updateSearch.ExecuteEndDate > DateTime.MinValue)
{
cmd.CommandText += " AND BUE.ExecuteStartDate<=@EDate";
cmd.Parameters.Add(SafeSQL.CreateInputParam("@EDate", SqlDbType.DateTime, updateSearch.ExecuteEndDate));
}
if (updateSearch.ProductID > 0)
{
cmd.CommandText += " AND ( PEU.ProductID=@ProductID OR " +
"PSU.ProductID=@ProductID OR " +
"PSOU.ProductID=@ProductID OR " +
"PDU.ProductID=@ProductID OR " +
"PPU.ProductID=@ProductID )";
cmd.Parameters.Add(SafeSQL.CreateInputParam("@ProductID", SqlDbType.Int, updateSearch.ProductID));
}
if (!string.IsNullOrEmpty(updateSearch.EventName))
{
cmd.CommandText += " AND BUE.EventName LIKE '%'+ @EventName+ '%' ";
cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventName", SqlDbType.NVarChar, updateSearch.EventName));
}
return(cmd);
}
private void BindData(int page, int pageSize)
{
BulkUpdateSearch search = MapSearch(page, pageSize);
var dt = _productBulkUpdateService.ListBulkUpdate(search);
int acount = 0;
if (dt.Rows != null && dt.Rows.Count > 0)
{
acount = Convert.ToInt32(dt.Rows[0]["totalcount"].ToString());
}
rptList.DataSource = dt;
rptList.DataBind();
var searchquery = GetSearchQuery(search);
GetPaging(acount, _pageSize, page, searchquery);
list_panel.Update();
}
private string GetSearchQuery(BulkUpdateSearch updateSearch)
{
var searchqeury = "";
if (updateSearch.ExecuteStartDate.HasValue)
{
searchqeury += "&st=" + updateSearch.ExecuteStartDate.Value.ToString("yyyy-MM-dd");
}
if (updateSearch.ExecuteEndDate.HasValue)
{
searchqeury += "&et=" + updateSearch.ExecuteEndDate.Value.ToString("yyyy-MM-dd");
}
if (!string.IsNullOrEmpty(updateSearch.EventName))
{
searchqeury += "&en=" + updateSearch.EventName;
}
if (updateSearch.ProductID.HasValue)
{
searchqeury += "&pid=" + updateSearch.ProductID.Value;
}
return(searchqeury);
}
private BulkUpdateSearch MapSearch(int page, int pageSize)
{
var search = new BulkUpdateSearch();
if (!string.IsNullOrEmpty(txtSearchSDate.Text))
{
search.ExecuteStartDate = DateTime.Parse(txtSearchSDate.Text);
}
if (!string.IsNullOrEmpty(txtSearchEDate.Text))
{
search.ExecuteEndDate = DateTime.Parse(txtSearchEDate.Text);
}
if (!string.IsNullOrEmpty(txtSearchID.Text))
{
search.ProductID = int.Parse(txtSearchID.Text);
}
search.EventName = txtSearchName.Text;
search.PageSize = pageSize;
search.CurrentPage = page;
return(search);
}