]]> _ _ _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Const LiveUrl As String = "https://api-3t.paypal.com/nvp" Private Const SandboxUrl As String = "https://api-3t.sandbox.paypal.com/nvp" Private Const ApiVersion As String = "85.0" Private Const MethodTrxSearch As String = "TransactionSearch" Private Const MethodGetDetails As String = "GetTransactionDetails" Private Const SubscriptionId As String = "SUBSCRIPTIONID" Private Const ReceiptId As String = "RECEIPTID" Private Const TimeStamp_1P As String = "L_TIMESTAMP{0}" Private Const TimeZone_1P As String = "L_TIMEZONE{0}" Private Const Type_1P As String = "L_TYPE{0}" Private Const Email_1P As String = "L_EMAIL{0}" Private Const Name_1P As String = "L_NAME{0}" Private Const TransactionId_1P As String = "L_TRANSACTIONID{0}" Private Const Status_1P As String = "L_STATUS{0}" Private Const Amt_1P As String = "L_AMT{0}" Private Const CurrencyCode_1P As String = "L_CURRENCYCODE{0}" Private Const FeeAmt_1P As String = "L_FEEAMT{0}" Private Const NetAmt_1P As String = "L_NETAMT{0}" Private Const ErrorCode_1P As String = "L_ERRORCODE{0}" Private Const LongMessage_1P As String = "L_LONGMESSAGE{0}" Private Const Qty_1P As String = "L_QTY{0}" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum ServerTypes Production Sandbox End Enum ' ServerTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum TransactionClassTypes All Sent Received MassPay MoneyRequest FundsAdded FundsWithdrawn Referral Fee Subscription Dividend Billpay Refund CurrencyConversions BalanceTransfer Reversal Shipping BalanceAffecting ECheck End Enum ' TransactionClassTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Enum StatusTypes Pending Processing Success Denied Reversed End Enum ' StatusTypes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Try ' Setup method parameters. Dim trxParams As String = _ "&STARTDATE=" + GetDateString_(m_startDate, Me.IsStartDateVariable) + _ "&ENDDATE=" + GetDateString_(m_endDate, Me.IsEndDateVariable) + _ "&TRANSACTIONCLASS=" + Me.TransactionClass.ToString() + _ "&STATUS=" + Me.Status.ToString() + _ "&" + Me.Other.Replace("\r\n", "&") Dim trxList As Hashtable = QueryPayPal_(MethodTrxSearch, trxParams) Dim index As Integer = 0 While trxList.Contains(String.Format(TimeStamp_1P, index)) With Me.OutputBuffer Call .AddRow() Dim trxId As String = CStr(trxList(String.Format(TransactionId_1P, index))) .Timestamp = Convert.ToDateTime(trxList(String.Format(TimeStamp_1P, index))) .Timezone = CStr(trxList(String.Format(TimeZone_1P, index))) .Type = CStr(trxList(String.Format(Type_1P, index))) .Email = CStr(trxList(String.Format(Email_1P, index))) .Name = CStr(trxList(String.Format(Name_1P, index))) .TransactionId = trxId .Status = CStr(trxList(String.Format(Status_1P, index))) .Amt = Convert.ToDecimal(trxList(String.Format(Amt_1P, index))) .CurrencyCode = CStr(trxList(String.Format(CurrencyCode_1P, index))) .FeeAmt = Convert.ToDecimal(trxList(String.Format(FeeAmt_1P, index))) .NetAmt = Convert.ToDecimal(trxList(String.Format(NetAmt_1P, index))) If Me.GetDetails Then ' Retrieve transaction details. Dim detailParams As String = "&TRANSACTIONID=" + trxId Dim trxDetails As Hashtable = QueryPayPal_(MethodGetDetails, detailParams) Dim subscrId As String = CStr(trxDetails(SubscriptionId)) Dim recId As String = CStr(trxDetails(ReceiptId)) ' Setup detail id. If Not String.IsNullOrEmpty(subscrId) Then .DetailId = subscrId ElseIf Not String.IsNullOrEmpty(recId) Then .DetailId = recId Else .DetailId = trxId End If ' Setup other details. .DetailName = GetNVPList_(trxDetails, Name_1P) .DetailQty = GetNVPList_(trxDetails, Qty_1P) End If End With index += 1 End While Catch ex As Exception Call FireError_(ex.Message) End Try End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.User) Then Throw New ApplicationException("Specify user name.") End If If String.IsNullOrEmpty(Me.Password) Then Throw New ApplicationException("Specify password.") End If If String.IsNullOrEmpty(Me.Signature) Then Throw New ApplicationException("Specify signature.") End If If String.IsNullOrEmpty(m_startDate) Then Throw New ApplicationException("Specify start date.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Setup static. Call result.Add("ServerType") Call result.Add("User") Call result.Add("Password") Call result.Add("Signature") Call result.Add("GetDetails") Call result.Add("TransactionClass") Call result.Add("Status") Call result.Add("Other") ' Setup dynamic. Call result.Add("IsStartDateVariable") Call result.Add(IIf(Me.IsStartDateVariable, "StartDateVariable", "StartDate")) Call result.Add("IsEndDateVariable") Call result.Add(IIf(Me.IsEndDateVariable, "EndDateVariable", "EndDate")) Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property ServerType() As ServerTypes Get ServerType = m_serverType End Get Set(ByVal value As ServerTypes) m_serverType = value End Set End Property ' ServerType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property User() As String Get User = m_user End Get Set(ByVal value As String) m_user = value End Set End Property ' User ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Password() As String Get Password = m_pwd End Get Set(ByVal value As String) m_pwd = value End Set End Property ' Password ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property Signature() As String Get Signature = m_signature End Get Set(ByVal value As String) m_signature = value End Set End Property ' Signature ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property GetDetails() As Boolean Get GetDetails = m_getDetails End Get Set(ByVal value As Boolean) m_getDetails = value End Set End Property ' GetDetails ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property IsStartDateVariable() As Boolean Get IsStartDateVariable = m_isStartDateVar End Get Set(ByVal value As Boolean) If m_isStartDateVar <> value Then m_isStartDateVar = value m_startDate = String.Empty End If End Set End Property ' IsStartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property StartDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_startDate) Then result = Convert.ToDateTime(m_startDate) End If StartDate = result End Get Set(ByVal value As DateTime) m_startDate = value.ToString() End Set End Property ' StartDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property StartDateVariable() As String Get StartDateVariable = m_startDate End Get Set(ByVal value As String) m_startDate = value End Set End Property ' StartDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property IsEndDateVariable() As Boolean Get IsEndDateVariable = m_isEndDateVar End Get Set(ByVal value As Boolean) If m_isEndDateVar <> value Then m_isEndDateVar = value m_endDate = String.Empty End If End Set End Property ' IsEndDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property EndDate() As DateTime Get Dim result As DateTime If Not String.IsNullOrEmpty(m_endDate) Then result = Convert.ToDateTime(m_endDate) End If EndDate = result End Get Set(ByVal value As DateTime) m_endDate = value.ToString() End Set End Property ' EndDate ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property EndDateVariable() As String Get EndDateVariable = m_endDate End Get Set(ByVal value As String) m_endDate = value End Set End Property ' EndDateVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ Public Property TransactionClass() As TransactionClassTypes Get TransactionClass = m_trxClass End Get Set(ByVal value As TransactionClassTypes) m_trxClass = value End Set End Property ' TransactionClass ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Status() As StatusTypes Get Status = m_status End Get Set(ByVal value As StatusTypes) m_status = value End Set End Property ' Status ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ _ Public Property Other() As String Get Other = m_other End Get Set(ByVal value As String) m_other = value End Set End Property ' Other #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub FireError_(ByVal message As String) Dim cancel As Boolean Call Me.ComponentMetaData.FireError(0, "PayPal Trx Source", message, String.Empty, 0, cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function QueryPayPal_(ByVal method As String, ByVal params As String) As Hashtable Dim result As Hashtable ' Setup server URL. Dim serverUrl As String = String.Empty Select Case Me.ServerType Case ServerTypes.Production serverUrl = LiveUrl Case ServerTypes.Sandbox serverUrl = SandboxUrl Case Else Throw New Exception("Unhandled server type.") End Select params = _ "USER=" + Me.User + _ "&PWD=" + Me.Password + _ "&SIGNATURE=" + Me.Signature + _ params params = _ "METHOD=" + method + "&" + _ "VERSION=" + ApiVersion + "&" + _ GetEncodedParams_(params) ' Setup web request. Dim req As HttpWebRequest = CType(WebRequest.Create(serverUrl), HttpWebRequest) req.Method = "POST" req.ContentType = "application/x-www-form-urlencoded" Using reqStream As Stream = req.GetRequestStream() Dim reqBytes() As Byte = Encoding.UTF8.GetBytes(params) Call reqStream.Write(reqBytes, 0, reqBytes.Length) End Using ' Get result. Dim resResult As String = String.Empty Dim res As WebResponse = req.GetResponse() Using resStream As Stream = res.GetResponseStream() resResult = New StreamReader(resStream).ReadToEnd() End Using ' Process result. result = GetDictionaryFromNVP_(resResult) If CStr(result("ACK")) <> "Success" Then ' Method call failed. Report error. Throw New Exception(GetErrorFromNVP_(result)) End If QueryPayPal_ = result End Function ' QueryPayPal_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetDictionaryFromNVP_(ByVal listStr As String) As Hashtable Dim result As Hashtable = New Hashtable() Dim list() As String = HttpUtility.UrlDecode(listStr).Split( _ New String() {"&"}, _ StringSplitOptions.RemoveEmptyEntries) For Each item As String In list Dim sepIndex As Integer = item.IndexOf("=") Dim name As String = item.Substring(0, sepIndex) Dim value As String = item.Substring(sepIndex + 1) result(name) = value Next GetDictionaryFromNVP_ = result End Function ' GetDictionaryFromNVP_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Escape special characters in parameters. Private Function GetEncodedParams_(ByVal params As String) As String Dim result As String = String.Empty Dim paramsList As Hashtable = GetDictionaryFromNVP_(params) For Each param As DictionaryEntry In paramsList If Not String.IsNullOrEmpty(result) Then result += "&" End If result += String.Format( _ "{0}={1}", _ param.Key, _ HttpUtility.UrlEncode(CStr(param.Value))) Next GetEncodedParams_ = result End Function ' GetEncodedParams_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetDateString_(ByVal dateStr As String, ByVal isVar As Boolean) As String Dim result As String = String.Empty If Not String.IsNullOrEmpty(dateStr) Then If isVar Then dateStr = GetVariable_(dateStr).ToString() End If result = Convert.ToDateTime(dateStr).ToString("yyyy-MM-dd") + "T00:00:00Z" End If GetDateString_ = result End Function ' GetDateString_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetErrorFromNVP_(ByVal resList As Hashtable) As String Dim result As String = String.Empty Dim index As Integer = 0 While resList.Contains(String.Format(ErrorCode_1P, index)) If index > 0 Then result += "\r\n" End If result += String.Format( _ "Error code: {0}, Message: {1}", _ resList(String.Format(ErrorCode_1P, index)), _ resList(String.Format(LongMessage_1P, index))) index += 1 End While GetErrorFromNVP_ = result End Function ' GetErrorFromNVP_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of names combined into a string. Private Function GetNVPList_(ByVal resList As Hashtable, ByVal name As String) As String Dim result As String = String.Empty Dim index As Integer = 0 While resList.Contains(String.Format(name, index)) If index > 0 Then result += "|" End If result += CStr(resList(String.Format(name, index))) index += 1 End While GetNVPList_ = result End Function ' GetNVPList_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetVariable_(ByVal varName As String) As Object Dim result As Object Dim vars As IDTSVariables90 = Nothing Call Me.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ #End Region ' Internals #Region "Attributes" Private m_serverType As ServerTypes Private m_user As String Private m_pwd As String Private m_signature As String Private m_getDetails As Boolean Private m_isStartDateVar As Boolean Private m_startDate As String Private m_isEndDateVar As Boolean Private m_endDate As String Private m_trxClass As TransactionClassTypes Private m_status As StatusTypes Private m_other As String = String.Empty #End Region ' Attributes End Class ' ScriptMain ]]> ScriptComponent_a47149323d3c46b7b82edea048c6dbe8 true CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea