Hello all,
I have been working on a scheduled gateway script that will take values from one table and insert them into another table with some additional values in order to automate a "Charge Hours" page where the manager will charge the hours to the different crafts. I will run this script every sunday in order to handle the weekend overtime.
Included below is my script in it's current state and I will talk about it after:
def onScheduledEvent():
#insert MOS_Signup to HoursChargedHistorical: approved sat to SaturdayOvertime, approved sun to SundayOvertime, denied sat homeshift priority 1 to SaturdayRejected for 10 hours,
#denied sun homeshift priority 1 to SundayRejected for 10 hours, denied sat offshift priority 1 to SaturdayRejected for 2 hours, denied sun offshift priority 1 to SundayRejected for 2 hours,
#Variables
SundayDate = system.tag.readBlocking("[default]MOS/Date End")[0]
Sunday = SundayDate.value
SaturdayDate = system.tag.readBlocking("[default]MOS/Saturday Date")[0]
Saturday = SaturdayDate.value
#Saturday
returnedData = system.db.runNamedQuery("Overtime/Charge Hours/Info", {"Date" :Saturday})
for row in range(returnedData.getRowCount()):
Status = returnedData.getValueAt(row, "Status")
Shift = returnedData.getValueAt(row, "Shift")
Craft = returnedData.getValueAt(row, "TradeSkill")
LastName = returnedData.getValueAt(row, "Lname")
FirstName = returnedData.getValueAt(row, "Fname")
ClockNumber = returnedData.getValueAt(row, "EID")
CurrentHours = returnedData.getValueAt(row, "CurrentHours")
ApprovedShift = returnedData.getValueAt(row, "ApprovedShift")
ApprovedSecondShift = returnedData.getValueAt(row, "ApprovedSecondShift")
Midnights = returnedData.getValueAt(row, "FirstShift")
Days = returnedData.getValueAt(row, "SecondShift")
Afternoons = returnedData.getValueAt(row, "ThirdShift")
Time = system.date.now()
Timestamp = system.date.format(Time, "MM/dd/YYYY hh:mm:ss a")
if Status == 1 and ApprovedShift != 0 and ApprovedSecondShift == 0:
SaturdayOvertime = 8
SaturdayRejected = 0
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = SaturdayOvertime
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Saturday Insert", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "Craft" :Craft, "Shift" :Shift, "SaturdayOvertime" :SaturdayOvertime, "SaturdayRejected" :SaturdayRejected, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime, "Timestamp" :Timestamp})
elif Status == 1 and ApprovedShift != 0 and ApprovedSecondShift != 0:
SaturdayOvertime = 16
SaturdayRejected = 0
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = SaturdayOvertime
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Saturday Insert", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "Craft" :Craft, "Shift" :Shift, "SaturdayOvertime" :SaturdayOvertime, "SaturdayRejected" :SaturdayRejected, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime, "Timestamp" :Timestamp})
elif Status != 1 and (Midnights == 1 and Shift == 1) or (Days == 1 and Shift == 2) or (Afternoons == 1 and Shift == 3) or (Midnights == 4 and Shift == 1) or (Days == 4 and Shift == 2) or (Afternoons == 4 and Shift == 3):
SaturdayOvertime = 0
SaturdayRejected = 10
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = SaturdayRejected
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Saturday Insert", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "Craft" :Craft, "Shift" :Shift, "SaturdayOvertime" :SaturdayOvertime, "SaturdayRejected" :SaturdayRejected, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime, "Timestamp" :Timestamp})
elif Status != 1 and (Midnights == 1 and Shift != 1) or (Days == 1 and Shift != 2) or (Afternoons == 1 and Shift != 3) or (Midnights == 4 and Shift != 1) or (Days == 4 and Shift != 2) or (Afternoons == 4 and Shift != 3):
SaturdayOvertime = 0
SaturdayRejected = 2
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = SaturdayRejected
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Saturday Insert", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "Craft" :Craft, "Shift" :Shift, "SaturdayOvertime" :SaturdayOvertime, "SaturdayRejected" :SaturdayRejected, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime, "Timestamp" :Timestamp})
else:
SaturdayOvertime = 0
SaturdayRejected = 0
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = 0
TotalYearlyOvertime = CurrentHours
system.db.runNamedQuery("Overtime/Charge Hours/Saturday Insert", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "Craft" :Craft, "Shift" :Shift, "SaturdayOvertime" :SaturdayOvertime, "SaturdayRejected" :SaturdayRejected, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime, "Timestamp" :Timestamp})
#Sunday (needs edited)
returnedData1 = system.db.runNamedQuery("Overtime/Charge Hours/Info", {"Date" :Sunday})
for row in range(returnedData1.getRowCount()):
Status = returnedData1.getValueAt(row, "Status")
Shift = returnedData1.getValueAt(row, "Shift")
LastName = returnedData1.getValueAt(row, "Lname")
FirstName = returnedData1.getValueAt(row, "Fname")
ClockNumber = returnedData1.getValueAt(row, "EID")
CurrentHours = returnedData1.getValueAt(row, "CurrentHours") #Watch this, make sure it is new current after saturday instead of before
ApprovedShift = returnedData1.getValueAt(row, "ApprovedShift")
ApprovedSecondShift = returnedData1.getValueAt(row, "ApprovedSecondShift")
Midnights = returnedData1.getValueAt(row, "FirstShift")
Days = returnedData1.getValueAt(row, "SecondShift")
Afternoons = returnedData1.getValueAt(row, "ThirdShift")
if Status == 1 and ApprovedShift != 0 and ApprovedSecondShift == 0:
SundayOvertime = 8
SundayRejected = 0
TotalWeekOvertime = SaturdayOvertime + SaturdayRejected + SundayOvertime
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Sunday Update", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime})
elif Status == 1 and ApprovedShift != 0 and ApprovedSecondShift != 0:
SundayOvertime = 16
SundayRejected = 0
TotalWeekOvertime = SaturdayOvertime + SaturdayRejected + SundayOvertime
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Sunday Update", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime})
elif Status != 1 and (Midnights == 1 and Shift == 1) or (Days == 1 and Shift == 2) or (Afternoons == 1 and Shift == 3) or (Midnights == 4 and Shift == 1) or (Days == 4 and Shift == 2) or (Afternoons == 4 and Shift == 3):
SundayOvertime = 0
SundayRejected = 10
TotalWeekOvertime = SaturdayOvertime + SaturdayRejected + SundayRejected
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Sunday Update", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime})
elif Status != 1 and (Midnights == 1 and Shift != 1) or (Days == 1 and Shift != 2) or (Afternoons == 1 and Shift != 3) or (Midnights == 4 and Shift != 1) or (Days == 4 and Shift != 2) or (Afternoons == 4 and Shift != 3):
SundayOvertime = 0
SundayRejected = 2
TotalWeekOvertime = SaturdayOvertime + SaturdayRejected + SundayRejected
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Sunday Update", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime})
else:
SundayOvertime = 0
SundayRejected = 0
TotalWeekOvertime = SaturdayOvertime + SaturdayRejected
TotalYearlyOvertime = CurrentHours + TotalWeekOvertime
system.db.runNamedQuery("Overtime/Charge Hours/Sunday Update", {"OvertimeWeek" :Saturday, "ClockNumber" :ClockNumber, "LastName" :LastName, "FirstName" :FirstName, "SundayOvertime" :SundayOvertime, "SundayRejected" :SundayRejected, "TotalWeekOvertime" :TotalWeekOvertime, "TotalYearlyOvertime" :TotalYearlyOvertime})
So, the way the script works is it takes all values from a signup table and awards hours as follows:
Signed up and accepted for 1 shift = 8 hours, signed up and accepted for 2 shifts = 16 hours, signed up and rejected for their home shift = 10 hours and signed up and rejected for an off shift = 2 hours. These variables would be: Status for accepted - 1 being accepted and 2 being denied, ApprovedShift being anything but 0 is approved, approvedsecondshift being anything but 0 is approved. The script loops through these signups looking at these values and then inserts it into a charge hours table with the awarded hours.
The script works well for inserting the values for Saturday through the first for loop. It also works well with the updating of already existing records Sunday values (with the exception of the TotalWeekOvertime showing 10 for some reason when there is a 2 in SaturdayRejected and a 2 in SundayRejected, should be 4...).
What I need is to figure out how to add new records for employees who signed up for sunday and didn't sign up for saturday. I have a sunday insert query, a count query and a saturday info query that pulls the SaturdayOvertime and SaturdayRejected values from the table based on clocknumber. I was trying to fit these into the second for loop somewhere but couldn't seem to figure it out. Here are the queries I have in SQL.
Overtime/Charge Hours/Info:
SELECT Status, Shift, Lname, Fname, EID, CurrentHours, ApprovedShift, ApprovedSecondShift, FirstShift, SecondShift, ThirdShift, TradeSkill
FROM [Maintenance].[dbo].[MOS_Signup]
WHERE DateTime = :Date
Overtime/Charge Hours/Saturday Insert
INSERT INTO [Maintenance].[dbo].[MOS_HoursChargedHistorical] (OvertimeWeek, ClockNumber, LastName, FirstName, Craft, Shift, WeeklyOvertime, SaturdayOvertime, SaturdayRejected, SundayOvertime, SundayRejected, TotalWeekOvertime, TotalYearlyOvertime, Username, ChargeTimestamp)
VALUES (:OvertimeWeek, :ClockNumber, :LastName, :FirstName, :Craft, :Shift, 0, :SaturdayOvertime, :SaturdayRejected, :SundayOvertime, :SundayRejected, :TotalWeekOvertime, :TotalYearlyOvertime, 'AUTO', :Timestamp)
Overtime/Charge Hours/Sunday Update
UPDATE [Maintenance].[dbo].[MOS_HoursChargedHistorical]
SET SundayOvertime = :SundayOvertime, SundayRejected = :SundayRejected, TotalWeekOvertime = :TotalWeekOvertime, TotalYearlyOvertime = :TotalYearlyOvertime
WHERE FirstName = :FirstName and LastName = :LastName and ClockNumber = :ClockNumber and OvertimeWeek = :OvertimeWeek
Overtime/Charge Hours/Saturday Count
SELECT COUNT(ClockNumber)
FROM [Maintenance].[dbo].[MOS_HoursChargedHistorical]
WHERE OvertimeWeek = :Date and ClockNumber = :ClockNumber
Overtime/Charge Hours/Saturday Info
SELECT SaturdayOvertime, SaturdayRejected
FROM [Maintenance].[dbo].[MOS_HoursChargedHistorical]
WHERE OvertimeWeek = :Date and ClockNumber = :ClockNumber
Overtime/Charge Hours/Sunday Insert
INSERT INTO [Maintenance].[dbo].[MOS_HoursChargedHistorical] (OvertimeWeek, ClockNumber, LastName, FirstName, Craft, Shift, WeeklyOvertime, SaturdayOvertime, SaturdayRejected, SundayOvertime, SundayRejected, TotalWeekOvertime, TotalYearlyOvertime, Username, ChargeTimestamp)
VALUES (:OvertimeWeek, :ClockNumber, :LastName, :FirstName, :Craft, :Shift, 0, 0, 0, :SundayOvertime, :SundayRejected, :TotalWeekOvertime, :TotalYearlyOvertime, 'AUTO', :Timestamp)