IF OBJECT_ID('SK_vi_Download') IS NOT NULL
   DROP VIEW SK_vi_Download
GO

CREATE VIEW dbo.SK_vi_Download
AS
   SELECT Person_No,Person_Name,Card_No,Consume_Time,Consume_Fund,
          CASE WHEN A.Consume_Mode=1 THEN '計時' WHEN A.Consume_Mode=2 THEN '計次' ELSE '計量' END [Consume_Mode],
          Consume_Copy,Consume_TimeLen,Consume_Capacity,A.MOC_No,MOC_Name,Card_Balance
     FROM XF_Consume A
     LEFT JOIN ST_Person B ON B.Person_ID=A.Person_ID
     LEFT JOIN ST_Moc C ON C.MOC_No=A.MOC_No
    WHERE A.System_No='SK'
GO

IF OBJECT_ID('SK_Pd_PsnTotal') IS NOT NULL
   DROP PROCEDURE SK_Pd_PsnTotal
GO

-----水控消費匯總查詢
CREATE PROCEDURE dbo.SK_Pd_PsnTotal
@DateBgn VARCHAR(20),		--起始日期
@DateEnd VARCHAR(20),		--結束日期
@Moc_No VARCHAR(3000),		--機器編號
@Dept VARCHAR(20),			--部門條件
@Type integer,             --匯總類別
@UseType VARCHAR(20),      --用途分類
@isDept bit
AS

DECLARE @sql1 VARCHAR(3000), @sql2 VARCHAR(3000),@sql3 VARCHAR(3000)
SET   @sql2 = '  And Consume_Time >= ' + @DateBgn + ' And  Consume_Time <=' + @DateEnd


IF @Type = 1   --部門+時間匯總
BEGIN
IF @Dept = ''    --不選擇部門,只按日期范圍
BEGIN
   Select c.Person_No, c.Person_Name ,d.Dept_Name, c.TotalTimeLen, c.TotalCopy, c.TotalCapacity, c.TotalFund From(
   (Select a.*, b.Person_No,b.Person_Name,b.Dept_ID From (
   Select Person_ID, sum(Consume_TimeLen) as TotalTimeLen, sum(Consume_Copy) as TotalCopy, 
   sum(Consume_Capacity) as TotalCapacity, sum(Consume_Fund) as TotalFund
   From XF_Consume Where System_No = 'SK'   And Consume_Time >=  @DateBgn  And  Consume_Time <= @DateEnd  
   Group By Person_ID) a 
   Left Join ST_Person b On a.Person_Id = b.Person_Id) c
   Left Join ST_Department d on c.Dept_ID = d.Dept_ID)
END
ELSE   -- 	帶部門或人員
BEGIN
if  @isDept = 1               --匯總部門
BEGIN
   Select d.Person_No, d.Person_Name, d.Dept_Name,
   sum(c.Consume_TimeLen) as TotalTimeLen,sum(c.Consume_Copy) as TotalCopy,sum(c.Consume_Capacity) as TotalCapacity,
   sum(c.Consume_Fund) as TotalFund
   From 
   (SELECT a.Person_id,a.Person_no,a.Person_name,b.Dept_Name FROM 
   ST_Person a  LEFT JOIN  ST_Department b ON a.Dept_id = b.Dept_id  WHERE  b.Dept_no  LIKE @Dept) d
   Left Join XF_Consume c  
   ON c.Person_Id = d.Person_id WHERE c.System_No = 'SK' 
   And c.Consume_Time >=  @DateBgn  And c. Consume_Time <= @DateEnd  
   Group by d.Person_ID,d.Person_No,d.Person_name,d.Dept_Name
END
ELSE              
BEGIN               ---------單個人員匯總
   Select c.Person_No, c.Person_Name, d.Dept_Name, c.TotalTimeLen ,c.TotalCopy, c.TotalCapacity,c.TotalFund
   From 
   (Select b.Person_No,Person_Name,b.Dept_ID, sum(a.Consume_TimeLen) as TotalTimeLen,sum(a.Consume_Copy) as TotalCopy,sum(a.Consume_Capacity) as TotalCapacity,
   sum(a.Consume_Fund) as TotalFund From
   XF_Consume a   Left Join ST_Person b
   ON a.Person_Id = b.Person_id WHERE a.System_No = 'SK' 
   And a.Consume_Time >=  @DateBgn  And a. Consume_Time <= @DateEnd   And a.Person_ID = Convert(integer,@Dept)
   Group by a.Person_Id,b.Person_No,b.Person_name,b.Dept_ID) c 
   Left Join ST_Department d ON c.Dept_ID = d.Dept_ID
END
  
END
END
ELSE   -----------------------------------------按用途或機號
BEGIN
IF @Dept = ''    --不選擇部門,只按日期范圍
BEGIN
   --匯總機號
   IF @Moc_No = ''
      SET @Sql3 =''
   ELSE
      SET @sql3 = ' And  Moc_No in (' + @Moc_No + ')'

   SET  @Sql1 ='
   Select a.Moc_No, ISNULL(b.Moc_Name, '+ '''''' + ') as Moc_Name, a.TotalTimelen, a.TotalCopy, a.TotalCapacity, a.TotalFund   From (
   Select Moc_No,  sum(Consume_TimeLen) as TotalTimelen, sum(Consume_Copy) as TotalCopy, 
   sum(Consume_Capacity) as TotalCapacity, sum(Consume_Fund) as TotalFund
   From XF_Consume Where System_No = ' + '''SK'''   + @Sql2 + @Sql3      
    + ' Group by Moc_No) a
    Left Join  ST_Moc b ON a.Moc_No = b.Moc_No' 

IF @Type = 3       --按用途
BEGIN
   IF @UseType = ''
     SET @Sql2 = ''
   ELSE
     SET @Sql2 =' Where  x.KQ_UseType  IN (' + @UseType + ')'

   SET @Sql1='
   Select z.*,
   ( Case When x.KQ_UseType = 0 Then ' + '''娛樂''' + 
   ' When x.KQ_UseType = 1 Then ' +  '''開水'''  + 
   'When x.KQ_UseType = 2 Then ' +  '''洗浴''' +
   ' When x.KQ_UseType = 3 Then ' + '''機房'''  +
   'When x.KQ_UseType = 4 Then ' + '''洗衣'''  + 
   'When x.KQ_UseType = 5 Then ' + '''用電''' + 
   'ELSE '  + '''其它''' + ' End) as  MocUse  From (' + @Sql1 + ') z Left Join ST_Moc x ON z.Moc_No=x.Moc_No' + @Sql2
   
   --執行按用途匯總
   SET @Sql1='
   Select  MocUse, Sum(TotalTimeLen) as TotalTimeLen, Sum(TotalCopy) as TotalCopy, Sum(TotalCapacity) as TotalCapacity, 
   Sum(TotalFund) as TotalFund   From (' + @Sql1   + ') y  Group by  y.MocUse'

END
Exec (@Sql1)
END
ELSE   ------------------------帶部門的機號或用途匯總
BEGIN
   IF @Moc_No = ''
      SET @Sql3 =''
   ELSE
      SET  @sql3 = '  And  Moc_No in (' + @Moc_No + ')'

SET @Sql1 ='
   Select e.Moc_No,f.Moc_Name, e.TotalTimeLen,e.TotalCopy,e.TotalCapacity,.e.TotalFund From
   (Select c.Moc_No, 
   sum(c.Consume_TimeLen) as TotalTimeLen,sum(c.Consume_Copy) as TotalCopy,sum(c.Consume_Capacity) as TotalCapacity,
   sum(c.Consume_Fund) as TotalFund
   From 
   (SELECT a.Person_id FROM 
   ST_Person a  LEFT JOIN  ST_Department b ON a.Dept_id = b.Dept_id  WHERE  b.Dept_no  LIKE  ''' + @Dept + ''') d
   Left Join XF_Consume c  
   ON c.Person_Id = d.Person_id WHERE c.System_No ='  + '''SK''' +  @sql2 + @sql3
   + '
   Group by  c.Moc_No) e Left Join ST_Moc f ON e.Moc_No = f.Moc_No' 

IF @Type = 3        --按用途
BEGIN
   IF @UseType = ''
     SET @Sql2 = ''
   ELSE
     SET @Sql2 = '  Where  x.KQ_UseType  IN (' + @UseType + ')'

   SET @Sql1='
   Select z.*,
   ( Case When x.KQ_UseType = 0 Then ' + '''娛樂''' + 
   ' When x.KQ_UseType = 1 Then ' +  '''開水'''  + 
   'When x.KQ_UseType = 2 Then ' +  '''洗浴''' +
   ' When x.KQ_UseType = 3 Then ' + '''機房'''  +
   'When x.KQ_UseType = 4 Then ' + '''洗衣'''  + 
   'When x.KQ_UseType = 5 Then ' + '''用電''' + 
   'ELSE '  + '''其它''' + ' End) as MocUse  From (' + @Sql1 + ') z Left Join ST_Moc x ON z.Moc_No=x.Moc_No' + @Sql2

--執行按用途匯總
   SET @Sql1='
   Select  MocUse, Sum(TotalTimeLen) as TotalTimeLen, Sum(TotalCopy) as TotalCopy, Sum(TotalCapacity) as TotalCapacity, 
   Sum(TotalFund) as TotalFund   From (' + @Sql1   + ') y  Group by  y.MocUse'

END
Exec (@Sql1)  
END
END
GO


IF OBJECT_ID('SK_pd_Consume') IS NOT NULL
   DROP PROCEDURE SK_pd_Consume
GO

-----水控消費明細查詢
CREATE   PROCEDURE dbo.SK_pd_Consume
@DateBgn VARCHAR(20),		--起始日期
@DateEnd VARCHAR(20),		--結束日期
@Moc_No VARCHAR(3000),		--機器編號
@Dept VARCHAR(20),			--部門條件
@isDept bit,
@MulSql varchar(1000)='1=1'
AS
DECLARE @sql1 VARCHAR(3000), @sql2 VARCHAR(3000),@sql3 VARCHAR(3000)
DECLARE @BgnDate VARCHAR(10),@EndDate VARCHAR(10)
IF @Dept = ''    --不選擇部門
BEGIN
SET @sql2 = '  And Consume_Time >= ' + @DateBgn + ' And  Consume_Time <=' + @DateEnd
IF  @Moc_No = ''
SET  @sql3 =''
ELSE
SET  @sql3 = '  And Moc_No in (' + @Moc_No + ')'
SET @sql1='Select * from (
Select e.*,  (CASE WHEN  f.KQ_usetype  = 0 THEN   ' + '''娛樂''' + '
WHEN  f.KQ_usetype  = 1  THEN   ' + '''開水''' +  '
WHEN  f.KQ_usetype  = 2  THEN   ' + '''洗浴''' +'
WHEN  f.KQ_usetype  = 3  THEN   ' + '''機房''' +'
WHEN  f.KQ_usetype  = 4  THEN   ' + '''洗衣''' +'
WHEN  f.KQ_usetype  = 5  THEN   ' + '''用電''' +
' ELSE '  + '''其它'''  + ' END) as UseType From (
Select c.*,d.Dept_Name From
(Select a.*,b.Person_No,b.Person_Name,b.Dept_ID From
(
(Select Person_ID,
(CASE WHEN  Consume_Mode = 1 THEN   ' + '''計時''' + '
WHEN  Consume_Mode = 2 THEN   ' + '''計次''' +
' ELSE '  + '''計流量'''  + ' END) as Consume_Mode,
Consume_Time,Consume_Fund,Moc_No,
(CASE WHEN  Consume_Mode = 1 THEN Consume_TimeLen
WHEN  Consume_Mode = 2 THEN Consume_Copy
ELSE Consume_Capacity END) as Consume_Num
From XF_Consume Where System_No = ' + '''SK''' +
@sql2 + @Sql3 +
'  ) a
Left Join ST_Person b On a.Person_ID = b.Person_ID)
) c
Left Join ST_Department d on c.dept_ID = d.dept_ID' +
   ' ) e Left Join ST_Moc f ON e.Moc_No = f.Moc_No'
set @Sql1=@Sql1+') A where '+@MulSql
Exec (@Sql1)
END
ELSE                --選擇了部門
BEGIN
IF @isDept = 1
BEGIN
IF  @Moc_No = ''
SET  @sql3 =''
ELSE
SET  @sql3 = '  And c.Moc_No in (' + @Moc_No + ')'
SET @Sql1='Select * from (
Select e.*,  (CASE WHEN  f.KQ_usetype  = 0 THEN   ' + '''娛樂''' + '
WHEN  f.KQ_usetype  = 1  THEN   ' + '''開水''' +  '
WHEN  f.KQ_usetype  = 2  THEN   ' + '''洗浴''' +'
WHEN  f.KQ_usetype  = 3  THEN   ' + '''機房''' +'
WHEN  f.KQ_usetype  = 4  THEN   ' + '''洗衣''' +'
WHEN  f.KQ_usetype  = 5  THEN   ' + '''用電''' +
' ELSE '  + '''其它'''  + ' END) as UseType From (
SELECT  d.Dept_name, c.Person_id, d.Person_no,d.Person_name,
(CASE WHEN  c.Consume_Mode = 1 THEN   ' + '''計時''' + '
WHEN  c.Consume_Mode = 2 THEN   ' + '''計次''' +
' ELSE '  + '''計流量'''  + ' END) as Consume_Mode,
c.Consume_Time,c.Consume_Fund,c.Moc_No,
(CASE WHEN  c.Consume_Mode = 1 THEN Consume_TimeLen
WHEN  c.Consume_Mode = 2 THEN Consume_Copy
ELSE Consume_Capacity END) as Consume_Num
FROM
(SELECT a.Person_id,a.Person_no,a.Person_name,b.Dept_Name FROM
ST_Person a  LEFT JOIN  ST_Department b ON a.Dept_id = b.Dept_id  WHERE  b.Dept_no  LIKE '
+  @dept  +  ') d
Left Join XF_Consume c
ON c.Person_Id = d.Person_id WHERE c.system_No = ' + '''SK'''  +
'  And c.Consume_Time >= ' + @DateBgn + ' And  c.Consume_Time <=' + @DateEnd + @Sql3 +
   ' ) e Left Join ST_Moc f ON e.Moc_No = f.Moc_No'
set @Sql1=@Sql1+') A where '+@MulSql
Exec (@sql1)
END
ELSE            --查詢單個人員
BEGIN
SET @sql2 = '  And Consume_Time >= ' + @DateBgn + ' And  Consume_Time <=' + @DateEnd
IF  @Moc_No = ''
SET  @sql3 =''
ELSE
SET  @sql3 = '  And Moc_No in (' + @Moc_No + ')'
SET @sql1='Select * from (
Select e.*,  (CASE WHEN  f.KQ_usetype  = 0 THEN   ' + '''娛樂''' + '
WHEN  f.KQ_usetype  = 1  THEN   ' + '''開水''' +  '
WHEN  f.KQ_usetype  = 2  THEN   ' + '''洗浴''' +'
WHEN  f.KQ_usetype  = 3  THEN   ' + '''機房''' +'
WHEN  f.KQ_usetype  = 4  THEN   ' + '''洗衣''' +
' ELSE '  + '''用電'''  + ' END) as UseType From (
Select c.*,d.Dept_Name From
(Select a.*,b.Person_No,b.Person_Name,b.Dept_ID From
(
(Select Person_ID,
(CASE WHEN  Consume_Mode = 1 THEN   ' + '''計時''' + '
WHEN  Consume_Mode = 2 THEN   ' + '''計次''' +
' ELSE '  + '''計流量'''  + ' END) as Consume_Mode,
Consume_Time,Consume_Fund,Moc_No,
(CASE WHEN  Consume_Mode = 1 THEN Consume_TimeLen
WHEN  Consume_Mode = 2 THEN Consume_Copy
ELSE Consume_Capacity END) as Consume_Num
From XF_Consume Where System_No = ' + '''SK''' + ' And Person_ID =' + @Dept +
@sql2  +
@sql3  +
'  ) a
Left Join ST_Person b On a.Person_ID = b.Person_ID)
) c
Left Join ST_Department d on c.dept_ID = d.dept_ID' +
   ' ) e Left Join ST_Moc f ON e.Moc_No = f.Moc_No'   

set @Sql1=@Sql1+') A where '+@MulSql
Exec(@Sql1)
END
ENd


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



