Exemplo n.º 1
1
        public JsonResult ListTasks([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
        {
            var data = LiveWorkspace.Instance.GetAllTasksByProfile(GetUser.User1());

            var dataOrdenada = data.OrderBy(o => o.DataFim.Ticks);

            var paged = dataOrdenada.Skip(requestModel.Start).Take(requestModel.Length);

            var sortedColumns = requestModel.Columns.GetSortedColumns();
            foreach (var column in sortedColumns)
            {
                if (requestModel.Draw > 1)
                {
                    if (column.Orderable && column.IsOrdered)
                    {

                        if (column.SortDirection == DataTables.Mvc.Column.OrderDirection.Ascendant)
                            paged = paged.OrderBy(o => o.GetType()
                                .GetProperty(column.Data)
                                .GetValue(o, null));
                        else
                            paged = paged.OrderByDescending(o => o.GetType()
                            .GetProperty(column.Data)
                            .GetValue(o, null));

                    }
                }
            }

            var resp = new DataTablesResponse(
                requestModel.Draw,
                paged,
                dataOrdenada.Count(),
                data.Count);

            return Json(resp, JsonRequestBehavior.AllowGet);
        }
        public JsonResult GetCampaigns(
            [ModelBinder(typeof (GetCampaignsDataTablesBinder))] GetCampaignsDataTablesRequest request)
        {
            IEnumerable<CampaignItemViewModel> campaignItemViewModels;
            int campaignsTotal;
            int campaignsFilteredTotal;

            using (var connection = new SqlConnection(_shellSettings.DataConnectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "GetCampaigns";

                        var currentDateParameter = new SqlParameter("@CurrentDate", SqlDbType.DateTime)
                        {
                            Value = DateTime.UtcNow
                        };
                        command.Parameters.Add(currentDateParameter);

                        if (request.FilterCurrencyId.HasValue)
                        {
                            var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
                            {
                                Value = request.FilterCurrencyId.Value
                            };
                            command.Parameters.Add(currencyIdParameter);
                        }

                        if (!string.IsNullOrWhiteSpace(request.Search.Value))
                        {
                            var filterParameter = new SqlParameter("@Filter", SqlDbType.NVarChar, 4000)
                            {
                                Value = request.Search.Value
                            };
                            command.Parameters.Add(filterParameter);
                        }

                        var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
                        {
                            Value = _cultureUsed
                        };
                        command.Parameters.Add(cultureParameter);

                        if (request.Columns.GetSortedColumns().Any())
                        {
                            var sortColumn = request.Columns.GetSortedColumns().First();
                            var sortColumnParameter = new SqlParameter("@SortColumn", SqlDbType.NVarChar, 100)
                            {
                                Value = sortColumn.Data
                            };
                            command.Parameters.Add(sortColumnParameter);

                            var sortDirection = sortColumn.SortDirection == Column.OrderDirection.Ascendant
                                ? "ASC"
                                : "DESC";
                            var sortDirectionParameter = new SqlParameter("@SortDirection", SqlDbType.NVarChar, 50)
                            {
                                Value = sortDirection
                            };
                            command.Parameters.Add(sortDirectionParameter);
                        }

                        var skipParameter = new SqlParameter("@Skip", SqlDbType.Int)
                        {
                            Value = request.Start
                        };
                        command.Parameters.Add(skipParameter);
                        var takeParameter = new SqlParameter("@Take", SqlDbType.Int)
                        {
                            Value = request.Length
                        };
                        command.Parameters.Add(takeParameter);

                        IEnumerable<CampaignItem> campaignItems;

                        using (var reader = command.ExecuteReader())
                        {
                            campaignItems = GetCampaignItemsFrom(reader);
                        }

                        campaignItemViewModels = ConvertToCampaignItemViewModels(campaignItems);
                    }

                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "GetCampaignsCount";

                        var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
                        {
                            Value = _cultureUsed
                        };
                        command.Parameters.Add(cultureParameter);

                        if (request.FilterCurrencyId.HasValue)
                        {
                            var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
                            {
                                Value = request.FilterCurrencyId.Value
                            };
                            command.Parameters.Add(currencyIdParameter);
                        }

                        campaignsTotal = (int) command.ExecuteScalar();
                    }

                    if (!string.IsNullOrWhiteSpace(request.Search.Value))
                    {
                        using (var command = connection.CreateCommand())
                        {
                            command.Transaction = transaction;
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandText = "GetCampaignsCount";

                            var cultureParameter = new SqlParameter("@Culture", SqlDbType.NVarChar, 50)
                            {
                                Value = _cultureUsed
                            };
                            command.Parameters.Add(cultureParameter);

                            var filterParameter = new SqlParameter("@Filter", SqlDbType.NVarChar, 4000)
                            {
                                Value = request.Search.Value
                            };
                            command.Parameters.Add(filterParameter);

                            if (request.FilterCurrencyId.HasValue)
                            {
                                var currencyIdParameter = new SqlParameter("@CurrencyId", SqlDbType.Int)
                                {
                                    Value = request.FilterCurrencyId.Value
                                };
                                command.Parameters.Add(currencyIdParameter);
                            }

                            campaignsFilteredTotal = (int) command.ExecuteScalar();
                        }
                    }
                    else
                    {
                        campaignsFilteredTotal = campaignsTotal;
                    }

                    transaction.Commit();
                }
            }

            var dataTableResponse = new DataTablesResponse(
                request.Draw,
                campaignItemViewModels,
                campaignsFilteredTotal,
                campaignsTotal);

            return Json(dataTableResponse);
        }