/// <summary>
/// No validation is performed here; this is strictly data access.
/// </summary>
/// <param name="user"></param>
/// <param name="ca"></param>
/// <returns></returns>
public bool SaveAnnualUsageReport(User user, JsonContiguousAcres ca, out string errors)
{
errors = "";
using (var trans = BeginTransaction()) {
try {
ExecuteNonQuery(@"
merge BankedWater as target
using (
select
@caId as caId,
@actualUserId as actualUserId,
@actingUserId as actingUserId,
@acres as acres,
@inches as inches,
@year as operatingYear
) as source on (
target.ContiguousAcresId = source.caId
and target.OperatingYear = source.operatingYear
)
when matched then
update set
ActualUserId = source.actualUserId,
ActingUserId = source.actingUserId,
Acres = source.acres,
BankedWaterInches = source.inches
when not matched then
insert (
ContiguousAcresId,
ActualUserId,
ActingUserId,
Acres,
BankedWaterInches,
OperatingYear
) values (
source.caId,
source.actualUserId,
source.actingUserId,
source.acres,
source.inches,
source.operatingYear
);",
new Param("@caId", ca.number),
new Param("@actualUserId", user.Id),
new Param("@actingUserId", user.ActingAsUserId ?? user.Id),
new Param("@acres", ca.annualUsageSummary.contiguousArea),
new Param("@inches", ca.annualUsageSummary.desiredBankInches),
new Param("@year", ca.year)
);
if (Config.IsCafoDeployed) {
foreach (var cafo in ca.cafos) {
ExecuteNonQuery(@"
merge ReportedCafoUsage as target
using (
select
@cafoUsageId as cafoUsageId,
@caId as caId,
@operatingYear as operatingYear,
@cafoOperationId as cafoOperationId,
@avgLivestock as avgLivestock,
@calcVolGals as calcVolGals,
@userRevisedVol as userRevisedVol,
@userRevisedVolUnitId as userRevisedVolUnitId
) as source on (
target.CafoUsageId = source.cafoUsageId
)
when matched then
update set
CafoOperationId = source.cafoOperationId,
OperatingYear = source.operatingYear,
ContiguousAcresId = source.caId,
AvgLivestockPerDay = source.avgLivestock,
CalculatedVolumeGallons = source.calcVolGals,
UserRevisedVolume = source.userRevisedVol,
UserRevisedVolumeUnitId = source.userRevisedVolUnitId
when not matched then
insert (
OperatingYear,
ContiguousAcresId,
CafoOperationId,
AvgLivestockPerDay,
CalculatedVolumeGallons,
UserRevisedVolume,
UserRevisedVolumeUnitId
) values (
source.operatingYear,
source.caId,
source.cafoOperationId,
source.avgLivestock,
source.calcVolGals,
source.userRevisedVol,
source.userRevisedVolUnitId
);",
new Param("@cafoUsageId", cafo.id > -1 ? cafo.id : (object)DBNull.Value),
new Param("@operatingYear", ca.year),
new Param("@caId", ca.number),
new Param("@cafoOperationId", cafo.cafoId),
new Param("@avgLivestock", cafo.avgLivestock),
new Param("@calcVolGals", cafo.calculatedVolumeGallons),
new Param("@userRevisedVol", (cafo.acceptCalculation ? (object)DBNull.Value : cafo.userRevisedVolume)),
new Param("@userRevisedVolUnitId", (cafo.acceptCalculation ? (object)DBNull.Value : cafo.userRevisedVolumeUnitId))
);
}
}
// Save reported meter volumes
foreach (var mr in ca.meterReadings) {
ExecuteNonQuery(@"
merge ReportedMeterVolumes as target
using (
select
@caId as caId,
@miid as miid,
@year as year,
@actualUserId as actualUserId,
@actingUserId as actingUserId,
@calcVolGals as calcVolGals,
@userRevisedVol as userRevisedVol,
@userRevisedVolUnitId as userRevisedVolUnitId
) as source on (
target.ContiguousAcresId = source.caId
and target.MeterInstallationId = source.miid
and target.OperatingYear = source.year
)
when matched then
update set
ActualUserId = source.actualUserId,
ActingUserId = source.actingUserId,
CalculatedVolumeGallons = source.calcVolGals,
UserRevisedVolume = source.userRevisedVol,
UserRevisedVolumeUnitId = source.userRevisedVolUnitId,
SubmitDatetime = GETDATE()
when not matched then
insert (
MeterInstallationId,
ContiguousAcresId,
OperatingYear,
ActualUserId,
ActingUserId,
CalculatedVolumeGallons,
UserRevisedVolume,
UserRevisedVolumeUnitId,
SubmitDatetime
) values (
source.miid,
source.caId,
source.year,
source.actualUserId,
source.actingUserId,
source.calcVolGals,
source.userRevisedVol,
source.userRevisedVolUnitId,
GETDATE()
);",
new Param("@caId", ca.number),
new Param("@miid", mr.Value.meterInstallationId),
new Param("@year", ca.year),
new Param("@actualUserId", user.Id),
new Param("@actingUserId", user.ActingAsUserId ?? user.Id),
new Param("@calcVolGals", mr.Value.calculatedVolumeGallons),
new Param("@userRevisedVol", mr.Value.userRevisedVolume.GetValueOrDefault() == 0 ? (object)DBNull.Value : mr.Value.userRevisedVolume),
new Param("@userRevisedVolUnitId", mr.Value.userRevisedVolume.GetValueOrDefault() == 0 ? (object)DBNull.Value : mr.Value.userRevisedVolumeUnitId)
);
// mjia: change submittal status from "0" or "NULL" to "1"
if (mr.Value.readings.Length != 0)
{
StringBuilder builder = new StringBuilder("update MeterInstallationReadings set IsSubmitted = 1 where MeterInstallationReadingID in (");
foreach (JsonMeterReading reading in mr.Value.readings)
{
builder.Append(reading.meterInstalltionReadingID + ",");
}
builder.Replace(",", ")", builder.Length - 1, 1);
ExecuteNonQuery(builder.ToString());
}
}
trans.Commit();
} catch (Exception ex) {
trans.Rollback();
new LogDalc().SaveError(user, ex);
errors = "Server error: " + ex.Message;
return false;
}
}
return true;
}
/// <summary>
/// This method populates all properties that should be refreshed on every page
/// load, i.e. not using existing stored JSON state.
/// </summary>
/// <param name="ca"></param>
/// <returns></returns>
protected JsonContiguousAcres PopulateNonPersistentProperties(JsonContiguousAcres ca)
{
throw new NotImplementedException();
}
/// <summary>
/// Validates CA for usage submittal. Assumes current reporting year.
/// </summary>
/// <param name="ca"></param>
/// <param name="errors"></param>
/// <returns></returns>
protected bool ValidateContiguousAcres(JsonContiguousAcres ca, out List<string> errors)
{
errors = new List<string>();
/* MWinckler.20130202: This set of validation is written as CA-specific, but it is
* attempting to validate meter-specific conditions. This validation needs to be
* rewritten to validate meter readings, and also not rely on calculated boolean
* values sent from the (untrustworthy) client. Rely on the actual submitted meter
* readings and user inputs - we have them all here.
if (ca.isFakingValidReadings)
{
if(!ca.userRevisedVolume)
errors.Add("The end reading for one of the meters is not in the valid reporting range of December 15 to January 15.");
}
if (ca.notEnoughReadingsForAllMeters) // This also allows user override
{
if (!ca.userRevisedVolume)
errors.Add("One of the meters does not have enough valid readings for volume calculation.");
}
if (!ca.hasValidBeginReadings) // This also allows user override
{
if (!ca.userRevisedVolume)
errors.Add("One of the meters does not have valid begin readings for volume calculation.");
}
*/
foreach (var mr in ca.meterReadings)
{
// If this is an annual reading or if the user supplied a revised volume, this validation doesn't matter.
if (!mr.Value.readings.Any(x => x.isAnnualTotalReading)
&& mr.Value.userRevisedVolume.GetValueOrDefault(0) <= 0)
{
if (mr.Value.readings.Length < 2)
{
// Too few readings.
errors.Add("One of the meters does not have enough valid readings for volume calculation.");
} else if (!mr.Value.readings.Any(x => x.isValidBeginReading)) {
// Need both a valid begin and a valid end reading.
// Note that the above check is not good - it depends on a value
// sent by the client.
// TODO: Refactor this to not rely on .isValidBeginReading; get
// the values needed to validate meter reading against the database
// using the methods in ReportingDalc.
errors.Add("One of the meters does not have valid begin readings for volume calculation.");
} else if (!mr.Value.readings.Any(x => x.isValidEndReading)) {
// TODO: See above comment regarding trusting vlient values.
errors.Add("One of the meters does not have valid ending readings for volume calculation.");
}
}
}
// Ensure the CA actually exists in the database.
if (!new GisDalc().ContiguousAcresExists(ca.number)) {
errors.Add("The specified contiguous area (ID: " + ca.number + ") does not exist.");
} else {
// (the remainder of validation is only useful if the CA actually exists)
// Verify that the user is actually associated with the CA
if (!new GisDalc().OwnsContiguousAcres(ActualUser, ca.number)) {
errors.Add("Records show that you do not own the specified contiguous acres. You may not record banked water for this area.");
return false;
}
// Ensure the CA is for the current reporting year.
if (ca.year != CurrentReportingYear) {
errors.Add("You cannot submit usage reports for previous operating years.");
}
// Check to see if the CA has already been submitted.
if (new ReportingDalc().IsSubmitted(ca.number, CurrentReportingYear)) {
errors.Add("The usage report has already been submitted for this contiguous area in this reporting year.");
}
if (ca.annualUsageSummary.contiguousArea <= 0) {
errors.Add("The contiguous area has no acreage defined!");
}
foreach (var mr in ca.meterReadings) {
if (mr.Value.acceptCalculation) {
if (mr.Value.calculatedVolumeGallons < 0) {
errors.Add("Meter ID " + mr.Key.ToString() + " has an invalid volume.");
}
} else {
if (mr.Value.userRevisedVolume <= 0) {
errors.Add("Meter ID " + mr.Key.ToString() + " has an invalid user-revised volume.");
}
}
}
if (ca.annualUsageSummary.desiredBankInches < 0) {
errors.Add("Desired water bank value cannot be negative.");
}
// Retrieve allowable application rate for this year
// It's also on the submitted CA JSON, but we cannot
// trust the client here!
var rdalc = new ReportingDalc();
var allowableRate = rdalc.GetAllowableProductionRate(CurrentReportingYear);
if (ca.annualUsageSummary.desiredBankInches > allowableRate) {
errors.Add(string.Format("You cannot bank more than {0} inches for this reporting year.", allowableRate));
}
// Check all associated wells
foreach (var well in ca.wells) {
if (well.meterInstallationIds.Length == 0) {
// Check to see if there's already been a user error response
if (!rdalc.IsWellErrorResponseRecorded(well.id, ActualUser.ActingAsUserId ?? ActualUser.Id)) {
errors.Add("Well #" + well.permitNumber + " has no associated meters.");
}
}
}
// Retrieve previous banked water values for the CA.
// (These are on the ca object, but we can't trust the client.)
var bankHistory = new ReportingDalc().GetHistoricalBankedWater(ca.number);
double prevBank = bankHistory.Where(x => x.year == CurrentReportingYear - 1).Select(x => x.bankedInches).DefaultIfEmpty(0).First();//mjia: round banked water to the 10ths.
int avgAppRate = (int)(ca.annualUsageSummary.annualVolume / ca.annualUsageSummary.contiguousArea);
int surplusSubtotal = ca.annualUsageSummary.allowableApplicationRate - avgAppRate;
var surplusTotal = prevBank + surplusSubtotal;
if (ca.annualUsageSummary.desiredBankInches > 0 && ca.annualUsageSummary.desiredBankInches > surplusTotal) {
errors.Add("You cannot bank more water than your cumulative available total (previous year's banked inches plus this year's surplus/deficit).");
}
}
return errors.Count == 0;
}
/// <summary>
/// Creates a dynamic object representing the page state.
/// This method will attempt to load and deserialize JSON
/// from the database if it exists for this user (updating
/// relevant properties appropriately), and if no existing
/// JSON exists will construct a new object with which the
/// user can report water usage.
/// </summary>
/// <returns></returns>
protected dynamic GetPageStateJson(User user)
{
// Only update the _current_ reporting year or unsubmitted
var pageStateJson = new ReportingDalc().GetReportingSummary(user.IsAdmin ? (user.ActingAsUserId ?? user.Id) : user.Id);
ReportingSummary pageState = null;
try {
pageState = JsonConvert.DeserializeObject<ReportingSummary>(pageStateJson);
} catch (Exception ex) {
// There was a problem deserializing.
// TODO: Notify user, possibly admins!
throw;
}
if (pageState == null) {
pageState = new ReportingSummary();
}
/// Returns the volume (Item1) and a bool indicating whether rollover occurred (Item2)
/// Args: meterInstallationId, meterreadings
Func<int, IEnumerable<JsonMeterReading>, Tuple<int, bool>> calculateVolume = (meterInstallationId, readings) => {
double runningTotal = 0d;
if (readings.Count() == 0) {
return new Tuple<int, bool>((int)runningTotal, false);
}
bool rolledOver = false;
var prevReading = readings.First();
foreach (var currentReading in readings.Skip(1)) {
double delta = 0;
if (currentReading.reading.GetValueOrDefault() < prevReading.reading.GetValueOrDefault()) {
// Rollover occurred
rolledOver = true;
// Retrieve the rollover value for this meter installation
var rollover = new MeterDalc().GetMeterInstallations(meterInstallationId).First().RolloverValue;
delta = (rollover - prevReading.reading.Value) + currentReading.reading.Value;
} else {
delta = currentReading.reading.GetValueOrDefault() - prevReading.reading.GetValueOrDefault();
}
runningTotal += delta * (prevReading.rate.HasValue ? prevReading.rate.Value : 1);
prevReading = currentReading;
}
return new Tuple<int, bool>((int)runningTotal, rolledOver);
};
// Only show Approved CAs unless the user is an admin
var contigAcres = new GisDalc().GetContiguousAcres(user, true).Where(ca => user.IsAdmin || ca.IsApproved);
// Key: CA ID, Value: well ids
var wellIds = new Dictionary<int, HashSet<int>>();
Dictionary<int, ContiguousAcres> validAcres = new Dictionary<int, ContiguousAcres>();
// There may be load errors that got persisted from a previous page load, but we
// only want to show current ones so clear the list.
pageState.loadErrors.Clear();
if (contigAcres.Count() > 0) { // Only create the service if contigAcres has stuff, because SOAP takes time
var service = new GisServiceSoapClient();
foreach (var ca in contigAcres) {
string result = "";
// This call is prone to failure whenever the GIS service is down.
try {
result = service.GetWellIDsByCA(ca.OBJECTID);
} catch (Exception ex) {
pageState.loadErrors.Add("Unable to load well associations for any CAs: the GIS service appears to be down!");
}
try {
int[] ids = JsonConvert.DeserializeObject<int[]>(result.Trim('{', '}'));
wellIds[ca.caID] = new HashSet<int>(ids);
validAcres[ca.caID] = ca;
} catch (JsonException ex) {
pageState.loadErrors.Add("Error loading CA ID " + ca.caID + ": " + ex.Message + " Service response: " + result);
}
}
}
var mdalc = new MeterDalc();
var rptgDalc = new ReportingDalc();
var wellDalc = new WellDalc();
var wells = wellDalc.GetWells(wellIds.SelectMany(x => x.Value).ToArray());
var meterInstallations = mdalc.GetMeterInstallations(wells.SelectMany(x => x.MeterInstallationIds).ToArray()).GroupBy(x => x.Id).Select(x => x.First()).ToDictionary(x => x.Id, x => x);
// In addition to the wells retrieved by the spatial query, we also need to
// retrieve wells attached to the meters on that initial set.
wells = wells.Concat(wellDalc.GetWellsByMeterInstallationIds(meterInstallations.Select(x => x.Key).ToArray()))
.Distinct((a,b) => a.WellId == b.WellId);
Func<int, Dictionary<int, JsonBankedWaterRecord>> getBankedWaterTable = caId => {
return rptgDalc.GetHistoricalBankedWater(caId).ToDictionary(x => x.year, x => x);
};
// func to populate a JsonCA in the current reporting year
Func<ContiguousAcres, JsonContiguousAcres> createNewJsonCA = ca => {
ca.Wells = wellDalc.GetWells(wellIds[ca.caID].ToArray()).ToArray();
var reportedVolumes = mdalc.GetReportedVolumeGallons(ca.Wells.SelectMany(w => w.MeterInstallationIds), ca.caID, CurrentReportingYear);
JsonContiguousAcres ret = new JsonContiguousAcres(ca, CurrentReportingYear);
ReportedVolume rvol;
ret.meterReadings = new Dictionary<int, JsonMeterReadingContainer>();
MeterInstallation meter = null;
foreach (var miid in ret.wells.SelectMany(x => x.meterInstallationIds).Distinct()) {
meterInstallations.TryGetValue(miid, out meter);
var container = new JsonMeterReadingContainer() {
meterInstallationId = miid,
calculatedVolumeGallons = reportedVolumes.TryGetValue(miid, out rvol) ? rvol.CalculatedVolumeGallons : 0,
acceptCalculation = rvol == null || !rvol.UserRevisedVolumeUnitId.HasValue,
userRevisedVolume = rvol != null && rvol.UserRevisedVolume.HasValue ? rvol.UserRevisedVolume.Value : 0,
userRevisedVolumeUnitId = rvol != null && rvol.UserRevisedVolumeUnitId.HasValue ? rvol.UserRevisedVolumeUnitId.Value : new int?(),
totalVolumeAcreInches = 0,
isNozzlePackage = meter != null ? meter.MeterType.Description().ToLower() == "nozzle package" : false,
isElectric = meter != null ? meter.MeterType.Description().ToLower() == "electric" : false,
isNaturalGas = meter != null ? meter.MeterType.Description().ToLower() == "natural gas" : false,
isThirdParty = meter != null ? meter.MeterType.Description().ToLower() == "bizarro-meter" : false,
depthToRedbed = -99999,
//squeezeValue = 0,
unitId = meter != null ? meter.UnitId : null,
county = meter != null ? meter.County : "",
rolloverValue = meter != null ? meter.RolloverValue : 0,
countyId = meter != null ? meter.CountyId : -1,
meterType = meter != null ? meter.MeterType.Description() : "",
nonStandardUnits = meter != null && meter.MeterType != HPEntities.Entities.Enums.MeterType.standard ? new MeterDalc().GetUnits(meter.MeterType).Description() : "",
meterMultiplier = meter.Multiplier != 0 ? meter.Multiplier : 1.0d // Make sure multiplier is not 0. NULL value is already converted to 1.0d in Dalc
};
// Add depth to redbed if the meter is of 1) natural gas or 2) electric
if (container.isNaturalGas || container.isElectric)
{
/* //ned skip this lookup until we really need to implement it
var service1 = new GisServiceSoapClient();
string depthInString = service1.GetDepthToRedbedByMeter(miid);
string depthInString = "Implement this later"
double depth = -99999;
if (!Double.TryParse(depthInString, out depth))
depth = -99999;
*/
double depth = 100; //take this out when the above works
container.depthToRedbed = depth;
}
container.readings = (from mr in mdalc.GetReadings(miid, CurrentReportingYear).Reverse() // By default, they're ordered by reading date desc
select new JsonMeterReading() {
reading = mr.Reading,
rate = mr.Rate,
isValidBeginReading = ReportingDalc.IsMeterReadingValidBeginReading(mr, CurrentReportingYear),
isValidEndReading = ReportingDalc.IsMeterReadingValidEndReading(mr, CurrentReportingYear),
isAnnualTotalReading = ReportingDalc.IsMeterReadingAnnualTotal(mr, CurrentReportingYear),
meterInstalltionReadingID = mr.MeterInstallationReadingId
}).ToArray();
ret.meterReadings[miid] = container;
}
ret.meterInstallationErrors = GetMeterInstallationErrors(ca, wellIds[ca.caID]);
ret.bankedWaterHistory = getBankedWaterTable(ca.caID);
JsonBankedWaterRecord bankedWaterLastYear;
ret.annualUsageSummary = new JsonAnnualUsageSummary() {
contiguousArea = ca.AreaInAcres,
annualVolume = 0, // this will only be populated after submittal
allowableApplicationRate = rptgDalc.GetAllowableProductionRate(CurrentReportingYear),
bankedWaterFromPreviousYear = ret.bankedWaterHistory.TryGetValue(CurrentReportingYear - 1, out bankedWaterLastYear) ? bankedWaterLastYear.bankedInches : 0
};
ret.isSubmitted = rptgDalc.IsSubmitted(ca.caID, CurrentReportingYear);
ret.ownerClientId = ca.OwnerClientId;
ret.isCurrentUserOwner = (this.ActualUser.ActingAsUserId ?? this.ActualUser.Id) == ca.OwnerClientId;
return ret;
};
JsonReportingYear year;
if (pageState.years.TryGetValue(CurrentReportingYear, out year)) {
// Check the existing year object and update as necessary
// For now, wholly reload any CAs that have not yet been submitted.
List<JsonContiguousAcres> removals = year.contiguousAcres.Where(ca => !ca.isSubmitted).ToList();
HashSet<int> submittedCaIds = new HashSet<int>(year.contiguousAcres.Where(ca => ca.isSubmitted).Select(x => x.number));
// Ensure that all the CAs are still applicable to this account - the CA IDs need
// to match what's presently associated with the user account, else they're removed.
foreach (var ca in removals) {
year.contiguousAcres.Remove(ca);
}
// Add any CAs that were missing from the original state
foreach (var kv in validAcres) {
if (!submittedCaIds.Contains(kv.Value.caID)) {
year.contiguousAcres.Add(createNewJsonCA(kv.Value));
}
for (int i = 0; i < year.contiguousAcres.Count; i++) {
if (year.contiguousAcres[i].number == kv.Value.caID) {
year.contiguousAcres[i].meterInstallationErrors = GetMeterInstallationErrors(kv.Value, wellIds[kv.Value.caID]);
year.contiguousAcres[i].wells = kv.Value.Wells.Select(w => new JsonWell(w)).ToArray();
}
}
//year.contiguousAcres.Where(jca => jca.number == kv.Value.caID).First().meterInstallationErrors = GetMeterInstallationErrors(kv.Value, wellIds[kv.Value.caID]);
}
foreach (var ca in year.contiguousAcres) {
// This is used on the frontend to decide whether to show the user an editable form.
// Only CA owners get the editable form.
ca.isCurrentUserOwner = (this.ActualUser.ActingAsUserId ?? this.ActualUser.Id) == ca.ownerClientId;
}
} else { // No existing year record exists; create a new one
year = new JsonReportingYear();
year.contiguousAcres = validAcres.Values.Select(ca => {
return createNewJsonCA(ca);
}).ToList();
pageState.years[CurrentReportingYear] = year;
}
// Populate meterInstallations by retrieving metadata for all used meter installations
HashSet<int> meterInstallationIds = new HashSet<int>((from y in pageState.years
from ca in y.Value.contiguousAcres
from w in ca.wells
select w.meterInstallationIds).SelectMany(x => x));
// Converting this directly to a dictionary will fail in the event that a meter
// is associated with wells in multiple counties. However, that case doesn't really
// make any sense and would break all sorts of other things (such as meter unit conversion factors
// that are specific to counties), so we're going to assume that there's always
// a single county.
pageState.meterInstallations = mdalc.GetMeterInstallations(meterInstallationIds.ToArray())
.GroupBy(x => x.Id)
.Select(mi => new JsonMeterInstallation(mi.First()))
.ToDictionary(
x => x.id,
x => x
);
pageState.currentReportingYear = CurrentReportingYear;
pageState.isReportingAllowed = IsReportingAllowed;
pageState.adminOverride = ((!IsReportingAllowed) && this.ActualUser.IsAdmin);
pageState.cafoLookups = rptgDalc.GetCafoLookups();
// Go through and check all the submittal states of the CAs
foreach (var ca in pageState.years[CurrentReportingYear].contiguousAcres) {
ca.isSubmitted = rptgDalc.IsSubmitted(ca.number, CurrentReportingYear);
if (ca.isSubmitted) {
// If it was a submitted CA, the banked water history table
// still needs to be loaded because it wasn't set above.
ca.bankedWaterHistory = getBankedWaterTable(ca.number);
}
}
return pageState;
}