public bool addProduct(Product product)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
cnn.Open();
MySqlTransaction transaction = cnn.BeginTransaction();
try
{
const string SQL = @"INSERT INTO products (productcode,barcode,productname,description,pricein,priceout,remark,createddate,createdby,categoryid,updateddate,updatedby)" +
"VALUES(@productcode,@barcode,@productname,@description,@pricein,@priceout,@remark,CURRENT_TIMESTAMP(),@createdby,@categoryid,CURRENT_TIMESTAMP(),@updatedby)";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@productcode", product.Productcode);
command.Parameters.AddWithValue("@barcode", product.Barcode);
command.Parameters.AddWithValue("@productname", product.Productname);
command.Parameters.AddWithValue("@description", product.Description);
command.Parameters.AddWithValue("@pricein", product.Pricein);
command.Parameters.AddWithValue("@priceout", product.Priceout);
command.Parameters.AddWithValue("@remark", product.Remark);
command.Parameters.AddWithValue("@createdby", product.Createdby.Staffid);
command.Parameters.AddWithValue("@categoryid", product.Category.Categoryid);
command.Parameters.AddWithValue("@updatedby", product.Createdby.Staffid);
if (command.ExecuteNonQuery() > 0)
{
transaction.Commit();
return true;
}
}
catch (MySqlException e)
{
Console.WriteLine(e);
transaction.Rollback();
}
finally
{
cnn.Close();
}
}
return false;
}
public Invoice getInvoice(int invoiceId)
{
MySqlConnection con = DBUtility.getConnection();
if (con != null)
con.Open();
{
try
{
MySqlCommand cmdInv = new MySqlCommand("SELECT I.invoiceid, I.invoicedate, I.remark, I.discount, ID.quantity, ID.pricein, ID.priceout, ID.discount id_discount, S.staffid, S.staffname, S.stafftype, M.memberid, M.membername, M.membercode, M.phonenumber, M.createddate, P.productid, P.productcode, P.barcode, P.productname, P.description FROM Invoices I INNER JOIN InvoiceDetail ID ON I.invoiceid=ID.invoiceid INNER JOIN Products P ON ID.productid=P.productid INNER JOIN Staffs S ON I.Staffid=S.Staffid INNER JOIN Members M ON I.memberid=M.memberid WHERE I.invoiceid=" + invoiceId, con);
MySqlDataReader drInv = cmdInv.ExecuteReader();
ArrayList arrInvDetail = new ArrayList();
Invoice inv = new Invoice();
inv.Invoiceid = invoiceId;
Member member = new Member();
Staff staff = new Staff();
while (drInv.Read())
{
inv.Invoicedate = drInv.GetDateTime("invoicedate");
inv.Discount = drInv.GetDecimal("discount");
inv.Remark = DBUtility.SafeGetString(drInv, "remark");
InvoiceDetail invDetail = new InvoiceDetail();
Product product = new Product();
product.Productid = drInv.GetInt16("productid");
product.Productname = DBUtility.SafeGetString(drInv, "productname");
product.Productcode = DBUtility.SafeGetString(drInv, "productCode");
product.Barcode = DBUtility.SafeGetString(drInv, "barcode");
product.Quantity = drInv.GetDecimal("quantity");
product.Description = DBUtility.SafeGetString(drInv, "description");
invDetail.Product = product;
invDetail.Pricein = drInv.GetDecimal("pricein");
invDetail.Priceout = drInv.GetDecimal("priceout");
invDetail.Quantity = drInv.GetDecimal("quantity");
invDetail.Dicount = drInv.GetDecimal("id_discount");
arrInvDetail.Add(invDetail);
member.Memberid = drInv.GetInt16("memberid");
member.Membername = DBUtility.SafeGetString(drInv, "membername");
member.Phonenumber = DBUtility.SafeGetString(drInv, "phonenumber");
staff.Staffid = drInv.GetInt16("staffid");
staff.Staffname = drInv.GetString("staffname");
staff.Stafftype = drInv.GetString("stafftype");
inv.Staff = staff;
inv.Member = member;
}
inv.InvoiceDetail = arrInvDetail;
return inv;
}
catch (MySqlException e)
{
Console.WriteLine(e.ToString());
}
finally
{
con.Close();
}
}
return null;
}
public bool updateProduct(Product product)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
try
{
cnn.Open();
const string SQL = @"UPDATE products SET productcode = @productcode ," +
"barcode=@barcode, " +
"productname=@productname, " +
"description=@description, " +
"pricein=@pricein, " +
"priceout=@priceout, " +
"remark=@remark," +
"updateddate=CURRENT_TIMESTAMP()," +
"updatedby=@updatedby, " +
"categoryid=@categoryid " +
"WHERE productid = @productid";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@productcode", product.Productcode);
command.Parameters.AddWithValue("@barcode", product.Barcode);
command.Parameters.AddWithValue("@productname", product.Productname);
// command.Parameters.AddWithValue("@quantity", product.Quantity);
command.Parameters.AddWithValue("@description", product.Description);
command.Parameters.AddWithValue("@pricein", product.Pricein);
command.Parameters.AddWithValue("@priceout", product.Priceout);
// command.Parameters.AddWithValue("@returnquantity", product.Returnquantity);
command.Parameters.AddWithValue("@remark", product.Remark);
command.Parameters.AddWithValue("@updatedby", product.Updatedby.Staffid);
command.Parameters.AddWithValue("@categoryid", product.Category.Categoryid);
command.Parameters.AddWithValue("@productid", product.Productid);
if (command.ExecuteNonQuery() > 0)
{
return true;
}
}
catch (MySqlException e)
{
Console.WriteLine(e);
}
finally
{
cnn.Close();
}
}
return false;
}
public Product getProductById(String productId, int storeid)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
try
{
cnn.Open();
const string SQL = "SELECT P.productid,productcode,barcode,productname,description,pricein,priceout,remark,createddate,createdby,updateddate,updatedby,categoryid, Quantity, ReturnQuantity FROM products P LEFT JOIN StoreProduct SP ON P.productid=SP.productid WHERE P.productid=@productId AND SP.storeid = @storeid;";
MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare();
command.Parameters.AddWithValue("@productId", productId);
command.Parameters.AddWithValue("@storeid", storeid);
MySqlDataReader reader = command.ExecuteReader();
Product product = null;
while (reader.Read())
{
product = new Product();
product.Productid = reader.GetInt16("productid");
product.Productcode = DBUtility.SafeGetString(reader, "productcode");
product.Barcode = DBUtility.SafeGetString(reader, "barcode");
product.Productname = DBUtility.SafeGetString(reader, "productname");
product.Quantity = reader.GetInt16("quantity");
product.Description = DBUtility.SafeGetString(reader, "description");
product.Pricein = reader.GetDecimal("pricein");
product.Priceout = reader.GetDecimal("priceout");
product.Returnquantity = reader.GetInt16("returnquantity");
product.Remark = DBUtility.SafeGetString(reader, "remark");
product.Createddate = reader.GetDateTime("createddate");
Staff createdby = new Staff();
createdby.Staffid = reader.GetInt16("createdby");
Staff updatedby = new Staff();
updatedby.Staffid = reader.GetInt16("updatedby");
product.Createdby = createdby;
product.Updatedby = updatedby;
product.Updateddate = reader.GetDateTime("updateddate");
Category category = new Category();
category.Categoryid = reader.GetInt16("categoryid");
product.Category = category;
}
return product;
}
catch (MySqlException e)
{
Console.WriteLine(e);
}
finally
{
cnn.Close();
}
}
return null;
}
public Product getProduct(int productid)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
try
{
cnn.Open();
const string SQL = "SELECT productid,productcode,barcode,productname,description,pricein,priceout,remark,createddate,createdby,updateddate,updatedby,categoryid FROM products WHERE productid=@productid;";
MySqlCommand command = new MySqlCommand(SQL, cnn); command.Prepare();
command.Parameters.AddWithValue("@productid", productid);
MySqlDataReader reader = command.ExecuteReader();
Product product = null;
while (reader.Read())
{
product = new Product();
product.Productid = reader.GetInt16("productid");
product.Productcode = reader.GetString("productcode");
product.Barcode = reader.GetString("barcode");
product.Productname = reader.GetString("productname");
//product.Quantity = reader.GetInt16("quantity");
product.Description = reader.GetString("description");
product.Pricein = reader.GetInt16("pricein");
product.Priceout = reader.GetInt16("priceout");
//product.Returnquantity = reader.GetInt16("returnquantity");
product.Remark = reader.GetString("remark");
product.Createddate = reader.GetDateTime("createddate");
product.Createdby.Staffid = reader.GetInt16("createdby");
product.Updatedby.Staffid = reader.GetInt16("updatedby");
product.Updateddate = reader.GetDateTime("updateddate");
product.Category.Categoryid = reader.GetInt16("categoryid");
}
return product;
}
catch (MySqlException e)
{
Console.WriteLine(e);
}
finally
{
cnn.Close();
}
}
return null;
}