public System.Data.DataTable ListAsDataTable(AccessGroupTypes clsAccessGroupTypes = AccessGroupTypes.All, string SearchKey = "", Int32 limit = 0, Int32 UserGroupID = 0, string SortField = "Name", SortOption SortOrder = SortOption.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsAccessGroupTypes) + "WHERE deleted = '0' ";
if (clsAccessGroupTypes == AccessGroupTypes.Waiters || clsAccessGroupTypes == AccessGroupTypes.Bagger)
{
SQL += "AND c.GroupName IN ('Waiters','Bagger') ";
cmd.Parameters.AddWithValue("@GroupName", clsAccessGroupTypes.ToString("G"));
}
else if (clsAccessGroupTypes != AccessGroupTypes.All)
{
SQL += "AND c.GroupName = @GroupName ";
cmd.Parameters.AddWithValue("@GroupName", clsAccessGroupTypes.ToString("G"));
}
if (UserGroupID != 0)
{
SQL += "AND b.GroupID = @GroupID ";
cmd.Parameters.AddWithValue("@GroupID", UserGroupID);
}
if (!string.IsNullOrEmpty(SearchKey))
{
SQL += "AND (UserName LIKE @SearchKey " +
"OR Name LIKE @SearchKey " +
"OR Address1 LIKE @SearchKey " +
"OR CountryName LIKE @SearchKey " +
"OR GroupName LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", SearchKey);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "Name") + " ";
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);
if (clsAccessGroupTypes == AccessGroupTypes.Waiters)
{
dt = new System.Data.DataTable("tblWaiters");
}
else
{
dt = new System.Data.DataTable("tblAccessUser");
}
base.MySqlDataAdapterFill(cmd, dt);
return(dt);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private string SQLSelect(AccessGroupTypes clsAccessGroupTypes = AccessGroupTypes.All)
{
string stSQL = string.Empty;
if (clsAccessGroupTypes == AccessGroupTypes.Waiters)
{
stSQL = "SELECT " +
"a.UID as WaiterID, " +
"UserName as WaiterCode, " +
"Name as WaiterName, " +
"a.CreatedOn, " +
"a.LastModified " +
"FROM sysAccessUsers a " +
"INNER JOIN sysAccessUserDetails b ON a.UID = b.UID " +
"INNER JOIN sysAccessGroups c ON b.GroupID = c.GroupID " +
"LEFT OUTER JOIN tblCountry d ON b.CountryID = d.CountryID ";
}
else
{
stSQL = "SELECT " +
"a.UID, " +
"UserName, " +
"Password, " +
"DateCreated, " +
"Deleted, " +
"Name, " +
"Address1, " +
"Address2, " +
"City, " +
"State, " +
"b.CountryID, " +
"d.CountryName, " +
"OfficePhone, " +
"DirectPhone, " +
"HomePhone, " +
"FaxPhone, " +
"MobilePhone, " +
"EmailAddress, " +
"b.GroupID, " +
"GroupName, " +
"PageSize, " +
"a.CreatedOn, " +
"a.LastModified " +
"FROM sysAccessUsers a " +
"INNER JOIN sysAccessUserDetails b ON a.UID = b.UID " +
"INNER JOIN sysAccessGroups c ON b.GroupID = c.GroupID " +
"LEFT OUTER JOIN tblCountry d ON b.CountryID = d.CountryID ";
}
return(stSQL);
}
public System.Data.DataTable ListAsDataTable(AccessGroupTypes clsAccessGroupTypes = AccessGroupTypes.All, string SearchKey = "", Int32 limit = 0, Int32 UserGroupID = 0, string SortField = "Name", SortOption SortOrder = SortOption.Ascending)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect(clsAccessGroupTypes) + "WHERE deleted = '0' ";
if (clsAccessGroupTypes == AccessGroupTypes.Waiters || clsAccessGroupTypes == AccessGroupTypes.Bagger)
{
SQL += "AND c.GroupName IN ('Waiters','Bagger') ";
cmd.Parameters.AddWithValue("@GroupName", clsAccessGroupTypes.ToString("G"));
}
else if (clsAccessGroupTypes != AccessGroupTypes.All)
{
SQL += "AND c.GroupName = @GroupName ";
cmd.Parameters.AddWithValue("@GroupName", clsAccessGroupTypes.ToString("G"));
}
if (UserGroupID != 0)
{
SQL += "AND b.GroupID = @GroupID ";
cmd.Parameters.AddWithValue("@GroupID", UserGroupID);
}
if (!string.IsNullOrEmpty(SearchKey))
{
SQL += "AND (UserName LIKE @SearchKey " +
"OR Name LIKE @SearchKey " +
"OR Address1 LIKE @SearchKey " +
"OR CountryName LIKE @SearchKey " +
"OR GroupName LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", SearchKey);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "Name") + " ";
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);
if (clsAccessGroupTypes == AccessGroupTypes.Waiters)
dt = new System.Data.DataTable("tblWaiters");
else
dt = new System.Data.DataTable("tblAccessUser");
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private string SQLSelect(AccessGroupTypes clsAccessGroupTypes = AccessGroupTypes.All)
{
string stSQL = string.Empty;
if (clsAccessGroupTypes == AccessGroupTypes.Waiters)
{
stSQL = "SELECT " +
"a.UID as WaiterID, " +
"UserName as WaiterCode, " +
"Name as WaiterName, " +
"a.CreatedOn, " +
"a.LastModified " +
"FROM sysAccessUsers a " +
"INNER JOIN sysAccessUserDetails b ON a.UID = b.UID " +
"INNER JOIN sysAccessGroups c ON b.GroupID = c.GroupID " +
"LEFT OUTER JOIN tblCountry d ON b.CountryID = d.CountryID ";
}
else
{
stSQL = "SELECT " +
"a.UID, " +
"UserName, " +
"Password, " +
"DateCreated, " +
"Deleted, " +
"Name, " +
"Address1, " +
"Address2, " +
"City, " +
"State, " +
"b.CountryID, " +
"d.CountryName, " +
"OfficePhone, " +
"DirectPhone, " +
"HomePhone, " +
"FaxPhone, " +
"MobilePhone, " +
"EmailAddress, " +
"b.GroupID, " +
"GroupName, " +
"PageSize, " +
"a.CreatedOn, " +
"a.LastModified " +
"FROM sysAccessUsers a " +
"INNER JOIN sysAccessUserDetails b ON a.UID = b.UID " +
"INNER JOIN sysAccessGroups c ON b.GroupID = c.GroupID " +
"LEFT OUTER JOIN tblCountry d ON b.CountryID = d.CountryID ";
}
return stSQL;
}
