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

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

        protected void Browse_Click(object sender, EventArgs e)
        {

            //Thread thdSyncRead = new Thread(new ThreadStart(openfolder));
            //thdSyncRead.SetApartmentState(ApartmentState.STA);
            //thdSyncRead.Start();
            try
            {


                con = obj.Connect();

                var program_name = "";
                var business_process = "";
                //if (Directory.Exists(fbd.SelectedPath))
                //{
                string[] operands;
                //con = obj.Connect();
                SqlCommand cmd1 = new SqlCommand("SP_SelectSql_Type", con);
                cmd1.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
                DataSet ds1 = new DataSet();
                sda1.Fill(ds1);

                //string[] ArrayToSearch = new string[] { "SELECT", "CREATE", "INSERT", "UPDATE", "DELETE" };

                char[] sep = new char[2];
                sep[0] = '\\';
                sep[1] = '\\';
                string[] Directories = System.IO.Path.GetDirectoryName(FileUpload1.PostedFile.FileName).Split(sep);
                string DirectoryName = (Directories.Length == 2 && Directories[1].Length == 0) ? Directories[0] : Directories[Directories.Length - 1];
                
                foreach (HttpPostedFile postedfile in FileUpload1.PostedFiles)
                {

                    //string fl = Server.MapPath(postedfile.FileName); 
                    int sql_no = 0;
                    int sqlnoval = 0;
                    int cntlines = 0;
                    int counter = 0;
                    string line;
                    var sql_fl = "";

                    var sql_type = "";
                    var program_text = "";
                    bool isinsert = false;



                    System.IO.StreamReader file1 =
                        new System.IO.StreamReader(postedfile.InputStream);
                    // Read the file and display it line by line.  


                    while ((line = file1.ReadLine()) != null)
                    {
                        if (line.Contains("Script Name")|| line.Contains("Script name")|| line.Contains("SCRIPT NAME")|| line.Contains("script name"))
                        {
                            program_name = line.Split(':')[1];

                            break;
                        }

                    }

                    program_name = program_name.Replace(" ", "");
                    program_name = program_name.Replace("#", "");

                    file1.BaseStream.Position = 0;
                    file1.DiscardBufferedData();
                    string line1;
                    //System.IO.StreamReader fileval =
                    //    new System.IO.StreamReader(postedfile.InputStream);
                    while ((line1 = file1.ReadLine()) != null)
                    {
                        bool linenmsql = false;
                        SqlCommand cmd = new SqlCommand("SP_Insertlevel1", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (DirectoryName.ToString() != "")
                        {
                            business_process = DirectoryName;
                        }
                        else
                        {
                            business_process = "Busp1";
                        }

                        cmd.Parameters.AddWithValue("@business_process", business_process.Trim());
                        //System.Console.WriteLine(line1);
                        counter++;
                        //if (line.Contains("Script Name"))
                        //{
                        //    program_name = line.Split(':')[1];
                        //}
                        program_text = line1;

                        cmd.Parameters.AddWithValue("@program_name", program_name.Trim());
                        cmd.Parameters.AddWithValue("@line_num", counter);
                        cmd.Parameters.AddWithValue("@program_text", program_text);


                        foreach (DataTable table in ds1.Tables)
                        {
                            foreach (DataRow dr in table.Rows)//(string sqlflval in ArrayToSearch)
                            {
                                if (linenmsql == false)
                                {
                                    if (cntlines == 0)
                                    {

                                        if (line1.ToUpper().Trim().StartsWith(dr["sql_type"].ToString().ToUpper().Trim()))
                                        {
                                            if (line1.ToUpper().Trim().Contains("INSERT"))
                                            {
                                                if (line1.ToUpper().Trim().Contains("SELECT"))
                                                {
                                                    sql_type = dr["sql_type"].ToString().ToUpper().Trim();

                                                    //sql_type = "I_SELECT";
                                                }
                                                else
                                                {
                                                    sql_type = dr["sql_type"].ToString().ToUpper().Trim();
                                                    isinsert = true;
                                                }

                                            }
                                            else
                                            {
                                                sql_type = dr["sql_type"].ToString().ToUpper().Trim();

                                            }

                                            cntlines = 1;
                                            sql_fl = "Y";

                                            sql_no++;


                                            if (line1.Trim().EndsWith(";"))
                                            {
                                                cntlines = 0;
                                            }
                                            linenmsql = true;

                                        }

                                        else
                                        {

                                            sql_fl = "N";
                                            sqlnoval = 0;
                                        }



                                    }
                                    else if (cntlines == 1)
                                    {
                                        sql_fl = "Y";
                                        //if(isinsert == true)
                                        //    {
                                        //        if (line.ToUpper().Trim().Contains("SELECT"))
                                        //        {
                                        //            //sql_type = "I_SELECT";
                                        //        }
                                        //        else
                                        //        {
                                        //            //if (sql_type != "I_SELECT")
                                        //            //{
                                        //            //    sql_type = dr["sql_type"].ToString().ToUpper().Trim();
                                        //            //}

                                        //        }

                                        //    }
                                        //    else
                                        //    {
                                        //        //sql_type = sql_type;

                                        //    }
                                        //sql_no++;
                                        if (line1.Trim().EndsWith(";"))
                                        {
                                            cntlines = 0;
                                            isinsert = false;
                                        }
                                        linenmsql = true;
                                    }


                                }

                            }
                        }

                        cmd.Parameters.AddWithValue("@sql_fl", sql_fl);
                        cmd.Parameters.AddWithValue("@sql_no", sql_no);
                        cmd.Parameters.AddWithValue("@sqlnoval", sqlnoval);
                        cmd.Parameters.AddWithValue("@sql_type", sql_type);

                        int k = cmd.ExecuteNonQuery();
                    }
                    SqlCommand cmdupd = new SqlCommand("SP_UPDlevel1_SQLTYPE", con);
                    cmdupd.CommandType = CommandType.StoredProcedure;


                    cmdupd.Parameters.AddWithValue("@business_process", business_process.Trim());
                    cmdupd.Parameters.AddWithValue("@program_name", program_name.Trim());

                    int m = cmdupd.ExecuteNonQuery();
                    // -----------New tablelevel1 insert refined code-------------------------------------------
                    SqlCommand cmdsrfnlvl1 = new SqlCommand("SP_SelectLevel0", con);
                    cmdsrfnlvl1.CommandType = CommandType.StoredProcedure;
                    cmdsrfnlvl1.Parameters.AddWithValue("@business_process", business_process.Trim());
                    cmdsrfnlvl1.Parameters.AddWithValue("@program_name", program_name.Trim());

                    SqlDataAdapter sdasrfnlvl1 = new SqlDataAdapter(cmdsrfnlvl1);
                    DataSet dssrfnlvl1 = new DataSet();
                    sdasrfnlvl1.Fill(dssrfnlvl1);

                    foreach (DataTable table1 in dssrfnlvl1.Tables)
                    {
                        
                        bool cmntblkfnd = false;
                        foreach (DataRow dr1 in table1.Rows)
                        {
                            bool endcmntblk = false;


                            Regex comments1 = new Regex(@"--.*?$", RegexOptions.Multiline);
                            //Regex comments2 = new Regex(@"/\*(.*?)\*/", RegexOptions.Singleline);
                            string afterRemoval = comments1.Replace(dr1["program_text"].ToString().Trim(), "");


                            if (endcmntblk == false)
                            {
                                if (afterRemoval.Contains("/*"))
                                {


                                    afterRemoval = afterRemoval.Substring(0, afterRemoval.IndexOf("/*"));
                                    cmntblkfnd = true;

                                }
                                if (cmntblkfnd == true)
                                {

                                    if (afterRemoval.Contains("*/"))
                                    {
                                        afterRemoval = afterRemoval.Substring(afterRemoval.IndexOf("*/") + 2);

                                        endcmntblk = true;

                                        cmntblkfnd = false;
                                    }
                                    else
                                    {
                                        afterRemoval = "";
                                    }
                                    

                                    //else
                                    //{
                                    //    afterRemoval = "";
                                    //}



                                }
                                else
                                {
                                    afterRemoval = comments1.Replace(dr1["program_text"].ToString().Trim(), "");
                                }
                            }





                            SqlCommand cmdrfnlvl1 = new SqlCommand("SP_InsertRefnlevel1", con);
                            cmdrfnlvl1.CommandType = CommandType.StoredProcedure;
                            cmdrfnlvl1.Parameters.AddWithValue("@lvl1_business_process", dr1["business_process"].ToString().Trim());
                            cmdrfnlvl1.Parameters.AddWithValue("@lvl1_program_name", dr1["program_name"].ToString().Trim());
                            cmdrfnlvl1.Parameters.AddWithValue("@lvl1_line_num", dr1["line_num"].ToString().Trim());
                            cmdrfnlvl1.Parameters.AddWithValue("@lvl1_program_text", afterRemoval);

                            cmdrfnlvl1.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());
                            cmdrfnlvl1.Parameters.AddWithValue("@sql_fl", dr1["sql_fl"].ToString());

                            cmdrfnlvl1.Parameters.AddWithValue("@sql_type", dr1["sql_type"].ToString());


                            int o1 = cmdrfnlvl1.ExecuteNonQuery();
                        }
                    }

//----------------------------------END-----------------------------------
                            //System.Windows.Forms.MessageBox.Show("Files found: " + files.Length.ToString(), "Message");

                            string grprplc = "";


//----------------------------------------column insert--------------------------------------------------------------------------------------------------------------

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

                            var sub_query_sts = "";
                            int colno = 0;
                            if (dr1["sql_type"].ToString() == "SELECT")
                            {
                                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 = 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 = 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 finalresult = "";
                                string resbrkup = "";
                                string res = "";
                                int count1 = 0;
                                string FirstString = "SELECT";
                                string LastString;

                                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++;
                                                }
                                                if(resbrkup.Contains("FROM"))
                                            {
                                               
                                                    resbrkup = resbrkup.Remove(resbrkup.IndexOf("FROM"));

                                            }

                                        }
                                        }
                                        else
                                        {
                                            LastString = "FROM";
                                        }
                                   // }
                                }
                                else
                                {
                                    LastString = ";";
                                }
                                int Pos1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                                int Pos2 = 0;
                                if (count1 > 1)
                                {
                                    Pos2 = resbrkup.ToString().ToUpper().LastIndexOf("FROM");
                                    FinalString = resbrkup.ToUpper().Substring(0, Pos2 - 0);
                                }
                                else
                                {
                                    Pos2 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                                    FinalString = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1, Pos2 - Pos1);
                                }
                                //if(FinalString.Contains("FROM"))
                                //    {

                                //    int index1 = FinalString.IndexOf("FROM");
                                //    if (index1 != -1)
                                //    {
                                //         FinalString = FinalString.Remove(index1);
                                //    }
                                //    else
                                //    {
                                //        // "from" not found
                                //    }
                                //}
                                
                                

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


                                int bracketCount = 1;
                                var textArray = FinalString.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 = FinalString.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.
                                if(res==resbrkup)
                                {
                                    finalresult = finalresult + resbrkup  + grprplc;

                                }
                                else
                                {
                                    finalresult = finalresult + resbrkup + res + grprplc;

                                }
                                if (finalresult=="")
                                {
                                    finalresult = FinalString;
                                }
                                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);

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


                                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 = 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 = 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
                            {

                            }
//----------------------------------------------------------------END of column insert----------------------------------------------------------------------------------------------------

                            

                        //    if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("GROUP") || dr1["lvl1_program_text"].ToString().ToUpper().Contains("JOIN"))
                        //{
                        //    //foreach (Match m in re.Matches(dr1["program_text"].ToString()))
                        //    //{
                        //    //    if(!m.Value.Contains("FROM"))
                        //    //    {

                        //    string FirstString = "SELECT";
                        //    string LastString;
                        //    if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("JOIN"))
                        //    {
                        //        LastString = "JOIN";
                        //    }
                        //    else
                        //    {
                        //        LastString = "FROM";

                        //    }

                        //    int Pos1 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString) + FirstString.Length;
                        //    int Pos2 = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString);
                        //    int i = 0;
                        //    int a = 0;
                        //    FinalString = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1, Pos2 - Pos1);
                        //    while ((i = FinalString.IndexOf("FROM", i)) != -1)
                        //    {

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

                        //    if (a != 0)
                        //    {
                        //        grprplc = FinalString.Remove(a);
                        //    }
                        //    else
                        //    {
                        //        grprplc = FinalString;
                        //    }




                        //    //    }

                        //    //}

                        //    //string pattern = "(,(?=[^\\)]*(?:\\(|$)))";

                        //    //operands = Regex.Split(dr1["program_text"].ToString(), pattern);
                        //}
                        //else
                        //{
                        //    int count1 = 0;
                        //    string resbrkup = "";
                        //    if (dr1["sql_type"].ToString() == "SELECT" || dr1["sql_type"].ToString() == "ISELECT")
                        //    {
                        //        string FirstString = "SELECT";
                        //        string LastString;
                                    
                        //            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"))
                        //                {
                        //                    resbrkup = selectfrmdt;
                        //                }
                        //            }
                        //            if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM("))
                        //            {
                        //                string FirstString1 = "FROM(";
                        //                string LastString1 = "FROM";
                        //                int Pos1c = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(FirstString1) + FirstString1.Length;
                        //                int Pos2d = dr1["lvl1_program_text"].ToString().ToUpper().IndexOf(LastString1);
                        //                querystatus = "Y";
                        //                string selectfrmdt1 = dr1["lvl1_program_text"].ToString().ToUpper().Substring(Pos1c, Pos2d - Pos1c);

                        //                resbrkup += selectfrmdt1.Replace("SELECT", "");
                        //                FinalString = resbrkup;


                        //            }
                        //            else
                        //            {

                        //                if (dr1["lvl1_program_text"].ToString().ToUpper().Contains("FROM"))
                        //                {
                        //                    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++;
                        //                    }

                        //                    if (count > 1)
                        //                    {
                        //                        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++;
                        //                        }


                        //                    }
                        //                    else
                        //                    {
                        //                        LastString = "FROM";
                        //                    }
                        //                }

                        //                else
                        //                {
                        //                    LastString = ";";

                        //                }

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

                        //            }
                        //    }

                        //}
                            
                        //    //if(dr1["sql_type"].ToString().ToUpper().Contains("SELECT"))
                        //    //{
                        //    //    if(!dr1["sql_type"].ToString().ToUpper().Contains("FROM"))
                        //    //    {
                        //    if (dr1["sql_type"].ToString() == "SELECT" || dr1["sql_type"].ToString() == "ISELECT" || dr1["sql_type"].ToString() == "CREATE")
                        //{

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


                        //            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);
                        //            }
                                  

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


                        //    int bracketCount = 1;
                        //    var textArray = FinalString.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;
                        //        }
                        //    }
                        //    string finalresult = "";
                        //    if (grprplc != "")
                        //    {
                        //            if (FinalString != "")
                        //            {
                        //                finalresult = grprplc+FinalString;
                        //            }
                        //            else
                        //            {
                        //                finalresult = grprplc;

                        //            }


                        //        }
                        //    else
                        //    {
                        //        finalresult = FinalString.ToString().ToUpper();

                        //    }

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

                        //        string result = FinalString.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.

                        //    //Using the Split method to "split" your initial string
                        //    var sepcol = finalresult.Split(',');
                            
                        //    //Iterate through each of the letters
                        //    foreach (var col in sepcol)
                        //    {
                        //        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 = TextFollowing(col, "END ");

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

                        //            }
                        //            else
                        //            {
                        //                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());
                        //            }

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

                            SqlCommand cmdupd1 = new SqlCommand("SP_UPD_SQLColumns", con);
                            cmdupd1.CommandType = CommandType.StoredProcedure;
                            cmdupd1.Parameters.AddWithValue("@business_process", dr1["lvl1_business_process"].ToString().Trim());
                            cmdupd1.Parameters.AddWithValue("@program_name", dr1["lvl1_program_name"].ToString().Trim());
                            cmdupd1.Parameters.AddWithValue("@sql_no", dr1["sql_no"].ToString());

                            int n = cmdupd1.ExecuteNonQuery();

                        //}

//-----------------------------------------------------------------end of column insertion--------------------------------------------------------------------------------------------------
                        //SqlCommand cmd = new SqlCommand("SP_CvtQuery", 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", dr1["lvl1_program_text"].ToString().ToUpper());
                        //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());
                        //cmd.Parameters.AddWithValue("@grp_rplc", grprplc);

                        //int l = cmd.ExecuteNonQuery();

                        grprplc = "";

                        //    }
                        //}
                    }
                }
                    //Alias and text replacement
                    //SqlCommand cmdlv2 = new SqlCommand("SP_SelectLevel2", con);
                    //cmdlv2.CommandType = CommandType.StoredProcedure;
                    //cmdlv2.Parameters.AddWithValue("@business_process", business_process.Trim());
                    //cmdlv2.Parameters.AddWithValue("@program_name", program_name.Trim());
                    //SqlDataAdapter sdalv2 = new SqlDataAdapter(cmdlv2);
                    //DataSet dslv2 = new DataSet();
                    //sdalv2.Fill(dslv2);
                    //SqlCommand cmdrplalias = new SqlCommand("SP_ReplaceAlias", con);
                    //cmdrplalias.CommandType = CommandType.StoredProcedure;
                    //cmdrplalias.Parameters.AddWithValue("@business_process", business_process.Trim());
                    //cmdrplalias.Parameters.AddWithValue("@program_name", program_name.Trim());
                    //SqlDataAdapter sdarplalias = new SqlDataAdapter(cmdrplalias);
                    //DataSet dsrplalias = new DataSet();
                    //sdarplalias.Fill(dsrplalias);
                    //SqlCommand cmdrpltxt = new SqlCommand("SP_SelectReplaceTxt", con);
                    //cmdrpltxt.CommandType = CommandType.StoredProcedure;
                    //SqlDataAdapter sdarpltxt = new SqlDataAdapter(cmdrpltxt);
                    //DataSet dsrpltxt = new DataSet();
                    //sdarpltxt.Fill(dsrpltxt);

                    //foreach (DataTable tablelv2 in dslv2.Tables)
                    //{
                    //    foreach (DataRow drlv2 in tablelv2.Rows)
                    //    {
                    //        string newprogrplc = "";
                    //        foreach (DataTable tablerplalias in dsrplalias.Tables)
                    //        {

                    //            foreach (DataRow drrplalias in tablerplalias.Rows)
                    //            {
                    //                if (newprogrplc == "")
                    //                {
                    //                    newprogrplc = drlv2["lvl2_program_text"].ToString().Replace(drrplalias["qs_col_name"].ToString(), drrplalias["qs_new_col"].ToString());
                    //                }
                    //                else
                    //                {
                    //                    newprogrplc = newprogrplc.Replace(drrplalias["qs_col_name"].ToString(), drrplalias["qs_new_col"].ToString());

                    //                }

                    //                SqlCommand cmdupdlv2 = new SqlCommand("SP_UPD_Level2_Alias", con);
                    //                cmdupdlv2.CommandType = CommandType.StoredProcedure;
                    //                cmdupdlv2.Parameters.AddWithValue("@business_process", drrplalias["qs_business_process"].ToString().Trim());
                    //                cmdupdlv2.Parameters.AddWithValue("@program_name", drrplalias["qs_program_name"].ToString().Trim());
                    //                cmdupdlv2.Parameters.AddWithValue("@sql_no", drrplalias["qs_sql_no"].ToString());
                    //                cmdupdlv2.Parameters.AddWithValue("@program_text", newprogrplc);


                    //                int q = cmdupdlv2.ExecuteNonQuery();

                    //            }

                    //        }

                    //        foreach (DataTable tabledsrpltxt in dsrpltxt.Tables)
                    //        {

                    //            foreach (DataRow drdsrpltxt in tabledsrpltxt.Rows)
                    //            {
                    //                if (newprogrplc == "")
                    //                {
                    //                    newprogrplc = drlv2["lvl2_program_text"].ToString().Replace(drdsrpltxt["old_txt"].ToString(), drdsrpltxt["new_txt"].ToString());
                    //                }
                    //                else
                    //                {
                    //                    newprogrplc = newprogrplc.Replace(drdsrpltxt["old_txt"].ToString(), drdsrpltxt["new_txt"].ToString());

                    //                }
                    //                SqlCommand cmdupdlv2 = new SqlCommand("SP_UPD_Level2_Alias", con);
                    //                cmdupdlv2.CommandType = CommandType.StoredProcedure;
                    //                cmdupdlv2.Parameters.AddWithValue("@business_process", drlv2["lvl2_business_process"].ToString().Trim());
                    //                cmdupdlv2.Parameters.AddWithValue("@program_name", drlv2["lvl2_program_name"].ToString().Trim());
                    //                cmdupdlv2.Parameters.AddWithValue("@sql_no", drlv2["lvl2_sql_no"].ToString());
                    //                cmdupdlv2.Parameters.AddWithValue("@program_text", newprogrplc);


                    //                int q = cmdupdlv2.ExecuteNonQuery();

                    //            }

                    //        }

                    //    }
                    //}



                    //----------------END-----------------------------------------------------//
                    //-------------------------------------------------------------Table Insertion-----------------------------------------------------------------------------------------

                    SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectLevel1", con);
                    //SqlCommand cmdsqllvl1 = new SqlCommand("SP_SelectSqlToConvert", con);

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

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


                        }
                                                }

                        // ----------------------------------------------------level 2 insertion----------------------------------------------------------------------------------------------                    
                        int sqlnocall = 0;
                        foreach (DataRow dr1 in table1.Rows)
                        {
                            SqlCommand cmdgrp = new SqlCommand("SP_Selectlevel1SqlGrp", con);
                            cmdgrp.CommandType = CommandType.StoredProcedure;
                            cmdgrp.Parameters.AddWithValue("@business_process", business_process.Trim());
                            cmdgrp.Parameters.AddWithValue("@program_name", program_name.Trim());
                            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)
                                                {
                                                    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();
                                            }
                                            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(";");
                                            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
                                {
                                        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();
                                        }
                                    }
                            }
                            }

                        }
                        SqlCommand cmdlvluser = new SqlCommand("SP_Insertlevel_user", con);
                        cmdlvluser.CommandType = CommandType.StoredProcedure;
                        cmdlvluser.Parameters.AddWithValue("@business_process", business_process.Trim());
                        cmdlvluser.Parameters.AddWithValue("@program_name", program_name);
                        int luser = cmdlvluser.ExecuteNonQuery();


                    }
                }
                BindBusinessProcess();
                DropDownListBusProc.SelectedValue = business_process;
                BindProgram();
                DropDownListProg.SelectedValue = program_name.Replace(" ","");
                BindGrid();
                lblSuccess.Text = string.Format("{0} files have been converted successfully.", FileUpload1.PostedFiles.Count);
            }
            catch (Exception th)
            {

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



        }

        private void BindBusinessProcess()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_BP", 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 = "lvl1_business_process";
                    DropDownListBusProc.DataValueField = "lvl1_business_process";
                    DropDownListBusProc.DataBind();
                    DropDownListBusProc.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));

                }
                else
                {
                    DropDownListBusProc.Items.Clear();
                    DropDownListBusProc.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));
                }
            }
            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", 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 = "lvl1_program_name";
                    DropDownListProg.DataValueField = "lvl1_program_name";
                    DropDownListProg.DataBind();

                }
                else
                {
                    DropDownListProg.Items.Clear();
                    DropDownListProg.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));
                }
            }
            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_LVL1_CODE", 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)
        {
            BindGrid();
        }
        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;
        }


    }
}