public void PersonSocialNetwork(int idMeeting)
{
using (var db = FactoryConn.GetConn()) {
try {
var MeetingId = int.Parse(idMeeting.ToString());
var dto = new ModelContainer();
dto.Reference = db.Table <Meeting> ().Where(s => s.Id == idMeeting).FirstOrDefault().CaseDetentionId.ToString() ?? "";
SocialNetwork me = db.Table <SocialNetwork> ().Where(mee => mee.MeetingId == idMeeting).FirstOrDefault();
if (me == null)
{
me = new SocialNetwork();
me.Comment = "";
me.MeetingId = idMeeting;
db.Insert(me);
}
PersonSocialNetwork mdl = new PersonSocialNetwork();
mdl.SocialNetworkId = me.Id;
dto.JsonModel = JsonConvert.SerializeObject(mdl);
var temp = new PersonSocialNetworkUpsert {
Model = dto
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
} catch (Exception e) {
db.Rollback();
Console.WriteLine("catched exception in MeetingController method PersonSocialNetwork");
Console.WriteLine("Exception message :::>" + e.Message);
} finally {
db.Commit();
}
db.Close();
}
}
public void MeetingDatosPersonales(int idCase)
{
using (var db = FactoryConn.GetConn()) {
if (idCase == 0)
{
idCase = db.Table <Case> ().FirstOrDefault().Id;
}
// var result = new MeetingDatosPersonalesDto ();
// var resAux = db.Table<Case> ().Where (csAux=>csAux.Id == idCase).FirstOrDefault ();
// if (resAux != null) {
// var ent = db.Table<Meeting> ().Where (mtAux=> mtAux.CaseDetentionId == resAux.Id ).FirstOrDefault ();
// if (ent != null ) {
// var imputado = db.Table<Imputed> ().Where (datosimp => datosimp.MeetingId == ent.Id).FirstOrDefault ();
// MeetingDatosPersonalesDto found = new MeetingDatosPersonalesDto ();
// found.IdFolder = resAux.IdFolder;
// found.ImputedId = imputado.Id;
// found.LastNameM = imputado.LastNameM;
// found.LastNameP = imputado.LastNameP;
// found.Name = imputado.Name;
// found.FoneticString = imputado.FoneticString;
// found.CelPhone = imputado.CelPhone;
// found.YearsMaritalStatus = imputado.YearsMaritalStatus;
// found.MaritalStatusId = imputado.MaritalStatusId;
// found.Boys = imputado.Boys;
// found.DependentBoys = imputado.DependentBoys;
// found.BirthCountry = imputado.BirthCountry;
// found.BirthMunicipality = imputado.BirthMunicipality;
// found.BirthState = imputado.BirthState;
// found.BirthLocation = imputado.BirthLocation;
// found.Nickname = imputado.Nickname;
// found.LocationId = imputado.LocationId;
//
// found.MeetingId = ent.Id;
// found.ReviewerId = ent.ReviewerId;
// found.StatusMeetingId = ent.StatusMeetingId;
// found.CommentReference = ent.CommentReference;
// found.CommentJob = ent.CommentJob;
// found.CommentSchool = ent.CommentSchool;
// found.CommentCountry = ent.CommentCountry;
// found.CommentHome = ent.CommentHome;
// found.CommentDrug = ent.CommentDrug;
// found.DateCreate = ent.DateCreate;
// found.DateTerminate = ent.DateTerminate;
//
// found.CaseId = resAux.Id;
// found.BirthDate = imputado.BirthDate;
// found.Gender = imputado.Gender;
// result=found;
// }
// }
var result = db.Query <MeetingDatosPersonalesDto> (
"SELECT cs.id_folder as 'IdFolder', im.id_imputed as 'ImputedId',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM'"
+ " ,im.birth_date as 'BirthDate', im.gender as 'Gender'"
+ " ,im.fonetic_string as 'FoneticString', im.cel_phone as 'CelPhone'"
+ " ,im.years_marital_status as 'YearsMaritalStatus', im.id_marital_status as 'MaritalStatusId'"
+ " ,im.boys as 'Boys', im.dependent_boys as 'DependentBoys'"
+ " ,im.id_country as 'BirthCountry', im.birth_municipality as 'BirthMunicipality'"
+ " ,im.birth_state as 'BirthState', im.birth_location as 'BirthLocation'"
+ " ,im.nickname as 'Nickname', im.id_location as 'LocationId'"
+ " ,im.id_birth_info_availability as 'BirthInfoId'"
+ " ,me.id_meeting as 'MeetingId'"
+ " ,me.id_reviewer as 'ReviewerId', me.id_status as 'StatusMeetingId'"
+ " ,me.comment_refernce as 'CommentReference', me.comment_job as 'CommentJob'"
+ " ,me.comment_school as 'CommentSchool', me.comment_country as 'CommentCountry'"
+ " ,me.comment_home as 'CommentHome', me.comment_drug as 'CommentDrug'"
+ " ,me.date_create as 'DateCreate', me.date_terminate as 'DateTerminate'"
// +", csm.status as 'StatusCode', csm.description as 'Description'"
+ " FROM meeting as me "
+ " left JOIN case_detention as cs ON me.id_case = cs.id_case "
+ " left JOIN imputed as im ON im.id_meeting = me.id_meeting "
// +" left JOIN cat_status_meeting as csm ON csm.id_status = me.id_status "
// +" and me.id_reviewer = 2 "
+ " where cs.id_case = ? and cs.has_negation = 0; ", idCase).FirstOrDefault();
result.CaseId = idCase;
result.ageString = services.calculateAge(result.BirthDate);
var domiciliosImputado = db.Table <ImputedHome> ().Where(im => im.MeetingId == result.MeetingId).ToList();
if (domiciliosImputado != null)
{
result.JsonDomicilios = domiciliosImputado;
}
var SE = db.Table <SocialEnvironment> ().Where(s => s.MeetingId == result.MeetingId).FirstOrDefault();
if (SE != null)
{
result.PhysicalCondition = SE.physicalCondition;
result.comment = SE.comment;
var ActList = db.Table <RelActivity> ().Where(s => s.SocialEnvironmentId == SE.Id).ToList();
if (ActList != null && ActList.Count > 0)
{
result.Activities = JsonConvert.SerializeObject(ActList);
}
}
//socialNetworkComment
var socialNetComent = db.Table <SocialNetwork> ().Where(s => s.MeetingId == result.MeetingId).FirstOrDefault();
if (socialNetComent != null)
{
result.CommentSocialNetwork = socialNetComent.Comment;
}
else
{
socialNetComent = new SocialNetwork();
socialNetComent.Comment = "";
socialNetComent.MeetingId = result.MeetingId ?? 0;
db.Insert(socialNetComent);
}
//PersonSocialNetwork
var personsSocNet = db.Table <PersonSocialNetwork> ().Where(sn => sn.SocialNetworkId == socialNetComent.Id).ToList();
if (personsSocNet != null)
{
result.JsonPersonSN = personsSocNet;
}
else
{
result.JsonPersonSN = null;
}
//Reference
var references = db.Table <Reference> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (references != null)
{
result.JsonReferences = references;
}
else
{
result.JsonReferences = null;
}
//Laboral History
var trabajos = db.Table <Job> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (trabajos != null)
{
result.JsonJobs = trabajos;
}
else
{
result.JsonJobs = null;
}
//school history
var escuelaUtlActual = db.Table <School> ().Where(sc => sc.MeetingId == result.MeetingId).FirstOrDefault();
if (escuelaUtlActual != null)
{
result.SchoolAddress = escuelaUtlActual.Address;
result.SchoolBlock = escuelaUtlActual.block;
result.SchoolDegreeId = escuelaUtlActual.DegreeId.GetValueOrDefault();
result.SchoolName = escuelaUtlActual.Name;
result.SchoolPhone = escuelaUtlActual.Phone;
result.SchoolSpecification = escuelaUtlActual.Specification;
}
//DROGAS
var drogas = db.Table <Drug> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (drogas != null)
{
result.JsonDrugs = drogas;
}
else
{
result.JsonDrugs = null;
}
if (escuelaUtlActual != null)
{
var schedule = db.Table <Schedule> ().Where(sc => sc.SchoolId == escuelaUtlActual.Id).ToList();
if (schedule != null)
{
result.ScheduleSchool = JsonConvert.SerializeObject(schedule);
}
}
//leave country
var leaveActual = db.Table <LeaveCountry> ().Where(lv => lv.MeetingId == result.MeetingId).FirstOrDefault();
if (leaveActual != null)
{
result.OfficialDocumentationId = leaveActual.OfficialDocumentationId;
result.LivedCountryId = leaveActual.LivedCountryId;
result.timeAgo = leaveActual.timeAgo;
result.Reason = leaveActual.Reason;
result.FamilyAnotherCountryId = leaveActual.FamilyAnotherCountryId;
result.CountryId = leaveActual.CountryId;
result.State = leaveActual.State;
result.Media = leaveActual.Media;
result.Address = leaveActual.Address;
result.ImmigrationDocumentId = leaveActual.ImmigrationDocumentId;
result.RelationshipId = leaveActual.RelationshipId;
result.TimeResidence = leaveActual.TimeResidence;
result.SpecficationImmigranDoc = leaveActual.SpecficationImmigranDoc;
result.SpecificationRelationship = leaveActual.SpecificationRelationship;
result.CommunicationFamilyId = leaveActual.CommunicationFamilyId;
}
//End leave country
string output = JsonConvert.SerializeObject(result);
result.JsonMeeting = output;
result.JsonCountrys = this.jsonCountrys;
result.JsonStates = this.jsonStates;
result.JsonMunycipality = this.jsonMunycipality;
result.JsonElection = this.jsonElection;
result.JsonActivities = JsonConvert.SerializeObject(db.Table <ActivityCatalog> ().ToList());
var temp = new MeetingDatosPersonales {
Model = result
};
// var temp = new NewMeeting{Model = new EntrevistaTabla{Name="nombre" , DateBirthString=DateTime.Today.ToString("yyyy/mm/dd")} };
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}
public void ValidationMeetingBySource(int idSource)
{
using (var db = FactoryConn.GetConn()) {
var source = db.Table <SourceVerification> ().Where(sv => sv.Id == idSource).FirstOrDefault();
int idCase = (int)source.CaseRequestId;
User Reviewer = db.Table <User> ().First();
Case cs = db.Table <Case> ().Where(cas => cas.Id == idCase).ToList().First();
Meeting baseMe = db.Table <Meeting> ().Where(meet => meet.CaseDetentionId == cs.Id).First();
Imputed baseImp = db.Table <Imputed> ().Where(imput => imput.MeetingId == baseMe.Id).First();
var verification = db.Table <Verification> ().Where(ver => ver.CaseDetentionId == idCase).FirstOrDefault();
var result = new VerificationMeetingSourceDto();
result.IdFolder = cs.IdFolder;
result.ImputedId = baseImp.Id;
result.Name = baseImp.Name;
result.LastNameP = baseImp.LastNameP;
result.LastNameM = baseImp.LastNameM;
result.BirthDate = baseImp.BirthDate;
result.Gender = baseImp.Gender;
result.FoneticString = baseImp.FoneticString;
result.CelPhone = baseImp.CelPhone;
result.YearsMaritalStatus = baseImp.YearsMaritalStatus;
result.MaritalStatusId = baseImp.MaritalStatusId;
result.Boys = baseImp.Boys;
result.DependentBoys = baseImp.DependentBoys;
// result.Location = baseImp.Location;
result.LocationId = baseImp.LocationId;
result.BirthLocation = baseImp.BirthLocation;
result.BirthCountry = baseImp.BirthCountry;
result.BirthState = baseImp.BirthState;
result.BirthMunicipality = baseImp.BirthMunicipality;
result.Nickname = baseImp.Nickname;
result.BirthInfoId = baseImp.BirthInfo;
result.MeetingId = baseMe.Id;
result.ReviewerId = Reviewer.Id;
result.StatusMeetingId = baseMe.StatusMeetingId;
result.CommentReference = baseMe.CommentReference;
result.CommentCountry = baseMe.CommentCountry;
result.CommentDrug = baseMe.CommentDrug;
result.CommentHome = baseMe.CommentHome;
result.CommentJob = baseMe.CommentJob;
result.CommentSchool = baseMe.CommentSchool;
result.DateCreate = baseMe.DateCreate;
result.DateTerminate = baseMe.DateTerminate;
result.CaseId = idCase;
result.ageString = services.calculateAge(result.BirthDate);
var parantesco = db.Table <Relationship> ().ToList();
result.ListaDeRelaciones = parantesco;
var elecciones = db.Table <Election> ().ToList();
result.ListaDeElection = elecciones;
var documentosIdentificacion = db.Table <DocumentType> ().ToList();
result.ListaDeIdentificaciones = documentosIdentificacion;
var drogasCatalog = db.Table <DrugType> ().ToList();
result.ListaDeDrogas = drogasCatalog;
var periodoCatalog = db.Table <Periodicity> ().ToList();
result.ListaDePeriodicidad = periodoCatalog;
var registerCatalog = db.Table <RegisterType> ().ToList();
result.ListaDeRegisterType = registerCatalog;
result.JsonDomicilios = new List <DomiciliosVerificationDto> ();
var domiciliosImputado = db.Table <ImputedHome> ().Where(im => im.MeetingId == result.MeetingId).ToList();
if (domiciliosImputado != null && domiciliosImputado.Count > 0)
{
var domVerified = new List <DomiciliosVerificationDto> ();
foreach (ImputedHome i in domiciliosImputado)
{
var home = new DomiciliosVerificationDto(i);
if (i.webId != null && i.webId != 0L)
{
home.Id = (int)i.webId;
}
home.ScheduleList = db.Table <Schedule> ().Where(xywz => xywz.ImputedHomeId == i.Id).ToList();
domVerified.Add(home);
}
result.JsonDomicilios = domVerified;
}
foreach (DomiciliosVerificationDto h in result.JsonDomicilios)
{
h.Schedule = "";
var horario = db.Table <Schedule> ().Where(sche => sche.ImputedHomeId == h.Id).ToList();
if (horario != null && horario.Count > 0)
{
foreach (Schedule skdl in horario)
{
h.Schedule += "<tr>";
h.Schedule += "<td>" + skdl.Day + "</td>";
h.Schedule += "<td>" + skdl.Start + "</td>";
h.Schedule += "<td>" + skdl.End + "</td>";
h.Schedule += "</ tr>";
}
}
}
var SE = db.Table <SocialEnvironment> ().Where(s => s.MeetingId == result.MeetingId).FirstOrDefault();
if (SE != null)
{
result.PhysicalCondition = SE.physicalCondition;
result.comment = SE.comment;
var ActList = db.Table <RelActivity> ().Where(s => s.SocialEnvironmentId == SE.Id).ToList();
if (ActList != null && ActList.Count > 0)
{
result.Activities = JsonConvert.SerializeObject(ActList);
}
}
//socialNetworkComment
var socialNetComent = db.Table <SocialNetwork> ().Where(s => s.MeetingId == result.MeetingId).FirstOrDefault();
if (socialNetComent != null)
{
result.CommentSocialNetwork = socialNetComent.Comment;
}
else
{
socialNetComent = new SocialNetwork();
socialNetComent.Comment = "";
socialNetComent.MeetingId = result.MeetingId ?? 0;
db.Insert(socialNetComent);
}
//PersonSocialNetwork
var personsSocNet = db.Table <PersonSocialNetwork> ().Where(sn => sn.SocialNetworkId == socialNetComent.Id).ToList();
if (personsSocNet != null && personsSocNet.Count > 0)
{
var socialList = new List <PersonSocialNetworkVerificationDto> ();
foreach (PersonSocialNetwork psn in personsSocNet)
{
var nuev = new PersonSocialNetworkVerificationDto(psn);
if (psn.webId != null && psn.webId != 0L)
{
nuev.Id = (int)psn.webId;
}
socialList.Add(nuev);
}
result.JsonPersonSN = socialList;
}
else
{
result.JsonPersonSN = null;
}
//Reference
var references = db.Table <Reference> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (references != null && references.Count > 0)
{
for (var cont = 0; cont < references.Count; cont++)
{
if (references [cont].webId != null && references [cont].webId != 0L)
{
references [cont].Id = (int)references [cont].webId;
}
}
result.JsonReferences = references;
}
else
{
result.JsonReferences = null;
}
//Laboral History
var trabajos = db.Table <Job> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (trabajos != null && trabajos.Count > 0)
{
var dtojob = new List <JobVerificationDto> ();
foreach (Job j in trabajos)
{
var trabajo = new JobVerificationDto(j);
if (j.webId != null && j.webId != 0L)
{
trabajo.Id = (int)j.webId;
}
trabajo.ScheduleList = db.Table <Schedule> ().Where(trbjao => trbjao.JobId == j.Id).ToList() ?? new List <Schedule> ();
dtojob.Add(trabajo);
}
result.JsonJobs = dtojob;
}
else
{
result.JsonJobs = null;
}
//school history
var escuelaUtlActual = db.Table <School> ().Where(sc => sc.MeetingId == result.MeetingId).FirstOrDefault();
if (escuelaUtlActual != null)
{
result.SchoolAddress = escuelaUtlActual.Address;
result.SchoolBlock = escuelaUtlActual.block;
result.SchoolDegreeId = escuelaUtlActual.DegreeId.GetValueOrDefault();
result.SchoolName = escuelaUtlActual.Name;
result.SchoolPhone = escuelaUtlActual.Phone;
result.SchoolSpecification = escuelaUtlActual.Specification;
}
if (escuelaUtlActual != null)
{
result.SchoolId = escuelaUtlActual.Id;
var schedule = db.Table <Schedule> ().Where(scular => scular.SchoolId == escuelaUtlActual.Id).ToList();
if (schedule != null)
{
result.ScheduleSchool = schedule;
}
}
//DROGAS
var drogas = db.Table <Drug> ().Where(sn => sn.MeetingId == result.MeetingId).ToList();
if (drogas != null && drogas.Count > 0)
{
for (var cont2 = 0; cont2 < drogas.Count; cont2++)
{
if (drogas [cont2].webId != null && drogas [cont2].webId != 0L)
{
drogas [cont2].Id = (int)drogas [cont2].webId;
}
}
result.JsonDrugs = drogas;
}
else
{
result.JsonDrugs = null;
}
//leave country
var leaveActual = db.Table <LeaveCountry> ().Where(lv => lv.MeetingId == result.MeetingId).FirstOrDefault();
if (leaveActual != null)
{
result.OfficialDocumentationId = leaveActual.OfficialDocumentationId;
result.LivedCountryId = leaveActual.LivedCountryId;
result.timeAgo = leaveActual.timeAgo;
result.Reason = leaveActual.Reason;
result.FamilyAnotherCountryId = leaveActual.FamilyAnotherCountryId;
result.CountryId = leaveActual.CountryId;
result.State = leaveActual.State;
result.Media = leaveActual.Media;
result.Address = leaveActual.Address;
result.ImmigrationDocumentId = leaveActual.ImmigrationDocumentId;
result.RelationshipId = leaveActual.RelationshipId;
result.TimeResidence = leaveActual.TimeResidence;
result.SpecficationImmigranDoc = leaveActual.SpecficationImmigranDoc;
result.SpecificationRelationship = leaveActual.SpecificationRelationship;
result.CommunicationFamilyId = leaveActual.CommunicationFamilyId;
}
//End leave country
string output = JsonConvert.SerializeObject(result);
result.JsonMeeting = output;
result.JsonCountrys = this.JsonCountrys;
result.JsonStates = this.JsonStates;
result.JsonMunycipality = this.JsonMunycipality;
result.JsonElection = this.JsonElection;
result.JsonActivities = this.JsonActivities;
//result source
result.SourceAddress = source.Address;
result.SourceAge = source.Age;
result.SourceId = source.Id;
result.SourceName = source.FullName;
result.SourcePhone = source.Phone;
var SourceRelationship = db.Table <Relationship> ().Where(sore => sore.Id == source.RelationshipId).FirstOrDefault();
if (SourceRelationship.Equals(null))
{
result.SourceRelationshipString = "";
}
else
{
result.SourceRelationshipString = SourceRelationship.Name;
}
var temp = new VerificacionInterview {
Model = result
};
// var temp = new NewMeeting{Model = new EntrevistaTabla{Name="nombre" , DateBirthString=DateTime.Today.ToString("yyyy/mm/dd")} };
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}