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

namespace FrameworkDAL.Query.Address
{
    public static class AddressQB
    {
        public const string GET_ADDRESS = @"
                                        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.CITYID AS CityId,
                                            C.CITYNAME AS CityName,

                                            A.COUNTRYID AS CountryId,
                                            CN.COUNTRYNAME AS CountryName,

                                            A.STATEID AS StateId,
                                            S.STATENAME AS StateName,

                                            --A.STANDARDREGIONID AS StandardRegionId,
                                            --SR.STANDARDREGIONNAME AS StandardRegionName,

                                            A.LOCATIONID AS LocationId,
                                            L.LOCATIONNAME AS LocationName,

                                            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.SORTORDER AS AddressSortOrder,
                                            A.VERSION AS AddressVersion,
                                            A.STATUS AS AddressStatus,
                                            A.SOURCETYPE AS AddressSourceType,
                                            A.CREATEDBYID AS AddressCreatedById,
                                            A.CREATEDON AS AddressCreatedOn,
                                            A.MODIFIEDBYID AS AddressModifiedById,
                                            A.MODIFIEDON AS AddressModifiedOn,
                                            A.TDSCIRCLE AS AddressTDSCircle,
                                            A.GSTIN AS AddressGSTIn,
                                            A.GEOLOCATION AS AddressGeolocation,
                                            A.BORDER AS AddressBorder

                                        FROM MADDRESS A
                                        LEFT JOIN MCITY C ON C.CITYID = A.CITYID
                                        LEFT JOIN MSTATE S ON S.STATEID = A.STATEID
                                        LEFT JOIN MCOUNTRY CN ON CN.COUNTRYID = A.COUNTRYID
                                        --LEFT JOIN MSTANDARDREGION SR ON SR.STANDARDREGIONID = A.STANDARDREGIONID
                                        LEFT JOIN MLOCATION L ON L.LOCATIONID = A.LOCATIONID
                                        WHERE
                                            A.ADDRESSID = @addressid;
";


        public const string GET_ADDRESS_DETAIL = @"SELECT
                                                    P.PARTYCODE AS PartyCode,
                                                    P.PARTYNAME AS PartyName,
                                                    PB.PARTYBRANCHCODE AS PartyBranchCode,
                                                    PB.PARTYBRANCHNAME AS PartyBranchName,
                                                    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,
                                                    A.GEOLOCATION AS AddressGeolocation,
                                                    A.BORDER AS AddressBorder

                                                FROM MADDRESS A
                                                LEFT JOIN MPARTY P ON A.ADDRESSID = P.PARTYID
                                                LEFT JOIN MPARTYBRANCH PB ON A.ADDRESSID = PB.PARTYBRANCHID
                                                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 GET_SELECTLIST_ADDRESS = @"WITH PagedAddress AS (
                                                SELECT
                                                    A.ADDRESSID AS Id,
                                                    A.ADDRESSLINE1 AS Line1,
                                                    A.ADDRESSLINE2 AS Line2,
                                                    A.ADDRESSLINE3 AS Line3,
                                                    A.ADDRESSLINE4 AS Line4,
                                                    A.MOBILE AS Mobile,
                                                    ROW_NUMBER() OVER (ORDER BY A.ADDRESSID) AS RowNum
                                                FROM MADDRESS A
                                            )
                                            SELECT Id, Line1, Line2, Line3, Line4, Mobile
                                            FROM PagedAddress
                                             WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_ADDRESS_NEW = @"SELECT  
    A.ADDRESSID AS Id, 
    A.OBJECTID AS ObjectId, 
    A.OBJECTTYPEID AS ObjectTypeId, 
    A.ADDRESSLINE1 AS Line1, 
    A.ADDRESSLINE2 AS Line2, 
    A.ADDRESSLINE3 AS Line3, 
    A.MOBILE AS Mobile, 
    A.FAX AS Fax 
FROM MADDRESS A WITH (NOLOCK);
";
        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;";

        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 COUNT_OF_TRMADDRESS =
    @" select count(*) 
from TR_MADDRESS
where
   LANGUAGEID = @languageid
  and ADDRESSID = @addressid;";

        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
     LANGUAGEID = @LanguageId
    AND ADDRESSID = @AddressId;";

        public const string DELETE_ADDRESS = @"
DELETE FROM MADDRESS WHERE ADDRESSID = @addressid;";
    }
}