namespace FMDAL.Query.AssetActivity { // Confirmed against the real MASSETACTIVITY DDL via the legacy NHibernate mapping // (DAL/FMDAL/HibernateMapFile/AssetActivity.hbm.xml). Like MASSETTYPEACTIVITY, this table // carries no STATUS/VERSION/CREATEDBYID/etc audit trail — confirmed by both the mapping and // the existing AssetActivityDTO's matching absence of those fields. NATURE/CRONDESCRIPTION // appear in the mapping with a trailing space in the column name attribute // (column="NATURE " / column="CRONDESCRIPTION ") — treated here as a mapping-file formatting // artifact, not a literal trailing-space column name. public static class AssetActivityQB { public const string GET_ASSETACTIVITY = @"SELECT AA.ASSETACTIVITYID AS AssetActivityId, AA.ASSETID AS AssetId, AA.SLNO AS AssetActivitySlno, AA.ACTIVITYID AS ActivityId, AA.LASTREADINGON AS AssetActivityLastReadingOn, AA.LASTREADINGREMARKS AS AssetActivityLastReadingRemarks, AA.CRONEXPRESSION AS AssetActivityCronExpression, AA.LASTGENERATEDTILL AS AssetActivityLastGeneratedTill, AA.REMINDERTIME AS AssetActivityRemainderTime, AA.OVERDUEREMINDERTIME AS AssetActivityOverDueRemainderTime, AA.GRACETIME AS AssetActivityGraceTime, AA.TICKETTIME AS AssetActivityTicketTime, AA.AUTOCLOSETIME AS AssetActivityAutoCloseTime, AA.TASKUSERGROUPID AS TaskUserGroupId, AA.TICKETQUEUENAME AS AssetActivityTicketQueueName, AA.SELECTEDMONTH AS AssetActivitySelectedMonth, AA.SELECTEDDAYS AS AssetActivitySelectedDays, AA.DATETYPE AS AssetActivityDateType, AA.SELECTEDDATES AS AssetActivitySelectedDates, AA.OCCURENCENO AS AssetActivityOccurenceNo, AA.OCCURENCEDAY AS AssetActivityOccurenceDay, AA.EVERYNOOFMONTHS AS AssetActivityEveryNoOfMonths, AA.EVERYNOOFDAYS AS AssetActivityEveryNoOfDays, AA.TIMERANGETYPE AS AssetActivityTimeRangeType, AA.EVERYNOOFHOURS AS AssetActivityEveryNoOfHours, AA.EVERYNOOFMINUTES AS AssetActivityEveryNoOfMinutes, AA.OCCURSATTIME AS AssetActivityOccursAtTime, AA.OCCURSATMULTIPLETIME AS AssetActivityOccursAtMultipleTime, AA.TASKASSIGNEDTYPE AS AssetActivityTaskAssignedType, AA.DURATION AS AssetActivityDuration, AA.NATURE AS AssetActivityNature, AA.CRONDESCRIPTION AS AssetActivityCronDescription, AA.TASKUSERID AS UserId, AA.TASKROLEID AS RoleId, AA.TICKETQUEUEID AS TicketQueueId, AA.PARAMETERSETID AS ParameterSetId, AA.MACHINEID AS MachineId, AA.RESOURCEID AS ResourceId, AA.MACHINETYPEID AS MachineTypeId, AA.RESOURCETYPEID AS ResourceTypeId, AA.EMPLOYEEID AS EmployeeId, AA.VEHICLEID AS VehicleId FROM MASSETACTIVITY AA WHERE AA.ASSETACTIVITYID = @AssetActivityId;"; public const string GET_ASSETACTIVITY_LIST_BY_ASSET = @"SELECT AA.ASSETACTIVITYID AS AssetActivityId, AA.ASSETID AS AssetId, AA.SLNO AS AssetActivitySlno, AA.ACTIVITYID AS ActivityId, AA.LASTREADINGON AS AssetActivityLastReadingOn, AA.LASTREADINGREMARKS AS AssetActivityLastReadingRemarks, AA.CRONEXPRESSION AS AssetActivityCronExpression, AA.LASTGENERATEDTILL AS AssetActivityLastGeneratedTill, AA.REMINDERTIME AS AssetActivityRemainderTime, AA.OVERDUEREMINDERTIME AS AssetActivityOverDueRemainderTime, AA.GRACETIME AS AssetActivityGraceTime, AA.TICKETTIME AS AssetActivityTicketTime, AA.AUTOCLOSETIME AS AssetActivityAutoCloseTime, AA.TASKUSERGROUPID AS TaskUserGroupId, AA.TICKETQUEUENAME AS AssetActivityTicketQueueName, AA.SELECTEDMONTH AS AssetActivitySelectedMonth, AA.SELECTEDDAYS AS AssetActivitySelectedDays, AA.DATETYPE AS AssetActivityDateType, AA.SELECTEDDATES AS AssetActivitySelectedDates, AA.OCCURENCENO AS AssetActivityOccurenceNo, AA.OCCURENCEDAY AS AssetActivityOccurenceDay, AA.EVERYNOOFMONTHS AS AssetActivityEveryNoOfMonths, AA.EVERYNOOFDAYS AS AssetActivityEveryNoOfDays, AA.TIMERANGETYPE AS AssetActivityTimeRangeType, AA.EVERYNOOFHOURS AS AssetActivityEveryNoOfHours, AA.EVERYNOOFMINUTES AS AssetActivityEveryNoOfMinutes, AA.OCCURSATTIME AS AssetActivityOccursAtTime, AA.OCCURSATMULTIPLETIME AS AssetActivityOccursAtMultipleTime, AA.TASKASSIGNEDTYPE AS AssetActivityTaskAssignedType, AA.DURATION AS AssetActivityDuration, AA.NATURE AS AssetActivityNature, AA.CRONDESCRIPTION AS AssetActivityCronDescription, AA.TASKUSERID AS UserId, AA.TASKROLEID AS RoleId, AA.TICKETQUEUEID AS TicketQueueId, AA.PARAMETERSETID AS ParameterSetId, AA.MACHINEID AS MachineId, AA.RESOURCEID AS ResourceId, AA.MACHINETYPEID AS MachineTypeId, AA.RESOURCETYPEID AS ResourceTypeId, AA.EMPLOYEEID AS EmployeeId, AA.VEHICLEID AS VehicleId FROM MASSETACTIVITY AA WHERE AA.ASSETID = @AssetId ORDER BY AA.SLNO;"; public const string SAVE_ASSETACTIVITY = @"INSERT INTO MASSETACTIVITY ( ASSETACTIVITYID, ASSETID, SLNO, ACTIVITYID, LASTREADINGON, LASTREADINGREMARKS, CRONEXPRESSION, LASTGENERATEDTILL, REMINDERTIME, OVERDUEREMINDERTIME, GRACETIME, TICKETTIME, AUTOCLOSETIME, TASKUSERGROUPID, TICKETQUEUENAME, SELECTEDMONTH, SELECTEDDAYS, DATETYPE, SELECTEDDATES, OCCURENCENO, OCCURENCEDAY, EVERYNOOFMONTHS, EVERYNOOFDAYS, TIMERANGETYPE, EVERYNOOFHOURS, EVERYNOOFMINUTES, OCCURSATTIME, OCCURSATMULTIPLETIME, TASKASSIGNEDTYPE, DURATION, NATURE, CRONDESCRIPTION, TASKUSERID, TASKROLEID, TICKETQUEUEID, PARAMETERSETID, MACHINEID, RESOURCEID, MACHINETYPEID, RESOURCETYPEID, EMPLOYEEID, VEHICLEID) VALUES ( @AssetActivityId, @AssetId, @AssetActivitySlno, @ActivityId, @AssetActivityLastReadingOn, @AssetActivityLastReadingRemarks, @AssetActivityCronExpression, @AssetActivityLastGeneratedTill, @AssetActivityRemainderTime, @AssetActivityOverDueRemainderTime, @AssetActivityGraceTime, @AssetActivityTicketTime, @AssetActivityAutoCloseTime, @TaskUserGroupId, @AssetActivityTicketQueueName, @AssetActivitySelectedMonth, @AssetActivitySelectedDays, @AssetActivityDateType, @AssetActivitySelectedDates, @AssetActivityOccurenceNo, @AssetActivityOccurenceDay, @AssetActivityEveryNoOfMonths, @AssetActivityEveryNoOfDays, @AssetActivityTimeRangeType, @AssetActivityEveryNoOfHours, @AssetActivityEveryNoOfMinutes, @AssetActivityOccursAtTime, @AssetActivityOccursAtMultipleTime, @AssetActivityTaskAssignedType, @AssetActivityDuration, @AssetActivityNature, @AssetActivityCronDescription, @UserId, @RoleId, @TicketQueueId, @ParameterSetId, @MachineId, @ResourceId, @MachineTypeId, @ResourceTypeId, @EmployeeId, @VehicleId);"; public const string UPDATE_ASSETACTIVITY = @"UPDATE MASSETACTIVITY SET ASSETID = @AssetId, SLNO = @AssetActivitySlno, ACTIVITYID = @ActivityId, LASTREADINGON = @AssetActivityLastReadingOn, LASTREADINGREMARKS = @AssetActivityLastReadingRemarks, CRONEXPRESSION = @AssetActivityCronExpression, LASTGENERATEDTILL = @AssetActivityLastGeneratedTill, REMINDERTIME = @AssetActivityRemainderTime, OVERDUEREMINDERTIME = @AssetActivityOverDueRemainderTime, GRACETIME = @AssetActivityGraceTime, TICKETTIME = @AssetActivityTicketTime, AUTOCLOSETIME = @AssetActivityAutoCloseTime, TASKUSERGROUPID = @TaskUserGroupId, TICKETQUEUENAME = @AssetActivityTicketQueueName, SELECTEDMONTH = @AssetActivitySelectedMonth, SELECTEDDAYS = @AssetActivitySelectedDays, DATETYPE = @AssetActivityDateType, SELECTEDDATES = @AssetActivitySelectedDates, OCCURENCENO = @AssetActivityOccurenceNo, OCCURENCEDAY = @AssetActivityOccurenceDay, EVERYNOOFMONTHS = @AssetActivityEveryNoOfMonths, EVERYNOOFDAYS = @AssetActivityEveryNoOfDays, TIMERANGETYPE = @AssetActivityTimeRangeType, EVERYNOOFHOURS = @AssetActivityEveryNoOfHours, EVERYNOOFMINUTES = @AssetActivityEveryNoOfMinutes, OCCURSATTIME = @AssetActivityOccursAtTime, OCCURSATMULTIPLETIME = @AssetActivityOccursAtMultipleTime, TASKASSIGNEDTYPE = @AssetActivityTaskAssignedType, DURATION = @AssetActivityDuration, NATURE = @AssetActivityNature, CRONDESCRIPTION = @AssetActivityCronDescription, TASKUSERID = @UserId, TASKROLEID = @RoleId, TICKETQUEUEID = @TicketQueueId, PARAMETERSETID = @ParameterSetId, MACHINEID = @MachineId, RESOURCEID = @ResourceId, MACHINETYPEID = @MachineTypeId, RESOURCETYPEID = @ResourceTypeId, EMPLOYEEID = @EmployeeId, VEHICLEID = @VehicleId WHERE ASSETACTIVITYID = @AssetActivityId;"; public const string DELETE_ASSETACTIVITY = @"DELETE FROM MASSETACTIVITY WHERE ASSETACTIVITYID = @AssetActivityId;"; // Scheduler support — every row with a configured CRONEXPRESSION is a scheduling // candidate; the job evaluates each row's own cron via Quartz.CronExpression against // LASTGENERATEDTILL to decide whether it is actually due. No TENANTID filter — confirmed // absent on this table (see header note). public const string GET_ASSETACTIVITY_DUE_FOR_SCHEDULING = @"SELECT AA.ASSETACTIVITYID AS AssetActivityId, AA.ASSETID AS AssetId, AA.SLNO AS AssetActivitySlno, AA.ACTIVITYID AS ActivityId, ACT.ACTIVITYNAME AS ActivityName, AA.LASTREADINGON AS AssetActivityLastReadingOn, AA.LASTREADINGREMARKS AS AssetActivityLastReadingRemarks, AA.CRONEXPRESSION AS AssetActivityCronExpression, AA.LASTGENERATEDTILL AS AssetActivityLastGeneratedTill, AA.REMINDERTIME AS AssetActivityRemainderTime, AA.OVERDUEREMINDERTIME AS AssetActivityOverDueRemainderTime, AA.GRACETIME AS AssetActivityGraceTime, AA.TICKETTIME AS AssetActivityTicketTime, AA.AUTOCLOSETIME AS AssetActivityAutoCloseTime, AA.TASKUSERGROUPID AS TaskUserGroupId, AA.TICKETQUEUENAME AS AssetActivityTicketQueueName, AA.SELECTEDMONTH AS AssetActivitySelectedMonth, AA.SELECTEDDAYS AS AssetActivitySelectedDays, AA.DATETYPE AS AssetActivityDateType, AA.SELECTEDDATES AS AssetActivitySelectedDates, AA.OCCURENCENO AS AssetActivityOccurenceNo, AA.OCCURENCEDAY AS AssetActivityOccurenceDay, AA.EVERYNOOFMONTHS AS AssetActivityEveryNoOfMonths, AA.EVERYNOOFDAYS AS AssetActivityEveryNoOfDays, AA.TIMERANGETYPE AS AssetActivityTimeRangeType, AA.EVERYNOOFHOURS AS AssetActivityEveryNoOfHours, AA.EVERYNOOFMINUTES AS AssetActivityEveryNoOfMinutes, AA.OCCURSATTIME AS AssetActivityOccursAtTime, AA.OCCURSATMULTIPLETIME AS AssetActivityOccursAtMultipleTime, AA.TASKASSIGNEDTYPE AS AssetActivityTaskAssignedType, AA.DURATION AS AssetActivityDuration, AA.NATURE AS AssetActivityNature, AA.CRONDESCRIPTION AS AssetActivityCronDescription, AA.TASKUSERID AS UserId, AA.TASKROLEID AS RoleId, AA.TICKETQUEUEID AS TicketQueueId, AA.PARAMETERSETID AS ParameterSetId, AA.MACHINEID AS MachineId, AA.RESOURCEID AS ResourceId, AA.MACHINETYPEID AS MachineTypeId, AA.RESOURCETYPEID AS ResourceTypeId, AA.EMPLOYEEID AS EmployeeId, AA.VEHICLEID AS VehicleId FROM MASSETACTIVITY AA LEFT JOIN MACTIVITY ACT ON ACT.ACTIVITYID = AA.ACTIVITYID WHERE AA.CRONEXPRESSION IS NOT NULL AND AA.CRONEXPRESSION <> '';"; public const string UPDATE_ASSETACTIVITY_LASTGENERATEDTILL = @"UPDATE MASSETACTIVITY SET LASTGENERATEDTILL = @LastGeneratedTill WHERE ASSETACTIVITYID = @AssetActivityId;"; } }