A complex scenario

[newbie14]

Dear All,
I have a very complex scenario to solve. I have got the idea how to solve but I worried on the implementation part of in C#.

Let me explain my scenario. I have 2 table tblTranfer and tblTransferDetails
Sample data of tblTranfer from outletID=1 to outletID=2 and 3

tranferID outletFrom outletTo tranferDate tranferTime
1 1 2 16/4/09 1:00:00
2 1 3 16/4/09 2:00:00
3 1 2 17/4/09 1:00:00
4 1 3 18/4/09 2:00:00

Sample data of tblTransferDetails

tranferDetails transferID itemID itemQty itemPrice
1 1 1 2 200
2 2 1 2 200
3 3 1 2 800
4 4 1 2 900

Now upon receive the item in outletID=2
transferID=1 receive the average price is 200
then transferID=3 receive the average price now jump to 500 which wrong due to wrong price of item as 800.

Now the scenario is that from outletID=2 we transfer the goods out to now outletID=5 below is the tblTransfer and tblTransferDetails.

Sample data of tblTranfer from outletID=2 to outletID=5

tranferID outletFrom outletTo tranferDate tranferTime
1 2 5 20/4/09 1:00:00


Sample data of tblTransferDetails

tranferDetails transferID itemID itemQty itemPrice
1 1 1 2 500

So based on my scenario in this case there was a mistake in the pricing at transferID=3 from the first outletID=1 to outletID=2 and this mistake cause the average to go up. Thereafter this wrong price will be transfered to outletID=5 and this scenario can repeat many level but I just keep to 2 level.

So my solution is that once I change the price at the first place of the mistake I want the changes to be updated across all the other databases too. In this scenario each outletID is a separate database by itself.

My idea is that based on the date and time of the transferID first I go and search where I transfer then I come to outletID=2 then from here I will do a recursive search go further to outletID=5 and this can repeat recursively.

My problem here offcourse I have the idea to do it recursively but I also want to do all of this in a transaction so if one fail all will fail.

Can any one show me some light in terms of C# code how to implement my solution ? Thank you in advance.

 

[BobRodes]

Let's take a look at your problem first. If I understand you correctly, you have four entities:

1. Items
2. Outlets
3. Transfers
4. TransferDetails

Your work has to do with transferring items from one outlet to another, and each such transfer is a line item in the transfer table. Each transfer involves multiple items, and each item in a given transfer is a line item in the transferdetails table.

Is this correct so far?

Bob

Finally, the price of items isn't fixed: the price of an item varies from transfer to transfer, and may also

 

[newbie14]

Dear Bob,
Your are absolutely right in understanding my scenario. I would like to add at first only from the outletID=1 the price is set based on certain markup price. So just say the current average price is 200 and marked up to 250 so this will be the transferDetails price. This markup only occurs in the hq. So if it is wrongly marked up too high when the item is received at the next outlet overall the average will shoot too and that is wrong. So that is what I am trying to rectify. Thank you for your time.

 

[BobRodes]

Oops, left that little bit in there at the end. Ok, we need to back off of specifics and focus on general stuff still.

More questions.

1. How do you determine the current average price? Do you do it outside of the system, and plug it in somewhere, or do you calculate it from existing data in the system?

2. What's the hq? Is that the outlet with the id of 1?

Let's get the structure down first, and then solve your specific problem. Sometimes reevaluating the structure of the system will give the key to the specific problem. I'm thinking just now, though, that you have to keep track of your transaction and store it somewhere, so you can look it up and undo it if you need to. We'll get to that.

Bob

 

[newbie14]

Dear Bob,
Ok first yes Hq is outletID=1. So at outlet=1(hq) we will do receiving of item from supplier which will have its cost price. Each time a item comes in we will re-calculate the average price based on what we have in the stock and added with the new items received? So am I clear to you how I calculate the average price. So upon getting the average price and only from the hq to other outlet transfer that we will do some markup if this wrongly marked up.This is where the problem so when the stock is received at the outlet the average price will shoot too high and this will be a chain reaction if the items are further transfered to other outlets.Thank you once again.

 

[newbie14]

Dear Bob,
Is there any other information you need from my side? Am I missing any more stuff? Thank you.