public override void EnableLots() { using (DbTransaction transaction = new DbTransaction(this)) { ItemsManagementType imt = GetItemsManagementType(); if (imt != ItemsManagementType.AveragePrice && imt != ItemsManagementType.QuickAveragePrice && imt != ItemsManagementType.LastPurchasePrice) { return; } long unavailable = ExecuteScalar <long> ("SELECT count(1) FROM store WHERE Qtty < 0"); if (unavailable > 0) { throw new InsufficientItemAvailabilityException(null); } string mixedPriceInItem = ExecuteScalar <string> (@"SELECT goods.Name FROM operations as op INNER JOIN store ON op.GoodID = store.GoodID AND op.PriceIn <> store.Price INNER JOIN goods ON op.GoodID = goods.ID LIMIT 1"); if (!string.IsNullOrWhiteSpace(mixedPriceInItem)) { throw new MixedPriceInItemException(mixedPriceInItem); } ExecuteNonQuery("DELETE FROM store WHERE ABS(Qtty) < 0.0000001"); ExecuteNonQuery("UPDATE operations SET Lot = 'NA'"); ExecuteNonQuery("UPDATE store SET Lot = 'NA', LotOrder = 1"); transaction.Complete(); } }
public override void AddUpdateTransferIn(object transferObject, object [] transferDetailObjects, bool allowNegativeQty, bool increaseStoreAvailability) { ItemsManagementType type = GetItemsManagementType(); SqlHelper helper = GetSqlHelper(); helper.AddObject(transferObject); long id = (long)helper.GetObjectValue(DataField.OperationNumber); if (id < 0) { foreach (object detail in transferDetailObjects) { AddUpdateDetail(transferObject, detail, allowNegativeQty, type); } } else { foreach (object detail in transferDetailObjects) { AddUpdateTransferInDetail(transferObject, detail, allowNegativeQty, type, increaseStoreAvailability); } } }
public override void AddUpdateTransferOut(object transferObject, object [] transferDetailObjects, bool allowNegativeQty) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in transferDetailObjects) { AddUpdateDetail(transferObject, detail, allowNegativeQty, type); } }
public override void AddUpdatePurchase(object purchaseObject, object [] purchaseDetailObjects, bool allowNegativeQty, DataField priceOutField) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in purchaseDetailObjects) { AddUpdateDetail(purchaseObject, detail, allowNegativeQty, type, priceOutField); } }
public override void AddUpdateComplexProductionProd(object obj, object [] detailObj, bool allowNegativeQty) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in detailObj) { AddUpdateDetail(obj, detail, allowNegativeQty, type); } }
public override void AddUpdateWaste(object wasteObject, object [] wasteDetailObjects, bool allowNegativeQty) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in wasteDetailObjects) { AddUpdateDetail(wasteObject, detail, allowNegativeQty, type); } }
public override void AddUpdateSale(object saleObject, object [] saleDetailObjects, bool allowNegativeQty, long childLocationId) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in saleDetailObjects) { AddUpdateDetail(saleObject, detail, allowNegativeQty, type, childLocationId: childLocationId); } }
public override void AddUpdateStockTaking(object stockTakingObject, object [] stockTakingDetailObjects, bool allowNegativeQty, DataField priceOutField, bool annul) { ItemsManagementType type = GetItemsManagementType(); foreach (object detail in stockTakingDetailObjects) { AddUpdateStockTakingDetail(stockTakingObject, detail, allowNegativeQty, priceOutField, type, annul); } }
public override void AddUpdateItem(object itemObject) { SqlHelper helper = GetSqlHelper(); helper.AddObject(itemObject); using (DbTransaction transaction = new DbTransaction(this)) { // Check if we already have that item long temp = ExecuteScalar <long> ("SELECT count(*) FROM goods WHERE ID = @ID", helper.Parameters); // We are updating item information if (temp == 1) { temp = ExecuteNonQuery(string.Format("UPDATE goods {0} WHERE ID = @ID", helper.GetSetStatement(DataField.ItemId, DataField.StoreQtty)), helper.Parameters); if (temp != 1) { throw new Exception(string.Format("Cannot update goods with ID={0}", helper.GetObjectValue(DataField.ItemId))); } } // We are creating new item information else if (temp == 0) { temp = ExecuteNonQuery(string.Format("INSERT INTO goods {0}", helper.GetColumnsAndValuesStatement(DataField.ItemId, DataField.StoreQtty)), helper.Parameters); if (temp != 1) { throw new Exception(string.Format("Cannot add goods with name=\'{0}\'", helper.GetObjectValue(DataField.ItemName))); } temp = GetLastAutoId(); helper.SetObjectValue(DataField.ItemId, temp); ItemsManagementType imt = GetItemsManagementType(); if (imt == ItemsManagementType.AveragePrice || imt == ItemsManagementType.QuickAveragePrice || imt == ItemsManagementType.LastPurchasePrice) { // Add store availability for the items in the new location ExecuteNonQuery("INSERT INTO store (ObjectID, GoodID, Qtty, Price, Lot, LotID, LotOrder) SELECT ID, @GoodID, 0, 0, ' ', 1, 1 FROM objects", new DbParam("GoodID", temp)); } } else { throw new Exception("Too many entries with the same ID found in goods table."); } transaction.Complete(); } }
public override LazyListModel <T> GetLots <T> (long itemId, long?locationId, ItemsManagementType imt) { DbParam [] pars = { new DbParam("itemId", itemId), new DbParam("locationId", locationId ?? -1) }; string query = string.Format(@"SELECT s.Qtty as {0}, s.Price as {1}, s.Lot as {2}, l.ID as {3}, l.SerialNo as {4}, l.EndDate as {5}, l.ProductionDate as {6}, l.Location as {7} FROM store as s LEFT JOIN lots as l ON s.LotID = l.ID WHERE s.GoodID = @itemId{8} {9} ORDER BY {{0}}", fieldsTable.GetFieldAlias(DataField.StoreQtty), fieldsTable.GetFieldAlias(DataField.StorePrice), fieldsTable.GetFieldAlias(DataField.StoreLot), fieldsTable.GetFieldAlias(DataField.LotId), fieldsTable.GetFieldAlias(DataField.LotSerialNumber), fieldsTable.GetFieldAlias(DataField.LotExpirationDate), fieldsTable.GetFieldAlias(DataField.LotProductionDate), fieldsTable.GetFieldAlias(DataField.LotLocation), locationId.HasValue ? " AND s.ObjectID = @locationId" : string.Empty, locationId.HasValue ? string.Empty : "GROUP BY LotID, Lot"); switch (imt) { case ItemsManagementType.AveragePrice: case ItemsManagementType.QuickAveragePrice: case ItemsManagementType.LastPurchasePrice: return(new LazyListModel <T> ()); case ItemsManagementType.FIFO: case ItemsManagementType.Choice: return(ExecuteLazyModel <T> (string.Format(query, "s.LotOrder ASC"), pars)); case ItemsManagementType.LIFO: return(ExecuteLazyModel <T> (string.Format(query, "s.LotOrder DESC"), pars)); case ItemsManagementType.FEFO: return(ExecuteLazyModel <T> (string.Format(query, "l.EndDate ASC"), pars)); default: throw new ArgumentOutOfRangeException("imt"); } }
public override void DisableLots() { using (DbTransaction transaction = new DbTransaction(this)) { ItemsManagementType imt = GetItemsManagementType(); if (imt == ItemsManagementType.AveragePrice || imt == ItemsManagementType.QuickAveragePrice || imt == ItemsManagementType.LastPurchasePrice) { return; } long operId = CreateNewOperationId(OperationType.Temp, -1); DbParam [] pars = { new DbParam("operType", (int)OperationType.Temp), new DbParam("operId", operId) }; ExecuteNonQuery(@" INSERT INTO operations (OperType, Acct, ObjectID, GoodID, Qtty) SELECT @operType, @operId, ObjectID, GoodID, SUM(Qtty) FROM store GROUP BY ObjectID, GoodID", pars); ExecuteNonQuery("DELETE FROM store"); ExecuteNonQuery(@" INSERT INTO store (ObjectID, GoodID, Qtty, Price, Lot, LotID, LotOrder) SELECT o.ID, g.ID, SUM(IFNULL(op.Qtty, 0)), g.PriceIn, ' ', 1, 1 FROM (goods as g, objects as o) LEFT JOIN operations as op ON op.GoodID = g.ID AND op.ObjectID = o.ID AND op.OperType = @operType AND op.Acct = @operId GROUP BY o.ID, g.ID", pars); ExecuteNonQuery("DELETE FROM operations WHERE OperType = @operType AND Acct = @operId", pars); DeleteOperationId(OperationType.Temp, operId); ExecuteNonQuery("UPDATE operations SET Lot = ' ', LotID = 1"); ExecuteNonQuery("DELETE FROM lots WHERE ID <> 1"); transaction.Complete(); } }
public override T GetItemBySerialNumber <T> (string serial, long locationId, ItemsManagementType imt) { if (string.IsNullOrWhiteSpace(serial)) { return(default(T)); } DbParam [] pars = { new DbParam("serial", serial), new DbParam("locationId", locationId) }; string query = string.Format(@" SELECT {0}, store.ID as {1} FROM (store INNER JOIN lots ON store.LotID = lots.ID) INNER JOIN goods ON store.GoodID = goods.ID WHERE store.ObjectID = @locationId AND lots.SerialNo = @serial AND goods.Deleted <> -1 ORDER BY {{0}} LIMIT 1", ItemDefaultAliases, fieldsTable.GetFieldAlias(DataField.StoreQtty)); switch (imt) { case ItemsManagementType.AveragePrice: case ItemsManagementType.QuickAveragePrice: case ItemsManagementType.LastPurchasePrice: return(default(T)); case ItemsManagementType.FIFO: case ItemsManagementType.Choice: return(ExecuteObject <T> (string.Format(query, "store.LotOrder ASC"), pars)); case ItemsManagementType.LIFO: return(ExecuteObject <T> (string.Format(query, "store.LotOrder DESC"), pars)); case ItemsManagementType.FEFO: return(ExecuteObject <T> (string.Format(query, "lots.EndDate ASC"), pars)); default: throw new ArgumentOutOfRangeException("imt"); } }
public override void AddUpdateLocation(object locationObject, bool documentNumbersPerLocation, long recommendedRange) { SqlHelper helper = GetSqlHelper(); helper.AddObject(locationObject); using (DbTransaction transaction = new DbTransaction(this)) { // Check if we already have that item long temp = ExecuteScalar <long> ("SELECT count(*) FROM objects WHERE ID = @ID", helper.Parameters); // We are updating location if (temp == 1) { temp = ExecuteNonQuery(string.Format("UPDATE objects {0} WHERE ID = @ID", helper.GetSetStatement(DataField.LocationId)), helper.Parameters); if (temp != 1) { throw new Exception(string.Format("Cannot update location with ID={0}", helper.GetObjectValue(DataField.LocationId))); } } // We are creating new location else if (temp == 0) { temp = ExecuteNonQuery(string.Format("INSERT INTO objects {0}", helper.GetColumnsAndValuesStatement(DataField.LocationId)), helper.Parameters); if (temp != 1) { throw new Exception(string.Format("Cannot add location with name=\'{0}\'", helper.GetObjectValue(DataField.LocationName))); } temp = GetLastAutoId(); helper.SetObjectValue(DataField.LocationId, temp); ItemsManagementType imt = GetItemsManagementType(); if (imt == ItemsManagementType.AveragePrice || imt == ItemsManagementType.QuickAveragePrice || imt == ItemsManagementType.LastPurchasePrice) { // Add store availability for the items in the new location ExecuteNonQuery("INSERT INTO store (ObjectID, GoodID, Qtty, Price, Lot, LotID, LotOrder) SELECT @ObjectID, ID, 0, PriceIn, ' ', 1, 1 FROM goods", new DbParam("ObjectID", temp)); } if (documentNumbersPerLocation) { List <long> documentNumbers = ExecuteList <long> (@" SELECT Acct FROM operations WHERE PartnerID = 0 OR PartnerID IS NULL GROUP BY ObjectID ORDER BY Acct DESC"); if (documentNumbers.Count > 1) { long maxRangeSize = long.MinValue; for (int i = 0; i < documentNumbers.Count - 1; i++) { maxRangeSize = Math.Max(maxRangeSize, documentNumbers [i] - documentNumbers [i + 1]); } AddOperationStartNumbersPerLocation(temp, documentNumbers [0] + maxRangeSize, null); } else { long locationIndex = ExecuteScalar <long> ("SELECT COUNT(*) - 1 FROM objects"); long rangeStart = documentNumbers.Count == 1 ? documentNumbers [0] : 0; AddNumberingPerLocationByIndex(temp, locationIndex - 1, rangeStart + 1, null, recommendedRange); } } OnLocationAdded(new LocationAddedArgs { LocationId = temp }); } else { throw new Exception("Too many entries with the same ID found in objects table."); } transaction.Complete(); } }
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."); } }