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

namespace PayRollDAL.Query.TRMEmployee
{
    public static class TRMEmployeeQB
    {
        public const string GET_TRMEMPLOYEE = @"SELECT
    emp.EMPLOYEEID AS EmployeeId,
    emp.EMPLOYEECOMMONID AS EmployeeCommonId,
    emp.CODEDEFINEID AS EmployeeCodeDefineId,
    emp.EMPLOYEECODE AS EmployeeCode,
    ISNULL(tremp.EMPLOYEENAME, emp.EMPLOYEENAME) AS EmployeeName,
    ISNULL(tremp.FATHERNAME, emp.FATHERNAME) AS EmployeeFatherName,
    ISNULL(tremp.MOTHERNAME, emp.MOTHERNAME) AS EmployeeMotherName,
    ISNULL(tremp.SPOUSENAME, 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,
    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.ISESIHOURLYCONTRIBUTION AS EmployeeIsESIHourlyContribution,
    emp.BASICON AS EmployeeBasicOn,
    emp.PAYMODE AS EmployeePayMode,
    emp.OTPAYMODE AS EmployeeOTPayMode,
    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,
    ISNULL(tremp.VERSION, emp.VERSION) AS EmployeeVersion,
    ISNULL(tremp.STATUS, emp.STATUS) AS EmployeeStatus,
    ISNULL(tremp.CREATEDBYID, emp.CREATEDBYID) AS EmployeeCreatedById,
    ISNULL(tremp.CREATEDON, emp.CREATEDON) AS EmployeeCreatedOn,
    ISNULL(tremp.MODIFIEDBYID, emp.MODIFIEDBYID) AS EmployeeModifiedById,
    ISNULL(tremp.MODIFIEDON, 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.REASONFORPFLEAVING AS EmployeeReasonForPFLeaving,
    emp.VILLAGEID AS VillageId,
    emp.EMPLOYERESIPERCENTAGE AS EmployeeEmployerESIPercentage,
    ISNULL(tremp.SOURCETYPE, emp.SOURCETYPE) AS SourceType,
    ISNULL(tremp.SORTORDER, emp.SORTORDER) AS EmployeeSortOrder,
    emp.OTTYPE AS EmployeeOtType,
    emp.CALENDARID AS CalendarId,
    emp.PROBATIONSTARTDATE AS EmployeeProbationStartDate,
    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,
    ISNULL(tremp.FIRSTNAME, emp.FIRSTNAME) AS FirstName,
    ISNULL(tremp.MIDDLENAME, emp.MIDDLENAME) AS MiddleName,
    ISNULL(tremp.LASTNAME, 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,
    emp.TRAININGENDINGDATE AS EmployeeTrainingendingDate,
    emp.ISTIMESHEETREQUIRED AS EmployeeIsTimeSheetRequired,
    emp.ISPOSTINGREQUIRED AS EmployeeIsPostingRequired,
    emp.DATEOFINTIMATION AS EmployeeDateOfIntimation,
    emp.REASONFORLEAVING AS EmployeeReasonForLeaving,
    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
FROM MEMPLOYEE emp
LEFT OUTER JOIN TR_MEMPLOYEE tremp 
    ON emp.EMPLOYEEID = tremp.EMPLOYEEID AND tremp.LANGUAGEID = @languageId
WHERE emp.EMPLOYEEID = @employeeid;";
        public const string GET_TRMEMPLOYEE_PG = @"SELECT
    emp.employeeid AS EmployeeId,
    emp.employeecommonid AS EmployeeCommonId,
    emp.codedefineid AS EmployeeCodeDefineId,
    emp.employeecode AS EmployeeCode,
    COALESCE(tremp.employeename, emp.employeename) AS EmployeeName,
    COALESCE(tremp.fathername, emp.fathername) AS EmployeeFatherName,
    COALESCE(tremp.mothername, emp.mothername) AS EmployeeMotherName,
    COALESCE(tremp.spousename, 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,
    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.isesihourlycontribution AS EmployeeIsESIHourlyContribution,
    emp.basicon AS EmployeeBasicOn,
    emp.paymode AS EmployeePayMode,
    emp.otpaymode AS EmployeeOTPayMode,
    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,
    COALESCE(tremp.version, emp.version) AS EmployeeVersion,
    COALESCE(tremp.status, emp.status) AS EmployeeStatus,
    COALESCE(tremp.createdbyid, emp.createdbyid) AS EmployeeCreatedById,
    COALESCE(tremp.createdon, emp.createdon) AS EmployeeCreatedOn,
    COALESCE(tremp.modifiedbyid, emp.modifiedbyid) AS EmployeeModifiedById,
    COALESCE(tremp.modifiedon, 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.reasonforpfleaving AS EmployeeReasonForPFLeaving,
    emp.villageid AS VillageId,
    emp.employeresipercentage AS EmployeeEmployerESIPercentage,
    COALESCE(tremp.sourcetype, emp.sourcetype) AS SourceType,
    COALESCE(tremp.sortorder, emp.sortorder) AS EmployeeSortOrder,
    emp.ottype AS EmployeeOtType,
    emp.calendarid AS CalendarId,
    emp.probationstartdate AS EmployeeProbationStartDate,
    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,
    COALESCE(tremp.firstname, emp.firstname) AS FirstName,
    COALESCE(tremp.middlename, emp.middlename) AS MiddleName,
    COALESCE(tremp.lastname, 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,
    emp.trainingendingdate AS EmployeeTrainingendingDate,
    emp.istimesheetrequired AS EmployeeIsTimeSheetRequired,
    emp.ispostingrequired AS EmployeeIsPostingRequired,
    emp.dateofintimation AS EmployeeDateOfIntimation,
    emp.reasonforleaving AS EmployeeReasonForLeaving,
    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
FROM public.memployee emp
LEFT JOIN public.tr_memployee tremp
    ON emp.employeeid = tremp.employeeid 
    AND tremp.languageid = @languageid
WHERE emp.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 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 EMPLOYEEID = @EmployeeId AND LANGUAGEID =@LanguageId ;";

        public const string DELETE_TRMEMPLOYEE = "DELETE FROM TR_MEMPLOYEE WHERE EMPLOYEEID = @employeeid;";
    }
}