//public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, string SONumber) //{ // log4net.Config.XmlConfigurator.Configure(); // string whereCondition = ""; // if (request.Filters.Count > 0) // { // whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]); // } // var data = new DC_AD_SO_Detail().GetPage(request, whereCondition, SONumber); // return Json(data); //} public ActionResult Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <Products> list, string SONumber) { var dbConn = new OrmliteConnection().openConn(); try { if (list != null && ModelState.IsValid) { foreach (var item in list) { if (item.Qty > 0) { //string SONumber = Request["SONumber"]; var header = new SOHeader(); var detail = new SODetail(); if (dbConn.Select <SODetail>(s => s.SONumber == SONumber && s.ItemCode == item.Code).Count() > 0) { dbConn.Update <SODetail>(set: "Qty = '" + item.Qty + "', TotalAmt = '" + item.Qty * item.Price + "'", where : "SONumber = '" + SONumber + "'"); } else { var data = new SODetail(); data.ItemName = item.Name; data.ItemCode = item.Code; data.Note = ""; data.Price = item.VATPrice; data.Qty = item.Qty; data.SONumber = SONumber; data.UnitID = item.Unit; data.UnitName = dbConn.Select <INUnit>(s => s.UnitID == item.Unit).FirstOrDefault().UnitName; data.TotalAmt = item.Qty * item.VATPrice; data.Status = ""; data.CreatedAt = DateTime.Now; data.CreatedBy = currentUser.UserID; //data.UpdatedAt = DateTime.Parse("1900-01-01"); data.UpdatedAt = DateTime.Now; data.UpdatedBy = ""; dbConn.Insert <SODetail>(data); } dbConn.Update <SOHeader>(set: "TotalQty ='" + dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.Qty) + "', TotalAmt = '" + +dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt) + "'", where : "SONumber ='" + SONumber + "'"); } else { dbConn.Delete <SOHeader>(s => s.SONumber == SONumber); ModelState.AddModelError("error", "Đơn hàng được tạo khi số lượng > 0"); return(Json(list.ToDataSourceResult(request, ModelState))); } } } dbConn.Close(); } catch (Exception e) { dbConn.Close(); ModelState.AddModelError("error", e.Message); return(Json(list.ToDataSourceResult(request, ModelState))); } return(Json(new { sussess = true })); }
public JsonResult GetCustomerHirerachy(string customerid) { IDbConnection db = new OrmliteConnection().openConn(); try { //select list menu cha List <CustomerHirerachy> lstFirstMenu = db.Select <CustomerHirerachy>("Status = 1 AND ParentCustomerHirerachyID ='' ").OrderBy(p => p.CustomerHirerachyIndex).ToList(); List <CustomerHirerachy> allCustomerHirerachy = db.Select <CustomerHirerachy>("Status = 1 ").OrderBy(p => p.CustomerHirerachyIndex).ToList(); var listCustomer = new List <CustomerHirerachy>(); if (!string.IsNullOrEmpty(customerid)) { listCustomer = db.SqlList <CustomerHirerachy>("p_CustomerHirerachy_Select '" + customerid + "'"); } List <CustomerHirerachyViewModel> lstMenuView = new List <CustomerHirerachyViewModel>(); foreach (CustomerHirerachy der in lstFirstMenu) { CustomerHirerachyViewModel node = new CustomerHirerachyViewModel(); node.id = der.CustomerHirerachyID; node.text = der.CustomerHirerachyName; node.items = new List <CustomerHirerachyViewModel>(); AddChildrenNode(ref node, allCustomerHirerachy, listCustomer); lstMenuView.Add(node); } return(Json(new { success = true, Data = lstMenuView })); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } finally { db.Close(); } }
//public JsonResult GetMenu([DataSourceRequest] DataSourceRequest request) //{ // IDbConnection db = new OrmliteConnection().openConn(); // try // { // var listMenu = db.Select<Auth_Menu>(p => p.IsVisible == true).OrderBy(p => p.MenuIndex).ToList(); // DataSourceResult dsr = new DataSourceResult(); // dsr.Data = listMenu; // return Json(dsr, JsonRequestBehavior.AllowGet); // } // catch (Exception e) // { // return Json(new { success = false, message = e.Message }); // } // finally { db.Close(); } //} //[HttpPost] public JsonResult GetMenu(string action, int roleID) { IDbConnection db = new OrmliteConnection().openConn(); try { //select list menu cha List <Auth_Menu> lstFirstMenu = db.Select <Auth_Menu>("IsVisible = 1 AND ParentMenuID ='' AND MenuID <> 'Home'").OrderBy(p => p.MenuIndex).ToList(); List <Auth_Menu> allAuthMenu = db.Select <Auth_Menu>("IsVisible = 1 AND MenuID <> 'Home'").OrderBy(p => p.MenuIndex).ToList(); var listAction = new List <Auth_Menu>(); if (!string.IsNullOrEmpty(action)) { listAction = db.SqlList <Auth_Menu>("p_Auth_Menu_Select_By_Action '" + action + "', " + roleID); } List <AuthMenuViewModel> lstMenuView = new List <AuthMenuViewModel>(); foreach (Auth_Menu der in lstFirstMenu) { AuthMenuViewModel node = new AuthMenuViewModel(); node.id = der.MenuID; node.text = der.MenuName; node.items = new List <AuthMenuViewModel>(); AddChidrenNode(ref node, allAuthMenu, listAction); lstMenuView.Add(node); } return(Json(new { success = true, Data = lstMenuView })); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } finally { db.Close(); } }
public List <Menu> GetRootMenu() { using (var dbconn = new OrmliteConnection().openConn()) { var data = dbconn.Select <Menu>(m => m.ParentID == 0 && m.Status == true); return(dbconn.Select <Menu>(m => m.ParentID < 1 && m.Status == true)); } }
public List <Merchant_Product_Promotion> ReadByMerchantID(string connectionString, string MerchantID) { using (var db = new OrmliteConnection().openConn(connectionString)) { if (MerchantID == "All") { return(db.Select <Merchant_Product_Promotion>()); } return(db.Select <Merchant_Product_Promotion>(s => s.ma_gian_hang == MerchantID)); } }
public List <Menu> GetAll() { using (var dbconn = new OrmliteConnection().openConn()) { return(dbconn.Select <Menu>()); } }
public ActionResult ReadHeader([DataSourceRequest] DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); var data = new List <DC_AD_Picking_Header>(); if (request.Filters.Any()) { var where = new KendoApplyFilter().ApplyFilter(request.Filters[0]); data = dbConn.Select <DC_AD_Picking_Header>(where); } else { data = dbConn.Select <DC_AD_Picking_Header>(); } return(Json(data.ToDataSourceResult(request))); }
public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, string text) { var dbConn = new OrmliteConnection().openConn(); var data = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == text).ToList(); return(Json(data.ToDataSourceResult(request))); }
public List <Menu> GetSubMenu(int ID) { using (var dbconn = new OrmliteConnection().openConn()) { return(dbconn.Select <Menu>(m => m.ParentID == ID && m.Status == true)); } }
public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, string text) { var dbConn = new OrmliteConnection().openConn(); var data = dbConn.Select <SODetail>("SELECT * FROM SODetail WHERE SONumber ='" + text + "'"); return(Json(data.ToDataSourceResult(request))); }
public ActionResult CancelSalesOrder(string data) { var dbConn = new OrmliteConnection().openConn(); if (userAsset.ContainsKey("Insert") && userAsset["Insert"]) { try { string[] separators = { "@@" }; var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries); foreach (var item in listdata) { if (dbConn.Select <SOHeader>("Select * from SOHeader where Status <> N'Mới' AND SONumber = '" + item + "'").Count() > 0) { return(Json(new { success = false, message = "Chỉ hủy được các mẫu tin có trạng thái Mới" })); } dbConn.Update <SOHeader>(set: "Status = N'Hủy'", where : "SONumber = '" + item + "'"); } } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } return(Json(new{ success = true })); } else { return(Json(new { success = false, message = "Bạn không có quyền hủy." })); } }
public List <CustomerHirerachy> GetAllCustomerHirerachy() { IDbConnection db = new OrmliteConnection().openConn(); var lst = db.Select <CustomerHirerachy>().Where(p => p.Status == true).ToList(); db.Close(); return(lst); }
public ActionResult PartialDetail(string id) { if (userAsset.ContainsKey("View") && userAsset["View"]) { IDbConnection dbConn = new OrmliteConnection().openConn(); var dict = new Dictionary <string, object>(); dict["asset"] = userAsset; dict["listItem"] = dbConn.Select <Products>(p => p.Status == true); dict["listUnit"] = dbConn.Select <DC_AD_Unit>(p => p.Status == true); dict["PickingNumber"] = id; return(PartialView("PickingListDetail", dict)); } else { return(Json(new { succsess = false, message = "Không có quyền tạo." })); } }
public List <Auth_Menu> GetAllMenu() { IDbConnection db = new OrmliteConnection().openConn(); var lst = db.Select <Auth_Menu>().Where(p => p.IsVisible == true).ToList(); db.Close(); return(lst); }
public ActionResult UpdateDetail([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <SODetail> list) { var dbConn = new OrmliteConnection().openConn(); if (userAsset.ContainsKey("Update") && userAsset["Update"]) { if (list != null)//&& ModelState.IsValid) { foreach (var item in list) { if (dbConn.Select <SOHeader>(s => s.SONumber == item.SONumber && s.Status != "Mới").Count() > 0) { return(Json(new { success = false, message = "Đơn hàng đã xác nhận nên không được xóa." })); } else if (item.Qty > 0) { var isExist = dbConn.SingleOrDefault <SODetail>("SONumber = {0} AND ItemCode = {1}", item.SONumber, item.ItemCode); if (isExist != null) { try { isExist.Qty = item.Qty; isExist.TotalAmt = item.Qty * item.Price; isExist.UpdatedAt = DateTime.Now; isExist.UpdatedBy = currentUser.UserID; dbConn.Update <SODetail>(isExist); //dbConn.Update<SODetail>(set: "Qty = '" + item.Qty + "', TotalAmt = '" + item.Qty * item.Price + "',UpdatedAt = '" + DateTime.Now + "', UpdatedBy ='" + currentUser.UserID + "'", where: "SONumber = '" + item.SONumber + "' AND ItemCode ='" + item.ItemCode + "'"); dbConn.Update <SOHeader>(set: "UpdatedBy='" + currentUser.UserID + "',TotalQty ='" + dbConn.Select <SODetail>(s => s.SONumber == item.SONumber).Sum(s => s.Qty) + "', TotalAmt = '" + dbConn.Select <SODetail>(s => s.SONumber == item.SONumber).Sum(s => s.TotalAmt) + "'", where : "SONumber ='" + item.SONumber + "'"); var success = dbConn.Execute(@"UPDATE SOHeader Set UpdatedAt = @UpdatedAt WHERE SONumber = '" + item.SONumber + "'", new { UpdatedAt = DateTime.Now, }) == 1; } catch (Exception ex) { ModelState.AddModelError("error", ex.Message); return(Json(list.ToDataSourceResult(request, ModelState))); } } } else { ModelState.AddModelError("error", "Đơn hàng được tạo khi số lượng > 0"); return(Json(list.ToDataSourceResult(request, ModelState))); } } } dbConn.Close(); return(Json(new { sussess = true })); } else { dbConn.Close(); ModelState.AddModelError("error", "Bạn không có quyền cập nhật."); return(Json(list.ToDataSourceResult(request, ModelState))); } }
public ActionResult PartialCustomer() { if (userAsset.ContainsKey("View") && userAsset["View"]) { IDbConnection dbConn = new OrmliteConnection().openConn(); var dict = new Dictionary <string, object>(); dict["asset"] = userAsset; dict["activestatus"] = new CommonLib().GetActiveStatus(); dict["listprovince"] = dbConn.Select <Master_Territory>("Select TerritoryID,TerritoryName From Master_Territory where Level ='Province'"); dict["listdistrict"] = dbConn.Select <Master_Territory>("Select TerritoryID,TerritoryName From Master_Territory where Level ='District'"); dbConn.Close(); return(PartialView("_Customer", dict)); } else { return(RedirectToAction("NoAccess", "Error")); } }
public ActionResult PartialWHL() { if (userAsset.ContainsKey("View") && userAsset["View"]) { IDbConnection dbConn = new OrmliteConnection().openConn(); var dict = new Dictionary <string, object>(); dict["asset"] = userAsset; dict["activestatus"] = new CommonLib().GetActiveStatus(); dict["user"] = dbConn.Select <Auth_User>(p => p.IsActive == true); dict["listWH"] = dbConn.Select <WareHouse>(p => p.Status == true); dbConn.Close(); return(PartialView("_IN_WareHouseLocation", dict)); } else { return(RedirectToAction("NoAccess", "Error")); } }
public static List <FE_MenuItem> GetHomeItem() { try { IDbConnection dbConn = new OrmliteConnection().openConn(); var lst_cate = dbConn.Select <Category>("isactive={0}", 1); var result = new List <FE_MenuItem>(); foreach (var item in lst_cate) { var entryItem = new FE_MenuItem(); entryItem.CategoryId = item.entryid; entryItem.CategoryName = item.entryname; entryItem.MenuSubItems = new List <FE_MenuSubItem>(); var lst_subcate = dbConn.Select <SubCategory>("isactive={0} and categoryid = {1}", 1, item.entryid).ToList(); foreach (var subitem in lst_subcate) { if (item.entryid == subitem.categoryid) { var entrysubitem = new FE_MenuSubItem(); entrysubitem.CategoryId = subitem.categoryid; entrysubitem.SubCategoryId = subitem.entryid; entrysubitem.SubCategoryName = subitem.entryname; var lst_item = dbConn.Select <Product>("isactive={0} and subcategoryid = {1}", 1, subitem.entryid).Take(8).ToList(); if (lst_item != null && lst_item.Count > 4 && lst_item.Count < 8) { lst_item = lst_item.Take(4).ToList(); } var lst_product = Mapper.Map <List <FE_Product> >(lst_item); entrysubitem.ProductItems = lst_product; entryItem.MenuSubItems.Add(entrysubitem); } } result.Add(entryItem); } return(result); } catch (Exception ex) { return(new List <FE_MenuItem>()); } }
public void setDetail(ref List <Product_Hierarchy_Property> list, string connectionString) { using (var db = new OrmliteConnection().openConn(connectionString)) { foreach (Product_Hierarchy_Property item in list) { item.detail = db.Select <Property_Detail>("select * from ERPAPD..Property_Detail where ma_thong_so ={0}", item.ma_thong_so); } } }
public ActionResult CompletePicking(string data) { var dbConn = new OrmliteConnection().openConn(); if (userAsset.ContainsKey("Update") && userAsset["Update"]) { try { string[] separators = { "@@" }; var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries); foreach (var item in listdata) { if (dbConn.Select <DC_AD_Picking_Header>(s => s.Status == "Hoàn thành" && s.PickingNumber == item).Count() > 0) { return(Json(new { success = false, message = item + " đã được hoàn thành trước đó." })); } if (dbConn.Select <DC_AD_Picking_Header>(s => s.Status != "Đang giao hàng" && s.PickingNumber == item).Count() > 0) { return(Json(new { success = false, message = item + " vui lòng nhập kho trước khi hoàn thành." })); } dbConn.Update <DC_AD_Picking_Header>(set: "Status = N'Hoàn thành', UpdatedBy ='" + currentUser.UserID + "', UpdatedAt= '" + DateTime.Now + "'", where : "PickingNumber = '" + item + "'"); foreach (var so in dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == item).ToList()) { dbConn.Update <SOHeader>(set: "Status = N'Hoàn thành'", where : "SONumber = '" + so.SONumber + "'"); } //dbConn.Update<DC_AD_SO_Header>(set: "Status = N'Hoàn thành'", where: "SONumber = '" + item + "'"); } } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } return(Json(new { success = true })); } else { return(Json(new { success = false, message = "Bạn không có quyền hủy." })); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); log4net.Config.XmlConfigurator.Configure(); string whereCondition = ""; var data = new List <Products>(); if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); data = dbConn.Select <Products>(whereCondition).ToList(); } else { data = dbConn.Select <Products>().ToList(); } dbConn.Close(); return(Json(data.ToDataSourceResult(request))); }
public ActionResult UpdateDetail([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <DC_AD_Picking_Detail> list) { var dbConn = new OrmliteConnection().openConn(); try { if (list != null && ModelState.IsValid) { foreach (var item in list) { if (string.IsNullOrEmpty(item.PickingNumber)) { ModelState.AddModelError("", "Số picking không tồn tại"); return(Json(list.ToDataSourceResult(request, ModelState))); } if (item.Qty <= 0) { ModelState.AddModelError("", "Số lượng phải lớn hơn 0."); return(Json(list.ToDataSourceResult(request, ModelState))); } dbConn.Update <DC_AD_Picking_Detail>(set: "Qty = '" + item.Qty + "', TotalAmt = '" + item.Price * item.Qty + "'", where : "ID = '" + item.Id + "'"); var success = dbConn.Execute(@"UPDATE DC_AD_Picking_Header Set TotalQty = @TotalQty, TotalAmt =@TotalAmt WHERE PickingNumber = '" + item.PickingNumber + "'", new { TotalQty = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == item.PickingNumber).Sum(s => s.Qty), TotalAmt = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == item.PickingNumber).Sum(s => s.TotalAmt), }) == 1; if (!success) { return(Json(new { success = false, message = "Không thể lưu" })); } } } } catch (Exception e) { ModelState.AddModelError("error", e.Message); return(Json(list.ToDataSourceResult(request, ModelState))); } return(Json(new { sussess = true })); }
public ActionResult PartialDetail(string id) { if (userAsset.ContainsKey("View") && userAsset["View"]) { IDbConnection dbConn = new OrmliteConnection().openConn(); var dict = new Dictionary <string, object>(); dict["asset"] = userAsset; dict["listWH"] = dbConn.Select <WareHouse>(p => p.Status == true); dict["listWHL"] = dbConn.Select <WareHouseLocation>(p => p.Status == true); dict["listVendor"] = dbConn.Select <Vendor>(p => p.Status == true); dict["listWH"] = dbConn.Select <WareHouse>(p => p.Status == true); dict["listWHL"] = dbConn.Select <WareHouseLocation>(p => p.Status == true); dict["listUnit"] = dbConn.Select <INUnit>(p => p.Status == true); dict["SONumber"] = id; dict["activestatus"] = new CommonLib().GetActiveStatus(); dbConn.Close(); if (string.IsNullOrEmpty(id)) { return(PartialView("_OP_CreateOrderIndex", dict)); } else { return(PartialView("_OP_CreateOrder", dict)); } } else { return(RedirectToAction("NoAccess", "Error")); } }
public string Sync(Product_Info item, string UserName, string connectionString) { List <SqlParameter> lstParameter = new List <SqlParameter>(); if (!CheckBeforeSync(item)) { return("Sản phẩm chưa đủ điều kiện Sync !\n Vui lòng kiểm tra tất cả trạng thái !"); } lstParameter.Clear(); lstParameter.Add(new SqlParameter("@id", item.id)); lstParameter.Add(new SqlParameter("@ma_san_pham", item.ma_san_pham)); using (var db = new OrmliteConnection().openConn(connectionString)) { using (var dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted)) { try { var pro_hier = db.FirstOrDefault <Product_Hierarchy>(s => s.ma_san_pham == item.ma_san_pham && s.trang_thai == AllConstant.trang_thai.DANG_SU_DUNG); if (pro_hier != null) { pro_hier.ngay_cap_nhat = DateTime.Now; pro_hier.nguoi_cap_nhat = UserName; db.Update(pro_hier); } else { dbTrans.Rollback(); return("Cây phân cấp chưa có hoặc trạng thái không hoạt động!"); } var pro_price = db.Select <Product_Price>(s => s.ma_san_pham == item.ma_san_pham && s.trang_thai == AllConstant.trang_thai.DANG_SU_DUNG && s.trang_thai_duyet == AllConstant.trang_thai_duyet.DA_DUYET && s.trang_thai_xuat_ban == AllConstant.trang_thai_xuat_ban.DA_XUAT_BAN); if (pro_price == null) { return("Chưa có giá sản phẩm thõa mãn!"); } foreach (Product_Price iitem in pro_price) { iitem.ngay_cap_nhat = DateTime.Now; iitem.nguoi_cap_nhat = UserName; db.Update(iitem); } dbTrans.Commit(); } catch (Exception e) { dbTrans.Rollback(); return(e.Message); } } } new SqlHelper(connectionString).ExecuteNoneQuery("p_Product_SyncFullToMySQL", lstParameter); return(""); }
public List <AuthMenu> GetAllMenu(string lang) { IDbConnection dbConn = new OrmliteConnection().openConn(); try { return(dbConn.Select <AuthMenu>()); } catch (Exception ex) { return(new List <AuthMenu>()); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = dbConn.Select <DC_LG_Discountion>(whereCondition); return(Json(data.ToDataSourceResult(request))); }
public ActionResult GetBySONumber(string id) { try { var dbConn = new OrmliteConnection().openConn(); var data = dbConn.Select <SOHeader>("SELECT * FROM SOHeader WHERE SONumber ='" + id + "'").FirstOrDefault(); return(Json(data, JsonRequestBehavior.AllowGet)); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } }
public ActionResult GetByPickingNumber(string id) { try { var dbConn = new OrmliteConnection().openConn(); var data = dbConn.Select <DC_AD_Picking_Header>(s => s.PickingNumber == id).FirstOrDefault(); return(Json(data, JsonRequestBehavior.AllowGet)); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } }
public ActionResult GetDisByProvinceID(string ProvinceID) { var dbConn = new OrmliteConnection().openConn(); try { var data = dbConn.Select <Master_Territory>("select TerritoryID,TerritoryName from Master_Territory where ParentID ={0}", ProvinceID); return(Json(data, JsonRequestBehavior.AllowGet)); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } }
public ActionResult GetLocationhByWareHouseID(string WHID) { var dbConn = new OrmliteConnection().openConn(); try { var data = dbConn.Select <WareHouseLocation>(s => s.WHID == WHID); return(Json(data, JsonRequestBehavior.AllowGet)); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } }