using DataAccess;
using GenClassLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Windows.Forms;

namespace ConvertApp
{
    public partial class ManualScriptED : System.Web.UI.Page
    {
        GenClass obj = new GenClass();
        SqlConnection con;
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!Page.IsPostBack)
            {
                DropDownListBusProc.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select All-", "0"));
                DropDownListProg.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select All-", "0"));
                DropDownListsqlno.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select SQL-", "0"));

                BindExceptionType();
                BindBusinessProcess();
                

            }


        }

        private void BindBusinessProcess()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_l4_PRG_BP", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@exception_type", DropDownListException.SelectedValue);

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    DropDownListBusProc.DataSource = dt;
                    DropDownListBusProc.DataTextField = "Name";
                    DropDownListBusProc.DataValueField = "ID";
                    DropDownListBusProc.DataBind();
                    

                }
               
            }
            catch (Exception ex)
            {

                //throw ex;
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        private void BindProgram()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_l4_PRG_NM", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmd.Parameters.AddWithValue("@exception_type", DropDownListException.SelectedValue);

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    DropDownListProg.DataSource = dt;
                    DropDownListProg.DataTextField = "Name";
                    DropDownListProg.DataValueField = "ID";
                    DropDownListProg.DataBind();

                }
                
            }
            catch (Exception ex)
            {

                throw ex;
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        private void BindExceptionType()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_Exp_type", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    DropDownListException.DataSource = dt;
                    DropDownListException.DataTextField = "Name";
                    DropDownListException.DataValueField = "ID";
                    DropDownListException.DataBind();

                }
                
            }
            catch (Exception ex)
            {

                throw ex;
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        private void BindProgramSqlNo()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_l4_PRG_SQLNO", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmd.Parameters.AddWithValue("@exception_type", DropDownListException.SelectedValue);
                cmd.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);

                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    DropDownListsqlno.DataSource = dt;
                    DropDownListsqlno.DataTextField = "Name";
                    DropDownListsqlno.DataValueField = "ID";
                    DropDownListsqlno.DataBind();


                }

            }
            catch (Exception ex)
            {

                //throw ex;
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        protected void DropDownListException_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindBusinessProcess();
        }

        protected void DropDownListBusProc_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindProgram();
        }

        protected void DropDownListProg_SelectedIndexChanged(object sender, EventArgs e)
        {
            //BindProgramSqlNo();

            ChkCodeConvertSts();
        }

        protected void ButtonGo_Click(object sender, EventArgs e)
        {
            BindGrid();
        }

        private void BindGrid()
        {
            try
            {
                con = obj.Connect();
                DataSet ds = new DataSet();
                List<SqlParameter> sqlParams = new List<SqlParameter>();

                sqlParams.Add(new SqlParameter("@business_process", DropDownListBusProc.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@program_name", DropDownListProg.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@exception_type", DropDownListException.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@sql_no", DropDownListsqlno.SelectedValue.Trim()));

                    StringBuilder sb = new StringBuilder();

                ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam("SP_Select_l4_PRG_TEXT", sqlParams);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    TextBoxoutput.Text = null;
                    Buttonsav.Attributes.Add("class", "btn btn-primary disabled");
                }
                else
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        // Concatenate all 
                        sb.Append(string.Join(",", row.ItemArray));
                        sb.AppendLine();  // Add a new line (or a sb.Append(",") for a comma)
                    }
                    Buttonsav.Attributes.Add("class", "btn btn-primary");
                }
                TextBoxoutput.Text = sb.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }


        }

        protected void Buttonsav_Click(object sender, EventArgs e)
        {
            try
            {
                con = obj.Connect();
                var progtext = TextBoxoutput.Text.Replace(Environment.NewLine, "~");
                SqlCommand cmdlvluser = new SqlCommand("SP_SavelevelUser_SQL", con);
                cmdlvluser.CommandType = CommandType.StoredProcedure;
                cmdlvluser.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmdlvluser.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
               
                cmdlvluser.Parameters.AddWithValue("@sql_no", DropDownListsqlno.SelectedValue);
                cmdlvluser.Parameters.AddWithValue("@sql_text", progtext.Remove(progtext.Length - 1));

                int luser = cmdlvluser.ExecuteNonQuery();
                BindGrid();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        protected void Buttongetsyscode_Click(object sender, EventArgs e)
        {
            try
            {
                con = obj.Connect();
                DataSet ds = new DataSet();
                List<SqlParameter> sqlParams = new List<SqlParameter>();

                sqlParams.Add(new SqlParameter("@business_process", DropDownListBusProc.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@program_name", DropDownListProg.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@exception_type", DropDownListException.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@sql_no", DropDownListsqlno.SelectedValue.Trim()));

                StringBuilder sb = new StringBuilder();

                ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam("SP_Select_l2_PRG_TEXT", sqlParams);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    TextBoxoutput.Text = null;
                }
                else
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        // Concatenate all 
                        sb.Append(string.Join(",", row.ItemArray));
                        sb.AppendLine();  // Add a new line (or a sb.Append(",") for a comma)
                    }
                }
                TextBoxoutput.Text = sb.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        protected void ButtonReset_Click(object sender, EventArgs e)
        {
            BindGrid();
        }

        protected void Buttonvwsyscode_Click(object sender, EventArgs e)
        {
            try
            {
                con = obj.Connect();
                DataSet ds = new DataSet();
                List<SqlParameter> sqlParams = new List<SqlParameter>();

                sqlParams.Add(new SqlParameter("@business_process", DropDownListBusProc.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@program_name", DropDownListProg.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@exception_type", DropDownListException.SelectedValue.Trim()));
                sqlParams.Add(new SqlParameter("@sql_no", DropDownListsqlno.SelectedValue.Trim()));
                ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam("SP_Select_l2_PRG_TEXT", sqlParams);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    GridView1.DataSource = null;
                }
                else
                {
                    GridView1.DataSource = ds;
                }
                GridView1.DataBind();
                ClientScript.RegisterStartupScript(GetType(), "Show", "<script> $('#myModal').modal('toggle');</script>");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        protected void DropDownListsqlno_SelectedIndexChanged(object sender, EventArgs e)
        {
            TextBoxoutput.Text = "";
        }

        private void ChkCodeConvertSts()
        {
            con = obj.Connect();
            SqlCommand cmd = new SqlCommand("ChkCodeConvertSts", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
            cmd.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                string lvl_status = dt.Rows[0]["lvl_status"].ToString();

                if (lvl_status == "4")
                {
                    ButtonGo.Visible = false;                    
                    divMsg.Visible = true;
                }
                else
                {
                    BindProgramSqlNo();
                    ButtonGo.Visible = true;
                    divMsg.Visible = false;
                }
            }
            else
            {
                BindProgramSqlNo();
                ButtonGo.Visible = true;
                divMsg.Visible = false;
            }
        }

    }
}