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 NHibernate.Type; using Sleis.Data.CustomTypes; using System.Collections; using Sleis.ViewModels; using System.Data; using Spring.Data.Generic; using Spring.Data.Common; namespace Sleis.Data { public class PublicProcessEmissionData : 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()) { //NOTES: 11-16-2011 LINQ to NHibernate does not do outer joins..... /*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 into pe_upcd 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 into pe_eucd from cd1 in pe_eucd.DefaultIfEmpty() from cd2 in pe_upcd.DefaultIfEmpty() where cd1.ControlDeviceId == controlDeviceId || cd2.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 P_RPT_PRD pe INNER JOIN P_UNIT_PROC up on pe.UNIT_PROC_ID = up.UNIT_PROC_ID LEFT OUTER JOIN P_UNIT_PROC_CTRL_APCH upca ON upca.UNIT_PROC_ID = up.UNIT_PROC_ID LEFT OUTER JOIN P_UNIT_PROC_CTRL_DEVC upcd ON upcd.UNIT_PROC_CTRL_APCH_ID = upca.UNIT_PROC_CTRL_APCH_ID INNER JOIN P_EMIS_UNIT eu on up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID LEFT OUTER JOIN P_EMIS_UNIT_CTRL_APCH euca ON euca.EMIS_UNIT_ID = eu.EMIS_UNIT_ID LEFT OUTER JOIN P_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) { /*ISQLQuery query = session.CreateSQLQuery(@" DECLARE @v_public_ind NUMERIC(1,0), @v_up_ind NUMERIC(1,0), @v_unit_proc_id NUMERIC(10,0), @v_pollutant_cd VARCHAR(10), @result NUMERIC(8,5) SET @v_public_ind = 1 SET @v_up_ind = :unitProcessIndicator SET @v_unit_proc_id = :unitProcessId SET @v_pollutant_cd = :pollutantCode EXECUTE @result = [F_GET_OVERALL_CTRL_PCT] @v_public_ind, @v_up_ind, @v_unit_proc_id, @v_pollutant_cd SELECT @result as result"); * okay. procedure is: P_GET_OVERALL_CTRL_PCT params: @v_public_ind NUMERIC(1,0), --1=Public application, 0=Agency application @v_up_ind NUMERIC(1,0), --1=Unit Process control approach, 0=Emission Unit control approach @v_unit_proc_id INT, @v_pollutant_cd VARCHAR(10), @v_overall_ctrl_pct NUMERIC(8,5) OUTPUT 48:29 no error code output variable. if the @v_overall_ctrl_pct is NULL, there's an error. * * */ 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 = 1; 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); AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_GET_OVERALL_CTRL_PCT", args); string paramName = CreateParameterName("p_overall_ctrl_pct"); var rawResults = GetOutParamValue("p_overall_ctrl_pct", args); if (rawResults != null) { return Convert.ToDecimal(rawResults); } return 0; } public Sleis.ViewModels.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 = 1; 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 P_UNIT_PROC up INNER JOIN P_EMIS_UNIT eu ON up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID INNER JOIN P_FAC_SITE fs ON eu.FAC_SITE_ID = fs.FAC_SITE_ID INNER JOIN P_FAC_EMIS_RPT fer ON fs.FAC_EMIS_RPT_ID = fer.FAC_EMIS_RPT_ID INNER JOIN M_AGN_RPT ar ON fer.AGN_RPT_ID = ar.AGN_RPT_ID INNER JOIN P_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_SUMMER_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 P_UNIT_PROC up INNER JOIN P_EMIS_UNIT eu ON up.EMIS_UNIT_ID = eu.EMIS_UNIT_ID INNER JOIN P_FAC_SITE fs ON eu.FAC_SITE_ID = fs.FAC_SITE_ID INNER JOIN P_FAC_EMIS_RPT fer ON fs.FAC_EMIS_RPT_ID = fer.FAC_EMIS_RPT_ID INNER JOIN M_AGN_RPT ar ON fer.AGN_RPT_ID = ar.AGN_RPT_ID INNER JOIN P_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(PublicProcessEmission)); return query.UniqueResult(); } } } }