private DeletePermission DeleteLot(long lotId)
{
if (lotId == DefaultLotId)
{
return(DeletePermission.Reserved);
}
using (DbTransaction trans = new DbTransaction(this)) {
DbParam par = new DbParam("lotId", lotId);
long temp = ExecuteScalar <long> ("SELECT count(1) FROM store WHERE LotID = @lotId", par);
if (temp != 0)
{
return(DeletePermission.No);
}
temp = ExecuteScalar <long> ("SELECT count(1) FROM operations WHERE LotID = @lotId", par);
if (temp != 0)
{
return(DeletePermission.No);
}
temp = ExecuteNonQuery("DELETE FROM lots WHERE ID = @lotId", par);
if (temp != 1)
{
return(DeletePermission.No);
}
trans.Complete();
return(DeletePermission.Yes);
}
}
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 AddInvoice(IEnumerable <object> invoiceObjects, int docNumberLen, bool createNewId)
{
SqlHelper helper = GetSqlHelper();
using (DbTransaction transaction = new DbTransaction(this)) {
List <List <DbParam> > parameters = new List <List <DbParam> > ();
foreach (object invoiceObject in invoiceObjects)
{
helper.ChangeObject(invoiceObject, DocumentNonDBFields);
if (createNewId)
{
string invoiceNumber = (string)helper.GetObjectValue(DataField.DocumentNumber);
long invoiceId = long.Parse(invoiceNumber);
if (IsDocumentNumberUsed(invoiceId))
{
throw new InvoiceNumberInUseException();
}
CreateNewDocumentId(invoiceId, docNumberLen);
createNewId = false;
}
parameters.Add(new List <DbParam> (helper.Parameters));
}
BulkInsert("documents", helper.GetColumns(DocumentNonDBFields.Select(f => new DbField(f)).ToArray()), parameters, "Unable to create new invoice.");
transaction.Complete();
}
}
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 CreateDatabase(string dbName, CreateDatabaseType type, Action <double> pCallback)
{
dbName = FilterSqlParameter(dbName);
string script;
int extraCommands = 0;
List <DbParam> parameters = new List <DbParam> ();
switch (type)
{
case CreateDatabaseType.SampleRestaurant:
script = string.Format(GetScript("SampleRestaurant_307.sql.gz", parameters), dbName);
extraCommands = AdjustSampleDatesCommandsNumber;
break;
case CreateDatabaseType.SampleStore:
script = string.Format(GetScript("SampleStore_307.sql.gz", parameters), dbName);
extraCommands = AdjustSampleDatesCommandsNumber;
break;
default:
script = string.Format(GetScript("Blank_307.sql.gz", parameters), dbName);
break;
}
int i = 0;
string [] scriptLines = script.Split(';');
try {
using (DbTransaction transaction = new DbTransaction(this)) {
for (i = 0; i < scriptLines.Length; i++)
{
string line = scriptLines [i];
line = line.Trim(' ', '\n', '\r', '\t', '\xA0');
if (line.Length > 0)
{
ExecuteNonQuery(line, parameters.ToArray());
}
if (pCallback != null)
{
pCallback(((double)(i * 100)) / (scriptLines.Length + extraCommands - 1));
}
}
if (type == CreateDatabaseType.SampleRestaurant ||
type == CreateDatabaseType.SampleStore)
{
AdjustSampleDates(scriptLines.Length, pCallback);
}
EnsurePatchVersion(dbName);
transaction.Complete();
}
} catch (Exception ex) {
throw new SqlSyntaxException(scriptLines [i], i, ex);
}
}
public override void DeleteItem(long id)
{
DbParam par = new DbParam("id", id);
using (DbTransaction transaction = new DbTransaction(this)) {
ExecuteNonQuery("DELETE FROM goods WHERE ID = @id", par);
ExecuteNonQuery("DELETE FROM store WHERE GoodID = @id", par);
transaction.Complete();
}
}
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 DeleteLocation(long locationId)
{
DbParam par = new DbParam("locationId", locationId);
using (DbTransaction transaction = new DbTransaction(this)) {
ExecuteNonQuery("DELETE FROM objects WHERE ID = @locationId", par);
ExecuteNonQuery("DELETE FROM store WHERE ObjectID = @locationId", par);
ExecuteNonQuery("DELETE FROM operations WHERE ObjectID = @locationId AND (operations.PartnerID = 0 OR PartnerID IS NULL)", par);
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 DeletePriceRule(long priceRuleId)
{
DbParam dbParameter = new DbParam("priceRuleId", priceRuleId);
using (DbTransaction transaction = new DbTransaction(this)) {
int priority = ExecuteScalar <int> ("SELECT Priority FROM pricerules WHERE ID = @priceRuleId", dbParameter);
ExecuteNonQuery("DELETE FROM pricerules WHERE ID = @priceRuleId", dbParameter);
ExecuteNonQuery("UPDATE pricerules SET Priority = Priority - 1 WHERE Priority > @priority",
new DbParam("priority", priority));
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 CommitChanges(bool logChanges = false, bool logNewDetailChanges = false, long?mainAvailabilityLocation = null, bool checkAvailability = false)
{
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
// Create a new operation Id if needed);
bool editMode = true;
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
// Set the new operation id
editMode = false;
}
// Save the order in the database
RestaurantOrderDetail reservationDetail = new RestaurantOrderDetail {
ReferenceDocumentId = referenceDocumentId, Note = Note
};
BusinessDomain.DataAccessProvider.AddUpdateReservation(this, reservationDetail);
RestaurantOrder order = GetRestaurantOrder();
if (order.Details.Count > 0)
{
order.Id = id;
order.CommitChanges(logChanges, logNewDetailChanges, mainAvailabilityLocation, checkAvailability);
}
// We have to delete the payment records if there is nothing left
if (editMode && Persons.IsZero())
{
BusinessDomain.DataAccessProvider.DeleteOperationId(OperationType.Reservation, id);
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Void reservation No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
else if (!Persons.IsZero())
{
// Don't update the amount paid if this is not a new sale
if (editMode)
{
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Edit reservation No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
}
IsDirty = false;
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 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 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();
}
}
private void Commit(Payment [] duePayments, Payment [] paidPayments, PriceGroup priceGroup)
{
bool editMode = true;
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
// Create a new operation Id if needed
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
// Set the new operation id
foreach (Payment payment in duePayments)
{
transaction.SnapshotObject(payment);
payment.OperationId = id;
}
foreach (Payment payment in paidPayments)
{
transaction.SnapshotObject(payment);
payment.OperationId = id;
}
editMode = false;
}
// Save the purchase in the database
BusinessDomain.DataAccessProvider.AddUpdatePurchase(this, details.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability, Item.GetPriceGroupField(priceGroup));
InvalidateItemsCache();
CommitPayments(duePayments, paidPayments, editMode, true);
if (editMode)
{
RemoveAllEmptyDetails();
}
transaction.Complete();
}
}
private void Commit(Payment [] duePayments, Payment [] paidPayments, PriceGroup priceGroup, bool annul)
{
bool editMode = true;
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
// Create a new operation Id if needed
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
// Set the new operation id
foreach (Payment payment in duePayments)
{
transaction.SnapshotObject(payment);
payment.OperationId = id;
}
foreach (Payment payment in paidPayments)
{
transaction.SnapshotObject(payment);
payment.OperationId = id;
}
editMode = false;
}
LotsEvaluate(Details);
// Save the stock-taking in the database
IEnumerable <StockTakingDetail> detailsToSave = GetDetailsToSave(annul);
BusinessDomain.DataAccessProvider.AddUpdateStockTaking(this, detailsToSave.ToArray(),
BusinessDomain.AppConfiguration.AllowNegativeAvailability, Item.GetPriceGroupField(priceGroup), annul);
InvalidateItemsCache();
CommitPayments(duePayments, paidPayments, editMode, false);
transaction.Complete();
}
}
public override void Commit()
{
bool editMode = true;
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
// Create a new operation Id if needed);
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
editMode = false;
}
LotsEvaluate(Details);
BusinessDomain.DataAccessProvider.AddUpdateWaste(this, details.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability);
if (editMode && Total.IsZero())
{
BusinessDomain.DataAccessProvider.DeleteOperationId(Data.OperationType.Waste, id);
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Void waste No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
else if (editMode)
{
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Edit waste No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
if (editMode)
{
RemoveAllEmptyDetails();
}
transaction.Complete();
}
}
public void CommitAdvance()
{
bool isNew = id < 0;
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
if (isNew)
{
mode = PaymentMode.Paid;
userId = BusinessDomain.LoggedUser.Id;
operationType = (int)Data.OperationType.AdvancePayment;
sign = 1;
locationId = Location.DefaultId;
}
BusinessDomain.DataAccessProvider.AddAdvancePayment(this);
if (isNew && Type.BaseType == BasePaymentType.Cash)
{
CashBookEntry cashBookEntry = new CashBookEntry(this)
{
OperationNumber = OperationId,
PartnerName = PartnerName,
TurnoverType = TurnoverType.IncomeAdvance,
DescriptionTemplate = Translator.GetString("Advance Payment No. {0}, {1}")
};
cashBookEntry.CommitChanges();
}
transaction.Complete();
}
if (isNew)
{
BusinessDomain.OnPaymentCommited(this);
}
}
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();
}
}
public ComplexRecipe CommitChanges()
{
bool editMode = true;
try {
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
// Create a new operation Id if needed
if (State == OperationState.New || State == OperationState.NewDraft)
{
id = CreateNewId();
editMode = false;
}
// Save the output ComplexRecipe in the database
OperationType = OperationType.ComplexRecipeMaterial;
foreach (ComplexRecipeDetail detail in DetailsMat)
{
detail.Note = name;
}
BusinessDomain.DataAccessProvider.AddUpdateComplexRecipeMat(this, DetailsMat.ToArray());
// Save the input ComplexRecipe in the database
OperationType = OperationType.ComplexRecipeProduct;
foreach (ComplexRecipeDetail detail in DetailsProd)
{
detail.Note = name;
}
BusinessDomain.DataAccessProvider.AddUpdateComplexRecipeProd(this, DetailsProd.ToArray());
if (editMode && Total.IsZero())
{
BusinessDomain.DataAccessProvider.DeleteOperationId(OperationType.ComplexRecipeMaterial, id);
string format = Translator.GetString("Void recipe No.{0} from {1}");
ApplicationLogEntry.AddNew(string.Format(format, GetFormattedOperationNumber(id),
BusinessDomain.GetFormattedDate(date)));
}
else if (editMode)
{
string format = Translator.GetString("Edit recipe No.{0} from {1}");
ApplicationLogEntry.AddNew(string.Format(format, GetFormattedOperationNumber(id),
BusinessDomain.GetFormattedDate(date)));
}
if (editMode)
{
RemoveAllEmptyDetails();
}
transaction.Complete();
}
} catch {
if (!editMode)
{
id = -1;
}
throw;
}
return(this);
}
public virtual void CommitChanges(bool logChanges = false, bool logNewDetailChanges = false, long?mainAvailabilityLocation = null, bool checkAvailability = false)
{
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
IEnumerable <string> changes = logChanges ? GetChanges(logNewDetailChanges, false) : new List <string> ();
Dictionary <long, double> orderedQuantities = new Dictionary <long, double> (details.Count);
if (checkAvailability && mainAvailabilityLocation != null)
{
if (BusinessDomain.AppConfiguration.AutoProduction)
{
AutomaticProduction(mainAvailabilityLocation.Value, locationId);
}
else if (!BusinessDomain.AppConfiguration.AllowNegativeAvailability)
{
foreach (RestaurantOrderDetail orderDetail in details.Where(d => d.DetailId >= 0 || d.Quantity > 0))
{
if (orderedQuantities.ContainsKey(orderDetail.ItemId))
{
orderedQuantities [orderDetail.ItemId] += orderDetail.Quantity;
}
else
{
orderedQuantities.Add(orderDetail.ItemId, orderDetail.Quantity);
}
}
foreach (KeyValuePair <long, double> orderedQuantity in orderedQuantities)
{
if (Item.GetAvailability(orderedQuantity.Key, mainAvailabilityLocation.Value, locationId) < orderedQuantity.Value)
{
throw new InsufficientItemAvailabilityException(details.Find(d => d.ItemId == orderedQuantity.Key).ItemName);
}
}
}
}
if (State == OperationState.New)
{
if (BusinessDomain.AppConfiguration.PrintOrderCodeOnReceipts)
{
id = -10 - BusinessDomain.DataAccessProvider.CreateNewOperationId(operationType, mainAvailabilityLocation ?? Entities.Location.DefaultId, currentState: State);
}
else
{
id = 0;
}
}
foreach (RestaurantOrderDetail detail in details)
{
detail.ReferenceDocumentId = referenceDocumentId;
}
// Save the order in the database
BusinessDomain.DataAccessProvider.AddUpdateRestaurantOrder(this, isDirty ?
details.ToArray() :
details.Where(detail => detail.IsDirty).ToArray());
for (int i = details.Count - 1; i >= 0; i--)
{
if (details [i].Quantity.IsZero())
{
details.RemoveAt(i);
}
}
IsDirty = false;
foreach (string change in changes)
{
ApplicationLogEntry.AddNew(change);
}
transaction.Complete();
}
}
public void CommitChanges(bool increaseStoreAvailability = true, bool increaseStoreAvailabilityOnAnnull = true)
{
bool editMode = true;
try {
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
if (BusinessDomain.AppConfiguration.AutoProduction)
{
AutomaticProduction();
}
// Create a new operation Id if needed);
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
editMode = false;
}
LotsEvaluate(Details);
// Save the output transfer in the database
OperationType = OperationType.TransferOut;
LocationId = SourceLocationId;
bool annulling = editMode && Total.IsZero();
foreach (TransferDetail detail in details)
{
detail.Sign = annulling && !increaseStoreAvailabilityOnAnnull ? 0 : -1;
detail.DetailId = detail.SourceDetailId;
}
BusinessDomain.DataAccessProvider.AddUpdateTransferOut(this, details.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability);
foreach (TransferDetail detail in details)
{
detail.SourceDetailId = detail.DetailId;
}
// Save the input transfer in the database
OperationType = OperationType.TransferIn;
LocationId = TargetLocationId;
foreach (TransferDetail detail in details)
{
detail.Sign = 1;
detail.DetailId = detail.TargetDetailId;
}
BusinessDomain.DataAccessProvider.AddUpdateTransferIn(this, details.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability, increaseStoreAvailability);
foreach (TransferDetail detail in details)
{
detail.TargetDetailId = detail.DetailId;
}
InvalidateItemsCache();
if (annulling)
{
BusinessDomain.DataAccessProvider.DeleteOperationId(OperationType.TransferIn, id);
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Void transfer No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
else if (editMode)
{
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Edit transfer No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
if (editMode)
{
RemoveAllEmptyDetails();
}
transaction.Complete();
}
} finally {
LocationId = SourceLocationId;
}
}
private long GetCreateLotId(string serialNo, DateTime?endDate, DateTime?prodDate, string location, long?lastLotId)
{
string filter = string.Format("SerialNo = @serialNo AND Location = @location AND {0} AND {1}",
endDate.HasValue ? "EndDate = @endDate" : "EndDate IS NULL",
prodDate.HasValue ? "ProductionDate = @prodDate" : "ProductionDate IS NULL");
DbParam [] pars =
{
new DbParam("serialNo", serialNo),
new DbParam("location", location ?? string.Empty),
new DbParam("endDate", endDate),
new DbParam("prodDate", prodDate)
};
using (DbTransaction trans = new DbTransaction(this)) {
long temp;
if (lastLotId.HasValue && lastLotId != DefaultLotId)
{
DbParam lotId = new DbParam("lotId", lastLotId);
temp = ExecuteScalar <long> ("SELECT count(1) FROM store WHERE LotID = @lotId", lotId);
if (temp == 1)
{
temp = ExecuteScalar <long> ("SELECT count(1) FROM operations WHERE LotID = @lotId", lotId);
if (temp == 1)
{
List <DbParam> pars1 = new List <DbParam> (pars)
{
lotId
};
temp = ExecuteNonQuery(string.Format("UPDATE lots SET SerialNo = @serialNo, Location = @location{0}{1} WHERE ID = @lotId",
endDate.HasValue ? ", EndDate = @endDate" : string.Empty,
prodDate.HasValue ? ", ProductionDate = @prodDate" : string.Empty), pars1.ToArray());
if (temp != 1)
{
throw new Exception(string.Format("Cannot change lot with id = \'{0}\' serial=\'{1}\' endDate=\'{2}\' prodDate=\'{3}\' location=\'{4}\'",
lotId, serialNo, endDate, prodDate, location));
}
return(lastLotId.Value);
}
}
}
temp = ExecuteScalar <long> (string.Format("SELECT count(1) FROM lots WHERE {0}", filter), pars);
int ret;
switch (temp)
{
case 1:
ret = ExecuteScalar <int> (string.Format("SELECT ID FROM lots WHERE {0}", filter), pars);
break;
case 0:
temp = ExecuteNonQuery(string.Format("INSERT INTO lots (SerialNo, Location{0}{1}) VALUES(@serialNo, @location{2}{3})",
endDate.HasValue ? ", EndDate" : string.Empty,
prodDate.HasValue ? ", ProductionDate" : string.Empty,
endDate.HasValue ? ", @endDate" : string.Empty,
prodDate.HasValue ? ", @prodDate" : string.Empty), pars);
if (temp != 1)
{
throw new Exception(string.Format("Cannot add lot with serial=\'{0}\' endDate=\'{1}\' prodDate=\'{2}\' location=\'{3}\'",
serialNo, endDate, prodDate, location));
}
ret = (int)GetLastAutoId();
break;
default:
throw new Exception("Too many entries with the same ID found in lots table.");
}
trans.Complete();
return(ret);
}
}
public override void Commit()
{
bool editMode = true;
using (DbTransaction transaction = new DbTransaction(BusinessDomain.DataAccessProvider)) {
transaction.SnapshotObject(this);
if (BusinessDomain.AppConfiguration.AutoProduction)
{
if (AutomaticProduction())
{
RecalculatePrices();
}
}
// Create a new operation Id if needed);
OperationState operationState = State;
if (operationState == OperationState.New || operationState == OperationState.NewDraft || operationState == OperationState.NewPending)
{
id = CreateNewId();
editMode = false;
if (BusinessDomain.AppConfiguration.ItemsManagementUseLots)
{
foreach (ComplexProductionDetail detail in DetailsProd)
{
if (detail.ProductionDate == null)
{
detail.ProductionDate = Date;
}
}
}
}
LotsEvaluate(DetailsMat);
// Save the output ComplexProduction in the database
OperationType = OperationType.ComplexProductionMaterial;
foreach (ComplexProductionDetail detail in DetailsMat)
{
detail.ReferenceDocumentId = id;
detail.ResetSign(-1);
}
BusinessDomain.DataAccessProvider.AddUpdateComplexProductionMat(this, DetailsMat.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability);
// Save the input ComplexProduction in the database
OperationType = OperationType.ComplexProductionProduct;
foreach (ComplexProductionDetail detail in DetailsProd)
{
detail.ReferenceDocumentId = id;
detail.ResetSign(1);
}
BusinessDomain.DataAccessProvider.AddUpdateComplexProductionProd(this, DetailsProd.ToArray(), BusinessDomain.AppConfiguration.AllowNegativeAvailability);
Item.Cache.Clear(DetailsProd.Select(d => d.ItemId));
if (editMode && Total.IsZero())
{
BusinessDomain.DataAccessProvider.DeleteOperationId(OperationType.ComplexProductionMaterial, id);
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Void production No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
else if (editMode)
{
ApplicationLogEntry.AddNew(string.Format(Translator.GetString("Edit production No.{0} from {1}"), GetFormattedOperationNumber(id), BusinessDomain.GetFormattedDate(date)));
}
if (editMode)
{
RemoveAllEmptyDetails();
}
transaction.Complete();
}
}
public override void AddUpdateUserRestriction <T> (IEnumerable <T> restrictionObjects)
{
using (DbTransaction transaction = new DbTransaction(this)) {
var lookup = ExecuteList <ObjectsContainer <string, long, int> > (
"SELECT ControlName as Value1, UserID as Value2, State as Value3 FROM userssecurity")
.ToLookup(v => v.Value1 + "+" + v.Value2.ToString(CultureInfo.InvariantCulture));
List <List <DbParam> > insertParams = new List <List <DbParam> > ();
List <DbParam> deleteParams = new List <DbParam> ();
string insertColumns = null;
SqlHelper helper = GetSqlHelper();
foreach (object restrictionObject in restrictionObjects)
{
helper.ChangeObject(restrictionObject);
string key = string.Format("{0}+{1}",
helper.GetObjectValue(DataField.UsersSecurityControlName),
((long)helper.GetObjectValue(DataField.UsersSecurityUserId)).ToString(CultureInfo.InvariantCulture));
int state = (int)helper.GetObjectValue(DataField.UsersSecurityState);
if (lookup.Contains(key))
{
bool entryFound = false;
foreach (var entry in lookup[key])
{
if (state != entry.Value3 && state == 2 && !entryFound)
{
int temp = ExecuteNonQuery(string.Format("UPDATE userssecurity {0} WHERE ID = @ID",
helper.GetSetStatement(DataField.UsersSecurityId)), helper.Parameters);
if (temp != 1)
{
throw new Exception(string.Format("Cannot update user restriction with id=\'{0}\'",
helper.GetObjectValue(DataField.UsersSecurityId)));
}
}
else if (state != 2 || entryFound)
{
deleteParams.Add(helper.Parameters.First(p => p.ParameterName == "@ID"));
}
entryFound = true;
}
}
else if (state == 2)
{
helper.ChangeObject(restrictionObject, DataField.UsersSecurityId);
insertParams.Add(new List <DbParam> (helper.Parameters));
if (insertColumns == null)
{
insertColumns = helper.GetColumns(DataField.UsersSecurityId);
}
}
}
if (deleteParams.Count > 0)
{
DeleteRestrictions(deleteParams);
}
if (insertColumns != null)
{
BulkInsert("userssecurity", insertColumns, insertParams, "Cannot insert user restrictions");
}
transaction.Complete();
}
}