using Google.Protobuf.WellKnownTypes; using Org.BouncyCastle.Asn1.Utilities; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.Employee { public static class EmployeeQB { public const string SAVE_EMPLOYEE_SECRET_KEY = "Update MEMPLOYEE set SECRETKEY = @secretkey where EMPLOYEEID = @employeeid"; // Manual raw-code-entry scan path only — resolves EmployeeCode to EmployeeId so the // caller can then use the existing GetEmployee(EmployeeId) path for full details. public const string GET_EMPLOYEEID_BY_CODE = @" SELECT TOP 1 EMPLOYEEID FROM MEMPLOYEE WHERE EMPLOYEECODE = @EmployeeCode AND TENANTID = @TenantId"; // GB4 parity (Redmine #43104): resolves the addon-based approval head for an OU+Department // combination. Z_DEPTWISEOUWISEHEAD is an optional customization table — not every tenant has it. public const string GET_OUWISE_DEPARTMENTWISE_HEAD = "SELECT INCHARGEID FROM Z_DEPTWISEOUWISEHEAD WHERE OUID = @ouid AND DEPID = @depid"; // GB4 parity (Employee.svc.cs GetDifferenceFromJoiningDate): SQL-Server-only feature. public const string GET_EMPLOYEE_DATE_OF_JOINING = "SELECT DATEOFJOINING FROM MEMPLOYEE WHERE EMPLOYEEID = @employeeid"; // GB4's GET_AGE_SPLIT_FOR_EMPLOYEE called the same GetAgeSplit(...) table-valued function, // but built the call by string-replacing raw date text directly into the SQL — ported here as // a proper parameterized query instead. Relies on the GetAgeSplit TVF still existing in the DB // (shared schema object, not app code — not something this migration can create/verify). public const string GET_AGE_SPLIT_FOR_EMPLOYEE = @" SELECT years AS SeparationYears, months AS SeparationMonths, days AS SeparationDays FROM GetAgeSplit(1, @employeedateofjoining, DATEADD(day, 1, @separationdate))"; public const string GET_EMPLOYEE = @" SELECT -- ── Employee Core Fields from MEMPLOYEE ────────────────── emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECOMMONID AS EmployeeCommonId, emp.CODEDEFINEID AS CodeDefineId, -- Same column, second alias: the caller's save/update payload round-trips via -- EmployeeCodeDefineId (see EmployeeDTO.cs), not CodeDefineId — without this, every GET -- returns EmployeeCodeDefineId stuck at its class default (-1), so a load-edit-resubmit -- update carries that stale -1 forward and silently reverts the real CodeDefineId. emp.CODEDEFINEID AS EmployeeCodeDefineId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.FATHERNAME AS EmployeeFatherName, emp.MOTHERNAME AS EmployeeMotherName, emp.SPOUSENAME AS EmployeeSpouseName, emp.DEPARTMENTID AS DepartmentId, emp.COSTCENTERTYPE AS EmployeeCostCenterType, emp.COSTCENTERID AS CostCenterId, emp.COSTCENTERPATTERNID AS CostCenterPatternId, emp.WORKOUID AS WorkOUId, emp.ISCROSSOU AS EmployeeIsCrossOU, emp.OUGROUPID AS OUGroupId, emp.PAYGROUPID AS PayGroupId, emp.WORKTYPEID AS WorkTypeId, emp.EMPLOYEETYPEID AS EmployeeTypeId, emp.STAFFTYPEID AS StaffTypeId, emp.DESIGNATIONID AS DesignationId, emp.NATIONALITYID AS NationalityId, emp.RELIGIONID AS ReligionId, emp.LOCATIONID AS LocationId, emp.CASTEID AS CasteId, emp.HEIGHTINCMS AS EmployeeHeightInCMS, emp.WEIGHTINKGS AS EmployeeWeightInKGS, emp.CHESTSIZE AS EmployeeChestSize, emp.ISHANDICAPPED AS EmployeeIsHandiCapped, emp.IDENTIFICATIONMARK AS EmployeeIdentificationMark, emp.PHYSICALCONDITION AS EmployeePhysicalCondition, emp.EMPLOYEESTATUS AS EmployeeEmployerStatus, emp.DATEOFBIRTH AS EmployeeDateOfBirth, emp.ACTUALDATEOFBIRTH AS EmployeeActualDateOfBirth, emp.DATEOFJOINING AS EmployeeDateOfJoining, emp.ISCONFIRMED AS EmployeeIsConfirmed, emp.DATEOFCONFIRMATION AS EmployeeDateOfConfirmation, emp.DATEOFRESIGNATION AS EmployeeDateOfResignation, emp.CALCULATEDDATEOFRETIREMENT AS EmployeeCalculatedDateOfRetirment, emp.ACTUALDATEOFRETIREMENT AS EmployeeActualDateOfRetirment, emp.MARITALSTATUS AS EmployeeMaritalStatus, emp.SEX AS EmployeeSex, CASE WHEN emp.SEX = 0 THEN 'Male' WHEN emp.SEX = 1 THEN 'Female' ELSE 'Other' END AS SexName, emp.BLOODGROUP AS EmployeeBloodGroup, emp.ISPFAPPLICABLE AS EmployeeIsPFApplicable, emp.PFSTATUS AS EmployeePFStatus, emp.PFNUMBER AS EmployeePFNumber, emp.STATUTORYPFPERCENTAGE AS EmployeeStatutorypfPercentage, emp.EMPLOYEEPFPERCENTAGE AS EmployeeEmployeePFPercentage, emp.EMPLOYERPFPERCENTAGE AS EmployeeEmployerPFPercentage, emp.ISFAMILYPENSION AS EmployeeIsFamilyPension, emp.FAMILYPENSIONNUMBER AS EmployeeFamilyPensionNumber, emp.FAMILYPENSIONPERCENTAGE AS EmployeeFamilyPensionPercentage, emp.PFJOINDATE AS EmployeePFJoinDate, emp.PFENDDATE AS EmployeePFEndDate, emp.ISESIAPPLICABLE AS EmployeeIsESIApplicable, emp.ESISTATUS AS EmployeeESIStatus, emp.ESINUMBER AS EmployeeESINumber, emp.ESIJOINDATE AS EmployeeESIJoinDate, emp.ESIENDDATE AS EmployeeESIEndDate, emp.ESIDISPENSARY AS EmployeeESIDispensary, emp.EMPLOYEEESIPERCENTAGE AS EmployeeEmployeeESIPercentage, emp.EMPLOYERESIPERCENTAGE AS EmployeeEmployerESIPercentage, emp.ISESIHOURLYCONTRIBUTION AS EmployeeIsESIHourlyContribution, emp.BASICON AS EmployeeBasicOn, emp.PAYMODE AS EmployeePayMode, emp.OTPAYMODE AS EmployeeOTPayMode, emp.OTTYPE AS EmployeeOtType, emp.ISTDSAPPLICABLE AS EmployeeIsTDSApplicable, emp.SALARYGRADEID AS SalaryGradeId, emp.DAPOINTGROUPID AS DAPointGroupId, emp.PANNUMBER AS EmployeePANNumber, emp.ACCOUNTNUMBER AS EmployeeAccountNumber, emp.SHIFTID AS ShiftId, emp.WEEKOFFDAY AS EmployeeWeekOfDay, emp.ACCOUNTID AS AccountId, emp.NOMINEE AS EmployeeNominee, emp.NOMINEERELATION AS EmployeeNomineeRelation, emp.ATTENDANCETYPE AS EmployeeAttendanceType, emp.ONSITEPLACE AS EmployeeOnSitePlace, emp.ISDAILYREPORT AS EmployeeIsDailyReport, emp.PUNCHTYPE AS EmployeePunchType, emp.PRIMARYEMAILID AS EmployeePrimaryEMailId, emp.PRIMARYMOBILE AS EmployeePrimaryMobile, emp.REPORTINGTOEMPLOYEEID AS ReportInttoEmployeeId, emp.EXPERIENCE AS EmployeeExperience, emp.ISTEMPORARYCARD AS EmployeeIsTemporaryCard, emp.TEMPORARYCARDNUMBER AS EmployeeTemporaryCardNumber, emp.CARDVALIDTILL AS EmployeeCardValidTill, emp.ISNUMBERREQUIRED AS EmployeeIsNumberRequired, emp.BIOMATCHNUMBER AS EmployeeBioMatchNumber, emp.ADVANCEACCOUNTID AS AdvanceAccountId, emp.SALARYACCOUNTID AS SalaryAccountId, emp.DEFAULTIMAGEID AS EmployeeDefaultImage, emp.ISCONTRACT AS EmployeeIsContract, emp.ISNUMBER AS EmployeeIsNumber, emp.ISPURCHASE AS EmployeeIsPurchase, emp.ISMARKETING AS EmployeeIsMarketing, emp.ISSALES AS EmployeeIsSales, emp.ISPLANNING AS EmployeeIsPlanning, emp.ISSERVICE AS EmployeeIsService, emp.ISUSER AS EmployeeIsUser, emp.VERSION AS EmployeeVersion, emp.STATUS AS EmployeeStatus, emp.CREATEDBYID AS EmployeeCreatedById, emp.CREATEDON AS EmployeeCreatedOn, emp.MODIFIEDBYID AS EmployeeModifiedById, emp.MODIFIEDON AS EmployeeModifiedOn, emp.SEGMENTID AS SegmentId, emp.UNITID AS UnitId, emp.SHIFTPATTERNDETAILID AS ShiftPatternDetailId, emp.PATTERNDATE AS EmployeePatternDate, emp.REASONFORESILEAVING AS EmployeeReasonForESILeaving, emp.REASONFORLEAVING AS EmployeeReasonForLeaving, emp.REASONFORPFLEAVING AS EmployeeReasonForPFLeaving, emp.VILLAGEID AS VillageId, emp.SOURCETYPE AS SourceType, emp.SORTORDER AS EmployeeSortOrder, emp.PROBATIONSTARTDATE AS EmployeeProbationStartDate, emp.CALENDARID AS CalendarId, emp.RESOURCETYPEID AS ResourceTypeId, emp.ISPRODUCTION AS EmployeeIsProduction, emp.ADHARNUMBER AS EmployeeAdharNumber, emp.PFUANNUMBER AS EmployeePFUANNumber, emp.PAYCONFIGURATIONID AS PayConfigurationId, emp.PRODUCTCATEGORYIDS AS EmployeeProductCategoryIds, emp.PRODUCTPRICECATEGORYIDS AS EmployeeProductPriceCategoryIds, emp.TENANTID AS TenantId, emp.ISPIREQUIRED AS EmployeeIsPiRequired, emp.GRATUITYGROUPID AS GratuityGroupId, emp.CURRENCYID AS CurrencyId, emp.ADMINREPORTINGTOID AS AdminReportingToId, emp.CONTRACTORID AS ContractorId, emp.FIRSTNAME AS FirstName, emp.MIDDLENAME AS MiddleName, emp.LASTNAME AS LastName, emp.NOOFVERIFICATION AS EmployeeNoOfVerfication, emp.LOCATIONIDS AS EmployeeLocationIds, emp.SECONDARYMAILID AS SecondaryMailId, emp.SECONDARYMOBILE AS SecondaryMobile, emp.IBANNUMBER AS EmployeeIBANNumber, emp.EMPLOYEEBANKBRANCHID AS EmployeeBankBranchId, emp.PRESENTADDRESSID AS EmployeePresentAddressId, emp.PERMANENTADDRESSID AS EmployeePermenantAddressId, -- ⚠️ FIX #1: was ""ContactId"" — renamed to avoid splitOn collision with cont.CONTACTID below emp.CONTACTID AS EmployeeContactId, emp.TRAININGPERIOD AS EmployeeTrainingPeriod, emp.TRAININGENDINGDATE AS EmployeeTrainingendingDate, emp.ISTIMESHEETREQUIRED AS EmployeeIsTimeSheetRequired, emp.ISPOSTINGREQUIRED AS EmployeeIsPostingRequired, emp.PLACEOFBIRTH AS EmployeePlaceOfBirth, emp.NATIVEPLACE AS EmployeeNativePlace, emp.NATIVEDISTRICT AS EmployeeNativeDistrict, emp.NOTICEPERIODINDAYS AS EmployeeNoticePeriodInDays, emp.DATEOFREJOIN AS EmployeeDateOfReJoin, emp.LEGACYEMPLOYEECODE AS EmployeeLegacyEmployeeCode, emp.NATURE AS EmployeeNature, emp.VERSIONFROM AS EmployeeVersionFrom, emp.JOBPOSITIONID AS JobPositionId, emp.CURRENTOUID AS CurrentOUId, emp.THUMBNAIL AS EmployeeThumbNail, emp.VISAENTITYID AS VisaEntityId, emp.GRACEMINUTES AS EmployeeGraceMinutes, -- ── Lookup Fields (joined) ──────────────────────────────── empcommon.EMPLOYEECODE AS EmployeeCommonCode, empcommon.EMPLOYEENAME AS EmployeeCommonName, cd.CATEGORYCODE AS CodeDefineCategoryCode, cd.CATEGORYNAME AS CodeDefineCategoryName, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, vil.VILLAGECODE AS VillageCode, vil.VILLAGENAME AS VillageName, cc.COSTCENTERCODE AS CostCenterCode, cc.COSTCENTERNAME AS CostCenterName, ou.ORGANIZATIONUNITCODE AS WorkOUCode, ou.ORGANIZATIONUNITNAME AS WorkOUName, oug.ORGANIZATIONGROUPCODE AS OUGroupCode, oug.ORGANIZATIONGROUPNAME AS OUGroupName, pg.PAYGROUPCODE AS PayGroupCode, pg.PAYGROUPNAME AS PayGroupName, wt.GCMCODE AS WorkTypeCode, wt.GCMNAME AS WorkTypeName, et.GCMCODE AS EmployeeTypeCode, et.GCMNAME AS EmployeeTypeName, st.GCMCODE AS StaffTypeCode, st.GCMNAME AS StaffTypeName, des.GCMCODE AS DesignationCode, des.GCMNAME AS DesignationName, nat.GCMCODE AS NationalityCode, nat.GCMNAME AS NationalityName, rel.GCMCODE AS ReligionCode, rel.GCMNAME AS ReligionName, loc.LOCATIONCODE AS LocationCode, loc.LOCATIONNAME AS LocationName, loc.GEOCODE AS LocationGeoCode, caste.GCMCODE AS CasteCode, caste.GCMNAME AS CasteName, sg.GCMCODE AS SalaryGradeCode, sg.GCMNAME AS SalaryGradeName, dapg.DAPOINTGROUPCODE AS DAPointGroupCode, dapg.DAPOINTGROUPNAME AS DAPointGroupName, bankbranch.BANKBRANCHNAME AS BankBranchName, bank.GCMCODE AS BankCode, bank.GCMNAME AS BankName, bankbranch.BANKID AS BankId, shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, acc.ACCOUNTCODE AS AccountCode, acc.ACCOUNTNAME AS AccountName, repto.EMPLOYEECODE AS ReportInttoEmployeeCode, repto.EMPLOYEENAME AS ReportInttoEmployeeName, advacc.ACCOUNTCODE AS AdvanceAccountCode, advacc.ACCOUNTNAME AS AdvanceAccountName, salacc.ACCOUNTCODE AS SalaryAccountCode, salacc.ACCOUNTNAME AS SalaryAccountName, seg.GCMCODE AS SegmentCode, seg.GCMNAME AS SegmentName, unit.GCMCODE AS UnitCode, unit.GCMNAME AS UnitName, spd.SLNO AS ShiftPatternDetailSlno, spd.DAYTYPE AS ShiftPatternDetailDayType, cal.CALENDARCODE AS CalendarCode, cal.CALENDARNAME AS CalendarName, rt.RESOURCETYPECODE AS ResourceTypeCode, rt.RESOURCETYPENAME AS ResourceTypeName, pc.PAYCONFIGURATIONCODE AS PayConfigurationCode, pc.PAYCONFIGURATIONNAME AS PayConfigurationName, pc.ISPARTIALPROCESSREQUIRED AS PayConfigurationIsPartialProcessRequired, gg.GRATUITYGROUPCODE AS GratuityGroupCode, gg.GRATUITYGROUPNAME AS GratuityGroupName, curr.CURRENCYCODE AS CurrencyCode, curr.CURRENCYNAME AS CurrencyName, adminrep.USERCODE AS AdminReportingToCode, adminrep.USERNAME AS AdminReportingToName, contractor.PARTYCODE AS ContractorCode, contractor.PARTYNAME AS ContractorName, jp.JOBPOSITIONCODE AS JobPositionCode, jp.JOBPOSITIONNAME AS JobPositionName, currentou.ORGANIZATIONUNITCODE AS CurrentOUCode, currentou.ORGANIZATIONUNITNAME AS CurrentOUName, ve.GCMCODE AS VisaEntityCode, ve.GCMNAME AS VisaEntityName, -- Placeholder only (splitOn boundary for the DAL's multi-map) — deliberately NOT -- joined to MEMPLOYEEADDON, whose column set is client-defined and previously broke -- this query outright for tenants without the exact hardcoded columns it expected. -- EmployeeBLL.GetEmployee re-populates the real EmployeeAddon afterward via -- IAddonService.GetAddonJsonAsync, which adapts to whatever columns actually exist. emp.EMPLOYEEID AS AddonEmployeeId, -- ── Present Address (splitOn: AddressId) ───────────────── presaddr.ADDRESSID AS AddressId, presaddr.OBJECTTYPEID AS AddressObjectTypeId, presaddr.OBJECTID AS AddressObjectId, presaddr.ADDRESSTYPE AS AddressAddressType, presaddr.ADDRESSNATURE AS AddressAddressNature, presaddr.ADDRESSLINE1 AS AddressLine1, presaddr.ADDRESSLINE2 AS AddressLine2, presaddr.ADDRESSLINE3 AS AddressLine3, presaddr.ADDRESSLINE4 AS AddressLine4, presaddr.ADDRESSLINE5 AS AddressLine5, presaddr.ZIPCODE AS AddressZipCode, presaddr.CITYID AS CityId, prescity.CITYCODE AS CityCode, prescity.CITYNAME AS CityName, presaddr.COUNTRYID AS CountryId, prescountry.COUNTRYCODE AS CountryCode, prescountry.COUNTRYNAME AS CountryName, prescountry.ISDCODE AS CountryISDCode, presaddr.PHONE AS AddressPhone, presaddr.MOBILE AS AddressMobile, presaddr.FAX AS AddressFax, presaddr.WEB AS AddressWeb, presaddr.GEOCODE AS AddressGeoCode, presaddr.MAIL AS AddressMail, presaddr.VATTINNUMBER AS AddressVATTINNumber, presaddr.VATCSTNUMBER AS AddressVATCSTNumber, presaddr.PAN AS AddressPAN, presaddr.TAN AS AddressTAN, presaddr.SERVICETAXNUMBER AS AddressServiceTaxNumber, presaddr.EXCISEDIVISION AS AddressExciseDivision, presaddr.EXCISERANGE AS AddressExciseRange, presaddr.EXCISECODENUMBER AS AddressExciseCodeNumber, presaddr.EXCISENOTIFICATION AS AddressExciseNotification, presaddr.EXCISECIRCLE AS AddressExciseCircle, presaddr.EXCISECOLLECTRATE AS AddressExciseCollectrate, presaddr.EXCISEREGISTERNUMBER AS AddressExciseRegisterNumber, presaddr.TDSCIRCLE AS AddressTDSCircle, presaddr.STANDARDREGIONID AS StandardRegionId, presstdreg.REGIONCODE AS StandardRegionCode, presstdreg.REGIONNAME AS StandardRegionName, presaddr.LOCATIONID AS AddressLocationId, presloc.LOCATIONCODE AS AddressLocationCode, presloc.LOCATIONNAME AS AddressLocationName, presaddr.GSTIN AS AddressGSTIn, presaddr.STATEID AS StateId, presstate.STATECODE AS StateCode, presstate.STATENAME AS StateName, presstate.GSTSTATECODE AS StateGSTStateCode, presstate.TINSTART AS StateTINStart, presaddr.CREATEDBYID AS AddressCreatedById, presaddr.CREATEDON AS AddressCreatedOn, presaddr.MODIFIEDBYID AS AddressModifiedById, presaddr.MODIFIEDON AS AddressModifiedOn, presaddr.SORTORDER AS AddressSortOrder, presaddr.VERSION AS AddressVersion, presaddr.SOURCETYPE AS AddressSourceType, presaddr.STATUS AS AddressStatus, presaddr.GEOLOCATION.ToString() AS AddressGeolocation, presaddr.BORDER.ToString() AS AddressBorder, -- ── Permanent Address (splitOn: PermAddressId) ─────────── permaddr.ADDRESSID AS PermAddressId, permaddr.OBJECTTYPEID AS PermAddressObjectTypeId, permaddr.OBJECTID AS PermAddressObjectId, permaddr.ADDRESSTYPE AS PermAddressAddressType, permaddr.ADDRESSNATURE AS PermAddressAddressNature, permaddr.ADDRESSLINE1 AS PermAddressLine1, permaddr.ADDRESSLINE2 AS PermAddressLine2, permaddr.ADDRESSLINE3 AS PermAddressLine3, permaddr.ADDRESSLINE4 AS PermAddressLine4, permaddr.ADDRESSLINE5 AS PermAddressLine5, permaddr.ZIPCODE AS PermAddressZipCode, permaddr.CITYID AS PermCityId, permcity.CITYCODE AS PermCityCode, permcity.CITYNAME AS PermCityName, permaddr.COUNTRYID AS PermCountryId, permcountry.COUNTRYCODE AS PermCountryCode, permcountry.COUNTRYNAME AS PermCountryName, permcountry.ISDCODE AS PermCountryISDCode, permaddr.PHONE AS PermAddressPhone, permaddr.MOBILE AS PermAddressMobile, permaddr.FAX AS PermAddressFax, permaddr.WEB AS PermAddressWeb, permaddr.GEOCODE AS PermAddressGeoCode, permaddr.MAIL AS PermAddressMail, permaddr.VATTINNUMBER AS PermAddressVATTINNumber, permaddr.VATCSTNUMBER AS PermAddressVATCSTNumber, permaddr.PAN AS PermAddressPAN, permaddr.TAN AS PermAddressTAN, permaddr.SERVICETAXNUMBER AS PermAddressServiceTaxNumber, permaddr.EXCISEDIVISION AS PermAddressExciseDivision, permaddr.EXCISERANGE AS PermAddressExciseRange, permaddr.EXCISECODENUMBER AS PermAddressExciseCodeNumber, permaddr.EXCISENOTIFICATION AS PermAddressExciseNotification, permaddr.EXCISECIRCLE AS PermAddressExciseCircle, permaddr.EXCISECOLLECTRATE AS PermAddressExciseCollectrate, permaddr.EXCISEREGISTERNUMBER AS PermAddressExciseRegisterNumber, permaddr.TDSCIRCLE AS PermAddressTDSCircle, permaddr.STANDARDREGIONID AS PermStandardRegionId, permstdreg.REGIONCODE AS PermStandardRegionCode, permstdreg.REGIONNAME AS PermStandardRegionName, permaddr.LOCATIONID AS PermLocationId, permloc.LOCATIONCODE AS PermLocationCode, permloc.LOCATIONNAME AS PermLocationName, permaddr.GSTIN AS PermAddressGSTIn, permaddr.STATEID AS PermStateId, permstate.STATECODE AS PermStateCode, permstate.STATENAME AS PermStateName, permstate.GSTSTATECODE AS PermStateGSTStateCode, permstate.TINSTART AS PermStateTINStart, permaddr.CREATEDBYID AS PermAddressCreatedById, permaddr.CREATEDON AS PermAddressCreatedOn, permaddr.MODIFIEDBYID AS PermAddressModifiedById, permaddr.MODIFIEDON AS PermAddressModifiedOn, permaddr.SORTORDER AS PermAddressSortOrder, permaddr.VERSION AS PermAddressVersion, permaddr.SOURCETYPE AS PermAddressSourceType, permaddr.STATUS AS PermAddressStatus, permaddr.GEOLOCATION.ToString() AS PermAddressGeolocation, permaddr.BORDER.ToString() AS PermAddressBorder, -- ── Contact (splitOn: ContactId) ───────────────────────── -- ⚠️ This ContactId is the real split point — emp.CONTACTID above -- has been renamed EmployeeContactId so this fires correctly. cont.CONTACTID AS ContactId, cont.OBJECTTYPEID AS ContactObjectTypeId, cont.OBJECTID AS ContactObjectId, cont.CONTACTNATURE AS ContactContactNature, cont.NAME AS ContactName, cont.FIRSTNAME AS ContactFirstName, cont.MIDDLENAME AS ContactMiddleName, cont.LASTNAME AS ContactLastName, cont.SALUTATION AS ContactSalutation, cont.SEX AS ContactSex, CASE WHEN cont.SEX = 0 THEN 'MALE' WHEN cont.SEX = 1 THEN 'FEMALE' ELSE 'OTHER' END AS ContactSexName, cont.DOB AS ContactDob, cont.MAILID AS ContactMailId, cont.PHONENO AS ContactPhoneNo, cont.MOBILENO AS ContactMobileNo, cont.RELATIONSHIP AS ContactRelationShip, cont.DESIGNATION AS ContactDesignation, cont.COMPANY AS ContactCompany, cont.DEFAULTIMAGEID AS ContactDefaultImageId, cont.LINKTYPE AS ContactLinkType, cont.REPORTINGTOID AS ReportingToId, cont.USERLINKID AS UserLinkId, cont.THUMBNAIL AS ContactThumbNail, cont.CREATEDBYID AS ContactCreatedById, cont.CREATEDON AS ContactCreatedOn, cont.MODIFIEDBYID AS ContactModifiedById, cont.MODIFIEDON AS ContactModifiedOn, cont.SORTORDER AS ContactSortOrder, cont.STATUS AS ContactStatus, cont.VERSION AS ContactVersion, cont.SOURCETYPE AS ContactSourceType FROM [DBO].MEMPLOYEE emp -- Common Employee Lookup Joins LEFT JOIN [DBO].MEMPLOYEE empcommon ON emp.EMPLOYEECOMMONID = empcommon.EMPLOYEEID LEFT JOIN [DBO].MCODEDEFINE cd ON emp.CODEDEFINEID = cd.CODEDEFINEID LEFT JOIN [DBO].MDEPARTMENT dept ON emp.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN [DBO].MVILLAGE vil ON emp.VILLAGEID = vil.VILLAGEID LEFT JOIN [DBO].MCOSTCENTER cc ON emp.COSTCENTERID = cc.COSTCENTERID LEFT JOIN [DBO].MORGANIZATIONUNIT ou ON emp.WORKOUID = ou.OUID LEFT JOIN [DBO].MORGANIZATIONGROUP oug ON emp.OUGROUPID = oug.ORGANIZATIONGROUPID LEFT JOIN [DBO].MPAYGROUP pg ON emp.PAYGROUPID = pg.PAYGROUPID LEFT JOIN [DBO].MGCM wt ON emp.WORKTYPEID = wt.GCMID LEFT JOIN [DBO].MGCM et ON emp.EMPLOYEETYPEID = et.GCMID LEFT JOIN [DBO].MGCM st ON emp.STAFFTYPEID = st.GCMID LEFT JOIN [DBO].MGCM des ON emp.DESIGNATIONID = des.GCMID LEFT JOIN [DBO].MGCM nat ON emp.NATIONALITYID = nat.GCMID LEFT JOIN [DBO].MGCM rel ON emp.RELIGIONID = rel.GCMID LEFT JOIN [DBO].MLOCATION loc ON emp.LOCATIONID = loc.LOCATIONID LEFT JOIN [DBO].MGCM caste ON emp.CASTEID = caste.GCMID LEFT JOIN [DBO].MGCM sg ON emp.SALARYGRADEID = sg.GCMID LEFT JOIN [DBO].MDAPOINTGROUP dapg ON emp.DAPOINTGROUPID = dapg.DAPOINTGROUPID LEFT JOIN [DBO].MBANKBRANCH bankbranch ON emp.EMPLOYEEBANKBRANCHID = bankbranch.BANKBRANCHID LEFT JOIN [DBO].MGCM bank ON bankbranch.BANKID = bank.GCMID LEFT JOIN [DBO].MSHIFT shift ON emp.SHIFTID = shift.SHIFTID LEFT JOIN [DBO].MACCOUNT acc ON emp.ACCOUNTID = acc.ACCOUNTID LEFT JOIN [DBO].MEMPLOYEE repto ON emp.REPORTINGTOEMPLOYEEID = repto.EMPLOYEEID LEFT JOIN [DBO].MACCOUNT advacc ON emp.ADVANCEACCOUNTID = advacc.ACCOUNTID LEFT JOIN [DBO].MACCOUNT salacc ON emp.SALARYACCOUNTID = salacc.ACCOUNTID LEFT JOIN [DBO].MGCM seg ON emp.SEGMENTID = seg.GCMID LEFT JOIN [DBO].MGCM unit ON emp.UNITID = unit.GCMID LEFT JOIN [DBO].MSHIFTPATTERNDETAIL spd ON emp.SHIFTPATTERNDETAILID = spd.SHIFTPATTERNDETAILID LEFT JOIN [DBO].MCALENDAR cal ON emp.CALENDARID = cal.CALENDARID LEFT JOIN [DBO].MRESOURCETYPE rt ON emp.RESOURCETYPEID = rt.RESOURCETYPEID LEFT JOIN [DBO].MPAYCONFIGURATION pc ON emp.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID LEFT JOIN [DBO].MGRATUITYGROUP gg ON emp.GRATUITYGROUPID = gg.GRATUITYGROUPID LEFT JOIN [DBO].MCURRENCY curr ON emp.CURRENCYID = curr.CURRENCYID LEFT JOIN [DBO].MUSER adminrep ON emp.ADMINREPORTINGTOID = adminrep.USERID LEFT JOIN [DBO].MPARTY contractor ON emp.CONTRACTORID = contractor.PARTYID LEFT JOIN [DBO].MJOBPOSITION jp ON emp.JOBPOSITIONID = jp.JOBPOSITIONID LEFT JOIN [DBO].MORGANIZATIONUNIT currentou ON emp.CURRENTOUID = currentou.OUID LEFT JOIN [DBO].MGCM ve ON emp.VISAENTITYID = ve.GCMID -- Present Address LEFT JOIN [DBO].MADDRESS presaddr ON emp.PRESENTADDRESSID = presaddr.ADDRESSID LEFT JOIN [DBO].MCITY prescity ON presaddr.CITYID = prescity.CITYID LEFT JOIN [DBO].MCOUNTRY prescountry ON presaddr.COUNTRYID = prescountry.COUNTRYID LEFT JOIN [DBO].MSTATE presstate ON presaddr.STATEID = presstate.STATEID LEFT JOIN [DBO].MREGION presstdreg ON presaddr.STANDARDREGIONID = presstdreg.REGIONID LEFT JOIN [DBO].MLOCATION presloc ON presaddr.LOCATIONID = presloc.LOCATIONID -- Permanent Address LEFT JOIN [DBO].MADDRESS permaddr ON emp.PERMANENTADDRESSID = permaddr.ADDRESSID LEFT JOIN [DBO].MCITY permcity ON permaddr.CITYID = permcity.CITYID LEFT JOIN [DBO].MCOUNTRY permcountry ON permaddr.COUNTRYID = permcountry.COUNTRYID LEFT JOIN [DBO].MSTATE permstate ON permaddr.STATEID = permstate.STATEID LEFT JOIN [DBO].MREGION permstdreg ON permaddr.STANDARDREGIONID = permstdreg.REGIONID LEFT JOIN [DBO].MLOCATION permloc ON permaddr.LOCATIONID = permloc.LOCATIONID -- Contact LEFT JOIN [DBO].MCONTACT cont ON emp.CONTACTID = cont.CONTACTID LEFT JOIN [DBO].MCONTACT contrep ON cont.REPORTINGTOID = contrep.CONTACTID -- EmployeeAddon deliberately NOT joined here — MEMPLOYEEADDON's column set is -- client-defined (each tenant may have different or no addon fields configured), -- so hardcoding specific column names (PERMOBNUM, ENTRYLONG, ...) broke this query -- outright for any tenant without those exact columns. EmployeeBLL.GetEmployee -- already fetches addon data safely via IAddonService.GetAddonJsonAsync (a -- `SELECT *` that adapts to whatever columns actually exist) and overwrites -- dto.EmployeeAddon afterward — this JOIN was redundant as well as broken. WHERE emp.EMPLOYEEID = @employeeid;"; public const string GET_EMPLOYEE_PG = @" SELECT -- Employee Core Fields emp.employeeid AS EmployeeId, emp.employeecommonid AS EmployeeCommonId, emp.codedefineid AS CodeDefineId, emp.employeecode AS EmployeeCode, emp.employeename AS EmployeeName, emp.fathername AS EmployeeFatherName, emp.mothername AS EmployeeMotherName, emp.spousename AS EmployeeSpouseName, emp.departmentid AS DepartmentId, emp.costcentertype AS EmployeeCostCenterType, emp.costcenterid AS CostCenterId, emp.costcenterpatternid AS CostCenterPatternId, emp.workouid AS WorkOUId, emp.iscrossou AS EmployeeIsCrossOU, emp.ougroupid AS OUGroupId, emp.paygroupid AS PayGroupId, emp.worktypeid AS WorkTypeId, emp.employeetypeid AS EmployeeTypeId, emp.stafftypeid AS StaffTypeId, emp.designationid AS DesignationId, emp.nationalityid AS NationalityId, emp.religionid AS ReligionId, emp.locationid AS LocationId, emp.casteid AS CasteId, emp.heightincms AS EmployeeHeightInCMS, emp.weightinkgs AS EmployeeWeightInKGS, emp.chestsize AS EmployeeChestSize, emp.ishandicapped AS EmployeeIsHandiCapped, emp.identificationmark AS EmployeeIdentificationMark, emp.physicalcondition AS EmployeePhysicalCondition, emp.employeestatus AS EmployeeEmployerStatus, CAST(emp.dateofbirth AS TIMESTAMP) AS EmployeeDateOfBirth, CAST(emp.actualdateofbirth AS TIMESTAMP) AS EmployeeActualDateOfBirth, CAST(emp.dateofjoining AS TIMESTAMP) AS EmployeeDateOfJoining, emp.isconfirmed AS EmployeeIsConfirmed, CAST(emp.dateofconfirmation AS TIMESTAMP) AS EmployeeDateOfConfirmation, CAST(emp.dateofresignation AS TIMESTAMP) AS EmployeeDateOfResignation, CAST(emp.calculateddateofretirement AS TIMESTAMP) AS EmployeeCalculatedDateOfRetirment, CAST(emp.actualdateofretirement AS TIMESTAMP) AS EmployeeActualDateOfRetirment, emp.maritalstatus AS EmployeeMaritalStatus, emp.sex AS EmployeeSex, CASE WHEN emp.sex = 0 THEN 'Male' WHEN emp.sex = 1 THEN 'Female' ELSE 'Other' END AS SexName, emp.bloodgroup AS EmployeeBloodGroup, emp.ispfapplicable AS EmployeeIsPFApplicable, emp.pfstatus AS EmployeePFStatus, emp.pfnumber AS EmployeePFNumber, emp.statutorypfpercentage AS EmployeeStatutorypfPercentage, emp.employeepfpercentage AS EmployeeEmployeePFPercentage, emp.employerpfpercentage AS EmployeeEmployerPFPercentage, emp.isfamilypension AS EmployeeIsFamilyPension, emp.familypensionnumber AS EmployeeFamilyPensionNumber, emp.familypensionpercentage AS EmployeeFamilyPensionPercentage, CAST(emp.pfjoindate AS TIMESTAMP) AS EmployeePFJoinDate, CAST(emp.pfenddate AS TIMESTAMP) AS EmployeePFEndDate, emp.isesiapplicable AS EmployeeIsESIApplicable, emp.esistatus AS EmployeeESIStatus, emp.esinumber AS EmployeeESINumber, CAST(emp.esijoindate AS TIMESTAMP) AS EmployeeESIJoinDate, CAST(emp.esienddate AS TIMESTAMP) AS EmployeeESIEndDate, emp.esidispensary AS EmployeeESIDispensary, emp.employeeesipercentage AS EmployeeEmployeeESIPercentage, emp.employeresipercentage AS EmployeeEmployerESIPercentage, emp.isesihourlycontribution AS EmployeeIsESIHourlyContribution, emp.basicon AS EmployeeBasicOn, emp.paymode AS EmployeePayMode, emp.otpaymode AS EmployeeOTPayMode, emp.ottype AS EmployeeOtType, emp.istdsapplicable AS EmployeeIsTDSApplicable, emp.salarygradeid AS SalaryGradeId, emp.dapointgroupid AS DAPointGroupId, emp.pannumber AS EmployeePANNumber, emp.accountnumber AS EmployeeAccountNumber, emp.shiftid AS ShiftId, emp.weekoffday AS EmployeeWeekOfDay, emp.accountid AS AccountId, emp.nominee AS EmployeeNominee, emp.nomineerelation AS EmployeeNomineeRelation, emp.attendancetype AS EmployeeAttendanceType, emp.onsiteplace AS EmployeeOnSitePlace, emp.isdailyreport AS EmployeeIsDailyReport, emp.punchtype AS EmployeePunchType, emp.primaryemailid AS EmployeePrimaryEMailId, emp.primarymobile AS EmployeePrimaryMobile, emp.reportingtoemployeeid AS ReportInttoEmployeeId, emp.experience AS EmployeeExperience, emp.istemporarycard AS EmployeeIsTemporaryCard, emp.temporarycardnumber AS EmployeeTemporaryCardNumber, CAST(emp.cardvalidtill AS TIMESTAMP) AS EmployeeCardValidTill, emp.isnumberrequired AS EmployeeIsNumberRequired, emp.biomatchnumber AS EmployeeBioMatchNumber, emp.advanceaccountid AS AdvanceAccountId, emp.salaryaccountid AS SalaryAccountId, emp.defaultimageid AS EmployeeDefaultImage, emp.iscontract AS EmployeeIsContract, emp.isnumber AS EmployeeIsNumber, emp.ispurchase AS EmployeeIsPurchase, emp.ismarketing AS EmployeeIsMarketing, emp.issales AS EmployeeIsSales, emp.isplanning AS EmployeeIsPlanning, emp.isservice AS EmployeeIsService, emp.isuser AS EmployeeIsUser, emp.version AS EmployeeVersion, emp.status AS EmployeeStatus, emp.createdbyid AS EmployeeCreatedById, emp.createdon AS EmployeeCreatedOn, emp.modifiedbyid AS EmployeeModifiedById, emp.modifiedon AS EmployeeModifiedOn, emp.segmentid AS SegmentId, emp.unitid AS UnitId, emp.shiftpatterndetailid AS ShiftPatternDetailId, CAST(emp.patterndate AS TIMESTAMP) AS EmployeePatternDate, emp.reasonforesileaving AS EmployeeReasonForESILeaving, emp.reasonforleaving AS EmployeeReasonForLeaving, emp.reasonforpfleaving AS EmployeeReasonForPFLeaving, emp.villageid AS VillageId, emp.sourcetype AS SourceType, emp.sortorder AS EmployeeSortOrder, CAST(emp.probationstartdate AS TIMESTAMP) AS EmployeeProbationStartDate, emp.calendarid AS CalendarId, emp.resourcetypeid AS ResourceTypeId, emp.isproduction AS EmployeeIsProduction, emp.adharnumber AS EmployeeAdharNumber, emp.pfuannumber AS EmployeePFUANNumber, emp.payconfigurationid AS PayConfigurationId, emp.productcategoryids AS EmployeeProductCategoryIds, emp.productpricecategoryids AS EmployeeProductPriceCategoryIds, emp.tenantid AS TenantId, emp.ispirequired AS EmployeeIsPiRequired, emp.gratuitygroupid AS GratuityGroupId, emp.currencyid AS CurrencyId, emp.adminreportingtoid AS AdminReportingToId, emp.firstname AS FirstName, emp.middlename AS MiddleName, emp.lastname AS LastName, emp.noofverification AS EmployeeNoOfVerfication, emp.locationids AS EmployeeLocationIds, emp.secondarymailid AS SecondaryMailId, emp.secondarymobile AS SecondaryMobile, emp.ibannumber AS EmployeeIBANNumber, emp.employeebankbranchid AS EmployeeBankBranchId, emp.presentaddressid AS EmployeePresentAddressId, emp.permanentaddressid AS EmployeePermenantAddressId, emp.contactid AS ContactId, emp.trainingperiod AS EmployeeTrainingPeriod, CAST(emp.trainingendingdate AS TIMESTAMP) AS EmployeeTrainingendingDate, emp.istimesheetrequired AS EmployeeIsTimeSheetRequired, emp.ispostingrequired AS EmployeeIsPostingRequired, emp.placeofbirth AS EmployeePlaceOfBirth, emp.nativeplace AS EmployeeNativePlace, emp.nativedistrict AS EmployeeNativeDistrict, emp.noticeperiodindays AS EmployeeNoticePeriodInDays, CAST(emp.dateofrejoin AS TIMESTAMP) AS EmployeeDateOfReJoin, emp.legacyemployeecode AS EmployeeLegacyEmployeeCode, emp.nature AS EmployeeNature, CAST(emp.versionfrom AS TIMESTAMP) AS EmployeeVersionFrom, emp.jobpositionid AS JobPositionId, emp.currentouid AS CurrentOUId, emp.thumbnail AS EmployeeThumbNail, emp.visaentityid AS VisaEntityId, emp.graceminutes AS EmployeeGraceMinutes, CAST(emp.dateofintimation AS TIMESTAMP) AS EmployeeDateOfIntimation, -- Lookup / Joined Fields empcommon.employeecode AS EmployeeCommonCode, empcommon.employeename AS EmployeeCommonName, cd.categorycode AS CodeDefineCategoryCode, cd.categoryname AS CodeDefineCategoryName, dept.departmentcode AS DepartmentCode, dept.departmentname AS DepartmentName, vil.villagecode AS VillageCode, vil.villagename AS VillageName, cc.costcentercode AS CostCenterCode, cc.costcentername AS CostCenterName, ou.organizationunitcode AS WorkOUCode, ou.organizationunitname AS WorkOUName, oug.organizationgroupcode AS OUGroupCode, oug.organizationgroupname AS OUGroupName, pg.paygroupcode AS PayGroupCode, pg.paygroupname AS PayGroupName, wt.gcmcode AS WorkTypeCode, wt.gcmname AS WorkTypeName, et.gcmcode AS EmployeeTypeCode, et.gcmname AS EmployeeTypeName, st.gcmcode AS StaffTypeCode, st.gcmname AS StaffTypeName, des.gcmcode AS DesignationCode, des.gcmname AS DesignationName, nat.gcmcode AS NationalityCode, nat.gcmname AS NationalityName, rel.gcmcode AS ReligionCode, rel.gcmname AS ReligionName, loc.locationcode AS LocationCode, loc.locationname AS LocationName, loc.geocode AS LocationGeoCode, caste.gcmcode AS CasteCode, caste.gcmname AS CasteName, sg.gcmcode AS SalaryGradeCode, sg.gcmname AS SalaryGradeName, dapg.dapointgroupcode AS DAPointGroupCode, dapg.dapointgroupname AS DAPointGroupName, bankbranch.bankbranchname AS BankBranchName, bank.gcmcode AS BankCode, bank.gcmname AS BankName, bankbranch.bankid AS BankId, shift.shiftcode AS ShiftCode, shift.shiftdescription AS ShiftDescription, acc.accountcode AS AccountCode, acc.accountname AS AccountName, repto.employeecode AS ReportInttoEmployeeCode, repto.employeename AS ReportInttoEmployeeName, advacc.accountcode AS AdvanceAccountCode, advacc.accountname AS AdvanceAccountName, salacc.accountcode AS SalaryAccountCode, salacc.accountname AS SalaryAccountName, seg.gcmcode AS SegmentCode, seg.gcmname AS SegmentName, unit.gcmcode AS UnitCode, unit.gcmname AS UnitName, spd.slno AS ShiftPatternDetailSlno, spd.daytype AS ShiftPatternDetailDayType, cal.calendarcode AS CalendarCode, cal.calendarname AS CalendarName, rt.resourcetypecode AS ResourceTypeCode, rt.resourcetypename AS ResourceTypeName, pc.payconfigurationcode AS PayConfigurationCode, pc.payconfigurationname AS PayConfigurationName, pc.ispartialprocessrequired AS PayConfigurationIsPartialProcessRequired, gg.gratuitygroupcode AS GratuityGroupCode, gg.gratuitygroupname AS GratuityGroupName, curr.currencycode AS CurrencyCode, curr.currencyname AS CurrencyName, adminrep.usercode AS AdminReportingToCode, adminrep.username AS AdminReportingToName, jp.jobpositioncode AS JobPositionCode, jp.jobpositionname AS JobPositionName, currentou.organizationunitcode AS CurrentOUCode, currentou.organizationunitname AS CurrentOUName, ve.gcmcode AS VisaEntityCode, ve.gcmname AS VisaEntityName, -- Placeholder only — see matching note in GET_EMPLOYEE (SQL Server variant above). emp.employeeid AS AddonEmployeeId, -- Present Address presaddr.addressid AS AddressId, presaddr.objecttypeid AS AddressObjectTypeId, presaddr.objectid AS AddressObjectId, presaddr.addresstype AS AddressAddressType, presaddr.addressnature AS AddressAddressNature, presaddr.addressline1 AS AddressLine1, presaddr.addressline2 AS AddressLine2, presaddr.addressline3 AS AddressLine3, presaddr.addressline4 AS AddressLine4, presaddr.addressline5 AS AddressLine5, presaddr.zipcode AS AddressZipCode, presaddr.cityid AS CityId, prescity.citycode AS CityCode, prescity.cityname AS CityName, presaddr.countryid AS CountryId, prescountry.countrycode AS CountryCode, prescountry.countryname AS CountryName, prescountry.isdcode AS CountryISDCode, presaddr.phone AS AddressPhone, presaddr.mobile AS AddressMobile, presaddr.fax AS AddressFax, presaddr.web AS AddressWeb, presaddr.geocode AS AddressGeoCode, presaddr.mail AS AddressMail, presaddr.gstin AS AddressGSTIn, presaddr.stateid AS StateId, presstate.statecode AS StateCode, presstate.statename AS StateName, presstate.gststatecode AS StateGSTStateCode, presstate.tinstart AS StateTINStart, presaddr.standardregionid AS StandardRegionId, presstdreg.regioncode AS StandardRegionCode, presstdreg.regionname AS StandardRegionName, presaddr.locationid AS LocationId, presloc.locationcode AS LocationCode, presloc.locationname AS LocationName, presaddr.createdbyid AS AddressCreatedById, presaddr.createdon AS AddressCreatedOn, presaddr.modifiedbyid AS AddressModifiedById, presaddr.modifiedon AS AddressModifiedOn, presaddr.sortorder AS AddressSortOrder, presaddr.version AS AddressVersion, presaddr.sourcetype AS AddressSourceType, presaddr.status AS AddressStatus, -- Permanent Address permaddr.addressid AS PermAddressId, permaddr.objecttypeid AS PermAddressObjectTypeId, permaddr.objectid AS PermAddressObjectId, permaddr.addresstype AS PermAddressAddressType, permaddr.addressnature AS PermAddressAddressNature, permaddr.addressline1 AS PermAddressLine1, permaddr.addressline2 AS PermAddressLine2, permaddr.addressline3 AS PermAddressLine3, permaddr.addressline4 AS PermAddressLine4, permaddr.addressline5 AS PermAddressLine5, permaddr.zipcode AS PermAddressZipCode, permaddr.cityid AS PermCityId, permcity.citycode AS PermCityCode, permcity.cityname AS PermCityName, permaddr.countryid AS PermCountryId, permcountry.countrycode AS PermCountryCode, permcountry.countryname AS PermCountryName, permcountry.isdcode AS PermCountryISDCode, permaddr.phone AS PermAddressPhone, permaddr.mobile AS PermAddressMobile, permaddr.fax AS PermAddressFax, permaddr.web AS PermAddressWeb, permaddr.geocode AS PermAddressGeoCode, permaddr.mail AS PermAddressMail, permaddr.gstin AS PermAddressGSTIn, permaddr.stateid AS PermStateId, permstate.statecode AS PermStateCode, permstate.statename AS PermStateName, permstate.gststatecode AS PermStateGSTStateCode, permstate.tinstart AS PermStateTINStart, permaddr.standardregionid AS PermStandardRegionId, permstdreg.regioncode AS PermStandardRegionCode, permstdreg.regionname AS PermStandardRegionName, permaddr.locationid AS PermLocationId, permloc.locationcode AS PermLocationCode, permloc.locationname AS PermLocationName, permaddr.createdbyid AS PermAddressCreatedById, permaddr.createdon AS PermAddressCreatedOn, permaddr.modifiedbyid AS PermAddressModifiedById, permaddr.modifiedon AS PermAddressModifiedOn, permaddr.sortorder AS PermAddressSortOrder, permaddr.version AS PermAddressVersion, permaddr.sourcetype AS PermAddressSourceType, permaddr.status AS PermAddressStatus, -- Contact cont.contactid AS ContactId, cont.objecttypeid AS ContactObjectTypeId, cont.objectid AS ContactObjectId, cont.contactnature AS ContactContactNature, cont.name AS ContactName, cont.firstname AS ContactFirstName, cont.middlename AS ContactMiddleName, cont.lastname AS ContactLastName, cont.salutation AS ContactSalutation, cont.sex AS ContactSex, CASE WHEN cont.sex = 0 THEN 'MALE' WHEN cont.sex = 1 THEN 'FEMALE' ELSE 'OTHER' END AS ContactSexName, CAST(cont.dob AS TIMESTAMP) AS ContactDob, cont.mailid AS ContactMailId, cont.phoneno AS ContactPhoneNo, cont.mobileno AS ContactMobileNo, cont.relationship AS ContactRelationShip, cont.designation AS ContactDesignation, cont.company AS ContactCompany, cont.defaultimageid AS ContactDefaultImageId, cont.linktype AS ContactLinkType, cont.reportingtoid AS ReportingToId, cont.userlinkid AS UserLinkId, cont.thumbnail AS ContactThumbNail, cont.createdbyid AS ContactCreatedById, cont.createdon AS ContactCreatedOn, cont.modifiedbyid AS ContactModifiedById, cont.modifiedon AS ContactModifiedOn, cont.sortorder AS ContactSortOrder, cont.status AS ContactStatus, cont.version AS ContactVersion, cont.sourcetype AS ContactSourceType FROM public.memployee emp LEFT JOIN public.memployee empcommon ON emp.employeecommonid = empcommon.employeeid LEFT JOIN public.mcodedefine cd ON emp.codedefineid = cd.codedefineid LEFT JOIN public.mdepartment dept ON emp.departmentid = dept.departmentid LEFT JOIN public.mvillage vil ON emp.villageid = vil.villageid LEFT JOIN public.mcostcenter cc ON emp.costcenterid = cc.costcenterid LEFT JOIN public.morganizationunit ou ON emp.workouid = ou.ouid LEFT JOIN public.morganizationgroup oug ON emp.ougroupid = oug.organizationgroupid LEFT JOIN public.mpaygroup pg ON emp.paygroupid = pg.paygroupid LEFT JOIN public.mgcm wt ON emp.worktypeid = wt.gcmid LEFT JOIN public.mgcm et ON emp.employeetypeid = et.gcmid LEFT JOIN public.mgcm st ON emp.stafftypeid = st.gcmid LEFT JOIN public.mgcm des ON emp.designationid = des.gcmid LEFT JOIN public.mgcm nat ON emp.nationalityid = nat.gcmid LEFT JOIN public.mgcm rel ON emp.religionid = rel.gcmid LEFT JOIN public.mlocation loc ON emp.locationid = loc.locationid LEFT JOIN public.mgcm caste ON emp.casteid = caste.gcmid LEFT JOIN public.mgcm sg ON emp.salarygradeid = sg.gcmid LEFT JOIN public.mdapointgroup dapg ON emp.dapointgroupid = dapg.dapointgroupid LEFT JOIN public.mbankbranch bankbranch ON emp.employeebankbranchid = bankbranch.bankbranchid LEFT JOIN public.mgcm bank ON bankbranch.bankid = bank.gcmid LEFT JOIN public.mshift shift ON emp.shiftid = shift.shiftid LEFT JOIN public.maccount acc ON emp.accountid = acc.accountid LEFT JOIN public.memployee repto ON emp.reportingtoemployeeid = repto.employeeid LEFT JOIN public.maccount advacc ON emp.advanceaccountid = advacc.accountid LEFT JOIN public.maccount salacc ON emp.salaryaccountid = salacc.accountid LEFT JOIN public.mgcm seg ON emp.segmentid = seg.gcmid LEFT JOIN public.mgcm unit ON emp.unitid = unit.gcmid LEFT JOIN public.mshiftpatterndetail spd ON emp.shiftpatterndetailid = spd.shiftpatterndetailid LEFT JOIN public.mcalendar cal ON emp.calendarid = cal.calendarid LEFT JOIN public.mresourcetype rt ON emp.resourcetypeid = rt.resourcetypeid LEFT JOIN public.mpayconfiguration pc ON emp.payconfigurationid = pc.payconfigurationid LEFT JOIN public.mgratuitygroup gg ON emp.gratuitygroupid = gg.gratuitygroupid LEFT JOIN public.mcurrency curr ON emp.currencyid = curr.currencyid LEFT JOIN public.muser adminrep ON emp.adminreportingtoid = adminrep.userid LEFT JOIN public.mjobposition jp ON emp.jobpositionid = jp.jobpositionid LEFT JOIN public.morganizationunit currentou ON emp.currentouid = currentou.ouid LEFT JOIN public.mgcm ve ON emp.visaentityid = ve.gcmid LEFT JOIN public.maddress presaddr ON emp.presentaddressid = presaddr.addressid LEFT JOIN public.mcity prescity ON presaddr.cityid = prescity.cityid LEFT JOIN public.mcountry prescountry ON presaddr.countryid = prescountry.countryid LEFT JOIN public.mstate presstate ON presaddr.stateid = presstate.stateid LEFT JOIN public.mregion presstdreg ON presaddr.standardregionid = presstdreg.regionid LEFT JOIN public.mlocation presloc ON presaddr.locationid = presloc.locationid LEFT JOIN public.maddress permaddr ON emp.permanentaddressid = permaddr.addressid LEFT JOIN public.mcity permcity ON permaddr.cityid = permcity.cityid LEFT JOIN public.mcountry permcountry ON permaddr.countryid = permcountry.countryid LEFT JOIN public.mstate permstate ON permaddr.stateid = permstate.stateid LEFT JOIN public.mregion permstdreg ON permaddr.standardregionid = permstdreg.regionid LEFT JOIN public.mlocation permloc ON permaddr.locationid = permloc.locationid LEFT JOIN public.mcontact cont ON emp.contactid = cont.contactid LEFT JOIN public.mcontact contrep ON cont.reportingtoid = contrep.contactid WHERE emp.employeeid = @employeeid;"; public const string SAVE_EMPLOYEE = @" INSERT INTO MEMPLOYEE ( EMPLOYEEID,EMPLOYEECOMMONID,CODEDEFINEID,EMPLOYEECODE,EMPLOYEENAME,FATHERNAME,MOTHERNAME, SPOUSENAME,DEPARTMENTID,COSTCENTERTYPE,COSTCENTERID,COSTCENTERPATTERNID,WORKOUID,ISCROSSOU,OUGROUPID,PAYGROUPID,WORKTYPEID,EMPLOYEETYPEID,STAFFTYPEID,DESIGNATIONID, NATIONALITYID,RELIGIONID,LOCATIONID,CASTEID,HEIGHTINCMS,WEIGHTINKGS,CHESTSIZE,ISHANDICAPPED,IDENTIFICATIONMARK,PHYSICALCONDITION,EMPLOYEESTATUS,DATEOFBIRTH,ACTUALDATEOFBIRTH, DATEOFJOINING,ISCONFIRMED,DATEOFCONFIRMATION,DATEOFRESIGNATION,CALCULATEDDATEOFRETIREMENT,ACTUALDATEOFRETIREMENT,MARITALSTATUS,SEX,BLOODGROUP,ISPFAPPLICABLE,PFSTATUS,PFNUMBER, STATUTORYPFPERCENTAGE,EMPLOYEEPFPERCENTAGE,EMPLOYERPFPERCENTAGE,ISFAMILYPENSION,FAMILYPENSIONNUMBER,FAMILYPENSIONPERCENTAGE,PFJOINDATE,PFENDDATE,ISESIAPPLICABLE,ESISTATUS, ESINUMBER,ESIJOINDATE,ESIENDDATE,ESIDISPENSARY,EMPLOYEEESIPERCENTAGE,ISESIHOURLYCONTRIBUTION,BASICON,PAYMODE,OTPAYMODE,ISTDSAPPLICABLE,SALARYGRADEID,DAPOINTGROUPID,PANNUMBER, ACCOUNTNUMBER,SHIFTID,WEEKOFFDAY,ACCOUNTID,NOMINEE,NOMINEERELATION,ATTENDANCETYPE,ONSITEPLACE,ISDAILYREPORT,PUNCHTYPE,PRIMARYEMAILID,PRIMARYMOBILE,REPORTINGTOEMPLOYEEID, EXPERIENCE,ISTEMPORARYCARD,TEMPORARYCARDNUMBER,CARDVALIDTILL,ISNUMBERREQUIRED,BIOMATCHNUMBER,ADVANCEACCOUNTID,SALARYACCOUNTID,DEFAULTIMAGEID,ISCONTRACT,ISNUMBER,ISPURCHASE, ISMARKETING,ISSALES,ISPLANNING,ISSERVICE,ISUSER,VERSION,STATUS,CREATEDBYID,CREATEDON,MODIFIEDBYID,MODIFIEDON,SEGMENTID,UNITID,SHIFTPATTERNDETAILID,PATTERNDATE,REASONFORESILEAVING, REASONFORPFLEAVING,VILLAGEID,EMPLOYERESIPERCENTAGE,SOURCETYPE,SORTORDER,OTTYPE,CALENDARID,PROBATIONSTARTDATE,RESOURCETYPEID,ISPRODUCTION,ADHARNUMBER,PFUANNUMBER,PAYCONFIGURATIONID, PRODUCTCATEGORYIDS,PRODUCTPRICECATEGORYIDS,TENANTID,ISPIREQUIRED,GRATUITYGROUPID,CURRENCYID,ADMINREPORTINGTOID,CONTRACTORID,FIRSTNAME,MIDDLENAME,LASTNAME,NOOFVERIFICATION,LOCATIONIDS, SECONDARYMAILID,SECONDARYMOBILE,IBANNUMBER,EMPLOYEEBANKBRANCHID,PRESENTADDRESSID,PERMANENTADDRESSID,CONTACTID,TRAININGPERIOD,TRAININGENDINGDATE,ISTIMESHEETREQUIRED, ISPOSTINGREQUIRED,DATEOFINTIMATION,REASONFORLEAVING,PLACEOFBIRTH,NATIVEPLACE,NATIVEDISTRICT,NOTICEPERIODINDAYS,DATEOFREJOIN,LEGACYEMPLOYEECODE,NATURE,VERSIONFROM, JOBPOSITIONID,CURRENTOUID,THUMBNAIL,VISAENTITYID,GRACEMINUTES ) VALUES ( @EmployeeId,@EmployeeCommonId,@CodeDefineId,@EmployeeCode,@EmployeeName,@EmployeeFatherName,@EmployeeMotherName, @EmployeeSpouseName,@DepartmentId,@EmployeeCostCenterType,@CostCenterId,@CostCenterPatternId,@WorkOUId,@EmployeeIsCrossOU,@OUGroupId,@PayGroupId,@WorkTypeId,@EmployeeTypeId,@StaffTypeId,@DesignationId, @NationalityId,@ReligionId,@LocationId,@CasteId,@EmployeeHeightInCMS,@EmployeeWeightInKGS,@EmployeeChestSize,@EmployeeIsHandiCapped,@EmployeeIdentificationMark,@EmployeePhysicalCondition, @EmployeeEmployerStatus,@EmployeeDateOfBirth,@EmployeeActualDateOfBirth,@EmployeeDateOfJoining,@EmployeeIsConfirmed,@EmployeeDateOfConfirmation,@EmployeeDateOfResignation, @EmployeeCalculatedDateOfRetirment,@EmployeeActualDateOfRetirment,@EmployeeMaritalStatus,@EmployeeSex,@EmployeeBloodGroup,@EmployeeIsPFApplicable,@EmployeePFStatus,@EmployeePFNumber, @EmployeeStatutorypfPercentage,@EmployeeEmployeePFPercentage,@EmployeeEmployerPFPercentage,@EmployeeIsFamilyPension,@EmployeeFamilyPensionNumber,@EmployeeFamilyPensionPercentage, @EmployeePFJoinDate,@EmployeePFEndDate,@EmployeeIsESIApplicable,@EmployeeESIStatus,@EmployeeESINumber,@EmployeeESIJoinDate,@EmployeeESIEndDate,@EmployeeESIDispensary, @EmployeeEmployeeESIPercentage,@EmployeeIsESIHourlyContribution,@EmployeeBasicOn,@EmployeePayMode,@EmployeeOTPayMode,@EmployeeIsTDSApplicable,@SalaryGradeId,@DAPointGroupId, @EmployeePANNumber,@EmployeeAccountNumber,@ShiftId,@EmployeeWeekOfDay,@AccountId,@EmployeeNominee,@EmployeeNomineeRelation,@EmployeeAttendanceType,@EmployeeOnSitePlace, @EmployeeIsDailyReport,@EmployeePunchType,@EmployeePrimaryEMailId,@EmployeePrimaryMobile,@ReportInttoEmployeeId,@EmployeeExperience,@EmployeeIsTemporaryCard, @EmployeeTemporaryCardNumber,@EmployeeCardValidTill,@EmployeeIsNumberRequired,@EmployeeBioMatchNumber,@AdvanceAccountId,@SalaryAccountId,@EmployeeDefaultImage,@EmployeeIsContract, @EmployeeIsNumber,@EmployeeIsPurchase,@EmployeeIsMarketing,@EmployeeIsSales,@EmployeeIsPlanning,@EmployeeIsService,@EmployeeIsUser,@EmployeeVersion,@EmployeeStatus, @EmployeeCreatedById,@EmployeeCreatedOn,@EmployeeModifiedById,@EmployeeModifiedOn,@SegmentId,@UnitId,@ShiftPatternDetailId,@EmployeePatternDate,@EmployeeReasonForESILeaving, @EmployeeReasonForPFLeaving,@VillageId,@EmployeeEmployerESIPercentage,@SourceType,@EmployeeSortOrder,@EmployeeOtType,@CalendarId,@EmployeeProbationStartDate,@ResourceTypeId, @EmployeeIsProduction,@EmployeeAdharNumber,@EmployeePFUANNumber,@PayConfigurationId,@EmployeeProductCategoryIds,@EmployeeProductPriceCategoryIds,@TenantId,@EmployeeIsPiRequired, @GratuityGroupId,@CurrencyId,@AdminReportingToId,@ContractorId,@FirstName,@MiddleName,@LastName,@EmployeeNoOfVerfication,@EmployeeLocationIds,@SecondaryMailId,@SecondaryMobile,@EmployeeIBANNumber, @EmployeeBankBranchId,@EmployeePresentAddressId,@EmployeePermenantAddressId,@EmployeeContactId,@EmployeeTrainingPeriod,@EmployeeTrainingendingDate,@EmployeeIsTimeSheetRequired,@EmployeeIsPostingRequired, @EmployeeDateOfIntimation,@EmployeeReasonForLeaving,@EmployeePlaceOfBirth,@EmployeeNativePlace,@EmployeeNativeDistrict,@EmployeeNoticePeriodInDays,@EmployeeDateOfReJoin, @EmployeeLegacyEmployeeCode,@EmployeeNature,@EmployeeVersionFrom,@JobPositionId,@CurrentOUId,@EmployeeThumbNail,@VisaEntityId,@EmployeeGraceMinutes );"; public const string UPDATE_EMPLOYEE = @"UPDATE MEMPLOYEE SET EMPLOYEECOMMONID = @EmployeeCommonId,CODEDEFINEID = @CodeDefineId,EMPLOYEECODE = @EmployeeCode,EMPLOYEENAME = @EmployeeName,FATHERNAME = @EmployeeFatherName, MOTHERNAME = @EmployeeMotherName,SPOUSENAME = @EmployeeSpouseName,DEPARTMENTID = @DepartmentId,COSTCENTERTYPE = @EmployeeCostCenterType,COSTCENTERID = @CostCenterId,COSTCENTERPATTERNID = @CostCenterPatternId, WORKOUID = @WorkOUId,ISCROSSOU = @EmployeeIsCrossOU,OUGROUPID = @OUGroupId,PAYGROUPID = @PayGroupId,WORKTYPEID = @WorkTypeId,EMPLOYEETYPEID = @EmployeeTypeId,STAFFTYPEID = @StaffTypeId, DESIGNATIONID = @DesignationId,NATIONALITYID = @NationalityId,RELIGIONID = @ReligionId,LOCATIONID = @LocationId,CASTEID = @CasteId,HEIGHTINCMS = @EmployeeHeightInCMS, WEIGHTINKGS = @EmployeeWeightInKGS,CHESTSIZE = @EmployeeChestSize,ISHANDICAPPED = @EmployeeIsHandiCapped,IDENTIFICATIONMARK = @EmployeeIdentificationMark,PHYSICALCONDITION = @EmployeePhysicalCondition, EMPLOYEESTATUS = @EmployeeEmployerStatus,DATEOFBIRTH = @EmployeeDateOfBirth,ACTUALDATEOFBIRTH = @EmployeeActualDateOfBirth,DATEOFJOINING = @EmployeeDateOfJoining,ISCONFIRMED = @EmployeeIsConfirmed, DATEOFCONFIRMATION = @EmployeeDateOfConfirmation,DATEOFRESIGNATION = @EmployeeDateOfResignation,CALCULATEDDATEOFRETIREMENT = @EmployeeCalculatedDateOfRetirment,ACTUALDATEOFRETIREMENT = @EmployeeActualDateOfRetirment, MARITALSTATUS = @EmployeeMaritalStatus,SEX = @EmployeeSex,BLOODGROUP = @EmployeeBloodGroup,ISPFAPPLICABLE = @EmployeeIsPFApplicable,PFSTATUS = @EmployeePFStatus,PFNUMBER = @EmployeePFNumber,STATUTORYPFPERCENTAGE = @EmployeeStatutorypfPercentage, EMPLOYEEPFPERCENTAGE = @EmployeeEmployeePFPercentage,EMPLOYERPFPERCENTAGE = @EmployeeEmployerPFPercentage,ISFAMILYPENSION = @EmployeeIsFamilyPension,FAMILYPENSIONNUMBER = @EmployeeFamilyPensionNumber, FAMILYPENSIONPERCENTAGE = @EmployeeFamilyPensionPercentage,PFJOINDATE = @EmployeePFJoinDate,PFENDDATE = @EmployeePFEndDate,ISESIAPPLICABLE = @EmployeeIsESIApplicable,ESISTATUS = @EmployeeESIStatus,ESINUMBER = @EmployeeESINumber, ESIJOINDATE = @EmployeeESIJoinDate,ESIENDDATE = @EmployeeESIEndDate,ESIDISPENSARY = @EmployeeESIDispensary,EMPLOYEEESIPERCENTAGE = @EmployeeEmployeeESIPercentage,ISESIHOURLYCONTRIBUTION = @EmployeeIsESIHourlyContribution, BASICON = @EmployeeBasicOn,PAYMODE = @EmployeePayMode,OTPAYMODE = @EmployeeOTPayMode,ISTDSAPPLICABLE = @EmployeeIsTDSApplicable,SALARYGRADEID = @SalaryGradeId,DAPOINTGROUPID = @DAPointGroupId,PANNUMBER = @EmployeePANNumber, ACCOUNTNUMBER = @EmployeeAccountNumber,SHIFTID = @ShiftId,WEEKOFFDAY = @EmployeeWeekOfDay,ACCOUNTID = @AccountId,NOMINEE = @EmployeeNominee,NOMINEERELATION = @EmployeeNomineeRelation,ATTENDANCETYPE = @EmployeeAttendanceType, ONSITEPLACE = @EmployeeOnSitePlace,ISDAILYREPORT = @EmployeeIsDailyReport,PUNCHTYPE = @EmployeePunchType,PRIMARYEMAILID = @EmployeePrimaryEMailId,PRIMARYMOBILE = @EmployeePrimaryMobile,REPORTINGTOEMPLOYEEID = @ReportInttoEmployeeId, EXPERIENCE = @EmployeeExperience,ISTEMPORARYCARD = @EmployeeIsTemporaryCard,TEMPORARYCARDNUMBER = @EmployeeTemporaryCardNumber,CARDVALIDTILL = @EmployeeCardValidTill,ISNUMBERREQUIRED = @EmployeeIsNumberRequired, BIOMATCHNUMBER = @EmployeeBioMatchNumber,ADVANCEACCOUNTID = @AdvanceAccountId,SALARYACCOUNTID = @SalaryAccountId,DEFAULTIMAGEID = @EmployeeDefaultImage,ISCONTRACT = @EmployeeIsContract,ISNUMBER = @EmployeeIsNumber, ISPURCHASE = @EmployeeIsPurchase,ISMARKETING = @EmployeeIsMarketing,ISSALES = @EmployeeIsSales,ISPLANNING = @EmployeeIsPlanning,ISSERVICE = @EmployeeIsService,ISUSER = @EmployeeIsUser,VERSION = @EmployeeVersion,STATUS = @EmployeeStatus, CREATEDBYID = @EmployeeCreatedById,CREATEDON = @EmployeeCreatedOn,MODIFIEDBYID = @EmployeeModifiedById,MODIFIEDON = @EmployeeModifiedOn,SEGMENTID = @SegmentId,UNITID = @UnitId,SHIFTPATTERNDETAILID = @ShiftPatternDetailId, PATTERNDATE = @EmployeePatternDate,REASONFORESILEAVING = @EmployeeReasonForESILeaving,REASONFORPFLEAVING = @EmployeeReasonForPFLeaving,VILLAGEID = @VillageId,EMPLOYERESIPERCENTAGE = @EmployeeEmployerESIPercentage, SOURCETYPE = @SourceType,SORTORDER = @EmployeeSortOrder,OTTYPE = @EmployeeOtType,CALENDARID = @CalendarId,PROBATIONSTARTDATE = @EmployeeProbationStartDate,RESOURCETYPEID = @ResourceTypeId,ISPRODUCTION = @EmployeeIsProduction, ADHARNUMBER = @EmployeeAdharNumber,PFUANNUMBER = @EmployeePFUANNumber,PAYCONFIGURATIONID = @PayConfigurationId,PRODUCTCATEGORYIDS = @EmployeeProductCategoryIds,PRODUCTPRICECATEGORYIDS = @EmployeeProductPriceCategoryIds, TENANTID = @TenantId,ISPIREQUIRED = @EmployeeIsPiRequired,GRATUITYGROUPID = @GratuityGroupId,CURRENCYID = @CurrencyId,ADMINREPORTINGTOID = @AdminReportingToId,CONTRACTORID = @ContractorId,FIRSTNAME = @FirstName, MIDDLENAME = @MiddleName,LASTNAME = @LastName,NOOFVERIFICATION = @EmployeeNoOfVerfication,LOCATIONIDS = @EmployeeLocationIds,SECONDARYMAILID = @SecondaryMailId,SECONDARYMOBILE = @SecondaryMobile, IBANNUMBER = @EmployeeIBANNumber,EMPLOYEEBANKBRANCHID = @EmployeeBankBranchId,PRESENTADDRESSID = @EmployeePresentAddressId,PERMANENTADDRESSID = @EmployeePermenantAddressId,CONTACTID = @EmployeeContactId, TRAININGPERIOD = @EmployeeTrainingPeriod,TRAININGENDINGDATE = @EmployeeTrainingendingDate,ISTIMESHEETREQUIRED = @EmployeeIsTimeSheetRequired,ISPOSTINGREQUIRED = @EmployeeIsPostingRequired, DATEOFINTIMATION = @EmployeeDateOfIntimation,REASONFORLEAVING = @EmployeeReasonForLeaving,PLACEOFBIRTH = @EmployeePlaceOfBirth,NATIVEPLACE = @EmployeeNativePlace,NATIVEDISTRICT = @EmployeeNativeDistrict, NOTICEPERIODINDAYS = @EmployeeNoticePeriodInDays,DATEOFREJOIN = @EmployeeDateOfReJoin,LEGACYEMPLOYEECODE = @EmployeeLegacyEmployeeCode,NATURE = @EmployeeNature,VERSIONFROM = @EmployeeVersionFrom, JOBPOSITIONID = @JobPositionId,CURRENTOUID = @CurrentOUId,THUMBNAIL = @EmployeeThumbNail,VISAENTITYID = @VisaEntityId,GRACEMINUTES = @EmployeeGraceMinutes WHERE EMPLOYEEID = @EmployeeId;"; public const string SAVE_TRMEMPLOYEE = @" INSERT INTO TR_MEMPLOYEE (LANGUAGEID, EMPLOYEEID, EMPLOYEENAME, FATHERNAME, MOTHERNAME, SPOUSENAME, FIRSTNAME, MIDDLENAME, LASTNAME, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID) VALUES (@LanguageId, @EmployeeId, @EmployeeName, @EmployeeFatherName, @EmployeeMotherName, @EmployeeSpouseName, @FirstName, @MiddleName, @LastName, @EmployeeSortOrder, @EmployeeStatus, @EmployeeVersion, @EmployeeCreatedById, @EmployeeCreatedOn, @EmployeeModifiedById, @EmployeeModifiedOn, @SourceType, @TenantId);"; public const string COUNT_OF_TRMEMPLOYEE = @" select count(*) from TR_MEMPLOYEE where EMPLOYEEID = @employeeid and LANGUAGEID = @languageid;"; public const string UPDATE_TRMEMPLOYEE = @"UPDATE TR_MEMPLOYEE SET LANGUAGEID = @LANGUAGEID, EMPLOYEEID = @EmployeeId, EMPLOYEENAME = @EmployeeName, FATHERNAME = @EmployeeFatherName, MOTHERNAME = @EmployeeMotherName, SPOUSENAME = @EmployeeSpouseName, FIRSTNAME = @FirstName, MIDDLENAME = @MiddleName, LASTNAME = @LastName, SORTORDER = @EmployeeSortOrder, STATUS = @EmployeeStatus, VERSION = @EmployeeVersion, CREATEDBYID = @EmployeeCreatedById, CREATEDON = @EmployeeCreatedOn, MODIFIEDBYID = @EmployeeModifiedById, MODIFIEDON = @EmployeeModifiedOn, SOURCETYPE = @SourceType, TENANTID = @TenantId WHERE EMPLOYEETRANSLATIONID = @EmployeeTranslationId;"; public const string DELETE_EMPLOYEE = @" DELETE FROM MEMPLOYEEWORKING WHERE EMPLOYEEID = @employeeid; DELETE FROM MEMPLOYEE WHERE EMPLOYEEID = @employeeid;"; public const string DELETE_EMPLOYEE_ADDON = @" DELETE FROM MEMPLOYEEADDON WHERE EMPLOYEEID = @EmployeeId;"; public const string UPSERT_EMPLOYEE_ADDON = @" MERGE MEMPLOYEEADDON AS target USING (SELECT @EmployeeId AS EMPLOYEEID) AS source ON target.EMPLOYEEID = source.EMPLOYEEID WHEN MATCHED THEN UPDATE SET PERMOBNUM = @EmployeeAddonPerMobNum, ENTRYLONG = @EmployeeAddonEntryLong, ENTRYDATETIME = @EmployeeAddonEntryDateTime, ENTRYDOUBLE = @EmployeeAddonEntryDouble, ENTRYINT16 = @EmployeeAddonEntryInt16, ENTRYBYTE = @EmployeeAddonEntryByte, FORMULAINT = @EmployeeAddonFormulaInt, LOVSTRING = @EmployeeAddonLovString, STANDARDLIST = @EmployeeAddonStandardList, FILESELECTION = @EmployeeAddonFileSelection, BONUSAPPLICABLE = @EmployeeAddonBonusApplicable WHEN NOT MATCHED THEN INSERT (EMPLOYEEID, PERMOBNUM, ENTRYLONG, ENTRYDATETIME, ENTRYDOUBLE, ENTRYINT16, ENTRYBYTE, FORMULAINT, LOVSTRING, STANDARDLIST, FILESELECTION, BONUSAPPLICABLE) VALUES (@EmployeeId, @EmployeeAddonPerMobNum, @EmployeeAddonEntryLong, @EmployeeAddonEntryDateTime, @EmployeeAddonEntryDouble, @EmployeeAddonEntryInt16, @EmployeeAddonEntryByte, @EmployeeAddonFormulaInt, @EmployeeAddonLovString, @EmployeeAddonStandardList, @EmployeeAddonFileSelection, @EmployeeAddonBonusApplicable);"; public const string GET_FUTURE_EMPLOYEE_VERSION = @" SELECT TOP(1) VERSIONFROM as EmployeeVersionFrom FROM MEMPLOYEEVERSION WHERE VERSIONFROM > @versionfrom AND EMPLOYEEID = @employeeid ORDER BY VERSIONFROM DESC"; public const string SAVE_OR_UPDATE_EMPLOYEE_ESI = @" MERGE INTO MEMPLOYEE AS Target USING (SELECT @EmployeeId AS EMPLOYEEID) AS Source ON (Target.EMPLOYEEID = Source.EMPLOYEEID) WHEN MATCHED THEN UPDATE SET ESINUMBER = @EmployeeESINumber, ESIDISPENSARY = @EmployeeESIDispensary, ESISTATUS = @EmployeeESIStatus, ESIJOINDATE = @EmployeeESIJoinDate, ESIENDDATE = @EmployeeESIEndDate, EMPLOYEEESIPERCENTAGE = @EmployeeEmployeeESIPercentage, EMPLOYERESIPERCENTAGE = @EmployeeEmployerESIPercentage, ISESIHOURLYCONTRIBUTION = @EmployeeIsESIHourlyContribution, REASONFORESILEAVING = @EmployeeReasonForESILeaving, MODIFIEDBYID = @EmployeeModifiedById, MODIFIEDON = @EmployeeModifiedOn WHEN NOT MATCHED THEN INSERT ( EMPLOYEEID, ESINUMBER, ESIDISPENSARY, ESISTATUS, ESIJOINDATE, ESIENDDATE, EMPLOYEEESIPERCENTAGE, EMPLOYERESIPERCENTAGE, ISESIHOURLYCONTRIBUTION, REASONFORESILEAVING, CREATEDBYID, CREATEDON ) VALUES ( @EmployeeId, @EmployeeESINumber, @EmployeeESIDispensary, @EmployeeESIStatus, @EmployeeESIJoinDate, @EmployeeESIEndDate, @EmployeeEmployeeESIPercentage, @EmployeeEmployerESIPercentage, @EmployeeIsESIHourlyContribution, @EmployeeReasonForESILeaving, @EmployeeCreatedById, @EmployeeCreatedOn );"; public const string CLOSE_EMPLOYEE_ESI = @" UPDATE MEMPLOYEE SET ESIENDDATE = @EmployeeESIEndDate, MODIFIEDBYID = @EmployeeModifiedById, MODIFIEDON = @EmployeeModifiedOn WHERE EMPLOYEEID = @EmployeeId AND ESIENDDATE = '9999-12-31';"; public const string SAVE_OR_UPDATE_EMPLOYEE_PF = @" MERGE INTO MEMPLOYEE AS Target USING (SELECT @EmployeeId AS EMPLOYEEID) AS Source ON (Target.EMPLOYEEID = Source.EMPLOYEEID) WHEN MATCHED THEN UPDATE SET PFNUMBER = @EmployeePFNumber, PFSTATUS = @EmployeePFStatus, PFJOINDATE = @EmployeePFJoinDate, PFENDDATE = @EmployeePFEndDate, EMPLOYEEPFPERCENTAGE = @EmployeeEmployeePFPercentage, EMPLOYERPFPERCENTAGE = @EmployeeEmployerPFPercentage, REASONFORPFLEAVING = @EmployeeReasonForPFLeaving, MODIFIEDBYID = @EmployeeModifiedById, MODIFIEDON = @EmployeeModifiedOn WHEN NOT MATCHED THEN INSERT ( EMPLOYEEID, PFNUMBER, PFSTATUS, PFJOINDATE, PFENDDATE, EMPLOYEEPFPERCENTAGE, EMPLOYERPFPERCENTAGE, REASONFORPFLEAVING, CREATEDBYID, CREATEDON ) VALUES ( @EmployeeId, @EmployeePFNumber, @EmployeePFStatus, @EmployeePFJoinDate, @EmployeePFEndDate, @EmployeeEmployeePFPercentage, @EmployeeEmployerPFPercentage, @EmployeeReasonForPFLeaving, @EmployeeCreatedById, @EmployeeCreatedOn );"; public const string CLOSE_EMPLOYEE_PF = @" UPDATE MEMPLOYEEPFO SET PFENDDATE = @EmployeePFEndDate, REASONFORPFLEAVING = @EmployeeReasonForPFLeaving, MODIFIEDBYID = @EmployeeModifiedById, MODIFIEDONID = @EmployeeModifiedOn WHERE EMPLOYEEID = @EmployeeId AND PFENDDATE = '9999-12-31';"; public const string UPDATE_ATTENDANCE_AFTER_RESIGNATION = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = 13, TEXTDAYTYPE = 'RESG', LEAVEID = MOULEVELSETTING.ABSENTLEAVEID, ISMANUALDAYTYPE = 0, ISMANUALINTIME = 0, ISMANUALOUTTIME = 0 FROM MOULEVELSETTING WHERE TDAILYATTENDANCE.OUID = MOULEVELSETTING.OUID AND TDAILYATTENDANCE.EMPLOYEEID = @EmployeeId AND ATTENDANCEDATE > @EmployeeDateOfResignation; "; public const string UPDATE_USER_STATUS_AFTER_RESIGNATION = @" UPDATE MUSER SET STATUS = 0, VALIDTO = @EmployeeDateOfResignation WHERE USERID = @EmployeeId; "; // Deactivates all MJOBDEFINE rows whose RUNASUSEID matches the resigned/deactivated user. // STATUS=1 means inactive in MJOBDEFINE (inverted from MUSER convention). // Runs against the tenant DB via IQueryExecutor — no TENANTID filter needed. public const string DEACTIVATE_SCHEDULER_JOBS_FOR_USER = @" UPDATE MJOBDEFINE SET STATUS = 1, MODIFIEDON = GETUTCDATE(), MODIFIEDBYID = @ModifiedById WHERE RUNASUSEID = @EmployeeId AND STATUS = 0"; public const string GETTRANSFEREMPLOYEEADVANCE = @"SELECT o.employeeid as OldEmployeeId,o.employeecode as OldEmployeeCode,o.employeename as OldEmployeeName,o.legacyemployeecode as LegacyCode, a.advanceid as AdvanceId, a.amountremaining as AmountRemaining, n.employeeid as NewEmployeeId,n.employeecode as NewEmployeeCode,n.employeename as Newemployeename from memployee o inner join tadvance a on(o.EMPLOYEEID = a.employeeid and a.amountremaining>0) inner join memployee n on (o.legacyemployeecode = n.legacyemployeecode and o.legacyemployeecode<>'' and o.legacyemployeecode<>'0' and o.employeeid=n.EMPLOYEECOMMONID) WHERE 1=1 and o.EMPLOYEEID <> n.employeeid and n.employeestatus in (0) -- new code to be active"; public const string UPDATE_MEMPLOYEE_BASED_ON_EMPLOYEE_ID = @" UPDATE MEMPLOYEE SET ESINUMBER = @EmployeeESINumber, ESIJOINDATE = @EmployeeESIJoinDate, EMPLOYEEESIPERCENTAGE = @EmployeeEmployeeESIPercentage, ISTDSAPPLICABLE = @EmployeeIsTDSApplicable, ESISTATUS = @EmployeeESIStatus, ISESIAPPLICABLE = @EmployeeIsESIApplicable, ESIDISPENSARY = @EmployeeESIDispensary, ISESIHOURLYCONTRIBUTION = @EmployeeIsESIHourlyContribution WHERE EMPLOYEEID = @EmployeeId;"; #region EmployeeESILeaving public const string UPDATE_MEMPLOYEE_BASED_ON_EMPLOYEEESILEAVING_EMPLOYEE_ID = @" UPDATE MEMPLOYEE SET ESINUMBER = @EmployeeESINumber, ESIENDDATE = @EmployeeESIEndDate, REASONFORESILEAVING = @EmployeeReasonForESILeaving WHERE EMPLOYEEID = @EmployeeId;"; #endregion #region EmployeePFLeaving public const string UPDATE_MEMPLOYEE_BASED_ON_EMPLOYEEPFLEAVING_EMPLOYEE_ID = @" UPDATE MEMPLOYEE SET PFNUMBER = @EmployeePFNumber, PFENDDATE = @EmployeePFEndDate, REASONFORPFLEAVING = @EmployeeReasonForPFLeaving, ISPFAPPLICABLE = @EmployeeIsPFApplicable, PFSTATUS = @EmployeePFStatus WHERE EMPLOYEEID = @EmployeeId;"; #endregion #region EmployeePFJoining public const string UPDATE_MEMPLOYEE_BASED_ON_EMPLOYEEPFJOINING_EMPLOYEE_ID = @" UPDATE MEMPLOYEE SET PFNUMBER = @EmployeePFNumber, PFJOINDATE = @EmployeePFJoinDate, EMPLOYEEPFPERCENTAGE = @EmployeeEmployeePFPercentage, ISFAMILYPENSION = @EmployeeIsFamilyPension, FAMILYPENSIONPERCENTAGE= @EmployeeFamilyPensionPercentage, FAMILYPENSIONNUMBER = @EmployeeFamilyPensionNumber, NOMINEE = @EmployeeNominee, NOMINEERELATION = @EmployeeNomineeRelation WHERE EMPLOYEEID = @EmployeeId;"; #endregion #region EmployeeShiftPattern public const string UPDATE_MEMPLOYEE_FOR_SHIFTPATTERN = @" UPDATE MEMPLOYEE SET SHIFTPATTERNDETAILID = @ShiftPatternDetailId, PATTERNDATE = @EmployeePatternDate WHERE EMPLOYEEID = @EmployeeId;"; public const string UPDATE_MEMPLOYEE_FOR_SHIFTPATTERN_NEW = @" UPDATE MEMPLOYEE SET SHIFTPATTERNDETAILID = @ShiftPatternDetailId, PATTERNDATE = @EmployeePatternDate WHERE EMPLOYEEID = @EmployeeId;"; public const string UPDATE_MEMPLOYEE_FOR_SHIFTPATTERN_DAYS = @" UPDATE MSHIFTPATTERNDAYS SET NUMBEROFDAYS = @NumberOfDays FROM MSHIFTPATTERNDETAIL WHERE MSHIFTPATTERNDETAIL.SHIFTPATTERNDAYSID = MSHIFTPATTERNDAYS.SHIFTPATTERNDAYSID AND MSHIFTPATTERNDETAIL.SHIFTPATTERNDETAILID = @ShiftPatternDetailId;"; #endregion #region HeadCountReport public const string GET_HEAD_COUNT_REPORT = @" SELECT mgbdetail.SUBPERIODNAME AS GBPeriodName, dep.DEPARTMENTNAME AS DepartmentName, SUM(CASE WHEN emp.DATEOFJOINING < mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFRESIGNATION > mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) + SUM(CASE WHEN emp.DATEOFRESIGNATION >= mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFRESIGNATION <= mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) AS Existing, SUM(CASE WHEN emp.DATEOFJOINING >= mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFJOINING <= mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) AS Joined, SUM(CASE WHEN emp.DATEOFRESIGNATION >= mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFRESIGNATION <= mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) AS Resigned, SUM(CASE WHEN emp.DATEOFJOINING < mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFRESIGNATION > mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) + SUM(CASE WHEN emp.DATEOFJOINING >= mgbdetail.SUBPERIODFROMDATE AND emp.DATEOFJOINING <= mgbdetail.SUBPERIODTODATE THEN 1 ELSE 0 END) AS Total FROM MGBPERIODDETAIL mgbdetail JOIN MGBPERIOD mgperiod ON mgbdetail.GBPERIODID = mgperiod.GBPERIODID JOIN MEMPLOYEE emp ON 1=1 JOIN MDEPARTMENT dep ON emp.DEPARTMENTID = dep.DEPARTMENTID WHERE 1=1;"; #endregion #region ESI Detail public const string GET_ESI_DETAIL = @" SELECT EMP.EMPLOYEECODE AS EmployeeCode, EMP.EMPLOYEENAME AS EmployeeName, EMP.ESINUMBER AS EmployeeESINumber, EMP.ESIDISPENSARY AS EmployeeESIDispensary, SUM(ESID.WAGES) AS Wages, SUM(PAYPRSS.PAIDDAYS) AS PresentDays, SUM(ESID.EMPLOYEEAMOUNT) AS EmployeeAmount, SUM(ESID.EMPLOYERAMOUNT) AS EmployerAmount, SUM(ESID.EMPLOYEEAMOUNT + ESID.EMPLOYERAMOUNT) AS TotalESIAmount, MAX(ESID.EMPLOYEEACTUALPERCENTAGE) AS EmployeePercent, MAX(ESID.EMPLOYERACTUALPERCENTAGE) AS EmployerPercent, EMP.ISPFAPPLICABLE AS EmployeeIsPFApplicable, EMP.ISESIAPPLICABLE AS EmployeeIsESIApplicable, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, OU.DISPLAYNAME AS OUDisplayName, OU.ORGANIZATIONUNITSHORTNAME AS OUShortName, MAX(p.PAYPERIODNAME) AS PayperiodCode, MAX(p.PAYPERIODNAME) AS PayperiodName FROM TPAYPROCESS PAYPRSS INNER JOIN TESIDETAIL ESID ON PAYPRSS.EMPLOYEEID = ESID.EMPLOYEEID INNER JOIN TESI ESI ON ESI.ESIID = ESID.ESIID AND ESI.PAYPERIODID = PAYPRSS.PAYPERIODID AND ESI.OUID = OU.OUID INNER JOIN MEMPLOYEE EMP ON EMP.EMPLOYEEID = PAYPRSS.EMPLOYEEID INNER JOIN MORGANIZATIONUNIT OU ON ESI.OUID = OU.OUID INNER JOIN MPAYPERIOD p ON PAYPRSS.PAYPERIODID = p.PAYPERIODID OUTER APPLY ( SELECT STUFF(( SELECT ',' + PAYPERIODNAME FROM MPAYPERIOD WHERE PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ',')) FOR XML PATH('') ), 1, 1, '') AS PayperiodName ) pay WHERE PAYPRSS.IsInternal = 1 AND (@PayPeriodIds IS NULL OR PAYPRSS.PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ',')));"; #endregion #region ESI Summary public const string GET_ESI_SUMMARY = @" SELECT EMP.EMPLOYEECODE AS EmployeeCode, EMP.EMPLOYEENAME AS EmployeeName, EMP.ESINUMBER AS EmployeeESINumber, SUM(ESID.WAGES) AS Wages, SUM(PAYPRSS.PRESENTDAYS) AS PresentDays, EMP.ISPFAPPLICABLE AS IsPFApplicable, EMP.ISESIAPPLICABLE AS IsESIApplicable, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, OU.DISPLAYNAME AS OUDisplayName, OU.ORGANIZATIONUNITSHORTNAME AS OUShortName FROM TPAYPROCESS PAYPRSS INNER JOIN TESIDETAIL ESID ON PAYPRSS.EMPLOYEEID = ESID.EMPLOYEEID INNER JOIN TESI ESI ON ESI.ESIID = ESID.ESIID AND ESI.PAYPERIODID = PAYPRSS.PAYPERIODID AND ESI.OUID = OU.OUID INNER JOIN MEMPLOYEE EMP ON EMP.EMPLOYEEID = PAYPRSS.EMPLOYEEID INNER JOIN MORGANIZATIONUNIT OU ON ESI.OUID = OU.OUID WHERE PAYPRSS.IsInternal = 1 AND (@PayPeriodIds IS NULL OR PAYPRSS.PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ','))) GROUP BY EMP.EMPLOYEECODE, EMP.EMPLOYEENAME, EMP.EmployeeESINumber, EMP.ISPFAPPLICABLE, EMP.ISESIAPPLICABLE, OU.ORGANIZATIONUNITCODE, OU.ORGANIZATIONUNITNAME, OU.DISPLAYNAME, OU.ORGANIZATIONUNITSHORTNAME;"; #endregion #region PF Detail public const string GET_PF_DETAIL = @" SELECT EMP.EMPLOYEECODE AS EmployeeCode, MAX(pay.PAYPERIODNAME) AS PayperiodCode, MAX(pay.PAYPERIODNAME) AS PayperiodName, EMP.PFUANNUMBER AS PFNumber, EMP.EMPLOYEENAME AS EmployeeName, SUM(PFD.GROSSWAGES) AS GrossWages, CASE WHEN SUM(PFD.DLIWAGES) <= 15000 THEN SUM(PFD.DLIWAGES) ELSE 15000 END AS DLIWages, SUM(PFD.REFUND) AS Refund, SUM(PFD.WAGES) AS Wages, CASE WHEN SUM(PFD.PENSIONWAGES) <= 15000 THEN SUM(PFD.PENSIONWAGES) ELSE 15000 END AS PensionWages, SUM(PFD.EMPLOYERAMOUNT) AS EmployerAmount, SUM(PFD.EMPLOYEEAMOUNT) AS EmployeeAmount, CASE WHEN SUM(PFD.FPFEMPLOYERAMOUNT) <= 1250 THEN SUM(PFD.FPFEMPLOYERAMOUNT) ELSE 1250 END AS FPFEmployerAmount, SUM(PFD.LOPDAYS) AS LopDays, EMP.DATEOFRESIGNATION AS DateOfResignation, SUM(PFD.EMPLOYERAMOUNT) - CASE WHEN SUM(PFD.FPFEMPLOYERAMOUNT) <= 1250 THEN SUM(PFD.FPFEMPLOYERAMOUNT) ELSE 1250 END AS ERShare, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.NOMINEE ELSE '' END AS Nominee, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.NOMINEERELATION ELSE '' END AS NomineeRelation, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.DATEOFBIRTH ELSE '1900-01-01' END AS DateofBirth, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN CASE WHEN EMP.SEX = 0 THEN 'M' ELSE 'F' END ELSE ' ' END AS Sex, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.PFJOINDATE ELSE '1900-01-01' END AS PFJoinDate, CASE WHEN EMP.PFENDDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.PFENDDATE ELSE '1900-01-01' END AS PFEndDate, CASE WHEN EMP.PFENDDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.REASONFORESILEAVING ELSE '' END AS ReasonForLeaving, EMP.ISPFAPPLICABLE AS IsPFApplicable, EMP.ISESIAPPLICABLE AS IsESIApplicable, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, OU.DISPLAYNAME AS OUDisplayName, OU.ORGANIZATIONUNITSHORTNAME AS OUShortName FROM MEMPLOYEE EMP INNER JOIN TPFDETAIL PFD ON EMP.EMPLOYEEID = PFD.EMPLOYEEID INNER JOIN TPF PF ON PF.PFID = PFD.PFID INNER JOIN MPAYPERIOD payperiod ON PF.PAYPERIODID = payperiod.PAYPERIODID INNER JOIN MORGANIZATIONUNIT OU ON PF.OUID = OU.OUID OUTER APPLY ( SELECT STUFF(( SELECT ',' + PAYPERIODNAME FROM MPAYPERIOD WHERE PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ',')) FOR XML PATH('') ), 1, 1, '') AS PayperiodName ) pay WHERE (@PayPeriodIds IS NULL OR PF.PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ','))) GROUP BY EMP.EMPLOYEECODE, EMP.PFUANNUMBER, EMP.EMPLOYEENAME, EMP.DATEOFRESIGNATION, EMP.NOMINEE, EMP.NOMINEERELATION, EMP.DATEOFBIRTH, EMP.SEX, EMP.PFJOINDATE, EMP.PFENDDATE, EMP.REASONFORESILEAVING, EMP.ISPFAPPLICABLE, EMP.ISESIAPPLICABLE, OU.ORGANIZATIONUNITCODE, OU.ORGANIZATIONUNITNAME, OU.DISPLAYNAME, OU.ORGANIZATIONUNITSHORTNAME, payperiod.FROMDATE, payperiod.TODATE;"; #endregion #region PF Summary public const string GET_PF_SUMMARY = @" SELECT EMP.EMPLOYEECODE AS EmployeeCode, EMP.PFUANNUMBER AS PFNumber, EMP.EMPLOYEENAME AS EmployeeName, SUM(PFD.GROSSWAGES) AS GrossWages, SUM(PFD.DLIWAGES) AS DLIWages, SUM(PFD.REFUND) AS Refund, SUM(PFD.WAGES) AS Wages, SUM(PFD.PENSIONWAGES) AS PensionWages, SUM(PFD.EMPLOYERAMOUNT) AS EmployerAmount, SUM(PFD.EMPLOYEEAMOUNT) AS EmployeeAmount, SUM(PFD.FPFEMPLOYERAMOUNT) AS FPFEmployerAmount, SUM(PFD.LOPDAYS) AS LopDays, EMP.DATEOFRESIGNATION AS DateOfResignation, SUM(PFD.EMPLOYERAMOUNT) - SUM(PFD.FPFEMPLOYERAMOUNT) AS ERShare, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.NOMINEE ELSE '' END AS Nominee, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.NOMINEERELATION ELSE '' END AS NomineeRelation, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.DATEOFBIRTH ELSE '1900-01-01' END AS DateofBirth, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN CASE WHEN EMP.SEX = 0 THEN 'M' ELSE 'F' END ELSE ' ' END AS Sex, CASE WHEN EMP.PFJOINDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.PFJOINDATE ELSE '1900-01-01' END AS PFJoinDate, CASE WHEN EMP.PFENDDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.PFENDDATE ELSE '1900-01-01' END AS PFEndDate, CASE WHEN EMP.PFENDDATE BETWEEN payperiod.FROMDATE AND payperiod.TODATE THEN EMP.REASONFORESILEAVING ELSE '' END AS ReasonForLeaving, EMP.ISPFAPPLICABLE AS IsPFApplicable, EMP.ISESIAPPLICABLE AS IsESIApplicable, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, OU.DISPLAYNAME AS OUDisplayName, OU.ORGANIZATIONUNITSHORTNAME AS OUShortName FROM MEMPLOYEE EMP INNER JOIN TPFDETAIL PFD ON EMP.EMPLOYEEID = PFD.EMPLOYEEID INNER JOIN TPF PF ON PF.PFID = PFD.PFID INNER JOIN MPAYPERIOD payperiod ON PF.PAYPERIODID = payperiod.PAYPERIODID INNER JOIN MORGANIZATIONUNIT OU ON PF.OUID = OU.OUID WHERE (@PayPeriodIds IS NULL OR PF.PAYPERIODID IN (SELECT value FROM STRING_SPLIT(@PayPeriodIds, ','))) GROUP BY EMP.EMPLOYEECODE, EMP.PFUANNUMBER, EMP.EMPLOYEENAME, EMP.DATEOFRESIGNATION, EMP.NOMINEE, EMP.NOMINEERELATION, EMP.DATEOFBIRTH, EMP.SEX, EMP.PFJOINDATE, EMP.PFENDDATE, EMP.REASONFORESILEAVING, EMP.ISPFAPPLICABLE, EMP.ISESIAPPLICABLE, OU.ORGANIZATIONUNITCODE, OU.ORGANIZATIONUNITNAME, OU.DISPLAYNAME, OU.ORGANIZATIONUNITSHORTNAME, payperiod.FROMDATE, payperiod.TODATE;"; #endregion #region EmployeeReports public const string GET_JOB_POSITION_REPORT = @" SELECT a.JOBPOSITIONID AS JobPositionId, a.JOBPOSITIONCODE AS JobPositionCode, a.JOBPOSITIONNAME AS JobPositionName FROM MJOBPOSITION a WHERE 1=1;"; public const string GET_EMPLOYEE_REPORT = @" SELECT emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECOMMONID AS EmployeeCommonId, emp.EMPLOYEECOMMONID AS EmployeeCommonCode, -- assuming common code = ID emp.EMPLOYEENAME AS EmployeeCommonName, -- fallback emp.CODEDEFINEID AS EmployeeCodeDefineId, emp.FATHERNAME AS EmployeeFatherName, emp.MOTHERNAME AS EmployeeMotherName, emp.SPOUSENAME AS EmployeeSpouseName, dept.DEPARTMENTID AS DepartmentId, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, vil.VILLAGEID AS VillageId, vil.VILLAGECODE AS VillageCode, vil.VILLAGENAME AS VillageName, emp.COSTCENTERTYPE AS EmployeeCostCenterType, cc.COSTCENTERID AS CostCenterId, cc.COSTCENTERCODE AS CostCenterCode, cc.COSTCENTERNAME AS CostCenterName, ou.WORKOUID AS WorkOUId, ou.ORGANIZATIONUNITCODE AS WorkOUCode, ou.ORGANIZATIONUNITNAME AS WorkOUName, emp.ISCROSSOU AS EmployeeIsCrossOU, oug.OUGROUPID AS OUGroupId, oug.OUGROUPCODE AS OUGroupCode, oug.OUGROUPNAME AS OUGroupName, pg.PAYGROUPID AS PayGroupId, pg.PAYGROUPCODE AS PayGroupCode, pg.PAYGROUPNAME AS PayGroupName, wt.WORKTYPEID AS WorkTypeId, wt.WORKTYPECODE AS WorkTypeCode, wt.WORKTYPENAME AS WorkTypeName, et.EMPLOYEETYPEID AS EmployeeTypeId, et.EMPLOYEETYPECODE AS EmployeeTypeCode, et.EMPLOYEETYPENAME AS EmployeeTypeName, st.STAFFTYPEID AS StaffTypeId, st.STAFFTYPECODE AS StaffTypeCode, st.STAFFTYPENAME AS StaffTypeName, des.DESIGNATIONID AS DesignationId, des.DESIGNATIONCODE AS DesignationCode, des.DESIGNATIONNAME AS DesignationName, nat.NATIONALITYID AS NationalityId, nat.NATIONALITYCODE AS NationalityCode, nat.NATIONALITYNAME AS NationalityName, rel.RELIGIONID AS ReligionId, rel.RELIGIONCODE AS ReligionCode, rel.RELIGIONNAME AS ReligionName, loc.LOCATIONID AS LocationId, loc.LOCATIONCODE AS LocationCode, loc.LOCATIONNAME AS LocationName, cas.CASTEID AS CasteId, cas.CASTECODE AS CasteCode, cas.CASTENAME AS CasteName, emp.HEIGHTINCMS AS EmployeeHeightInCMS, emp.WEIGHTINKGS AS EmployeeWeightInKGS, emp.CHESTSIZE AS EmployeeChestSize, emp.ISHANDICAPPED AS EmployeeIsHandiCapped, emp.IDENTIFICATIONMARK AS EmployeeIdentificationMark, emp.PHYSICALCONDITION AS EmployeePhysicalCondition, emp.EMPLOYEESTATUS AS EmployeeEmployerStatus, emp.DATEOFBIRTH AS EmployeeDateOfBirth, emp.ACTUALDATEOFBIRTH AS EmployeeActualDateOfBirth, emp.DATEOFJOINING AS EmployeeDateOfJoining, emp.ISCONFIRMED AS EmployeeIsConfirmed, emp.DATEOFCONFIRMATION AS EmployeeDateOfConfirmation, emp.DATEOFRESIGNATION AS EmployeeDateOfResignation, emp.CALCULATEDDATEOFRETIREMENT AS EmployeeCalculatedDateOfRetirment, emp.ACTUALDATEOFRETIREMENT AS EmployeeActualDateOfRetirment, emp.MARITALSTATUS AS EmployeeMaritalStatus, CASE WHEN emp.MARITALSTATUS = 0 THEN 'UnMarried' WHEN emp.MARITALSTATUS = 1 THEN 'Married' WHEN emp.MARITALSTATUS = 2 THEN 'Widow' ELSE 'Divorcee' END AS EmployeeMaritalStatusName, emp.SEX AS EmployeeSex, CASE WHEN emp.SEX = 0 THEN 'M' ELSE 'F' END AS EmployeeSexName, emp.BLOODGROUP AS EmployeeBloodGroup, emp.ISPFAPPLICABLE AS EmployeeIsPFApplicable, emp.PFSTATUS AS EmployeePFStatus, emp.PFNUMBER AS EmployeePFNumber, emp.STATUTORYPFPERCENTAGE AS EmployeeStatutorypfPercentage, emp.EMPLOYEEPFPERCENTAGE AS EmployeeEmployeePFPercentage, emp.ISFAMILYPENSION AS EmployeeIsFamilyPension, emp.FAMILYPENSIONNUMBER AS EmployeeFamilyPensionNumber, emp.FAMILYPENSIONPERCENTAGE AS EmployeeFamilyPensionPercentage, emp.PFJOINDATE AS EmployeePFJoinDate, emp.PFENDDATE AS EmployeePFEndDate, emp.ISESIAPPLICABLE AS EmployeeIsESIApplicable, emp.ESISTATUS AS EmployeeESIStatus, emp.ESINUMBER AS EmployeeESINumber, emp.ESIJOINDATE AS EmployeeESIJoinDate, emp.ESIENDDATE AS EmployeeESIEndDate, emp.ESIDISPENSARY AS EmployeeESIDispensary, emp.EMPLOYEEESIPERCENTAGE AS EmployeeEmployeeESIPercentage, emp.ISESIHOURLYCONTRIBUTION AS EmployeeIsESIHourlyContribution, emp.BASICON AS EmployeeBasicOn, emp.PAYMODE AS EmployeePayMode, emp.OTPAYMODE AS EmployeeOTPayMode, emp.ISTDSAPPLICABLE AS EmployeeIsTDSApplicable, sg.SALARYGRADEID AS SalaryGradeId, sg.SALARYGRADECODE AS SalaryGradeCode, sg.SALARYGRADENAME AS SalaryGradeName, dpg.DAPOINTGROUPID AS DAPointGroupId, dpg.DAPOINTGROUPCODE AS DAPointGroupCode, dpg.DAPOINTGROUPNAME AS DAPointGroupName, bank.BANKID AS BankId, bank.BANKCODE AS BankCode, bank.BANKNAME AS BankName, bb.EMPLOYEEBANKBRANCHID AS BankBranchId, bb.BANKBRANCHNAME AS BankBranchCode, bb.BANKBRANCHNAME AS BankBranchName, emp.ACCOUNTNUMBER AS EmployeeAccountNumber, sh.SHIFTID AS ShiftId, sh.SHIFTCODE AS ShiftCode, sh.SHIFTDESCRIPTION AS ShiftDescription, emp.WEEKOFFDAY AS EmployeeWeekOfDay, acc.ACCOUNTID AS AccountId, acc.ACCOUNTCODE AS AccountCode, acc.ACCOUNTNAME AS AccountName, emp.NOMINEE AS EmployeeNominee, emp.NOMINEERELATION AS EmployeeNomineeRelation, emp.ATTENDANCETYPE AS EmployeeAttendanceType, emp.ONSITEPLACE AS EmployeeOnSitePlace, emp.ISDAILYREPORT AS EmployeeIsDailyReport, emp.PUNCHTYPE AS EmployeePunchType, emp.PRIMARYEMAILID AS EmployeePrimaryEMailId, emp.PRIMARYMOBILE AS EmployeePrimaryMobile, rep.REPORTINGTOEMPLOYEEID AS ReportInttoEmployeeId, rep.EMPLOYEECODE AS ReportInttoEmployeeCode, rep.EMPLOYEENAME AS ReportInttoEmployeeName, emp.EXPERIENCE AS EmployeeExperience, emp.ISTEMPORARYCARD AS EmployeeIsTemporaryCard, emp.TEMPORARYCARDNUMBER AS EmployeeTemporaryCardNumber, emp.CARDVALIDTILL AS EmployeeCardValidTill, emp.ISNUMBERREQUIRED AS EmployeeIsNumberRequired, emp.BIOMATCHNUMBER AS EmployeeBioMatchNumber, adv.ACCOUNTID AS AdvanceAccountId, adv.ACCOUNTCODE AS AdvanceAccountCode, adv.ACCOUNTNAME AS AdvanceAccountName, sal.ACCOUNTID AS SalaryAccountId, sal.ACCOUNTCODE AS SalaryAccountCode, sal.ACCOUNTNAME AS SalaryAccountName, emp.DEFAULTIMAGEID AS EmployeeDefaultImage, emp.ISCONTRACT AS EmployeeIsContract, emp.ISNUMBER AS EmployeeIsNumber, emp.ISPURCHASE AS EmployeeIsPurchase, emp.ISMARKETING AS EmployeeIsMarketing, emp.ISSALES AS EmployeeIsSales, emp.ISPLANNING AS EmployeeIsPlanning, emp.ISSERVICE AS EmployeeIsService, emp.PANNUMBER AS EmployeePANNumber, emp.ISUSER AS EmployeeIsUser, emp.EMPLOYERPFPERCENTAGE AS EmployeeEmployerPFPercentage, seg.SEGMENTID AS SegmentId, seg.SEGMENTCODE AS SegmentCode, seg.SEGMENTNAME AS SegmentName, unit.UNITID AS UnitId, unit.UNITCODE AS UnitCode, unit.UNITNAME AS UnitName, div.DIVISIONID AS DivisionId, div.DIVISIONCODE AS DivisionCode, div.DIVISIONNAME AS DivisionName, emp.REASONFORESILEAVING AS EmployeeReasonForESILeaving, emp.REASONFORPFLEAVING AS EmployeeReasonForPFLeaving, emp.SHIFTPATTERNDETAILID AS ShiftPatternDetailId, emp.PATTERNDATE AS EmployeePatternDate, emp.EMPLOYERESIPERCENTAGE AS EmployeeEmployerESIPercentage, emp.JOBPOSITIONID AS JobPositionId FROM MEMPLOYEE emp LEFT JOIN MDEPARTMENT dept ON emp.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN MVILLAGE vil ON emp.VILLAGEID = vil.VILLAGEID LEFT JOIN MCOSTCENTER cc ON emp.COSTCENTERID = cc.COSTCENTERID LEFT JOIN MORGANIZATIONUNIT ou ON emp.WORKOUID = ou.WORKOUID LEFT JOIN MOUGROUP oug ON emp.OUGROUPID = oug.OUGROUPID LEFT JOIN MPAYGROUP pg ON emp.PAYGROUPID = pg.PAYGROUPID LEFT JOIN MWORKTYPE wt ON emp.WORKTYPEID = wt.WORKTYPEID LEFT JOIN MEMPLOYEETYPE et ON emp.EMPLOYEETYPEID = et.EMPLOYEETYPEID LEFT JOIN MSTAFFTYPE st ON emp.STAFFTYPEID = st.STAFFTYPEID LEFT JOIN MDESIGNATION des ON emp.DESIGNATIONID = des.DESIGNATIONID LEFT JOIN MNATIONALITY nat ON emp.NATIONALITYID = nat.NATIONALITYID LEFT JOIN MRELIGION rel ON emp.RELIGIONID = rel.RELIGIONID LEFT JOIN MLOCATION loc ON emp.LOCATIONID = loc.LOCATIONID LEFT JOIN MCASTE cas ON emp.CASTEID = cas.CASTEID LEFT JOIN MSALARYGRADE sg ON emp.SALARYGRADEID = sg.SALARYGRADEID LEFT JOIN MDAPOINTGROUP dpg ON emp.DAPOINTGROUPID = dpg.DAPOINTGROUPID LEFT JOIN MBANK bank ON bb.BANKID = bank.BANKID LEFT JOIN MBANKBRANCH bb ON emp.EMPLOYEEBANKBRANCHID = bb.EMPLOYEEBANKBRANCHID LEFT JOIN MSHIFT sh ON emp.SHIFTID = sh.SHIFTID LEFT JOIN MACCOUNT acc ON emp.ACCOUNTID = acc.ACCOUNTID LEFT JOIN MEMPLOYEE rep ON emp.REPORTINGTOEMPLOYEEID = rep.EMPLOYEEID LEFT JOIN MACCOUNT adv ON emp.ADVANCEACCOUNTID = adv.ACCOUNTID LEFT JOIN MACCOUNT sal ON emp.SALARYACCOUNTID = sal.ACCOUNTID LEFT JOIN MSEGMENT seg ON emp.SEGMENTID = seg.SEGMENTID LEFT JOIN MUNIT unit ON emp.UNITID = unit.UNITID LEFT JOIN MDIVISION div ON ou.DIVISIONID = div.DIVISIONID WHERE 1=1 :NormalReplacement;"; #endregion #region EmployeePicklist public const string GET_EMPLOYEEPICKLIST_FOR_SELECT2 = @" SELECT emp.EMPLOYEEID AS Id, emp.EMPLOYEECODE AS Code, emp.EMPLOYEENAME AS Name, emp.PRIMARYEMAILID AS PrimaryEMailId, ISNULL(emp.ADHARNUMBER, '') AS AadhaarNumber, CASE emp.STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted' WHEN 3 THEN 'Amended' WHEN 4 THEN 'InActive' WHEN 5 THEN 'Archived' ELSE '' END AS EmployeeNewStatus, emp.THUMBNAIL AS ThumbNail, emp.REPORTINGTOEMPLOYEEID AS ReportingToEmployeeId FROM MEMPLOYEE emp INNER JOIN MUSERACCESSRIGHTS uar ON emp.WORKOUID = uar.OUID WHERE 1 = 1 AND (@UserId IS NULL OR uar.USERID = @UserId);"; #endregion public const string GET_EMPLOYEEPICKLIST_FOR_SELECTLIST = @" SELECT emp.EMPLOYEEID AS Id, emp.EMPLOYEECODE AS Code, emp.EMPLOYEENAME AS Name, emp.PRIMARYEMAILID AS PrimaryEMailId, emp.DEPARTMENTID AS DepartmentId, ISNULL(emp.ADHARNUMBER, '') AS AadhaarNumber, dept.DEPARTMENTNAME AS DepartmentName, emp.EMPLOYEESTATUS AS EmployeeStatus, CASE emp.STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted' WHEN 3 THEN 'Amended' WHEN 4 THEN 'InActive' WHEN 5 THEN 'Archived' ELSE '' END AS EmployeeNewStatus, emp.THUMBNAIL AS ThumbNail, emp.REPORTINGTOEMPLOYEEID AS ReportingToEmployeeId FROM MEMPLOYEE emp INNER JOIN MDEPARTMENT dept ON emp.DEPARTMENTID = dept.DEPARTMENTID INNER JOIN MUSERACCESSRIGHTS uar ON emp.WORKOUID = uar.OUID WHERE emp.STATUS NOT IN (4, 5) AND (@UserId IS NULL OR uar.USERID = @UserId);"; public const string GET_EMPLOYEENEWMULTIPICKLIST_FOR_SELECT2 = @" SELECT emp.EMPLOYEEID AS Id, emp.EMPLOYEECODE AS Code, emp.EMPLOYEENAME + ' ( ' + emp.EMPLOYEECODE + ' ) ' AS Name, emp.PRIMARYEMAILID AS PrimaryEMailId, ISNULL(emp.ADHARNUMBER, '') AS AadhaarNumber FROM MEMPLOYEE emp INNER JOIN MUSERACCESSRIGHTS uar ON emp.WORKOUID = uar.OUID WHERE emp.STATUS <> 5 AND (@UserId IS NULL OR uar.USERID = @UserId);"; #region EmployeeAddon public const string GET_EMPLOYEE_ADDON = @" SELECT EMPLOYEEID AS EmployeeId :addonfields FROM MEMPLOYEEADDON WHERE EMPLOYEEID IN (SELECT value FROM STRING_SPLIT(@Ids, ','));"; public const string GET_EMPLOYEE_ADDON_NEW = @" SELECT a.EMPLOYEEID AS EmployeeId :addonfields FROM MEMPLOYEEADDON a INNER JOIN #temppayprocessforOthers b ON a.EMPLOYEEID = b.EMPLOYEEID ORDER BY a.EMPLOYEEID;"; #endregion #region EmployeePayProcessReport public const string EMPLOYEEMASTER_REPORT = @" SELECT employeepayprocessreport FROM EmployeePayProcessReport employeepayprocessreport WHERE 1 = 1;"; #endregion public const string GET_ISUSER_FOR_EMPLOYEE = @" SELECT ISUSER AS EmployeeIsUser FROM MEMPLOYEE WHERE EMPLOYEEID = @EmployeeId;"; #region TDSCALCULATION public const string GET_EMPLOYEE_TDSCALCULATION = @" SELECT a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, a.DATEOFBIRTH AS DOB, a.DATEOFJOINING AS DOJ, a.PANNUMBER AS PANNo, g.GCMNAME AS Designation, b.SLNO AS SlNo, b.PHASE AS Type, b.PARTICULARS AS Particulars, CASE WHEN b.VIEWTYPE IN (1, 2) THEN ISNULL(c.NUMERICVALUE, 0) ELSE 0 END AS Amount, CASE WHEN b.VIEWTYPE = 0 THEN ISNULL(c.NUMERICVALUE, 0) ELSE 0 END AS ValueAmount, b.DISPLAYLEVEL AS DisplayLevel, b.ISZEROSUPPRESS AS IsZeroSupress, b.VIEWTYPE AS ViewType, b.DATASOURCE AS DataSource, b.MAXLIMIT AS MaxLimit, (SELECT PERIODCODE FROM MPERIOD WHERE PERIODID = @PeriodId) AS PeriodCode FROM MEMPLOYEE a INNER JOIN MGCM g ON a.DESIGNATIONID = g.GCMID INNER JOIN MPAYTAXTYPECALCULATION b ON 1 = 1 LEFT JOIN TPAYTAXCALCULATION c ON b.PAYTAXTYPECALCULATIONID = c.PAYTAXTYPECALCULATIONID AND a.EMPLOYEEID = c.EMPLOYEEID WHERE a.ISTDSAPPLICABLE = 0 AND a.WORKOUID IN (SELECT value FROM STRING_SPLIT(@OuIds, ',')) AND c.PERIODID = @PeriodId;"; public const string GET_EMPLOYEE_TDSCALCULATION_COUNT = @" SELECT COUNT(a.EMPLOYEEID) AS EmployeeCount FROM MEMPLOYEE a INNER JOIN MGCM g ON a.DESIGNATIONID = g.GCMID INNER JOIN MPAYTAXTYPECALCULATION b ON 1 = 1 LEFT JOIN TPAYTAXCALCULATION c ON b.PAYTAXTYPECALCULATIONID = c.PAYTAXTYPECALCULATIONID AND a.EMPLOYEEID = c.EMPLOYEEID WHERE a.ISTDSAPPLICABLE = 0 AND a.WORKOUID IN (SELECT value FROM STRING_SPLIT(@OuIds, ',')) AND c.PERIODID = @PeriodId;"; #endregion #region EmployeeTempTable public const string CREATE_TEMP_TABLE_MEPLOYEE = @" SELECT a.EMPLOYEEID, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME, 0 AS PunchStatus INTO #emptemp FROM TPUNCH a INNER JOIN MEMPLOYEE b ON a.EMPLOYEEID = b.EMPLOYEEID WHERE 1 <> 1;"; // Dummy WHERE to create empty temp table public const string DUMP_DATA_INTO_TABLE = @" INSERT INTO #emptemp (EMPLOYEEID, ATTENDANCEDATE, PUNCHDATE, PUNCHTIME, PunchStatus) VALUES (@EmployeeId, @AttendanceDate, @PunchDate, @InPunchTime, 0), (-1, @EmployeeCode, @AttendanceDate, @OutPunchTime, 1);"; public const string CREATE_TEMP_TABLE_MEPLOYEE_NEW = @" SELECT b.EMPLOYEECODE, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME, 0 AS PunchStatus, b.EMPLOYEENAME AS DEVICECODE INTO #emptemp FROM TPUNCH a INNER JOIN MEMPLOYEE b ON a.EMPLOYEEID = b.EMPLOYEEID WHERE 1 <> 1;"; public const string DUMP_DATA_INTO_TABLE_NEW = @" INSERT INTO #emptemp (EMPLOYEECODE, ATTENDANCEDATE, PUNCHDATE, PUNCHTIME, PunchStatus, DEVICECODE) VALUES (@EmployeeCode, @AttendanceDate, @PunchDate, @InPunchTime, 0, @DeviceCode);"; public const string UPDATE_TEMP_EMPLOYEE_CODE = @" UPDATE #emptemp SET EMPLOYEEID = memployee.EMPLOYEEID FROM MEMPLOYEE memployee WHERE #emptemp.EMPLOYEECODE = memployee.EMPLOYEECODE;"; public const string DELETE_FROM_TPUNCH_TEMP = @" DELETE TPUNCH FROM #emptemp t WHERE t.ATTENDANCEDATE = TPUNCH.ATTENDANCEDATE AND t.EMPLOYEEID = TPUNCH.EMPLOYEEID;"; #endregion #region Punch public const string COUNT_PUNCH = @" SELECT COUNT(PUNCHID) FROM TPUNCH WHERE PUNCHDATE >= @FromDate AND PUNCHDATE <= @ToDate;"; public const string DELETE_FROM_TPUNCH_FOR_PUNCH_DATE = @" DELETE FROM TPUNCH WHERE PUNCHDATE >= @FromDate AND PUNCHDATE <= @ToDate AND VERIFICATIONSTATUS <> 2;"; public const string FINAL_TPUNCH_INSERT = @" INSERT INTO TPUNCH (EMPLOYEEID, ATTENDANCEDATE, PUNCHDATE, PUNCHTIME, PUNCHSTATUS) SELECT a.EMPLOYEEID, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME, a.PUNCHSTATUS FROM #emptemp a INNER JOIN MEMPLOYEE c ON a.EMPLOYEEID = c.EMPLOYEEID LEFT JOIN TPUNCH b ON a.EMPLOYEEID = b.EMPLOYEEID AND a.ATTENDANCEDATE = b.ATTENDANCEDATE AND a.PUNCHDATE = b.PUNCHDATE AND a.PUNCHTIME = b.PUNCHTIME WHERE a.EMPLOYEEID <> -1 AND b.EMPLOYEEID IS NULL ORDER BY a.EMPLOYEEID, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME;"; public const string FINAL_TPUNCH_INSERT_NEW = @" INSERT INTO TPUNCH (EMPLOYEEID, ATTENDANCEDATE, PUNCHDATE, PUNCHTIME, PUNCHSTATUS, DEVICEID) SELECT c.EMPLOYEEID, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME, a.PUNCHSTATUS, ISNULL(vv.DEVICEID, -1) AS DEVICEID FROM #emptemp a CROSS APPLY ( SELECT TOP 1 c.* FROM MEMPLOYEE c WHERE a.EMPLOYEECODE = c.EMPLOYEECODE ) c OUTER APPLY ( SELECT TOP 1 vv.DEVICEID FROM MDEVICE vv WHERE vv.DEVICECODE = a.DEVICECODE ) vv LEFT JOIN TPUNCH b ON c.EMPLOYEEID = b.EMPLOYEEID AND a.ATTENDANCEDATE = b.ATTENDANCEDATE AND a.PUNCHDATE = b.PUNCHDATE AND a.PUNCHTIME = b.PUNCHTIME WHERE c.EMPLOYEEID <> -1 AND b.EMPLOYEEID IS NULL ORDER BY c.EMPLOYEEID, a.ATTENDANCEDATE, a.PUNCHDATE, a.PUNCHTIME;"; public const string IGNORE_PUNCH_FOR_FIVE_MINUTES_DURATION_1 = @" WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID, PUNCHDATE ORDER BY EMPLOYEEID, PUNCHTIME) AS RowNumber, PUNCHDATE, PUNCHID, EMPLOYEEID, PUNCHTIME FROM TPUNCH WHERE PUNCHDATE = @AttendanceDate ) SELECT cur.PUNCHID INTO #temp FROM cte cur LEFT JOIN cte previous ON cur.RowNumber = previous.RowNumber + 1 AND cur.EMPLOYEEID = previous.EMPLOYEEID AND cur.PUNCHDATE = previous.PUNCHDATE WHERE ISNULL(DATEDIFF(MINUTE, previous.PUNCHTIME, cur.PUNCHTIME), 10) BETWEEN 0 AND 2;"; public const string IGNORE_PUNCH_FOR_FIVE_MINUTES_DURATION_2 = @" DELETE FROM TPUNCH WHERE PUNCHID IN (SELECT PUNCHID FROM #temp);"; public const string GET_MIN_MAX_TEMP_DATE = @" SELECT MIN(ATTENDANCEDATE) AS PayPeriodFromDate, MAX(ATTENDANCEDATE) AS PayPeriodToDate FROM #emptemp;"; public const string UPDATE_TPUNCH_INSERT = @" UPDATE TPUNCH SET PUNCHSTATUS = sub.PUNCHSTATUS FROM ( SELECT PUNCHID, CASE WHEN DENSE_RANK() OVER (PARTITION BY EMPLOYEEID, ATTENDANCEDATE ORDER BY PUNCHDATE, PUNCHTIME) % 2 = 0 THEN 1 ELSE 0 END AS PUNCHSTATUS FROM TPUNCH WHERE ATTENDANCEDATE = @AttendanceDate ) sub WHERE TPUNCH.PUNCHID = sub.PUNCHID;"; public const string UPDATE_TPUNCH_ATTENDANCEDATE_TO_PREVIOUS_DATE_BETWEEN_0_TO_360 = @" UPDATE TPUNCH SET ATTENDANCEDATE = DATEADD(DAY, -1, PUNCHDATE) FROM ( SELECT A.PUNCHID FROM TPUNCH A INNER JOIN TDAILYATTENDANCE B ON A.PUNCHDATE = B.ATTENDANCEDATE AND A.EMPLOYEEID = B.EMPLOYEEID INNER JOIN MSHIFT C ON B.SHIFTID = C.SHIFTID WHERE A.PUNCHTIME < C.STARTTIME - 180 AND A.PUNCHDATE = @AttendanceDate ) SUB WHERE TPUNCH.PUNCHID = SUB.PUNCHID;"; #endregion public const string GET_TOTAL_EMPLOYEE = @" SELECT EMPLOYEE FROM Employee employee WHERE employee.ModifiedOn >= @ModifiedOn;"; public const string UPDATE_JOBDEFINE = @" UPDATE MJOBDEFINE SET LASTRUNTIME = @LastRunTime WHERE JOBID = @JobId;"; public const string GET_TOTAL_EMPLOYEE_BASED_ON_OU = @" SELECT employee FROM Employee employee WHERE employee.WorkOU.Id IN (SELECT value FROM STRING_SPLIT(@OuIds, ','));"; #region TotalEmployee public const string GET_TOTAL_EMPLOYEE_NEW = @" SELECT emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.DATEOFJOINING AS EmployeeDateOfJoining, emp.DATEOFBIRTH AS EmployeeDateOfBirth, emp.EMPLOYEEID AS EmployeeId, emp.STATUS AS TempStatus, branch.BRANCHCODE AS OurBranchCode, dep.DEPARTMENTCODE AS DepartmentCode, designation.GCMCODE AS DesignationCode, shift.SHIFTCODE AS ShiftCode, emptype.GCMCODE AS EmployeeTypeCode, emp.EMPLOYEESTATUS AS TempEmployeeEmployerStatus, emp.DATEOFRESIGNATION AS EmployeeDateOfResignation, emp.SEX AS TempEmployeeSex FROM MEMPLOYEE emp INNER JOIN MBRANCH branch ON emp.OURBRANCHID = branch.BRANCHID INNER JOIN MDEPARTMENT dep ON emp.DEPARTMENTID = dep.DEPARTMENTID INNER JOIN MGCM designation ON emp.DESIGNATIONID = designation.GCMID INNER JOIN MSHIFT shift ON emp.SHIFTID = shift.SHIFTID INNER JOIN MGCM emptype ON emp.EMPLOYEETYPEID = emptype.GCMID;"; #endregion #region EmployeeDetailReport public const string EMPLOYEE_DESIGNATION_FROMDATE = @" SELECT EMP.EMPLOYEEID, CASE WHEN VV.EMPLOYEEID IS NULL THEN EMP.DATEOFJOINING ELSE VV.FROMDATE END AS DESIGNATIONFROMDATE, CASE WHEN VV.EMPLOYEEID IS NULL THEN GCM.GCMCODE ELSE VV.GCMCODE END AS DESIGNATIONCODE, CASE WHEN VV.EMPLOYEEID IS NULL THEN GCM.GCMNAME ELSE VV.GCMNAME END AS DESIGNATIONNAME INTO #empdesignation FROM MEMPLOYEE EMP OUTER APPLY ( SELECT DEGDATE.FROMDATE, GCM.GCMCODE, GCM.GCMNAME, A.EMPLOYEEID FROM ( SELECT EMPLOYEEID, MAX(EMPLOYEEWORKINGID) AS EMPLOYEEWORKINGID FROM MEMPLOYEEWORKING GROUP BY EMPLOYEEID ) SUB INNER JOIN MEMPLOYEEWORKING A ON A.EMPLOYEEWORKINGID = SUB.EMPLOYEEWORKINGID CROSS APPLY ( SELECT MIN(FROMDATE) AS FROMDATE FROM MEMPLOYEEWORKING WHERE DESIGNATIONID = A.DESIGNATIONID AND EMPLOYEEID = A.EMPLOYEEID ) DEGDATE INNER JOIN MGCM GCM ON A.DESIGNATIONID = GCM.GCMID WHERE A.EMPLOYEEID = EMP.EMPLOYEEID ) VV INNER JOIN MGCM GCM ON EMP.DESIGNATIONID = GCM.GCMID INNER JOIN MORGANIZATIONUNIT ouunit ON EMP.WORKOUID = ouunit.OUID WHERE EMP.EMPLOYEEID <> -1 @DtTypeFields;"; public const string EMPLOYEE_DETAIL_FORMS_REPORT = @" SELECT DISTINCT -- Employee emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.FIRSTNAME AS FirstName, emp.MIDDLENAME AS MiddleName, emp.LASTNAME AS LastName, emp.FATHERNAME AS FatherName, emp.MOTHERNAME AS MotherName, emp.SPOUSENAME AS SpouseName, emp.PRIMARYEMAILID AS PrimaryEmailId, emp.PRIMARYMOBILE AS PrimaryMobile, emp.SECONDARYMAILID AS SecondaryMailId, emp.SECONDARYMOBILE AS SecondaryMobile, emp.WORKOUID AS WorkOuId, emp.WORKTYPEID AS WorkTypeId, emp.EMPLOYEETYPEID AS EmployeeTypeId, emp.STAFFTYPEID AS StaffTypeId, emp.DESIGNATIONID AS DesignationId, emp.NATIONALITYID AS NationalityId, emp.RELIGIONID AS ReligionId, emp.EMPLOYEESTATUS AS EmployeeStatus, CASE WHEN emp.EMPLOYEESTATUS = 0 THEN 'Active' ELSE 'InActive' END AS EmployeeStatusName, CASE WHEN emp.COSTCENTERTYPE = 0 THEN 'Single' WHEN emp.COSTCENTERTYPE = 1 THEN 'Multiple' ELSE 'Allocation' END AS CostCenterTypeName, emp.DATEOFBIRTH AS DateOfBirth, emp.ACTUALDATEOFBIRTH AS ActualDateOfBirth, emp.DATEOFJOINING AS DateOfJoining, DATEADD(DAY, 480, emp.DATEOFJOINING) AS JoinServiceDate, emp.DATEOFCONFIRMATION AS DateOfConfirmation, CASE WHEN DATEDIFF(DAY, emp.DATEOFCONFIRMATION, GETDATE()) <= 60 THEN 'Delay in ' + CAST(DATEDIFF(MONTH, emp.DATEOFCONFIRMATION, GETDATE()) AS VARCHAR(50)) + ' days' ELSE 'Delay in ' + CAST(DATEDIFF(MONTH, emp.DATEOFCONFIRMATION, GETDATE()) AS VARCHAR(50)) + ' Months' END AS Delay, emp.DATEOFRESIGNATION AS DateOfResignation, emp.CALCULATEDDATEOFRETIREMENT AS CalculateOfRetirement, emp.ACTUALDATEOFRETIREMENT AS ActualDateOfRetirement, emp.MARITALSTATUS AS MaritalStatus, CASE emp.MARITALSTATUS WHEN 0 THEN 'UnMarried' WHEN 1 THEN 'Married' WHEN 2 THEN 'Widow' ELSE 'Divorcee' END AS MaritalStatusName, emp.SEX AS Sex, CASE emp.SEX WHEN 0 THEN 'Male' ELSE 'Female' END AS SexName, CASE emp.SEX WHEN 0 THEN 'Mr.' ELSE 'M/s' END AS EmpSalutation, emp.BLOODGROUP AS BloodGroup, CASE WHEN emp.ISPFAPPLICABLE = 0 THEN 'Yes' ELSE 'No' END AS IsPFApplicable, CASE emp.PAYMODE WHEN 0 THEN 'CASH' WHEN 1 THEN 'CHEQUE' ELSE 'BANK' END AS PayMode, CASE emp.OTPAYMODE WHEN 0 THEN 'CASH' WHEN 1 THEN 'CHEQUE' ELSE 'BANK' END AS OtPayMode, CASE emp.PFSTATUS WHEN 0 THEN 'Non Member' WHEN 1 THEN 'Member' ELSE 'End' END AS PFStatus, emp.STATUTORYPFPERCENTAGE AS StatutoryPFPercentage, emp.EMPLOYEEPFPERCENTAGE AS EmployeePFPercentage, emp.EMPLOYERPFPERCENTAGE AS EmployerPFPercentage, emp.FAMILYPENSIONNUMBER AS FamilyPensionNumber, emp.FAMILYPENSIONPERCENTAGE AS FamilyPensionPercentage, emp.PFJOINDATE AS PFJoinDate, emp.PFENDDATE AS PFEndDate, CASE WHEN emp.ISESIAPPLICABLE = 0 THEN 'Yes' ELSE 'No' END AS IsESIApplicable, CASE emp.ESISTATUS WHEN 0 THEN 'Non Member' WHEN 1 THEN 'Member' ELSE 'End' END AS EmployeeESIStatus, emp.ESIJOINDATE AS EmployeeESIJoinDate, emp.ESIENDDATE AS EmployeeESIEndDate, emp.ESIDISPENSARY AS EmployeeESIDispensary, emp.EMPLOYEEESIPERCENTAGE AS EmployeeEmployeeESIPercentage, CASE WHEN emp.ISTDSAPPLICABLE = 0 THEN 'Yes' ELSE 'No' END AS IsTDSApplicable, emp.SALARYGRADEID AS SalaryGradeId, emp.PANNUMBER AS PANNumber, emp.ACCOUNTNUMBER AS AccountNumber, emp.SHIFTID AS ShiftId, CASE emp.WEEKOFFDAY WHEN 0 THEN 'SUN' WHEN 1 THEN 'MON' WHEN 2 THEN 'TUE' WHEN 3 THEN 'WED' WHEN 4 THEN 'THU' WHEN 5 THEN 'FRI' ELSE 'SAT' END AS WeekOffDay, emp.ACCOUNTID AS AccountId, emp.NOMINEE AS Nominee, emp.NOMINEERELATION AS NomineeRelation, CASE emp.ATTENDANCETYPE WHEN 0 THEN 'Not required' WHEN 1 THEN 'Org' WHEN 2 THEN 'Location' WHEN 3 THEN 'On Field' ELSE 'Onsite' END AS AttendanceType, emp.ADHARNUMBER AS ADHARNumber, emp.PFUANNUMBER AS PFUANNumber, emp.LOCATIONID AS LocationId, loc.LOCATIONCODE AS LocationCode, loc.LOCATIONNAME AS LocationName, stafftype.GCMCODE AS StaffTypeCode, stafftype.GCMNAME AS StaffTypeName, empdesg.DESIGNATIONFROMDATE AS DesignationFromDate, empdesg.DESIGNATIONCODE AS DesignationCode, empdesg.DESIGNATIONNAME AS DesignationName, worktype.GCMCODE AS WorkTypeCode, worktype.GCMNAME AS WorkTypeName, nationality.GCMCODE AS NationalityCode, nationality.GCMNAME AS NationalityName, religion.GCMCODE AS ReligionCode, religion.GCMNAME AS ReligionName, -- Nominee empno.EMPLOYEENOMINEEID AS EmployeeNomineeId, empno.EMPLOYEEID AS NEmployeeId, empno.NOMINEETYPE AS NomineeType, empno.SLNO AS SlNo, empno.NOMINEENAME AS NomineeName, empno.RELATION AS NRelation, ISNULL(empno.DOB, '1899-01-01') AS NomineeDOB, DATEDIFF(YEAR, empno.DOB, GETDATE()) AS NomineeDOBYear, empno.ADDRESS AS NomineeAddress, empno.PERCENTAGE AS NPercentage, empno.GUARDIAN AS NGuardian, empno.GUARDIANADDRESS AS NGuardianAddress, empno.AADHARNUMBER AS NomineeAADHARNumber, -- Department emp.DEPARTMENTID AS DepartmentId, dep.DEPARTMENTCODE AS DepartmentCode, dep.DEPARTMENTNAME AS DepartmentName, -- OuGroup emp.OUGROUPID AS OuGroupId, ougrp.ORGANIZATIONGROUPCODE AS OUGroupCode, ougrp.ORGANIZATIONGROUPNAME AS OUGroupName, -- OuUnit ouunit.ORGANIZATIONUNITCODE AS OUUnitCode, ouunit.ORGANIZATIONUNITNAME AS OUUnitName, -- Company ouunit.COMPANYID AS CompanyId, mc.COMPANYCODE AS CompanyCode, mc.COMPANYNAME AS CompanyName, -- PayGroup emp.PAYGROUPID AS PayGroupId, paygrp.PAYGROUPCODE AS PayGroupCode, paygrp.PAYGROUPNAME AS PayGroupName, -- Branch ouunit.BRANCHID AS BranchId, branch.BRANCHCODE AS BranchCode, branch.BRANCHNAME AS BranchName, -- DAGroup emp.DAPOINTGROUPID AS DAGroupId, dapointgrp.DAPOINTGROUPCODE AS DAGroupCode, dapointgrp.DAPOINTGROUPNAME AS DAGroupName, -- Bank bankbranch.BANKID AS BankId, bankbranchbank.GCMCODE AS BankCode, bankbranchbank.GCMNAME AS BankName, -- Bank Address bankadd.ADDRESSID AS AddressId, bankadd.ADDRESSLINE1 AS Line1, bankadd.ADDRESSLINE2 AS Line2, bankadd.ADDRESSLINE3 AS Line3, bankadd.ADDRESSLINE4 AS Line4, bankadd.ADDRESSLINE5 AS Line5, bankadd.ADDRESSTYPE AS AddressType, bankadd.MAIL AS AddressMail, bankadd.MOBILE AS AddressMobile, bankadd.PHONE AS AddressPhone, bankadd.WEB AS AddressWeb, bankadd.CITYID AS CityId, bankadd.COUNTRYID AS CountryId, bankadd.GSTIN AS GSTIN, -- Bankbranch emp.EMPLOYEEBANKBRANCHID AS BankBranchId, bankbranch.BANKBRANCHNAME AS BankBranchCode, bankbranch.BANKBRANCHNAME AS BankBranchName, bankbranch.IFSC AS BankIFSC, -- Emp Address adds.ADDRESSID AS EmpAddressId, adds.ADDRESSLINE1 AS EmpAddressLine1, adds.ADDRESSLINE2 AS EmpAddressLine2, adds.ADDRESSLINE3 AS EmpAddressLine3, adds.ADDRESSLINE4 AS EmpAddressLine4, adds.ADDRESSLINE5 AS EmpAddressLine5, empstate.STATENAME AS EmpAddressState, adds.ADDRESSTYPE AS EmpAddressType, adds.MAIL AS EmpAddressMail, adds.MOBILE AS EmpAddressMobile, adds.PHONE AS EmpAddressPhone, adds.WEB AS EmpAddressWeb, adds.CITYID AS EmpCityId, city.CITYCODE AS EmpCityCode, city.CITYNAME AS EmpCityName, adds.COUNTRYID AS EmpCountryId, country.COUNTRYCODE AS EmpCountryCode, country.COUNTRYNAME AS EmpCountryName, adds.GSTIN AS EmpGSTIN, adds.ZIPCODE AS EmpZipCode, -- Temp Address addstype.ADDRESSID AS TmpAddressId, addstype.ADDRESSLINE1 AS TmpAddressLine1, addstype.ADDRESSLINE2 AS TmpAddressLine2, addstype.ADDRESSLINE3 AS TmpAddressLine3, addstype.ADDRESSLINE4 AS TmpAddressLine4, addstype.ADDRESSLINE5 AS TmpAddressLine5, addstype.ADDRESSTYPE AS TmpAddressType, tmpstate.STATENAME AS TmpAddressState, addstype.MAIL AS TmpAddressMail, addstype.MOBILE AS TmpAddressMobile, addstype.PHONE AS TmpAddressPhone, addstype.WEB AS TmpAddressWeb, addstype.CITYID AS TmpCityId, addstype.COUNTRYID AS TmpCountryId, addstype.GSTIN AS TmpGSTIN, addstype.ZIPCODE AS TmpZipCode, -- Contact cont.CONTACTID AS ContactId, cont.COMPANY AS Company, cont.CONTACTNATURE AS ContactNature, cont.DESIGNATION AS ContactDesignation, cont.DOB AS ContactDOB, cont.FIRSTNAME AS ContactFirstName, cont.LASTNAME AS ContactLastName, cont.MIDDLENAME AS ContactMiddleName, cont.MOBILENO AS ContactMobileNo, cont.NAME AS ContactName, cont.PHONENO AS ContactPhoneNo, cont.RELATIONSHIP AS ContactRelationShip, cont.SALUTATION AS ContactSalutation, cont.SEX AS ContactSex, cont.STATUS AS ContactStatus, -- EmployeeQualification empqua.EMPLOYEEQUALIFICATIONID AS EmployeeQualificationId, empqua.SLNO AS EmployeeQualificationSlNo, empqua.COURSE AS EmployeeQualificationCourse, empqua.INSTITUTE AS EmployeeQualificationInstitute, empqua.DURATION AS EmployeeQualificationDuration, empqua.FROMYEAR AS EmployeeQualificationFromYear, empqua.TOYEAR AS EmployeeQualificationToYear, empqua.GRADE AS EmployeeQualificationGrade, empqua.REMARKS AS EmployeeQualificationRemarks, -- EmployeeWorking empwork.FROMDATE AS EmployeeWorkingFromDate, empwork.TODATE AS EmployeeWorkingToDate, empwork.SLNO AS EmployeeWorkingSlNo, empworkdepart.DEPARTMENTCODE AS EmployeeWorkingDepartmentCode, empworkdepart.DEPARTMENTNAME AS EmployeeWorkingDepartmentName, empworkloc.GCMCODE AS EmployeeWorkingLocationCode, empworkloc.GCMNAME AS EmployeeWorkingLocationName, empworkgrade.GCMCODE AS EmployeeWorkingGradeCode, empworkgrade.GCMNAME AS EmployeeWorkingGradeName, empworkgrade.REMARKS AS EmployeeWorkingGradeRemarks, -- EmployeeExperience empexp.EMPLOYEEEXPERIENCEID AS EmployeeExperienceId, empexp.SLNO AS EmployeeExperienceSlno, empexp.EMPLOYER AS EmployeeExperienceEmployer, empexp.CITY AS EmployeeExperienceCity, empexp.FROMMONTHYEAR AS EmployeeExperienceFromMonthYear, empexp.TOMONTHYEAR AS EmployeeExperienceToMonthYear, empexp.NUMBEROFYEAR AS EmployeeExperienceNumberOfYear, empexp.DESIGNATION AS EmployeeExperienceDesignation, empexp.SALARYDRAWN AS EmployeeExperienceSalaryDrawn, empexp.REASONFORLEAVING AS EmployeeExperienceReasonForLeaving, -- EmployeeReference empref.EMPLOYEEREFERENCEID AS EmployeeReferenceId, empref.SLNO AS EmployeeReferenceSlNo, empref.REFERENCE AS EmployeeReferenceReference, empref.REFERENCECOMPANY AS EmployeeReferenceReferenceCompany, empref.DESIGNATION AS EmployeeReferenceDesignation, empref.RELATIONSHIP AS EmployeeReferenceRelationship, empref.TIMEKNOWN AS EmployeeReferenceTimeKnown, empref.ISVERIFIED AS EmployeeReferenceIsVerified, empref.VERIFIEDON AS EmployeeReferenceVerifiedOn, empref.REMARKS AS EmployeeReferenceRemarks, empref.VERIFIEDBYID AS VerifiedById, -- EmployeeSkill empskill.EMPLOYEESKILLID AS EmployeeSkillId, empskill.SLNO AS EmployeeSkillSlno, empskill.SKILLLEVEL AS EmployeeSkillSkillLevel, empskill.PARTICULARS AS EmployeeSkillParticulars, empskill.SKILLID AS SkillId, -- New Fields emp.EMPLOYEECOMMONID AS EmployeeCommonId, emp.CODEDEFINEID AS Codedefineid, emp.COSTCENTERTYPE AS CostCenterType, emp.COSTCENTERID AS CostCenterId, emp.ISCROSSOU AS IsCrossOu, emp.COSTCENTERPATTERNID AS CostCenterPatternId, emp.CASTEID AS CasteId, emp.HEIGHTINCMS AS HeightinCMS, emp.WEIGHTINKGS AS WeightinCMS, emp.CHESTSIZE AS ChestSize, CASE WHEN emp.ISHANDICAPPED = 0 THEN 'Yes' ELSE 'No' END AS IsHandiCapped, emp.IDENTIFICATIONMARK AS IdentificationMark, emp.PHYSICALCONDITION AS PhysicalCondition, CASE WHEN emp.ISCONFIRMED = 0 THEN 'Yes' ELSE 'No' END AS IsConfirmed, CASE WHEN emp.ISFAMILYPENSION = 0 THEN 'Yes' ELSE 'No' END AS IsFamilyPension, CASE WHEN emp.ISESIHOURLYCONTRIBUTION = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsESIHourlyContribution, CASE emp.BASICON WHEN 0 THEN 'MONTHLY' WHEN 1 THEN 'DAILY' WHEN 2 THEN 'WEEKLY' ELSE 'HOURLY' END AS EmployeeBasicOn, emp.ONSITEPLACE AS OnSitePlace, CASE WHEN emp.ISDAILYREPORT = 0 THEN 'Yes' ELSE 'No' END AS IsDailyReport, CASE emp.PUNCHTYPE WHEN 0 THEN 'Not required' WHEN 1 THEN 'PUNCH' WHEN 2 THEN 'LOGIN/OFF' WHEN 3 THEN 'ONLY ATTENDANCE' WHEN 4 THEN 'PUNCH BETWEEN SAME ATTENDANCEDATE' ELSE 'PUNCH BETWEEN ACROSS ATTENDANCEDATE' END AS PunchType, emp.PRIMARYMOBILE AS PrimaryMoblie, emp.REPORTINGTOEMPLOYEEID AS ReportinToEmployeeId, emp.EXPERIENCE AS Experience, CASE WHEN emp.ISTEMPORARYCARD = 0 THEN 'Yes' ELSE 'No' END AS IsTemporaryCard, emp.TEMPORARYCARDNUMBER AS TemporaryCardNumber, emp.CARDVALIDTILL AS CardValidTill, CASE WHEN emp.ISNUMBERREQUIRED = 0 THEN 'Yes' ELSE 'No' END AS IsNumberRequired, emp.BIOMATCHNUMBER AS BioMatchNumber, emp.ADVANCEACCOUNTID AS AdvanceAccountId, emp.SALARYACCOUNTID AS SalaryAccountId, emp.DEFAULTIMAGEID AS DefaultImageId, CASE WHEN emp.ISCONTRACT = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsContract, CASE WHEN emp.ISNUMBER = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsNumber, CASE WHEN emp.ISPURCHASE = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsPurchase, CASE WHEN emp.ISMARKETING = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsMarketing, CASE WHEN emp.ISSALES = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsSales, CASE WHEN emp.ISPLANNING = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsPlanning, CASE WHEN emp.ISSERVICE = 0 THEN 'Yes' ELSE 'No' END AS EmployeeIsService, CASE WHEN emp.ISUSER = 0 THEN 'Yes' ELSE 'No' END AS IsUser, emp.VERSION AS Version, CASE emp.STATUS WHEN 0 THEN 'PENDING' WHEN 1 THEN 'ACTIVE' WHEN 2 THEN 'DELETED' WHEN 3 THEN 'AMENDED' WHEN 4 THEN 'INACTIVE' ELSE 'ARCHIVED' END AS Status, emp.CREATEDBYID AS CreatedById, emp.CREATEDON AS CreatedOn, emp.MODIFIEDBYID AS EmployeeModifiedById, emp.MODIFIEDON AS ModifiedOn, emp.SEGMENTID AS SegmentId, ouunit.DIVISIONID AS DivisionId, emp.SHIFTPATTERNDETAILID AS ShiftPatternDetailId, emp.PATTERNDATE AS PatternDate, emp.REASONFORESILEAVING AS EmployeeReasonForESILeaving, emp.REASONFORPFLEAVING AS EmployeeReasonForPFLeaving, emp.VILLAGEID AS VillageId, emp.EMPLOYERESIPERCENTAGE AS EmployeeEmployerESIPercentage, emp.SOURCETYPE AS SourceType, emp.SORTORDER AS SortOrder, emp.OTTYPE AS OtType, emp.CALENDARID AS CalendarId, emp.PROBATIONSTARTDATE AS ProbationStartDate, emp.RESOURCETYPEID AS ResourceTypeId, restype.RESOURCETYPECODE AS ResourceTypeCode, restype.RESOURCETYPENAME AS ResourceTypeName, CASE WHEN emp.ISPRODUCTION = 0 THEN 'Yes' ELSE 'No' END AS IsProduction, emp.PAYCONFIGURATIONID AS PayConfigurationId, payconfig.PAYCONFIGURATIONCODE AS PayConfigurationCode, payconfig.PAYCONFIGURATIONNAME AS PayConfigurationName, emp.PRODUCTCATEGORYIDS AS ProductCategoryIds, emp.PRODUCTPRICECATEGORYIDS AS ProductPriceCategoryIds, emp.TENANTID AS TenantId, CASE WHEN emp.ISPIREQUIRED = 0 THEN 'Yes' ELSE 'No' END AS IsPIRequired, cal.CALENDARCODE AS CalendarCode, cal.CALENDARNAME AS CalendarName, caste.GCMCODE AS CasteCode, caste.GCMNAME AS CasteName, define.CATEGORYCODE AS CodeDefineCategoryCode, define.CATEGORYNAME AS CodeDefineCategoryName, costcenter.COSTCENTERCODE AS CostCenterCode, costcenter.COSTCENTERNAME AS CostCenterName, pattern.COSTCENTERPATTERNCODE AS CostCenterPatternCode, pattern.COSTCENTERPATTERNNAME AS CostCenterPatternName, created.USERCODE AS CreatedByCode, created.USERNAME AS CreatedByName, modified.USERCODE AS ModifiedByCode, modified.USERNAME AS ModifiedByName, empcommon.EMPLOYEECODE AS EmployeeCommonCode, empcommon.EMPLOYEENAME AS EmployeeCommonName, salaryacc.ACCOUNTCODE AS SalaryAccountCode, salaryacc.ACCOUNTNAME AS SalaryAccountName, advanceacc.ACCOUNTCODE AS AdvanceAccountCode, advanceacc.ACCOUNTNAME AS AdvanceAccountName, -- Shift shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, shift.SHIFTMINUTES AS ShiftMinutes, shift.STARTTIME AS ShiftStartTime, shift.ENDTIME AS ShiftEndTime, shift.BREAKMINUTES AS ShiftBreakMinute, -- Village village.VILLAGECODE AS VillageCode, village.VILLAGENAME AS VillageName, -- Segment segment.GCMCODE AS SegmentCode, segment.GCMNAME AS SegmentName, -- Emp Addon empaddon.EMPLOYEEID AS EmpAddonEmployeeId, -- Employee Type & Salary Grade emptype.GCMCODE AS EmployeeTypeCode, emptype.GCMNAME AS EmployeeTypeName, salgrad.GCMCODE AS SalaryGradeCode, salgrad.GCMNAME AS SalaryGradeName, -- Unit & Division unit.GCMCODE AS UnitCode, unit.GCMNAME AS UnitName, div.DIVISIONCODE AS DivisionCode, div.DIVISIONNAME AS DivisionName, -- Account account.ACCOUNTCODE AS AccountCode, account.ACCOUNTNAME AS AccountName, -- Reporting To reportto.EMPLOYEECODE AS ReportInttoEmployeeCode, reportto.EMPLOYEENAME AS ReportInttoEmployeeName, -- OU Level oulevel.PFNUMBER AS PFNumber, oulevel.ESINUMBER AS EmployeeESINumber, -- Salary Revision emprelatedfields.C2C AS EmployeeRelatedSalaryC2C, 12 * emprelatedfields.C2C AS EmployeeSalaryC2CForYear, emprelatedfields.NETSALARY AS EmployeeRelatedSalaryNetPay, empcurr.CURRENCYID AS CurrencyId, empcurr.CURRENCYCODE AS CurrencyCode, empcurr.CURRENCYNAME AS CurrencyName, emprelatedfields.EffectiveFrom AS RevEffectiveFromDate, emprelatedfields.EffectiveTo AS RevEffectiveToDate, emprelatedfields.RevisionDate AS RevisionDate, -- User ISNULL(usermaster.DEFAULTCMSID, 'NONE') AS UserDefaultImageId, ISNULL(usermaster.ESIGNATURECMSID, 'NONE') AS UserESignatureId, -- Current OU currentou.OUID AS CurrentOUId, currentou.ORGANIZATIONUNITCODE AS CurrentOUCode, currentou.ORGANIZATIONUNITNAME AS CurrentOUName INTO #temp11 FROM #empdesignation empdesg INNER JOIN MEMPLOYEE emp ON empdesg.EMPLOYEEID = emp.EMPLOYEEID LEFT JOIN MUSER usermaster ON emp.EMPLOYEEID = usermaster.USERID LEFT JOIN MRESOURCETYPE restype ON emp.RESOURCETYPEID = restype.RESOURCETYPEID LEFT JOIN MPAYCONFIGURATION payconfig ON emp.PAYCONFIGURATIONID = payconfig.PAYCONFIGURATIONID OUTER APPLY ( SELECT b.EMPLOYEEID, b.C2C, b.NETSALARY, rev.EffectiveFrom, rev.EffectiveTo, rev.RevisionDate FROM MPAYPERIOD a INNER JOIN TPAYPROCESS b ON a.PAYPERIODID = b.PAYPERIODID INNER JOIN ( SELECT EMPLOYEEID, MAX(TODATE) AS TODATE FROM TPAYPROCESS WHERE PAYPERIODID = -3 GROUP BY EMPLOYEEID ) sub ON a.TODATE = sub.TODATE AND b.EMPLOYEEID = sub.EMPLOYEEID LEFT JOIN TPAYREVISION rev ON ISNULL(b.PAYREVISIONID, -1) = rev.PAYREVISIONID WHERE a.PERIODTYPE = 3 AND b.EMPLOYEEID = emp.EMPLOYEEID ) emprelatedfields LEFT JOIN MEMPLOYEENOMINEE empno ON emp.EMPLOYEEID = empno.EMPLOYEEID OUTER APPLY (SELECT TOP 1 * FROM MADDRESS WHERE OBJECTID = emp.EMPLOYEEID AND OBJECTTYPEID = -1399999905) adds LEFT JOIN MSTATE empstate ON adds.STATEID = empstate.STATEID LEFT JOIN MCITY city ON adds.CITYID = city.CITYID LEFT JOIN MCOUNTRY country ON adds.COUNTRYID = country.COUNTRYID OUTER APPLY (SELECT TOP 1 * FROM MADDRESS WHERE OBJECTID = emp.EMPLOYEEID AND OBJECTTYPEID = -1399999905 AND ADDRESSTYPE = 1) addstype LEFT JOIN MSTATE tmpstate ON addstype.STATEID = tmpstate.STATEID OUTER APPLY (SELECT TOP 1 * FROM MCONTACT WHERE OBJECTID = emp.EMPLOYEEID AND OBJECTTYPEID = -1399999905) cont LEFT JOIN MEMPLOYEEWORKING empwork ON emp.EMPLOYEEID = empwork.EMPLOYEEID LEFT JOIN MDEPARTMENT empworkdepart ON empworkdepart.DEPARTMENTID = empwork.DEPARTMENTID LEFT JOIN MGCM empworkloc ON empworkloc.GCMID = empwork.LOCATIONID LEFT JOIN MGCM empworkgrade ON empworkgrade.GCMID = empwork.SALARYGRADEID LEFT JOIN MEMPLOYEEEXPERIENCE empexp ON emp.EMPLOYEEID = empexp.EMPLOYEEID LEFT JOIN MEMPLOYEEQUALIFICATION empqua ON emp.EMPLOYEEID = empqua.EMPLOYEEID LEFT JOIN MEMPLOYEEREFERENCE empref ON emp.EMPLOYEEID = empref.EMPLOYEEID LEFT JOIN MEMPLOYEESKILL empskill ON emp.EMPLOYEEID = empskill.EMPLOYEEID LEFT JOIN MGCM emptype ON emp.EMPLOYEETYPEID = emptype.GCMID LEFT JOIN MEMPLOYEE reportto ON emp.REPORTINGTOEMPLOYEEID = reportto.EMPLOYEEID LEFT JOIN MVILLAGE village ON emp.VILLAGEID = village.VILLAGEID INNER JOIN MDEPARTMENT dep ON emp.DEPARTMENTID = dep.DEPARTMENTID INNER JOIN MORGANIZATIONGROUP ougrp ON emp.OUGROUPID = ougrp.ORGANIZATIONGROUPID INNER JOIN MORGANIZATIONUNIT ouunit ON emp.WORKOUID = ouunit.OUID INNER JOIN MCOMPANY mc ON ouunit.COMPANYID = mc.COMPANYID INNER JOIN MPAYGROUP paygrp ON emp.PAYGROUPID = paygrp.PAYGROUPID INNER JOIN MBRANCH branch ON ouunit.BRANCHID = branch.BRANCHID INNER JOIN MDAPOINTGROUP dapointgrp ON emp.DAPOINTGROUPID = dapointgrp.DAPOINTGROUPID INNER JOIN MSHIFT shift ON emp.SHIFTID = shift.SHIFTID INNER JOIN MACCOUNT account ON emp.ACCOUNTID = account.ACCOUNTID INNER JOIN MACCOUNT salaryacc ON emp.SALARYACCOUNTID = salaryacc.ACCOUNTID INNER JOIN MACCOUNT advanceacc ON emp.ADVANCEACCOUNTID = advanceacc.ACCOUNTID INNER JOIN MGCM unit ON emp.UNITID = unit.GCMID INNER JOIN MDIVISION div ON ouunit.DIVISIONID = div.DIVISIONID INNER JOIN MGCM worktype ON emp.WORKTYPEID = worktype.GCMID INNER JOIN MGCM stafftype ON emp.STAFFTYPEID = stafftype.GCMID INNER JOIN MGCM designation ON emp.DESIGNATIONID = designation.GCMID INNER JOIN MBANKBRANCH bankbranch ON emp.EMPLOYEEBANKBRANCHID = bankbranch.BANKBRANCHID INNER JOIN MGCM bankbranchbank ON bankbranch.BANKID = bankbranchbank.GCMID INNER JOIN MLOCATION loc ON emp.LOCATIONID = loc.LOCATIONID INNER JOIN MGCM nationality ON emp.NATIONALITYID = nationality.GCMID INNER JOIN MGCM religion ON emp.RELIGIONID = religion.GCMID -- LEFT (not INNER): MEMPLOYEEADDON rows are client-defined/optional — an employee -- with none must still appear in this report (only EMPLOYEEID, the FK, is read here; -- no client-varying addon columns are selected, so this join itself is schema-safe). LEFT JOIN MEMPLOYEEADDON empaddon ON emp.EMPLOYEEID = empaddon.EMPLOYEEID INNER JOIN MCALENDAR cal ON emp.CALENDARID = cal.CALENDARID INNER JOIN MGCM caste ON emp.CASTEID = caste.GCMID INNER JOIN MCODEDEFINE define ON emp.CODEDEFINEID = define.CODEDEFINEID INNER JOIN MCOSTCENTER costcenter ON emp.COSTCENTERID = costcenter.COSTCENTERID INNER JOIN MCOSTCENTERPATTERN pattern ON emp.COSTCENTERPATTERNID = pattern.COSTCENTERPATTERNID INNER JOIN MUSER created ON emp.CREATEDBYID = created.USERID INNER JOIN MEMPLOYEE empcommon ON emp.EMPLOYEECOMMONID = empcommon.EMPLOYEEID INNER JOIN MUSER modified ON emp.MODIFIEDBYID = modified.USERID INNER JOIN MGCM segment ON emp.SEGMENTID = segment.GCMID INNER JOIN MGCM salgrad ON emp.SALARYGRADEID = salgrad.GCMID INNER JOIN MOULEVELSETTING oulevel ON oulevel.OUID = @OuId INNER JOIN MCURRENCY empcurr ON emp.CURRENCYID = empcurr.CURRENCYID INNER JOIN MORGANIZATIONUNIT currentou ON currentou.OUID = emp.CURRENTOUID @DtTypeFields @EmpReplaceFields;"; public const string EMPLOYEE_DETAIL_FORMS_TEMP_REPORT = @" SELECT * FROM #temp11 ORDER BY EmployeeId;"; public const string EMPLOYEE_DETAIL_FORMS_TEMP_REPORT_NEW = @" SELECT * FROM #temp11 ORDER BY EmployeeId, LATESTPAYREVISIONID;"; public const string LEGACY_EMPLOYEE_LIST_FORMS_REPORT = @" WITH cte AS ( SELECT EMPLOYEEID, EMPLOYEECODE, LEGACYEMPLOYEECODE, EMPLOYEEID AS LegacyEmployeeId FROM MEMPLOYEE UNION ALL SELECT a.EMPLOYEEID, b.EMPLOYEECODE, b.LEGACYEMPLOYEECODE, b.EMPLOYEEID AS LegacyEmployeeId FROM cte a INNER JOIN MEMPLOYEE b ON a.LEGACYEMPLOYEECODE = b.EMPLOYEECODE ) SELECT a.EMPLOYEEID AS RootEmployeeId, b.* INTO #tempLegacyEmployee FROM cte a INNER JOIN MEMPLOYEE b ON a.LegacyEmployeeId = b.EMPLOYEEID;"; public const string EMPLOYEE_LIST_LATEST_PAY_REVISION_TEMP_REPORT = @" SELECT c.PAYREVISIONID AS PayRevisionId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName INTO #latestpayrev FROM MEMPLOYEE a LEFT JOIN TPAYREVISION c ON a.EMPLOYEEID = c.EMPLOYEEID AND c.APPLICABLE = 5 AND c.NATURE = 0 AND c.EFFECTIVETO >= GETDATE() AND c.EFFECTIVEFROM <= GETDATE() LEFT JOIN TPAYREVISIONADDON b ON b.PAYREVISIONID = c.PAYREVISIONID WHERE a.EMPLOYEESTATUS = 0 AND a.STATUS = 1 AND b.PAYREVISIONID IS NOT NULL;"; public const string EMPLOYEE_LIST_FORMS_REPORT = @" -- [Same as EMPLOYEE_DETAIL_FORMS_REPORT but without INTO #temp11 and with additional fields] -- (Omitted for brevity — use same logic as above with @OuId and dynamic fields) -- Final SELECT * FROM #temp11 will be used after this query runs -- This is a **reduced version** used in list view -- Full version is in EMPLOYEE_DETAIL_FORMS_REPORT -- Use same pattern: explicit JOINs, @ parameters, no string concat -- See full version above for structure ;"; #endregion public static class EmployeeReportQueries { public const string EmployeeFormsArrayReport = """ SELECT -- Employee emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.FIRSTNAME AS FirstName, emp.MIDDLENAME AS MiddleName, emp.LASTNAME AS LastName, emp.FATHERNAME AS FatherName, emp.MOTHERNAME AS MotherName, emp.SPOUSENAME AS SpouseName, emp.MARITALSTATUS AS MaritalStatus, CASE WHEN emp.MaritalStatus = 0 THEN 'UnMarried' WHEN emp.MaritalStatus = 1 THEN 'Married' WHEN emp.MaritalStatus = 2 THEN 'Widow' ELSE 'Divorcee' END AS MaritalStatusName, emp.SEX AS Sex, CASE WHEN emp.Sex = 0 THEN 'Male' ELSE 'Female' END AS SexName, emp.DATEOFBIRTH AS DateOfBirth, emp.ACTUALDATEOFBIRTH AS ActualDateOfBirth, emp.DATEOFJOINING AS DateOfJoining, emp.DATEOFCONFIRMATION AS DateOfConfirmation, emp.DATEOFRESIGNATION AS DateOfResignation, emp.DEFAULTIMAGEID AS DefaultImageId, -- Emp Address (any address) adds.ADDRESSID AS EmpAddressId, adds.ADDRESSLINE1 AS EmpAddressLine1, adds.ADDRESSLINE2 AS EmpAddressLine2, adds.ADDRESSLINE3 AS EmpAddressLine3, adds.ADDRESSLINE4 AS EmpAddressLine4, adds.ADDRESSLINE5 AS EmpAddressLine5, adds.ADDRESSTYPE AS EmpAddressType, adds.MAIL AS EmpAddressMail, adds.MOBILE AS EmpAddressMobile, adds.PHONE AS EmpAddressPhone, adds.WEB AS EmpAddressWeb, adds.CITYID AS EmpCityId, adds.COUNTRYID AS EmpCountryId, adds.GSTIN AS EmpGSTIN, adds.ZIPCODE AS EmpZipCode, empsal.NetPay AS EmployeeSalaryNetPay, -- Temporary address (type = 1) addstype.ADDRESSID AS TmpAddressId, addstype.ADDRESSLINE1 AS TmpAddressLine1, addstype.ADDRESSLINE2 AS TmpAddressLine2, addstype.ADDRESSLINE3 AS TmpAddressLine3, addstype.ADDRESSLINE4 AS TmpAddressLine4, addstype.ADDRESSLINE5 AS TmpAddressLine5, addstype.ADDRESSTYPE AS TmpAddressType, designation.GCMCODE AS DesignationCode, designation.GCMNAME AS DesignationName, emp.LOCATIONID AS LocationId, loc.LOCATIONCODE AS LocationCode, loc.LOCATIONNAME AS LocationName, emp.ONSITEPLACE AS OnSitePlace, emp.DEPARTMENTID AS DepartmentId, dep.DEPARTMENTCODE AS DepartmentCode, dep.DEPARTMENTNAME AS DepartmentName, emp.PRIMARYEMAILID AS PrimaryEmailId, emp.SECONDARYMAILID AS SecondaryMailId, emp.PRIMARYMOBILE AS PrimaryMobile, emp.SECONDARYMOBILE AS SecondaryMobile, ISNULL(usermaster.DEFAULTCMSID,'NONE') AS UserDefaultImageId, ISNULL(usermaster.ESIGNATURECMSID,'NONE') AS UserESignatureId FROM MEMPLOYEE emp LEFT JOIN MUSER usermaster ON emp.EMPLOYEEID = usermaster.userid OUTER APPLY (SELECT TOP (1) * FROM MADDRESS WHERE OBJECTID = emp.employeeid AND OBJECTTypeID = -1399999905) adds OUTER APPLY (SELECT TOP (1) * FROM MADDRESS WHERE OBJECTID = emp.employeeid AND OBJECTTypeID = -1399999905 AND ADDRESSTYPE = 1) addstype OUTER APPLY ( SELECT a.* FROM MEMPLOYEESALARY a INNER JOIN ( SELECT MAX(Employeesalaryid) EMPLOYEESALARYID, EMPLOYEEID FROM MEMPLOYEESALARY a INNER JOIN MPAYPERIOD b ON a.FROMDATE >= b.FROMDATE AND a.TODATE <= b.TODATE AND b.PERIODTYPE = 0 WHERE a.EMPLOYEEID = emp.EMPLOYEEID GROUP BY EMPLOYEEID ) sub ON a.EMPLOYEESALARYID = sub.EMPLOYEESALARYID ) empsal INNER JOIN MLOCATION loc ON emp.LOCATIONID = loc.LOCATIONID INNER JOIN MGCM designation ON emp.DESIGNATIONID = designation.GCMID INNER JOIN MDEPARTMENT dep ON emp.DEPARTMENTID = dep.DEPARTMENTID INNER JOIN MORGANIZATIONUNIT ouunit ON emp.WORKOUID = ouunit.OUID WHERE 1 = 1 :dttypefields :empreplacefields """; // The three “detail” reports are identical in structure – only the columns change public const string EmployeeQualificationDetailFormsReport = """ SELECT emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, empqua.EMPLOYEEQUALIFICATIONID AS EmployeeQualificationId, empqua.SlNo AS EmployeeQualificationSlNo, empqua.Course AS EmployeeQualificationCourse, empqua.Institute AS EmployeeQualificationInstitute, empqua.Duration AS EmployeeQualificationDuration, empqua.FromYear AS EmployeeQualificationFromYear, empqua.ToYear AS EmployeeQualificationToYear, empqua.Grade AS EmployeeQualificationGrade, empqua.Remarks AS EmployeeQualificationRemarks FROM MEMPLOYEE emp LEFT JOIN MEMPLOYEEQUALIFICATION empqua ON emp.employeeid = empqua.employeeid INNER JOIN MORGANIZATIONUNIT ouunit ON emp.WORKOUID = ouunit.OUID WHERE 1 = 1 :dttypefields :empreplacefields """; public const string EmployeeNomineeDetailFormsReport = """ SELECT emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, empno.EMPLOYEENOMINEEID AS EmployeeNomineeId, empno.EMPLOYEEID AS NEmployeeId, empno.NOMINEETYPE AS NomineeType, empno.SLNO AS SlNo, empno.NOMINEENAME AS NomineeName, empno.RELATION AS NRelation, COALESCE(empno.DOB, '1899-01-01') AS NomineeDOB, DATEDIFF(YEAR, empno.DOB, GETDATE()) AS NomineeDOBYear, empno.ADDRESS AS NomineeAddress, empno.PERCENTAGE AS NPercentage, empno.GUARDIAN AS NGuardian, empno.GUARDIANADDRESS AS NGuardianAddress, empno.AADHARNUMBER AS NomineeAADHARNumber FROM MEMPLOYEE emp LEFT JOIN MEMPLOYEENOMINEE empno ON emp.employeeid = empno.employeeid INNER JOIN MORGANIZATIONUNIT ouunit ON emp.WORKOUID = ouunit.OUID WHERE 1 = 1 :dttypefields :empreplacefields """; public const string EmployeeExperienceDetailFormsReport = """ SELECT emp.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, empexp.EMPLOYEEEXPERIENCEID AS EmployeeExperienceId, empexp.Slno AS EmployeeExperienceSlno, empexp.Employer AS EmployeeExperienceEmployer, empexp.City AS EmployeeExperienceCity, empexp.FromMonthYear AS EmployeeExperienceFromMonthYear, empexp.ToMonthYear AS EmployeeExperienceToMonthYear, empexp.NumberOfYear AS EmployeeExperienceNumberOfYear, empexp.Designation AS EmployeeExperienceDesignation, empexp.SalaryDrawn AS EmployeeExperienceSalaryDrawn, empexp.ReasonForLeaving AS EmployeeExperienceReasonForLeaving FROM MEMPLOYEE emp LEFT JOIN MEMPLOYEEEXPERIENCE empexp ON emp.employeeid = empexp.employeeid INNER JOIN MORGANIZATIONUNIT ouunit ON emp.WORKOUID = ouunit.OUID WHERE 1 = 1 :dttypefields :empreplacefields """; // Salary-addon queries (kept short – they are already fairly compact) public const string EmployeeDetailSalaryAddon = """ SELECT a.EMPLOYEEID AS EmployeeId :fields FROM TPAYREVISION a INNER JOIN TPAYREVISIONADDON addon ON a.PAYREVISIONID = addon.PAYREVISIONID INNER JOIN ( SELECT EMPLOYEEID AS EmployeeId, MIN(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION WHERE NATURE = 0 GROUP BY EMPLOYEEID ) b ON a.PAYREVISIONID = b.PAYREVISIONID WHERE 1 = 1 :replacefield """; public const string EmployeeDetailSalaryAddonNew = """ SELECT cc.EMPLOYEEID AS EmployeeId :fields FROM #temp11 cc OUTER APPLY ( SELECT b.EmployeeId, addon.* FROM TPAYREVISION a INNER JOIN TPAYREVISIONADDON addon ON a.PAYREVISIONID = addon.PAYREVISIONID INNER JOIN ( SELECT EMPLOYEEID AS EmployeeId, MIN(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION WHERE NATURE = 0 GROUP BY EMPLOYEEID ) b ON a.PAYREVISIONID = b.PAYREVISIONID WHERE a.EMPLOYEEID = cc.EMPLOYEEID ) addon """; public const string EmployeeDetailSalaryAddonNew1 = """ SELECT addon.PAYREVISIONID AS PAYREVISIONID :fields FROM #temp11 cc INNER JOIN TPAYREVISIONADDON addon ON cc.LATESTPAYREVISIONID = addon.PAYREVISIONID """; } public const string GET_EMPLOYEEPICKLIST_SELECT2 = @"SELECT emp.EmployeeId AS Id, emp.EmployeeCode AS Code, emp.EmployeeName AS Name, emp.PrimaryEMailId, emp.PrimaryMobile, emp.ADHARNUMBER AS AadhaarNumber, CASE WHEN emp.Status = 0 THEN 'Pending' WHEN emp.Status = 1 THEN 'Active' WHEN emp.Status = 2 THEN 'Deleted' WHEN emp.Status = 3 THEN 'Amended' WHEN emp.Status = 4 THEN 'InActive' WHEN emp.Status = 5 THEN 'Archived' ELSE '' END AS EmployeeNewStatus, emp.THUMBNAIL AS ThumbNail, emp.REPORTINGTOEMPLOYEEID AS ReportingToEmployeeId, ISNULL(parent.EmployeeName,'') AS ReportingToEmployeeName, parent.THUMBNAIL AS ParentThumbNail, emp.LOCATIONID AS LocationId, ISNULL(loc.LOCATIONCODE,'') AS LocationCode, ISNULL(loc.LOCATIONNAME,'') AS LocationName, emp.DEPARTMENTID AS DepartmentId, ISNULL(dep.DEPARTMENTCODE,'') AS DepartmentCode, ISNULL(dep.DEPARTMENTNAME,'') AS DepartmentName, emp.DESIGNATIONID AS DesignationId, ISNULL(desg.GCMCODE,'') AS DesignationCode, ISNULL(desg.GCMNAME,'') AS DesignationName, emp.WORKOUID AS WorkOuId, ISNULL(workOu.ORGANIZATIONUNITNAME,'') AS WorkOuName, emp.CURRENTOUID AS CurrentOuId, ISNULL(currentOu.ORGANIZATIONUNITNAME,'') AS CurrentOuName, emp.EMPLOYEESTATUS AS EmployeeStatus, CASE WHEN usr.ISOUTOFOFFICE = 0 THEN 'Yes' ELSE 'No' END AS IsOutOfOffice, usr.OUTOFOFFICEFROMDATE AS OutOfOfficeFromDate, usr.OUTOFOFFICETODATE AS OutOfOfficeToDate FROM MEMPLOYEE emp LEFT JOIN MLOCATION loc ON loc.LOCATIONID = emp.LOCATIONID LEFT JOIN MEMPLOYEE parent ON parent.EMPLOYEEID = emp.REPORTINGTOEMPLOYEEID LEFT JOIN MUSER usr ON usr.USERID = emp.CREATEDBYID LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = emp.DEPARTMENTID LEFT JOIN MGCM desg ON desg.GCMID = emp.DESIGNATIONID LEFT JOIN MORGANIZATIONUNIT workOu ON workOu.OUId = emp.WORKOUID LEFT JOIN MORGANIZATIONUNIT currentOu ON currentOu.OUId = emp.CURRENTOUID WHERE emp.TENANTID = @tenantId"; #region Employee Hierarchy (GB4 EmployeeHierarchyDashboard / HRMSDashboard widget) // GB5 idiom (Menu/MenuGroup precedent): one flat query for the WHOLE tenant, tree built // in C# via FrameworkDAL.CustomCode.Tree.TreeHelper.ConvertToTree — not a recursive CTE. // When @OuId is supplied, employees in that OU are forced to ParentId=-1 (independent // roots) exactly like the legacy recursive-CTE's anchor member did; their real // subordinates (in any OU) are still linked in underneath via the normal ParentId chain // since the full tenant is always fetched. Required covering index: // MEMPLOYEE (TENANTID, EMPLOYEESTATUS) INCLUDE (REPORTINGTOEMPLOYEEID, WORKOUID, DEPARTMENTID, DESIGNATIONID) public const string GET_EMPLOYEE_HIERARCHY = @" SELECT a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, a.WORKOUID AS WorkOuId, CASE WHEN a.WORKOUID IN @OuIds THEN -1 ELSE a.REPORTINGTOEMPLOYEEID END AS ParentId, ISNULL(p.EMPLOYEECODE, '') AS ParentCode, ISNULL(p.EMPLOYEENAME, '') AS ParentName, ISNULL(desg.GCMNAME, '') AS Designation, ISNULL(dep.DEPARTMENTNAME, '') AS Department, ISNULL(a.DEFAULTIMAGEID, '') AS EmployeePhoto, ISNULL(a.THUMBNAIL, '') AS ThumbNail, ISNULL(a.PRIMARYEMAILID, '') AS PrimaryEmailId, ISNULL(a.PRIMARYMOBILE, '') AS PrimaryMobile, ISNULL(a.BLOODGROUP, '') AS BloodGroup, ISNULL(a.FATHERNAME, '') AS FatherName, ISNULL(admn.USERID, -1) AS AdminReportingToId, ISNULL(admn.USERCODE, '') AS AdminReportingToCode, ISNULL(admn.USERNAME, '') AS AdminReportingToName FROM MEMPLOYEE a LEFT JOIN MEMPLOYEE p ON p.EMPLOYEEID = a.REPORTINGTOEMPLOYEEID LEFT JOIN MGCM desg ON desg.GCMID = a.DESIGNATIONID LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = a.DEPARTMENTID LEFT JOIN MUSER admn ON admn.USERID = a.ADMINREPORTINGTOID WHERE a.EMPLOYEESTATUS = 0 AND a.TENANTID = @TenantId"; #endregion #region HRMS Dashboard widgets (GB4 HRMSDashboard) // All widgets share the MEMPLOYEE/MDEPARTMENT/MGCM/MORGANIZATIONUNIT join conventions // already established above. SEX convention assumed 1=Male,2=Female — verify against // live data / MGCM lookup during endpoint verification. public const string GET_HRMS_BREAKUP_SEX_AGE = @" SELECT SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) AS Male, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) AS Female, COUNT(1) AS Total, SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS MalePercent, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS FemalePercent, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) / 12.0 AS AvgTenureYears, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) % 12 AS AvgTenureMonths, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) / 12.0 AS AvgAgeYears, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) % 12 AS AvgAgeMonths FROM MEMPLOYEE a WHERE a.EMPLOYEESTATUS = 0 AND a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId)"; public const string GET_HRMS_DEPARTMENT_WISE_BREAKUP = @" SELECT dep.DEPARTMENTID AS DepartmentId, dep.DEPARTMENTCODE AS DepartmentCode, dep.DEPARTMENTNAME AS DepartmentName, SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) AS Male, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) AS Female, COUNT(1) AS Total, SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS MalePercent, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS FemalePercent, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) / 12.0 AS AvgTenureYears, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) % 12 AS AvgTenureMonths, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) / 12.0 AS AvgAgeYears, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) % 12 AS AvgAgeMonths, COUNT(1) * 100.0 / dbo.FNZERODIV(SUM(COUNT(1)) OVER()) AS SharePercent FROM MEMPLOYEE a JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = a.DEPARTMENTID WHERE a.EMPLOYEESTATUS = 0 AND a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) GROUP BY dep.DEPARTMENTID, dep.DEPARTMENTCODE, dep.DEPARTMENTNAME"; public const string GET_HRMS_DESIGNATION_WISE_BREAKUP = @" SELECT desg.GCMID AS DesignationId, desg.GCMCODE AS DesignationCode, desg.GCMNAME AS Designation, SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) AS Male, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) AS Female, COUNT(1) AS Total, SUM(CASE WHEN a.SEX = 1 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS MalePercent, SUM(CASE WHEN a.SEX = 2 THEN 1 ELSE 0 END) * 100.0 / dbo.FNZERODIV(COUNT(1)) AS FemalePercent, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) / 12.0 AS AvgTenureYears, AVG(DATEDIFF(MONTH, a.DATEOFJOINING, GETDATE())) % 12 AS AvgTenureMonths, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) / 12.0 AS AvgAgeYears, AVG(DATEDIFF(MONTH, a.DATEOFBIRTH, GETDATE())) % 12 AS AvgAgeMonths, COUNT(1) * 100.0 / dbo.FNZERODIV(SUM(COUNT(1)) OVER()) AS SharePercent FROM MEMPLOYEE a JOIN MGCM desg ON desg.GCMID = a.DESIGNATIONID WHERE a.EMPLOYEESTATUS = 0 AND a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId) GROUP BY desg.GCMID, desg.GCMCODE, desg.GCMNAME"; // Last 10 years headcount + YoY change. NumberTally.N supplies 0..N-1 (0-9 covers 10 years). public const string GET_HRMS_HEADCOUNT_BY_YEAR = @" WITH Years AS ( SELECT YEAR(GETDATE()) - t.N AS Yr FROM NumberTally t WHERE t.N BETWEEN 0 AND 9 ), YearCounts AS ( SELECT y.Yr AS Year, (SELECT COUNT(1) FROM MEMPLOYEE a WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) AND a.DATEOFJOINING <= DATEFROMPARTS(y.Yr, 12, 31) AND (a.EMPLOYEESTATUS = 0 OR a.DATEOFRESIGNATION > DATEFROMPARTS(y.Yr, 12, 31)) ) AS YearCount FROM Years y ) SELECT Year, YearCount, YearCount - LAG(YearCount) OVER (ORDER BY Year) AS Change, (YearCount - LAG(YearCount) OVER (ORDER BY Year)) * 100.0 / dbo.FNZERODIV(LAG(YearCount) OVER (ORDER BY Year)) AS ChangePercent FROM YearCounts ORDER BY Year"; // Month/Quarter/Year Open-Joined-Left-Close + attrition, as of today. public const string GET_HRMS_HEADCOUNT = @" DECLARE @Today DATE = CAST(GETDATE() AS DATE); DECLARE @MonthStart DATE = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1); DECLARE @QuarterStart DATE = DATEFROMPARTS(YEAR(@Today), (DATEPART(QUARTER, @Today) - 1) * 3 + 1, 1); DECLARE @YearStart DATE = DATEFROMPARTS(YEAR(@Today), 1, 1); ;WITH Base AS ( SELECT a.DATEOFJOINING, a.DATEOFRESIGNATION, a.EMPLOYEESTATUS FROM MEMPLOYEE a WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) ) SELECT (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @MonthStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @MonthStart)) AS MonthOpen, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING >= @MonthStart AND DATEOFJOINING <= @Today) AS MonthJoined, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @MonthStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) AS MonthLeft, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING <= @Today AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION > @Today)) AS MonthClose, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @MonthStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) * 100.0 / dbo.FNZERODIV((SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @MonthStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @MonthStart))) AS MonthlyAttrition, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @QuarterStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @QuarterStart)) AS QuarterOpen, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING >= @QuarterStart AND DATEOFJOINING <= @Today) AS QuarterJoined, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @QuarterStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) AS QuarterLeft, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING <= @Today AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION > @Today)) AS QuarterClose, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @QuarterStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) * 100.0 / dbo.FNZERODIV((SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @QuarterStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @QuarterStart))) AS QuarterAttrition, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @YearStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @YearStart)) AS YearOpen, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING >= @YearStart AND DATEOFJOINING <= @Today) AS YearJoined, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @YearStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) AS YearLeft, (SELECT COUNT(1) FROM Base WHERE DATEOFJOINING <= @Today AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION > @Today)) AS YearClose, (SELECT COUNT(1) FROM Base WHERE DATEOFRESIGNATION >= @YearStart AND DATEOFRESIGNATION <= @Today AND EMPLOYEESTATUS <> 0) * 100.0 / dbo.FNZERODIV((SELECT COUNT(1) FROM Base WHERE DATEOFJOINING < @YearStart AND (EMPLOYEESTATUS = 0 OR DATEOFRESIGNATION >= @YearStart))) AS YearAttrition"; // One aggregate row over TMONTHLYATTENDANCE for the requested period. public const string GET_HRMS_ATTENDANCE_SUMMARY = @" SELECT 'Overall' AS Particulars, SUM(m.PRESENTDAYS + m.ABSENT + m.LEAVEDAYS + m.WEEKLYOFF + m.HOLIDAYS) AS Total, SUM(m.WORKEDDAYS) AS Worked, SUM(m.ABSENT) AS Absent, SUM(m.LEAVEDAYS) AS Leave, SUM(m.WEEKLYOFF) AS WOff, SUM(m.HOLIDAYS) AS Holiday, SUM(m.COMPWORK) AS CompWork, SUM(m.COMPOFF) AS CompLeave, 0 AS Others, CAST(SUM(m.NUMBEROFOT) AS INT) AS OTNumber, CAST(SUM(m.NUMBEROFLATEIN) AS INT) AS LateInNumber, CAST(SUM(m.NUMBEROFEARLYOUT) AS INT) AS EarlyOutNumber, CAST(SUM(m.OTHOURS) AS INT) AS TotalOTHours, CAST(SUM(m.LATEIN) AS INT) AS TotalLateInHours, CAST(SUM(m.EARLYOUT) AS INT) AS TotalEarlyOutHours, SUM(m.OTFACTORHOURS) AS TotalOTFactorHours FROM TMONTHLYATTENDANCE m JOIN MEMPLOYEE a ON a.EMPLOYEEID = m.EMPLOYEEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR m.OUID = @OuId) AND (@PeriodFromDate IS NULL OR m.ATTENDANCEFROMDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR m.ATTENDANCETODATE <= @PeriodToDate)"; public const string GET_HRMS_ABSENTISM_SUMMARY = @" SELECT 'Overall' AS Particulars, SUM(m.PRESENTDAYS + m.ABSENT + m.LEAVEDAYS) AS TotalPlanned, SUM(m.WORKEDDAYS) AS Worked, SUM(m.ABSENT) AS Absent, SUM(m.LEAVEDAYS) AS Leave, SUM(m.LEAVEDAYS) * 100.0 / dbo.FNZERODIV(SUM(m.PRESENTDAYS + m.ABSENT + m.LEAVEDAYS)) AS LeavePercent, SUM(m.ABSENT) * 100.0 / dbo.FNZERODIV(SUM(m.PRESENTDAYS + m.ABSENT + m.LEAVEDAYS)) AS AbsentPercent, SUM(m.ABSENT + m.LEAVEDAYS) * 100.0 / dbo.FNZERODIV(SUM(m.PRESENTDAYS + m.ABSENT + m.LEAVEDAYS)) AS LeaveAbsentPercent FROM TMONTHLYATTENDANCE m JOIN MEMPLOYEE a ON a.EMPLOYEEID = m.EMPLOYEEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR m.OUID = @OuId) AND (@PeriodFromDate IS NULL OR m.ATTENDANCEFROMDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR m.ATTENDANCETODATE <= @PeriodToDate)"; // NumberConversion: 0=Lakh (1e5), 1=Million (1e6), 2=Crore (1e7) — matches legacy toggle. public const string GET_HRMS_PAY_COST_BY_MONTH = @" SELECT s.YEAR AS Year, s.MONTH AS Month, SUM(s.C2C) AS C2C, COUNT(DISTINCT s.EMPLOYEEID) AS HeadCount, CAST(SUM(s.C2C) / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END) AS VARCHAR(50)) AS C2CValue FROM MEMPLOYEESALARY s JOIN MEMPLOYEE a ON a.EMPLOYEEID = s.EMPLOYEEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) AND (@PeriodFromDate IS NULL OR s.FROMDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR s.TODATE <= @PeriodToDate) GROUP BY s.YEAR, s.MONTH ORDER BY s.YEAR, s.MONTH"; public const string GET_HRMS_YEARLY_SALARY_COST = @" SELECT s.YEAR AS Year, SUM(s.C2C) AS C2C, COUNT(DISTINCT s.EMPLOYEEID) AS HeadCount, CAST(SUM(s.C2C) / (CASE @NumberConversion WHEN 1 THEN 1000000.0 WHEN 2 THEN 10000000.0 ELSE 100000.0 END) AS VARCHAR(50)) AS C2CValue FROM MEMPLOYEESALARY s JOIN MEMPLOYEE a ON a.EMPLOYEEID = s.EMPLOYEEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR a.WORKOUID = @OuId) AND (@PeriodFromDate IS NULL OR s.FROMDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR s.TODATE <= @PeriodToDate) GROUP BY s.YEAR ORDER BY s.YEAR"; public const string GET_HRMS_ATTENDANCE_SHIFT_SUMMARY = @" SELECT sh.SHIFTID AS ShiftId, sh.SHIFTCODE AS ShiftCode, sh.SHIFTDESCRIPTION AS ShiftDescription, COUNT(DISTINCT d.EMPLOYEEID) AS NoofEmployee, CONVERT(VARCHAR(10), @PeriodFromDate, 103) AS FromDate, CONVERT(VARCHAR(10), @PeriodToDate, 103) AS ToDate, COUNT(DISTINCT CASE WHEN a.SEX = 1 THEN d.EMPLOYEEID END) AS MaleCount, COUNT(DISTINCT CASE WHEN a.SEX = 2 THEN d.EMPLOYEEID END) AS FemaleCount FROM TDAILYATTENDANCE d JOIN MSHIFT sh ON sh.SHIFTID = d.SHIFTID JOIN MEMPLOYEE a ON a.EMPLOYEEID = d.EMPLOYEEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR d.OUID = @OuId) AND d.ATTENDANCEDATE >= @PeriodFromDate AND d.ATTENDANCEDATE <= @PeriodToDate GROUP BY sh.SHIFTID, sh.SHIFTCODE, sh.SHIFTDESCRIPTION"; // Rows where the employee's shift actually changed vs the previous recorded shift. public const string GET_HRMS_DAILY_SHIFT_CHANGE = @" SELECT d.DAILYATTENDANCEID AS DailyAttendanceId, d.ATTENDANCEDATE AS DailyAttendanceAttendanceDate, d.DAYTYPE AS DailyAttendanceDayType, ISNULL(d.WORKTYPEID, -1) AS WorkTypeId, ISNULL(wt.GCMCODE, '') AS WorkTypeCode, ISNULL(wt.GCMNAME, '') AS WorkTypeName, a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, CASE a.SEX WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' ELSE '' END AS SexName, a.SEX AS EmployeeSex, a.SORTORDER AS SortOrder, d.SHIFTID AS ShiftId, ISNULL(sh.SHIFTCODE, '') AS ShiftCode, ISNULL(sh.SHIFTDESCRIPTION, '') AS ShiftDescription, ISNULL(psh.SHIFTCODE, '') AS PreviousShiftCode, ISNULL(psh.SHIFTDESCRIPTION, '') AS PreviousShiftDescription, ISNULL(d.LOCATION, '') AS DailyAttendanceLocation, a.DEPARTMENTID AS DepartmentId, ISNULL(dep.DEPARTMENTCODE, '') AS DepartmentCode, ISNULL(dep.DEPARTMENTNAME, '') AS DepartmentName, a.DESIGNATIONID AS DesignationId, ISNULL(desg.GCMCODE, '') AS DesignationCode, ISNULL(desg.GCMNAME, '') AS DesignationName, ISNULL(d.TEXTDAYTYPE, '') AS DayType, ISNULL(d.TEXTDAYTYPE, '') AS DailyAttendanceTextDayType, ISNULL(d.INTIME, 0) AS DailyAttendanceInTime, ISNULL(d.OUTTIME, 0) AS DailyAttendanceOutTime, ISNULL(d.WORKEDHOURS, 0) AS DailyAttendanceWorkedHours, ISNULL(d.REMARKS, '') AS DailyAttendanceRemarks FROM TDAILYATTENDANCE d JOIN MEMPLOYEE a ON a.EMPLOYEEID = d.EMPLOYEEID LEFT JOIN MSHIFT sh ON sh.SHIFTID = d.SHIFTID LEFT JOIN MSHIFT psh ON psh.SHIFTID = d.PREVIOUSSHIFTID LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = a.DEPARTMENTID LEFT JOIN MGCM desg ON desg.GCMID = a.DESIGNATIONID LEFT JOIN MGCM wt ON wt.GCMID = d.WORKTYPEID WHERE a.TENANTID = @TenantId AND (@OuId IS NULL OR d.OUID = @OuId) AND d.ATTENDANCEDATE >= @PeriodFromDate AND d.ATTENDANCEDATE <= @PeriodToDate AND d.PREVIOUSSHIFTID IS NOT NULL AND d.PREVIOUSSHIFTID <> d.SHIFTID"; #endregion #region Employee Profile Report (GB4 ProfileReport) — master + 6 in-scope sub-reports // First-migration scope covers master fields + Salary/Skill/Working/Experience/ // Qualification/Reference. ESI/PF/Gratuity/Appointment/BioData/Form25/Induction are // deferred (legacy delegated those to a separate large GetEmployeesDetailFormReport // method not ported in this pass). Master query has NO OFFSET/FETCH here — the DAL // appends paging + builds the COUNT(*) query off this same base text, same manual // pattern as GetSelectListEmployee/BuildEmployeePicklistQueryAsync above (QueryPagedAsync // can't be used here: it wraps the exact sql text — including any OFFSET/FETCH — in the // COUNT(*) subquery, which would report the page size as the total, not the true count). public const string GET_EMPLOYEE_PROFILE_REPORT = @" SELECT a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, ISNULL(a.FATHERNAME, '') AS EmployeeFatherName, ISNULL(a.MOTHERNAME, '') AS EmployeeMotherName, ISNULL(a.SPOUSENAME, '') AS EmployeeSpouseName, a.DEPARTMENTID AS DepartmentId, ISNULL(dep.DEPARTMENTCODE, '') AS DepartmentCode, ISNULL(dep.DEPARTMENTNAME, '') AS DepartmentName, a.COSTCENTERTYPE AS EmployeeCostCenterType, a.COSTCENTERID AS CostCenterId, ISNULL(cc.COSTCENTERCODE, '') AS CostCenterCode, ISNULL(cc.COSTCENTERNAME, '') AS CostCenterName, a.WORKOUID AS WorkOuId, ISNULL(ou.ORGANIZATIONUNITCODE, '') AS WorkOuCode, ISNULL(ou.ORGANIZATIONUNITNAME, '') AS WorkOuName, a.ISCROSSOU AS EmployeeIsCrossOu, a.PAYGROUPID AS PayGroupId, ISNULL(pg.PAYGROUPCODE, '') AS PayGroupCode, ISNULL(pg.PAYGROUPNAME, '') AS PayGroupName, a.WORKTYPEID AS WorkTypeId, ISNULL(wt.GCMCODE, '') AS WorkTypeCode, ISNULL(wt.GCMNAME, '') AS WorkTypeName, a.EMPLOYEETYPEID AS EmployeeTypeId, ISNULL(et.GCMCODE, '') AS EmployeeTypeCode, ISNULL(et.GCMNAME, '') AS EmployeeTypeName, a.STAFFTYPEID AS StaffTypeId, ISNULL(st.GCMCODE, '') AS StaffTypeCode, ISNULL(st.GCMNAME, '') AS StaffTypeName, a.DESIGNATIONID AS DesignationId, ISNULL(desg.GCMCODE, '') AS DesignationCode, ISNULL(desg.GCMNAME, '') AS DesignationName, a.LOCATIONID AS LocationId, ISNULL(loc.LOCATIONCODE, '') AS LocationCode, ISNULL(loc.LOCATIONNAME, '') AS LocationName, a.SALARYGRADEID AS SalaryGradeId, ISNULL(sg.GCMCODE, '') AS SalaryGradeCode, ISNULL(sg.GCMNAME, '') AS SalaryGradeName, a.JOBPOSITIONID AS JobPositionId, ISNULL(jp.JOBPOSITIONCODE, '') AS JobPositionCode, ISNULL(jp.JOBPOSITIONNAME, '') AS JobPositionName, a.EMPLOYEEBANKBRANCHID AS EmployeeBankBranchId, ISNULL(bb.BANKBRANCHNAME, '') AS EmployeeBankBranchName, a.DATEOFBIRTH AS DateOfBirth, a.DATEOFJOINING AS DateOfJoining, a.DATEOFCONFIRMATION AS DateOfConfirmation, a.DATEOFRESIGNATION AS DateOfResignation, a.EMPLOYEESTATUS AS EmployeeStatus, a.SEX AS EmployeeSex, a.THUMBNAIL AS EmployeeThumbnail, ISNULL(a.BLOODGROUP, '') AS BloodGroup, ISNULL(a.PRIMARYEMAILID, '') AS EmployeePrimaryEMailId, ISNULL(a.PRIMARYMOBILE, '') AS EmployeePrimaryMobile, ISNULL(a.PANNUMBER, '') AS PANNumber, ISNULL(a.ADHARNUMBER, '') AS AadharNumber, ISNULL(a.PFNUMBER, '') AS PFNumber, ISNULL(a.PFUANNUMBER, '') AS PFUANNumber, ISNULL(a.ESINUMBER, '') AS ESINumber, a.REPORTINGTOEMPLOYEEID AS ReportingToEmployeeId, a.ADMINREPORTINGTOID AS AdminReportingToId FROM MEMPLOYEE a LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = a.DEPARTMENTID LEFT JOIN MCOSTCENTER cc ON cc.COSTCENTERID = a.COSTCENTERID LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID = a.WORKOUID LEFT JOIN MPAYGROUP pg ON pg.PAYGROUPID = a.PAYGROUPID LEFT JOIN MGCM wt ON wt.GCMID = a.WORKTYPEID LEFT JOIN MGCM et ON et.GCMID = a.EMPLOYEETYPEID LEFT JOIN MGCM st ON st.GCMID = a.STAFFTYPEID LEFT JOIN MGCM desg ON desg.GCMID = a.DESIGNATIONID LEFT JOIN MLOCATION loc ON loc.LOCATIONID = a.LOCATIONID LEFT JOIN MGCM sg ON sg.GCMID = a.SALARYGRADEID LEFT JOIN MJOBPOSITION jp ON jp.JOBPOSITIONID = a.JOBPOSITIONID LEFT JOIN MBANKBRANCH bb ON bb.BANKBRANCHID = a.EMPLOYEEBANKBRANCHID WHERE a.TENANTID = @TenantId AND (@EmployeeId IS NULL OR a.EMPLOYEEID = @EmployeeId) AND (@EmployeeStatus IS NULL OR a.EMPLOYEESTATUS = @EmployeeStatus)"; public const string GET_EMPLOYEE_PROFILE_SALARY = @" SELECT s.EMPLOYEESALARYID AS EmployeeSalaryId, s.EMPLOYEEID AS EmployeeId, s.TYPE AS EmployeeSalaryType, s.MONTH AS EmployeeSalaryMonth, s.YEAR AS EmployeeSalaryYear, s.FROMDATE AS EmployeeSalaryFromDate, s.TODATE AS EmployeeSalaryToDate, s.TOTALEARNINGS AS EmployeeSalaryTotalEarnings, s.TOTALDEDUCTION AS EmployeeSalaryTotalDeduction, s.NETPAY AS EmployeeSalaryNetPay, s.C2C AS EmployeeSalaryC2C, s.GRATUITYDAYS AS EmployeeSalaryGratuityDays, s.BONUSONAMOUNT AS EmployeeSalaryBonusOnAmount FROM MEMPLOYEESALARY s WHERE s.EMPLOYEEID IN @EmployeeIds"; public const string GET_EMPLOYEE_PROFILE_SKILL = @" SELECT sk.EMPLOYEESKILLID AS EmployeeSkillId, sk.EMPLOYEEID AS EmployeeId, sk.SKILLID AS SkillId, ISNULL(g.GCMCODE,'') AS SkillCode, ISNULL(g.GCMNAME,'') AS SkillName, sk.SKILLLEVEL AS EmployeeSkillSkillLevel, ISNULL(sk.PARTICULARS,'') AS EmployeeSkillParticulars FROM MEMPLOYEESKILL sk LEFT JOIN MGCM g ON g.GCMID = sk.SKILLID WHERE sk.EMPLOYEEID IN @EmployeeIds"; public const string GET_EMPLOYEE_PROFILE_WORKING = @" SELECT w.EMPLOYEEWORKINGID AS EmployeeWorkingId, w.EMPLOYEEID AS EmployeeId, w.DATEOFJOINING AS DateOfJoining, w.DATEOFRESIGNATION AS DateOfResignation, w.PROBATIONSTARTDATE AS ProbationStartDate, w.FROMDATE AS EmployeeWorkingFromDate, w.TODATE AS EmployeeWorkingToDate, w.DEPARTMENTID AS DepartmentId, ISNULL(dep.DEPARTMENTCODE,'') AS DepartmentCode, ISNULL(dep.DEPARTMENTNAME,'') AS DepartmentName, w.DESIGNATIONID AS DesignationId, ISNULL(desg.GCMCODE,'') AS DesignationCode, ISNULL(desg.GCMNAME,'') AS DesignationName, w.LOCATIONID AS LocationId, ISNULL(loc.LOCATIONCODE,'') AS LocationCode, ISNULL(loc.LOCATIONNAME,'') AS LocationName, w.SALARYGRADEID AS GradeId, ISNULL(sg.GCMCODE,'') AS GradeCode, ISNULL(sg.GCMNAME,'') AS GradeName, w.REPORTINGTOEMPLOYEEID AS ReportingToId, ISNULL(rp.EMPLOYEECODE,'') AS ReportingToCode, ISNULL(rp.EMPLOYEENAME,'') AS ReportingToName, w.RESOURCETYPEID AS ResourceTypeId, ISNULL(rt.GCMCODE,'') AS ResourceTypeCode, ISNULL(rt.GCMNAME,'') AS ResourceTypeName, w.EMPLOYEETYPEID AS EmployeeTypeId, ISNULL(et.GCMCODE,'') AS EmployeeTypeCode, ISNULL(et.GCMNAME,'') AS EmployeeTypeName, w.STAFFTYPEID AS StaffTypeId, ISNULL(st.GCMCODE,'') AS StaffTypeCode, ISNULL(st.GCMNAME,'') AS StaffTypeName, -1 AS EmployeeGroupId, '' AS EmployeeGroupCode, '' AS EmployeeGroupName, w.WORKTYPEID AS WorkTypeId, ISNULL(wt.GCMCODE,'') AS WorkTypeCode, ISNULL(wt.GCMNAME,'') AS WorkTypeName, w.SEGMENTID AS SegmentId, ISNULL(seg.GCMCODE,'') AS SegmentCode, ISNULL(seg.GCMNAME,'') AS SegmentName, w.UNITID AS UnitId, '' AS UnitCode, '' AS UnitName, w.WORKOUID AS OUId, ISNULL(ou.ORGANIZATIONUNITCODE,'') AS OUCode, ISNULL(ou.ORGANIZATIONUNITNAME,'') AS OUName, w.ISPOSTINGREQUIRED AS EmployeeWorkingIsPostingRequired, w.DATEOFJOINING AS EmployeeWorkingDateOfJoining, w.DATEOFRESIGNATION AS EmployeeWorkingDateOfResignation FROM MEMPLOYEEWORKING w LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = w.DEPARTMENTID LEFT JOIN MGCM desg ON desg.GCMID = w.DESIGNATIONID LEFT JOIN MLOCATION loc ON loc.LOCATIONID = w.LOCATIONID LEFT JOIN MGCM sg ON sg.GCMID = w.SALARYGRADEID LEFT JOIN MEMPLOYEE rp ON rp.EMPLOYEEID = w.REPORTINGTOEMPLOYEEID LEFT JOIN MGCM rt ON rt.GCMID = w.RESOURCETYPEID LEFT JOIN MGCM et ON et.GCMID = w.EMPLOYEETYPEID LEFT JOIN MGCM st ON st.GCMID = w.STAFFTYPEID LEFT JOIN MGCM wt ON wt.GCMID = w.WORKTYPEID LEFT JOIN MGCM seg ON seg.GCMID = w.SEGMENTID LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID = w.WORKOUID WHERE w.EMPLOYEEID IN @EmployeeIds"; public const string GET_EMPLOYEE_PROFILE_EXPERIENCE = @" SELECT ex.EMPLOYEEEXPERIENCEID AS EmployeeExperienceId, ex.EMPLOYEEID AS EmployeeId, ISNULL(ex.EMPLOYER,'') AS EmployeeExperienceEmployer, ISNULL(ex.CITY,'') AS EmployeeExperienceCity, ISNULL(ex.FROMMONTHYEAR,'') AS EmployeeExperienceFromMonthYear, ISNULL(ex.TOMONTHYEAR,'') AS EmployeeExperienceToMonthYear, ex.NUMBEROFYEAR AS EmployeeExperienceNumberOfYear, ISNULL(ex.DESIGNATION,'') AS EmployeeExperienceDesignation, ex.SALARYDRAWN AS EmployeeExperienceSalaryDrawn, ISNULL(ex.REASONFORLEAVING,'') AS EmployeeExperienceReasonForLeaving FROM MEMPLOYEEEXPERIENCE ex WHERE ex.EMPLOYEEID IN @EmployeeIds"; public const string GET_EMPLOYEE_PROFILE_QUALIFICATION = @" SELECT q.EMPLOYEEQUALIFICATIONID AS EmployeeQualificationId, q.EMPLOYEEID AS EmployeeId, ISNULL(q.COURSE,'') AS EmployeeQualificationCourse, ISNULL(q.INSTITUTE,'') AS EmployeeQualificationInstitute, ISNULL(q.DURATION,'') AS EmployeeQualificationDuration, ISNULL(q.FROMYEAR,'') AS EmployeeQualificationFromYear, q.TOYEAR AS EmployeeQualificationToYear, ISNULL(q.GRADE,'') AS EmployeeQualificationGrade, ISNULL(q.REMARKS,'') AS EmployeeQualificationRemarks FROM MEMPLOYEEQUALIFICATION q WHERE q.EMPLOYEEID IN @EmployeeIds"; public const string GET_EMPLOYEE_PROFILE_REFERENCE = @" SELECT r.EMPLOYEEREFERENCEID AS EmployeeReferenceId, r.EMPLOYEEID AS EmployeeId, ISNULL(r.REFERENCE,'') AS EmployeeReferenceReference, ISNULL(r.REFERENCECOMPANY,'') AS EmployeeReferenceReferenceCompany, ISNULL(r.DESIGNATION,'') AS EmployeeReferenceDesignation, ISNULL(r.RELATIONSHIP,'') AS EmployeeReferenceRelationship, ISNULL(r.TIMEKNOWN,'') AS EmployeeReferenceTimeKnown, r.ISVERIFIED AS EmployeeReferenceIsVerified, r.VERIFIEDBYID AS VerifiedById, ISNULL(v.USERCODE,'') AS VerifiedByCode, ISNULL(v.USERNAME,'') AS VerifiedByName, r.VERIFIEDON AS EmployeeReferenceVerifiedOn, ISNULL(r.REMARKS,'') AS EmployeeReferenceRemarks FROM MEMPLOYEEREFERENCE r LEFT JOIN MUSER v ON v.USERID = r.VERIFIEDBYID WHERE r.EMPLOYEEID IN @EmployeeIds"; #endregion // ───────────────────────────────────────────────────────────────── // EmployeePostingSelectionList — migrated from GB4 // EmployeeQueryBuilder.GET_EMPLOYEE_POSTING_SELECTION / // GET_EMPLOYEE_POSTING_SELECTION_FORMM. // // GB4 built the WHERE clause by string-replacing raw values (including // user-supplied SearchQuery) directly into SQL text — a SQL-injection // shaped anti-pattern. Both variants below are fully parameterized. // // GB4's optional ":hitchstatus" clause (present only when IsDrillDown=1) // is expressed here as a single always-present filter driven by // @IsDrillDown, since the DAL can no longer freely splice raw SQL text. // // CrewTimesheetActivitiHistoryDTOs enrichment (GB4's drilldown=0 branch, // sourced from MonthlyAttendanceQueryBuilder.GET_ACTIVITI_HISTORY_DETAIL_ // FOR_TIMESHEET_BULK) is NOT ported — GB5's EmployeePostingSelectListDTO // already carries that property commented out, matching this gap. // ───────────────────────────────────────────────────────────────── public const string GET_EMPLOYEE_POSTING_SELECTION = @" SELECT a.POSTINGPOSITIONID AS PostingPositionId, a.REFERENCEID AS PostingReferenceId, CASE a.POSTINGSTATUS WHEN 0 THEN 'Planned' WHEN 1 THEN 'Approved' WHEN 2 THEN 'In Progress' WHEN 3 THEN 'Closed' WHEN 4 THEN 'Hold' WHEN 5 THEN 'Drop' END AS PostingPositionStatus, a.MMDETAILID AS MMDetailId, f.PARTYID AS PartyId, f.PARTYCODE AS PartyCode, f.PARTYNAME AS PartyName, g.PARTYBRANCHID AS PartyBranchId, g.PARTYBRANCHCODE AS PartyBranchCode, g.PARTYBRANCHNAME AS PartyBranchName, e.GCMID AS WorkTypeId, e.GCMCODE AS WorkTypeCode, e.GCMNAME AS WorkTypeName, a.HITCHSLNO AS HitchSlNo, a.FROMDATE AS HitchStart, a.TODATE AS HitchEnd, a.REMARKS AS Remarks, c.OUID AS OUId, mou.ORGANIZATIONUNITCODE AS OUCode, (CASE WHEN a.FROMDATE >= @PeriodFromDate THEN a.FROMDATE ELSE @PeriodFromDate END) AS DRFrom, (CASE WHEN a.TODATE >= @PeriodToDate THEN @PeriodToDate ELSE a.TODATE END) AS DRTo, c.DOCUMENTNUMBER AS DocumentNumber, c.REFERENCENUMBER AS ReferenceNumber, c.DOCUMENTDATE AS DocumentDate, pbaddcountry.COUNTRYID AS CountryId, pbaddcountry.COUNTRYNAME AS CountryName, employee.EMPLOYEEID AS EmployeeId, employee.EMPLOYEECODE AS EmployeeCode, employee.EMPLOYEENAME AS EmployeeName, shiftpatt.SHIFTPATTERNID AS RotationId, shiftpatt.SHIFTPATTERNNAME AS RotationName, allocation.ALLOCATIONID AS ProjectId, allocation.DESCRIPTION AS ProjectName, (CASE WHEN CAST(GETDATE() AS DATE) BETWEEN a.FROMDATE AND a.TODATE THEN 'CURRENT' WHEN a.TODATE > CAST(GETDATE() AS DATE) THEN 'FUTURE' ELSE 'PREVIOUS' END) AS HitchStatus, ISNULL(timesheet.TimeSheetId, -1) AS TimeSheetId, ISNULL(monthlystatus.Status, 0) AS TimeSheetStatus FROM TPOSTINGPOSITION a OUTER APPLY ( SELECT TOP (1) dailyatt.TIMESHEETID AS TimeSheetId FROM TDAILYATTENDANCE dailyatt WHERE dailyatt.EMPLOYEEID = a.EMPLOYEEID AND dailyatt.MMDETAILID = a.MMDETAILID AND dailyatt.POSTINGPOSITIONID = a.POSTINGPOSITIONID AND dailyatt.ATTENDANCEDATE >= (CASE WHEN a.FROMDATE >= @PeriodFromDate THEN a.FROMDATE ELSE @PeriodFromDate END) AND dailyatt.ATTENDANCEDATE <= (CASE WHEN a.TODATE >= @PeriodToDate THEN @PeriodToDate ELSE a.TODATE END) ) timesheet OUTER APPLY ( SELECT TOP (1) monthly.STATUS AS Status FROM TMONTHLYATTENDANCE monthly WHERE monthly.TIMESHEETID = ISNULL(timesheet.TimeSheetId, -1) ) monthlystatus INNER JOIN TMMDETAIL b ON a.MMDETAILID = b.DOCUMENTDETAILID INNER JOIN TMMHEAD c ON b.DOCUMENTID = c.DOCUMENTID INNER JOIN MITEM d ON b.ITEMID = d.ITEMID INNER JOIN MGCM e ON d.ITEMCODE = e.GCMCODE AND e.GCMTYPEID = -1399999995 INNER JOIN MPARTY f ON c.PARTYID = f.PARTYID INNER JOIN MPARTYBRANCH g ON c.PARTYBRANCHID = g.PARTYBRANCHID INNER JOIN MADDRESS pbadd ON g.DEFAULTADDRESSID = pbadd.ADDRESSID INNER JOIN MCOUNTRY pbaddcountry ON pbadd.COUNTRYID = pbaddcountry.COUNTRYID INNER JOIN MEMPLOYEE employee ON a.EMPLOYEEID = employee.EMPLOYEEID INNER JOIN MSHIFTPATTERN shiftpatt ON a.SHIFTPATTERNID = shiftpatt.SHIFTPATTERNID INNER JOIN MALLOCATION allocation ON b.ALLOCATIONID = allocation.ALLOCATIONID INNER JOIN MORGANIZATIONUNIT mou ON mou.OUID = c.OUID WHERE (@OUId = -1 OR c.OUID = @OUId) AND a.EMPLOYEEID = @EmployeeId AND a.TODATE >= @PeriodFromDate AND a.FROMDATE <= @PeriodToDate AND employee.TENANTID = @TenantId AND ( @IsDrillDown = 0 OR ( a.POSTINGSTATUS <> 4 AND (CASE WHEN a.POSTINGSTATUS = 5 THEN ISNULL(timesheet.TimeSheetId, -1) ELSE -1900000000 END) <> -1 ) ) AND (@PartyId is null OR f.PARTYID = @PartyId) AND (@PartyCode IS NULL OR f.PARTYCODE = @PartyCode) AND (@PartyName IS NULL OR f.PARTYNAME = @PartyName) AND (@PostingPositionId IS NULL OR a.POSTINGPOSITIONID = @PostingPositionId) AND (@MMDId IS NULL OR b.DOCUMENTDETAILID = @MMDId) AND ( @SearchQuery = '' OR f.PARTYNAME LIKE '%' + @SearchQuery + '%' OR g.PARTYBRANCHNAME LIKE '%' + @SearchQuery + '%' OR e.GCMNAME LIKE '%' + @SearchQuery + '%' OR pbaddcountry.COUNTRYNAME LIKE '%' + @SearchQuery + '%' OR CAST(YEAR(CASE WHEN a.FROMDATE >= @PeriodFromDate THEN a.FROMDATE ELSE @PeriodFromDate END) AS NVARCHAR(4)) LIKE '%' + @SearchQuery + '%' OR CAST(YEAR(CASE WHEN a.TODATE >= @PeriodToDate THEN @PeriodToDate ELSE a.TODATE END) AS NVARCHAR(4)) LIKE '%' + @SearchQuery + '%' ) ORDER BY a.FROMDATE DESC"; // Narrow date-range-only PayPeriod lookup for EmployeePostingSelectionList. // Not routed through IPayPeriodDAL.GetPayPeriod — PayPeriodDAL already depends // on IEmployeeDAL, so that call would create a DI cycle. public const string GET_PAYPERIOD_DATE_RANGE = @" SELECT PP.FROMDATE AS FromDate, PP.TODATE AS ToDate FROM PAYPERIOD PP WHERE PP.PAYPERIODID = @PayPeriodId"; // GB4's "FromInventory != -1" variant — simplified/distinct, no daily-attendance // or timesheet joins, hardcoded POSTINGSTATUS exclusion of Hold(4)/Drop(5). public const string GET_EMPLOYEE_POSTING_SELECTION_FORMM = @" SELECT DISTINCT a.MMDETAILID AS MMDetailId, f.PARTYID AS PartyId, f.PARTYCODE AS PartyCode, f.PARTYNAME AS PartyName, g.PARTYBRANCHID AS PartyBranchId, g.PARTYBRANCHCODE AS PartyBranchCode, g.PARTYBRANCHNAME AS PartyBranchName, e.GCMID AS WorkTypeId, e.GCMCODE AS WorkTypeCode, e.GCMNAME AS WorkTypeName, c.DOCUMENTNUMBER AS DocumentNumber, c.REFERENCENUMBER AS ReferenceNumber, pbaddcountry.COUNTRYID AS CountryId, pbaddcountry.COUNTRYNAME AS CountryName, employee.EMPLOYEEID AS EmployeeId, employee.EMPLOYEECODE AS EmployeeCode, employee.EMPLOYEENAME AS EmployeeName, shiftpatt.SHIFTPATTERNID AS RotationId, shiftpatt.SHIFTPATTERNNAME AS RotationName, allocation.ALLOCATIONID AS ProjectId, allocation.ALLOCATIONNAME AS ProjectName FROM TPOSTINGPOSITION a INNER JOIN TMMDETAIL b ON a.MMDETAILID = b.DOCUMENTDETAILID INNER JOIN TMMHEAD c ON b.DOCUMENTID = c.DOCUMENTID INNER JOIN MITEM d ON b.ITEMID = d.ITEMID INNER JOIN MGCM e ON d.ITEMCODE = e.GCMCODE AND e.GCMTYPEID = -1399999995 INNER JOIN MPARTY f ON c.PARTYID = f.PARTYID INNER JOIN MPARTYBRANCH g ON c.PARTYBRANCHID = g.PARTYBRANCHID INNER JOIN MADDRESS pbadd ON g.DEFAULTADDRESSID = pbadd.ADDRESSID INNER JOIN MCOUNTRY pbaddcountry ON pbadd.COUNTRYID = pbaddcountry.COUNTRYID INNER JOIN MEMPLOYEE employee ON a.EMPLOYEEID = employee.EMPLOYEEID INNER JOIN MSHIFTPATTERN shiftpatt ON a.SHIFTPATTERNID = shiftpatt.SHIFTPATTERNID INNER JOIN MALLOCATION allocation ON b.ALLOCATIONID = allocation.ALLOCATIONID WHERE (@OUId = -1 OR c.OUID = @OUId) AND a.EMPLOYEEID = @EmployeeId AND a.POSTINGSTATUS NOT IN (4, 5) AND employee.TENANTID = @TenantId AND (@PartyId is null OR f.PARTYID = @PartyId) AND (@PartyCode IS NULL OR f.PARTYCODE = @PartyCode) AND (@PartyName IS NULL OR f.PARTYNAME = @PartyName) AND (@PostingPositionId IS NULL OR a.POSTINGPOSITIONID = @PostingPositionId) AND (@MMDId IS NULL OR b.DOCUMENTDETAILID = @MMDId) AND ( @SearchQuery = '' OR f.PARTYNAME LIKE '%' + @SearchQuery + '%' OR g.PARTYBRANCHNAME LIKE '%' + @SearchQuery + '%' OR e.GCMNAME LIKE '%' + @SearchQuery + '%' OR pbaddcountry.COUNTRYNAME LIKE '%' + @SearchQuery + '%' )"; // ── GetEmployeeVsShiftPattern (GB4 Employee.svc.cs / EmployeeBLL.cs:3512, EmployeeDAL.cs:6100, // EmployeeQueryBuilder.cs:5536/:5600) ────────────────────────────────────────────────────── // Criteria columns (all against alias A except SHIFTPATTERNID -> D): EMPLOYEEID, DEPARTMENTID, // PAYCONFIGURATIONID, EMPLOYEETYPEID, UNITID, STAFFTYPEID, SHIFTPATTERNID, EMPLOYEESTATUS, // WORKOUID, CurrentOUId, PunchType. TENANTID added on A per §23 (not present in GB4 original). public const string GET_EMPLOYEE_VS_SHIFT_PATTERN_BASE = @" FROM MEMPLOYEE A INNER JOIN MSHIFTPATTERNDETAIL B ON A.SHIFTPATTERNDETAILID = B.SHIFTPATTERNDETAILID INNER JOIN MSHIFTPATTERNDAYS C ON C.SHIFTPATTERNDAYSID = B.SHIFTPATTERNDAYSID INNER JOIN MSHIFTPATTERN D ON D.SHIFTPATTERNID = C.SHIFTPATTERNID INNER JOIN MDEPARTMENT DEPT ON A.DEPARTMENTID = DEPT.DEPARTMENTID , MDBLEVELSETTING DB WHERE A.TENANTID = @TenantId AND (@EmployeeId IS NULL OR A.EMPLOYEEID = @EmployeeId) AND (@DepartmentId IS NULL OR A.DEPARTMENTID = @DepartmentId) AND (@PayConfigurationId IS NULL OR A.PAYCONFIGURATIONID = @PayConfigurationId) AND (@EmployeeTypeId IS NULL OR A.EMPLOYEETYPEID = @EmployeeTypeId) AND (@UnitId IS NULL OR A.UNITID = @UnitId) AND (@StaffTypeId IS NULL OR A.STAFFTYPEID = @StaffTypeId) AND (@ShiftPatternId IS NULL OR D.SHIFTPATTERNID = @ShiftPatternId) AND (@EmployeeStatus IS NULL OR A.EMPLOYEESTATUS = @EmployeeStatus) AND (@WorkOUId IS NULL OR A.WORKOUID = @WorkOUId) AND (@CurrentOUId IS NULL OR A.CURRENTOUID = @CurrentOUId) AND (@PunchType IS NULL OR A.PUNCHTYPE = @PunchType)"; public static readonly string GET_EMPLOYEE_VS_SHIFT_PATTERN = @" SELECT A.EMPLOYEECODE AS EmployeeCode, A.EMPLOYEENAME AS EmployeeName, D.SHIFTPATTERNCODE AS ShiftPatternCode, D.SHIFTPATTERNNAME AS ShiftPatternName, DEPT.DEPARTMENTCODE AS DepartmentCode, DEPT.DEPARTMENTNAME AS DepartmentName, A.PATTERNDATE AS PatternDate, CASE WHEN A.PUNCHTYPE = 0 THEN 'Not Required' WHEN A.PUNCHTYPE = 1 THEN 'Punch Hours(First IN and Last OUT)' WHEN A.PUNCHTYPE = 2 THEN 'Login/Out' WHEN A.PUNCHTYPE = 3 THEN 'Only Attendnace' WHEN A.PUNCHTYPE = 4 THEN 'Punch Work Hourse(Same Date)' WHEN A.PUNCHTYPE = 5 THEN 'Punch Work Hourse(Across Date)' ELSE '' END AS PunchType, CASE WHEN A.ATTENDANCETYPE = 0 THEN 'Not Required' WHEN A.ATTENDANCETYPE = 1 THEN 'Organization' WHEN A.ATTENDANCETYPE = 2 THEN 'Location' WHEN A.ATTENDANCETYPE = 3 THEN 'On Field' WHEN A.ATTENDANCETYPE = 4 THEN 'Onsite' ELSE '' END AS AttendanceType, CASE WHEN DB.GEOFENCING = 0 THEN 'Not Required' WHEN DB.GEOFENCING = 1 THEN 'Warning' WHEN DB.GEOFENCING = 2 THEN 'Error' ELSE '' END AS GeoFencing" + GET_EMPLOYEE_VS_SHIFT_PATTERN_BASE; public const string GET_EMPLOYEE_VS_SHIFT_PATTERN_COUNT = "SELECT COUNT(1) " + GET_EMPLOYEE_VS_SHIFT_PATTERN_BASE; // ── GetEmployeeVsShiftPatternNew (GB4 Employee.svc "Get/Employee/Vs/ShiftPattern/Load", // EmployeeBLL.cs:3576, EmployeeQueryBuilder.cs:5639 GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_LOAD) // — genuinely distinct from GET_EMPLOYEE_VS_SHIFT_PATTERN: returns per-shift-pattern-day // DayType/NumberofDays rows (one row per MSHIFTPATTERNDAYS entry for the employee's pattern), // not the punch-type/attendance-type/geofencing summary. Same base table set, different join // cardinality (no MDEPARTMENT/MDBLEVELSETTING join). TENANTID filter added per §23. public const string GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_BASE = @" FROM MEMPLOYEE A INNER JOIN MSHIFTPATTERNDETAIL B ON A.SHIFTPATTERNDETAILID = B.SHIFTPATTERNDETAILID INNER JOIN MSHIFTPATTERNDAYS C ON B.SHIFTPATTERNDAYSID = C.SHIFTPATTERNDAYSID INNER JOIN MSHIFTPATTERN D ON C.SHIFTPATTERNID = D.SHIFTPATTERNID WHERE A.TENANTID = @TenantId AND (@EmployeeId IS NULL OR A.EMPLOYEEID = @EmployeeId) AND (@DepartmentId IS NULL OR A.DEPARTMENTID = @DepartmentId) AND (@ShiftPatternId IS NULL OR D.SHIFTPATTERNID = @ShiftPatternId) AND (@WorkOUId IS NULL OR A.WORKOUID = @WorkOUId) -- GB4 parity: EmployeeBLL.GetEmployeeVsShiftPatternLoad's CriteriaWithQueryField map. AND (@WorkTypeId IS NULL OR A.WORKTYPEID = @WorkTypeId) AND (@StaffTypeId IS NULL OR A.STAFFTYPEID = @StaffTypeId) AND (@EmployeeTypeId IS NULL OR A.EMPLOYEETYPEID = @EmployeeTypeId) AND (@OurBranchId IS NULL OR A.OURBRANCHID = @OurBranchId) AND (@FunctionalReportingToId IS NULL OR A.REPORTINGTOEMPLOYEEID = @FunctionalReportingToId) AND (@SubUnitId IS NULL OR A.UNITID = @SubUnitId) AND (@PayConfigurationId IS NULL OR A.PAYCONFIGURATIONID = @PayConfigurationId) AND (@AdminReportingToId IS NULL OR A.ADMINREPORTINGTOID = @AdminReportingToId)"; public const string GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_LOAD = @" SELECT ROW_NUMBER() OVER (ORDER BY A.EMPLOYEEID) AS Slno, A.EMPLOYEEID AS EmployeeId, A.EMPLOYEECODE AS EmployeeCode, A.EMPLOYEENAME AS EmployeeName, A.PATTERNDATE AS EmployeePatternDate, A.SHIFTPATTERNDETAILID AS ShiftPatternDetailId, D.SHIFTPATTERNID AS ShiftPatternId, D.SHIFTPATTERNNAME AS ShiftPatternName, C.DAYTYPE AS DayType, C.NUMBEROFDAYS AS NumberofDays" + GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_BASE; public const string GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_LOAD_COUNT = "SELECT COUNT(1) " + GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_BASE; // ── GetEmployeeVersionPicklist (GB4 EmployeeBLL.cs:4834 "GetEmployeeVerisonPicklist", // EmployeeDAL.cs:9667, EmployeeQueryBuilder.cs:10080) ───────────────────────────────────── // GB4's EmployerVersionId criteria mapping pointed at a non-existent column — intentionally // not ported (documented GB4 bug). TENANTID added per §23 (MEMPLOYEEVERSION carries TENANTID). public const string GET_EMPLOYEEVERSION_PICKLIST = @" SELECT empv.EMPLOYEEVERSIONID AS EmployeeVersionId, empv.VERSIONFROM AS VersionFrom, empv.EMPLOYEEID AS EmployeeId, empv.EMPLOYEECODE AS EmployeeCode, empv.EMPLOYEENAME AS EmployeeName FROM MEMPLOYEEVERSION empv WHERE empv.EMPLOYEEID = @EmployeeId AND empv.TENANTID = @TenantId AND (@VersionFrom IS NULL OR empv.VERSIONFROM = @VersionFrom)"; // ── LegecyBasedEmployeeInformation (GB4 EmployeeBLL.cs:4965, EmployeeQueryBuilder.cs:11480) ── // Recursive CTE walking LEGACYEMPLOYEECODE chains from the given EmployeeId. public const string LEGECY_BASED_EMPLOYEE_INFORMATION = @" WITH cte AS ( SELECT employeeid, employeecode, legacyemployeecode FROM memployee WHERE employeeid = @EmployeeId AND tenantid = @TenantId UNION ALL SELECT b.employeeid, b.employeecode, b.legacyemployeecode FROM cte a, memployee b WHERE a.legacyemployeecode = b.employeecode AND b.tenantid = @TenantId ) SELECT b.employeeid AS EmployeeId, b.employeecode AS EmployeeCode, b.employeename AS EmployeeName, c.departmentname AS DepartmentName, emptype.gcmname AS EmployeeType, stafftype.gcmname AS StaffType, ou.organizationunitname AS WorkOU, REPLACE(CONVERT(varchar(20), b.versionfrom, 106), ' ', '-') AS EffectiveFrom, desg.gcmname AS Dessignation, payconf.payconfigurationname AS PayConfiguration, REPLACE(CONVERT(varchar(20), b.dateofjoining, 106), ' ', '-') AS DOJ, work.gcmname AS WorkType FROM cte a JOIN memployee b ON a.employeeid = b.employeeid JOIN mdepartment c ON b.departmentid = c.departmentid JOIN mgcm emptype ON b.employeetypeid = emptype.gcmid JOIN mgcm stafftype ON b.stafftypeid = stafftype.gcmid JOIN morganizationunit ou ON b.workouid = ou.ouid JOIN mgcm desg ON b.designationid = desg.gcmid JOIN mpayconfiguration payconf ON b.payconfigurationid = payconf.payconfigurationid JOIN mgcm work ON b.worktypeid = work.gcmid"; // ── GetEmployeeVersionInPayProcess (GB4 EmployeeBLL.cs:4856, EmployeeDAL.cs:9754, // EmployeeQueryBuilder.cs:10093) ──────────────────────────────────────────────────────── public const string EMPLOYEE_VERSION_CHECKING_WITH_PAYPROCESS = @" SELECT DISTINCT a.EMPLOYEEVERSIONID AS EmployeeVersionId, a.VERSIONFROM AS VersionFrom FROM MEMPLOYEEVERSION a, TPAYPROCESS b, MPAYPERIOD c WHERE b.PAYPERIODID = c.PAYPERIODID AND a.EMPLOYEEID = b.EMPLOYEEID AND b.EMPLOYEEID = @EmployeeId AND a.VERSIONFROM = @VersionFrom AND (a.VERSIONFROM >= c.FROMDATE OR a.VERSIONFROM <= c.FROMDATE) AND a.VERSIONFROM <= c.TODATE AND c.PAYPERIODID <> -5 AND a.TENANTID = @TenantId"; // ── GetEmployeeVersion (GB4 EmployeeBLL.cs:1159-1452) — explicit SQL replacing GB4's // NHibernate lazy-load. Join shapes mirror GB5's GET_EMPLOYEE query for the same reference // tables (see GET_EMPLOYEE above) since MEMPLOYEEVERSION mirrors MEMPLOYEE column-for-column. public const string GET_EMPLOYEE_VERSION = @" SELECT ev.EMPLOYEEVERSIONID AS EmployeeVersionId, ev.EMPLOYEEID AS EmployeeId, ev.EMPLOYEECOMMONID AS EmployeeCommonId, ev.CODEDEFINEID AS EmployeeCodeDefineId, ev.EMPLOYEECODE AS EmployeeCode, ev.EMPLOYEENAME AS EmployeeName, ev.FATHERNAME AS EmployeeFatherName, ev.MOTHERNAME AS EmployeeMotherName, ev.SPOUSENAME AS EmployeeSpouseName, ev.DEPARTMENTID AS DepartmentId, ev.COSTCENTERTYPE AS EmployeeCostCenterType, ev.COSTCENTERID AS CostCenterId, ev.COSTCENTERPATTERNID AS CostCenterAllocationId, ev.WORKOUID AS WorkOUId, ev.ISCROSSOU AS EmployeeIsCrossOU, ev.OUGROUPID AS OUGroupId, ev.PAYGROUPID AS PayGroupId, ev.WORKTYPEID AS WorkTypeId, ev.EMPLOYEETYPEID AS EmployeeTypeId, ev.STAFFTYPEID AS StaffTypeId, ev.DESIGNATIONID AS DesignationId, ev.NATIONALITYID AS NationalityId, ev.RELIGIONID AS ReligionId, ev.LOCATIONID AS LocationId, ev.CASTEID AS CasteId, ev.HEIGHTINCMS AS EmployeeHeightInCMS, ev.WEIGHTINKGS AS EmployeeWeightInKGS, ev.CHESTSIZE AS EmployeeChestSize, ev.ISHANDICAPPED AS EmployeeIsHandiCapped, ev.IDENTIFICATIONMARK AS EmployeeIdentificationMark, ev.PHYSICALCONDITION AS EmployeePhysicalCondition, ev.EMPLOYEESTATUS AS EmployeeEmployerStatus, ev.DATEOFBIRTH AS EmployeeDateOfBirth, ev.ACTUALDATEOFBIRTH AS EmployeeActualDateOfBirth, ev.DATEOFJOINING AS EmployeeDateOfJoining, ev.ISCONFIRMED AS EmployeeIsConfirmed, ev.DATEOFCONFIRMATION AS EmployeeDateOfConfirmation, ev.DATEOFRESIGNATION AS EmployeeDateOfResignation, ev.CALCULATEDDATEOFRETIREMENT AS EmployeeCalculatedDateOfRetirment, ev.ACTUALDATEOFRETIREMENT AS EmployeeActualDateOfRetirment, ev.MARITALSTATUS AS EmployeeMaritalStatus, ev.SEX AS EmployeeSex, CASE WHEN ev.SEX = 0 THEN 'Male' WHEN ev.SEX = 1 THEN 'Female' ELSE 'Other' END AS SexName, ev.BLOODGROUP AS EmployeeBloodGroup, ev.ISPFAPPLICABLE AS EmployeeIsPFApplicable, ev.PFSTATUS AS EmployeePFStatus, ev.PFNUMBER AS EmployeePFNumber, ev.STATUTORYPFPERCENTAGE AS EmployeeStatutorypfPercentage, ev.EMPLOYEEPFPERCENTAGE AS EmployeeEmployeePFPercentage, ev.EMPLOYERPFPERCENTAGE AS EmployeeEmployerPFPercentage, ev.ISFAMILYPENSION AS EmployeeIsFamilyPension, ev.FAMILYPENSIONNUMBER AS EmployeeFamilyPensionNumber, ev.FAMILYPENSIONPERCENTAGE AS EmployeeFamilyPensionPercentage, ev.PFJOINDATE AS EmployeePFJoinDate, ev.PFENDDATE AS EmployeePFEndDate, ev.ISESIAPPLICABLE AS EmployeeIsESIApplicable, ev.ESISTATUS AS EmployeeESIStatus, ev.ESINUMBER AS EmployeeESINumber, ev.ESIJOINDATE AS EmployeeESIJoinDate, ev.ESIENDDATE AS EmployeeESIEndDate, ev.ESIDISPENSARY AS EmployeeESIDispensary, ev.EMPLOYEEESIPERCENTAGE AS EmployeeEmployeeESIPercentage, ev.EMPLOYERESIPERCENTAGE AS EmployeeEmployerESIPercentage, ev.ISESIHOURLYCONTRIBUTION AS EmployeeIsESIHourlyContribution, ev.BASICON AS EmployeeBasicOn, ev.PAYMODE AS EmployeePayMode, ev.OTPAYMODE AS EmployeeOTPayMode, ev.OTTYPE AS EmployeeOtType, ev.ISTDSAPPLICABLE AS EmployeeIsTDSApplicable, ev.SALARYGRADEID AS SalaryGradeId, ev.DAPOINTGROUPID AS DAPointGroupId, ev.PANNUMBER AS EmployeePANNumber, ev.ACCOUNTNUMBER AS EmployeeAccountNumber, ev.SHIFTID AS ShiftId, ev.WEEKOFFDAY AS EmployeeWeekOfDay, ev.ACCOUNTID AS AccountId, ev.NOMINEE AS EmployeeNominee, ev.NOMINEERELATION AS EmployeeNomineeRelation, ev.ATTENDANCETYPE AS EmployeeAttendanceType, ev.ONSITEPLACE AS EmployeeOnSitePlace, ev.ISDAILYREPORT AS EmployeeIsDailyReport, ev.PUNCHTYPE AS EmployeePunchType, ev.PRIMARYEMAILID AS EmployeePrimaryEMailId, ev.PRIMARYMOBILE AS EmployeePrimaryMobile, ev.REPORTINGTOEMPLOYEEID AS ReportInttoEmployeeId, ev.EXPERIENCE AS EmployeeExperience, ev.ISTEMPORARYCARD AS EmployeeIsTemporaryCard, ev.TEMPORARYCARDNUMBER AS EmployeeTemporaryCardNumber, ev.CARDVALIDTILL AS EmployeeCardValidTill, ev.ISNUMBERREQUIRED AS EmployeeIsNumberRequired, ev.BIOMATCHNUMBER AS EmployeeBioMatchNumber, ev.ADVANCEACCOUNTID AS AdvanceAccountId, ev.SALARYACCOUNTID AS SalaryAccountId, ev.DEFAULTIMAGEID AS EmployeeDefaultImage, ev.ISCONTRACT AS EmployeeIsContract, ev.ISNUMBER AS EmployeeIsNumber, ev.ISPURCHASE AS EmployeeIsPurchase, ev.ISMARKETING AS EmployeeIsMarketing, ev.ISSALES AS EmployeeIsSales, ev.ISPLANNING AS EmployeeIsPlanning, ev.ISSERVICE AS EmployeeIsService, ev.ISUSER AS EmployeeIsUser, ev.VERSION AS EmployeeVersion, ev.STATUS AS EmployeeStatus, ev.CREATEDBYID AS EmployeeCreatedById, ev.CREATEDON AS EmployeeCreatedOn, ev.MODIFIEDBYID AS EmployeeModifiedById, ev.MODIFIEDON AS EmployeeModifiedOn, ev.SEGMENTID AS SegmentId, ev.UNITID AS UnitId, ev.SHIFTPATTERNDETAILID AS ShiftPatternDetailId, ev.PATTERNDATE AS EmployeePatternDate, ev.REASONFORESILEAVING AS EmployeeReasonForESILeaving, ev.REASONFORLEAVING AS EmployeeReasonForLeaving, ev.REASONFORPFLEAVING AS EmployeeReasonForPFLeaving, ev.VILLAGEID AS VillageId, ev.SORTORDER AS EmployeeSortOrder, ev.PROBATIONSTARTDATE AS EmployeeProbationStartDate, ev.CALENDARID AS CalendarId, ev.RESOURCETYPEID AS ResourceTypeId, ev.ISPRODUCTION AS EmployeeIsProduction, ev.ADHARNUMBER AS EmployeeAdharNumber, ev.PFUANNUMBER AS EmployeePFUANNumber, ev.PAYCONFIGURATIONID AS PayConfigurationId, ev.PRODUCTCATEGORYIDS AS EmployeeProductCategoryIds, ev.PRODUCTPRICECATEGORYIDS AS EmployeeProductPriceCategoryIds, ev.TENANTID AS TenantId, ev.ISPIREQUIRED AS EmployeeIsPiRequired, ev.GRATUITYGROUPID AS GratuityGroupId, ev.CURRENCYID AS CurrencyId, ev.ADMINREPORTINGTOID AS AdminReportingToId, ev.FIRSTNAME AS FirstName, ev.MIDDLENAME AS MiddleName, ev.LASTNAME AS LastName, ev.NOOFVERIFICATION AS EmployeeNoOfVerfication, ev.LOCATIONIDS AS EmployeeLocationIds, ev.SECONDARYMAILID AS SecondaryMailId, ev.SECONDARYMOBILE AS SecondaryMobile, ev.IBANNUMBER AS EmployeeIBANNumber, ev.EMPLOYEEBANKBRANCHID AS EmployeeBankBranchId, ev.PRESENTADDRESSID AS EmployeePresentAddressId, ev.PERMANENTADDRESSID AS EmployeePermenantAddressId, ev.CONTACTID AS ContactId, ev.TRAININGPERIOD AS EmployeeTrainingPeriod, ev.TRAININGENDINGDATE AS EmployeeTrainingendingDate, ev.ISTIMESHEETREQUIRED AS EmployeeIsTimeSheetRequired, ev.ISPOSTINGREQUIRED AS EmployeeIsPostingRequired, ev.PLACEOFBIRTH AS EmployeePlaceOfBirth, ev.NATIVEPLACE AS EmployeeNativePlace, ev.NATIVEDISTRICT AS EmployeeNativeDistrict, ev.NOTICEPERIODINDAYS AS EmployeeNoticePeriodInDays, ev.DATEOFREJOIN AS EmployeeDateOfReJoin, ev.LEGACYEMPLOYEECODE AS EmployeeLegacyEmployeeCode, ev.NATURE AS EmployeeNature, ev.VERSIONFROM AS EmployeeVersionFrom, ev.CURRENTOUID AS CurrentOUId, ev.GRACEMINUTES AS EmployeeGraceMinutes, empcommon.EMPLOYEECODE AS EmployeeCommonCode, empcommon.EMPLOYEENAME AS EmployeeCommonName, cd.CATEGORYCODE AS CodeDefineCategoryCode, cd.CATEGORYNAME AS CodeDefineCategoryName, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, vil.VILLAGECODE AS VillageCode, vil.VILLAGENAME AS VillageName, cc.COSTCENTERCODE AS CostCenterCode, cc.COSTCENTERNAME AS CostCenterName, ccalloc.PATTERNCODE AS CostCenterAllocationCode, ccalloc.PATTERNNAME AS CostCenterAllocationName, ou.ORGANIZATIONUNITCODE AS WorkOUCode, ou.ORGANIZATIONUNITNAME AS WorkOUName, oug.ORGANIZATIONGROUPCODE AS OUGroupCode, oug.ORGANIZATIONGROUPNAME AS OUGroupName, pg.PAYGROUPCODE AS PayGroupCode, pg.PAYGROUPNAME AS PayGroupName, wt.GCMCODE AS WorkTypeCode, wt.GCMNAME AS WorkTypeName, et.GCMCODE AS EmployeeTypeCode, et.GCMNAME AS EmployeeTypeName, st.GCMCODE AS StaffTypeCode, st.GCMNAME AS StaffTypeName, des.GCMCODE AS DesignationCode, des.GCMNAME AS DesignationName, nat.GCMCODE AS NationalityCode, nat.GCMNAME AS NationalityName, rel.GCMCODE AS ReligionCode, rel.GCMNAME AS ReligionName, loc.LOCATIONCODE AS LocationCode, loc.LOCATIONNAME AS LocationName, caste.GCMCODE AS CasteCode, caste.GCMNAME AS CasteName, sg.GCMCODE AS SalaryGradeCode, sg.GCMNAME AS SalaryGradeName, dapg.DAPOINTGROUPCODE AS DAPointGroupCode, dapg.DAPOINTGROUPNAME AS DAPointGroupName, shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, acc.ACCOUNTCODE AS AccountCode, acc.ACCOUNTNAME AS AccountName, repto.EMPLOYEECODE AS ReportInttoEmployeeCode, repto.EMPLOYEENAME AS ReportInttoEmployeeName, advacc.ACCOUNTCODE AS AdvanceAccountCode, advacc.ACCOUNTNAME AS AdvanceAccountName, salacc.ACCOUNTCODE AS SalaryAccountCode, salacc.ACCOUNTNAME AS SalaryAccountName, seg.GCMCODE AS SegmentCode, seg.GCMNAME AS SegmentName, unit.GCMCODE AS UnitCode, unit.GCMNAME AS UnitName, cal.CALENDARCODE AS CalendarCode, cal.CALENDARNAME AS CalendarName, rt.RESOURCETYPECODE AS ResourceTypeCode, rt.RESOURCETYPENAME AS ResourceTypeName, pc.PAYCONFIGURATIONCODE AS PayConfigurationCode, pc.PAYCONFIGURATIONNAME AS PayConfigurationName, -- GB4 parity fix (EmployeeBLL.cs:1417): PayConfiguration.IsPartialProcessRequired -- was dropped in the initial migration; restored from the same pc join GET_EMPLOYEE uses. pc.ISPARTIALPROCESSREQUIRED AS PayConfigurationIsPartialProcessRequired, gg.GRATUITYGROUPCODE AS GratuityGroupCode, gg.GRATUITYGROUPNAME AS GratuityGroupName, curr.CURRENCYCODE AS CurrencyCode, curr.CURRENCYNAME AS CurrencyName, adminrep.USERCODE AS AdminReportingToCode, adminrep.USERNAME AS AdminReportingToName, currentou.ORGANIZATIONUNITCODE AS CurrentOUCode, currentou.ORGANIZATIONUNITNAME AS CurrentOUName, -- GB4 parity fix (EmployeeBLL.cs:1422-23): ContactName resolution was dropped — -- ContactId was selected but never joined to resolve the name. Same MCONTACT join -- shape as GET_EMPLOYEE (see cont alias above in this file). cont.NAME AS ContactName FROM [DBO].MEMPLOYEEVERSION ev LEFT JOIN [DBO].MEMPLOYEE empcommon ON ev.EMPLOYEECOMMONID = empcommon.EMPLOYEEID LEFT JOIN [DBO].MCODEDEFINE cd ON ev.CODEDEFINEID = cd.CODEDEFINEID LEFT JOIN [DBO].MDEPARTMENT dept ON ev.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN [DBO].MVILLAGE vil ON ev.VILLAGEID = vil.VILLAGEID LEFT JOIN [DBO].MCOSTCENTER cc ON ev.COSTCENTERID = cc.COSTCENTERID LEFT JOIN [DBO].MCOSTCENTERPATTERN ccalloc ON ev.COSTCENTERPATTERNID = ccalloc.PATTERNID LEFT JOIN [DBO].MORGANIZATIONUNIT ou ON ev.WORKOUID = ou.OUID LEFT JOIN [DBO].MORGANIZATIONGROUP oug ON ev.OUGROUPID = oug.ORGANIZATIONGROUPID LEFT JOIN [DBO].MPAYGROUP pg ON ev.PAYGROUPID = pg.PAYGROUPID LEFT JOIN [DBO].MGCM wt ON ev.WORKTYPEID = wt.GCMID LEFT JOIN [DBO].MGCM et ON ev.EMPLOYEETYPEID = et.GCMID LEFT JOIN [DBO].MGCM st ON ev.STAFFTYPEID = st.GCMID LEFT JOIN [DBO].MGCM des ON ev.DESIGNATIONID = des.GCMID LEFT JOIN [DBO].MGCM nat ON ev.NATIONALITYID = nat.GCMID LEFT JOIN [DBO].MGCM rel ON ev.RELIGIONID = rel.GCMID LEFT JOIN [DBO].MLOCATION loc ON ev.LOCATIONID = loc.LOCATIONID LEFT JOIN [DBO].MGCM caste ON ev.CASTEID = caste.GCMID LEFT JOIN [DBO].MGCM sg ON ev.SALARYGRADEID = sg.GCMID LEFT JOIN [DBO].MDAPOINTGROUP dapg ON ev.DAPOINTGROUPID = dapg.DAPOINTGROUPID LEFT JOIN [DBO].MSHIFT shift ON ev.SHIFTID = shift.SHIFTID LEFT JOIN [DBO].MACCOUNT acc ON ev.ACCOUNTID = acc.ACCOUNTID LEFT JOIN [DBO].MEMPLOYEE repto ON ev.REPORTINGTOEMPLOYEEID = repto.EMPLOYEEID LEFT JOIN [DBO].MACCOUNT advacc ON ev.ADVANCEACCOUNTID = advacc.ACCOUNTID LEFT JOIN [DBO].MACCOUNT salacc ON ev.SALARYACCOUNTID = salacc.ACCOUNTID LEFT JOIN [DBO].MGCM seg ON ev.SEGMENTID = seg.GCMID LEFT JOIN [DBO].MGCM unit ON ev.UNITID = unit.GCMID LEFT JOIN [DBO].MCALENDAR cal ON ev.CALENDARID = cal.CALENDARID LEFT JOIN [DBO].MRESOURCETYPE rt ON ev.RESOURCETYPEID = rt.RESOURCETYPEID LEFT JOIN [DBO].MPAYCONFIGURATION pc ON ev.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID LEFT JOIN [DBO].MGRATUITYGROUP gg ON ev.GRATUITYGROUPID = gg.GRATUITYGROUPID LEFT JOIN [DBO].MCURRENCY curr ON ev.CURRENCYID = curr.CURRENCYID LEFT JOIN [DBO].MUSER adminrep ON ev.ADMINREPORTINGTOID = adminrep.USERID LEFT JOIN [DBO].MORGANIZATIONUNIT currentou ON ev.CURRENTOUID = currentou.OUID LEFT JOIN [DBO].MCONTACT cont ON ev.CONTACTID = cont.CONTACTID WHERE ev.EMPLOYEEVERSIONID = @EmployeeVersionId AND ev.TENANTID = @TenantId"; // ── GetDuplicateEmployee (GB4 EmployeeBLL.cs:4765, EmployeeDAL.cs:9455, // EmployeeQueryBuilder.cs:9469) ── parameterized optional-filter query, replacing GB4's // dynamic string-concatenation of AND clauses (SQL-injection-shaped anti-pattern). public const string GET_DUPLICATE_EMPLOYEE = @" SELECT a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, a.DATEOFBIRTH AS DateOfBirth, a.FATHERNAME AS FatherName, a.MOTHERNAME AS MotherName, a.ADHARNUMBER AS AdharNumber, a.PFNUMBER AS PFNumber, a.ESINUMBER AS ESINumber, a.PFUANNUMBER AS PFUANNumber FROM MEMPLOYEE a WHERE a.TENANTID = @TenantId AND (@EmployeeName IS NULL OR @EmployeeName = '' OR a.EMPLOYEENAME LIKE '%' + @EmployeeName + '%') AND (@FatherName IS NULL OR @FatherName = '' OR a.FATHERNAME LIKE '%' + @FatherName + '%') AND (@DateOfBirth IS NULL OR a.DATEOFBIRTH = @DateOfBirth) AND (@AdharNumber IS NULL OR @AdharNumber = '' OR a.ADHARNUMBER LIKE '%' + @AdharNumber + '%') AND (@PFNumber IS NULL OR @PFNumber = '' OR a.PFNUMBER LIKE '%' + @PFNumber + '%') AND (@ESINumber IS NULL OR @ESINumber = '' OR a.ESINUMBER LIKE '%' + @ESINumber + '%') AND (@PFUANNumber IS NULL OR @PFUANNumber = '' OR a.PFUANNUMBER LIKE '%' + @PFUANNumber + '%')"; // ── DeleteEmployeeVersion (GB4 EmployeeBLL.cs:1732-1816) ──────────────────────────────────── public const string GET_PAYPROCESS_DONE_AFTER_EMPLOYEE_VERSION = @" SELECT COUNT(a.EMPLOYEEVERSIONID) FROM MEMPLOYEEVERSION a, TPAYPROCESS b, MPAYPERIOD c WHERE a.EMPLOYEEVERSIONID = @EmployeeVersionId AND a.EMPLOYEEID = b.EMPLOYEEID AND b.PAYPERIODID = c.PAYPERIODID AND c.PERIODTYPE = 0 AND c.FROMDATE >= a.VERSIONFROM"; public const string GET_LATEST_VERSION_AFTER_GIVEN_VERSION = @" SELECT COUNT(b.EMPLOYEEVERSIONID) FROM MEMPLOYEEVERSION a, MEMPLOYEEVERSION b WHERE a.EMPLOYEEID = b.EMPLOYEEID AND a.EMPLOYEEVERSIONID = @EmployeeVersionId AND b.VERSIONFROM > a.VERSIONFROM"; public const string GET_EMPLOYEE_ID_FROM_VERSION = "SELECT EMPLOYEEID FROM MEMPLOYEEVERSION WHERE EMPLOYEEVERSIONID = @EmployeeVersionId"; public const string DELETE_EMPLOYEEADDONVERSION_BY_VERSION = "DELETE FROM MEMPLOYEEADDONVERSION WHERE EMPLOYEEVERSIONID = @EmployeeVersionId"; public const string DELETE_EMPLOYEEVERSION_BY_ID = "DELETE FROM MEMPLOYEEVERSION WHERE EMPLOYEEVERSIONID = @EmployeeVersionId"; public const string GET_EMPLOYEE_VERSION_LATEST_ID = @" SELECT a.EMPLOYEEVERSIONID FROM MEMPLOYEEVERSION a, (SELECT MAX(VersionFrom) VersionFrom, EMPLOYEEID FROM MEMPLOYEEVERSION WHERE EMPLOYEEID = @EmployeeId GROUP BY EMPLOYEEID) b WHERE a.EMPLOYEEID = b.EMPLOYEEID AND a.VersionFrom = b.VersionFrom"; // 120+ column parity UPDATE ported verbatim (column-for-column) from GB4 // EmployeeQueryBuilder.UPDATE_LATEST_VESION_IN_MEMPLOYEE_AFTER_DELETE, parameterized. public const string UPDATE_LATEST_VERSION_IN_MEMPLOYEE_AFTER_DELETE = @" UPDATE memployee SET employeecommonid = I.employeecommonid, codedefineid = I.codedefineid, employeecode = I.employeecode, employeename = I.employeename, fathername = I.fathername, mothername = I.mothername, spousename = I.spousename, departmentid = I.departmentid, costcentertype = I.costcentertype, costcenterid = I.costcenterid, costcenterpatternid = I.costcenterpatternid, workouid = I.workouid, iscrossou = I.iscrossou, ougroupid = I.ougroupid, paygroupid = I.paygroupid, worktypeid = I.worktypeid, employeetypeid = I.employeetypeid, stafftypeid = I.stafftypeid, designationid = I.designationid, nationalityid = I.nationalityid, religionid = I.religionid, locationid = I.locationid, casteid = I.casteid, heightincms = I.heightincms, weightinkgs = I.weightinkgs, chestsize = I.chestsize, ishandicapped = I.ishandicapped, identificationmark = I.identificationmark, physicalcondition = I.physicalcondition, employeestatus = I.employeestatus, dateofbirth = I.dateofbirth, actualdateofbirth = I.actualdateofbirth, dateofjoining = I.dateofjoining, isconfirmed = I.isconfirmed, dateofconfirmation = I.dateofconfirmation, dateofresignation = I.dateofresignation, calculateddateofretirement = I.calculateddateofretirement, actualdateofretirement = I.actualdateofretirement, maritalstatus = I.maritalstatus, sex = I.sex, bloodgroup = I.bloodgroup, ispfapplicable = I.ispfapplicable, pfstatus = I.pfstatus, pfnumber = I.pfnumber, statutorypfpercentage = I.statutorypfpercentage, employeepfpercentage = I.employeepfpercentage, employerpfpercentage = I.employerpfpercentage, isfamilypension = I.isfamilypension, familypensionnumber = I.familypensionnumber, familypensionpercentage = I.familypensionpercentage, pfjoindate = I.pfjoindate, pfenddate = I.pfenddate, isesiapplicable = I.isesiapplicable, esistatus = I.esistatus, esinumber = I.esinumber, esijoindate = I.esijoindate, esienddate = I.esienddate, esidispensary = I.esidispensary, employeeesipercentage = I.employeeesipercentage, isesihourlycontribution = I.isesihourlycontribution, basicon = I.basicon, paymode = I.paymode, otpaymode = I.otpaymode, istdsapplicable = I.istdsapplicable, salarygradeid = I.salarygradeid, dapointgroupid = I.dapointgroupid, pannumber = I.pannumber, accountnumber = I.accountnumber, shiftid = I.shiftid, weekoffday = I.weekoffday, accountid = I.accountid, nominee = I.nominee, nomineerelation = I.nomineerelation, attendancetype = I.attendancetype, onsiteplace = I.onsiteplace, isdailyreport = I.isdailyreport, punchtype = I.punchtype, primaryemailid = I.primaryemailid, primarymobile = I.primarymobile, reportingtoemployeeid = I.reportingtoemployeeid, experience = I.experience, istemporarycard = I.istemporarycard, temporarycardnumber = I.temporarycardnumber, cardvalidtill = I.cardvalidtill, isnumberrequired = I.isnumberrequired, biomatchnumber = I.biomatchnumber, advanceaccountid = I.advanceaccountid, salaryaccountid = I.salaryaccountid, defaultimageid = I.defaultimageid, iscontract = I.iscontract, isnumber = I.isnumber, ispurchase = I.ispurchase, ismarketing = I.ismarketing, issales = I.issales, isplanning = I.isplanning, isservice = I.isservice, isuser = I.isuser, version = I.version, status = I.status, createdbyid = I.createdbyid, createdon = I.createdon, modifiedbyid = I.modifiedbyid, modifiedon = I.modifiedon, segmentid = I.segmentid, unitid = I.unitid, shiftpatterndetailid = I.shiftpatterndetailid, patterndate = I.patterndate, reasonforesileaving = I.reasonforesileaving, reasonforpfleaving = I.reasonforpfleaving, villageid = I.villageid, employeresipercentage = I.employeresipercentage, sourcetype = I.sourcetype, sortorder = I.sortorder, ottype = I.ottype, calendarid = I.calendarid, probationstartdate = I.probationstartdate, resourcetypeid = I.resourcetypeid, isproduction = I.isproduction, adharnumber = I.adharnumber, pfuannumber = I.pfuannumber, payconfigurationid = I.payconfigurationid, productcategoryids = I.productcategoryids, productpricecategoryids = I.productpricecategoryids, tenantid = I.tenantid, ispirequired = I.ispirequired, gratuitygroupid = I.gratuitygroupid, currencyid = I.currencyid, adminreportingtoid = I.adminreportingtoid, firstname = I.firstname, middlename = I.middlename, lastname = I.lastname, noofverification = I.noofverification, locationids = I.locationids, secondarymailid = I.secondarymailid, secondarymobile = I.secondarymobile, ibannumber = I.ibannumber, employeebankbranchid = I.employeebankbranchid, presentaddressid = I.presentaddressid, permanentaddressid = I.permanentaddressid, contactid = I.contactid, trainingperiod = I.trainingperiod, trainingendingdate = I.trainingendingdate, istimesheetrequired = I.istimesheetrequired, ispostingrequired = I.ispostingrequired, dateofintimation = I.dateofintimation, reasonforleaving = I.reasonforleaving, placeofbirth = I.placeofbirth, nativeplace = I.nativeplace, nativedistrict = I.nativedistrict, noticeperiodindays = I.noticeperiodindays, dateofrejoin = I.dateofrejoin, legacyemployeecode = I.legacyemployeecode, nature = I.nature, versionfrom = CAST(I.versionfrom AS DATE) FROM memployeeversion I WHERE I.employeeid = memployee.employeeid AND I.employeeversionid = @LatestEmployeeVersionId"; // Dynamic SQL: the addon field list is per-tenant metadata (MADDONFIELDS), ported as-is // from GB4's dynamic EXEC pattern. @LatestEmployeeVersionId is substituted as a literal int // (validated integer, not user free-text) into the generated statement, matching GB4's own // approach for this metadata-driven statement — MADDONFIELDS.FIELDNAME values are DDL-time // tenant configuration, not end-user request input. public const string UPDATE_LATEST_VERSION_IN_MEMPLOYEE_ADDON_AFTER_DELETE = @" DECLARE @ADDONFIELDS VARCHAR(max) DECLARE @SSQL VARCHAR(max) SELECT @ADDONFIELDS = Stuff((SELECT ',' + fieldname + ' = I.' + fieldname FROM maddonfields WHERE entityid = -1399999336 -- EMPLOYEE ENTITYID FOR xml path('')), 1, 1, '') IF Len(@ADDONFIELDS) > 1 BEGIN SET @SSQL = 'UPDATE MEMPLOYEEADDON SET ' SET @SSQL = @SSQL + @ADDONFIELDS SET @SSQL = @SSQL + ' FROM MEMPLOYEEADDONVERSION I,memployeeversion EV' SET @SSQL = @SSQL + ' WHERE I.EMPLOYEEVERSIONID = ' + CAST(@LatestEmployeeVersionId AS VARCHAR(20)) SET @SSQL = @SSQL + ' AND I.EMPLOYEEVERSIONID = EV.EMPLOYEEVERSIONID' SET @SSQL = @SSQL + ' AND MEMPLOYEEADDON.employeeid = EV.employeeid' EXEC (@SSQL) END"; } }