public void RenderGrid()
{
Connection connect = new Connection();
conn = connect.Connect();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
string sqlSelectAll = "SELECT spares.spares_id,spares.spares_name,verify_item.num,format(verify_item.price,0),format((verify_item.num * verify_item.price),0) as all_price,'ลบ' AS btn_del " +
"from verify_item " +
"INNER JOIN spares on verify_item.spares_id = spares.spares_id " +
"where ver_id='" + this.id + "' ";
// Console.WriteLine(sqlSelectAll);
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, conn);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
///ปรับแต่งข้อความ header ของ gridview
dataGridView1.Columns[0].HeaderText = "ID";
dataGridView1.Columns[0].Width = 200;
dataGridView1.Columns[1].HeaderText = "ชื่ออะไหล่";
dataGridView1.Columns[1].Width = 180;
dataGridView1.Columns[2].HeaderText = "จำนวน";
dataGridView1.Columns[2].Width = 60;
dataGridView1.Columns[3].HeaderText = "ราคาขาย";
dataGridView1.Columns[3].Width = 80;
dataGridView1.Columns[4].HeaderText = "รวม";
dataGridView1.Columns[4].Width = 130;
dataGridView1.Columns[5].HeaderText = "";
dataGridView1.Columns[5].Width = 100;
dataGridView1.Columns[5].DefaultCellStyle.ForeColor = Color.Red;
dataGridView1.Columns[5].DefaultCellStyle.Font = new Font(this.Font, FontStyle.Bold);
string query = "SELECT SUM(vm.price*vm.num) AS price " +
"FROM spares AS sp " +
"INNER JOIN verify_item AS vm ON vm.spares_id = sp.spares_id " +
"WHERE vm.ver_id = @id GROUP BY vm.ver_id ";
MySqlCommand cmdQuery = new MySqlCommand(query, conn);
cmdQuery.Parameters.AddWithValue("@id", this.id);
conn.Open();
MySqlDataReader dr = cmdQuery.ExecuteReader();
double c_cost = 0;
while (dr.Read())
{
c_cost = dr.GetDouble("price");
}
//tb_change.Text = c_cost.ToString();
tb_change.Text = String.Format("{0:#,##0}", c_cost);
hiddenval.Text = c_cost.ToString();
if (c_cost == 0)
{
button1.Enabled = false;
}
else
{
button1.Enabled = true;
}
conn.Close();
}
public void RenderGrid()
{
Connection connect = new Connection();
conn = connect.Connect();
MySqlDataAdapter MyDA = new MySqlDataAdapter();
string where = "WHERE 1";
if (tb_search_cus_name.Text != "")
{
where += " AND fullname LIKE '%" + tb_search_cus_name.Text + "%'";
}
if (tb_search_cus_tel.Text != "")
{
where += " AND cus_tel LIKE '%" + tb_search_cus_tel.Text + "%'";
}
if (tb_search_cus_email.Text != "")
{
where += " AND cus_email LIKE '%" + tb_search_cus_email.Text + "%'";
}
if (tb_search_cus_address.Text != "")
{
where += " AND cus_address LIKE '%" + tb_search_cus_address.Text + "%'";
}
if (tb_search_cus_idcard.Text != "")
{
where += " AND cus_idcard LIKE '%" + tb_search_cus_idcard.Text + "%'";
}
if (tb_search_cus_veh_id.Text != "")
{
where += " AND veh_id LIKE '%" + tb_search_cus_veh_id.Text + "%'";
}
if (tb_search_cus_veh_type.Text != "")
{
where += " AND veh_type LIKE '%" + tb_search_cus_veh_type.Text + "%'";
}
string sqlSelectAll = "SELECT cus_id,fullname,cus_tel,cus_email,cus_idcard,cus_address,veh_id,veh_type,'แก้ไข' AS btn_edit,'ลบ' AS btn_del from customers " + where + " ORDER BY cus_id DESC";
// Console.WriteLine(sqlSelectAll);
MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, conn);
DataTable table = new DataTable();
MyDA.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
///ปรับแต่งข้อความ header ของ gridview
dataGridView1.Columns[0].HeaderText = "ID";
dataGridView1.Columns[0].Width = 50;
dataGridView1.Columns[1].HeaderText = "ชื่อ";
dataGridView1.Columns[1].Width = 150;
dataGridView1.Columns[2].HeaderText = "เบอร์โทรศัพท์";
dataGridView1.Columns[2].Width = 130;
dataGridView1.Columns[3].HeaderText = "อีเมล์";
dataGridView1.Columns[3].Width = 100;
dataGridView1.Columns[4].HeaderText = "เลขประจำตัวบัตรประชาชน";
dataGridView1.Columns[4].Width = 170;
dataGridView1.Columns[5].HeaderText = "ที่อยู่";
dataGridView1.Columns[5].Width = 120;
dataGridView1.Columns[6].HeaderText = "ทะเบียนรถ";
dataGridView1.Columns[6].Width = 100;
dataGridView1.Columns[7].HeaderText = "ยี่ห้อรถ";
dataGridView1.Columns[7].Width = 100;
dataGridView1.Columns[8].HeaderText = "";
dataGridView1.Columns[8].Width = 50;
dataGridView1.Columns[8].DefaultCellStyle.ForeColor = Color.Blue;
dataGridView1.Columns[8].DefaultCellStyle.Font = new Font(this.Font, FontStyle.Bold);
dataGridView1.Columns[9].HeaderText = "";
dataGridView1.Columns[9].Width = 50;
dataGridView1.Columns[9].DefaultCellStyle.ForeColor = Color.Red;
dataGridView1.Columns[9].DefaultCellStyle.Font = new Font(this.Font, FontStyle.Bold);
}
private void Login_Load(object sender, EventArgs e)
{
Connection connect = new Connection();
conn = connect.Connect();
}