public Customer GetCustomerByNumber(string number)
{
using (var connectionScope = new ConnectionScope())
{
var where = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
where.Predicates.Add(Predicates.Field<Customer>(f => f.Number, Operator.Eq, number));
return connectionScope.Connection.GetList<Customer>(where.Predicates.Any() ? where : null).FirstOrDefault();
}
}
public void Dispose()
{
if (Current == this)
{
Connection.Close();
Connection = null;
Current = null;
}
}
public ConnectionScope()
{
if (Current == null)
{
Current = this;
var config = ConfigurationManager.ConnectionStrings["Shop"];
Connection = new SqlConnection(config != null ? config.ConnectionString : @"Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Shop;Integrated Security=True;Pooling=False");
Connection.Open();
}
}
public ConnectionScope()
{
if (Current == null)
{
Current = this;
var config = ConfigurationManager.ConnectionStrings["Shop"];
Connection = new SqlConnection(config != null ? config.ConnectionString : @"Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Shop;Integrated Security=True;Pooling=False");
Connection.Open();
}
}
public DiscountModel GetDiscountModel(Guid id)
{
using (var connectionScope = new ConnectionScope())
{
var result = new DiscountModel();
result.Discount = connectionScope.Connection.Get<Discount>(id);
result.Products = connectionScope.Connection.GetList<DiscountProduct>(Predicates.Field<DiscountProduct>(f => f.DiscountId, Operator.Eq, id)).ToList();
result.Customers = connectionScope.Connection.GetList<DiscountCustomer>(Predicates.Field<DiscountCustomer>(f => f.DiscountId, Operator.Eq, id)).ToList();
return result;
}
}
public static void Insert <T>(this IEnumerable <T> entities)
where T : class
{
using (var transaction = new TransactionScope())
using (var connection = new ConnectionScope())
{
foreach (var entity in entities)
{
connection.Connection.Insert(entity);
}
transaction.Complete();
}
}
public IEnumerable<DiscountProduct> GetDiscountProductsByCustomerId(Guid customerId)
{
using (var connectionScope = new ConnectionScope())
{
var sql = @"
select
*
from
[DiscountProduct] dp
join [DiscountCustomer] dc on dc.DiscountId = dp.DiscountId and dc.CustomerId = @CustomerId";
return connectionScope.Connection.Query<DiscountProduct>(sql, new { CustomerId = customerId }).ToList();
}
}
public void AddTransaction(CustomerTransaction customerTransaction)
{
using (var transaction = new TransactionScope())
using (var connection = new ConnectionScope())
{
var customer = GetCustomer(customerTransaction.CustomerId);
if (customer == null)
throw new Exception("Customer not found");
customerTransaction.Insert();
customer.Balance += customerTransaction.Amount;
customer.Update();
transaction.Complete();
}
}
public DiscountModel InsertDiscountModel(DiscountModel entity)
{
// TODO - Make this more efficient.
using (var connectionScope = new ConnectionScope())
{
connectionScope.Connection.Insert<Discount>(entity.Discount);
foreach (var product in entity.Products)
product.DiscountId = entity.Discount.Id;
foreach (var customer in entity.Customers)
customer.DiscountId = entity.Discount.Id;
connectionScope.Connection.Insert<DiscountProduct>(entity.Products);
connectionScope.Connection.Insert<DiscountCustomer>(entity.Customers);
}
return entity;
}
public void AddMovement(ProductMovement movement)
{
using (var transaction = new TransactionScope())
using (var connection = new ConnectionScope())
{
var product = GetProduct(movement.ProductId);
if (product == null)
throw new Exception("Product not found");
if (product.QuantityOnHand + movement.Quantity < 0)
throw new Exception("Quantity on hand cannot become negative.");
movement.Insert();
product.QuantityOnHand += movement.Quantity;
product.Update();
transaction.Complete();
}
}
public DiscountModel UpdateDiscountModel(DiscountModel entity)
{
// TODO - Make this more efficient.
using (var connectionScope = new ConnectionScope())
{
connectionScope.Connection.Delete<DiscountProduct>(Predicates.Field<DiscountProduct>(f => f.DiscountId, Operator.Eq, entity.Discount.Id));
connectionScope.Connection.Delete<DiscountCustomer>(Predicates.Field<DiscountCustomer>(f => f.DiscountId, Operator.Eq, entity.Discount.Id));
connectionScope.Connection.Update<Discount>(entity.Discount);
foreach (var product in entity.Products)
product.DiscountId = entity.Discount.Id;
foreach (var customer in entity.Customers)
customer.DiscountId = entity.Discount.Id;
connectionScope.Connection.Insert<DiscountProduct>(entity.Products);
connectionScope.Connection.Insert<DiscountCustomer>(entity.Customers);
}
return entity;
}
public IEnumerable<ProductMovement> GetProductMovements(Guid productId)
{
using (var connection = new ConnectionScope())
{
var sql = @"
select
Id,
ProductId,
MovementType,
Quantity,
DateTime,
SourceId,
SourceItemNumber
from
dbo.ProductMovement
where
ProductId = @ProductId
order by
DateTime desc,
MovementType";
return connection.Connection.Query<ProductMovement>(sql, new { ProductId = productId });
}
}
public void AddInvoice(Invoice invoice, IEnumerable<InvoiceItem> items, decimal payment)
{
using (var transaction = new TransactionScope())
using (var connection = new ConnectionScope())
{
// Insert Invoice.
invoice.Insert();
foreach (var item in items)
item.InvoiceId = invoice.Id;
items.Insert();
// Update Inventory.
foreach (var item in items)
{
var product = ProductManager.GetProduct(item.ProductId);
if (product == null)
throw new Exception("Product not found.");
// Stock Correction?
if (product.QuantityOnHand < item.Quantity)
{
ProductManager.AddMovement(new ProductMovement
{
Id = Guid.NewGuid(),
ProductId = item.ProductId,
DateTime = invoice.DateTime,
MovementType = ProductMovementType.Correction,
Quantity = item.Quantity - product.QuantityOnHand,
SourceId = invoice.Id,
SourceItemNumber = item.ItemNumber
});
}
// Movement.
ProductManager.AddMovement(new ProductMovement
{
Id = Guid.NewGuid(),
ProductId = item.ProductId,
DateTime = invoice.DateTime,
MovementType = ProductMovementType.Invoice,
Quantity = item.Quantity * -1,
SourceId = invoice.Id,
SourceItemNumber = item.ItemNumber
});
}
// Add Customer Transaction.
CustomerManager.AddTransaction(new CustomerTransaction()
{
Id = Guid.NewGuid(),
CustomerId = invoice.CustomerId,
DateTime = invoice.DateTime,
Type = CustomerTransactionType.Invoice,
Amount = items.Aggregate(0.0m, (total, item) => total += item.Quantity * Math.Round(item.Price * (100 - item.Discount) / 100, 2, MidpointRounding.AwayFromZero)),
SourceId = invoice.Id
});
// Payment?
if (payment > 0)
{
CustomerManager.AddTransaction(new CustomerTransaction()
{
Id = Guid.NewGuid(),
CustomerId = invoice.CustomerId,
DateTime = invoice.DateTime,
Type = CustomerTransactionType.Payment,
Amount = payment * -1,
SourceId = invoice.Id
});
}
transaction.Complete();
}
}
public IEnumerable<InvoiceItem> GetInvoiceItems(Guid invoiceId)
{
using (var connection = new ConnectionScope())
{
return connection.Connection.GetList<InvoiceItem>(new { InvoiceId = invoiceId }).ToList();
}
}
public IEnumerable<dynamic> GetInvoiceItemHistory(DateTimeOffset startDateTime)
{
using (var connection = new ConnectionScope())
{
var sql = @"
select
invoice.DateTime,
customer.Name as CustomerName,
product.Description as ProductDescription,
item.Quantity,
item.Price,
item.Discount
from
dbo.Invoice invoice
join dbo.Customer customer on customer.Id = invoice.CustomerId
join dbo.InvoiceItem item on item.InvoiceId = invoice.Id
join dbo.Product product on product.Id = item.ProductId
where
invoice.DateTime >= @StartDateTime
order by
invoice.DateTime desc,
item.ItemNumber";
return connection.Connection.Query(sql, new { StartDateTime = startDateTime });
}
}
public void AddReceipt(Invoice invoice, IEnumerable<InvoiceItem> items)
{
using (var transaction = new TransactionScope())
using (var connection = new ConnectionScope())
{
if (invoice.Id == Guid.Empty)
invoice.Id = Guid.NewGuid();
// Update Inventory.
foreach (var item in items)
{
var product = ProductManager.GetProduct(item.ProductId);
if (product == null)
throw new Exception("Product not found.");
// Stock Correction?
// TODO.
// Movement.
ProductManager.AddMovement(new ProductMovement
{
Id = Guid.NewGuid(),
ProductId = item.ProductId,
DateTime = invoice.DateTime,
MovementType = ProductMovementType.Receipt,
Quantity = item.Quantity,
SourceId = invoice.Id,
SourceItemNumber = item.ItemNumber
});
}
// Add Customer Transaction.
CustomerManager.AddTransaction(new CustomerTransaction()
{
Id = Guid.NewGuid(),
CustomerId = invoice.CustomerId,
DateTime = invoice.DateTime,
Type = CustomerTransactionType.Payment,
Amount = items.Aggregate(0.0m, (total, item) => total += item.Quantity * item.Price) * -1.0m,
SourceId = invoice.Id
});
transaction.Complete();
}
}
public IEnumerable<CustomerTransaction> GetTransactions(Guid? customerId, DateTimeOffset? fromDate, DateTimeOffset? toDate)
{
using (var connectionScope = new ConnectionScope())
{
var where = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
if (customerId.HasValue)
where.Predicates.Add(Predicates.Field<CustomerTransaction>(f => f.CustomerId, Operator.Eq, customerId.Value));
if (fromDate.HasValue)
where.Predicates.Add(Predicates.Field<CustomerTransaction>(f => f.DateTime, Operator.Ge, fromDate.Value));
if (toDate.HasValue)
where.Predicates.Add(Predicates.Field<CustomerTransaction>(f => f.DateTime, Operator.Le, toDate.Value));
return connectionScope.Connection.GetList<CustomerTransaction>(where.Predicates.Any() ? where : null).ToList();
}
}
