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 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 void SendRequest(User user, ReportRequestData ord)
{
string storesConcat = "";
if (ord.Stores.Count > 0)
{
storesConcat = string.Format("STORE IN ({1})", _Column.STId, string.Join(",", from store in ord.Stores select "'" + store.SQLEscape() + "'"));
}
string retailConcat = string.Join("; ", from week in ord.Weeks select week.ToSQL());
var genDate = DateTime.Now;
try
{
var connection = ConnectionsMgr.GetAdminConnection();
{
var vals = new Dictionary <string, string>()
{
{ _Column.UniqueKey, connection.GetNewKey() },
{ _Column.RequestDate, genDate.ToMySQLDateTimeStr() },
{ _Column.Customer, user.Customer.SQLEscape() },
{ _Column.ConnectID, user.OCConnID.SQLEscape() },
{ _Column.OutputName, string.Format("{0}_{1}_{2}.xlsx", user.Customer.SQLEscape(), 852, genDate.ToString("yyyyMMddHHmmssF")) },
{ _Column.OutputPath, string.Format(@"\ecgb\data\networks\{0}\outbox", user.OCConnID.SQLEscape()) },
{ _Column.Filter, string.Format("{0}|{1}|{2}|{3}", ord.RequestType, storesConcat, retailConcat, user.ActivePartner.SQLEscape()) },
{ _Column.Processed, _ProgressFlag.Unprocessed },
{ _Column.ToEmail, ord.Email.SQLEscape() },
{ _Column.OptCenterUpload, "1" }
};
connection.Insert(_Table.SalesRequest, vals.ToNameValueCollection());
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, "SalesRequestManager", "GetStoreList", e.Message);
}
}
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);
}