NCS – News Center System

Installation Guide

 

Index

 

General Requirements for Windows. 3

Java Run Time Environment Installation on Windows. 4

Tomcat Installation on Windows. 9

NCS Installation. 12

Start Up and shutdown Tomcat 17

Running NCS. 19

NCS Advanced Configuration. 20

Base Directory Configuration. 20

Home Directory Configuration. 20

Default Language Selection. 20

Database Selection. 20

JDBC-ODBC database selection and configuration. 20

SQL Server selection and configuration. 21

Oracle Database selection and configuration. 21

Database Setup for News Center System.. 22

Access Database ODBC Datasource setup. 22

SQL Server database setup. 23

Oracle database setup. 29

Appendix A : NCS configuration file - web.xml 30

Appendix B : Oracle Environment Setup Script 36

Appendix B : SQL Server Setup Script 41

 


 

General Requirements for Windows

 

           Server Requirement

                       Pentimum II 333 MHz

                       128 MB or more

                       100 MB free disk

 

           Operating System Requirement

                       Windows XP/2000/NT4.0

                       Windows ME/98/95 (not recommented)

 

           Dependent Software

                       Sun JRE 1.3.1 version or above

                       Apache Tomcat 4.03 or above

 

           Client Browser

                       Internet Expolorer 5.0 or above

                  

Java Run Time Environment Installation on Windows

Download a Java Run time Environment JRE (current version is JRE 1.4.0 ) from following Home page 
    http://java.sun.com/j2se/1.4/download.html
You can also find the same file on the MMS packege CD-ROM. The file name is 
j2re-1_4_0-win-i.exe
Double Click and execute the packege to install JRE on your Windows System.
 

 

 

 

 

 

 

 

  Click Next button to continue installation.

 

 

Click YES button to accept the License agreement and begin the installation. If you not aggree to the License, click the NO button to quit from the installation.

 

 

  Confirm the Install Folder and Click NEXT Button to continue.

    Default Install Folder will be <SysDrive>\ProgramFiles\JavaSoft\JRE1.3.1_02

 

 

    Select Your Browser ( Microsoft Internet Expolorer ) and press NEXT to continue.

 

 

 

 

 

 

 

 

 

After Complete the Installation, setup the JAVA_HOME Evironment Variables,   The setup step shown as below.

1.        Start Menu => Settings => Control Pannel

2.        Double click System Icon to Open the System Property Setup Dialog

 

 

3.        Click Environment Button to Open Environment setup Dialog.

 


 

 

 

 

  On System Environment Region, click the NEW Button to create one JAVA_HOME. The Enviornment Value should same with your Installation Directory

 

 

 

 

NOTE :

1. MMS – News Center System can not run the Java environment under than JRE 1.4.0. For this reason, need to configure you JAVA_HOME correctly on your PC is exteamly important. Otherwise the installation will fail.

                   2. TOMCAT 4.0.3 installation should be after the JRE installation and JAVA_HOME setup.

Tomcat Installation on Windows

    Apache Tomcat Binary code can be downloaded from following URL.
http://jakarta.apache.org/site/binindex.html
NOTE:  As an alternative to downloading a binary distribution, you can create
your own from the Tomcat source repository, as described in "BUILDING.txt".
    ON MMS packege CD-ROM you can find the most recent version of Tomcat V4.03. the file name is 
         jakarta-tomcat-4.0.3.exe
Double Click on the file name and begin installation.
 
 
    Click Ok button to continue to install.
 
 
    Click the I Agree button to continue installation, otherwise will cancel installation.
 
 
On this dialog, add the NT Service (NT/2k/XP only) check box check on if you using Windows 2000/NT/XP. Click next to continue.
 
 
NOTE : Please take care here, do not use the default folder selected by Tomcat. NCS system use the directory <SysDrive>\Tomcat as default directory. If you selected the default directory, no need to extra configuration of the NCS.
 
    Click Close to complete the tomcat installation.
 

 

NCS Installation

    NCS install file can be found on the MMS packege CD-ROM. The file name is 
                 NCS.exe
    Double click on the file and start installation.
 
 
    Click Next Button to continue.(No Install Language Selection currently)
 
 
    Click Next Button to continue.
 
 
    Check the I Agree box to agree the license and click next to continue.
 
 
    This is the simple System Introduction, Click Next to Continue.
 
 
 
 
    NOTE : Here you select the Installation Folder. If your tomcat installation is <SysDrive>\tomcat then here should be <SysDrive>\tomcat\webapps\.
 
    If you selected tomcat directory other than default, please change this default directory to match your selection. 
    Click Next to install. Because your directory already existed after tomcat installation. Now you get following message box.
 
 
    Click Yes to continue.
 

 
 
    Select Full Install and click Next button.
 
 
    Click the Next Button to install.
 
 
    After completion on Install, you can get following screen.
 
 
   Check the NCS Setup check box and click Ok. This will start one DOS prompt window and proceed the post installation process. It will be successfully installed on the server if the package is authorized on this server.
 
 

 

Start Up and shutdown Tomcat

    NCS system depend on tomcat server. When tomcat server is startup, the NCS web application is availale to user.

 
There are 3 ways to start Tomcat on Windows XP/2000/NT 4.0.
 
1.                        Startup From Start Menu.
After completion on Apache Tomcat installation on Windows, there are one shootcut folder on Start Menu. You can startup tomcat selecting following road.
Start Menu -> Program Files -> Apache Tomcat 4.0 -> Start Tomcat
 
 
  Same way we can shutdown tomcat using following path command.
  Start Menu -> Program Files -> Apache Tomcat 4.0 -> Stop Tomcat
 

 
2.                        Startup from command prompt.
Open one dos command prompt and change directory to <SysDrive>\Tomcat\bin.
Execute the startup.bat batch file to startup Tomcat
     CD \Tomcat\bin
     Startup
 
 
  For shutdown tomcat, run batch file shutdown.bat under <SysDrive>\tomcat\bin.
     CD \Tomcat\bin
     shutdown
 
3.                        Startup through Service.
 
After completed the Apache Tomcat installation on Windows XP/2000/NT 4.0, it will register one tomcat service on the system and set it as auto startable on rebooting or starting Operating System.
You startup and shutdown this service on service control pannel.
Start Menu -> Settings -> Control Pannel -> Service
 
After startup, the default web applications included with Tomcat 4.0 will be
available by browsing:
    http://localhost:8080/ or http://jrwin2000:8080/ (server name is jrwin2000)
 

Running NCS

 

Startup your Tomcat Server as introduced above. And using client browser to access following address.

 

http://jrwin2000:8080/NCS/main

from your server you can use

http://localhost:8080/NCS/main

 

You should get NCS welcom page like bellow.

 

 

 


 

NCS Advanced Configuration

NCS system will setup as the default settings at the first installation. If you need to rearrage the directory or Database type, follow the following step to configure the system.

 

NCS configuration file exist under $TOMCAT_HOM\webapps\ NCS\WEB-INF.  The file name is web.xml.

The configuration file details, please refer the NCS configuration file - web.xml

.

Base Directory Configuration

Base directory is for Database and Data files directory. If you need to change the NCS News store Base Directory, you can change the part of the document as bellow.

    <context-param>

      <param-name>BASEDIR</param-name>

      <param-value>c:\tomcat\webapps\NCS\news\</param-value>

    </context-param>

Home Directory Configuration

Note. HomeDir is for finding XHTML files for run time. Normally, no necessary to change the Home Directory to other place than under Tomcat Home. But if your Tomcat Home is not like bellow, please change to fit your directory structure.

    <context-param>

      <param-name>HOMEDIR</param-name>

      <param-value>c:\tomcat\webapps\NCS\</param-value>

    </context-param>

Default Language Selection

NCS can run multi language version. For configuring Default Language, you can change following part of the configuration file.

    <context-param>

      <param-name>LANGUAGE</param-name> <!-- Default language in NCS -->

      <param-value>EN</param-value>

    </context-param>

Database Selection

NCS can select any kind of Database. Database can be connected with NCS using JDBC or JDBC-ODBC. Currently, we provide 3 tested version of Oracle Database JDBC connection configuration, SQL Server JDBC Connection configuration and Access database JDBC-ODBC connection. For selecting the which database, you can update following part of the configuration file.

JDBC-ODBC database selection and configuration

The same way if you selecting Access or other database and using JDBC-ODBC bridge to connect to NCS. You need to change this part.

 

<!-- this is for ODBC definition -->

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>odbc</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:odbc:NCSMDB</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

SQL Server selection and configuration

You can remove the comment of to configure SQL Server Connection definitions here.

<!-- this is for MSSQL definition   

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>mssql</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:microsoft:sqlserver://192.168.0.16:1433</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

-->

Oracle Database selection and configuration

You can remove the comment to configure Oracle Connection definitions here.

<!-- this is for oracle definition

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>oracle</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:oracle:thin:@jrwin2000:1521:jrmt</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

-->

 

Database Setup for News Center System

 

Database Configuration for NCS as bellow. NCS have 6 tables. Departments, Users, News, NewsClasses, Priorities, Principals. The specification of the tables please refer NCS User Manual.

 

Access Database ODBC Datasource setup

NCS Access database is provided with Executable Binary. You can find the NCS.mdb file under $TOMCAT_HOME/NCS. Access database is default database on installation. The default ODBC datasource is configuration is registered in install program. In case you change the location of the file, need to configure the ODBC datasource again. 

 

For Access database ODBC configuration need to be like following screen shots.

Start->Control Pannel -> DataSource->ODBC

 

 

 

On NCS Configuration file web.xml need to configure same name with this datasource define.

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:odbc:NCSMDB</param-value>

    </context-param>

 

SQL Server database setup

 

1.       create SQL server database user NCS password can be NCS.

 

 

 


 

 

if you changed password please don't fogot the config file have password also need to be modify.

 

2.       created a database NCS and permit NCS user to full access .

 

 

 

 

3.       run NCS SQL Server setup scripts ncs_mssql.sql to create NCS necessary dictionary data. Using SQL Query Analyzer to run the scripts, login user should select NCS.

 


 

4.       Install SQL client software on your NCS system server if the SQL server is a different server. The SQL Server Connection component is enough for NCS usage.

 

 

5.       setup ODBC data source to upload NCS.mdb data ( demo data ) into SQL Database if needed.

 

Select SQL Server ODBC Driver.

 


 

 

    Input the DSN Name and Server Name.

   

 

    Select SQL Server authentication using a login ID and password and input login ID as NCS as well as password.

 


 

    Other part select default settings and complete ODBC DSN setup.

 

 

    Click Ok to get the ODBC DSN – NCSSQL in System DSN as bellow.

 

 

 

6.       Modify NCS configuration file NCS\WEB-INF\web.xml to select using mssql database.and its connection URL respectively.

 

 


 

Oracle database setup

Install Oracle Database if you have no Oracle Database running.(Please refer Oracle  installation Guide )

Setup Your Web server Oracle Client NET 8 configuration. You can follow the oracle instruction to complete it. Here I tell you change direct on TNSNAMES.ora file.

 

You can find TNSNAMES.ora file under your $ORACLE_HOME/network/admin and/or $ORACLE_HOME/net80/admin.

You can modify and add the following definition part in the file

NCS =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = JRWIN2000)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ORCL)

    )

  )

 

Please modify HOST name as your server name or IP. Change port as your Database port number. Default installation is 1521. change SERVICE_NAME as the SID of you Oracle Database Instance. Default installation will be ORCL. and save the file. You can use SQL plus conection to check the server connection is working.

 

Run the newscenter.sql using SQL Plus to create NCS schema and the tables as well as the Master data. Newscenter.sql file is provided with Executable binary or Source code.

 

Change the Oracle definition part in NCS web.xml driver name need to be same with this configuration.

<context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:oracle:thin:@jrwin2000:1521:ORCL</param-value>

    </context-param>

Change the user and password part in configuration file.

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

 


 

Appendix A : NCS configuration file - web.xml

<?xml version="1.0" encoding="ISO-8859-1"?>

 

<!DOCTYPE web-app

    PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

    "http://java.sun.com/dtd/web-app_2_3.dtd">

 

<web-app>

 

    <!-- Define application database connection parameters -->

<!-- this is for oracle definition

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>oracle</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:oracle:thin:@jrwin2000:1521:jrmt</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

-->

 

<!-- this is for MSSQL definition   

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>mssql</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:microsoft:sqlserver://192.168.0.16:1433</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

-->

 

 

<!-- this is for ODBC definition -->

    <context-param>

      <param-name>DatabaseTYPE</param-name>

      <param-value>odbc</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseURL</param-name>

      <param-value>jdbc:odbc:NCSMDB</param-value>

    </context-param>

    <context-param>

      <param-name>DatabaseUser</param-name>

      <param-value>ncs</param-value>

    </context-param>

    <context-param>

      <param-name>DatabasePassword</param-name>

      <param-value>ncs</param-value>

    </context-param>

 

    <!-- Define application URL parameters -->

    <!-- These URL should be same with the Servlet url-pattern defined below -->

    <context-param>

      <param-name>ROOT</param-name>

      <param-value>/NCS</param-value>

    </context-param>

    <context-param>

      <param-name>MAIN</param-name>

      <param-value>main</param-value>

    </context-param>

    <context-param>

      <param-name>LOGIN</param-name>

      <param-value>login</param-value>

    </context-param>

    <context-param>

      <param-name>NEWSPUBLISH</param-name>

      <param-value>newspublish</param-value>

    </context-param>

    <context-param>

      <param-name>NEWSQUERY</param-name>

      <param-value>newsquery</param-value>

    </context-param>

    <context-param>

      <param-name>NEWSBROWSE</param-name>

      <param-value>newsbrowse</param-value>

    </context-param>

    <context-param>

      <param-name>NEWSFORM</param-name>

      <param-value>newsform</param-value>

    </context-param>

    <context-param>

      <param-name>NCSPUBLISH</param-name>

      <param-value>ncspublish</param-value>

    </context-param>

    <context-param>

      <param-name>NCSCONFIG</param-name>

      <param-value>ncsconfig</param-value>

    </context-param>

    <context-param>

      <param-name>NCSHELP</param-name>

      <param-value>ncshelp</param-value>

    </context-param>

    <context-param>

      <param-name>NCSSTATISTICS</param-name>

      <param-value>ncsstatistics</param-value>

    </context-param>

 

    <context-param>

      <param-name>EXCEPTIONURL</param-name>

      <param-value>NcsException</param-value>

    </context-param>

 

    <!-- Define application Other parameters -->

    <context-param>

      <param-name>BASEDIR</param-name>

      <param-value>c:\tomcat\webapps\NCS\news\</param-value> <!-- News Database if not exist use default -->

    </context-param>

    <context-param>

      <param-name>HOMEDIR</param-name>

      <param-value>c:\tomcat\webapps\NCS\</param-value>  <!-- Page Home Directory if not exist use default -->

    </context-param>

    <context-param>

      <param-name>LANGUAGE</param-name> <!-- Default language in NCS -->

      <param-value>CN</param-value>

    </context-param>

    <context-param>

      <param-name>FILENUMBER</param-name> <!-- Default file number in news list -->

      <param-value>3</param-value>

    </context-param>

 

    <context-param>

      <param-name>BGCOLOR</param-name>  <!-- background color, the default is #66CCFF -->

      <param-value>#66CCFF</param-value>

    </context-param>

    <context-param>

      <param-name>FORECOLOR</param-name>  <!-- Foreground color, the default is black -->

      <param-value>#000000</param-value>

    </context-param>

 

    <!-- Define servlets that are included in the application -->

 

    <servlet>

        <servlet-name>

            NewsCenterServlet

        </servlet-name>

        <servlet-class>

            NewsCenterServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            UserLoginServlet

        </servlet-name>

        <servlet-class>

            UserLoginServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NewsPublishServlet

        </servlet-name>

        <servlet-class>

            NewsPublishServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NewsQueryServlet

        </servlet-name>

        <servlet-class>

            NewsQueryServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NewsBrowseServlet

        </servlet-name>

        <servlet-class>

            NewsBrowseServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NewsFormServlet

        </servlet-name>

        <servlet-class>

            NewsFormServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NcsPublishServlet

        </servlet-name>

        <servlet-class>

            NcsPublishServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NcsConfigServlet

        </servlet-name>

        <servlet-class>

            NcsConfigServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NcsHelpServlet

        </servlet-name>

        <servlet-class>

            NcsHelpServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NcsExceptionServlet

        </servlet-name>

        <servlet-class>

            NcsExceptionServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

    <servlet>

        <servlet-name>

            NcsStatisticsServlet

        </servlet-name>

        <servlet-class>

            NcsStatisticsServlet

        </servlet-class>

        <run-as>

            <description>Security role for anonymous access</description>

            <role-name>tomcat</role-name>

        </run-as>

    </servlet>

 

    <servlet>

      <servlet-name>

            BaseServlet

      </servlet-name>

      <servlet-class>

            BaseServlet

      </servlet-class>

    </servlet>

 

    <!-- Define Servlet URL mapping -->

    <!-- These URL should be same with the URL parameters defined above -->

    <servlet-mapping>

        <servlet-name>NewsCenterServlet</servlet-name>

        <url-pattern>/main</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>UserLoginServlet</servlet-name>

        <url-pattern>/login</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NewsPublishServlet</servlet-name>

        <url-pattern>/newspublish</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NewsQueryServlet</servlet-name>

        <url-pattern>/newsquery</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NewsBrowseServlet</servlet-name>

        <url-pattern>/newsbrowse</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NewsFormServlet</servlet-name>

        <url-pattern>/newsform</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NcsPublishServlet</servlet-name>

        <url-pattern>/ncspublish</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NcsConfigServlet</servlet-name>

        <url-pattern>/ncsconfig</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NcsHelpServlet</servlet-name>

        <url-pattern>/ncshelp</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NcsStatisticsServlet</servlet-name>

        <url-pattern>/ncsstatistics</url-pattern>

    </servlet-mapping>

    <servlet-mapping>

        <servlet-name>NcsExceptionServlet</servlet-name>

        <url-pattern>/NcsException</url-pattern>

    </servlet-mapping>

 

</web-app>

 


 

Appendix B : Oracle Environment Setup Script

You can find the Oracle Environment setup script under

$TOMCAT_HOME\Webapps\NCS\ directory. The File Name : ncs_oracle.sql

 

GRANT CONNECT, RESOURCE TO NCS IDENTIFIED BY NCS;

ALTER USER NCS DEFAULT TABLESPACE USERS;

ALTER USER NCS TEMPORARY TABLESPACE TEMP;

CONNECT NCS/NCS@ORCL;

 

DROP TABLE DEPARTMENTS;

CREATE TABLE DEPARTMENTS (

DEPARTMENT_ID         NUMBER PRIMARY KEY,

CODE                                                   VARCHAR2(16) NOT NULL,

NAME                                                  VARCHAR2(128) NOT NULL,

LOCATION                      VARCHAR2(32),

ADDRESS                                             VARCHAR2(256),

PHONE_NUMBER          VARCHAR2(32),

MANAGER_ID                NUMBER,

PARENT_ID                    NUMBER,

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

 

DROP TABLE USERS;

CREATE TABLE USERS (

USER_ID                         NUMBER PRIMARY KEY,

EMPLOYEE_NUMBER   VARCHAR2(16)  NOT NULL,

USERNAME                     VARCHAR2(16)  NOT NULL,

PASSWORD                     VARCHAR2(16)  NOT NULL,

FIRSTNAME                    VARCHAR2(32),

LASTNAME                    VARCHAR2(32),

E_MAIL                                               VARCHAR2(64),

DEPARTMENT_ID        NUMBER,

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

 

DROP TABLE NEWSCLASSES;

CREATE TABLE NEWSCLASSES (

CLASS_ID                        NUMBER PRIMARY KEY,

NEWSCLASS                 VARCHAR2(64) NOT NULL,

DESCRIPTION                VARCHAR2(128),

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

DROP TABLE PRIORITIES;

CREATE TABLE PRIORITIES (

PRIORITY_ID                 NUMBER PRIMARY KEY,

PRIORITY                     VARCHAR2(8) NOT NULL,

DESCRIPTION                VARCHAR2(128),

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

DROP TABLE PRINCIPALS;

CREATE TABLE PRINCIPALS (

PRINCIPAL_ID               NUMBER PRIMARY KEY,

PRINCIPAL                  VARCHAR2(64) NOT NULL,

DESCRIPTION                VARCHAR2(128),

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

 

 

DROP TABLE NEWS;

CREATE TABLE NEWS(

NEWS_ID                       NUMBER     PRIMARY KEY,

NEWSTITLE                 VARCHAR2(160),

NEWSCLASS                 VARCHAR2(32),

PRIORITY                      VARCHAR2(8),

RELEASEDATE               DATE,

DEPARTMENT_ID   NUMBER,

REPORTER                      VARCHAR2(32),

PRINCIPAL                     VARCHAR2(64),

KEYWORDS                    VARCHAR2(255),

FILENAME                      VARCHAR2(128),

OBJPATH                                             VARCHAR2(125),

OBJECTS                                              VARCHAR2(255),

HYPERLINK                   VARCHAR(128),

STATUS                                                VARCHAR2(8),

CREATED_BY                 NUMBER,

CREATE_DATE              DATE,

UPDATED_BY                NUMBER,

UPDATE_DATE              DATE

);

 

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, '5', 'TOP', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, '4', 'HIGH', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, '3', 'MEDIUM', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, '2', 'NORMAL', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, '1', 'LOW', 0, SYSDATE, 0, SYSDATE );      

 

 

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, 'CLASS A', 'CLASS A', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, 'CLASS B', 'CLASS B', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, 'CLASS C', 'CLASS C', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, 'CLASS D', 'CLASS D', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, 'CLASS E', 'CLASS E', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (5, 'CLASS F', 'CLASS F', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (6, 'CLASS G', 'CLASS G', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (7, 'CLASS H', 'CLASS H', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (8, 'CLASS I', 'CLASS I', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (9, 'CLASS J', 'CLASS J', 0, SYSDATE, 0, SYSDATE );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (10, 'CLASS K', 'CLASS K', 0, SYSDATE, 0, SYSDATE );

 

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, 'PRINCIPAL ONE', 'PRINCIPAL ONE', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, 'PRINCIPAL TWO', 'PRINCIPAL TWO', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, 'PRINCIPAL THERE', 'PRINCIPAL THERE', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, 'PRINCIPAL FOUR', 'PRINCIPAL FOUR', 0, SYSDATE, 0, SYSDATE );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, 'PRINCIPAL FIVE', 'PRINCIPAL FIVE', 0, SYSDATE, 0, SYSDATE );      

 

 

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (0, '00000000', 'TOP ORGANIZATION', null, null, null, null, -1, 0, sysdate, 0, sysdate );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (1, 'A0010000', 'GROUP A', null, null, null, 1, 0, 0, sysdate, 0, sysdate );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (2, 'B0010000', 'GROUP B', null, null, null, 2, 0, 0, sysdate, 0, sysdate );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (3, 'C0010000', 'GROUP C', null, null, null, 3, 0, 0, sysdate, 0, sysdate );     

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (4, 'A0010100', 'GROUP A DEPARTMENT I', NULL, NULL, NULL, 4, 1, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (5, 'A0010200', 'GROUP A DEPARTMENT II', NULL, NULL, NULL, 5, 1, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (6, 'A0010101', 'GROUP A DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 6, 4, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (7, 'A0010102', 'GROUP A DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 7, 4, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (8, 'A0010101', 'GROUP A DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 8, 5, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (9, 'B0010100', 'GROUP B DEPARTMENT I', NULL, NULL, NULL, 9, 2, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (10, 'B0010200', 'GROUP B DEPARTMENT II', NULL, NULL, NULL, 10, 2, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (11, 'B0010101', 'GROUP B DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 11, 9, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (12, 'B0010102', 'GROUP B DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 12, 9, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (13, 'B0010101', 'GROUP B DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 13, 10, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (14, 'C0010100', 'GROUP C DEPARTMENT I', NULL, NULL, NULL, 14, 3, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (15, 'C0010200', 'GROUP C DEPARTMENT II', NULL, NULL, NULL, 15, 3, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (16, 'C0010101', 'GROUP C DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 16, 14, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (17, 'C0010102', 'GROUP C DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 17, 14, 0, SYSDATE, 0, SYSDATE );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (18, 'C0010101', 'GROUP C DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 18, 15, 0, SYSDATE, 0, SYSDATE );

 

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (0, '00000000', 'ncsadmin', 'hello', 'admin', 'ncs', 'ncs.admin@ncs.com', 0, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (1, '00000001', 'jirimuto', 'hello', 'muto', 'jiri', 'muto.jiri@ncs.com', 1, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (2, '00000002', 'chen', 'hello', 'haiting', 'chen', 'haiting.chen@ncs.com', 2, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (3, '00000003', 'weibin', 'hello', 'bin', 'wei', 'bin.wei@ncs.com', 3, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (4, '00000004', 'user04', 'hello', 'user04', 'user04', 'user@ncs.com', 4, 0, sysdate, 0, sysdate );

 

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (5, '00000005', 'user05', 'hello', 'user05', 'user05', 'user@ncs.com', 5, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (6, '00000006', 'user06', 'hello', 'user06', 'user06', 'user@ncs.com', 6, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (7, '00000007', 'user07', 'hello', 'user07', 'user07', 'user@ncs.com', 7, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (8, '00000008', 'user08', 'hello', 'user08', 'user08', 'user@ncs.com', 8, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (9, '00000009', 'user09', 'hello', 'user09', 'user09', 'user@ncs.com', 9, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (10, '00000010', 'user10', 'hello', 'user10', 'user10', 'user@ncs.com', 10, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (11, '00000011', 'user11', 'hello', 'user11', 'user11', 'user@ncs.com', 11, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (12, '00000012', 'user12', 'hello', 'user12', 'user12', 'user@ncs.com', 12, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (13, '00000013', 'user13', 'hello', 'user13', 'user13', 'user@ncs.com', 13, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (14, '00000014', 'user14', 'hello', 'user14', 'user14', 'user@ncs.com', 14, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (15, '00000015', 'user15', 'hello', 'user15', 'user15', 'user@ncs.com', 15, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (16, '00000016', 'user16', 'hello', 'user16', 'user16', 'user@ncs.com', 16, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (17, '00000017', 'user17', 'hello', 'user17', 'user17', 'user@ncs.com', 17, 0, sysdate, 0, sysdate );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, created_by, create_date, updated_by, update_date )

       values (18, '00000018', 'user18', 'hello', 'user18', 'user18', 'user@ncs.com', 18, 0, sysdate, 0, sysdate );

 


 

Appendix B : SQL Server Setup Script

You can find the Oracle Environment setup script under

$TOMCAT_HOME\Webapps\NCS\ directory. The File Name : ncs_mssql.sql

 

/* 1. create SQL server database user NCS password can be NCS.

        if you changed password please don't fogot the config file have password also need to modify.

    2. created a database NCS and select NCS user as owner.

    3. run following scripts to create NCS necessary dictionary data.

    4. Install SQL client software on your NCS system server if the SQL server is a different server.

    5. setup ODBC data source to upload NCS.mdb data if needed.

    6. modify NCS configuration file NCS\WEB-INF\web.xml to select using mssql database.and connection URL

*/

 

DROP TABLE DEPARTMENTS;

CREATE TABLE DEPARTMENTS (

DEPARTMENT_ID         INT PRIMARY KEY,

CODE                                                   NVARCHAR(16) NOT NULL,

NAME                                                  NVARCHAR(128) NOT NULL,

LOCATION                      NVARCHAR(32),

ADDRESS                                             NVARCHAR(256),

PHONE_NUMBER          NVARCHAR(32),

MANAGER_ID                INT,

PARENT_ID                    INT,

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

DROP TABLE USERS;

CREATE TABLE USERS (

USER_ID                         INT PRIMARY KEY,

EMPLOYEE_NUMBER   NVARCHAR(16)  NOT NULL,

USERNAME                     NVARCHAR(16)  NOT NULL,

PASSWORD                     NVARCHAR(16)  NOT NULL,

FIRSTNAME                    NVARCHAR(32),

LASTNAME                    NVARCHAR(32),

E_MAIL                                               NVARCHAR(64),

DEPARTMENT_ID        INT,

PRIVILEGE                      VARCHAR(20),

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

DROP TABLE NEWSCLASSES;

CREATE TABLE NEWSCLASSES (

CLASS_ID                        INT PRIMARY KEY,

NEWSCLASS                 NVARCHAR(64) NOT NULL,

DESCRIPTION                NVARCHAR(128),

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

DROP TABLE PRIORITIES;

CREATE TABLE PRIORITIES (

PRIORITY_ID                 INT PRIMARY KEY,

PRIORITY                     NVARCHAR(8) NOT NULL,

DESCRIPTION                NVARCHAR(128),

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

DROP TABLE PRINCIPALS;

CREATE TABLE PRINCIPALS (

PRINCIPAL_ID               INT PRIMARY KEY,

PRINCIPAL                  NVARCHAR(64) NOT NULL,

DESCRIPTION                NVARCHAR(128),

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

DROP TABLE NEWS;

CREATE TABLE NEWS(

NEWS_ID                       INT              PRIMARY KEY,

NEWSTITLE                 NVARCHAR(160),

NEWSCLASS                 NVARCHAR(32),

PRIORITY                      NVARCHAR(8),

RELEASEDATE               DATETIME,

DEPARTMENT_ID   INT,

REPORTER                      NVARCHAR(32),

PRINCIPAL                     NVARCHAR(64),

KEYWORDS                    NVARCHAR(255),

FILENAME                      NVARCHAR(128),

OBJPATH                                             NVARCHAR(125),

OBJECTS                                              NVARCHAR(255),

HYPERLINK                   NVARCHAR(128),

STATUS                                                NVARCHAR(8),

CREATED_BY                 INT,

CREATE_DATE              DATETIME,

UPDATED_BY                INT,

UPDATE_DATE              DATETIME

);

 

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, '5', 'TOP', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, '4', 'HIGH', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, '3', 'MEDIUM', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, '2', 'NORMAL', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRIORITIES (PRIORITY_ID, PRIORITY, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, '1', 'LOW', 0, GETDATE(), 0, GETDATE() );      

 

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, 'CLASS A', 'CLASS A', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, 'CLASS B', 'CLASS B', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, 'CLASS C', 'CLASS C', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, 'CLASS D', 'CLASS D', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, 'CLASS E', 'CLASS E', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (5, 'CLASS F', 'CLASS F', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (6, 'CLASS G', 'CLASS G', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (7, 'CLASS H', 'CLASS H', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (8, 'CLASS I', 'CLASS I', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (9, 'CLASS J', 'CLASS J', 0, GETDATE(), 0, GETDATE() );

INSERT INTO NEWSCLASSES (CLASS_ID, NEWSCLASS, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (10, 'CLASS K', 'CLASS K', 0, GETDATE(), 0, GETDATE() );

 

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (0, 'PRINCIPAL ONE', 'PRINCIPAL ONE', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (1, 'PRINCIPAL TWO', 'PRINCIPAL TWO', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (2, 'PRINCIPAL THERE', 'PRINCIPAL THERE', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (3, 'PRINCIPAL FOUR', 'PRINCIPAL FOUR', 0, GETDATE(), 0, GETDATE() );      

INSERT INTO PRINCIPALS (PRINCIPAL_ID, PRINCIPAL, DESCRIPTION, CREATED_BY, CREATE_DATE, UPDATED_BY, UPDATE_DATE )

       VALUES (4, 'PRINCIPAL FIVE', 'PRINCIPAL FIVE', 0, GETDATE(), 0, GETDATE() );      

 

 

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (0, '00000000', 'TOP ORGANIZATION', null, null, null, null, -1, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (1, 'A0010000', 'GROUP A', null, null, null, 1, 0, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (2, 'B0010000', 'GROUP B', null, null, null, 2, 0, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (3, 'C0010000', 'GROUP C', null, null, null, 3, 0, 0, GETDATE(), 0, GETDATE() );     

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (4, 'A0010100', 'GROUP A DEPARTMENT I', NULL, NULL, NULL, 4, 1, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (5, 'A0010200', 'GROUP A DEPARTMENT II', NULL, NULL, NULL, 5, 1, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (6, 'A0010101', 'GROUP A DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 6, 4, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (7, 'A0010102', 'GROUP A DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 7, 4, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (8, 'A0010101', 'GROUP A DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 8, 5, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (9, 'B0010100', 'GROUP B DEPARTMENT I', NULL, NULL, NULL, 9, 2, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (10, 'B0010200', 'GROUP B DEPARTMENT II', NULL, NULL, NULL, 10, 2, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (11, 'B0010101', 'GROUP B DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 11, 9, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (12, 'B0010102', 'GROUP B DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 12, 9, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (13, 'B0010101', 'GROUP B DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 13, 10, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (14, 'C0010100', 'GROUP C DEPARTMENT I', NULL, NULL, NULL, 14, 3, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (15, 'C0010200', 'GROUP C DEPARTMENT II', NULL, NULL, NULL, 15, 3, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (16, 'C0010101', 'GROUP C DEPARTMENT I SECTION ONE', NULL, NULL, NULL, 16, 14, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (17, 'C0010102', 'GROUP C DEPARTMENT I SECTION TWO', NULL, NULL, NULL, 17, 14, 0, GETDATE(), 0, GETDATE() );

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,CODE,NAME,LOCATION,ADDRESS,PHONE_NUMBER,MANAGER_ID,PARENT_ID,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)

       VALUES (18, 'C0010101', 'GROUP C DEPARTMENT II SECTION ONE', NULL, NULL, NULL, 18, 15, 0, GETDATE(), 0, GETDATE() );

 

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (0, '00000000', 'ncsadmin', 'hello', 'admin', 'ncs', 'ncs.admin@ncs.com', 0, 'Administrator', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (1, '00000001', 'jirimuto', 'hello', 'muto', 'jiri', 'muto.jiri@ncs.com', 1, 'Administrator', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (2, '00000002', 'chen', 'hello', 'haiting', 'chen', 'haiting.chen@ncs.com', 2, 'Administrator', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (3, '00000003', 'weibin', 'hello', 'bin', 'wei', 'bin.wei@ncs.com', 3, 'Administrator', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (4, '00000004', 'user04', 'hello', 'user04', 'user04', 'user@ncs.com', 4, 'User', 0, GETDATE(), 0, GETDATE() );

 

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (5, '00000005', 'user05', 'hello', 'user05', 'user05', 'user@ncs.com', 5, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (6, '00000006', 'user06', 'hello', 'user06', 'user06', 'user@ncs.com', 6, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (7, '00000007', 'user07', 'hello', 'user07', 'user07', 'user@ncs.com', 7, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (8, '00000008', 'user08', 'hello', 'user08', 'user08', 'user@ncs.com', 8, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (9, '00000009', 'user09', 'hello', 'user09', 'user09', 'user@ncs.com', 9, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (10, '00000010', 'user10', 'hello', 'user10', 'user10', 'user@ncs.com', 10, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (11, '00000011', 'user11', 'hello', 'user11', 'user11', 'user@ncs.com', 11, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (12, '00000012', 'user12', 'hello', 'user12', 'user12', 'user@ncs.com', 12, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (13, '00000013', 'user13', 'hello', 'user13', 'user13', 'user@ncs.com', 13, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (14, '00000014', 'user14', 'hello', 'user14', 'user14', 'user@ncs.com', 14, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (15, '00000015', 'user15', 'hello', 'user15', 'user15', 'user@ncs.com', 15, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (16, '00000016', 'user16', 'hello', 'user16', 'user16', 'user@ncs.com', 16, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (17, '00000017', 'user17', 'hello', 'user17', 'user17', 'user@ncs.com', 17, 'User', 0, GETDATE(), 0, GETDATE() );

insert into users (user_id, employee_number, username, password, firstname, lastname, e_mail, department_id, privilege, created_by, create_date, updated_by, update_date )

       values (18, '00000018', 'user18', 'hello', 'user18', 'user18', 'user@ncs.com', 18, 'User', 0, GETDATE(), 0, GETDATE() );