private static StLookupDict GetStoreInfoLookup(User user, string storePartner, IEnumerable <string> storeList) { StLookupDict stLookup = new StLookupDict(); string compList = storeList.ToSqlValueList(); try { DBConnect connection = ConnectionsMgr.GetSharedConnection(user); using (var querySt = connection.Select(new[] { BYId, XrefId, STName }, _Table.Stinfo, $"WHERE {Partner}='{storePartner}' AND ({BYId} IN {compList} OR TRIM({XrefId}) IN {compList})")) { while (querySt.Read()) { string xref = querySt.FieldByName(XrefId); string byid = querySt.FieldByName(BYId); string stname = querySt.FieldByName(STName); if (!stLookup.byidDict.ContainsKey(byid)) { stLookup.byidDict.Add(byid, stname); } if (!stLookup.xrefDict.ContainsKey(xref)) { stLookup.xrefDict.Add(xref, stname); } } } connection.Close(); } catch (Exception e) { Log(user, nameof(GetStoreInfoLookup), e); } return(stLookup); }
public void SetStoreName(string sPartner, StLookupDict storeList) { if (sPartner == _Partner.MarinesReplenishment) { if (Store.Length > 4) { if (storeList.byidDict.ContainsKey(StoreLookup)) { StoreName = storeList.byidDict[StoreLookup]; } } else { if (storeList.xrefDict.ContainsKey(StoreLookup)) { StoreName = storeList.xrefDict[StoreLookup]; } } } else { if (storeList.byidDict.ContainsKey(StoreLookup)) { StoreName = storeList.byidDict[StoreLookup]; } } }
private static string GenLocationReportThlaNavrMrnr(User user, LocationReportDetails reportDetails) { try { string dbSales = "sales_" + user.Customer; string sCustomer = user.Customer.SQLEscape(); string sPartner = user.ActivePartner.SQLEscape(); string sStartDate = reportDetails.StartDate.ToMySQLDateStr(); string sEndDate = reportDetails.EndDate.ToMySQLDateStr(); string storePartner = GetStorePartner(sPartner); string condH856 = $"{Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND {ShipmentDate}>='{sStartDate}' AND {ShipmentDate}<='{sEndDate}'"; var itemList = GetLocReportData(user, sPartner, condH856); var storeLookup = new StLookupDict(); var itemLookup = new Dictionary <string, ItemInfo>(); if (itemList.Count > 0) { storeLookup = GetStoreInfoLookup(user, storePartner, itemList.Select(x => x.StoreLookup)); var vendList = sPartner == _Partner.Thalia ? itemList.Select(x => x.PidStyle.Length > 11 ? x.PidStyle.Substring(0, 11) : x.PidStyle) : itemList.Select(x => x.PidStyle); itemLookup = GetItemInfoLookup(user, sPartner, dbSales, vendList, itemList.Select(x => x.Upc)); } foreach (var item in itemList) { item.SetStoreName(sPartner, storeLookup); item.SetItemInfo(itemLookup); } { HashSet <string> snSet = new HashSet <string>(reportDetails.Stores?.Distinct() ?? new List <string>()); HashSet <string> bnSet = new HashSet <string>(reportDetails.BrandNames?.Distinct() ?? new List <string>()); itemList = itemList.Where(x => (reportDetails.POQuery != "" ? x.MilPo == reportDetails.POQuery : true) && (snSet.Count > 0 ? snSet.Contains(x.StoreLookup) : true) && (bnSet.Count > 0 ? bnSet.Contains(x.BrandName) : true) ).ToList(); } var header = new[] { "Mil PO", "PID Style", "UPC", "Color Code", "Color Description", "Category", "Brand Name", "Sub Category", "Class", "Sub Class", "Description", "Mil Cost", "Total Mil Cost", "Mil Retail", "Total Mil Retail", "Store #", "Store Name", "Ship Date", "On Order", "Shipped" }; var reportData = CopyItemInfoToArray(itemList); int colCount = header.Length; int rowCount = itemList.Count; return(SaveReportToExcelFile(user, header, reportData, rowCount, colCount, new[] { "L:L", "M:M", "N:N", "O:O" })); } catch (Exception e) { Log(user, nameof(GenLocationReportThlaNavrMrnr), e); return(""); } }