/// <summary>
        /// Returns a list of all sale lines for a specific product.
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public static List <List <string> > GetProductSales(string product)
        {
            TsqlQuery             query        = new TsqlQuery("Get Products by SaleID", QuerySaleItemsString(product));
            List <List <string> > productSales = new List <List <string> >();

            SqlDataReader reader = query.Command.ExecuteReader();

            while (reader.Read())
            {
                string p  = CleanString(reader.GetString(0));
                string s  = CleanString(reader.GetInt64(1).ToString());
                string q  = CleanString(reader.GetInt16(2).ToString());
                string tc = CleanString(reader.GetDecimal(3).ToString());

                List <string> line = new List <string>();

                line.Add(p);  // product_ID
                line.Add(s);  // sale_ID
                line.Add(q);  // qty
                line.Add(tc); //total cost

                productSales.Add(line);
            }

            return(productSales);
        }
        /// <summary>
        /// returns an itemised list by category of each sale line.
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public static List <List <string> > GetItemisedCategorySales(string category = null)
        {
            TsqlQuery             query         = new TsqlQuery("Get Sales by Category", QuerySaleCategoryString(true, category));
            List <List <string> > categorySales = new List <List <string> >();

            SqlDataReader reader = query.Command.ExecuteReader();

            while (reader.Read())
            {
                string p  = CleanString(reader.GetString(0));
                string c  = CleanString(reader.GetString(1));
                string s  = CleanString(reader.GetInt64(2).ToString());
                string q  = CleanString(reader.GetInt16(3).ToString());
                string tc = CleanString(reader.GetDecimal(4).ToString());

                List <string> line = new List <string>();

                line.Add(p);  // product_ID
                line.Add(c);  // category
                line.Add(s);  // sale_ID
                line.Add(q);  // qty
                line.Add(tc); //total cost

                categorySales.Add(line);
            }

            return(categorySales);
        }
예제 #3
0
        /// <summary>
        /// Queries the database between two dates and produces a csv of the sales line info.
        /// </summary>
        /// <param name="earliest">The ealiest date to export sales from</param>
        /// <param name="latest">The latest date to export sales from</param>
        public static void ExportSales(string earliest, string latest)
        {
            TsqlQuery     query  = new TsqlQuery("Get sales lines for export", QuerySalesString(earliest, latest));
            SqlDataReader reader = query.Command.ExecuteReader();

            Dictionary <string, string> saleDetails = new Dictionary <string, string>()
            {
                { "SaleID", "" },
                { "SaleDate", "" },
                { "ProductID", "" },
                { "QuantitySold", "" },
                { "CostPerItem", "" },
                { "LineTotal", "" }
            };

            while (reader.Read())
            {
                string key = reader.GetInt32(0).ToString();
                if (!salesToCSV.ContainsKey(key))
                {
                    salesToCSV.Add(key, new Dictionary <string, string>(saleDetails));
                    salesToCSV[key]["SaleID"]       = reader.GetInt64(1).ToString();
                    salesToCSV[key]["SaleDate"]     = reader.GetDateTime(2).ToString();
                    salesToCSV[key]["ProductID"]    = reader.GetString(3);
                    salesToCSV[key]["QuantitySold"] = reader.GetInt16(4).ToString();
                    salesToCSV[key]["CostPerItem"]  = reader.GetDecimal(5).ToString();
                    salesToCSV[key]["LineTotal"]    = reader.GetDecimal(6).ToString();
                }
            }

            reader.Close();
            string file = "Sale_Details_" + earliest + "_-_" + latest + ".csv";

            WriteCSV(file, salesToCSV);
        }
        /// <summary>
        /// Will return a list of categories and their summarised sales.
        /// </summary>
        /// <returns></returns>
        public static List <List <string> > GetSummarisedCategorySales()
        {
            TsqlQuery             query         = new TsqlQuery("Get Sales by Category", QuerySaleCategoryString());
            List <List <string> > categorySales = new List <List <string> >();

            SqlDataReader reader = query.Command.ExecuteReader();

            while (reader.Read())
            {
                string c  = CleanString(reader.GetString(0));
                string tc = CleanString(reader.GetString(1));

                List <string> line = new List <string>();

                line.Add(c);  // category
                line.Add(tc); //total cost

                categorySales.Add(line);
            }

            return(categorySales);
        }
        public static void UpdateCompanies()
        {
            companies.Clear();

            TsqlQuery query = new TsqlQuery("Update companies from DB", DBCompanyQueryString());

            SqlDataReader reader = query.Command.ExecuteReader();

            while (reader.Read())
            {
                Company c = new Company(
                    reader.IsDBNull(0)? null : reader.GetString(0),
                    reader.IsDBNull(1)? null : reader.GetString(1),
                    reader.IsDBNull(2)? null : reader.GetString(2),
                    reader.IsDBNull(3)? null : reader.GetString(3)
                    );

                companies.Add(c);
            }

            reader.Close();
        }
        /// <summary>
        /// Updates the internal product list from the DB.
        /// </summary>
        public static void UpdateProducts()
        {
            products.Clear();

            TsqlQuery query = new TsqlQuery("Update products from DB", DBProductQueryString());

            SqlDataReader reader = query.Command.ExecuteReader();

            while (reader.Read())
            {
                Product p = new Product(
                    CleanString(reader.GetString(0)),
                    CleanString(reader.GetString(1)),
                    CleanString(reader.GetString(2)),
                    CleanString(reader.GetString(3)),
                    CleanString(reader.GetDecimal(4).ToString()),
                    CleanString(reader.GetDecimal(5).ToString()),
                    CleanString(reader.GetInt16(6).ToString()));

                products.Add(p);
            }

            reader.Close();
        }