Micros 3700 Labor Times

[pmegan]

Hi,

Our payroll periods are changing, and I'd like to take advantage and update our exports and reports. They currently determine the pay period manually, using the latest Sunday as the end date and offsetting back to the previous Monday to get the start date. I'd like to change this and use the pay periods or even the current labor week, but can't find where this data is stored.

Does anybody know where the labor periods are held in a non-LM 3700? There's an option in Report Date Ranges for Start of Payroll Period and End of Payroll Period so it's got to be in the database somewhere.

Thanks

 

[Moregelen]

If the current payroll's week start day is explicitly set somewhere in the database, I could never find it, even after an entire day wasted looking. I gave up at that point and just used the 'seed' date to calculate it:

[COLOR=#000000]        mdb.Connect();
        var psd = mdb.Query([/color][COLOR=#A31515]"select pay_period_start_date from micros.time_clock_def"[/color][COLOR=#000000]);
        [/color][COLOR=#0000FF]if[/color][COLOR=#000000] (psd == [/color][COLOR=#0000FF]null[/color][COLOR=#000000])
        {
            WriteLogMessage([/color][COLOR=#A31515]"CRITICAL ERROR: A null start date was found! Data is corrupt! Exiting!"[/color][COLOR=#000000]);
            [/color][COLOR=#0000FF]return[/color][COLOR=#000000];
        }
        DateTime payroll_start_date = (DateTime)psd.Rows[0][[/color][COLOR=#A31515]"pay_period_start_date"[/color][COLOR=#000000]];
        [/color][COLOR=#0000FF]int[/color][COLOR=#000000] difference = (DateTime.Today - payroll_start_date).Days;
        [/color][COLOR=#0000FF]int[/color][COLOR=#000000] weeks = difference / 14;
        DateTime startDate = payroll_start_date.AddDays(weeks * 14);
        DataTable payrollData = mdb.RunStoredProcedure([/color][COLOR=#A31515]"sp_R_timecard_and_sales"[/color][COLOR=#000000], 
            [/color][COLOR=#0000FF]new[/color][COLOR=#000000] SPParam[]
            { 
                [/color][COLOR=#0000FF]new[/color][COLOR=#000000] SPParam([/color][COLOR=#A31515]"p_begindate"[/color][COLOR=#000000], startDate.AddDays(-14).ToString([/color][COLOR=#A31515]"yyyy-MM-dd"[/color][COLOR=#000000])), 
                [/color][COLOR=#0000FF]new[/color][COLOR=#000000] SPParam([/color][COLOR=#A31515]"p_enddate"[/color][COLOR=#000000], startDate.AddDays(-1).ToString([/color][COLOR=#A31515]"yyyy-MM-dd"[/color][COLOR=#000000])) 
            }
         );[/color]

 

[pmegan]

Yeah, that's exactly the kind of stuff I've been doing too and was hoping to avoid. I think I'm going to just add a report date range, write a stored procedure and pull start/end dates from sp_parm_temp.

And now comes the joy of linking tips to a business date.

 

[Moregelen]

Hah. Thats what our custom stored procedure is for. I look at it and it makes my head hurt. So glad I didn't have to write it.