/// <summary>
/// Insert or update a given purchase
/// </summary>
/// <param name="purchase">Sale to be synchronized</param>
/// <param name="updateItem">True for updating rate or unit or tax of an item.
/// False for not doing permanent update to item, but save vatiation in rate etc. for this purchase alone. </param>
/// <param name="syncTransaction">False if transaction related to this purchase is synchronized already.
/// True if need to be sychronized again, or if u r not sure what to pass.</param>
/// <returns>Updated purchase</returns>
public SalePurchase SyncSale(SalePurchase sale, bool updateItem, bool syncTransaction)
{
if (syncTransaction)
{
sale.Transaction = (new TransactionManager()).SyncTransaction(sale.Transaction);
}
//sync item
sale.Item = (new ItemManager()).SyncItem(sale.Item, updateItem);
sale.SaleUnit = (new UnitManager()).SyncUnit(sale.SaleUnit);
return(SaleCache.GetInstance().SyncSale(sale));
}
internal double GetSuppressedMovingAvgRate(SalePurchase purchaseToDelete)
{
//Suppress the change in moving average introduced by this purchase item.
double oldMovingAvg = ((purchaseToDelete.Item.MovingAvgRate * purchaseToDelete.Item.Stock)
- (purchaseToDelete.SaleRate * purchaseToDelete.Quantity)) / (purchaseToDelete.Item.Stock - purchaseToDelete.Quantity);
if (double.IsNaN(oldMovingAvg))
{
return(0);
}
return(oldMovingAvg);
}
public List <SalePurchase> GetSalesByTransactionId(int transactionID)
{
Transaction transaction = TransactionCache.GetInstance()
.GetTransactionById(transactionID);
SalePurchase sale;
List <SalePurchase> sales = new List <SalePurchase>();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, Item, SaleRate, SaleQuantity, Tax, SaleNumber, SaleUnit " +
"From Sales Where TransactionId=? Order By SaleNumber");
command.Parameters.AddWithValue("Transaction", transactionID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
sale = new SalePurchase();
sale.ID = int.Parse(reader[0].ToString());
sale.Transaction = transaction;
sale.SaleRate = double.Parse(reader[2].ToString());
sale.Quantity = double.Parse(reader[3].ToString());
sale.SaleTax = double.Parse(reader[4].ToString());
sale.Number = int.Parse(reader[5].ToString());
sale.SaleUnit = new Unit();
if (reader[6].ToString() == string.Empty)
{
sale.SaleUnit.ID = -1;
}
else
{
sale.SaleUnit.ID = int.Parse(reader[6].ToString());
}
sale.Item = new Item();
sale.Item.ID = int.Parse(reader[1].ToString());
sales.Add(sale);
}
foreach (SalePurchase s in sales)
{
s.Item = ItemCache.GetInstance()
.GetItemById(s.Item.ID);
if (s.SaleUnit.ID < 0)
{
s.SaleUnit = s.Item.Unit;
}
else
{
s.SaleUnit = UnitCache.GetInstance().GetUnitById(s.SaleUnit.ID);
}
}
return(sales);
}
internal double GetMovingAvgRate(SalePurchase purchase, SalePurchase prevPurchase)
{
//Step 1. Get the unmodified purchase item from db.
//prevPurchase
//step 2. Suppress the change in moving average introduced by this purchase item.
double oldMovingAvg = GetSuppressedMovingAvgRate(prevPurchase);
//step 3. apply this value only if new item and old item in this purchase line has not changed.
if (prevPurchase.Item.ID == purchase.Item.ID)
{
purchase.Item.MovingAvgRate = oldMovingAvg;
}
//step 3. Introduce change in moving average for the modified purchase from cache.
return(GetMovingAvgRate(purchase));
}
public double GetMovingAvgRate(SalePurchase purchase)
{
double newAverage = purchase.Item.MovingAvgRate;
//modify movingavg only if this is a purchase.
if (purchase.Transaction.IsPurchase & purchase.Quantity > 0.0)
{
double n;
//this is a new purchase.
//in this case, need to update with the new purchase rate and quantity
n = (purchase.Item.Stock /* + purchase.Quantity (is already added)*/) / purchase.Quantity;
newAverage = purchase.Item.MovingAvgRate - (purchase.Item.MovingAvgRate / n);
newAverage += (purchase.SaleRate / n);
}
return(newAverage);
}
private SalePurchase GetSaleBySaleIDFromDB(int salePurchaseID)
{
SalePurchase saleInDb = new SalePurchase();
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, Item, SaleRate, SaleQuantity, Tax, SaleNumber, SaleUnit, TransactionId " +
"From Sales Where ID=?");
command.Parameters.AddWithValue("ID", salePurchaseID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
while (reader.Read())
{
saleInDb.ID = int.Parse(reader[0].ToString());
saleInDb.SaleRate = double.Parse(reader[2].ToString());
saleInDb.Quantity = double.Parse(reader[3].ToString());
saleInDb.SaleTax = double.Parse(reader[4].ToString());
saleInDb.Number = int.Parse(reader[5].ToString());
saleInDb.SaleUnit = new Unit();
if (reader[6].ToString() == string.Empty)
{
saleInDb.SaleUnit.ID = -1;
}
else
{
saleInDb.SaleUnit.ID = int.Parse(reader[6].ToString());
}
saleInDb.Item = new Item();
saleInDb.Item.ID = int.Parse(reader[1].ToString());
}
//fill up child objects
saleInDb.Item = ItemCache.GetInstance()
.GetItemById(saleInDb.Item.ID);
if (saleInDb.SaleUnit.ID < 0)
{
saleInDb.SaleUnit = saleInDb.Item.Unit;
}
else
{
saleInDb.SaleUnit = UnitCache.GetInstance().GetUnitById(saleInDb.SaleUnit.ID);
}
return(saleInDb);
}
private bool SaveThisSale()
{
string titleBackUp = this.Text;
this.Text = "Saving....";
int transactionIdBackUp = transaction.ID;//need this in case of rollback
//try
//{
comm.BeginBatchOperation();
SalePurchase sale;
Item item;
Unit unit;
transaction.TransactionTime = DateTime.Parse(estimateDatePicker.Value.ToShortDateString() + " "
+ DateTime.Now.TimeOfDay.ToString());
transaction.ParentProject.Name = projectSelector.Text;
transaction = tm.SyncTransaction(transaction);
sales = new List <SalePurchase>();
foreach (DataGridViewRow row in purchaseGrid.Rows)
{
if (!row.IsNewRow)
{
unit = new Unit();
unit.Name = (string)row.Cells[4].Value;
item = new Item((string)row.Cells[1].Value, double.Parse(row.Cells[3].Value.ToString()), unit);
sale = new SalePurchase();
sale.Number = (int)row.Cells[0].Value;
sale.ID = (int)row.Cells["id"].Value;
sale.Quantity = double.Parse(row.Cells[2].Value.ToString());
sale.SaleRate = double.Parse(row.Cells[3].Value.ToString());
sale.Item = item;
sale.SaleUnit = unit;
sale.Transaction = transaction;
sales.Add(sale);
}
}
//create payment
payment = new Payment();
payment.Amount = double.Parse(currentPayment.Text);
payment.BankMoney = eMoney.Checked;
payment.Transaction = transaction;
if (!paymentMode)
{
sm.SyncSales(sales, updateItems.Checked, transaction.ID, true);
}
pm.SyncPayment(payment, estimateDatePicker.Value);
comm.ConfirmBatchOperation();
return(true);
//}
//catch (Exception ex)
//{
// cancelFormClose = true;
// comm.RollBackBatchOperation();
// transaction.ID = transactionIdBackUp;
// MessageBox.Show("Please try again." + Environment.NewLine + "Details:" + ex.Message, "Sorry! Something went wrong", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return false;
//}
//finally
//{
// this.Text = titleBackUp;
// titleBackUp = string.Empty;
//}
}
//modified to incorporate stock management on sale insert/update 24.9.2010
public SalePurchase SyncSale(SalePurchase sale)
{
OleDbCommand command = DBFunctions.GetInstance().GetCommand(
"Select ID, SaleQuantity From Sales Where ID=?");
command.Parameters.AddWithValue("ID", sale.ID);
OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
//sale present. So update it
//moving average rate of items incorporated. 25.8.2014
//stock management incorporated. 24.9.2010
//sale purchase modes incorporated. 1/4/2011
if (sale.Transaction.IsPurchase)
{
//subtract the previous purchase qtty to stock, then add new purchace qtty.
sale.Item.Stock = sale.Item.Stock - double.Parse(reader[1].ToString()) + sale.Quantity;
//in case of updating a purchase, revert the average changed due to
//previous item rate, apply change in average due to new rate.
//this has to be done before updating DB with new purchase rate.
//Get the previous purchase object from db and pass it on to the calculator.
SalePurchase prevPurchase = GetSaleBySaleIDFromDB(sale.ID);
sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale, prevPurchase);
MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
}
else
{
//add the previous salequantity to item's stock, then subtract the new salequantity
sale.Item.Stock = sale.Item.Stock + double.Parse(reader[1].ToString()) - sale.Quantity;
//if not a purchase, do not bother updating moving avg
}
StockCache.GetInstance().UpdateStock(sale.Item);
command = DBFunctions.GetInstance().GetCommand(
"Update Sales Set TransactionId=?, Item=?, SaleRate=?, SaleQuantity=?, Tax=?, SaleNumber=?,SaleUnit=?, LastUpdatedTime=Now() Where ID=?");
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
command.Parameters.AddWithValue("Item", sale.Item.ID);
command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
command.Parameters.AddWithValue("Tax", sale.SaleTax);
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
command.Parameters.AddWithValue("ID", sale.ID);
command.ExecuteNonQuery();
}
else
{
//sale not present. so insert it
command = DBFunctions.GetInstance().GetCommand(
"INSERT INTO SALES(TransactionId,Item,SaleRate,SaleQuantity,Tax,SaleNumber,SaleUnit,LastUpdatedTime) "
+ "VALUES(?,?,?,?,?,?,?,?)");
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
command.Parameters.AddWithValue("Item", sale.Item.ID);
command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
command.Parameters.AddWithValue("Tax", sale.SaleTax);
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
command.Parameters.Add("LastUpdatedTime", OleDbType.Date).Value = DateTime.Now;
command.ExecuteNonQuery();
//stock management incorporated. 24.9.2010
//sale/purchace incorporated 1.4.2011
if (sale.Transaction.IsPurchase)
{
//add new sale's sale qtty to the item stock.
sale.Item.Stock = sale.Item.Stock + sale.Quantity;
//incorporating moving avg rate of item 25.8.2014
//while inserting a new purchase, update moving averate rate.
sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale);
MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
}
else
{
//subtract new sale's sale qtty from the item stock.
sale.Item.Stock = sale.Item.Stock - sale.Quantity;
}
StockCache.GetInstance().UpdateStock(sale.Item);
//get back the id
command = DBFunctions.GetInstance().GetCommand(
"Select ID From Sales Where SaleNumber=? And TransactionId=?");
command.Parameters.AddWithValue("SaleNumber", sale.Number);
command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
reader = DBFunctions.GetInstance().GetReader(command);
if (reader.Read())
{
sale.ID = int.Parse(reader[0].ToString());
}
}
return(sale);
}
