/// <summary>
/// Handles the TextChanged event of the Name_Search_TextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void Name_Search_TextBox_TextChanged(object sender, TextChangedEventArgs e)
{
// if both of the datepickers are with dates
if (!string.IsNullOrWhiteSpace(Start_datePicker.Text) || !string.IsNullOrWhiteSpace(End_datePicker.Text))
{
Fillterdates();
}
if (mistake == false)
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string searchidkey = CSQ.checkForSingleQuotationMark(this.Name_Search_TextBox.Text);
//string searchidkey = this.Name_Search_TextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%'", searchidkey);
}
}
/// <summary>
/// Handles the TextChanged event of the IDSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void IDSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchidkey = this.IDSearchTextBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
searchidkey = CSQ.checkForSingleQuotationMark(searchidkey);
string Query1 = "SELECT costumerid as `חפ לקוח`,costumerName as `שם לקוח` ,costumer_insideNum as `מספר לקוח`,costumerAddress as `כתובת לקוח`,costumerDesc as `הערות בקשר ללקוח` from project.costumers where costumerid Like '%" + searchidkey + "%' group by costumerid";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
// DataTable dt = new DataTable("Custumers");
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Updategrids this instance.
/// </summary>
public void updategrid()
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string Query1 = ("SELECT itemStageOrder as `מספר שלב`,stageName as `שם השלב`, stage_discription as `תאור השלב` FROM item WHERE itemid='" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "' AND itemStatus='בעבודה'");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Handles the TextChanged event of the JobIDSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void JobIDSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
String searchkey = CSQ.checkForSingleQuotationMark(this.JobIDSearchTextBox.Text);
// String searchkey = this.JobIDSearchTextBox.Text;
string Query1 = ("SELECT jobid as `מספר עבודה`, reg_date as `תאריך רישום`,orderid as`מספר הזמנה` ,jobdescription as `תאור עבודה` ,jobs.costumerid as `חפ לקוח` ,costumers.costumerName as `שם לקוח` ,contact_id as `מספר איש קשר` , costumers.contactName as `שם איש קשר` ,job_status as `סטטוס עבודה`,startDate as `תאריך התחלה`,expectedFinishDate as `תאריך סיום משוער` ,actualFinishDate as `תאריך סיום בפועל` ,deliveryid as `תעודת משלוח` ,invoiceNumber as `מספר חשבונית` from project.jobs, project.costumers where jobid Like '" + searchkey + "%' AND jobs.costumerid=costumers.costumerid AND jobs.contact_id=costumers.contactid GROUP BY jobid");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Handles the Click event of the Add_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_button_Click(object sender, RoutedEventArgs e)
{
hpcusid_W_label.Visibility = Visibility.Hidden;
internalcusid_W_label.Visibility = Visibility.Hidden;
cusname_W_label.Visibility = Visibility.Hidden;
cusaddress_W_label.Visibility = Visibility.Hidden;
name_W_label.Visibility = Visibility.Hidden;
mail_W_label.Visibility = Visibility.Hidden;
phone_W_label.Visibility = Visibility.Hidden;
both_W_label.Visibility = Visibility.Hidden;
cell_W_label.Visibility = Visibility.Hidden;
dep_W_label.Visibility = Visibility.Hidden;
bool f1 = false, f2 = false, f3 = false, f4 = false, f5 = false, f6 = false, f7 = false, f8 = false, f9 = false, f10 = false, f11 = false, f12 = false;
if (!string.IsNullOrWhiteSpace(hpcusid_textBox.Text))
{
try
{
int hpcustideCheck = Convert.ToInt32(hpcusid_textBox.Text);
hpcusid = hpcusid_textBox.Text;
f1 = true;
}
catch
{
hpcusid_W_label.Content = "ח.פ. הלקוח חייב להכיל מספרים בלבד!";
hpcusid_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!ח.פ. הלקוח חייב להכיל מספרים בלבד");
}
}
else
{
hpcusid_W_label.Content = "אנא הכנס חפ לקוח";
hpcusid_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס חפ לקוח ");
}
if (!string.IsNullOrWhiteSpace(internalcusid_textBox.Text))
{
try
{
int hpcustideCheck = Convert.ToInt32(internalcusid_textBox.Text);
internalcusid = internalcusid_textBox.Text;
f8 = true;
}
catch
{
internalcusid_W_label.Content = "מספר הלקוח חייב להכיל מספרים בלבד!";
internalcusid_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!מספר הלקוח חייב להכיל מספרים בלבד");
}
}
else
{
internalcusid = "לא הוזן";
f8 = true;
// internalcusid_W_label.Content = "אנא הכנס מס לקוח";
// internalcusid_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס מס לקוח ");
}
// if (firstname_textBox.Text != null)
if (!string.IsNullOrWhiteSpace(custname_textBox.Text))
{
/*
bool instring = false;
instring = custname_textBox.Text.Contains("'");
if (instring)
{
MessageBox.Show("אסור להשתמש בגרש");
return;
}
*/
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
custname = CSQ.checkForSingleQuotationMark(custname_textBox.Text);
//custname = custname_textBox.Text;
f2 = true;
// MessageBox.Show("" + username + "");
}
else
{
cusname_W_label.Content = "אנא הכנס שם לקוח";
cusname_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס שם לקוח");
}
// if (address_textBox != null)
if (!string.IsNullOrWhiteSpace(address_textBox.Text))
{
//cusaddress = address_textBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
cusaddress = CSQ.checkForSingleQuotationMark(address_textBox.Text);
f3 = true;
}
else
{
cusaddress_W_label.Content = "אנא הכנס כתובת לקוח";
cusaddress_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס כתובת לקוח ");
}
// if (email_textBox1.Text != null)
if (!string.IsNullOrWhiteSpace(email_textBox1.Text))
{
if ((Regex.IsMatch(this.email_textBox1.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")))
{
contact_email = email_textBox1.Text;
// MessageBox.Show("" + email + "");
f4 = true;
}
else
{
mail_W_label.Content = "אנא בדוק תקינות כתובת האימייל";
mail_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא בדוק תקינות כתובת האימייל");
}
}
else
{
contact_email = "לא הוזן";
f4 = true;
// mail_W_label.Content = "אנא הכנס כתובת אימייל";
// mail_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס כתובת אימייל");
}
if (!string.IsNullOrWhiteSpace(contact_name_textBox.Text))
{
//contact_name = contact_name_textBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
contact_name = CSQ.checkForSingleQuotationMark(contact_name_textBox.Text);
f5 = true;
}
else
{
name_W_label.Content = "אנא הכנס שם איש קשר";
name_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס שם איש קשר ");
}
if (!string.IsNullOrWhiteSpace(cont_phone_text.Text))
{
try
{
int phoneCheck = Convert.ToInt32(cont_phone_text.Text);
contact_phone = cont_phone_text.Text;
f10 = true; //phone
}
catch
{
f11 = true;
phone_W_label.Content = "מספר הטלפון חייב להכיל מספרים בלבד!";
phone_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!מספר הטלפון חייב להכיל מספרים בלבד");
}
}
if (!string.IsNullOrWhiteSpace(cell_textBox.Text))
{
try
{
int cellphoneCheck = Convert.ToInt32(cell_textBox.Text);
contact_cellphone = cell_textBox.Text;
f9 = true; //cell
}
catch
{
f12 = true;
cell_W_label.Content = "מספר נייד חייב להכיל מספרים בלבד!";
cell_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!מספר הטלפון נייד חייב להכיל מספרים בלבד");
}
}
if (f9 || f10) //user enterd phone and/or cellphone correctly.
{
if (!f11 && !f12) // if non was wrong.
{
f6 = true;
}
}
//user did not enterd cellphone and/or phone.
if (string.IsNullOrWhiteSpace(cont_phone_text.Text) && string.IsNullOrWhiteSpace(cell_textBox.Text))
{
both_W_label.Content = "אנא הכנס מספר טלפון ו/או נייד עבור איש הקשר";
both_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס מספר טלפון ו/או נייד עבור איש הקשר ");
}
if (!string.IsNullOrWhiteSpace(cont_dep_text.Text))
{
//contact_dep = cont_dep_text.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
contact_dep = CSQ.checkForSingleQuotationMark(cont_dep_text.Text);
f7 = true;
}
else
{
contact_dep = "לא הוזן";
f7 = true;
// dep_W_label.Content = "אנא הכנס מחלקת איש קשר";
// dep_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס מחלקת איש קשר ");
}
// if all is ok then add new user to the DB.
if (f1 && f2 && f3 && f4 && f5 && f6 && f7 && f8)
{
int hp = 0;
int count = 0;
//string not = "לא מחובר";
// string query = ("insert into project.costumers (costumerid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment) values ('" + hpcusid + "','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + cusaddress + "','" + contact_dep + "')");
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string query1 = ("select costumerid from costumers where costumerid='" + hpcusid + "'");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
while (dr.Read())
{
hp++;
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
if (internalcusid != "לא הוזן")
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string query2 = ("select costumer_insideNum from costumers where costumer_insideNum='" + internalcusid + "' AND costumer_insideNum !='לא הוזן` ");
MySqlCommand MSQLcrcommand2 = new MySqlCommand(query2, MySqlConn);
MSQLcrcommand2.ExecuteNonQuery();
MySqlDataReader dr2 = MSQLcrcommand2.ExecuteReader();
while (dr2.Read())
{
count++;
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
else
{
count=0;
}
//MessageBox.Show("" + count + "");
if (hp == 0 && count == 0)
{
// if only phone
string query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + hpcusid + "','1','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + cusaddress + "','" + contact_dep + "','" + internalcusid + "')");
if (!f10 && f9) // if only cell
{
query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactCellPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + hpcusid + "','1','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_cellphone + "','" + cusaddress + "','" + contact_dep + "','" + internalcusid + "')");
}
if (f9 && f10) // if both
{
query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,contactCellPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + hpcusid + "','1','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + contact_cellphone + "','" + cusaddress + "','" + contact_dep + "','" + internalcusid + "')");
}
//string query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment) values ('" + hpcusid + "',' 1 ','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + cusaddress + "','" + contact_dep + "')");
DBConnection DBC = new DBConnection();
Console.WriteLine("שאילתת ההוספה");
Console.WriteLine(query);
DBC.InsertDataIntoDB(Login.Connectionstring, query);
hpcusid_textBox.Clear();
internalcusid_textBox.Clear();
custname_textBox.Clear();
address_textBox.Clear();
contact_name_textBox.Clear();
email_textBox1.Clear();
cont_phone_text.Clear();
cell_textBox.Clear();
cont_dep_text.Clear();
try
{
MySqlConnection MySqlConn1 = new MySqlConnection(Login.Connectionstring);
MySqlConn1.Open();
string Query1 = ("SELECT costumerid as `חפ לקוח`,costumerName as `שם לקוח` ,costumer_insideNum as `מספר לקוח`,costumerAddress as `כתובת לקוח`,costumerDesc as `הערות בקשר ללקוח` from project.costumers group by costumerid");
MySqlCommand MSQLcrcommand11 = new MySqlCommand(Query1, MySqlConn1);
MSQLcrcommand11.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand11);
// DataTable dt = new DataTable("custumers");
ManagerCusGui.dt.Clear();
mysqlDAdp.Fill(ManagerCusGui.dt);
//dataGrid1.ItemsSource = ManagerCusGui.dt.DefaultView;
mysqlDAdp.Update(ManagerCusGui.dt);
MySqlConn1.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
if (hp > 0 && count > 0)
{
MessageBox.Show("מספר ח.פ. ומספר לקוח שהוזנו כבר קיימים במערכת", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (hp > 0)
{
MessageBox.Show("מספר ח.פ. שהוזן כבר קיים במערכת", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else
{
MessageBox.Show("מספר לקוח כבר קיים במערכת ", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
}
}
/// <summary>
/// Handles the TextChanged event of the CustumerNameSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void CustumerNameSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchkey = this.CustumerNameSearchTextBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
searchkey = CSQ.checkForSingleQuotationMark(searchkey);
string Query1 = "select costumerid as `חפ לקוח`,costumerName as `שם לקוח` ,costumerAddress as `כתובת לקוח` from project.costumers where costumerName Like '%" + searchkey + "%' group by costumerid";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
// DataTable dt = new DataTable("custumers");
// DataColumn d = dt.Columns["בחר/י לקוח"];
dt.Clear();
// dt.Columns.Remove("בחר/י לקוח");
// dt.Columns.Add(d);
// dt.Columns["בחר לקוח"] = d;
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Handles the Click event of the UpdateBtn control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void UpdateBtn_Click(object sender, RoutedEventArgs e)
{
try
{
DataRowView row = (DataRowView)dataGrid1.SelectedItems[0];
if (MessageBox.Show("?האם אתה בטוח שברצונך לעדכן פריט זה", "וידוא עדכון", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
{
//dont do stuff
}
else // if the user clicked on "Yes" so he wants to Update.
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string selected_Item = row["מקט פריט"].ToString();
selected_Item = CSQ.checkForSingleQuotationMark(selected_Item);
string itemname = row["שם פריט"].ToString();
itemname = CSQ.checkForSingleQuotationMark(itemname);
string itemdesc = row["תיאור פריט"].ToString();
itemdesc = CSQ.checkForSingleQuotationMark(itemdesc);
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = "UPDATE item SET itemName='" + itemname + "',item_discription='" + itemdesc + "' WHERE itemid='" + selected_Item + "' ";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlConn.Close();
MessageBox.Show("תבנית הפריט עודכנה", "!הצלחה", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT itemid as `מקט פריט`, itemName as `שם פריט`,item_discription as `תיאור פריט` FROM item GROUP BY itemid");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
catch
{
MessageBox.Show("לא נבחרה תבנית פריט לעדכון", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
/// <summary>
/// Handles the TextChanged event of the IDSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void IDSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
// string Connectionstring = " Server=localhost;Database=project; UId=root;Password=1234;";
// MySqlConnection MySqlConn = new MySqlConnection(Connectionstring);
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchidkey = this.IDSearchTextBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
searchidkey = CSQ.checkForSingleQuotationMark(searchidkey);
// string Query1 = "select users.empid as `תעודת זהות` ,employees.emp_firstname as `שם פרטי` ,employees.emp_lastname as `שם משפחה` ,users.user_name as `שם משתמש` ,password as סיסמה ,role as תפקיד ,connected as מחובר ,email as `כתובת אימייל` from project.users , project.employees where users.empid=employees.empid and users.empid Like '%" + searchidkey + "%' ";
string Query1 = ("SELECT users.empid as `תעודת זהות`,employees.emp_firstname as `שם פרטי` ,employees.emp_lastname as `שם משפחה` ,password as סיסמה ,role as תפקיד ,connected as מחובר ,email as `כתובת אימייל`, rec_answer as `בית ספר יסודי`,users.last_log_in_date as `התחברות אחרונה` ,last_location as `התחברות אחרונה ממחשב` , users.user_description as `הערות לגבי המשתמש` from project.users , project.employees where users.empid=employees.empid and users.empid Like '%" + searchidkey + "%' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
// DataTable dt = new DataTable("users");
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
reafreshandclear();
}
}
/// <summary>
/// Handles the TextChanged event of the itemNameSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void itemNameSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
String searchkey = CSQ.checkForSingleQuotationMark(this.itemNameSearchTextBox.Text);
// String searchkey = this.itemNameSearchTextBox.Text;
string Query1 = ("SELECT itemid as `מקט פריט`, itemName as `שם פריט`,item_discription as `תיאור פריט` FROM item WHERE itemName Like '%" + searchkey + "%' GROUP BY itemid ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Handles the Click event of the item_stages_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void item_stages_button_Click(object sender, RoutedEventArgs e)
{
try
{
DataRowView row = (DataRowView)dataGrid1.SelectedItems[0];
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string selected_Item = CSQ.checkForSingleQuotationMark( row["מקט פריט"].ToString() );
ManagerGeneralItemStagesGui MGIG = new ManagerGeneralItemStagesGui(selected_Item);
MGIG.Owner = this;
MGIG.ShowDialog();
}
catch
{
MessageBox.Show("לא נבחר פריט", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
/// <summary>
/// Handles the TextChanged event of the StageNameSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void StageNameSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
String searchNamekey = CSQ.checkForSingleQuotationMark(this.StageNameSearchTextBox.Text);
// String searchNamekey = this.StageNameSearchTextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם פריט` LIKE '%{0}%'", searchNamekey);
/*
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchNamekey = this.StageNameSearchTextBox.Text;
string Query1 = ("select itemid as `מקט פריט`,itemName as `שם פריט`, item_discription as `תאור פריט` FROM jobs,item WHERE jobs.itemid=item.itemid and jobs.itemStageOrder=item.itemStageOrder and jobs.itemStatus=item.itemStatus and jobs.jobid='" + jobID + "' and item.stageName Like '%" + searchNamekey + "%'");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
*/
}
/// <summary>
/// Handles the Click event of the Add_Existing_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_Existing_button_Click(object sender, RoutedEventArgs e)
{
try
{
DataTable changedRecordsItemsTable = dt.GetChanges();
int sizeofItemsnewtable = changedRecordsItemsTable.Rows.Count;
int count = 0; // will count the number of rows with cell "" in the changedRecordsItemsTable.
Console.WriteLine("sizeofItemsnewtable = " + sizeofItemsnewtable);
// test the input
foreach (DataRow testrow in changedRecordsItemsTable.Rows)// for every row in the updateds table.
{
string q = testrow["כמות"].ToString();
Console.WriteLine("q = " + q);
try
{
if (q != "") // in case the user deleted a cell in the item and now it have a string of- "" .
{
int new_item_quantity = Convert.ToInt32(q);
if (new_item_quantity > 0)
{
Console.WriteLine("הקלט היה טוב במקט מספר =" + testrow["מקט פריט"].ToString());
}//end if (item_quantity > 0)
else
{
MessageBox.Show("שדה הכמות מכיל כמות שלילית או 0 במקט פריט - " + testrow["מקט פריט"].ToString() + "", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
} // if (q != "")
else { count++; }
}// end try
catch
{
MessageBox.Show("שדה הכמות לא כולל רק מספרים במקט פריט - " + testrow["מקט פריט"].ToString() + "", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
} // end of foreach to test the input
if (count == changedRecordsItemsTable.Rows.Count)
{
MessageBox.Show(" לא נבחרו פריטים מהטבלה ", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// if the unpute was OK then we can add the items.
count = 0;
foreach (DataRow dri in changedRecordsItemsTable.Rows)// for every row in the updateds table.
{
string q = dri["כמות"].ToString();
Console.WriteLine("q = " + q);
string itemid = dri["מקט פריט"].ToString();
Console.WriteLine("itemid = " + itemid);
//string cost_itemid = dri["מקט לקוח"].ToString();
try
{
if (q != "") // in case the user deleted a cell in the item and now it have a string of- "" .
{
int new_item_quantity = Convert.ToInt32(q) , maxItemNum=0;
if (new_item_quantity > 0)
{
string itemStatus = "רישום", itemStageOrder = "1";
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = "(SELECT *, MAX(itemNum) FROM project.jobs WHERE jobid='" + jobID + "' AND itemid='" + itemid + "')";
Console.WriteLine("שורה 267 Query1 = " + Query1);
MySqlCommand crcommand1 = new MySqlCommand(Query1, MySqlConn);
crcommand1.ExecuteNonQuery();
MySqlDataReader dr1 = crcommand1.ExecuteReader();
int count1 = 0, expectedItemQuantity=0;
string costumerid = "", itemsDescription = "", job_status = "", jobdescription = "", startDate = "", expectedFinishDate = "", contact_id = "", orderid = "", group_Status = "", group_StageOrder = "", deliveryid = "", invoiceNumber = "", group_costomer_itemid = "", group_itemToFixStageOrder = "", reg_date="";
while (dr1.Read())
{
if (!dr1.IsDBNull(0))
{
count1++; // inc count only if there are already items with this itemid for this jobid.
Console.WriteLine("שורה 279 count1 = " + count1);
maxItemNum = dr1.GetInt32(25); // get the added MAX(itemNum) column
Console.WriteLine("שורה 281 maxItemNum = " + maxItemNum);
expectedItemQuantity = dr1.GetInt32(5);
Console.WriteLine("שורה 283 expectedItemQuantity = " + expectedItemQuantity);
costumerid = dr1.GetString(6);
Console.WriteLine("שורה 285 costumerid = " + costumerid);
itemsDescription = dr1.GetString(8);
Console.WriteLine("שורה 287 itemsDescription = " + itemsDescription);
job_status = dr1.GetString(10);
Console.WriteLine("שורה 289 job_status = " + job_status);
jobdescription = dr1.GetString(11);
Console.WriteLine("שורה 291 jobdescription = " + jobdescription);
startDate = dr1.GetString(12);
Console.WriteLine("שורה 293 startDate = " + startDate);
startDate = Convert.ToDateTime(startDate).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 295 startDate = " + startDate);
expectedFinishDate = dr1.GetString(13);
Console.WriteLine("שורה 297 expectedFinishDate = " + expectedFinishDate);
expectedFinishDate = Convert.ToDateTime(expectedFinishDate).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 299 expectedFinishDate = " + expectedFinishDate);
contact_id = dr1.GetString(15);
Console.WriteLine("שורה 301 contact_id = " + contact_id);
orderid = dr1.GetString(16);
Console.WriteLine("שורה 303 orderid = " + orderid);
group_Status = dr1.GetString(17);
Console.WriteLine("שורה 305 group_Status = " + group_Status);
group_StageOrder = dr1.GetString(18);
Console.WriteLine("שורה 307 group_StageOrder = " + group_StageOrder);
deliveryid = dr1.GetString(19);
Console.WriteLine("שורה 309 deliveryid = " + deliveryid);
invoiceNumber = dr1.GetString(20);
Console.WriteLine("שורה 311 invoiceNumber = " + invoiceNumber);
group_costomer_itemid = dr1.GetString(21);
Console.WriteLine("שורה 312 group_costomer_itemid = " + group_costomer_itemid);
group_itemToFixStageOrder= dr1.GetString(22);
Console.WriteLine("שורה 315 group_itemToFixStageOrder = " + group_itemToFixStageOrder);
reg_date = dr1.GetString(24);
Console.WriteLine("שורה 317 reg_date = " + reg_date);
reg_date = Convert.ToDateTime(reg_date).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 319 reg_date = " + reg_date);
}
}
MySqlConn.Close();
Console.WriteLine("שורה 323 לפני בדיקה האם המקט כבר קיים");
if (count1 == 1)// if an itemid already exist in this job.
{
// int new_expected = new_item_quantity + expectedItemQuantity;
for (int i = 1; i <= new_item_quantity; i++)
{
Console.WriteLine("שורה 329 if (count1 == 1)// if an itemid already exist in this job.");
maxItemNum++;
// string itemid = dri["מקט פריט"].ToString();
try
{
MySqlConnection MySqlConn1 = new MySqlConnection(Login.Connectionstring);
MySqlConn1.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string Query2 = ("INSERT INTO project.jobs (jobid, itemid,itemNum,itemStatus,itemStageOrder, expectedItemQuantity,costumerid, itemsDescription, job_status, jobdescription, startDate, expectedFinishDate, contact_id , orderid , group_Status , group_StageOrder , deliveryid , invoiceNumber , group_costomer_itemid, group_itemToFixStageOrder, reg_date ) VALUES ('" + jobID + "','" + CSQ.checkForSingleQuotationMark(itemid) + "','" + maxItemNum + "','" + itemStatus + "','" + itemStageOrder + "','" + expectedItemQuantity + "','" + costumerid + "','" + CSQ.checkForSingleQuotationMark(itemsDescription) + "','" + job_status + "','" + CSQ.checkForSingleQuotationMark(jobdescription) + "','" + startDate + "','" + expectedFinishDate + "','" + contact_id + "','" + orderid + "','" + group_Status + "','" + group_StageOrder + "','" + deliveryid + "','" + invoiceNumber + "','" + group_costomer_itemid + "','" + group_itemToFixStageOrder + "','" + reg_date + "')");
Console.WriteLine("שורה 377 Query2 = " + Query2);
MySqlCommand MSQLcrcommand2 = new MySqlCommand(Query2, MySqlConn1);
MSQLcrcommand2.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp1 = new MySqlDataAdapter(MSQLcrcommand2);
MySqlConn1.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Console.WriteLine("if (for (int i = 1; i <= new_item_quantity; i++))");
return;
}
}//end for (int i = 1; i <= new_item_quantity; i++)
} // end of if (count1 == 1)// if an itemid already exist in this job.
else // cound1==0 so no such itemid for this jobid.
{
try
{
Console.WriteLine("שורה 373 cound1==0 so no such itemid for this jobid. ");
MySqlConnection MySqlConn5 = new MySqlConnection(Login.Connectionstring);
MySqlConn5.Open();
string Query5 = "(SELECT *, MAX(itemNum) FROM project.jobs WHERE jobid='" + jobID + "')";
Console.WriteLine("שורה 377 Query5 = " + Query5);
MySqlCommand crcommand5 = new MySqlCommand(Query5, MySqlConn5);
crcommand5.ExecuteNonQuery();
MySqlDataReader dr5 = crcommand5.ExecuteReader();
while (dr5.Read())
{
expectedItemQuantity = dr5.GetInt32(5);
Console.WriteLine("שורה 384 expectedItemQuantity = " + expectedItemQuantity);
costumerid = dr5.GetString(6);
Console.WriteLine("שורה 386 costumerid = " + costumerid);
itemsDescription = dr5.GetString(8);
Console.WriteLine("שורה 388 itemsDescription = " + itemsDescription);
job_status = dr5.GetString(10);
Console.WriteLine("שורה 390 job_status = " + job_status);
jobdescription = dr5.GetString(11);
Console.WriteLine("שורה 392 jobdescription = " + jobdescription);
startDate = dr5.GetString(12);
Console.WriteLine("שורה 394 startDate = " + startDate);
startDate = Convert.ToDateTime(startDate).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 396 startDate = " + startDate);
expectedFinishDate = dr5.GetString(13);
Console.WriteLine("שורה 398 expectedFinishDate = " + expectedFinishDate);
expectedFinishDate = Convert.ToDateTime(expectedFinishDate).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 400 expectedFinishDate = " + expectedFinishDate);
contact_id = dr5.GetString(15);
Console.WriteLine("שורה 402 contact_id = " + contact_id);
orderid = dr5.GetString(16);
Console.WriteLine("שורה 405 orderid = " + orderid);
deliveryid = dr5.GetString(19);
Console.WriteLine("שורה 407 deliveryid = " + deliveryid);
invoiceNumber = dr5.GetString(20);
Console.WriteLine("שורה 409 invoiceNumber = " + invoiceNumber);
reg_date = dr5.GetString(24);
Console.WriteLine("שורה 411 reg_date = " + reg_date);
reg_date = Convert.ToDateTime(reg_date).ToString("yyyy-MM-dd");
Console.WriteLine("שורה 413 reg_date = " + reg_date);
}
MySqlConn5.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Console.WriteLine("שורה 423");
return;
}
int itemNum = 0;
for (int i = 1; i <= new_item_quantity; i++)
{
itemNum++;
Console.WriteLine("שורה 430 itemNum = " + itemNum);
try
{
MySqlConnection MySqlConn4 = new MySqlConnection(Login.Connectionstring);
MySqlConn4.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string Query4 = ("INSERT INTO project.jobs (jobid, itemid,itemNum,itemStatus,itemStageOrder, expectedItemQuantity,costumerid, itemsDescription, job_status, jobdescription, startDate, expectedFinishDate, contact_id , orderid , deliveryid , invoiceNumber , reg_date) VALUES ('" + jobID + "','" + CSQ.checkForSingleQuotationMark(itemid) + "','" + itemNum + "','" + itemStatus + "','" + itemStageOrder + "','" + new_item_quantity + "','" + costumerid + "','" + CSQ.checkForSingleQuotationMark(itemsDescription) + "','" + job_status + "','" + CSQ.checkForSingleQuotationMark(jobdescription) + "','" + startDate + "','" + expectedFinishDate + "','" + contact_id + "','" + orderid + "','" + deliveryid + "','" + invoiceNumber + "','" + reg_date + "')");
Console.WriteLine("שורה 436 Query4 = " + Query4);
MySqlCommand MSQLcrcommand4 = new MySqlCommand(Query4, MySqlConn4);
MSQLcrcommand4.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp4 = new MySqlDataAdapter(MSQLcrcommand4);
MySqlConn4.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Console.WriteLine("שורה 445");
return;
}
} // end for (int i = 1; i <= new_item_quantity; i++)
}// end else // cound1==0 so no such itemid for this jobid.
}// end of first try.
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Console.WriteLine("Query1");
return;
}
}//end if (item_quantity > 0)
else
{
MessageBox.Show("שדה הכמות מכיל כמות שלילית או 0 בפריט מספר - " + dri["מקט פריט"].ToString() + "" , "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
} // if (q != "")
else { count++; }
}// end try
catch
{
MessageBox.Show("שדה הכמות לא כולל רק מספרים בפריט מספר - " + dri["מקט פריט"].ToString() + "", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}// end foreach (DataRow dri in changedRecordsTable.Rows)}
if (count == changedRecordsItemsTable.Rows.Count)
{
MessageBox.Show(" לא נבחרו פריטים מהטבלה ", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error); return;
}
else
{
MessageBox.Show(".!הפריט/ים נוסף/ו לעבודה", "!הצלחה", MessageBoxButton.OK, MessageBoxImage.Information);
ManagerJobInfoGui MJIG = new ManagerJobInfoGui(jobID);
MJIG.Show();
Login.close = 1;
this.Close();
}
}
catch
{
MessageBox.Show("לא נבחרו פריטים", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
//כפתור כניסה
/// <summary>
/// Handles the Click event of the button1 control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void button1_Click(object sender, RoutedEventArgs e)
{
try
{
if (client.Equals(true)) // if this is a remote computer.
{
serverip = this.Host_textBox.Text;
Connectionstring = "Server=" + serverip + "; Database=project; UId=root;Password=1234;";
// MessageBox.Show("" + Connectionstring +"");
}
else if (client.Equals(false)) // // if this is the host computer (the one with the SQL DataBase on it).
{
Connectionstring = "Server=localhost;Database=project; UId=root;Password=1234;";
}
// string Connectionstring = " Server=localhost;Database=project; UId=root;Password=1234;";
MySqlConnection objc = new MySqlConnection(Connectionstring);
try
{
// MessageBox.Show("ניסיון התחברות");
objc.Open();
// MessageBox.Show("התחברות הצליחה");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string pass = this.textBox2.Password;
Console.WriteLine(pass);
pass = CSQ.checkForSingleQuotationMark(pass);
Console.WriteLine("לאחר בדיקת ציטוט בסיסמה");
Console.WriteLine(pass);
string Query = "select * from project.users where empid='" + CSQ.checkForSingleQuotationMark(this.textBox1.Text) + "'and password='******'";
Console.WriteLine("השאילתה הנשלחת בכניסה:");
Console.WriteLine(Query);
MySqlCommand crcommand = new MySqlCommand(Query, objc);
crcommand.ExecuteNonQuery();
MySqlDataReader dr = crcommand.ExecuteReader();
int count = 0;
while (dr.Read())
{
count++;
user_role = dr.GetString(2);
}
if (count == 1)
{
// string Connectionstring1 = " Server=localhost;Database=project; UId=root;Password=1234;";
// MySqlConnection objc1 = new MySqlConnection(Connectionstring1);
MySqlConnection objc1 = new MySqlConnection(Connectionstring);
try
{
//MessageBox.Show(" 1 ניסיון התחברות");
objc1.Open();
// MessageBox.Show("התחברות הצליחה 1");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
string Query1 = "SELECT employees.emp_firstname, employees.emp_lastname, users.connected, users.email FROM project.users, project.employees WHERE users.empid=employees.empid and users.empid='" + this.textBox1.Text + "'and users.password='******'";
//MySqlCommand crcommand1 = new MySqlCommand(Query1, objc);
MySqlCommand crcommand1 = new MySqlCommand(Query1, objc1);
crcommand1.ExecuteNonQuery();
MySqlDataReader dr1 = crcommand1.ExecuteReader();
int count1 = 0;
while (dr1.Read())
{
count1++;
first_name = dr1.GetString(0);
first_name1 = first_name;
first_name = CSQ.checkForSingleQuotationMark(first_name);
last_name = dr1.GetString(1);
last_name1 = last_name;
last_name = CSQ.checkForSingleQuotationMark(last_name);
connected = dr1.GetString(2);
useremail = dr1.GetString(3);
}
//MessageBox.Show(""+connected+"");
if (count1 == 1)
{
if (connected != "מחובר" && connected != "לא מחובר")
{
MessageBox.Show("קיימת בעיה במצב החיבור שלך, יש לפנות למנהל המערכת ", " שגיאה", MessageBoxButton.OK,MessageBoxImage.Error);
}
if (connected.Equals("מחובר"))
{
MessageBox.Show("אתה כבר מחובר למערכת ", " שגיאה", MessageBoxButton.OK,MessageBoxImage.Error);
}
if (connected.Equals("לא מחובר"))
{
MessageBox.Show(" ברוכ/ה הבא/ה " + Login.last_name1 + " " + Login.first_name1 + "", "!ההתחברות למערכת בוצעה בהצלחה", MessageBoxButton.OK,MessageBoxImage.Information);
empid = this.textBox1.Text;
string user_connected = "מחובר";
//string user_connected = "לא מחובר";
if (user_role.Equals("מנהל"))
{
DBConnection conn = new DBConnection();
Console.WriteLine(my_host_name);
string query2 = "UPDATE users SET connected='" + user_connected + "',last_log_in_date='" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + "' ,last_location='" + my_host_name + "' WHERE empid= '" + this.textBox1.Text + "' and password ='******' ";
conn.LogIn(query2, Connectionstring);
//conn.LogIn(query2);
ManagerGui MG = new ManagerGui();
MG.Show();
}
if (user_role.Equals("מזכירה"))
{
DBConnection conn = new DBConnection();
string query2 = "UPDATE users SET connected='" + user_connected + "',last_log_in_date='" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + "' ,last_location='" + my_host_name + "' WHERE empid= '" + this.textBox1.Text + "' and password ='******'";
//conn.LogIn(query2);
conn.LogIn(query2, Connectionstring);
SecretaryGui SG = new SecretaryGui();
SG.Show();
}
if (user_role.Equals("איכות"))
{
DBConnection conn = new DBConnection();
string query2 = "UPDATE users SET connected='" + user_connected + "',last_log_in_date='" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + "' ,last_location='" + my_host_name + "' WHERE empid= '" + this.textBox1.Text + "' and password ='******'";
// conn.LogIn(query2);
conn.LogIn(query2, Connectionstring);
QualityGui QG = new QualityGui();
QG.Show();
}
this.Close();
// MGui.ShowDialog();
// this.Close();
}
// else
// {
// MessageBox.Show("קיימת בעיה במצב החיבור שלך, יש לפנות למנהל המערכת ", " שגיאה", MessageBoxButton.OK);
// }
}
else
{
MessageBox.Show("!אינך משתמש פעיל במערכת, אנא פנה למנהל", "!ההתחברות למערכת נכשלה", MessageBoxButton.OK,MessageBoxImage.Error);
textBox1.Clear();
textBox2.Clear();
}
objc1.Close();
}
if (count < 1)
{
MessageBox.Show("שם משתמש ו/או סיסמא שגויים! אנא נסה שנית", "!ההתחברות למערכת נכשלה", MessageBoxButton.OK,MessageBoxImage.Error);
// textBox1.Clear();
// textBox2.Clear();
}
objc.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// This func will check and add the new user to the DB if all is ok.
/// <summary>
/// Handles the Click event of the Add_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_button_Click(object sender, RoutedEventArgs e)
{
try
{
Console.WriteLine("נכנס לטראי");
DataRowView row1 = (DataRowView)dataGrid1.SelectedItems[0];
}
catch { MessageBox.Show("אנא בחר עובד", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error); return; }
DataRowView row = (DataRowView)dataGrid1.SelectedItems[0];
string empid = row["תעודת זהות"].ToString();
Console.WriteLine(empid);
bool f1 = false, f2 = false, f3 = false, f4 = false;
// if (Password_textBox != null)
if (Password_textBox != null && !string.IsNullOrWhiteSpace(Password_textBox.Password))
{
password = Password_textBox.Password;
f1 = true;
}
else
{
MessageBox.Show("אנא הכנס סיסמא", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
// if (Email_textBox.Text != null)
if (Email_textBox != null && !string.IsNullOrWhiteSpace(Email_textBox.Text))
{
if ((Regex.IsMatch(this.Email_textBox.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")))
{
email = Email_textBox.Text;
// MessageBox.Show("" + email + "");
f2 = true;
}
else
{
MessageBox.Show("אנא בדוק תקינות כתובת האימייל", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
MessageBox.Show("אנא הכנס כתובת אימייל", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (!string.IsNullOrWhiteSpace(school_textBox.Text))
{
school = school_textBox.Text;
f4 = true;
}
else
{
MessageBox.Show("אנא הכנס שם בית ספר יסודי", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (Role_comboBox.SelectedValue != null)
{
role = Role_comboBox.SelectedValue.ToString();
f3 = true;
}
else
{
MessageBox.Show("אנא בחר תפקיד", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
}
// if all is ok then add new user to the DB.
if (f1 && f2 && f3 & f4)
{
DateTime yesterday = DateTime.Today.AddDays(-1);
string date = Convert.ToDateTime(yesterday).ToString("yyyy-MM-dd");
Console.WriteLine(date);
//string not = "לא מחובר";
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string query = ("insert into project.users (empid, password, role , email , last_email_sent_date , rec_answer) values ('" + empid + "','" + CSQ.checkForSingleQuotationMark(password) + "','" + role + "','" + email + "','" + date + "','" + CSQ.checkForSingleQuotationMark(school) + "')");
DBConnection DBC = new DBConnection();
DBC.InsertDataIntoDB(Login.Connectionstring, query);
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT empid as `תעודת זהות`,emp_firstname as `שם פרטי` ,emp_lastname as `שם משפחה` , emp_insidenum as `מספר עובד` ,emp_address as `כתובת` ,emp_phone as `מספר טלפון`, emp_cellphone as `טלפון נייד`, emp_start_date as `תאריך התחלת עבודה` FROM project.employees WHERE employees.empid not in (SELECT users.empid FROM project.users)");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// Handles the Click event of the Add_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_button_Click(object sender, RoutedEventArgs e)
{
name_W_label.Visibility = Visibility.Hidden;
mail_W_label.Visibility = Visibility.Hidden;
phone_W_label.Visibility = Visibility.Hidden;
both_W_label.Visibility = Visibility.Hidden;
cell_W_label.Visibility = Visibility.Hidden;
dep_W_label.Visibility = Visibility.Hidden;
bool f1 = false, f2 = false, f3 = false, f4 = false, f5 = false, f6 = false, f7 = false, f8 = false;
// if (email_textBox1.Text != null)
if (!string.IsNullOrWhiteSpace(email_textBox1.Text))
{
if ((Regex.IsMatch(this.email_textBox1.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")))
{
contact_email = email_textBox1.Text;
f1 = true;
}
else
{
mail_W_label.Content = "אנא בדוק תקינות כתובת האימייל";
mail_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא בדוק תקינות כתובת האימייל");
}
}
else
{
contact_email = "לא הוזן";
f1 = true;
// mail_W_label.Content = "אנא הכנס כתובת אימייל";
// mail_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס כתובת אימייל");
}
if (!string.IsNullOrWhiteSpace(contact_name_textBox.Text))
{
contact_name = contact_name_textBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
contact_name = CSQ.checkForSingleQuotationMark(contact_name);
f2 = true;
}
else
{
name_W_label.Content = "אנא הכנס שם איש קשר";
name_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס שם איש קשר ");
}
if (!string.IsNullOrWhiteSpace(cont_phone_text.Text))
{
try
{
int phoneCheck = Convert.ToInt32(cont_phone_text.Text);
contact_phone = cont_phone_text.Text;
f5 = true;
}
catch
{
f7 = true;
phone_W_label.Content = "מספר הטלפון חייב להכיל מספרים בלבד!";
phone_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!מספר הטלפון חייב להכיל מספרים בלבד");
}
}
if (!string.IsNullOrWhiteSpace(cell_textBox.Text))
{
try
{
int cellphoneCheck = Convert.ToInt32(cell_textBox.Text);
contact_cellphone = cell_textBox.Text;
f6 = true;
}
catch
{
f8 = true;
cell_W_label.Content = "מספר הטלפון נייד חייב להכיל מספרים בלבד!";
cell_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("!מספר הטלפון נייד חייב להכיל מספרים בלבד");
}
}
if (f5 || f6) //user enterd phone and/or cellphone correctly.
{
if (!f7 && !f8) // if non was wrong.
{
f3 = true;
}
}
//user did not enterd cellphone and/or phone.
if (string.IsNullOrWhiteSpace(cont_phone_text.Text) && string.IsNullOrWhiteSpace(cell_textBox.Text))
{
both_W_label.Content = "אנא הכנס מספר טלפון ו/או נייד עבור איש הקשר";
both_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס מספר טלפון ו/או נייד עבור איש הקשר ");
}
if (!string.IsNullOrWhiteSpace(cont_dep_text.Text))
{
contact_dep = cont_dep_text.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
contact_dep = CSQ.checkForSingleQuotationMark(contact_dep);
f4 = true;
}
else
{
contact_dep = "לא הוזן";
f4 = true;
// dep_W_label.Content = "אנא הכנס מחלקת איש קשר";
// dep_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס מחלקת איש קשר ");
}
// if all is ok then add new user to the DB.
if (f1 && f2 && f3 && f4)
{
// string query = ("insert into project.costumers (costumerid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment) values ('" + cusid + "','" + custname + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + cusaddress + "','" + contact_dep + "')");
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
//MessageBox.Show("" + CostNum_label.Content + "");
string query1 = ("select MAX(contactid) from costumers where costumerid='" + CostNum_label.Content + "'");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
int max = Convert.ToInt32(MSQLcrcommand1.ExecuteScalar());
max++;
string name = CostName_label.Content.ToString();
Console.WriteLine(CostName_label.Content);
Console.WriteLine(name);
// if only phone
string query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + CostNum_label.Content + "','" + max + "','" + CSQ.checkForSingleQuotationMark(name) + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + CSQ.checkForSingleQuotationMark(cosADDs) + "','" + contact_dep + "','" + cos_num_label.Content + "')");
Console.WriteLine(query);
if (!f5 && f6) // if only cell
{
query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactCellPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + CostNum_label.Content + "','" + max + "','" + CSQ.checkForSingleQuotationMark(name) + "','" + contact_name + "','" + contact_email + "','" + contact_cellphone + "','" + CSQ.checkForSingleQuotationMark(cosADDs) + "','" + contact_dep + "','" + cos_num_label.Content + "')");
}
if (f5 && f6) // if both
{
query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,contactCellPhone,costumerAddress,contactDepartment,costumer_insideNum) values ('" + CostNum_label.Content + "','" + max + "','" + CSQ.checkForSingleQuotationMark(name) + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + contact_cellphone + "','" + CSQ.checkForSingleQuotationMark(cosADDs) + "','" + contact_dep + "','" + cos_num_label.Content + "')");
}
//string query = ("insert into project.costumers (costumerid, contactid, costumerName, contactName , contactEmail,contactPhone,costumerAddress,contactDepartment) values ('" + CostNum_label.Content + "','" + max + "','" + CostName_label.Content + "','" + contact_name + "','" + contact_email + "','" + contact_phone + "','" + cosADDs + "','" + contact_dep + "')");
DBConnection DBC = new DBConnection();
DBC.InsertDataIntoDB(Login.Connectionstring, query);
contact_name_textBox.Clear();
email_textBox1.Clear();
cont_phone_text.Clear();
cell_textBox.Clear();
cont_dep_text.Clear();
try
{
MySqlConnection MySqlConn1 = new MySqlConnection(Login.Connectionstring);
MySqlConn1.Open();
string Query1 = ("select contactid as `מספר איש קשר`,contactName as `שם איש קשר` ,contactEmail as `אימייל איש קשר` ,contactPhone as `טלפון איש קשר`,contactCellPhone as `טלפון נייד של איש הקשר` ,contactDepartment as `מחלקת איש קשר`, contactDesc as `הערות לגבי איש הקשר` from costumers where costumerid='" + CostNum_label.Content + "'");
MySqlCommand MSQLcrcommand11 = new MySqlCommand(Query1, MySqlConn1);
MSQLcrcommand11.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand11);
SecContactsGUI.dt.Clear();
mysqlDAdp.Fill(SecContactsGUI.dt);
mysqlDAdp.Update(SecContactsGUI.dt);
MySqlConn1.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// Handles the TextChanged event of the IDSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void IDSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
String searchidkey = CSQ.checkForSingleQuotationMark(this.IDSearchTextBox.Text);
//String searchidkey = this.IDSearchTextBox.Text;
string Query1 = "SELECT empid as `תעודת זהות`,emp_firstname as `שם פרטי` ,emp_lastname as `שם משפחה` , emp_insidenum as `מספר עובד` ,emp_address as `כתובת` ,emp_phone as `מספר טלפון`, emp_cellphone as `טלפון נייד`, emp_start_date as `תאריך התחלת עבודה` FROM employees WHERE empid Like '%" + searchidkey + "%' AND employees.empid not in (SELECT users.empid FROM project.users)";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
//DataTable dt = new DataTable("employees");
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//This function will filter the startDate by date selected from 2 DatePickers.
// if the dates were not right a message will be shown.
/// <summary>
/// Handles the Click event of the Filter_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Filter_button_Click(object sender, RoutedEventArgs e)
{
Fillterdates();
if (!string.IsNullOrWhiteSpace(Name_Search_TextBox.Text))
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string searchidkey = CSQ.checkForSingleQuotationMark(this.Name_Search_TextBox.Text);
// string searchidkey = this.Name_Search_TextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%'", searchidkey);
}
/*
//MessageBox.Show("" + Start_datePicker.Text + "");
if (Start_datePicker.Text != "" && End_datePicker.Text != "")
{
String start, end;
DateTime s = (DateTime)Convert.ToDateTime(Start_datePicker.Text);
DateTime f = (DateTime)Convert.ToDateTime(End_datePicker.Text);
TimeSpan ts = f - s;
//MessageBox.Show("" + s + "");
//MessageBox.Show("" + f + "");
//MessageBox.Show("" + ts.Days + "");
if (ts.Days >= 0)
{
start = Convert.ToDateTime(Start_datePicker.Text).ToString("yyyy-MM-dd");
end = Convert.ToDateTime(End_datePicker.Text).ToString("yyyy-MM-dd");
string radio = "startDate";
if (ExpectedFinishDate_radioButton.IsChecked == true)
{
radio = "expectedFinishDate";
}
if (ActualFinishDate_radioButton.IsChecked == true)
{
radio = "actualFinishDate";
}
//MessageBox.Show("" + radio + "");
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT jobid as `מספר עבודה`, reg_date as `תאריך רישום`,orderid as`מספר הזמנה` ,jobdescription as `תאור עבודה` ,jobs.costumerid as `חפ לקוח` ,costumers.costumerName as `שם לקוח` ,contact_id as `מספר איש קשר` , costumers.contactName as `שם איש קשר` ,job_status as `סטטוס עבודה`,startDate as `תאריך התחלה`,expectedFinishDate as `תאריך סיום משוער` ,actualFinishDate as `תאריך סיום בפועל` ,deliveryid as `תעודת משלוח` ,invoiceNumber as `מספר חשבונית` FROM jobs WHERE " + radio + " BETWEEN '" + start + "' AND '" + end + "' group by jobid ");
//MessageBox.Show("" + Query1 + "");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("אסור שתאריך הסוף הנבחר יהיה לפני תאריך ההתחלה הנבחר");
}
}
else
{
if (Start_datePicker.Text == "" && End_datePicker.Text != "")
{MessageBox.Show("לא נבחר תאריך התחלה לסינון");}
if (Start_datePicker.Text != "" && End_datePicker.Text == "")
{ MessageBox.Show("לא נבחר תאריך סוף לסינון"); }
if (Start_datePicker.Text == "" && End_datePicker.Text == "")
{ MessageBox.Show("לא נבחרו תאריכי התחלה וסוף לסינון "); }
}
*/
}
// This func will check and add the new user to the DB if all is ok.
/// <summary>
/// Handles the Click event of the Add_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_button_Click(object sender, RoutedEventArgs e)
{
bool f1 = false, f2 = false, f3 = false, f4 = false, f5 = false, f6 = false;
phone_W_label.Visibility = Visibility.Hidden;
id_W_label.Visibility = Visibility.Hidden;
first_W_label.Visibility = Visibility.Hidden;
address_W_label.Visibility = Visibility.Hidden;
last_W_label.Visibility = Visibility.Hidden;
cell_W_label.Visibility = Visibility.Hidden;
empnum_W_label.Visibility = Visibility.Hidden;
// if (id_textBox.Text != null)
if (id_textBox != null && !string.IsNullOrWhiteSpace(id_textBox.Text))
{
try
{
int idnumbersCheck = Convert.ToInt32(id_textBox.Text);
}
catch
{
id_W_label.Content = "ת.ז. חייבת להכיל מספרים בלבד!";
id_W_label.Visibility = Visibility.Visible;
return;
}
try
{ //to see if the empid already in the system.
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT COUNT(empid) FROM employees WHERE empid='" + id_textBox.Text + "' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
int empidtimes = Convert.ToInt32(MSQLcrcommand1.ExecuteScalar());
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlConn.Close();
if (empidtimes != 0)
{
MessageBox.Show("כבר קיים עובד בעל תעודת זהות זו - " + id_textBox.Text , "!שים לב" ,MessageBoxButton.OK ,MessageBoxImage.Error);
id_W_label.Content = "ת.ז. זו כבר קיימת";
id_W_label.Visibility = Visibility.Visible;
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
empid = id_textBox.Text;
f1 = true;
}
else
{
id_W_label.Content = "אנא הכנס תעודת זהות";
id_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס תעודת זהות");
}
if (firstname_textBox != null && !string.IsNullOrWhiteSpace(firstname_textBox.Text))
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
firstname = CSQ.checkForSingleQuotationMark(firstname_textBox.Text);
// firstname = firstname_textBox.Text;
f2 = true;
}
else
{
first_W_label.Visibility = Visibility.Visible;
// MessageBox.Show("אנא הכנס שם פרטי ");
}
if (lastname_textBox != null && !string.IsNullOrWhiteSpace(lastname_textBox.Text))
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
lastname = CSQ.checkForSingleQuotationMark(lastname_textBox.Text);
// lastname = lastname_textBox.Text;
f3 = true;
}
else
{
last_W_label.Visibility = Visibility.Visible;
//MessageBox.Show("אנא הכנס שם משפחה ");
}
if (emp_num_textBox != null && !string.IsNullOrWhiteSpace(emp_num_textBox.Text))
{
try
{
int idnumbersCheck = Convert.ToInt32(emp_num_textBox.Text);
}
catch
{
empnum_W_label.Content = "מספר עובד חייב להכיל מספרים בלבד!";
empnum_W_label.Visibility = Visibility.Visible;
return;
}
try
{ //to see if the emp_insidenum already in the system.
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT COUNT(emp_insidenum) FROM employees WHERE emp_insidenum='" + emp_num_textBox.Text + "' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
int times = Convert.ToInt32(MSQLcrcommand1.ExecuteScalar());
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlConn.Close();
if (times != 0)
{
MessageBox.Show("כבר קיים מספר עובד - " + emp_num_textBox.Text, "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
empnum_W_label.Content = "מספר עובד זה כבר קיים";
empnum_W_label.Visibility = Visibility.Visible;
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
emp_num = emp_num_textBox.Text;
f4 = true;
}
else
{
empnum_W_label.Content = "אנא הכנס מספר עובד";
empnum_W_label.Visibility = Visibility.Visible;
// MessageBox.Show("אנא הכנס מספר עובד");
}
if (phone_textBox1 != null && !string.IsNullOrWhiteSpace(phone_textBox1.Text))
{
try
{
int phoneCheck = Convert.ToInt32(phone_textBox1.Text);
}
catch
{
phone_W_label.Visibility = Visibility.Visible;
// MessageBox.Show("!מספר הטלפון חייב להכיל מספרים בלבד");
return;
}
phone = phone_textBox1.Text;
}
if (startdatePicker != null && !string.IsNullOrWhiteSpace(startdatePicker.Text))
{
try
{
start = Convert.ToDateTime(startdatePicker.Text).ToString("yyyy-MM-dd");
f6 = true;
}
catch { MessageBox.Show("תאריך התחלה אינו תקין", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error); return; }
}
if (cell_textBox != null && !string.IsNullOrWhiteSpace(cell_textBox.Text))
{
try
{
int cellphoneCheck = Convert.ToInt32(cell_textBox.Text);
}
catch
{
cell_W_label.Visibility = Visibility.Visible;
// MessageBox.Show("!מספר הנייד חייב להכיל מספרים בלבד");
return;
}
cellphone = cell_textBox.Text;
}
if (address_textBox1 != null && !string.IsNullOrWhiteSpace(address_textBox1.Text))
{
address = address_textBox1.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
address = CSQ.checkForSingleQuotationMark(address);
f5 = true;
}
else
{
address_W_label.Visibility = Visibility.Visible;
// MessageBox.Show("אנא הכנס כתובת");
}
// if all is ok then add new user to the DB.
if (f1 && f2 && f3 && f4 && f5 && f6)
{
try
{
string query = ("insert into project.employees (empid, emp_firstname, emp_lastname, emp_address , emp_phone,emp_cellphone,emp_start_date,emp_insidenum) values ('" + empid + "','" + firstname + "','" + lastname + "','" + address + "','" + phone + "','" + cellphone + "','" + start + "','" + emp_num + "')");
DBConnection DBC = new DBConnection();
DBC.InsertDataIntoDB(Login.Connectionstring, query);
//MessageBox.Show("העובד התווסף למערכת");
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT empid as `תעודת זהות`,emp_firstname as `שם פרטי` ,emp_lastname as `שם משפחה` , emp_insidenum as `מספר עובד` ,emp_address as `כתובת` ,emp_phone as `מספר טלפון`, emp_cellphone as `טלפון נייד`, emp_start_date as `תאריך התחלת עבודה` from project.employees ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
// DataTable dt = new DataTable("employess");
ManagerEMPGui.dt.Clear();
mysqlDAdp.Fill(ManagerEMPGui.dt);
// ManagerEMPGui.dataGrid1.ItemsSource = ManagerEMPGui.dt.DefaultView;
mysqlDAdp.Update(ManagerEMPGui.dt);
MySqlConn.Close();
id_textBox.Clear();
firstname_textBox.Clear();
lastname_textBox.Clear();
emp_num_textBox.Clear();
phone_textBox1.Clear();
startdatePicker.SelectedDate = null;
cell_textBox.Clear();
address_textBox1.Clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if (f1 && f2 && f3 && f4 && f5 && !f6)
{
try
{
string query = ("insert into project.employees (empid, emp_firstname, emp_lastname, emp_address , emp_phone,emp_cellphone,emp_insidenum) values ('" + empid + "','" + firstname + "','" + lastname + "','" + address + "','" + phone + "','" + cellphone + "','" + emp_num + "')");
DBConnection DBC = new DBConnection();
DBC.InsertDataIntoDB(Login.Connectionstring, query);
//MessageBox.Show("העובד התווסף למערכת");
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT empid as `תעודת זהות`,emp_firstname as `שם פרטי` ,emp_lastname as `שם משפחה` , emp_insidenum as `מספר עובד` ,emp_address as `כתובת` ,emp_phone as `מספר טלפון`, emp_cellphone as `טלפון נייד`, emp_start_date as `תאריך התחלת עבודה` FROM project.employees ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
SecEMPGui.dt.Clear();
mysqlDAdp.Fill(SecEMPGui.dt);
mysqlDAdp.Update(SecEMPGui.dt);
MySqlConn.Close();
id_textBox.Clear();
firstname_textBox.Clear();
lastname_textBox.Clear();
emp_num_textBox.Clear();
phone_textBox1.Clear();
startdatePicker.SelectedDate = null;
cell_textBox.Clear();
address_textBox1.Clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// Handles the TextChanged event of the Name_Search_TextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void Name_Search_TextBox_TextChanged(object sender, TextChangedEventArgs e)
{
// if both of the datepickers are with dates
if (!string.IsNullOrWhiteSpace(Start_datePicker.Text) || !string.IsNullOrWhiteSpace(End_datePicker.Text))
{
Fillterdates();
}
if (mistake == false)
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string searchidkey = CSQ.checkForSingleQuotationMark(this.Name_Search_TextBox.Text);
//string searchidkey = this.Name_Search_TextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%'", searchidkey);
}
// if not both of the datepicker are filled
/* if (string.IsNullOrWhiteSpace(Start_datePicker.Text) || string.IsNullOrWhiteSpace(End_datePicker.Text))
{
string searchidkey = this.Name_Search_TextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%'", searchidkey);
}
// if both of the datepickers are with dates
if (string.IsNullOrWhiteSpace(Start_datePicker.Text) || string.IsNullOrWhiteSpace(End_datePicker.Text))
{
string searchidkey = this.Name_Search_TextBox.Text;
string startdate = this.Start_datePicker.Text;
string enddate = this.End_datePicker.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%' AND `תאריך התחלה` LIKE '#{0}#' AND `תאריך סיום משוער` LIKE '#{0}#'", searchidkey, startdate, enddate);
}
*/
/* try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchidkey = this.Name_Search_TextBox.Text;
string Query1 = ("SELECT jobid as `מספר עבודה`, reg_date as `תאריך רישום`,orderid as`מספר הזמנה` ,jobdescription as `תאור עבודה` ,jobs.costumerid as `חפ לקוח` ,costumers.costumerName as `שם לקוח` ,contact_id as `מספר איש קשר` , costumers.contactName as `שם איש קשר` ,job_status as `סטטוס עבודה`,startDate as `תאריך התחלה`,expectedFinishDate as `תאריך סיום משוער` ,actualFinishDate as `תאריך סיום בפועל` ,deliveryid as `תעודת משלוח` ,invoiceNumber as `מספר חשבונית` from project.jobs where costumerid Like '%" + searchidkey + "%' group by jobid");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
//DataTable dt = new DataTable("jobs");
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
*/
}
/// <summary>
/// Handles the TextChanged event of the Item_Search_textBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void Item_Search_textBox_TextChanged(object sender, TextChangedEventArgs e)
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
String searchkey = CSQ.checkForSingleQuotationMark(this.Item_Search_textBox.Text);
//String searchkey = this.Item_Search_textBox.Text;
dt1.DefaultView.RowFilter = string.Format("`מקט פריט` LIKE '%{0}%'", searchkey);
}
//This function will filter the startDate by date selected from 2 DatePickers.
// if the dates were not right a message will be shown.
/// <summary>
/// Handles the Click event of the Filter_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Filter_button_Click(object sender, RoutedEventArgs e)
{
Fillterdates();
if (!string.IsNullOrWhiteSpace(Name_Search_TextBox.Text))
{
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string searchidkey = CSQ.checkForSingleQuotationMark(this.Name_Search_TextBox.Text);
// string searchidkey = this.Name_Search_TextBox.Text;
dt.DefaultView.RowFilter = string.Format("`שם לקוח` LIKE '%{0}%'", searchidkey);
}
}
/// <summary>
/// Handles the TextChanged event of the IDSearchTextBox control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="TextChangedEventArgs"/> instance containing the event data.</param>
private void IDSearchTextBox_TextChanged(object sender, TextChangedEventArgs e)
{
try
{
// string Connectionstring = " Server=localhost;Database=project; UId=root;Password=1234;";
// MySqlConnection MySqlConn = new MySqlConnection(Connectionstring);
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
String searchidkey = this.IDSearchTextBox.Text;
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
searchidkey = CSQ.checkForSingleQuotationMark(searchidkey);
string Query1 = ("select contactid as `מספר איש קשר`,contactName as `שם איש קשר` ,contactEmail as `אימייל איש קשר` ,contactPhone as `טלפון איש קשר`,contactCellPhone as `טלפון נייד של איש הקשר` ,contactDepartment as `מחלקת איש קשר`, contactDesc as `הערות לגבי איש הקשר` from costumers where contactid Like '%" + searchidkey + "%' and costumerid='" + hpcostid + "'");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
// DataTable dt = new DataTable("contacts");
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Handles the Click event of the Add_button control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RoutedEventArgs"/> instance containing the event data.</param>
private void Add_button_Click(object sender, RoutedEventArgs e)
{
string stagename = "";
try
{
stagename = stages_comboBox.SelectedValue.ToString();
}
catch
{
MessageBox.Show("!לא נבחר שלב", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// string itemid;
if (count == 1) // if this is the first stage.
{
string itemid;
if (!string.IsNullOrWhiteSpace(item_id_textBox.Text))
{
itemid = item_id_textBox.Text;
}
else
{
MessageBox.Show("!לא הוכנס מקט פריט", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
// MessageBox.Show("לא הוכנס מק``ט פריט");
return;
}
if (string.IsNullOrWhiteSpace(itemname_textBox.Text))
{
MessageBox.Show("!לא הוכנס שם פריט", "!שים לב", MessageBoxButton.OK, MessageBoxImage.Error);
//MessageBox.Show("לא הוכנס שם פריט");
return;
}
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string itemid1 = CSQ.checkForSingleQuotationMark(itemid);
string Query1 = ("SELECT COUNT(itemid) FROM project.item WHERE itemid='" + itemid1 + "' "); //to see if the itemid already in the system.
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
int times = Convert.ToInt32(MSQLcrcommand1.ExecuteScalar());
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlConn.Close();
if (times != 0)
{
MessageBox.Show("כבר קיים פריט בעל מקט - " + itemid, "!שים לב", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
} // end if (count == 1) - if this is the first stage.
try // if all is OK then create the stage.
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
CheckSingleQuotationMark CSQ = new CheckSingleQuotationMark();
string Query1 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "', 'בעבודה' ,'" + count + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stages_comboBox.SelectedItem.ToString() + "','" + CSQ.checkForSingleQuotationMark(stage_desc_textBox.Text) + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
Console.WriteLine(Query1);
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
if (count == 1)
{
// creating the default stages.
string zero="0" , one = "1", two = "2", itemStatus = "רישום", stageName = "שרטוט", stage_disc = "שרטוט הפריט";
string Query2 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + one + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand2 = new MySqlCommand(Query2, MySqlConn);
MSQLcrcommand2.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp2 = new MySqlDataAdapter(MSQLcrcommand2);
stageName = "רכישת חומר גלם";
stage_disc = "רכישת חומר הגלם לעבודה";
string Query3 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + two + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand3 = new MySqlCommand(Query3, MySqlConn);
MSQLcrcommand3.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp3 = new MySqlDataAdapter(MSQLcrcommand3);
itemStatus="תיקון";
stageName = "תיקון הפריט";
stage_disc = "תיקון לפי מהות התקלה והחלטת הבוחן במקום";
string Query4 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + one + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand4 = new MySqlCommand(Query4, MySqlConn);
MSQLcrcommand4.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp4 = new MySqlDataAdapter(MSQLcrcommand4);
itemStatus="פסול";
stageName = "פסילה";
stage_disc = "נמצא כלא ניתן לתיקון ולכן נפסל";
string Query5 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + one + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand5 = new MySqlCommand(Query5, MySqlConn);
MSQLcrcommand5.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp5 = new MySqlDataAdapter(MSQLcrcommand5);
itemStatus="גמר ייצור";
stageName = "ביקורת לקוח";
stage_disc = "מבוקר על ידי הלקוח";
string Query6 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + zero + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand6 = new MySqlCommand(Query6, MySqlConn);
MSQLcrcommand6.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp6 = new MySqlDataAdapter(MSQLcrcommand6);
stageName = "אריזה";
stage_disc = "נארז למשלוח";
string Query7 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + one + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand7 = new MySqlCommand(Query7, MySqlConn);
MSQLcrcommand7.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp7 = new MySqlDataAdapter(MSQLcrcommand7);
itemStatus="הסתיים";
stageName = "המתנה למשלוח";
stage_disc = "ממתין לשילוח";
string Query8 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + one + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand8 = new MySqlCommand(Query8, MySqlConn);
MSQLcrcommand8.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp8 = new MySqlDataAdapter(MSQLcrcommand8);
stageName = "נשלח";
stage_disc = "נשלח ללקוח";
string Query9 = ("INSERT INTO project.item (itemid, itemStatus,itemStageOrder,itemName,stageName, stage_discription, item_discription ) VALUES ('" + CSQ.checkForSingleQuotationMark(item_id_textBox.Text) + "','" + itemStatus + "','" + two + "','" + CSQ.checkForSingleQuotationMark(itemname_textBox.Text) + "','" + stageName + "','" + stage_disc + "','" + CSQ.checkForSingleQuotationMark(item_disc_textBox.Text) + "') ");
MySqlCommand MSQLcrcommand9 = new MySqlCommand(Query9, MySqlConn);
MSQLcrcommand9.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp9 = new MySqlDataAdapter(MSQLcrcommand9);
}
MySqlConn.Close();
count++;
item_stage_num.Content = count;
item_num2_label.Content = item_id_textBox.Text;
item_id_textBox.Visibility = Visibility.Hidden;
item_num2_label.Visibility = Visibility.Visible;
item_name2_label.Content = itemname_textBox.Text;
itemname_textBox.Visibility = Visibility.Hidden;
item_name2_label.Visibility = Visibility.Visible;
item_disc_textBlock.Text = item_disc_textBox.Text;
item_disc_textBlock.Visibility = Visibility.Visible;
item_disc_textBox.Visibility = Visibility.Hidden;
label1.Visibility = Visibility.Hidden;
stage_desc_textBox.Clear();
dataGrid1.Visibility = Visibility.Visible;
updategrid();
MessageBox.Show("השלב התווסף", "!הצלחה", MessageBoxButton.OK, MessageBoxImage.Information);
//MessageBox.Show("שלב התווסף");
updateparentgrid();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
