vb ma

'The SQL data and syntax imported into the VBA.Net code
Imports System.Data
Imports MySql.Data
Imports MySql.Data.MySqlClient

Public Class Main
    'code in order to connect the vba code to the MySQL database
    Dim cmd As MySqlCommand
    Dim sbCmd As New System.Text.StringBuilder
    Public connStr As String = "server=127.0.0.1;database=stockmanagementsystem;port=3306;"
    Public conn As New MySqlConnection(connStr)
    Dim items As New List(Of Item)
    Dim ds As New DataSet
    Sub SetUpDB()
        'A serise of DDL statements to set up the database
        'These are to create the tables
        Try
            MsgBox("Connected to MySQL...")
            conn.Open()
            ' Create CurrentStock========================

            'StockDetails
            sbCmd.Append("Create Table If Not Exists StockItems ")
            sbCmd.Append("(StockID int PRIMARY KEY, ItemName VarChar(25), Price DECIMAL, Weight VarChar(10))")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            'StockLevel=========================================
            sbCmd.Append("Create Table If Not Exists StockLevel ")
            sbCmd.Append("(StockID VarChar(13) PRIMARY KEY, Qty int, DateStamp DATE)")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            '=============Deliveries
            sbCmd.Append("Create Table If Not Exists Deliveries ")
            sbCmd.Append("(StockID int PRIMARY KEY,QTY int ,DateTimeDelivered DATE)")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            '=============SalesAndPreviousSales
            sbCmd.Append("Create Table If Not Exists SalesAndPreviousSales ")
            sbCmd.Append("(Sales VarChar(6), PreviousSales VarChar(5), Total Decimal, PRIMARY KEY (Sales, PreviousSales))") 'NB composite key syntax
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            '=============SalesAnalysis
            sbCmd.Append("Create Table If Not Exists SalesAnalysis ")
            sbCmd.Append("(HighestSales VarChar(6), LowestSales VarChar(5), PRIMARY KEY (HighestSales, LowestSales))") 'NB composite key syntax
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            '=============OrderTable
            sbCmd.Append("Create Table If Not Exists Orders")
            sbCmd.Append("(OrderID VarChar(6), OrderDate VarChar(5), PRIMARY KEY (OrderID))")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()

            '=============StockCheck
            sbCmd.Append("Create Table If Not Exists StockCheck")
            sbCmd.Append("(StockID int, DateTimeCheck DATE, Quantity int, PRIMARY KEY (StockID, DateTimeCheck))")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.ExecuteNonQuery()
            sbCmd.Clear()
            conn.Close()
        Catch ex As Exception
            MsgBox("Connection unsuccessful")
        End Try

    End Sub
    'This Sub is used in order to read items from the MySQL database and output them into the Web browser in the Forms window
    Sub TestSQL()
        Dim Reader As MySqlDataReader
        conn.Open()
        'Reads my SQL data
        Dim sbDataTable As New System.Text.StringBuilder
        sbCmd.Clear()
        sbDataTable.Append("<table border='1' width='100%'><tr><td>StockId</td><td>itemname</td><td>price<td></td></tr>") ' Html code in order to set up the table in the web browser
        'statement used in order to gather data from StockItems field on the database
        sbCmd.Append("Select * from stockItems ORDER by StockId ASC ") 'selects stockitems from the database

        cmd = New MySqlCommand(sbCmd.ToString, conn)
        Reader = cmd.ExecuteReader()
        Dim tempitem As New Item ' Reads SQL data and builds a html table
        While Reader.Read()
            tempitem.Id = Reader("StockId")
            tempitem.Name = Reader("itemname")
            tempitem.Price = Reader("price")
            tempitem.Price = Reader("price")
            sbDataTable.Append("<tr><td>" & tempitem.Id & "</td><td>" & tempitem.Name & "</td><td>" & tempitem.Price & "</td></tr>")
            items.Add(tempitem)
        End While
        WB.DocumentText = sbDataTable.ToString() 'outputs html table to web browser
        Reader.Close()
        sbCmd.Clear()
    End Sub
    Private Sub Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' MsgBox("system loaded")
        'CurrentItemsindatabase.Show()
        'SetUpDB() 'can be uncommented in order to add new tables, but once set up it is no longer needed
        TestSQL()
    End Sub




    '------------------- Enter data button for the adding the items
    Private Sub AddItemEnter_Click(sender As Object, e As EventArgs) Handles AddItemEnter.Click
        Try
            ' assigns variables to a text box for the user to enter their data
            Dim StockIDItem As Integer = txtID.Text
            Dim WeightItem As Integer = txtWeight.Text
            Dim PriceItem As Decimal = txtPrice.Text
            Dim ItemName As String = txtName.Text
            sbCmd.Clear()

            sbCmd.Append("INSERT INTO `stockitems` (`StockID`, `ItemName`, `Price`, `Weight`) VALUES(@SID,@ItemName,@Price,@Weight")
            'sbCmd.Append("('" & StockIDItem & "', '" & ItemName & "', '" & Price & "', '" & Weight & "');")
            cmd = New MySqlCommand(sbCmd.ToString, conn)
            cmd.Parameters.AddWithValue("@SID", StockIDItem)
            cmd.Parameters.AddWithValue("@ItemName", ItemName)
            cmd.Parameters.AddWithValue("@Price", PriceItem)
            cmd.Parameters.AddWithValue("@Weight", WeightItem)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Item has been entered")
        Catch ex As Exception
            MsgBox("Please enter valid data")
        End Try

    End Sub
    ' links to code for each button as a reference
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Deliveries.Show()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        EPOS.Show()
    End Sub

    Private Sub Orders_Click(sender As Object, e As EventArgs) Handles btnorder.Click
        Orders.Show()
    End Sub

    Private Sub BtnStockCheck_Click(sender As Object, e As EventArgs)
        StockCheck.Show()
    End Sub

    Private Sub BtnStockLevel_Click(sender As Object, e As EventArgs) Handles BtnStockLevel.Click
        BtnStockItem.Show()
    End Sub

    Private Sub BtnDelItems_Click(sender As Object, e As EventArgs) Handles BtnDelItems.Click
        sbCmd.Clear()
        sbCmd.Append("DELETE FROM `stockitems` WHERE `StockID` = @SID")
        cmd = New MySqlCommand(sbCmd.ToString, conn)
        cmd.Parameters.AddWithValue("@SID", txtID.Text)

        cmd.ExecuteNonQuery()
        conn.Close()
        TestSQL()


    End Sub

    Private Sub Regression_Click(sender As Object, e As EventArgs) Handles Regression.Click
        Try 'try catch used to validate the regression input of the stockid if nothing is entered or an exception is thrown
            Dim RegressionStockID As Integer = TxtStockIDRegression.Text
            Dim SQLRetrieveDate As String = "SELECT Quantity, DateTimeSold from sales where stockid = @sid" 'Reads sales data from database
            Dim salesreader As MySqlDataReader
            Dim sales As New List(Of Sale) 'adds a list of sales data from the database for the algorith to process
            Dim RegressionPoints As New List(Of RegPoint)
            Dim RateOfSales, StockatStratOfCalculation, currentStock, regressionStartstockQty As Double
            StockatStratOfCalculation = 200
            currentStock = 0
            cmd = New MySqlCommand(SQLRetrieveDate, conn)
            cmd.Parameters.AddWithValue("@sid", RegressionStockID)
            salesreader = cmd.ExecuteReader
            While salesreader.Read()
                sales.Add(New Sale(RegressionStockID, salesreader("Quantity"), salesreader("Datetimesold"))) 'adds sales data to a variable
            End While
            For Each sale In sales
                currentStock = currentStock - sale.QtyOfSales
                RegressionPoints.Add(New RegPoint(sale.DateTimeSold, currentStock))
            Next
            MsgBox(" Error = " & LinearLeastSquaresCalc(RegressionPoints, RateOfSales, regressionStartstockQty) & "   Gradient = " & RateOfSales & "   Intercept= " & regressionStartstockQty) 'outputs data into a message box when button is clicked
            salesreader.Close()
            conn.Close()
        Catch ex As Exception
            MsgBox("Enter a valid StockID") ' shows the user that their input was wrong
        End Try
    End Sub

    Public Function DaysTillOutofstock(ByVal currentStock As Integer, ByVal salerate As Single)
        'salerate is sales per day
        'days left = currentStock/sales rate
        DaysTillOutofstock = currentStock / salerate
    End Function
    Public Function LinearLeastSquaresCalc(ByVal DataPoints As List(Of RegPoint), ByRef Gradient As Double, ByRef Intercept As Double) As Double
        ' Performs the calculation.
        ' Find the values simplifiedx ect....
        Dim Simplified1 As Double = DataPoints.Count
        Dim Simplifiedx As Double = 0
        Dim Simplifiedy As Double = 0
        Dim Simplifiedxx As Double = 0
        Dim Simplifiedxy As Double = 0
        For Each DataPoint As RegPoint In DataPoints
            Simplifiedx += DataPoint.x
            Simplifiedy += DataPoint.y
            Simplifiedxx += DataPoint.x * DataPoint.x
            Simplifiedxy += DataPoint.x * DataPoint.y
        Next

        ' Solve for Gradient and Intercept.
        Gradient = (Simplifiedxy * Simplified1 - Simplifiedx * Simplifiedy) / (Simplifiedxx * Simplified1 - Simplifiedx * Simplifiedx)
        Intercept = (Simplifiedxy * Simplifiedx - Simplifiedy * Simplifiedxx) / (Simplifiedx * Simplifiedx - Simplified1 * Simplifiedxx)

        Return Math.Sqrt(ESqrd(DataPoints, Gradient, Intercept)) ' this function actually returns the error not the gradient
    End Function
    Public Function ESqrd(ByVal DataPoints As List(Of RegPoint), ByVal Gradient As Double, ByVal Intercept As Double) As Double 'The error squared sub produces a value that shows how accurate the sales are, the higher the error the least accurate
        Dim total As Double = 0
        For Each DataPoint As RegPoint In DataPoints
            Dim yDiff As Double = DataPoint.y - (Gradient * CDbl(DataPoint.x) + Intercept)
            total += yDiff ^ 2
        Next
        Return total
    End Function

    Class RegPoint
        Property Sale As Date
        Property x As Single
        Property y As Single
        Sub New(ByVal saleDate As Date, y As Single)
            Dim interval As TimeSpan = saleDate - #01/01/2019#
            Me.x = interval.Days()
            Me.y = y
        End Sub
    End Class


End Class

Class Item
    Property Id As Integer
    Property Name As String
    Property Price As Decimal
    Property Weight As Single
    Property Sales As List(Of Sale)
    Property StockLevels As List(Of Stocklevel)
    Property Deliveryitems As List(Of Delivery)
    Property OrderItems As List(Of OrdersClass)
End Class

Class Stocklevel 'classes used for item reader for MySQL
    Property StockId As Integer
    Property Qtyitem As Integer
    Property Datestamp As Date
End Class
Class Sale 'classes used for item reader for MySQL


    Property StockIdSales As Integer
    Property QtyOfSales As Integer
    Property DateTimeSold As DateTime
    Sub New(id, qty, dateNtime) 'Class used for regression
        Me.StockIdSales = id
        Me.QtyOfSales = qty
        Me.DateTimeSold = dateNtime
    End Sub
End Class
Class Delivery 'classes used for item reader for MySQL
    Property StockIDDelivery As Integer
    Property QTYDelivery As Integer
    Property Datetimedelivered As DateTime
End Class
Class OrdersClass 'classes used for item reader for MySQL
    Property OrderIDItem As Integer
    Property OrderDateItem As Date
End Class
Class StockCheckClass 'classes used for item reader for MySQL
    Property StockIDCheck As Integer
    Property DateTimeCheck As DateTime
    Property QuantityCheck As Integer
End Class