60 lines
2.2 KiB
SQL
60 lines
2.2 KiB
SQL
CREATE TABLE bargeldkasse (
|
|
`Oid` BIGINT(19) NOT NULL AUTO_INCREMENT,
|
|
`Tid` INT(10) DEFAULT NULL,
|
|
`IsActive` TINYINT(1) DEFAULT NULL,
|
|
`Notice` VARCHAR(1024) DEFAULT NULL,
|
|
`InsTs` DATETIME DEFAULT NULL,
|
|
`InsUser` VARCHAR(256) DEFAULT NULL,
|
|
`Version` BIGINT(19) DEFAULT NULL,
|
|
`UdpUser` VARCHAR(256) DEFAULT NULL,
|
|
`SystemEntryID` INT(10) DEFAULT NULL,
|
|
`ObjectOid` BIGINT(19) DEFAULT NULL,
|
|
`ObjectTid` INT(10) DEFAULT NULL,
|
|
`Kassenname` VARCHAR(256) DEFAULT NULL,
|
|
`Auszahlungsintervall` TINYINT(10) DEFAULT NULL,
|
|
PRIMARY KEY (`Oid`)
|
|
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;
|
|
|
|
ALTER TABLE bargeldtransaktion DROP FOREIGN KEY `FK_BARGELDTRANSAKTION_CUSTOMER`;
|
|
|
|
ALTER TABLE bargeldtransaktion
|
|
ADD COLUMN `BargeldkassenOid` BIGINT(19) DEFAULT NULL,
|
|
ADD FOREIGN KEY `FK_BARGELDTRANSAKTION_BARGELDKASSE`(`BargeldkassenOid`) REFERENCES `bargeldkasse`(`Oid`) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
#Bargeldkassen für Klienten, die schon Bargeldtransaktionen haben, erzeugen.
|
|
INSERT INTO bargeldkasse(Tid, IsActive, InsTs, InsUser, Version, UdpUser, ObjectOid, ObjectTid, Kassenname, Auszahlungsintervall)
|
|
SELECT DISTINCT 114, 1, NOW(), 'System', 1, 'System', CustomerOid, 8, 'Bargeldkasse', c.Auszahlungsintervall FROM bargeldtransaktion bt JOIN customer c ON bt.CustomerOid = c.Oid;
|
|
|
|
ALTER TABLE customer DROP COLUMN `Auszahlungsintervall`;
|
|
|
|
#Verbindung zwischen Bargeldtransaktionen und Bargeldkassen herstellen
|
|
|
|
DELIMITER ;;
|
|
|
|
DROP PROCEDURE IF EXISTS connect_bargeldtransaktionen_to_bargeldkassen;;
|
|
CREATE PROCEDURE connect_bargeldtransaktionen_to_bargeldkassen()
|
|
BEGIN
|
|
DECLARE done INT DEFAULT FALSE;
|
|
DECLARE bkOid BIGINT(19);
|
|
DECLARE cOid BIGINT(19);
|
|
|
|
DECLARE curs1 CURSOR FOR SELECT Oid, ObjectOid FROM bargeldkasse WHERE ObjectTid = 8;
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
|
|
OPEN curs1;
|
|
|
|
read_loop: LOOP
|
|
FETCH curs1 INTO bkOid, cOid;
|
|
IF done
|
|
THEN LEAVE read_loop;
|
|
END IF;
|
|
UPDATE bargeldtransaktion SET BargeldkassenOid = bkOid WHERE CustomerOid = cOid AND BargeldkassenOid IS NULL;
|
|
END LOOP;
|
|
|
|
CLOSE curs1;
|
|
END;;
|
|
DELIMITER ;
|
|
|
|
CALL connect_bargeldtransaktionen_to_bargeldkassen();
|
|
|
|
DROP PROCEDURE connect_bargeldtransaktionen_to_bargeldkassen; |