更新系統檔案信息

/第一步:插入除了手機的商品信息到檔案/
declare @sql varchar(max)
set @sql=(select max(FEntryID) FEntryID from PAEZ_t_xsxxxx where FEntryID>1)
--INSERT INTO PAEZ_t_xsxxxx(F_ENTRYID_JF,fid,fentryid,F_PAEZ_DATE,F_PAEZ_DJLX,F_PAEZ_MD,F_PAEZ_PRICE,F_PAEZ_SPDM,F_PAEZ_SPMC1,F_PAEZ_XSFBILLNO,F_PAEZ_XSJE,F_PAEZ_XSSL,F_PAEZ_qy)
select FEntryID,t16.fid,isnull(@sql,'1001')+ROW_NUMBER() over(order by t0.fbillno) ggg,convert(varchar(10),t0.F_PBKA_DATE,21),t12.fname,t11.fname,FDiscountPrice,t2.FNUMBER,t3.fname,t0.fbillno,case when t0.FBILLTYPEID='57faf492b10d31'
then FSDJAmount else -FSDJAmount end,case when t0.FBILLTYPEID='57faf492b10d31' then FQTY else -FQTY end ,tbtp.fname
From (select fid,F_PBKA_DATE,FBILLTYPEID,FOLDDEPARTMENT,rtrim(ltrim(F_PBKA_PHONE)) F_PBKA_PHONE,FBILLNO From PBKA_t_BillHead where fid>1) t0
left join(select fid,FSDJAmount,FQTY,FMATERIALID,FDiscountPrice,FEntryID from PBKA_t_GoodsDetail where FEntryID>1) t1 on t0.fid=t1.fid
left join (select fid,FLOCALEID from PBKA_t_BillHead_L where fid>1) t10 on t0.fid=t10.fid and t10.FLOCALEID=2052
left join (select FMATERIALID,FNUMBER from t_bd_material where FMATERIALID>1) t2 on t1.FMATERIALID=t2.FMATERIALID
left join (select FMATERIALID,fname From T_BD_MATERIAL_L where FMATERIALID>1 and FLocaleId=2052) t3 on t1.FMATERIALID=t3.FMATERIALID
inner join (select FDEPTID,fname from t_bd_department_l where FDEPTID>1 and FLocaleId=2052) t11 on t0.FOLDDEPARTMENT=t11.FDEPTID
inner join (select FBILLTYPEID,fname from T_BAS_BILLTYPE_L where FLocaleId=2052) t12 on t0.FBILLTYPEID =t12.FBILLTYPEID
inner join (select FMOBILEPHONE,fid From T_ECC_MEMBER where fid>1) t16 on F_PBKA_PHONE=t16.FMOBILEPHONE
left join (select FCATEGORYID,FMATERIALID From T_BD_MATERIALBASE where FCATEGORYID>1) m on m.FMATERIALID=t2.FMATERIALID
inner join (select FDEPTID,FGROUP from T_BD_DEPARTMENT) tbt on tbt.FDEPTID=t11.FDEPTID
inner join (select FDEPTID,fname from T_BD_DEPARTMENT_L where FLOCALEID=2052) tbtl on tbt.FDEPTID=tbtl.FDEPTID
inner join (select fid,fname from T_BD_DEPARTGROUP_L where FLOCALEID=2052) tbtp on tbt.FGROUP=tbtp.fid
where (t0.FBILLTYPEID ='580778655b6d7d' or t0.FBILLTYPEID ='57faf492b10d31') and m.FCATEGORYID<>'2092334' and FDiscountPrice<>0
and not exists (select F_ENTRYID_JF from PAEZ_t_xsxxxx where F_ENTRYID_JF<>'' and t1.FEntryID=F_ENTRYID_JF)
AND (CONVERT(VARCHAR(10),t0.F_PBKA_DATE,21)>=CONVERT(varchar(10), '2018-02-01', 21) AND CONVERT(VARCHAR(10),t0.F_PBKA_DATE,21)<=CONVERT(varchar(10), '2018-02-24', 21) )
--and fbillno='LSD180101393327'
order by fbillno

/第二步:/

---取今天的數據
select (select MAX(FID) FID From T_ECC_MEMBER)+ROW_NUMBER() over(order by fbillno) GGG
,'HY'+substring(t0.fbillno,3,20) xuhao,
F_PBKA_NAME,fbillno,
rtrim(ltrim(F_PBKA_PHONE)) F_PBKA_PHONE,F_PBKA_OLDSALORGID,'1001' FMEMBERTYPEID,'1002' FMEMBERLEVELID,'C' FDOCUMENTSTATUS,
FCREATORID,FCREATEDATE,FCREATORID FCREATORIDs,FCREATEDATE FCREATEDATEs,F_PBKA_CLIENT,F_PBKA_ID
into #uuu
From (select fbillno,rtrim(ltrim(F_PBKA_PHONE)) F_PBKA_PHONE,F_PBKA_OLDSALORGID,F_PBKA_CLIENT,F_PBKA_ID,FCREATEDATE,FCREATORID,fid from PBKA_t_BillHead where fid>1) t0
left join (select fid,F_PBKA_NAME from PBKA_t_BillHead_L where fid>1) t10 on t0.fid=t10.fid
where F_PBKA_PHONE<>'' and F_PBKA_PHONE not in(select distinct FMOBILEPHONE from T_ECC_MEMBER)
AND CONVERT(VARCHAR(10),FCREATEDATE,21)=CONVERT(varchar(10), getdate(), 21)

------顧客資料表去重電話插入臨時表--------
create table #dh (F_PBKA_PHONE varchar(100))
insert into #dh
select distinct rtrim(ltrim(F_PBKA_PHONE)) from #uuu
-------把要插入顧客資料表的信息去重處理----
CREATE TABLE #qc(fbillno varchar(100),F_PBKA_PHONE varchar(100))
insert into #qc
SELECT distinct MAX(fbillno) fbillno,F_PBKA_PHONE FROM #uuu GROUP BY F_PBKA_PHONE ORDER BY 2
---最終要插入的數據
select a.GGG, xuhao,a.fbillno,F_PBKA_NAME,a.F_PBKA_PHONE,F_PBKA_OLDSALORGID,FMEMBERTYPEID,FMEMBERLEVELID,FDOCUMENTSTATUS,FCREATORID,FCREATEDATE,FCREATORID FCREATORIDs,FCREATEDATE FCREATEDATEs,F_PBKA_CLIENT,F_PBKA_ID
into #uuu1
From #uuu a
inner join #qc b on a.FBILLNO=b.fbillno and a.F_PBKA_PHONE=b.F_PBKA_PHONE

select F_PBKA_NAME,GGG,xuhao,F_PBKA_PHONE,F_PBKA_OLDSALORGID,FMEMBERTYPEID,FMEMBERLEVELID,FDOCUMENTSTATUS,FCREATORID,FCREATEDATE,FCREATORID FCREATORIDs,FCREATEDATE FCREATEDATEs,F_PBKA_CLIENT,F_PBKA_ID
into #uuu2
FROM #uuu1
where F_PBKA_PHONE not in(select distinct FMOBILEPHONE from T_ECC_MEMBER)

---判斷檔案是否存在，插入主表
insert into T_ECC_MEMBER
(FID,FNUMBER ,FMOBILEPHONE ,FSOURCESTOREID ,FMEMBERTYPEID ,FMEMBERLEVELID ,FDOCUMENTSTATUS,FCREATORID ,FCREATEDATE ,FAPPROVERID ,FAPPROVEDATE,FSourceCustId,FFORBIDSTATUS,F_PAEZ_UPDATE,FCardNo,FCardType )
select GGG,xuhao,F_PBKA_PHONE,F_PBKA_OLDSALORGID,FMEMBERTYPEID,FMEMBERLEVELID,FDOCUMENTSTATUS,FCREATORID,FCREATEDATE,FCREATORID FCREATORIDs,FCREATEDATE FCREATEDATEs,F_PBKA_CLIENT,'A','WB',F_PBKA_ID,'2462D139-91B1-4722-AB9A-E87FCDDB63D7' From #uuu2
----end
-----插入從表
insert into T_ECC_MEMBER_L(fpkid,fid,FLOCALEID,FNAME)
select (SELECT MAX(FPKID) FPKID fROM T_ECC_MEMBER_L)+ROW_NUMBER() over(order by FNumber) ,FID,'2052',ISNULL(B.F_PBKA_NAME,'WU') From T_ECC_MEMBER a
INNER JOIN #uuu2 B ON A.FMOBILEPHONE=B.F_PBKA_PHONE
------消費明細更新
declare @sql varchar(max)
set @sql=(select max(FEntryID) FEntryID from PAEZ_t_xsxxxx where FEntryID>1)
INSERT INTO PAEZ_t_xsxxxx(F_ENTRYID_JF,fid,fentryid,F_PAEZ_DATE,F_PAEZ_DJLX,F_PAEZ_MD,F_PAEZ_PRICE,F_PAEZ_SPDM,F_PAEZ_SPMC1,F_PAEZ_XSFBILLNO,F_PAEZ_XSJE,F_PAEZ_XSSL,F_PAEZ_qy)
select FEntryID,t16.fid,isnull(@sql,'1001')+ROW_NUMBER() over(order by t0.fbillno) ggg,convert(varchar(10),t0.F_PBKA_DATE,21),t12.fname,t11.fname,FDiscountPrice,t2.FNUMBER,t3.fname,t0.fbillno,case when t0.FBILLTYPEID='57faf492b10d31'
then FSDJAmount else -FSDJAmount end,case when t0.FBILLTYPEID='57faf492b10d31' then FQTY else -FQTY end ,tbtp.fname
From (select fid,F_PBKA_DATE,FBILLTYPEID,FOLDDEPARTMENT,rtrim(ltrim(F_PBKA_PHONE)) F_PBKA_PHONE,FBILLNO From PBKA_t_BillHead where fid>1) t0
left join(select fid,FSDJAmount,FQTY,FMATERIALID,FDiscountPrice,FEntryID from PBKA_t_GoodsDetail where FEntryID>1) t1 on t0.fid=t1.fid
left join (select fid,FLOCALEID from PBKA_t_BillHead_L where fid>1) t10 on t0.fid=t10.fid and t10.FLOCALEID=2052
left join (select FMATERIALID,FNUMBER from t_bd_material where FMATERIALID>1) t2 on t1.FMATERIALID=t2.FMATERIALID
left join (select FMATERIALID,fname From T_BD_MATERIAL_L where FMATERIALID>1 and FLocaleId=2052) t3 on t1.FMATERIALID=t3.FMATERIALID
inner join (select FDEPTID,fname from t_bd_department_l where FDEPTID>1 and FLocaleId=2052) t11 on t0.FOLDDEPARTMENT=t11.FDEPTID
inner join (select FBILLTYPEID,fname from T_BAS_BILLTYPE_L where FLocaleId=2052) t12 on t0.FBILLTYPEID =t12.FBILLTYPEID
inner join (select FMOBILEPHONE,fid From T_ECC_MEMBER where fid>1) t16 on F_PBKA_PHONE=t16.FMOBILEPHONE
left join (select FCATEGORYID,FMATERIALID From T_BD_MATERIALBASE where FCATEGORYID>1) m on m.FMATERIALID=t2.FMATERIALID
inner join (select FDEPTID,FGROUP from T_BD_DEPARTMENT) tbt on tbt.FDEPTID=t11.FDEPTID
inner join (select FDEPTID,fname from T_BD_DEPARTMENT_L where FLOCALEID=2052) tbtl on tbt.FDEPTID=tbtl.FDEPTID
inner join (select fid,fname from T_BD_DEPARTGROUP_L where FLOCALEID=2052) tbtp on tbt.FGROUP=tbtp.fid
where (t0.FBILLTYPEID ='580778655b6d7d' or t0.FBILLTYPEID ='57faf492b10d31') and FDiscountPrice<>0
and not exists (select F_ENTRYID_JF from PAEZ_t_xsxxxx where F_ENTRYID_JF<>'' and t1.FEntryID=F_ENTRYID_JF)
AND CONVERT(VARCHAR(10),t0.F_PBKA_DATE,21)=CONVERT(varchar(10),GETDATE(), 21)
order by fbillno