-- -----------------------------------------------------------------------------
-- Reports
-- -----------------------------------------------------------------------------


INSERT INTO `ie_zz_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES (NULL, 'Report_', 'Reports to Submit', '140');

-- DROP TABLE IF EXISTS ie_Report;
CREATE TABLE `ie_Report` (
 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `dueDateMonth` char(2) NULL,
 `dueDateDay` char(2) NULL,
 `dueIdx` INT( 11 ) NOT NULL DEFAULT  '2000' COMMENT  '($month+($month >= START_MONTH ? -(START_MONTH-1) : (12-START_MONTH+1))) * 100 + $day',
 `section` varchar(64) NOT NULL,
 `topic` varchar(64) NOT NULL,
 `description` TEXT NOT NULL,
 `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 INDEX (`dueDateMonth`),
 INDEX (`dueDateDay`),
 INDEX (`dueIdx`),
 INDEX (`section`),
 INDEX (`topic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


-- DROP TABLE IF EXISTS ie_ReportExclude;
CREATE TABLE IF NOT EXISTS `ie_ReportExclude` (
 `id` int(11) unsigned NOT NULL,
 `reportId` int(11) NOT NULL,
 `schoolId` int(11) NOT NULL,
 `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


-- DROP TABLE IF EXISTS ie_UserReport;
CREATE TABLE `ie_UserReport` (
 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `reportId` int(11) UNSIGNED NOT NULL,
 `userId` int(11) UNSIGNED NOT NULL,
 `schoolId` int(11) UNSIGNED NOT NULL,
 `file` varchar(255) NOT NULL,
 `comments` TEXT NOT NULL DEFAULT '',
 `addedIdx` INT(11) NOT NULL DEFAULT  0 COMMENT  '($month+($month >= START_MONTH ? -(START_MONTH-1) : (12-START_MONTH+1))) * 100 + $day',
 `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 INDEX (`reportId`),
 INDEX (`userId`),
 INDEX (`addedIdx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

ALTER TABLE `ie_zz_Admin`  ADD `reportsAccess` ENUM('y', 'n') NOT NULL DEFAULT 'n'  AFTER `stsLoginEnabled`;
ALTER TABLE `ie_zz_Admin`  ADD `canEditReportsAccess` ENUM('y', 'n') NOT NULL DEFAULT 'n'  AFTER `reportsAccess`;
INSERT INTO `ie_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES ('reportInstructions', '6', 'htmlEditor', '', '', '', '', 'n', 'n', '7', '2009-09-11 12:35:43', '2014-11-18 10:26:05');
INSERT INTO `ie_zz_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES (NULL, 'Report Instructions', 'n', '6');
INSERT INTO `ie_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES (NULL, 'reportInstructions', 'mode', 'view', 'string');


INSERT INTO `ie_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('reportOverdueEmailHeader', 6, 'header', '', 'Reports Notification Email Template', '', '', 'n', 'n', 180, '2014-11-24 15:30:00', '2014-11-24 15:30:00'),
('reportOverdueEmailFrom', 6, 'text', 'From Email Address', 'Friends of  Education <info@improvek-12education.org>', '', '', 'n', 'n', 181, '2014-11-24 15:30:00', '2014-11-24 15:30:00'),
('reportOverdueEmailSubject', 6, 'text', 'Subject', 'Overdue Reports Notification from improvek-12education.org', '', '', 'y', 'n', 182, '2014-11-24 15:30:00', '2014-11-24 15:30:00'),
('reportOverdueEmailBody', 6, 'HtmlEditor', 'Message Body', '', '', 'Constants: {date}, {schoolName}, {count}, {list}, {link}', 'y', 'n', 183, '2014-11-24 15:30:00', '2014-11-24 15:30:00');

INSERT INTO `ie_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'reportOverdueEmailBody', 'mode', 'view', 'string'),
(NULL, 'reportOverdueEmailBody', 'toolbarSet', 'Basic', 'string'),
(NULL, 'reportOverdueEmailBody', 'scriptOptions[Config][EnterMode]', 'br', 'string');

UPDATE `ie_zz_Admin` SET `canEditReportsAccess` =  'y' WHERE  `login` = 'support';


-- Manually add page
-- Alias:          "reports-to-submit"
-- Handler:        "User Page"
-- Body Template:  "logged.tpl"
-- Options:        [x] Final Page
-- Title:          "Reports to Submit"
-- Items:
--     Reports to submit