using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;

public partial class POS_SpanExtractor : System.Web.UI.Page
{

   
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //lnkCPS.Visible = false;
            LoadCSV_Files();
        }
    }




    private void LoadCSV_Files()
    {
        ddlFiles2.Items.Clear();
        DirectoryInfo dire = new DirectoryInfo(ConfigurationSettings.AppSettings["CSV_F"].ToString());
        FileInfo[] finf = dire.GetFiles();

        int index = 0;
        foreach (FileInfo fi in finf)
        {
            if (fi.Extension == ".csv")
            {
                ddlFiles2.Items.Add(fi.Name);
                ddlFiles2.Items[index].Value = fi.Name;
                index++;
            }
        }

        if (ddlFiles2.Items.Count > 0)
            lnkCSV_Download.NavigateUrl = ConfigurationSettings.AppSettings["CSV_F_IIS_V"].ToString() + ddlFiles2.Items[0].Value;
    }



    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (dtDate.date == "")
            lblError.Text = "Please Select a Date";
        else
        {
            CreateCSV_File();
        }
    }

    private void CreateCSV_File()
    {
        /*string csvDirec = ConfigurationSettings.AppSettings["CSVEXE"];*/
       // System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo();
        /*System.Diagnostics.Process.Start(csvDirec, ddlFileType.SelectedValue + " " + dtDate.date);*/
        string DBConnection = Helper.dbConnection();
        OracleConnection oracon = new OracleConnection(DBConnection);
        OracleCommand oracom = new OracleCommand();
        oracom.Connection = oracon;

        oracon.Open();
        oracom.CommandType = CommandType.StoredProcedure;
        oracom.CommandText = "SPAN_EXTRACTOR.generateCSV_File";

        oracom.Parameters.Add("p_date", OracleType.Number).Direction = ParameterDirection.Input;
        oracom.Parameters.Add("p_financial_flag", 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;

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


        if (ddlFin.SelectedValue == "2")
            oracom.Parameters["p_financial_flag"].Value = OracleString.Null;
        else
            oracom.Parameters["p_financial_flag"].Value = ddlFin.SelectedValue;

        oracom.Parameters["p_fileName"].Value = ddlFileType.SelectedValue;

        OracleDataReader dtReader = oracom.ExecuteReader();
        string FileName =
        @"C:\Reconss\CSV_FILES\SPAN_" + ddlFileType.SelectedValue + "_" + dtDate.date + "_" + ddlFin.SelectedItem.Text + ".csv";

        StreamWriter sw = new StreamWriter(FileName);

        sw.WriteLine(
"SETTL_DATE,TRAN_DATE,TRAN_TIME,CARD_NUMBER,TERM_ID,TRAN_AMOUNT,B24_Amount, Retailer_id,"+
        "TRAN_SOURCE,TRAN_DEST,MSG_TYPE,MATCH_RESULT,FINANCIAL_FLAG,EVENT_NAME,B24_financial,CLAIM_ID");

        while (dtReader.Read())
        {
            string data = dtReader.GetOracleValue(0).ToString() + ",";//SETTL_DATE
            data += dtReader.GetOracleValue(1).ToString() + ",";//TRAN_DATE
            data += dtReader.GetOracleValue(2).ToString() + ",";//TRAN_TIME
            data += dtReader.GetOracleValue(3).ToString() + ",";//CARD_NUMBER  string.Format("{0:######################}", ) 
            data += dtReader.GetOracleValue(4).ToString() + ",";//TERM_ID
            data += dtReader.GetOracleValue(5).ToString().Replace(",", "") + ",";//TRAN_AMOUNT
            data += dtReader.GetOracleValue(6).ToString().Replace(",", "") + ",";//B24_Amount
            data += dtReader.GetOracleValue(7).ToString() + ",";//Retailer_id
            data += dtReader.GetOracleValue(8).ToString() + ",";//TRAN_SOURCE            
            data += dtReader.GetOracleValue(9).ToString() + ",";//TRAN_DEST
            data += dtReader.GetOracleValue(10).ToString() + ",";//MSG_TYPE
            data += dtReader.GetOracleValue(11).ToString() + ",";//MATCH_RESULT
            data += dtReader.GetOracleValue(12).ToString() + ",";//FINANCIAL_FLAG
            data += dtReader.GetOracleValue(13).ToString() + ",";//EVENT_NAME
            data += dtReader.GetOracleValue(14).ToString() + ",";//B24_financial
            data += dtReader.GetOracleValue(15).ToString();// +",";//CLAIM_ID

           /* data += dtReader.GetOracleValue(16).ToString() + ",";
            data += dtReader.GetOracleValue(17).ToString() + ",";
            data += dtReader.GetOracleValue(18).ToString() + ",";
            data += dtReader.GetOracleValue(19).ToString() + ",";
            data += dtReader.GetOracleValue(20).ToString();*/
            sw.WriteLine(data);
        }
        oracon.Close();
        lblStatus.Text = "File is Generated .. ";
        LoadCSV_Files();
    }


    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }
  
    protected void ddlFiles2_SelectedIndexChanged(object sender, EventArgs e)
    {
        lnkCSV_Download.NavigateUrl = ConfigurationSettings.AppSettings["CSV_F_IIS_V"].ToString() + ddlFiles2.SelectedValue;
    }
}