Re: inventory management problem

From: prabuinet <prabuinet_at_gmail.com>
Date: 14 Oct 2006 23:36:40 -0700
Message-ID: <1160894200.626520.251450_at_b28g2000cwb.googlegroups.com>

Volker Hetzer wrote:
> prabuinet schrieb:
> > I have two tables:
> >
> > purchase (ItemId, PurchQuantity, PurchaseDate);
> > sales (ItemId, SalesQuantity, SalesDate);
> >
> >
> > With these two table could I able to find:
> >
> > 1. Quantity in Stock at any given date of any item.
> > 2. Purchase and Sales and their OpeningStock and ClosingStock Report
> > Ordered by date
> What are opening and closing stock reports?
> And is this a homework assignment? You sure go about it like if it is.
>
> Volker
> --
> For email replies, please substitute the obvious.

Thanks for response,

This is not an home work assignment, I just simplified my tables and put it like this,
actually my real tables are:

create table PMPurchase(
PMPurchaseId integer primary key autoincrement, RefNo varchar(50) unique,
SellerId integer,
PurchaseDate smalldatetime);

create table PMPurchaseDetails
(PMPurchaseDetailsId integer primary key autoincrement, PMPurchaseId Integer,
ItemId Integer,
GodownId Integer,
Quantity Real,
Rate Real);

Create table StockLedger(
StockLedgerId integer primary key autoincrement, ItemId integer,
GodownId integer,
TransId integer,
TransDate SmallDateTime,
OpeningBalance Integer,
ClosingBalance Integer,
AdjustedQuantity Integer,
RecordType Integer);

create index ixall on StockLedger
(TransDate, ItemId, GodownId, TransId, RecordType);

With these tables, it seem to be very difficult to maintain the stockledger table, I tried to write trigger for this:

Create trigger trg_PmPurchDet_Ins
after insert on PMPurchasedetails
begin

	insert into StockLedger
	(ItemId, GodownId, TransId, TransDate, OpeningBalance, ClosingBalance,
AdjustedQuantity, RecordType)
	values
	(new.ItemId, 		/* Item Id */
	new.GodownId, 		/* Godown Id */
	new.PMPurchaseDetailsId, /* TransId */
	(select PurchaseDate from PMPurchase where PMPurchaseId =
new.PMPurchaseId), 	/* TransDate */
	coalesce ((select coalesce(ClosingBalance, 0) from StockLedger where
		/* Opening Balance */
	 	ItemId=new.ItemId and GodownId=new.GodownId and TransId
		=
		(select TransId from StockLedger where TransDate <= (select

PurchaseDate from PMPurchase where PMPurchaseId=new.PMPurchaseId) order by TransDate Desc, TransId Desc limit 1)), 0),

	coalesce ((select coalesce(ClosingBalance, 0) from StockLedger where
		/* Closing Balance */
	 	ItemId=new.ItemId and GodownId=new.GodownId and TransId
		=
		(select TransId from StockLedger where TransDate <= (select

PurchaseDate from PMPurchase where PMPurchaseId=new.PMPurchaseId) order by TransDate Desc, TransId Desc limit 1)), 0) + new.Quantity,

        new.Quantity, /* Adjusted Quantity */

        2); /* Record Type */

	update StockLedger set
			OpeningBalance = OpeningBalance + new.quantity,
			ClosingBalance = ClosingBalance + new.quantity
	where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = new.PMPurchaseId)
		and ItemId = new.ItemId and GodownId = new.GodownId;

end;

Create trigger trg_PmPurchDet_Upd
after update on PMPurchasedetails
begin

	update StockLedger set
			OpeningBalance = OpeningBalance - old.quantity,
		   	ClosingBalance = ClosingBalance - old.quantity
	where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = old.PMPurchaseId)
		and ItemId = old.ItemId and GodownId = old.GodownId;

	update StockLedger set
			ItemId = new.ItemId,
			GodownId = new.GodownId,
			ClosingBalance = OpeningBalance + new.quantity,
			AdjustedQuantity = new.Quantity
	where TransId = old.PMPurchaseDetailsId and RecordType = 2;

	update StockLedger set
			OpeningBalance = OpeningBalance + new.quantity,
			ClosingBalance = ClosingBalance + new.quantity
	where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = old.PMPurchaseId)
		and ItemId = new.ItemId and GodownId = new.GodownId;

end;

create trigger trg_PmPurchaseDet_Del
after delete on PMPurchasedetails
begin

	update StockLedger set
			OpeningBalance = OpeningBalance - old.quantity,
		   	ClosingBalance = ClosingBalance - old.quantity
	where (TransDate > (select TransDate from StockLedger where TransId =

old.PMPurchaseDetailsId and RecordType = 2 and ItemId = old.ItemId and GodownId = old.GodownId)

              (TransDate = (select TransDate from StockLedger where TransId = old.PMPurchaseDetailsId and RecordType = 2 and ItemId = old.ItemId and GodownId = old.GodownId)

        and ItemId = old.ItemId and GodownId = old.GodownId;

	delete from StockLedger where
		TransId = old.PMPurchaseDetailsId and RecordType = 2;

end;

See how nasty my trigger looks, I'm using sqlite.

My problem is whether it would affect the speed of the application... or is there any other neat way to do this? Received on Sun Oct 15 2006 - 08:36:40 CEST