Biskilog/BISKILOG POS BASIC/Delivery/OrderAssignment.cs

using BiskLog_Point_Of_Sale.Classes;
using BiskLog_Point_Of_Sale.Multiple_Login;
using BiskLog_Point_Of_Sale.Properties;
using Point_Of_Sale_Managment;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BiskLog_Point_Of_Sale.Delivery
{
    public partial class OrderAssignment : Form
    {
        LoadingBay loadingBay;
        string orderID;
        SqlConnection cn;
        SqlCommand cm;
        SqlDataReader dr;
        DatabaseConn conn = new DatabaseConn();
        public OrderAssignment(LoadingBay loadingBay, string orderID = null)
        {
            InitializeComponent();
            this.loadingBay = loadingBay;
            this.orderID = orderID;
            cn = new SqlConnection(conn.MyConnection());
            holding.Left = (ClientSize.Width - holding.Width) / 2;
            holding.Top = (ClientSize.Height - holding.Height) / 2;
            updateBox.Left = (ClientSize.Width - updateBox.Width) / 2;
        }
        public int pullTrucks()
        {
            try
            {
                cn.Open();
                cm = new SqlCommand("Select truckID,licensePlate from tblTrucks where branchID = @branchID and driver is not null", cn);
                cm.Parameters.AddWithValue("@branchID", Settings.Default.BranchID);
                cm.ExecuteNonQuery();
                dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    ComboboxItem combobox = new ComboboxItem();
                    combobox.Text = dr[1].ToString();
                    combobox.Value = dr[0].ToString();
                    trucks.Invoke(new Action(() => trucks.Items.Add(combobox)));
                }
                cn.Close();
                return 1;
            }
            catch (Exception ex)
            {
                cn.Close();
                ErrorLogging.WriteToFile(ex.ToString());
                return 0;
            }
        }
        public int loadCustomer()
        {
            try
            {
                cn.Open();
                cm = new SqlCommand("Select customerID,status from tblDeliveryHead where deliveryID = @orderID and branchID = @branchID", cn);
                cm.Parameters.AddWithValue("@orderID", orderID);
                cm.Parameters.AddWithValue("branchID", Settings.Default.BranchID);
                cm.ExecuteNonQuery();
                dr = cm.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    if (dr[1].ToString().Equals("DELIVERED"))
                    {
                        dr.Close();
                        cn.Close();
                        string title = "Order delivered";
                        string message = "Sorry you cannot reassign an order which has been tagged as delivered";
                        NoAction noAction = new NoAction(title, message);
                        noAction.BringToFront();
                        noAction.ShowDialog();
                        customerID.Invoke(new Action(() => customerID.Text = ""));
                        txtOrderID.Invoke(new Action(() => txtOrderID.Text = ""));
                        return 3;
                    }
                    else if (dr[1].ToString().Equals("ASSIGNED TO TRUCK"))
                    {

                        dr.Close();
                        cn.Close();
                        string title = "Order Already Assigned";
                        string message = "Sorry you cannot reassign an order which has been already assigned to a different truck";
                        NoAction noAction = new NoAction(title, message);
                        noAction.BringToFront();
                        noAction.ShowDialog();
                        customerID.Invoke(new Action(() => customerID.Text = ""));
                        txtOrderID.Invoke(new Action(() => txtOrderID.Text = ""));
                        return 3;
                    }
                    else
                    {
                        customerID.Invoke(new Action(() => customerID.Text = dr[0].ToString().ToUpper()));
                        dr.Close();
                        cn.Close();
                        return 1;
                    }
                }
                else
                {
                    customerID.Invoke(new Action(() => customerID.Text = ""));
                    txtOrderID.Invoke(new Action(() => txtOrderID.Text = ""));
                    dr.Close();
                    cn.Close();
                    return 2;
                }

            }
            catch (Exception ex)
            {
                cn.Close();
                ErrorLogging.WriteToFile(ex.ToString());
                return 0;
            }
        }
        public int alreadyAssigned()
        {
            try
            {
                cn.Open();
                cm = new SqlCommand("Select TD.truckID,TD.licensePlate from tblTruckAssignments TA Inner Join tblDeliveryHead TD on " +
                    "deliveryID = TA.orderID where TA.orderID = @orderID and TD.status <> @state " +
                    "and TD.branchID = @branchID", cn);
                cm.Parameters.AddWithValue("@branchID", Settings.Default.BranchID);
                cm.Parameters.AddWithValue("@state", "CANCELLED");
                cm.Parameters.AddWithValue("@orderID", orderID);
                cm.ExecuteNonQuery();
                dr = cm.ExecuteReader();
                dr.Read();
                if (dr.HasRows)
                {
                    ComboboxItem combobox = new ComboboxItem();
                    combobox.Text = dr[1].ToString();
                    combobox.Value = dr[0].ToString();
                    trucks.Invoke(new Action(() => trucks.SelectedItem = combobox));
                    string title = "Order already assigned";
                    string message = "This order has been already assigned to a delivery truck, would you like to re-assign order ?";
                    Confirmation oR_No = new Confirmation(title, message);
                    oR_No.BringToFront();
                    oR_No.ShowDialog();
                    if (oR_No.DialogResult == DialogResult.No)
                    {
                        customerID.Invoke(new Action(() => customerID.Text = ""));
                        txtOrderID.Invoke(new Action(() => txtOrderID.Text = ""));
                    }
                }
                dr.Close();
                cn.Close();
                return 1;
            }
            catch (Exception ex)
            {
                cn.Close();
                ErrorLogging.WriteToFile(ex.ToString());
                return 0;
            }
        }
        private async void OrderAssignment_Load(object sender, EventArgs e)
        {
            holding.Visible = true;
            if (!String.IsNullOrEmpty(orderID))
            {
                txtOrderID.Text = orderID;
                Task<int> task = new Task<int>(() =>
               {
                   pullTrucks();
                   loadCustomer();
                   loadAssignments();
                   return 1;
               });
                task.Start();
                await task;
                BTNsave.Enabled = true;
                button1.Enabled = false;
            }
            else
            {
                Task<int> task = new Task<int>(() =>
                {
                    pullTrucks();
                    loadAssignments();
                    return 1;
                });
                task.Start();
                await task;
            }
            holding.Visible = false;
        }
        private void TxtOrderID_TextChanged(object sender, EventArgs e)
        {
            orderID = txtOrderID.Text;
            customerID.Text = "";
            BTNsave.Enabled = false;
            if (String.IsNullOrEmpty(orderID))
            {
                button1.Enabled = false;
            }
            else
            {
                button1.Enabled = true;
            }
        }
        private async void Button1_Click(object sender, EventArgs e)
        {
            Task<int> task = new Task<int>(loadCustomer);
            holding2.Visible = true;
            task.Start();
            int result = await task;
            if (result == 2)
            {
                string title = "Order not found";
                string message = "Records of the order was not found in the system please check the order id and try again";
                NoAction noAction = new NoAction(title, message);
                noAction.BringToFront();
                noAction.ShowDialog();
            }
            else if (result == 0)
            {
                string title = "Error Occurred";
                string message = "An error occurred while trying to fetch records of the order, please try again later";
                NoAction noAction = new NoAction(title, message);
                noAction.BringToFront();
                noAction.ShowDialog();
            }
            holding2.Visible = false;
        }
        public int assignments()
        {
            string truckID = "";
            trucks.Invoke(new Action(() => truckID = (trucks.SelectedItem as ComboboxItem).Value.ToString()));
            cn.Open();
            SqlTransaction transaction;
            transaction = cn.BeginTransaction();
            List<ProduceAssignments> produce = new List<ProduceAssignments>();
            try
            {
                cm = new SqlCommand("Select pcode,quantity,unit from tblDeliveryDetails where deliveryID = @orderID", cn, transaction);
                cm.Parameters.AddWithValue("@orderID", orderID);
                cm.ExecuteNonQuery();
                dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    ProduceAssignments produce1 = new ProduceAssignments();
                    produce1.pcode = dr[0].ToString();
                    produce1.quantity = int.Parse(dr[1].ToString());
                    produce1.unitCode = dr[2].ToString();
                    produce.Add(produce1);
                }
                dr.Close();

                cm = new SqlCommand("Declare @cost decimal(19,2) = (Select totalCost from tblDeliveryHead where branchID = @branchID and deliveryID = @orderID)" +
                    "Insert into tblTruckAssignments (orderID,cost,truckID,status,dateAssigned,countID) values " +
                    "(@orderID,@cost,@truckID,@status,@dateAssigned,@countID)", cn, transaction);
                cm.Parameters.AddWithValue("@orderID", orderID);
                cm.Parameters.AddWithValue("@truckID", truckID);
                cm.Parameters.AddWithValue("@status", "ASSIGNED TO TRUCK");
                cm.Parameters.AddWithValue("@branchID", Settings.Default.BranchID);
                cm.Parameters.AddWithValue("@dateAssigned", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                cm.Parameters.AddWithValue("@countID", Form1.branch + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                cm.ExecuteNonQuery();

                cm = new SqlCommand("Update tblDeliveryHead set status = @status where deliveryID = @orderID and branchID = @branchID", cn, transaction);
                cm.Parameters.AddWithValue("@orderID", orderID);
                cm.Parameters.AddWithValue("@status", "ASSIGNED TO TRUCK");
                cm.Parameters.AddWithValue("@branchID", Settings.Default.BranchID);
                cm.ExecuteNonQuery();

                foreach (var productDet in produce)
                {
                    cm = new SqlCommand("Declare @baseunit varchar(150) = (Select baseUnit from tblProduct where pcode = @pcode) " +
                        "Declare @dquantity int = Case when @baseUnit = @unit then 1 * @quantity else " +
                        "(Select[quantity/unit] * @quantity as quantity from ProductAltUnit where pcode = @pcode and unitCode = @unit and branchID = @branchID) " +
                        "end Declare @newQuantity int = (Select MAX(quantity) - MIN(@dquantity) from tblInventory where pcode = @pcode and branchID = @branchID) " +
                        "Update tblInventory set quantity = @newQuantity where pcode = @pcode and branchID = @branchID " +
                        "Insert into tblInventoryEntries(pcode, quantity, date, countID, branchID) values (@pcode, @newQuantity, @date, @countID, @branchID)", cn, transaction);
                    cm.Parameters.AddWithValue("@pcode", productDet.pcode);
                    cm.Parameters.AddWithValue("@date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                    cm.Parameters.AddWithValue("@branchID", Form1.branch);
                    cm.Parameters.AddWithValue("@countID", productDet.pcode + Form1.branch + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                    cm.Parameters.AddWithValue("@quantity", productDet.quantity);
                    cm.Parameters.AddWithValue("@unit", productDet.unitCode);
                    cm.ExecuteNonQuery();

                    cm = new SqlCommand("; With NewValues AS (Select truckID = @truckID,pcode = @pcode,quantity = @quantity,countID = @countID,unit = @unit) " +
                        "Merge Into tblTruckInventory As T Using NewValues As NV On (T.pcode = NV.pcode and NV.truckID = t.truckID and T.unit = NV.unit) When Not Matched By Target Then " +
                        "Insert(truckID, pcode, quantity, countID,unit) Values(NV.truckID, NV.pcode, NV.quantity, NV.countID,NV.unit) When Matched Then " +
                        "Update set quantity = NV.quantity;", cn, transaction);
                    cm.Parameters.AddWithValue("@pcode", productDet.pcode);
                    cm.Parameters.AddWithValue("@countID", productDet.pcode + Form1.branch + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                    cm.Parameters.AddWithValue("@quantity", productDet.quantity);
                    cm.Parameters.AddWithValue("@unit", productDet.unitCode);
                    cm.Parameters.AddWithValue("@truckID", truckID);
                    cm.ExecuteNonQuery();
                }

                transaction.Commit();
                cn.Close();
                return 1;
            }
            catch (Exception ex)
            {
                cn.Close();
                ErrorLogging.WriteToFile(ex.ToString());
                return 0;
            }
        }
        private async void BTNsave_Click(object sender, EventArgs e)
        {
            Task<int> task = new Task<int>(assignments);
            holding2.Visible = true;
            BTNsave.Enabled = false;
            task.Start();
            int result = await task;
            if (result == 1)
            {
                string title = "Success";
                string message = "Order assigned to truck and ready for delivery";
                NoAction noAction = new NoAction(title, message);
                noAction.BringToFront();
                noAction.ShowDialog();
                txtOrderID.Text = "";
                OrderAssignment_Load(sender, e);
            }
            else
            {
                string title = "Error Occurred";
                string message = "An error occurred while assigning order to truck, please try again in a few moments";
                NoAction noAction = new NoAction(title, message);
                noAction.BringToFront();
                noAction.ShowDialog();
            }
            holding2.Visible = false;
        }
        public int loadAssignments()
        {
            try
            {
                cn.Open();
                int i = 1;
                orders.Invoke(new Action(() =>
                {
                    orders.Rows.Clear();
                    cm = new SqlCommand("Select OA.orderID,TD.licensePlate,OA.dateAssigned,OA.cost,DH.status from tblTruckAssignments OA Inner " +
                        "Join tblDeliveryHead DH On DH.deliveryID = OA.orderID Inner Join tblTrucks TD on TD.truckID = OA.truckID where TD.branchID " +
                        "= @branchID and OA.orderID like '%" + txtSearch.Text + "%'", cn);
                    cm.Parameters.AddWithValue("@branchID", Settings.Default.BranchID);
                    cm.ExecuteNonQuery();
                    dr = cm.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr[4].ToString() == "ASSIGNED TO TRUCK")
                        {
                            orders.Rows.Add(i, dr[0].ToString().ToUpper(), dr[1].ToString().ToUpper(), Convert.ToDateTime(dr[2].ToString()).ToLongDateString(), Settings.Default.currrencyCode + " " + dr[3].ToString().ToUpper(),
                                "PENDING DELIVERY");
                        }
                        else
                        {
                            orders.Rows.Add(i, dr[0].ToString().ToUpper(), dr[1].ToString().ToUpper(), Convert.ToDateTime(dr[2].ToString()).ToLongDateString(), Settings.Default.currrencyCode + " " + dr[3].ToString().ToUpper(),
                                dr[4].ToString().ToUpper());
                        }
                        i++;
                    }
                    dr.Close();
                }));
                cn.Close();
                return 1;
            }
            catch (Exception ex)
            {
                cn.Close();
                ErrorLogging.WriteToFile(ex.ToString());
                return 0;
            }
        }
    }
}