using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using LFSO83Lib;
using DocumentProcessor83;
using System.Data.SqlClient;

namespace BT_Statement_Merge
{
    public class Laserfiche
    {
        public LFApplication lfapp;
        public LFServer lfserv;
        public LFDatabase lfdb;
        public LFConnection conn;

        public string lfServer = ConfigurationManager.AppSettings["lfServer"];
        public string lfUser = ConfigurationManager.AppSettings["lfUser"];
        public string lfPass = ConfigurationManager.AppSettings["lfPass"];
        public string lfRepo = ConfigurationManager.AppSettings["lfRepo"];
        public string lfFolder = ConfigurationManager.AppSettings["lfFolder"];

        public Laserfiche()
        {
            lfapp = new LFApplication();
            lfserv = (LFServer)lfapp.GetServerByName(lfServer);
            lfdb = (LFDatabase)lfserv.GetDatabaseByName(lfRepo);
            conn = new LFConnection();
            conn.UserName = lfUser;
            conn.Password = lfPass;
            conn.Create(lfdb);
        }

        public void LaserficheActivate()
        {
            try
            {
                conn.Connect(lfdb);
            }
            catch (Exception ex)
            {
                Reporting.Error(ex.Message, ex.StackTrace);
            }
        }

        public void LaserficheDeactivate()
        {
            try
            {
                conn.Terminate();
            }
            catch {}
        }

        public ArrayList GetStatements()
        {
            ArrayList statements = new ArrayList();
            string mergeStatus = ConfigurationManager.AppSettings["Merge Status"];

            ILFFolder ParentFolder = (ILFFolder)lfdb.GetEntryByPath(lfFolder);
            ILFCollection allDocs = (ILFCollection)ParentFolder.GetChildren();

            foreach (ILFEntry entry in allDocs)
            {
                if (entry.EntryType != Entry_Type.ENTRY_TYPE_DOCUMENT)
                    continue;
                LFDocument doc = entry as LFDocument;
                LFFieldData fields = doc.FieldData;
                if (((string)fields.Field[ConfigurationManager.AppSettings["Document Type Field"]]).Equals(ConfigurationManager.AppSettings["Statement DocType"]) &&
                    ((string)fields.Field[ConfigurationManager.AppSettings["Status Field"]]).Equals(mergeStatus))
                {
                    statements.Add(entry);
                }
            }

            Console.Write(statements.Count + " statements found in Waiting state\n");

            return statements;
        }

        public ArrayList GetStaticDocumentIDs()
        {
            string staticDocFolder = ConfigurationManager.AppSettings["Static Page Folder"];
            ArrayList documents = new ArrayList();

            ILFFolder ParentFolder = (ILFFolder)lfdb.GetEntryByPath(staticDocFolder);
            ILFCollection allDocs = (ILFCollection)ParentFolder.GetChildren();

            foreach (ILFEntry entry in allDocs)
            {
                if (entry.EntryType != Entry_Type.ENTRY_TYPE_DOCUMENT)
                    continue;
                LFDocument doc = entry as LFDocument;
                documents.Add(doc.ID);
            }

            return documents;
        }

        public LFDocument GetDocument(int id)
        {
            return lfdb.GetEntryByID(id) as LFDocument;
        }

        public ArrayList GetOpenInvoiceIDs(int CustomerNumber, ArrayList InvoiceNumbers, int StoreNumber = -1)
        {
            ArrayList result = new ArrayList();
            ArrayList found = new ArrayList();
            ArrayList notFound = new ArrayList();

            if (InvoiceNumbers.Count == 0)
            {
                result.Add(found);
                result.Add(notFound);
                return result;
            }

            SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString);
            string cust_num_col = ConfigurationManager.AppSettings["Customer Number Field"];
            string inv_num_col = ConfigurationManager.AppSettings["Invoice Number Field"];
            string store_num_col = ConfigurationManager.AppSettings["Store Number Field"];
            sql.Open();
            string lfsql = ConfigurationManager.AppSettings["LFSQL DB"];
            string query = "";

            if (StoreNumber != -1)
            {
                query = "SELECT doc.tocid as id, propval1.num_val as custnum, propval2.num_val as invoicenum, propval3.num_val as storenum " +
                "FROM " + lfsql + ".dbo.doc as doc " +
                "JOIN " + lfsql + ".dbo.propval as propval1 ON doc.tocid = propval1.tocid " +
                "JOIN " + lfsql + ".dbo.propdef as propdef1 ON propval1.prop_id = propdef1.prop_id " +
                "JOIN " + lfsql + ".dbo.propval as propval2 ON doc.tocid = propval2.tocid " +
                "JOIN " + lfsql + ".dbo.propdef as propdef2 ON propval2.prop_id = propdef2.prop_id " +
                "JOIN " + lfsql + ".dbo.propval as propval3 ON doc.tocid = propval3.tocid " +
                "JOIN " + lfsql + ".dbo.propdef as propdef3 ON propval3.prop_id = propdef3.prop_id " +
                "WHERE doc.pagenum = 0 " +
                " AND propval1.num_val = @CUSTNUM " +
                " AND propdef1.prop_name = @CUSTNUMCOL " +
                " AND propdef3.prop_name = @STORENUMCOL AND propval3.num_val <> "+StoreNumber+" ";
                Console.Out.WriteLine("Wholesale Statement");
            }
            else
            {
                query = "SELECT doc.tocid as id, propval1.num_val as custnum, propval2.num_val as invoicenum " +
                "FROM " + lfsql + ".dbo.doc as doc " +
                "JOIN " + lfsql + ".dbo.propval as propval1 ON doc.tocid = propval1.tocid " +
                "JOIN " + lfsql + ".dbo.propdef as propdef1 ON propval1.prop_id = propdef1.prop_id " +
                "JOIN " + lfsql + ".dbo.propval as propval2 ON doc.tocid = propval2.tocid " +
                "JOIN " + lfsql + ".dbo.propdef as propdef2 ON propval2.prop_id = propdef2.prop_id " +
                "WHERE doc.pagenum = 0 " +
                " AND propval1.num_val = @CUSTNUM " +
                " AND propdef1.prop_name = @CUSTNUMCOL ";
                Console.Out.WriteLine("Retail Statement");
            }

            query += "AND propdef2.prop_name = @INVOICENUMCOL AND ( propval2.num_val = ";

            if (InvoiceNumbers.Count == 1)
            {
                int n = Convert.ToInt32(InvoiceNumbers[0]);
                query += n + ")";
            }
            else
            {
                for (int i = 0; i < InvoiceNumbers.Count; i++)
                {
                    int n = Convert.ToInt32(InvoiceNumbers[i]);
                    string s = n.ToString();
                    
                    if (s.Length > 5)
                    {
                        query += n + " OR propval2.num_val = " + s.Substring(0, s.Length - 1);
                    }
                    else
                    {                    
                        query += n + " OR propval2.num_val = " + s;
                    }
                    if (i == InvoiceNumbers.Count - 1)
                    {
                        query += ")";
                    }
                    else
                    {
                        query += " OR propval2.num_val = ";
                    }
                }
            }
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.AddWithValue("@CUSTNUM", CustomerNumber);
            cmd.Parameters.AddWithValue("@CUSTNUMCOL", cust_num_col);
            cmd.Parameters.AddWithValue("@INVOICENUMCOL", inv_num_col);
            cmd.Parameters.AddWithValue("@STORENUMCOL", store_num_col);

            SqlDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                Console.Out.WriteLine(query);
                throw new Exception("Wholesale Error");
            }
            DataTable data = new DataTable();
            data.Load(reader);
            sql.Close();

            Console.Out.WriteLine("Query returned " + data.Rows.Count + " Rows");

            Hashtable invoices = new Hashtable();
            Hashtable excuses = new Hashtable();

            foreach (object o in InvoiceNumbers)
            {
                if (o == null) continue;
                string inv = o.ToString();
                bool f = false;
                foreach (DataRow r in data.Rows)
                {
                    int r0 = Convert.ToInt32(r[0]);
                    
                    string r2 = Convert.ToInt32(r[2]).ToString();
                    if (r2.Equals(inv)) //This row is a matching invoice
                    {
                        //Reporting.AppendLog("FOUND", r2);
                        try
                        {
                            LFDocument doc = lfdb.GetEntryByID(r0) as LFDocument;
                            if (!doc.FullPath.Contains(@"AR\Customers"))
                            {
                                doc.Dispose();
                                excuses[CustomerNumber] = doc.FullPath + @" does not contain AR\Customers";
                                continue;
                            }
                            doc.Dispose();
                        }
                        catch (Exception ex)
                        {
                            Reporting.AppendLog("LASERFICHE", ex.Message);
                            continue;
                        }
                        Console.Out.WriteLine("Found Invoice");
                        try
                        {
                            if ((invoices.ContainsKey(inv) && Convert.ToInt32(invoices[inv]) < r0) || !invoices.ContainsKey(inv))
                            {
                                Console.Out.WriteLine("   Adding Invoice for Merge");
                                invoices[inv] = r0;
                            }
                            else
                            {
                                Console.Out.WriteLine("  Not Adding Invoice for Merge");
                            }
                        }
                        catch
                        {
                            throw new Exception("Invalid Cast in ContainsKey");
                        }
                        //found.Add(Convert.ToInt32(r[0]));
                        //found.Add(r0);
                        f = true;
                    }
                }
                if (!f)
                {
                    notFound.Add(inv);
                    if (!excuses.ContainsKey(CustomerNumber))
                    {
                        excuses[CustomerNumber] = "Invoice(s) not found in Laserfiche";
                    }
                }
            }
            //found.Clear();
            ArrayList values = new ArrayList(invoices.Values);
            foreach (object v in values)
            {
                if (v == null) continue;
                found.Add(Convert.ToInt32(v));
            }
            result.Add(found);
            result.Add(notFound);
            result.Add(excuses);
            return result;
        }
    }

    public class Statement
    {
        public int EntryID { get; set; }
        public ArrayList invoices { get; set; }
    }
}