mardi 19 juillet 2016

Crystal Reports conditional field

I have a VB.net project for a company to be able to log the contracts of the people they deal with, print reminders/invoices etc.

I have a window where they can print the invoice for the contract from, and it includes the calculated VAT rate. However, there are some companies where the VAT is not added on, due to the type of company/contract.

I have added a set of radio buttons , one saying 'Yes', one saying 'No', and put an IF statement into my subroutine to load the invoice. Is there a way to change this code so that it only displays VAT rate if rbtnYes.Checked = True?

 Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles cmdRemind.Click

    Dim dt As New DataTable
    Dim ca As OleDb.OleDbDataAdapter
    Dim cs As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    Dim renAd As OleDb.OleDbDataAdapter
    Dim renSet As New DataSet

    Dim cmd As New OleDb.OleDbCommand

    If rbtnYes.Checked = False And rbtnNo.Checked = False Then
        MsgBox("Please select VAT option")
    Else

        Try
            sql = "ALTER TABLE tblContracts ADD DateSent date"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
        End Try

        Try
            sql = "ALTER TABLE tblContracts ADD TimeSent date"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
        End Try

        Try
            sql = "ALTER TABLE tblContracts ADD ReminderName varchar(255)"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
        End Try

        Try
            sql = "ALTER TABLE tblContracts ADD ReminderEmail varchar(255)"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
        End Try

        sql = "UPDATE tblContracts SET DateSent = @date, TimeSent = @time WHERE ContractID = @cid"
        cmd = New OleDb.OleDbCommand(sql, con)
        cmd.Parameters.AddWithValue("@date", Today.Date)
        cmd.Parameters.AddWithValue("@time", Now.TimeOfDay)
        cmd.Parameters.AddWithValue("@cid", selected)
        cmd.ExecuteNonQuery()

        Try
            sql = "UPDATE tblContracts SET ReminderName = @rname WHERE CustAcc = @custacc"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.Parameters.AddWithValue("@rname", txtName.Text)
            cmd.Parameters.AddWithValue("@custacc", accCode)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
        End Try

        Try
            sql = "SELECT Name, Address1, Address2, Address3, Address4, Address5, Postcode, Telephone, Fax, Email FROM Ledger WHERE Account_Code = '" & cmbName.Text & "';"
            da = New OleDb.OleDbDataAdapter(sql, acccon)
            ds = New DataSet
            da.Fill(ds)
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
            MsgBox("Failed to retrieve accounts set, refer to error log")
        End Try

        Try
            sql = "SELECT * FROM tblContracts WHERE ContractID = " & selected & ";"
            ca = New OleDb.OleDbDataAdapter(sql, con)
            cs = New DataSet
            ca.Fill(cs)
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
            MsgBox("Failed to retrieve contract set, refer to error log")
        End Try

        Try
            sql = "SELECT * FROM tblContractText"
            renAd = New OleDb.OleDbDataAdapter(sql, con)
            renSet = New DataSet
            renAd.Fill(renSet)
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
            MsgBox("Failed to retrieve contract text, refer to error log")
        End Try

        Try
            Dim printset As New DataSet
            printset = cs.Copy
            dt.Clear()
            dt = printset.Tables(0)

            obj = New ReportDocument
            Dim dest As String = readIni("REPORTS", directorypath & "connectionpaths.ini") & "Invoice.rpt"
            obj.Load(dest)

            Dim info As CrystalDecisions.Shared.TableLogOnInfo
            info = New CrystalDecisions.Shared.TableLogOnInfo()

            info.ConnectionInfo.DatabaseName = ""
            info.ConnectionInfo.ServerName = readIni("CONTRACTSTRING", directorypath & "connectionpaths.ini")
            info.ConnectionInfo.Password = ""
            info.ConnectionInfo.UserID = ""

            obj.Database.Tables(0).ApplyLogOnInfo(info)
        Catch ex As Exception
            MsgBox("Failed to apply log-on info, refer to error log.")
            errorLog(ex.Message, ex.StackTrace)
        End Try

        Try
            Dim controlset As New DataSet
            Dim controlad As OleDb.OleDbDataAdapter

            sql = "SELECT * FROM Control"
            controlad = New OleDb.OleDbDataAdapter(sql, maintcon)
            controlad.Fill(controlset)

            fillObject("Address1", ds.Tables(0).Rows(0).Item("Address1") & "", obj)
            fillObject("Address2", ds.Tables(0).Rows(0).Item("Address2") & "", obj)
            fillObject("Address3", ds.Tables(0).Rows(0).Item("Address3") & "", obj)
            fillObject("Address4", ds.Tables(0).Rows(0).Item("Address4") & "", obj)
            fillObject("Postcode", ds.Tables(0).Rows(0).Item("Postcode") & "", obj)
            fillObject("Email", ds.Tables(0).Rows(0).Item("Email") & "", obj)
            fillObject("Name", ds.Tables(0).Rows(0).Item("Name") & "", obj)
            fillObject("Tele", ds.Tables(0).Rows(0).Item("Telephone") & "", obj)
            fillObject("Fax", ds.Tables(0).Rows(0).Item("Fax") & "", obj)
            fillObject("rName", "Dear " & txtName.Text & ",", obj)
            fillObject("VatReg", controlset.Tables(0).Rows(0).Item("VAT_Registration"), obj)
            fillObject("toName", txtName.Text, obj)

        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
            MsgBox("Failed to fill accounts information in report, refer to error log")
        End Try

        obj.RecordSelectionFormula = "{tblContracts.ContractID} = " & cs.Tables(0).Rows(0).Item("ContractID")

        Try
            a = cs.Tables(0).Rows(0).Item("CustAcc") & ""
            b = cs.Tables(0).Rows(0).Item("ContractID") & ""
            c = ds.Tables(0).Rows(0).Item("Email") & ""
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
            MsgBox("Failed to set AccountCode, ContractID and E-mail, refer to error log")
        End Try

        Try
            Dim f As frmReports
            f = New frmReports(con, acccon, a, b, txtEmail.Text, maintcon, obj, , , txtName.Text, cs.Tables(0).Rows(0).Item("Fee"), cs.Tables(0).Rows(0).Item("DateRenew"), , "Support Contract", , , False)
            f.Show()
        Catch ex As Exception
            MsgBox("Unable to load report, refer to error log")
            errorLog(ex.Message, ex.StackTrace)
        End Try

        Try
            updateDates()
        Catch ex As Exception
            errorLog(ex.Message, ex.StackTrace)
        End Try
    End If
End Sub

Aucun commentaire:

Enregistrer un commentaire