DROP TABLE IF EXISTS luporgcodes; CREATE TABLE luporgcodes ( orgcode varchar(50) default NULL, orgid int(10) default NULL); DROP TABLE IF EXISTS luppriority; CREATE TABLE luppriority ( PriorityID int(10), Name varchar(50) default NULL, PRIMARY KEY (PriorityID)); INSERT INTO luppriority (PriorityID,Name) VALUES (1,'Critical'), (2,'High'), (3,'Normal'), (4,'Low'), (5,'Not decided'); DROP TABLE IF EXISTS lupstatus; CREATE TABLE lupstatus ( StatusID int(10) default NULL default 0, Name varchar(50) default NULL, PRIMARY KEY (StatusID) ); INSERT INTO lupstatus (StatusID,Name) VALUES (1,'Open'), (2,'Closed'), (3,'In Test'); DROP TABLE IF EXISTS tblcategory; CREATE TABLE tblcategory ( TEST int(10) default NULL auto_increment, PRIMARY KEY (TEST) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblclient; CREATE TABLE tblclient ( orgid int(10) default NULL auto_increment, Name varchar(50) default NULL, Address1 varchar(50) default NULL, Address2 varchar(50) default NULL, Address3 varchar(50) default NULL, City varchar(50) default NULL, Country varchar(50) default NULL, Email varchar(50) default NULL, Phone varchar(50) default NULL, Fax varchar(50) default NULL, Website varchar(200) default NULL, PRIMARY KEY (orgid) ); DROP TABLE IF EXISTS tblcrs; CREATE TABLE tblcrs ( crid int(10) default NULL, projectid int(10) default NULL, requestdate datetime, requeststaffid int(10) default NULL, priorityid int(10) default NULL, scope tinyint(1) default NULL, description varchar(4000) default NULL, justify varchar(4000) default NULL, impactofnot varchar(4000) default NULL, impactcost varchar(4000) default NULL, impactschedule varchar(4000) default NULL, impactconfig varchar(4000) default NULL, alternatives varchar(4000) default NULL, recommend varchar(4000) default NULL, decisionid int(10) default NULL, decisiondate datetime default NULL, decisionstaffid int(10) default NULL, statusid int(10) default NULL, category varchar(255) default NULL ); DROP TABLE IF EXISTS tblhistory; CREATE TABLE tblhistory ( historyid int(10) default NULL, historytype int(10) default NULL, projectid int(10) default NULL, description varchar(4000) default NULL, date datetime default NULL, link varchar(255) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblissues; CREATE TABLE tblissues ( IssueNo int(10) default NULL auto_increment, ProjectID int(10) default NULL, Status int(10) default NULL, RaisedByID int(10) default NULL, Date_Raised datetime default NULL, Issue_Description longtext, Impact_Activity longtext, Date_Resolved datetime default NULL, Category varchar(50) default NULL, Priorityid int(10) default NULL, Call_No varchar(50) default NULL, UAT_No varchar(50) default NULL, Owner int(10) default NULL, DateDue datetime default NULL, Progress int(10) default NULL, PRIMARY KEY (IssueNo), KEY IssueID (IssueNo), KEY ProjectID (ProjectID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tbllocked; CREATE TABLE tbllocked ( tablename char(30) character set utf8 default NULL, fieldname char(30) character set utf8 default NULL, fieldid smallint(6) default NULL, expiry datetime default NULL, staffid smallint(6) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblorg; CREATE TABLE tblorg ( orgid int(10) default NULL auto_increment, Name varchar(50) default NULL, Address1 varchar(50) default NULL, Address2 varchar(50) default NULL, Address3 varchar(50) default NULL, City varchar(50) default NULL, Country varchar(50) default NULL, Email varchar(50) default NULL, Phone varchar(50) default NULL, Fax varchar(50) default NULL, Website varchar(200) default NULL, PRIMARY KEY (orgid), KEY orgid (orgid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblprojects; CREATE TABLE tblprojects ( ProjectID int(10) default NULL, Name varchar(50) default NULL, Start datetime default NULL, Deadline datetime default NULL, orgid int(10) default NULL, Contact varchar(50) default NULL, budget decimal(19,4) default NULL, PRIMARY KEY (ProjectID), KEY orgid (orgid), KEY ProjectID (ProjectID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblprojectstaff; CREATE TABLE tblprojectstaff ( projectstaffid int(10) default NULL, staffid int(10) default NULL, projectid int(10) default NULL, title varchar(255) default NULL, expense decimal(19,4) default NULL, budget decimal(19,4) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblreleases; CREATE TABLE tblreleases ( ReleaseID int(10) default NULL, ProjectID int(10) default NULL, ReleaseName varchar(50) default NULL, ReleaseDate varchar(50) default NULL, Description longtext default NULL, status int(10) default NULL, staffid int(10) default NULL, progress int(10) default NULL, priorityid int(10) default NULL, crid int(10) default NULL, category varchar(50) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblreleasestatus; CREATE TABLE tblreleasestatus ( name varchar(255) default NULL, description varchar(255) default NULL, statusid int(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('New','New',1), ('Open','Open releases',2), ('Design','Currently in design process',3), ('Develop','Currently in development phase',4), ('In Test','Currently in QA/ Testing environment',5); INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('UAT','Currently in User Acceptance Testing',6), ('Production','Currently in Production',7); DROP TABLE IF EXISTS tblstaff; CREATE TABLE tblstaff ( StaffID int(10) default NULL auto_increment, Initial varchar(50) default NULL, Name varchar(50) default NULL, orgid int(10) default NULL, Phone2 varchar(50) default NULL, Email varchar(50) default NULL, username varchar(50) default NULL, password varchar(50) default NULL, isadmin tinyint(1) default NULL, isglobaladmin tinyint(1) default NULL, First varchar(50) default NULL, Last varchar(50) default NULL, position varchar(50) default NULL, department varchar(50) default NULL, info varchar(4000) default NULL, address1 varchar(50) default NULL, address2 varchar(50) default NULL, address3 varchar(50) default NULL, town varchar(50) default NULL, Ext varchar(50) default NULL, Fax varchar(50) default NULL, inactive tinyint(1) default NULL, Phone varchar(50) default NULL, PRIMARY KEY (StaffID), KEY ProjectID (StaffID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tbltaskresources; CREATE TABLE tbltaskresources ( taskresourceid int(10) default NULL, resourceid int(10) default NULL, projectid int(10) default NULL, taskid int(10) default NULL, hours int(10) default NULL, description varchar(255) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tbltasks; CREATE TABLE tbltasks ( description longtext default NULL, projectid int(10) default NULL, startdate datetime default NULL, enddate datetime default NULL, taskid int(10) default NULL, taskstatus int(10) default NULL, status int(10) default NULL, priorityid int(10) default NULL, category varchar(50) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tbltime; CREATE TABLE tbltime ( timeid int(10) default NULL, projectid int(10) default NULL, issueid int(10) default NULL, start datetime default NULL, end datetime default NULL, worktypeid int(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblFiles; CREATE TABLE tblfiles( filename varchar(500) default NULL, projectid int(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS tblAttach; CREATE TABLE tblAttach( filename varchar(200) default NULL, projectid int(10) default NULL, tablename nchar(20) default NULL, field nchar(20) default NULL, fieldid int(10) default NULL );