public static DateTime GetLastTransactionDate() { int lastTransactionDate = 0; var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string Table = ConfigurationManager.AppSettings[name : "PurchaseHistory"]; string PurchaseDate = ConfigurationManager.AppSettings[name : "PurchaseHistory_PurchaseDate"]; string queryString = "select max(" + PurchaseDate + ") from " + Table; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { if (reader.Read()) { lastTransactionDate = (int)reader[0]; } } } return(DateManipulation.intToDateTime(lastTransactionDate)); }
protected override void getPredictedPurchases() { var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string RecomendTable = ConfigurationManager.AppSettings[name : "PLS_RecomendHist"]; string Recomend_CustNumber = ConfigurationManager.AppSettings[name : "PLS_RecomendHist_CustNo"]; string Recomend_ItemNumber = ConfigurationManager.AppSettings[name : "PLS_RecomendHist_ItemNo"]; string Recomend_Date = ConfigurationManager.AppSettings[name : "PLS_RecomendHist_ProcessingDateInt"]; string query = "select distinct " + Recomend_ItemNumber + ", " + Recomend_CustNumber + " from " + RecomendTable + " where " + Recomend_Date + ">=@startingDate and " + Recomend_Date + "<=@endDate order by CustNo"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@startingDate", startingDate); command.Parameters.AddWithValue("@endDate", DateManipulation.DateTimeToint(DateManipulation.intToDateTime((int)startingDate).AddDays(6))); using (var reader = command.ExecuteReader()) { while (reader.Read()) { predictedPurchases.Add(new Purchase((string)reader[0], (string)reader[1])); } } } }
public static List <DateTime> GetProcessingDates() { List <DateTime> dates = new List <DateTime>(); try { var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string Table = ConfigurationManager.AppSettings[name : "Parameters"]; string ProcessingParameters = ConfigurationManager.AppSettings[name : "Parameters_ProcessingParameters"]; string query = "select distinct JSON_VALUE(" + ProcessingParameters + " ,'$.processingDate') from " + Table; using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(query, connection); using (var reader = command.ExecuteReader()) { while (reader.Read()) { dates.Add(DateManipulation.intToDateTime(Int32.Parse(reader[0].ToString()))); } } } return(dates); } catch (Exception ex) { return(dates); } }
public static List <int> ParametersIDs(DateTime processingDate) { List <int> ids = new List <int>(); int date = DateManipulation.DateTimeToint(processingDate); var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string Table = ConfigurationManager.AppSettings[name : "Parameters"]; string ProcessingParameters = ConfigurationManager.AppSettings[name : "Parameters_ProcessingParameters"]; string ID = ConfigurationManager.AppSettings[name : "Parameters_ID"]; string query = "select " + ID + " from " + Table + " where JSON_VALUE(" + ProcessingParameters + ",'$.processingDate') = @date"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@date", date); using (var reader = command.ExecuteReader()) { while (reader.Read()) { ids.Add((int)reader[0]); } } } return(ids); }
protected void getOccuredPurchases() { var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string HistoryTable = ConfigurationManager.AppSettings[name : "PurchaseHistory"]; string History_CustNumber = ConfigurationManager.AppSettings[name : "PurchaseHistory_CustomerID"]; string History_ItemNumber = ConfigurationManager.AppSettings[name : "PurchaseHistory_ItemID"]; string History_Date = ConfigurationManager.AppSettings[name : "PurchaseHistory_PurchaseDate"]; string query = ""; if (allPurchases) { query = "select distinct " + History_ItemNumber + ", " + History_CustNumber + " from " + HistoryTable + " where " + History_Date + ">=@startingDate and " + History_Date + "<=@endDate order by CustNo"; } else { query = "select distinct " + History_ItemNumber + ", " + History_CustNumber + " from " + HistoryTable + " where " + History_Date + "<=@endDate group by " + History_ItemNumber + ", " + History_CustNumber + " having count(*)>2 and max(" + History_Date + ") >=@startingDate"; } using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@startingDate", startingDate); command.Parameters.AddWithValue("@endDate", DateManipulation.DateTimeToint(DateManipulation.intToDateTime((int)startingDate).AddDays(6))); using (var reader = command.ExecuteReader()) { while (reader.Read()) { occuredPurchases.Add(new Purchase((string)reader[0], (string)reader[1])); } } } }
public void getAllItems() { itemNos = new List <string>(); lastPurchases = new List <int>(); DateTime processingDateDateFormat = DateManipulation.intToDateTime(Parameters.processingDate); var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string Table = ConfigurationManager.AppSettings[name : "PurchasePeriods"]; string CustomerID = ConfigurationManager.AppSettings[name : "PurchasePeriods_CustomerID"]; string ItemID = ConfigurationManager.AppSettings[name : "PurchasePeriods_ItemID"]; string Period = ConfigurationManager.AppSettings[name : "PurchasePeriods_Period"]; string PeriodEnd = ConfigurationManager.AppSettings[name : "PurchasePeriods_PeriodEnd"]; string queryString = "select distinct(" + ItemID + "),max(" + PeriodEnd + ") from " + Table + " where " + CustomerID + "= @custNo" + " and " + PeriodEnd + "<@bDate" + " group by " + ItemID + " having count(*)>1 and max(" + PeriodEnd + ")>@bDateMinus6Months " + " and min(" + Period + ") * 0.5< DATEDIFF(DAY, max(" + PeriodEnd + "), @bDate) + 7" + " and max(" + Period + ") * 1.5 > DATEDIFF(DAY, max(" + PeriodEnd + "), @bDate)"; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@custNo", custNo); command.Parameters.AddWithValue("@bDate", processingDateDateFormat.ToShortDateString()); command.Parameters.AddWithValue("@bDateMinus6Months", processingDateDateFormat.AddMonths(-6).ToShortDateString()); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { itemNos.Add(((String)reader[0]).Replace(" ", String.Empty)); lastPurchases.Add(DateManipulation.DateTimeToint((DateTime)reader[1])); } } } }
public static async Task nextWeekPredictionsAsync(int date, Action t1_OnProgressUpdate, Action <string> t2_OnFinishUpdate, System.ComponentModel.BackgroundWorker bWorker) { string message = ""; bool stop = false; try { Prediction.init(); OnProgressUpdate = t1_OnProgressUpdate; OnProgressFinish = t2_OnFinishUpdate; List <string> allCustomers = new List <string>(); Customer.worker = bWorker; var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string Table = ConfigurationManager.AppSettings[name : "PurchaseHistory"]; string CustomerID = ConfigurationManager.AppSettings[name : "PurchaseHistory_CustomerID"]; string PurchaseDate = ConfigurationManager.AppSettings[name : "PurchaseHistory_PurchaseDate"]; string queryString = "select distinct(" + CustomerID + ") from " + Table + " where " + PurchaseDate + "< @date and " + PurchaseDate + "> @dateMin"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@date", date); command.Parameters.AddWithValue("@dateMin", DateManipulation.DateTimeToint(DateManipulation.intToDateTime(date).AddMonths(-Parameters.customerRecency))); using (var reader = command.ExecuteReader()) { while (reader.Read()) { allCustomers.Add(reader[0].ToString()); } } } int custCount = allCustomers.Count(); var listCust = allCustomers.ToList(); TotalCount = custCount; DoneCount = 0; totalWrites = 0; t1_OnProgressUpdate?.Invoke(); Parallel.For(0, custCount, new ParallelOptions { MaxDegreeOfParallelism = 3 }, (index, state) => { Customer newCustomer = new Customer() { custNo = listCust[index] }; if (worker.CancellationPending) { stop = true; state.Stop(); message = "The process has been canceled!"; } if (!stop) { newCustomer.PredictAllItems(); } }); } catch (Exception ex) { message = ex.Message; log.Error(ex.Message); } if (DoneCount == TotalCount) { message = "Predictions have successfully been made"; Parameters.Update((int)Enum.ProcessingStatus.Status.SUCCESS, ""); OnProgressFinish?.Invoke(message); } else if (!stop) { Parameters.Update((int)Enum.ProcessingStatus.Status.ERROR, message); OnProgressFinish?.Invoke(message); } else { Parameters.Update((int)Enum.ProcessingStatus.Status.SUSPENDED, ""); OnProgressFinish?.Invoke(message); } }
public static List <Purchase> getListOfPurchases(string custNo, string itemNo) { List <Purchase> returnList = new List <Purchase>(); var connectionString = ConfigurationManager.ConnectionStrings[name : "PED"].ConnectionString; string HistoryTable = ConfigurationManager.AppSettings[name : "PurchaseHistory"]; string History_CustNumber = ConfigurationManager.AppSettings[name : "PurchaseHistory_CustomerID"]; string History_ItemNumber = ConfigurationManager.AppSettings[name : "PurchaseHistory_ItemID"]; string History_Date = ConfigurationManager.AppSettings[name : "PurchaseHistory_PurchaseDate"]; string History_Quantity = ConfigurationManager.AppSettings[name : "PurchaseHistory_PurchaseQuantity"]; string query = "select " + History_ItemNumber + ", " + History_CustNumber + ", " + History_Date + ", " + History_Quantity + " from " + HistoryTable + " where " + History_ItemNumber + "=@itemNo and " + History_CustNumber + "=@custNo order by " + History_Date; using (var connection = new SqlConnection(connectionString)) { connection.Open(); var command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@itemNo", itemNo); command.Parameters.AddWithValue("@custNo", custNo); using (var reader = command.ExecuteReader()) { while (reader.Read()) { returnList.Add(new Purchase((string)reader[0], (string)reader[1], DateManipulation.intToDateTime((int)reader[2]), (int)reader[3])); } } } return(returnList); }