public MySqlDataReader Search(string SearchKey, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
try
{
string SQL = "SELECT " +
"VariationID, " +
"VariationCode, " +
"VariationType " +
"FROM tblVariations " +
"WHERE VariationType LIKE @SearchKey " +
"ORDER BY " + SortField;
SQL += SortOrder == System.Data.SqlClient.SortOrder.Ascending ? "ASC " : "DESC ";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey", MySqlDbType.String);
prmSearchKey.Value = "%" + SearchKey + "%";
cmd.Parameters.Add(prmSearchKey);
return(base.ExecuteReader(cmd));
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListAsDataTableSimple(long SequenceNoStart, System.Data.SqlClient.SortOrder SequenceSortOrder, string SortField = "SequenceNo", SortOption SortOrder = SortOption.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelectSimple() + "WHERE 1=1 ";
if (SequenceNoStart != 0)
{
if (SequenceSortOrder == System.Data.SqlClient.SortOrder.Descending)
{
SQL += "AND SequenceNo <= " + SequenceNoStart.ToString() + " ";
}
else
{
SQL += "AND SequenceNo >= " + SequenceNoStart.ToString() + " ";
}
}
SQL += "ORDER BY " + SortField + " ";
SQL += SortOrder == SortOption.Ascending ? "ASC " : "DESC ";
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);
}
}
private void LoadSortFieldOptions(DataListItemEventArgs e)
{
string stParam = null;
System.Data.SqlClient.SortOrder sortoption = System.Data.SqlClient.SortOrder.Ascending;
if (Request.QueryString["sortoption"] != null)
{
sortoption = (System.Data.SqlClient.SortOrder)Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true);
}
if (sortoption == System.Data.SqlClient.SortOrder.Ascending)
{
stParam += "?sortoption=" + Common.Encrypt(SortOption.Desscending.ToString("G"), Session.SessionID);
}
else if (sortoption == System.Data.SqlClient.SortOrder.Descending)
{
stParam += "?sortoption=" + Common.Encrypt(SortOption.Ascending.ToString("G"), Session.SessionID);
}
System.Collections.Specialized.NameValueCollection querystrings = Request.QueryString;;
foreach (string querystring in querystrings.AllKeys)
{
if (querystring.ToLower() != "sortfield" && querystring.ToLower() != "sortoption")
{
stParam += "&" + querystring + "=" + querystrings[querystring].ToString();
}
}
HyperLink SortByVariationCode = (HyperLink)e.Item.FindControl("SortByVariationCode");
HyperLink SortByVariationType = (HyperLink)e.Item.FindControl("SortByVariationType");
SortByVariationCode.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("VariationCode", Session.SessionID);
SortByVariationType.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("VariationType", Session.SessionID);
}
private void LoadSortFieldOptions(DataListItemEventArgs e)
{
string stParam = null;
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);
}
if (sortoption == System.Data.SqlClient.SortOrder.Ascending)
{
stParam += "?sortoption=" + Common.Encrypt(System.Data.SqlClient.SortOrder.Descending.ToString("G"), Session.SessionID);
}
else if (sortoption == System.Data.SqlClient.SortOrder.Descending)
{
stParam += "?sortoption=" + Common.Encrypt(System.Data.SqlClient.SortOrder.Ascending.ToString("G"), Session.SessionID);
}
System.Collections.Specialized.NameValueCollection querystrings = Request.QueryString;;
foreach (string querystring in querystrings.AllKeys)
{
if (querystring.ToLower() != "sortfield" && querystring.ToLower() != "sortoption")
{
stParam += "&" + querystring + "=" + querystrings[querystring].ToString();
}
}
HyperLink SortByContactCode = (HyperLink)e.Item.FindControl("SortByContactCode");
HyperLink SortByContactName = (HyperLink)e.Item.FindControl("SortByContactName");
HyperLink SortByCreditType = (HyperLink)e.Item.FindControl("SortByCreditType");
HyperLink SortByCreditCardNo = (HyperLink)e.Item.FindControl("SortByCreditCardNo");
HyperLink SortByCreditCardStatus = (HyperLink)e.Item.FindControl("SortByCreditCardStatus");
HyperLink SortByExpiryDate = (HyperLink)e.Item.FindControl("SortByExpiryDate");
HyperLink SortByCreditLimit = (HyperLink)e.Item.FindControl("SortByCreditLimit");
HyperLink SortByCredit = (HyperLink)e.Item.FindControl("SortByCredit");
HyperLink SortByTotalPurchases = (HyperLink)e.Item.FindControl("SortByTotalPurchases");
HyperLink SortByLastBillingDate = (HyperLink)e.Item.FindControl("SortByLastBillingDate");
SortByContactCode.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("ContactCode", Session.SessionID);
SortByContactName.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("ContactName", Session.SessionID);
SortByCreditType.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("CreditType", Session.SessionID);
SortByCreditCardNo.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("CreditCardNo", Session.SessionID);
SortByCreditCardStatus.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("CreditCardStatus", Session.SessionID);
SortByExpiryDate.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("ExpiryDate", Session.SessionID);
SortByCreditLimit.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("CreditLimit", Session.SessionID);
SortByCredit.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("Credit", Session.SessionID);
SortByTotalPurchases.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("TotalPurchases", Session.SessionID);
SortByLastBillingDate.NavigateUrl = "Default.aspx" + stParam + "&sortfield=" + Common.Encrypt("LastBillingDate", Session.SessionID);
}
//public MySqlDataReader List(string SortField, SortOption SortOrder)
//{
// try
// {
// if (SortField == null || SortField == string.Empty) SortField = "VariationID";
// string SQL = SQLSelect() + "ORDER BY " + SortField;
// if (SortOrder == SortOption.Ascending)
// SQL += " ASC";
// else
// SQL += " DESC";
// MySqlCommand cmd = new MySqlCommand();
// cmd.CommandType = System.Data.CommandType.Text;
// cmd.CommandText = SQL;
// return base.ExecuteReader(cmd);
// }
// catch (Exception ex)
// {
// throw base.ThrowException(ex);
// }
//}
public DataTable ListAsDataTable(string SortField = "VariationCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
if (SortField == null || SortField == string.Empty)
{
SortField = "VariationID";
}
SortField = string.IsNullOrEmpty(SortField) ? "VariationCode" : SortField;
string SQL = SQLSelect() + "ORDER BY " + SortField + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Descending ? "DESC " : "ASC ";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
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);
}
public IQueryable <TEntity> Find <TSortKey>(Expression <Func <TEntity, bool> > exp, Expression <Func <TEntity, TSortKey> > orderExp, System.Data.SqlClient.SortOrder order, int index, int limit)
{
var query = GetTypeCollection <TEntity>(MappingCfg.ToDatabase, MappingCfg.ToCollection)
.AsQueryable()
.Where(exp);
if (order == System.Data.SqlClient.SortOrder.Ascending)
{
return(query.OrderBy(orderExp)
.Skip(index * limit)
.Take(limit));
}
else if (order == System.Data.SqlClient.SortOrder.Descending)
{
return(query.OrderByDescending(orderExp)
.Skip(index * limit)
.Take(limit));
}
else
{
return(query.OrderByDescending(orderExp)
.Skip(index * limit)
.Take(limit));
}
}
public System.Data.DataTable ListAsDataTable(DepositColumns clsDepositColumns, DepositDetails clsSearchKey, string SortField = "DepositID", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsDepositColumns) + "WHERE 1=1 ";
if (clsSearchKey.BranchDetails.BranchID != 0)
{
SQL += "AND tblDeposit.BranchID = @BranchID ";
cmd.Parameters.AddWithValue("BranchID", clsSearchKey.BranchDetails.BranchID);
}
if (clsSearchKey.CashierID != 0)
{
SQL += "AND tblDeposit.CashierID = @CashierID ";
cmd.Parameters.AddWithValue("CashierID", clsSearchKey.CashierID);
}
if (clsSearchKey.ContactID != 0)
{
SQL += "AND tblDeposit.ContactID = @ContactID ";
cmd.Parameters.AddWithValue("ContactID", clsSearchKey.ContactID);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.DateCreated >= @StartTransactionDate ";
cmd.Parameters.AddWithValue("StartTransactionDate", clsSearchKey.StartTransactionDate);
}
if (clsSearchKey.StartTransactionDate != DateTime.MinValue)
{
SQL += "AND tblDeposit.DateCreated <= @EndTransactionDate ";
cmd.Parameters.AddWithValue("EndTransactionDate", clsSearchKey.EndTransactionDate);
}
SQL += "ORDER BY " + SortField + " ";
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);
}
}
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;
clsSearchColumns.CreditDetails = 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();
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);
PageData.DataSource = clsContact.CustomersWithCredits(clsContactColumns, LastNameFrom: txtLastNameFrom.Text, LastNameTo: txtLastNameTo.Text, CustomerCode_CreditCardNo: strSearch, CreditCardExpiryDateFrom: dteExpiryDateFrom, CreditCardExpiryDateTo: dteExpiryDateTo, CreditCardStatus: enumCreditCardStatus, CreditCardTypeID: Int32.Parse(cboCreditType.SelectedItem.Value), CheckCustomersGuarantor: true, 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;
}
public System.Data.DataTable ListAsDataTable(Int64 GuarantorID = 0, Int64 ContactID = 0, Int16 CreditCardTypeID = 0, CreditType CreditType = CreditType.Both, DateTime?BillingDate = null, bool CheckIsBillPrinted = false, bool IsBillPrinted = false, string SortField = "CreditBillHeaderID", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "";
SQL += "WHERE 1=1 ";
if (CheckIsBillPrinted)
{
SQL += "AND IsBillPrinted = @IsBillPrinted ";
cmd.Parameters.AddWithValue("IsBillPrinted", IsBillPrinted ? 1 : 0);
}
if (GuarantorID != 0)
{
SQL += "AND CBH.GuarantorID = @GuarantorID ";
cmd.Parameters.AddWithValue("GuarantorID", GuarantorID);
}
if (ContactID != 0)
{
SQL += "AND CBH.ContactID = @ContactID ";
cmd.Parameters.AddWithValue("ContactID", ContactID);
}
if (CreditType == CreditType.Group)
{
SQL += "AND CBH.GuarantorID <> 0 ";
}
else if (CreditType == CreditType.Individual)
{
SQL += "AND CBH.GuarantorID = 0 ";
}
if (CreditCardTypeID != 0)
{
SQL += "AND CBL.CreditCardTypeID = @CreditCardTypeID ";
cmd.Parameters.AddWithValue("CreditCardTypeID", CreditCardTypeID);
}
if (BillingDate.GetValueOrDefault(Constants.C_DATE_MIN_VALUE) == Constants.C_DATE_MIN_VALUE)
{
SQL += "AND CBL.BillingDate = (SELECT MAX(BillingDate) FROM tblCreditBills) ";
}
else
{
SQL += "AND CBL.BillingDate = @BillingDate ";
cmd.Parameters.AddWithValue("BillingDate", BillingDate.GetValueOrDefault(Constants.C_DATE_MIN_VALUE));
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "CreditBillHeaderID") + " ";
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 System.Data.DataTable ListAsDataTable(string Salutation = "", string SortField = "SalutationCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
try
{
string SQL = "CALL procSalutationSelect(@Salutation, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@Salutation", Salutation);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case System.Data.SqlClient.SortOrder.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case System.Data.SqlClient.SortOrder.Descending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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 System.Data.DataTable ListAsDataTable(BranchInventoryColumns clsBranchInventoryColumns, BranchInventoryColumns SearchColumns, int BranchID, long ProductID, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
string SQL = SQLSelect(clsBranchInventoryColumns);
if (BranchID != 0)
{
SQL += "AND tblBranchInventory.BranchID = @BranchID ";
}
if (ProductID != 0)
{
SQL += "AND tblBranchInventory.ProductID = @ProductID ";
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending)
{
SQL += "ASC ";
}
else
{
SQL += "DESC ";
}
}
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
if (BranchID != 0)
{
cmd.Parameters.AddWithValue("@BranchID", BranchID);
}
if (ProductID != 0)
{
cmd.Parameters.AddWithValue("@ProductID", ProductID);
}
System.Data.DataTable dt = new System.Data.DataTable("tblBranch");
base.MySqlDataAdapterFill(cmd, dt);
return(dt);
}
public List <CardTypeDetails> ListUnPrintedCreditCardTypes(CreditType CreditType = CreditType.Both, string SortField = "CardTypeCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelectCreditCardTypes() + "";
SQL += "WHERE CBH.IsBillPrinted = 0 ";
if (CreditType == CreditType.Group)
{
SQL += "AND CBH.GuarantorID <> 0 ";
}
else if (CreditType == CreditType.Individual)
{
SQL += "AND CBH.GuarantorID = 0 ";
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "CardTypeCode") + " ";
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);
List <CardTypeDetails> lstRetValue = new List <CardTypeDetails>();
foreach (DataRow dr in dt.Rows)
{
lstRetValue.Add(new Data.CardTypeDetails()
{
CardTypeID = Int16.Parse(dr["CreditCardTypeID"].ToString()),
CardTypeCode = dr["CardTypeCode"].ToString(),
CardTypeName = dr["CardTypeName"].ToString(),
CreditFinanceCharge = decimal.Parse(dr["CreditFinanceCharge"].ToString()),
CreditLatePenaltyCharge = decimal.Parse(dr["CreditLatePenaltyCharge"].ToString()),
CreditMinimumAmountDue = decimal.Parse(dr["CreditMinimumAmountDue"].ToString()),
CreditMinimumPercentageDue = decimal.Parse(dr["CreditMinimumPercentageDue"].ToString()),
CreditFinanceCharge15th = decimal.Parse(dr["CreditFinanceCharge15th"].ToString()),
CreditLatePenaltyCharge15th = decimal.Parse(dr["CreditLatePenaltyCharge15th"].ToString()),
CreditMinimumAmountDue15th = decimal.Parse(dr["CreditMinimumAmountDue15th"].ToString()),
CreditMinimumPercentageDue15th = decimal.Parse(dr["CreditMinimumPercentageDue15th"].ToString()),
CreditPurcStartDateToProcess = DateTime.Parse(dr["CreditPurcStartDateToProcess"].ToString()),
CreditPurcEndDateToProcess = DateTime.Parse(dr["CreditPurcEndDateToProcess"].ToString()),
CreditCutOffDate = DateTime.Parse(dr["CreditCutOffDate"].ToString()),
CreditCardType = (CreditCardTypes)Enum.Parse(typeof(CreditCardTypes), dr["CreditCardType"].ToString()),
WithGuarantor = bool.Parse(dr["WithGuarantor"].ToString()),
BillingDate = DateTime.Parse(dr["BillingDate"].ToString())
});
}
return(lstRetValue);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public List <BillingDetails> List(Int64 GuarantorID = 0, Int64 ContactID = 0, Int16 CreditCardTypeID = 0, CreditType CreditType = CreditType.Both, DateTime?BillingDate = null, bool CheckIsBillPrinted = false, bool IsBillPrinted = false, string SortField = "ContactID", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
System.Data.DataTable dt = ListAsDataTable(GuarantorID, ContactID, CreditCardTypeID, CreditType, BillingDate, CheckIsBillPrinted, IsBillPrinted, SortField, SortOrder, limit);
List <BillingDetails> lstRetValue = new List <BillingDetails>();
foreach (DataRow dr in dt.Rows)
{
lstRetValue.Add(setDetails(dr));
}
return(lstRetValue);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListBillingDateAsDataTable(CreditType CreditType, Int64 CustomerID = 0, DateTime?BillingDate = null, Int16 CreditCardTypeID = 0, string SortField = "BillingDate", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Descending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "SELECT DISTINCT DATE_FORMAT(CBH.BillingDate, '%Y-%m-%d') BillingDate, CBH.BillingFile FROM tblCreditBillHeader CBH " +
"INNER JOIN tblCreditBills CB ON CB.CreditBillID = CBH.CreditBillID WHERE 1=1 ";
if (CreditType == CreditType.Individual)
{
SQL += "AND CB.WithGuarantor = 0 ";
}
if (CreditType == CreditType.Group)
{
SQL += "AND CB.WithGuarantor <> 0 ";
}
if (CustomerID != 0)
{
SQL += "AND CBH.ContactID = @CustomerID ";
cmd.Parameters.AddWithValue("CustomerID", CustomerID);
}
if (BillingDate.GetValueOrDefault(Constants.C_DATE_MIN_VALUE) != Constants.C_DATE_MIN_VALUE)
{
SQL += "AND CBH.BillingDate = @BillingDate ";
cmd.Parameters.AddWithValue("BillingDate", BillingDate);
}
if (CreditCardTypeID != 0)
{
SQL += "AND CB.CreditCardTypeID = @CreditCardTypeID ";
cmd.Parameters.AddWithValue("CreditCardTypeID", CreditCardTypeID);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "BillingDate") + " ";
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 System.Data.DataTable ListDetailsAsDataTable(Int64 CreditBillHeaderID = 0, string SortField = "TransactionDate", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "SELECT CreditBillHeaderID ,TransactionDate ,Description ,Amount FROM tblCreditBillDetail WHERE 1=1 ";
if (CreditBillHeaderID != 0)
{
SQL += "AND CreditBillHeaderID = @CreditBillHeaderID ";
cmd.Parameters.AddWithValue("CreditBillHeaderID", CreditBillHeaderID);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "TransactionDate") + " ";
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 System.Data.DataTable ClosingInventoryGroupCodes(string ReferenceNo = "", string SortField = "ProductGroupCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
string SQL = "CALL procInventoryProductGroupCode(@ReferenceNo, @SortField, @SortOrder)";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@ReferenceNo", ReferenceNo);
cmd.Parameters.AddWithValue("@SortField", SortField);
switch (SortOrder)
{
case System.Data.SqlClient.SortOrder.Ascending:
cmd.Parameters.AddWithValue("@SortOrder", "ASC");
break;
case System.Data.SqlClient.SortOrder.Descending:
cmd.Parameters.AddWithValue("@SortOrder", "DESC");
break;
}
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);
}
public System.Data.DataTable ListAsDataTable(int BranchID, long ProductID, string SortField, System.Data.SqlClient.SortOrder SortOrder)
{
string SQL = "SELECT a.BranchID, BranchCode, Quantity " +
"FROM tblBranchInventory a " +
" INNER JOIN tblBranch b ON a.BranchID = b.BranchID WHERE 1=1 ";
if (BranchID != 0)
{
SQL += "AND a.BranchID = @BranchID ";
}
if (ProductID != 0)
{
SQL += "AND ProductID = @ProductID ";
}
if (SortField != string.Empty && SortField != null)
{
SQL += "ORDER BY " + SortField + " ";
if (SortOrder != System.Data.SqlClient.SortOrder.Descending)
{
SQL += "ASC ";
}
else
{
SQL += "DESC ";
}
}
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
if (BranchID != 0)
{
cmd.Parameters.AddWithValue("@BranchID", BranchID);
}
if (ProductID != 0)
{
cmd.Parameters.AddWithValue("@ProductID", ProductID);
}
System.Data.DataTable dt = new System.Data.DataTable("tblBranch");
base.MySqlDataAdapterFill(cmd, dt);
return(dt);
}
private void LoadList()
{
Variation clsVariation = new Variation();
DataClass clsDataClass = new DataClass();
string SortField = "VariationType";
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(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true);
}
if (Request.QueryString["Search"] == null)
{
PageData.DataSource = clsVariation.ListAsDataTable(SortField, sortoption).DefaultView;
}
else
{
string SearchKey = Common.Decrypt((string)Request.QueryString["search"], Session.SessionID);
PageData.DataSource = clsDataClass.DataReaderToDataTable(clsVariation.Search(SearchKey, SortField, sortoption)).DefaultView;
}
clsVariation.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 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 = "SequenceNo";
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++; }
#region Sequence # Counter
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();
}
#endregion
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 = "";
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()), mclsTerminalDetails.TerminalNo, mclsTerminalDetails.BranchID);
if (stTransactionNo != string.Empty)
{
clsSalesTransactionDetails = clsSalesTransactions.Details(stTransactionNo, mclsTerminalDetails.TerminalNo, mclsTerminalDetails.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 = "";
lblNoOfPax.Text = clsSalesTransactionDetails.PaxNo.ToString() + "Pax";
cmdTable.Controls.Add(lblNoOfPax);
// Jan 31, 2015 : Lemu
// Added disabling of Suspended Transactions.
// Put the SuspendedOpen Status to in LoadTransaction
if (mboShowAvailableTableOnly || clsSalesTransactionDetails.TransactionStatus == TransactionStatus.SuspendedOpen)
{
cmdTable.BackColor = System.Drawing.Color.DarkGray;
cmdTable.GradientBottom = System.Drawing.Color.DarkGray;
cmdTable.GradientTop = System.Drawing.Color.DarkGray;
cmdTable.Enabled = false;
if (clsSalesTransactionDetails.TransactionStatus == TransactionStatus.SuspendedOpen)
{
cmdTable.BackColor = System.Drawing.Color.Gray;
cmdTable.GradientBottom = System.Drawing.Color.Gray;
cmdTable.GradientTop = System.Drawing.Color.Gray;
cmdTable.Enabled = true;
cmdTable.Text += Environment.NewLine + "(open in other terminal)";
}
}
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 (clsMergeTableDetails.ChildTableCode == dr[Data.ContactColumnNames.ContactCode].ToString() &&
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 to " + clsMergeTableDetails.MainTableCode;
}
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.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;
}
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);
}
public System.Data.DataTable ListAsDataTable(Int64 ProductID, string SortField = "VariationCode", System.Data.SqlClient.SortOrder SortOrder = System.Data.SqlClient.SortOrder.Ascending)
{
try
{
string SQL = "SELECT a.ProductID, " +
"a.VariationID, " +
"b.VariationCode, " +
"b.VariationType " +
"FROM tblProductVariations a " +
"LEFT JOIN tblVariations b ON a.VariationID = b.VariationID " +
"WHERE ProductID = @ProductID ";
SortField = string.IsNullOrEmpty(SortField) ? "VariationCode" : SortField;
SQL += "ORDER BY " + SortField + " ";
SQL += SortOrder == System.Data.SqlClient.SortOrder.Descending ? "DESC " : "ASC ";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@ProductID", ProductID);
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);
}
}
