Файл: Разработка информационной системы учёта товаров магазина мелисса.docx

THEN

VMY_PRICE = 0;

/* ОБНОВИТЬ ТАБЛИЦУ */

UPDATE TGOODS

SET FAMOUNT = :VMY_AMOUNT, FPRICE = :VMY_PRICE

WHERE FID = :VMY_GOODSID; END

END ELSE

/*---------- Р Е А Л И З А Ц И Я Т О В А Р А */

IF (:VMY_DOCTYPE = 2) THEN BEGIN

FOR SELECT C.FGOODSID, C.FAMOUNT, C.fprice, C.FSTOREID FROM (TDOCUMCONTENT C

LEFT JOIN TGOODS G ON C.FGOODSID = G.FID) WHERE (C.FDOCID = :VIN_DOCID)

INTO :VMY_GOODSID, :VMY_AMOUNT, :VMY_PRiCe, :VMY_STOREID DO

BEGIN

VMY_LAPCOUNT = (:VMY_LAPCOUNT + 1);

/* РАСЧЕТ СУММЫ */

VMY_SUMM = (:VMY_SUMM + (:VMY_AMOUNT * :VMY_price)); BEGIN

/*----- СПИСАНИЕ СО СКЛАДА */

SELECT FAMOUNT FROM TSTORE

WHERE (FID = :VMY_STOREID)

INTO VMY_AMOUNT_DISPLAY;/* ПОЛУЧИТЬ ТЕК. ЗНАЧЕНИЕ НА СКЛАДЕ */ IF ((:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT) < 0 ) THEN

EXCEPTION ESTORE_LACKAMOUNT 'Не хватает количества для позиции № "'||:VMY_LAPCOUNT||'"';

UPDATE TSTORE

SET FAMOUNT = (:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT)

WHERE (FID = :VMY_STOREID);/* ОБНОВИТЬ ЗНАЧЕНИЕ НА СКЛАДЕ */

/*----- ВИЗУАЛЬНЫЕ ИЗМЕНЕНИЯ */

SELECT FAMOUNT, FPRICE FROM TGOODS

WHERE (FID = :VMY_GOODSID)

INTO VMY_AMOUNT_DISPLAY, :VMY_PRICE_DISPLAY;

/* РАСЧИТАТЬ НОВОЕ КОЛИЧЕСТВО */

VMY_AMOUNT = (:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT); IF (:VMY_AMOUNT = 0) THEN

BEGIN

VMY_AMOUNT = NULL; VMY_PRICE = NULL; END

ELSE BEGIN

/* ПРОВЕРИТЬ НА НАЛИЧИЕ НЕСКОЛЬКИХ ЦЕН */ IF (:VMY_PRICE_DISPLAY = 0) THEN

IF ((SELECT FIRST 1 COUNT(DISTINCT FPRICE) FROM TSTORE

WHERE ((FGOODSID = :VMY_GOODSID) AND (FAMOUNT > 0))) > 1) THEN VMY_PRICE = 0;

ELSE

VMY_PRICE = (SELECT FIRST 1 FPRICE FROM TSTORE

WHERE ((FGOODSID = :VMY_GOODSID) AND (FAMOUNT > 0))); END

/* ОБНОВИТЬ ТАБЛИЦУ */ UPDATE TGOODS

SET FAMOUNT = :VMY_AMOUNT, FPRICE = :VMY_PRICE

WHERE FID = :VMY_GOODSID;

end end END ELSE

/*---------- С П И С А Н И Е Т О В А Р А */

IF (:VMY_DOCTYPE = 3) THEN BEGIN

VMY_LAPCOUNT = (:VMY_LAPCOUNT + 1);

FOR SELECT FGOODSID, FAMOUNT, FPRICE, FSTOREID FROM TDOCUMCONTENT

WHERE (FDOCID = :VIN_DOCID)

INTO :VMY_GOODSID,

---

:VMY_AMOUNT, :VMY_PRICE, :VMY_STOREID DO

BEGIN

/*----- СПИСАНИЕ СО СКЛАДА */

SELECT FAMOUNT FROM TSTORE

WHERE (FID = :VMY_STOREID)

INTO VMY_AMOUNT_DISPLAY;/* ПОЛУЧИТЬ ТЕК. ЗНАЧЕНИЕ НА СКЛАДЕ */ IF ((:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT) < 0 ) THEN

EXCEPTION ESTORE_LACKAMOUNT 'Не хватает количества для позиции № "'||:VMY_LAPCOUNT||'"';

UPDATE TSTORE

SET FAMOUNT = (:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT)

WHERE (FID = :VMY_STOREID);/* ОБНОВИТЬ ЗНАЧЕНИЕ НА СКЛАДЕ */

/* РАСЧЕТ СУММЫ */

VMY_SUMM = (:VMY_SUMM + (:VMY_AMOUNT * :VMY_PRICE));

/*----- ВИЗУАЛЬНЫЕ ИЗМЕНЕНИЯ */

SELECT FAMOUNT, FPRICE FROM TGOODS

WHERE (FID = :VMY_GOODSID)

INTO VMY_AMOUNT_DISPLAY, :VMY_PRICE_DISPLAY;

/* РАСЧИТАТЬ НОВОЕ КОЛИЧЕСТВО */

VMY_AMOUNT = (:VMY_AMOUNT_DISPLAY - :VMY_AMOUNT);

/* РАСЧИТАТЬ НОВУЮ ЦЕНУ */ IF (:VMY_AMOUNT = 0) THEN BEGIN

VMY_AMOUNT = NULL; VMY_PRICE = NULL; END

ELSE BEGIN

/* ПРОВЕРИТЬ НА НАЛИЧИЕ НЕСКОЛЬКИХ ЦЕН */

IF (:VMY_PRICE_DISPLAY = 0) THEN

IF ((SELECT FIRST 1 COUNT(DISTINCT FPRICE) FROM TSTORE

WHERE ((FGOODSID = :VMY_GOODSID) AND (FAMOUNT > 0))) > 1) THEN VMY_PRICE = 0;

ELSE

VMY_PRICE = (SELECT FIRST 1 FPRICE FROM TSTORE

WHERE ((FGOODSID = :VMY_GOODSID) AND (FAMOUNT > 0)));

END

/* ОБНОВИТЬ ТАБЛИЦУ */ UPDATE TGOODS

SET FAMOUNT = :VMY_AMOUNT, FPRICE = :VMY_PRICE

WHERE FID = :VMY_GOODSID; END

END ELSE

/*---------- В О З В Р А Т Т О В А Р А */

IF (:VMY_DOCTYPE = 4) THEN BEGIN

FOR SELECT C.FGOODSID, C.FAMOUNT, C.FPRICE FROM (TDOCUMCONTENT C

LEFT JOIN TGOODS G ON C.FGOODSID = G.FID) WHERE (C.FDOCID = :VIN_DOCID)

INTO :VMY_GOODSID, :VMY_AMOUNT, :VMY_PRICE DO

BEGIN

VMY_LAPCOUNT = (:VMY_LAPCOUNT + 1);

/* РАСЧЕТ СУММЫ */

VMY_SUMM = (:VMY_SUMM + (:VMY_AMOUNT * :VMY_PRICE));

/*----- ВСТАВКА НА СКЛАД */

INSERT INTO TSTORE (FGOODSID, FAMOUNT, FPRICE, FPERSON, FUSER) VALUES (:VMY_GOODSID, :VMY_AMOUNT, :VMY_PRICE, :VIN_PERSON,

:VIN_USER);

/*----- ВИЗУАЛЬНЫЕ ИЗМЕНЕНИЯ */

SELECT FAMOUNT, FPRICE FROM TGOODS

WHERE (FID

---

= :VMY_GOODSID)

INTO VMY_AMOUNT_DISPLAY, :VMY_PRICE_DISPLAY;

/* РАСЧИТАТЬ НОВОЕ КОЛИЧЕСТВО */

IF (:VMY_AMOUNT_DISPLAY IS NULL) THEN VMY_AMOUNT_DISPLAY = 0;

VMY_AMOUNT = (:VMY_AMOUNT_DISPLAY + :VMY_AMOUNT);

/* РАСЧИТАТЬ НОВУЮ ЦЕНУ */

IF (:VMY_PRICE_DISPLAY IS NOT NULL) THEN

IF ((:VMY_PRICE_DISPLAY = 0) OR ((:VMY_PRICE_DISPLAY <>

:VMY_PRICE))) THEN

VMY_PRICE = 0;

/* ОБНОВИТЬ ТАБЛИЦУ */ UPDATE TGOODS

SET FAMOUNT = :VMY_AMOUNT,

FPRICE = :VMY_PRICE WHERE FID = :VMY_GOODSID;

END /* FOR */ End
create or alter procedure PDOCUMTITLES_GETTITLE ( VIN_SHOWDEL smallint,

VIN_STARTDATE date,

VIN_ENDDATE date, VIN_METHOD smallint, VIN_VALUE integer, VIN_USERID integer)

returns (

VID bigint,

VTYPENAME varchar(20), VTYPEID smallint, VSTATEID smallint, VSTATENAME varchar(15), VDATE date,

VTIME time, VPERSONID bigint, VPERSON varchar(30), VUSER varchar(20), VSUMM decimal(15,2))

as BEGIN

FOR SELECT D.FID, D.FTYPE,b.ftype, D.FSTATE,a.FSTATE, D.FDATE, D.FTIME, D.FPERSON, P.FSHORTNAME, U.FLOGIN, D.FSUMM

FROM ((((TDOCUMTITLES D

LEFT JOIN TPERSON P ON D.FPERSON = P.FID) LEFT JOIN TUSERS U ON D.FUSER = U.FID)

LEFT JOIN tdocumstate a ON D.fstate = a.fid) LEFT JOIN tdocumtype b ON D.ftype = b.fid) WHERE ((:VIN_SHOWDEL = 0 AND

D.FSTATE IN (1, 2) /*ОТОБРАЖАТЬ ТОЛЬКО НЕ УДАЛЕННЫЕ*/ OR (:VIN_SHOWDEL = 1)) /*ОТОБРАЖАТЬ ВСЕ*/

AND

((:VIN_METHOD = 0 AND D.FUSER = :VIN_USERID)

OR (:VIN_METHOD IN (1, 2) AND D.FPERSON = :VIN_VALUE AND D.FUSER =

:VIN_USERID)

OR (:VIN_METHOD = 3 AND D.FTYPE = :VIN_VALUE AND D.FUSER =

:VIN_USERID)

OR (:VIN_METHOD = 5 AND (:VIN_VALUE = 0 OR (:VIN_VALUE > 0 AND D.FUSER = :VIN_VALUE))))

AND (D.FDATE BETWEEN :VIN_STARTDATE AND :VIN_ENDDATE)

OR (:VIN_METHOD = 0 AND D.FSTATE = 0 AND D.FUSER = :VIN_USERID)) INTO :VID, :VTYPEid, :VTYPEname, :VSTATEID, :VSTATENAME, :VDATE, :VTIME,

:VPERSONID, :VPERSON, :VUSER, :VSUMM DO

BEGIN SUSPEND; END

create or alter procedure PGOODS_GETGOODS ( VIN_CURRID integer not null, VIN_AVAILABLE smallint,

VIN_FINDTEXT varchar(40), VIN_SHOWDEL integer)

returns (

VID integer,

VGOODSNAME varchar(150), VARTICLE varchar(30), VUNIT varchar(10), VCOMMENT varchar(30), VPRICE varchar(15),

---

VAMOUNT varchar(10), VSTATE smallint)

as BEGIN

/*VIN_CURRID =

>=0 Return category content

-1 Find AUTO

-2 Find by BARCODE

-3 Find by ID

-4 Find by Name:

-5 Find by Article

*/

FOR SELECT G.FID, G.FSTATE, G.FGOODSNAME, G.FARTICLE, G.FCOMMENT, U.FUNITNAME, G.FAMOUNT, G.FPRICE

FROM (TGOODS G

LEFT JOIN TUNITS U ON G.FUNIT = U.FID) WHERE ((:VIN_CURRID > 0 AND

G.fgroop = :VIN_CURRID)

OR (:VIN_CURRID = -1 AND (

G.FGOODSNAME CONTAINING (:VIN_FINDTEXT) OR G.FARTICLE LIKE :VIN_FINDTEXT || '%'

))

OR (:VIN_CURRID = -3 AND G.FID = :VIN_FINDTEXT)

OR (:VIN_CURRID = -4 AND G.FGOODSNAME CONTAINING (:VIN_FINDTEXT))

OR (:VIN_CURRID = -5 AND G.FARTICLE LIKE :VIN_FINDTEXT || '%')) AND

((:VIN_SHOWDEL = 0 AND

(G.FSTATE = 1)) /*ОТОБРАЖАТЬ ТОЛЬКО НЕ УДАЛЕННЫЕ*/ OR (:VIN_SHOWDEL = 1)) /*ОТОБРАЖАТЬ ВСЕ*/

AND

((:VIN_AVAILABLE = 0) /*ОТОБРАЖАТЬ БЕЗ УЧЕТА НАЛИЧИЯ*/ OR (:VIN_AVAILABLE = 1 AND

G.FAMOUNT IS NOT NULL)) /*ОТОБРАЖАТЬ ТОЛЬКО ТОВАРЫ В НАЛИЧИИ*/

INTO :VID, :VSTATE, :VGOODSNAME, :VARTICLE, :VCOMMENT, :VUNIT,

:VAMOUNT, :VPRICE DO

BEGIN

IF (:VPRICE = 0) THEN VPRICE = '<несколько>'; SUSPEND;

END

---

Создание триггеров

CREATE OR ALTER TRIGGER TSTORE_BI FOR TSTORE ACTIVE BEFORE INSERT POSITION 0

AS BEGIN

IF (NEW.FID IS NULL) THEN

NEW.FID = GEN_ID(GEN_TSTORE_ID,1); END

Создание генераторов

CREATE SEQUENCE GEN_TDOCUMCONTENT_ID; CREATE SEQUENCE GEN_TDOCUMTITLES_ID; CREATE SEQUENCE GEN_TGOODS_ID;

CREATE SEQUENCE GEN_TGROOP_ID; CREATE SEQUENCE GEN_TPERSON_ID; CREATE SEQUENCE GEN_TSTORE_ID; CREATE SEQUENCE GEN_TUNITS_ID; CREATE SEQUENCE GEN_TUSERS_ID;

DataModule2->goods_select->ParamByName("VIN_CURRID")-

>AsInteger=DataModule2->groop_select->FieldByName("VID")->AsInteger; DataModule2->goods_select->ParamByName("VIN_AVAILABLE")-

>AsInteger=0;

DataModule2->goods_select->Open(); Form3->ShowModal();

}//
//ПРОДАЖА ТОВАРА

voidfastcall TForm1::Button2Click(TObject *Sender)

{Application->CreateForm(classid(TForm3), &Form3);

Form3->type_doc->Caption="Продажа товара (Расходная накладная)"; Form3->type->Caption="2";

Form3->state_doc->Caption="Сохранён, не проведён";

DataModule2->new_doc->ParamByName("VIN_TYPE")->AsInteger=2; DataModule2->new_doc->ParamByName("VIN_USER")->AsInteger=1; DataModule2->new_doc->Prepare();

DataModule2->new_doc->ExecProc();

Form3->number_doc->Caption=DataModule2->new_doc-

>ParamByName("VNEWID")->AsString; DataModule2->IBTransaction1->Commit(); DataModule2->person_select->Close();

DataModule2->person_select->ParamByName("VIN_GROUP")->AsInteger=2;

---