--drop PROCEDURE pr_TodaySaleInfo

CREATE   PROCEDURE pr_TodaySaleInfo
(
     @CMode          varchar(25),
     
      @begindate         varchar(25),
      @enddate           varchar(25)
     
)     
AS

DECLARE @SQL VARCHAR(8000);
DECLARE @SQL1 VARCHAR(8000);
DECLARE @SQL2 VARCHAR(8000);
-- 建立查询临时表 
CREATE TABLE #Detail (
    PTYPEID VARCHAR(200),
    pfullname VARCHAR(200),
    QTY  VARCHAR(200),
    TOTAL VARCHAR(200),
    ToadymAX VARCHAR(200)
)		

if @cmode='P'
BEGIN 
set @SQL='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
    TOTAL,
    ToadymAX )
     select top 1 m.Ptypeid PTYPEID,m.FullName pfullname, Cast(m.saleqty  as decimal(18,2)) QTY,cast(m.taxtotal  as decimal(18,2)) TOTAL,''1'' ToadymAX from ( 
     select a.FullName, a.typeid Ptypeid,isnull(sum(-mx.qty),0) as saleqty,sum(-mx.taxtotal) as taxtotal 
   from ptype a left join (select i.qty,p.typeId ptypeid,i.Tax , i.taxtotal, i.total, i.costtotal from ptype p inner JOIN  
  (select  i.PRec,sum(i.qty) qty,  sum(i.Tax) as tax,sum(i.taxtotal) taxtotal,sum(i.total) total,sum(convert(numeric(20,2),i.costtotal)) costtotal 
  from inoutstocktable i where  audited = 1  and substring(CONVERT(varchar(100), billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'
       and ('''' ='''' or exists(select *  from employee e  where e.rec=i.ERec and e.typeid like ''%''))--ETYPEID 全部为空 
       AND (exists (SELECT 1 FROM (SELECT rec,typeid  FROM BTYPE) r WHERE r.Rec = i.Brec AND (typeid LIKE ''%'' OR ('''' = '''')))) 
        AND (exists (SELECT 1 FROM (SELECT rec,typeid  FROM Stock) k WHERE i.KRec =k.rec AND (typeid LIKE ''%'' OR ('''' = '''')))) 
       and billtype in(11,151,203,45,152,704,707,207,210) 
       and i.Status=0  GROUP BY  i.PRec)   i ON  p.Rec = i.PRec 
     where ''A'' =''A'' or (''00000'' = ''00000'' OR  p.typeId LIKE   ''00000%'') 
  union all  
  select 0 as qty,p.typeid ptypeid,sum(i.Tax) as tax, 
         case b.billtype when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal) else          sum(-i.difftaxtotal) 
       end as taxtotal,case b.billtype  when 209 then sum(i.difftotal)   when 210 then sum(i.difftotal) else          sum(-i.difftotal) 
       end as total,0 as costtotal 
       from dbo.PriceDiffBill   i  ,Billindex B,(SELECT rec,typeid  FROM BTYPE) bt,(SELECT rec,typeid FROM Stock) k,employee e,ptype p,employee be  
       where b.BillID = i.BillID  and bt.Rec = b.BRec and b.ERec = e.Rec and  i.KRec = k.Rec  
       and p.Rec = i.PRec and be.rec=b.bille and redword=0 and b.draft=0 
       and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  and b.billtype in(11,151,203,45,152,704,707,207,210) 
 	   group by P.typeid,b.billtype ) as mx  on (mx.ptypeid=a.typeid) 
  where a.sonnum=0 and a.typeid <>''00000'' and a.deleted=0   group by a.typeid,a.RowIndex,a.FullName 
  )m group by m.Ptypeid,m.FullName,m.taxtotal,m.saleqty order by sum(m.saleqty) desc;';
  
 set @SQL1='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
    TOTAL,
    ToadymAX )
  select top 1 m.Ptypeid PTYPEID,m.FullName pfullname,cast(m.saleqty  as decimal(18,2)) QTY ,cast(m.taxtotal as decimal(18,2)) TOTAL ,''0'' ToadymAX from ( 
  select a.FullName, a.typeid Ptypeid,isnull(sum(-mx.qty),0) as saleqty,sum(-mx.taxtotal) as taxtotal 
  from ptype a left join ( 
       select i.qty,p.typeId ptypeid,i.Tax , i.taxtotal, i.total, i.costtotal 
        from ptype p inner JOIN 
   (select  i.PRec,sum(i.qty) qty,  sum(i.Tax) as tax,sum(i.taxtotal) taxtotal,sum(i.total) total,sum(convert(numeric(20,2),i.costtotal)) costtotal 
  from inoutstocktable i where  audited = 1   and substring(CONVERT(varchar(100), billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  
        and ('''' ='''' or exists(select *  from employee e  where e.rec=i.ERec and e.typeid like ''%''))--ETYPEID 全部为空 
       AND (exists (SELECT 1 FROM (SELECT rec,typeid  FROM BTYPE) r WHERE r.Rec = i.Brec AND (typeid LIKE ''%'' OR ('''' = ''''))))--btypeid全部为空
       AND (exists (SELECT 1 FROM (SELECT rec,typeid  FROM Stock) k WHERE i.KRec =k.rec AND (typeid LIKE ''%'' OR ('''' = ''''))))--ktypeid全部为空  
     and billtype in(11,151,203,45,152,704,707,207,210) 
      and i.Status=0  GROUP BY  i.PRec)   i ON  p.Rec = i.PRec where ''A'' =''A''  
  union all 
  select 0 as qty,p.typeid ptypeid,sum(i.Tax) as tax, 
      case b.billtype when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal) else          sum(-i.difftaxtotal) 
       end as taxtotal,case b.billtype  when 209 then sum(i.difftotal)   when 210 then sum(i.difftotal) else          sum(-i.difftotal) 
       end as total,0 as costtotal 
      from dbo.PriceDiffBill   i  ,Billindex B,(SELECT rec,typeid  FROM BTYPE) bt,(SELECT rec,typeid FROM Stock) k,employee e,ptype p,employee be  
      where b.BillID = i.BillID  and bt.Rec = b.BRec and b.ERec = e.Rec and  i.KRec = k.Rec  and p.Rec = i.PRec and be.rec=b.bille 
      and redword=0 and b.draft=0 and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and '+char(39)+@enddate+char(39)+' 
      and b.billtype in(11,151,203,45,152,704,707,207,210) group by P.typeid,b.billtype ) as mx  on (mx.ptypeid=a.typeid) 
  where a.sonnum=0 and a.typeid <>''00000'' and a.deleted=0  group by a.typeid,a.RowIndex,a.FullName 
  )m group by m.Ptypeid,m.FullName,m.saleqty ,m.taxtotal order by sum(m.taxtotal) desc;';
  
  
END
IF @CMode='B'--kehu
BEGIN
set @SQL='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
    TOTAL,
    ToadymAX )
	select top 1 m.Btypeid PTYPEID,m.FullName pfullname,m.saleqty QTY,m.Total TOTAL,''1'' ToadymAX from (	  
 select  a.typeid Btypeid,a.FullName, a.rowIndex,isnull(sum(-mx.qty),0) as saleqty,
 cast(isnull(sum(-mx.taxtotal),0) as numeric(20,5)) as taxtotal,
cast(isnull(sum(-mx.total),0) as numeric(20,5)) as Total
 from   (select typeid ,fullname,rec,sonnum,parid,deleted,RowIndex  from btype where  typeid<>'''') a 
 left join (		   select sum(i.qty) qty,b.typeid btypeid,sum(i.taxtotal) taxtotal,sum(i.total) total,
 sum(i.costtotal) costtotal
from inoutstocktable i,vPTypeArea p,(   select typeid ,rec,sonnum,parid,deleted,RowIndex  from btype 
  where  typeid<>'''') b,( select typeid ,rec  from stock where  typeid<>'''') k,employee e,employee be ,btype bt 
 where audited = 1 and i.PRec=p.Rec and b.Rec =i.Brec and k.Rec = i.KRec and e.Rec = i.ERec 
and be.Rec =i.Bille and bt.Rec= i.providerid
 and substring(CONVERT(varchar(100), i.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'
and i.billtype in(11,151,203,45,152,704,707,207,210)
 and i.Status=0 group by b.typeid
union all 
select 0 as qty,btype.typeid btypeid, case b.billtype   when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal)
 else          sum(-i.difftaxtotal) end as taxtotal,case b.billtype   when 209 then sum(i.difftotal) when 210 then sum(i.difftotal)
  else          sum(-i.difftotal) end as total,0 as costtotal
 from vPriceDiffBillHasKType   i   join     Billindex B   on i.BillID=b.BillID join (select typeid ,rec,sonnum,parid,deleted,RowIndex  from dbo.fn_BTypeRight(94) where  typeid<>'''') btype on btype.rec=b.brec
join vPTypeArea p on i.PRec=p.Rec join btype bt on bt.Rec = i.providerid JOIN (select typeid ,rec  from stock where  typeid<>'''') k ON  k.Rec = i.KRec
  where   b.redword = 0 and b.draft=0  
and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+' and b.billtype in(11,151,203,45,152,704,707,207,210)
 group by btype.typeid,b.billtype ) as mx on (left(mx.btypeid,len(a.typeid))=a.typeid)
 where a.sonnum=0 and a.typeid<>''00000''  and  a.typeid<>''91000'' and a.deleted=0 and (left(a.typeid,5)<>''90000'' or 1<>7)
 group by a.typeid,a.rowIndex,a.FullName
)m group by m.Btypeid ,m.FullName,m.Total,m.saleqty order by sum(m.saleqty) desc;';


set @SQL1='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
   TOTAL,
    ToadymAX )
select top 1 m.Btypeid PTYPEID,m.FullName pfullname,m.saleqty QTY,m.Total TOTAL,''0'' ToadymAX from (	  
 select  a.typeid Btypeid,a.FullName, a.rowIndex,isnull(sum(-mx.qty),0) as saleqty,
 cast(isnull(sum(-mx.taxtotal),0) as numeric(20,5)) as taxtotal,
cast(isnull(sum(-mx.total),0) as numeric(20,5)) as Total
 from   (select typeid ,fullname,rec,sonnum,parid,deleted,RowIndex  from btype where  typeid<>'''') a 
 left join (		   select sum(i.qty) qty,b.typeid btypeid,sum(i.taxtotal) taxtotal,sum(i.total) total,
 sum(i.costtotal) costtotal
from inoutstocktable i,vPTypeArea p,(   select typeid ,rec,sonnum,parid,deleted,RowIndex  from btype 
  where  typeid<>'''') b,( select typeid ,rec  from stock where  typeid<>'''') k,employee e,employee be ,btype bt 
 where audited = 1 and i.PRec=p.Rec and b.Rec =i.Brec and k.Rec = i.KRec and e.Rec = i.ERec 
and be.Rec =i.Bille and bt.Rec= i.providerid
 and substring(CONVERT(varchar(100), i.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'
and i.billtype in(11,151,203,45,152,704,707,207,210)
 and i.Status=0 group by b.typeid
union all 
select 0 as qty,btype.typeid btypeid, case b.billtype   when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal)
 else          sum(-i.difftaxtotal) end as taxtotal,case b.billtype   when 209 then sum(i.difftotal) when 210 then sum(i.difftotal)
  else          sum(-i.difftotal) end as total,0 as costtotal
 from vPriceDiffBillHasKType   i   join     Billindex B   on i.BillID=b.BillID join (select typeid ,rec,sonnum,parid,deleted,RowIndex  from dbo.fn_BTypeRight(94) where  typeid<>'''') btype on btype.rec=b.brec
join vPTypeArea p on i.PRec=p.Rec join btype bt on bt.Rec = i.providerid JOIN (select typeid ,rec  from stock where  typeid<>'''') k ON  k.Rec = i.KRec
  where   b.redword = 0 and b.draft=0  
and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+' and b.billtype in(11,151,203,45,152,704,707,207,210)
 group by btype.typeid,b.billtype ) as mx on (left(mx.btypeid,len(a.typeid))=a.typeid)
 where a.sonnum=0 and a.typeid<>''00000''  and  a.typeid<>''91000'' and a.deleted=0 and (left(a.typeid,5)<>''90000'' or 1<>7)
 group by a.typeid,a.rowIndex,a.FullName
)m group by m.Btypeid ,m.FullName,m.saleqty,m.Total order by sum(m.Total) desc;';

END
 IF @CMode='E'--职员
begin 
set @SQL='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
    TOTAL,
    ToadymAX )
select top 1 m.etypeid PTYPEID,m.FullName pfullname,m.saleqty QTY ,m.Total TOTAL,''1'' ToadymAX from (
 select a.typeid etypeid,a.FullName, cast(isnull(sum(-mx.qty),0) as numeric(10,2)) as saleqty,
  cast(isnull(sum(-mx.taxtotal),0) as numeric(20,5)) as taxprice,                
          cast(isnull(sum(-mx.total),0) as numeric(20,2)) as Total     
  from employee a 
   Left join (
    select qty,e.typeid etypeid,taxtotal,total,costtotal
from employee e 
join 
(           select sum(i.qty) qty,i.erec ,sum(i.taxtotal) taxtotal,sum(i.total) total,
       sum(i.costtotal) costtotal  ,
       count(DISTINCT CASE when i.BillType IN (11,203,704,151) then  i.BillID ELSE 0 END) XS_n1,--统计销售次数 + 1
	   count(DISTINCT CASE when i.BillType IN (11,203,704,151) then  0 ELSE i.BillID END) XS_n2--统计退货次数 + 1                 
       from inoutstocktable i
       where audited = 1 
       and substring(CONVERT(varchar(100), billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  
       and ('''' ='''' or exists(select *  from btype b   where i.bRec = b.rec and b.typeid like ''%''))
       and ('''' ='''' or exists(select *  from Stock  k  where i.kRec = k.rec and k.typeid like ''%''))
       and (''''='''' or exists(select *  from employee be  where be.rec=bille and typeid like ''%''))
	   and (''''=''''  or exists(select *   from ptype p  where p.rec=prec and typeid like ''%''))
	   and ( exists(select *  from stock k  where i.KRec = k.rec))
	   and ( exists(select *  from btype b  where i.bRec = b.rec))
       and billtype in(11,151,203,45,152,704,707,207,210)
       and i.Status=0        
group by erec) ls on  e.Rec = ls.ERec 
and e.sonnum=0 and e.deleted=0
union all 
select 0 as qty,e.typeid etypeid,
       case b.billtype   when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal) else          sum(-i.difftaxtotal)
       end as taxtotal, case b.billtype  when 209 then sum(i.difftotal)  when 210 then sum(i.difftotal)  else          sum(-i.difftotal)
 end as total,0 as costtotal  from pricediffbill   i  ,Billindex b,stock k,btype c ,employee e ,ptype p  
 where b.BillID = i.BillID  and k.rec = b.KRec and c.Rec = b.Brec and e.Rec = b.ERec
and redword=0 and b.draft=0 and p.Rec = i.PRec        
 and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  
and e.sonnum=0 and e.deleted=0
 and ( exists(select *  from  employee be  where be.rec=bille and typeid like ''%''))
 and (exists(select *  from  ptype p  where p.rec=prec and typeid like ''%''))
  and b.billtype in(207,210)   group by e.typeid,b.billtype        ) as mx
   on a.typeid=left(mx.etypeid,len(a.typeid))
   where a.typeid<>''00000''and a.sonnum=0 and a.deleted=0  
   and a.TypeID in(select left(e.typeid,len(a.typeid)) from ( select TypeID from employee 
  where sonnum=0 ) E)
   group by a.typeid,a.RowIndex,a.FullName)m group BY  m.etypeid,m.Total,m.FullName,m.saleqty order by sum(m.saleqty) desc;';
  
  
   set @SQL1='insert into #Detail(	
    PTYPEID ,
    pfullname,
    QTY,
    TOTAL,
    ToadymAX )
   select top 1 m.etypeid PTYPEID,m.FullName pfullname, m.saleqty  QTY,m.Total TOTAL ,''0'' ToadymAX from (
 select a.typeid etypeid,a.FullName, cast(isnull(sum(-mx.qty),0) as numeric(10,2)) as saleqty,
  cast(isnull(sum(-mx.taxtotal),0) as numeric(20,5)) as taxprice,                
          cast(isnull(sum(-mx.total),0) as numeric(20,2)) as Total     
  from employee a 
   Left join (
    select qty,e.typeid etypeid,taxtotal,total,costtotal
from employee e 
join 
(           select sum(i.qty) qty,i.erec ,sum(i.taxtotal) taxtotal,sum(i.total) total,
       sum(i.costtotal) costtotal  ,
       count(DISTINCT CASE when i.BillType IN (11,203,704,151) then  i.BillID ELSE 0 END) XS_n1,--统计销售次数 + 1
	   count(DISTINCT CASE when i.BillType IN (11,203,704,151) then  0 ELSE i.BillID END) XS_n2--统计退货次数 + 1                 
       from inoutstocktable i
       where audited = 1 
       and substring(CONVERT(varchar(100), billdate, 120),0,11)  between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  
       and ('''' ='''' or exists(select *  from btype b   where i.bRec = b.rec and b.typeid like ''%''))
       and ('''' ='''' or exists(select *  from Stock  k  where i.kRec = k.rec and k.typeid like ''%''))
       and (''''='''' or exists(select *  from employee be  where be.rec=bille and typeid like ''%''))
	   and (''''=''''  or exists(select *   from ptype p  where p.rec=prec and typeid like ''%''))
	   and ( exists(select *  from stock k  where i.KRec = k.rec))
	   and ( exists(select *  from btype b  where i.bRec = b.rec))
       and billtype in(11,151,203,45,152,704,707,207,210)
       and i.Status=0        
group by erec) ls on  e.Rec = ls.ERec 
and e.sonnum=0 and e.deleted=0
union all 
select 0 as qty,e.typeid etypeid,
       case b.billtype   when 209 then sum(i.difftaxtotal) when 210 then sum(i.difftaxtotal) else          sum(-i.difftaxtotal)
       end as taxtotal, case b.billtype  when 209 then sum(i.difftotal)  when 210 then sum(i.difftotal)  else          sum(-i.difftotal)
 end as total,0 as costtotal  from pricediffbill   i  ,Billindex b,stock k,btype c ,employee e ,ptype p  
 where b.BillID = i.BillID  and k.rec = b.KRec and c.Rec = b.Brec and e.Rec = b.ERec
and redword=0 and b.draft=0 and p.Rec = i.PRec        
 and substring(CONVERT(varchar(100), b.billdate, 120),0,11) between '+char(39)+@begindate+char(39)+' and  '+char(39)+@enddate+char(39)+'  
and e.sonnum=0 and e.deleted=0
 and ( exists(select *  from  employee be  where be.rec=bille and typeid like ''%''))
 and (exists(select *  from  ptype p  where p.rec=prec and typeid like ''%''))
  and b.billtype in(207,210)   group by e.typeid,b.billtype        ) as mx
   on a.typeid=left(mx.etypeid,len(a.typeid))
   where a.typeid<>''00000''and a.sonnum=0 and a.deleted=0  
   and a.TypeID in(select left(e.typeid,len(a.typeid)) from ( select TypeID from employee 
  where sonnum=0 ) E)
   group by a.typeid,a.RowIndex,a.FullName)m group BY  m.etypeid,m.saleqty,m.FullName,m.Total order by sum(m.Total) desc;';
 end  
   EXEC(@SQL)
   EXEC(@SQL1)
   set @SQL2='Select * from #Detail;'; 
   
  EXEC(@SQL2)
