using System;

namespace FrameworkDAL.Query.ZIP
{
    public static class ZIPQB
    {
        public const string GET_SELECTLIST_ZIP = @"
SELECT
    Z.ZIPID          AS Id,
    Z.ZIPCODE        AS Code,
    Z.ZIPNAME        AS Name,

    C.CITYID         AS CityId,
    C.CITYCODE       AS CityCode,
    C.CITYNAME       AS CityName,

    S.STATEID        AS StateId,
    S.STATECODE      AS StateCode,
    S.STATENAME      AS StateName

FROM MZIP Z
JOIN MCITY C
    ON C.CITYID = Z.CITYID
JOIN MSTATE S
    ON S.STATEID = C.STATEID
ORDER BY Z.ZIPNAME";


        public const string GET_ZIP = @"
SELECT
    -- ZIP
    Z.ZIPID                          AS ZipId,
    Z.ZIPCODE                        AS ZipCode,
    Z.ZIPNAME                        AS ZipName,

    -- CITY
    Z.CITYID                         AS CityId,
    C.CITYCODE                       AS CityCode,
    C.CITYNAME                       AS CityName,

    -- STATE
    S.STATEID                        AS StateId,
    S.STATECODE                      AS StateCode,
    S.STATENAME                      AS StateName,

    -- COUNTRY
    CT.COUNTRYID                     AS CountryId,
    CT.COUNTRYCODE                   AS CountryCode,
    CT.COUNTRYNAME                   AS CountryName,


    -- ZIP Audit
    Z.CREATEDBYID                    AS ZipCreatedById,
    Z.CREATEDON                      AS ZipCreatedOn,
    UC.USERNAME                      AS ZipCreatedByName,
    Z.MODIFIEDBYID                   AS ZipModifiedById,
    Z.MODIFIEDON                     AS ZipModifiedOn,
    UM.USERNAME                      AS ZipModifiedByName,
    Z.SORTORDER                      AS ZipSortOrder,
    Z.STATUS                         AS ZipStatus,
    Z.VERSION                        AS ZipVersion,
    Z.SOURCETYPE                     AS ZipSourceType,
    Z.GEOLOCATION.STAsText()         AS ZipGeolocation,
    Z.BORDER.STAsText()              AS ZipBorder

FROM MZIP Z

LEFT JOIN MCITY C
    ON Z.CITYID = C.CITYID

LEFT JOIN MSTATE S
    ON C.STATEID = S.STATEID

LEFT JOIN MCOUNTRY CT
    ON S.COUNTRYID = CT.COUNTRYID

-- ZIP Users
LEFT JOIN MUSER UC
    ON Z.CREATEDBYID = UC.USERID
LEFT JOIN MUSER UM
    ON Z.MODIFIEDBYID = UM.USERID

-- STATE Users
LEFT JOIN MUSER UCS
    ON S.CREATEDBYID = UCS.USERID
LEFT JOIN MUSER UMS
    ON S.MODIFIEDBYID = UMS.USERID

-- COUNTRY Users
LEFT JOIN MUSER UCC
    ON CT.CREATEDBYID = UCC.USERID
LEFT JOIN MUSER UMC
    ON CT.MODIFIEDBYID = UMC.USERID

WHERE Z.ZIPID = @ZipId;";

        public const string SAVE_ZIP = @"
INSERT INTO MZIP
(
    ZIPID,
    ZIPCODE,
    ZIPNAME,
    CITYID,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    GEOLOCATION,
    BORDER
)
VALUES
(
    @ZipId,
    @ZipCode,
    @ZipName,
    @CityId,
    @ZipSortOrder,
    @ZipStatus,
    @ZipVersion,
    @ZipCreatedById,
    @ZipCreatedOn,
    @ZipModifiedById,
    @ZipModifiedOn,
    @ZipSourceType,
    @ZipGeolocation,
    @ZipBorder
);";


        public const string UPDATE_ZIP = @"
UPDATE MZIP
SET
    ZIPCODE      = @ZipCode,
    ZIPNAME      = @ZipName,
    CITYID       = @CityId,
    SORTORDER    = @ZipSortOrder,
    STATUS       = @ZipStatus,
    VERSION      = @ZipVersion,
    MODIFIEDBYID = @ZipModifiedById,
    MODIFIEDON   = @ZipModifiedOn,
    SOURCETYPE   = @ZipSourceType,
    GEOLOCATION  = @ZipGeolocation,
    BORDER       = @ZipBorder
WHERE ZIPID = @ZipId;";


        public const string DELETE_ZIP = @"
DELETE FROM MZIP
WHERE ZIPID = @ZipId;";
    }
}