Board index » delphi » Managing Production Day and Shift

2006-11-20 03:34:41 AM
delphi27
MS/SQL 2000
Delphi 2006 Ent
This should not be hard but it is kicking my {*word*82}right now. And I swear I
have done this before.
I am righting a middle-ware app that retrieves data from a PLC controller
and writes it to the database.
The data has :
Line
Group
Weight
DateTime
and other stuff
The controller is capable of caching the data for up to 12 hours so the
DateTime record may not be the same as system datatime.
The plants where I will install this software often do not start and stop
their day at midnight. They have a Production day which may not be the same
as the present day.
For exampl, if they start production at 10:45 PM, the records gernerated
between 10:45 PM and 11:59:59 will be assigned to the production day of the
following day. I believe I have Production day figured.
I have a simple setup GUI with a ComboBox and two radio buttons.
case rgMidNightOffset.ItemIndex of
0: GetTimeAfterMidnight;
1: GetTimeBeforeMidnight;
end;
procedure TfrmLots.GetTimeAfterMidnight;
begin
cbOffset.Clear;
cbOffset.Items.Add('None');
cbOffset.Items.Add('1 AM');
cbOffset.Items.Add('2 AM');
cbOffset.Items.Add('3 AM');
...
end;
procedure TfrmLots.GetTimeBeforeMidnight;
begin
cbOffset.Clear;
cbOffset.Items.Add('None');
cbOffset.Items.Add('11 PM');
cbOffset.Items.Add('10 PM');
cbOffset.Items.Add(' 9 PM');
...
end;
===========================================================================
Declare @MyDate DateTime,
@HoursMultiple Int,
@TypeOffset Int,
@AmtOffSet Int,
@AdjDateTime DateTime,
@ProdWorkDate DateTime
Set @MyDate = :WeighTime
Set @TypeOffset = :TypeOffset
Set @AmtOffSet = :AmtOffSet
Set @HoursMultiple = (case @TypeOffset
when 0 then -1 -- After midnight
when 1 then 1 -- before midnight
when -1 then 0 -- no offset
end)
Set @AdjDateTime = DATEADD ( hh ,(@AmtOffSet * @HoursMultiple ) , @MyDate )
Set @ProdWorkDate = DateAdd(d,DATEDIFF(d,0,@AdjDateTime),0)
print @ProdWorkDate
===========================================================================
This always returns the correct Production work day.
What is eating my lunch are the Shifts.
A shift may or may not cross midnight though only one shift will.
It may start or end with the ProdWorkDay but need not. Of course, it may
never cross a ProdWorkDay.
When I write the data to the database, I need to stamp both the ProdWorkDay
and Shift.
eg.
Shift1 and ProdWorkDay
start at 10:30 PM Shift2 begins
10:30PM
^ | 8:00AM
^
<----|----------|-12:00AM---------------------------------------------------------------------|------------|-12:00AM----->
|
| ProdWorkDay ends
|
|
6:30AM
Shift2 Ends
Shift 1 Ends
3:00 PM
In the Setup, I make the user (normally our tech) set the offset for the
Shift, just as I do for ProdWorkDay.
I only expect two shifts but three is possible.
I have a table as:
CREATE TABLE [dbo].[ShiftSetUp] (
[Line] [tinyint] NOT NULL ,
[Shift] [tinyint] NULL ,
[ShiftStart] [datetime] NULL ,
[StartOffset] [smallint] NULL , //number of hours before or after
midnight
[StartTypeOffSet] [smallint] NULL , //if starting before midnight then
will be 1, if after 0, else -1
[ShiftStop] [datetime] NULL ,
[StopOffset] [smallint] NULL , //number of hours before or after midnight
[StopTypeOffSet] [smallint] NULL //if starting before midnight then will
be 1, if after 0, else -1
)
I have worked with this for three days and still have not figured it out.
It is like a Sudoku in greek. Obviously, someone else has run across this.
Two many business and manufacturing have to deal with the same thing. Can
some one give me a start??
Thanks.
 

Larry writes:
the problem you have with the shifts? What do you need that you do not
have achieved?
--
Best regards :)
Guillem Vicens Meier
Dep. Informatica Green Service S.A.
www.clubgreenoasis.com
 

Larry writes:
It's very possible I am misunderstanding what you need to do / get from all
this, but it seems to me you're making this more complicated than it needs
to be.
The combination of comboboxes, etc, you have set up seems complicated - and
still limited, if they decide to change production time to 8 pm, you have to
rewrite your UI and logic again!
It seems to me all you need to get from the user for setup is:
1. What is the Production Day Start Time.
2. What are the Production Shift Start Times and Durations (or End Times).
That's all you should need to figure out everything else, and it does not
matter what values they put in for the above. You should then be able to
query the correct set of PLC records for any requested shift or any
requested production day. There should be no reason (AFAICS) to
*pre*-interpret what shift or production date an incoming PLC record is for,
that can all be figured out when you actually need to produce some report or
perform some process later.
Now: what am I missing here? What do you actually need to get out of the
database besides what shift/day a set of PLC records is for?
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Some see private enterprise as a predatory target to be shot, others
as a cow to be milked, but few are those who see it as a sturdy horse
pulling the wagon." - Winston Churchill
 

First, my GUI works just find. If you notice, I put ... at the bottom of
the list. I simply truncated it in the post of brevity.
Look, at I have when a datastream comes from the controller, besides the
weight and other asundry fields is a date and time. The date and time may
not be real time since the controller can cache up to 12 hours of records.
I need to assign those records to the proper Production day and Shift.
Production Days NEVER go midnight to midnight. They either start before
midnight or end after midnight. FOr each case, I need want to asisgn the
record to the correct Production Day. For example,
a shift may start at 10:00 PM and for the next two hours its records will be
acredited to the following day. On the other hand, a shift may end at 2:00
AM where the records from midnight until 2:00 AM are acredited to the
preceeding day. Of course, both cannot happen at the same place.
This is more common then rare in industry and manufacturing.
On the database end I have a Shift Start time and Stop Time. It is only the
time and not the DateTime since the same value is used for every day until
cheanged. Since they may change the shift times, I need to stamp the shift
and Production Day with each record as I write it to the database, rather
then use a relation.
The trick is to resolve a incoming date and time to a particular ProdWorkDay
and Shift. When shifts run across midnight, it gets pretty hairy and I just
can't get my mind around it. Since this phenomena is not unusaul, I assume
someone must have been down this road before. The same process should work
for all affairs.
TIA
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes

Can we assume that:
1.. First shift StartTime is equal to ProdDay start time?
2.. Last shift EndTime is equal to ProdDay end time?
3.. Shifts duration is the same for all shifts?
 

No. Shifts will always take place with a ProdWorkDay but the length could
vary.
Actually, go with that since I only care about the Shift times.
But ProdWorkDay is not truly a given. I save the start of day in a table
with its offset from midnight.
I use an adjustment to determine the ProdWorkDay that I showed before.
The length of shift can be determined by the start and stop times but is not
explicitly saved. I guess I can but saw no reason.
Thanks for the reply.
"Vitali Kalinin" <XXXX@XXXXX.COM>writes

Ok as far I understood assumption#3 is wrong, what about #1 and #2?
Also EndTime of shift is StartTime for the next shift, i.e. no delay between
subsequent shifts correct?
"Larry" <XXXX@XXXXX.COM>ÓÏÏÂÝÉ?ÓÏÏÂÝÉÌÁ ?ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅ?

Every plant is different but generally there is a short delay between Shift
1 and 2. Say 15 to 30 minutes and we schedule it that way. Between shift 2
and the next shift 1 there is a full plant washdown (these are poultry
processing plants) which may go for 6 to 8 hours.
"Vitali Kalinin" <XXXX@XXXXX.COM>writes

Larry writes:
offsets - don't need offsets, that is just more complicated.
Store the actual production start time. that is all that is needed (I'm
assuming only that a production day is 24 hours).
Likewise store the start and end times for each shift.
When a PLC record comes in, it has the date and time it was produced (which
like you say, maybe some hours ago).
To calculate the production date and shift it was produced on:
(pseudocode since I don't know your actual field or variable names)
ProductionDate := PLC.Date; // assume same to start
if ProductionStartTime < 12:00 {noon} then
ProductionDate := ProductionDate - 1; // could be "yesterday's"
production
if PLC.TIME>ProductionStartTime then
ProductionDate := ProductionDate + 1; // next production date
for each shift in defined shifts
if PLC.time between shift.start and shift.end
ProductionShift := shift.Number;
That should be it. Instead of storing offsets and comparing those, and
trying to set positive or negative increments, just use 12 noon as a pivot.
A production start date before 12 noon is an "after midnight" setting, 12
noon or later is a "before midnight" setting. The above code will work no
matter what the production start time is and no matter what date and time
are on the PLC records.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Some see private enterprise as a predatory target to be shot, others
as a cow to be milked, but few are those who see it as a sturdy horse
pulling the wagon." - Winston Churchill
 

Thanks Wayne.
I got it down to one recursive SP.
It works.
Larry
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes