public List<ItemPrice> GetItemPrices(Int32 id) {
List<ItemPrice> items;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
// 20 last price changes
items = db.ItemPrices.Where(m => m.ItemId == id).OrderByDescending(m => m.DateCreated).Take(20).ToList();
return items;
}
public Tuple<User, Customer, List<String>> Login(String email, String password) {
Tuple<User, Customer, List<String>> retValue;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
User user;
Customer customer;
List<String> modules = new List<String>();
user = db.Users.Where(m => m.Email == email && m.Password == password && m.Active && m.EmailConfirmed).FirstOrDefault();
if (user != null) {
customer = db.Customers.Where(m => m.id == user.CustomerId).FirstOrDefault();
var mods = customer.CustomerModules.Select(m => m.ModulePrices.Select(t => t.Module)).ToList().Select(r => r.Select(d => d.Name)).ToList();
foreach (var mod in mods) {
modules.Add(mod.ToArray()[0]);
}
//menu = db.Menus.Where(m => m.CustomerId == user.CustomerId).FirstOrDefault();
retValue = new Tuple<User, Customer, List<String>>(user, customer, modules);
return retValue;
}
return null;
}
public List<Services.ReportsService.InventoryRecord> InventoryDataSet(DateTime dateFrom, DateTime dateTo) {
try {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
List<Services.ReportsService.InventoryRecord> retVal = null;
retVal = (from ib in db.InventoryBalances
where ib.Date >= dateFrom.Date && ib.Date <= dateTo.Date
let item = ib.Items.Where(m => m.id == ib.AssociatedItemId).FirstOrDefault()
let price = (Decimal)item.ItemPrices.OrderByDescending(m => m.DateCreated).Take(1).Select(m => m.Price).FirstOrDefault()
select new InventoryRecord {
Date = ib.Date.ToShortDateString(),
Item = item.Name,
Price = price,
UOM = item.UOM.ToString(),
Start = ib.Start,
StartTotal = price * ib.Start,
In = ib.In,
InTotal = price * ib.In,
Out = ib.Out,
OutTotal = price * ib.Out,
Total = ib.Saldo,
TotalTotal = price * ib.Saldo
}).ToList();
// LINQ does not understand EnumHelper<Common.UOM>.Parse - need to do it manually
foreach (Services.ReportsService.InventoryRecord var in retVal) {
var.Item = String.Format("{0} ({1})", var.Item, EnumHelper<Common.UOM>.Parse(var.UOM).ToString());
}
return retVal;
}
catch (Exception ex) {
return null;
}
}
public Boolean DeleteCheck(Int32 id) {
IEnumerable<ChecksMenu> checkMenus;
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
Check orderCheck = db.Checks.FirstOrDefault(m => m.id == id);
if (orderCheck != default(Check)) {
checkMenus = db.ChecksMenus.Where(m => m.CheckId == orderCheck.id);
if (checkMenus.Any()) {
foreach (ChecksMenu menu in checkMenus) {
IEnumerable<ChecksMenuProduct> products = db.ChecksMenuProducts.Where(m => m.CheckMenuId == menu.id);
if (products.Any()) {
foreach (ChecksMenuProduct product in products) {
IEnumerable<ChecksMenuProductItem> items = db.ChecksMenuProductItems.Where(m => m.ProductId == product.id);
if (items.Any()) {
db.ChecksMenuProductItems.DeleteAllOnSubmit(items);
}
}
}
db.ChecksMenuProducts.DeleteAllOnSubmit(products);
}
db.ChecksMenus.DeleteAllOnSubmit(checkMenus);
}
db.Checks.DeleteOnSubmit(orderCheck);
}
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteMenu(Int32 id) {
ItemService _itemService = new ItemService();
InventoryService _inventoryService = new InventoryService();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
ChecksMenu menuItem = db.ChecksMenus.FirstOrDefault(m => m.id == id);
if (menuItem != default(ChecksMenu)) {
db.ChecksMenus.DeleteOnSubmit(menuItem);
IEnumerable<ChecksMenuProduct> products = db.ChecksMenuProducts.Where(m => m.CheckMenuId == id);
if (products.Any()) {
db.ChecksMenuProducts.DeleteAllOnSubmit(products);
foreach (ChecksMenuProduct product in products) {
IEnumerable<ChecksMenuProductItem> items = db.ChecksMenuProductItems.Where(m => m.ProductId == product.id);
db.ChecksMenuProductItems.DeleteAllOnSubmit(items);
}
}
}
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public List<Category> GetMenuCategories(Int32 customerId, Common.CategoryType type, Int32 menuId) {
List<CategoryView> retVal;
menuzRusDataContext db;
try {
if (menuId == 0) {
// All
return (List<Category>)GetCategories(customerId, type);
}
db = new menuzRusDataContext(base.connectionString);
retVal = (from category in GetCategories(customerId, type)
select new CategoryView {
id = category.id,
Name = category.Name,
Description = category.Description,
Items = (from item in db.Items
join menuItem in db.MenuItems on item.id equals menuItem.ItemId
where category.id == item.CategoryId && menuItem.MenuId == menuId
select item
).ToEntitySet()
}
).ToList();
return retVal.Cast<Category>().ToList();
}
catch (Exception ex) {
}
return null;
}
public List<SalesRecord> SalesDataSet(DateTime dateFrom, DateTime dateTo) {
try {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
var retVal = (
from tor in db.TableOrders
join c in db.Checks on tor.id equals c.TableOrderId
join u in db.Users on c.UserId equals u.id
where
tor.Status == 3 &&
tor.DateModified.Date >= dateFrom.Date && tor.DateModified.Date <= dateTo.Date
orderby
tor.DateModified descending
select new SalesRecord() {
Check = String.Format("{0}{1}", "#", c.id),
Price = c.Price,
Tax = c.Tax,
Adjustment = c.Adjustment,
Total = c.Price + c.Tax + c.Adjustment,
DateModified = tor.DateModified.ToShortDateString()
}
).ToList();
return retVal;
}
catch (Exception ex) {
return null;
}
}
public Boolean SaveItemProduct(ItemProduct item) {
ItemProduct itemQuery = new ItemProduct();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
if (item.id != 0) {
itemQuery = db.ItemProducts.Where(m => m.id == item.id).FirstOrDefault();
}
if (itemQuery != default(ItemProduct)) {
itemQuery.id = item.id;
itemQuery.Type = item.Type;
itemQuery.ItemId = item.ItemId;
}
if (item.id == 0) {
db.ItemProducts.InsertOnSubmit(itemQuery);
}
db.SubmitChanges();
DeleteItemProductAssociations(itemQuery.id);
foreach (ItemProductAssociation ipa in item.ItemProductAssociations) {
ItemProductAssociation itemsQuery = new ItemProductAssociation();
itemsQuery.ItemProductId = itemQuery.id;
itemsQuery.ItemId = ipa.ItemId;
db.ItemProductAssociations.InsertOnSubmit(itemsQuery);
}
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public List<Module> GetModulesByCustomer(Int32 id) {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
return (from cm in db.CustomerModules
join mp in db.ModulePrices on cm.ModulePriceId equals mp.id
join m in db.Modules on mp.ModuleId equals m.id
where cm.CustomerId == id && cm.EndDate == null
select m).ToList();
}
public Int32 SaveCustomer(Customer customer) {
Customer query = new Customer();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
if (customer.id != 0) {
query = db.Customers.Where(m => m.id == customer.id).FirstOrDefault();
}
if (query != default(Customer)) {
query.Name = customer.Name;
query.Address = customer.Address;
query.Address2 = customer.Address2;
query.City = customer.City;
query.State = customer.State;
query.Phone = customer.Phone.CleanPhone();
query.Zip = customer.Zip;
query.Tax = customer.Tax;
query.ImageUrl = customer.ImageUrl;
}
if (customer.id == 0) {
db.Customers.InsertOnSubmit(query);
}
db.SubmitChanges();
// Update ImageName for a new category
if (customer.id == 0 && query.ImageUrl != null) {
query.ImageUrl = String.Format("{0}{1}", query.id, customer.ImageUrl);
db.SubmitChanges();
}
// Create infrastructure
String path = String.Format("{0}//Images/Menus/{1}", AppDomain.CurrentDomain.BaseDirectory, customer.id);
if (!Directory.Exists(path)) {
DirectoryInfo di = Directory.CreateDirectory(path);
String subpath = String.Format("{0}/Customers", path);
if (!Directory.Exists(subpath)) {
di = Directory.CreateDirectory(subpath);
}
subpath = String.Format("{0}/Users", path);
if (!Directory.Exists(subpath)) {
di = Directory.CreateDirectory(subpath);
}
subpath = String.Format("{0}/Categories", path);
if (!Directory.Exists(subpath)) {
di = Directory.CreateDirectory(subpath);
}
subpath = String.Format("{0}/Items", path);
if (!Directory.Exists(subpath)) {
di = Directory.CreateDirectory(subpath);
}
}
}
}
catch (Exception ex) {
throw ex;
}
return query.id;
}
public List<Module> GetModulesAll() {
try {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
return db.Modules.ToList();
}
catch (Exception ex) {
throw ex;
}
}
public Table GetAlertTable(Int32 tableId) {
Table table;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
table = (from t in db.Tables
where t.id == tableId
select t).FirstOrDefault();
return table;
}
public String GetItemComment(Int32 parentId, Common.CommentType type, Int32 customerId) {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
String[] query = (from com in db.Comments
join cmc in db.CheckMenuComments on com.id equals cmc.CommentId
where com.CustomerId == customerId
&& cmc.ParentId == parentId
&& cmc.Type == (Int32)type
select com.CommentText).ToArray();
return String.Join(" | ", query);
}
public Item GetAlertItem(Int32 id) {
Item item;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
item = (from alert in db.Alerts
join checkMenu in db.ChecksMenus on alert.CheckMenuId equals checkMenu.id
join it in db.Items on checkMenu.MenuId equals it.id
where alert.id == id
select it).FirstOrDefault();
return item;
}
public Check GetAlertCheck(Int32 id) {
Check check;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
check = (from alert in db.Alerts
join checkMenu in db.ChecksMenus on alert.CheckMenuId equals checkMenu.id
join chk in db.Checks on checkMenu.CheckId equals chk.id
where alert.id == id
select chk).FirstOrDefault();
return check;
}
public List<Alert> GetAlerts(Int32 userId) {
List<Alert> alerts;
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
alerts = (from check in db.Checks
join checkMenu in db.ChecksMenus on check.id equals checkMenu.CheckId
join alert in db.Alerts on checkMenu.id equals alert.CheckMenuId
where check.UserId == userId
&& alert.Status == (Int32)Common.Status.Active
select alert).ToList();
return alerts;
}
public Int32 Save(String commentText, Int32 customerId) {
Comment query = new Comment();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query.CommentText = commentText;
query.CustomerId = customerId;
query.DateModified = DateTime.UtcNow;
db.Comments.InsertOnSubmit(query);
db.SubmitChanges();
}
}
catch (Exception ex) {
}
return query.id;
}
public Boolean DeleteItemProductAssociations(Int32 id) {
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
IEnumerable<ItemProductAssociation> query = db.ItemProductAssociations.Where(m => m.ItemProductId == id);
if (query != default(ItemProductAssociation)) {
db.ItemProductAssociations.DeleteAllOnSubmit(query);
}
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public User Confirm(String hash) {
try {
menuzRusDataContext db = new menuzRusDataContext(base.connectionString);
user = db.Users.Where(m => m.Hash == hash).FirstOrDefault();
if (user != default(User)) {
user.Active = true;
user.EmailConfirmed = true;
db.SubmitChanges();
}
}
catch (Exception ex) {
return null;
}
return user;
}
public Boolean AddItemPrice(Int32 id, Decimal price) {
ItemPrice query = new ItemPrice();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query.Price = price;
query.ItemId = id;
db.ItemPrices.InsertOnSubmit(query);
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Int32 SaveComment(Int32 id, Int32 parentId, Common.CommentType type) {
CheckMenuComment query = new CheckMenuComment();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query.Type = (Int32)type;
query.ParentId = parentId;
query.CommentId = id;
db.CheckMenuComments.InsertOnSubmit(query);
db.SubmitChanges();
}
}
catch (Exception ex) {
}
return query.id;
}
public Boolean DeleteComment(Int32 id, Int32 parentId, Common.CommentType type) {
CheckMenuComment query;
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query = db.CheckMenuComments.FirstOrDefault(m => m.CommentId == id && m.ParentId == parentId && m.Type == (Int32)type);
if (query != default(CheckMenuComment)) {
db.CheckMenuComments.DeleteOnSubmit(query);
db.SubmitChanges();
}
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteInventoryAssociation(Int32 id) {
ItemInventoryAssociation item = new ItemInventoryAssociation();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
item = db.ItemInventoryAssociations.Where(m => m.id == id).FirstOrDefault();
if (item != default(ItemInventoryAssociation)) {
db.ItemInventoryAssociations.DeleteOnSubmit(item);
db.SubmitChanges();
}
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteTable(Int32 id) {
Table query = new Table();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query = db.Tables.FirstOrDefault(m => m.id == id);
if (query != default(Table)) {
query.Status = (Int32)Common.Status.NotActive;
db.SubmitChanges();
}
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean AddItemRegistry(Int32 id, Decimal qty, Common.InventoryType type, String comment) {
InventoryRegistry query = new InventoryRegistry();
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query.Quantity = qty;
query.Type = (Int32)type;
query.AssociatedItemId = id;
query.Comment = comment;
db.InventoryRegistries.InsertOnSubmit(query);
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteUser(Int32? id) {
User query = new User();
id = id.HasValue ? id : 0;
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query = db.Users.Where(m => m.id == id).FirstOrDefault();
if (query != default(User)) {
db.Users.DeleteOnSubmit(query);
}
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteCategory(Int32? id) {
Category query = new Category();
id = id.HasValue ? id : 0;
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query = db.Categories.Where(m => m.id == id).FirstOrDefault();
if (query != default(Category)) {
query.Status = (Int32)Common.Status.NotActive;
db.SubmitChanges();
}
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean AddInventoryRegistryCheckMenu(Int32 registryId, Int32 checkMenuId) {
InventoryRegistryCheckMenu item;
try {
item = new InventoryRegistryCheckMenu();
item.ChecksMenuId = checkMenuId;
item.InventoryRegistryId = registryId;
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
db.InventoryRegistryCheckMenus.InsertOnSubmit(item);
db.SubmitChanges();
}
}
catch (Exception ex) {
return false;
}
return true;
}
public Boolean DeleteItem(Int32? id) {
Item query;
id = id.HasValue ? id : 0;
try {
query = new Item();
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
query = db.Items.FirstOrDefault(m => m.id == id);
if (query != default(Item)) {
query.Status = (Int32)Common.Status.NotActive;
db.SubmitChanges();
}
}
}
catch (Exception ex) {
return false;
}
return true;
}
private void SendToLogger(Common.LogType type, Int32 userId, String sessionId, String trace, String route, params Object[] data) {
try {
using (menuzRusDataContext db = new menuzRusDataContext(base.connectionString)) {
Log log = new Log();
log.IP = Common.GetIP();
log.LogType = (Int32)type;
log.UserId = userId;
log.SessionId = sessionId != null ? sessionId : "N/A";
log.Trace = String.Format("{0}{1}", trace, BuildParameters(data));
log.Route = route;
db.Logs.InsertOnSubmit(log);
db.SubmitChanges();
}
}
catch (Exception ex) {
throw ex;
}
}