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