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 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 Create(Products item) { IDbConnection db = new OrmliteConnection().openConn(); try { var isExist = db.SingleOrDefault <Products>("SELECT Code, Id FROM dbo.Products Where Code ='" + item.Code + "'"); if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null) { if (isExist != null) { return(Json(new { success = false, message = "Sản phẩm đã tồn tại." })); } string id = ""; var checkID = db.SingleOrDefault <Products>("SELECT Code, Id FROM dbo.Products ORDER BY Id DESC"); if (checkID != null) { var nextNo = int.Parse(checkID.Code.Substring(2, checkID.Code.Length - 2)) + 1; id = "PR" + String.Format("{0:00000000}", nextNo); } else { id = "PR00000001"; } item.Code = id; item.Name = !string.IsNullOrEmpty(item.Name) ? item.Name.Trim() : ""; item.Price = item.VATPrice / 1.1; item.VATPrice = item.VATPrice; item.Size = !string.IsNullOrEmpty(item.Size) ? item.Size.Trim() : "";; item.Unit = !string.IsNullOrEmpty(item.Unit) ? item.Unit.Trim() : "";; item.Type = !string.IsNullOrEmpty(item.Type) ? item.Type.Trim() : "";; item.WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID : ""; item.WHLID = !string.IsNullOrEmpty(item.WHLID) ? item.WHLID : ""; item.Desc = !string.IsNullOrEmpty(item.Desc) ? item.Desc.Trim() : ""; item.ShapeTemplate = !string.IsNullOrEmpty(item.ShapeTemplate) ? item.ShapeTemplate.Trim() : ""; item.CreatedAt = DateTime.Now; item.CreatedBy = currentUser.UserID; item.UpdatedAt = DateTime.Parse("1900-01-01"); item.UpdatedBy = ""; item.Status = item.Status; db.Insert <Products>(item); return(Json(new { success = true, Code = item.Code, createdat = item.CreatedAt, createdby = item.CreatedBy })); } else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null) { var success = db.Execute(@"UPDATE Products SET Status = @Status, VATPrice = @VATPrice, Size= @Size, Unit=@Unit,Type=@Type, WHID=@WHID, WHLID=@WHLID, ShapeTemplate = @ShapeTemplate, UpdatedAt = @UpdatedAt,UpdatedBy =@UpdatedBy, Price=@Price,[Desc]=@Desc, Name = @Name WHERE Code = '" + item.Code + "'", new { Status = item.Status, Price = item.VATPrice / 1.1, VATPrice = item.VATPrice, Size = !string.IsNullOrEmpty(item.Size) ? item.Size.Trim() : "", Unit = !string.IsNullOrEmpty(item.Unit) ? item.Unit.Trim() : "", Type = !string.IsNullOrEmpty(item.Type) ? item.Type.Trim() : "", WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID : "", WHLID = !string.IsNullOrEmpty(item.WHLID) ? item.WHLID : "", ShapeTemplate = !string.IsNullOrEmpty(item.ShapeTemplate) ? item.ShapeTemplate.Trim() : "", UpdatedAt = DateTime.Now, UpdatedBy = currentUser.UserID, Desc = !string.IsNullOrEmpty(item.Desc) ? item.Desc.Trim() : "", Name = !string.IsNullOrEmpty(item.Name) ? item.Name.Trim() : "", }) == 1; if (!success) { return(Json(new { success = false, message = "Cập nhật không thành công." })); } return(Json(new { success = true })); } else { return(Json(new { success = false, message = "Bạn không có quyền" })); } } catch (Exception e) { log.Error(" ListProducts - Create - " + e.Message); return(Json(new { success = false, message = e.Message })); } finally { db.Close(); } }
public ActionResult CreateWHL(WareHouseLocation item) { IDbConnection db = new OrmliteConnection().openConn(); try { var isExist = db.SingleOrDefault <WareHouseLocation>("SELECT WHLID, Id FROM dbo.WareHouseLocation Where WHLID ='" + item.WHLID + "'"); if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null) { if (isExist != null) { return(Json(new { success = false, message = "Vị trí kho đã tồn tại." })); } string id = ""; var checkID = db.SingleOrDefault <WareHouseLocation>("SELECT WHLID, Id FROM dbo.WareHouseLocation ORDER BY Id DESC"); if (checkID != null) { var nextNo = int.Parse(checkID.WHLID.Substring(3, checkID.WHLID.Length - 3)) + 1; id = "WHL" + String.Format("{0:00000000}", nextNo); } else { id = "WHL00000001"; } item.WHLID = id; item.WHLName = !string.IsNullOrEmpty(item.WHLName) ? item.WHLName.Trim() : ""; item.WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID.Trim() : ""; item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : ""; item.CreatedAt = DateTime.Now; item.CreatedBy = currentUser.UserID; item.UpdatedAt = DateTime.Parse("1900-01-01"); item.UpdatedBy = ""; item.Status = item.Status; db.Insert <WareHouseLocation>(item); return(Json(new { success = true, Code = item.WHLID, createdate = item.CreatedAt, createdby = item.CreatedBy })); } else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null) { var success = db.Execute(@"UPDATE WareHouseLocation SET Status = @Status, Note = @Note, UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, WHLName = @WHLName, WHID = @WHID WHERE WHLID = '" + item.WHLID + "'", new { Status = item.Status, //WHName = !string.IsNullOrEmpty(item.WHName) ? item.WHName.Trim() : "", Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : "", UpdatedAt = DateTime.Now, UpdatedBy = currentUser.UserID, WHLName = !string.IsNullOrEmpty(item.WHLName) ? item.WHLName.Trim() : "", WHID = !string.IsNullOrEmpty(item.WHID) ? item.WHID.Trim() : "", }) == 1; if (!success) { return(Json(new { success = false, message = "Cập nhật không thành công." })); } return(Json(new { success = true })); } else { return(Json(new { success = false, message = "Bạn không có quyền" })); } } catch (Exception e) { log.Error(" ListPublication - Create - " + e.Message); return(Json(new { success = false, message = e.Message })); } finally { db.Close(); } }
public ActionResult ConfirmCreate() { var dbConn = new OrmliteConnection().openConn(); if (userAsset.ContainsKey("Insert") && userAsset["Insert"]) { try { string SONumber = Request["SONumber"]; var header = new SOHeader(); var detail = new SODetail(); if (string.IsNullOrEmpty(SONumber)) { string datetimeSO = DateTime.Now.ToString("yyMMdd"); var existSO = dbConn.SingleOrDefault <SOHeader>("SELECT id, SONumber FROM SOHeader ORDER BY Id DESC"); if (existSO != null) { var nextNo = Int32.Parse(existSO.SONumber.Substring(8, 5)) + 1; SONumber = "SO" + datetimeSO + String.Format("{0:00000}", nextNo); } else { SONumber = "SO" + datetimeSO + "00001"; } } if (!string.IsNullOrEmpty(Request["SODate"])) { DateTime fromDateValue; if (!DateTime.TryParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out fromDateValue)) { return(Json(new { message = "Ngày tạo không đúng." })); } } if (dbConn.Select <DC_OCM_Merchant>(p => p.MerchantID == Request["MerchantID"]).Count() <= 0) { return(Json(new { success = false, message = "Nhà cung cấp không tồn tại." })); } if (dbConn.Select <SODetail>(p => p.SONumber == SONumber).Count() > 0) { if (!string.IsNullOrEmpty(Request["ItemCode"])) { if (dbConn.Select <Products>(p => p.Code == Request["ItemCode"]).Count() <= 0) { return(Json(new { success = false, message = "Ấn phẩm không tồn tại." })); } var itemcode = dbConn.Select <Products>(s => s.Code == Request["ItemCode"]).FirstOrDefault(); var itemunit = dbConn.Select <Products>(s => s.UnitID == itemcode.Unit).FirstOrDefault(); if (dbConn.Select <SODetail>(p => p.ItemCode == Request["ItemCode"] && p.SONumber == SONumber).Count() > 0) { var success = dbConn.Execute(@"UPDATE SODetail Set Qty = @Qty, TotalAmt =@TotalAmt ,UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, Price = @Price WHERE SONumber = '" + SONumber + "' AND ItemCode = '" + Request["ItemCode"] + "'", new { Qty = dbConn.Select <SODetail>(s => s.ItemCode == Request["ItemCode"] && s.SONumber == SONumber).Sum(s => s.Qty) + int.Parse(Request["Qty"]), TotalAmt = itemcode != null ? itemcode.VATPrice * (dbConn.Select <SODetail>(s => s.ItemCode == Request["ItemCode"] && s.SONumber == SONumber).Sum(s => s.Qty) + int.Parse(Request["Qty"])) : 0, Price = itemcode.VATPrice, UpdatedBy = currentUser.UserID, UpdatedAt = DateTime.Now, }) == 1; if (!success) { return(Json(new { success = false, message = "Không thể lưu" })); } } else { detail.SONumber = SONumber; detail.ItemCode = !string.IsNullOrEmpty(Request["ItemCode"]) ? Request["ItemCode"] : ""; detail.ItemName = !string.IsNullOrEmpty(itemcode.Name) ? itemcode.Name : ""; detail.Price = itemcode != null ? itemcode.VATPrice : 0; detail.Qty = int.Parse(Request["Qty"]); detail.TotalAmt = itemcode.VATPrice * int.Parse(Request["Qty"]); detail.UnitID = !string.IsNullOrEmpty(itemunit.UnitID) ? itemunit.UnitID : ""; detail.UnitName = !string.IsNullOrEmpty(itemunit.UnitName) ? itemunit.UnitName : ""; detail.Note = ""; detail.Status = ""; detail.CreatedBy = currentUser.UserID; detail.CreatedAt = DateTime.Now; detail.UpdatedBy = ""; detail.UpdatedAt = DateTime.Parse("1900-01-01"); dbConn.Insert <SODetail>(detail); } } } else { if (string.IsNullOrEmpty(Request["ItemCode"]) || dbConn.Select <Products>(p => p.Code == Request["ItemCode"]).Count() <= 0) { return(Json(new { success = false, message = "Ấn phẩm không tồn tại." })); } var itemcode = dbConn.Select <Products>(s => s.Code == Request["ItemCode"]).FirstOrDefault(); var itemunit = dbConn.Select <Products>(s => s.UnitID == itemcode.Unit).FirstOrDefault(); detail.SONumber = SONumber; detail.ItemCode = !string.IsNullOrEmpty(Request["ItemCode"]) ? Request["ItemCode"] : ""; detail.ItemName = !string.IsNullOrEmpty(itemcode.Name) ? itemcode.Name : ""; detail.Price = itemcode != null ? itemcode.VATPrice :0; detail.Qty = int.Parse(Request["Qty"]); detail.TotalAmt = itemcode.VATPrice * int.Parse(Request["Qty"]); detail.UnitID = !string.IsNullOrEmpty(itemunit.UnitID) ? itemunit.UnitID : ""; detail.UnitName = !string.IsNullOrEmpty(itemunit.UnitName) ? itemunit.UnitName : ""; detail.Note = ""; detail.Status = ""; detail.CreatedBy = currentUser.UserID; detail.CreatedAt = DateTime.Now; detail.UpdatedBy = ""; detail.UpdatedAt = DateTime.Parse("1900-01-01"); dbConn.Insert <SODetail>(detail); } if (dbConn.Select <SOHeader>(p => p.SONumber == SONumber).Count() > 0) { var success = dbConn.Execute(@"UPDATE SOHeader Set TotalQty = @TotalQty, TotalAmt =@TotalAmt ,UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, MerchantID = @MerchantID, SODate = @SODate , WHLID =@WHLID, WHID =@WHID WHERE SONumber = '" + SONumber + "'", new { TotalQty = dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.Qty), TotalAmt = dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt), SODate = !string.IsNullOrEmpty(Request["SODate"]) ? DateTime.Parse(DateTime.ParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")) : DateTime.Now, WHID = !string.IsNullOrEmpty(Request["WHID"]) ? Request["WHID"] : "", WHLID = !string.IsNullOrEmpty(Request["WHLID"]) ? Request["WHLID"] : "", MerchantID = !string.IsNullOrEmpty(Request["MerchantID"]) ? Request["MerchantID"] : "", UpdatedBy = currentUser.UserID, UpdatedAt = DateTime.Now, }) == 1; if (!success) { return(Json(new { success = false, message = "Không thể lưu" })); } } else { header.SONumber = SONumber; header.SODate = !string.IsNullOrEmpty(Request["SODate"]) ? DateTime.Parse(DateTime.ParseExact(Request["SODate"], "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd")) : DateTime.Now; header.VendorID = !string.IsNullOrEmpty(Request["MerchantID"]) ? Request["MerchantID"] : ""; header.Note = !string.IsNullOrEmpty(Request["Note"]) ? Request["Note"] : ""; header.TotalQty = dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.Qty); header.WHID = Request["WHID"]; header.Status = "Mới"; header.WHLID = !string.IsNullOrEmpty(Request["WHLID"]) ? Request["WHLID"] : ""; header.TotalAmt = dbConn.Select <SODetail>(s => s.SONumber == SONumber).Sum(s => s.TotalAmt); header.CreatedBy = currentUser.UserID; header.CreatedAt = DateTime.Now; header.UpdatedBy = ""; header.UpdatedAt = DateTime.Parse("1900-01-01"); dbConn.Insert <SOHeader>(header); } return(Json(new { success = true, SONumber = SONumber })); } catch (Exception e) { return(Json(new { success = false, message = e.Message })); } } else { return(Json(new { success = false, message = "Không có quyền tạo." })); } }
public ActionResult CreatePicking(string data, string printer, string pickingdate) { var dbConn = new OrmliteConnection().openConn(); if (userAsset.ContainsKey("Insert") && userAsset["Insert"]) { try { string[] separators = { "@@" }; var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries); string PickingNumber = ""; string datetimeSO = DateTime.Now.ToString("yyMMdd"); var existSO = dbConn.SingleOrDefault <DC_AD_Picking_Header>("SELECT id, PickingNumber FROM DC_AD_Picking_Header ORDER BY Id DESC"); if (existSO != null) { var nextNo = Int32.Parse(existSO.PickingNumber.Substring(8, 5)) + 1; PickingNumber = "PK" + datetimeSO + String.Format("{0:00000}", nextNo); } else { PickingNumber = "PK" + datetimeSO + "00001"; } if (!string.IsNullOrEmpty(pickingdate)) { DateTime fromDateValue; if (!DateTime.TryParseExact(pickingdate, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out fromDateValue)) { return(Json(new { success = false, message = "Ngày tạo không đúng." })); } } //int TotalQty = 0; //double TotalAmt = 0; var detail = new DC_AD_Picking_Detail(); foreach (var item in listdata) { if (dbConn.Select <SOHeader>(s => s.SONumber == item && s.Status != "Đã đặt hàng").Count() > 0) { return(Json(new { success = false, massege = "Vui lòng đặt hàng " + item + " trước khi tạo Picking." })); } if (dbConn.Select <DC_AD_Picking_Detail>(s => s.SONumber == item).Count() > 0) { return(Json(new { success = false, massege = item + " Đă được đăt trước đó." })); } foreach (var dtSO in dbConn.Select <SODetail>(s => s.SONumber == item).ToList()) { if (dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == PickingNumber && s.ItemCode == dtSO.ItemCode && s.UnitID == dtSO.UnitID).Count() > 0) { var success = dbConn.Execute(@"UPDATE DC_AD_Picking_Detail Set Qty = @Qty, TotalAmt =@TotalAmt ,UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, Price = @Price WHERE PickingNumber = '" + PickingNumber + "' AND ItemCode = '" + dtSO.ItemCode + "' AND UnitID = '" + dtSO.UnitID + "'", new { Qty = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == PickingNumber && s.ItemCode == dtSO.ItemCode && s.UnitID == dtSO.UnitID).Sum(s => s.Qty) + dtSO.Qty, TotalAmt = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == PickingNumber && s.ItemCode == dtSO.ItemCode && s.UnitID == dtSO.UnitID).Sum(s => s.TotalAmt) + dtSO.TotalAmt, Price = dtSO.Price, UpdatedBy = currentUser.UserID, UpdatedAt = DateTime.Now, }) == 1; if (!success) { return(Json(new { success = false, message = "Không thể lưu" })); } } else { detail.PickingNumber = PickingNumber; detail.SONumber = item; detail.ItemCode = !string.IsNullOrEmpty(dtSO.ItemCode) ? dtSO.ItemCode : ""; detail.ItemName = !string.IsNullOrEmpty(dtSO.ItemName) ? dtSO.ItemName : ""; detail.Price = dtSO.Price; detail.Qty = dtSO.Qty; detail.TotalAmt = dtSO.TotalAmt; detail.UnitID = !string.IsNullOrEmpty(dtSO.UnitID) ? dtSO.UnitID : ""; detail.UnitName = !string.IsNullOrEmpty(dtSO.UnitName) ? dtSO.UnitName : ""; detail.Note = ""; detail.Status = ""; detail.CreatedBy = currentUser.UserID; detail.CreatedAt = DateTime.Now; detail.UpdatedBy = ""; detail.UpdatedAt = DateTime.Parse("1900-01-01"); dbConn.Insert <DC_AD_Picking_Detail>(detail); } } dbConn.Update <SOHeader>(set: "Status = N'Đang giao hàng'", where : "SONumber = '" + item + "'"); } var header = new DC_AD_Picking_Header(); header.PickingNumber = PickingNumber; header.PickingDate = DateTime.Parse(pickingdate); header.PrinterID = printer; header.PrinterName = dbConn.Select <DC_AD_Printer>(s => s.PrinterID == printer).FirstOrDefault().PrinterName; header.TotalQty = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == PickingNumber).Sum(s => s.Qty); header.TotalAmt = dbConn.Select <DC_AD_Picking_Detail>(s => s.PickingNumber == PickingNumber).Sum(s => s.TotalAmt); header.Note = ""; header.Status = "Mới"; header.CreatedBy = currentUser.UserID; header.CreatedAt = DateTime.Now; header.UpdatedBy = ""; header.UpdatedAt = DateTime.Parse("1900-01-01"); dbConn.Insert <DC_AD_Picking_Header>(header); } catch (Exception e) { return(Json(new { success = false, massege = e.Message })); } return(Json(new { success = true })); } else { return(Json(new { success = false, message = "Không có quyền tạo." })); } }