Menu
▾
▴
web-erp-developers
|
From: Jesse P. <jes...@st...> - 2004-04-28 14:01:34
|
Whew. Wasn't quite sure how well some of that stuff would go over. = There's still more to be done, too. I'll keep an eye out for continued = discussiuon on the logic (if we can call it that :) ) for the rest of = the serialised work. jesse > -----Original Message----- > From: Phil Daintree [mailto:ph...@du...] > Sent: Wednesday, April 28, 2004 06:25 > To: web...@li... > Subject: [Web-erp-developers] Re: some serialised work >=20 >=20 > Jesse, >=20 > Started to look at this - noticed you included those=20 > variables in the cart=20 > object ShipVia etc that should have been there originally -=20 > thanks. Like your=20 > mod to DB_query - that will take a lot of unecessary code out=20 > of every=20 > script!! again I should have done this :-( but didn't think=20 > of it ....=20 > clever! Many of your functions have application througout.=20 >=20 > Still not bottomed the logic of your serialised items yet ...=20 > give me time. >=20 > Looks exciting - clearly you have much to teach me. Please be=20 > patient with me=20 > - it does take me a bit to absorb new ideas. >=20 > Thanks a LOT for all this!=20 > Phil >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by: Oracle 10g > Get certified on the hottest thing ever to hit the market...=20 > Oracle 10g.=20 > Take an Oracle 10g class now, and we'll give you the exam FREE.=20 > http://ads.osdn.com/?ad_id=3D3149&alloc_id=3D8166&op=3Dclick > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers >=20 |
|
From: Jesse P. <jes...@st...> - 2004-04-30 00:29:00
|
What a freakin day. I swear I first hit reply 12 hours ago.. ugh... here = goes.... > Now tried to receive this PO ... > Need to think about the serial number entry stuff - this file=20 > selection stuff=20 > looks great as does the keyed entry - the barcode is the same=20 > as keyed - might just need a little bit of java to move to the next=20 > field after input somehow. *javascript* ;) yeah, I just didn't want to work on that yet b/c it's = going to be a little bit of a mess to make onChange functions work = correctly... and my Symbol barcode scanner lets me program it with = 'Suffix' data (so a tab in the simplest form). Any suggestions will be = considered... > I can't see why a bundleid is necessary as well as serial numbers for=20 > controlled and serialised? You may be right. It kind of amounts to a specialized tracking number = (beside the StkMoveNo). Maybe it should be treated as such... > The instructions on GoodsReceived.php says to click the part=20 > code to enter serial numbers - this link didn't work until you enter a = > quantity and hit update. Would be great to accumulate the quantity = from the=20 > serial numbers entered ... I rekon. I thought about that and I'm pretty sure it's necessary... the thing is, = right now the script creates the form based on the Qty, which is why I = required it and made it work like that... with a list of requirements on = how it should work, I can probably fix it up fairly easily. > Notice you don't use ?php tags you are an ex ASP person ??=20 > Using short tags=20 > got me into trouble once can't remember why - I always use=20 > long tags now. Well, I use ASP when I have no choice... it's more that I'm lazy and = it's never caused me problems. I'll try to be more correct... > Created an order and confirmed dispatch .... >=20 > Would be good to be able to select the items being dispatched=20 > - ie serial=20 > numbers available to sell from StockItems. Yes, thought about that... though the majority of the time, my = fulfillment folks find it easier to pull items and enter what they = pulled rather than be told/shown what they can pull and having to go = find it. It's about the same fix as accumulating the Qty on the = GoodsReceived above... =20 > StockItems could also have a quantity field in it no ? This would do = for the=20 > bundleID and quantity on each bundle then - sorry to keep=20 > harping back. I would like to figure out a way to have = bundles/rolls/batches=20 > of product referenced in the same way as a serial number but referring = > to a quantity of the item not a single unit. That may be our happy medium... I still have to think this out more to = make it work correctly for all scenarios. > StockItems could be called something like StockSerialItems or such to = avoid=20 > confusion with a stock master and specify that the table is to do with = > serialised stuff. yeah, yeah, yeah - makes sense :) shall I fix it in the next set of = changes I send? |
|
From: Phil D. <ph...@du...> - 2004-04-30 00:59:15
|
> > StockItems could be called something like StockSerialItems or such to avoid > > confusion with a stock master and specify that the table is to do with > > serialised stuff. > yeah, yeah, yeah - makes sense :) shall I fix it in the next set of changes I send? Yes please! I am finding the diff great for working through the changes, but to a large extent your mods look as though they could go straight in without me "interfering", it seems your changes to the existing code are beneficial from a readability and maintainence perspective of those I have seen, so its good to have the actual scripts as well. I have an installation set up with your db and scripts for testing. The only thing is you would have to make sure you work off the very latest CVS - wouldn't want to lose all the little bits I have changed over the last month or so - noticed your config.php was old - notices came up again. I am yet to really understand the other table SerialisedStockMoves table referencing the stockmoveto and stockmovefrom. i like your idea of retaining the existing structure and having a sub level of data that keeps the serialised record keeping in tact. I will get there ... without understanding it intuitively I would prefer to record all movements of each serialised item, in a separate table - I realise that this would be a busy table. I just don't see how we could satisfy the requirements for serialisable stock without it. As a fundamental criteria for the functionality of serialised stock, we need to be able to determine and track which serial number items we have at any point in time and at which location these are stored. We need to be able to find to whom a particular serial number was sold. We need to be able to see when a particular serial number item came in. This effectively boils down to an inquiry as per the current stock movements inquiry but at the lower level. ie the stockmovements inquiry looks at the part and location - we need to look at the part, location and serial number to see the movements of the individual item. I am sure you have thought of all this and as you will be able to tell I am some way from understanding your system. Phil |
|
From: Jesse P. <jes...@st...> - 2004-04-30 15:09:25
|
> As a fundamental criteria for the functionality of serialised stock, = we need > to be able to determine and track which serial number items we have at = any > point in time and at which location these are stored. We need to be = able to > find to whom a particular serial number was sold. We need to be able = to see > when a particular serial number item came in. This effectively boils = down to > an inquiry as per the current stock movements inquiry but at the lower > level. ie the stockmovements inquiry looks at the part and location - = we > need to look at the part, location and serial number to see the = movements of > the individual item. I believe it satisifies this... you'll probably find that soon. = Basically the SerialisedStockMoves tables does what you want. First, = though, realize that the current StockMoveNo for a Serial Items is = stored in the (soon to be renamed) StockSerialItems table. That gives us = the current 'status' of the Item at a moments glance. For a Historical = view, there will be multiple entries in the SerialisedMoves table per = SerialItem that maintains links to the previous StockMove and the = CurrentStock move. For example, here's what it might look like: 5 - PO/GoodsReceived 6 - LocationTransfer 7,8,9 - SalesOrders StockId from to 10 5 6 11 5 6 12 5 6 10 6 7 12 6 8 11 6 9 * Note there is no initial record for the PO Received (or what ever the = 1st move is) aside from the StockSerialItems entry w/ the = CurrentStockMove. So, I believe the data is available - getting at it will require = determining how people find the need to get at it... For example, are = you going to start with a single Item and look for it's history? A whole = bundle/StockMove of items and find out where they went? This can get = hairy, but there's really no option as we need to track all the data. = Consider 1000 items purchased at a main warehouse, sent to 10 different = warehouses, and then sold separarately to 200 customers. That's alot of = moves and a fairly large complicated report for some of the options = above. Let me know how that goes over with you.... btw, when you get a chance = to get to the Credit stuff we started talking about, let me know... jesse |
|
From: Daintree <p.d...@pa...> - 2004-04-30 22:47:34
|
> I believe it satisifies this... you'll probably find that soon. Basically the SerialisedStockMoves >tables does what you want. First, though, realize that the current StockMoveNo for a Serial Items >is stored in the (soon to be renamed) StockSerialItems table. That gives us the current 'status' of >the Item at a moments glance. For a Historical view, there will be multiple entries in the >SerialisedMoves table per SerialItem that maintains links to the previous StockMove and the >CurrentStock move. For example, here's what it might look like: > > 5 - PO/GoodsReceived > 6 - LocationTransfer > 7,8,9 - SalesOrders > > StockId from to > 10 5 6 > 11 5 6 > 12 5 6 > 10 6 7 > 12 6 8 > 11 6 9 > * Note there is no initial record for the PO Received (or what ever the 1st move is) aside from the StockSerialItems entry w/ the CurrentStockMove. > So to get the the actual stock movments of a Serialised items Stock Moves we have some grotesque, miserable, SLOW SQL summat like ... SELECT DISTINCT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate, StockMoves.Qty (we need to know whether the stock was going in or out of this location so the sign is important) FROM StockMoves INNER JOIN SerailisedMoves ON StockMoves.ID=SerialisedMoves.From INNER JOIN SerialisedMoves AS SerialisedMovesAgain ON StockMoves.ID=SerialisedMovesAgain.To WHERE SerialisedMoves.SerialStockID=$SerialNumberWeAreAfter AND SerialisedMovesAgain.SerialStockID=$SerialNumberWeAreAfter Not sure if there would be another more efficient way of retrieving the stock movements of a serial item if not then I believe we need to think again :-( StockMoves is already a busy table in its own right and I suspect SerialMoves will be substanital too - these inquiries will be S L O W. >This can get hairy, but there's really no option as we need to track all the data. Consider 1000 >items purchased at a main warehouse, sent to 10 different warehouses, and then sold separarately >to 200 customers. That's alot of moves and a fairly large complicated report for some of the >options above. > Yeah I agree ... very hairy. Why couldn't the SerialiasedMoves table look like: SerialMoveID, Primary auto_increment StockMoveID, Foreign key to StockMoves SerialID, Foreign key to StockSerialItems Qty - I still think we should build this in although I appreciate your only interested in 1's This would be a straight many to 1 with the existing StockMoves table. I don't believe there is significant extra overhead in this strucutre and it sure makes getting the required data from the db easier and I would suspect quicker. SELECT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate, SerailisedMoves.Qty FROM StockMoves INNER JOIN SerailisedMoves ON StockMoves.ID=SerialisedMoves.StockMoveID WHERE SerialisedMoves.SerialID=$SerialNumberWeAreAfter AND StockMoves.StockID=$StockItemWeAreAfter; I am still obviously to get to the bottom of what you are doing, and because I don't see it all yet .... I do have some worries :-( Phil |
|
From: Jesse P. <je...@st...> - 2004-05-02 21:02:14
|
>So to get the the actual stock movments of a Serialised items Stock Moves we
>have some grotesque, miserable, SLOW SQL summat like ...
>
>SELECT DISTINCT StockMoves.Type, StockMoves.LocCode, StockMoves.TranDate,
>StockMoves.Qty (we need to know whether the stock was going in or out of
>this location so the sign is important) FROM StockMoves INNER JOIN
>SerailisedMoves ON StockMoves.ID=SerialisedMoves.From INNER JOIN
>SerialisedMoves AS SerialisedMovesAgain ON
>StockMoves.ID=SerialisedMovesAgain.To WHERE
>SerialisedMoves.SerialStockID=$SerialNumberWeAreAfter AND
>SerialisedMovesAgain.SerialStockID=$SerialNumberWeAreAfter
>
>
select STKMFrom.*, STKMTo.*
from SerialisedItems SI left join SerialisedMoves SM
on SI.StockItemId = SMGoing.StockItemId
left join StockMoves SMFrom
on SM.FromStkMoveNo = SMFrom.StkMoveNo
left join StockMoves SMTo
on SM.ToStkMoveNo = SMTo.StkMoveNo
where SI.SerialNo = '$SerialNumberWeAreAfter'
>StockMoves is already a busy table in its own right and I suspect
>SerialMoves will be substanital too - these inquiries will be S L O W.
>
>
possibly, but at least it should not affect any other part of the
system. Usage may show how to better design/revise it.
>Why couldn't the SerialiasedMoves table look like:
>
>SerialMoveID, Primary auto_increment
>StockMoveID, Foreign key to StockMoves
>SerialID, Foreign key to StockSerialItems
>Qty - I still think we should build this in although I appreciate your only
>interested in 1's
>
>This would be a straight many to 1 with the existing StockMoves table.
>
>
May be better or at least easier to work with. My thoughts had been that
you know an item went from A to B, so force it. We can pick up the Qty
thing from previous discussions that came from the Crediting stuff about
possibly those things being a type of manufactured product once you're
ready.
>I am still obviously to get to the bottom of what you are doing, and because
>I don't see it all yet .... I do have some worries :-(
>
>
Just ask and I'll explain why I did something some way or what I
intended - it's completely possibly something may be done wrong.
|
|
From: Jesse P. <jes...@st...> - 2004-05-04 17:29:53
|
Sorry for the delayed response.=20 With the message functions, show me before you change them too much... = the getXX and prnXXX were purposely done so I could either capture text = to a variable for later display (I'm pretty sure I've already used it = that way) or just print it out on the fly. Just let me see them (or tell = me when to check cvs) so I can make adjustments on my end. How's this sound for moving forward: 1) You go ahead and include MiscFunctions, DB stuff, and other = non-Serialised affecting scripts to the base code that you have so that = you, I, and others can start re-standardizing scripts as we have time. = That's going to be just uner 150 scripts, so it will probably take a = while - good thing is we know it works both ways. 2) In a bit (hopefully soon), I'll get back up to speed on our = discussions on the functionality of strict/loose Crediting, etc. to help = with #3.... 3) I'll continue work on expanding the rest of the scripts for = Serialised support now that part of the base changes are fairly settled = and we are just back to defining functional requirements for Serialised = support. thoughts? jesse > -----Original Message----- > From: Daintree [mailto:p.d...@pa...] > Sent: Sunday, May 02, 2004 21:15 > To: web...@li... > Subject: Re: [Web-erp-developers] Re: some serialised work >=20 >=20 > The work you are doing on this requires a very deep=20 > understanding of the > system and without knowing the time you have spent coming up=20 > to speed, I > have to say it is impressive that you felt confident to take=20 > this on. I must > say I feel quite daunted by the task myself. I think in many=20 > systems you > would not have wanted to tackle this! >=20 > I know full well my view is the minority one :-( >=20 > OK, lets go. However, I would like to make the system=20 > consistent though. > Heck there are only a few scripts in the system, with a=20 > little help I am > sure we can know them over quite quickly. I can make your=20 > message functions > into one function at least. >=20 > Phil >=20 >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by: Oracle 10g > Get certified on the hottest thing ever to hit the market...=20 > Oracle 10g.=20 > Take an Oracle 10g class now, and we'll give you the exam FREE.=20 > http://ads.osdn.com/?ad_id=3D3149&alloc_id=3D8166&op=3Dclick > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers >=20 |
|
From: Daintree <p.d...@pa...> - 2004-05-04 19:58:50
|
OK, sounds good, I am not sure I can include Confirm_DispatchInvoice.= php, so we are only looking at the includes just yet. I really feel the DB st= ructure re SerialMoves is a snag for getting the required information out. I = will work on re-jigging a little if thats ok in SerialMoves. Better let me= have a weekend on it. Phil ----- Original Message ----- =46rom: "Jesse Peterson" <jes...@st...> To: <web...@li...> Sent: Wednesday, May 05, 2004 5:29 AM Subject: RE: [Web-erp-developers] Re: some serialised work > Sorry for the delayed response. > With the message functions, show me before you change them too much= ... the getXX and prnXXX were purposely done so I could either capture text t= o a variable for later display (I'm pretty sure I've already used it that= way) or just print it out on the fly. Just let me see them (or tell me whe= n to check cvs) so I can make adjustments on my end. > > How's this sound for moving forward: > > 1) You go ahead and include MiscFunctions, DB stuff, and other non-Serialised affecting scripts to the base code that you have so th= at you, I, and others can start re-standardizing scripts as we have time. Tha= t's going to be just uner 150 scripts, so it will probably take a while -= good thing is we know it works both ways. > > 2) In a bit (hopefully soon), I'll get back up to speed on our disc= ussions on the functionality of strict/loose Crediting, etc. to help with #3.= ... > > 3) I'll continue work on expanding the rest of the scripts for Seri= alised support now that part of the base changes are fairly settled and we a= re just back to defining functional requirements for Serialised support. > > thoughts? > > jesse > > > > -----Original Message----- > > From: Daintree [mailto:p.d...@pa...] > > Sent: Sunday, May 02, 2004 21:15 > > To: web...@li... > > Subject: Re: [Web-erp-developers] Re: some serialised work > > > > > > The work you are doing on this requires a very deep > > understanding of the > > system and without knowing the time you have spent coming up > > to speed, I > > have to say it is impressive that you felt confident to take > > this on. I must > > say I feel quite daunted by the task myself. I think in many > > systems you > > would not have wanted to tackle this! > > > > I know full well my view is the minority one :-( > > > > OK, lets go. However, I would like to make the system > > consistent though. > > Heck there are only a few scripts in the system, with a > > little help I am > > sure we can know them over quite quickly. I can make your > > message functions > > into one function at least. > > > > Phil > > > > > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by: Oracle 10g > > Get certified on the hottest thing ever to hit the market... > > Oracle 10g. > > Take an Oracle 10g class now, and we'll give you the exam FREE. > > http://ads.osdn.com/?ad_id=3D3149&alloc_id=3D8166&op=3Dclick > > _______________________________________________ > > Web-erp-developers mailing list > > Web...@li... > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > ------------------------------------------------------- > This SF.Net email is sponsored by: Oracle 10g > Get certified on the hottest thing ever to hit the market... Oracle= 10g. > Take an Oracle 10g class now, and we'll give you the exam FREE. > http://ads.osdn.com/?ad_id149&alloc_id=8166&op=CCk > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > |
|
From: Jesse P. <jes...@st...> - 2004-05-04 20:15:08
|
I wouldn't even include the others (goodreceived, etc.) until Serialised = stuff is ready, just the includes as you mentioned. serialmoves...hum...Did you check out my response on that and the sql = statements in it? you had linked the tables a bit off in your 1st email = about them. If you still see problems, let's chat about the restructuring a bit... > -----Original Message----- > From: Daintree [mailto:p.d...@pa...] > Sent: Tuesday, May 04, 2004 15:59 > To: web...@li... > Subject: Re: [Web-erp-developers] Re: some serialised work >=20 >=20 > OK, sounds good, I am not sure I can include=20 > Confirm_DispatchInvoice.php, so > we are only looking at the includes just yet. I really feel=20 > the DB structure > re SerialMoves is a snag for getting the required information=20 > out. I will > work on re-jigging a little if thats ok in SerialMoves.=20 > Better let me have a > weekend on it. >=20 > Phil >=20 >=20 > ----- Original Message ----- > From: "Jesse Peterson" <jes...@st...> > To: <web...@li...> > Sent: Wednesday, May 05, 2004 5:29 AM > Subject: RE: [Web-erp-developers] Re: some serialised work >=20 >=20 > > Sorry for the delayed response. > > With the message functions, show me before you change them=20 > too much... the > getXX and prnXXX were purposely done so I could either=20 > capture text to a > variable for later display (I'm pretty sure I've already used=20 > it that way) > or just print it out on the fly. Just let me see them (or=20 > tell me when to > check cvs) so I can make adjustments on my end. > > > > How's this sound for moving forward: > > > > 1) You go ahead and include MiscFunctions, DB stuff, and other > non-Serialised affecting scripts to the base code that you=20 > have so that you, > I, and others can start re-standardizing scripts as we have=20 > time. That's > going to be just uner 150 scripts, so it will probably take a=20 > while - good > thing is we know it works both ways. > > > > 2) In a bit (hopefully soon), I'll get back up to speed on=20 > our discussions > on the functionality of strict/loose Crediting, etc. to help=20 > with #3.... > > > > 3) I'll continue work on expanding the rest of the scripts=20 > for Serialised > support now that part of the base changes are fairly settled=20 > and we are just > back to defining functional requirements for Serialised support. > > > > thoughts? > > > > jesse > > > > > > > -----Original Message----- > > > From: Daintree [mailto:p.d...@pa...] > > > Sent: Sunday, May 02, 2004 21:15 > > > To: web...@li... > > > Subject: Re: [Web-erp-developers] Re: some serialised work > > > > > > > > > The work you are doing on this requires a very deep > > > understanding of the > > > system and without knowing the time you have spent coming up > > > to speed, I > > > have to say it is impressive that you felt confident to take > > > this on. I must > > > say I feel quite daunted by the task myself. I think in many > > > systems you > > > would not have wanted to tackle this! > > > > > > I know full well my view is the minority one :-( > > > > > > OK, lets go. However, I would like to make the system > > > consistent though. > > > Heck there are only a few scripts in the system, with a > > > little help I am > > > sure we can know them over quite quickly. I can make your > > > message functions > > > into one function at least. > > > > > > Phil > > > > > > > > > > > > ------------------------------------------------------- > > > This SF.Net email is sponsored by: Oracle 10g > > > Get certified on the hottest thing ever to hit the market... > > > Oracle 10g. > > > Take an Oracle 10g class now, and we'll give you the exam FREE. > > > http://ads.osdn.com/?ad_id=3D3149&alloc_id=3D8166&op=3Dclick > > > _______________________________________________ > > > Web-erp-developers mailing list > > > Web...@li... > > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > > > > > > ------------------------------------------------------- > > This SF.Net email is sponsored by: Oracle 10g > > Get certified on the hottest thing ever to hit the=20 > market... Oracle 10g. > > Take an Oracle 10g class now, and we'll give you the exam FREE. > > http://ads.osdn.com/?ad_id149&alloc_id=8166&op=CCk > > _______________________________________________ > > Web-erp-developers mailing list > > Web...@li... > > https://lists.sourceforge.net/lists/listinfo/web-erp-developers > > > > >=20 >=20 >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by: Oracle 10g > Get certified on the hottest thing ever to hit the market...=20 > Oracle 10g.=20 > Take an Oracle 10g class now, and we'll give you the exam FREE.=20 > http://ads.osdn.com/?ad_id149&alloc_id=8166&op=3Dick > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers >=20 |
|
From: Phil D. <ph...@du...> - 2004-05-04 21:32:30
|
> serialmoves...hum...Did you check out my response on that and the sql statements in it? you had linked the tables a bit off in your 1st email about them. > If you still see problems, let's chat about the restructuring a bit... Yeah I saw your reply which basically confirmed that we are into outer joins or several copies of the table to get all the movements of serialised items. > > 3) I'll continue work on expanding the rest of the scripts > for Serialised > support now that part of the base changes are fairly settled > and we are just > back to defining functional requirements for Serialised support. > > I rekon defining the functional requirements comes first in many ways, it is only when there is a clear idea of what is required to be output that we can define appropriately the inputs. There may be outputs that you require that are best served with the structure you have. I do like your treatment of the serialised stuff by entirely separate tables thus leaving the level about completely untouched - damn clever! I think knowing: - to whom and the date a serial item is sold - when a serial number arrived (or was made) - when it was returned if it was credited This is basically the stock movement inquiry I was talking about. I think this could be a sub-inquiry from the stockmovements inquiry ie to click on a stockmovement record to get the lower level serial moves. We also need to know: - what serial numbers we have in stock Not sure how we get this from StockSerialItems ? I am not happy with a requirement to retrieve all stock movements to re-calculate the stock on hand - a 2 minute query - choking other activity - when there may be none! This also ignores the possibility of purging old data. To my mind this would have to be an update to the SerialStockItems at the time of a movement. Phil |
|
From: Jesse P. <jes...@st...> - 2004-05-04 22:14:55
|
> > serialmoves...hum...Did you check out my response on that=20 > and the sql > statements in it? you had linked the tables a bit off in your=20 > 1st email > about them. > > If you still see problems, let's chat about the=20 > restructuring a bit... >=20 > Yeah I saw your reply which basically confirmed that we are=20 > into outer joins > or several copies of the table to get all the movements of=20 > serialised items. Just Left joins... much faster than Full joins or Right Outers, and the = SerialItems you start with are indexes... We could force Inner Joins by = changing the Move Table to record Moves w/o the To/From as you suggested = and inserting the Initial StockMove movement (which I'd left off since I = didn't have an initial From move). =20 > I think knowing: > - to whom and the date a serial item is sold > - when a serial number arrived (or was made) > - when it was returned if it was credited > This is basically the stock movement inquiry I was talking=20 > about. I think > this could be a sub-inquiry from the stockmovements inquiry=20 > ie to click on a > stockmovement record to get the lower level serial moves. > We also need to know: > - what serial numbers we have in stock All possible with the current structure... remember, the SerialItems = table is also holding the *current* StockMove (not serialmove) the Item = is associated with - this allows a straight inner join with StockMoves - = the 'type' of the StockMove tells us whether it is available in = inventory. It may be better to have SerialItems link to SerialMoves to = StockMoves, but I'll admit I kludged that w/ code to avoid the extra = join on another large table. > Not sure how we get this from StockSerialItems ? I am not happy with a > requirement to retrieve all stock movements to re-calculate=20 > the stock on hand - a 2 minute query - choking other activity - when = there=20 > may be none! his also ignores the possibility of purging old data.=20 Theoretically (ahem...bear with me) StockSerialItems should not fall out = of sync with the StockMoves quantities so long as it is coded correctly = and people are not mucking with the database incorrectly - those are not = scenarios I would try to worry about (well, except the coding = correctly). Really ditto with the StockMoves table... And if you really = needed to reconcile StockSerialItems against SerialMoves against = StockMoves (I don't see a need for that ever/often aside from = development [now]), it may be slow. > To my mind this would have to be an update to the SerialStockItems at = the time of > a movement. Currently there kind of is that ... see my note about the kludged = StockMove above... and the possibilities to unkludge it that I mentioned = really should alleviate any other non-inner joins.=20 Then again, if we need to account for people purging records without = some 'approved' purge tool that ensures you keep database integrity, = alot of things will need to be change to use left joins and a lot of = extra logic will need to be added to correctly report missing = records/data. |
|
From: Phil D. <ph...@du...> - 2004-05-04 22:51:52
|
> > Yeah I saw your reply which basically confirmed that we are > > into outer joins > > or several copies of the table to get all the movements of > > serialised items. > Just Left joins... much faster than Full joins or Right Outers, and the SerialItems you start with are indexes... We could force Inner Joins by changing the Move Table to record Moves w/o the To/From as you suggested and inserting the Initial StockMove movement (which I'd left off since I didn't have an initial From move). I think that's the answer - no biggie really. > > > I think knowing: > > - to whom and the date a serial item is sold > > - when a serial number arrived (or was made) > > - when it was returned if it was credited > > This is basically the stock movement inquiry I was talking > > about. I think > > this could be a sub-inquiry from the stockmovements inquiry > > ie to click on a > > stockmovement record to get the lower level serial moves. > > We also need to know: > > - what serial numbers we have in stock > All possible with the current structure... remember, the SerialItems table is also holding the *current* >StockMove (not serialmove) the Item is associated with - this allows a straight inner join with >StockMoves - the 'type' of the StockMove tells us whether it is available in inventory. It may be better >to have SerialItems link to SerialMoves to StockMoves, but I'll admit I kludged that w/ code to avoid >the extra join on another large table. The StockMoves table has the total of all serial items in stock not the quantity of the actual serial number - this goes to the bundle/roll/batch issue again. but even a 1 or 0 for SerialItems would be good. > > > Not sure how we get this from StockSerialItems ? I am not happy with a > > requirement to retrieve all stock movements to re-calculate > > the stock on hand - a 2 minute query - choking other activity - when there > > may be none! his also ignores the possibility of purging old data. > Theoretically (ahem...bear with me) StockSerialItems should not fall out of sync with the StockMoves >quantities so long as it is coded correctly and people are not mucking with the database incorrectly - >those are not scenarios I would try to worry about (well, except the coding correctly). Really ditto with >the StockMoves table... And if you really needed to reconcile StockSerialItems against SerialMoves >against StockMoves (I don't see a need for that ever/often aside from development [now]), it may be >slow. > > > To my mind this would have to be an update to the SerialStockItems at the time of > > a movement. > Currently there kind of is that ... see my note about the kludged StockMove above... and the possibilities to unkludge it that I mentioned really should alleviate any other non-inner joins. > Then again, if we need to account for people purging records without some 'approved' purge tool that ensures you keep database integrity, alot of things will need to be change to use left joins and a lot of extra logic will need to be added to correctly report missing records/data. My only point is to consider how we might proceed without the movement records which will be substantial and how we might structure a purge process that retains the info required. I like the idea of a quantity against the StockSerialItems table which holds the quantity 0 or 1 (or hey maybe more for batch/bundle/rolls ;-) This would mean that there is no join going on at all to get the stock status of a particular bundleid - but an extra update at the time of the movement. Phil |
|
From: Phil D. <ph...@du...> - 2004-05-05 10:45:29
|
Jesse, I commited a pile of stuff to CVS which underlies your work. The db changes are probably going to be contentious. I would like to have a go with the structure i have commited, I will work through your scripts starting with GoodsReceived to see if I can get it going with this structure - if not lets go back to plan A - ie your way. I feel it will be a heap easier to get at the data for inquiries/reports with this structure. I included the MiscFunctions.php (Dick wants us to use .php instead of .inc where possible - think he's right) Changed some of the functions - now need to pass "error", "succ" or whatever as a parameter instead of calling a function with these in the name of the function - only 2 functions though now not 10. Also, DefineStockMasterClass.php, StockModules.php, StockModules/GenericStockItem.php and CellPhoneItem.php, modiifed Stocks.php, session.inc ...its a start! Phil |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X