public Form1()
{
InitializeComponent();
PC.list = new List<PC>();
PC.total = new List<PC>();
data.Columns.Add("Id", "Id");
data.Columns.Add("PC", "PC");
data.Columns.Add("Inicio", "Inicio");
data.Columns.Add("Tiempo", "Tiempo");
data.Columns.Add("Total", "Total");
data.Columns[3].Width = 130;
data.Columns[3].Width = 130;
r = MSAConnection.read("select * from item");
while (r.Read())
{
String d = "";
PC p = new PC();
p.id = r.GetInt32(0);
p.precio = r.GetInt32(2);
if (!r.IsDBNull(3))
{
//d = r.GetDateTime(3).ToString();
DateTime x = r.GetDateTime(3);
d = x.Hour + ":" + x.Minute + ":" + x.Second;
p.inicio = r.GetDateTime(3);
PC.list.Add(p);
}
PC.total.Add(p);
data.Rows.Add(r.GetInt32(0), r.GetString(1), d);
}
// MessageBox.Show(PC.total.Count + "");
thetimer.Start();
}
// Constructor for searched volunteer holiday
public Holiday(uint gvhid)
{
// Declarations
uint gvoid = 0, modifiedby = 0;
DateTime datemodified = new DateTime(1901, 1, 1), start = new DateTime(1901, 1, 1), end = new DateTime(1901, 1, 1);
// Command
string query = string.Format("SELECT gvo_id, gvh_start, gvh_end, date_modified, modified_by " +
"FROM gfrc_volunteer_holiday WHERE gvh_id = {0}", gvhid);
try
{
using (conn)
{
conn.Open();
cmd = new OleDbCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
gvoid = Convert.ToUInt32(rdr.GetInt32(0));
start = rdr.GetDateTime(1);
if (!DateTime.TryParse(rdr.GetValue(2).ToString(), out end))
end = new DateTime(1901, 1, 1);
if (!DateTime.TryParse(rdr.GetValue(14).ToString(), out datemodified))
datemodified = new DateTime(1901, 1, 1);
if (!UInt32.TryParse(rdr.GetValue(15).ToString(), out modifiedby))
modifiedby = 0;
}
}
}
finally
{
if (rdr != null)
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
_gvhID = gvhid;
_gvoID = gvoid;
_Start = start;
_End = end;
_DateModified = datemodified;
_ModifiedBy = modifiedby;
}
public Vector<Holiday> listHolidays(uint gvoid)
{
// Declarations
Vector<Holiday> quals = new Vector<Holiday>();
uint gvhid = 0, modifiedby = 0;
DateTime datemodified = new DateTime(1901, 1, 1), start = new DateTime(1901, 1, 1), end = new DateTime(1901, 1, 1);
Holiday input = new Holiday();
// Declarations
// Command
string query = string.Format("SELECT gvh_id, gvh_start, gvh_end, date_modified, modified_by " +
"FROM gfrc_volunteer_holiday WHERE gvo_id = {0}", gvoid);
try
{
using (conn)
{
conn.Open();
cmd = new OleDbCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (UInt32.TryParse(rdr.GetValue(0).ToString(), out gvhid))
{
start = rdr.GetDateTime(1);
if (!DateTime.TryParse(rdr.GetValue(2).ToString(), out end))
end = new DateTime(1901, 1, 1);
if (!DateTime.TryParse(rdr.GetValue(14).ToString(), out datemodified))
datemodified = new DateTime(1901, 1, 1);
if (!UInt32.TryParse(rdr.GetValue(15).ToString(), out modifiedby))
modifiedby = 0;
input = new Holiday(gvhid, gvoid, start, end, datemodified, modifiedby);
quals.Add(input);
}
}
}
}
finally
{
if (rdr != null)
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return quals;
}
internal static List<User> searchUsers(string key)
{
List<User> list = new List<User>();
try
{
cmd = new OleDbCommand("SELECT * FROM Users WHERE Username LIKE '%" + key + "%';", conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
User u = new User(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetDateTime(8), reader.GetInt32(9), reader.GetInt32(10));
list.Add(u);
}
return list;
}
catch (Exception e)
{
MessageBox.Show("Error getting usersList in searchUsers: " + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
internal static List<User> GetUsers()
{
List<User> usersList = new List<User>();
try
{
cmd = new OleDbCommand("SELECT * FROM Users ORDER BY ID;", conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
User u = new User(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetDateTime(8), reader.GetInt32(9), reader.GetInt32(10));
usersList.Add(u);
}
reader.Close();
return usersList;
}
catch (Exception e)
{
MessageBox.Show("Error getting usersList: " + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
internal static List<Order> getOrderList()
{
List<Order> ol = new List<Order>();
try
{
//IFNULL(SUM(score),'N')
cmd = new OleDbCommand("SELECT Orders.UserID, Orders.FoodID, Orders.Quantity, Orders.TotalExpense, Orders.OrderDate, Orders.ReportID, Orders.Status, FoodList.FoodName, Users.Username, Orders.ID FROM (Users INNER JOIN Orders ON Users.ID = Orders.UserID) INNER JOIN FoodList ON Orders.FoodID = FoodList.ID ORDER BY Orders.OrderDate;", conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
Order o = new Order(reader.GetInt32(0),
reader.GetInt32(1),
reader.GetInt32(2),
reader.GetInt32(3),
reader.GetDateTime(4), reader.GetInt32(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetInt32(9));
ol.Add(o);
}
reader.Close();
}
catch (Exception ex)
{
//runs perfectly fine but theres an ERROR. I DONT KNOW WHY --> line 295 (reader.GetInt32(0)) OH NO!
MessageBox.Show("getOrderList() error: " + ex.ToString());
}
return ol;
}
public Result getUserInfo(string username)
{
Result r = new Result();
try
{
cmd = new OleDbCommand("SELECT * FROM Users WHERE Username = ?;", conn);
cmd.Parameters.Add("Username", OleDbType.VarChar).Value = username;
reader = cmd.ExecuteReader();
if (reader.Read())
{
User u = new User(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetDateTime(8), reader.GetInt32(9), reader.GetInt32(10));
r.user = u;
r.success = true;
}
return r;
}
catch (Exception e)
{
MessageBox.Show("Error getting userInfo: " + e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
protected List<DBItem> GetSubmissionsQueryCallback(OleDbDataReader reader)
{
List<DBItem> returnList = new List<DBItem>();
if (reader.HasRows)
{
while (reader.Read())
{
DBItem item = new DBItem();
item.ID = reader.GetInt32(reader.GetOrdinal("ID"));
item.User = reader.GetString(reader.GetOrdinal("User"));
item.DateTime = reader.GetDateTime(reader.GetOrdinal("DateTime"));
item.Person = reader.GetString(reader.GetOrdinal("Person"));
item.Rating = reader.GetString(reader.GetOrdinal("Rating"));
item.Description = reader.GetString(reader.GetOrdinal("Description"));
item.Votes = reader.GetString(reader.GetOrdinal("Votes"));
returnList.Add(item);
}
}
return returnList;
}
// Constructor for searched volunteer
public Availability(uint gvoid)
{
// Declarations
uint gvaid = 0, reqhours = 0, modifiedby = 0;
bool mon = false, tues = false, wed = false, thur = false, fri = false, fillin = false;
DateTime datemodified = new DateTime(1901, 1, 1), start = new DateTime(1901, 1, 1), end = new DateTime(1901, 1, 1);
// Command
string query = string.Format("SELECT gva_id, gva_start, gva_end, gva_req_hours, gva_mon, gva_tues, gva_wed, gva_thur, gva_fri, gva_fill_in, date_modified, modified_by " +
"FROM gfrc_volunteer_avail WHERE gvo_id = {0}", gvoid);
try
{
using (conn)
{
conn.Open();
cmd = new OleDbCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
UInt32.TryParse(rdr.GetValue(0).ToString(), out gvaid);
start = rdr.GetDateTime(1);
if (!DateTime.TryParse(rdr.GetValue(2).ToString(), out end))
end = new DateTime(1901, 1, 1);
UInt32.TryParse(rdr.GetValue(3).ToString(), out reqhours);
mon = rdr.GetBoolean(4);
tues = rdr.GetBoolean(5);
wed = rdr.GetBoolean(6);
thur = rdr.GetBoolean(7);
fri = rdr.GetBoolean(8);
fillin = rdr.GetBoolean(9);
if (!DateTime.TryParse(rdr.GetValue(10).ToString(), out datemodified))
datemodified = new DateTime(1901, 1, 1);
if (!UInt32.TryParse(rdr.GetValue(11).ToString(), out modifiedby))
modifiedby = 0;
}
}
}
finally
{
if (rdr != null)
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
_gvaID = gvaid;
_gvoID = gvoid;
_Start = start;
_End = end;
_ReqHours = reqhours;
_Mon = mon;
_Tues = tues;
_Wed = wed;
_Thur = thur;
_Fri = fri;
_FillIn = fillin;
_DateModified = datemodified;
_ModifiedBy = modifiedby;
}
public void SimpleValue()
{
try
{
BeginCase("check simple value");
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
cmd = new OleDbCommand("Select BirthDate From Employees where EmployeeID = 100", con);
rdr = cmd.ExecuteReader();
rdr.Read();
DateTime dt = rdr.GetDateTime(0); //will be 1988-May-31 15:33:44
Compare(dt,new DateTime(1988,5,31,15,33,44,00));
}
catch(Exception ex)
{
exp = ex;
}
finally
{
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
}
EndCase(exp);
exp = null;
}
}
// Constructor for searched volunteer
public Volunteer(uint gvoid)
{
// Declarations
uint modifiedby = 0;
string name = null, address = null, postal = null, email = null, home = null, mobile = null;
string status = null, referred = null, refdoc = null, poldoc = null, application = null;
bool police = false, induction = false;
DateTime datemodified = new DateTime(1901, 1, 1), dob = new DateTime(1901, 1, 1);
// Command
string query = string.Format("SELECT gvo_name, gvo_address, gvo_postal_address, gvo_email, gvo_home_ph, gvo_mobile_ph, gvo_dob, gvo_status, gvo_referred, gvo_referred_doc, " +
"gvo_police, gvo_police_doc, gvo_induction, gvo_application, date_modified, modified_by FROM gfrc_volunteer" +
" WHERE (gvo_id = {0})", gvoid);
try
{
using (conn)
{
conn.Open();
cmd = new OleDbCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
name = rdr.GetString(0);
address = rdr.GetString(1);
postal = rdr.GetString(2);
email = rdr.GetString(3);
home = rdr.GetString(4);
mobile = rdr.GetString(5);
dob = rdr.GetDateTime(6);
status = rdr.GetString(7);
referred = rdr.GetString(8);
refdoc = rdr.GetString(9);
police = rdr.GetBoolean(10);
poldoc = rdr.GetString(11);
induction = rdr.GetBoolean(12);
application = rdr.GetString(13);
if (!DateTime.TryParse(rdr.GetValue(14).ToString(), out datemodified))
datemodified = new DateTime(1901, 1, 1);
if (!UInt32.TryParse(rdr.GetValue(15).ToString(), out modifiedby))
modifiedby = 0;
}
}
}
catch (Exception e)
{
name = "";
address = "";
postal = "";
email = "";
home = "";
mobile = "";
dob = new DateTime(1901, 1, 1);
status = "";
referred = "";
refdoc = "";
police = false;
poldoc = "";
induction = false;
application = "";
datemodified = new DateTime(1901, 1, 1);
modifiedby = 0;
}
finally
{
if (rdr != null)
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
_gvoID = gvoid;
_Name = name;
_Address = address;
_PostalAddress = postal;
_Email = email;
_HomePh = home;
_MobilePh = mobile;
_DOB = dob;
_Status = status;
_Referred = referred;
_ReferredDoc = refdoc;
_Police = police;
_PoliceDoc = poldoc;
_Induction = induction;
_Application = application;
_DateModified = datemodified;
_ModifiedBy = modifiedby;
}
