Lab Report Add C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data.SQLite;
using Newtonsoft.Json;
namespace Checkup_Diagnostic
{
    public partial class AddTestReport : Form
    {
        //user data
        private int userId;
        private string userName;
        private string fullName;
        private int userRole;
        private string loginTime;
        // end of user data

        private CurrentUser CU = new CurrentUser();
        private LabratoryHelper LH = new LabratoryHelper();
        System.Drawing.Text.PrivateFontCollection privateFonts;
        private Font _customFont;
        private string conString = "SERVER=localhost;" + "DATABASE=dms;" +
      "UID=dms;" + "PASSWORD=11223344;";

        /* Tables */
        private string tablePatients = "patients";
        private string tableDoctors = "doctors";
        private string tableUsers = "users";
        private string tableAppointments = "appointments";
        private string tableReferences = "reference";


        private List<CartItem> LabCart = new List<CartItem>();

        public AddTestReport()
        {
            InitializeComponent();
            loadUserInfo();
            CU.LoadInfo();
            privateFonts = new System.Drawing.Text.PrivateFontCollection();
            privateFonts.AddFontFile("fonts/open-sans/OpenSans-Regular.ttf");
        }

        private void Labratory_Load(object sender, EventArgs e)
        {
             CU.LoadInfo(); //Load current userInfo
             loadPatient(); //Load Patinet list to dropdown
             cbxTest.Enabled = false;
             button1.Enabled = false;
             btnProceed.Enabled = false;

            //default value
             tbxSubTotal.Text = "0";
             tbxDiscount.Text = "0";
             tbxTotal.Text = "0";

        }

        private void button1_Click(object sender, EventArgs e)
        {
           if(is_valid()){
               LH.loadUserInfo(int.Parse(((ComboBoxItem)cbxTest.SelectedItem).HiddenValue.ToString()));


            CartItem CartItem = new CartItem() {
                ID = LH.ItemId,
                TestName = LH.ItemName,
                Category = LH.Category,
                ItemPrice = LH.ItemPrice


                };

            LabCart.Add(CartItem);
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = LabCart;
               tbxDiscount.Enabled = true;
               btnProceed.Enabled = true;


                   decimal subtotal = LabCart.Sum(x => x.ItemPrice);
                  // MessageBox.Show(subtotal.ToString());
                   decimal number;
                  // decimal discount;

                   tbxSubTotal.Text = subtotal.ToString();

                       if (decimal.TryParse(tbxDiscount.Text.Trim(), out number))
                       {
                           decimal total = subtotal - number;
                           tbxTotal.Text = total.ToString();
                       }
                   else{
                       tbxTotal.Text = subtotal.ToString();
                   }

              // MessageBox.Show();
           }






        }

        private bool is_valid()
        {

            if (cbxPatients.SelectedIndex == -1)
            {
                MessageBox.Show("Select Patient  Field is required", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cbxPatients.Focus();
                return false;
            }



            if (cbxTest.SelectedIndex == -1)
            {
                MessageBox.Show("Select Test  Field is required", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cbxPatients.Focus();
                return false;
            }

            return true;
        }

        private void loadPatient()
        {
            MySqlConnection connection = new MySqlConnection(this.conString);
            MySqlCommand command = connection.CreateCommand();


            MySqlDataReader Reader;


            command.CommandText = "SELECT * FROM `patients` WHERE `status`= 1 ORDER BY `patient_id` DESC";



            connection.Open();

            Reader = command.ExecuteReader();
            while (Reader.Read())
            {

                cbxPatients.Items.Add(new ComboBoxItem(Reader.GetValue(1).ToString(), Reader.GetValue(0).ToString()));


            }
            command.Cancel();
            Reader.Close();
            connection.Close();
        }


        private void cbxPatients_SelectedIndexChanged(object sender,
        System.EventArgs e)
        {
            cbxPatients.Enabled = false;
            cbxTest.Enabled = true;
            button1.Enabled = true;
            loadTests();

        }

        private void loadTests()
        {
            MySqlConnection connection = new MySqlConnection(this.conString);
            MySqlCommand command = connection.CreateCommand();


            MySqlDataReader Reader;


            command.CommandText = "SELECT `item_id`,`item_name` FROM `test_items` WHERE `status` = 1 ORDER BY `item_id` DESC";



            connection.Open();

            Reader = command.ExecuteReader();
            while (Reader.Read())
            {


                cbxTest.Items.Add(new ComboBoxItem(Reader.GetValue(1).ToString(), Reader.GetValue(0).ToString()));


            }
            command.Cancel();
            Reader.Close();
            connection.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var confirmResult = MessageBox.Show("Are you sure to cancel the report?",
                                    "Confirm Cancel!!",
                                    MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (confirmResult == DialogResult.Yes)
            {


            }
            else
            {
                //do nothing
            }
        }

        private void tbxDiscountOnChange(object sender, EventArgs e)
        {

            if(is_valid_decimal()){
                decimal number;

                if (decimal.TryParse(tbxDiscount.Text.Trim().ToString(), out number))
                {
                    decimal total = decimal.Parse(tbxSubTotal.Text.Trim()) - number;
                    tbxTotal.Text = total.ToString();
                }
            }

        }

        private bool is_valid_decimal()
        {
            decimal number;

            if (tbxDiscount.Text.Trim() != string.Empty)
            {
                if (!decimal.TryParse(tbxDiscount.Text.Trim().ToString(), out number))
                {
                    MessageBox.Show("Only decimal number is allowed", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    tbxDiscount.Text = "0";
                    tbxDiscount.Focus();
                    return false;
                }
            }

            return true;
        }

        private void printPreviewDialog1_Load(object sender, EventArgs e)
        {

        }

        private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {
            Font _customFont = new Font(privateFonts.Families[0], 12, FontStyle.Regular);
            //e.Graphics.DrawImage(Checkup_Diagnostic.Properties.Resources.mac_book_pro, 10, 10, Checkup_Diagnostic.Properties.Resources.mac_book_pro.Width, Checkup_Diagnostic.Properties.Resources.mac_book_pro.Height);
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Calibri", 14, FontStyle.Regular), Brushes.Black, new Point(10, 100));

            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Arial", 14, FontStyle.Regular), Brushes.Black, new Point(10, 120));
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Times New Roman", 14, FontStyle.Regular), Brushes.Black, new Point(10, 140));
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Calibri", 14, FontStyle.Regular), Brushes.Black, new Point(10, 160));
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Tahoma", 14, FontStyle.Regular), Brushes.Black, new Point(10, 180));
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", new Font("Georgia", 14, FontStyle.Regular), Brushes.Black, new Point(10, 200));
            e.Graphics.DrawString("Hello World! Welcome to C# Programming. I am very happy to be a C# programmer.", _customFont, Brushes.Black, new Point(10, 220));



        }

        private void button3_MouseClick(object sender, MouseEventArgs e)
        {


        }


        private void btnProceed_Click(object sender, EventArgs e)
        {
           // String jsonData = JsonConvert.SerializeObject(LabCart);


           /**
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                MessageBox.Show(row.Cells["ID"].Value.ToString());
            }
            **/

                int PatientId = int.Parse(((ComboBoxItem)cbxPatients.SelectedItem).HiddenValue);
                int TestId;

                MySqlConnection con = new MySqlConnection(this.conString);
                MySqlCommand command = con.CreateCommand();
                command.CommandText = "INSERT INTO `lab_report`(`report_patient`, `report_subtotal`, `report_discount`, `added_by`, `time_added`,  `status`) VALUES ( @patient , @subtotal , @discount , @user , @time , @status )";

                command.Parameters.AddWithValue("@patient", PatientId);
                command.Parameters.AddWithValue("@subtotal", double.Parse(tbxSubTotal.Text.ToString()));
                command.Parameters.AddWithValue("@discount", double.Parse(tbxDiscount.Text.ToString()));
                command.Parameters.AddWithValue("@user", this.userId);
                command.Parameters.AddWithValue("@time", DateTime.Now);
                command.Parameters.AddWithValue("@status", 1);

                con.Open();
                    int a = command.ExecuteNonQuery();
                    if (a > 0)
                    {
                        TestId = int.Parse(command.LastInsertedId.ToString());
                       // MessageBox.Show("Insert ID: " + TestId.ToString());
                        foreach(CartItem item in LabCart){
                            //MessageBox.Show(item.ID.ToString());
                            MySqlCommand cmd = con.CreateCommand();
                            cmd.CommandText = "INSERT INTO `test_selected`(`ts_testid`,`ts_temid`, `ts_itemname`, `ts_price`,  `time_added`, `status`) VALUES ( @testid , @itemid , @itemname, @price, @time , @status )";
                            cmd.Parameters.AddWithValue("@testid", TestId);
                            cmd.Parameters.AddWithValue("@itemid", item.ID);
                            cmd.Parameters.AddWithValue("@itemname", item.TestName);
                            cmd.Parameters.AddWithValue("@price", item.ItemPrice);
                            cmd.Parameters.AddWithValue("@time", DateTime.Now);
                            cmd.Parameters.AddWithValue("@status", 1);
                            int b = cmd.ExecuteNonQuery();
                            if(b > 0){

                            }
                            else
                            {
                                MessageBox.Show("Unable to add to database. Item ID: " + item.ID.ToString() , "Database Error", MessageBoxButtons.OK,  MessageBoxIcon.Error);
                            }


                        }

                        this.Close();
                        MessageBox.Show("Test Report Assigned successfully to the Lab");
                    }
                    else
                    {
                        MessageBox.Show("Uanable to add Lab Report", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    con.Close();



        }

        private bool is_valid_for_proceed()
        {

            if (tbxSubTotal.Text.Trim() == String.Empty || int.Parse(tbxSubTotal.Text.Trim()) == 0)
            {
            MessageBox.Show("Add some test item", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
            }

            return true;
        }


        private void loadUserInfo()
        {
            // SQLiteConnection.CreateFile("app.sqlite");

            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=app.sqlite;Version=3;");
            m_dbConnection.Open();




            string SQLTableRead = "SELECT * FROM login";

            SQLiteCommand command3 = new SQLiteCommand(SQLTableRead, m_dbConnection);
            //command2.ExecuteNonQuery();
            command3.CommandText = SQLTableRead;

            SQLiteDataReader TableReader = command3.ExecuteReader();

            if (TableReader.Read())
            {
                //  MessageBox.Show(TableReader["id"].ToString());
                this.userId = int.Parse(TableReader["loginid"].ToString());
                this.userName = TableReader["username"].ToString();
                this.fullName = TableReader["fullname"].ToString();
                this.userRole = int.Parse(TableReader["role"].ToString());
                this.loginTime = TableReader["time_added"].ToString();
            }

            TableReader.Close();
            m_dbConnection.Close();


        }


    }
}