private void linkLabelCustomer_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT code,BPName FROM businesspartner WHERE BPType = 1 ORDER BY code DESC, BPName");
if (txtCustomerFrm.Text.Trim() == "" || txtCustomerTo.Text.Trim() == "")
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sb.ToString();
frmDialogForm.ShowDialog();
if (txtCustomerFrm.Text.Trim() == "")
{
txtCustomerFrm.Text = frmDialogForm.selectedValue;
}
else
{
txtCustomerTo.Text = frmDialogForm.selectedValue;
}
if (txtCustomerFrm.Text.Trim() != "" && txtCustomerTo.Text.Trim() != "")
{
linkLabelCustomer.Enabled = false;
}
}
}
private void linkLabelIP_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT docId,postingDate,countDateTime,warehouse,remarks1 FROM inventoryposting ORDER BY docId DESC");
if (txtIPNoFrm.Text.Trim() == "" || txtIPNoTo.Text.Trim() == "")
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sb.ToString();
frmDialogForm.ShowDialog();
if (txtIPNoFrm.Text.Trim() == "")
{
txtIPNoFrm.Text = frmDialogForm.selectedValue;
}
else
{
txtIPNoTo.Text = frmDialogForm.selectedValue;
}
if (txtIPNoFrm.Text.Trim() != "" && txtIPNoTo.Text.Trim() != "")
{
linkLabelIP.Enabled = false;
}
}
}
private void linkLabelSalesInvoiceNo_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT docId,customerCode,customerCode,postingDate,warehouse,grossTotal FROM salesreturn ORDER BY docId DESC");
if (txtSalesReturnInvoiceNoFrm.Text.Trim() == "" || txtSalesReturnInvoiceNoTo.Text.Trim() == "")
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sb.ToString();
frmDialogForm.ShowDialog();
if (txtSalesReturnInvoiceNoFrm.Text.Trim() == "")
{
txtSalesReturnInvoiceNoFrm.Text = frmDialogForm.selectedValue;
}
else
{
txtSalesReturnInvoiceNoTo.Text = frmDialogForm.selectedValue;
}
if (txtSalesReturnInvoiceNoFrm.Text.Trim() != "" && txtSalesReturnInvoiceNoTo.Text.Trim() != "")
{
linkLabelSalesReturnInvoiceNo.Enabled = false;
}
}
}
private void linkLabelIP_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT docId,postingDate,warehouse,grossTotal,remarks1 FROM deliveryreceipt ORDER BY docId DESC");
if (txtDRNoFrm.Text.Trim() == "" || txtDRNoTo.Text.Trim() == "")
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sb.ToString();
frmDialogForm.ShowDialog();
if (txtDRNoFrm.Text.Trim() == "")
{
txtDRNoFrm.Text = frmDialogForm.selectedValue;
}
else
{
txtDRNoTo.Text = frmDialogForm.selectedValue;
}
if (txtDRNoFrm.Text.Trim() != "" && txtDRNoTo.Text.Trim() != "")
{
linkLabelDeliveryReceipt.Enabled = false;
}
}
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT docId,vendorCode,vendorName,postingDate,warehouse,grossTotal FROM purchaseorder ORDER BY docId DESC");
if (txtPONoFrm.Text.Trim() == "" || txtPONoTo.Text.Trim() == "")
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sb.ToString();
frmDialogForm.ShowDialog();
if (txtPONoFrm.Text.Trim() == "")
{
txtPONoFrm.Text = frmDialogForm.selectedValue;
}
else
{
txtPONoTo.Text = frmDialogForm.selectedValue;
}
if (txtPONoFrm.Text.Trim() != "" && txtPONoTo.Text.Trim() != "")
{
linkLabelPO.Enabled = false;
}
}
}
private void linkVendor_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
sql = "SELECT code,BPName FROM businesspartner WHERE BPType=0 AND deactivated='N' AND code like '" + txtVendor.Text.Replace("*", "%").Trim() + "' ORDER BY BPName";
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtVendor.Text = frmDialogForm.selectedValue;
}
private void btnFind_Click(object sender, EventArgs e)
{
if (btnFind.Text == "&Find")
{
if (txtWhCode.Text.Trim() != "" && !txtWhCode.Text.Contains("*"))
{
sql = sql = "SELECT id,code,name,deactivated FROM warehouse WHERE code='" + txtWhCode.Text.Trim() + "'";
database db = new database();
DataTable dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count < 1)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
id = Convert.ToInt32(dt.Rows[0]["id"]);
txtWhCode.Text = dt.Rows[0]["code"].ToString();
txtName.Text = dt.Rows[0]["name"].ToString();
if (dt.Rows[0]["deactivated"].ToString() == "Y")
{
chkDeactivate.Checked = true;
}
else
{
chkDeactivate.Checked = false;
}
if (vars.role > 0)
{
txtWhCode.ReadOnly = true;
txtName.ReadOnly = true;
}
btnFind.Text = "&OK";
}
else
{
registerControls();
bool found = false;
foreach (Control current_control in this.Controls)
{
if (current_control is TextBox && current_control.Text.Contains("*"))
{
foreach (DataRow row in table.Rows)
{
if ((string)row["controls"] == current_control.Name.ToString() && current_control.Text != "")
{
row["Value"] = current_control.Text; //insert the value if current_control is not ""
found = true;
}
}
}
}
if (found == false)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
sql = "SELECT code,name,deactivated FROM warehouse WHERE ";
foreach (DataRow row in table.Rows)
{
if ((string)row["Value"] != "")
{
sql += row["DBcolumnName"] + " like '" + row["Value"].ToString().Replace("*", "%") + "' AND ";
}
}
}
sql = sql.Remove(sql.Length - 7);
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtWhCode.Text = frmDialogForm.selectedValue;
}
}
else if (btnFind.Text == "&OK")
{
this.Close();
}
else if (btnFind.Text == "&Update")
{
if (checkValues())
{
string c = chkDeactivate.Checked == true ? "Y" : "N";
database db = new database();
sql = "UPDATE warehouse SET code='" + txtWhCode.Text.Trim() + "',name='" + txtName.Text.Trim() + "',updateDate=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'),updatedBy=" + vars.user_id + ",deactivated='" + c + "' WHERE id=" + id;
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Updating has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnFind.Text = "&OK";
}
}
}
else if (btnFind.Text == "&Save")
{
if (checkValues())
{
if (MessageBox.Show(this, "Are you sure you want to save this?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
string c;
c = (chkDeactivate.Checked == true) ? "Y" : "N";
sql = "INSERT INTO warehouse(code,name,deactivated,createDate,createdBy) ";
sql += "Values('" + txtWhCode.Text.Trim() + "','" + txtName.Text.Trim().Replace("'", "''") + "','" + c + "',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')," + vars.user_id + ")";
database db = new database();
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Saving has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
cleanUpUI();
btnFind.Text = "&Find";
}
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
if (btnFind.Text == "&Find")
{
sql = "SELECT username Username,password,fName'First name',midName 'Middle',lName 'Last name',email,address,gender,picLocation,deactivated,role FROM users WHERE ";
if (txtUsername.Text.Trim() != "" && !txtUsername.Text.Contains("*")) //txtUsername = "******"
{
sql += "username='******'";
database db = new database();
DataTable dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count < 1)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
txtFirst.Text = dt.Rows[0]["First name"].ToString();
txtMiddle.Text = dt.Rows[0]["Middle"].ToString();
txtLast.Text = dt.Rows[0]["Last name"].ToString();
txtEmail.Text = dt.Rows[0]["email"].ToString();
txtAddress.Text = dt.Rows[0]["address"].ToString();
cboGender.Text = dt.Rows[0]["gender"].ToString();
picLocation = dt.Rows[0]["picLocation"].ToString();
pictureBox1.ImageLocation = picLocation;
txtPassword.Text = dt.Rows[0]["password"].ToString();
password = dt.Rows[0]["password"].ToString();
if (dt.Rows[0]["deactivated"].ToString() == "Y")
{
chkDeactivate.Checked = true;
}
else
{
chkDeactivate.Checked = false;
}
cboRole.Text = convertRole.role(Convert.ToInt16(dt.Rows[0]["role"]));
if (vars.role == 0 || txtUsername.Text.Trim() == vars.username)
{
linkChangePic.Enabled = true;
linkPassword.Enabled = true;
}
txtUsername.ReadOnly = true;
txtPassword.ReadOnly = true;
btnFind.Text = "&OK";
}
else
{
registerControls();
bool found; found = false;
foreach (Control current_control in this.Controls)
{
if (current_control is TextBox && current_control.Text.Contains("*") && current_control.Name != txtPassword.Name)
{
foreach (DataRow row in table.Rows)
{
if ((string)row["controls"] == current_control.Name.ToString() && current_control.Text != "")
{
row["Value"] = current_control.Text; //insert the value if current_control is not ""
found = true;
}
}
}
}
if (found == false)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
foreach (DataRow row in table.Rows)
{
if ((string)row["Value"] != "")
{
sql += row["DBcolumnName"] + " like '" + row["Value"].ToString().Replace("*", "%") + "' AND ";
}
}
}
sql = sql.Remove(sql.Length - 7);
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtUsername.Text = frmDialogForm.selectedValue;
}
}
else if (btnFind.Text == "&OK")
{
this.Close();
}
else if (btnFind.Text == "&Update")
{
if (checkValues())
{
pw = txtPassword.Text.Trim() + vars.staticSalt;
salt = BCrypt.GenerateSalt();
hash = BCrypt.HashPassword(pw, salt);
Hashtable ht = new Hashtable();
ht.Add("username", txtUsername.Text.Trim());
ht.Add("password", hash);
ht.Add("fName", txtFirst.Text.Trim());
ht.Add("midName", txtMiddle.Text.Trim());
ht.Add("lName", txtLast.Text.Trim());
ht.Add("email", txtEmail.Text.Trim());
ht.Add("address", txtAddress.Text.Trim());
ht.Add("gender", cboGender.Text.Trim());
string c = (chkDeactivate.Checked == true) ? "Y" : "N";
ht.Add("deactivated", c);
picLocation = picLocation.Replace(@"\", @"\\");
ht.Add("picLocation", picLocation);
ht.Add("role", convertRole.role(cboRole.Text));
ht.Add("createdBy", vars.user_id);
if (changedPW)
{
ht.Add("changePassword", true);
}
Users user = new Users();
if (user.updateUser(ht))
{
MessageBox.Show(this, "Saving has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
cleanUpUI();
btnFind.Text = "&Find";
}
}
}
else if (btnFind.Text == "&Save")
{
if (checkValues())
{
if (MessageBox.Show(this, "Are you sure you want to save this?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
pw = txtPassword.Text.Trim() + vars.staticSalt;
salt = BCrypt.GenerateSalt();
hash = BCrypt.HashPassword(pw, salt);
Hashtable ht = new Hashtable();
ht.Add("username", txtUsername.Text.Trim());
ht.Add("password", hash);
ht.Add("fName", txtFirst.Text.Trim());
ht.Add("midName", txtMiddle.Text.Trim());
ht.Add("lName", txtLast.Text.Trim());
ht.Add("email", txtEmail.Text.Trim());
ht.Add("address", txtAddress.Text.Trim());
ht.Add("gender", cboGender.Text.Trim());
c = (chkDeactivate.Checked == true) ? "Y" : "N";
ht.Add("deactivated", c);
ht.Add("picLocation", picLocation);
ht.Add("role", convertRole.role(cboRole.Text));
ht.Add("createdBy", vars.user_id);
Users user = new Users();
if (user.addUser(ht))
{
MessageBox.Show(this, "Saving has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
cleanUpUI();
btnFind.Text = "&Find";
}
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
if (btnFind.Text == "&Find")
{
sql = "SELECT code,BPType,BPname,address,tel1,tel2,fax,email,website,contactP,deactivated,remarks FROM businesspartner WHERE ";
if (txtBPCode.Text.Trim() != "" && !txtBPCode.Text.Contains("*"))
{
sql += "code='" + txtBPCode.Text.Trim().Replace("'", "''") + "'";
database db = new database();
DataTable dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count < 1)
{
MessageBox.Show(this, "No matching record found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
txtBPCode.Text = dt.Rows[0]["code"].ToString();
txtName.Text = dt.Rows[0]["BPName"].ToString();
txtAddress.Text = dt.Rows[0]["address"].ToString();
txtTel1.Text = dt.Rows[0]["tel1"].ToString();
txtTel2.Text = dt.Rows[0]["tel2"].ToString();
txtFaxNo.Text = dt.Rows[0]["fax"].ToString();
txtEmail.Text = dt.Rows[0]["email"].ToString();
txtWebsite.Text = dt.Rows[0]["website"].ToString();
txtContactP.Text = dt.Rows[0]["contactP"].ToString();
txtRemarks.Text = dt.Rows[0]["remarks"].ToString();
if (dt.Rows[0]["deactivated"].ToString() == "Y")
{
chkDeactivate.Checked = true;
}
else
{
chkDeactivate.Checked = false;
}
//if (Convert.ToInt16(dt.Rows[0]["BPType"]) == 0)
cboBPType.SelectedIndex = Convert.ToInt16(dt.Rows[0]["BPType"]);
/* if (vars.role > 0)
* {
* txtWhCode.ReadOnly = true;
* txtName.ReadOnly = true;
* } */
btnFind.Text = "&OK";
}
else
{
registerControls();
bool found = false;
foreach (Control current_control in this.Controls)
{
if (current_control is TextBox && current_control.Text.Contains("*"))
{
foreach (DataRow row in table.Rows)
{
if ((string)row["controls"] == current_control.Name.ToString() && current_control.Text != "")
{
row["Value"] = current_control.Text; //insert the value if current_control is not ""
found = true;
}
}
}
}
if (found == false)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
foreach (DataRow row in table.Rows)
{
if ((string)row["Value"] != "")
{
sql += row["DBcolumnName"] + " like '" + row["Value"].ToString().Replace("*", "%") + "' AND ";
}
}
}
sql = sql.Remove(sql.Length - 7);
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtBPCode.Text = frmDialogForm.selectedValue;
}
}
else if (btnFind.Text == "&OK")
{
this.Close();
}
else if (btnFind.Text == "&Update")
{
if (checkValues())
{
string c = chkDeactivate.Checked == true ? "Y" : "N";
database db = new database();
sql = "UPDATE businesspartner SET BPname='" + txtName.Text.Trim().Replace("'", "''") + "',address='" + txtAddress.Text.Trim().Replace("'", "''") + "',tel1='" + txtTel1.Text.Trim() + "',tel2='" + txtTel2.Text.Trim() + "',fax='" + txtFaxNo.Text.Trim() + "',email='" + txtEmail.Text.Trim() + "',website='" + txtWebsite.Text.Trim() + "',contactP='" + txtContactP.Text.Trim() + "',remarks='" + txtRemarks.Text.Trim().Replace("'", "''") + "',deactivated='" + c + "',updateDate=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'),updatedBy=" + vars.user_id + " WHERE code='" + txtBPCode.Text.Trim() + "'";
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Updating has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnFind.Text = "&OK";
}
}
}
else if (btnFind.Text == "&Save")
{
if (checkValues())
{
if (MessageBox.Show(this, "Are you sure you want to save this?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
string c; int type;
c = (chkDeactivate.Checked == true) ? "Y" : "N";
type = BPCode(cboBPType.Text);
sql = "INSERT INTO businesspartner(code,BPType,BPname,address,tel1,tel2,fax,email,website,contactP,deactivated,remarks,createDate,createdBy) ";
sql += "Values('" + txtBPCode.Text.Trim() + "'," + type + ",'" + txtName.Text.Trim().Replace("'", "''") + "','" + txtAddress.Text.Trim().Replace("'", "''") + "','" + txtTel1.Text.Trim() + "','" + txtTel2.Text.Trim() + "','" + txtFaxNo.Text.Trim() + "','" + txtEmail.Text.Trim() + "','" + txtWebsite.Text.Trim() + "','" + txtContactP.Text.Trim() + "','" + c + "','" + txtRemarks.Text.Trim().Replace("'", "''") + "',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')," + vars.user_id + ")";
database db = new database();
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Saving has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
cleanUpUI();
btnFind.Text = "&Find";
}
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
if (btnFind.Text == "&Find")
{
sql = "SELECT A.itemCode,A.description,A.shortName,A.vatable,A.vendor,qtyPrPrchsUoM,qtyPrSaleUoM,prchsUoM,SaleUoM,A.deactivated,A.remarks,A.trans,minStock,maxStock";
sql += " FROM itemmasterdata A WHERE ";
if (txtItemCode.Text.Trim() != "" && !txtItemCode.Text.Contains("*"))
{
sql += "itemCode='" + txtItemCode.Text.Trim() + "'";
database db = new database();
DataTable dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count < 1)
{
MessageBox.Show(this, "No matching record found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
txtItemCode.Text = dt.Rows[0]["itemCode"].ToString();
txtDescription.Text = dt.Rows[0]["description"].ToString();
txtShortName.Text = dt.Rows[0]["shortName"].ToString();
txtVendor.Text = dt.Rows[0]["vendor"].ToString();
txtRemarks.Text = dt.Rows[0]["remarks"].ToString();
trans = dt.Rows[0]["trans"].ToString() == "Y" ? true : false; //N and null are the same
txtPurchaseUoM.Text = dt.Rows[0]["prchsUoM"].ToString();
txtQtyPrPrchsUoM.Text = dt.Rows[0]["qtyPrPrchsUoM"].ToString();
txtSaleUoM.Text = dt.Rows[0]["saleUoM"].ToString();
txtQtyPrSalesUoM.Text = dt.Rows[0]["qtyPrSaleUoM"].ToString();
txtMinStock.Text = dt.Rows[0]["minStock"].ToString();
txtMaxStock.Text = dt.Rows[0]["maxStock"].ToString();
if (trans == true)
{
txtItemCode.ReadOnly = true;
txtVendor.ReadOnly = true;
linkVendor.Enabled = false;
txtPurchaseUoM.ReadOnly = true;
txtSaleUoM.ReadOnly = true;
txtQtyPrPrchsUoM.ReadOnly = true;
txtQtyPrSalesUoM.ReadOnly = true;
}
else
{
linkVendor.Enabled = true;
}
if (dt.Rows[0]["vatable"].ToString() == "Y")
{
chkVatable.Checked = true;
}
else
{
chkVatable.Checked = false;
}
if (dt.Rows[0]["deactivated"].ToString() == "Y")
{
chkDeactivate.Checked = true;
}
else
{
chkDeactivate.Checked = false;
}
db = new database();
dt = db.select("SELECT barcode FROM barcode WHERE itemCode='" + txtItemCode.Text + "'", vars.MySqlConnection);
htBarcodeFromDB = new Hashtable();
i = 0;
foreach (DataRow r in dt.Rows)
{
dgvBarcode.Rows.Add(r["barcode"].ToString());
htBarcodeFromDB.Add(i, r["barcode"].ToString());
i++;
}
sql = "SELECT priceListCode,netPrice FROM pricelist WHERE itemCode='" + txtItemCode.Text.Trim() + "' ORDER BY priceListCode";
db = new database(); dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
htPriceFromDB = new Hashtable();
foreach (DataRow r in dt.Rows)
{
htPriceFromDB[r["priceListCode"]] = r["netPrice"];
htNewPrice[r["priceListCode"]] = r["netPrice"];
}
sql = "SELECT B.*,A.name FROM warehouse A JOIN item_warehouse B ON A.code=B.whCode WHERE itemCode='" + txtItemCode.Text.Trim() + "'";
db = new database(); dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
rowCount = dt.Rows.Count;
for (i = 0; i < rowCount; i++)
{
dgvInventory.Rows.Add();
dgvInventory.Rows[i].Cells["whCode"].Value = dt.Rows[i]["itemCode"].ToString();
dgvInventory.Rows[i].Cells["whName"].Value = dt.Rows[i]["name"].ToString();
dgvInventory.Rows[i].Cells["inStock"].Value = dt.Rows[i]["inStock"].ToString();
}
btnFind.Text = "&OK";
}
else
{
registerControls();
bool found = false;
foreach (Control current_control in this.Controls)
{
if (current_control is TextBox && current_control.Text.Contains("*"))
{
foreach (DataRow row in table.Rows)
{
if ((string)row["controls"] == current_control.Name.ToString() && current_control.Text != "")
{
row["Value"] = current_control.Text; //insert the value if current_control is not ""
found = true;
}
}
}
}
if (found == false)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
foreach (DataRow row in table.Rows)
{
if ((string)row["Value"] != "")
{
sql += row["DBcolumnName"] + " like '" + row["Value"].ToString().Replace("*", "%") + "' AND ";
}
}
}
sql = sql.Remove(sql.Length - 7);
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtItemCode.Text = frmDialogForm.selectedValue;
}
}
else if (btnFind.Text == "&OK")
{
this.Close();
}
else if (btnFind.Text == "&Update")
{
if (MessageBox.Show(this, "Are you sure you want to update the records?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
if (checkValues())
{
string deactivate = chkDeactivate.Checked == true ? "Y" : "N";
string v = chkVatable.Checked == true ? "Y" : "N";
string varWeightItem = chkVarWeightItm.Checked == true ? "Y" : "N";
database db = new database();
if (trans == false && txtVendor.Text.Trim() != "")
{
sql = "SELECT code FROM businesspartner WHERE code='" + txtVendor.Text.Trim() + "' AND BPType=0 AND deactivated='N';";
if (db.select(sql, vars.MySqlConnection).Rows.Count == 0)
{
MessageBox.Show(this, "Invalid Vendor code.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
db = new database();
sql = "START TRANSACTION;";
sql += "SET @date=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');";
sql += "SET @user_id=" + vars.user_id + ";";
sql += "SET @varWeightItm='" + varWeightItem + "';";
sql += "SET @vendor=(SELECT code FROM businesspartner WHERE code='" + txtVendor.Text.Trim() + "' AND BPType=0 AND deactivated='N');";
sql += "UPDATE itemmasterdata SET itemCode='" + txtItemCode.Text.Trim() + "',description='" + txtDescription.Text.Trim().Replace("'", "''") + "',shortName='" + txtShortName.Text.Trim().Replace("'", "''") + "',vatable='" + v + "',varWeightItm='" + varWeightItem + "',deactivated='" + deactivate + "',updateDate=@date,updatedBy=@user_id";
if (txtRemarks.Text.Trim() != "")
{
sql += ",remarks='" + txtRemarks.Text.Trim().Replace("'", "''") + "'";
}
if (trans == false)
{
sql += ",qtyPrPrchsUoM=" + txtQtyPrPrchsUoM.Text.ToString() + ",qtyPrSaleUoM=" + txtQtyPrSalesUoM.Text.ToString() + ",prchsUoM='" + txtPurchaseUoM.Text.ToString() + "',SaleUoM='" + txtSaleUoM.Text.ToString() + "'";
}
sql += ",minStock=" + txtMinStock.Text.Trim() + ",maxStock=" + txtMaxStock.Text.Trim();
sql += ",vendor=@vendor";
sql += " WHERE itemCode='" + txtItemCode.Text.Trim() + "';";
//compare current barcode(s) and the retrieved barcode(s)
rowCount = (Int16)htNewBarcode.Count;
for (i = 0; i < rowCount; i++)
{
if (htNewBarcode[i].ToString() != htBarcodeFromDB[i].ToString())
{
sql += "INSERT INTO barcodehistory(itemCode,barcode,cp_createDate,cp_createdBy,createDate,createdBy) SELECT itemCode,barcode,createDate,createdBy,@date,@user_id FROM barcode WHERE itemCode='" + txtItemCode.Text.Trim() + "' AND barcode='" + htBarcodeFromDB[i].ToString() + "';";
sql += "UPDATE barcode SET barcode='" + htNewBarcode[i].ToString() + "' WHERE itemCode='" + txtItemCode.Text.Trim() + "' AND barcode='" + htBarcodeFromDB[i].ToString() + "';";
}
}
//we need this because when txtUnitPrice did not fire new price is not updated.
htNewPrice[(int)priceList(cboPriceList.Text)] = txtUnitPrice.Text.Trim();
//compare new price and previews price
rowCount = (Int16)htNewPrice.Count;
for (i = 0; i < rowCount; i++)
{
if (Convert.ToDecimal(htNewPrice[i]) != Convert.ToDecimal(htPriceFromDB[i]))
{
sql += "INSERT INTO pricelisthistory(itemCode,priceListCode,netPrice,cp_createDate,cp_createdBy,createDate,createdBy) SELECT itemCode,priceListCode,netPrice,createDate,createdBy,@date,@user_id FROM pricelist WHERE itemCode='" + txtItemCode.Text.Trim() + "' AND priceListCode=" + i + ";";
sql += "UPDATE pricelist SET netPrice=" + Convert.ToDecimal(htNewPrice[i]) + " WHERE itemCode='" + txtItemCode.Text.Trim() + "' AND priceListCode=" + i + ";";
}
}
sql += "COMMIT;"; //Finally make it permanent
//if (MessageBox.Show(this, sql, "SQL", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
// return;
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
//copy values into htFromDB
rowCount = (Int16)htNewPrice.Count;
for (i = 0; i < rowCount; i++)
{
htPriceFromDB[i] = htNewPrice[i];
}
rowCount = (Int16)htNewBarcode.Count;
for (i = 0; i < rowCount; i++)
{
htBarcodeFromDB[i] = htNewBarcode[i];
}
btnFind.Text = "&OK";
dgvBarcode.Refresh();
MessageBox.Show(this, "Updating has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
else if (btnFind.Text == "&Save")
{
if (checkValues())
{
if (MessageBox.Show(this, "Are you sure you want to save this?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
string deactivate = (chkDeactivate.Checked == true) ? "Y" : "N";
string varWeightItem = chkVarWeightItm.Checked == true ? "Y" : "N";
string v = chkVatable.Checked == true ? "Y" : "N";
sql = "START TRANSACTION;";
sql += "SET @date=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');";
sql += "SET @user_id=" + vars.user_id + ";";
sql += "SET @varWeightItm='" + varWeightItem + "';";
sql += "SET @newId=(SELECT CAST(lastNo+1 AS char(11)) FROM documents WHERE documentCode='IMD');";
sql += "SET @itemCode=CONCAT('" + vars.terminalId + "', 'ITM', @newId);";
sql += "SET @vendor=(SELECT code FROM businesspartner WHERE code='" + txtVendor.Text.Trim() + "' AND BPType=0 AND deactivated='N');";
sql += "INSERT INTO itemmasterdata(itemCode";
if (txtDescription.Text.Trim() != "")
{
sql += ",description";
}
if (txtShortName.Text.Trim() != "")
{
sql += ",shortName";
}
sql += ",vatable,vendor,deactivated,qtyPrPrchsUoM,qtyPrSaleUoM,prchsUoM,saleUoM,varWeightItm";
if (txtRemarks.Text.Trim() != "")
{
sql += ",remarks";
}
sql += ",minStock,maxStock";
sql += ",createDate,createdBy)";
sql += " VALUES(@itemCode";
if (txtDescription.Text.Trim() != "")
{
sql += ",'" + txtDescription.Text.Trim().Replace("'", "''") + "'";
}
if (txtShortName.Text.Trim() != "")
{
sql += ",'" + txtShortName.Text.Trim().Replace("'", "''") + "'";
}
sql += ",'" + v + "',@vendor,'" + deactivate + "'," + txtQtyPrPrchsUoM.Text.Trim() + "," + txtQtyPrSalesUoM.Text.Trim() + ",'" + txtPurchaseUoM.Text.Trim() + "','" + txtSaleUoM.Text.Trim() + "',@varWeightItm";
if (txtRemarks.Text.Trim() != "")
{
sql += ",'" + txtRemarks.Text.Trim().Replace("'", "''") + "'";
}
sql += "," + txtMinStock.Text.Trim() + "," + txtMaxStock.Text.Trim();
sql += ",@date,@user_id);";
Int16 priceListCode; double p;
foreach (string item in cboPriceList.Items)
{
priceListCode = priceList(item);
p = Convert.ToDouble(htNewPrice[(int)priceListCode]);
sql += "INSERT INTO pricelist(itemCode,priceListCode,netPrice,createdBy) VALUES(@itemCode," + priceListCode + "," + p + "," + vars.user_id + ");";
}
rowCount = Convert.ToInt16(dgvBarcode.Rows.Count);
for (i = 0; i < rowCount; i++)
{
if (!dgvBarcode.Rows[i].IsNewRow)
{
if (dgvBarcode.Rows[i].Cells[0].Value.ToString().Trim() != "")
{
sql += "INSERT INTO barcode(itemCode,barcode,createDate,createdBy) VALUES(@itemCode,'" + dgvBarcode.Rows[i].Cells[0].Value.ToString() + "',@date,@user_id);";
}
}
}
sql += "UPDATE documents SET lastNo=CAST(@newId AS UNSIGNED) WHERE documentCode='IMD';";
sql += " COMMIT;"; //Finally commit it
database db = new database();
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Saving has been successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnFind.Text = "&Find";
cleanUpUI();
}
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
if (btnFind.Text == "&Find")
{
sql = "SELECT docId,postingDate,countDateTime,warehouse,remarks1 FROM inventoryposting WHERE ";
if (txtInvPostingNo.Text.Trim() != "" && !txtInvPostingNo.Text.Contains("*"))
{
sql += "docId='" + txtInvPostingNo.Text.Trim().Replace("'", "''") + "'";
db = new database();
dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count < 1)
{
MessageBox.Show(this, "No matching record found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
txtInvPostingNo.Text = dt.Rows[0]["docId"].ToString();
txtPostingDate.Text = dt.Rows[0]["postingDate"].ToString();
txtCountDateTime.Text = dt.Rows[0]["countDateTime"].ToString();
cboWarehouse.Text = dt.Rows[0]["warehouse"].ToString();
txtRemarks1.Text = dt.Rows[0]["remarks1"].ToString();
sql = "SELECT * FROM inventoryposting_item WHERE docId='" + txtInvPostingNo.Text.Trim() + "' ORDER BY indx";
db = new database();
dt = db.select(sql, vars.MySqlConnection);
rowCount = dt.Rows.Count;
decimal d;
for (i = 0; i < rowCount; i++)
{
dgvItems.Rows.Add();
dgvItems.Rows[i].Cells["colItemCode"].Value = dt.Rows[i]["itemCode"].ToString();
dgvItems.Rows[i].Cells["colItemDescription"].Value = dt.Rows[i]["description"].ToString();
dgvItems.Rows[i].Cells["colVatable"].Value = dt.Rows[i]["vatable"].ToString();
dgvItems.Rows[i].Cells["colCurrentQty"].Value = Decimal.Parse(dt.Rows[i]["currentQty"].ToString()).ToString(vars.format);
d = Decimal.Parse(dt.Rows[i]["countedQty"].ToString());
dgvItems.Rows[i].Cells["colCountedQty"].Value = d.ToString(vars.format);
dgvItems.Rows[i].Cells["colVariance"].Value = Decimal.Parse(dt.Rows[i]["varianceQty"].ToString()).ToString(vars.format);
d = Decimal.Parse(dt.Rows[i]["prchsPrc"].ToString());
dgvItems.Rows[i].Cells["colPrchsPrc"].Value = d.ToString(vars.format);
d = Decimal.Parse(dt.Rows[i]["retailPrc"].ToString());
dgvItems.Rows[i].Cells["colRetailPrc"].Value = d.ToString(vars.format);
}
setCntrlsToOKMode();
computeItemCount();
}
else
{
registerControls();
bool found = false;
foreach (Control current_control in this.Controls)
{
if (current_control is TextBox && current_control.Text.Contains("*"))
{
foreach (DataRow row in table.Rows)
{
if ((string)row["controls"] == current_control.Name.ToString() && current_control.Text != "")
{
row["Value"] = current_control.Text; //insert the value if current_control is not ""
found = true;
}
}
}
}
if (found == false)
{
MessageBox.Show(this, "No matching records found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtInvPostingNo.Focus();
return;
}
else
{
foreach (DataRow row in table.Rows)
{
if ((string)row["Value"] != "")
{
sql += row["DBcolumnName"] + " like '" + row["Value"].ToString().Replace("*", "%") + "' AND ";
}
}
}
sql = sql.Remove(sql.Length - 7);
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
txtInvPostingNo.Text = frmDialogForm.selectedValue;
}
}
else if (btnFind.Text == "&OK")
{
this.Close();
}
else if (btnFind.Text == "&Update")
{
db = new database();
sql = "UPDATE grpo SET remarks1='" + txtRemarks1.Text.Trim().Replace("'", "''") + "',updateDate=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'),updatedBy=" + vars.user_id + " WHERE docId='" + txtInvPostingNo.Text.Trim() + "'";
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Updating has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnFind.Text = "&OK";
}
}
else if (btnFind.Text == "&Save")
{
if (checkValues())
{
if (MessageBox.Show(this, "Are you sure you want to save this?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
DateTime dateTime; string strPostingDate, strCountDateTime;
try
{
dateTime = DateTime.Parse(txtPostingDate.Text.Trim());
strPostingDate = dateTime.ToString("yyyy/MM/dd");
dateTime = DateTime.Parse(txtCountDateTime.Text.Trim());
strCountDateTime = dateTime.ToString("yyyy/MM/dd H:mm");
}
catch (Exception err)
{
MessageBox.Show(this, err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
sql = "START TRANSACTION;";
sql += "SET @date=DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');";
sql += "SET @postingDate=DATE_FORMAT('" + strPostingDate + "', '%Y-%m-%d');";
sql += "SET @user_id=" + vars.user_id + ";";
sql += "SET @newId=(SELECT CAST(lastNo+1 AS char(11)) FROM documents WHERE documentCode='IP');";
sql += "SET @docId=CONCAT('" + vars.terminalId + "', @newId);";
sql += "SET @countDateTime=DATE_FORMAT('" + strCountDateTime + "', '%Y-%m-%d %H:%i');";
sql += "SET @warehouse='" + cboWarehouse.Text + "';";
sql += "INSERT INTO inventoryPosting(docId,postingDate,countDateTime,warehouse";
if (txtRemarks1.Text.Trim() != "")
{
sql += ",remarks1";
}
sql += ",createDate,createdBy)";
sql += " VALUES(@docId,@postingDate,@countDateTime,@warehouse";
if (txtRemarks1.Text.Trim() != "")
{
sql += ",'" + txtRemarks1.Text.Trim() + "'";
}
sql += ",@date,@user_id);";
rowCount = dgvItems.Rows.Count;
for (i = 0; i < rowCount; i++)
{
if (!dgvItems.Rows[i].IsNewRow && dgvItems.Rows[i].Cells[0].Value.ToString() != "")
{
string varItemCode, varDescription, varVatable;
decimal varCurrentQty, varCountedQty, varVariance, varPrchsPrc, varRetailPrc;
varItemCode = dgvItems.Rows[i].Cells["colItemCode"].Value.ToString();
varDescription = dgvItems.Rows[i].Cells["colItemDescription"].Value.ToString();
varVatable = dgvItems.Rows[i].Cells["colVatable"].Value.ToString();
varCurrentQty = Decimal.Parse(dgvItems.Rows[i].Cells["colCurrentQty"].Value.ToString());
varCountedQty = Decimal.Parse(dgvItems.Rows[i].Cells["colCountedQty"].Value.ToString());
varVariance = Decimal.Parse(dgvItems.Rows[i].Cells["colVariance"].Value.ToString());
varPrchsPrc = Decimal.Parse(dgvItems.Rows[i].Cells["colPrchsPrc"].Value.ToString());
varRetailPrc = Decimal.Parse(dgvItems.Rows[i].Cells["colRetailPrc"].Value.ToString());
sql += "INSERT INTO inventoryPosting_item(docId,indx,itemCode,description,vatable,currentQty,countedQty,varianceQty,prchsPrc,retailPrc)";
sql += " VALUES(@docId," + i + ",'" + varItemCode + "','" + varDescription + "','" + varVatable + "'," + varCurrentQty + "," + varCountedQty + "," + varVariance + "," + varPrchsPrc + "," + varRetailPrc + ");";
sql += "UPDATE itemmasterdata SET trans='Y' WHERE itemCode='" + varItemCode + "';";
sql += "INSERT INTO item_warehouse(itemCode,whCode,inStock) VALUES('" + varItemCode + "','" + cboWarehouse.Text + "'," + varVariance + ") ON DUPLICATE KEY UPDATE";
if (varVariance < 0)
{
sql += " inStock=inStock-" + Math.Abs(varVariance) + ";";
}
else
{
sql += " inStock=inStock+" + varVariance + ";";
}
}
}
sql += "UPDATE documents SET lastNo=CAST(@newId AS UNSIGNED) WHERE documentCode='IP';";
sql += "COMMIT;";
db = new database();
if (db.executeNonQuery(sql, vars.MySqlConnection) > 0)
{
MessageBox.Show(this, "Saving has been successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
db = new database(); dt = new DataTable();
dt = db.select("SELECT docId FROM inventoryposting ORDER BY id DESC LIMIT 1", vars.MySqlConnection);
txtInvPostingNo.Text = dt.Rows[0][0].ToString();
setCntrlToAfterSaveMode();
}
}
}
}
void dgvItems_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (btnFind.Text == "&Save")
{
if (dgvItems.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
string dbColumnName = getDbColumnName(dgvItems.Columns[e.ColumnIndex].Name);
if (dbColumnName == "itemCode" || dbColumnName == "description")
{
sql = "SELECT itemCode,description FROM itemmasterdata WHERE deactivated='N'";
string returnedValue;
if (dgvItems.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
if (dgvItems.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString().Contains("*"))
{
sql += " AND " + dbColumnName + " LIKE '" + dgvItems.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString().Replace("*", "%") + "' ORDER BY " + dbColumnName;
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
returnedValue = frmDialogForm.selectedValue;
if (returnedValue != "")
{
dgvItems.Rows[e.RowIndex].Cells["colItemCode"].Value = returnedValue;
Hashtable ht = new Hashtable(getInfo(returnedValue));
if (ht != null)
{
populateDataGridRow(ht, e.RowIndex);
}
}
}
else //doesn't contain asterisk
{
sql += " AND " + dbColumnName + "='" + dgvItems.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() + "' ORDER BY " + dbColumnName;
db = new database();
dt = new DataTable();
dt = db.select(sql, vars.MySqlConnection);
if (dt.Rows.Count > 1)
{
frmDialogForm = new frmDialog();
frmDialogForm.selectedValue = sql;
frmDialogForm.ShowDialog();
returnedValue = frmDialogForm.selectedValue;
if (returnedValue != "")
{
dgvItems.Rows[e.RowIndex].Cells["itemCode"].Value = returnedValue;
Hashtable ht = new Hashtable(getInfo(returnedValue));
if (ht != null)
{
resetCellValues(e.RowIndex);
dgvItems.Rows[e.RowIndex].Cells["colItemDescription"].Value = ht["description"];
populateDataGridRow(ht, e.RowIndex);
}
}
}
else if (dt.Rows.Count == 1)
{
dgvItems.Rows[e.RowIndex].Cells["colItemCode"].Value = dt.Rows[0]["itemCode"].ToString();
Hashtable ht = new Hashtable(getInfo(dt.Rows[0]["itemCode"].ToString()));
if (ht != null)
{
resetCellValues(e.RowIndex);
populateDataGridRow(ht, e.RowIndex);
}
}
else
{
MessageBox.Show(this, "No matching record found.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
resetCellValues(e.RowIndex);
}
}
}
}
//when column colCountedQty is modified
if (dgvItems.Columns[e.ColumnIndex].Name == "colCountedQty")
{
computeForCountedQty(e.RowIndex);
}
}
//every edit of the cell must compute other values
computeItemCount();
}
}