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);
}
