海信商海導(dǎo)航sql修改語句（Hisense sea navigation SQL modification statement）

1、海信商海導(dǎo)航sql修改語句（Hisense sea navigation SQL modification statement）刪除非儲(chǔ)值卡會(huì)員/ * * /刪除xvipdb .貴賓在存儲(chǔ)= 0xvipdb viptopos刪除.在存儲(chǔ)= 0修改上次結(jié)帳日期/ * * /更新syscfg SysDB .集2007-04-10 itemvalue = 1在項(xiàng)目名稱= overstatus”更新syscfg vipdb .集2007-04-10 itemvalue = 1在項(xiàng)目名稱= overstatus”使用xvipdb將儲(chǔ)值卡會(huì)員變成非儲(chǔ)值卡會(huì)員/ * * /更新VIP設(shè)置存儲(chǔ)為0在存儲(chǔ)= 1

2、”和viptype = 2”更新VIP設(shè)置存儲(chǔ)為0在存儲(chǔ)= 1”和viptype = 2”將非儲(chǔ)值卡會(huì)員變成儲(chǔ)值卡會(huì)員/ * * /更新VIP設(shè)置存儲(chǔ)= 1”在存儲(chǔ)= 0和viptype = 2”更新VIP設(shè)置存儲(chǔ)= 1”在存儲(chǔ)= 0和viptype = 2”查詢多條碼與商品條碼重復(fù)選擇*從barcoderef條形碼在哪里（從商品中選擇條形碼）查詢商品條碼重復(fù)的商品選擇*從貨物在條碼（選擇條碼從貨物按條碼組計(jì)數(shù)（*） 1）4.3將某些商品的供應(yīng)商經(jīng)銷改聯(lián)銷導(dǎo)航/ * * /更新供應(yīng)商集jymode = 2”在vendorcode = 001”更新商品集netjprice = 0，jprice

3、= 0，plutype = 2”在vendorcode = 001”更新B集b.jprice = 0，jprice = 0，b.gcount = 0，b.ccount = 0，b.cost = 0，cost = 0，b.total = 0從商品，goodsorg B在a.plutype = 2”和a.plucode = b.plucode和（b.gcount！= 0或b.ccount！= 0）和a.vendorcode = 001”1 / * isweight稱重類型計(jì)重2是計(jì)數(shù)*/更新商品集isweight = 1”在isweight = 2”1 / * vendorty

4、pe供應(yīng)商類型自采0非自采*/更新供應(yīng)商集vendortype = 1”在vendortype = 00 / *修改商品類型普通商品2部類商品3商品包*/更新商品集plutype = 0在plutype = 2”清除條件為某部門的庫存/ * * /更新商品= 0集gcount，帳戶= 0，成本= 0，netcost = 0，共占0DepCode = 1”更改供應(yīng)商編碼/ * * /更新商品集vendorcode = 001”在vendorcode =”將儲(chǔ)值卡會(huì)員變成非儲(chǔ)值卡會(huì)員/ * * /更新VIP設(shè)置存儲(chǔ)為0在存儲(chǔ)= 1”和viptype = 2”更新VIP設(shè)置存儲(chǔ)為0在存儲(chǔ)= 1”和v

5、iptype = 2”修改會(huì)員有限期/ * * /使用vipdb更新VIP集validdate = 2010-12-30”更新viptopos集validdate = 2010-12-30”/ *對(duì)IC卡累計(jì)儲(chǔ)值金額為零，累計(jì)支出金額為零并有累計(jì)消費(fèi)金額IC會(huì)員卡的累計(jì)消費(fèi)金額消零*/zvipdb43 VIP更新.集sumtendtotal = 0在sumstoretotal = 0和sumpaytotal = 0和充值卡類型= 1”銷售變退貨1。零售/ * * /SELECT *為itemb1從全國的在trantype = BSELECT *為payb1從endpay在trantype =

6、Bselect * into totalb1from endtotalwhere trantype = b2./ * 退貨 * /select * into itemd1from enditemwhere trantype = dselect * into payd1from endpaywhere trantype = dselect * into totald1from endtotalwhere trantype = d3./ * 將銷售流水轉(zhuǎn)為退貨 * /update itemb1set pluqty = - pluqty, pludsc = - pludsc, plutotal =

7、- plutotal, trantype = dupdate payb1set amount = amount, payamount = - payamount, trantype = dupdate totalb1set totaldsc = - totaldsc, total = total, totalpay = - totalpay, trantype = dupdate itemd1set pluqty = - pluqty, pludsc = - pludsc, plutotal = - plutotal, trantype = bupdate payd1set amount =

8、amount, payamount = - payamount, trantype = bupdate totald1set totaldsc = - totaldsc, total = total, totalpay = - totalpay, trantype = b4./ * 將修改好的銷售流水插入endpay, enditem, endtotal表里 * /insert into pay(posno, orgcode, sdate, stime, serialno, itemno, payflag, trantype, cshcode, amount, payamount, payid

9、, accdate)select posno, orgcode, sdate, stime, serialno, itemno, payflag, trantype, cshcode, amount, payamount, payid, accdatefrom payb1insert into total(posno, orgcode, sdate, stime, serialno, trantype, cshcode, totaldsc, totally, totalpay, change, custflag, accdate)select posno, orgcode, sdate, st

10、ime, serialno, trantype, cshcode, totaldsc, totally, totalpay, change, custflag, accdatefrom totalb1insert into.(posno, orgcode, sdate, stime, serialno, pageno, itemno, trantype, cshcode, clerkcode, itemtype, plucode,barcode, pluname, depcode, pluprice, pluqty, pludsc, plutotal, accdate)select posno

11、, orgcode, sdate, stime, serialno, pageno, itemno, trantype, cshcode, clerkcode, itemtype, plucode,barcode, pluname, depcode, pluprice, pluqty, pludsc, plutotal, accdatefrom itemb1insert into pay(posno, orgcode, sdate, stime, serialno, itemno, payflag, trantype, cshcode, amount, payamount, payid, ac

12、cdate)select posno, orgcode, sdate, stime, serialno, itemno, payflag, trantype, cshcode, amount, payamount, payid, accdatefrom payd1insert into total(posno, orgcode, sdate, stime, serialno, trantype, cshcode, totaldsc, totally, totalpay, change, custflag, accdate)select posno, orgcode, sdate, stime,

13、 serialno, trantype, cshcode, totaldsc, totally, totalpay, change, custflag, accdatefrom totald1insert into.(posno, orgcode, sdate, stime, serialno, pageno, itemno, trantype, cshcode, clerkcode, itemtype, plucode,barcode, pluname, depcode, pluprice, pluqty, pludsc, plutotal, accdate)select posno, or

14、gcode, sdate, stime, serialno, pageno, itemno, trantype, cshcode, clerkcode, itemtype, plucode,barcode, pluname, depcode, pluprice,pluqty, pludsc, plutotal, accdatefrom itemd15./ * 將結(jié)帳日期去掉 * /update payseen accdate = 2008 - 01 01 update completeseen accdate = 2008 - 01 01 update itemseen accdate = 2

15、008 - 01 01 update headingseen chkoffflg = 3where posno = 001/ * 會(huì)員價(jià)四舍五入舍分 * /update goodsorgseen vipprice = round (vipprice, 1)update goodsseen vipprice = round (vipprice, 1)- 將已經(jīng)發(fā)生過業(yè)務(wù)的商品手工增加月初數(shù)據(jù)insert into gbk200905 (bookdate, shoal, plucode, depcode, formno, formtype, orgcode, jprice, netjprice,

16、price, fscount, fscost,fsnetcost, fstotal, jccount, jccost, jcnetcost, jctotal, roof, accdate)select distinct 2009 - 05 01, 22: 50: 47, a.plucode, depcode, 手工調(diào)整, z, orgcode, jprice, netjprice, price, 0,0,0,0,the case when formtype in (3, 5, 6, 7, a, e, f, g) then jccount + fscount else jccount - fsc

17、ount thanthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jccost + fscost else jccost - fscost thanthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jcnetcost + fsnetcost else jcnetcost - fsnetcost thanthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jctotal + fstotal else jctota

18、l - fstotal than, null, 2009 - 05 01from gbk200905 a, (select plucode, my (serialno) serialno from gbk200905 group city plucode) bwhere not exists (select * from gbk200905 where bookdate = 2009 - 05 01 and formtype = z and plucode = a.plucode)and b.plucode = a.plucode and b.serialno = a.serialnoduck

19、 (case when formtype in (3, 5, 6, 7, a, e, f, g) then jccount + fscount else jccount - fscount than 0 orthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jccost + fscost else jccost - fscost than 0 orthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jcnetcost + fsnetcost else jcnetcost -

20、 fsnetcost than 0 orthe case when formtype in (3, 5, 6, 7, a, e, f, g) then jctotal + fstotal else jctotal - fstotal than 0).- - 103go- 將未發(fā)生過業(yè)務(wù)的商品手工增加月初數(shù)據(jù)insert into gbk200905 (bookdate, shoal, plucode, b.depcode, formno, formtype, orgcode, jprice, netjprice, price, fscount, fscost, fsnetcost, fstot

21、al,jccount, jccost, jcnetcost, jctotal, roof, accdate)select distinct 2009 - 05 01, 22: 50: 47, a.plucode, b.depcode, 手工調(diào)整, z, orgcode, a.jprice, jprice, a.price, 0,0,0,0,(ccount + gcount) as jccount, cost as jccost, netcost as jcnetcost, total as jctotal, null, 2009 - 05 01from goodsorg a good

22、s bwhere a.plucode = b.plucode and orgcode = 001 and (ccount + gcount) 0 or cost 0 or total 0).不存在（從gbk200905哪里plucode = a.plucode和形式= Z選擇1）- 15去修改商品編碼的生成規(guī)則修改syscfg表plurule 0手工編碼123按流水編碼品類部門plumaxserial -最大流水區(qū)間pluminserial -最小流水區(qū)間修改表gclasslevel在相應(yīng)的分類級(jí)別后面的iscode改為1清除數(shù)據(jù)/ * * /刪除前請先備份數(shù)據(jù)，刪除后請收縮一下數(shù)據(jù)庫手工將帶

23、年月表刪除/ * * /刪除商品驗(yàn)收單/ * * /刪除acceptdetail在AcceptNo（選擇acceptno從acceptform在acceptdate 2009-09-01”）刪除acceptform在acceptdate 2009-09-01”刪除售價(jià)調(diào)整單/ * * /刪除adjustdetail在AdjustNo（選擇adjustno從adjustform在adjustdate 2009-09-01”）刪除adjustform在adjustdate 2009-09-01”刪除要貨單/ * * /刪除askdetail在BillNo（選擇billno從askform在lrda

24、te 2009-09-01”）刪除askform在lrdate 2009-09-01”2009年刪除收款員報(bào)表/ * * /刪除cshsum2009在accdate 2009-09-01”2009年刪除部門銷售匯總/ * * /刪除depsale2009在accdate 2009-09-01”2009年刪除部門進(jìn)銷存報(bào)表/ * * /刪除depsum2009在accdate 2009-09-01”刪除盤點(diǎn)差異單/ * * /刪除differdetail在DifferNo（選擇differno從differform在differdate 2009-09-01”）刪除differform在diff

25、erdate 2009-09-01”刪除備份清單/ * * /刪除backuphist刪除前臺(tái)結(jié)賬交易流水表/ * * /刪除傳真：052-932-1169在 2009-09-01姓名”刪除endpay在 2009-09-01姓名”刪除endtotal在 2009-09-01姓名”刪除endposlog結(jié)算單據(jù)匯總表/ * * /刪除jspaybill在acceptdate 2009-09-01”庫存盤點(diǎn)數(shù)據(jù)表/ * * /刪除kcpdlist在stockdate 2009-09-01”采購單/ * * /刪除訂單在OrderNo（選擇-ORDERNO.從訂單在 2009-09-01 Orde

26、rDate”）刪除訂單在 2009-09-01 OrderDate”商品退貨單/ * * /刪除returndetail在ReturnNo（選擇returnno從returnform在 2009-09-01姓名”）刪除returnform在 2009-09-01姓名”發(fā)送數(shù)據(jù)目錄表/ * * /刪除tranfscatalog在fsdate 2009-09-01”數(shù)據(jù)通訊日志表/ * * /刪除tranitemlog在fsdate 2009-09-01”接收數(shù)據(jù)目錄表/ * * /刪除tranjscatalog在jsdate 2009-09-01”刪除操作日志/ * * /刪除tranlog在l

27、ogdate 2009-09-01”/ * delete member consumption amount and balanceThe amount of business membership manual initialization.Remove all the amount of supplier / * * /Update Vendor set JhTotal=0, ThTotal=0, ZrTotal=0, FkTotal=0, Prepay=0, Payable=0* / front desk guide to data 4.3*/Select PluCode, BarCode, PluName, DepCode, PluMN, Spec, Unit, ClsCode, VendorCode, Price, VipPrice, PackPrice, PluType, IsWeight, into, aFromOPENROWSET (MICROSO