Ejemplo n.º 1
0
        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;
        }
Ejemplo n.º 3
0
        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);
            }
        }
Ejemplo n.º 4
0
        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);
            }
        }