public void InsertStatusCase(Activity act){
using (var db = FactoryConn.GetConn ()) {
db.CreateTable<StatusCase> ();
if (db.Table<StatusCase> ().Count () == 0) {
IEnumerable<String[]> data = GetDataOfFile (ConstantsDb.CONTENT_FOLDER_CATALOG + "/status_case.txt", act);
List<StatusCase> entities = new List<StatusCase> ();
foreach (String[] line in data) {
try {
StatusCase model = new StatusCase ();
model.Id = int.Parse (line [0]);
model.Name = line [1];
model.Description = line [2];
entities.Add (model);
} catch (Exception e) {
Console.WriteLine ("StatusCase error " + e.Message);
}
}
db.InsertAll (entities);
var content = db.Table<StatusCase> ().ToList ();
Console.WriteLine ("Se inserto en tabla StatusCase:");
foreach (StatusCase m in content) {
Console.WriteLine ("Id: " + m.Id + " Name:" + m.Name);
}
}
db.Close ();
}
}
public void IndexVerificacion()
{
StatusMeeting statusMeeting1 = services.statusMeetingfindByCode(Constants.S_MEETING_INCOMPLETE);
StatusMeeting statusMeeting2 = services.statusMeetingfindByCode(Constants.S_MEETING_INCOMPLETE_LEGAL);
StatusCase sc = services.statusCasefindByCode(Constants.CASE_STATUS_MEETING);
using (var db = FactoryConn.GetConn()) {
var result = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM',"
+ " im.birth_date as 'DateBirth', im.gender as 'Gender', 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 "
+ " WHERE me.id_status in (?,?) "
// +" and me.id_reviewer = 2 "
+ " AND cs.id_status = ?; ", statusMeeting1.Id, statusMeeting2.Id, sc.Id);
var temp = new MeetingList {
Model = result
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}
public void IndexSuperv()
{
using (var db = FactoryConn.GetConn()) {
db.CreateTable <User> ();
var usrList = db.Table <User> ().ToList();
User reviewer = usrList.FirstOrDefault();
int revId = 0;
if (reviewer != null && reviewer.Id > 0)
{
revId = reviewer.Id;
}
//estatus para supervicion
db.CreateTable <HearingFormatImputed> ();
db.CreateTable <HearingFormat> ();
StatusCase statusCaseSupervition1 = services.statusCasefindByCode(Constants.CASE_STATUS_TECHNICAL_REVIEW);
StatusCase statusCaseSupervition2 = services.statusCasefindByCode(Constants.CASE_STATUS_HEARING_FORMAT_END);
StatusCase statusCaseSupervition3 = services.statusCasefindByCode(Constants.CASE_STATUS_HEARING_FORMAT_INCOMPLETE);
StatusCase statusCaseSupervition4 = services.statusCasefindByCode(Constants.CASE_STATUS_CONDITIONAL_REPRIEVE);
StatusCase statusCaseSupervition5 = services.statusCasefindByCode(Constants.CASE_STATUS_FRAMING_INCOMPLETE);
StatusCase statusCaseSupervition6 = services.statusCasefindByCode(Constants.CASE_STATUS_FRAMING_COMPLETE);
StatusCase statusCaseSupervition7 = services.statusCasefindByCode(Constants.CASE_STATUS_NOT_PROSECUTE_OPEN);
StatusCase sc = services.statusCasefindByCode(Constants.CASE_STATUS_VERIFICATION);
var result = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM',"
+ " im.birth_date as 'DateBirth', im.gender as 'Gender', 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 "
+ " Where cs.id_status in (?,?,?,?,?,?,?) "
+ " and me.id_reviewer = ? ", statusCaseSupervition1.Id, statusCaseSupervition2.Id,
statusCaseSupervition3.Id, statusCaseSupervition4.Id,
statusCaseSupervition5.Id, statusCaseSupervition6.Id, statusCaseSupervition7.Id, revId);
var c1 = 0;
if (result != null)
{
for (c1 = 0; c1 < result.Count; c1++)
{
result [c1].Action = "hearing";
}
}
var temp = new SyncCaseListSup {
Model = result
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}
public void Index()
{
init();
StatusMeeting statusMeeting1 = services.statusMeetingfindByCode(Constants.S_MEETING_INCOMPLETE);
StatusMeeting statusMeeting2 = services.statusMeetingfindByCode(Constants.S_MEETING_INCOMPLETE_LEGAL);
StatusCase sc = services.statusCasefindByCode(Constants.CASE_STATUS_MEETING);
StatusCase sc1 = services.statusCasefindByCode(Constants.ST_CASE_TABLET_ASSIGNED);
using (var db = FactoryConn.GetConn()) {
db.CreateTable <User> ();
var usrList = db.Table <User> ().ToList();
User reviewer = usrList.FirstOrDefault();
int revId = 0;
if (reviewer != null && reviewer.Id != 0)
{
revId = reviewer.Id;
}
var result = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM',"
+ " im.birth_date as 'DateBirth', im.gender as 'Gender', 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"
+ " WHERE me.id_status in (?,?) "
+ " and me.id_reviewer = ? "
+ " AND cs.id_status in (?,?); ", statusMeeting1.Id, statusMeeting2.Id, revId, sc.Id, sc1.Id);
var temp = new MeetingList {
Model = result
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}
public void Index()
{
using (var db = FactoryConn.GetConn()) {
db.CreateTable <User> ();
var usrList = db.Table <User> ().ToList();
User reviewer = usrList.FirstOrDefault();
int revId = 0;
if (reviewer != null && reviewer.Id > 0)
{
revId = reviewer.Id;
}
//estatus de meeting terminados
StatusMeeting statusMeeting2 = services.statusMeetingfindByCode(Constants.S_MEETING_INCOMPLETE_LEGAL);
StatusCase sc = services.statusCasefindByCode(Constants.CASE_STATUS_MEETING);
StatusCase sc1 = services.statusCasefindByCode(Constants.ST_CASE_TABLET_ASSIGNED);
var result = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM',"
+ " im.birth_date as 'DateBirth', im.gender as 'Gender', csm.status as 'StatusCode', csm.description as 'Description' , me.id_reviewer as 'ReviewerId' "
+ " 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 "
+ " WHERE me.id_status =? "
+ " and me.id_reviewer = ? "
+ " AND cs.id_status in (?,?); ", statusMeeting2.Id, revId, sc.Id, sc1.Id);
var c1 = 0;
if (result != null)
{
for (c1 = 0; c1 < result.Count; c1++)
{
result [c1].Action = "meeting";
}
}
StatusVerification statusVerification1 = services.statusVerificationfindByCode(Constants.VERIFICATION_STATUS_AUTHORIZED);
StatusVerification statusVerification2 = services.statusVerificationfindByCode(Constants.VERIFICATION_STATUS_MEETING_COMPLETE);
StatusCase scv = services.statusCasefindByCode(Constants.CASE_STATUS_VERIFICATION);
StatusCase scv1 = services.statusCasefindByCode(Constants.ST_CASE_TABLET_ASSIGNED);
var result2 = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder', "
+ " csm.description as 'StatusCode', csm.description as 'Description' , me.id_reviewer as 'ReviewerId' "
+ " , me.id_verification as 'MeetingStatusId' "
+ " FROM verification as me "
+ " left JOIN case_detention as cs ON me.id_case = cs.id_case "
+ " left JOIN cat_status_verification as csm ON csm.id_status = me.id_status_verification "
+ " WHERE me.id_status_verification in (?,?) "
+ " and me.id_reviewer = ? "
+ " AND cs.id_status in (?,?); ", statusVerification1.Id, statusVerification2.Id, revId, scv.Id, scv1.Id);
var c2 = 0;
try {
if (result2 != null)
{
for (c2 = 0; c2 < result2.Count; c2++)
{
var id = result2 [c2].MeetingStatusId;
var expected = db.Table <SourceVerification> ().Where(sv => sv.VerificationId == id && sv.Visible == true).ToList();
var finished = db.Table <SourceVerification> ().Where(sv => sv.VerificationId == id && sv.DateComplete != null && sv.Visible == true).ToList();
if (expected.Count == finished.Count)
{
result2 [c2].Action = "verification";
}
else
{
result2 [c2].Action = "verificationIncomplete";
}
var caseis = result2 [c2].CaseId;
var me = db.Table <Meeting> ().Where(met => met.CaseDetentionId == caseis).FirstOrDefault();
if (me != null)
{
var imp = db.Table <Imputed> ().Where(iputad => iputad.MeetingId == me.Id).FirstOrDefault();
if (imp != null)
{
result2 [c2].Name = imp.Name;
result2 [c2].LastNameP = imp.LastNameP;
result2 [c2].LastNameM = imp.LastNameM;
result2 [c2].Gender = imp.Gender;
}
}
}
}
} catch (Exception e) {
Console.WriteLine("excepcion en sincronizacion ");
Console.WriteLine(e.Message);
}
result.AddRange(result2);
/////
StatusMeeting statusMeetingNeg = services.statusMeetingfindByCode(Constants.S_MEETING_DECLINE);
StatusCase sc2 = services.statusCasefindByCode(Constants.CASE_STATUS_NOT_PROSECUTE);
var result3 = db.Query <MeetingTblDto> (
"SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',im.name as 'Name',im.lastname_p as 'LastNameP',im.lastname_m as 'LastNameM',"
+ " im.birth_date as 'DateBirth', im.gender as 'Gender', csm.status as 'StatusCode', csm.description as 'Description' , me.id_reviewer as 'ReviewerId' "
+ " 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 "
+ " WHERE me.id_status =? "
+ " and me.id_reviewer = ? "
+ " AND cs.id_status = ?; ", statusMeetingNeg.Id, revId, sc2.Id);
var c3 = 0;
if (result != null)
{
for (c3 = 0; c3 < result3.Count; c3++)
{
result3 [c3].Action = "negacion";
}
}
result.AddRange(result3);
/////
var temp = new SyncCaseList {
Model = result
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}
public void Index()
{
StatusVerification statusVerification1 = services.statusVerificationfindByCode(Constants.VERIFICATION_STATUS_AUTHORIZED);
StatusVerification statusVerification2 = services.statusVerificationfindByCode(Constants.VERIFICATION_STATUS_MEETING_COMPLETE);
StatusCase sc = services.statusCasefindByCode(Constants.CASE_STATUS_VERIFICATION);
StatusCase sc1 = services.statusCasefindByCode(Constants.ST_CASE_TABLET_ASSIGNED);
using (var db = FactoryConn.GetConn()) {
db.CreateTable <User> ();
var usrList = db.Table <User> ().ToList();
User reviewer = usrList.FirstOrDefault();
int revId = 0;
if (reviewer != null && reviewer.Id > 0)
{
revId = reviewer.Id;
}
var result = new List <MeetingTblDto> ();
var resAux = db.Table <Verification> ().Where(meAux => meAux.ReviewerId == revId && (meAux.StatusVerificationId == statusVerification1.Id || meAux.StatusVerificationId == statusVerification2.Id)).ToList();
foreach (Verification ent in resAux)
{
var casoRev = db.Table <Case> ().Where(revCase => revCase.Id == ent.CaseDetentionId).FirstOrDefault();
if (casoRev != null && (casoRev.StatusCaseId == sc.Id || casoRev.StatusCaseId == sc1.Id))
{
MeetingTblDto found = new MeetingTblDto();
found.CaseId = casoRev.Id;
found.IdFolder = casoRev.IdFolder;
found.StatusCode = ent.StatusVerificationId == statusVerification1.Id ? statusVerification1.Description : statusVerification2.Description;
found.Description = casoRev.StatusCaseId == sc.Id ? sc.Description : sc1.Description;
found.ReviewerId = revId;
result.Add(found);
}
}
// var result = db.Query<MeetingTblDto> (
// "SELECT cs.id_case as 'CaseId',cs.id_folder as 'IdFolder',"
// + " csm.description as 'StatusCode', csm.description as 'Description' , me.id_reviewer as 'ReviewerId' "
// + " FROM verification as me "
// + " left JOIN case_detention as cs ON me.id_case = cs.id_case "
// + " left JOIN cat_status_verification as csm ON csm.id_status = me.id_status_verification "
// + " WHERE me.id_status_verification in (?,?) "
// + " and me.id_reviewer = ? "
// + " AND cs.id_status in (?,?); ", statusVerification1.Id, statusVerification2.Id, revId, sc.Id, sc1.Id);
var counter = 0;
for (counter = 0; counter < result.Count; counter++)
{
var caseis = result [counter].CaseId;
var me = db.Table <Meeting> ().Where(met => met.CaseDetentionId == caseis).FirstOrDefault();
if (me != null)
{
var imp = db.Table <Imputed> ().Where(iputad => iputad.MeetingId == me.Id).FirstOrDefault();
if (imp != null)
{
result [counter].Name = imp.Name;
result [counter].LastNameP = imp.LastNameP;
result [counter].LastNameM = imp.LastNameM;
result [counter].Gender = imp.Gender;
}
}
}
var temp = new VerificationList {
Model = result
};
var pagestring = "nada que ver";
pagestring = temp.GenerateString();
webView.LoadHtmlString(pagestring);
db.Close();
}
}