We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
  1. Home
  2. Forum
  3. WinForms
  4. when TableControlCurrentCellEditingComplete is fired how to set focus to next cell of next row and same column in child table in gridgrouping control of syncfusion

when TableControlCurrentCellEditingComplete is fired how to set focus to next cell of next row and same column in child table in gridgrouping control of syncfusion

Hi,
I am using Gridgrouping control.
I have Parent-Child Relationship in the control.
I want to change values in cell of  rows values in child table.
when TableControlCurrentCellEditingComplete is fired 
And down arrow key is pressed how to set focus to next cell of next row and same column  in child table in gridgrouping control of syncfusion

Thank you and Regards,
Supriya

4 Replies
MK Muthukumar Kalyanasundaram Syncfusion Team January 10, 2015 05:20 PM UTC

Hi Supriya,

 

Thank you for your interest in Syncfusion products.

 

If you want to set focus to next row of same column index in child table in gridgroupingcontrol, you can use the “TableControlKeyDown” event and focus particular cell by using “MoveTo()”method and set the “GridSetCurrentCellOptions” as SetFocus. Please refer the below code,

 

Code:

this.gridGroupingControl1.TableControlKeyDown += new GridTableControlKeyEventHandler(gridGroupingControl1_TableControlKeyDown);

 

void gridGroupingControl1_TableControlKeyDown(object sender, GridTableControlKeyEventArgs e)

{

GridCurrentCell cc = e.TableControl.CurrentCell;

GridTable tablechild = this.gridGroupingControl1.GetTable("Orders");

if (tablechild.CurrentRecord != null)

{

if (e.Inner.KeyCode == Keys.Down)

{

e.TableControl.CurrentCell.MoveTo(GridRangeInfo.Cell(cc.RowIndex + 1, cc.ColIndex), GridSetCurrentCellOptions.SetFocus); // focus to next row in child table

}

}

MessageBox.Show("Table RowIndex : " + tablechild.CurrentRecord.GetRowIndex());

e.Inner.Handled = true;

}

 

Please let us know if you have any concern.

 

Regards,

Muthukumar K


SV Supriya Varne January 12, 2015 07:59 AM UTC

Hi,
Sir Solution not working properly.
Please give me another solution.
My Code is
Public Sub BindSyncfusionGrid(ByVal dtset1 As DataSet)
Try
'Dim dr As SqlDataReader
Dim dttax As New DataTable
Dim index As Integer
If (dtset1.Tables(0).Rows.Count > 0) Then
'for adding the column Availabel RetQty in bought item
GridGroupingControl1.DataSource = Nothing
If Not dtset1.Tables(0).Columns.Contains("AvailabelRetQty") Then
index = dtset1.Tables(0).Columns("AlreadyReturnedQuantity").Ordinal
dtset1.Tables(0).Columns.Add("AvailabelRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
Dim invqty, alretqty As Decimal
invqty = dtset1.Tables(0).Rows(i)("Qty").ToString()
alretqty = dtset1.Tables(0).Rows(i)("AlreadyReturnedQuantity").ToString()
dtset1.Tables(0).Rows(i)("AvailabelRetQty") = invqty - alretqty
Next
End If
'for adding the column Current RetQty in bought item
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(0).Columns.Contains("CurrentRetQty") Then
index = dtset1.Tables(0).Columns("AvailabelRetQty").Ordinal
dtset1.Tables(0).Columns.Add("CurrentRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("CurrentRetQty") = dtset1.Tables(0).Rows(i)("AvailabelRetQty")
Next
End If
End If
'for adding the column SetAmt in bought item
If Not dtset1.Tables(0).Columns.Contains("SetAmt") Then
index = dtset1.Tables(0).Columns("Amt").Ordinal
dtset1.Tables(0).Columns.Add("SetAmt").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetAmt") = dtset1.Tables(0).Rows(i)("Amt").ToString()
Next
End If
'for adding the column SetAssAmt in bought item
If Not dtset1.Tables(0).Columns.Contains("SetAssAmt") Then
index = dtset1.Tables(0).Columns("AssessableAmt").Ordinal
dtset1.Tables(0).Columns.Add("SetAssAmt").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetAssAmt") = dtset1.Tables(0).Rows(i)("AssessableAmt").ToString()
Next
End If
'for adding the column SetTotalTax in bought item
If Not dtset1.Tables(0).Columns.Contains("SetTotalTax") Then
index = dtset1.Tables(0).Columns("TotalTax").Ordinal
dtset1.Tables(0).Columns.Add("SetTotalTax").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetTotalTax") = dtset1.Tables(0).Rows(i)("TotalTax").ToString()
Next
End If
'dtset1.Tables(1).Columns.Add("SetQty")
'For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
' Dim setqty, qty, sqty As Decimal
' Dim itemcode As String
' Dim Setdt As New DataTable
' itemcode = dtset1.Tables(0).Rows(i)("ItemCode").ToString()
' If isSet11(itemcode) Then
' qty = dtset1.Tables(0).Rows(i)("Qty").ToString()
' For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' sqty = dtset1.Tables(1).Rows(j)("Qty").ToString()
' setqty = qty * sqty
' If (itemcode = dtset1.Tables(1).Rows(j)("SetsID").ToString()) Then
' dtset1.Tables(1).Rows(j)("SetQty") = setqty
' End If
' Next
' End If
'Next
'for adding the column Availabel RetQty in bought items of set item
If Not dtset1.Tables(1).Columns.Contains("AvailabelRetQty") Then
index = dtset1.Tables(1).Columns("AlreadyReturnedQuantity").Ordinal
dtset1.Tables(1).Columns.Add("AvailabelRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
Dim invqty, alretqty As Decimal
invqty = dtset1.Tables(1).Rows(i)("Qty").ToString()
alretqty = dtset1.Tables(1).Rows(i)("AlreadyReturnedQuantity").ToString()
dtset1.Tables(1).Rows(i)("AvailabelRetQty") = invqty - alretqty
Next
End If
'for adding the column Current RetQty in bought items of setitem
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(1).Columns.Contains("CurrentRetQty") Then
index = dtset1.Tables(1).Columns("AvailabelRetQty").Ordinal
dtset1.Tables(1).Columns.Add("CurrentRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
dtset1.Tables(1).Rows(i)("CurrentRetQty") = dtset1.Tables(1).Rows(i)("AvailabelRetQty")
Next
End If
End If
''for adding the column amt in bought items of set items
'If Not dtset1.Tables(1).Columns.Contains("Amt") Then
' dtset1.Tables(1).Columns.Add("Amt")
' For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' dtset1.Tables(1).Rows(i)("Amt") = 0.0
' Next
'End If
''for adding the column Assessable amt in bought items of set items
'If Not dtset1.Tables(1).Columns.Contains("AssessableAmt") Then
' dtset1.Tables(1).Columns.Add("AssessableAmt")
' For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' dtset1.Tables(1).Rows(i)("AssessableAmt") = 0.0
' Next
'End If
''for adding the column TotalTax in bought items of set items
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(1).Columns.Contains("TotalTax") Then
dtset1.Tables(1).Columns.Add("TotalTax")
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
dtset1.Tables(1).Rows(i)("TotalTax") = 0.0
Next
End If
End If
Dim parentToChildRelationDescriptor As New Syncfusion.Windows.Forms.Grid.Grouping.GridRelationDescriptor()
GridGroupingControl1.DataSource = Nothing
parentToChildRelationDescriptor.ChildTableName = "SetItems"
parentToChildRelationDescriptor.RelationKind = Syncfusion.Grouping.RelationKind.RelatedMasterDetails
parentToChildRelationDescriptor.RelationKeys.Add("ItemCode", "SetItem")
GridGroupingControl1.TableDescriptor.Relations.Add(parentToChildRelationDescriptor)
GridGroupingControl1.Engine.SourceListSet.Add("ItemMaster", dtset1.Tables(0))
GridGroupingControl1.Engine.SourceListSet.Add("SetItems", dtset1.Tables(1))
GridGroupingControl1.DataSource = dtset1.Tables(0)
' parentToChildRelationDescriptor.ChildTableDescriptor.Columns.AddRange(New GridColumnDescriptor() {New GridColumnDescriptor("BoughtItem"), New GridColumnDescriptor("ItemName"), New GridColumnDescriptor("Qty"), New GridColumnDescriptor("AlreadyReturnedQuantity"), New GridColumnDescriptor("AvailabelRetQty"), New GridColumnDescriptor("CurrentRetQty"), New GridColumnDescriptor("Rate"), New GridColumnDescriptor("Disc"), New GridColumnDescriptor("adddisc"), New GridColumnDescriptor("Amt"), New GridColumnDescriptor("AssessableAmt"), New GridColumnDescriptor("TotalTax")})
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetAssAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetTotalTax")
If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("CurName")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvAssAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvTaxDetails")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvTotalTax")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
End If
'GridGroupingControl1.TableModel.HideCols("TaxDetails") = True
'GridGroupingControl1.TableModel.HideCols("SetAssAmt") = True
'GridGroupingControl1.TableModel.HideCols("SetAmt") = True
'GridGroupingControl1.TableModel.HideCols("SetTotalTax") = True
'If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
' GridGroupingControl1.TableModel.HideCols("CurName") = True
'End If
GridGroupingControl1.TableDescriptor.AllowNew = False
parentToChildRelationDescriptor.ChildTableDescriptor.AllowNew = False
GridGroupingControl1.TableDescriptor.Columns("itmgroupcd").ReadOnly = True
'GridGroupingControl1.TableDescriptor.Columns("SrNo").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ItemCode").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ItemName").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Qty").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AlreadyReturnedQuantity").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Rate").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Amt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AssessableAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Unit").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("TaxDetails").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetAssAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetTotalTax").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AvailabelRetQty").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("TotalTax").ReadOnly = True
Dim TColIndex, totcolindex As Integer
Dim taxname As String
TColIndex = dtset1.Tables(0).Columns("TaxDetails").Ordinal
totcolindex = dtset1.Tables(0).Columns("TotalTax").Ordinal - 1
For i As Integer = TColIndex + 1 To totcolindex
taxname = dtset1.Tables(0).Columns(i).ColumnName
GridGroupingControl1.TableDescriptor.Columns(taxname).ReadOnly = True
Next
'GridGroupingControl1.TableDescriptor.Columns.
If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
GridGroupingControl1.TableDescriptor.Columns("CurName").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ExistingRetQty").ReadOnly = True
End If
'******For hiding the column taxdetail in childtable
Dim tbl As GridTableModel = Me.GridGroupingControl1.GetTableModel("SetItems")
AddHandler tbl.QueryColWidth, AddressOf tbl_QueryColWidth
If (dtset1.Tables(1).Rows.Count > 0) Then
'parentToChildRelationDescriptor.ChildTableDescriptor.Columns("SetItem").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("BoughtItem").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("ItemName").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Qty").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AlreadyReturnedQuantity").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Rate").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("disc").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("adddisc").ReadOnly = True
'parentToChildRelationDescriptor.ChildTableDescriptor.Columns("TaxAmt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Amt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AssessableAmt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AvailabelRetQty").ReadOnly = True
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
If (SRExists(cmbSRetNo.Text, yearfrom, yearto)) Then
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("ExistingRetQty").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("TaxDetails").ReadOnly = True
End If
End If
Else
GridGroupingControl1.DataSource = Nothing
End If
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub GridGroupingControl1_TableControlCurrentCellEditingComplete(ByVal sender As System.Object, ByVal e As Syncfusion.Windows.Forms.Grid.Grouping.GridTableControlEventArgs) Handles GridGroupingControl1.TableControlCurrentCellEditingComplete
Try
Dim CRowIndex, cnt, ColIndex, totcolindex, TColIndex, CRetQtyIndex, ind As Integer
Dim Curretqty, AvRetQty, ExRetQty, chkRetQty, InvQty, Rate, Amt, CalAmt, AssAmt, CalAssAmt, TaxAmt, CalTaxAmt, TotDiscPer, TradeDiscountAmt, CashDiscountAmt, DiscountAmt, DiscountedAmt As Double
Dim ItemCode, taxname, colname As String
Dim ItemGrpCd As String = Nothing
Dim calformula As Double
RowIndex = e.TableControl.CurrentCell.RowIndex()
ColIndex = e.TableControl.CurrentCell.ColIndex()
Dim cc1 As GridCurrentCell = e.TableControl.CurrentCell
Dim style1 As GridTableCellStyleInfoIdentity = Me.GridGroupingControl1.TableModel(cc1.RowIndex, cc1.ColIndex).TableCellIdentity
If (style1.TableCellType = GridTableCellType.RecordFieldCell Or style1.TableCellType = GridTableCellType.AlternateRecordFieldCell) Then
colname = style1.Column.Name
End If
' Me.GridGroupingControl1.TableDescriptor.ColIndexToField(ColIndex)
TColIndex = dtset1.Tables(0).Columns("TaxDetails").Ordinal 'for Tax calculation
' totcolindex = dtset1.Tables(0).Columns.Count - 1
totcolindex = dtset1.Tables(0).Columns("TotalTax").Ordinal - 1
DatasetIndex = RowIndex - 3
'If (DatasetIndex >= 0) Then
' ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
'End If
'**************For Bought Item Calculation*************************
CRetQtyIndex = dtset1.Tables(0).Columns("CurrentRetQty").Ordinal
If (colname = "CurrentRetQty") Then
'If (e.TableControl.CurrentCell.ColIndex() = 9 Or e.TableControl.CurrentCell.ColIndex() = 10 And dtset1.Tables(0).Columns("CurrentRetQty").Ordinal = 7 And ItemGrpCd <> "IG011" And ItemGrpCd = "IG005") Then
'If (e.TableControl.CurrentCell.ColIndex() = 9 Or e.TableControl.CurrentCell.ColIndex() = 10) Then
Curretqty = dtset1.Tables(0).Rows(DatasetIndex)("CurrentRetQty").ToString()
AvRetQty = dtset1.Tables(0).Rows(DatasetIndex)("AvailabelRetQty").ToString()
ItemCode = dtset1.Tables(0).Rows(DatasetIndex)("ItemCode").ToString()
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If (Curretqty > AvRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity")
e.TableControl.Model(RowIndex, 9).Text = 0
End If
Rate = dtset1.Tables(0).Rows(DatasetIndex)("Rate").ToString()
Amt = dtset1.Tables(0).Rows(DatasetIndex)("SetAmt").ToString()
InvQty = dtset1.Tables(0).Rows(DatasetIndex)("Qty").ToString()
ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
CalAmt = Curretqty * Rate
dtset1.Tables(0).Rows(DatasetIndex)("Amt") = Math.Round(CalAmt, 3)
AssAmt = dtset1.Tables(0).Rows(DatasetIndex)("SetAssAmt").ToString()
CalAssAmt = (AssAmt / Amt) * Rate * Curretqty
dtset1.Tables(0).Rows(DatasetIndex)("AssessableAmt") = Math.Round(CalAssAmt, 3)
'TaxAmt = dtset1.Tables(0).Rows(DatasetIndex)("SetTotalTax").ToString()
'CalTaxAmt = (TaxAmt * Curretqty) / InvQty
'dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(CalTaxAmt, 3)
'** Tax Calculation
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(CalAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(CalAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
For i As Integer = 1 To totcolindex - TColIndex
Dim dtfnm, dtcal As New DataTable
' Dim calformula As Double
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(CalAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(CalAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
dtset1.Tables(0).Rows(DatasetIndex)(taxname) = calformula
Next
'***Addition of multiple taxes for bought item set to total tax
Dim TotalTaxAmt As Decimal = 0
For i As Integer = 1 To totcolindex - TColIndex
Dim TempTaxAmt As Decimal
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
TempTaxAmt = dtset1.Tables(0).Rows(DatasetIndex)(taxname).ToString()
TotalTaxAmt = TotalTaxAmt + TempTaxAmt
Next
dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(TotalTaxAmt, 3)
Else
ExRetQty = dtset1.Tables(0).Rows(DatasetIndex)("ExistingRetQty").ToString()
chkRetQty = AvRetQty + ExRetQty
If (Curretqty > chkRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity + Existing Return Quantity")
e.TableControl.Model(RowIndex, 10).Text = 0
End If
Dim dtStock As New DataTable
Dim StkOnDt, StkChkQty As Decimal
query = "Select StkOnDt from vw_currentstock where ItemCode='" & ItemCode & "'"
dtStock = ExecuteQry(query)
StkOnDt = dtStock.Rows(0)("StkOnDt").ToString()
StkChkQty = Curretqty - ExRetQty + StkOnDt
If (StkChkQty >= 0) Then
' MessageBox.Show("Stock Is Available")
Else
MessageBox.Show("Stock Is Not Available")
End If
Rate = dtset1.Tables(0).Rows(DatasetIndex)("Rate").ToString()
Amt = dtset1.Tables(0).Rows(DatasetIndex)("InvAmt").ToString()
InvQty = dtset1.Tables(0).Rows(DatasetIndex)("Qty").ToString()
'ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
CalAmt = Curretqty * Rate
dtset1.Tables(0).Rows(DatasetIndex)("Amt") = Math.Round(CalAmt, 3)
AssAmt = dtset1.Tables(0).Rows(DatasetIndex)("InvAssAmt").ToString()
CalAssAmt = (AssAmt / Amt) * Rate * Curretqty
dtset1.Tables(0).Rows(DatasetIndex)("AssessableAmt") = Math.Round(CalAssAmt, 3)
'TaxAmt = dtset1.Tables(0).Rows(DatasetIndex)("InvTotalTax").ToString()
'CalTaxAmt = (TaxAmt * Curretqty) / InvQty
'dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(CalTaxAmt, 3)
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(CalAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(CalAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
For i As Integer = 1 To totcolindex - TColIndex
Dim dtfnm, dtcal As New DataTable
'Dim calformula As Double
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(CalAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(CalAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
dtset1.Tables(0).Rows(DatasetIndex)(taxname) = calformula
Next
'***Addition of multiple taxes for bought item set to total tax
Dim TotalTaxAmt As Decimal = 0
For i As Integer = 1 To totcolindex - TColIndex
Dim TempTaxAmt As Decimal
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
TempTaxAmt = dtset1.Tables(0).Rows(DatasetIndex)(taxname).ToString()
TotalTaxAmt = TotalTaxAmt + TempTaxAmt
Next
dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(TotalTaxAmt, 3)
End If
CalculateSubTotal()
CalculateAssessableAmt()
CalculateTaxAmountInAccount()
CalculateTotal()
BindSyncfusionGrid(dtset1)
GoTo abc
End If
'**************Calculation For bought item in set *************************
Dim s1 As GridRangeInfoList
Dim SBCalAmt, SBRate, SBDisc, SBAddDisc, SBCurRetQty, SBAvQty, SBExQty, SBInvQty, SInvAmt, TaxAmtPerTax, SAssAmt, STaxAmt, RCalAmtVsInvAmt, RCalAmtVsAssAmt, RCalAmtVsTaxAmt, SBAmt, SBAssAmt, SBTaxAmt As Decimal
Dim SBItemCode, SItemCode, SItemGrpCd, SBItemGrpCode As String
Dim el As Element
Dim rec As GridRecord
Dim table, tablechild As GridTable
Dim tablecontrol As GridTableControl
Dim cc As GridCurrentCell
Dim style As GridTableCellStyleInfo
Dim SBCRetQtyIndex As Integer
SBCRetQtyIndex = dtset1.Tables(1).Columns("CurrentRetQty").Ordinal
Dim StartTaxIndex, TotalColIndex As Integer
Dim childcolname As String
' Dim calformula As Double
If (Pindex <> 0) Then
el = GridGroupingControl1.Table.GetInnerMostCurrentElement()
cc = e.TableControl.CurrentCell
tablechild = Me.GridGroupingControl1.GetTable("SetItems")
style = tablechild.GetTableCellStyle(cc.RowIndex, cc.ColIndex)
childcolname = style.TableCellIdentity.Column.Name
End If
'If (Pindex <> Nothing And ItemGrpCd <> "IG005" And dtset1.Tables(1).Columns("CurrentRetQty").Ordinal = 6 And (e.TableControl.CurrentCell.ColIndex() = 6 Or e.TableControl.CurrentCell.ColIndex() = 7)) Then
'If (dtset1.Tables(1).Columns("CurrentRetQty").Ordinal = 6 And (e.TableControl.CurrentCell.ColIndex() = 6 Or e.TableControl.CurrentCell.ColIndex() = 7)) Then
If (childcolname = "CurrentRetQty") Then
SItemCode = GridGroupingControl1.Table.Records(Pindex - 3)("ItemCode").ToString()
SInvAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetAmt").ToString()
SItemGrpCd = GridGroupingControl1.Table.Records(Pindex - 3)("itmgroupcd").ToString()
tempsbitmfcd = SItemGrpCd
SAssAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetAssAmt").ToString()
'For i As Integer = TColIndex + 1 To totcolindex
' taxname = dtset1.Tables(0).Columns(i).ColumnName
' STaxAmt = GridGroupingControl1.Table.Records(Pindex - 3)(taxname).ToString()
'Next
'**Currently not used
'STaxAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetTotalTax").ToString()
'*****fetching tax amt from set item
'For i As Integer = TColIndex + 1 To totcolindex
' taxname = dtset1.Tables(0).Columns(i).ColumnName
' TaxAmtPerTax = GridGroupingControl1.Table.Records(Pindex - 3)(taxname).ToString()
'Next
'Dim rc As Record = GridGroupingControl1.Table.CurrentRecord()
'SItemCode = rc("ItemCode").ToString()
'********Fetching Record from child table************
el = Me.GridGroupingControl1.Table.GetInnerMostCurrentElement()
If Not el Is Nothing Then
table = CType(IIf(TypeOf el.ParentTable Is GridTable, el.ParentTable, Nothing), GridTable)
tablecontrol = Me.GridGroupingControl1.GetTableControl(table.TableDescriptor.Name)
cc = tablecontrol.CurrentCell
style = table.GetTableCellStyle(cc.RowIndex, cc.ColIndex)
rec = CType(IIf(TypeOf el Is GridRecord, el, Nothing), GridRecord)
If rec Is Nothing AndAlso TypeOf el Is GridRecordRow Then
rec = CType(IIf(TypeOf el.ParentRecord Is GridRecord, el.ParentRecord, Nothing), GridRecord)
End If
SBItemCode = e.TableControl.Model(RowIndex, 1).Text
'SBItemCode = el.GetRecord().GetValue("BoughtItem").ToString()
'SBItemGrpCode = el.GetRecord().GetValue("ItmGroupCd").ToString()
SBCurRetQty = e.TableControl.Model(RowIndex, 6).Text
'SBCurRetQty = el.GetRecord().GetValue("CurrentRetQty").ToString()
SBAvQty = e.TableControl.Model(RowIndex, 5).Text
'SBAvQty = el.GetRecord().GetValue("AvailabelRetQty").ToString()
'SBInvQty = el.GetRecord().GetValue("Qty").ToString()
SBInvQty = e.TableControl.Model(RowIndex, 3).Text
'SBRate = el.GetRecord().GetValue("Rate").ToString()
SBRate = e.TableControl.Model(RowIndex, 7).Text
'SBDisc = el.GetRecord().GetValue("disc").ToString()
SBDisc = e.TableControl.Model(RowIndex, 8).Text
' SBAddDisc = el.GetRecord().GetValue("adddisc").ToString()
SBAddDisc = e.TableControl.Model(RowIndex, 9).Text
SBCalAmt = SBRate * (1 - ((SBDisc + SBAddDisc) / 100)) * SBInvQty
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If (SBCurRetQty > SBAvQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity")
e.TableControl.Model(RowIndex, 6).Text = 0
End If
Else
SBExQty = el.GetRecord().GetValue("ExistingRetQty").ToString()
chkRetQty = SBAvQty + SBExQty
If (SBCurRetQty > chkRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity + ExistingRetQty ")
e.TableControl.Model(RowIndex, 8).Text = 0
End If
Dim dtStock As New DataTable
Dim StkOnDt, StkChkQty As Decimal
query = "Select StkOnDt from vw_currentstock where ItemCode='" & SBItemCode & "'"
dtStock = ExecuteQry(query)
StkOnDt = dtStock.Rows(0)("StkOnDt").ToString()
StkChkQty = SBCurRetQty - SBExQty + StkOnDt
If (StkChkQty >= 0) Then
' MessageBox.Show("Stock Is Available")
Else
MessageBox.Show("Stock Is Not Available")
End If
End If
'**************Ratios of CalculatedAmount Vs. Invoiced Amt
RCalAmtVsInvAmt = SBCalAmt / SInvAmt
'*************Ratios of CalculatedAmount Vs. Assessable Amt
RCalAmtVsAssAmt = SBCalAmt / SAssAmt
'*************Ratios of Calculated Amount Vs. Tax Amount
RCalAmtVsTaxAmt = STaxAmt * (RCalAmtVsAssAmt / SBCalAmt)
'****************Amount of Bought items of SetItem
'SBAmt = SBCurRetQty * ((SBCalAmt / SBInvQty) / RCalAmtVsInvAmt)
SBAmt = SInvAmt * RCalAmtVsInvAmt * (SBCurRetQty / SBInvQty)
'****************ASSessable Amount of Bought items of SetItem
'SBAssAmt = SBCurRetQty * ((SBCalAmt / SBInvQty) / RCalAmtVsAssAmt)
SBAssAmt = SAssAmt * RCalAmtVsAssAmt * (SBCurRetQty / SBInvQty)
'****************Tax Amount of Bought items of SetItem
' old SBTaxAmt = SBCurRetQty * RCalAmtVsTaxAmt * ((SBCalAmt / SBInvQty) / RCalAmtVsAssAmt)
' new SBTaxAmt = SBAssAmt * RCalAmtVsTaxAmt
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(SBAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(SBAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
'StartTaxIndex = dtset1.Tables(1).Columns("AssessableAmt").Ordinal
StartTaxIndex = dtset1.Tables(1).Columns("TaxDetails").Ordinal
TotalColIndex = dtset1.Tables(1).Columns("TotalTax").Ordinal - 1
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim dtfnm, dtcal As New DataTable
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(SBAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(SBAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
'***Binding Tax amount to taxname in dtset1.tables(1)
For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(j)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(j)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(j)(taxname) = calformula
End If
Next
Next
Else
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(SBAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(SBAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
StartTaxIndex = dtset1.Tables(1).Columns("TaxDetails").Ordinal
TotalColIndex = dtset1.Tables(1).Columns("TotalTax").Ordinal - 1
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim dtfnm, dtcal As New DataTable
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(SBAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(SBAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
'***Binding Tax amount to taxname in dtset1.tables(1)
For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(j)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(j)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(j)(taxname) = calformula
End If
Next
Next
End If
el = Me.GridGroupingControl1.Table.GetInnerMostCurrentElement()
'*******Addition of child bought item Multiple Tax Amount set to TotalTax***********
Dim Totaltaxamt As Decimal = 0.0
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim taxvalue As Decimal
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
'taxvalue = el.GetRecord().GetValue(taxname).ToString()
taxvalue = e.TableControl.Model(RowIndex, StartTaxIndex + i).Text
Totaltaxamt = Totaltaxamt + taxvalue
Next
'***Binding amt,AssessableAmt,TotalTax in dtset1.tables(1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(i)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(i)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(i)("Amt") = Math.Round(SBAmt, 3)
dtset1.Tables(1).Rows(i)("AssessableAmt") = Math.Round(SBAssAmt, 3)
'dtset1.Tables(1).Rows(i)("TaxAmt") = Math.Round(SBTaxAmt, 3)
dtset1.Tables(1).Rows(i)("TotalTax") = Math.Round(Totaltaxamt, 3)
End If
Next
'Creating datatable for specific bought items for set item
dtset1.Tables(1).AcceptChanges()
Dim Caldt As New DataTable
Caldt = dtset1.Tables(1).Clone()
Dim dr2 As DataRow() = dtset1.Tables(1).Select("SetItem = '" & SItemCode & "'")
For Each row As DataRow In dr2
Caldt.ImportRow(row)
Next
'*******Addition of child bought item Amount set to the set item***********
Dim setAmt As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim samt As Decimal = dtset1.Tables(1).Rows(i)("Amt").ToString()
' setAmt = setAmt + samt
' dtset1.Tables(0).Rows(Pindex - 3)("Amt") = Math.Round(setAmt, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim samt As Decimal = Caldt.Rows(i)("Amt").ToString()
setAmt = setAmt + samt
Next
dtset1.Tables(0).Rows(Pindex - 3)("Amt") = Math.Round(setAmt, 3)
'*******Addition of child bought item Assessable Amount set to the set item***********
Dim setAssAmt As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim AssAmount As Decimal = dtset1.Tables(1).Rows(i)("AssessableAmt").ToString()
' setAssAmt = setAssAmt + AssAmount
' dtset1.Tables(0).Rows(Pindex - 3)("AssessableAmt") = Math.Round(setAssAmt, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim AssAmount As Decimal = Caldt.Rows(i)("AssessableAmt").ToString()
setAssAmt = setAssAmt + AssAmount
Next
dtset1.Tables(0).Rows(Pindex - 3)("AssessableAmt") = Math.Round(setAssAmt, 3)
'*******Addition of child bought item TotalTax set to the set item ***********
Dim setTotalTax As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim TaxAmount As Decimal = dtset1.Tables(1).Rows(i)("TotalTaxAmt").ToString()
' setTotalTax = setTotalTax + TaxAmount
' dtset1.Tables(0).Rows(Pindex - 3)("TotalTax") = Math.Round(setTotalTax, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim TaxAmount As Decimal = Caldt.Rows(i)("TotalTax").ToString()
setTotalTax = setTotalTax + TaxAmount
Next
dtset1.Tables(0).Rows(Pindex - 3)("TotalTax") = Math.Round(setTotalTax, 3)
End If
End If
CalculateSubTotal()
CalculateAssessableAmt()
CalculateTaxAmountInAccount()
CalculateTotal()
BindSyncfusionGrid(dtset1)
If (SItemGrpCd = "IG011") Then
Me.GridGroupingControl1.Table.Records(Pindex - 3).IsExpanded = True
End If
GoTo xyz
abc:
xyz:
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub



SV Supriya Varne replied to Supriya Varne January 13, 2015 08:37 AM UTC

Hi,
Sir Solution not working properly.
Please give me another solution.
My Code is
Public Sub BindSyncfusionGrid(ByVal dtset1 As DataSet)
Try
'Dim dr As SqlDataReader
Dim dttax As New DataTable
Dim index As Integer
If (dtset1.Tables(0).Rows.Count > 0) Then
'for adding the column Availabel RetQty in bought item
GridGroupingControl1.DataSource = Nothing
If Not dtset1.Tables(0).Columns.Contains("AvailabelRetQty") Then
index = dtset1.Tables(0).Columns("AlreadyReturnedQuantity").Ordinal
dtset1.Tables(0).Columns.Add("AvailabelRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
Dim invqty, alretqty As Decimal
invqty = dtset1.Tables(0).Rows(i)("Qty").ToString()
alretqty = dtset1.Tables(0).Rows(i)("AlreadyReturnedQuantity").ToString()
dtset1.Tables(0).Rows(i)("AvailabelRetQty") = invqty - alretqty
Next
End If
'for adding the column Current RetQty in bought item
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(0).Columns.Contains("CurrentRetQty") Then
index = dtset1.Tables(0).Columns("AvailabelRetQty").Ordinal
dtset1.Tables(0).Columns.Add("CurrentRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("CurrentRetQty") = dtset1.Tables(0).Rows(i)("AvailabelRetQty")
Next
End If
End If
'for adding the column SetAmt in bought item
If Not dtset1.Tables(0).Columns.Contains("SetAmt") Then
index = dtset1.Tables(0).Columns("Amt").Ordinal
dtset1.Tables(0).Columns.Add("SetAmt").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetAmt") = dtset1.Tables(0).Rows(i)("Amt").ToString()
Next
End If
'for adding the column SetAssAmt in bought item
If Not dtset1.Tables(0).Columns.Contains("SetAssAmt") Then
index = dtset1.Tables(0).Columns("AssessableAmt").Ordinal
dtset1.Tables(0).Columns.Add("SetAssAmt").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetAssAmt") = dtset1.Tables(0).Rows(i)("AssessableAmt").ToString()
Next
End If
'for adding the column SetTotalTax in bought item
If Not dtset1.Tables(0).Columns.Contains("SetTotalTax") Then
index = dtset1.Tables(0).Columns("TotalTax").Ordinal
dtset1.Tables(0).Columns.Add("SetTotalTax").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
dtset1.Tables(0).Rows(i)("SetTotalTax") = dtset1.Tables(0).Rows(i)("TotalTax").ToString()
Next
End If
'dtset1.Tables(1).Columns.Add("SetQty")
'For i As Integer = 0 To dtset1.Tables(0).Rows.Count - 1
' Dim setqty, qty, sqty As Decimal
' Dim itemcode As String
' Dim Setdt As New DataTable
' itemcode = dtset1.Tables(0).Rows(i)("ItemCode").ToString()
' If isSet11(itemcode) Then
' qty = dtset1.Tables(0).Rows(i)("Qty").ToString()
' For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' sqty = dtset1.Tables(1).Rows(j)("Qty").ToString()
' setqty = qty * sqty
' If (itemcode = dtset1.Tables(1).Rows(j)("SetsID").ToString()) Then
' dtset1.Tables(1).Rows(j)("SetQty") = setqty
' End If
' Next
' End If
'Next
'for adding the column Availabel RetQty in bought items of set item
If Not dtset1.Tables(1).Columns.Contains("AvailabelRetQty") Then
index = dtset1.Tables(1).Columns("AlreadyReturnedQuantity").Ordinal
dtset1.Tables(1).Columns.Add("AvailabelRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
Dim invqty, alretqty As Decimal
invqty = dtset1.Tables(1).Rows(i)("Qty").ToString()
alretqty = dtset1.Tables(1).Rows(i)("AlreadyReturnedQuantity").ToString()
dtset1.Tables(1).Rows(i)("AvailabelRetQty") = invqty - alretqty
Next
End If
'for adding the column Current RetQty in bought items of setitem
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(1).Columns.Contains("CurrentRetQty") Then
index = dtset1.Tables(1).Columns("AvailabelRetQty").Ordinal
dtset1.Tables(1).Columns.Add("CurrentRetQty").SetOrdinal(index + 1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
dtset1.Tables(1).Rows(i)("CurrentRetQty") = dtset1.Tables(1).Rows(i)("AvailabelRetQty")
Next
End If
End If
''for adding the column amt in bought items of set items
'If Not dtset1.Tables(1).Columns.Contains("Amt") Then
' dtset1.Tables(1).Columns.Add("Amt")
' For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' dtset1.Tables(1).Rows(i)("Amt") = 0.0
' Next
'End If
''for adding the column Assessable amt in bought items of set items
'If Not dtset1.Tables(1).Columns.Contains("AssessableAmt") Then
' dtset1.Tables(1).Columns.Add("AssessableAmt")
' For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' dtset1.Tables(1).Rows(i)("AssessableAmt") = 0.0
' Next
'End If
''for adding the column TotalTax in bought items of set items
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If Not dtset1.Tables(1).Columns.Contains("TotalTax") Then
dtset1.Tables(1).Columns.Add("TotalTax")
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
dtset1.Tables(1).Rows(i)("TotalTax") = 0.0
Next
End If
End If
Dim parentToChildRelationDescriptor As New Syncfusion.Windows.Forms.Grid.Grouping.GridRelationDescriptor()
GridGroupingControl1.DataSource = Nothing
parentToChildRelationDescriptor.ChildTableName = "SetItems"
parentToChildRelationDescriptor.RelationKind = Syncfusion.Grouping.RelationKind.RelatedMasterDetails
parentToChildRelationDescriptor.RelationKeys.Add("ItemCode", "SetItem")
GridGroupingControl1.TableDescriptor.Relations.Add(parentToChildRelationDescriptor)
GridGroupingControl1.Engine.SourceListSet.Add("ItemMaster", dtset1.Tables(0))
GridGroupingControl1.Engine.SourceListSet.Add("SetItems", dtset1.Tables(1))
GridGroupingControl1.DataSource = dtset1.Tables(0)
' parentToChildRelationDescriptor.ChildTableDescriptor.Columns.AddRange(New GridColumnDescriptor() {New GridColumnDescriptor("BoughtItem"), New GridColumnDescriptor("ItemName"), New GridColumnDescriptor("Qty"), New GridColumnDescriptor("AlreadyReturnedQuantity"), New GridColumnDescriptor("AvailabelRetQty"), New GridColumnDescriptor("CurrentRetQty"), New GridColumnDescriptor("Rate"), New GridColumnDescriptor("Disc"), New GridColumnDescriptor("adddisc"), New GridColumnDescriptor("Amt"), New GridColumnDescriptor("AssessableAmt"), New GridColumnDescriptor("TotalTax")})
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetAssAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("SetTotalTax")
If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("CurName")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvAssAmt")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvTaxDetails")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("InvTotalTax")
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
End If
'GridGroupingControl1.TableModel.HideCols("TaxDetails") = True
'GridGroupingControl1.TableModel.HideCols("SetAssAmt") = True
'GridGroupingControl1.TableModel.HideCols("SetAmt") = True
'GridGroupingControl1.TableModel.HideCols("SetTotalTax") = True
'If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
' GridGroupingControl1.TableModel.HideCols("CurName") = True
'End If
GridGroupingControl1.TableDescriptor.AllowNew = False
parentToChildRelationDescriptor.ChildTableDescriptor.AllowNew = False
GridGroupingControl1.TableDescriptor.Columns("itmgroupcd").ReadOnly = True
'GridGroupingControl1.TableDescriptor.Columns("SrNo").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ItemCode").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ItemName").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Qty").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AlreadyReturnedQuantity").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Rate").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Amt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AssessableAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("Unit").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("TaxDetails").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetAssAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetAmt").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("SetTotalTax").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("AvailabelRetQty").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("TotalTax").ReadOnly = True
Dim TColIndex, totcolindex As Integer
Dim taxname As String
TColIndex = dtset1.Tables(0).Columns("TaxDetails").Ordinal
totcolindex = dtset1.Tables(0).Columns("TotalTax").Ordinal - 1
For i As Integer = TColIndex + 1 To totcolindex
taxname = dtset1.Tables(0).Columns(i).ColumnName
GridGroupingControl1.TableDescriptor.Columns(taxname).ReadOnly = True
Next
'GridGroupingControl1.TableDescriptor.Columns.
If SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
GridGroupingControl1.TableDescriptor.Columns("CurName").ReadOnly = True
GridGroupingControl1.TableDescriptor.Columns("ExistingRetQty").ReadOnly = True
End If
'******For hiding the column taxdetail in childtable
Dim tbl As GridTableModel = Me.GridGroupingControl1.GetTableModel("SetItems")
AddHandler tbl.QueryColWidth, AddressOf tbl_QueryColWidth
If (dtset1.Tables(1).Rows.Count > 0) Then
'parentToChildRelationDescriptor.ChildTableDescriptor.Columns("SetItem").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("BoughtItem").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("ItemName").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Qty").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AlreadyReturnedQuantity").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Rate").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("disc").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("adddisc").ReadOnly = True
'parentToChildRelationDescriptor.ChildTableDescriptor.Columns("TaxAmt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("Amt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AssessableAmt").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("AvailabelRetQty").ReadOnly = True
GridGroupingControl1.TableDescriptor.VisibleColumns.Remove("TaxDetails")
If (SRExists(cmbSRetNo.Text, yearfrom, yearto)) Then
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("ExistingRetQty").ReadOnly = True
parentToChildRelationDescriptor.ChildTableDescriptor.Columns("TaxDetails").ReadOnly = True
End If
End If
Else
GridGroupingControl1.DataSource = Nothing
End If
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub GridGroupingControl1_TableControlCurrentCellEditingComplete(ByVal sender As System.Object, ByVal e As Syncfusion.Windows.Forms.Grid.Grouping.GridTableControlEventArgs) Handles GridGroupingControl1.TableControlCurrentCellEditingComplete
Try
Dim CRowIndex, cnt, ColIndex, totcolindex, TColIndex, CRetQtyIndex, ind As Integer
Dim Curretqty, AvRetQty, ExRetQty, chkRetQty, InvQty, Rate, Amt, CalAmt, AssAmt, CalAssAmt, TaxAmt, CalTaxAmt, TotDiscPer, TradeDiscountAmt, CashDiscountAmt, DiscountAmt, DiscountedAmt As Double
Dim ItemCode, taxname, colname As String
Dim ItemGrpCd As String = Nothing
Dim calformula As Double
RowIndex = e.TableControl.CurrentCell.RowIndex()
ColIndex = e.TableControl.CurrentCell.ColIndex()
Dim cc1 As GridCurrentCell = e.TableControl.CurrentCell
Dim style1 As GridTableCellStyleInfoIdentity = Me.GridGroupingControl1.TableModel(cc1.RowIndex, cc1.ColIndex).TableCellIdentity
If (style1.TableCellType = GridTableCellType.RecordFieldCell Or style1.TableCellType = GridTableCellType.AlternateRecordFieldCell) Then
colname = style1.Column.Name
End If
' Me.GridGroupingControl1.TableDescriptor.ColIndexToField(ColIndex)
TColIndex = dtset1.Tables(0).Columns("TaxDetails").Ordinal 'for Tax calculation
' totcolindex = dtset1.Tables(0).Columns.Count - 1
totcolindex = dtset1.Tables(0).Columns("TotalTax").Ordinal - 1
DatasetIndex = RowIndex - 3
'If (DatasetIndex >= 0) Then
' ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
'End If
'**************For Bought Item Calculation*************************
CRetQtyIndex = dtset1.Tables(0).Columns("CurrentRetQty").Ordinal
If (colname = "CurrentRetQty") Then
'If (e.TableControl.CurrentCell.ColIndex() = 9 Or e.TableControl.CurrentCell.ColIndex() = 10 And dtset1.Tables(0).Columns("CurrentRetQty").Ordinal = 7 And ItemGrpCd <> "IG011" And ItemGrpCd = "IG005") Then
'If (e.TableControl.CurrentCell.ColIndex() = 9 Or e.TableControl.CurrentCell.ColIndex() = 10) Then
Curretqty = dtset1.Tables(0).Rows(DatasetIndex)("CurrentRetQty").ToString()
AvRetQty = dtset1.Tables(0).Rows(DatasetIndex)("AvailabelRetQty").ToString()
ItemCode = dtset1.Tables(0).Rows(DatasetIndex)("ItemCode").ToString()
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If (Curretqty > AvRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity")
e.TableControl.Model(RowIndex, 9).Text = 0
End If
Rate = dtset1.Tables(0).Rows(DatasetIndex)("Rate").ToString()
Amt = dtset1.Tables(0).Rows(DatasetIndex)("SetAmt").ToString()
InvQty = dtset1.Tables(0).Rows(DatasetIndex)("Qty").ToString()
ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
CalAmt = Curretqty * Rate
dtset1.Tables(0).Rows(DatasetIndex)("Amt") = Math.Round(CalAmt, 3)
AssAmt = dtset1.Tables(0).Rows(DatasetIndex)("SetAssAmt").ToString()
CalAssAmt = (AssAmt / Amt) * Rate * Curretqty
dtset1.Tables(0).Rows(DatasetIndex)("AssessableAmt") = Math.Round(CalAssAmt, 3)
'TaxAmt = dtset1.Tables(0).Rows(DatasetIndex)("SetTotalTax").ToString()
'CalTaxAmt = (TaxAmt * Curretqty) / InvQty
'dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(CalTaxAmt, 3)
'** Tax Calculation
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(CalAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(CalAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
For i As Integer = 1 To totcolindex - TColIndex
Dim dtfnm, dtcal As New DataTable
' Dim calformula As Double
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(CalAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(CalAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
dtset1.Tables(0).Rows(DatasetIndex)(taxname) = calformula
Next
'***Addition of multiple taxes for bought item set to total tax
Dim TotalTaxAmt As Decimal = 0
For i As Integer = 1 To totcolindex - TColIndex
Dim TempTaxAmt As Decimal
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
TempTaxAmt = dtset1.Tables(0).Rows(DatasetIndex)(taxname).ToString()
TotalTaxAmt = TotalTaxAmt + TempTaxAmt
Next
dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(TotalTaxAmt, 3)
Else
ExRetQty = dtset1.Tables(0).Rows(DatasetIndex)("ExistingRetQty").ToString()
chkRetQty = AvRetQty + ExRetQty
If (Curretqty > chkRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity + Existing Return Quantity")
e.TableControl.Model(RowIndex, 10).Text = 0
End If
Dim dtStock As New DataTable
Dim StkOnDt, StkChkQty As Decimal
query = "Select StkOnDt from vw_currentstock where ItemCode='" & ItemCode & "'"
dtStock = ExecuteQry(query)
StkOnDt = dtStock.Rows(0)("StkOnDt").ToString()
StkChkQty = Curretqty - ExRetQty + StkOnDt
If (StkChkQty >= 0) Then
' MessageBox.Show("Stock Is Available")
Else
MessageBox.Show("Stock Is Not Available")
End If
Rate = dtset1.Tables(0).Rows(DatasetIndex)("Rate").ToString()
Amt = dtset1.Tables(0).Rows(DatasetIndex)("InvAmt").ToString()
InvQty = dtset1.Tables(0).Rows(DatasetIndex)("Qty").ToString()
'ItemGrpCd = dtset1.Tables(0).Rows(DatasetIndex)("itmgroupcd").ToString()
CalAmt = Curretqty * Rate
dtset1.Tables(0).Rows(DatasetIndex)("Amt") = Math.Round(CalAmt, 3)
AssAmt = dtset1.Tables(0).Rows(DatasetIndex)("InvAssAmt").ToString()
CalAssAmt = (AssAmt / Amt) * Rate * Curretqty
dtset1.Tables(0).Rows(DatasetIndex)("AssessableAmt") = Math.Round(CalAssAmt, 3)
'TaxAmt = dtset1.Tables(0).Rows(DatasetIndex)("InvTotalTax").ToString()
'CalTaxAmt = (TaxAmt * Curretqty) / InvQty
'dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(CalTaxAmt, 3)
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(CalAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(CalAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(CalAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
For i As Integer = 1 To totcolindex - TColIndex
Dim dtfnm, dtcal As New DataTable
'Dim calformula As Double
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(CalAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(CalAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
dtset1.Tables(0).Rows(DatasetIndex)(taxname) = calformula
Next
'***Addition of multiple taxes for bought item set to total tax
Dim TotalTaxAmt As Decimal = 0
For i As Integer = 1 To totcolindex - TColIndex
Dim TempTaxAmt As Decimal
taxname = dtset1.Tables(0).Columns(TColIndex + i).ColumnName
TempTaxAmt = dtset1.Tables(0).Rows(DatasetIndex)(taxname).ToString()
TotalTaxAmt = TotalTaxAmt + TempTaxAmt
Next
dtset1.Tables(0).Rows(DatasetIndex)("TotalTax") = Math.Round(TotalTaxAmt, 3)
End If
CalculateSubTotal()
CalculateAssessableAmt()
CalculateTaxAmountInAccount()
CalculateTotal()
BindSyncfusionGrid(dtset1)
GoTo abc
End If
'**************Calculation For bought item in set *************************
Dim s1 As GridRangeInfoList
Dim SBCalAmt, SBRate, SBDisc, SBAddDisc, SBCurRetQty, SBAvQty, SBExQty, SBInvQty, SInvAmt, TaxAmtPerTax, SAssAmt, STaxAmt, RCalAmtVsInvAmt, RCalAmtVsAssAmt, RCalAmtVsTaxAmt, SBAmt, SBAssAmt, SBTaxAmt As Decimal
Dim SBItemCode, SItemCode, SItemGrpCd, SBItemGrpCode As String
Dim el As Element
Dim rec As GridRecord
Dim table, tablechild As GridTable
Dim tablecontrol As GridTableControl
Dim cc As GridCurrentCell
Dim style As GridTableCellStyleInfo
Dim SBCRetQtyIndex As Integer
SBCRetQtyIndex = dtset1.Tables(1).Columns("CurrentRetQty").Ordinal
Dim StartTaxIndex, TotalColIndex As Integer
Dim childcolname As String
' Dim calformula As Double
If (Pindex <> 0) Then
el = GridGroupingControl1.Table.GetInnerMostCurrentElement()
cc = e.TableControl.CurrentCell
tablechild = Me.GridGroupingControl1.GetTable("SetItems")
style = tablechild.GetTableCellStyle(cc.RowIndex, cc.ColIndex)
childcolname = style.TableCellIdentity.Column.Name
End If
'If (Pindex <> Nothing And ItemGrpCd <> "IG005" And dtset1.Tables(1).Columns("CurrentRetQty").Ordinal = 6 And (e.TableControl.CurrentCell.ColIndex() = 6 Or e.TableControl.CurrentCell.ColIndex() = 7)) Then
'If (dtset1.Tables(1).Columns("CurrentRetQty").Ordinal = 6 And (e.TableControl.CurrentCell.ColIndex() = 6 Or e.TableControl.CurrentCell.ColIndex() = 7)) Then
If (childcolname = "CurrentRetQty") Then
SItemCode = GridGroupingControl1.Table.Records(Pindex - 3)("ItemCode").ToString()
SInvAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetAmt").ToString()
SItemGrpCd = GridGroupingControl1.Table.Records(Pindex - 3)("itmgroupcd").ToString()
tempsbitmfcd = SItemGrpCd
SAssAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetAssAmt").ToString()
'For i As Integer = TColIndex + 1 To totcolindex
' taxname = dtset1.Tables(0).Columns(i).ColumnName
' STaxAmt = GridGroupingControl1.Table.Records(Pindex - 3)(taxname).ToString()
'Next
'**Currently not used
'STaxAmt = GridGroupingControl1.Table.Records(Pindex - 3)("SetTotalTax").ToString()
'*****fetching tax amt from set item
'For i As Integer = TColIndex + 1 To totcolindex
' taxname = dtset1.Tables(0).Columns(i).ColumnName
' TaxAmtPerTax = GridGroupingControl1.Table.Records(Pindex - 3)(taxname).ToString()
'Next
'Dim rc As Record = GridGroupingControl1.Table.CurrentRecord()
'SItemCode = rc("ItemCode").ToString()
'********Fetching Record from child table************
el = Me.GridGroupingControl1.Table.GetInnerMostCurrentElement()
If Not el Is Nothing Then
table = CType(IIf(TypeOf el.ParentTable Is GridTable, el.ParentTable, Nothing), GridTable)
tablecontrol = Me.GridGroupingControl1.GetTableControl(table.TableDescriptor.Name)
cc = tablecontrol.CurrentCell
style = table.GetTableCellStyle(cc.RowIndex, cc.ColIndex)
rec = CType(IIf(TypeOf el Is GridRecord, el, Nothing), GridRecord)
If rec Is Nothing AndAlso TypeOf el Is GridRecordRow Then
rec = CType(IIf(TypeOf el.ParentRecord Is GridRecord, el.ParentRecord, Nothing), GridRecord)
End If
SBItemCode = e.TableControl.Model(RowIndex, 1).Text
'SBItemCode = el.GetRecord().GetValue("BoughtItem").ToString()
'SBItemGrpCode = el.GetRecord().GetValue("ItmGroupCd").ToString()
SBCurRetQty = e.TableControl.Model(RowIndex, 6).Text
'SBCurRetQty = el.GetRecord().GetValue("CurrentRetQty").ToString()
SBAvQty = e.TableControl.Model(RowIndex, 5).Text
'SBAvQty = el.GetRecord().GetValue("AvailabelRetQty").ToString()
'SBInvQty = el.GetRecord().GetValue("Qty").ToString()
SBInvQty = e.TableControl.Model(RowIndex, 3).Text
'SBRate = el.GetRecord().GetValue("Rate").ToString()
SBRate = e.TableControl.Model(RowIndex, 7).Text
'SBDisc = el.GetRecord().GetValue("disc").ToString()
SBDisc = e.TableControl.Model(RowIndex, 8).Text
' SBAddDisc = el.GetRecord().GetValue("adddisc").ToString()
SBAddDisc = e.TableControl.Model(RowIndex, 9).Text
SBCalAmt = SBRate * (1 - ((SBDisc + SBAddDisc) / 100)) * SBInvQty
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
If (SBCurRetQty > SBAvQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity")
e.TableControl.Model(RowIndex, 6).Text = 0
End If
Else
SBExQty = el.GetRecord().GetValue("ExistingRetQty").ToString()
chkRetQty = SBAvQty + SBExQty
If (SBCurRetQty > chkRetQty) Then
MessageBox.Show("Sales Return Quantity Should be Less Than Availabel Return Quantity + ExistingRetQty ")
e.TableControl.Model(RowIndex, 8).Text = 0
End If
Dim dtStock As New DataTable
Dim StkOnDt, StkChkQty As Decimal
query = "Select StkOnDt from vw_currentstock where ItemCode='" & SBItemCode & "'"
dtStock = ExecuteQry(query)
StkOnDt = dtStock.Rows(0)("StkOnDt").ToString()
StkChkQty = SBCurRetQty - SBExQty + StkOnDt
If (StkChkQty >= 0) Then
' MessageBox.Show("Stock Is Available")
Else
MessageBox.Show("Stock Is Not Available")
End If
End If
'**************Ratios of CalculatedAmount Vs. Invoiced Amt
RCalAmtVsInvAmt = SBCalAmt / SInvAmt
'*************Ratios of CalculatedAmount Vs. Assessable Amt
RCalAmtVsAssAmt = SBCalAmt / SAssAmt
'*************Ratios of Calculated Amount Vs. Tax Amount
RCalAmtVsTaxAmt = STaxAmt * (RCalAmtVsAssAmt / SBCalAmt)
'****************Amount of Bought items of SetItem
'SBAmt = SBCurRetQty * ((SBCalAmt / SBInvQty) / RCalAmtVsInvAmt)
SBAmt = SInvAmt * RCalAmtVsInvAmt * (SBCurRetQty / SBInvQty)
'****************ASSessable Amount of Bought items of SetItem
'SBAssAmt = SBCurRetQty * ((SBCalAmt / SBInvQty) / RCalAmtVsAssAmt)
SBAssAmt = SAssAmt * RCalAmtVsAssAmt * (SBCurRetQty / SBInvQty)
'****************Tax Amount of Bought items of SetItem
' old SBTaxAmt = SBCurRetQty * RCalAmtVsTaxAmt * ((SBCalAmt / SBInvQty) / RCalAmtVsAssAmt)
' new SBTaxAmt = SBAssAmt * RCalAmtVsTaxAmt
If Not SRExists(cmbSRetNo.Text, yearfrom, yearto) Then
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(SBAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(SBAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
'StartTaxIndex = dtset1.Tables(1).Columns("AssessableAmt").Ordinal
StartTaxIndex = dtset1.Tables(1).Columns("TaxDetails").Ordinal
TotalColIndex = dtset1.Tables(1).Columns("TotalTax").Ordinal - 1
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim dtfnm, dtcal As New DataTable
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(SBAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(SBAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
'***Binding Tax amount to taxname in dtset1.tables(1)
For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(j)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(j)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(j)(taxname) = calformula
End If
Next
Next
Else
TotDiscPer = Val(txtDiscPer.Text) + Val(txtTDiscPer.Text)
TradeDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtTDiscPer.Text) / 100), "#0.000"))
CashDiscountAmt = CDbl(VB6.Format(SBAmt * ((txtDiscPer.Text) / 100), "#0.000"))
DiscountAmt = CDbl(VB6.Format(SBAmt - TradeDiscountAmt - CashDiscountAmt, "#0.000"))
DiscountedAmt = CDbl(VB6.Format(SBAmt - DiscountAmt, "#0.000"))
cnt = dtset1.Tables(0).Columns.Count
StartTaxIndex = dtset1.Tables(1).Columns("TaxDetails").Ordinal
TotalColIndex = dtset1.Tables(1).Columns("TotalTax").Ordinal - 1
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim dtfnm, dtcal As New DataTable
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
query = "select Formula from FormulaMst where TaxName like '" & taxname & "'"
dtfnm = ExecuteQry(query)
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Gross Amount}", VB6.Format(SBAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Trade Discount}", VB6.Format(TradeDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Cash Discount}", VB6.Format(CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discount}", VB6.Format(TradeDiscountAmt + CashDiscountAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Discounted Amount}", VB6.Format(DiscountedAmt, "#0.000"))
dtfnm.Rows(0)("Formula") = Replace(dtfnm.Rows(0)("Formula"), "{Assessable Value}", VB6.Format(SBAssAmt, "#0.000"))
query = "select " & dtfnm.Rows(0)("Formula").ToString()
dtcal = ExecuteQry(query)
calformula = VB6.Format(dtcal.Rows(0)(0), "#0.000")
'***Binding Tax amount to taxname in dtset1.tables(1)
For j As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(j)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(j)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(j)(taxname) = calformula
End If
Next
Next
End If
el = Me.GridGroupingControl1.Table.GetInnerMostCurrentElement()
'*******Addition of child bought item Multiple Tax Amount set to TotalTax***********
Dim Totaltaxamt As Decimal = 0.0
For i As Integer = 1 To TotalColIndex - StartTaxIndex
Dim taxvalue As Decimal
taxname = dtset1.Tables(1).Columns(StartTaxIndex + i).ColumnName
'taxvalue = el.GetRecord().GetValue(taxname).ToString()
taxvalue = e.TableControl.Model(RowIndex, StartTaxIndex + i).Text
Totaltaxamt = Totaltaxamt + taxvalue
Next
'***Binding amt,AssessableAmt,TotalTax in dtset1.tables(1)
For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
If (dtset1.Tables(1).Rows(i)("BoughtItem").ToString() = SBItemCode And dtset1.Tables(1).Rows(i)("SetItem").ToString() = SItemCode) Then
dtset1.Tables(1).Rows(i)("Amt") = Math.Round(SBAmt, 3)
dtset1.Tables(1).Rows(i)("AssessableAmt") = Math.Round(SBAssAmt, 3)
'dtset1.Tables(1).Rows(i)("TaxAmt") = Math.Round(SBTaxAmt, 3)
dtset1.Tables(1).Rows(i)("TotalTax") = Math.Round(Totaltaxamt, 3)
End If
Next
'Creating datatable for specific bought items for set item
dtset1.Tables(1).AcceptChanges()
Dim Caldt As New DataTable
Caldt = dtset1.Tables(1).Clone()
Dim dr2 As DataRow() = dtset1.Tables(1).Select("SetItem = '" & SItemCode & "'")
For Each row As DataRow In dr2
Caldt.ImportRow(row)
Next
'*******Addition of child bought item Amount set to the set item***********
Dim setAmt As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim samt As Decimal = dtset1.Tables(1).Rows(i)("Amt").ToString()
' setAmt = setAmt + samt
' dtset1.Tables(0).Rows(Pindex - 3)("Amt") = Math.Round(setAmt, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim samt As Decimal = Caldt.Rows(i)("Amt").ToString()
setAmt = setAmt + samt
Next
dtset1.Tables(0).Rows(Pindex - 3)("Amt") = Math.Round(setAmt, 3)
'*******Addition of child bought item Assessable Amount set to the set item***********
Dim setAssAmt As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim AssAmount As Decimal = dtset1.Tables(1).Rows(i)("AssessableAmt").ToString()
' setAssAmt = setAssAmt + AssAmount
' dtset1.Tables(0).Rows(Pindex - 3)("AssessableAmt") = Math.Round(setAssAmt, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim AssAmount As Decimal = Caldt.Rows(i)("AssessableAmt").ToString()
setAssAmt = setAssAmt + AssAmount
Next
dtset1.Tables(0).Rows(Pindex - 3)("AssessableAmt") = Math.Round(setAssAmt, 3)
'*******Addition of child bought item TotalTax set to the set item ***********
Dim setTotalTax As Decimal = 0.0
'For i As Integer = 0 To dtset1.Tables(1).Rows.Count - 1
' Dim TaxAmount As Decimal = dtset1.Tables(1).Rows(i)("TotalTaxAmt").ToString()
' setTotalTax = setTotalTax + TaxAmount
' dtset1.Tables(0).Rows(Pindex - 3)("TotalTax") = Math.Round(setTotalTax, 3)
'Next
For i As Integer = 0 To Caldt.Rows.Count - 1
Dim TaxAmount As Decimal = Caldt.Rows(i)("TotalTax").ToString()
setTotalTax = setTotalTax + TaxAmount
Next
dtset1.Tables(0).Rows(Pindex - 3)("TotalTax") = Math.Round(setTotalTax, 3)
End If
End If
CalculateSubTotal()
CalculateAssessableAmt()
CalculateTaxAmountInAccount()
CalculateTotal()
BindSyncfusionGrid(dtset1)
If (SItemGrpCd = "IG011") Then
Me.GridGroupingControl1.Table.Records(Pindex - 3).IsExpanded = True
End If
GoTo xyz
abc:
xyz:
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub



hi,
Thanks sir its working.
regards,
supriya



MK Muthukumar Kalyanasundaram Syncfusion Team January 13, 2015 12:47 PM UTC

Hi Supriya, 

We are glad to hear that the reported issue has been resolved. Please let us know if you need any further assistance. 

Regards,

Muthukumar K


Loader.
Live Chat Icon For mobile
Up arrow icon