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