Code:
Private Sub IDfield_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles IDfield.KeyPress
If Asc(e.KeyChar) = Keys.Enter Then
If IDfield.Text = Nothing Then
MsgBox("Dont forget to enter the Operator ID")
ElseIf IDfield.Text.Length < 4 Then
MsgBox("Operator ID must have 4 to 8 characters")
Else
Dim samplefrmt As Integer = 0
BatSizenew = Form1.BatSize.Text
BatSizenew = CInt(Int(Form1.BatSize.Text))
For ya As Integer = 0 To Form1.SelBat.Items.Count - 1
Timer1.Start()
Timer1.Stop()
rec1 = 1
Dim imgCount As Integer = Val(Form1.SelBat.Items(ya).SubItems(1).Text)
Dim batSz As Integer = Val(Form1.BatSize.Text)
Dim dbCount As Integer = CInt(Math.Ceiling(imgCount / batSz))
Dim batchFolderName As String = Form1.SelBat.Items(ya).SubItems(0).Text
Dim batchFolderDirectory As String = Form1.Dir_folder.Text & "\DDC OS" & "\CARD DECK" & "\" & DateTime.Now.ToString("yyyyMMdd") & "\" & batchFolderName
Dim testPath1 As String = batchFolderDirectory & "\Compare"
Dim testPath5 As String = batchFolderDirectory & "\Entry1"
Dim testPath2 As String = batchFolderDirectory & "\Entry2"
Dim testPath3 As String = batchFolderDirectory & "\Images"
Dim testPath4 As String = Form1.Dir_folder.Text & "\CBATCH"
Dim testPath6 As String = Form1.Dir_folder.Text & "\CBATCH" & "\CardDeck" & "\" & DateTime.Now.ToString("yyyyMMdd")
Dim ncbpth As String = testPath6 & "\" & Form1.SelBat.Items(ya).SubItems(0).Text & ".mdb"
Dim ncbtext As String = testPath6 & "\" & Form1.SelBat.Items(ya).SubItems(0).Text & ".txt"
If Directory.Exists(batchFolderDirectory) Then
Dim ans As DialogResult = MessageBox.Show("Already contains a folder name", "Do you want to Overwrite?", MessageBoxButtons.YesNoCancel)
If ans = Windows.Forms.DialogResult.Yes Then
Try
Timer1.Start()
aldrin: Directory.Delete(batchFolderDirectory, True)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
Application.Exit()
End Try
IO.File.Delete(ncbpth)
IO.File.Delete(ncbtext)
ElseIf ans = Windows.Forms.DialogResult.No Then
'Timer1.Stop()
'Form1.ProgressBar1.Value = 0
Continue For
Else
'Timer1.Stop()
'Form1.ProgressBar1.Value = 0
Exit For
End If
End If
If Not IO.Directory.Exists(testPath5) Then
MkDir(testPath5)
End If
If Not IO.Directory.Exists(testPath1) Then
MkDir(testPath1)
End If
If Not IO.Directory.Exists(testPath2) Then
MkDir(testPath2)
End If
If Not IO.Directory.Exists(testPath3) Then
MkDir(testPath3)
End If
If Not IO.Directory.Exists(testPath4) Then
MkDir(testPath4)
End If
If Not IO.Directory.Exists(testPath6) Then
MkDir(testPath6)
End If
dir = testPath1
Dim cbatpath As String = Form1.SelBat.Items(ya).SubItems(0).Text
Dim file As System.IO.StreamWriter
Dim s As String
Dim u As String
s = Form1.SelBat.Items(ya).SubItems(2).Text & "\" & Form1.SelBat.Items(ya).SubItems(0).Text
u = IDfield.Text
file = My.Computer.FileSystem.OpenTextFileWriter(testPath6 & "\" & cbatpath & ".txt", False)
file.WriteLine("Client name : DDC OS")
file.WriteLine("Job name : CARD DECK")
file.WriteLine("Programmer name : Jeramiel Ortega")
file.WriteLine("Project Coordinator : Marites Santos")
file.WriteLine("Date : " & Format(Now, "f"))
file.WriteLine("Shipment/Zipfile : " & s)
file.WriteLine("Batch by(OperID) : " & u)
file.WriteLine("Directory Compare : " & testPath1)
file.WriteLine("Directory Entry1 : " & testPath5)
file.WriteLine("Directory Entry2 : " & testPath2)
file.WriteLine("Directory Images : " & testPath3)
file.WriteLine("Date Received : _____________________________")
file.WriteLine("Output Date : _____________________________")
file.WriteLine("DE Target : _____________________________")
file.WriteLine("QC Target : _____________________________")
file.WriteLine("Val Target : _____________________________")
file.WriteLine("")
file.WriteLine(String.Format("{0,0} {1,23} {2,15} {3,15}", "MDB Names", "Start Record", "End Record", "BatchCount"))
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + testPath6 + "\" & cbatpath & ".mdb")
'***********************CBATCH TABLE**********************************
conctn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=" + testPath6 + "\" & cbatpath & ".mdb")
Dim cmnd As New OleDb.OleDbCommand("CREATE TABLE CardDeck( " & _
"RecNum int identity," & "ZipFile NVarchar(50)," & "DvdName NVarchar(50)," & "BatchNo NVarchar(50)," & "OImage001 NVarchar(50)," & _
"Image001 NVarchar(50)," & "CONSTRAINT [pk_RecNum] PRIMARY KEY (RecNum)) ", conctn)
conctn.Open()
cmnd.ExecuteNonQuery()
'conctn.Close()
'******************************ENDS HERE!! ****************************
'************DB PER BATSIZE*************
Dim imageNum As Integer = 0
Dim ss As Integer = 0
'2ND LOOP HERE
For i As Integer = 0 To dbCount - 1
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + dir + "\" & Format(i + 1, "00000000") & ".mdb")
conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=" + dir + "\" & Format(i + 1, "00000000") & ".mdb")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE Data001( " & _
"RecNum int identity," & "PullOut int," & "Title NVarchar(50)," & "Firstname NVarchar(50)," & "Surname NVarchar(50)," & _
"Address1 NVarchar(50)," & "Address2 NVarchar(50)," & "Address3 NVarchar(50)," & "County NVarchar(50)," & "Postcode text," & _
"QaskFlag NVarchar(50)," & "Telephone number," & "Mobile number," & "Email NVarchar(50)," & "DOB NVarchar(50)," & "Field001 NVarchar(50)," & _
"Field002 NVarchar(50)," & "Field003 NVarchar(50)," & "Field004 NVarchar(50)," & "Field005 NVarchar(50)," & "CONSTRAINT [pk_RecNum] PRIMARY KEY (RecNum)) ", conn)
conn.Open()
cmd.ExecuteNonQuery()
Dim cmd1 As New OleDb.OleDbCommand("CREATE TABLE CardDeck ( " & _
"RecNum int identity," & "ZipFile NVarchar(50)," & "DvdName NVarchar(50)," & "BatchNo NVarchar(50)," & "PullOut int," & _
"RefInd int," & "KeyFlag NVarchar(50)," & "KeyId NVarchar(50)," & "KeyDate NVarchar(50)," & "VerFlag NVarchar(50)," & "VerID NVarchar(50)," & _
"VerDate int," & "ComFlag int," & "ComID NVarchar(50)," & "ComDate NVarchar(50)," & "UpdFlag NVarchar(50)," & "UpdID NVarchar(50)," & _
"UpdDate NVarchar(50)," & "QCFlag NVarchar(50)," & "QCID NVarchar(50)," & "QCDate NVarchar(50)," & "QASFlag NVarchar(50)," & _
"QASID NVarchar(50)," & "QASDate NVarchar(50)," & "ValFlag NVarchar(50)," & "ValID NVarchar(50)," & "ValDate NVarchar(50)," & _
"RefFlag NVarchar(50)," & "RefID NVarchar(50)," & "RefDate NVarchar(50)," & "Accuracy1 NVarchar(50)," & "Accuracy2 NVarchar(50)," & _
"Accuracy3 NVarchar(50)," & "Accuracy4 NVarchar(50)," & "Accuracy5 NVarchar(50)," & "KSCount NVarchar(50)," & "QCID1 NVarchar(50)," & _
"QCID2 NVarchar(50)," & "QCID3 NVarchar(50)," & "QCID4 NVarchar(50)," & "QCID5 NVarchar(50)," & "LKFlag1 NVarchar(50)," & _
"LKFlag2 NVarchar(50)," & "LKFlag3 NVarchar(50)," & "LKFlag4 NVarchar(50)," & "LKFlag5 NVarchar(50)," & "LocId1 NVarchar(50)," & _
"LocId2 NVarchar(50)," & "OImage001 NVarchar(250)," & "Image001 Varchar(150)," & "CONSTRAINT [pk_RecNum] PRIMARY KEY (RecNum)) ", conn)
cmd1.ExecuteNonQuery()
dir2 = Form1.Dir_folder.Text + "\CBATCH"
Dim ost As String
Dim dbinsert As String = "00000000.##"
samplefrmt = samplefrmt + 1
ost = samplefrmt.ToString(dbinsert) & ".mdb"
Form1.FileListBox1.Path = Form1.SelBat.Items(ya).SubItems(2).Text & "\" & Form1.SelBat.Items(ya).SubItems(0).Text
Dim src As String
Dim bS As String = Form1.SelBat.Items(ya).SubItems(2).Text & "\" & Form1.SelBat.Items(ya).SubItems(0).Text
Dim dest As String
Form1.FileListBox1.Path = Form1.SelBat.Items(ya).SubItems(2).Text & "\" & Form1.SelBat.Items(ya).SubItems(0).Text
Dim imgFrmt As String = "00000000.##"
Dim flcount As Integer = Form1.FileListBox1.Items.Count
Dim pieces As Integer = 0
Dim ssfrmt As String = ""
'3rd loop
For n As Integer = imageNum To flcount - 1
pieces = pieces + 1
Dim op As String = Form1.Dir_folder.Text & "\DDC OS" & "\CARD DECK" & "\" & DateTime.Now.ToString("yyyyMMdd") & "\" & batchFolderName & "\Images"
ss = ss + 0
Dim sam As String
imageNum = imageNum + 1
src = bS + "\" + Form1.FileListBox1.Items(n).ToString
dest = op + "\" + imageNum.ToString(imgFrmt) & ".tif"
System.IO.File.Copy(src, dest, True)
k = imageNum.ToString(imgFrmt) & ".tif"
f = Form1.FileListBox1.Items(n).ToString
d = Form1.SelBat.Items(ya).SubItems(0).Text
Dim filePath As String = Form1.SelBat.Items(ya).SubItems(2).Text
Dim directory As String = Path.GetDirectoryName(filePath)
Dim split As String() = filePath.Split("\")
Dim parentFolder As String = split(split.Length - 1)
c = parentFolder
Dim cmdB1 As String = "INSERT INTO [CardDeck] ([ZipFile],[DvdName],[BatchNo],[RefId],[VerFlag],[ComFlag],[UpdFlag],[QCFlag],[QASFlag],[ValFlag],[RefFlag],[LKFlag1],[LKFlag2],[LKFlag3],[LKFlag4],[LKFlag5],[OImage001],[Image001]) VALUES (@ZipFile, @DvdName, @BatchNo, @RefId, @VerFlag, @ComFlag, @UpdFlag, @QCFlag, @QASFlag, @ValFlag, @RefFlag, @LKFlag1, @LKFlag2, @LKFlag3, @LKFlag4, @LKFlag5, @OImage001, @Image001);"
Dim cmdG1 As New OleDb.OleDbCommand(cmdB1, conn)
cmdG1.Parameters.AddWithValue("@ZipFile", d)
cmdG1.Parameters.AddWithValue("@DvdName", c)
cmdG1.Parameters.AddWithValue("@BatchNo", "0000001")
cmdG1.Parameters.AddWithValue("@ReFId", "0")
cmdG1.Parameters.AddWithValue("@VerFlag", "0")
cmdG1.Parameters.AddWithValue("@ComFlag", "0")
cmdG1.Parameters.AddWithValue("@UpdFlag", "0")
cmdG1.Parameters.AddWithValue("@QCFlag", "0")
cmdG1.Parameters.AddWithValue("@QASFlag", "0")
cmdG1.Parameters.AddWithValue("@ValFlag", "0")
cmdG1.Parameters.AddWithValue("@RefFlag", "0")
cmdG1.Parameters.AddWithValue("@LKFlag1", "0")
cmdG1.Parameters.AddWithValue("@LKFlag2", "0")
cmdG1.Parameters.AddWithValue("@LKFlag3", "0")
cmdG1.Parameters.AddWithValue("@LKFlag4", "0")
cmdG1.Parameters.AddWithValue("@LKFlag5", "0")
cmdG1.Parameters.AddWithValue("@OImage001", f)
cmdG1.Parameters.AddWithValue("@Image001", k)
cmdG1.ExecuteNonQuery()
sam = ss.ToString(ssfrmt)
Dim cmdB As String = ("INSERT INTO [Data001] ([PullOut],[QaskFlag],[Field001],[Field002],[Field003],[Field004],[Field005]) VALUES (@PullOut,@QaskFlag,@Field001,@Field002,@Field003,@Field004,@Field005)")
Dim cmdG As New OleDb.OleDbCommand(cmdB, conn)
cmdG.Parameters.AddWithValue("@PullOut", "0")
cmdG.Parameters.AddWithValue("@QaskFlag", "0")
cmdG.Parameters.AddWithValue("@Field001", "0")
cmdG.Parameters.AddWithValue("@Field002", "0")
cmdG.Parameters.AddWithValue("@Field003", "0")
cmdG.Parameters.AddWithValue("@Field004", "0")
cmdG.Parameters.AddWithValue("@Field005", sam)
cmdG.ExecuteNonQuery()
'**********************CBATCH INSERT FIELDS VALUE*******************
Dim cmdB3 As String = "INSERT INTO [CardDeck] ([ZipFile],[DvdName],[BatchNo],[OImage001],[Image001]) VALUES (@ZipFile, @DvdName, @BatchNo, @OImage001, @Image001);"
Dim cmdG3 As New OleDb.OleDbCommand(cmdB3, conctn)
cmdG3.Parameters.AddWithValue("@ZipFile", d)
cmdG3.Parameters.AddWithValue("@DvdName", c)
cmdG3.Parameters.AddWithValue("@BatchNo", "00000001")
cmdG3.Parameters.AddWithValue("@OImage001", f)
cmdG3.Parameters.AddWithValue("@Image001", k)
cmdG3.ExecuteNonQuery()
'****************************ENDS HERE!!!****************************
rec2 += 1
If pieces = batSz Then
Exit For
End If
Timer1.Start()
Next n
bS = Form1.SelBat.Items(ya).SubItems(2).Text & "\" & Form1.SelBat.Items(0).SubItems(0).Text
Dim tt As String = testPath1
CType(catNewDB.ActiveConnection, ADODB.Connection).Close()
My.Computer.FileSystem.CopyDirectory(tt, testPath5, True)
My.Computer.FileSystem.CopyDirectory(tt, testPath2, True)
'**CBATCH LOG about databases created****************
file.WriteLine(String.Format("{0,5} {1,15} {2,15} {3,15}", Format(i + 1, "00000000") & ".mdb", rec1.ToString, rec2.ToString, batSz))
rec1 = rec2 + 1
rec2 = rec2
conn.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
conn.Open()
conn.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
Application.DoEvents()
Next
file.Close()
rec2 = 0
Application.DoEvents()
Next
Me.Close()
' Timer1.Start()
End If
Dim newpath As String = Form1.Dir_folder.Text & "\CBATCH"
If Not IO.Directory.Exists(newpath) Then
MkDir(newpath)
End If
conns = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + newpath + "\CardDeck.mdb")
If Not File.Exists(newpath + "\CardDeck.mdb") Then
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + newpath + "\CardDeck.mdb")
Dim cmdd As New OleDb.OleDbCommand("CREATE TABLE CardDeck( " & _
"RecNum int identity," & "ZipFile NVarchar(50)," & "DvdName NVarchar(50)," & "SeqNo1 NVarchar(50)," & "SeqNo2 NVarchar(50)," & _
"JobCode NVarchar(50)," & "ClientCode NVarchar(50)," & "TotalImages NVarchar(50)," & "TotalRecs NVarchar(50)," & "BatchCount NVarchar(50)," & "BatchDate NVarchar(50)," & "BatchSize NVarchar(50)," & "CONSTRAINT [pk_RecNum] PRIMARY KEY (RecNum)) ", conns)
conns.Open()
cmdd.ExecuteNonQuery()
conns.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
End If
For ast As Integer = 0 To Form1.SelBat.Items.Count - 1
Dim o As String
o = Form1.SelBat.Items(ast).SubItems(0).Text()
Dim y As String
Dim fPath As String = Form1.SelBat.Items(ast).SubItems(2).Text
Dim directory3 As String = Path.GetDirectoryName(fPath)
Dim split As String() = fPath.Split("\")
Dim exfldr As String = split(split.Length - 1)
y = exfldr
Dim count As String = Form1.SelBat.Items(ast).SubItems(1).Text
Dim newbatsize As String
newbatsize = Form1.BatSize.Text
Dim batchFolderNames As String = Form1.SelBat.Items(ast).SubItems(0).Text
Dim fldr As String = Form1.Dir_folder.Text & "\CBATCH"
Dim countOfMdb = Directory.GetFiles(fldr, "*.mdb", System.IO.SearchOption.AllDirectories).Length
Dim cmdB1 As String = "INSERT INTO [CardDeck] ([ZipFile],[DvdName],[SeqNo1],[SeqNo2],[JobCode],[ClientCode],[TotalImages],[TotalRecs],[BatchCount],[BatchDate],[BatchSize]) VALUES (@ZipFile, @DvdName, @SeqNo1, @SeqNo2, @JobCode, @ClientCode, @TotalImages, @TotalRecs, @BatchCount, @BatchDate, @BatchSize);"
Dim cmdG1 As New OleDb.OleDbCommand(cmdB1, conns)
cmdG1.Parameters.AddWithValue("@ZipFile", o)
cmdG1.Parameters.AddWithValue("@DvdName", y)
cmdG1.Parameters.AddWithValue("@SeqNo1", "00000001")
cmdG1.Parameters.AddWithValue("@SeqNo2", "00000001")
cmdG1.Parameters.AddWithValue("@JobCode", "Card Deck")
cmdG1.Parameters.AddWithValue("@ClientCode", "DDC OS")
cmdG1.Parameters.AddWithValue("@TotalImages", count)
cmdG1.Parameters.AddWithValue("@TotalRecs", count)
cmdG1.Parameters.AddWithValue("@BatchCount", countOfMdb)
cmdG1.Parameters.AddWithValue("@BatchDate", Format(Now, "f"))
cmdG1.Parameters.AddWithValue("@BatchSize", newbatsize)
conns.Open()
cmdG1.ExecuteNonQuery()
conns.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
IDfield.Clear()
Next
End If
End Sub
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Form1.ProgressBar1.Increment(1)
If Form1.ProgressBar1.Value = 25 Then
Form1.Label8.Text = "Started. Doing something"
ElseIf Form1.ProgressBar1.Value = 50 Then
Form1.Label8.Text = "Half Way There"
ElseIf Form1.ProgressBar1.Value = 75 Then
Form1.Label8.Text = "Working on it.. Almost Done!"
End If
If Form1.ProgressBar1.Value = Form1.ProgressBar1.Maximum Then
Timer1.Stop()
MsgBox("Complete!")
Form1.ProgressBar1.Value = 0
Form1.Label8.Text = "Idle"
Button1.Enabled = True '
If conns.State = ConnectionState.Open Then
conns.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
End If
If conn.State = ConnectionState.Open Then
conn.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
End If
If conctn.State = ConnectionState.Open Then
conctn.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
End If
End If
End Sub