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

namespace FrameworkDAL.Query.TRMAddress
{
    public static class TRMAddressQB
    {
        public const string GET_TRMADDRESS = @"
SELECT
    ISNULL(taddr.ADDRESSID, A.ADDRESSID) AS AddressId,
    A.OBJECTTYPEID AS AddressObjectTypeId,
    A.OBJECTID AS AddressObjectId,
    A.ADDRESSTYPE AS AddressAddressType,
    A.ADDRESSNATURE AS AddressAddressNature,
    ISNULL(taddr.ADDRESSLINE1, A.ADDRESSLINE1) AS AddressLine1,
    ISNULL(taddr.ADDRESSLINE2, A.ADDRESSLINE2) AS AddressLine2,
    ISNULL(taddr.ADDRESSLINE3, A.ADDRESSLINE3) AS AddressLine3,
    ISNULL(taddr.ADDRESSLINE4, A.ADDRESSLINE4) AS AddressLine4,
    ISNULL(taddr.ADDRESSLINE5, A.ADDRESSLINE5) AS AddressLine5,
    A.ZIPCODE AS AddressZipCode,
    A.CITYID AS CityId,
    A.COUNTRYID AS CountryId,
    A.PHONE AS AddressPhone,
    A.MOBILE AS AddressMobile,
    A.FAX AS AddressFax,
    A.WEB AS AddressWeb,
    A.GEOCODE AS AddressGeoCode,
    A.STANDARDREGIONID AS StandardRegionId,
    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,
    ISNULL(taddr.SORTORDER, A.SORTORDER) AS AddressSortOrder,
    ISNULL(taddr.VERSION, A.VERSION) AS AddressVersion,
    ISNULL(taddr.STATUS, A.STATUS) AS AddressStatus,
    ISNULL(taddr.SOURCETYPE, A.SOURCETYPE) AS AddressSourceType,
    ISNULL(taddr.CREATEDBYID, A.CREATEDBYID) AS AddressCreatedById,
    ISNULL(taddr.CREATEDON, A.CREATEDON) AS AddressCreatedOn,
    ISNULL(taddr.MODIFIEDBYID, A.MODIFIEDBYID) AS AddressModifiedById,
    ISNULL(taddr.MODIFIEDON, A.MODIFIEDON) AS AddressModifiedOn,
    A.TDSCIRCLE AS AddressTDSCircle,
    A.LOCATIONID AS LocationId,
    A.GSTIN AS AddressGSTIn,
    A.STATEID AS StateId,
    A.GEOLOCATION AS AddressGeolocation,
    A.BORDER AS AddressBorder,

    -- These only come from translation table
    taddr.ADDRESSTRANSLATIONID AS AddressTranslationId,
    taddr.LANGUAGEID AS LanguageId,
    taddr.TENANTID AS TenantId

FROM MADDRESS A
LEFT OUTER JOIN TR_MADDRESS taddr
    ON A.ADDRESSID = taddr.ADDRESSID AND taddr.LANGUAGEID = @languageId
WHERE A.ADDRESSID = @addressid;";

        public const string SAVE_TRMADDRESS = @"
INSERT INTO TR_MADDRESS
(
    --ADDRESSTRANSLATIONID,
    LANGUAGEID,
    ADDRESSID,
    ADDRESSLINE1,
    ADDRESSLINE2,
    ADDRESSLINE3,
    ADDRESSLINE4,
    ADDRESSLINE5,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    --@AddressTranslationId,
    @LanguageId,
    @AddressId,
    @AddressLine1,
    @AddressLine2,
    @AddressLine3,
    @AddressLine4,
    @AddressLine5,
    @AddressCreatedById,
    @AddressCreatedOn,
    @AddressModifiedById,
    @AddressModifiedOn,
    @AddressSortOrder, 
    @AddressStatus,
    @AddressVersion,
    @AddressSourceType,
    @TenantId
);";

        public const string UPDATE_TRMADDRESS = @"
UPDATE TR_MADDRESS
SET
    ADDRESSLINE1 = @AddressLine1,
    ADDRESSLINE2 = @AddressLine2,
    ADDRESSLINE3 = @AddressLine3,
    ADDRESSLINE4 = @AddressLine4,
    ADDRESSLINE5 = @AddressLine5,
    MODIFIEDBYID = @AddressModifiedById,
    MODIFIEDON = @AddressModifiedOn,
    SORTORDER = @AddressSortOrder,
    STATUS = @AddressStatus,
    VERSION = @AddressVersion,
    SOURCETYPE = @AddressSourceType,
    TENANTID = @TenantId
WHERE
    ADDRESSTRANSLATIONID = @AddressTranslationId
    AND LANGUAGEID = @LanguageId
    AND ADDRESSID = @AddressId;";

        public const string DELETE_TRMADDRESS = @"
DELETE FROM TR_MADDRESS WHERE ADDRESSID = @addressid;";
    }
}