using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using NHibernate; using NHibernate.Linq; using Sleis.Models; using Sleis.Infrastructure; using NHibernate.Criterion; using System.Collections; using Sleis.ViewModels; using System.Data; using Spring.Data.Common; namespace Sleis.Data { public class AgencyProcessEmissionData : BaseData, IProcessEmissionData { public List GetProcessEmissionsByEmissionUnit(int emissionUnitId) { using (ISession session = GetSession()) { return (from pe in session.Query() join up in session.Query() on pe.UnitProcessId equals up.Id where up.EmissionUnitId == emissionUnitId select pe).ToList(); } } public List GetProcessEmissionsByControlDevice(int controlDeviceId) { using (ISession session = GetSession()) { /*return (from pe in session.Query() join up in session.Query() on pe.UnitProcessId equals up.Id join upca in session.Query() on up.Id equals upca.UnitProcessId join upcd in session.Query() on upca.Id equals upcd.ControlApproachId join eu in session.Query() on up.EmissionUnitId equals eu.Id join eucp in session.Query() on eu.Id equals eucp.EmissionUnitId join eucd in session.Query() on eucp.Id equals eucd.ControlApproachId where upcd.ControlDeviceId == controlDeviceId || eucd.ControlDeviceId == controlDeviceId select pe).Distinct().ToList(); * */ ISQLQuery query = session.CreateSQLQuery(@"SELECT Distinct pe.RPT_PRD_ID ,pe.UNIT_PROC_ID ,pe.E_RPT_PRD_TYPE_CD ,pe.E_EMIS_OPER_TYPE_CD ,pe.UNIT_PROC_REPORTED_IND ,pe.UNIT_PROC_CBI_IND ,pe.START_DATE ,pe.END_DATE ,pe.E_CALC_PARM_TYPE_CD ,pe.CALC_PARM_VAL ,pe.E_CALC_PARM_UOM_CD ,pe.E_CALC_MTRL_TYPE_CD ,pe.CALC_PARM_JAN_VAL ,pe.CALC_PARM_FEB_VAL ,pe.CALC_PARM_MAR_VAL ,pe.CALC_PARM_APR_VAL ,pe.CALC_PARM_MAY_VAL ,pe.CALC_PARM_JUN_VAL ,pe.CALC_PARM_JUL_VAL ,pe.CALC_PARM_AUG_VAL ,pe.CALC_PARM_SEP_VAL ,pe.CALC_PARM_OCT_VAL ,pe.CALC_PARM_NOV_VAL ,pe.CALC_PARM_DEC_VAL ,pe.ACTL_HOURS_PER_PRD ,pe.AVG_DAYS_PER_WEEK ,pe.AVG_HOURS_PER_DAY ,pe.AVG_WEEKS_PER_PRD ,pe.PCT_WINTER_ACT ,pe.PCT_SPRING_ACT ,pe.PCT_SUMMER_ACT ,pe.PCT_FALL_ACT ,pe.ACTL_OZONE_SEAS_DAYS ,pe.ACTL_SUMMER_SEAS_DAYS ,pe.ACTL_CO_SEAS_DAYS ,pe.PCT_ASH_CONT ,pe.PCT_SULFUR_CONT ,pe.HEAT_CONT ,pe.RPT_PRD_CMNT ,pe.LAST_UPDATED_DATE ,pe.LAST_UPDATED_BY_USER_ID FROM M_RPT_PRD pe INNER JOIN M_UNIT_PROC up on pe.UNIT_PROC_ID = up.UNIT_PROC_ID LEFT OUTER JOIN M_UNIT_PROC_CTRL_APCH upca ON upca.UNIT_PROC_ID = up.UNIT_PROC_ID LEFT OUTER JOIN M_UNIT_PROC_CTRL_DEVC upcd ON upcd.UNIT_PROC_CTRL_APCH_ID = upca.UNIT_PROC_CTRL_APCH_ID INNER JOIN M_EMIS_UNIT eu on up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID LEFT OUTER JOIN M_EMIS_UNIT_CTRL_APCH euca ON euca.EMIS_UNIT_ID = eu.EMIS_UNIT_ID LEFT OUTER JOIN M_EMIS_UNIT_CTRL_DEVC eucd ON euca.EMIS_UNIT_CTRL_APCH_ID = eucd.EMIS_UNIT_CTRL_APCH_ID WHERE eucd.CTRL_DEVC_ID = :ControlDeviceId or upcd.CTRL_DEVC_ID = :ControlDeviceId"); query.SetInt32("ControlDeviceId", controlDeviceId); query.AddEntity(typeof(PublicProcessEmission)); return query.List().Distinct().ToList(); } } public List GetEmissionFactors(string pollutantCode, string calculationMethodCode, string sccCode, string emissionFactorUomCode) { using (ISession session = GetSession()) { ISQLQuery query = session.CreateSQLQuery(@" SELECT EMIS_FCTR FROM A_EMIS_FCTR WHERE E_POLLUTANT_CD = :pollutantCode AND E_EMIS_CALC_MTHD_CD = :calculationMethodCode AND E_SRC_CLASS_CD = :sccCode AND E_EMIS_FCTR_DN_UOM_CD = :emissionFactorUomCode "); query.SetString("pollutantCode", pollutantCode); query.SetString("calculationMethodCode", calculationMethodCode); query.SetString("sccCode", sccCode); query.SetString("emissionFactorUomCode", emissionFactorUomCode); return query.List().ToList(); } } public decimal CalculateControlEfficiency(int reportYear, int unitProcessId, string pollutantCode) { IDbParameters args = AdoTemplate.CreateDbParameters(); //query.SetDecimal("result", 0); args.Add("p_report_year", DbType.Int32).Value = reportYear; args.Add("p_public_ind", DbType.Int32).Value = 0; args.Add("p_unit_proc_id", DbType.Int32).Value = unitProcessId; args.Add("p_pollutant_cd", DbType.AnsiString).Value = pollutantCode; args.Add("p_up_ind", DbType.Int32).Value = (Properties.Get(Constants.UseEmissionUnitControlApproach) == "false") ? 1 : 0; args.AddInOut("p_overall_ctrl_pct", DbType.Double); //needs to be DbType.Double for proper conversion AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_GET_OVERALL_CTRL_PCT", args); var rawResults = GetOutParamValue("p_overall_ctrl_pct", args); if (rawResults != null) { return Convert.ToDecimal(rawResults); } return 0; } public EmissionCalculation CalculateEmissions(int reportYear, EmissionCalculation emission, ISession session) { /*SPRING DATA PARAMS */ IDbParameters args = AdoTemplate.CreateDbParameters(); args.Add("p_report_year", DbType.Int32).Value = reportYear; args.Add("p_unit_proc_id", DbType.Int32).Value = emission.UnitProcessId; args.Add("p_throughput", DbType.Decimal).Value = GetNullableDbParam(emission.AnnualThroughput); args.Add("p_throughput_uom_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.AnnualThroughputUom); args.Add("p_throughput_type_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.AnnualThroughputTypeCode); args.Add("p_src_class_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.Scc); args.Add("p_pct_ash_cont", DbType.Decimal).Value = GetNullableDbParam(emission.PercentAshContent); args.Add("p_pct_sulfur_cont", DbType.Decimal).Value = GetNullableDbParam(emission.PercentSulfurContent); args.Add("p_act_summer_seas_days", DbType.Decimal).Value = GetNullableDbParam(emission.ActualSummerSeasonDays); args.Add("p_pct_summer_act", DbType.Decimal).Value = GetNullableDbParam(emission.PercentSummerActual); args.Add("p_pct_ozone_season_act", DbType.Decimal).Value = GetNullableDbParam(emission.PercentOzoneSeasonActual); args.Add("p_pollutant_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.PollutantCode); args.Add("p_emis_calc_mthd_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.EmissionCalculationMethod); args.Add("p_emis_fctr", DbType.Decimal).Value = GetNullableDbParam(emission.EmissionFactor); args.Add("p_emis_fctr_dn_uom_cd", DbType.AnsiString).Value = GetNullableDbStringParam(emission.EmissionFactorUom); args.Add("p_up_ind", DbType.Boolean).Value = (Properties.Get(Constants.UseEmissionUnitControlApproach) == "false"); args.Add("p_public_ind", DbType.Boolean).Value = 0; args.AddInOut("p_total_emis", DbType.Double).Value = GetNullableDbParam(emission.TotalEmissions); args.AddOut("p_overall_ctrl_pct", DbType.Decimal); args.AddOut("p_summer_day_emis", DbType.Double); args.AddOut("p_ozone_season_emis", DbType.Double); args.AddOut("p_err_text", DbType.AnsiString, 1000); args.AddOut("p_err_code", DbType.Int32); object rawResults = AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_GET_EST_EMIS", args); emission.OverallControlPercent = GetOutParamValue("p_overall_ctrl_pct", args) != DBNull.Value ? Convert.ToDecimal(GetOutParamValue("p_overall_ctrl_pct", args)) : new decimal?(); emission.CalculationResult = Convert.ToInt32(GetOutParamValue("p_err_code", args)); emission.ErrorMessage = GetOutParamValue("p_err_text", args).ToString(); emission.TotalEmissions = GetOutParamValue("p_total_emis", args) != DBNull.Value ? Convert.ToDecimal(GetOutParamValue("p_total_emis", args)) : new decimal?(); emission.OzoneSeasonEmissions = GetOutParamValue("p_ozone_season_emis", args) != DBNull.Value ? Convert.ToDecimal(GetOutParamValue("p_ozone_season_emis", args)) : new decimal?(); emission.SummerDayEmissions = GetOutParamValue("p_summer_day_emis", args) != DBNull.Value ? Convert.ToDecimal(GetOutParamValue("p_summer_day_emis", args)) : new decimal?(); return emission; } public Sleis.ViewModels.EmissionCalculation CalculateEmissions(int reportYear, Sleis.ViewModels.EmissionCalculation emission) { using (ISession session = GetSession()) { return CalculateEmissions(reportYear, emission, session); } } public List GetProcessEmissions(int reportId) { using (ISession session = GetSession()) { ISQLQuery query = session.CreateSQLQuery(@"SELECT rp.RPT_PRD_ID ,up.UNIT_PROC_ID ,rp.E_RPT_PRD_TYPE_CD ,rp.E_EMIS_OPER_TYPE_CD ,rp.UNIT_PROC_REPORTED_IND ,rp.UNIT_PROC_CBI_IND ,rp.[START_DATE] ,rp.END_DATE ,rp.E_CALC_PARM_TYPE_CD ,rp.CALC_PARM_VAL ,rp.E_CALC_PARM_UOM_CD ,rp.E_CALC_MTRL_TYPE_CD ,rp.CALC_PARM_JAN_VAL ,rp.CALC_PARM_FEB_VAL ,rp.CALC_PARM_MAR_VAL ,rp.CALC_PARM_APR_VAL ,rp.CALC_PARM_MAY_VAL ,rp.CALC_PARM_JUN_VAL ,rp.CALC_PARM_JUL_VAL ,rp.CALC_PARM_AUG_VAL ,rp.CALC_PARM_SEP_VAL ,rp.CALC_PARM_OCT_VAL ,rp.CALC_PARM_NOV_VAL ,rp.CALC_PARM_DEC_VAL ,rp.ACTL_HOURS_PER_PRD ,rp.AVG_DAYS_PER_WEEK ,rp.AVG_HOURS_PER_DAY ,rp.AVG_WEEKS_PER_PRD ,rp.PCT_WINTER_ACT ,rp.PCT_SPRING_ACT ,rp.PCT_SUMMER_ACT ,rp.PCT_FALL_ACT ,rp.ACTL_OZONE_SEAS_DAYS ,rp.ACTL_CO_SEAS_DAYS ,rp.PCT_ASH_CONT ,rp.PCT_SULFUR_CONT ,rp.HEAT_CONT ,rp.RPT_PRD_CMNT ,rp.LAST_UPDATED_DATE ,rp.LAST_UPDATED_BY_USER_ID FROM M_UNIT_PROC up INNER JOIN M_EMIS_UNIT eu ON up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID INNER JOIN M_FAC_SITE fs ON eu.FAC_SITE_ID = fs.FAC_SITE_ID INNER JOIN M_AGN_RPT ar ON fs.AGN_RPT_ID = ar.AGN_RPT_ID INNER JOIN M_RPT_PRD rp ON up.UNIT_PROC_ID = rp.UNIT_PROC_ID WHERE ar.AGN_RPT_ID = :reportId"); query.SetInt32("reportId", reportId); query.AddEntity(typeof(ProcessEmission)); return query.List().ToList(); //obsolete /*var tmp = (from d in session.Query() where d.Id == reportId join e in session.Query() on d.Id equals e.ReportId join f in session.Query() on e.Id equals f.ReportId join g in session.Query() on e.Id equals g.FacilityId join h in session.Query() on g.Id equals h.EmissionUnitId join i in session.Query() on h.Id equals i.UnitProcessId where g.StatusCode == EmissionUnitCode.OP.ToString() || (g.StatusCode == EmissionUnitCode.TS.ToString() && g.StatusEffectiveDate == null) || (g.StatusCode == EmissionUnitCode.TS.ToString() && g.StatusEffectiveDate != null && g.StatusEffectiveDate.Value.Year == d.Number) || (g.StatusCode == EmissionUnitCode.PS.ToString() && g.StatusEffectiveDate == null) || (g.StatusCode == EmissionUnitCode.PS.ToString() && g.StatusEffectiveDate != null && g.StatusEffectiveDate.Value.Year == d.Number) select new ProcessEmission() { Id = i.Id , UnitProcessId = h.Id , UnitProcess = h , Description = g.Description , ValueUomCode = i.ValueUomCode , ValueTypeCode = i.ValueTypeCode , Value = i.Value , MaterialTypeCode = i.MaterialTypeCode } ); return tmp.ToList() .Distinct() .ToList(); * */ } } public ProcessEmission GetProcessEmission(int reportId, int unitProcessId) { using (ISession session = GetSession()) { ISQLQuery query = session.CreateSQLQuery(@"SELECT rp.RPT_PRD_ID ,up.UNIT_PROC_ID ,rp.E_RPT_PRD_TYPE_CD ,rp.E_EMIS_OPER_TYPE_CD ,rp.UNIT_PROC_REPORTED_IND ,rp.UNIT_PROC_CBI_IND ,rp.[START_DATE] ,rp.END_DATE ,rp.E_CALC_PARM_TYPE_CD ,rp.CALC_PARM_VAL ,rp.E_CALC_PARM_UOM_CD ,rp.E_CALC_MTRL_TYPE_CD ,rp.CALC_PARM_JAN_VAL ,rp.CALC_PARM_FEB_VAL ,rp.CALC_PARM_MAR_VAL ,rp.CALC_PARM_APR_VAL ,rp.CALC_PARM_MAY_VAL ,rp.CALC_PARM_JUN_VAL ,rp.CALC_PARM_JUL_VAL ,rp.CALC_PARM_AUG_VAL ,rp.CALC_PARM_SEP_VAL ,rp.CALC_PARM_OCT_VAL ,rp.CALC_PARM_NOV_VAL ,rp.CALC_PARM_DEC_VAL ,rp.ACTL_HOURS_PER_PRD ,rp.AVG_DAYS_PER_WEEK ,rp.AVG_HOURS_PER_DAY ,rp.AVG_WEEKS_PER_PRD ,rp.PCT_WINTER_ACT ,rp.PCT_SPRING_ACT ,rp.PCT_SUMMER_ACT ,rp.PCT_FALL_ACT ,rp.ACTL_OZONE_SEAS_DAYS ,rp.ACTL_CO_SEAS_DAYS ,rp.PCT_ASH_CONT ,rp.PCT_SULFUR_CONT ,rp.HEAT_CONT ,rp.RPT_PRD_CMNT ,rp.LAST_UPDATED_DATE ,rp.LAST_UPDATED_BY_USER_ID FROM M_UNIT_PROC up INNER JOIN M_EMIS_UNIT eu ON up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID INNER JOIN M_FAC_SITE fs ON eu.FAC_SITE_ID = fs.FAC_SITE_ID INNER JOIN M_AGN_RPT ar ON fs.AGN_RPT_ID = ar.AGN_RPT_ID INNER JOIN M_RPT_PRD rp ON up.UNIT_PROC_ID = rp.UNIT_PROC_ID WHERE ar.AGN_RPT_ID = :reportId AND up.UNIT_PROC_ID = :unitProcessId"); query.SetInt32("reportId", reportId); query.SetInt32("unitProcessId", unitProcessId); query.AddEntity(typeof(ProcessEmission)); return query.UniqueResult(); } } } }