public IQueryable <AgencyReportingBudgetRow> GetAgencyReportingData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var q = from appb in db.AppBudgets.Where(permissions.AppBudgetsFilter)
join appbs in db.AppBudgetServices.Where(permissions.AppBudgetServicesFilter) on appb.Id equals appbs.AppBudgetId
let app = appb.App
let agency = appbs.Agency
let agencygroup = appbs.Agency.AgencyGroup
let service = appbs.Service
let serviceType = appbs.Service.ServiceType
select new AgencyReportingBudgetRow
{
AgencyGroupId = agencygroup.Id,
FundId = app.FundId,
AgencyGroupName = agencygroup.Name + ", " + app.Fund.Name + ", " + app.Name,
Year = System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", appb.App.StartDate),
AgencyName = agency.Name,
ServiceTypeName = serviceType.Name,
ServiceName = service.Name,
CcGrant = appbs.CcGrant,
Cur = app.CurrencyId,
BudgetStatusName =
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.Approved ? "Approved" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.AwaitingGlobalPoApproval ? "Awaiting Global Program Officer Approval" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.AwaitingRegionalPoApproval ? "Awaiting Regional Program Officer Approval" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.Cancelled ? "Cancelled" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.New ? "New" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.Rejected ? "Rejected" :
appb.StatusId == (int)CC.Data.AppBudgetApprovalStatuses.ReturnedToAgency ? "Returned to Agency" : null
};
return(q);
}
public CountryIdConverter()
{
using (var db = new CC.Data.ccEntities())
{
countries = db.Countries.ToList();
}
}
public AgencyIdTypeConverter()
{
using (var db = new CC.Data.ccEntities())
{
if (System.Web.HttpContext.Current != null)
{
var username = System.Web.HttpContext.Current.User.Identity.Name;
var permissions = CC.Data.Services.PermissionsFactory.GetPermissionsFor(username);
if (permissions != null)
{
var ags = db.Agencies.Where(permissions.AgencyFilter).ToList();
this.ids = new HashSet <int>(ags.Select(f => f.Id));
}
}
else
{
var permissions = CC.Data.Services.PermissionsFactory.GetPermissionsFor("sysadmin");
if (permissions != null)
{
var ags = db.Agencies.Where(permissions.AgencyFilter).ToList();
this.ids = new HashSet <int>(ags.Select(f => f.Id));
}
}
}
}
public FunctionalityScoreTypeConverter()
{
using (var db = new CC.Data.ccEntities())
{
funclevels = db.FunctionalityLevels.ToList();
}
}
protected override string GetCultureName(ActionExecutingContext filterContext)
{
var username = filterContext.HttpContext.User.Identity.Name;
using (var db = new CC.Data.ccEntities())
{
var q = (from u in db.Users
where u.UserName == username
select new
{
u.RoleId,
AgencyCulture = u.Agency.AgencyGroup.Culture ??
u.Agency.AgencyGroup.Country.Culture,
SerCulture = u.AgencyGroup.Culture ??
u.AgencyGroup.Country.Culture
}).FirstOrDefault();
if (q != null)
{
switch ((CC.Data.FixedRoles)q.RoleId)
{
case CC.Data.FixedRoles.AgencyUser:
case CC.Data.FixedRoles.DafEvaluator:
case CC.Data.FixedRoles.DafReviewer:
case CC.Data.FixedRoles.AgencyUserAndReviewer:
return(q.AgencyCulture);
case CC.Data.FixedRoles.Ser:
case CC.Data.FixedRoles.SerAndReviewer:
return(q.SerCulture);
}
}
return(null);
}
}
public NationalIdTypeConverter()
{
using (var db = new CC.Data.ccEntities())
{
nationalidtypes = db.NationalIdTypes.ToList();
}
}
public LeaveReasonIdConverter()
{
using (var db = new CC.Data.ccEntities())
{
leavereasons = db.LeaveReasons.ToList();
}
}
public CommPrefConverter()
{
using (var db = new CC.Data.ccEntities())
{
commprefs = db.CommunicationsPreferences.ToList();
}
}
public CareReceivedConverter()
{
using (var db = new CC.Data.ccEntities())
{
careoptions = db.CareReceivingOptions.ToList();
}
}
public BirthCountryIdConverter()
{
using (var db = new CC.Data.ccEntities())
{
birthcountries = db.BirthCountries.ToList();
}
}
public static void NeedToApply()
{
using (var db = new CC.Data.ccEntities())
{
var ids = db.ProcessNeedToApplyClients();
}
}
public static void RejectedClients()
{
using (var db = new CC.Data.ccEntities())
{
var ids = db.ProcessRejectedClients();
}
}
public static void NoContact()
{
using (var db = new CC.Data.ccEntities())
{
var ids = db.ProcessNoContact();
}
}
public StateIdTypeConverter()
{
using (var db = new CC.Data.ccEntities())
{
states = db.States.ToList();
}
}
protected override void ExecuteInternal(IJobExecutionContext contex)
{
using (var db = new CC.Data.ccEntities())
{
CC.Data.User user = db.Users.SingleOrDefault(f => f.UserName == HttpContext.Current.User.Identity.Name);
CC.Web.Helpers.AutomatedReportsHelper.AutoEmailAllReports(CC.Data.Services.PermissionsFactory.GetPermissionsFor(user));
}
}
public static int?GetWeekStartDay(int AgengyGroupId, int AppId)
{
using (var db = new CC.Data.ccEntities())
{
if (db.AgencyApps.Any(f => f.Agency.GroupId == AgengyGroupId && f.AppId == AppId))
{
return(db.AgencyApps.FirstOrDefault(f => f.Agency.GroupId == AgengyGroupId && f.AppId == AppId).WeekStartDay);
}
return(null);
}
}
/// <summary>
/// Retrieves last successful end time of the rep.FinSumJob stored procedure execution
/// </summary>
/// <returns></returns>
public static DateTime?GetFinSumJobDate()
{
var message = "FinSumJob";
using (var db = new CC.Data.ccEntities())
{
return(db.Globals.Where(f => f.Message == message)
.OrderByDescending(f => f.Date)
.Select(f => f.Date)
.FirstOrDefault());
}
}
public Models.jQueryDataTableResult GetJqResult(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var source = Data(db, permissions);
var filtered = Filter(source);
var sorted = filtered.OrderByField(this.sSortCol_0, this.bSortDir_0);
return(new CC.Web.Models.jQueryDataTableResult
{
aaData = sorted.Skip(this.iDisplayStart).Take(this.iDisplayLength).ToList(),
iTotalDisplayRecords = filtered.Count(),
iTotalRecords = source.Count(),
sEcho = this.sEcho
});
}
public IQueryable <DafDateRangeRow> GetAgencyReportingData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var q = from fs in db.FunctionalityScores
join c in db.Clients.Where(permissions.ClientsFilter) on fs.ClientId equals c.Id
select new DafDateRangeRow
{
RegionId = fs.Client.Agency.AgencyGroup.Country.RegionId,
CountryId = fs.Client.Agency.AgencyGroup.CountryId,
AgencyGroupId = fs.Client.Agency.GroupId,
ClientId = fs.ClientId,
DiagnosticScore = fs.DiagnosticScore,
StartDate = fs.StartDate
};
return(q);
}
protected override void ExecuteInternal(IJobExecutionContext contex)
{
using (var db = new CC.Data.ccEntities())
{
var users = db.MembershipUsers.Include("User").ToList();
foreach (var user in users)
{
if ((DateTime.Now - (user.LastLoginDate ?? DateTime.Now)).TotalDays > UserDisabledInDays)
{
var u = db.Users.SingleOrDefault(f => f.Id == user.User.Id);
u.Disabled = true;
db.SaveChanges();
}
}
}
}
public static void SetString(string name, string value)
{
using (var db = new CC.Data.ccEntities())
{
try
{
var item = db.GlobalStrings.SingleOrDefault(f => f.Name == name);
if (item == null && value != null)
{
item = new CC.Data.GlobalString
{
Name = name,
Value = value
};
db.GlobalStrings.AddObject(item);
}
else if (item != null && value == null)
{
db.GlobalStrings.DeleteObject(item);
}
else if (item != null && value != null)
{
item.Value = value;
}
}
catch
{
}
try
{
db.SaveChanges();
if (value == null)
{
x.Set(name, DBNull.Value, new System.Runtime.Caching.CacheItemPolicy());
}
else
{
x.Set(name, value, new System.Runtime.Caching.CacheItemPolicy());
}
}
catch (Exception)
{
throw;
}
}
}
public static int GetWeekStartDay(CC.Data.MainReport mainReport, CC.Data.ccEntities db)
{
var agencyApp = (from sr in db.SubReports
where sr.MainReportId == mainReport.Id
select new
{
AgencyId = sr.AppBudgetService.AgencyId,
AppId = sr.AppBudgetService.AppBudget.AppId,
AgencyGroupId = sr.AppBudgetService.Agency.GroupId
}).FirstOrDefault();
var startingWeek = mainReport.Start;
DayOfWeek selectedDOW = startingWeek.DayOfWeek;
int? selectedDowDb = GlobalHelper.GetWeekStartDay(agencyApp.AgencyGroupId, agencyApp.AppId);
return(selectedDowDb ?? (int)selectedDOW);
}
protected override string GetCultureName(System.Web.Mvc.ActionExecutingContext filterContext)
{
var dd = filterContext.ActionParameters[_parameterName];
int id;
if (int.TryParse(dd.ToString(), out id))
{
using (var db = new CC.Data.ccEntities())
{
var cultureName = db.Clients.Where(f => f.Id == id).Select(f => f.Agency.AgencyGroup.Culture ?? f.Agency.AgencyGroup.Country.Culture).FirstOrDefault();
return(cultureName);
}
}
else
{
return(null);
}
}
public void Load(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
this.AgenciesList = new SelectList(
db.Agencies.Where(permissions.AgencyFilter)
.Select(f => new { Id = f.Id, Name = f.Name })
.OrderBy(f => f.Name), "id", "name");
this.AppsList = new SelectList(
db.Apps.Where(permissions.AppsFilter)
.Select(f => new { Id = f.Id, Name = f.Name })
.OrderBy(f => f.Name), "id", "name");
this.MusterFundsList = new SelectList(
(from a in db.Apps.Where(permissions.AppsFilter)
group a by a.Fund.MasterFund into g
select g.Key)
.Select(f => new { Id = f.Id, Name = f.Name })
.OrderBy(f => f.Name), "id", "name");
this.FundsList = new SelectList(
(from a in db.Apps.Where(permissions.AppsFilter)
group a by a.Fund into g
select g.Key)
.Select(f => new { Id = f.Id, Name = f.Name })
.OrderBy(f => f.Name), "id", "name");
this.ServicesList = new SelectList(
(from ag in db.AgencyGroups.Where(permissions.AgencyGroupsFilter)
from app in ag.Apps
from s in app.Services
select s).Distinct()
.Select(f => new { Id = f.Id, Name = f.Name }).Distinct()
.OrderBy(f => f.Name), "id", "Name");
this.ServiceTypesList = new SelectList(
(from ag in db.AgencyGroups.Where(permissions.AgencyGroupsFilter)
from app in ag.Apps
from s in app.Services
select s.ServiceType)
.Select(f => new { Id = f.Id, Name = f.Name }).Distinct()
.OrderBy(f => f.Name), "id", "name");
this.Currencies = new SelectList(CC.Data.Currency.ConvertableCurrencies);
}
public IQueryable <AgencyReportingRow> GetAgencyReportingData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var q = from mr in db.MainReports.Where(permissions.MainReportsFilter)
join sr in db.SubReports.Where(permissions.SubReportsFilter) on mr.Id equals sr.MainReportId
join sra in db.viewSubreportAmounts on sr.Id equals sra.id
let app = mr.AppBudget.App
let appbs = sr.AppBudgetService
let agency = sr.AppBudgetService.Agency
let agencygroup = mr.AppBudget.App.AgencyGroup
let service = sr.AppBudgetService.Service
let serviceType = sr.AppBudgetService.Service.ServiceType
select new AgencyReportingRow
{
RegionId = agencygroup.Country.RegionId,
AgencyGroupId = agencygroup.Id,
FundId = app.FundId,
AgencyGroupName = agencygroup.Name + ", " + app.Fund.Name + ", " + app.Name,
Year = System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", mr.Start),
Quarter = System.Data.Objects.SqlClient.SqlFunctions.DatePart("quarter", mr.Start),
AgencyName = agency.Name,
ServiceTypeName = serviceType.Name,
ServiceName = service.Name,
CcGrant = appbs.CcGrant,
CcExp = sra.Amount,
Cur = app.CurrencyId,
ReportStatusName =
mr.StatusId == (int)CC.Data.MainReport.Statuses.Approved ? "Approved" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.AwaitingProgramAssistantApproval ? "Awaiting PA Approval" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.AwaitingProgramOfficerApproval ? "Awaiting PO Approval" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.AwaitingAgencyResponse ? "Awaiting Agency Response" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.Cancelled ? "Cancelled" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.New ? "New" :
mr.StatusId == (int)CC.Data.MainReport.Statuses.Rejected ? "Rejected":
mr.StatusId == (int)CC.Data.MainReport.Statuses.ReturnedToAgency? "Returned To Agency" : null
};
return(q);
}
public static string GetString(string name)
{
string result = null;
if (x.Contains(name))
{
var obj = x[name];
result = (string)(obj == DBNull.Value ? null : obj);
}
else
{
using (var db = new CC.Data.ccEntities())
{
try
{
var item = db.GlobalStrings.SingleOrDefault(f => f.Name == name);
if (item != null)
{
result = item.Value;
}
}
catch (Exception)
{
return(name + " - value not found");
}
}
if (result == null)
{
x.Set(name, DBNull.Value, new System.Runtime.Caching.CacheItemPolicy());
}
else
{
x.Set(name, result, new System.Runtime.Caching.CacheItemPolicy());
}
}
return(result);
}
public static List <T> GetCachedList <T>() where T : class
{
List <T> result = null;
string name = typeof(T).ToString();
var cache = HttpContext.Current.Cache;
if (cache[name] == null)
{
using (var db = new CC.Data.ccEntities())
{
db.ContextOptions.LazyLoadingEnabled = false;
db.ContextOptions.ProxyCreationEnabled = false;
var objectset = db.CreateObjectSet <T>();
result = objectset.ToList();
cache[name] = result;
}
}
else
{
result = (List <T>)cache[name];
}
return(result);
}
public IQueryable <SocializationClientsCountRow> GetAgencyReportingData(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var q = from mr in db.MainReports.Where(permissions.MainReportsFilter)
join sr in db.SubReports.Where(permissions.SubReportsFilter) on mr.Id equals sr.MainReportId
join cec in db.ClientEventsCountReports on sr.Id equals cec.SubReportId
select new SocializationClientsCountRow
{
RegionId = sr.AppBudgetService.Agency.AgencyGroup.Country.RegionId,
CountryId = sr.AppBudgetService.Agency.AgencyGroup.CountryId,
StateId = sr.AppBudgetService.Agency.AgencyGroup.StateId,
MasterFundId = mr.AppBudget.App.Fund.MasterFundId,
FundId = mr.AppBudget.App.FundId,
AgencyGroupId = sr.AppBudgetService.Agency.GroupId,
Year = System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", mr.Start),
AgencyGroupName = sr.AppBudgetService.Agency.AgencyGroup.Name,
AgencyName = sr.AppBudgetService.Agency.Name,
EventDate = cec.EventDate,
JNVCount = cec.JNVCount,
TotalCount = cec.TotalCount,
Remarks = cec.Remarks
};
return(q);
}
public IQueryable <BudgetSummaryDataRow> Data(CC.Data.ccEntities db, CC.Data.Services.IPermissionsBase permissions)
{
var raw =
from abs in db.AppBudgetServices
join aex in db.viewAppExchangeRates on new { appid = abs.AppBudget.AppId, CurId = this.CurId } equals new { appid = aex.AppId, CurId = aex.ToCur } into aexG
from aex in aexG.DefaultIfEmpty()
select new
{
AgencyId = abs.AgencyId,
ServiceId = abs.ServiceId,
ServiceTypeId = abs.Service.ServiceType.Id,
serviceName = abs.Service.Name,
serviceType = abs.Service.ServiceType.Name,
FundId = abs.AppBudget.App.FundId,
AppExRate = aex.Value,
Start = abs.AppBudget.App.StartDate,
End = abs.AppBudget.App.EndDate,
Amount = abs.CcGrant,
AppId = abs.AppBudget.AppId,
MasterFundId = abs.AppBudget.App.Fund.MasterFundId
};
#region filter
if (this.AgencyId.HasValue)
{
raw = raw.Where(f => f.AgencyId == AgencyId);
}
if (this.ServiceTypeId.HasValue)
{
raw = raw.Where(f => f.ServiceTypeId == this.ServiceTypeId);
}
if (this.ServiceId.HasValue)
{
raw = raw.Where(f => f.ServiceId == this.ServiceId);
}
if (this.StartDate.HasValue)
{
raw = raw.Where(f => f.Start >= this.StartDate);
}
if (this.EndDate.HasValue)
{
raw = raw.Where(f => f.End <= this.EndDate);
}
if (this.FundId.HasValue)
{
raw = raw.Where(f => f.FundId == this.FundId);
}
if (this.AppId.HasValue)
{
raw = raw.Where(f => f.AppId == this.AppId);
}
#endregion
var source = from item in raw
join agency in db.Agencies.Where(permissions.AgencyFilter) on item.AgencyId equals agency.Id
join fund in db.Funds.Where(permissions.FundsFilter) on item.FundId equals fund.Id
join app in db.Apps.Where(permissions.AppsFilter) on item.AppId equals app.Id
select new BudgetSummaryDataRow
{
AgencyName = agency.Name,
ServiceName = item.serviceName,
ServiceId = item.ServiceId,
ServiceTypeId = item.ServiceTypeId,
ServiceTypeName = item.serviceType,
AppName = app.Name,
Fund = fund.Name,
CCGrant = item.Amount * item.AppExRate ?? 0,
CurrencyCode = this.CurId,
MasterFundId = item.MasterFundId
};
return(source);
}
