static void Main(string[] arugs)
{
Context context = new Context();
//Console.WriteLine( context.Model.Magazines.GetCount());
//Console.WriteLine(context.Model.Users.GetCount());
//Console.WriteLine(context.Model.Status.GetCount());
//Console.WriteLine(context.Model.Regions.GetCount());
//Console.WriteLine(context.Model.Notes.GetCount());
//Console.WriteLine(context.Model.Clients.GetCount());
var filter = new CFilters();
filter.UserID = 1;
filter.MagazineID = 1;
filter.SearchText = "Thai";
Console.WriteLine(context.Model.Clients.GetCount(filter));
//context.Model.Clients.LoadListItems(50, 1,filter);
Console.ReadLine();
}
// string CurrentFilter , int resultPerPage=50,int pageIndex=1,int UserID=1,int MagazineID=1,string SearchText=""
public void OnGet(string currentFilter, int pageIndex, int pageSize = 50, string seachText = "")
{
//create filter
var filter = new CFilters();
filter.UserID = UserID;
filter.MagazineID = MagazineID;
if (seachText != null)
{
pageIndex = 1;
filter.SearchText = seachText;
}
else
{
filter.SearchText = CurrentFilter;
}
Count = _context.Model.Clients.GetCount(filter);
//the number of results in one page, when in last page, it won't be 50;
PageIndex = pageIndex;
TotalPage = Count / pageSize;
if (pageIndex == 1)
{
PreviousPage = false;
}
else
{
PreviousPage = true;
}
if (pageIndex == TotalPage)
{
NextPage = false;
}
else
{
NextPage = true;
}
//loading
_context.Model.Clients.LoadListItems(pageSize, pageIndex, filter);
PageResultCount = _context.Model.Clients.Count;
Clients = _context.Model.Clients;
}
public static int GetCount(this List <client_cln_list> list, CFilters Filter)
{
string Query = BuildClientListQuery(Filter);
Query = Data.CreateRecordCountQuery(Query);
string response = Connection.SendStream(Query);
int count = Convert.ToInt32(Data.GetData(response, typeof(string), "RecordCount"));
return(count);
string createSelect(string tableName)
{
string query = $"SELECT * FROM {tableName};";
return(query);
}
string BuildClientListQuery(CFilters filter)
{
string sQuery;
string sOperator = " WHERE ";
// Select correct stored query
if (filter.UserID == 0 && filter.MagazineID == 0)
{
sQuery = "SELECT * FROM client_cln_List0";
}
else if (filter.UserID > 0 && filter.MagazineID == 0)
{
sQuery = "SELECT * FROM client_cln_List1 WHERE id_user_usr_cln={1}";
sQuery = sQuery.Replace("{1}", filter.UserID.ToString());
}
else if (filter.UserID == 0 && filter.MagazineID > 0)
{
sQuery = "SELECT * FROM client_cln_List2 WHERE id_magazine_cln_mag={2}";
sQuery = sQuery.Replace("{2}", filter.MagazineID.ToString());
}
else // (UserID > 0 && MagazineID > 0)
{
sQuery = "SELECT * FROM client_cln_List3 WHERE (id_user_usr_cln={1} AND id_magazine_cln_mag={2})";
sQuery = sQuery.Replace("{1}", filter.UserID.ToString());
sQuery = sQuery.Replace("{2}", filter.MagazineID.ToString());
}
// Check if WHERE has been used yet
if (sQuery.IndexOf("WHERE") > -1)
{
sOperator = " AND ";
}
// Add Region and Status criteria
if (filter.RegionID == 0 && filter.StatusID == 0)
{
//
}
else if (filter.RegionID > 0 && filter.StatusID == 0)
{
sQuery += sOperator + "id_region_cln={1}";
sQuery = sQuery.Replace("{1}", filter.RegionID.ToString());
}
else if (filter.RegionID == 0 && filter.StatusID > 0)
{
sQuery += sOperator + "id_status_cln={2}";
sQuery = sQuery.Replace("{2}", filter.StatusID.ToString());
}
else // (RegionID > 0 && StatusID > 0)
{
sQuery += sOperator + "(id_region_cln={1} AND id_status_cln={2})";
sQuery = sQuery.Replace("{1}", filter.RegionID.ToString());
sQuery = sQuery.Replace("{2}", filter.StatusID.ToString());
}
// Check if WHERE has been used yet
if (sQuery.IndexOf("WHERE") > -1)
{
sOperator = " AND ";
}
// Add text search criteria
if (filter.SearchText != "")
{
string sFind = filter.SearchText;
if (filter.SearchKeywordInsteadOfName)
{
sQuery += sOperator + $"InStr([tags_cln],\"{sFind}\")>0";
}
else
{
sQuery += sOperator + $"InStr([name_cln],\"{sFind}\")>0";
}
}
sQuery += ";";
return(sQuery);
}
}
public static void LoadListItems(this List <client_cln_list> list, int ResultsPerPage, int PageNumber, CFilters Filter)
{
string[] Results;
char Separator = Convert.ToChar(21);
list.Clear();
string Query = BuildClientListQuery(Filter);
string Response;
if (ResultsPerPage > 0 && PageNumber > 0)
{
Query = Data.CreatePagedQuery(Query, PageNumber, ResultsPerPage);
}
else
{
Query = Data.CreateStandardQuery(Query);
}
Response = Connection.SendStream(Query);
Results = Response.Split(Separator);
int resultNumber = Results.Length - 1;
for (int count = 1; count <= resultNumber; count++)
{
var listObj = new client_cln_list();
EntityMapping(Results[count], listObj);
list.Add(listObj);
}
var a = list.Count;
string BuildClientListQuery(CFilters filter)
{
string sQuery;
string sOperator = " WHERE ";
// Select correct stored query
if (filter.UserID == 0 && filter.MagazineID == 0)
{
sQuery = "SELECT * FROM client_cln_List0";
}
else if (filter.UserID > 0 && filter.MagazineID == 0)
{
sQuery = "SELECT * FROM client_cln_List1 WHERE id_user_usr_cln={1}";
sQuery = sQuery.Replace("{1}", filter.UserID.ToString());
}
else if (filter.UserID == 0 && filter.MagazineID > 0)
{
sQuery = "SELECT * FROM client_cln_List2 WHERE id_magazine_cln_mag={2}";
sQuery = sQuery.Replace("{2}", filter.MagazineID.ToString());
}
else // (UserID > 0 && MagazineID > 0)
{
sQuery = "SELECT * FROM client_cln_List3 WHERE (id_user_usr_cln={1} AND id_magazine_cln_mag={2})";
sQuery = sQuery.Replace("{1}", filter.UserID.ToString());
sQuery = sQuery.Replace("{2}", filter.MagazineID.ToString());
}
// Check if WHERE has been used yet
if (sQuery.IndexOf("WHERE") > -1)
{
sOperator = " AND ";
}
// Add Region and Status criteria
if (filter.RegionID == 0 && filter.StatusID == 0)
{
//
}
else if (filter.RegionID > 0 && filter.StatusID == 0)
{
sQuery += sOperator + "id_region_cln={1}";
sQuery = sQuery.Replace("{1}", filter.RegionID.ToString());
}
else if (filter.RegionID == 0 && filter.StatusID > 0)
{
sQuery += sOperator + "id_status_cln={2}";
sQuery = sQuery.Replace("{2}", filter.StatusID.ToString());
}
else // (RegionID > 0 && StatusID > 0)
{
sQuery += sOperator + "(id_region_cln={1} AND id_status_cln={2})";
sQuery = sQuery.Replace("{1}", filter.RegionID.ToString());
sQuery = sQuery.Replace("{2}", filter.StatusID.ToString());
}
// Check if WHERE has been used yet
if (sQuery.IndexOf("WHERE") > -1)
{
sOperator = " AND ";
}
// Add text search criteria
if (filter.SearchText != "")
{
string sFind = filter.SearchText;
if (filter.SearchKeywordInsteadOfName)
{
sQuery += sOperator + $"InStr([tags_cln],\"{sFind}\")>0";
}
else
{
sQuery += sOperator + $"InStr([name_cln],\"{sFind}\")>0";
}
}
sQuery += ";";
return(sQuery);
}
}