metadata_changes.sql

/*------ 21.04.2004 16:15:49 --------*/

CREATE TABLE MUSTERI (
MUSTERI_KOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:15:49 --------*/

alter table MUSTERI
add constraint PK_MUSTERI
primary key (MUSTERI_KOD);


/*------ 21.04.2004 16:15:49 --------*/

CREATE GENERATOR MUSTERI_MUSTERI_KOD_GEN;


/*------ 21.04.2004 16:15:49 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_MUSTERI_MUSTERI_KOD FOR MUSTERI
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.MUSTERI_KOD IS NULL) THEN
      NEW.MUSTERI_KOD = GEN_ID(MUSTERI_MUSTERI_KOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:16:16 --------*/

ALTER TABLE MUSTERI
ADD MUSTERI_AD VARCHAR(100) CHARACTER SET WIN1254 
NOT NULL 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:16:52 --------*/

alter table MUSTERI add MUSTERI_VERGIDAIRESI VARCHAR (100) CHARACTER SET WIN1254 NOT NULL COLLATE PXW_TURK;


/*------ 21.04.2004 16:16:56 --------*/

update MUSTERI set MUSTERI_VERGIDAIRESI=MUSTERI_AD;


/*------ 21.04.2004 16:17:05 --------*/

alter table MUSTERI add MUSTERI_VERGINO VARCHAR (100) CHARACTER SET WIN1254 NOT NULL COLLATE PXW_TURK;


/*------ 21.04.2004 16:17:21 --------*/

alter table MUSTERI add MUSTERI_ADRES VARCHAR (100) CHARACTER SET WIN1254 NOT NULL COLLATE PXW_TURK;


/*------ 21.04.2004 16:17:55 --------*/

ALTER TABLE MUSTERI
ADD MUSTERI_GRUPKODU INTEGER;


/*------ 21.04.2004 16:18:00 --------*/

update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'MUSTERI_ADRES') and
(RDB$RELATION_NAME = 'MUSTERI')
;


/*------ 21.04.2004 16:18:04 --------*/

update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'MUSTERI_VERGINO') and
(RDB$RELATION_NAME = 'MUSTERI')
;


/*------ 21.04.2004 16:18:10 --------*/

update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'MUSTERI_VERGIDAIRESI') and
(RDB$RELATION_NAME = 'MUSTERI')
;


/*------ 21.04.2004 16:18:56 --------*/

CREATE TABLE MASRAFLAR (
MASRAFKOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:18:56 --------*/

alter table MASRAFLAR
add constraint PK_MASRAFLAR
primary key (MASRAFKOD);


/*------ 21.04.2004 16:18:56 --------*/

CREATE GENERATOR MASRAFLAR_MASRAFKOD_GEN;


/*------ 21.04.2004 16:18:56 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_MASRAFLAR_MASRAFKOD FOR MASRAFLAR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.MASRAFKOD IS NULL) THEN
      NEW.MASRAFKOD = GEN_ID(MASRAFLAR_MASRAFKOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:19:34 --------*/

ALTER TABLE MASRAFLAR
ADD MASRAFAD VARCHAR(100) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:20:41 --------*/

CREATE TABLE PERSONEL (
PERSONELKOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:20:41 --------*/

alter table PERSONEL
add constraint PK_PERSONEL
primary key (PERSONELKOD);


/*------ 21.04.2004 16:20:41 --------*/

CREATE GENERATOR PERSONEL_PERSONELKOD_GEN;


/*------ 21.04.2004 16:20:41 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_PERSONEL_PERSONELKOD FOR PERSONEL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.PERSONELKOD IS NULL) THEN
      NEW.PERSONELKOD = GEN_ID(PERSONEL_PERSONELKOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:21:14 --------*/

ALTER TABLE PERSONEL
ADD PERSONELADSOYAD VARCHAR(150) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:21:38 --------*/

ALTER TABLE PERSONEL
ADD PERSONELUNVAN VARCHAR(100) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:21:58 --------*/

ALTER TABLE PERSONEL
ADD PERSONELGRUPKODU INTEGER;


/*------ 21.04.2004 16:23:02 --------*/

ALTER TABLE PERSONEL
ADD PERSONELEKIPKODU INTEGER;


/*------ 21.04.2004 16:23:23 --------*/

CREATE TABLE EKIP (
EKIPKODU INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:23:23 --------*/

alter table EKIP
add constraint PK_EKIP
primary key (EKIPKODU);


/*------ 21.04.2004 16:23:23 --------*/

CREATE GENERATOR EKIP_EKIPKODU_GEN;


/*------ 21.04.2004 16:23:23 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_EKIP_EKIPKODU FOR EKIP
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.EKIPKODU IS NULL) THEN
      NEW.EKIPKODU = GEN_ID(EKIP_EKIPKODU_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:23:46 --------*/

ALTER TABLE EKIP
ADD EKIPAD VARCHAR(150) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:25:03 --------*/

CREATE TABLE HARCAMALAR (
HARCAMALAR_KOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:25:03 --------*/

alter table HARCAMALAR
add constraint PK_HARCAMALAR
primary key (HARCAMALAR_KOD);


/*------ 21.04.2004 16:25:03 --------*/

CREATE GENERATOR HARCAMALAR_HARCAMALAR_KOD_GEN;


/*------ 21.04.2004 16:25:03 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_HARCAMALAR_HARCAMALAR_KOD FOR HARCAMALAR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.HARCAMALAR_KOD IS NULL) THEN
      NEW.HARCAMALAR_KOD = GEN_ID(HARCAMALAR_HARCAMALAR_KOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:26:56 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_EKIPKODU INTEGER
NOT NULL ;


/*------ 21.04.2004 16:27:40 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_TARIH DATE
DEFAULT 'TODAY' 
NOT NULL ;


/*------ 21.04.2004 16:27:47 --------*/

update HARCAMALAR set HARCAMALAR_TARIH=1;


/*------ 21.04.2004 16:28:36 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_MASRAFKODU INTEGER
NOT NULL ;


/*------ 21.04.2004 16:29:00 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_MIKTAR INTEGER
NOT NULL ;


/*------ 21.04.2004 16:30:16 --------*/

alter table HARCAMALAR
add constraint FK_HARCAMALAR
foreign key (HARCAMALAR_EKIPKODU)
references EKIP(EKIPKODU)
on update CASCADE;


/*------ 21.04.2004 16:30:24 --------*/

ALTER TABLE HARCAMALAR DROP CONSTRAINT FK_HARCAMALAR;


/*------ 21.04.2004 16:30:24 --------*/

alter table HARCAMALAR
add constraint FK_HARCAMALAR_EKIP
foreign key (HARCAMALAR_EKIPKODU)
references EKIP(EKIPKODU)
on update CASCADE;


/*------ 21.04.2004 16:30:49 --------*/

alter table HARCAMALAR
add constraint FK_HARCAMALAR_MASRAFLAR
foreign key (HARCAMALAR_MASRAFKODU)
references MASRAFLAR(MASRAFKOD)
on update CASCADE;


/*------ 21.04.2004 16:31:09 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_KOD')
;


/*------ 21.04.2004 16:31:09 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_TARIH')
;


/*------ 21.04.2004 16:31:09 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_EKIPKODU')
;


/*------ 21.04.2004 16:31:09 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MASRAFKODU')
;


/*------ 21.04.2004 16:31:09 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MIKTAR')
;


/*------ 21.04.2004 16:31:36 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_ACIKLAMA VARCHAR(150) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 16:32:18 --------*/

alter table PERSONEL
add constraint FK_PERSONEL_EKIP
foreign key (PERSONELEKIPKODU)
references EKIP(EKIPKODU)
on update CASCADE;


/*------ 21.04.2004 16:33:15 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_MUSTERIKODU INTEGER
NOT NULL ;


/*------ 21.04.2004 16:33:41 --------*/

alter table HARCAMALAR
add constraint FK_HARCAMALAR_MUSTERI
foreign key (HARCAMALAR_MUSTERIKODU)
references MUSTERI(MUSTERI_KOD)
on update CASCADE;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_KOD')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_TARIH')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MUSTERIKODU')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_EKIPKODU')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MASRAFKODU')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 5
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MIKTAR')
;


/*------ 21.04.2004 16:33:53 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 6
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_ACIKLAMA')
;


/*------ 21.04.2004 16:36:24 --------*/

CREATE TABLE AVANSLAR (
AVANSLAR_KOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 16:36:24 --------*/

alter table AVANSLAR
add constraint PK_AVANSLAR
primary key (AVANSLAR_KOD);


/*------ 21.04.2004 16:36:24 --------*/

CREATE GENERATOR AVANSLAR_AVANSLAR_KOD_GEN;


/*------ 21.04.2004 16:36:24 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_AVANSLAR_AVANSLAR_KOD FOR AVANSLAR
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.AVANSLAR_KOD IS NULL) THEN
      NEW.AVANSLAR_KOD = GEN_ID(AVANSLAR_AVANSLAR_KOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 16:36:59 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_EKIPKOD INTEGER
NOT NULL ;


/*------ 21.04.2004 16:37:16 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_TARIH DATE
DEFAULT 'TODAY' ;


/*------ 21.04.2004 16:37:47 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_MIKTAR NUMERIC(18,0);


/*------ 21.04.2004 16:38:11 --------*/

ALTER TABLE HARCAMALAR
ALTER COLUMN HARCAMALAR_MIKTAR
TYPE 
DOUBLE PRECISION;


/*------ 21.04.2004 16:38:52 --------*/

alter table "HARCAMALAR" drop "HARCAMALAR_MIKTAR";


/*------ 21.04.2004 16:39:02 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_MIKTAR NUMERIC(18,0);


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_KOD')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_TARIH')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MUSTERIKODU')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_EKIPKODU')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MASRAFKODU')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 5
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MIKTAR')
;


/*------ 21.04.2004 16:39:08 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 6
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_ACIKLAMA')
;


/*------ 21.04.2004 16:39:37 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_MUSTERIKOD INTEGER;


/*------ 21.04.2004 16:39:56 --------*/

alter table AVANSLAR
add constraint FK_AVANSLAR_EKIPKOD
foreign key (AVANSLAR_EKIPKOD)
references EKIP(EKIPKODU)
on update CASCADE;


/*------ 21.04.2004 16:40:26 --------*/

alter table AVANSLAR
add constraint FK_AVANSLAR_MUSTERI
foreign key (AVANSLAR_MUSTERIKOD)
references MUSTERI(MUSTERI_KOD)
on update CASCADE;


/*------ 21.04.2004 17:03:46 --------*/

CREATE TABLE HARCIRAH (
HARCIRAH_KOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 17:03:46 --------*/

alter table HARCIRAH
add constraint PK_HARCIRAH
primary key (HARCIRAH_KOD);


/*------ 21.04.2004 17:03:46 --------*/

CREATE GENERATOR HARCIRAH_HARCIRAH_KOD_GEN;


/*------ 21.04.2004 17:03:46 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_HARCIRAH_HARCIRAH_KOD FOR HARCIRAH
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.HARCIRAH_KOD IS NULL) THEN
      NEW.HARCIRAH_KOD = GEN_ID(HARCIRAH_HARCIRAH_KOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 17:04:09 --------*/

ALTER TABLE HARCIRAH
ADD HARCIRAH_BASLANGICDATE DATE
NOT NULL ;


/*------ 21.04.2004 17:04:22 --------*/

alter table HARCIRAH add HARCIRAH_BITISDATE DATE NOT NULL;


/*------ 21.04.2004 17:04:52 --------*/

ALTER TABLE HARCIRAH
ADD HARCIRAH_GUNLUKBEDEL NUMERIC(18,0)
NOT NULL ;


/*------ 21.04.2004 17:05:16 --------*/

ALTER TABLE HARCIRAH
ADD HARCIRAH_PERSONELKODU INTEGER
NOT NULL ;


/*------ 21.04.2004 17:05:33 --------*/

alter table HARCIRAH
add constraint FK_HARCIRAH_PERSONEL
foreign key (HARCIRAH_PERSONELKODU)
references PERSONEL(PERSONELKOD)
on update CASCADE;


/*------ 21.04.2004 17:07:20 --------*/

ALTER TABLE EKIP
ADD EKIPSBASI INTEGER
NOT NULL ;


/*------ 21.04.2004 17:07:40 --------*/

alter table EKIP
add constraint FK_EKIP_PERSONEL
foreign key (EKIPSBASI)
references PERSONEL(PERSONELKOD)
on update CASCADE;


/*------ 21.04.2004 17:24:03 --------*/

CREATE TABLE FIS (
FIS_KOD INTEGER
NOT NULL 
);


/*------ 21.04.2004 17:24:03 --------*/

alter table FIS
add constraint PK_FIS
primary key (FIS_KOD);


/*------ 21.04.2004 17:24:03 --------*/

CREATE GENERATOR FIS_FIS_KOD_GEN;


/*------ 21.04.2004 17:24:03 --------*/

SET TERM ^ ;

CREATE TRIGGER AI_FIS_FIS_KOD FOR FIS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.FIS_KOD IS NULL) THEN
      NEW.FIS_KOD = GEN_ID(FIS_FIS_KOD_GEN, 1);
END
^

SET TERM ; ^




/*------ 21.04.2004 17:24:29 --------*/

ALTER TABLE FIS
ADD FIS_TARIH DATE
DEFAULT 'TODAY' 
NOT NULL ;


/*------ 21.04.2004 17:25:11 --------*/

ALTER TABLE FIS
ADD FIS_SIPARISLER VARCHAR(100) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 21.04.2004 17:41:36 --------*/

ALTER TABLE HARCIRAH
ADD HARCIRAH_FISKOD INTEGER
NOT NULL ;


/*------ 21.04.2004 17:41:50 --------*/

alter table HARCIRAH
add constraint FK_HARCIRAH
foreign key (HARCIRAH_FISKOD)
references FIS(FIS_KOD)
on update CASCADE;


/*------ 21.04.2004 17:53:12 --------*/

ALTER TABLE FIS
ADD FIS_MUSTERIKODU INTEGER
NOT NULL ;


/*------ 21.04.2004 17:53:37 --------*/

alter table FIS
add constraint FK_FIS_MUSTERI
foreign key (FIS_MUSTERIKODU)
references MUSTERI(MUSTERI_KOD)
on update CASCADE;


/*------ 22.04.2004 08:23:43 --------*/

update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'EKIPSBASI') and
(RDB$RELATION_NAME = 'EKIP')
;


/*------ 22.04.2004 10:12:50 --------*/

alter table "HARCAMALAR" drop "HARCAMALAR_MIKTAR";


/*------ 22.04.2004 10:12:59 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_MIKTAR DOUBLE PRECISION;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_KOD')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_TARIH')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MUSTERIKODU')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_EKIPKODU')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MASRAFKODU')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 5
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MIKTAR')
;


/*------ 22.04.2004 10:13:06 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 6
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_ACIKLAMA')
;


/*------ 22.04.2004 10:13:21 --------*/

alter table "AVANSLAR" drop "AVANSLAR_MIKTAR";


/*------ 22.04.2004 10:13:30 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_MIKTAR DOUBLE PRECISION;


/*------ 22.04.2004 10:13:34 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'AVANSLAR') and 
(RDB$FIELD_NAME= 'AVANSLAR_KOD')
;


/*------ 22.04.2004 10:13:34 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'AVANSLAR') and 
(RDB$FIELD_NAME= 'AVANSLAR_EKIPKOD')
;


/*------ 22.04.2004 10:13:34 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'AVANSLAR') and 
(RDB$FIELD_NAME= 'AVANSLAR_TARIH')
;


/*------ 22.04.2004 10:13:34 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'AVANSLAR') and 
(RDB$FIELD_NAME= 'AVANSLAR_MIKTAR')
;


/*------ 22.04.2004 10:13:34 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'AVANSLAR') and 
(RDB$FIELD_NAME= 'AVANSLAR_MUSTERIKOD')
;


/*------ 22.04.2004 11:39:40 --------*/

ALTER TABLE HARCAMALAR DROP CONSTRAINT FK_HARCAMALAR_MUSTERI;


/*------ 22.04.2004 11:39:47 --------*/

ALTER TABLE HARCAMALAR DROP CONSTRAINT FK_HARCAMALAR_EKIP;


/*------ 22.04.2004 11:39:53 --------*/

alter table "HARCAMALAR" drop "HARCAMALAR_EKIPKODU";


/*------ 22.04.2004 11:39:58 --------*/

alter table "HARCAMALAR" drop "HARCAMALAR_MUSTERIKODU";


/*------ 22.04.2004 11:40:19 --------*/

ALTER TABLE HARCAMALAR
ADD HARCAMALAR_FISKOD INTEGER
NOT NULL ;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_KOD')
;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_FISKOD')
;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_TARIH')
;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MASRAFKODU')
;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_MIKTAR')
;


/*------ 22.04.2004 11:40:28 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 5
where (RDB$RELATION_NAME = 'HARCAMALAR') and 
(RDB$FIELD_NAME= 'HARCAMALAR_ACIKLAMA')
;


/*------ 22.04.2004 11:41:10 --------*/

alter table HARCAMALAR
add constraint FK_HARCAMALAR_FISKOD
foreign key (HARCAMALAR_FISKOD)
references FIS(FIS_KOD)
on delete CASCADE
on update CASCADE;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 0
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_KOD')
;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 1
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_FISKOD')
;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 2
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_BASLANGICDATE')
;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 3
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_BITISDATE')
;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 4
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_GUNLUKBEDEL')
;


/*------ 22.04.2004 11:41:37 --------*/

update RDB$RELATION_FIELDS set RDB$FIELD_POSITION = 5
where (RDB$RELATION_NAME = 'HARCIRAH') and 
(RDB$FIELD_NAME= 'HARCIRAH_PERSONELKODU')
;


/*------ 22.04.2004 16:05:37 --------*/

ALTER TABLE HARCIRAH DROP CONSTRAINT FK_HARCIRAH;


/*------ 22.04.2004 16:06:04 --------*/

alter table HARCIRAH
add constraint FK_HARCIRAH_FISKOD
foreign key (HARCIRAH_FISKOD)
references FIS(FIS_KOD)
on delete CASCADE
on update CASCADE;


/*------ 22.04.2004 17:05:10 --------*/

SET TERM ^ ;

CREATE PROCEDURE FIS_HAZIRLA (FISKODU INTEGER)
returns
(TIP VARCHAR(20),
 KOD INTEGER,
 TARIH DATE,
 SIPARISLER VARCHAR(100),
 MUSTERIAD VARCHAR(100),
 MASRAFAD VARCHAR(100),
 MIKTAR DOUBLE PRECISION,
 ACIKLAMA VARCHAR(100),
 BASLADATE DATE,
 BITISDATE DATE,
 GUNLUKBEDEL DOUBLE PRECISION,
 GUNSAYISI INTEGER,
 TOPLAM DOUBLE PRECISION,
 PERSONELADSOYAD VARCHAR(100),
 TOPLAMHARCAMA DOUBLE PRECISION,
 TOPLAMHARCIRAH DOUBLE PRECISION,
 AVANSTOPLAM DOUBLE PRECISION
)
AS
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD;
  SUSPEND;
  
  
END
^

SET TERM ; ^




/*------ 22.04.2004 17:15:32 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    MIKTAR DOUBLE PRECISION,
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAM DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD;
  SUSPEND;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  INTO :TARIH,:MASRAFAD,:MIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAM
  DO
    SUSPEND;
  
END
^

SET TERM ; ^




/*------ 22.04.2004 17:17:20 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD;
  SUSPEND;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;
  
END
^

SET TERM ; ^




/*------ 22.04.2004 17:19:01 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;
  
END
^

SET TERM ; ^




/*------ 22.04.2004 17:30:34 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_KOD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;

  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = : MUSTERIKODU
  INTO :AVANSTOPLAM;
END
^

SET TERM ; ^




/*------ 22.04.2004 17:32:21 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_KOD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;

  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = : MUSTERIKODU
  INTO :AVANSTOPLAM;
END
^

SET TERM ; ^




/*------ 22.04.2004 17:35:22 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_KOD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;

  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = : MUSTERIKODU
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 22.04.2004 17:36:39 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_KOD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = : MUSTERIKODU
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 22.04.2004 17:37:28 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_KOD
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = :MUSTERIKODU
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 22.04.2004 17:38:36 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE AVANSLAR_MUSTERIKOD = :MUSTERIKODU
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 23.04.2004 09:09:38 --------*/

CREATE DOMAIN BOOLEAN_DOMAIN AS 
CHAR(1) CHARACTER SET WIN1254 
DEFAULT 'Y' 
NOT NULL 
CHECK (VALUE IN ('Y','N')) 
COLLATE PXW_TURK ;


/*------ 23.04.2004 09:10:51 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_ODENDIMI BOOLEAN_DOMAIN
DEFAULT 'N' 
NOT NULL 
COLLATE PXW_TURK ;


/*------ 23.04.2004 09:10:58 --------*/

update AVANSLAR set AVANSLAR_ODENDIMI='N';


/*------ 23.04.2004 09:12:04 --------*/

ALTER TABLE AVANSLAR
ADD AVANSLAR_ACIKLAMA VARCHAR(150) CHARACTER SET WIN1254 
COLLATE PXW_TURK ;


/*------ 24.04.2004 07:58:29 --------*/

ALTER TABLE FIS
ADD FIS_EKIPKODU INTEGER
NOT NULL ;


/*------ 24.04.2004 07:58:37 --------*/

update FIS set FIS_EKIPKODU=4;


/*------ 24.04.2004 07:59:12 --------*/

alter table FIS
add constraint FK_FIS_EKIP
foreign key (FIS_EKIPKODU)
references EKIP(EKIPKODU)
on update CASCADE;


/*------ 24.04.2004 08:03:13 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    SUM(H2.HARCAMALAR_MIKTAR)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    SUM(AVANSLAR_MIKTAR)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 08:04:49 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 08:07:35 --------*/

CREATE EXCEPTION EXCP_FIS_TANIMSIZ 'BELIRTILEN FIS VERITABANINDA TANIMLANMAMISTIR. BASKA BR FIS SECINIZ...';


/*------ 24.04.2004 08:11:28 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
declare variable MEVCUTMU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT COUNT(FIS_KOD) FROM FIS INTO :MEVCUTMU;
  if (:MEVCUTMU = 0) then
  BEGIN
    EXCEPTION EXCP_FIS_TANIMSIZ;
    EXIT;
  END

  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 08:13:02 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
declare variable MEVCUTMU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT COUNT(FIS_KOD) FROM FIS WHERE FIS_KOD = :FISKODU INTO :MEVCUTMU;
  if (:MEVCUTMU = 0) then
  BEGIN
    EXCEPTION EXCP_FIS_TANIMSIZ;
    EXIT;
  END

  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD=MUSTERIKODU;
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 08:13:25 --------*/

ALTER EXCEPTION EXCP_FIS_TANIMSIZ 'BELIRTILEN FIS VERITABANINDA TANIMLANMAMISTIR. BASKA BIR FIS SECINIZ...';


/*------ 24.04.2004 11:06:09 --------*/

update RDB$RELATION_FIELDS set
RDB$NULL_FLAG = 1
where (RDB$FIELD_NAME = 'PERSONELADSOYAD') and
(RDB$RELATION_NAME = 'PERSONEL')
;


/*------ 24.04.2004 12:35:20 --------*/

CREATE TABLE TANIMLAR (
TANIMAD CHAR(10) CHARACTER SET WIN1254 
NOT NULL 
COLLATE PXW_TURK 
);


/*------ 24.04.2004 12:35:20 --------*/

alter table TANIMLAR
add constraint PK_TANIMLAR
primary key (TANIMAD);


/*------ 24.04.2004 12:35:45 --------*/

ALTER TABLE TANIMLAR
ADD DEGER VARCHAR(50) CHARACTER SET WIN1254 
NOT NULL 
COLLATE PXW_TURK ;


/*------ 24.04.2004 12:35:54 --------*/

update TANIMLAR set DEGER=0;


/*------ 24.04.2004 16:00:44 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100),
    TOPLAMHARCAMA DOUBLE PRECISION,
    TOPLAMHARCIRAH DOUBLE PRECISION,
    AVANSTOPLAM DOUBLE PRECISION)
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
declare variable MEVCUTMU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT COUNT(FIS_KOD) FROM FIS WHERE FIS_KOD = :FISKODU INTO :MEVCUTMU;
  if (:MEVCUTMU = 0) then
  BEGIN
    EXCEPTION EXCP_FIS_TANIMSIZ;
    EXIT;
  END

  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  MUSTERIAD='HARCAMALAR TOPLAM';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMHARCAMA;
  SUSPEND;
  TOPLAMHARCAMA =NULL;

  MUSTERIAD='HARCIRAH TOPLAM';
  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMHARCIRAH;
  SUSPEND;
  TOPLAMHARCIRAH=NULL;

  MUSTERIAD='AVANSLAR TOPLAM';
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :AVANSTOPLAM;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 16:02:42 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100))
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
declare variable MEVCUTMU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT COUNT(FIS_KOD) FROM FIS WHERE FIS_KOD = :FISKODU INTO :MEVCUTMU;
  if (:MEVCUTMU = 0) then
  BEGIN
    EXCEPTION EXCP_FIS_TANIMSIZ;
    EXIT;
  END

  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  MUSTERIAD='HARCAMALAR TOPLAM';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMMIKTAR;
  SUSPEND;

  MUSTERIAD='HARCIRAH TOPLAM';
  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMMIKTAR;
  SUSPEND;

  MUSTERIAD='AVANSLAR TOPLAM';
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :TOPLAMMIKTAR;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 16:20:50 --------*/

SET TERM ^ ;

ALTER PROCEDURE FIS_HAZIRLA (
    FISKODU INTEGER)
RETURNS (
    TIP VARCHAR (20),
    KOD INTEGER,
    TARIH DATE,
    SIPARISLER VARCHAR (100),
    MUSTERIAD VARCHAR (100),
    MASRAFAD VARCHAR (100),
    ACIKLAMA VARCHAR (100),
    BASLADATE DATE,
    BITISDATE DATE,
    GUNLUKBEDEL DOUBLE PRECISION,
    GUNSAYISI INTEGER,
    TOPLAMMIKTAR DOUBLE PRECISION,
    PERSONELADSOYAD VARCHAR (100))
AS
declare variable MUSTERIKODU INTEGER;
declare variable EKIPKODU INTEGER;
declare variable MEVCUTMU INTEGER;
BEGIN
  /* RAPOR ICIN HAZIRLADIM TANSU */
  SELECT COUNT(FIS_KOD) FROM FIS WHERE FIS_KOD = :FISKODU INTO :MEVCUTMU;
  if (:MEVCUTMU = 0) then
  BEGIN
    EXCEPTION EXCP_FIS_TANIMSIZ;
    EXIT;
  END

  TIP = 'FIS';
  SELECT
    F.FIS_KOD,
    F.FIS_TARIH,
    F.FIS_SIPARISLER,
    M.MUSTERI_AD,
    F.FIS_MUSTERIKODU,
    F.FIS_EKIPKODU
  FROM FIS F
  JOIN MUSTERI M
   ON  F.FIS_MUSTERIKODU = M.MUSTERI_KOD
  WHERE F.FIS_KOD = :FISKODU
  INTO :KOD,:TARIH,:SIPARISLER,:MUSTERIAD,:MUSTERIKODU,:EKIPKODU;
  SUSPEND;
  SIPARISLER = NULL;
  MUSTERIAD = NULL;
  
  TIP ='HARCAMALAR';
  FOR
  SELECT
    H.HARCAMALAR_TARIH,
    MA.MASRAFAD,
    H.HARCAMALAR_MIKTAR,
    H.HARCAMALAR_ACIKLAMA
  FROM HARCAMALAR H
  JOIN MASRAFLAR MA
    ON H.HARCAMALAR_MASRAFKODU = MA.MASRAFKOD
  WHERE H.HARCAMALAR_FISKOD = :FISKODU
  INTO :TARIH,:MASRAFAD,:TOPLAMMIKTAR,:ACIKLAMA
  DO
    SUSPEND;
  
  TARIH = NULL;
  MASRAFAD=NULL;
  ACIKLAMA = NULL;
  TIP='HARCIRAH';
  FOR
  SELECT
    HA.HARCIRAH_BASLANGICDATE,
    HA.HARCIRAH_BITISDATE,
    HA.HARCIRAH_GUNLUKBEDEL,
    P.PERSONELADSOYAD,
    (HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE) AS GUNSAYISI,
    ((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL) AS TOPLAMH
  FROM HARCIRAH HA
  JOIN PERSONEL P
    ON P.PERSONELKOD = HA.HARCIRAH_PERSONELKODU
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :BASLADATE,:BITISDATE,:GUNLUKBEDEL,:PERSONELADSOYAD,:GUNSAYISI,:TOPLAMMIKTAR
  DO
    SUSPEND;

  BASLADATE=NULL;
  BITISDATE=NULL;
  GUNLUKBEDEL=NULL;
  PERSONELADSOYAD=NULL;
  GUNSAYISI=NULL;
  TOPLAMMIKTAR=NULL;
  
  TIP ='TOPLAMLAR';
  MUSTERIAD='HARCAMALAR TOPLAM';
  SELECT
    COALESCE(SUM(H2.HARCAMALAR_MIKTAR),0)
  FROM HARCAMALAR H2
  WHERE H2.HARCAMALAR_FISKOD = :FISKODU
  INTO :TOPLAMMIKTAR;
  SUSPEND;

  MUSTERIAD='HARCIRAH TOPLAM';
  SELECT
    COALESCE(SUM((HA.HARCIRAH_BITISDATE - HA.HARCIRAH_BASLANGICDATE)*HA.HARCIRAH_GUNLUKBEDEL),0) AS TOPLAMH
  FROM HARCIRAH HA
  WHERE HA.HARCIRAH_FISKOD = : FISKODU
  INTO :TOPLAMMIKTAR;
  SUSPEND;

  MUSTERIAD='AVANSLAR TOPLAM';
  SELECT
    COALESCE(SUM(AVANSLAR_MIKTAR),0)*(-1)
  FROM AVANSLAR
  WHERE
    (AVANSLAR_MUSTERIKOD = :MUSTERIKODU)
    AND (AVANSLAR_EKIPKOD = :EKIPKODU)
    AND (AVANSLAR_ODENDIMI = 'N')
  INTO :TOPLAMMIKTAR;
  SUSPEND;
END
^

SET TERM ; ^




/*------ 24.04.2004 17:15:46 --------*/

CREATE EXCEPTION EXCP_TARIH 'BASLANGIC VE BITIS TARIHLERINI DUZGUN GIRMELISINIZ....';


/*------ 24.04.2004 17:16:32 --------*/

SET TERM ^ ;

CREATE TRIGGER HARCIRAH_BI FOR HARCIRAH
BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
  /* Trigger body */
  if (NEW.HARCIRAH_BASLANGICDATE>=NEW.HARCIRAH_BITISDATE) then
  BEGIN
    EXCEPTION EXCP_TARIH;
    EXIT;
  END
END
^

SET TERM ; ^