public int CreateProductWithCost(ProductWithCost newProductWithCost)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbProductWithCost(PCode,Cost,RecordDate)");
sb.Append(" VALUES (@PCode,@Cost,@RecordDate)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProductWithCost.PCode;
com.Parameters.Add("@Cost", OleDbType.VarChar).Value = newProductWithCost.Cost;
string dateRecord = String.Format("{0:dd/MM/yyyy}", newProductWithCost.RecordDate);
com.Parameters.Add("@RecordDate", OleDbType.Date).Value = dateRecord;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateReciveOrder(ReciveOrder newReciveOrder, string _WHCode)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbReciveOrder(ReciveOrderCode,DateRecive,PO,SPCode,NetDc,NetVat,Net)");
sb.Append(" VALUES (@ReciveOrderCode,@DateRecive,@PO,@SPCode,@NetDc,@NetVat,@Net)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@ReciveOrderCode", OleDbType.VarChar).Value = newReciveOrder.ReciveCode;
string dateRecive = String.Format("{0:dd/MM/yyyy}", newReciveOrder.DateRecive);
com.Parameters.Add("@DateRecive", OleDbType.VarChar).Value = dateRecive;
com.Parameters.Add("@PO", OleDbType.VarChar).Value = newReciveOrder.PO;
com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newReciveOrder.SPCode;
com.Parameters.Add("@NetDc", OleDbType.VarChar).Value = newReciveOrder.NetDc;
com.Parameters.Add("@NetVat", OleDbType.VarChar).Value = newReciveOrder.NetVat;
com.Parameters.Add("@Net", OleDbType.VarChar).Value = newReciveOrder.Net;
com.ExecuteNonQuery();
foreach (ReciveOrderDetail r in newReciveOrder.reciveOrderDetails)
{
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbReciveOrderDetail(ReciveOrderCode,PCode,Cost,NumberRecive,Total)");
sb.Append(" VALUES (@ReciveOrderCode,@PCode,@Cost,@NumberRecive,@Total)");
string sqlSaveDetail;
sqlSaveDetail = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSaveDetail;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@ReciveOrderCode", OleDbType.VarChar).Value = r.ReciveCode;
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode;
com.Parameters.Add("@Cost", OleDbType.VarChar).Value = r.Cost;
com.Parameters.Add("@NumberRecive", OleDbType.VarChar).Value = r.NumberRecive;
com.Parameters.Add("@Total", OleDbType.VarChar).Value = r.Total;
com.ExecuteNonQuery();
ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _WHCode);
if (pOnwarehouse == null)
{
ProductOnWareHouse newproductOnWareHouse = new ProductOnWareHouse();
newproductOnWareHouse.PCode = r.PCode;
newproductOnWareHouse.WHCode = _WHCode;
Product p = serviceProduct.getByCode(r.PCode);
newproductOnWareHouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
newproductOnWareHouse.QtyWithUnit = u.QtyUnit;
int curnumber = r.NumberRecive * u.QtyUnit;
newproductOnWareHouse.RealQty = curnumber;
int tmp = serviceProductOnWarhose.CreateProductOnWareHouse(newproductOnWareHouse);
if (tmp > 0) {
Console.WriteLine("save newproductOnWareHouse Complate");
}
}
else {
Product p = serviceProduct.getByCode(r.PCode);
pOnwarehouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
pOnwarehouse.QtyWithUnit = u.QtyUnit;
int oldblance = pOnwarehouse.RealQty;
int curnumber = r.NumberRecive * u.QtyUnit;
int newbalnce = oldblance + curnumber;
pOnwarehouse.RealQty = newbalnce;
int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse);
if (tmp > 0)
{
Console.WriteLine("update pOnwarehouse Complate");
}
}
ProductWithCost productWithCost = serviceProductWithCost.getByPCodeAndCost(r.PCode,Convert.ToString(r.Cost));
if (productWithCost == null)
{
ProductWithCost newproductWithCost = new ProductWithCost();
newproductWithCost.PCode = r.PCode;
newproductWithCost.Cost = r.Cost;
newproductWithCost.RecordDate = DateTime.Today;
int tmp = serviceProductWithCost.CreateProductWithCost(newproductWithCost);
if (tmp > 0)
{
Console.WriteLine("save newproductWithCost Complate");
}
}
else {
productWithCost.PCode = r.PCode;
productWithCost.Cost = r.Cost;
productWithCost.RecordDate = DateTime.Today;
int tmp = serviceProductWithCost.UpdateProductWithCost(productWithCost);
if (tmp > 0)
{
Console.WriteLine("update productWithCost Complate");
}
}
ProductOnSupiler _productOnSupiler = servicePrductOnSupiler.getByPCodeAndSPCost(r.PCode, newReciveOrder.SPCode);
if (_productOnSupiler ==null)
{
ProductOnSupiler newProductOnSupiler = new ProductOnSupiler();
newProductOnSupiler.PCode = r.PCode;
newProductOnSupiler.SPCode = newReciveOrder.SPCode;
newProductOnSupiler.Cost = r.Cost;
newProductOnSupiler.RecordDate = DateTime.Today;
int tmp = servicePrductOnSupiler.CreateProductOnSupiler(newProductOnSupiler);
if (tmp > 0)
{
Console.WriteLine("save newProductOnSupiler Complate");
}
}
else {
_productOnSupiler.PCode = r.PCode;
_productOnSupiler.SPCode = newReciveOrder.SPCode;
_productOnSupiler.Cost = r.Cost;
_productOnSupiler.RecordDate = DateTime.Today;
int tmp = servicePrductOnSupiler.UpdateProductOnSuplier(_productOnSupiler);
if (tmp > 0)
{
Console.WriteLine("update _productOnSupiler Complate");
}
}
Product updateProduct = serviceProduct.getByCode(r.PCode);
updateProduct.LastCost = r.Cost;
int tmpProduct = serviceProduct.UpdateProduct(updateProduct);
if (tmpProduct > 0)
{
Console.WriteLine("update updateProduct Complate");
}
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public ProductWithCost getByPCodeAndCost(string _code,string _cost)
{
ProductWithCost productWithCost = null;
try
{
Conn = db.openConnAccess();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,PCode,Cost,RecordDate FROM tbProductWithCost ");
sb.Append(" WHERE (PCode='" + _code + "')");
sb.Append(" AND (Cost='" + _cost + "')");
string sql;
sql = sb.ToString();
com = new OleDbCommand();
com.CommandText = sql;
com.CommandType = CommandType.Text;
com.Connection = Conn;
dr = com.ExecuteReader();
if (dr.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dr);
int index = 1;
foreach (DataRow drw in dt.Rows)
{
productWithCost = new ProductWithCost();
productWithCost.ID = Convert.ToInt32(drw["ID"].ToString());
productWithCost.Index = Convert.ToString(index);
productWithCost.PCode = drw["PCode"].ToString();
productWithCost.Cost = Convert.ToDouble(drw["Cost"].ToString());
productWithCost.RecordDate =Convert.ToDateTime(drw["RecordDate"].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
Conn.Close();
return null;
throw ex;
}
finally
{
Conn.Close();
}
return productWithCost;
}
public int UpdateProductWithCost(ProductWithCost updateProductWithCost)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbProductWithCost ");
sb.Append(" SET Cost='" + updateProductWithCost.Cost + "',");
sb.Append(" RecordDate='" + updateProductWithCost.RecordDate + "'");
sb.Append(" WHERE (PCode='" + updateProductWithCost.PCode + "')");
sb.Append(" AND (Cost='" + updateProductWithCost.Cost + "')");
string sqlUpdate;
sqlUpdate = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlUpdate;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}