Imports DigitalData.Modules.Logging Imports DigitalData.Modules.Database Imports System.IO Imports System.ComponentModel Imports GdPicture14 Imports DevExpress.DataAccess.Sql Public Class Form1 Dim MyLogger As LogConfig Dim Logger As Logger Dim MySQLSDB As MSSQLServer Dim MyFreigabebericht As String Dim MyStempel As String Dim MyOriginStamped As String Dim MyEndResult As String Dim DEBUG As Boolean = False Dim GDPICTURE_LICENSE As String Private _licenseManager As New GdPicture14.LicenseManager() Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load MyLogger = New LogConfig(LogConfig.PathType.CustomPath, Application.StartupPath & "\log", Nothing, "Digital Data", "WISAGErgReport") Logger = MyLogger.GetLogger() MySQLSDB = New MSSQLServer(MyLogger, My.Settings.CONF_SQLSERVER,) If MySQLSDB.DBInitialized = False Then MsgBox("Could not initialize database. Check the ConfigString My.Settings.CONF_SQLSERVER!", MsgBoxStyle.Critical) Me.Close() End If Dim oSql = "Select * from TBDD_3RD_PARTY_MODULES" Dim oDT As DataTable = MySQLSDB.GetDatatable(oSql) For Each oROW As DataRow In oDT.Rows If oROW.Item("NAME") = "GDPICTURE" Then Try GDPICTURE_LICENSE = oROW.Item("LICENSE") _licenseManager.RegisterKEY(GDPICTURE_LICENSE) Catch ex As Exception Logger.Error(ex) End Try End If Next Me.TimerMinute.Start() txtDocID.Text = My.Settings.MyDocID tsiGENKey.Text = "Generator-ID: " & My.Settings.GEN_ID End Sub Dim Run_Active As Boolean = False Private Sub TimerMinute_Tick(sender As Object, e As EventArgs) Handles TimerMinute.Tick Logger.Debug($"Tick---Hour: {My.Computer.Clock.LocalTime.Hour}") If DEBUG = True Then Exit Sub If Run_Active = True Then Exit Sub If My.Computer.Clock.LocalTime.Hour > 6 And My.Computer.Clock.LocalTime.Hour < 23 Then Try Dim oDT As DataTable = MySQLSDB.GetDatatable(My.Settings.oSQLGetDocId) If Not IsNothing(oDT) Then Dim oDocID As Long If oDT.Rows.Count > 0 Then For Each oRow As DataRow In oDT.Rows oDocID = oRow.Item(0).ToString Dim oInsert = $"INSERT INTO TBCUST_PM_RPT_2BCREATED (DocID,GEN_ID) VALUES ({oDocID},'{My.Settings.GEN_ID}')" MySQLSDB.ExecuteNonQuery(oInsert) Next Run_Active = True For Each oRow As DataRow In oDT.Rows oDocID = oRow.Item(0).ToString Dim oSQL = $"select count(*) from VWCUST_RPTPM_INV_RPT_STAMP_CHECK WHERE docID = {oDocID.ToString}" If MySQLSDB.GetScalarValue(oSQL) >= 1 Then Create_EB_Report(oDocID.ToString, 0) Else Logger.Info($"ATTENTION - No FinalFreigabe for DocID: {oDocID.ToString}") Dim oDELETE = $"DELETE FROM TBCUST_PM_RPT_2BCREATED WHERE DocID = {oDocID} AND GEN_ID = '{My.Settings.GEN_ID}'" MySQLSDB.ExecuteNonQuery(oDELETE) End If Next End If ToolStripStatusLabel1.Text = $"Last Run: {Now.ToString}" Run_Active = False End If Catch ex As Exception Run_Active = False End Try Else ToolStripStatusLabel1.Text = $"In Sleep Mode 19-06: {Now.ToString}" End If End Sub Sub Create_EB_Report(pDocID As String, ReportOption As Integer) MyFreigabebericht = "" MyStempel = "" MyOriginStamped = "" Logger.Info($"#### Trying to Create_Report for DocID: {pDocID}... ####") Dim errortext As String Try If DEBUG = True Then DocumentViewer1.DocumentSource = Nothing End If Catch ex As Exception If DEBUG = True Then MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in 1:") End If Logger.Error(ex) Exit Sub End Try Try Dim oReport As New XtraReport2() Dim oDatasource As SqlDataSource = oReport.DataSource Dim oSelectQuery As CustomSqlQuery = oDatasource.Queries(0) Dim oParameter As QueryParameter = oSelectQuery.Parameters(0) oParameter.Value = pDocID Try oReport.ReportPrintOptions.PrintOnEmptyDataSource = False oReport.FilterString = "DocID = " + pDocID 'report.ObjectDataSource1.Parameters(0).Value = Convert.ToInt64(TextBox1.Text) oReport.CreateDocument(False) If DEBUG = True Then DocumentViewer1.DocumentSource = oReport End If Catch ex As Exception Dim oUpdate = $"DELETE FROM TBCUST_PM_RPT_2BCREATED WHERE DocID = {pDocID} AND GEN_ID = '{My.Settings.GEN_ID}'" MySQLSDB.ExecuteNonQuery(oUpdate) If DEBUG = True Then MsgBox("Unexpected error in XtraReport2 Create", MsgBoxStyle.Critical, "Error in XtraReport2:") End If Logger.Warn($"Unexpected Error in XtraReport2 Create for DocID: {pDocID} - ERROR: {ex.Message}") Exit Sub End Try Dim oMyErgebnisbericht As String = Path.Combine(My.Settings.CONCAT_TEMPFolder, pDocID & "_ERPT.pdf") If File.Exists(oMyErgebnisbericht) Then Try File.Delete(oMyErgebnisbericht) Catch ex As Exception If DEBUG = True Then MsgBox($"Could not delete existing Reportfile: {oMyErgebnisbericht}", MsgBoxStyle.Critical) End If Exit Sub End Try End If oReport.ExportToPdf(oMyErgebnisbericht) If File.Exists(oMyErgebnisbericht) Then If ReportOption = 1 Then Exit Sub End If MyFreigabebericht = oMyErgebnisbericht Dim oOriginFile As String Dim osql = $"select dbo.FNDD_GET_WINDREAM_FILE_PATH ({pDocID},1)" oOriginFile = MySQLSDB.GetScalarValue(osql) If File.Exists(oOriginFile) = False Then Logger.Warn($"File {oOriginFile} is not existing. Check the pathconfiguration!") Exit Sub End If If Not IsNothing(oOriginFile) Then If File.Exists(My.Settings.Path2PDFTK) Then If Create_MyStempel(DEBUG, pDocID) = True Then 'Jetzt Stempeln des OriginakBeleges mit dem Stamp-Report Dim oStampedResult = CreateStampedPDF(oOriginFile, pDocID) If oStampedResult <> "" Then If Concat_Files(pDocID, oOriginFile) = True Then If DEBUG Then MsgBox($"The ResultReport has been created: {MyEndResult}", MsgBoxStyle.Information) End If If DEBUG = False Then Dim oInsert = $"INSERT INTO TBCUST_PM_RPT_CREATED (DocID) VALUES ({Convert.ToInt64(pDocID)})" MySQLSDB.ExecuteNonQuery(oInsert) End If Logger.Info($"#### Created Ergebnisbericht for [{pDocID}]! #####") Logger.Info($"") End If Else Logger.Info("!!CreateStampedPDF = False or ""!!") End If Else Logger.Info("!!Create_MyStempel = False!!") End If End If Else Logger.Warn("OriginFile is nothing!!") If DEBUG Then MsgBox("OriginFile is nothing", MsgBoxStyle.Critical) End If End If End If Catch ex As Exception Dim oUpdate = $"DELETE FROM TBCUST_PM_RPT_2BCREATED WHERE DocID = {pDocID} AND GEN_ID = '{My.Settings.GEN_ID}'" MySQLSDB.ExecuteNonQuery(oUpdate) errortext = "ex.message: " & ex.Message & vbNewLine & "ex.StackTrace: " & ex.StackTrace.ToString & vbNewLine & "ex.InnerException: " & ex.InnerException.Message If DEBUG = True Then MsgBox(errortext, MsgBoxStyle.Critical, "Error in 2:") Clipboard.SetText(errortext) End If Logger.Warn($"Unexpected Error in Create_Report for DocID: {pDocID} - ERROR: {ex.Message}") End Try End Sub Private Function Create_MyStempel(istest As Boolean, pDocID As String) MyStempel = "" Logger.Info($"Trying to create Stampreport for DocID: {pDocID}...") Dim errortext As String Try If istest = True Then DocumentViewer1.DocumentSource = Nothing End If Catch ex As Exception If istest = True Then MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in 1:") End If Logger.Error(ex) Return False End Try Try Dim myReport As New RptStampIcon() Dim oDatasource As SqlDataSource = myReport.DataSource Dim oSelectQuery As CustomSqlQuery = oDatasource.Queries(0) Dim oParameter As QueryParameter = oSelectQuery.Parameters(0) oParameter.Value = pDocID myReport.ReportPrintOptions.PrintOnEmptyDataSource = False myReport.FilterString = "DocID = " + pDocID 'report.ObjectDataSource1.Parameters(0).Value = Convert.ToInt64(TextBox1.Text) myReport.CreateDocument(False) If istest = True Then DocumentViewer1.DocumentSource = myReport End If Dim oTempStempel As String = Path.Combine(My.Settings.CONCAT_TEMPFolder, pDocID & "_Stamp.jpg") If File.Exists(oTempStempel) Then Try File.Delete(oTempStempel) Catch ex As Exception If istest = True Then MsgBox($"Could not delete existing Reportfile: {oTempStempel}", MsgBoxStyle.Critical) End If Return False End Try End If 'myReport.ExportToPdf(oTempStempel) Dim oExportOptions As New DevExpress.XtraPrinting.ImageExportOptions oExportOptions.Resolution = 400 oExportOptions.Format = Imaging.ImageFormat.Jpeg oExportOptions.ExportMode = DevExpress.XtraPrinting.ImageExportMode.SingleFile myReport.ExportToImage(oTempStempel, oExportOptions) If File.Exists(oTempStempel) Then MyStempel = oTempStempel Logger.Info($"...Stampreport created for DocID: {pDocID}!") If istest = True Then MsgBox($"Stampreport [{oTempStempel}] created for DocID [{pDocID}]!", MsgBoxStyle.Information) End If Return True Else MyStempel = "" Return False End If Catch ex As Exception errortext = "ex.message: " & ex.Message & vbNewLine & "ex.StackTrace: " & ex.StackTrace.ToString & vbNewLine & "ex.InnerException: " & ex.InnerException.Message Dim oUpdate = $"DELETE FROM TBCUST_PM_RPT_2BCREATED WHERE DocID = {pDocID} AND GEN_ID = '{My.Settings.GEN_ID}'" MySQLSDB.ExecuteNonQuery(oUpdate) If istest = True Then MsgBox(errortext, MsgBoxStyle.Critical, "Error in Create_StampReport:") Clipboard.SetText(errortext) End If Logger.Error(ex) Return False End Try End Function Private Function CreateStampedPDF(oOriginFile As String, pDocID As Integer) Try Logger.Info($"Trying to CreateStampedPDF for DocID: {pDocID}...") Logger.Debug($"Now in CreateStampedPDF") Dim myProcess As New Process Dim ProcID myProcess.StartInfo.FileName = My.Settings.Path2PDFTK myProcess.StartInfo.CreateNoWindow = False 'Dim oOutputFile As String 'oOutputFile = Path.GetFileNameWithoutExtension(oOriginFile) & My.Settings.Concatted_File_Suffix 'oOutputFile = Path.Combine(My.Settings.CONCAT_TEMPFolder, oOutputFile + "_BG.pdf") Dim oStampedOriginWithPicture As String oStampedOriginWithPicture = Path.GetFileNameWithoutExtension(oOriginFile) & My.Settings.Concatted_File_Suffix oStampedOriginWithPicture = Path.Combine(My.Settings.CONCAT_TEMPFolder, oStampedOriginWithPicture + "_StampedOriginal.pdf") 'Using oBackPDF As New GdPicturePDF ' If oBackPDF.LoadFromFile(oOriginFile, False) = GdPictureStatus.OK Then ' Using oOverlayPDF As New GdPicturePDF ' If oOverlayPDF.LoadFromFile(MyStempel, False) = GdPictureStatus.OK Then ' oBackPDF.DrawPage(oOverlayPDF, 1, 0, 0, oBackPDF.GetPageWidth, oBackPDF.GetPageHeight) ' oBackPDF.SaveToFile(oOutputFile1, True) ' End If ' End Using ' End If 'End Using Dim oGdPicturePDF As New GdPicturePDF() Dim oGdPictureImaging As New GdPictureImaging() 'Creating a new empty PDF document. If oGdPicturePDF.LoadFromFile(oOriginFile, False) = GdPictureStatus.OK Then Dim oYDistance = oGdPicturePDF.GetPageHeight 'Just to remind you that units are set to points and the origin is set to bottom left by default. 'Loading an image from a file. Dim imageID As Integer = oGdPictureImaging.CreateGdPictureImageFromFile(MyStempel) If oGdPictureImaging.GetStat() = GdPictureStatus.OK Then 'Adding an image as a resource into the PDF document - we decided not to draw the image in this moment. Dim imageResName As String = oGdPicturePDF.AddImageFromGdPictureImage(imageID, False, False) If System.IO.File.Exists(oStampedOriginWithPicture) Then System.IO.File.Delete(oStampedOriginWithPicture) End If Dim oStempelHEight = oGdPictureImaging.GetHeight(imageID) / 5 oYDistance = (oYDistance - oStempelHEight) - 10 'Drawing the image resource onto the current page and saving the PDF document. If (oGdPicturePDF.GetStat() <> GdPictureStatus.OK) OrElse (oGdPicturePDF.DrawImage(imageResName, My.Settings.XDistance, oYDistance, oGdPictureImaging.GetWidth(imageID) / 5, oGdPictureImaging.GetHeight(imageID) / 5) <> GdPictureStatus.OK) OrElse (oGdPicturePDF.SaveToFile(oStampedOriginWithPicture) <> GdPictureStatus.OK) Then Logger.Warn("The example has NOT been followed successfully. Error: " + oGdPicturePDF.GetStat().ToString()) 'MessageBox.Show("The example has NOT been followed successfully. Error: " + oGdPicturePDF.GetStat().ToString(), "Adding an image Example", MessageBoxButtons.OK, MessageBoxIcon.Error) Return "" End If 'Releasing the image. oGdPictureImaging.ReleaseGdPictureImage(imageID) Else Logger.Warn("The image can't be loaded. Error: " + oGdPictureImaging.GetStat().ToString()) Return "" 'MessageBox.Show("The image can't be loaded. Error: " + oGdPictureImaging.GetStat().ToString(), "Adding an image Example", MessageBoxButtons.OK, MessageBoxIcon.Error) End If oGdPicturePDF.CloseDocument() Else Logger.Warn("The new PDF document can't be created. Error: " + oGdPicturePDF.GetStat().ToString()) 'MessageBox.Show("The new PDF document can't be created. Error: " + oGdPicturePDF.GetStat().ToString(), "Adding an image Example", MessageBoxButtons.OK, MessageBoxIcon.Error) Return "" End If oGdPictureImaging.Dispose() oGdPicturePDF.Dispose() '###### OLD VERSION WITH PDF TK 'Dim _argument As String = """" & oOriginFile & """" & " background " & """" & MyStempel & """" _ ' & " output " & """" & oOutputFile & """" 'myProcess.StartInfo.Arguments = _argument 'Logger.Debug("Arguments: " & _argument) 'myProcess.Start() 'ProcID = myProcess.Id 'Dim p As Process 'p = Process.GetProcessById(ProcID) Dim sw As Stopwatch = New Stopwatch() 'sw.Start() 'Do While p.HasExited = False ' If sw.Elapsed.TotalSeconds = 30 Then ' Logger.Info("Still waiting (30 sec) for ending of process-id: " & ProcID.ToString) ' ElseIf sw.Elapsed.TotalMinutes = 1 Then ' Logger.Info("Still waiting (60 sec) for ending of process-id: " & ProcID.ToString & " - Exit now") ' Exit Do ' End If 'Loop Dim oSuccess As Boolean = True 'Logger.Debug("...process has exited: ") 'sw.Stop() sw.Start() Logger.Debug("Waiting for file: " & oStampedOriginWithPicture) Do While File.Exists(oStampedOriginWithPicture) = False If sw.Elapsed.TotalSeconds = 30 Then Logger.Info("ATTENTION: Still waiting (30 sec) for file: " & oStampedOriginWithPicture) ElseIf sw.Elapsed.TotalMinutes = 1 Then Logger.Info("ATTENTION: Still waiting (60 sec) for file: " & oStampedOriginWithPicture) oSuccess = False Exit Do End If Loop sw.Stop() Try File.Delete(MyStempel) Logger.Debug($"Deleted StempelFile [{MyStempel}]!") Catch ex As Exception Logger.Warn($"Could not delete reportfile after creating concatted file: {ex.Message}") End Try If oSuccess = True Then MyOriginStamped = oStampedOriginWithPicture Return oStampedOriginWithPicture Else MyOriginStamped = "" Return "" End If Catch ex As Exception Logger.Error(ex) '("Unexpected error: " & ex.Message, "clsProfil.Profil_Durchlauf(Concat Files to one pdf)") Return "" End Try End Function Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click My.Settings.Save() If txtDocID.Text = "" Then Exit Sub DEBUG = True MyLogger.Debug = True Create_EB_Report(txtDocID.Text, 0) End Sub Private Function Concat_Files(DocID As String, oOriginFile As String) Dim oConcatSuccessfull As Boolean = True Try Dim oFile2Concat As String = Path.GetFileNameWithoutExtension(oOriginFile) & My.Settings.Concatted_File_Suffix oFile2Concat = Path.Combine(My.Settings.Path_ConcattedFile, oFile2Concat + ".pdf") If File.Exists(My.Settings.Path2PDFTK) Then If File.Exists(oFile2Concat) Then Try File.Delete(oFile2Concat) Catch ex As Exception Logger.Warn($"Could not delete ConcattedFile {oFile2Concat}: {ex.Message}") Return False End Try End If Try Dim myProcess As New Process Dim ProcID myProcess.StartInfo.FileName = My.Settings.Path2PDFTK myProcess.StartInfo.CreateNoWindow = False Dim _argument As String = """" & MyOriginStamped & """" & " " & """" & MyFreigabebericht & """" myProcess.StartInfo.Arguments = _argument & " cat output " & oFile2Concat Logger.Debug("Arguments: " & _argument & " cat output " & oFile2Concat) myProcess.Start() ProcID = myProcess.Id Dim p As Process p = Process.GetProcessById(ProcID) Dim sw As Stopwatch = New Stopwatch() sw.Start() Do While p.HasExited = False If sw.Elapsed.TotalSeconds = 30 Then Logger.Info("Still waiting (30 sec) for ending of process-id: " & ProcID.ToString) ElseIf sw.Elapsed.TotalMinutes = 1 Then Logger.Info("Still waiting (60 sec) for ending of process-id: " & ProcID.ToString & " - Exit now") Exit Do End If Loop Logger.Debug("...process has exited: ") sw.Stop() Logger.Debug("Waiting for file: " & oFile2Concat) sw.Start() Do While File.Exists(oFile2Concat) = False If sw.Elapsed.TotalSeconds = 30 Then Logger.Info("ATTENTION: Still waiting (30 sec) for file: " & oFile2Concat) ElseIf sw.Elapsed.TotalMinutes = 1 Then Logger.Info("ATTENTION: Still waiting (60 sec) for file: " & oFile2Concat) oConcatSuccessfull = False Exit Do End If Loop sw.Stop() If oConcatSuccessfull = True Then MyEndResult = oFile2Concat End If Try File.Delete(MyOriginStamped) Logger.Debug($"Deleted file [{MyOriginStamped}]!") File.Delete(MyFreigabebericht) Logger.Debug($"Deleted file [{MyFreigabebericht}]!") Catch ex As Exception Logger.Warn($"Could not delete reportfile after creating concatted file: {ex.Message}") End Try Return oConcatSuccessfull Catch ex As Exception Logger.Error(ex) '("Unexpected error: " & ex.Message, "clsProfil.Profil_Durchlauf(Concat Files to one pdf)") Return False End Try Else Logger.Warn("pdftk is not existing") Return False End If Catch ex As Exception End Try End Function Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click If txtDocID.Text = "" Then Exit Sub My.Settings.Save() Create_MyStempel(True, txtDocID.Text) End Sub Private Sub Form1_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing My.Settings.Save() End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim myProcess As New Process Dim ProcID myProcess.StartInfo.FileName = My.Settings.Path2PDFTK myProcess.StartInfo.CreateNoWindow = True Dim _argument As String = txtPDFTKRun.Text myProcess.StartInfo.Arguments = _argument Logger.Debug("Arguments: " & _argument) myProcess.Start() ProcID = myProcess.Id Dim p As Process p = Process.GetProcessById(ProcID) Dim sw As Stopwatch = New Stopwatch() sw.Start() Do While p.HasExited = False If sw.Elapsed.TotalSeconds = 30 Then Logger.Info("Still waiting (30 sec) for ending of process-id: " & ProcID.ToString) ElseIf sw.Elapsed.TotalMinutes = 1 Then Logger.Info("Still waiting (60 sec) for ending of process-id: " & ProcID.ToString & " - Exit now") Exit Do End If Loop Logger.Debug("...process has exited: ") sw.Stop() End Sub Private Sub ToolStripButton3_Click(sender As Object, e As EventArgs) Handles ToolStripButton3.Click If ToolStripButton3.CheckState = CheckState.Checked Then DEBUG = True ToolStripButton3.BackColor = Color.Yellow MyLogger.Debug = True Me.Text = "Report-Generator (beta) in DebugMode - all Automatic stopped" Else DEBUG = False MyLogger.Debug = False ToolStripButton3.BackColor = Color.WhiteSmoke Me.Text = "Report-Generator (beta) - AutoMode" End If If My.Settings.CONF_SQLSERVER.Contains("_TEST") Then Me.Text &= " - TESTSystem" End If My.Settings.Save() End Sub Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click If txtDocID.Text = "" Then Exit Sub My.Settings.Save() DEBUG = True MyLogger.Debug = True Create_EB_Report(txtDocID.Text, 1) End Sub Private Sub ToolStripButton5_Click(sender As Object, e As EventArgs) Dim report As New RpStampTable() report.ReportPrintOptions.PrintOnEmptyDataSource = False report.FilterString = "DocID = " + txtDocID.Text 'report.DocID.Value = txtDocID.Text 'report.ObjectDataSource1.Parameters(0).Value = Convert.ToInt64(TextBox1.Text) report.CreateDocument(False) If DEBUG = True Then DocumentViewer1.DocumentSource = report End If End Sub Private Sub ToolStripButton6_Click(sender As Object, e As EventArgs) Handles ToolStripButton6.Click Dim report As New RptStampIcon() Dim oDatasource As SqlDataSource = report.DataSource Dim oSelectQuery As CustomSqlQuery = oDatasource.Queries(0) Dim oParameter As QueryParameter = oSelectQuery.Parameters(0) oParameter.Value = txtDocID.Text report.ReportPrintOptions.PrintOnEmptyDataSource = False report.FilterString = "DocID = " + txtDocID.Text 'report.DocID.Value = txtDocID.Text 'report.ObjectDataSource1.Parameters(0).Value = Convert.ToInt64(TextBox1.Text) report.CreateDocument(False) If DEBUG = True Then DocumentViewer1.DocumentSource = report End If End Sub End Class