18-10-19, 01:23 AM
مطلو اضافة عمود الرصيد بكشف الحساب كى يكون
مدين و دائن و رصيد
الكود موجود بالمرفقات
مدين و دائن و رصيد
الكود موجود بالمرفقات
كود :
Imports System.Data
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports Excel = Microsoft.Office.Interop.Excel
Public Class frmAccountBalance
Dim conn As SqlConnection = MainClass.ConnObj
Private Sub Panel2_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel2.Paint
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Close()
End Sub
Private Sub TextBox6_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCode.KeyPress
MainClass.ISInteger(e)
End Sub
Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
End Sub
Private Sub LoadAccounts()
Try
Dim da As New SqlDataAdapter("select Code,AName from Accounts_Index where Type=2 and (acc_branch=" & MainClass.BranchNo & " or acc_branch is null )", conn)
Dim dt As New DataTable
da.Fill(dt)
cmbAccounts.DataSource = dt
cmbAccounts.DisplayMember = "AName"
cmbAccounts.ValueMember = "Code"
cmbAccounts.SelectedIndex = -1
Catch ex As Exception
End Try
End Sub
Private Sub frmAccountBalance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
txtDateFrom.Value = DateTime.Now.ToShortDateString
txtDateTo.Value = DateTime.Now.ToShortDateString
Catch ex As Exception
End Try
LoadAccounts()
End Sub
Private Sub cmbAccounts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbAccounts.SelectedIndexChanged
Try
txtCode.Text = cmbAccounts.SelectedValue
Catch ex As Exception
End Try
End Sub
Private Sub chkAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkAll.CheckedChanged
Try
If chkAll.Checked Then
txtCode.Enabled = False
cmbAccounts.Enabled = False
Else
txtCode.Enabled = True
cmbAccounts.Enabled = True
End If
Catch ex As Exception
End Try
End Sub
Private Sub txtCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtCode.TextChanged
Try
cmbAccounts.SelectedValue = txtCode.Text
Catch ex As Exception
End Try
End Sub
Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel1.Paint
End Sub
Public Sub ShowAccount()
Try
Dim _SumDept As Double = 0
Dim _SumCredit As Double = 0
dgvSrch.Rows.Clear()
txtTotDept.Text = ""
txtTotCredit.Text = ""
txtBalance1.Text = ""
txtBalance2.Text = ""
Dim cond As String = ""
If Not chkAll.Checked Then
If cmbAccounts.SelectedValue Is Nothing Then
MessageBox.Show("اختر حساب", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
cmbAccounts.Focus()
Exit Sub
End If
cond += " and Restrictions_Sub.acc_no=" & txtCode.Text
End If
Dim CondBranch As String = ""
If MainClass.BranchNo <> -1 Then
CondBranch = "Restrictions.branch=" & MainClass.BranchNo & " and Restrictions_Sub.branch=" & MainClass.BranchNo & " and "
End If
Dim daopen As New SqlDataAdapter("select * from Accounts_Index where Code='" + txtCode.Text + "'", conn)
Dim dtopen As New DataTable()
daopen.Fill(dtopen)
If dtopen.Rows.Count > 0 Then
If Val("" & dtopen.Rows(0)("IValue")) <> 0 Then
dgvSrch.Rows.Add()
If Convert.ToInt32(dtopen.Rows(0)("Nature")) = 1 Then
dgvSrch.Rows(0).Cells(0).Value = String.Format("{0:0.#,##.##}", Convert.ToDouble(dtopen.Rows(0)("IValue")))
dgvSrch.Rows(0).Cells(1).Value = 0
_SumDept += Val(dtopen.Rows(0)("IValue"))
Else
dgvSrch.Rows(0).Cells(0).Value = 0
dgvSrch.Rows(0).Cells(1).Value = String.Format("{0:0.#,##.##}", Convert.ToDouble(dtopen.Rows(0)("IValue")))
_SumCredit += Val(dtopen.Rows(0)("IValue"))
End If
dgvSrch.Rows(0).Cells(2).Value = ""
dgvSrch.Rows(0).Cells(3).Value = ""
Dim str As String = "رصيد افتتاحي"
If MainClass.Language = "en" Then
str = "Open Balance"
End If
dgvSrch.Rows(0).Cells(4).Value = str
End If
End If
Dim da As New SqlDataAdapter("select Restrictions.id,Restrictions.date,sum(Restrictions_Sub.dept) as dept " & _
",sum(Restrictions_Sub.credit) as credit,Restrictions.notes from Restrictions,Restrictions_Sub " & _
"where " & CondBranch & " Restrictions.IS_Deleted=0 and Restrictions.state=1 and Restrictions.date>=@date1 and Restrictions.date<=@date2 " & _
"and Restrictions.id=Restrictions_Sub.res_id " & cond & _
" group by Restrictions.id,Restrictions.date,Restrictions.notes,Restrictions_Sub.acc_no", conn)
da.SelectCommand.Parameters.Add("@date1", SqlDbType.DateTime).Value = txtDateFrom.Value.ToShortDateString
Dim date2 As DateTime = txtDateTo.Value.AddHours(24)
da.SelectCommand.Parameters.Add("@date2", SqlDbType.DateTime).Value = date2
Dim dt As New DataTable
da.Fill(dt)
For i = 0 To dt.Rows.Count - 1
dgvSrch.Rows.Add()
dgvSrch.Rows(dgvSrch.Rows.Count - 1).Cells(0).Value = String.Format("{0:0.#,##.##}", Convert.ToDouble(dt.Rows(i)("dept")))
dgvSrch.Rows(dgvSrch.Rows.Count - 1).Cells(1).Value = String.Format("{0:0.#,##.##}", Convert.ToDouble(dt.Rows(i)("credit")))
dgvSrch.Rows(dgvSrch.Rows.Count - 1).Cells(2).Value = dt.Rows(i)("id")
dgvSrch.Rows(dgvSrch.Rows.Count - 1).Cells(3).Value = Convert.ToDateTime(dt.Rows(i)("date")).ToShortDateString
dgvSrch.Rows(dgvSrch.Rows.Count - 1).Cells(4).Value = dt.Rows(i)("notes")
_SumDept += Val(dt.Rows(i)("dept"))
_SumCredit += Val(dt.Rows(i)("credit"))
Next
txtTotDept.Text = String.Format("{0:0.#,##.##}", _SumDept)
txtTotCredit.Text = String.Format("{0:0.#,##.##}", _SumCredit)
If _SumDept > _SumCredit Then
txtBalance1.Text = String.Format("{0:0.#,##.##}", Math.Round((_SumDept - _SumCredit), 3))
ElseIf _SumCredit > _SumDept Then
txtBalance2.Text = String.Format("{0:0.#,##.##}", Math.Round((_SumCredit - _SumDept), 3))
ElseIf _SumDept = _SumCredit Then
txtBalance1.Text = "0"
txtBalance2.Text = "0"
End If
If rdDebt.Checked Then
dgvSrch.Columns(0).Visible = True
dgvSrch.Columns(1).Visible = False
txtTotDept.Visible = True
txtBalance1.Visible = True
txtTotCredit.Visible = False
txtBalance2.Visible = False
'TextBox4.Width = 790
'TextBox5.Width = 790
ElseIf rdCredit.Checked Then
dgvSrch.Columns(1).Visible = True
dgvSrch.Columns(0).Visible = False
txtTotDept.Visible = False
txtBalance1.Visible = False
txtTotCredit.Visible = True
txtBalance2.Visible = True
'TextBox4.Width = 790
'TextBox5.Width = 790
' txtTotCredit.Left = 796
'txtBalance2.Left = 796
Else
dgvSrch.Columns(0).Visible = True
dgvSrch.Columns(1).Visible = True
txtTotDept.Visible = True
txtBalance1.Visible = True
txtTotCredit.Visible = True
txtBalance2.Visible = True
'TextBox4.Width = 704
'TextBox5.Width = 704
'txtTotCredit.Left = 708
'txtBalance2.Left = 708
End If
Catch ex As Exception
End Try
End Sub
Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
ShowAccount()
End Sub
Private Sub dgvSrch_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSrch.CellDoubleClick
Try
'frmRestrictions.MdiParent = Form1
frmRestrictions.Show()
frmRestrictions.Navigate("select * from Restrictions where id=" & dgvSrch.Rows(e.RowIndex).Cells(2).Value)
frmRestrictions.Activate()
Catch ex As Exception
End Try
End Sub
Private Sub dgvSrch_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSrch.CellClick
Try
If e.ColumnIndex = 5 Then
Dim da As New SqlDataAdapter("select doc_no,type from Restrictions where id=" & dgvSrch.Rows(e.RowIndex).Cells(2).Value, conn)
Dim dt As New DataTable
da.Fill(dt)
If dt.Rows(0)("type") = 1 Or dt.Rows(0)("type") = 2 Then
Form1.RibbonTab1.Ribbon.SelectedTab = Nothing
Dim frm As New frmSalePurch
'frm.MdiParent = Form1
If dt.Rows(0)("type") = 1 Then
frm.InvProc = 1
Else
frm.InvProc = 2
End If
frm.Show()
' frm.WindowState = FormWindowState.Maximized
frm.Navigate("select * from Inv where IS_Deleted=0 and proc_type=1 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 3 Or dt.Rows(0)("type") = 4 Then
Form1.RibbonTab1.Ribbon.SelectedTab = Nothing
Dim frm As New frmSalePurch
'frm.MdiParent = Form1
If dt.Rows(0)("type") = 3 Then
frm.InvProc = 1
Else
frm.InvProc = 2
End If
frm.Show()
'frm.WindowState = FormWindowState.Maximized
frm.Navigate("select * from Inv where IS_Deleted=0 and proc_type=2 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 5 Then
Dim frm As New frmSandQ
'frm.MdiParent = Form1
frm.Show()
frm.Navigate("select * from SandQ where IS_Deleted=0 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 6 Then
Dim frm As New frmSandD
'frm.MdiParent = Form1
frm.Show()
frm.Navigate("select * from SandD where IS_Deleted=0 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 7 Then
Dim frm As New frmSandQD
'frm.MdiParent = Form1
frm.Show()
frm.Navigate("select * from SandQD where IS_Deleted=0 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 8 Then
Dim frm As New frmSandSD
'frm.MdiParent = Form1
frm.Show()
frm.Navigate("select * from SandSD where IS_Deleted=0 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
ElseIf dt.Rows(0)("type") = 9 Then
Form1.RibbonTab1.Ribbon.SelectedTab = Nothing
Dim frm As New frmSalePurch
'frm.MdiParent = Form1
frm.Show()
' frm.WindowState = FormWindowState.Maximized
frm.Navigate("select * from Inv where IS_Deleted=0 and proc_type=3 and id=" & dt.Rows(0)("doc_no"))
frm.Activate()
End If
End If
Catch ex As Exception
End Try
End Sub
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
PrintRpt(2)
End Sub
Private Function GetEmpUserName(ByVal emp As Integer) As String
Dim da As New SqlDataAdapter("select users.username from users,Employees where users.emp=Employees.id and Employees.id=" & emp, conn)
Dim dt As New DataTable
da.Fill(dt)
If dt.Rows.Count > 0 Then
Return "" & dt.Rows(0)(0)
End If
Return ""
End Function
Private Sub PrintRpt(ByVal type As Integer)
If dgvSrch.Rows.Count = 0 Then
MessageBox.Show("الجدول فارغ", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
Dim dt As New DataTable
If rdAll.Checked Or rdDebt.Checked Then dt.Columns.Add("dept")
If rdAll.Checked Or rdCredit.Checked Then dt.Columns.Add("credit")
dt.Columns.Add("no")
dt.Columns.Add("date")
dt.Columns.Add("notes")
For i = 0 To dgvSrch.Rows.Count - 1
If rdAll.Checked Then dt.Rows.Add(dgvSrch.Rows(i).Cells(0).Value, dgvSrch.Rows(i).Cells(1).Value, dgvSrch.Rows(i).Cells(2).Value, dgvSrch.Rows(i).Cells(3).Value, dgvSrch.Rows(i).Cells(4).Value)
If rdDebt.Checked Then dt.Rows.Add(dgvSrch.Rows(i).Cells(0).Value, dgvSrch.Rows(i).Cells(2).Value, dgvSrch.Rows(i).Cells(3).Value, dgvSrch.Rows(i).Cells(4).Value)
If rdCredit.Checked Then dt.Rows.Add(dgvSrch.Rows(i).Cells(1).Value, dgvSrch.Rows(i).Cells(2).Value, dgvSrch.Rows(i).Cells(3).Value, dgvSrch.Rows(i).Cells(4).Value)
Next
Dim rpt As New Object
If MainClass.Language = "ar" Then
rpt = New rptAccountBalance
Else
rpt = New rptAccountBalance___EN
End If
rpt.SetDataSource(dt)
Dim acc As String = "الكل"
If chkAll.Checked = False Then
acc = cmbAccounts.Text
End If
Dim txtacc As TextObject = CType(rpt.ReportDefinition.Sections(1).ReportObjects("txtacc"), TextObject)
txtacc.Text = acc
Dim txtdate1 As TextObject = CType(rpt.ReportDefinition.Sections(1).ReportObjects("txtDateFrom"), TextObject)
txtdate1.Text = txtDateFrom.Value.ToShortDateString
Dim txtdate2 As TextObject = CType(rpt.ReportDefinition.Sections(1).ReportObjects("txtDateTo"), TextObject)
txtdate2.Text = txtDateTo.Value.ToShortDateString
Dim txt_user As TextObject = CType(rpt.ReportDefinition.Sections(2).ReportObjects("txtuser"), TextObject)
txt_user.Text = GetEmpUserName(MainClass.EmpNo)
If rdDebt.Checked Or rdAll.Checked Then
Dim txtsum1 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum1"), TextObject)
txtsum1.Text = txtTotDept.Text
Dim txtsum3 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum3"), TextObject)
txtsum3.Text = txtBalance1.Text
End If
If rdCredit.Checked Or rdAll.Checked Then
Dim txtsum2 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum2"), TextObject)
txtsum2.Text = txtTotCredit.Text
Dim txtsum4 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum4"), TextObject)
txtsum4.Text = txtBalance2.Text
End If
If rdDebt.Checked Or rdCredit.Checked Then
Dim _Line16 As LineObject = CType(rpt.ReportDefinition.Sections(2).ReportObjects("Line16"), LineObject)
_Line16.LineStyle = LineStyle.NoLine
'Dim Text11 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("Text11"), TextObject)
'Text11.Width = 4440
'Dim Text17 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("Text17"), TextObject)
'Text17.Width = 4440
End If
If rdDebt.Checked Then
Dim txt5 As TextObject = CType(rpt.ReportDefinition.Sections(2).ReportObjects("Text5"), TextObject)
txt5.Left = 8550
Dim dept1 As FieldObject = CType(rpt.ReportDefinition.Sections(3).ReportObjects("dept1"), FieldObject)
dept1.Left = 8550
Dim txtsum1 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum1"), TextObject)
txtsum1.Left = 7680
txtsum1.Width = 3680
Dim txtsum3 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum3"), TextObject)
txtsum3.Left = 7680
txtsum3.Width = 3680
Dim txtsum2 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum2"), TextObject)
txtsum2.Left = 15000
Dim txtsum4 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum4"), TextObject)
txtsum4.Left = 15000
End If
If rdCredit.Checked Then
Dim txt4 As TextObject = CType(rpt.ReportDefinition.Sections(2).ReportObjects("Text4"), TextObject)
txt4.Left = 8550
Dim credit1 As FieldObject = CType(rpt.ReportDefinition.Sections(3).ReportObjects("credit1"), FieldObject)
credit1.Left = 8550
Dim txtsum2 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum2"), TextObject)
txtsum2.Left = 7680
txtsum2.Width = 3680
Dim txtsum4 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum4"), TextObject)
txtsum4.Left = 7680
txtsum4.Width = 3680
Dim txtsum1 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum1"), TextObject)
txtsum1.Left = 15000
Dim txtsum3 As TextObject = CType(rpt.ReportDefinition.Sections(4).ReportObjects("txtsum3"), TextObject)
txtsum3.Left = 15000
End If
Dim daCmp As New SqlDataAdapter("select * from Foundation", conn)
Dim dtCmp As New DataTable
daCmp.Fill(dtCmp)
rpt.Subreports("rptHeader").SetDataSource(dtCmp)
If dtCmp.Rows.Count > 0 Then
Dim txtadd As TextObject = CType(rpt.ReportDefinition.Sections(5).ReportObjects("txtAddress"), TextObject)
txtadd.Text = "" & dtCmp.Rows(0)("Address")
Dim txttel As TextObject = CType(rpt.ReportDefinition.Sections(5).ReportObjects("txtTel"), TextObject)
txttel.Text = "" & dtCmp.Rows(0)("Tel")
Dim txtmobile As TextObject = CType(rpt.ReportDefinition.Sections(5).ReportObjects("txtMobile"), TextObject)
txtmobile.Text = "" & dtCmp.Rows(0)("Mobile")
Dim txtfax As TextObject = CType(rpt.ReportDefinition.Sections(5).ReportObjects("txtFax"), TextObject)
txtfax.Text = "" & dtCmp.Rows(0)("Fax")
End If
Dim frm As New frmRptViewer
Dim viewer As New CrystalDecisions.Windows.Forms.CrystalReportViewer
frm.Controls.Add(viewer)
viewer.Dock = DockStyle.Fill
viewer.DisplayGroupTree = False
viewer.ReportSource = rpt
frm.WindowState = FormWindowState.Maximized
If type = 1 Then
frm.Show()
Else
Try
viewer.ShowLastPage()
Dim TotalPage As Integer = viewer.GetCurrentPageNumber()
viewer.ShowFirstPage()
rpt.PrintToPrinter(1, False, 1, TotalPage)
Catch ex As Exception
End Try
End If
End Sub
Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreview.Click
PrintRpt(1)
End Sub
Private Sub dgvSrch_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvSrch.CellContentClick
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
frmTaxRptPeriod.Show()
frmTaxRptPeriod.Activate()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
Dim exlapp As New Excel.Application
Dim exlworkbook As Excel.Workbook
Dim exlworksheet As Excel.Worksheet
Dim misvalue As Object = System.Reflection.Missing.Value
exlworkbook = exlapp.Workbooks.Add(misvalue)
exlworksheet = exlworkbook.Sheets("Sheet1")
For colhead As Integer = 0 To dgvSrch.ColumnCount - 1
exlworksheet.Cells(1, colhead + 1) = dgvSrch.Columns(colhead).HeaderText
Next
For i As Integer = 0 To dgvSrch.RowCount - 1 '2
For j As Integer = 0 To dgvSrch.ColumnCount - 1
exlworksheet.Cells(i + 2, j + 1) = dgvSrch.Rows(i).Cells(j).Value.ToString
Next
Next
SaveFileDialog1.Filter = "Excel Files|*.xlsx|Excel 2003|*.xls"
If SaveFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
exlworksheet.SaveAs(SaveFileDialog1.FileName)
End If
exlworkbook.Close()
exlapp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlapp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlworkbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(exlworksheet)
exlapp = Nothing
exlworkbook = Nothing
exlworksheet = Nothing
If MsgBox("هل تريد فتح الملف", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
Process.Start(SaveFileDialog1.FileName)
End If
End Sub
Private Sub Panel3_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel3.Paint
End Sub
End Class