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

namespace ConvertApp
{
    public partial class ConvertLevel2 : 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-", "0"));

                DropDownListProg.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select All-", "0"));
                BindBusinessProcess();




            }
        }

        private void BindBusinessProcess()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_BP_Level2", con);
                cmd.CommandType = CommandType.StoredProcedure;

                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();
                    

                }
                else
                {
                    
                }
            }
            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_PRG_NM_Level2", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@business_process", DropDownListBusProc.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();

                }
                else
                {
                   
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

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

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

        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()));
                ds = DataAccessUtility.SelectDataSetViaStoreProcedureParam("SP_Select_LVL2_SQLS", sqlParams);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    GridView1.DataSource = null;
                }
                else
                {
                    GridView1.DataSource = ds;
                }
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_DELETE_Level2", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmd.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
                int luser = cmd.ExecuteNonQuery();

                SqlCommand cmd2 = new SqlCommand("SP_Select_Groups", con);
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmd2.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
                SqlDataAdapter sda2 = new SqlDataAdapter(cmd2);
                DataSet ds2 = new DataSet();
                sda2.Fill(ds2);
                foreach (DataTable table1 in ds2.Tables)
                {
                    foreach (DataRow dr1 in table1.Rows)
                    {
                        AddColumn(dr1["lvl1_business_process"].ToString(), dr1["lvl1_program_name"].ToString());
                        AddTables(dr1["lvl1_business_process"].ToString(), dr1["lvl1_program_name"].ToString());
                        AddLevel2(dr1["lvl1_business_process"].ToString(), dr1["lvl1_program_name"].ToString());

                    }
                }
                //BindGrid();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        private void AddColumn(string businesss_process,string program_name)
        {
            try
            {
                con = obj.Connect();
               
                SqlCommand cmd2 = new SqlCommand("SP_SelectSqlToConvert", con);
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.AddWithValue("@business_process", businesss_process);
                cmd2.Parameters.AddWithValue("@program_name", program_name);
                SqlDataAdapter sda2 = new SqlDataAdapter(cmd2);
                DataSet ds2 = new DataSet();
                sda2.Fill(ds2);
                foreach (DataTable table1 in ds2.Tables)
                {
                    foreach (DataRow dr1 in table1.Rows)
                    {
                        try {
                            string querystatus = "";
                            int sqlnoitrt = 0;

                            var sub_query_sts = "";
                            int colno = 0;
                            if (dr1["sql_type"].ToString() == "SELECT")
                            {
                                string FinalString = "";
                                if (dr1["lvl1_program_text"].ToString().ToUpper().StartsWith("SELECT") && dr1["lvl1_program_text"].ToString().ToUpper().EndsWith(";") && !dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM"))
                                {
                                    string FirstString = "SELECT";
                                    string LastString = ";";

                                    int Pos1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                    int Pos2 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                    FinalString = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1, Pos2 - Pos1);
                                    if (FinalString.Contains(","))
                                    {
                                        foreach (var col in FinalString.Split(','))
                                        {
                                            SqlCommand cmdseq = new SqlCommand("SP_InsertQuerySeq", con);
                                            cmdseq.CommandType = CommandType.StoredProcedure;
                                            cmdseq.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                            cmdseq.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());

                                            cmdseq.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                            cmdseq.Parameters.AddWithValue("@col_name", col);

                                            string aliasname = "";
                                            if (col.Contains("END "))
                                            {
                                                aliasname = TextFollowing(col, "END ");

                                            }

                                            //if(col.Contains("AS "))
                                            //{
                                            //     aliasname = TextFollowing(col, "AS ");
                                            //}

                                            cmdseq.Parameters.AddWithValue("@alias_name", aliasname);

                                            if (col.Contains("SELECT"))
                                            {
                                                sub_query_sts = "Y";

                                            }
                                            else
                                            {
                                                sub_query_sts = "N";

                                            }

                                            if (sqlnoitrt == 0)
                                            {
                                                colno = 1;
                                                sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                            }
                                            else
                                            {
                                                if (sqlnoitrt == Convert.ToInt32(dr1["sql_no"].ToString()))
                                                {
                                                    colno = colno + 1;
                                                }
                                                else
                                                {
                                                    colno = 1;
                                                    sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                                }

                                            }

                                            cmdseq.Parameters.AddWithValue("@sub_query_sts", sub_query_sts);
                                            cmdseq.Parameters.AddWithValue("@qs_col_no", colno);


                                            int m1 = cmdseq.ExecuteNonQuery();
                                        }
                                    }
                                    else
                                    {
                                        SqlCommand cmdseq = new SqlCommand("SP_InsertQuerySeq", con);
                                        cmdseq.CommandType = CommandType.StoredProcedure;
                                        cmdseq.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                        cmdseq.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());

                                        cmdseq.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                        cmdseq.Parameters.AddWithValue("@col_name", FinalString);
                                        string aliasname = "";
                                        if (FinalString.Contains("END "))
                                        {
                                            aliasname = TextFollowing(FinalString, "END ");

                                        }

                                        //if (FinalString.Contains("AS "))
                                        //{
                                        //    aliasname = TextFollowing(FinalString, "AS ");
                                        //}
                                        //string aliasname = TextFollowing(FinalString, "END ");

                                        cmdseq.Parameters.AddWithValue("@alias_name", aliasname);
                                        if (querystatus != "")
                                        {
                                            sub_query_sts = querystatus;

                                        }
                                        else
                                        {
                                            if (FinalString.Contains("SELECT"))
                                            {
                                                sub_query_sts = "Y";

                                            }
                                            else
                                            {
                                                sub_query_sts = "N";

                                            }
                                        }
                                        if (sqlnoitrt == 0)
                                        {
                                            colno = 1;
                                            sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                        }
                                        else
                                        {
                                            if (sqlnoitrt == Convert.ToInt32(dr1["sql_no"].ToString()))
                                            {
                                                colno = colno + 1;
                                            }
                                            else
                                            {
                                                colno = 1;
                                                sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                            }

                                        }

                                        cmdseq.Parameters.AddWithValue("@sub_query_sts", sub_query_sts);
                                        cmdseq.Parameters.AddWithValue("@qs_col_no", colno);


                                        int m1 = cmdseq.ExecuteNonQuery();
                                    }


                                }

                            }
                            else if (dr1["sql_type"].ToString() == "ISELECT")
                            {
                                string grprplc = "";
                                string FinalString = "";
                                string finalresult = "";
                                string resbrkup = "";
                                string res = "";
                                int count1 = 0;
                                string FirstString = "SELECT";
                                string LastString;
                                string wherestring = "";
                                string semicolstr = "";
                                if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM"))
                                {
                                    LastString = "FROM";
                                    int Pos1a = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                    int Pos2b = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                    string selectfrmdt = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1a, Pos2b - Pos1a);
                                    if (!selectfrmdt.Contains("SELECT"))
                                    {
                                        res = selectfrmdt;

                                    }
                                    //else
                                    //{
                                    int count = 0;
                                    int a = 0;

                                    string pattern = "FROM";
                                    while ((a = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(pattern, a)) != -1)
                                    {
                                        a += pattern.Length;
                                        count++;
                                    }
                                    bool selectsubqueryfrm = false;
                                    if (count > 1)
                                    {
                                        if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("JOIN"))
                                        {
                                            if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM("))
                                            {
                                                FirstString = "FROM(";
                                                selectsubqueryfrm = true;
                                                //querystatus = "Y";
                                            }
                                            LastString = "JOIN";
                                            int Pos1j = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                            int Pos2j = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                            int i = 0;
                                            int a2 = 0;
                                            FinalString = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1j, Pos2j - Pos1j);
                                            while ((i = FinalString.IndexOf("FROM", i)) != -1)
                                            {

                                                if (i != -1)
                                                {
                                                    a2 = i;
                                                }
                                                i = i + 1;
                                            }

                                            if (a2 != 0)
                                            {
                                                grprplc = FinalString.Remove(a2);
                                                if (selectsubqueryfrm == true)
                                                {
                                                    grprplc = grprplc.Replace("SELECT", ",");
                                                }
                                            }
                                            else
                                            {
                                                grprplc = FinalString;
                                            }
                                        }
                                        else
                                        {

                                            LastString = "WHERE";
                                            int Posa = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                            int Posb = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                            resbrkup = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Posa, Posb - Posa);

                                            int a1 = 0;
                                            while ((a1 = resbrkup.ToString().ToUpper().IndexOf(pattern, a1)) != -1)
                                            {
                                                a1 += pattern.Length;
                                                count1++;
                                            }
                                            int Pos1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                            int Pos2 = 0;
                                            if (count1 > 1)
                                            {
                                                Pos2 = resbrkup.ToString().ToUpper().LastIndexOf("FROM");
                                                wherestring = resbrkup.ToUpper().Substring(0, Pos2 - 0);
                                            }
                                            else if (count1 == 1)
                                            {
                                                Pos2 = resbrkup.ToString().ToUpper().LastIndexOf("FROM");
                                                wherestring = resbrkup.ToUpper().Substring(0, Pos2 - 0);

                                            }
                                            else
                                            {
                                                wherestring = resbrkup;
                                            }


                                        }
                                    }
                                    else
                                    {
                                        LastString = "FROM";
                                    }
                                    // }
                                }
                                else
                                {
                                    LastString = ";";
                                    int Position1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                    int Position2 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString) + LastString.Length;
                                    semicolstr = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Position1, Position2 - Position1);
                                }
                                if (grprplc != "")
                                {
                                    finalresult = grprplc;
                                }
                                if (finalresult != res)
                                {
                                    finalresult += res;
                                }
                                if (finalresult != wherestring)
                                {
                                    finalresult += wherestring;
                                }
                                if (finalresult != semicolstr)
                                {
                                    finalresult += semicolstr;
                                }





                                List<int> openbracindex = new List<int>();
                                List<int> closebracindex = new List<int>();


                                int bracketCount = 1;
                                var textArray = finalresult.ToUpper().ToCharArray();

                                for (int i = 0; i < textArray.Length; i++)
                                {
                                    if (textArray[i] == '(')
                                    {
                                        openbracindex.Add(i);
                                        bracketCount++;

                                    }
                                    else if (textArray[i] == ')')
                                    {
                                        closebracindex.Add(i);
                                        bracketCount--;
                                    }

                                    if (bracketCount == 0)
                                    {

                                        break;
                                    }
                                }
                                for (int i = 0; i < openbracindex.Count; i++)
                                {

                                    string result = finalresult.ToString().ToUpper().Substring(openbracindex[i], (closebracindex[i] - openbracindex[i]));
                                    if (result.Contains(','))
                                    {
                                        string result1 = result.Replace(",", "~");
                                        finalresult = finalresult.Replace(result, result1);
                                    }

                                }
                                finalresult = finalresult.Replace("'0000000000',1,10", "'0000000000'~1~10");// not replacing with the above replace code ln no.339.
                                                                                                            //finalresult = finalresult.Replace("CAST((CALENDAR_DATE (FORMAT 'YYYY-MM-DD')) AS CHAR(10)))", "TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD')");

                                foreach (var col in finalresult.Split(','))
                                {
                                    SqlCommand cmdseq = new SqlCommand("SP_InsertQuerySeq", con);
                                    cmdseq.CommandType = CommandType.StoredProcedure;
                                    cmdseq.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                    cmdseq.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());

                                    cmdseq.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                    cmdseq.Parameters.AddWithValue("@col_name", col.Replace("~", ","));
                                    string aliasname = "";
                                    if (col.Contains("END "))
                                    {
                                        aliasname = TextFollowing(col, "END ");

                                    }

                                    //if (col.Contains("AS "))
                                    //{
                                    //    aliasname = TextFollowing(col, "AS ");
                                    //}

                                    //string aliasname = TextFollowing(col, "END ");
                                    //if (aliasname == "")
                                    //{
                                    //    aliasname = TextFollowing(col, "AS ");
                                    //}
                                    cmdseq.Parameters.AddWithValue("@alias_name", aliasname);


                                    if (col.Contains("SELECT"))
                                    {
                                        sub_query_sts = "Y";

                                    }
                                    else
                                    {
                                        sub_query_sts = "N";

                                    }


                                    if (sqlnoitrt == 0)
                                    {
                                        colno = 1;
                                        sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                    }
                                    else
                                    {
                                        if (sqlnoitrt == Convert.ToInt32(dr1["sql_no"].ToString()))
                                        {
                                            colno = colno + 1;
                                        }
                                        else
                                        {
                                            colno = 1;
                                            sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                        }

                                    }

                                    cmdseq.Parameters.AddWithValue("@sub_query_sts", sub_query_sts);
                                    cmdseq.Parameters.AddWithValue("@qs_col_no", colno);


                                    int m1 = cmdseq.ExecuteNonQuery();
                                }

                            }
                            else if (dr1["sql_type"].ToString() == "CREATE")
                            {
                                string FirstString = "(";
                                string LastString = ")";
                                int Pos1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                int Pos2 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                string FinalString = "";

                                FinalString = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1, Pos2 - Pos1);
                                FinalString = FinalString.TrimStart();
                                if (FinalString.Contains(","))
                                {
                                    var clseprtr = FinalString.Split(',');
                                    List<string> clseprtrarr = new List<string>();

                                    foreach (var item in clseprtr)
                                    {
                                        if (item.Contains(" "))
                                        {
                                            var val = item.TrimStart().Split(' ')[0];
                                            clseprtrarr.Add(val.ToString());

                                        }
                                    }
                                    FinalString = string.Join(",", clseprtrarr);
                                    foreach (var col in FinalString.Split(','))
                                    {
                                        SqlCommand cmdseq = new SqlCommand("SP_InsertQuerySeq", con);
                                        cmdseq.CommandType = CommandType.StoredProcedure;
                                        cmdseq.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                        cmdseq.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());

                                        cmdseq.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                        cmdseq.Parameters.AddWithValue("@col_name", col);
                                        string aliasname = "";
                                        if (col.Contains("END "))
                                        {
                                            aliasname = TextFollowing(col, "END ");

                                        }

                                        //if (col.Contains("AS "))
                                        //{
                                        //    aliasname = TextFollowing(col, "AS ");
                                        //}
                                        //string aliasname = TextFollowing(col, "END ");

                                        cmdseq.Parameters.AddWithValue("@alias_name", aliasname);

                                        if (col.Contains("SELECT"))
                                        {
                                            sub_query_sts = "Y";

                                        }
                                        else
                                        {
                                            sub_query_sts = "N";

                                        }

                                        if (sqlnoitrt == 0)
                                        {
                                            colno = 1;
                                            sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                        }
                                        else
                                        {
                                            if (sqlnoitrt == Convert.ToInt32(dr1["sql_no"].ToString()))
                                            {
                                                colno = colno + 1;
                                            }
                                            else
                                            {
                                                colno = 1;
                                                sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                            }

                                        }

                                        cmdseq.Parameters.AddWithValue("@sub_query_sts", sub_query_sts);
                                        cmdseq.Parameters.AddWithValue("@qs_col_no", colno);


                                        int m1 = cmdseq.ExecuteNonQuery();
                                    }
                                }
                                else
                                {
                                    SqlCommand cmdseq = new SqlCommand("SP_InsertQuerySeq", con);
                                    cmdseq.CommandType = CommandType.StoredProcedure;
                                    cmdseq.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                    cmdseq.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());

                                    cmdseq.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                    cmdseq.Parameters.AddWithValue("@col_name", FinalString);
                                    string aliasname = "";
                                    if (FinalString.Contains("END "))
                                    {
                                        aliasname = TextFollowing(FinalString, "END ");

                                    }

                                    //if (FinalString.Contains("AS "))
                                    //{
                                    //    aliasname = TextFollowing(FinalString, "AS ");
                                    //}
                                    //string aliasname = TextFollowing(FinalString, "END ");

                                    cmdseq.Parameters.AddWithValue("@alias_name", aliasname);
                                    if (querystatus != "")
                                    {
                                        sub_query_sts = querystatus;

                                    }
                                    else
                                    {
                                        if (FinalString.Contains("SELECT"))
                                        {
                                            sub_query_sts = "Y";

                                        }
                                        else
                                        {
                                            sub_query_sts = "N";

                                        }
                                    }
                                    if (sqlnoitrt == 0)
                                    {
                                        colno = 1;
                                        sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                    }
                                    else
                                    {
                                        if (sqlnoitrt == Convert.ToInt32(dr1["sql_no"].ToString()))
                                        {
                                            colno = colno + 1;
                                        }
                                        else
                                        {
                                            colno = 1;
                                            sqlnoitrt = Convert.ToInt32(dr1["sql_no"].ToString());
                                        }

                                    }

                                    cmdseq.Parameters.AddWithValue("@sub_query_sts", sub_query_sts);
                                    cmdseq.Parameters.AddWithValue("@qs_col_no", colno);


                                    int m1 = cmdseq.ExecuteNonQuery();
                                }
                            }
                            else if (dr1["sql_type"].ToString() == "UPDATE")
                            {

                            }
                            else
                            {

                            }
                        }
                        catch(Exception ex)
                        {

                        }
                        }
                }
            }
            catch(Exception ex)
            {

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

            }
        }

        private void AddTables(string businesss_process, string program_name)
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectLevel1", con);
                //SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectSqlToConvert", con);


                cmdsqllvl1.CommandType = CommandType.StoredProcedure;
                cmdsqllvl1.Parameters.AddWithValue("@business_process", businesss_process);
                cmdsqllvl1.Parameters.AddWithValue("@program_name", program_name);

                SqlDataAdapter sdasqllvl1 = new SqlDataAdapter(cmdsqllvl1);
                DataSet dssqllvl1 = new DataSet();
                sdasqllvl1.Fill(dssqllvl1);

                foreach (DataTable table1 in dssqllvl1.Tables)
                {
                    int sqlnoitrttbl = 0;
                    int colnotbl = 0;
                    bool isbracket = false;
                    //bool iscomma = false;
                    //bool isfrom = false;

                    foreach (DataRow dr1 in table1.Rows)
                    {
                        try {
                            bool isfrom = false;
                            bool iscomma = false;

                            List<string> restbls = new List<string>();
                            var res = dr1["lvl1_program_text"].ToString().ToUpper();
                            string sub_query_ststbl = "";
                            string tbl_type = "";
                            if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM"))
                            {




                                if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("SELECT"))
                                {
                                    sub_query_ststbl = "Y";

                                }
                                else
                                {
                                    sub_query_ststbl = "N";

                                }
                                res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("FROM")) + 4);
                                if (res.Contains(","))
                                {
                                    iscomma = true;
                                    isfrom = true;
                                    if (res.Contains(")"))
                                    {
                                        isbracket = true;
                                        iscomma = false;
                                    }

                                    foreach (var tbls in res.Split(','))
                                    {
                                        if (tbls.Contains(" "))
                                        {
                                            restbls.Add(tbls.Split(' ')[1]);
                                        }
                                        else
                                        {
                                            restbls.Add(tbls);
                                        }
                                    }
                                }
                                else
                                {
                                    if (res.Contains(" "))
                                    {
                                        res = res.Split(' ')[1];
                                    }

                                }
                            }
                            else if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("JOIN"))
                            {




                                if (!dr1["lvl1_program_text"].ToString().ToUpper().Contains("SELECT"))
                                {
                                    res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("JOIN")) + 4);
                                    if (res.Contains(" "))
                                    {
                                        res = res.Split(' ')[1];
                                    }
                                    sub_query_ststbl = "N";

                                }
                                else
                                {
                                    res = null;
                                    sub_query_ststbl = "Y";

                                }

                            }
                            else if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("INSERT INTO"))
                            {

                                if (!dr1["lvl1_program_text"].ToString().ToUpper().Contains("SELECT"))
                                {
                                    res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("INSERT INTO")) + 10);
                                    if (res.Contains(" "))
                                    {
                                        res = res.Split(' ')[1];
                                    }
                                    sub_query_ststbl = "N";

                                }
                                else
                                {
                                    res = null;
                                    sub_query_ststbl = "Y";

                                }

                            }
                            else if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("CREATE"))
                            {
                                if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("VOLATILE"))
                                {
                                    tbl_type = "V";
                                }

                                res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("CREATE VOLATILE TABLE")) + 21);
                                if (res.Contains(" "))
                                {
                                    res = res.Split(' ')[1];
                                }
                                sub_query_ststbl = "N";




                            }
                            else if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("UPDATE"))
                            {


                                res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("UPDATE")) + 6);
                                if (res.Contains(" "))
                                {
                                    res = res.Split(' ')[1];
                                }
                                sub_query_ststbl = "N";




                            }
                            else if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("DELETE"))
                            {

                                if (!dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM"))
                                {
                                    res = dr1["lvl1_program_text"].ToString().ToUpper().Substring((dr1["lvl1_program_text"].ToString().ToUpper().IndexOf("DELETE")) + 6);
                                    if (res.Contains(" "))
                                    {
                                        res = res.Split(' ')[1];
                                    }
                                    sub_query_ststbl = "N";
                                }




                            }
                            else
                            {
                                if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("WHERE"))
                                {
                                    isfrom = false;
                                }
                                if (isbracket == false)
                                {
                                    if (iscomma == true)
                                    {
                                        res = dr1["lvl1_program_text"].ToString().ToUpper().TrimStart();
                                        if (res.Contains(" "))
                                        {
                                            res = res.Split(' ')[0];
                                        }
                                        sub_query_ststbl = "N";
                                        iscomma = false;
                                        isbracket = false;
                                    }
                                    else
                                    {
                                        if (isfrom == true)
                                        {
                                            res = dr1["lvl1_program_text"].ToString().ToUpper().TrimStart();
                                            if (res.Contains(" "))
                                            {
                                                res = res.Split(' ')[0];
                                            }
                                            sub_query_ststbl = "N";
                                        }
                                        else
                                        {
                                            isbracket = false;
                                            res = null;
                                        }
                                    }
                                }
                                else
                                {

                                    isbracket = false;
                                    res = null;

                                }




                            }


                            if (restbls.Count != 0)
                            {
                                foreach (var tbl in restbls)
                                {
                                    if (tbl != null && tbl.Trim() != "")
                                    {
                                        if (sqlnoitrttbl == Convert.ToInt32(dr1["sql_no"].ToString()))
                                        {
                                            colnotbl = colnotbl + 1;
                                        }
                                        else
                                        {
                                            colnotbl = 1;
                                            sqlnoitrttbl = Convert.ToInt32(dr1["sql_no"].ToString());
                                        }
                                    }
                                    SqlCommand cmdqtbl = new SqlCommand("SP_InsertQueryTbl", con);
                                    cmdqtbl.CommandType = CommandType.StoredProcedure;
                                    cmdqtbl.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                    cmdqtbl.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                    cmdqtbl.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_name", tbl);

                                    cmdqtbl.Parameters.AddWithValue("@sub_query_sts", sub_query_ststbl);
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_no", colnotbl);
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_type", tbl_type);

                                    int o = cmdqtbl.ExecuteNonQuery();
                                }
                            }
                            else
                            {
                                if (res != null && res.Trim() != "")
                                {
                                    if (sqlnoitrttbl == Convert.ToInt32(dr1["sql_no"].ToString()))
                                    {
                                        colnotbl = colnotbl + 1;
                                    }
                                    else
                                    {
                                        colnotbl = 1;
                                        sqlnoitrttbl =
                                            Convert.ToInt32(dr1["sql_no"].ToString());
                                    }
                                    SqlCommand cmdqtbl = new SqlCommand("SP_InsertQueryTbl", con);
                                    cmdqtbl.CommandType = CommandType.StoredProcedure;
                                    cmdqtbl.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                    cmdqtbl.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                    cmdqtbl.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_name", res);

                                    cmdqtbl.Parameters.AddWithValue("@sub_query_sts", sub_query_ststbl);
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_no", colnotbl);
                                    cmdqtbl.Parameters.AddWithValue("@qs_tbl_type", tbl_type);

                                    int o = cmdqtbl.ExecuteNonQuery();
                                }


                            }
                        }
                        catch(Exception ex)
                        {

                        }
                        }
                }
            }
            catch(Exception ex)
            {

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

        public static string TextFollowing(string searchTxt, string value)
        {
            if (!String.IsNullOrEmpty(searchTxt) && !String.IsNullOrEmpty(value))
            {
                int index = searchTxt.IndexOf(value);
                if (-1 < index)
                {
                    int start = index + value.Length;
                    if (start <= searchTxt.Length)
                    {
                        return searchTxt.Substring(start);
                    }
                }
            }
            return null;
        }

        private void AddLevel2(string businesss_process, string program_name)
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectLevel1", con);
                //SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectSqlToConvert", con);


                cmdsqllvl1.CommandType = CommandType.StoredProcedure;
                cmdsqllvl1.Parameters.AddWithValue("@business_process", businesss_process);
                cmdsqllvl1.Parameters.AddWithValue("@program_name", program_name);

                SqlDataAdapter sdasqllvl1 = new SqlDataAdapter(cmdsqllvl1);
                DataSet dssqllvl1 = new DataSet();
                sdasqllvl1.Fill(dssqllvl1);

                foreach (DataTable table1 in dssqllvl1.Tables)
                {
                    int sqlnocall = 0;
                    foreach (DataRow dr1 in table1.Rows)
                    {
                        try {
                            SqlCommand cmdgrp = new SqlCommand("SP_Selectlevel1SqlGrp", con);
                            cmdgrp.CommandType = CommandType.StoredProcedure;
                            cmdgrp.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                            cmdgrp.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
                            cmdgrp.Parameters.AddWithValue("@sqlno", dr1["sql_no"].ToString());

                            SqlDataAdapter sdagrp = new SqlDataAdapter(cmdgrp);
                            DataSet dsgrp = new DataSet();
                            sdagrp.Fill(dsgrp);

                            foreach (DataTable tablegrp in dsgrp.Tables)
                            {
                                List<string> progtextarr = new List<string>();
                                string progtextres = "";
                                foreach (DataRow drgrp in tablegrp.Rows)
                                {
                                    progtextarr.Add(drgrp["lvl1_program_text"].ToString().ToUpper());

                                }

                                if (sqlnocall != Convert.ToInt32(dr1["sql_no"].ToString()))
                                {
                                    sqlnocall = Convert.ToInt32(dr1["sql_no"].ToString());
                                    string progtextstr = string.Join("~ ", progtextarr);
                                    if (progtextstr.StartsWith("SELECT") && progtextstr.EndsWith(";"))
                                    {
                                        if (!progtextstr.Contains("FROM"))
                                        {
                                            progtextres = progtextstr.Replace("~", "").ToString().Trim(';') + " FROM DUAL;";
                                        }
                                        else
                                        {
                                            progtextres = progtextstr.Replace("~", "");
                                        }
                                        SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                        cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                        cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                        cmd.Parameters.AddWithValue("@program_text", progtextres);
                                        cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                        cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                        cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                        int l = cmd.ExecuteNonQuery();

                                    }
                                    else if (dr1["sql_type"].ToString() == "ISELECT")
                                    {
                                        //progtextres = progtextstr.Replace("~", "").Substring(0, progtextstr.IndexOf(" SELECT")+6); // string till select
                                        int Posres1 = 0;
                                        int Posres2 = progtextstr.ToString().ToUpper().IndexOf("SELECT") + 6;
                                        progtextres = progtextstr.ToString().ToUpper().Substring(Posres1, Posres2 - Posres1);
                                        string[] elements = progtextres.Split('~');
                                        foreach (string items in elements)
                                        {
                                            SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                            cmd.CommandType = CommandType.StoredProcedure;
                                            cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                            cmd.Parameters.AddWithValue("@program_text", items);
                                            cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                            int l = cmd.ExecuteNonQuery();
                                        }
                                        // Regex regex = new Regex("\\((?<TextInsideBrackets>\\w+)\\)"); // from from till groupby
                                        int realfrompos = 0;
                                        SqlCommand cmdlv2 = new SqlCommand("SP_CreateLevel2_cols", con);
                                        cmdlv2.CommandType = CommandType.StoredProcedure;
                                        cmdlv2.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                        cmdlv2.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                        cmdlv2.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                        int lv2 = cmdlv2.ExecuteNonQuery();


                                        if (progtextstr.Contains("FROM"))
                                        {
                                            //int index = progtextstr.IndexOf("FROM");

                                            int i = 0;
                                            int j = 0;
                                            int k = 0;
                                            bool jlp = false;
                                            bool ilp = false;


                                            while ((i = progtextstr.IndexOf("FROM", i)) != -1)
                                            {

                                                i++;
                                                while ((j = progtextstr.IndexOf("(", j)) != -1)
                                                {

                                                    j++;


                                                    while ((k = progtextstr.IndexOf(")", k)) != -1)
                                                    {

                                                        k++;

                                                        if (i > j && i < k)
                                                        {
                                                            jlp = true;

                                                            break;
                                                        }
                                                        else
                                                        {
                                                            realfrompos = i;
                                                            ilp = true;
                                                            break;
                                                        }
                                                    }
                                                    if (jlp == true)
                                                    {
                                                        break;
                                                    }
                                                }

                                                if (ilp == true)
                                                {
                                                    break;
                                                }

                                                realfrompos = i;

                                            }
                                            if (progtextstr.Contains("GROUP BY"))
                                            {

                                                int Posa = realfrompos - 1;
                                                int Posb = progtextstr.ToString().ToUpper().IndexOf("GROUP BY") + 8;
                                                string res = progtextstr.ToString().ToUpper().Substring(Posa, Posb - Posa);

                                                string[] elements1 = res.Split('~');
                                                foreach (string items in elements1)
                                                {
                                                    SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                    cmd.CommandType = CommandType.StoredProcedure;
                                                    cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                    cmd.Parameters.AddWithValue("@program_text", items);
                                                    cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                    int l = cmd.ExecuteNonQuery();
                                                }
                                                int Posfst = progtextstr.ToString().ToUpper().IndexOf("GROUP BY") + 8;
                                                int Poslst = progtextstr.ToString().ToUpper().IndexOf(";");
                                                string grouptext = progtextstr.ToString().ToUpper().Substring(Posfst, Poslst - Posfst);
                                                if (grouptext.Any(char.IsDigit) == true)
                                                {
                                                    if (grouptext.Contains("HAVING COUNT(*) > 1"))
                                                    {
                                                        grouptext = grouptext.Replace("HAVING COUNT(*) > 1", "");
                                                    }
                                                    SqlCommand cmdlv2grp = new SqlCommand("SP_CreateLevel2_grp_cols", con);
                                                    cmdlv2grp.CommandType = CommandType.StoredProcedure;
                                                    cmdlv2grp.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                    cmdlv2grp.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                    cmdlv2grp.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                    cmdlv2grp.Parameters.AddWithValue("@lvl1_line_num", dr1["lvl1_line_num"].ToString());
                                                    cmdlv2grp.Parameters.AddWithValue("@groupbytext", grouptext.Replace("~", ""));

                                                    int lv2grp = cmdlv2grp.ExecuteNonQuery();

                                                    SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                    cmd.CommandType = CommandType.StoredProcedure;
                                                    cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                    cmd.Parameters.AddWithValue("@program_text", ";");
                                                    cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                    int l = cmd.ExecuteNonQuery();
                                                }
                                                else
                                                {
                                                    //int Pos1fs = realfrompos-1;
                                                    //int Pos1ls = progtextstr.ToString().ToUpper().IndexOf(";");
                                                    //string resfsls = progtextstr.ToString().ToUpper().Substring(Pos1fs, Pos1ls - Pos1fs);
                                                    string[] elements2 = grouptext.Split('~');

                                                    foreach (string items in elements2)
                                                    {
                                                        SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                        cmd.CommandType = CommandType.StoredProcedure;
                                                        cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                        cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                        cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                        cmd.Parameters.AddWithValue("@program_text", items);
                                                        cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                        cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                        cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                        int l = cmd.ExecuteNonQuery();
                                                    }
                                                }

                                            }
                                            else
                                            {
                                                int Posa = realfrompos - 1;
                                                int Posb = progtextstr.ToString().ToUpper().IndexOf(";") + 1;
                                                string res = progtextstr.ToString().ToUpper().Substring(Posa, Posb - Posa);
                                                string[] elements1 = res.Split('~');

                                                foreach (string items in elements1)
                                                {
                                                    SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                    cmd.CommandType = CommandType.StoredProcedure;
                                                    cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                    cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                    cmd.Parameters.AddWithValue("@program_text", items);
                                                    cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                    cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                    int l = cmd.ExecuteNonQuery();
                                                }
                                            }

                                        }


                                    }
                                    else if (dr1["sql_type"].ToString() == "CREATE")
                                    {
                                        if (progtextstr.ToString().ToUpper().Contains("VOLATILE"))
                                        {
                                            progtextres = Volatile(progtextstr.ToString().ToUpper());
                                            string[] elements = progtextres.Split('~');
                                            foreach (string items in elements)
                                            {
                                                SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                cmd.CommandType = CommandType.StoredProcedure;
                                                cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                cmd.Parameters.AddWithValue("@program_text", items);
                                                cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                int l = cmd.ExecuteNonQuery();
                                            }
                                        }
                                        else
                                        {
                                            string[] elements = progtextstr.Split('~');


                                            foreach (string items in elements)
                                            {
                                                SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                                cmd.CommandType = CommandType.StoredProcedure;
                                                cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                                cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                                cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                                cmd.Parameters.AddWithValue("@program_text", items);
                                                cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                                cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                                cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                                int l = cmd.ExecuteNonQuery();
                                            }
                                        }
                                    }
                                    else if (dr1["sql_type"].ToString() == "USELECT")
                                    {
                                        progtextres = MergeUpdate(progtextstr.ToString().ToUpper());
                                        string[] elements = progtextres.Split('~');
                                        foreach (string items in elements)
                                        {
                                            SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                            cmd.CommandType = CommandType.StoredProcedure;
                                            cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                            cmd.Parameters.AddWithValue("@program_text", items);
                                            cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                            int l = cmd.ExecuteNonQuery();
                                        }

                                    }
                                    else
                                    {
                                        string[] elements = progtextstr.Split('~');


                                        foreach (string items in elements)
                                        {
                                            SqlCommand cmd = new SqlCommand("SP_Insertlevel2", con);
                                            cmd.CommandType = CommandType.StoredProcedure;
                                            cmd.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                                            cmd.Parameters.AddWithValue("@line_num", dr1["lvl1_line_num"].ToString());
                                            cmd.Parameters.AddWithValue("@program_text", items);
                                            cmd.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                                            cmd.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                                            int l = cmd.ExecuteNonQuery();
                                        }
                                    }
                                }
                            }
                        }
                        catch(Exception ex)
                        {

                        }
                        }

                }
                SqlCommand cmdadprfx = new SqlCommand("SP_ADD_VTL_PREFIX", con);
                cmdadprfx.CommandType = CommandType.StoredProcedure;
               
                int ladprfx = cmdadprfx.ExecuteNonQuery();

                SqlCommand cmdlvluser = new SqlCommand("SP_Insertlevel_user", con);
                cmdlvluser.CommandType = CommandType.StoredProcedure;
                cmdlvluser.Parameters.AddWithValue("@business_process", DropDownListBusProc.SelectedValue);
                cmdlvluser.Parameters.AddWithValue("@program_name", DropDownListProg.SelectedValue);
                int luser = cmdlvluser.ExecuteNonQuery();
            }
            catch(Exception ex)
            {

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

        public static string Volatile(string str)
        {
           
                string Fststr = ",";
                string Lststr = "(";
                int Posres1 = str.ToString().ToUpper().IndexOf(Fststr);
                int Posres2 = str.ToString().ToUpper().IndexOf(Lststr);

                string res = str.ToString().ToUpper().Substring(Posres1, Posres2 - Posres1);
                res = str.Replace(res, "");
                res = res.Replace("VOLATILE TABLE", "PRIVATE TEMPORARY TABLE");
                res = res.Replace("INTEGER","NUMBER");
                res = res.Replace("NOT NULL", "");
                res = res.Replace(" CHAR", " VARCHAR2");
                res = res.Replace("PRESERVE ROWS", "PRESERVE DEFINITION");
            if(res.Contains("ON COMMIT"))
            {
                int Posres3=0;
                if (res.Contains("PRIMARY INDEX"))
                {
                    if (res.Contains("UNIQUE"))
                    {
                        Posres3 = res.ToString().ToUpper().IndexOf("UNIQUE PRIMARY INDEX");

                    }
                    else
                    {
                        Posres3 = res.ToString().ToUpper().IndexOf("PRIMARY INDEX");

                    }


                    int Posres4 = res.ToString().ToUpper().IndexOf("ON COMMIT");
                    string res1 = res.ToString().ToUpper().Substring(Posres3, Posres4 - Posres3);
                    if (res1 != "")
                    {
                        res = res.Replace(res1, "");
                    }
                }
            }
            

            return res;
           
        }

        //public static string TopReplace(string str)
        //{


        //    return;

        //}


        private string MergeUpdate(string InputQry)
        {
            string outputQry = "";
            string tgptbl = "";
            string srctbl = "";
            string onparm = "";
            string setparm = "";
            tgptbl = InputQry.Substring(7, InputQry.IndexOf("FROM") - 7);
            srctbl = InputQry.Substring(InputQry.IndexOf("FROM") + 4, (InputQry.IndexOf("SET") - (InputQry.IndexOf("FROM") + 4)));
            onparm = InputQry.Substring
                (
                InputQry.IndexOf("WHERE", InputQry.IndexOf("SET")) + 6,
                (InputQry.IndexOf(";") - (InputQry.IndexOf("WHERE", InputQry.IndexOf("SET")) + 6))
                );

            setparm = InputQry.Substring
                (
                InputQry.IndexOf("SET") + 3,
                (InputQry.IndexOf("WHERE", InputQry.IndexOf("SET")) - (InputQry.IndexOf("SET") + 3))
                );

            outputQry = "MERGE INTO " + tgptbl + " USING " + srctbl + " ON ( " + onparm + " ) WHEN MATCHED THEN UPDATE SET " + setparm + " ;";


            return outputQry;
        }
        static bool checkParentheses(string str)
        {
            if (str.Length <= 1)
                return false;

            int count = 0;

            for (int i = 0; i < str.Length; i++)
            {
                switch (str[i])
                {
                    case '(':
                        count++;
                        break;

                    case ')':
                        count--;
                        if (count < 0)
                            return false;
                        break;
                }
            }

            return (count == 0);

        }

        protected void DropDownListProg_SelectedIndexChanged(object sender, EventArgs e)
        {
            ChkCodeConvertSts();
        }

        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")
                {
                    Button1.Visible = false;
                    divMsg.Visible = true;
                }
                else
                {
                    Button1.Visible = true;
                    divMsg.Visible = false;
                }
            }
            else
            {
                Button1.Visible = true;
                divMsg.Visible = false;
            }
        }
    }
}