// 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 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;
}
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 void frmSaleProducts_Load(object sender, EventArgs e)
{
DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter();
// เติมข้อมูลใส่ combobox Department บันทึกโดยใช้ combobox.SelectedValue
DataTable dtDepartment = dc.ComboboxHelper(cmbDepartment, "departmentName", "---เลือกรายการ---", "SELECT department_id, departmentName FROM department");
cmbDepartment.DataSource = dtDepartment;
cmbDepartment.DisplayMember = "departmentName";
cmbDepartment.ValueMember = "department_id";
FillData();
}
// Fill data into Any Combobox
private void FillCombobox()
{
DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter();
// เติมข้อมูลใส่ combobox budgettype บันทึกโดยใช้ combobox.SelectedValue
DataTable dtBudget = dc.ComboboxHelper(cmbBudgetType, "budgetType", "---เลือกรายการ---", "select budget_id, budgetType from budget");
cmbBudgetType.DataSource = dtBudget;
cmbBudgetType.DisplayMember = "budgetType";
cmbBudgetType.ValueMember = "budget_id";
// เติมข้อมูลใส่ combobox howtobuy บันทึกโดยใช้ combobox.SelectedValue
DataTable dtHTB = dc.ComboboxHelper(cmbHowtobuy, "htbName", "---เลือกรายการ---", "select htb_id, htbName from howtobuy");
cmbHowtobuy.DataSource = dtHTB;
cmbHowtobuy.DisplayMember = "htbName";
cmbHowtobuy.ValueMember = "htb_id";
}
private void FillCombobox()
{
DatabaseLayer.DataCenter dc = new DatabaseLayer.DataCenter();
// เติมข้อมูลใส่ combobox cmbDruggroup บันทึกโดยใช้ combobox.SelectedValue
DataTable dtDruggroup = dc.ComboboxHelper(cmbDruggroup, "druggroupName", "---เลือกรายการ---", "select druggroup_id, druggroupName from druggroup");
cmbDruggroup.DataSource = dtDruggroup;
cmbDruggroup.DisplayMember = "druggroupName";
cmbDruggroup.ValueMember = "druggroup_id";
// เติมข้อมูลใส่ combobox cmbDruggroup บันทึกโดยใช้ combobox.SelectedValue
DataTable dtDrugtype = dc.ComboboxHelper(cmbDrugtype, "typeName", "---เลือกรายการ---", "select type_id, typeName from type");
cmbDrugtype.DataSource = dtDrugtype;
cmbDrugtype.DisplayMember = "typeName";
cmbDrugtype.ValueMember = "type_id";
}
// 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);
}
