public override void AddUpdateCompanyRecord(object companyRecordObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(companyRecordObject);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have that item
long temp = ExecuteScalar <long> ("SELECT count(*) FROM registration WHERE ID = @ID", helper.Parameters);
// We are updating company record
long id;
if (temp == 1)
{
temp = ExecuteNonQuery(string.Format("UPDATE registration {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.CompanyId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add company record with id=\'{0}\'", helper.GetObjectValue(DataField.CompanyId)));
}
id = Convert.ToInt64(helper.GetObjectValue(DataField.CompanyId));
} // We are creating new company record
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO registration {0}",
helper.GetColumnsAndValuesStatement(DataField.CompanyId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add company record with name=\'{0}\'", helper.GetObjectValue(DataField.CompanyName)));
}
id = GetLastAutoId();
helper.SetObjectValue(DataField.CompanyId, id);
}
else
{
throw new Exception("Too many entries with the same ID found in registration table.");
}
// If the newly placed record is default then remove any other default record(s)
if ((int)helper.GetObjectValue(DataField.CompanyDefault) == -1)
{
ExecuteNonQuery(string.Format("UPDATE registration SET IsDefault = 0 WHERE ID != {0}", id));
} // If the newly placed record is not default see if there are other default record(s)
else
{
temp = ExecuteScalar <long> ("SELECT count(*) FROM registration WHERE IsDefault = -1");
if (temp == 0)
{
ExecuteNonQuery("UPDATE registration SET IsDefault = -1 WHERE ID = 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 bool AddUpdatePayment(object paymentObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(paymentObject);
bool ret = false;
// Check if we already have that payment
long temp = ExecuteScalar <long> ("SELECT count(*) FROM payments WHERE ID = @ID", helper.Parameters);
// We are updating payment information
if (temp == 1)
{
// Get the quantity from the new detail
double newQty = (double)helper.GetObjectValue(DataField.PaymentAmount);
if (newQty.IsZero())
{
temp = ExecuteNonQuery("DELETE FROM payments WHERE ID = @ID", helper.Parameters);
if ((OperationType)helper.GetObjectValue(DataField.PaymentOperationType) == OperationType.AdvancePayment)
{
DeleteOperationId(OperationType.AdvancePayment, (long)helper.GetObjectValue(DataField.PaymentOperationId));
}
}
else
{
temp = ExecuteNonQuery(string.Format("UPDATE payments {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.PaymentId, DataField.PartnerName, DataField.LocationName, DataField.PartnerId)),
helper.Parameters);
}
if (temp != 1)
{
throw new Exception("Unable to update operation detail.");
}
} // We are creating new payment information
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO payments {0}",
helper.GetColumnsAndValuesStatement(DataField.PaymentId, DataField.PartnerName, DataField.LocationName, DataField.PartnerId)),
helper.Parameters);
if (temp != 1)
{
throw new Exception("Unable to insert operation detail.");
}
long lastAutoId = GetLastAutoId();
helper.SetObjectValue(DataField.PaymentId, lastAutoId);
ret = true;
}
else
{
throw new Exception("Wrong number of payments found with the given Id.");
}
return(ret);
}
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 void AddUpdateUsersGroup(object groupObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(groupObject);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have that group
long temp = ExecuteScalar <long> ("SELECT count(*) FROM usersgroups WHERE ID = @ID", helper.Parameters);
// We are updating group
if (temp == 1)
{
temp = ExecuteScalar <long> ("SELECT count(*) FROM usersgroups WHERE ID = @ID AND Code = @Code",
helper.Parameters);
// We have changed the parent
if (temp != 1)
{
UsersGroupCalculateCode(helper);
}
temp = ExecuteNonQuery(string.Format("UPDATE usersgroups {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.UsersGroupsId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot update users group with ID={0}", helper.GetObjectValue(DataField.UsersGroupsId)));
}
} // We are creating new group
else if (temp == 0)
{
UsersGroupCalculateCode(helper);
temp = ExecuteNonQuery(string.Format("INSERT INTO usersgroups {0}",
helper.GetColumnsAndValuesStatement(DataField.UsersGroupsId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add users group with name=\'{0}\'", helper.GetObjectValue(DataField.UsersGroupsName)));
}
long id = GetLastAutoId();
helper.SetObjectValue(DataField.UsersGroupsId, id);
}
else
{
throw new Exception("Too many entries with the same ID found in objects table.");
}
transaction.Complete();
}
}
public override void EditAdvancePayment(object payment)
{
using (DbTransaction transaction = new DbTransaction(this)) {
SqlHelper helper = GetSqlHelper();
helper.AddObject(payment);
ExecuteNonQuery(string.Format("UPDATE payments {0} WHERE payments.ID = @ID",
helper.GetSetStatement(DataField.PaymentId, DataField.PartnerName, DataField.LocationName, DataField.PartnerId)),
helper.Parameters);
transaction.Complete();
}
}
public override void AddUpdatePriceRule(object priceRule)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(priceRule);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have this price rule
long count = ExecuteScalar <long> ("SELECT count(*) FROM pricerules WHERE ID = @ID",
helper.Parameters);
int affectedRows;
switch (count)
{
case 0:
affectedRows = ExecuteNonQuery(string.Format("INSERT INTO pricerules {0}",
helper.GetColumnsAndValuesStatement(DataField.PriceRuleId)), helper.Parameters);
if (affectedRows != 1)
{
throw new Exception(string.Format("Cannot add price rule with name=\'{0}\'",
helper.GetObjectValue(DataField.PriceRuleName)));
}
long insertedId = GetLastAutoId();
helper.SetObjectValue(DataField.PriceRuleId, insertedId);
break;
case 1:
affectedRows = ExecuteNonQuery(string.Format("UPDATE pricerules {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.PriceRuleId)), helper.Parameters);
if (affectedRows != 1)
{
throw new Exception(string.Format("Cannot update price rules with ID={0}",
helper.GetObjectValue(DataField.PriceRuleId)));
}
break;
default:
throw new Exception("Too many entries with the same ID found in pricerules table.");
}
transaction.Complete();
}
}
public override void AddUpdatePartner(object partnerObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(partnerObject);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have that item
long temp = ExecuteScalar <long> ("SELECT count(*) FROM partners WHERE ID = @ID", helper.Parameters);
// We are updating item information
if (temp == 1)
{
helper.UpdateTimeStamp = false;
temp = ExecuteNonQuery(string.Format("UPDATE partners {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.PartnerId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot update partner with id=\'{0}\'", helper.GetObjectValue(DataField.PartnerId)));
}
} // We are creating new item information
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO partners {0}",
helper.GetColumnsAndValuesStatement(DataField.PartnerId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add partner with name=\'{0}\'", helper.GetObjectValue(DataField.PartnerName)));
}
temp = GetLastAutoId();
helper.SetObjectValue(DataField.PartnerId, temp);
}
else
{
throw new Exception("Too many entries with the same ID found in partners table.");
}
transaction.Complete();
}
}
public override void AddUpdateConfiguration(object configurationObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(configurationObject);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have that item
long temp = ExecuteScalar <long> ("SELECT count(*) FROM configuration WHERE ID = @ID", helper.Parameters);
// We are updating location
if (temp == 1)
{
temp = ExecuteNonQuery(string.Format("UPDATE configuration {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.ConfigEntryId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot update configuration with ID={0}", helper.GetObjectValue(DataField.ConfigEntryId)));
}
} // We are creating new location
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO configuration {0}",
helper.GetColumnsAndValuesStatement(DataField.ConfigEntryId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add configuration with key=\'{0}\'", helper.GetObjectValue(DataField.ConfigEntryKey)));
}
temp = GetLastAutoId();
helper.SetObjectValue(DataField.ConfigEntryId, temp);
}
else
{
throw new Exception("Too many entries with the same ID found in configuration table.");
}
transaction.Complete();
}
}
public override void AddUpdateVATGroup(object vatGroupObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(vatGroupObject);
using (DbTransaction transaction = new DbTransaction(this)) {
// Check if we already have that goods
long temp = ExecuteScalar <long> ("SELECT count(*) FROM vatgroups WHERE ID = @ID", helper.Parameters);
// We are updating location
if (temp == 1)
{
temp = ExecuteNonQuery(string.Format("UPDATE vatgroups {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.VATGroupId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot update VAT Group with ID={0}", helper.GetObjectValue(DataField.VATGroupId)));
}
} // We are creating new location
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO vatgroups {0}",
helper.GetColumnsAndValuesStatement(DataField.VATGroupId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add VAT Group with name=\'{0}\'", helper.GetObjectValue(DataField.VATGroupName)));
}
temp = GetLastAutoId();
helper.SetObjectValue(DataField.VATGroupId, temp);
}
else
{
throw new Exception("Too many entries with the same ID found in vatgroups table.");
}
transaction.Complete();
}
}
public override bool AddUpdateCashBookEntry(object chashBookEntryObject)
{
SqlHelper helper = GetSqlHelper();
helper.AddObject(chashBookEntryObject);
bool ret = false;
// Check if we already have that entry
long temp = ExecuteScalar <long> ("SELECT count(*) FROM cashbook WHERE ID = @ID", helper.Parameters);
// We are updating cash book information
if (temp == 1)
{
temp = ExecuteNonQuery(string.Format("UPDATE cashbook {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.CashEntryId, DataField.LocationCode, DataField.LocationName, DataField.UserName)), helper.Parameters);
if (temp != 1)
{
throw new Exception("Unable to update cashbook entry.");
}
} // We are creating new cash book entry information
else if (temp == 0)
{
temp = ExecuteNonQuery(string.Format("INSERT INTO cashbook {0}",
helper.GetColumnsAndValuesStatement(DataField.CashEntryId, DataField.LocationCode, DataField.LocationName, DataField.UserName)), helper.Parameters);
if (temp != 1)
{
throw new Exception("Unable to insert cashbook entry.");
}
ret = true;
}
else
{
throw new Exception("Wrong number of cashbook entries found with the given Id.");
}
return(ret);
}
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.");
}
}
