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