/// <summary>
/// Remove prescription from Database
/// </summary>
/// <param name="id"> Prescription's id </param>
public override void Remove(int id)
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"DELETE FROM Prescriptions WHERE PrescriptionID = @id;";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully removed", ConsoleColor.Green);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Show all prescriptions in console
/// </summary>
public override void ShowAll()
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText = @"SELECT * FROM Prescriptions";
var data = sqlCommand.ExecuteReader();
while (data.HasRows && data.Read())
{
Console.WriteLine(
$"ID: {data["PrescriptionID"].ToString().PadRight(4)} " +
$"| Customer Name: {data["CustomerName"].ToString().PadRight(20)} " +
$"| PESEL: {data["PESEL"]} " +
$"| Prescription Number: {data["PrescriptionNumber"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Show all medicines in console
/// </summary>
public override void ShowAll()
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText = @"SELECT * FROM Medicines";
var data = sqlCommand.ExecuteReader();
while (data.HasRows && data.Read())
{
Console.WriteLine(
$"ID: {data["MedicineID"].ToString().PadRight(4)} " +
$"| Name: {data["Name"].ToString().PadRight(20)} " +
$"| Manufacturer: {data["Manufacturer"].ToString().PadRight(20)} " +
$"| Price: {data["Price"].ToString().PadRight(6)} " +
$"| Amount: {data["Amount"].ToString().PadRight(6)} " +
$"| WithPrescription: {data["WithPrescription"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Check if medicine is with prescription
/// </summary>
/// <param name="medId"> Medicine's id </param>
/// <returns> bool:true/false </returns>
public bool CheckWithPrescription(int medId)
{
bool result = false;
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText = @"SELECT WithPrescription FROM Medicines WHERE MedicineID=@id";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = medId,
ParameterName = "@id"
};
sqlCommand.Parameters.Add(sqlIdParam);
result = (bool)sqlCommand.ExecuteScalar();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(result);
}
/// <summary>
/// Check medicine's id from order
/// </summary>
/// <param name="id"> Order's id </param>
/// <returns> Medicine's id </returns>
static int CheckMedId(int id)
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText = @"SELECT MedicineID FROM Orders WHERE ID=@id";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
sqlCommand.Parameters.Add(sqlIdParam);
int medID = (int)sqlCommand.ExecuteScalar();
return(medID);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(0);
}
/// <summary>
/// Stock change on medicine
/// </summary>
/// <param name="amount"> Stock amount </param>
/// <param name="medId"> Medicine's id </param>
public void ChangeStock(int amount, int medId)
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"UPDATE Medicines SET Amount = @Amount WHERE MedicineID = @MedicineID;";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = medId,
ParameterName = "@MedicineID"
};
var sqlAmountParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = amount,
ParameterName = "@Amount"
};
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.Parameters.Add(sqlAmountParam);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Remove order from Database
/// </summary>
/// <param name="id"> Order's id </param>
public override void Remove(int id)
{
int medId = CheckMedId(id);
int amount = CheckMedicineStock(medId);
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"DELETE FROM Orders WHERE ID = @id;
UPDATE Medicines SET Amount = Amount + @Am WHERE MedicineID = @medId;";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
var sqlAmParam = new SqlParameter()
{
DbType = System.Data.DbType.Int32,
Value = amount,
ParameterName = "@Am"
};
var sqlmedId = new SqlParameter()
{
DbType = System.Data.DbType.Int32,
Value = medId,
ParameterName = "@medId"
};
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.Parameters.Add(sqlAmParam);
sqlCommand.Parameters.Add(sqlmedId);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully removed", ConsoleColor.Green);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Display prescription from Database
/// </summary>
/// <param name="id"> Prescription's id </param>
public void ShowPrescription(int id)
{
try
{
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText = @"SELECT * FROM Prescriptions WHERE PrescriptionID = @id ";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
sqlCommand.Parameters.Add(sqlIdParam);
var data = sqlCommand.ExecuteReader();
while (data.HasRows && data.Read())
{
Console.WriteLine(
$"Prescription ID: {data["PrescriptionID"].ToString().PadRight(4)} " +
$"| Customer Name: {data["CustomerName"].ToString().PadRight(10)} " +
$"| PESEL: {data["PESEL"]}");
}
}
Console.WriteLine("");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Save prescription in Database
/// </summary>
public override void Save()
{
do
{
try
{
Console.WriteLine("Customer Name: ");
string name = Console.ReadLine();
Console.WriteLine("PESEL: ");
string pesel = Console.ReadLine();
Console.WriteLine("Prescription number: ");
int presNumber = Int32.Parse(Console.ReadLine());
Prescription pres = new Prescription(name, pesel, presNumber);
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"INSERT INTO Prescriptions (CustomerName, PESEL, PrescriptionNumber)
VALUES (@CustomerName, @PESEL, @PrescriptionNumber);" ;
var sqlCustomerNameParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = pres.CustomerName,
ParameterName = "@CustomerName"
};
var sqlPeselParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = pres.PESEL,
ParameterName = "@PESEL"
};
var sqlPrescriptionNumberParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = pres.PrescriptionNumber,
ParameterName = "@PrescriptionNumber"
};
sqlCommand.Parameters.Add(sqlCustomerNameParam);
sqlCommand.Parameters.Add(sqlPeselParam);
sqlCommand.Parameters.Add(sqlPrescriptionNumberParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully added", ConsoleColor.Green);
Console.WriteLine("Do you want to add another? [yes/no]");
string ans = Console.ReadLine();
if (ans.ToLower() == "no")
{
break;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (true);
}
/// <summary>
/// Reload prescription in Database
/// </summary>
/// <param name="id"> Prescription's id </param>
public override void Reload(int id)
{
try
{
Console.WriteLine("Customer Name: ");
string name = Console.ReadLine();
Console.WriteLine("PESEL: ");
string pesel = Console.ReadLine();
Console.WriteLine("Prescription number: ");
int presNumber = Int32.Parse(Console.ReadLine());
Prescription pres = new Prescription(name, pesel, presNumber);
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"UPDATE Prescriptions SET CustomerName = @CustomerName, PESEL = @PESEL, PrescriptionNumber = @PrescriptionNumber WHERE PrescriptionID = @id;";
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
var sqlCustomerNameParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = pres.CustomerName,
ParameterName = "@CustomerName"
};
var sqlPeselParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = pres.PESEL,
ParameterName = "@PESEL"
};
var sqlPrescriptionNumberParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = pres.PrescriptionNumber,
ParameterName = "@PrescriptionNumber"
};
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.Parameters.Add(sqlCustomerNameParam);
sqlCommand.Parameters.Add(sqlPeselParam);
sqlCommand.Parameters.Add(sqlPrescriptionNumberParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully edited", ConsoleColor.Green);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Save medicine to Database
/// </summary>
public override void Save()
{
do
{
try
{
Console.WriteLine("Medicine Name: ");
string name = Console.ReadLine();
Console.WriteLine("Manufacturer: ");
string manufacturer = Console.ReadLine();
Console.WriteLine("Price: ");
decimal price = Decimal.Parse(Console.ReadLine());
Console.WriteLine("Amount: ");
int amount = Int32.Parse(Console.ReadLine());
Console.WriteLine("With Prescription?(True/False):");
bool withPrescription = bool.Parse(Console.ReadLine());
Medicine med = new Medicine(name, manufacturer, price, amount, withPrescription);
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"INSERT INTO Medicines (Name, Manufacturer, Price, Amount, WithPrescription)
VALUES (@Name, @Manufacturer, @Price, @Amount, @WithPrescription);" ;
var sqlNameParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = med.Name,
ParameterName = "@Name"
};
var sqlManufacturerParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = med.Manufacturer,
ParameterName = "@Manufacturer"
};
var sqlPriceParam = new SqlParameter
{
DbType = System.Data.DbType.Decimal,
Value = med.Price,
ParameterName = "@Price"
};
var sqlAmountParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = med.Amount,
ParameterName = "@Amount"
};
var sqlWithPrescriptionParam = new SqlParameter
{
DbType = System.Data.DbType.Boolean,
Value = med.WithPrescription,
ParameterName = "@WithPrescription"
};
sqlCommand.Parameters.Add(sqlNameParam);
sqlCommand.Parameters.Add(sqlManufacturerParam);
sqlCommand.Parameters.Add(sqlPriceParam);
sqlCommand.Parameters.Add(sqlAmountParam);
sqlCommand.Parameters.Add(sqlWithPrescriptionParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully added", ConsoleColor.Green);
Console.WriteLine("Do you want to add another? [yes/no]");
string ans = Console.ReadLine();
if (ans.ToLower() == "no")
{
break;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (true);
}
/// <summary>
/// Change medicine from Database
/// </summary>
/// <param name="id"> Medicine's id </param>
public override void Reload(int id)
{
try
{
Console.WriteLine("Medicine Name: ");
string name = Console.ReadLine();
Console.WriteLine("Manufacturer: ");
string manufacturer = Console.ReadLine();
Console.WriteLine("Price: ");
decimal price = Decimal.Parse(Console.ReadLine());
Console.WriteLine("Amount: ");
int amount = Int32.Parse(Console.ReadLine());
Console.WriteLine("With Prescription?(True/False): ");
bool withPrescription = bool.Parse(Console.ReadLine());
var med = new Medicine(name, manufacturer, price, amount, withPrescription);
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"UPDATE Medicines SET Name = @Name, Manufacturer = @Manufacturer, Price = @Price, Amount = @Amount, WithPrescription = @WithPrescription
WHERE MedicineID = @id;" ;
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
var sqlNameParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = med.Name,
ParameterName = "@Name"
};
var sqlManufacturerParam = new SqlParameter
{
DbType = System.Data.DbType.AnsiString,
Value = med.Manufacturer,
ParameterName = "@Manufacturer"
};
var sqlPriceParam = new SqlParameter
{
DbType = System.Data.DbType.Decimal,
Value = med.Price,
ParameterName = "@Price"
};
var sqlAmountParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = med.Amount,
ParameterName = "@Amount"
};
var sqlWithPrescriptionParam = new SqlParameter
{
DbType = System.Data.DbType.Boolean,
Value = med.WithPrescription,
ParameterName = "@WithPrescription"
};
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.Parameters.Add(sqlNameParam);
sqlCommand.Parameters.Add(sqlManufacturerParam);
sqlCommand.Parameters.Add(sqlPriceParam);
sqlCommand.Parameters.Add(sqlAmountParam);
sqlCommand.Parameters.Add(sqlWithPrescriptionParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully edited", ConsoleColor.Green);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Reload order in Database
/// </summary>
/// <param name="id"> Order's id </param>
public override void Reload(int id)
{
try
{
var sqlCommand = new SqlCommand();
sqlCommand.CommandText = @"UPDATE Orders SET MedicineID = @MedicineID, Amount = @Amount WHERE ID = @id";
int?presID = null;
Console.WriteLine("MedicineID: ");
int medID = Int32.Parse(Console.ReadLine());
if (new Medicine().CheckWithPrescription(medID) == true)
{
Console.WriteLine("Prescription ID: ");
presID = Int32.Parse(Console.ReadLine());
sqlCommand.CommandText =
@"UPDATE Orders SET PrescriptionID = @PrescriptionID, MedicineID = @MedicineID, Amount = @Amount WHERE ID = @id;";
var sqlPrescriptionIDParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = presID,
ParameterName = "@PrescriptionID"
};
sqlCommand.Parameters.Add(sqlPrescriptionIDParam);
}
Console.WriteLine("Amount: ");
int amount = Int32.Parse(Console.ReadLine());
Order ord = new Order(presID, medID, amount);
int oldAmount = CheckOrderAmount(id);
int currentAmount = CheckMedicineStock(medID);
using (var connection = ActiveRecord.Open())
{
sqlCommand.Connection = connection;
var sqlMedicineIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = ord.MedicineID,
ParameterName = "@MedicineID"
};
var sqlIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = id,
ParameterName = "@id"
};
var sqlAmountParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = ord.Amount,
ParameterName = "@Amount"
};
sqlCommand.Parameters.Add(sqlMedicineIdParam);
sqlCommand.Parameters.Add(sqlIdParam);
sqlCommand.Parameters.Add(sqlAmountParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully edited", ConsoleColor.Green);
}
int correctAmount = currentAmount + (oldAmount - amount);
new Medicine().ChangeStock(correctAmount, medID);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// Save Order without prescription in Database
/// </summary>
public void SaveWithoutPrescription()
{
do
{
try
{
Console.WriteLine("MedicineID: ");
int medID = Int32.Parse(Console.ReadLine());
Console.WriteLine($"Avaiable stock: {CheckMedicineStock(medID)}");
Console.WriteLine("Amount: ");
int amount = Int32.Parse(Console.ReadLine());
if (amount > CheckMedicineStock(medID))
{
throw new OutOfMemoryException("Order could not be realised becuase the amount is to high.");
}
Order ord = new Order(null, medID, Date, amount);
using (var connection = ActiveRecord.Open())
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandText =
@"INSERT INTO Orders (MedicineID, Date, Amount)
VALUES (@MedicineID, @Date, @Amount);
UPDATE Medicines SET Amount = Amount - @Amount WHERE MedicineID = @MedicineID;";
var sqlMedicineIdParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = ord.MedicineID,
ParameterName = "@MedicineID"
};
var sqlDateParam = new SqlParameter
{
DbType = System.Data.DbType.DateTime,
Value = ord.Date,
ParameterName = "@Date"
};
var sqlAmountParam = new SqlParameter
{
DbType = System.Data.DbType.Int32,
Value = ord.Amount,
ParameterName = "@Amount"
};
sqlCommand.Parameters.Add(sqlMedicineIdParam);
sqlCommand.Parameters.Add(sqlDateParam);
sqlCommand.Parameters.Add(sqlAmountParam);
sqlCommand.ExecuteNonQuery();
ConsoleEx.WriteLine("Successfully added", ConsoleColor.Green);
Console.WriteLine("Do you want to add another? [yes/no]");
string ans = Console.ReadLine();
if (ans.ToLower() == "no")
{
break;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (true);
}
