SHARE
TWEET

Untitled

a guest Mar 15th, 2017 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [UTNetSystem_3_2_Letshego_2]
  2. GO
  3. /****** Object:  StoredProcedure [utnetAgentManagement].[GetTransactionsLog]    Script Date: 03/15/2017 13:37:48 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9.  
  10. declare
  11.         @ImplementationID nvarchar(50) = 'Letshego',
  12.         @AgentID bigint = '-1',
  13.         @OperationTypeID bigint = null,
  14.         @CustomerNumber nvarchar(200) = null,
  15.         @ReferenceNumber nvarchar(200) = null,
  16.         @DateFrom nvarchar(10) = '2017-01-01',
  17.         @DateTo nvarchar(10) = '2017-03-15',
  18.         @CurrencyID nchar(3) = 'MZN',
  19.         @ReceiptPrinted bit = null,
  20.         @supervisor nvarchar(50) = NULL,
  21.         @Status varchar(10),
  22.         @TransactionsDataXML xml,
  23.         @Result bigint ,
  24.         @ResultMessage nvarchar(max)
  25.  
  26.  
  27.     IF @AgentID = ''
  28.         SET @AgentID = NULL;
  29.     IF @supervisor = ''
  30.         SET @supervisor = NULL;
  31.     IF @Status = ''
  32.         SET @Status = NULL;
  33.     IF @DateFrom = ''
  34.         SET @DateFrom = NULL;
  35.     IF @DateTo = ''
  36.         SET @DateTo = NULL;
  37.     IF @CustomerNumber = ''
  38.         SET @CustomerNumber = NULL;
  39.     IF @ReferenceNumber = ''
  40.         SET @ReferenceNumber = NULL;
  41.     IF @CurrencyID = ''
  42.         SET @CurrencyID = NULL;
  43.     IF @OperationTypeID = 0
  44.         SET @OperationTypeID = NULL;
  45.  
  46.         IF OBJECT_ID('tempdb..#transaction') IS NOT NULL
  47.         DROP TABLE  #transaction
  48.  
  49.     CREATE TABLE #transaction(tranid bigint)
  50.     IF NOT EXISTS(
  51.         select ag.AgentID
  52.         from [utnetAgentManagement].[tAgents] ag
  53.         LEFT JOIN [utnetAgentManagement].[tAgentMembers] am on ag.AgentID=am.AgentID
  54.         JOIN [utUserManagement].[tUserProfiles] UP on  am.UserProfileID=up.UserProfileID AND up.IsSupervisor=0
  55.         where ag.IsDeleted = 0
  56.         AND ag.IsSuperAgent=0
  57.         AND AgentDetails.value( '(/AgentDetails/Supervisor/text())[1]', 'nvarchar(max)' ) is null
  58.         AND (@supervisor is null OR
  59.             ((@supervisor != '-1' AND am.UserProfileID = @supervisor) OR (@supervisor = '-1')))
  60.         AND (@AgentID is null or
  61.             ((@AgentID != '-1' AND ag.AgentID = @AgentID) OR (@AgentID = '-1')))
  62.         )
  63.     BEGIN  
  64.         insert into  #transaction
  65.         SELECT  distinct TransactionReferenceID
  66.         FROM utnetTeller.tTransactionReferences tr
  67.         JOIN utUserManagement.tUserProfiles up on tr.LevelAgentSupervisor=up.UserProfileID or tr.AgentSupervisor=up.UserProfileID
  68.         WHERE (LevelAgentSupervisor = @supervisor or AgentSupervisor = @supervisor or tr.AgentID=@AgentID)
  69.         AND up.IsDeleted = 0
  70.         AND up.IsLocked = 0
  71.         AND up.IsAuthorized = 1
  72.     END
  73.     ELSE
  74.         insert into  #transaction
  75.         select tr.TransactionReferenceID
  76.         from  utnetTeller.tTransactionReferences tr
  77.  
  78.     print(@AgentID)
  79.    
  80.         SELECT
  81.             tr.AgentID,        
  82.             tr.AgentAccount as floatAccountNumber,
  83.             CASE WHEN ISNULL(ptr.OperationTypeID,tr.OperationTypeID) = 1
  84.                 THEN tra.ToAccountNumber
  85.                 ELSE tra.FromAccountNumber
  86.             END AS CustomerAccountNumber,
  87.             CASE WHEN ISNULL(ptr.OperationTypeID,tr.OperationTypeID) = 3
  88.                 THEN tra.ToAccountNumber
  89.                 ELSE ''
  90.             END AS ReceiverAccountNumber,
  91.             CASE WHEN tr.TransactionDetails IS NULL
  92.                 THEN ''
  93.                 ELSE COALESCE(tr.TransactionDetails.value('(/TransactionDetails//RecipientName/node())[1]', 'nvarchar(max)'),'')
  94.             END AS Receiver,
  95.             tr.TransactionReferenceID,
  96.             ISNULL(tr.TransactionUniqueRef,'') AS TransactionUniqueRef,
  97.             coalesce(pf.Amount,0) AS FeeAmount,
  98.             coalesce(pc.Amount,0) AS CommissionAmount,
  99.             coalesce(pc.SuperAgentAmount,0) AS SuperAgentCommissionAmount,
  100.             tra.FromAccountNumber,
  101.             tra.ToAccountNumber,           
  102.             CASE WHEN ptr.OperationTypeID = 9 THEN 0 ELSE tra.Amount END AS Amount,
  103.             tra.CurrencyID,
  104.             CASE WHEN ptr.OperationTypeID = 3
  105.                 THEN tra.CurrencyID
  106.                 ELSE ''
  107.             END AS ReceiverCurrencyID,
  108.             tr.TransactionStatusID,
  109.             tr.CreatedBy,          
  110.             case when am.IsOwner = '0' then tr.AgentFirstName + ' ' + tr.AgentLastName + ' ('+ isnull(amc.Username, 'No Username')+')' END AS AgentMember,
  111.             ISNULL(ag.AgentDetails.value( '(/AgentDetails/AgentName/text())[1]', 'nvarchar(max)' ), tr.AgentFirstName + ' ' + tr.AgentLastName) + ' ('+ isnull(aguc.Username, 'No Username')+')' as Agent,                 
  112.             up.FirstName + ' ' + up.LastName  + ' ('+ isnull(sc.Username, 'No Username')+')' as Supervisor,        
  113.             CONVERT(NVARCHAR(10), tr.CreatedOn, 103) AS CreatedOn,
  114.             CONVERT(NVARCHAR(5), tr.CreatedOn, 108) AS CreatedOnHour,
  115.             tr.CreatedOn AS CreatedOnFullDate,
  116.             ISNULL(ptr.OperationTypeID,tr.OperationTypeID) as OperationTypeID,
  117.             ot.Name AS OperationType,
  118.             c.CustomerUniqueRef AS CustomerNumber,
  119.             c.FirstName  + ' ' + c.LastName AS Customer,
  120.             CASE WHEN pc.CommissionDetails IS NULL
  121.                 THEN ''
  122.                 ELSE pc.CommissionDetails.value('(/CommissionDetails//CommissionTransactionRef/node())[1]', 'nvarchar(max)')
  123.             END AS CommissionTransactionRef,
  124.             CASE WHEN pc.CommissionDetails IS NULL
  125.                 THEN ''
  126.                 ELSE pc.CommissionDetails.value('(/CommissionDetails//CommissionPostedDate/node())[1]', 'nvarchar(max)')
  127.             END AS CommissionPostedDate,
  128.             ISNULL(pc.CommissionDetails.value('(/CommissionDetails/CommissionApproval/text())[1]', 'nvarchar(100)'),'') as CreatedByName,
  129.             tr.TransactionDetails as transactionsdetailsxml,
  130.             tr.TransactionDetails.value('(/TransactionDetails//Narrative/text())[1]', 'nvarchar(max)') as narrative,
  131.             agup.IsDeleted
  132.         FROM
  133.             utnetTeller.tTransactionReferences tr      
  134.         JOIN
  135.             utnetTeller.tTransactionAmounts tra ON tr.TransactionReferenceID = tra.TransactionReferenceID
  136.         LEFT JOIN
  137.             utnetAgentManagement.tProcessedTransactions ptr ON tr.TransactionReferenceID = ptr.TransactionReferenceID
  138.         LEFT JOIN
  139.             utnetAgentManagement.tProcessedFees pf ON ptr.ProcessedFeeID=pf.ProcessedFeeID
  140.         LEFT JOIN
  141.             utnetAgentManagement.tProcessedCommissions pc ON ptr.ProcessedCommissionID=pc.ProcessedCommissionID
  142.         LEFT JOIN
  143.             utnetCustomer.tCustomers c ON tr.CustomerID = c.CustomerID
  144.         LEFT JOIN
  145.             utnetAgentManagement.tOperationTypes ot ON ISNULL(tr.OperationTypeID,ptr.OperationTypeID) = ot.OperationTypeID             
  146.         LEFT JOIN
  147.             utnetAgentManagement.tAgentMembers am on am.UserProfileID = tr.CreatedBy
  148.         LEFT JOIN
  149.             utnetAgentManagement.tAgentMembers am2 on am2.AgentID = am.AgentID AND am2.IsOwner='true'
  150.         LEFT JOIN
  151.             utnetAgentManagement.tAgents ag on ag.AgentID = am.AgentID
  152.         LEFT JOIN
  153.             utUserManagement.tUserProfiles agup on agup.UserProfileID = am2.UserProfileID
  154.         LEFT JOIN
  155.             utUserManagement.[tUserProfileCredentials] agupc on agupc.UserProfileID = agup.UserProfileID
  156.         LEFT JOIN
  157.             utUserManagement.[tUserCredentials] aguc on aguc.UserCredentialsID = agupc.UserCredentialsID AND aguc.IsSystemCredential = 1
  158.         LEFT JOIN
  159.             utUserManagement.tUserProfiles up on up.UserProfileID = tr.OneLevelUpSupervisor--ag.AgentDetails.value( '(/AgentDetails/Supervisor/text())[1]', 'nvarchar(max)' )--tr.AgentSupervisor
  160.  
  161.         LEFT JOIN (
  162.             [utUserManagement].[tUserProfileCredentials] spc
  163.         INNER JOIN [utUserManagement].[tUserCredentials] sc on (sc.UserCredentialsID = spc.UserCredentialsID AND  sc.IsSystemCredential = 1) ) on spc.UserProfileID = tr.OneLevelUpSupervisor--ag.AgentDetails.value('(/AgentDetails//Supervisor/node())[1]','nvarchar(100)')--up.UserProfileID        
  164.         LEFT JOIN [utUserManagement].[tUserProfileCredentials] ampc on ampc.UserProfileID = tr.CreatedBy
  165.         INNER JOIN [utUserManagement].[tUserCredentials] amc on (amc.UserCredentialsID = ampc.UserCredentialsID AND  amc.IsSystemCredential = 1)
  166.        
  167.         WHERE (@AgentID IS NULL OR
  168.               ((@AgentID != '-1' AND tr.AgentID = @AgentID) OR (@AgentID = '-1' AND agup.IsDeleted = 1)))      
  169.             AND ( tr.TransactionUniqueRef = @ReferenceNumber OR @ReferenceNumber IS NULL )         
  170.             AND ( tr.OperationTypeID = @OperationTypeID OR @OperationTypeID IS NULL )
  171.             AND (tr.TransactionStatusID = @Status or @Status IS NULL)
  172.             AND ( tra.CurrencyID = @CurrencyID OR @CurrencyID IS NULL )
  173.             AND ( CAST(tr.CreatedOn AS DATE) >= CAST(@DateFrom AS DATE)  OR @DateFrom IS NULL )
  174.             AND ( CAST(tr.CreatedOn AS DATE) <= CAST(@DateTo AS DATE)  OR @DateTo IS NULL )
  175.             and tr.TransactionReferenceID in (select tranid from #transaction)
  176.             --and (tr.AgentSupervisor = @supervisor OR tr.AgentID = @SuperAgentID OR tr.AgentID in (select id from #agentnet))
  177.    
  178.  
  179.  
  180.     SET @Result = 0
  181.     SET @ResultMessage = 'utnet_core_sql.GetOperatorTransactions.success'
RAW Paste Data
Top