public Product(int productid, String productcode, String barcode , String productname ,String description ,
decimal pricein ,decimal priceout, String remark ,Staff createdby , Staff updatedby ,Category category )
{
this.productid = productid;
this.productcode = productcode;
this.barcode = barcode;
this.productcode = productcode;
this.productname = productname;
this.description = description;
this.pricein = pricein;
this.priceout = priceout;
this.remark = remark;
this.createdby = createdby;
this.updatedby = updatedby;
this.category = category;
}
public bool AddCategory(Category category)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
cnn.Open();
MySqlTransaction transaction = cnn.BeginTransaction();
try
{
const string SQL = @"INSERT INTO
categories(
categoryname
)
VALUES(
@categoryname
);";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@categoryname", category.Categoryname);
if (command.ExecuteNonQuery() > 0)
{
transaction.Commit();
return true;
}
}
catch (MySqlException e)
{
Console.WriteLine(e);
transaction.Rollback();
}
finally
{
cnn.Close();
}
}
return false;
}
public bool UdateCategory(Category category)
{
MySqlConnection cnn = DBUtility.getConnection();
if(cnn != null)
{
try
{
cnn.Open();
const string SQL = @"UPDATE
categories
SET
categoryname = @categoryname
WHERE
categoryid = @categoryid";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@categoryname", category.Categoryname);
command.Parameters.AddWithValue("@categoryid",category.Categoryid);
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;
}