void Like() // функция поиска по таблице
{
lombard lb = new lombard();
SqlCommand cmd = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
switch (tabDate.SelectedIndex)
{
//case 0: cmd.CommandText = "select * from contract where clients.name like('" + search_namesl_contract.Text + "%') AND price_out like('%" + search_priceOut_contract.Text + "%') AND date_contract like('%" + search_datein_contract.Text + "%') AND sellers.name like('%" + search_namesl_contract.Text + "%')"; break;
case 0: cmd.CommandText = "select * from contract where price_out like('%" + search_priceOut_contract.Text + "%')AND client_id IN(select id_client from clients where name like('" + search_namecl_contract.Text + "%')) AND date_contract like('%" + search_datein_contract.Text + "%') AND seller_id IN (select id_seller from sellers where name like('%" + search_namesl_contract.Text + "%'))"; break;
case 1: search_items.Visible = true; break;
case 2: cmd.CommandText = "select * from clients where name like('" + search_name_cl.Text + "%') AND adress like('%" + search_adress_cl.Text + "%')"; break;
case 3: search_sale.Visible = true; break;
case 4: cmd.CommandText = "select * from sellers where name like('" + search_name_sellers.Text + "%') AND post like('%" + search_post_sellers.Text + "%') AND lvl like('%" + search_lvl_sellers.Text + "%')"; break;
}
cmd.ExecuteNonQuery(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
switch (tabDate.SelectedIndex)
{
case 0: contract_datagrid.DataSource = dt; break;
case 1: items_datagrid.DataSource = dt; break;
case 2: clients_datagrid.DataSource = dt; break;
case 3: sale_datagrid.DataSource = dt; break;
case 4: sellers_datagrid.DataSource = dt; break;
}
}
private void create_item_Click(object sender, EventArgs e)
{
lombard lb = new lombard(); String id_contract = "", category_id = "";
SqlCommand cmd = lb.con.CreateCommand();// SqlCommand cmd2 = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select top 1 * from contract ORDER BY id_contract DESC";
SqlDataReader dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
id_contract = dr1[0].ToString();
}
dr1.Close();
cmd.CommandText = "select id_category from category where category ='" + category_item_reg.Text + "'";
dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
category_id = dr1[0].ToString();
}
dr1.Close();
cmd.CommandText = "INSERT INTO items values('" + category_id + "','" + name_item_reg.Text + "','"
+ count_item_reg.Text + "','" + description_item_reg.Text + "','" + price_item_reg.Text + "','" + id_contract + "','" + status_id + "')";
MessageBox.Show(cmd.CommandText);
cmd.ExecuteNonQuery(); Main Mn = new Main(); Mn.lombardDataSet.AcceptChanges(); lb.con.Close(); this.Close();
}
private void s_i_in_wiz_Click(object sender, EventArgs e)
{
string row = items_datagrid[0, items_datagrid.CurrentCell.RowIndex].Value.ToString();
lombard lb = new lombard(); SqlCommand cmd = lb.con.CreateCommand(); cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO sale values('" + row + "','" + price_sell_item_wiz.Text + "','" + current_user + "','" + DateTime.Now + "')";
cmd.ExecuteNonQuery(); lombardDataSet.AcceptChanges();
cmd.CommandText = "UPDATE items SET status_id='4' WHERE id_item='" + row + "'"; MessageBox.Show(cmd.CommandText);
cmd.ExecuteNonQuery(); lombardDataSet.AcceptChanges();
}
private void sale_datagrid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
lombard lb = new lombard();
SqlCommand cmd = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
if (!sale_datagrid.ReadOnly)
{
Change(sale_datagrid, cmd);
}
}
private void create_client_reg_Click(object sender, EventArgs e)
{
lombard lb = new lombard();
SqlCommand cmd = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO clients values('" + name_cl_reg.Text + "','" + db_client_reg.Value + "','"
+ adres_client_reg.Text + "','" + passport_client_reg.Text + "','" + phone_client_reg.Text + "')"; MessageBox.Show(cmd.CommandText); cmd.ExecuteNonQuery(); Main Mn = new Main(); Mn.lombardDataSet.AcceptChanges(); DataSet dt = Mn.lombardDataSet;
Mn.contract_datagrid.DataSource = dt; Mn.items_datagrid.DataSource = dt;
Mn.clients_datagrid.DataSource = dt; Mn.sale_datagrid.DataSource = dt; Mn.sellers_datagrid.DataSource = dt;
lb.con.Close(); create_client_panel.Visible = false;
}
int DateDif() // сравнение дат
{
int ret = 0;
lombard lb = new lombard();
string query = "SELECT DATEDIFF(dd,(SELECT date_contract FROM contract),(SELECT date_out FROM contract))";
SqlCommand cmd = new SqlCommand(query, lb.con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ret = Convert.ToInt32(dr[0]);
}
return(ret);
}
private void button1_Click(object sender, EventArgs e)
{
lombard lb = new lombard();
string query = "SELECT * FROM sellers WHERE username =@user AND password =@pass";
SqlCommand cmd = new SqlCommand(query, lb.con);
cmd.Parameters.Add(new SqlParameter("@user", username.Text));
cmd.Parameters.Add(new SqlParameter("@pass", password.Text));
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows == true)
{
dr.Close();
this.Visible = false;
query = "SELECT NAME FROM sellers WHERE username = @user AND password = @pass";
SqlCommand cmd1 = new SqlCommand(query, lb.con);
cmd1.Parameters.Add(new SqlParameter("@user", username.Text));
cmd1.Parameters.Add(new SqlParameter("@pass", password.Text));
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
query = dr1[0].ToString();
}
dr1.Close(); lg.cur_user.Text = lg.cur_user.Text + query;
query = "SELECT NAME FROM sellers WHERE username = @user AND password = @pass";
cmd1 = new SqlCommand("SELECT id_seller FROM sellers WHERE username = @user AND password = @pass", lb.con);
cmd1.Parameters.Add(new SqlParameter("@user", username.Text));
cmd1.Parameters.Add(new SqlParameter("@pass", password.Text));
dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
lg.current_user = dr1[0].ToString();
}
dr.Close(); lg.Show();
}
else
{
MessageBox.Show("Введенные логин/пароль неверны!");
}
}
private void create_contract_Click(object sender, EventArgs e)
{
String client_id = "", seller_id = "";
lombard lb = new lombard();
SqlCommand cmd = lb.con.CreateCommand();// SqlCommand cmd2 = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select id_client from clients where name ='" + client_contr.Text + "'";
SqlDataReader dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
client_id = dr1[0].ToString();
}
dr1.Close();
cmd.CommandText = "select id_seller from sellers where name ='" + seller_contract.Text + "'";
dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
seller_id = dr1[0].ToString();
}
dr1.Close();
if (sell_or_not.SelectedIndex == 0)
{
status_id = "1";
cmd.CommandText = "INSERT INTO contract values('" + client_id + "','" + date_in_conrt.Value + "','"
+ date_out_contr.Value + "','" + price_out_contr.Text + "','" + seller_id + "')";
}
if (sell_or_not.SelectedIndex == 1)
{
cmd.CommandText = "INSERT INTO contract(client_id,date_contract,seller_id) values('" + client_id + "','" + date_in_conrt.Value + "','" + seller_id + "')"; status_id = "3";
}
MessageBox.Show(cmd.CommandText);
cmd.ExecuteNonQuery(); Main Mn = new Main(); Mn.lombardDataSet.AcceptChanges(); lb.con.Close();
item_timer.Enabled = true; item_create_panel.Visible = true;
}
private void edit_button_MouseUp(object sender, MouseEventArgs e)
{
/*if( edit_button.BackgroundImage == BD.Properties.Resources.change){
* edit_button.BackgroundImage = BD.Properties.Resources.change_hover;}
* else
* edit_button.BackgroundImage = BD.Properties.Resources.change;*/
DataGridView DG = null;
switch (tabDate.SelectedIndex)
{
case 0:
DG = contract_datagrid; break;
case 1:
DG = items_datagrid; break;
case 2:
DG = clients_datagrid; break;
case 3:
DG = sale_datagrid; break;
case 4:
DG = sellers_datagrid; break;
}
lombard lb = new lombard();
SqlCommand cmd = lb.con.CreateCommand();
cmd.CommandType = CommandType.Text;
if (DG.ReadOnly == true)
{
lombardDataSet.AcceptChanges();
edit_button.BackgroundImage = BD.Properties.Resources.change_hover;
DG.AllowUserToDeleteRows = true;
DG.ReadOnly = false; DG.BorderStyle = BorderStyle.FixedSingle;
DG.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
}
else
{
// cmd.CommandText = "";
edit_button.BackgroundImage = BD.Properties.Resources.change;
DG.AllowUserToDeleteRows = false;
DG.ReadOnly = true; DG.BorderStyle = BorderStyle.None;
DG.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
///////
/* string value = DG.CurrentCell.Value.ToString(); //значение
* string cell = DG.Columns[contract_datagrid.CurrentCell.ColumnIndex].DataPropertyName; //имя ячейки
* string row = DG[contract_datagrid.CurrentCell.RowIndex, 0].Value.ToString(); // айди строки
* //cmd.CommandText = "UPDATE contract SET " + cell + "=" + value + " WHERE " + row + "";
* //contract_datagrid[contract_datagrid.CurrentCell.RowIndex, 0].State = DataGridViewElementStates.Frozen;
* switch (tabDate.SelectedIndex)
* {
* case 0: cmd.CommandText = "UPDATE contract SET " + cell + "='" + value + "' WHERE id_contract='" + row + "'";
* cmd.ExecuteNonQuery(); lombardDataSet.AcceptChanges();break;
*
* case 1:
* this.clientsTableAdapter.Update(this.lombardDataSet); break;
* case 4: cmd.CommandText = "UPDATE sellers SET " + cell + "='" + value + "' WHERE id_seller='" + row + "'";
* MessageBox.Show(cmd.CommandText);
* cmd.ExecuteNonQuery(); lombardDataSet.AcceptChanges(); break;
*
* } */
}
}