using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using DataAccess;

namespace VMT
{
    public partial class TrimLineCompanyHistory : System.Web.UI.Page
    {

        string m_SP_SELECT_HISTORY_BU = Properties.Settings.Default.SP_SELECT_HISTORY_BU;
        string m_SP_SELECT_HIST_YEARS = Properties.Settings.Default.SP_SELECT_HIST_YEARS;
        string m_SP_SELECT_HIST_EC = Properties.Settings.Default.SP_SELECT_HIST_EC;
        string m_SP_SELECT_HIST_WA = Properties.Settings.Default.SP_SELECT_HIST_WA;
        string m_SP_VDM_SELECT_EC_RATE_HISTORY = Properties.Settings.Default.SP_VDM_SELECT_EC_RATE_HISTORY;
        string m_SP_VDM_SELECT_EC_RATE_RATIO = Properties.Settings.Default.SP_VDM_SELECT_EC_RATE_RATIO;
        string m_SP_VDM_SELECT_EC_BILLABLE = Properties.Settings.Default.SP_VDM_SELECT_EC_BILLABLE;
        string m_SP_SELECT_RATETYPE_DETAIL_COMP = Properties.Settings.Default.SP_SELECT_RATETYPE_DETAIL_COMP;
        string m_SP_SELECT_HISTORY_RATES = Properties.Settings.Default.SP_SELECT_HISTORY_RATES;

        string userRole = string.Empty;
        string m_SP_UR_GET_USER_ACCESS = Properties.Settings.Default.SP_UR_GET_USER_ACCESS;
        string pageName = "Assignment History";

        protected void Page_Load(object sender, EventArgs e)
        {
          

            //ScriptManager scriptManager = ScriptManager.GetCurrent(this.Page);
            //scriptManager.RegisterPostBackControl(this.btnExportExcel);
            if (!Page.IsPostBack)
            {

                //checks user role
                // userRole = DataAccessUtility.GetRole(m_SP_UR_GET_USER_ACCESS, pageName);
                userRole = VMT.UserRole.CallNewUserRoleWebService(m_SP_UR_GET_USER_ACCESS, pageName, System.Web.HttpContext.Current.User.Identity.Name.ToUpper());

                if (userRole == "0")
                {
                    System.Web.HttpContext.Current.Response.Redirect("Default.aspx");
                }


                BindBusinessUnit();
                ddlBUnit.SelectedValue = Properties.Settings.Default.SELECTED_BUNIT;
                BindYear();
                //if(ddlYear.Items.Count > 0)
                //{
                   // ddlYear.SelectedValue = DateTime.Now.Year.ToString().Substring(DateTime.Now.Year.ToString().Length - 2);
                //    BindEventCode();
                //}
                //if (ddlEventCode.Items.Count > 0)
                //{
                //    BindRateType();
                //    BindWorkArea();
                //}

                //BindAllEventCode();
            }
        }

        void BindBusinessUnit()
        {
            try
            {
                DataAccessUtility.BindDropdownListWithoutConditionViaProcedure(m_SP_SELECT_HISTORY_BU, ddlBUnit);
            }
            catch
            {
            }
        }

        void BindYear()
        {
            try
            {
                List<OleDbParameter> OledbParams = new List<OleDbParameter>();
                OledbParams.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", ddlBUnit.SelectedValue));
                DataAccessUtility.BindDropdownListWithConditionViaProcedure(m_SP_SELECT_HIST_YEARS, OledbParams, ddlYear);
            }
            catch
            {
            }
        }

        //void BindAllEventCode()
        //{
        //    DataAccessUtility.BindDropdownListWithoutConditionViaProcedure(m_SP_VDM_SELECT_ALL_EVENTCODE, ddlEventCode);
        //}

        void BindEventCode()
        {
            int V_BUSINESS_UNIT_ID = 0;
            int V_YEAR = 0;
            try
            {
                V_BUSINESS_UNIT_ID = Convert.ToInt32(ddlBUnit.SelectedValue);
                if (ddlYear.Items.Count > 0)
                {
                    V_YEAR = Convert.ToInt32(ddlYear.SelectedValue);
                }
                List<OleDbParameter> OledbParams = new List<OleDbParameter>();
                OledbParams.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", V_BUSINESS_UNIT_ID));
                OledbParams.Add(new OleDbParameter("V_YEAR_ID", V_YEAR));
                DataAccessUtility.BindDropdownListWithConditionViaProcedure(m_SP_SELECT_HIST_EC, OledbParams, ddlEventCode);
            }
            catch
            {
            }
        }

        protected void ddlBUnit_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindYear();
            BindEventCode();
        }

        protected void ddlYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindEventCode();
        }

        //protected void btnGo_Click(object sender, EventArgs e)
        //{
        //    BindData();
        //}

        void BindData()
        {
            int businessunit;
            int year;
            int Evtcode;
            int workarea;
            //lblHistory.Text = "History From - " + ddlYear.SelectedItem.Text;
            //lblNew.Text = "Current From - " + (Int32.Parse(ddlYear.SelectedItem.Text)+5).ToString();
            try
            {
                businessunit = Int32.Parse(ddlBUnit.SelectedValue.ToString());
                year = Int32.Parse(ddlYear.SelectedValue.ToString());
                if (ddlEventCode.SelectedValue.Trim() == "")
                    Evtcode = 0;
                else
                    Evtcode = Int32.Parse(ddlEventCode.SelectedValue.ToString());
                workarea = Int32.Parse(ddlWorkArea.SelectedValue.ToString());
                BindRateHistory(businessunit, year, Evtcode, workarea);
                BindLengthHistory(businessunit, year, Evtcode, workarea);
                BindRateNew(businessunit, year, Evtcode, workarea);
                BindBillableLength(businessunit, year, Evtcode, workarea);

                if (gdvRateNew.Rows.Count > 0)
                {
                    Header.Visible = true;
                    //AddNew.Visible = true;
                }
                BindRateType();
            }
            catch
            {
            }
        }

        void BindRateHistory(int businessunit, int year, int Evtcode,int workarea)
        {
           
            List<OleDbParameter> sqlParams = new List<OleDbParameter>();
            try
            {
                sqlParams.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", businessunit));
                sqlParams.Add(new OleDbParameter("V_YEAR_ID", year));
                sqlParams.Add(new OleDbParameter("V_EVENT_CODE_ID", Evtcode));
                sqlParams.Add(new OleDbParameter("V_WORK_AREA", workarea));
                DataAccessUtility.BindGridViewWithConditionViaProcedure(m_SP_VDM_SELECT_EC_RATE_HISTORY, sqlParams, gdvRateHistory);
                if (gdvRateHistory.Rows.Count == 0)
                    gdvRateHistory.Visible = false;
            }
            catch
            {
            }
        }
        void BindLengthHistory(int businessunit, int year, int Evtcode, int workarea)
        {

            List<OleDbParameter> sqlParams1 = new List<OleDbParameter>();
            try
            {
                sqlParams1.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", businessunit));
                sqlParams1.Add(new OleDbParameter("V_YEAR_ID", year));
                sqlParams1.Add(new OleDbParameter("V_EVENT_CODE_ID", Evtcode));
                sqlParams1.Add(new OleDbParameter("V_WORK_AREA", workarea));
                DataSet ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam(m_SP_VDM_SELECT_EC_RATE_RATIO, sqlParams1);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    tableHistory.Visible = true;
                    tableNew.Visible = true;
                    lblBillable.Text = ds.Tables[0].Rows[0]["billable_miles"].ToString();
                    lblNonBillable.Text = ds.Tables[0].Rows[0]["nonbillable_miles"].ToString();
                    lblSkipped.Text = ds.Tables[0].Rows[0]["skipped_miles"].ToString();
                    lblTotBillAmt.Text = ds.Tables[0].Rows[0]["billable"].ToString();


                    //txtNonBillable.Text = ds.Tables[0].Rows[0]["nonbillable_miles"].ToString();
                    txtSkipped.Text = ds.Tables[0].Rows[0]["skipped_miles"].ToString();
                    txtTotBillAmt.Text = ds.Tables[0].Rows[0]["billable"].ToString();
                }
                else
                {
                    tableHistory.Visible = false;
                    tableNew.Visible = false;
                }
            }
            catch
            {
            }
        }

        void BindRateNew(int businessunit, int year, int Evtcode, int workarea)
        {
            DataTable dt = new DataTable();
            List<OleDbParameter> sqlParameter = new List<OleDbParameter>();
            try
            {
                sqlParameter.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", businessunit));
                sqlParameter.Add(new OleDbParameter("V_YEAR_ID", year));
                sqlParameter.Add(new OleDbParameter("V_EVENT_CODE_ID", Evtcode));
                sqlParameter.Add(new OleDbParameter("V_WORK_AREA", workarea));
                // DataAccessUtility.BindGridViewWithConditionViaProcedure(m_SP_VDM_SELECT_EC_RATE_HISTORY, sqlParameter, gdvRateNew);
                dt = DataAccessUtility.SelectDataTableViaStoreProcedureParam(m_SP_VDM_SELECT_EC_RATE_HISTORY, sqlParameter);
                if (dt.Rows.Count > 0)
                {
                    // saving data in viewstate
                    ViewState["NewRates"] = dt;
                    //bind dataset tot gridview
                    gdvRateNew.DataSource = dt;
                    gdvRateNew.DataBind();

                }
            }
            catch
            {
            }
            finally
            {
                //dt.Clear();
                //dt.Dispose();
            }
        }

        protected void txtRateTypeAmount_TextChanged(object sender, EventArgs e)
        {
            GridViewRow gvRow = (GridViewRow)(sender as Control).Parent.Parent;
            Label lblRateTypeId = (Label)gvRow.FindControl("lblRateTypeId");
            TextBox txtRateTypeAmount = (TextBox)gvRow.FindControl("txtRateTypeAmount");

            int RateTypeId = Convert.ToInt32(lblRateTypeId.Text);
            decimal RateTypeAmt = Convert.ToDecimal(txtRateTypeAmount.Text);

            //update viewstate
            // check view state is not null  
            if (ViewState["NewRates"] != null)
            {
                //get datatable from view state   
                DataTable dtCurrentTable = (DataTable)ViewState["NewRates"];

                if (dtCurrentTable.Rows.Count > 0)
                {
                    DataRow[] rows = dtCurrentTable.Select("rate_type_id =" + RateTypeId);
                    if (rows.Length > 0)
                    {
                        foreach (DataRow row in rows)
                        {
                            row["rate_type_amount"] = RateTypeAmt;
                            dtCurrentTable.AcceptChanges();
                            row.SetModified();
                        }
                    }                    
                    //Save Data table into view state after creating each row  
                    ViewState["NewRates"] = dtCurrentTable;
                }
            }

            BindTotalAmount();
        }

        protected void txtRateRatio_TextChanged(object sender, EventArgs e)
        {
            GridViewRow gvRow = (GridViewRow)(sender as Control).Parent.Parent;
            Label lblRateTypeId = (Label)gvRow.FindControl("lblRateTypeId");
            TextBox txtRateRatio = (TextBox)gvRow.FindControl("txtRateRatio");

            int RateTypeId = Convert.ToInt32(lblRateTypeId.Text);
            int RateRatio = Convert.ToInt32(txtRateRatio.Text);

            //update viewstate
            // check view state is not null  
            if (ViewState["NewRates"] != null)
            {
                //get datatable from view state   
                DataTable dtCurrentTable = (DataTable)ViewState["NewRates"];

                if (dtCurrentTable.Rows.Count > 0)
                {
                    DataRow[] rows = dtCurrentTable.Select("rate_type_id =" + RateTypeId);
                    if (rows.Length > 0)
                    {
                        foreach (DataRow row in rows)
                        {
                            row["ratio"] = RateRatio;
                            dtCurrentTable.AcceptChanges();
                            row.SetModified();
                        }
                    }
                    //Save Data table into view state after creating each row  
                    ViewState["NewRates"] = dtCurrentTable;
                }
            }

            BindTotalAmount();
        }

        protected void txtBillable_TextChanged(object sender, EventArgs e)
        {
            BindTotalAmount();
        }
        void BindTotalAmount()
        {
            int ratio = 0;
            decimal totBillableAmt = 0;
            try
            {
                if (txtBillable.Text == "")
                {
                    txtBillable.Text = "0";
                    txtTotBillAmt.Text = "0";
                }
                //calculation of total billable amount
                decimal billableamt = Convert.ToDecimal(txtBillable.Text.Trim());
                for (int i = 0; i < gdvRateNew.Rows.Count; i++)
                {
                    decimal RateTypeAmt = Convert.ToDecimal(((TextBox)gdvRateNew.Rows[i].FindControl("txtRateTypeAmount")).Text.Trim());
                    int RateRatio = Convert.ToInt32(((TextBox)gdvRateNew.Rows[i].FindControl("txtRateRatio")).Text.Trim());
                    string uom = ((Label)gdvRateNew.Rows[i].FindControl("lblUOM")).Text.Trim();
                    if (uom == "Per Mile")
                    {
                        totBillableAmt = totBillableAmt + ((RateTypeAmt * RateRatio) * billableamt);
                    }
                    else
                    {
                        totBillableAmt = totBillableAmt + (RateTypeAmt * RateRatio);
                    }
                    ratio = ratio + RateRatio;
                }

                txtTotBillAmt.Text = (totBillableAmt / 100).ToString("N2");
                

                Label lblTotalRatio = gdvRateNew.FooterRow.FindControl("lblTotalRatio") as Label;
                lblTotalRatio.Text = ratio.ToString();
            }
            catch
            {
            }
        }


        int total = 0;
        protected void gdvRateNew_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            try
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                    total += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "ratio"));
                }
                if (e.Row.RowType == DataControlRowType.Footer)
                {
                    Label lblTotalRatio = (Label)e.Row.FindControl("lblTotalRatio");
                    lblTotalRatio.Text = total.ToString();
                }
            }
            catch
            {
            }
        }
        void BindBillableLength(int businessunit, int year, int Evtcode, int workarea)
        {

            List<OleDbParameter> sqlParams3 = new List<OleDbParameter>();
            sqlParams3.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", businessunit));
            sqlParams3.Add(new OleDbParameter("V_YEAR_ID", year+5));
            sqlParams3.Add(new OleDbParameter("V_EVENT_CODE_ID", Evtcode));
            sqlParams3.Add(new OleDbParameter("V_WORK_AREA", workarea));
            DataSet ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam(m_SP_VDM_SELECT_EC_BILLABLE, sqlParams3);
            try
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    txtBillable.Text = ds.Tables[0].Rows[0]["BILLABLE_LENGTH"].ToString();
                    txtNonBillable.Text = ds.Tables[0].Rows[0]["NONBILLABLE_LENGTH"].ToString();
                    BindTotalAmount();
                }
                else
                {
                    txtBillable.Text = "0";
                    txtNonBillable.Text = "0";
                    txtTotBillAmt.Text = "0";
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                ds.Clear();
                ds.Dispose();
            }
        }

        //Gridview functionality
        protected void btnshowhide_Click(object sender, ImageClickEventArgs e)
        {
            if (AddNew.Visible == true)
            {
                AddNew.Visible = false;
                btnshowhide.ImageUrl = "resource_gallery/graphics/plus.png";
            }
            else
            {
                btnshowhide.ImageUrl = "resource_gallery/graphics/minus.png";
                AddNew.Visible = true;
            }
        }
        void BindRateType()
        {
            DataTable dt = new DataTable();
            int V_BUSINESS_UNIT_ID = 0;
            int V_YEAR = 0;
            int V_EVENT_CODE_ID = 0;
            try
            {
                V_BUSINESS_UNIT_ID = Convert.ToInt32(ddlBUnit.SelectedValue);
                V_YEAR = Convert.ToInt32(ddlYear.SelectedValue);
                V_EVENT_CODE_ID = Convert.ToInt32(ddlEventCode.SelectedValue);
                List<OleDbParameter> OledbParams = new List<OleDbParameter>();
                OledbParams.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", V_BUSINESS_UNIT_ID));
                OledbParams.Add(new OleDbParameter("V_YEAR", V_YEAR));
                OledbParams.Add(new OleDbParameter("V_EVENT_CODE_ID", V_EVENT_CODE_ID));
                //DataAccessUtility.BindDropdownListWithConditionViaProcedure(m_SP_SELECT_HISTORY_RATES, OledbParams, ddlRateTypes);
                dt = DataAccessUtility.SelectDataTableViaStoreProcedureParam(m_SP_SELECT_HISTORY_RATES, OledbParams);
                if (dt.Rows.Count > 0)
                {
                    // saving data in viewstate
                    ViewState["NewRateType"] = dt;
                    //bind dataset tot gridview
                    ddlRateTypes.DataSource = dt;
                    ddlRateTypes.DataTextField = "Name";
                    ddlRateTypes.DataValueField = "ID";
                    ddlRateTypes.DataBind();

                }
            }
            catch
            {
            }
            finally
            {
                //dt.Clear();
                //dt.Dispose();
            }
        }
        protected void ddlRateTypes_SelectedIndexChanged(object sender, EventArgs e)
        {
            // bindrateType();
            // bindGrid();
            getUOM();
        }
        void getUOM()
        {
            DataSet ds = new DataSet();
            List<OleDbParameter> OleDbParams = new List<OleDbParameter>();
            try
            {
                if (ddlRateTypes.SelectedIndex >= 0)
                {
                    OleDbParams.Add(new OleDbParameter("V_RATE_TYPE_ID", ddlRateTypes.SelectedValue));
                    OleDbParams.Add(new OleDbParameter("V_RATE_TYPE_YR", ddlYear.SelectedValue));
                    OleDbParams.Add(new OleDbParameter("V_RATE_TYPE_EC", ddlEventCode.SelectedValue));
                    ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam(m_SP_SELECT_RATETYPE_DETAIL_COMP, OleDbParams);
                    //bind the fields 
                    lblUOM.Text = ds.Tables[0].Rows[0]["uom"].ToString();
                    txtRatio.Text = ds.Tables[0].Rows[0]["ratio"].ToString();
                }
                else
                {
                    lblUOM.Text = "";
                }
            }
            catch
            {
            }
            finally
            {
                ds.Clear();
                ds.Dispose();
            }
        }
        protected void ddlRateTypes_DataBound(object sender, EventArgs e)
        {
            getUOM();

        }
        protected void btnAddRates_Click(object sender, EventArgs e) 
        {
            try
            {
                // check view state is not null  
                if (ViewState["NewRates"] != null)
                {
                    //get datatable from view state   
                    DataTable dtCurrentTable = (DataTable)ViewState["NewRates"];
                    DataRow drCurrentRow = null;

                    if (dtCurrentTable.Rows.Count > 0)
                    {

                        for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                        {
                            //add each row into data table  
                            drCurrentRow = dtCurrentTable.NewRow();
                            drCurrentRow["event_code_id"] = ddlEventCode.SelectedValue;
                            drCurrentRow["rate_type_id"] = ddlRateTypes.SelectedValue;
                            drCurrentRow["rate_type_name"] = ddlRateTypes.SelectedItem.Text;
                            drCurrentRow["uom"] = lblUOM.Text.Trim();
                            drCurrentRow["rate_type_amount"] = txtRateamount.Text.Trim();
                            drCurrentRow["ratio"] = txtRatio.Text.Trim();
                        }
                        //Remove initial blank row  
                        if (dtCurrentTable.Rows[0][0].ToString() == "")
                        {
                            dtCurrentTable.Rows[0].Delete();
                            dtCurrentTable.AcceptChanges();
                        }

                        //add created Rows into dataTable  
                        dtCurrentTable.Rows.Add(drCurrentRow);
                        //Save Data table into view state after creating each row  
                        ViewState["NewRates"] = dtCurrentTable;
                        //Bind Gridview with latest Row  
                        gdvRateNew.DataSource = dtCurrentTable;
                        gdvRateNew.DataBind();

                        BindTotalAmount();

                        BindNewRateTypeAfterAdd(Int32.Parse(ddlRateTypes.SelectedValue.ToString()));
                    }
                    else
                    {
                        drCurrentRow = dtCurrentTable.NewRow();
                        drCurrentRow["event_code_id"] = ddlEventCode.SelectedValue;
                        drCurrentRow["rate_type_id"] = ddlRateTypes.SelectedValue;
                        drCurrentRow["rate_type_name"] = ddlRateTypes.SelectedItem.Text;
                        drCurrentRow["uom"] = lblUOM.Text.Trim();
                        drCurrentRow["rate_type_amount"] = txtRateamount.Text.Trim();
                        drCurrentRow["ratio"] = txtRatio.Text.Trim();

                        //add created Rows into dataTable  
                        dtCurrentTable.Rows.Add(drCurrentRow);
                        //Save Data table into view state after creating each row  
                        ViewState["NewRates"] = dtCurrentTable;
                        //Bind Gridview with latest Row  
                        gdvRateNew.DataSource = dtCurrentTable;
                        gdvRateNew.DataBind();
                        BindTotalAmount();
                        BindNewRateTypeAfterAdd(Int32.Parse(ddlRateTypes.SelectedValue.ToString()));
                    }
                }
                txtRateamount.Text = "0";
                txtRatio.Text = "0";
            }
            catch
            {
            }
        }

        protected void gdvRateNew_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            try
            {
                GridViewRow row = (GridViewRow)gdvRateNew.Rows[e.RowIndex];
                int lblRateTypeId = Convert.ToInt32((row.FindControl("lblRateTypeId") as Label).Text);
                string lblRateTypeName = (row.FindControl("lblRateTypeName") as Label).Text;
                // check view state is not null  
                if (ViewState["NewRates"] != null)
                {
                    //get datatable from view state   
                    DataTable dtCurrentTable = (DataTable)ViewState["NewRates"];
                   
                    if (dtCurrentTable.Rows.Count > 0)
                    {

                        for (int i = 0; i < dtCurrentTable.Rows.Count; i++)
                        {
                            //Remove initial blank row  
                            if (dtCurrentTable.Rows[i]["rate_type_id"].ToString() == lblRateTypeId.ToString())
                            {
                                dtCurrentTable.Rows[i].Delete();
                                dtCurrentTable.AcceptChanges();
                            }

                        }

                        //Save Data table into view state after creating each row  
                        ViewState["NewRates"] = dtCurrentTable;
                        //Bind Gridview with latest Row  
                        gdvRateNew.DataSource = dtCurrentTable;
                        gdvRateNew.DataBind();
                        BindNewRateTypeAfterDelete(lblRateTypeId, lblRateTypeName);
                    }
                    BindTotalAmount();
                }
            }
            catch
            {
            }
        }

        //Rate type after addition
        void BindNewRateTypeAfterAdd(int ID)
        {
            try
            {
                // check view state is not null  
                if (ViewState["NewRateType"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];

                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            //Remove row
                            if (dt.Rows[i]["ID"].ToString() == ID.ToString())
                            {
                                dt.Rows[i].Delete();
                                dt.AcceptChanges();
                            }
                        }
                        //  Convert DataTable to DataView
                        DataView dv = dt.DefaultView;
                        //   Sort data
                        dv.Sort = "ID DESC";
                        //   Convert back your sorted DataView to DataTable
                        dt = dv.ToTable(); 
                        //Save Data table into view state after creating each row  
                        ViewState["NewRateType"] = dt;
                        //Bind dropdown list with latest data  
                        ddlRateTypes.DataSource = dt;
                        ddlRateTypes.DataTextField = "Name";
                        ddlRateTypes.DataValueField = "ID";
                        ddlRateTypes.DataBind();

                        BindNewRateType();
                    }
                }
            }
            catch
            {
            }           
        }
        //Rate type after deletion
        void BindNewRateTypeAfterDelete(int ID,string Name)
        {
            try
            {
                // check view state is not null  
                if (ViewState["NewRateType"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];
                    DataRow drCurrentRow = null;

                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 1; i <= dt.Rows.Count; i++)
                        {
                            //add each row into data table  
                            drCurrentRow = dt.NewRow();
                            drCurrentRow["ID"] = ID;
                            drCurrentRow["Name"] = Name;
                        }
                        //Remove initial blank row  
                        if (dt.Rows[0][0].ToString() == "")
                        {
                            dt.Rows[0].Delete();
                            dt.AcceptChanges();
                        }

                        //add created Rows into dataTable  
                        dt.Rows.Add(drCurrentRow);

                        //  Convert DataTable to DataView
                        DataView dv = dt.DefaultView;
                        //   Sort data
                        dv.Sort = "ID DESC";
                        //   Convert back your sorted DataView to DataTable
                        dt = dv.ToTable(); 
                        //Save Data table into view state after creating each row  
                        ViewState["NewRateType"] = dt;
                        //Bind dropdownlist with latest data  
                        ddlRateTypes.DataSource = dt;
                        ddlRateTypes.DataTextField = "Name";
                        ddlRateTypes.DataValueField = "ID";
                        ddlRateTypes.DataBind();

                        BindNewRateType();
                    }
                }
            }
            catch
            {
            }
        }
        //It will be checked everytime 
        void BindNewRateType()
        {
            //if grid has no rows then flat,urban,rural and climbing will available on rate dropdown
            if (gdvRateNew.Rows.Count == 0)
            {
                if (ViewState["NewRateType"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];
                    DataRow drRow = null;
                    if (dt.Rows.Count > 0)
                    {
                       DataRow[] rowsFlat = dt.Select("ID = 5");
                       if (rowsFlat.Length > 0)
                       {
                       }
                       else
                       {
                           drRow = dt.NewRow();
                           drRow["ID"] = 5;
                           drRow["Name"] = "Flat";
                           dt.Rows.Add(drRow);
                       }
                       DataRow[] rowsUrban = dt.Select("ID = 1");
                       if (rowsUrban.Length > 0)
                       {
                       }
                       else
                       {
                           drRow = dt.NewRow();
                           drRow["ID"] = 1;
                           drRow["Name"] = "Urban";
                           dt.Rows.Add(drRow);
                       }
                       DataRow[] rowsRural = dt.Select("ID = 2");
                       if (rowsUrban.Length > 0)
                       {
                       }
                       else
                       {
                           drRow = dt.NewRow();
                           drRow["ID"] = 2;
                           drRow["Name"] = "Rural";
                           dt.Rows.Add(drRow);
                       }
                       DataRow[] rowsClimbing = dt.Select("ID = 3");
                       if (rowsUrban.Length > 0)
                       {
                       }
                       else
                       {
                           drRow = dt.NewRow();
                           drRow["ID"] = 3;
                           drRow["Name"] = "Climbing";
                           dt.Rows.Add(drRow);
                       }
                       //  Convert DataTable to DataView
                       DataView dv = dt.DefaultView;
                       //   Sort data
                       dv.Sort = "ID DESC";
                       //   Convert back your sorted DataView to DataTable
                       dt = dv.ToTable();
                       //Save Data table into view state after creating each row  
                       ViewState["NewRateType"] = dt;
                       //Bind dropdownlist with latest data  
                       ddlRateTypes.DataSource = dt;
                       ddlRateTypes.DataTextField = "Name";
                       ddlRateTypes.DataValueField = "ID";
                       ddlRateTypes.DataBind();
                    }
                }
            }
            //if grid contains flat then urban,rural and climbing will be removed from rate dropdown
            if (gdvRateNew.Rows.Count > 0)
            {
                if (ViewState["NewRates"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];
                    DataTable dt1 = (DataTable)ViewState["NewRates"];
                    if (dt1.Rows.Count > 0)
                    {
                        DataRow[] rowsFlat = dt1.Select("rate_type_id = 5");
                        if (rowsFlat.Length > 0)
                        {
                            for (int i = dt.Rows.Count - 1; i >= 0; i--)
                            {
                                DataRow dr = dt.Rows[i];
                                if (dr["ID"].ToString() == "1" || dr["ID"].ToString() == "2" || dr["ID"].ToString() == "3")
                                    dr.Delete();
                            }
                        }
                        
                        //  Convert DataTable to DataView
                        DataView dv = dt.DefaultView;
                        //   Sort data
                        dv.Sort = "ID DESC";
                        //   Convert back your sorted DataView to DataTable
                        dt = dv.ToTable();
                        //Save Data table into view state after creating each row  
                        ViewState["NewRateType"] = dt;
                        //Bind dropdownlist with latest data  
                        ddlRateTypes.DataSource = dt;
                        ddlRateTypes.DataTextField = "Name";
                        ddlRateTypes.DataValueField = "ID";
                        ddlRateTypes.DataBind();
                    }
                }
            }
            //if grid contains urban or rural or climbing then flat will be removed from rate dropdown
            if (gdvRateNew.Rows.Count > 0)
            {
                if (ViewState["NewRates"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];
                    DataTable dt1 = (DataTable)ViewState["NewRates"];
                    if (dt1.Rows.Count > 0)
                    {
                        DataRow[] rows = dt1.Select("rate_type_id = 1 OR rate_type_id = 2 OR rate_type_id = 3");
                        if (rows.Length > 0)
                        {
                            for (int i = dt.Rows.Count - 1; i >= 0; i--)
                            {
                                DataRow dr = dt.Rows[i];
                                if (dr["ID"].ToString() == "5")
                                    dr.Delete();
                            }
                        }

                        //  Convert DataTable to DataView
                        DataView dv = dt.DefaultView;
                        //   Sort data
                        dv.Sort = "ID DESC";
                        //   Convert back your sorted DataView to DataTable
                        dt = dv.ToTable();
                        //Save Data table into view state after creating each row  
                        ViewState["NewRateType"] = dt;
                        //Bind dropdownlist with latest data  
                        ddlRateTypes.DataSource = dt;
                        ddlRateTypes.DataTextField = "Name";
                        ddlRateTypes.DataValueField = "ID";
                        ddlRateTypes.DataBind();
                    }
                }
            }
            //if grid does not contains urban or rural or climbing or flat will be removed from rate dropdown
            if (gdvRateNew.Rows.Count > 0)
            {
                if (ViewState["NewRates"] != null)
                {
                    //get datatable from view state   
                    DataTable dt = (DataTable)ViewState["NewRateType"];
                    DataRow drRow = null;
                    DataTable dt1 = (DataTable)ViewState["NewRates"];
                    if (dt1.Rows.Count > 0)
                    {
                        DataRow[] rows = dt1.Select("rate_type_id = 1 OR rate_type_id = 2 OR rate_type_id = 3 OR rate_type_id = 5");
                        if (rows.Length == 0)
                        {
                            if (dt.Rows.Count > 0)
                            {
                                DataRow[] rowsFlat = dt.Select("ID = 5");
                                if (rowsFlat.Length > 0)
                                {
                                }
                                else
                                {
                                    drRow = dt.NewRow();
                                    drRow["ID"] = 5;
                                    drRow["Name"] = "Flat";
                                    dt.Rows.Add(drRow);
                                }
                                DataRow[] rowsUrban = dt.Select("ID = 1");
                                if (rowsUrban.Length > 0)
                                {
                                }
                                else
                                {
                                    drRow = dt.NewRow();
                                    drRow["ID"] = 1;
                                    drRow["Name"] = "Urban";
                                    dt.Rows.Add(drRow);
                                }
                                DataRow[] rowsRural = dt.Select("ID = 2");
                                if (rowsUrban.Length > 0)
                                {
                                }
                                else
                                {
                                    drRow = dt.NewRow();
                                    drRow["ID"] = 2;
                                    drRow["Name"] = "Rural";
                                    dt.Rows.Add(drRow);
                                }
                                DataRow[] rowsClimbing = dt.Select("ID = 3");
                                if (rowsUrban.Length > 0)
                                {
                                }
                                else
                                {
                                    drRow = dt.NewRow();
                                    drRow["ID"] = 3;
                                    drRow["Name"] = "Climbing";
                                    dt.Rows.Add(drRow);
                                }
                            }
                        }

                        //  Convert DataTable to DataView
                        DataView dv = dt.DefaultView;
                        //   Sort data
                        dv.Sort = "ID DESC";
                        //   Convert back your sorted DataView to DataTable
                        dt = dv.ToTable();
                        //Save Data table into view state after creating each row  
                        ViewState["NewRateType"] = dt;
                        //Bind dropdownlist with latest data  
                        ddlRateTypes.DataSource = dt;
                        ddlRateTypes.DataTextField = "Name";
                        ddlRateTypes.DataValueField = "ID";
                        ddlRateTypes.DataBind();
                    }
                }
            }

        }

        protected void ddlEventCode_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindWorkArea();
        }

        void BindWorkArea()
        {
            int V_BUSINESS_UNIT_ID = 0;
            int V_YEAR = 0;
            int V_EVENT_CODE_ID = 0;
            try
            {
                V_BUSINESS_UNIT_ID = Convert.ToInt32(ddlBUnit.SelectedValue);
                if (ddlYear.Items.Count > 0)
                {
                    V_YEAR = Convert.ToInt32(ddlYear.SelectedValue);
                }
                if (ddlEventCode.Items.Count > 0)
                {
                    V_EVENT_CODE_ID = Convert.ToInt32(ddlEventCode.SelectedValue);
                }
                List<OleDbParameter> OledbParams = new List<OleDbParameter>();
                OledbParams.Add(new OleDbParameter("V_BUSINESS_UNIT_ID", V_BUSINESS_UNIT_ID));
                OledbParams.Add(new OleDbParameter("V_YEAR_ID", V_YEAR));
                OledbParams.Add(new OleDbParameter("V_EVENT_CODE_ID", V_EVENT_CODE_ID));
                DataAccessUtility.BindDropdownListWithConditionViaProcedure(m_SP_SELECT_HIST_WA, OledbParams, ddlWorkArea);
            }
            catch
            {
            }
        }

        protected void ddlEventCode_DataBound(object sender, EventArgs e)
        {
            BindWorkArea();
        }

        protected void ddlYear_DataBound(object sender, EventArgs e)
        {
            BindEventCode();
        }

        protected void ddlWorkArea_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlWorkArea.SelectedValue.ToString() != "-1")
            {
                BindData();
                gdvRateHistory.Visible = true;
                gdvRateNew.Visible = true;
                tableHistory.Visible = true;
                tableNew.Visible = true;
                Header.Visible = true;
            }
            if (ddlWorkArea.SelectedValue.ToString() == "-1")
            {
                gdvRateHistory.Visible = false;
                AddNew.Visible = false;
                gdvRateNew.Visible = false;
                tableHistory.Visible = false;
                tableNew.Visible = false;
                Header.Visible = false;
            }
        }

        protected void ddlWorkArea_DataBound(object sender, EventArgs e)
        {

            if (ddlWorkArea.SelectedValue.ToString() != "-1")
            {
                BindData();
            }
            if (ddlWorkArea.SelectedValue.ToString() == "-1")
            {
                gdvRateHistory.Visible = false;
                AddNew.Visible = false;
                gdvRateNew.Visible = false;
                Header.Visible = false;
                tableHistory.Visible = false;
                tableNew.Visible = false;
            }
        }
    }
}