using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SCCInvoicing
{
    class Program
    {

        static string strSQLLiveConn = "Data Source=production-Server;" +
                                   "Initial Catalog=Production;" +
                                   "Persist Security Info=True;" +
                                   "user Id=CF;" +
                                   "Password=ENCTS";

        static string strSQLConnectionString = strSQLLiveConn;
        static string strSqlstmt = "";
        static SqlConnection myConn;
        static SqlCommand cmd;
        static SqlDataReader rs;
        static string logfile = "SCCInvoice_LOG_" + String.Format(DateTime.Now.Year.ToString(), "0000")
                    + String.Format(DateTime.Now.Month.ToString("00"), "00")
                    + String.Format(DateTime.Now.Day.ToString("00"), "00") + ".txt";

        static string strRoot = @"W:\SCC Passenger Assistant\Invoices\";
        static string strInvoiceFile = "SCCPassengerInvoice_" + String.Format(DateTime.Now.Year.ToString(), "0000")
                    + String.Format(DateTime.Now.Month.ToString("00"), "00")
                    + String.Format(DateTime.Now.Day.ToString("00"), "00") + ".csv";

        static void Main(string[] args)
        {
            addtoLogFile("*****************", logfile);
            addtoLogFile("SCC Invoicing 1.0", logfile);
            addtoLogFile("*****************", logfile);
            addtoLogFile("", logfile);
            //jobtype PassengerAssistant
            //sql orders
            getOrders();

            addtoLogFile("********************", logfile);
            addtoLogFile("End of SCC Invoicing", logfile);
            addtoLogFile("********************", logfile);

            System.Console.WriteLine("\nPress any key to exit");
            System.Console.ReadKey();
        }

        static void addtoLogFile(string textToAdd, string filename, bool addDateTime = true)
        {
            // this procedure will add the "textToAdd" to the "filename" and also write to the console
            // Warning!!! Optional parameters not supported
            System.IO.StreamWriter logWriter = new System.IO.StreamWriter(strRoot + logfile, true);
            string dateTime = "";
            if ((addDateTime == true))
            {
                dateTime = (DateTime.Now + " ");
            }
            Console.WriteLine((dateTime + ('\t' + textToAdd)));
            logWriter.WriteLine((dateTime + ('\t' + textToAdd)));
            logWriter.Close();
        }

        static void getOrders()
        {
            strSqlstmt = "SELECT * from orders with (nolock) WHERE jobtype = 'PassengerAssistant' AND DateInvoiced IS NULL ORDER BY OrderDate;";
            myConn = new SqlConnection(strSQLConnectionString);
            myConn.Open();
            cmd = new SqlCommand(strSqlstmt, myConn);
            rs = cmd.ExecuteReader();
            System.IO.StreamWriter objWriter = new System.IO.StreamWriter(strRoot + strInvoiceFile, false);
            objWriter.WriteLine("SCC Invoicing v1.0\n");
            string orderDate = "";
            while (rs.Read())
            {
                addtoLogFile("Importing batch " + rs["ID"].ToString(),logfile);
                addtoLogFile("Success! Found " + rs["OrderQTY"].ToString() + " records", logfile);
                addtoLogFile("", logfile);
                objWriter.WriteLine("\nDate Received" + "," + "BatchID" + ',' + "BatchIdentifier" + "," + "Cards Issued" + "," + "Cards Posted" + "," + "Invoiced" + "," + "Invoice No.");

                orderDate = rs["OrderDate"].ToString();
                orderDate = orderDate.Substring(6, 2) + "/" + orderDate.Substring(4, 2) + "/" + orderDate.Substring(0, 4);
                //objWriter.WriteLine(rs["ID"].ToString() + "," + rs["OrderQTY"].ToString() + "," + orderDate);
                objWriter.WriteLine(orderDate + "," + rs["ID"].ToString() + "," + rs["BatchIdentifier"].ToString() + "," + rs["OrderQTY"].ToString() + "," + "BLANK" + "," + DateTime.Now.ToShortDateString() + "");

            }
            objWriter.Close();
            objWriter.Dispose();
        }

    }
}