基于开源工作流引擎OSWorkflow的业务系统实例——请假审批系统 1

 

<!---->

<!---->

sql:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_CURRENTST__ID__0F975522]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_CURRENTSTEP_PREV] DROP CONSTRAINT FK__OS_CURRENTST__ID__0F975522
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_MEMBER__GROUP__1367E606]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_MEMBERSHIP] DROP CONSTRAINT FK__OS_MEMBER__GROUP__1367E606
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_CURREN__PREVI__0EA330E9]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_CURRENTSTEP_PREV] DROP CONSTRAINT FK__OS_CURREN__PREVI__0EA330E9
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_HISTOR__PREVI__117F9D94]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_HISTORYSTEP_PREV] DROP CONSTRAINT FK__OS_HISTOR__PREVI__117F9D94
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_HISTORYST__ID__1273C1CD]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_HISTORYSTEP_PREV] DROP CONSTRAINT FK__OS_HISTORYST__ID__1273C1CD
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_MEMBER__USERN__145C0A3F]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_MEMBERSHIP] DROP CONSTRAINT FK__OS_MEMBER__USERN__145C0A3F
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_CURREN__ENTRY__0DAF0CB0]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_CURRENTSTEP] DROP CONSTRAINT FK__OS_CURREN__ENTRY__0DAF0CB0
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__OS_HISTOR__ENTRY__108B795B]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OS_HISTORYSTEP] DROP CONSTRAINT FK__OS_HISTOR__ENTRY__108B795B
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_CURRENTSTEP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_CURRENTSTEP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_CURRENTSTEP_PREV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_CURRENTSTEP_PREV]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_GROUP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_GROUP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_HISTORYSTEP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_HISTORYSTEP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_HISTORYSTEP_PREV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_HISTORYSTEP_PREV]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_MEMBERSHIP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_MEMBERSHIP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_PROPERTYENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_PROPERTYENTRY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_STEPIDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_STEPIDS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_USER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_USER]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OS_WFENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OS_WFENTRY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bus_leaveApply]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bus_leaveApply]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bus_leaveApprove]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[bus_leaveApprove]
GO

CREATE TABLE [dbo].[OS_CURRENTSTEP] (
 [ID] [int] NOT NULL ,
 [ENTRY_ID] [int] NULL ,
 [STEP_ID] [smallint] NULL ,
 [ACTION_ID] [smallint] NULL ,
 [OWNER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [START_DATE] [datetime] NULL ,
 [FINISH_DATE] [datetime] NULL ,
 [DUE_DATE] [datetime] NULL ,
 [STATUS] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [CALLER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_CURRENTSTEP_PREV] (
 [ID] [int] NOT NULL ,
 [PREVIOUS_ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_GROUP] (
 [GROUPNAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_HISTORYSTEP] (
 [ID] [int] NOT NULL ,
 [ENTRY_ID] [int] NULL ,
 [STEP_ID] [smallint] NULL ,
 [ACTION_ID] [smallint] NULL ,
 [OWNER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [START_DATE] [datetime] NULL ,
 [FINISH_DATE] [datetime] NULL ,
 [DUE_DATE] [datetime] NULL ,
 [STATUS] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [CALLER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_HISTORYSTEP_PREV] (
 [ID] [int] NOT NULL ,
 [PREVIOUS_ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_MEMBERSHIP] (
 [USERNAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [GROUPNAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_PROPERTYENTRY] (
 [GLOBAL_KEY] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [ITEM_KEY] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [ITEM_TYPE] [smallint] NULL ,
 [STRING_VALUE] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
 [DATE_VALUE] [datetime] NULL ,
 [DATA_VALUE] [varbinary] (2000) NULL ,
 [FLOAT_VALUE] [float] NULL ,
 [NUMBER_VALUE] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_STEPIDS] (
 [ID] [bigint] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_USER] (
 [USERNAME] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [PASSWORDHASH] [text] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OS_WFENTRY] (
 [ID] [int] NOT NULL ,
 [NAME] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
 [STATE] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[bus_leaveApply] (
 [applyID] [int] IDENTITY (1, 1) NOT NULL ,
 [applicant] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [reason] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
 [dayCount] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [applyTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [wfid] [bigint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[bus_leaveApprove] (
 [approveID] [int] IDENTITY (1, 1) NOT NULL ,
 [applyID] [int] NULL ,
 [approver] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [approveTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [opinion] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_CURRENTSTEP] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_CURRENTSTEP_PREV] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID],
  [PREVIOUS_ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_GROUP] ADD
  PRIMARY KEY  CLUSTERED
 (
  [GROUPNAME]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_HISTORYSTEP] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_HISTORYSTEP_PREV] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID],
  [PREVIOUS_ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_MEMBERSHIP] ADD
  PRIMARY KEY  CLUSTERED
 (
  [USERNAME],
  [GROUPNAME]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_PROPERTYENTRY] ADD
  PRIMARY KEY  CLUSTERED
 (
  [GLOBAL_KEY],
  [ITEM_KEY]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_STEPIDS] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_USER] ADD
  PRIMARY KEY  CLUSTERED
 (
  [USERNAME]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_WFENTRY] ADD
  PRIMARY KEY  CLUSTERED
 (
  [ID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[bus_leaveApprove] ADD
 CONSTRAINT [PK_bus_reqApprove] PRIMARY KEY  CLUSTERED
 (
  [approveID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[OS_CURRENTSTEP] ADD
  FOREIGN KEY
 (
  [ENTRY_ID]
 ) REFERENCES [dbo].[OS_WFENTRY] (
  [ID]
 )
GO

ALTER TABLE [dbo].[OS_CURRENTSTEP_PREV] ADD
  FOREIGN KEY
 (
  [PREVIOUS_ID]
 ) REFERENCES [dbo].[OS_HISTORYSTEP] (
  [ID]
 ),
  FOREIGN KEY
 (
  [ID]
 ) REFERENCES [dbo].[OS_CURRENTSTEP] (
  [ID]
 )
GO

ALTER TABLE [dbo].[OS_HISTORYSTEP] ADD
  FOREIGN KEY
 (
  [ENTRY_ID]
 ) REFERENCES [dbo].[OS_WFENTRY] (
  [ID]
 )
GO

ALTER TABLE [dbo].[OS_HISTORYSTEP_PREV] ADD
  FOREIGN KEY
 (
  [PREVIOUS_ID]
 ) REFERENCES [dbo].[OS_HISTORYSTEP] (
  [ID]
 ),
  FOREIGN KEY
 (
  [ID]
 ) REFERENCES [dbo].[OS_HISTORYSTEP] (
  [ID]
 )
GO

ALTER TABLE [dbo].[OS_MEMBERSHIP] ADD
  FOREIGN KEY
 (
  [GROUPNAME]
 ) REFERENCES [dbo].[OS_GROUP] (
  [GROUPNAME]
 ),
  FOREIGN KEY
 (
  [USERNAME]
 ) REFERENCES [dbo].[OS_USER] (
  [USERNAME]
 )
GO

INSERT INTO OS_USER (USERNAME,PASSWORDHASH) VALUES ('employee1','7iaw3Ur350mqGo7jwQrpkj9hiYB3Lkc/iBml1JQODbJ6wYX4oOHV+E+IvIh/1nsUNzLDBMxfqa2Ob1f1ACio/w==')
INSERT INTO OS_USER (USERNAME,PASSWORDHASH) VALUES ('hr1','7iaw3Ur350mqGo7jwQrpkj9hiYB3Lkc/iBml1JQODbJ6wYX4oOHV+E+IvIh/1nsUNzLDBMxfqa2Ob1f1ACio/w==')
INSERT INTO OS_USER (USERNAME,PASSWORDHASH) VALUES ('manager1','7iaw3Ur350mqGo7jwQrpkj9hiYB3Lkc/iBml1JQODbJ6wYX4oOHV+E+IvIh/1nsUNzLDBMxfqa2Ob1f1ACio/w==')

 

INSERT INTO OS_GROUP (GROUPNAME) VALUES ('employee')
INSERT INTO OS_GROUP (GROUPNAME) VALUES ('hr')
INSERT INTO OS_GROUP (GROUPNAME) VALUES ('manager')

INSERT INTO OS_MEMBERSHIP (USERNAME,GROUPNAME) VALUES ('employee1','employee')
INSERT INTO OS_MEMBERSHIP (USERNAME,GROUPNAME) VALUES ('hr1','hr')
INSERT INTO OS_MEMBERSHIP (USERNAME,GROUPNAME) VALUES ('manager1','manager')

xml 代码

<!---->xml version="1.0" encoding="UTF-8"?>

<!---->
<!---->
<!---->
<!---->
<!---->

<!---->

  <!---->
  <!---->
  <!---->
  <!---->
  <!---->

<datasources>

<!---->
<!---->
<!---->
<!---->
<!---->
  <local-tx-datasource>
    <jndi-name>JSQLConnectDB_LeaveApplyjndi-name>
    <connection-url>jdbc:JSQLConnect://localhost:1433/database=osworkflow_appconnection-url>
    <driver-class>com.jnetdirect.jsql.JSQLDriverdriver-class>
    <user-name>sauser-name>
    <password>123456password>
  local-tx-datasource>

datasources>