using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using SettlProcessor;
using System.Configuration;

public partial class Business_MonthlySettlement : System.Web.UI.Page
{
    string settl_date, file_number, settl_category, diff_sum_amt, amt_sign, evnt_code, grp_id, response, Statusresp;
    public decimal spsetsum = 0, spsetvatsum = 0, amtsum, vatsum, netsum, diffsum = 0, dbamtsum = 0, cramtsum = 0, vatdbsum = 0, vatcrsum = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            pnl_result.Visible = false;

            //if (PostBackController.Value == "On_Confirm")
            //{
            //    OnConfirm_Click(null, new EventArgs());
            //}
        }
    }
    private void setParam(int ID, string newValue)
    {
        string DBConnection = Helper.dbConnection();

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

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

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "SYSTEM_PARAM_EDITOR.edit_parameter";

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

        oracom.Parameters.Add("p_param_id", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_new_value", 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_lang"].Value = "EN";
        oracom.Parameters["p_current_uid"].Value = Session["uid"].ToString();

        oracom.Parameters["p_param_id"].Value = ID;

        oracom.Parameters["p_new_value"].Value = newValue;

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

    protected void btnSummaryRPT_Click(object sender, EventArgs e)
    {
        string p1 = dtp_filter_sttlDate.date;

        string UrlString1 = "rpt=1010&p1=" + p1;
        ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);
    }
    protected void btnClaimReport_Click(object sender, EventArgs e)
    {
        string p1 = dtp_filter_sttlDate.date;
        string p2 = DateTime.ParseExact(p1, "yyMMdd", null).AddDays(1).ToString("yyMMdd");

        setParam(310, p1);
        setParam(311, p2);

        string UrlString1 = "rpt=1004&p1=" + p1 + "&p2=" + p2;
        ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);
    }
    protected void btnGenReport_Click(object sender, EventArgs e)
    {
        ExceptionUtility.LogEvent_Errors(0, "", "btnGenReport_Click", "Start");

        string p1 = dtp_filter_sttlDate.date;

        string UrlString1 = "rpt=1005&p1=" + p1;
        ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);

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

    private string getNumber(string amount)
    {
        return amount.Replace(",", "");
    }

    public string Chk_FPStatus(string file_date)
    {
        DataTable dtRes = new DataTable();
        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 = "MADA_PDF_Loader_Processor.ChkFileProcessStatus";

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

            oracom.Parameters["P_FILE_DATE"].Value = dtp_filter_sttlDate.date;

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

            Statusresp = oracom.Parameters["P_PrcStatus"].Value.ToString();
        }
        catch (Exception ex)
        {
            lbl_error.Text = ex.Message;
            Statusresp = "EXCEPTION";
        }

        return Statusresp;
    }

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

        this.ProcessButton();
        string R_Flag_0 = Chk_FPStatus(dtp_filter_sttlDate.date);

        if (R_Flag_0 == "0")
        {
            this.loadMTicketEntry(500, 1);
        }

        string R_Flag_1 = Chk_FPStatus(dtp_filter_sttlDate.date);
        if (R_Flag_1 == "1")
        {
            this.loadMTicketEntry(500, 1);
        }

        this.loadResult(500, 1);

        pnl_result.Visible = true;
        pnlResult_TicketEntry.Visible = true;

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

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

    protected void btn_Reprocess_Click(object sender, EventArgs e)
    {
        if (dtp_filter_sttlDate.date != "")
        {
            this.Clear();
            this.ReProcess();
        }
        else lbl_error.Text = "Please Enter the Transaction Date";
    }

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

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

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

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "MADA_PDF_Loader_Processor.REPROCESS_MMPDF";

        oracom.Parameters.Add("P_FILE_DATE", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_FILE_NUMBER", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_RSP", OracleType.VarChar, 250).Direction = ParameterDirection.Output;

        oracom.Parameters["P_FILE_DATE"].Value = dtp_filter_sttlDate.date;
        oracom.Parameters["P_FILE_NUMBER"].Value = "";

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

        if (oracom.Parameters["P_RSP"].Value.ToString() != "")
        {
            lbl_FSResponse.Text = oracom.Parameters["P_RSP"].Value.ToString();
            if (lbl_FSResponse.Text == "Successful")
            {
                this.loadResult(50, 1);
                lbl_error.ForeColor = System.Drawing.Color.Green;
                lbl_error.Text = "Re-Process Completed Successfully!!";
                this.ProcessButton();
            }
            else
            {
                lbl_error.Text = lbl_FSResponse.Text;
            }
        }
    }

    public void ProcessButton()
    {
        string R_Flag = Chk_FPStatus(dtp_filter_sttlDate.date);

        if (R_Flag == "0")
        {
            btn_Generate.Visible = true;
        }
    }

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

        if (dtp_filter_sttlDate.date != "")
        {
            string R_Flag = Chk_FPStatus(dtp_filter_sttlDate.date);

            if (R_Flag == "0")
            {
                this.MM_Exchg_Limit();
                this.Get_MADA_FileInfo();
                this.loadResult(500, 1);
            }
            else if (R_Flag == "1")
            {
                this.MM_Exchg_Limit();
                this.Get_MADA_FileInfo();
                this.loadResult(500, 1);
                this.loadMTicketEntry(500, 1);
            }
            else if (R_Flag == "9")
            {
                lbl_error.Text = "PDF File not Loaded";
            }

            this.ProcessButton();
            //this.Get_MADA_FileInfo();
        }
        else
        {
            this.Clear();
            lbl_error.Text = "Please Enter the Transaction Date";
        }

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

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

    public void Clear()
    {
        GV_Settlement.DataSource = null;
        GV_Settlement.DataBind();

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

        pnl_result.Visible = false;
        pnlResult_TicketEntry.Visible = false;

        lbl_error.Text = "";
    }

    /*protected void btn_Generate_Click(object sender, EventArgs e)
    {
        ExceptionUtility.LogEvent_Errors(0, "", "btn_Generate_Click", "Start");

        this.loadMTicketEntry(500, 1);
        this.loadResult(500, 1);

        pnl_result.Visible = true;
        pnlResult_TicketEntry.Visible = true;

        ExceptionUtility.LogEvent_Errors(0, "", "btn_Generate_Click", "End");
    }*/

    protected void btn_clear_Click(object sender, EventArgs e)
    {
        //his.clearResultControls();
        dtp_filter_sttlDate.date = "";
        pnl_result.Visible = false;
        pnlResult_TicketEntry.Visible = false;
        //this.pnl_searchCriteria.Enabled = true;

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

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

    protected void btn_print_Click(object sender, EventArgs e)
    {
        ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
            "window.open('../Reports/Default.aspx?rpt=222&p1=" + this.dtp_filter_sttlDate.date + "&p2=" + Session["uid"].ToString() + "','Report')", true);
    }

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

        //if (e.Row.RowType == DataControlRowType.Footer)
        //{
        //    TableRow tr = new TableRow();

        //    TableCell cell1 = new TableCell();
        //    cell1.Text = "Total 1";

        //    TableCell cell2 = new TableCell();
        //    cell2.Text = "";

        //    //TableCell cell3 = new TableCell();
        //    //cell3.Text = "Total VAT Amount";

        //    //TableCell cell4 = new TableCell();
        //    //cell4.Text = "";

        //    //TableCell cell5 = new TableCell();
        //    //cell5.Text = "";

        //    //TableCell cell6 = new TableCell();
        //    //cell6.Text = "";

        //    //TableCell cell2 = new TableCell();
        //    //Button btn = new Button();
        //    //btn.ID = "baba";
        //    //btn.Text = "Yahoo ! i mm in footer";
        //    ////btn.Click += new EventHandler(btn_Click);
        //    //cell2.Controls.Add(btn);

        //    e.Row.Cells.Clear();
        //    e.Row.Cells.Add(cell1);
        //    e.Row.Cells.Add(cell2);
        //    //e.Row.Cells.Add(cell3);
        //    //e.Row.Cells.Add(cell4);
        //    //e.Row.Cells.Add(cell5);
        //    //e.Row.Cells.Add(cell6);


        //    TableRow tr1 = new TableRow();

        //    TableCell cell11 = new TableCell();
        //    cell11.Text = "Total 2";

        //    TableCell cell12 = new TableCell();
        //    cell12.Text = "";

        //    e.Row.Cells.Add(cell11);
        //    e.Row.Cells.Add(cell12);
        //}
    }

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

    protected void GV_Settlement_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            /*Lbl_Limit_Warning.Text = e.Row.Cells[5].Text;

            if(e.Row.Cells[5].Text == "-4861.28")
            {

            }*/

            //string item = e.Row.Cells[0].Text;
            //foreach (Button button in e.Row.Cells[2].Controls.OfType<Button>())
            //{
            //    if (button.CommandName == "diff")
            //    {
            //        button.Attributes["onclick"] = "if(!confirm('Do you want to delete " + item + "?')){ return false; };";
            //    }
            //}

            /*if (e.Row.Cells[6].Text != "Entered")
            {
                btn_Generate.Visible = false;

                //this.loadMTicketEntry(500, 1);
                //pnlResult_TicketEntry.Visible = true;
            }
            else if (e.Row.Cells[6].Text == "Entered")
            {
                btn_Generate.Visible = true;
            }*/

            if (e.Row.Cells[12].Text == "PROCESSED")
            {
                e.Row.Cells[12].ForeColor = System.Drawing.Color.Green;
            }
            else if (e.Row.Cells[12].Text == "N" || e.Row.Cells[12].Text == "NOT PROCESSED")
            {
                e.Row.Cells[12].ForeColor = System.Drawing.Color.Red;
            }

            if (e.Row.Cells[2].Text != "SPSETSUM")
            {
                spsetsum += Convert.ToDecimal(e.Row.Cells[5].Text);

                if (e.Row.Cells[6].Text != "")
                {
                    spsetvatsum += Convert.ToDecimal(e.Row.Cells[6].Text);
                }
            }
            else
            {
                if (spsetsum != 0)
                {
                    e.Row.Cells[5].Text = Convert.ToDecimal(spsetsum).ToString("n2");
                    e.Row.Cells[6].Text = Convert.ToDecimal(spsetvatsum).ToString("n2");
                }
                spsetsum = 0;
                spsetvatsum = 0;
            }

            if (e.Row.Cells[2].Text == "SPSETSUM")
            {
                if (e.Row.Cells[4].Text.Contains("SPAN Adjustment"))
                {
                    ImageButton diff_status = (ImageButton)e.Row.FindControl("Chk_Diff");
                    diff_status.Visible = false;

                    ImageButton rpt_status = (ImageButton)e.Row.FindControl("RPT_Summary");
                    rpt_status.Visible = false;
                }
                else
                {
                    ImageButton diff_status = (ImageButton)e.Row.FindControl("Chk_Diff");
                    diff_status.Visible = true;

                    ImageButton rpt_status = (ImageButton)e.Row.FindControl("RPT_Summary");
                    rpt_status.Visible = false;
                }

                amtsum += Convert.ToDecimal(e.Row.Cells[5].Text.Replace("-",""));

                if (e.Row.Cells[6].Text != "")
                {
                    vatsum += Convert.ToDecimal(e.Row.Cells[6].Text.Replace("-", ""));
                }

                netsum += Convert.ToDecimal(e.Row.Cells[7].Text);

                if (Convert.ToDecimal(e.Row.Cells[8].Text) >= Convert.ToInt32(Lbl_Exchg_Limit.Text))
                {
                    e.Row.Cells[8].ForeColor = System.Drawing.Color.Red;

                    e.Row.Cells[2].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[3].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[4].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[5].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[6].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[7].BackColor = System.Drawing.Color.DarkTurquoise;
                    e.Row.Cells[8].BackColor = System.Drawing.Color.DarkTurquoise;
                    btn_SubmitForApproval.Enabled = false;
                }
                else
                {
                    if (Convert.ToDecimal(e.Row.Cells[8].Text) <= -1)
                    {
                        e.Row.Cells[8].ForeColor = System.Drawing.Color.Red;
                    }

                    e.Row.Cells[2].BackColor = System.Drawing.Color.SkyBlue;
                    e.Row.Cells[5].BackColor = System.Drawing.Color.SkyBlue;
                    e.Row.Cells[6].BackColor = System.Drawing.Color.SkyBlue;
                    e.Row.Cells[7].BackColor = System.Drawing.Color.SkyBlue;
                    e.Row.Cells[8].BackColor = System.Drawing.Color.SkyBlue;
                }

                if (e.Row.Cells[7].Text != "0")
                {
                    e.Row.Cells[8].Text = Convert.ToDecimal(Convert.ToDecimal(e.Row.Cells[7].Text) - Math.Abs(Convert.ToDecimal(e.Row.Cells[5].Text))).ToString("n2");

                    if (Convert.ToDecimal(e.Row.Cells[8].Text) != 0)
                    {
                        string response = GetAdjDifference(e.Row.Cells[4].Text, e.Row.Cells[8].Text.Replace("-",""));
                        if (response == "true")
                        {
                            e.Row.Cells[2].BackColor = System.Drawing.Color.SandyBrown;
                            e.Row.Cells[5].BackColor = System.Drawing.Color.SandyBrown;
                            e.Row.Cells[6].BackColor = System.Drawing.Color.SandyBrown;
                            e.Row.Cells[7].BackColor = System.Drawing.Color.SandyBrown;
                            e.Row.Cells[8].BackColor = System.Drawing.Color.SandyBrown;

                            ImageButton diff_status = (ImageButton)e.Row.FindControl("Chk_Diff");
                            diff_status.Enabled = false;
                        }
                    }
                }
            }
            else
            {
                ImageButton diff_status = (ImageButton)e.Row.FindControl("Chk_Diff");
                diff_status.Visible = false;

                ImageButton rpt_status = (ImageButton)e.Row.FindControl("RPT_Summary");
                rpt_status.Visible = false;
            }

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

            if (e.Row.Cells[5].Text == "Amount")
            {
                e.Row.Cells[5].ForeColor = System.Drawing.Color.Black;
            }

            diffsum += Convert.ToDecimal(e.Row.Cells[8].Text);
        }
    }

    protected void GV_Settlement_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("r"))
        {
            int index = int.Parse(e.CommandArgument.ToString());
            GridViewRow row = GV_Settlement.Rows[index];

            string filename = row.Cells[2].Text;
            ExceptionUtility.LogEvent_Errors(0, "", "MADA " + filename + " Summary Report", "Start");

            string p1 = row.Cells[0].Text;
            string p2 = row.Cells[1].Text;
            string p3 = filename;
            string p4 = row.Cells[5].Text;

            string UrlString1 = "rpt=1012&p1=" + p1 + "&p2=" + p2 + "&p3=" + p3 + "&p4=" + p4;
            ScriptManager.RegisterClientScriptBlock(this, typeof(string), "new window",
                    "window.open('../Reports/Default.aspx?" + UrlString1 + "','Report')", true);

            ExceptionUtility.LogEvent_Errors(0, "", "MADA " + filename + " Summary Report", "End");
        }
        else if(e.CommandName.Equals("diff"))
        {
            int index = int.Parse(e.CommandArgument.ToString());
            GridViewRow row = GV_Settlement.Rows[index];

            settl_date = row.Cells[0].Text;
            file_number = row.Cells[1].Text;
            settl_category = row.Cells[4].Text.Split('-')[1].TrimStart();
            diff_sum_amt = row.Cells[8].Text;

            if(row.Cells[7].Text.Contains("-"))
            {
                amt_sign = "DR";
            }
            else
            {
                amt_sign = "CR";
            }

            evnt_code = row.Cells[9].Text;
            grp_id = row.Cells[10].Text;

            //string script = "window.onload = function() { DiffConfirmation('" + settl_category + "'); };";
            //ClientScript.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(), "DiffConfirmation('" + settl_category + "');", script, true);

            //ScriptManager.RegisterStartupScript(this, this.GetType(), Guid.NewGuid().ToString(),
            //          "DiffConfirmation('" + settl_category + "','" + diff_sum_amt + "');", true);

            //ScriptManager.RegisterStartupScript(this.Page, Page.GetType(), Guid.NewGuid().ToString(),
            //          "DiffConfirm('" + settl_category + "','" + diff_sum_amt + "');", true);

            this.Process_MADA_Diff_Summary();

            //Page page = HttpContext.Current.CurrentHandler as Page;
            //string script = "DiffConfirmation('" + settl_category + "','" + diff_sum_amt + "')";
            //ScriptManager.RegisterStartupScript(page, page.GetType(), "its working", script, true);

        }
    }

    public void OnConfirm_Click(object sender, EventArgs e)
    {
        //string confirmValue = Request.Form["confirm_value"];
        //if (confirmValue == "Yes")
        //{
        //    //Your logic for OK button
        //    Response.Redirect("http://www.google.com");
        //}
        //else
        //{
        //    //Your logic for cancel button
        //}

        lbl_error.Text = "1";
        Response.Redirect("http://www.google.com");
    }

    public string GetAdjDifference(string settl_category, string diff_summary)
    {
        if (settl_category != "" && Convert.ToDecimal(diff_summary) != 0)
        {
            DataTable dtRes = new DataTable();

            string DBConnection = Helper.dbConnection();

            OracleConnection oracon = new OracleConnection(DBConnection);

            OracleCommand oraCom = new OracleCommand();
            oraCom.CommandText = "SELECT * FROM SPAN_SETTLEMENT_SUMMARY WHERE SETTL_DATE ='" + dtp_filter_sttlDate.date + "' AND AMOUNT = '" + diff_summary.Replace(",","") + "' AND NARRATIVE LIKE 'Adj Difference In " + settl_category.Split('-')[1].TrimStart() + "%'";
            oraCom.Connection = oracon;
            OracleDataAdapter da = new OracleDataAdapter(oraCom);

            da.Fill(dtRes);
            if (dtRes.Rows.Count > 0)
            {
                response = "true";
            }
            else
            {
                response = "false";
            }
        }
        return response;
    }

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

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

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

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "MADA_PDF_Loader_Processor.add_exchangediff_entry";

        oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Settl_Date", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_File_Number", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Evnt_code", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Prd_grp_id", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Amt", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Amt_Sign", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("P_Settle_Category", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_res_code", OracleType.VarChar, 250).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_current_uid"].Value = Session["uid"].ToString();
        oracom.Parameters["P_Settl_Date"].Value = settl_date;
        oracom.Parameters["P_File_Number"].Value = file_number;
        oracom.Parameters["P_Evnt_code"].Value = evnt_code;
        oracom.Parameters["P_Prd_grp_id"].Value = grp_id;
        oracom.Parameters["P_Amt"].Value = diff_sum_amt.Replace(",","");
        oracom.Parameters["P_Amt_Sign"].Value = amt_sign;
        oracom.Parameters["P_Settle_Category"].Value = settl_category;    

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

        if (oracom.Parameters["p_res_msg"].Value.ToString() != "")
        {
            response = oracom.Parameters["p_res_msg"].Value.ToString();
            if (response == "Successful")
            {
                this.loadResult(50, 1);
                pnl_result.Visible = true;

                lbl_error.Text = "Process Completed Successfully!!";
            }
            else
            {
                lbl_error.Text = response;
            }
        }
    }

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

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

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

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "MADA_PDF_Loader_Processor.GET_MM_FileInfo";

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

        oracom.Parameters["P_FILE_DATE"].Value = dtp_filter_sttlDate.date;

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

        if (oracom.Parameters["P_RSP"].Value.ToString() != "")
        {
            lbl_FSResponse.Text = oracom.Parameters["P_RSP"].Value.ToString();
            /*if (lbl_FSResponse.Text == "Successful")
            {
                this.loadResult(50, 1);
            }
            else
            {
                lbl_error.Text = lbl_FSResponse.Text;
            }*/
        }
    }

    protected void ButtonGenerateGL_Click(object sender, EventArgs e)
    {
        string strRespMsg = "";
        string p1 = dtp_filter_sttlDate.date;
        //string logFile = ConfigurationManager.AppSettings["ErrorLog"].ToString();
        string p2 = DateTime.ParseExact(p1, "yyMMdd", null).ToString("yyMMdd");
        SettlFileGenerator sl = new SettlFileGenerator();
        sl.Gen_Settl(ConfigurationManager.AppSettings["Gen_settle_location"].ToString(), "MADA", p2, out strRespMsg);
    }

    private bool Delete_CS_Entry(string event_code, int File_no, 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 = "VISA_SETTL_INFO_EDITOR.Delete_SettlEntry";

        oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_File_no", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_event_code", 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_current_uid"].Value = Session["uid"].ToString();
        oracom.Parameters["p_File_no"].Value = Convert.ToDecimal(File_no);
        oracom.Parameters["p_event_code"].Value = event_code;

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

    public void MM_Exchg_Limit()
    {
        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 Lookup_Getter.get_sys_param(320) AS MM_PDF_EXCHG_LIMIT FROM DUAL";

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

            if (ds.Tables[0].Rows.Count != 0)
            {
                Lbl_Exchg_Limit.Text = ds.Tables[0].Rows[0]["MM_PDF_EXCHG_LIMIT"].ToString();
                Lbl_Limit_Warning.Text = "Exchange Difference Limit : " + Lbl_Exchg_Limit.Text;
            }
            else
            {
                Lbl_Exchg_Limit.Text = "0";
                Lbl_Limit_Warning.Text = "";
            }

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

    private void loadResult(int pageSize, int pageNumber)
    {
        ExceptionUtility.LogEvent_Errors(0, "", "loadResult", "Start");

        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 = "MADA_PDF_Loader_Processor.Get_MM_Settl_Summary";

            oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_date", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_page_size", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_page_num", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_lang", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_Evnt_Code", 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.Add("p_output", OracleType.Cursor).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("p_total_pages", OracleType.Number).Direction = ParameterDirection.Output;

            oracom.Parameters["p_date"].Value = dtp_filter_sttlDate.date;

            oracom.Parameters["p_page_size"].Value = pageSize;
            oracom.Parameters["p_page_num"].Value = pageNumber;
            oracom.Parameters["p_lang"].Value = "EN";
            oracom.Parameters["p_current_uid"].Value = Session["uid"].ToString();
            oracom.Parameters["p_Evnt_Code"].Value = OracleString.Null;

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

            int nop = int.Parse(oracom.Parameters["p_total_pages"].Value.ToString());
            oracon.Close();

            if (lbl_FSResponse.Text == "Successful")
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    GV_Settlement.DataSource = ds;
                    GV_Settlement.DataBind();
                    pnl_result.Visible = true;

                    //this.loadMTicketEntry(500, 1);
                    pnlResult_TicketEntry.Visible = true;

                    GV_Settlement.FooterRow.BackColor = System.Drawing.Color.DarkGray;
                    GV_Settlement.FooterRow.ForeColor = System.Drawing.Color.Black;

                    GV_Settlement.FooterRow.Cells[0].Visible = false;
                    GV_Settlement.FooterRow.Cells[1].Visible = false;
                    GV_Settlement.FooterRow.Cells[9].Visible = false;
                    GV_Settlement.FooterRow.Cells[10].Visible = false;

                    GV_Settlement.FooterRow.Cells[4].Text = "Total";
                    GV_Settlement.FooterRow.Cells[4].Font.Bold = true;

                    GV_Settlement.FooterRow.Cells[5].HorizontalAlign = HorizontalAlign.Right;
                    GV_Settlement.FooterRow.Cells[5].Text = amtsum.ToString("N2");

                    GV_Settlement.FooterRow.Cells[6].HorizontalAlign = HorizontalAlign.Right;
                    GV_Settlement.FooterRow.Cells[6].Text = vatsum.ToString("N2");

                    GV_Settlement.FooterRow.Cells[7].HorizontalAlign = HorizontalAlign.Right;
                    GV_Settlement.FooterRow.Cells[7].Text = netsum.ToString("N2");

                    GV_Settlement.FooterRow.Cells[8].HorizontalAlign = HorizontalAlign.Right;
                    GV_Settlement.FooterRow.Cells[8].Text = Convert.ToDecimal(Convert.ToDecimal(netsum) - Convert.ToDecimal(amtsum)).ToString("N2");

                    if (GV_Settlement.FooterRow.Cells[5].Text.Contains("-"))
                    {
                        GV_Settlement.FooterRow.Cells[5].ForeColor = System.Drawing.Color.DarkRed;
                    }
                    else GV_Settlement.FooterRow.Cells[5].ForeColor = System.Drawing.Color.DarkGreen;

                    if (GV_Settlement.FooterRow.Cells[7].Text.Contains("-"))
                    {
                        GV_Settlement.FooterRow.Cells[7].ForeColor = System.Drawing.Color.DarkRed;
                    }
                    else GV_Settlement.FooterRow.Cells[7].ForeColor = System.Drawing.Color.DarkGreen;

                    if (GV_Settlement.FooterRow.Cells[8].Text.Contains("-"))
                    {
                        GV_Settlement.FooterRow.Cells[8].ForeColor = System.Drawing.Color.DarkRed;
                    }
                    else GV_Settlement.FooterRow.Cells[8].ForeColor = System.Drawing.Color.DarkGreen;
                }
                else
                {
                    lbl_error.Text = "No Data Found";
                }
            }
            else
            {
                GV_Settlement.DataSource = null;
                GV_Settlement.DataBind();

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

                pnl_result.Visible = false;
                pnlResult_TicketEntry.Visible = false;

                lbl_error.Text = "PDF File not Loaded";
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }

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

    private void loadMTicketEntry(int pageSize, int pageNumber)
    {
        //ExceptionUtility.LogEvent_Errors(0, "", "loadMTicketEntry", "Start");

        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 = "MADA_PDF_Loader_Processor.GET_MM_SETTL_ENTRY";

            oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_date", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FLAG", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_page_size", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_page_num", OracleType.Number).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.Add("p_output", OracleType.Cursor).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("p_total_pages", OracleType.Number).Direction = ParameterDirection.Output;

            //    date = date.Remove(6, 1);
            oracom.Parameters["p_date"].Value = dtp_filter_sttlDate.date;
            oracom.Parameters["P_FLAG"].Value = Convert.ToInt32(Chk_FPStatus(dtp_filter_sttlDate.date));

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

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

            int nop = int.Parse(oracom.Parameters["p_total_pages"].Value.ToString());
            oracon.Close();

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

            //Calculate Sum and display in Footer Row
            Session["File_Number"] = "";
            Session["CS_GL_CODE"] = "";

            //lbl_CR_DR_Diff.Text = "";
            if (ds.Tables[0].Rows.Count > 0)
            {
                //decimal drTotal = ds.Tables[0].AsEnumerable().Sum(row => row.Field<decimal>("DbAmt"));
                //decimal crTotal = ds.Tables[0].AsEnumerable().Sum(row => row.Field<decimal>("CRAmt"));

                GV_TicketEntry.FooterRow.BackColor = System.Drawing.Color.DarkGray;
                GV_TicketEntry.FooterRow.ForeColor = System.Drawing.Color.Black;

                GV_TicketEntry.FooterRow.Cells[0].Text = "Total Amount" + "</br>" + "Total VAT Amount";
                GV_TicketEntry.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Right;

                double vatdbdiff = Convert.ToDouble(vatcrsum) - Convert.ToDouble(vatdbsum);
                double vatdbsumamount = Convert.ToDouble(vatdbsum) + Convert.ToDouble(vatdbdiff);

                GV_TicketEntry.FooterRow.Cells[3].Text = dbamtsum.ToString("N2") + "</br>" + vatdbsumamount.ToString("N2");
                GV_TicketEntry.FooterRow.Cells[4].HorizontalAlign = HorizontalAlign.Right;
                GV_TicketEntry.FooterRow.Cells[4].Text = cramtsum.ToString("N2") + "</br>" + vatcrsum.ToString("N2");
                GV_TicketEntry.FooterRow.Cells[5].Text = "Difference(CR-DB)";
                GV_TicketEntry.FooterRow.Cells[6].Text = (cramtsum - dbamtsum).ToString("N2");

                if(GV_TicketEntry.FooterRow.Cells[6].Text.Contains("-"))
                {
                    GV_TicketEntry.FooterRow.Cells[6].ForeColor = System.Drawing.Color.DarkRed;
                }
                else
                {
                    GV_TicketEntry.FooterRow.Cells[6].ForeColor = System.Drawing.Color.DarkGreen;
                }

                GV_TicketEntry.FooterRow.Cells[7].Visible = false;
                GV_TicketEntry.FooterRow.Cells[8].Visible = false;

                //lbl_VAT_DBAmt.Text = vatdbsum.ToString("N2");
                //lbl_VAT_CDAmt.Text = vatcrsum.ToString("N2");
                //lbl_CR_DR_Diff.Text = (cramtsum - dbamtsum).ToString("N2");

                lbl_File_Number.Text = ds.Tables[0].Rows[0]["File_Number"].ToString();
                lbl_Status.Text = ds.Tables[0].Rows[0]["CS_GL_CODE"].ToString();
            }
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex);
            lbl_error.Text = ex.Message;
        }

        //ExceptionUtility.LogEvent_Errors(0, "", "loadMTicketEntry", "End");
    }

    protected void GV_TicketEntry_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (e.Row.Cells[0].Text.Contains("VAT"))
            {
                vatdbsum += Convert.ToDecimal(e.Row.Cells[3].Text);
                vatcrsum += Convert.ToDecimal(e.Row.Cells[4].Text);
            }
            else if (!e.Row.Cells[0].Text.Contains("FEES SUM"))
            {
                dbamtsum += Convert.ToDecimal(e.Row.Cells[3].Text);
                cramtsum += Convert.ToDecimal(e.Row.Cells[4].Text);
            }
            
            //if(e.Row.Cells[0].Text.Contains("CLEARG-EBD"))
            //{
                //dbamtsum += Convert.ToDecimal(e.Row.Cells[3].Text);
                //vatdbsum += Convert.ToDecimal(e.Row.Cells[3].Text);
            //}
        }

        //foreach (GridViewRow gvr in GV_TicketEntry.Rows)
        //{
        //    if (gvr.RowType == DataControlRowType.DataRow)
        //    {
        //        //if (e.Row.Cells[0].Text.Contains("VAT"))
        //        //{
        //        //    vatdbsum += Convert.ToDecimal(gvr.Cells[3].Text);
        //        //    vatcrsum += Convert.ToDecimal(gvr.Cells[4].Text);
        //        //}
        //        //else if (!e.Row.Cells[0].Text.Contains("FEES SUM"))
        //        //{
        //        //    dbamtsum += Convert.ToDecimal(gvr.Cells[3].Text);
        //        //    cramtsum += Convert.ToDecimal(gvr.Cells[4].Text);
        //        //}

        //        vatdbsum += Convert.ToDecimal(gvr.Cells[3].Text);
        //        //cramtsum += Convert.ToDecimal(gvr.Cells[4].Text);
        //    }
        //}
    }

    protected void GV_TicketEntry_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("d"))
        {
            int index = int.Parse(e.CommandArgument.ToString());
            string rowID = GV_TicketEntry.DataKeys[index].Value.ToString();

            string evnt_code = GV_TicketEntry.Rows[index].Cells[7].Text.ToString();
            int file_no = Convert.ToInt16(GV_TicketEntry.Rows[index].Cells[8].Text);

            string error = ""; ;

            string status_flag = GV_TicketEntry.Rows[index].Cells[9].Text.ToString();
            string editable_flag = GV_TicketEntry.Rows[index].Cells[10].Text.ToString();

            if (status_flag.Substring(0, 6).ToLower().Equals("cancel") || status_flag.ToLower().Equals("entered"))
            {
                if (editable_flag.ToUpper().Equals("Y"))
                {
                    bool saved = Delete_CS_Entry(evnt_code, file_no, ref error);

                    if (saved)
                    {
                        loadMTicketEntry(500, 1);
                        lbl_error.ForeColor = System.Drawing.Color.DarkGreen;
                        lbl_error.Text = "Successfuly deleted Entry";
                    }
                    else
                    {
                        lbl_error.Text = error;
                    }
                }
                else lbl_error.Text = "Deletion Failed - Auto Entry cannot be  deleted";
            }
            else lbl_error.Text = "Deletion Failed - Entry can be deleted only when status is Entered/Caneled";
        }
    }

    private bool Approval_Reject_GenerateGl(string settleDate, int file_number, string status)
    {
        string DBConnection = Helper.dbConnection();

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

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

        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "SAMA_SETTL_OPERATIONS.SUBMITFORAPRVL";

        oracom.Parameters.Add("p_current_uid", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_date", OracleType.VarChar).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_status", OracleType.VarChar).Direction = ParameterDirection.Input;
        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_status"].Value = status;

        oracom.Parameters["p_file_number"].Value = file_number;
        oracom.Parameters["p_date"].Value = settleDate;
        oracom.Parameters["p_current_uid"].Value = Session["uid"].ToString();

        OracleDataAdapter da = new OracleDataAdapter(oracom);

        oracom.ExecuteNonQuery();
        oracon.Close();
        string a = oracom.Parameters["p_res_code"].Value.ToString();

        if (a == "0")
        {
            lbl_error.ForeColor = System.Drawing.Color.DarkGreen;
            lbl_error.Text = oracom.Parameters["p_res_msg"].Value.ToString();
            return true;
        }
        else
        {
            lbl_error.Text = oracom.Parameters["p_res_msg"].Value.ToString();
            return false;
        }
    }

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

        int File_Number = Convert.ToInt32(lbl_File_Number.Text);

        if (Approval_Reject_GenerateGl(dtp_filter_sttlDate.date, File_Number, "001"))
        {
            loadMTicketEntry(500, 1);
            loadResult(500, 1);
        }

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

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

        int File_Number = Convert.ToInt32(lbl_File_Number.Text);

        if (Approval_Reject_GenerateGl(dtp_filter_sttlDate.date, File_Number, "008"))
        {
            loadMTicketEntry(500, 1);
            loadResult(500, 1);
        }

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

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

        int File_Number = Convert.ToInt32(lbl_File_Number.Text);

        if (Approval_Reject_GenerateGl(dtp_filter_sttlDate.date, File_Number, "002"))
        {
            loadMTicketEntry(500, 1);
            loadResult(500, 1);
        }

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