private static bool CreateTempStoreTable(User user, DBConnect connection, LocationReportDetails reportDetails)
{
try
{
connection.DropTable("temp.xstore", true);
List <DBField> fieldList = new List <DBField>()
{
new DBField("byid", DBField.FieldTypes.VARCHAR, "20", false),
new DBField("stname", DBField.FieldTypes.VARCHAR, "35", false)
};
string extra = "";
if (user.ActivePartner.SQLEscape() == _Partner.Marines)
{
fieldList.Add(new DBField("xrefid", DBField.FieldTypes.VARCHAR, "20", false));
extra += ",xrefid";
}
string condStore = "";
if (reportDetails.Stores.Count > 0)
{
condStore = string.Format("AND byid IN {0}", reportDetails.Stores.ToSqlValueList());
}
string insertQuery = string.Format("INSERT INTO temp.xstore SELECT byid,stname{0} FROM home.stinfo WHERE partner='{1}' {2} GROUP BY byid", extra, GetStorePartner(user.ActivePartner.SQLEscape()), condStore);
connection.CreateTemp("temp.xstore", fieldList, "byid");
connection.Query(insertQuery);
return(true);
}
catch (Exception e)
{
Log(user, nameof(CreateTempStoreTable), e);
return(false);
}
}
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("");
}
}
public static string GenerateASNSummaryReport(User user, LocationReportDetails reportDetails)
{
switch (user.ActivePartner)
{
case _Partner.Anastasia:
return("");
default:
return(GenASNReportGeneral(user, reportDetails));
}
}
public static string GenerateLocationReport(User user, LocationReportDetails reportDetails)
{
switch (user.ActivePartner)
{
case _Partner.Anastasia:
return(GenLocationReportANAS(user, reportDetails));
case _Partner.Thalia:
case _Partner.NavyReplenishment:
case _Partner.MarinesReplenishment:
return(GenLocationReportThlaNavrMrnr(user, reportDetails));
default:
return(GenLocationReportGeneral(user, reportDetails));
}
}
private static string GetConditionDetail(LocPrtSpecific prtInfo, LocationReportDetails reportDetails, string partner)
{
string conditionDetail = "1";
if (!string.IsNullOrWhiteSpace(reportDetails.BOLQuery))
{
if (partner == _Partner.Anastasia)
{
conditionDetail += string.Format(" AND h810.bolnumber='{0}'", reportDetails.BOLQuery.Trim().SQLEscape());
}
else
{
conditionDetail += string.Format(" AND h856.bolnumber='{0}'", reportDetails.BOLQuery.Trim().SQLEscape());
}
}
if (!string.IsNullOrWhiteSpace(reportDetails.POQuery))
{
if (partner == _Partner.Anastasia)
{
conditionDetail += string.Format(" AND h810.ponumber='{0}'", reportDetails.POQuery.Trim().SQLEscape());
}
else
{
conditionDetail += string.Format(" AND d856.ponumber='{0}'", reportDetails.POQuery.Trim().SQLEscape());
}
}
if (reportDetails.Stores.Count > 0)
{
conditionDetail += string.Format(" AND {0} IN {1}", prtInfo.StoreWhere, reportDetails.Stores.ToSqlValueList());
}
if (partner != _Partner.Anastasia && reportDetails.BrandNames.Count > 0)
{
conditionDetail += string.Format(" AND IFNULL(c.branddesc,d.deptname) IN {0}", reportDetails.BrandNames.ToSqlValueList());
}
if (partner != _Partner.Anastasia && reportDetails.AENames.Count > 0)
{
conditionDetail += string.Format(" AND d.aename IN {0}", reportDetails.AENames.ToSqlValueList());
}
return(conditionDetail);
}
private static string GenASNReportGeneral(User user, LocationReportDetails reportDetails)
{
try
{
string sPartner = user.ActivePartner.SQLEscape();
string reportTemplate = "SELECT d856.ponumber AS `Mil PO`," +
"gh855.custodate AS `Mil PO Date`," +
"UPPER(DATE_FORMAT(gh855.ARRIVDATE,'%b-%Y')) AS `DC Month`," +
"d856.shipnum AS `ASN #`," +
"TRIM(gd855.vendornum) AS `PID Style`," +
"TRIM(d856.upcnum) AS `UPC #`," +
"TRIM(c.colorcode) AS `Color Code`," +
"TRIM(c.itemcolor) AS `Color Decription`," +
"d856.cartid AS `Carton ID`," +
"TRIM(d.category) AS `Category`," +
"TRIM(IFNULL(c.branddesc,d.deptname)) AS `Brand Name`," +
"TRIM(c.scategory) AS `Sub Category`," +
"TRIM(c.prodclass) AS `Class`," +
"TRIM(c.sprodclass) AS `Sub Class`," +
"TRIM(c.itemdesc) AS `Description`," +
"ROUND(gd855.unitprice * SUM(d856.shpunits),2) AS `GLC`," +
"ROUND(gd855.chgprice * SUM(d856.shpunits),2) AS `Mil Cost`," +
"ROUND(gd855.retailprc * SUM(d856.shpunits),2) AS `Mil Retail`," +
"gh855.fobtype AS `East/West`," +
"d856.byid AS `Store #`," +
"s.stname AS `Store Name`," +
"h856.shipdate AS `Ship Date`," +
"ROUND(SUM(d856.ordunits)) AS `On Order`," +
"ROUND(SUM(d856.shpunits)) AS `Shipped`," +
"gh855.department AS `MMG Dept.`," +
"{0} AS `Buyer Name`," +
"d.aename AS `Account Executive`" +
//" FROM(SELECT uniquekey,shipdate,bolnumber FROM ecgb.head856 WHERE partner='{1}' AND shipdate>='{2}' AND shipdate<='{3}') AS h856" +
//" JOIN ecgb.detl856 AS d856 ON h856.uniquekey=d856.uniquekey" +
" FROM(SELECT uniquekey,shipdate,bolnumber FROM mrsk3pl.head856 WHERE partner IN {1} AND shipdate>='{2}' AND shipdate<='{3}') AS h856" +
" JOIN mrsk3pl.detl856 AS d856 ON h856.uniquekey=d856.uniquekey and !isnull(d856.invoiceno)" +
GetJoinThla(user.ActivePartner) +
" JOIN ecgb.ghead855 AS gh855 ON gh855.custorder=d856.ponumber" +
" LEFT JOIN temp.xdept AS d ON d.department=gh855.department" +
" LEFT JOIN ecgb.gdetl855 AS gd855 ON gd855.uniquekey=gh855.uniquekey" +
" AND gd855.upcnum=d856.upcnum" +
//" AND gd855.vendornum=d856.vendornum" + /* UPDATE FROM NEIL: We don't need to match Vendornum here since we're using ponumber + upcnum already */
" LEFT JOIN ecgb.catinfo AS c ON c.ponumber = gh855.ponumber" +
" AND c.upcnum=gd855.upcnum" +
" AND c.vendornum=gd855.vendornum" +
" LEFT JOIN temp.xstore AS s ON {4}" +
" WHERE " + GetConditionThla(user.ActivePartner) + " AND {5}" +
" GROUP BY h856.shipdate,d856.shipnum,d856.upcnum,d856.cartid,d856.ponumber,d856.podate,gd855.vendornum,gd855.itemdesc,d856.byid,d.aename,{0},gh855.department;";
LocPrtSpecific prtInfo = GetPartnerProperties(sPartner);
string conditionDetail = GetConditionDetail(prtInfo, reportDetails, user.ActivePartner);
string formatReport = string.Format(reportTemplate, prtInfo.BuyerName, GetPartnerQueryStr(user).ToSqlValueList(), reportDetails.StartDate.ToString("yyyy-MM-dd"), reportDetails.EndDate.ToString("yyyy-MM-dd"), prtInfo.StoreJoin, conditionDetail);
//string formatReport = string.Format(reportTemplate, prtInfo.BuyerName, user.ActivePartner.SQLEscape(), reportDetails.StartDate.ToString("yyyy-MM-dd"), reportDetails.EndDate.ToString("yyyy-MM-dd"), prtInfo.StoreJoin, conditionDetail);
var header = new[] { "Mil PO", "Mil PO Date", "DC Month", "ASN #", "PID Style", "UPC #", "Color Code", "Color Description", "Carton ID", "Category", "Brand Name", "Sub Category", "Class", "Sub Class", "Description", "GLC", "Mil Cost", "Mil Retail", "East/West", "Store #", "Store Name", "Ship Date", "On Order", "Shipped", "MMG Dept.", "Buyer Name", "Account Executive" };
int colCount = 0;
int rowCount = 0;
object[,] reportData = null;
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
if (!CreateTempStoreTable(user, connection, reportDetails))
{
return("");
}
if (!CreateTempDeptTable(user, connection))
{
return("");
}
using (var resultReport = connection.Query(formatReport))
{
colCount = resultReport.FieldCount;
rowCount = resultReport.AffectedRows;
reportData = CopyResultDataToArray(resultReport);
}
return(SaveReportToExcelFile(user, header, reportData, rowCount, colCount, new[] { "P:P", "Q:Q", "R:R" }));
}
catch (Exception e)
{
Log(user, nameof(GenASNReportGeneral), e);
return("");
}
}
private static string GenLocationReportANAS(User user, LocationReportDetails reportDetails)
{
try
{
string sCustomer = user.Customer.SQLEscape();
string sPartner = user.ActivePartner.SQLEscape();
string reportTemplate = "SELECT shipmonth AS `Ship Month`," +
"shipdate AS `Ship Date`," +
"ponumber AS `PO #`," +
"podate AS `PO Date`," +
"canceldate AS `Cancel Date`," +
"stid AS `STID`," +
"stname AS `Ship To Name`," +
"SUM(quantity) AS `Quantity`," +
"vendornum AS `Vendor #`," +
"upcnum AS `UPC #`," +
"itemdesc AS `Item Description`," +
"SUM(CAST(unitprice AS DECIMAL(14,2))) AS `Unit Price`," +
"SUM(CAST(lineprice AS DECIMAL(14,2))) AS `Extended Price`" +
"FROM" +
"(SELECT DATE_FORMAT(h810.shipdate,'%M %Y') AS shipmonth," +
"h810.shipdate AS shipdate," +
"h810.ponumber AS ponumber," +
"h810.podate AS podate," +
"h850.canceldate AS canceldate," +
"h810.stid AS stid," +
"s.stname AS stname," +
"ROUND(d810.quantity) AS quantity," +
"TRIM(d810.vendornum) AS vendornum," +
"d810.upcnum AS upcnum," +
"d850.itemdesc AS itemdesc," +
"d810.unitprice AS unitprice," +
"d810.unitprice * d810.quantity AS lineprice" +
" FROM (SELECT uniquekey,uniquepo,shipdate,ponumber,podate,stid,byid FROM ecgb.trxh810 WHERE customer='{0}' AND partner='ANAS' AND shipdate>='{1}' AND shipdate<='{2}') AS h810" +
" JOIN ecgb.trxd810 AS d810 ON h810.uniquekey=d810.uniquekey AND !isnull(d810.invoiceno)" +
" LEFT JOIN srch850 AS h850 ON h810.uniquepo=h850.uniquekey" +
" LEFT JOIN srcd850 AS d850 ON h850.uniquekey=d850.uniquekey AND d810.uniqpoitem=d850.uniqueitem" +
" LEFT JOIN temp.xstore AS s ON h810.byid=s.byid" +
" WHERE {3}" +
" GROUP BY ponumber,vendornum,upcnum,h810.byid,shipdate)" +
" AS st GROUP BY ponumber,vendornum,upcnum,shipdate;";
LocPrtSpecific prtInfo = GetPartnerProperties(sPartner);
string conditionDetail = GetConditionDetail(prtInfo, reportDetails, sPartner);
string formatReport = string.Format(reportTemplate, sCustomer, reportDetails.StartDate.ToMySQLDateStr(), reportDetails.EndDate.ToMySQLDateStr(), conditionDetail);
var header = new[] { "Ship Month", "Ship Date", "PO #", "PO Date", "Cancel Date", "STID", "Ship To Name", "Quantity", "Vendor #", "UPC #", "Item Description", "Unit Price", "Extended Price" };
int colCount = 0;
int rowCount = 0;
object[,] reportData = null;
List <List <string> > storeNameData = new List <List <string> >();
{
string condStore = "";
if (reportDetails.Stores.Count > 0)
{
condStore = string.Format("AND byid IN {0}", reportDetails.Stores.ToSqlValueList());
}
DBConnect connectionSHR = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
using (var queryStore = connectionSHR.Select("byid,stname", _Table.Stinfo, string.Format("WHERE partner='NAVY' {0} GROUP BY byid", condStore)))
{
while (queryStore.Read())
{
storeNameData.Add(new List <string>()
{
queryStore.Field(0), queryStore.Field(1)
});
}
}
connectionSHR.Close();
}
DBConnect connection = ConnectionsMgr.GetOCConnection(user, _Database.ECGB);
if (!CreateTempStoreTableFromData(user, connection, storeNameData))
{
return("");
}
using (var resultReport = connection.Query(formatReport))
{
colCount = resultReport.FieldCount;
rowCount = resultReport.AffectedRows;
reportData = CopyResultDataToArray(resultReport);
}
return(SaveReportToExcelFile(user, header, reportData, rowCount, colCount, new[] { "L:L", "M:M" }));
}
catch (Exception e)
{
Log(user, nameof(GenLocationReportANAS), e);
return("");
}
}
