public static bool Execute(string s)
{
s = s.Replace("'True'", "1");
s = s.Replace("'False'", "0");
SQL_Class sql_c = new SQL_Class();
sql_c.Create_Connection(Properties.Settings.Default.BasePath);
sql_c.Open_Connection();
var temp = new MySqlCommand(s, SQL_Connection);
try
{
temp.ExecuteNonQuery();
}
catch (Exception ex)
{
sql_c.Manualy_Close_Connection();
return(false);
}
sql_c.Manualy_Close_Connection();
return(true);
}
public static void add_upload_log(int itemId, string param, string from, string to)
{
string zap = "insert into logs (ItemID,Log,Date,Who) values (" + itemId + ",'Изменение параметра " + param +
" c " + from + " на " + to + "',NOW(),'" + Environment.UserName + "')";
SQL_Class.Execute(zap);
}
public GroupChangeForm(Element inp)
{
InitializeComponent();
el = inp;
cmb.DataSource = SQL_Class.get_data_Source("select " + el.name + " from " + el.addtable);
lb.Text = el.name;
}
public static List <string> check(XLS_Class xls, int start, int stop, int col, string table, string name, bool xls_marker)
{
List <string> rett = new List <string>();
string element;
if (xls_marker)
{
if (name != "Owner")
{
element = "xls" + name;
}
else
{
element = name;
}
}
else
{
element = name;
}
for (int i = start; i < stop; i++)
{
if (
SQL_Class.ReadValueInt32("select count(*) from " + table + " where " + element + " like '%" +
xls.read_val(i, col).ToString() + "%'") <= 0)
{
if (!rett.Contains(xls.read_val(i, col).ToString()))
{
rett.Add(xls.read_val(i, col).ToString());
}
}
}
return(rett);
}
private void dgv_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.RowIndex == -1 && e.Button == MouseButtons.Middle)
{
string name = dgv.Columns[e.ColumnIndex].Name;
for (int i = 0; i < tbl.el_list.Count; i++)
{
if (name == tbl.el_list[i].name && tbl.el_list[i].cmbox)
{
Element el = tbl.el_list[i];
GroupChangeForm frm = new GroupChangeForm(el);
frm.ShowDialog();
Application.DoEvents();
if (frm.Acept)
{
int new_index = frm.id;
List <int> ids = new List <int>();
for (int j = 0; j < dgv.RowCount; j++)
{
ids.Add((int)dgv.Rows[j].Cells[0].Value);
}
foreach (var VARIABLE in ids)
{
string req = "update itemtable set " + el.name + " = " + new_index + " where ID=" +
VARIABLE;
SQL_Class.Execute(req);
Logs.add_upload_log(VARIABLE, el.name, el.addtable, SQL_Class.ReadValueInt32("select " + el.name + " from itemtable where ID=" + VARIABLE), new_index);
}
Load_Data_With_Filters();
}
}
}
}
}
public List <string> check(XLS_Class xls, int start, int stop, int col, string table, string name)
{
List <string> rett = new List <string>();
string element;
if (XLS_Rbut.Checked)
{
element = "xls" + name;
}
else
{
element = name;
}
for (int i = start; i < stop; i++)
{
if (
SQL_Class.ReadValueInt32("select count(*) from " + table + " where " + element + " like '%" +
xls.read_val(i, col).ToString() + "%'") <= 0)
{
if (xls.read_val(i, col).ToString() == "СКЛАД")
{
MessageBox.Show(i.ToString());
}
if (!rett.Contains(xls.read_val(i, col).ToString()))
{
rett.Add(xls.read_val(i, col).ToString());
}
}
}
return(rett);
}
public void left(ComboBox cmb1, TextBox tb2, string par, string table)
{
string base_par = SQL_Class.ReadValueString("select " + par + " from " + table + " where xls" + par + " like '%" + tb2.Text.ToString() +
"%'");
cmb1.SelectedItem = base_par;
}
public static bool add_Element(Compare_Element el)
{
string zap = "insert into itemtable (itemtable.Serial,itemtable.Serial2," +
"itemtable.Mark,itemtable.Type,itemtable.Status,itemtable.Place," +
"itemtable.Text,itemtable.Text2,itemtable.Text3,itemtable.Text4,itemtable.Text5,itemtable.Text6 " +
",itemtable.Place2, itemtable.Prise, itemtable.OS, itemtable.Date,itemtable.Owner,itemtable.Status2)";
zap += " values(" +
"'" + el.Serial + "'," +
"'" + el.Serial2 + "'," +
el.Markid + "," +
el.Typeid + "," +
el.Statusid + "," +
el.Placeid + "," +
"'" + el.Text + "'," +
"'" + el.Text2 + "'," +
"'" + el.Text3 + "'," +
"'" + el.Text4 + "'," +
"'" + el.Text5 + "'," +
"'" + el.Text6 + "'," +
el.Place2id + "," +
"'" + el.Prise + "'," +
el.OSID + "," +
"'" + el.date + "'," + el.OwnerID + "," + el.Status2id + ")";
try
{
return(SQL_Class.Execute(zap));
}
catch (Exception)
{
return(false);
}
}
public void Update_One_Item(string name, string table, string fr, string addition)
{
int id = SQL_Class.ReadValueInt32("select ID from " + table + " where " + name + "='" + fr + "'");
string old = SQL_Class.ReadValueString("select xls" + name + " from " + table + " where ID=" + id);
old += "|" + addition + "|";
SQL_Class.Execute("Update " + table + " set xls" + name + "='" + old + "' where ID=" + id);
}
private void Acept_but_Click(object sender, EventArgs e)
{
Acept = true;
id =
SQL_Class.ReadValueInt32("select ID from " + el.addtable + " where " + el.name + " ='" +
cmb.SelectedItem.ToString() + "'");
Close();
}
public static void add_upload_log(int itemId, string param, string table_name, int from, string to)
{
string fr = SQL_Class.ReadValueString("select " + param + " from " + table_name + " where ID=" + from);
string zap = "insert into logs (ItemID,Log,Date,Who) values (" + itemId + ",'Изменение параметра " + param +
" c " + fr + " на " + to + "',NOW(),'" + Environment.UserName + "')";
SQL_Class.Execute(zap);
}
private void Owner_CmBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (Owner_CmBox.DataSource != null)
{
if (SQL_Class.get_data_Source("select Owner from ownertable").Contains(Owner_CmBox.SelectedItem.ToString()))
{
mas = Compare_Pare_Mas.get_base_elements(SQL_Class.ReadValueInt32("select ID from ownertable where Owner='" + Owner_CmBox.SelectedItem.ToString() + "'"));
}
}
}
private void OS_CmBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (OS_CmBox.DataSource != null)
{
if (SQL_Class.get_data_Source("select OS from ostable").Contains(OS_CmBox.SelectedItem.ToString()))
{
request_mas_of_base_elements();
}
}
}
public static void open_document(int id)
{
try
{
Process.Start(SQL_Class.ReadValueString("select Path from documents where ID=" + id));
}
catch (Exception)
{
}
}
public static Compare_Pare_Mas get_base_elements(int ownerID)//--
{
Compare_Pare_Mas rett = new Compare_Pare_Mas();
string zap = "Select itemtable.ID,itemtable.Serial,itemtable.Serial2," +
"itemtable.Mark,itemtable.Type,itemtable.Status,itemtable.Place," +
"itemtable.Text,itemtable.Text2,itemtable.Text3,itemtable.Text4,itemtable.Text5,itemtable.Text6 " +
",itemtable.Place2, itemtable.Prise, itemtable.OS, itemtable.Date from itemtable where itemtable.Owner=" + ownerID;
SQL_Class cl = new SQL_Class();
cl.ReadValues(zap);
while (cl.SQL_DataReader.Read())
{
Compare_Element el = new Compare_Element();
el.ID = cl.get_int(0);
el.Serial = cl.get_string(1);
el.Serial2 = cl.get_string(2);
el.Markid = cl.get_int(3);
el.Typeid = cl.get_int(4);
el.Statusid = cl.get_int(5);
el.Placeid = cl.get_int(6);
el.Place2id = cl.get_int(13);
el.Prise = cl.get_string(14);
el.OSID = cl.get_int(15);
el.date = cl.get_string(16);
el.Text = cl.get_string(7);
el.Text2 = cl.get_string(8);
el.Text3 = cl.get_string(9);
el.Text4 = cl.get_string(10);
el.Text5 = cl.get_string(11);
el.Text6 = cl.get_string(12);
el.read_other_names_base();
Compare_Pare pare = new Compare_Pare();
pare.base_el = el;
rett.pre_mas.Add(pare);
rett.Serial2_base.Add(el.Serial2);
}
cl.Manualy_Close_Connection2();
for (int i = 0; i < rett.pre_mas.Count; i++)
{
rett.pre_mas[i].base_el.read_other_names_base();
}
return(rett);
}
public void prepare_CmBox()
{
mark1.DataSource = SQL_Class.get_data_Source_with_Null("select Mark from marktable");
type1.DataSource = SQL_Class.get_data_Source_with_Null("select Type from typetable");
place1.DataSource = SQL_Class.get_data_Source_with_Null("select Place from placetable");
place21.DataSource = SQL_Class.get_data_Source_with_Null("select Place2 from place2table");
status1.DataSource = SQL_Class.get_data_Source_with_Null("select Status from statustable");
status21.DataSource = SQL_Class.get_data_Source_with_Null("select Status2 from status2table");
os1.DataSource = SQL_Class.get_data_Source_with_Null("select OS from ostable");
owner1.DataSource = SQL_Class.get_data_Source_with_Null("select Owner from ownertable");
}
public static string get_Data_asString(SQL_Class sqlcl, int index)
{
if (sqlcl.SQL_DataReader.GetValue(index).ToString() == "")
{
return("");
}
else
{
return(sqlcl.SQL_DataReader.GetDateTime(index).ToString("g"));
}
}
public static List <string> get_data_Source(string queue)
{
List <string> lst = new List <string>();
SQL_Class cl = new SQL_Class();
cl.ReadValues(queue);
while (cl.SQL_DataReader.Read())
{
lst.Add(cl.get_string(0));
}
return(lst);
}
} // for number
public void upload(string inter_str, string param, string par_name)
{
if (inter_str != param)
{
if (MessageBox.Show("Сохранить?", "Сохранить?", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
param = inter_str;
SQL_Class.Execute("update itemtable set " + par_name + "='" + param + "' where ID=" + pr.base_el.ID);
reload_base_el();
}
}
} // for string
public static SQL_Class Create_class()
{
SQL_Class sql_class = new SQL_Class();
sql_class.Create_Connection(Properties.Settings.Default.BasePath);
sql_class.Open_Connection();
if (SQL_Connection.State != ConnectionState.Open)
{
return(null);
}
return(sql_class);
}
public static SQL_Class Create_class(string base_path)
{
SQL_Class sql_class = new SQL_Class();
sql_class.Create_Connection(base_path);
sql_class.Open_Connection();
if (SQL_Connection.State != ConnectionState.Open)
{
return(null);
}
return(sql_class);
}
public static object ReadValue(string s, string base_path)
{
SQL_Class sql = new SQL_Class();
sql.Create_Connection(base_path);
sql.Open_Connection();
MySqlCommand comand = new MySqlCommand(s, SQL_Connection);
object obj = comand.ExecuteScalar();
sql.Manualy_Close_Connection();
return(obj);
}
private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show(dgv.RowCount.ToString());
SQL_Class.Execute("truncate " + TableName);
for (int i = 0; i < dgv.RowCount - 1; i++)
{
string str = "insert into " + TableName + " (ID," + FName + ") Values (" +
dgv.Rows[i].Cells[0].Value.ToString() + ",'" + dgv.Rows[i].Cells[1].Value.ToString() + "')";
SQL_Class.Execute(str);
}
load_info();
}
private void Settings_Form_Load(object sender, EventArgs e)
{
SQL_Class cl = new SQL_Class();
MySqlCommand sqlCom = new MySqlCommand("select * from settingstable", SQL_Class.SQL_Connection);
sqlCom.ExecuteNonQuery();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sqlCom);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
dgv.DataSource = dt;
dgv.Refresh();
}
public static bool add_document(int itemID, Image img)
{
try
{
string dir = SQL_Class.ReadValueString("select Serial2 from itemtable where ID=" + itemID);
string dest_dir = Path + "\\" + dir;
if (!Directory.Exists(dest_dir))
{
Directory.CreateDirectory(dest_dir);
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.InitialDirectory = dest_dir;
sfd.AddExtension = true;
sfd.Filter = ".jpeg";
if (sfd.ShowDialog() == DialogResult.OK)
{
string dest_path = sfd.FileName;
if (!File.Exists(dest_path))
{
img.Save(dest_path);
string zap = "insert into documents (ItemID,Name,Path) values (" + itemID + ",'" +
System.IO.Path.GetFileName(sfd.FileName) + "','" + dest_path.Replace("\\", "\\\\") +
"')";
SQL_Class.Execute(zap);
}
else
{
if (MessageBox.Show("Заменить файл?", "Заменить файл?", MessageBoxButtons.YesNo) ==
DialogResult.Yes)
{
File.Delete(dest_path);
img.Save(dest_path);
string zap = "insert into documents (ItemID,Name,Path) values (" + itemID + ",'" +
System.IO.Path.GetFileName(sfd.FileName) + "','" +
dest_path.Replace("\\", "\\\\") +
"')";
SQL_Class.Execute(zap);
}
}
return(true);
}
}
catch
(Exception)
{
return(false);
}
return(false);
}
} // for number
public void upload(DateTime inter_str, DateTime param, string par_name)
{
if (Convert.ToDateTime(inter_str) != param)
{
if (MessageBox.Show("Сохранить?", "Сохранить?", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
param = Convert.ToDateTime(inter_str);
SQL_Class.Execute("update itemtable set " + par_name + "=" + param + " where ID=" + pr.base_el.ID);
reload_base_el();
}
}
} // for number
string select_name_where_id(string name, string tablename, int i)
{
string zap = "Select " + tablename + "." + name + " from " + tablename + " where ID= " + i;
object ob = SQL_Class.ReadValue(zap);
if (ob == null)
{
return("");
}
else
{
return(ob.ToString());
}
}
int select_id_where_xlsname(string name, string tablename, string like)
{
string zap = "Select ID from " + tablename + " where " + tablename + ".xls" + name + " like '%" + like + "%'";
object ob = SQL_Class.ReadValue(zap);
if (ob == null)
{
return(-1);
}
else
{
return(Convert.ToInt32(ob));
}
}
//public void upload(string inter_str, ref string param, string par_name, string table,bool cmb)
//{
// if (inter_str != param)
// {
// if (MessageBox.Show("Сохранить?", "Сохранить?", MessageBoxButtons.YesNo) == DialogResult.Yes)
// {
// param = inter_str;
// if (cmb)
// {
// int id =
// SQL_Class.ReadValueInt32("select ID from " + table + " where " + par_name + "= '" + param +
// "'");
// SQL_Class.Execute("update itemtable set " + par_name + "=" + id + " where ID=" + pr.base_el.ID);
// }
// else
// {
// SQL_Class.Execute("update itemtable set " + par_name + "='" + param + "' where ID=" + pr.base_el.ID);
// }
// reload_base_el();
// }
// }
//}
public void upload(string inter_str, int param, string par_name, string table) // for cmbox
{
int new_param_id =
SQL_Class.ReadValueInt32("select ID from " + table + " where " + par_name + " ='" + inter_str + "'");
if (new_param_id != param)
{
if (MessageBox.Show("Сохранить?", "Сохранить?", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
param = new_param_id;
SQL_Class.Execute("update itemtable set " + par_name + "=" + new_param_id + " where ID=" + pr.base_el.ID);
reload_base_el();
}
}
}
public string get_one_Typethized_part(ComboBox cmb, CheckBox options, string field, string table)
{
string rett = " ";
if (options.Checked)
{
return(" AND itemtable." + field + "=" +
SQL_Class.ReadValueInt32("select ID from " + table + " where " + field + "='" + cmb.SelectedItem +
"'") + " ");
}
else
{
return("");
}
}