Database

DECLARE param_last_tier INT;

IF EXISTS (SELECT tier_number FROM order_tiers WHERE order_id = param_order_id) THEN
BEGIN
    SET param_last_tier = (SELECT MAX(tier_number) FROM order_tiers WHERE order_id = param_order_id) + 1;
END;
ELSE
BEGIN
    SET param_last_tier = 1;
END;
END IF;

INSERT INTO order_tiers (order_id, tier_type, tier_number, created_user)
VALUES (param_order_id, param_tier_type, param_last_tier, param_created_user);

subscribers.ToList().Select(Function(cs) New ws_subscribers_list With

Public Shared Function load_subscribers( _
        ByVal pPageIndex As Integer,
        ByVal pPageSize As Integer,
        ByRef pResults As List(Of ws_subscribers_list)) As Integer

        Dim pValue As Integer = 0
        Dim context As New hx5Context()

        Dim subscribers As IQueryable(Of CRM_SUBSCRIBERS) = context.crm_Subscribers

        'Set the Sort Order of the request
        subscribers = subscribers.OrderBy(Function(m) m.Name)

        'Get a Single Page of Data
        If (pPageIndex = 1) Then
            subscribers = subscribers.Take(pPageSize)
        Else
            subscribers = subscribers.Skip((pPageIndex - 1) * pPageSize).Take(pPageSize)
        End If

        pResults = _
            subscribers.Select(Function(cs) New ws_subscribers_list With
            {
                .SubscriberID = cs.SubscriberID,
                .CustomerID = cs.CustomerID,
                .Name = cs.Name,
                .EmailAddress = cs.EmailAddress,
                .Source = cs.Source,
                .Subscribe = cs.Subscribe,
                .DateCreated = cs.DateCreated,
                .Status = cs.Status
            }
        ).ToList()

        pValue = pResults.Count()

        Return pValue

    End Function

-- Add the parameters for the stored procedure here
	@GroupId int, 
	@StartDate DATETIME = NULL, 
	@EndDate DATETIME = NULL,
	@RegionCode varchar(30) = NULL
AS
BEGIN

	
	DECLARE @AgencyTable TABLE (GpoId int, OfcId int,  Total_Bills BIGINT, Bill_Value BIGINT, Commission BIGINT, Total_Amount BIGINT) 

	SET @EndDate = convert(datetime, convert(Varchar(12), @EndDate, 106) + ' 23:59:59PM')

 	
	--1. Billing Summary By GPO Name
	INSERT @AgencyTable (GpoId, OfcId, Total_Bills, Bill_Value, Commission, Total_Amount) 
	SELECT bil.GroupId,  Bil.SubOfficeId , isnull(COUNT(Bil.ConsumerNumber),0) --AS Total_Bills, 
	   ,ISNULL(SUM(Bil.C_Amount),0) --AS Bill_Value, 
	   ,ISNULL(SUM(Bil.Commission),0) --AS Commission, 
	   ,ISNULL(SUM(Bil.C_Amount),0) - ISNULL(SUM(Bil.Commission),0) --AS Total_Amount
    FROM BillTxnSO as Bil inner join pp_offices ofc On bil.GroupId = ofc.Group_Id and bil.SubOfficeId = ofc.OfficeCode  
    Where bil.GroupId = @GroupId AND TransDate  BETWEEN @StartDate AND @EndDate
    Group by bil.GroupId, Bil.SubOfficeId 
    
    --select * from @AgencyTable
	 
	 SELECT   ofc.OfficeName as SubOffice_Name  , isnull(gpo.Total_Bills,0)as Total_Bills , isnull(gpo.Bill_Value,0)as Bill_Value , isnull(gpo.Commission,0) as Commission, isnull(gpo.Total_Amount,0) as Total_Amount   
      FROM @AgencyTable gpo 
INNER JOIN pp_offices ofc On ofc.Group_Id = gpo.GpoId and gpo.OfcId  = ofc.OfficeCode 
   ORDER BY  ofc.OfficeName   
   
END