private void AddItemToInventory(long CreditMemoID)
{
CreditMemoDetails clsCreditMemoDetails = Details(CreditMemoID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
CreditMemoItems clsCreditMemoItems = new CreditMemoItems(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
System.Data.DataTable dt = clsCreditMemoItems.ListAsDataTable(CreditMemoID);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
/*******************************************
* Do not Add to Inventory coz this is a Debit Memo and no effect on inventory
* ****************************************/
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsCreditMemoDetails.PostingDate;
clsInventoryDetails.ReferenceNo = clsCreditMemoDetails.CNNo;
clsInventoryDetails.ContactID = clsCreditMemoDetails.CustomerID;
clsInventoryDetails.ContactCode = clsCreditMemoDetails.CustomerCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.SCreditQuantity = decQuantity;
clsInventoryDetails.SCreditCost = decItemCost - decVAT;
clsInventoryDetails.SCreditVAT = decItemCost; //Sales Return with VAT
clsInventory.Insert(clsInventoryDetails);
}
//myReader.Close();
}
public void Update(InventoryDetails Details)
{
try
{
string SQL = "UPDATE tblInventory SET " +
"BranchID = @BranchID, "+
"PostingDateFrom = @PostingDateFrom, "+
"PostingDateTo = @PostingDateTo, "+
"PostingDate = @PostingDate, "+
"ReferenceNo = @ReferenceNo, "+
"ContactID = @ContactID, "+
"ContactCode = @ContactCode, "+
"ProductID = @ProductID, "+
"VariationMatrixID = @VariationMatrixID, "+
"MatrixDescription = @MatrixDescription, "+
"ProductCode = @ProductCode, "+
"PurchaseQuantity = @PurchaseQuantity, "+
"PurchaseCost = @PurchaseCost, "+
"PurchaseVAT = @PurchaseVAT, "+
"PInvoiceQuantity = @PInvoiceQuantity, "+
"PInvoiceCost = @PInvoiceCost, "+
"PInvoiceVAT = @PInvoiceVAT, "+
"PReturnQuantity = @PReturnQuantity, "+
"PReturnCost = @PReturnCost, "+
"PReturnVAT = @PReturnVAT, "+
"PDebitQuantity = @PDebitQuantity, "+
"PDebitCost = @PDebitCost, "+
"PDebitVAT = @PDebitVAT, "+
"TransferInQuantity = @TransferInQuantity, "+
"TransferInCost = @TransferInCost, "+
"TransferInVAT = @TransferInVAT, "+
"TransferOutQuantity = @TransferOutQuantity, "+
"TransferOutCost = @TransferOutCost, "+
"TransferOutVAT = @TransferOutVAT, "+
"SoldQuantity = @SoldQuantity, "+
"SoldCost = @SoldCost, "+
"SoldVAT = @SoldVAT, "+
"SReturnQuantity = @SReturnQuantity, "+
"SReturnCost = @SReturnCost, "+
"SReturnVAT = @SReturnVAT, "+
"InvAdjustmentQuantity = @InvAdjustmentQuantity, "+
"InvAdjustmentCost = @InvAdjustmentCost, "+
"InvAdjustmentVAT = @InvAdjustmentVAT, "+
"ClosingQuantity = @ClosingQuantity, "+
"ClosingCost = @ClosingCost, "+
"ClosingVAT = @ClosingVAT "+
"WHERE InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", Details.BranchID);
cmd.Parameters.AddWithValue("@PostingDateFrom", Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDateTo", Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDate", Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@ReferenceNo", Details.ReferenceNo);
cmd.Parameters.AddWithValue("@ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("@ContactCode", Details.ContactCode);
cmd.Parameters.AddWithValue("@ProductID", Details.ProductID);
cmd.Parameters.AddWithValue("@ProductCode", Details.ProductCode);
cmd.Parameters.AddWithValue("@VariationMatrixID", Details.VariationMatrixID);
cmd.Parameters.AddWithValue("@MatrixDescription", Details.MatrixDescription);
cmd.Parameters.AddWithValue("@PurchaseQuantity", Details.PurchaseQuantity);
cmd.Parameters.AddWithValue("@PurchaseCost", Details.PurchaseCost);
cmd.Parameters.AddWithValue("@PurchaseVAT", Details.PurchaseVAT);
cmd.Parameters.AddWithValue("@PInvoiceQuantity", Details.PInvoiceQuantity);
cmd.Parameters.AddWithValue("@PInvoiceCost", Details.PInvoiceCost);
cmd.Parameters.AddWithValue("@PInvoiceVAT", Details.PInvoiceVAT);
cmd.Parameters.AddWithValue("@PReturnQuantity", Details.PReturnQuantity);
cmd.Parameters.AddWithValue("@PReturnCost", Details.PReturnCost);
cmd.Parameters.AddWithValue("@PReturnVAT", Details.PReturnVAT);
cmd.Parameters.AddWithValue("@PDebitQuantity", Details.PDebitQuantity);
cmd.Parameters.AddWithValue("@PDebitCost", Details.PDebitCost);
cmd.Parameters.AddWithValue("@PDebitVAT", Details.PDebitVAT);
cmd.Parameters.AddWithValue("@TransferInQuantity", Details.TransferInQuantity);
cmd.Parameters.AddWithValue("@TransferInCost", Details.TransferInCost);
cmd.Parameters.AddWithValue("@TransferInVAT", Details.TransferInVAT);
cmd.Parameters.AddWithValue("@TransferOutQuantity", Details.TransferOutQuantity);
cmd.Parameters.AddWithValue("@TransferOutCost", Details.TransferOutCost);
cmd.Parameters.AddWithValue("@TransferOutVAT", Details.TransferOutVAT);
cmd.Parameters.AddWithValue("@SoldQuantity", Details.SoldQuantity);
cmd.Parameters.AddWithValue("@SoldCost", Details.SoldCost);
cmd.Parameters.AddWithValue("@SoldVAT", Details.SoldVAT);
cmd.Parameters.AddWithValue("@SReturnQuantity", Details.SReturnQuantity);
cmd.Parameters.AddWithValue("@SReturnCost", Details.SReturnCost);
cmd.Parameters.AddWithValue("@SReturnVAT", Details.SReturnVAT);
cmd.Parameters.AddWithValue("@SCreditQuantity", Details.SCreditQuantity);
cmd.Parameters.AddWithValue("@SCreditCost", Details.SCreditCost);
cmd.Parameters.AddWithValue("@SCreditVAT", Details.SCreditVAT);
cmd.Parameters.AddWithValue("@InvAdjustmentQuantity", Details.InvAdjustmentQuantity);
cmd.Parameters.AddWithValue("@InvAdjustmentCost", Details.InvAdjustmentCost);
cmd.Parameters.AddWithValue("@InvAdjustmentVAT", Details.InvAdjustmentVAT);
cmd.Parameters.AddWithValue("@ClosingQuantity", Details.ClosingQuantity);
cmd.Parameters.AddWithValue("@ClosingCost", Details.ClosingCost);
cmd.Parameters.AddWithValue("@ClosingVAT", Details.ClosingVAT);
cmd.Parameters.AddWithValue("@InventoryID", Details.InventoryID);
base.ExecuteNonQuery(cmd);
//PO clsPO = new PO(base.Connection, base.Transaction);
//clsPO.SynchronizeAmount(Details.ReferenceNo);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void SubtractItemToInventory(long SOID)
{
SODetails clsSODetails = Details(SOID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
SOItem clsSOItem = new SOItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
ProductPackage clsProductPackage = new ProductPackage(base.Connection, base.Transaction);
MatrixPackage clsMatrixPackage = new MatrixPackage(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
//MatrixPackagePriceHistoryDetails clsMatrixPackagePriceHistoryDetails;
//ProductPackagePriceHistoryDetails clsProductPackagePriceHistoryDetails;
System.Data.DataTable dt = clsSOItem.ListAsDataTable(SOID, "SOItemID", SortOption.Ascending);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
///*******************************************
// * Add in the Sales Price History
// * ****************************************/
//if (lngVariationMatrixID != 0)
//{
// // Update MatrixPackagePriceHistory first to get the history
// clsMatrixPackagePriceHistoryDetails = new MatrixPackagePriceHistoryDetails();
// clsMatrixPackagePriceHistoryDetails.UID = clsSODetails.SellerID;
// clsMatrixPackagePriceHistoryDetails.PackageID = clsMatrixPackage.GetPackageID(lngVariationMatrixID, intProductUnitID);
// clsMatrixPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
// clsMatrixPackagePriceHistoryDetails.Price = decUnitCost;
// clsMatrixPackagePriceHistoryDetails.PurchasePrice = -1; //-1 = nochange
// clsMatrixPackagePriceHistoryDetails.VAT = -1; //-1 = nochange
// clsMatrixPackagePriceHistoryDetails.EVAT = -1; //-1 = nochange
// clsMatrixPackagePriceHistoryDetails.LocalTax = -1; //-1 = nochange
// clsMatrixPackagePriceHistoryDetails.Remarks = "Based on SO #: " + clsSODetails.SONo;
// MatrixPackagePriceHistory clsMatrixPackagePriceHistory = new MatrixPackagePriceHistory(base.Connection, base.Transaction);
// clsMatrixPackagePriceHistory.Insert(clsMatrixPackagePriceHistoryDetails);
//}
//else
//{
// // Update ProductPackagePriceHistory first to get the history
// clsProductPackagePriceHistoryDetails = new ProductPackagePriceHistoryDetails();
// clsProductPackagePriceHistoryDetails.UID = clsSODetails.SellerID;
// clsProductPackagePriceHistoryDetails.PackageID = clsProductPackage.GetPackageID(lngProductID, intProductUnitID);
// clsProductPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
// clsProductPackagePriceHistoryDetails.Price = decUnitCost;
// clsProductPackagePriceHistoryDetails.PurchasePrice = -1; //-1 = nochange
// clsProductPackagePriceHistoryDetails.VAT = -1; //-1 = nochange
// clsProductPackagePriceHistoryDetails.EVAT = -1; //-1 = nochange
// clsProductPackagePriceHistoryDetails.LocalTax = -1; //-1 = nochange
// clsProductPackagePriceHistoryDetails.Remarks = "Based on SO #: " + clsSODetails.SONo;
// ProductPackagePriceHistory clsProductPackagePriceHistory = new ProductPackagePriceHistory(base.Connection, base.Transaction);
// clsProductPackagePriceHistory.Insert(clsProductPackagePriceHistoryDetails);
//}
/*******************************************
* Subtract to Inventory
* ****************************************/
// clsProduct.SubtractQuantity(lngProductID, decQuantity);
// if (lngVariationMatrixID != 0) { clsProductVariationsMatrix.SubtractQuantity(lngVariationMatrixID, decQuantity); }
// July 28, 2011: change the above codes to the following
clsProduct.SubtractQuantity(clsSODetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.DEDUCT_SOLD_WHOLESALE), DateTime.Now, clsSODetails.SONo, clsSODetails.SellerName);
/*******************************************
* Update Selling Information
* ****************************************/
int iBaseUnitID = clsProduct.get_BaseUnitID(lngProductID);
if (iBaseUnitID != intProductUnitID)
{
clsProduct.UpdateSellingPrice(lngProductID, lngVariationMatrixID, clsSODetails.CustomerID, intProductUnitID, (decItemQuantity * decUnitCost) / decQuantity, -1, -1, -1,- 1, -1); // Price should be the sugegsted selling price
}
clsProduct.UpdateSellingWSPrice(lngProductID, lngVariationMatrixID, clsSODetails.CustomerID, intProductUnitID, decUnitCost); // WS Price should be the unit cost
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsSODetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsSODetails.SONo;
clsInventoryDetails.ContactID = clsSODetails.CustomerID;
clsInventoryDetails.ContactCode = clsSODetails.CustomerCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.SoldQuantity = decQuantity;
clsInventoryDetails.SoldCost = decItemCost - decVAT;
clsInventoryDetails.SoldVAT = decItemCost; // Sales Cost with VAT
clsInventory.Insert(clsInventoryDetails);
}
}
private void AddItemToInventory(long DebitMemoID)
{
POReturnDetails clsPOReturnDetails = Details(DebitMemoID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
POReturnItems clsPOReturnItems = new POReturnItems(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
//MySqlDataReader myReader = clsPOReturnItems.List(DebitMemoID, "DebitMemoItemID", SortOption.Ascending);
System.Data.DataTable dt = clsPOReturnItems.ListAsDataTable(DebitMemoID);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = Convert.ToInt64(dr["ProductID"]); // myReader.GetInt64("ProductID");
int intProductUnitID = Convert.ToInt16(dr["ProductUnitID"]); // myReader.GetInt16("ProductUnitID");
decimal decItemQuantity = Convert.ToDecimal(dr["Quantity"]); // myReader.GetDecimal("Quantity");
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = Convert.ToInt64(dr["VariationMatrixID"]); // myReader.GetInt64("VariationMatrixID");
string strMatrixDescription = dr["MatrixDescription"].ToString(); // "" + myReader["MatrixDescription"].ToString();
string strProductCode = dr["ProductCode"].ToString(); // "" + myReader["ProductCode"].ToString();
string strProductUnitCode = dr["ProductUnitCode"].ToString(); // "" + myReader["ProductUnitCode"].ToString();
decimal decUnitCost = Convert.ToDecimal(dr["UnitCost"]); // myReader.GetDecimal("UnitCost");
decimal decItemCost = Convert.ToDecimal(dr["Amount"]); // myReader.GetDecimal("Amount");
decimal decVAT = Convert.ToDecimal(dr["VAT"]); // myReader.GetDecimal("VAT");
/*******************************************
* Subtract from Inventory
* ****************************************/
// clsProduct.SubtractQuantity(ProductID, Quantity);
// if (VariationMatrixID != 0) { clsProductVariationsMatrix.SubtractQuantity(VariationMatrixID, Quantity);}
// July 26, 2011: change the above codes to the following
clsProduct.SubtractQuantity(clsPOReturnDetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.DEDUCT_PURCHASE_RETURN), DateTime.Now, clsPOReturnDetails.MemoNo, clsPOReturnDetails.PurchaserName);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsPOReturnDetails.PostingDate;
clsInventoryDetails.ReferenceNo = clsPOReturnDetails.MemoNo;
clsInventoryDetails.ContactID = clsPOReturnDetails.SupplierID;
clsInventoryDetails.ContactCode = clsPOReturnDetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.PReturnQuantity = decQuantity;
clsInventoryDetails.PReturnCost = decItemCost - decVAT;
clsInventoryDetails.PReturnVAT = decItemCost; //Purchase Return with VAT
clsInventory.Insert(clsInventoryDetails);
}
}
private void AddItemToInventory(long WBranchTransferID)
{
WBranchTransferDetails clsWBranchTransferDetails = Details(WBranchTransferID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
WBranchTransferItem clsWBranchTransferItem = new WBranchTransferItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
System.Data.DataTable dt = clsWBranchTransferItem.ListAsDataTable(WBranchTransferID, "WBranchTransferItemID", SortOption.Ascending);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = Int64.Parse(dr["ProductID"].ToString());
int intProductUnitID = Int16.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = Int64.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
string strProductUnitCode = "" + dr["ProductUnitCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
/*******************************************
* Subtract BranchIDFrom then Add to BranchIDTo -- Inventory
* ****************************************/
clsProduct.SubtractQuantity(clsWBranchTransferDetails.BranchIDFrom, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.DEDUCT_BRANCH_TRANSFER_FROM) + " " + clsWBranchTransferDetails.BranchCodeFrom + " @ " + decUnitCost.ToString("#,##0.#0") + "/" + strProductUnitCode, DateTime.Now, clsWBranchTransferDetails.WBranchTransferNo, clsWBranchTransferDetails.TransferrerName);
clsProduct.AddQuantity(clsWBranchTransferDetails.BranchIDTo, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_BRANCH_TRANSFER_TO) + " " + clsWBranchTransferDetails.BranchCodeTo + " @ " + decUnitCost.ToString("#,##0.#0") + "/" + strProductUnitCode, DateTime.Now, clsWBranchTransferDetails.WBranchTransferNo, clsWBranchTransferDetails.TransferrerName);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsWBranchTransferDetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsWBranchTransferDetails.WBranchTransferNo;
clsInventoryDetails.ContactID = Constants.C_RETAILPLUS_SUPPLIERID;
clsInventoryDetails.ContactCode = Constants.C_RETAILPLUS_SUPPLIER;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.PurchaseCost = decItemCost - decVAT;
clsInventoryDetails.PurchaseVAT = decItemCost; // Purchase Cost with VAT
// insert into branchid from with minus quantity
clsInventoryDetails.BranchID = clsWBranchTransferDetails.BranchIDFrom;
clsInventoryDetails.PurchaseQuantity = -decQuantity;
clsInventory.Insert(clsInventoryDetails);
// insert into branchid to with add quantity
clsInventoryDetails.BranchID = clsWBranchTransferDetails.BranchIDTo;
clsInventoryDetails.PurchaseQuantity = decQuantity;
clsInventory.Insert(clsInventoryDetails);
}
}
private void AddItemToInventory(long POID)
{
PODetails clsPODetails = Details(POID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
POItem clsPOItem = new POItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
ProductPackage clsProductPackage = new ProductPackage(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
ProductPackagePriceHistoryDetails clsProductPackagePriceHistoryDetails;
//MySqlDataReader myReader = clsPOItem.List(POID, "POItemID", SortOption.Ascending);
System.Data.DataTable dt = clsPOItem.ListAsDataTable(POID, "POItemID", SortOption.Ascending);
//while (myReader.Read())
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = Convert.ToInt64(dr["ProductID"]);
int intProductUnitID = Convert.ToInt16(dr["ProductUnitID"]);
decimal decItemQuantity = Convert.ToDecimal(dr["Quantity"]);
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = Convert.ToInt64(dr["VariationMatrixID"]);
string strMatrixDescription = dr["MatrixDescription"].ToString();
string strProductCode = dr["ProductCode"].ToString();
string strProductUnitCode = dr["ProductUnitCode"].ToString();
decimal decUnitCost = Convert.ToDecimal(dr["UnitCost"]);
decimal decItemCost = Convert.ToDecimal(dr["Amount"]);
decimal decSellingPrice = Convert.ToDecimal(dr["SellingPrice"]);
decimal decVAT = Convert.ToDecimal(dr["VAT"]);
decimal decEVAT = Convert.ToDecimal(dr["EVAT"]);
decimal decLocalTax = Convert.ToDecimal(dr["LocalTax"]);
/*******************************************
* Add in the Price History
* ****************************************/
// Update ProductPackagePriceHistory first to get the history
clsProductPackagePriceHistoryDetails = new ProductPackagePriceHistoryDetails();
clsProductPackagePriceHistoryDetails.UID = clsPODetails.PurchaserID;
clsProductPackagePriceHistoryDetails.PackageID = clsProductPackage.GetPackageID(lngProductID, intProductUnitID);
clsProductPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
clsProductPackagePriceHistoryDetails.PurchasePrice = (decItemQuantity * decUnitCost) / decQuantity;
clsProductPackagePriceHistoryDetails.Price = decSellingPrice;
clsProductPackagePriceHistoryDetails.VAT = decVAT;
clsProductPackagePriceHistoryDetails.EVAT = decEVAT;
clsProductPackagePriceHistoryDetails.LocalTax = decLocalTax;
clsProductPackagePriceHistoryDetails.Remarks = "Based on PO #: " + clsPODetails.PONo;
ProductPackagePriceHistory clsProductPackagePriceHistory = new ProductPackagePriceHistory(base.Connection, base.Transaction);
clsProductPackagePriceHistory.Insert(clsProductPackagePriceHistoryDetails);
/*******************************************
* Add to Inventory
* ****************************************/
//clsProduct.AddQuantity(lngProductID, decQuantity);
//if (lngVariationMatrixID != 0) { clsProductVariationsMatrix.AddQuantity(lngVariationMatrixID, decQuantity); }
// July 26, 2011: change the above codes to the following
clsProduct.AddQuantity(clsPODetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_PURCHASE) + " @ " + decUnitCost.ToString("#,##0.#0") + "/" + strProductUnitCode, DateTime.Now, clsPODetails.PONo, clsPODetails.PurchaserName);
/*******************************************
* Update Purchasing Information
*
* 30May2013 Include variation in the package.
* ****************************************/
int iBaseUnitID = clsProduct.get_BaseUnitID(lngProductID);
if (iBaseUnitID != intProductUnitID)
{
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsPODetails.SupplierID, iBaseUnitID, (decItemQuantity * decUnitCost) / decQuantity);
}
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsPODetails.SupplierID, intProductUnitID, decUnitCost);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.BranchID = clsPODetails.BranchID;
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsPODetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsPODetails.PONo;
clsInventoryDetails.ContactID = clsPODetails.SupplierID;
clsInventoryDetails.ContactCode = clsPODetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.PurchaseQuantity = decQuantity;
clsInventoryDetails.PurchaseCost = decItemCost - decVAT;
clsInventoryDetails.PurchaseVAT = decItemCost; // Purchase Cost with VAT
clsInventory.Insert(clsInventoryDetails);
/*******************************************
* Added Jan 1, 2010 4:20PM
* Update Selling Information when PO is posted
* ****************************************/
clsProduct.UpdateSellingPrice(lngProductID, lngVariationMatrixID, clsPODetails.SupplierID, intProductUnitID, Convert.ToDecimal(dr["SellingPrice"]), -1, -1, -1, -1, -1);
/*******************************************
* Added Mar 8, 2010 4:20PM
* Update the purchase price history to check who has the lowest price.
* ****************************************/
ProductPurchasePriceHistoryDetails clsProductPurchasePriceHistoryDetails = new ProductPurchasePriceHistoryDetails();
clsProductPurchasePriceHistoryDetails.ProductID = lngProductID;
clsProductPurchasePriceHistoryDetails.MatrixID = lngVariationMatrixID;
clsProductPurchasePriceHistoryDetails.SupplierID = clsPODetails.SupplierID;
clsProductPurchasePriceHistoryDetails.PurchasePrice = decUnitCost;
clsProductPurchasePriceHistoryDetails.PurchaseDate = clsPODetails.PODate;
clsProductPurchasePriceHistoryDetails.Remarks = clsPODetails.PONo;
clsProductPurchasePriceHistoryDetails.PurchaserName = clsPODetails.PurchaserName;
ProductPurchasePriceHistory clsProductPurchasePriceHistory = new ProductPurchasePriceHistory(base.Connection, base.Transaction);
clsProductPurchasePriceHistory.AddToList(clsProductPurchasePriceHistoryDetails);
}
//myReader.Close();
}
private void SubtractItemFromInventory(long TransferOutID)
{
TransferOutDetails clsTransferOutDetails = Details(TransferOutID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
TransferOutItem clsTransferOutItem = new TransferOutItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
ProductPackage clsProductPackage = new ProductPackage(base.Connection, base.Transaction);
MatrixPackage clsMatrixPackage = new MatrixPackage(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
//MatrixPackagePriceHistoryDetails clsMatrixPackagePriceHistoryDetails;
//ProductPackagePriceHistoryDetails clsProductPackagePriceHistoryDetails;
System.Data.DataTable dt = clsTransferOutItem.ListAsDataTable(TransferOutID, "TransferOutItemID", SortOption.Ascending);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
///*******************************************
// * Add in the Purchase Price History
// * ****************************************/
//if (lngVariationMatrixID != 0)
//{
// // Update MatrixPackagePriceHistory first to get the history
// clsMatrixPackagePriceHistoryDetails = new MatrixPackagePriceHistoryDetails();
// clsMatrixPackagePriceHistoryDetails.UID = clsTransferOutDetails.TransferrerID;
// clsMatrixPackagePriceHistoryDetails.PackageID = clsMatrixPackage.GetPackageID(lngVariationMatrixID, intProductUnitID);
// clsMatrixPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
// clsMatrixPackagePriceHistoryDetails.PurchasePrice = (decItemQuantity * decUnitCost) / decQuantity;
// clsMatrixPackagePriceHistoryDetails.Price = -1;
// clsMatrixPackagePriceHistoryDetails.VAT = -1;
// clsMatrixPackagePriceHistoryDetails.EVAT = -1;
// clsMatrixPackagePriceHistoryDetails.LocalTax = -1;
// clsMatrixPackagePriceHistoryDetails.Remarks = "Based on TransferOut #: " + clsTransferOutDetails.TransferOutNo;
// MatrixPackagePriceHistory clsMatrixPackagePriceHistory = new MatrixPackagePriceHistory(base.Connection, base.Transaction);
// clsMatrixPackagePriceHistory.Insert(clsMatrixPackagePriceHistoryDetails);
//}
//else
//{
// // Update ProductPackagePriceHistory first to get the history
// clsProductPackagePriceHistoryDetails = new ProductPackagePriceHistoryDetails();
// clsProductPackagePriceHistoryDetails.UID = clsTransferOutDetails.TransferrerID;
// clsProductPackagePriceHistoryDetails.PackageID = clsProductPackage.GetPackageID(lngProductID, intProductUnitID);
// clsProductPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
// clsProductPackagePriceHistoryDetails.PurchasePrice = (decItemQuantity * decUnitCost) / decQuantity;
// clsProductPackagePriceHistoryDetails.Price = -1;
// clsProductPackagePriceHistoryDetails.VAT = -1;
// clsProductPackagePriceHistoryDetails.EVAT = -1;
// clsProductPackagePriceHistoryDetails.LocalTax = -1;
// clsProductPackagePriceHistoryDetails.Remarks = "Based on TransferOut #: " + clsTransferOutDetails.TransferOutNo;
// ProductPackagePriceHistory clsProductPackagePriceHistory = new ProductPackagePriceHistory(base.Connection, base.Transaction);
// clsProductPackagePriceHistory.Insert(clsProductPackagePriceHistoryDetails);
//}
/*******************************************
* Add to Inventory
* ****************************************/
//clsProduct.AddQuantity(lngProductID, decQuantity);
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.AddQuantity(lngVariationMatrixID, decQuantity);
//}
// July 26, 2011: change the above codes to the following
clsProduct.SubtractQuantity(clsTransferOutDetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.DEDUCT_TRANSFER_OUT), DateTime.Now, clsTransferOutDetails.TransferOutNo, clsTransferOutDetails.TransferrerName);
///*******************************************
// * Update Purchasing Information
// * ****************************************/
//clsProduct.UpdatePurchasing(lngProductID, clsTransferOutDetails.SupplierID, intProductUnitID, (decItemQuantity * decUnitCost) / decQuantity);
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.UpdatePurchasing(lngVariationMatrixID, clsTransferOutDetails.SupplierID, intProductUnitID, (decItemQuantity * decUnitCost) / decQuantity);
//}
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsTransferOutDetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsTransferOutDetails.TransferOutNo;
clsInventoryDetails.ContactID = clsTransferOutDetails.SupplierID;
clsInventoryDetails.ContactCode = clsTransferOutDetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.TransferOutQuantity = decQuantity;
clsInventoryDetails.TransferOutCost = decItemCost - decVAT;
clsInventoryDetails.TransferOutVAT = decItemCost; // TransferOut Cost with VAT
clsInventory.Insert(clsInventoryDetails);
/*******************************************
* Added April 28, 2010 4:20PM
* Update Selling Information when TransferOut is posted
* ****************************************/
//clsProduct.UpdateSellingIncludingAllMatrixWithSameQuantityAndUnit(lngProductID, clsTransferOutDetails.SupplierID, intProductUnitID, decimal.Parse(myReader["SellingPrice");
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.UpdateSellingWithSameQuantityAndUnit(lngVariationMatrixID, clsPODetails.SupplierID, intProductUnitID, decimal.Parse(myReader["SellingPrice");
//}
/*******************************************
* Added April 28, 2010 4:20PM
* Update the purchase price history to check who has the lowest price.
* ****************************************/
//ProductPurchasePriceHistoryDetails clsProductPurchasePriceHistoryDetails = new ProductPurchasePriceHistoryDetails();
//clsProductPurchasePriceHistoryDetails.ProductID = lngProductID;
//clsProductPurchasePriceHistoryDetails.MatrixID = lngVariationMatrixID;
//clsProductPurchasePriceHistoryDetails.SupplierID = clsTransferOutDetails.SupplierID;
//clsProductPurchasePriceHistoryDetails.PurchasePrice = decUnitCost;
//clsProductPurchasePriceHistoryDetails.PurchaseDate = clsTransferOutDetails.TransferOutDate;
//clsProductPurchasePriceHistoryDetails.Remarks = clsTransferOutDetails.TransferOutNo;
//ProductPurchasePriceHistory clsProductPurchasePriceHistory = new ProductPurchasePriceHistory(base.Connection, base.Transaction);
//clsProductPurchasePriceHistory.AddToList(clsProductPurchasePriceHistoryDetails);
}
}
public long Insert(InventoryDetails Details)
{
try
{
string SQL = "INSERT INTO tblInventory (" +
"BranchID, " +
"PostingDateFrom, " +
"PostingDateTo, " +
"PostingDate, " +
"ReferenceNo, " +
"ContactID, " +
"ContactCode, " +
"ProductID, " +
"ProductCode, " +
"VariationMatrixID, " +
"MatrixDescription, " +
"PurchaseQuantity, " +
"PurchaseCost, " +
"PurchaseVAT, " +
"PInvoiceQuantity, " +
"PInvoiceCost, " +
"PInvoiceVAT, " +
"PReturnQuantity, " +
"PReturnCost, " +
"PReturnVAT, " +
"PDebitQuantity, " +
"PDebitCost, " +
"PDebitVAT, " +
"TransferInQuantity, " +
"TransferInCost, " +
"TransferInVAT, " +
"TransferOutQuantity, " +
"TransferOutCost," +
"TransferOutVAT," +
"SoldQuantity, " +
"SoldCost, " +
"SoldVAT, " +
"SReturnQuantity, " +
"SReturnCost, " +
"SReturnVAT, " +
"SCreditQuantity, " +
"SCreditCost, " +
"SCreditVAT, " +
"InvAdjustmentQuantity, " +
"InvAdjustmentCost, " +
"InvAdjustmentVAT, " +
"ClosingQuantity, " +
"ClosingActualQuantity, " +
"ClosingCost, " +
"ClosingVAT," +
"PurchasePrice" +
") VALUES (" +
"@BranchID, " +
"@PostingDateFrom, " +
"@PostingDateTo, " +
"@PostingDate, " +
"@ReferenceNo, " +
"@ContactID, " +
"@ContactCode, " +
"@ProductID, " +
"@ProductCode, " +
"@VariationMatrixID, " +
"@MatrixDescription, " +
"@PurchaseQuantity, " +
"@PurchaseCost, " +
"@PurchaseVAT, " +
"@PInvoiceQuantity, " +
"@PInvoiceCost, " +
"@PInvoiceVAT, " +
"@PReturnQuantity, " +
"@PReturnCost, " +
"@PReturnVAT, " +
"@PDebitQuantity, " +
"@PDebitCost, " +
"@PDebitVAT, " +
"@TransferInQuantity, " +
"@TransferInCost, " +
"@TransferInVAT, " +
"@TransferOutQuantity, " +
"@TransferOutCost," +
"@TransferOutVAT," +
"@SoldQuantity, " +
"@SoldCost, " +
"@SoldVAT, " +
"@SReturnQuantity, " +
"@SReturnCost, " +
"@SReturnVAT, " +
"@SCreditQuantity, " +
"@SCreditCost, " +
"@SCreditVAT, " +
"@InvAdjustmentQuantity, " +
"@InvAdjustmentCost, " +
"@InvAdjustmentVAT, " +
"@ClosingQuantity, " +
"@ClosingActualQuantity, " +
"@ClosingCost, " +
"@ClosingVAT," +
"@PurchasePrice" +
");";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", Details.BranchID);
cmd.Parameters.AddWithValue("@PostingDateFrom", Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDateTo", Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDate", Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@ReferenceNo", Details.ReferenceNo);
cmd.Parameters.AddWithValue("@ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("@ContactCode", Details.ContactCode);
cmd.Parameters.AddWithValue("@ProductID", Details.ProductID);
cmd.Parameters.AddWithValue("@ProductCode", Details.ProductCode);
cmd.Parameters.AddWithValue("@VariationMatrixID", Details.VariationMatrixID);
cmd.Parameters.AddWithValue("@MatrixDescription", Details.MatrixDescription);
cmd.Parameters.AddWithValue("@PurchaseQuantity", Details.PurchaseQuantity);
cmd.Parameters.AddWithValue("@PurchaseCost", Details.PurchaseCost);
cmd.Parameters.AddWithValue("@PurchaseVAT", Details.PurchaseVAT);
cmd.Parameters.AddWithValue("@PInvoiceQuantity", Details.PInvoiceQuantity);
cmd.Parameters.AddWithValue("@PInvoiceCost", Details.PInvoiceCost);
cmd.Parameters.AddWithValue("@PInvoiceVAT", Details.PInvoiceVAT);
cmd.Parameters.AddWithValue("@PReturnQuantity", Details.PReturnQuantity);
cmd.Parameters.AddWithValue("@PReturnCost", Details.PReturnCost);
cmd.Parameters.AddWithValue("@PReturnVAT", Details.PReturnVAT);
cmd.Parameters.AddWithValue("@PDebitQuantity", Details.PDebitQuantity);
cmd.Parameters.AddWithValue("@PDebitCost", Details.PDebitCost);
cmd.Parameters.AddWithValue("@PDebitVAT", Details.PDebitVAT);
cmd.Parameters.AddWithValue("@TransferInQuantity", Details.TransferInQuantity);
cmd.Parameters.AddWithValue("@TransferInCost", Details.TransferInCost);
cmd.Parameters.AddWithValue("@TransferInVAT", Details.TransferInVAT);
cmd.Parameters.AddWithValue("@TransferOutQuantity", Details.TransferOutQuantity);
cmd.Parameters.AddWithValue("@TransferOutCost", Details.TransferOutCost);
cmd.Parameters.AddWithValue("@TransferOutVAT", Details.TransferOutVAT);
cmd.Parameters.AddWithValue("@SoldQuantity", Details.SoldQuantity);
cmd.Parameters.AddWithValue("@SoldCost", Details.SoldCost);
cmd.Parameters.AddWithValue("@SoldVAT", Details.SoldVAT);
cmd.Parameters.AddWithValue("@SReturnQuantity", Details.SReturnQuantity);
cmd.Parameters.AddWithValue("@SReturnCost", Details.SReturnCost);
cmd.Parameters.AddWithValue("@SReturnVAT", Details.SReturnVAT);
cmd.Parameters.AddWithValue("@SCreditQuantity", Details.SCreditQuantity);
cmd.Parameters.AddWithValue("@SCreditCost", Details.SCreditCost);
cmd.Parameters.AddWithValue("@SCreditVAT", Details.SCreditVAT);
cmd.Parameters.AddWithValue("@InvAdjustmentQuantity", Details.InvAdjustmentQuantity);
cmd.Parameters.AddWithValue("@InvAdjustmentCost", Details.InvAdjustmentCost);
cmd.Parameters.AddWithValue("@InvAdjustmentVAT", Details.InvAdjustmentVAT);
cmd.Parameters.AddWithValue("@ClosingQuantity", Details.ClosingQuantity);
cmd.Parameters.AddWithValue("@ClosingActualQuantity", Details.ClosingActualQuantity);
cmd.Parameters.AddWithValue("@ClosingCost", Details.ClosingCost);
cmd.Parameters.AddWithValue("@ClosingVAT", Details.ClosingVAT);
cmd.Parameters.AddWithValue("@PurchasePrice", Details.PurchasePrice);
base.ExecuteNonQuery(cmd);
SQL = "SELECT LAST_INSERT_ID();";
cmd.Parameters.Clear();
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
Int64 iID = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
iID = Int64.Parse(dr[0].ToString());
}
return(iID);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public InventoryDetails Details(long InventoryID)
{
try
{
string SQL= SQLSelect() + "AND a.InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmInventoryID = new MySqlParameter("@InventoryID",MySqlDbType.Int64);
prmInventoryID.Value = InventoryID;
cmd.Parameters.Add(prmInventoryID);
MySqlDataReader myReader = base.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
InventoryDetails Details = new InventoryDetails();
while (myReader.Read())
{
Details.BranchID = myReader.GetInt32("BranchID");
Details.InventoryID = myReader.GetInt64("InventoryID");
Details.PostingDateFrom = myReader.GetDateTime("PostingDateFrom");
Details.PostingDateTo = myReader.GetDateTime("PostingDateTo");
Details.PostingDate = myReader.GetDateTime("PostingDate");
Details.ReferenceNo = "" + myReader["ReferenceNo"].ToString();
Details.ContactID = myReader.GetInt64("ContactID");
Details.ContactCode = "" + myReader["ContactCode"].ToString();
Details.ProductID = myReader.GetInt64("ProductID");
Details.ProductCode = "" + myReader["ProductCode"].ToString();
Details.VariationMatrixID = myReader.GetInt64("VariationMatrixID");
Details.MatrixDescription = "" + myReader["MatrixDescription"].ToString();
Details.PurchaseQuantity = myReader.GetDecimal("PurchaseQuantity");
Details.PurchaseCost = myReader.GetDecimal("PurchaseCost");
Details.PurchaseVAT = myReader.GetDecimal("PurchaseVAT");
Details.PReturnQuantity = myReader.GetDecimal("PReturnQuantity");
Details.PReturnCost = myReader.GetDecimal("PReturnCost");
Details.PReturnVAT = myReader.GetDecimal("PReturnVAT");
Details.TransferInQuantity = myReader.GetDecimal("TransferInQuantity");
Details.TransferInCost = myReader.GetDecimal("TransferInCost");
Details.TransferInVAT = myReader.GetDecimal("TransferInVAT");
Details.TransferOutQuantity = myReader.GetDecimal("TransferOutQuantity");
Details.TransferOutCost = myReader.GetDecimal("TransferOutCost");
Details.TransferOutVAT = myReader.GetDecimal("TransferOutVAT");
Details.SoldQuantity = myReader.GetDecimal("SoldQuantity");
Details.SoldCost = myReader.GetDecimal("SoldCost");
Details.SoldVAT = myReader.GetDecimal("SoldVAT");
Details.SReturnQuantity = myReader.GetDecimal("SReturnQuantity");
Details.SReturnCost = myReader.GetDecimal("SReturnCost");
Details.SReturnVAT = myReader.GetDecimal("SReturnVAT");
Details.SCreditQuantity = myReader.GetDecimal("SCreditQuantity");
Details.SCreditCost = myReader.GetDecimal("SCreditCost");
Details.SCreditVAT = myReader.GetDecimal("SCreditVAT");
}
myReader.Close();
return Details;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void UpdateSales(InventoryDetails Details)
{
try
{
string SQL= "UPDATE tblInventory SET " +
"PostingDateFrom = @PostingDateFrom, " +
"PostingDateTo = @PostingDateTo, " +
"PostingDate = @PostingDate, " +
"ReferenceNo = @ReferenceNo, " +
"ContactID = @ContactID, " +
"ContactCode = @ContactCode, " +
"ProductID = @ProductID, " +
"VariationMatrixID = @VariationMatrixID, " +
"MatrixDescription = @MatrixDescription, " +
"ProductCode = @ProductCode, " +
"SoldQuantity = @SoldQuantity, " +
"SoldCost = @SoldCost, " +
"SoldVAT = @SoldVAT " +
"WHERE InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmPostingDateFrom = new MySqlParameter("@PostingDateFrom",MySqlDbType.DateTime);
prmPostingDateFrom.Value = Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDateFrom);
MySqlParameter prmPostingDateTo = new MySqlParameter("@PostingDateTo",MySqlDbType.DateTime);
prmPostingDateTo.Value = Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDateTo);
MySqlParameter prmPostingDate = new MySqlParameter("@PostingDate",MySqlDbType.DateTime);
prmPostingDate.Value = Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDate);
MySqlParameter prmReferenceNo = new MySqlParameter("@ReferenceNo",MySqlDbType.String);
prmReferenceNo.Value = Details.ReferenceNo;
cmd.Parameters.Add(prmReferenceNo);
MySqlParameter prmContactID = new MySqlParameter("@ContactID",MySqlDbType.Int64);
prmContactID.Value = Details.ContactID;
cmd.Parameters.Add(prmContactID);
MySqlParameter prmContactCode = new MySqlParameter("@ContactCode",MySqlDbType.String);
prmContactCode.Value = Details.ContactCode;
cmd.Parameters.Add(prmContactCode);
MySqlParameter prmProductID = new MySqlParameter("@ProductID",MySqlDbType.Int64);
prmProductID.Value = Details.ProductID;
cmd.Parameters.Add(prmProductID);
MySqlParameter prmProductCode = new MySqlParameter("@ProductCode",MySqlDbType.String);
prmProductCode.Value = Details.ProductCode;
cmd.Parameters.Add(prmProductCode);
MySqlParameter prmVariationMatrixID = new MySqlParameter("@VariationMatrixID",MySqlDbType.Int64);
prmVariationMatrixID.Value = Details.VariationMatrixID;
cmd.Parameters.Add(prmVariationMatrixID);
MySqlParameter prmMatrixDescription = new MySqlParameter("@MatrixDescription",MySqlDbType.String);
prmMatrixDescription.Value = Details.MatrixDescription;
cmd.Parameters.Add(prmMatrixDescription);
MySqlParameter prmSoldQuantity = new MySqlParameter("@SoldQuantity",MySqlDbType.Decimal);
prmSoldQuantity.Value = Details.SoldQuantity;
cmd.Parameters.Add(prmSoldQuantity);
MySqlParameter prmSoldCost = new MySqlParameter("@SoldCost",MySqlDbType.Decimal);
prmSoldCost.Value = Details.SoldCost;
cmd.Parameters.Add(prmSoldCost);
MySqlParameter prmSoldVAT = new MySqlParameter("@SoldVAT",MySqlDbType.Decimal);
prmSoldVAT.Value = Details.SoldVAT;
cmd.Parameters.Add(prmSoldVAT);
MySqlParameter prmInventoryID = new MySqlParameter("@InventoryID",MySqlDbType.Int64);
prmInventoryID.Value = Details.InventoryID;
cmd.Parameters.Add(prmInventoryID);
base.ExecuteNonQuery(cmd);
//PO clsPO = new PO(base.Connection, base.Transaction);
//clsPO.SynchronizeAmount(Details.ReferenceNo);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void Update(InventoryDetails Details)
{
try
{
string SQL= "UPDATE tblInventory SET " +
"BranchID = @BranchID, " +
"PostingDateFrom = @PostingDateFrom, " +
"PostingDateTo = @PostingDateTo, " +
"PostingDate = @PostingDate, " +
"ReferenceNo = @ReferenceNo, " +
"ContactID = @ContactID, " +
"ContactCode = @ContactCode, " +
"ProductID = @ProductID, " +
"VariationMatrixID = @VariationMatrixID, " +
"MatrixDescription = @MatrixDescription, " +
"ProductCode = @ProductCode, " +
"PurchaseQuantity = @PurchaseQuantity, " +
"PurchaseCost = @PurchaseCost, " +
"PurchaseVAT = @PurchaseVAT, " +
"PInvoiceQuantity = @PInvoiceQuantity, " +
"PInvoiceCost = @PInvoiceCost, " +
"PInvoiceVAT = @PInvoiceVAT, " +
"PReturnQuantity = @PReturnQuantity, " +
"PReturnCost = @PReturnCost, " +
"PReturnVAT = @PReturnVAT, " +
"PDebitQuantity = @PDebitQuantity, " +
"PDebitCost = @PDebitCost, " +
"PDebitVAT = @PDebitVAT, " +
"TransferInQuantity = @TransferInQuantity, " +
"TransferInCost = @TransferInCost, " +
"TransferInVAT = @TransferInVAT, " +
"TransferOutQuantity = @TransferOutQuantity, " +
"TransferOutCost = @TransferOutCost, " +
"TransferOutVAT = @TransferOutVAT, " +
"SoldQuantity = @SoldQuantity, " +
"SoldCost = @SoldCost, " +
"SoldVAT = @SoldVAT, " +
"SReturnQuantity = @SReturnQuantity, " +
"SReturnCost = @SReturnCost, " +
"SReturnVAT = @SReturnVAT, " +
"InvAdjustmentQuantity = @InvAdjustmentQuantity, " +
"InvAdjustmentCost = @InvAdjustmentCost, " +
"InvAdjustmentVAT = @InvAdjustmentVAT, " +
"ClosingQuantity = @ClosingQuantity, " +
"ClosingCost = @ClosingCost, " +
"ClosingVAT = @ClosingVAT " +
"WHERE InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", Details.BranchID);
cmd.Parameters.AddWithValue("@PostingDateFrom", Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDateTo", Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDate", Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@ReferenceNo", Details.ReferenceNo);
cmd.Parameters.AddWithValue("@ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("@ContactCode", Details.ContactCode);
cmd.Parameters.AddWithValue("@ProductID", Details.ProductID);
cmd.Parameters.AddWithValue("@ProductCode", Details.ProductCode);
cmd.Parameters.AddWithValue("@VariationMatrixID", Details.VariationMatrixID);
cmd.Parameters.AddWithValue("@MatrixDescription", Details.MatrixDescription);
cmd.Parameters.AddWithValue("@PurchaseQuantity", Details.PurchaseQuantity);
cmd.Parameters.AddWithValue("@PurchaseCost", Details.PurchaseCost);
cmd.Parameters.AddWithValue("@PurchaseVAT", Details.PurchaseVAT);
cmd.Parameters.AddWithValue("@PInvoiceQuantity", Details.PInvoiceQuantity);
cmd.Parameters.AddWithValue("@PInvoiceCost", Details.PInvoiceCost);
cmd.Parameters.AddWithValue("@PInvoiceVAT", Details.PInvoiceVAT);
cmd.Parameters.AddWithValue("@PReturnQuantity", Details.PReturnQuantity);
cmd.Parameters.AddWithValue("@PReturnCost", Details.PReturnCost);
cmd.Parameters.AddWithValue("@PReturnVAT", Details.PReturnVAT);
cmd.Parameters.AddWithValue("@PDebitQuantity", Details.PDebitQuantity);
cmd.Parameters.AddWithValue("@PDebitCost", Details.PDebitCost);
cmd.Parameters.AddWithValue("@PDebitVAT", Details.PDebitVAT);
cmd.Parameters.AddWithValue("@TransferInQuantity", Details.TransferInQuantity);
cmd.Parameters.AddWithValue("@TransferInCost", Details.TransferInCost);
cmd.Parameters.AddWithValue("@TransferInVAT", Details.TransferInVAT);
cmd.Parameters.AddWithValue("@TransferOutQuantity", Details.TransferOutQuantity);
cmd.Parameters.AddWithValue("@TransferOutCost", Details.TransferOutCost);
cmd.Parameters.AddWithValue("@TransferOutVAT", Details.TransferOutVAT);
cmd.Parameters.AddWithValue("@SoldQuantity", Details.SoldQuantity);
cmd.Parameters.AddWithValue("@SoldCost", Details.SoldCost);
cmd.Parameters.AddWithValue("@SoldVAT", Details.SoldVAT);
cmd.Parameters.AddWithValue("@SReturnQuantity", Details.SReturnQuantity);
cmd.Parameters.AddWithValue("@SReturnCost", Details.SReturnCost);
cmd.Parameters.AddWithValue("@SReturnVAT", Details.SReturnVAT);
cmd.Parameters.AddWithValue("@SCreditQuantity", Details.SCreditQuantity);
cmd.Parameters.AddWithValue("@SCreditCost", Details.SCreditCost);
cmd.Parameters.AddWithValue("@SCreditVAT", Details.SCreditVAT);
cmd.Parameters.AddWithValue("@InvAdjustmentQuantity", Details.InvAdjustmentQuantity);
cmd.Parameters.AddWithValue("@InvAdjustmentCost", Details.InvAdjustmentCost);
cmd.Parameters.AddWithValue("@InvAdjustmentVAT", Details.InvAdjustmentVAT);
cmd.Parameters.AddWithValue("@ClosingQuantity", Details.ClosingQuantity);
cmd.Parameters.AddWithValue("@ClosingCost", Details.ClosingCost);
cmd.Parameters.AddWithValue("@ClosingVAT", Details.ClosingVAT);
cmd.Parameters.AddWithValue("@InventoryID", Details.InventoryID);
base.ExecuteNonQuery(cmd);
//PO clsPO = new PO(base.Connection, base.Transaction);
//clsPO.SynchronizeAmount(Details.ReferenceNo);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void AddItemToInventory(long ClosingID)
{
ClosingDetails clsClosingDetails = Details(ClosingID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
ClosingItem clsClosingItem = new ClosingItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
MySqlDataReader myReader = clsClosingItem.List(ClosingID, "ClosingItemID", SortOption.Ascending);
while (myReader.Read())
{
long lngProductID = myReader.GetInt64("ProductID");
int intProductUnitID = myReader.GetInt16("ProductUnitID");
decimal decItemQuantity = myReader.GetDecimal("Quantity");
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngMatrixID = myReader.GetInt64("VariationMatrixID");
string strMatrixDescription = "" + myReader["MatrixDescription"].ToString();
string strProductCode = "" + myReader["ProductCode"].ToString();
decimal decUnitCost = myReader.GetDecimal("UnitCost");
decimal decItemCost = myReader.GetDecimal("Amount");
decimal decVAT = myReader.GetDecimal("VAT");
/*******************************************
* Update Purchasing Information
* ****************************************/
clsProduct.UpdatePurchasing(lngProductID, lngMatrixID, clsClosingDetails.SupplierID, intProductUnitID, (decItemQuantity * decUnitCost) / decQuantity);
///*******************************************
// * Add to Inventory
// * ****************************************/
//clsProduct.AddQuantity(ProductID, Quantity);
//if (VariationMatrixID != 0)
//{ clsProductVariationsMatrix.AddQuantity(VariationMatrixID, Quantity);}
// July 26, 2011: change the above codes to the following
clsProduct.AddQuantity(clsClosingDetails.BranchID, lngProductID, lngMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_PURCHASE) + " @ " + decUnitCost.ToString("#,##0.#0"), DateTime.Now, clsClosingDetails.ClosingNo, clsClosingDetails.TransferredByID.ToString());
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsClosingDetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsClosingDetails.ClosingNo;
clsInventoryDetails.ContactID = clsClosingDetails.SupplierID;
clsInventoryDetails.ContactCode = clsClosingDetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.ClosingQuantity = decQuantity;
clsInventoryDetails.ClosingCost = decItemCost - decVAT;
clsInventoryDetails.ClosingVAT = decItemCost; // Closing Cost with VAT
clsInventory.Insert(clsInventoryDetails);
}
myReader.Close();
}
private void AddItemToInventory(long WBranchTransferID)
{
WBranchTransferDetails clsWBranchTransferDetails = Details(WBranchTransferID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
WBranchTransferItem clsWBranchTransferItem = new WBranchTransferItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
System.Data.DataTable dt = clsWBranchTransferItem.ListAsDataTable(WBranchTransferID, "WBranchTransferItemID", SortOption.Ascending);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = Int64.Parse(dr["ProductID"].ToString());
int intProductUnitID = Int16.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = Int64.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
string strProductUnitCode = "" + dr["ProductUnitCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
/*******************************************
* Subtract BranchIDFrom then Add to BranchIDTo -- Inventory
* ****************************************/
clsProduct.SubtractQuantity(clsWBranchTransferDetails.BranchIDFrom, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.DEDUCT_BRANCH_TRANSFER_FROM) + " " + clsWBranchTransferDetails.BranchCodeFrom + " @ " + decUnitCost.ToString("#,##0.#0") + "/" + strProductUnitCode, DateTime.Now, clsWBranchTransferDetails.WBranchTransferNo, clsWBranchTransferDetails.TransferrerName);
clsProduct.AddQuantity(clsWBranchTransferDetails.BranchIDTo, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_BRANCH_TRANSFER_TO) + " " + clsWBranchTransferDetails.BranchCodeTo + " @ " + decUnitCost.ToString("#,##0.#0") + "/" + strProductUnitCode, DateTime.Now, clsWBranchTransferDetails.WBranchTransferNo, clsWBranchTransferDetails.TransferrerName);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsWBranchTransferDetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsWBranchTransferDetails.WBranchTransferNo;
clsInventoryDetails.ContactID = Constants.C_RETAILPLUS_SUPPLIERID;
clsInventoryDetails.ContactCode = Constants.C_RETAILPLUS_SUPPLIER;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.PurchaseCost = decItemCost - decVAT;
clsInventoryDetails.PurchaseVAT = decItemCost; // Purchase Cost with VAT
// insert into branchid from with minus quantity
clsInventoryDetails.BranchID = clsWBranchTransferDetails.BranchIDFrom;
clsInventoryDetails.PurchaseQuantity = -decQuantity;
clsInventory.Insert(clsInventoryDetails);
// insert into branchid to with add quantity
clsInventoryDetails.BranchID = clsWBranchTransferDetails.BranchIDTo;
clsInventoryDetails.PurchaseQuantity = decQuantity;
clsInventory.Insert(clsInventoryDetails);
}
}
private void AddItemToInventory(long TransferInID)
{
try
{
TransferInDetails clsTransferInDetails = Details(TransferInID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
TransferInItem clsTransferInItem = new TransferInItem(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductPackage clsProductPackage = new ProductPackage(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
InventoryDetails clsInventoryDetails;
ProductPackagePriceHistoryDetails clsProductPackagePriceHistoryDetails;
System.Data.DataTable dt = clsTransferInItem.ListAsDataTable(TransferInID, "TransferInItemID", SortOption.Ascending);
foreach(System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = new ProductUnit().GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decSellingPrice = Convert.ToDecimal(dr["SellingPrice"]);
decimal decVAT = Convert.ToDecimal(dr["VAT"]); // myReader.GetDecimal("VAT");
decimal decEVAT = Convert.ToDecimal(dr["EVAT"]);
decimal decLocalTax = Convert.ToDecimal(dr["LocalTax"]);
/*******************************************
* Add in the Price History
* ****************************************/
// Update ProductPackagePriceHistory first to get the history
clsProductPackagePriceHistoryDetails = new ProductPackagePriceHistoryDetails();
clsProductPackagePriceHistoryDetails.UID = clsTransferInDetails.TransferrerID;
clsProductPackagePriceHistoryDetails.PackageID = new ProductPackage().GetPackageID(lngProductID, intProductUnitID);
clsProductPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
clsProductPackagePriceHistoryDetails.PurchasePrice = (decItemQuantity * decUnitCost) / decQuantity;
clsProductPackagePriceHistoryDetails.Price = decSellingPrice;
clsProductPackagePriceHistoryDetails.VAT = decVAT;
clsProductPackagePriceHistoryDetails.EVAT = decEVAT;
clsProductPackagePriceHistoryDetails.LocalTax = decLocalTax;
clsProductPackagePriceHistoryDetails.Remarks = "Based on TransferIn #: " + clsTransferInDetails.TransferInNo;
ProductPackagePriceHistory clsProductPackagePriceHistory = new ProductPackagePriceHistory(base.Connection, base.Transaction);
clsProductPackagePriceHistory.Insert(clsProductPackagePriceHistoryDetails);
/*******************************************
* Add to Inventory
* ****************************************/
//clsProduct.AddQuantity(lngProductID, decQuantity);
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.AddQuantity(lngVariationMatrixID, decQuantity);
//}
// July 26, 2011: change the above codes to the following
clsProduct.AddQuantity(clsTransferInDetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_TRANSFER_IN), DateTime.Now, clsTransferInDetails.TransferInNo, clsTransferInDetails.TransferrerName);
/*******************************************
* Update Purchasing Information
* ****************************************/
int iBaseUnitID = clsProduct.get_BaseUnitID(lngProductID);
if (iBaseUnitID != intProductUnitID)
{
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsTransferInDetails.SupplierID, iBaseUnitID, (decItemQuantity * decUnitCost) / decQuantity);
}
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsTransferInDetails.SupplierID, intProductUnitID, decUnitCost);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsTransferInDetails.DeliveryDate;
clsInventoryDetails.ReferenceNo = clsTransferInDetails.TransferInNo;
clsInventoryDetails.ContactID = clsTransferInDetails.SupplierID;
clsInventoryDetails.ContactCode = clsTransferInDetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.TransferInQuantity = decQuantity;
clsInventoryDetails.TransferInCost = decItemCost - decVAT;
clsInventoryDetails.TransferInVAT = decItemCost; // TransferIn Cost with VAT
clsInventory.Insert(clsInventoryDetails);
/*******************************************
* Added April 28, 2010 4:20PM
* Update Selling Information when TransferIn is posted
* ****************************************/
clsProduct.UpdateSellingPrice(lngProductID, lngVariationMatrixID, clsTransferInDetails.SupplierID, intProductUnitID, decimal.Parse(dr["SellingPrice"].ToString()), -1, -1, -1, -1, -1);
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.UpdateSellingWithSameQuantityAndUnit(lngVariationMatrixID, clsPODetails.SupplierID, intProductUnitID, decimal.Parse(myReader["SellingPrice");
//}
/*******************************************
* Added April 28, 2010 4:20PM
* Update the purchase price history to check who has the lowest price.
* ****************************************/
ProductPurchasePriceHistoryDetails clsProductPurchasePriceHistoryDetails = new ProductPurchasePriceHistoryDetails();
clsProductPurchasePriceHistoryDetails.ProductID = lngProductID;
clsProductPurchasePriceHistoryDetails.MatrixID = lngVariationMatrixID;
clsProductPurchasePriceHistoryDetails.SupplierID = clsTransferInDetails.SupplierID;
clsProductPurchasePriceHistoryDetails.PurchasePrice = decUnitCost;
clsProductPurchasePriceHistoryDetails.PurchaseDate = clsTransferInDetails.TransferInDate;
clsProductPurchasePriceHistoryDetails.Remarks = clsTransferInDetails.TransferInNo;
ProductPurchasePriceHistory clsProductPurchasePriceHistory = new ProductPurchasePriceHistory(base.Connection, base.Transaction);
clsProductPurchasePriceHistory.AddToList(clsProductPurchasePriceHistoryDetails);
}
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void AddItemToInventory(long CreditMemoID)
{
SOReturnDetails clsSOReturnDetails = Details(CreditMemoID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
SOReturnItems clsSOReturnItems = new SOReturnItems(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
System.Data.DataTable dt = clsSOReturnItems.ListAsDataTable(CreditMemoID);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
/*******************************************
* Add from Inventory
* ****************************************/
// clsProduct.AddQuantity(ProductID, Quantity);
// if (VariationMatrixID != 0){ clsProductVariationsMatrix.AddQuantity(VariationMatrixID, Quantity); }
// July 28, 2011: change the above codes to the following
clsProduct.AddQuantity(clsSOReturnDetails.BranchID, lngProductID, lngVariationMatrixID, decQuantity, Products.getPRODUCT_INVENTORY_MOVEMENT_VALUE(PRODUCT_INVENTORY_MOVEMENT.ADD_SALES_RETURN), DateTime.Now, clsSOReturnDetails.CNNo, clsSOReturnDetails.SellerName);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsSOReturnDetails.PostingDate;
clsInventoryDetails.ReferenceNo = clsSOReturnDetails.CNNo;
clsInventoryDetails.ContactID = clsSOReturnDetails.CustomerID;
clsInventoryDetails.ContactCode = clsSOReturnDetails.CustomerCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.SReturnQuantity = decQuantity;
clsInventoryDetails.SReturnCost = decItemCost - decVAT;
clsInventoryDetails.SReturnVAT = decItemCost; //Purchase Return with VAT
clsInventory.Insert(clsInventoryDetails);
}
//myReader.Close();
}
public void UpdateSales(InventoryDetails Details)
{
try
{
string SQL = "UPDATE tblInventory SET " +
"PostingDateFrom = @PostingDateFrom, "+
"PostingDateTo = @PostingDateTo, "+
"PostingDate = @PostingDate, "+
"ReferenceNo = @ReferenceNo, "+
"ContactID = @ContactID, "+
"ContactCode = @ContactCode, "+
"ProductID = @ProductID, "+
"VariationMatrixID = @VariationMatrixID, "+
"MatrixDescription = @MatrixDescription, "+
"ProductCode = @ProductCode, "+
"SoldQuantity = @SoldQuantity, "+
"SoldCost = @SoldCost, "+
"SoldVAT = @SoldVAT "+
"WHERE InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmPostingDateFrom = new MySqlParameter("@PostingDateFrom", MySqlDbType.DateTime);
prmPostingDateFrom.Value = Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDateFrom);
MySqlParameter prmPostingDateTo = new MySqlParameter("@PostingDateTo", MySqlDbType.DateTime);
prmPostingDateTo.Value = Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDateTo);
MySqlParameter prmPostingDate = new MySqlParameter("@PostingDate", MySqlDbType.DateTime);
prmPostingDate.Value = Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmPostingDate);
MySqlParameter prmReferenceNo = new MySqlParameter("@ReferenceNo", MySqlDbType.String);
prmReferenceNo.Value = Details.ReferenceNo;
cmd.Parameters.Add(prmReferenceNo);
MySqlParameter prmContactID = new MySqlParameter("@ContactID", MySqlDbType.Int64);
prmContactID.Value = Details.ContactID;
cmd.Parameters.Add(prmContactID);
MySqlParameter prmContactCode = new MySqlParameter("@ContactCode", MySqlDbType.String);
prmContactCode.Value = Details.ContactCode;
cmd.Parameters.Add(prmContactCode);
MySqlParameter prmProductID = new MySqlParameter("@ProductID", MySqlDbType.Int64);
prmProductID.Value = Details.ProductID;
cmd.Parameters.Add(prmProductID);
MySqlParameter prmProductCode = new MySqlParameter("@ProductCode", MySqlDbType.String);
prmProductCode.Value = Details.ProductCode;
cmd.Parameters.Add(prmProductCode);
MySqlParameter prmVariationMatrixID = new MySqlParameter("@VariationMatrixID", MySqlDbType.Int64);
prmVariationMatrixID.Value = Details.VariationMatrixID;
cmd.Parameters.Add(prmVariationMatrixID);
MySqlParameter prmMatrixDescription = new MySqlParameter("@MatrixDescription", MySqlDbType.String);
prmMatrixDescription.Value = Details.MatrixDescription;
cmd.Parameters.Add(prmMatrixDescription);
MySqlParameter prmSoldQuantity = new MySqlParameter("@SoldQuantity", MySqlDbType.Decimal);
prmSoldQuantity.Value = Details.SoldQuantity;
cmd.Parameters.Add(prmSoldQuantity);
MySqlParameter prmSoldCost = new MySqlParameter("@SoldCost", MySqlDbType.Decimal);
prmSoldCost.Value = Details.SoldCost;
cmd.Parameters.Add(prmSoldCost);
MySqlParameter prmSoldVAT = new MySqlParameter("@SoldVAT", MySqlDbType.Decimal);
prmSoldVAT.Value = Details.SoldVAT;
cmd.Parameters.Add(prmSoldVAT);
MySqlParameter prmInventoryID = new MySqlParameter("@InventoryID", MySqlDbType.Int64);
prmInventoryID.Value = Details.InventoryID;
cmd.Parameters.Add(prmInventoryID);
base.ExecuteNonQuery(cmd);
//PO clsPO = new PO(base.Connection, base.Transaction);
//clsPO.SynchronizeAmount(Details.ReferenceNo);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public InventoryDetails Details(long InventoryID)
{
try
{
string SQL = SQLSelect() + "AND a.InventoryID = @InventoryID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmInventoryID = new MySqlParameter("@InventoryID", MySqlDbType.Int64);
prmInventoryID.Value = InventoryID;
cmd.Parameters.Add(prmInventoryID);
MySqlDataReader myReader = base.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
InventoryDetails Details = new InventoryDetails();
while (myReader.Read())
{
Details.BranchID = myReader.GetInt32("BranchID");
Details.InventoryID = myReader.GetInt64("InventoryID");
Details.PostingDateFrom = myReader.GetDateTime("PostingDateFrom");
Details.PostingDateTo = myReader.GetDateTime("PostingDateTo");
Details.PostingDate = myReader.GetDateTime("PostingDate");
Details.ReferenceNo = "" + myReader["ReferenceNo"].ToString();
Details.ContactID = myReader.GetInt64("ContactID");
Details.ContactCode = "" + myReader["ContactCode"].ToString();
Details.ProductID = myReader.GetInt64("ProductID");
Details.ProductCode = "" + myReader["ProductCode"].ToString();
Details.VariationMatrixID = myReader.GetInt64("VariationMatrixID");
Details.MatrixDescription = "" + myReader["MatrixDescription"].ToString();
Details.PurchaseQuantity = myReader.GetDecimal("PurchaseQuantity");
Details.PurchaseCost = myReader.GetDecimal("PurchaseCost");
Details.PurchaseVAT = myReader.GetDecimal("PurchaseVAT");
Details.PReturnQuantity = myReader.GetDecimal("PReturnQuantity");
Details.PReturnCost = myReader.GetDecimal("PReturnCost");
Details.PReturnVAT = myReader.GetDecimal("PReturnVAT");
Details.TransferInQuantity = myReader.GetDecimal("TransferInQuantity");
Details.TransferInCost = myReader.GetDecimal("TransferInCost");
Details.TransferInVAT = myReader.GetDecimal("TransferInVAT");
Details.TransferOutQuantity = myReader.GetDecimal("TransferOutQuantity");
Details.TransferOutCost = myReader.GetDecimal("TransferOutCost");
Details.TransferOutVAT = myReader.GetDecimal("TransferOutVAT");
Details.SoldQuantity = myReader.GetDecimal("SoldQuantity");
Details.SoldCost = myReader.GetDecimal("SoldCost");
Details.SoldVAT = myReader.GetDecimal("SoldVAT");
Details.SReturnQuantity = myReader.GetDecimal("SReturnQuantity");
Details.SReturnCost = myReader.GetDecimal("SReturnCost");
Details.SReturnVAT = myReader.GetDecimal("SReturnVAT");
Details.SCreditQuantity = myReader.GetDecimal("SCreditQuantity");
Details.SCreditCost = myReader.GetDecimal("SCreditCost");
Details.SCreditVAT = myReader.GetDecimal("SCreditVAT");
}
myReader.Close();
return(Details);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public long Insert(InventoryDetails Details)
{
try
{
string SQL = "INSERT INTO tblInventory (" +
"BranchID, " +
"PostingDateFrom, " +
"PostingDateTo, " +
"PostingDate, " +
"ReferenceNo, " +
"ContactID, " +
"ContactCode, " +
"ProductID, " +
"ProductCode, " +
"VariationMatrixID, " +
"MatrixDescription, " +
"PurchaseQuantity, " +
"PurchaseCost, " +
"PurchaseVAT, " +
"PInvoiceQuantity, " +
"PInvoiceCost, " +
"PInvoiceVAT, " +
"PReturnQuantity, " +
"PReturnCost, " +
"PReturnVAT, " +
"PDebitQuantity, " +
"PDebitCost, " +
"PDebitVAT, " +
"TransferInQuantity, " +
"TransferInCost, " +
"TransferInVAT, " +
"TransferOutQuantity, " +
"TransferOutCost," +
"TransferOutVAT," +
"SoldQuantity, " +
"SoldCost, " +
"SoldVAT, " +
"SReturnQuantity, " +
"SReturnCost, " +
"SReturnVAT, " +
"SCreditQuantity, " +
"SCreditCost, " +
"SCreditVAT, " +
"InvAdjustmentQuantity, " +
"InvAdjustmentCost, " +
"InvAdjustmentVAT, " +
"ClosingQuantity, " +
"ClosingActualQuantity, " +
"ClosingCost, " +
"ClosingVAT," +
"PurchasePrice" +
") VALUES (" +
"@BranchID, " +
"@PostingDateFrom, " +
"@PostingDateTo, " +
"@PostingDate, " +
"@ReferenceNo, " +
"@ContactID, " +
"@ContactCode, " +
"@ProductID, " +
"@ProductCode, " +
"@VariationMatrixID, " +
"@MatrixDescription, " +
"@PurchaseQuantity, " +
"@PurchaseCost, " +
"@PurchaseVAT, " +
"@PInvoiceQuantity, " +
"@PInvoiceCost, " +
"@PInvoiceVAT, " +
"@PReturnQuantity, " +
"@PReturnCost, " +
"@PReturnVAT, " +
"@PDebitQuantity, " +
"@PDebitCost, " +
"@PDebitVAT, " +
"@TransferInQuantity, " +
"@TransferInCost, " +
"@TransferInVAT, " +
"@TransferOutQuantity, " +
"@TransferOutCost," +
"@TransferOutVAT," +
"@SoldQuantity, " +
"@SoldCost, " +
"@SoldVAT, " +
"@SReturnQuantity, " +
"@SReturnCost, " +
"@SReturnVAT, " +
"@SCreditQuantity, " +
"@SCreditCost, " +
"@SCreditVAT, " +
"@InvAdjustmentQuantity, " +
"@InvAdjustmentCost, " +
"@InvAdjustmentVAT, " +
"@ClosingQuantity, " +
"@ClosingActualQuantity, " +
"@ClosingCost, " +
"@ClosingVAT," +
"@PurchasePrice" +
");";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@BranchID", Details.BranchID);
cmd.Parameters.AddWithValue("@PostingDateFrom", Details.PostingDateFrom.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDateTo", Details.PostingDateTo.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@PostingDate", Details.PostingDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@ReferenceNo", Details.ReferenceNo);
cmd.Parameters.AddWithValue("@ContactID", Details.ContactID);
cmd.Parameters.AddWithValue("@ContactCode", Details.ContactCode);
cmd.Parameters.AddWithValue("@ProductID", Details.ProductID);
cmd.Parameters.AddWithValue("@ProductCode", Details.ProductCode);
cmd.Parameters.AddWithValue("@VariationMatrixID", Details.VariationMatrixID);
cmd.Parameters.AddWithValue("@MatrixDescription", Details.MatrixDescription);
cmd.Parameters.AddWithValue("@PurchaseQuantity", Details.PurchaseQuantity);
cmd.Parameters.AddWithValue("@PurchaseCost", Details.PurchaseCost);
cmd.Parameters.AddWithValue("@PurchaseVAT", Details.PurchaseVAT);
cmd.Parameters.AddWithValue("@PInvoiceQuantity", Details.PInvoiceQuantity);
cmd.Parameters.AddWithValue("@PInvoiceCost", Details.PInvoiceCost);
cmd.Parameters.AddWithValue("@PInvoiceVAT", Details.PInvoiceVAT);
cmd.Parameters.AddWithValue("@PReturnQuantity", Details.PReturnQuantity);
cmd.Parameters.AddWithValue("@PReturnCost", Details.PReturnCost);
cmd.Parameters.AddWithValue("@PReturnVAT", Details.PReturnVAT);
cmd.Parameters.AddWithValue("@PDebitQuantity", Details.PDebitQuantity);
cmd.Parameters.AddWithValue("@PDebitCost", Details.PDebitCost);
cmd.Parameters.AddWithValue("@PDebitVAT", Details.PDebitVAT);
cmd.Parameters.AddWithValue("@TransferInQuantity", Details.TransferInQuantity);
cmd.Parameters.AddWithValue("@TransferInCost", Details.TransferInCost);
cmd.Parameters.AddWithValue("@TransferInVAT", Details.TransferInVAT);
cmd.Parameters.AddWithValue("@TransferOutQuantity", Details.TransferOutQuantity);
cmd.Parameters.AddWithValue("@TransferOutCost", Details.TransferOutCost);
cmd.Parameters.AddWithValue("@TransferOutVAT", Details.TransferOutVAT);
cmd.Parameters.AddWithValue("@SoldQuantity", Details.SoldQuantity);
cmd.Parameters.AddWithValue("@SoldCost", Details.SoldCost);
cmd.Parameters.AddWithValue("@SoldVAT", Details.SoldVAT);
cmd.Parameters.AddWithValue("@SReturnQuantity", Details.SReturnQuantity);
cmd.Parameters.AddWithValue("@SReturnCost", Details.SReturnCost);
cmd.Parameters.AddWithValue("@SReturnVAT", Details.SReturnVAT);
cmd.Parameters.AddWithValue("@SCreditQuantity", Details.SCreditQuantity);
cmd.Parameters.AddWithValue("@SCreditCost", Details.SCreditCost);
cmd.Parameters.AddWithValue("@SCreditVAT", Details.SCreditVAT);
cmd.Parameters.AddWithValue("@InvAdjustmentQuantity", Details.InvAdjustmentQuantity);
cmd.Parameters.AddWithValue("@InvAdjustmentCost", Details.InvAdjustmentCost);
cmd.Parameters.AddWithValue("@InvAdjustmentVAT", Details.InvAdjustmentVAT);
cmd.Parameters.AddWithValue("@ClosingQuantity", Details.ClosingQuantity);
cmd.Parameters.AddWithValue("@ClosingActualQuantity", Details.ClosingActualQuantity);
cmd.Parameters.AddWithValue("@ClosingCost", Details.ClosingCost);
cmd.Parameters.AddWithValue("@ClosingVAT", Details.ClosingVAT);
cmd.Parameters.AddWithValue("@PurchasePrice", Details.PurchasePrice);
base.ExecuteNonQuery(cmd);
SQL = "SELECT LAST_INSERT_ID();";
cmd.Parameters.Clear();
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
Int64 iID = 0;
foreach (System.Data.DataRow dr in dt.Rows)
{
iID = Int64.Parse(dr[0].ToString());
}
return iID;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void AddItemToInventory(long pvtDebitMemoID)
{
DebitMemoDetails clsDebitMemoDetails = Details(pvtDebitMemoID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
DebitMemoItems clsDebitMemoItems = new DebitMemoItems(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
ProductDetails clsProductDetails = new ProductDetails();
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
ProductPackage clsProductPackage = new ProductPackage(base.Connection, base.Transaction);
MatrixPackage clsMatrixPackage = new MatrixPackage(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
ProductPackagePriceHistoryDetails clsProductPackagePriceHistoryDetails;
ProductPackagePriceHistory clsProductPackagePriceHistory = new ProductPackagePriceHistory(base.Connection, base.Transaction);
System.Data.DataTable dt = clsDebitMemoItems.ListAsDataTable(pvtDebitMemoID, SortField: "DebitMemoItemID", SortOrder: SortOption.Ascending);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = Convert.ToInt64(dr["ProductID"]);
int intProductUnitID = Convert.ToInt16(dr["ProductUnitID"]);
decimal decItemQuantity = Convert.ToDecimal(dr["Quantity"]);
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = Convert.ToInt64(dr["VariationMatrixID"]);
string strMatrixDescription = dr["MatrixDescription"].ToString();
string strProductCode = dr["ProductCode"].ToString();
string strProductUnitCode = dr["ProductUnitCode"].ToString();
decimal decNewUnitCost = Convert.ToDecimal(dr["UnitCost"]);
decimal decAmount = Convert.ToDecimal(dr["Amount"]);
//decimal decSellingPrice = Convert.ToDecimal(dr["SellingPrice"]);
decimal decVAT = Convert.ToDecimal(dr["VAT"]);
//decimal decEVAT = Convert.ToDecimal(dr["EVAT"]);
//decimal decLocalTax = Convert.ToDecimal(dr["LocalTax"]);
clsProductDetails = clsProduct.Details1(clsDebitMemoDetails.BranchID, lngProductID);
/*******************************************
* Add in the Purchase Price History based on Debit Memo
* ****************************************/
// Update ProductPackagePriceHistory first to get the history
clsProductPackagePriceHistoryDetails = new ProductPackagePriceHistoryDetails();
clsProductPackagePriceHistoryDetails.UID = clsDebitMemoDetails.PurchaserID;
clsProductPackagePriceHistoryDetails.PackageID = clsProductPackage.GetPackageID(lngProductID, intProductUnitID);
clsProductPackagePriceHistoryDetails.ChangeDate = DateTime.Now;
clsProductPackagePriceHistoryDetails.PurchasePrice = decNewUnitCost * (decItemQuantity / decQuantity);
clsProductPackagePriceHistoryDetails.Price = -1;
clsProductPackagePriceHistoryDetails.VAT = -1;
clsProductPackagePriceHistoryDetails.EVAT = -1;
clsProductPackagePriceHistoryDetails.LocalTax = -1;
clsProductPackagePriceHistoryDetails.Remarks = "Based on DebitMemo #: " + clsDebitMemoDetails.MemoNo;
clsProductPackagePriceHistory.Insert(clsProductPackagePriceHistoryDetails);
/*******************************************
* Subtract from Inventory : Remove this since this is a Debit Memo
* ****************************************/
//clsProduct.SubtractQuantity(lngProductID, decQuantity);
//if (lngVariationMatrixID != 0)
//{
// clsProductVariationsMatrix.SubtractQuantity(lngVariationMatrixID, decQuantity);
//}
/*******************************************
* Update Purchasing Information
* ****************************************/
int iBaseUnitID = clsProduct.get_BaseUnitID(lngProductID);
if (iBaseUnitID != intProductUnitID)
{
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsDebitMemoDetails.SupplierID, iBaseUnitID, (decItemQuantity * decNewUnitCost) / decQuantity);
}
clsProduct.UpdatePurchasing(lngProductID, lngVariationMatrixID, clsDebitMemoDetails.SupplierID, intProductUnitID, decNewUnitCost);
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsDebitMemoDetails.PostingDate;
clsInventoryDetails.ReferenceNo = clsDebitMemoDetails.MemoNo;
clsInventoryDetails.ContactID = clsDebitMemoDetails.SupplierID;
clsInventoryDetails.ContactCode = clsDebitMemoDetails.SupplierCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.PDebitQuantity = decQuantity;
clsInventoryDetails.PDebitCost = decAmount - decVAT;
clsInventoryDetails.PDebitVAT = decAmount;
clsInventory.Insert(clsInventoryDetails);
}
}
private void AddItemToInventory(long CreditMemoID)
{
CreditMemoDetails clsCreditMemoDetails = Details(CreditMemoID);
ERPConfig clsERPConfig = new ERPConfig(base.Connection, base.Transaction);
ERPConfigDetails clsERPConfigDetails = clsERPConfig.Details();
CreditMemoItems clsCreditMemoItems = new CreditMemoItems(base.Connection, base.Transaction);
ProductUnit clsProductUnit = new ProductUnit(base.Connection, base.Transaction);
Products clsProduct = new Products(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
Inventory clsInventory = new Inventory(base.Connection, base.Transaction);
System.Data.DataTable dt = clsCreditMemoItems.ListAsDataTable(CreditMemoID);
foreach (System.Data.DataRow dr in dt.Rows)
{
long lngProductID = long.Parse(dr["ProductID"].ToString());
int intProductUnitID = int.Parse(dr["ProductUnitID"].ToString());
decimal decItemQuantity = decimal.Parse(dr["Quantity"].ToString());
decimal decQuantity = clsProductUnit.GetBaseUnitValue(lngProductID, intProductUnitID, decItemQuantity);
long lngVariationMatrixID = long.Parse(dr["VariationMatrixID"].ToString());
string strMatrixDescription = "" + dr["MatrixDescription"].ToString();
string strProductCode = "" + dr["ProductCode"].ToString();
decimal decUnitCost = decimal.Parse(dr["UnitCost"].ToString());
decimal decItemCost = decimal.Parse(dr["Amount"].ToString());
decimal decVAT = decimal.Parse(dr["VAT"].ToString());
/*******************************************
* Do not Add to Inventory coz this is a Debit Memo and no effect on inventory
* ****************************************/
/*******************************************
* Add to Inventory Analysis
* ****************************************/
InventoryDetails clsInventoryDetails = new InventoryDetails();
clsInventoryDetails.PostingDateFrom = clsERPConfigDetails.PostingDateFrom;
clsInventoryDetails.PostingDateTo = clsERPConfigDetails.PostingDateTo;
clsInventoryDetails.PostingDate = clsCreditMemoDetails.PostingDate;
clsInventoryDetails.ReferenceNo = clsCreditMemoDetails.CNNo;
clsInventoryDetails.ContactID = clsCreditMemoDetails.CustomerID;
clsInventoryDetails.ContactCode = clsCreditMemoDetails.CustomerCode;
clsInventoryDetails.ProductID = lngProductID;
clsInventoryDetails.ProductCode = strProductCode;
clsInventoryDetails.VariationMatrixID = lngVariationMatrixID;
clsInventoryDetails.MatrixDescription = strMatrixDescription;
clsInventoryDetails.SCreditQuantity = decQuantity;
clsInventoryDetails.SCreditCost = decItemCost - decVAT;
clsInventoryDetails.SCreditVAT = decItemCost; //Sales Return with VAT
clsInventory.Insert(clsInventoryDetails);
}
//myReader.Close();
}