private static string GetFilterItem(User user, string sHeadUniqueKey850, Dictionary <string, string> sVals860) { List <string> cond = new List <string>(); if (sVals860.ContainsKey(_Column.UPCNum)) { cond.Add(string.Format("{0}='{1}'", _Column.UPCNum, sVals860[_Column.UPCNum])); } if (sVals860.ContainsKey(_Column.BuyerNum)) { cond.Add(string.Format("{0}='{1}'", _Column.BuyerNum, sVals860[_Column.BuyerNum])); } if (sVals860.ContainsKey(_Column.VendorNum)) { cond.Add(string.Format("{0}='{1}'", _Column.VendorNum, sVals860[_Column.VendorNum])); } if (cond.Count == 0) { ProgramLog.LogError(user, "ChangePOTracker", "GetFilterItem", string.Format("Item under POC key {0} could not be found in original PO by upc/buyer/vendor.", sHeadUniqueKey850)); return(""); } else { return(string.Format("WHERE {0}='{1}' AND ({2})", _Column.UniqueKey, sHeadUniqueKey850, string.Join(" OR ", cond))); } }
public static List <XrefRecord> GetXrefList(User user, FilterInfo filter) { List <XrefRecord> ret = new List <XrefRecord>(); try { var connection = ConnectionsMgr.GetOCConnection(user, Home); { var recCount = _GetRecordCount(user, connection, filter); if (recCount > 0) { filter.ResultCount = recCount; filter.MaxPage = (int)(Math.Ceiling(recCount / (double)resultPageSize)); } using (var queryCurrent = connection.Select("*", CatXref, $"WHERE {filter.ToFetchQueryString(user, searchCols, resultPageSize)}")) { while (queryCurrent.Read()) { XrefRecord xr = new XrefRecord(queryCurrent); ret.Add(xr); } } } connection.Close(); } catch (Exception e) { ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(GetXrefList), e.Message); } return(ret); }
public static ResponseType VerifyFile(User user, string xlFileName) { Application app = null; Workbook wb = null; Worksheet xlWS = null; try { app = new Application(); app.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable; wb = app.Workbooks.Open(Filename: xlFileName, ReadOnly: true); if (wb.Worksheets.Count == 0) { wb.Close(); return(ResponseType.ErrorAPOUnknown); } xlWS = wb.Worksheets[1]; ResponseType result = CheckSheet(user, xlWS); return(result); } catch (Exception e) { ProgramLog.LogError(user, "POAcknowledgeManager", "VerifyFile", e.Message); return(ResponseType.ErrorAPOUnknown); } finally { if (app != null && wb != null) { wb.Close(); } } }
private static ResponseType PriceChange(DBConnect connection, User user, string sUnitPrice, string sFilterLineItem) { try { var sUpdateVals = new Dictionary <string, string>() { { _Column.UnitPrice, sUnitPrice.SQLEscape() } }.ToNameValueCollection(); var result = connection.Update(_Table.Detail850All, sUpdateVals, sFilterLineItem); if (result.AffectedRows > 0) { return(ResponseType.SuccessCPO); } else { return(ResponseType.ErrorCPOCouldNotApplyItemChange); } } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "PriceChange", e.Message); return(ResponseType.ErrorCPOUnknown); } }
private const int OCSessionTimeOutSeconds = 60; //10 TIMED OUT OFTEN (SENDING TO 404 PAGE), TRYING 30 SECONDS. CHANGED TO 60 SECS BECAUSE OF TEMPORARY TIME DIFF BETWEEN THE TWO SERVERS /// <summary> /// Authenticates the given user. /// </summary> /// <param name="usUserName">The username to check.</param> /// <param name="usPassword">The password to check.</param> /// <returns>True if the login info is valid, false otherwise.</returns> public static bool AuthenticateUser(string usUserName, string usPassword) { string sUserName = usUserName.SQLEscape(); string sStoredHash = ""; DBConnect connect = new DBConnect(); try { connect.Connect(ConnectionsMgr.GetAuthConnInfo()); using (var queryUserAuthInfo = connect.Select(columnPassword, _Table.Users, string.Format("WHERE {0}='{1}'", columnUserName, sUserName))) { if (queryUserAuthInfo.AffectedRows <= 0) { connect.Close(); return(false); } queryUserAuthInfo.Read(); sStoredHash = Encoding.UTF8.GetString((byte[])queryUserAuthInfo.Field2(0)); } connect.Close(); return(MD5Crypt.Verify(usPassword, sStoredHash)); } catch (Exception ex) { ProgramLog.LogError(null, "Auth", "AuthenticateUser", ex.Message + " " + ex.StackTrace); connect.Close(); return(false); } }
public static List <RetailWeekData> GetRetailWeeks(User user) { string dateLatest = ""; List <RetailWeekData> retailWeekList = new List <RetailWeekData>(); try { var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC); { var querySalesDataLatest = connectionSalesData.Select("startdate", tableSales, string.Format("WHERE {0}='{1}' ORDER BY {2} DESC LIMIT 1", _Column.Partner, user.ActivePartner.SQLEscape(), "startdate")); if (querySalesDataLatest.Read()) { DateTime temp; if (!DateTime.TryParse(querySalesDataLatest.Field(0), out temp)) { dateLatest = DateTime.Now.ToMySQLDateStr(); } else { dateLatest = querySalesDataLatest.Field(0); } } else { dateLatest = DateTime.Now.ToMySQLDateStr(); } } connectionSalesData.Close(); var connectionAdminData = ConnectionsMgr.GetAdminConnection(); { var resultQueryWeek = connectionAdminData.Select(new[] { columnRetailWeek, columnRetailYear, columnRetailWeekStart, columnRetailWeekEnd }, _Table.RetailCalendar, string.Format("WHERE {0}<'{1}'", columnRetailWeekStart, dateLatest)); while (resultQueryWeek.Read()) { var date = resultQueryWeek.Field2(2) as DateTime?; if (date != null && date < DateTime.Now) { RetailWeekData retailWeek = new RetailWeekData(); retailWeek.Week = int.Parse(resultQueryWeek.Field(0)); retailWeek.Year = int.Parse(resultQueryWeek.Field(1)); retailWeek.WeekStart = resultQueryWeek.Field(2); retailWeek.WeekEnd = resultQueryWeek.Field(3); retailWeekList.Add(retailWeek); } } } connectionAdminData.Close(); retailWeekList.Sort((weekA, weekB) => weekB.CompareTo(weekA)); return(retailWeekList); } catch (Exception e) { ProgramLog.LogError(user, "SalesRequestManager", "GetRetailWeeks", e.Message); return(new List <RetailWeekData>()); } }
/// <summary> /// Begin a session. Must be called whenever a page loads. /// </summary> /// <param name="usUserName">The username to initialize a session for.</param> /// <param name="request">The request from the user's browser.</param> /// <returns>True if the session was initialized successfully, false otherwise.</returns> public static bool BeginSession(string usUserName, HttpRequest request, OCUserInfo userInfo = null) { try { // Check existence of session HttpSessionState session = HttpContext.Current.Session; if (session == null) { return(false); } User user = new User(); bool isTest = false; //CHECK DEVELOPMENT/TEST ENVIRONMENT string hostname = request.Url.Authority; if (hostname == "10.0.0.245:30658") { isTest = true; } #if DEBUG isTest = true; #endif if (userInfo != null) { isTest = userInfo.IsTest; } if (!GetUserInfo(user, usUserName, isTest)) { return(false); } if (userInfo != null) { for (int i = 0; i < user.PartnerList.Count; i++) { if (user.PartnerList[i].ID == userInfo.ActivePartner) { user.PartnerIndex = i; break; } } } session[SKeys.User] = user; session[SKeys.IsTest] = isTest; session[SKeys.LandingPg] = "Default.aspx"; session[SKeys.TokenSet] = new HashSet <string>(); session[SKeys.TrxDict] = ProcessQueue.GetReqTypeDict(user); session[SKeys.IsOCSession] = userInfo != null; return(true); } catch (Exception e) { ProgramLog.LogError(usUserName, "EDIO", "EDIO", "SessionHandler", "BeginSession", e.Message); return(false); } }
public static List <ItemInfo> GetItemData(User user) { Dictionary <string, QuantityInfo> currentDict = new Dictionary <string, QuantityInfo>(); List <ItemInfo> _itemTable = new List <ItemInfo>(); try { DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC); { using (var reader = connection.Query(string.Format(qFetchCurrDist, user.ActivePartner.SQLEscape()))) { while (reader.Read()) { string vendor = reader.Field(0, "").ToString(); QuantityInfo cqi = new QuantityInfo(); string min = reader.Field(1, "0"); string max = reader.Field(2, "0"); string reo = reader.Field(3, "0"); cqi.Min = (int)double.Parse(min); cqi.Max = (int)double.Parse(max); cqi.Reorder = (int)double.Parse(reo); currentDict.Add(vendor, cqi); } } using (var reader = connection.Query(qFetchBaseDist)) { while (reader.Read()) { ItemInfo info = new ItemInfo(); info.Vendor = reader.Field(0, "").ToString(); info.ItemUPC = reader.Field(1, "").ToString(); info.Description = reader.Field(2, "").ToString(); info.Base = new QuantityInfo() { Min = int.Parse(reader.Field(3, "0")), Max = int.Parse(reader.Field(4, "0")), Reorder = int.Parse(reader.Field(5, "0")) }; if (currentDict.ContainsKey(info.Vendor)) { info.Current = currentDict[info.Vendor]; } _itemTable.Add(info); ItemInfo infoCopy = info.Clone(); } } } connection.Close(); return(_itemTable); } catch (Exception e) { ProgramLog.LogError(user, "ItemTable", "GetItemData", e.Message); return(new List <ItemInfo>()); } }
public static bool Submit(HttpRequest request, ReportDetail req, string usUserName, string usCustomer, string usPartner) { if (request == null || req == null) { return(false); } DBConnect connectionAdmin = new DBConnect(); try { connectionAdmin.Connect(new DatabaseInfo(ConnectionsMgr.GetAdminConnInfo()) { Database = databaseEmailRequest }); string defaultToEmail = "*****@*****.**"; string defaultFromEmail = "*****@*****.**"; StringBuilder builtMessage = new StringBuilder(); builtMessage.AppendLine("From: " + req.Name); builtMessage.AppendLine("Company: " + req.Company); builtMessage.AppendLine("Email: " + req.Email); builtMessage.AppendLine("Message: " + req.Message); builtMessage.AppendLine(); builtMessage.AppendLine("Additional Info"); builtMessage.AppendLine("IP Address: " + request.UserHostAddress); builtMessage.AppendLine("Browser Info: " + request.UserAgent); builtMessage.AppendLine("Referral: " + request.UrlReferrer.ToString()); DateTime requestTime = DateTime.Now; var vals = new Dictionary <string, string>() { { columnUniqueKey, connectionAdmin.GetNewKey() }, { columnCustomer, (usCustomer ?? "").SQLEscape() }, { columnPartner, (usPartner ?? "").SQLEscape() }, { columnRequestDate, requestTime.ToString("yyyy-MM-dd HH:mm:ss") }, { columnToEmail, defaultToEmail }, { columnFromEmail, defaultFromEmail }, { columnSubject, "EDIOC- Support Submission" }, { columnMessage, WrapTextTo70(builtMessage.ToString().SQLEscape()) }, #if DEBUG { columnProcessed, "Y" }, #else { columnProcessed, "" }, #endif { columnSendAfter, requestTime.ToString("yyyy-MM-dd HH:mm:ss") } }; var result = connectionAdmin.Insert(tableEmailReq, vals.ToNameValueCollection()); return(result.AffectedRows > 0); } catch (Exception e) { ProgramLog.LogError(usUserName, usCustomer, usPartner, "SupportRequest", "Submit", e.Message); return(false); } }
public static string GenerateReport(User user, DateTime startDate, DateTime endDate) { try { DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB); Application app = new Application(); app.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable; Workbook xlWB = app.Workbooks.Add(); Worksheet ws = xlWB.Worksheets.Add(); app.ActiveWindow.SplitRow = 1; app.ActiveWindow.FreezePanes = true; //string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" + // " FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey WHERE arrivdate BETWEEN '{0}' AND '{1}' GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum"; string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,c.COLORCODE, c.ITEMCOLOR,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" + " FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey " + " LEFT join catinfo c on d.VENDORNUM=c.VENDORNUM and d.UPCNUM = c.UPCNUM and h.PONUMBER = c.PONUMBER " + " WHERE h.arrivdate BETWEEN '{0}' AND '{1}' " + //" GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum"; " GROUP BY h.ponumber,d.vendornum,c.colorcode ORDER BY h.ponumber,d.vendornum,c.colorcode"; string formatReport = string.Format(queryReport, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")); DBResult resultReport = connection.Query(formatReport); char colLastLetter = (char)('A' + resultReport.FieldCount - 1); string rowLastIndex = (2 + resultReport.AffectedRows - 1).ToString(); object[,] reportData = new object[resultReport.AffectedRows, resultReport.FieldCount]; for (int rowIndex = 0; rowIndex < resultReport.AffectedRows; rowIndex++) { resultReport.Read(); for (int columnIndex = 0; columnIndex < resultReport.FieldCount; columnIndex++) { reportData[rowIndex, columnIndex] = resultReport.Field2(columnIndex, "'---"); } } var rangeHead = ws.Range["A1", colLastLetter + "1"]; var rangeBody = ws.Range["A2", colLastLetter + rowLastIndex]; rangeHead.Value = new[] { "PidQty", "GLC", "MilCost", "MilRetail", "PID", "Color Code", "Color Description", "Contract", "PO #", "Dept.", "Brand", "INDCDate" }; rangeBody.Value = reportData; ws.Columns.AutoFit(); string diFileName = SiteFileSystem.GetTempFileName(); xlWB.SaveCopyAs(diFileName); xlWB.Close(false); string eoToken = Crypt.EncryptFileToFile(user, diFileName); File.Delete(diFileName); return(eoToken); } catch (Exception e) { ProgramLog.LogError(user, "GVMReport", "GenerateReport", e.Message); return(""); } }
private static ShipReport _GenShipReport(User user, string store, List <RetailWeekData> retailWeeks) { ShipReport shipReport = new ShipReport(); shipReport.Store = store.SQLEscape(); shipReport.Shipments = new List <ShipReportData>(); var queryShipInfo = string.Format("SELECT {0},{1},IFNULL({2},'')FROM {3}", _Column.UniqueKey, _Column.ShipmentDate, _Column.BOLNumber, _Table.PHead850) + string.Format(" WHERE {0}='{1}' AND {2}='{3}' AND({4})", _Column.Partner, user.ActivePartner.SQLEscape(), _Column.STId, shipReport.Store, string.Join("OR", from week in retailWeeks select string.Format("({0}>='{1}' AND {0}<='{2}')", _Column.ShipmentDate, week.WeekStart.SQLEscape(), week.WeekEnd.SQLEscape()))); var queryQtyInfo = string.Format("SELECT {0},{1},IFNULL({2},0) FROM {3}", _Column.VendorNum, _Column.UPCNum, _Column.Quantity, _Table.PDetl850); try { var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC); var result = connectionSalesData.Query(queryShipInfo); while (result.Read()) { ShipReportData srd = new ShipReportData(); string keyShip = result.Field(0, ""); srd.ShipDate = ((DateTime)result.Field2(1)).ToString("MMM dd, yyyy"); srd.TrackingNumber = result.Field(2, "").Trim(); srd.Quantity = 0; srd.Items = new List <ShipItemInfo>(); if (keyShip != "") { var resultQuantity = connectionSalesData.Query(queryQtyInfo + string.Format(" WHERE {0}='{1}'", _Column.UniqueKey, keyShip)); while (resultQuantity.Read()) { ShipItemInfo itemInfo = new ShipItemInfo(); itemInfo.VendorNum = resultQuantity.Field(0); itemInfo.UPCNum = resultQuantity.Field(1); itemInfo.Quantity = (int)decimal.Parse(resultQuantity.Field(2)); srd.Items.Add(itemInfo); } if (srd.Items.Count > 0) { srd.Quantity = srd.Items.Sum(item => item.Quantity); } } shipReport.Shipments.Add(srd); } connectionSalesData.Close(); return(shipReport); } catch (Exception e) { ProgramLog.LogError(user, "SalesRequestManager", "_GenShipReport", e.Message); return(shipReport); } }
public static void EditXref(User user, List <XrefRecord> updateRecs) { try { DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home); { List <string> updateCols = new List <string>() { UniqueKey, VendorName, VendorId, VendorSeq, BrandName, Customer, Partner }; List <string> replaceCols = new List <string>() { string.Format("{0}=VALUES({0})", VendorName), string.Format("{0}=VALUES({0})", VendorId), string.Format("{0}=VALUES({0})", VendorSeq), string.Format("{0}=VALUES({0})", BrandName), }; List <string> updateVals = new List <string>(); foreach (var record in updateRecs) { List <string> recVals = new List <string>(); recVals.Add(record.Key); recVals.Add(record.CompanyName.Truncate(60).SQLEscape()); recVals.Add(record.GXSAccount.Truncate(15).SQLEscape()); recVals.Add(record.SelectionCode.Truncate(3).SQLEscape()); recVals.Add(record.BrandName.Truncate(80).SQLEscape()); recVals.Add(user.Customer.SQLEscape()); recVals.Add(user.ActivePartner.SQLEscape()); updateVals.Add(recVals.ToSqlValueList()); } connection.Query(string.Format("INSERT INTO {0} ({1}) VALUES {2} ON DUPLICATE KEY UPDATE {3}", CatXref, updateCols.ToSqlColumnList(), string.Join(",", updateVals), string.Join(",", replaceCols))); } connection.Close(); } catch (Exception e) { ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(EditXref), e.Message); } }
public static void RemoveXref(User user, List <string> keyList) { try { DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home); { connection.Delete(CatXref, $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}"); } connection.Close(); } catch (Exception e) { ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(RemoveXref), e.Message); } }
public static PdsOptions GetOptions(User user) { try { DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home); var opt = _GetOptions(user, connection); connection.Close(); return(opt); } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(GetOptions), e.Message); return(new PdsOptions()); } }
public static OCUserInfo GetOCRecord(string usToken) { OCUserInfo info = new OCUserInfo(); if (string.IsNullOrEmpty(usToken)) { return(info); } string sToken = usToken.SQLEscape(); DBConnect connection = new DBConnect(); DateTime expTime = new DateTime(); try { connection.Connect(ConnectionsMgr.GetAdminConnInfo()); using (var queryUserAuthInfo = connection.Select(new[] { columnUserName, columnActivePartner, columnIsTest, columnCreateDate }, tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken))) { if (queryUserAuthInfo.AffectedRows <= 0) { connection.Close(); return(info); } queryUserAuthInfo.Read(); info.UserName = queryUserAuthInfo.Field(0); info.ActivePartner = queryUserAuthInfo.Field(1); info.IsTest = queryUserAuthInfo.Field(2) == "1"; expTime = (DateTime)queryUserAuthInfo.Field2(3, DateTime.MinValue); connection.Delete(tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken)); } connection.Close(); var authLimit = DateTime.Now.AddSeconds(-OCSessionTimeOutSeconds); if (expTime >= authLimit) //expire date must be within the last OCSessionTimeOutSeconds seconds { info.IsValid = true; } else { throw new Exception("Authentication time is not within range allowed. Auth Time: " + expTime.ToString("yyyy-MM-dd HH:mm:ss") + ", Auth Limit: " + authLimit.ToString("yyyy-MM-dd HH:mm:ss")); } return(info); } catch (Exception ex) { ProgramLog.LogError(null, "Auth", "GetOCRecord", ex.Message); connection.Close(); return(info); } }
public static List <CPOSummaryHead> GetChangeList(User user) { List <CPOSummaryHead> ret = new List <CPOSummaryHead>(); DBConnect connection = new DBConnect(); try { connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB)); var queryCPOHead = connection.Select(new[] { _Column.UniqueKey, _Column.PONumber, _Column.POChangeDate, _Column.Purpose, _Column.TotalItems, _Column.HProcessed }, _Table.Head860, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND ({4}='{5}' OR {4}='{6}')", _Column.Customer, user.Customer, _Column.Partner, user.ActivePartner, _Column.HProcessed, _ProgressFlag.Unprocessed, _ProgressFlag.Error)); while (queryCPOHead.Read()) { CPOSummaryHead newHead = new CPOSummaryHead(); newHead.UniqueKey = queryCPOHead.Field(0, ""); newHead.PONumber = queryCPOHead.Field(1, ""); newHead.POChangeDate = queryCPOHead.Field(2, ""); newHead.Purpose = ElementLookup.GetDesc(user, _Element.Purpose, queryCPOHead.Field(3, "")); newHead.Affected = queryCPOHead.Field(4, ""); newHead.Status = queryCPOHead.Field(5, ""); newHead.Details = new List <CPOSummaryDetail>(); var queryCPODetail = connection.Select(new[] { _Column.ChangeType, _Column.Quantity, _Column.ChangeQuantity, _Column.UnitPrice, _Column.RetailPrice, _Column.UPCNum, _Column.VendorNum, _Column.ItemDesc, _Column.PackSize, _Column.Dropship, _Column.Processed }, _Table.Detail860, string.Format("WHERE {0}='{1}'", _Column.UniqueKey, newHead.UniqueKey)); while (queryCPODetail.Read()) { CPOSummaryDetail newDetail = new CPOSummaryDetail(); newDetail.ChangeType = ElementLookup.GetDesc(user, _Element.ChangeType, queryCPODetail.Field(0, "")); newDetail.Quantity = decimal.Parse(queryCPODetail.Field(1, "0")).ToString("N0"); newDetail.ChangeQuantity = decimal.Parse(queryCPODetail.Field(2, "0")).ToString("N0"); newDetail.UnitPrice = queryCPODetail.Field(3, ""); newDetail.RetailPrc = queryCPODetail.Field(4, ""); newDetail.UPC = queryCPODetail.Field(5, ""); newDetail.VendorNum = queryCPODetail.Field(6, ""); newDetail.ItemDesc = queryCPODetail.Field(7, ""); newDetail.PackSize = queryCPODetail.Field(8, ""); newDetail.Dropship = queryCPODetail.Field(9, "").Replace('\r', ' '); newDetail.Status = queryCPODetail.Field(10, ""); newHead.Details.Add(newDetail); } ret.Add(newHead); } return(ret.OrderBy(h => h.POChangeDate).ThenBy(h => h.PONumber).ThenBy(h => h.Purpose).ToList()); } catch (Exception e) { connection.Close(); ProgramLog.LogError(user, "ChangePOTracker", "GetChangeList", e.Message); return(new List <CPOSummaryHead>()); } }
private static ResponseType Apply860(User user, string sHeadUniqueKey860) { try { DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB); string filterUniqueKey = string.Format("WHERE {0}='{1}'", _Column.UniqueKey, sHeadUniqueKey860); var resultCheckHead = connection.Select(new[] { _Column.PONumber, _Column.Purpose }, _Table.Head860, filterUniqueKey); if (resultCheckHead.AffectedRows == 0) { return(ResponseType.ErrorCPOCouldNotApplyItemChange); } resultCheckHead.Read(); string sPONumber = resultCheckHead.Field(0, ""); string sPurpose = resultCheckHead.Field(1, ""); ResponseType resultOperation = ResponseType.ErrorCPOUnknown; switch (sPurpose) { case Code353.Cancel: resultOperation = CancelPO(connection, user, sPONumber); break; case Code353.Change: resultOperation = ChangePO(connection, user, sPONumber, sHeadUniqueKey860); break; default: resultOperation = ResponseType.ErrorCPOPurposeUnrecognized; break; } if (resultOperation == ResponseType.SuccessCPO) { UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Success); } else { UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Error); } return(resultOperation); } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "Apply860", e.Message); return(ResponseType.ErrorCPOUnknown); } }
private static ResponseType Cancel860(User user, string sHeadUniqueKey860) { DBConnect connection = new DBConnect(); try { connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB)); UpdateAll860ProcFlags(connection, sHeadUniqueKey860, _ProgressFlag.Canceled); connection.Close(); return(ResponseType.SuccessCPO); } catch (Exception e) { connection.Close(); ProgramLog.LogError(user, "ChangePOTracker", "Cancel860", e.Message); return(ResponseType.ErrorCPOUnknown); } }
public static bool SetConnIDs(User user, bool isTest) { if (isTest) { // Use default connections when debugging user.OCConnID = idTestOC; user.NPConnID = idTestNP; user.SHConnID = idTestSH; // Use OC ID user.SLConnID = idTestSL; // Use OC ID return(true); } DBConnect connection = new DBConnect(); if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo())) { return(false); } try { using (var res = connection.Select(new[] { columnOCConnID, columnNPConnID, columnSHConnID, columnSLConnID }, tableConnectionIDInfo, string.Format("WHERE {0}='{1}'", columnCustomer, user.Customer))) { if (!res.Read()) { // No info for customer? ProgramLog.LogError(user.UserName, user.Customer, "EDIO", "ConnectionsMgr", "SetConnIDs", string.Format("Unable to find connection info in {0} for customer {1}", tableConnectionIDInfo, user.Customer)); connection.Close(); return(false); } user.OCConnID = res.Field(0); user.NPConnID = res.Field(1); user.SHConnID = res.Field(2); user.SLConnID = res.Field(3); } connection.Close(); return(true); } catch (Exception e) { ProgramLog.LogError(user, "ConnectionsMgr", "SetConnIDs", e.Message); connection.Close(); return(false); } }
public static List <ReportDesc> GetReportList(User user, List <string> keyList) { List <ReportDesc> repList = new List <ReportDesc>(); List <string> polist = new List <string>(); string sCustomer = user.Customer.SQLEscape(); string sPartner = user.ActivePartner.SQLEscape(); string prefix = $"trx/{user.OCConnID}/downloads/"; if (keyList.Count == 0) { return(repList); } try { var connection = ConnectionsMgr.GetOCConnection(user); using (var queryPoNum = connection.Select(PONumber, $"{ECGB}.{SrcH850}", $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}")) { while (queryPoNum.Read()) { polist.Add(queryPoNum.Field(0)); } } if (polist.Count <= 0) { return(repList); } string filter = $"WHERE {Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND {PONumber} IN {polist.ToSqlValueList()}"; using (var queryReport = connection.Select("*", $"{GrpAdmin}.{Reports}", filter)) { while (queryReport.Read()) { repList.Add(new ReportDesc(queryReport, prefix)); } } } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(GetReportList), e.Message); } return(repList); }
private static string GetVendId(User user, DBConnect connection) { try { using (var query = connection.Select(VendID, $"{Home}.{NetGroup}", $"WHERE {Customer}='{user.Customer.SQLEscape()}' AND {Partner}='{user.ActivePartner.SQLEscape()}'")) { if (query.Read()) { return(query.Field(0)); } } return(""); } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(GetVendId), e.Message); return(""); } }
private static void InsertMultiple(User user, DBConnect connection, string table, List <string> columns, List <List <string> > records) { try { int blockIndex = 0; while (true) { var currentBlock = records.TakeBlock(1000, ref blockIndex); if (currentBlock.Count() <= 0) { break; } connection.InsertMulti(table, columns.ToSqlColumnList(), currentBlock.ToList()); } } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(InsertMultiple), e.Message); } }
private static Dictionary <string, string> GetTrxTypeDict(User user, DBConnect connection, IEnumerable <string> keyList) { Dictionary <string, string> ret = new Dictionary <string, string>(); try { using (var queryRes = connection.Query($"SELECT u,IF(c>1,'D','M')FROM(SELECT uniquekey u,COUNT(*)c FROM srch850 h LEFT JOIN srcb850 b using(uniquekey)WHERE uniquekey IN {keyList.ToSqlValueList()} GROUP BY h.uniquekey)x;")) { while (queryRes.Read()) { ret.Add(queryRes.Field(0), queryRes.Field(1)); } } } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(GetTrxTypeDict), e.Message); } return(ret); }
private static DatabaseInfo _GetConnInfo(User user, string connID, string database = "") { DatabaseInfo dbInfo = new DatabaseInfo(); DBConnect connection = new DBConnect(); if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo())) { ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", "Unable to connect to admin database."); return(dbInfo); } using (var res = connection.Select(new[] { columnServer, columnPort, columnIsTest }, tableAllPorts, string.Format("WHERE {0}='{1}'", columnConnectID, connID.SQLEscape()))) { if (!res.Read()) { ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", string.Format("Unable to find info in table \"{0}\" for connection ID \"{1}\".", tableConnectionIDInfo, connID.SQLEscape())); connection.Close(); return(dbInfo); } dbInfo.Server = res.Field(0); dbInfo.Port = (int)res.Field2(1); dbInfo.IsTest = (int)res.Field2(2) != 0; } connection.Close(); dbInfo.Id = connID; dbInfo.Database = string.IsNullOrWhiteSpace(database) ? databaseHome : database; dbInfo.Driver = _DefaultDriver; if (cred.ContainsKey(dbInfo.Id)) { dbInfo.Username = cred[dbInfo.Id].UserName; dbInfo.Password = cred[dbInfo.Id].Password; } else { dbInfo.Username = defaultCred.UserName; dbInfo.Password = defaultCred.Password; } return(dbInfo); }
private static PdsOptions _GetOptions(User user, DBConnect connection) { try { PdsOptions opt = new PdsOptions(); using (var queryPL = connection.Select(PdsOptions.Columns, Home + "." + NetGroup, $"WHERE {Customer}='{user.Customer.SQLEscape()}' AND {Partner}='{user.ActivePartner.SQLEscape()}'")) { if (queryPL.Read()) { opt = new PdsOptions(queryPL); } } return(opt); } catch (Exception e) { ProgramLog.LogError(user, nameof(PdsManager), nameof(_GetOptions), e.Message); return(new PdsOptions()); } }
private static ResponseType CancelPO(DBConnect connection, User user, string sPONumber) { try { string filterPONumber = string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.Partner, user.ActivePartner.SQLEscape(), _Column.PONumber, sPONumber); var updateVals = new Dictionary <string, string>() { { _Column.CancelDate, DateTime.Now.ToString("yyyy-MM-dd") } }.ToNameValueCollection(); connection.Update(_Table.Head850All, updateVals, filterPONumber); return(ResponseType.SuccessCPO); } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "CancelPO", e.Message); return(ResponseType.ErrorCPOUnknown); } }
private static ResponseType DeleteItem(DBConnect connection, User user, string sFilterLineItem) { try { var result = connection.Delete(_Table.Detail850All, sFilterLineItem); if (result.AffectedRows > 0) { return(ResponseType.SuccessCPO); } else { return(ResponseType.ErrorCPOCouldNotApplyItemChange); } } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "DeleteItem", e.Message); return(ResponseType.ErrorCPOUnknown); } }
private static ResponseType ReplaceAllValues(DBConnect connection, User user, Dictionary <string, string> sVals860, string sFilterLineItem) { try { var result = connection.Update(_Table.Detail850All, GetUpdate850Vals(sVals860).ToNameValueCollection(), sFilterLineItem); if (result.AffectedRows > 0) { return(ResponseType.SuccessCPO); } else { return(ResponseType.ErrorCPOCouldNotApplyItemChange); } } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "ReplaceAllValues", e.Message); return(ResponseType.ErrorCPOUnknown); } }
public static ResponseType PerformAction(User user, CPOAction action) { try { switch (action.Action) { case ActionType.Cancel: return(Cancel860(user, action.Key.SQLEscape())); case ActionType.Apply: return(Apply860(user, action.Key.SQLEscape())); default: return(ResponseType.ErrorCPOUnknown); } } catch (Exception e) { ProgramLog.LogError(user, "ChangePOTracker", "PerformAction", e.Message); return(ResponseType.ErrorCPOUnknown); } }
public static string EncryptFileToFile(User user, string iFileName) { try { if (!File.Exists(iFileName)) { return(""); } byte[] iv = new byte[IVSizeBytes]; byte[] key = new byte[KeySizeBytes]; using (RNGCryptoServiceProvider rcsp = new RNGCryptoServiceProvider()) { rcsp.GetBytes(iv); rcsp.GetBytes(key); } string ivAsString = ByteArrayToString(iv); string keyAsString = ByteArrayToString(key); string oFileName = Path.Combine(new FileInfo(iFileName).Directory.FullName, ivAsString); using (AesCryptoServiceProvider aesProvider = new AesCryptoServiceProvider()) { aesProvider.KeySize = KeySizeBits; aesProvider.IV = iv; aesProvider.Key = key; using (FileStream diStream = File.OpenRead(iFileName)) using (FileStream eoStream = File.Create(oFileName)) using (CryptoStream cryptoStream = new CryptoStream(eoStream, aesProvider.CreateEncryptor(), CryptoStreamMode.Write)) { diStream.CopyTo(cryptoStream); } } return(keyAsString + ivAsString); } catch (Exception e) { ProgramLog.LogError(user, "Crypt", "EncryptFileToFile", e.Message); return(""); } }