using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
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.Diagnostics;
using System.IO;
using System.Net;
using Renci.SshNet;
using Renci.SshNet.Sftp;

public partial class VATGLSummary : System.Web.UI.Page
{
    public int page_id = 0;
    public string table_name = "";
    string url = "";
    static int intCount = 0;
    string vatFile = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        table_name = "business_rules";
        url = HttpContext.Current.Request.Url.AbsolutePath;
        string[] urlArr = url.Split('/');
        //url = urlArr[2] + "/" + urlArr[3];
        url = urlArr[1] + "/" + urlArr[2];
        page_id = loadPageIDs(url);
        if (!IsPostBack)
        {
            SecurityAgent.AuthenticateArchive();
            ExceptionUtility.LogException(0, "", "5", "Page_Load", "");

            if(Request.QueryString["VATFileDate"] != null && Request.QueryString["VATFileName"] != null)
            {
                string VATfdate = Request.QueryString["VATFileDate"];
                dtPostDateFrom.date = Helper.convertDate(VATfdate);

                loadFileNames();

                string VATfname = Request.QueryString["VATFileName"];
                ddlFiles.SelectedValue = VATfname;

                Button1_Click(new object(), new EventArgs());
            }
        }
        //if (Request.QueryString["vatfile"] != null)
        //{
        //    vatFile = Request.QueryString["vatfile"];
        //}
    }

    private int loadPageIDs(string URL)
    {
        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 = "lookup_getter.get_pages";

            oracom.Parameters.Add("p_url", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("p_pages", OracleType.Cursor).Direction = ParameterDirection.Output;


            oracom.Parameters["p_url"].Value = URL;

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

            oracon.Close();
            return int.Parse(ds.Tables[0].Rows[0]["pid"].ToString());
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex, "lookup_getter.get_pages","", "loadPageIDs", "URL: " + URL);
            //Response.Redirect("../ErrorPages/ErrorPage.aspx");
            return 0;
        }
    }
    

    private void loadFileNames()
    {
        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 = "Generate_VAT_Invoice.Get_VATInv_FileName";

            oracom.Parameters.Add("P_FileDate", OracleType.VarChar, 250).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_Resp_FileDate", OracleType.VarChar, 250).Direction = ParameterDirection.Input;

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

            oracom.Parameters["P_FileDate"].Value = dtPostDateFrom.date;
            oracom.Parameters["P_Resp_FileDate"].Value = dtPostDateTo.date;
            
            OracleDataAdapter da = new OracleDataAdapter(oracom);
            DataSet ds = new DataSet();
            da.Fill(ds);

            ddlFiles.DataSource = ds;
            ddlFiles.DataBind();
            ddlFiles.Items.Insert(0, "------ Files ------");

            oracon.Close();
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex, "Generate_VAT_Invoice.Get_GWFileName", "", "loadFileNames", "");
            //Response.Redirect("../ErrorPages/ErrorPage.aspx");
        }
    }

    

    private void loadResult( int pageSize, int pageNumber)
    {
        string parameters = "";
        try
        {
            string currentMethod = "";
            StackTrace stackTrace = new StackTrace();
            currentMethod = stackTrace.GetFrame(1).GetMethod().Name;
            string DBConnection = Helper.dbConnection();

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

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

            oracom.CommandType = CommandType.StoredProcedure;
            oracom.CommandText = "Generate_VAT_Invoice.GetVATInvFileSummary";

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

            //if (channelID.Length != 0)
            //{
            //    parameters = "p_rc_code-" + channelID;
            //    oracom.Parameters["p_rc_code"].Value = channelID;
            //}
            //else
            //    oracom.Parameters["p_rc_code"].Value = OracleNumber.Null;
            //if (eventID.Length != 0)
            //{
            //    parameters = parameters + "p_re_code-" + eventID;
            //    oracom.Parameters["p_re_code"].Value = eventID;
            //}
            //else
            //    oracom.Parameters["p_re_code"].Value = OracleNumber.Null;

            oracom.Parameters["P_FileDate"].Value = dtPostDateFrom.date;
            oracom.Parameters["P_Resp_FileDate"].Value = dtPostDateTo.date;
            if(ddlFiles.SelectedIndex >0)
                oracom.Parameters["P_FileName"].Value = ddlFiles.SelectedValue;
            else
                oracom.Parameters["P_FileName"].Value = OracleString.Null;

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

            oracon.Close();

            GridView1.DataSource = ds;
            GridView1.DataBind();
            
            if (currentMethod.Contains("Button1_Click"))
                ExceptionUtility.LogException(0, "BUSINESS_RULES_EDITOR.Get_Business_Rules","132.1", "loadResult", parameters );
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex, "BUSINESS_RULES_EDITOR.Get_Business_Rules","132.1", "loadResult", parameters );
            //Response.Redirect("../ErrorPages/ErrorPage.aspx");
        }
    }

    private void loadDetails(string fileName, string ptscode, int pageNumber)
    {
        string parameters = "";
        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 = "Generate_VAT_Invoice.Display_VAT_Txns";

            oracom.Parameters.Add("P_Post_Date", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FileName", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_Pts_Code", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_Txn_Output", OracleType.Cursor).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Resp", OracleType.VarChar,250).Direction = ParameterDirection.Output;

            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_mid", OracleType.Number).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("p_total_pages", OracleType.Number).Direction = ParameterDirection.Output;

            oracom.Parameters["P_Post_Date"].Value = dtPostDateFrom.date;
            oracom.Parameters["P_FileName"].Value = fileName;
            oracom.Parameters["P_Pts_Code"].Value = ptscode;

            oracom.Parameters["p_page_size"].Value = Helper.gridSize();
            oracom.Parameters["p_page_num"].Value = pageNumber;

            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();
            GridView2.DataSource = ds;
            GridView2.DataBind();
            if (pageNumber == 1)
            {
                ddlPages.Items.Clear();
                for (int i = 1; i <= nop; i++)
                {
                    ddlPages.Items.Add(i.ToString());
                }
            }

            ExceptionUtility.LogException(0, "Generate_VAT_Invoice.Display_VAT_Txns", "132.1", "loadResult", parameters);
        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex, "Generate_VAT_Invoice.Display_VAT_Txns", "132.1", "loadResult", parameters);
            //Response.Redirect("../ErrorPages/ErrorPage.aspx");
        }
    }
    
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (dtPostDateFrom.date.Length == 0)
        {
            lblError.Text = "Please enter dates";
        }
        else
        {
            pnlResult.Visible = true;
            HyperLink1.Visible = false;
            pnlBR.Visible = false;
            pnlDetails.Visible = false;
            ExceptionUtility.LogException(0, "", "5.132", "Button1_Click", "");//channelID: " + channelID + ", eventID: " + eventID + ", pageSize: " + pageSize + ", pageNumber: " + pageNumber);

            loadResult( Helper.gridSize(), 1);
        }
    }

    protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e)
    {
        //string channelID = "", EventID = "";
        //if (ddlChannels.SelectedIndex > 0)
        //    channelID = ddlChannels.SelectedValue;
        //if (ddlEvents.SelectedIndex > 0)
        //    EventID = ddlEvents.SelectedValue;
        //pnlBR.Visible = false;
        //ExceptionUtility.LogException(0, "", "", "ddlPages_SelectedIndexChanged", "");
        //loadResult(channelID, EventID, Helper.gridSize(), int.Parse(ddlPages.SelectedValue));
        loadDetails(ViewState["FileName"].ToString(), txtPTSCode.Text, int.Parse(ddlPages.SelectedValue));
    }

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("u"))
        {
            intCount = 0;
            ExceptionUtility.LogException(0, "", "5.8", "GridView1_RowCommand.Edit", "");
            int index = int.Parse(e.CommandArgument.ToString());
            string rowID = GridView1.DataKeys[index].Value.ToString();
            
            pnlBR.Visible = true;
            pnlBR.Enabled = true;
            ViewState["rowid"] = rowID;
            ViewState["mode"] = "u";
            

            //addrow.Visible = false;
        }
        else if (e.CommandName.Equals("d"))
        {
            ExceptionUtility.LogException(0, "", "5.7", "GridView1_RowCommand.View", "");
            int index = int.Parse(e.CommandArgument.ToString());
            string rowID = GridView1.DataKeys[index].Value.ToString();
            string FileName = GridView1.Rows[index].Cells[3].Text.ToString();
            ViewState["FileName"] = FileName; 
            
            pnlDetails.Visible = true;
        }
        
    }

    private void generateVATGL(string FileName)
    {
        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 = "Generate_VAT_Invoice.Generate_GLVAT_FILE";

            oracom.Parameters.Add("P_Current_Uid", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_GWFile_Name", OracleType.VarChar).Direction = ParameterDirection.Input;

            oracom.Parameters.Add("P_VAT_Inv_FileName", OracleType.VarChar, 250).Direction = ParameterDirection.Output;
            oracom.Parameters.Add("P_Res", OracleType.VarChar, 250).Direction = ParameterDirection.Output;

            oracom.Parameters.Add("P_VATInv_File", OracleType.Clob).Direction = ParameterDirection.Output;


            oracom.Parameters["p_current_uid"].Value = Session["uid"].ToString();
            oracom.Parameters["P_GWFile_Name"].Value = FileName;

            oracom.ExecuteNonQuery();
            oracon.Close();
            string a = oracom.Parameters["P_Res"].Value.ToString();
            if (a == "0")
            {
                OracleLob myLob = (OracleLob)oracom.Parameters["P_VATInv_File"].Value;
                string content = (String)myLob.Value;

                string filename = oracom.Parameters["P_VAT_Inv_FileName"].Value.ToString();
                StreamWriter sw = new StreamWriter(Server.MapPath("..\\Download\\" + filename));
                sw.Write(content);
                sw.Close();

                oracon.Close();
                HyperLink1.Text = filename;
                HyperLink1.NavigateUrl = "..\\Download\\" + filename;
                HyperLink1.Visible = true;
                //lblError.Text = error;

              

            }
            else
            {
                
            }


            // Label2.Text += "1 ";

        }
        catch (Exception ex)
        {
            ExceptionUtility.LogException(ex, "gl_processor.Generate_BDC_GL", "", "GenerateBDCFile", "");

            //Response.Redirect("../ErrorPages/ErrorPage.aspx");
        }
    }
  

   
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        
    }
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        
    }


    protected void btnGetFiles_Click(object sender, EventArgs e)
    {
        loadFileNames();
        ExceptionUtility.LogException(0, "", "", "btnGetFiles_Click", "");//rowID: " + rowID);
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {

            string[] validFileTypes = { "resp" };//, "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)
            {
                lblError.Text = "Uploaded file has more than one extension. Please check the file and upload again";
                return;
            }
            for (int i = 0; i < validFileTypes.Length; i++)
            {
                if (ext == "." + validFileTypes[i])
                {
                    isValidFile = true;
                    break;
                }
            }
            if (!isValidFile)
            {
                lblError.ForeColor = System.Drawing.Color.Red;
                lblError.Text = "Invalid File. Please upload a File with extension " +
                               string.Join(",", validFileTypes);

            }
            else
            {
                uploadFile();
                //Transfer to SFTP
                string respFile = ConfigurationSettings.AppSettings["VATpath"] + FileUpload1.FileName;
                string ftpIP = ConfigurationSettings.AppSettings["ftpIP"];
                string ftpPort = ConfigurationSettings.AppSettings["ftpPort"];
                string ftpUser = ConfigurationSettings.AppSettings["ftpUser"];
                string ftpPass = ConfigurationSettings.AppSettings["ftpPass"];
                string ftpDirectory = ConfigurationSettings.AppSettings["ftpDirectory"];

                using (SftpClient client = new SftpClient(ftpIP, int.Parse(ftpPort), ftpUser, ftpPass))
                {
                    client.Connect();
                    client.ChangeDirectory(ftpDirectory);
                    using (FileStream fs = new FileStream(respFile, FileMode.Open))
                    {
                        client.BufferSize = 4 * 1024;
                        client.UploadFile(fs, Path.GetFileName(respFile));
                    }
                }
            }

        }
        else
            lblError.Text = "Please choose a file";
    }

    protected void btnProcess_Click(object sender, EventArgs e)
    {
        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 = "Generate_VAT_Invoice.Read_VATInv_Resp";

            oracom.Parameters.Add("P_Current_Id", OracleType.Number).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_Resp_FileName", OracleType.VarChar).Direction = ParameterDirection.Input;
            oracom.Parameters.Add("P_FileDate", OracleType.VarChar).Direction = ParameterDirection.Input;

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



            oracom.Parameters["P_Current_Id"].Value = Session["uid"].ToString();
            oracom.Parameters["P_Resp_FileName"].Value = ViewState["FN"].ToString();
            oracom.Parameters["P_FileDate"].Value = DateTime.Now.Year.ToString().Substring(2) + DateTime.Now.Month.ToString("MM") + DateTime.Now.Day.ToString("dd");

            oracom.ExecuteNonQuery();
            oracon.Close();
            string a = oracom.Parameters["P_Res"].Value.ToString();
            if (a == "0")
            {


            }
        }
        catch (Exception ex)
        {
            lblError.Text = "Error occured. Please contact the system administrator";
        }
    }

    private void uploadFile()
    {
        string uploadPath = ConfigurationSettings.AppSettings["VATpath"];
        if (File.Exists(uploadPath + FileUpload1.FileName.Replace(" ", "")))
            lblError.Text = "file already exists";
        else
        {
            FileUpload1.SaveAs(uploadPath + FileUpload1.FileName.Replace(" ", ""));
            ViewState["FN"] = FileUpload1.FileName;
            //FileUtilityDLL.CPSFileSender cfs =
            //    new FileUtilityDLL.CPSFileSender(uploadPath, FileUpload1.FileName.Replace(" ", ""));
            //loadFiles();
            lblError.Text = "Response File Uploaded...";// and being transfered to the files list. Please wait for a few seconds and refresh the page";
            btnProcess.Enabled = true;

        }
    }

    protected void btnCancel_Click(object sender, EventArgs e)
    {
        pnlDetails.Visible = false;
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        pnlBR.Visible = false;
        btnProcess.Enabled = false;
    }

    protected void DownloadFile(object sender, EventArgs e)
    {
        string filePath = (sender as LinkButton).CommandArgument;
        Response.ContentType = ContentType;
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
        Response.WriteFile(ConfigurationSettings.AppSettings["VATpath"]+filePath);
        Response.End();
    }

    protected void btnSearchTran_Click(object sender, EventArgs e)
    {
        if (txtPTSCode.Text.Length == 0)
        {
            lblError.Text = "Please enter PTS Code";
        }
        else
        {
            lblError.Text = "";
            loadDetails(ViewState["FileName"].ToString(), txtPTSCode.Text,1);
        }
    }
}
