package com.google.appengine.codelab;
import static com.google.appengine.api.datastore.FetchOptions.Builder.withLimit;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.appengine.api.datastore.DatastoreService;
import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.datastore.PreparedQuery;
import com.google.appengine.api.datastore.Query;
import com.google.appengine.api.datastore.Query.CompositeFilterOperator;
import com.google.appengine.api.datastore.Query.Filter;
import com.google.appengine.api.datastore.Query.FilterOperator;
import com.google.appengine.api.datastore.Query.FilterPredicate;
@SuppressWarnings("serial")
public class UserStory10Servlet extends BaseServlet {
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
super.doGet(req, resp);
insertEntities();
PrintWriter out = resp.getWriter();
if (req.getParameter("q").equals("q1")) {
out.println(writeAllSistemasOperacionais());
} else if (req.getParameter("q").equals("q2")) {
out.println(writeAllSisNomes());
} else if (req.getParameter("q").equals("q3")) {
out.println(writeSisNomeBySesId1());
} else if (req.getParameter("q").equals("q4")) {
out.println(writeTotalFraudesBySistemaOperacional("", ""));
} else if (req.getParameter("q").equals("q5")) {
out.println(writeTotalFraudesByAdroid41("", ""));
} else if (req.getParameter("q").equals("q6")) {
out.println(writeTotalFraudesBySistemaOperacionalType("", ""));
} else if (req.getParameter("q").equals("q7")) {
out.println(writeTotalFraudesByWin7IE9("", ""));
} else if (req.getParameter("q").equals("q8")) {
out.println(writeSisNomeLocalidadeByFraudeId5());
}
return;
}
public static void insertEntities() { //Guigows: Mudei para PUBLIC STATIC VOID
Localidade.createOrUpdateLocalidade("1", new Float(-13.58), new Float(-51.85), "", "", "", "", "");
Localidade.createOrUpdateLocalidade("2", new Float(-15.78), new Float(-47.92), "", "", "", "", "");
Cliente.createOrUpdateCliente("1", "joao", "Joao", "Costa", "", "", "", "1");
Cliente.createOrUpdateCliente("2", "jose", "JOSE", "SILVA", "", "", "", "1");
Cliente.createOrUpdateCliente("3", "maria", "MARIA", "BENEDITA", "", "", "", "1");
Estabelecimento.createOrUpdateEstabelecimento("1", "", "ESTABELECIMENTO1", "1");
Estabelecimento.createOrUpdateEstabelecimento("2", "", "ESTABELECIMENTO2", "2");
Midia.createOrUpdateMidia("1", "MIDIA", "", "", "", "1");
Midia.createOrUpdateMidia("2", "MIDIA", "", "", "", "2");
Midia.createOrUpdateMidia("3", "MIDIA", "", "", "", "3");
Transacao.createOrUpdateTransacao("1", "", new Double(10), "20130609", "", "1", "1", "1", "1");
Transacao.createOrUpdateTransacao("2", "", new Double(10), "20130609", "", "1", "2", "2", "1");
Transacao.createOrUpdateTransacao("3", "", new Double(500.1),"20130609", "", "1", "3", "2", "1");
Transacao.createOrUpdateTransacao("4", "", new Double(25), "20130609", "", "1", "2", "2", "1");
Transacao.createOrUpdateTransacao("5", "", new Double(80), "20130609", "", "1", "3", "3", "1");
Transacao.createOrUpdateTransacao("6", "", new Double(500), "20130609", "", "1", "3", "3", "2");
Transacao.createOrUpdateTransacao("7", "", new Double(50), "20130609", "", "1", "3", "3", "2");
Transacao.createOrUpdateTransacao("8", "", new Double(50), "20130609", "", "1", "3", "3", "2");
Transacao.createOrUpdateTransacao("9", "", new Double(30), "20130609", "", "1", "3", "3", "2");
Transacao.createOrUpdateTransacao("10", "", new Double(100), "20130609", "", "1", "2", "3", "2");
Transacao.createOrUpdateTransacao("11", "", new Double(15),"20130609", "", "1", "2", "3", "2");
Transacao.createOrUpdateTransacao("12", "", new Double(120), "20130609", "", "1", "1", "3", "2");
Transacao.createOrUpdateTransacao("13", "", new Double(120), "20130610", "", "1", "1", "3", "2"); //Guigows: inseri este registro
Fraude.createOrUpdateFraude("1", "FRAUDE1", "TIPO1", "", "09/06/2013", "2");
Fraude.createOrUpdateFraude("2", "FRAUDE2", "TIPO1", "", "09/06/2013", "3");
Fraude.createOrUpdateFraude("3", "FRAUDE3", "TIPO1", "", "09/06/2013", "6");
Fraude.createOrUpdateFraude("4", "FRAUDE4", "TIPO1", "", "09/06/2013", "7");
Fraude.createOrUpdateFraude("5", "FRAUDE5", "TIPO1", "", "09/06/2013", "10");
Fraude.createOrUpdateFraude("6", "FRAUDE6", "TIPO1", "", "09/06/2013", "12");
Fraude.createOrUpdateFraude("7", "FRAUDE7", "TIPO1", "", "09/06/2013", "13"); //Guigows: inseri este registro
SistemaOperacional sis = new SistemaOperacional();
sis.createOrUpdate(1, "Windows XP", "5.1", "0");
sis.createOrUpdate(2, "Windows 7", "6.1", "0");
sis.createOrUpdate(3, "Ubuntu", "12.04", "0");
sis.createOrUpdate(4, "Android", "4.1", "1");
sis.createOrUpdate(5, "Android", "4.2", "1");
sis.createOrUpdate(6, "iOS", "6.0", "1");
Navegador nav = new Navegador();
nav.createOrUpdate(1, "Chrome", "27.0");
nav.createOrUpdate(2, "Firefox", "21.0");
nav.createOrUpdate(3, "Internet Explorer", "9.0");
Sessao ses = new Sessao();
ses.createOrUpdate(1, "1", 3, 2, "20130606", "19:30:00");
ses.createOrUpdate(2, "2", 1, 1, "20130606", "19:31:00");
ses.createOrUpdate(3, "3", 5, 1, "20130606", "19:32:00");
ses.createOrUpdate(4, "3", 2, 3, "20130606", "19:35:00");
ses.createOrUpdate(5, "1", 4, 1, "20130609", "08:31:00");
ses.createOrUpdate(6, "1", 1, 2, "20130609", "08:31:00"); //Guigows: inseri este registro
Transacao.updateSessao("2", 2);
Transacao.updateSessao("3", 4);
Transacao.updateSessao("6", 4);
Transacao.updateSessao("10", 2);
Transacao.updateSessao("12", 5);
Transacao.updateSessao("13", 6); //Guigows: inseri este registro
}
public static long countFraudesBySistemaOperacional(String sis_nome, String sis_versao,
String sis_movel, String data_ini, String data_fin) {
DatastoreService ds = Util.getDatastoreServiceInstance();
List<Filter> filter = new ArrayList<Filter>();
long count = 0;
Query query = new Query("SistemaOperacional");
if (sis_nome != "") {
filter.add(new FilterPredicate("sis_nome", FilterOperator.EQUAL, sis_nome));
}
if (sis_versao != "") {
filter.add(new FilterPredicate("sis_versao", FilterOperator.EQUAL, sis_versao));
}
if (sis_movel != "") {
filter.add(new FilterPredicate("sis_movel", FilterOperator.EQUAL, sis_movel));
}
if (filter.size() > 0) {
query.setFilter(filter.size() == 1 ? filter.get(0) : CompositeFilterOperator.and(filter));
}
for (Entity sis : ds.prepare(query).asIterable()) {
for (Entity ses : new Sessao().getBySisId(sis.getKey().getId(), data_ini,
data_fin).asIterable()) {
for (Entity tra : getTransacaoBySesId(ses.getKey().getId()).asIterable()) {
count += getFraudeByTransId(tra.getKey().getName()).
countEntities(withLimit(10000));
}
}
}
return count;
}
public static long countFraudesBySistemaOperacionalAndNavegador(String sis_nome,
String sis_versao, String sis_movel, String nav_nome, String nav_versao,
String data_ini, String data_fin) {
DatastoreService ds = Util.getDatastoreServiceInstance();
List<Filter> fsis = new ArrayList<Filter>();
List<Filter> fnav = new ArrayList<Filter>();
long count = 0;
Query qsis = new Query("SistemaOperacional");
Query qnav = new Query("Navegador");
if (sis_nome != "") {
fsis.add(new FilterPredicate("sis_nome", FilterOperator.EQUAL, sis_nome));
}
if (sis_versao != "") {
fsis.add(new FilterPredicate("sis_versao", FilterOperator.EQUAL, sis_versao));
}
if (sis_movel != "") {
fsis.add(new FilterPredicate("sis_movel", FilterOperator.EQUAL, sis_movel));
}
if (fsis.size() > 0) {
qsis.setFilter(fsis.size() == 1 ? fsis.get(0) : CompositeFilterOperator.and(fsis));
}
if (nav_nome != "") {
fnav.add(new FilterPredicate("nav_nome", FilterOperator.EQUAL, nav_nome));
}
if (nav_versao != "") {
fnav.add(new FilterPredicate("nav_versao", FilterOperator.EQUAL, nav_versao));
}
if (fnav.size() > 0) {
qnav.setFilter(fnav.size() == 1 ? fnav.get(0) : CompositeFilterOperator.and(fnav));
}
for (Entity sis : ds.prepare(qsis).asIterable()) {
for (Entity nav : ds.prepare(qnav).asIterable()) {
for (Entity ses : new Sessao().getBySisAndNavIds(sis.getKey().getId(),
nav.getKey().getId(), data_ini, data_fin).asIterable()) {
for (Entity tra : getTransacaoBySesId(ses.getKey().getId()).asIterable()) {
count += getFraudeByTransId(tra.getKey().getName()).
countEntities(withLimit(10000));
}
}
}
}
return count;
}
public static PreparedQuery getByFilter(String kind, Filter filter) {
DatastoreService ds = Util.getDatastoreServiceInstance();
Query query = new Query(kind);
query.setFilter(filter);
return ds.prepare(query);
}
public static PreparedQuery getFraudeByTransId(String trans_id) {
Filter filter = new FilterPredicate("trans_id", FilterOperator.EQUAL, trans_id);
return getByFilter("Fraude", filter);
}
public static String getSisNomeBySesId(long ses_id) {
long sis_id = Long.parseLong(new Sessao().get(ses_id).getProperty("sis_id").toString());
String sis_nome = new SistemaOperacional().get(sis_id).getProperty("sis_nome").toString();
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
sb.append("{\"sis_nome\":");
sb.append("\"" + sis_nome + "\"");
sb.append("}");
sb.append("]}");
return sb.toString();
}
public static String getSisNomeLocalidadeByFraudeId(String fraude_id) {
String trans_id = Fraude.getSingleFraude(fraude_id).getProperty("trans_id").toString();
Entity trans = Transacao.getSingleTransacao(trans_id);
if (trans.getProperty("ses_id") != null) {
long ses_id = Long.parseLong(trans.getProperty("ses_id").toString());
long sis_id = Long.parseLong(new Sessao().get(ses_id).getProperty("sis_id").toString());
String sis_nome = new SistemaOperacional().get(sis_id).getProperty("sis_nome").toString();
String localidade_id = Transacao.getSingleTransacao(trans_id).getProperty("localidade_id").toString();
Entity loc = Localidade.getSingleLocalidade(localidade_id);
String localidade_latitude = String.format("%.2f", Float.parseFloat(loc.getProperty("localidade_latitude").toString()));
String localidade_longitude = String.format("%.2f", Float.parseFloat(loc.getProperty("localidade_longitude").toString()));
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
sb.append("{\"sis_nome\":");
sb.append("\"" + sis_nome + "\"");
sb.append(",\"localidade_latitude\":");
sb.append("\"" + localidade_latitude + "\"");
sb.append(",\"localidade_longitude\":");
sb.append("\"" + localidade_longitude + "\"");
sb.append("}");
sb.append("]}");
return sb.toString();
} else {
return "";
}
}
public static String getSistemaOperacionalJSON(long sis_id, String sis_nome,
String sis_versao, String sis_movel) {
StringBuilder sb = new StringBuilder();
sb.append("{");
if (sis_id > 0) {
sb.append("\"sis_id\":");
sb.append("\"" + sis_id + "\"");
sb.append(",");
}
sb.append("\"sis_nome\":");
sb.append("\"" + sis_nome + "\"");
sb.append(",\"sis_versao\":");
sb.append("\"" + sis_versao + "\"");
sb.append(",\"sis_movel\":");
sb.append("\"" + sis_movel + "\"");
return sb.toString();
}
public static String getTotalFraudesBySistemaOperacional(Iterable<Entity> iter,
String data_ini, String data_fin) {
long total = totalFraudesByInternetBanking(data_ini, data_fin);
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
for (Entity sis : iter) {
long sis_id = sis.getKey().getId();
String sis_nome = sis.getProperty("sis_nome").toString();
String sis_versao = sis.getProperty("sis_versao").toString();
String sis_movel = sis.getProperty("sis_movel").toString().equals("1") ? "Sim" :
"N�o";
long count = countFraudesBySistemaOperacional(sis_nome, sis_versao, "", data_ini,
data_fin);
sb.append(getSistemaOperacionalJSON(sis_id, sis_nome, sis_versao, sis_movel));
sb.append(",\"count\":");
sb.append("\"" + count + "\"");
sb.append(",\"percentage\":");
sb.append("\"" + String.format("%.2f%%", new Float((count * 100) / (double) total))
+ "\"");
sb.append("},");
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append("]}");
return sb.toString();
}
public static String getTotalFraudesBySisNav(String sis_nome, String sis_versao,
String nav_nome, String nav_versao, String data_ini, String data_fin) {
Filter f1 = new FilterPredicate("sis_nome", FilterOperator.EQUAL, sis_nome);
Filter f2;
Filter fc;
if (sis_versao != "") {
f2 = new FilterPredicate("sis_versao", FilterOperator.EQUAL, sis_versao);
fc = CompositeFilterOperator.and(f1, f2);
} else {
fc = f1;
}
Entity sis = new SistemaOperacional().get(fc).asSingleEntity();
long sis_id = sis.getKey().getId();
sis_nome = sis.getProperty("sis_nome").toString();
sis_versao = sis.getProperty("sis_versao").toString();
String sis_movel = sis.getProperty("sis_movel").toString();
f1 = new FilterPredicate("nav_nome", FilterOperator.EQUAL, nav_nome);
if (nav_versao != "") {
f2 = new FilterPredicate("nav_versao", FilterOperator.EQUAL, nav_versao);
fc = CompositeFilterOperator.and(f1, f2);
} else {
fc = f1;
}
Entity nav = new Navegador().get(fc).asSingleEntity();
long nav_id = nav.getKey().getId();
nav_nome = nav.getProperty("nav_nome").toString();
nav_versao = nav.getProperty("nav_versao").toString();
long total = totalFraudesByInternetBanking(data_ini, data_fin);
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
long count = countFraudesBySistemaOperacionalAndNavegador(sis_nome, sis_versao,
sis_movel, nav_nome, nav_versao, data_ini, data_fin);
sb.append("{\"sis_id\":");
sb.append("\"" + sis_id + "\"");
sb.append(",\"sis_nome\":");
sb.append("\"" + sis_nome + "\"");
sb.append(",\"sis_versao\":");
sb.append("\"" + sis_versao + "\"");
sb.append(",\"sis_movel\":");
sb.append("\"" + (sis_movel.equals("1") ? "Sim" : "N�o") + "\"");
sb.append(",\"nav_id\":");
sb.append("\"" + nav_id + "\"");
sb.append(",\"nav_nome\":");
sb.append("\"" + nav_nome + "\"");
sb.append(",\"nav_versao\":");
sb.append("\"" + nav_versao + "\"");
sb.append(",\"count\":");
sb.append("\"" + count + "\"");
sb.append(",\"percentage\":");
sb.append("\"" + String.format("%.2f%%", new Float((count * 100) / (double) total))
+ "\"");
sb.append("}");
sb.append("]}");
return sb.toString();
}
public static PreparedQuery getTransacaoBySesId(long ses_id) {
Filter filter = new FilterPredicate("ses_id", FilterOperator.EQUAL, ses_id);
return getByFilter("Transacao", filter);
}
public static long totalFraudesByInternetBanking(String data_ini, String data_fin) {
long count = 0;
for (Entity ses : new Sessao().get(data_ini, data_fin).asIterable()) {
for (Entity tra : getTransacaoBySesId(ses.getKey().getId()).asIterable()) {
count += getFraudeByTransId(tra.getKey().getName()).
countEntities(withLimit(10000));
}
}
return count;
}
public static String writeAllSistemasOperacionais() {
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
for (Entity sis : new SistemaOperacional().getAll().asIterable()) {
long sis_id = sis.getKey().getId();
String sis_nome = sis.getProperty("sis_nome").toString();
String sis_versao = sis.getProperty("sis_versao").toString();
String sis_movel = sis.getProperty("sis_movel").toString().equals("1") ? "Sim" :
"N�o";
sb.append(getSistemaOperacionalJSON(sis_id, sis_nome, sis_versao, sis_movel));
sb.append("},");
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append("]}");
return sb.toString();
}
public static String writeAllSisNomes() {
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
for (String sis_nome : new SistemaOperacional().nameAsList()) {
sb.append("{\"sis_nome\":");
sb.append("\"" + sis_nome + "\"");
sb.append("},");
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append("]}");
return sb.toString();
}
public static String writeSisNomeBySesId1() {
return getSisNomeBySesId(1);
}
public static String writeTotalFraudesBySistemaOperacional(String data_ini, String data_fin) {
return getTotalFraudesBySistemaOperacional(
new SistemaOperacional().getAll().asIterable(), data_ini, data_fin);
}
public static String writeTotalFraudesByAdroid41(String data_ini, String data_fin) {
Filter f1 = new FilterPredicate("sis_nome", FilterOperator.EQUAL, "Android");
Filter f2 = new FilterPredicate("sis_versao", FilterOperator.EQUAL, "4.1");
Filter fc = CompositeFilterOperator.and(f1, f2);
return getTotalFraudesBySistemaOperacional(
new SistemaOperacional().get(fc).asIterable(), data_ini, data_fin);
}
public static String writeTotalFraudesBySistemaOperacionalType(String data_ini,
String data_fin) {
long total = totalFraudesByInternetBanking(data_ini, data_fin);
StringBuilder sb = new StringBuilder();
sb.append("{\"data\": [");
long count = countFraudesBySistemaOperacional("", "", "0", data_ini, data_fin);
sb.append("{\"type\":");
sb.append("\"Desktop\"");
sb.append(",\"count\":");
sb.append("\"" + count + "\"");
sb.append(",\"percentage\":");
sb.append("\"" + String.format("%.2f%%", new Float((count * 100) / (double) total))
+ "\"");
sb.append("},");
sb.append("{\"type\":");
sb.append("\"M�vel\"");
sb.append(",\"count\":");
sb.append("\"" + (total - count) + "\"");
sb.append(",\"percentage\":");
sb.append("\"" + String.format("%.2f%%",
new Float(((total - count) * 100) / (double) total)) + "\"");
sb.append("}");
sb.append("]}");
return sb.toString();
}
public static String writeTotalFraudesByWin7IE9(String data_ini, String data_fin) {
return getTotalFraudesBySisNav("Windows 7", "", "Internet Explorer", "9.0", data_ini,
data_fin);
}
public static String writeSisNomeLocalidadeByFraudeId5() {
return getSisNomeLocalidadeByFraudeId("5");
}
}