Example #1
0
        /// <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();
        }
Example #2
0
        /// <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();
        }
Example #3
0
        /// <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();
        }
Example #4
0
        /// <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);
        }
Example #5
0
        /// <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);
        }
Example #6
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);
            }
        }
Example #7
0
        /// <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();
        }
Example #8
0
        /// <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);
            }
        }
Example #9
0
        /// <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);
        }
Example #10
0
        /// <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();
        }
Example #11
0
        /// <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);
        }
Example #12
0
        /// <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();
        }
Example #13
0
        /// <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();
        }
Example #14
0
        /// <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);
        }