Imports System.Data.

SqlClient
Imports MySql.Data.MySqlClient
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Diagnostics
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading
Imports System.Threading.Tasks
Imports System.Windows.Forms

Public Class Form1

Inherits Form

Private WithEvents BackgroundWorker1 As New BackgroundWorker()

Private WithEvents Timer1 As New Windows.Forms.Timer()
Private WithEvents Timer2 As New Windows.Forms.Timer()

Public Sub New()

InitializeComponent()
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

' Configure and start the BackgroundWorker
AddHandler BackgroundWorker1.DoWork, AddressOf BackgroundWorker1_DoWork
AddHandler BackgroundWorker1.ProgressChanged, AddressOf
BackgroundWorker1_ProgressChanged
AddHandler BackgroundWorker1.RunWorkerCompleted, AddressOf
BackgroundWorker1_RunWorkerCompleted
BackgroundWorker1.WorkerReportsProgress = True
BackgroundWorker1.WorkerSupportsCancellation = False
BackgroundWorker1.RunWorkerAsync()

' Configure and start the Timer

Timer1.Interval = 300000 ' 5 min in milliseconds update frequency
Timer1.Enabled = True
AddHandler Timer1.Tick, AddressOf Timer1_Tick
Timer1.Start()

Timer2.Interval = 1000 ' 1 hour in milliseconds

Timer2.Enabled = True
AddHandler Timer2.Tick, AddressOf Timer2_Tick
Timer2.Start()

' Initialize progress bar

ProgressBar1.Minimum = 0
ProgressBar1.Maximum = 100
ProgressBar1.Value = 0
'()
MoveDataFromSqlServerToMySql1()
Me.WindowState = FormWindowState.Minimized
End Sub
Dim starttx As Integer = 0
Private Sub Timer1_Tick(sender As Object, e As EventArgs)
BackgroundWorker1.RunWorkerAsync()
End Sub
 Private Sub Timer2_Tick(sender As Object, e As EventArgs)
Label2.Text = "Date Time :" & DateTime.Now
End Sub

Private Sub MoveDataFromSqlServerToMySql()

'Dim sqlServerConnString As String = "Server=LAPTOP-MM979Q55;Integrated
Security=True;MultipleActiveResultSets=True;Connect Timeout=30;Initial
Catalog=Cavinkare;"
Dim sqlServerConnString As String = "Server=PROD-UNIT2\
SQLEXPRESS;Integrated Security=True;MultipleActiveResultSets=True;Connect
Timeout=30;Initial Catalog=Cavinkare;"

Dim sqlTableName As String = "Weighment"

Dim mysqlConnString As String =

"Server=135.181.112.181;Port=3306;Database=iiot_Assam;uid=iiot_iotteam;password=b7g
zBhE!*E#p;"
'Dim mysqlTableName As String = "MasterWeight_UKM1"

' Determine the date range for the last seven days
Dim startDate As DateTime = DateTime.Today.AddDays(-1)
Dim endDate As DateTime = DateTime.Today

Dim currentProgress As Integer = 0

Dim sqlQuery As String = $"SELECT * FROM Weighment WHERE fst_date BETWEEN

'{startDate.ToString("yyyy-MM-dd")}' AND '{endDate.ToString("yyyy-MM-dd")}'"

' DataTable to hold SQL Server data

Dim sqlServerDataTable As New DataTable()

' Step 1: Retrieve data from SQL Server

Using sqlServerConn As New SqlConnection(sqlServerConnString)
Using sqlCmd As New SqlCommand(sqlQuery, sqlServerConn)
sqlServerConn.Open()
Using sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
sqlServerDataTable.Load(sqlReader)
End Using
End Using
End Using

' Step 2: Connect to MySQL and update the data

Using mysqlConn As New MySqlConnection(mysqlConnString)
mysqlConn.Open()

' Delete existing records in MySQL table

Dim deleteQuery As String = "DELETE FROM weighment_UKM1 WHERE Ticket_no
= @Ticket_no;"

For Each row As DataRow In sqlServerDataTable.Rows

Using mysqlCmdDelete As New MySqlCommand(deleteQuery, mysqlConn)
mysqlCmdDelete.Parameters.AddWithValue("@Ticket_no",
If(row.IsNull("Ticket_no"), "Null", row("Ticket_no")))
mysqlCmdDelete.ExecuteNonQuery()
End Using
Next

' Truncate MySQL table (optional, based on requirement)

'Dim truncateQuery As String = "TRUNCATE TABLE MasterWeight_UKM1"
 'ExecuteNonQuery(mysqlConn, truncateQuery)

' Insert or update data in MySQL table

Dim insertQuery As String = $"INSERT INTO weighment_UKM1 " &
"(Supplier_code, Supplier_name, Product_code, product_name,
transporter_code, transporter_name, " &
"Vehicle_no, fst_wgt, fst_date, fst_time, Ticket_no, type_,
Process_type, snd_wgt, snd_date, " &
"snd_time, net_wgt, wtdate, wtime, Status, weight_type, challno,
challdt, miscell, delivery_n, " &
"challwt, shift, challwt1, trans_name) " &
"VALUES (@Supplier_Code, @Supplier_name, @Product_Code,
@product_Name, @transporter_Code, @transporter_name, " &
"@Vehicle_no, @fst_wgt, @fst_date, @fst_time, @Ticket_no, @type_,
@Process_type, @snd_wgt, @snd_date, " &
"@snd_time, @net_wgt, @wtdate, @wtime, @Status, @weight_type,
@challno, @challdt, @miscell, @delivery_n, " &
"@challwt, @shift, @challwt1, @trans_name) " &
"ON DUPLICATE KEY UPDATE " &
"Supplier_code = VALUES(Supplier_code), Supplier_name =
VALUES(Supplier_name), " &
"Product_code = VALUES(Product_code), product_name =
VALUES(product_name), " &
"transporter_code = VALUES(transporter_code), transporter_name =
VALUES(transporter_name), " &
"Vehicle_no = VALUES(Vehicle_no), fst_wgt = VALUES(fst_wgt),
fst_date = VALUES(fst_date), " &
"fst_time = VALUES(fst_time), type_ = VALUES(type_), Process_type =
VALUES(Process_type), " &
"snd_wgt = VALUES(snd_wgt), snd_date = VALUES(snd_date), snd_time =
VALUES(snd_time), " &
"net_wgt = VALUES(net_wgt), wtdate = VALUES(wtdate), wtime =
VALUES(wtime), " &
"Status = VALUES(Status), weight_type = VALUES(weight_type),
challno = VALUES(challno), " &
"challdt = VALUES(challdt), miscell = VALUES(miscell), delivery_n =
VALUES(delivery_n), " &
"challwt = VALUES(challwt), shift = VALUES(shift), challwt1 =
VALUES(challwt1), " &
"trans_name = VALUES(trans_name);"

For Each row As DataRow In sqlServerDataTable.Rows

Using mysqlCmd As New MySqlCommand(insertQuery, mysqlConn)
mysqlCmd.Parameters.AddWithValue("@Supplier_Code",
If(row.IsNull("Supplier_code"), "Null", row("Supplier_code")))
mysqlCmd.Parameters.AddWithValue("@Supplier_name",
If(row.IsNull("Supplier_name"), "Null", row("Supplier_name")))
mysqlCmd.Parameters.AddWithValue("@Product_Code",
If(row.IsNull("Product_code"), "Null", row("Product_code")))
mysqlCmd.Parameters.AddWithValue("@product_Name",
If(row.IsNull("product_name"), "Null", row("product_name")))
mysqlCmd.Parameters.AddWithValue("@transporter_Code",
If(row.IsNull("transporter_code"), "Null", row("transporter_code")))
mysqlCmd.Parameters.AddWithValue("@transporter_name",
If(row.IsNull("transporter_name"), "Null", row("transporter_name")))
mysqlCmd.Parameters.AddWithValue("@Vehicle_no",
If(row.IsNull("Vehicle_no"), "Null", row("Vehicle_no")))
mysqlCmd.Parameters.AddWithValue("@fst_wgt",
If(row.IsNull("fst_wgt"), "Null", row("fst_wgt")))
 mysqlCmd.Parameters.AddWithValue("@fst_date",
If(row.IsNull("fst_date"), "Null", row("fst_date")))
mysqlCmd.Parameters.AddWithValue("@fst_time",
If(row.IsNull("fst_time"), "Null", row("fst_time")))
mysqlCmd.Parameters.AddWithValue("@Ticket_no",
If(row.IsNull("Ticket_no"), "Null", row("Ticket_no")))
mysqlCmd.Parameters.AddWithValue("@type_",
If(row.IsNull("type"), "Null", row("type")))
mysqlCmd.Parameters.AddWithValue("@Process_type",
If(row.IsNull("Process_type"), "Null", row("Process_type")))
mysqlCmd.Parameters.AddWithValue("@snd_wgt",
If(row.IsNull("snd_wgt"), "Null", row("snd_wgt")))
mysqlCmd.Parameters.AddWithValue("@snd_date",
If(row.IsNull("snd_date"), "Null", row("snd_date")))
mysqlCmd.Parameters.AddWithValue("@snd_time",
If(row.IsNull("snd_time"), "Null", row("snd_time")))
mysqlCmd.Parameters.AddWithValue("@net_wgt",
If(row.IsNull("net_wgt"), "Null", row("net_wgt")))
mysqlCmd.Parameters.AddWithValue("@wtdate",
If(row.IsNull("wtdate"), "Null", row("wtdate")))
mysqlCmd.Parameters.AddWithValue("@wtime",
If(row.IsNull("wtime"), "Null", row("wtime")))
mysqlCmd.Parameters.AddWithValue("@Status",
If(row.IsNull("Status"), "Null", row("Status")))
mysqlCmd.Parameters.AddWithValue("@weight_type",
If(row.IsNull("weight_type"), "Null", row("weight_type")))
mysqlCmd.Parameters.AddWithValue("@challno",
If(row.IsNull("challno"), "Null", row("challno")))
mysqlCmd.Parameters.AddWithValue("@challdt",
If(row.IsNull("challdt"), "Null", row("challdt")))
mysqlCmd.Parameters.AddWithValue("@miscell",
If(row.IsNull("miscell"), "Null", row("miscell")))
mysqlCmd.Parameters.AddWithValue("@delivery_n",
If(row.IsNull("delivery_n"), "Null", row("delivery_n")))
mysqlCmd.Parameters.AddWithValue("@challwt",
If(row.IsNull("challwt"), "Null", row("challwt")))
mysqlCmd.Parameters.AddWithValue("@shift",
If(row.IsNull("shift"), "Null", row("shift")))
mysqlCmd.Parameters.AddWithValue("@challwt1",
If(row.IsNull("challwt1"), "Null", row("challwt1")))
mysqlCmd.Parameters.AddWithValue("@trans_name",
If(row.IsNull("trans_name"), "Null", row("trans_name")))
'mysqlCmd.Parameters.AddWithValue("@Flag", "0")
mysqlCmd.ExecuteNonQuery()
End Using

currentProgress = CInt((row.Table.Rows.IndexOf(row) + 1) /
sqlServerDataTable.Rows.Count * 100)
BackgroundWorker1.ReportProgress(currentProgress)
Next
End Using
MoveDataFromSqlServerToMySql1()
End Sub

Private Sub ExecuteNonQuery(connection As IDbConnection, query As String)

Using cmd As IDbCommand = connection.CreateCommand()
cmd.CommandText = query
cmd.ExecuteNonQuery()
End Using
 End Sub
Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs)
Handles BackgroundWorker1.DoWork
' Call the method to move data
MoveDataFromSqlServerToMySql()

End Sub

Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As

ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
' Update progress bar
ProgressBar1.Value = e.ProgressPercentage
If e.ProgressPercentage = 100 Then
BackgroundWorker1.Dispose()
ProgressBar1.Value = 0
Label1.Text = "Last data transferred on " & DateTime.Now
End If
End Sub
Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As
RunWorkerCompletedEventArgs)
If e.Error IsNot Nothing Then
' Handle any errors
MessageBox.Show($"An error occurred: {e.Error.Message}", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf e.Cancelled Then
' Handle cancellation
'LabelStatus.Text = "Operation canceled."
Else
' Handle successful completion

End If
End Sub

Private Sub Form1_Resize(sender As Object, e As EventArgs) Handles Me.Resize

If Me.WindowState = FormWindowState.Minimized Then
'NotifyIcon1.Visible = True
Me.Hide()
'NotifyIcon1.BalloonTipText = "Hi from right system tray"
'NotifyIcon1.ShowBalloonTip(500)
End If
End Sub
Private Sub MoveDataFromSqlServerToMySql1()
'Dim sqlServerConnString As String = "Server=LAPTOP-MM979Q55;Integrated
Security=True;MultipleActiveResultSets=True;Connect Timeout=30;Initial
Catalog=Cavinkare;"
Dim sqlServerConnString As String = "Server=PROD-UNIT2\
SQLEXPRESS;Integrated Security=True;MultipleActiveResultSets=True;Connect
Timeout=30;Initial Catalog=Cavinkare;"

Dim sqlQuery As String = "SELECT * FROM Pending;"

Dim mysqlConnString As String =

"Server=135.181.112.181;Port=3306;Database=iiot_Assam;uid=iiot_iotteam;password=b7g
zBhE!*E#p;"
Dim mysqlTableName As String = "Pending_UKM1"

Dim sqlServerDataTable As New DataTable()

Using sqlServerConn As New SqlConnection(sqlServerConnString)

 Using sqlCmd As New SqlCommand(sqlQuery, sqlServerConn)
sqlServerConn.Open()
Using sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
sqlServerDataTable.Load(sqlReader)
End Using
End Using
End Using

MoveDataToMySql(sqlServerDataTable, mysqlConnString, mysqlTableName)

End Sub

Private Sub MoveDataToMySql(dataTable As DataTable, mysqlConnString As String,

mysqlTableName As String)
Using mysqlConn As New MySqlConnection(mysqlConnString)
mysqlConn.Open()

Dim totalRows As Integer = dataTable.Rows.Count

Dim currentProgress As Integer = 0

Dim truncateQuery As String = $"TRUNCATE TABLE {mysqlTableName};"

ExecuteNonQuery1(mysqlConn, truncateQuery)

For Each row As DataRow In dataTable.Rows

Dim insertQuery As String = $"INSERT INTO {mysqlTableName} " &
"(TICKET_NO, Supplier_code, Supplier_name, Product_code,
product_name, " &
"transporter_code, transporter_name, Vehicle_no,
fst_wgt, fst_date, " &
"fst_time, type_, Process_type, challno, challdt,
miscell, delivery_n, challwt, trans_name) " &
"VALUES (@ticket_no, @supplier_code, @supplier_name,
@product_code, @product_name, " &
"@transporter_code, @transporter_name, @vehicle_no,
@fst_wgt, @fst_date, " &
"@fst_time, @type_, @process_type, @challno,
@challdt, @miscell, @delivery_n, @challwt, @trans_name)"
Using mysqlCmd As New MySqlCommand(insertQuery, mysqlConn)
' Add parameters, handle null values
mysqlCmd.Parameters.AddWithValue("@TICKET_NO",
If(row.IsNull("TICKET_NO"), "Null", row("TICKET_NO")))
mysqlCmd.Parameters.AddWithValue("@Supplier_Code",
If(row.IsNull("Supplier_code"), "Null", row("Supplier_code")))
mysqlCmd.Parameters.AddWithValue("@Supplier_name",
If(row.IsNull("Supplier_name"), "Null", row("Supplier_name")))
mysqlCmd.Parameters.AddWithValue("@Product_code",
If(row.IsNull("Product_code"), "Null", row("Product_code")))
mysqlCmd.Parameters.AddWithValue("@product_name",
If(row.IsNull("product_name"), "Null", row("product_name")))
mysqlCmd.Parameters.AddWithValue("@transporter_code",
If(row.IsNull("transporter_code"), "Null", row("transporter_code")))
mysqlCmd.Parameters.AddWithValue("@transporter_name",
If(row.IsNull("transporter_name"), "Null", row("transporter_name")))
mysqlCmd.Parameters.AddWithValue("@Vehicle_no",
If(row.IsNull("Vehicle_no"), "Null", row("Vehicle_no")))
mysqlCmd.Parameters.AddWithValue("@fst_wgt",
If(row.IsNull("fst_wgt"), "Null", row("fst_wgt")))
mysqlCmd.Parameters.AddWithValue("@fst_date",
If(row.IsNull("fst_date"), "Null", row("fst_date")))
mysqlCmd.Parameters.AddWithValue("@fst_time",
If(row.IsNull("fst_time"), "Null", row("fst_time")))
mysqlCmd.Parameters.AddWithValue("@type_",
If(row.IsNull("type"), "Null", row("type")))
mysqlCmd.Parameters.AddWithValue("@Process_type",
If(row.IsNull("Process_type"), "Null", row("Process_type")))
mysqlCmd.Parameters.AddWithValue("@challno",
If(row.IsNull("challno"), "Null", row("challno")))
mysqlCmd.Parameters.AddWithValue("@challdt",
If(row.IsNull("challdt"), "Null", row("challdt")))
mysqlCmd.Parameters.AddWithValue("@miscell",
If(row.IsNull("miscell"), "Null", row("miscell")))
mysqlCmd.Parameters.AddWithValue("@delivery_n",
If(row.IsNull("delivery_n"), "Null", row("delivery_n")))
mysqlCmd.Parameters.AddWithValue("@challwt",
If(row.IsNull("challwt"), "Null", row("challwt")))
mysqlCmd.Parameters.AddWithValue("@trans_name",
If(row.IsNull("trans_name"), "Null", row("trans_name")))
mysqlCmd.ExecuteNonQuery()
End Using

' Update progress

currentProgress = CInt((row.Table.Rows.IndexOf(row) + 1) /
totalRows * 100)
BackgroundWorker1.ReportProgress(currentProgress)
Next
End Using
End Sub

Private Sub ExecuteNonQuery1(connection As IDbConnection, query As String)

Using cmd As IDbCommand = connection.CreateCommand()
cmd.CommandText = query
cmd.ExecuteNonQuery()
End Using
End Sub

End Class