public HospitalizacionHabitacionList GetHabitaciónHospi(ref OperationResult pobjOperationResult, int habId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from a in dbContext.hospitalizacionhabitacion
join b in dbContext.hospitalizacion on a.v_HopitalizacionId equals b.v_HopitalizacionId
join J1 in dbContext.systemparameter on new { a = 309, b = a.i_HabitacionId.Value }
equals new { a = J1.i_GroupId, b = J1.i_ParameterId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
where a.i_IsDeleted == 0 && J1.i_ParameterId == habId
select new HospitalizacionHabitacionList
{
v_HopitalizacionId = b.v_HopitalizacionId,
i_HabitacionId = J1.i_ParameterId,
NroHabitacion = J1.v_Value1,
d_Precio = a.d_Precio.Value
}
).FirstOrDefault();
pobjOperationResult.Success = 1;
return(query);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <LocationList> GetLocationPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.location
join B in dbContext.organization on A.v_OrganizationId equals B.v_OrganizationId
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new LocationList
{
v_LocationId = A.v_LocationId,
v_OrganizationId = A.v_OrganizationId,
v_OrganizationName = B.v_Name,
v_Name = A.v_Name,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <LocationList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <ProductList> GetProductsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.product
join B in dbContext.datahierarchy on new { a = A.i_CategoryId.Value, b = 103 } equals new { a = B.i_ItemId, b = B.i_GroupId }
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
orderby B.v_Value1, A.v_Name ascending
select new ProductList
{
v_ProductId = A.v_ProductId,
i_CategoryId = A.i_CategoryId.Value,
v_CategoryName = B.v_Value1,
v_Name = A.v_Name,
v_GenericName = A.v_GenericName,
v_BarCode = A.v_BarCode,
v_ProductCode = A.v_ProductCode,
v_Brand = A.v_Brand,
v_Model = A.v_Model,
v_SerialNumber = A.v_SerialNumber,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <ProductList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public BoardSupplier GetSuppliers(BoardSupplier data)
{
try
{
string filterSupplierRazonSocial = string.IsNullOrWhiteSpace(data.RazonSocial) ? "" : data.RazonSocial;
string filterSupplierRuc = string.IsNullOrWhiteSpace(data.RazonSocial) ? "" : data.RazonSocial;
int skip = (data.Index - 1) * data.Take;
DatabaseContext dbContext = new DatabaseContext();
var query = (from A in dbContext.Supplier
join C in dbContext.DataHierarchy on A.i_SectorTypeId equals C.i_ItemId
join J1 in dbContext.SystemUser on new { i_InsertUserId = A.i_InsertUserId }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.SystemUser on new { i_UpdateUserId = A.i_UpdateUserId }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where C.i_GroupId == 104 && (data.SectorId == -1 || A.i_SectorTypeId == data.SectorId) &&
(A.v_Name.Contains(filterSupplierRazonSocial) || A.v_IdentificationNumber.Contains(filterSupplierRuc))
select new SupplierList
{
SupplierId = A.v_SupplierId,
SectorTypeId = (int)A.i_SectorTypeId,
SectorTypeIdName = C.v_Value1,
IdentificationNumber = A.v_IdentificationNumber,
Name = A.v_Name,
Address = A.v_Address,
PhoneNumber = A.v_PhoneNumber,
Mail = A.v_Mail,
CreationUser = J1.v_UserName,
UpdateUser = J2.v_UserName,
CreationDate = A.d_InsertDate,
UpdateDate = A.d_UpdateDate,
IsDeleted = A.i_IsDeleted
}).ToList();
if (data.Take > 0)
{
query = query.Skip(skip).Take(data.Take).ToList();
}
data.TotalRecords = query.ToList().Count;
data.List = query.OrderBy(a => a.Name).ToList();
return(data);
}
catch (Exception ex)
{
return(null);
}
}
public List <ExamsCustom> GetMedicalExam(string componentName)
{
try
{
var query = from A in ctx.Component
join J1 in ctx.SystemUser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in ctx.SystemUser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join B in ctx.SystemParameter on new { a = A.i_CategoryId, b = 116 } // CATEGORIA DEL EXAMEN
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join E in ctx.SystemParameter on new { a = A.i_DiagnosableId.Value, b = 111 } equals new { a = E.i_ParameterId, b = E.i_GroupId }
join F in ctx.SystemParameter on new { a = A.i_ComponentTypeId.Value, b = 126 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
where A.i_IsDeleted == 0 && (A.v_Name.Contains(componentName) || componentName == null)
select new ExamsCustom
{
v_ComponentId = A.v_ComponentId,
v_ComponentName = A.v_Name,
i_CategoryId = A.i_CategoryId,
v_CategoryName = B.v_Value1,
v_DiagnosableName = E.v_Value1,
v_ComponentTypeName = F.v_Value1,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted.Value,
r_Price = A.r_BasePrice,
i_ComponentTypeId = A.i_ComponentTypeId,
i_index = A.i_UIIndex.Value,
v_IdUnidadProductiva = A.v_IdUnidadProductiva
};
List <ExamsCustom> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public SystemUserList GetSystemUserName(ref OperationResult pobjOperationResult, int SystemUserId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from su1 in dbContext.systemuser
join A in dbContext.person on su1.v_PersonId equals A.v_PersonId
join B in dbContext.professional on A.v_PersonId equals B.v_PersonId
join J1 in dbContext.datahierarchy on new { a = 121, b = su1.i_RolVentaId.Value }
equals new { a = J1.i_GroupId, b = J1.i_ItemId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
where su1.i_IsDeleted == 0 && su1.i_SystemUserId == SystemUserId
select new SystemUserList
{
v_PersonName = A.v_FirstName + " " + A.v_FirstLastName + " " + A.v_SecondLastName,
v_RolVenta = J1.v_Value1,
i_RolVenta = su1.i_RolVentaId,
InfAdicional = B.v_ProfessionalInformation,
v_PersonId = B.v_PersonId
}
).FirstOrDefault();
//List<SystemUserList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(query);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <ServiceComponentList> GetServiceComponentsReport(string pstrServiceId)
{
//mon.IsActive = true;
int isDeleted = 0;
try
{
DatabaseContext dbContext = new DatabaseContext();
#region serviceComponentFields
var serviceComponentFields = (from A in dbContext.ServiceComponent
join B in dbContext.ServiceComponentFields on A.v_ServiceComponentId equals B.v_ServiceComponentId
join C in dbContext.ServiceComponentFieldValues on B.v_ServiceComponentFieldsId equals C.v_ServiceComponentFieldsId
join cfs in dbContext.ComponentFields on B.v_ComponentFieldId equals cfs.v_ComponentFieldId
join D in dbContext.ComponentField on B.v_ComponentFieldId equals D.v_ComponentFieldId
join cm in dbContext.Component on cfs.v_ComponentId equals cm.v_ComponentId
join dh in dbContext.DataHierarchy on new { a = 105, b = D.i_MeasurementUnitId.Value }
equals new { a = dh.i_GroupId, b = dh.i_ItemId } into dh_join
from dh in dh_join.DefaultIfEmpty()
where (A.v_ServiceId == pstrServiceId) &&
//(cm.v_ComponentId == pstrComponentId) &&
(A.i_IsDeleted == isDeleted) &&
(B.i_IsDeleted == isDeleted) &&
(C.i_IsDeleted == isDeleted)
select new ServiceComponentFieldsList
{
v_ServiceComponentFieldsId = B.v_ServiceComponentFieldsId,
v_ComponentFieldsId = B.v_ComponentFieldId,
v_ComponentFielName = D.v_TextLabel,
v_Value1 = C.v_Value1 == "" ? null : C.v_Value1,
i_GroupId = D.i_GroupId.Value,
v_MeasurementUnitName = dh.v_Value1,
v_ComponentId = cm.v_ComponentId,
v_ServiceComponentId = A.v_ServiceComponentId
}).ToList();
int rpta = 0;
var _finalQuery = (from a in serviceComponentFields
let value1 = int.TryParse(a.v_Value1, out rpta)
join sp in dbContext.SystemParameter on new { a = a.i_GroupId, b = rpta }
equals new { a = sp.i_GroupId, b = sp.i_ParameterId } into sp_join
from sp in sp_join.DefaultIfEmpty()
select new ServiceComponentFieldsList
{
v_ServiceComponentFieldsId = a.v_ServiceComponentFieldsId,
v_ComponentFieldsId = a.v_ComponentFieldsId,
v_ComponentFielName = a.v_ComponentFielName,
i_GroupId = a.i_GroupId,
v_Value1 = a.v_Value1,
v_Value1Name = sp == null ? "" : sp.v_Value1,
v_MeasurementUnitName = a.v_MeasurementUnitName,
v_ComponentId = a.v_ComponentId,
v_ConclusionAndDiagnostic = a.v_Value1 + " / " + GetServiceComponentDiagnosticsReport(pstrServiceId, a.v_ComponentId),
v_ServiceComponentId = a.v_ServiceComponentId
}).ToList();
#endregion
var components = (from aaa in dbContext.ServiceComponent
join bbb in dbContext.Component on aaa.v_ComponentId equals bbb.v_ComponentId
join J1 in dbContext.SystemUser on new { i_InsertUserId = aaa.i_InsertUserId }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.SystemUser on new { i_UpdateUserId = aaa.i_UpdateUserId }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join fff in dbContext.SystemParameter on new { a = bbb.i_CategoryId, b = 116 } // CATEGORIA DEL EXAMEN
equals new { a = fff.i_ParameterId, b = fff.i_GroupId } into J5_join
from fff in J5_join.DefaultIfEmpty()
// Usuario Medico Evaluador / Medico Aprobador ****************************
join me in dbContext.SystemUser on aaa.i_ApprovedUpdateUserId equals me.i_SystemUserId into me_join
from me in me_join.DefaultIfEmpty()
join pme in dbContext.Professional on me.v_PersonId equals pme.v_PersonId into pme_join
from pme in pme_join.DefaultIfEmpty()
//*********************************************************************
where (aaa.v_ServiceId == pstrServiceId) &&
(bbb.i_ComponentTypeId == (int?)Enumeratores.ComponentType.Examen) &&
(aaa.i_IsDeleted == 0) &&
(aaa.i_IsRequiredId == (int?)Enumeratores.SiNo.Si)
//orderby bbb.i_CategoryId, bbb.v_Name
select new
{
v_ComponentId = bbb.v_ComponentId,
v_ComponentName = bbb.v_Name,
v_ServiceComponentId = aaa.v_ServiceComponentId,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = aaa.d_InsertDate,
d_UpdateDate = aaa.d_UpdateDate,
i_IsDeleted = aaa.i_IsDeleted.Value,
i_CategoryId = bbb.i_CategoryId,
v_CategoryName = fff.v_Value1,
i_ServiceComponentStatusId = aaa.i_ServiceComponentStatusId,
DiagnosticRepository = (from dr in aaa.service.diagnosticrepository
where (dr.v_ServiceId == pstrServiceId) &&
(dr.v_ComponentId == aaa.v_ComponentId && dr.i_IsDeleted == 0)
select new DiagnosticRepositoryList
{
v_DiseasesId = dr.diseases.v_DiseasesId,
v_DiseasesName = dr.diseases.v_Name
}),
FirmaMedico = pme.b_SignatureImage
}).AsEnumerable().Select(p => new ServiceComponentList
{
v_ComponentId = p.v_ComponentId,
v_ComponentName = p.v_ComponentName,
v_ServiceComponentId = p.v_ServiceComponentId,
v_CreationUser = p.v_CreationUser,
v_UpdateUser = p.v_UpdateUser,
d_CreationDate = p.d_CreationDate,
d_UpdateDate = p.d_UpdateDate,
i_IsDeleted = p.i_IsDeleted,
i_CategoryId = p.i_CategoryId,
v_CategoryName = p.v_CategoryName,
i_ServiceComponentStatusId = p.i_ServiceComponentStatusId,
DiagnosticRepository = p.DiagnosticRepository.ToList(),
FirmaMedico = p.FirmaMedico
}).ToList();
components.Sort((x, y) => x.v_ComponentId.CompareTo(y.v_ComponentId));
components.ForEach(a => a.ServiceComponentFields = _finalQuery.FindAll(p => p.v_ComponentId == a.v_ComponentId));
return(components);
}
catch (Exception)
{
throw;
}
}
public List <SupplierList> GetSuppliersPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.supplier
join C in dbContext.datahierarchy on A.i_SectorTypeId equals C.i_ItemId
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where C.i_GroupId == 104
select new SupplierList
{
v_SupplierId = A.v_SupplierId,
i_SectorTypeId = (int)A.i_SectorTypeId,
v_SectorTypeIdName = C.v_Value1,
v_IdentificationNumber = A.v_IdentificationNumber,
v_Name = A.v_Name,
v_Address = A.v_Address,
v_PhoneNumber = A.v_PhoneNumber,
v_Mail = A.v_Mail,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <SupplierList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <ServiceOrderList> GetReportCotizacionConsolidado(string pstServiceOrderId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
//var LogoEmpresa = GetLogoMedicalCenter();
var MedicalCenter = new ServiceBL().GetInfoMedicalCenter();
var query = from A in dbContext.serviceorderdetail
join B in dbContext.protocol on A.v_ProtocolId equals B.v_ProtocolId
join C in dbContext.serviceorder on A.v_ServiceOrderId equals C.v_ServiceOrderId
join E in dbContext.organization on B.v_CustomerOrganizationId equals E.v_OrganizationId
join I in dbContext.groupoccupation on B.v_GroupOccupationId equals I.v_GroupOccupationId
join F in dbContext.systemparameter on new { a = B.i_EsoTypeId.Value, b = 118 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join su in dbContext.systemuser on C.i_InsertUserId.Value equals su.i_SystemUserId into su_join
from su in su_join.DefaultIfEmpty()
join pr in dbContext.professional on su.v_PersonId equals pr.v_PersonId into pr_join
from pr in pr_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_ServiceOrderId == pstServiceOrderId
select new ServiceOrderList
{
v_ServiceOrderId = A.v_ServiceOrderId,
v_ProtocolId = A.v_ProtocolId,
v_Protocol = B.v_Name,
r_Price = A.r_ProtocolPrice,
CantidadTrabajadores = A.i_NumberOfWorkerProtocol,
v_Organization = E.v_Name,
RucCliente = E.v_IdentificationNumber,
v_Address = E.v_Address,
d_InsertDate = C.d_InsertDate.Value,
EmpresaPropietaria = MedicalCenter.v_Name,
GESO = I.v_Name,
v_EsoType = F.v_Value1,
EmpresaPropietariaDireccion = MedicalCenter.v_Address,
EmpresaPropietariaTelefono = MedicalCenter.v_PhoneNumber,
EmpresaPropietariaEmail = MedicalCenter.v_Mail,
Firma = pr.b_SignatureImage,
};
//var query = from A in dbContext.serviceorder
// join A1 in dbContext.serviceorderdetail on new { a = pstServiceOrderId} equals new { a = A1.v_ServiceOrderId}
// join B in dbContext.systemparameter on new { a = A.i_ServiceOrderStatusId.Value, b = 194 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
// join C in dbContext.serviceorderdetail on A.v_ServiceOrderId equals C.v_ServiceOrderId
// join D in dbContext.protocol on C.v_ProtocolId equals D.v_ProtocolId
// join E in dbContext.organization on D.v_CustomerOrganizationId equals E.v_OrganizationId
// join F in dbContext.systemparameter on new { a = D.i_EsoTypeId.Value, b = 118 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
// join G in dbContext.protocolcomponent on D.v_ProtocolId equals G.v_ProtocolId
// join H in dbContext.component on G.v_ComponentId equals H.v_ComponentId
// join I in dbContext.groupoccupation on D.v_GroupOccupationId equals I.v_GroupOccupationId
// join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
// equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
// from J1 in J1_join.DefaultIfEmpty()
// join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
// equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
// from J2 in J2_join.DefaultIfEmpty()
// join su in dbContext.systemuser on A.i_InsertUserId.Value equals su.i_SystemUserId into su_join
// from su in su_join.DefaultIfEmpty()
// join pr in dbContext.professional on su.v_PersonId equals pr.v_PersonId into pr_join
// from pr in pr_join.DefaultIfEmpty()
// where A.i_IsDeleted == 0 && A.v_ServiceOrderId == pstServiceOrderId
// //&& G.i_IsDeleted == 0
// //&& D.v_ProtocolId == pstrProtocolId
// select new ServiceOrderList
// {
// v_ServiceOrderId = A.v_ServiceOrderId,
// v_CustomServiceOrderId = A.v_CustomServiceOrderId,
// v_Description = A.v_Description,
// v_Comentary = A.v_Comentary,
// i_NumberOfWorker = A1.i_NumberOfWorkerProtocol,
// r_TotalCost = A1.r_Total,
// d_DeliveryDate = A.d_DeliveryDate,
// RucCliente = E.v_IdentificationNumber,
// i_ServiceOrderStatusId = A.i_ServiceOrderStatusId,
// v_ServiceOrderStatusName = B.v_Value1,
// v_CreationUser = J1.v_UserName,
// v_UpdateUser = J2.v_UserName,
// d_CreationDate = A.d_InsertDate,
// d_UpdateDate = A.d_UpdateDate,
// v_ProtocolId = C.v_ProtocolId,
// v_Protocol = D.v_Name,
// v_Organization = E.v_Name,
// v_ContacName = E.v_ContacName,
// v_Address = E.v_Address,
// v_EsoType = F.v_Value1,
// v_ComponentName = H.v_Name,
// r_Price = G.r_Price, // H.r_BasePrice.Value,
// d_InsertDate = A.d_InsertDate.Value,
// Logo = MedicalCenter.b_Image,
// EmpresaPropietaria = MedicalCenter.v_Name,
// EmpresaPropietariaDireccion = MedicalCenter.v_Address,
// EmpresaPropietariaTelefono = MedicalCenter.v_PhoneNumber,
// EmpresaPropietariaEmail = MedicalCenter.v_Mail,
// GESO = I.v_Name,
// Firma = pr.b_SignatureImage,
// };
List <ServiceOrderList> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public List <DataHierarchyList> GetDataHierarchiesPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, int pintGroupId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.datahierarchy
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join J4 in dbContext.datahierarchy on new { ItemId = A.i_ParentItemId.Value, groupId = A.i_GroupId }
equals new { ItemId = J4.i_ItemId, groupId = J4.i_GroupId } into J4_join
from J4 in J4_join.DefaultIfEmpty()
where A.i_GroupId == pintGroupId &&
(A.i_IsDeleted == 0 || A.i_IsDeleted == null)
select new DataHierarchyList
{
i_GroupId = A.i_GroupId,
i_ItemId = A.i_ItemId,
v_Value1 = A.v_Value1,
i_ParentItemId = A.i_ParentItemId.Value,
v_ParentItemName = J4.v_Value1,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
query = query.Take(pintResultsPerPage.Value);
}
List <DataHierarchyList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = ex.Message;
return(null);
}
}
public List <SystemParameterList> GetSystemParametersPagedAndFilteredNew(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, int?pintGroupId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.systemparameter
join B in dbContext.systemparameter on new { a = A.i_ParentParameterId.Value, b = A.i_GroupId }
equals new { a = B.i_ParameterId, b = 147 } into B_join
from B in B_join.DefaultIfEmpty()
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join J3 in dbContext.diseases on new { a = A.v_Value1 }
equals new { a = J3.v_DiseasesId } into J3_join
from J3 in J3_join.DefaultIfEmpty()
where B.i_GroupId == pintGroupId && B.i_IsDeleted == 0
select new SystemParameterList
{
i_GroupId = A.i_GroupId,
i_ParameterId = A.i_ParameterId,
i_ParentGroupId = A.i_GroupId,
i_ParentParameterId = A.i_ParentParameterId.Value,
v_Value1 = A.v_Value1,
v_Value2 = A.v_Value2,
v_DiseasesName = J3.v_Name,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted,
Enfermedad = B.v_Value1,
v_DiseaseName = J3.v_Name,
SI = false,
NO = true,
ND = false,
i_Answer = 0
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
query = query.Take(pintResultsPerPage.Value);
}
List <SystemParameterList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = ex.Message;
return(null);
}
}
public List <BlackListList> GetBlackListPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.blacklistperson
join B in dbContext.person on A.v_PersonId equals B.v_PersonId
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new BlackListList
{
v_BlackListPerson = A.v_BlackListPerson,
v_PersonId = A.v_PersonId,
v_NroDocumento = B.v_DocNumber,
Paciente = B.v_FirstLastName + " " + B.v_SecondLastName + " " + B.v_FirstName,
v_Comment = A.v_Comment,
seleccion = false,
i_Status = A.i_Status.Value,
d_DateRegister = A.d_DateRegister,
d_DateDetection = A.d_DateDetection,
d_DateSolution = A.d_DateSolution,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_DateRegister >= @0 && d_DateRegister <= @1", pdatBeginDate.Value, pdatEndDate.Value);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <BlackListList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <Sigesoft.Node.WinClient.BE.ProtocolList> GetProtocolPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.protocol
join B in dbContext.organization on A.v_EmployerOrganizationId equals B.v_OrganizationId
join C in dbContext.location on A.v_EmployerLocationId equals C.v_LocationId
join D in dbContext.groupoccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId
join E in dbContext.systemparameter on new { a = A.i_EsoTypeId.Value, b = 118 }
equals new { a = E.i_ParameterId, b = E.i_GroupId } into J3_join
from E in J3_join.DefaultIfEmpty()
join F in dbContext.organization on A.v_CustomerOrganizationId equals F.v_OrganizationId
join I in dbContext.location on A.v_CustomerLocationId equals I.v_LocationId
join G in dbContext.organization on A.v_WorkingOrganizationId equals G.v_OrganizationId into J4_join
from G in J4_join.DefaultIfEmpty()
join J in dbContext.location on A.v_WorkingLocationId equals J.v_LocationId into J6_join
from J in J6_join.DefaultIfEmpty()
join H in dbContext.systemparameter on new { a = A.i_MasterServiceId.Value, b = 119 }
equals new { a = H.i_ParameterId, b = H.i_GroupId } into J5_join
from H in J5_join.DefaultIfEmpty()
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new Sigesoft.Node.WinClient.BE.ProtocolList
{
v_ProtocolId = A.v_ProtocolId,
v_Protocol = A.v_Name,
v_Organization = B.v_Name + " / " + C.v_Name,
v_Location = C.v_Name,
v_EsoType = E.v_Value1,
v_GroupOccupation = D.v_Name,
v_OrganizationInvoice = F.v_Name + " / " + I.v_Name,
v_CostCenter = A.v_CostCenter,
v_IntermediaryOrganization = G.v_Name + " / " + J.v_Name,
i_ServiceTypeId = A.i_MasterServiceTypeId.Value,
v_MasterServiceName = H.v_Value1,
i_MasterServiceId = A.i_MasterServiceId.Value,
v_OrganizationId = F.v_OrganizationId + "|" + I.v_LocationId,
i_EsoTypeId = A.i_EsoTypeId,
v_WorkingOrganizationId = G.v_OrganizationId,
v_OrganizationInvoiceId = F.v_OrganizationId,
v_GroupOccupationId = D.v_GroupOccupationId,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
v_LocationId = A.v_EmployerLocationId,
v_CustomerLocationId = A.v_CustomerLocationId,
v_WorkingLocationId = A.v_WorkingLocationId,
i_IsActive = A.i_IsActive
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <Sigesoft.Node.WinClient.BE.ProtocolList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
//Devart.Data.PostgreSql.PgSqlMonitor mon = new Devart.Data.PostgreSql.PgSqlMonitor();
public List <MedicalExamFieldValuesList> GetMedicalExamFieldValuesPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.componentfieldvalues
join B in dbContext.systemparameter on new { a = A.i_OperatorId.Value, b = 129 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
join C in dbContext.systemparameter on new { a = A.i_IsAnormal.Value, b = 111 } equals new { a = C.i_ParameterId, b = C.i_GroupId }
//join D in dbContext.diseases on new { A.v_Diseases equals D.v_DiseasesId }
join D in dbContext.diseases on new { a = A.v_Diseases }
equals new { a = D.v_DiseasesId } into D_join
from D in D_join.DefaultIfEmpty()
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new MedicalExamFieldValuesList
{
v_MedicalExamFieldValuesId = A.v_ComponentFieldValuesId,
v_MedicalExamFieldsId = A.v_ComponentFieldId,
v_AnalyzingValue1 = A.v_AnalyzingValue1,
v_AnalyzingValue2 = A.v_AnalyzingValue2,
i_OperatorId = A.i_OperatorId.Value,
v_OperatorName = B.v_Value1,
v_IsAnormal = C.v_Value1,
v_Cie10Name = D.v_Name + " / " + D.v_CIE10Id,
v_LegalStandard = A.v_LegalStandard,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted.Value
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <MedicalExamFieldValuesList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <OrganizationList> GetOrganizationsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.organization
join B in dbContext.systemparameter on new { a = A.i_OrganizationTypeId.Value, b = 103 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
//join C in dbContext.datahierarchy on new { a = A.i_SectorTypeId.Value, b = 104 } equals new { a = C.i_ItemId, b = C.i_GroupId }
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new OrganizationList
{
b_Seleccionar = false,
v_OrganizationId = A.v_OrganizationId,
i_OrganizationTypeId = (int)A.i_OrganizationTypeId,
v_OrganizationTypeIdName = B.v_Value1,
i_SectorTypeId = (int)A.i_SectorTypeId,
v_EmailContacto = A.v_EmailContacto,
v_IdentificationNumber = A.v_IdentificationNumber,
v_Name = A.v_Name,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted,
v_SectorName = A.v_SectorName,
v_SectorCodigo = A.v_SectorCodigo,
v_ContacName = A.v_ContacName + " / " + A.v_Mail,
v_Contacto = A.v_Contacto + " / " + A.v_EmailContacto,
v_ContactoMedico = A.v_ContactoMedico + " / " + A.v_EmailMedico,
v_Address = A.v_Address,
v_Observation = A.v_Observation,
v_PhoneNumber = A.v_PhoneNumber
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <OrganizationList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public BoardCliente GetClients(BoardCliente data)
{
try
{
string filtValue = data.Name == "" ? null : data.Name;
DatabaseSAMBHSContext cnx = new DatabaseSAMBHSContext();
var query = (from A in cnx.Cliente
join J1 in cnx.SystemUser on new { i_InsertUserId = A.i_InsertaIdUsuario.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in cnx.SystemUser on new { i_UpdateUserId = A.i_ActualizaIdUsuario.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join J3 in cnx.SystemParameter on new { a = A.i_IdTipoIdentificacion.Value, b = 150 }
equals new { a = J3.i_ParameterId, b = J3.i_GroupId } into J3_join
from J3 in J3_join.DefaultIfEmpty()
join J4 in cnx.ClienteDirecciones on new { IdDireccion = A.v_IdCliente, eliminado = 0, predeterminado = 1 }
equals new { IdDireccion = J4.v_IdCliente, eliminado = J4.i_Eliminado.Value, predeterminado = J4.i_EsDireccionPredeterminada.Value } into J4_join
from J4 in J4_join.DefaultIfEmpty()
where A.i_Eliminado == 0 && (A.v_RazonSocial.Contains(filtValue) || A.v_NroDocIdentificacion.Contains(filtValue) || filtValue == null)
select new ClienteCustom
{
NombreRazonSocial = (A.v_ApePaterno + " " + A.v_ApeMaterno + " " + A.v_PrimerNombre + " " + A.v_SegundoNombre + " " + A.v_RazonSocial).Trim(),
v_IdCliente = A.v_IdCliente,
v_ApeMaterno = A.v_ApeMaterno,
v_ApePaterno = A.v_ApePaterno,
v_CodCliente = A.v_CodCliente,
i_IdLista = A.i_IdListaPrecios,
v_NroDocIdentificacion = A.v_NroDocIdentificacion,
v_PrimerNombre = A.v_PrimerNombre,
v_RazonSocial = (A.v_ApePaterno + " " + A.v_ApeMaterno + " " + A.v_PrimerNombre + " " + A.v_SegundoNombre + " " + A.v_RazonSocial).Trim(),
v_SegundoNombre = A.v_SegundoNombre,
i_IdTipoIdentificacion = A.i_IdTipoIdentificacion,
i_IdTipoPersona = A.i_IdTipoPersona,
t_ActualizaFecha = A.t_ActualizaFecha.Value,
t_InsertaFecha = A.t_InsertaFecha.Value,
v_UsuarioCreacion = J1.v_UserName,
v_UsuarioModificacion = J2.v_UserName,
TipoDocumento = J3.v_Value1,
v_FlagPantalla = A.v_FlagPantalla,
v_Direccion = J4 == null ? A.v_DirecPrincipal : J4.v_Direccion,
i_ParameterId = J3.i_ParameterId,
i_IdDireccionCliente = J4 == null ? -1 : J4.i_IdDireccionCliente
}).ToList();
int skip = (data.Index - 1) * data.Take;
var ListClients = query.GroupBy(g => g.v_IdCliente).Select(s => s.First()).ToList();
data.TotalRecords = ListClients.Count;
if (data.Take > 0)
{
ListClients = ListClients.Skip(skip).Take(data.Take).ToList();
}
data.List = ListClients.OrderBy(x => x.v_NroDocIdentificacion).ToList();
return(data);
}
catch (Exception ex)
{
return(null);
}
}
public static ProtocolList GetProtocolById(string pstrProtocolId)
{
try
{
DatabaseContext dbContext = new DatabaseContext();
var objProtocol = (from A in dbContext.Protocol
join B in dbContext.Organization on A.v_EmployerOrganizationId equals B.v_OrganizationId
join C in dbContext.Location on A.v_EmployerLocationId equals C.v_LocationId
join D in dbContext.GroupOccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId
join E in dbContext.SystemParameter on new { a = A.i_EsoTypeId.Value, b = 118 } equals new { a = E.i_ParameterId, b = E.i_GroupId }
join F in dbContext.Organization on A.v_CustomerOrganizationId equals F.v_OrganizationId
join H in dbContext.SystemParameter on new { a = A.i_MasterServiceId.Value, b = 119 } equals new { a = H.i_ParameterId, b = H.i_GroupId }
join I in dbContext.Location on A.v_CustomerLocationId equals I.v_LocationId
join J in dbContext.Location on A.v_EmployerLocationId equals J.v_LocationId
join J1 in dbContext.SystemUser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.SystemUser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join J3 in dbContext.Organization on new { a = A.v_WorkingOrganizationId }
equals new { a = J3.v_OrganizationId } into J3_join
from J3 in J3_join.DefaultIfEmpty()
join J4 in dbContext.Area on new { a = C.v_LocationId }
equals new { a = J4.v_LocationId } into J4_join
from J4 in J4_join.DefaultIfEmpty()
join J5 in dbContext.Ges on new { a = J4.v_AreaId }
equals new { a = J5.v_AreaId } into J5_join
from J5 in J5_join.DefaultIfEmpty()
join J6 in dbContext.Occupation on new { a = J5.v_GesId, b = D.v_GroupOccupationId }
equals new { a = J6.v_GesId, b = J6.v_GroupOccupationId } into J6_join
from J6 in J6_join.DefaultIfEmpty()
//join J7 in dbContext.groupoccupation on new { a = J4.v_AreaId }
// equals new { a = J7.v_AreaId } into J7_join
//from J5 in J5_join.DefaultIfEmpty()
where (A.v_ProtocolId == pstrProtocolId) && (A.i_IsDeleted == 0)
select new ProtocolList
{
v_Name = A.v_Name,
v_ProtocolId = A.v_ProtocolId, //id Protocolo
v_Protocol = A.v_Name, // monbre protocolo
v_Organization = B.v_Name + " / " + J.v_Name, // nombre organizacion
v_Location = C.v_Name, // nombre de sede
v_EsoType = E.v_Value1, // Esoa, Esor
v_OrganizationInvoice = F.v_Name, // empresa que factura
v_CostCenter = A.v_CostCenter, // centro de costo
v_IntermediaryOrganization = J3.v_Name + " / " + I.v_Name, // empresa intermediaria
v_MasterServiceName = H.v_Value1, // Eso o no Eo
v_OrganizationId = B.v_OrganizationId,
i_EsoTypeId = E.i_ParameterId, // Id de (Esoa, Esor, Espo)
v_WorkingOrganizationId = J3.v_OrganizationId,
v_OrganizationInvoiceId = F.v_OrganizationId,
v_GroupOccupationId = D.v_GroupOccupationId,
v_Ges = J5.v_Name,
v_GroupOccupation = D.v_Name, // nombre GESO
v_Occupation = J6.v_Name,
i_ServiceTypeId = A.i_MasterServiceTypeId.Value,
i_MasterServiceId = A.i_MasterServiceId.Value,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
v_ContacName = F.v_ContacName,
v_Address = F.v_Address,
v_CustomerOrganizationId = A.v_CustomerOrganizationId
}).FirstOrDefault();
ProtocolList objData = objProtocol;
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public static BoardProtocol GetAllProtocols(BoardProtocol data)
{
try
{
string WorkingOrganization = data.EmpresaTrabajo == "-1" ? "-1" : data.EmpresaTrabajo.Split('|')[0];
string EmployerOrganization = data.EmpresaEmpleadora == "-1" ? "-1" : data.EmpresaEmpleadora.Split('|')[0];
string CustomerOrganization = data.EmpresaCliente == "-1" ? "-1" : data.EmpresaCliente.Split('|')[0];
DatabaseContext dbContext = new DatabaseContext();
int skip = (data.Index - 1) * data.Take;
var query = from A in dbContext.Protocol
join B in dbContext.Organization on A.v_EmployerOrganizationId equals B.v_OrganizationId
join C in dbContext.Location on A.v_EmployerLocationId equals C.v_LocationId
join D in dbContext.GroupOccupation on A.v_GroupOccupationId equals D.v_GroupOccupationId
join E in dbContext.SystemParameter on new { a = A.i_EsoTypeId.Value, b = 118 }
equals new { a = E.i_ParameterId, b = E.i_GroupId } into J3_join
from E in J3_join.DefaultIfEmpty()
join F in dbContext.Organization on A.v_CustomerOrganizationId equals F.v_OrganizationId
join I in dbContext.Location on A.v_CustomerLocationId equals I.v_LocationId
join G in dbContext.Organization on A.v_WorkingOrganizationId equals G.v_OrganizationId into J4_join
from G in J4_join.DefaultIfEmpty()
join J in dbContext.Location on A.v_WorkingLocationId equals J.v_LocationId into J6_join
from J in J6_join.DefaultIfEmpty()
join H in dbContext.SystemParameter on new { a = A.i_MasterServiceId.Value, b = 119 }
equals new { a = H.i_ParameterId, b = H.i_GroupId } into J5_join
from H in J5_join.DefaultIfEmpty()
join J1 in dbContext.SystemUser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.SystemUser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join L in dbContext.ProtocolComponent on A.v_ProtocolId equals L.v_ProtocolId
join K in dbContext.Component on L.v_ComponentId equals K.v_ComponentId
where A.i_IsDeleted == 0 &&
(A.v_Name.Contains(data.ProtocolName) || data.ProtocolName == null) &&
(A.i_MasterServiceId == data.MasterService || data.MasterService == -1) && (B.v_OrganizationId == EmployerOrganization || EmployerOrganization == "-1") &&
(F.v_OrganizationId == CustomerOrganization || CustomerOrganization == "-1") && (G.v_OrganizationId == WorkingOrganization || WorkingOrganization == "-1") &&
(A.i_MasterServiceTypeId == data.MasterServiceType || data.MasterServiceType == -1) && (D.v_GroupOccupationId == data.GESO || data.GESO == "-1") && (A.i_EsoTypeId == data.EsoType || data.EsoType == -1) &&
(K.v_Name.Contains(data.ComponentName) || data.ComponentName == null) && A.i_IsActive == data.IsActive
select new ProtocolList
{
v_ProtocolId = A.v_ProtocolId,
v_Protocol = A.v_Name,
v_Organization = B.v_Name + " / " + C.v_Name,
v_Location = C.v_Name,
v_EsoType = E.v_Value1,
v_GroupOccupation = D.v_Name,
v_OrganizationInvoice = F.v_Name + " / " + I.v_Name,
v_CostCenter = A.v_CostCenter,
v_IntermediaryOrganization = G.v_Name + " / " + J.v_Name,
i_ServiceTypeId = A.i_MasterServiceTypeId.Value,
v_MasterServiceName = H.v_Value1,
i_MasterServiceId = A.i_MasterServiceId.Value,
v_OrganizationId = B.v_OrganizationId + "|" + C.v_LocationId,
i_EsoTypeId = A.i_EsoTypeId,
v_WorkingOrganizationId = G.v_OrganizationId + "|" + J.v_LocationId,
v_OrganizationInvoiceId = F.v_OrganizationId + "|" + I.v_LocationId,
v_GroupOccupationId = D.v_GroupOccupationId,
v_Geso = D.v_Name,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
v_LocationId = A.v_EmployerLocationId,
v_CustomerLocationId = A.v_CustomerLocationId,
v_WorkingLocationId = A.v_WorkingLocationId,
i_IsActive = A.i_IsActive,
v_ComponenteNombre = K.v_Name,
r_PriceFactor = A.r_PriceFactor,
r_HospitalBedPrice = A.r_HospitalBedPrice,
r_DiscountExam = A.r_DiscountExam,
r_MedicineDiscount = A.r_MedicineDiscount,
AseguradoraId = A.v_AseguradoraOrganizationId
};
var list = query.ToList();
list = list.GroupBy(x => x.v_ProtocolId).Select(z => z.First()).OrderByDescending(x => x.v_ProtocolId).ToList();
int totalRecords = list.Count;
if (data.Take > 0)
{
list = list.Skip(skip).Take(data.Take).ToList();
}
data.List = list;
data.TotalRecords = totalRecords;
return(data);
}
catch (Exception ex)
{
return(null);
}
}
public List <ServiceOrderList> GetServiceOrderPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.serviceorder
join B in dbContext.systemparameter on new { a = A.i_ServiceOrderStatusId.Value, b = 194 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
//join C in dbContext.serviceorderdetail on A.v_ServiceOrderId equals C.v_ServiceOrderId
//join E in dbContext.protocol on C.v_ProtocolId equals E.v_ProtocolId
// Empresa / Sede Cliente ******************************************************
//join oc in dbContext.organization on new { a = E.v_CustomerOrganizationId }
// equals new { a = oc.v_OrganizationId } into oc_join
//from oc in oc_join.DefaultIfEmpty()
//join lc in dbContext.location on new { a = E.v_CustomerOrganizationId, b = E.v_CustomerLocationId }
// equals new { a = lc.v_OrganizationId, b = lc.v_LocationId } into lc_join
//from lc in lc_join.DefaultIfEmpty()
//// Empresa / Sede Empleadora *******************************************
//join oe in dbContext.organization on new { a = E.v_EmployerOrganizationId }
// equals new { a = oe.v_OrganizationId } into oe_join
//from oe in oe_join.DefaultIfEmpty()
//join le in dbContext.location on new { a = E.v_EmployerOrganizationId, b = E.v_EmployerLocationId }
// equals new { a = le.v_OrganizationId, b = le.v_LocationId } into le_join
//from le in le_join.DefaultIfEmpty()
//// Empresa / Sede Trabajo *******************************************
//join ow in dbContext.organization on new { a = E.v_WorkingOrganizationId }
// equals new { a = ow.v_OrganizationId } into ow_join
//from ow in ow_join.DefaultIfEmpty()
//join lw in dbContext.location on new { a = E.v_WorkingOrganizationId, b = E.v_WorkingLocationId }
// equals new { a = lw.v_OrganizationId, b = lw.v_LocationId } into lw_join
//from lw in lw_join.DefaultIfEmpty()
//// *****************************************************************************
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new ServiceOrderList
{
v_ServiceOrderId = A.v_ServiceOrderId,
v_CustomServiceOrderId = A.v_CustomServiceOrderId,
v_Description = A.v_Description,
v_Comentary = A.v_Comentary,
i_NumberOfWorker = A.i_NumberOfWorker,
r_TotalCost = A.r_TotalCost,
d_DeliveryDate = A.d_DeliveryDate,
i_ServiceOrderStatusId = A.i_ServiceOrderStatusId,
v_ServiceOrderStatusName = B.v_Value1,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
//v_ProtocolId = C.v_ProtocolId,
//v_CustomerOrganizationId = E.v_CustomerOrganizationId,
//v_CustomerLocationId = E.v_CustomerLocationId,
//v_EmployerOrganizationId = E.v_EmployerOrganizationId,
//v_EmployerLocationId = E.v_EmployerLocationId,
//v_WorkingOrganizationId = E.v_WorkingOrganizationId,
//v_WorkingLocationId = E.v_WorkingLocationId
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_DeliveryDate >= @0 && d_DeliveryDate <= @1 || d_DeliveryDate == NULL", pdatBeginDate.Value, pdatEndDate.Value);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <ServiceOrderList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <ServiceOrderList> GetReportServiceOrder(string pstServiceOrderId, string pstrProtocolId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var LogoEmpresa = GetLogoMedicalCenter();
var query = from A in dbContext.serviceorder
join A1 in dbContext.serviceorderdetail on new { a = pstServiceOrderId, b = pstrProtocolId } equals new { a = A1.v_ServiceOrderId, b = A1.v_ProtocolId }
//join A1 in dbContext.serviceorderdetail on A.v_ServiceOrderId equals A1.v_ServiceOrderId
join B in dbContext.systemparameter on new { a = A.i_ServiceOrderStatusId.Value, b = 194 } equals new { a = B.i_ParameterId, b = B.i_GroupId }
join C in dbContext.serviceorderdetail on A.v_ServiceOrderId equals C.v_ServiceOrderId
join D in dbContext.protocol on C.v_ProtocolId equals D.v_ProtocolId
join E in dbContext.organization on D.v_CustomerOrganizationId equals E.v_OrganizationId
join F in dbContext.systemparameter on new { a = D.i_EsoTypeId.Value, b = 118 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
join G in dbContext.protocolcomponent on D.v_ProtocolId equals G.v_ProtocolId
join H in dbContext.component on G.v_ComponentId equals H.v_ComponentId
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_ServiceOrderId == pstServiceOrderId && G.i_IsDeleted == 0 &&
D.v_ProtocolId == pstrProtocolId
select new ServiceOrderList
{
v_ServiceOrderId = A.v_ServiceOrderId,
v_CustomServiceOrderId = A.v_CustomServiceOrderId,
v_Description = A.v_Description,
v_Comentary = A.v_Comentary,
i_NumberOfWorker = A1.i_NumberOfWorkerProtocol,
r_TotalCost = A1.r_Total,
d_DeliveryDate = A.d_DeliveryDate,
RucCliente = E.v_IdentificationNumber,
i_ServiceOrderStatusId = A.i_ServiceOrderStatusId,
v_ServiceOrderStatusName = B.v_Value1,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
v_ProtocolId = C.v_ProtocolId,
v_Protocol = D.v_Name,
v_Organization = E.v_Name,
v_ContacName = E.v_ContacName,
v_Address = E.v_Address,
v_EsoType = F.v_Value1,
v_ComponentName = H.v_Name,
r_Price = G.r_Price, // H.r_BasePrice.Value,
d_InsertDate = A.d_InsertDate.Value,
Logo = LogoEmpresa
};
List <ServiceOrderList> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public List <MedicalExamList> GetMedicalExamPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.component
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
join B in dbContext.systemparameter on new { a = A.i_CategoryId.Value, b = 116 } // CATEGORIA DEL EXAMEN
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join E in dbContext.systemparameter on new { a = A.i_DiagnosableId.Value, b = 111 } equals new { a = E.i_ParameterId, b = E.i_GroupId }
join F in dbContext.systemparameter on new { a = A.i_ComponentTypeId.Value, b = 126 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
where A.i_IsDeleted == 0
select new MedicalExamList
{
v_MedicalExamId = A.v_ComponentId,
v_Name = A.v_Name,
i_CategoryId = A.i_CategoryId.Value,
v_CategoryName = B.v_Value1,
v_DiagnosableName = E.v_Value1,
v_ComponentTypeName = F.v_Value1,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted.Value,
r_BasePrice = A.r_BasePrice,
i_ComponentTypeId = A.i_ComponentTypeId,
i_UIIndex = A.i_UIIndex.Value,
v_IdUnidadProductiva = A.v_IdUnidadProductiva
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <MedicalExamList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <MedicalExamFieldsList> GetMedicalExamFieldsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.componentfields
join B in dbContext.componentfield on A.v_ComponentFieldId equals B.v_ComponentFieldId
join C in dbContext.systemparameter on new { a = B.i_IsRequired.Value, b = 111 } equals new { a = C.i_ParameterId, b = C.i_GroupId }
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new MedicalExamFieldsList
{
v_MedicalExamFieldId = B.v_ComponentFieldId,
v_ComponentId = A.v_ComponentId,
v_TextLabel = B.v_TextLabel,
i_LabelWidth = B.i_LabelWidth.Value,
v_DefaultText = B.v_DefaultText,
i_ControlId = B.i_ControlId.Value,
i_GroupId = B.i_GroupId.Value,
i_ItemId = B.i_ItemId.Value,
i_ControlWidth = B.i_WidthControl.Value,
i_HeightControl = B.i_HeightControl.Value,
i_MaxLenght = B.i_MaxLenght.Value,
i_IsRequired = B.i_IsRequired.Value,
v_IsRequired = C.v_Value1,
i_IsCalculate = B.i_IsCalculate.Value,
i_Order = B.i_Order.Value,
i_MeasurementUnitId = B.i_MeasurementUnitId.Value,
r_ValidateValue1 = B.r_ValidateValue1.Value,
r_ValidateValue2 = B.r_ValidateValue2.Value,
v_Group = A.v_Group,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted.Value,
v_ComponentFieldId = B.v_ComponentFieldId
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
{
int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
query = query.Skip(intStartRowIndex);
}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <MedicalExamFieldsList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <CalendarList> GetCalendarsPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, DateTime?pdatBeginDate, DateTime?pdatEndDate)
{
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.calendar
join B in dbContext.person on A.v_PersonId equals B.v_PersonId
join C in dbContext.systemparameter on new { a = A.i_LineStatusId.Value, b = 120 } equals new { a = C.i_ParameterId, b = C.i_GroupId }
join D in dbContext.service on A.v_ServiceId equals D.v_ServiceId
join E in dbContext.systemparameter on new { a = A.i_ServiceTypeId.Value, b = 119 } equals new { a = E.i_ParameterId, b = E.i_GroupId }
join F in dbContext.systemparameter on new { a = A.i_ServiceId.Value, b = 119 } equals new { a = F.i_ParameterId, b = F.i_GroupId }
join G in dbContext.systemparameter on new { a = A.i_NewContinuationId.Value, b = 121 } equals new { a = G.i_ParameterId, b = G.i_GroupId }
join H in dbContext.systemparameter on new { a = A.i_CalendarStatusId.Value, b = 122 } equals new { a = H.i_ParameterId, b = H.i_GroupId }
join I in dbContext.systemparameter on new { a = A.i_IsVipId.Value, b = 111 } equals new { a = I.i_ParameterId, b = I.i_GroupId }
join J in dbContext.protocol on new { a = D.v_ProtocolId }
equals new { a = J.v_ProtocolId } into J_join
from J in J_join.DefaultIfEmpty()
join K in dbContext.systemparameter on new { a = J.i_EsoTypeId.Value, b = 118 }
equals new { a = K.i_ParameterId, b = K.i_GroupId } into K_join
from K in K_join.DefaultIfEmpty()
// Empresa / Sede Cliente **************
join oc in dbContext.organization on new { a = J.v_CustomerOrganizationId }
equals new { a = oc.v_OrganizationId } into oc_join
from oc in oc_join.DefaultIfEmpty()
join lc in dbContext.location on new { a = J.v_CustomerOrganizationId, b = J.v_CustomerLocationId }
equals new { a = lc.v_OrganizationId, b = lc.v_LocationId } into lc_join
from lc in lc_join.DefaultIfEmpty()
// Empresa / Sede Trabajo ********************************************************
join ow in dbContext.organization on new { a = J.v_WorkingOrganizationId }
equals new { a = ow.v_OrganizationId } into ow_join
from ow in ow_join.DefaultIfEmpty()
join lw in dbContext.location on new { a = J.v_WorkingOrganizationId, b = J.v_WorkingLocationId }
equals new { a = lw.v_OrganizationId, b = lw.v_LocationId } into lw_join
from lw in lw_join.DefaultIfEmpty()
//************************************************************************************
join N in dbContext.organization on new { a = D.v_OrganizationId }
equals new { a = N.v_OrganizationId } into N_join
from N in N_join.DefaultIfEmpty()
join O in dbContext.location on new { a = N.v_OrganizationId, b = D.v_LocationId }
equals new { a = O.v_OrganizationId, b = O.v_LocationId } into O_join
from O in O_join.DefaultIfEmpty()
join J3 in dbContext.systemparameter on new { a = D.i_ServiceStatusId.Value, b = 125 }
equals new { a = J3.i_ParameterId, b = J3.i_GroupId } into J3_join
from J3 in J3_join.DefaultIfEmpty()
join J4 in dbContext.systemparameter on new { a = D.i_AptitudeStatusId.Value, b = 124 }
equals new { a = J4.i_ParameterId, b = J4.i_GroupId } into J4_join
from J4 in J4_join.DefaultIfEmpty()
join J5 in dbContext.datahierarchy on new { a = B.i_DocTypeId.Value, b = 106 }
equals new { a = J5.i_ItemId, b = J5.i_GroupId } into J5_join
from J5 in J5_join.DefaultIfEmpty()
join J1 in dbContext.systemuser on new { i_InsertUserId = A.i_InsertUserId.Value }
equals new { i_InsertUserId = J1.i_SystemUserId } into J1_join
from J1 in J1_join.DefaultIfEmpty()
join J2 in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = J2.i_SystemUserId } into J2_join
from J2 in J2_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new CalendarList
{
v_CalendarId = A.v_CalendarId,
d_DateTimeCalendar = A.d_DateTimeCalendar.Value,
v_Pacient = B.v_FirstLastName + " " + B.v_SecondLastName + " " + B.v_FirstName,
v_NumberDocument = B.v_DocNumber,
v_LineStatusName = C.v_Value1,
v_ServiceId = A.v_ServiceId,
v_ProtocolId = A.v_ProtocolId,
v_ProtocolName = J.v_Name,
v_ServiceStatusName = J3.v_Value1,
v_AptitudeStatusName = J4.v_Value1,
v_ServiceTypeName = E.v_Value1,
v_ServiceName = F.v_Value1,
v_NewContinuationName = G.v_Value1,
v_PersonId = A.v_PersonId,
v_CalendarStatusName = H.v_Value1,
i_ServiceStatusId = D.i_ServiceStatusId.Value,
v_IsVipName = I.v_Value1,
i_ServiceId = A.i_ServiceId.Value,
i_ServiceTypeId = A.i_ServiceTypeId.Value,
i_CalendarStatusId = A.i_CalendarStatusId.Value,
i_MasterServiceId = A.i_ServiceId.Value,
i_NewContinuationId = A.i_NewContinuationId.Value,
i_LineStatusId = A.i_LineStatusId.Value,
i_IsVipId = A.i_IsVipId.Value,
i_EsoTypeId = J.i_EsoTypeId.Value,
v_EsoTypeName = K.v_Value1,
v_OrganizationLocationProtocol = oc.v_Name + " / " + lc.v_Name,
v_OrganizationLocationService = N.v_Name + " / " + O.v_Name,
v_CreationUser = J1.v_UserName,
v_UpdateUser = J2.v_UserName,
d_CreationDate = A.d_InsertDate,
d_UpdateDate = A.d_UpdateDate,
i_IsDeleted = A.i_IsDeleted,
v_CustomerOrganizationId = oc.v_OrganizationId,
v_CustomerLocationId = lc.v_LocationId,
v_DocTypeName = J5.v_Value1,
v_DocNumber = B.v_DocNumber,
i_DocTypeId = B.i_DocTypeId.Value,
d_EntryTimeCM = A.d_EntryTimeCM.Value,
v_WorkingOrganizationName = ow.v_Name
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_DateTimeCalendar >= @0 && d_DateTimeCalendar <= @1", pdatBeginDate.Value, pdatEndDate.Value);
}
if (!string.IsNullOrEmpty(pstrSortExpression))
{
query = query.OrderBy(pstrSortExpression);
}
//if (pintPageIndex.HasValue && pintResultsPerPage.HasValue)
//{
// int intStartRowIndex = pintPageIndex.Value * pintResultsPerPage.Value;
// query = query.Skip(intStartRowIndex);
//}
if (pintResultsPerPage.HasValue)
{
query = query.Take(pintResultsPerPage.Value);
}
List <CalendarList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <Sigesoft.Node.WinClient.BE.ProtocolComponentList> GetProtocolComponents(ref OperationResult pobjOperationResult, string pstrProtocolId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var objEntity = (from a in dbContext.protocolcomponent
join b in dbContext.component on a.v_ComponentId equals b.v_ComponentId
join fff in dbContext.systemparameter on new { a = b.i_CategoryId.Value, b = 116 } // CATEGORIA DEL EXAMEN
equals new { a = fff.i_ParameterId, b = fff.i_GroupId } into J5_join
from fff in J5_join.DefaultIfEmpty()
join E in dbContext.systemparameter on new { a = a.i_OperatorId.Value, b = 117 }
equals new { a = E.i_ParameterId, b = E.i_GroupId } into J1_join
from E in J1_join.DefaultIfEmpty()
join H in dbContext.systemparameter on new { a = a.i_GenderId.Value, b = 130 } // Genero condicional
equals new { a = H.i_ParameterId, b = H.i_GroupId } into J2_join
from H in J2_join.DefaultIfEmpty()
join I in dbContext.systemparameter on new { a = b.i_ComponentTypeId.Value, b = 126 } // Tipo componente
equals new { a = I.i_ParameterId, b = I.i_GroupId } into J3_join
from I in J3_join.DefaultIfEmpty()
where a.v_ProtocolId == pstrProtocolId &&
a.i_IsDeleted == 0
select new Sigesoft.Node.WinClient.BE.ProtocolComponentList
{
v_ComponentId = a.v_ComponentId,
v_ComponentName = b.v_Name,
//v_ServiceComponentStatusName = K.v_Value1,
v_ProtocolComponentId = a.v_ProtocolComponentId,
r_Price = a.r_Price,
v_Operator = E.v_Value1,
i_Age = a.i_Age,
v_Gender = H.v_Value1,
i_IsConditionalIMC = a.i_IsConditionalIMC,
r_Imc = a.r_Imc,
v_IsConditional = a.i_IsConditionalId == 1 ? "Si" : "No",
i_isAdditional = a.i_IsAdditional,
v_ComponentTypeName = I.v_Value1,
i_RecordStatus = (int)RecordStatus.Grabado,
i_RecordType = (int)RecordType.NoTemporal,
i_GenderId = a.i_GenderId,
i_IsConditionalId = a.i_IsConditionalId,
i_OperatorId = a.i_OperatorId,
i_IsDeleted = a.i_IsDeleted,
d_CreationDate = a.d_InsertDate,
v_CategoryName = fff.v_Value1,
i_CategoryId = b.i_CategoryId
}).ToList();
pobjOperationResult.Success = 1;
return(objEntity);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}