using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;

namespace HRM.DA
{
    internal class LocationDA
    {
        #region Constructor

        private LocationDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, Location item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO Location(LocationID, code, description, parentID, CreatedBy, CreationDate, SequenceNo, Status,TIRE, payrolltypeid, Longitude, Latitude, AttendancePoint, descriptioninbangla, LatLongSetBy, LatLongSetDate, LatLongApproveStatus)" +
                " VALUES(%n, %s, %s, %n,%n, %d, %n, %n,%n,%n,%n,%n, %b, %u, %n, %d, %b)", item.ID, item.Code, item.Name, item.ParentID,
                item.CreatedBy, item.CreatedDate, item.Sequence, item.Status, item.Tier, item.PayrollTypeID, item.Longitude, item.Latitude, item.AttendancePoint, item.NameInBangla, item.LatLongSetBy, item.LatLongSetDate, item.LatLongApproveStatus);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, Location item)
        {
            string sql = SQLParser.MakeSQL(@"UPDATE Location SET code=%s, description=%s, parentID=%n,ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n, Longitude=%n, Latitude=%n, AttendancePoint=%b, descriptioninbangla=%u, LatLongSetBy=%n, LatLongSetDate=%d, LatLongApproveStatus=%b" +
                " WHERE LocationID=%n", item.Code, item.Name, item.ParentID, item.ModifiedBy, item.ModifiedDate,
                item.Sequence, item.Status, item.Longitude, item.Latitude, item.AttendancePoint, item.NameInBangla, item.LatLongSetBy, item.LatLongSetDate, item.LatLongApproveStatus ,item.ID);
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM Location");
        }

        internal static IDataReader Get(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM Location WHERE LocationID=%n", ID);
        }

        internal static IDataReader GetEmployeeLocation(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM Location WHERE LocationID IN(Select LocationID From Employee Where EmployeeID=%n)", ID);
        }
        internal static IDataReader Get(TransactionContext tc, string sCode)
        {
            return tc.ExecuteReader("SELECT * FROM Location WHERE Code=%s", sCode);
        }

        internal static IDataReader GetChilds(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM Location WHERE ParentID=%n Order By DESCRIPTION", ID);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                string sql =
                    SQLParser.MakeSQL(
                        "SELECT * FROM Location Where   Status=%n and payrollTypeid=%n  Order By DESCRIPTION", status,
                        payrollTypeID);
                return tc.ExecuteReader(sql);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Location Where  payrollTypeid=%n Order By DESCRIPTION",
                    payrollTypeID);
            }
        }


        internal static IDataReader GetParent(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                string sql =
                    SQLParser.MakeSQL(
                        "SELECT * FROM Location parentid is null Where and Status=%n  Order By DESCRIPTION", status,
                        payrollTypeID);
                return tc.ExecuteReader(sql);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM Location Where parentid is null Order By DESCRIPTION",
                    payrollTypeID);
            }
        }

        internal static IDataReader GetByTier(TransactionContext tc, int tier)
        {
            return tc.ExecuteReader("SELECT * FROM Location Where Tire=%n Order by Description", tier);
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int ID)
        {
            tc.ExecuteNonQuery("DELETE FROM Location WHERE LocationID=%n", ID);
        }

        #endregion

        internal static IDataReader Get(TransactionContext tc, EnumStatus sts)
        {
            string sql = SQLParser.MakeSQL("Select * From Location where Status = %n", sts);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetForJV(TransactionContext tc, DateTime dSalaryMonth, int payrollTypeID)
        {
            string sql =
                SQLParser.MakeSQL(
                    "Select * from Location where LocationID in(Select Distinct LocationID from SalaryMonthly where PayrollTypeID=%n and SalaryMonth=%d) order by Code",
                    payrollTypeID, dSalaryMonth);
            return tc.ExecuteReader(sql);
        }

        internal static IDataReader GetAllLocation(TransactionContext tc, object payrollTypeID, EnumStatus status,
            string code, string name)
        {
            string sqlClause = string.Empty;
            sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
            }

            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
            }

            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM Location %q ", sqlClause);
        }
        internal static IDataReader GetAllLocation(TransactionContext tc, string code, string name)
        {
            string sqlClause = string.Empty;
            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM Location %q ", sqlClause);
        }
        internal static IDataReader GetByEmployeeID(TransactionContext tc, int employeeID)
        {
            string query = SQLParser.MakeSQL(@"SELECT l.* FROM EMPLOYEE e,Location l " +
                                    "WHERE e.LOCATIONID = l.LOCATIONID " +
                                    "AND e.EMPLOYEEID = %n", employeeID);
            return tc.ExecuteReader(query);
        }
        internal static DataSet GetLocationFromCoord(TransactionContext tc, double Lat, double Long)
        {
            DataSet locations = new DataSet();
            try
            {
                string sql = string.Empty;

                //sql = SQLParser.MakeSQL(@"
                //                        DECLARE
                //                        @GEO1 GEOGRAPHY,
                //                        @LAT VARCHAR(10),
                //                        @LONG VARCHAR(10)

                //                        SET @LAT=%s
                //                        SET @LONG=%s

                //                        SET @geo1= geography::Point(@LAT, @LONG, 4326)

                //                        SELECT LOCATIONID,CODE, DESCRIPTION,LEFT(CONVERT(VARCHAR,
                //                        (@geo1.STDistance(geography::Point(ISNULL(Latitude,0),
                //                        ISNULL(Longitude,0), 4326)))/1000),5)+' Km'
                //                        as DISTANCE from LOCATION
                //                        WHERE (@geo1.STDistance(geography::Point(ISNULL(Latitude,0),
                //                        ISNULL(Longitude,0), 4326)))/1000 < 0.2", Lat, Long);

                sql = SQLParser.MakeSQL(@"
                                        DECLARE @GEO1 GEOGRAPHY,
                                         @LAT DECIMAL(9, 6),
                                         @LONG DECIMAL(9, 6),
                                         @DistanceThreshold FLOAT

                                        SET @LAT = %s
                                        SET @LONG = %s
                                        SET @DistanceThreshold = 0.2

                                        SET @geo1 = geography::Point(@LAT, @LONG, 4326)

                                        DECLARE @DistanceInMeters FLOAT
                                        SET @DistanceInMeters = @DistanceThreshold * 1000

                                        DECLARE @MinLatitude DECIMAL(9, 6)
                                        DECLARE @MaxLatitude DECIMAL(9, 6)
                                        DECLARE @MinLongitude DECIMAL(9, 6)
                                        DECLARE @MaxLongitude DECIMAL(9, 6)

                                        SET @MinLatitude = @LAT - @DistanceThreshold / 111.2
                                        SET @MaxLatitude = @LAT + @DistanceThreshold / 111.2
                                        SET @MinLongitude = @LONG - @DistanceThreshold / (111.2 * COS(RADIANS(@LAT)))
                                        SET @MaxLongitude = @LONG + @DistanceThreshold / (111.2 * COS(RADIANS(@LAT)))

                                        SELECT LOCATIONID, CODE, DESCRIPTION,
                                               LEFT(CONVERT(VARCHAR, (@geo1.STDistance(geography::Point(ISNULL(Latitude, 0), ISNULL(Longitude, 0), 4326))) / 1000), 5) + ' Km' AS DISTANCE
                                        FROM LOCATION With(NoLock)
                                        WHERE Latitude BETWEEN @MinLatitude AND @MaxLatitude
                                        AND Longitude BETWEEN @MinLongitude AND @MaxLongitude
                                        AND(@geo1.STDistance(geography::Point(ISNULL(Latitude, 0), ISNULL(Longitude, 0), 4326))) <= @DistanceInMeters", Lat, Long);


                locations = tc.ExecuteDataSet(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return locations;
        }
        internal static DataTable GetLocationAllTier(TransactionContext tc, int locationID)
        {
            DataSet presentDays = new DataSet();
            string sql = SQLParser.MakeSQL(@"SELECT l1.DESCRIPTION AS Child, 
                                            l2.DESCRIPTION AS MidChild, 
                                            l3.DESCRIPTION AS Parent FROM LOCATION l1
                                            LEFT JOIN LOCATION l2
                                            ON l1.PARENTID = l2.LOCATIONID
                                            LEFT JOIN LOCATION l3
                                            ON l2.PARENTID = l3.LOCATIONID
                                            WHERE l1.LOCATIONID = %n", locationID);
            presentDays = tc.ExecuteDataSet(sql);

            return presentDays.Tables[0];
        }
        internal static void SaveLocation(TransactionContext tc, int locationID, double latitude, double longitude, int userid, bool latLongApproveStatus)
        {
            tc.ExecuteNonQuery("UPDATE Location SET latitude=%n, longitude=%n, LatLongSetBy=%n, LatLongSetDate=%d, LatLongApproveStatus=%b" +
            " WHERE LocationID=%n", latitude, longitude, userid, DateTime.Now, latLongApproveStatus, locationID);
        }
        internal static IDataReader GetLowerTierLocations(TransactionContext tc, int LocationID)
        {
            string sql = SQLParser.MakeSQL("SELECT * FROM LOCATION WHERE PARENTID = %n and AttendancePoint = 1", LocationID);

            return tc.ExecuteReader(sql);
        }
    }
}