public void GenerateItemsForReorder(Int32 TerminalID, Int64 ClosingID)
{
try
{
GetConnection();
Terminal clsTerminal = new Terminal(base.Connection, base.Transaction);
TerminalDetails clsTerminalDetails = clsTerminal.Details(TerminalID);
ClosingDetails clsClosingDetails = Details(ClosingID);
Products clsProduct = new Products(base.Connection, base.Transaction);
System.Data.DataTable dt = clsProduct.ForReorder(clsClosingDetails.SupplierID);
ClosingItem clsClosingItem = new ClosingItem(base.Connection, base.Transaction);
ProductVariationsMatrix clsProductVariationsMatrix = new ProductVariationsMatrix(base.Connection, base.Transaction);
foreach(System.Data.DataRow dr in dt.Rows)
{
ClosingItemDetails clsDetails = new ClosingItemDetails();
clsDetails.ClosingID = ClosingID;
clsDetails.ProductID = Convert.ToInt64(dr["ProductID"]);
clsDetails.ProductCode = dr["ProductCode"].ToString();
clsDetails.BarCode = dr["BarCode"].ToString();
clsDetails.Description = dr["ProductDesc"].ToString();
clsDetails.ProductGroup = dr["ProductGroupCode"].ToString();
clsDetails.ProductSubGroup = dr["ProductSubGroupCode"].ToString();
clsDetails.ProductUnitID = Convert.ToInt32(dr["UnitID"].ToString());
clsDetails.ProductUnitCode = dr["UnitName"].ToString();
clsDetails.Quantity = Convert.ToDecimal(dr["ReorderQty"].ToString());
clsDetails.UnitCost = Convert.ToDecimal(dr["Price"].ToString());
clsDetails.Discount = 0;
clsDetails.InPercent = false;
clsDetails.TotalDiscount = 0;
clsDetails.Remarks = "added using auto generation";
decimal amount = clsDetails.Quantity * clsDetails.UnitCost;
if (Convert.ToDecimal(dr["VAT"]) > 0)
{
clsDetails.VatableAmount = amount;
clsDetails.EVatableAmount = amount;
clsDetails.LocalTax = amount;
clsDetails.VAT = clsDetails.VatableAmount * (clsTerminalDetails.VAT / 100);
clsDetails.EVAT = clsDetails.EVatableAmount * (clsTerminalDetails.EVAT / 100);
clsDetails.LocalTax = clsDetails.LocalTax * (clsTerminalDetails.LocalTax / 100);
clsDetails.IsVatable = true;
}
else
{
clsDetails.VAT = 0;
clsDetails.VatableAmount = 0;
clsDetails.EVAT = 0;
clsDetails.EVatableAmount = 0;
clsDetails.LocalTax = 0;
clsDetails.IsVatable = false;
}
clsDetails.Amount = amount + clsDetails.VAT;
System.Data.DataTable dtmatrix = clsProductVariationsMatrix.ForReorder(clsDetails.ProductID, clsClosingDetails.SupplierID);
if (dtmatrix.Rows.Count > 0)
foreach(System.Data.DataRow drmatrix in dtmatrix.Rows)
{
amount = clsDetails.Quantity * clsDetails.UnitCost;
clsDetails.ProductUnitID = Convert.ToInt32(drmatrix["UnitID"]);
clsDetails.ProductUnitCode = drmatrix["UnitName"].ToString();
clsDetails.Quantity = Convert.ToDecimal(drmatrix["ReorderQty"]);
clsDetails.UnitCost = Convert.ToDecimal(drmatrix["Price"]);
if (Convert.ToDecimal(drmatrix["VAT"]) > 0)
{
clsDetails.VatableAmount = amount;
clsDetails.EVatableAmount = amount;
clsDetails.LocalTax = amount;
clsDetails.VAT = clsDetails.VatableAmount * (clsTerminalDetails.VAT / 100);
clsDetails.EVAT = clsDetails.EVatableAmount * (clsTerminalDetails.EVAT / 100);
clsDetails.LocalTax = clsDetails.LocalTax * (clsTerminalDetails.LocalTax / 100);
clsDetails.IsVatable = true;
}
else
{
clsDetails.VAT = 0;
clsDetails.VatableAmount = 0;
clsDetails.EVAT = 0;
clsDetails.EVatableAmount = 0;
clsDetails.LocalTax = 0;
clsDetails.IsVatable = false;
}
clsDetails.Amount = amount + clsDetails.VAT;
clsDetails.VariationMatrixID = Convert.ToInt64(drmatrix["MatrixID"]);
clsDetails.MatrixDescription = drmatrix["VariationDesc"].ToString();
clsClosingItem.Insert(clsDetails);
}
else
{
clsDetails.VariationMatrixID = 0;
clsDetails.MatrixDescription = string.Empty;
clsClosingItem.Insert(clsDetails);
}
}
}
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();
}
public void Cancel(long ClosingID, DateTime CancelledDate, string Remarks, long CancelledByID)
{
try
{
string SQL= "UPDATE tblClosing SET " +
"CancelledDate = @CancelledDate, " +
"CancelledRemarks = @CancelledRemarks, " +
"CancelledByID = @CancelledByID, " +
"ClosingStatus = @ClosingStatus " +
"WHERE ClosingID = @ClosingID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmCancelledDate = new MySqlParameter("@CancelledDate",MySqlDbType.DateTime);
prmCancelledDate.Value = CancelledDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmCancelledDate);
MySqlParameter prmCancelledRemarks = new MySqlParameter("@CancelledRemarks",MySqlDbType.String);
prmCancelledRemarks.Value = Remarks;
cmd.Parameters.Add(prmCancelledRemarks);
MySqlParameter prmCancelledByID = new MySqlParameter("@CancelledByID",MySqlDbType.Int64);
prmCancelledByID.Value = CancelledByID;
cmd.Parameters.Add(prmCancelledByID);
MySqlParameter prmClosingStatus = new MySqlParameter("@ClosingStatus",MySqlDbType.Int16);
prmClosingStatus.Value = ClosingStatus.Cancelled.ToString("d");
cmd.Parameters.Add(prmClosingStatus);
MySqlParameter prmClosingID = new MySqlParameter("@ClosingID",MySqlDbType.Int64);
prmClosingID.Value = ClosingID;
cmd.Parameters.Add(prmClosingID);
base.ExecuteNonQuery(cmd);
/*******************************************
* Update the status of items
* ****************************************/
ClosingItem clsClosingItem = new ClosingItem(base.Connection, base.Transaction);
clsClosingItem.Cancel(ClosingID);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public void IssueGRN(long ClosingID, string SupplierDRNo, DateTime DeliveryDate)
{
try
{
string SQL= "UPDATE tblClosing SET " +
"SupplierDRNo = @SupplierDRNo, " +
"DeliveryDate = @DeliveryDate, " +
"ClosingStatus = @ClosingStatus " +
"WHERE ClosingID = @ClosingID;";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
MySqlParameter prmSupplierDRNo = new MySqlParameter("@SupplierDRNo",MySqlDbType.String);
prmSupplierDRNo.Value = SupplierDRNo;
cmd.Parameters.Add(prmSupplierDRNo);
MySqlParameter prmDeliveryDate = new MySqlParameter("@DeliveryDate",MySqlDbType.DateTime);
prmDeliveryDate.Value = DeliveryDate.ToString("yyyy-MM-dd HH:mm:ss");
cmd.Parameters.Add(prmDeliveryDate);
MySqlParameter prmClosingStatus = new MySqlParameter("@ClosingStatus",MySqlDbType.Int16);
prmClosingStatus.Value = ClosingStatus.Posted.ToString("d");
cmd.Parameters.Add(prmClosingStatus);
MySqlParameter prmClosingID = new MySqlParameter("@ClosingID",MySqlDbType.Int64);
prmClosingID.Value = ClosingID;
cmd.Parameters.Add(prmClosingID);
base.ExecuteNonQuery(cmd);
/*******************************************
* Update the status of items
* ****************************************/
ClosingItem clsClosingItem = new ClosingItem(base.Connection, base.Transaction);
clsClosingItem.Post(ClosingID);
/*******************************************
* Update Vendor Account
* ****************************************/
AddItemToInventory(ClosingID);
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}