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