/// <summary>
/// Default KPA Constructor
/// </summary>
public KPA()
{
plan = new Plan();
purch = new Purch();
purchSub = new Purch_Sub();
purchTotal = new Purch_Total();
purchPlanTotal = new Purch_Plan_Total();
followUp = new Follow_Up();
cancellations = new Cancellations();
ncrs = new NCRs();
hotJobs = new Hot_Jobs();
excessStockStock = new Excess_Stock_Stock();
excessStockOpenOrders = new Excess_Stock_Open_Order();
currPlanVsActual = new Current_Plan_vs_Actual();
mtc = new MTC();
}
public bool Update_MtnCol_Issued()
{
CurrentFunction = "Update_MtnCol_Issued";
//load salesdata with newest salesdate first
statusStrip1.Items[0].Text = "Loading Mountain Collective Usage";
SetTaskStatus("", "", pbMTCUpload);
string CurDate = DateTime.Now.AddDays(-2).ToString(Mirror.AxessDateFormat);
string Tomorrow = DateTime.Now.AddDays(1).ToString(Mirror.AxessDateFormat);
DataSet tDS = CF.LoadDataSet(DW.dwConn, $"SELECT serialkey, nticktype, szprompt1, NUNICODENR, ttype, saledate, nkassanr, nseriennr, mediaid FROM {DW.ActiveDatabase}.salesdata WHERE dtupdate >= '{CurDate}' AND dtupdate<'{Tomorrow}' AND SZPROMPT1 LIKE 'MC%' AND tgroup LIKE 'MC%' AND nkassanr<60 AND nticktype IN (2025, 170, 171, 172)", new DataSet(), "salesday");
if (tDS.Tables.Contains("salesday"))
{
PbMTCUsage.Maximum = tDS.Tables["salesday"].Rows.Count;
//loop through salesdata rows
foreach (DataRow SalesDataRow in tDS.Tables["salesday"].Rows)
{
statusStrip1.Items[0].Text = $"Uploading records to MTC {tDS.Tables["salesday"].Rows.IndexOf(SalesDataRow)} of {tDS.Tables["salesday"].Rows.Count}";
SetTaskStatus("", "", pbMTCUpload);
string mkey = SalesDataRow["serialkey"].ToString();
//load all mtncol_issued rows for this serialkey
string mcclink = SalesDataRow["nticktype"].ToString() != "2025" ? "N" : "Y";
string szprompt1 = SalesDataRow["szprompt1"].ToString().ToUpper();
//int muses = 1;
////if serialkey is in SPCards
//if (CF.RowExists(DW.dwConn, $"{DW.ActiveDatabase}.spcards", $"tgroup LIKE 'MC%' AND serialkey='{mkey}'"))
//{
// //get uses from SPCards (if nticktype=30036 uses = freedays else 1)
// if (SalesDataRow["nticktype"].ToString() == "2025")
// {
// muses = Convert.ToInt16(CF.GetSQLField(DW.dwConn, $"SELECT freedays FROM {DW.ActiveDatabase}.spcards WHERE tgroup LIKE 'MC%' AND serialkey='{mkey}'"));
// }
//}
bool wasUpdate = false;
tDS = CF.LoadDataSet(BUY.Buy_Alta_ComConn, $"SELECT mcpnbr, tied_to_cc, mediaid FROM asbshared.mtncol_issued WHERE serialkey='{mkey}'", tDS, "asbissued");
if (tDS.Tables.Contains("asbissued"))
{
//if mtncol_issued rows exist for this serialkey
if (tDS.Tables["asbissued"].Rows.Count != 0)
{
wasUpdate = true;
string mcpnbr = tDS.Tables["asbissued"].Rows[0].Field <string>("mcpnbr");
//if mcpnbr, cclink or uses do not match
if ((mcpnbr.Length == 0 && szprompt1.Length != 0) || (szprompt1 != mcpnbr) || (tDS.Tables["asbissued"].Rows[0].Field <string>("tied_to_cc") != mcclink) ||
(tDS.Tables["asbissued"].Rows[0].Field <string>("mediaid") != SalesDataRow["mediaid"].ToString()))
{
//set these values in mtncol_issued to match salesdata values
CF.ExecuteSQL(BUY.Buy_Alta_ComConn, $"UPDATE asbshared.mtncol_issued SET mcpnbr='{szprompt1}', tied_to_cc='{mcclink}', mediaid='{SalesDataRow["mediaid"].ToString()}' WHERE serialkey = '{mkey} '");
}
}
}
// if not updated (no rows existed in mtncol_issued then insert
//if (!wasUpdate)
//{
// CF.ExecuteSQL(BUY.Buy_Alta_ComConn, $"INSERT asbshared.mtncol_issued (mcpnbr,sale_type,issued_date, issued_by, nkassanr,nserialnr,nunicodenr,tied_to_cc,uses,created,serialkey,mediaid) VALUES ('{szprompt1}','{SalesDataRow["ttype"].ToString()}','{Convert.ToDateTime(SalesDataRow["saledate"].ToString()).ToString(Mirror.AxessDateTimeFormat)}','ALTA','{SalesDataRow["nkassanr"].ToString()}','{SalesDataRow["nseriennr"].ToString()}','{SalesDataRow["NUNICODENR"].ToString()}','{mcclink}',0,NOW(),'{SalesDataRow["serialkey"].ToString()}','{SalesDataRow["mediaid"].ToString()}')");
//}
}
tDS.Tables.Remove("salesday");
}
statusStrip1.Items[0].Text = "Loading Mountain Collective Usage";
//SetTaskStatus("", "", pbMTCUpload);
DataTable MTCIssued = CF.LoadTable(BUY.Buy_Alta_ComConn, $"SELECT recid, serialkey, uses FROM asbshared.mtncol_issued WHERE serialkey <> '' ORDER BY recid", "MTCIssued");
if (CF.TableHasData(MTCIssued))
{
pbMTCIssued.Maximum = MTCIssued.Rows.Count;
foreach (DataRow MTC in MTCIssued.Rows)
{
pbMTCIssued.Value = MTCIssued.Rows.IndexOf(MTC) + 1;
long Uses = CF.RowCount(DW.dwConn, $"{DW.ActiveDatabase}.skivisits", $"READDATE >= '2018-11-23' AND serialkey = '{MTC["serialkey"].ToString()}'");
if (Uses != MTC.Field <Int32>("uses"))
{
CF.ExecuteSQL(BUY.Buy_Alta_ComConn, $"UPDATE asbshared.mtncol_issued SET uses={Uses} WHERE recid={MTC["recid"].ToString()}");
}
}
}
//statusStrip1.Items[0].Text = "Setting Mountain Collective Usage";
//CF.ExecuteSQL(BUY.Buy_Alta_ComConn, "UPDATE asbshared.mtncol_issued SET uses = 0");
//using (DataTable MCPVisits = CF.LoadTable(DW.dwConn, $"SELECT mcpnbr, serialkey, COUNT(*) AS Visits FROM {DW.ActiveDatabase}.skivisits WHERE MCPNBR LIKE 'MCP%' AND READDATE > '2018-11-20' GROUP BY serialkey", "MCPVisits"))
//{
// pbMTCIssued.Maximum = MCPVisits.Rows.Count;
// foreach (DataRow MCPVisit in MCPVisits.Rows)
// {
// if (MCPVisit["mcpnbr"].ToString().Trim() == "MCP181934009")
// pbMTCIssued.Maximum = MCPVisits.Rows.Count;
// pbMTCIssued.Value = MCPVisits.Rows.IndexOf(MCPVisit) + 1;
// CF.ExecuteSQL(BUY.Buy_Alta_ComConn, $"UPDATE asbshared.mtncol_issued SET uses = {MCPVisit["Visits"].ToString()} WHERE serialkey = '{MCPVisit["serialkey"].ToString()}'");
// }
//}
//load all mtncol_issued grouping by mcpnbr
//tDS = CF.LoadDataSet(BUY.Buy_Alta_ComConn, "SELECT I.nserialnr, I.mcpnbr, (SELECT MIN(issued_date) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) AS first_issued_date, (SELECT CONCAT_WS('-', nkassanr, MIN(nserialnr), nunicodenr) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr AND created = (SELECT MIN(created) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) LIMIT 1) AS first_Serial, (SELECT issued_by FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr AND created = (SELECT MIN(created) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) LIMIT 1) AS first_issued_by, (SELECT MAX(issued_date) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) AS last_issued_date, (SELECT CONCAT_WS('-', nkassanr, MAX(nserialnr), nunicodenr) AS last_Serial FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr AND created = (SELECT MAX(created) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) LIMIT 1) AS last_Serial, (SELECT issued_by FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr AND created = (SELECT MAX(created) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) LIMIT 1) AS last_issued_by, (SELECT COUNT(issued_by) FROM asbshared.mtncol_issued WHERE issued_by = 'SB' AND mcpnbr = I.mcpnbr) AS SBCnt, (SELECT COUNT(issued_by) FROM asbshared.mtncol_issued WHERE issued_by <> 'SB' AND mcpnbr = I.mcpnbr) AS AltaCnt, (SELECT nkassanr FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr AND issued_date = (SELECT MIN(issued_date) FROM asbshared.mtncol_issued WHERE mcpnbr = I.mcpnbr) LIMIT 1) AS nkassanr, MAX(tied_to_cc) AS tied_to_cc, COUNT(*) AS uses FROM asbshared.mtncol_issued AS I WHERE I.mcpnbr LIKE 'MCP%' GROUP BY I.mcpnbr", tDS, "asbissued");
//PbMTCUsage.Maximum = tDS.Tables["asbissued"].Rows.Count;
//if (tDS.Tables.Contains("asbissued"))
//{
// foreach (DataRow tRow in tDS.Tables["asbissued"].Rows)
// {
// //loop through the rows
// PbMTCUsage.Value = tDS.Tables["asbissued"].Rows.IndexOf(tRow);
// statusStrip1.Items[0].Text = "Setting Mountain Collective Usage";
// SetTaskStatus("", "", pbMTCUpload);
// string mkassanr = tRow["nkassanr"].ToString();
// string mserialnr = tRow["nserialnr"].ToString() != string.Empty ? tRow["nserialnr"].ToString() : $"{mkassanr}-{tRow["nserialnr"].ToString()}-{tRow["unicodenr"].ToString()}";
// //DataRow zRow = CF.LoadDataRow(DW.dwConn, "SELECT ");
// string mkey = tRow["mcpnbr"].ToString().Trim();
// string mserialkey = tRow["first_serial"].ToString();
// string mfirstissuedby = tRow["first_issued_by"].ToString();
// string mfirstissued_date = Convert.ToDateTime(tRow["first_issued_date"].ToString()).ToString(Mirror.AxessDateFormat);
// string mlastserialkey = tRow["last_serial"].ToString();
// string mlasstissuedby = tRow["last_issued_by"].ToString();
// string mlastissued_date = Convert.ToDateTime(tRow["last_issued_date"].ToString()).ToString(Mirror.AxessDateFormat);
// int msbcnt = Convert.ToInt16(tRow["sbcnt"].ToString());
// int maltacnt = Convert.ToInt16(tRow["altacnt"].ToString());
// long mtotUses = CF.RowCount(DW.dwConn, $"{DW.ActiveDatabase}.skivisits", $"mcpnbr = '{mkey}'");
// string mcclink = tRow["tied_to_cc"].ToString();
// if (CF.RowExists(DW.dwConn, $"{DW.ActiveDatabase}.willcall", $"res_no = '{mkey}'"))
// {
// //CF.ExecuteSQL(DW.dwConn, $"UPDATE {DW.ActiveDatabase}.willcall SET uses={mtotUses.ToString()}, itemstatus='I', issue_date='{mfirstissued_date}', POS='{mkassanr}', first_issue_date='{mfirstissued_date}', issuedby='{mfirstissuedby}', firstserial='{mserialkey}', last_issue_date='{mlastissued_date}', lastissuedby='{mlasstissuedby}', lastserial='{mlastserialkey}', altauses={maltacnt.ToString()}, sbuses={msbcnt.ToString()}, cclink='{mcclink}' WHERE res_no='{mkey} '");
// CF.ExecuteSQL(BUY.Buy_Alta_ComConn, $"UPDATE {BUY.ActiveDatabase}.mtncol_issued SET axuses={mtotUses.ToString()} WHERE mcpnbr = '{mkey}'");
// }
// }
// tDS.Tables.Remove("asbissued");
//}
return(true);
}
