/// <summary>
/// get all the size options for the y dim of a box, checking if there are Angles with sufficient size for the wardrobe as a whole
/// </summary>
/// <param name="h_box"></param>
/// <param name="h_wardrobe"></param>
/// <returns></returns>
public static List <string> DbGetHeightOpt(double h_box, double h_wardrobe)
{
BDD database = new BDD("kitbox");
string selection = "hauteur";
string table_name = "catalog";
string condition = "WHERE (";
Dictionary <string, string> selected_characteristics = new Dictionary <string, string>()
{
{ "Ref", "Panneau Ar" },
{ "largeur", Convert.ToString(32) },
{ "profondeur", Convert.ToString(0) },
{ "couleur", "Blanc" }
};
int counter = 0;
foreach (string key in selected_characteristics.Keys)
{
counter++;
condition += key + "='" + selected_characteristics[key] + "'";
if (counter != selected_characteristics.Count)
{
condition += " AND ";
}
}
condition += ")";
List <List <object> > result = database.readElement(selection, table_name, condition);
condition = "WHERE (Ref = 'Cornieres') ORDER BY hauteur DESC;";
List <List <object> > cornieres = database.readElement(selection, table_name, condition);
double h_max = Convert.ToDouble(cornieres[0][0]);
List <string> hs = new List <string>();
foreach (List <object> h in result)
{
if (h_wardrobe - h_box + 4 + Convert.ToDouble(h[0]) <= h_max)
{
hs.Add(Convert.ToString(4 + Convert.ToDouble(h[0])));
}
}
return(hs);
}
static bool DblsCLient(int id, string password)
{
BDD database = new BDD("kitbox");
string tableName = "client";
string columnNames = "Password";
string condtion = string.Format("WHERE (Client_Id = {0})", id);
List <List <object> > list = new List <List <object> >();
list = database.readElement(columnNames, tableName, condtion);
if (list.Count == 0)
{
return(false);
}
else
{
if (Convert.ToString(list[0][0]) == password)
{
return(true);
}
}
return(false);
}
/// <summary>
/// This function searches for the Id of the client corresponding to the phone number.
/// </summary>
/// <param name="phonenumber"></param>
/// <returns>int</returns>
public static int searchId(int phonenumber)
{
BDD database = new BDD("kitbox");
string tableName = "client";
string columnNames = "Client_Id";
string condtion = string.Format("WHERE (Phonenumber = '{0}')", phonenumber);
List <List <object> > list = new List <List <object> >();
list = database.readElement(columnNames, tableName, condtion);
Person person = new Person();
if (list.Count == 0)
{
return(0);
}
else
{
person.Id = Convert.ToInt32(list[0][0]);
return(person.Id);
}
}
/// <summary>
/// This function searchs for a client according to the id.
/// </summary>
/// <param name="id">int</param>
/// <returns>Person</returns>
public static Person searchClient(int id)
{
BDD database = new BDD("kitbox");
string tableName = "client";
string columnNames = "Firstname,Lastname";
string condtion = string.Format("WHERE (Client_Id = '{0}')", id);
List <List <object> > list = new List <List <object> >();
list = database.readElement(columnNames, tableName, condtion);
Person person = new Person();
if (list.Count == 0)
{
return(null);
}
else
{
person.FirstName = Convert.ToString(list[0][0]);
person.LastName = Convert.ToString(list[0][1]);
return(person);
}
}
static Person DbConnectClient(int id, string password)
{
BDD database = new BDD("kitbox");
string tableName = "client";
string columnNames = textString.columnNames(tableName);
string condtion = string.Format("WHERE (Client_Id = {0} AND Password = {1})", id, password);
List <List <object> > list = new List <List <object> >();
list = database.readElement(columnNames, tableName, condtion);
Person person = new Person();
person.Id = id;
person.Password = password;
person.FirstName = Convert.ToString(list[0][1]);
person.LastName = Convert.ToString(list[0][2]);
person.PhoneNumber = Convert.ToInt16(list[0][3]);
person.Email = Convert.ToString(list[0][4]);
String[] split = Convert.ToString(list[0][6]).Split(';');
person.Address["Street"] = split[0];
person.Address["Street number"] = split[1];
person.Address["Postal code"] = split[2];
return(person);
}
static void DbConnectClient(params string[] args)
{
BDD database = new BDD("kitbox");
//string columnNames=
}
/// <summary>
/// Returns a Part with the selected_characteristics if found in database. If there are multiple results, returns the first
/// </summary>
/// <param name="selected_characteristics"></param>
/// <param name="order_by"></param>
/// <returns></returns>
public static Part DbSelectPart(Dictionary <string, string> selected_characteristics, string order_by = "")
{
BDD database = new BDD("kitbox");
string selection = "Ref, Code, hauteur, profondeur, largeur, Couleur, PrixClient";
string table_name = "catalog";
string condition = "WHERE (";
int counter = 0;
foreach (string key in selected_characteristics.Keys)
{
counter++;
condition += key + "='" + selected_characteristics[key] + "'";
if (counter != selected_characteristics.Count)
{
condition += " AND ";
}
}
condition += ")";
if (order_by != "")
{
condition += "ORDER BY " + order_by + ";";
}
Part request = null;
List <List <object> > result = database.readElement(selection, table_name, condition);
if (result.Count > 0)
{
if (result[0].Count == selection.Split(',').Length)
{
Dictionary <string, object> data = new Dictionary <string, object>();
data["Reference"] = Convert.ToString(result[0][0]);
data["Code"] = Convert.ToString(result[0][1]);
data["Dimensions"] = new Size3D(Convert.ToDouble(result[0][4]), Convert.ToDouble(result[0][2]), Convert.ToDouble(result[0][3]));
data["Color"] = Color.FromName(TranslateColor(Convert.ToString(result[0][5])));
data["Selling_price"] = Convert.ToDouble(result[0][6]);
if (Convert.ToString(result[0][0]).Contains("Panneau") || Convert.ToString(result[0][0]).Contains("Traverse"))
{
request = new Panel();
request.SetData(data);
}
else if (Convert.ToString(result[0][0]).Contains("Porte"))
{
request = new Door();
request.SetData(data);
}
else if (Convert.ToString(result[0][0]).Contains("Coupelles"))
{
request = new Knop();
request.SetData(data);
}
else if (Convert.ToString(result[0][0]).Contains("Cornieres"))
{
request = new Angle();
request.SetData(data);
}
else if (Convert.ToString(result[0][0]).Contains("Tasseau"))
{
request = new Door();
request.SetData(data);
}
}
}
return(request);
}
/// <summary>
/// Adds in the database the order given in the parameter, fills
/// the tables with the components and links them to the current
/// client and his the ordrer. The order must have a current_client
/// with an existing id
/// </summary>
/// <param name="order">Order.</param>
public static void DbAddOrder(Order order)
{
BDD database = new BDD("kitbox");
Dictionary <string, object> bill = textString.bill();
Dictionary <string, Dictionary <string, object> > components = new Dictionary <string, Dictionary <string, object> >();
string header_bill = bill["header"].ToString();
string footer_bill = bill["footer"].ToString();
string columnNames = "Client_Id, Date, Header_Bill, Footer_Bill";
string tableName = "orders";
string client_id = order.CurrentClient.Id.ToString();
DateTime date = DateTime.Now;
date.AddDays(order.Delayed);
string dateString = date.ToString();
string data = string.Format("{0},'{1}','{2}','{3}'", client_id, dateString, header_bill, footer_bill);
// The order id is auto incremented in the orders table and we add
// in the orders table : the client id, the date, the header bill and the footer bill
database.addElement(tableName, columnNames, data);
List <List <object> > list = database.readElement("Order_Id", tableName, string.Format("WHERE Client_Id = '{0}'", client_id.ToString()));
int order_id = Convert.ToInt32(list[list.Count - 1][0]);
List <object> wardrobes = order.Wardrobes;
tableName = "rel_catord";
columnNames = "Order_Id ,Wardrobe_Id,Box_Id,Component_Id";
data = "";
// Choose a wardrobe in the list of wardrobes
for (int i = 1; i <= wardrobes.Count; i++)
{
Wardrobe wardrobe = (Wardrobe)wardrobes[i - 1];
int number_box = wardrobe.Components["Etage"].Count;
data = string.Format("{0},{1},{2},{3}", order_id.ToString(), i.ToString(), "", "");
// Choose an Angle
foreach (KeyValuePair <string, object> cornieres in wardrobe.Components["Cornieres"])
{
Angle angle = (Angle)cornieres.Value;
string Id = database.readElement("Id", "catalog", string.Format("WHERE Code='{0}'", angle.Code.ToString()))[0][0].ToString();
data = string.Format("'{0}','{1}','{2}','{3}'", order_id.ToString(), i.ToString(), "0", Id);
// Add the corner and its information in the table
database.addElement(tableName, columnNames, data);
}
// Choose a box of the wardrobe
for (int j = 1; j <= number_box; j++)
{
data = string.Format("{0},{1},{2},{3}", order_id.ToString(), i.ToString(), j.ToString(), "");
// The "Etage" key gives all the boxes
foreach (KeyValuePair <string, object> kvp in wardrobe.Components["Etage"])
{
if (Convert.ToInt32(kvp.Key) == j)
{
Box box = (Box)wardrobe.Components["Etage"][j.ToString()];
Dictionary <string, Dictionary <string, Part> > parts = box.Pieces;
foreach (KeyValuePair <string, Dictionary <string, Part> > kvp2 in parts)
{
// Choose a part
foreach (KeyValuePair <string, Part> kvp3 in kvp2.Value)
{
string Id = database.readElement("Id", "catalog", string.Format("WHERE Code='{0}'", kvp3.Value.Code.ToString()))[0][0].ToString();
data = string.Format("{0},{1},{2},{3}", order_id.ToString(), i.ToString(), j.ToString(), Id);
// Add the part of the box and its information in the database
database.addElement(tableName, columnNames, data);
}
}
}
}
}
}
}