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);
}
//method to query database for single query
static public SQLQueryResult QueryDatabase(SQLQuery sQLQuery)
{
try
{
//initialize new DataTable
DataTable dataTable = new DataTable();
//start transaction scope
using (TransactionScope transactionScope = new TransactionScope())
{
//initialize new SqlConnection with connectionstring from Utility Class
using (SqlConnection connection = new SqlConnection(Utility.connectionString))
{
//open connection
connection.Open();
//initialize SqlCommand
using (SqlCommand command = new SqlCommand(sQLQuery.query, connection))
{
//add parameters to query if needed
foreach (SQLParameter sqlParameter in sQLQuery.sQLParameters)
{
if (sqlParameter.parameterValue == null)
{
command.Parameters.Add(sqlParameter.parameterName, sqlParameter.dataType).Value = DBNull.Value;
}
else
{
command.Parameters.Add(sqlParameter.parameterName, sqlParameter.dataType).Value = sqlParameter.parameterValue;
}
}
//execute sql to server and fill dataTable with returned result
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
sqlDataAdapter.Fill(dataTable);
}
}
//complete and commit transaction
transactionScope.Complete();
}
return(new SQLQueryResult(dataTable, 0, null));
}
catch (Exception e)
{
string test = e.GetType().ToString();
//return empty datatable and error code + exception if there were any issues
return(new SQLQueryResult(new DataTable(), 1, e));
}
}
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 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);
}
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 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);
}
}
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 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 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 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> GetManyTreatmentsByDateTime(DateTime startTime, DateTime endTime)
{
string query = Utility.ReadSQLQueryFromFile("GetManyTreatmentsByDateTime.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 CantFindTreatmentsException("", sQLQueryResult.exception);
}
List <Treatment> treatments = new List <Treatment>();
treatments = GetTreatmentList(sQLQueryResult);
return(treatments);
}
public List <TreatmentType> GetAllTreatmentTypes()
{
string query = Utility.ReadSQLQueryFromFile("GetManyTreatmentTypes.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0)
{
throw new NoTreatmentTypesFoundException("", sQLQueryResult.exception);
}
else
{
List <TreatmentType> treatments = new List <TreatmentType>();
treatments = GetTreatmentTypeList(sQLQueryResult);
return(treatments);
}
}
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 <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 <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);
}
}
public List <Item> GetAllActiveItems()
{
string query = Utility.ReadSQLQueryFromFile("GetAllActiveItems.txt");
SQLQuery sQLQuery = new SQLQuery(query);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code == 0)
{
List <Item> items = GetListOfItemsFromDatatable(sQLQueryResult.dataTable);
if (items.Count == 0)
{
throw new NoItemsFoundException("1");
}
return(items);
}
else
{
throw new NoItemsFoundException("", sQLQueryResult.exception);
}
}
public MedicalRecord CreateMedicalRecordEntry(MedicalRecord medicalRecord)
{
Animal animal = medicalRecord.animal;
Treatment treatment = medicalRecord.treatment;
string query = Utility.ReadSQLQueryFromFile("CreateMedicalRecordEntry.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@entry", medicalRecord.entry.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@animalID", animal.animalID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@treatmentID", treatment.treatmentID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
int medicalrecordID = (int)sQLQueryResult.dataTable.Rows[0]["MedicalRecordID"];
if (sQLQueryResult.code != 0)
{
throw new CantUpdateTreatmentException("", sQLQueryResult.exception);
}
medicalRecord.UpdateMedicalRecordID(medicalrecordID);
return(medicalRecord);
}
public List <Sale> GetManySalesByCustomerID(Customer customer)
{
List <Sale> sales;
string query = Utility.ReadSQLQueryFromFile("GetManySalesByCustomerID.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0)
{
return(new List <Sale>());
}
else
{
sales = GetSalesFromDataTable(sQLQueryResult.dataTable, customer);
}
return(sales);
}
public Animal CreateAnimal(Animal animal)
{
string query = Utility.ReadSQLQueryFromFile("CreateAnimal.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@customerid", animal.customer.customerID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@species", animal.Species.speciesID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@birthday", animal.birthYear.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime);
sQLQuery.AddParameter("@name", animal.name.ToString(), SqlDbType.VarChar);
sQLQuery.AddParameter("@weight", animal.weight.ToString(), SqlDbType.Float);
sQLQuery.AddParameter("@gender", animal.gender.ToString(), SqlDbType.Bit);
sQLQuery.AddParameter("@active", "True", SqlDbType.Bit);
if (animal.Employee == null)
{
sQLQuery.AddParameter("@employeeid", null, SqlDbType.Int);
}
else
{
sQLQuery.AddParameter("@employeeid", animal.Employee.employeeID.ToString(), SqlDbType.Int);
}
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
int animalID = (int)sQLQueryResult.dataTable.Rows[0]["AnimalID"];
animal.UpdateID(animalID);
if (sQLQueryResult.code == 0)
{
return(animal);
}
else
{
throw new AnimalNotCreatedException("", sQLQueryResult.exception);
}
}
public List <Prescription> GetAllPrescriptionByAnimal(int animalID)
{
string query = Utility.ReadSQLQueryFromFile("GetAllPrescriptionByAnimal.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@animalID", animalID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
List <Prescription> prescription = new List <Prescription>();
prescription = GetAllPrescriptionList(sQLQueryResult);
if (sQLQueryResult.code == 0)
{
return(prescription);
}
else
{
throw new PrescriptionNotFoundException("", sQLQueryResult.exception);
}
}
public List <MedicalRecord> GetAllMedicalRecordEntriesByAnimalID(Animal animal)
{
string query = Utility.ReadSQLQueryFromFile("GetAllMedicalRecordByAnimal.txt");
SQLQuery sQLQuery = new SQLQuery(query);
sQLQuery.AddParameter("@animalid", animal.animalID.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
List <MedicalRecord> entries = new List <MedicalRecord>();
entries = GetMedicalRecordEntryList(animal, sQLQueryResult);
if (sQLQueryResult.code == 0)
{
return(entries);
}
else
{
throw new MedicalRecordEntryNotFoundException("", sQLQueryResult.exception);
}
}
public string UpdateTreatment(Treatment treatment)
{
string query = Utility.ReadSQLQueryFromFile("UpdateTreatment.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("@treatmentID", treatment.treatmentID.ToString(), SqlDbType.Int);
sQLQuery.AddParameter("@status", treatment.status.ToString(), SqlDbType.Int);
SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery);
if (sQLQueryResult.code != 0)
{
throw new CantUpdateTreatmentException("", sQLQueryResult.exception);
}
return("ok");
}
