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 Spring.Data.Common;
using System.Data;

namespace Sleis.Data
{
    public class AgencyUnitProcessData : BaseData<AgencyUnitProcess>, Sleis.Data.IUnitProcessData
    {
		public List<UnitProcess> GetUnitProcessesByFacility(int facilityId)
        {
            using(ISession session= GetSession())
            {
                var tmp = (from d in session.Query<AgencyEmissionUnit>()
						   join r in session.Query<AgencyUnitProcess>()
                           on d.Id equals r.EmissionUnitId
                           where d.FacilityId == facilityId
                           select r);
				List<UnitProcess> list = tmp.Distinct<AgencyUnitProcess>().ToList<UnitProcess>();
                return list;
            }
        }
        public List<UnitProcess> GetUnitProcessesByAgencyReport(int reportId)
        {
            using (ISession session = GetSession())
            {
                var tmp = (from d in session.Query<AgencyEmissionUnit>()
                           join r in session.Query<AgencyUnitProcess>()
                           on d.Id equals r.EmissionUnitId
                           join f in session.Query<AgencyFacilityModel>()
                           on d.FacilityId equals f.Id
                           where f.ReportId == reportId
                           select r);
                List<UnitProcess> list = tmp.Distinct<AgencyUnitProcess>().ToList<UnitProcess>();
                return list;
            }
        }
		public UnitProcess GetUnitProcess(int id)
        {

            using (ISession session = GetSession())
            {
				return (from d in session.Query<AgencyUnitProcess>()
                        where d.Id == id
						select d).FirstOrDefault().As<UnitProcess>();
            }
        }

        public List<string> GetRelatedUnitProcessIdentifiers(int controlDeviceId)
        {
            using (ISession session = GetSession())
            {
				return (from d in session.Query<AgencyUnitProcess>()
                        join e in session.Query<AgencyUnitProcessControlApproach>()
                        on d.Id equals e.UnitProcessId
                        join f in session.Query<AgencyUnitProcessControlDevice>()
                        on e.Id equals f.ControlApproachId
                        join g in session.Query<AgencyEmissionUnit>()
                        on d.EmissionUnitId equals g.Id
						join h in session.Query<AgencyFacilityModel>()
                        on g.FacilityId equals h.Id                        
                        where f.ControlDeviceId == controlDeviceId
                        select d.Identifier).Distinct<string>().ToList();
            }

        }
        public List<UnitProcess> GetUnitProcessesByControlDevice(int controlDeviceId)
        {
            using (ISession session = GetSession())
            {
                return (from d in session.Query<AgencyUnitProcess>()
                        join e in session.Query<AgencyUnitProcessControlApproach>()
                        on d.Id equals e.UnitProcessId
                        join f in session.Query<AgencyUnitProcessControlDevice>()
                        on e.Id equals f.ControlApproachId                        
                        where f.ControlDeviceId == controlDeviceId
                        select d).Distinct().ToList<UnitProcess>();
            }

        }

        public List<string> GetUnitProcessIdentifiers(int emissionUnitId)
        {
			return (from d in GetSession().Query<AgencyUnitProcess>()
                    where d.EmissionUnitId == emissionUnitId
                    select d.Identifier).Distinct<string>().ToList();
        }
        public List<UnitProcess> GetUnitProcessesByEmissionUnit(int emissionUnitId)
        {
            return (from d in GetSession().Query<AgencyUnitProcess>()
                    where d.EmissionUnitId == emissionUnitId
                    select d).Distinct().ToList<UnitProcess>();
        }
		public List<UnitProcess> GetReleasePointUnitProcesses(int reportId, int releasePointId)
        {
            if (reportId > 0)
            {
                using (ISession session = GetSession())
                {
                    return (from uprp in session.Query<AgencyUnitProcessReleasePoint>()
                            join up in session.Query<AgencyUnitProcess>() on uprp.UnitProcessId equals up.Id
                            join rp in session.Query<AgencyReleasePoint>() on uprp.ReleasePointId equals rp.Id                            
                            where rp.Id == releasePointId
                            select up).Distinct<UnitProcess>().ToList();
                }
            }
                //reportId is null/0, therefor fetching master records. do not add joins
            else
            {
                using (ISession session = GetSession())
                {
                    return (from uprp in session.Query<AgencyUnitProcessReleasePoint>()
                            join up in session.Query<AgencyUnitProcess>() on uprp.UnitProcessId equals up.Id
                            join rp in session.Query<AgencyReleasePoint>() on uprp.ReleasePointId equals rp.Id                            
                            where rp.Id == releasePointId
                            select up).Distinct<UnitProcess>().ToList();
                }
            }
        }
		public UnitProcess GetByIdentifierAndEmissionUnitIdentifier(string identifier, string emissionUnitIdentifier, int agencyReportId)
        {
            using (ISession session = GetSession())
            {
				ISQLQuery query = session.CreateSQLQuery(@"SELECT up.UNIT_PROC_ID
                                                                  ,up.EMIS_UNIT_ID
                                                                  ,up.UNIT_PROC_IDEN
                                                                  ,up.E_SRC_CLASS_CD
                                                                  ,up.PROC_DESC
                                                                  ,up.PROC_CMNT                                                            
                                                            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
                                                                eu.EMIS_UNIT_IDEN = :emissionUnitIdentifier AND up.UNIT_PROC_IDEN = :unitProcessIden AND rp.REPORT_ID = :reportId");
                query.SetString("emissionUnitIdentifier", emissionUnitIdentifier);
                query.SetString("unitProcessIden", identifier);
                query.SetInt32("reportId", agencyReportId);
				query.AddEntity(typeof(AgencyUnitProcess));
				return query.UniqueResult<UnitProcess>();

            }
        }
        public bool CanDelete(int id)
        {
            string query = @"SELECT COUNT(*)
                            FROM M_UNIT_PROC
                            WHERE
                            UNIT_PROC_ID IN (SELECT UNIT_PROC_ID FROM P_UNIT_PROC WHERE MSTR_UNIT_PROC_ID = {0})
                            OR UNIT_PROC_ID IN (SELECT UNIT_PROC_ID FROM M_UNIT_PROC WHERE MSTR_UNIT_PROC_ID = {0})";
            query = String.Format(query, CreateParameterName("unit_process_id"));
            IDbParameters args = AdoTemplate.CreateDbParameters();
            args.Add("unit_process_id", DbType.Int32).Value = id;
            return Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, query, args)) == 0;
        }
		public bool IsIdentifierUnique(UnitProcess unitProcess)
		{
			using (ISession session = GetSession())
			{
				return (from up in session.Query<AgencyUnitProcess>()
                        where up.Identifier == unitProcess.Identifier && up.Id != unitProcess.Id && up.EmissionUnitId == unitProcess.EmissionUnitId
						select up).Count() == 0;
			}
		}
        
    }
}