 

USE [db9pm2c]
GO
/****** Object:  Table [dbo].[MaterialCategory]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MaterialCategory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PriceUnit]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PriceUnit](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[PriceType] [int] NOT NULL,
	[UseCount] [int] NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Material]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Material](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[CategoryId] [int] NOT NULL,
	[CostPrice] [numeric](18, 4) NOT NULL,
	[SailPrice] [numeric](18, 4) NOT NULL,
	[PriceUnitId] [int] NOT NULL,
	[ForStock] [bit] NOT NULL,
	[ForOrder] [bit] NOT NULL,
	[StockMaterialId] [int] NULL,
	[SafetyStock] [decimal](18, 2) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Memo] [nvarchar](500) NULL,
	[FactoryPrice] [numeric](18, 4) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Stock]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stock](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MaterialId] [int] NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[InStockTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompanyConfiguration]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyConfiguration](
	[SettingName] [nvarchar](50) NOT NULL,
	[SettingValue] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_CompanyConfiguration] PRIMARY KEY CLUSTERED 
(
	[SettingName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockOut]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockOut](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SN] [nvarchar](50) NOT NULL,
	[Type] [int] NOT NULL,
	[Status] [int] NOT NULL,
	[Memo] [nvarchar](250) NULL,
	[Operator] [nvarchar](150) NOT NULL,
	[StockOutTime] [datetime] NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockOutLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockOutLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StockOutId] [int] NOT NULL,
	[LogMessage] [nvarchar](250) NOT NULL,
	[Operator] [nvarchar](50) NOT NULL,
	[LogTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Supplier]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Contact] [nvarchar](150) NULL,
	[Phone] [nvarchar](150) NULL,
	[Mobile] [nvarchar](150) NULL,
	[Email] [nvarchar](150) NULL,
	[Other] [nvarchar](250) NULL,
	[Memo] [nvarchar](max) NULL,
	[IsActive] [bit] NOT NULL,
	[IsSystemReserved] [bit] NOT NULL,
	[Bank] [nvarchar](150) NULL,
	[BankAccount] [nvarchar](150) NULL,
	[BankAccountName] [nvarchar](150) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockIn]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockIn](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SN] [nvarchar](50) NOT NULL,
	[SupplierId] [int] NOT NULL,
	[Type] [int] NOT NULL,
	[Status] [int] NOT NULL,
	[PaymentStatus] [smallint] NOT NULL,
	[Memo] [nvarchar](250) NULL,
	[Operator] [nvarchar](50) NOT NULL,
	[StockInTime] [datetime] NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[MarketAmount] [numeric](18, 2) NOT NULL,
	[Discount] [numeric](18, 2) NULL,
	[Paid] [numeric](18, 2) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockInPayment]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockInPayment](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StockInId] [int] NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[PaymentType] [nvarchar](50) NOT NULL,
	[Memo] [nvarchar](200) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockInLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockInLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StockInId] [int] NOT NULL,
	[LogMessage] [nvarchar](250) NOT NULL,
	[Operator] [nvarchar](50) NOT NULL,
	[LogTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PaymentType]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PaymentType](
	[PaymentTypeNo] [int] IDENTITY(1,1) NOT NULL,
	[TypeName] [nvarchar](50) NOT NULL,
	[IsSystemReversed] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[PaymentTypeNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderTemplate]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderTemplate](
	[TemplateNo] [int] IDENTITY(1,1) NOT NULL,
	[Template] [nvarchar](max) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[Creator] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[TemplateNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderStatus]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderStatus](
	[StatusNo] [int] IDENTITY(1,1) NOT NULL,
	[StatusName] [nvarchar](50) NULL,
	[IsSystemReversed] [bit] NOT NULL,
	[Icon] [nvarchar](50) NULL,
	[IsBegin] [bit] NOT NULL,
	[IsEnd] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[StatusNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryTemplate]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryTemplate](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Template] [nvarchar](max) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[Creator] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryPaymentType]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryPaymentType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TypeName] [nvarchar](50) NOT NULL,
	[IsSystemReserved] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[AccountTitle]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AccountTitle](
	[AccountTitleNo] [int] IDENTITY(1,1) NOT NULL,
	[AccountTitleName] [nvarchar](150) NOT NULL,
	[AccountTitleType] [varchar](1) NOT NULL,
	[Memo] [nvarchar](1000) NULL,
	[IsActive] [bit] NOT NULL,
	[AutoMemo] [nvarchar](max) NULL,
	[Creator] [nvarchar](max) NOT NULL,
	[Createtime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[AccountTitleNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Department]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [nvarchar](250) NOT NULL,
	[ParentDepartmentId] [int] NULL,
	[IsRoot] [bit] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Memo] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
	[CustomerNo] [int] IDENTITY(1,1) NOT NULL,
	[CustomerFullName] [nvarchar](100) NOT NULL,
	[CustomerShortName] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](200) NULL,
	[IsOrganization] [bit] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[SearchKey] [nvarchar](50) NULL,
	[CreatedDate] [datetime] NOT NULL,
	[Address] [nvarchar](500) NULL,
	[Phone] [nvarchar](150) NULL,
	[LoginId] [nvarchar](50) NOT NULL,
	[CanLogin] [bit] NOT NULL,
	[Password] [nvarchar](50) NULL,
	[Balance] [decimal](18, 2) NOT NULL,
	[UseBalanceFirst] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[CustomerNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Configuration]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Configuration](
	[SettingName] [nvarchar](50) NOT NULL,
	[SettingValue] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Configuration] PRIMARY KEY CLUSTERED 
(
	[SettingName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompanySalarySetting]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanySalarySetting](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SettingType] [int] NOT NULL,
	[SettingValue] [nvarchar](1000) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Factory]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Factory](
	[FactoryNo] [int] IDENTITY(1,1) NOT NULL,
	[FactoryName] [nvarchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[BankAcount] [nvarchar](200) NULL,
	[Description] [nvarchar](200) NULL,
	[Contact] [nvarchar](200) NULL,
	[Phone] [nvarchar](50) NULL,
	[Mobile] [nvarchar](50) NULL,
	[Email] [nvarchar](150) NULL,
	[QQ] [nvarchar](50) NULL,
	[Other] [nvarchar](200) NULL,
	[SearchKey] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[FactoryNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SystemSetting]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SystemSetting](
	[SettingName] [nvarchar](50) NOT NULL,
	[SettingValue1] [nvarchar](250) NULL,
	[SettingValue2] [nvarchar](250) NULL,
	[SettingValue3] [nvarchar](250) NULL,
	[SettingValue4] [nvarchar](250) NULL,
	[SettingValue5] [nvarchar](250) NULL,
	[SettingValue6] [nvarchar](250) NULL,
	[SettingValue7] [nvarchar](250) NULL,
	[SettingValue8] [nvarchar](250) NULL,
	[SettingValue9] [nvarchar](250) NULL,
	[SettingValue10] [nvarchar](250) NULL,
	[Id] [int] IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RoleGroup]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoleGroup](
	[RoleGroupNo] [nvarchar](50) NOT NULL,
	[RoleGroupName] [nvarchar](50) NULL,
 CONSTRAINT [PK_RoleGroup] PRIMARY KEY CLUSTERED 
(
	[RoleGroupNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WorkFlow]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkFlow](
	[WorkFlowNo] [int] IDENTITY(1,1) NOT NULL,
	[WorkFlowName] [nvarchar](50) NOT NULL,
	[WorkFlowDescription] [nvarchar](200) NULL,
	[IsActive] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[WorkFlowNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WorkFlowStep]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkFlowStep](
	[WorkFlowStepNo] [int] IDENTITY(1,1) NOT NULL,
	[WorkFlowStepName] [nvarchar](50) NOT NULL,
	[WorkFlowNo] [int] NOT NULL,
	[CanMarkComplete] [bit] NOT NULL,
	[FollowupSteps] [nvarchar](200) NULL,
	[StatusNo] [int] NOT NULL,
	[DepartmentNos] [nvarchar](2000) NULL,
	[UserIds] [nvarchar](2000) NULL,
	[SN] [nvarchar](50) NOT NULL,
	[IsSystemReserved] [bit] NOT NULL,
	[IsBeginPoint] [bit] NOT NULL,
	[IsEndPoint] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[WorkFlowStepNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockOutDetail]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockOutDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StockOutId] [int] NOT NULL,
	[MaterialId] [int] NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StockInDetail]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockInDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StockInId] [int] NOT NULL,
	[MaterialId] [int] NOT NULL,
	[Price] [numeric](18, 4) NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompanyAccount]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyAccount](
	[CompanyAccountNo] [int] IDENTITY(1,1) NOT NULL,
	[AccountTitleNo] [int] NOT NULL,
	[TransactionDate] [datetime] NOT NULL,
	[Amount] [decimal](18, 2) NOT NULL,
	[Memo] [nvarchar](1000) NULL,
	[IsActive] [bit] NOT NULL,
	[AutoMemo] [nvarchar](max) NULL,
	[Creator] [nvarchar](max) NOT NULL,
	[Createtime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[CompanyAccountNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [nvarchar](50) NOT NULL,
	[EmployeeNo] [nvarchar](50) NULL,
	[DepartmentId] [int] NOT NULL,
	[Position] [nvarchar](50) NULL,
	[Birthday] [datetime] NULL,
	[Gender] [bit] NULL,
	[IdCardNo] [nvarchar](50) NULL,
	[OnboardDate] [datetime] NULL,
	[EmployeeType] [int] NOT NULL,
	[EmployeeStatus] [int] NOT NULL,
	[PositiveDate] [datetime] NULL,
	[LeaveDate] [datetime] NULL,
	[OfficePhone] [nvarchar](50) NULL,
	[Mobile] [nvarchar](50) NULL,
	[Email] [nvarchar](150) NULL,
	[Qualifications] [int] NOT NULL,
	[Country] [int] NOT NULL,
	[Nationality] [int] NOT NULL,
	[PoliticsStatus] [int] NOT NULL,
	[MaritalStatus] [int] NOT NULL,
	[EmergencyContact] [nvarchar](50) NULL,
	[EmergencyRelation] [int] NOT NULL,
	[EmergencyPhone] [nvarchar](50) NULL,
	[FamilyAddress] [nvarchar](250) NULL,
	[HuKou] [nvarchar](150) NULL,
	[Memo] [nvarchar](500) NULL,
	[Face] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Permission]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Permission](
	[PermissionNo] [nvarchar](50) NOT NULL,
	[RoleGroupNo] [nvarchar](50) NULL,
	[PermissionName] [nvarchar](50) NOT NULL,
	[IsDefault] [int] NOT NULL,
 CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED 
(
	[PermissionNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CustomerDeposit]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerDeposit](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CustomerNo] [int] NOT NULL,
	[RealAmount] [decimal](18, 2) NOT NULL,
	[FreeAmount] [decimal](18, 2) NOT NULL,
	[Memo] [nvarchar](250) NULL,
	[LogMessage] [nvarchar](250) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Contact]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contact](
	[ContactNo] [int] IDENTITY(1,1) NOT NULL,
	[ContactName] [nvarchar](50) NOT NULL,
	[Phone] [nvarchar](50) NULL,
	[Mobile] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[IsActive] [bit] NOT NULL,
	[CustomerNo] [int] NOT NULL,
	[SearchKey] [nvarchar](50) NULL,
	[QQ] [nvarchar](50) NULL,
	[Other] [nvarchar](200) NULL,
	[IsMain] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ContactNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CustomerMaterial]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerMaterial](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CustomerNo] [int] NOT NULL,
	[MaterialId] [int] NOT NULL,
	[SailPrice] [numeric](18, 4) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryMaterial]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryMaterial](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FactoryNo] [int] NOT NULL,
	[MaterialId] [int] NOT NULL,
	[SailPrice] [numeric](18, 4) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeSalary]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalary](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[MonthSn] [nvarchar](10) NOT NULL,
	[AdjustName] [nvarchar](1000) NULL,
	[AdustAmount] [decimal](18, 2) NOT NULL,
	[SalaryStatus] [int] NOT NULL,
	[PerformanceAmount] [decimal](18, 2) NOT NULL,
	[IncomeTax] [decimal](18, 2) NOT NULL,
	[TotalCost] [decimal](18, 2) NOT NULL,
	[NetPay] [decimal](18, 2) NOT NULL,
	[BaseSalary] [decimal](18, 2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LogType] [int] NOT NULL,
	[LogMessage] [nvarchar](250) NOT NULL,
	[Operator] [nvarchar](50) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[EmployeeId] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeSalarySetting]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalarySetting](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[SettingType] [int] NOT NULL,
	[SettingName] [nvarchar](150) NULL,
	[SettingValue] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[User]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[LoginId] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[Password] [nvarchar](50) NULL,
	[IsActive] [bit] NOT NULL,
	[IsAdmin] [bit] NOT NULL,
	[Permissions] [nvarchar](500) NULL,
	[LastLoginTime] [datetime] NULL,
	[AutoGenerateUserId] [bit] NOT NULL,
	[AutoLoginGUID] [nvarchar](50) NULL,
	[AutoLoginLastDate] [datetime] NULL,
	[Token] [nvarchar](250) NULL,
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [int] NOT NULL,
	[GUID] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ToDo]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ToDo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](250) NOT NULL,
	[Memo] [nvarchar](max) NULL,
	[UserId] [int] NOT NULL,
	[Status] [int] NOT NULL,
	[ExecuteDate] [datetime] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_ToDo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeSalaryInsurance]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalaryInsurance](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeSalaryId] [int] NOT NULL,
	[InsuranceType] [int] NOT NULL,
	[InsuranceAmountP] [decimal](18, 2) NOT NULL,
	[InsuranceAmountC] [decimal](18, 2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeSalaryDeduct]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalaryDeduct](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeSalaryId] [int] NOT NULL,
	[DeductName] [nvarchar](250) NOT NULL,
	[DeductAmount] [decimal](18, 2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EmployeeSalaryAllowance]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeSalaryAllowance](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeSalaryId] [int] NOT NULL,
	[AllowanceName] [nvarchar](250) NOT NULL,
	[AllowanceAmount] [decimal](18, 2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[DataInitial]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DataInitial]		 
	@CompanyName nvarchar(150),
	@UserName nvarchar(50),		 	 
	@Mobile nvarchar(150)	
AS
BEGIN
		
begin tran aa
declare @UserId int 
insert into CompanyConfiguration(SettingName,SettingValue)
values 
	('CompanyNameOnReport',@CompanyName),
	('PhoneFaxOnReport',@Mobile) 
	
insert into CompanyConfiguration(SettingName,SettingValue)
	select SettingValue1,SettingValue2 from SystemSetting where SettingName='CompanyConfiguration'

insert into PriceUnit
	select SettingValue1,SettingValue2,SettingValue3,SettingValue4 from SystemSetting where SettingName='PriceUnit'
 
insert into RoleGroup
	select SettingValue1,SettingValue2 from SystemSetting where SettingName='RoleGroup'
insert into Permission 
	select SettingValue1,SettingValue2,SettingValue3,SettingValue4 from SystemSetting where SettingName='Permission'

insert into Department values(@CompanyName,null,1,1,'')	
	

insert into CompanySalarySetting
	select SettingValue1,SettingValue2 from SystemSetting where SettingName='CompanySalarySetting'
 	

INSERT INTO Customer(CustomerFullName,CustomerShortName,[Description],IsOrganization,IsActive,SearchKey,
CreatedDate,[Address],Phone,LoginId,CanLogin,[Password],UseBalanceFirst,Balance)
     VALUES('散客','散客',null,0,1,'SK',GETDATE(),null,null,'1012',0,null,0,0)

-- Payment type
insert into PaymentType(TypeName,IsSystemReversed)
	select SettingValue1,SettingValue2 from SystemSetting where SettingName='PaymentType'
			
-- Order status
declare @workFlowNo int
declare @status1 int, @status2 int, @status3 int,@status4 int,@status5 int
declare @step1 int, @step2 int, @step3 int,@step4 int,@step5 int

insert into OrderStatus(StatusName,IsSystemReversed,Icon,IsBegin,IsEnd)
	values	('已开单',1,'2.png',1,0)
set @status1=SCOPE_IDENTITY()
insert into OrderStatus(StatusName,IsSystemReversed,Icon,IsBegin,IsEnd)
	values	('设计中',0,'3.png',0,0)
set @status2=SCOPE_IDENTITY()
insert into OrderStatus(StatusName,IsSystemReversed,Icon,IsBegin,IsEnd)
	values	('制作中',0,'4.png',0,0)
set @status3=SCOPE_IDENTITY()
insert into OrderStatus(StatusName,IsSystemReversed,Icon,IsBegin,IsEnd)
	values	('送货中',0,'5.png',0,0)
set @status4=SCOPE_IDENTITY()
insert into OrderStatus(StatusName,IsSystemReversed,Icon,IsBegin,IsEnd)
	values	('已完成',1,'20.png',0,1)
set @status5=SCOPE_IDENTITY()

-- Default password is "HelloWorld"
-- Default permission is ALL
declare @permissions nvarchar(1000)
set @permissions=''
select @permissions = @permissions + ','+ PermissionNo from Permission
set @permissions = substring(@permissions,2,len(@permissions)-1) 

declare @EmployeeId int
insert into Employee values(
	  @UserName, '',(select top 1 Id from Department),
		null,null,null,null,null,1,1,null,null,null,@Mobile,null,7,1,4,3,3,null,5,null,null,null,null,'/Image/noface.png')
set @EmployeeId= SCOPE_IDENTITY() 

insert into EmployeeSalarySetting
select @EmployeeId,SettingValue1,null,SettingValue2 from SystemSetting where SettingName='EmployeeSalarySetting'

insert into [User](LoginId,UserName, [Password],IsActive,IsAdmin,[Permissions],AutoGenerateUserId,EmployeeId,GUID)
	values(@Mobile,@UserName, 'E10ADC3949BA59ABBE56E057F20F883E',1,1,@permissions,0,@EmployeeId,replace(newid(), '-','') + replace(newid(), '-','') ) 
set @UserId= SCOPE_IDENTITY() 

-- Work flow

insert into WorkFlow(WorkFlowName,WorkFlowDescription,IsActive)
	values	('标准流程',null,1)
set @workFlowNo=SCOPE_IDENTITY()

insert into WorkFlowStep(WorkFlowStepName,WorkFlowNo,CanMarkComplete,
			FollowupSteps,StatusNo,SN,IsSystemReserved,IsBeginPoint,IsEndPoint,UserIds)
	values ('开单',@workFlowNo,0,'',@status1,'01',1,1,0,null)
set @step1=SCOPE_IDENTITY()
insert into WorkFlowStep(WorkFlowStepName,WorkFlowNo,CanMarkComplete,
			FollowupSteps,StatusNo,SN,IsSystemReserved,IsBeginPoint,IsEndPoint,UserIds)
	values  ('设计',@workFlowNo,0,'',@status2,'02',0,0,0,@UserId)
set @step2=SCOPE_IDENTITY()
insert into WorkFlowStep(WorkFlowStepName,WorkFlowNo,CanMarkComplete,
			FollowupSteps,StatusNo,SN,IsSystemReserved,IsBeginPoint,IsEndPoint,UserIds)
	values ('制作',@workFlowNo,0,'',@status3,'03',0,0,0,@UserId)
set @step3=SCOPE_IDENTITY()
insert into WorkFlowStep(WorkFlowStepName,WorkFlowNo,CanMarkComplete,
			FollowupSteps,StatusNo,SN,IsSystemReserved,IsBeginPoint,IsEndPoint,UserIds)
	values ('送货',@workFlowNo,0,'',@status4,'04',0,0,0,@UserId)
set @step4=SCOPE_IDENTITY()
insert into WorkFlowStep(WorkFlowStepName,WorkFlowNo,CanMarkComplete,
			FollowupSteps,StatusNo,SN,IsSystemReserved,IsBeginPoint,IsEndPoint,UserIds)
	values ('完成',@workFlowNo,0,null,@status5,'99',1,0,1,null)	
set @step5=SCOPE_IDENTITY()

update WorkFlowStep set FollowupSteps=CONVERT(nvarchar(10),@step2)+','+CONVERT(nvarchar(10),@step5) where WorkFlowStepNo=@step1
update WorkFlowStep set FollowupSteps=@step3 where WorkFlowStepNo=@step2
update WorkFlowStep set FollowupSteps=CONVERT(nvarchar(10), @step4)+','+CONVERT(nvarchar(10),@step5) where WorkFlowStepNo=@step3
update WorkFlowStep set FollowupSteps=@step5 where WorkFlowStepNo=@step4

DECLARE @Memo NVARCHAR(50)
SET @Memo = convert(nvarchar(20),datepart(year, getdate()))+'-' +convert(nvarchar(20),datepart(month, getdate()))+'-'+convert(nvarchar(20),datepart(day, getdate()))+' 系统创建'

INSERT INTO AccountTitle
	select SettingValue1,SettingValue2,null,1,@Memo,'SYS',GETDATE() from SystemSetting where SettingName='AccountTitle'
 
	
insert into Supplier(Name,IsActive,IsSystemReserved,Creator,CreateTime)
	values('空（无需对账）',1,1,'sys',GETDATE())


commit tran aa
END
GO
/****** Object:  StoredProcedure [dbo].[CopyFactoryPrice]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CopyFactoryPrice]	 
	@SourceFactoryNo nvarchar(50),
	@DestinationFactoryNo nvarchar(50)
as
begin 		
			
			begin tran aa
			
				delete from FactoryMaterial where FactoryNo in (select FactoryNo from Factory)
					and FactoryNo = @DestinationFactoryNo
					
				insert into FactoryMaterial(FactoryNo,MaterialId,SailPrice)
					select @DestinationFactoryNo,MaterialId,SailPrice from FactoryMaterial
						where FactoryNo=@SourceFactoryNo
					
			commit tran aa
	 
end
GO
/****** Object:  StoredProcedure [dbo].[CopyCustomerPrice]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CopyCustomerPrice]	 
	@SourceCustomerNo nvarchar(50),
	@DestinationCustomerNo nvarchar(50)
as
begin 		
			
			begin tran aa
			
				delete from CustomerMaterial where CustomerNo in (select CustomerNo from Customer)
					and CustomerNo = @DestinationCustomerNo
					
				insert into CustomerMaterial(CustomerNo,MaterialId,SailPrice)
					select @DestinationCustomerNo,MaterialId,SailPrice from CustomerMaterial
						where CustomerNo=@SourceCustomerNo
					
			commit tran aa
	 
end
GO
/****** Object:  StoredProcedure [dbo].[SetFactoryPrice]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[SetFactoryPrice]	
	@FactoryNo nvarchar(50),
	@MaterialId int,
	@SailPrice decimal(18,4)
as
begin

	if not exists (select FactoryNo from Factory where FactoryNo=@FactoryNo)
	return
	
	if not exists (select Id from Material where Id=@MaterialId and CategoryId in (select Id from MaterialCategory))
	return	
	
	if exists (select Id from FactoryMaterial where FactoryNo=@FactoryNo and MaterialId=@MaterialId)
		update FactoryMaterial set SailPrice=@SailPrice where FactoryNo=@FactoryNo and MaterialId=@MaterialId
	else
		insert into FactoryMaterial(FactoryNo,MaterialId,SailPrice)
			values (@FactoryNo,@MaterialId,@SailPrice)	
end
GO
/****** Object:  StoredProcedure [dbo].[SetCustomerPrice]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[SetCustomerPrice]
	@CustomerNo nvarchar(50),
	@MaterialId int,
	@SailPrice decimal(18,4)
as
begin

	if not exists (select CustomerNo from Customer where CustomerNo=@CustomerNo)
	return
	
	if not exists (select Id from Material where Id=@MaterialId and CategoryId in (select Id from MaterialCategory))
	return	
	
	if exists (select Id from CustomerMaterial where CustomerNo=@CustomerNo and MaterialId=@MaterialId)
		update CustomerMaterial set SailPrice=@SailPrice where CustomerNo=@CustomerNo and MaterialId=@MaterialId
	else
		insert into CustomerMaterial(CustomerNo,MaterialId,SailPrice)
			values (@CustomerNo,@MaterialId,@SailPrice)
			
	 
end
GO
/****** Object:  StoredProcedure [dbo].[QueryFactoryPrices]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[QueryFactoryPrices]	
	@FactoryNo nvarchar(50),
	@MaterialId int
as
begin 		
	if @FactoryNo is not null and @MaterialId is not null	
	select c.FactoryNo,c.FactoryName as FactoryName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Factory c on c.FactoryNo=@FactoryNo and m.Id=@MaterialId and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id 
				left join FactoryMaterial cm on cm.FactoryNo=c.FactoryNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId			
			
	else if @FactoryNo is not null
			select c.FactoryNo,c.FactoryName as FactoryName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Factory c on c.FactoryNo=@FactoryNo and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id 
				left join FactoryMaterial cm on cm.FactoryNo=c.FactoryNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId	
	else 
	  	 select c.FactoryNo,c.FactoryName as FactoryName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Factory c on m.Id=@MaterialId and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id 
				left join FactoryMaterial cm on cm.FactoryNo=c.FactoryNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId	
	 
end
GO
/****** Object:  StoredProcedure [dbo].[QueryCustomerPrices]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[QueryCustomerPrices]
	@CustomerNo nvarchar(50),
	@MaterialId int
as
begin 		
	if @CustomerNo is not null and @MaterialId is not null	
	select c.CustomerNo,c.CustomerShortName as CustomerName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Customer c on c.CustomerNo=@CustomerNo and m.Id=@MaterialId and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id 
				left join CustomerMaterial cm on cm.CustomerNo=c.CustomerNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId			
			
	else if @CustomerNo is not null
			select c.CustomerNo,c.CustomerShortName as CustomerName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Customer c on c.CustomerNo=@CustomerNo and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id
				left join CustomerMaterial cm on cm.CustomerNo=c.CustomerNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId	
	else 
	  	 select c.CustomerNo,c.CustomerShortName as CustomerName,m.Id as MaterialId,m.Name as MaterialName ,cm.SailPrice,pu.Name as UnitName from Material m  
				join Customer c on m.Id=@MaterialId and m.IsActive=1
				join MaterialCategory mc on m.CategoryId=mc.Id 
				left join CustomerMaterial cm on cm.CustomerNo=c.CustomerNo and cm.MaterialId=m.Id	
				inner join PriceUnit pu on pu.Id=m.PriceUnitId	
	 
end
GO
/****** Object:  Table [dbo].[Quotation]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Quotation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[QuotationName] [nvarchar](1000) NOT NULL,
	[CustomerNo] [int] NULL,
	[CustomerName] [nvarchar](150) NULL,
	[ContactNo] [int] NULL,
	[Contact] [nvarchar](100) NULL,
	[QuotationDate] [datetime] NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](1000) NULL,
	[TemplateNo] [int] NULL,
	[Discount] [numeric](18, 2) NULL,
	[MarketAmount] [numeric](18, 2) NOT NULL,
	[CreatorId] [int] NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreatedTime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Order]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Order](
	[OrderNo] [nvarchar](50) NOT NULL,
	[OrderName] [nvarchar](1000) NOT NULL,
	[ContactNo] [int] NULL,
	[Contact] [nvarchar](100) NULL,
	[PaymentStatus] [char](1) NOT NULL,
	[CustomerNo] [int] NOT NULL,
	[CustomerShortName] [nvarchar](100) NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Paid] [numeric](18, 2) NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreatedTime] [datetime] NOT NULL,
	[TransactionDate] [datetime] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[OrderTemplate] [nvarchar](max) NULL,
	[Memo] [nvarchar](1000) NULL,
	[OrderStatusNo] [int] NOT NULL,
	[ContributionComplete] [bit] NULL,
	[Salesman] [nvarchar](500) NULL,
	[WorkFlowNo] [int] NULL,
	[IsSubOrder] [bit] NOT NULL,
	[ExpectDeliveryDate] [date] NULL,
	[QueryCode] [nvarchar](4) NOT NULL,
	[Approved] [bit] NOT NULL,
	[IsDelivery] [bit] NOT NULL,
	[DeliveryAddress] [nvarchar](500) NULL,
	[TemplateNo] [int] NULL,
	[DeliveryType] [nvarchar](50) NULL,
	[Discount] [numeric](18, 2) NULL,
	[MarketAmount] [numeric](18, 2) NOT NULL,
	[RecordChannel] [int] NOT NULL,
	[CreatorId] [int] NOT NULL,
	[IsInvoiced] [bit] NOT NULL,
	[EditModel] [int] NOT NULL,
	[CompletionDate] [datetime] NULL,
	[InstallMemo] [nvarchar](250) NULL,
	[InstallImg] [nvarchar](250) NULL,
	[WidthUnit] [nvarchar](2) NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
	[OrderNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[FactoryOrder]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FactoryOrder](
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
	[OrderName] [nvarchar](1000) NOT NULL,
	[Contact] [nvarchar](100) NULL,
	[PaymentStatus] [char](1) NOT NULL,
	[FactoryNo] [int] NOT NULL,
	[MarketAmount] [numeric](18, 2) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Discount] [numeric](18, 2) NULL,
	[Paid] [numeric](18, 2) NULL,
	[TrackUser] [int] NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[TransactionDate] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[Memo] [nvarchar](max) NULL,
	[OrderStatus] [int] NOT NULL,
	[ExpectDeliveryDate] [datetime] NULL,
	[TemplateId] [int] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[IsInvoiced] [bit] NOT NULL,
	[EditModel] [int] NOT NULL,
	[WidthUnit] [nvarchar](2) NULL,
PRIMARY KEY CLUSTERED 
(
	[FactoryOrderNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[OrderPayment]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderPayment](
	[PaymentNo] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[PaymentType] [nvarchar](50) NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Memo] [nvarchar](200) NULL,
 CONSTRAINT [PK_OrderPayment] PRIMARY KEY CLUSTERED 
(
	[PaymentNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderMemo]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderMemo](
	[MemoNo] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[Memo] [nvarchar](200) NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[Createtime] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[MemoNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderLog](
	[OrderLogNo] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Operation] [ntext] NOT NULL,
	[LogDate] [datetime] NOT NULL,
	[IsPublic] [bit] NOT NULL,
 CONSTRAINT [PK_OrderLog] PRIMARY KEY CLUSTERED 
(
	[OrderLogNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderFlow]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderFlow](
	[OrderFlowNo] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[WorkFlowNo] [int] NOT NULL,
	[WorkFlowStepNo] [int] NOT NULL,
	[BeginDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[UserId] [int] NULL,
	[UserName] [nvarchar](50) NULL,
	[IsFinished] [bit] NOT NULL,
	[Memo] [nvarchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[OrderFlowNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderFactoryOrder]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderFactoryOrder](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderDetail]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[Detail] [nvarchar](max) NOT NULL,
	[DetailText] [nvarchar](max) NULL,
	[Category] [nvarchar](50) NOT NULL,
	[CategoryName] [nvarchar](50) NOT NULL,
	[ReportCategory] [nvarchar](50) NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[Price] [numeric](18, 4) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](max) NULL,
	[CreateTime] [datetime] NOT NULL,
	[OrderName] [nvarchar](250) NOT NULL,
	[PriceUnitId] [int] NULL,
	[MaterialId] [int] NULL,
	[MaterialCostPrice] [decimal](18, 4) NULL,
	[MaterialName] [nvarchar](150) NULL,
	[StockNumber] [numeric](18, 2) NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderCost]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderCost](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[FeeDesign] [numeric](18, 2) NULL,
	[FeeMaterial] [numeric](18, 2) NULL,
	[FeeProcessing] [numeric](18, 2) NULL,
	[FeeFactory] [numeric](18, 2) NULL,
	[FeeCommission] [numeric](18, 2) NULL,
	[FeeCommunication] [numeric](18, 2) NULL,
	[FeeInstallation] [numeric](18, 2) NULL,
	[FeeTax] [numeric](18, 2) NULL,
	[FeeOther] [numeric](18, 2) NULL,
	[TotalFee]  AS ((((((((isnull([FeeDesign],(0))+isnull([FeeMaterial],(0)))+isnull([FeeProcessing],(0)))+isnull([FeeFactory],(0)))+isnull([FeeCommission],(0)))+isnull([FeeInstallation],(0)))+isnull([FeeTax],(0)))+isnull([FeeOther],(0)))+isnull([FeeCommunication],(0))),
	[Memo] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[OrderAttachment]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderAttachment](
	[AttachmentNo] [int] IDENTITY(1,1) NOT NULL,
	[OrderNo] [nvarchar](50) NOT NULL,
	[FileName] [nvarchar](200) NOT NULL,
	[FilePath] [nvarchar](200) NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[Createtime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[FileTypeImage] [nvarchar](50) NOT NULL,
	[UploadByCustomer] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[AttachmentNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryOrderPayment]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryOrderPayment](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[PaymentType] [nvarchar](50) NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[Memo] [nvarchar](250) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryOrderLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryOrderLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Operation] [nvarchar](250) NOT NULL,
	[LogDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryOrderDetail]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryOrderDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderName] [nvarchar](1000) NOT NULL,
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
	[Detail] [nvarchar](max) NOT NULL,
	[DetailText] [nvarchar](max) NULL,
	[Category] [nvarchar](50) NOT NULL,
	[ReportCategory] [nvarchar](50) NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[Price] [numeric](18, 4) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](max) NULL,
	[CreateTime] [datetime] NOT NULL,
	[PriceUnitId] [int] NULL,
	[MaterialId] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[FactoryOrderAttachment]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FactoryOrderAttachment](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FactoryOrderNo] [nvarchar](50) NOT NULL,
	[FileName] [nvarchar](200) NOT NULL,
	[FilePath] [nvarchar](200) NOT NULL,
	[Creator] [nvarchar](50) NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[IsPublic] [bit] NOT NULL,
	[FileTypeImage] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[QuotationLog]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuotationLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[QuotationId] [int] NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Operation] [ntext] NOT NULL,
	[LogDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[QuotationDetail]    Script Date: 01/30/2018 13:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuotationDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[QuotationId] [int] NOT NULL,
	[Detail] [nvarchar](max) NOT NULL,
	[DetailText] [nvarchar](max) NULL,
	[Category] [nvarchar](50) NOT NULL,
	[CategoryName] [nvarchar](50) NOT NULL,
	[ReportCategory] [nvarchar](50) NOT NULL,
	[Number] [numeric](18, 2) NOT NULL,
	[Price] [numeric](18, 4) NOT NULL,
	[Amount] [numeric](18, 2) NOT NULL,
	[Memo] [nvarchar](max) NULL,
	[QuotationName] [nvarchar](250) NOT NULL,
	[PriceUnitId] [int] NULL,
	[MaterialId] [int] NULL,
	[MaterialCostPrice] [decimal](18, 4) NULL,
	[MaterialName] [nvarchar](150) NULL,
	[StockNumber] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[UpdateOrderCost]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[UpdateOrderCost]	
	@OrderNo nvarchar(50),
	@Cost numeric(18,2),
	@Memo nvarchar(150),
	@UpdateType int,
	@TotalCost numeric(18,2) output
as
begin

	set @TotalCost=0
	if not exists (select OrderNo  from [Order] where OrderNo=@OrderNo)
	begin		
		return
	end
	
	if not exists (select Id from OrderCost where OrderNo=@OrderNo)	
		insert into OrderCost(OrderNo) values (@OrderNo)
	
	if @UpdateType=1
		update OrderCost set FeeDesign=@Cost where OrderNo=@OrderNo
	else if @UpdateType=2
		update OrderCost set FeeMaterial=@Cost where OrderNo=@OrderNo
	else if @UpdateType=3
		update OrderCost set FeeProcessing=@Cost where OrderNo=@OrderNo
	else if @UpdateType=4
		update OrderCost set FeeFactory=@Cost where OrderNo=@OrderNo
	else if @UpdateType=5
		update OrderCost set FeeCommission=@Cost where OrderNo=@OrderNo
	else if @UpdateType=6
		update OrderCost set FeeCommunication=@Cost where OrderNo=@OrderNo
	else if @UpdateType=7
		update OrderCost set FeeInstallation=@Cost where OrderNo=@OrderNo
	else if @UpdateType=8
		update OrderCost set FeeTax=@Cost where OrderNo=@OrderNo
	else if @UpdateType=9
		update OrderCost set FeeOther=@Cost where OrderNo=@OrderNo
	else
		update OrderCost set Memo=@Memo where OrderNo=@OrderNo
	 
	 select @TotalCost=TotalFee*100 from OrderCost where OrderNo=@OrderNo	 
	 
end
GO
/****** Object:  StoredProcedure [dbo].[QueryOrderCostReport]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[QueryOrderCostReport]
	@BeginDate datetime,
	@EndDate datetime	 
as
begin 	

	create table #OrderCost(ItemName nvarchar(50),ItemValue decimal(18,2),ItemPercent decimal(18,2),ItemType int)
	
	declare @totalNumber int
	declare @totalAmount decimal(18,2)
	declare @totalCost decimal(18,2)
	declare @totalProfile decimal(18,2)
	declare @feeDesign decimal(18,2)
	declare @feeMaterial decimal(18,2)
	declare @feeProcessing decimal(18,2)
	declare @feefactory decimal(18,2)
	declare @feeCommission decimal(18,2)
	declare @feeCommunication decimal(18,2)
	declare @feeInstallation decimal(18,2)
	declare @feeTax decimal(18,2)
	declare @feeOther decimal(18,2) 
	
	select @totalNumber=COUNT(OrderNo),@totalAmount=sum(Amount) from [Order] where IsActive=1 and TransactionDate>=@BeginDate and TransactionDate<=@EndDate 
	select @totalCost=SUM(OrderCost.TotalFee) ,
		@feeDesign=sum(OrderCost.FeeDesign),
		@feeMaterial=sum(OrderCost.FeeMaterial),
		@feeProcessing=sum(OrderCost.FeeProcessing),
		@feefactory=sum(OrderCost.FeeFactory),
		@feeCommission=sum(OrderCost.FeeCommission),
		@feeCommunication=sum(OrderCost.FeeCommunication),
		@feeInstallation=sum(OrderCost.FeeInstallation),
		@feeTax=sum(OrderCost.FeeTax),
		@feeOther=sum(OrderCost.FeeOther) 		
	from OrderCost inner join [Order] on [Order].OrderNo=OrderCost.OrderNo and [Order].IsActive=1 and [Order].TransactionDate>=@BeginDate and [Order].TransactionDate<=@EndDate 
	
	
	set @totalAmount=ISNULL(@totalAmount,0)
	set @totalCost=ISNULL(@totalCost,0)
	set @feeDesign=ISNULL(@feeDesign,0)
	set @feeMaterial=ISNULL(@feeMaterial,0)
	set @feeProcessing=ISNULL(@feeProcessing,0)
	set @feefactory=ISNULL(@feefactory,0)
	set @feeCommission=ISNULL(@feeCommission,0)
	set @feeCommunication=ISNULL(@feeCommunication,0)
	set @feeInstallation=ISNULL(@feeInstallation,0)
	set @feeTax=ISNULL(@feeTax,0)
	set @feeOther=ISNULL(@feeOther,0)
	
	set @totalProfile=ISNULL(@totalAmount,0) - ISNULL(@totalCost,0)
	
	insert into #OrderCost values
	('订单总数量',@totalNumber,0,1),
	('订单总金额',@totalAmount,0,1),
	('订单总成本',@totalCost,0,1),
	('订单总毛利',@totalProfile,0,1),
	('订单毛利率',case @totalAmount when 0 then 0 else  @totalProfile/@totalAmount end,0,1),
	
	('设计费',@feeDesign,case @totalCost when 0 then 0 else @feeDesign/@totalCost end,2),
	('材料费',@feeMaterial,case @totalCost when 0 then 0 else @feeMaterial/@totalCost end,2),
	('制作费',@feeProcessing,case @totalCost when 0 then 0 else @feeProcessing/@totalCost end,2),
	('外协费',@feefactory,case @totalCost when 0 then 0 else @feefactory/@totalCost end,2),
	('业务提成',@feeCommission,case @totalCost when 0 then 0 else @feeCommission/@totalCost end,2),
	('交通费',@feeCommunication,case @totalCost when 0 then 0 else @feeCommunication/@totalCost end,2),
	('安装费',@feeInstallation,case @totalCost when 0 then 0 else @feeInstallation/@totalCost end,2),
	('税费',@feeTax,case @totalCost when 0 then 0 else @feeTax/@totalCost end,2),
	('其它费用',@feeOther,case @totalCost when 0 then 0 else @feeOther/@totalCost end,2)
	
	
	select ItemName,ItemValue,ItemPercent,ItemType from #OrderCost
	drop table #OrderCost						
	 
end
GO
/****** Object:  StoredProcedure [dbo].[QueryOrderCost]    Script Date: 01/30/2018 13:08:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[QueryOrderCost]
	@BeginDate datetime,
	@EndDate datetime,
	@Key nvarchar(150)
as
begin 	
	  	 select c.CustomerShortName as CustomerName,m.Creator,m.TransactionDate,m.Amount,
	  			cm.FeeCommission,cm.FeeCommunication,cm.FeeDesign,cm.FeeFactory,cm.FeeInstallation,cm.FeeMaterial,cm.FeeOther,
	  			cm.FeeProcessing,cm.FeeTax,cm.TotalFee, cm.Memo,cm.Id as CostId,m.OrderNo,m.OrderName
	  	  from [Order] m  
				join Customer c on m.CustomerNo=c.CustomerNo and m.IsActive=1				
				left join OrderCost cm on cm.OrderNo=m.OrderNo 
					where (@Key is null or m.OrderName like '%'+ @Key +'%' or c.CustomerFullName like '%'+ @Key +'%'
					or m.OrderNo like '%'+ @Key +'%' or m.Creator like '%'+ @Key +'%') and m.TransactionDate>=@BeginDate and m.TransactionDate<=@EndDate order by m.TransactionDate desc			
				
								
	 
end
GO
/****** Object:  StoredProcedure [dbo].[AutoGetOrderCostFee]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[AutoGetOrderCostFee]	
	@OrderNo nvarchar(50),		
	@UpdateType int,
	@Fee numeric(18,2) output,
	@TotalCost numeric(18,2) output,
	@Message nvarchar(150) output
as
begin

	set @Fee=null
	set @TotalCost=null
	set @Message=null
	if not exists (select OrderNo  from [Order] where OrderNo=@OrderNo)
	begin		
		return
	end
	
	if not exists (select Id from OrderCost where OrderNo=@OrderNo)	
		insert into OrderCost(OrderNo) values (@OrderNo)
		
			
	if @UpdateType=2
	begin
		select @Fee=SUM(ISNULL(MaterialCostPrice,0) * Number) from OrderDetail where OrderNo=@OrderNo
		update OrderCost set FeeMaterial=ISNULL(@Fee,0) where OrderNo=@OrderNo
		set @Fee=ISNULL(@Fee,0)*100
	end	
	else if @UpdateType=4
	begin
		select @Fee=SUM(ISNULL(Amount,0)) from FactoryOrder 
			where FactoryOrderNo in (select FactoryOrderNo from OrderFactoryOrder where OrderNo=@OrderNo)			
		update OrderCost set FeeFactory=ISNULL(@Fee,0) where OrderNo=@OrderNo
		
		declare @fOrderNo nvarchar(50)
		select @fOrderNo=FactoryOrderNo from OrderFactoryOrder 
			 where FactoryOrderNo in (select FactoryOrderNo from OrderFactoryOrder where OrderNo=@OrderNo)
			 group by FactoryOrderNo having COUNT(FactoryOrderNo)>1
		
		if @fOrderNo is not null
			set @Message='提示：外协单['+@fOrderNo+']关联了多个客户订单，自动获取的外协费用可能不准确。'
			
		set @Fee=ISNULL(@Fee,0)*100
	end 
	
	select @TotalCost=TotalFee*100 from OrderCost where OrderNo=@OrderNo	 
	 
end
GO
/****** Object:  StoredProcedure [dbo].[AddOrderBatchPayment]    Script Date: 01/30/2018 13:08:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddOrderBatchPayment]		
	@OrderNos nvarchar(max),
	@TotalPaymentAmount decimal(18,2),	
	@PaymentType nvarchar(50),
	@PaymentMemo nvarchar(150),	 
	@UserName nvarchar(150),
	@Result int output --1:支付金额大于未付金额 2:不是同一个客户
	
AS
BEGIN
	declare @UnPaidAmount decimal(18,2)
	declare @Sql nvarchar(max),@CustomerNo nvarchar(50),@Balance decimal(18,2),@IsSystemReversed bit
	set @Balance=0
	set @IsSystemReversed=0
	
	create table #NosTable(OrderNo nvarchar(50))
	set @Sql='insert into #NosTable select ''' +  replace(@OrderNos,',',''' union select ''') + ''''
	exec(@Sql)
	
	select @UnPaidAmount=sum(Amount-ISNULL(Paid,0)) from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1
	
	
	if @UnPaidAmount<@TotalPaymentAmount
	begin
		set @Result=1
		drop table #NosTable
		return
	end
	
	select distinct @CustomerNo=CustomerNo from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1
	if @@rowcount!=1
	begin
		set @Result=2
		drop table #NosTable
		return
	end	
	 
	select @IsSystemReversed=IsSystemReversed from PaymentType where TypeName=@PaymentType
	 
	if @IsSystemReversed=1
	begin
		select @Balance=isnull(Balance,0) from Customer where CustomerNo=@CustomerNo		
		if @Balance<@TotalPaymentAmount
		begin
			set @Result=3 --账户余额不足
			drop table #NosTable
			return
		end
	end
	
	
	declare @OrderNo varchar(50) ,@Amount decimal(18,2),@Paid decimal(18,2),@UnPaid decimal(18,2)
	declare @Now datetime
	set @Now=GETDATE()
	declare CurOrder cursor for select OrderNo,Amount,Paid from [Order] where OrderNo in (select OrderNo from #NosTable) and IsActive=1 order by TransactionDate 
	OPEN CurOrder
	
	FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
	
	BEGIN TRY
	
		begin tran tran_pay
		
		WHILE @@FETCH_STATUS=0
		begin			
			set @UnPaid=@Amount-ISNULL(@Paid,0)
			
			if @UnPaid=0 or @TotalPaymentAmount=0 -- 余额已分配完毕或此订单无需支付
			begin		
				FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
				continue			
			end
			
			if @TotalPaymentAmount>=@UnPaid --余额足够完全支付
			begin			 
				update [Order] set Paid=Amount,PaymentStatus=3 where OrderNo=@OrderNo
				insert into OrderPayment(OrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@OrderNo,@UnPaid,@PaymentType,@UserName,@Now,@PaymentMemo)				
				set @TotalPaymentAmount=@TotalPaymentAmount-@UnPaid
			end
			else 
			begin
				-- 余额仅能部分支付
				update [Order] set Paid=ISNULL(Paid,0) + @TotalPaymentAmount,PaymentStatus=2 where OrderNo=@OrderNo
				insert into OrderPayment(OrderNo,Amount,PaymentType,Creator,CreateTime,Memo)
					values(@OrderNo,@TotalPaymentAmount,@PaymentType,@UserName,@Now,@PaymentMemo)	
				set @TotalPaymentAmount=0 --无更多余额可供支付
				break
			end 			
			FETCH NEXT FROM CurOrder INTO @OrderNo, @Amount,@Paid
		end
		if @TotalPaymentAmount=0
		begin
			commit tran tran_pay
			set @Result=0
		end			
		else
		begin
			ROLLBACK TRAN tran_pay
			set @Result=4
		end
	end try
	begin catch
		IF @@TRANCOUNT > 0 
		ROLLBACK TRAN tran_pay
	end catch	
	
	CLOSE CurOrder
	DEALLOCATE CurOrder 	
	drop table #NosTable	
	
end
GO
/****** Object:  Default [DF__FactoryOr__IsInv__7C3A67EB]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrder] ADD  DEFAULT ((0)) FOR [IsInvoiced]
GO
/****** Object:  Default [DF_Order_IsActive]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order] ADD  CONSTRAINT [DF_Order_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
/****** Object:  Default [DF__Order__IsInvoice__7B4643B2]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order] ADD  DEFAULT ((0)) FOR [IsInvoiced]
GO
/****** Object:  Default [DF__Permissio__IsDef__2A01329B]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Permission] ADD  DEFAULT ((0)) FOR [IsDefault]
GO
/****** Object:  Default [DF_User_IsActive]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_IsActive]  DEFAULT ((0)) FOR [IsActive]
GO
/****** Object:  ForeignKey [FK__CompanyAc__Accou__058EC7FB]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[CompanyAccount]  WITH CHECK ADD FOREIGN KEY([AccountTitleNo])
REFERENCES [dbo].[AccountTitle] ([AccountTitleNo])
GO

/****** Object:  ForeignKey [FK__Contact__Custome__5669C4BE]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD FOREIGN KEY([CustomerNo])
REFERENCES [dbo].[Customer] ([CustomerNo])
GO

/****** Object:  ForeignKey [FK_CustomerDeposit_Customer]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[CustomerDeposit]  WITH CHECK ADD  CONSTRAINT [FK_CustomerDeposit_Customer] FOREIGN KEY([CustomerNo])
REFERENCES [dbo].[Customer] ([CustomerNo])
GO
ALTER TABLE [dbo].[CustomerDeposit] CHECK CONSTRAINT [FK_CustomerDeposit_Customer]
GO
/****** Object:  ForeignKey [FK__CustomerM__Custo__58520D30]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[CustomerMaterial]  WITH CHECK ADD FOREIGN KEY([CustomerNo])
REFERENCES [dbo].[Customer] ([CustomerNo])
GO

/****** Object:  ForeignKey [FK__CustomerM__Mater__59463169]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[CustomerMaterial]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__Employee__Depart__05C3D225]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeL__Emplo__13DCE752]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeLog]  WITH CHECK ADD FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeS__Emplo__18D6A699]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeSalary]  WITH CHECK ADD FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeS__Emplo__1D9B5BB6]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeSalaryAllowance]  WITH CHECK ADD FOREIGN KEY([EmployeeSalaryId])
REFERENCES [dbo].[EmployeeSalary] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeS__Emplo__226010D3]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeSalaryDeduct]  WITH CHECK ADD FOREIGN KEY([EmployeeSalaryId])
REFERENCES [dbo].[EmployeeSalary] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeS__Emplo__2724C5F0]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeSalaryInsurance]  WITH CHECK ADD FOREIGN KEY([EmployeeSalaryId])
REFERENCES [dbo].[EmployeeSalary] ([Id])
GO

/****** Object:  ForeignKey [FK__EmployeeS__Emplo__10416098]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[EmployeeSalarySetting]  WITH CHECK ADD FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO

/****** Object:  ForeignKey [FK__FactoryMa__Facto__027D5126]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryMaterial]  WITH CHECK ADD FOREIGN KEY([FactoryNo])
REFERENCES [dbo].[Factory] ([FactoryNo])
GO

/****** Object:  ForeignKey [FK__FactoryMa__Mater__0371755F]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryMaterial]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Templ__5BCD9859]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrder]  WITH CHECK ADD FOREIGN KEY([TemplateId])
REFERENCES [dbo].[FactoryTemplate] ([Id])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Track__5AD97420]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrder]  WITH CHECK ADD FOREIGN KEY([TrackUser])
REFERENCES [dbo].[User] ([UserId])
GO

/****** Object:  ForeignKey [FK_FactoryOrder_Factory]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrder]  WITH CHECK ADD  CONSTRAINT [FK_FactoryOrder_Factory] FOREIGN KEY([FactoryNo])
REFERENCES [dbo].[Factory] ([FactoryNo])
GO
ALTER TABLE [dbo].[FactoryOrder] CHECK CONSTRAINT [FK_FactoryOrder_Factory]
GO
/****** Object:  ForeignKey [FK__FactoryOr__Facto__65AC084E]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderAttachment]  WITH CHECK ADD FOREIGN KEY([FactoryOrderNo])
REFERENCES [dbo].[FactoryOrder] ([FactoryOrderNo])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Facto__664B26CC]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderDetail]  WITH CHECK ADD FOREIGN KEY([FactoryOrderNo])
REFERENCES [dbo].[FactoryOrder] ([FactoryOrderNo])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Mater__679450C0]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderDetail]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Price__15C52FC4]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderDetail]  WITH CHECK ADD FOREIGN KEY([PriceUnitId])
REFERENCES [dbo].[PriceUnit] ([Id])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Facto__697C9932]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderLog]  WITH CHECK ADD FOREIGN KEY([FactoryOrderNo])
REFERENCES [dbo].[FactoryOrder] ([FactoryOrderNo])
GO

/****** Object:  ForeignKey [FK__FactoryOr__Facto__6A70BD6B]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[FactoryOrderPayment]  WITH CHECK ADD FOREIGN KEY([FactoryOrderNo])
REFERENCES [dbo].[FactoryOrder] ([FactoryOrderNo])
GO

/****** Object:  ForeignKey [FK__Material__Catego__2AC04CAA]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Material]  WITH CHECK ADD FOREIGN KEY([CategoryId])
REFERENCES [dbo].[MaterialCategory] ([Id])
GO

/****** Object:  ForeignKey [FK__Material__PriceU__2BB470E3]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Material]  WITH CHECK ADD FOREIGN KEY([PriceUnitId])
REFERENCES [dbo].[PriceUnit] ([Id])
GO

/****** Object:  ForeignKey [FK__Material__StockM__2CA8951C]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Material]  WITH CHECK ADD FOREIGN KEY([StockMaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__Order__CreatorId__6E414E4F]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order]  WITH CHECK ADD FOREIGN KEY([CreatorId])
REFERENCES [dbo].[User] ([UserId])
GO

/****** Object:  ForeignKey [FK_Order_Customer]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerNo])
REFERENCES [dbo].[Customer] ([CustomerNo])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO

/****** Object:  ForeignKey [FK_Order_OrderStatus]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_OrderStatus] FOREIGN KEY([OrderStatusNo])
REFERENCES [dbo].[OrderStatus] ([StatusNo])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderStatus]
GO
/****** Object:  ForeignKey [FK_Order_OrderTemplate]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_OrderTemplate] FOREIGN KEY([TemplateNo])
REFERENCES [dbo].[OrderTemplate] ([TemplateNo])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderTemplate]
GO
/****** Object:  ForeignKey [FK_Order_WorkFlow]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_WorkFlow] FOREIGN KEY([WorkFlowNo])
REFERENCES [dbo].[WorkFlow] ([WorkFlowNo])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_WorkFlow]
GO
/****** Object:  ForeignKey [FK__OrderAtta__Order__6BCEF5F8]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderAttachment]  WITH CHECK ADD FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO

/****** Object:  ForeignKey [FK__OrderCost__Order__0B129727]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderCost]  WITH CHECK ADD FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO

/****** Object:  ForeignKey [FK__OrderDeta__Mater__5A6F5FCC]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__OrderDeta__Price__14D10B8B]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK ADD FOREIGN KEY([PriceUnitId])
REFERENCES [dbo].[PriceUnit] ([Id])
GO

/****** Object:  ForeignKey [FK_Order_OrderDetail]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_Order_OrderDetail] FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_Order_OrderDetail]
GO
/****** Object:  ForeignKey [FK__OrderFact__Facto__618671AF]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFactoryOrder]  WITH CHECK ADD FOREIGN KEY([FactoryOrderNo])
REFERENCES [dbo].[FactoryOrder] ([FactoryOrderNo])
GO

/****** Object:  ForeignKey [FK__OrderFact__Order__60924D76]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFactoryOrder]  WITH CHECK ADD FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO

/****** Object:  ForeignKey [FK__OrderFlow__Order__642DD430]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFlow]  WITH CHECK ADD FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO

/****** Object:  ForeignKey [FK__OrderFlow__UserI__670A40DB]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFlow]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO

/****** Object:  ForeignKey [FK__OrderFlow__WorkF__6521F869]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFlow]  WITH CHECK ADD FOREIGN KEY([WorkFlowNo])
REFERENCES [dbo].[WorkFlow] ([WorkFlowNo])
GO
/****** Object:  ForeignKey [FK__OrderFlow__WorkF__66161CA2]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderFlow]  WITH CHECK ADD FOREIGN KEY([WorkFlowStepNo])
REFERENCES [dbo].[WorkFlowStep] ([WorkFlowStepNo])
GO

/****** Object:  ForeignKey [FK_OrderLog_Order]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderLog]  WITH CHECK ADD  CONSTRAINT [FK_OrderLog_Order] FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO
ALTER TABLE [dbo].[OrderLog] CHECK CONSTRAINT [FK_OrderLog_Order]
GO
/****** Object:  ForeignKey [FK_OrderMemo_Order]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderMemo]  WITH CHECK ADD  CONSTRAINT [FK_OrderMemo_Order] FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO
ALTER TABLE [dbo].[OrderMemo] CHECK CONSTRAINT [FK_OrderMemo_Order]
GO
/****** Object:  ForeignKey [FK_OrderPayment_Order]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[OrderPayment]  WITH CHECK ADD  CONSTRAINT [FK_OrderPayment_Order] FOREIGN KEY([OrderNo])
REFERENCES [dbo].[Order] ([OrderNo])
GO
ALTER TABLE [dbo].[OrderPayment] CHECK CONSTRAINT [FK_OrderPayment_Order]
GO
/****** Object:  ForeignKey [FK_Permission_RoleGroup]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Permission]  WITH CHECK ADD  CONSTRAINT [FK_Permission_RoleGroup] FOREIGN KEY([RoleGroupNo])
REFERENCES [dbo].[RoleGroup] ([RoleGroupNo])
GO
ALTER TABLE [dbo].[Permission] CHECK CONSTRAINT [FK_Permission_RoleGroup]
GO
/****** Object:  ForeignKey [FK__Quotation__Creat__024846FC]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Quotation]  WITH CHECK ADD FOREIGN KEY([CreatorId])
REFERENCES [dbo].[User] ([UserId])
GO

/****** Object:  ForeignKey [FK__Quotation__Custo__033C6B35]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Quotation]  WITH CHECK ADD FOREIGN KEY([CustomerNo])
REFERENCES [dbo].[Customer] ([CustomerNo])
GO

/****** Object:  ForeignKey [FK__Quotation__Templ__04308F6E]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Quotation]  WITH CHECK ADD FOREIGN KEY([TemplateNo])
REFERENCES [dbo].[OrderTemplate] ([TemplateNo])
GO

/****** Object:  ForeignKey [FK__Quotation__Mater__0524B3A7]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[QuotationDetail]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__Quotation__Price__0618D7E0]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[QuotationDetail]  WITH CHECK ADD FOREIGN KEY([PriceUnitId])
REFERENCES [dbo].[PriceUnit] ([Id])
GO

/****** Object:  ForeignKey [FK__Quotation__Quota__070CFC19]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[QuotationDetail]  WITH CHECK ADD FOREIGN KEY([QuotationId])
REFERENCES [dbo].[Quotation] ([Id])
GO

/****** Object:  ForeignKey [FK__Quotation__Quota__08012052]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[QuotationLog]  WITH CHECK ADD FOREIGN KEY([QuotationId])
REFERENCES [dbo].[Quotation] ([Id])
GO

/****** Object:  ForeignKey [FK__Stock__MaterialI__08F5448B]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[Stock]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__StockIn__Supplie__09E968C4]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockIn]  WITH CHECK ADD FOREIGN KEY([SupplierId])
REFERENCES [dbo].[Supplier] ([Id])
GO

/****** Object:  ForeignKey [FK__StockInDe__Mater__0ADD8CFD]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockInDetail]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO

/****** Object:  ForeignKey [FK__StockInDe__Stock__0BD1B136]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockInDetail]  WITH CHECK ADD FOREIGN KEY([StockInId])
REFERENCES [dbo].[StockIn] ([Id])
GO

/****** Object:  ForeignKey [FK__StockInLo__Stock__0CC5D56F]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockInLog]  WITH CHECK ADD FOREIGN KEY([StockInId])
REFERENCES [dbo].[StockIn] ([Id])
GO

/****** Object:  ForeignKey [FK__StockInPa__Stock__0DB9F9A8]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockInPayment]  WITH CHECK ADD FOREIGN KEY([StockInId])
REFERENCES [dbo].[StockIn] ([Id])
GO

/****** Object:  ForeignKey [FK__StockOutD__Mater__0EAE1DE1]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockOutDetail]  WITH CHECK ADD FOREIGN KEY([MaterialId])
REFERENCES [dbo].[Material] ([Id])
GO
 
/****** Object:  ForeignKey [FK__StockOutD__Stock__0FA2421A]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockOutDetail]  WITH CHECK ADD FOREIGN KEY([StockOutId])
REFERENCES [dbo].[StockOut] ([Id])
GO
 
/****** Object:  ForeignKey [FK__StockOutL__Stock__10966653]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[StockOutLog]  WITH CHECK ADD FOREIGN KEY([StockOutId])
REFERENCES [dbo].[StockOut] ([Id])
GO
 
/****** Object:  ForeignKey [FK_ToDo_User]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[ToDo]  WITH CHECK ADD  CONSTRAINT [FK_ToDo_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[ToDo] CHECK CONSTRAINT [FK_ToDo_User]
GO
/****** Object:  ForeignKey [FK__User__EmployeeId__127EAEC5]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[User]  WITH CHECK ADD FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO
 
/****** Object:  ForeignKey [FK__WorkFlowS__WorkF__1372D2FE]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[WorkFlowStep]  WITH CHECK ADD FOREIGN KEY([WorkFlowNo])
REFERENCES [dbo].[WorkFlow] ([WorkFlowNo])
GO
 
/****** Object:  ForeignKey [FK_WorkFlowStep_OrderStatus]    Script Date: 01/30/2018 13:06:26 ******/
ALTER TABLE [dbo].[WorkFlowStep]  WITH CHECK ADD  CONSTRAINT [FK_WorkFlowStep_OrderStatus] FOREIGN KEY([StatusNo])
REFERENCES [dbo].[OrderStatus] ([StatusNo])
GO
ALTER TABLE [dbo].[WorkFlowStep] CHECK CONSTRAINT [FK_WorkFlowStep_OrderStatus]
GO

USE [db9pm2c]
GO
 

create trigger [dbo].[DeductStockDelete]
on [dbo].[OrderDetail] after delete
as
begin
	declare @MaterialId int
	declare @ForStock bit,@StockMaterialId int,@OrderNo nvarchar(50),@DeductStock nvarchar(5),@DeductStockAfterZero nvarchar(5),@Number numeric(18,2),@StockNumber numeric(18,2)
	select @MaterialId=MaterialId from deleted
	select @ForStock=ForStock,@StockMaterialId=StockMaterialId from Material where Id=@MaterialId
	if @ForStock=0
	begin
		set @MaterialId=@StockMaterialId
	end
	
	if @MaterialId is not null
	begin		
		select @OrderNo=OrderNo,@Number=isnull(StockNumber,0) from deleted	
		select @DeductStock=SettingValue from CompanyConfiguration where SettingName='DeductStock' 
		if lower(@DeductStock)='true'
		begin
			select @StockNumber=Number from Stock where MaterialId=@MaterialId
			if @StockNumber is not null
			begin
				set @StockNumber=@StockNumber+@Number				 
				update Stock set Number=@StockNumber where MaterialId=@MaterialId 
			end
		end
	end	
end
go

USE [db9pm2c]
GO
 


create trigger [dbo].[DeductStockInsert]
on [dbo].[OrderDetail] after insert
as
begin
	declare @MaterialId int
	declare @ForStock bit,@StockMaterialId int, @OrderNo nvarchar(50),@DeductStock nvarchar(5),@DeductStockAfterZero nvarchar(5),@Number numeric(18,2),@StockNumber numeric(18,2)
	select @MaterialId=MaterialId from inserted
	select @ForStock=ForStock,@StockMaterialId=StockMaterialId from Material where Id=@MaterialId
	if @ForStock=0
	begin
		set @MaterialId=@StockMaterialId
	end
	
	if @MaterialId is not null
	begin		
		select @OrderNo=OrderNo,@Number=isnull(StockNumber,0) from inserted		
		select @DeductStock=SettingValue from CompanyConfiguration where SettingName='DeductStock' 
		select @DeductStockAfterZero=SettingValue from CompanyConfiguration where SettingName='DeductStockLessThenZero' 
		if lower(@DeductStock)='true'
		begin
			select @StockNumber=Number from Stock where MaterialId=@MaterialId
			if @StockNumber is not null
			begin
				set @StockNumber=@StockNumber-@Number
				if lower(@DeductStockAfterZero)='false' and @StockNumber<0
				begin
					set @StockNumber=0
				end
				update Stock set Number=@StockNumber where MaterialId=@MaterialId 
			end
		end
	end	
end
go

USE [db9pm2c]
GO
 

create trigger [dbo].[DeductStockUpdate]
on [dbo].[OrderDetail] after update
as
begin
	declare @MaterialId int
	declare @ForStock bit,@StockMaterialId int,@OrderNo nvarchar(50),@DeductStock nvarchar(5),@DeductStockAfterZero nvarchar(5),@Number numeric(18,2),@StockNumber numeric(18,2)
		
	select @OrderNo=OrderNo,@Number=isnull(StockNumber,0) from deleted
	
	select @DeductStock=SettingValue from CompanyConfiguration where SettingName='DeductStock'  
	select @DeductStockAfterZero=SettingValue from CompanyConfiguration where SettingName='DeductStockLessThenZero' 	
	select @MaterialId=MaterialId from deleted
	select @ForStock=ForStock,@StockMaterialId=StockMaterialId from Material where Id=@MaterialId
	if @ForStock=0
	begin
		set @MaterialId=@StockMaterialId
	end
	
	if @MaterialId is not null
	begin					
		if lower(@DeductStock)='true'
		begin
			select @StockNumber=Number from Stock where MaterialId=@MaterialId
			if @StockNumber is not null
			begin
				set @StockNumber=@StockNumber+@Number				 
				update Stock set Number=@StockNumber where MaterialId=@MaterialId 
			end			 
			 
		end
	end	
	
	set @MaterialId=null
	select @MaterialId=MaterialId from inserted
	select @Number=isnull(StockNumber,0) from inserted
	select @ForStock=ForStock,@StockMaterialId=StockMaterialId from Material where Id=@MaterialId
	if @ForStock=0
	begin
		set @MaterialId=@StockMaterialId
	end
	
	if @MaterialId is not null
	begin		
		if lower(@DeductStock)='true'
		begin
			set @StockNumber=null
			select @StockNumber=Number from Stock where MaterialId=@MaterialId
			if @StockNumber is not null
			begin
				set @StockNumber=@StockNumber-@Number
				if lower(@DeductStockAfterZero)='false' and @StockNumber<0
				begin
					set @StockNumber=0
				end
				update Stock set Number=@StockNumber where MaterialId=@MaterialId 
			end
		end
	end	
	
end
go

USE [db9pm2c]
GO
 

create trigger [dbo].[StockInApproved]
on [dbo].[StockIn] after update
as
begin
	declare @MaterialId int,@Status1 int,@Status2 int,@StockInTime datetime	
	declare @ForStock bit,@StockMaterialId int,@StockInId int, @DeductStock nvarchar(5),@DeductStockAfterZero nvarchar(5),@Number numeric(18,2)
	select @Status1 =[Status],@StockInTime=StockInTime from deleted 
	select @Status2 =[Status],@StockInId=id from inserted
	select @DeductStockAfterZero=SettingValue from CompanyConfiguration where SettingName='DeductStockLessThenZero' 
	
	if @Status1=1 and @Status2=2
	begin		
		select (case b.ForStock when 1 then b.Id else b.StockMaterialId end) as MaterialId ,sum(a.Number) as Number into #mtable  
			from StockInDetail a inner join Material b on a.MaterialId=b.Id where StockInId=@StockInId and MaterialId is not null
				group by b.Id,b.ForStock,b.StockMaterialId		
	
		insert into Stock 
			select MaterialId,0,@StockInTime from #mtable where MaterialId not in(select MaterialId from Stock )
			
		update a set Number=a.Number+ isnull(b.Number,0) from Stock a, #mtable b where a.MaterialId=b.MaterialId			
			
		drop table #mtable
	end
	else if @Status1=2 and @Status2=1
	begin
		select (case b.ForStock when 1 then b.Id else b.StockMaterialId end) as MaterialId ,sum(a.Number) as Number into #mtable1  
			from StockInDetail a inner join Material b on a.MaterialId=b.Id where StockInId=@StockInId and MaterialId is not null
					group by b.Id,b.ForStock,b.StockMaterialId
		 
		insert into Stock 
			select MaterialId,0,@StockInTime from #mtable1 where MaterialId not in(select MaterialId from Stock)
		
		update a set Number=a.Number- isnull(b.Number,0) from Stock a, #mtable1 b where a.MaterialId=b.MaterialId	
		if LOWER(@DeductStockAfterZero)='false'
		begin
			update a set Number=0 from Stock a, #mtable1 b where a.MaterialId=b.MaterialId and a.Number<0
		end		
		 	
		drop table #mtable1
	end
	
end
go

USE [db9pm2c]
GO
 
 
create trigger [dbo].[StockOutApproved]
on [dbo].[StockOut] after update
as
begin
	declare @MaterialId int,@Status1 int,@Status2 int,@StockOutTime datetime	
	declare @ForStock bit,@StockMaterialId int, @StockOutId int, @DeductStockAfterZero nvarchar(5),@Number numeric(18,2)
	select @Status1 =[Status],@StockOutTime=StockOutTime from deleted 
	select @Status2 =[Status],@StockOutId=Id from inserted
	select @DeductStockAfterZero=SettingValue from CompanyConfiguration where SettingName='DeductStockLessThenZero' 
	
	if @Status1=1 and @Status2=2
	begin		
		select (case b.ForStock when 1 then b.Id else b.StockMaterialId end) as MaterialId ,sum(a.Number) as Number into #mtable  
			from StockOutDetail a inner join Material b on a.MaterialId=b.Id where StockOutId=@StockOutId and MaterialId is not null
					group by b.Id,b.ForStock,b.StockMaterialId		 
		insert into Stock 
			select MaterialId,0,@StockOutTime from #mtable where MaterialId not in(select MaterialId from Stock)
		
		update a set Number=a.Number - isnull(b.Number,0) from Stock a, #mtable b where a.MaterialId=b.MaterialId	
		if LOWER(@DeductStockAfterZero)='false'
		begin
			update a set Number=0 from Stock a, #mtable b where a.MaterialId=b.MaterialId and a.Number<0
		end		
			
		 	
		drop table #mtable
	end
	else if @Status1=2 and @Status2=1
	begin
		select (case b.ForStock when 1 then b.Id else b.StockMaterialId end) as MaterialId ,sum(a.Number) as Number into #mtable1  
			from StockOutDetail a inner join Material b on a.MaterialId=b.Id where StockOutId=@StockOutId and MaterialId is not null
					group by b.Id,b.ForStock,b.StockMaterialId	
		 
		insert into Stock 
			select MaterialId,0,@StockOutTime from #mtable1 where MaterialId not in(select MaterialId from Stock)
		
		update a set Number=a.Number + isnull(b.Number,0) from Stock a, #mtable1 b where a.MaterialId=b.MaterialId			
	 	
		drop table #mtable1
	end
	
end
go

alter table FactoryOrder add TemplateType int null
alter table FactoryOrder add TemplateNo int null foreign key references OrderTemplate(TemplateNo)
go
update FactoryOrder set TemplateType=2
go
alter table FactoryOrder alter column TemplateType int not null
alter table FactoryOrder alter column TemplateId int null
go