public static bool validateLogin(UserLog ul)
{
DBConnector dbcon = new DBConnector();
dbcon.openConnection();
//try {
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT * FROM user WHERE username='******' AND password=MD5('" + ul.getPassword() + "')";
cmd.Connection = dbcon.connection;
MySqlDataReader login = cmd.ExecuteReader();
if (login.Read())
{
LoginSession.setSession(login.GetString("iduser"));
//login.Close();
dbcon.closeConnection();
return true;
}
else
{
//login.Close();
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e){
//int errorcode = e.Number;
//return false;
//}
}
public static bool addWorkingExperience(WorkingExperience we)
{
DBConnector dbcon = new DBConnector();
try
{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO working_experience (institute, address, telephone, email, department, contact, date_from, date_to, date_perma, resign_reason, responsibility, occupation_relevant, award, employee_idemployee) VALUES (N'" + we.institute + "', N'" + we.address + "', N'" + we.telephone + "', N'" + we.email + "', N'" + we.department + "', N'" + we.contact + "', '" + we.getDate_from().ToString("yyyy-MM-dd") + "', '" + we.getDate_to().ToString("yyyy-MM-dd") + "', '" + we.getDate_perma().ToString("yyyy-MM-dd") + "', N'" + we.resign_reason + "', N'" + we.responsibility + "', " + we.occupation_relevant + ", N'" + we.award + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}
}
public static bool addDependentDetails(DependentDetails dd)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO dependent_details (salutation, full_name, relation, date_of_post, nic_no, personal_tp, office_tp, personal_address, official_address, education, status, note, birth_certificate, marriage_certificate, deathade, doctor, employee_idemployee) VALUES (N'" + dd.salutation + "', N'" + dd.full_name + "', N'" + dd.relation + "', '" + dd.getDate_of_post().ToString("yyyy-MM-dd") + "', N'" + dd.nic_no + "', N'" + dd.personal_tp + "', N'" + dd.office_tp + "', N'" + dd.personal_address + "', N'" + dd.official_address + "', N'" + dd.education + "', N'" + dd.status + "', N'" + dd.note + "', N'" + dd.birth_certificate + "', N'" + dd.marriage_certificate + "', " + dd.deathade + ", " + dd.doctor + ", " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public static bool addRemuneration(Remuneration r)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO remuneration (rank, post, salary_grade, salary_slip_no, basic_salary, transport_allowance, fuel_allowance, mobile_phone_allowance, housing_allowance, other_allowance, salary_increase_date, salary_increase_amount, current_total_salary, employee_idemployee) VALUES (N'" + r.rank + "', N'" + r.post + "', N'" + r.salary_grade + "', N'" + r.salary_slip_no + "', " + r.basic_salary + ", " + r.transport_allowance + ", " + r.fuel_allowance + ", " + r.mobile_phone_allowance + ", " + r.housing_allowance + ", " + r.other_allowance + ", '" + r.getsalary_increase_date().ToString("yyyy-MM-dd") + "', " + r.salary_increase_amount + ", " + r.current_total_salary + ", " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public static bool addPensionAndDeath(PensionAndDeath pd)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO pension_death (retired, dead, died_date, informed_date, note, employee_idemployee) VALUES (" + pd.retired + ", " + pd.dead + ", '" + pd.died_date.ToString("yyyy-MM-dd") + "', '" + pd.informed_date.ToString("yyyy-MM-dd") + "', N'" + pd.note + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public static bool addNotification(Notification n)
{
DBConnector dbcon = new DBConnector();
try
{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO notification (title, content, date, employee_name, employee_number, employee_idemployee, user_iduser) VALUES (N'" + n.Title + "', N'" + n.Content + "', '" + n.Date.ToString("yyyy-MM-dd") + "', N'" + n.Employee_name + "', N'" + n.Employee_number + "', " + n.Employee_idemployee + ", " + n.User_iduser + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
}
catch (MySqlException e)
{
int errorcode = e.Number;
Console.Write(e.Message + "\n");
dbcon.closeConnection();
return false;
}
}
public static bool addTraining(Training t)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO training (course_name, course_type, institute, payments, started_date, ending_date, extended_days, new_ending_date, country, result, employee_idemployee) VALUES (N'" + t.course_name + "', N'" + t.course_type + "', N'" + t.institute + "', N'" + t.payments + "', '" + t.started_date.ToString("yyyy-MM-dd") + "', '" + t.ending_date.ToString("yyyy-MM-dd") + "', N'" + t.extended_days + "', '" + t.new_ending_date.ToString("yyyy-MM-dd") + "', N'" + t.country + "', N'" + t.result + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public List<TicketResource> GetActiveTicketsBetweenDates(DateTime startDate, DateTime endDate)
{
DBConnector connector = new DBConnector();
MySqlCommand getTicketsCommand = new MySqlCommand();
getTicketsCommand.Connection = connector.connection;
getTicketsCommand.CommandText = "SELECT * from Tickets join Customer on Tickets.CustomerId = Customer.id where date_ready >= @startDate AND date_ready <= @endDate AND status = 'a'";
getTicketsCommand.Parameters.AddWithValue("@startDate", ConvertDateTimeToUTCString(startDate));
getTicketsCommand.Parameters.AddWithValue("@endDate", ConvertDateTimeToUTCString(endDate));
List<TicketResource> ticketResources = new List<TicketResource>();
try
{
MySqlDataReader reader = getTicketsCommand.ExecuteReader();
while (reader.Read())
{
ticketResources.Add(ConvertSQLReaderRowToTicketResource(reader));
}
reader.Close();
connector.CloseConnection();
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
return ticketResources;
}
public static bool addJobStatusRecord()
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO job_status (contract_started_date, contract_ended_date, probation_started_date, probation_ended_date, probation_to_permanent_date, contract_to_permanent_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public CustomerResource getCustomerById(int customerId)
{
DBConnector connector = new DBConnector();
MySqlCommand getTicketCommand = new MySqlCommand();
getTicketCommand.Connection = connector.connection;
getTicketCommand.CommandText = "select * from Customer where id = @customer_id LIMIT 1";
getTicketCommand.Parameters.AddWithValue("@customer_id", customerId);
CustomerResource customerToReturn = null;
try
{
MySqlDataReader reader = getTicketCommand.ExecuteReader();
while (reader.Read())
{
customerToReturn = ConvertSQLReaderRowToTicketResource(reader);
}
reader.Close();
connector.CloseConnection();
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
return customerToReturn;
}
public static bool addMembership(Membership m)
{
DBConnector dbcon = new DBConnector();
try
{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO membership (institute, post_name, method, member_id, contribution, begin_date, renewal_date, status, personal_payment, active_date_person, institutional_payment, active_date_insti, employee_idemployee) VALUES (N'" + m.institute + "', N'" + m.post_name + "', N'" + m.method + "', N'" + m.member_id + "', N'" + m.contribution + "', '" + m.getBegin_date().ToString("yyyy-MM-dd") + "', '" + m.getRenewal_date().ToString("yyyy-MM-dd") + "', N'" + m.status + "', " + m.personal_payment + ", '" + m.getActive_date_person().ToString("yyyy-MM-dd") + "', " + m.institutional_payment + ", '" + m.getActive_date_insti().ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}
}
public static bool addFinanceInsurance(FinanceInsurance fi)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO finance_insurance (type, value, begin_date, end_date, note, employee_idemployee) VALUES (N'" + fi.type + "', " + fi.value + ", '" + fi.getBegin_date().ToString("yyyy-MM-dd") + "', '" + fi.getEnd_date().ToString("yyyy-MM-dd") + "', N'" + fi.note + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public static void Cleanup()
{
try
{
if(LogfileScanner != null)
{
LogfileScanner.Dispose();
LogfileScanner = null;
}
if(DBCon != null)
{
DBCon.Dispose();
DBCon = null;
}
// if EliteDBProcess is not null the process is created
// by this program, so we also have to do the cleanup
if((EliteDBProcess != null) && (!EliteDBProcess.WasRunning))
{
String user = IniFile.GetValue("DB_Connection", "User", "RN_User");
String pass = IniFile.GetValue("DB_Connection", "Pass", "Elite");
EliteDBProcess.StopServer(user, pass);
EliteDBProcess.Dispose();
EliteDBProcess = null;
}
}
catch (Exception ex)
{
throw new Exception("Error while cleaning up", ex);
}
}
public static Qualification getQualification()
{
//try
//{
DBConnector dbcon = new DBConnector();
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT * FROM qualification WHERE employee_idemployee=" + Employee.employee_id;
cmd.Connection = dbcon.connection;
MySqlDataReader reader = cmd.ExecuteReader();
Console.Write(Employee.employee_id + "\n");
Qualification q = null;
if (reader.Read())
{
q = new Qualification();
q.q_id = int.Parse(reader["idqualification"].ToString());
q.institute = reader["institute"].ToString();
q.months = reader["months"].ToString();
q.status = reader["status"].ToString();
q.note = reader["note"].ToString();
q.qualification = reader["qualification"].ToString();
q.year = reader["year"].ToString();
q.qualification_no = reader["qualification_no"].ToString();
if (reader["occupation_relevant"].ToString() == "True") { q.occupation_relevant = true; }
else { q.occupation_relevant = false; }
if (reader["highest_qualification"].ToString() == "True") { q.highest_qualification = true; }
else { q.highest_qualification = false; }
}
reader.Close();
dbcon.closeConnection();
return q;
}
else
{
return null;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//return null;
//}
}
public static bool addResignationRecord()
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO resignation (letter_submitted_date, resignation_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public static bool addPassport(Passport p)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO passport (rank, post, passport_no, place_of_issue, date_of_issue, date_of_renewal, status, employee_idemployee) VALUES (N'" + p.rank + "', N'" + p.post + "', N'" + p.number + "', N'" + p.place_of_issue + "', '" + p.getdate_of_issue().ToString("yyyy-MM-dd") + "', '" + p.getdate_of_renewal().ToString("yyyy-MM-dd") + "', " + p.status + ", " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public TicketAlterationResource GetAlterationsByTicketId(int ticketId)
{
DBConnector connector = new DBConnector();
MySqlCommand getAlterationsCommand = new MySqlCommand();
getAlterationsCommand.Connection = connector.connection;
getAlterationsCommand.CommandText = @"SELECT * from Ticket_Alterations where ticket_id = @ticket_id ORDER BY order_index";
getAlterationsCommand.Parameters.AddWithValue("@ticket_id", ticketId);
List<TicketAlterationResourceItem> alterationItems = new List<TicketAlterationResourceItem>();
try
{
MySqlDataReader reader = getAlterationsCommand.ExecuteReader();
while (reader.Read())
{
alterationItems.Add(new TicketAlterationResourceItem
{
Price = Convert.ToDouble(reader["price"]),
Quantity = Convert.ToInt32(reader["quantity"]),
Description = (String)reader["description"],
Taxable = Convert.ToInt32(reader["taxable"])
});
}
reader.Close();
connector.CloseConnection();
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
TicketAlterationResource alterationRes = TicketAlterationFactory.CreateTicketAlterationResource(alterationItems);
return alterationRes;
}
protected void ButtonLogin_Click(object sender, EventArgs e)
{
String strSql = "Select count(*) from Users where ID =" + TextBoxUserName.Text + " And Pwd = '" +
Helper.CalMD5(TextBoxPassword.Text) + "'";
DBConnector db = new DBConnector();
if ((Int32)db.ExecuteObject(strSql) > 0)
{
Helper.UserId = int.Parse(TextBoxUserName.Text);
strSql = "SELECT Name FROM Users WHERE ID = " + Helper.UserId;
Helper.UserName = (String)db.ExecuteObject(strSql);
strSql = "SELECT IsAdmin FROM Users WHERE ID = " + Helper.UserId;
Helper.IsAdmin = (bool)db.ExecuteObject(strSql);
if (Helper.IsAdmin)
{
MultiView1.SetActiveView(ViewAdministrator);
LabelAdminName.Text = Helper.UserName;
LinkButton2.Visible = true;
}
else
{
MultiView1.SetActiveView(ViewUsers);
LabelUserName.Text = Helper.UserName;
LinkButton2.Visible = true;
}
Response.Redirect(".");
}
else
{
Helper.MsgBox("登陆失败");
}
}
public static bool addEmergencyContact(EmergencyContact ec)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO emergency_contact (salutation, full_name, relation, nic_no, personal_address, official_address, personal_tp, official_tp, mobile_no, employee_no, int_ext, priority, employee_idemployee) VALUES (N'" + ec.salutation + "', N'" + ec.full_name + "', N'" + ec.relation + "', '" + ec.nic_no + "', N'" + ec.personal_address + "', N'" + ec.official_address + "', N'" + ec.personal_tp + "', N'" + ec.office_tp + "', N'" + ec.mobile_no + "', N'" + ec.employee_no + "', N'" + ec.int_ext + "', N'" + ec.priority + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public static bool addQualification(Qualification q)
{
DBConnector dbcon = new DBConnector();
try
{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO qualification (qualification_no, institute, months, occupation_relevant, highest_qualification, status, note, qualification, year, employee_idemployee) VALUES (N'" + q.qualification_no + "', N'" + q.institute + "', N'" + q.months + "', " + q.occupation_relevant + ", " + q.highest_qualification + ", N'" + q.status + "', N'" + q.note + "', N'" + q.qualification + "', N'" + q.year + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}
}
public static void setSession(string userid)
{
LoginInfo.UserID = userid;
LoginInfo.inTime = DateTime.Now;
LoginInfo.computer_name = Environment.MachineName;
LoginInfo.ipAddress = Dns.GetHostAddresses(Environment.MachineName)[0].ToString();
DBConnector dbcon = new DBConnector();
dbcon.openConnection();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO login_session (in_time, computer_name, ip_address, user_iduser) VALUES ('" + LoginInfo.inTime.ToString("yyyy-MM-dd HH:mm:ss") + "', N'" + LoginInfo.computer_name + "', '" + LoginInfo.ipAddress + "', "+int.Parse(LoginInfo.UserID)+")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM login_session ORDER BY idlogin_session DESC LIMIT 1";
cmd.Connection = dbcon.connection;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
LoginInfo.sessionID = int.Parse(reader.GetString(0));
//Console.Write(LoginInfo.sessionID);
}
dbcon.closeConnection();
}
/// <summary>
/// Insert data to database
/// </summary>
/// <param name="sqlString">Query striing</param>
/// <returns></returns>
public bool InsertData(string sqlString)
{
dbConnectorObject = new DBConnector();
sqlConnectionObject = new SqlConnection();
sqlConnectionObject = dbConnectorObject.GetConnection;
try
{
sqlConnectionObject.Open();
SqlCommand sqlCommandObject = new SqlCommand(sqlString, sqlConnectionObject);
int i = sqlCommandObject.ExecuteNonQuery();
if (i > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw new Exception(" Database problem.\n" + ex.Message);
}
finally
{
if (sqlConnectionObject.State == ConnectionState.Open)
{
sqlConnectionObject.Close();
}
}
}
public static bool addDisciplinaryDetails(DisciplinaryDetails dd)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO desciplinary_details (rank, post, breaking, request_main_admin, breaking_date, given_sentence, given_sentence_date, verdict_of_director_general, verdict_of_director_general_date, inquiry_officer, suspended_date, rejoined_date, suspended_time, employee_idemployee) VALUES (N'" + dd.rank + "', N'" + dd.post + "', N'" + dd.breaking + "', N'" + dd.request_main_admin + "', '" + dd.breaking_date.ToString("yyyy-MM-dd") + "', N'" + dd.given_sentence + "', '" + dd.given_sentence_date.ToString("yyyy-MM-dd") + "', N'" + dd.verdict_of_director_general + "', '" + dd.verdict_of_director_general_date.ToString("yyyy-MM-dd") + "', N'" + dd.inquiry_officer + "', '" + dd.suspended_date.ToString("yyyy-MM-dd") + "', '" + dd.rejoined_date.ToString("yyyy-MM-dd") + "', N'" + dd.suspended_time + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public double GetTotalPaidNonOrdersBetweenDates(DateTime startDate, DateTime endDate, Boolean taxable)
{
DBConnector connector = new DBConnector();
MySqlCommand getPaidCommand = new MySqlCommand();
getPaidCommand.Connection = connector.connection;
getPaidCommand.CommandText = @"select price, taxable
from Tickets tix
join Ticket_Alterations ta
on tix.ticket_id = ta.ticket_id
AND completed_date >= @start_date
AND completed_date <= @end_date
AND taxable = @taxable
AND status = 'd'
AND (order_id = ''
or order_id IS NULL)";
getPaidCommand.Parameters.AddWithValue("@start_date", ConvertDateTimeToUTCString(startDate));
getPaidCommand.Parameters.AddWithValue("@end_date", ConvertDateTimeToUTCString(endDate));
int taxableInt = taxable == true ? 1 : 0;
getPaidCommand.Parameters.AddWithValue("@taxable", taxableInt);
try
{
MySqlDataReader reader = getPaidCommand.ExecuteReader();
double total = getPriceTotalFromReader(reader);
reader.Close();
connector.CloseConnection();
return total;
}
catch (MySqlException ex)
{
MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
}
return 0;
}
public static bool addExtracurricularActivity(ExtracurricularActivity eca)
{
DBConnector dbcon = new DBConnector();
try
{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO extracurricular_activity (type, method, award, employee_idemployee) VALUES (N'" + eca.type + "', N'" + eca.method + "', N'" + eca.award + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}
}
public static bool addDisciplinaryDetailsRecord()
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO desciplinary_details (breaking_date, given_sentence_date, verdict_of_director_general_date, suspended_date, rejoined_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public static bool addWorkstationDetails(WorkstationDetails wd)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO workstation_details (rank, division, post, date_of_post, salary_station, responsibility, power, employee_idemployee) VALUES (N'" + wd.rank + "', N'" + wd.division + "', N'" + wd.post + "', '" + wd.getDate_of_post().ToString("yyyy-MM-dd") + "', N'" + wd.salary_station + "', N'" + wd.responsibility + "', N'" + wd.power + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public Task<IEnumerable<PaymentInfo>> GetPaymentsAsync(DateTime start, CancellationToken cancellationToken)
{
DB.RegisterFactory("MySql", MySql.Data.MySqlClient.MySqlClientFactory.Instance);
DB.RegisterFactory("MySql.Data.MySqlClient", MySql.Data.MySqlClient.MySqlClientFactory.Instance);
var db = new DBConnector(Config.GetElement("Databases", "WordPress"));
using (var reader = db.ExecuteReader(@"
SELECT p.id, s.name,
us.billingFirstName, us.billingLastName, us.billingCompany,
us.billingAddress, us.billingCity, us.billingState, us.billingZip, us.billingCountry, us.billingPhone,
us.emailAddress,
us.lastFourDigitsOfCreditCard, p.xAmount, p.paymentDate,
us.subscriptionNotes, us.userIP,
CONVERT(p.fullAuthorizeNetResponse USING utf8) AS response
FROM wp_authnet_user_subscription us
JOIN wp_authnet_payment p ON us.ID = p.user_subscription_id
LEFT JOIN wp_authnet_subscription s ON s.ID = p.xSubscriptionId
WHERE p.paymentDate > ?start", new { start })) {
return Task.FromResult<IEnumerable<PaymentInfo>>(reader.Cast<IDataRecord>()
.Select(dr => new PaymentInfo {
Id = dr.GetString(dr.GetOrdinal("id")),
Email = dr.GetString(dr.GetOrdinal("emailAddress")),
FinalFour = dr.GetString(dr.GetOrdinal("lastFourDigitsOfCreditCard")),
CardIssuer = dr.GetString(dr.GetOrdinal("response")).Split('|')[51],
Date = dr.GetDateTime(dr.GetOrdinal("paymentDate")),
Amount = dr.GetDecimal(dr.GetOrdinal("xAmount")),
Comments = dr.GetString(dr.GetOrdinal("subscriptionNotes"))
+ "\nIP Address: " + dr.GetString(dr.GetOrdinal("userIP"))
})
.ToList());
}
}
public static bool addFinanceBank(FinanceBank fb)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO finance_bank (bank_name, branch_name, account_number, account_type, begin_date, end_date, qualification, qual_year, employee_idemployee) VALUES (N'" + fb.bank_name + "', N'" + fb.branch_name + "', N'" + fb.account_number + "', N'" + fb.account_type + "', '" + fb.getBegin_date().ToString("yyyy-MM-dd") + "', '" + fb.getEnd_date().ToString("yyyy-MM-dd") + "', N'" + fb.qualification + "', N'" + fb.Qual_year + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
//}
//catch (MySqlException e)
//{
//int errorcode = e.Number;
//dbcon.closeConnection();
//return false;
//}
}
public static bool addReAppointment(ReAppointment ra)
{
DBConnector dbcon = new DBConnector();
//try
//{
if (dbcon.openConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO reappointment (rank, post, suspension__system, suspension_reason, suspension_approved_note, rejoining_date, notes, employee_idemployee) VALUES (N'" + ra.rank + "', N'" + ra.post + "', N'" + ra.suspension__system + "', N'" + ra.suspension_reason + "', N'" + ra.suspension_approved_note + "', '" + ra.rejoining_date.ToString("yyyy-MM-dd") + "', N'" + ra.notes + "', " + Employee.employee_id + ")";
cmd.Connection = dbcon.connection;
cmd.Prepare();
cmd.ExecuteNonQuery();
dbcon.closeConnection();
return true;
}
else
{
dbcon.closeConnection();
return false;
}
/*}
catch (MySqlException e)
{
int errorcode = e.Number;
dbcon.closeConnection();
return false;
}*/
}
public HarvestHistoryVM()
{
LoadHarvests();
DBConnector.GetInstance().HarvestChanged += LoadHarvests;
}
private void button_Aceptar_Click(object sender, EventArgs e)
{
SqlCommand modificarAfi = new SqlCommand("ELIMINAR_CAR.modificarAfiliado", DBConnector.ObtenerConexion());
modificarAfi.CommandType = CommandType.StoredProcedure;
modificarAfi.Parameters.Add(new SqlParameter("@id_afiliado", afiliadoAMod.idAfiliado));
Boolean huboCambios = false;
//Telefono
if (seCambioTelefono)
{
huboCambios = true;
modificarAfi.Parameters.AddWithValue("@telefono", Int64.Parse(tel.Text));
}
else
{
modificarAfi.Parameters.AddWithValue("@telefono", DBNull.Value);
}
//Direccion
if (!afiliadoAMod.direccion.Equals(direc.Text))
{
huboCambios = true;
modificarAfi.Parameters.Add(new SqlParameter("@direccion", direc.Text));
}
else
{
modificarAfi.Parameters.Add(new SqlParameter("@direccion", DBNull.Value));
}
//Estado civil
if (afiliadoAMod.estadoCivil != (estado_civil)estadoCiv.SelectedItem)
{
huboCambios = true;
modificarAfi.Parameters.AddWithValue("@estado_civil", (Int32)estadoCiv.SelectedItem);
}
else
{
modificarAfi.Parameters.AddWithValue("@estado_civil", DBNull.Value);
}
//MAIL
if (!afiliadoAMod.mail.Equals(mail.Text))
{
huboCambios = true;
modificarAfi.Parameters.AddWithValue("@mail", mail.Text);
}
else
{
modificarAfi.Parameters.AddWithValue("@mail", DBNull.Value);
}
//Sexo
if (afiliadoAMod.sexo != (sexo)Sexo.SelectedItem)
{
huboCambios = true;
modificarAfi.Parameters.AddWithValue("@sexo", (int)Sexo.SelectedItem);
}
else
{
modificarAfi.Parameters.AddWithValue("@sexo", DBNull.Value);
}
//Plan
if (afiliadoAMod.idPlan != ((Plan)planMed.SelectedItem).id_plan)
{
huboCambios = true;
modificarAfi.Parameters.AddWithValue("@id_plan", ((Plan)planMed.SelectedItem).id_plan);
}
else
{
modificarAfi.Parameters.AddWithValue("@id_plan", DBNull.Value);
}
if (huboCambios)
{
DialogResult resultado = MessageBox.Show("¿Desea realizar estos cambios?", "Clinica-FRBA", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (resultado == DialogResult.OK)
{
if (afiliadoAMod.idPlan != ((Plan)planMed.SelectedItem).id_plan)
{
MotivoCambioPlan motivo = new MotivoCambioPlan();
this.Visible = false;
motivo.ShowDialog();
this.Visible = true;
modificarAfi.Parameters.AddWithValue("@fecha_cambio", ClinicaFrba.Utils.Fechas.getCurrentDateTime().Date);
if (motivo.fueCerradoPorusuario)
{
modificarAfi.Parameters.AddWithValue("@motivo_cambio_plan", "No especificado");
}
else
{
modificarAfi.Parameters.AddWithValue("@motivo_cambio_plan", ((RichTextBox)motivo.Controls["tb_motivo"]).Text);
}
}
else
{
modificarAfi.Parameters.AddWithValue("@fecha_cambio", DBNull.Value);
modificarAfi.Parameters.AddWithValue("@motivo_cambio_plan", DBNull.Value);
}
modificarAfi.ExecuteNonQuery();
MessageBox.Show("El afiliado fue modificado correctamente", "Clinica-FRBA", MessageBoxButtons.OK, MessageBoxIcon.Information);
huboCambios = false;
seCambioTelefono = false;
if ((tipo == 0) && ((estado_civil)estadoCiv.SelectedItem == estado_civil.Casado) && afiliadoAMod.estadoCivil != estado_civil.Casado)
{
DialogResult res = MessageBox.Show("¿Desea agregar a su conyuge?", "Clinica-FRBA", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
NuevoAfiliado conyuge = new NuevoAfiliado(1, afiliadoAMod.idFamilia);
this.Visible = false;
conyuge.ShowDialog();
this.Visible = true;
}
}
if ((tipo == 0) && ((estado_civil)estadoCiv.SelectedItem == estado_civil.Concubinato) && afiliadoAMod.estadoCivil != estado_civil.Concubinato)
{
DialogResult res = MessageBox.Show("¿Desea agregar a su conyuge?", "Clinica-FRBA", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
NuevoAfiliado conyuge = new NuevoAfiliado(2, afiliadoAMod.idFamilia);
this.Visible = false;
conyuge.ShowDialog();
this.Visible = true;
}
}
afiliadoAMod = Afiliado.getAfiliadoPorID(afiliadoAMod.idAfiliado); // Lo recargamos
}
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//მასწავლებელი - საგანი
if (menustrip == "მასწავლებელი - საგანი")
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
item.Cells[0].Value = false;
}
if (comboBox1.SelectedValue != null)
{
int teacher_id = int.Parse(comboBox1.SelectedValue.ToString());
if (teacher_id != 0)
{
List <int> subjectsList = DBConnector.GetSubjectByTeacherId(teacher_id);
foreach (DataGridViewRow item in dataGridView1.Rows)
{
try
{
int subject_id = int.Parse(item.Cells["subject_id"].Value.ToString());
if (subjectsList.Contains(subject_id))
{
item.Cells[0].Value = true;
}
}
catch (Exception ex)
{
continue;
}
}
}
}
}
//მასწავლებელი - კლასი
if (menustrip == "მასწავლებელი - კლასი")
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
item.Cells[0].Value = false;
}
if (comboBox1.SelectedValue != null)
{
int teacher_id = int.Parse(comboBox1.SelectedValue.ToString());
if (teacher_id != 0)
{
List <int> classList = DBConnector.GetClassByTeacherId(teacher_id);
foreach (DataGridViewRow item in dataGridView1.Rows)
{
try
{
int class_code = int.Parse(item.Cells["class_code"].Value.ToString());
if (classList.Contains(class_code))
{
item.Cells[0].Value = true;
}
}
catch (Exception ex)
{
continue;
}
}
}
}
}
//მოსწავლე - საგანი
if (menustrip == "მოსწავლე - საგანი")
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
item.Cells[0].Value = false;
}
if (comboBox1.SelectedValue != null)
{
int pupil_id = int.Parse(comboBox1.SelectedValue.ToString());
if (pupil_id != 0)
{
List <int> subjectList = DBConnector.GetSubjectByPupilId(pupil_id);
foreach (DataGridViewRow item in dataGridView1.Rows)
{
try
{
int subject_id = int.Parse(item.Cells["subject_id"].Value.ToString());
if (subjectList.Contains(subject_id))
{
item.Cells[0].Value = true;
}
}
catch (Exception ex)
{
continue;
}
}
}
}
}
}
private void button_add_Click(object sender, EventArgs e)
{
if (menustrip == "მასწავლებელი - საგანი")
{
if (comboBox1.SelectedItem != null)
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
try
{
if (item.Cells[0].Value is true)
{
DBConnector.AddTeacherSubjectRelation(int.Parse(item.Cells[1].Value.ToString()), int.Parse(comboBox1.SelectedValue.ToString()));
}
else if (item.Cells[0].Value is false)
{
DBConnector.RemoveTeacherSubjectRelation(int.Parse(item.Cells[1].Value.ToString()), int.Parse(comboBox1.SelectedValue.ToString()));
}
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE"))
{
continue;
}
//MessageBox.Show(ex.Message);
}
}
MessageBox.Show("დამახსოვრებულია!");
}
else
{
MessageBox.Show("აირჩიეთ მასწავლებელი");
}
}
if (menustrip == "მასწავლებელი - კლასი")
{
if (comboBox1.SelectedItem != null)
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
try
{
if (item.Cells[0].Value is true)
{
DBConnector.AddTeacherClassRelation(int.Parse(item.Cells[1].Value.ToString()), int.Parse(comboBox1.SelectedValue.ToString()));
}
else if (item.Cells[0].Value is false)
{
DBConnector.RemoveTeacherClassRelation(int.Parse(item.Cells[1].Value.ToString()), int.Parse(comboBox1.SelectedValue.ToString()));
}
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE"))
{
continue;
}
//MessageBox.Show(ex.Message);
}
}
MessageBox.Show("დამახსოვრებულია!");
}
else
{
MessageBox.Show("აირჩიეთ მასწავლებელი");
}
}
}
/*
* Vypis hracu ve hre a jejich pinu
*/
void Hraci()
{
mujprofil.Children.Clear();
MySqlCommand prikaz = new MySqlCommand("Select bakalarka.uzivatel.iduzivatel as id, bakalarka.uzivatel.heslo as pin, bakalarka.uzivatel.tym as tym, bakalarka.uzivatel.role as role from bakalarka.uzivatel inner join bakalarka.tym on bakalarka.uzivatel.tym=bakalarka.tym.idtym where bakalarka.tym.hra=@idhra;");
prikaz.Parameters.AddWithValue("@idhra", Hra.idhry);
MySqlDataReader data = DBConnector.ProvedeniPrikazuSelect(prikaz);
int radek = 1;
var id = new Label()
{
Text = "ID uživatele"
};
mujprofil.Children.Add(id, 0, 0);
var pin = new Label()
{
Text = "PIN"
};
mujprofil.Children.Add(pin, 1, 0);
var role = new Label()
{
Text = "Role"
};
mujprofil.Children.Add(role, 2, 0);
var tym = new Label()
{
Text = "Tým"
};
mujprofil.Children.Add(tym, 3, 0);
while (data.Read())
{
var idh = new Label()
{
Text = Convert.ToString(data["id"])
};
mujprofil.Children.Add(idh, 0, radek);
var pinh = new Label()
{
Text = Convert.ToString(data["pin"])
};
mujprofil.Children.Add(pinh, 1, radek);
var roleh = new Label();
if ((int)data["role"] == 1)
{
roleh.Text = "Těžař";
}
if ((int)data["role"] == 2)
{
roleh.Text = "Lovec";
}
if ((int)data["role"] == 3)
{
roleh.Text = "Domeček";
}
mujprofil.Children.Add(roleh, 2, radek);
var tymh = new Label()
{
Text = Convert.ToString(data["tym"])
};
mujprofil.Children.Add(tymh, 3, radek);
radek++;
}
var zpet = new Button()
{
Text = "Zpět", BackgroundColor = Color.DarkGray, TextColor = Color.RoyalBlue, FontSize = 15, CornerRadius = 4, BorderColor = Color.RoyalBlue, BorderWidth = 2
};
Grid.SetColumnSpan(zpet, 4);
Grid.SetRow(zpet, radek);
Grid.SetColumn(zpet, 0);
mujprofil.Children.Add(zpet);
zpet.Clicked += async(sender, args) =>
{
MujProfilVedouci();
};
ScrollView scroll = new ScrollView();
scroll.Content = mujprofil;
Content = scroll;
}
protected void btnUpdate_OnClick(object sender, EventArgs e)
{
pnlError.Visible = false;
var errors = ValidateInput();
if (errors.Count == 0)
{
var checkedButton = pnlHolders.Controls.OfType <RadioButton>().FirstOrDefault(r => r.Checked);
int holderCompany = (int)Enum.Parse(typeof(CompanyHolders), checkedButton.ToolTip);
checkedButton = pnlAssign.Controls.OfType <RadioButton>().FirstOrDefault(btn => btn.Checked);
int assignmentStatus = (int)Enum.Parse(typeof(Assigned), checkedButton.ToolTip);
DBConnector conn = new DBConnector();
int provID = conn.GetProviderIdByName(txtProvider.Text);
bool passed = false;
if (provID < 0)
{
var result = conn.insertProvider(txtProvider.Text, ref passed);
if (passed)
{
provID = Convert.ToInt32(result);
}
}
passed = false;
int softID = conn.GetSoftwareIdByName(txtSoftName.Text, provID.ToString());
if (softID < 0)
{
var result = conn.insertSoftware(txtSoftName.Text, provID, ref passed);
if (passed)
{
softID = Convert.ToInt32(result);
}
}
conn.updateSoftwareProvider(softID, provID);
Guid g = Guid.Empty;
if (!String.IsNullOrEmpty(hdnGuid.Value))
{
g = new Guid(hdnGuid.Value);
}
if (fileUpload.HasFile)
{
try
{
if (g == Guid.Empty)
{
g = new Guid();
Directory.CreateDirectory(directoryPath + g);
string filename = Path.GetFileName(fileUpload.FileName);
fileUpload.SaveAs(directoryPath + g + @"\" + filename);
}
else
{
string path = Directory.GetFiles(directoryPath + hdnGuid.Value).First();
File.Delete(path);
string filename = Path.GetFileName(fileUpload.FileName);
fileUpload.SaveAs(directoryPath + g + @"\" + filename);
}
}
catch
{
}
}
//String.Format("{0:0.00}", a); - reference
LicenseKey LK = new LicenseKey()
{
SoftwareId = softID,
Description = txtSoftDescription.Text,
Key = txtLiKey.Text,
Holder = ddlLicHolder.SelectedItem.Text,
HolderID = ddlLicHolder.SelectedItem.Value,
Manager = ddlHolderManager.SelectedItem.Text,
LicenseCost = Convert.ToDecimal(txtLiCost.Text),
RequisitionNumber = txtReqNum.Text,
ChargebackComplete = ddlChargeback.SelectedIndex == 0,
Provider = provID,
Assignment = assignmentStatus,
Speedchart = txtSpeedchart.Text,
DateUpdated = DateTime.Now.Date, //Validate empty
DateAssigned =
txtDateAssigned.Text.Length > 0
? Convert.ToDateTime(txtDateUpdated.Text)
: DateTime.MinValue,
DateRemoved =
txtDateRemoved.Text.Length > 0 ? Convert.ToDateTime(txtDateRemoved.Text) : DateTime.MinValue,
DateExpiring = Convert.ToDateTime(txtDateExpiring.Text), //Validate empty
LicenseHolderCompany = holderCompany,
Comments = Server.HtmlEncode(editor.InnerText),
fileSubpath = g,
LastModifiedBy = _user.FirstName + " " + _user.LastName
};
if (new DBConnector().UpdateLicenseKey(LK))
{
pnlMain.Visible = false;
pnlSelection.Visible = true;
pnlSuccess.Visible = true;
lblSuccess.Text = "Update Successful";
pnlWarningAttach.Visible = false;
}
else
{
lblError.Visible = true;
lblError.ForeColor = Color.Red;
lblError.Text = "An unknown error occurred, update failed";
pnlError.Visible = true;
}
//string response = new DBConnector().InsertSoftware(software);
//clearForm();
}
else
{
lblError.Visible = true;
lblError.ForeColor = Color.Red;
var E = new StringBuilder();
foreach (var error in errors)
{
E.Append(error);
}
lblError.Text = E.ToString();
pnlError.Visible = true;
}
}
public static int GetFileSize(IHttpClientFactory httpClientFactory, LoaderProperties loaderProperties, DBConnector cnn, int fieldId, string shortFieldUrl, string longFieldUrl)
{
if (loaderProperties.UseFileSizeService)
{
var url = GetFileStorageUrl(cnn, fieldId, longFieldUrl);
var client = httpClientFactory.CreateClient();
try
{
var response = client.GetAsync(url).Result.Content.ReadAsStringAsync().Result;
return(int.Parse(response));
}
catch (Exception ex)
{
Logger.Warn()
.Exception(ex)
.Message("Cannot receive file size with url: {url}", url)
.Write();
return(0);
}
}
var path = GetFileFromQpFieldPath(cnn, fieldId, shortFieldUrl);
if (File.Exists(path))
{
return((int)new FileInfo(path).Length);
}
Logger.Warn()
.Message("Cannot find file with path: {path}", path)
.Write();
return(0);
}
private void btn_cancelar_Click(object sender, EventArgs e)
{
Errores errores = new Errores();
if ((DateTime)cb_dia_desde.SelectedItem > (DateTime)cb_dia_hasta.SelectedItem)
{
errores.agregarError("La fecha final de la franja debe ser anterior a la inicial.");
}
if (tb_motivo.TextLength == 0)
{
errores.agregarError("Debe escribir un motivo de cancelacion");
}
if (errores.huboError())
{
MessageBox.Show(errores.stringErrores(), "Clinica-FRBA ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
SqlCommand cancelar = new SqlCommand("ELIMINAR_CAR.cancelarTurnoProfesional", DBConnector.ObtenerConexion());
cancelar.CommandType = CommandType.StoredProcedure;
cancelar.Parameters.Add("@matricula", SqlDbType.BigInt).Value = matricula;
cancelar.Parameters.Add("@fecha_desde", SqlDbType.Date).Value = (DateTime)cb_dia_desde.SelectedItem;
cancelar.Parameters.Add("@fecha_hasta", SqlDbType.Date).Value = (DateTime)cb_dia_hasta.SelectedItem;
cancelar.Parameters.Add("@motivo", SqlDbType.VarChar).Value = tb_motivo.Text;
cancelar.ExecuteNonQuery();
MessageBox.Show("Franja Cancelada Correctamente", "Clinica-FRBA", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
private void botonAceptar_Click(object sender, EventArgs e)
{
Errores errores = new Errores();
if (textBox_NombAfi.TextLength == 0)
{
errores.agregarError("El nombre del afiliado no puede ser nulo");
}
if (textBox_ApAfi.TextLength == 0)
{
errores.agregarError("El apellido del afiliado no puede ser nulo");
}
if (textBox_NumDoc.TextLength == 0)
{
errores.agregarError("El número de documento del afiliado no puede ser nulo");
}
if (PlanMedAfi.SelectedItem == null)
{
errores.agregarError("El plan médico del afiliado no puede ser nulo");
}
SqlCommand cuantosHayConDNI = new SqlCommand("ELIMINAR_CAR.verificar_doc_afiliado", DBConnector.ObtenerConexion());
int cantDNI = 0;
cuantosHayConDNI.CommandType = CommandType.StoredProcedure;
cuantosHayConDNI.Parameters.AddWithValue("@tipo", (int)comboBox_TipoDoc.SelectedItem);
cuantosHayConDNI.Parameters.AddWithValue("@dni", Int64.Parse(textBox_NumDoc.Text));
cuantosHayConDNI.Parameters.AddWithValue("@resultado", cantDNI);
cuantosHayConDNI.Parameters["@resultado"].Direction = ParameterDirection.Output;
cuantosHayConDNI.ExecuteNonQuery();
cantDNI = (int)cuantosHayConDNI.Parameters["@resultado"].Value;
if (cantDNI > 0)
{
errores.agregarError("El tipo y numero de documento ingresado ya existe.");
}
if (errores.huboError())
{
MessageBox.Show(errores.stringErrores(), "Clinica-FRBA: ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else //Coportamiento si esta todo ok
{
//Insertar Chabon
if (tipo == 0)
{
SqlCommand insertar = new SqlCommand("ELIMINAR_CAR.insertarAfiliadoRaiz", DBConnector.ObtenerConexion());
insertar.CommandType = CommandType.StoredProcedure;
insertar.Parameters.Add(new SqlParameter("@tipo_doc", (Int32)comboBox_TipoDoc.SelectedValue));
insertar.Parameters.Add(new SqlParameter("@n_doc", Decimal.Parse(textBox_NumDoc.Text)));
insertar.Parameters.Add(new SqlParameter("@nombre", textBox_NombAfi.Text));
insertar.Parameters.Add(new SqlParameter("@apellido", textBox_ApAfi.Text));
insertar.Parameters.Add(new SqlParameter("@direccion", textBox_DirecAfi.Text));
int telefono;
if (Int32.TryParse(textBox_TelAfi.Text, out telefono))
{
insertar.Parameters.Add(new SqlParameter("@telefono", Int64.Parse(textBox_TelAfi.Text)));
}
else
{
insertar.Parameters.Add(new SqlParameter("@telefono", DBNull.Value));
}
insertar.Parameters.Add(new SqlParameter("@mail", textBox_MailAfi.Text));
insertar.Parameters.Add(new SqlParameter("@fecha_nac", dtp_fecha_nac.Value));
insertar.Parameters.Add(new SqlParameter("@estado_civil", (Int32)comboBox_EstadoCivilAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@sexo", (Int32)comboBox_SexoAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@id_plan", ((Plan)PlanMedAfi.SelectedItem).id_plan));
int familiares = 0;
//Le ponemos 0 a cargo porque no cuenta el conyuge y al agregar los demas se suman
// if (Int32.TryParse(textBox_CantFami.Text, out familiares)) ;
//else familiares = 0;
insertar.Parameters.Add(new SqlParameter("@familiares_a_cargo", familiares));
insertar.Parameters.Add(new SqlParameter("@id_familia_out", numFam));
Int64 id_afiliado = 0;
insertar.Parameters.Add(new SqlParameter("@id_afiliado_out", id_afiliado));
insertar.Parameters["@id_familia_out"].Direction = ParameterDirection.Output;
insertar.Parameters["@id_afiliado_out"].Direction = ParameterDirection.Output;
insertar.ExecuteNonQuery();
numFam = (Int64)insertar.Parameters["@id_familia_out"].Value;
id_afiliado = (Int64)insertar.Parameters["@id_afiliado_out"].Value;
}
else if (tipo == 1 || tipo == 2)
{
SqlCommand insertar = new SqlCommand("ELIMINAR_CAR.insertarConyuge", DBConnector.ObtenerConexion());
insertar.CommandType = CommandType.StoredProcedure;
insertar.Parameters.Add(new SqlParameter("@id_familia", numFam));
insertar.Parameters.Add(new SqlParameter("@tipo_doc", (Int32)comboBox_TipoDoc.SelectedValue));
insertar.Parameters.Add(new SqlParameter("@n_doc", Decimal.Parse(textBox_NumDoc.Text)));
insertar.Parameters.Add(new SqlParameter("@nombre", textBox_NombAfi.Text));
insertar.Parameters.Add(new SqlParameter("@apellido", textBox_ApAfi.Text));
insertar.Parameters.Add(new SqlParameter("@direccion", textBox_DirecAfi.Text));
int telefono;
if (Int32.TryParse(textBox_TelAfi.Text, out telefono))
{
insertar.Parameters.Add(new SqlParameter("@telefono", Int64.Parse(textBox_TelAfi.Text)));
}
else
{
insertar.Parameters.Add(new SqlParameter("@telefono", DBNull.Value));
}
insertar.Parameters.Add(new SqlParameter("@mail", textBox_MailAfi.Text));
insertar.Parameters.Add(new SqlParameter("@fecha_nac", dtp_fecha_nac.Value));
insertar.Parameters.Add(new SqlParameter("@estado_civil", (Int32)comboBox_EstadoCivilAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@sexo", (Int32)comboBox_SexoAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@id_plan", ((Plan)PlanMedAfi.SelectedItem).id_plan));
Int64 id_afiliado = 0;
insertar.Parameters.Add(new SqlParameter("@id_afiliado_out", id_afiliado));
insertar.Parameters["@id_afiliado_out"].Direction = ParameterDirection.Output;
insertar.ExecuteNonQuery();
id_afiliado = (Int64)insertar.Parameters["@id_afiliado_out"].Value;
}
else
{
SqlCommand insertar = new SqlCommand("ELIMINAR_CAR.insertarDependiente", DBConnector.ObtenerConexion());
insertar.CommandType = CommandType.StoredProcedure;
insertar.Parameters.Add(new SqlParameter("@id_familia", numFam));
insertar.Parameters.Add(new SqlParameter("@tipo_doc", (Int32)comboBox_TipoDoc.SelectedValue));
insertar.Parameters.Add(new SqlParameter("@n_doc", Decimal.Parse(textBox_NumDoc.Text)));
insertar.Parameters.Add(new SqlParameter("@nombre", textBox_NombAfi.Text));
insertar.Parameters.Add(new SqlParameter("@apellido", textBox_ApAfi.Text));
insertar.Parameters.Add(new SqlParameter("@direccion", textBox_DirecAfi.Text));
int telefono;
if (Int32.TryParse(textBox_TelAfi.Text, out telefono))
{
insertar.Parameters.Add(new SqlParameter("@telefono", Int64.Parse(textBox_TelAfi.Text)));
}
else
{
insertar.Parameters.Add(new SqlParameter("@telefono", DBNull.Value));
}
insertar.Parameters.Add(new SqlParameter("@mail", textBox_MailAfi.Text));
insertar.Parameters.Add(new SqlParameter("@fecha_nac", dtp_fecha_nac.Value));
insertar.Parameters.Add(new SqlParameter("@estado_civil", (Int32)comboBox_EstadoCivilAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@sexo", (Int32)comboBox_SexoAfi.SelectedItem));
insertar.Parameters.Add(new SqlParameter("@id_plan", ((Plan)PlanMedAfi.SelectedItem).id_plan));
Int64 id_afiliado = 0;
insertar.Parameters.Add(new SqlParameter("@id_afiliado_out", id_afiliado));
insertar.Parameters["@id_afiliado_out"].Direction = ParameterDirection.Output;
insertar.ExecuteNonQuery();
id_afiliado = (Int64)insertar.Parameters["@id_afiliado_out"].Value;
}
MessageBox.Show("El afiliado fue agregado corrrectamente\n", "Clinica-FRBA", MessageBoxButtons.OK);
//Casteo a tipo de dato, porque devuelve object
if ((tipo == 0) && ((estado_civil)comboBox_EstadoCivilAfi.SelectedItem == estado_civil.Casado))
{
DialogResult res = MessageBox.Show("¿Desea agregar a su conyuge?", "Clinica-FRBA", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
NuevoAfiliado conyuge = new NuevoAfiliado(1, numFam);
this.Visible = false;
conyuge.ShowDialog();
this.Visible = true;
}
}
if ((tipo == 0) && ((estado_civil)comboBox_EstadoCivilAfi.SelectedItem == estado_civil.Concubinato))
{
DialogResult res = MessageBox.Show("¿Desea agregar a su conyuge?", "Clinica-FRBA", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (res == DialogResult.Yes)
{
NuevoAfiliado conyuge = new NuevoAfiliado(2, numFam);
this.Visible = false;
conyuge.ShowDialog();
this.Visible = true;
}
}
if ((tipo == 0) && textBox_CantFami.TextLength > 0 && (int.Parse(textBox_CantFami.Text) > 0))
{
int cantFamiliares = int.Parse(textBox_CantFami.Text);
for (int i = 0; i < cantFamiliares; i++)
{
DialogResult resultado = MessageBox.Show("¿Desea agregar un nuevo familiar a cargo?", "Clinica-FRBA", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (resultado == DialogResult.Yes)
{
NuevoAfiliado familiar = new NuevoAfiliado(3, numFam);
this.Visible = false;
familiar.ShowDialog();
this.Visible = true;
}
else
{
break;
}
}
}
this.Close();
}
}
public void OnClosing(object sender, System.ComponentModel.CancelEventArgs e)
{
DBConnector.CloseConnection();
}
public Return_Assets()
{
dbConnector = DBConnector.getInstance();
InitializeComponent();
int act = 1;
DataGridViewComboBoxColumn location = new DataGridViewComboBoxColumn();
dataGridView1.Columns.Add(location);
location.DataSource = dbConnector.GetSiteList(act);
location.HeaderText = "Location";
location.Name = "Location";
location.Width = 100;
DataGridViewTextBoxColumn assetCode = new DataGridViewTextBoxColumn();
dataGridView1.Columns.Add(assetCode);
assetCode.HeaderText = "Asset Code";
assetCode.Name = "Asset Code";
assetCode.Width = 100;
DataGridViewTextBoxColumn quantity = new DataGridViewTextBoxColumn();
dataGridView1.Columns.Add(quantity);
quantity.HeaderText = "Quantity";
quantity.Name = "Quantity";
quantity.Width = 100;
DataGridViewTextBoxColumn rtnquantity = new DataGridViewTextBoxColumn();
dataGridView1.Columns.Add(rtnquantity);
rtnquantity.HeaderText = "Return Quantity";
rtnquantity.Name = "Return Quantity";
rtnquantity.Width = 100;
DataGridViewTextBoxColumn damage = new DataGridViewTextBoxColumn();
dataGridView1.Columns.Add(damage);
damage.HeaderText = "Remarks";
damage.Name = "Remarks";
damage.Width = 100;
DataGridViewTextBoxColumn warranty = new DataGridViewTextBoxColumn();
dataGridView1.Columns.Add(warranty);
warranty.HeaderText = "Warranty";
warranty.Name = "Warranty";
warranty.Width = 100;
DataGridViewComboBoxColumn action = new DataGridViewComboBoxColumn();
dataGridView1.Columns.Add(action);
action.HeaderText = "Action";
action.Name = "Action";
action.Items.Add("Warranty");
action.Items.Add("Auction");
action.Items.Add("Dispose");
action.Items.Add("Lost");
action.Items.Add("None");
action.Width = 100;
}
public ListadoConsultas(DateTime fechaP)
{
InitializeComponent();
this.conexion = DBConnector.ObtenerConexion();
this.fechaParametro = fechaP;
}
public Operator()
{
dbConnector = DBConnector.getInstance();
InitializeComponent();
}
public FormulariosController(DBConnector db, IHostingEnvironment env)
{
Database = db;
hostingEnv = env;
}
public DeleteRentalPage(DBConnector connector) : this()
{
Connector = connector;
}
public static string ExtractData(AccountDetails details, string CBAConstr, string ClirecConstr, string CBAType)
{
string uuid = Guid.NewGuid().ToString();
DataTable data = new DataTable();
try {
var db = new DBConnector(CBAConstr, ClirecConstr);
Utils.Log("Beginning Data Etraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")");
InterfaceDefinition def = db.getDefinition(details.DefinitionID);
string mappedAccts = ""; var map = new StringBuilder(); int ct = 1;
foreach (string cba in details.CBAccount)
{
map.Append("'").Append(cba).Append("'");
if (ct < details.CBAccount.Count)
{
map.Append(",");
}
ct++;
}
mappedAccts = map.ToString();
string sql = def.script.Replace("startdate", getDate(details.LastLedgerDate)).
Replace("enddate", details.Endate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log("Connecting to transaction Database");
Utils.Log(sql);
switch (CBAType.CleanUp())
{
case "mysql":
data = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
data = db.getSQLServerCBA(sql);
break;
default:
data = db.fetchCBA(sql);
break;
}
int nofT = data.Rows.Count;
bool isData = nofT > 0;
Utils.Log(nofT + " transaction(s) fetched");
int sn = 1;
data = Utils.AddExtractionColumns(data);
foreach (DataRow row in data.Rows)
{
row["PostDate"] = row[def.postDateCol].toDateTime();
row["Valdate"] = row[def.valDateCol].toDateTime();
bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D");
row["CrDr"] = (isDebit ? "1" : "2");
row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero);
row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal());
row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal();
getUDFs(row, def);
row["Details"] = getNaration(def, row);
row["SN"] = sn++;
row["Id"] = uuid;
row["username"] = "******";
}
var balance = decimal.Zero;
if (isData)
{
string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" };
data = new DataView(data).ToTable(false, selected);
Utils.Log("Attempting to Fetch Account Balance");
foreach (string cba in details.CBAccount)
{
sql = def.balScript.Replace("startdate", Utils.getFirstDayofMonth(details.CurrentDate))
.Replace("enddate", details.Endate).Replace("acctid", cba).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
var baldata = new DataTable();
switch (CBAType.CleanUp())
{
case "mysql":
baldata = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
data = db.getSQLServerCBA(sql);
break;
default:
baldata = db.fetchCBA(sql);
break;
}
if (baldata.Rows.Count > 0)
{
balance += baldata.Rows[0][def.balCol].toDecimal();
}
}
Utils.Log(sql);
Utils.Log("Balance downloaded successfully and = " + balance.ToString("0.00"));
db.CopyDataTableToDB(data, "ExtractionTemp");
}
var ID = uuid;
return((isData)?ID + "$" + balance.ToString("0.00"): "No Data Pulled!");
} catch (Exception e) {
throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message);
}
finally
{
data.Clear();
data.Dispose();
}
}
public static ExctractionDetails ExtractAjustedEntries(AccountDetails details, string CBAConstr, string ClirecConstr, string CBAType, string submitedDateCol)
{
string uuid = Guid.NewGuid().ToString();
DataTable data = new DataTable();
var Edetails = new ExctractionDetails();
Edetails.Data = new DataTable();
try
{
var db = new DBConnector(CBAConstr, ClirecConstr);
Utils.Log("Beginging Data Etraction for (" + details.AccountCode + ") using Interface definition ID (" + details.DefinitionID + ")");
InterfaceDefinition def = db.getDefinition(details.DefinitionID);
string mappedAccts = ""; var map = new StringBuilder(); int ct = 1;
foreach (string cba in details.CBAccount)
{
map.Append("'").Append(cba).Append("'");
if (ct < details.CBAccount.Count)
{
map.Append(",");
}
ct++;
}
mappedAccts = map.ToString();
Utils.Log("Connecting to transaction Database");
var sql = "";
sql = "select lastdate from AdjustmentPosition where AccountID='" + details.AccountCode + "'";
var dt = db.getDataSet(sql);
string Startdate, Enddate, StartSubmitedDate, EndSubmited;
Startdate = Utils.getFirstDayofMonth(details.CurrentDate);
Enddate = Utils.getLastDateofMonth(details.CurrentDate);
StartSubmitedDate = dt.Rows.Count > 0? dt.Rows[0]["lastdate"].ToString(): Enddate;
EndSubmited = Utils.getLastDateofMonth(details.CurrentDate.AddMonths(1));
switch (CBAType.CleanUp())
{
case "mysql":
sql = def.script.Replace("startdate", details.LastLedgerDate.ToString("yyyy-MM-dd")).
Replace("enddate", Convert.ToDateTime(details.Endate).ToString("yyyy-MM-dd")).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode);
Utils.Log(sql);
data = db.fetchMySQLCBA(sql);
break;
case "sqlserver":
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("StartSubDate", StartSubmitedDate).Replace("EndSubDate", EndSubmited);
Utils.Log(sql);
data = db.getSQLServerCBA(sql);
break;
default:
sql = def.script.Replace("startdate", Startdate).
Replace("enddate", Enddate).Replace("acctid", mappedAccts).
Replace("acctCcode", details.currency).Replace("acctBcode", details.CbaBranchCode).Replace("StartSubDate", StartSubmitedDate).Replace("EndSubDate", EndSubmited);
Utils.Log(sql);
data = db.fetchCBA(sql);
break;
}
int nofT = data.Rows.Count;
bool isData = nofT > 0;
Utils.Log(nofT + " transaction(s) fetched");
int sn = 1;
data = Utils.AddExtractionColumns(data);
foreach (DataRow row in data.Rows)
{
row["PostDate"] = row[def.postDateCol].toDateTime();
row["Valdate"] = row[def.valDateCol].toDateTime();
bool isDebit = row[def.directionCol].ToString().ToUpper().Equals("D");
row["CrDr"] = (isDebit ? "1" : "2");
row["Debits"] = (isDebit ? row[def.amountCol].toDecimal() : decimal.Zero);
row["Credits"] = (isDebit ? decimal.Zero : row[def.amountCol].toDecimal());
row["Amount"] = row["Credits"].toDecimal() + row["Debits"].toDecimal();
getUDFs(row, def);
row["Details"] = getNaration(def, row);
row["SN"] = sn++;
row["Id"] = uuid;
row["username"] = "******";
}
var balance = decimal.Zero;
if (isData)
{
Utils.Log("Computing Max Submited Date");
Edetails.LastSubmitedDate = data.Rows[0]["SubmittedOn"].ToString();
Utils.Log("Max Submitted Date =" + Edetails.LastSubmitedDate);
string[] selected = new[] { "SN", "PostDate", "Valdate", "Details", "Debits", "Credits", "Amount", "CrDr", "ud1", "ud2", "ud3", "ud4", "ud5", "Id", "username" };
data = new DataView(data).ToTable(false, selected);
balance = details.Balance + data.Compute("sum(Credits)", "").toDecimal() - data.Compute("sum(Debits)", "").toDecimal();
Edetails.TotalCredits = Convert.ToDecimal(data.Compute("Sum(Credits)", ""));
Edetails.TotalDebits = Convert.ToDecimal(data.Compute("Sum(Debits)", ""));
Edetails.Latest = Convert.ToDateTime(data.Compute("Max(PostDate)", ""));
Edetails.CreditCount = data.Compute("count(CrDr)", "CrDr ='2' ").toInt();
Edetails.DebitCount = data.Compute("count(CrDr)", "CrDr ='1' ").toInt();
Edetails.DataID = uuid;
Edetails.Balance = balance;
db.CopyDataTableToDB(data, "ExtractionTemp");
}
return(Edetails);
}
catch (Exception e)
{
throw new Exception("CBA Download failed for " + details.AccountName + "(" + details.AccountCode + ") because: " + e.Message);
}
finally
{
data.Clear();
data.Dispose();
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
string username = txtUserName.Text.Trim();
string newpassword = txtNewPassword.Text.Trim();
string oldpassword = txtOldPassword.Text.Trim();
string confirmpassword = txtConfirmPassword.Text.Trim();
try
{
if (username.Length == 0)
{
MessageBox.Show("Please enter user name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUserName.Focus();
return;
}
if (oldpassword.Length == 0)
{
MessageBox.Show("Please enter old password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtOldPassword.Focus();
return;
}
if (newpassword.Length == 0)
{
MessageBox.Show("Please enter new password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtNewPassword.Focus();
return;
}
if (confirmpassword.Length == 0)
{
MessageBox.Show("Please confirm new password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtConfirmPassword.Focus();
return;
}
if ((txtNewPassword.TextLength < 5))
{
MessageBox.Show("The New Password Should be of Atleast 5 Characters", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtNewPassword.Text = "";
txtConfirmPassword.Text = "";
txtNewPassword.Focus();
return;
}
else if ((txtNewPassword.Text != txtConfirmPassword.Text))
{
MessageBox.Show("Password do not match", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtNewPassword.Text = "";
txtOldPassword.Text = "";
txtConfirmPassword.Text = "";
txtOldPassword.Focus();
return;
}
else if ((txtOldPassword.Text == txtNewPassword.Text))
{
MessageBox.Show("Password is same..Re-enter new password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtNewPassword.Text = "";
txtConfirmPassword.Text = "";
txtNewPassword.Focus();
return;
}
if (DBConnector.getInstance().UpdateUserDetailsTable(username, newpassword, oldpassword))
{
MessageBox.Show("Password Subbmitted!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoginForm form = new LoginForm();
this.Hide();
form.Show();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static int GetFieldId(DBConnector dbConnector, string contentName, string fieldName) => dbConnector.FieldID(SiteId, contentName, fieldName);
public static Dictionary <string, int> GetIdsWithTitles(DBConnector dbConnector, int contentId) => dbConnector.GetRealData($"select content_item_id, Title from content_{contentId}_united")
.Select()
.Select(row => new { Id = Convert.ToInt32(row["content_item_id"]), Title = Convert.ToString(row["Title"]) })
.ToDictionary(row => row.Title, row => row.Id);
public static int GetContentId(DBConnector dbConnector, string contentName) => dbConnector.GetRealData($"select content_id from content where site_id = {SiteId} and content_name = '{contentName}'")
.Select()
.Select(row => Convert.ToInt32(row["content_id"]))
.SingleOrDefault();
/// <summary>
/// constructor -- connects to & opens the database
/// </summary>
private DBUtilities()
{
connector = new DBConnector();
connector.openDB();
conn = connector.getConn();
}//end constructor
public ApplicationUserStore(DBConnector context) : base(context.db)
{
DB = context;
}
private ArticleField DeserializeEntityField(EntityField fieldInDef, Quantumart.QP8.BLL.Field qpField, IProductDataSource productDataSource, DBConnector connector, Context context)
{
string fieldName = fieldInDef.FieldName ?? qpField.Name;
ArticleField articleField;
if (qpField.RelationType == RelationType.OneToMany)
{
articleField = new SingleArticleField
{
Item = DeserializeArticle(productDataSource.GetContainer(fieldName), fieldInDef.Content, connector, context),
Aggregated = qpField.Aggregated,
SubContentId = fieldInDef.Content.ContentId
};
}
else if (qpField.RelationType == RelationType.ManyToMany || qpField.RelationType == RelationType.ManyToOne)
{
var multiArticleField = new MultiArticleField {
SubContentId = fieldInDef.Content.ContentId
};
var containersCollection = productDataSource.GetContainersCollection(fieldName);
if (containersCollection != null)
{
foreach (Article article in containersCollection.Select(x => DeserializeArticle(x, fieldInDef.Content, connector, context)))
{
multiArticleField.Items.Add(article.Id, article);
}
}
articleField = multiArticleField;
}
else
{
throw new Exception(string.Format("Field definition id={0} has EntityField type but its RelationType is not valid", fieldInDef.FieldId));
}
return(articleField);
}
private Article DeserializeArticle(IProductDataSource productDataSource, Models.Configuration.Content definition, DBConnector connector, Context context)
{
if (productDataSource == null)
{
return(null);
}
int id = productDataSource.GetArticleId();
context.TakeIntoAccount(id);
var qpContent = _contentService.Read(definition.ContentId);
Article article = new Article
{
Id = id,
ContentName = qpContent.NetName,
Modified = productDataSource.GetModified(),
ContentId = definition.ContentId,
ContentDisplayName = qpContent.Name,
PublishingMode = definition.PublishingMode,
IsReadOnly = definition.IsReadOnly,
Visible = true
};
foreach (Field fieldInDef in definition.Fields.Where(x => !(x is BaseVirtualField) && !(x is Dictionaries)))
{
var field = DeserializeField(fieldInDef, _fieldService.Read(fieldInDef.FieldId), productDataSource, connector, context);
article.Fields[field.FieldName] = field;
}
if (definition.LoadAllPlainFields)
{
var qpFields = _fieldService.List(definition.ContentId);
foreach (var plainFieldFromQp in qpFields.Where(x => x.RelationType == RelationType.None && definition.Fields.All(y => y.FieldId != x.Id)))
{
article.Fields[plainFieldFromQp.Name] = DeserializeField(new PlainField {
FieldId = plainFieldFromQp.Id
}, plainFieldFromQp, productDataSource, connector, context);
}
}
return(article);
}
public static int[] GetIds(DBConnector dbConnector, int contentId) => dbConnector.GetRealData($"select content_item_id from content_{contentId}_united")
.Select()
.Select(row => Convert.ToInt32(row["content_item_id"]))
.OrderBy(row => row)
.ToArray();
public async Task <object> InsertWithSelect()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Admin_with_creator> result = new List <Admin_with_creator>();
string temp6tablename = "#temp6";
string createtabletemp6 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) ",
temp6tablename, User_list.FieldName.USER_ID);
string insertcmd = "";
string ts = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[93];
insertcmd += string.Format(
"IF NOT EXISTS(select * from {0} where {1} = '{2}' or {3} = '{2}') " +
"begin " +
"insert into {4} " +
"select * from (insert into {0} ({5}, {1}, {6}, {3}, {7},{16}) output inserted.{8} " +
"values ('{9}', '{2}', '{10}', '{2}', '{11}','{17}')) as outputinsert " +
"insert into {12} ({13},{14}) select {8},{15} from {4} " +
getselectcmd() + " " +
"end " +
"else " +
"RETURN ", User_list.FieldName.TABLE_NAME, User_list.FieldName.USERNAME, username,
User_list.FieldName.EMAIL, temp6tablename,
User_list.FieldName.USER_TYPE_ID, FieldName.PASSWORD, FieldName.TIMESTAMP,
User_list.FieldName.USER_ID,
/*****9****/ 7, password, ts,
/****12****/ FieldName.TABLE_NAME, FieldName.ADMIN_ID, FieldName.ADMIN_CREATOR_ID, admin_creator_id,
FieldName.T_NAME, t_name);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} END ", createtabletemp6,
insertcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(new Admin_with_creator
{
timestamp = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIMESTAMP].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
admin_creator_id = item.ItemArray[data.Columns[FieldName.ADMIN_CREATOR_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ADMIN_CREATOR_ID].Ordinal]) : 0,
creator_name = item.ItemArray[data.Columns["c_t_prename"].Ordinal].ToString() + item.ItemArray[data.Columns["c_t_name"].Ordinal].ToString(),
t_name = item.ItemArray[data.Columns[FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[FieldName.T_NAME].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[FieldName.EMAIL].Ordinal].ToString(),
username = item.ItemArray[data.Columns[FieldName.USERNAME].Ordinal].ToString(),
user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(),
admin_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ADMIN_ID].Ordinal])
});
}
data.Dispose();
}
else
{
res.Close();
return("อีเมล์ดังกล่าวมีอยู่แล้วในระบบ");
}
res.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
//Handle error from sql execution
if (ex.Number == 8152)
{
return("มีรายละเอียดของผู้ควบคุมระบบคนใหม่บางส่วนที่ต้องการเพิ่มมีขนาดที่ยาวเกินกำหนด");
}
else
{
return(ex.Message);
}
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
/*
* Nacist hru pro vedouciho
*/
void NacistHruVzhled()
{
mujprofil.Children.Clear();
MySqlCommand prikaz = new MySqlCommand("Select nazev from bakalarka.hra;");
prikaz.Parameters.AddWithValue("@id", Hrac.iduzivatel);
MySqlDataReader data = DBConnector.ProvedeniPrikazuSelect(prikaz);
Picker seznamBox = new Picker()
{
Title = "Název",
VerticalOptions = LayoutOptions.CenterAndExpand
};
while (data.Read())
{
seznamBox.Items.Add(data["nazev"].ToString());
}
Grid.SetColumnSpan(seznamBox, 2);
Grid.SetRow(seznamBox, 1);
Grid.SetColumn(seznamBox, 1);
mujprofil.Children.Add(seznamBox);
var nazevlbl = new Label {
Text = "název:"
};
mujprofil.Children.Add(nazevlbl, 0, 1);
var nacist = new Button()
{
Text = "Načíst hru", BackgroundColor = Color.RoyalBlue, TextColor = Color.DarkGray, FontSize = 15, CornerRadius = 4, BorderColor = Color.DarkGray, BorderWidth = 2
};
Grid.SetColumnSpan(nacist, 3);
Grid.SetRow(nacist, 2);
Grid.SetColumn(nacist, 0);
mujprofil.Children.Add(nacist);
nacist.Clicked += async(sender, args) => {
if (seznamBox.SelectedIndex == -1)
{
await DisplayAlert("Chyba", "Musí být vybrán název hry!", "Zavřít");
}
else
{
Hra.idHry(seznamBox.Items[seznamBox.SelectedIndex]);
Hra.nacteniHry(Hra.idhry);
Hra.AktualizacePolohy();
MujProfilVedouci();
}
};
var zpet = new Button()
{
Text = "Zpět", BackgroundColor = Color.DarkGray, TextColor = Color.RoyalBlue, FontSize = 15, CornerRadius = 4, BorderColor = Color.RoyalBlue, BorderWidth = 2
};
Grid.SetColumnSpan(zpet, 3);
Grid.SetRow(zpet, 3);
Grid.SetColumn(zpet, 0);
mujprofil.Children.Add(zpet);
zpet.Clicked += async(sender, args) =>
{
MujProfilVedouci();
};
}
public async Task <object> Insert(List <UsernamePassword> list, List <string> target_curri_id_list)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <string> result = new List <string>();
string temp5tablename = "#temp5";
string temp6tablename = "#temp6";
string temp7tablename = "#temp7";
string createtabletemp5 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] VARCHAR(60) NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} VARCHAR(60) COLLATE DATABASE_DEFAULT ",
temp5tablename, FieldName.EMAIL);
string createtabletemp6 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) ",
temp6tablename, User_list.FieldName.USER_ID);
string createtabletemp7 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {2} NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ",
temp7tablename, User_curriculum.FieldName.CURRI_ID, DBFieldDataType.CURRI_ID_TYPE);
string insertintotemp7 = string.Format("insert into {0} values ", temp7tablename);
int len = insertintotemp7.Length;
foreach (string curriitem in target_curri_id_list)
{
if (insertintotemp7.Length <= len)
{
insertintotemp7 += string.Format("('{0}')", curriitem);
}
else
{
insertintotemp7 += string.Format(",('{0}')", curriitem);
}
}
string insertintousercurri = "";
if (insertintotemp7.Length > len)
{
insertintousercurri = string.Format("insert into {0} " +
"select {1},{2} from {3},{4} ",
User_curriculum.FieldName.TABLE_NAME,
User_curriculum.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, temp6tablename, temp7tablename);
}
else
{
insertintotemp7 = "";
}
string insertcmd = "";
foreach (UsernamePassword item in list)
{
string ts = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[93];
insertcmd += string.Format(
"IF NOT EXISTS(select * from {0} where {1} = '{2}' or {3} = '{2}') " +
"begin " +
"insert into {4} " +
"select * from (insert into {0} ({5}, {1}, {6}, {3}, {7}, {15}) output inserted.{8} " +
"values ('{9}', '{2}', '{10}', '{2}', '{11}', '{2}')) as outputinsert " +
"insert into {12} ({13}) select {8} from {4} " +
insertintousercurri + " " +
"delete from {4} " +
"end " +
"else " +
"begin " +
"insert into {14} values ('{2}') " +
"end ", User_list.FieldName.TABLE_NAME, User_list.FieldName.USERNAME, item.username,
User_list.FieldName.EMAIL, temp6tablename,
User_list.FieldName.USER_TYPE_ID, FieldName.PASSWORD, FieldName.TIMESTAMP,
User_list.FieldName.USER_ID,
/*****9****/ 3, item.password, ts,
/****12****/ FieldName.TABLE_NAME, FieldName.USER_ID, temp5tablename,
User_list.FieldName.T_NAME
);
}
string selectcmd = string.Format("select {1} from {0} ", temp5tablename, FieldName.EMAIL);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END ", createtabletemp5, createtabletemp6, createtabletemp7,
insertintotemp7, insertcmd, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
result.Add(
item.ItemArray[data.Columns[FieldName.EMAIL].Ordinal].ToString()
);
}
data.Dispose();
}
else
{
//Reserved for return error string
}
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
if (result.Count != 0)
{
return(result);
}
else
{
return(null);
}
}
private ArticleField DeserializeBackwardField(BackwardRelationField fieldInDef, IProductDataSource productDataSource, DBConnector connector, Context context)
{
if (string.IsNullOrEmpty(fieldInDef.FieldName))
{
throw new Exception("BackwardArticleField definition should have non-empty FieldName");
}
IEnumerable <IProductDataSource> containersCollection = productDataSource.GetContainersCollection(fieldInDef.FieldName);
var backwardArticleField = new BackwardArticleField
{
SubContentId = fieldInDef.Content.ContentId,
};
if (containersCollection != null)
{
foreach (Article article in containersCollection.Select(x => DeserializeArticle(x, fieldInDef.Content, connector, context)))
{
backwardArticleField.Items.Add(article.Id, article);
}
}
return(backwardArticleField);
}
