宁波数据导入

--1.客户表：TBL_Customer
--(0)
DELETE FROM [WebErp].[dbo].[TBL_Customer]
DELETE FROM [WebErp].[dbo].[TBL_ColorModel]
DELETE FROM [WebErp].[dbo].[TBL_Schema]
DELETE FROM [WebErp].[dbo].[TBL_SchemaDetail]
DELETE FROM [WebErp].[dbo].[TBL_SchemaCustom]
DELETE FROM [WebErp].[dbo].[TBL_StockMaterial]
DELETE FROM [WebErp].[dbo].[TBL_Order]

Select * into [TBL_Order]   from [WebErp].[dbo].[TBL_Order]
Select * into [TBL_Customer]   from [WebErp].[dbo].[TBL_Customer]
Select * into [TBL_ColorModel]   from [WebErp].[dbo].[TBL_ColorModel]
Select * into [TBL_Schema]   from [WebErp].[dbo].[TBL_Schema]
Select * into [TBL_SchemaDetail]  from [WebErp].[dbo].[TBL_SchemaDetail]
Select * into [TBL_SchemaCustom]  from [WebErp].[dbo].[TBL_SchemaCustom]
Select * into [TBL_StockMaterial]  from [WebErp].[dbo].[TBL_StockMaterial]
Select * into [TBL_Order]   from [WebErp].[dbo].[TBL_Order]

select * into TBL_Customer_Temp from [TBL_Customer]

--(1)插入TBL_Customer
Insert into TBL_Customer_Temp
(  [CustName], [CustomerUID],[BranchNo], Operator, arrearage,[OperateDate], IsOffline)
SELECT Distinct [客户名称] , 0 , 'NB', 'NB-00',0,getDate(),0
FROM [配方]
WHERE [配方].[客户名称] IS NOT NULL

Insert into TBL_Customer_Temp
(  [CustName], [CustomerUID],[BranchNo], Operator,arrearage, [OperateDate], IsOffline)
SELECT Distinct [客户名称] , 0 , 'NB', 'NB-00',0,getDate(),0
FROM [订单]
WHERE [订单].[客户名称] IS NOT NULL

--(2)删除有资料的客户、去掉重复
DELETE TBL_Customer_Temp
FROM TBL_Customer_Temp,[客户资料]
WHERE TBL_Customer_Temp.CustName = [客户资料].常用名称

INSERT INTO [TBL_Customer]
([CustomerUID], [CustName], [CustAlias], [Contacter], [Mobile], [TelephoneNo], [FaxNo], [Bank], [BalanceNo], [TaxNo], [ContactAddr], [Memo],
[BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline],[arrearage])
SELECT [CustomerUID], [CustName], [CustAlias], [Contacter], [Mobile], [TelephoneNo], [FaxNo], [Bank], [BalanceNo], [TaxNo], [ContactAddr], [Memo], [BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline],[arrearage]
FROM [TBL_Customer_Temp]
--(3)增加有资料的客户
Insert into  [TBL_Customer]
( [CustName], [Contacter], [Mobile], [TelephoneNo], [FaxNo], [Bank], [BalanceNo], [TaxNo], [ContactAddr], [Memo], [CustomerUID], [BranchNo], [Operator], [OperateDate], [IsOffline],[arrearage] )
SELECT [常用名称], [联系人], 0, [电话号码], [传真号码], [开户银行], [银行帐号], [税号], [地址], [附注], 0, 'NB', 'NB-00', getDate(), 0,0 
FROM [客户资料]

--(4)添加客户别名
UPDATE [TBL_Customer]
SET [CustAlias] = 客户名称.其它名称
FROM TBL_Customer,客户名称
WHERE TBL_Customer.CustName = 客户名称.常用名称

--(5)更新客户[CustomerUID]为PkId
UPDATE [TBL_Customer]
SET [CustomerUID] = [PKId]
FROM TBL_Customer

--(6)更新客户别名
UPDATE [TBL_Customer]
SET [CustAlias] = [CustAlias] + '|' + [CustName]
FROM TBL_Customer
WHERE CustAlias <> [CustName]

UPDATE [TBL_Customer]
SET [CustAlias] = [CustName]
FROM TBL_Customer
WHERE CustAlias is null

--2.配方表:TBL_Schema
--(1)
Insert into TBL_Schema
( [SchemaNo], [SchemaColorNo], [ModelMakeDate], [Weight], [NetWeight], [Price], [PantoneNo], [Color], [PriceClass], [LightSource], [Plastic],
[Using], [IsColorModelMade], [DevelopType], [HasOrder], [Maker], [CustName], [Memo], [IsConfirmed], [BranchNo], [Operator], [OperateDate],
[IsOffline] )
SELECT [配方编号], '-1', [制版日期], [每份重量], [每份重量], [单价], null,[颜色], null, null, [所用塑料], null ,1, 100, 1, null, [客户名称], [配方备注] ,1,'NB','NB-00',getdate(),1
FROM [配方]

--(2)去掉回车、前导空格
UPDATE [TBL_Schema] SET memo = LTrim(REPLACE(memo , 0x0D,0x00))
where memo is not null

--3.配方明细表: [TBL_SchemaDetail]

Insert into TBL_SchemaDetail
( [SchemaNo], [BranchNo], [MaterialNo], [MaterialWeight], [CurMoney], [IsAppend], [MaterialMemo], [Operator], [OperateDate], [IsOffline] )

SELECT [配方编号], 'NB', [材料编号], [材料用量], null,0,null,'NB-01',getdate(),1 FROM [配方明细]

--4.配方客户联系表：TBL_SchemaCustom
Insert into TBL_SchemaCustom
( [SchemaNo], [BranchNo], [CustName], [OperateDate])
SELECT [配方].[配方编号],'NB',[配方].[客户名称],getDate()
FROM [配方]
WHERE [配方].[客户名称] is not null AND [配方].[客户名称] <> ''

Insert into TBL_SchemaCustom
( [SchemaNo], [BranchNo], [CustName], [OperateDate])
SELECT [订单].[配方编号],'NB',[订单].[客户名称],getDate()
FROM 订单
WHERE [订单].[客户名称] is not null AND [订单].[客户名称] <> ''

--5.材料表：TBL_StockMaterial
INSERT INTO TBL_StockMaterial
   (MaterialNo, Price,UpLimit,LowerLimit)
SELECT [材料编号], [成本单价], 0, 0 FROM [材料]

--6.(**)色卡表：TBL_ColorModel
INSERT INTO TBL_ColorModel
([ColorModelNo], [LightSource], [PerWeight], [PantoneNo], [Inspection], [ComputerTest], [Maker], [ModelMakeDate], [Checker], [Conclude],
 [HasOrder], [IsSchemaMade],[IsConfirmed], [BranchNo], [Operator], [OperateDate], [IsOffline],   [SchemaColorNo], [SchemaID], [SheetId])
SELECT [配方编号], [光源], [每份重量],  [国际色号], [目测],  [电脑测试(△E)], [制作者], [制版日期], [复核者], [结论描述] , 1,1,1,'NB','NB-00',getDate(),0,-1,-1,-1
--[客户名称], [所用塑料], [颜色],  
FROM [颜色卡]

--7.订单表:TBL_Order

INSERT INTO TBL_Order
( [OrderNo], [CustName], [SchemaNo], [IsOrder], [IsConfirmed], [FillTimes], [FillQuantity], [OrderQuantity], [DeliveryWay], [DeliveryDate],
[TechnicalMemo], [BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline], [IsNewDrawOut], [DevelopType], [SpeciModel], [Units],
[IsDelete],[IsBranchOut] )
SELECT [订单编号],[客户名称],[配方编号], 1,1, [配料次数], [生产数量], [生产数量],0, [交货日期], [技术要求],'NB','','NB-00',getdate(),1,1,100,
[配方编号],'份',0,0 FROM [订单]

--10.(**)选择配方编号最大值：(手工选择)
(1)找出最大值*M*
SELECT [SchemaNo]FROM [TBL_Schema]
WHERE
 ISNUMERIC(SchemaNo) = 1
ORDER BY CAST([SchemaNo] AS int) DESC

(2)将最大值*M*写入TBL_ColorModel

INSERT INTO TBL_ColorModel
( [ColorModelNo], [SchemaID], [HasOrder], [IsConfirmed], [BranchNo], [Operator], [OperateDate], [IsOffline], [SchemaColorNo], [SheetId])
VALUES( <*M*>,<*M*>,1,1,'YY','YY-01',getDate(),0,'-1',-1)

 

--11.正式导入数据
INSERT INTO [WebErp].[dbo].[TBL_Customer]
([CustomerUID], [CustName], [CustAlias], [Contacter], [Mobile], [TelephoneNo], [FaxNo], [Bank], [BalanceNo], [TaxNo], [ContactAddr], [Memo],
[BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline])
SELECT [CustomerUID], [CustName], [CustAlias], [Contacter], [Mobile], [TelephoneNo], [FaxNo], [Bank], [BalanceNo], [TaxNo], [ContactAddr], [Memo], [BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline]
FROM [TBL_Customer]

INSERT INTO [WebErp].[dbo].[TBL_ColorModel]
( [SchemaColorNo], [SchemaID], [SheetId], [ColorModelNo], [LightSource], [PerWeight], [PantoneNo], [Inspection], [ComputerTest], [Maker],
[ModelMakeDate], [Checker], [Conclude], [HasOrder], [IsConfirmed], [BranchNo], [Operator], [OperateDate], [IsOffline] )
SELECT [SchemaColorNo], [SchemaID], [SheetId], [ColorModelNo], [LightSource], [PerWeight], [PantoneNo], [Inspection], [ComputerTest],
[Maker], [ModelMakeDate], [Checker], [Conclude], [HasOrder], [IsConfirmed], [BranchNo], [Operator], [OperateDate], [IsOffline]
FROM [TBL_ColorModel]

INSERT INTO [WebErp].[dbo].[TBL_Schema]
( [SchemaNo], [SchemaColorNo], [ModelMakeDate], [Weight], [NetWeight], [Price], [PantoneNo], [Color], [PriceClass], [LightSource], [Plastic],
[Using], [IsColorModelMade], [DevelopType], [HasOrder], [Maker], [CustName], [Memo], [IsConfirmed], [BranchNo], [Operator], [OperateDate],
[IsOffline] )
SELECT  [SchemaNo], [SchemaColorNo], [ModelMakeDate], [Weight], [NetWeight], [Price], [PantoneNo], [Color], [PriceClass], [LightSource],
[Plastic], [Using], [IsColorModelMade], [DevelopType], [HasOrder], [Maker], [CustName], [Memo], [IsConfirmed], [BranchNo], [Operator],
[OperateDate], [IsOffline]
FROM [TBL_Schema]

INSERT INTO [WebErp].[dbo].[TBL_SchemaDetail]
([SchemaNo], [BranchNo], [MaterialNo], [MaterialWeight], [CurMoney], [IsAppend], [MaterialMemo], [Operator], [OperateDate], [IsOffline] )
SELECT [SchemaNo], [BranchNo], [MaterialNo], [MaterialWeight], [CurMoney], [IsAppend], [MaterialMemo], [Operator], [OperateDate], [IsOffline]
FROM [TBL_SchemaDetail]

INSERT INTO [WebErp].[dbo].[TBL_SchemaCustom]
([SchemaNo], [BranchNo], [CustName], [OperateDate])
SELECT Distinct [SchemaNo], [BranchNo], [CustName], getdate()
FROM [TBL_SchemaCustom]

INSERT INTO [WebErp].[dbo].[TBL_StockMaterial]
( [MaterialNo], [Type], [Special], [Unit], [UpLimit], [LowerLimit], [IsMix], [PeriodStartAmount], [Price], [SortOrder], [Memo] )
SELECT [MaterialNo], [Type], [Special], [Unit], [UpLimit], [LowerLimit], [IsMix], [PeriodStartAmount], [Price], [SortOrder], [Memo]
FROM [TBL_StockMaterial]

INSERT INTO [WebErp].[dbo].[TBL_Order]
( [OrderNo], [CustName], [SchemaNo], [IsOrder], [IsConfirmed], [FillTimes], [FillQuantity], [OrderQuantity], [DeliveryWay], [DeliveryDate],
[TechnicalMemo], [BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline], [IsNewDrawOut], [OrderdrawoutMemo], [BillNo],
[DevelopType], [ProductName], [SpeciModel], [Units], [UnitPrice], [SaleMoney] )
SELECT [OrderNo], [CustName], [SchemaNo], [IsOrder], [IsConfirmed], [FillTimes], [FillQuantity], [OrderQuantity], [DeliveryWay], [DeliveryDate], [TechnicalMemo], [BranchNo], [BusiDeptNo], [Operator], [OperateDate], [IsOffline], [IsNewDrawOut], [OrderdrawoutMemo], [BillNo],
[DevelopType], [ProductName], [SpeciModel], [Units], [UnitPrice], [SaleMoney] FROM [TBL_Order]