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