private static string GetFilterItem(User user, string sHeadUniqueKey850, Dictionary <string, string> sVals860)
{
List <string> cond = new List <string>();
if (sVals860.ContainsKey(_Column.UPCNum))
{
cond.Add(string.Format("{0}='{1}'", _Column.UPCNum, sVals860[_Column.UPCNum]));
}
if (sVals860.ContainsKey(_Column.BuyerNum))
{
cond.Add(string.Format("{0}='{1}'", _Column.BuyerNum, sVals860[_Column.BuyerNum]));
}
if (sVals860.ContainsKey(_Column.VendorNum))
{
cond.Add(string.Format("{0}='{1}'", _Column.VendorNum, sVals860[_Column.VendorNum]));
}
if (cond.Count == 0)
{
ProgramLog.LogError(user, "ChangePOTracker", "GetFilterItem", string.Format("Item under POC key {0} could not be found in original PO by upc/buyer/vendor.", sHeadUniqueKey850));
return("");
}
else
{
return(string.Format("WHERE {0}='{1}' AND ({2})", _Column.UniqueKey, sHeadUniqueKey850, string.Join(" OR ", cond)));
}
}
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 ResponseType VerifyFile(User user, string xlFileName)
{
Application app = null;
Workbook wb = null;
Worksheet xlWS = null;
try
{
app = new Application();
app.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
wb = app.Workbooks.Open(Filename: xlFileName, ReadOnly: true);
if (wb.Worksheets.Count == 0)
{
wb.Close();
return(ResponseType.ErrorAPOUnknown);
}
xlWS = wb.Worksheets[1];
ResponseType result = CheckSheet(user, xlWS);
return(result);
}
catch (Exception e)
{
ProgramLog.LogError(user, "POAcknowledgeManager", "VerifyFile", e.Message);
return(ResponseType.ErrorAPOUnknown);
}
finally
{
if (app != null && wb != null)
{
wb.Close();
}
}
}
private static ResponseType PriceChange(DBConnect connection, User user, string sUnitPrice, string sFilterLineItem)
{
try
{
var sUpdateVals = new Dictionary <string, string>()
{
{ _Column.UnitPrice, sUnitPrice.SQLEscape() }
}.ToNameValueCollection();
var result = connection.Update(_Table.Detail850All, sUpdateVals, sFilterLineItem);
if (result.AffectedRows > 0)
{
return(ResponseType.SuccessCPO);
}
else
{
return(ResponseType.ErrorCPOCouldNotApplyItemChange);
}
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "PriceChange", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
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 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>());
}
}
/// <summary>
/// Begin a session. Must be called whenever a page loads.
/// </summary>
/// <param name="usUserName">The username to initialize a session for.</param>
/// <param name="request">The request from the user's browser.</param>
/// <returns>True if the session was initialized successfully, false otherwise.</returns>
public static bool BeginSession(string usUserName, HttpRequest request, OCUserInfo userInfo = null)
{
try
{
// Check existence of session
HttpSessionState session = HttpContext.Current.Session;
if (session == null)
{
return(false);
}
User user = new User();
bool isTest = false;
//CHECK DEVELOPMENT/TEST ENVIRONMENT
string hostname = request.Url.Authority;
if (hostname == "10.0.0.245:30658")
{
isTest = true;
}
#if DEBUG
isTest = true;
#endif
if (userInfo != null)
{
isTest = userInfo.IsTest;
}
if (!GetUserInfo(user, usUserName, isTest))
{
return(false);
}
if (userInfo != null)
{
for (int i = 0; i < user.PartnerList.Count; i++)
{
if (user.PartnerList[i].ID == userInfo.ActivePartner)
{
user.PartnerIndex = i;
break;
}
}
}
session[SKeys.User] = user;
session[SKeys.IsTest] = isTest;
session[SKeys.LandingPg] = "Default.aspx";
session[SKeys.TokenSet] = new HashSet <string>();
session[SKeys.TrxDict] = ProcessQueue.GetReqTypeDict(user);
session[SKeys.IsOCSession] = userInfo != null;
return(true);
}
catch (Exception e)
{
ProgramLog.LogError(usUserName, "EDIO", "EDIO", "SessionHandler", "BeginSession", e.Message);
return(false);
}
}
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 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);
}
}
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>());
}
}
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);
}
}
public static bool SetConnIDs(User user, bool isTest)
{
if (isTest)
{
// Use default connections when debugging
user.OCConnID = idTestOC;
user.NPConnID = idTestNP;
user.SHConnID = idTestSH; // Use OC ID
user.SLConnID = idTestSL; // Use OC ID
return(true);
}
DBConnect connection = new DBConnect();
if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo()))
{
return(false);
}
try
{
using (var res = connection.Select(new[] { columnOCConnID, columnNPConnID, columnSHConnID, columnSLConnID }, tableConnectionIDInfo, string.Format("WHERE {0}='{1}'", columnCustomer, user.Customer)))
{
if (!res.Read())
{
// No info for customer?
ProgramLog.LogError(user.UserName, user.Customer, "EDIO", "ConnectionsMgr", "SetConnIDs", string.Format("Unable to find connection info in {0} for customer {1}", tableConnectionIDInfo, user.Customer));
connection.Close();
return(false);
}
user.OCConnID = res.Field(0);
user.NPConnID = res.Field(1);
user.SHConnID = res.Field(2);
user.SLConnID = res.Field(3);
}
connection.Close();
return(true);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ConnectionsMgr", "SetConnIDs", e.Message);
connection.Close();
return(false);
}
}
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 string GetVendId(User user, DBConnect connection)
{
try
{
using (var query = connection.Select(VendID, $"{Home}.{NetGroup}", $"WHERE {Customer}='{user.Customer.SQLEscape()}' AND {Partner}='{user.ActivePartner.SQLEscape()}'"))
{
if (query.Read())
{
return(query.Field(0));
}
}
return("");
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetVendId), e.Message);
return("");
}
}
private static void InsertMultiple(User user, DBConnect connection, string table, List <string> columns, List <List <string> > records)
{
try
{
int blockIndex = 0;
while (true)
{
var currentBlock = records.TakeBlock(1000, ref blockIndex);
if (currentBlock.Count() <= 0)
{
break;
}
connection.InsertMulti(table, columns.ToSqlColumnList(), currentBlock.ToList());
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(InsertMultiple), e.Message);
}
}
private static Dictionary <string, string> GetTrxTypeDict(User user, DBConnect connection, IEnumerable <string> keyList)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
try
{
using (var queryRes = connection.Query($"SELECT u,IF(c>1,'D','M')FROM(SELECT uniquekey u,COUNT(*)c FROM srch850 h LEFT JOIN srcb850 b using(uniquekey)WHERE uniquekey IN {keyList.ToSqlValueList()} GROUP BY h.uniquekey)x;"))
{
while (queryRes.Read())
{
ret.Add(queryRes.Field(0), queryRes.Field(1));
}
}
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetTrxTypeDict), e.Message);
}
return(ret);
}
private static DatabaseInfo _GetConnInfo(User user, string connID, string database = "")
{
DatabaseInfo dbInfo = new DatabaseInfo();
DBConnect connection = new DBConnect();
if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo()))
{
ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", "Unable to connect to admin database.");
return(dbInfo);
}
using (var res = connection.Select(new[] { columnServer, columnPort, columnIsTest }, tableAllPorts, string.Format("WHERE {0}='{1}'", columnConnectID, connID.SQLEscape())))
{
if (!res.Read())
{
ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", string.Format("Unable to find info in table \"{0}\" for connection ID \"{1}\".", tableConnectionIDInfo, connID.SQLEscape()));
connection.Close();
return(dbInfo);
}
dbInfo.Server = res.Field(0);
dbInfo.Port = (int)res.Field2(1);
dbInfo.IsTest = (int)res.Field2(2) != 0;
}
connection.Close();
dbInfo.Id = connID;
dbInfo.Database = string.IsNullOrWhiteSpace(database) ? databaseHome : database;
dbInfo.Driver = _DefaultDriver;
if (cred.ContainsKey(dbInfo.Id))
{
dbInfo.Username = cred[dbInfo.Id].UserName;
dbInfo.Password = cred[dbInfo.Id].Password;
}
else
{
dbInfo.Username = defaultCred.UserName;
dbInfo.Password = defaultCred.Password;
}
return(dbInfo);
}
private static PdsOptions _GetOptions(User user, DBConnect connection)
{
try
{
PdsOptions opt = new PdsOptions();
using (var queryPL = connection.Select(PdsOptions.Columns, Home + "." + NetGroup, $"WHERE {Customer}='{user.Customer.SQLEscape()}' AND {Partner}='{user.ActivePartner.SQLEscape()}'"))
{
if (queryPL.Read())
{
opt = new PdsOptions(queryPL);
}
}
return(opt);
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(_GetOptions), e.Message);
return(new PdsOptions());
}
}
private static ResponseType CancelPO(DBConnect connection, User user, string sPONumber)
{
try
{
string filterPONumber = string.Format("WHERE {0}='{1}' AND {2}='{3}'", _Column.Partner, user.ActivePartner.SQLEscape(), _Column.PONumber, sPONumber);
var updateVals = new Dictionary <string, string>()
{
{ _Column.CancelDate, DateTime.Now.ToString("yyyy-MM-dd") }
}.ToNameValueCollection();
connection.Update(_Table.Head850All, updateVals, filterPONumber);
return(ResponseType.SuccessCPO);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "CancelPO", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
private static ResponseType DeleteItem(DBConnect connection, User user, string sFilterLineItem)
{
try
{
var result = connection.Delete(_Table.Detail850All, sFilterLineItem);
if (result.AffectedRows > 0)
{
return(ResponseType.SuccessCPO);
}
else
{
return(ResponseType.ErrorCPOCouldNotApplyItemChange);
}
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "DeleteItem", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
private static ResponseType ReplaceAllValues(DBConnect connection, User user, Dictionary <string, string> sVals860, string sFilterLineItem)
{
try
{
var result = connection.Update(_Table.Detail850All, GetUpdate850Vals(sVals860).ToNameValueCollection(), sFilterLineItem);
if (result.AffectedRows > 0)
{
return(ResponseType.SuccessCPO);
}
else
{
return(ResponseType.ErrorCPOCouldNotApplyItemChange);
}
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "ReplaceAllValues", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
public static ResponseType PerformAction(User user, CPOAction action)
{
try
{
switch (action.Action)
{
case ActionType.Cancel:
return(Cancel860(user, action.Key.SQLEscape()));
case ActionType.Apply:
return(Apply860(user, action.Key.SQLEscape()));
default:
return(ResponseType.ErrorCPOUnknown);
}
}
catch (Exception e)
{
ProgramLog.LogError(user, "ChangePOTracker", "PerformAction", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
public static string EncryptFileToFile(User user, string iFileName)
{
try
{
if (!File.Exists(iFileName))
{
return("");
}
byte[] iv = new byte[IVSizeBytes];
byte[] key = new byte[KeySizeBytes];
using (RNGCryptoServiceProvider rcsp = new RNGCryptoServiceProvider())
{
rcsp.GetBytes(iv);
rcsp.GetBytes(key);
}
string ivAsString = ByteArrayToString(iv);
string keyAsString = ByteArrayToString(key);
string oFileName = Path.Combine(new FileInfo(iFileName).Directory.FullName, ivAsString);
using (AesCryptoServiceProvider aesProvider = new AesCryptoServiceProvider())
{
aesProvider.KeySize = KeySizeBits;
aesProvider.IV = iv;
aesProvider.Key = key;
using (FileStream diStream = File.OpenRead(iFileName))
using (FileStream eoStream = File.Create(oFileName))
using (CryptoStream cryptoStream = new CryptoStream(eoStream, aesProvider.CreateEncryptor(), CryptoStreamMode.Write))
{
diStream.CopyTo(cryptoStream);
}
}
return(keyAsString + ivAsString);
}
catch (Exception e)
{
ProgramLog.LogError(user, "Crypt", "EncryptFileToFile", e.Message);
return("");
}
}
