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());
}
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);
}
