using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.Address { public static class AddressQB { 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 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, SR.STANDARDCODE AS StandardRegionCode, SR.STANDARDNAME AS StandardRegionName, L.LOCATIONID AS LocationId, L.LOCATIONCODE AS LocationCode, L.LOCATIONNAME AS LocationName, C.CITYID AS CityId, C.CITYCODE AS CityCode, C.CITYNAME AS CityName, CO.COUNTRYID AS CountryId, CO.COUNTRYCODE AS CountryCode, CO.COUNTRYNAME AS CountryName, CO.ISDCODE AS CountryISDCode, S.STATEID AS StateId, S.STATECODE AS StateCode, S.STATENAME 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.ToString() AS AddressGeolocation, A.BORDER.ToString() 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_ADDRESS_DETAIL_FOR_FULL_EMPLOYEE = @" SELECT addr.ADDRESSID AS AddressId, addr.OBJECTTYPEID AS AddressObjectTypeId, addr.OBJECTID AS AddressObjectId, addr.ADDRESSTYPE AS AddressAddressType, addr.ADDRESSNATURE AS AddressAddressNature, addr.ADDRESSLINE1 AS AddressLine1, addr.ADDRESSLINE2 AS AddressLine2, addr.ADDRESSLINE3 AS AddressLine3, addr.ADDRESSLINE4 AS AddressLine4, addr.ADDRESSLINE5 AS AddressLine5, addr.ZIPCODE AS AddressZipCode, city.CITYID AS CityId, city.CITYCODE AS CityCode, city.CITYNAME AS CityName, country.countryID AS CountryId, country.countryCODE AS CountryCode, country.countryNAME AS CountryName, addr.PHONE AS AddressPhone, addr.MOBILE AS AddressMobile, addr.FAX AS AddressFax, addr.WEB AS AddressWeb, addr.GEOCODE AS AddressGeoCode, addr.STANDARDREGIONID AS StandardRegionId, addr.VERSION AS AddressVersion, region.regionCODE AS StandardRegionCode, region.regionNAME AS StandardRegionName FROM MADDRESS addr INNER JOIN MREGION region ON region.regionID = addr.STANDARDREGIONID INNER JOIN mCITY city ON city.CITYID = addr.CITYID INNER JOIN MCOUNTRY country ON country.COUNTRYID = addr.COUNTRYID WHERE addr.OBJECTTYPEID = -1399999905 "; public const string GET_ADDRESS_DETAIL_FOR_FULL_EMPLOYEE_PG = @"SELECT addr.""addressid"" AS ""AddressId"", addr.""objecttypeid"" AS ""AddressObjectTypeId"", addr.""objectid"" AS ""AddressObjectId"", addr.""addresstype"" AS ""AddressAddressType"", addr.""addressnature"" AS ""AddressAddressNature"", addr.""addressline1"" AS ""AddressLine1"", addr.""addressline2"" AS ""AddressLine2"", addr.""addressline3"" AS ""AddressLine3"", addr.""addressline4"" AS ""AddressLine4"", addr.""addressline5"" AS ""AddressLine5"", addr.""zipcode"" AS ""AddressZipCode"", city.""cityid"" AS ""CityId"", city.""citycode"" AS ""CityCode"", city.""cityname"" AS ""CityName"", country.""countryid"" AS ""CountryId"", country.""countrycode"" AS ""CountryCode"", country.""countryname"" AS ""CountryName"", addr.""phone"" AS ""AddressPhone"", addr.""mobile"" AS ""AddressMobile"", addr.""fax"" AS ""AddressFax"", addr.""web"" AS ""AddressWeb"", addr.""geocode"" AS ""AddressGeoCode"", addr.""standardregionid"" AS ""StandardRegionId"", addr.""version"" AS ""AddressVersion"", region.""regioncode"" AS ""StandardRegionCode"", region.""regionname"" AS ""StandardRegionName"" FROM ""maddress"" addr INNER JOIN ""mregion"" region ON region.""regionid"" = addr.""standardregionid"" INNER JOIN ""mcity"" city ON city.""cityid"" = addr.""cityid"" INNER JOIN ""mcountry"" country ON country.""countryid"" = addr.""countryid"" WHERE addr.""objecttypeid"" = -1399999905;"; } }