public int CreateProductOnWareHouse(ProductOnWareHouse newProductOnWareHouse)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbProductOnWareHouse(PCode,WHCode,UCode,QtyWithUnit,RealQty)");
sb.Append(" VALUES (@PCode,@WHCode,@UCode,@QtyWithUnit,@RealQty )");
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 = newProductOnWareHouse.PCode;
com.Parameters.Add("@WHCode", OleDbType.VarChar).Value = newProductOnWareHouse.WHCode;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newProductOnWareHouse.UCode;
com.Parameters.Add("@QtyWithUnit", OleDbType.VarChar).Value = newProductOnWareHouse.QtyWithUnit;
com.Parameters.Add("@RealQty", OleDbType.VarChar).Value = newProductOnWareHouse.RealQty;
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 ProductOnWareHouse getByPCodeAndWHCode(string _pCode, string _wHCode)
{
ProductOnWareHouse productOnWareHouse = null;
try
{
Conn = db.openConnAccess();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,PCode,WHCode,UCode,QtyWithUnit,RealQty FROM tbProductOnWareHouse ");
sb.Append(" WHERE (PCode='" + _pCode + "')");
sb.Append(" AND (WHCode='" + _wHCode + "')");
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)
{
productOnWareHouse = new ProductOnWareHouse();
productOnWareHouse.ID = Convert.ToInt32(drw["ID"].ToString());
productOnWareHouse.Index = Convert.ToString(index);
Product product = serviceProduct.getByCode(drw["PCode"].ToString());
productOnWareHouse.PCode = drw["PCode"].ToString();
productOnWareHouse.PName = product.PName;
WareHouse wareHouse = serviceWarehouse.getByCode(drw["WHCode"].ToString());
productOnWareHouse.WHCode = drw["WHCode"].ToString();
productOnWareHouse.WHName = wareHouse.WHName;
Unit unit = serviceUnit.getByCode(drw["UCode"].ToString());
productOnWareHouse.UCode = drw["UCode"].ToString();
productOnWareHouse.UName = unit.UName;
productOnWareHouse.QtyWithUnit =Convert.ToInt32(drw["QtyWithUnit"].ToString());
productOnWareHouse.RealQty =Convert.ToInt32(drw["RealQty"].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
Conn.Close();
return null;
throw ex;
}
finally
{
Conn.Close();
}
return productOnWareHouse;
}
public int UpdateProductOnWareHouse(ProductOnWareHouse updateProductOnWareHouse)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbProductOnWareHouse ");
sb.Append(" SET WHCode='" + updateProductOnWareHouse.WHCode + "',");
sb.Append(" UCode='" + updateProductOnWareHouse.UCode + "',");
sb.Append(" QtyWithUnit='" + updateProductOnWareHouse.QtyWithUnit+ "',");
sb.Append(" RealQty='" + updateProductOnWareHouse.RealQty+ "'");
sb.Append(" WHERE (PCode='" + updateProductOnWareHouse.PCode + "')");
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;
}