CREATE TABLE luporgcodes ( orgcode varchar(50) default NULL, orgid int default NULL) GO CREATE TABLE luppriority ( PriorityID int, Name varchar(50) default NULL, PRIMARY KEY (PriorityID)) GO INSERT INTO luppriority (PriorityID,Name) VALUES (1,'Critical') GO INSERT INTO luppriority (PriorityID,Name) VALUES (2,'High') GO INSERT INTO luppriority (PriorityID,Name) VALUES (3,'Normal') GO INSERT INTO luppriority (PriorityID,Name) VALUES (4,'Low') GO INSERT INTO luppriority (PriorityID,Name) VALUES (5,'Not decided') GO CREATE TABLE lupstatus ( StatusID int NOT NULL default 0, Name varchar(50) default NULL, PRIMARY KEY (StatusID) ) GO INSERT INTO lupstatus (StatusID,Name) VALUES (1,'Open') GO INSERT INTO lupstatus (StatusID,Name) VALUES (2,'Closed') GO INSERT INTO lupstatus (StatusID,Name) VALUES (3,'In Test') GO CREATE TABLE tblcategory ( TEST int NOT NULL, PRIMARY KEY (TEST) ) GO CREATE TABLE tblclient ( orgid int NOT NULL, 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) ) GO CREATE TABLE tblcrs ( crid int default NULL, projectid int default NULL, requestdate datetime, requeststaffid int default NULL, priorityid int default NULL, scope bit default NULL, description varchar(255) default NULL, justify varchar(255) default NULL, impactofnot varchar(255) default NULL, impactcost varchar(255) default NULL, impactschedule varchar(255) default NULL, impactconfig varchar(255) default NULL, alternatives varchar(255) default NULL, recommend varchar(255) default NULL, decisionid int default NULL, decisiondate datetime default NULL, decisionstaffid int default NULL, statusid int default NULL, category varchar(255) default NULL ) GO CREATE TABLE tblissues ( IssueNo int NOT NULL, ProjectID int default NULL, Status int default NULL, RaisedByID int default NULL, Date_Raised datetime default NULL, Issue_Description text, Impact_Activity text, Date_Resolved datetime default NULL, Category varchar(50) default NULL, Priorityid int default NULL, Call_No varchar(50) default NULL, UAT_No varchar(50) default NULL, Owner int default NULL, DateDue datetime default NULL, Progress int default NULL, PRIMARY KEY (IssueNo) ) GO CREATE TABLE tbllocked ( tablename varchar(30) default NULL, fieldname varchar(30) default NULL, fieldid smallint default NULL, expiry datetime default NULL, staffid smallint default NULL ) GO CREATE TABLE tblorg ( orgid int NOT NULL, 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 ) GO CREATE TABLE tblprojectstaff ( projectstaffid int default NULL, staffid int NOT NULL, projectid int NOT NULL, title varchar(255) NOT NULL, expense decimal(19,4) NOT NULL, budget decimal(19,4) NOT NULL ) GO CREATE TABLE tblreleases ( ReleaseID int default NULL, ProjectID int default NULL, ReleaseName varchar(50) default NULL, ReleaseDate varchar(50) default NULL, Description varchar(50) default NULL, status int default NULL, staffid int default NULL, progress int default NULL, priorityid int default NULL, crid int default NULL, category varchar(50) default NULL ) GO CREATE TABLE tblreleasestatus ( name varchar(255) default NULL, description varchar(255) default NULL, statusid int default NULL ) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('New','New',1) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Open','Open releases',2) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Design','Currently in design process',3) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Develop','Currently in development phase',4) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('In Test','Currently in QA/ Testing environment',5) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('UAT','Currently in User Acceptance Testing',6) GO INSERT INTO tblreleasestatus (name,description,statusid) VALUES ('Production','Currently in Production',7) GO CREATE TABLE tblstaff ( StaffID int NOT NULL, Initial varchar(50) default NULL, Name varchar(50) default NULL, orgid int 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) NOT NULL, isglobaladmin tinyint(1) NOT NULL, First varchar(50) default NULL, Last varchar(50) default NULL, position varchar(50) default NULL, department varchar(50) default NULL, info varchar(200) 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) NOT NULL, Phone varchar(50) default NULL, PRIMARY KEY (StaffID) ) GO CREATE TABLE tbltaskresources ( taskresourceid int default NULL, resourceid int default NULL, projectid int default NULL, taskid int default NULL, hours int default NULL, description varchar(255) NOT NULL ) GO CREATE TABLE tbltasks ( description varchar(255) NOT NULL, projectid int NOT NULL, startdate datetime default NULL, enddate datetime default NULL, taskid int NOT NULL, taskstatus int default NULL, status int default NULL, priorityid int default NULL, category varchar(50) default NULL ) GO CREATE TABLE tbltime ( timeid int default NULL, projectid int default NULL, issueid int default NULL, start datetime default NULL, end datetime default NULL, worktypeid int default NULL ) GO CREATE TABLE tblfiles( filename varchar(500) NOT NULL, projectid int default NULL ) GO CREATE TABLE tblAttach( filename varchar(200) default NULL, projectid int default NULL, tablename nchar(20) default NULL, field nchar(20) default NULL, fieldid int default NULL ) GO CREATE TABLE tblhistory ( historyid int default NULL, historytype int default NULL, projectid int NOT NULL, description varchar(255) NOT NULL, date datetime NOT NULL, link varchar(255) NOT NULL ) GO CREATE TABLE tblprojects ( ProjectID int NOT NULL, Name varchar(50) default NULL, Start datetime default NULL, Deadline datetime default NULL, orgid int default NULL, Contact varchar(50) default NULL, budget decimal(19,4) NOT NULL, PRIMARY KEY (ProjectID) ) GO