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; }