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.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

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

                BindBusinessProcess();
                BindProgram();
            }

        }

        private void BindBusinessProcess()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_luser_PRG_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 = "Name";
                    DropDownListBusProc.DataValueField = "ID";
                    DropDownListBusProc.DataBind();
                    

                }
                else
                {
                    
                }
            }
            catch (Exception ex)
            {

                //throw ex;
            }

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

        private void BindProgram()
        {
            try
            {
                con = obj.Connect();
                SqlCommand cmd = new SqlCommand("SP_Select_luser_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 = "Name";
                    DropDownListProg.DataValueField = "ID";
                    DropDownListProg.DataBind();

                }
                else
                {
                    
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

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


        private void BindGrid()
        {
            try
            {
                string spname = "";
                if(CheckBoxremark.Checked==true)
                {
                    spname = "SP_Select_luser_PRG_TEXT_WC";
                }
                else
                {
                    spname = "SP_Select_luser_PRG_TEXT";

                }
                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(spname, sqlParams);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    GridView1.DataSource = null;
                    ButtonSqlCnvt.Visible = false;
                    ButtonConverted.Visible = false;
                }
                else
                {
                    GridView1.DataSource = ds;
                    ButtonSqlCnvt.Visible = true;
                    ButtonConverted.Visible = true;
                }
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

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

        protected void ButtonSqlCnvt_Click(object sender, EventArgs e)
        {
            ExportTextFile(sender,e);
        }
        protected void ExportTextFile(object sender, EventArgs e)
        {


            string txt = string.Empty;

            //foreach (TableCell cell in GridView1.HeaderRow.Cells)
            //{
            //    txt += cell.Text + "\t\t";
            //}


            //txt += "\r\n";

            foreach (GridViewRow row in GridView1.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {

                    //Add the Data rows.
                    txt += cell.Text.Replace("&#39;","'").Replace("&lt;", "<").Replace("&nbsp;", "").Replace("&nbsp","").Replace("&gt;", ">") + "\t\t";
                    //txt += cell.Text + "\t\t";

                }

                //Add new line.
                txt += "\r\n";
            }

            //Download the Text file.

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=" + DropDownListProg.SelectedValue.Replace(".sh", "") + ".sql");
            Response.Charset = "";
            Response.ContentType = "application/text";
            //Response.Output.Write(txt);
            Response.Output.Write(txt);
           
            Response.Flush();
            Response.SuppressContent = true;
            Response.End();
        }
        protected void DropDownListBusProc_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindProgram();
            GridView1.DataSource = null;
            GridView1.DataBind();
        }

        protected void DropDownListProg_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridView1.DataSource = null;
            GridView1.DataBind();
        }

        protected void ButtonConverted_Click(object sender, EventArgs e)
        {

        }
    }
}