using System;
using System.IO;
using System.Data;
using System.Text;
using System.Diagnostics;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Threading;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OracleClient;
using System.Security.Cryptography;
using EjadaCSGenParser;
using EjadaCSLoader;
using EjadaFileLoaderLicenceVerifier;
using System.Data.OleDb;
using System.Text.RegularExpressions;

public partial class Business_CSBilling : System.Web.UI.Page
{
    public static bool start = false;
    public int status = 0;
    public string fname, ftype, cardscheme, fustatus, qb_fileno, qb_fustatus, flcode, fltype, fldesc;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SecurityAgent.AuthenticateArchive();
            LoadCardScheme();
            start = false;
        }
    }

    private void LoadCardScheme()
    {
        string DBConnection = Helper.dbConnection();

        OracleConnection oracon = new OracleConnection(DBConnection);
        oracon.Open();

        OracleCommand oracom = new OracleCommand();
        oracom.Connection = oracon;

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "CS_SETTL_INFO_EDITOR.Get_CSBILL_Product_Lookup";

        oracom.Parameters.Add("p_output_PRD", OracleType.Cursor).Direction = ParameterDirection.Output;

        OracleDataAdapter da = new OracleDataAdapter(oracom);
        DataSet ds = new DataSet();
        da.Fill(ds);

        ddlCScheme.DataSource = ds;
        ddlCScheme.DataBind();

        ddlCScheme.Items.Insert(0, "------ Card Scheme ------");
        oracon.Close();
    }

    protected void txtscsdate_TextChanged(object sender, EventArgs e)
    {
        this.Clear();
        this.Search();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        this.Clear();
        this.Search();
    }

    public void Clear()
    {
        lbl_error.Text = "";
        LblLSettlements.Text = "";

        GV_FileLS.DataSource = null;
        GV_FileLS.DataBind();

        pnl_FLSResults.Visible = false;

        GV_BFileLS.DataSource = null;
        GV_BFileLS.DataBind();

        pnl_BreakdownFR.Visible = false;

        GV_Settlements_USD.DataSource = null;
        GV_Settlements_USD.DataBind();

        GV_Settlements_SAR.DataSource = null;
        GV_Settlements_SAR.DataBind();

        pnlSettlements.Visible = false;

        LblQBFProcess.Text = "";
    }

    protected void btnReset_Click(object sender, EventArgs e)
    {
        ExceptionUtility.LogEvent_Errors(0, "", "btnReset_Click", "Start Reset");

        ddlCScheme.SelectedIndex = 0;
        this.dtp_scsdate.date = "";

        fu_label.Visible = false;
        fileupload.Visible = false;
        btnUpload.Visible = false;

        GV_FileLS.DataSource = null;
        GV_FileLS.DataBind();

        pnl_FLSResults.Visible = false;

        GV_BFileLS.DataSource = null;
        GV_BFileLS.DataBind();

        pnl_BreakdownFR.Visible = false;

        GV_Settlements_USD.DataSource = null;
        GV_Settlements_USD.DataBind();

        GV_Settlements_SAR.DataSource = null;
        GV_Settlements_SAR.DataBind();

        pnlSettlements.Visible = false;

        LblQBFProcess.Text = "";

        ExceptionUtility.LogEvent_Errors(0, "", "btnReset_Click", "End Reset");
    }

    public void Search()
    {
        ExceptionUtility.LogEvent_Errors(0, "", "btnSearch_Click", "Start Search");

        if (ddlCScheme.SelectedIndex != 0)
        {
            if (dtp_scsdate.date != "")
            {
                loadFLSummary();

                if (status == 1 && LblFLStatus.Text == "")
                {
                    fu_label.Visible = false;
                    fileupload.Visible = false;
                    btnUpload.Visible = false;
                }
                else if (status == 0)
                {
                    Lbl_FileName.Text = "Choose File:";
                    fu_label.Visible = true;
                    fileupload.Visible = true;
                    btnUpload.Visible = true;
                }
            }
            else
            {
                lbl_error.Text = "Please Choose the Date";
            }
        }
        else
        {
            lbl_error.Text = "Please Choose a Card Scheme";
        }

        ExceptionUtility.LogEvent_Errors(0, "", "btnSearch_Click", "End Search");
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "Start File Loading");

        if (ddlCScheme.SelectedIndex != 0)
        {
            if (dtp_scsdate.date != "")
            {
                if (FileUpload1.HasFile)
                {
                    try
                    {
                        string error = "";
                        string[] validFileTypes = { "csv", "xls" }; //, "gif", "png", "jpg", "jpeg", "doc", "xls" };
                        string ext = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
                        bool isValidFile = false;
                        int count = FileUpload1.FileName.Split('.').Length;
                        if (count > 2)
                        {
                            lbl_error.Text = "Uploaded file has more than one extension. Please check the file and upload again";
                        }
                        for (int i = 0; i < validFileTypes.Length; i++)
                        {
                            if (ext == "." + validFileTypes[i])
                            {
                                isValidFile = true;
                                break;
                            }
                        }
                        if (!isValidFile)
                        {
                            lbl_error.Text = "Invalid File. Please upload a File with extension " + string.Join(" / ", validFileTypes);
                        }
                        else
                        {
                            if (LblFLStatus.Text == "")
                            {
                                this.loadFiles();
                            }
                            else if (LblFLStatus.Text == "1")
                            {
                                this.loadBreakdownFiles();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ExceptionUtility.LogException(ex);
                        lbl_error.Text = ex.Message;
                    }
                }
                else
                {
                    lbl_error.Text = "Please Choose a File";
                }
            }
            else
            {
                lbl_error.Text = "Please Choose the Date";
            }
        }
        else
        {
            lbl_error.Text = "Please Choose a Card Scheme";
        }

        ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "End File Loading");
    }

    protected void ddlCScheme_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlCScheme.SelectedIndex != 0)
        {
            this.Clear();
            //if (ddlCScheme.SelectedValue == "VISA BILL MONTHLY")
            //{
            //    txtjobname.Text = "LOAD_VISA_BILL_M_WIN";
            //    this.Search();
            //}
            //else if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
            //{
            //    txtjobname.Text = "LOAD_VISA_BILL_Q_WIN";
            //    this.Search();
            //}
            //else if (ddlCScheme.SelectedValue == "MC BILL")
            //{
            //    txtjobname.Text = "LOAD_MC_BILL_WIN";
            //    this.Search();
            //}

            this.GetJobName();
            this.Search();
        }
    }

    public void GetJobName()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "CS_SETTL_INFO_EDITOR.List_CSBill_PrdJobName";

            oracom.Parameters.Add("P_PrdName", OracleType.VarChar).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_PrdID", OracleType.VarChar, 250).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Filename_Type", OracleType.VarChar, 250).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Prd_Job_name", OracleType.VarChar, 250).Direction = ParameterDirection.Output;

            oracom.Parameters["P_PrdName"].Value = ddlCScheme.SelectedValue;

            oracom.ExecuteNonQuery();
            if (oracom.Parameters["P_PrdID"].Value.ToString() != "")
            {
                txtproductid.Text = oracom.Parameters["P_PrdID"].Value.ToString();
            }

            if (oracom.Parameters["P_Filename_Type"].Value.ToString() != "")
            {
                txtfilentype.Text = oracom.Parameters["P_Filename_Type"].Value.ToString();
            }

            if (oracom.Parameters["P_Prd_Job_name"].Value.ToString() != "")
            {
                txtjobname.Text = oracom.Parameters["P_Prd_Job_name"].Value.ToString();
            }

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void loadFiles()
    {
        string CSName = GetCSName(ddlCScheme.SelectedValue); // != "DBJOB")
        ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "Load '" + CSName + "' File");

        string FilePath = ConfigurationSettings.AppSettings["CSLoader_Files"] + "\\" + CSName + "\\";
        FileUpload1.SaveAs(FilePath + FileUpload1.PostedFile.FileName);

        string strRespMsg = ""; //this will hold the response message details

        if (CSName.Length > 1)
        {
            try
            {
                EJCSLoader objEJCSLoader = new EJCSLoader();
                ExceptionUtility.LogEvent_Errors(0, "", "btnStart_Click", "Before dll Call");

                objEJCSLoader.Load_CS(ConfigurationSettings.AppSettings["CSLoader"], dtp_scsdate.date, CSName, out strRespMsg);

                if (strRespMsg == "Process Completed Successfully!!" || strRespMsg == "Unable to Move Files to Backup folder!")
                {
                    lbl_error.ForeColor = System.Drawing.Color.DarkGreen;
                    lbl_error.Text = "Process Completed Successfully!!";
                }
                else
                {
                    lbl_error.Text = strRespMsg;
                }

                ExceptionUtility.LogEvent_Errors(0, "", "btnStart_Click", "dll Call Response" + strRespMsg);
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(ex);
                lbl_error.Text = "Exception: Please Refer to application Log";
            }
        }
        else
        {
            lbl_error.Text = "Failed to find relevent Job";
        }

        loadFLSummary();

        ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "End '" + CSName + "' File");
    }

    private void loadBreakdownFiles()
    {
        try
        {
            string CSName = "VISA_BILL_QB";
            ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "Load '" + CSName + "' File");

            string FilePath = ConfigurationSettings.AppSettings["CSLoader_Files"] + "\\" + "VISA_BILL_Q" + "\\";
            string path = FilePath + FileUpload1.PostedFile.FileName;
            FileUpload1.SaveAs(path);

            string strRespMsg = ""; //this will hold the response message details

            if (CSName.Length > 1)
            {
                try
                {
                    EJCSLoader objEJCSLoader = new EJCSLoader();
                    ExceptionUtility.LogEvent_Errors(0, "", "btnStart_Click", "Before dll Call");

                    objEJCSLoader.Load_CS(ConfigurationSettings.AppSettings["CSLoader"], dtp_scsdate.date, CSName, out strRespMsg);

                    this.BreakdownFileResults();
                    pnl_BreakdownFR.Visible = true;

                    this.loadFLSummary();

                    if (strRespMsg == "Process Completed Successfully!!" || strRespMsg == "Unable to Move Files to Backup folder!")
                    {
                        lbl_error.ForeColor = System.Drawing.Color.DarkGreen;
                        lbl_error.Text = "Process Completed Successfully!!";
                    }
                    else
                    {
                        lbl_error.Text = strRespMsg;
                    }

                    ExceptionUtility.LogEvent_Errors(0, "", "btnStart_Click", "dll Call Response" + strRespMsg);
                }
                catch (Exception ex)
                {
                    ExceptionUtility.LogException(ex);
                    lbl_error.Text = "Exception: Please Refer to application Log";
                }
            }
            else
            {
                lbl_error.Text = "Failed to find relevent Job";
            }

            ExceptionUtility.LogEvent_Errors(0, "", "btnUpload_Click", "End '" + CSName + "' File");
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void BreakdownFileResults()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.Text;

            oracom.CommandText = "SELECT * FROM Cardschemes_Tran_VisaQBrk WHERE FILE_NUMBER = '" + qb_fileno + "' AND UPPER(CARD_TYPE_1) NOT LIKE '%CARD%'";

            OracleDataAdapter da = new OracleDataAdapter(oracom);
            DataSet ds = new DataSet();
            da.Fill(ds);

            if (ds.Tables[0].Rows.Count != 0)
            {
                pnl_BreakdownFR.Visible = true;

                GV_BFileLS.DataSource = ds;
                GV_BFileLS.DataBind();
            }
            else
            {
                pnl_BreakdownFR.Visible = false;
            }

            GV_BFileLS.Columns[1].Visible = false;
            GV_BFileLS.Columns[8].Visible = false;

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    protected void btnProcess_Click(object sender, EventArgs e)
    {
        this.Fill_VisaBilling_QB_Recs();
    }

    public string GetCSName(string job_name)
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from application_jobs where job_name ='" + txtjobname.Text + "'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0 && dtRes.Rows[0]["LOAD_TYPE"] as string == "DLL")
                return dtRes.Rows[0]["directroy"] as string;
            else return "DBJOB";
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
            return "EXCEPTION";
        }
    }

    //public string GetCardScheme(string job_name)
    //{
    //    DataTable dtRes = new DataTable();
    //    try
    //    {
    //        string DBConnection = Helper.dbConnection();

    //        OracleConnection oracon = new OracleConnection(DBConnection);

    //        OracleCommand oraCom = new OracleCommand();
    //        oraCom.CommandText = "select * from lkp_file_types where job_name ='" + txtjobname.Text + "'";
    //        oraCom.Connection = oracon;
    //        OracleDataAdapter da = new OracleDataAdapter(oraCom);

    //        da.Fill(dtRes);
    //        if (dtRes.Rows.Count > 0)
    //        {
    //            return dtRes.Rows[0]["FILE_TYPE"] as string;
    //        }
    //        else
    //        {
    //            return "";
    //        }
    //    }
    //    catch (Exception ex)
    //    {
    //        ExceptionUtility.LogException(ex);
    //        Notifications.showMessage(ex.Message, this, Notifications.errorMessage, false);
    //        return "EXCEPTION";
    //    }
    //}


    public void GetFileLookup()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from lkp_file_process_types where LFPT_FILENAME_TYPE ='" + GetCSName(txtjobname.Text) + "'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                flcode = dtRes.Rows[0]["LFPT_CODE"].ToString();
                fldesc = dtRes.Rows[0]["LFPT_CODE_DESCRIPTION"].ToString();
            }
            else
            {
                flcode = "";
                fldesc = "";
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void GetFPStatus()
    {
        this.GetFileLookup();

        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from File_Process_Log where File_Type ='" + flcode + "' AND FILE_ID = '" + dtp_scsdate.date + "' AND FILE_NAME LIKE '" + fldesc + "%'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                if (dtRes.Rows[0]["PROCESSING_STATUS"].ToString() == "002")
                {
                    btnGenerate.Visible = false;
                }
                else
                {
                    btnGenerate.Visible = true;
                }
            }
            else
            {
                btnGenerate.Visible = true;
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private bool startJob(ref string error)
    {
        string DBConnection = Helper.dbConnection();

        OracleConnection oracon = new OracleConnection(DBConnection);
        oracon.Open();

        OracleCommand oracom = new OracleCommand();
        oracom.Connection = oracon;

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "APPLICATION_MONITOR.Start_Load_Job";

        oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_job_name", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_file_date", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_file_type", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_all_files", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_lang", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_res_code", OracleType.Number).Direction = ParameterDirection.Output;
        oracom.Parameters.Add("p_res_type", OracleType.VarChar, 250).Direction = ParameterDirection.Output;
        oracom.Parameters.Add("p_res_msg", OracleType.VarChar, 250).Direction = ParameterDirection.Output;

        oracom.Parameters["p_job_name"].Value = txtjobname.Text;
        oracom.Parameters["p_file_date"].Value = dtp_scsdate.date;

        oracom.Parameters["p_file_type"].Value = OracleString.Null;
        oracom.Parameters["p_all_files"].Value = "Y";
        oracom.Parameters["p_file_type"].Value = "";
        oracom.Parameters["p_all_files"].Value = "N";

        oracom.Parameters["p_lang"].Value = "EN";
        oracom.Parameters["p_current_uid"].Value = Session["uid"].ToString();

        oracom.ExecuteNonQuery();
        oracon.Close();
        string a = oracom.Parameters["p_res_code"].Value.ToString();
        error = oracom.Parameters["p_res_msg"].Value.ToString();
        if (a == "0")
            return true;
        else
            return false;
    }

    protected void GV_FileLS_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("v"))
        {
            LblQBFProcess.Text = "";

            if (LblLSettlements.Text == "1")
            {
                pnlSettlements.Visible = true;
                pnl_BreakdownFR.Visible = false;
            }
            else
            {
                this.Response_Settlements();
                this.GetFPStatus();

                pnlSettlements.Visible = true;
                pnl_BreakdownFR.Visible = false;

                LblLSettlements.Text = "1";

                this.loadFLSummary();
            }

            //GV_FileLS_RowDataBound(null, null);

            //for (int i = 0; i < GV_FileLS.Rows.Count; i++)
            //{
            //    Button btn = (Button)GV_BFileLS.Rows[i].FindControl("btn_details");
            //    if (btn != null) btn.Enabled = true;
            //}
        }
        else if (e.CommandName.Equals("vb"))
        {
            pnl_BreakdownFR.Visible = true;
            pnlSettlements.Visible = false;
        }
        else if (e.CommandName.Equals("d"))
        {
            int index = int.Parse(e.CommandArgument.ToString());
            string fdate = Helper.getDate(GV_FileLS.DataKeys[index].Value.ToString());

            cardscheme = txtfilentype.Text;
            ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window", "window.open('SettlSearch.aspx?cs=" + cardscheme + "&fdate=" + fdate + "','Redirect')", true);
        }
    }

    protected void GV_Settlements_SAR_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("r"))
        {
            ExceptionUtility.LogEvent_Errors(0, "", "btnGenReport_Click", "Start");

            int index = int.Parse(e.CommandArgument.ToString());
            GridViewRow row = GV_Settlements_USD.Rows[index];

            string p1 = dtp_scsdate.date;
            string p2 = LblFileNumber.Text;
            string p3 = row.Cells[0].Text;

            if(ddlCScheme.SelectedValue == "VISA BILL MONTHLY")
            {
                string UrlString1 = "rpt=1013&p1=" + p1 + "&p2=" + p2 + "&p3=" + p3;
                ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                        "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);
            }
            else if(ddlCScheme.SelectedValue == "VISA BILL QUARD")
            {
                string UrlString1 = "rpt=1014&p1=" + p1 + "&p2=" + p2 + "&p3=" + p3;
                ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                        "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);
            }

            ExceptionUtility.LogEvent_Errors(0, "", "btnGenReport_Click", "End");
        }
    }

    protected void GV_FileLS_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header || e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[1].Visible = false;
            e.Row.Cells[2].Visible = false;
        }
    }

    protected void GV_FileLS_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (ddlCScheme.SelectedValue == "MC BILL")
            {
                (e.Row.FindControl("btn_summary") as Button).Visible = false;
                (e.Row.FindControl("btn_breakdown") as Button).Visible = false;
            }
            else
            {
                (e.Row.FindControl("btn_summary") as Button).Visible = true;
                (e.Row.FindControl("btn_details") as Button).Enabled = false;
                (e.Row.FindControl("btn_breakdown") as Button).Visible = false;

                if (LblLSettlements.Text == "1")
                {
                    (e.Row.FindControl("btn_details") as Button).Enabled = true;
                }
            }

            if (e.Row.Cells[5].Text == "Failed")
            {
                (e.Row.FindControl("btn_summary") as Button).Visible = false;
                (e.Row.FindControl("btn_details") as Button).Enabled = false;
                (e.Row.FindControl("btn_breakdown") as Button).Visible = false;
            }

            if (e.Row.Cells[0].Text.Contains("VISA_BILL_QB_"))
            {
                (e.Row.FindControl("btn_breakdown") as Button).Visible = true;
                (e.Row.FindControl("btn_summary") as Button).Visible = false;
                (e.Row.FindControl("btn_details") as Button).Visible = false;
            }

            if (e.Row.Cells[5].Text == "Successful")
            {
                e.Row.Cells[5].ForeColor = System.Drawing.Color.Green;
            }
            else if (e.Row.Cells[5].Text == "Status")
            {
                e.Row.Cells[5].ForeColor = System.Drawing.Color.Black;
            }
            else
            {
                e.Row.Cells[5].ForeColor = System.Drawing.Color.Red;
            }

            //if (e.Row.Cells[0].Text.Contains("VISA_BILL_M_")) 
            //{
            //    if (e.Row.Cells[5].Text == "Successful")
            //    {
            //        (e.Row.FindControl("btn_summary") as Button).Visible = true;
            //        (e.Row.FindControl("btn_details") as Button).Enabled = true;
            //        this.Response_Settlements();
            //    }
            //}

            //DataRowView row = (DataRowView)e.Row.DataItem;
            //int action = (int)row["file_number"];
            //Button btnaction = (Button)e.Row.FindControl("btn_details");
            //btnaction.Enabled = action != 0;
        }
    }

    protected void GV_FileLS_PreRender(object sender, EventArgs e)
    {
        if (GV_FileLS.Rows.Count > 0)
        {
            GV_FileLS.UseAccessibleHeader = true;
            GV_FileLS.HeaderRow.TableSection = TableRowSection.TableHeader;
        }
    }

    protected void GV_BFileLS_PreRender(object sender, EventArgs e)
    {
        if (GV_BFileLS.Rows.Count > 0)
        {
            GV_BFileLS.UseAccessibleHeader = true;
            GV_BFileLS.HeaderRow.TableSection = TableRowSection.TableHeader;
        }
    }

    protected void GV_BFileLS_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[3].Text = e.Row.Cells[3].Text.Replace(" ", "").Replace("&quot;", "");
            e.Row.Cells[4].Text = e.Row.Cells[4].Text.Replace(" ", "").Replace("&quot;", "");
            e.Row.Cells[5].Text = e.Row.Cells[5].Text.Replace(" ", "").Replace("&quot;", "");
            e.Row.Cells[6].Text = e.Row.Cells[6].Text.Replace(" ", "").Replace("&quot;", "");
            e.Row.Cells[7].Text = e.Row.Cells[7].Text.Replace(" ", "").Replace("&quot;", "");

            if (e.Row.Cells[3].Text == "&nbsp;" || e.Row.Cells[3].Text == "-")
            {
                e.Row.Cells[3].Text = "0.00";
            }

            if (e.Row.Cells[4].Text == "&nbsp;" || e.Row.Cells[4].Text == "-")
            {
                e.Row.Cells[4].Text = "0.00";
            }

            if (e.Row.Cells[5].Text == "&nbsp;" || e.Row.Cells[5].Text == "-")
            {
                e.Row.Cells[5].Text = "0.00";
            }

            if (e.Row.Cells[6].Text == "&nbsp;" || e.Row.Cells[6].Text == "-")
            {
                e.Row.Cells[6].Text = "0.00";
            }

            if (e.Row.Cells[7].Text == "&nbsp;" || e.Row.Cells[7].Text == "-")
            {
                e.Row.Cells[7].Text = "0.00";
            }
        }
    }

    protected void GV_Settlements_USD_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
            {
                if (e.Row.Cells[0].Text == "GENERAL")
                {
                    e.Row.Cells[0].Text = "VISA ACQUIRING";
                }
            }
        }
    }

    protected void GV_Settlements_SAR_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
            {
                if (e.Row.Cells[0].Text == "GENERAL")
                {
                    e.Row.Cells[0].Text = "VISA ACQUIRING";
                }
            }
        }
    }

    //protected void GV_BFileLS_RowCreated(object sender, GridViewRowEventArgs e)
    //{
    //    if (e.Row.RowType == DataControlRowType.Header || e.Row.RowType == DataControlRowType.DataRow)
    //    {
    //        e.Row.Cells[1].Visible = false;
    //    }
    //}

    protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e)
    {
        //if (ddlCScheme.SelectedValue == "VISA BILL MONTHLY")
        //{
        //    load_VISABill_M_FLS(Helper.gridSize(), int.Parse(ddlPages.SelectedValue));
        //}
        //else if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
        //{

        //}
        //else if (ddlCScheme.SelectedValue == "MC BILL")
        //{
        //    load_MCBill_FLS(Helper.gridSize(), int.Parse(ddlPages.SelectedValue));
        //}
    }

    public void FileUploadingStatus()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from channels_process_log where File_Id Like'%" + txtfilentype.Text + "_%' AND File_Date = '" + dtp_scsdate.date + "' AND PROCESSING_STATUS = 002";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                fustatus = dtRes.Rows[0]["PROCESSING_STATUS"].ToString();
            }
            else
            {
                this.VerifyFileUploadingStatus();
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void VerifyFileUploadingStatus()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from channels_process_log where File_Id Like'%" + txtfilentype.Text + "_%' AND File_Date = '" + dtp_scsdate.date + "'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                fustatus = dtRes.Rows[0]["PROCESSING_STATUS"].ToString();
            }
            else
            {
                fustatus = "";
                LblFLStatus.Text = "";
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void QB_FileUploadingStatus()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "select * from channels_process_log where File_Id Like'VISA_BILL_QB_%' AND File_Date = '" + dtp_scsdate.date + "'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                qb_fileno = dtRes.Rows[0]["FILE_NUMBER"].ToString();
                qb_fustatus = dtRes.Rows[0]["PROCESSING_STATUS"].ToString();
            }
            else
            {
                qb_fileno = "";
                qb_fustatus = "";
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void loadFLSummary()
    {
        this.FileUploadingStatus();

        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;

            if (ddlCScheme.SelectedValue == "VISA BILL MONTHLY" || ddlCScheme.SelectedValue == "VISA BILL QUARD")
            {
                oracom.CommandText = "FILE_LOADING_SUMMARY.Get_VisaBill_Files_gv";
            }
            else if (ddlCScheme.SelectedValue == "MC BILL")
            {
                oracom.CommandText = "FILE_LOADING_SUMMARY.Get_MCBill_Files_gv";
            }

            oracom.Parameters.Add("P_FileName", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_date", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_output", OracleType.Cursor).Direction = ParameterDirection.Output;

            oracom.Parameters["P_FileName"].Value = txtfilentype.Text;
            oracom.Parameters["p_date"].Value = dtp_scsdate.date;

            OracleDataAdapter da = new OracleDataAdapter(oracom);
            DataSet ds = new DataSet();
            da.Fill(ds);

            pnl_FLSResults.Visible = true;

            GV_FileLS.DataSource = ds;
            GV_FileLS.DataBind();

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (fustatus == "002")
                {
                    status = 1;

                    if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
                    {
                        LblFLStatus.Text = "1";
                        Lbl_FileName.Text = "Choose Breakdown File:";

                        fu_label.Visible = true;
                        fileupload.Visible = true;
                        btnUpload.Visible = true;

                        this.QB_FileUploadingStatus();

                        if (qb_fustatus == "002")
                        {
                            this.BreakdownFileResults();

                            fu_label.Visible = false;
                            fileupload.Visible = false;
                            btnUpload.Visible = false;
                        }
                    }
                }
                else
                {
                    status = 0;
                }

                GV_FileLS.FooterRow.Cells[1].Visible = false;
                GV_FileLS.FooterRow.Cells[2].Visible = false;
            }
            else
            {
                pnlSettlements.Visible = false;

                GV_Settlements_USD.DataSource = null;
                GV_Settlements_USD.DataBind();

                GV_Settlements_SAR.DataSource = null;
                GV_Settlements_SAR.DataBind();
            }

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void Response_Settlements()
    {
        this.GetFileNumber();
        this.RS_USD();
        this.RS_SAR();
    }

    private void GetFileNumber()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            if (ddlCScheme.SelectedValue == "VISA BILL MONTHLY")
            {
                fname = "VISA_BILL_";
                ftype = "M";

                LblNetTitle.Text = "Monthly Net Settlement SAR Amount";

                //fname = txtfilentype.Text.Remove(txtfilentype.Text.Length - 1, 1);
                //ftype = txtfilentype.Text.Split('_').Last();
            }
            else if (ddlCScheme.SelectedValue == "VISA BILL QUARD" || LblQBFProcess.Text == "1")
            {
                fname = "VISA_BILL_";
                ftype = "Q";

                LblNetTitle.Text = "Quartely Net Settlement SAR Amount";
            }

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "File_Loader_Processor.GET_CS_BILL_FileInfo";

            oracom.Parameters.Add("P_FileName", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FileType", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FILE_DATE", OracleType.VarChar).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_FILE_NUMBER", OracleType.Number).Direction = ParameterDirection.Output;

            oracom.Parameters["P_FileName"].Value = fname;
            oracom.Parameters["P_FileType"].Value = ftype;
            oracom.Parameters["P_FILE_DATE"].Value = dtp_scsdate.date;

            oracom.ExecuteNonQuery();
            if (oracom.Parameters["P_FILE_NUMBER"].Value.ToString() != "")
            {
                LblFileNumber.Text = oracom.Parameters["P_FILE_NUMBER"].Value.ToString();
            }

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void RS_USD()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.Text;

            //oracom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ SAR.CARD_TYPE AS CARD_TYPE, SAR.ATM_SAR_AMOUNT AS ATM_SAR_AMOUNT, USD.ATM_SAR_AMOUNT AS ATM_USD_AMOUNT, SAR.POS_SAR_AMOUNT AS POS_SAR_AMOUNT, USD.POS_SAR_AMOUNT AS POS_USD_AMOUNT, SAR.GENERAL_SAR_AMOUNT AS GENERAL_SAR_AMOUNT, USD.GENERAL_SAR_AMOUNT AS GENERAL_USD_AMOUNT FROM Pvt_Vc_Bill_SARTotal_V SAR   INNER JOIN Pvt_Vc_Bill_USDTotal_V USD   ON SAR.CARD_TYPE = USD.CARD_TYPE AND SAR.File_Date=USD.File_Date WHERE SAR.File_Date = '" + Helper.getDate(txtscsdate.Text) + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_SAR_AMOUNT,0) + NVL(ATM_USD_AMOUNT,0) + NVL(POS_SAR_AMOUNT,0) + NVL(POS_USD_AMOUNT,0) + NVL(GENERAL_SAR_AMOUNT,0) + NVL(GENERAL_USD_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT cte1.CARD_TYPE, cte1.ATM_SAR_AMOUNT, cte1.ATM_USD_AMOUNT, cte1.POS_SAR_AMOUNT, cte1.POS_USD_AMOUNT, cte1.GENERAL_SAR_AMOUNT, cte1.GENERAL_USD_AMOUNT, cte2.TOTAL FROM Settlemets_cte1 cte1 INNER JOIN Settlemets_cte2 cte2 ON cte1.CARD_TYPE = cte2.CARD_TYPE";

            oracom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ CARD_TYPE, ATM_USD_AMOUNT, POS_USD_AMOUNT, GENERAL_USD_AMOUNT FROM Pvt_Vc_Bill_USDTotal_V WHERE FILE_DATE = '" + dtp_scsdate.date + "' AND FILE_NUMBER = '" + LblFileNumber.Text + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_USD_AMOUNT,0) + NVL(POS_USD_AMOUNT,0) + NVL(GENERAL_USD_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT cte1.CARD_TYPE, cte1.ATM_USD_AMOUNT, cte1.POS_USD_AMOUNT, cte1.GENERAL_USD_AMOUNT, cte2.TOTAL FROM Settlemets_cte1 cte1 INNER JOIN Settlemets_cte2 cte2 ON cte1.CARD_TYPE = cte2.CARD_TYPE ORDER BY cte2.CARD_TYPE ASC";

            OracleDataAdapter da = new OracleDataAdapter(oracom);
            DataSet ds = new DataSet();
            da.Fill(ds);

            if (ds.Tables[0].Rows.Count != 0)
            {
                pnlSettlements.Visible = true;
            }
            else
            {
                pnlSettlements.Visible = false;
            }

            GV_Settlements_USD.DataSource = ds;
            GV_Settlements_USD.DataBind();

            this.GetNTotal_USD();

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void RS_SAR()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.Text;
            //oracom.CommandText = "SELECT SAR.CARD_TYPE AS CARD_TYPE, SAR.ATM_SAR_AMOUNT AS ATM_SAR_AMOUNT, USD.ATM_SAR_AMOUNT AS ATM_USD_AMOUNT, SAR.POS_SAR_AMOUNT AS POS_SAR_AMOUNT, USD.POS_SAR_AMOUNT AS POS_USD_AMOUNT, SAR.GENERAL_SAR_AMOUNT AS GENERAL_SAR_AMOUNT, USD.GENERAL_SAR_AMOUNT AS GENERAL_USD_AMOUNT FROM Pvt_Vc_Bill_SARTotal_V SAR INNER JOIN Pvt_Vc_Bill_USDTotal_V USD ON SAR.CARD_TYPE = USD.CARD_TYPE AND SAR.File_Date = USD.File_Date WHERE SAR.File_Date = '" + Helper.getDate(txtscsdate.Text) + "' GROUP BY SAR.CARD_TYPE";

            //oracom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ SAR.CARD_TYPE AS CARD_TYPE, SAR.ATM_SAR_AMOUNT AS ATM_SAR_AMOUNT, USD.ATM_SAR_AMOUNT AS ATM_USD_AMOUNT, SAR.POS_SAR_AMOUNT AS POS_SAR_AMOUNT, USD.POS_SAR_AMOUNT AS POS_USD_AMOUNT, SAR.GENERAL_SAR_AMOUNT AS GENERAL_SAR_AMOUNT, USD.GENERAL_SAR_AMOUNT AS GENERAL_USD_AMOUNT FROM Pvt_Vc_Bill_SARTotal_V SAR   INNER JOIN Pvt_Vc_Bill_USDTotal_V USD   ON SAR.CARD_TYPE = USD.CARD_TYPE AND SAR.File_Date=USD.File_Date WHERE SAR.File_Date = '" + Helper.getDate(txtscsdate.Text) + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_SAR_AMOUNT,0) + NVL(ATM_USD_AMOUNT,0) + NVL(POS_SAR_AMOUNT,0) + NVL(POS_USD_AMOUNT,0) + NVL(GENERAL_SAR_AMOUNT,0) + NVL(GENERAL_USD_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT cte1.CARD_TYPE, cte1.ATM_SAR_AMOUNT, cte1.ATM_USD_AMOUNT, cte1.POS_SAR_AMOUNT, cte1.POS_USD_AMOUNT, cte1.GENERAL_SAR_AMOUNT, cte1.GENERAL_USD_AMOUNT, cte2.TOTAL FROM Settlemets_cte1 cte1 INNER JOIN Settlemets_cte2 cte2 ON cte1.CARD_TYPE = cte2.CARD_TYPE";

            oracom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ CARD_TYPE, ATM_SAR_AMOUNT, POS_SAR_AMOUNT, GENERAL_SAR_AMOUNT FROM Pvt_Vc_Bill_SARTotal_V WHERE FILE_DATE = '" + dtp_scsdate.date + "' AND FILE_NUMBER = '" + LblFileNumber.Text + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_SAR_AMOUNT,0) + NVL(POS_SAR_AMOUNT,0) + NVL(GENERAL_SAR_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT cte1.CARD_TYPE, cte1.ATM_SAR_AMOUNT, cte1.POS_SAR_AMOUNT, cte1.GENERAL_SAR_AMOUNT, cte2.TOTAL FROM Settlemets_cte1 cte1 INNER JOIN Settlemets_cte2 cte2 ON cte1.CARD_TYPE = cte2.CARD_TYPE ORDER BY cte2.CARD_TYPE ASC";

            OracleDataAdapter da = new OracleDataAdapter(oracom);
            DataSet ds = new DataSet();
            da.Fill(ds);

            if (ds.Tables[0].Rows.Count != 0)
            {
                pnlSettlements.Visible = true;
            }
            else
            {
                pnlSettlements.Visible = false;
            }

            GV_Settlements_SAR.DataSource = ds;
            GV_Settlements_SAR.DataBind();

            this.GetNTotal_SAR();
            this.ConvertionRate();

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void GetNTotal_USD()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ CARD_TYPE, ATM_USD_AMOUNT, POS_USD_AMOUNT, GENERAL_USD_AMOUNT FROM Pvt_Vc_Bill_USDTotal_V WHERE FILE_DATE = '" + dtp_scsdate.date + "' AND FILE_NUMBER = '" + LblFileNumber.Text + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_USD_AMOUNT,0) + NVL(POS_USD_AMOUNT,0) + NVL(GENERAL_USD_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT SUM(cte2.TOTAL) AS TOTAL FROM Settlemets_cte2 cte2";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                if (dtRes.Rows[0]["TOTAL"].ToString() != "")
                {
                    lblNetTotal_USD.Text = Convert.ToDouble(dtRes.Rows[0]["TOTAL"]).ToString("N2");
                }
                else
                {
                    lblNetTotal_USD.Text = "0.00";
                }
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void GetNTotal_SAR()
    {
        DataTable dtRes = new DataTable();
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "WITH Settlemets_cte1 AS(SELECT /*+PARALLEL(32)*/ CARD_TYPE, ATM_SAR_AMOUNT, POS_SAR_AMOUNT, GENERAL_SAR_AMOUNT FROM Pvt_Vc_Bill_SARTotal_V WHERE FILE_DATE = '" + dtp_scsdate.date + "' AND FILE_NUMBER = '" + LblFileNumber.Text + "'), Settlemets_cte2 AS(SELECT CARD_TYPE, SUM(NVL(ATM_SAR_AMOUNT,0) + NVL(POS_SAR_AMOUNT,0) + NVL(GENERAL_SAR_AMOUNT,0)) AS TOTAL FROM Settlemets_cte1 GROUP BY CARD_TYPE) SELECT SUM(cte2.TOTAL) AS TOTAL FROM Settlemets_cte2 cte2";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                if (dtRes.Rows[0]["TOTAL"].ToString() != "")
                {
                    lblNetTotal_SAR.Text = Convert.ToDouble(dtRes.Rows[0]["TOTAL"]).ToString("N2");
                }
                else
                {
                    lblNetTotal_SAR.Text = "0.00";
                }
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    private void ConvertionRate()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "CS_SETTL_INTERFACE.GET_VCBILL_Amt";

            oracom.Parameters.Add("P_FILE_DATE", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FILENAME", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FILE_NUMBER", OracleType.Number).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_SAR_RATE", OracleType.Number).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_SAR_AMT", OracleType.Number).Direction = ParameterDirection.Output;

            oracom.Parameters["P_FILE_DATE"].Value = dtp_scsdate.date;
            oracom.Parameters["P_FILENAME"].Value = GetCSName(ddlCScheme.SelectedValue);
            oracom.Parameters["P_FILE_NUMBER"].Value = LblFileNumber.Text;

            oracom.ExecuteNonQuery();
            oracon.Close();

            if (oracom.Parameters["P_SAR_RATE"].Value.ToString() != "")
            {
                Lbl_SARRate.Text = oracom.Parameters["P_SAR_RATE"].Value.ToString();
            }
            else
            {
                SA_RRate.Visible = false;
            }

            if ((Convert.ToInt32(oracom.Parameters["P_SAR_RATE"].Value)) <= 0)
            {
                btnGenerate.Enabled = false;
            }

            if (oracom.Parameters["P_SAR_AMT"].Value.ToString() != "")
            {
                //Lbl_VBAmount.Text = oracom.Parameters["P_SAR_AMT"].Value.ToString();
                Lbl_VBAmount.Text = Convert.ToDouble(oracom.Parameters["P_SAR_AMT"].Value).ToString("N2");
            }
            else
            {
                VB_Amount.Visible = false;
            }
        }
        catch (Exception ex)
        {
            SA_RRate.Visible = false;
            VB_Amount.Visible = false;

            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    //protected void btn_summary_Click(object sender, EventArgs e)
    //{
    //    //for (int i = 0; i < GV_FileLS.Rows.Count; i++)
    //    //{
    //    //    HtmlInputButton btn = (HtmlInputButton)GV_FileLS.Rows[i].FindControl("btn_details");
    //    //    if (btn != null) btn.Disabled = false;
    //    //}

    //    for (int i = 0; i < GV_FileLS.Rows.Count; i++)
    //    {
    //        Button btn = (Button)GV_BFileLS.Rows[i].FindControl("btn_details");
    //        if (btn != null) btn.Enabled = true;
    //    }
    //}

    protected void btnGenerate_Click(object sender, EventArgs e)
    {
        string fdate = dtp_scsdate.date;
        string cardscheme = "";

        if (ddlCScheme.SelectedValue == "VISA BILL MONTHLY")
        {
            cardscheme = "VISA_BILL_M";
        }
        else if (ddlCScheme.SelectedValue == "VISA BILL QUARD")
        {
            cardscheme = "VISA_BILL_Q";
        }

        btnGenerate.Enabled = false;
        ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window", "window.open('SettlSearch.aspx?cs=" + cardscheme + "&fdate=" + fdate + "','Redirect')", true);
    }

    public void GetQBFileNumber()
    {
        try
        {
            string DBConnection = Helper.dbConnection();

            //if (LblQBFProcess.Text == "1")
            //{
            //    fname = "VISA_BILL_";
            //    ftype = "Q";
            //}
            //else
            //{
            //    fname = "VISA_BILL_";
            //    ftype = "QB";
            //}

            fname = "VISA_BILL_";
            ftype = "QB";

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "File_Loader_Processor.GET_CS_BILL_FileInfo";

            oracom.Parameters.Add("P_FileName", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FileType", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FILE_DATE", OracleType.VarChar).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_FILE_NUMBER", OracleType.Number).Direction = ParameterDirection.Output;

            oracom.Parameters["P_FileName"].Value = fname;
            oracom.Parameters["P_FileType"].Value = ftype;
            oracom.Parameters["P_FILE_DATE"].Value = dtp_scsdate.date;

            oracom.ExecuteNonQuery();
            if (oracom.Parameters["P_FILE_NUMBER"].Value.ToString() != "")
            {
                LblQBFNumber.Text = oracom.Parameters["P_FILE_NUMBER"].Value.ToString();
            }

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }

    public void Fill_VisaBilling_QB_Recs()
    {
        try
        {
            this.GetQBFileNumber();

            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);
            oracon.Open();

            OracleCommand oracom = new OracleCommand();
            oracom.Connection = oracon;

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "File_Loader_Processor.Fill_VisaBilling_QB_Recs";

            oracom.Parameters.Add("P_File_Number", OracleType.Number).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_Res_Code", OracleType.Number).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Res_Type", OracleType.VarChar, 250).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Res_Msg", OracleType.VarChar, 250).Direction = ParameterDirection.Output;

            oracom.Parameters["P_File_Number"].Value = LblQBFNumber.Text;

            oracom.ExecuteNonQuery();
            if (oracom.Parameters["P_Res_Msg"].Value.ToString() == "Successful")
            {
                if (LblLSettlements.Text == "1")
                {
                    pnlSettlements.Visible = true;
                    pnl_BreakdownFR.Visible = false;
                }
                else
                {
                    this.Response_Settlements();
                    this.GetFPStatus();

                    pnlSettlements.Visible = true;
                    pnl_BreakdownFR.Visible = false;

                    //btnProcess.Enabled = false;

                    LblLSettlements.Text = "1";
                }
            }
            else
            {
                pnl_BreakdownFR.Visible = true;
                pnlSettlements.Visible = false;

                lbl_error.Text = oracom.Parameters["P_Res_Msg"].Value.ToString();
            }

            oracon.Close();
        }
        catch (Exception ex)
        {
            pnl_BreakdownFR.Visible = true;
            pnlSettlements.Visible = false;

            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }
    }
}