public ActionResult ListJSON(string sidx, string sord, int page, int rows)
{
var columns = new Dictionary <string, string> {
{ "ID", "i" },
{ "NOMBRE", "s" },
{ "NRO_OMI", "s" },
{ "MATRICULA", "s" },
{ "SDIST", "d" },
{ "BANDERA", "s" },
{ "ORIGEN", "s" },
{ "DESTINO", "S" },
{ "FECHA_SALIDA", "d" },
{ "ACTUAL", "s" },
{ "NOTAS", "s" },
{ "ESTADO", "s" }
};
//Agregamos a mano el filtro
var tmp = JQGrid.JQGridUtils.PaginageS1("VW_VIAJES_MARITIMOS", Request.Params, columns,
page, rows, sidx, sord);
var cmdcount = new OracleCommand((string)tmp[2]);
int cnt = int.Parse(((Dictionary <string, string>)DaoLib.doSQL(cmdcount)[0])["TOTAL"]);
var cmd = new OracleCommand((string)tmp[0]);
cmd.Parameters.AddRange((OracleParameter[])tmp[1]);
var items = DaoLib.doSQL(cmd);
var coso = JQGrid.JQGridUtils.PaginateS2(items, columns, cnt, page, rows);
return(Json(coso, JsonRequestBehavior.AllowGet));
}
public ActionResult ListEtapasJSON(string sidx, string sord, int page, int rows)
{
var columns = new Dictionary <string, string> {
{ "ID", "i" },
{ "DESCRIPCION", "s" },
{ "VIAJE_ID", "i" },
{ "NRO_ETAPA", "i" },
{ "ORIGEN_DESC", "s" },
{ "DESTINO_DESC", "s" },
{ "HRP", "d" },
{ "FECHA_SALIDA", "d" },
{ "FECHA_LLEGADA", "d" },
{ "CREATED_AT", "d" }
};
var tmp = JQGrid.JQGridUtils.PaginageS1("VW_ETAPA_VIAJE", Request.Params, columns, page, rows, sidx, sord);
var cmdcount = new OracleCommand((string)tmp[2]);
int cnt = int.Parse(((Dictionary <string, string>)DaoLib.doSQL(cmdcount)[0])["TOTAL"]);
var cmd = new OracleCommand((string)tmp[0]);
cmd.Parameters.AddRange((OracleParameter[])tmp[1]);
var items = DaoLib.doSQL(cmd);
var coso = JQGrid.JQGridUtils.PaginateS2(items, columns, cnt, page, rows);
return(Json(coso, JsonRequestBehavior.AllowGet));
}
public ActionResult ListCargasJSON(string sidx, string sord, int page, int rows)
{
var columns = new Dictionary <string, string> {
{ "ID", "i" },
{ "NOMBRE", "s" },
{ "CANTIDAD", "i" },
{ "CANTIDAD_INICIAL", "i" },
{ "CANTIDAD_ENTRADA", "i" },
{ "CANTIDAD_SALIDA", "i" },
{ "UNIDAD", "s" },
{ "CODIGO", "s" },
{ "BARCAZA", "s" },
{ "ETAPA_ID", "i" }
};
var tmp = JQGrid.JQGridUtils.PaginageS1("VW_CARGA_ETAPA", Request.Params, columns, page, rows, sidx, sord);
var cmdcount = new OracleCommand((string)tmp[2]);
int cnt = int.Parse(((Dictionary <string, string>)DaoLib.doSQL(cmdcount)[0])["TOTAL"]);
var cmd = new OracleCommand((string)tmp[0]);
cmd.Parameters.AddRange((OracleParameter[])tmp[1]);
var items = DaoLib.doSQL(cmd);
var coso = JQGrid.JQGridUtils.PaginateS2(items, columns, cnt, page, rows);
return(Json(coso, JsonRequestBehavior.AllowGet));
}
public ActionResult ListJSON(string sidx, string sord, int page, int rows)
{
var columns = new Dictionary <string, string> {
{ "ID", "i" },
{ "COSTERA", "s" },
{ "NOMBRE", "s" },
{ "BANDERA", "s" },
{ "IMO", "s" },
{ "ETA", "d" },
{ "PUERTO_LLEGADA", "s" },
{ "PROCEDENCIA", "s" },
{ "NIVEL_PROTECCION_ACTUAL", "i" },
{ "CIPB_EXPIRACION", "d" }
};
//Agregamos a mano el filtro
var tmp = JQGrid.JQGridUtils.PaginageS1("VIEW_PBIP_LISTAR", Request.Params, columns,
page, rows, sidx, sord);
var cmdcount = new OracleCommand((string)tmp[2]);
int cnt = int.Parse(((Dictionary <string, string>)DaoLib.doSQL(cmdcount)[0])["TOTAL"]);
var cmd = new OracleCommand((string)tmp[0]);
cmd.Parameters.AddRange((OracleParameter[])tmp[1]);
var items = DaoLib.doSQL(cmd);
var coso = JQGrid.JQGridUtils.PaginateS2(items, columns, cnt, page, rows);
return(Json(coso, JsonRequestBehavior.AllowGet));
}
/* Visual Query Builder **************************************************/
/* ***********************************************************************/
public ActionResult Ver(int reporte_id, int count, string print_me)
{
var rep = DaoLib.reporte_obtener(reporte_id) as Dictionary <string, string>;
var _params = DaoLib.reporte_obtener_parametros(reporte_id);
var lparams = new List <OracleParameter>();
for (int i = 0; i < count; i++)
{
var param = _params.Find(o => (o as Dictionary <string, string>)["INDICE"] == (i + 1).ToString()) as Dictionary <string, string>;
object value = Request.Params["param" + (i + 1).ToString()];
string pname = ":p" + (i + 1).ToString();
string tmp = rep["CONSULTA_SQL"];
int qcount = tmp.Select((c, j) => tmp.Substring(j)).Count(sub => sub.StartsWith(pname));
for (int k = 0; k < qcount; k++)
{
if (param["TIPO_DATO"] == "0")
{
lparams.Add(new OracleParameter(pname, OracleDbType.Date, DateTime.ParseExact(value.ToString(), "dd-MM-yy", CultureInfo.InvariantCulture), System.Data.ParameterDirection.Input));
}
if (param["TIPO_DATO"] == "1")
{
lparams.Add(new OracleParameter(pname, OracleDbType.Varchar2, value, System.Data.ParameterDirection.Input));
}
if (param["TIPO_DATO"] == "2")
{
lparams.Add(new OracleParameter(pname, OracleDbType.Varchar2, value, System.Data.ParameterDirection.Input));
}
if (param["TIPO_DATO"] == "3")
{
lparams.Add(new OracleParameter(pname, OracleDbType.Varchar2, value, System.Data.ParameterDirection.Input));
}
}
}
var cmd = new OracleCommand(rep["CONSULTA_SQL"]);
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddRange(lparams.ToArray());
var rs = DaoLib.doSQL(cmd);
//Print?
if (print_me != null)
{
//StringWriter sw = new StringWriter();
////First line for column names
//var first = true;
//foreach(Dictionary<string, string> item in rs)
//{
// if (first)
// {
// string tmp = string.Empty;
// foreach(var kv in item)
// {
// if(kv.Key.EndsWith("_fmt")) continue;
// tmp = tmp + "\"" + kv.Key + "\",";
// }
// first = false;
// sw.WriteLine(tmp.Substring(0,tmp.Length-1));
// }
// string tmp2 = string.Empty;
// foreach(var kv in item)
// {
// if(kv.Key.EndsWith("_fmt")) continue;
// tmp2 = tmp2 + "\"" + kv.Value + "\",";
// }
// sw.WriteLine(tmp2.Substring(0,tmp2.Length-1));
//}
//Response.AddHeader("Content-Disposition", "attachment; filename=test.csv");
//Response.ContentType = "text/csv";
//Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
//Response.Write(sw);
//Response.End();
var ew = new mbpc.Models.ExcellWriter();
ew.Proccess(rep["NOMBRE"] + ".xls", rs, Response);
return(null);
}
if (rs.Count == 0)
{
return(View("_result_empty"));
}
ViewData["result"] = rs;
ViewData["reporte"] = rep;
return(View("_result"));
}
public ActionResult guardar(string id)
{
StringBuilder parameterCommands = new StringBuilder();
string insertParamStatement = " INTO tbl_reporte_param( reporte_id, tipo, entity, xml_id, operador, valor, orden, is_param) VALUES (:reporte_id, '{0}', '{1}', '{2}', '{3}', '{4}', {5}, {6}) ";
string insertParamStatementReporte_id = ":reporte_id";
int editing_reporte_id = 0;
if (!String.IsNullOrEmpty(id))
{
editing_reporte_id = Convert.ToInt32(id);
}
// Me guardo los nombres de las entidades y los id.
// KEY=entity_name ; VALUE=entity_id
Dictionary <string, string> entities = new Dictionary <string, string>();
// Lista de ids de entidades, temporal.
List <string> entities_id_list_tmp = new List <string>();
foreach (string item in Request.Form["entities_list"].Split(','))
{
string[] splitted_item = item.Split('=');
string entity_name = splitted_item[0];
string entity_id = splitted_item[1];
entities.Add(entity_name, entity_id);
entities_id_list_tmp.Add(splitted_item[1]);
// Comandos para insertar Entidades y su orden.
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.entidad),
entity_id,
entity_id,
"",
"",
entities_id_list_tmp.Count.ToString(),
0.ToString()
);
}
// Lista de entidades, para indexarlas por orden de llegada en los params del post.
string[] entities_id = entities_id_list_tmp.ToArray();
entities_id_list_tmp = null;
// Lista de cantidad de condiciones (where clauses) por entidad. Key=entity_<INDEX> -> como el html se arma dinamico, indizo por valor, para poder recuperarlos luego.
Dictionary <string, int> conditions_by_entity_count = new Dictionary <string, int>();
foreach (string item in Request.Form["conditions_by_entity_count"].Split(','))
{
string[] splitted_item = item.Split('=');
conditions_by_entity_count.Add(splitted_item[0], Convert.ToInt32(splitted_item[1]));
}
// WHERE clause.
StringBuilder strConditions = new StringBuilder();
XmlDocument xmlDoc = openSQLConfig();
Dictionary <string, Dictionary <string, string> > reporteParams = new Dictionary <string, Dictionary <string, string> >();
int index = 0;
int paramCount = 1;
int total_condition_count = 0;
foreach (KeyValuePair <string, int> pair in conditions_by_entity_count)
{
int current_snorbol = 1;
int condition_count = 0;
while (condition_count < pair.Value)
{
if (Request.Form["conditionitem-attribute_" + entities_id[index] + "_" + current_snorbol.ToString()] != null)
{
string attr = Request.Form["conditionitem-attribute_" + entities_id[index] + "_" + current_snorbol.ToString()];
string oper = Request.Form["conditionitem-operator_" + entities_id[index] + "_" + current_snorbol.ToString()];
string value = Request.Form["conditionitem-value_" + entities_id[index] + "_" + current_snorbol.ToString()];
string is_param = Request.Form["conditionitem-isparam_" + entities_id[index] + "_" + current_snorbol.ToString()];
string entity_id = entities_id[index];
string type = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", attr)).Attributes.GetNamedItem("type").Value.Trim();
string sql = "";
string sql_column = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", attr)).Attributes.GetNamedItem("sql_column").Value.Trim();
if (type != "hardcoded")
{
string value_format = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/operators/operator[@type='{0}']", type)).Attributes.GetNamedItem("format").Value.Trim();
if (is_param != null && is_param.Equals("on"))
{
value = string.Format(":p{0}", paramCount.ToString());
string name = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", attr)).Attributes.GetNamedItem("name").Value.Trim();
Dictionary <string, string> paramData = new Dictionary <string, string>();
paramData.Add("data_type", Convert.ToInt32(Enum.Parse(typeof(ReporteParamDataType), type.ToUpper())).ToString());
paramData.Add("name", Models.Hlp.GenerateSlug(name));
reporteParams.Add(value, paramData);
string oper_format = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/operators/operator/oper[@id='{0}']", oper)).Attributes.GetNamedItem("format").Value.Trim();
sql = oper_format.Replace("$c", sql_column);
sql = sql.Replace("$v", value);
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.where),
entity_id,
attr,
oper,
"", //value
(condition_count + 1).ToString(),
1.ToString()
);
paramCount++;
}
else
{
string formatted_value = value;
if (type == "string")
{
value = value.Replace("'", "''");
formatted_value = String.Format(value_format, value);
}
//if (!String.IsNullOrEmpty(value_format) && !String.IsNullOrEmpty(value))
// value = String.Format(value_format, value);
string oper_format = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/operators/operator/oper[@id='{0}']", oper)).Attributes.GetNamedItem("format").Value.Trim();
sql = oper_format.Replace("$c", sql_column);
sql = sql.Replace("$v", formatted_value);
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.where),
entity_id,
attr,
oper,
value,
(condition_count + 1).ToString(),
0.ToString()
);
}
}
else
{
value = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", attr)).Attributes.GetNamedItem("value").Value.Trim();
//sql = string.Format(" {0} = {1} ", sql_column, value);
sql = string.Format(" {0} ", value);
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.where),
entity_id,
attr,
"",
"",
(condition_count + 1).ToString(),
0.ToString()
);
}
if (!String.IsNullOrEmpty(sql))
{
if (String.IsNullOrEmpty(strConditions.ToString()))
{
strConditions.AppendFormat(" WHERE ");
}
strConditions.AppendFormat(" {0} {1} ", (total_condition_count > 0 ? "AND" : ""), sql);
total_condition_count++; // -> *Cuando pones condiciones de dos entidades distintas, falta un AND entre medio
}
condition_count++;
}
current_snorbol++;
}
index++;
}
// SELECT clause.
int resultfields_count = Convert.ToInt32(Request.Form["resultfields_count"]);
StringBuilder strSelect = new StringBuilder();
strSelect.AppendFormat(" SELECT ");
index = 0;
int my_resultfields_count = 0;
int current_snajdarg = 1;
while (my_resultfields_count < resultfields_count)
{
if (Request.Form["resultcolumn-field_" + current_snajdarg.ToString()] != null)
{
string field = Request.Form["resultcolumn-field_" + current_snajdarg.ToString()];
string value = Request.Form["resultcolumn-value_" + current_snajdarg.ToString()];
//if (!String.IsNullOrEmpty(value))
// value = Models.Hlp.GenerateSlug(value) ;//value.Replace(" ", "_");
string entity_name = field.Split('.')[0];
string entity_attribute_id = field.Split('.')[1];
string the_field = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", entity_attribute_id)).Attributes.GetNamedItem("sql_column").Value.Trim();
string sql = string.Format("{0} {1}", the_field, String.IsNullOrEmpty(value) ? "" : string.Format(" as \"{0}\"", value));
strSelect.AppendFormat(" {0} {1} ", (my_resultfields_count > 0?", ":""), sql);
my_resultfields_count++;
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.select),
entities[entity_name],
entity_attribute_id,
"",
value,
my_resultfields_count.ToString(),
0.ToString()
);
}
current_snajdarg++;
index++;
}
strSelect.AppendFormat(" FROM ");
Dictionary <string, string> relations = new Dictionary <string, string>();
int fromIndex = 0;
foreach (string key in entities.Keys)
{
string entity = key;//entities[key];
string sql = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity[@name='{0}']/sql", entity)).InnerText.Trim();
foreach (XmlNode relation in xmlDoc.SelectNodes(string.Format("/sqlbuilder/entities/entity[@name='{0}']/relations/relation", entity)))
{
string target = relation.Attributes.GetNamedItem("target").Value.Trim();
if (!relations.ContainsKey(target))
{
relations.Add(target, relation.ChildNodes[0].InnerText.Trim());
}
}
if (fromIndex == 0)
{
strSelect.AppendFormat(" {0} ", sql);
}
else
{
strSelect.AppendFormat(" {0} ", relations[entity]);
}
fromIndex++;
}
// Order.
int orderfields_count = Convert.ToInt32(Request.Form["orderfields_count"]);
StringBuilder strOrder = new StringBuilder();
index = 0;
int my_orderfields_count = 0;
current_snajdarg = 1;
while (my_orderfields_count < orderfields_count)
{
if (Request.Form["ordercolumn-field_" + current_snajdarg.ToString()] != null)
{
string field = Request.Form["ordercolumn-field_" + current_snajdarg.ToString()];
string sort = Request.Form["ordercolumn-value_" + current_snajdarg.ToString()];
string entity_name = field.Split('.')[0];
string entity_attribute_id = field.Split('.')[1];
string the_field = xmlDoc.SelectSingleNode(string.Format("/sqlbuilder/entities/entity/attributes/attribute[@id='{0}']", entity_attribute_id)).Attributes.GetNamedItem("sql_column").Value.Trim();
if (index == 0)
{
strOrder.AppendFormat(" ORDER BY ");
}
strOrder.AppendFormat(" {0} {1} {2} ", (my_orderfields_count > 0 ? "," : ""), the_field, sort);
my_orderfields_count++;
parameterCommands.AppendFormat(insertParamStatement,
Convert.ToString(Tipo.order),
entities[entity_name],
entity_attribute_id,
"",
sort,
my_orderfields_count.ToString(),
0.ToString()
);
}
current_snajdarg++;
index++;
}
closeXml();
xmlDoc = null;
System.GC.Collect();
string reporte_sql = strSelect.ToString() + strConditions.ToString() + strOrder.ToString();
ViewData["response"] = reporte_sql;
// Parametros que van derecho al datastore.
string serialized_form = Request.Form["serialized_form"];
string nombre_reporte = Request.Form["nombre_reporte"];
List <object> res = null;
int lastReportId = editing_reporte_id;
if (editing_reporte_id > 0)
{
//res = DaoLib.reporte_actualizar(editing_reporte_id, nombre_reporte, nombre_reporte, 1, reporte_sql, serialized_form, "", json_form);
res = DaoLib.reporte_actualizar(editing_reporte_id, nombre_reporte, nombre_reporte, 1, reporte_sql, serialized_form);
}
else
{
res = DaoLib.reporte_insertar(nombre_reporte, nombre_reporte, 1, reporte_sql, serialized_form);
Dictionary <string, string> resDict = ((res[0]) as Dictionary <string, string>);
lastReportId = Convert.ToInt32(resDict[resDict.Keys.ElementAt(0)]);
}
List <int> reporte_id = new List <int>();
List <int> indice = new List <int>();
List <int> tipo_dato = new List <int>();
List <string> nombre = new List <string>();
index = 1;
foreach (KeyValuePair <string, Dictionary <string, string> > data in reporteParams)
{
reporte_id.Add(lastReportId);
indice.Add(index);
tipo_dato.Add(Convert.ToInt32(data.Value["data_type"]));
nombre.Add(data.Value["name"]);
index++;
}
if (editing_reporte_id > 0)
{
DaoLib.reporte_eliminar_params(lastReportId);
}
if (nombre.Count > 0)
{
List <object> res2 = DaoLib.reporte_insertar_params(reporte_id.ToArray(), indice.ToArray(), nombre.ToArray(), tipo_dato.ToArray());
}
parameterCommands.Replace(insertParamStatementReporte_id, lastReportId.ToString());
var cmd = new OracleCommand(string.Format("INSERT ALL {0} SELECT * FROM dual", parameterCommands.ToString()));
cmd.CommandType = System.Data.CommandType.Text;
var rs = DaoLib.doSQL(cmd);
return(this.RedirectToAction("editar", "Reporte", new { id = lastReportId.ToString() }));
//return View();
}