public List <ProblemaList> GetProblemaPagedAndFiltered(string pstrPersonId)
{
//mon.IsActive = true;
try
{
DatabaseContext dbContext = new DatabaseContext();
var query = from A in dbContext.Problema
join B in dbContext.SystemParameter on new { a = A.i_EsControlado.Value, b = 111 } // CATEGORIA DEL EXAMEN
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new ProblemaList
{
v_ProblemaId = A.v_ProblemaId,
i_Tipo = A.i_Tipo,
v_PersonId = A.v_PersonId,
d_Fecha = A.d_Fecha.Value,
v_Descripcion = A.v_Descripcion,
i_EsControlado = A.i_EsControlado,
v_Observacion = A.v_Observacion,
v_EsControlado = B.v_Value1
};
List <ProblemaList> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public List <ProblemasList> GetAtencionIntegral(string personId)
{
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.problema
join B in dbContext.systemparameter on new { a = A.i_EsControlado.Value, b = 111 } equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.v_PersonId == personId && A.i_IsDeleted == 0
select new ProblemasList
{
v_PersonId = A.v_PersonId,
d_Fecha = A.d_Fecha,
i_Tipo = A.i_Tipo,
v_Descripcion = A.v_Descripcion,
i_EsControlado = A.i_EsControlado,
v_EsControlao = B.v_Value1,
v_Observacion = A.v_Observacion
};
List <ProblemasList> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public void sr_llenar_resultados(ref DataGridView dgr, int IDS, DateTime dia)
{
animalitos db = new animalitos(CN.CONEC);
var query = (from A in db.dbresultados
join B in db.dbSorteos on new { ID_LOTERIA = Convert.ToInt32(A.ID_LOTERIA) } equals new { ID_LOTERIA = B.ID_SORTEO } into B_join
from B in B_join.DefaultIfEmpty()
join C in db.dbhorarios
on new { A.ID_LOTERIA, ID_SORTEOHR = Convert.ToInt32(A.ID_SORTEOHR) }
equals new { ID_LOTERIA = C.IDSORTEOHR, ID_SORTEOHR = C.IDHORA } into C_join
from C in C_join.DefaultIfEmpty()
join D in db.dbanimalitos on new { ID_ANIMAL = Convert.ToInt32(A.ID_ANIMAL) } equals new { ID_ANIMAL = D.IDANIMALITOS } into D_join
from D in D_join.DefaultIfEmpty()
join E in db.dbestatus on new { ESTATUS = A.ESTATUS } equals new { ESTATUS = E.CODESTATUS } into E_join
from E in E_join.DefaultIfEmpty()
where
A.FECHA == dia &&
A.ID_LOTERIA == IDS
select new
{
A.ID_RESULTADO,
NOMBRE_SORTEO = B.NOMBRE_SORTEO,
HORA = C.HORA,
NOMBRE_ANIMALITO = D.NOMBRE_ANIMALITO,
NOMESTATUS = E.NOMESTATUS,
A.FECHA,
Column1 = A.HORA
});
dgr.DataSource = query;
}
public List <PlanIntegralList> GetPlanIntegralAndFiltered(string pstrPersonId)
{
//mon.IsActive = true;
try
{
DatabaseContext dbContext = new DatabaseContext();
var query = from A in dbContext.PlanIntegral
join B in dbContext.SystemParameter on new { a = A.i_TipoId.Value, b = 281 } // CATEGORIA DEL EXAMEN
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new PlanIntegralList
{
v_PlanIntegral = A.v_PlanIntegral,
v_PersonId = A.v_PersonId,
i_TipoId = A.i_TipoId.Value,
v_Descripcion = A.v_Descripcion,
d_Fecha = A.d_Fecha.Value,
v_Lugar = A.v_Lugar,
v_Tipo = B.v_Value1
};
List <PlanIntegralList> objData = query.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public List <KeyValueDTO> GetAllComponents()
{
//mon.IsActive = true;
try
{
var DataComponentList = (from a in ctx.Component
join B in ctx.SystemParameter on new { a = a.i_CategoryId, b = 116 } equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where a.i_IsDeleted == 0 &&
a.i_ComponentTypeId == 1
select new KeyValueDTO
{
Value4 = a.i_CategoryId, //i_CategoryId
Value = a.i_CategoryId == -1 ? a.v_Name : B.v_Value1, //CategoryName
Value2 = a.v_ComponentId, // ComponentId
Value3 = a.v_Name, // v_Name
//Id = a.v_ComponentId
}).ToList();
List <KeyValueDTO> objData = DataComponentList.ToList();
return(objData);
}
catch (Exception ex)
{
return(null);
}
}
public List <Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList> GetFamilyMedicalAntecedentsReport(string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.familymedicalantecedents
join B in dbContext.systemparameter on new { a = A.i_TypeFamilyId.Value, b = 149 }
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemparameter on new { a = B.i_ParentParameterId.Value, b = 149 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.diseases on new { a = A.v_DiseasesId }
equals new { a = D.v_DiseasesId } into D_join
from D in D_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
{
v_FamilyMedicalAntecedentsId = A.v_FamilyMedicalAntecedentsId,
v_PersonId = A.v_PersonId,
v_DiseasesId = A.v_DiseasesId,
v_DiseaseName = D.v_Name,
//i_TypeFamilyId = A.i_TypeFamilyId.Value,
i_TypeFamilyId = C.i_ParameterId,
v_TypeFamilyName = C.v_Value1,
v_Comment = A.v_Comment,
v_FullAntecedentName = D.v_Name + " / " + C.v_Value1 + ", " + A.v_Comment,
DxAndComment = D.v_Name + "," + A.v_Comment
}).ToList();
// add the sequence number on the fly
var query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.FamilyMedicalAntecedentsList
{
i_Item = index + 1,
v_FamilyMedicalAntecedentsId = x.v_FamilyMedicalAntecedentsId,
v_PersonId = x.v_PersonId,
v_DiseasesId = x.v_DiseasesId,
v_DiseaseName = x.v_DiseaseName,
i_TypeFamilyId = x.i_TypeFamilyId,
v_TypeFamilyName = x.v_TypeFamilyName,
v_Comment = x.v_Comment,
v_FullAntecedentName = x.v_FullAntecedentName,
DxAndComment = x.DxAndComment
}).ToList();
return(query1);
}
catch (Exception ex)
{
return(null);
}
}
public List <ProblemaList> GetProblemaPagedAndFiltered(ref OperationResult pobjOperationResult, int?pintPageIndex, int?pintResultsPerPage, string pstrSortExpression, string pstrFilterExpression, string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = from A in dbContext.problema
join B in dbContext.systemparameter on new { a = A.i_EsControlado.Value, b = 111 } // CATEGORIA DEL EXAMEN
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new ProblemaList
{
v_ProblemaId = A.v_ProblemaId,
i_Tipo = A.i_Tipo,
v_PersonId = A.v_PersonId,
d_Fecha = A.d_Fecha.Value,
v_Descripcion = A.v_Descripcion,
i_EsControlado = A.i_EsControlado,
v_Observacion = A.v_Observacion,
v_EsControlado = B.v_Value1
};
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 <ProblemaList> 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.NoxiousHabitsList> GetNoxiousHabitsReport(string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.noxioushabits
join B in dbContext.systemparameter on new { a = A.i_TypeHabitsId.Value, b = 148 }
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == pstrPersonId
select new Sigesoft.Node.WinClient.BE.NoxiousHabitsList
{
v_NoxiousHabitsId = A.v_NoxiousHabitsId,
v_NoxiousHabitsName = B.v_Value1,
v_PersonId = A.v_PersonId,
v_Frequency = A.v_Frequency + ", " + A.v_Comment,
v_Comment = A.v_Comment,
i_TypeHabitsId = B.i_ParameterId,
v_TypeHabitsName = B.v_Value1,
i_RecordStatus = 0, // grabado
i_RecordType = 2, // no temporal
v_DescriptionQuantity = A.v_DescriptionQuantity,
v_DescriptionHabit = A.v_DescriptionHabit,
v_FrecuenciaHabito = A.v_Frequency
}).ToList();
// add the sequence number on the fly
var query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.NoxiousHabitsList
{
i_Item = index + 1,
v_NoxiousHabitsId = x.v_NoxiousHabitsId,
v_NoxiousHabitsName = x.v_NoxiousHabitsName,
v_PersonId = x.v_PersonId,
v_Frequency = x.v_Frequency,
v_Comment = x.v_Comment,
i_TypeHabitsId = x.i_TypeHabitsId,
v_TypeHabitsName = x.v_TypeHabitsName,
v_DescriptionQuantity = x.v_DescriptionQuantity,
v_DescriptionHabit = x.v_DescriptionHabit,
v_FrecuenciaHabito = x.v_FrecuenciaHabito
}).ToList();
return(query1);
}
catch (Exception ex)
{
return(null);
}
}
public List <HospitalizacionHabitacionList> GetHospitalizacionHabitacionPagedAndFiltered(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.hospitalizacionhabitacion
join B in dbContext.systemparameter on new { a = A.i_HabitacionId.Value, b = 309 } equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0
select new HospitalizacionHabitacionList
{
v_HospitalizacionHabitacionId = A.v_HospitalizacionHabitacionId,
v_HopitalizacionId = A.v_HopitalizacionId,
i_HabitacionId = A.i_HabitacionId.Value,
d_StartDate = A.d_StartDate.Value,
d_EndDate = A.d_EndDate.Value,
NroHabitacion = B.v_Value1
};
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 <HospitalizacionHabitacionList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
return(null);
}
}
public List <DtoBvsAllotment> GetAllotedServices(int PersonId, int ItemId = 0)
{
//var alloted =
return((from bs in _db.BvsServices
join B in (
(from bas in _db.BvsAllotServices
join bvs in _db.BvsAllots on bas.BVSAllotId equals bvs.BVSAllotId
join p in _db.Purchases on bvs.ItemId equals p.PurchaseId
join i in _db.Items on p.ItemId equals i.ItemId
where
bvs.PersonId == PersonId
select new
{
bas.BvsServiceId,
bas.BVSAllotServiceId,
ItemId = p.ItemId,
ItemName = i.Name
}))on new { BvsServiceId = bs.BvsServiceId } equals new { BvsServiceId = (int)B.BvsServiceId }
into B_join
from B in B_join.DefaultIfEmpty()
select new DtoBvsAllotment
{
ItemId = (int?)B.ItemId ?? 0,
ItemName = B.ItemName ?? "",
AllotmentServiceses = new List <DtoBvsAllotmentServices>
{
new DtoBvsAllotmentServices
{
BVSAllotServiceId = (int?)B.BVSAllotServiceId ?? 0,
BvsServiceId = bs.BvsServiceId,
BvsServiceName = bs.Name,
Active =
B.BVSAllotServiceId == null ? false : true
}
}
}
//select new DtoBvsAllotmentServices
//{
// BVSAllotServiceId = (int?)B.BVSAllotServiceId,
// BvsServiceName = bs.Name,
// Active =
// B.BVSAllotServiceId == null ? false : true
//}
).ToList());
//DtoBvsAllotment allotment = new DtoBvsAllotment();
//foreach (DtoBvsAllotment bvs in alloted)
//{
// allotment.ItemId =
//}
}
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 List <DtvAppHierarchy> GetSystemParameterForCombo(ref OperationResult pobjOperationResult)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
List <DtvAppHierarchy> DataHierarchyList;
List <DtvAppHierarchy> DataHierarchyListForBinding = new List <DtvAppHierarchy>();
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 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_IsDeleted == 0 && B.i_GroupId == 147
select new DtvAppHierarchy
{
Id = A.i_ParameterId,
Description = J3.v_Name,
ParentId = B.i_ParentParameterId.Value,
EnabledSelect = true,
i_GroupId = A.i_GroupId
};
query = query.Where("i_GroupId== 147");
DataHierarchyList = query.ToList();
// Iterar y ordenar la data
ProcessData(DataHierarchyList, -1, DataHierarchyListForBinding, 0);
pobjOperationResult.Success = 1;
return(DataHierarchyListForBinding);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = ex.Message;
return(null);
}
}
private string ConcatenateFamilyMedicalAntecedents(string pstrPersonId, int pintTypeFamilyId)
{
var qry = (from A in ctx.FamilyMedicalAntecedents
join B in ctx.SystemParameter on new { a = A.i_TypeFamilyId.Value, b = 149 } // ANTECEDENTES FAMILIARES MÉDICOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in ctx.SystemParameter on new { a = B.i_ParentParameterId.Value, b = 149 } // [PADRE,MADRE,HERMANOS]
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in ctx.Diseases on new { a = A.v_DiseasesId }
equals new { a = D.v_DiseasesId } into D_join
from D in D_join.DefaultIfEmpty()
where A.v_PersonId == pstrPersonId &&
A.i_IsDeleted == 0 && C.i_ParameterId == pintTypeFamilyId
select new
{
v_DiseasesName = D.v_Name
}).ToList();
return(string.Join(", ", qry.Select(p => p.v_DiseasesName)));
}
public List <PersonMedicalHistoryList> GetAntecedentConsolidateForService(string patientId)
{
//mon.IsActive = true;
try
{
List <PersonMedicalHistoryList> lis = new List <PersonMedicalHistoryList>();
int isDeleted = (int)SiNo.No;
#region querys individuales
// Obtener todos loa antecedentes de una persona (una o varias empresas)
var historyId = (from a in ctx.History
where a.v_PersonId == patientId && a.i_IsDeleted == isDeleted
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales",
v_DiseasesName = null,
v_HistoryId = a.v_HistoryId,
d_StartDate = a.d_StartDate,
d_EndDate = a.d_EndDate,
v_Occupation = a.v_workstation,
v_GroupName = null
}).ToList();
// personmedicalhistory
var q1tmp = (from A in ctx.PersonMedicalHistory
join D in ctx.Diseases on A.v_DiseasesId equals D.v_DiseasesId
where A.i_IsDeleted == isDeleted && A.v_PersonId == patientId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Medicos-Personales",
v_DiseasesName = D.v_Name,
d_StartDate = A.d_StartDate,
v_GroupName = null
}).ToList();
var q1 = (from A in q1tmp
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Medicos-Personales",
v_DiseasesName = A.v_DiseasesName,
v_DateOrGroup = A.d_StartDate.Value.ToShortDateString(),
d_StartDate = A.d_StartDate.Value
}).ToList();
// typeofeep
var q2 = (from A in historyId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales, " + A.v_Occupation,
v_DiseasesName = ConcatenateTypeOfeep(A.v_HistoryId),
v_DateOrGroup = A.d_StartDate.Value.ToString("MM/yyyy") + " - " + A.d_EndDate.Value.ToString("MM/yyyy"),
d_StartDate = A.d_StartDate,
}).ToList();
// workstationdangers
var q3 = (from A in historyId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Ocupacionales, " + A.v_Occupation,
v_DiseasesName = ConcatenateWorkStationDangers(A.v_HistoryId),
v_DateOrGroup = A.d_StartDate.Value.ToString("MM/yyyy") + " - " + A.d_EndDate.Value.ToString("MM/yyyy"),
d_StartDate = A.d_StartDate,
}).ToList();
// noxioushabits
var q4 = (from A in ctx.NoxiousHabits
join B in ctx.SystemParameter on new { a = A.i_TypeHabitsId.Value, b = 148 } // HÁBITOS NOCIVOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == patientId
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Hábitos Nocivos",
v_DiseasesName = B.v_Value1 + ", " + A.v_Frequency,
}).ToList();
// familymedicalantecedents
var q5tmp = (from A in ctx.FamilyMedicalAntecedents
join B in ctx.SystemParameter on new { a = A.i_TypeFamilyId.Value, b = 149 } // ANTECEDENTES FAMILIARES MÉDICOS
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in ctx.SystemParameter on new { a = B.i_ParentParameterId.Value, b = 149 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_PersonId == patientId
group C by new { C.i_ParameterId, C.v_Value1 } into g
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = "Familiares",
i_TypeFamilyId = g.Key.i_ParameterId,
v_TypeFamilyName = g.Key.v_Value1
}).ToList();
var q5 = (from A in q5tmp
select new PersonMedicalHistoryList
{
v_AntecedentTypeName = A.v_AntecedentTypeName,
v_DiseasesName = ConcatenateFamilyMedicalAntecedents(patientId, A.i_TypeFamilyId),
v_DateOrGroup = A.v_TypeFamilyName
}).ToList();
#endregion
#region Fusion
if (q1.Count > 0)
{
lis.AddRange(q1);
}
if (q2.Count > 0)
{
lis.AddRange(q2);
}
if (q3.Count > 0)
{
lis.AddRange(q3);
}
if (q4.Count > 0)
{
lis.AddRange(q4);
}
if (q5.Count > 0)
{
lis.AddRange(q5);
}
#endregion
return(lis.OrderBy(x => x.v_AntecedentTypeName).ToList());
}
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 <Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList> GetPersonMedicalHistoryReport(string pstrPersonId)
{
//mon.IsActive = true;
try
{
SigesoftEntitiesModel dbContext = new SigesoftEntitiesModel();
var query = (from A in dbContext.personmedicalhistory
join B in dbContext.systemparameter on new { a = A.v_DiseasesId, b = 147 }
equals new { a = B.v_Value1, b = B.i_GroupId } into B_join
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemparameter on new { a = B.i_ParentParameterId.Value, b = 147 }
equals new { a = C.i_ParameterId, b = C.i_GroupId } into C_join
from C in C_join.DefaultIfEmpty()
join D in dbContext.diseases on A.v_DiseasesId equals D.v_DiseasesId
join E in dbContext.systemparameter on new { a = A.i_TypeDiagnosticId.Value, b = 139 }
equals new { a = E.i_ParameterId, b = E.i_GroupId } into E_join
from E in E_join.DefaultIfEmpty()
where (A.i_IsDeleted == 0) && (A.v_PersonId == pstrPersonId)
select new Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList
{
v_PersonMedicalHistoryId = A.v_PersonMedicalHistoryId,
v_PersonId = A.v_PersonId,
v_DiseasesId = A.v_DiseasesId,
v_DiseasesName = D.v_Name,
i_TypeDiagnosticId = A.i_TypeDiagnosticId,
d_StartDate = A.d_StartDate.Value,
v_TreatmentSite = A.v_TreatmentSite,
v_GroupName = C.v_Value1,
v_TypeDiagnosticName = E.v_Value1,
v_DiagnosticDetail = A.v_DiagnosticDetail,
i_Answer = A.i_AnswerId.Value,
}).ToList();
// add the sequence number on the fly
var query1 = new List <Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList>();
query1 = query.Select((x, index) => new Sigesoft.Node.WinClient.BE.PersonMedicalHistoryList
{
i_Item = index + 1,
v_PersonMedicalHistoryId = x.v_PersonMedicalHistoryId,
v_PersonId = x.v_PersonId,
v_DiseasesId = x.v_DiseasesId,
v_DiseasesName = x.v_DiseasesName,
i_TypeDiagnosticId = x.i_TypeDiagnosticId,
d_StartDate = x.d_StartDate,
v_TreatmentSite = x.v_TreatmentSite,
v_GroupName = x.v_GroupName,
v_TypeDiagnosticName = x.v_TypeDiagnosticName,
v_DiagnosticDetail = x.v_DiagnosticDetail,
i_Answer = x.i_Answer,
}).ToList();
//List<PersonMedicalHistoryList> objData = query.ToList();
return(query1);
}
catch (Exception ex)
{
return(null);
}
}
public List <RolCuotaList> GetRolCuotaPagedAndFiltered(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.rolcuota
join B in dbContext.datahierarchy on new { a = A.i_RolId.Value, b = 121 } equals new { a = B.i_ItemId, b = B.i_GroupId } 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()
where A.i_IsDeleted == 0
select new RolCuotaList
{
v_RolName = B.v_Value1,
v_RolCuotaId = A.v_RolCuotaId,
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 <RolCuotaList> objData = query.ToList();
pobjOperationResult.Success = 1;
return(objData);
}
catch (Exception ex)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(ex);
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 <TramasList> GettramasPageAndFilteredHospitalizacion(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.tramas
join B in dbContext.systemparameter on new { a = A.i_GrupoEtario.Value, b = 347 }
equals new { a = B.i_ParameterId, b = B.i_GroupId } into B_join // GRUPO ETARIO
from B in B_join.DefaultIfEmpty()
join C in dbContext.systemuser on A.i_InsertUserId equals C.i_SystemUserId
join D in dbContext.person on C.v_PersonId equals D.v_PersonId
join E in dbContext.systemuser on new { i_UpdateUserId = A.i_UpdateUserId.Value }
equals new { i_UpdateUserId = E.i_SystemUserId } into E_join
from E in E_join.DefaultIfEmpty()
join F in dbContext.person on new { v_PersonId = E.v_PersonId }
equals new { v_PersonId = F.v_PersonId } into F_join
from F in F_join.DefaultIfEmpty()
join G in dbContext.systemparameter on new { a = A.i_UPS.Value, b = 349 }
equals new { a = G.i_ParameterId, b = G.i_GroupId } into G_join // UPS DETAIL
from G in G_join.DefaultIfEmpty()
where A.i_IsDeleted == 0 && A.v_TipoRegistro == "Hospitalización"
select new TramasList
{
v_TramaId = A.v_TramaId,
v_TipoRegistro = A.v_TipoRegistro,
d_FechaIngreso = A.d_FechaIngreso.Value,
i_Genero = A.i_Genero,
GrupoEtario = B.v_Value1,
v_DiseasesName = A.v_DiseasesName,
v_CIE10Id = A.v_CIE10Id,
d_InsertDate = A.d_InsertDate.Value,
d_UpdateDate = A.d_UpdateDate.Value,
User_Crea = D.v_FirstName + " " + D.v_FirstLastName + " " + D.v_SecondLastName,
User_Act = E.i_SystemUserId == null ? "---" : F.v_FirstName + " " + F.v_FirstLastName + " " + F.v_SecondLastName,
d_FechaAlta = A.d_FechaAlta.Value,
i_UPS = A.i_UPS.Value,
i_Procedimiento = A.i_Procedimiento,
ups_Detail = G.v_Value1
};
if (!string.IsNullOrEmpty(pstrFilterExpression))
{
query = query.Where(pstrFilterExpression);
}
if (pdatBeginDate.HasValue && pdatEndDate.HasValue)
{
query = query.Where("d_FechaIngreso >= @0 && d_FechaIngreso <= @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 <TramasList> objData = query.ToList();
var tramasdetalle = (from a in objData
select new TramasList
{
v_TramaId = a.v_TramaId,
v_TipoRegistro = a.v_TipoRegistro,
d_FechaIngreso = a.d_FechaIngreso,
Genero = a.i_Genero == 1 ? "MASC" : "FEM",
GrupoEtario = a.GrupoEtario,
v_DiseasesName = a.v_DiseasesName,
v_CIE10Id = a.v_CIE10Id,
User_Crea = a.User_Crea,
User_Act = a.User_Act,
d_InsertDate = a.d_InsertDate,
d_UpdateDate = a.d_UpdateDate,
d_FechaAlta = a.d_FechaAlta,
i_UPS = a.i_UPS,
ups_Detail = a.ups_Detail,
i_Procedimiento = a.i_Procedimiento,
dead_prod = a.i_Procedimiento == 0 ? "NO" : "SI",
}).ToList();
pobjOperationResult.Success = 1;
return(tramasdetalle);
}
catch (Exception e)
{
pobjOperationResult.Success = 0;
pobjOperationResult.ExceptionMessage = Common.Utils.ExceptionFormatter(e);
return(null);
//Console.WriteLine(e);
//throw;
}
}
public Dictionary <string, object> BindGrid(Initial_LinkSetup IL)
{
DataSet ds = new DataSet();
try
{
using (QuickCheck_AngularEntities Dc = new QuickCheck_AngularEntities())
{
int Min_Value = (IL.Maxcount) * ((IL.PageCount) - 1) + 1;
int Max_Value = (IL.Maxcount) * (IL.PageCount);
var _LinkList =
from A in Dc.Tbl_Links
join B in Dc.TblParentMenus on new { ParetmenuID = A.ParetmenuID } equals new { ParetmenuID = B.ParentID } into B_join
from B in B_join.DefaultIfEmpty()
where (A.LinkName.Contains(IL.SearchValue) || B.ParentMenuName.Contains(IL.SearchValue))
select new
{
LinkID = A.LinkID,
LinkName = A.LinkName,
url = A.url,
Purpose = A.Purpose,
OrderNo = A.OrderNo,
IconName = A.IconName,
IsActive = A.IsActive,
IsDeleted = A.IsDeleted,
CreatedOn = (A.CreatedOn),//.ToString("dd-MM-yyyy").Substring(0, 10) == "01-01-1900",
ModifiedOn = A.ModifiedOn,
ParetmenuID = A.ParetmenuID,
ParentMenuName = (B.ParentMenuName ?? "")
};
var result = _LinkList.AsEnumerable().Select((x, RowNumber) => new
{
RowNumber = RowNumber + 1,
x.LinkID,
x.LinkName,
x.url,
x.Purpose,
x.OrderNo,
x.IsActive,
x.CreatedOn,
x.ParentMenuName,
x.ParetmenuID,
RoleName = string.Join(",", Dc.TblRoleLinkBindings.Join(Dc.TblRoleMasters, a => a.RoleID, b => b.RoleID, (a, b) => new { a.LinkID, b.RoleName }).Where(p => p.LinkID == x.LinkID).Select(b => b.RoleName))
}).Where(x => x.RowNumber >= Min_Value && x.RowNumber <= Max_Value);
DataTable dtLink = Common.LINQResultToDataTable(result);
dtLink.TableName = "Table";
var _iconNames = (from LS in Dc.Tbl_Icon
select LS);
DataTable dtIcon = Common.LINQResultToDataTable(_iconNames);
dtIcon.TableName = "Table1";
var _RoleNames = (from LS in Dc.TblRoleMasters
where LS.IsActive == true
select new { LS.RoleID, RoleName = (LS.RoleName) + " " + (LS.RoleType == 1 ? "(Bank-Roles)" : LS.RoleType == 2 ? "(Corporate-Roles)" : "(Yoeki-Roles)") });
DataTable dtRole = Common.LINQResultToDataTable(_RoleNames);
dtRole.TableName = "Table2";
var _Parent = (from LS in Dc.TblParentMenus
where LS.IsActive == true
select new { LS.ParentID, LS.ParentMenuName });
DataTable dtParent = Common.LINQResultToDataTable(_Parent);
dtParent.TableName = "Table3";
ds.Tables.Add(dtLink);
ds.Tables.Add(dtIcon);
ds.Tables.Add(dtRole);
ds.Tables.Add(dtParent);
return(ClsJson.JsonMethods.ToJson(ds));
}
}
catch (Exception ex)
{ return(null); }
}