public ActionResult ShowDetails(int id) { if (id == 0) { TempData["error"] = "That was an invalid/unknown request."; return(RedirectToAction("Index", "Home")); } BOQPreparingRepository repo = new BOQPreparingRepository(); SubContractorDropDown(); BOQPreparing model = repo.GetBOQPreparingView(id); return(View("BOQPreparing", model)); }
public BOQPreparing GetBOQPreparingView(int ProjectWorkId) { using (IDbConnection connection = OpenConnection(dataConnection)) { BOQPreparing model = new BOQPreparing(); //head//dt//work string query = @"select hd.ProjectWorkId,projectWorkRefNo, convert (varchar(50), projectWorkDate, 103)projectWorkDate, hd.ClientId ClientId,clientName,ProjectEnquiry,PreparedBy from ProjectWorkBOQ hd inner join ProjectWorkBOQItem dt on hd.ProjectWorkId=dt.ProjectWorkId inner join ProjectTask task on task.ProjectTaskId=dt.ProjectTaskId inner join Project on Project.ProjectId=task.ProjectId inner join Client c on c.ClientId =hd.ClientId where hd.ProjectWorkId=@id SELECT ProjectWorkId, ProjectWorkItemId, ProjectRefNo, ProjectDate, MileStoneName, TaskName, TotalAmount FROM ProjectWorkBOQItem Item inner join ProjectTask task on task.ProjectTaskId=Item.ProjectTaskId inner join Project on Project.ProjectId=task.ProjectId WHERE Item.ProjectWorkId = @id SELECT ProjectWorkItemId INTO #Item FROM ProjectWorkBOQItem WHERE ProjectWorkId = @id SELECT * FROM ProjectWorkBOQItemWork WHERE ProjectWorkItemId IN (SELECT ProjectWorkItemId FROM #Item) DROP TABLE #Item;"; using (var dataset = connection.QueryMultiple(query, new { id = ProjectWorkId })) { model = dataset.Read <BOQPreparing>().First(); model.BOQPreparingItem = dataset.Read <BOQPreparingItem>().AsList(); List <BOQPreparingItemWork> Work = dataset.Read <BOQPreparingItemWork>().AsList(); foreach (var item in model.BOQPreparingItem) { item.BOQPreparingItemWork = Work.Where(x => x.ProjectWorkItemId == item.ProjectWorkItemId).Select(x => x).ToList(); } } return(model); } }
public ActionResult ShowDetails(BOQPreparing model) { //ViewBag.Title = "Edit"; model.CreatedBy = UserID.ToString(); model.CreatedDate = System.DateTime.Now; var repo = new BOQPreparingRepository(); //var result1 = new BOQPreparingRepository().CHECK(model.QuerySheetId); //if (result1 > 0) //{ // TempData["error"] = "Sorry! Already Used!"; // TempData["QuerySheetRefNo"] = null; // return View("Edit", model); //} //else //{ try { int row = new BOQPreparingRepository().UpdateBOQPreparing(model); TempData["success"] = "Updated successfully"; TempData["error"] = ""; return(RedirectToAction("Index")); } //catch (SqlException) //{ // TempData["error"] = "Some error occured while connecting to database. Please check your network connection and try again."; //} catch (NullReferenceException) { TempData["error"] = "Some required data was missing. Please try again."; } catch (Exception) { TempData["error"] = "Some error occured. Please try again."; } return(RedirectToAction("Index")); //} }
public ActionResult BOQPreparing(BOQPreparing model) { model.CreatedBy = UserID.ToString(); model.CreatedDate = System.DateTime.Now; if (!ModelState.IsValid) { var allErrors = ModelState.Values.SelectMany(v => v.Errors); return(View(model)); } Result res = new BOQPreparingRepository().Insert(model); if (res.Value) { TempData["Success"] = "Saved Successfully!"; } else { } return(RedirectToAction("Index")); }
public int UpdateBOQPreparing(BOQPreparing model) { using (IDbConnection connection = OpenConnection(dataConnection)) { IDbTransaction txn = connection.BeginTransaction(); try { string sql = ""; var row = 0; foreach (BOQPreparingItem items in model.BOQPreparingItem) { foreach (BOQPreparingItemWork item in items.BOQPreparingItemWork) { item.ProjectWorkItemId = items.ProjectWorkItemId; item.ProjectWorkId = items.ProjectWorkId; sql = @"UPDATE ProjectWorkBOQItemWork SET ProjectWorkId=@ProjectWorkId,ProjectWorkItemId=@ProjectWorkItemId, ProjectWorkDescription=@ProjectWorkDescription,SubContractorId=@SubContractorId, PlanedStartDate=@PlanedStartDate,PlanedEndDate=@PlanedEndDate,WorkAmount=@WorkAmount WHERE ProjectWorkId = @ProjectWorkId"; row = connection.Execute(sql, item, txn); } } //InsertLoginHistory(dataConnection, model.CreatedBy, "Modify", "BOQ", model.ProjectWorkId.ToString(), "0"); txn.Commit(); return(row); } catch (Exception ex) { txn.Rollback(); throw ex; } } }
public string GetRefNo(BOQPreparing objBOQPreparing) { using (IDbConnection connection = OpenConnection(dataConnection)) { string RefNo = ""; var result = new BOQPreparing(); IDbTransaction trn = connection.BeginTransaction(); try { int internalid = DatabaseCommonRepository.GetInternalIDFromDatabase(connection, trn, typeof(BOQPreparing).Name, "0", 0); RefNo = internalid.ToString(); trn.Commit(); } catch (Exception ex) { trn.Rollback(); } return(RefNo); } }
public Result Insert(BOQPreparing model) { Result res = new Result(false); int id = 0; try { using (IDbConnection connection = OpenConnection(dataConnection)) { model.projectWorkRefNo = BOQPreparingRepository.GetNextRefNo(); string sql = @"INSERT INTO ProjectWorkBOQ (projectWorkRefNo,projectWorkDate,PreparedBy,ClientId,CreatedBy,CreatedDate) VALUES (@projectWorkRefNo,@projectWorkDate,@PreparedBy,@ClientId,@CreatedBy,@CreatedDate); SELECT CAST(SCOPE_IDENTITY() as int);"; model.ProjectWorkId = connection.Query <int>(sql, model).Single(); foreach (BOQPreparingItem items in model.BOQPreparingItem) { items.ProjectWorkId = model.ProjectWorkId; sql = @"INSERT INTO ProjectWorkBOQItem (ProjectWorkId,ProjectTaskId,TotalAmount,IsCompleted) VALUES (@ProjectWorkId,@ProjectTaskId,@TotalAmount,0); SELECT CAST(SCOPE_IDENTITY() as int);"; items.ProjectWorkItemId = connection.Query <int>(sql, items).Single(); foreach (BOQPreparingItemWork item in items.BOQPreparingItemWork) { item.ProjectWorkItemId = items.ProjectWorkItemId; item.ProjectWorkId = items.ProjectWorkId; sql = @"insert into ProjectWorkBOQItemWork(ProjectWorkId,ProjectWorkItemId,ProjectWorkDescription, SubContractorId,PlanedStartDate,PlanedEndDate,WorkAmount,PercentageComplete) Values (@ProjectWorkId,@ProjectWorkItemId,@ProjectWorkDescription,@SubContractorId, @PlanedStartDate,@PlanedEndDate,@WorkAmount,0)"; id = connection.Execute(sql, item); } } if (id > 0) { InsertLoginHistory(dataConnection, model.CreatedBy, "Create", "BOQ", id.ToString(), "0"); return(new Result(true)); } } } catch (Exception ex) { return(new Result(false, ex.InnerException == null ? ex.Message : ex.InnerException.Message)); } return(res); }