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.Web.Configuration;
using System.Data.OleDb;
using System.Data;
using System.Text;
using System.Threading;
using System.Globalization;
public partial class SearchBatch : System.Web.UI.Page
{
private DataSet ds = new DataSet();
private int m_iAccountID = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (Request.IsAuthenticated == false)
{
Response.Redirect("~/Account/Login.aspx");
}
if (!Page.IsPostBack)
{
LoadDefaultValuesFromDatabase();
}
this.pnlAdjustments.Visible = false;
}
private void LoadDefaultValuesFromDatabase()
{
string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;
OleDbConnection oleDBConn = new OleDbConnection(connectionString);
string sql = "SELECT curSingleRate, " +
"curCoupleRate, " +
"curFamilyRate, " +
"intBatchRenewalYear " +
"FROM BERPPlanAdministration";
OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);
oleDBConn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Session["SingleRate"] = (decimal)reader[0];
Session["CoupleRate"] = (decimal)reader[1];
Session["FamilyRate"] = (decimal)reader[2];
Session["RenewalYear"] = (Int16)reader[3];
}
reader.Close();
oleDBConn.Close();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
Page.Validate();
if (Page.IsValid)
{
this.gvBatchSearch.SelectedIndex = -1;
this.gvBatchSearch.PageIndex = 0;
this.gvBatchDetails.SelectedIndex = -1;
this.gvBatchDetails.PageIndex = 0;
}
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtBoxAccountID.Text = "";
txtBoxLastName.Text = "";
txtBoxFirstName.Text = "";
ddlJamatkhana.SelectedIndex = -1;
this.gvBatchSearch.PageIndex = 0;
this.gvBatchDetails.PageIndex = 0;
if (Request.QueryString.Get("id") != null)
{
Response.Redirect("~/SearchBatch.aspx"); ;
}
this.lblError.Visible = false;
this.pnlAdjustments.Visible = false;
}
protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
{
if (txtBoxAccountID.Text != string.Empty)
{
args.IsValid = true;
}
else if ((txtBoxLastName.Text != string.Empty) || (txtBoxFirstName.Text != string.Empty) || (ddlJamatkhana.SelectedIndex != 0))
{
args.IsValid = true;
}
else
{
args.IsValid = false;
}
}
protected void gvBatchSearch_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.btnSearch_Click(sender, e);
this.gvBatchSearch.PageIndex = e.NewPageIndex;
}
protected void gvBatchSearch_SelectedIndexChanged(object sender, EventArgs e)
{
lblError.Visible = false;
int selectedIndex = gvBatchSearch.SelectedIndex;
// You can retrieve the key field from the SelectedDataKey property
int accountID = (int)gvBatchSearch.SelectedDataKey.Values["AccountID"];
m_iAccountID = accountID;
/*
int previousRenewalYear = GetPreviousRenewalYearForSelectedAccount(accountID);
int count = GetFamilyCountForSelectedAccount(accountID);
if (count == 1)
{
if (Session["SingleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
{
this.lblBatchAmount.Text = Convert.ToDecimal(Session["SingleRate"]).ToString(); ; //Convert.ToDecimal(Session["SingleRate"]).ToString("C");
}
}
else if (count == 2)
{
if (Session["CoupleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
{
this.lblBatchAmount.Text = Convert.ToDecimal(Session["CoupleRate"]).ToString();
}
}
else if (count > 2)
{
if (Session["FamilyRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
this.lblBatchAmount.Text = Convert.ToDecimal(Session["FamilyRate"]).ToString();
}
else
{
if (Session["SingleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
this.lblBatchAmount.Text = Convert.ToDecimal(Session["SingleRate"]).ToString();
}
this.lblRenewalYear.Text = Convert.ToInt16(Session["RenewalYear"]).ToString();
this.lblPreviousRenewalYear.Text = previousRenewalYear.ToString();
this.txtBoxAmountPaidByCash.Text = "0"; // Convert.ToDecimal(0).ToString("C");
this.txtBoxAmountPaidByCheque.Text = "0"; // Convert.ToDecimal(0).ToString("C");
this.txtBoxChequeNumber.Text = "";
*/
this.lblError.Text = "";
}
private int GetFamilyCountForSelectedAccount(int accountID)
{
int familyCount = 0;
string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;
OleDbConnection oleDBConn = new OleDbConnection(connectionString);
string sql = "SELECT COUNT(*) FROM Members WHERE AccountID = " + accountID.ToString() + " AND Members.MemberStatusID = 1";
OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);
oleDBConn.Open();
familyCount = (int)cmd.ExecuteScalar();
oleDBConn.Close();
return familyCount;
}
private int GetPreviousRenewalYearForSelectedAccount(int accountID)
{
int renewalYear = 0;
string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;
OleDbConnection oleDBConn = new OleDbConnection(connectionString);
string sql = "SELECT AccountRenewalYear FROM Accounts WHERE AccountID = " + accountID.ToString();
OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);
oleDBConn.Open();
renewalYear = (short)cmd.ExecuteScalar();
oleDBConn.Close();
return renewalYear;
}
protected void btnAddToCart_Click(object sender, EventArgs e)
{
Page.Validate();
if (Page.IsValid)
{
/*
Account selectedAccount = new Account();
selectedAccount.iAccountID = (int)gvBatchSearch.SelectedDataKey.Values["AccountID"];
selectedAccount.dAmountDue = Convert.ToDecimal(this.lblAmountDue.Text);
selectedAccount.dAmountPaidCash = Convert.ToDecimal(this.txtBoxAmountPaidByCash.Text);
selectedAccount.dAmountPaidCheque = Convert.ToDecimal(this.txtBoxAmountPaidByCheque.Text);
// You can retrieve other data directly from the Cells coll
// as long as you know the column offset.
if (gvBatchSearch.SelectedRow.Cells[2].Text != " ")
selectedAccount.mLastName = gvBatchSearch.SelectedRow.Cells[2].Text;
else
gvBatchSearch.SelectedRow.Cells[2].Text = "";
if (gvBatchSearch.SelectedRow.Cells[3].Text != " ")
selectedAccount.mMiddleName = gvBatchSearch.SelectedRow.Cells[3].Text;
else
selectedAccount.mMiddleName = "";
if (gvBatchSearch.SelectedRow.Cells[4].Text != " ")
selectedAccount.mFirstName = gvBatchSearch.SelectedRow.Cells[4].Text;
else
selectedAccount.mFirstName = "";
if (this.txtBoxChequeNumber.Text != string.Empty)
{
selectedAccount.iChequeNumber = Convert.ToInt32(this.txtBoxChequeNumber.Text);
}
CartItem cartitem = new CartItem();
cartitem.cartAccount = selectedAccount;
this.AddToCart(cartitem);
*/
}
}
private void AddToCart(CartItem cartitem)
{
SortedList cart = this.GetCart();
int iAccountID = cartitem.cartAccount.iAccountID;
if (cart.ContainsKey(iAccountID))
{
// Account already exists in the batch
this.lblError.Text = "Account ID " + iAccountID.ToString() + " was not added to the batch as it already exists.";
}
else
{
cart.Add(iAccountID, cartitem);
this.lblError.Text = "Account ID " + iAccountID.ToString() + " was successfully added to the batch.";
}
}
private SortedList GetCart()
{
if (Session["Cart"] == null)
{
Session.Add("Cart", new SortedList());
}
return (SortedList)Session["Cart"];
}
protected void adsMembers_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
lblError.Visible = true;
if (e.AffectedRows > 0)
{
lblError.Text = "Member details have been successfully updated.";
}
else
{
lblError.Text = "ERROR: Failed to update the the Member details. Please try again.";
}
}
protected void gvAccountBatches_SelectedIndexChanged(object sender, EventArgs e)
{
this.lblError.Visible = false;
this.gvBatchDetails.Visible = true;
if (Context.User.IsInRole("BerpAdministrators"))
{
gvBatchDetails.Columns[0].Visible = true;
}
else
{
gvBatchDetails.Columns[0].Visible = false;
}
this.gvBatchAdjustments.Visible = true;
}
protected void adsSearchBatch_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString.Get("id") == null)
{
e.Cancel = true;
}
}
else if ((txtBoxAccountID.Text == "") &&
(txtBoxLastName.Text == "") &&
(txtBoxFirstName.Text == "") &&
(ddlJamatkhana.SelectedIndex == 0) )
{
e.Cancel = true;
}
}
protected void gvBatchSearch_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
this.gvAccountBatches.SelectedIndex = -1;
this.gvAccountBatches.DataBind();
this.gvBatchDetails.DataBind();
this.gvBatchAdjustments.DataBind();
}
protected void gvBatchDetails_SelectedIndexChanged(object sender, EventArgs e)
{
this.lblError.Visible = false;
if (Context.User.IsInRole("BerpAdministrators"))
{
this.pnlAdjustments.Visible = true;
}
else
{
this.pnlAdjustments.Visible = false;
}
this.gvBatchAdjustments.Visible = true;
// You can retrieve the key field from the SelectedDataKey property
int batchID = (int)gvBatchDetails.SelectedDataKey.Values["BatchID"];
// You can retrieve the key field from the SelectedDataKey property
int accountID = (int)gvBatchDetails.SelectedDataKey.Values["AccountID"];
int selectedIndex = gvBatchSearch.SelectedIndex;
// You can retrieve the key field from the SelectedDataKey property
//int accountID = (int)gvBatchSearch.SelectedDataKey.Values["AccountID"];
//int previousRenewalYear = GetPreviousRenewalYearForSelectedAccount(accountID);
int count = GetFamilyCountForSelectedAccount(accountID);
if (count == 1)
{
if (Session["SingleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
{
this.lblBatchAmount.Text = Convert.ToDecimal(Session["SingleRate"]).ToString(); ; //Convert.ToDecimal(Session["SingleRate"]).ToString("C");
}
}
else if (count == 2)
{
if (Session["CoupleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
{
this.lblBatchAmount.Text = Convert.ToDecimal(Session["CoupleRate"]).ToString();
}
}
else if (count > 2)
{
if (Session["FamilyRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
this.lblBatchAmount.Text = Convert.ToDecimal(Session["FamilyRate"]).ToString();
}
else
{
if (Session["SingleRate"] == null)
{
this.lblBatchAmount.Text = "0";
}
else
this.lblBatchAmount.Text = Convert.ToDecimal(Session["SingleRate"]).ToString();
}
StringBuilder sbName = new StringBuilder();
// You can retrieve other data directly from the Cells coll
// as long as you know the column offset.
if (gvBatchDetails.SelectedRow.Cells[3].Text != " ")
sbName.Append(gvBatchDetails.SelectedRow.Cells[3].Text.ToUpper());
else
sbName.Append("");
//sbName.Append(", ");
//if (gvBatchDetails.SelectedRow.Cells[5].Text != " ")
// sbName.Append(gvBatchDetails.SelectedRow.Cells[5].Text);
//else
// sbName.Append("");
this.lblBatchID.Text = batchID.ToString();
this.lblAccountID.Text = accountID.ToString();
this.lblFullName.Text = sbName.ToString();
this.txtBoxAmountPaidByCash.Text = "0";
this.txtBoxAmountPaidByCheque.Text = "0";
this.txtBoxChequeNumber.Text = "";
this.txtboxReason.Text = "";
}
protected void CustomValidatorAdjustmentAmount_ServerValidate(object source, ServerValidateEventArgs args)
{
decimal cash = Convert.ToDecimal(txtBoxAmountPaidByCash.Text);
decimal cheque = Convert.ToDecimal(txtBoxAmountPaidByCheque.Text);
decimal total = Convert.ToDecimal(lblBatchAmount.Text);
if (txtboxReason.Text == string.Empty)
{
args.IsValid = false;
}
else if ((txtBoxAmountPaidByCash.Text == string.Empty) || (txtBoxAmountPaidByCheque.Text == string.Empty))
{
args.IsValid = false;
}
else if (cash + cheque == 0.0M)
{
args.IsValid = false;
}
else if (cash + cheque > total)
{
args.IsValid = false;
}
else
{
args.IsValid = true;
}
}
protected void btnAddAdjustment_Click(object sender, EventArgs e)
{
Page.Validate();
if (Page.IsValid)
{
DateTime dtAccountCreated = new DateTime();
dtAccountCreated = DateTime.Now;
string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;
OleDbConnection oleDBConn = new OleDbConnection(connectionString);
string sql = "INSERT INTO BatchAdjustments (BatchAdjustmentBatchID, BatchAdjustmentAccountID, BatchAdjustmentCashAmount, BatchAdjustmentChequeAmount, BatchAdjustmentChequeNumber, BatchAdjustmentCreatedBy, BatchAdjustmentCreatedDate, BatchAdjustmentComments) VALUES (@BatchID, @AccountID, @CashAmount, @ChequeAmount, @ChequeNumber, @CreatedBy, @CreatedDate, @Comments)";
OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);
cmd.CommandType = CommandType.Text;
decimal dCash = 0.0M;
decimal dCheque = 0.0M;
if (rblAdjustmentType.SelectedValue == "1")
{
// Refunds, Stop payments (batch reduction)
dCash = -1 * Convert.ToDecimal(txtBoxAmountPaidByCash.Text.Trim());
dCheque = -1 * Convert.ToDecimal(txtBoxAmountPaidByCheque.Text.Trim());
}
else
{
// Payment received for registration (batch increased)
dCash = +1 * Convert.ToDecimal(txtBoxAmountPaidByCash.Text.Trim());
dCheque = +1 * Convert.ToDecimal(txtBoxAmountPaidByCheque.Text.Trim());
}
cmd.Parameters.AddWithValue("@BatchID", lblBatchID.Text);
cmd.Parameters.AddWithValue("@AccountID", lblAccountID.Text);
cmd.Parameters.AddWithValue("@CashAmount", dCash);
cmd.Parameters.AddWithValue("@ChequeAmount", dCheque);
if (txtBoxChequeNumber.Text != string.Empty)
cmd.Parameters.AddWithValue("@ChequeNumber", txtBoxChequeNumber.Text.Trim());
else
cmd.Parameters.AddWithValue("@ChequeNumber", DBNull.Value);
cmd.Parameters.AddWithValue("@CreatedBy", User.Identity.Name);
cmd.Parameters.AddWithValue("@CreatedDate", dtAccountCreated.ToOADate());
cmd.Parameters.AddWithValue("@Comments", txtboxReason.Text.Trim());
oleDBConn.Open();
cmd.ExecuteNonQuery();
oleDBConn.Close();
this.lblError.Visible = true;
this.gvBatchSearch.SelectedIndex = -1;
this.gvAccountBatches.DataBind();
this.lblError.Text = "Batch was successfully adjusted for BatchID = " + lblBatchID.Text + " AccountID = " + lblAccountID.Text;
this.txtBoxAmountPaidByCash.Text = "";
this.txtBoxAmountPaidByCheque.Text = "";
this.txtBoxChequeNumber.Text = "";
this.txtboxReason.Text = "";
}
}
protected void gvBatchDetails_RowDataBound(object sender, GridViewRowEventArgs e)
{
}
protected string FormatLastNameToUpper(object objLastName)
{
string sLastName = (string)objLastName;
return sLastName.ToUpper();
}
protected void ChequeNumberCustomValidator_ServerValidate(object source, ServerValidateEventArgs args)
{
if (txtBoxAmountPaidByCheque.Text == string.Empty)
{
args.IsValid = false;
}
else if (txtBoxChequeNumber.Text == string.Empty)
{
decimal cheque = Convert.ToDecimal(txtBoxAmountPaidByCheque.Text);
if (cheque > 0.0M)
args.IsValid = false;
else if (cheque == 0.0M)
args.IsValid = true;
}
else
{
args.IsValid = true;
}
}
}