Exemplo n.º 1
0
        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]));
                    }
                }
            }
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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]));
                    }
                }
            }
        }
Exemplo n.º 4
0
        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]));
                    }
                }
            }
        }
Exemplo n.º 5
0
        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);
            }
        }