public bool addToContract(int equioment_in_contract)
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
string saveQuery = string.Format("INSERT INTO equioments_in_contract(equioment_in_contract, id_equipment) "
+ "VALUES (@equioment_in_contract, @id_equipment); UPDATE equipments SET in_stock = @in_stock WHERE id_equipment = {0}", id_equipment);
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@equioment_in_contract", equioment_in_contract),
new DictionaryEntry("@id_equipment", id_equipment),
new DictionaryEntry("@in_stock", in_stock - 1)
))
{
db.dbClose(); return(false);
}
/* if (id_equipment < 0)
* {
* id_equipment = Convert.ToInt32(db.executeScalar("SELECT last_insert_rowid()"));
* }*/
db.dbClose();
return(true);
}
public bool delInContract(int equipment_in_contract)
{
db db = new db();
SQLiteDataReader dbRdr;
if (!db.dbOpen())
{
return(false);
}
List <int> id_equip_in_contr = new List <int>();
dbRdr = db.getReader(String.Format("SELECT id_equioment_in_contract FROM equioments_in_contract WHERE id_equipment = {0} AND equioment_in_contract = {1};", id_equipment, equipment_in_contract));
while (dbRdr.Read())
{
int id_equioment_in_contract = Convert.ToInt32(dbRdr["id_equioment_in_contract"]);
id_equip_in_contr.Add(id_equioment_in_contract);
}
dbRdr.Close();
string query = String.Format("DELETE FROM equioments_in_contract WHERE id_equipment = {0} AND equioment_in_contract = {1} AND id_equioment_in_contract = {2}; ", id_equipment, equipment_in_contract, id_equip_in_contr[0]);
if (!db.executeParamQuery(query,
new DictionaryEntry("@in_stock", in_stock + 1)
))
{
db.dbClose(); return(false);
}
db.dbClose();
return(true);
// "UPDATE equipments SET in_stock = @in_stock WHERE id_equipment = {0} "
}
public void getList(int id_client, int returned = -2, int period = -1)
{
db db;
SQLiteDataReader dbRdr;
viewHire viewItem;
viewHireList.Clear();
string query = "SELECT *,GROUP_CONCAT('Бренд: ' || brand ||' - '|| model || ' Цвет: ' || color || ' Размер: ' || size ) as all_equip FROM contracts_hire ";
query += "INNER JOIN equioments_in_contract ON contracts_hire.equipment_in_contract = equioments_in_contract.equioment_in_contract ";
query += "INNER JOIN equipments ON equioments_in_contract.id_equipment = equipments.id_equipment ";
query += "INNER JOIN clients ON contracts_hire.id_client = clients.id_client ";
List <string> wheres = new List <string>();
if (id_client > 0)
{
wheres.Add(String.Format("clients.id_client = {0} ", id_client));
}
if (returned != -2)
{
wheres.Add(String.Format("returned = {0} ", returned));
}
if (period != -1)
{
wheres.Add(String.Format("start_time >= {0} ", period));
}
if (wheres.Count > 0)
{
query += "WHERE " + string.Join(" AND ", wheres.ToArray());
}
query += "GROUP by equioments_in_contract.equioment_in_contract ";
query += "ORDER by start_time DESC ";
// query += "LIMIT 60 ";
db = new db();
try
{
if (!db.dbOpen())
{
throw new Exception("Ошибка подключения к БД.");
}
dbRdr = db.getReader(query);
while (dbRdr.Read())
{
viewItem = new viewHire();
viewItem.setView(dbRdr);
viewHireList.Add(viewItem);
}
dbRdr.Close();
db.dbClose();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { db.dbClose(); }
}
public void getEquipmentList(string searchByAllFields = "", int inStock = -2, int type = 0)
{
db db;
SQLiteDataReader dbRdr;
cEquipment e;
equipments.Clear();
string query = "SELECT * FROM equipments ";
List <string> wheres = new List <string>();
if (searchByAllFields != "")
{
wheres.Add(String.Format("(brand || model || color || size || number) like '%{0}%'", searchByAllFields));
}
if (inStock != -2)
{
wheres.Add(String.Format("in_stock >= '{0}'", inStock));
}
if (type != 0)
{
wheres.Add(String.Format("type = '{0}'", type));
}
if (wheres.Count > 0)
{
query += "WHERE " + string.Join(" AND ", wheres.ToArray());
}
db = new db();
try
{
if (!db.dbOpen())
{
throw new Exception("Ошибка подключения к БД.");
}
dbRdr = db.getReader(query);
while (dbRdr.Read())
{
e = new cEquipment();
e.setEquipment(dbRdr);
equipments.Add(e);
}
dbRdr.Close();
db.dbClose();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { db.dbClose(); }
}
public bool delete()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
if (!db.executeQuery("DELETE FROM equipments WHERE id_equipment = " + id_equipment))
{
db.dbClose(); return(false);
}
db.dbClose();
return(true);
}
public bool delete()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
if (!db.executeQuery("DELETE FROM contracts_hire WHERE id_contract_hire = " + id_contract_hire))
{
db.dbClose(); return(false);
}
db.dbClose();
return(true);
}
public static List <cType> getType()
{
List <cType> lst = new List <cType>();
SQLiteDataReader dbRdr;
db db = new db();
cType r;
if (!db.dbOpen())
{
throw new Exception("Ошибка подключения к базе данных.");
}
dbRdr = db.getReader("SELECT * FROM types");
if (dbRdr == null)
{
return(lst);
}
while (dbRdr.Read())
{
r = new cType();
r.setType(dbRdr);
lst.Add(r);
}
dbRdr.Close();
db.dbClose();
return(lst);
}
public cContractsHire(int id)
{
id_contract_hire = -1;
db db = new db();
SQLiteDataReader dbRdr;
try
{
if (!db.dbOpen())
{
throw new Exception();
}
dbRdr = db.getReader("SELECT * FROM contracts_hire WHERE id_contract_hire = " + id);
dbRdr.Read();
setHire(dbRdr);
dbRdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
id_contract_hire = -1;
}
finally { db.dbClose(); }
}
public void getClientList(string searchByAllFields = "")
{
db db;
SQLiteDataReader dbRdr;
cClient c;
clients.Clear();
string query = "SELECT * FROM clients ";
List <string> wheres = new List <string>();
if (searchByAllFields != "")
{
wheres.Add(String.Format("(fio || phone || address || passport) like '%{0}%'", searchByAllFields));
}
if (wheres.Count > 0)
{
query += "WHERE " + string.Join(" AND ", wheres.ToArray());
}
query += " ORDER BY fio";
db = new db();
try
{
if (!db.dbOpen())
{
throw new Exception("Ошибка подключения к БД.");
}
dbRdr = db.getReader(query);
while (dbRdr.Read())
{
c = new cClient();
c.setClient(dbRdr);
clients.Add(c);
}
dbRdr.Close();
db.dbClose();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { db.dbClose(); }
}
public cEquiomentsInContract()
{
db db = new db();
if (db.dbOpen())
{
id_equioment_in_contract = -1;//1 + Convert.ToInt32(db.executeScalar("SELECT COUNT(*) FROM equioments_in_contract"));
}
db.dbClose();
}
public bool save()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
string saveQuery = "";
if (id_equipment < 0)
{
saveQuery = getQuery("i");
}
else
{
saveQuery = getQuery("u");
}
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@brand", brand),
new DictionaryEntry("@model", model),
new DictionaryEntry("@color", color),
new DictionaryEntry("@size", size),
new DictionaryEntry("@number", number),
new DictionaryEntry("@cost", cost),
new DictionaryEntry("@in_stock", in_stock),
new DictionaryEntry("@type", type)
))
{
db.dbClose(); return(false);
}
if (id_equipment < 0)
{
id_equipment = Convert.ToInt32(db.executeScalar("SELECT last_insert_rowid()"));
}
db.dbClose();
return(true);
}
public cContractsHire()
{
id_contract_hire = -1;
returned = -1;
db db = new db();
if (db.dbOpen())
{
equipment_in_contract = 1 + Convert.ToInt32(db.executeScalar("SELECT equioment_in_contract FROM equioments_in_contract ORDER by equioment_in_contract DESC "));
}
db.dbClose();
}
public bool save()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
string saveQuery = "";
if (id_contract_hire < 0)
{
saveQuery = getQuery("i");
}
else
{
saveQuery = getQuery("u");
}
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@id_client", id_client),
new DictionaryEntry("@equipment_in_contract", equipment_in_contract),
new DictionaryEntry("@object_of_pledge", object_of_pledge),
new DictionaryEntry("@rent", rent),
new DictionaryEntry("@start_time", start_time),
new DictionaryEntry("@end_time", end_time),
new DictionaryEntry("@returned", returned)
))
{
db.dbClose(); return(false);
}
if (id_contract_hire < 0)
{
id_contract_hire = Convert.ToInt32(db.executeScalar("SELECT last_insert_rowid()"));
}
db.dbClose();
return(true);
}
public bool reduceStock()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
string saveQuery = string.Format("UPDATE equipments SET in_stock = @in_stock WHERE id_equipment = {0}", id_equipment);
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@in_stock", in_stock - 1)
))
{
db.dbClose(); return(false);
}
db.dbClose();
return(true);
}
public bool save()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
string saveQuery = "";
if (id_client < 0)
{
saveQuery = getQuery("i");
}
else
{
saveQuery = getQuery("u");
}
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@fio", fio),
new DictionaryEntry("@phone", phone),
new DictionaryEntry("@address", address),
new DictionaryEntry("@passport", passport)
))
{
db.dbClose(); return(false);
}
if (id_client < 0)
{
id_client = Convert.ToInt32(db.executeScalar("SELECT last_insert_rowid()"));
}
db.dbClose();
return(true);
}
public List <cEquipment> getEquipment()
{
List <cEquipment> equipList = new List <cEquipment>();
db db;
SQLiteDataReader dbRdr;
cEquipment equip;
equipList.Clear();
string query = "SELECT * FROM equipments ";
query += "INNER JOIN equioments_in_contract ON equipments.id_equipment = equioments_in_contract.id_equipment ";
query += String.Format("WHERE equioments_in_contract.equioment_in_contract = {0} ", equipment_in_contract);
db = new db();
try
{
if (!db.dbOpen())
{
throw new Exception("Ошибка подключения к БД.");
}
dbRdr = db.getReader(query);
while (dbRdr.Read())
{
equip = new cEquipment();
equip.setEquipment(dbRdr);
equipList.Add(equip);
}
dbRdr.Close();
db.dbClose();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка чтения из базы данных: " + ex.Message, "Ошибка базы данных", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { db.dbClose(); }
return(equipList);
}
public bool addStock()
{
db db = new db();
if (!db.dbOpen())
{
return(false);
}
in_stock = Convert.ToInt32(db.executeScalar(string.Format("SELECT in_stock FROM equipments WHERE id_equipment = {0}", id_equipment)));
string saveQuery = string.Format("UPDATE equipments SET in_stock = @in_stock WHERE id_equipment = {0}", id_equipment);
if (!db.executeParamQuery(saveQuery,
new DictionaryEntry("@in_stock", in_stock + 1)
))
{
db.dbClose(); return(false);
}
db.dbClose();
return(true);
}