public SearchResult<ConsumableUsage> GetByCriteria(int page, int limit, DateTime? start=null, DateTime? end=null)
        {
            var searchInfo = new ConsumableUsageSearchInfo
                {
                    Start = start,
                    End = end,
                    PageIndex = page,
                    PageSize = limit
                };

            return service.Search(searchInfo);
        }
 void OnRefreshList(object param)
 {
     var searchInfo = new ConsumableUsageSearchInfo
         {
             Start = startDate,
             End = endDate,
             PageIndex = pageIndex,
             PageSize = pageSize
         };
     var result = usageService.Search(searchInfo);
     UsageList = new ObservableCollection<ConsumableUsage>(result.Items);
     TotalRecords =  result.Total;
     DeleteCommand.EntityId = Guid.Empty;
 }
        public void Can_SearchByDate()
        {
            Models.Data.ConsumableUsage usage1, usage2;

            using (var conn = factory.OpenDbConnection())
            {
                var id1 = Guid.NewGuid();
                var id2 = Guid.NewGuid();

                usage1 = new Models.Data.ConsumableUsage
                {
                    Id = id1,
                    Date = DateTime.Today.AddDays(-1),
                    Total = 10000
                };
                usage2 = new Models.Data.ConsumableUsage()
                {
                    Id = id2,
                    Date = DateTime.Today,
                    Total = 5000
                };

                using (var tx = conn.OpenTransaction())
                {
                    conn.InsertParam(usage1);
                    conn.InsertParam(usage2);
                    tx.Commit();
                }
            }

            var searchInfo = new ConsumableUsageSearchInfo
                {
                    Start = DateTime.Today.AddDays(-2),
                    End = DateTime.Today.AddDays(1),
                    PageIndex = 1,
                    PageSize = 1
                };

            var result = service.Search(searchInfo);

            Assert.Equal(2, result.Total);
            Assert.Equal(usage2.Id, result.Items[0].Id);
        }
        /// <summary>
        /// Searches the usage based on search criteria.
        /// </summary>
        /// <param name="searchInfo">The search criteria.</param>
        /// <returns>collection of usage data and total number.</returns>
        public SearchResult<ConsumableUsage> Search(ConsumableUsageSearchInfo searchInfo)
        {
            int start = (searchInfo.PageIndex - 1)*searchInfo.PageSize;

            var result = new SearchResult<ConsumableUsage>();

            using (var conn = factory.OpenDbConnection())
            {

                var ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Models.Data.ConsumableUsage>();

                if (searchInfo.Start.HasValue && searchInfo.End.HasValue)
                {
                    ev = ev.Where(e => e.Date >= searchInfo.Start.Value.Date && e.Date <= searchInfo.End.Value.Date);
                }

                ev.OrderByDescending(e => e.Date).Limit(start, searchInfo.PageSize);

                var usages = conn.Select(ev);
                foreach (var usage in usages)
                {
                    var list = conn.Where<Models.Data.ConsumableUsageDetail>(new { UsageId = usage.Id });

                    var usageModel = MapUsageToModel(usage, list);
                    result.Items.Add(usageModel);
                }

                if (searchInfo.Start.HasValue && searchInfo.End.HasValue)
                {
                    result.Total =(int) conn.Count<Models.Data.ConsumableUsage>(
                        e => e.Date >= searchInfo.Start.Value.Date && e.Date <= searchInfo.End.Value.Date);
                }
                else
                {
                    result.Total = (int)  conn.Count<Models.Data.ConsumableUsage>();
                }
            }

            return result;
        }