/*Table structure for table `accomplishments` */

DROP TABLE IF EXISTS `accomplishments`;

CREATE TABLE `accomplishments` (
  `ACCOMPLISH_ID` int(11) NOT NULL auto_increment,
  `ACCOMPLISH_NAME` varchar(50) default NULL,
  `DETAILS` varchar(5000) default NULL,
  `STATUS` int(1) default NULL,
  PRIMARY KEY  (`ACCOMPLISH_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `accomplishments` */

insert into `accomplishments` (`ACCOMPLISH_ID`,`ACCOMPLISH_NAME`,`DETAILS`,`STATUS`) values (1,'Annual Accomplishment Report of 2007','Asset Management Department<br />\r\nPRIVATIZATION DIVISION',1);

/*Table structure for table `accomplishments_uploads` */

DROP TABLE IF EXISTS `accomplishments_uploads`;

CREATE TABLE `accomplishments_uploads` (
  `ACCOMPLISH_UPLOAD_ID` int(5) NOT NULL auto_increment,
  `ACCOMPLISH_ID` int(5) default NULL,
  `FILE_NAME` varchar(50) default NULL,
  `UPLOAD_PATH` varchar(100) default NULL,
  PRIMARY KEY  (`ACCOMPLISH_UPLOAD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `accomplishments_uploads` */

/*Table structure for table `account_information` */

DROP TABLE IF EXISTS `account_information`;

CREATE TABLE `account_information` (
  `ACCOUNT_ID` int(3) NOT NULL auto_increment,
  `USER_ID` int(3) default NULL,
  `LNAME` char(20) default NULL,
  `FNAME` char(20) default NULL,
  `MNAME` char(20) default NULL,
  `ADDRESS` char(100) default NULL,
  `TEL_NO` char(20) default NULL,
  `EMAIL` char(50) default NULL,
  `MOBILE_NO` char(20) default NULL,
  PRIMARY KEY  (`ACCOUNT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `account_information` */

insert into `account_information` (`ACCOUNT_ID`,`USER_ID`,`LNAME`,`FNAME`,`MNAME`,`ADDRESS`,`TEL_NO`,`EMAIL`,`MOBILE_NO`) values (1,1,'Herrero','Jonathan Noel','Clado','214 Ilang Ilang St. Greenland Cainta, Rizal','2480774','kwingers@gmail.com','09198515984');
insert into `account_information` (`ACCOUNT_ID`,`USER_ID`,`LNAME`,`FNAME`,`MNAME`,`ADDRESS`,`TEL_NO`,`EMAIL`,`MOBILE_NO`) values (6,10,'HERRERO3','Jonathan Noel3','CLADO3','214 ILANG ILANG ST GREENLAND SUBD. CAINTA, RIZAL3','24807743','kwingers12@hotmail.com3','091985159833');
insert into `account_information` (`ACCOUNT_ID`,`USER_ID`,`LNAME`,`FNAME`,`MNAME`,`ADDRESS`,`TEL_NO`,`EMAIL`,`MOBILE_NO`) values (7,11,'Esguerra','Marlon','C','Tondo','7236709','dep1976@gmail.com','09162025461');

/*Table structure for table `assets_for_privatization` */

DROP TABLE IF EXISTS `assets_for_privatization`;

CREATE TABLE `assets_for_privatization` (
  `PRIVATIZATION_ID` int(11) NOT NULL auto_increment,
  `FILE_TITLE` varchar(50) default NULL,
  `FILE_URL` varchar(100) default NULL,
  `STATUS` int(1) default NULL,
  PRIMARY KEY  (`PRIVATIZATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `assets_for_privatization` */

insert into `assets_for_privatization` (`PRIVATIZATION_ID`,`FILE_TITLE`,`FILE_URL`,`STATUS`) values (1,'Assets for privatization','files/assetsForPrivatization/ANNEX A - priva plan for surrendered & sequestered assets 2008.pdf',1);

/*Table structure for table `cases` */

DROP TABLE IF EXISTS `cases`;

CREATE TABLE `cases` (
  `CASE_ID` int(11) NOT NULL auto_increment,
  `CASE_CODE` char(50) default NULL,
  `CASE_TYPE` int(1) default NULL COMMENT '0-LOCAL; 1-FOREIGN',
  `DESCRIPTION` varchar(2000) default NULL,
  `STATUS` int(1) default NULL,
  PRIMARY KEY  (`CASE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `cases` */

/*Table structure for table `cash_recoveries` */

DROP TABLE IF EXISTS `cash_recoveries`;

CREATE TABLE `cash_recoveries` (
  `ADMINISTRATION_ID` int(2) NOT NULL auto_increment,
  `ADMINISTRATION_NAME` varchar(25) default NULL,
  `RECOVERED_ASSETS` decimal(9,3) default NULL,
  `FILE_URL` varchar(100) default NULL,
  `STATUS` int(1) default NULL,
  PRIMARY KEY  (`ADMINISTRATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `cash_recoveries` */

insert into `cash_recoveries` (`ADMINISTRATION_ID`,`ADMINISTRATION_NAME`,`RECOVERED_ASSETS`,`FILE_URL`,`STATUS`) values (3,'Pres. Aquino','3.122','files/recoveredAssets/PC_EXPRESS.pdf',1);
insert into `cash_recoveries` (`ADMINISTRATION_ID`,`ADMINISTRATION_NAME`,`RECOVERED_ASSETS`,`FILE_URL`,`STATUS`) values (4,'Pres. Ramos','16.632','files/recoveredAssets/MIT-FlashLite.pdf',1);
insert into `cash_recoveries` (`ADMINISTRATION_ID`,`ADMINISTRATION_NAME`,`RECOVERED_ASSETS`,`FILE_URL`,`STATUS`) values (5,'Pres. Estrada','1.636','files/recoveredAssets/Tutorial_Flash_R1A.pdf',1);
insert into `cash_recoveries` (`ADMINISTRATION_ID`,`ADMINISTRATION_NAME`,`RECOVERED_ASSETS`,`FILE_URL`,`STATUS`) values (6,'Pres. Arroyo','61.528','files/recoveredAssets/tutorial_flash_graphics_r1a.pdf',1);

/*Table structure for table `chairmen` */

DROP TABLE IF EXISTS `chairmen`;

CREATE TABLE `chairmen` (
  `COMM_ID` int(11) NOT NULL auto_increment,
  `COMM_NAME` varchar(100) default NULL,
  `POSITION` char(15) default NULL,
  `STATUS` int(11) default NULL,
  `FROM_TERM` char(15) default NULL,
  `UNTIL_TERM` char(15) default NULL,
  `PHOTO_URL` char(100) default NULL,
  `FILE_URL` char(100) default NULL,
  `FOREWORD` longtext,
  PRIMARY KEY  (`COMM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `chairmen` */

insert into `chairmen` (`COMM_ID`,`COMM_NAME`,`POSITION`,`STATUS`,`FROM_TERM`,`UNTIL_TERM`,`PHOTO_URL`,`FILE_URL`,`FOREWORD`) values (2,'Chairman Camilo L. Sabio',NULL,1,'2008-04-23 16:5','PRESENT','files/chairmen/photos/cls1.gif','files/chairmen/profile/Chairman Camilo L. Sabio.pdf','Welcome to the 22nd year of our existence as an office, created for the sole purpose of retrieving for our people, from a past regime\'s profligacy, what belongs to our people, by right and by law<br />\r\n<br />\r\nWelcome to the web site of the Presidential Commission on Good Government. <br />\r\n<br />\r\nOn behalf of all the dedicated women and men who served before our time, and together with the present staff we are privileged to work with, we invite you to enter our home pages and read the detailed story of what we have done and what we are presently doing. Our work is tedious, voluminous, circuitous and oftentimes, dangerous. But it is a labor we offer for God, country and people, because, the ultimate repository of our retrieval is the comprehensive agrarian reform program - - - in short, the Filipino farmer.<br />\r\n<br />\r\nWe take particular pride in the fact that we are a fundamental logistical backbone of our economy because the billions of pesos that the PCGG has recovered, and continues to recover, go to the National Treasury for the use of agrarian reform.<br />\r\n<br />\r\n<br />\r\nThank you for opening this page, and welcome once again to a cause that is greater than ourselves.');

/*Table structure for table `commissioners` */

DROP TABLE IF EXISTS `commissioners`;

CREATE TABLE `commissioners` (
  `COMM_ID` int(11) NOT NULL auto_increment,
  `COMM_NAME` varchar(100) default NULL,
  `STATUS` int(11) default NULL,
  `FROM_TERM` char(15) default NULL,
  `UNTIL_TERM` char(15) default NULL,
  `PHOTO_URL` char(100) default NULL,
  `FILE_URL` char(100) default NULL,
  PRIMARY KEY  (`COMM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `commissioners` */

insert into `commissioners` (`COMM_ID`,`COMM_NAME`,`STATUS`,`FROM_TERM`,`UNTIL_TERM`,`PHOTO_URL`,`FILE_URL`) values (2,'ATTY. RICARDO M. ABCEDE',1,'2008-04-23 16:5','PRESENT','files/commissioners/photos/Comm Abcede 2 copy.jpg','files/commissioners/profile/CRMAbcederesume.pdf');
insert into `commissioners` (`COMM_ID`,`COMM_NAME`,`STATUS`,`FROM_TERM`,`UNTIL_TERM`,`PHOTO_URL`,`FILE_URL`) values (3,'NARCISO S. NARIO',1,'2008-04-23 16:5','PRESENT','files/commissioners/photos/Comm Nario 2.jpg','files/commissioners/profile/s Resume 2.pdf');
insert into `commissioners` (`COMM_ID`,`COMM_NAME`,`STATUS`,`FROM_TERM`,`UNTIL_TERM`,`PHOTO_URL`,`FILE_URL`) values (4,'TERESO L. JAVIER',1,'2008-04-23 16:5','PRESENT','files/commissioners/photos/Com Javier 2.jpg','files/commissioners/profile/TLJavier BIO-DATA 2.pdf');

/*Table structure for table `commissioners_and_chairmen` */

DROP TABLE IF EXISTS `commissioners_and_chairmen`;

CREATE TABLE `commissioners_and_chairmen` (
  `C_ID` int(10) NOT NULL auto_increment COMMENT 'C Stands for Commissioner or Chairman',
  `C_NAME` varchar(50) default NULL,
  `START_DATE` datetime default NULL,
  `END_DATE` datetime default NULL,
  `STATUS` int(11) default NULL,
  `DUTY_FLAG` int(11) default NULL COMMENT 'This field will be used for the organization management',
  `PHOTO_URL` varchar(100) default NULL,
  `FOREWORD` varchar(250) default NULL,
  PRIMARY KEY  (`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `commissioners_and_chairmen` */

/*Table structure for table `news` */

DROP TABLE IF EXISTS `news`;

CREATE TABLE `news` (
  `ARTICLE_ID` int(11) NOT NULL auto_increment,
  `CATEGORY_ID` int(11) default NULL,
  `ARTICLE_TITLE` longtext,
  `DATE_CREATED` datetime default NULL,
  `STATUS` int(11) default NULL,
  `FILE_URL` char(100) default NULL,
  PRIMARY KEY  (`ARTICLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `news` */

insert into `news` (`ARTICLE_ID`,`CATEGORY_ID`,`ARTICLE_TITLE`,`DATE_CREATED`,`STATUS`,`FILE_URL`) values (1,1,'PRESS STATEMENT OF CHAIRMAN CAMILO L. SABIO<br />\r\nPRESIDENTIAL COMMISSION ON GOOD GOVERNMENT<br />\r\nTUESDAY, FEBRUARY 27, 2007','2008-04-27 21:16:34',1,'files/pressRelease/CLS PressCon 022807.jpg.pdf');
insert into `news` (`ARTICLE_ID`,`CATEGORY_ID`,`ARTICLE_TITLE`,`DATE_CREATED`,`STATUS`,`FILE_URL`) values (2,1,'PRESS STATEMENT OF CHAIRMAN CAMILO L. SABIO<br />\r\nPresidential Commission on Good Government<br />\r\nTuesday, September 18, 2007<br />\r\nLatest Development on the PAYANIG PROPERTY','2008-04-27 21:18:13',1,'files/pressRelease/CLS PressCon 091807.jpg.pdf');
insert into `news` (`ARTICLE_ID`,`CATEGORY_ID`,`ARTICLE_TITLE`,`DATE_CREATED`,`STATUS`,`FILE_URL`) values (3,1,'PRESS STATEMENT OF CHAIRMAN CAMILO L. SABIO<br />\r\nPresidential Commission on Good Government (PCGG)<br />\r\nTuesday, 9 October 2007<br />\r\nRE: THE NEWS REPORT OF MS. CHRISTINA MENDEZ ON PAGE 4 OF THE FRIDAY, OCTOBER 5, 2007 ISSUE OF THE PHILIPPINE STAR','2008-04-27 21:21:01',1,'files/pressRelease/CLS PressCon 100907.jpg.pdf');
insert into `news` (`ARTICLE_ID`,`CATEGORY_ID`,`ARTICLE_TITLE`,`DATE_CREATED`,`STATUS`,`FILE_URL`) values (4,1,'OPENING STATEMENT OF PCGG CHAIRMAN CAMILO L. SABIO DURING THE PUBLIC HEARING ON WEDNESDAY, 13 FEBRUARY 2008 ON SENATE BILLS NOS. 1911, 1990 AND 2047 IN RELATION TO SENATE RESOLUTION NOS. 45, 65 AND 263','2008-04-27 21:26:18',1,'files/pressRelease/CARP statement Senate 21308.pdf');
insert into `news` (`ARTICLE_ID`,`CATEGORY_ID`,`ARTICLE_TITLE`,`DATE_CREATED`,`STATUS`,`FILE_URL`) values (5,1,'PRESS STATEMENT OF<br />\r\nCHAIRMAN CAMILO L. SABIO<br />\r\nPRESIDENTIAL COMMISSION ON GOOD GOVERNMENT (PCGG)<br />\r\nTHURSDAY, 07 FEBRUARY 2008','2008-04-27 21:25:19',1,'files/pressRelease/Press statement Chairman 02072008.pdf');

/*Table structure for table `news_category` */

DROP TABLE IF EXISTS `news_category`;

CREATE TABLE `news_category` (
  `CATEGORY_ID` int(11) NOT NULL auto_increment,
  `ARTICLE_TITLE` varchar(100) default NULL,
  PRIMARY KEY  (`CATEGORY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `news_category` */

insert into `news_category` (`CATEGORY_ID`,`ARTICLE_TITLE`) values (1,'CURRENT EVENTS');
insert into `news_category` (`CATEGORY_ID`,`ARTICLE_TITLE`) values (2,'OTHERS');

/*Table structure for table `news_photos` */

DROP TABLE IF EXISTS `news_photos`;

CREATE TABLE `news_photos` (
  `PHOTO_ID` int(11) NOT NULL auto_increment,
  `ARTICLE_ID` int(11) default NULL,
  `PHOTO_CAPTION` char(50) default NULL,
  `IMAGE_URL` varchar(100) default NULL,
  PRIMARY KEY  (`PHOTO_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `news_photos` */

/*Table structure for table `news_uploads` */

DROP TABLE IF EXISTS `news_uploads`;

CREATE TABLE `news_uploads` (
  `ARTICLE_UPLOAD_ID` int(11) NOT NULL auto_increment,
  `ARTICLE_ID` int(11) default NULL,
  `UPLOAD_PATH` varchar(100) default NULL,
  PRIMARY KEY  (`ARTICLE_UPLOAD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `news_uploads` */

/*Table structure for table `organizational_chart` */

DROP TABLE IF EXISTS `organizational_chart`;

CREATE TABLE `organizational_chart` (
  `ORG_ID` int(11) NOT NULL auto_increment,
  `ORG_TITLE` varchar(200) default NULL,
  `STATUS` int(1) default NULL,
  `DATE_CREATED` datetime default NULL,
  `FILE_URL` char(100) default NULL,
  PRIMARY KEY  (`ORG_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `organizational_chart` */

insert into `organizational_chart` (`ORG_ID`,`ORG_TITLE`,`STATUS`,`DATE_CREATED`,`FILE_URL`) values (15,'2008 Organizational Chart',1,'2008-04-23 17:01:12','files/orgChart/orgChart.jpg');

/*Table structure for table `photo_gallery` */

DROP TABLE IF EXISTS `photo_gallery`;

CREATE TABLE `photo_gallery` (
  `GALLERY_ID` int(11) NOT NULL auto_increment,
  `GALLERY_TITLE` char(20) default NULL,
  `DESCRIPTION` char(50) default NULL,
  `STATUS` int(11) default NULL,
  `PHOTO_THUMBNAIL_URL` varchar(100) default NULL,
  PRIMARY KEY  (`GALLERY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `photo_gallery` */

insert into `photo_gallery` (`GALLERY_ID`,`GALLERY_TITLE`,`DESCRIPTION`,`STATUS`,`PHOTO_THUMBNAIL_URL`) values (1,'PCGG PHOTOS','Collection of PCGG Photos',1,'../thumbnails/logoSmall.jpg');

/*Table structure for table `photos` */

DROP TABLE IF EXISTS `photos`;

CREATE TABLE `photos` (
  `PHOTO_ID` int(11) NOT NULL auto_increment,
  `GALLERY_ID` int(11) default NULL,
  `CAPTION` varchar(50) default NULL,
  `IMAGE_URL` varchar(100) default NULL,
  PRIMARY KEY  (`PHOTO_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `photos` */

insert into `photos` (`PHOTO_ID`,`GALLERY_ID`,`CAPTION`,`IMAGE_URL`) values (1,1,'ITEasy_contract_signing.jpg','../photos/PCGG PHOTOS/ITEasy_contract_signing.jpg');

/*Table structure for table `web_system_users` */

DROP TABLE IF EXISTS `web_system_users`;

CREATE TABLE `web_system_users` (
  `USER_ID` int(11) NOT NULL auto_increment,
  `USERNAME` char(20) default NULL,
  `USERPASSWORD` char(100) default NULL,
  `STATUS` int(2) default NULL COMMENT '1 - ENABLED; 0 - DISABLED',
  `LAST_LOGIN_DATE` datetime default NULL,
  `LOGIN_FLAG` int(1) default NULL,
  PRIMARY KEY  (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `web_system_users` */

insert into `web_system_users` (`USER_ID`,`USERNAME`,`USERPASSWORD`,`STATUS`,`LAST_LOGIN_DATE`,`LOGIN_FLAG`) values (1,'admin','*4ACFE3202A5FF5CF467898FC58AAB1D615029441',1,'0000-00-00 00:00:00',0);
insert into `web_system_users` (`USER_ID`,`USERNAME`,`USERPASSWORD`,`STATUS`,`LAST_LOGIN_DATE`,`LOGIN_FLAG`) values (10,'kwing3','*252232AC629D9F1851E7B1CCAF10BE4F0FFFB964',1,NULL,NULL);
insert into `web_system_users` (`USER_ID`,`USERNAME`,`USERPASSWORD`,`STATUS`,`LAST_LOGIN_DATE`,`LOGIN_FLAG`) values (11,'marlon','*D6E3232C0293785C3C9AA2AB8978C5BDF29BCE9B',1,NULL,NULL);

/* Procedure structure for procedure `webAccomplishments` */

drop procedure if exists `webAccomplishments`;

DELIMITER $$;

CREATE DEFINER=`root`@`localhost` PROCEDURE `webAccomplishments`(
	pMethod CHAR(10),
	pAccomplishID INT(11),
	pAccomplishName VARCHAR(50),
	pDetails VARCHAR(5000),
	pStatus INT(11)
)
BEGIN
	CASE pMethod
	    WHEN "add" THEN
		INSERT INTO accomplishments (
			ACCOMPLISH_NAME,
			DETAILS, STATUS
		)
		VALUES (
			pAccomplishName, pDetails,
			pStatus	
		);
	     WHEN "edit" THEN
		UPDATE accomplishments SET
			ACCOMPLISH_NAME = pAccomplishName,
			DETAILS = pDetails,
			STATUS = pStatus
		WHERE ACCOMPLISH_ID = pAccomplishID;
	END CASE;
END$$

DELIMITER ;$$

/* Procedure structure for procedure `webAuthenticate` */

drop procedure if exists `webAuthenticate`;

DELIMITER $$;

CREATE DEFINER=`root`@`localhost` PROCEDURE `webAuthenticate`(
	vUserName CHAR(20),
	vPassword CHAR(100)
)
BEGIN
/*
	IF (SELECT COUNT(*) FROM web_system_users WHERE USERNAME = vUserName AND USERPASSWORD = PASSWORD(vPassword) AND LOGIN_FLAG = 0) = 1
	THEN
		--UPDATE web_system_users SET
		--LOGIN_FLAG = 1
		--WHERE USERNAME = vUsername;
		
		SELECT  FROM WEB_SYSTEM_USERS
	ELSE
		SELECT 0 AS 'RESULT';
	END IF;
*/
SELECT * FROM web_system_users WHERE USERNAME = vUserName 
AND USERPASSWORD = PASSWORD(vPassword);
END$$

DELIMITER ;$$

/* Procedure structure for procedure `webLegal` */

drop procedure if exists `webLegal`;

DELIMITER $$;

CREATE DEFINER=`root`@`localhost` PROCEDURE `webLegal`(
	pMethod CHAR(10),
	pLinkID INT(11),
	pLinkName CHAR(100),
	pDescription VARCHAR(5000),
	pURL VARCHAR(100),
	pStatus INT(11)
)
BEGIN
	CASE pMethod 
	WHEN "add" THEN /*ADD*/
	     INSERT INTO legal (
		LINK_NAME,
		DESCRIPTION, URL,
		STATUS
		)
	     VALUES (
		pLinkName,
		pDescription, pURL, 
		pStatus
	     );
	WHEN "edit" THEN /*EDIT*/
	     UPDATE legal SET
		LINK_NAME = pLinkName,
		DESCRIPTION = pDescription,
		URL = pURL,
		STATUS = pStatus
	     WHERE LINK_ID = pLinkID;
	END CASE;
END$$

DELIMITER ;$$

/* Procedure structure for procedure `webPressRelease` */

drop procedure if exists `webPressRelease`;

DELIMITER $$;

CREATE DEFINER=`root`@`localhost` PROCEDURE `webPressRelease`(
	pMethod CHAR(10),
	pArticleID INT(11),
	pCategoryID INT(11),
	pArticleTitle VARCHAR(100),
	pNewsContents VARCHAR(3000),
	pStatus INT
)
BEGIN
	CASE pMethod 
	WHEN "add" THEN /*ADD*/
	     INSERT INTO news (
		CATEGORY_ID, ARTICLE_TITLE,
		NEWS_CONTENTS, DATE_CREATED,
		STATUS
		)
	     VALUES (
		pCategoryID, pArticleTitle,
		pNewsContents, NOW(),
		pStatus
	     );
	WHEN "edit" THEN /*EDIT*/
	     UPDATE news SET
		ARTICLE_TITLE = pArticleTitle,
		NEWS_CONTENTS = pNewsContents,
		STATUS = pStatus
	     WHERE ARTICLE_ID = pArticleID;
	END CASE;
END$$

DELIMITER ;$$

SET SQL_MODE=@OLD_SQL_MODE;