海信商海导航数据库常用操作.doc

在查询分析器中输入1把质疑的数据库更改为紧急模式sp_configure allow,1go-reconfigure with overridego-update master.sysdatabasesset status = 32768where name = zb2不能修改进价解决方案if Not Exists(select * from XtFunction where FunCode in (OP09) begin insert into XtRightFunc(UGrpCode,FunCode,IsInsert,IsUpdate,IsDelete,IsQuery,IsPrint,IsConvert) select UGrpCode,OP09,0,0,0,0,0,0 from xtUserGrp x where UGrpCode not in (01,02) and not exists(select 1 from XtRightFunc where UGrpCode=x.UGrpCode and FunCode=OP09 ) if not exists(select 1 from XtFunction where FunCode=OP09) insert into XtFunction(FunCode,FunName,FunType,DllName,DllFun,DllType,FormType,VER,SerialNo,IsMenu,Remark,IsInsert,IsUpdate,IsDelete,IsQuery,IsPrint,IsConvert,OrderNo,IsChart,ChartSerialNo,ChartFunType,ChartOrderNo,ChartViewPosition,ChartParentFunCode) values(OP09,采购和验收允许修改进价,1,0,1,0,0070,0,0,0,0,0,0,0,10,2,0900,1,1,OP)endGo3部类商品与普通商品的转换命令update goods set plutype=2 where plucode=商品编码备注：输入2是把普通商品转换为部类商品 输入0是把部类商品转换为普通商品4海信ftp中心ftp:/navigator:hisensoft81 ftp:/navigator:hisensoft0 ftp:/navigator:hisensoft465海信普通商品改部类商品update goods set plutype=2 where plucode=商品编码 另外要把修改商品的供应商经联销性质更改2是部门商品0是普通商品6海信数据库重见表索引DECLARE DBCCString NVARCHAR(1000)DECLARE TableName VARCHAR(100)DECLARE Cur_Index CURSORFOR SELECT Name AS TblName FROM sysobjects WHERE xType=U ORDER BY TblNameFOR READ ONLYOPEN Cur_IndexFETCH NEXT FROM Cur_IndexINTO TableNameWHILE FETCH_STATUS=0BEGIN SET DBCCString = DBCC DBREINDEX(TblName,)WITH NO_INFOMSGS EXEC SP_EXECUTESQL DBCCString, NTblName VARCHAR(100), TableName PRINT 重建表 + TableName +的索引.OK! FETCH NEXT FROM Cur_Index INTO TableNameENDCLOSE Cur_IndexDEALLOCATE Cur_IndexPRINT 操作完成！7解决部门商品更改为普通商品时库存管理没有启用的问题update goods set mngstock=11 where plucode=商品编码8品类进销存GeneralRptName=品类进销存RptComment=按照日期查询某时间内品类进销存汇总ParametersCount=4ParametersParameter1=BgnDate;起始日期：;3;0;Parameter2=EndDate;截止日期：;3;0;Parameter3=FClsCode;品类：;5;0;Parameter4=orgcode;门店：;6;0;Columns上期库存|数量=上期库存|数量;0.000;R;2上期库存|含税进价金额=上期库存|含税进价金额;0.00;R;2上期库存|无税进价金额=上期库存|无税进价金额;0.00;R;2上期库存|售价金额=上期库存|售价金额;0.00;R;2进货|数量=进货|数量;0.000;R;2进货|含税进价金额=进货|含税进价金额;0.00;R;2进货|无税进价金额=进货|无税进价金额;0.00;R;2进货|售价金额=进货|售价金额;0.00;R;2退货|数量=退货|数量;0.000;R;2退货|含税进价金额=退货|含税进价金额;0.00;R;2退货|无税进价金额=退货|无税进价金额;0.00;R;2退货|售价金额=退货|售价金额;0.00;R;2调入|数量=调入|数量;0.000;R;2调入|含税进价金额=调入|含税进价金额;0.00;R;2调入|无税进价金额=调入|无税进价金额;0.00;R;2调入|售价金额=调入|售价金额;0.00;R;2调出|数量=调出|数量;0.000;R;2调出|含税进价金额=调出|含税进价金额;0.00;R;2调出|无税进价金额=调出|无税进价金额;0.00;R;2调出|售价金额=调出|售价金额;0.00;R;2配送|数量=配送|数量;0.000;R;2配送|含税进价金额=配送|含税进价金额;0.00;R;2配送|无税进价金额=配送|无税进价金额;0.00;R;2配送|售价金额=配送|售价金额;0.00;R;2配送退货|数量=配送退货|数量;0.000;R;2配送退货|含税进价金额=配送退货|含税进价金额;0.00;R;2配送退货|无税进价金额=配送退货|无税进价金额;0.00;R;2配送退货|售价金额=配送退货|售价金额;0.00;R;2领用|数量=领用|数量;0.000;R;2领用|含税进价金额=领用|含税进价金额;0.00;R;2领用|无税进价金额=领用|无税进价金额;0.00;R;2领用|售价金额=领用|售价金额;0.00;R;2盘盈|数量=盘盈|数量;0.000;R;2盘盈|含税进价金额=盘盈|含税进价金额;0.00;R;2盘盈|无税进价金额=盘盈|无税进价金额;0.00;R;2盘盈|售价金额=盘盈|售价金额;0.00;R;2盘亏|数量=盘亏|数量;0.000;R;2盘亏|含税进价金额=盘亏|含税进价金额;0.00;R;2盘亏|无税进价金额=盘亏|无税进价金额;0.00;R;2盘亏|售价金额=盘亏|售价金额;0.00;R;2残损削价|数量=残损削价|数量;0.000;R;2残损削价|含税进价金额=残损削价|含税进价金额;0.00;R;2残损削价|无税进价金额=残损削价|无税进价金额;0.00;R;2残损削价|售价金额=残损削价|售价金额;0.00;R;2变价增值|含税进价金额=变价增值|含税进价金额;0.00;R;2变价增值|无税进价金额=变价增值|无税进价金额;0.00;R;2变价减值|含税进价金额=变价减值|含税进价金额;0.00;R;2变价减值|无税进价金额=变价减值|无税进价金额;0.00;R;2销售|数量=销售|数量;0.000;R;2销售|含税销售成本=销售|含税销售成本;0.00;R;2销售|无税销售成本=销售|无税销售成本;0.00;R;2销售|售价金额=销售|售价金额;0.00;R;2批发|数量=批发|数量;0.000;R;2批发|含税成本=批发|含税成本;0.00;R;2批发|无税成本=批发|无税成本;0.00;R;2批发|售价金额=批发|售价金额;0.00;R;2本期库存|数量=本期库存|数量;0.000;R;2本期库存|含税进价金额=本期库存|含税进价金额;0.00;R;2本期库存|无税进价金额=本期库存|无税进价金额;0.00;R;2本期库存|售价金额=本期库存|售价金额;0.00;R;2Content/*按照日期查询某时间内品类进销存报表使用说明：本结果取自gbk商品台账及goods商品信息表中的品类编码Created by 秦树琦 2008-03-12*/ Declare BgnDate char(10) Declare EndDate char(10) Declare OrgCode char(10) Declare ClsCode char(10) Declare ClsLevel char(1) -品类总级别 Declare SelectClsLevel char(1) -当前选定的品类级别 Declare sTmpDate Varchar(10) -临时日期 Declare sSql Varchar(8000) select BgnDate=:BgnDate select EndDate=:EndDate select OrgCode=:orgcode select ClsCode=:FClsCode/*查询品类级别和当前选择的品类级别,以便计算出汇总其下一级品类的进销存*/ select ClsLevel=ItemValue From SysCfg where Section = SYSTEM and ItemName = CLSLEVEL if ClsCode = 000000 set SelectClsLevel=0 else select SelectClsLevel=ClsLevel from GClass where ClsCode = ClsCode /*删除旧的视图V_GBK*/ if exists (select * from dbo.sysobjects where id = object_id(Ndbo.V_GBK) and OBJECTPROPERTY(id, NIsView) = 1) drop view dbo.V_GBK SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON /*设置基本语句指定时间gbk汇总到视图V_GBK*/ set sTmpDate=BgnDate while not Exists(Select Name from sysobjects where name=GBK+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2) and XType=U) begin Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120) end Set sSQL=CREATE VIEW dbo.V_GBK AS SELECT * FROM GBK+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2)Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120) While (SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2)=(SubString(EndDate,1,4)+SubString(EndDate,6,2)/*设置Union语句*/BeginIf Exists(Select Name from sysobjects where name=GBK+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2) and XType=U) Begin-Union Set sSQL=sSQL+ union allSELECT * FROM GBK+SubString(sTmpDate,1,4)+SubString(sTmpDate,6,2) End Set sTmpDate=Convert(char(10),DateAdd(Month,1,sTmpDate),120)End/*执行命令，更新视图语句*/Exec(sSql)-SET QUOTED_IDENTIFIER OFF -SET ANSI_NULLS OFF - select * from V_GBK=/*创建临时表#TmpGBk*/if exists (select * from tempdb.sysobjects where id = object_id(N#TmpGBk) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table #TmpGBkCreate table #TmpGBk(AccDate char (10) NOT NULL, OrgCode char (6) NOT NULL Default (), PluCode char (20) NOT NULL, DepCode char (6) NOT NULL, SqCost money NOT NULL DEFAULT (0), SqNetCost money NOT NULL DEFAULT (0), SqTotal money NOT NULL DEFAULT (0), SqCount money NOT NULL DEFAULT (0), JhCost money NOT NULL DEFAULT (0), JhNetCost money NOT NULL DEFAULT (0), JhTotal money NOT NULL DEFAULT (0), JhCount money NOT NULL DEFAULT (0), ThCost money NOT NULL DEFAULT (0), ThNetCost money NOT NULL DEFAULT (0), ThTotal money NOT NULL DEFAULT (0), ThCount money NOT NULL DEFAULT (0), DrCost money NOT NULL DEFAULT (0), DrNetCost money NOT NULL DEFAULT (0), DrTotal money NOT NULL DEFAULT (0), DrCount money NOT NULL DEFAULT (0), DcCost money NOT NULL DEFAULT (0), DcNetCost money NOT NULL DEFAULT (0), DcTotal money NULL DEFAULT (0), DcCount money NULL DEFAULT (0), ZpCost money NOT NULL DEFAULT (0), ZpNetCost money NOT NULL DEFAULT (0), ZpTotal money NOT NULL DEFAULT (0), ZpAmount money NOT NULL DEFAULT (0), ZpCount money NOT NULL DEFAULT (0), ZtCost money NOT NULL DEFAULT (0), ZtNetCost money NOT NULL DEFAULT (0), ZtTotal money NOT NULL DEFAULT (0), ZtAmount money NOT NULL DEFAULT (0), ZtCount money NOT NULL DEFAULT (0), LyCost money NOT NULL DEFAULT (0), LyNetCost money NOT NULL DEFAULT (0), LyTotal money NOT NULL DEFAULT (0), LyCount money NOT NULL DEFAULT (0), PyCost money NOT NULL DEFAULT (0), PyNetCost money NOT NULL DEFAULT (0), PyTotal money NOT NULL DEFAULT (0), PyCount money NOT NULL DEFAULT (0), PkCost money NOT NULL DEFAULT (0), PkNetCost money NOT NULL DEFAULT (0), PkTotal money NOT NULL DEFAULT (0), PkCount money NOT NULL DEFAULT (0), XjCost money NOT NULL DEFAULT (0), XjNetCost money NOT NULL DEFAULT (0), XjTotal money NOT NULL DEFAULT (0), XjCount money NOT NULL DEFAULT (0), TkCost money NOT NULL DEFAULT (0), TkNetCost money NOT NULL DEFAULT (0), TjCost money NOT NULL DEFAULT (0), TjNetCost money NOT NULL DEFAULT (0), TkTotal money NOT NULL DEFAULT (0), TjTotal money NOT NULL DEFAULT (0), XsCost money NOT NULL DEFAULT (0), XsNetCost money NOT NULL DEFAULT (0), XsTotal money NOT NULL DEFAULT (0), XsAmount money NOT NULL DEFAULT (0), XsCount money NOT NULL DEFAULT (0), DscTotal money NOT NULL DEFAULT (0), PfSaleCount money NOT NULL DEFAULT (0), PfSaleCost money NOT NULL DEFAULT (0), PfSaleNetCost money NOT NULL DEFAULT (0), PfSaleTotal money NOT NULL DEFAULT (0), PfSaleAmount money NOT NULL DEFAULT (0), PfReturnCount money NOT NULL DEFAULT (0), PfReturnCost money NOT NULL DEFAULT (0), PfReturnNetCost money NOT NULL DEFAULT (0), PfReturnTotal money NOT NULL DEFAULT (0), PfReturnAmount money NOT NULL DEFAULT (0), BqCost money NOT NULL DEFAULT (0), BqNetCost money NOT NULL DEFAULT (0), BqTotal money NOT NULL DEFAULT (0), BqCount money NOT NULL DEFAULT (0), CONSTRAINT PK_#TmpGBk PRIMARY KEY (AccDate,OrgCode,PluCode,DepCode) )-插入编码、部门、组织 Insert into #TmpGBk (AccDate,PluCode,DepCode,OrgCode) select Distinct,PluCode,DepCode,OrgCode from V_GBK A Where AccDate = EndDate or FormType=Z -更新上期结存数=上期结存数Update A Set A.SqCount=G.JcCount,A.SqCost=G.JcCost,A.SqNetCost=G.JcNetCost,A.SqTotal=G.JcTotal from #TmpGBk A,V_GBK G where A.PluCode=G.PluCode and A.DepCode=G.DepCode and A.OrgCode=G.OrgCode and G.FormType=Z and month(g.accdate)=month(bgndate) and year(g.accDate)=year(BgnDate) -用发生数更新上期结存数Update G Set G.SqCount=G.SqCount+B.FsCount,G.SqCost=G.SqCost+B.FsCost,G.SqNetCost=G.SqNetCost+B.FsNetCost,G.SqTotal=G.SqTotal+B.FsTotal from #TmpGBk G, (select PluCode,DepCode,OrgCode, Sum(Case When FormType in(3,5,6,7,A,E,F,G) then -fsCount When FormType in (4,8) then 0 Else FsCount End) as FsCount, Sum(Case When FormType in(3,5,6,7,A,E,F,G) then -fsCost Else FsCost End) as FsCost, Sum(Case When FormType in(3,5,6,7,A,E,F,G) then -fsNetCost Else fsNetCost End) as fsNetCost, Sum(Case When FormType in(3,5,6,7,A,E,F,G) then -fsTotal Else fsTotal End) as fsTotal from V_GBK A where AccDate0) or (FormType=D) then FsCount when (FormType=F) or (FormType=G) and (FsCount0) then FsCount*(-1) else 0 end ) DrCount, Sum(Case when (FormType=1) and (FsCount0) then FsCount else 0 end ) DcCount, Sum(Case when (FormType=2) or (FormType=9) and (FsCount0) then FsCount else 0 end ) PyCount,Sum(Case when (FormType=2) or (FormType=9) and (FsCount0) then FsCount else 0 end ) ZpCount, Sum(Case when (FormType=E) and (FsCount0) or (FormType=D) then FsCost when (FormType=F) or (FormType=G) and (FsCount0) then FsCost*(-1) else 0 end ) DrCost, Sum(Case when (FormType=1) and (FsCount0) then FsCost else 0 end ) DcCost, Sum(Case when (FormType=2) or (FormType=9) and (FsCost0) then FsCost else 0 end ) PyCost,Sum(Case when (FormType=2) or (FormType=9) and (FsCost0) then FsCost*(-1) else 0 end ) PkCost, Sum(Case FormType when 3 then FsCost else 0 end ) XjCost, Sum(Case when (FormType=4) and (FsCost)0 then FsCost else 0 end ) TjCost, Sum(Case FormType when 5 then FsCost else 0 end ) ThCost, Sum(Case FormType when 6 then FsCost else 0 end ) XsCost, Sum(Case FormType when 7 then FsCost else 0 end ) LyCost, Sum(Case FormType when A then FsCost else 0 end ) PfSaleCost, Sum(Case FormType when B then FsCost else 0 end ) PfReturnCost, Sum(Case when (FormType=E) and (FsCost0) then FsCost else 0 end ) ZpCost, Sum(Case when (FormType=E) and (FsCost0) or (FormType=D) then FsNetCost when (FormType=F) or (FormType=G) and (FsCount0) then FsNetCost*(-1) else 0 end ) DrNetCost, Sum(Case when (FormType=1) and (FsCount0) then FsNetCost else 0 end ) DcNetCost, Sum(Case when (FormType=2) or (FormType=9) and (FsNetCost0) then FsNetCost else 0 end ) PyNetCost,Sum(Case when (FormType=2) or (FormType=9) and (FsNetCost0) then FsNetCost*(-1) else 0 end ) PkNetCost, Sum(Case FormType when 3 then FsNetCost else 0 end ) XjNetCost, Sum(Case when (FormType=4) and (FsNetCost)0 then FsNetCost else 0 end ) TjNetCost, Sum(Case FormType when 5 then FsNetCost else 0 end ) ThNetCost, Sum(Case FormType when 6 then FsNetCost else 0 end ) XsNetCost, Sum(Case FormType when 7 then FsNetCost else 0 end ) LyNetCost, Sum(Case FormType when A then FsNetCost else 0 end ) PfSaleNetCost, Sum(Case FormType when B then FsNetCost else 0 end ) PfReturnNetCost, Sum(Case when (FormType=E) and (FsNetCost0) then FsNetCost else 0 end ) ZpNetCost, Sum(Case when (FormType=E) and (FsNetCost0) or (FormType=D) then FsTotal when (FormType=F) or (FormType=G) and (FsCount0) then FsTotal*(-1) else 0 end ) DrTotal, Sum(Case when (FormType=1) and (FsCount0) then FsTotal else 0 end ) DcTotal, Sum(Case when (FormType=2) or (FormType=9) and (FsTotal0) then FsTotal else 0 end ) PyTotal,Sum(Case when (FormType=2) or (FormType=9) and (FsTotal0) then FsTotal*(-1) else 0 end ) PkTotal, Sum(Case FormType when 3 then FsTotal else 0 end ) XjTotal, Sum(Case FormType when 5 then FsTotal else 0 end ) ThTotal, Sum(Case FormType when 6 then FsTotal else 0 end ) XsTotal, Sum(Case FormType when 7 then FsTotal else 0 end ) LyTotal, Sum(Case when (FormType=8) and (FsTotal)0 then FsTotal else 0 end ) TjTotal, Sum(Case FormType when A then FsTotal else 0 end ) PfSaleTotal, Sum(Case FormType when B then FsTotal else 0 end ) PfReturnTotal, Sum(Case when (FormType=E) and (FsTotal0) then FsTotal else 0 end ) ZpTotal, Sum(Case when (FormType=E) and (FsTotal= BgnDate and AccDate= EndDate Group By PluCode,OrgCode,DepCode ) B WHERE G.PluCode=B.PluCode and G.Dep