医药CRM系统开发

自已做医药CRM系统有四年了,终于可以算个产品了,努力市场化,今年重种将医药营销的理念加入CRM

导航

单品流向

Posted on 2007-10-20 12:00  hhq80  阅读(3366)  评论(0编辑  收藏  举报

--方式,记帐日期,单据日期,单价,批发价,零售价,实际售价,数量,单据号,库存余量,流向单位,批号

SET NOCOUNT ON

declare @starttime char(19)
declare @stoptime char(19)
declare @nbbm decimal(18,0)
--set @date1=getdate()
set @starttime='2007-08-21 00:00:00'
set @stoptime='2007-08-23 23:59:59'
set @nbbm=10000290

select Identity(int,1,1) as rowid,nbbm,cfbj,djbh,jzrq,djrq,bzdj,pfj,lsj,isnull(jsdj,0) as jsdj,bzsl*bzl as bzsl,jsdjh,0 as kcl,dw,ph into #sun_web_pzlx from zy_yp1_sfmx where nbbm=@nbbm and jzrq>@starttime and jzrq<=@stoptime order by jzrq,ph,cfbj

declare @fs char(1)
declare @bzsl decimal(18,2)
declare @kcl decimal(18,2)
declare @rksl decimal(18,2)
declare @cksl decimal(18,2)
declare @rowid char(18)
declare @pri_kcl decimal(18,2)
declare @ini_rks decimal(18,2)--初始入库数
declare @ini_cks decimal(18,2)--初始销售数

  --1.得到最先时间段的总库存数
  set @ini_rks=(Select sum(bzsl) as zrzs from zy_yp1_sfmx where cfbj='T' AND nbbm=@nbbm and jzrq<@starttime)
  set @ini_cks=(Select sum(bzsl) as ckzs from zy_yp1_sfmx where cfbj='F' AND nbbm=@nbbm and jzrq<@starttime)
  set @kcl=@ini_rks-@ini_cks
--print @kcl

--select sum(bzl*bzsl*(case isnull(cfbj,'T') when 'T' then 1 else - 1 end)) from zy_yp1_sfmx where spmnm=@nbbm and jzrq<@starttime

DECLARE Get_pzlx CURSOR FOR
SELECT cfbj,bzsl,rowid
FROM #sun_web_pzlx order by rowid

OPEN Get_pzlx

FETCH NEXT FROM Get_pzlx INTO @fs, @bzsl,@rowid
SET @kcl=@kcl+(case when @fs='T' then 1 else -1 end )*@bzsl
UPDATE #sun_web_pzlx SET  kcl=@kcl where rowid=@rowid

 WHILE @@FETCH_STATUS = 0
  BEGIN
   FETCH NEXT FROM Get_pzlx INTO @fs, @bzsl,@rowid
   --处理
   IF @@FETCH_STATUS = 0
   BEGIN
    SET @kcl=@kcl+(case when @fs='T' then 1 else -1 end )*@bzsl
    UPDATE #sun_web_pzlx SET  kcl=@kcl where rowid=@rowid
    --print @@FETCH_STATUS
   END
  END
CLOSE Get_pzlx
DEALLOCATE Get_pzlx
select b.rowid,a.ypm,a.gg,a.cd,a.zxdw,b.kcl,b.bzsl,b.djbh,b.cfbj,b.jzrq,b.djrq,b.bzdj,b.pfj,b.lsj,b.jsdj,b.jsdjh,b.dw,b.ph from #sun_web_pzlx b,zy_sys2_ypzdk a where a.nbbm=b.nbbm order by b.rowid
drop table #sun_web_pzlx

SET NOCOUNT OFF