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."); } }