private static Dictionary <string, ItemInfo> GetItemInfoLookup(User user, string sPartner, string dbSales, IEnumerable <string> vendList, IEnumerable <string> upcList)
{
Dictionary <string, ItemInfo> itemDict = new Dictionary <string, ItemInfo>();
try
{
DBConnect connection = ConnectionsMgr.GetSalesConnection(user, dbSales);
string colVendNum = sPartner == _Partner.Thalia ?
$"SUBSTR(TRIM({VendorNum}),1,11)" :
$"TRIM({VendorNum})";
using (var queryItemInfo = connection.Select("vendornum,upcnum,prodcat,deptname,prodsubcat,classdesc,subcdesc,itemdesc,colorcode,itemcolor", _Table.Sl_MasterCat, $"WHERE {colVendNum} IN {vendList.ToSqlValueList()} AND TRIM({UPCNum}) IN {upcList.ToSqlValueList()} group by vendornum,upcnum"))
{
while (queryItemInfo.Read())
{
string key = queryItemInfo.FieldByName(VendorNum).Trim() + queryItemInfo.FieldByName(UPCNum).Trim();
if (!itemDict.ContainsKey(key))
{
itemDict.Add(key, new ItemInfo(queryItemInfo));
}
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetItemInfoLookup), e);
}
return(itemDict);
}
public static List <ProcessRecord> GetPreviousRecords(User user)
{
List <ProcessRecord> listRecord = new List <ProcessRecord>();
DBConnect connection = new DBConnect();
try
{
var filter = string.Format("WHERE {0}='{1}' AND {2}='{3}' AND {4}>'{5}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape(), _Column.PReqDate, DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss"));
connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
using (var query = connection.Select(new[] { _Column.PReqDate, _Column.PReqType, _Column.Processed }, _Table.ProcessQ, filter))
{
while (query.Read())
{
ProcessRecord pr = new ProcessRecord();
pr.Date = query.Field(0, "");
pr.Type = query.Field(1, "");
pr.Status = query.Field(2, "");
listRecord.Add(pr);
}
}
connection.Close();
return(listRecord);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "GetPreviousRecords", e.Message);
connection.Close();
return(new List <ProcessRecord>());
}
}
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);
}
/// <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 const int OCSessionTimeOutSeconds = 60; //10 TIMED OUT OFTEN (SENDING TO 404 PAGE), TRYING 30 SECONDS. CHANGED TO 60 SECS BECAUSE OF TEMPORARY TIME DIFF BETWEEN THE TWO SERVERS
/// <summary>
/// Authenticates the given user.
/// </summary>
/// <param name="usUserName">The username to check.</param>
/// <param name="usPassword">The password to check.</param>
/// <returns>True if the login info is valid, false otherwise.</returns>
public static bool AuthenticateUser(string usUserName, string usPassword)
{
string sUserName = usUserName.SQLEscape();
string sStoredHash = "";
DBConnect connect = new DBConnect();
try
{
connect.Connect(ConnectionsMgr.GetAuthConnInfo());
using (var queryUserAuthInfo = connect.Select(columnPassword, _Table.Users, string.Format("WHERE {0}='{1}'", columnUserName, sUserName)))
{
if (queryUserAuthInfo.AffectedRows <= 0)
{
connect.Close();
return(false);
}
queryUserAuthInfo.Read();
sStoredHash = Encoding.UTF8.GetString((byte[])queryUserAuthInfo.Field2(0));
}
connect.Close();
return(MD5Crypt.Verify(usPassword, sStoredHash));
}
catch (Exception ex)
{
ProgramLog.LogError(null, "Auth", "AuthenticateUser", ex.Message + " " + ex.StackTrace);
connect.Close();
return(false);
}
}
public static bool CreateUploadRecord(User user, DateTime reqDate, string reqType, string fileName)
{
// uniquekey, preqdate, customer, partner, preqtype, preqaction, pfilename, pcustomfile, presultfile, presultdate, processed
DBConnect connection = new DBConnect();
bool success = false;
try
{
connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
var insertVals = new Dictionary <string, string>()
{
{ _Column.UniqueKey, connection.GetNewKey() },
{ _Column.PReqDate, reqDate.ToString("yyyy-MM-dd HH:mm:ss") },
{ _Column.Customer, user.Customer.SQLEscape() },
{ _Column.PReqType, reqType.SQLEscape() },
{ _Column.PFileName, fileName.SQLEscape() },
{ _Column.PCustomFile, "1" },
{ _Column.Partner, user.ActivePartner.SQLEscape() }
};
using (var res = connection.Insert(_Table.ProcessQ, insertVals.ToNameValueCollection()))
{
success = res.AffectedRows != 0;
}
connection.Close();
return(success);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "CreateUploadRecord", e.Message);
return(false);
}
}
public static List <SalesRequestDetail> GetPastOrders(User user)
{
List <SalesRequestDetail> pastRequests = new List <SalesRequestDetail>();
try
{
var connection = ConnectionsMgr.GetAdminConnection();
{
var request = connection.Select(new[] { _Column.RequestDate, _Column.OutputName, _Column.ToEmail, _Column.Processed }, _Table.SalesRequest, string.Format("WHERE {0}='{1}' AND {2}='{3}' ORDER BY reqdate DESC LIMIT 10", _Column.Customer, user.Customer.SQLEscape(), _Column.ConnectID, user.OCConnID.SQLEscape()));
while (request.Read())
{
pastRequests.Add(new SalesRequestDetail(
((DateTime)request.Field2(0)).ToString("MMM dd, yyyy"),
request.Field(1, ""),
request.Field(2, ""),
request.Field(3, "").ToUpper() == "Y" ? "Processed" : "Unprocessed"
));
}
}
connection.Close();
return(pastRequests);
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "GetPastOrders", e.Message);
return(new List <SalesRequestDetail>());
}
}
public static Dictionary <string, string> GetAllowedApps(User user)
{
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
Dictionary <string, string> ret = new Dictionary <string, string>();
using (var queryUserLevelAP = connection.Select(appFlags, _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())))
{
while (queryUserLevelAP.Read())
{
for (int i = 0; i < appFlags.Length; i++)
{
string f = queryUserLevelAP.Field(i);
if (f == "1")
{
string name = columnToDetail[appFlags[i]].appName;
string url = columnToDetail[appFlags[i]].appURL;
if (!ret.ContainsKey(name))
{
ret.Add(name, url);
}
}
}
}
if (ret.Count > 0)
{
return(ret);
}
}
using (var queryCustPartAP = connection.Select(appFlags, _Table.NetGroup, string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape())))
{
while (queryCustPartAP.Read())
{
for (int i = 0; i < appFlags.Length; i++)
{
string f = queryCustPartAP.Field(i);
if (f == "1")
{
string name = columnToDetail[appFlags[i]].appName;
string url = columnToDetail[appFlags[i]].appURL;
if (!ret.ContainsKey(name))
{
ret.Add(name, url);
}
}
}
}
return(ret);
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(AppManagement), nameof(GetAllowedApps), e.Message);
return(new Dictionary <string, string>());
}
}
/// <summary>
/// Gets the current list of brand names.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <string> GetBrandNameList(User user)
{
List <string> bnList = new List <string>();
try
{
string sCustomer = user.Customer.SQLEscape().ToLower();
DBConnect connection = ConnectionsMgr.GetSalesConnection(user, "sales_" + sCustomer);
{
var query = connection.Query("SELECT brand FROM sl_filters GROUP BY brand ORDER BY brand");
while (query.Read())
{
var bn = query.Field(0, "").Trim();
if (bn != "")
{
bnList.Add(bn);
}
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetBrandNameList), e);
}
return(bnList);
}
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 Dictionary <string, string> GetReqTypeDict(User user)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetAdminConnInfo());
using (var query = connection.Select(new[] { _Column.TrxType, _Column.TrxDesc }, _Table.TrxInfo))
{
while (query.Read())
{
ret.Add(query.Field(0), query.Field(1));
}
}
connection.Close();
ret.Add("832_A", "Item Attributes");
ret.Add("850_X", "PO Store X-Ref");
ret.Add("856_X", "ASN Release X-Ref");
ret.Add("832_P", "Al Tayer Item Attributes");
return(ret);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "GetReqTypeDict", e.Message);
connection.Close();
return(new Dictionary <string, string>());
}
}
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);
}
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 bool Submit(HttpRequest request, ReportDetail req, string usUserName, string usCustomer, string usPartner)
{
if (request == null || req == null)
{
return(false);
}
DBConnect connectionAdmin = new DBConnect();
try
{
connectionAdmin.Connect(new DatabaseInfo(ConnectionsMgr.GetAdminConnInfo())
{
Database = databaseEmailRequest
});
string defaultToEmail = "*****@*****.**";
string defaultFromEmail = "*****@*****.**";
StringBuilder builtMessage = new StringBuilder();
builtMessage.AppendLine("From: " + req.Name);
builtMessage.AppendLine("Company: " + req.Company);
builtMessage.AppendLine("Email: " + req.Email);
builtMessage.AppendLine("Message: " + req.Message);
builtMessage.AppendLine();
builtMessage.AppendLine("Additional Info");
builtMessage.AppendLine("IP Address: " + request.UserHostAddress);
builtMessage.AppendLine("Browser Info: " + request.UserAgent);
builtMessage.AppendLine("Referral: " + request.UrlReferrer.ToString());
DateTime requestTime = DateTime.Now;
var vals = new Dictionary <string, string>()
{
{ columnUniqueKey, connectionAdmin.GetNewKey() },
{ columnCustomer, (usCustomer ?? "").SQLEscape() },
{ columnPartner, (usPartner ?? "").SQLEscape() },
{ columnRequestDate, requestTime.ToString("yyyy-MM-dd HH:mm:ss") },
{ columnToEmail, defaultToEmail },
{ columnFromEmail, defaultFromEmail },
{ columnSubject, "EDIOC- Support Submission" },
{ columnMessage, WrapTextTo70(builtMessage.ToString().SQLEscape()) },
#if DEBUG
{ columnProcessed, "Y" },
#else
{ columnProcessed, "" },
#endif
{ columnSendAfter, requestTime.ToString("yyyy-MM-dd HH:mm:ss") }
};
var result = connectionAdmin.Insert(tableEmailReq, vals.ToNameValueCollection());
return(result.AffectedRows > 0);
}
catch (Exception e)
{
ProgramLog.LogError(usUserName, usCustomer, usPartner, "SupportRequest", "Submit", e.Message);
return(false);
}
}
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);
}
}
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 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 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 List <CPOSummaryHead> GetChangeList(User user)
{
List <CPOSummaryHead> ret = new List <CPOSummaryHead>();
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
var queryCPOHead = connection.Select(new[] { _Column.UniqueKey, _Column.PONumber, _Column.POChangeDate, _Column.Purpose, _Column.TotalItems, _Column.HProcessed }, _Table.Head860, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND ({4}='{5}' OR {4}='{6}')", _Column.Customer, user.Customer, _Column.Partner, user.ActivePartner, _Column.HProcessed, _ProgressFlag.Unprocessed, _ProgressFlag.Error));
while (queryCPOHead.Read())
{
CPOSummaryHead newHead = new CPOSummaryHead();
newHead.UniqueKey = queryCPOHead.Field(0, "");
newHead.PONumber = queryCPOHead.Field(1, "");
newHead.POChangeDate = queryCPOHead.Field(2, "");
newHead.Purpose = ElementLookup.GetDesc(user, _Element.Purpose, queryCPOHead.Field(3, ""));
newHead.Affected = queryCPOHead.Field(4, "");
newHead.Status = queryCPOHead.Field(5, "");
newHead.Details = new List <CPOSummaryDetail>();
var queryCPODetail = connection.Select(new[] { _Column.ChangeType, _Column.Quantity, _Column.ChangeQuantity, _Column.UnitPrice, _Column.RetailPrice, _Column.UPCNum, _Column.VendorNum, _Column.ItemDesc, _Column.PackSize, _Column.Dropship, _Column.Processed }, _Table.Detail860, string.Format("WHERE {0}='{1}'", _Column.UniqueKey, newHead.UniqueKey));
while (queryCPODetail.Read())
{
CPOSummaryDetail newDetail = new CPOSummaryDetail();
newDetail.ChangeType = ElementLookup.GetDesc(user, _Element.ChangeType, queryCPODetail.Field(0, ""));
newDetail.Quantity = decimal.Parse(queryCPODetail.Field(1, "0")).ToString("N0");
newDetail.ChangeQuantity = decimal.Parse(queryCPODetail.Field(2, "0")).ToString("N0");
newDetail.UnitPrice = queryCPODetail.Field(3, "");
newDetail.RetailPrc = queryCPODetail.Field(4, "");
newDetail.UPC = queryCPODetail.Field(5, "");
newDetail.VendorNum = queryCPODetail.Field(6, "");
newDetail.ItemDesc = queryCPODetail.Field(7, "");
newDetail.PackSize = queryCPODetail.Field(8, "");
newDetail.Dropship = queryCPODetail.Field(9, "").Replace('\r', ' ');
newDetail.Status = queryCPODetail.Field(10, "");
newHead.Details.Add(newDetail);
}
ret.Add(newHead);
}
return(ret.OrderBy(h => h.POChangeDate).ThenBy(h => h.PONumber).ThenBy(h => h.Purpose).ToList());
}
catch (Exception e)
{
connection.Close();
ProgramLog.LogError(user, "ChangePOTracker", "GetChangeList", e.Message);
return(new List <CPOSummaryHead>());
}
}
public static OCUserInfo GetOCRecord(string usToken)
{
OCUserInfo info = new OCUserInfo();
if (string.IsNullOrEmpty(usToken))
{
return(info);
}
string sToken = usToken.SQLEscape();
DBConnect connection = new DBConnect();
DateTime expTime = new DateTime();
try
{
connection.Connect(ConnectionsMgr.GetAdminConnInfo());
using (var queryUserAuthInfo = connection.Select(new[] { columnUserName, columnActivePartner, columnIsTest, columnCreateDate }, tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken)))
{
if (queryUserAuthInfo.AffectedRows <= 0)
{
connection.Close();
return(info);
}
queryUserAuthInfo.Read();
info.UserName = queryUserAuthInfo.Field(0);
info.ActivePartner = queryUserAuthInfo.Field(1);
info.IsTest = queryUserAuthInfo.Field(2) == "1";
expTime = (DateTime)queryUserAuthInfo.Field2(3, DateTime.MinValue);
connection.Delete(tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken));
}
connection.Close();
var authLimit = DateTime.Now.AddSeconds(-OCSessionTimeOutSeconds);
if (expTime >= authLimit) //expire date must be within the last OCSessionTimeOutSeconds seconds
{
info.IsValid = true;
}
else
{
throw new Exception("Authentication time is not within range allowed. Auth Time: " + expTime.ToString("yyyy-MM-dd HH:mm:ss") + ", Auth Limit: " + authLimit.ToString("yyyy-MM-dd HH:mm:ss"));
}
return(info);
}
catch (Exception ex)
{
ProgramLog.LogError(null, "Auth", "GetOCRecord", ex.Message);
connection.Close();
return(info);
}
}
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);
}
}
private static ResponseType Cancel860(User user, string sHeadUniqueKey860)
{
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
UpdateAll860ProcFlags(connection, sHeadUniqueKey860, _ProgressFlag.Canceled);
connection.Close();
return(ResponseType.SuccessCPO);
}
catch (Exception e)
{
connection.Close();
ProgramLog.LogError(user, "ChangePOTracker", "Cancel860", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
protected void SetDevelopmentInfo(User user)
{
DatabaseInfo di = null;
string sFormat = "{1} ({0})";
List <string> conns = new List <string>();
di = ConnectionsMgr.GetOCConnInfo(user);
conns.Add(string.Format(sFormat, di.Port, di.Id));
di = ConnectionsMgr.GetNPConnInfo(user);
conns.Add(string.Format(sFormat, di.Port, di.Id));
di = ConnectionsMgr.GetSHConnInfo(user);
conns.Add(string.Format(sFormat, di.Port, di.Id));
di = ConnectionsMgr.GetSLConnInfo(user);
conns.Add(string.Format(sFormat, di.Port, di.Id));
lblDeNotice.Text = string.Format("Development Environment: {0}", string.Join(", ", conns));
divDeNotice.Visible = true;
}
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);
}
private static void _Log(string username, string customer, string partner, string source, string method, string type, string message)
{
if (customer == "")
{
customer = "EDIO";
}
if (partner == "")
{
partner = "EDIO";
}
string sCustomer = TruncateAndEscape(customer, 4);
string sPartner = TruncateAndEscape(partner, 4);
string sUsername = TruncateAndEscape(username, 40);
string sSource = TruncateAndEscape(source, 32);
string sMethod = TruncateAndEscape(method, 32);
string sMessage = (string.IsNullOrWhiteSpace(message) ? "" : message).SQLEscape();
bool isTest = false;
#if DEBUG
isTest = true;
#endif
var connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetAdminConnInfo());
Dictionary <string, string> insertVals = new Dictionary <string, string>()
{
{ _Column.UniqueKey, connection.GetNewKey() },
{ columnLogTime, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") },
{ columnSource, sSource },
{ columnMethod, sMethod },
{ columnStatus, type },
{ columnMessage, sMessage },
{ _Column.UserName, sUsername },
{ _Column.Customer, sCustomer },
{ _Column.Partner, sPartner },
{ columnIsTest, isTest ? "1" : "0" }
};
connection.Insert(tableLog, insertVals.ToNameValueCollection());
}
catch { }
connection.Close();
}
private static string _GetElemDesc(User user, string usEleNum, string usCode)
{
DBConnect connect = new DBConnect();
try
{
connect.Connect(ConnectionsMgr.GetAdminConnInfo());
var queryCodes = connect.Select(_Column.CodeDesc, _Table.DisaCode, string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.EleNum, usEleNum.SQLEscape(), _Column.Code, usCode.SQLEscape()));
if (queryCodes.Read())
{
return(queryCodes.Field(0));
}
else
{
return("");
}
}
catch (Exception e)
{
ProgramLog.LogError(user, "ElementLookup", "_GetElemDesc", e.Message);
return("");
}
}
