-
Notifications
You must be signed in to change notification settings - Fork 0
/
Default.aspx.cs
240 lines (215 loc) · 10.3 KB
/
Default.aspx.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Drawing;
namespace HBR_Port_GUI
{
public partial class _Default : Page
{
static string ConnectString = "provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=Z:\\ba372\\Project\\BackendCode\\BA372.accdb";
//storing Driver and DB location
static System.Data.OleDb.OleDbConnection Connection = new System.Data.OleDb.OleDbConnection(ConnectString);
//method to return id value
public static string GetID(String SQLquery)
{
//make an Oledbcommandobject
OleDbCommand cmd = null;
//make an Oledbdatareader
System.Data.OleDb.OleDbDataReader Reader = null;
string PrimaryKeyID = null;
//connects to the database and catches if there is a problem connecting
cmd = new OleDbCommand(SQLquery, Connection);
try
{
Reader = cmd.ExecuteReader();
}
catch (InvalidOperationException)
{
Connection.Close();
}
Reader.Close();
return PrimaryKeyID;
}
public static void InsertsSqlCommand(string SQLquery)
{
//make an Oledbcommandobject
OleDbCommand cmd = null;
cmd = new OleDbCommand(SQLquery, Connection);
try
{
cmd.ExecuteNonQuery();
}
catch (InvalidOperationException)
{
Connection.Close();
}
}
public List<string> GetSqlList(String SQLquery)
{
//make an Oledbcommandobject
OleDbCommand cmd = null;
//make an Oledbdatareader
System.Data.OleDb.OleDbDataReader Reader = null;
//connects to the database and catches if there is a problem connecting
List<string> sqlViewRequests = new List<string>(); //Initializing list so things can be stored in it
cmd = new OleDbCommand(SQLquery, Connection);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read()) // while there is still information to be read, pull that information out and add it to the list
{
sqlViewRequests.Add(Convert.ToString((Reader[0]))); //Converting reader output to string
}
Reader.Close();
}
catch (InvalidOperationException)
{
Connection.Close();
}
Reader.Close();
return sqlViewRequests;
}
public static List<string> GetReqInfo(int reqID)
{
//This Function is used for getting Req+ID info for populating rows. Each item pulls out into a list that can be referenced individually.
{
//make an Oledbcommandobject
OleDbCommand cmd = null;
//make an Oledbdatareader
System.Data.OleDb.OleDbDataReader Reader = null;
//Making Query
string query = "SELECT * FROM Requests WHERE Req_ID =" + reqID;
//connects to the database and catches if there is a problem connecting
List<string> reqIDList = new List<string>(); //Initializing list so things can be stored in it
cmd = new OleDbCommand(query, Connection);
try
{
Reader = cmd.ExecuteReader();
while (Reader.Read()) // while there is still information to be read, pull that information out and add it to the list
{
reqIDList.Add(Convert.ToString(Reader["Travel"]));
reqIDList.Add(Convert.ToString(Reader["Est_Total_Cost"]));
reqIDList.Add(Convert.ToString(Reader["Emp_ID"]));
reqIDList.Add(Convert.ToString(Reader["Description"]));
reqIDList.Add(Convert.ToString(Reader["Begin_Date"]));
reqIDList.Add(Convert.ToString(Reader["End_Date"]));
reqIDList.Add(Convert.ToString(Reader["Decision_Date"]));
reqIDList.Add(Convert.ToString(Reader["Status"]));
reqIDList.Add(Convert.ToString(Reader["Current_Approval"]));
}
Reader.Close();
}
catch (InvalidOperationException)
{
Connection.Close();
}
Reader.Close();
return reqIDList;
}
}
protected void Page_Load(object sender, EventArgs e)
{
int SQLFMORD = 0;
string po = "";
//connection
int number_Rows = 5;
string PositionView = Request.QueryString["rank"];
if (PositionView == "fm" || PositionView == "director")
{
//the connections is open
try
{
Console.WriteLine(ConnectString);
Connection.Open();
Console.WriteLine("Database connection open...");
}
catch (System.Exception er) //if opening the connection failed
{
Console.WriteLine("Problems opening..." + er.Message);
Console.WriteLine("Connection aborted");
return;
}
if (PositionView == "fm")
{
SQLFMORD = 1;
}
if (PositionView == "director")
{
SQLFMORD = 3;
}
string query = "SELECT REQ_ID FROM Requests WHERE Current_Approval=" + Convert.ToString(SQLFMORD) + " AND Status=\"In-Progress\""; //building SQL query that will run agaisn't database using sqlView variable.
List<int> sqlViewRequests = GetSqlList(query).Select(s => int.Parse(s)).ToList(); //Using GetSqlList function to populate the sqlViewRequests list. The select statement on the end is being used to parse the data into int's
//loops over each ID
foreach (int i in sqlViewRequests)
{
//
string est_Total_cost = "N/A";
string travel = "N/A";
string emp_ID = "N/A";
string description = "N/A";
string beg_date = "N/A";
string end_date = "N/A";
string status = "N/A";
string Current_approval = "N/A";
List<string> testingReqID = GetReqInfo(i).ToList(); //This line pulls the info from the "GetReqInfo() function and adds it to another list I just made using the .ToList command. Note you can also clear lists using ListName.Clear();
travel = testingReqID[0];
est_Total_cost = testingReqID[1];
emp_ID = testingReqID[2];
description = testingReqID[3];
beg_date = testingReqID[4];
end_date = testingReqID[5];
status = testingReqID[6];
Current_approval = testingReqID[7];
//Puts variables in HTML format and uses index i to give variables distinct identifiers
po = po + "<tr>" +
"<Form>" +
"<th scope=\"row\"><input type=\"radio\" name=\"Decision" + i + "\" value=\"Approve\"></button></th>" +
"<td><input type=\"radio\" name=\"Decision" + i + "\" value=\"DENY\"></button></td>" +
"</Form>" +
"<td>" + sqlViewRequests[i-1] + "</td>" + //subtract 1 because the list starts at 1 but we need to call it by elements that start at 0
"<td>" + est_Total_cost + "</td>" +
"<td>" + emp_ID + "</td>" +
"<td>" + beg_date + "</td>" +
"<td>" + end_date + "</td>" +
"<td>" + Current_approval + "</td>" +
"<td>" + description + "</td>" +
"</tr>";
}
//for (int i = 1; i <= number_Rows; i++)
//{
// // po = purchase order
// // string (purchase order) is null, then for loop.
// // for int i, as long as in is less than 5 (will change to variable that represents total number of po's)
// // will update the 1-7 to be variable for each purchase order variable
// po = po + "<tr>" +
// "<Form>" +
// "<th scope=\"row\"><input type=\"radio\" name=\"Decision" + i + "\" value=\"Approve\"></button></th>" +
// "<td><input type=\"radio\" name=\"Decision" + i + "\" value=\"DENY\"></button></td>" +
// "</Form>" +
// "<td>"+sqlViewRequests[i]+"</td>" +
// "<td>Est_Cost</td>" +
// "<td>Emp_ID</td>" +
// "<td>Beg_Date</td>" +
// "<td>End_Date</td>" +
// "<td>Decision</td>" +
// "<td>Desc</td>" +
// "</tr>";
//} // 8 total categories (-status)
}
else
{
}
Somename.Text = po;
}
}
}