/// <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 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 object[,] CopyResultDataToArray(DBResult resultReport)
{
int colCount = resultReport.FieldCount;
int rowCount = resultReport.AffectedRows;
object[,] reportData = new object[rowCount, colCount];
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
resultReport.Read();
for (int columnIndex = 0; columnIndex < colCount; columnIndex++)
{
reportData[rowIndex, columnIndex] = resultReport.Field2(columnIndex, "'---");
}
}
return(reportData);
}
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);
}