erp脚本.txt

一个ERP系统可以用来学习之用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[WuliaoRc] (
	[Rcid] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RcNo] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Gxdaihao] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Wldaihao] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[RcShuliang] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Price] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Jine] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[CFdidian] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[RcMan] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Rcdate] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Business] (
	[BusinessID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BusinessDate] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Client] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[ClientID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Linkman] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[BusinessType] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Amount] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Operator] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[ClientType] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Businessman] (
	[BusinessmanID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BusinessmanName] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Country] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Province] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[City] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Linkman] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Telephone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Fax] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Buyer] (
	[BuyerID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BuyerName] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Department] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CO] (
	[COID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CODate] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[COItemID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[SalesItemID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[COQty] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[ConsignmentDate] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[IsComplete] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeliverID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CheckIn] (
	[CheckinMonth] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Emp_Id] [int] NULL ,
	[DaysOfMonth] [int] NULL ,
	[qqDays] [decimal](4, 1) NULL ,
	[bjDays] [decimal](4, 1) NULL ,
	[sjDays] [decimal](4, 1) NULL ,
	[kgDays] [decimal](4, 1) NULL ,
	[fdxjDays] [decimal](4, 1) NULL ,
	[nxjDays] [decimal](4, 1) NULL ,
	[dxjDays] [decimal](4, 1) NULL ,
	[cxDays] [decimal](4, 1) NULL ,
	[cdDays] [decimal](4, 1) NULL ,
	[ztDays] [decimal](4, 1) NULL ,
	[ot1Days] [decimal](4, 1) NULL ,
	[ot2Days] [decimal](4, 1) NULL ,
	[ot3Days] [decimal](4, 1) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child] (
	[ParentItem] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[CT] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[ChildItem] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Qty] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[QT] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Scrap] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Offset] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Remark] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customer] (
	[CustomerID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CustomerName] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Country] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Province] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[City] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Linkman] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Telephone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Fax] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Deliver] (
	[CustomerID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[DeliverID] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Country] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Province] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[City] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Linkman] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Telephone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Fax] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department] (
	[Dep_Id] [int] NOT NULL ,
	[Dep_Name] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Describe] [char] (300) COLLATE Chinese_PRC_CI_AS NULL ,
	[UpperId] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees] (
	[Emp_Id] [int] NOT NULL ,
	[Emp_NAME] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Photo] [image] NULL ,
	[Sex] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Nationality] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Birth] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Political_Party] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Culture_Level] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Marital_Condition] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Family_Place] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Id_Card] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[PcID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[BadgeID] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Office_phone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Home_phone] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Bp] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Files_Keep_Org] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Residence] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Hukou] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Postcode] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Police_Post] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[Street_Office] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,