private void AddUpdateTransferInDetail(object transferObject, object transferDetailObject, bool allowNegativeQty, ItemsManagementType imt, bool increaseStoreAvailability)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(transferObject);
helper.AddObject(transferDetailObject);
bool usesLots = imt != ItemsManagementType.AveragePrice &&
imt != ItemsManagementType.QuickAveragePrice &&
imt != ItemsManagementType.LastPurchasePrice;
// Check if we already have that detail
long result;
var oldInfo = ExecuteObject <ObjectsContainer <int, long, long, double> > ("SELECT Sign as Value1, ObjectID as Value2, GoodID as Value3, Qtty as Value4 FROM operations WHERE ID = @ID", helper.Parameters);
// Get the quantity from the new detail
double newGoodsQty = (double)helper.GetObjectValue(DataField.OperationDetailQuantity);
// We are updating detail information
if (oldInfo != null)
{
long oldLocationId = oldInfo.Value2;
long oldGoodsId = oldInfo.Value3;
double oldGoodsQty = oldInfo.Value4;
// Get the store id from the new detail
long newLocationId = (long)helper.GetObjectValue(DataField.OperationLocationId);
// Get the item id from the new detail
long newGoodsId = (long)helper.GetObjectValue(DataField.OperationDetailItemId);
long newLotId;
string newLot;
double newPrice;
long oldLotId;
string oldLot;
double oldPrice;
GetOperationDetailLotInfo(helper, usesLots, newGoodsQty, out newLotId, out newLot, out newPrice, out oldLotId, out oldLot, out oldPrice);
if (newGoodsQty.IsZero())
{
// Update the operation row with the changes
result = ExecuteNonQuery("DELETE FROM operations WHERE ID = @ID", helper.Parameters);
}
else
{
// Update the operation row with the changes
result = ExecuteNonQuery(string.Format("UPDATE operations {0} WHERE ID = @ID",
helper.GetSetStatement(OperationNonDBFields.Union(new [] { DataField.OperationDetailId }).ToArray())), helper.Parameters);
}
if (result != 1)
{
throw new Exception("Unable to update operation detail.");
}
if (oldInfo.Value1 == 0 && increaseStoreAvailability)
{
ApplyTransferInInsertToStore(helper, usesLots);
return;
}
helper.AddParameters(new DbParam("oldGoodsQty", oldGoodsQty));
helper.AddParameters(new DbParam("oldLocationId", oldLocationId));
helper.AddParameters(new DbParam("oldGoodsId", oldGoodsId));
if (usesLots)
{
if (newLocationId != oldLocationId || newGoodsId != oldGoodsId || newGoodsQty != oldGoodsQty ||
newPrice != oldPrice || newLot != oldLot || newLotId != oldLotId)
{
helper.AddParameters(new DbParam("oldPrice", oldPrice));
helper.AddParameters(new DbParam("oldLot", oldLot));
helper.AddParameters(new DbParam("oldLotId", oldLotId));
// Set the new quantity in the store
if (!newGoodsQty.IsZero())
{
result = ExecuteNonQuery(string.Format(@"UPDATE store
SET Qtty = Qtty + @Qtty
WHERE ObjectID = @ObjectID AND GoodID = @GoodID AND Lot = @Lot AND LotID = @LotID AND ABS(Price - @PriceIn) < {0}",
PurchaseCurrencyPrecisionString), helper.Parameters);
if (result == 0)
{
result = ExecuteScalar <long> (@"SELECT IFNULL(MAX(LotOrder), 0)
FROM store
WHERE ObjectID = @ObjectID AND GoodID = @GoodID", helper.Parameters);
helper.AddParameters(new DbParam("newLotOrder", result + 1));
ExecuteNonQuery(@"INSERT INTO store (ObjectID, GoodID, Lot, LotID, Price, Qtty, LotOrder)
VALUES(@ObjectID, @GoodID, @Lot, @LotID, @PriceIn, @Qtty, @newLotOrder)",
helper.Parameters);
}
}
// Revert the old quantity in the store
result = ExecuteNonQuery(@"UPDATE store
SET Qtty = Qtty - @oldGoodsQty
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND Lot = @oldLot AND LotID = @oldLotId AND Price = @oldPrice AND " +
GetQuantityCondition("oldGoodsId", "oldGoodsQty", "oldLocationId", helper, -1),
helper.Parameters);
if (result != 1)
{
string goodsName = ExecuteScalar <string> ("SELECT Name FROM goods WHERE ID = @oldGoodsId",
helper.Parameters);
throw new InsufficientItemAvailabilityException(goodsName);
}
// Delete the row if it has become with 0 quantity
ExecuteNonQuery(@"DELETE FROM store
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND Lot = @oldLot AND LotID = @oldLotId AND Price = @oldPrice AND ABS(Qtty) < 0.0000001", helper.Parameters);
DeleteLot(oldLotId);
}
}
else
{
if (newLocationId != oldLocationId || newGoodsId != oldGoodsId || newGoodsQty != oldGoodsQty)
{
// Set the new quantity in the store
if (!newGoodsQty.IsZero())
{
ExecuteNonQuery("UPDATE store SET Qtty = Qtty + @Qtty WHERE ObjectID = @ObjectID AND GoodID = @GoodID",
helper.Parameters);
}
// Revert the old quantity in the store
if (allowNegativeQty)
{
ExecuteNonQuery("UPDATE store SET Qtty = Qtty - @oldGoodsQty WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId",
helper.Parameters);
}
else
{
result = ExecuteNonQuery(@"UPDATE store SET Qtty = Qtty - @oldGoodsQty
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND " +
GetQuantityCondition("oldGoodsId", "oldGoodsQty", "oldLocationId", helper, -1),
helper.Parameters);
if (result != 1)
{
string goodsName = ExecuteScalar <string> ("SELECT Name FROM goods WHERE ID = @oldGoodsId",
helper.Parameters);
throw new InsufficientItemAvailabilityException(goodsName);
}
}
}
}
} // We are creating new detail information
else
{
if (newGoodsQty.IsZero())
{
return;
}
// Insert the operation row
result = ExecuteNonQuery(string.Format("INSERT INTO operations {0}",
helper.GetColumnsAndValuesStatement(OperationNonDBFields.Union(new [] { DataField.OperationDetailId }).ToArray())), helper.Parameters);
if (result != 1)
{
throw new Exception("Unable to insert operation detail.");
}
result = GetLastAutoId();
helper.SetObjectValue(DataField.OperationDetailId, result);
if (increaseStoreAvailability)
{
ApplyTransferInInsertToStore(helper, usesLots);
}
}
}
private void AddUpdateStockTakingDetail(object stockTakingObject, object stockTakingDetailObjects,
bool allowNegativeQty, DataField priceOutField, ItemsManagementType imt, bool annul)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(stockTakingObject);
helper.AddObject(stockTakingDetailObjects);
bool usesLots = imt != ItemsManagementType.AveragePrice &&
imt != ItemsManagementType.QuickAveragePrice &&
imt != ItemsManagementType.LastPurchasePrice;
// Check if we already have that detail
var oldInfo = ExecuteObject <ObjectsContainer <int, long, long, double> > ("SELECT Sign as Value1, ObjectID as Value2, GoodID as Value3, Qtty as Value4 FROM operations WHERE ID = @ID", helper.Parameters);
// Get the quantity from the new detail
double newGoodsQty = (double)helper.GetObjectValue(DataField.OperationDetailQuantity);
long result;
// We are updating detail information
if (oldInfo != null)
{
int oldSign = oldInfo.Value1;
long oldLocationId = oldInfo.Value2;
long oldGoodsId = oldInfo.Value3;
double oldGoodsQty = oldInfo.Value4;
long newLotId;
string newLot;
double newPrice;
long oldLotId;
string oldLot;
double oldPrice;
GetOperationDetailLotInfo(helper, usesLots, newGoodsQty, out newLotId, out newLot, out newPrice, out oldLotId, out oldLot, out oldPrice);
// unlike other operations, stock-takings are allowed to have zero quantity
if (annul)
{
// Update the operation row with the changes
result = ExecuteNonQuery("DELETE FROM operations WHERE ID = @ID", helper.Parameters);
}
else
{
// Update the operation row with the changes
result = ExecuteNonQuery(string.Format("UPDATE operations {0} WHERE ID = @ID",
helper.GetSetStatement(OperationNonDBFields.Union(new [] { DataField.OperationDetailId }).ToArray())), helper.Parameters);
}
if (result != 1)
{
throw new Exception("Unable to update operation detail.");
}
int sign = (int)helper.GetObjectValue(DataField.OperationDetailSign);
if (sign == 0)
{
return;
}
if (oldSign == 0)
{
oldGoodsQty = 0;
}
helper.AddParameters(new DbParam("oldGoodsQty", oldGoodsQty));
helper.AddParameters(new DbParam("oldLocationId", oldLocationId));
helper.AddParameters(new DbParam("oldGoodsId", oldGoodsId));
ExecuteNonQuery(string.Format("UPDATE goods SET PriceIn = @PriceIn, {0} = @PriceOut WHERE ID = @GoodID", fieldsTable.GetFieldName(priceOutField)),
helper.Parameters);
#region Update location items availability on hand
if (usesLots)
{
if (!newGoodsQty.IsEqualTo(oldGoodsQty) ||
!newPrice.IsEqualTo(oldPrice) || newLot != oldLot || newLotId != oldLotId || annul)
{
// Set the new quantity in the store
if (!newGoodsQty.IsZero())
{
result = ExecuteNonQuery(string.Format(@"UPDATE store
SET Qtty = Qtty + @Qtty
WHERE ObjectID = @ObjectID AND GoodID = @GoodID AND Lot = @Lot AND LotID = @LotID AND ABS(Price - @PriceIn) < {0}", PurchaseCurrencyPrecisionString), helper.Parameters);
if (result == 0)
{
result = ExecuteScalar <long> (@"SELECT IFNULL(MAX(LotOrder), 0)
FROM store
WHERE ObjectID = @ObjectID AND GoodID = @GoodID", helper.Parameters);
helper.AddParameters(new DbParam("newLotOrder", result + 1));
if ((double)helper.GetObjectValue(DataField.OperationDetailQuantity) > 0)
{
ExecuteNonQuery(@"INSERT INTO store (ObjectID, GoodID, Lot, LotID, Price, Qtty, LotOrder)
VALUES(@ObjectID, @GoodID, @Lot, @LotID, @PriceIn, @Qtty, @newLotOrder)", helper.Parameters);
}
}
}
helper.AddParameters(new DbParam("oldPrice", oldPrice));
helper.AddParameters(new DbParam("oldLot", oldLot));
helper.AddParameters(new DbParam("oldLotId", oldLotId));
// a stock-taking may have deleted a lot by nullifying the quantity; check for the lot and restore it if needed
object lot = ExecuteScalar(@"SELECT ID FROM store WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND Lot = @oldLot
AND LotID = @oldLotId AND Price = @oldPrice", helper.Parameters);
if (lot != null)
{
result = ExecuteNonQuery(@"UPDATE store
SET Qtty = Qtty - @oldGoodsQty
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND Lot = @oldLot AND LotID = @oldLotId AND
Price = @oldPrice AND " + GetQuantityCondition("oldGoodsId", "oldGoodsQty", "oldLocationId", helper, -1), helper.Parameters);
}
else
{
if (oldGoodsQty <= 0 || allowNegativeQty)
{
result = ExecuteScalar <long> (@"SELECT IFNULL(MAX(LotOrder), 0)
FROM store
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId", helper.Parameters);
helper.AddParameters(new DbParam("newLotOrder", result + 1));
result = ExecuteNonQuery(@"INSERT INTO store (ObjectID, GoodID, Lot, LotID, Price, Qtty, LotOrder)
VALUES(@oldLocationId, @oldGoodsId, @oldLot, @oldLotId, @oldPrice, @oldGoodsQty, @newLotOrder)", helper.Parameters);
}
else
{
result = 0;
}
}
if (result != 1)
{
string goodsName = ExecuteScalar <string> ("SELECT Name FROM goods WHERE ID = @oldGoodsId",
helper.Parameters);
throw new InsufficientItemAvailabilityException(goodsName);
}
// Delete the row if it has become with 0 quantity
ExecuteNonQuery(@"DELETE FROM store
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND Lot = @oldLot AND LotID = @oldLotId AND Price = @oldPrice AND ABS(Qtty) < 0.0000001", helper.Parameters);
DeleteLot(oldLotId);
}
}
else
{
if (!newGoodsQty.IsEqualTo(oldGoodsQty) || annul)
{
// Set the new price in store
ExecuteNonQuery("UPDATE store SET Price = @PriceIn WHERE GoodID = @GoodID", helper.Parameters);
// Set the new quantity in the store
if (!newGoodsQty.IsZero())
{
ExecuteNonQuery("UPDATE store SET Qtty = Qtty + @Qtty WHERE ObjectID = @ObjectID AND GoodID = @GoodID",
helper.Parameters);
}
// Revert the old quantity in the store
if (allowNegativeQty)
{
ExecuteNonQuery("UPDATE store SET Qtty = Qtty - @oldGoodsQty WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId",
helper.Parameters);
}
else
{
result = ExecuteNonQuery(@"UPDATE store SET Qtty = Qtty - @oldGoodsQty
WHERE ObjectID = @oldLocationId AND GoodID = @oldGoodsId AND " +
GetQuantityCondition("oldGoodsId", "oldGoodsQty", "oldLocationId", helper, -1),
helper.Parameters);
if (result != 1)
{
string goodsName = ExecuteScalar <string> ("SELECT Name FROM goods WHERE ID = @oldGoodsId",
helper.Parameters);
throw new InsufficientItemAvailabilityException(goodsName);
}
}
}
}
#endregion
} // We are creating new detail information
else if (!annul)
{
if (usesLots)
{
long lotId = GetCreateLotId((string)helper.GetObjectValue(DataField.LotSerialNumber),
(DateTime?)helper.GetObjectValue(DataField.LotExpirationDate),
(DateTime?)helper.GetObjectValue(DataField.LotProductionDate),
(string)helper.GetObjectValue(DataField.LotLocation), null);
helper.SetObjectValue(DataField.OperationDetailLotId, lotId);
helper.SetParameterValue(DataField.OperationDetailLotId, lotId);
}
// Insert the operation row
result = ExecuteNonQuery(string.Format("INSERT INTO operations {0}",
helper.GetColumnsAndValuesStatement(OperationNonDBFields.Union(new [] { DataField.OperationDetailId }).ToArray())), helper.Parameters);
if (result != 1)
{
throw new Exception("Unable to insert operation detail.");
}
result = GetLastAutoId();
helper.SetObjectValue(DataField.OperationDetailId, result);
int sign = (int)helper.GetObjectValue(DataField.OperationDetailSign);
if (sign == 0)
{
return;
}
ExecuteNonQuery(string.Format("UPDATE goods SET PriceIn = @PriceIn, {0} = @PriceOut WHERE ID = @GoodID", fieldsTable.GetFieldName(priceOutField)),
helper.Parameters);
#region Update location items availability on hand
if (usesLots)
{
result = ExecuteNonQuery(string.Format(@"UPDATE store
SET Qtty = Qtty + @Qtty
WHERE ObjectID = @ObjectID AND GoodID = @GoodID AND Lot = @Lot AND LotID = @LotID AND ABS(Price - @PriceIn) < {0} AND Qtty + @Qtty + 0.0000001 > 0", PurchaseCurrencyPrecisionString), helper.Parameters);
if (result == 0)
{
newGoodsQty = ExecuteScalar <double> (string.Format(@"SELECT IFNULL(MAX({0} + @Qtty), 0)
FROM store
WHERE ObjectID = @ObjectID AND GoodID = @GoodID AND Lot = @Lot AND LotID = @LotID AND ABS(Price - @PriceIn) < {1}", GetQuantity("GoodID", "ObjectID", helper, -1), PurchaseCurrencyPrecisionString), helper.Parameters);
if (newGoodsQty < 0)
{
throw new InsufficientItemAvailabilityException((string)helper.GetObjectValue(DataField.ItemName));
}
result = ExecuteScalar <long> (@"SELECT IFNULL(MAX(LotOrder), 0)
FROM store
WHERE ObjectID = @ObjectID AND GoodID = @GoodID", helper.Parameters);
helper.AddParameters(new DbParam("newLotOrder", result + 1));
if ((double)helper.GetObjectValue(DataField.OperationDetailQuantity) > 0)
{
ExecuteNonQuery(@"INSERT INTO store (ObjectID, GoodID, Lot, LotID, Price, Qtty, LotOrder)
VALUES(@ObjectID, @GoodID, @Lot, @LotID, @PriceIn, @Qtty, @newLotOrder)", helper.Parameters);
}
}
else
{
// Delete the row if it has become with 0 quantity
ExecuteNonQuery(string.Format(@"DELETE FROM store
WHERE ObjectID = @ObjectID AND GoodID = @GoodID AND Lot = @Lot AND LotID = @LotID AND ABS(Price - @PriceIn) < {0} AND ABS(Qtty) < 0.0000001", PurchaseCurrencyPrecisionString), helper.Parameters);
}
}
else
{
// Set the new quantity in the store
ExecuteNonQuery("UPDATE store SET Price = @PriceIn WHERE GoodID = @GoodID", helper.Parameters);
if (allowNegativeQty)
{
ExecuteNonQuery("UPDATE store SET Qtty = Qtty + @Qtty WHERE GoodID = @GoodID AND ObjectID = @ObjectID",
helper.Parameters);
}
else
{
result = ExecuteNonQuery(string.Format(@"UPDATE store SET Qtty = Qtty + @Qtty
WHERE GoodID = @GoodID AND ObjectID = @ObjectID AND {0} + @Qtty + 0.0000001 > 0" , GetQuantity("GoodID", "ObjectID", helper, -1)),
helper.Parameters);
if (result != 1)
{
string goodsName = ExecuteScalar <string> ("SELECT Name FROM goods WHERE ID = @GoodID", helper.Parameters);
throw new InsufficientItemAvailabilityException(goodsName);
}
}
}
#endregion
}
else
{
throw new Exception("Wrong number of operation details found with the given Id.");
}
}
