-- http://www.phpmyadmin.net
--
-- Hoszt: localhost
-- Létrehozás ideje: 2015. Ápr 11. 16:15
-- Szerver verzió: 5.5.38-0ubuntu0.14.04.1
-- PHP verzió: 5.5.9-1ubuntu4.5




--
-- Adatbázis: `szamtar`
--

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `worksheet`
--

CREATE TABLE IF NOT EXISTS `worksheet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `worksheetNumber` varchar(16) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `firstName` varchar(25) COLLATE utf8_hungarian_ci NOT NULL,
  `lastName` varchar(25) COLLATE utf8_hungarian_ci NOT NULL,
  `address` varchar(50) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `phone` varchar(15) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `phone2` varchar(15) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `device` varchar(8) COLLATE utf8_hungarian_ci NOT NULL,
  `dateIncome` datetime DEFAULT NULL,
  `dateOut` datetime DEFAULT NULL,
  `dateReady` datetime DEFAULT NULL,
  `warranty` tinyint(1) DEFAULT NULL,
  `errorMs` mediumtext COLLATE utf8_hungarian_ci NOT NULL,
  `description` mediumtext COLLATE utf8_hungarian_ci,
  `specification` mediumtext COLLATE utf8_hungarian_ci,
  `broken` tinyint(1) DEFAULT NULL,
  `archive` tinyint(1) DEFAULT NULL,
  `ready` tinyint(1) DEFAULT NULL,
  `taken` tinyint(1) DEFAULT NULL,
  `password` varchar(25) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `sparePrice` int(11) DEFAULT NULL,
  `workPrice` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `urgent` tinyint(1) DEFAULT NULL,
  `scratched` tinyint(1) DEFAULT NULL,
  `type` varchar(50) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `backup` mediumtext COLLATE utf8_hungarian_ci,
  `counted` int(11) DEFAULT NULL,
  `user` varchar(15) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `inWarranty` tinyint(1) DEFAULT NULL,
  `adapter` tinyint(1) DEFAULT NULL,
  `bag` tinyint(1) DEFAULT NULL,
  `battery` tinyint(1) DEFAULT NULL,
  `recipient` varchar(50) COLLATE utf8_hungarian_ci DEFAULT NULL,
  `notify` tinyint(1) DEFAULT NULL,
  `dateNotify` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=103 ;

--
-- Eseményindítók `worksheet`
--
DROP TRIGGER IF EXISTS `worksheet_seq`;
DELIMITER //
CREATE TRIGGER `worksheet_seq` BEFORE INSERT ON `worksheet`
 FOR EACH ROW BEGIN

	DECLARE creationdate DATE;
	DECLARE maxdayid INTEGER;
	DECLARE rowid INTEGER;
	DECLARE newrowid INTEGER;
	DECLARE newdayid INTEGER;
	
	SET creationdate = CURDATE();

	SELECT MAX(CAST(SUBSTR(worksheetNumber FROM 14) AS UNSIGNED)) INTO maxdayid FROM worksheet WHERE DATE_FORMAT(dateIncome,'%Y-%m-%d') = creationdate;
	SELECT id INTO rowid FROM worksheet WHERE DATE_FORMAT( dateIncome,  '%Y-%m-%d' ) = CURDATE( ) AND CAST( SUBSTR( worksheetNumber FROM 14 ) AS UNSIGNED ) = maxdayid;
	SELECT `AUTO_INCREMENT` INTO newrowid FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'szamtar_orig' AND TABLE_NAME = 'worksheet';
	
	IF maxdayid IS NULL THEN
		SET newdayid = 1;
	ELSEIF maxdayid IS NOT NULL THEN 
		SET newdayid = maxdayid + (newrowid - rowid);
	END IF;

	SET NEW.`worksheetNumber` = CONCAT("SZTH",DATE_FORMAT(creationdate,'%Y%m%d'),'-',newdayid);
END
//
DELIMITER ;

