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