public Client Get(long id)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
using (_connection)
{
_connection.Open();
string sql = "select * from client where id = @id";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", id);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
Client client = new Client()
{
Id = (long)reader["id"],
Name = (string)reader["name"],
LastName = (string)reader["last_name"],
PhoneNumber = (string)reader["phone_number"]
};
return(client);
}
throw new Exception("Not found");
}
}
public Car Get(long id)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
using (_connection)
{
_connection.Open();
string sql = "select * from car where id = @id";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", id);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
Car car = new Car()
{
Id = (long)reader["id"],
ClientId = (long)reader["client_id"],
Number = (string)reader["number"],
Brand = (string)reader["brand"],
Year = (Int16)reader["manufacturer_year"]
};
return(car);
}
throw new Exception("Not found");
}
}
public Contract Update(long contractId, DateTime startDate, DateTime endDate)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
using (_connection)
{
string sql;
SqlCommand cmd;
_connection.Open();
if (endDate < DateTime.Parse("2000-01-01"))
{
sql = "update contract set end_date = null where id = @id";
cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", contractId);
}
else
{
sql = "update contract set start_date = @startDate, end_date = @endDate where id = @id";
cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", contractId);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
}
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
sql = "select * from contract where id = @id";
cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", contractId);
cmd.CommandType = CommandType.Text;
var reader = cmd.ExecuteReader();
if (reader.Read())
{
DateTime date = DateTime.MinValue;
try
{
date = (DateTime)reader["end_date"];
}
catch (Exception e)
{
}
Contract contract = new Contract()
{
ContractId = (long)reader["id"],
CarId = (long)reader["car_id"],
StartDate = (DateTime)reader["start_date"],
EndDate = date
};
return(contract);
}
}
throw new Exception("There is no contract with this id");
}
public int Delete(string phoneNumber)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
using (_connection)
{
_connection.Open();
string sql = "delete from client where phone_number = @phoneNumber";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
return(cmd.ExecuteNonQuery());
}
}
public IEnumerable <ContractService> GetAllByContract(long contractId)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
var result = new List <ContractService>();
using (_connection)
{
_connection.Open();
string sql = "select service_id, price, coefficient_id, coefficient, s.name as name, date_of_service from contract_services inner join coefficient c on contract_services.coefficient_id = c.id inner join service s on contract_services.service_id = s.id where contract_id = @contractId";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@contractId", contractId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime d = DateTime.MinValue;
try
{
d = (DateTime)reader["date_of_service"];
}
catch (Exception e)
{
}
ContractService service = new ContractService()
{
ServiceId = (long)reader["service_id"],
Price = (double)reader["price"],
CoefId = (long)reader["coefficient_id"],
Date = d,
Name = (string)reader["name"],
Coef = (double)reader["coefficient"]
};
result.Add(service);
}
return(result);
}
}
public IEnumerable <Contract> GetById(long id)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
var result = new List <Contract>();
using (_connection)
{
_connection.Open();
string sql =
"select * from contract where id = @id";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@id", id);
cmd.CommandType = CommandType.Text;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime date = DateTime.MinValue;
try
{
date = (DateTime)reader["end_date"];
}
catch (Exception e)
{
}
Contract contract = new Contract()
{
ContractId = (long)reader["id"],
CarId = (long)reader["car_id"],
StartDate = (DateTime)reader["start_date"],
EndDate = date
};
result.Add(contract);
}
}
return(result.AsEnumerable());
}
public Client Create(string name, string lastName, string phoneNumber)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
using (_connection)
{
_connection.Open();
string sql =
"insert into client (name, last_name, phone_number) values (@name, @lastName, @phoneNumber)";
SqlCommand cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@lastName", lastName);
cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
sql = "select * from client where phone_number = @phoneNumber";
cmd = new SqlCommand(sql, _connection);
cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
Client client = new Client()
{
Id = (long)reader["id"],
Name = (string)reader["name"],
LastName = (string)reader["last_name"],
PhoneNumber = (string)reader["phone_number"],
};
return(client);
}
throw new Exception("Phone number is already registered");
}
//todo check exception
}
public double GetContractPrice(long id)
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
double sum = 0;
using (_connection)
{
_connection.Open();
SqlCommand cmd = new SqlCommand("ServicesSum", _connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ContractId", id);
cmd.Parameters.AddWithValue("@Sum", sum).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
sum = Convert.ToDouble(cmd.Parameters["@Sum"].Value);
}
return(sum);
}
public EmployeeDao()
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
}
public ContractServicesDao()
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
}
public PartManufacturerDao()
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
}
public ServiceCoefficientDao()
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
}
public ClientDao()
{
_connection = new SqlConnection(DalConfiguration.GetSqlConnectionString());
}