400 lines
20 KiB
Transact-SQL
400 lines
20 KiB
Transact-SQL
|
||
DELETE FROM dbo.FilesManagement
|
||
DELETE FROM dbo.Score_JournalEvaluation
|
||
DELETE FROM dbo.FC_Score
|
||
DELETE FROM dbo.FC_SESRelatedData
|
||
|
||
DELETE FROM [dbo].[FC_SESReport]
|
||
|
||
DELETE FROM [dbo].[FC_SESReportToCPT]
|
||
DELETE FROM [dbo].[SESList]
|
||
DELETE FROM [dbo].[CPTList]
|
||
DELETE FROM [dbo].[SSR]
|
||
|
||
----DELETE FROM dbo.Sys_UserToEMial
|
||
|
||
--==注：必须先把原表里面用户信息拉取过来后才可以进行数据抓取，角色相关信息按照客户要求进行新增==--
|
||
--==注：确保数据插入表为空才可以执行，只可执行一次，单条执行，切记别统一执行==--
|
||
--==脚本单条执行，需为空库，切记不可拿有数据的库进行导入，目前只有业务相关数据，必须先把原表的用户信息全部拉取过来后才可以进行执行
|
||
|
||
--==小星星评价==--
|
||
truncate table FCLDB..FC_Score
|
||
insert into FCLDB..FC_Score(Contract_No,Score1,Score2,Score3,Score4,Score5,Score6,Score7,
|
||
Timely,Honesty,UserId,[Role],FileID,Remark,Remark1,DateIn,UserName,RoleId)
|
||
select s.Contract_No,s.Score1,s.Score2,s.Score3,s.Score4,s.Score5,s.Score6,s.Score7,
|
||
0 Timely,0 Honesty,
|
||
(CASE WHEN (SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=s.[By]) IS NULL THEN ''
|
||
ELSE (SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=s.[By]) END) UserId,
|
||
s.[Role],s.FileID,s.Remark,s.Remark1,s.DateIn,s.[By] UserName,'' RoleId
|
||
FROM CPT..FC_Score as s
|
||
|
||
--附件
|
||
insert into FCLDB..FilesManagement(FileId,FO,FileName,FileType,FileLength,UploadUser,UploadDate,Remark,FileUrl)
|
||
select NewId() FileId,f.Contract_NO FO,f.[FileName],f.FileType,f.FileLength,f.UploadUser,f.DateIn
|
||
UploadDate,f.Remark,'File/'+f.FileID
|
||
FROM CPT..FC_File as f
|
||
--INNER join CPT..FC_Score as s on f.Contract_NO=s.Contract_No
|
||
|
||
--评价日志
|
||
truncate table FCLDB..Score_JournalEvaluation
|
||
insert into FCLDB..Score_JournalEvaluation(SES_No,Evaluate_UserId,Evaluate_UserName,Evaluate_Time,Contract_No,Role,Remark)
|
||
SELECT a.SES_No,(SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=a.[By]) Evaluate_UserId,
|
||
[By] Evaluate_UserName,DateIn Evaluate_Time,a.Contract_No,a.[Role],a.Remark
|
||
FROM CPT..FC_SESRecord as a
|
||
--==基础信息==--
|
||
--Base_Contractor
|
||
|
||
--insert into FCLDB..Base_Contractor(ContractorId,Contractor,VendorNumber)
|
||
--select NEWID() ContractorId,Contractor,Vendor_NO
|
||
-- FROM CPT..FC_SESRelatedData where isnull(Vendor_NO,'')!=''
|
||
-- GROUP by Contractor,Vendor_NO
|
||
|
||
--Base_PriceScheme
|
||
--insert into FCLDB..Base_PriceScheme(PriceSchemeId,PriceScheme)
|
||
--select NEWID() PriceSchemeId,Pricing_Scheme from CPT..FC_SESRelatedData where ISNULL(Pricing_Scheme,'')!='' group by Pricing_Scheme
|
||
|
||
--Base_Item
|
||
--insert into FCLDB..Base_Item(ItemId,Item)
|
||
--select NEWID() as ItemId,Item from CPT..FC_SESRelatedData where ISNULL(Item,'')!='' group by Item
|
||
|
||
--Base_Currency
|
||
--insert into FCLDB..Base_Currency(CurrencyId,Currency)
|
||
--select NEWID() CurrencyId,Currency from CPT..FC_SESRelatedData where isnull(Currency,'')!='' group by Currency
|
||
|
||
--Base_Type
|
||
--insert into FCLDB..Base_Type(TypeId,[Type])
|
||
--select NEWID(),[Type] from CPT..FC_SESRelatedData where isnull([Type],'')!='' group by [Type]
|
||
--GO
|
||
|
||
--在提取FC_SESRelatedData数据时选更新不规范的CPT
|
||
UPDATE CPT..FC_SESRelatedData SET Vendor_NO='2315246' WHERE Vendor_NO='2520280'
|
||
GO
|
||
UPDATE CPT..FC_SESRelatedData SET Vendor_NO='0000000' WHERE Vendor_NO=''
|
||
GO
|
||
UPDATE CPT..FC_SESRelatedData SET Vendor_NO='2306803' WHERE Vendor_NO='On-call'
|
||
GO
|
||
|
||
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Cui GuangMing' WHERE Main_Coordinator='Cui Guangmin'
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Hou yingyan' WHERE Main_Coordinator='Houyingyan'
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='wu jiazhao' WHERE Main_Coordinator='wu jiazhao Yang Gang'
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Ben guoping' WHERE Main_Coordinator='Ben guopin'
|
||
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Dong LuJin' WHERE Main_Coordinator='Donglujin'
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Jiang Peng' WHERE Main_Coordinator='jiangpeng'
|
||
UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Jiang Peng' WHERE Main_Coordinator='jiangpeng'
|
||
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='Tong ShaoJian' WHERE User_Representative='Tong Shaojian'
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='Xu PanPan' WHERE User_Representative='Xu Panpan'
|
||
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='Cui GuangMing' WHERE User_Representative='Cui Guangmin'
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='Wen WeiPing' WHERE User_Representative='Wen Weipng'
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='' WHERE User_Representative='liyinghua'
|
||
UPDATE CPT..FC_SESRelatedData SET User_Representative='' WHERE User_Representative='N/A'
|
||
GO
|
||
|
||
--FC_SESRelatedData
|
||
|
||
truncate table FCLDB.dbo.FC_SESRelatedData
|
||
insert into FCLDB..FC_SESRelatedData(FO_NO,
|
||
Contract_Title,
|
||
Contractor,
|
||
Pricing_Scheme,
|
||
Item,
|
||
Material_Group,
|
||
Purchase_Group,
|
||
Cost_Element,
|
||
Vendor_NO,
|
||
Currency,
|
||
Original_WC,
|
||
Type,
|
||
Contract_Admin,
|
||
Buyer,
|
||
Main_Coordinator,
|
||
User_Representative,
|
||
Applicant,
|
||
Validate_Date,
|
||
Expire_Date,
|
||
FC_Status,
|
||
Contract_Person,
|
||
Contract_Tel,
|
||
Total_Budget,
|
||
Proportion_of_FC_Definition,
|
||
Actual_Budget,
|
||
DateIn,
|
||
Remark,
|
||
Remark1,
|
||
OldSAPData,
|
||
ConstRecords)
|
||
|
||
SELECT a.FO_NO,
|
||
a.Contract_Title,
|
||
(CASE WHEN a.Vendor_NO!='0000000' THEN (select top 1 c.ContractorId from Base_Contractor c where c.VendorNumber=a.Vendor_NO)
|
||
ELSE
|
||
(select top 1 c.ContractorId from Base_Contractor c where charindex(rtrim(ltrim(c.ContractorCN)),a.Contractor)>0
|
||
OR charindex(rtrim(ltrim(c.Contractor)), a.Contractor)>0) END) AS Contractor,
|
||
p.PriceSchemeId as FC_Price_Scheme,
|
||
i.ItemId,
|
||
a.Material_Group,
|
||
a.Purchase_Group,
|
||
a.Cost_Element,
|
||
a.Vendor_NO,
|
||
c.CurrencyId,
|
||
a.Original_WC,
|
||
t.TypeId,
|
||
--ca.UserId as Contract_Admin,
|
||
(SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE u.UserName= a.Contract_Admin ) as Contract_Admin,
|
||
a.Buyer,
|
||
--mc.UserId as Main_Coordinator,
|
||
--ur.UserId as User_Representative,
|
||
(SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE u.UserName= a.Main_Coordinator) as Main_Coordinator,
|
||
(SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE LOWER(u.UserName)= LOWER(a.User_Representative)) as User_Representative,
|
||
a.Applicant,
|
||
a.Validate_Date,
|
||
a.Expire_Date,
|
||
a.FC_Status,
|
||
a.Contract_Person,
|
||
a.Contract_Tel,
|
||
a.Total_Budget,
|
||
a.Proportion_of_FC_Definition,
|
||
a.Actual_Budget,
|
||
a.DateIn,
|
||
a.Remark,
|
||
a.Remark1,
|
||
a.OldSAPData,
|
||
'N' as ConstRecords
|
||
from CPT..FC_SESRelatedData as a
|
||
left join FCLDB..Base_PriceScheme as p on p.PriceScheme = a.Pricing_Scheme
|
||
left join FCLDB..Base_Item as i on i.Item = a.Item
|
||
left join FCLDB..Base_Currency as c on c.Currency = a.Currency
|
||
left join FCLDB..Base_Type as t on t.Type = a.Type
|
||
|
||
|
||
GO
|
||
|
||
|
||
--==FC_SESReport
|
||
truncate table FCLDB.dbo.FC_SESReport
|
||
insert into FCLDB.dbo.FC_SESReport(SES_No,Short_Descrption,Start_Date,End_Date,Created_by,
|
||
Created_on,TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline,
|
||
SSR_budget,Currency,Contractor_quotation,SSR_Actual_cost,Cost_checker,Tax_rate,
|
||
Changed_by,Deviation,Deviation_Percentage,Overrun,Long_text,Work_Order,Function_location,
|
||
Main_work_center,Work_Center,Dep,Section,Cost_center,WBS,Network,TODAY,Claim_sheets_receive,
|
||
CS_REC_Format,No_SUBM_To_today,Contractor_duration,Engineer_confirmed_o,ENG_CONF_Format,
|
||
No_BoQ_CONF_to_today,BoQ_confirmation_dur,SES_Confirmed_on,SES_CONF_Format,No_SES_to_today,
|
||
Settlement_duration,Invoiced_on,Invoice_duration,Payment_made_on,Payment_duration,DateIn,
|
||
Remark1,Remark2,Accepted,Deleted,Blocked)
|
||
SELECT SES_No,Short_Descrption,
|
||
(case ISDATE((case ISNULL(Start_Date,'') when '' then null
|
||
ELSE ((case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,7,4) end)
|
||
+'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,4,2) end)
|
||
+'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,1,2) end)) end))
|
||
WHEN 1 then (case ISNULL(Start_Date,'') when '' then null else ((case ISNULL(Start_Date,'')
|
||
WHEN '' then '' else SUBSTRING(Start_Date,7,4) end)
|
||
+'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,4,2) end)
|
||
+'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,1,2) end)) end)
|
||
ELSE null end) as Start_Date,
|
||
(case ISDATE((case ISNULL(End_Date,'') when '' then null
|
||
ELSE ((case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,7,4) end)
|
||
+'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,4,2) end)
|
||
+'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,1,2) end)) end))
|
||
WHEN 1 then (case ISNULL(End_Date,'') when '' then null
|
||
ELSE ((case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,7,4) end)
|
||
+'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,4,2) end)
|
||
+'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,1,2) end)) end)
|
||
ELSE null end) as End_Date,
|
||
Created_by,
|
||
(case ISDATE((case ISNULL(Created_on,'') when '' then null
|
||
ELSE ((case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,7,4) end)
|
||
+'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,4,2) end)
|
||
+'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,1,2) end)) end))
|
||
WHEN 1 then (case ISNULL(Created_on,'') when '' then null
|
||
ELSE ((case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,7,4) end)
|
||
+'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,4,2) end)
|
||
+'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,1,2) end)) end)
|
||
ELSE null end) as Created_on,
|
||
TECO_Format as TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline,
|
||
(case ISNUMERIC((case ISNULL(SSR_budget,'') when '' then null
|
||
ELSE REPLACE(REPLACE(SSR_budget, '.', ''),',','.') end))
|
||
WHEN 1 then (case ISNULL(SSR_budget,'') when '' then null
|
||
ELSE REPLACE(REPLACE(SSR_budget, '.', ''),',','.') end)
|
||
ELSE null end) SSR_budget,
|
||
Currency,
|
||
(case ISNULL(Contractor_quotation,'') when '' then null else Contractor_quotation end) as Contractor_quotation,
|
||
(case ISNUMERIC((case ISNULL(SSR_budget,'') when '' then null
|
||
ELSE REPLACE(REPLACE(SSR_Actual_cost, '.', ''),',','.') end))
|
||
WHEN 1 then (case ISNULL(SSR_budget,'') when '' then null
|
||
ELSE REPLACE(REPLACE(SSR_Actual_cost, '.', ''),',','.') end)
|
||
ELSE null end) as SSR_Actual_cost,
|
||
Cost_checker,
|
||
(case ISNULL(Tax_rate,'') when '' then null else Tax_rate end) as Tax_rate,
|
||
Changed_by,
|
||
(case ISNUMERIC((case ISNULL(Deviation,'') when '' then null
|
||
ELSE REPLACE(REPLACE(Deviation, '.', ''),',','.') end))
|
||
WHEN 1 then (case ISNULL(Deviation,'') when '' then null
|
||
ELSE REPLACE(REPLACE(Deviation, '.', ''),',','.') end) else null end)
|
||
AS Deviation,
|
||
(case ISNUMERIC((case ISNULL(Deviation_Percentage,'') when '' then null
|
||
ELSE REPLACE(REPLACE(Deviation_Percentage, '.', ''),',','.') end))
|
||
WHEN 1 then (case ISNULL(Deviation_Percentage,'') when '' then null
|
||
ELSE REPLACE(REPLACE(Deviation_Percentage, '.', ''),',','.') end) else null end)
|
||
AS Deviation_Percentage,
|
||
Overrun,Long_text,Work_Order,Function_location,Main_work_center,
|
||
Work_Center,Dep,Section,Cost_center,WBS,Network,
|
||
(case ISDATE(TODAY) when 1 then TODAY else null end) as TODAY,
|
||
CS_REC_Format as Claim_sheets_receive,
|
||
CS_REC_Format,No_SUBM_To_today,Contractor_duration,
|
||
ENG_CONF_Format as Engineer_confirmed_o,ENG_CONF_Format,No_BoQ_CONF_to_today,
|
||
BoQ_confirmation_dur,SES_CONF_Format as SES_Confirmed_on,SES_CONF_Format,
|
||
No_SES_to_today,Settlement_duration,
|
||
(case ISDATE((case ISNULL(Invoiced_on,'') when '' then null
|
||
ELSE ((case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,7,4) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,4,2) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,1,2) end)) end)) when 1 then (case ISNULL(Invoiced_on,'')
|
||
WHEN '' then null else ((case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,7,4) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,4,2) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then ''
|
||
ELSE SUBSTRING(Invoiced_on,1,2) end)) end) else null end) as Invoiced_on,
|
||
Invoice_duration,
|
||
(case ISDATE((case ISNULL(Payment_made_on,'') when '' then null
|
||
ELSE ((case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,7,4) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,4,2) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,1,2) end)) end))
|
||
WHEN 1 then (case ISNULL(Payment_made_on,'') when '' then null
|
||
ELSE ((case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,7,4) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,4,2) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then ''
|
||
ELSE SUBSTRING(Payment_made_on,1,2) end)) end) else null end) as Payment_made_on,
|
||
Payment_duration,DateIn,Remark1,Remark2,Accepted,Deleted,Blocked
|
||
from CPT.dbo.FC_SESReport
|
||
|
||
GO
|
||
|
||
--==CPT==--
|
||
--CPTList
|
||
truncate table FCLDB..CPTList
|
||
insert into FCLDB..CPTList(CPT_No,Contract_No,CA,Contractor,
|
||
FC_Desctription,FC_Price_Scheme,FC_Start_Date,FC_End_Date,Net_Amount,Tax_Amount,
|
||
Tax,Currency,CT_Director,CT_GM,Comment,Report_Date,Remark,UserId,Last_Payment)
|
||
select a.CPT_No,a.Contract_No,a.CA,
|
||
(select top 1 c.ContractorId from Base_Contractor c where charindex(rtrim(ltrim(c.ContractorCN)), a.Contractor)>0
|
||
or charindex(rtrim(ltrim(c.Contractor)), a.Contractor)>0) as Contractor,
|
||
a.FC_Desctription,
|
||
p.PriceSchemeId as FC_Price_Scheme,
|
||
(case ISDATE(a.FC_Start_Date) when 1 then a.FC_Start_Date else null end) as FC_Start_Date,
|
||
(case ISDATE(a.FC_End_Date) when 1 then a.FC_End_Date else null end) as FC_Start_Date,
|
||
(case ISNUMERIC(REPLACE(a.Net_Amount, ',', '')) when 1 then REPLACE(a.Net_Amount, ',', '') else null end) Net_Amount,
|
||
(case ISNUMERIC(REPLACE(a.Tax_Amount, ',', '')) when 1 then REPLACE(a.Tax_Amount, ',', '') else null end) Tax_Amount,
|
||
a.Tax,a.Currency,
|
||
(select top 1 u.UserId from Sys_User u where charindex(rtrim(ltrim(u.ChineseName)), a.CT_Director)>0
|
||
or charindex(rtrim(ltrim(u.UserName)), a.CT_Director)>0) as CT_Director,
|
||
a.CT_GM,Comment,a.Report_Date,a.Remark,
|
||
(SELECT TOP 1 x.Contract_Admin FROM FCLDB..FC_SESRelatedData x WHERE x.FO_NO=a.Contract_No) AS UserId,
|
||
'Y' Last_Payment
|
||
from CPT..CPTList as a
|
||
--left join FCLDB..FC_SESRelatedData as b on a.Contract_No=b.FO_NO
|
||
left join FCLDB..Base_PriceScheme as p on p.PriceScheme = a.FC_Price_Scheme
|
||
GO
|
||
|
||
|
||
--SESList
|
||
truncate table FCLDB..SESList
|
||
insert into FCLDB..SESList(SES,CPT_No,Requistioner,Short_Description,Start_Date,End_Date,
|
||
Budget,Quotation,Net_Value,Tax_Value,Deviation,By_Perc,Deduction,Con_Days,BoQ_Days,
|
||
SES_Days,Submit_Date,Remark,UserId)
|
||
select a.SES,'CPT-'+(b.Contract_No)+SUBSTRING(a.CPT_No,10,LEN(a.CPT_No)) AS CPT_No,
|
||
a.Requistioner,a.Short_Description,
|
||
(case ISNULL(a.Start_Date,'') when '' then null
|
||
ELSE ((case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,7,4) end)
|
||
+'-'+(case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,4,2) end)
|
||
+'-'+(case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,1,2) end))
|
||
END) as Start_Date,
|
||
(case ISNULL(a.End_Date,'') when '' then null else ((case ISNULL(a.End_Date,'') when '' then ''
|
||
ELSE SUBSTRING(a.End_Date,7,4) end)+'-'+(case ISNULL(a.End_Date,'') when '' then ''
|
||
ELSE SUBSTRING(a.End_Date,4,2) end)+'-'+(case ISNULL(a.End_Date,'') when '' then ''
|
||
ELSE SUBSTRING(a.End_Date,1,2) end)) end) as End_Date,
|
||
a.Budget,a.Quotation,a.Net_Value,a.Tax_Value,a.Deviation,
|
||
(case when ISNUMERIC (By_Perc)>0 then CONVERT(DECIMAL(6,2),By_Perc)*100 ELSE NULL END) AS By_Perc,
|
||
a.Deduction,a.Con_Days,
|
||
a.BoQ_Days,a.SES_Days,a.Submit_Date,a.Remark,c.Contract_Admin UserId
|
||
FROM CPT..SESList as a
|
||
INNER join CPT..CPTList as b on a.CPT_No=b.CPT_No
|
||
LEFT join FCLDB..FC_SESRelatedData as c on b.Contract_No=c.FO_NO
|
||
GO
|
||
|
||
|
||
|
||
--FC_SESReportToCPT(一定切记，FC_SESReport表里面的数据导入到系统里面后才可以执行，FC_SESReport表中数据是由SES report菜单导入进去的)
|
||
--因为FC_SESReport表中没回导入的都是最新的，所以不需要去原表里面抓取数据，FC_SESReportToCPT表是新增的表，目的是为了区分SES和CPT的SES
|
||
--切记，切记，切记，数据一定导入到系统后才可以执行此导入脚本，只可执行一次
|
||
insert into FCLDB..FC_SESReportToCPT(SES_No,Short_Descrption,Start_Date,End_Date,Created_by,Created_on,
|
||
TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline,SSR_budget,Currency,
|
||
Contractor_quotation,SSR_Actual_cost,Cost_checker,Tax_rate,Changed_by,Deviation,
|
||
Deviation_Percentage,Overrun,Long_text,Work_Order,Function_location,Main_work_center,
|
||
Work_Center,Dep,Section,Cost_center,WBS,Network,TODAY,Claim_sheets_receive,CS_REC_Format,
|
||
No_SUBM_To_today,Contractor_duration,Engineer_confirmed_o,ENG_CONF_Format,No_BoQ_CONF_to_today,
|
||
BoQ_confirmation_dur,SES_Confirmed_on,SES_CONF_Format,No_SES_to_today,Settlement_duration,
|
||
Invoiced_on,Invoice_duration,Payment_made_on,Payment_duration,DateIn,Remark1,Remark2,
|
||
Accepted,Deleted,Blocked,User_field,Reduced_by_quantity,UserId)
|
||
select a.SES_No,a.Short_Descrption,a.Start_Date,a.End_Date,a.Created_by,a.Created_on,
|
||
a.TECO_Date,a.TECO_Format,a.Requisitioner,a.FO,a.Item,a.Vendor_Name,a.Discipline,a.SSR_budget,a.Currency,
|
||
a.Contractor_quotation,a.SSR_Actual_cost,a.Cost_checker,a.Tax_rate,a.Changed_by,a.Deviation,
|
||
a.Deviation_Percentage,a.Overrun,a.Long_text,a.Work_Order,a.Function_location,a.Main_work_center,
|
||
a.Work_Center,a.Dep,a.Section,a.Cost_center,a.WBS,a.Network,a.TODAY,a.Claim_sheets_receive,a.CS_REC_Format,
|
||
a.No_SUBM_To_today,a.Contractor_duration,a.Engineer_confirmed_o,a.ENG_CONF_Format,a.No_BoQ_CONF_to_today,
|
||
a.BoQ_confirmation_dur,a.SES_Confirmed_on,a.SES_CONF_Format,a.No_SES_to_today,a.Settlement_duration,
|
||
a.Invoiced_on,a.Invoice_duration,a.Payment_made_on,a.Payment_duration,a.DateIn,a.Remark1,a.Remark2,
|
||
a.Accepted,a.Deleted,a.Blocked,a.User_field,a.Reduced_by_quantity,d.Contract_Admin UserId
|
||
FROM FCLDB..FC_SESReport as a
|
||
inner join CPT..SESList as b on b.SES=a.SES_No
|
||
left join CPT..CPTList as c on c.CPT_No=b.CPT_No
|
||
left join FCLDB..FC_SESRelatedData as d on d.FO_NO=c.Contract_No
|
||
|
||
GO
|
||
|
||
--OEM
|
||
UPDATE dbo.FC_SESRelatedData SET FOTypeId='58544360-5d26-4358-8459-928aa5f6e3ab'
|
||
WHERE Type='8DA3CA5A-1BE2-4B8E-B794-3DCA8FEF9D6E'
|
||
GO
|
||
|
||
--TAR
|
||
UPDATE dbo.FC_SESRelatedData SET FOTypeId='eb846026-639f-4063-8d3d-a253905ddfe0'
|
||
WHERE FO_NO IN ('4955869592','4948283631','4948283628','4944996032','4944996029',
|
||
'4955272920','4954367650','4954367649','4953576899','4953576900','4953576898',
|
||
'4945360915','4945360916','4945360918','4949702138','4950678523','4950868006',
|
||
'4950735378','4947530079','4952257851','4950735399','4954176265','4954367647',
|
||
'4954367646','4954248437','4954248438','4954248439','4955057488','4955590479',
|
||
'4954610379','4954610377','4954546470','4954546471','4954546472','4954967671',
|
||
'4955305910','4956544261')
|
||
GO
|
||
|
||
--FC
|
||
UPDATE dbo.FC_SESRelatedData SET FOTypeId='e3dd9ecc-4474-4e94-85ff-22dc073fd8b0'
|
||
WHERE FOTypeId IS NULL
|
||
GO
|
||
|
||
UPDATE dbo.FC_SESRelatedData SET Original_WC= SUBSTRING(Original_WC,1,3)
|
||
GO
|
||
|
||
UPDATE dbo.FC_SESRelatedData SET DisciplineId=
|
||
(SELECT TOP 1 DisciplineId FROM dbo.Base_Discipline d WHERE charindex(rtrim(ltrim(d.Discipline)), Contract_Title)>0)
|
||
GO
|
||
|
||
UPDATE dbo.FC_SESRelatedData SET Contractor='335C267D-24E7-4125-8FE7-6A0E8F05BC58' WHERE FO_NO='A546061365'
|
||
GO
|
||
|
||
--删除空SES_No为空的记录
|
||
DELETE FROM dbo.SSR WHERE (SES_No IS NULL OR SES_No='')
|
||
GO
|
||
|
||
UPDATE dbo.CPTList SET Contractor =(SELECT TOP 1 f.Contractor FROM dbo.FC_SESRelatedData f WHERE f.FO_NO=CPTList.Contract_No )
|
||
WHERE CPTList.Contractor IS NULL
|
||
GO
|
||
|
||
UPDATE dbo.CPTList SET CPT_No='CPT-'+Contract_No+SUBSTRING(CPT_No,10,LEN(CPT_No))
|
||
GO
|
||
|
||
UPDATE dbo.SESList SET CPT_No='CPT-'+(SELECT TOP 1 fo FROM dbo.FC_SESReportToCPT c WHERE c.SES_No=SES)+SUBSTRING(CPT_No,10,LEN(CPT_No))
|
||
GO |