public decimal FieldDecimal(qGen.Select selectCommand)
{
selectCommand.SqlMode = this.SqlMode;
object Res = this.ExecuteScalar(selectCommand.ToString());
if (Res == null || Res is DBNull)
return 0;
else
return System.Convert.ToDecimal(Res);
}
public Lfx.Data.Row FirstRowFromSelect(qGen.Select selectCommand)
{
selectCommand.SqlMode = this.SqlMode;
return this.FirstRowFromSelect(selectCommand.ToString());
}
public int Execute(qGen.Command sqlCommand)
{
if (this.ReadOnly)
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
if (sqlCommand is qGen.Update || sqlCommand is qGen.Insert || sqlCommand is qGen.Delete) {
if (this.RequiresTransaction && this.m_InTransaction == false)
throw new InvalidOperationException("Comando fuera una transacción: " + sqlCommand);
}
sqlCommand.SqlMode = this.SqlMode;
using (System.Data.IDbCommand Cmd = this.GetCommand(sqlCommand)) {
return this.ExecuteNonQuery(Cmd);
}
}
public string FieldString(qGen.Select selectCommand)
{
selectCommand.SqlMode = this.SqlMode;
object Res = this.ExecuteScalar(selectCommand.ToString());
if (Res == null || Res is DBNull)
return null;
else
return Res.ToString();
}
protected qGen.Select SelectCommand(bool forCount, qGen.Where additionalFilters)
{
return this.SelectCommand("COUNT", null, additionalFilters);
}
/// <summary>
/// Exporta una tabla a un archivo de texto con una secuencia de comandos SQL.
/// </summary>
public void ExportTable(qGen.Select Comando, bool ExportarBlobs, System.IO.StreamWriter writer)
{
System.Data.DataTable Tabla = Lfx.Workspace.Master.MasterConnection.Select(Comando);
string NombresCampos = null;
// Hago un dump de los campos
foreach (System.Data.DataColumn Campo in Tabla.Columns) {
if (NombresCampos == null)
NombresCampos = Campo.ColumnName;
else
NombresCampos += ", " + Campo.ColumnName;
}
foreach (System.Data.DataRow Registro in Tabla.Rows) {
string Valores = "";
foreach (System.Data.DataColumn Campo in Tabla.Columns) {
string Valor = "";
if (Registro[Campo.ColumnName] == DBNull.Value || Registro[Campo.ColumnName] == null) {
Valor = "NULL";
} else {
switch (Campo.DataType.Name) {
case "Byte[]":
if (ExportarBlobs) {
// FIXME: exportar BLOBS
} else {
Valor = "NULL";
}
break;
case "SByte":
case "Byte":
case "Int16":
case "Int32":
case "Int64":
Valor = Registro[Campo.ColumnName].ToString();
break;
case "Single":
case "Double":
Valor = System.Convert.ToDouble(Registro[Campo.ColumnName]).ToString(System.Globalization.CultureInfo.InvariantCulture);
break;
case "Decimal":
Valor = System.Convert.ToDecimal(Registro[Campo.ColumnName]).ToString(System.Globalization.CultureInfo.InvariantCulture);
break;
case "DateTime":
Valor = "'" + Lfx.Types.Formatting.FormatDateTimeSql(System.Convert.ToDateTime(Registro[Campo.ColumnName])) + "'";
break;
case "String":
Valor = "'" + Lfx.Workspace.Master.MasterConnection.EscapeString(System.Convert.ToString(Registro[Campo.ColumnName])).Replace("\r", @"\r").Replace("\n", @"\n") + "'";
break;
default:
Lfx.Workspace.Master.RunTime.Toast("No se puede restaurar campo tipo " + Campo.DataType.Name, "Error");
Valor = "'" + Lfx.Workspace.Master.MasterConnection.EscapeString(Registro[Campo.ColumnName].ToString()) + "'";
break;
}
}
//Quito el primer ", "
Valores += ", " + Valor;
}
Valores = Valores.Substring(2, Valores.Length - 2);
writer.WriteLine("$INSERTORREPLACE$ " + Comando.Tables + " (" + NombresCampos + ") VALUES (" + Valores + ")" + ";");
}
writer.WriteLine("");
writer.WriteLine("");
return;
}
public System.Data.IDbCommand GetCommand(qGen.Command command)
{
System.Data.IDbCommand TempCommand = Lfx.Data.DataBaseCache.DefaultCache.Provider.GetCommand();
TempCommand.Connection = this.DbConnection;
command.SetupDbCommand(ref TempCommand);
return TempCommand;
}
public static string EscapeString(string stringValue, qGen.SqlModes sqlMode)
{
switch (sqlMode) {
case qGen.SqlModes.PostgreSql:
return stringValue.Replace("'", "''");
case qGen.SqlModes.MySql:
default:
return stringValue.Replace("'", "''").Replace(@"\", @"\\");
}
}
public RelationFilter(string label, Lfx.Data.Relation relation, qGen.Where filter)
: this(label, relation)
{
this.Filter = filter;
}
public Reporte2(Lfx.Data.Connection dataBase, qGen.Select selectCommand)
{
this.DataBase = dataBase;
this.SelectCommand = selectCommand;
}
public string ToString(qGen.SqlModes mode)
{
m_Mode = mode;
return this.ToString();
}
public ComparisonCondition(qGen.SqlModes Mode, string LeftValue, qGen.ComparisonOperators compOperator, object RightValue)
: this(LeftValue, compOperator, RightValue)
{
m_Mode = Mode;
}
public ComparisonCondition(string leftValue, qGen.ComparisonOperators compOperator, object rightValue)
: this()
{
this.LeftValue = leftValue;
this.Operator = compOperator;
this.m_RightValue = rightValue;
}
public System.Data.IDataReader GetReader(qGen.Select comando)
{
comando.SqlMode = this.SqlMode;
return this.GetReader(comando.ToString());
}
public int Update(qGen.Update updateCommand)
{
if (this.ReadOnly)
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
updateCommand.SqlMode = this.SqlMode;
if (this.IsOpen() == false)
this.Open();
using (System.Data.IDbCommand TempCommand = this.GetCommand(updateCommand)) {
while (true) {
try {
TempCommand.Connection = this.DbConnection;
this.ResetKeepAliveTimer();
int Res = TempCommand.ExecuteNonQuery();
return Res;
} catch (Exception ex) {
if (this.TryToRecover(ex)) {
ex.Data.Add("Command", updateCommand.ToString());
throw ex;
}
}
}
}
}
public System.Data.DataTable Select(qGen.Select selectCommand)
{
selectCommand.SqlMode = this.SqlMode;
return Select(selectCommand.ToString());
}
public int Delete(qGen.Delete deleteCommand)
{
if (this.ReadOnly)
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
deleteCommand.SqlMode = this.SqlMode;
if (this.IsOpen() == false)
this.Open();
return this.ExecuteSql(deleteCommand.ToString());
}
protected void Fill(qGen.Select command)
{
if (this.Listado.Columns.Count == 0)
this.SetupListviewColumns();
CancelFill = false;
if (this.Connection == null || command == null)
return;
if (command.Window == null) {
if (Lfx.Workspace.Master.SlowLink)
command.Window = new qGen.Window(1000 > m_Limit ? 1000 : m_Limit);
else if (m_Limit > 0)
command.Window = new qGen.Window(m_Limit);
else
command.Window = null;
}
System.Data.DataTable Tabla = this.Connection.Select(command);
ListViewItem CurItem = null;
if (Listado.SelectedItems.Count > 0)
CurItem = (ListViewItem)Listado.SelectedItems[0].Clone();
else
CurItem = null;
bool Actualizando;
if (ItemListado.Count > 0) {
Actualizando = true;
EtiquetaCantidad.Text = "Actualizando...";
} else {
Actualizando = false;
//Listado.Items.Clear();
EtiquetaCantidad.Text = "Cargando...";
}
PicEsperar.Visible = true;
EtiquetaCantidad.Refresh();
PicEsperar.Refresh();
Listado.ListViewItemSorter = null;
Listado.SuspendLayout();
Listado.BeginUpdate();
foreach (ListViewItem Itm in ItemListado.Values) {
Itm.Tag = null;
}
if (Tabla != null && Tabla.Rows.Count > 0) {
int FillCount = 0;
foreach (System.Data.DataRow DtRow in Tabla.Rows) {
Lfx.Data.Row Registro = (Lfx.Data.Row)DtRow;
string NombreCampoId = Lfx.Data.Field.GetNameOnly(this.Definicion.KeyColumn.Name);
int ItemId = Registro.Fields[NombreCampoId].ValueInt;
if (CancelFill) {
if (Listado.Created) {
Listado.EndUpdate();
Listado.ResumeLayout();
}
return;
}
ListViewItem Itm;
string ItemKey = ItemId.ToString();
if (ItemListado.ContainsKey(ItemId)) {
Itm = this.FormatListViewItem(ItemListado[ItemId], ItemId, Registro);
if (Listado.Items.Contains(Itm) == false)
Listado.Items.Add(Itm);
} else {
Itm = this.FormatListViewItem(new ListViewItem(ItemKey), ItemId, Registro);
ItemListado.Add(ItemId, Itm);
Listado.Items.Add(Itm);
}
Itm.Tag = Registro;
// Agrego el item a un grupo, si hay agrupación activa
if (m_GroupingColumnName != null) {
string FormattedGroupingValue = this.FormatValue(Registro[m_GroupingColumnName], this.Definicion.Columns[m_GroupingColumnName]);
if (LastGroupingValue != FormattedGroupingValue) {
LastGroupingValue = FormattedGroupingValue;
if (Listado.Groups[LastGroupingValue] != null)
LastGroup = Listado.Groups[LastGroupingValue];
else
LastGroup = Listado.Groups.Add(LastGroupingValue, LastGroupingValue);
}
}
Itm.Group = LastGroup;
OnItemAdded(Itm, Registro);
if (CurItem != null && Itm.Text == CurItem.Text)
CurItem = Itm;
if ((++FillCount % 100) == 0) {
// Cuando ya tengo algunos como para mostrar, actualizo el listview
// así parece que el listado ya cargó, cuando en realidad sigue cargando
double Pct = Math.Round(FillCount * 100D / Tabla.Rows.Count);
if (Actualizando) {
EtiquetaCantidad.Text = "Actualizando, " + Pct.ToString() + "%";
System.Windows.Forms.Application.DoEvents();
} else {
EtiquetaCantidad.Text = "Cargando, " + Pct.ToString() + "%";
Listado.EndUpdate();
Listado.ResumeLayout(true);
System.Windows.Forms.Application.DoEvents();
Listado.BeginUpdate();
Listado.SuspendLayout();
}
}
}
}
foreach (ListViewItem Itm in Listado.Items) {
if (Itm.Tag == null) {
Listado.Items.Remove(Itm);
}
}
if (Listado.Items.Count > 0 && Listado.SelectedItems.Count == 0) {
Listado.Items[0].Focused = true;
Listado.Items[0].Selected = true;
}
if (Listado.Items.Count == 0) {
EtiquetaCantidad.Text = "";
EtiquetaCantidad.TextStyle = Lazaro.Pres.DisplayStyles.TextStyles.Default;
} else if (Listado.Items.Count == m_Limit) {
int Cantidad;
try {
qGen.Select SelCount = this.SelectCommand(true, null);
Cantidad = this.Connection.FieldInt(SelCount);
} catch {
Cantidad = 0;
}
if (Cantidad > 0)
EtiquetaCantidad.Text = "Mostrando sólo los primeros " + m_Limit.ToString() + " de un total de " + Cantidad.ToString();
else
EtiquetaCantidad.Text = "Mostrando sólo los primeros " + m_Limit.ToString();
EtiquetaCantidad.TextStyle = Lazaro.Pres.DisplayStyles.TextStyles.Small;
} else {
EtiquetaCantidad.Text = Listado.Items.Count.ToString() + " elementos";
EtiquetaCantidad.TextStyle = Lazaro.Pres.DisplayStyles.TextStyles.Default;
}
// Muestro los totales de grupo
if (m_GroupingColumnName != null) {
foreach (ListViewGroup Grp in Listado.Groups) {
Grp.Header = Grp.Name + " (" + Grp.Items.Count.ToString() + ")";
ListViewItem Itm = Listado.Items.Add("Subtotales");
Itm.Font = new Font(Itm.Font, FontStyle.Bold);
Itm.BackColor = Color.Lavender;
foreach (ColumnHeader Col in Listado.Columns) {
if (Col.Name != this.Definicion.KeyColumn.Name) {
Itm.Group = Grp;
object ColFunc = this.Definicion.Columns[Col.Name].TotalFunction;
if (ColFunc == null) {
Itm.SubItems.Add("");
} else if (ColFunc is Lazaro.Pres.Spreadsheet.QuickFunctions) {
Lazaro.Pres.Spreadsheet.QuickFunctions QuickFunc = (Lazaro.Pres.Spreadsheet.QuickFunctions)(this.Definicion.Columns[Col.Name].TotalFunction);
switch (QuickFunc) {
case Lazaro.Pres.Spreadsheet.QuickFunctions.Count:
Itm.SubItems.Add(Grp.Items.Count.ToString());
break;
case Lazaro.Pres.Spreadsheet.QuickFunctions.Sum:
decimal Res = 0;
foreach (ListViewItem SubItm in Grp.Items) {
Lfx.Data.Row Rw = SubItm.Tag as Lfx.Data.Row;
if (Rw != null)
Res += Rw.Fields[Col.Name].ValueDecimal;
}
Itm.SubItems.Add(Lfx.Types.Formatting.FormatCurrency(Res));
break;
default:
Itm.SubItems.Add("");
break;
}
} else {
Itm.SubItems.Add(ColFunc.ToString());
}
}
}
}
}
if (this.Sorter != null && this.Sorter.SortOrder != SortOrder.None) {
Listado.ListViewItemSorter = this.Sorter;
Listado.Sort();
}
Listado.EndUpdate();
Listado.ResumeLayout();
PicEsperar.Visible = false;
if (CurItem != null) {
CurItem.Selected = true;
CurItem.Focused = true;
CurItem.EnsureVisible();
}
CancelFill = true;
}
public int Insert(qGen.Insert insertCommand)
{
if (this.ReadOnly)
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
insertCommand.SqlMode = this.SqlMode;
if (this.IsOpen() == false)
this.Open();
if (Lfx.Workspace.Master.TraceMode)
Lfx.Workspace.Master.DebugLog(this.Handle, insertCommand.ToString());
System.Data.IDbCommand TempCommand = this.GetCommand(insertCommand);
try {
return TempCommand.ExecuteNonQuery();
} catch (Exception ex) {
this.LogError("----------------------------------------------------------------------------");
this.LogError(ex.Message);
this.LogError(TempCommand.CommandText);
throw ex;
}
}
protected qGen.Select SelectCommand(string agrFunction, string onField, qGen.Where additionalFilters)
{
if (this.Connection != null && this.Definicion != null && this.Definicion.TableName != null) {
qGen.Select ComandoSelect = new qGen.Select(this.Connection.SqlMode);
// Genero la lista de tablas, con JOIN y todo
string ListaTablas = null;
ListaTablas = this.Definicion.TableName;
if (this.Definicion.Joins != null && this.Definicion.Joins.Count > 0)
ComandoSelect.Joins = this.Definicion.Joins;
foreach (Lazaro.Pres.Field Fld in this.Definicion.Columns) {
if (Fld.DataType == Lfx.Data.InputFieldTypes.Relation && Fld.Relation != null) {
qGen.Join RelJoin = new qGen.Join(Fld.Relation);
if (ComandoSelect.Joins.Contains(RelJoin) == false)
ComandoSelect.Joins.Add(RelJoin);
}
}
string ListaCampos;
if (agrFunction != null) {
if (onField == null)
onField = this.Definicion.KeyColumn.Name;
string Alias = agrFunction + "_" + onField.Replace(".", "_");
ListaCampos = agrFunction + "(" + onField + ") AS " + Alias;
} else {
// Genero la lista de campos
ListaCampos = this.Definicion.KeyColumn.Name;
foreach (Lazaro.Pres.Field CurField in this.Definicion.Columns)
ListaCampos += "," + CurField.Name;
}
// Genero las condiciones del WHERE
qGen.Where WhereBuscarTexto = new qGen.Where();
WhereBuscarTexto.Operator = qGen.AndOr.Or;
if (this.SearchText != string.Empty) {
bool EsNumero = this.SearchText.IsNumericInt() || this.SearchText.IsNumericFloat();
if (this.SearchText.IsNumericInt())
WhereBuscarTexto.AddWithValue(this.Definicion.KeyColumn.Name, Lfx.Types.Parsing.ParseInt(this.SearchText).ToString());
if (this.Definicion.Columns != null) {
foreach (Lazaro.Pres.Field CurField in this.Definicion.Columns) {
if (CurField.Name.IndexOf(" AS ") == -1 && CurField.Name.IndexOf("(") == -1) {
switch (CurField.DataType) {
case Lfx.Data.InputFieldTypes.Binary:
case Lfx.Data.InputFieldTypes.Image:
case Lfx.Data.InputFieldTypes.Bool:
case Lfx.Data.InputFieldTypes.Set:
// En estos tipos de campos no se busca
break;
case Lfx.Data.InputFieldTypes.Date:
case Lfx.Data.InputFieldTypes.DateTime:
// En estos campos, busco atento a que se trata de una fecha
NullableDateTime Fecha = SearchText.ParseDateTime(); // TODO: cachear el valor para que no lo parsee en cada iteración
if (Fecha != null) {
DateTime SearchDate = Fecha.Value;
DateTime FromDate = new DateTime(SearchDate.Year, SearchDate.Month, SearchDate.Day, 0, 0, 0);
DateTime ToDate = new DateTime(SearchDate.Year, SearchDate.Month, SearchDate.Day, 23, 59, 59);
WhereBuscarTexto.AddWithValue(CurField.Name, FromDate, ToDate);
}
break;
case Lfx.Data.InputFieldTypes.Currency:
case Lfx.Data.InputFieldTypes.Integer:
case Lfx.Data.InputFieldTypes.Numeric:
case Lfx.Data.InputFieldTypes.NumericSet:
case Lfx.Data.InputFieldTypes.Serial:
// En estos tipos de campos busco sólo números
if (EsNumero)
WhereBuscarTexto.AddWithValue(CurField.Name, qGen.ComparisonOperators.InsensitiveLike, "%" + this.SearchText + "%");
break;
case Lfx.Data.InputFieldTypes.Relation:
default:
WhereBuscarTexto.AddWithValue(CurField.Name, qGen.ComparisonOperators.InsensitiveLike, "%" + this.SearchText + "%");
break;
}
}
}
}
if (this.Definicion.ExtraSearchColumns != null) {
foreach (Lazaro.Pres.Field CurField in this.Definicion.ExtraSearchColumns) {
switch (CurField.DataType) {
case Lfx.Data.InputFieldTypes.Binary:
case Lfx.Data.InputFieldTypes.Image:
// En estos tipos de campos no se busca
break;
case Lfx.Data.InputFieldTypes.Currency:
case Lfx.Data.InputFieldTypes.Integer:
case Lfx.Data.InputFieldTypes.Numeric:
case Lfx.Data.InputFieldTypes.NumericSet:
case Lfx.Data.InputFieldTypes.Serial:
// En estos tipos de campos busco sólo números
if (EsNumero)
WhereBuscarTexto.AddWithValue(CurField.Name, qGen.ComparisonOperators.InsensitiveLike, "%" + this.SearchText + "%");
break;
case Lfx.Data.InputFieldTypes.Relation:
default:
WhereBuscarTexto.AddWithValue(CurField.Name, qGen.ComparisonOperators.InsensitiveLike, "%" + this.SearchText + "%");
break;
}
}
}
}
qGen.Where WhereCompleto = new qGen.Where();
WhereCompleto.Operator = qGen.AndOr.And;
if (m_Labels != null) {
if (m_LabelField == null || m_LabelField.Length == 0)
m_LabelField = this.Definicion.KeyColumn.Name;
if (m_Labels.Count == 1) {
// Ids negativos sólo cuando hay una sola etiqueta
if (m_Labels[0] > 0)
WhereCompleto.AddWithValue(m_LabelField, qGen.ComparisonOperators.In, new qGen.SqlExpression("(SELECT item_id FROM sys_labels_values WHERE id_label=" + m_Labels[0].ToString() + ")"));
else
WhereCompleto.AddWithValue(m_LabelField, qGen.ComparisonOperators.NotIn, new qGen.SqlExpression("(SELECT item_id FROM sys_labels_values WHERE id_label=" + (-m_Labels[0]).ToString() + ")"));
} else if (m_Labels.Count > 1) {
string[] LabelsString = Array.ConvertAll<int, string>(m_Labels.ToArray(), new Converter<int, string>(Convert.ToString));
WhereCompleto.AddWithValue(m_LabelField, qGen.ComparisonOperators.In, new qGen.SqlExpression("(SELECT item_id FROM sys_labels_values WHERE id_label IN (" + string.Join(",", LabelsString) + "))"));
}
}
if (WhereBuscarTexto.Count > 0)
WhereCompleto.AddWithValue(WhereBuscarTexto);
if (m_CustomFilters != null && m_CustomFilters.Count > 0)
WhereCompleto.AddWithValue(m_CustomFilters);
if (m_FixedFilters != null && m_FixedFilters.Count > 0)
WhereCompleto.AddWithValue(m_FixedFilters);
if (additionalFilters != null && additionalFilters.Count > 0)
WhereCompleto.AddWithValue(additionalFilters);
ComandoSelect.Tables = ListaTablas;
ComandoSelect.Fields = ListaCampos;
ComandoSelect.WhereClause = WhereCompleto;
if (this.Definicion.GroupBy != null && agrFunction == null)
ComandoSelect.Group = this.Definicion.GroupBy.Name;
if (this.Definicion.Having != null)
ComandoSelect.HavingClause = this.Definicion.Having;
if (agrFunction == null)
ComandoSelect.Order = this.Definicion.OrderBy;
return ComandoSelect;
} else {
return null;
}
}
/// <summary>
/// Exporta los campos binarios de una tabla en archivos.
/// </summary>
public void ExportBlobs(qGen.Select ComandoSelect, string Carpeta)
{
if (char.Parse(Carpeta.Substring(Carpeta.Length - 1, 1)) != System.IO.Path.DirectorySeparatorChar)
Carpeta += System.Convert.ToString(System.IO.Path.DirectorySeparatorChar);
using (System.Data.DataTable Tabla = Lfx.Workspace.Master.MasterConnection.Select(ComandoSelect.ToString())) {
foreach (System.Data.DataRow Registro in Tabla.Rows) {
foreach (System.Data.DataColumn Campo in Tabla.Columns) {
if (Campo.DataType.Name == "Byte[]" && Registro[Campo.ColumnName] != null && ((byte[])(Registro[Campo.ColumnName])).Length > 5) {
byte[] Contenido = ((byte[])(Registro[Campo.ColumnName]));
string NombreArchivo = ComandoSelect.Tables + "_" + Campo.ColumnName + "_" + Registro[0].ToString() + ".blb";
using (System.IO.FileStream Archivo = new System.IO.FileStream(Carpeta + NombreArchivo, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write)) {
Archivo.Write(Contenido, 0, Contenido.Length);
Archivo.Close();
}
using (System.IO.FileStream Archivo = new System.IO.FileStream(Carpeta + "blobs.lst", System.IO.FileMode.Append, System.IO.FileAccess.Write)) {
System.IO.StreamWriter Escribidor = new System.IO.StreamWriter(Archivo);
Escribidor.WriteLine(ComandoSelect.Tables + "," + Campo.ColumnName + "," + Tabla.Columns[0].ColumnName + "='" + Registro[0].ToString() + "'," + NombreArchivo);
Escribidor.Close();
Archivo.Close();
}
}
}
}
}
}
