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

WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'

WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'

WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'

WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'

WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'