using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
    #region Organogram DA

    internal class OrganogramDA
    {
        #region  Constructor

        private OrganogramDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, OrganogramBasic item)
        {
            string Ssql = SQLParser.MakeSQL("INSERT INTO Organogram(OrganogramID, PositionName, ParentID, Tier, UniqueId,"
            + " PositionNo,CompanyID,DepartmentID,FunctionID,"
            + " GradeID,DesignationID,LocationID,CreatedBy,"
            + " CreationDate, SequenceNo, Status,JDNo,RevisionNo,RevisionDate)"
            + " VALUES(%n, %s, %n, %n, %n,"
            + " %s, %n, %n,%n,"
            + " %n,%n,%n,%n,"
            + " %d,%n,%n,%s,%s,%d)",
            item.ID.Integer, item.PositionName, DataReader.GetNullValue(item.ParentID, IDType.Integer), item.Tier, item.UniqueId,
            item.PositionNo, item.CompanyID.Integer, item.DepartmentID.Integer, item.FunctionID.Integer,
            item.GradeID.Integer, item.DesignationID.Integer, item.LocationID.Integer, item.CreatedBy.Integer,
            item.CreatedDate, item.Sequence, item.Status, item.JDNo, item.RevisionNo, DataReader.GetNullValue(item.RevisionDate));

            tc.ExecuteNonQuery(Ssql);
        }

        internal static void Insert(TransactionContext tc, OrganogramAuthority item)
        {
            tc.ExecuteNonQuery("INSERT INTO OrganAuthority(OrganAuthorityID, NodeID, AuthorityType, ChildNodeID, EmployeeID)"
            + " VALUES(%n, %n, %n, %n, %n)",
            item.ID.Integer,item.NodeID.Integer,item.AuthorityType,item.ChildNodeID.Integer,DataReader.GetNullValue(item.EmployeeID, IDType.Integer));
        }

        internal static int GetMaxTier(TransactionContext tc)
        {
            object minID = tc.ExecuteScalar("SELECT Max(Tier) FROM Organogram");
            if (minID == null || minID == DBNull.Value)
                return 0;
            else
                return Convert.ToInt32(minID);
        }
        internal static void Insert(TransactionContext tc, OrganogramDelegation item)
        {
            tc.ExecuteNonQuery("INSERT INTO OrganDelegation(OrganDelegationID,NodeID,EmployeeID,"
                + " FromDate,ToDate,DelegationType,IsActive,CreatedBy,CreationDate)"
                + " VALUES(%n,%n,%n,"
                + " %d,%d,%s,%n,%n,%d)",
                item.ID.Integer, item.NodeID.Integer, item.EmployeeID.Integer,
                item.FromDate, item.ToDate, item.IsActive, DataReader.GetNullValue(item.CreatedBy, IDType.Integer), item.CreatedDate);
        }

        internal static void Insert(TransactionContext tc, OrganogramJobDesc item)
        {
            tc.ExecuteNonQuery("INSERT INTO OrganJobDescription(OrganJobDescID,NodeID,Purpose,"
                + " JobSummery,Experience,Education,CreatedBy,CreationDate)"
                + " VALUES(%n,%n,%s,"
                + " %s,%s,%s,%n,%d)",
                item.ID.Integer, item.NodeID.Integer, item.Purpose,
                item.JobSummery, item.Experience, item.Education,item.CreatedBy.Integer,item.CreatedDate);
        }

        internal static void Insert(TransactionContext tc, OrganogramResponsibility item)
        {
            //,CreatedBy,CreationDate ,%n,%d ,item.CreatedBy.Integer,item.CreatedDate
            tc.ExecuteNonQuery("INSERT INTO OrganResponsibility(OrganResponsibilityID,NodeID,Responsibility,Isprimary)"
            + " VALUES(%n,%n,%s,%n)",
            item.ID.Integer, item.NodeID.Integer, item.Responsibility, item.IsPrimary);
        }

        internal static void Insert(TransactionContext tc, OrganogramSkillLevel item)
        {
            //,CreatedBy,CreationDate ,%n,%d ,item.CreatedBy.Integer,item.CreatedDate
            tc.ExecuteNonQuery("INSERT INTO OrganSkillLevel(OrganSkillLevelID,NodeID,LevelID,SkillID)"
            + " VALUES(%n,%n,%n,%n)",
            item.ID.Integer,item.NodeID.Integer,item.LevelID.Integer,item.SkillID.Integer);
        }

        internal static void Insert(TransactionContext tc, OrganogramPosition item)
        {
            tc.ExecuteNonQuery("INSERT INTO OrganPosition(OrganPositionID,NodeID,PositionTypeID)"
            + " VALUES(%n,%n,%n)",
            item.ID.Integer, item.NodeID.Integer, item.PositionTypeID.Integer);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, OrganogramBasic item)
        {
            tc.ExecuteNonQuery("UPDATE Organogram SET PositionName=%s, ParentID=%n, Tier=%n, UniqueId=%n,"
            + " PositionNo=%s,CompanyID=%n,DepartmentID=%n,FunctionID=%n,"
            + " GradeID=%n,DesignationID=%n,LocationID=%n, ModifiedBy=%n,"
            + " ModifiedDate=%d, SequenceNo=%n, Status=%n,JDNo=%s,RevisionNo=%s,RevisionDate=%d"
            + " WHERE OrganogramID=%n",
            item.PositionName, DataReader.GetNullValue(item.ParentID,IDType.Integer) , item.Tier, item.UniqueId,
            item.PositionNo, DataReader.GetNullValue(item.CompanyID, IDType.Integer), DataReader.GetNullValue(item.DepartmentID, IDType.Integer), DataReader.GetNullValue(item.FunctionID, IDType.Integer),
            DataReader.GetNullValue(item.GradeID, IDType.Integer), DataReader.GetNullValue(item.DesignationID, IDType.Integer),DataReader.GetNullValue(item.LocationID, IDType.Integer) , item.ModifiedBy.Integer,              
            item.ModifiedDate, item.Sequence, item.Status,item.JDNo,item.RevisionNo,
            DataReader.GetNullValue(item.RevisionDate), item.ID.Integer);
        }

        public static void UpdateParentAndTier(TransactionContext tc, OrganogramBasic oItem)
        {
            tc.ExecuteNonQuery("UPDATE Organogram SET Tier=%n, ParentID=%n"
              + " WHERE OrganogramID=%n", oItem.Tier,
              DataReader.GetNullValue(oItem.ParentID, IDType.Integer), oItem.ID.Integer);
        }

        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
            tc.ExecuteNonQuery("DELETE FROM [Organogram] WHERE OrganogramID=%n", nodeID.Integer);
        }


        internal static void DeleteOrganAuthority(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
        }

        internal static void DeleteOrganDelegation(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
        }

        internal static void DeleteOranJobDesc(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
        }

        internal static void DeleteOrganResponsibility(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
        }

        internal static void DeleteOrganSkilLevel(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
        }

        internal static void DeleteOrganPosition(TransactionContext tc, ID nodeID)
        {
            tc.ExecuteNonQuery("DELETE FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
        }


        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM Organogram WHERE OrganogramID=%n", nID.Integer);
        }

        internal static IDataReader GetbyEmployeeID(TransactionContext tc, ID employeeId)
        {
            return tc.ExecuteReader("SELECT O.* FROM Organogram O, OrganEmployee A WHERE A.NodeID=O.OrganogramID AND A.EmployeeID =%n", employeeId.Integer);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Organogram where Status=%n order by OrganogramID", status);
            }
            else return tc.ExecuteReader("SELECT * FROM Organogram order by OrganogramID");
        }

        internal static IDataReader GetByParentID(TransactionContext tc, int parentID,EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                if (parentID > 0)
                    return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID=%n AND Status=%n", parentID,status);
                else
                    return tc.ExecuteReader("SELECT * FROM Organogram WHERE Status=%n AND ParentID is Null",status);

            }
            else
            {
                if (parentID > 0)
                    return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID=%n", parentID);
                else
                    return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID is Null");            
            }
        }


        public static IDataReader GetTopParents(TransactionContext tc, EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID IS NULL AND Status=%n", status);
            }
            else
                return tc.ExecuteReader("SELECT * FROM Organogram WHERE ParentID IS NULL");
        }


        internal static IDataReader Get4OrganAuthority(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganAuthority WHERE NodeID=%n", nodeID.Integer);
        }

        internal static IDataReader Get4OrganDelegation(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganDelegation WHERE NodeID=%n", nodeID.Integer);
        }

        internal static IDataReader Get4OrganJobDesc(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganJobDescription WHERE NodeID=%n", nodeID.Integer);
        }

        internal static IDataReader Get4OrganResponsibility(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganResponsibility WHERE NodeID=%n", nodeID.Integer);
        }

        internal static IDataReader Get4OrganSkilLevel(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganSkillLevel WHERE NodeID=%n", nodeID.Integer);
        }

        internal static IDataReader Get4OrganPosition(TransactionContext tc, ID nodeID)
        {
            return tc.ExecuteReader("SELECT * FROM OrganPosition WHERE NodeID=%n", nodeID.Integer);
        }
        internal static IDataReader GetAllOrganPosition(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM OrganPosition");
        }
        internal static IDataReader GetMinNodeIdByDept(TransactionContext tc, ID nDeptID)
        {
            return tc.ExecuteReader("select * from Organogram where OrganogramID in (select Min(OrganogramID) from Organogram where DepartmentID =%n)", nDeptID.Integer);
        }
        internal static IDataReader GetbyPositionType(TransactionContext tc, ID positionTypeid)
        {
            return tc.ExecuteReader("Select Organogram.* from Organogram, OrganPosition where Organogram.OrganogramID = OrganPosition.NodeID and PositionTypeID =%n", positionTypeid.Integer);
        }
        internal static DataSet GetOrganogramNodes(TransactionContext tc, int nNodeID)
        {
            DataSet ds = new DataSet();
            try
            {
                ds = tc.ExecuteDataSet("WITH RecursiveCte AS " +
"(" +
    " SELECT 1 as Tier, H1.OrganogramID, H1.ParentId, H1.PositionName FROM Organogram H1" +
    " WHERE OrganogramID = %n" +
    " UNION ALL" +
    " SELECT RCTE.Tier + 1 as Tier, H2.OrganogramID, H2.ParentId, H2.PositionName FROM Organogram H2" +
    " INNER JOIN RecursiveCte RCTE ON H2.ParentId = RCTE.OrganogramID" +
" )" +
" SELECT a.OrganogramID,a.parentid,'Position : '+a.PositionName as PositionName,c.employeeid,c.employeeno,c.employeeno+'('+c.name+')' as noname,'Name : '+c.name as name,'Designation : '+d.name as designation ,c.photopath,c.Gender" +
" FROM RecursiveCte a" +
" left outer join  organemployee b on a.OrganogramID=b.nodeid " +
" left outer join employee c on b.employeeid=c.employeeid " +
" left outer join designation d on c.designationid=d.designationid", nNodeID);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
        internal static DataSet GetLMOrganogramNodes(TransactionContext tc, int nNodeID)
        {
            DataSet ds = new DataSet();
            try
            {
                ds = tc.ExecuteDataSet("SELECT a.OrganogramID,a.parentid,'Position : '+a.PositionName as PositionName,c.employeeid,c.employeeno,c.employeeno+'('+c.name+')' as noname,'Name : '+c.name as name,'Designation : '+d.name as designation ,c.photopath,c.Gender FROM Organogram a   left outer join  organemployee b on a.OrganogramID=b.nodeid left outer join employee c on b.employeeid=c.employeeid left outer join designation d on c.designationid=d.designationid    where a.organogramid=%n Or parentid=%n", nNodeID, nNodeID);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
        #endregion            
                  
    }
    #endregion
}