Truncate Table version

Go

if not exists(select * from version where version='2.8.3.32')
  insert into version(version,updatetime) values ('2.8.3.32',getdate())
Go
--------------- SQL ---------------


Delete Users
Where IsNull(User_Code,'')=''
Go

Delete UserPrivilege
Where Object_ID is Null
Go

Delete GroupPrivilege
Where Object_ID is Null
Go

Delete Objects
Where Isnull(Object_Code,'')=''
Go

ALTER TABLE [dbo].[MouldProvideBase]
ALTER COLUMN [provider] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldProvideBase]
ALTER COLUMN [receiver] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldProvideBase]
ALTER COLUMN [maker] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceive]
ALTER COLUMN [receiver] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceive]
ALTER COLUMN [provider] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceive]
ALTER COLUMN [maker] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceiveBase]
ALTER COLUMN [receiver] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceiveBase]
ALTER COLUMN [provider] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldReceiveBase]
ALTER COLUMN [maker] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldMaintenance]
ALTER COLUMN [receiver] nvarchar(10)
GO
--------------- SQL ---------------

ALTER TABLE [dbo].[MouldMaintenance]
ALTER COLUMN [provider] nvarchar(10)
GO

if not exists(select * from sysobjects where name='MouldRegular' and xtype='U')
CREATE TABLE [dbo].[MouldRegular] (
  [ProdNr] nvarchar(20) NOT NULL,
  [SeriesNr] nvarchar(3) NOT NULL,
  [Repairitem] nvarchar(8),
  [RepairName] nvarchar(50),
  [PrevDate] datetime,
  [Days] int,
  [NextDate] datetime,
  [Times] int
)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldVindicate'
  and xtype='U') and name='SeriesNr')
ALTER TABLE [dbo].[MouldVindicate]
ADD [SeriesNr] nvarchar(3)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='IsClear')
ALTER TABLE [dbo].[MouldMaintenance]
ADD [IsClear] bit DEFAULT 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldVindicate'
  and xtype='U') and name='OldPunchQty')
ALTER TABLE [dbo].[MouldVindicate]
ADD [OldPunchQty] int DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldReceiveMangle'
  and xtype='U') and name='CMemo')
ALTER TABLE [dbo].[MouldReceiveMangle]
ADD [CMemo] nvarchar(1600)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldReceiveRepair'
  and xtype='U') and name='CMemo')
ALTER TABLE [dbo].[MouldReceiveRepair]
ADD [CMemo] nvarchar(1600)
GO

--------------- SQL ---------------

if not exists(select * from sysobjects where name='WorkCenterPn' and xtype='U')
CREATE TABLE [WorkCenterPn] (
	[WorkCnNr] [nvarchar] (13) NOT NULL ,
	[PersonNr] [nvarchar] (10) NOT NULL ,
	CONSTRAINT [IX_WorkCenterPn] UNIQUE  NONCLUSTERED
	(
		[WorkCnNr],
		[PersonNr]
	)  ON [PRIMARY]
) ON [PRIMARY]
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBase'
  and xtype='U') and name='ProjectName')
ALTER TABLE [dbo].[MouldProcessBase]
ADD [ProjectName] nvarchar(50)
GO

ALTER TABLE [dbo].[RPTTEMPLATE]
ALTER COLUMN [RPTNAME] varchar(100)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='tmpseriesnr')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [tmpseriesnr] nvarchar(3)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdProcessProduct'
  and xtype='U') and name='ProcessID')
ALTER TABLE [dbo].[MouldStdProcessProduct]
ADD [ProcessID] nvarchar(50) DEFAULT NewID()
GO

Update MouldStdProcessProduct
Set ProcessID=NewID()
Where isnull(ProcessID,'')=''
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdProcessProduct'
  and xtype='U') and name='tmpseriesnr')
ALTER TABLE [dbo].[MouldStdProcessProduct]
ADD [tmpseriesnr] nvarchar(3)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='WorkDate')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [WorkDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldscheduledetailnew'
  and xtype='U') and name='WorkDate')
ALTER TABLE [dbo].[mouldscheduledetailnew]
ADD [WorkDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProvide'
  and xtype='U') and name='ISCurrent')
ALTER TABLE [dbo].[MouldProvide]
ADD [ISCurrent] bit DEFAULT 0
GO

ALTER TABLE [dbo].[MouldBase]
ALTER COLUMN [gx_prodnr] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='CompanyInfo'
  and xtype='U') and name='CtrlPlanByBOM')
ALTER TABLE [dbo].[CompanyInfo]
ADD [CtrlPlanByBOM] bit DEFAULT 0
GO

ALTER TABLE [dbo].[BomReplace]
ALTER COLUMN [Prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldChgStatusProduct]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldCostBase]
ALTER COLUMN [ProdNr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldCostProduct]
ALTER COLUMN [Prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldInOutProduct]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldMaintain]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldMaintenance]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProcessBase]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProcessBOM]
ALTER COLUMN [Masternr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProcessProduct]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProduct]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProvide]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProvideBase]
ALTER COLUMN [productnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProduct]
ALTER COLUMN [productnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProvideBase]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProvideProduct]
ALTER COLUMN [ProdNr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldReceive]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldReceiveBase]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldReceiveProduct]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldRegular]
ALTER COLUMN [ProdNr] nvarchar(30)NOT NULL
GO

ALTER TABLE [dbo].[mouldschedulemaster]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldStdProcessBase]
ALTER COLUMN [prodnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldStdProcessProduct]
ALTER COLUMN [prodnr] nvarchar(31) NOT NULL
GO

ALTER TABLE [dbo].[MouldVindicate]
ALTER COLUMN [prodnr] nvarchar(30)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOM'
  and xtype='U') and name='Spec')
ALTER TABLE [dbo].[MouldProcessBOM]
ADD [Spec] nvarchar(50) DEFAULT ''
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostBase'
  and xtype='U') and name='SumAmount')
ALTER TABLE [dbo].[MouldCostBase]
ADD [SumAmount] float DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostProduct'
  and xtype='U') and name='Spec')
ALTER TABLE [dbo].[MouldCostProduct]
ADD [Spec] nvarchar(50) DEFAULT ''
GO

if not exists(select * from sysobjects where name='BomImport')
Create table BomImport
(RecNo  nvarchar(10),
 Prodnr nvarchar(20),
 ProdnrUnit nvarchar(10),
 Masternr nvarchar(20),
 Descript nvarchar(40),
 Spec nvarchar(50) ,
 MasternrUnit nvarchar(10),
 UnitCost nvarchar(10),
 Provider nvarchar(50),
 MainQty nvarchar(10),
 LossRate nvarchar(10) 
)
Go

if not exists(select * from sysobjects where name='MouldScheduleItem')
CREATE TABLE [MouldScheduleItem] (
  [ItemID] nvarchar(50) NOT NULL,
  [InvSeriesNr] nvarchar(13),
  [SeriesNr] nvarchar(3),
  [ItemNr] nvarchar(3),
  [WorkCenterNr] nvarchar(20),
  [P_NO] nvarchar(20),
  [InstDays] float(20) DEFAULT 0.0,
  [SoProcessID] nvarchar(50),
  PRIMARY KEY CLUSTERED ([ItemID])
)
GO

if not exists(select * from sysobjects where name='InventoryStore')
CREATE TABLE [InventoryStore] (
	[prodnr] [nvarchar] (30) NOT NULL ,
	[Dep_No] [nvarchar] (10) NOT NULL ,
	[Qty] [float] NULL  DEFAULT (0),
	[PreInQty] [float] NULL  DEFAULT (0),
	[PreOutQty] [float] NULL DEFAULT (0),
	[UseQty] [float] NULL DEFAULT (0),
	[updatetime] [datetime] NULL ,
	[updateby] [nvarchar] (20) NULL ,
	CONSTRAINT [PK_InventoryStore] PRIMARY KEY  CLUSTERED 
	(
		[prodnr],
		[Dep_No]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='InOutBase')
CREATE TABLE [InOutBase] (
	[invseriesnr] [nvarchar] (13)  NOT NULL ,
	[invnr] [nvarchar] (20)  NULL ,
	[Mark] [nvarchar] (1)  NULL ,
	[Ivdate] [datetime] NOT NULL ,
	[WorkCenterNr] [nvarchar] (20)  NULL ,
	[WorkerNr] [nvarchar] (50)   NULL ,
	[WorkerName] [nvarchar] (20)  NULL ,
	[make] [nvarchar] (15)  NULL ,
	[makeDate] [datetime] NULL ,
	[updateby] [nvarchar] (10)  NULL ,
	[updatetime] [datetime] NULL ,
	[cmemo] [ntext]   NULL ,
	CONSTRAINT [PK_InOutBase] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='InOutProduct')
CREATE TABLE [InOutProduct] (
	[invseriesnr] [nvarchar] (13)   NOT NULL ,
	[invnr] [nvarchar] (20)   NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[prodnr] [nvarchar] (30)   NULL ,
	[whousenr] [nvarchar] (4)   NULL ,
	[MainUnit] [nvarchar] (4)   NULL ,
	[Qty] [float] NULL DEFAULT (0),
	[soinvseriesnr] [nvarchar] (13)   NULL ,
	[soseriersnr] [nvarchar] (3)   NULL ,
	[PauseArea] [nvarchar] (10)   NULL ,
	[SourceWhourseNr] [nvarchar] (50)   NULL ,
	[SourcePos_No] [nvarchar] (20)   NULL ,
	[TargetWhourseNr] [nvarchar] (20)   NULL ,
	[TargetPos_No] [nvarchar] (50)   NULL ,
	[Cmemo] [ntext]   NULL ,
	[soprinvseriesnr] [nvarchar] (13)   NULL ,
	[soprseriersnr] [nvarchar] (3)   NULL ,
	CONSTRAINT [PK_InOutProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOM'
  and xtype='U') and name='BomID')
ALTER TABLE [dbo].[MouldProcessBOM]
ADD [BomID] [nvarchar] (50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOM'
  and xtype='U') and name='DelQty')
ALTER TABLE [dbo].[MouldProcessBOM]
ADD [DelQty] float DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InOutProduct'
  and xtype='U') and name='SoBomID')
ALTER TABLE [dbo].[InOutProduct]
ADD [SoBomID] [nvarchar] (50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='CycleTime')
ALTER TABLE [dbo].[InventoryBase]
ADD [CycleTime] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='Color')
ALTER TABLE [dbo].[InventoryBase]
ADD [Color] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='ApprovalDate')
ALTER TABLE [dbo].[InventoryBase]
ADD [ApprovalDate] DateTime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='mainnw')
ALTER TABLE [dbo].[MouldProductNr]
ADD [mainnw] float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='maingw')
ALTER TABLE [dbo].[MouldProductNr]
ADD [maingw] float DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='CycleTime')
ALTER TABLE [dbo].[MouldProductNr]
ADD [CycleTime] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='Color')
ALTER TABLE [dbo].[MouldProductNr]
ADD [Color] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='ApprovalDate')
ALTER TABLE [dbo].[MouldProductNr]
ADD [ApprovalDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdProduct'
  and xtype='U') and name='OKQty')
ALTER TABLE [dbo].[MouldProdProduct]
ADD [OKQty] int DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdProduct'
  and xtype='U') and name='BadQty')
ALTER TABLE [dbo].[MouldProdProduct]
ADD [BadQty] int DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdBase'
  and xtype='U') and name='OKQty')
ALTER TABLE [dbo].[MouldProdBase]
ADD [OKQty] int DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdBase'
  and xtype='U') and name='BadQty')
ALTER TABLE [dbo].[MouldProdBase]
ADD [BadQty] int DEFAULT 0
GO

if not exists(select * from sysobjects where name='InGoodsBase')
begin
CREATE TABLE [InGoodsBase] (
	[invseriesnr] [nvarchar] (13)  NOT NULL ,
	[invnr] [nvarchar] (20)  NULL ,
	[Ivdate] [smalldatetime] NOT NULL ,
	[PYCustomernr] [nvarchar] (20)  NOT NULL ,
	[RCCustomernr] [nvarchar] (20)  NULL ,
	[shipto] [nvarchar] (50)  NULL ,
	[phone] [nvarchar] (20)  NULL ,
	[attn_name] [nvarchar] (20)  NULL ,
	[invoicenr] [nvarchar] (13)  NULL ,
	[localamnt] [money] NULL ,
	[make] [nvarchar] (15)  NOT NULL ,
	[makeDate] [smalldatetime] NOT NULL ,
	[ttlcarton] [float] NULL ,
	[ttlgw] [float] NULL ,
	[ttlnw] [float] NULL ,
	[updateby] [nvarchar] (15)  NOT NULL ,
	[updatetime] [smalldatetime] NOT NULL ,
	[cmemo] [ntext]  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
end
GO


if not exists(select * from sysobjects where name='InGoodsProduct')
begin
CREATE TABLE [InGoodsProduct] (
	[invseriesnr] [nvarchar] (13)  NOT NULL ,
	[seriesnr] [nvarchar] (3)  NOT NULL ,
	[prodnr] [nvarchar] (20)  NOT NULL ,
	[cprodnr] [nvarchar] (20)  NULL ,
	[descript] [nvarchar] (50)  NULL ,
	[whousenr] [nvarchar] (4)  NULL ,
	[unit] [nvarchar] (6)  NULL ,
	[qty] [float] NULL ,
	[lprice] [money] NULL ,
	[amount] [money] NULL ,
	[esddate] [smalldatetime] NULL ,
	[TtlBox] [float] NULL ,
	[gw] [float] NULL ,
	[nw] [float] NULL ,
	[Cmemo] [ntext]  NULL ,
	[spec] [nvarchar] (50)  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
end
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdBase'
  and xtype='U') and name='Dep_No')
ALTER TABLE [dbo].[MouldProdBase]
ADD [Dep_No] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProdProduct'
  and xtype='U') and name='Ton')
ALTER TABLE [dbo].[MouldProdProduct]
ADD [Ton] nvarchar(20)
GO

if not exists(select * from sysobjects where name='InGoodsBackBase')
begin
CREATE TABLE [InGoodsBackBase] (
	[invseriesnr] [nvarchar] (13)   NOT NULL ,
	[invnr] [nvarchar] (20)   NULL ,
	[Ivdate] [smalldatetime] NOT NULL ,
	[PYCustomernr] [nvarchar] (20)   NOT NULL ,
	[RCCustomernr] [nvarchar] (20)   NULL ,
	[shipto] [nvarchar] (50)   NULL ,
	[phone] [nvarchar] (20)   NULL ,
	[attn_name] [nvarchar] (20)   NULL ,
	[invoicenr] [nvarchar] (13)   NULL ,
	[localamnt] [money] NULL ,
	[make] [nvarchar] (15)   NOT NULL ,
	[makeDate] [smalldatetime] NOT NULL ,
	[ttlcarton] [float] NULL ,
	[ttlgw] [float] NULL ,
	[ttlnw] [float] NULL ,
	[updateby] [nvarchar] (15)   NOT NULL ,
	[updatetime] [smalldatetime] NOT NULL ,
	[cmemo] [ntext]   NULL 
) 
end
GO

if not exists(select * from sysobjects where name='InGoodsBackProduct')
begin
CREATE TABLE [InGoodsBackProduct] (
	[invseriesnr] [nvarchar] (13)   NOT NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[prodnr] [nvarchar] (20)   NOT NULL ,
	[cprodnr] [nvarchar] (20)   NULL ,
	[descript] [nvarchar] (50)   NULL ,
	[whousenr] [nvarchar] (4)   NULL ,
	[unit] [nvarchar] (6)   NULL ,
	[qty] [float] NULL ,
	[lprice] [money] NULL ,
	[amount] [money] NULL ,
	[esddate] [smalldatetime] NULL ,
	[TtlBox] [float] NULL ,
	[gw] [float] NULL ,
	[nw] [float] NULL ,
	[Cmemo] [ntext]   NULL ,
	[spec] [nvarchar] (50)   NULL 
) 
end
GO

if not exists(select * from sysobjects where name='Plastic_Price')
Create table Plastic_Price
(
 Invseriesnr varchar(13),
 Invnr varchar(20),
 MakeId varchar(20),
 MakeDate datetime,
 UpdateId varchar(20),
 UpdateDate datetime,
 PartNetWeight float,
 PartName varchar(50),
 PartNumber varchar(50),
 Weight float, 
 Material varchar(50),
 MaterialPrice1 float,
 MaterialCharge float,
 AltMat1 varchar(50),
 MaterialPrice2 float,
 RecyclingMaterial float,
 UnitCostMaterial float,
 AltMat2 float, 
 Cavities float,
 GateWeight float,
 Mould1 varchar(50),
 Mould2 varchar(50), 
 MachineCode varchar(50),
 ClampingForce varchar(50),
 CycleTime float,
 MachineHourlyCost float,
 ShotPrice float,
 MouldingUnitPrice float, 
 QualityControl float,
 EmployeeNumber float,
 HourPerShift float,
 LaterProcess1 varchar(50),
 Employees1 float,
 Shifts1 float,
 LaterProcess2 varchar(50),
 Employees2 float,
 Shifts2 float,
 LaterProcess3 varchar(50),
 Employees3 float,
 Shifts3 float,
 LaterProcess4 varchar(50),
 Employees4 float,
 Shifts4 float,
 LaterProcess5 varchar(50),
 Employees5 float,
 Shifts5 float,
 LaterProcess6 varchar(50),
 Employees6 float,
 Shifts6 float,
 HourlyCost float,
 WorkerUnitPrice float, 
 PcsBox1 float,
 BoxPrice1 float,
 PcsBox2 float,
 BoxPrice2 float,
 PcsBox3 float,
 BoxPrice3 float,
 PackagingUnitPrice float, 
 TransportCharge float,
 Surcharge float, 
 CostRmb float,
 CostHk float,
 CostUs float,
 CostEuro float,
 PriceRmb float,
 PriceHk float,
 PriceUs float,
 PriceEuro float,
 Provider nvarchar(50)
)
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='ProjectNr')ALTER TABLE [dbo].[InventoryBase]
ADD [ProjectNr] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='ProjectNr')
ALTER TABLE [dbo].[MouldProductNr]
ADD [ProjectNr] nvarchar(50)
GO

if not exists(select * from sysobjects where name='MouldProject')
CREATE TABLE [dbo].[MouldProject] (
  [ProjectNr] nvarchar(50) NOT NULL,
  CONSTRAINT [PK_MouldProject] PRIMARY KEY CLUSTERED ([ProjectNr])
)
ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='ProviderProdNr')
ALTER TABLE [dbo].[MouldBase]
ADD [ProviderProdNr] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='remark')
ALTER TABLE [dbo].[MouldBase]
ADD [remark] text
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='AutoCalc')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [AutoCalc] bit DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='ProjectDesc')
ALTER TABLE [dbo].[InventoryBase]
ADD [ProjectDesc] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProject'
  and xtype='U') and name='ProjectDesc')
ALTER TABLE [dbo].[MouldProject]
ADD [ProjectDesc] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='ProjectDesc')
ALTER TABLE [dbo].[MouldBase]
ADD [ProjectDesc] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Plastic_Price'
  and xtype='U') and name='Provider')
ALTER TABLE [dbo].[Plastic_Price]
ADD [Provider] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProductNr'
  and xtype='U') and name='ProjectDesc')
ALTER TABLE [dbo].[MouldProductNr]
ADD [ProjectDesc] nvarchar(50)
GO

if not exists(select * from sysobjects where name='SysInvClose')
begin
CREATE TABLE [dbo].[SysInvClose] (
  [YM] nvarchar(6) ,
  [Dep_No] nvarchar(4) ,
  [CloseBy] nvarchar(20) ,
  [CloseDate] datetime,
  [OpenBy] nvarchar(20),
  [OpenDate] datetime
)
ON [PRIMARY]
End
GO

if not exists(select * from syscolumns where id=
(select id from sysobjects where name='MouldStdProcessProduct'
  and xtype='U') and name='WorkPiece')
ALTER TABLE [dbo].[MouldStdProcessProduct]
ADD [WorkPiece] nvarchar(50)
GO

if not exists(select * from syscolumns where name='Object_Action' and id=(select id from sysobjects where name='objects'))
  alter table objects 
  add Object_Action nvarchar(50)
Go

/*系统资讯*/
update objects set object_action='actSysPara' where object_id=1002
update objects set object_action='actCodes' where object_id=1003
update objects set object_action='actUserManag' where object_id=1008
update objects set object_action='actSysLog' where object_id=1029
--小数设置DotCtrlFrm
update objects set object_action='actDotSet' where object_id=1030
/*基本资讯*/
update objects set object_action='actDepart' where object_id=1101
update objects set object_action='actPerson' where object_id=1103
update objects set object_action='actCustom' where object_id=1201
update objects set object_action='actProvider' where object_id=1301
update objects set object_action='actMldMachine' where object_id=9829
update objects set object_action='actPlasticPrice' where object_id=1104
/*库存资讯*/
update objects set object_action='actStockKind' where object_id=1401
update objects set object_action='actStockMain' where object_id=1402
update objects set object_action='actChgStockNr' where object_id=1403
/*模具制造*/
update objects set object_action='actMouldNr' where object_id=9826
update objects set object_action='actMouldBom' where object_id=9807
update objects set object_action='actWorkCn' where object_id=9808
update objects set object_action='actTechnics' where object_id=9809
update objects set object_action='actDesign' where object_id=9810
update objects set object_action='actPlan' where object_id=9811
update objects set object_action='actProcess' where object_id=9824

update objects set object_action='actChgTcsPrice' where object_id=9827
update objects set object_action='actProcess' where object_id=9828
update objects set object_action='actChgBomPrice' where object_id=9822
update objects set object_action='actCost' where object_id=9813
/*模具资讯*/
update objects set object_action='actMldKind' where object_id=9801
update objects set object_action='actMldBase' where object_id=9802
update objects set object_action='actMldProdnr' where object_id=9817
update objects set object_action='actChgMldNr' where object_id=9818
update objects set object_action='actChgMldStatus' where object_id=9825
/*模具使用*/
update objects set object_action='actMldProvide' where object_id=9803
update objects set object_action='actMldReceive' where object_id=9804
update objects set object_action='actMldOut' where object_id=9819
update objects set object_action='actMldIn' where object_id=9820
update objects set object_action='actMldMove' where object_id=9821
--模具满穴率MouldProdFrm
update objects set object_action='actMouldContent' where object_id=9828
/*模具维护*/
update objects set object_action='actMangleSet' where object_id=9814
update objects set object_action='actRepairSet' where object_id=9815
update objects set object_action='actVindicate' where object_id=9823
/*报表*/
update objects set object_action='actRptBom' where object_id=9915
update objects set object_action='actRptPlan' where object_id=9917
update objects set object_action='actRptMo' where object_id=9927
update objects set object_action='actRptProcess' where object_id=9920
update objects set object_action='actRptNovel' where object_id=9919
update objects set object_action='actRptCost' where object_id=9916
update objects set object_action='actRptCostDtl' where object_id=9925
update objects set object_action='actRpthour' where object_id=9923
--模具生产计划总表ProcessTotalRptFrm
update objects set object_action='actRptTtlPlan' where object_id=9930
--模具生产统计表frmMouldScheduleNewRpt
update objects set object_action='actRptTtlMo' where object_id=9931
--模具生产进度分析表MouldScheduleAnalyseRptFrm
update objects set object_action='actRptTtlPrs' where object_id=9932
update objects set object_action='actRptHis' where object_id=9901
update objects set object_action='actRptState' where object_id=9904
update objects set object_action='actRptPrj' where object_id=9908
update objects set object_action='actRptOwner' where object_id=9905
update objects set object_action='actRptSlack' where object_id=9928
update objects set object_action='actRptPrd' where object_id=9902
update objects set object_action='actRptMldOut' where object_id=9921
update objects set object_action='actRptMldLst' where object_id=9910
update objects set object_action='actRptRcv' where object_id=9903
update objects set object_action='actRptMldIn' where object_id=9922
update objects set object_action='actRptMldIO' where object_id=9907
update objects set object_action='actRptMldMove' where object_id=9924
--模具满穴率报表MouldProdRptFrm
update objects set object_action='actMouldContentRpt' where object_id=9933
update objects set object_action='actRptMangle' where object_id=9913
update objects set object_action='actRptRepair' where object_id=9914
update objects set object_action='actRptVindicate' where object_id=9926
update objects set object_action='actProcessLoadRpt' where object_id=9934
update objects set object_action='actMouldScheduleStatusRpt' where object_id=9935
update objects set object_action='actMouldScheduleWTReachRpt' where object_id=9936
update objects set object_action='actRptProcessBom' where object_id=9937
Go

if not exists(select * from sysobjects where name='WorkCenterMn' and xtype='U')
CREATE TABLE [WorkCenterMn] (
	[WorkCnNr] [nvarchar] (13) NOT NULL ,
	[MachinNr] [nvarchar] (10) NOT NULL ,
	CONSTRAINT [IX_WorkCenterMn] UNIQUE  NONCLUSTERED
	(
		[WorkCnNr],
		[MachinNr]
	)  ON [PRIMARY]
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='WorkCenter'
  and xtype='U') and name='ISMachine')
ALTER TABLE [dbo].[WorkCenter]
ADD [ISMachine] bit DEFAULT 0
GO

if not exists(select * from sysobjects where name='MouldScheduleMachine')
CREATE TABLE [MouldScheduleMachine] (
  [ItemID] nvarchar(50) NOT NULL,
  [InvSeriesNr] nvarchar(13),
  [SeriesNr] nvarchar(3),
  [ItemNr] nvarchar(3),
  [WorkCenterNr] nvarchar(20),
  [MachinNr] nvarchar(10),
  [InstDays] float(20) DEFAULT 0.0,
  [P_NO] nvarchar(20),
  [SoProcessID] nvarchar(50),
  PRIMARY KEY CLUSTERED ([ItemID])
)
GO

if exists(select * from sysindexes where name='PK_MouldProcessProduct')
Alter Table MouldProcessProduct
DROP PK_MouldProcessProduct 
GO

if not exists(select * from syscolumns where name='NavToolsEnuFile' and id=(select id from sysobjects where name='version'))
  alter table version add NavToolsEnuFile image
Go

if not exists(select * from syscolumns where name='NavToolsChsFile' and id=(select id from sysobjects where name='version'))
  alter table version add NavToolsChsFile image
Go

if not exists(select * from syscolumns where name='NavToolsChtFile' and id=(select id from sysobjects where name='version'))
  alter table version add NavToolsChtFile image
Go

if exists(select * from sysindexes where name='PK_MouldStdProcessProduct')
Alter Table MouldStdProcessProduct
DROP PK_MouldStdProcessProduct 
GO

if not exists(select * from syscolumns where name='EnuFile' and id=(select id from sysobjects where name='version'))
  
alter table version add EnuFile image

Go



if not exists(select * from syscolumns where name='UpdateEnuFile' and id=(select id from sysobjects where name='version'))
  
alter table version add UpdateEnuFile image

Go

Delete From groupprivilege Where isnull(Group_ID,0)=0 
Go

Delete From Userprivilege Where isnull(User_ID,0)=0 
Go

Delete From GroupObject Where isnull(Group_ID,0)=0 
Go

alter table mouldvindicate
alter column projectnr nvarchar(8)
Go

Alter Table Users
	Alter Column User_Code nVarChar(50)
Go

Alter Table Users
	Alter Column [User_Name] nVarChar(50)
Go

Alter Table OperLog
	Alter Column OperKey nVarChar(50)
Go

Alter Table OperLog
	Alter Column OperObject nVarChar(50)
Go

Alter Table OperLog
	Alter Column OperMethod nVarChar(50)
Go

Alter Table OperLog
	Alter Column OperName nVarChar(50)
Go

Alter Table OperLog
	Alter Column OperPcName nVarChar(50)
Go

if not exists(select * from syscolumns where name='Stoped' and id=(select id from sysobjects where name='empperson'))
  alter table empperson
  add Stoped int  
Go

update empperson set stoped=0 where stoped is null
Go

if not exists(select * from syscolumns where name='Stoped' and id=(select id from sysobjects where name='WorkCenter'))
  alter table WorkCenter
  add Stoped int  
Go

update WorkCenter set stoped=0 where stoped is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='WorkCenter'
  and xtype='U') and name='UnitCapacity')
ALTER TABLE [dbo].[WorkCenter]
ADD [UnitCapacity] float DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='WorkCenter'
  and xtype='U') and name='TotalCapacity')
ALTER TABLE [dbo].[WorkCenter]
ADD [TotalCapacity] float DEFAULT 0
GO

Update WorkCenter
	Set IsMachine=0
	Where Isnull(IsMachine,0)=0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='WorkTimeAllot')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [WorkTimeAllot] nVarChar(20) Default '0'
GO

Update MouldProcessProduct
	Set WorkTimeAllot='0'
	Where WorkTimeAllot is null
Go

if not exists(select * from sysobjects where name='MouldMachine')
  create table MouldMachine(
    zucode nvarchar(50),
    znamec nvarchar(60),
    ton nvarchar(20),
    unitcapacity float,
    stoped int,
    memo nvarchar(200)
  )
Go

if not exists(select * from syscolumns where name='CycleTime' and id=(select id from sysobjects where name='mouldprodproduct'))
  alter table mouldprodproduct
  add CycleTime nvarchar(50)
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='CompanyInfo'
  and xtype='U') and name='IsCurrDayReport')
ALTER TABLE [dbo].[CompanyInfo]
ADD [IsCurrDayReport] bit DEFAULT 0
GO

Update CompanyInfo
	Set IsCurrDayReport=0
	Where isnull(IsCurrDayReport,0)=0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='MakeYear')
ALTER TABLE [dbo].[MouldBase]
ADD [MakeYear] nvarchar(4)
GO

Update MouldBase
	Set MakeYear=Year(CreateDate)
Go

if not exists(select * from syscolumns where name='RecNo' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add RecNo nvarchar(10)
Go

if not exists(select * from syscolumns where name='BomInvseriesnr' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add BomInvseriesnr nvarchar(13)
Go


if not exists(select * from sysobjects where name='MouldProcessBomBase')
  create table MouldProcessBomBase(Invseriesnr nvarchar(13),BomInvseriesnr nvarchar(13),
    make nvarchar(15),makedate datetime,updateby nvarchar(15),updatetime datetime,
    Prodnr nvarchar(30),invnr nvarchar(20),Memo nvarchar(250))
Go

if not exists(select * from syscolumns where name='Memo' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Memo nvarchar(200)
Go

ALTER TABLE [dbo].[MouldProcessBOM]
ALTER COLUMN [MainUnit] nvarchar(10)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='IsKey')
ALTER TABLE [dbo].[MouldTechnics]
ADD [IsKey] bit DEFAULT 0
Go

Update MouldTechnics 
	Set IsKey=0 
	Where IsKey is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='AutoCalc')
ALTER TABLE [dbo].[MouldTechnics]
ADD [AutoCalc] bit DEFAULT 0
Go

Update MouldTechnics 
	Set AutoCalc=0 
	Where AutoCalc is null
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='EnglishDesc')
ALTER TABLE [dbo].[MouldTechnics]
ADD [EnglishDesc] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='WTypeE')
ALTER TABLE [dbo].[MouldTechnics]
ADD [WTypeE] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='WTypeC')
ALTER TABLE [dbo].[MouldTechnics]
ADD [WTypeC] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='IsKey')
ALTER TABLE [dbo].[MouldTechnics]
ADD [IsKey] bit DEFAULT 0
Go

Update MouldTechnics 
	Set IsKey=0 
	Where IsKey is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='AutoCalc')
ALTER TABLE [dbo].[MouldTechnics]
ADD [AutoCalc] bit DEFAULT 0
Go

Update MouldTechnics 
	Set AutoCalc=0 
	Where AutoCalc is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='EnglishDesc')
ALTER TABLE [dbo].[MouldTechnics]
ADD [EnglishDesc] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='WTypeE')
ALTER TABLE [dbo].[MouldTechnics]
ADD [WTypeE] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='WTypeC')
ALTER TABLE [dbo].[MouldTechnics]
ADD [WTypeC] nVarChar(100) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='WorkCenter'
  and xtype='U') and name='WorkTimeAllot')
ALTER TABLE [dbo].[WorkCenter]
ADD [WorkTimeAllot] nVarChar(20) Default '0'
GO

Update WorkCenter 
	Set WorkTimeAllot='0' 
	Where WorkTimeAllot is null
Go

alter table mouldprocessbase
alter column descript nvarchar(60)
Go

if not exists(select * from syscolumns where name='linkman' and id=(select id from sysobjects where name='customerbase'))
alter table customerbase
add linkman nvarchar(100)
Go

if not exists(select * from syscolumns where name='linkman' and id=(select id from sysobjects where name='providerbase'))
alter table providerbase
add linkman nvarchar(100)
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='Groups'
  and xtype='U') and name='IsCtrlTime')
ALTER TABLE [dbo].[Groups]
ADD [IsCtrlTime] bit DEFAULT 0
Go

Update Groups 
	Set IsCtrlTime=0 
	Where IsCtrlTime is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Groups'
  and xtype='U') and name='HushTime')
ALTER TABLE [dbo].[Groups]
ADD [HushTime] int DEFAULT 0
Go

Update Groups 
	Set HushTime=0 
	Where HushTime is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Groups'
  and xtype='U') and name='WorkTime')
ALTER TABLE [dbo].[Groups]
ADD [WorkTime] int DEFAULT 0
Go

Update Groups 
	Set WorkTime=0 
	Where WorkTime is null
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='Users'
  and xtype='U') and name='IsCtrlTime')
ALTER TABLE [dbo].[Users]
ADD [IsCtrlTime] bit DEFAULT 0
Go

Update Users 
	Set IsCtrlTime=0 
	Where IsCtrlTime is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Users'
  and xtype='U') and name='HushTime')
ALTER TABLE [dbo].[Users]
ADD [HushTime] int DEFAULT 0
Go

Update Users 
	Set HushTime=0 
	Where HushTime is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Users'
  and xtype='U') and name='WorkTime')
ALTER TABLE [dbo].[Users]
ADD [WorkTime] int DEFAULT 0
Go

Update Users 
	Set WorkTime=0 
	Where WorkTime is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdProcessProduct'
  and xtype='U') and name='PlanStartDay')
ALTER TABLE [dbo].[MouldStdProcessProduct]
ADD [PlanStartDay] Int Default 0
GO

Update MouldStdProcessProduct 
	Set PlanStartDay=0 
	Where PlanStartDay is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdProcessProduct'
  and xtype='U') and name='PlanFinishDay')
ALTER TABLE [dbo].[MouldStdProcessProduct]
ADD [PlanFinishDay] Int Default 0
GO

Update MouldStdProcessProduct 
	Set PlanFinishDay=0 
	Where PlanFinishDay is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBase'
  and xtype='U') and name='FuBanDate')
ALTER TABLE [dbo].[MouldProcessBase]
ADD [FuBanDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='PlanStartDay')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [PlanStartDay] Int Default 0
GO

Update MouldProcessProduct 
	Set PlanStartDay=0 
	Where PlanStartDay is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='PlanFinishDay')
ALTER TABLE [dbo].[MouldProcessProduct]
ADD [PlanFinishDay] Int Default 0
GO

Update MouldProcessProduct 
	Set PlanFinishDay=0 
	Where PlanFinishDay is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleDetailNew'
  and xtype='U') and name='FinishQty')
ALTER TABLE [dbo].[MouldScheduleDetailNew]
ADD [FinishQty] float Default 0
GO

Update MouldScheduleDetailNew 
	Set FinishQty=0 
	Where FinishQty is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleDetailNew'
  and xtype='U') and name='BadQty')
ALTER TABLE [dbo].[MouldScheduleDetailNew]
ADD [BadQty] float Default 0
GO

Update MouldScheduleDetailNew 
	Set BadQty=0 
	Where BadQty is null
Go

if not exists(select * from sysobjects where name='FactoryCalendar')
CREATE TABLE [FactoryCalendar] (
  [ItemID] nvarchar(50) NOT NULL,
  [YMD] nvarchar(8),
  [Date] DateTime,
  [Week] Int,
  [Lunar] nvarchar(20),
  [ISBreak] Bit Default 0,
  PRIMARY KEY CLUSTERED ([ItemID])
)
GO

if not exists(select * from sysobjects where name='CalendarWeek')
CREATE TABLE [CalendarWeek] (
  [ItemID] nvarchar(50) NOT NULL,
  [Week0] Bit Default 0,
  [Week1] Bit Default 0,
  [Week2] Bit Default 0,
  [Week3] Bit Default 0,
  [Week4] Bit Default 0,
  [Week5] Bit Default 0,
  [Week6] Bit Default 0,
  PRIMARY KEY CLUSTERED ([ItemID])
)
GO

if not exists (Select * From CalendarWeek)
	Insert Into CalendarWeek(ItemID) Values(NewID())
GO

if not exists(select * from sysobjects where name='ChgProdnr')
  create table ChgProdnr(ProdnrName nvarchar(30),TblName nvarchar(30),
    FldName nvarchar(30),FldDescriptName nvarchar(30),IsOwner Int)
Go

if not exists(select * from syscolumns where name='Cspememo' and id=(select id from sysobjects where name='bomimport'))
  alter table bomimport 
  add Cspememo ntext
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BomProduct'
  and xtype='U') and name='BomID')
ALTER TABLE [dbo].[BomProduct]
ADD [BomID] [nvarchar] (50) Default Newid()
GO

Update BomProduct
	Set BomID= Newid()
	Where BomID is Null
Go

alter table bomimport
alter column Prodnr nvarchar(30)
Go

if not exists(select * from syscolumns where name='Note' and id=(select id from sysobjects where name='MouldProdProduct'))
  alter table MouldProdProduct
  add Note nvarchar(200)
Go

if not exists(select * from syscolumns where name='kind' and id=(select id from sysobjects where name='mouldbase'))
  alter table mouldbase
  add kind nvarchar(10)
Go

if not exists(select * from syscolumns where name='BomId' and id=(select id from sysobjects where name='bomimport'))
  alter table bomimport
  add BomId nvarchar(50)
Go


if not exists(select * from syscolumns where name='BomId' and id=(select id from sysobjects where name='bomimport'))
  alter table bomimport
  add BomId nvarchar(50)
Go

if not exists(select * from syscolumns where name='physicalqty' and id=(select id from sysobjects where name='mouldcostproduct'))
  alter table mouldcostproduct
  add physicalqty float
Go

if not exists(select * from syscolumns where name='unicost' and id=(select id from sysobjects where name='mouldcostproduct'))
  alter table mouldcostproduct
  add unicost money
Go

if not exists(select * from syscolumns where name='CMemo' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct
  add CMemo Text
Go


if not exists(select * from syscolumns where name='InstDays' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct
  add InstDays float Default 0.0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBase'
  and xtype='U') and name='PlanStartDate')
ALTER TABLE [dbo].[MouldProcessBase]
ADD [PlanStartDate] datetime
GO

Update MouldProcessBase
Set SchedulePercent=0
Where SchedulePercent is null
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select id from sysobjects where name='mouldprocessproduct'))
  alter table mouldprocessproduct
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='PictureNo' and id=(select id from sysobjects where name='mouldprocessproduct'))
  alter table mouldprocessproduct
  add PictureNo nvarchar(50)
Go

if not exists(select * from syscolumns where name='ProcType' and id=(select id from sysobjects where name='mouldschedulemasternew'))
  alter table mouldschedulemasternew 
  add ProcType nvarchar(1) default '1'
Go

Update mouldschedulemasternew
Set ProcType='1'
Where Isnull(ProcType,'')=''
Go

if not exists(select * from syscolumns where name='amt' and id=(select id from sysobjects where name='mouldscheduledetailnew'))
  alter table mouldscheduledetailnew 
  add amt float default 0
Go
if not exists(select * from syscolumns where name='amt' and id=(select id from sysobjects where name='mouldscheduledetailnew'))
  alter table mouldscheduledetailnew 
  add amt float default 0
Go

Update mouldscheduledetailnew
Set amt='1'
Where Isnull(amt,0)=0
Go

if not exists(select * from syscolumns where name='RepeatHour' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add RepeatHour float default 0
Go

Update MouldProcessProduct
Set RepeatHour='0'
Where Isnull(RepeatHour,0)=0
Go

if not exists(select * from syscolumns where name='RepeatPrice' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add RepeatPrice float default 0
Go

Update MouldProcessProduct
Set RepeatPrice=0
Where Isnull(RepeatPrice,0)=0
Go

if not exists(select * from syscolumns where name='OverHour' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add OverHour float default 0
Go

Update MouldScheduleDetailNew
Set OverHour=0
Where Isnull(OverHour,0)=0
Go

if not exists(select * from syscolumns where name='RepeatOverHour' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add RepeatOverHour float default 0
Go

Update MouldProcessProduct
Set RepeatOverHour=0
Where Isnull(RepeatOverHour,0)=0
Go

if not exists(select * from syscolumns where name='RepeatOverPrice' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add RepeatOverPrice float default 0
Go

Update MouldProcessProduct
Set RepeatOverPrice=0
Where Isnull(RepeatOverPrice,0)=0
Go

if not exists(select * from sysobjects where name='MaterialImport')
  create table MaterialImport
   (RecNo nvarchar(10),
    Prodnr nvarchar(30), 
    Masternr nvarchar(20), 
    Descript nvarchar(40), 
    Spec nvarchar(50), 
    MasternrUnit nvarchar(10), 
    UnitCost nvarchar(10), 
    MainQty nvarchar(10), 
    cspememo ntext,
    BomId nvarchar(50))
Go

if not exists(select * from syscolumns where name='RecNo' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom
  add RecNo int
Go

if not exists(select * from syscolumns where name='NeedSign' and id=(select id  from sysobjects where name='companyinfo'))
  alter table companyinfo
  add NeedSign bit 
Go

update companyinfo set NeedSign=0 where NeedSign is null
Go

if not exists(select * from syscolumns where name='StatusId' and id=(
  select id from sysobjects where name='mouldprocessbase'))
  alter table mouldprocessbase
  add StatusId int 
Go

update mouldprocessbase set StatusId=0 where StatusId is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='PlanFinishDate')
ALTER TABLE [dbo].[MouldBase]
ADD [PlanFinishDate] datetime
GO

if not exists(select * from syscolumns where name='OverInstHour' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add OverInstHour float default 0
Go

Update MouldProcessProduct
Set OverInstHour=0
Where Isnull(OverInstHour,0)=0
Go

if not exists(select * from syscolumns where name='OverInstHour' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add OverInstHour float default 0
Go

Update MouldScheduleDetailNew
Set OverInstHour=0
Where Isnull(OverInstHour,0)=0
Go

if not exists(select * from syscolumns where name='OverInstHour' and id=(select id from sysobjects where name='MouldScheduleMachine'))
  alter table MouldScheduleMachine 
  add OverInstHour float default 0
Go

Update MouldScheduleMachine
Set OverInstHour=0
Where Isnull(OverInstHour,0)=0
Go

if not exists(select * from syscolumns where name='OverInstHour' and id=(select id from sysobjects where name='MouldScheduleDetail'))
  alter table MouldScheduleDetail 
  add OverInstHour float default 0
Go

Update MouldScheduleDetail
Set OverInstHour=0
Where Isnull(OverInstHour,0)=0
Go

if not exists(select * from syscolumns where name='OverInstHour' and id=(select id from sysobjects where name='MouldScheduleItem'))
  alter table MouldScheduleItem 
  add OverInstHour float default 0
Go

Update MouldScheduleItem
Set OverInstHour=0
Where Isnull(OverInstHour,0)=0
Go

if not exists(select * from syscolumns where name='RepeatReason' and id=(select id from sysobjects where name='mouldscheduledetailnew'))
  alter table mouldscheduledetailnew 
  add RepeatReason nvarchar(200)
Go

if not exists(select * from sysobjects where name='MouldScheduleRepeat')
  create table MouldScheduleRepeat
    (RepeatItem nvarchar(50),
RepeatTimes int,
     SoProcessId nvarchar(50),
     InstStartTime datetime,
     InstFinishTime datetime,
     RepeatHour float default 0,
     RepeatOverHour float default 0,
SchedulePercent float default 0,
RepeatReason nvarchar(200))
Go

if not exists(select * from sysobjects where name='LEDSet' and xtype='U')
CREATE TABLE [LEDSet] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[IP] [nvarchar] (15) NULL ,
	[Name] [nvarchar] (50) NULL ,
	[IP2] [nvarchar] (15) NULL ,
	[Name2] [nvarchar] (50) NULL ,
	[Width1] [int] NULL CONSTRAINT [DF_LEDSet_Width1] DEFAULT (384),
	[Height1] [int] NULL CONSTRAINT [DF_LEDSet_Height1] DEFAULT (224),
	[Width2] [int] NULL CONSTRAINT [DF_LEDSet_Width2] DEFAULT (0),
	[Height2] [int] NULL CONSTRAINT [DF_LEDSet_Height2] DEFAULT (0),
	[Color] [int] NULL CONSTRAINT [DF_LEDSet_Color] DEFAULT (0),
	[Bright] [smallInt] NULL CONSTRAINT [DF_LEDSet_Bright] DEFAULT (1),
	[Bright2] [smallInt] NULL CONSTRAINT [DF_LEDSet_Bright2] DEFAULT (1),
	[FullScreen] [bit] NULL CONSTRAINT [DF_LEDSet_FullScreen] DEFAULT (1),
	[InMode1] [int] NULL CONSTRAINT [DF_LEDSet_InMode1] DEFAULT (1),
	[MoveSpeed1] [int] NULL CONSTRAINT [DF_LEDSet_MoveSpeed1] DEFAULT (8),
	[StopTime1] [int] NULL CONSTRAINT [DF_LEDSet_StopTime1] DEFAULT (10),
	[InMode2] [int] NULL CONSTRAINT [DF_LEDSet_InMode2] DEFAULT (1),
	[MoveSpeed2] [int] NULL CONSTRAINT [DF_LEDSet_MoveSpeed2] DEFAULT (8),
	[StopTime2] [int] NULL CONSTRAINT [DF_LEDSet_StopTime2] DEFAULT (10),
	[IsSelect] [bit] NULL CONSTRAINT [DF_LEDSet_IsSelect] DEFAULT (1),
	CONSTRAINT [PK_LEDSet] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Update MouldScheduleDetailNew
	Set OverHour=OverInstHour
	Where OverHour=0 and OverInstHour>0
Go

if exists(select * from sysobjects where name='sp_ReadMakeRepeat')
  drop procedure sp_ReadMakeRepeat
Go

create procedure sp_ReadMakeRepeat
@soprocessid varchar(50),
@workdate datetime
As
declare @inststarttime datetime
declare @instfinishtime datetime
declare @schedulepercent float
declare @repeatreason nvarchar(200)
declare @maxtimes int
declare @mintimes int
declare @maxschedulepercent float
declare @minschedulepercent float
begin
  /*
  根据@soprocessid和@workdate从表mouldschedulerepeat中返回inststarttime、instfinishtime、schedulepercent、repeatreason
  基本原则同sp_WriteMakeRepeat
  */
  if not exists(select * from mouldschedulerepeat where soprocessid=@soprocessid)
    select @inststarttime=null,@instfinishtime=null,@schedulepercent=0,@repeatreason=''
  else
  begin
    select @maxtimes=max(RepeatTimes) 
      from MouldScheduleRepeat where soprocessid=@soprocessid
    if exists(select top 1 * from MouldScheduleRepeat where soprocessid=@soprocessid 
      and RepeatTimes=@maxtimes and @workdate>=inststarttime)
    begin
      select @maxschedulepercent=schedulepercent from MouldScheduleRepeat
      	where soprocessid=@soprocessid and RepeatTimes=@maxtimes
      if @maxschedulepercent=100
        select @inststarttime=null,@instfinishtime=null,@schedulepercent=0,@repeatreason=''
      else
        select @inststarttime=inststarttime,@instfinishtime=instfinishtime,
          @schedulepercent=schedulepercent,@repeatreason=repeatreason
        from MouldScheduleRepeat 
        where soprocessid=@soprocessid and RepeatTimes=@maxtimes 
    end
    else
    begin
      select @mintimes=1
      if exists(select top 1 * from MouldScheduleRepeat where soprocessid=@soprocessid 
        and RepeatTimes=@mintimes and @workdate<inststarttime)
        select @inststarttime=null,@instfinishtime=null,@schedulepercent=0,@repeatreason=''
      else
      begin
        select @mintimes=(select top 1 repeattimes from MouldScheduleRepeat where soprocessid=@soprocessid
          and @workdate>=inststarttime order by inststarttime desc)
        select @maxtimes=(select top 1 repeattimes from MouldScheduleRepeat where soprocessid=@soprocessid
          and @workdate<=inststarttime order by inststarttime) 
        if @maxtimes>@mintimes
        begin
          select @minschedulepercent=schedulepercent from MouldScheduleRepeat
      	    where soprocessid=@soprocessid and RepeatTimes=@mintimes 
      	  if @minschedulepercent=100
     	    select @inststarttime=null,@instfinishtime=null,@schedulepercent=0,@repeatreason=''  
      	  else
            select @inststarttime=inststarttime,@instfinishtime=instfinishtime,
              @schedulepercent=schedulepercent,@repeatreason=repeatreason
            from MouldScheduleRepeat
      	    where soprocessid=@soprocessid and RepeatTimes=@mintimes
      	end       	
      end 
    end
  end
  select @inststarttime inststarttime,@instfinishtime instfinishtime,
    @schedulepercent schedulepercent,@repeatreason repeatreason
end
Go

if not exists(select * from syscolumns where name='WorkPieceNo' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add WorkPieceNo nVarChar(50)
Go

if not exists(select * from syscolumns where name='WorkPieceNo' and id=(select id from sysobjects where name='MouldStdProcessProduct'))
  alter table MouldStdProcessProduct 
  add WorkPieceNo nVarChar(50)
Go

if not exists(select * from sysobjects where name='TmpMouldScheduleRepeat')
  create table TmpMouldScheduleRepeat
    (Flag int,
     WorkDate datetime,
     SoProcessId nvarchar(50),
     InstStartTime datetime,
     InstFinishTime datetime,
     RepeatHour float default 0,
     RepeatOverHour float default 0,
     SchedulePercent float default 0,
     RepeatReason nvarchar(200))
Go

if not exists(select * from syscolumns where name='ID' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add ID Int IDENTITY (1, 1) NOT NULL
Go

if not exists(select * from syscolumns where name='InChargeOf' and id=(select id from sysobjects where name='MouldBase'))
  alter table MouldBase 
  add InChargeOf nVarChar(20)
Go

if not exists(select * from syscolumns where name='Designer' and id=(select id from sysobjects where name='MouldBase'))
  alter table MouldBase 
  add Designer nVarChar(20)
Go

Alter Table MouldScheduleMachine
	Alter Column MachinNr nVarChar(20)
Go

if exists(select * from sysobjects where name='sp_WriteMakeRepeat')
  drop procedure sp_WriteMakeRepeat
Go

create procedure sp_WriteMakeRepeat
As
declare @Invseriesnr nvarchar(20)
declare @soprocessid nvarchar(50)
declare @inststarttime datetime
declare @instfinishtime datetime
declare @workdate datetime
declare @instdays float
declare @overhour float
declare @schedulepercent float
declare @repeatreason nvarchar(200)
declare @pretimes int
declare @nextimes int
declare @curtimes int
declare @maxtimes int
declare @mintimes int
declare @maxschedulepercent float
declare @minschedulepercent float
begin
  /*
  删除MouldScheduleRepeat表中soprocessid存在于tmpMouldScheduleRepeat中的记录
  根据tmpMouldScheduleRepeat中的soprocessid筛选所有满足条件的invseriesnr
  以soprocessid为关键字，根据repeattimes回写
    inststarttime、instfinishtime、repeathour、repeatoverhour、schedulepercent、repeatreason
  一、没有soprocessid的记录
      直接插入，次数为1
  二、有soprocessid的记录
    1：workdate大于最大次数的开始日期
      1.1：最大次数已完成，将新增一条，次数=最大次数+1
      1.2：最大次数未完成，将直接更新最大次数数据
    2：workdate小于最小次数的开始日期，将新增一条，次数=1，更新后面次数+1
    3：workdate介于两个次数之间的开始日期
      3.1：前一次数已完成，将新增一条，次数=前一次数+1，更新后面次数+1
      3.2：前一次数未完成，将直接更新前一次数数据
  */	
  delete from MouldScheduleRepeat where soprocessid in 
   (select soprocessid from tmpMouldScheduleRepeat)
  declare head cursor for select distinct invseriesnr from mouldscheduledetailnew
    where soprocessid in (select soprocessid from tmpMouldScheduleRepeat)
    and invseriesnr in (select invseriesnr from mouldschedulemasternew where proctype=3)
    order by invseriesnr
  open head
  fetch next from head into @invseriesnr
  while @@fetch_status=0
  begin
    declare detail cursor for select soprocessid from mouldscheduledetailnew
      where invseriesnr=@Invseriesnr and soprocessid in 
      (select soprocessid from tmpMouldScheduleRepeat)
    open detail
    fetch next from detail into @soprocessid
    while @@fetch_status=0
    begin
      select @inststarttime=inststarttime,@instfinishtime=instfinishtime,@workdate=workdate,@instdays=instdays,
        @overhour=overhour,@schedulepercent=schedulepercent,@repeatreason=repeatreason
      from mouldscheduledetailnew where invseriesnr=@Invseriesnr
        and soprocessid=@soprocessid
      /*没有soprocessid的记录*/
      if not exists(select * from MouldScheduleRepeat where soprocessid=@soprocessid)
      begin
        select @curtimes=1
        insert into MouldScheduleRepeat(RepeatItem,RepeatTimes,SoProcessId,InstStartTime,InstFinishTime,RepeatHour,
          RepeatOverHour,SchedulePercent,RepeatReason)
        values(newid(),@curtimes,@soprocessid,@inststarttime,@instfinishtime,@instdays,@overhour,@schedulepercent,@repeatreason)        
      end
      else
      begin
      	select @maxtimes=max(RepeatTimes) 
      	  from MouldScheduleRepeat where soprocessid=@soprocessid
        /*workdate大于最大次数的开始日期*/
        if exists(select top 1 * from MouldScheduleRepeat where soprocessid=@soprocessid 
           and RepeatTimes=@maxtimes and @workdate>=inststarttime)
        begin      	
      	  select @maxschedulepercent=schedulepercent from MouldScheduleRepeat
      	    where soprocessid=@soprocessid and RepeatTimes=@maxtimes
      	  /*最大次数已完成，将新增一条，次数=最大次数+1*/  
      	  if @maxschedulepercent=100
      	  begin
      	    select @curtimes=@maxtimes+1
      	    insert into MouldScheduleRepeat(RepeatItem,RepeatTimes,SoProcessId,InstStartTime,InstFinishTime,RepeatHour,
              RepeatOverHour,SchedulePercent,RepeatReason)
            values(newid(),@curtimes,@soprocessid,@inststarttime,@instfinishtime,@instdays,@overhour,@schedulepercent,@repeatreason)	
      	  end 
      	  else
      	  /*最大次数未完成，将直接更新最大次数数据*/
      	  begin
      	    update MouldScheduleRepeat set inststarttime=@inststarttime,instfinishtime=@instfinishtime,
      	      repeathour=isnull(repeathour,0)+@instdays,repeatoverhour=isnull(repeatoverhour,0)+@overhour,
              SchedulePercent=@SchedulePercent,repeatreason=@repeatreason
            where soprocessid=@soprocessid and repeattimes=@maxtimes	
      	  end 
      	end
      	else
      	begin
      	  select @mintimes=1
      	  /*workdate小于最小次数的开始日期*/  
      	  if exists(select top 1 * from MouldScheduleRepeat where soprocessid=@soprocessid 
            and RepeatTimes=@mintimes and @workdate<inststarttime)
          begin
            update MouldScheduleRepeat set RepeatTimes=RepeatTimes+1 where soprocessid=@soprocessid and RepeatTimes>=@mintimes	
            select @curtimes=1
            insert into MouldScheduleRepeat(RepeatItem,RepeatTimes,SoProcessId,InstStartTime,InstFinishTime,RepeatHour,
              RepeatOverHour,SchedulePercent,RepeatReason)
            values(newid(),@curtimes,@soprocessid,@inststarttime,@instfinishtime,@instdays,@overhour,@schedulepercent,@repeatreason)	
          end 
          else
          begin
            /*workdate介于两个次数之间的开始日期*/
            select @mintimes=(select top 1 repeattimes from MouldScheduleRepeat where soprocessid=@soprocessid
              and @workdate>=inststarttime order by inststarttime desc)
            select @maxtimes=(select top 1 repeattimes from MouldScheduleRepeat where soprocessid=@soprocessid
              and @workdate<=inststarttime order by inststarttime) 
            if @maxtimes>@mintimes
            begin
              select @minschedulepercent=schedulepercent from MouldScheduleRepeat
      	        where soprocessid=@soprocessid and RepeatTimes=@mintimes
      	      /*最小次数已完成*/  
      	      if @minschedulepercent=100
      	      begin
      	      	update MouldScheduleRepeat set RepeatTimes=RepeatTimes+1 where soprocessid=@soprocessid and RepeatTimes>=@mintimes+1	
                select @curtimes=@mintimes+1
                insert into MouldScheduleRepeat(RepeatItem,RepeatTimes,SoProcessId,InstStartTime,InstFinishTime,RepeatHour,
                  RepeatOverHour,SchedulePercent,RepeatReason)
                values(newid(),@curtimes,@soprocessid,@inststarttime,@instfinishtime,@instdays,@overhour,@schedulepercent,@repeatreason)	
      	      end  	
      	      else
      	      /*最小次数未完成*/  
      	      begin
      	      	update MouldScheduleRepeat set inststarttime=@inststarttime,instfinishtime=@instfinishtime,
      	          repeathour=isnull(repeathour,0)+@instdays,repeatoverhour=isnull(repeatoverhour,0)+@overhour,
                  SchedulePercent=@SchedulePercent,repeatreason=@repeatreason
                where soprocessid=@soprocessid and repeattimes=@mintimes
      	      end
            end    	
          end  	
      	end 
      end  	  
      fetch next from detail into @soprocessid
    end
    close detail
    deallocate detail
    fetch next from head into @invseriesnr
  end
  close head
  deallocate head
end
Go

if not exists(select * from syscolumns where name='IsNew' and id=(select id from sysobjects where name='MouldBase'))
  alter table MouldBase 
  add IsNew Bit Default 0
Go

Update MouldBase
	Set IsNew=1
	Where IsNew is Null
Go

Update MouldBase
	Set IsNew=1
	Where (State=5)
	Or (IsNew=1)
Go

Update MouldBase
	Set IsNew=0
	Where State<>5
Go

if not exists(select * from syscolumns where name='SMoInvseriesnr' and id= (select id from sysobjects where name='mouldbase'))
  alter table mouldbase add SMoInvseriesnr nvarchar(13)
Go

if not exists(select * from syscolumns where name='SMoDate' and id=(select id  from sysobjects where name='mouldbase'))
  alter table mouldbase add SMoDate datetime
Go

if not exists(select * from syscolumns where name='ProcessInvSeriesNr' and id= (select id from sysobjects where name='mouldbase'))
  alter table mouldbase add ProcessInvSeriesNr nvarchar(13)
Go

Update MouldBase
Set ProcessInvSeriesNr=b.InvSeriesNr
From MouldBase a,
(Select Min(InvSeriesNr) as InvSeriesNr,ProdNr
From MouldProcessBase
Group By ProdNr) b
Where isnull(a.ProcessInvSeriesNr,'')=''
and a.ProdNr=b.ProdNr
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select  id from sysobjects where name='bomproduct'))
  alter table bomproduct 
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select  id from sysobjects where name='bomimport'))
  alter table bomimport 
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select id from sysobjects where name='MouldProcessBOM'))
  alter table MouldProcessBOM 
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select id from sysobjects where name='MaterialImport'))
  alter table MaterialImport 
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='MaterialCase' and id=(select id from sysobjects where  name='mouldcostproduct'))
  alter table mouldcostproduct 
  add MaterialCase nvarchar(50)
Go

if not exists(select * from syscolumns where name='SoID' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add SoID Int Default 0
Go

if not exists(select * from syscolumns where name='DetailID' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add DetailID nVarChar(50) Default NewID()
Go

Update MouldScheduleDetailNew
	Set DetailID=NewID()
	Where Isnull(DetailID,'')='' 

if not exists(select * from syscolumns where name='DetailID' and id=(select id from sysobjects where name='MouldScheduleItem'))
  alter table MouldScheduleItem 
  add DetailID nVarChar(50) 
Go

if not exists(select * from syscolumns where name='DetailID' and id=(select id from sysobjects where name='MouldScheduleMachine'))
  alter table MouldScheduleMachine 
  add DetailID nVarChar(50)
Go

Update MouldScheduleItem
	Set DetailID=D.DetailID
	From MouldScheduleItem I Join MouldScheduleDetailNew D
	On I.InvSeriesNr=D.InvSeriesNr and I.SeriesNr=D.SeriesNr
	Where Isnull(I.DetailID,'')=''
Go

Update MouldScheduleMachine
	Set DetailID=D.DetailID
	From MouldScheduleMachine I Join MouldScheduleDetailNew D
	On I.InvSeriesNr=D.InvSeriesNr and I.SeriesNr=D.SeriesNr
	Where Isnull(I.DetailID,'')=''
Go

if not exists(select * from syscolumns where name='WorkPiece' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add WorkPiece nVarChar(50)
Go


if not exists(select * from syscolumns where name='ProdNr' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add ProdNr nVarChar(30)
Go

if not exists(select * from syscolumns where name='TechnicsNr' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew 
  add TechnicsNr nVarChar(20)
Go

Update MouldScheduleDetailNew
	Set ProdNr=B.ProdNr
	From MouldScheduleDetailNew a Join MouldProcessProduct b On a.SoProcessID=B.ProcessID
	and isnull(a.ProdNr,'')=''
Go

Update MouldScheduleDetailNew
	Set WorkPiece=B.WorkPiece
	From MouldScheduleDetailNew a Join MouldProcessProduct b On a.SoProcessID=B.ProcessID
	and isnull(a.WorkPiece,'')=''
Go

Update MouldScheduleDetailNew
	Set TechnicsNr=B.TechnicsNr
	From MouldScheduleDetailNew a Join MouldProcessProduct b On a.SoProcessID=B.ProcessID
	and isnull(a.TechnicsNr,'')=''
Go

if not exists(select * from syscolumns where name='RepeatReason' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add RepeatReason nvarchar(200)
Go

if not exists(select * from syscolumns where name='OrderId' and id=(select id from sysobjects where name='workcenter'))
  alter table workcenter
  add OrderId int
Go

if not exists(select * from syscolumns where name='Applyer' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Applyer nvarchar(20)
Go

if not exists(select * from syscolumns where name='ApplyDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add ApplyDate datetime
Go

if not exists(select * from syscolumns where name='PlanProcureDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add PlanProcureDate datetime
Go

if not exists(select * from syscolumns where name='PlanAttendDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add PlanAttendDate datetime
Go

if not exists(select * from syscolumns where name='ProcureOrderDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add ProcureOrderDate datetime
Go

if not exists(select * from syscolumns where name='Procurer' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Procurer nvarchar(20)
Go

if not exists(select * from syscolumns where name='Receiver' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Receiver nvarchar(20)
Go

if not exists(select * from syscolumns where name='ReceiveDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add ReceiveDate datetime
Go

if not exists(select * from syscolumns where name='Sender' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Sender nvarchar(20)
Go

if not exists(select * from syscolumns where name='SendDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add SendDate datetime
Go

if not exists(select * from sysobjects where name='XlsImport' and xtype='U')
CREATE TABLE [XlsImport] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Type] [nvarchar] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
	[FdName] [nvarchar] (200) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
	[FdDesc] [nvarchar] (200) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
	[XlsFdName] [nvarchar] (200) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
	[IsNeed] [bit] NULL CONSTRAINT [DF_XlsImport_IsNeed] DEFAULT (0),
	CONSTRAINT [PK_XlsImport] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='IsLast' and id=(select id from  sysobjects where name='MouldTechnics'))
  alter table MouldTechnics
  add IsLast bit default (0)   
Go

update MouldTechnics set IsLast=0 where IsLast is null
Go

if not exists(select * from syscolumns where name='IsLast' and id=(select id from  sysobjects where name='mouldprocessproduct'))
  alter table mouldprocessproduct
  add IsLast bit default (0)   
Go

update mouldprocessproduct set IsLast=0 where IsLast is null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BomProduct'
  and xtype='U') and name='ProdClass')
ALTER TABLE [dbo].[BomProduct]
ADD [ProdClass] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOM'
  and xtype='U') and name='ProdClass')
ALTER TABLE [dbo].[MouldProcessBOM]
ADD [ProdClass] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostProduct'
  and xtype='U') and name='ProdClass')
ALTER TABLE [dbo].[MouldCostProduct]
ADD [ProdClass] nvarchar(20)
GO

if not exists(select * from syscolumns where name='HaveQty' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add HaveQty bit default (0)   
Go

update bomproduct set haveqty=0 where haveqty is null
Go

if not exists(select * from syscolumns where name='FdType' and id=(select id from sysobjects where name='XlsImport'))
  alter table XlsImport
  add FdType nVarChar(1)   
Go

if not exists(select * from sysobjects where name='MouldTestNoticeBase')
  create table MouldTestNoticeBase(invseriesnr nvarchar(13),invnr nvarchar(20),
    planinvseriesnr nvarchar(13),times int,planstartdate datetime,
    planfinishdate datetime,memo nvarchar(250),make nvarchar(15),
    makedate datetime,updateby nvarchar(15),updatetime datetime,
    prodnr nvarchar(30))
Go

if not exists(select * from sysobjects where name='MouldTestNoticeProduct')
  create table MouldTestNoticeProduct(invseriesnr nvarchar(13),seriesnr nvarchar(3),
    memo nvarchar(200),productId nvarchar(50))
Go 

if not exists(select * from sysobjects where name='MouldTestReportBase')
  create table MouldTestReportBase(invseriesnr nvarchar(13),invnr nvarchar(20),
    Noticeinvseriesnr nvarchar(13),make nvarchar(15),memo nvarchar(250),
    makedate datetime,updateby nvarchar(15),updatetime datetime,
    cost float,insthour float,testdate datetime,testman nvarchar(15))
Go

if not exists(select * from sysobjects where name='MouldTestReportProduct')
  create table MouldTestReportProduct(invseriesnr nvarchar(13),seriesnr nvarchar(3),
    testResult nvarchar(200),testSolution nvarchar(200),productId nvarchar(50))
Go 

if not exists(select * from sysobjects where name='MouldTestReportBase')
  create table MouldTestReportBase(invseriesnr nvarchar(13),invnr nvarchar(20),
    Noticeinvseriesnr nvarchar(13),make nvarchar(15),memo nvarchar(250),
    makedate datetime,updateby nvarchar(15),updatetime datetime,
    cost float,insthour float,testdate datetime,testman nvarchar(15))
Go

if not exists(select * from sysobjects where name='MouldTestReportProduct')
  create table MouldTestReportProduct(invseriesnr nvarchar(13),seriesnr nvarchar(3),
    testResult nvarchar(200),testSolution nvarchar(200),productId nvarchar(50))
Go 
if not exists(select * from syscolumns where name='prodnr' and id=(select id from  sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add prodnr nvarchar(30)
Go 

if not exists(select * from sysobjects where name='MouldMakeProduct')
  create table MouldMakeProduct(soinvseriesnr nvarchar(13),prodnr nvarchar(30),
    state_flag nvarchar(50),state_date datetime,type_nr nvarchar(2))
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='CompanyInfo'
  and xtype='U') and name='LocalCurrency')
ALTER TABLE [dbo].[CompanyInfo]
ADD [LocalCurrency] nVarChar(20)
GO

if not exists(select * from sysobjects where name='MouldMakeProduct')
  create table MouldMakeProduct(soinvseriesnr nvarchar(13),prodnr nvarchar(30),
    state_flag nvarchar(50),state_date datetime,type_nr nvarchar(2))
Go
if not exists(select * from syscolumns where name='State_Id' and id=(select id from sysobjects where  name='MouldMakeProduct'))
   alter table MouldMakeProduct
   add State_Id integer
Go 

if not exists(select * from syscolumns where name='follower' and id=(select id from sysobjects where name='mouldbase'))
  alter table mouldbase
  add follower nvarchar(20)
Go 

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleItem'
  and xtype='U') and name='InstStartDate')
ALTER TABLE [dbo].[MouldScheduleItem]
ADD [InstStartDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleItem'
  and xtype='U') and name='InstFinishDate')
ALTER TABLE [dbo].[MouldScheduleItem]
ADD [InstFinishDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleMachine'
  and xtype='U') and name='InstStartDate')
ALTER TABLE [dbo].[MouldScheduleMachine]
ADD [InstStartDate] datetime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldScheduleMachine'
  and xtype='U') and name='InstFinishDate')
ALTER TABLE [dbo].[MouldScheduleMachine]
ADD [InstFinishDate] datetime
GO


if not exists(select * from sysobjects where name='WorkPiece' and xtype='U')
CREATE TABLE [WorkPiece] (
	[WorkPieceNo] [nvarchar] (50)  NOT NULL ,
	[WorkPiece] [nvarchar] (100)  NULL ,
	[IsKey] [bit] NULL CONSTRAINT [DF_WorkPiece_IsKey] DEFAULT (0),
	[Maker] [nvarchar] (50) NULL ,
	[MakeDate] [datetime] NULL ,
	[Updater] [nvarchar] (50) NULL ,
	[UpdateDate] [datetime] NULL ,
	CONSTRAINT [PK_WorkPiece] PRIMARY KEY  CLUSTERED 
	(
		[WorkPieceNo]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='WorkStatus' and xtype='U')
CREATE TABLE [WorkStatus] (
	[s_no] [nvarchar] (10)   NOT NULL ,
	[s_name] [nvarchar] (20)   NULL ,
	[Picture] [nvarchar] (200)   NULL ,
	[IsWrite] [bit] NULL CONSTRAINT [DF_WorkStatus_IsWrite] DEFAULT (0),
	CONSTRAINT [PK_WorkStatus] PRIMARY KEY  CLUSTERED 
	(
		[s_no]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldMakeJit')
  create table MouldMakeJit(id int identity(1,1),Prodnr nvarchar(30),
    WorkPieceNo nvarchar(50),TechnicsNr nvarchar(20),P_No nvarchar(20),
    MachineNr nvarchar(20),StateNr nvarchar(20),WorkDate datetime,ProcessId nvarchar(50),
    TypeNr nvarchar(2),HaveConvert bit default (0) ,WorkCnNr nvarchar(20),IsWrite bit,
ProcessInvseriesnr nvarchar(13))
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostBase'
  and xtype='U') and name='RepeatJobCost')
ALTER TABLE [dbo].[MouldCostBase]
ADD [RepeatJobCost] Float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostBase'
  and xtype='U') and name='RepeatJobCost2')
ALTER TABLE [dbo].[MouldCostBase]
ADD [RepeatJobCost2] Float Default 0
GO

ALTER TABLE [dbo].[ProductClass]
ALTER COLUMN [PClassName] nvarchar(20)
GO

if not exists(select * from syscolumns where name='PcId' and id=(select id from sysobjects where name='MouldMakeJit'))
  alter table MouldMakeJit
  add PcId nvarchar(10)
Go
if not exists(select * from syscolumns where name='IsOver' and id=(select id from sysobjects where name='MouldMakeJit'))
  alter table MouldMakeJit
  add IsOver bit
Go
if not exists(select * from syscolumns where name='StateKind' and id=(select id from sysobjects where name='MouldMakeJit'))
  alter table MouldMakeJit
  add StateKind int
Go
if not exists(select * from syscolumns where name='IsMachine' and id=(select id from sysobjects where name='MouldMakeJit'))
  alter table MouldMakeJit
  add IsMachine bit
Go
if not exists(select * from syscolumns where name='WorkPieceNoNew' and id=(select id from sysobjects where name='MouldMakeJit'))
  alter table MouldMakeJit
  add WorkPieceNoNew nvarchar(50)
Go

if not exists(select * from sysobjects where name='WorkPiecePack')
  create table WorkPiecePack(WorkPieceNo nvarchar(50),
    WorkPiecePackNo nvarchar(50),Maker nvarchar(20),
    MakeDate datetime,Updater nvarchar(20),
    UpdateDate datetime)
Go

if not exists(select * from syscolumns where name='IsOver' and id=(select id from sysobjects where name='workstatus'))
  alter table workstatus
  add IsOver bit default (0)
Go

if not exists(select * from syscolumns where name='Kind' and id=(select id from sysobjects where name='workstatus'))
  alter table workstatus
  add Kind int 
Go

if not exists(select * from syscolumns where name='KeyJitData' and id=(select id from sysobjects where name='companyinfo'))
  alter table companyinfo
  add KeyJitData int
Go
/*0：终端采集1：手工KEY数据*/
Update companyinfo set KeyJitData=0
Go

ALTER TABLE [dbo].[MouldReceiveRepair]
ALTER COLUMN [Repairitem] nvarchar(20)
GO

ALTER TABLE [dbo].[MouldReceiveMangle]
ALTER COLUMN [manglereason] nvarchar(20)
GO

ALTER TABLE [dbo].[MouldRegular]
ALTER COLUMN [Repairitem] nvarchar(20)
GO

ALTER TABLE [dbo].[MouldVindicate]
ALTER COLUMN [projectNr] nvarchar(20)
GO

if not exists(select * from syscolumns where name='Kind' and id=(select id from sysobjects where name='WorkPiece'))
  alter table WorkPiece
  add Kind nVarChar(10)
Go


if not exists(select * from syscolumns where name='Material' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Material nVarChar(50)
Go

if not exists(select * from syscolumns where name='Color' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Color nVarChar(20)
Go

if not exists(select * from syscolumns where name='ProduceWeight' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add ProduceWeight Float Default 0
Go

if not exists(select * from syscolumns where name='GateWeight' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add GateWeight Float Default 0
Go

if not exists(select * from syscolumns where name='MachineNr' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add MachineNr nVarChar(20)
Go

if not exists(select * from syscolumns where name='Ton' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Ton Int
Go

if not exists(select * from syscolumns where name='CycleTime' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add CycleTime Int
Go

if not exists(select * from syscolumns where name='TestReason' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add TestReason nVarChar(20)
Go

if not exists(select * from syscolumns where name='Qty1' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Qty1 Int
Go

if not exists(select * from syscolumns where name='Qty2' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Qty2 Int
Go

if not exists(select * from syscolumns where name='Qty3' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Qty3 Int
Go

if not exists(select * from syscolumns where name='Qty4' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Qty4 Int
Go

if not exists(select * from syscolumns where name='Qty5' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Qty5 Int
Go

if not exists(select * from syscolumns where name='TotalQty' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add TotalQty Int
Go

if not exists(select * from syscolumns where name='eWorkPiece' and 
  id=(select id from sysobjects where name='workpiece'))
  alter table workpiece
  add eWorkPiece nvarchar(50)
Go 

if not exists(select * from sysobjects where name='MouldPartBase')
  create table MouldPartBase(prodnr nvarchar(30),make nvarchar(20),
    makedate datetime,updateby nvarchar(20),updatetime datetime,
    memo nvarchar(200))
Go

if not exists(select * from sysobjects where name='MouldPartProduct')
  create table MouldPartProduct(prodnr nvarchar(30),seriesnr nvarchar(3),
    workpieceno nvarchar(20),workpiecename nvarchar(30),qty int,
    memo nvarchar(100),productid nvarchar(50), eWorkPiece nvarchar(50))
Go

if not exists(select * from sysobjects where name='MouldPartDetail')
  create table MouldPartDetail(prodnr nvarchar(30),seriesnr nvarchar(3),
    workpieceno nvarchar(20),workpiecename nvarchar(30),qty int,
    memo nvarchar(100),productid nvarchar(50), eWorkPiece nvarchar(50))
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='ProductNr')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ProductNr] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='Material')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [Material] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MaterialColor')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MaterialColor] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MachineNr')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MachineNr] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='Ton')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [Ton] nvarchar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CycleTime')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CycleTime] Int
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='NetWeight')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [NetWeight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='GateWeight')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [GateWeight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='Weight')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [Weight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MaterialWeight')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MaterialWeight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BakeMoisture')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BakeMoisture] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BakeHour')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BakeHour] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='Qty')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [Qty] Int
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MouthMoisture6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MouthMoisture6] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldStress2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldStress3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldStress3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldSpeed3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldSpeed3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='OpenMouldPosition3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [OpenMouldPosition3] nVarChar(20)
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldStress2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldStress3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldStress3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldSpeed3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldSpeed3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='CloseMouldPosition3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [CloseMouldPosition3] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectStress2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectStress3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectStress3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectSpeed3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectSpeed3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectPosition3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectPosition3] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectStress4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectStress4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectSpeed4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectSpeed4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectPosition4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectPosition4] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectStress5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectStress5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectSpeed5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectSpeed5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectPosition5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectPosition5] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectTime1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectTime1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectTime2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectTime2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectTime3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectTime3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectTime4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectTime4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='InjectTime5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [InjectTime5] Float
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BaoYaSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BaoYaSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BaoYaStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BaoYaStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaStress2] Float
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='DingChuSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='DingChuSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='DingChuStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='DingChuStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuStress2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MeltSpeed')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MeltSpeed] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MeltStress')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MeltStress] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MeltPositon')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MeltPositon] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MeltTime')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MeltTime] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='MeltSongtui')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [MeltSongtui] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='BringinType')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BringinType] nVarChar(40)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='Conclusion')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [Conclusion] nVarChar(40)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='RecodNr')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [RecodNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='Material')
ALTER TABLE [dbo].[MouldBase]
ADD [Material] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='Shrinking')
ALTER TABLE [dbo].[MouldBase]
ADD [Shrinking] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='Steel')
ALTER TABLE [dbo].[MouldBase]
ADD [Steel] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldscheduledetailnew'
  and xtype='U') and name='Material')
ALTER TABLE [dbo].[mouldscheduledetailnew]
ADD [Material] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldscheduledetailnew'
  and xtype='U') and name='Weight')
ALTER TABLE [dbo].[mouldscheduledetailnew]
ADD [Weight] float Default 0.0
GO

ALTER TABLE [dbo].[MouldTestNoticeBase]
ALTER COLUMN [TestReason] nvarchar(100)
GO

if not exists(select * from syscolumns where name='Consignment' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Consignment nVarChar(20)
Go

if not exists(select * from syscolumns where name='AgainSign' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add AgainSign nVarChar(20)
Go

if not exists(select * from syscolumns where name='StatusNo' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add StatusNo nVarChar(10)
Go


if not exists(select * from syscolumns where name='SignDate' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add SignDate DateTime
Go

if not exists(select * from syscolumns where name='AgainSignDate' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add AgainSignDate DateTime
Go

Update MouldTestNoticeBase
Set StatusNo='0'
Where IsNull(StatusNo,'')=''
Go

if not exists(select * from syscolumns where name='Orders' and id=(select id from sysobjects where name='MouldTechnics'))
  alter table MouldTechnics
  add Orders SmallInt
Go

Alter Table BOMBase
Alter Column PicNum nVarChar(200)
Go

if not exists(select * from sysobjects where name='MouldFixNotice' and xtype='U')
CREATE TABLE [MouldFixNotice] (
	[InvSeriesNr] [nvarchar] (13) NOT NULL ,
	[ProdNr] [nvarchar] (30)  NULL ,
	[Descript] [nvarchar] (50)  NULL ,
	[FixFinishDate] [datetime] NULL ,
	[MouldFooting] [nvarchar] (1000) NULL ,
	[FixItem] [nvarchar] (1000)  NULL ,
	[MakeDate] [datetime] NULL ,
	[Maker] [nvarchar] (20) NULL ,
	CONSTRAINT [PK_MouldFixNotice] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='ProcessOrderDate' and id=(select id  from sysobjects where name='mouldbase'))
  alter table MouldBase add ProcessOrderDate datetime
Go

Update MouldBase
Set ProcessOrderDate=b.MakeDate
From MouldBase a Join MouldProcessBase b On a.ProdNr=b.ProdNr
Where a.ProcessOrderDate is Null

alter table mouldscheduledetailnew
alter column unwontedreason nvarchar(200)
Go

alter table mouldscheduledetailnew
alter column transactway nvarchar(200)
Go

alter table mouldprocessproduct
alter column unwontedreason nvarchar(200)
Go

alter table mouldprocessproduct
alter column transactway nvarchar(200)
Go 

if not exists(select * from syscolumns where name='QtyUnit' and id=(select id  from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct add QtyUnit Int Default 1
Go

Update MouldProcessProduct
Set QtyUnit=1 
Where IsNull(QtyUnit,0)<=0
Go

if not exists(select * from syscolumns where name='JitId' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add JitId nvarchar(50)
Go

if not exists(select * from syscolumns where name='ScheduleInvseriesnr' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add ScheduleInvseriesnr nvarchar(15)
Go

if not exists(select * from sysobjects where name='MouldSchedulePart')
  create table MouldSchedulePart(ProcessId nvarchar(50),P_NO nvarchar(20),
    MachineNr nvarchar(20),WorkPieceNo nvarchar(30),JitId nvarchar(50),
    StartDate datetime,WorkDate datetime,StateKind int,InstHours float)
Go

if not exists(select * from syscolumns where name='ChillTime' and id=(select id from  sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add ChillTime Int
Go 

if not exists(select * from syscolumns where name='CtrlRepeatJob' and id=(select id from sysobjects where name='companyinfo'))
  alter table companyinfo add CtrlRepeatJob bit
Go

update companyinfo set CtrlRepeatJob=0 where CtrlRepeatJob is null
Go 

if not exists(select * from syscolumns where name='QtyUnit' and id=(select id from sysobjects where name='MouldStdProcessProduct'))
  alter table MouldStdProcessProduct add QtyUnit int
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldscheduledetailnew'
  and xtype='U') and name='Spec')
ALTER TABLE [dbo].[mouldscheduledetailnew]
ADD [Spec] nVarChar(200) 
GO

if exists(select * from sysobjects where name='v_WorkCenter')
  drop view dbo.v_WorkCenter
Go

Create view v_WorkCenter
As
SELECT WorkCnNr,WorkCnName,1 as Type1,WorkTimeAllot
From WorkCenter
where stoped=0
Union
Select providernr,providerbrief,2 as Type1,'0' as WorkTimeAllot
From ProviderBase
Go 

if not exists(select * from syscolumns where name='RtfFileName' and id=(select id from sysobjects where name='CompanyInfo'))
  alter table CompanyInfo add RtfFileName Image
Go

if exists(select * from sysobjects where name='sp_DeleteScheduleSeriesnr')
  drop procedure sp_DeleteScheduleSeriesnr
Go

Create procedure sp_DeleteScheduleSeriesnr
@Invseriesnr nvarchar(13)
As
declare @DetailId nvarchar(50)
declare @i int
declare @s nvarchar(3)
begin
  declare seriesnr cursor for select detailid from mouldscheduledetailnew where invseriesnr=@Invseriesnr
  open seriesnr
  fetch next from seriesnr into @detailid
  select @i=1
  while @@fetch_status=0
  begin
    if @i<10 select @s='00'+convert(nvarchar(1),@i)
    if (@i<100) and (@i>9) select @s='0'+convert(nvarchar(2),@i)
    if @i>99 select @s=convert(nvarchar(3),@i)
    update mouldscheduledetailnew set seriesnr=@s where invseriesnr=@Invseriesnr and detailid=@detailid
    fetch next from seriesnr into @detailid
    select @i=@i+1
  end
  close seriesnr
  deallocate seriesnr
end
Go

if not exists(select * from syscolumns where name='Unit' and id=(select id from sysobjects where name='mouldscheduledetailnew'))
  alter table mouldscheduledetailnew add Unit nvarchar(50)
Go

if not exists(select * from syscolumns where name='WorkCnNr' and id=(select id from sysobjects where name='mouldtechnics'))
alter table mouldtechnics
add WorkCnNr nvarchar(13)
Go

if not exists(select * from syscolumns where name='Getser' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Getser nvarchar(20)
Go

if not exists(select * from syscolumns where name='GetsDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add GetsDate datetime 
Go

if not exists(select * from syscolumns where name='PMRate' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct
  add PMRate float Default 1 
Go

Update MouldProcessProduct
Set PMRate=1
Where IsNull(PMRate,0)=0
Go

if not exists(select * from sysobjects where name='FactotumBase' and xtype='U')
CREATE TABLE [FactotumBase] (
	[InvSeriesNr] [nvarchar] (13) NOT NULL ,
	[InvNr] [nvarchar] (20)  NULL ,
	[Maker] [nvarchar] (20)  NULL ,
	[MakeDate] [datetime] NULL ,
	[Updateby] [nvarchar] (20)  NULL ,
	[UpdateTime] [datetime] NULL ,
	[CMemo] [text]  NULL ,
	CONSTRAINT [PK_FactotumBase] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='FactotumProduct' and xtype='U')
CREATE TABLE [FactotumProduct] (
	[InvSeriesNr] [nvarchar] (13) NOT NULL ,
	[SeriesNr] [nvarchar] (3) NOT NULL ,
	[InstStartDate] [datetime] NULL ,
	[InstFinishDate] [datetime] NULL ,
	[P_No] [nvarchar] (20)  NULL ,
	[Effort] [nvarchar] (100)  NULL ,
	[InstDays] [float] NULL  DEFAULT (0),
	[OverInstHour] [float] NULL DEFAULT (0),
	CONSTRAINT [PK_FactotumProduct] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='P_No' and id=(select id from sysobjects where name='MouldScheduleDetailNew'))
  alter table MouldScheduleDetailNew
  add P_No nVarChar(20) 
Go

ALTER TABLE [dbo].[MouldProcessProduct]
ALTER COLUMN [WorkPiece] nvarchar(150)
GO

ALTER TABLE [dbo].[MouldScheduleDetailNew]
ALTER COLUMN [WorkPiece] nvarchar(150)
GO

if not exists(select * from sysobjects where name='Bom2ProcessMasterNr')
  create table Bom2ProcessMasterNr(prodnr nvarchar(30),masternr nvarchar(30),
    IsConvert bit)
Go

if not exists(select * from sysobjects where name='Bom2ProcessTechnicsNr')
  create table Bom2ProcessTechnicsNr(prodnr nvarchar(30),masternr nvarchar(30),
    technicsnr nvarchar(20),IsConvert bit)
Go

if not exists(select * from syscolumns where name='NeedConfirmOverMaintain' and id=(select id from sysobjects where name='companyinfo'))
  alter table companyinfo add NeedConfirmOverMaintain bit
Go

if not exists(select * from syscolumns where name='OverMaintain' and id=(select id from sysobjects where name='companyinfo'))
  alter table companyinfo add OverMaintain int
Go

update companyinfo set NeedConfirmOverMaintain=0 where NeedConfirmOverMaintain is null
Go

update companyinfo set OverMaintain=0 where OverMaintain is null
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldprocessbom'
  and xtype='U') and name='LCurrency')
ALTER TABLE [dbo].[mouldprocessbom]
ADD [LCurrency] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldprocessbom'
  and xtype='U') and name='FCurrency')
ALTER TABLE [dbo].[mouldprocessbom]
ADD [FCurrency] nvarchar(20)
GO

Update mouldprocessbom
Set LCurrency=(Select Top 1 LocalCurrency From CompanyInfo)
Where IsNull(LCurrency,'')=''
Go

Update mouldprocessbom
Set FCurrency=(Select Top 1 LocalCurrency From CompanyInfo)
Where IsNull(FCurrency,'')=''
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldprocessbom'
  and xtype='U') and name='FPrice')
ALTER TABLE [dbo].[mouldprocessbom]
ADD [FPrice] float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldprocessbom'
  and xtype='U') and name='Rate')
ALTER TABLE [dbo].[mouldprocessbom]
ADD [Rate] float Default 1
GO

Update mouldprocessbom
Set FPrice=UniCost
Where IsNull(FPrice,0)=0
Go

Update mouldprocessbom
Set Rate=1
Where IsNull(Rate,0)=0
Go

if not exists(select * from syscolumns where name='Length' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add Length float Default 0
Go

if not exists(select * from syscolumns where name='Length' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Length  float Default 0
Go

if not exists(select * from syscolumns where name='Width' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add Width float Default 0
Go

if not exists(select * from syscolumns where name='Width' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Width  float Default 0
Go

if not exists(select * from syscolumns where name='Height' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add Height float Default 0
Go

if not exists(select * from syscolumns where name='Height' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Height  float Default 0
Go

if not exists(select * from syscolumns where name='Density' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add Density float Default 0
Go

if not exists(select * from syscolumns where name='Density' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Density  float Default 0
Go

if not exists(select * from syscolumns where name='Weight' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct add Weight float Default 0
Go

if not exists(select * from syscolumns where name='Weight' and id=(select id from sysobjects where name='mouldprocessbom'))
  alter table mouldprocessbom add Weight  float Default 1
Go

ALTER TABLE [dbo].[MouldBase]
ALTER COLUMN [Productnr] nvarchar(50)
GO

ALTER TABLE [dbo].[MouldBase]
ALTER COLUMN [SMoInvseriesnr] nvarchar(30)
GO

ALTER TABLE [dbo].[MouldProcessBase]
ALTER COLUMN [SMoInvseriesnr] nvarchar(30)
GO

if not exists(select * from sysobjects where name='Principal' and xtype='U')
CREATE TABLE [Principal] (
	[PrincipalNo] [nvarchar] (20)  NOT NULL ,
	[P_No] [nvarchar] (20)  NOT NULL ,
	CONSTRAINT [PK_Principal] PRIMARY KEY  CLUSTERED 
	(
		[PrincipalNo],
		[P_No]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='SaleOrdBase' and xtype='U')
CREATE TABLE [SaleOrdBase] (
	[invseriesnr] [nvarchar] (20) NOT NULL ,
	[invnr] [nvarchar] (20)   NULL ,
	[CInvNr] [nvarchar] (30) NULL ,
	[IVDate] [datetime] NULL ,
	[CustomerNr] [nvarchar] (20)  NULL ,
	[ShipTo] [nvarchar] (200)  NULL ,
	[SaleNr] [nvarchar] (20)  NULL ,
	[AttnName] [nvarchar] (20)  NULL ,
	[AttnPhone] [nvarchar] (50)  NULL ,
	[TLocalAmnt] [float] NULL ,
	[Memo] [ntext]   NULL ,
	[make] [nvarchar] (20)   NULL ,
	[makeDate] [datetime] NULL ,
	[Consignment] [nvarchar] (20)   NULL ,
	[signDate] [datetime] NULL ,
	[againsign] [nvarchar] (20)   NULL ,
	[againsignDate] [datetime] NULL ,
	[statusno] [nvarchar] (10)   NULL ,
	CONSTRAINT [PK_SaleOrdBase] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='SaleOrdProduct' and xtype='U')
CREATE TABLE [SaleOrdProduct] (
	[invseriesnr] [nvarchar] (20)  NOT NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[CProdNr] [nvarchar] (30)   NULL ,
	[ProdNr] [nvarchar] (30)   NULL ,
	[descript] [nvarchar] (50)   NULL ,
	[Project] [nvarchar] (50)   NULL ,
	[UnitQty] [int] NULL ,
	[Material] [nvarchar] (50)   NULL ,
	[MouldType] [nvarchar] (20)   NULL ,
	[PlanStartDate] [datetime] NULL ,
	[PlanFinishDate] [datetime] NULL ,
	[PlanOutDate] [datetime] NULL ,
	[Unit] [nvarchar] (20)   NULL ,
	[Qty] [int] NULL ,
	[Price] [float] NULL ,
	[Memo] [ntext]   NULL ,
	CONSTRAINT [PK_SaleOrdProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='PlanStartDate')
ALTER TABLE [dbo].[MouldBase]
Add  [PlanStartDate] DateTime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='PlanOutDate')
ALTER TABLE [dbo].[MouldBase]
Add  [PlanOutDate] DateTime
GO

if not exists(select * from sysobjects where name='MouldProjProcBase' and xtype='U')
CREATE TABLE [MouldProjProcBase] (
	[invseriesnr] [nvarchar] (20) NOT NULL ,
	[invnr] [nvarchar] (20)  NULL ,
	[prodnr] [nvarchar] (30)  NULL ,
	[Descript] [nvarchar] (60)   NULL ,
	[SMoInvseriesnr] [nvarchar] (30)  NULL ,
	[ProjectName] [nvarchar] (50) NULL ,
	[MouldNum] [nvarchar] (13) NULL ,
	[StdProdNr] [nvarchar] (30)  NULL ,
	[TypeNr] [nvarchar] (2) NULL ,
	[ChargeType] [nvarchar] (2) NULL ,
	[PlanStartDate] [datetime] NULL ,
	[PlanFinishDate] [datetime] NULL ,
	[make] [nvarchar] (20)  NULL ,
	[makeDate] [smalldatetime] NULL ,
	[consignment] [nvarchar] (20) NULL ,
	[signDate] [smalldatetime] NULL ,
	[againsign] [nvarchar] (20)  NULL ,
	[againsignDate] [smalldatetime] NULL ,
	[statusno] [nvarchar] (10)   NULL ,
	[updateby] [nvarchar] (10)  NULL ,
	[updatetime] [smalldatetime] NULL ,
	[cmemo] [ntext]   NULL ,
	[SchedulePercent] [float] NULL ,
	[StatusId] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldProjProcProduct' and xtype='U')
CREATE TABLE [MouldProjProcProduct] (
	[invseriesnr] [nvarchar] (20)   NOT NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[prodnr] [nvarchar] (30)   NULL ,
	[TechnicsNr] [nvarchar] (20)   NULL ,
	[TechnicsName] [nvarchar] (40)   NULL ,
	[TechnicsDescript] [nvarchar] (50)   NULL ,
	[MakeType] [tinyint] NULL ,
	[WorkCnNr] [nvarchar] (13)  NULL ,
	[PlanDays] [float] NULL ,
	[PlanHour] [float] NULL ,
	[PlanStartTime] [Datetime] NULL ,
	[PlanFinishTime] [Datetime] NULL ,
	[WorkTimeAllot] [nvarchar] (20)  NULL   DEFAULT ('0'),
	[PMRate] [float] NULL DEFAULT (1),
	[QtyUnit] [int] NULL  DEFAULT (1),
	[pSchedulePercent] [float] NULL  ,
	[iSchedulePercent] [float] NULL ,
	[PriceType] [smallint] NULL ,
	[StdPrice] [float] NULL ,
	[TechnicsQty] [float] NULL ,
	[ProcessRate] [float] NULL ,
	[UnitQty] [float] NULL ,
	[Qty] [float] NULL ,
	[IssueQty] [float] NULL ,
	[FinishQty] [float] NULL ,
	[BadQty] [float] NULL ,
	[SpoilQty] [float] NULL ,
	[InstStartTime] [smalldatetime] NULL ,
	[InstFinishTime] [smalldatetime] NULL ,
	[InstHour] [float] NULL DEFAULT (0),
	[OverInstHour] [float] NULL DEFAULT (0),
	[RepeatHour] [float] NULL  DEFAULT (0),
	[RepeatPrice] [float] NULL  DEFAULT (0),
	[RepeatOverHour] [float] NULL  DEFAULT (0),
	[RepeatOverPrice] [float] NULL  DEFAULT (0),
	[WorkPieceNo] [nvarchar] (50)  NULL ,
	[WorkPiece] [nvarchar] (150)   NULL ,
	[Cost] [float] NULL ,
	[UnwontedReason] [nvarchar] (200)   NULL ,
	[TransactWay] [nvarchar] (200)  NULL ,
	[scheduleInfo1] [nvarchar] (1)  NULL ,
	[scheduleInfo2] [nvarchar] (1)  NULL ,
	[ProcessID] [nvarchar] (50)   NULL ,
	[tmpseriesnr] [nvarchar] (13)  NULL ,
	[WorkDate] [datetime] NULL ,
	[PlanStartDay] [int] NULL  DEFAULT (0),
	[PlanFinishDay] [int] NULL  DEFAULT (0),
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[RepeatReason] [nvarchar] (200)  NULL ,
	[IsLast] [bit] NULL DEFAULT (0),
	[CMemo] [ntext]  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='Mouldstdprocessproduct'
  and xtype='U') and name='PlanDays')
ALTER TABLE [dbo].[MouldStdProcessProduct]
Add  [PlanDays] float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='Mouldstdprocessproduct'
  and xtype='U') and name='pSchedulePercent')
ALTER TABLE [dbo].[MouldStdProcessProduct]
Add  [pSchedulePercent] float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBase'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[MouldProcessBase]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessProduct'
  and xtype='U') and name='soProcessID')
ALTER TABLE [dbo].[MouldProcessProduct]
Add  [soProcessID] nVarChar(50)
GO

ALTER TABLE [dbo].[mouldschedulemasternew]
ALTER COLUMN [Maker] nvarchar(20)
GO

ALTER TABLE [dbo].[mouldschedulemasternew]
ALTER COLUMN [UpdateBy] nvarchar(20)
GO

if not exists(select * from sysobjects where name='WorkProject')
CREATE TABLE [WorkProject] (
	[ProjectNr] [nvarchar] (20) NOT NULL ,
	[ProjectDesc] [nvarchar] (200) NULL ,
	[WorkType] [nvarchar] (20)  NULL ,
	CONSTRAINT [PK_WorkProject] PRIMARY KEY  CLUSTERED 
	(
		[ProjectNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='ProjectNr')
ALTER TABLE [dbo].[MouldTechnics]
Add  [ProjectNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BomProduct'
  and xtype='U') and name='MaterialCaseNr')
ALTER TABLE [dbo].[BomProduct]
Add  [MaterialCaseNr] nVarChar(30)
GO

if not exists(select * from sysobjects where name='MouldDesignBase')
CREATE TABLE [MouldDesignBase] (
	[invseriesnr] [nvarchar] (15) NOT NULL ,
	[invnr] [nvarchar] (20)  NULL ,
	[StdProdNr] [nvarchar] (20)  NULL ,
	[prodnr] [nvarchar] (30) NULL ,
	[Descript] [nvarchar] (60)  NULL ,
	[SMoInvseriesnr] [nvarchar] (30)  NULL ,
	[SumQty] [float] NULL ,
	[prodqty] [float] NULL ,
	[badqty] [float] NULL ,
	[finishqty] [float] NULL ,
	[make] [nvarchar] (15)  NULL ,
	[makeDate] [smalldatetime] NULL ,
	[consignment] [nvarchar] (15)  NULL ,
	[signDate] [smalldatetime] NULL ,
	[againsign] [nvarchar] (15)   NULL ,
	[againsignDate] [smalldatetime] NULL ,
	[statusno] [nvarchar] (10)   NULL ,
	[updateby] [nvarchar] (10)   NULL ,
	[updatetime] [smalldatetime] NULL ,
	[cmemo] [ntext]   NULL ,
	[SchedulePercent] [float] NULL ,
	[TypeNr] [nvarchar] (2)   NULL ,
	[ChargeType] [nvarchar] (2)   NULL ,
	[ProjectName] [nvarchar] (50)   NULL ,
	[PlanStartDate] [datetime] NULL ,
	[PlanFinishDate] [datetime] NULL ,
	[StatusId] [int] NULL ,
	[soInvSeriesNr] [nvarchar] (20)   NULL 
	CONSTRAINT [PK_MouldDesignBase] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldDesignProduct')
CREATE TABLE [MouldDesignProduct] (
	[invseriesnr] [nvarchar] (15)NOT NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[prodnr] [nvarchar] (30)   NULL ,
	[TechnicsNr] [nvarchar] (20)   NULL ,
	[TechnicsName] [nvarchar] (40)  NULL ,
	[Descript] [nvarchar] (50)   NULL ,
	[PlanHour] [float] NULL ,
	[StdPrice] [float] NULL ,
	[ProcessRate] [float] NULL ,
	[UnitQty] [float] NULL ,
	[Qty] [float] NULL ,
	[IssueQty] [float] NULL ,
	[FinishQty] [float] NULL ,
	[WorkCnNr] [nvarchar] (13)   NULL ,
	[WorkCnName] [nvarchar] (20)   NULL ,
	[PlanStartTime] [smalldatetime] NULL ,
	[PlanFinishTime] [smalldatetime] NULL ,
	[InstStartTime] [smalldatetime] NULL ,
	[InstFinishTime] [smalldatetime] NULL ,
	[InstHour] [float] NOT NULL ,
	[SchedulePercent] [float] NULL ,
	[MakeType] [tinyint] NULL ,
	[UnwontedReason] [nvarchar] (200)   NULL ,
	[TransactWay] [nvarchar] (200)   NULL ,
	[scheduleInfo1] [nvarchar] (1)   NULL ,
	[scheduleInfo2] [nvarchar] (1)   NULL ,
	[ProcessID] [nvarchar] (50)   NULL ,
	[tmpseriesnr] [nvarchar] (13)   NULL ,
	[WorkDate] [datetime] NULL ,
	[AutoCalc] [bit] NULL  DEFAULT (0),
	[WorkTimeAllot] [nvarchar] (20)  NULL   DEFAULT ('0'),
	[PlanStartDay] [int] NULL   DEFAULT (0),
	[PlanFinishDay] [int] NULL   DEFAULT (0),
	[CMemo] [ntext]   NULL ,
	[InstDays] [float] NULL   DEFAULT (0.0),
	[RepeatHour] [float] NULL   DEFAULT (0),
	[RepeatPrice] [float] NULL   DEFAULT (0),
	[RepeatOverHour] [float] NULL   DEFAULT (0),
	[RepeatOverPrice] [float] NULL   DEFAULT (0),
	[OverInstHour] [float] NULL   DEFAULT (0),
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[RepeatReason] [nvarchar] (200)  NULL ,
	[QtyUnit] [int] NULL  DEFAULT (1),
	[PMRate] [float] NULL   DEFAULT (1),
	[soProcessID] [nvarchar] (50)   NULL ,
	CONSTRAINT [PK_MouldDesignProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



if not exists(select * from sysobjects where name='DesignSchedule')
CREATE TABLE [DesignSchedule] (
	[InvSeriesNr] [nvarchar] (20)   NOT NULL ,
	[SeriesNr] [nvarchar] (3)   NOT NULL ,
	[ProdNr] [nvarchar] (30)   NULL ,
	[soProcessID] [nvarchar] (50)   NULL ,
	[ProcessID] [nvarchar] (50)   NOT NULL ,
	[TechnicsNr] [nvarchar] (20)   NULL ,
	[Descript] [nvarchar] (50)   NULL ,
	[WorkCnNr] [nvarchar] (20)   NULL ,
	[InstStartTime] [datetime] NULL ,
	[InstFinishTime] [datetime] NULL ,
	[InstHour] [float] NULL  DEFAULT (0),
	[OverInstHour] [float] NULL DEFAULT (0),
	[RepeatHour] [float] NULL DEFAULT (0),
	[RepeatOverHour] [float] NULL  DEFAULT (0),
	[SchedulePercent] [float] NULL  DEFAULT (0),
	[scheduleInfo1] [nvarchar] (20)   NULL ,
	[scheduleInfo2] [nvarchar] (500)   NULL ,
	[UnwontedReason] [nvarchar] (500)   NULL ,
	[TransactWay] [nvarchar] (500)   NULL ,
	[CMemo] [ntext]   NULL ,
	[Maker] [nvarchar] (20)   NULL ,
	[MakeDate] [datetime] NULL ,
	[UpdateBy] [nvarchar] (20)   NULL ,
	[UpdateTime] [datetime] NULL ,
	CONSTRAINT [PK_DesignSchedule] PRIMARY KEY  CLUSTERED 
	(
		[ProcessID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldDesignProduct'
  and xtype='U') and name='Designer')
ALTER TABLE [dbo].[MouldDesignProduct]
Add  [Designer] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldDesignProduct'
  and xtype='U') and name='IsEdit')
ALTER TABLE [dbo].[MouldDesignProduct]
Add  [IsEdit] Bit Default 0
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestNoticeBase'
  and xtype='U') and name='soProcessID')
ALTER TABLE [dbo].[MouldTestNoticeBase]
Add  [soProcessID] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='soProcessID')
ALTER TABLE [dbo].[MouldTestReportBase]
Add  [soProcessID] nVarChar(50)
GO

if not exists(select * from sysobjects where name='OutProcessPlanBase')
CREATE TABLE [OutProcessPlanBase] (
	[InvSeriesNr] [nvarchar] (20)  NOT NULL ,
	[InvNr] [nvarchar] (20)   NULL ,
	[ProdNr] [nvarchar] (30)   NULL ,
	[SoProcessID] [nvarchar] (50)  NULL ,
	[ProvideNr] [nvarchar] (20)   NULL ,
	[ShipTo] [nvarchar] (200)   NULL ,
	[PersonNr] [nvarchar] (20)   NULL ,
	[Memo] [ntext]   NULL ,
	[Maker] [nvarchar] (30)   NULL ,
	[MakeDate] [datetime] NULL ,
	[Updateby] [nvarchar] (30)   NULL ,
	[UpdateDate] [datetime] NULL ,
	[statusno] nVarChar (20) Null,
	CONSTRAINT [PK_OutProcessPlanBase] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if not exists(select * from sysobjects where name='OutProcessPlanProduct')
CREATE TABLE [OutProcessPlanProduct] (
	[invseriesnr] [nvarchar] (15)   NOT NULL ,
	[seriesnr] [nvarchar] (3)   NOT NULL ,
	[ProdNr] [nvarchar] (30)   NULL ,
	[WorkPieceNo] [nvarchar] (50)   NULL ,
	[WorkPiece] [nvarchar] (150)   NULL ,
	[TechnicsNr] [nvarchar] (20)   NULL ,
	[TechnicsName] [nvarchar] (40)   NULL ,
	[Descript] [nvarchar] (50)   NULL ,
	[PriceType] [smallint] NULL ,
	[StdPrice] [float] NULL   DEFAULT (0),
	[Qty] [float] NULL   DEFAULT (1),
	[PlanStartTime] [smalldatetime] NULL ,
	[PlanFinishTime] [smalldatetime] NULL ,
	[IssueQty] [float] NULL   DEFAULT (0),
	[FinishQty] [float] NULL  DEFAULT (0),
	[BadQty] [float] NULL  DEFAULT (0),
	[SpoilQty] [float] NULL  DEFAULT (0),
	[PlanHour] [float] NULL  DEFAULT (0),
	[InstHour] [float] NOT NULL  DEFAULT (0),
	[RepeatHour] [float] NULL   DEFAULT (0),
	[RepeatPrice] [float] NULL  DEFAULT (0),
	[RepeatOverHour] [float] NULL DEFAULT (0),
	[RepeatOverPrice] [float] NULL  DEFAULT (0),
	[OverInstHour] [float] NULL  DEFAULT (0),
	[InstStartTime] [smalldatetime] NULL ,
	[InstFinishTime] [smalldatetime] NULL ,
	[Cspecmemo] [ntext]   NULL ,
	[Cost] [float] NULL ,
	[ProcessID] [nvarchar] (50)   NULL ,
	[WorkDate] [datetime] NULL ,
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[SoProcessID] [nvarchar] (50)   NULL ,
	[RepeatReason] [nvarchar] (200)   NULL ,
	CONSTRAINT [PK_OutProcessPlanProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='mouldscheduledetailnew'
  and xtype='U') and name='WorkPieceNo')
ALTER TABLE [dbo].[mouldscheduledetailnew]
Add  [WorkPieceNo] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcProduct'
  and xtype='U') and name='LevelNr')
ALTER TABLE [dbo].[MouldProjProcProduct]
Add  [LevelNr] Int Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcProduct'
  and xtype='U') and name='PrevID')
ALTER TABLE [dbo].[MouldProjProcProduct]
Add  [PrevID] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcProduct'
  and xtype='U') and name='Resource')
ALTER TABLE [dbo].[MouldProjProcProduct]
Add  [Resource] nVarChar(500)
GO

Update MouldProjProcProduct
Set LevelNr=0
Where LevelNr Is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMakeJit'
  and xtype='U') and name='ScanType')
ALTER TABLE [dbo].[MouldMakeJit]
Add  [ScanType] Int Default 1
GO

if not exists(select * from syscolumns where name='MyId' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add MyId nvarchar(50)
Go

if not exists(select * from syscolumns where name='OldDep_No' and id=(select id from sysobjects where name='mouldchgstatusproduct'))
  alter table mouldchgstatusproduct add OldDep_No nVarChar(20)
Go

if not exists(select * from syscolumns where name='NewDep_No' and id=(select id from sysobjects where name='mouldchgstatusproduct'))
  alter table mouldchgstatusproduct add NewDep_No  nVarChar(20)
Go

if not exists(select * from sysobjects where name='MouldJitTotal')
CREATE TABLE [MouldJitTotal] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[Prodnr] [nvarchar] (30)  NULL ,
	[WorkPieceNo] [nvarchar] (50)  NULL ,
	[TechnicsNr] [nvarchar] (20)  NULL ,
	[P_No] [nvarchar] (20)  NULL ,
	[MachineNr] [nvarchar] (20)  NULL ,
	[ProcessId] [nvarchar] (50)  NULL ,
	[HaveConvert] [bit] NULL DEFAULT (0),
	[WorkCnNr] [nvarchar] (20)  NULL ,
	[IsWrite] [bit] NULL ,
	[PcId] [nvarchar] (10)  NULL ,
	[IsMachine] [bit] NULL ,
	[BId] [nvarchar] (50)  NULL ,
	[InstStartTime] [datetime] NULL ,
	[BStateNr] [nvarchar] (20)  NULL ,
	[EId] [nvarchar] (50)  NULL ,
	[InstFinishTime] [datetime] NULL ,
	[EStateNr] [nvarchar] (20)  NULL ,
	[ScheduleID] [nvarchar] (50)  NULL ,
	[ScanType] [int] DEFAULT (1),
	[MyId] [nvarchar] (50)  NULL 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='TotalId' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add TotalId nvarchar(50)
Go

if not exists(select * from syscolumns where name='AdjustHour' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add AdjustHour float default 0
Go

Update MouldProcessProduct
Set AdjustHour='0'
Where Isnull(AdjustHour,0)=0
Go

if not exists(select * from syscolumns where name='AdjustOverHour' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add AdjustOverHour float default 0
Go

Update MouldProcessProduct
Set AdjustOverHour='0'
Where Isnull(AdjustOverHour,0)=0
Go

if not exists(select * from syscolumns where name='Remark' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add Remark nvarchar(50)
Go

if not exists(select * from syscolumns where name='IsAdd' and id=(select id from sysobjects where name='mouldmakejit'))
  alter table mouldmakejit
  add IsAdd Bit Default 0
Go

if not exists(select * from sysobjects where name='MouldQuoteCalcBase')
CREATE TABLE [MouldQuoteCalcBase] (
	[InvSeriesNr] [nvarchar] (13)   NOT NULL ,
	[InvDate] [datetime] NULL ,
	[CalcPerson] [nvarchar] (20)   NULL ,
	[CustProdNr] [nvarchar] (30)   NULL ,
	[CustomerNr] [nvarchar] (20)   NULL ,
	[ProductNr] [nvarchar] (50)   NULL ,
	[ProductName] [nvarchar] (100)   NULL ,
	[ProductMater] [nvarchar] (100)   NULL ,
	[MouldType] [nvarchar] (20)   NULL ,
	[UnitQty] [smallint] NULL ,
	[MaterFees] [float] NULL ,
	[DirectMaterFees] [float] NULL ,
	[ProcessFees] [float] NULL ,
	[DesignFees] [float] NULL ,
	[TestFees] [float] NULL ,
	[PackFees] [float] NULL ,
	[TaxFees] [float] NULL ,
	[TransportFees] [float] NULL ,
	[TaxRate] [float] NULL ,
	[ManageFees] [float] NULL ,
	[Cost] [float] NULL ,
	[GainRate] [float] NULL ,
	[Gain] [float] NULL ,
	[TTLAmount] [float] NULL ,
	[MakeDate] [smalldatetime] NULL ,
	[Make] [nvarchar] (20)   NULL ,
	[UpdateTime] [datetime] NULL ,
	[UpdateBy] [nvarchar] (20)   NULL ,
	CONSTRAINT [PK_MouldQuoteCalcBase] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteCalcMater')
CREATE TABLE [MouldQuoteCalcMater] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[MaterNr] [nvarchar] (50)  NULL ,
	[MaterName] [nvarchar] (100)  NULL ,
	[MaterDesc] [nvarchar] (100)  NULL ,
	[Shape] [nvarchar] (20)  NULL ,
	[Length] [float] NULL ,
	[Width] [float] NULL ,
	[Height] [float] NULL ,
	[Diameter] [float] NULL ,
	[Density] [float] NULL ,
	[Qty] [int] NULL ,
	[Unit] [nvarchar] (20)  NULL ,
	[Weight] [float] NULL ,
	[LossRate] [float] NULL ,
	[Price] [float] NULL ,
	[TotalPrice] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteCalcMater] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteCalcDirMater')
CREATE TABLE [MouldQuoteCalcDirMater] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[MaterNr] [nvarchar] (50)  NULL ,
	[MaterName] [nvarchar] (100)  NULL ,
	[MaterDesc] [nvarchar] (100)  NULL ,
	[Qty] [int] NULL ,
	[Unit] [nvarchar] (20)  NULL ,
	[Price] [float] NULL ,
	[TotalPrice] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteCalcDirMater] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteProcess')
CREATE TABLE [MouldQuoteProcess] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[TechnicsNr] [nvarchar] (20)  NULL ,
	[TechnicsName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteProcess] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteDesign')
CREATE TABLE [MouldQuoteDesign] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[TechnicsNr] [nvarchar] (20)  NULL ,
	[TechnicsName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteDesign] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteTest')
CREATE TABLE [MouldQuoteTest] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[TechnicsNr] [nvarchar] (20)  NULL ,
	[TechnicsName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteTest] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuotePack')
CREATE TABLE [MouldQuotePack] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[PackNr] [nvarchar] (20)  NULL ,
	[PackName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuotePack] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteTransport')
CREATE TABLE [MouldQuoteTransport] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[TransportNr] [nvarchar] (20)  NULL ,
	[TransportName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteTransport] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteTax')
CREATE TABLE [MouldQuoteTax] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[TaxNr] [nvarchar] (20)  NULL ,
	[TaxName] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[Hours] [float] NULL ,
	[Cost] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteTax] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteBase')
CREATE TABLE [MouldQuoteBase] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[InvNr] [nvarchar] (20)  NULL ,
	[IvDate] [datetime] NULL ,
	[CustomerNr] [nvarchar] (20)  NULL ,
	[CustAttnNr] [nvarchar] (20)  NULL ,
	[Tel] [nvarchar] (50)  NULL ,
	[Fax] [nvarchar] (50)  NULL ,
	[EMail] [nvarchar] (50)  NULL ,
	[ShipTo] [nvarchar] (200)  NULL ,
	[ValidDay] [int] NULL ,
	[AttnName] [nvarchar] (50)  NULL ,
	[CurrencyNr] [nvarchar] (20)  NULL ,
	[ExChange] [float] NULL ,
	[FAmount] [float] NULL ,
	[LAmount] [float] NULL ,
	[PayTimes] [int] NULL ,
	[Memo] [ntext]  NULL ,
	[Make] [nvarchar] (20)  NULL ,
	[MakeDate] [datetime] NULL ,
	[UpdateBy] [nvarchar] (20)  NULL ,
	[UpdateTime] [datetime] NULL ,
	[Consignment] [nvarchar] (20)  NULL ,
	[signDate] [datetime] NULL ,
	[againsign] [nvarchar] (20)  NULL ,
	[againsignDate] [datetime] NULL ,
	[statusno] [nvarchar] (10)  NULL ,
	CONSTRAINT [PK_MouldQuoteBase] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuoteProduct')
CREATE TABLE [MouldQuoteProduct] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[CustProdNr] [nvarchar] (50)  NULL ,
	[ProductNr] [nvarchar] (50)  NULL ,
	[ProductName] [nvarchar] (100)  NULL ,
	[UnitQty] [int] NULL ,
	[ProductMater] [nvarchar] (50)  NULL ,
	[MouldType] [nvarchar] (20)  NULL ,
	[ProcessDays] [int] NULL ,
	[Qty] [int] NULL ,
	[Unit] [nvarchar] (50)  NULL ,
	[Price] [float] NULL ,
	[TTLAmount] [float] NULL ,
	CONSTRAINT [PK_MouldQuoteProduct] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldQuotePay')
CREATE TABLE [MouldQuotePay] (
	[InvSeriesNr] [nvarchar] (13)  NOT NULL ,
	[SeriesNr] [nvarchar] (3)  NOT NULL ,
	[PayTimes] [nvarchar] (20)  NULL ,
	[PayRate] [float] NULL ,
	[PayAmount] [float] NULL ,
	[PayMemo] [nvarchar] (200)  NULL ,
	CONSTRAINT [PK_MouldQuotePay] PRIMARY KEY  CLUSTERED 
	(
		[InvSeriesNr],
		[SeriesNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='boProcessID' and id=(select id from sysobjects where name='MouldProcessProduct'))
  alter table MouldProcessProduct 
  add boProcessID nVarChar(50)
Go

if not exists(select * from sysobjects where name='Bom2ProcBOMSelect')
CREATE TABLE [Bom2ProcBOMSelect] (
	[BOMID] [nvarchar] (50)  NOT NULL ,
	[ProdNr] [nvarchar] (30)  NULL ,
	[IsSelect] [bit] NULL ,
	[SeriesNr] [nvarchar] (3)  NULL ,
	[MasterNr] [nvarchar] (30)  NULL ,
	[Descript] [nvarchar] (50)  NULL ,
	[Spec] [nvarchar] (50)  NULL ,
	[MasterQty] [int] NULL ,
	CONSTRAINT [PK_Bom2ProcBOMSelect] PRIMARY KEY  CLUSTERED 
	(
		[BOMID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='Bom2ProcTechSelect')
CREATE TABLE [Bom2ProcTechSelect] (
	[ProdNr] [nvarchar] (30)  NULL ,
	[BOMID] [nvarchar] (50)  NULL ,
	[IsSelect] [bit] NULL ,
	[TechnicsNr] [nvarchar] (20)  NULL ,
	[TechnicsName] [nvarchar] (50)  NULL ,
	[ProcessID] [nvarchar] (50)  NOT NULL ,
	[PlanHour] [float] NULL ,
	[PlanMin] [int] NULL ,
	[ProjectNr] [nvarchar] (30)  NULL ,
	[WorkCnNr] [nvarchar] (20)  NULL ,
	[Orders] [int] NULL ,
	CONSTRAINT [PK_BOM2ProcTechSelect] PRIMARY KEY  CLUSTERED 
	(
		[ProcessID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='PurchaseOrdBase')
CREATE TABLE [PurchaseOrdBase] (
	[invseriesnr] [nvarchar] (20)   NOT NULL ,
	[invnr] [nvarchar] (20)   NULL ,
	[CInvNr] [nvarchar] (30)   NULL ,
	[IVDate] [datetime] NULL ,
	[CustomerNr] [nvarchar] (20)   NULL ,
	[ShipTo] [nvarchar] (200)   NULL ,
	[SaleNr] [nvarchar] (20)   NULL ,
	[AttnName] [nvarchar] (20)   NULL ,
	[AttnPhone] [nvarchar] (50)   NULL ,
	[TLocalAmnt] [float] NULL ,
	[Memo] [ntext]   NULL ,
	[make] [nvarchar] (20)   NULL ,
	[makeDate] [datetime] NULL ,
	[Consignment] [nvarchar] (20)   NULL ,
	[signDate] [datetime] NULL ,
	[againsign] [nvarchar] (20)   NULL ,
	[againsignDate] [datetime] NULL ,
	[statusno] [nvarchar] (10)   NULL ,
	CONSTRAINT [PK_PurchaseOrdBase] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='PurchaseOrdProduct')
CREATE TABLE [PurchaseOrdProduct] (
	[invseriesnr] [nvarchar] (20)  NOT NULL ,
	[seriesnr] [nvarchar] (3)  NOT NULL ,
	[ProdNr] [nvarchar] (30)  NULL ,
	[ProductNr] [nvarchar] (30)  NULL ,
	[descript] [nvarchar] (50)  NULL ,
	[Project] [nvarchar] (50)  NULL ,
	[UnitQty] [int] NULL ,
	[Material] [nvarchar] (50)  NULL ,
	[Unit] [nvarchar] (20)  NULL ,
	[Qty] [int] NULL ,
	[Price] [float] NULL ,
	[Memo] [ntext]  NULL ,
	CONSTRAINT [PK_PurchaseOrdProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='PPInvSeriesNr' and id=(select id from sysobjects where name='MouldProcessBase'))
  alter table MouldProcessBase
  add PPInvSeriesNr nVarChar(13)
Go

if not exists(select * from sysobjects where name='MouldTechLineBase')
CREATE TABLE [MouldTechLineBase] (
	[invseriesnr] [nvarchar] (13)  NOT NULL ,
	[LineName] [nvarchar] (30)  NULL ,
	[Descript] [nvarchar] (40)  NULL ,
	[make] [nvarchar] (15)  NULL ,
	[makeDate] [smalldatetime] NULL ,
	[consignment] [nvarchar] (15)  NULL ,
	[signDate] [smalldatetime] NULL ,
	[againsign] [nvarchar] (15)  NULL ,
	[againsignDate] [smalldatetime] NULL ,
	[statusno] [nvarchar] (10)  NULL ,
	[updateby] [nvarchar] (10)  NULL ,
	[updatetime] [smalldatetime] NULL ,
	[cmemo] [ntext]  NULL ,
	CONSTRAINT [PK_MouldTechLineBase] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='MouldTechLineProduct')
CREATE TABLE [MouldTechLineProduct] (
	[invseriesnr] [nvarchar] (13) NOT NULL ,
	[seriesnr] [nvarchar] (3) NOT NULL ,
	[LineName] [nvarchar] (30) NOT NULL ,
	[LevelNr] [smallint] NULL ,
	[TechnicsNr] [nvarchar] (20) NULL ,
	[Descript] [nvarchar] (50) NULL ,
	[StdHour] [float] NULL ,
	[StdPrice] [float] NULL ,
	[WorkCnNr] [nvarchar] (13) NULL ,
	[Cspecmemo] [ntext] NULL,
 	CONSTRAINT [PK_MouldTechLineProduct] PRIMARY KEY  CLUSTERED 
	(
		[invseriesnr],
		[seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='WorkPieceTechLine')
CREATE TABLE [WorkPieceTechLine] (
	[WorkPieceNo] [nvarchar] (50) NOT NULL ,
	[Seriesnr] [nvarchar] (3) NOT NULL ,
	[LineName] [nvarchar] (30) NOT NULL ,
	[TechnicsNr] [nvarchar] (20) NULL ,
	[Descript] [nvarchar] (50) NULL ,
	[StdHour] [float] NULL ,
	[StdPrice] [float] NULL ,
	[WorkCnNr] [nvarchar] (13) NULL ,
	[Cspecmemo] [ntext] NULL,
 	CONSTRAINT [PK_WorkPieceTechLine] PRIMARY KEY  CLUSTERED 
	(
		[WorkPieceNo],
		[Seriesnr]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='LineName' and id=(select id from sysobjects where name='WorkPiece'))
  alter table WorkPiece
  add LineName nVarChar(30)
Go

if not exists(select * from syscolumns where name='LineName' and id=(select id from sysobjects where name='Bom2ProcBOMSelect'))
  alter table Bom2ProcBOMSelect
  add LineName nVarChar(30)
Go


if not exists(select * from syscolumns where name='Orders' and id=(select id from sysobjects where name='WorkPieceTechLine'))
  alter table WorkPieceTechLine
  add Orders Int Default 0
Go

if not exists(select * from syscolumns where name='Orders' and id=(select id from sysobjects where name='MouldTechLineProduct'))
  alter table MouldTechLineProduct
  add Orders Int Default 0
Go

if not exists(select * from syscolumns where name='CStateNr' and id=(select id from sysobjects where name='MouldJitTotal'))
  alter table MouldJitTotal
  add CStateNr nVarChar(20)
Go

if not exists(select * from syscolumns where name='CID' and id=(select id from sysobjects where name='MouldJitTotal'))
  alter table MouldJitTotal
  add CID nVarChar(50)
Go

if not exists(select * from syscolumns where name='CTime' and id=(select id from sysobjects where name='MouldJitTotal'))
  alter table MouldJitTotal
  add CTime DateTime
Go

if not exists(select * from syscolumns where name='PlanStartBackDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanStartBackDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanFinishBackDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanFinishBackDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanStartDesignDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanStartDesignDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanFinishDesignDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanFinishDesignDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanStartProcDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanStartProcDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanFinishProcDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanFinishProcDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanStartTestDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanStartTestDate SmallDateTime
Go

if not exists(select * from syscolumns where name='PlanFinishTestDate' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add PlanFinishTestDate SmallDateTime
Go

if not exists(select * from syscolumns where name='FinishDate' and id=(select id from sysobjects where name='MouldProcessBase'))
  alter table MouldProcessBase
  add FinishDate SmallDateTime
Go

if not exists(select * from syscolumns where name='Finisher' and id=(select id from sysobjects where name='MouldProcessBase'))
  alter table MouldProcessBase
  add Finisher nVarChar(20)
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='WorkCenter'
  and xtype='U') and name='pUnitCapacity')
ALTER TABLE [dbo].[WorkCenter]
ADD [pUnitCapacity] float DEFAULT 0
GO

if not exists(select * from syscolumns where name='WorkType' and id=(select id from sysobjects where name='MouldCostProduct'))
  alter table MouldCostProduct
  add WorkType nVarChar(20)
Go

if not exists(select * from syscolumns where name='WorkPieceNo' and id=(select id from sysobjects where name='MouldCostProduct'))
  alter table MouldCostProduct
  add WorkPieceNo nVarChar(50)
Go

if not exists(select * from sysobjects where name='MmsDataReport')
    create table MmsDataReport(class_flag int,prodnr nvarchar(30),instdays numeric(15,2),overhour numeric(15,2),
      repeatinstdays numeric(15,2),repeatoverhour numeric(15,2),MouldStartOverCnt int,TaskStartOverCnt int,
      MouldFinishOverCnt int,TaskFinishOverCnt int)
Go

if not exists(select * from syscolumns where name='ScanCnt' and id=(select id from sysobjects where name='MmsDataReport'))
  	alter table MmsDataReport
  	add ScanCnt Int Default 0
Go

if not exists(select * from syscolumns where name='WpProcessID' and id=(select id from sysobjects where name='OutProcessPlanProduct'))
  	alter table OutProcessPlanProduct
  	add WpProcessID nVarChar(50)
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BOMBase'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[BOMBase]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BOMProduct'
  and xtype='U') and name='Type')
ALTER TABLE [dbo].[BOMProduct]
Add  [Type] nVarChar(10)
GO

Update BOMProduct
	Set Type='B'
	Where Type is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BOMProduct'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[BOMProduct]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='Descript')
ALTER TABLE [dbo].[InventoryBase]
Alter Column  [Descript] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='Spec')
ALTER TABLE [dbo].[InventoryBase]
Alter Column  [Spec] nVarChar(50)
GO

Insert InventoryBase(ProdNr,Type,Descript,UpdateTime,UpdateBy,unittype,unicost,uniprice,Alocation) 
Select WorkPieceNo,'B',WorkPiece,GetDate(),'Admin',1,0,0,0
From WorkPiece 
Where WorkPieceNo not in
(Select ProdNr
From InventoryBase)
Order By WorkPieceNo
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='POID')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [POID] nVarChar(50) Default NewID()
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='RCDate')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [RCDate] SmallDateTime
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='PNID')
ALTER TABLE [dbo].[InGoodsProduct]
Add  [PNID] nVarChar(50) Default NewID()
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='MasterNr')
ALTER TABLE [dbo].[InGoodsProduct]
Add  [MasterNr] nVarChar(30)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[InGoodsProduct]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InventoryBase'
  and xtype='U') and name='StoreQty')
ALTER TABLE [dbo].[InventoryBase]
Add  [StoreQty] float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='PRID')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  [PRID] nVarChar(50) Default NewID()
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='MasterNr')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  [MasterNr] nVarChar(30)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostBase'
  and xtype='U') and name='DesignCost')
ALTER TABLE [dbo].[MouldCostBase]
Add  [DesignCost] float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldCostBase'
  and xtype='U') and name='AdjustCost')
ALTER TABLE [dbo].[MouldCostBase]
Add  [AdjustCost] float Default 0
GO

Update MouldBase
Set departnr=''
Where IsNull(departnr,'')=''
Go

Update MouldBase
Set locatenr=''
Where IsNull(locatenr,'')=''
Go

if not exists(select * from syscolumns where name='OldDep_No' and id=(select id from sysobjects where name='mouldchgstatusproduct'))
  alter table mouldchgstatusproduct add OldDep_No nVarChar(20)
Go

if not exists(select * from syscolumns where name='NewDep_No' and id=(select id from sysobjects where name='mouldchgstatusproduct'))
  alter table mouldchgstatusproduct add NewDep_No  nVarChar(20)
Go

if not exists(select * from syscolumns where name='UserList' and id=(select id from sysobjects where name='CustomerBase'))
  alter table CustomerBase add UserList  nVarChar(200)
Go

if not exists(select * from syscolumns where name='CueDepart' and id=(select id from sysobjects where name='MouldProvideBase'))
  alter table MouldProvideBase add CueDepart  nVarChar(20)
Go


if not exists(select * from syscolumns where name='CueDepart' and id=(select id from sysobjects where name='MouldProvide'))
  alter table MouldProvide add CueDepart  nVarChar(20)
Go

if not exists(select * from syscolumns where name='OtherFiles' and id=(select id from sysobjects where name='MouldReceive'))
  alter table MouldReceive add OtherFiles  nVarChar(200)
Go

if not exists(select * from syscolumns where name='OtherFiles' and id=(select id from sysobjects where name='MouldInOutProduct'))
  alter table MouldInOutProduct add OtherFiles  nVarChar(200)
Go

if not exists(select * from sysobjects where name='MouldStdSpec')
CREATE TABLE [MouldStdSpec] (
	[SpecID] [nvarchar] (50) NOT NULL CONSTRAINT [DF_MouldStdSpec_SpecID] DEFAULT (newid()),
	[Spec] [nvarchar] (50)  NULL ,
	CONSTRAINT [PK_MouldStdSpec] PRIMARY KEY  CLUSTERED 
	(
		[SpecID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where name='Length' and id=(select id from sysobjects where name='PurchaseOrdProduct'))
  alter table PurchaseOrdProduct add Length float Default 0
Go

if not exists(select * from syscolumns where name='Width' and id=(select id from sysobjects where name='PurchaseOrdProduct'))
  alter table PurchaseOrdProduct add Width float Default 0
Go

if not exists(select * from syscolumns where name='Height' and id=(select id from sysobjects where name='PurchaseOrdProduct'))
  alter table PurchaseOrdProduct add Height float Default 0
Go

if not exists(select * from syscolumns where name='Density' and id=(select id from sysobjects where name='PurchaseOrdProduct'))
  alter table PurchaseOrdProduct add Density float Default 0
Go

if not exists(select * from syscolumns where name='Weight' and id=(select id from sysobjects where name='PurchaseOrdProduct'))
  alter table PurchaseOrdProduct add Weight  float Default 1
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdBase'
  and xtype='U') and name='soInvSeriesNr')
ALTER TABLE [dbo].[SaleOrdBase]
Add  [soInvSeriesNr] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdSpec'
  and xtype='U') and name='ProdClass')
ALTER TABLE [dbo].[MouldStdSpec]
Add  [ProdClass] nVarChar(20)
GO

Update InventoryBase
Set StoreQty=0
Where StoreQty is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='POID')
ALTER TABLE [dbo].[InGoodsProduct]
Add  [POID] nVarChar(50) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='POID')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  [POID] nVarChar(50) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='PNQty')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [PNQty] Float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='PRQty')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [PRQty] Float Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOM'
  and xtype='U') and name='WhouseNr')
ALTER TABLE [dbo].[MouldProcessBOM]
Add  [WhouseNr] nVarChar(20) 
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdProduct'
  and xtype='U') and name='ProcessDays')
ALTER TABLE [dbo].[SaleOrdProduct]
ADD [ProcessDays] int DEFAULT 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='Weight')
ALTER TABLE [dbo].[InGoodsProduct]
ADD [Weight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='Weight')
ALTER TABLE [dbo].[InGoodsBackProduct]
ADD [Weight] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldStdProcessBase'
  and xtype='U') and name='StdType')
ALTER TABLE [dbo].MouldStdProcessBase
ADD [StdType] Int Default 2
GO

Update MouldStdProcessBase
	Set StdType=2
	Where StdType Is Null
Go

if not exists(select * from sysobjects where name='MouldMoveOutBase')
CREATE TABLE [dbo].[MouldMoveOutBase](
	[InvSeriesNr] [nchar](13) NOT NULL,
	[InvNr] [nchar](20) NULL,
	[CustomeNr] [nchar](20) NULL,
	[AttnNr] [nchar](20) NULL,
	[AttnPhone] [nchar](20) NULL,
	[Make] [nchar](20) NULL,
	[MakeDate] [datetime] NULL,
	[UpdateBy] [nchar](20) NULL,
	[UpdateTime] [datetime] NULL,
	[StatusNo] [nchar](20) NULL,
 CONSTRAINT [PK_MouldMoveOutBase] PRIMARY KEY CLUSTERED 
(
	[InvSeriesNr] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

if not exists(select * from sysobjects where name='MouldMoveOutProduct')
CREATE TABLE [dbo].[MouldMoveOutProduct](
	[InvSeriesNr] [nchar](20) NOT NULL,
	[SeriesNr] [nchar](3) NOT NULL,
	[ProdNr] [nchar](30) NULL,
	[Unit] [nchar](20) NULL,
	[Qty] [int] NULL,
	[Price] [float] NULL,
	[PPInvSeriesNr] [nchar](13) NULL,
	[soProcessID] [nchar](50) NULL,
 CONSTRAINT [PK_MouldMoveOutProduct] PRIMARY KEY CLUSTERED 
(
	[InvSeriesNr] ASC,
	[SeriesNr] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMoveOutProduct'
  and xtype='U') and name='ProcessID')
ALTER TABLE [dbo].MouldMoveOutProduct
ADD [ProcessID] nvarchar(50) DEFAULT NewID()
GO

if not exists(select * from sysobjects where name='MouldMachineProcessPlan')
CREATE TABLE [dbo].[MouldMachineProcessPlan](
	[InvseriesNr] [nvarchar](15) NOT NULL,
	[SeriesNr] [nvarchar](3) NOT NULL,
	[ProdNr] [nvarchar](30) NULL,
	[WorkPieceNo] [nvarchar](50) NULL,
	LevelNr Int Null,
	PlanDays float Null,
	PrevID nVarChar(50) Null,
	Resource nVarChar(50) Null,
	iSchedulePercent float Null,
	cMemo nVarChar(50) Null,
	[Priority] [float] NULL,
	[TechnicsNr] [nvarchar](20) NULL,
	[Descript] [nvarchar](50) NULL,
	[UnitQty] [float] NULL,
	[Qty] [Int] NULL,
	[WorkCnNr] [nvarchar](13) NULL,
	[PlanStartTime] [datetime] NULL,
	[PlanFinishTime] [datetime] NULL,
	[PlanHour] [float] NULL,
	[ProcessID] [nvarchar](50) NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[soProcessID] [nvarchar](50) NULL,
CONSTRAINT [PK_MouldMachineProcessPlan] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMachineProcessPlan'
  and xtype='U') and name='MachineNr')
ALTER TABLE [dbo].MouldMachineProcessPlan
ADD [MachineNr] nvarchar(20) 
GO


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='PrepareHour')
ALTER TABLE [dbo].[MouldTechnics]
ADD PrepareHour float DEFAULT 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='MoveHour')
ALTER TABLE [dbo].[MouldTechnics]
ADD MoveHour float DEFAULT 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTechnics'
  and xtype='U') and name='WaitHour')
ALTER TABLE [dbo].[MouldTechnics]
ADD WaitHour float DEFAULT 0
Go

Update MouldTechnics
Set PrepareHour=0
Where PrepareHour is Null
Go

Update MouldTechnics
Set MoveHour=0
Where MoveHour is Null
Go

Update MouldTechnics
Set WaitHour=0
Where WaitHour is Null
Go

if not exists(select * from syscolumns where name='Charge' and id=(select id from sysobjects where name='MouldMaintenance'))
  alter table MouldMaintenance add Charge Float Default 0.0
Go

Update MouldMaintenance
Set Charge=0
Where Charge Is Null
Go


if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='MaintainItem')
ALTER TABLE [dbo].[MouldMaintenance]
ADD MaintainItem nVarChar(20)
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PlanStartTime')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PlanStartTime DateTime
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PlanFinishTime')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PlanFinishTime DateTime
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='SafeQty')
ALTER TABLE [dbo].[MouldMaintenance]
ADD SafeQty Int Default 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PunchQty')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PunchQty Int Default 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='Times')
ALTER TABLE [dbo].[MouldMaintenance]
ADD Times Int Default 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='Days')
ALTER TABLE [dbo].[MouldMaintenance]
ADD Days Int Default 0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PrevDate')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PrevDate DateTime
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='NextDate')
ALTER TABLE [dbo].[MouldMaintenance]
ADD NextDate DateTime
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PlanInvSeriesNr')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PlanInvSeriesNr nVarChar(13)
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='PlanMakeDate')
ALTER TABLE [dbo].[MouldMaintenance]
ADD PlanMakeDate DateTime
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldMaintenance'
  and xtype='U') and name='MID')
ALTER TABLE [dbo].[MouldMaintenance]
ADD MID [int] IDENTITY (1, 1) NOT NULL
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='CompanyInfo'
  and xtype='U') and name='IsCtrlChgStatus')
ALTER TABLE [dbo].[CompanyInfo]
ADD [IsCtrlChgStatus] bit DEFAULT 0
GO

if not exists(select * from syscolumns where name='Designer' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add Designer nvarchar(20)
Go

if not exists(select * from syscolumns where name='DesignDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add DesignDate datetime Default GetDate()
Go

if not exists(select * from syscolumns where name='IsAudit' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add IsAudit bit Default 0
Go

if not exists(select * from syscolumns where name='BOMAuditer' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add BOMAuditer nvarchar(20)
Go

if not exists(select * from syscolumns where name='BOMAuditDate' and id=(select id from sysobjects where name='bomproduct'))
  alter table bomproduct
  add BOMAuditDate datetime Default GetDate()
Go

if exists(select * from syscolumns where name='StdHour' and id=(select id from sysobjects where name='MouldStdProcessProduct'))
  alter table MouldStdProcessProduct
  Alter Column StdHour Float Null 
Go

if exists(select * from syscolumns where name='StdPrice' and id=(select id from sysobjects where name='MouldStdProcessProduct'))
  alter table MouldStdProcessProduct
  Alter Column StdPrice Float Null 
Go

if not exists(select * from syscolumns where name='MJProdNr' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add MJProdNr nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='PoleNr' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add PoleNr nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='Material' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add Material nVarChar(50) Null 
Go

if not exists(select * from syscolumns where name='ProjectionArea' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add ProjectionArea nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='Volume' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add Volume nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='CoreMaterial' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add CoreMaterial nVarChar(50) Null 
Go

if not exists(select * from syscolumns where name='HRCRigidity' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add HRCRigidity nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='CoreSize' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add CoreSize nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='ParentProdNr' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add ParentProdNr nVarChar(30) Null 
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='InjectSpeed1')
ALTER TABLE [dbo].MouldBase
ADD [InjectSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='InjectStress1')
ALTER TABLE [dbo].MouldBase
ADD [InjectStress1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldBase'
  and xtype='U') and name='InjectTime1')
ALTER TABLE [dbo].MouldBase
ADD [InjectTime1] Float
GO

if not exists(select * from syscolumns where name='PunchDiam' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add PunchDiam Int Null 
Go

if not exists(select * from syscolumns where name='PunchSpeed' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add PunchSpeed Int Null 
Go

if not exists(select * from syscolumns where name='MouthDiam' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add MouthDiam Int Null 
Go

if not exists(select * from syscolumns where name='BakeMoisture' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add BakeMoisture Float Null 
Go
if not exists(select * from syscolumns where name='MouthMoisture' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add MouthMoisture Float Null 
Go

if not exists(select * from syscolumns where name='MeltTime' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add MeltTime Float Null 
Go

if not exists(select * from syscolumns where name='DingChuStress1' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add DingChuStress1 Float Null 
Go

if not exists(select * from syscolumns where name='ChillTime' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add ChillTime Float Null 
Go

if not exists(select * from syscolumns where name='SprueArea' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add SprueArea Float Null 
Go

if not exists(select * from syscolumns where name='CrossArea' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add CrossArea Float Null 
Go

if not exists(select * from syscolumns where name='FlowShape' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add FlowShape nVarChar(30) Null 
Go

if not exists(select * from syscolumns where name='FlowArea' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add FlowArea Float Null 
Go

if not exists(select * from syscolumns where name='MouldMoisture' and id=(select id from sysobjects where name='MouldBase'))
  alter Table MouldBase
  Add MouldMoisture Float Null 
Go

if not exists(select * from syscolumns where name='StoveMoisture' and id=(select id from sysobjects where name='MouldTestReportBase'))
  alter Table MouldTestReportBase
  Add StoveMoisture Float Null 
Go

if not exists(select * from syscolumns where name='NeckMoisture' and id=(select id from sysobjects where name='MouldTestReportBase'))
  alter Table MouldTestReportBase
  Add NeckMoisture Float Null 
Go

if not exists(select * from syscolumns where name='MouldMoisture' and id=(select id from sysobjects where name='MouldTestReportBase'))
  alter Table MouldTestReportBase
  Add MouldMoisture Float Null 
Go

if not exists(select * from syscolumns where name='Remark' and id=(select id from sysobjects where name='MouldTestReportBase'))
  alter Table MouldTestReportBase
  Add Remark nText Null 
Go

if not exists(select * from sysobjects where name='MouldInStore')
CREATE TABLE [dbo].MouldInStore(
	InvSeriesNr [nvarchar](13) NOT NULL,
	[prodnr] [nvarchar](20) NOT NULL,
	[papernr] [nvarchar](20) NULL,
	[unitqty] [int] NULL Default 1,
	[unitqtymemo] [nvarchar](50) NULL,
	[projectName] [nvarchar](50) NULL,
	[productnr] [nvarchar](50) NULL,
	[departnr] [nvarchar](20) NULL,
	[locatenr] [nvarchar](20) NULL,
	[createdate] [datetime] NULL,
	[state] [nvarchar](2) NULL,
	[relativeqty] [int] NULL Default 1,
	[statememo] [ntext] NULL,
	[ttlage] [int] NULL Default 0,
	[CustomProdnr] [nvarchar](20) NULL,
	[safeage] [int] NULL Default 0,
	[Dim] [nvarchar](50) NULL,
	[Ton] [nvarchar](50) NULL,
	[Weight] [nvarchar](20) NULL,
	[Coffer] [nvarchar](20) NULL,
	[MouldPicFile] [nvarchar](255) NULL,
	[RefPicFile] [nvarchar](255) NULL,
	[InRecordFile] [nvarchar](255) NULL,
	[RefPicFile2] [nvarchar](255) NULL,
	[Pagelocatenr] [nvarchar](20) NULL,
	[CommendMachine1] [nvarchar](20) NULL,
	[MakeYear] [nvarchar](4) NULL,
	[cmemo] [ntext] NULL,
	[makedate] [datetime] NULL,
	[maker] [nvarchar](7) NULL,
 CONSTRAINT [PK_MouldInStore] PRIMARY KEY CLUSTERED 
(
	InvSeriesNr ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO


if not exists(select * from sysobjects where name='PurchRequestOrdBase')
CREATE TABLE [dbo].[PurchRequestOrdBase](
	[invseriesnr] [nvarchar](20) NOT NULL,
	[invnr] [nvarchar](20) NULL,
	[CInvNr] [nvarchar](30) NULL,
	[IVDate] [datetime] NULL,
	[CustomerNr] [nvarchar](20) NULL,
	[ShipTo] [nvarchar](200) NULL,
	[SaleNr] [nvarchar](20) NULL,
	[AttnName] [nvarchar](20) NULL,
	[AttnPhone] [nvarchar](50) NULL,
	[PayConditong] [nvarchar](200) NULL,
	[TLocalAmnt] [float] NULL,
	[Memo] [ntext] NULL,
	[make] [nvarchar](20) NULL,
	[makeDate] [datetime] NULL,
	[Consignment] [nvarchar](20) NULL,
	[signDate] [datetime] NULL,
	[againsign] [nvarchar](20) NULL,
	[againsignDate] [datetime] NULL,
	[statusno] [nvarchar](10) NULL,
 CONSTRAINT [PK_PurchRequestOrdBase] PRIMARY KEY CLUSTERED 
(
	[invseriesnr] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

if not exists(select * from sysobjects where name='PurchRequestProduct')
CREATE TABLE [dbo].[PurchRequestProduct](
	[invseriesnr] [nvarchar](20) NOT NULL,
	[seriesnr] [nvarchar](3) NOT NULL,
	[ProdNr] [nvarchar](30) NULL,
	[ProductNr] [nvarchar](30) NULL,
	[descript] [nvarchar](50) NULL,
	[Project] [nvarchar](50) NULL,
	[UnitQty] [int] NULL,
	[Material] [nvarchar](50) NULL,
	[Unit] [nvarchar](20) NULL,
	[Qty] [int] NULL,
	[Price] [float] NULL,
	[Memo] [ntext] NULL,
	[soInvSeriesNr] [nvarchar](20) NULL,
	[PRID] [nvarchar](50) NULL Default newid(),
	[POID] [nvarchar](50) NULL ,
	[RCDate] [smalldatetime] NULL,
	[Length] [float] DEFAULT (0) NULL,
	[Width] [float] DEFAULT (0) NULL,
	[Height] [float] DEFAULT (0) NULL,
	[Density] [float] DEFAULT (0) NULL,
	[Weight] [float] DEFAULT (0) NULL,
	[PNQty] [float] DEFAULT (0) NULL,
	[PRQty] [float] DEFAULT (0) NULL,
 CONSTRAINT [PK_PurchRequestProduct] PRIMARY KEY CLUSTERED 
(
	[invseriesnr] ASC,
	[seriesnr] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BOMProduct'
  and xtype='U') and name='PRID')
ALTER TABLE [dbo].[BOMProduct]
Add  [PRID] nVarChar(50) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='PRID')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  [PRID] nVarChar(50) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='Material')
ALTER TABLE [dbo].[InGoodsProduct]
Add  Material nVarChar(50) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBomBase'
  and xtype='U') and name='MaterFrom')
ALTER TABLE [dbo].[MouldProcessBomBase]
Add  MaterFrom nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBomBase'
  and xtype='U') and name='MaterTo')
ALTER TABLE [dbo].[MouldProcessBomBase]
Add  MaterTo nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdBase'
  and xtype='U') and name='PayMode')
ALTER TABLE [dbo].[PurchaseOrdBase]
Add  PayMode nVarChar(100) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcBase'
  and xtype='U') and name='Follwer')
ALTER TABLE [dbo].[MouldProjProcBase]
Add  Follwer nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcBase'
  and xtype='U') and name='Designer')
ALTER TABLE [dbo].[MouldProjProcBase]
Add  Designer nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcBase'
  and xtype='U') and name='InChargeOf')
ALTER TABLE [dbo].[MouldProjProcBase]
Add  InChargeOf nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='Spec')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  Spec nVarChar(50) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdProduct'
  and xtype='U') and name='MouldNr')
ALTER TABLE [dbo].[SaleOrdProduct]
Add  MouldNr nVarChar(30) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='TubeMoisture6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [TubeMoisture6] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='BaoYaPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='BaoYaPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='DingChuPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='DingChuPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='BaoYaTime1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaTime1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='BaoYaTime2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [BaoYaTime2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='DingChuTime1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuTime1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='DingChuTime2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [DingChuTime2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='SongtuiTime')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [SongtuiTime] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='SongtuiPosition')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [SongtuiPosition] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='SongtuiSpeed')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [SongtuiSpeed] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress1 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress2 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress3 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress4 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress5 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziStress6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD ZhongziStress6 Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziSpeed6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziSpeed6] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition1] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition2] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition3] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition4] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition5] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziPosition6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziPosition6] nVarChar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime1')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime1] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime2')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime2] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime3')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime3] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime4')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime4] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime5')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime5] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where 
name='MouldTestReportBase'
  and xtype='U') and name='ZhongziTime6')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [ZhongziTime6] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='QianMoisture')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [QianMoisture] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='HouMoisture')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [HouMoisture] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='AddLiaoType')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [AddLiaoType] nVarChar(50)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='LenType')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [LenType] nVarChar(50)
GO

if not exists(select * from syscolumns where name='SheJiaoTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add SheJiaoTime Int Default 0
Go 

if not exists(select * from syscolumns where name='BaoYaTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add BaoYaTime Int Default 0
Go 

if not exists(select * from syscolumns where name='AddLiaoTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add AddLiaoTime Int Default 0
Go 


if not exists(select * from syscolumns where name='CloseOpenTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add CloseOpenTime Int Default 0
Go 

if not exists(select * from syscolumns where name='QujianTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add QujianTime Int Default 0
Go 

if not exists(select * from syscolumns where name='QiFuTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add QiFuTime Int Default 0
Go 

if not exists(select * from syscolumns where name='ZhouQiTime' and id=(select id from  
sysobjects where name='MouldTestReportBase'))
  alter table MouldTestReportBase
  add ZhouQiTime Int Default 0
Go 

if not exists(select * from syscolumns where name='MainProjectNr' and id=(select id from  
sysobjects where name='InventoryBase'))
  alter table InventoryBase
  add MainProjectNr nVarChar(20)
Go 

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='QuLiaoStress')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [QuLiaoStress] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='QuLiaoSpeed')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [QuLiaoSpeed] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='QuLiaoTime')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [QuLiaoTime] Float
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldTestReportBase'
  and xtype='U') and name='QuLiaoPosition')
ALTER TABLE [dbo].[MouldTestReportBase]
ADD [QuLiaoPosition] nVarChar(20)
GO

if not exists(select * from syscolumns where name='kind' and id=(select id from sysobjects where name='MouldProjProcBase'))
  alter table MouldProjProcBase
  add kind nvarchar(10)
Go

Update MouldProjProcBase Set Kind=0 Where Kind Is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdBase'
  and xtype='U') and name='TaxMode')
ALTER TABLE [dbo].[PurchaseOrdBase]
Add  TaxMode nVarChar(100) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='CalcMode')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  CalcMode nVarChar(20) Null Default '0'
GO

Update PurchaseOrdProduct Set CalcMode='0' Where CalcMode Is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='Cost')
ALTER TABLE [dbo].[PurchaseOrdProduct]
Add  Cost Float Null Default 0
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='providerbase'
  and xtype='U') and name='WorkMemo')
ALTER TABLE [dbo].[providerbase]
Add  WorkMemo nText
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdBase'
  and xtype='U') and name='WorkMemo')
ALTER TABLE [dbo].[PurchaseOrdBase]
Add  WorkMemo nText
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BomProduct'
  and xtype='U') and name='ElectrodeNo')
ALTER TABLE [dbo].[BomProduct]
Add  ElectrodeNo nVarChar(30)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='BomProduct'
  and xtype='U') and name='ID')
ALTER TABLE [dbo].[BomProduct]
Add  [ID] [int] IDENTITY (1, 1) NOT NULL
GO

if exists(select * from sysobjects where name='sp_BOM2PO')
  drop procedure sp_BOM2PO
Go

Create procedure sp_BOM2PO as
if exists(select * from sysobjects where name='#BOM2POTmp')
	Drop Table #BOM2POTmp
Select Convert(Bit,0) as IsSelect,a.ID,a.SoInvSeriesNr,a.ProdNr,a.MasterNr,a.Descript,a.Spec,a.MaterialCase,a.UniCost,a.PhysicalQty,a.Weight,a.Length,a.Width,a.Height,a.Type,a.BOMID,a.Provider,a.PlanProcureDate,a.MainUnit
,a.Designer,a.DesignDate,a.IsAudit,a.BOMAuditDate,a.BOMAuditer,a.Procurer,a.HaveQty,a.PRID, a.Cspecmemo,ElectrodeNo,
BOMStateN=Case When IsAudit=0 then '2' When HaveQty=1 Then '3' When ISNULL(Procurer,'')<>'' Then '4' Else '1' End
Into #BOM2POTmp
From BomProduct a 
Where a.Type in ('B','C','E')
Order By Prodnr,Masternr
Select * From #BOM2POTmp
Go 

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsProduct'
  and xtype='U') and name='CalcMode')
ALTER TABLE [dbo].[InGoodsProduct]
Add  CalcMode nVarChar(20) Null Default '0'
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='Material')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  Material nVarChar(50) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackProduct'
  and xtype='U') and name='CalcMode')
ALTER TABLE [dbo].[InGoodsBackProduct]
Add  CalcMode nVarChar(20) Null Default '0'
GO

ALTER TABLE [dbo].[InGoodsBase]
ALTER COLUMN [invoicenr] nvarchar(20)
GO

ALTER TABLE [dbo].[InGoodsBackBase]
ALTER COLUMN [invoicenr] nvarchar(20)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdProduct'
  and xtype='U') and name='ProcessID')
ALTER TABLE [dbo].[SaleOrdProduct]
ADD [ProcessID] nvarchar(50) DEFAULT NewID()
GO

Update SaleOrdProduct
Set ProcessID=NewID()
Where isnull(ProcessID,'')=''
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdProduct'
  and xtype='U') and name='TypeNr')
ALTER TABLE [dbo].[SaleOrdProduct]
ADD [TypeNr] nvarchar(20)
GO

Update SaleOrdProduct
Set TypeNr='01'
Where isnull(TypeNr,'')='01'
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='SaleOrdProduct'
  and xtype='U') and name='ChargeType')
ALTER TABLE [dbo].[SaleOrdProduct]
ADD [ChargeType] nvarchar(20)
GO

Update SaleOrdProduct
Set ChargeType='01'
Where isnull(ChargeType,'')='02'
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcBase'
  and xtype='U') and name='soProcessID')
ALTER TABLE [dbo].[MouldProjProcBase]
ADD soProcessID nvarchar(50) DEFAULT NewID()
GO

Update SaleOrdProduct
Set MouldNr=B.ProdNr
From SaleOrdProduct a Join MouldBase b On ISNULL(a.CProdNr,'')=ISNULL(b.CustomProdNr,'')
Where ISNULL(a.MouldNr,'')=''
Go

Update MouldProjProcBase
Set soProcessID=b.ProcessID
From MouldProjProcBase a Join SaleOrdProduct b On ISNULL(a.prodnr,'')=ISNULL(b.MouldNr,'')
Where isnull(soProcessID,'')=''
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProjProcBase'
  and xtype='U') and name='SoInvSeriesNr')
ALTER TABLE [dbo].[MouldProjProcBase]
ADD [SoInvSeriesNr] nvarchar(20)
GO

Update MouldProjProcBase
Set SoInvSeriesNr=b.InvSeriesNr
From MouldProjProcBase a Join SaleOrdProduct b On ISNULL(a.prodnr,'')=ISNULL(b.MouldNr,'')
Where isnull(SoInvSeriesNr,'')=''
Go

ALTER TABLE [dbo].[BOMProduct]
ALTER COLUMN [Spec] nVarChar(300)
GO

ALTER TABLE [dbo].[PurchaseOrdProduct]
ALTER COLUMN [Spec] nVarChar(300)
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='PurchaseOrdProduct'
  and xtype='U') and name='BomID')
ALTER TABLE [dbo].[PurchaseOrdProduct]
ADD [BomID] [nvarchar] (50)
GO

Update PurchaseOrdProduct
Set BOMID=b.BOMID
From PurchaseOrdProduct a Join BOMProduct b On a.PRID=b.PRID
Where a.BomID is Null
Go

Update PurchaseOrdProduct
Set BOMID=b.BOMID
From PurchaseOrdProduct a Join BOMProduct b On a.soInvSeriesNr=b.soInvSeriesNr
and a.ProductNr=b.Masternr
and a.Spec=b.Spec
Where a.BomID is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBase'
  and xtype='U') and name='MaterFrom')
ALTER TABLE [dbo].[InGoodsBase]
Add  MaterFrom nVarChar(20) Null
GO

if not exists(select * from syscolumns where id=(select id from sysobjects where name='InGoodsBackBase'
  and xtype='U') and name='MaterFrom')
ALTER TABLE [dbo].[InGoodsBackBase]
Add  MaterFrom nVarChar(20) Null
GO

if not exists(select * from syscolumns where name='Dim' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add Dim nVarChar(100)
Go

if not exists(select * from syscolumns where name='MaterWeight' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add MaterWeight nVarChar(10)
Go

if not exists(select * from syscolumns where name='LiuDaoType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add LiuDaoType nVarChar(20)
Go

if not exists(select * from syscolumns where name='DingChuType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add DingChuType nVarChar(20)
Go

if not exists(select * from syscolumns where name='ChouXinType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add ChouXinType nVarChar(20)
Go

if not exists(select * from syscolumns where name='KOType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add KOType nVarChar(20)
Go

if not exists(select * from syscolumns where name='JieGouType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add JieGouType nVarChar(20)
Go

if not exists(select * from syscolumns where name='RuShuiType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add RuShuiType nVarChar(20)
Go

if not exists(select * from syscolumns where name='YunShuiType' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add YunShuiType nVarChar(20)
Go


if not exists(select * from syscolumns where name='ISFuWei' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add ISFuWei Bit Default 0
Go

if not exists(select * from syscolumns where name='LiuDaoQty' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add LiuDaoQty nVarChar(20)
Go

if not exists(select * from syscolumns where name='LiuDaoWD' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add LiuDaoWD nVarChar(20)
Go

if not exists(select * from syscolumns where name='YunShuiWD1' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add YunShuiWD1 nVarChar(20)
Go

if not exists(select * from syscolumns where name='YunShuiWD2' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add YunShuiWD2 nVarChar(20)
Go

if not exists(select * from syscolumns where name='YunShuiWD3' and id=(select id from sysobjects where name='MouldTestNoticeBase'))
  alter table MouldTestNoticeBase
  add YunShuiWD3 nVarChar(20)
Go

Delete mouldscheduledetailnew Where DetailID='4DC87A58-8185-42FC-8146-A8A8AFBFC6D8'
Go

if not exists(select * from syscolumns where name='IsQty' and id=(select id from sysobjects where name='InventoryBase'))
  alter table InventoryBase
  add IsQty Bit Default 1
Go

Update InventoryBase Set IsQty=1 Where IsQty is Null
Go

if not exists(select * from syscolumns where name='IsQty' and id=(select id from sysobjects where name='MouldProcessBOM'))
  alter table MouldProcessBOM
  add IsQty Bit Default 1
Go

Update MouldProcessBOM Set IsQty=1 Where IsQty is Null
Go

Delete From MouldScheduleDetailNew Where invseriesnr not in (Select invseriesnr From MouldScheduleMasterNew)
Go

Delete From MouldScheduleItem Where DetailID not in (Select DetailID From MouldScheduleDetailNew )
Go

Delete From MouldScheduleMachine Where DetailID not in (Select DetailID From MouldScheduleDetailNew )
Go

if not exists(select * from syscolumns where name='DelQty' and id=(select id from sysobjects where name='BOMProduct'))
  alter table BOMProduct
  add DelQty Float Default 0
Go

Update BOMProduct Set DelQty=0 Where DelQty is Null
Go

if not exists(select * from syscolumns where name='IsSelect' and id=(select id from sysobjects where name='BOMProduct'))
  alter table BOMProduct
  add IsSelect Bit Default 0
Go

Update BOMProduct Set IsSelect=0 Where IsSelect is Null
Go

Update MouldProcessBOM Set DelQty=B.DelQty From MouldProcessBOM a Join (Select BOMID,Sum(PhysicalQty) as DelQty From MouldProcessBOM Group By BOMID) b On a.BomID=b.BomID and a.DelQty<=0
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOMBase'
  and xtype='U') and name='MType')
ALTER TABLE [dbo].[MouldProcessBOMBase]
Add  MType Int Default 1
GO

Update MouldProcessBOMBase Set MType=1 Where MType is Null
Go

if not exists(select * from syscolumns where id=(select id from sysobjects where name='MouldProcessBOMBase'
  and xtype='U') and name='TTLWeight')
ALTER TABLE [dbo].[MouldProcessBOMBase]
Add  TTLWeight Float Default 0
GO

Update MouldProcessBOMBase Set TTLWeight=0 Where TTLWeight is Null
Go

if not exists(select * from syscolumns where name='PlanRDate1' and id=(select id from sysobjects where name='MouldInOutProduct'))
  alter table MouldInOutProduct
  add PlanRDate1 DateTime
Go

if not exists(select * from syscolumns where name='PlanRDate2' and id=(select id from sysobjects where name='MouldInOutProduct'))
  alter table MouldInOutProduct
  add PlanRDate2 DateTime
Go

if not exists(select * from syscolumns where name='IsReceive' and id=(select id from sysobjects where name='MouldInOutProduct'))
  alter table MouldInOutProduct
  add IsReceive Bit Default 0
Go

Update MouldInOutProduct Set IsReceive=1 Where IsReceive is Null
Go

if not exists(select * from syscolumns where name='FactQty' and id=(select id from sysobjects where name='InOutProduct'))
  alter table InOutProduct
  add FactQty Float Default 0
Go

if not exists(select * from syscolumns where name='ProductMater' and id=(select id from sysobjects where name='SaleOrdProduct'))
  alter table SaleOrdProduct
  add ProductMater nVarChar(30)
Go

if not exists(select * from syscolumns where name='ProductQty' and id=(select id from sysobjects where name='SaleOrdProduct'))
  alter table SaleOrdProduct
  add ProductQty Int Default 0
Go

if not exists(select * from syscolumns where name='IsMaterE' and id=(select id from sysobjects where name='InGoodsBase'))
  alter table InGoodsBase
  add IsMaterE Bit Default 0
Go

Update InGoodsBase Set IsMaterE=0 Where IsMaterE is Null
Go

if not exists(select * from syscolumns where name='TTLWeight' and id=(select id from sysobjects where name='InGoodsBase'))
  alter table InGoodsBase
  add TTLWeight Float Default 0
Go


Update InGoodsBase Set TTLWeight=0 Where TTLWeight is Null
Go

if not exists(select * from syscolumns where name='ProdNr' and id=(select id from sysobjects where name='FactotumProduct'))
  alter table FactotumProduct
  add ProdNr nVarChar(30)
Go

Update BOMProduct Set Masternr='E001-' Where Masternr<>'E001-' and Descript='红铜' 
Go

Update PurchaseOrdProduct Set ProductNr='E001-'  Where ProductNr<>'E001-' and Descript='红铜' 
Go

Update MouldProcessBOM Set Masternr='E001-'  Where Masternr<>'E001-' and Descript='红铜' 
Go

Update InGoodsProduct Set Masternr='E001-'  Where Masternr<>'E001-' and Descript='红铜' 
Go