public static List <Supplier> getSupplier()
{
//get the user query and data from the database
string query = DatabaseQuery.getHumans(1);
DataTable dt = tools.Database.getData(query);
//convert all the user into a user object
List <Supplier> temp = new List <Supplier>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
string first_name = (string)dt.Rows[i]["first_name"];
string last_name = (string)dt.Rows[i]["last_name"];
string enter_name = (string)dt.Rows[i]["enterprise_name"];
string enter_add = (string)dt.Rows[i]["enterprise_adress"];
string email = (string)dt.Rows[i]["email"];
string phone = (string)dt.Rows[i]["phone_num"];
DateTime datet = DateTime.Today;//DateTime.Parse((string)dt.Rows[i]["date"]);
temp.Add(new Supplier(id, first_name, last_name, enter_name, enter_add, email, phone, datet));
}
return(temp);
}
public static int updateUser(User moduser)
{
string q = DatabaseQuery.setUserGrade(moduser.getId(), moduser.getGrade()) + ";";
q += DatabaseQuery.setUserPass(moduser.getId(), moduser.getHashPass());
return(Database.setData(q));
}
public static List <Bike> getBikes(Dictionary <string, BikeTemplate> btList)
{
string query = DatabaseQuery.getBike();
DataTable dt = tools.Database.getData(query);
List <Bike> temp = new List <Bike>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
int id_tBike = Convert.ToInt32(dt.Rows[i]["id_tBike"]);
int id_sale;
if (dt.Rows[i]["id_sale"] != DBNull.Value)
{
id_sale = Convert.ToInt32(dt.Rows[i]["id_sale"]);
}
else
{
id_sale = -1;
}
int state = Convert.ToInt32(dt.Rows[i]["state"]);
int poste = Convert.ToInt32(dt.Rows[i]["poste"]);
DateTime planned_date = (DateTime)dt.Rows[i]["planne_cDate"];
DateTime Constr_date;
if (dt.Rows[i]["create_Date"] != DBNull.Value)
{
Constr_date = (DateTime)dt.Rows[i]["create_Date"];
}
else
{
Constr_date = DateTime.MinValue;
}
foreach (BikeTemplate bt in btList.Values)
{
if (bt.getId() == id_tBike)
{
temp.Add(new Bike(id, state, id_sale, poste, bt, planned_date, Constr_date));
}
}
}
return(temp);
}
public static Dictionary <string, BikeTemplate> getBikeTemplates(List <CatalogBike> cbList, List <KitTemplate> ktList)
{
string query = DatabaseQuery.getTBike();
DataTable dt = tools.Database.getData(query);
Dictionary <string, BikeTemplate> Btemp = new Dictionary <string, BikeTemplate>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
int id_cat = Convert.ToInt32(dt.Rows[i]["id_cat"]);
foreach (CatalogBike cb in cbList)
{
if (cb.getId() == id_cat)
{
BikeTemplate bt = new BikeTemplate(id, cb);
string q = DatabaseQuery.getKitId_byTBike(id);
DataTable kdt = Database.getData(q);
for (int j = 0; j < kdt.Rows.Count; j++)
{
int idK = Convert.ToInt32(kdt.Rows[j]["id"]);
foreach (KitTemplate kt in ktList)
{
if (kt.getId() == idK)
{
bt.linkKitTemplate(kt);
}
}
}
if (!Btemp.ContainsKey(bt.Key))
{
Btemp.Add(bt.Key, bt);
}
}
}
}
return(Btemp);
}
public static List <Commande> getCommandes(List <KitTemplate> kitTemplatesList, List <User> userList, List <Supplier> clientList)
{
//get the user query and data from the database
string query = DatabaseQuery.getCommande();
DataTable st = tools.Database.getData(query);
//convert all the user into a user object
List <Commande> temp = new List <Commande>();
for (int i = 0; i < st.Rows.Count; i++)
{
int id = Convert.ToInt32(st.Rows[i]["id"]);
int id_client = Convert.ToInt32(st.Rows[i]["id_human"]);
int id_seller = Convert.ToInt32(st.Rows[i]["id_seller"]);
string state = (string)st.Rows[i]["state"];
DateTime prevision_date = DateTime.Today; //DateTime.Parse((string)dt.Rows[i]["date"]);
DateTime date = DateTime.Today; //DateTime.Parse((string)dt.Rows[i]["date"]);
List <Commande_item> ci_list = new List <Commande_item>();
//get commande items
string q = DatabaseQuery.getCommandeItems(id);
DataTable cmdit_dt = Database.getData(q);
for (int j = 0; j < cmdit_dt.Rows.Count; j++)
{
int id_kit = Convert.ToInt32(cmdit_dt.Rows[j]["id_type_kit"]);
int qnt = Convert.ToInt32(cmdit_dt.Rows[j]["qnt"]);
KitTemplate kt = kitTemplatesList.Where(x => x.getId() == id_kit).ToList()[0];
Commande_item ci = new Commande_item(kt, qnt);
ci_list.Add(ci);
}
temp.Add(new Commande(id, id_seller, id_client, state, date, prevision_date, ci_list, userList, clientList));
}
return(temp);
}
public static List <User> getUsers()
{
//get the user query and data from the database
string query = DatabaseQuery.getUsers(0);
DataTable dt = tools.Database.getData(query);
//convert all the user into a user object
List <User> temp = new List <User>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
string name = (string)dt.Rows[i]["user"];
int grade = Convert.ToInt32(dt.Rows[i]["grade"]);
string psw = (string)dt.Rows[i]["psw"];
temp.Add(new User(id, name, grade, psw));
}
return(temp);
}
public static List <string> checkTable()
{
string q = DatabaseQuery.getTable();
DataTable dt = getData(q);
List <string> changed_table = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
string[] temp = new string[2];
if (Properties.Settings.Default.DBProd)
{
temp[0] = (string)dt.Rows[i]["Tables_in_" + Properties.Settings.Default.DBUser];
}
else
{
temp[0] = (string)dt.Rows[i]["Tables_in_" + Properties.Settings.Default.DBUser2];
}
string q2 = DatabaseQuery.getCheckSum_tableQuery(temp[0]);
DataTable dt2 = getData(q2);
temp[1] = Convert.ToInt64(dt2.Rows[0]["Checksum"]).ToString();
if (tableCheck.ContainsKey(temp[0]))
{
if (tableCheck[temp[0]] != temp[1])
{
changed_table.Add(temp[0]);
}
}
else
{
tableCheck.Add(temp[0], temp[1]);
}
}
return(changed_table);
}
public static List <Sale> getSales(List <Bike> bikeList, List <User> userList, List <Client> clientList)
{
//get the user query and data from the database
string query = DatabaseQuery.getSales();
DataTable st = tools.Database.getData(query);
//convert all the user into a user object
List <Sale> temp = new List <Sale>();
for (int i = 0; i < st.Rows.Count; i++)
{
int id = Convert.ToInt32(st.Rows[i]["id"]);
int id_client = Convert.ToInt32(st.Rows[i]["id_human"]);
int id_seller = Convert.ToInt32(st.Rows[i]["id_seller"]);
string state = (string)st.Rows[i]["state"];
DateTime prevision_date = DateTime.Today; //DateTime.Parse((string)dt.Rows[i]["date"]);
DateTime date = DateTime.Today; //DateTime.Parse((string)dt.Rows[i]["date"]);
temp.Add(new Sale(id, id_seller, id_client, state, date, prevision_date, userList, clientList));
}
return(temp);
}
public static List <CatalogBike> getCatalogBikes(List <KitTemplate> ktList)
{
string q = DatabaseQuery.getCatalogBike();
DataTable dt = Database.getData(q);
//convert all the user into a user object
List <CatalogBike> temp = new List <CatalogBike>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
string name = (string)dt.Rows[i]["name"];
int PriceMul = Convert.ToInt32(dt.Rows[i]["PriceMul"]);
string pic_filename = (string)dt.Rows[i]["picture"];
string query = DatabaseQuery.getKit_by_catalogBikeId(id);
DataTable kdt = Database.getData(query);
CatalogBike cb = new CatalogBike(id, name, PriceMul, pic_filename);
for (int j = 0; j < kdt.Rows.Count; j++)
{
foreach (KitTemplate kt in ktList)
{
if (kt.getId() == Convert.ToInt32(kdt.Rows[j]["id_tKit"]))
{
cb.linkKitTemplate(kt);
}
}
}
temp.Add(cb);
}
return(temp);
}
public static List <KitTemplate> getKitTemplates()
{
//get the user query and data from the database
string query = DatabaseQuery.getKits();
DataTable dt = tools.Database.getData(query);
//convert all the user into a user object
List <KitTemplate> temp = new List <KitTemplate>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = Convert.ToInt32(dt.Rows[i]["id"]);
string name = (string)dt.Rows[i]["name"];
int cat = Convert.ToInt32(dt.Rows[i]["category"]);
int p = Convert.ToInt32(dt.Rows[i]["Price"]);
string prop;
if (dt.Rows[i]["properties"] != DBNull.Value)
{
prop = (string)dt.Rows[i]["properties"];
}
else
{
prop = "";
}
int stock_qtt = Convert.ToInt32(dt.Rows[i]["stock_qtt"]);
int stock_location_x = Convert.ToInt32(dt.Rows[i]["location_x"]);
int stock_location_y = Convert.ToInt32(dt.Rows[i]["location_y"]);
int bike_qtt = Convert.ToInt32(dt.Rows[i]["bike_qtt"]);
temp.Add(new KitTemplate(id, name, (KitCategory)cat, p, prop, stock_qtt, stock_location_x, stock_location_y, bike_qtt));
}
return(temp);
}
public static int updateKitTemplate(KitTemplate kt)
{
string q = DatabaseQuery.updateKitTemplate(kt.getId(), kt.getName(), (int)kt.getCategory(), kt.getPrice(), kt.getProperties(), kt.getStockQtt(), kt.getStockLocationX(), kt.getStockLocationY(), kt.getBikeQtt());
return(Database.setData(q));
}
public static int updateSale(Sale s)
{
string q = DatabaseQuery.updateTransaction(s);
return(Database.setData(q));
}
public static int addCatalogBike(CatalogBike cb)
{
string q = DatabaseQuery.addCatalogBike(cb);
return(Database.setData(q));
}
public static int unlinkKTCB(CatalogBike cb, KitTemplate kt)
{
string q = DatabaseQuery.delCompatibleKit(cb.getId(), kt.getId());
return(Database.setData(q));
}
public static int updateHuman(Human modClient)
{
string q = DatabaseQuery.updateHumans(modClient.getId(), modClient.getEtpName(), modClient.getEtpAdress(), modClient.getEmail(), modClient.getPhoneNumb());
return(Database.setData(q));
}
public static int setBikeState(Bike bk)
{
string q = DatabaseQuery.updateBike(bk);
return(Database.setData(q));
}
public static int addUser(User NewUser)
{
string q = DatabaseQuery.addUser(NewUser.getName(), NewUser.getHashPass(), NewUser.getGrade());
return(Database.setData(q));
}
public static int updateCommande(Commande c)
{
string q = DatabaseQuery.updateTransaction(c);
return(Database.setData(q));
}
public static int updateCatalogBike(CatalogBike kt)
{
string q = DatabaseQuery.updateCatalogBike(kt);
return(Database.setData(q));
}