private List <string> GetValues(string tableName, string propName)
{
DepotDbConnector depotDbConnector = DepotDbConnector.GetInstance();
DataSet ds = depotDbConnector.SelectQuerry("SELECT " + propName + " FROM " + tableName);
List <string> list = new List <string>();
object val;
List <object> listObj = new List <object>();
string s;
foreach (DataRow row in ds.Tables[0].Rows)
{
val = row[propName];
listObj.Add(val);
}
HashSet <object> list2 = new HashSet <object>(listObj);
listObj = new List <object>(list2);
listObj.Sort();
foreach (var it in listObj)
{
try
{
s = ((DateTime)it).ToString("d");
}
catch
{
s = it.ToString();
}
list.Add(s);
}
return(list);
}
public static void UpdateManufacturer(int oldId, string ids, string names, string countrys, string citys, string tels)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string name = ValidateField.ValidateString(names);
string country = ValidateField.ValidateString(countrys);
string city = ValidateField.ValidateString(citys);
string tel = ValidateField.ValidateTelephone(tels);
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update manufacturer set id_manufacturer = " + id +
", name = '" + name +
"', country = '" + country +
"', city = '" + city +
"', telephone = '" + tel +
"' where id_manufacturer = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into manufacturer (id_manufacturer, name, country, city, telephone) values (" +
id + ", '" +
name + "', '" +
country + "', '" +
city + "', '" +
tel + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
private void Init()
{
if (SystemUser.GetInstance().UserType == SystemUser.UsersType.Client)
{
AddButton.Visibility = Visibility.Hidden;
UpdateButton.Visibility = Visibility.Hidden;
DeleteButton.Visibility = Visibility.Hidden;
}
if (SystemUser.GetInstance().UserType == SystemUser.UsersType.Admin)
{
AddOrderButton.Visibility = Visibility.Hidden;
}
// Buffer initializes
bufferData = BufferData.GetInstance();
// list of tables initializes
if (bufferData.Mode == BufferData.Modes.Structure)
{
StructureRadioButton1.IsChecked = true;
}
else
{
ViewRadioButton2.IsChecked = true;
}
tableListBox.ItemsSource = bufferData.TablesList;
depotDbConnector = DepotDbConnector.GetInstance();
string selectedTable = "state";
SelectTable(selectedTable);
}
public static void UpdateReceivedMaterial(int oldId, string ids, string dates, string amounts, string idMats)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string date = ValidateField.ValidateDate(dates);
int amount = ValidateField.ValidateNumber(amounts);
int idMat = ValidateField.ValidateNumber(idMats);
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update received_material set id_received_material = " + id +
", date_received = '" + date +
"', amount = '" + amount +
"', id_material = '" + idMat +
"' where id_received_material = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into received_material (id_received_material, date_received, amount, id_material) values (" +
id + ", '" +
date + "', '" +
amount + "', '" +
idMat + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
public static void UpdateFashion(int oldId, string ids, string names, string defs)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string name = ValidateField.ValidateString(names);
string def = ValidateField.ValidateString(defs);
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update fashion set id_fashion = " + id +
", name = '" + name + "', definition = '" + def +
"' where id_fashion = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into fashion (id_fashion, name, definition) values (" + id +
", '" + name + "', '" + def + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
private DataSet FindValues(string tableName, string condition)
{
DepotDbConnector depotDbConnector = DepotDbConnector.GetInstance();
DataSet ds = depotDbConnector.SelectQuerry(
"SELECT * FROM " + tableName + " where " + condition);
return(ds);
}
public static void Delete(string tableName, int id, string key)
{
string query;
tableName = Correct(tableName);
query = "Delete from " + tableName + " where " + key + "= " + id + ";";
DepotDbConnector.GetInstance().DeleteQuerry(query);
}
// Adds record to the table orders
public static bool AddOrder(string dates, string amounts, string mats, string dests,
string fashions, string types)
{
BufferData bufferData = BufferData.GetInstance();
int id = bufferData.OrdersViewTable.Data.Max(x => x.IdOrder) + 1;
string date = ValidateField.ValidateDate(dates);
int amount = ValidateField.ValidateNumber(amounts);
mats = ValidateField.ValidateString(mats);
dests = ValidateField.ValidateString(dests);
fashions = ValidateField.ValidateString(fashions);
types = ValidateField.ValidateString(types);
int idDest = bufferData.DestinationTable.Data.First(x => x.Type == dests).IdDestination;
int idState;
int idFashion = bufferData.FashionTable.Data.First(x => x.Name == fashions).IdFashion;
int idType = bufferData.MaterialTypeTable.Data.First(x => x.Name == types).IdMaterialType;
int idMat = bufferData.MaterialTable.Data.First(
x => x.Name == mats &&
x.IdFashion == idFashion &&
x.IdMaterialType == idType).IdMaterial;
if ((StockCalculate.GetTotalExistMaterial(idMat) - amount) >= 0)
{
idState = 1;
}
else
{
idState = 2;
}
string query;
query = "Insert into orders (id_order, date, amount_material, id_material, id_destination, id_state) values (" +
id + ", '" +
date + "', '" +
amount + "', '" +
idMat + "', '" +
idDest + "', '" +
idState + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
return((idState == 1) ? true : false);
}
public static void UpdateOrders(int oldId, string ids, string dates, string amounts, string idMats, string idDests, string idStates)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string date = ValidateField.ValidateDate(dates);
int amount = ValidateField.ValidateNumber(amounts);
int idMat = ValidateField.ValidateNumber(idMats);
int idDest = ValidateField.ValidateNumber(idDests);
int idState = ValidateField.ValidateNumber(idStates);
if ((StockCalculate.GetTotalExistMaterial(idMat) - amount) >= 0)
{
idState = 1;
}
else
{
idState = 2;
}
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update orders set id_order = " + id +
", date = '" + date +
"', amount_material = '" + amount +
"', id_material = '" + idMat +
"', id_destination = '" + idDest +
"', id_state = '" + idState +
"' where id_order = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into orders (id_order, date, amount_material, id_material, id_destination, id_state) values (" +
id + ", '" +
date + "', '" +
amount + "', '" +
idMat + "', '" +
idDest + "', '" +
idState + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
public static void UpdateMaterial(int oldId, string ids, string names, string dates, string idManufacturers, string idMatTypes, string idFashions)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string name = ValidateField.ValidateString(names);
string date = ValidateField.ValidateDate(dates);
int idMan = ValidateField.ValidateNumber(idManufacturers);
int idMat = ValidateField.ValidateNumber(idMatTypes);
int idFashion = ValidateField.ValidateNumber(idFashions);
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update material set id_material = " + id +
", name = '" + name +
"', date_producing = '" + date +
"', id_manufacturer = '" + idMan +
"', id_material_type = '" + idMat +
"', id_fashion = '" + idFashion +
"' where id_material = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into material (id_material, name, date_producing, id_manufacturer, id_material_type, id_fashion) values (" +
id + ", '" +
name + "', '" +
date + "', '" +
idMan + "', '" +
idMat + "', '" +
idFashion + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
public static SystemUser.UsersType FindUser(string login, string password)
{
string querry = "Select * from users where login='******' and password='******';";
DataSet ds = DepotDbConnector.GetInstance().SelectQuerry(querry);
UserTable userTable = new UserTable();
string type;
try
{
type = userTable.DataSetToList(ds)
.First(x => (x.Login == login) && (x.Password == password))
.Type;
}
catch
{
throw new UnknownUserException("Неправильний логін або пароль");
}
switch(type)
{
case "admin": return SystemUser.UsersType.Admin;
default: return SystemUser.UsersType.Client;
}
}
public static void UpdateDestination(int oldId, string ids, string types)
{
BufferData bufferData = BufferData.GetInstance();
int id = ValidateField.ValidateKey(ids, oldId, bufferData.DestinationTable.GetIdList());
string type = ValidateField.ValidateString(types);
string query;
if (bufferData.EditMode == BufferData.EditModes.Update)
{
query = "Update destination set id_destination = " + id +
", type = '" + type + "' where id_destination = " + oldId + ";";
DepotDbConnector.GetInstance().UpdateQuerry(query);
}
if (bufferData.EditMode == BufferData.EditModes.Insert)
{
query = "Insert into destination (id_destination, type) values (" + id +
", '" + type + "');";
DepotDbConnector.GetInstance().InsertQuerry(query);
}
}
public void OpenTable(string tableName)
{
DepotDbConnector depotDbConnector = DepotDbConnector.GetInstance();
DataSet ds = depotDbConnector.SelectQuerry("SELECT * FROM " + tableName);
//MessageBox.Show(tableName);
switch (tableName)
{
case "state":
StateTable.DataSetToList(ds);
break;
case "destination":
DestinationTable.DataSetToList(ds);
break;
case "fashion":
FashionTable.DataSetToList(ds);
break;
case "manufacturer":
ManufacturerTable.DataSetToList(ds);
break;
case "material":
MaterialTable.DataSetToList(ds);
break;
case "material_type":
MaterialTypeTable.DataSetToList(ds);
break;
case "orders":
OrdersTable.DataSetToList(ds);
break;
case "received_material":
ReceivedMaterialTable.DataSetToList(ds);
break;
case "issued_materials":
IssuedMaterialsTable.DataSetToList(ds);
break;
case "materialview":
MaterialViewTable.DataSetToList(ds);
break;
case "issuedview":
IssuedViewTable.DataSetToList(ds);
break;
case "receivedview":
ReceivedViewTable.DataSetToList(ds);
break;
case "ordersview":
OrdersViewTable.DataSetToList(ds);
break;
}
}
