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);
}
