private static List <Models.MSTR_User> GetOrganizationPilots()
{
var DB = new Models.ExponentPortalEntities();
int AccountID = Util.getAccountID();
var Query = from u in DB.MSTR_User
where u.AccountId == AccountID && u.IsPilot == true
orderby u.FirstName
select u;
var TheList = Query.ToList();
//If no photo image, skip it
foreach (var Usr in TheList)
{
if (String.IsNullOrEmpty(Usr.PhotoUrl))
{
Usr.PhotoUrl = "/images/PilotImage.png";
}
else
{
Usr.PhotoUrl = $"/Upload/User/{Usr.UserId}/{Usr.PhotoUrl}";
if (!System.IO.File.Exists(System.Web.HttpContext.Current.Server.MapPath(Usr.PhotoUrl)))
{
Usr.PhotoUrl = "/images/PilotImage.png";
}
}
}//foreach(var Usr in TheList)
return(TheList);
}
public async Task ApplyDroneFlight(Models.DroneFlight flight, Models.ExponentPortalEntities db)
{
if (flight == null)
{
return;
}
String SQL =
$@"SELECT
{CalculateField} * {CostMultipliedBy} / {_CostDividedBy}
FROM DroneFlight
WHERE ID={flight.ID}";
if (!String.IsNullOrWhiteSpace(ApplyCondition))
{
SQL += $" AND ({ApplyCondition})";
}
using (var cmd = db.Database.Connection.CreateCommand()) {
cmd.CommandText = SQL;
var Result = await cmd.ExecuteScalarAsync();
if (Result == null)
{
_CalculatedCost = 0;
}
else
{
Decimal.TryParse(Result.ToString(), out _CalculatedCost);
}
}//using ctx.Database.Connection.CreateCommand
}
public async Task ApplyFlightEstimate(BillingNOC noc, Models.ExponentPortalEntities db)
{
String SQL =
$@"SELECT
{CalculateField} * {CostMultipliedBy} / {_CostDividedBy}
FROM #NOC_Details";
if (!String.IsNullOrWhiteSpace(ApplyCondition))
{
String _ACondition = ApplyCondition.Replace("DroneFlight.", "#NOC_Details.");
SQL += $" WHERE ({_ACondition})";
}
using (var cmd = db.Database.Connection.CreateCommand()) {
cmd.CommandText = SQL;
var Result = await cmd.ExecuteScalarAsync();
if (Result == null)
{
_CalculatedCost = 0;
}
else
{
Decimal.TryParse(Result.ToString(), out _CalculatedCost);
}
}//using ctx.Database.Connection.CreateCommand
}
public async Task <List <BillingGroupRule> > GenerateEstimate(BillingNOC noc)
{
using (var db = new Models.ExponentPortalEntities()) {
await db.Database.Connection.OpenAsync();
await CreateTempTableFor(noc, db);
foreach (var rule in Rules.Where(w => w.IsActive && w.CalculateOn == "NOC_Details"))
{
await rule.ApplyNoc(noc, db);
}
foreach (var rule in Rules.Where(w => w.IsActive && w.CalculateOn == "DroneFlight"))
{
await rule.ApplyFlightEstimate(noc, db);
}
}
//Return all active ruels applied
return(Rules.Where(w => w.IsActive).ToList());
}
public IQueryable <Object> SetFilter(Models.ExponentPortalEntities db)
{
var List = db.AgriTraxManagements.Select(e => new {
AgriTraxID = e.AgriTraxID,
CustomerReference = e.CustomerReference,
DisburementDate = e.DisburementDate,
Tenor = e.Tenor,
PrincipalAmount = e.PrincipalAmount,
BranchID = e.BranchID,
LoanOfficer = e.LoanOfficer,
LandAddress = e.LandAddress,
LandSize = e.LandSize,
Lat = e.Lat,
Lng = e.Lng,
AccountNumber = e.AccountNumber,
DisbursementDate = e.DisburementDate,
InspectionOfficer = e.InspectionOfficer,
NextSiteVisitDate = e.NextSiteVisitDate,
SiteVisitDate = e.SiteVisitDate,
InspectionNote = e.InspectionNote,
Images = e.Images
});
if (!String.IsNullOrWhiteSpace(CustomerReference))
{
List = List.Where(e => e.CustomerReference.Contains(CustomerReference));
}
if (!String.IsNullOrWhiteSpace(AccountNumber))
{
List = List.Where(e => e.AccountNumber.Contains(CustomerReference));
}
if (PrincipalAmountFrom > 0)
{
List = List.Where(e => e.PrincipalAmount >= PrincipalAmountFrom * 1000);
}
if (PrincipalAmountTo > 0)
{
List = List.Where(e => e.PrincipalAmount <= PrincipalAmountTo * 1000);
}
if (DisbursementDateFrom.HasValue)
{
List = List.Where(e => e.DisbursementDate >= DisbursementDateFrom);
}
if (DisbursementDateTo.HasValue)
{
List = List.Where(e => e.DisbursementDate <= DisbursementDateTo);
}
if (!String.IsNullOrWhiteSpace(BranchID))
{
List = List.Where(e => e.BranchID.Contains(BranchID));
}
if (!String.IsNullOrWhiteSpace(LoanOfficer) && LoanOfficer != "-")
{
List = List.Where(e => e.LoanOfficer == LoanOfficer);
}
if (Tenor > 0)
{
List = List.Where(e => e.Tenor == Tenor);
}
return(List.OrderBy(e => e.DisburementDate));
}
private async Task CreateTempTableFor(BillingNOC noc, Models.ExponentPortalEntities db)
{
String SQL1 = @"
CREATE TABLE [#NOC_Details](
[PilotID] [int] NOT NULL,
[DroneID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[StartTime] [time](7) NOT NULL,
[EndTime] [time](7) NOT NULL,
[MinAltitude] [int] NOT NULL,
[MaxAltitude] [int] NOT NULL,
[Coordinates] [text] NOT NULL,
[LOS] [char](5) NULL,
[BillingDays] [int] NOT NULL,
[BillingTotalMinutes] [int] NOT NULL,
[BillingPeakMinutes] [int] NOT NULL,
[BillingOffPeakMinutes] [int] NOT NULL,
[BillingArea] decimal NOT NULL,
[BillingVolume] decimal NOT NULL
)";
String SQL2 =
$@"INSERT INTO [#NOC_Details](
[PilotID]
,[DroneID]
,[StartDate]
,[EndDate]
,[StartTime]
,[EndTime]
,[MinAltitude]
,[MaxAltitude]
,[Coordinates]
,[LOS]
,[BillingDays]
,[BillingTotalMinutes]
,[BillingPeakMinutes]
,[BillingOffPeakMinutes]
,[BillingArea]
,[BillingVolume]
) VALUES(
0
,0
,'{noc.StartDate.ToString("yyyy-MM-dd")}'
,'{noc.EndDate.ToString("yyyy-MM-dd")}'
,'{noc.StartTime.ToString()}'
,'{noc.EndTime.ToString()}'
,{noc.MinAltitude}
,{noc.MaxAltitude}
,'{noc.Coordinates}'
,'{noc.LOS}'
,{noc.BillingDays}
,{noc.BillingTotalMinutes}
,{noc.BillingPeakMinutes}
,{noc.BillingOffPeakMinutes}
,{noc.BillingArea}
,{noc.BillingVolume}
)";
await db.Database.ExecuteSqlCommandAsync(SQL1);
await db.Database.ExecuteSqlCommandAsync(SQL2);
}
