SLTechnology News&Howtos › Internet Technology ›

Update system file information

Shulou Source: shulou.com Published: 2022-06-03 06:43:19 10月03日 Update

/ first step: insert product information except mobile phone into file /

Declare @ sql varchar (max)

Set @ sql= (select max (FEntryID) FEntryID from PAEZ_t_xsxxxx where FEntryID > 1)

-- INSERT INTO PAEZ_t_xsxxxx (Fountain ENTRYIDDATEQ Fountain PAEZID DATEX Fiction PAEZBEZD Med Flying PAEZING PRICEL Flying PAEZING SPMC1

Select FEntryID,t16.fid,isnull (@ sql,'1001') + ROW_NUMBER () over (order by t0.fbillno) ggg,convert (varchar (10), t0.FenewPBKANGDATE21), t12.fnamememe t11.fnameReporter FDiscountPriceReporter t2.FNUMBERLYPEIDE case when t0.FBILLTYPEIDenz57faf492b10d31'

Then FSDJAmount else-FSDJAmount end,case when t0.FBILLTYPEIDES 57faf492b10d31' then FQTY else-FQTY end, tbtp.fname

From (select fid,F_PBKA_DATE,FBILLTYPEID,FOLDDEPARTMENT,rtrim (ltrim (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 FDiscountPrice0

And not exists (select F_ENTRYID_JF from PAEZ_t_xsxxxx where favored entry JFY 'and t1.FEntryID=F_ENTRYID_JF)

AND (CONVERT (VARCHAR (10), t0.FipPBKANGDATERE21) > = CONVERT (varchar (10), '2018-02-01DATE21) AND CONVERT (VARCHAR (10), t0.FUBKANGDATERE21) 1) t0

Left join (select fid,F_PBKA_NAME from PBKA_t_BillHead_L where fid > 1) T10 on t0.fid=t10.fid

Where Fang PBKANGPHONEN 'and F_PBKA_PHONE not in (select distinct FMOBILEPHONE from T_ECC_MEMBER)

AND CONVERT (VARCHAR (10), FCREATEDATE,21) = CONVERT (varchar (10), getdate (), 21)

-customer data form to re-call and insert temporary form-

Create table # dh (F_PBKA_PHONE varchar)

Insert into # dh

Select distinct rtrim (ltrim (F_PBKA_PHONE)) from # uuu

-reprocess the information to be inserted into the customer table-

CREATE TABLE # qc (fbillno varchar, F_PBKA_PHONE varchar)

Insert into # qc

SELECT distinct MAX (fbillno) fbillno,F_PBKA_PHONE FROM # uuu GROUP BY F_PBKA_PHONE ORDER BY 2

-the data to be inserted eventually

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)

-determine whether the file exists and insert the main table

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 words AB9A Israel E87FCDDB63D7' From # uuu2

-end

-insert slave table

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.

INNER JOIN # uuu2 B ON A.FMOBILEPHONE=B.F_PBKA_PHONE

-consumption details update

Declare @ sql varchar (max)

Set @ sql= (select max (FEntryID) FEntryID from PAEZ_t_xsxxxx where FEntryID > 1)

INSERT INTO PAEZ_t_xsxxxx (Fountain ENTRYIDDATE, Fountain PAEZID DATEX, Fountain PAEZIDEMD, Fountain PAEZIDEMet Flying PAEZIDEMet Flying PAEZING SPDM SPMC1, Flying PAEZINXSFBILLNOQy