public int DeleteBulkUpdate(Guid eventId)
{
var exludeUpdateCol = new List <string> {
"SysId", "ExecuteStartDate", "EventType"
};
var cmdList = new List <SqlCommand>();
var bulkupdateEvent = new BulkUpdateEvent {
SysId = eventId, Status = (int)BulkUpdateStatus.已刪除
};
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", bulkupdateEvent, exludeUpdateCol, new List <string> {
"SysId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductDateUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductEventUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductPreOrderUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductSortUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductStatusUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList));
}
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 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 bool AddLog(APIHelper helper)
{
PrescoAPILog prescoAPILog = MapAPILog(helper);
var cmd = SqlExtension.GetInsertSqlCmd("PrescoAPILog", prescoAPILog);
return(SqlDbmanager.ExecuteNonQry(cmd));
}
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);
}
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);
}
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 bool UpdateProduct(Guid eventId)
{
var sql = @" UPDATE WP
SET WP07 = ProductStatus
FROM WP
INNER JOIN [ScheduleEvent].[dbo].ProductStatusUpdate PSU ON PSU.ProductId = WP.WP01
INNER JOIN [ScheduleEvent].[dbo].BulkUpdateEvent BUE ON BUE.SysId = PSU.EventId
WHERE BUE.SysId = @EventId
AND BUE.Status = 1
AND PSU.Status = 1 ";
var cmd = new SqlCommand {
CommandText = sql
};
var cmdUpdateStatus = new SqlCommand {
CommandText = "UPDATE [ScheduleEvent].[dbo].BulkUpdateEvent SET Status = 2 WHERE SysId = @EventId"
};
cmd.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));
cmdUpdateStatus.Parameters.Add(SafeSQL.CreateInputParam("@EventId", SqlDbType.UniqueIdentifier, eventId));
var cmdList = new List <SqlCommand>();
cmdList.Add(cmd);
cmdList.Add(cmdUpdateStatus);
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList) == 1);
}
public int AddBulkUpdate(BulkUpdateData productBulkUpdateData)
{
_bulkUpdateDetailsService = _updateDetailsFactory.CreateService(productBulkUpdateData.UpdateEvent.EventType);
var cmdList = new List <SqlCommand>();
cmdList.Add(SqlExtension.getInsertSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", productBulkUpdateData.UpdateEvent));
cmdList.AddRange(_bulkUpdateDetailsService.AddBulkUpdate(productBulkUpdateData.UpdateDetails));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList));
}
private int AddShipNumber(List <ShipmentNumber> shipmentNumbers)
{
var cmdList = new List <SqlCommand>();
foreach (var number in shipmentNumbers)
{
cmdList.Add(SqlExtension.GetInsertSqlCmd("PrescoShipment", number));
}
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
return(rval);
}
private int UpdateShipNumber(List <ShipmentNumber> shipmentNumbers)
{
var cmdlist = new List <SqlCommand>();
foreach (var shipnumber in shipmentNumbers)
{
shipnumber.Status = (int)ShipNumberStatus.Used;
shipnumber.UDate = DateTime.Now;
cmdlist.Add(SqlExtension.GetUpdateSqlCmd("PrescoShipment", shipnumber, new List <string> {
"Id"
}, "Id=@Id"));
}
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdlist);
return(rval);
}
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 int UpdateShipmentStatus(List <PrescoShipment> shipStatusRequests)
{
var updatelist = GetUpdateShipments(shipStatusRequests);
var updateCmd = new List <SqlCommand>();
foreach (var shiphistory in updatelist)
{
var shipment = new Shipment {
ST12 = shiphistory.SSH05, ST02 = shiphistory.SSH03
};
updateCmd.Add(SqlExtension.GetUpdateSqlCmd("Shipment", shipment, new List <string> {
"ST02", "ST03", "ST13"
}, "ST02=@ST02", null));
updateCmd.Add(InsertShipmentHistory(shiphistory));
}
return(SqlDbmanager.ExecuteNonQryMutiSqlCmd(updateCmd));
}
public bool UpdateBulkUpdate(BulkUpdateData productBulkUpdate)
{
_bulkUpdateDetailsService = _updateDetailsFactory.CreateService(productBulkUpdate.UpdateEvent.EventType);
var excludeUpdateColumns = new List <string> {
"ProductID", "SysId", "ExecuteDate"
};
var whereColumns = new List <string> {
"SysId=@SysId"
};
var cmd = SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", productBulkUpdate.UpdateEvent, excludeUpdateColumns, whereColumns);
var cmdList = new List <SqlCommand>();
cmdList.Add(cmd);
cmdList.AddRange(_bulkUpdateDetailsService.EditBulkUpdate(productBulkUpdate.UpdateDetails));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList) == 1);
}
private int AddOrderLog(List <OrderRequest> request, APIHelper aPIHelper)
{
var cmdList = new List <SqlCommand>();
var prescoAPILog = MapAPILog(aPIHelper);
foreach (var item in request)
{
var orderlog = new PrescoOrderLog();
orderlog.SysId = Guid.NewGuid();
orderlog.PrescoAPILogID = prescoAPILog.SysId;
orderlog.PrescoShipID = item.ShipNo;
orderlog.GMShipID = item.OrderNo;
cmdList.Add(SqlExtension.GetInsertSqlCmd("Prescoorderlog", orderlog));
}
cmdList.Add(SqlExtension.GetInsertSqlCmd("PrescoAPILog", prescoAPILog));
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
return(rval);
}
public void SendEmailAysnc(string recipient, string content, string subject)
{
try
{
string fromEmail = "*****@*****.**";
MailMessage message = new MailMessage(fromEmail, recipient, subject, content);
SmtpClient client = new SmtpClient("smtp.gmail.com", 587);
client.EnableSsl = true;
client.DeliveryMethod = SmtpDeliveryMethod.Network;
client.UseDefaultCredentials = false;
client.Credentials = new System.Net.NetworkCredential(fromEmail, "h@Wo0o!@#$%");
client.Send(message);
}
catch (Exception ex)
{
SqlDbmanager.AddErrorLog("", ex.Message);
}
}
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);
}
private void AddMolPayTransaction(List <TransactionAPIResponse> transactionAPIResponses, MolPayMember member)
{
var cmdList = new List <SqlCommand>();
foreach (var response in transactionAPIResponses)
{
response.SysId = Guid.NewGuid();
if (response.SettlementDate.Contains("0000-00-00"))
{
response.SettlementDate = null;
}
if (response.PaidDate.Contains("0000-00-00"))
{
response.PaidDate = null;
}
response.MemberName = member.MemberName;
response.MemberId = member.MemberId;
cmdList.Add(SqlExtension.GetInsertSqlCmd("MolPayTransaction", response));
}
SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
}
