public List <DispRequestDto> GetDispatchStructure(int id)
{
try {
List <DispRequestDto> result = new List <DispRequestDto> ();
string strQuery = string.Format("select st.name structureFamily,ps.components_count as RequiredComponenentCount,(select count(*) from component c2 where proj_struct_id =ps.id) as CurrentComponentsCount, drs.id DispStructureId, drs.is_modification isModified, drs.proj_struct_id ProjectStructureId,dr.dispatch_no DCNumber,dr.id DispatchRequirementId,dr.quantity Quantity,dr.status status, dr.status_internal StatusInternal,dr.to_projectid projectId,ps.structure_id StructureId,ps.struct_code StructureCode,s.name StructrueName,p.name ProjectName,ps.structure_attributes_val StructureAttValue from dispatch_requirement dr inner join disp_req_structure drs on dr.id = drs.dispreq_id inner join project_structure ps on ps.id=drs.proj_struct_id inner join structures s on ps.structure_id =s.id inner join structure_type st on s.structure_type_id=st.id inner join project p on p.id =dr.to_projectid where dr.status <>'{1}' and dr.status <>'{2}' and dr.servicetype_id =4 ORDER BY dr.id DESC ", id, Util.GetDescription(commonEnum.SiteDispatchSatus.DISPATCHED).ToString(), Util.GetDescription(commonEnum.SiteDispatchSatus.REJECT).ToString());
result = _context.Query <DispRequestDto> ().FromSqlRaw(strQuery).ToList();
return(result);
} catch (Exception ex) {
throw ex;
}
}
public List <SurplusDetails> GetSurplus(SiteDeclarationDetailsPayload reqPayload)
{
try {
List <SurplusDetails> result = new List <SurplusDetails> ();
var sureplusDecl = _context.Query <SurplusDetails> ().FromSqlRaw("exec sp_getDeclaration {0}, {1}", reqPayload.role_name.ToString(), reqPayload.role_hierarchy).ToList();
result = _mapper.Map <List <SurplusDetails> > (sureplusDecl);
return(result);
} catch (Exception ex) {
throw ex;
}
}
public List <ReceiveDetail> GetReceiveDetails(int projectId)
{
try
{
List <ReceiveDetail> lstReceiveDetails = new List <ReceiveDetail>();
var receiveDetails = _context.Query <ReceiveDetail>().FromSqlRaw("exec SP_GetReceiveDetails {0}", projectId).ToList();
lstReceiveDetails = _mapper.Map <List <ReceiveDetail> >(receiveDetails);
return(lstReceiveDetails);
}
catch (Exception ex)
{
throw ex;
}
}
public List <PhysicalVerificationDetail> GetPhysicalVerificationStructure(int projectId)
{
try
{
List <PhysicalVerificationDetail> result = new List <PhysicalVerificationDetail>();
string strQuery = string.Format("select dr.sitereq_id SiteId,dr.to_projectid ProjectId,drs.proj_struct_id ProjectstructureId,dr.status Status,dr.status_internal StatusInternal,dr.role_id RoleId,s.name StructureName,ps.struct_code StructureCode,st.name StructureFamily from dispatch_requirement dr inner join disp_req_structure drs on dr.id=drs.dispreq_id inner join project_structure ps on drs.proj_struct_id =ps.id inner join structures s on ps.structure_id =s.id inner join structure_type st on s.structure_type_id =st.id where ps.structure_status = '{1}' and ps.current_status = '{2}' and dr.to_projectid ={0}", projectId, Util.GetDescription(commonEnum.StructureStatus.NOTAVAILABLE).ToString(), Util.GetDescription(commonEnum.StructureInternalStatus.INUSE).ToString());
result = _context.Query <PhysicalVerificationDetail>().FromSqlRaw(strQuery).ToList();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public List <AssignStructureDtlsOnly> GetAssignStructureDtls()
{
try {
List <AssignStructureDtlsOnly> result = new List <AssignStructureDtlsOnly> ();
result = _context.Query <AssignStructureDtlsOnly> ().FromSqlRaw("select ps.id as ProjectStructureId, ic.name ICName,bu.name BuName,ps.structure_id StructureId, ps.project_id ProjectId, ps.drawing_no DrawingNo,s.name StrcutureName, ps.struct_code StructureCode,st.name StrcutureTypeName, p.name ProjectName,ps.structure_attributes_val StructureAttributes,ps.components_count ComponentsCount, ps.structure_status Status, (select sum(weight) from component c where proj_struct_id = ps.id) as TotalWeight,ps.estimated_weight EstimatedWeight, ps.current_status CurrentStatus from project_structure ps inner join structures s on ps.structure_id = s.id inner join project p on ps.project_id = p.id inner join structure_type st on st.id =s.structure_type_id inner join independent_company ic on ic.id = p.ic_id inner join business_unit bu on bu.id = p.bu_id where ps.is_delete =0 and s.is_delete =0 and p.is_delete =0 and st.is_delete =0 order by ps.created_at desc").ToList();
//result = _mapper.Map<List<SurplusDetails>> (sureplusDecl);
// var result = _context.ProjectStructure.Include (x => x.Structure).Include (x => x.Project).Where (m => m.IsDelete == false).ToList ();
// List<AssignStructureDtlsOnly> response = _mapper.Map<List<AssignStructureDtlsOnly>> (result);
return(result);
} catch (Exception ex) {
throw ex;
}
}
public List <ScrapStructureWorkFlowDetail> GetScrapWorkflowDetails(ScrapWorkflowDetailsPayload reqPayload)
{
try {
List <ScrapStructureWorkFlowDetail> result = new List <ScrapStructureWorkFlowDetail> ();
var sureplusDecl = _context.Query <ScrapStructureWorkFlowDetail> ().FromSqlRaw("select ss.Id, ss.from_project_id as FromProjectId, ss.proj_struct_id as ProjStructId,ss.status as Status, ss.role_id as RoleId , (select name from structures s2 where id in (select structure_id from project_structure ps where id =ss.proj_struct_id )) as StructureName,(select struct_code from project_structure ps where id =ss.proj_struct_id) as StructCode ,(select structure_attributes_val from project_structure ps where id =ss.proj_struct_id) as StructureAttValue ,(select count(*) from component c2 where proj_struct_id =ps2.id) as CurrentComponentsCount, ps2.components_count as RequiredComponenentCount,p.name as FromProjectName, p.proj_code as ProjectCode, ss.created_at as CreatedDate,ps2.struct_code as StructureCode from scrap_structure ss inner join project_structure ps2 on ps2.id = ss.proj_struct_id inner join project p on p.id = ps2.project_id order by ss.created_at,ss.updated_at desc").ToList();
result = _mapper.Map <List <ScrapStructureWorkFlowDetail> > (sureplusDecl);
return(result);
} catch (Exception ex) {
throw ex;
}
}
// public List<SiteRequirementDetail> GetRequirementDetails()
// {
// try
// {
// List<SiteRequirementDetail> result = new List<SiteRequirementDetail>();
// var siteRequirements = _context.SiteRequirement.Where(x => x.IsDelete == false)
// .Include(s => s.SiteReqStructure).ToList();
// result = _mapper.Map<List<SiteRequirementDetail>>(siteRequirements);
// return result;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// }
public List <SiteRequirementDetail> GetRequirementDetails(SiteRequirementDetailPayload reqPayload)
{
try {
List <SiteRequirementDetail> result = new List <SiteRequirementDetail> ();
var siteRequirements = _context.Query <SiteRequirementDetail> ().FromSqlRaw("exec sp_GetRequirement {0}, {1}", reqPayload.role_name.ToString(), reqPayload.role_hierarchy).ToList();
result = _mapper.Map <List <SiteRequirementDetail> > (siteRequirements);
return(result);
} catch (Exception ex) {
throw ex;
}
}
public List <CodeList> GetVendorCodeListWithServiceType()
{
try {
List <CodeList> result = new List <CodeList> ();
var vendors = _context.Query <CodeList> ().FromSqlRaw("select sc.id , sc.name, scst.servicetype_id as ServiceTypeId from sub_contractor sc inner join subContractor_serviceType scst ON sc.id = scst .subcont_id where sc.is_status =1").ToList();
foreach (var item in vendors)
{
result.Add(new CodeList()
{
Id = item.Id,
Name = item.Name,
ServiceTypeId = item.ServiceTypeId
});
}
return(result);
} catch (Exception ex) {
throw ex;
}
}
public List <AsBuildStructure> GetStructureLst(int projectId)
{
try
{
List <AsBuildStructure> result = new List <AsBuildStructure>();
string strQuery = string.Format("select dr.dispatch_no as DispatchNo,st.name as StructureFamily,sc.name as VendorName,drs.id as DispReqStructId, dr.status Status, dr.status_internal StatusInternal ,drs.proj_struct_id ProjectStructureId,dr.id DispatchRequirementId,dr.quantity Quantity,dr.to_projectid projectId,ps.structure_id StructureId,ps.struct_code StructureCode,s.name StructrueName,p.name ProjectName,ps.structure_attributes_val StructureAttValue, ps.components_count as RequiredComponenentCount,(select sum(weight) from component c2 where proj_struct_id =ps.id) ComponentWeight , (select count(*) from component c2 where proj_struct_id =ps.id) as CurrentComponentsCount from dispatch_requirement dr inner join disp_req_structure drs on dr.id = drs.dispreq_id inner join project_structure ps on ps.id=drs.proj_struct_id inner join structures s on ps.structure_id =s.id inner join structure_type st on st.id=s.structure_type_id inner join project p on p.id =dr.to_projectid left outer join dispatchreq_subcont ds on ds.dispreq_id =dr.id left outer join sub_contractor sc on sc.id =ds.subcon_id where (dr.servicetype_id = 1 or dr.servicetype_id = 2 or (dr.servicetype_id = 4 and drs.is_modification=1))and ps.structure_status ='{1}' and ps.current_status ='{2}' and dr.to_projectid ={0} ORDER BY dr.id DESC", projectId, Util.GetDescription(commonEnum.StructureStatus.NOTAVAILABLE).ToString(), Util.GetDescription(commonEnum.StructureInternalStatus.INUSE).ToString());
result = _context.Query <AsBuildStructure>().FromSqlRaw(strQuery).ToList();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public List <SiteDispatchDetail> GetSiteDispatchDetails(SiteDispatchPayload siteDispatchPayload)
{
try {
List <SiteDispatchDetail> result = new List <SiteDispatchDetail> ();
var siteDispatchDetails = _context.Query <SiteDispatchDetail> ().FromSqlRaw("exec sp_getDispatch {0}, {1},{2},{3}", siteDispatchPayload.role_name.ToString(), siteDispatchPayload.role_hierarchy, siteDispatchPayload.ProjectId, siteDispatchPayload.VendorId).ToList();
result = _mapper.Map <List <SiteDispatchDetail> > (siteDispatchDetails);
return(result);
} catch (Exception ex) {
throw ex;
}
}
public ResponseMessage UpdateComponentHistory(DispComponentDto Component)
{
using (var transaction = _context.Database.BeginTransaction())
{
try
{
ResponseMessage responseMessage = new ResponseMessage();
List <ComponentDetailsInput> result = new List <ComponentDetailsInput>();
int id = Component.DispStructureId;
string strQuery = string.Format("select dsc.id DispstructCompId, dsc.disp_structure_id DispStructureId,c.id DispCompId from disp_structure_comp dsc inner join component c on dsc.disp_comp_id = c.id inner join disp_mod_stage_component dmsc on dmsc.dispstruct_comp_id =dsc.id inner join component_type ct on c.comp_type_id =ct.id where dsc.disp_structure_id ={0}", id);
result = _context.Query <ComponentDetailsInput>().FromSqlRaw(strQuery).ToList();
List <DispStructureComp> dispStructureComp = _context.DispStructureComp.Where(w => w.DispStructureId == Component.DispStructureId).ToList();
result.ForEach(item =>
{
Component compDetails =
_context.Component.Single(w => w.Id == item.DispCompId);
ComponentHistory AddItem = new ComponentHistory();
if (compDetails != null)
{
AddItem.Weight = compDetails.Weight;
AddItem.Leng = compDetails.Leng;
AddItem.Breath = compDetails.Breath;
AddItem.Height = compDetails.Height;
AddItem.Thickness = compDetails.Thickness;
//AddItem.MakeType=compDetails.MakeType;
AddItem.CreatedAt = DateTime.Now;
AddItem.CreatedBy = 1; //To do
AddItem.ProjStructId = compDetails.ProjStructId;
AddItem.CompId = compDetails.CompId;
AddItem.ProjStructId = compDetails.ProjStructId;
AddItem.CompTypeId = compDetails.CompTypeId;
}
_context.ComponentHistory.Add(AddItem);
_context.SaveChanges();
DispModStageComponent compModStageDetails = _context.DispModStageComponent.OrderByDescending(o => o.CreatedAt).FirstOrDefault(x => x.DispstructCompId == item.DispstructCompId);
compDetails.Weight = compModStageDetails.Weight;
compDetails.Leng = compModStageDetails.Leng;
compDetails.Breath = compModStageDetails.Breath;
compDetails.Height = compModStageDetails.Height;
compDetails.Thickness = compModStageDetails.Thickness;
compDetails.MakeType = compModStageDetails.MakeType;
compDetails.ProjStructId = compDetails.ProjStructId;
_context.Component.Update(compDetails);
_context.SaveChanges();
});
DispReqStructure structid =
_context.DispReqStructure.FirstOrDefault(w => w.ProjStructId == Component.ProjectStructureId &&
w.Id == Component.DispStructureId);
if (Component.IsVendor)
{
if (structid != null)
{
{
responseMessage = _dispatchReqSubConRepository.OSAssignVendor(Component.OSDispatchReqSubCont);
}
structid.DispStructStatus = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString();
_context.DispReqStructure.Update(structid);
_context.SaveChanges();
}
}
if (Component.IsSite)
{
DispReqStructure dispstructid =
_context.DispReqStructure.SingleOrDefault(w => w.ProjStructId == Component.ProjectStructureId &&
w.Id == Component.DispStructureId);
if (dispstructid != null)
{
dispstructid.DispStructStatus = Util.GetDescription(commonEnum.SiteDispatchSatus.READYTODELIVER).ToString();
_context.DispReqStructure.Update(dispstructid);
_context.SaveChanges();
}
}
DisreqStatusHistory disReqHis = new DisreqStatusHistory();
DispatchRequirement disreq = _context.DispatchRequirement
.SingleOrDefault(w => w.Id == Component.DispatchRequirementId);
var totalCount = _context.DispReqStructure.Where(x => x.DispreqId == Component.DispatchRequirementId).Count();
var appCount = _context.DispReqStructure.Where(x => x.DispreqId == Component.DispatchRequirementId && (x.DispStructStatus == Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString() || x.DispStructStatus == Util.GetDescription(commonEnum.SiteDispatchSatus.READYTODELIVER).ToString())).Count();
// if(disreq!=null){
// throw new ValueNotFoundException ("DispatchRequirementId Not Available");
// }
if (totalCount != appCount)
{
disreq.Status = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCPARIALLYMODIFYAPRD).ToString();
disreq.StatusInternal = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCPARIALLYMODIFYAPRD).ToString();
disreq.UpdatedBy = 1; //To DO
disReqHis.Status = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCPARIALLYMODIFYAPRD).ToString();
disReqHis.StatusInternal = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCPARIALLYMODIFYAPRD).ToString();
disreq.UpdatedAt = DateTime.Now;
disReqHis.DispatchNo = disreq.DispatchNo;
disReqHis.RoleId = disreq.RoleId;
disReqHis.CreatedBy = 1; //To DO
disReqHis.CreatedAt = DateTime.Now;
}
else
{
disreq.Status = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString();
disreq.StatusInternal = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString();
disreq.UpdatedBy = 1; //To DO
disreq.UpdatedAt = DateTime.Now;
disReqHis.DispatchNo = disreq.DispatchNo;
disReqHis.Status = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString();
disReqHis.StatusInternal = Util.GetDescription(commonEnum.SiteDispatchSatus.TWCCMODIFYAPRD).ToString();
disReqHis.RoleId = disreq.RoleId;
disReqHis.CreatedBy = 1; //To DO
disReqHis.CreatedAt = DateTime.Now;
}
_context.DispatchRequirement.Update(disreq);
_context.DisreqStatusHistory.Add(disReqHis);
_context.SaveChanges();
responseMessage.Message = "Component updated";
transaction.Commit();
return(responseMessage);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
