using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.Configuration;
using System.Data.OleDb;
using System.Text;
using System.Globalization;
using System.Threading;

public partial class Renewal : System.Web.UI.Page
{
   private SortedList cart = new SortedList();

   private DateTime dtBatchCreation = new DateTime();

   protected void Page_Load(object sender, EventArgs e)
   {
      if (Request.IsAuthenticated == false)
      {
         Response.Redirect("~/Account/Login.aspx");
      }

      if (!Page.IsPostBack)
      {
         GetCart();
         DisplayCart();
         DisplayTotals();

         this.btnRenew.Enabled = ((lbBatchRenewal.Items.Count == 0) ? false : true );
         this.btnRemoveItem.Enabled = ((lbBatchRenewal.Items.Count == 0) ? false : true);
         this.btnEmptyCart.Enabled = ((lbBatchRenewal.Items.Count == 0) ? false : true);

         this.lblInfoMessage.Visible = false;
      }
   }

   private void DisplayCart()
   {
      lbBatchRenewal.Items.Clear();

      foreach (DictionaryEntry entry in cart)
      {
         CartItem item = (CartItem)entry.Value;
         lbBatchRenewal.Items.Add(item.Display());
      }
   }

   private void GetCart()
   {
      if (Session["Cart"] == null)
      {
         Session.Add("Cart", new SortedList());
      }

      this.cart = (SortedList)Session["Cart"];
   }

   protected void btnRemoveItem_Click(object sender, EventArgs e)
   {
      this.GetCart();

      if ((lbBatchRenewal.SelectedIndex > -1) && (this.cart.Count > 0))
      {
         cart.RemoveAt(lbBatchRenewal.SelectedIndex);

         this.DisplayCart();
         this.DisplayTotals();
      }
      else
      {
         this.lblInfoMessage.Visible = true;
         this.lblInfoMessage.Text = "Please select an item from the Batch Cart to remove.";
      }
   }

   protected void btnEmptyCart_Click(object sender, EventArgs e)
   {
      this.GetCart();

      this.cart.Clear();

      lbBatchRenewal.Items.Clear();

      this.DisplayTotals();

      this.lblInfoMessage.Visible = true;
      this.lblInfoMessage.Text = "All items in the Batch Cart have been successfully removed.";
   }

   protected void btnRenew_Click(object sender, EventArgs e)
   {
      Page.Validate();

      if (Page.IsValid)
      {
         decimal dBatchTotal = CalculateBatchTotal();
         
         InsertBatch(dBatchTotal);

         int iBatchID = GetBatchID();

         InsertBatchDetails(iBatchID);

         UpdateAccounts();

         //if all has gone well upto now
         this.cart.Clear();

         lbBatchRenewal.Items.Clear();

         this.DisplayTotals();

         this.ddlJamatkhana.SelectedIndex = 0;
         this.txtboxBatchComments.Text = "";

         this.lblInfoMessage.Visible = true;
         this.lblInfoMessage.Text = "Accounts in the batch cart were renewed with Batch ID = " + iBatchID.ToString() ;
      }

   }

   private void UpdateAccounts()
   {
      StringBuilder sbAccountIDs = new StringBuilder();
  
      this.GetCart();

      int index = 0;

      foreach (DictionaryEntry entry in cart)
      {
         string strDelimiter = "";

         CartItem item = (CartItem)entry.Value;

         if (index++ == 0)
            strDelimiter = "";
         else
            strDelimiter = ",";

         sbAccountIDs.Append( strDelimiter );
         sbAccountIDs.Append( item.cartAccount.iAccountID );
      }

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sql = "UPDATE Accounts SET AccountRenewalYear = ?, AccountDateRenewed = ? WHERE AccountID IN ({0})";

      string newsql = String.Format(sql, sbAccountIDs);

      OleDbCommand cmd = new OleDbCommand(newsql, oleDBConn);

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.AddWithValue("@RenewalYear", Convert.ToInt32(Session["RenewalYear"]));
      cmd.Parameters.AddWithValue("@DateRenewed", dtBatchCreation.ToOADate());

      oleDBConn.Open();

      cmd.ExecuteNonQuery();

      oleDBConn.Close();      
   }

   private int GetBatchID()
   {
      int batchID = 0;

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);
     
      string sql = "SELECT BatchID FROM Batch WHERE BatchCreationDate = @BatchDate";

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      cmd.Parameters.AddWithValue("@BatchDate", dtBatchCreation.ToOADate());

      oleDBConn.Open();

      OleDbDataReader reader = cmd.ExecuteReader();

      while (reader.Read())
      {
         batchID = (int)reader["BatchID"];
      }

      reader.Close();

      oleDBConn.Close();

      return batchID;
   }

   private void DisplayTotals()
   {
      this.GetCart();

      decimal dCashTotal = 0.0M;
      decimal dChequeTotal = 0.0M;

      foreach (DictionaryEntry entry in cart)
      {
         CartItem item = (CartItem)entry.Value;
         dCashTotal += item.cartAccount.dAmountPaidCash;
         dChequeTotal += item.cartAccount.dAmountPaidCheque;
      }

      this.lblSummaryCash.Text = dCashTotal.ToString("C");
      this.lblSummaryCheque.Text = dChequeTotal.ToString("C");
      this.lblSummaryTotal.Text = (dCashTotal + dChequeTotal).ToString("C");
      return;
   }

   private decimal CalculateBatchTotal()
   {
      this.GetCart();

      decimal dBatchTotal = 0.0M;

      foreach (DictionaryEntry entry in cart)
      {
         CartItem item = (CartItem)entry.Value;
         dBatchTotal += item.cartAccount.dAmountPaidCash;
         dBatchTotal += item.cartAccount.dAmountPaidCheque;
      }
      return dBatchTotal;
   }

   private void InsertBatch(decimal dBatchTotal)
   {
      //Get the culture property of the thread.
      CultureInfo ci = Thread.CurrentThread.CurrentCulture;
      //Create TextInfo object.
      TextInfo ti = ci.TextInfo;

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);
      
      dtBatchCreation = DateTime.Now;

      string sql = "INSERT INTO Batch (JamatkhanaID, BatchCreationDate, UserName, BatchTotal, BatchComments) VALUES (@JamatkhanaID, @BatchCreationDate, @UserName, @BatchTotal, @Comments)";
      
      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      cmd.CommandType = CommandType.Text;
      cmd.Parameters.AddWithValue("@JamatkhanaID", Convert.ToInt32(ddlJamatkhana.SelectedValue) );
      cmd.Parameters.AddWithValue("@BatchCreationDate", dtBatchCreation.ToOADate());
      cmd.Parameters.AddWithValue("@UserName", User.Identity.Name);
      cmd.Parameters.AddWithValue("@BatchTotal", dBatchTotal);

      if (txtboxBatchComments.Text != string.Empty)
      {
         cmd.Parameters.AddWithValue("@Comments", ti.ToTitleCase(txtboxBatchComments.Text.Trim()));
      }
      else
      {
         cmd.Parameters.AddWithValue("@Comments", "Initial batch created.");
      }

      oleDBConn.Open();

      cmd.ExecuteNonQuery();

      oleDBConn.Close();
   }

   private void InsertBatchDetails(int batchID)
   {
            //Get the culture property of the thread.
      CultureInfo ci = Thread.CurrentThread.CurrentCulture;
      //Create TextInfo object.
      TextInfo ti = ci.TextInfo;

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      string sql = "INSERT INTO BatchDetails (BatchID, AccountID, AmountDue, AmountPaid, BatchDetailsCashAmount, BatchDetailsChequeAmount, BatchDetailsChequeNumber, BatchPaymentTypeID, BatchDetailsComments) VALUES (@BatchID, @AccountID, @AmountDue, @AmountPaid, @CashAmount, @ChequeAmount, @ChequeNumber, @BatchPaymentTypeID, @Comments)";

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add("@BatchID", OleDbType.Integer );
      cmd.Parameters.Add("@AccountID", OleDbType.Integer);
      cmd.Parameters.Add("@AmountDue", OleDbType.Currency);
      cmd.Parameters.Add("@AmountPaid", OleDbType.Currency);

      cmd.Parameters.Add("@CashAmount", OleDbType.Currency);
      cmd.Parameters.Add("@ChequeAmount", OleDbType.Currency);
      cmd.Parameters.Add("@ChequeNumber", OleDbType.Currency);

      cmd.Parameters.Add("@BatchPaymentTypeID", OleDbType.Integer);
      cmd.Parameters.Add("@Comments", OleDbType.Char);

      this.GetCart();

      oleDBConn.Open();

      foreach (DictionaryEntry entry in cart)
      {
         CartItem item = (CartItem)entry.Value;
         cmd.Parameters["@BatchID"].Value = batchID;
         cmd.Parameters["@AccountID"].Value = item.cartAccount.iAccountID;
         cmd.Parameters["@AmountDue"].Value = item.cartAccount.dAmountDue;
         cmd.Parameters["@AmountPaid"].Value = item.cartAccount.dAmountPaidCash + item.cartAccount.dAmountPaidCheque;

         cmd.Parameters["@CashAmount"].Value = item.cartAccount.dAmountPaidCash;
         cmd.Parameters["@ChequeAmount"].Value = item.cartAccount.dAmountPaidCheque;

         if (item.cartAccount.iChequeNumber != 0)
            cmd.Parameters["@ChequeNumber"].Value = item.cartAccount.iChequeNumber;
         else
            cmd.Parameters["@ChequeNumber"].Value = DBNull.Value;

         if (item.cartAccount.dAmountPaidCash == 0)
            cmd.Parameters["@BatchPaymentTypeID"].Value = 1; // Cheque = 1
         else if (item.cartAccount.dAmountPaidCheque == 0)
            cmd.Parameters["@BatchPaymentTypeID"].Value = 2; // Cash = 2
         else
            cmd.Parameters["@BatchPaymentTypeID"].Value = 3; // Cash/Cheque = 3

         if ((item.cartAccount.sComments != null) && (item.cartAccount.sComments != string.Empty))
         {
            cmd.Parameters["@Comments"].Value = ti.ToTitleCase(item.cartAccount.sComments.Trim());
         }
         else
         {
            cmd.Parameters["@Comments"].Value = string.Empty;
         }

         cmd.ExecuteNonQuery();

      }
      
      oleDBConn.Close();
   }
}