public DataTable CustomersWithRewards(ContactColumns clsContactColumns, long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, Int32 limit, string CustomerCode_RewardCardNo, DateTime RewardExpiryDateFrom, DateTime RewardExpiryDateTo, Constants.DateSelectionString BirthDate = Constants.DateSelectionString.ALL, Int16 RewardCardStatus = -1, string SortField = "ContactCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
// enable this to include joining to table tblContactGroup
clsContactColumns.ContactGroupName = true;
clsContactColumns.RewardDetails = true;
string SQL = SQLSelect(clsContactColumns);
SQL += "WHERE (tblContactGroup.ContactGroupCategory = @CustomerCategory OR tblContactGroup.ContactGroupCategory = @BothCategory) ";
cmd.Parameters.AddWithValue("@CustomerCategory", ContactGroupCategory.CUSTOMER.ToString("d"));
cmd.Parameters.AddWithValue("@BothCategory", ContactGroupCategory.BOTH.ToString("d"));
if (SequenceNoStart != Constants.ZERO)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND tblContacts.ContactID < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND tblContacts.ContactID > " + SequenceNoStart.ToString() + " ";
}
if (CustomerCode_RewardCardNo != string.Empty)
{
SQL += "AND (RewardCardNo LIKE @CustomerCode_RewardCardNo OR ContactCode LIKE @CustomerCode_RewardCardNo OR ContactName LIKE @CustomerCode_RewardCardNo) ";
cmd.Parameters.AddWithValue("@CustomerCode_RewardCardNo", CustomerCode_RewardCardNo);
}
if (RewardCardStatus != -1)
{
SQL += "AND RewardCardStatus = @RewardCardStatus ";
cmd.Parameters.AddWithValue("@RewardCardStatus", RewardCardStatus);
}
if (RewardExpiryDateFrom != DateTime.MinValue)
{
SQL += "AND ExpiryDate >= @RewardExpiryDateFrom ";
cmd.Parameters.AddWithValue("@RewardExpiryDateFrom", RewardExpiryDateFrom.ToString("yyyy-MM-dd"));
}
if (RewardExpiryDateFrom != DateTime.MinValue)
{
SQL += "AND ExpiryDate <= @RewardExpiryDateTo ";
cmd.Parameters.AddWithValue("@RewardExpiryDateTo", RewardExpiryDateTo.ToString("yyyy-MM-dd"));
}
if (BirthDate != Constants.DateSelectionString.ALL)
{
switch (BirthDate)
{
case Constants.DateSelectionString.Today:
SQL += "AND BirthDate = @BirthDate ";
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now.ToString("yyyy-MM-dd"));
break;
case Constants.DateSelectionString.CurrentMonth:
SQL += "AND MONTH(BirthDate) = @BirthDate ";
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now.Month);
break;
case Constants.DateSelectionString.PreviousMonth:
SQL += "AND MONTH(BirthDate) = @BirthDate ";
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddMonths(-1).Month);
break;
case Constants.DateSelectionString.NextMonth:
SQL += "AND MONTH(BirthDate) = @BirthDate ";
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddMonths(1).Month);
break;
}
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ContactCode") + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public DataTable Guarantors(ContactColumns clsContactColumns, string LastNameFrom = "", string LastNameTo = "", string CustomerCode_CreditCardNo = "", DateTime? CreditCardExpiryDateFrom = null, DateTime? CreditCardExpiryDateTo = null, CreditCardStatus CreditCardStatus = CreditCardStatus.All, Int32 CreditCardTypeID = 0, string SortField = "ContactCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
// enable this to include joining to table tblContactGroup
clsContactColumns.ContactGroupName = true;
clsContactColumns.CreditDetails = true;
string SQL = SQLSelect(clsContactColumns);
if (CreditCardTypeID == 0)
{
SQL += "WHERE tblContacts.ContactID = tblContactCreditCardInfo.GuarantorID ";
}
else
{
SQL += "WHERE tblContacts.ContactID = tblContactCreditCardInfo.GuarantorID AND tblContactCreditCardInfo.CreditCardTypeID = @CreditCardTypeID ";
cmd.Parameters.AddWithValue("CreditCardTypeID", CreditCardTypeID);
}
if (!string.IsNullOrEmpty(CustomerCode_CreditCardNo))
{
SQL += "AND (CreditCardNo LIKE @CustomerCode_CreditCardNo OR ContactCode LIKE @CustomerCode_CreditCardNo OR ContactName LIKE @CustomerCode_CreditCardNo) ";
cmd.Parameters.AddWithValue("@CustomerCode_CreditCardNo", CustomerCode_CreditCardNo + '%');
}
if (CreditCardExpiryDateFrom.GetValueOrDefault(DateTime.MinValue) != DateTime.MinValue)
{
SQL += "AND ExpiryDate >= @CreditCardExpiryDateFrom ";
cmd.Parameters.AddWithValue("@CreditCardExpiryDateFrom", CreditCardExpiryDateFrom.GetValueOrDefault(DateTime.MinValue).ToString("yyyy-MM-dd"));
}
if (CreditCardExpiryDateFrom.GetValueOrDefault(DateTime.MinValue) != DateTime.MinValue)
{
SQL += "AND ExpiryDate <= @CreditCardExpiryDateTo ";
cmd.Parameters.AddWithValue("@CreditCardExpiryDateTo", CreditCardExpiryDateTo.GetValueOrDefault(DateTime.MinValue).ToString("yyyy-MM-dd"));
}
if (CreditCardStatus != RetailPlus.CreditCardStatus.All)
{
SQL += "AND CreditCardStatus = @CreditCardStatus ";
cmd.Parameters.AddWithValue("@CreditCardStatus", CreditCardStatus.ToString("d"));
}
if (!string.IsNullOrEmpty(LastNameFrom) && !string.IsNullOrEmpty(LastNameTo))
{
SQL += "AND ContactID IN (SELECT ContactID FROM tblContactAddOn WHERE LastName >= @LastNameFrom AND LastName <= @LastNameTo) ";
cmd.Parameters.AddWithValue("LastNameFrom", LastNameFrom);
cmd.Parameters.AddWithValue("LastNameTo", LastNameTo);
}
else if (!string.IsNullOrEmpty(LastNameFrom))
{
SQL += "AND ContactID IN (SELECT ContactID FROM tblContactAddOn WHERE LastName LIKE @LastNameFrom) ";
cmd.Parameters.AddWithValue("LastNameFrom", LastNameFrom + '%');
}
else if (!string.IsNullOrEmpty(LastNameTo))
{
SQL += "AND ContactID IN (SELECT ContactID FROM tblContactAddOn WHERE LastName LIKE @LastNameTo) ";
cmd.Parameters.AddWithValue("LastNameTo", LastNameTo + '%');
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ContactCode") + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public DataTable Tables(ContactColumns clsContactColumns, long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, ContactColumns SearchColumns, string SearchKey, Int32 limit, bool HasCreditOnly, string SortField = "ContactName", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
// enable this to include joining to table tblContactGroup
clsContactColumns.ContactGroupName = true;
string SQL = SQLSelect(clsContactColumns);
SQL += "WHERE (tblContactGroup.ContactGroupCategory = @TableCategory) ";
if (SequenceNoStart != Constants.ZERO)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND tblContacts.ContactID < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND tblContacts.ContactID > " + SequenceNoStart.ToString() + " ";
}
if (SearchKey != string.Empty)
{
string SQLSearch = string.Empty;
if (SearchColumns.ContactCode)
{ if (SQLSearch == string.Empty) SQLSearch += "ContactCode LIKE @SearchKey "; else SQLSearch += "OR ContactCode LIKE @SearchKey "; }
if (SearchColumns.ContactName)
{ if (SQLSearch == string.Empty) SQLSearch += "ContactName LIKE @SearchKey "; else SQLSearch += "OR ContactName LIKE @SearchKey "; }
if (SearchColumns.RewardDetails)
{ if (SQLSearch == string.Empty) SQLSearch += "RewardCardNo LIKE @SearchKey "; else SQLSearch += "OR RewardCardNo LIKE @SearchKey "; }
if (SQLSearch != string.Empty) SQL += "AND (" + SQLSearch + ") ";
cmd.Parameters.AddWithValue("SearchKey", "%" + SearchKey + "%");
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ContactName") + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.Parameters.AddWithValue("TableCategory", ContactGroupCategory.TABLES.ToString("d"));
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public DataTable Suppliers(ContactColumns clsContactColumns, long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, ContactColumns SearchColumns, string SearchKey, Int32 Limit, bool HasCreditOnly, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
try
{
// enable this to include joining to table tblContactGroup
clsContactColumns.ContactGroupName = true;
string SQL = SQLSelect(clsContactColumns);
SQL += "WHERE (tblContactGroup.ContactGroupCategory = @SupplierCategory OR tblContactGroup.ContactGroupCategory = @BothCategory) ";
if (SequenceNoStart != Constants.ZERO)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
SQL += "AND tblContacts.ContactID < " + SequenceNoStart.ToString() + " ";
else
SQL += "AND tblContacts.ContactID > " + SequenceNoStart.ToString() + " ";
}
if (SearchKey != string.Empty)
{
string SQLSearch = string.Empty;
if (SearchColumns.ContactCode)
{ if (SQLSearch == string.Empty) SQLSearch += "ContactCode LIKE @SearchKey "; else SQLSearch += "OR ContactCode LIKE @SearchKey "; }
if (SearchColumns.ContactName)
{ if (SQLSearch == string.Empty) SQLSearch += "ContactName LIKE @SearchKey "; else SQLSearch += "OR ContactName LIKE @SearchKey "; }
if (SQLSearch != string.Empty) SQL += "AND (" + SQLSearch + ") ";
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending) SQL += "ASC ";
else SQL += "DESC ";
}
if (Limit != 0)
SQL += "LIMIT " + Limit + " ";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmSupplierCategory = new MySqlParameter("@SupplierCategory",MySqlDbType.Int16);
prmSupplierCategory.Value = ContactGroupCategory.SUPPLIER.ToString("d");
cmd.Parameters.Add(prmSupplierCategory);
MySqlParameter prmBothCategory = new MySqlParameter("@BothCategory",MySqlDbType.Int16);
prmBothCategory.Value = ContactGroupCategory.BOTH.ToString("d");
cmd.Parameters.Add(prmBothCategory);
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
protected void cboContact_SelectedIndexChanged(object sender, System.EventArgs e)
{
try
{
if (cboContact.SelectedItem.Value != Constants.ZERO_STRING)
{
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.CreditDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.CreditDetails = true;
Int64 iContactID = Int64.Parse(cboContact.SelectedItem.Value);
Contacts clsContact = new Contacts();
Data.ContactDetails clsContactDetails = clsContact.Details(iContactID);
Data.ContactDetails clsGuarantorDetails = clsContact.Details(clsContactDetails.CreditDetails.GuarantorID);
clsContact.CommitAndDispose();
txtGuarantorName.Text = clsGuarantorDetails.ContactName.ToString();
txtGuarantorName.ToolTip = clsGuarantorDetails.ContactID.ToString();
divContactInfo.Visible = true;
}
else
{
divContactInfo.Visible = false;
}
}
catch (Exception ex)
{
throw ex;
}
}
private void LoadList()
{
Contacts clsContact = new Contacts();
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.RewardDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.RewardDetails = true;
string SortField = "ContactID";
if (Request.QueryString["sortfield"]!=null)
{ SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID); }
SortOption sortoption = SortOption.Ascending;
if (Request.QueryString["sortoption"]!=null)
{ sortoption = (SortOption) Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true); }
string SearchKey = txtSearch.Text;
string ContactGroupCode = "";
ContactGroupCode = cboGroup.SelectedIndex == 0 ? "" : cboGroup.SelectedItem.Text;
DateTime dteBirthDateFrom = Constants.C_DATE_MIN_VALUE;
DateTime dteBirthDateTo = Constants.C_DATE_MIN_VALUE;
DateTime dteAnniversaryDateFrom = Constants.C_DATE_MIN_VALUE;
DateTime dteAnniversaryDateTo = Constants.C_DATE_MIN_VALUE;
dteBirthDateFrom = DateTime.TryParse(txtBirthStartDate.Text, out dteBirthDateFrom) ? dteBirthDateFrom : Constants.C_DATE_MIN_VALUE;
dteBirthDateTo = DateTime.TryParse(txtBirthEndDate.Text, out dteBirthDateTo) ? dteBirthDateTo : Constants.C_DATE_MIN_VALUE;
dteAnniversaryDateFrom = DateTime.TryParse(txtAnnivStartDate.Text, out dteAnniversaryDateFrom) ? dteAnniversaryDateFrom : Constants.C_DATE_MIN_VALUE;
dteAnniversaryDateTo = DateTime.TryParse(txtAnnivEndDate.Text, out dteAnniversaryDateTo) ? dteAnniversaryDateTo : Constants.C_DATE_MIN_VALUE;
//PageData.DataSource = clsContact.Customers(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, clsSearchColumns, SearchKey, 0, false, null, System.Data.SqlClient.SortOrder.Ascending).DefaultView;
PageData.DataSource = clsContact.ListAsDataTable(ContactGroupCategory.CUSTOMER, ContactCode: SearchKey, ContactName: SearchKey, ContactGroupCode: ContactGroupCode, SortField: SortField, SortOrder: sortoption, BirthDateFrom: dteBirthDateFrom.ToString("yyyy-MM-dd"), BirthDateTo: dteBirthDateTo.ToString("yyyy-MM-dd"), AnniversaryDateFrom: dteAnniversaryDateFrom.ToString("yyyy-MM-dd"), AnniversaryDateTo: dteAnniversaryDateTo.ToString("yyyy-MM-dd"), BirthMonth: cboBirthMonth.SelectedIndex, AnniversaryMonth: cboAnniversaryMonth.SelectedIndex).DefaultView;
//clsContact.CustomersDataTable(SearchKey, SortField: SortField, SortOrder: sortoption).DefaultView;
clsContact.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]) ;
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i=0; i < PageData.PageCount;i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(),iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{ cboCurrentPage.Items[i].Selected = true;}
else
{ cboCurrentPage.Items[i].Selected = false;}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
protected void lstItem_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Header)
{
LoadSortFieldOptions(e);
}
else if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
DataRowView dr = (DataRowView)e.Item.DataItem;
ImageButton imgItemEdit = (ImageButton)e.Item.FindControl("imgItemEdit");
ImageButton imgPrintBilling = (ImageButton)e.Item.FindControl("imgPrintBilling");
HtmlInputCheckBox chkList = (HtmlInputCheckBox)e.Item.FindControl("chkList");
chkList.Value = dr["ContactID"].ToString();
if (chkList.Value == "1" || chkList.Value == "2")
{
chkList.Attributes.Add("disabled", "false");
imgItemEdit.Enabled = false; imgItemEdit.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
imgPrintBilling.Enabled = false; ; imgPrintBilling.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
}
else
{
imgItemEdit.Enabled = cmdEdit.Visible; if (!imgItemEdit.Enabled) imgItemEdit.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
if (DateTime.Parse(dr["LastBillingDate"].ToString()) != DateTime.MinValue && DateTime.Parse(dr["LastBillingDate"].ToString()) != Constants.C_DATE_MIN_VALUE)
{
imgPrintBilling.Enabled = imgPrintBilling.Visible; if (!imgPrintBilling.Enabled) imgPrintBilling.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/print.gif";
imgPrintBilling.ToolTip = DateTime.Parse(dr["LastBillingDate"].ToString()).ToString("yyyy-MMM-dd");
}
else
{
imgPrintBilling.Enabled = false; ; imgPrintBilling.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
imgPrintBilling.ToolTip = Constants.C_DATE_MIN_VALUE_STRING;
}
}
HyperLink lnkContactCode = (HyperLink)e.Item.FindControl("lnkContactCode");
lnkContactCode.Text = dr["ContactCode"].ToString();
lnkContactCode.NavigateUrl = "Default.aspx?task=" + Common.Encrypt("details", Session.SessionID) + "&id=" + Common.Encrypt(chkList.Value, Session.SessionID);
HyperLink lnkContactName = (HyperLink)e.Item.FindControl("lnkContactName");
lnkContactName.Text = dr["ContactName"].ToString();
lnkContactName.NavigateUrl = "Default.aspx?task=" + Common.Encrypt("details", Session.SessionID) + "&id=" + Common.Encrypt(chkList.Value, Session.SessionID);
Label lblCreditType = (Label)e.Item.FindControl("lblCreditType");
lblCreditType.Text = dr["CardTypeCode"].ToString().ToString();
//Label lblCreditCardNo = (Label)e.Item.FindControl("lblCreditCardNo");
//lblCreditCardNo.Text = dr["CreditCardNo"].ToString();
//Label lblCreditCardStatus = (Label)e.Item.FindControl("lblCreditCardStatus");
//lblCreditCardStatus.Text = Enum.Parse(typeof(CreditCardStatus), dr["CreditCardStatus"].ToString()).ToString();
//Label lblCreditActive = (Label)e.Item.FindControl("lblCreditActive");
//lblCreditActive.Text = Data.Contacts.checkCreditActive((CreditCardStatus)Enum.Parse(typeof(CreditCardStatus), dr["CreditCardStatus"].ToString())) ? "Active" : "InActive";
//Label lblExpiryDate = (Label)e.Item.FindControl("lblExpiryDate");
//lblExpiryDate.Text = Convert.ToDateTime(dr["ExpiryDate"].ToString()).ToString("dd-MMM-yyyy");
//decimal decCreditLimit = Convert.ToDecimal(dr["CreditLimit"].ToString());
//decimal decCredit = Convert.ToDecimal(dr["Credit"].ToString());
//decimal decAvailableCredit = decCreditLimit - decCredit;
//Label lblCreditLimit = (Label)e.Item.FindControl("lblCreditLimit");
//lblCreditLimit.Text = decCreditLimit.ToString("#,##0.#");
//Label lblCredit = (Label)e.Item.FindControl("lblCredit");
//lblCredit.Text = decCredit.ToString("#,##0.#");
//Label lblAvailableCredit = (Label)e.Item.FindControl("lblAvailableCredit");
//lblAvailableCredit.Text = decAvailableCredit.ToString("#,##0.#");
//Label lblTotalPurchases = (Label)e.Item.FindControl("lblTotalPurchases");
//lblTotalPurchases.Text = Convert.ToDecimal(dr["TotalPurchases"].ToString()).ToString("#,##0.#");
//Label lblLastBillingDate = (Label)e.Item.FindControl("lblLastBillingDate");
//lblLastBillingDate.Text = Convert.ToDateTime(dr["LastBillingDate"].ToString()).ToString("dd-MMM-yyyy");
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.CreditDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.CreditDetails = true;
Int64 iGuarantorID = Int64.Parse(dr["ContactID"].ToString());
DataList lstItemCustomer = (DataList)e.Item.FindControl("lstItemCustomer");
if (iGuarantorID != 0)
{
Contacts clsContact = new Contacts();
System.Data.DataTable dt = clsContact.CustomersWithCredits(clsContactColumns, GuarantorID: iGuarantorID, SortField: "CreditCardNo");
clsContact.CommitAndDispose();
lstItemCustomer.DataSource = dt.DefaultView;
lstItemCustomer.DataBind();
lstItemCustomer.Visible = true;
}
else
{
lstItemCustomer.Visible = false;
}
}
}
private void LoadOptions()
{
DataClass clsDataClass = new DataClass();
Int32 iID = Convert.ToInt32(Common.Decrypt(Request.QueryString["id"],Session.SessionID));
ProductGroup clsProductGroup = new ProductGroup();
cboProductGroup.DataTextField = "ProductGroupName";
cboProductGroup.DataValueField = "ProductGroupID";
cboProductGroup.DataSource = clsProductGroup.ListAsDataTable(SortField:"ProductGroupName").DefaultView;
cboProductGroup.DataBind();
cboProductGroup.SelectedIndex = cboProductGroup.Items.Count - 1;
Data.Unit clsUnit = new Data.Unit(clsProductGroup.Connection, clsProductGroup.Transaction);
cboProductUnit.DataTextField = "UnitName";
cboProductUnit.DataValueField = "UnitID";
cboProductUnit.DataSource = clsUnit.ListAsDataTable(SortField:"UnitName").DefaultView;
cboProductUnit.DataBind();
cboProductUnit.SelectedIndex = cboProductUnit.Items.Count - 1;
clsUnit.CommitAndDispose();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactName = true;
ContactColumns clsContactSearchColumns = new ContactColumns();
Contacts clsContact = new Contacts(clsProductGroup.Connection, clsProductGroup.Transaction);
cboSupplier.DataTextField = "ContactName";
cboSupplier.DataValueField = "ContactID";
cboSupplier.DataSource = clsContact.Suppliers(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, clsContactSearchColumns, string.Empty, 0, false, "ContactName", System.Data.SqlClient.SortOrder.Ascending).DefaultView;
cboSupplier.DataBind();
cboSupplier.SelectedIndex = cboSupplier.Items.Count - 1;
ProductUnitsMatrix clsUnitMatrix = new ProductUnitsMatrix(clsProductGroup.Connection, clsProductGroup.Transaction);
ProductUnitsMatrixDetails clsUnitDetails = clsUnitMatrix.LastDetails(iID);
if (clsUnitDetails.BottomUnitName == null)
{
cboProductUnit.Enabled = true;
}
// Added July 9, 2010
Terminal clsTerminal = new Terminal(clsProductGroup.Connection, clsProductGroup.Transaction);
TerminalDetails clsTerminalDetails = clsTerminal.Details(Int32.Parse(Session["BranchID"].ToString()), Session["TerminalNo"].ToString());
txtWSPriceMarkUp.Text = clsTerminalDetails.WSPriceMarkUp.ToString();
txtMargin.Text = clsTerminalDetails.RETPriceMarkUp.ToString();
clsProductGroup.CommitAndDispose();
}
private void LoadContactData(System.Data.SqlClient.SortOrder SequenceSortOrder)
{
try
{
tblLayout.Controls.Clear();
Int64 intSequenceNoStart = 0;
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
try { intSequenceNoStart = long.Parse(cmdTableLeft.Tag.ToString()); }
catch { }
else
try { intSequenceNoStart = long.Parse(cmdTableRight.Tag.ToString()); }
catch { }
// Sep 24, 2014 put an override if cmdSubGroupLeft.Tag = 0
// always do an asceding coz its already the end.
if (intSequenceNoStart < Constants.C_RESTOPLUS_MAX_TABLES) intSequenceNoStart = 0; //reset to 0 if it's 1
if (intSequenceNoStart == 0) SequenceSortOrder = System.Data.SqlClient.SortOrder.Ascending;
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactCode = true;
clsContactColumns.LastCheckInDate = true;
ContactColumns clsSearchColumns = new ContactColumns();
Contacts clsContact = new Contacts();
System.Data.DataTable dtContact;
if (ContactGroupCategory == Data.ContactGroupCategory.TABLES)
dtContact = clsContact.Tables(clsContactColumns, intSequenceNoStart, SequenceSortOrder, clsSearchColumns, string.Empty, SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending ? Constants.C_RESTOPLUS_MAX_TABLES : Constants.C_RESTOPLUS_MAX_TABLES + 1, false, "SequenceNo", SequenceSortOrder);
else
dtContact = clsContact.Customers(clsContactColumns, intSequenceNoStart, SequenceSortOrder, clsSearchColumns, string.Empty, SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending ? Constants.C_RESTOPLUS_MAX_TABLES : Constants.C_RESTOPLUS_MAX_TABLES + 1, false, "SequenceNo", SequenceSortOrder);
// re-order the products by sequence no
if (dtContact.Rows.Count > 0)
{
System.Data.DataView dv = dtContact.DefaultView;
dv.Sort = "ContactCode";
dtContact = dv.ToTable();
}
int iRow = 0;
int iCol = 0;
int iCtr = 1;
Data.SalesTransactions clsSalesTransactions = new Data.SalesTransactions(clsContact.Connection, clsContact.Transaction);
Data.SalesTransactionDetails clsSalesTransactionDetails = new Data.SalesTransactionDetails();
Data.MergeTable clsMergeTable = new Data.MergeTable(clsContact.Connection, clsContact.Transaction);
Data.MergeTableDetails clsMergeTableDetails = new Data.MergeTableDetails();
if (dtContact.Rows.Count == 0)
{
cmdTableLeft.Tag = "0".ToString(); // reset the sequenceno to 0 if no record
cmdTableRight.Tag = "0".ToString(); // reset the sequenceno to 0 if no record
}
foreach (System.Data.DataRow dr in dtContact.Rows)
{
//if (iCol == 5) { iCol = 0; iRow++; }
if (iCtr > Constants.C_RESTOPLUS_MAX_TABLES) break;
if (iCtr == 1) cmdTableLeft.Tag = dr[Data.ContactColumnNames.SequenceNo].ToString();
if (iCtr >= 1 && dtContact.Rows.Count > Constants.C_RESTOPLUS_MAX_TABLES) cmdTableRight.Tag = dr[Data.ContactColumnNames.SequenceNo].ToString();
ProductButton cmdTable = new ProductButton();
cmdTable.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
cmdTable.BackColor = System.Drawing.Color.Red;
cmdTable.Dock = System.Windows.Forms.DockStyle.Fill;
cmdTable.FlatAppearance.BorderColor = System.Drawing.Color.Gold;
cmdTable.Font = new System.Drawing.Font("Tahoma", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
cmdTable.ForeColor = System.Drawing.SystemColors.ControlText;
cmdTable.GradientBottom = System.Drawing.Color.DarkRed;
cmdTable.GradientTop = System.Drawing.Color.Red;
cmdTable.ImageAlign = System.Drawing.ContentAlignment.TopCenter;
cmdTable.Location = new System.Drawing.Point(3, 3);
cmdTable.Size = new System.Drawing.Size(245, 90);
cmdTable.TabIndex = 118;
cmdTable.TextAlign = System.Drawing.ContentAlignment.BottomCenter;
cmdTable.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText;
cmdTable.UseVisualStyleBackColor = false;
cmdTable.Name = "cmdTable" + iCtr.ToString();
cmdTable.Text = dr[Data.ContactColumnNames.ContactCode].ToString();
cmdTable.Tag = dr[Data.ContactColumnNames.ContactID].ToString();
cmdTable.Click += new System.EventHandler(cmdTable_Click);
if (DateTime.Parse(dr[Data.ContactColumnNames.LastCheckInDate].ToString()) != Constants.C_DATE_MIN_VALUE)
{
TimeSpan iLapse = DateTime.Now - DateTime.Parse(dr[Data.ContactColumnNames.LastCheckInDate].ToString());
Label lblLastCheckInDate = new System.Windows.Forms.Label();
lblLastCheckInDate.AutoSize = true;
lblLastCheckInDate.BackColor = System.Drawing.Color.Transparent;
lblLastCheckInDate.Font = new System.Drawing.Font("Tahoma", 7.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
lblLastCheckInDate.ForeColor = System.Drawing.Color.Blue;
lblLastCheckInDate.Location = new System.Drawing.Point(5, 5);
lblLastCheckInDate.Name = "lblLastCheckInDate" + iCtr.ToString();
lblLastCheckInDate.TabIndex = 1;
lblLastCheckInDate.Text = "CheckIn: " + DateTime.Parse(dr[Data.ContactColumnNames.LastCheckInDate].ToString()).ToString("dd-MMM hh:mm tt") + " [" + iLapse.Hours.ToString("0#") + "hrs " + iLapse.Minutes.ToString("0#") + "mins]";
cmdTable.Controls.Add(lblLastCheckInDate);
}
string stTransactionNo = clsSalesTransactions.getSuspendedTransactionNo(long.Parse(dr[Data.ContactColumnNames.ContactID].ToString()), TerminalDetails.TerminalNo, TerminalDetails.BranchID);
if (stTransactionNo != string.Empty)
{
clsSalesTransactionDetails = clsSalesTransactions.Details(stTransactionNo, TerminalDetails.TerminalNo, TerminalDetails.BranchID);
cmdTable.Text = dr[Data.ContactColumnNames.ContactCode].ToString();
decimal decAmountDue = Convert.ToDecimal(clsSalesTransactionDetails.SubTotal + clsSalesTransactionDetails.Charge - clsSalesTransactionDetails.Discount);
cmdTable.Text += Environment.NewLine + Environment.NewLine + "Amount Due:" + decAmountDue.ToString("#,###.#0");
Label lblNoOfPax = new System.Windows.Forms.Label();
lblNoOfPax.AutoSize = true;
lblNoOfPax.BackColor = System.Drawing.Color.Transparent;
lblNoOfPax.Font = new System.Drawing.Font("Tahoma", 7.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
lblNoOfPax.ForeColor = System.Drawing.Color.White;
lblNoOfPax.Location = new System.Drawing.Point(210, 75);
lblNoOfPax.Name = "lblNoOfPax" + iCtr.ToString();
lblNoOfPax.TabIndex = 1;
lblNoOfPax.Text = clsSalesTransactionDetails.PaxNo.ToString() + "Pax";
cmdTable.Controls.Add(lblNoOfPax);
if (mboShowAvailableTableOnly)
{
cmdTable.BackColor = System.Drawing.Color.DarkGray;
cmdTable.GradientBottom = System.Drawing.Color.DarkGray;
cmdTable.GradientTop = System.Drawing.Color.LightGray;
cmdTable.Enabled = false;
}
else
{
cmdTable.BackColor = System.Drawing.Color.DarkBlue;
cmdTable.GradientBottom = System.Drawing.Color.DarkBlue;
cmdTable.GradientTop = System.Drawing.Color.LightBlue;
}
}
Label lblMerge = new System.Windows.Forms.Label();
lblMerge.AutoSize = true;
lblMerge.BackColor = System.Drawing.Color.Transparent;
lblMerge.Font = new System.Drawing.Font("Tahoma", 7.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
lblMerge.ForeColor = System.Drawing.Color.White;
lblMerge.Location = new System.Drawing.Point(5, 75);
lblMerge.Name = "lblMerge" + iCtr.ToString();
lblMerge.TabIndex = 1;
lblMerge.Text = "Merged";
lblMerge.Visible = false;
cmdTable.Controls.Add(lblMerge);
clsMergeTableDetails = clsMergeTable.Details(dr[Data.ContactColumnNames.ContactCode].ToString());
if (MainTableToMerge.ContactCode == dr[Data.ContactColumnNames.ContactCode].ToString())
{
cmdTable.BackColor = System.Drawing.Color.LightGreen;
cmdTable.GradientBottom = System.Drawing.Color.LightGreen;
cmdTable.GradientTop = System.Drawing.Color.DarkGreen;
cmdTable.Enabled = false;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Visible = true;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Text = "Merged to this table";
}
else if (clsMergeTableDetails.MainTableCode == dr[Data.ContactColumnNames.ContactCode].ToString())
{
cmdTable.BackColor = System.Drawing.Color.DarkGray;
cmdTable.GradientBottom = System.Drawing.Color.DarkGray;
cmdTable.GradientTop = System.Drawing.Color.LightGray;
cmdTable.Enabled = false;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Visible = true;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Text = "Merged - Main Table";
}
else if (clsMergeTableDetails.ChildTableCode == dr[Data.ContactColumnNames.ContactCode].ToString() &&
MainTableToMerge.ContactCode != clsMergeTableDetails.MainTableCode)
{
cmdTable.BackColor = System.Drawing.Color.DarkGray;
cmdTable.GradientBottom = System.Drawing.Color.DarkGray;
cmdTable.GradientTop = System.Drawing.Color.LightGray;
cmdTable.Enabled = false;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Visible = true;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Text = "Merged to " + clsMergeTableDetails.MainTableCode;
}
else if (clsMergeTableDetails.ChildTableCode == dr[Data.ContactColumnNames.ContactCode].ToString() &&
MainTableToMerge.ContactCode == clsMergeTableDetails.MainTableCode)
{
Data.ContactDetails clsContactDetails = clsContact.Details(long.Parse(cmdTable.Tag.ToString()));
mlstTables.Add(clsContactDetails);
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Visible = true;
cmdTable.Controls["lblMerge" + cmdTable.Name.Replace("cmdTable", "")].Text = "Merged to " + clsMergeTableDetails.MainTableCode;
cmdTable.GradientTop = System.Drawing.Color.Orange;
}
tblLayout.Controls.Add(cmdTable, iCol, iRow);
iCol++; iCtr++;
}
clsContact.CommitAndDispose();
}
catch (IndexOutOfRangeException){}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"RetailPlus",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
private void LoadList()
{
Contacts clsContact = new Contacts();
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.CreditDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
string SortField = "ContactCode";
if (Request.QueryString["sortfield"]!=null)
{ SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID); }
System.Data.SqlClient.SortOrder sortoption = System.Data.SqlClient.SortOrder.Ascending;
if (Request.QueryString["sortoption"]!=null)
{ sortoption = (System.Data.SqlClient.SortOrder) Enum.Parse(typeof(System.Data.SqlClient.SortOrder), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true); }
string SearchKey = string.Empty;
if (Request.QueryString["Search"]!=null)
{
SearchKey = Common.Decrypt((string)Request.QueryString["search"],Session.SessionID);
txtSearch.Text = SearchKey;
}
//string strSearch = txtSearch.Text.Trim();
//DateTime dteExpiryDateFrom = DateTime.TryParse(txtExpiryDateFrom.Text, out dteExpiryDateFrom) ? dteExpiryDateFrom : DateTime.MinValue;
//DateTime dteExpiryDateTo = DateTime.TryParse(txtExpiryDateTo.Text, out dteExpiryDateTo) ? dteExpiryDateTo : DateTime.MinValue;
//CreditCardStatus enumCreditCardStatus = (CreditCardStatus)Enum.Parse(typeof(CreditCardStatus), cboCreditCardStatus.SelectedItem.Value);
string strSearch = txtSearch.Text.Trim();
DateTime dteExpiryDateFrom = DateTime.TryParse(txtExpiryDateFrom.Text, out dteExpiryDateFrom) ? dteExpiryDateFrom : DateTime.MinValue;
DateTime dteExpiryDateTo = DateTime.TryParse(txtExpiryDateTo.Text, out dteExpiryDateTo) ? dteExpiryDateTo : DateTime.MinValue;
CreditCardStatus clsCreditCardStatus = (CreditCardStatus) Enum.Parse(typeof(CreditCardStatus), cboCreditCardStatus.SelectedItem.Value);
Int32 limit = 50;
limit = Int32.TryParse(Session["PageSize"].ToString(), out limit) ? limit * 2 : 50;
PageData.DataSource = clsContact.Guarantors(clsContactColumns, txtLastNameFrom.Text, txtLastNameTo.Text, strSearch, dteExpiryDateFrom, dteExpiryDateTo, clsCreditCardStatus, Int32.Parse(cboCreditType.SelectedItem.Value), SortField, sortoption, limit).DefaultView;
clsContact.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]) ;
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i=0; i < PageData.PageCount;i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(),iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{ cboCurrentPage.Items[i].Selected = true;}
else
{ cboCurrentPage.Items[i].Selected = false;}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
private void LoadOptions()
{
DataClass clsDataClass = new DataClass();
ProductGroup clsProductGroup = new ProductGroup();
cboProductGroup.DataTextField = "ProductGroupName";
cboProductGroup.DataValueField = "ProductGroupID";
cboProductGroup.DataSource = clsProductGroup.ListAsDataTable(SortField:"ProductGroupName").DefaultView;
cboProductGroup.DataBind();
cboProductGroup.SelectedIndex = cboProductGroup.Items.Count - 1;
Data.Unit clsUnit = new Data.Unit(clsProductGroup.Connection, clsProductGroup.Transaction);
cboProductUnit.DataTextField = "UnitName";
cboProductUnit.DataValueField = "UnitID";
cboProductUnit.DataSource = clsUnit.ListAsDataTable(SortField:"UnitName").DefaultView;
cboProductUnit.DataBind();
cboProductUnit.SelectedIndex = cboProductUnit.Items.Count - 1;
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactName = true;
ContactColumns clsContactSearchColumns = new ContactColumns();
Contacts clsContact = new Contacts(clsProductGroup.Connection , clsProductGroup.Transaction);
cboSupplier.DataTextField = "ContactName";
cboSupplier.DataValueField = "ContactID";
cboSupplier.DataSource = clsContact.Suppliers(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, clsContactSearchColumns, string.Empty, 0, false, "ContactName", System.Data.SqlClient.SortOrder.Ascending).DefaultView;
cboSupplier.DataBind();
cboSupplier.SelectedIndex = cboSupplier.Items.Count - 1;
//// Added July 9, 2010
// Remove Nov 22, 2011 - overwritten when a subgroup is selected
//Terminal clsTerminal = new Terminal(clsProductGroup.Connection, clsProductGroup.Transaction);
//TerminalDetails clsTerminalDetails = clsTerminal.Details(1);
//txtWSPriceMarkUp.Text = clsTerminalDetails.WSPriceMarkUp.ToString();
//txtMargin.Text = clsTerminalDetails.RETPriceMarkUp.ToString();
//txtVAT.Text = clsTerminalDetails.VAT.ToString("###.#0");
//txtEVAT.Text = clsTerminalDetails.EVAT.ToString("###.#0");
//txtLocalTax.Text = clsTerminalDetails.LocalTax.ToString("###.#0");
clsProductGroup.CommitAndDispose();
cboProductGroup_SelectedIndexChanged(null, null);
}
public new DataTable Suppliers(ContactColumns clsContactColumns, long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, ContactColumns SearchColumns, string SearchKey, Int32 Limit, bool HasCreditOnly, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
return null;
}
private void LoadList()
{
Contacts clsContact = new Contacts();
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.RewardDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.RewardDetails = true;
string SortField = "ContactID";
if (Request.QueryString["sortfield"]!=null)
{ SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID); }
System.Data.SqlClient.SortOrder sortoption = System.Data.SqlClient.SortOrder.Ascending;
if (Request.QueryString["sortoption"]!=null)
{ sortoption = (System.Data.SqlClient.SortOrder) Enum.Parse(typeof(System.Data.SqlClient.SortOrder), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true); }
string SearchKey = string.Empty;
if (Request.QueryString["Search"]!=null)
{
SearchKey = Common.Decrypt((string)Request.QueryString["search"],Session.SessionID);
txtSearch.Text = SearchKey;
}
string strSearch = txtSearch.Text.Trim();
Constants.DateSelectionString BirthDate = (Constants.DateSelectionString)Enum.Parse(typeof(Constants.DateSelectionString), cboBirthDate.SelectedItem.Value);
DateTime dteRewardExpiryDateFrom = DateTime.TryParse(txtRewardExpiryDateFrom.Text, out dteRewardExpiryDateFrom) ? dteRewardExpiryDateFrom : DateTime.MinValue;
DateTime dteRewardExpiryDateTo = DateTime.TryParse(txtRewardExpiryDateTo.Text, out dteRewardExpiryDateTo) ? dteRewardExpiryDateTo : DateTime.MinValue;
Int16 intRewardCardStatus = (cboRewardCardStatus.SelectedItem.Value == Constants.ALL) ? Int16.Parse("-1") : Int16.Parse(cboRewardCardStatus.SelectedItem.Value);
PageData.DataSource = clsContact.CustomersWithRewards(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, 0, strSearch, dteRewardExpiryDateFrom, dteRewardExpiryDateTo, BirthDate, intRewardCardStatus, SortField, sortoption).DefaultView;
clsContact.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]) ;
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i=0; i < PageData.PageCount;i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(),iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{ cboCurrentPage.Items[i].Selected = true;}
else
{ cboCurrentPage.Items[i].Selected = false;}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
private string SQLSelect(ContactColumns clsContactColumns)
{
string stSQL = "SELECT ";
if (clsContactColumns.ContactCode) stSQL += "SequenceNo, ";
if (clsContactColumns.ContactCode) stSQL+= "ContactCode, ";
if (clsContactColumns.ContactName) stSQL+= "ContactName, ";
if (clsContactColumns.ContactGroupID) stSQL+= "tblContacts.ContactGroupID, ";
if (clsContactColumns.ContactGroupName) stSQL += "tblContactGroup.ContactGroupName, ";
if (clsContactColumns.ModeOfTerms) stSQL+= "tblContacts.ModeOfTerms, ";
if (clsContactColumns.Terms) stSQL+= "tblContacts.Terms, ";
if (clsContactColumns.Address) stSQL+= "tblContacts.Address, ";
if (clsContactColumns.BusinessName) stSQL+= "tblContacts.BusinessName, ";
if (clsContactColumns.TelephoneNo) stSQL+= "tblContacts.TelephoneNo, ";
if (clsContactColumns.Remarks) stSQL+= "tblContacts.Remarks, ";
if (clsContactColumns.Debit) stSQL+= "tblContacts.Debit, ";
if (clsContactColumns.Credit) stSQL+= "tblContacts.Credit, ";
if (clsContactColumns.CreditLimit) stSQL+= "tblContacts.CreditLimit, ";
if (clsContactColumns.IsCreditAllowed) stSQL += "tblContacts.IsCreditAllowed, ";
if (clsContactColumns.DateCreated) stSQL+= "tblContacts.DateCreated, ";
if (clsContactColumns.Deleted) stSQL+= "tblContacts.Deleted, ";
if (clsContactColumns.DepartmentID) stSQL+= "tblContacts.DepartmentID, ";
if (clsContactColumns.DepartmentName) stSQL+= "tblDepartments.DepartmentName, ";
if (clsContactColumns.PositionID) stSQL+= "tblContacts.PositionID, ";
if (clsContactColumns.PositionName) stSQL+= "tblPositions.PositionName, ";
if (clsContactColumns.RewardDetails)
{
stSQL += "tblContactRewards.RewardCardNo, " +
"tblContactRewards.RewardActive, " +
"tblContactRewards.RewardPoints, " +
"tblContactRewards.RewardAwardDate, " +
"tblContactRewards.TotalPurchases, " +
"tblContactRewards.RedeemedPoints, " +
"tblContactRewards.RewardCardStatus, " +
"tblContactRewards.ExpiryDate, " +
"tblContactRewards.BirthDate,";
}
if (clsContactColumns.CreditDetails)
{
if (!clsContactColumns.Credit) stSQL += "tblContacts.Credit, ";
if (!clsContactColumns.CreditLimit) stSQL += "tblContacts.CreditLimit, ";
if (!clsContactColumns.IsCreditAllowed) stSQL += "tblContacts.IsCreditAllowed, ";
stSQL += "tblContactCreditCardInfo.GuarantorID, " +
"tblContactCreditCardInfo.CreditCardTypeID, " +
"tblContactCreditCardInfo.CreditCardNo, " +
"tblContactCreditCardInfo.CreditAwardDate, " +
"tblContactCreditCardInfo.TotalPurchases, " +
"tblContactCreditCardInfo.CreditPaid, " +
"tblContactCreditCardInfo.CreditCardStatus, " +
"tblContactCreditCardInfo.ExpiryDate, " +
"tblContactCreditCardInfo.EmbossedCardNo, " +
"tblContactCreditCardInfo.LastBillingDate, " +
"tblCardTypes.CardTypeCode, " +
"tblCardTypes.CardTypeName, ";
}
stSQL += "tblContacts.LastCheckInDate, ";
stSQL += "tblContacts.TINNo, ";
stSQL += "tblContacts.LTONo, ";
stSQL += "tblContacts.PriceLevel, ";
stSQL += "tblContacts.ContactID ";
stSQL += "FROM tblContacts ";
if (clsContactColumns.ContactGroupName)
stSQL += "INNER JOIN tblContactGroup ON tblContacts.ContactGroupID = tblContactGroup.ContactGroupID ";
if (clsContactColumns.DepartmentName)
stSQL += "INNER JOIN tblDepartments ON tblContacts.DepartmentID = tblDepartments.DepartmentID ";
if (clsContactColumns.PositionName)
stSQL += "INNER JOIN tblPositions ON tblContacts.PositionID = tblPositions.PositionID ";
if (clsContactColumns.RewardDetails)
stSQL += "INNER JOIN tblContactRewards ON tblContacts.ContactID = tblContactRewards.CustomerID ";
if (clsContactColumns.CreditDetails)
{
stSQL += "INNER JOIN tblContactCreditCardInfo ON tblContacts.ContactID = tblContactCreditCardInfo.CustomerID ";
stSQL += "INNER JOIN tblCardTypes ON tblCardTypes.CardTypeID = tblContactCreditCardInfo.CreditCardTypeID ";
}
return stSQL;
}
private void LoadList()
{
Contacts clsContact = new Contacts();
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
//clsContactColumns.ContactGroupID = true;
//clsContactColumns.ContactGroupName = true;
//clsContactColumns.ModeOfTerms = true;
//clsContactColumns.Terms = true;
//clsContactColumns.Address = true;
//clsContactColumns.BusinessName = true;
//clsContactColumns.TelephoneNo = true;
//clsContactColumns.Remarks = true;
//clsContactColumns.Debit = true;
//clsContactColumns.Credit = true;
//clsContactColumns.CreditLimit = true;
//clsContactColumns.IsCreditAllowed = true;
//clsContactColumns.DateCreated = true;
//clsContactColumns.Deleted = true;
//clsContactColumns.DepartmentID = true;
//clsContactColumns.DepartmentName = true;
//clsContactColumns.PositionID = true;
//clsContactColumns.PositionName = true;
clsContactColumns.RewardDetails = true;
//clsContactColumns.CreditDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.RewardDetails = true;
string SortField = "ContactID";
if (Request.QueryString["sortfield"]!=null)
{ SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID); }
SortOption sortoption = SortOption.Ascending;
if (Request.QueryString["sortoption"]!=null)
{ sortoption = (SortOption) Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true); }
string SearchKey = string.Empty;
if (Request.QueryString["Search"]!=null)
{
SearchKey = Common.Decrypt((string)Request.QueryString["search"],Session.SessionID);
}
PageData.DataSource = clsContact.Customers(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, clsSearchColumns, SearchKey, 0, false, null, System.Data.SqlClient.SortOrder.Ascending).DefaultView;
clsContact.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]) ;
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i=0; i < PageData.PageCount;i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(),iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{ cboCurrentPage.Items[i].Selected = true;}
else
{ cboCurrentPage.Items[i].Selected = false;}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
private void LoadMembers()
{
Contacts clsContact = new Contacts();
DataClass clsDataClass = new DataClass();
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.RewardDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.RewardDetails = true;
cboCustomer.DataTextField = "ContactName";
cboCustomer.DataValueField = "ContactID";
string SearchKey = "%" + txtCustomer.Text;
cboCustomer.DataSource = clsContact.Customers(clsContactColumns, 0, System.Data.SqlClient.SortOrder.Ascending, clsSearchColumns, SearchKey, 20, false, "ContactName", System.Data.SqlClient.SortOrder.Ascending).DefaultView;
cboCustomer.DataBind();
clsContact.CommitAndDispose();
if (cboCustomer.Items.Count == 0) cboCustomer.Items.Insert(0, new ListItem(Constants.PLEASE_SELECT, Constants.ZERO_STRING));
cboCustomer.SelectedIndex = 0;
}
protected void cboContact_SelectedIndexChanged(object sender, System.EventArgs e)
{
try
{
if (cboContact.SelectedItem.Value != Constants.ZERO_STRING)
{
ContactColumns clsContactColumns = new ContactColumns();
clsContactColumns.ContactID = true;
clsContactColumns.ContactCode = true;
clsContactColumns.ContactName = true;
clsContactColumns.CreditDetails = true;
ContactColumns clsSearchColumns = new ContactColumns();
clsSearchColumns.ContactCode = true;
clsSearchColumns.ContactName = true;
clsSearchColumns.CreditDetails = true;
Int64 iGuarantorID = Int64.Parse(cboContact.SelectedItem.Value);
Contacts clsContact = new Contacts();
Data.ContactDetails clsContactDetails = clsContact.Details(iGuarantorID);
System.Data.DataTable dt = clsContact.CustomersWithCredits(clsContactColumns, iGuarantorID, SortField: "CreditCardNo"); // "", dteExpiryDateFrom, dteExpiryDateTo, enumCreditCardStatus, Int32.Parse(cboCreditType.SelectedItem.Value),
clsContact.CommitAndDispose();
lstItemCustomer.DataSource = dt.DefaultView;
lstItemCustomer.DataBind();
txtCreditCardTypeCode.Text = clsContactDetails.CreditDetails.CardTypeDetails.CardTypeCode;
txtCreditCardTypeCode.ToolTip = clsContactDetails.CreditDetails.CardTypeDetails.CardTypeID.ToString();
divGuarantorInfo.Visible = true;
}
else
{
divGuarantorInfo.Visible = false;
}
}
catch (Exception ex)
{
throw ex;
}
}
