namespace AccountsDAL.Query.Address
{
    // Scoped copy of FrameworkDAL.Query.Address.AddressQB — only the queries PartyBLL's
    // FindAddress/SaveAddress/UpdateAddress flow needs, so AccountsBLL doesn't have to
    // depend on the FrameworkDAL project for MADDRESS access.
    public static class AddressQB
    {
        public const string GET_ADDRESS_DETAIL = @"SELECT
                                                    A.ADDRESSID AS AddressId,
                                                    A.OBJECTTYPEID AS AddressObjectTypeId,
                                                    A.OBJECTID AS AddressObjectId,
                                                    A.ADDRESSTYPE AS AddressAddressType,
                                                    A.ADDRESSNATURE AS AddressAddressNature,
                                                    A.ADDRESSLINE1 AS AddressLine1,
                                                    A.ADDRESSLINE2 AS AddressLine2,
                                                    A.ADDRESSLINE3 AS AddressLine3,
                                                    A.ADDRESSLINE4 AS AddressLine4,
                                                    A.ADDRESSLINE5 AS AddressLine5,
                                                    A.ZIPCODE AS AddressZipCode,
                                                    A.PHONE AS AddressPhone,
                                                    A.MOBILE AS AddressMobile,
                                                    A.FAX AS AddressFax,
                                                    A.WEB AS AddressWeb,
                                                    A.GEOCODE AS AddressGeoCode,
                                                    A.MAIL AS AddressMail,
                                                    A.VATTINNUMBER AS AddressVATTINNumber,
                                                    A.VATCSTNUMBER AS AddressVATCSTNumber,
                                                    A.PAN AS AddressPAN,
                                                    A.TAN AS AddressTAN,
                                                    A.SERVICETAXNUMBER AS AddressServiceTaxNumber,
                                                    A.EXCISEDIVISION AS AddressExciseDivision,
                                                    A.EXCISERANGE AS AddressExciseRange,
                                                    A.EXCISECODENUMBER AS AddressExciseCodeNumber,
                                                    A.EXCISENOTIFICATION AS AddressExciseNotification,
                                                    A.EXCISECIRCLE AS AddressExciseCircle,
                                                    A.EXCISECOLLECTRATE AS AddressExciseCollectrate,
                                                    A.EXCISEREGISTERNUMBER AS AddressExciseRegisterNumber,
                                                    A.TDSCIRCLE AS AddressTDSCircle,
                                                    A.GSTIN AS AddressGSTIn,
                                                    SR.STANDARDID AS StandardRegionId,
                                                    ISNULL(SR.STANDARDCODE, 'NONE') AS StandardRegionCode,
                                                    ISNULL(SR.STANDARDNAME, 'NONE') AS StandardRegionName,
                                                    L.LOCATIONID AS LocationId,
                                                    ISNULL(L.LOCATIONCODE, 'NONE') AS LocationCode,
                                                    ISNULL(L.LOCATIONNAME, 'NONE') AS LocationName,
                                                    C.CITYID AS CityId,
                                                    ISNULL(C.CITYCODE, 'NONE') AS CityCode,
                                                    ISNULL(C.CITYNAME, 'NONE') AS CityName,
                                                    CO.COUNTRYID AS CountryId,
                                                    ISNULL(CO.COUNTRYCODE, 'NONE') AS CountryCode,
                                                    ISNULL(CO.COUNTRYNAME, 'NONE') AS CountryName,
                                                    CO.ISDCODE AS CountryISDCode,
                                                    S.STATEID AS StateId,
                                                    ISNULL(S.STATECODE, 'NONE') AS StateCode,
                                                    ISNULL(S.STATENAME, 'NONE') AS StateName,
                                                    S.GSTSTATECODE AS StateGSTStateCode,
                                                    S.TINSTART AS StateTINStart,
                                                    A.CREATEDBYID AS AddressCreatedById,
                                                    A.CREATEDON AS AddressCreatedOn,
                                                    A.MODIFIEDBYID AS AddressModifiedById,
                                                    A.MODIFIEDON AS AddressModifiedOn,
                                                    A.SORTORDER AS AddressSortOrder,
                                                    A.VERSION AS AddressVersion,
                                                    A.SOURCETYPE AS AddressSourceType,
                                                    A.STATUS AS AddressStatus,
                                                    ISNULL(CAST(A.GEOLOCATION AS VARCHAR(50)), '') AS AddressGeolocation,
                                                    ISNULL(CAST(A.BORDER AS VARCHAR(50)), '') AS AddressBorder

                                                FROM MADDRESS A
                                                LEFT JOIN MCITY C ON A.CITYID = C.CITYID
                                                LEFT JOIN MCOUNTRY CO ON A.COUNTRYID = CO.COUNTRYID
                                                LEFT JOIN MSTATE S ON A.STATEID = S.STATEID
                                                LEFT JOIN MSTANDARD SR ON A.STANDARDREGIONID = SR.STANDARDID
                                                LEFT JOIN MLOCATION L ON A.LOCATIONID = L.LOCATIONID
                                                WHERE
                                                A.OBJECTTYPEID = @ObjectTypeId AND
                                                A.OBJECTID = @ObjectId;
                                                ";

        public const string SAVE_ADDRESS = @"
INSERT INTO MADDRESS (
    ADDRESSID,
    OBJECTTYPEID, OBJECTID, ADDRESSTYPE, ADDRESSNATURE, ADDRESSLINE1, ADDRESSLINE2, ADDRESSLINE3, ADDRESSLINE4, ADDRESSLINE5,
    ZIPCODE, CITYID, COUNTRYID, PHONE, MOBILE, FAX, WEB, GEOCODE, STANDARDREGIONID, MAIL, VATTINNUMBER, VATCSTNUMBER,
    PAN, TAN, SERVICETAXNUMBER, EXCISEDIVISION, EXCISERANGE, EXCISECODENUMBER, EXCISENOTIFICATION, EXCISECIRCLE,
    EXCISECOLLECTRATE, EXCISEREGISTERNUMBER, SORTORDER, VERSION, STATUS, SOURCETYPE, CREATEDBYID, CREATEDON,
    MODIFIEDBYID, MODIFIEDON, TDSCIRCLE, LOCATIONID, GSTIN, STATEID, GEOLOCATION, BORDER
)
VALUES (
    @AddressId,
    @AddressObjectTypeId, @AddressObjectId, @AddressAddressType, @AddressAddressNature, @AddressLine1, @AddressLine2,
    @AddressLine3, @AddressLine4, @AddressLine5, @AddressZipCode, @CityId, @CountryId, @AddressPhone, @AddressMobile,
    @AddressFax, @AddressWeb, @AddressGeoCode, @StandardRegionId, @AddressMail, @AddressVATTINNumber,
    @AddressVATCSTNumber, @AddressPAN, @AddressTAN, @AddressServiceTaxNumber, @AddressExciseDivision,
    @AddressExciseRange, @AddressExciseCodeNumber, @AddressExciseNotification, @AddressExciseCircle,
    @AddressExciseCollectrate, @AddressExciseRegisterNumber, @AddressSortOrder, @AddressVersion, @AddressStatus,
    @AddressSourceType, @AddressCreatedById, @AddressCreatedOn, @AddressModifiedById, @AddressModifiedOn,
    @AddressTDSCircle, @LocationId, @AddressGSTIn, @StateId, @AddressGeolocation, @AddressBorder
);";

        public const string UPDATE_ADDRESS = @"
UPDATE MADDRESS
SET
    OBJECTTYPEID = @AddressObjectTypeId,
    OBJECTID = @AddressObjectId,
    ADDRESSTYPE = @AddressAddressType,
    ADDRESSNATURE = @AddressAddressNature,
    ADDRESSLINE1 = @AddressLine1,
    ADDRESSLINE2 = @AddressLine2,
    ADDRESSLINE3 = @AddressLine3,
    ADDRESSLINE4 = @AddressLine4,
    ADDRESSLINE5 = @AddressLine5,
    ZIPCODE = @AddressZipCode,
    CITYID = @CityId,
    COUNTRYID = @CountryId,
    PHONE = @AddressPhone,
    MOBILE = @AddressMobile,
    FAX = @AddressFax,
    WEB = @AddressWeb,
    GEOCODE = @AddressGeoCode,
    STANDARDREGIONID = @StandardRegionId,
    MAIL = @AddressMail,
    VATTINNUMBER = @AddressVATTINNumber,
    VATCSTNUMBER = @AddressVATCSTNumber,
    PAN = @AddressPAN,
    TAN = @AddressTAN,
    SERVICETAXNUMBER = @AddressServiceTaxNumber,
    EXCISEDIVISION = @AddressExciseDivision,
    EXCISERANGE = @AddressExciseRange,
    EXCISECODENUMBER = @AddressExciseCodeNumber,
    EXCISENOTIFICATION = @AddressExciseNotification,
    EXCISECIRCLE = @AddressExciseCircle,
    EXCISECOLLECTRATE = @AddressExciseCollectrate,
    EXCISEREGISTERNUMBER = @AddressExciseRegisterNumber,
    SORTORDER = @AddressSortOrder,
    VERSION = @AddressVersion,
    STATUS = @AddressStatus,
    SOURCETYPE = @AddressSourceType,
    MODIFIEDBYID = @AddressModifiedById,
    MODIFIEDON = @AddressModifiedOn,
    TDSCIRCLE = @AddressTDSCircle,
    LOCATIONID = @LocationId,
    GSTIN = @AddressGSTIn,
    STATEID = @StateId,
    GEOLOCATION = @AddressGeolocation,
    BORDER = @AddressBorder
WHERE
    ADDRESSID = @AddressId;";
    }
}