private Sale InsertSale(Sale sale, SqlConnection connection)
{
string saleQuery = Utility.ReadSQLQueryFromFile("CreateSale.txt");
SQLQuery sQLQuery = new SQLQuery(saleQuery);
string customerID;
if (sale.customer == null)
{
customerID = null;
}
else
{
customerID = sale.customer.customerID.ToString();
}
sQLQuery.AddParameter("@customerid", customerID, SqlDbType.Int);
sQLQuery.AddParameter("@salesday", sale.salesDay.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery, connection);
if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0)
{
throw sQLQueryResult.exception;
}
else
{
int saleID = (int)sQLQueryResult.dataTable.Rows[0]["SaleID"];
sale.UpdateSaleID(saleID);
}
return(sale);
}
public List <Treatment> GetManyTreatmentsForSendingRminders(DateTime startTime, DateTime endTime)
{
string query = Utility.ReadSQLQueryFromFile("SendReminder.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@startTime", startTime.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime);
sQLQuery.AddParameter("@endTime", endTime.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new NoRemindersFoundException("1", sQLQueryResult.exception);
}
List <Treatment> treatments = new List <Treatment>();
treatments = GetTreatmentList(sQLQueryResult);
if (treatments.Count == 0)
{
throw new NoRemindersFoundException("2", sQLQueryResult.exception);
}
return(treatments);
}
public Prescription CreatePrescription(Prescription prescription)
{
string query = Utility.ReadSQLQueryFromFile("CreatePrescription.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@amount", prescription.amount.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@employeeID", prescription.employee.employeeID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@animalID", prescription.animal.animalID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@itemID", prescription.item.itemID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@prescriptionDay", prescription.prescriptionDay.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantCreatePrescription("", sQLQueryResult.exception);
}
int prescriptionID = (int)sQLQueryResult.dataTable.Rows[0]["PrescriptionID"];
prescription.UpdateID(prescriptionID);
return(prescription);
}
public string UpdateCustomer(Customer customer)
{
SqlConnection con = new SqlConnection(Utility.connectionString);
string query = Utility.ReadSQLQueryFromFile("UpdateCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@phone", customer.phone.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@name", customer.name.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@address", customer.address.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@email", customer.email.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@customerID", customer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return("Kunde rettet");
}
else
{
throw new CustomerNotUpdated("", sQLQueryResult.exception);
}
}
public string UpdateAnimal(Animal animal)
{
string query = Utility.ReadSQLQueryFromFile("UpdateAnimal.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@animalID", animal.animalID.ToString(), SqlDbType.Int);
if (animal.Employee == null)
{
sQLQuery.AddParameter("@employeeid", null, SqlDbType.Int);
}
else
{
sQLQuery.AddParameter("@employeeid", animal.Employee.employeeID.ToString(), SqlDbType.Int);
}
sQLQuery.AddParameter("@weight", animal.weight.ToString(), SqlDbType.Decimal);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return("ok");
}
else
{
throw new AnimalNotEditedException("", sQLQueryResult.exception);
}
}
public void UpdateMedicinePrice(int adapterSelector)
{
IMedicinePriceAdapter medicinPriceAdapter;
if (adapterSelector == 1)
{
medicinPriceAdapter = new MedicinePriceAdapter1();
}
else
{
medicinPriceAdapter = new MedicinePriceAdapter2();
}
DateTime UpdateTime = GetLastUpdate();
List <MedicinePrice> medicinePrices = medicinPriceAdapter.GetMedicinePrice();
if (medicinePrices[0].date > UpdateTime)
{
for (int i = 0; i < medicinePrices.Count; i++)
{
MedicinePrice medicinePrice = medicinePrices[i];
string query = Utility.ReadSQLQueryFromFile("UpdateMedicinCostPrice.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@name", medicinePrice.name.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@costprice", medicinePrice.price.ToString(), SqlDbType.Decimal);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
}
UpdateMedicinePriceDate(medicinePrices[0]);
}
}
public Customer GetCustomer(string phone)
{
Customer customer;
string query = Utility.ReadSQLQueryFromFile("GetCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@phone", phone, SqlDbType.VarChar);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0 && sQLQueryResult.dataTable.Rows.Count > 0)
{
DataRow dataRow = sQLQueryResult.dataTable.Rows[0];
customer = CustomerFactory.Instance().CreateCustomer((int)dataRow["CustomerID"], (string)dataRow["Name"], (string)dataRow["Adress"], (string)dataRow["Phone"], (string)dataRow["Email"], (bool)dataRow["Active"], (int)dataRow["cvr"]);
return(customer);
}
else
{
throw new NoCustomerFoundException("", sQLQueryResult.exception);
}
}
public Treatment CreateTreatment(Treatment treatment)
{
string query = Utility.ReadSQLQueryFromFile("CreateTreatment.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@startTime", treatment.startTime.ToString(), SqlDbType.DateTime);
sQLQuery.AddParameter("@endTime", treatment.endTime.ToString(), SqlDbType.DateTime);
sQLQuery.AddParameter("@payed", treatment.payed.ToString(), SqlDbType.Bit);
sQLQuery.AddParameter("@headline", treatment.headline.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@active", treatment.active.ToString(), SqlDbType.Bit);
sQLQuery.AddParameter("@treatmenttypeid", treatment.treatmentType.treatmentTypeID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@itemid", treatment.item.itemID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@animalid", treatment.animal.animalID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@status", treatment.status.ToString(), SqlDbType.Int);
if (treatment.employee == null)
{
sQLQuery.AddParameter("@employeeid", null, SqlDbType.Int);
}
else
{
sQLQuery.AddParameter("@employeeid", treatment.employee.employeeID.ToString(), SqlDbType.Int);
}
if (treatment.operationRoom == null)
{
sQLQuery.AddParameter("@operationroomid", null, SqlDbType.Int);
}
else
{
sQLQuery.AddParameter("@operationroomid", treatment.operationRoom.operationRoomID.ToString(), SqlDbType.Int);
}
if (treatment.cage == null)
{
sQLQuery.AddParameter("@cageid", null, SqlDbType.Int);
}
else
{
sQLQuery.AddParameter("@cageid", treatment.cage.CageID.ToString(), SqlDbType.Int);
}
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new TreatmentNotCreatedException("", sQLQueryResult.exception);
}
else
{
int treatmentID = (int)sQLQueryResult.dataTable.Rows[0]["TreatmentID"];
treatment.UpdateID(treatmentID);
return(treatment);
}
}
private Sale InsertSaleLineItemsInSale(Sale sale, SqlConnection connection)
{
string saleLineItemquery = Utility.ReadSQLQueryFromFile("CreateSaleLineItem.txt");
string updateItemAmountQuery = Utility.ReadSQLQueryFromFile("UpdateItemAmount.txt");
foreach (SaleLineItem saleLineItem in sale.saleLineItems)
{
SQLQuery sQLQuery = new SQLQuery(saleLineItemquery);
sQLQuery.AddParameter("@amount", saleLineItem.amount.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@price", saleLineItem.price.ToString(), SqlDbType.Decimal);
sQLQuery.AddParameter("@saleid", sale.saleID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@itemid", saleLineItem.item.itemID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery, connection);
if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0)
{
throw sQLQueryResult.exception;
}
else
{
if (saleLineItem.item.treatment == false)
{
int saleLineItemID = (int)sQLQueryResult.dataTable.Rows[0]["SaleLineItemsID"];
saleLineItem.UpdateSaleLineItemID(saleLineItemID);
SQLQuery updateAmountSQLQuery = new SQLQuery(updateItemAmountQuery);
updateAmountSQLQuery.AddParameter("@decreaseamount", saleLineItem.amount.ToString(), SqlDbType.Int);
updateAmountSQLQuery.AddParameter("@itemid", saleLineItem.item.itemID.ToString(), SqlDbType.Int);
sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateAmountSQLQuery, connection);
if (sQLQueryResult.code != 0)
{
throw sQLQueryResult.exception;
}
}
}
if (saleLineItem.treatment != null)
{
UpdateTreatmentAsPaid(saleLineItem.treatment, connection);
}
if (saleLineItem.prescription != null)
{
UpdatePrescriptionAsPaid(saleLineItem.prescription, connection);
}
}
return(sale);
}
private void UpdatePrescriptionAsPaid(Prescription prescription, SqlConnection connection)
{
string UpdateQuery = Utility.ReadSQLQueryFromFile("UpdatePrescriptionAsPaid.txt");
SQLQuery updateSQLQuery = new SQLQuery(UpdateQuery);
updateSQLQuery.AddParameter("@prescriptionid", prescription.prescriptionID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateSQLQuery, connection);
if (sQLQueryResult.code != 0)
{
throw sQLQueryResult.exception;
}
}
public List <Species> GetAllSpecies()
{
string query = Utility.ReadSQLQueryFromFile("GetSpecies.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
List <Species> allspecies = new List <Species>();
allspecies = GetSpecies(sQLQueryResult);
return(allspecies);
}
private void DeleteSale(Sale sale, SqlConnection connection)
{
string query = Utility.ReadSQLQueryFromFile("DeleteSale.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@saleid", sale.saleID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery, connection);
if (sQLQueryResult.code != 0)
{
throw sQLQueryResult.exception;
}
}
private void UpdateTreatmentAsPaid(Treatment treatment, SqlConnection connection)
{
string UpdateQuery = Utility.ReadSQLQueryFromFile("UpdateTreatmentAsPaid.txt");
SQLQuery updateSQLQuery = new SQLQuery(UpdateQuery);
updateSQLQuery.AddParameter("@treatmentid", treatment.treatmentID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateSQLQuery, connection);
if (sQLQueryResult.code != 0)
{
throw sQLQueryResult.exception;
}
}
private void CreatePrivateCustomer(PrivateCustomer privateCustomer)
{
string query = Utility.ReadSQLQueryFromFile("CreatePrivateCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@customerID", privateCustomer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantCreateCustomer("", sQLQueryResult.exception);
}
}
public List <Treatment> GetTreatment(int treatmentID)
{
string query = Utility.ReadSQLQueryFromFile("GetTreatment.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@treatmentID", treatmentID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
List <Treatment> treatments = new List <Treatment>();
treatments = GetTreatmentList(sQLQueryResult);
return(treatments);
}
public string DeleteTreatment(int treatmentID)
{
string query = Utility.ReadSQLQueryFromFile("DeleteTreatment.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@treatmentID", treatmentID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantDeleteTreatmentException("", sQLQueryResult.exception);
}
return("ok");
}
public DateTime GetLastUpdate()
{
string query = Utility.ReadSQLQueryFromFile("GetLastUpdate.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return((DateTime)sQLQueryResult.dataTable.Rows[0]["UpdateTime"]);
}
else
{
throw new DateNotFound("", sQLQueryResult.exception);
}
}
public string UpdateMedicinePriceDate(MedicinePrice medicinePrice)
{
string query = Utility.ReadSQLQueryFromFile("UpdateMedicinPriceDate.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@newdate", medicinePrice.date.ToString(), SqlDbType.DateTime);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return("ok");
}
else
{
throw new DateNotUpdated("", sQLQueryResult.exception);
}
}
public List <OperationRoom> GetAllOperationRooms()
{
string query = Utility.ReadSQLQueryFromFile("GetAllOperationRooms.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantFindOperationRoomsException("", sQLQueryResult.exception);
}
List <OperationRoom> operationRooms = new List <OperationRoom>();
operationRooms = GetAllOperationRooms(sQLQueryResult);
return(operationRooms);
}
private void CreateBusinessCustomer(BusinessCustomer businessCustomer)
{
SqlConnection con = new SqlConnection(Utility.connectionString);
string query = Utility.ReadSQLQueryFromFile("CreateBusinessCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@cvr", businessCustomer.cvr.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@businesscustomerID", businessCustomer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantCreateCustomer("", sQLQueryResult.exception);
}
}
public List <Cage> GetAllCages()
{
string query = Utility.ReadSQLQueryFromFile("GetAllCages.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantFindCagesException("", sQLQueryResult.exception);
}
List <Cage> cages = new List <Cage>();
cages = GetCageList(sQLQueryResult);
return(cages);
}
public Customer CreateCustomer(Customer customer)
{
SqlConnection con = new SqlConnection(Utility.connectionString);
string query = Utility.ReadSQLQueryFromFile("CreateCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@phone", customer.phone.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@name", customer.name.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@address", customer.address.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@email", customer.email.ToString(), SqlDbType.VarChar);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantCreateCustomer("", sQLQueryResult.exception);
}
int customerID = (int)sQLQueryResult.dataTable.Rows[0]["CustomerID"];
customer.UpdateID(customerID);
if (customer.GetType() == typeof(BusinessCustomer))
{
//der laves en BusinessCustomer som castes til customer
BusinessCustomer businessCustomer = (BusinessCustomer)customer;
CreateBusinessCustomer(businessCustomer);
}
if (customer.GetType() == typeof(PrivateCustomer))
{
//der laves en PrivateCustomer som castes til customer
PrivateCustomer privateCustomer = (PrivateCustomer)customer;
CreatePrivateCustomer(privateCustomer);
}
return(customer);
}
public string UndeleteCustomer(Customer customer)
{
string query = Utility.ReadSQLQueryFromFile("UndeleteCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
//af en eller anden grund virker det her fint med varchars og strings
sQLQuery.AddParameter("@customerID", customer.customerID.ToString(), SqlDbType.VarChar);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return("Kunde gjort aktiv");
}
else
{
return(sQLQueryResult.exception.Message.ToString());
}
}
public string DeleteAnimal(Animal animal)
{
string query = Utility.ReadSQLQueryFromFile("DeleteAnimal.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@animalid", animal.animalID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
return("ok");
}
else
{
throw new AnimalNotDeletedException("", sQLQueryResult.exception);
}
}
public List <Treatment> GetUnpaidTreatmentsByCustomer(Customer customer)
{
string query = Utility.ReadSQLQueryFromFile("GetUnpaidTreatmentsByCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantGetUnPaidTreatments("", sQLQueryResult.exception);
}
List <Treatment> treatments = new List <Treatment>();
treatments = GetTreatmentList(sQLQueryResult);
return(treatments);
}
public Sale CreateSale(Sale sale)
{
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection sqlConnection = SQLDatabaseConnector.OpenConnection())
{
sale = InsertSale(sale, sqlConnection);
sale = InsertSaleLineItemsInSale(sale, sqlConnection);
}
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new CantCreateSaleException("", e);
}
return(sale);
}
public string DeleteSale(Sale sale)
{
try
{
using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection sqlConnection = SQLDatabaseConnector.OpenConnection())
{
DeleteSaleLineItemsFromSale(sale, sqlConnection);
DeleteSale(sale, sqlConnection);
}
transactionScope.Complete();
}
}
catch (Exception e)
{
throw new CantDeleteSaleException("", e);
}
return("Sale deleted");
}
public List <Prescription> GetUnpaidPrescriptionByCustomer(Customer customer)
{
string query = Utility.ReadSQLQueryFromFile("GetUnpaidPrescriptionsByCustomer.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
List <Prescription> prescription = new List <Prescription>();
prescription = GetAllPrescriptionList(sQLQueryResult);
return(prescription);
}
else
{
throw new CantGetUnPaidPrescription("", sQLQueryResult.exception);
}
}
public List <Animal> GetManyAnimalByCustomerID(Customer customer)
{
string query = Utility.ReadSQLQueryFromFile("GetManyAnimalsByCustomerID.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@CustomerID", customer.customerID.ToString(), SqlDbType.VarChar);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
List <Animal> animals = new List <Animal>();
animals = GetAnimalList(sQLQueryResult);
return(animals);
}
else
{
throw new CantGetAnimalList("", sQLQueryResult.exception);
}
}
public List <Employee> GetAllEmployees()
{
List <Employee> employees = new List <Employee>();
string query = Utility.ReadSQLQueryFromFile("GetAllEmployees.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0)
{
throw new NoEmployeesFoundException("", sQLQueryResult.exception);
}
else
{
employees = GetEmployeeList(sQLQueryResult);
return(employees);
}
}