-- สร้างฐานศิษย์เก่า
-- CREATE DATABASE `registealumni` ;

-- 
-- โครงสร้างตาราง `AlumniBio`
-- 

CREATE TABLE `AlumniBio` (
  `alumniId` int(11) NOT NULL default '0',
  `studentId` int(10) NOT NULL default '0',
  `nationId` int(2) default NULL,
  `religionId` int(1) default NULL,
  `bloodGroup` varchar(6) NOT NULL,
  `birthDate` date NOT NULL default '0000-00-00',
  `birthCntId` int(4) NOT NULL,
  `birthProvinceId` int(2) default NULL,
  `homeAddress` varchar(100) NOT NULL,
  `homeDistrictId` int(2) default NULL,
  `homeAmphurId` int(2) default NULL,
  `homeProvinceId` int(2) default NULL,
  `homeZipcode` varchar(5) NOT NULL,
  `homePhoneNo` varchar(10) NOT NULL,
  `officeName` varchar(100) NOT NULL,
  `workCntId` int(4) NOT NULL default '0',
  `officeAddress` varchar(100) NOT NULL,
  `officeDistrictId` int(2) default NULL,
  `officeAmphurId` int(2) default NULL,
  `officeProvinceId` int(2) default NULL,
  `officeZipcode` varchar(5) NOT NULL,
  `officePhoneNo` varchar(10) NOT NULL,
  `workingStatus` char(1) NOT NULL,
  `workingPosition` varchar(100) NOT NULL,
  `workingSalary` float NOT NULL default '0',
  `fatherName` varchar(32) NOT NULL,
  `fatherAddress` varchar(100) NOT NULL,
  `fatherDistrictId` int(2) default NULL,
  `fatherAmphurId` int(2) default NULL,
  `fatherProvinceId` int(2) default NULL,
  `fatherZipcode` varchar(5) NOT NULL,
  `fatherPhoneNo` varchar(10) NOT NULL,
  `fatherOccupation` varchar(100) NOT NULL,
  `fatherStatus` int(1) NOT NULL default '0',
  `motherName` varchar(32) NOT NULL,
  `motherAddress` varchar(100) NOT NULL,
  `motherDistrictId` int(2) default NULL,
  `motherAmphurId` int(2) default NULL,
  `motherProvinceId` int(2) default NULL,
  `motherZipcode` varchar(5) NOT NULL,
  `motherPhoneNo` varchar(10) NOT NULL,
  `motherOccupation` varchar(100) NOT NULL,
  `motherStatus` int(1) NOT NULL default '0',
  `studentSex` char(1) NOT NULL,
  `parentName` varchar(32) NOT NULL,
  `parentRelation` varchar(16) NOT NULL,
  `parentAddress` varchar(100) NOT NULL,
  `parentDistrictId` int(2) default NULL,
  `parentAmphurId` int(2) default NULL,
  `parentProvinceId` int(2) default NULL,
  `parentZipcode` varchar(5) NOT NULL,
  `parentPhoneNo` varchar(10) NOT NULL,
  `parentMobile` varchar(10) default NULL,
  `parentOcc` varchar(50) NOT NULL,
  `parentIncome` float NOT NULL default '0',
  `parentEmail` varchar(32) NOT NULL,
  `contactPerson` varchar(32) NOT NULL,
  `contactAddress` varchar(100) NOT NULL,
  `contactDistrictId` int(2) default NULL,
  `contactAmphurId` int(2) default NULL,
  `contactProvinceId` int(2) default NULL,
  `contactZipcode` varchar(5) NOT NULL,
  `contactPhoneNo` varchar(10) NOT NULL,
  `cardExpiryDate` date NOT NULL default '0000-00-00',
  `currentCntId` int(4) NOT NULL default '0',
  `currentAddress` varchar(100) NOT NULL,
  `currentDistrictId` int(2) default NULL,
  `currentAmphurId` int(2) default NULL,
  `currentProvinceId` int(2) default NULL,
  `currentZipcode` varchar(5) NOT NULL,
  `currentPhoneNo` varchar(10) NOT NULL,
  `graduateAddress` varchar(100) NOT NULL,
  `graduateDistrictId` int(2) default NULL,
  `graduateAmphurId` int(2) default NULL,
  `graduateProvinceId` int(2) default NULL,
  `graduateZipcode` varchar(5) NOT NULL,
  `graduatePhoneNo` varchar(10) NOT NULL,
  `maritalStatusId` int(1) default NULL,
  `weight` float NOT NULL default '0',
  `height` float NOT NULL default '0',
  `picturePath` varchar(100) NOT NULL default 'photo.jpg',
  `recruitmentTypeId` int(1) default NULL,
  `occExamResult` varchar(10) NOT NULL,
  `canRefund` char(1) NOT NULL default 'N',
  `oldStudentName` varchar(32) default NULL,
  `entryDegree` int(11) default NULL,
  `healthPrivId` int(11) default NULL,
  `o1prefixId` int(3) NOT NULL,
  `o1fName` varchar(50) NOT NULL,
  `o1lName` varchar(50) NOT NULL,
  `o2prefixId` int(3) NOT NULL,
  `o2fName` varchar(50) NOT NULL,
  `o2lName` varchar(50) NOT NULL,
  `regPrefix` int(4) default NULL,
  `regStdName` varchar(100) default NULL,
  `regStdSurname` varchar(100) default NULL,
  `regStdNameE` varchar(100) default NULL,
  `regStdSurnameE` varchar(100) default NULL,
  PRIMARY KEY  (`alumniId`),
  KEY `nationId` (`nationId`),
  KEY `religionId` (`religionId`),
  KEY `birthProvinceId` (`birthProvinceId`),
  KEY `homeDistrictId` (`homeDistrictId`),
  KEY `homeAmphurId` (`homeAmphurId`),
  KEY `homeProvinceId` (`homeProvinceId`),
  KEY `officeDistrictId` (`officeDistrictId`),
  KEY `officeAmphurId` (`officeAmphurId`),
  KEY `officeProvinceId` (`officeProvinceId`),
  KEY `fatherDistrictId` (`fatherDistrictId`),
  KEY `fatherAmphurId` (`fatherAmphurId`),
  KEY `fatherProvinceId` (`fatherProvinceId`),
  KEY `motherDistrictId` (`motherDistrictId`),
  KEY `motherAmphurId` (`motherAmphurId`),
  KEY `motherProvinceId` (`motherProvinceId`),
  KEY `parentDistrictId` (`parentDistrictId`),
  KEY `parentAmphurId` (`parentAmphurId`),
  KEY `parentProvinceId` (`parentProvinceId`),
  KEY `contactDistrictId` (`contactDistrictId`),
  KEY `contactAmphurId` (`contactAmphurId`),
  KEY `contactProvinceId` (`contactProvinceId`),
  KEY `currentDistrictId` (`currentDistrictId`),
  KEY `currentAmphurId` (`currentAmphurId`),
  KEY `currentProvinceId` (`currentProvinceId`),
  KEY `graduateDistrictId` (`graduateDistrictId`),
  KEY `graduateAmphurId` (`graduateAmphurId`),
  KEY `graduateProvinceId` (`graduateProvinceId`),
  KEY `maritalStatusId` (`maritalStatusId`),
  KEY `recruitmentTypeId` (`recruitmentTypeId`),
  KEY `entryDegree` (`entryDegree`),
  KEY `healthPrivId` (`healthPrivId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `AlumniMain`
-- 

CREATE TABLE `AlumniMain` (
  `alumniId` int(11) NOT NULL auto_increment,
  `studentId` int(12) NOT NULL default '0',
  `studentCode` varchar(10) default NULL,
  `levelId` int(2) default NULL,
  `programId` int(12) default NULL,
  `prefixId` int(2) NOT NULL default '0',
  `studentName` varchar(32) NOT NULL,
  `studentSurname` varchar(32) NOT NULL,
  `studentNameEng` varchar(32) NOT NULL,
  `studentSurnameEng` varchar(32) NOT NULL,
  `creditAttempt` float NOT NULL default '0',
  `creditSatisfy` float NOT NULL default '0',
  `GPA` float NOT NULL default '0',
  `admitAcadYear` int(4) NOT NULL default '0',
  `admitSemester` int(1) NOT NULL default '0',
  `admitDate` date NOT NULL default '0000-00-00',
  `finishDate` date NOT NULL default '0000-00-00',
  `studentPassword` varchar(16) NOT NULL,
  `studentEmail` varchar(32) NOT NULL,
  `studentYear` int(1) NOT NULL default '0',
  `studentStatus` int(2) NOT NULL default '0',
  `officerId1` int(12) default NULL,
  `officerId2` int(12) default NULL,
  `financeStatus` int(2) NOT NULL default '0',
  `updateUserId` varchar(20) NOT NULL,
  `updateDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `citizenId` varchar(13) default NULL,
  `graduateYear` int(4) NOT NULL default '0',
  `genStatus` char(1) NOT NULL default 'N',
  `genNo` int(4) NOT NULL default '0',
  `entryTypeId` int(2) NOT NULL default '0',
  `entryTypeId2` int(2) NOT NULL default '0',
  `studentSex` char(1) NOT NULL,
  `scholarId` int(11) NOT NULL default '0',
  `preAdmitPositionId` int(11) default NULL,
  `totalPoint` int(11) NOT NULL default '100',
  `honor` char(1) default NULL,
  `medal` char(1) default NULL,
  `exitExam` char(1) NOT NULL default 'N',
  `studentStatusTmp` int(2) NOT NULL default '1',
  `importtoalumni` char(2) NOT NULL,
  `glantId` varchar(5) NOT NULL,
  `glantDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `glantreqDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `amAnsQn` varchar(1) NOT NULL,
  `amProgramAlumni` int(4) default '0',
  PRIMARY KEY  (`alumniId`),
  -- UNIQUE KEY `studentCode` (`studentCode`),
  KEY `levelId` (`levelId`),
  KEY `programId` (`programId`),
  KEY `prefixId` (`prefixId`),
  KEY `admitSemester` (`admitSemester`),
  KEY `studentYear` (`studentYear`),
  KEY `studentStatus` (`studentStatus`),
  KEY `officerId1` (`officerId1`),
  KEY `officerId2` (`officerId2`),
  KEY `financeStatus` (`financeStatus`),
  KEY `genNo` (`genNo`),
  KEY `entryTypeId` (`entryTypeId`),
  KEY `entryTypeId2` (`entryTypeId2`),
  KEY `scholarId` (`scholarId`),
  KEY `preAdmitPositionId` (`preAdmitPositionId`),
  KEY `studentStatusTmp` (`studentStatusTmp`),
  KEY `amProgramAlumni` (`amProgramAlumni`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `AlumniQn`
-- 

CREATE TABLE `AlumniQn` (
  `qnId` int(11) NOT NULL,
  `alumniId` int(11) NOT NULL,
  `checkwork` varchar(2) NOT NULL,
  `checkedu` varchar(2) NOT NULL,
  `startDateWork` date NOT NULL,
  `workPosition` varchar(50) NOT NULL,
  `workSalary` int(10) NOT NULL,
  `leveleduId` int(3) NOT NULL,
  `majoreduId` int(11) NOT NULL,
  `educationId` int(11) NOT NULL,
  `investiture` varchar(2) NOT NULL,
  `updateByPerson` varchar(2) NOT NULL,
  `writeDate` date NOT NULL,
  PRIMARY KEY  (`qnId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `ea_AlumniQn`
-- 

CREATE TABLE `ea_AlumniQn` (
  `aqQnId` int(11) NOT NULL auto_increment,
  `aqAlumniId` int(11) NOT NULL,
  `aqStateQn` int(1) NOT NULL,
  `aqWorkStatus` varchar(1) NOT NULL,
  `aqWorkCompanyT` varchar(100) NOT NULL,
  `aqWorkCompanyE` varchar(100) NOT NULL,
  `aqWorkPosition` varchar(100) NOT NULL,
  `aqWorkAddress` varchar(100) NOT NULL,
  `aqWorkDtId` int(11) default NULL,
  `aqWorkApId` int(11) default NULL,
  `aqWorkPrvId` int(11) default NULL,
  `aqWorkZipcode` varchar(5) default NULL,
  `aqWorkTelephone` varchar(10) default NULL,
  `aqWorkSalary` float NOT NULL,
  `aqWorkSalarySpe` float NOT NULL,
  `aqWorkStartDate` date NOT NULL default '0000-00-00',
  `aqDegree` varchar(100) NOT NULL,
  `aqLevelId` int(11) default NULL,
  `aqMajId` int(11) default NULL,
  `aqEduId` int(11) default NULL,
  `aqEduStartDate` date NOT NULL default '0000-00-00',
  `aqInvestiture` varchar(1) NOT NULL,
  `aqUserId` varchar(10) NOT NULL,
  `aqUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`aqQnId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `ea_BaseSalary`
-- 

CREATE TABLE `ea_BaseSalary` (
  `bsId` int(4) NOT NULL auto_increment,
  `bsEdgId` int(4) NOT NULL,
  `bsLevelId` int(4) NOT NULL,
  `bsStartUse` date NOT NULL,
  `bsFinishUse` date NOT NULL,
  `bsAmount` float NOT NULL,
  PRIMARY KEY  (`bsId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `ea_CurriculumConfig`
-- 

CREATE TABLE `ea_CurriculumConfig` (
  `ccId` int(11) NOT NULL auto_increment,
  `ccCode` char(2) NOT NULL,
  `ccName` varchar(100) NOT NULL default '',
  `ccStatus` char(1) NOT NULL default 'N',
  PRIMARY KEY  (`ccId`),
  UNIQUE KEY `ccCode` (`ccCode`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `ea_CurriculumDetails`
-- 

CREATE TABLE `ea_CurriculumDetails` (
  `curId` int(4) NOT NULL,
  `curImproveY` int(4) NOT NULL,
  `curName` varchar(100) NOT NULL,
  `curNameE` varchar(100) NOT NULL,
  `curAbbr` varchar(100) NOT NULL,
  `curAbbrE` varchar(100) NOT NULL,
  `curStatus` char(1) NOT NULL,
  `curCcId` int(4) NOT NULL,
  `ccCode` char(2) NOT NULL,
  `ccName` varchar(100) NOT NULL,
  `curEdgId` int(4) NOT NULL,
  `edgName` varchar(100) NOT NULL,
  `edgNameE` varchar(100) NOT NULL,
  `edgAbbr` varchar(50) NOT NULL,
  `edgAbbrE` varchar(50) NOT NULL,
  `curElvId` int(4) NOT NULL,
  `levelName` varchar(100) NOT NULL,
  `levelNameEng` varchar(100) NOT NULL,
  `levelAbbr` varchar(20) NOT NULL,
  `levelAbbrEng` varchar(20) NOT NULL,
  `curAddType` varchar(1) NOT NULL,
  PRIMARY KEY  (`curId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `ea_EduDegree`
-- 

CREATE TABLE `ea_EduDegree` (
  `edgId` int(2) NOT NULL auto_increment,
  `edgName` varchar(100) NOT NULL default '',
  `edgNameE` varchar(100) NOT NULL default '',
  `edgAbbr` varchar(50) NOT NULL default '',
  `edgAbbrE` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`edgId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `ea_Generation`
-- 

CREATE TABLE `ea_Generation` (
  `genId` int(4) NOT NULL,
  `genAcY` int(4) NOT NULL,
  `genNo` int(11) NOT NULL,
  `genCurId` int(11) NOT NULL,
  `genTmId` int(1) NOT NULL,
  PRIMARY KEY  (`genId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `ea_HisEducation`
-- 

CREATE TABLE `ea_HisEducation` (
  `hedSeqId` int(4) NOT NULL,
  `hedAlumniId` int(11) NOT NULL,
  `hedDegree` varchar(200) NOT NULL,
  `hedLevelId` int(11) NOT NULL,
  `hedEduId` int(11) NOT NULL,
  `hedMajId` int(11) NOT NULL,
  `hedStartDate` date NOT NULL,
  `hedFinishDate` date NOT NULL,
  `hedUserId` varchar(10) NOT NULL,
  `hedUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`hedSeqId`,`hedAlumniId`),
  KEY `hedLevelId` (`hedLevelId`),
  KEY `hedEduId` (`hedEduId`),
  KEY `hedMajId` (`hedMajId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `Education`
-- 

CREATE TABLE `Education` (
  `educationId` int(4) NOT NULL auto_increment,
  `educationName` varchar(100) NOT NULL,
  `pbriId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`educationId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `HisAward`
-- 

CREATE TABLE `HisAward` (
  `seqId` int(11) NOT NULL,
  `alumniId` int(11) NOT NULL,
  `awardNameT` varchar(200) NOT NULL,
  `awardNameE` varchar(200) NOT NULL,
  `awardCompany` varchar(200) NOT NULL,
  `typeAwardId` int(4) NOT NULL default '0',
  `levelAwardId` int(4) NOT NULL default '0',
  `receiveDate` date NOT NULL default '0000-00-00',
  `haUserId` varchar(10) NOT NULL,
  `haUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`alumniId`,`seqId`),
  KEY `typeAwardId` (`typeAwardId`),
  KEY `levelAwardId` (`levelAwardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `HisWork`
-- 

CREATE TABLE `HisWork` (
  `seqId` int(11) NOT NULL default '0',
  `alumniId` int(11) NOT NULL default '0',
  `companyNameT` varchar(200) NOT NULL,
  `companyNameE` varchar(200) NOT NULL,
  `companyAddr` varchar(200) NOT NULL,
  `companyPosition` varchar(200) NOT NULL,
  `companyAdmidPosYear` int(4) default NULL,
  `companyStartDate` date NOT NULL default '0000-00-00',
  `companyEndDate` date NOT NULL default '0000-00-00',
  `hwUserId` varchar(10) NOT NULL,
  `hwUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`alumniId`,`seqId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

-- 
-- โครงสร้างตาราง `ImportUpdate`
-- 

CREATE TABLE `ImportUpdate` (
  `importId` int(11) NOT NULL auto_increment,
  `graduateY` int(4) NOT NULL,
  `importDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `usLogin` varchar(20) NOT NULL,
  PRIMARY KEY  (`importId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `LevelAward`
-- 

CREATE TABLE `LevelAward` (
  `levelAwardId` int(4) NOT NULL auto_increment,
  `levelAwardNameE` varchar(100) NOT NULL,
  `levelAwardNameT` varchar(100) NOT NULL,
  PRIMARY KEY  (`levelAwardId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `LevelEdu`
-- 

CREATE TABLE `LevelEdu` (
  `leveleduId` int(4) NOT NULL auto_increment,
  `leveleduName` varchar(100) NOT NULL,
  `leveleduNameEng` varchar(100) NOT NULL,
  `leveleduAbbr` varchar(20) NOT NULL,
  `leveleduAbbrEng` varchar(20) NOT NULL,
  `pbriId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`leveleduId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `MajorEdu`
-- 

CREATE TABLE `MajorEdu` (
  `majoreduId` int(4) NOT NULL auto_increment,
  `majoreduName` varchar(70) NOT NULL,
  `pbriId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`majoreduId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `MapProgram`
-- 

CREATE TABLE `MapProgram` (
  `mapId` int(11) NOT NULL auto_increment,
  `mpGraduateY` int(4) NOT NULL,
  `programId_reg` int(11) NOT NULL,
  `programId_alumni` int(11) NOT NULL,
  PRIMARY KEY  (`mapId`),
  KEY `programId_reg` (`programId_reg`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `ProgramAlumni`
-- 

CREATE TABLE `ProgramAlumni` (
  `programalumniId` int(11) NOT NULL auto_increment,
  `programalumniName` varchar(100) NOT NULL,
  PRIMARY KEY  (`programalumniId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `TimeQn`
-- 

CREATE TABLE `TimeQn` (
  `seqId` int(11) NOT NULL auto_increment,
  `graduateY` int(4) NOT NULL,
  `startDate` date NOT NULL default '0000-00-00',
  `endDate` date NOT NULL default '0000-00-00',
  `tqUserId` varchar(10) NOT NULL,
  `tqUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`seqId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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

-- 
-- โครงสร้างตาราง `TypeAward`
-- 

CREATE TABLE `TypeAward` (
  `typeAwardId` int(4) NOT NULL auto_increment,
  `typeAwardNameE` varchar(100) NOT NULL,
  `typeAwardNameT` varchar(100) NOT NULL,
  PRIMARY KEY  (`typeAwardId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;