// Fill data into DGV by DataTable public async void FillDataToDGV() { DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter(); DataTable dt = await dc.SelectDataAsync(string.Format(@"SELECT dummy.dummy_id, dummy.user_id, dummy.receiveNo, dummy.invoiceNo, dummy.orderNo, dummy.drug_id, drg.drugName, dummy.vendor_id, dummy.lastStock, dummy.qty, dummy.pack, dummy.price, dummy.totalPrice, dummy.totalStock, dummy.lotNo, dummy.htb_id, dummy.budget_id, dummy.receiveDate, dummy.expDate, dummy.orderDate, dummy.approveDate , vd.vendorName FROM receivedata_dummy AS dummy LEFT JOIN drug AS drg ON dummy.drug_id = drg.drug_id LEFT JOIN vendor AS vd ON dummy.vendor_id = vd.vendor_id WHERE (CoSSK = '0' AND CoSubSSK = '0') ORDER BY dummy.receiveNo ASC")); dgvAddproducts.DataSource = dt; dgvAddproducts.Columns[0].Visible = false; dgvAddproducts.Columns[1].Visible = false; dgvAddproducts.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // reveiveNo dgvAddproducts.Columns[3].Visible = false; dgvAddproducts.Columns[4].Visible = false; dgvAddproducts.Columns[5].Visible = false; dgvAddproducts.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; // drugName dgvAddproducts.Columns[7].Visible = false; dgvAddproducts.Columns[8].Visible = false; dgvAddproducts.Columns[9].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // qty dgvAddproducts.Columns[10].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // pack dgvAddproducts.Columns[11].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // price dgvAddproducts.Columns[12].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // totalPrice dgvAddproducts.Columns[13].Visible = false; dgvAddproducts.Columns[14].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; // lotNo dgvAddproducts.Columns[15].Visible = false; dgvAddproducts.Columns[16].Visible = false; dgvAddproducts.Columns[17].Visible = false; dgvAddproducts.Columns[18].Visible = false; dgvAddproducts.Columns[19].Visible = false; dgvAddproducts.Columns[20].Visible = false; dgvAddproducts.Columns[21].Visible = false; }
private async void SearchVendor(string searchValue) { dgvSearchVendor.DataSource = null; string query = string.Empty; if (string.IsNullOrEmpty(searchValue)) { query = string.Format(@"select * from vendor"); } else { query = string.Format(@"select * from vendor where vendorName like '%{0}%' or vendorCode like '%{1}%' ", searchValue.Trim(), searchValue.Trim()); } DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter(); DataTable dt = await dc.SelectDataAsync(query); dgvSearchVendor.DataSource = dt; dgvSearchVendor.Columns[0].Visible = false; dgvSearchVendor.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvSearchVendor.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSearchVendor.Columns[3].Visible = false; dgvSearchVendor.Columns[4].Visible = false; dgvSearchVendor.Columns[5].Visible = false; }
private async void SearchDrugs(string searchValue) { dgvSearchDrug.DataSource = null; string query = string.Empty; if (string.IsNullOrEmpty(searchValue)) { query = string.Format(@"SELECT * FROM drug"); } else { query = string.Format(@"SELECT * FROM drug WHERE drugName LIKE '%{0}%' ", searchValue.Trim()); } DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter(); DataTable dt = await dc.SelectDataAsync(query); dgvSearchDrug.DataSource = dt; dgvSearchDrug.Columns[0].Visible = false; dgvSearchDrug.Columns[1].Visible = false; dgvSearchDrug.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgvSearchDrug.Columns[3].Visible = false; dgvSearchDrug.Columns[4].Visible = false; dgvSearchDrug.Columns[5].Visible = false; dgvSearchDrug.Columns[6].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvSearchDrug.Columns[7].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvSearchDrug.Columns[8].Visible = false; dgvSearchDrug.Columns[9].Visible = false; }
// Fill Data into dgvUncheck by DataTable private async void FillData(string searchValue) { dgvCheck2.DataSource = null; DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter(); DataTable dt = new DataTable(); if (string.IsNullOrEmpty(searchValue)) { dt = await dc.SelectDataAsync(string.Format(@"SELECT dummy.*, bg.budgetType, vd.vendorName FROM receivedata_dummy AS dummy LEFT JOIN vendor AS vd ON dummy.vendor_id = vd.vendor_id LEFT JOIN budget as bg ON dummy.budget_id = bg.budget_id ")); } else { dt = await dc.SelectDataAsync(string.Format(@"SELECT dummy.*, bg.budgetType, vd.vendorName FROM receivedata_dummy AS dummy LEFT JOIN vendor AS vd ON dummy.vendor_id = vd.vendor_id LEFT JOIN budget as bg ON dummy.budget_id = bg.budget_id WHERE dummy.receiveNo = '{0}' ", searchValue)); } // create 8 DGV columns in dgvUncheck before do this coz we have to add 2 buttons to DGV for choose either ซื้อร่วมจังหวัด or ซื้อร่วมเขต //foreach (DataRow data in dt.Rows) //{ // dgvCheck2.Rows.Add(); //} // if (dt.Rows.Count > 0) { // column + 2 coz checkbox CoSSK, CoSubSSK dgvCheck2.DataSource = dt; dgvCheck2.Columns[0].Visible = true; dgvCheck2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[1].Visible = true; dgvCheck2.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[2].Visible = false; dgvCheck2.Columns[3].Visible = false; dgvCheck2.Columns[4].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[5].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[6].Visible = false; dgvCheck2.Columns[7].Visible = false; dgvCheck2.Columns[8].Visible = false; dgvCheck2.Columns[9].Visible = false; dgvCheck2.Columns[10].Visible = false; dgvCheck2.Columns[11].Visible = false; dgvCheck2.Columns[12].Visible = false; dgvCheck2.Columns[13].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[14].Visible = false; dgvCheck2.Columns[15].Visible = false; dgvCheck2.Columns[16].Visible = false; dgvCheck2.Columns[17].Visible = false; dgvCheck2.Columns[18].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[19].Visible = false; dgvCheck2.Columns[20].Visible = false; dgvCheck2.Columns[21].Visible = false; dgvCheck2.Columns[22].Visible = false; dgvCheck2.Columns[23].Visible = false; dgvCheck2.Columns[23].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; dgvCheck2.Columns[23].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; } decimal totalPrice = 0; foreach (DataGridViewRow data in dgvCheck2.Rows) { totalPrice += Convert.ToDecimal(data.Cells[13].Value.ToString()); } lblCount.Text = Convert.ToString(dgvCheck2.Rows.Count); lblTotalPrice.Text = Convert.ToString(totalPrice); }