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

namespace Sleis.Data
{
	public class ReportData : BaseData<AgencyReport>, IReportData
	{
		public List<int> GetStageableReportingYears()
		{
			using (ISession session = GetSession())
			{
				return (from a in session.Query<AgencyReport>()
						select a.Number).Distinct().ToList<int>();
			}
		}

		public bool CompleteReview(int reportId)
		{
			/*bool result = false;
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"
					DECLARE @v_err_code int;

					EXECUTE @v_err_code = P_PROMOTE_RPT :reportId;

					SELECT @v_err_code;");
				query.SetInt32("reportId", reportId);
				result = (query.UniqueResult<int>() == 0);
			}
            */			
            IDbParameters args = AdoTemplate.CreateDbParameters();
            args.Add("p_agn_rpt_id", DbType.Int32).Value = reportId; //reportId            
            args.AddInOut("p_err_code", DbType.Decimal);
            AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_PROMOTE_RPT", args);
            var rawResults = GetOutParamValue("p_err_code", args);
            return Convert.ToInt32(rawResults) == 0;
		}

		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_AGN_RPT :reportId, 0
//
//					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 = 0; //is public or agency report 
            args.AddInOut("p_err_code", DbType.Decimal);
            AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_START_AGN_RPT", args);
            var rawResults = GetOutParamValue("p_err_code", args);
            Log.Debug("P_START_ARGN_RPT - P_ERR_CODE = " + rawResults.ToString());
            return Convert.ToInt32(rawResults) == 0;
		}

		public int GetSubmittedReportsCount()
		{
			using (ISession session = GetSession())
			{
                /*
				ISQLQuery query = session.CreateSQLQuery(@" SELECT COUNT (1) as count
															FROM M_AGN_RPT ar
                                                                 INNER JOIN P_FAC_EMIS_RPT pr ON pr.AGN_RPT_ID = ar.AGN_RPT_ID
															WHERE ar.RPT_RCVD_DATE IS NOT NULL AND ar.RPT_LOAD_DATE IS NULL AND pr.RPT_SUBM_DATE IS NOT NULL");
                
				//return query.UniqueResult<int>();
                 * */
                return (from a in session.Query<AgencyReport>()
                        join p in session.Query<PublicReportModel>()
                            on a.Id equals p.ReportId
                        where a.ReceivedDate != null && a.LoadDate == null && p.ReportSubmissionDate != null
                        select a).Distinct().Count();
			}
		}

		public List<SubmittedReports> GetSubmittedReports()
		{
			using (ISession session = GetSession())
			{
				return (from a in session.Query<AgencyReport>()
						join p in session.Query<PublicReportModel>()
							on a.Id equals p.ReportId
						join f in GetSession().Query<AgencyFacilityModel>()
							on a.FacilityId equals f.Id
						where a.ReceivedDate != null && a.LoadDate == null && p.ReportSubmissionDate!=null
						select new SubmittedReports(a.Id, f.FacName, a.Number, a.DueDate, p.ReportSubmissionDate)).ToList();
			}
		}

		public int LoadSubmittedReports(List<int> SelectedReports, out bool isError)
		{
           int result = 0;
           isError = false;
//            foreach (int reportId in SelectedReports)
//            {
//                using (ISession session = GetSession())
//                {
//                    ISQLQuery query = session.CreateSQLQuery(@"
//						DECLARE @v_err_code int;
//
//						EXECUTE @v_err_code = P_LOAD_PUB_RPT :reportId;
//
//						SELECT @v_err_code;");
//                    query.SetInt32("reportId", reportId);
//                    if (query.UniqueResult<int>() == 0)
//                    {
//                        ++result;
//                    }
//                    else
//                    {
//                        isError = true;
//                    }
//                }
//            }
//            return result;
            foreach (int reportId in SelectedReports)
            {
                IDbParameters args = AdoTemplate.CreateDbParameters();
                //query.SetDecimal("result", 0);            
                args.Add("p_agn_rpt_id", DbType.Int32).Value = reportId; //reportId                          
                args.AddInOut("p_err_code", DbType.Decimal);
                AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, "P_LOAD_PUB_RPT", args);
                var rawResults = GetOutParamValue("p_err_code", args);
                if (Convert.ToInt32(rawResults) == 0)
                {
                    ++result;
                }
                else
                {
                    isError = true;
                }
            }
            return result;
		}

		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
						M_RPT_PRD_EMIS rpe
						INNER JOIN M_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN M_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						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 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
						M_RPT_PRD_EMIS rpe
						INNER JOIN M_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN M_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						INNER JOIN M_UNIT_PROC_REL_PT uprp ON up.UNIT_PROC_ID = uprp.UNIT_PROC_ID
						INNER JOIN M_REL_PT rel ON uprp.REL_PT_ID = rel.REL_PT_ID
						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 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
						M_RPT_PRD_EMIS rpe
						INNER JOIN M_RPT_PRD rp ON rpe.RPT_PRD_ID = rp.RPT_PRD_ID
						INNER JOIN M_UNIT_PROC up ON rp.UNIT_PROC_ID = up.UNIT_PROC_ID
						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 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;
		}

	}
}