Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel
Public Class kittingForm
    Private cnstr = mainForm.cnstr
    Private conn As New SqlConnection(cnstr)
    Private myQuery As String = String.Empty
    Private myCommand As SqlCommand
    Private adapter As SqlDataAdapter = New SqlDataAdapter()
    Private ds As New DataSet("MachineSequence")
    Private i, limit As Integer
    Private sequence As New Sequence()
    Private kitted As Boolean = False

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim a As Integer
    Dim j As Integer
    Private Sub kittingForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.MdiParent = mainForm
        Control.CheckForIllegalCrossThreadCalls = False
        getSequence()
        initializeDG()
    End Sub

    Private Sub initializeDG()
        Dim partNo As New DataGridViewTextBoxColumn
        Dim machine As New DataGridViewTextBoxColumn
        Dim feeder As New DataGridViewTextBoxColumn
        Dim fsbsQty As New DataGridViewTextBoxColumn
        Dim desc1 As New DataGridViewTextBoxColumn
        Dim spq As New DataGridViewTextBoxColumn
        Dim qty As New DataGridViewTextBoxColumn
        Dim eoh As New DataGridViewTextBoxColumn

        'sequence.HeaderText = "Sequence"
        partNo.HeaderText = "Part Number"
        machine.HeaderText = "Machine"
        feeder.HeaderText = "Feeder"
        desc1.HeaderText = "Description"
        fsbsQty.HeaderText = "Usage"
        spq.HeaderText = "SPQ"
        qty.HeaderText = "Initial_Quantity"
        eoh.HeaderText = "EOH"

        dgKitting.Columns.Add(partNo)
        dgKitting.Columns.Add(machine)
        dgKitting.Columns.Add(feeder)
        dgKitting.Columns.Add(desc1)
        dgKitting.Columns.Add(fsbsQty)
        dgKitting.Columns.Add(spq)
        dgKitting.Columns.Add(qty)
        dgKitting.Columns.Add(eoh)

        dgKitting.Columns(0).ReadOnly = True
        dgKitting.Columns(1).ReadOnly = True
        dgKitting.Columns(2).ReadOnly = True
        dgKitting.Columns(3).ReadOnly = True
        dgKitting.Columns(4).ReadOnly = True
        dgKitting.Columns(5).ReadOnly = True

        dgKitting.Columns(0).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(1).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(2).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(3).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(4).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(5).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(6).SortMode = DataGridViewColumnSortMode.Programmatic
        dgKitting.Columns(7).SortMode = DataGridViewColumnSortMode.Programmatic

        'dgKitting.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dgKitting.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

        dgKitting.RowsDefaultCellStyle.BackColor = Color.AntiqueWhite
        dgKitting.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
    End Sub

    Private Sub fillDataGridView()
        Dim i As Integer = 0

        dgKitting.Rows.Clear()
        For i = 0 To ds.Tables(0).Rows.Count - 1
            dgKitting.Rows.Add()
            dgKitting.Rows(i).Cells(0).Value = Trim(ds.Tables(0).Rows(i).Item("KSQPARTNO"))
            dgKitting.Rows(i).Cells(1).Value = Trim(ds.Tables(0).Rows(i).Item("KSQMODULE"))
            dgKitting.Rows(i).Cells(2).Value = Trim(ds.Tables(0).Rows(i).Item("KSQFEEDERNO"))
            dgKitting.Rows(i).Cells(3).Value = Trim(ds.Tables(0).Rows(i).Item("KSQDESC1"))
            dgKitting.Rows(i).Cells(4).Value = Trim(ds.Tables(0).Rows(i).Item("KSQFSBSQTY"))
            dgKitting.Rows(i).Cells(5).Value = Trim(ds.Tables(0).Rows(i).Item("SPQ"))
            'COMPUTATION OF (USAGE * loading quantity)/ SPQ

            dgKitting.Rows(i).Cells(6).Value = (Double.Parse(txtLoading.Text) * Double.Parse(Trim(ds.Tables(0).Rows(i).Item("KSQFSBSQTY"))))
            dgKitting.Rows(i).Cells(7).Value = 0
            'If kitted Then
            ' dgKitting.Rows(i).Cells(6).Value = Trim(ds.Tables(0).Rows(i).Item("QTY"))
            'End If
        Next
        dgKitting.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
    End Sub

    Private Sub fillDataSet()
        Dim conn As New SqlConnection(cnstr)
        Dim seq As New Sequence
        Dim sequenceno = seq.getSequenceNo(cmbSeq.SelectedItem, cmbLine.SelectedItem, cmbModel.SelectedItem, cmbType.SelectedItem, cmbRev.SelectedItem)
        myQuery = "SELECT KSQPARTNO,KSQMODULE,KSQFEEDERNO,KSQDESC1,[KSQFSBSQTY],'0' AS [SPQ] FROM KSEQUENCE_DETAIL a " & _
                  "INNER JOIN [I-Report].[SYS21].[dbo].[Item_Costing] b ON b.[Item_Code] = a.[KSQPARTNO] WHERE KSEQUENCENO = '" & sequenceno & "' " & _
                  "ORDER BY KSQPARTNO,KSQMODULE,KSQFEEDERNO"

        myCommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            adapter.SelectCommand = myCommand
            ds.Clear()
            adapter.Fill(ds)
            i = 0
            limit = ds.Tables(0).Rows.Count - 1
            If ds.Tables(0).Rows.Count > 0 Then
                btnPrint.Enabled = True
            Else
                btnPrint.Enabled = False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Private Sub fillDataSetKitted()
        Dim conn As New SqlConnection(cnstr)

        myQuery = "SELECT [PARTNO] AS [KSQPARTNO],[MACHINE] AS [KSQMODULE],[FEEDER] AS [KSQFEEDERNO],[DESC1] AS [KSQDESC1],[USAGE] AS [KSQFSBSQTY],[SPQ],[QTY] FROM dbo.KITTING " & _
                  "WHERE [REV] = '" & cmbRev.SelectedItem & "'" & _
                  "AND [ACCOUNT] = '" & cmbSeq.SelectedItem & "'" & _
                  "AND [MODEL] = '" & cmbModel.SelectedItem & "'" & _
                  "AND [TYPE] = '" & cmbType.SelectedItem & "'" & _
                  "AND [LINE] = '" & cmbLine.SelectedItem & "'"


        myCommand = New SqlCommand(myQuery, conn)

        Try
            conn.Open()
            adapter.SelectCommand = myCommand
            ds.Clear()
            adapter.Fill(ds)
            i = 0
            limit = ds.Tables(0).Rows.Count - 1
            If ds.Tables(0).Rows.Count > 0 Then
                kitted = True
                btnPrint.Enabled = True
            Else
                btnPrint.Enabled = False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Public Sub getSequence()
        Dim sequence As New Sequence
        Dim sequenceList As New List(Of String)
        Dim i As Integer = 0

        cmbRev.Items.Clear()
        sequenceList = sequence.getValidSequence()
        cmbSeq.Items.Clear()
        While i < sequenceList.Count
            cmbSeq.Items.Add(sequenceList(i))
            i = i + 1
        End While
    End Sub

    Public Sub getRevisions()
        Dim rev As New Sequence
        Dim revList As New List(Of String)
        Dim i As Integer = 0

        revList = rev.getRevisions(cmbSeq.SelectedItem, cmbLine.SelectedItem, cmbModel.SelectedItem, cmbType.SelectedItem)
        cmbRev.Items.Clear()
        While i < revList.Count
            cmbRev.Items.Add(revList(i))
            i = i + 1
        End While
    End Sub

    Public Sub getModels()
        Dim rev As New Sequence
        Dim revList As New List(Of String)
        Dim i As Integer = 0

        revList = rev.getModel(cmbSeq.SelectedItem, cmbLine.SelectedItem)
        cmbModel.Items.Clear()
        While i < revList.Count
            cmbModel.Items.Add(revList(i))
            i = i + 1
        End While
    End Sub

    Public Sub getTypes()
        Dim rev As New Sequence
        Dim revList As New List(Of String)
        Dim i As Integer = 0

        revList = rev.getTypes(cmbSeq.SelectedItem, cmbLine.SelectedItem, cmbModel.SelectedItem)
        cmbType.Items.Clear()
        While i < revList.Count
            cmbType.Items.Add(revList(i))
            i = i + 1
        End While
    End Sub

    Public Sub getLines()
        Dim rev As New Sequence
        Dim revList As New List(Of String)
        Dim i As Integer = 0

        revList = rev.getLines(cmbSeq.SelectedItem)
        cmbLine.Items.Clear()
        While i < revList.Count
            cmbLine.Items.Add(revList(i))
            i = i + 1
        End While
    End Sub



    Public Sub reset()
        cmbRev.Items.Clear()
        cmbType.Items.Clear()
        cmbModel.Items.Clear()
        cmbLine.Items.Clear()
        cmbSeq.Items.Clear()
        ts_delete.Enabled = False
        btnPrint.Enabled = False
        dgKitting.Rows.Clear()
        txtLoading.Text = Nothing
        getSequence()
    End Sub

    Private Sub cmbRev_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click

        If checkRows() Then
            If kitted Then
                Dim conn As New SqlConnection(cnstr)
                Try
                    conn.Open()
                    For i As Integer = 0 To dgKitting.RowCount - 1
                        myQuery = "UPDATE KITTING SET USAGE = '" & dgKitting.Rows(i).Cells(4).Value.ToString & "', SPQ = '" & dgKitting.Rows(i).Cells(5).Value.ToString & "',OPERATOR = '" & loginForm.username & "',KITTINGDATE = GETDATE(), QTY = '" & dgKitting.Rows(i).Cells(6).Value.ToString & "' WHERE REV = '" & cmbRev.SelectedItem.ToString & "' AND PARTNO = '" & dgKitting.Rows(i).Cells(0).Value.ToString & "' "
                        myCommand = New SqlCommand(myQuery, conn)
                        myCommand.ExecuteNonQuery()
                    Next

                    kittedReportForm.account = cmbSeq.SelectedItem
                    kittedReportForm.line = cmbLine.SelectedItem
                    kittedReportForm.model = cmbModel.SelectedItem
                    kittedReportForm.type = cmbType.SelectedItem
                    kittedReportForm.revision = cmbRev.SelectedItem
                    kittedReportForm.Show()
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Finally
                    conn.Close()
                    conn.Dispose()
                End Try


            Else
                Dim conn As New SqlConnection(cnstr)



                Try
                    conn.Open()
                    For i As Integer = 0 To dgKitting.RowCount - 1
                        myQuery = "INSERT INTO KITTING VALUES('" & cmbRev.SelectedItem.ToString & "','" & dgKitting.Rows(i).Cells(0).Value.ToString & "','" & dgKitting.Rows(i).Cells(3).Value.ToString & "','" & dgKitting.Rows(i).Cells(4).Value.ToString & "','" & dgKitting.Rows(i).Cells(5).Value.ToString & "','" & loginForm.username & "',GETDATE(),'" & dgKitting.Rows(i).Cells(6).Value.ToString & "','" & dgKitting.Rows(i).Cells(2).Value.ToString & "','" & cmbSeq.SelectedItem & "','" & dgKitting.Rows(i).Cells(1).Value.ToString & "','" & cmbModel.SelectedItem & "','" & cmbType.SelectedItem & "','" & cmbLine.SelectedItem & "','" & dgKitting.Rows(i).Cells(7).Value.ToString & "')"
                        myCommand = New SqlCommand(myQuery, conn)
                        myCommand.ExecuteNonQuery()

                    Next

                    kittedReportForm.account = cmbSeq.SelectedItem
                    kittedReportForm.line = cmbLine.SelectedItem
                    kittedReportForm.model = cmbModel.SelectedItem
                    kittedReportForm.type = cmbType.SelectedItem
                    kittedReportForm.revision = cmbRev.SelectedItem
                    kittedReportForm.Show()
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "Error insert", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Finally
                    conn.Close()
                    conn.Dispose()
                End Try
            End If
        End If

        reset()
    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
    Public Function checkRows()
        Dim complete As Boolean = True
        For i As Integer = 0 To dgKitting.RowCount - 1
            If (dgKitting.Rows(i).Cells(4).FormattedValue.ToString = "") Then
                complete = False
            End If
        Next
        Return complete
    End Function

    Private Sub btnPrint_ClientSizeChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrint.ClientSizeChanged

    End Sub

    Private Sub SaveFileDialog_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles SaveFileDialog.FileOk


        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        xlWorkSheet.Cells(1, 1) = "Part No"
        xlWorkSheet.Cells(1, 2) = "Description"
        xlWorkSheet.Cells(1, 3) = "Usage"
        xlWorkSheet.Cells(1, 4) = "SPQ"
        xlWorkSheet.Cells(1, 5) = "Quantity"
        For a = 0 To dgKitting.RowCount - 1
            For j = 0 To dgKitting.ColumnCount - 1
                xlWorkSheet.Cells(a + 2, j + 1) = _
                    dgKitting(j, a).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs(SaveFileDialog.FileName)
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

    Private Sub cmbLine_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbLine.SelectedIndexChanged
        dgKitting.Rows.Clear()
        getModels()
    End Sub

    Private Sub cmbSeq_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSeq.SelectedIndexChanged
        dgKitting.Rows.Clear()
        getLines()
    End Sub

    Private Sub cmbModel_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbModel.SelectedIndexChanged
        dgKitting.Rows.Clear()
        getTypes()
    End Sub

    Private Sub cmbRev_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbRev.SelectedIndexChanged
        If txtLoading.Text <> "" And cmbSeq.SelectedItem <> "" And cmbLine.SelectedItem <> "" And cmbModel.SelectedItem <> "" And cmbType.SelectedItem <> "" And cmbRev.SelectedItem <> "" Then
            'fillDataSetKitted()
            If Not kitted Then
                fillDataSet()
            End If
            fillDataGridView()
        End If
    End Sub

    Private Sub cmbType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbType.SelectedIndexChanged
        dgKitting.Rows.Clear()
        getRevisions()
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        reset()
    End Sub
End Class