USE [ChinaCharity]
GO
/****** Object: StoredProcedure [dbo].[UP_GetDeliveryPointByCity] Script Date: 03/01/2013 17:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @TotalCount INT
exec [UP_GetDeliveryPointByCity] -1,-1,-1,-1,@TotalCount output
select @TotalCount
*/
ALTER PROC [dbo].[UP_GetDeliveryPointByCity]
(
@CityID INT,
@ProvinceID INT,
@StartIndex INT,
@EndIndex INT ,
@TotalCount INT OUT
)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
SELECT dp.DeliveryPointID AS DeliveryPointID,SUM(ISNULL(i.Quantity,0)) AS Quantity
INTO #FirstTemp
FROM Inventory i
LEFT JOIN DeliveryPoint dp ON i.DeliveryPointID=dp.DeliveryPointID
LEFT JOIN City c ON dp.CityID=c.Id
WHERE i.ActiveFlag=1
AND dp.ActiveFlag=1
AND ((i.[Status]=5 AND i.InventoryType='Transfer')OR (i.[Status]=9 AND i.InventoryType='Breakage'))
AND (@CityID = -1 OR c.Id = @CityID)
AND (@ProvinceID = -1 OR c.ProvinceId = @ProvinceID)
GROUP BY dp.DeliveryPointID
SELECT dp.DeliveryPointID, dp.DeliveryPointName, c.Name AS CityName, ISNULL(ft.Quantity,0) AS Quantity,(SELECT COUNT([PatientDeliveryPointID])
FROM [PatientDeliveryPoint] pdp ,Patient p
WHERE pdp.[Status] = 2 AND pdp.[DeliveryPointID] = dp.DeliveryPointID AND pdp.ActiveFlag = 1 and p.PatientID = pdp.PatientID
AND (p.JoinApprovalStatus = 'Approved' AND (p.LeaveApprovalStatus <> 'Approved' OR p.LeaveApprovalStatus IS NULL))) AS patientCount
into #temp
FROM DeliveryPoint dp
LEFT JOIN City c ON dp.CityID = c.Id
LEFT JOIN #FirstTemp ft ON ft.DeliveryPointID=dp.DeliveryPointID
WHERE dp.ActiveFlag = 1
AND (@CityID = -1 OR c.Id = @CityID)
AND (@ProvinceID = -1 OR c.ProvinceId = @ProvinceID)
SET @TotalCount = @@ROWCOUNT
SELECT *
FROM (
SELECT *, ROW_NUMBER()OVER(ORDER BY DeliveryPointName) AS RowNum
FROM #temp) TT
WHERE RowNum BETWEEN @StartIndex AND @EndIndex
END
--(SELECT @TransferQuantity = SUM(Inventory.Quantity) FROM Inventory WHERE Inventory.DeliveryPointID = dp.DeliveryPointID AND Inventory.Status = 1 AND Inventory.ActiveFlag = 1 AND Inventory.InventoryType='Transfer')
--(SELECT @LossQuantity= SUM(Inventory.Quantity) FROM Inventory WHERE Inventory.DeliveryPointID = dp.DeliveryPointID AND Inventory.Status = 1 AND Inventory.ActiveFlag = 1 AND Inventory.InventoryType='Breakage')
--SELECT * FROM City c WHERE C.[Name] LIKE '%济南%'
--EXECUTE UP_GetDeliveryPointByCity 135,15,0,10,0