working with Payroll Arrays :/

[trend]

Hello, I have an array returned with these fields:

 

Name, AmountDue, TypeID, Description

 

The array is (100,1) and string type

 

My problem is.. Some of the people in the array are in there more than once.. (For example they get a bonus on top of their hourly pay).

 

How can I get an array with each entry having a unique Name.. and where the AmountDue is the combination of all money owed to them?

 

example:

OrgArray(100,1)

 

OrgArray(0,0) = "Jay"

OrgArray(0,1) = "10.00"

OrgArray(1,0) = "Jay"

OrgArray(1,1) = "1.00"

OrgArray(2,0) = "David"

OrgArray(2,1) = "10.00"

OrgArray(3,0) = "Erin"

OrgArray(3,1) = "10.00"

OrgArray(4,0) = "David"

OrgArray(4,1) = "1.00"

 

 

NewArray(100,1) should be something like:

"Jay" = NewArray(0,0)

"11.00" = NewArray(0,1)

"David" = NewArray(1,0)

"11.00" = NewArray(1,1)

"Erin" = NewArray(2,0)

"10.00" = NewArray(2,1)

 

Any ideas?

 

thanks

[teixeira]

Hi,

 

I think you could use a DataTable inspite of an array.

DataTable and DataView gives you all the tools you need to sort and sum the values that you're using now in the array.

 

try something linke this, its in C# but you'll understant surely:

 

           DataTable dt = new DataTable("Employees");

           //Columns
           dt.Columns.Add("name",typeof(System.String));
           dt.Columns.Add("amount",typeof(System.String));

           //Fill datatable with some 10 records
           for (int i = 0; i < 5; i++)
           {
               DataRow dr = dt.NewRow();
               dr["name"] = String.Format("Employee{0}",i);
               dr["amount"] = "10";

               //Add datarow to the datatable
               dt.Rows.Add(dr);
           }

           for (int j = 0; j < 5; j++)
           {
               DataRow dr = dt.NewRow();
               dr["name"] = String.Format("Employee{0}", i);
               dr["amount"] = "15";

               //Add datarow to the datatable
               dt.Rows.Add(dr);
           }

           //Here you sort the DataTable from emplyee name and you make a sum of its values
           DataView dvForSort = new DataView(dt, null, "name ASC", DataViewRowState.CurrentRows);

//Here you should write some code for making the sum of the field "amount"

 

I hope this helps you solving your issue,

Greetings

Tiago Teixeira

[PlausiblyDamp]

It would be far esaier to work with if you created a class or structure to hold a person's details and then created an array of them rather than dealing with Arrays of Multi-Dimensional arrays...

[trend]

It would be far esaier to work with if you created a class or structure to hold a person's details and then created an array of them rather than dealing with Arrays of Multi-Dimensional arrays...

 

Code example please.. Because I am completely clueless on what you are saying to do :/

 

thanks :)

[trend]

I am guessing.. I need to create a new array from the DT returns UNIQUE 'Names'

And then from there.. loop through the array saying.. select all records where Name = Name(x) perhaps?

[trend]

Hmm.. I will pay someone to write the vb.net code.. Because I am honestly lost :/

[Goggy]

just some tinkering.....

 

Public Class Form1
   Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

   Public Sub New()
       MyBase.New()

       'This call is required by the Windows Form Designer.
       InitializeComponent()

       'Add any initialization after the InitializeComponent() call

   End Sub

   'Form overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
       If disposing Then
           If Not (components Is Nothing) Then
               components.Dispose()
           End If
       End If
       MyBase.Dispose(disposing)
   End Sub

   'Required by the Windows Form Designer
   Private components As System.ComponentModel.IContainer

   'NOTE: The following procedure is required by the Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
       '
       'Form1
       '
       Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
       Me.ClientSize = New System.Drawing.Size(292, 266)
       Me.Name = "Form1"
       Me.Text = "Form1"

   End Sub

#End Region

   Dim Coll As New Collection
   Public Structure Person
       Public Name As String
       Public Cash As Int32
   End Structure

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

       Dim OrgArray(100, 1) As String
       Dim RetArray(0, 0) As String
       Dim InstanceOfPerson As Person

       'Notice i have replaced "." with "," for the conversion from string to number to work properly.
       OrgArray(0, 0) = "Jay"
       OrgArray(0, 1) = "10,00"
       OrgArray(1, 0) = "Jay"
       OrgArray(1, 1) = "1,00"
       OrgArray(2, 0) = "David"
       OrgArray(2, 1) = "10,00"
       OrgArray(3, 0) = "Erin"
       OrgArray(3, 1) = "10,00"
       OrgArray(4, 0) = "David"
       OrgArray(4, 1) = "1,00"

       'Make a collection to hold all the people and add the totals.
       For i As Int16 = 0 To UBound(OrgArray, 1)
           If OrgArray(i, 0) <> "" Then
               With InstanceOfPerson
                   .Name = OrgArray(i, 0)
                   .Cash = CInt(OrgArray(i, 1))
               End With
               If Not ItemExistInCollection(InstanceOfPerson.Name) Then
                   Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
               Else
                   InstanceOfPerson = Coll(OrgArray(i, 0))
                   InstanceOfPerson.Cash = InstanceOfPerson.Cash + CInt(OrgArray(i, 1))
                   Coll.Remove(InstanceOfPerson.Name)
                   Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
               End If
           End If
       Next

       'if where keep going to work with the collection all we have to do to loop tru then is
       For Each InstanceOfPerson In Coll
           Debug.WriteLine(InstanceOfPerson.Name & InstanceOfPerson.Cash)
       Next

   End Sub

   Private Function ItemExistInCollection(ByVal Item As String) As Boolean
       On Error Resume Next

       Dim Ret As Object
       Ret = Coll(Item)

       If Ret <> "" Then
           Return True
       End If

       Return False

   End Function
End Class

[trend]

Awesome!

 

Code works great.

 

One thing though. My db query returns amount due (money) in string in format XX.XX or just XX (so not using ",")

 

And I am not 100percent sure if (?) i need to replace the . with , .. Because code:

 

Dim Coll As New Collection
   Public Structure Person
       Public Name As String
       Public Cash As Double
   End Structure

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


       Dim RetArray(0, 0) As String
       Dim InstanceOfPerson As Person


       Dim OrgArray(100, 1) As String
       OrgArray = LookupPendingAccounts("get")


       Dim x As Integer = 0
       While OrgArray(x, 0) <> ""
           TextBox1.Text = TextBox1.Text & vbNewLine & OrgArray(x, 0) & " " & OrgArray(x, 1)
           x = x + 1
       End While
       TextBox1.Text = TextBox1.Text & vbNewLine & "---------------" & vbNewLine

       'Make a collection to hold all the people and add the totals.
       For i As Int16 = 0 To UBound(OrgArray, 1)
           If OrgArray(i, 0) <> "" Then
               With InstanceOfPerson
                   .Name = OrgArray(i, 0)
                   .Cash = CType(OrgArray(i, 1), Double)
               End With
               If Not ItemExistInCollection(InstanceOfPerson.Name) Then
                   Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
               Else
                   InstanceOfPerson = Coll(OrgArray(i, 0))
                   InstanceOfPerson.Cash = InstanceOfPerson.Cash + CType(OrgArray(i, 1), Double)
                   Coll.Remove(InstanceOfPerson.Name)
                   Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
               End If
           End If
       Next

       'if where keep going to work with the collection all we have to do to loop tru then is
       For Each InstanceOfPerson In Coll
           ' Debug.WriteLine(InstanceOfPerson.Name & InstanceOfPerson.Cash)
           TextBox1.Text = TextBox1.Text & vbNewLine & InstanceOfPerson.Name & " " & InstanceOfPerson.Cash
       Next

   End Sub

   Private Function ItemExistInCollection(ByVal Item As String) As Boolean
       On Error Resume Next

       Dim Ret As Object
       Ret = Coll(Item)

       If Ret <> "" Then
           Return True
       End If

       Return False

   End Function


 

 

Seems to handle the addition fine. (As soon as I changed int32 values to doubles ).

 

What do you think? Do I really need to regex and replace the . with , then convert to int32? or is using a double just as fine?

 

thanks sooo much for the help :) If I owe you anything , let me know

 

thanks

Edited July 14, 2006 by trend

[Goggy]

:-)

 

glad to help u my friend....

and if it works....it works....so feel free to use the double ;)

 

best regards Goggy

[PlausiblyDamp]

The "." or "," is down to localisation - some countries use , as the thousands sperator and . for decimals other countries use them reversed. You could change the lines similar to

.Cash = CType(OrgArray(i, 1), Double)

to be more explicit by using the .Parse method

.Cash = Double.Parse(OrgArray(i,1), System.Globalization.NumberStyles.Currency)
'or to be more exacting still you can provide an explicit culture to use for parsing
.Cash = Double.Parse(OrgArray(i,1), System.Globalization.NumberStyles.Currency, System.Globalization.CultureInfo.CurrentUICulture)

 

 

You could simplify the code some more by using one of the classes from System.Collections such as ArrayList rather than just the Collection class.

Collection seems to really be there for VB6 compatability and isn't as functional as the others, e.g. Arraylist provides an .IndexOf method to check for existing entries. Using a HashTable would allow you to store a person and reference it by the person's name directly...