if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ContactsQuery]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].Web_ContactsQuery
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
	作者：
	日期：往来查询
	描述：
*/
CREATE PROCEDURE Web_ContactsQuery
(
	@OperatorID VARCHAR(50), --操作员id
	@StypeID VARCHAR(max),	  --分支机构id,多个以逗号隔开
	@BtypeID VARCHAR(max),	  --往来单位id,多个以逗号隔开
	@bfullname VARCHAR(50),	  --往来单位名称筛选
	@Qtype	CHAR(1)  --I 应收  O应付
)
--@encode$--
AS
SET NOCOUNT ON
	SELECT @OperatorId = typeId FROM dbo.employee WHERE UserCode = @OperatorId or fullname=@OperatorId
	CREATE TABLE #stype(typeid VARCHAR(50))
	CREATE TABLE #btype(typeid VARCHAR(50))
	
	--分支权限
	INSERT INTO #stype
	SELECT typeid FROM F_GetAllManagerBaseTypeId(@StypeID,11,@OperatorID)
	
	--往来权限
	INSERT INTO #btype
	SELECT typeid FROM F_GetAllManagerBaseTypeId(@BtypeID,2,@OperatorID)
	
	IF @Qtype='I'
	BEGIN
		SELECT TOP 10 a.BTypeID,a.total,b.FullName,b.UserCode 
		FROM 
		(	SELECT a.BtypeID,SUM(a.ARTotal) AS total
			FROM dbo.BType_ArApTotal a
			INNER JOIN #stype s ON a.stypeid = s.typeid
			INNER JOIN #btype b ON a.BTypeID=b.typeId
			GROUP BY a.BTypeID   
		)a,
		dbo.btype b
		WHERE a.BTypeID=b.typeId
		AND b.typeId LIKE '00001%'
		AND b.FullName LIKE	 '%'+@bfullname+'%'
		ORDER BY a.total DESC,b.usercode
	END
	ELSE
	BEGIN
		SELECT TOP 10 a.BTypeID,a.total,b.FullName,b.UserCode 
		FROM 
		(	SELECT a.BtypeID,SUM(a.ApTotal) AS total
			FROM dbo.BType_ArApTotal a
			INNER JOIN #stype s ON a.stypeid = s.typeid
			INNER JOIN #btype b ON a.BTypeID=b.typeId
			GROUP BY a.BTypeID   
		)a,
		dbo.btype b
		WHERE a.BTypeID=b.typeId
		AND b.typeId LIKE '00002%'
		AND b.FullName LIKE	 '%'+@bfullname+'%'
		ORDER BY a.total DESC,b.usercode
	END
	
	DROP TABLE #btype,#stype
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_IncomeStatement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].Web_IncomeStatement
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
	作者：
	日期：收支利润表
	描述：
*/
CREATE PROCEDURE Web_IncomeStatement
(
	@OperatorID VARCHAR(50), --操作员id
	@StypeID	VARCHAR(max),	  --分支机构id,多个以逗号隔开
	@Qtype		 INT  --查询类型 1=当前，2=本月，3=本年
)
--@encode$--
AS
SET NOCOUNT ON
	SELECT @OperatorId = typeId FROM dbo.employee WHERE UserCode = @OperatorId or fullname=@OperatorId
	CREATE TABLE #stype(typeid VARCHAR(50))
	
	--分支权限
	INSERT INTO #stype
	SELECT typeid FROM F_GetAllManagerBaseTypeId(@StypeID,11,@OperatorID)
	
	DECLARE @InCome NUMERIC(24,10)	--收入
	DECLARE @Cost	NUMERIC(24,10)  --成本
	DECLARE @Profits NUMERIC(24,10) --利润
	
    
	--结果表	
	CREATE TABLE #Result(Typeid VARCHAR(50),nid INT,fullname VARCHAR(200),Total NUMERIC(24,10))

	IF @Qtype=1--当前
	BEGIN
		--收入
		SELECT @InCome=ISNULL(SUM(A.TOTAL),0)
		FROM dbo.AType_Total A,
			dbo.Ac_Items AC,
			#stype s
		WHERE A.ATypeID=AC.nId
			AND s.typeid=a.STypeID
			AND AC.nTypeId LIKE'00003%' AND AC.SonNum=0
		
		--成本
		SELECT @Cost=ISNULL(SUM(A.TOTAL),0)
		FROM dbo.AType_Total A,
			dbo.Ac_Items AC,
			#stype s
		WHERE A.ATypeID=AC.nId
			AND s.typeid=a.STypeID
			AND AC.nTypeId LIKE'00004%' AND AC.SonNum=0
			
		--利润=收入-成本
	   SET @Profits=@InCome-@Cost
	   
	   INSERT INTO #Result
	   SELECT '00003' AS typeid, 65 AS nid,'收入' AS fullname ,@InCome AS total
	   UNION ALL
	   SELECT '00004' AS typeid, 68 AS nid,'成本' AS fullname ,@Cost AS total
	   UNION ALL
	   SELECT '0000500005' AS typeid, 50 AS nid,'利润' AS fullname ,@Profits AS total
	END
    ELSE  --其他查询本年 本月等
    BEGIN
		DECLARE @StartDate DATETIME,
			@EndDate    DATETIME
			
		IF @Qtype = 2 --本月 
		BEGIN
			SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)	   --本月第一天
			SET @EndDate = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))    --本月最后一天
		END
		IF @Qtype = 3 --本年 
		BEGIN
			SET @StartDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)		  --本年第一天
			SET @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))	  --本年最后一天
		END
		
		
		CREATE TABLE #tmpTable(AtypeID INT,NtypeID VARCHAR(50),Total NUMERIC(24,10))
		
		INSERT INTO #tmpTable(AtypeID,NtypeID,Total)
			SELECT l.AtypeID,ac.nTypeId,SUM(l.total)
			FROM dbo.ListTable l,
				dbo.Ac_Items ac,
				 #stype s
			WHERE l.AtypeId=ac.nId
				AND l.STypeID=s.typeid
				AND (AC.nTypeId LIKE '00003%' OR  AC.nTypeId LIKE'00004%') 
				AND AC.SonNum=0
				AND BillDate>@StartDate
				AND BillDate<@EndDate
			GROUP BY l.AtypeId,ac.nTypeId   
		
	    --收入
		SELECT @InCome=ISNULL(SUM(A.TOTAL),0)
		FROM #tmpTable A
	    WHERE a.nTypeId LIKE'00003%'
		
		--成本
		SELECT @Cost=ISNULL(SUM(A.TOTAL),0)
		FROM #tmpTable A
	    WHERE a.nTypeId LIKE'00004%'
			
		--利润=收入-成本
	   SET @Profits=@InCome-@Cost
		 
	   INSERT INTO #Result
	   SELECT '00003' AS typeid, 65 AS nid,'收入' AS fullname ,@InCome AS total
	   UNION ALL
	   SELECT '00004' AS typeid, 68 AS nid,'成本' AS fullname ,@Cost AS total
	   UNION ALL
	   SELECT '0000500005' AS typeid, 50 AS nid,'利润' AS fullname ,@Profits AS total
	   
	   DROP TABLE #tmpTable
    END
    
	SELECT * FROM #Result
	
	
	DROP TABLE #stype,#Result
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_IncomeStatementDetail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].Web_IncomeStatementDetail
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
	作者：
	日期：收支利润表明细
	描述：
*/
CREATE PROCEDURE Web_IncomeStatementDetail
(
	@OperatorID VARCHAR(50), --操作员id
	@StypeID	VARCHAR(max),	  --分支机构id,多个以逗号隔开
	@Qtype		 INT,  --查询类型 1=当前，2=本月，3=本年 
	@detialType	 VARCHAR(50) --明细类型 '00003'=收入，'00004'=成本
)
--@encode$--
AS
SET NOCOUNT ON
	SELECT @OperatorId = typeId FROM dbo.employee WHERE UserCode = @OperatorId or fullname=@OperatorId
	CREATE TABLE #stype(typeid VARCHAR(50))
	
	--分支权限
	INSERT INTO #stype
	SELECT typeid FROM F_GetAllManagerBaseTypeId(@StypeID,11,@OperatorID)
	
	DECLARE @InCome NUMERIC(24,10)	--收入
	DECLARE @Cost	NUMERIC(24,10)  --成本
	DECLARE @Profits NUMERIC(24,10) --利润
	
	
	--结果表	
	CREATE TABLE #Result(Typeid VARCHAR(50),nid INT,fullname VARCHAR(200),Total NUMERIC(24,10))
	
	IF @Qtype=1--当前
	BEGIN
		IF @detialType='00003' --收入
		 BEGIN
			INSERT INTO #Result( Typeid, nid, fullname, Total )
			SELECT '0000300002',66 AS AtypeID,'商品类收入',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000300002%'    AND ac.SonNum=0
			UNION ALL
			SELECT '0000300003',67 AS AtypeID,'其他收入',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000300003%'    AND ac.SonNum=0
			UNION ALL
			SELECT '0000300004',12 AS AtypeID,'返点获利收入',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000300004%'    AND ac.SonNum=0
		 END
		 ELSE
		 BEGIN
			INSERT INTO #Result( Typeid, nid, fullname, Total )
			SELECT '0000400002',69 AS AtypeID,'商品类支出',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000400002%'    AND ac.SonNum=0
			UNION ALL
			SELECT '0000400003',70 AS AtypeID,'费用合计',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000400003%'    AND ac.SonNum=0
			UNION ALL
			SELECT '0000400004',16 AS AtypeID,'返利支出',SUM(a.Total) AS total 
			FROM dbo.AType_Total a
			INNER JOIN dbo.Ac_Items ac ON a.ATypeID=ac.nId
			INNER JOIN #stype s ON a.STypeID=s.typeid
			WHERE ac.NtypeID LIKE '0000400004%'    AND ac.SonNum=0
		 END
	END
    ELSE IF @Qtype<>0 --其他查询本年 本月等
    BEGIN
		DECLARE @StartDate DATETIME,
			@EndDate    DATETIME
			
		IF @Qtype = 2 --本月 
		BEGIN
			SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)	   --本月第一天
			SET @EndDate = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))    --本月最后一天
		END
		IF @Qtype = 3 --本年 
		BEGIN
			SET @StartDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)		  --本年第一天
			SET @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))	  --本年最后一天
		END
		
		CREATE TABLE #tmpTable(AtypeID INT,NtypeID VARCHAR(50),Total NUMERIC(24,10))
	
		INSERT INTO #tmpTable(AtypeID,NtypeID,Total)
		SELECT l.AtypeID,ac.nTypeId,SUM(l.total)
		FROM dbo.ListTable l,
			dbo.Ac_Items ac,
			 #stype s
		WHERE l.AtypeId=ac.nId
			AND l.STypeID=s.typeid
			AND (AC.nTypeId LIKE '00003%' OR  AC.nTypeId LIKE'00004%') 
			AND AC.SonNum=0
			AND BillDate>@StartDate
			AND BillDate<@EndDate
	    GROUP BY l.AtypeId,ac.nTypeId  
	
		--SELECT @StartDate,@EndDate 
    
	   IF @detialType='00003' --收入
		 BEGIN
			INSERT INTO #Result( Typeid, nid, fullname, Total )
			SELECT '0000300002',66 AS AtypeID,'商品类收入',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000300002%'
			UNION ALL
			SELECT '0000300003',67 AS AtypeID,'其他收入',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000300003%'
			UNION ALL
			SELECT '0000300004',12 AS AtypeID,'返点获利收入',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000300004%'
		 END
		 ELSE
		 BEGIN
			INSERT INTO #Result( Typeid, nid, fullname, Total )
			SELECT '0000400002',69 AS AtypeID,'商品类支出',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000400002%'
			UNION ALL
			SELECT '0000400003',70 AS AtypeID,'费用合计',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000400003%'
			UNION ALL
			SELECT '0000400004',16 AS AtypeID,'返利支出',SUM(Total) AS total 
			FROM #tmpTable 
			WHERE NtypeID LIKE '0000400004%'
		 END
		 DROP TABLE #tmpTable
    END
    
	SELECT Typeid,nid,fullname,ISNULL(Total,0) AS Total FROM #Result
	DROP TABLE #stype,#Result
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




GO
/*
*    库存排行查询
*    @PTypeID=''				表示Top5+其他类别
*    @PTypeID='00001'			表示00001类别下明细
*    @PTypeID='00000'			表示其他类别(其他类别PTypeID为00000)
*	 @PageIndex=1				第一页明细数据(查明细时生效)  第一页从1开始
*    @PageSize=5				每页5行明细(查明细时生效)
*	 
*	 SELECT PTypeID,KTypeID,STypeID,FullName,Total
*/
IF EXISTS(SELECT 1 FROM SysObjects WHERE ID=OBJECT_ID(N'Web_Query_Goods_Stock_Rank',N'P'))
BEGIN
	DROP PROC Web_Query_Goods_Stock_Rank
END
GO

CREATE PROC Web_Query_Goods_Stock_Rank
(
	@PTypeID    VARCHAR(100)='',			--商品ID,'' 表示大类,'00000'表示其他,否则表示某一类
	@STypeID    VARCHAR(100)='',			--分支机构ID
	@KTypeID    VARCHAR(100)='',			--仓库ID
	@OperatorID VARCHAR(100)='',			--经手人ID 
	@PageIndex	INT=1,						--默认第一页
	@PageSize	INT=5						--默认每页5行
)
AS
BEGIN
	SELECT @OperatorId = typeId FROM dbo.employee WHERE UserCode = @OperatorId or fullname=@OperatorId
	--分支机构
	CREATE TABLE #STypeId(TypeId	VARCHAR(100))
	INSERT INTO #STypeId(TypeId) SELECT TypeID FROM F_GetAllManagerBaseTypeId(@stypeid,11,@OperatorID)

	--商品
	CREATE TABLE #PTypeId(TypeId	VARCHAR(100))
	INSERT INTO #PTypeId(TypeId) SELECT TypeID FROM F_GetAllManagerBaseTypeId('',1,@OperatorID)

	--仓库
	CREATE TABLE #KTypeId(TypeId	VARCHAR(100))
	INSERT INTO  #KTypeId(typeid) SELECT TypeID FROM F_GetAllManagerBaseTypeId(@KTypeId,4,@OperatorID)

	--分类结果
	CREATE TABLE #QueryResult
	(
		PTypeID VARCHAR(100),
		KTypeID VARCHAR(100),
		STypeID VARCHAR(100),
		Total	NUMERIC(24,10),
		FullName VARCHAR(100)
	)
	
	;WITH QueryCTE
	AS
	(
		SELECT  
				LEFT(A.PTypeID, 5) AS PTypeID ,
				@KTypeID  AS KTypeID,
				@STypeID  AS STypeID,
				SUM(ISNULL(A.Total,0)) AS Total
		FROM    dbo.GoodsStocks A
				INNER JOIN dbo.Ptype B ON A.PtypeId = B.TypeID
				INNER JOIN #KTypeId C ON A.KtypeId = C.TypeId
				INNER JOIN #PTypeId D ON A.PTypeID = D.TypeId
				INNER JOIN Stock E ON C.TypeID = E.typeId
				INNER JOIN #STypeId F ON E.STypeID = F.TypeId
		WHERE   LEN(A.PtypeId) > 5
				GROUP BY  LEFT(A.PTypeID,5)
	)
	INSERT INTO #QueryResult
	        ( PTypeID ,
	          KTypeID ,
	          STypeID ,
	          Total 
	        )
	SELECT	  PTypeID ,
			  KTypeID ,
			  STypeID ,
			  Total 
    FROM QueryCTE ORDER BY Total DESC
	UPDATE A SET A.FullName=B.FullName FROM #QueryResult A,PType B WHERE A.PTypeID=B.typeId AND A.FullName IS NULL
	
	--TOP 5 分类+其他
	IF @PTypeID=''
	BEGIN
		--其他类金额=总金额-TOP5分类金额
		;WITH OtherTotalCTE
		AS
		(
            SELECT  SUM(ISNULL(A.Total, 0)) - ( SELECT  SUM(ISNULL(Total, 0))
                                                FROM    ( SELECT TOP 5
                                                              Total
                                                          FROM
                                                              #QueryResult
                                                          ORDER BY Total DESC
                                                        ) V
                                              ) AS Total
            FROM    dbo.GoodsStocks A
                    INNER JOIN dbo.Ptype B ON A.PtypeId = B.TypeID
                    INNER JOIN #KTypeId C ON A.KtypeId = C.TypeId
                    INNER JOIN #PTypeId D ON A.PTypeID = D.TypeId
                    INNER JOIN Stock E ON C.TypeID = E.typeId
                    INNER JOIN #STypeId F ON E.STypeID = F.TypeId
		)
		
		SELECT TOP 5 PTypeID,KTypeID,STypeID,FullName,Total FROM #QueryResult
		UNION ALL
		SELECT '00000',@KTypeID,@STypeID,'其他',Total FROM OtherTotalCTE
	END
	--其他类别明细
	ELSE IF @PTypeID='00000'
	BEGIN
		;WITH QueryCTE
		AS
		(
		  SELECT    A.PTypeID ,
					@KTypeID  AS KTypeID,
					@STypeID AS STypeID,
					SUM(ISNULL(A.Total,0)) AS Total
		  FROM      dbo.GoodsStocks A
					INNER JOIN dbo.Ptype B ON A.PtypeId = B.TypeID
					INNER JOIN #KTypeId C ON A.KtypeId = C.TypeId
					INNER JOIN #PTypeId D ON A.PTypeID = D.TypeId
					INNER JOIN Stock E ON C.TypeID = E.typeId
					INNER JOIN #STypeId F ON E.STypeID = F.TypeId
		  WHERE     LEFT(A.PTypeID,5) NOT IN(SELECT TOP 5 PTypeID FROM #QueryResult ORDER BY Total DESC)
					GROUP BY  A.PTypeID
		)

		SELECT PTypeID,KTypeID,STypeID,Total,FullName FROM (
			SELECT  A.PTypeID ,
					A.KTypeiD ,
					A.STypeID ,
					A.Total ,
					B.FullName,
					ROW_NUMBER() OVER(ORDER BY A.Total DESC) AS RowIndex
			FROM    QueryCTE A
					INNER JOIN PType B ON A.PTypeID = B.TypeID
        ) V
       WHERE   RowIndex BETWEEN (@PageIndex-1) * @PageSize + 1 AND @PageSize * @PageIndex
	END
	--某一类明细
	ELSE 
	BEGIN
		;WITH QueryCTE
		AS
		(
		  SELECT    A.PTypeID ,
					@KTypeID  AS KTypeID,
					@STypeID AS STypeID,
					SUM(ISNULL(A.Total,0)) AS Total
		  FROM      dbo.GoodsStocks A
					INNER JOIN dbo.Ptype B ON A.PtypeId = B.TypeID
					INNER JOIN #KTypeId C ON A.KtypeId = C.TypeId
					INNER JOIN #PTypeId D ON A.PTypeID = D.TypeId
					INNER JOIN Stock E ON C.TypeID = E.typeId
					INNER JOIN #STypeId F ON E.STypeID = F.TypeId
		  WHERE     LEFT(A.PTypeID,5) = @PTypeID
					GROUP BY  A.PTypeID
		)	

		SELECT PTypeID,KTypeID,STypeID,Total,FullName FROM (
			SELECT  A.PTypeID ,
					A.KTypeiD ,
					A.STypeID ,
					A.Total ,
					B.FullName,
					ROW_NUMBER() OVER(ORDER BY A.Total DESC) AS RowIndex
			FROM    QueryCTE A
					INNER JOIN PType B ON A.PTypeID = B.TypeID
        ) V
       WHERE   RowIndex BETWEEN (@PageIndex-1) * @PageSize + 1 AND @PageSize * @PageIndex
	END
	DROP TABLE #KTypeId,#PTypeId,#STypeId,#QueryResult
END
GO





GO
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'Web_QuerySaleRanking') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE Web_QuerySaleRanking
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
	作者：唐韬
	日期：2015-11-18
	描述：钉钉管家婆手机版销售排名
*/
CREATE PROCEDURE Web_QuerySaleRanking
(
	@OperatorId			VARCHAR(100)='',	-- 操作员ID
	@STypeId			VARCHAR(MAX)='',	-- 分支机构id 只能 为空 或则 一个 为空 就是 所有分支机构
	@Date				INT	= 1		   ,	-- 1 按天 2 按月 3 按年
	@ParId				VARCHAR(50) =''	,	-- 父类typeid @ParId为空查询大类 @ParId为空不查询明细  @ParId=other时 查询其他子类
	@PageIndex			INT = 1		,		-- 第一页为1 第二页为2 ......
	@PageSize			INT = 5
)

AS
SET NOCOUNT ON
SELECT @OperatorId = typeId FROM dbo.employee WHERE UserCode = @OperatorId or fullname=@OperatorId
--------------------------------------------------------------权限检查-------------------------------------------------------
IF @OperatorID IS NULL RETURN 1
DECLARE @Re int,@MAtypeid varchar(50), @MBtypeid varchar(50), @MBtypeid2 varchar(50), @MEtypeid varchar(50),
            @MKtypeid varchar(50), @MPtypeid varchar(50), @MExpense varchar(50), @MIncome varchar(50),@MCapital VARCHAR(50),@MDtypeid VARCHAR(50),@MStypeid VARCHAR(50)
EXEC @Re=GP_CheckOperatorRight23_Rep @OperatorID,@MAtypeid OUTPUT,@MBtypeid OUTPUT,@MBtypeid2 OUTPUT,@MEtypeid OUTPUT,@MKtypeid OUTPUT,@MPtypeid OUTPUT,@MExpense OUTPUT,@MIncome OUTPUT,@MCapital OUTPUT,@MDtypeid OUTPUT,@MStypeid OUTPUT
IF @Re<>0 RETURN @Re

--分支机构
CREATE TABLE #GetSTypeId(TypeId	varchar(100))
INSERT INTO #GetSTypeId(TypeId) SELECT typeid FROM F_GetAllManagerBaseTypeId(@STypeId,11,@OperatorID)

--商品
CREATE TABLE #GetPTypeId(TypeId	varchar(100))
INSERT INTO #GetPTypeId(TypeId) SELECT * FROM F_GetAllManagerBaseTypeId('',1,@OperatorID)
--------------------------------------------------------------权限检查-------------------------------------------------------

--单据
DECLARE @CurrentDate VARCHAR(10);
SELECT @CurrentDate = CONVERT(VARCHAR(10),GETDATE(),120)
CREATE TABLE #GetBillIndex(BillNumberid NUMERIC(10,0),BillType INT)
IF @Date = 1
BEGIN
	INSERT INTO #GetBillIndex(BillNumberid,BillType) 
    SELECT A.BillNumberId , A.BillType FROM dbo.BillIndex A
    INNER JOIN #GetSTypeId S ON S.TypeId = A.Stypeid
    WHERE A.BillType IN (11,45)
    AND A.ifcheck = 't'
    AND A.RedWord = 0
    AND A.BillDate = @CurrentDate
END
ELSE IF @Date = 2
BEGIN
	INSERT INTO #GetBillIndex(BillNumberid,BillType) 
    SELECT A.BillNumberId , A.BillType FROM dbo.BillIndex A
    INNER JOIN #GetSTypeId S ON S.TypeId = A.Stypeid
    WHERE A.BillType IN (11,45)
    AND A.ifcheck = 't'
    AND A.RedWord = 0
    AND A.BillDate BETWEEN CONVERT(VARCHAR(10),DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) ,120) AND @CurrentDate
END
ELSE IF @Date = 3
BEGIN
	INSERT INTO #GetBillIndex(BillNumberid,BillType) 
    SELECT A.BillNumberId , A.BillType FROM dbo.BillIndex A
    INNER JOIN #GetSTypeId S ON S.TypeId = A.Stypeid
    WHERE A.BillType IN (11,45)
    AND A.ifcheck = 't'
    AND A.RedWord = 0
    AND A.BillDate BETWEEN CONVERT(VARCHAR(10),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) ,120) AND @CurrentDate
END

--#Temp
CREATE TABLE #GetTemp(TypeID VARCHAR(50),Total NUMERIC(24,10))
CREATE TABLE #GetTempType(TypeID VARCHAR(50))
IF @ParId IS NULL OR @ParId = ''
BEGIN
	--大类
	INSERT INTO #GetTemp (TypeID,Total)
	SELECT ParID, SUM(total) AS 'Total' 
	FROM (
			SELECT LEFT(A.PtypeId,5) AS 'ParID', A.total  FROM dbo.SaleBill A		
			INNER JOIN #GetBillIndex B ON B.BillNumberId = A.BillNumberid
			INNER JOIN #GetPTypeId C ON C.TypeId = A.PtypeId
			WHERE  LEN(A.PtypeId) > 5 AND B.BillType = 11
			
			UNION ALL
			
			SELECT LEFT(D.PtypeId,5) AS 'ParID',-D.total  FROM dbo.saleBackBill D	
			INNER JOIN #GetBillIndex E ON E.BillNumberId = D.BillNumberid
			INNER JOIN #GetPTypeId F ON F.TypeId = D.PtypeId
			WHERE LEN(D.PtypeId) > 5 AND E.BillType = 45

	) AS T GROUP by ParID
	
	DECLARE 
	@Top5Total		NUMERIC(24,10) = 0,
	@OtherTotal		NUMERIC(24,10) = 0;

	SELECT @Top5Total = ISNULL(SUM(Total),0) FROM (
		SELECT TOP 5 Total FROM #GetTemp ORDER BY Total DESC
	) T1
	/* 
	SELECT @OtherTotal = ISNULL(SUM(Total)-@Top5Total,0) FROM #GetTemp
	*/
	-- @OtherTotal
	SELECT @OtherTotal= SUM(total)
	FROM (
		SELECT A.total  FROM dbo.SaleBill A		
		INNER JOIN #GetBillIndex B ON B.BillNumberId = A.BillNumberid
		INNER JOIN #GetPTypeId C ON C.TypeId = A.PtypeId
		WHERE LEFT(A.PtypeId,5) NOT IN (
			SELECT TOP 5 TypeId FROM #GetTemp ORDER BY Total DESC
		) AND B.BillType = 11
		UNION ALL
		
		SELECT -D.total  FROM dbo.saleBackBill D	
		INNER JOIN #GetBillIndex E ON E.BillNumberId = D.BillNumberid
		INNER JOIN #GetPTypeId F ON F.TypeId = D.PtypeId
		WHERE LEFT(D.PtypeId,5) NOT IN (
			SELECT TOP 5 TypeId FROM #GetTemp ORDER BY Total DESC
		) AND E.BillType = 45
	)AS T
	
	SELECT ISNULL( Total,0) AS 'Total' ,TypeID ,FullName FROM (
		SELECT TOP 5 A.Total,A.TypeID,B.FullName 
		FROM #GetTemp A 
		INNER JOIN dbo.ptype B ON a.TypeID = b.typeId 
		ORDER BY Total DESC
	)AS T1
	UNION ALL
	SELECT ISNULL(@OtherTotal,0) AS 'Total','other' AS 'ParID', '其他' AS 'FullName'
	
END
ELSE
BEGIN
	--明细
	IF @ParId <> 'other'
	BEGIN
		INSERT INTO #GetTemp (TypeID,Total)
		SELECT PtypeId , SUM(total) AS 'Total' 
		FROM (
				SELECT A.PtypeId , A.total  FROM dbo.SaleBill A		
				INNER JOIN #GetBillIndex B ON B.BillNumberId = A.BillNumberid
				INNER JOIN #GetPTypeId C ON C.TypeId = A.PtypeId
				WHERE LEFT(A.PtypeId,5) = @ParId AND B.BillType = 11
				
				UNION ALL
				
				SELECT D.PtypeId ,-D.total  FROM dbo.saleBackBill D	
				INNER JOIN #GetBillIndex E ON E.BillNumberId = D.BillNumberid
				INNER JOIN #GetPTypeId F ON F.TypeId = D.PtypeId
				WHERE LEFT(D.PtypeId,5) = @ParId  AND E.BillType = 45

		) AS T GROUP by PtypeId

	END
	ELSE
	BEGIN
		
		INSERT INTO #GetTempType (TypeID)
		SELECT TOP 5 ParID FROM (
			SELECT ParID, SUM(total) AS 'Total' 
			FROM (
					SELECT LEFT(A.PtypeId,5) AS 'ParID', A.total  FROM dbo.SaleBill A		
					INNER JOIN #GetBillIndex B ON B.BillNumberId = A.BillNumberid
					INNER JOIN #GetPTypeId C ON C.TypeId = A.PtypeId
					WHERE LEN(A.PtypeId) > 5 AND B.BillType = 11
					
					UNION ALL
					
					SELECT LEFT(D.PtypeId,5) AS 'ParID',-D.total  FROM dbo.saleBackBill D	
					INNER JOIN #GetBillIndex E ON E.BillNumberId = D.BillNumberid
					INNER JOIN #GetPTypeId F ON F.TypeId = D.PtypeId
					WHERE LEN(D.PtypeId) > 5 AND E.BillType = 45

			) AS T GROUP BY ParID
		) AS T1 ORDER BY Total DESC
		
		INSERT INTO #GetTemp (TypeID,Total)
		SELECT PtypeId , SUM(total) AS 'Total' 
		FROM (
				SELECT A.PtypeId , A.total  FROM dbo.SaleBill A		
				INNER JOIN #GetBillIndex B ON B.BillNumberId = A.BillNumberid
				INNER JOIN #GetPTypeId C ON C.TypeId = A.PtypeId
				WHERE LEFT(A.PtypeId,5) NOT IN (
					SELECT TypeID FROM #GetTempType
				)AND B.BillType = 11
				
				UNION ALL
				
				SELECT D.PtypeId ,-D.total  FROM dbo.saleBackBill D	
				INNER JOIN #GetBillIndex E ON E.BillNumberId = D.BillNumberid
				INNER JOIN #GetPTypeId F ON F.TypeId = D.PtypeId
				WHERE LEFT(D.PtypeId,5) NOT IN (
					SELECT TypeID FROM #GetTempType
				)AND E.BillType = 45
		) AS T GROUP by PtypeId

	END
			
	SELECT Total, TypeID, FullName, Standard, Type FROM (
		SELECT ROW_NUMBER() OVER( ORDER BY Total DESC ) AS RowIndex, A.Total,A.TypeID,B.FullName,B.Standard,B.Type
		FROM #GetTemp A 
		INNER JOIN dbo.ptype B ON A.TypeID = B.typeId  
	) AS T 
	WHERE RowIndex BETWEEN (@PageIndex-1) * @PageSize + 1 AND @PageSize * @PageIndex
		
END

DROP TABLE	#GetSTypeId,#GetPTypeId,#GetBillIndex,#GetTemp,#GetTempType

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO













USE [master]
GO

/****** Object:  Table [dbo].[DDingInfo]    Script Date: 11/25/2015 10:23:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS(SELECT 1 FROM [master]..SysObjects WHERE ID=OBJECT_ID(N'[master]..[DDingInfo]',N'U'))
BEGIN
CREATE TABLE [master]..[DDingInfo](
	[CorpID] [varchar](500) NULL,
	[CorpSecrect] [varchar](500) NULL,
	[ServerIP] [varchar](50) NULL,
	[ServerPort] [int] NULL,
	[AppID] [varchar](50) NULL,
	[GUID] [nvarchar](50) NULL
) ON [PRIMARY]
END

GO

SET ANSI_PADDING OFF
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GP_RWXZTSelectnew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].GP_RWXZTSelectnew
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[GP_RWXZTSelectnew] 
(
  @zttablename   varchar(100),
  @Version 	 VARCHAR(100),
  @mversion  VARCHAR(10)
 )
AS
create table #SystemTaleb([name]varchar(100)not null,[dbid] varchar(100) not null);
declare @IntisVersion tinyint 
declare @tablename varchar(100)
declare @dbid varchar(100)
declare @intexists tinyint

declare @strsql nvarchar(1000)
DECLARE @mark int=0
declare cursorname cursor  ------查询游标
 local scroll static 
  for  select [name],dbid from sysdatabases where [name] not in ('master','tempdb','model','msdb') AND [name] not like '%ReportServer%'
open cursorname
fetch next from cursorname
    into @tablename,@dbid
 -------------------------deleted为0的账套筛选---------------------------   
while @@FETCH_STATUS=0
begin
	begin try
	set @strSql='use ['+@tablename+']'+CHAR(13)
	set @strSql=@strsql+'if exists(select * from dbo.sysobjects where id = object_id(N''[dbo].['+@zttablename+']'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) set @intexists=1 else set @intexists=0 '	
	EXECUTE sp_executesql @strSql,N'@intexists tinyint OUTPUT',@intexists OUTPUT
	if @intexists=1
	begin
		set @strsql=' IF EXISTS(select TOP 1 1 from ['+@tablename+']..sysdata WHERE SubName=''Version'' AND SubValue='''+@Version+''') SET @IntisVersion=1 else set @IntisVersion=0 '
		EXECUTE sp_executesql @strSql,N'@IntisVersion tinyint OUTPUT',@IntisVersion OUTPUT		
		if(@IntisVersion=1) AND ISNULL(@mversion,0)=''
			insert into #SystemTaleb values(@tablename,@dbid)
		ELSE
		BEGIN
			set @strsql=' IF EXISTS(select TOP 1 1 from ['+@tablename+']..sysdata WHERE SubName=''mversion'' AND SubValue='''+@mversion+''') SET @IntisVersion=1 else set @IntisVersion=0 '
			EXECUTE sp_executesql @strSql,N'@IntisVersion tinyint OUTPUT',@IntisVersion OUTPUT	
			if(@IntisVersion=1)
				insert into #SystemTaleb values(@tablename,@dbid)	
		END
	end
	end try
	begin catch
	--select * from #SystemTaleb	
	SET @mark+=1	
	fetch next from cursorname
	into @tablename,@dbid
	end catch
	if(@mark=0)
	begin
	fetch next from cursorname
	into @tablename,@dbid
	end
	SET @mark=0
end
close cursorname
deallocate cursorname
select * from #SystemTaleb
drop table #SystemTaleb



SET ANSI_PADDING OFF
GO










