01.Imports System.Data
02.Imports System.Data.OracleClient
03.Partial Class Sum_salary_grupt
04. Inherits System.Web.UI.Page
05. Dim Conn As OracleConnection
06. Dim Cmd As OracleCommand
07. Dim Cmd2 As OracleCommand
08. Dim Cmd3 As OracleCommand
09. Dim sqlx As String
10. Dim dtReader As OracleDataReader
11. Dim dtReader2 As OracleDataReader
12. Dim dtReader3 As OracleDataReader
13.
14. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
15. Dim strconnstring As String
16. strconnstring = "Data Source=pkmreg;User Id=pkmreg;Password=oracle;"
17. Conn = New OracleConnection(strconnstring)
18. Conn.Open()
19. End Sub
20.
21. Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
22. select_per()
23. End Sub
24.
25. Sub select_per()
26. If ComboBox1.Text <> "" And DropDownList2.Text <> "" Then
27.
28. sqlx = "SELECT OFFICERCODE FROM PEROFFICER WHERE FACULTYID = '" & Me.ComboBox1.Text & "'"
29. Cmd = New OracleCommand(sqlx, Conn)
30. dtReader = Cmd.ExecuteReader()
31. While dtReader.Read()
32. Dim sum_in As Single
33. Dim sum_ina As Single
34. Dim sumz As Single
35. Dim sum_ex As Single
36. Dim all_sum_pay As Single
37. Dim code As String = dtReader.Item("OFFICERCODE")
38.
39.
40. Dim sqlx_cou2 As String = "SELECT COUNT(REX_ID) AS COU_EX FROM PER_SALARY_ROUTINE_EX WHERE OFFICERCODE = '" & code & "' AND REX_IOD_CODE = '" & Me.DropDownList2.Text & "'"
41. Cmd = New OracleCommand(sqlx_cou2, Conn)
42. dtReader = Cmd.ExecuteReader
43. dtReader.Read()
44. Dim cou_ex As String = dtReader.Item("COU_EX")
45. If cou_ex <> "0" Then
46. Dim sqlx_ex As String = "SELECT SUM(REX_NUM) AS REX_SUM FROM PER_SALARY_ROUTINE_EX WHERE OFFICERCODE = '" & code & "' AND REX_IOD_CODE = '" & Me.DropDownList2.Text & "'"
47. Cmd3 = New OracleCommand(sqlx_ex, Conn)
48. dtReader3 = Cmd3.ExecuteReader
49. dtReader3.Read()
50. sum_ex = dtReader3.Item("REX_SUM")
51. MsgBox("pay : " & sum_ex & "code : " & code)
52. End If
53.
54. Try
55. Dim sqlx_cou1 As String = "SELECT COUNT(RIN_ID) AS COU_IN FROM PER_SALARY_ROUTINE_IN WHERE OFFICERCODE = '" & code & "'"
56. Cmd = New OracleCommand(sqlx_cou1, Conn)
57. dtReader = Cmd.ExecuteReader
58. dtReader.Read()
59. Dim cou_in As String = dtReader.Item("COU_IN")
60. If cou_in <> "0" Then
61. Dim sqlx_in As String = "SELECT SUM(RIN_NUM) AS RIN_SUM FROM PER_SALARY_ROUTINE_IN WHERE OFFICERCODE = '" & code & "'"
62. Cmd2 = New OracleCommand(sqlx_in, Conn)
63. dtReader2 = Cmd2.ExecuteReader
64. dtReader2.Read()
65. sum_in = dtReader2.Item("RIN_SUM")
66. End If
67.
68. Dim sqlx_cou3 As String = "SELECT COUNT(AD_ID) AS COU_INA FROM PER_SALARY_ROUTINE_ADD WHERE OFFICERCODE = '" & code & "' AND AD_IOD_CODE = '" & Me.DropDownList2.Text & "'"
69. Cmd = New OracleCommand(sqlx_cou3, Conn)
70. dtReader = Cmd.ExecuteReader
71. dtReader.Read()
72. Dim cou_inx As String = dtReader.Item("COU_INA")
73. If cou_inx <> "0" Then
74. Dim sqlx_in As String = "SELECT SUM(AD_NUM) AS ADD_SUM FROM PER_SALARY_ROUTINE_ADD WHERE OFFICERCODE = '" & code & "' AND AD_IOD_CODE = '" & Me.DropDownList2.Text & "'"
75. Cmd2 = New OracleCommand(sqlx_in, Conn)
76. dtReader2 = Cmd2.ExecuteReader
77. dtReader2.Read()
78. sum_ina = dtReader2.Item("ADD_SUM")
79. End If
80.
81. If sum_in <> "0" And sum_ina <> "0" Then
82. sumz = sum_in + sum_ina
83. Dim muney_pay1 As Single = sumz - sum_ex
84. all_sum_pay = muney_pay1
85. ElseIf sum_ina = "0" Then
86. Dim muney_pay2 As Single = sum_in - sum_ex
87. all_sum_pay = muney_pay2
88. End If
89.
90. Catch ex As Exception
91.
92. End Try
93. End While
94. End If
95. End Sub
96.End Class
