}//SaleOrderId is received here
//public ActionResult JobCardDetails(int id)
//{
// return PartialView("_JobCardDetails", new ProjectCompletionRepository().GetJobCardDetails(id));
//}
public ActionResult ItemBatchDetails(int id)//SaleOrderId is received here
{
ProjectCompletion model = new ProjectCompletion();
model.ItemBatches = new ProjectCompletionRepository().GetSerialNos(id).ToList <ItemBatch>();
return(PartialView("_ItemBatchDetails", model));
}
public ProjectCompletion GetProjectCompletion(int ProjectCompletionId)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
try
{
string query = string.Empty;
query = @" SELECT PC.*,SO.SaleOrderId, SO.SaleOrderRefNo,SO.SaleOrderDate,
C.CustomerName,QS.ProjectName,'' Location,ISNULL(SQ.ProjectCompletionId,0)IsUsed
FROM ProjectCompletion PC
INNER JOIN SaleOrder SO ON PC.SaleOrderId = SO.SaleOrderId
INNER JOIN Customer C ON SO.CustomerId = C.CustomerId
LEFT JOIN SalesQuotation SQ ON SO.SalesQuotationId = SQ.SalesQuotationId
INNER JOIN QuerySheet QS ON SQ.QuerySheetId = QS.QuerySheetId
WHERE PC.ProjectCompletionId = @ProjectCompletionId
SELECT
QI.RoomDetails,ExternalRoomDimension,Refrigerant,
ISNULL(QI.Quantity, 1) Quantity,QI.QuerySheetItemId,
STUFF((SELECT ', ' + T2.ItemName FROM QuerySheetItemUnit T1
LEFT JOIN Item T2 ON T1.CondenserUnitId = T2.ItemId
WHERE T1.QuerySheetItemId = QI.QuerySheetItemId FOR XML PATH('')), 1, 2, '') CondensingUnit,
STUFF((SELECT ', ' + T2.ItemName FROM QuerySheetItemUnit T1
LEFT JOIN Item T2 ON T1.EvaporatorUnitId = T2.ItemId
WHERE T1.QuerySheetItemId = QI.QuerySheetItemId FOR XML PATH('')), 1, 2, '') Evaporator,
QI.TemperatureRequired
FROM ProjectCompletion PC
INNER JOIN SaleOrder SO ON SO.SaleOrderId = PC.SaleOrderId
LEFT JOIN SalesQuotation SQ ON SQ.SalesQuotationId = SO.SalesQuotationId
LEFT JOIN QuerySheetItem QI ON QI.QuerySheetId=SQ.QuerySheetId
INNER JOIN QuerySheet QS ON QS.QuerySheetId=QI.QuerySheetId
INNER JOIN Customer C ON SQ.CustomerId = C.CustomerId
WHERE PC.ProjectCompletionId = @ProjectCompletionId";
ProjectCompletion _model = new ProjectCompletion();
using (var dataset = connection.QueryMultiple(query, new { ProjectCompletionId = ProjectCompletionId }))
{
_model = dataset.Read <ProjectCompletion>().First();
_model.ProjectRoomAndUnitDetails = dataset.Read <ProjectRoomAndUnitDetails>().ToList();
}
return(_model);
}
catch (Exception ex)
{
throw ex;
}
}
}
public int InsertItemBatch(ProjectCompletion model, IDbConnection connection, IDbTransaction txn)
{
string query = "UPDATE ItemBatch SET ProjectCompletionId = " + model.ProjectCompletionId + @", WarrantyStartDate = @WarrantyStartDate, WarrantyExpireDate = @WarrantyExpireDate
WHERE ItemBatchId = @ItemBatchId";
int id = 0;
foreach (var item in model.ItemBatches)
{
id = connection.Execute(query, item, txn);
if (id == 0)
{
throw new Exception("Updated row count returned as 0");
}
}
return(id);
}
// public List<ItemBatch> GetSerialNosByProjectCompletioId(int ProjectCompletionId)
// {
// using (IDbConnection connection = OpenConnection(dataConnection))
// {
// try
// {
// string query = @"SELECT
// IB.SerialNo,
// I.ItemName,
// CONVERT(VARCHAR, IB.WarrantyStartDate, 106) WarrantyStartDate,
// CONVERT(VARCHAR, IB.WarrantyExpireDate, 106) WarrantyExpireDate
// FROM ItemBatch IB
// LEFT JOIN GRNItem GI ON IB.GRNItemId = GI.GRNItemId
// INNER JOIN Item I ON GI.ItemId = I.ItemId
// WHERE IB.ProjectCompletionId = @ProjectCompletionId";
// return connection.Query<ItemBatch>(query, new { ProjectCompletionId = ProjectCompletionId }).ToList();
// }
// catch
// {
// return new List<ItemBatch>();
// }
// }
// }
public int UpdateProjectCompletion(ProjectCompletion objProjectCompletion)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
string sql = @"UPDATE
ProjectCompletion SET ProjectCompletionRefNo=@ProjectCompletionRefNo,ProjectCompletionDate=@ProjectCompletionDate,
ChillerTemperature=@ChillerTemperature,ChillerDimension=@ChillerDimension,ChillerCondensingUnit=@ChillerCondensingUnit,
ChillerEvaporator=@ChillerEvaporator,ChillerRefrigerant=@ChillerRefrigerant,ChillerQuantity=@ChillerQuantity,
FreezerTemperature=@FreezerTemperature,FreezerDimension=@FreezerDimension,FreezerCondensingUnit=@FreezerCondensingUnit,
FreezerEvaporator=@FreezerEvaporator,FreezerRefrigerant=@FreezerRefrigerant,FreezerQuantity=@FreezerQuantity,
SaleOrderId=@SaleOrderId,OrganizationId=@OrganizationId,CreatedDate=@CreatedDate,CreatedBy=@CreatedBy,isActive =1,
ProjectWarrantyExpiryDate = @ProjectWarrantyExpiryDate
WHERE ProjectCompletionId = @ProjectCompletionId;";
try
{
var id = connection.Execute(sql, objProjectCompletion, txn);
int i = 0;
if (objProjectCompletion.ItemBatches != null && objProjectCompletion.ItemBatches.Count > 0)
{
InsertItemBatch(objProjectCompletion, connection, txn);
}
//foreach (var model in objProjectCompletion.ItemBatches)
//{
// model.ProjectCompletionId = objProjectCompletion.ProjectCompletionId;
// //ProjectCompletionRepository repo = new ProjectCompletionRepository();
// if (InsertItemBatch(model, connection, txn) == 0) throw new Exception("Some error occured while saving jobcard task");
// i++;
//}
InsertLoginHistory(dataConnection, objProjectCompletion.CreatedBy, "Update", "Project Completion", id.ToString(), objProjectCompletion.OrganizationId.ToString());
txn.Commit();
return(id);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public string InsertProjectCompletion(ProjectCompletion model)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
try
{
string query = @"INSERT INTO ProjectCompletion
(ProjectCompletionRefNo,ProjectCompletionDate,ChillerTemperature,
ChillerDimension,ChillerCondensingUnit,ChillerEvaporator,
ChillerRefrigerant,ChillerQuantity,FreezerTemperature,FreezerDimension,
FreezerCondensingUnit,FreezerEvaporator,FreezerRefrigerant,FreezerQuantity,
SaleOrderId,OrganizationId,CreatedDate,CreatedBy,isActive, ProjectWarrantyExpiryDate)
VALUES
(@ProjectCompletionRefNo,@ProjectCompletionDate,@ChillerTemperature,
@ChillerDimension,@ChillerCondensingUnit,@ChillerEvaporator,
@ChillerRefrigerant,@ChillerQuantity,@FreezerTemperature,@FreezerDimension,
@FreezerCondensingUnit,@FreezerEvaporator,@FreezerRefrigerant,@FreezerQuantity,
@SaleOrderId,@OrganizationId,@CreatedDate,@CreatedBy,1, @ProjectWarrantyExpiryDate);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
model.ProjectCompletionRefNo = DatabaseCommonRepository.GetNewDocNo(connection, model.OrganizationId, 30, true, txn);
model.ProjectCompletionId = connection.Query <int>(query, model, txn).First();
if (model.ItemBatches != null && model.ItemBatches.Count > 0)
{
InsertItemBatch(model, connection, txn);
}
InsertLoginHistory(dataConnection, model.CreatedBy, "Create", typeof(ProjectCompletion).Name, model.ProjectCompletionId.ToString(), model.OrganizationId.ToString());
txn.Commit();
return(model.ProjectCompletionRefNo);
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
}
}
public ActionResult Complete(ProjectCompletion model)
{
try
{
model.CreatedBy = UserID.ToString(); model.CreatedDate = DateTime.Today; model.OrganizationId = OrganizationId;
if (model.ItemBatches != null && model.ItemBatches.Count > 0)
{
foreach (ItemBatch item in model.ItemBatches)
{
item.WarrantyStartDate = model.ProjectCompletionDate;
item.WarrantyExpireDate = model.ProjectCompletionDate.AddMonths(item.WarrantyPeriodInMonths ?? 0).AddDays(-1);
}
}
string ref_no = new ProjectCompletionRepository().InsertProjectCompletion(model);
TempData["success"] = "Saved Successfully. Reference No. is " + ref_no;
return(RedirectToAction("Pending"));
}
catch (Exception)
{
TempData["error"] = "Some error occured. Please try agian.";
}
return(View(model));
}
public ActionResult Details(ProjectCompletion model)
{
try
{
model.CreatedBy = UserID.ToString(); model.CreatedDate = DateTime.Today; model.OrganizationId = OrganizationId;
if (model.ItemBatches != null && model.ItemBatches.Count > 0)
{
foreach (ItemBatch item in model.ItemBatches)
{
item.WarrantyStartDate = model.ProjectCompletionDate;
item.WarrantyExpireDate = model.ProjectCompletionDate.AddMonths(item.WarrantyPeriodInMonths ?? 0).AddDays(-1);
}
}
new ProjectCompletionRepository().UpdateProjectCompletion(model);
TempData["success"] = "Updated Successfully (" + model.ProjectCompletionRefNo + ")";
return(RedirectToAction("Index"));
}
catch (Exception)
{
TempData["error"] = "Some error occurred. Please try again.";
}
return(View(model));
}
public ProjectCompletion GetProjectCompletionHD(int ProjectCompletionId, int OrganizationId)
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
try
{
string query = string.Empty;
#region
//old query
// query = @"
// SELECT O.*,PC.*,SO.SaleOrderId, SO.SaleOrderRefNo,SO.SaleOrderDate,
// C.CustomerName,QS.ProjectName,'' Location,ISNULL(SQ.ProjectCompletionId,0)IsUsed,
// QI.RoomDetails,ExternalRoomDimension FreezerDimension,Refrigerant FreezerRefrigerant,
// ISNULL(QI.Quantity, 1) FreezerQuantity ,QI.QuerySheetItemId,US.UserName CreatedUser,US.[Signature] CreatedUsersig,
// DI.DesignationName CreatedDes,
// STUFF((SELECT ', ' + T2.ItemName FROM QuerySheetItemUnit T1
// LEFT JOIN Item T2 ON T1.CondenserUnitId = T2.ItemId
// WHERE T1.QuerySheetItemId = QI.QuerySheetItemId FOR XML PATH('')), 1, 2, '') FreezerCondensingUnit,
// STUFF((SELECT ', ' + T2.ItemName FROM QuerySheetItemUnit T1
// LEFT JOIN Item T2 ON T1.EvaporatorUnitId = T2.ItemId
// WHERE T1.QuerySheetItemId = QI.QuerySheetItemId FOR XML PATH('')), 1, 2, '') FreezerEvaporator,
// QI.TemperatureRequired FreezerTemperature
// FROM ProjectCompletion PC
// INNER JOIN SaleOrder SO ON PC.SaleOrderId = SO.SaleOrderId
// INNER JOIN Customer C ON SO.CustomerId = C.CustomerId
// INNER JOIN SalesQuotation SQ ON SO.SalesQuotationId = SQ.SalesQuotationId
// INNER JOIN QuerySheet QS ON SQ.QuerySheetId = QS.QuerySheetId
// INNER JOIN QuerySheetItem QI ON QI.QuerySheetId=SQ.QuerySheetId
// INNER JOIN Organization O ON O.OrganizationId=PC.OrganizationId
// left JOIN Country ORR ON ORR.CountryId=O.Country
// left join [User] US ON US.[UserId]=PC.CreatedBy
// left join Designation DI ON DI.DesignationId=US.DesignationId
// WHERE PC.ProjectCompletionId =@ProjectCompletionId";
#endregion
query = @"
SELECT O.*,PC.*,SO.SaleOrderId, SO.SaleOrderRefNo,SO.SaleOrderDate,
C.CustomerName,QS.ProjectName,'' Location,ISNULL(SQ.ProjectCompletionId,0)IsUsed,
US.UserName CreatedUser,US.[Signature] CreatedUsersig,
DI.DesignationName CreatedDes
FROM ProjectCompletion PC
INNER JOIN SaleOrder SO ON PC.SaleOrderId = SO.SaleOrderId
INNER JOIN Customer C ON SO.CustomerId = C.CustomerId
INNER JOIN SalesQuotation SQ ON SO.SalesQuotationId = SQ.SalesQuotationId
INNER JOIN QuerySheet QS ON SQ.QuerySheetId = QS.QuerySheetId
INNER JOIN Organization O ON O.OrganizationId=PC.OrganizationId
left JOIN Country ORR ON ORR.CountryId=O.Country
left join [User] US ON US.[UserId]=PC.CreatedBy
left join Designation DI ON DI.DesignationId=US.DesignationId
WHERE PC.ProjectCompletionId =@ProjectCompletionId";
ProjectCompletion ProjectCompletion = connection.Query <ProjectCompletion>(query, new { ProjectCompletionId = ProjectCompletionId, OrganizationId = OrganizationId }).FirstOrDefault();
return(ProjectCompletion);
}
catch (Exception ex)
{
throw ex;
}
}
}
