Option Explicit

Function questionmarks(size) As String
    Dim qarray() As String
    ReDim qarray(1 To size)
    Dim i As Integer
    For i = 1 To size
        qarray(i) = "?"
    Next i
    questionmarks = Join(qarray, ",")
End Function

Sub getdata()
    'cusipRange = Range(ActiveCell, ActiveCell.End(xlDown))
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset

    Dim Notional As Variant
    Dim DisableReinv As Variant
    Dim Cusip As Object

    Notional = Selection.Offset(0, 4).Value
    DisableReinv = Selection.Offset(0, 7).Value
    Set cn = DBConn()

    Application.EnableEvents = False

    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For Each Cusip In Selection
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 13, Trim(Cusip))
        Next Cusip
        .CommandText = "SELECT * FROM et_cusip_details(" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    ActiveCell.Offset(0, 2).CopyFromRecordset rs
    rs.Close
    cn.Close
    Selection.Offset(0, 4).Value = Notional
    Selection.Offset(0, 7).Value = DisableReinv
    Application.EnableEvents = True
End Sub
Sub savecolor()
    'cusipRange = Range(ActiveCell, ActiveCell.End(xlDown))
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim Cusip As Object
    Set cn = DBConn()

    Application.EnableEvents = False

    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("ListDate", adDate, adParamInput)
        .Parameters.Append .CreateParameter("ListInfo", adVarChar, adParamInput, 20)
        .Parameters.Append .CreateParameter("Cusip", adChar, adParamInput, 9)
        .Parameters.Append .CreateParameter("Notional", adDouble, adParamInput)
        .Parameters.Append .CreateParameter("Indications", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("Cover", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("ListColor", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("Bid", adVarChar, adParamInput, 100)
        .Parameters.Append .CreateParameter("BidNote", adVarChar, adParamInput, 100)
        For Each Cusip In Selection
            .Parameters("ListDate") = Cusip.Offset(0, -2)
            .Parameters("ListInfo") = Cusip.Offset(0, -1)
            .Parameters("Cusip") = Trim(Cusip)
            .Parameters("Notional") = Cusip.Offset(0, 4)
            .Parameters("Indications") = Cusip.Offset(0, 31)
            .Parameters("Cover") = Cusip.Offset(0, 32)
            .Parameters("ListColor") = Cusip.Offset(0, 33)
            .Parameters("Bid") = Cusip.Offset(0, 29)
            .Parameters("BidNote") = Cusip.Offset(0, 30)
            .CommandText = "INSERT INTO color VALUES(" & questionmarks(cmd.Parameters.Count) & ")"
            .Execute
        Next Cusip
    End With
    cn.Close
    Application.EnableEvents = True
End Sub
Sub getcolor()
    'cusipRange = Range(ActiveCell, ActiveCell.End(xlDown))
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset

    Dim Cusip As Object

    Set cn = DBConn()

    Application.EnableEvents = False

    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("Cusip", adChar, adParamInput, 9)
        For Each Cusip In Selection
            .Parameters("Cusip") = Trim(Cusip)
            .CommandText = "select max(listdate), string_agg(listinfo, ',') as listinfo, string_agg(bid,',') as bid," & _
                            "string_agg(bid_note,',') as bid_note, sum(notional) as notional," & _
                            "string_agg(indications,',') as indications, string_agg(cover,',') as cover, " & _
                            "string_agg(listcolor,',') as listcolor from latest_color where cusip=?"
            Set rs = cmd.Execute
            Cusip.Offset(0, 37).CopyFromRecordset rs
        Next Cusip
    End With
    rs.Close
    cn.Close
    Application.EnableEvents = True
End Sub

Sub getintexdealnames()
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset
    Dim Clip As New DataObject

    Dim firsttime As Boolean
    Set cn = DBConn()
    Dim dealnameArray As Variant
    Dim cliptext As String
    Dim Cusip As Object
    Dim i As Integer

    Application.EnableEvents = False
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For Each Cusip In Selection
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 10, Trim(Cusip))
        Next Cusip
        .CommandText = "SELECT * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    dealnameArray = rs.GetRows()

    For i = 0 To Selection.Count - 1
        cliptext = cliptext & dealnameArray(0, i) & vbNewLine
    Next
    Clip.SetText cliptext
    Clip.PutInClipboard
    rs.Close
    cn.Close
    Application.EnableEvents = True
End Sub

Sub getallcusips()
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset
    Dim Clip As New DataObject

    Set cn = DBConn()
    Dim cusipArray, dealnameArray As Variant
    Dim cliptext As String
    Dim Cusip As Object
    Dim i As Integer

    Application.EnableEvents = False
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For Each Cusip In Selection
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 10, Trim(Cusip))
        Next Cusip
        .CommandText = "SELECT * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    dealnameArray = rs.GetRows()

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For i = 0 To Selection.Count - 1
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 10, dealnameArray(0, i))
        Next i
        .CommandText = "SELECT DISTINCT cusip FROM latest_cusip_universe where dealname IN (" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    cusipArray = rs.GetRows()

    For i = 0 To UBound(cusipArray, 2)
        cliptext = cliptext & cusipArray(0, i) & vbNewLine
    Next
    Clip.SetText cliptext
    Clip.PutInClipboard
    rs.Close
    cn.Close
    Application.EnableEvents = True

End Sub

Sub savedeals_to_price()

    'Just showing where the input data are
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Set cn = DBConn()
    Dim cusipArray, dealnameArray, dealnameArrayuniq As Variant
    Dim sqlstr, Data As String
    Dim dealname As Variant
    Dim i As Integer
    Dim key As Variant

    Dim filename As String
    Dim fh As Integer
    Dim Cusip As Object
    Dim noreinvindex As New Scripting.Dictionary
    Dim noreinvdeal As New Scripting.Dictionary
    Application.EnableEvents = False

    filename = "\\WDSENTINEL\share\CorpCDOs\scripts\deals_to_price.txt"

    'Get an unused file number
    fh = FreeFile
    'Create a new file (or overwrite an existing one)
    Open filename For Output As fh

    key = 0
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For Each Cusip In Selection
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 10, Trim(Cusip))
            If Cusip.Offset(0, 7) = "Y" Then
                noreinvindex.Add key, 1
            End If
            key = key + 1
        Next Cusip
        .CommandText = "SELECT * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    dealnameArray = rs.GetRows()

    For Each key In noreinvindex.Keys()
        noreinvdeal.Add dealnameArray(0, key), 1
    Next

    cmd.CommandText = "SELECT distinct * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    Set rs = cmd.Execute
    dealnameArrayuniq = rs.GetRows()
    For Each dealname In dealnameArrayuniq
        If Not IsNull(dealname) Then
            If Not noreinvdeal.Exists(dealname) Then
                sqlstr = "Select ""Reinv End Date"" from latest_clo_universe where dealname = '" & dealname & "'"
                Set rs = cn.Execute(sqlstr)
                Data = rs.GetString()
                If Len(Data) > 1 Then
                    Print #fh, dealname & vbTab & "TRUE"
                Else
                    Print #fh, dealname & vbTab & "FALSE"
                End If
            Else
                Print #fh, dealname & vbTab & "FALSE"
            End If
        End If
    Next dealname

    'Close the file
    Close #fh
End Sub

Sub generate_intex_portfolio()
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset
    Dim Clip As New DataObject

    Set cn = DBConn()
    Dim cusipArray, dealnameArray, dealnameArrayuniq As Variant
    Dim cliptext As String
    Dim Cusip As Object
    Dim sqlstr, Data As String
    Dim dealname As Variant
    Dim i As Integer
    Dim key As Variant

    Dim noreinvindex As New Scripting.Dictionary
    Dim noreinvdeal As New Scripting.Dictionary
    Application.EnableEvents = False

    key = 0
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        For Each Cusip In Selection
            .Parameters.Append .CreateParameter(, adChar, adParamInput, 10, Trim(Cusip))
            If Cusip.Offset(0, 7) = "Y" Then
                noreinvindex.Add key, 1
            End If
            key = key + 1
        Next Cusip
        .CommandText = "SELECT * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    End With

    Set rs = cmd.Execute
    dealnameArray = rs.GetRows()

    For Each key In noreinvindex.Keys()
        noreinvdeal.Add dealnameArray(0, key), 1
    Next

    cmd.CommandText = "SELECT distinct * FROM dealname_from_cusip(" & questionmarks(Selection.Count) & ")"
    Set rs = cmd.Execute
    dealnameArrayuniq = rs.GetRows()
    For Each dealname In dealnameArrayuniq
        If Not IsNull(dealname) Then
            If Not noreinvdeal.Exists(dealname) Then
                sqlstr = "Select ""Reinv End Date"" from latest_clo_universe where dealname = '" & dealname & "'"
                Set rs = cn.Execute(sqlstr)
                Data = rs.GetString()
                If Len(Data) > 1 Then
                    cliptext = cliptext & UCase(dealname) & "," & "COLLAT_INITIAL" & vbTab
                    cliptext = cliptext & dealname & ".sss" & vbNewLine
                    cliptext = cliptext & UCase(dealname) & "," & "COLLAT_REINVEST" & vbTab
                    cliptext = cliptext & dealname & ".sss" & vbNewLine
                Else
                    cliptext = cliptext & UCase(dealname) & "," & "COLLAT" & vbTab
                    cliptext = cliptext & dealname & ".sss" & vbNewLine
                End If
            Else
                cliptext = cliptext & UCase(dealname) & "," & "COLLAT" & vbTab
                cliptext = cliptext & dealname & ".sss" & vbNewLine
            End If
        End If
    Next dealname

    i = 1
    For Each Cusip In Selection
        If Not IsNull(dealnameArray(0, i - 1)) Then
            cliptext = cliptext & Cusip & vbTab & dealnameArray(0, i - 1) & ".sss" & vbNewLine
        End If
        i = i + 1
    Next Cusip

    Clip.SetText cliptext
    Clip.PutInClipboard
    rs.Close
    cn.Close
    Application.EnableEvents = True

End Sub