private static LocPrtSpecific GetPartnerProperties(string sPartner)
{
LocPrtSpecific p = new LocPrtSpecific();
p.StoreWhere = "s.byid";
switch (sPartner)
{
case _Partner.AAFES:
p.BuyerName = "d.aafesbuyer";
p.StoreJoin = "d856.byid=" + p.StoreWhere;
break;
case _Partner.Navy:
p.BuyerName = "d.nexbuyer";
p.StoreJoin = "substr(lpad(replace(replace(d856.byid,'E',''),'W',''),4,'0'),2,3)=" + p.StoreWhere;
break;
case _Partner.Marines:
p.BuyerName = "d.mcxbuyer";
p.StoreJoin = "if(LENGTH(trim(d856.byid))>4,s.byid=d856.byid, trim(s.xrefid)=substr(trim(LEADING '0' from d856.byid),1,3))"; //WE ARE EXLUDING 4 CHAR STORES THAT DON'T END IN E/W
break;
default:
p.BuyerName = "d.mcxbuyer";
p.StoreJoin = "d856.byid=" + p.StoreWhere;
break;
}
return(p);
}
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("");
}
}
