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