public static List <PoSummary> VerifyPoList(User user, IEnumerable <string> poList)
{
List <PoSummary> ret = new List <PoSummary>();
try
{
string sCustomer = user.Customer.SQLEscape();
string sPartner = user.ActivePartner.SQLEscape();
var connection = ConnectionsMgr.GetOCConnection(user, ECGB);
{
using (var queryCurrent = connection.Select($"{UniqueKey},{PONumber}", SrcH850, $"WHERE {Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND POStatus IN (0, 1) AND {PONumber} IN {poList.ToSqlValueList()}"))
{
while (queryCurrent.Read())
{
ret.Add(new PoSummary(queryCurrent.Field(0), queryCurrent.Field(1)));
}
}
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(VerifyPoList), e.Message);
}
return(ret);
}
public static List <PoItem> GetCurrentPoList(User user, FilterInfo filter)
{
List <PoItem> ret = new List <PoItem>();
try
{
var connection = ConnectionsMgr.GetOCConnection(user, ECGB);
{
var recCount = _GetRecordCount(user, connection, filter);
if (recCount > 0)
{
filter.ResultCount = recCount;
filter.MaxPage = (int)(Math.Ceiling(recCount / (double)resultPageSize));
}
using (var queryCurrent = connection.Select($"{_Column.UniqueKey},{_Column.PONumber},{_Column.PODate},{_Column.ShipmentDate},{_Column.CancelDate},potype,round(totcmpinv) totcmpinv,round(totcmpasn) totcmpasn,round({_Column.TotalItems}) {_Column.TotalItems}", SrcH850, $"WHERE POStatus IN (0, 1) AND {filter.ToFetchQueryString(user, searchCols, resultPageSize)}"))
{
while (queryCurrent.Read())
{
PoItem po = new PoItem(queryCurrent);
ret.Add(po);
}
}
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetCurrentPoList), e.Message);
}
return(ret);
}
public static List <XrefRecord> GetXrefList(User user, FilterInfo filter)
{
List <XrefRecord> ret = new List <XrefRecord>();
try
{
var connection = ConnectionsMgr.GetOCConnection(user, Home);
{
var recCount = _GetRecordCount(user, connection, filter);
if (recCount > 0)
{
filter.ResultCount = recCount;
filter.MaxPage = (int)(Math.Ceiling(recCount / (double)resultPageSize));
}
using (var queryCurrent = connection.Select("*", CatXref, $"WHERE {filter.ToFetchQueryString(user, searchCols, resultPageSize)}"))
{
while (queryCurrent.Read())
{
XrefRecord xr = new XrefRecord(queryCurrent);
ret.Add(xr);
}
}
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(GetXrefList), e.Message);
}
return(ret);
}
public static void EditXref(User user, List <XrefRecord> updateRecs)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
{
List <string> updateCols = new List <string>()
{
UniqueKey,
VendorName,
VendorId,
VendorSeq,
BrandName,
Customer,
Partner
};
List <string> replaceCols = new List <string>()
{
string.Format("{0}=VALUES({0})", VendorName),
string.Format("{0}=VALUES({0})", VendorId),
string.Format("{0}=VALUES({0})", VendorSeq),
string.Format("{0}=VALUES({0})", BrandName),
};
List <string> updateVals = new List <string>();
foreach (var record in updateRecs)
{
List <string> recVals = new List <string>();
recVals.Add(record.Key);
recVals.Add(record.CompanyName.Truncate(60).SQLEscape());
recVals.Add(record.GXSAccount.Truncate(15).SQLEscape());
recVals.Add(record.SelectionCode.Truncate(3).SQLEscape());
recVals.Add(record.BrandName.Truncate(80).SQLEscape());
recVals.Add(user.Customer.SQLEscape());
recVals.Add(user.ActivePartner.SQLEscape());
updateVals.Add(recVals.ToSqlValueList());
}
connection.Query(string.Format("INSERT INTO {0} ({1}) VALUES {2} ON DUPLICATE KEY UPDATE {3}", CatXref, updateCols.ToSqlColumnList(), string.Join(",", updateVals), string.Join(",", replaceCols)));
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(EditXref), e.Message);
}
}
public static PdsOptions GetOptions(User user)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
var opt = _GetOptions(user, connection);
connection.Close();
return(opt);
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetOptions), e.Message);
return(new PdsOptions());
}
}
public static void RemoveXref(User user, List <string> keyList)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
{
connection.Delete(CatXref, $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}");
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(RemoveXref), e.Message);
}
}
public static List <ReportDesc> GetReportList(User user, List <string> keyList)
{
List <ReportDesc> repList = new List <ReportDesc>();
List <string> polist = new List <string>();
string sCustomer = user.Customer.SQLEscape();
string sPartner = user.ActivePartner.SQLEscape();
string prefix = $"trx/{user.OCConnID}/downloads/";
if (keyList.Count == 0)
{
return(repList);
}
try
{
var connection = ConnectionsMgr.GetOCConnection(user);
using (var queryPoNum = connection.Select(PONumber, $"{ECGB}.{SrcH850}", $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}"))
{
while (queryPoNum.Read())
{
polist.Add(queryPoNum.Field(0));
}
}
if (polist.Count <= 0)
{
return(repList);
}
string filter = $"WHERE {Customer}='{sCustomer}' AND {Partner}='{sPartner}' AND {PONumber} IN {polist.ToSqlValueList()}";
using (var queryReport = connection.Select("*", $"{GrpAdmin}.{Reports}", filter))
{
while (queryReport.Read())
{
repList.Add(new ReportDesc(queryReport, prefix));
}
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetReportList), e.Message);
}
return(repList);
}
public static bool IsAllowed(User user, AppID app)
{
try
{
if (user == null || app == AppID.None)
{
return(false);
}
var connection = ConnectionsMgr.GetOCConnection(user);
using (var queryUserLevelAP = connection.Select(idToColumn[app], _Table.UserInfo, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND {4}='{5}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape(), _Column.UserName, user.UserName.SQLEscape())))
{
if (queryUserLevelAP.AffectedRows != 0)
{
queryUserLevelAP.Read();
return(queryUserLevelAP.Field(0) != "0");
}
}
using (var queryCustPartAP = connection.Select(idToColumn[app], _Table.NetGroup, string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape())))
{
if (queryCustPartAP.AffectedRows == 0)
{
ProgramLog.LogError(user, nameof(AppManagement), nameof(IsAllowed), string.Format("Error: No information found for customer {0} and partner {1}.", user.Customer, user.ActivePartner));
return(false);
}
queryCustPartAP.Read();
return(queryCustPartAP.Field(0) != "0");
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(AppManagement), nameof(IsAllowed), e.Message);
return(false);
}
}
public static bool CreateXref(User user, XrefRecord rec)
{
string sVendName = rec.CompanyName.Truncate(60).SQLEscape();
string sVendId = rec.GXSAccount.Truncate(15).SQLEscape();
string sVendSeq = rec.SelectionCode.Truncate(3).SQLEscape();
string sBrandName = rec.BrandName.Truncate(80).SQLEscape();
string sCustomer = user.Customer.SQLEscape();
string sPartner = user.ActivePartner.SQLEscape();
string skey = DBConnect.GenerateUniqueKey();
Dictionary <string, string> insertDict = new Dictionary <string, string>()
{
{ UniqueKey, skey },
{ VendorName, sVendName },
{ VendorId, sVendId },
{ VendorSeq, sVendSeq },
{ BrandName, sBrandName },
{ Customer, sCustomer },
{ Partner, sPartner }
};
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
{
connection.Insert(CatXref, insertDict);
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(CreateXref), e.Message);
return(false);
}
return(true);
}
public static List <string> CreateInvAsn(User user, List <PoSummary> poList)
{
string cCustomer = user.Customer.SQLEscape();
string cPartner = user.ActivePartner.SQLEscape();
List <string> sentKeys = new List <string>();
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, ECGB);
var options = _GetOptions(user, connection);
var trxDict = GetTrxTypeDict(user, connection, poList.Select(x => x.Key));
string cVendId = GetVendId(user, connection);
string invkey = null;
int fileIdx = 0;
Dictionary <string, string> invKeys = new Dictionary <string, string>();
List <List <string> > invRpts = new List <List <string> >();
foreach (var po in poList)
{
string cPoNumber = po.PONumber.SQLEscape();
string cInvNumber = po.InvoiceNumber.SQLEscape();
string cBolNumber = po.BolNumber.SQLEscape();
string cTrxType = trxDict.ContainsKey(po.Key) ? trxDict[po.Key] : "M";
if (options.IsInvoiceEnabled && options.IsPackingEnabled)
{
connection.Query($"CALL ediproc.CreT856FromS850multi('{cPoNumber}','{cBolNumber}','{cCustomer}','{cPartner}','{cTrxType}','0')");
using (var q = connection.Query("SELECT @NewKey AS uniquekey"))
{
if (q.Read())
{
string cSourceKey = q.Field(0);
using (DBResult res = connection.Query($"SELECT ediproc.CreT810FromT856S850multi('{cSourceKey}','{cInvNumber}','{cCustomer}','{cPartner}','{cPoNumber}','')"))
{
if (res.Read())
{
invkey = res.Field(0);
invKeys.Add(cInvNumber, invkey);
invRpts.Add(_CreateReportReq(user, DateTime.Now, invkey, "810", "trx810p.rpt", $"{ cCustomer}_810_{DateTime.Now.ToString("yyyyMMddHHmmss")}{++fileIdx}-report.pdf", "Invoice Report", cCustomer, cPartner, cPoNumber));
connection.Query($"call ediproc.UpdPOInvoiced('{cPoNumber}','{cCustomer}','{cPartner}','UPCNUM', 0)");
}
}
sentKeys.Add(cSourceKey);
}
}
}
else if (options.IsInvoiceEnabled)
{
using (DBResult res = connection.Query($"SELECT ediproc.CreT810FromS850('{cPoNumber}','{cInvNumber}','{cVendId}','{cCustomer}','{cPartner}')"))
{
if (res.Read())
{
invkey = res.Field(0);
invKeys.Add(cInvNumber, invkey);
invRpts.Add(_CreateReportReq(user, DateTime.Now, invkey, "810", "trx810p.rpt", $"{ cCustomer}_810_{DateTime.Now.ToString("yyyyMMddHHmmss")}{++fileIdx}-report.pdf", "Invoice Report", cCustomer, cPartner, cPoNumber));
connection.Query($"call ediproc.UpdPOInvoiced('{cPoNumber}','{cCustomer}','{cPartner}','UPCNUM', 0)");
}
}
}
else if (options.IsPackingEnabled)
{
connection.Query($"CALL ediproc.CreT856FromS850multi('{cPoNumber}','{cBolNumber}','{cCustomer}','{cPartner}','{cTrxType}','0')");
using (var q = connection.Query("SELECT @NewKey AS uniquekey"))
{
if (q.Read())
{
sentKeys.Add(q.Field(0));
}
}
}
}
if (sentKeys.Count() > 0)
{
//UPDATE SHIPDATE TO CURRENT DATE (STORED PROCEDURE LEAVES IT NULL ON PURPOSE)
//ALSO UPDATE SHIP WEIGHT AND DELIVDATE
connection.Query($"UPDATE TRXH856 SET SHIPDATE='{DateTime.Now.ToMySQLDateStr()}', DELIVDATE='{DateTime.Now.AddDays(2).ToMySQLDateStr()}', SHIPWEIGHT=1 WHERE UNIQUEKEY IN ('{string.Join("','",sentKeys)}')");
}
if (invKeys.Count() > 0)
{
connection.Query($"UPDATE TRXH810 SET SHIPDATE='{DateTime.Now.ToMySQLDateStr()}' WHERE UNIQUEKEY IN ('{string.Join("','", invKeys.Values)}') AND CUSTOMER='{cCustomer}' AND PARTNER='{cPartner}'");
DBConnect connectAdmin = ConnectionsMgr.GetAdminConnection();
InsertMultiple(user, connectAdmin, _Table.ReportReq, Common.colReportReq, invRpts);
connectAdmin.Close();
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(CreateInvAsn), e.Message);
}
return(sentKeys);
}
private static List <LocReportLine> GetLocReportData(User user, string sPartner, string condH856)
{
List <LocReportLine> retList = new List <LocReportLine>();
string queryCatalog = "SELECT st.ponumber AS milpo," +
"st.vendornum AS pidstyle," +
"st.upcnum AS upc," +
"st.milcost AS milcost," +
"cast(SUM(st.ttlmilcost) AS decimal(14,2)) AS ttlmilcost," +
"st.retail AS retail," +
"cast(SUM(st.ttlretailprc) AS decimal(14,2)) AS ttlretail," +
"st.storenum AS store," +
"st.shipdate AS shipdate," +
"round(SUM(st.onorder)) AS onorder," +
"round(SUM(st.shipped)) AS shipped " +
"FROM" +
"(SELECT d856.ponumber AS ponumber," +
"d856.vendornum AS vendornum," +
"d856.upcnum AS upcnum," +
"s850.unitprice AS milcost," +
"s850.unitprice*IFNULL(c856.ctnqty,d856.shpunits) AS ttlmilcost," +
"s850.retailprc AS retail," +
"s850.retailprc*IFNULL(c856.ctnqty,d856.shpunits) AS ttlretailprc," +
"IFNULL(c856.byid,d856.byid) AS storenum," +
"h856.shipdate AS shipdate," +
"IFNULL(b850.ctnqty,d856.ordunits) AS onorder," +
"IFNULL(c856.ctnqty,d856.shpunits) AS shipped " +
"FROM" +
"(SELECT uniquekey," +
"shipdate " +
"FROM trxh856 " +
"WHERE " + condH856 +
") AS h856" +
" JOIN trxd856 d856 USING (uniquekey)" +
" LEFT JOIN trxc856 c856 USING (uniquekey," +
"uniqueitem)" +
" LEFT JOIN srcd850 s850 ON d856.uniquepo=s850.uniquekey" +
" AND d856.uniqpoitem=s850.uniqueitem" +
" LEFT JOIN srcb850 b850 ON s850.uniquekey=b850.uniquekey" +
" AND b850.uniqueitem=s850.uniqueitem" +
" AND b850.byid=c856.byid ) AS st" +
" GROUP BY ponumber," +
"vendornum," +
"upcnum," +
"store," +
"shipdate";
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, _Database.ECGB);
using (var queryRpt = connection.Query(queryCatalog))
{
while (queryRpt.Read())
{
retList.Add(new LocReportLine(queryRpt, sPartner));
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetLocReportData), e);
}
return(retList);
}
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("");
}
}
