public List <tbl_Unit> GetAllUnit() { using (var db = _connectionData.OpenDbConnection()) { return(db.Select <tbl_Unit>(p => p.SysHotelID == CommService.GetHotelId()).ToList()); } }
public List <tbl_Store> GetAllStore() { using (var db = _connectionData.OpenDbConnection()) { return(db.Select <tbl_Store>(p => p.SysHotelID == CommService.GetHotelId())); } }
public List <tbl_Product> GetallProducts() { using (var db = _connectionData.OpenDbConnection()) { return(db.Select <tbl_Product>(p => p.UnitID != 0 && p.SysHotelID == CommService.GetHotelId()).ToList()); } }
public List <Vw_TransferOrder> GetALlTransferOrders(PagingModel page, DateTime fromDate, DateTime Todate, string keyword, out int count) { if (page.search == null) { page.search = ""; } // ServiceStackHelper.Help(); // LicenseUtils.ActivatedLicenseFeatures(); //search again //DateTime _fdate; //DateTime _tdate; //DateTime.TryParse(model.Fdate, CultureInfo.GetCultureInfo("vi-vn"), DateTimeStyles.None, out _fdate); //DateTime.TryParse(model.Tdate, CultureInfo.GetCultureInfo("vi-vn"), DateTimeStyles.None, out _tdate); //_tdate = _tdate.AddDays(1); using (var db = _connectionData.OpenDbConnection()) { var query = db.From <Vw_TransferOrder>().Where(p => p.SysHotelID == CommService.GetHotelId() && p.InputDate >= fromDate && p.InputDate <= Todate); if (!string.IsNullOrEmpty(keyword)) { query.Where(x => x.ProductCode.Contains(keyword) || x.ProductName.Contains(keyword) || x.SupplierName.Contains(keyword)); } query.OrderByDescending(x => x.Id); int offset = 0; try { offset = page.offset; } catch { } int limit = 10; //int.Parse(Request.Params["limit"]); try { limit = page.limit; } catch { } var rows = db.Select(query); count = rows.Count; rows = rows.Skip(offset).Take(limit).ToList(); return(rows); } }
public string GetProductCode() { using (var db = _connectionData.OpenDbConnection()) { var query = db.From <tbl_Product>().OrderByDescending(e => e.Id); var obj = db.Select(query).FirstOrDefault(); if (obj == null) { return("FP" + comm.GetHotelCode() + "000001"); } return("FP" + comm.GetHotelCode() + CommService.fitTo6(obj.Id + 1)); } }
/// <summary> /// lấy cấu hình định mức /// </summary> /// <param name="storeId"></param> /// <returns></returns> public List <tbl_StoreProduct_Config> GetStoreProductListConfig(int storeId) { using (var db = _connectionData.OpenDbConnection()) { var query = db.From <tbl_StoreProduct_Config>() .Join <tbl_Store>((p, q) => p.storeid == q.Id) .Where <tbl_Store>(p => p.typeStore == 2 && p.SysHotelID == CommService.GetHotelId()); if (storeId != 0) { query = query.Where(p => p.storeid == storeId); } return(db.Select(query)); } }
public string GetTransferCode() { try { using (var db = _connectionData.OpenDbConnection()) { var holtel = db.Select <tbl_Hotel>(p => p.Id == CommService.GetHotelId()).FirstOrDefault(); var order = db.Select <tbl_TransferOrder>().OrderByDescending(p => p.Id).FirstOrDefault(); var id = order != null ? order.Id + 1 : 1; return(holtel.Code + "CK" + id.ToString("000000000")); } } catch (Exception) { return(DateTime.Now.ToString("ddMMyyHHmmssfff")); } }
/// <summary> /// thực hiện tìm chính sách gia cho 1 loại phòng nào đấy: tính theo ngày check-in (ngày đến thôi) /// </summary> /// <param name="roomid"></param> /// <param name="roomid"></param> /// <returns>Trả về giá theo ngày, đếm tháng</returns> public tbl_RoomPriceLevel GetPrice(int roomTypeID, DateTime dtIn) { DayOfWeek dayOfWeek = dtIn.DayOfWeek; string iDayOfWeek = CommService.ConvertToInt(dayOfWeek).ToString(); //search again using (var db = _connectionData.OpenDbConnection()) { var query = db.From <tbl_RoomPriceLevel>() .Where(e => e.RoomTypeID == roomTypeID && e.Status == true && e.dayOfWeeks.Contains(iDayOfWeek) && (e.dateFrom <= dtIn) && (e.dateTo >= dtIn) ) .OrderByDescending(e => e.Id); tbl_RoomPriceLevel rows = db.Select <tbl_RoomPriceLevel>(query).FirstOrDefault(); return(rows); } }
public bool Xuatkho(tbl_PurchaseOrder order, List <tbl_PurchaseOrderDetail> orderDetails, ref string msg) { using (var db = _connectionData.OpenDbConnection()) { // using (var ts = new TransactionScope()) var tran = db.OpenTransaction(); { try { order.Id = Convert.ToInt32(db.Insert(order, true)); foreach (var item in orderDetails) { item.PurchaseID = order.Id; } db.InsertAll(orderDetails); //Cập nhật tồn kho: //Group theo StoreId var storeIds = orderDetails.Select(p => p.StoreID).Distinct().ToList(); foreach (var storeId in storeIds) { var orderByStore = orderDetails.Where(p => p.StoreID == storeId); //Group theo sản phẩm var productGroup = orderByStore.GroupBy(p => p.itemid, (key, g) => new { ProductId = key, Total = g.Sum(p => p.quantity) }); foreach (var item in productGroup) { var product = db.Select <tbl_Product>( p => p.SysHotelID == CommService.GetHotelId() && p.Id == item.ProductId) .FirstOrDefault(); var existItem = db.Select <tbl_StoreProduct>( p => p.productid == item.ProductId && p.storeid == storeId).FirstOrDefault(); if (existItem == null || existItem.quantity < item.Total) { msg = "Tạo phiếu không thành công. Sản phẩm " + product.Name + " không đủ hàng"; tran.Rollback(); return(false); } else { var log = new tbl_StoreProductLog { typeImportExport = 2, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, storeid = storeId, productid = item.ProductId, storeproductid = existItem.Id, unitid = product.UnitID, quantity = item.Total, }; db.Insert(log); existItem.quantity = existItem.quantity - item.Total; db.Update(existItem); } } } tran.Commit(); return(true); } catch (Exception) { tran.Rollback(); return(false); } } } }
public bool StoreTransfer(tbl_TransferOrder order, List <tbl_TransferOrderDetail> orderDetails) { using (var db = _connectionData.OpenDbConnection()) { using (var ts = db.OpenTransaction()) { try { order.Id = Convert.ToInt32(db.Insert(order, true)); foreach (var item in orderDetails) { item.TransferID = order.Id; } var srcStoreId = orderDetails.FirstOrDefault().FromStoreId; foreach (var item in orderDetails) { var product = db.Select <tbl_Product>( p => p.SysHotelID == CommService.GetHotelId() && p.Id == item.itemid) .FirstOrDefault(); var srcStoreProduct = db.Select <tbl_StoreProduct>( p => p.productid == item.itemid && p.storeid == srcStoreId).FirstOrDefault(); if (srcStoreProduct == null || srcStoreProduct.quantity == 0) { continue; } item.quantity = srcStoreProduct.quantity > item.quantity ? item.quantity : srcStoreProduct.quantity; db.Insert(item); srcStoreProduct.quantity = srcStoreProduct.quantity - item.quantity; db.Update(srcStoreProduct); var existItem = db.Select <tbl_StoreProduct>( p => p.productid == item.itemid && p.storeid == item.StoreID).FirstOrDefault(); if (existItem == null) { existItem = new tbl_StoreProduct { quantity = item.quantity, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, productid = item.itemid, unitid = product.UnitID, storeid = item.StoreID }; var id = db.Insert(existItem, true); //Thêm bản ghi Log var log = new tbl_StoreProductLog { typeImportExport = 3, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, storeid = item.StoreID, productid = item.itemid, storeproductid = Convert.ToInt32(id), unitid = product.UnitID, quantity = item.quantity, fromstoreid = srcStoreId }; db.Insert(log); } else { var log = new tbl_StoreProductLog { typeImportExport = 3, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, storeid = item.StoreID, productid = item.itemid, storeproductid = existItem.Id, unitid = product.UnitID, quantity = item.quantity, fromstoreid = srcStoreId }; db.Insert(log); existItem.quantity = existItem.quantity + item.quantity; db.Update(existItem); } } ts.Commit(); return(true); } catch (Exception) { ts.Rollback(); return(false); } } } }
public bool StoreTransfer(tbl_TransferOrder order, List <tbl_TransferOrderDetail> orderDetails, ref string msg) { using (var db = _connectionData.OpenDbConnection()) { using (var ts = db.OpenTransaction()) { try { order.Id = Convert.ToInt32(db.Insert(order, true)); foreach (var item in orderDetails) { item.TransferID = order.Id; } db.InsertAll(orderDetails); //Cập nhật tồn kho: //Group theo StoreId var srcStoreId = orderDetails.FirstOrDefault().FromStoreId; var storeId = orderDetails.FirstOrDefault().StoreID; // foreach (var storeId in storeIds) var orderByStore = orderDetails.Where(p => p.StoreID == storeId); //Group theo sản phẩm var productGroup = orderByStore.GroupBy(p => p.itemid, (key, g) => new { ProductId = key, Total = g.Sum(p => p.quantity) }); foreach (var item in productGroup) { //trừ tồn kho Kho gốc var srcStoreProduct = db.Select <tbl_StoreProduct>( p => p.productid == item.ProductId && p.storeid == srcStoreId).FirstOrDefault(); var product = db.Select <tbl_Product>( p => p.SysHotelID == CommService.GetHotelId() && p.Id == item.ProductId) .FirstOrDefault(); if (srcStoreProduct == null || srcStoreProduct.quantity < item.Total) { // var product = msg = "Tạo phiếu lỗi. Sản phẩm " + product.Name + " không đủ số lượng trong kho nguồn"; ts.Rollback(); return(false); } srcStoreProduct.quantity = srcStoreProduct.quantity - item.Total; db.Update(srcStoreProduct); var existItem = db.Select <tbl_StoreProduct>( p => p.productid == item.ProductId && p.storeid == storeId).FirstOrDefault(); if (existItem == null) { existItem = new tbl_StoreProduct { quantity = item.Total, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, productid = item.ProductId, unitid = product.UnitID, storeid = storeId }; var id = db.Insert(existItem, true); //Thêm bản ghi Log var log = new tbl_StoreProductLog { typeImportExport = 3, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, storeid = storeId, productid = item.ProductId, storeproductid = Convert.ToInt32(id), unitid = product.UnitID, quantity = item.Total, fromstoreid = srcStoreId }; db.Insert(log); } else { var log = new tbl_StoreProductLog { typeImportExport = 3, SysHotelID = CommService.GetHotelId(), datecreated = DateTime.Now, storeid = storeId, productid = item.ProductId, storeproductid = existItem.Id, unitid = product.UnitID, quantity = item.Total, fromstoreid = srcStoreId }; db.Insert(log); existItem.quantity = existItem.quantity + item.Total; db.Update(existItem); } } ts.Commit(); return(true); } catch (Exception) { ts.Rollback(); return(false); } } } }
public List <Vw_PurchaseOrderDetail> GetAll(PagingModel page, DateTime fromDate, DateTime Todate, string keyword, int type, out int count) { if (page.search == null) { page.search = ""; } using (var db = _connectionData.OpenDbConnection()) { var query = db.From <Vw_PurchaseOrderDetail>().Where(p => p.SysHotelID == CommService.GetHotelId() && p.InputDate >= fromDate && p.InputDate <= Todate.AddDays(1).AddSeconds(-1) && p.TypeOrder == type); if (!string.IsNullOrEmpty(keyword)) { query.Where(x => x.ProductCode.Contains(keyword) || x.ProductName.Contains(keyword) || x.SupplierName.Contains(keyword)); } query.OrderByDescending(x => x.Id); int offset = 0; try { offset = page.offset; } catch { } int limit = 10; //int.Parse(Request.Params["limit"]); try { limit = page.limit; } catch { } var rows = db.Select(query); count = rows.Count; rows = rows.Skip(offset).Take(limit).ToList(); return(rows); } }
public List <Vw_Report_TienPhong> BaoCaoTienPhong(PagingModel page, DateTime fromDate, DateTime Todate, string keyword, out int count, out double total) { if (page.search == null) { page.search = ""; } using (var db = _connectionData.OpenDbConnection()) { var query = db.From <Vw_Report_TienPhong>().Where(p => p.SysHotelID == CommService.GetHotelId() && p.DateCreated >= fromDate && p.DateCreated <= Todate.AddDays(1).AddSeconds(-1)); if (!string.IsNullOrEmpty(keyword)) { query.Where(x => x.Customername.Contains(keyword)); } query.OrderByDescending(x => x.id); int offset = 0; try { offset = page.offset; } catch { } int limit = 10; //int.Parse(Request.Params["limit"]); try { limit = page.limit; } catch { } var rows = db.Select(query); count = rows.Count; total = rows.Sum(p => p.TotalAmount ?? 0); rows = rows.Skip(offset).Take(limit).ToList(); return(rows); } }