using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.Location
{
    public static class LocationQB
    {
        public const string GET_LOCATION = @"
SELECT 
    ML.LOCATIONID AS LocationId,
    ML.LOCATIONCODE AS LocationCode,
    ML.LOCATIONNAME AS LocationName,
    ML.REMARKS AS LocationRemarks,
    ML.SORTORDER AS LocationSortOrder,
    ML.STATUS AS LocationStatus,
    ML.VERSION AS LocationVersion,
    ML.SOURCETYPE AS LocationSourceType,
    ML.CREATEDBYID AS LocationCreatedById,
    ML.CREATEDON AS LocationCreatedOn,
    ML.MODIFIEDBYID AS LocationModifiedById,
    ML.MODIFIEDON AS LocationModifiedOn,
    ML.CALENDARID AS LocationCalendarId,
   ML.GEOLOCATION.STAsText() AS LocationGeolocation, -- Converts geography to WKT string
    ML.BORDER.STAsText() AS LocationBorder,
    ML.GEOCODE AS LocationGeoCode,
    ML.GEODATATYPE AS LocationGeoDataType,
    ML.SHAPETYPE AS LocationShapeType,
    ML.RADIUS AS LocationRadius,
    ML.BOUNDS AS LocationBounds,
    ML.COORDINATES AS LocationCoordinates,

    CU.USERNAME AS CreatedByUserName,
    MU.USERNAME AS ModifiedByUserName,
    MC.CALENDARCODE AS CalendarCode,
    MC.CALENDARNAME AS CalendarName

FROM MLOCATION ML
LEFT JOIN MUSER CU ON ML.CREATEDBYID = CU.USERID
LEFT JOIN MUSER MU ON ML.MODIFIEDBYID = MU.USERID
LEFT JOIN MCALENDAR MC ON ML.CALENDARID = MC.CALENDARID
WHERE ML.LOCATIONID = @locationid;
";


        public const string SAVE_LOCATION = @"INSERT 
                            INTO MLOCATION (
                                 LOCATIONID,
                                 LOCATIONCODE,
                                 LOCATIONNAME,
                                 REMARKS,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 SOURCETYPE,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 CALENDARID,
                                 GEOLOCATION,
                                 BORDER,
                                 GEOCODE,
                                 GEODATATYPE,
                                 SHAPETYPE,
                                 RADIUS,
                                 BOUNDS,
                                 COORDINATES
                            ) VALUES (
                                 @LocationId,
                                 @LocationCode,
                                 @LocationName,
                                 @LocationRemarks,
                                 @LocationSortOrder,
                                 @LocationStatus,
                                 @LocationVersion,
                                 @LocationSourceType,
                                 @LocationCreatedById,
                                 @LocationCreatedOn,
                                 @LocationModifiedById,
                                 @LocationModifiedOn,
                                 @LocationCalendarId,
                                 @LocationGeolocation,
                                 @LocationBorder,
                                 @LocationGeoCode,
                                 @LocationGeoDataType,
                                 @LocationShapeType,
                                 @LocationRadius,
                                 @LocationBounds,
                                 @LocationCoordinates
                            );
                            ";

        public const string UPDATE_LOCATION = @"UPDATE 
                            MLOCATION SET
                            LOCATIONCODE = @LocationCode,
                            LOCATIONNAME = @LocationName,
                            REMARKS = @LocationRemarks,
                            SORTORDER = @LocationSortOrder,
                            STATUS = @LocationStatus,
                            VERSION = @LocationVersion,
                            SOURCETYPE = @LocationSourceType,
                            MODIFIEDBYID = @LocationModifiedById,
                            MODIFIEDON = @LocationModifiedOn,
                            CALENDARID = @LocationCalendarId,
                            GEOLOCATION = @LocationGeolocation,
                            BORDER = @LocationBorder,
                            GEOCODE = @LocationGeoCode,
                            GEODATATYPE = @LocationGeoDataType,
                            SHAPETYPE = @LocationShapeType,
                            RADIUS = @LocationRadius,
                            BOUNDS = @LocationBounds,
                            COORDINATES = @LocationCoordinates
                            WHERE LOCATIONID = @LocationId;
                            ";

        public const string DELETE_LOCATION = @"DELETE 
                            FROM MLOCATION
                            WHERE LOCATIONID = @locationid;
                            ";

        public const string GET_SELECTLIST_LOCATION = @"
                            WITH PagedLocation AS (
                            SELECT 
                            L.LOCATIONID AS Id,
                            L.LOCATIONCODE AS Code,
                            L.LOCATIONNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY L.LOCATIONID) AS RowNum
                            FROM MLOCATION L
                            )
                            SELECT
                            Id,
                            Code,
                            Name
                            FROM PagedLocation
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}