public bool InsertMyWthExpUsage(WthExpUsage wthExpUsage)
{
string sql = @"
INSERT INTO [MyWthExpUsage] ([RdngID], [Units], [ExpUsage_New], [PercentDelta_New], [ExpUsage_Old], [PercentDelta_Old], [DateStart], [DateEnd],
[AccID], [UtilID], [UnitID])
VALUES (@RdngID, @Units, @ExpUsage_New, @PercentDelta_New, @ExpUsage_Old, @PercentDelta_Old, @DateStart, @DateEnd, @AccID, @UtilID, @UnitID);";
//SELECT CAST(SCOPE_IDENTITY() as int)";
//using (IDbConnection db = new SqlConnection(_jitWebData3ConnectionString))
using (IDbConnection db = new SqlConnection(_myConnectionString))
{
int rowsAffected = db.Execute(sql, new
{
wthExpUsage.RdngID,
wthExpUsage.Units,
wthExpUsage.ExpUsage_New,
wthExpUsage.PercentDelta_New,
wthExpUsage.ExpUsage_Old,
wthExpUsage.PercentDelta_Old,
wthExpUsage.DateStart,
wthExpUsage.DateEnd,
wthExpUsage.AccID,
wthExpUsage.UtilID,
wthExpUsage.UnitID
});
return(rowsAffected == 1);
}
}
private void PopulateMyWthExpUsage()
{
//List<ReadingsQueryResult> allReadings = _weatherRepository.GetReadingsFromExpUsageOriginal();
List <ReadingsQueryResult> allReadings = _weatherRepository.GetReadingsFromExpUsageOriginalCorrected();
var readingsByAccount = allReadings.GroupBy(r => new { r.AccID, r.UtilID, r.RUnitID });
foreach (var group in readingsByAccount)
{
try
{
List <ReadingsQueryResult> readings = group.ToList();
WthNormalParams _params =
_weatherRepository.GetParamsForReading(readings.First().AccID, readings.First().UtilID, readings.First().RUnitID);
foreach (ReadingsQueryResult reading in readings)
{
WthExpUsage wthExpUsage = new WthExpUsage();
wthExpUsage.RdngID = reading.RdngID;
wthExpUsage.AccID = reading.AccID;
wthExpUsage.UtilID = reading.UtilID;
wthExpUsage.UnitID = reading.RUnitID;
decimal expUsage_Old = decimal.Round(Convert.ToDecimal(reading.ExpUsage), 4, MidpointRounding.AwayFromZero);
wthExpUsage.ExpUsage_Old = expUsage_Old;
wthExpUsage.Units = reading.Units;
List <WeatherData> weatherDataList =
_weatherRepository.GetWeatherDataByZipStartAndEndDate(_params.WthZipCode, reading.DateStart, reading.DateEnd);
reading.B3 = _params.B3_New;
reading.B5 = _params.B5_New;
HeatingCoolingDegreeDays hcdd = HeatingCoolingDegreeDaysValueOf(reading, weatherDataList);
decimal expUsage_New = _params.B1_New * reading.Days
+ (_params.B2_New * decimal.Round(Convert.ToDecimal(hcdd.HDD), 4, MidpointRounding.AwayFromZero))
+ (_params.B4_New * decimal.Round(Convert.ToDecimal(hcdd.CDD), 4, MidpointRounding.AwayFromZero));
wthExpUsage.ExpUsage_New = expUsage_New;
if (reading.Units != 0)
{
wthExpUsage.PercentDelta_Old = Math.Abs((expUsage_Old - reading.Units) / reading.Units);
wthExpUsage.PercentDelta_New = Math.Abs((expUsage_New - reading.Units) / reading.Units);
}
wthExpUsage.DateStart = reading.DateStart;
wthExpUsage.DateEnd = reading.DateEnd;
_weatherRepository.InsertMyWthExpUsage(wthExpUsage);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " " + group.ToList().First().RdngID + " " + group.ToList().First().AccID + " " + group.ToList().First().RUnitID
+ "\n" + e.StackTrace);
}
}
}
public bool InsertMyWthExpUsage(WthExpUsage wthExpUsage, bool accord)
{
if (!accord)
{
return(false);
}
string sql = @"update MyWthExpUsage SET ExpUsage_Accord = @ExpUsage_New, PercentDelta_Accord = @PercentDelta_New
where RdngID = @RdngID";
//using (IDbConnection db = new SqlConnection(_jitWebData3ConnectionString))
using (IDbConnection db = new SqlConnection(_myConnectionString))
{
int rowsAffected = db.Execute(sql, new
{
wthExpUsage.ExpUsage_New,
wthExpUsage.PercentDelta_New,
wthExpUsage.RdngID
});
return(rowsAffected == 1);
}
}
private void PopulateMyWthExpUsage(AccordResult accord)
{
//List<ReadingsQueryResult> allReadings = _weatherRepository.GetReadingsFromExpUsageOriginal();
List <ReadingsQueryResult> allReadings = _weatherRepository.GetReadingsFromExpUsageOriginalCorrected(accord);
var readingsByAccount = allReadings.GroupBy(r => new { r.AccID, r.UtilID, r.RUnitID });
foreach (var group in readingsByAccount)
{
try
{
List <ReadingsQueryResult> readings = group.ToList();
WthNormalParams _params =
_weatherRepository.GetParamsForReading(readings.First().AccID, readings.First().UtilID, readings.First().RUnitID);
foreach (ReadingsQueryResult reading in readings)
{
WthExpUsage wthExpUsage = new WthExpUsage();
wthExpUsage.RdngID = reading.RdngID;
wthExpUsage.AccID = reading.AccID;
wthExpUsage.UtilID = reading.UtilID;
wthExpUsage.UnitID = reading.RUnitID;
decimal expUsage_Old = decimal.Round(Convert.ToDecimal(reading.ExpUsage), 4, MidpointRounding.AwayFromZero);
wthExpUsage.ExpUsage_Old = expUsage_Old;
wthExpUsage.Units = reading.Units;
List <WeatherData> weatherDataList =
_weatherRepository.GetWeatherDataByZipStartAndEndDate(_params.WthZipCode, reading.DateStart, reading.DateEnd);
decimal B1 = 0;
decimal B2 = 0;
decimal B4 = 0;
reading.B3 = accord.HeatingBP;
reading.B5 = accord.CoolingBP;
if (accord.IsSimpleSingleRegression)
{
if (reading.B3 > 0)
{
B1 = decimal.Round(Convert.ToDecimal(accord.SimpleLinearRegression.Intercept), 12, MidpointRounding.AwayFromZero);
B2 = decimal.Round(Convert.ToDecimal(accord.SimpleLinearRegression.Slope), 12, MidpointRounding.AwayFromZero);
B4 = 0;
}
else if (reading.B5 > 0)
{
B1 = decimal.Round(Convert.ToDecimal(accord.SimpleLinearRegression.Intercept), 12, MidpointRounding.AwayFromZero);
B2 = 0;
B4 = decimal.Round(Convert.ToDecimal(accord.SimpleLinearRegression.Slope), 12, MidpointRounding.AwayFromZero);
}
}
else
{
if (reading.B3 > 0 && reading.B5 > 0)
{
B1 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[0]), 12, MidpointRounding.AwayFromZero);
B2 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[1]), 12, MidpointRounding.AwayFromZero);
B4 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[2]), 12, MidpointRounding.AwayFromZero);
}
else if (reading.B3 > 0)
{
B1 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[0]), 12, MidpointRounding.AwayFromZero);
B2 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[1]), 12, MidpointRounding.AwayFromZero);
}
else if (reading.B5 > 0)
{
B1 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[0]), 12, MidpointRounding.AwayFromZero);
B4 = decimal.Round(Convert.ToDecimal(accord.MultipleRegression.Weights[1]), 12, MidpointRounding.AwayFromZero);
}
}
HeatingCoolingDegreeDays hcdd = HeatingCoolingDegreeDaysValueOf(reading, weatherDataList);
decimal expUsage_New = (B1 * reading.Days)
+ (B2 * decimal.Round(Convert.ToDecimal(hcdd.HDD), 12, MidpointRounding.AwayFromZero))
+ (B4 * decimal.Round(Convert.ToDecimal(hcdd.CDD), 12, MidpointRounding.AwayFromZero));
wthExpUsage.ExpUsage_New = expUsage_New;
if (reading.Units != 0)
{
wthExpUsage.PercentDelta_Old = Math.Abs((expUsage_Old - reading.Units) / reading.Units);
wthExpUsage.PercentDelta_New = Math.Abs((expUsage_New - reading.Units) / reading.Units);
}
wthExpUsage.DateStart = reading.DateStart;
wthExpUsage.DateEnd = reading.DateEnd;
_weatherRepository.InsertMyWthExpUsage(wthExpUsage, Accord: true);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message + " " + group.ToList().First().RdngID + " " + group.ToList().First().AccID + " " + group.ToList().First().RUnitID
+ "\n" + e.StackTrace);
}
}
}