Visual Basic

<pre>    Private Sub Gera2d()

        Try
            data = DateAndTime.Now
            Dim con As New SqlConnection
            Dim sql_query As New SqlCommand
            '
            Dim sql_result As SqlDataReader
            '
            con.ConnectionString = "server='" & SqlServer & "';Database=etiquetasKLC;Uid='" & SqlUser & "';PWD='" & Sqlpassword & "';"
            sql_query.Connection = con
            con.Open()
            sql_query.Parameters.Add("@posto", SqlDbType.NVarChar).Value = posto
            sql_query.Parameters.Add("@data", SqlDbType.NVarChar).Value = data
            sql_query.Parameters.Add("@lote", SqlDbType.NVarChar).Value = ordemFabrico
            sql_query.Parameters.Add("@versao", SqlDbType.NVarChar).Value = status
            sql_query.Parameters.Add("@indexnumber", SqlDbType.NVarChar).Value = cavidade + indexnumber
            sql_query.Parameters.Add("@semana", SqlDbType.NVarChar).Value = weekyear
            sql_query.Parameters.Add("@qrgerado", SqlDbType.NVarChar).Value = codigoQr
            sql_query.Parameters.Add("@operador", SqlDbType.NVarChar).Value = op
            sql_query.Parameters.Add("@partnumber", SqlDbType.NVarChar).Value = partnumber
            sql_query.Parameters.Add("posx", SqlDbType.NVarChar).Value = labelx
            sql_query.Parameters.Add("posy", SqlDbType.NVarChar).Value = labely
            sql_query.Parameters.Add("ip_impressora", SqlDbType.NVarChar).Value = enderecoimpressora
            sql_query.CommandText = "INSERT INTO etiquetas with (tablockx)(nome_posto, data, lote, serialnumber,partnumber,versao,indexnumber,semana, qrgerado,operador,snpn,posx,posy,impressora) select top (1) @posto,
            @data,@lote,T.x,@partnumber,@versao, @indexnumber,@semana, T.x+@partnumber+@versao+@indexnumber+@semana, @operador, T.x+@partnumber, @posx,@posy,@ip_impressora
            FROM [etiquetas] x CROSS APPLY (VALUES(format(convert(int,x.serialnumber)+1,'0000000')))T(x) where partnumber like @partnumber  order by id desc ;"

            sql_query.Parameters.AddWithValue("@sn", serialnumber)

            serialnumber = sql_query.ExecuteScalar()

            Try

                If sql_query.ExecuteScalar() <> 0 Then
                    serialnumber = serialnumber
                End If

            Catch ex As Exception
                IO.File.AppendAllText("C:\ti\Log.txt", String.Format("{0}{1}", Environment.NewLine, Now() + " - " + ex.ToString()))

            End Try
            con.Close()
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try
    End Sub

Private Sub LoadChart()

        Dim oDT As New DataTable
        Dim sqlCmd As New SqlCommand

        Dim sSQLDateCTE As String = "WITH Date_CTE as ( " & _
                "SELECT {0} DDim.[Date] as SaleDate " & _
                "FROM [DateDimension] as DDim {1} "
        Dim sWHEREDateCTE As String = "WHERE [Date] BETWEEN DATEADD({0},-{1},GETDATE()) AND GETDATE() {2} "
        Dim sSQLSUM As String = "), SUM_CTE as ( " & _
                "SELECT {0}" & _
                "FROM {1} " & _
                "WHERE F1034 = 3 AND F64 <> 0 AND F01 = @UPCCode " & _
                "AND F254 IN (SELECT saledate FROM Date_CTE) " & _
                "GROUP BY {2}) "
        Dim sSQLFinalPart = "SELECT {0} DC.SaleDate ,ISNULL(SC.UnitsSold,0) as UnitsSold " & _
                "FROM Date_CTE as DC " & _
                "LEFT OUTER JOIN SUM_CTE as SC " & _
                "ON SC.SaleDate = DC.SaleDate {1}" & _
                "ORDER BY DC.SaleDate ASC "


        Dim sFieldList As String = ""
        Dim sGroupBy As String = ""
        Dim sStorelist As String = ""

        Dim sWhereIN As String = ""
        Dim sWhereINDateCTE As String = ""

        Dim sSQL As String = ""

        Try

            If oAppEnv.sSource = "H" Then
                For Each CheckedItem In clbStores.CheckedItems
                    If sStorelist <> "" Then
                        sStorelist = sStorelist & ", "
                    End If
                    sStorelist = sStorelist & CheckedItem.key
                Next
            End If

            If sStorelist <> "" Then
                sFieldList = "F01 as UPCCode, F1056 as Store, F254 as SaleDate, SUM(F64) as UnitsSold "
                sGroupBy = "F01,F1056,F254"
                sSQLDateCTE = String.Format(sSQLDateCTE, "SL.Store, ", "CROSS JOIN Newbuck.dbo.tblStoreList as SL ")
                sWhereIN = "AND {0} in ({1}) "
                sWhereINDateCTE = String.Format(sWhereIN, "SL.Store", sStorelist)
                sSQLFinalPart = String.Format(sSQLFinalPart, "DC.Store, ", "AND SC.Store = DC.Store ")
            Else
                sFieldList = "F01 as UPCCode, F254 as SaleDate, SUM(F64) as UnitsSold "
                sGroupBy = "F01,F254"
                sSQLDateCTE = String.Format(sSQLDateCTE, "", "")
                sWhereINDateCTE = ""
                sSQLFinalPart = String.Format(sSQLFinalPart, "", "")

            End If


            Dim PeriodRow As DataRow = oAppEnv.dtPeriods.Select(String.Format("DPPeriodID = {0}", cboPeriods.SelectedValue)).FirstOrDefault

            Dim DurationRow As DataRow = oAppEnv.dtDurations.Select(String.Format("DDDurationID = '{0}'", cboDuration.SelectedValue)).FirstOrDefault

            Select Case PeriodRow.Item("DPPeriodAbbrev")
                Case "D"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "DD", DurationRow.Item("DDDurationLength"), sWhereINDateCTE)
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_D", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "W"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "WW", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND Weekday = 1 "
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_W", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "M"
                    sWHEREDateCTE = String.Format(sWHEREDateCTE, "MM", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND [DAY] = DATEPART(Day,getdate()) "
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_M", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
                Case "Y"
                    sWHEREDateCTE = String.Format("WHERE [Date] BETWEEN '01-01-2009' AND GETDATE() AND DayOfYear = 1 {0} ", sWhereINDateCTE)
                    sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_Y", sGroupBy)
                    sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
            End Select

            sqlCmd.CommandText = sSQL

            sqlCmd.Parameters.AddWithValue("@UPCCode", sUPCCode)

            oDT = oAppEnv.oLogiDM.GetSQLData(sqlCmd)

            ctSalesData.ChartAreas.Clear()
            ctSalesData.ChartAreas.Add("Sales")
            ctSalesData.Series.Clear()

            If oDT IsNot Nothing Then

                '//Get the Date range.

                Dim oSortDV As DataView = oDT.DefaultView
                oSortDV.Sort = "UnitsSold DESC"

                'ctSalesData.DataSource = oDT
                Dim iCnt As Integer = 0

                ctSalesData.AlignDataPointsByAxisLabel()

                If oAppEnv.sSource = "H" And sStorelist <> "" Then
                    For Each CheckedItem In clbStores.CheckedItems
                        Dim oStoreRows() As DataRow = oDT.Select("Store = " & CheckedItem.key)
                        iCnt = 0
                        Dim cSeries As New Charting.Series(CheckedItem.key)
                        cSeries.ChartType = Charting.SeriesChartType.Bar
                        cSeries.IsXValueIndexed = True
                        cSeries.CustomProperties = "PixelPointWidth = 20"
                        cSeries.IsValueShownAsLabel = True
                        For Each row In oStoreRows
                            cSeries.Points.AddXY(iCnt, row.Item("UnitsSold"))
                            cSeries.Points(iCnt).AxisLabel = row.Item("SaleDate")
                            iCnt += 1
                        Next
                        ctSalesData.Series.Add(cSeries)
                    Next
                Else
                    Dim cSeries As New Charting.Series("Sales")
                    cSeries.ChartType = Charting.SeriesChartType.Bar
                    cSeries.IsXValueIndexed = True
                    cSeries.CustomProperties = "PixelPointWidth = 15"
                    cSeries.IsValueShownAsLabel = True

                    For Each row In oDT.Rows
                        cSeries.Points.AddXY(iCnt, row.item("UnitsSold"))
                        cSeries.Points(iCnt).AxisLabel = row.item("SaleDate")
                        iCnt += 1
                    Next
                    ctSalesData.Series.Add(cSeries)
                End If

                ctSalesData.ChartAreas(0).AxisX.Minimum = 0
                ctSalesData.ChartAreas(0).AxisX.Maximum = oDT.Rows.Count + 1

                If oDT.Rows.Count > 21 Then
                    Dim ZoomDate As Date = oDT.Rows(14).Item("SaleDate")
                    ctSalesData.ChartAreas(0).AxisX.ScaleView.Zoom(0, 22)
                End If

                ctSalesData.ChartAreas(0).AxisX.Interval = 1
                ctSalesData.ChartAreas(0).AxisY.Minimum = 0
                ctSalesData.ChartAreas(0).AxisY.Interval = 1

                ctSalesData.ChartAreas(0).AxisY.Maximum = Math.Ceiling(oSortDV(0).Item("UnitsSold") / 10) * 10

                If ctSalesData.ChartAreas(0).AxisY.Maximum = oSortDV(0).Item("UnitsSold") Then
                    ctSalesData.ChartAreas(0).AxisY.Maximum += 10
                End If

                ctSalesData.ChartAreas(0).CursorX.AutoScroll = True

                ctSalesData.ChartAreas(0).AxisX.ScaleView.SizeType = Charting.DateTimeIntervalType.Number
                ctSalesData.ChartAreas(0).AxisX.ScrollBar.ButtonStyle = Charting.ScrollBarButtonStyles.SmallScroll

                ctSalesData.ChartAreas(0).AxisX.ScaleView.SmallScrollSize = 21
                ctSalesData.ChartAreas(0).AxisX.ScrollBar.IsPositionedInside = False


            End If


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Sub

Shared ClientSocket As System.Net.Sockets.TcpClient
Shared ClientStream As System.Net.Sockets.NetworkStream

ClientStream.Write(TxBuffer, 0, bytesToTx)
ClientStream.Read(RxBuffer, bytesRxed, RxBuffer.Length - bytesRxed)

'For x = 0 To ds.Tables.Count - 1
'    Dim tp As New TabPage("TabPage" & x)
'    TabControl1.TabPages.Add(tp)
'    DataGridView(x) = New DataGridView
'    'Dim tabPagex As System.Windows.Forms.TabPage
'    tp.Controls.Add(DataGridView(x))
'    DataGridView(x).DataSource = ds.Tables(x)
'    DataGridView(x).Dock = DockStyle.Fill
'Next

Dim xlApp, xlBook, xlSheet As Object
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Worksheets()
        xlApp.Visible = True
        xlSheet = xlBook.Sheets("sheet1")
        Dim Cols As Integer
        For Cols = 1 To DataGridView1.Columns.Count
            xlApp.Cells(1, Cols) = DataGridView1.Columns(Cols - 1).HeaderText
        Next
        Dim i As Integer
        For i = 0 To DataGridView1.RowCount - 1
            Dim j As Integer
            For j = 0 To DataGridView1.ColumnCount - 1
                If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then
                    xlApp.Cells(i = 2, j = 1) = ""
                Else
                    xlApp.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString
                End If
            Next
        Next
        xlApp.Sheets.Add(xlApp.Sheets("sheet1"))
        xlApp.Sheets("sheet2").Select()
        For Cols = 1 To DataGridView2.Columns.Count
            xlApp.Cells(1, Cols) = DataGridView2.Columns(Cols - 1).HeaderText
        Next
        For i = 0 To DataGridView2.RowCount - 1
            Dim j As Integer
            For j = 0 To DataGridView2.ColumnCount - 1
                If Me.DataGridView2(j, i).Value Is System.DBNull.Value Then
                    xlApp.Cells(i = 2, j = 1) = ""
                Else
                    xlApp.Cells(i + 2, j + 1) = DataGridView2(j, i).Value.ToString
                End If
            Next
        Next
        xlApp.Sheets.Add(xlApp.Sheets("sheet2"))
        xlApp.Sheets("sheet3").Select()
        For Cols = 1 To DataGridView3.Columns.Count
            xlApp.Cells(1, Cols) = DataGridView3.Columns(Cols - 1).HeaderText
        Next
        For i = 0 To DataGridView3.RowCount - 1
            Dim j As Integer
            For j = 0 To DataGridView3.ColumnCount - 1
                If Me.DataGridView3(j, i).Value Is System.DBNull.Value Then
                    xlApp.Cells(i = 2, j = 1) = ""
                Else
                    xlApp.Cells(i + 2, j + 1) = DataGridView3(j, i).Value.ToString
                End If
            Next
        Next