context: using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Linq;
  using System.Web;
  using CmsData;
  using CmsData.Finance;
  using CmsData.View;
  using MoreLinq;
  using UtilityExtensions;
  
  namespace CmsWeb.Models
  {
      public class TransactionsModel
      {
          private int? _count;
          private IQueryable<TransactionList> _transactions;
          public int nameid;
  
          public TransactionsModel(int? tranid, string reference = "", string desc = "")
              : this()
          {
              name = tranid.ToString();
              if (!tranid.HasValue)
                  GoerId = null;
  
              if (!string.IsNullOrWhiteSpace(reference))
              {
                  name = reference;
              }
              if (!string.IsNullOrWhiteSpace(desc))
              {
                  description = desc;
              }
          }
  
          public TransactionsModel()
          {
              Pager = new PagerModel2(Count);
              Pager.Sort = "Date";
              Pager.Direction = "desc";
              finance = HttpContext.Current.User.IsInRole("Finance");
              admin = HttpContext.Current.User.IsInRole("Admin") || HttpContext.Current.User.IsInRole("ManageTransactions");
          }
  
          public string description { get; set; }
          public string name { get; set; }
          public string Submit { get; set; }
          public decimal? gtamount { get; set; }
          public decimal? ltamount { get; set; }
          public DateTime? startdt { get; set; }
          public DateTime? enddt { get; set; }
          public bool testtransactions { get; set; }
          public bool apprtransactions { get; set; }
          public bool nocoupons { get; set; }
          public string batchref { get; set; }
          public bool usebatchdates { get; set; }
          public PagerModel2 Pager { get; set; }
          public bool finance { get; set; }
          public bool admin { get; set; }
          public int? GoerId { get; set; } // for mission trip supporters of this goer
          public int? SenderId { get; set; } // for mission trip goers of this supporter
  
          public int Count()
          {
              if (!_count.HasValue)
                  _count = FetchTransactions().Count();
              return _count.Value;
          }
  
          public IEnumerable<TransactionList> Transactions()
          {
              var q0 = ApplySort();
              q0 = q0.Skip(Pager.StartRow).Take(Pager.PageSize);
              return q0;
          }
  
          public TotalTransaction TotalTransactions()
          {
              var q0 = FetchTransactions();
              var q = from t in q0
                      group t by 1
                      into g
                      select new TotalTransaction
                      {
                          Amt = g.Sum(tt => tt.Amt ?? 0),
                          Amtdue = g.Sum(tt => tt.Amtdue ?? 0),
                          Donate = g.Sum(tt => tt.Donate ?? 0),
                          Count = g.Count()
                      };
              return q.FirstOrDefault();
          }
  
          private IQueryable<TransactionList> FetchTransactions()
          {
              if (_transactions != null)
                  return _transactions;
              if (!name.HasValue())
                  name = null;
              string first, last;
              Util.NameSplit(name, out first, out last);
              var hasfirst = first.HasValue();
              nameid = name.ToInt();
              _transactions
                  = from t in DbUtil.Db.ViewTransactionLists
                    let donate = t.Donate ?? 0
                    where t.Amt >= gtamount || gtamount == null
                    where t.Amt <= ltamount || ltamount == null
                    where description == null || t.Description.Contains(description)
                    where nameid > 0 || ((t.Testing ?? false) == testtransactions)
                    where apprtransactions == (t.Moneytran == true) || !apprtransactions
                    where (nocoupons && !t.TransactionId.Contains("Coupon")) || !nocoupons
                    where (t.Financeonly ?? false) == false || finance
                    select t;
              if (name != null)
                  if (name == "0")
                      // special case, return no transactions, all we are interested in is the Senders on a Mission Trip
                      _transactions = from t in _transactions
                                      where t.OriginalId == nameid
                                      select t;
                  else
                      _transactions = from t in _transactions
                                      where
                                          (
                                              (t.Last.StartsWith(last) || t.Last.StartsWith(name))
                                              && (!hasfirst || t.First.StartsWith(first) || t.Last.StartsWith(name))
                                              )
                                          || t.Batchref == name || t.TransactionId == name || t.OriginalId == nameid || t.Id == nameid
                                      select t;
              if (!HttpContext.Current.User.IsInRole("Finance"))
                  _transactions = _transactions.Where(tt => (tt.Financeonly ?? false) == false);
  
              var edt = enddt;
              if (!edt.HasValue && startdt.HasValue)
                  edt = startdt;
              edt = edt?.AddHours(24);
              if (usebatchdates && startdt.HasValue)
              {
                  CheckBatchDates(startdt.Value, edt.Value);
                  _transactions = from t in _transactions
                                  where t.Batch >= startdt || startdt == null
                                  where t.Batch <= edt || edt == null
                                  where t.Moneytran == true
                                  select t;
              }
              else
                  _transactions = from t in _transactions
                                  where t.TransactionDate >= startdt || startdt == null
                                  where t.TransactionDate <= edt || edt == null
                                  select t;
              //			var q0 = _transactions.ToList();
              //            foreach(var t in q0)
              //                Debug.WriteLine("\"{0}\"\t{1}\t{2}", t.Description, t.Id, t.Amt);
              return _transactions;
          }
  
          public IQueryable<BatchTranGroup> FetchBatchTransactions()
          {
              var q = from t in FetchTransactions()
                      group t by t.Batchref
                      into g
                      orderby g.First().Batch descending
                      select new BatchTranGroup
                      {
                          count = g.Count(),
                          batchdate = g.Max(gg => gg.Batch),
                          BatchRef = g.Key,
                          BatchType = g.First().Batchtyp,
                          Total = g.Sum(gg => gg.Amt ?? 0)
                      };
              return q;
          }
  
          public IEnumerable<DescriptionGroup> FetchTransactionsByDescription()
          {
              var q0 = FetchTransactions();
              var q = from t in q0
                      group t by t.Description
                      into g
                      orderby g.First().Batch descending
                      select new DescriptionGroup
                      {
                          count = g.Count(),
                          Description = g.Key,
                          Total = g.Sum(gg => (gg.Amt ?? 0) - (gg.Donate ?? 0))
                      };
              return q;
          }
  
          public IQueryable<BatchDescriptionGroup> FetchTransactionsByBatchDescription()
          {
              var q = from t in FetchTransactions()
                      group t by new {t.Batchref, t.Description}
                      into g
                      let f = g.First()
                      orderby f.Batch, f.Description descending
                      select new BatchDescriptionGroup
                      {
                          count = g.Count(),
                          batchdate = f.Batch,
                          BatchRef = f.Batchref,
                          BatchType = f.Batchtyp,
                          Description = f.Description,
  
gt:                         Total = g.Sum(gg => (gg.Amt ?? 0) - (gg.Donate ?? 0))