コード例 #1
0
        public static DataTablesResponseModel <BankAccountModel> GetBankAccountDataTable(int pageSize, int position, string search, int sortColumnId, string sortDirection)
        {
            List <BankAccountModel> items = new List <BankAccountModel>();

            var bankAccountList = GetAccounts();

            int totalCount = bankAccountList.Count();

            var query = bankAccountList.Select(x => x.Account).AsQueryable();

            if (search != String.Empty)
            {
                query = query.Where(x => x.Name.Contains(search) || x.IBAN.Contains(search) || x.Code.Contains(search));
            }

            string sortColumn = "Code";

            switch (sortColumnId)
            {
            case 1:
                sortColumn = "Name";
                break;

            case 2:
                sortColumn = "Description";
                break;

            case 3:
                sortColumn = "IBAN";
                break;

            default:
                sortColumn = "Code";
                break;
            }

            string direction = (sortDirection == "DESC") ? "DESC" : "ASC";

            // Apply sorting:
            //dynamic from System.Linq.Dynamic in Common Exstensions
            query = query.OrderBy(sortColumn + " " + sortDirection);

            //paging
            query = query.Skip(position).Take(pageSize);

            DataTablesResponseModel <BankAccountModel> model = new DataTablesResponseModel <BankAccountModel>(query.ToList(), totalCount, totalCount, 0);

            return(model);
        }
コード例 #2
0
        public static DataTablesResponseModel <DataTableCvCompetenceModel> GetCvCompetenceDataTable(int pageSize, int position, string search, int sortColumnId, string sortDirection)
        {
            List <DataTableCvCompetenceModel> items = new List <DataTableCvCompetenceModel>();

            // Set the total count
            // so GridView knows how many pages to create
            int totalCount = 0;


            using (var db = new Entities())
            {
                totalCount = db.CvCompetences.Count();

                var query = db.CvCompetences.Where(x => x.Id != null);
                if (search != String.Empty)
                {
                    query = query.Where(x => x.Name.Contains(search) || x.Description.Contains(search));
                }

                string sortColumn = "Code";

                switch (sortColumnId)
                {
                case 1:
                    sortColumn = "Name";
                    break;

                case 2:
                    sortColumn = "Description";
                    break;

                default:

                    break;
                }

                string direction = (sortDirection == "DESC") ? "DESC" : "ASC";
                // Apply sorting:
                //dynamic from System.Linq.Dynamic in Common Exstensions
                query = query.OrderBy(sortColumn + " " + sortDirection);

                //paging
                query = query.Skip(position).Take(pageSize);

                int actionCounter = 1;
                foreach (var item in query.ToList())
                {
                    items.Add(new DataTableCvCompetenceModel
                    {
                        Code        = item.Code,
                        Name        = item.Name,
                        Description = item.Description,
                        //Action = "/Admin/CvCompetenceEdit/" + item.Id.ToString()
                        Action = item.Id.ToString()
                    });
                    actionCounter++;
                }
            }


            DataTablesResponseModel <DataTableCvCompetenceModel> model = new DataTablesResponseModel <DataTableCvCompetenceModel>(items, totalCount, totalCount, 0);

            return(model);
        }
コード例 #3
0
        /// <summary>
        /// The call for this function is generated in order to simplify the filtering below.  This is not usually necessary for IQueryable&lt;object&gt;
        /// However, LLBLGen has problems with IQuerable when the type is casted to object, so the proper typed call is generated above and this generic is called.
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="dataTableInit"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private static JsonResult Query <TEntity>(DataTablesInitializationModel <TEntity> dataTableInit, DataTablesRequestModel model)
        {
            // Get source model used to create the view.
            var source = dataTableInit.Source;

            // get IQueryable type of object passed in, this makes the work below generalized for any type

            /*var source2 = source as IQueryable<PatientEntity>;
             * if(source2 != null)
             * {
             *  source2 = source2.Where(x => Convert.ToString(x.BirthDate, new DateTimeFormatInfo(){ShortDatePattern = }).Contains("fe"));
             *
             *  var result = source2.ToList();
             * }
             */

            // start with the source, then add on each filter
            var count        = source.Count();
            var displayCount = count;

            if (!String.IsNullOrEmpty(model.sSearch))
            {
                var llblGenProProvider = source.Provider as LLBLGenProProvider;
                if (llblGenProProvider != null)
                {
                    var dateConverter = new FunctionMapping(typeof(DateTime), "ToString", 0,
                                                            @"CONVERT(nvarchar(2), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 6, 2))) + '/' + CONVERT(nvarchar(2), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 9, 2))) + '/' + CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 1, 4))) + ' ' + CASE WHEN CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) > 12 THEN CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) - 12) ELSE CONVERT(nvarchar(4), CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2))) END +':' +SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 15, 2) + ':' + SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 18, 2) + CASE WHEN CONVERT(int, SUBSTRING(CONVERT(nvarchar(23), {0}, 121), 12, 2)) > 11 THEN ' PM' ELSE ' AM' END");
                    //var indexOf = new FunctionMapping(typeof(string), "IndexOf", 1, "PATINDEX('%{1}%', {0}) - 1");
                    //var indexOf2 = new FunctionMapping(typeof(string), "IndexOf", 2, "PATINDEX('%{1}%', {0}) - 1");
                    if ((llblGenProProvider).CustomFunctionMappings == null)
                    {
                        (llblGenProProvider).CustomFunctionMappings = new FunctionMappingStore();
                    }
                    (llblGenProProvider).CustomFunctionMappings.Add(dateConverter);
                    //(llblGenProProvider).CustomFunctionMappings.Add(indexOf);
                    //(llblGenProProvider).CustomFunctionMappings.Add(indexOf2);
                    // ^--- Doesn't work for no apparent reason but would make the Search() function a lot simpler.
                }

                // search all specified columns for the specified string
                source = source.Search(dataTableInit.Columns, model.sSearch, !(source.Provider is LLBLGenProProvider));

                displayCount = source.Count();
            }

            // sort the result based on the model, this calls OrderBy on multiple columns sequentially
            if (model.Sorts.Any())
            {
                source = source.Sort(dataTableInit.Columns, model.Sorts);
            }

            // filter page based on start and length, this is like the SQL LIMIT directive
            if (model.iDisplayLength > 0)
            {
                source = source.Skip(model.iDisplayStart).Take(model.iDisplayLength);
            }

            // now build the response
            var response = new DataTablesResponseModel
            {
                sEcho                = model.sEcho.Value,
                iTotalRecords        = count,
                iTotalDisplayRecords = displayCount
            };

            // loop through each item and output JSON
            foreach (TEntity entity in source.ToList())
            {
                var row = response.NewRow();
                row.SetRowId(string.Empty + entity.GetHashCode());
                foreach (var column in dataTableInit.Columns)
                {
                    // if the column specifies a formatting function
                    if (column.Format != null)
                    {
                        var method = column.Format.Compile();
                        row.PushColumn(method.Invoke(entity).ToString());
                    }
                    else
                    {
                        var value = DataBinder.Eval(entity, column.ColumnName);
                        row.PushColumn(value != null ? value.ToString() : "");
                    }
                }
            }

            return(new JsonResult
            {
                Data = response,
                ContentType = null,
                ContentEncoding = null,
                JsonRequestBehavior = JsonRequestBehavior.AllowGet
            });
        }