Exercise 3: Create Form

Objectives: Create A Form to load A/R Invoice, A/R Debit Note, A/R Credit Note and A/R Payment into one Data Grid.

1. Open the plug-in project “Tutorial.PRG201”

2. Amend the plug-in and project version to “1.0.3”

3. Right-Click on “Tutorial.PRG101” (Project), and Select -> Add | Windows Forms…

Type the form name as “FormARTransData.cs”

4. Add MenuItemAttribute before the partial class of FormARTransData

[BCE.AutoCount.PlugIn.MenuItem("AR Transactions Enquiry", MenuOrder = 10)]

5. Insert “BCE.Data.DBSetting dbSetting” at the constructor’s argument

6. Add a Panel Control onto FormARTransData

[Properties]

(Name) = panelHeader

Dock = Top

7. Add a DataGridView at below of the panelHeader

[Properties]

(Name) = dataGridMain

Dock = Fill

8. Add a Button on panelHeader

[Properties]

(Name) = btnRefresh

Text = "Refresh"

9. Create Event for button [Refresh]

Double clicks [Refresh] button.
In the event of btnRefresh_Click, insert a method of LoadARData()

private void btnRefresh_Click(object sender, EventArgs e)
{
    LoadARData();
}

private void LoadARData()
{
    //Algorithm to load data
}

10. Before proceed to load data, DBSetting object is required for the connection to SQL Server.

Create BCE.Data.DBSetting object to store dbSetting which is passed from the AutoCount Accounting.

private BCE.Data.DBSetting mydbset;

public FormARTransData(BCE.Data.DBSetting dbSetting)
{
    InitializeComponent();

    mydbset = dbSetting;
}

11. Prepare SQL Query to select data to load Copy below string and paste in the method of LoadARData()

string sqlSelect = "SELECT"
    + " 'RI' AS DocType, DebtorCode, DocNo, DocDate, [Description]"
    + ",SalesAgent, LocalNetTotal AS LocalAmount,"
    + "(Outstanding * CurrencyRate) AS LocalBalance"
    + " FROM ARINVOICE"
    + " WHERE Cancelled = 'F'"
    + " UNION"
    + " SELECT 'RD' AS DocType, DebtorCode, DocNo, DocDate, [Description]"
    + ",SalesAgent, LocalNetTotal AS LocalAmount"
    + ",Outstanding AS LocalBalance"
    + " FROM ARDN"
    + " WHERE Cancelled = 'F'"
    + " UNION"
    + " SELECT 'RC' AS DocType, DebtorCode, DocNo, DocDate, [Description]"
    + ",'' AS SalesAgent, LocalNetTotal AS LocalAmount"
    + ",(NetTotal - KnockOffAmt + RefundAmt) * CurrencyRate AS LocalBalance"
    + " FROM ARCN"
    + " WHERE Cancelled = 'F'"
    + " UNION"
    + " SELECT 'RP' AS DocType, DebtorCode, DocNo, DocDate, [Description]"
    + ",'' AS SalesAgent, LocalPaymentAmt AS LocalAmount"
    + ",LocalUnappliedAmount AS LocalBalance"
    + " FROM ARPAYMENT"
    + " WHERE Cancelled = 'F'";

12. Access SQL Server using mydbset.

Update LoadARData() method, to load data into DataTable.

private void LoadARData()
{
    //Paste sqlSelect here
    DataTable tableARTrans = mydbset.GetDataTable(sqlSelect, false);
    dataGridMain.DataSource = tableARTrans;
}