Listing 2

Imports Data.Shared.Shared
Imports Microsoft.Office.Interop.Excel

Public Class Utility
    Public Function GetColumnLetter(ColumnNumber As Integer)
        Dim result = String.Empty
        If (ColumnNumber < 27)="" then="" result="Chr(ColumnNumber" +="" 64)="" else="" dim="" cmultiple="CInt(ColumnNumber" 26)="" dim="" csingle="CInt(ColumnNumber" -="" (cmultiple="" *="" 26))="" result="Chr(CMultiple" +="" 64)="" &="" chr(csingle="" +="" 64)="" end="" if="" return="" result="" end="" function="" public="" shared="" function="" updateaccountingperiods(chinookchecked="" as="" boolean,="" northwindchecked="" as="" boolean)="" as="" list(of="" string)="" dim="" apchinook="" as="" new="" list(of="" string)="" dim="" apnorthwind="" as="" new="" list(of="" string)="" if="" chinookchecked="" then="" apchinook="Data.Chinook.Data.AccountingPeriods()" if="" northwindchecked="" then="" apnorthwind="Data.Northwind.Data.AccountingPeriods()" dim="" result="CombinedAccountingPeriods(apChinook," apnorthwind)="" return="" result="" end="" function="" public="" sub="" addworksheet(name="" as="" string,="" afteractive="" as="" boolean)="" 'add="" a="" new="" worksheet="" to="" the="" left="" or="" right="" of="" active="" sheet,="" with="" specified="" name="" if="" afteractive="" then="" 'add="" the="" new="" sheet="" after="" this="" sheet="" dim="" result="Globals.ThisWorkbook.Worksheets.Add(After:=Globals.ThisWorkbook.ActiveSheet)" result.name="Name" else="" dim="" result="Globals.ThisWorkbook.Worksheets.Add()" result.name="Name" end="" if="" end="" sub="" public="" sub="" addchinookinvoicesforperiod(worksheetname="" as="" string,="" accountingperiod="" as="" string)="" 'exit="" if="" the="" worksheet="" cannot="" be="" found="" dim="" index="" as="" integer="WorkSheetIndex(WorkSheetName)" if="" index="">< 1="" then="" messagebox.show("utility.chinookinvoices="" :="" invalid="" worksheet="" name:="" "="" +="" worksheetname)="" return="" end="" if="" dim="" chinookinvoices="Data.Chinook.Data.InvoicesForPeriod(AccountingPeriod)" globals.thisworkbook.sheets(index).select()="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.Worksheets(index)," excel.worksheet)="" dim="" rownumber="1" ws.cells(rownumber,="" 1)="Chinook Invoice Details for Accounting Period: " +="" accountingperiod="" rownumber="" +="2" ws.cells(rownumber,="" 1)="Invoice #" ws.cells(rownumber,="" 2)="Invoice Date" ws.cells(rownumber,="" 3)="Customer" ws.cells(rownumber,="" 4)="Invoice Total" ws.cells(rownumber,="" 5)="Line #" ws.cells(rownumber,="" 6)="Track" ws.cells(rownumber,="" 7)="Unit" ws.cells(rownumber,="" 8)="Quantity" ws.cells(rownumber,="" 9)="Line Total" rownumber="" +="2" for="" invoice="0" to="" chinookinvoices.count="" -="" 1="" dim="" cinvoice="ChinookInvoices(Invoice)" 'invoice="" fields:="" a:invoiceid,="" b:invoicedate,="" c:customername,="" d:total="" ws.cells(rownumber,="" 1)="cInvoice.InvoiceId" ws.cells(rownumber,="" 2)="cInvoice.InvoiceDate.ToShortDateString()" ws.cells(rownumber,="" 3)="cInvoice.Customer.LastName" +="" ",="" "="" +="" chinookinvoices(invoice).customer.firstname="" ws.cells(rownumber,="" 4)="cInvoice.Total" rownumber="" +="1" for="" invoiceline="0" to="" cinvoice.invoicelines.count="" -="" 1="" 'e:invoicelineid,="" f:track="" name,="" g:="" unitprice,="" h:="" quanity,="" i:="" total="" dim="" clinvoice="cInvoice.InvoiceLines(InvoiceLine)" ws.cells(rownumber,="" 5)="clInvoice.InvoiceLineId" ws.cells(rownumber,="" 6)="clInvoice.Track.Name" ws.cells(rownumber,="" 7)="clInvoice.UnitPrice" ws.cells(rownumber,="" 8)="clInvoice.Quantity" ws.cells(rownumber,="" 9)="clInvoice.UnitPrice" *="" clinvoice.quantity="" rownumber="" +="1" next="" invoiceline="" rownumber="" +="2" next="" invoice="" return="" end="" sub="" 'helper="" function="" public="" function="" worksheetindex(worksheetname="" as="" string)="" as="" integer="" 'linq="" not="" available="" on="" the="" sheets="" collection="" dim="" result="0" dim="" matchname="WorkSheetName.ToLower" for="" index="1" to="" globals.thisworkbook.sheets.count="" dim="" worksheetnamelc="" as="" string="Globals.ThisWorkbook.Sheets(index).Name.ToString.ToLower" if="" (worksheetnamelc="MatchName)" then="" result="index" end="" if="" next="" return="" result="" end="" function="" public="" function="" findfirstoccurancerow(column="" as="" integer,="" searchtext="" as="" string)="" as="" integer="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" firstrow="-1" dim="" locatedcell="ws.Cells.Find(" searchtext,="" ctype(ws.cells(1,="" column),="" excel.range),="" xlfindlookin.xlvalues,="" xllookat.xlwhole,="" xlsearchorder.xlbyrows,="" xlsearchdirection.xlnext,="" false,="" false)="" dim="" result="LocatedCell.Row" return="" result="" end="" function="" public="" sub="" setrowbold(row="" as="" integer)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" srcerange="ws.Range(" a""="" +="" row.tostring,="" "a"="" +="" row.tostring)="" srcerange.entirerow.font.bold="True" end="" sub="" public="" sub="" hidecolumn(column="" as="" integer)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" columnletter="GetColumnLetter(column)" dim="" srcerange="ws.Range(ColumnLetter" +="" "1",="" columnletter="" +="" "1")="" srcerange.entirecolumn.hidden="True" end="" sub="" public="" sub="" writevaluetosheet(searchtext="" as="" string,="" column="" as="" integer,="" value="" as="" string)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" row="FindFirstOccuranceRow(1," searchtext)="" if="" row=""> 0 Then ws.Cells(row, Column) = Value
        Return
    End Sub

    Public Sub WriteValueToSheet(Row As Integer, Column As Integer, Value As String)
        If Row > 0 Then
            Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)
            ws.Cells(Row, Column) = Value
        End If
        Return
    End Sub

    Public Structure WorksheetDimensions
        Dim LastRow As Integer
        Dim LastColumn As Integer
    End Structure

    Public Function GetActiveWorksheetDimensions() As WorksheetDimensions
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)
        Dim result As New WorksheetDimensions

        result.LastRow = ws.Cells.Find(
            "*",
            CType(ws.Cells(1, 1), Excel.Range),
            XlFindLookIn.xlValues,
            XlLookAt.xlWhole,
            XlSearchOrder.xlByRows,
            XlSearchDirection.xlPrevious,
            False,
            False).Row

        result.LastColumn = ws.Cells.Find(
            "*",
            CType(ws.Cells(1, 1), Excel.Range),
            XlFindLookIn.xlValues,
            XlLookAt.xlWhole,
            XlSearchOrder.xlByColumns,
            XlSearchDirection.xlPrevious,
            False,
            False).Column

        Return result
    End Function

    Public Class ConsolidatedRow
        Public Property Company As String
        Public Property ItemNumber As Integer
        Public Property ItemDate As DateTime
        Public Property OrderInvoiceID As Integer
        Public Property CustomerID As String
        Public Property CustomerName As String
        Public Property Amount As Decimal
        Public Property SalesToDate As Decimal
        Public Property LargestSale As Boolean
    End Class

    Public Function ConsolidateDetail(AccountingPeriod As String) As List(Of ConsolidatedRow)
        Dim result = New List(Of ConsolidatedRow)

        Dim LargestChinookInvoiceID = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim ChinookCustomerSalesToDate = Data.Chinook.Data.SalesToPeriodByCustomer(AccountingPeriod)
        Dim ChinookSalesForPeriod = Data.Chinook.Data.InvoicesForPeriod(AccountingPeriod)
        Dim ChinookLargestSale = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)

        Dim LargestNorthwindOrderID = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim NorthWindCustomerSalesToDate = Data.Northwind.Data.SalesToPeriodByCustomer(AccountingPeriod)
        Dim NorthwindSalesForPeriod = Data.Northwind.Data.OrdersForPeriod(AccountingPeriod)
        Dim NorthwindLargestSale = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)

        Dim ChinookLarger = ChinookLargestSale >= NorthwindLargestSale
        Dim NorthwindLarger = NorthwindLargestSale >= ChinookLargestSale

        For c = 0 To ChinookSalesForPeriod.Count - 1
            Dim cr As New ConsolidatedRow
            cr.Company = "Chinook"
            cr.ItemDate = ChinookSalesForPeriod(c).InvoiceDate
            cr.OrderInvoiceID = ChinookSalesForPeriod(c).InvoiceId
            cr.CustomerID = ChinookSalesForPeriod(c).CustomerId
            cr.CustomerName = ChinookSalesForPeriod(c).Customer.LastName + ", " + ChinookSalesForPeriod(c).Customer.FirstName
            cr.Amount = ChinookSalesForPeriod(c).Total
            If ChinookLarger AndAlso cr.OrderInvoiceID = ChinookLargestSale Then cr.LargestSale = True
            result.Add(cr)
        Next

        For n = 0 To NorthwindSalesForPeriod.Count - 1
            Dim cr As New ConsolidatedRow
            cr.Company = "Northwind"
            cr.ItemDate = NorthwindSalesForPeriod(n).OrderDate
            cr.OrderInvoiceID = NorthwindSalesForPeriod(n).OrderID
            cr.CustomerID = NorthwindSalesForPeriod(n).CustomerID
            cr.CustomerName = NorthwindSalesForPeriod(n).Customer.CompanyName
            cr.Amount = Data.Northwind.Data.GetTotalForOrder(cr.OrderInvoiceID)
            If NorthwindLarger AndAlso cr.OrderInvoiceID = NorthwindLargestSale Then cr.LargestSale = True
            result.Add(cr)
        Next

        result = (From item In result Order By item.ItemDate).ToList

        For i = 0 To result.Count - 1
            Dim cr = result(i)
            cr.ItemNumber = i + 1
            If cr.Company = "Chinook" Then
                If ChinookCustomerSalesToDate.ContainsKey(cr.CustomerID) Then
                    cr.SalesToDate = ChinookCustomerSalesToDate(cr.CustomerID) + cr.Amount
                    ChinookCustomerSalesToDate(cr.CustomerID) = cr.SalesToDate
                Else
                    ChinookCustomerSalesToDate.Add(cr.CustomerID, cr.Amount)
                End If
            Else
                If NorthWindCustomerSalesToDate.ContainsKey(cr.CustomerID) Then
                    cr.SalesToDate = NorthWindCustomerSalesToDate(cr.CustomerID) + cr.Amount
                    NorthWindCustomerSalesToDate(cr.CustomerID) = cr.SalesToDate
                Else
                    NorthWindCustomerSalesToDate.Add(cr.CustomerID, cr.Amount)
                End If
            End If
        Next
        Return result
    End Function

    'Copy an entire row to a location, optionally inserting a new row at the destination
    Public Sub CopyRowActiveSheet(SourceRowNumber As Integer, DestinationRowNumber As Integer, InsertCopiedRow As Boolean)
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)

        Dim wsDimensions As WorksheetDimensions = GetActiveWorksheetDimensions()
        Dim LastColumnLetter = GetColumnLetter(wsDimensions.LastColumn)

        Dim SrceRange = ws.Range("A" + SourceRowNumber.ToString, LastColumnLetter + SourceRowNumber.ToString)
        Dim DestRange = ws.Range("A" + DestinationRowNumber.ToString, LastColumnLetter + DestinationRowNumber.ToString)

        If InsertCopiedRow Then
            DestRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown)
        End If

        DestRange = ws.Range("A" + DestinationRowNumber.ToString(), LastColumnLetter + DestinationRowNumber.ToString())
        DestRange.Value = SrceRange.Value
    End Sub

    'Copy an entire row to a location, optionally inserting a new row at the destination
    Public Sub InsertRowActiveSheet(RowNumber As Integer)
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)

        Dim wsDimensions As WorksheetDimensions = GetActiveWorksheetDimensions()
        Dim LastColumnLetter = GetColumnLetter(wsDimensions.LastColumn)

        Dim DestRange = ws.Range("A" + RowNumber.ToString, LastColumnLetter + RowNumber.ToString)
        DestRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown)
    End Sub
End Class