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

namespace Sleis.Data
{
    public class AgencyControlDeviceData : BaseData, IControlDeviceData
    {
        public List<ControlDeviceModel> GetControlDevicesByFacility(int facilityId)
        {
            using (ISession session = GetSession())
            {
                var list = from d in session.Query<AgencyControlDevice>()
                           where d.FacilityId == facilityId
                           select d;
                return list.ToList<ControlDeviceModel>();
            }
        }
        public List<ControlDeviceModel> GetControlDevicesByReport(int reportId)
        {
            using (ISession session = GetSession())
            {
                return (from cd in session.Query<AgencyControlDevice>()
                        join f in session.Query<AgencyFacilityModel>()
                         on cd.FacilityId equals f.Id                        
                        where f.ReportId == reportId
                        select cd).ToList<ControlDeviceModel>();
            }
        }
        /*
       
        public List<PublicControlDevice> GetControlDevicesByEmissionUnitId(int emissionUnitId)
        {
            using (ISession session = GetSession())
            {
                return (from d in session.Query<PublicEmissionUnitControlApproach>()
                        where d.EmissionUnitId == emissionUnitId
                        join e in session.Query<PublicEmissionUnitControlDevice>()
                        on d.Id equals e.DeviceApproachId
                        join f in session.Query<PublicControlDeviceModel>()
                        on e.ControlDevice.DeviceId equals f.Id
                        select f).Distinct().ToList();
            }
        }

        */
        public List<string> GetControlDeviceIdentifiersByUnitProcessId(int unitProcessId)
        {
            using (ISession session = GetSession())
            {
                return (from ca in session.Query<AgencyUnitProcessControlApproach>()                        
                        join ucd in session.Query<AgencyUnitProcessControlDevice>() on ca.Id equals ucd.ControlApproachId
                        join cd in session.Query<AgencyControlDevice>() on ucd.ControlDeviceId equals cd.Id
                        where ca.UnitProcessId == unitProcessId
                        select cd.Identifier).Distinct<string>().ToList();
            }
        }
        
        public List<ControlDeviceModel> GetControlDevicesByUnitProcess(int unitProcessId)
        {
            using (ISession session = GetSession())
            {
                return (from ca in session.Query<AgencyUnitProcessControlApproach>()
                        join ucd in session.Query<AgencyUnitProcessControlDevice>() on ca.Id equals ucd.ControlApproachId
                        join cd in session.Query<AgencyControlDevice>() on ucd.ControlDeviceId equals cd.Id
                        where ca.UnitProcessId == unitProcessId
                        select cd).Distinct().ToList<ControlDeviceModel>();
            }
        }
        public List<ControlDeviceModel> GetControlDevicesByEmissionUnit(int emissionUnitId)
        {
            using (ISession session = GetSession())
            {
                return (from ca in session.Query<AgencyEmissionUnitControlApproach>()
                        join ucd in session.Query<AgencyEmissionUnitControlDevice>() on ca.Id equals ucd.ControlApproachId
                        join cd in session.Query<AgencyControlDevice>() on ucd.ControlDeviceId equals cd.Id
                        where ca.EmissionUnitId == emissionUnitId
                        select cd).Distinct().ToList<ControlDeviceModel>();
            }
        }
        
        public bool CanDelete(int id)
        {
            string query = @"SELECT Count(*)
                            FROM M_CTRL_DEVC
                            WHERE
                            CTRL_DEVC_ID IN (SELECT CTRL_DEVC_ID FROM P_CTRL_DEVC WHERE MSTR_CTRL_DEVC_ID = {0})
                            OR CTRL_DEVC_ID IN (SELECT CTRL_DEVC_ID FROM M_CTRL_DEVC WHERE MSTR_CTRL_DEVC_ID = {0})";
            query = String.Format(query, CreateParameterName("control_device_id"));            
            IDbParameters args = AdoTemplate.CreateDbParameters();            
            args.Add("control_device_id", DbType.Int32).Value = id;
            return Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, query, args))==0;
        }
		public bool IsIdentifierUnique(ControlDeviceModel controlDevice)
		{
			using (ISession session = GetSession())
			{
				return (from cd in session.Query<AgencyControlDevice>()
						where cd.Identifier == controlDevice.Identifier && cd.Id != controlDevice.Id && controlDevice.FacilityId==cd.FacilityId
						select cd).Count() == 0;
			}
		}
    }
}