262 lines
10 KiB
C#
262 lines
10 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using ConstructionCompanyPsqlImplement.Models;
|
||
using ConstructionCompanyContracts.BindingModels;
|
||
using Microsoft.EntityFrameworkCore;
|
||
using Npgsql;
|
||
using ConstructionCompanyDataModels.Enums;
|
||
using System.Diagnostics;
|
||
using Microsoft.Extensions.Logging;
|
||
|
||
namespace ConstructionCompanyPsqlImplement
|
||
{
|
||
public class ConstructionCompanyDatabase
|
||
{
|
||
static string connectionString = "Server=192.168.1.35;Port=5432;Database=ConstructionCompanyForwardEngineerd;User Id=postgres;Password=postgres;";
|
||
|
||
private static ConstructionCompanyDatabase? _instance;
|
||
private List<Material> _materials = new List<Material>();
|
||
private List<Employee> _employees = new List<Employee>();
|
||
private List<Position> _positions = new List<Position>();
|
||
private List<Order> _orders = new List<Order>();
|
||
private List<EmployeeOrder> _employeeOrders = new List<EmployeeOrder>();
|
||
private List<MaterialOrder> _materialOrders = new List<MaterialOrder>();
|
||
public List<Material> Materials
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _materials;
|
||
}
|
||
}
|
||
public List<Employee> Employees
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _employees;
|
||
}
|
||
}
|
||
public List<Position> Positions
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _positions;
|
||
}
|
||
}
|
||
public List<Order> Orders
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _orders;
|
||
}
|
||
}
|
||
|
||
public List<EmployeeOrder> EmployeeOrders
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _employeeOrders;
|
||
}
|
||
}
|
||
|
||
public List<MaterialOrder> MaterialOrders
|
||
{
|
||
get
|
||
{
|
||
refreshDb();
|
||
return _materialOrders;
|
||
}
|
||
}
|
||
|
||
public static ConstructionCompanyDatabase GetInstance()
|
||
{
|
||
if (_instance == null)
|
||
{
|
||
_instance = new ConstructionCompanyDatabase();
|
||
}
|
||
return _instance;
|
||
}
|
||
|
||
public void ExecuteSql(string commandString)
|
||
{
|
||
using var connection = new NpgsqlConnection(connectionString);
|
||
connection.Open();
|
||
using var command = connection.CreateCommand();
|
||
command.CommandText = commandString;
|
||
command.ExecuteNonQuery();
|
||
refreshDb();
|
||
connection.Close();
|
||
}
|
||
|
||
public List<List<string>> ExecuteReader(string commandString, int numOfFields)
|
||
{
|
||
using var connection = new NpgsqlConnection(connectionString);
|
||
connection.Open();
|
||
|
||
using var commandMaterials = connection.CreateCommand();
|
||
commandMaterials.CommandText = commandString;
|
||
using var reader = commandMaterials.ExecuteReader();
|
||
List<List<string>> res = new List<List<string>>();
|
||
while (reader.Read())
|
||
{
|
||
List<string> item = new List<string>();
|
||
for (int i =0; i < numOfFields; i++)
|
||
{
|
||
item.Add(reader.GetValue(i).ToString());
|
||
}
|
||
res.Add(item);
|
||
}
|
||
return res;
|
||
}
|
||
|
||
private void refreshDb()
|
||
{
|
||
_materials.Clear();
|
||
_positions.Clear();
|
||
_employees.Clear();
|
||
_orders.Clear();
|
||
_employeeOrders.Clear();
|
||
_materialOrders.Clear();
|
||
using var connection = new NpgsqlConnection(connectionString);
|
||
connection.Open();
|
||
|
||
using var commandMaterials = connection.CreateCommand();
|
||
commandMaterials.CommandText = "SELECT * FROM material;";
|
||
Stopwatch stopwatch = new Stopwatch();
|
||
stopwatch.Start();
|
||
using var readerMaterials = commandMaterials.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long materialsTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerMaterials.Read())
|
||
{
|
||
int id = readerMaterials.GetInt32(0);
|
||
string name = readerMaterials.GetString(1);
|
||
int quantity = readerMaterials.GetInt32(2);
|
||
Material? mat = Material.Create(new MaterialBindingModel { Id = id, MaterialName = name, Quantity = quantity });
|
||
if (mat != null) _materials.Add(mat);
|
||
}
|
||
readerMaterials.Close();
|
||
|
||
using var commandPositions = connection.CreateCommand();
|
||
commandPositions.CommandText = "SELECT * FROM position;";
|
||
stopwatch.Restart();
|
||
using var readerPositions = commandPositions.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long positionsTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerPositions.Read())
|
||
{
|
||
int id = readerPositions.GetInt32(0);
|
||
string name = readerPositions.GetString(1);
|
||
double salary = readerPositions.GetDouble(2);
|
||
Position? position = Position.Create(new PositionBindingModel { Id = id, PositionName = name, Salary = salary });
|
||
if (position != null) _positions.Add(position);
|
||
}
|
||
readerPositions.Close();
|
||
|
||
using var commandEmployees = connection.CreateCommand();
|
||
commandEmployees.CommandText = "SELECT * FROM employee;";
|
||
stopwatch.Restart();
|
||
using var readerEmployees = commandEmployees.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long employeesTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerEmployees.Read())
|
||
{
|
||
int id = readerEmployees.GetInt32(0);
|
||
string name = readerEmployees.GetString(1);
|
||
int positionId = readerEmployees.GetInt32(2);
|
||
Employee? employee = Employee.Create(new EmployeeBindingModel { Id = id, EmployeeName = name, PositionID = positionId }, _positions);
|
||
if (employee != null) _employees.Add(employee);
|
||
}
|
||
readerEmployees.Close();
|
||
|
||
using var commandOrders = connection.CreateCommand();
|
||
commandOrders.CommandText = "SELECT * FROM \"order\";";
|
||
stopwatch.Restart();
|
||
using var readerOrders = commandOrders.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long ordersTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerOrders.Read())
|
||
{
|
||
int id = readerOrders.GetInt32(0);
|
||
string description = readerOrders.GetString(1);
|
||
string adress = readerOrders.GetString(2);
|
||
double price = readerOrders.GetDouble(3);
|
||
string statusString = readerOrders.GetString(4);
|
||
OrderStatus status;
|
||
switch (statusString)
|
||
{
|
||
case "Принят":
|
||
status = OrderStatus.Принят;
|
||
break;
|
||
case "Выполняется":
|
||
status = OrderStatus.Выполняется;
|
||
break;
|
||
case "Завершён":
|
||
status = OrderStatus.Завершён;
|
||
break;
|
||
default:
|
||
status = OrderStatus.Неизвестен;
|
||
break;
|
||
}
|
||
string customerNumber = readerOrders.GetString(5);
|
||
DateTime dateBegin = readerOrders.GetDateTime(6);
|
||
Order? order;
|
||
if (status == OrderStatus.Завершён)
|
||
{
|
||
DateTime? dateEnd = readerOrders.GetDateTime(7);
|
||
order = Order.Create(new OrderBindingModel { Id = id, Description = description, Adress = adress, Price = price, Status = status, CustomerNumber = customerNumber, DateBegin = dateBegin, DateEnd = dateEnd });
|
||
}
|
||
else
|
||
{
|
||
order = Order.Create(new OrderBindingModel { Id = id, Description = description, Adress = adress, Price = price, Status = status, CustomerNumber = customerNumber, DateBegin = dateBegin});
|
||
}
|
||
if (order != null) _orders.Add(order);
|
||
}
|
||
readerOrders.Close();
|
||
|
||
using var commandEmployeeOrders = connection.CreateCommand();
|
||
|
||
commandEmployeeOrders.CommandText = "SELECT * FROM employee_order;";
|
||
|
||
stopwatch.Restart();
|
||
using var readerEmployeeOrders = commandEmployeeOrders.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long employeeOrderTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerEmployeeOrders.Read())
|
||
{
|
||
int employeeId = readerEmployeeOrders.GetInt32(0);
|
||
int orderId = readerEmployeeOrders.GetInt32(1);
|
||
EmployeeOrder? employeeOrder = EmployeeOrder.Create(new EmployeeOrderBindingModel { EmployeeId = employeeId, OrderId = orderId}, _employees, _orders);
|
||
if (employeeOrder != null) _employeeOrders.Add(employeeOrder);
|
||
}
|
||
readerEmployeeOrders.Close();
|
||
|
||
using var commandMaterialOrders = connection.CreateCommand();
|
||
commandMaterialOrders.CommandText = "SELECT * FROM material_order;";
|
||
stopwatch.Restart();
|
||
using var readerMaterialOrders = commandMaterialOrders.ExecuteReader();
|
||
stopwatch.Stop();
|
||
long materialOrderTime = stopwatch.ElapsedMilliseconds;
|
||
while (readerMaterialOrders.Read())
|
||
{
|
||
int materialId = readerMaterialOrders.GetInt32(0);
|
||
int orderId = readerMaterialOrders.GetInt32(1);
|
||
int quantity = readerMaterialOrders.GetInt32(2);
|
||
MaterialOrder? materialOrder = MaterialOrder.Create(new MaterialOrderBindingModel { MaterialId = materialId, OrderId = orderId, Quantity = quantity}, _materials, _orders);
|
||
if (materialOrder != null) _materialOrders.Add(materialOrder);
|
||
}
|
||
readerMaterialOrders.Close();
|
||
|
||
connection.Close();
|
||
|
||
}
|
||
}
|
||
}
|