using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.VisitorPass { public static class VisitorPassQB { public const string GET_VISITORPASS = @"SELECT VP.VISITORPASSID AS VisitorPassId, VP.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId, BT.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode, BT.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName, VP.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, VP.PERIODID AS PeriodId, PE.PERIODCODE AS PeriodCode, PE.PERIODNAME AS PeriodName, PE.FROMDATE AS PeriodFromDate, PE.TODATE AS PeriodToDate, VP.PASSNUMBER AS VisitorPassPassNumber, VP.PASSDATE AS VisitorPassPassDate, VP.PASSTYPE AS VisitorPassPassType, VP.APPROVEDBYID AS ApprovedByIdId, AP.EMPLOYEECODE AS ApprovedByIdCode, AP.EMPLOYEENAME AS ApprovedByIdName, VP.VALIDFROMDATE AS VisitorPassValidFromDate, VP.VALIDFROMTIME AS VisitorPassValidFromTime, RIGHT('0' + CAST(VP.VALIDFROMTIME / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(VP.VALIDFROMTIME % 60 AS VARCHAR), 2) AS VisitorPassCalValidFromTime, VP.VALIDTODATE AS VisitorPassValidToDate, VP.VALIDTOTIME AS VisitorPassValidToTime, RIGHT('0' + CAST(VP.VALIDTOTIME / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(VP.VALIDTOTIME % 60 AS VARCHAR), 2) AS VisitorPassCalValidToTime, VP.PAX AS VisitorPassPAX, VP.PARTYID AS PartyId, PA.PARTYCODE AS PartyCode, PA.PARTYNAME AS PartyName, VP.PARTYNAME AS VisitorPassPartyName, CIV.CITYID AS CityId, CIV.CITYNAME AS CityName, VP.PARTYADDRESS AS VisitorPassPartyAddress, VP.VISITORID AS VisitorId, CO.NAME AS VisitorName, CO.COMPANY AS VisitorCompany, CO.DESIGNATION AS VisitorDesignation, CO.MOBILENO AS VisitorMobileNo, CO.MAILID AS VisitorMailId, CO.THUMBNAIL AS VisitorThumbNail, CO.DEFAULTIMAGEID AS VisitorDefaultImageId, VP.VISITORNAME AS VisitorPassVisitorName, VP.PURPOSE AS VisitorPassPurpose, VP.DEPARTMENTID AS DepartmentId, DE.DEPARTMENTCODE AS DepartmentCode, DE.DEPARTMENTNAME AS DepartmentName, VP.EMPLOYEEID AS EmployeeId, EM.EMPLOYEECODE AS EmployeeCode, EM.EMPLOYEENAME AS EmployeeName, G.GCMNAME AS EmployeeDesignation, EM.PRIMARYMOBILE AS EmployeePrimaryMobile, EM.PRIMARYEMAILID AS EmployeePrimaryEMailId, EDE.DEPARTMENTNAME AS EmployeeDepartment, LO.LOCATIONNAME AS EmployeeLocation, VP.VERSION AS VisitorPassVersion, VP.STATUS AS VisitorPassStatus, VP.CREATEDBYID AS VisitorPassCreatedById, VP.CREATEDON AS VisitorPassCreatedOn, VP.MODIFIEDBYID AS VisitorPassModifiedById, VP.MODIFIEDON AS VisitorPassModifiedOn, VP.DEFAULTCMSID AS VisitorPassDefaultCmsId, VP.VISITORTYPEID AS VisitorTypeId, VT.GCMCODE AS VisitorTypeCode, VT.GCMNAME AS VisitorTypeName, VP.VISITORPURPOSEID AS VisitPurposeId, GVP.GCMCODE AS VisitPurposeCode, GVP.GCMNAME AS VisitPurposeName, VP.REMARKS AS VisitorPassRemarks, VP.CONTACTIDS AS VisitorPassContactIds, (SELECT STRING_AGG(VC.NAME, ',') FROM STRING_SPLIT(VP.CONTACTIDS, ',') CS INNER JOIN MCONTACT VC ON TRY_CAST(CS.VALUE AS INT) = VC.CONTACTID ) AS VisitorPassContactNames, (SELECT STRING_AGG(VC.COMPANY, ',') FROM STRING_SPLIT(VP.CONTACTIDS, ',') CS INNER JOIN MCONTACT VC ON TRY_CAST(CS.VALUE AS INT) = VC.CONTACTID ) AS VisitorPassContactCompany, (SELECT STRING_AGG(VC.DESIGNATION, ',') FROM STRING_SPLIT(VP.CONTACTIDS, ',') CS INNER JOIN MCONTACT VC ON TRY_CAST(CS.VALUE AS INT) = VC.CONTACTID ) AS VisitorPassContactDesignation, VP.PARTYBRANCHLOCATIONID AS PartyBranchLocationId, PBL.PARTYBRANCHLOCATIONCODE AS PartyBranchLocationCode, PBL.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName, VP.LOCATIONNAME AS VisitorPassLocationName, VPU.VISITORPUNCHID AS VisitorPunchId, VP.VISITORPASSID AS VisitorPassId, VP.PASSNUMBER AS VisitorPassPassNumber, VP.PASSDATE AS VisitorPassPassDate, VPU.PUNCHINDATE AS VisitorPunchPunchInDate, VPU.PUNCHINTIME AS VisitorPunchPunchInTime, RIGHT('0' + CAST(VPU.PUNCHINTIME / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(VPU.PUNCHINTIME % 60 AS VARCHAR), 2) AS VisitorPunchPunchCalInTime, VPU.VEHICLETYPEID AS VehicleTypeId, VHT.GCMCODE AS VehicleTypeCode, VHT.GCMNAME AS VehicleTypeName, VPU.VEHICLEID AS VehicleId, VH.VEHICLENUMBER AS VehicleNumber, VPU.LAPTOPDETAIL AS VisitorPunchLaptopDetail, VPU.CAMERADETAIL AS VisitorPunchCameraDetail, VPU.CARDNUMBER AS VisitorPunchCardNumber, VPU.PUNCHOUTDATE AS VisitorPunchPunchOutDate, VPU.PUNCHOUTTIME AS VisitorPunchPunchOutTime, RIGHT('0' + CAST(VPU.PUNCHOUTTIME / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(VPU.PUNCHOUTTIME % 60 AS VARCHAR), 2) AS VisitorPunchPunchCalOutTime, VPU.REMARKS AS VisitorPunchRemarks, VPU.STARTON AS VisitorPunchStartOn, VPU.ENDON AS VisitorPunchEndOn, VPU.STARTBYID AS StartById, ST.USERCODE AS StartByCode, ST.USERNAME AS StartByName, VPU.ENDBYID AS EndById, EN.USERCODE AS EndByCode, EN.USERNAME AS EndByName, VPU.MEETSTATUS AS VisitorPunchMeetStatus, VPD.VISITORPASSDETAILID AS VisitorPassDetailId, VP.VISITORPASSID AS VisitorPassId, VPD.DETAILTYPE AS VisitorPassDetailDetailType, VPD.PARTICULARS AS VisitorPassDetailParticulars, VPD.REMARKS AS VisitorPassDetailRemarks, VPD.SLNO AS VisitorPassDetailSlNo, VPD.APPLICABLETYPE AS VisitorPassDetailApplicableType, VPD.LOCATION AS VisitorPassDetailLocation FROM TVISITORPASS VP LEFT JOIN MBIZTRANSACTIONTYPE BT ON VP.BIZTRANSACTIONTYPEID = BT.BIZTRANSACTIONTYPEID LEFT JOIN MORGANIZATIONUNIT OU ON VP.OUID = OU.OUID LEFT JOIN MPERIOD PE ON VP.PERIODID = PE.PERIODID LEFT JOIN MEMPLOYEE AP ON VP.APPROVEDBYID = AP.EMPLOYEEID LEFT JOIN MPARTY PA ON VP.PARTYID = PA.PARTYID LEFT JOIN MCONTACT CO ON VP.VISITORID = CO.CONTACTID LEFT JOIN MADDRESS MADV ON CO.CONTACTID = MADV.OBJECTID AND MADV.OBJECTTYPEID = -2147481759 LEFT JOIN MCITY CIV ON MADV.CITYID = CIV.CITYID LEFT JOIN MDEPARTMENT DE ON VP.DEPARTMENTID = DE.DEPARTMENTID LEFT JOIN MEMPLOYEE EM ON VP.EMPLOYEEID = EM.EMPLOYEEID LEFT JOIN MGCM G ON EM.DESIGNATIONID = G.GCMID LEFT JOIN MDEPARTMENT EDE ON EM.DEPARTMENTID = EDE.DEPARTMENTID LEFT JOIN MLOCATION LO ON EM.LOCATIONID = LO.LOCATIONID LEFT JOIN MGCM VT ON VP.VISITORTYPEID = VT.GCMID LEFT JOIN MGCM GVP ON VP.VISITORPURPOSEID = GVP.GCMID LEFT JOIN MPARTYBRANCHLOCATION PBL ON VP.PARTYBRANCHLOCATIONID = PBL.PARTYBRANCHLOCATIONID LEFT JOIN TVISITORPUNCH VPU ON VP.VISITORPASSID = VPU.VISITORPASSID LEFT JOIN MGCM VHT ON VPU.VEHICLETYPEID = VHT.GCMID LEFT JOIN MVEHICLE VH ON VPU.VEHICLEID = VH.VEHICLEID LEFT JOIN MUSER ST ON VPU.STARTBYID = ST.USERID LEFT JOIN MUSER EN ON VPU.ENDBYID = EN.USERID LEFT JOIN TVISITORPASSDETAIL VPD ON VP.VISITORPASSID = VPD.VISITORPASSID WHERE VP.VISITORPASSID = @visitorpassid; "; public const string GET_SELECTLIST_VISITORPASS = @" WITH PagedVisitorPass AS ( SELECT VP.VISITORPASSID AS Id, VP.PASSNUMBER AS PassNumber, VP.PASSDATE AS PassDate, OU.ORGANIZATIONUNITNAME AS OUName, VP.VISITORNAME AS VisitorName, C.MOBILENO AS MobileNumber, C.COMPANY AS Company, C.MAILID AS MailId, C.DESIGNATION AS Designation, DATEADD(MINUTE, VP.VALIDTOTIME, VP.VALIDTODATE) AS ValidToDateTime, ROW_NUMBER() OVER (ORDER BY VP.VISITORPASSID) AS RowNum FROM TVISITORPASS VP LEFT JOIN MORGANIZATIONUNIT OU ON VP.OUID = OU.OUID LEFT JOIN MCONTACT C ON VP.VISITORID = C.CONTACTID WHERE 1 = 1 AND (@searchtext IS NULL OR @searchtext = '' OR VP.PASSNUMBER LIKE '%' + @searchtext + '%' OR OU.ORGANIZATIONUNITNAME LIKE '%' + @searchtext + '%' OR VP.VISITORNAME LIKE '%' + @searchtext + '%' OR C.MOBILENO LIKE '%' + @searchtext + '%') AND (@showcompleted = -1 OR (@showcompleted = 0 AND DATEADD(MINUTE, VP.VALIDTOTIME, VP.VALIDTODATE) < GETDATE()) OR (@showcompleted = 1 AND DATEADD(MINUTE, VP.VALIDTOTIME, VP.VALIDTODATE) >= GETDATE())) ) SELECT Id, PassNumber, PassDate, OUName, VisitorName, MobileNumber, Company, MailId, Designation FROM PagedVisitorPass WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult); "; public const string SAVE_VISITORPASS = @"INSERT INTO TVISITORPASS ( VISITORPASSID, BIZTRANSACTIONTYPEID, OUID, PERIODID, PASSNUMBER, PASSDATE, PASSTYPE, APPROVEDBYID, VALIDFROMDATE, VALIDFROMTIME, VALIDTODATE, VALIDTOTIME, PAX, PARTYID, PARTYNAME, PARTYADDRESS, VISITORID, VISITORNAME, PURPOSE, DEPARTMENTID, EMPLOYEEID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DEFAULTCMSID, VISITORTYPEID, VISITORPURPOSEID, REMARKS, CONTACTIDS, PARTYBRANCHLOCATIONID, LOCATIONNAME ) VALUES ( @VisitorPassId, @BIZTransactionTypeId, @OUId, @PeriodId, @VisitorPassPassNumber, @VisitorPassPassDate, @VisitorPassPassType, @ApprovedByIdId, @VisitorPassValidFromDate, @VisitorPassValidFromTime, @VisitorPassValidToDate, @VisitorPassValidToTime, @VisitorPassPAX, @PartyId, @VisitorPassPartyName, @VisitorPassPartyAddress, @VisitorId, @VisitorPassVisitorName, @VisitorPassPurpose, @DepartmentId, @EmployeeId, @VisitorPassVersion, @VisitorPassStatus, @VisitorPassCreatedById, @VisitorPassCreatedOn, @VisitorPassModifiedById, @VisitorPassModifiedOn, @VisitorPassDefaultCmsId, @VisitorTypeId, @VisitPurposeId, @VisitorPassRemarks, @VisitorPassContactIds, @PartyBranchLocationId, @VisitorPassLocationName ); "; public const string UPDATE_VISITORPASS = @"UPDATE TVISITORPASS SET BIZTRANSACTIONTYPEID = @BIZTransactionTypeId, OUID = @OUId, PERIODID = @PeriodId, PASSNUMBER = @VisitorPassPassNumber, PASSDATE = @VisitorPassPassDate, PASSTYPE = @VisitorPassPassType, APPROVEDBYID = @ApprovedByIdId, VALIDFROMDATE = @VisitorPassValidFromDate, VALIDFROMTIME = @VisitorPassValidFromTime, VALIDTODATE = @VisitorPassValidToDate, VALIDTOTIME = @VisitorPassValidToTime, PAX = @VisitorPassPAX, PARTYID = @PartyId, PARTYNAME = @VisitorPassPartyName, PARTYADDRESS = @VisitorPassPartyAddress, VISITORID = @VisitorId, VISITORNAME = @VisitorPassVisitorName, PURPOSE = @VisitorPassPurpose, DEPARTMENTID = @DepartmentId, EMPLOYEEID = @EmployeeId, VERSION = @VisitorPassVersion, STATUS = @VisitorPassStatus, MODIFIEDBYID = @VisitorPassModifiedById, MODIFIEDON = @VisitorPassModifiedOn, DEFAULTCMSID = @VisitorPassDefaultCmsId, VISITORTYPEID = @VisitorTypeId, VISITORPURPOSEID = @VisitPurposeId, REMARKS = @VisitorPassRemarks, CONTACTIDS = @VisitorPassContactIds, PARTYBRANCHLOCATIONID = @PartyBranchLocationId, LOCATIONNAME = @VisitorPassLocationName WHERE VISITORPASSID = @VisitorPassId; "; public const string SAVE_VISITORPASSDETAIL = @"INSERT INTO TVISITORPASSDETAIL ( VISITORPASSDETAILID, VISITORPASSID, DETAILTYPE, SLNO, PARTICULARS, REMARKS, APPLICABLETYPE, LOCATION ) VALUES ( @VisitorPassDetailId, @VisitorPassId, @VisitorPassDetailDetailType, @VisitorPassDetailSlNo, @VisitorPassDetailParticulars, @VisitorPassDetailRemarks, @VisitorPassDetailApplicableType, @VisitorPassDetailLocation ); "; public const string UPDATE_VISITORPASSDETAIL = @"UPDATE TVISITORPASSDETAIL SET DETAILTYPE = @VisitorPassDetailDetailType, SLNO = @VisitorPassDetailSlNo, PARTICULARS = @VisitorPassDetailParticulars, REMARKS = @VisitorPassDetailRemarks, APPLICABLETYPE = @VisitorPassDetailApplicableType, LOCATION = @VisitorPassDetailLocation WHERE VISITORPASSDETAILID = @VisitorPassDetailId; "; public const string SAVE_VISITORPUNCH = @"INSERT INTO TVISITORPUNCH ( VISITORPUNCHID, VISITORPASSID, PUNCHINDATE, PUNCHINTIME, VEHICLETYPEID, VEHICLEID, LAPTOPDETAIL, CAMERADETAIL, CARDNUMBER, PUNCHOUTDATE, PUNCHOUTTIME, REMARKS, STARTON, ENDON, STARTBYID, ENDBYID, MEETSTATUS ) VALUES ( @VisitorPunchId, @VisitorPassId, @VisitorPunchPunchInDate, @VisitorPunchPunchInTime, @VehicleTypeId, @VehicleId, @VisitorPunchLaptopDetail, @VisitorPunchCameraDetail, @VisitorPunchCardNumber, @VisitorPunchPunchOutDate, @VisitorPunchPunchOutTime, @VisitorPunchRemarks, @VisitorPunchStartOn, @VisitorPunchEndOn, @StartById, @EndById, @VisitorPunchMeetStatus ); "; public const string UPDATE_VISITORPUNCH = @"UPDATE TVISITORPUNCH SET PUNCHINDATE = @VisitorPunchPunchInDate, PUNCHINTIME = @VisitorPunchPunchInTime, VEHICLETYPEID = @VehicleTypeId, VEHICLEID = @VehicleId, LAPTOPDETAIL = @VisitorPunchLaptopDetail, CAMERADETAIL = @VisitorPunchCameraDetail, CARDNUMBER = @VisitorPunchCardNumber, PUNCHOUTDATE = @VisitorPunchPunchOutDate, PUNCHOUTTIME = @VisitorPunchPunchOutTime, REMARKS = @VisitorPunchRemarks, STARTON = @VisitorPunchStartOn, ENDON = @VisitorPunchEndOn, STARTBYID = @StartById, ENDBYID = @EndById, MEETSTATUS = @VisitorPunchMeetStatus WHERE VISITORPUNCHID = @VisitorPunchId; "; public const string DELETE_VISITORPASS = @"DELETE FROM TVISITORPASS WHERE VISITORPASSID = @visitorpassid; "; public const string DELETE_VISITORPASS_PUNCH = @"DELETE FROM TVISITORPUNCH WHERE VISITORPASSID = @visitorpassid; "; public const string DELETE_VISITORPASS_DETAIL = @"DELETE FROM TVISITORPASSDETAIL WHERE VISITORPASSID = @visitorpassid; "; public const string GET_MIN_VISITORPASSDETAILID = @"SELECT ISNULL(MIN(VISITORPASSDETAILID),0) FROM TVISITORPASSDETAIL; "; public const string GET_MIN_VISITORPUNCHID = @"SELECT ISNULL(MIN(VISITORPUNCHID),0) FROM TVISITORPUNCH; "; } }