/// <summary>
/// Gets the current list of account executives.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <string> GetAccountExecutiveList(User user)
{
List <string> aeList = new List <string>();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
{
var query = connection.Query("SELECT aename FROM deptdesc GROUP BY aename ORDER BY aename");
while (query.Read())
{
var ae = query.Field(0, "").Trim();
if (ae != "")
{
aeList.Add(ae);
}
}
}
connection.Close();
aeList.Sort();
}
catch (Exception e)
{
Log(user, nameof(GetAccountExecutiveList), e);
}
return(aeList);
}
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);
}
/// <summary>
/// Gets the current list of stores.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <Store> GetStoreList(User user)
{
List <Store> stores = new List <Store>();
string sPartner = user.ActivePartner.SQLEscape();
string prtCondition = "";
string storePartner = GetStorePartner(sPartner);
if (sPartner == _Partner.Marines)
{
prtCondition = "AND (LENGTH(xrefid)=3 OR LENGTH(xrefid)=5)"; //EXCLUDE 4 CHAR STORES THAT DON'T END IN 'E' OR 'W' (AKA HAVE 4 CHAR XREFID)
}
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
using (DBResult res = connection.Select(new[] { STName, BYId }, _Table.Stinfo, $"WHERE partner='{storePartner}' and byid!='' {prtCondition} GROUP BY byid ORDER BY byid,upddate DESC"))
{
while (res.Read())
{
var s = new Store(res);
stores.Add(s);
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetStoreList), e);
}
return(stores);
}
public static List <RetailWeekData> GetRetailWeeks(User user)
{
string dateLatest = "";
List <RetailWeekData> retailWeekList = new List <RetailWeekData>();
try
{
var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
var querySalesDataLatest = connectionSalesData.Select("startdate", tableSales, string.Format("WHERE {0}='{1}' ORDER BY {2} DESC LIMIT 1", _Column.Partner, user.ActivePartner.SQLEscape(), "startdate"));
if (querySalesDataLatest.Read())
{
DateTime temp;
if (!DateTime.TryParse(querySalesDataLatest.Field(0), out temp))
{
dateLatest = DateTime.Now.ToMySQLDateStr();
}
else
{
dateLatest = querySalesDataLatest.Field(0);
}
}
else
{
dateLatest = DateTime.Now.ToMySQLDateStr();
}
}
connectionSalesData.Close();
var connectionAdminData = ConnectionsMgr.GetAdminConnection();
{
var resultQueryWeek = connectionAdminData.Select(new[] { columnRetailWeek, columnRetailYear, columnRetailWeekStart, columnRetailWeekEnd }, _Table.RetailCalendar, string.Format("WHERE {0}<'{1}'", columnRetailWeekStart, dateLatest));
while (resultQueryWeek.Read())
{
var date = resultQueryWeek.Field2(2) as DateTime?;
if (date != null && date < DateTime.Now)
{
RetailWeekData retailWeek = new RetailWeekData();
retailWeek.Week = int.Parse(resultQueryWeek.Field(0));
retailWeek.Year = int.Parse(resultQueryWeek.Field(1));
retailWeek.WeekStart = resultQueryWeek.Field(2);
retailWeek.WeekEnd = resultQueryWeek.Field(3);
retailWeekList.Add(retailWeek);
}
}
}
connectionAdminData.Close();
retailWeekList.Sort((weekA, weekB) => weekB.CompareTo(weekA));
return(retailWeekList);
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "GetRetailWeeks", e.Message);
return(new List <RetailWeekData>());
}
}
public static List <ItemInfo> GetItemData(User user)
{
Dictionary <string, QuantityInfo> currentDict = new Dictionary <string, QuantityInfo>();
List <ItemInfo> _itemTable = new List <ItemInfo>();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
using (var reader = connection.Query(string.Format(qFetchCurrDist, user.ActivePartner.SQLEscape())))
{
while (reader.Read())
{
string vendor = reader.Field(0, "").ToString();
QuantityInfo cqi = new QuantityInfo();
string min = reader.Field(1, "0");
string max = reader.Field(2, "0");
string reo = reader.Field(3, "0");
cqi.Min = (int)double.Parse(min);
cqi.Max = (int)double.Parse(max);
cqi.Reorder = (int)double.Parse(reo);
currentDict.Add(vendor, cqi);
}
}
using (var reader = connection.Query(qFetchBaseDist))
{
while (reader.Read())
{
ItemInfo info = new ItemInfo();
info.Vendor = reader.Field(0, "").ToString();
info.ItemUPC = reader.Field(1, "").ToString();
info.Description = reader.Field(2, "").ToString();
info.Base = new QuantityInfo()
{
Min = int.Parse(reader.Field(3, "0")),
Max = int.Parse(reader.Field(4, "0")),
Reorder = int.Parse(reader.Field(5, "0"))
};
if (currentDict.ContainsKey(info.Vendor))
{
info.Current = currentDict[info.Vendor];
}
_itemTable.Add(info);
ItemInfo infoCopy = info.Clone();
}
}
}
connection.Close();
return(_itemTable);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ItemTable", "GetItemData", e.Message);
return(new List <ItemInfo>());
}
}
public static string GenerateReport(User user, DateTime startDate, DateTime endDate)
{
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
Application app = new Application();
app.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
Workbook xlWB = app.Workbooks.Add();
Worksheet ws = xlWB.Worksheets.Add();
app.ActiveWindow.SplitRow = 1;
app.ActiveWindow.FreezePanes = true;
//string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" +
// " FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey WHERE arrivdate BETWEEN '{0}' AND '{1}' GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum";
string queryReport = "SELECT sum(d.quantity) AS PidQty,d.unitprice AS GLC,d.chgprice AS MilCost,d.retailprc AS MilRetail,d.vendornum AS PID,c.COLORCODE, c.ITEMCOLOR,trim(h.ponumber) AS contract,trim(h.custorder) AS PO,h.department AS Dept,h.deptname AS Brand,h.arrivdate AS INDCDate" +
" FROM gdetl855 AS d JOIN ghead855 AS h ON d.uniquekey=h.uniquekey " +
" LEFT join catinfo c on d.VENDORNUM=c.VENDORNUM and d.UPCNUM = c.UPCNUM and h.PONUMBER = c.PONUMBER " +
" WHERE h.arrivdate BETWEEN '{0}' AND '{1}' " +
//" GROUP BY d.vendornum,h.ponumber ORDER BY h.ponumber,d.upcnum";
" GROUP BY h.ponumber,d.vendornum,c.colorcode ORDER BY h.ponumber,d.vendornum,c.colorcode";
string formatReport = string.Format(queryReport, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
DBResult resultReport = connection.Query(formatReport);
char colLastLetter = (char)('A' + resultReport.FieldCount - 1);
string rowLastIndex = (2 + resultReport.AffectedRows - 1).ToString();
object[,] reportData = new object[resultReport.AffectedRows, resultReport.FieldCount];
for (int rowIndex = 0; rowIndex < resultReport.AffectedRows; rowIndex++)
{
resultReport.Read();
for (int columnIndex = 0; columnIndex < resultReport.FieldCount; columnIndex++)
{
reportData[rowIndex, columnIndex] = resultReport.Field2(columnIndex, "'---");
}
}
var rangeHead = ws.Range["A1", colLastLetter + "1"];
var rangeBody = ws.Range["A2", colLastLetter + rowLastIndex];
rangeHead.Value = new[] { "PidQty", "GLC", "MilCost", "MilRetail", "PID", "Color Code", "Color Description", "Contract", "PO #", "Dept.", "Brand", "INDCDate" };
rangeBody.Value = reportData;
ws.Columns.AutoFit();
string diFileName = SiteFileSystem.GetTempFileName();
xlWB.SaveCopyAs(diFileName);
xlWB.Close(false);
string eoToken = Crypt.EncryptFileToFile(user, diFileName);
File.Delete(diFileName);
return(eoToken);
}
catch (Exception e)
{
ProgramLog.LogError(user, "GVMReport", "GenerateReport", e.Message);
return("");
}
}
private static ShipReport _GenShipReport(User user, string store, List <RetailWeekData> retailWeeks)
{
ShipReport shipReport = new ShipReport();
shipReport.Store = store.SQLEscape();
shipReport.Shipments = new List <ShipReportData>();
var queryShipInfo = string.Format("SELECT {0},{1},IFNULL({2},'')FROM {3}", _Column.UniqueKey, _Column.ShipmentDate, _Column.BOLNumber, _Table.PHead850) +
string.Format(" WHERE {0}='{1}' AND {2}='{3}' AND({4})", _Column.Partner, user.ActivePartner.SQLEscape(), _Column.STId, shipReport.Store, string.Join("OR", from week in retailWeeks select string.Format("({0}>='{1}' AND {0}<='{2}')", _Column.ShipmentDate, week.WeekStart.SQLEscape(), week.WeekEnd.SQLEscape())));
var queryQtyInfo = string.Format("SELECT {0},{1},IFNULL({2},0) FROM {3}", _Column.VendorNum, _Column.UPCNum, _Column.Quantity, _Table.PDetl850);
try
{
var connectionSalesData = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
var result = connectionSalesData.Query(queryShipInfo);
while (result.Read())
{
ShipReportData srd = new ShipReportData();
string keyShip = result.Field(0, "");
srd.ShipDate = ((DateTime)result.Field2(1)).ToString("MMM dd, yyyy");
srd.TrackingNumber = result.Field(2, "").Trim();
srd.Quantity = 0;
srd.Items = new List <ShipItemInfo>();
if (keyShip != "")
{
var resultQuantity = connectionSalesData.Query(queryQtyInfo + string.Format(" WHERE {0}='{1}'", _Column.UniqueKey, keyShip));
while (resultQuantity.Read())
{
ShipItemInfo itemInfo = new ShipItemInfo();
itemInfo.VendorNum = resultQuantity.Field(0);
itemInfo.UPCNum = resultQuantity.Field(1);
itemInfo.Quantity = (int)decimal.Parse(resultQuantity.Field(2));
srd.Items.Add(itemInfo);
}
if (srd.Items.Count > 0)
{
srd.Quantity = srd.Items.Sum(item => item.Quantity);
}
}
shipReport.Shipments.Add(srd);
}
connectionSalesData.Close();
return(shipReport);
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "_GenShipReport", e.Message);
return(shipReport);
}
}
private static ResponseType Apply860(User user, string sHeadUniqueKey860)
{
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ECGB);
string filterUniqueKey = string.Format("WHERE {0}='{1}'", _Column.UniqueKey, sHeadUniqueKey860);
var resultCheckHead = connection.Select(new[] { _Column.PONumber, _Column.Purpose }, _Table.Head860, filterUniqueKey);
if (resultCheckHead.AffectedRows == 0)
{
return(ResponseType.ErrorCPOCouldNotApplyItemChange);
}
resultCheckHead.Read();
string sPONumber = resultCheckHead.Field(0, "");
string sPurpose = resultCheckHead.Field(1, "");
ResponseType resultOperation = ResponseType.ErrorCPOUnknown;
switch (sPurpose)
{
case Code353.Cancel:
resultOperation = CancelPO(connection, user, sPONumber);
break;
case Code353.Change:
resultOperation = ChangePO(connection, user, sPONumber, sHeadUniqueKey860);
break;
default:
resultOperation = ResponseType.ErrorCPOPurposeUnrecognized;
break;
}
if (resultOperation == ResponseType.SuccessCPO)
{
UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Success);
}
else
{
UpdateHead860ProcFlag(connection, sHeadUniqueKey860, _ProgressFlag.Error);
}
return(resultOperation);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "Apply860", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
public static void DoChanges(User user, UpdateData updateData)
{
Dictionary <string, string> updateDistroVals = new Dictionary <string, string>()
{
{ columnReviewSun, updateData.days[0] ? "1" : "0" },
{ columnReviewMon, updateData.days[1] ? "1" : "0" },
{ columnReviewTue, updateData.days[2] ? "1" : "0" },
{ columnReviewWed, updateData.days[3] ? "1" : "0" },
{ columnReviewThu, updateData.days[4] ? "1" : "0" },
{ columnReviewFri, updateData.days[5] ? "1" : "0" },
{ columnReviewSat, updateData.days[6] ? "1" : "0" },
{ columnMinDollars, updateData.minDollars.ToString() },
{ columnDayRange, updateData.dayRange.ToString() }
};
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
connection.Update(tableDistroDays, updateDistroVals.ToNameValueCollection(), string.Format("WHERE {0}='{1}'", _Column.Partner, user.ActivePartner.SQLEscape()));
foreach (var kvp in updateData.itemInfo)
{
string qtymin = kvp.Value.Min.ToString();
string qtymax = kvp.Value.Max.ToString();
string qtyreo = kvp.Value.Reorder.ToString();
string vennum = kvp.Key;
connection.Query(string.Format(qUpdateItems, vennum.SQLEscape(), qtymin.SQLEscape(), qtymax.SQLEscape(), qtyreo.SQLEscape(), user.ActivePartner.SQLEscape()));
}
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, "ItemTable", "DoChanges", e.Message);
}
}
public static List <string> GetStoreList(User user)
{
List <string> storeList = new List <string>();
try
{
var connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
var queryStores = connection.Select(_Column.STId, tableStores, string.Format("WHERE {0}='{1}'", _Column.Partner, user.ActivePartner.SQLEscape()));
while (queryStores.Read())
{
storeList.Add(queryStores.Field(0).TrimEnd());
}
}
connection.Close();
return(storeList);
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "GetStoreList", e.Message);
return(new List <string>());
}
}
public static DistroInfo GetDistributionDays(User user)
{
DistroInfo distroDays = new DistroInfo();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
using (var queryDistroDays = connection.Select(new[] { columnReviewSun, columnReviewMon, columnReviewTue, columnReviewWed, columnReviewThu, columnReviewFri, columnReviewSat, columnMinDollars, columnDayRange }, tableDistroDays, string.Format("WHERE {0}='{1}'", _Column.Partner, user.ActivePartner.SQLEscape())))
{
if (queryDistroDays.AffectedRows == 0)
{
ProgramLog.LogError(user, "ItemTable", "GetDistributionDays", string.Format("No distribution data for partner \"{0}\"", user.ActivePartner));
return(distroDays);
}
queryDistroDays.Read();
distroDays.IsSunday = queryDistroDays.Field(0, "0").ToString() != "0";
distroDays.IsMonday = queryDistroDays.Field(1, "0").ToString() != "0";
distroDays.IsTuesday = queryDistroDays.Field(2, "0").ToString() != "0";
distroDays.IsWednesday = queryDistroDays.Field(3, "0").ToString() != "0";
distroDays.IsThursday = queryDistroDays.Field(4, "0").ToString() != "0";
distroDays.IsFriday = queryDistroDays.Field(5, "0").ToString() != "0";
distroDays.IsSaturday = queryDistroDays.Field(6, "0").ToString() != "0";
distroDays.MinDollars = int.Parse(queryDistroDays.Field(7, "0"));
distroDays.DayRange = int.Parse(queryDistroDays.Field(8, "0"));
}
}
connection.Close();
return(distroDays);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ItemTable", "GetDistributionDays", e.Message);
return(distroDays);
}
}
private static List <SalesReport> _GenSalesReport(User user, string storeId, List <RetailWeekData> listQueryWeeks)
{
string querySelectSalesData = "SELECT ihead.vendornum AS vendornum,ibody.unitcost AS unitcost,ibody.unitprice AS unitprice,SUM(IFNULL(onhand.qavailable,0)) as onhand";
string querySelectSalesDataTemplate = ",SUM(IFNULL(sales{0}.quantity,0))AS sold{0},SUM(IFNULL(orders{0}.quantity,0))AS order{0}";
string querySalesDataFormat = " FROM(SELECT uniqitem,unitcost,unitprice FROM vmiitem WHERE partner='{0}')AS ibody" +
" LEFT JOIN(SELECT uniquekey,vendornum FROM imaster)AS ihead ON ihead.uniquekey=ibody.uniqitem";
string queryOnHandFormat = " LEFT JOIN(SELECT * FROM(SELECT uniqueitem, qavailable, uniquest, updatedt FROM vmioh WHERE partner='{0}' AND uniquest='{1}' ORDER BY enddate DESC) AS onhand0 GROUP BY onhand0.uniqueitem)AS onhand ON ihead.uniquekey=onhand.uniqueitem";
string queryFromSalesDataTemplate = " LEFT JOIN(SELECT uniqitem,SUM(quantity)AS quantity FROM vmisales WHERE partner='{0}' AND enddate>='{1}' AND enddate<='{2}' {3} GROUP BY uniqitem)AS sales{5} ON sales{5}.uniqitem=ihead.uniquekey" +
" LEFT JOIN(SELECT pd.vendornum AS vendornum,ROUND(SUM(pd.quantity),0)AS quantity FROM(SELECT uniquekey FROM phead850 WHERE partner='{0}' AND podate>='{1}' AND podate<='{2}' {4})AS ph LEFT JOIN pdetl850 AS pd ON ph.uniquekey=pd.uniquekey GROUP BY vendornum)AS orders{5} ON orders{5}.vendornum=ihead.vendornum";
string queryFromSalesDataEnd = " GROUP BY ihead.vendornum";
string stidComp = _Column.STId + "='" + storeId.SQLEscape() + "'";
List <SalesReport> salesReport = new List <SalesReport>();
try
{
var connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
var queryStoreKeys = connection.Select(_Column.UniqueKey, tableStores, "WHERE " + stidComp);
string storeUniqueKey = "";
if (queryStoreKeys.Read())
{
storeUniqueKey = queryStoreKeys.Field(0);
}
string conditionStoreID = "AND " + stidComp;
string conditionStoreKey = string.Format("AND {0}='{1}'", _Column.UniqueSt, storeUniqueKey);
string queryFromSalesData = string.Format(querySalesDataFormat, user.ActivePartner.SQLEscape());
queryFromSalesData += string.Format(queryOnHandFormat, user.ActivePartner.SQLEscape(), storeUniqueKey);
for (int i = 0; i < listQueryWeeks.Count; i++)
{
querySelectSalesData += string.Format(querySelectSalesDataTemplate, i);
queryFromSalesData += string.Format(queryFromSalesDataTemplate, user.ActivePartner.SQLEscape(), listQueryWeeks[i].WeekStart, listQueryWeeks[i].WeekEnd, conditionStoreKey, conditionStoreID, i);
}
string queryCombined = querySelectSalesData + queryFromSalesData + queryFromSalesDataEnd;
var result = connection.Query(queryCombined);
while (result.Read())
{
SalesReport sr = new SalesReport();
sr.VendorNum = result.Field(0);
sr.UnitCost = decimal.Parse(result.Field(1, "0"));
sr.UnitPrice = decimal.Parse(result.Field(2, "0"));
sr.OnHand = int.Parse(result.Field(3, "0"));
sr.Details = new List <SaleData>();
for (int i = 0; i < listQueryWeeks.Count; i++)
{
SaleData sd = new SaleData();
sd.RetailWeek = listQueryWeeks[i];
sd.Sold = int.Parse(result.Field(4 + (i * 2), "0"));
sd.Order = int.Parse(result.Field(5 + (i * 2), "0"));
sr.Details.Add(sd);
}
salesReport.Add(sr);
}
}
connection.Close();
return(salesReport);
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "_GenSalesReport", e.Message);
return(new List <SalesReport>());
}
}
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("");
}
}
