using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NHibernate;
using NHibernate.Linq;
using Sleis.Models;
using Sleis.Infrastructure;
using NHibernate.Transform;
using Spring.Data.Common;
using System.Data;

namespace Sleis.Data
{
	public class PublicReportData : BaseData<PublicReportModel>, IReportData
	{
        public int GetSubmittedReportsCount()
        {
            using (ISession session = GetSession())
            {
                ISQLQuery query = session.CreateSQLQuery(@" SELECT COUNT (1) as count
															FROM M_AGN_RPT 
															WHERE RPT_RCVD_DATE IS NOT NULL AND RPT_LOAD_DATE IS NULL");
                return query.UniqueResult<int>();
            }
        }
		public List<SubmittedReports> GetSubmittedReports()
		{
			throw new System.NotImplementedException();
		}
		public int LoadSubmittedReports(List<int> SelectedReports, out bool isError)
		{
			throw new System.NotImplementedException();
		}
		public List<int> GetStageableReportingYears()
		{
			throw new System.NotImplementedException();
		}
		public bool CompleteReview(int reportId)
		{
			throw new System.NotImplementedException();
		}
		public bool StartReport(int reportId)
		{
            /*
			bool result = false;
			using (ISession session = GetSession())
			{
				//Second argument indicates public vs agency report id. 
				ISQLQuery query = session.CreateSQLQuery(@"
					DECLARE @v_err_code int;

					EXECUTE @v_err_code = P_START_PUB_RPT :reportId, 1

					SELECT @v_err_code;");
				query.SetInt32("reportId", reportId);
				result = (query.UniqueResult<int>() == 0);

			}

			return result;
             */
            IDbParameters args = AdoTemplate.CreateDbParameters();
            //query.SetDecimal("result", 0);            
            args.Add("p_agn_rpt_id", DbType.Int32).Value =reportId; //reportId
            args.Add("p_is_public_report", DbType.Int32).Value = 1; //is public or agency report            
            args.AddInOut("p_err_code", DbType.Decimal);
            AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_START_PUB_RPT", args);
            var rawResults = GetOutParamValue("p_err_code", args);
            Log.Debug("P_START_PUB_RPT - P_ERR_CODE = " + rawResults.ToString());
            return Convert.ToInt32(rawResults)==0;
            
		}

		public List<TotalEmissionsReportModel> GetTotalEmissionsByFacility(int reportId)
		{
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"
					SELECT                        
						rpe.E_POLLUTANT_CD as PollutantCode,                        
						p.POLLUTANT_DESC as PollutantDescription,
						SUM(rpe.TOTAL_EMIS) as TotalEmissions
					FROM
						P_RPT_PRD_EMIS rpe
						INNER JOIN P_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN P_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						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 E_POLLUTANT p ON rpe.E_POLLUTANT_CD = p.E_POLLUTANT_CD
					WHERE
						ar.AGN_RPT_ID = :reportId
					GROUP BY
						rpe.E_POLLUTANT_CD,
						p.POLLUTANT_DESC
					ORDER BY
						p.POLLUTANT_DESC");
				query.SetInt32("reportId", reportId);
                query.AddScalar("PollutantCode", NHibernateUtil.String);
                query.AddScalar("PollutantDescription", NHibernateUtil.String);
                query.AddScalar("TotalEmissions", NHibernateUtil.Double);                
				query.SetResultTransformer(Transformers.AliasToBean(typeof(TotalEmissionsReportModel) ));                
				return query.List<TotalEmissionsReportModel>().ToList();
			}
		}

		public List<TotalEmissionsReportModel> GetTotalEmissionsByReleasePoint(int reportId)
		{
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"
					SELECT
						rel.REL_PT_IDEN AS Identifier,
						rel.REL_PT_DESC AS Description,
						rpe.E_POLLUTANT_CD AS PollutantCode,
						p.POLLUTANT_DESC as PollutantDescription,
						SUM(rpe.TOTAL_EMIS * (uprp.AVG_PCT_EMIS / 100)) AS TotalEmissions
					FROM
						P_RPT_PRD_EMIS rpe
						INNER JOIN P_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN P_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						INNER JOIN P_UNIT_PROC_REL_PT uprp ON up.UNIT_PROC_ID = uprp.UNIT_PROC_ID
						INNER JOIN P_REL_PT rel ON uprp.REL_PT_ID = rel.REL_PT_ID
						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 E_POLLUTANT p ON rpe.E_POLLUTANT_CD = p.E_POLLUTANT_CD
					WHERE
						ar.AGN_RPT_ID = :reportId
					GROUP BY
						rel.REL_PT_IDEN,
						rel.REL_PT_DESC,
						rpe.E_POLLUTANT_CD,
						p.POLLUTANT_DESC
					ORDER BY
						rel.REL_PT_IDEN,
						p.POLLUTANT_DESC");
				query.SetInt32("reportId", reportId);
                query.AddScalar("PollutantCode", NHibernateUtil.String);
                query.AddScalar("PollutantDescription", NHibernateUtil.String);
                query.AddScalar("TotalEmissions", NHibernateUtil.Double);
                query.AddScalar("Description", NHibernateUtil.String);
                query.AddScalar("Identifier", NHibernateUtil.String);
				query.SetResultTransformer(Transformers.AliasToBean(typeof(TotalEmissionsReportModel)));
				return query.List<TotalEmissionsReportModel>().ToList();
			}
		}

		public List<TotalEmissionsReportModel> GetTotalEmissionsByEmissionUnit(int reportId)
		{
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"
					SELECT
						eu.EMIS_UNIT_IDEN AS Identifier,
						eu.UNIT_DESC AS Description,
						rpe.E_POLLUTANT_CD as PollutantCode,
						p.POLLUTANT_DESC as PollutantDescription,
						SUM(rpe.TOTAL_EMIS) as TotalEmissions
					FROM
						P_RPT_PRD_EMIS rpe
						INNER JOIN P_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN P_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						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 E_POLLUTANT p ON rpe.E_POLLUTANT_CD = p.E_POLLUTANT_CD
					WHERE
						ar.AGN_RPT_ID = :reportId
					GROUP BY
						eu.EMIS_UNIT_IDEN,
						eu.UNIT_DESC,
						rpe.E_POLLUTANT_CD,
						p.POLLUTANT_DESC
					ORDER BY
						eu.EMIS_UNIT_IDEN,
						p.POLLUTANT_DESC");
				query.SetInt32("reportId", reportId);
                query.AddScalar("PollutantCode", NHibernateUtil.String);
                query.AddScalar("PollutantDescription", NHibernateUtil.String);
                query.AddScalar("TotalEmissions", NHibernateUtil.Double);
                query.AddScalar("Description", NHibernateUtil.String);
                query.AddScalar("Identifier", NHibernateUtil.String);
				query.SetResultTransformer(Transformers.AliasToBean(typeof(TotalEmissionsReportModel)));
				return query.List<TotalEmissionsReportModel>().ToList();
			}
		}
        public int GenerateEmissionsReport(AgencyReport report)
        {
            int count = 0;
            using (ISession session = GetSession())
            {
                using (ITransaction tran = session.BeginTransaction())
                {
                    AgencyReport existingReport = (from d in session.Query<AgencyReport>()
                                                   where d.Number == report.Number && d.FacilityId == report.FacilityId
                                                   select d).FirstOrDefault<AgencyReport>();
                    if (existingReport == null)
                    {
                        Create(report, session);
                        tran.Commit();
                        ++count;
                    }
                }
            }
            return count;
        }
    }
}