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