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

namespace ConvertApp
{
    public partial class BatchUpload : System.Web.UI.Page
    {
        GenClass obj = new GenClass();
        SqlConnection con;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DropDownListBusProc.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));
                DropDownListProg.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));
                BindBusinessProcess();

                BindMasterBusinessProc();
            }
        }

        protected void Browse_Click(object sender, EventArgs e)
        {
            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 = DropDownListlogicalgrp.SelectedValue;
                        }

                        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;
                        if(line1.Contains("SEL "))
                        {
                            program_text=program_text.Replace("SEL ", "SELECT ");
                        }
                        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.StartsWith("INSERT\t"))
                                        {
                                            line1 = line1.Replace("INSERT\t", "INSERT ");
                                        }
                                        if (line1.ToUpper().Trim().StartsWith(dr["sql_type"].ToString().ToUpper()))
                                        {
                                            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("/*"));
                                    if (afterRemoval.Contains("*/"))
                                    {
                                        cmntblkfnd = true;
                                    }
                                    else
                                    {
                                        cmntblkfnd = false;

                                    }

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

                }
                BindBusinessProcess();
                if (DropDownListBusProc.Items.FindByValue(business_process) != null)
                {
                    DropDownListBusProc.SelectedValue = business_process;

                }
                else
                {
                    DropDownListBusProc.SelectedValue = "0";
                }
                BindProgram();
                if (DropDownListProg.Items.FindByValue(program_name.Replace(" ", "")) != null)
                {
                    DropDownListProg.SelectedValue = program_name.Replace(" ", "");


                }
                else
                {
                    DropDownListProg.SelectedValue = "0";

                }
                BindGrid();
                lblSuccess.Text = string.Format("{0} files have been converted successfully.", FileUpload1.PostedFiles.Count);

            }
            catch (Exception ex)
            {

                //throw ex;
            }

            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;
        }
        private void BindMasterBusinessProc()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_LogicalGrp", con);
                cmd.CommandType = CommandType.StoredProcedure;

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

                if (dt.Rows.Count > 0)
                {
                    DropDownListlogicalgrp.DataSource = dt;
                    DropDownListlogicalgrp.DataTextField = "Name";
                    DropDownListlogicalgrp.DataValueField = "Name";
                    DropDownListlogicalgrp.DataBind();
                    //DropDownListlogicalgrp.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));

                }
                else
                {
                    //DropDownListlogicalgrp.Items.Clear();
                    //DropDownListlogicalgrp.Items.Insert(0, new System.Web.UI.WebControls.ListItem("-Select-", "0"));
                }
            }
            catch (Exception ex)
            {

                //throw ex;
            }

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