//public MySqlDataReader Search(string SearchKey, string SortField, SortOption SortOrder)
//{
// try
// {
// string SQL = SQLSelect() + "(TransactionNo LIKE @SearchKey " +
// "OR StockTypeCode LIKE @SearchKey " +
// "OR a.Remarks LIKE @SearchKey) " +
// "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;
// 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 MySqlDataReader List(string SortField, SortOption SortOrder)
//{
// try
// {
// 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 System.Data.DataTable ListAsDataTableActiveInactive(TransactionListFilterType clsTransactionListFilterType, string SearchKey = null, string SortField = "StockID", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE 1=1 ";
if (clsTransactionListFilterType == TransactionListFilterType.ShowActiveOnly)
{
SQL += "AND a.Active = 1 ";
}
else if (clsTransactionListFilterType == TransactionListFilterType.ShowInactiveOnly)
{
SQL += "AND a.Active = 0 ";
}
if (!string.IsNullOrEmpty(SearchKey))
{
SQL += "AND (TransactionNo LIKE @SearchKey " +
"OR StockTypeCode LIKE @SearchKey " +
"OR a.Remarks LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%");
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "StockID") + " ";
SQL += SortOrder == SortOption.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 MySqlDataReader Search(string SearchKey, string SortField, SortOption SortOrder)
//{
// try
// {
// string SQL = SQLSelect() + "(TransactionNo LIKE @SearchKey " +
// "OR StockTypeCode LIKE @SearchKey " +
// "OR a.Remarks LIKE @SearchKey) " +
// "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;
// 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 MySqlDataReader List(string SortField, SortOption SortOrder)
//{
// try
// {
// 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 System.Data.DataTable ListAsDataTableActiveInactive(TransactionListFilterType clsTransactionListFilterType, string SearchKey = null, string SortField = "StockID", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE 1=1 ";
if (clsTransactionListFilterType == TransactionListFilterType.ShowActiveOnly) SQL += "AND a.Active = 1 ";
else if (clsTransactionListFilterType == TransactionListFilterType.ShowInactiveOnly) SQL += "AND a.Active = 0 ";
if (!string.IsNullOrEmpty(SearchKey))
{
SQL += "AND (TransactionNo LIKE @SearchKey " +
"OR StockTypeCode LIKE @SearchKey " +
"OR a.Remarks LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%");
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "StockID") + " ";
SQL += SortOrder == SortOption.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()
{
Stock clsStock = new Stock();
DataClass clsDataClass = new DataClass();
string SortField = "StockID";
if (Request.QueryString["sortfield"] != null)
{
SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID);
}
SortOption sortoption = SortOption.Desscending;
if (Request.QueryString["sortoption"] != null)
{
sortoption = (SortOption)Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true);
}
TransactionListFilterType clsTransactionListFilterType = TransactionListFilterType.ShowActiveAndInactive;
if (rdoShowActiveOnly.Checked == true)
{
clsTransactionListFilterType = TransactionListFilterType.ShowActiveOnly;
}
if (rdoShowInactiveOnly.Checked == true)
{
clsTransactionListFilterType = TransactionListFilterType.ShowInactiveOnly;
}
string SearchKey = "";
if (Request.QueryString["Search"] != null)
{
SearchKey = Common.Decrypt((string)Request.QueryString["search"], Session.SessionID);
}
PageData.DataSource = clsStock.ListAsDataTableActiveInactive(clsTransactionListFilterType, SearchKey, SortField, sortoption).DefaultView;
clsStock.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;
}
