Esempio n. 1
0
        private void storeItemsInComboBox()
        {
            //TODO: Weird results
            //var itemNameQuery =
            //from product in data.Products
            //select new {product.productName};

            //itemName.ItemsSource = itemNameQuery.ToList();


            //Copied from Products page as above query was not working as intended
            SqlAccessor.Open();
            int i = 0;

            using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT DISTINCT productID FROM dbo.Sales ORDER BY(productID);"))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        itemName.Items.Add(reader["productID"]);
                        i++;
                    }
                }
            }
            SqlAccessor.Close();
        }
Esempio n. 2
0
        private void Change_Clicked(object sender, RoutedEventArgs e)
        {
            // Update existing product
            List <SqlParameter> sqlParameters = new List <SqlParameter>()
            {
                new SqlParameter()
                {
                    ParameterName = "@name", Value = edititemname.Text
                },
                new SqlParameter()
                {
                    ParameterName = "@curQuan", Value = editProductQuantity.Text
                }
            };


            SqlAccessor.Open();
            using (SqlDataReader reader = SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE productName = @name;", sqlParameters))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
                        {
                            LoadProductsToDataGrid();
                        }
                    }
                }
            }
            SqlAccessor.Close();
        }
Esempio n. 3
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            InventoryItem newInvItem;

            if ((sender as Button) == salesadditem)
            {
                int currentItemQuantity = 0;
                SqlAccessor.Open();

                // Get the current quantity of a product
                using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            if ((reader["productName"].ToString().ToLower() == itemnamebox.Text.ToLower()))
                            {
                                currentItemQuantity = int.Parse((reader["currentQuantity"].ToString()));
                            }
                        }
                    }
                }

                // Reduce the current quantity of the product based on value
                List <SqlParameter> sqlParameters = new List <SqlParameter>()
                {
                    new SqlParameter()
                    {
                        ParameterName = "@curQuan", Value = currentItemQuantity - int.Parse(qtextbox.Text)
                    },
                    new SqlParameter()
                    {
                        ParameterName = "@name", Value = itemnamebox.Text
                    }
                };

                SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE productName = @name;", sqlParameters);

                SqlAccessor.Close();

                // UI / Storing
                newInvItem = new InventoryItem {
                    Name = itemnamebox.Text, QuantityCurrent = int.Parse(qtextbox.Text)
                };
                qtextbox.Clear();
                _inventoryItems.Add(newInvItem);
                dataGrid.Items.Add(newInvItem);
            }
        }
Esempio n. 4
0
        private void LoadProductsToDropdown()
        {
            SqlAccessor.Open();
            int i = 0;

            using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        edititemname.Items.Add(reader["productName"].ToString());
                        i++;
                    }
                }
            }
            SqlAccessor.Close();
        }
Esempio n. 5
0
        public SalesScreen()
        {
            InitializeComponent();
            InitDataGrid(dataGrid);

            SqlAccessor.Open();

            // Load from database -> combo box
            using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
            {
                if (reader != null)
                {
                    int i = 0;
                    while (reader.Read())
                    {
                        itemnamebox.Items.Add(reader["productName"].ToString());
                        i++;
                    }
                }
            }

            SqlAccessor.Close();
        }
Esempio n. 6
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            IQueryable query = null;
            int        x     = int.Parse(itemName.SelectedItem.ToString());
            int        total = 0;
            //Monthly

            var query2 =
                from p in data.Products
                where p.productID == x
                select new { p.productName };

            if (requestedPeriod.Text == "Monthly Forecast")
            {
                query =
                    from s in data.Sales
                    where s.productID == x
                    group s by s.productID into sales
                    select new
                {
                    sales = sales.Sum(a => a.salesQuantity)
                };

                // Output Monthly
                foreach (var row in query)
                {
                    foreach (var row2 in query2)
                    {
                        forecastDescriptor.Text = "Monthly Sales Forecast for: " + row2.productName.ToString() + row.ToString().Replace("{", "").Replace("}", " ");
                    }
                }
            }

            //Weekly
            else if (requestedPeriod.Text == "Weekly Forecast")
            {
                SqlAccessor.Open();
                int i = 0;

                using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT productID, DATEADD(week, DATEDIFF(week, 0, saleDate), 0) AS WeekStart, SUM(salesQuantity) as WeeklySales FROM Sales WHERE productID =" + x + "GROUP BY DATEADD(week, DATEDIFF(week, 0, saleDate), 0), productID;"))

                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            //pass the reader variables to a string
                            string readerToString = "ID: " + reader["productID"].ToString() + " DateTime: " + Convert.ToString(reader["WeekStart"]) + " Sales: " + reader["WeeklySales"].ToString();
                            //calculate total
                            total = reader.GetInt32(2) + total;
                            // forecastDescriptor.Text = read;
                            i++;
                        }
                    }
                }
                // i being the number of entries in reader
                int average = total / i;

                SqlAccessor.Close();

                // Output Weekly
                //forecastDescriptor.Text = "Weekly Sales Prediction for product: "  +  + " is " + average;
                foreach (var row in query2)
                {
                    forecastDescriptor.Text = "Weekly Sales Prediction for product: " + row.productName.ToString() + " is " + average;
                }
            }
        }
Esempio n. 7
0
        private void SaveChanges_Clicked(object sender, RoutedEventArgs e)
        {
            int currentProductID = int.MaxValue;

            SqlAccessor.Open();

            // Match each datagrid item with product table item, and insert product into sales table
            for (int j = 0; j < dataGrid.Items.Count; j++)
            {
                // Match product
                using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            if ((reader["productName"].ToString().ToLower() == _inventoryItems[j].Name.ToLower()))
                            {
                                currentProductID = int.Parse((reader["productID"].ToString()));
                            }
                        }
                    }
                }

                // Add product to sale
                if (currentProductID != int.MaxValue)
                {
                    DateTime t = DateTime.Today;
                    if (saleDatePicker.SelectedDate.HasValue)
                    {
                        t = saleDatePicker.SelectedDate.Value;
                    }

                    List <SqlParameter> sqlParameters = new List <SqlParameter>()
                    {
                        new SqlParameter()
                        {
                            ParameterName = "@pid", Value = currentProductID
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@date", Value = t
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@quantity", Value = _inventoryItems[j].QuantityCurrent
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@loginid", Value = loginscreen.GetLoginName()
                        }
                    };

                    using (SqlDataReader reader = SqlAccessor.RunQuery("INSERT INTO dbo.Sales (productID, saleDate, salesQuantity, employee) VALUES (@pid, @date, @quantity, @loginid)", sqlParameters))
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                if ((reader["productName"].ToString().ToLower() == _inventoryItems[j].Name.ToLower()))
                                {
                                    currentProductID = int.Parse((reader["productID"].ToString()));
                                }
                            }
                        }
                    }
                }
                else
                {
                    Console.WriteLine("Error, currentProductID not found in database");
                }
            }

            SqlAccessor.Close();

            // UI / Storing
            _inventoryItems.Clear();
            dataGrid.Items.Clear();
        }
Esempio n. 8
0
        private void Add_Clicked(object sender, RoutedEventArgs e)
        {
            if ((sender as Button) == additembutton)
            {
                bool checkItemInDB       = false;
                int  currentItemQuantity = 0;
                SqlAccessor.Open();

                // Confirm product is in database, and get the current quantity of that item
                using (SqlDataReader reader = SqlAccessor.RunQuery("SELECT * FROM dbo.Products;"))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
                            {
                                checkItemInDB       = true;
                                currentItemQuantity = int.Parse(reader["currentQuantity"].ToString());
                            }
                        }
                    }
                }

                if (checkItemInDB == false)
                {
                    // Add new product
                    List <SqlParameter> sqlParameters = new List <SqlParameter>()
                    {
                        new SqlParameter()
                        {
                            ParameterName = "@name", Value = addProductName.Text
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@quantity", Value = addProductQuantity.Text
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@brandID", Value = addProductID.Text
                        }
                    };

                    using (SqlDataReader reader = SqlAccessor.RunQuery("INSERT INTO dbo.Products (productName, currentQuantity, brandID) VALUES (@name, @quantity, @brandID)", sqlParameters))
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                if (reader["productName"].ToString().ToLower() == addProductName.Text.ToLower())
                                {
                                    LoadProductsToDataGrid();
                                    edititemname.Items.Add(addProductName.Text);
                                }
                            }
                        }
                    }
                }
                else
                {
                    // Update existing product
                    List <SqlParameter> sqlParameters = new List <SqlParameter>()
                    {
                        new SqlParameter()
                        {
                            ParameterName = "@id", Value = editProductQuantity.Text
                        },
                        new SqlParameter()
                        {
                            ParameterName = "@curQuan", Value = currentItemQuantity + int.Parse(addProductQuantity.Text)
                        }
                    };

                    using (SqlDataReader reader = SqlAccessor.RunQuery("UPDATE dbo.Products SET currentQuantity = @curQuan WHERE brandID = @id;", sqlParameters))
                    {
                        LoadProductsToDataGrid();
                    }
                }

                SqlAccessor.Close();
            }
        }