private static BulkUpdateEvent GetBulkUpdateEvent(Guid id)
{
var sql = "SELECT SysId, EventName,ExecuteStartDate, ExecuteEndDate,EventType FROM [ScheduleEvent].[dbo].BulkUpdateEvent WHERE SysId=@id ";
var cmd = new SqlCommand {
CommandText = sql
};
cmd.Parameters.Add(SafeSQL.CreateInputParam("@id", SqlDbType.UniqueIdentifier, id));
var dt = SqlDbmanager.queryBySql(cmd);
var updateEvent = new BulkUpdateEvent();
foreach (DataRow dr in dt.Rows)
{
updateEvent.SysId = Guid.Parse(dr["SysId"].ToString());
updateEvent.EventName = dr["EventName"].ToString();
updateEvent.ExecuteStartDate = DateTime.Parse(dr["ExecuteStartDate"].ToString());
updateEvent.EventType = int.Parse(dr["EventType"].ToString());
if (!string.IsNullOrEmpty(dr["ExecuteEndDate"].ToString()))
{
updateEvent.ExecuteEndDate = DateTime.Parse(dr["ExecuteEndDate"].ToString());
}
}
return(updateEvent);
}
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 List <int> GetProducts(List <int> products)
{
var sql = @"SELECT WP01 FROM WP WHERE WP01 IN (";
foreach (var id in products)
{
sql += id + ",";
}
sql = sql.TrimEnd(',') + ") ";
var dt = SqlDbmanager.queryBySql(sql);
if (dt == null || dt.Rows == null)
{
return(new List <int>());
}
var existingproductIds = new List <int>();
foreach (DataRow dr in dt.Rows)
{
existingproductIds.Add(int.Parse(dr["WP01"].ToString()));
}
return(existingproductIds);
}
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 List <MolPayMember> GetAPIRequests(DateTime date)
{
string sql = @"SELECT MI02 AS MemberId, MI07 As MemberName, MI77 AS MerchantId, MI78 As VerifyKey, MI79 As SecretKey
FROM GoodMaji.dbo.MemberInfo ON MI02=MA05
WHERE MI80=1 ";
var cmd = new SqlCommand {
CommandText = sql
};
var dt = SqlDbmanager.queryBySql(cmd);
var transactionApiRequests = new List <MolPayMember>();
if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
{
return(transactionApiRequests);
}
foreach (DataRow dr in dt.Rows)
{
var transactionRequest = new MolPayMember();
transactionRequest.MerchantId = dr["MerchantId"].ToString();
transactionRequest.MemberId = dr["MemberId"].ToString();
transactionRequest.MemberName = dr["MemberName"].ToString();
transactionRequest.SecretKey = dr["SecretKey"].ToString();
transactionRequest.VerifyKey = dr["VerifyKey"].ToString();
transactionApiRequests.Add(transactionRequest);
}
return(transactionApiRequests);
}
private TransactionAPIResponse SetMemberInfo(TransactionAPIResponse transactionAPIResponse)
{
string strSql = @"SELECT MI02 AS MemberId, MI07 As MemberName
FROM GoodMaji.dbo.CollectionOfMoney
INNER JOIN GoodMaji.dbo.MemberAccount ON MA02=COM04
INNER JOIN GoodMaji.dbo.MemberInfo ON MI02=MA05
WHERE COM02=@OrderID ";
var cmd = new SqlCommand {
CommandText = strSql
};
cmd.Parameters.Add(SafeSQL.CreateInputParam("@OrderID", System.Data.SqlDbType.VarChar, transactionAPIResponse.OrderID));
var dt = SqlDbmanager.queryBySql(cmd);
if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
{
return(transactionAPIResponse);
}
var dr = dt.Rows[0];
transactionAPIResponse.MemberId = dr["MemberId"].ToString();
transactionAPIResponse.MemberName = dr["MemberName"].ToString();
return(transactionAPIResponse);
}
private List <ShipmentNumber> GetShipmentNumber(int topcount)
{
var cmd = new SqlCommand {
CommandText = "SELECT TOP " + topcount + " * FROM PrescoShipment WHERE Status =1 ORDER BY CDate "
};
var dt = SqlDbmanager.queryBySql(cmd);
var result = new List <ShipmentNumber>();
foreach (DataRow dr in dt.Rows)
{
var number = new ShipmentNumber();
number.Id = int.Parse(dr["id"].ToString());
number.Number = dr["Number"].ToString();
result.Add(number);
}
return(result);
}
public List <UpdateDetails> GetBulkUpdate(Guid eventId)
{
var sql = @"SELECT* FROM [ScheduleEvent].[dbo].ProductStatusUpdate WHERE EventId = @EventId AND Status=1 ";
var cmd = new SqlCommand {
CommandText = sql
};
cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));
var dt = SqlDbmanager.queryBySql(cmd);
var list = new List <UpdateDetails>();
foreach (DataRow dr in dt.Rows)
{
var pStatus = new ProductStatusUpdate();
pStatus.ProductId = int.Parse(dr["ProductId"].ToString());
pStatus.ProductStatus = int.Parse(dr["ProductStatus"].ToString());
list.Add(pStatus);
}
return(list);
}