shecs
/
Basf_FCL
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Basf_FCL/DataBase/数据迁移.sql

400 lines
20 KiB
MySQL
Raw Permalink Normal View History

initFCL
2024-05-08 10:17:02 +08:00
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
--==ע<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ȱ<EFBFBD>ԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>û<EFBFBD><EFBFBD><EFBFBD>Ϣ<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ſ<EFBFBD><EFBFBD>Խ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ץȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϣ<EFBFBD><EFBFBD><EFBFBD>տͻ<EFBFBD>Ҫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>==--
--==ע<EFBFBD><EFBFBD>ȷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݲ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊ<EFBFBD>ղſ<EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>У<EFBFBD>ֻ<EFBFBD><EFBFBD>ִ<EFBFBD><EFBFBD>һ<EFBFBD>Σ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>У<EFBFBD><EFBFBD>мDZ<EFBFBD>ͳһִ<EFBFBD><EFBFBD>==--
--==<EFBFBD>ű<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>У<EFBFBD><EFBFBD><EFBFBD>Ϊ<EFBFBD>տ⣬<EFBFBD>мDz<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݵĿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>е<EFBFBD><EFBFBD>룬Ŀǰֻ<EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ȱ<EFBFBD>ԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>û<EFBFBD><EFBFBD><EFBFBD>Ϣȫ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ſ<EFBFBD><EFBFBD>Խ<EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD><EFBFBD>
--==С<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>==--
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
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>־
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
--==<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϣ==--
--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
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡFC_SESRelatedData<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʱѡ<EFBFBD><EFBFBD><EFBFBD>²<EFBFBD><EFBFBD>淶<EFBFBD><EFBFBD>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(һ<EFBFBD><EFBFBD><EFBFBD>мǣ<EFBFBD>FC_SESReport<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݵ<EFBFBD><EFBFBD>뵽ϵͳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ſ<EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>У<EFBFBD>FC_SESReport<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>SES report<EFBFBD>˵<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȥ<EFBFBD><EFBFBD>)
--<EFBFBD><EFBFBD>ΪFC_SESReport<EFBFBD><EFBFBD><EFBFBD><EFBFBD>û<EFBFBD>ص<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ķ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>µģ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Բ<EFBFBD><EFBFBD><EFBFBD>Ҫȥԭ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ץȡ<EFBFBD><EFBFBD><EFBFBD>ݣ<EFBFBD>FC_SESReportToCPT<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ı<EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ϊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>SES<EFBFBD><EFBFBD>CPT<EFBFBD><EFBFBD>SES
--<EFBFBD>мǣ<EFBFBD><EFBFBD>мǣ<EFBFBD><EFBFBD>мǣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>뵽ϵͳ<EFBFBD><EFBFBD><EFBFBD>ſ<EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>д˵<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ű<EFBFBD><EFBFBD><EFBFBD>ֻ<EFBFBD><EFBFBD>ִ<EFBFBD><EFBFBD>һ<EFBFBD><EFBFBD>
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
--ɾ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>SES_NoΪ<EFBFBD>յļ<EFBFBD>¼
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