更新系統檔案資訊

/第一步:插入除了手機的商品資訊到檔案/

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)