/// <summary>
/// Retorna un listado con los documentos de instancia XBRL disponibles para ser descargados.
/// </summary>
/// <returns>Lista de objetos de tipo DocumentoInstanciaDto.</returns>
public IList <DocumentoInstanciaDto> ObtenEnviosDocumentoInstancia()
{
var query = "SELECT di.* " +
"FROM DocumentoInstancia di LEFT JOIN ArchivoDocumentoInstancia adi ON di.IdDocumentoInstancia = adi.IdDocumentoInstancia, " +
"(SELECT MAX(FechaCreacion) FechaCreacion, IdEmpresa, " +
"ISNULL(NumFideicomiso, 'NULL') NumFideicomiso, ISNULL(EspacioNombresPrincipal, 'NULL') EspacioNombresPrincipal, " +
"FechaReporte " +
"FROM DocumentoInstancia " +
"GROUP BY IdEmpresa, NumFideicomiso, EspacioNombresPrincipal, FechaReporte " +
") dmax " +
"WHERE di.IdEmpresa = dmax.IdEmpresa " +
"AND ((di.NumFideicomiso IS NULL AND dmax.NumFideicomiso = 'NULL') OR (di.NumFideicomiso IS NOT NULL AND di.NumFideicomiso = dmax.NumFideicomiso)) " +
"AND di.EspacioNombresPrincipal = dmax.EspacioNombresPrincipal " +
"AND di.FechaReporte = dmax.FechaReporte " +
"AND di.EspacioNombresPrincipal IS NOT NULL " +
"AND adi.IdTipoArchivo = 5 " +
"ORDER BY di.FechaReporte, di.EspacioNombresPrincipal, di.ClaveEmisora, di.NumFideicomiso";
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate <DocumentoInstanciaDto>(CommandType.Text, query,
delegate(IDataReader reader, int rowNum) { return(CrearDocumentoInstancia(reader, rowNum)); },
builder.GetParameters());
return(documentosInstancia.ToList());
}
public IList <ICodeValueVO> RetrieveCommonCodesByCodeSet(string codeSetCode)
{
if (!String.IsNullOrEmpty(codeSetCode))
{
string query = RETRIEVE_CODE_VALUE_BY_CODE_SET + SORT_CODE_VALUE;
return((IList <ICodeValueVO>)
AdoTemplate.QueryWithRowMapperDelegate(
CommandType.Text,
query,
delegate(IDataReader dataReader, int rowNum)
{
ICodeValueVO codeValue = new CodeValueVO();
codeValue.Id = dataReader.GetSafeInt32("id");
codeValue.Code = dataReader.GetSafeString("code");
codeValue.Description = dataReader.GetSafeString("code_value");
return codeValue;
},
"CodeSetCode",
DbType.String,
50,
codeSetCode
));
}
return(null);
}
public IList <ICodeValueVO> RetrieveCommonCodesByCodeSetAndRefCode(string codeSetCode, string referenceCode)
{
if (!String.IsNullOrEmpty(codeSetCode) && !String.IsNullOrEmpty(referenceCode))
{
string query = RETRIEVE_CODE_VALUE_BY_CODE_SET
+ " AND cv.code_value_parent_code = @ReferenceCode "
+ SORT_CODE_VALUE;
IDbParametersBuilder builder = CreateDbParametersBuilder();
builder.Create().Name("CodeSetCode").Type(DbType.String).Size(50).Value(codeSetCode);
builder.Create().Name("ReferenceCode").Type(DbType.String).Size(50).Value(referenceCode);
return((IList <ICodeValueVO>)
AdoTemplate.QueryWithRowMapperDelegate(
CommandType.Text,
query,
delegate(IDataReader dataReader, int rowNum)
{
ICodeValueVO codeValue = new CodeValueVO();
codeValue.Id = dataReader.GetSafeInt32("id");
codeValue.Code = dataReader.GetSafeString("code");
codeValue.Description = dataReader.GetSafeString("code_value");
return codeValue;
},
builder.GetParameters()
));
}
return(null);
}
/// <summary>
/// Retorna un listado con los documentos de instancia XBRL disponibles para ser descargados.
/// </summary>
/// <returns>Lista de objetos de tipo DocumentoInstanciaDto.</returns>
public IList <DocumentoInstanciaDto> ObtenEnviosDocumentoInstancia()
{
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate <DocumentoInstanciaDto>(CommandType.Text, QUERY_RSS_FILES,
delegate(IDataReader reader, int rowNum) { return(CrearDocumentoInstancia(reader, rowNum)); },
builder.GetParameters());
return(documentosInstancia.ToList());
}
public virtual IList <ICallReportVO> RetrieveCallReportTaskList(int loginId, bool isInitiator)
{
if (loginId != 0)
{
string query = RETRIEVE_CALL_REPORT_TASK_LIST;
if (isInitiator)
{
query += " AND st.initiator_id = @LoginId AND st.initiator_id = st.current_owner_id ";
}
else
{
query += " AND (st.initiator_id = @LoginId OR st.current_owner_id = @LoginId) ";
query += " AND st.initiator_id <> st.current_owner_id ";
}
query += " ORDER BY sccr.date_of_call ";
return((IList <ICallReportVO>)
AdoTemplate.QueryWithRowMapperDelegate(
CommandType.Text,
query,
delegate(IDataReader dataReader, int rowNum)
{
ICallReportVO callReport = new CallReportVO();
callReport.Id = dataReader.GetSafeInt32("id");
callReport.Customer = new CustomerVO()
{
Id = dataReader.GetSafeInt32("customer_id"),
CustomerType = dataReader.GetSafeString("customer_type"),
CustomerName = dataReader.GetSafeString("customer_name")
};
callReport.ReferenceNo = dataReader.GetSafeString("ref_no");
callReport.CallDate = dataReader.GetSafeDateTime("date_of_call");
callReport.CallPurpose = new CodeValueVO()
{
Description = dataReader.GetSafeString("purpose_desc")
};
callReport.Owner = new UserVO()
{
FullName = dataReader.GetSafeString("owner_name")
};
callReport.CurrentRecipient = new UserVO()
{
FullName = dataReader.GetSafeString("current_recipient")
};
callReport.ProcessDefinition = dataReader.GetSafeString("process_definition");
return callReport;
},
"LoginId",
DbType.Int32,
0,
loginId
));
}
return(null);
}
/// <summary>
/// Obtiene el listado de Trimestres disponibles
/// </summary>
/// <returns></returns>
public IList <IList <string> > ObtenerTrimestres()
{
var builder = CreateDbParametersBuilder();
var trimestres = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, CONSULTA_TRIMESTRE,
delegate(IDataReader reader, int rowNum) { return(CrearDetalleTrimestres(reader, rowNum)); },
builder.GetParameters());
return(trimestres);
}
/// <summary>
/// Obtiene el listado de Ejercicios disponibles
/// </summary>
/// <returns></returns>
public IList <IList <string> > ObtenerEjercicios()
{
var builder = CreateDbParametersBuilder();
var ejercicios = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, CONSULTA_EJERCICIOS,
delegate(IDataReader reader, int rowNum) { return(CrearDetalleEjercicios(reader, rowNum)); },
builder.GetParameters());
return(ejercicios);
}
/// <summary>
/// Obtiene un listado con el detalle de las taxonomías disponibles.
/// </summary>
/// <returns>Listado de taxonomías XBRL disponibles.</returns>
public IList <TaxonomiaXbrlDto> ObtenDetalleTaxonomias()
{
var builder = CreateDbParametersBuilder();
var taxonomiasXbrl = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, CONSULTA_TAXONOMIAS,
delegate(IDataReader reader, int rowNum) { return(CrearDetalleTaxonomiaXbrl(reader, rowNum)); },
builder.GetParameters());
return(taxonomiasXbrl);
}
/// <summary>
/// Obtiene el listado de periodicidades disponibles.
/// </summary>
/// <returns>Listado de periodicidades disponibles.</returns>
public IList <OptionItemDto> ObtenOpcionesPeriodicidad()
{
var builder = CreateDbParametersBuilder();
var opcionesPeriodicidad = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, CONSULTA_PERIODICIDADES,
delegate(IDataReader reader, int rowNum) { return(CrearElementoOpcion(reader, rowNum)); },
builder.GetParameters());
return(opcionesPeriodicidad);
}
public BadBookkeeping GetBadBookkeeping(string id)
{
string sql = "select amount,direction,status from T_bookkeeping where id=@id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
return(AdoTemplate.QueryWithRowMapperDelegate <BadBookkeeping>(CommandType.Text, sql, (reader, rowNum) => new BadBookkeeping {
Amount = reader.GetDecimal(reader.GetOrdinal("amount")), Direction = (BookkeepingDirectionEnum)reader.GetInt32(reader.GetOrdinal("direction")), Status = (BookkeepingStatusEnum)reader.GetInt32(reader.GetOrdinal("status"))
}, pms).FirstOrDefault());
}
/// <summary>
/// Retorna el ultimo Documento de instancia insertado a la DB
/// </summary>
/// <returns></returns>
public DocumentoInstanciaDto ObtenerUltimoRegistroDocumentoInstancia()
{
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate <DocumentoInstanciaDto>(CommandType.Text, QUERY_TOP_FILES,
delegate(IDataReader reader, int rowNum) {
return(CrearDocumentoInstancia(reader, rowNum));
},
builder.GetParameters());
return(documentosInstancia.First());
}
public IList <MyTableItem> GetAll()
{
return(AdoTemplate.QueryWithRowMapperDelegate <MyTableItem>(System.Data.CommandType.Text, "SELECT * FROM MyTable", (reader, row) =>
{
return new MyTableItem
{
Id = (int)reader.GetValue(reader.GetOrdinal("Id")),
Name = reader.GetValue(reader.GetOrdinal("Name"))?.ToString(),
Age = (int)reader.GetValue(reader.GetOrdinal("Age"))
};
}));
}
public PaginacionSimpleDto <DocumentoInstanciaDto> ObtenerDocumentosInstanciaPorPaginacion(PaginacionSimpleDto <DocumentoInstanciaDto> paginacion)
{
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, CONSULTA_ULTIMOS_DOCUMENTOS,
delegate(IDataReader reader, int rowNum) { return(CrearDocumentoInstancia(reader, rowNum)); },
builder.GetParameters());
paginacion.ListaRegistros = documentosInstancia;
paginacion.TotalRegistros = documentosInstancia.Count();
return(paginacion);
}
public PeticionDataTableDto <IList <IList <string> > > ConsultaHistoricoEnvioDataTable(PeticionDataTableDto <IList <IList <string> > > peticionDataTableDto)
{
var builder = CreateDbParametersBuilder();
var query = CONSULTA_DATA_TABLE_HISTORICO_ENVIOS.Replace("#id", peticionDataTableDto.filtros["id"].ToString());
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, query,
delegate(IDataReader reader, int rowNum) { return(CrearDocumentoDataTableInstancia(reader, rowNum)); },
builder.GetParameters());
peticionDataTableDto.recordsTotal = documentosInstancia.Count();
peticionDataTableDto.data = documentosInstancia.ToList();
return(peticionDataTableDto);
}
public ArchivoDocumentoInstanciaDto ObtenerArchivoDocumentosInstancia(long idDocumentoInstancia, long idTipoArchivo)
{
var builder = CreateDbParametersBuilder();
var query = "SELECT DOC.* FROM ArchivoDocumentoInstancia DOC " +
"WHERE DOC.IdDocumentoInstancia=" + idDocumentoInstancia +
"and DOC.IdTipoArchivo=" + idTipoArchivo;
var archivosDocumentoInstancia = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, query,
delegate(IDataReader reader, int rowNum) { return(CrearArchivoDocumentoInstancia(reader, rowNum)); },
builder.GetParameters());
return((archivosDocumentoInstancia != null && archivosDocumentoInstancia.Count > 0) ? archivosDocumentoInstancia.First() : null);
}
public MyTableItem FindItem(int id)
{
var list = AdoTemplate.QueryWithRowMapperDelegate <MyTableItem>(System.Data.CommandType.Text, "SELECT * FROM MyTable WHERE id = " + id, (reader, row) =>
{
return(new MyTableItem
{
Id = (int)reader.GetValue(reader.GetOrdinal("Id")),
Name = reader.GetValue(reader.GetOrdinal("Name"))?.ToString(),
Age = (int)reader.GetValue(reader.GetOrdinal("Age"))
});
});
return(list.Count > 0 ? list[0] : null);
}
public IList <Sale> GetSales(string storeId)
{
return(AdoTemplate.QueryWithRowMapperDelegate <Sale>(CommandType.StoredProcedure, "history_proc",
delegate(IDataReader dataReader, int rowNum)
{
Sale sale = new Sale();
sale.Date = dataReader.GetDateTime(0);
sale.OrderNumber = dataReader.GetString(1);
sale.Quantity = dataReader.GetInt32(2);
sale.Title = dataReader.GetString(3);
sale.Discount = dataReader.GetFloat(4);
sale.Price = dataReader.GetFloat(5);
sale.Total = dataReader.GetFloat(6);
return sale;
}, "stor_id",
DbType.String, 0, storeId));
}
/// <summary>
/// Retorna la fecha del último envío recibido por CNBV
/// </summary>
/// <returns>Fecha del último envío.</returns>
public DateTime ObtenFechaUltimoEnvioDocumentoInstancia()
{
var builder = CreateDbParametersBuilder();
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate <DateTime>(CommandType.Text,
"SELECT MAX(FechaCreacion) maxFecha FROM DocumentoInstancia WHERE EspacioNombresPrincipal IS NOT NULL",
delegate(IDataReader reader, int rowNum) {
var fecha = DateTime.MinValue;
if (DbUtil.FieldExists(reader, "maxFecha"))
{
fecha = reader.GetDateTime(reader.GetOrdinal("maxFecha"));
}
return(fecha);
},
builder.GetParameters());
return(documentosInstancia.First());
}
/// <summary>
/// Gets the customers.
/// </summary>
/// <returns>A list of customers</returns>
/// <summary>
/// Gets the customers using a row mapper delegate.
/// </summary>
/// <remarks>Note that a NullMappingDataReader is used to convert
/// DbNull values to simple defaults (i.e. empty string)
/// when getting data from the DataReader. You can provide AdoTemplate
/// with your own version or not use a dataReader 'wrapper' entirely.
/// </remarks>
/// <returns>A list of customers</returns>
public virtual IList <Customer> GetCustomersWithDelegate()
{
return(AdoTemplate.QueryWithRowMapperDelegate <Customer>(CommandType.Text, cmdText,
delegate(IDataReader dataReader, int rowNum)
{
Customer customer = new Customer();
customer.Address = dataReader.GetString(0);
customer.City = dataReader.GetString(1);
customer.CompanyName = dataReader.GetString(2);
customer.ContactName = dataReader.GetString(3);
customer.ContactTitle = dataReader.GetString(4);
customer.Country = dataReader.GetString(5);
customer.Fax = dataReader.GetString(6);
customer.Id = dataReader.GetString(7);
customer.Phone = dataReader.GetString(8);
customer.PostalCode = dataReader.GetString(9);
customer.Region = dataReader.GetString(10);
return customer;
}));
}
public virtual IList <ICallReportVO> RetrieveCallReportsByCustomerId(int customerId)
{
if (customerId != 0)
{
return((IList <ICallReportVO>)
AdoTemplate.QueryWithRowMapperDelegate(
CommandType.Text,
RETRIEVE_CALL_REPORT_BY_CUSTOMER,
delegate(IDataReader dataReader, int rowNum)
{
ICallReportVO callReport = new CallReportVO();
callReport.Id = dataReader.GetSafeInt32("id");
callReport.ReferenceNo = dataReader.GetSafeString("ref_no");
callReport.CallPurpose = new CodeValueVO()
{
Description = dataReader.GetSafeString("purpose_desc")
};
callReport.CallDate = dataReader.GetSafeDateTime("date_of_call");
callReport.Owner = new UserVO()
{
FullName = dataReader.GetSafeString("owner_name")
};
callReport.LastUpdateDate = dataReader.GetSafeDateTime("last_update_date");
callReport.CurrentRecipient = new UserVO()
{
FullName = dataReader.GetSafeString("current_recipient")
};
callReport.Status = dataReader.GetSafeString("status");
callReport.ProcessDefinition = dataReader.GetSafeString("process_definition");
return callReport;
},
"CustomerId",
DbType.Int32,
0,
customerId
));
}
return(null);
}
public void Test()
{
//IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse1.15");
// dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Odbc-2.0");
dbProvider.ConnectionString =
"Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;";
Assert.IsNotNull(dbProvider);
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
IList <string> authorList =
adoTemplate.QueryWithRowMapperDelegate <string>(CommandType.Text, "select au_lname from authors",
delegate(IDataReader dataReader, int rowNum) { return(dataReader.GetString(0)); });
foreach (string s in authorList)
{
Console.WriteLine(s);
}
Assert.IsTrue(authorList.Count > 0);
}
public void Test()
{
//IDbProvider dbProvider = DbProviderFactory.GetDbProvider("SybaseAse1.15");
// dbProvider.ConnectionString = "Data Source='MARKT60';Port='5000';UID='sa';PWD='';Database='pubs2';";
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("Odbc-2.0");
dbProvider.ConnectionString =
"Driver={Adaptive Server Enterprise};server=MARKT60;port=5000;Database=pubs2;uid=sa;pwd=;";
Assert.IsNotNull(dbProvider);
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
IList<string> authorList =
adoTemplate.QueryWithRowMapperDelegate<string>(CommandType.Text, "select au_lname from authors",
delegate(IDataReader dataReader, int rowNum) { return dataReader.GetString(0); });
foreach (string s in authorList)
{
Console.WriteLine(s);
}
Assert.IsTrue(authorList.Count > 0);
}
public PeticionDataTableDto <IList <IList <string> > > ConsultaInformacionDataTable(PeticionDataTableDto <IList <IList <string> > > peticionDataTableDto)
{
var builder = CreateDbParametersBuilder();
var query = CONSULTA_DATA_TABLE_TODOS;
var prefixDoc = "DOC";
var prefixTax = "TAX";
if (peticionDataTableDto.filtros.ContainsKey("periodicidad"))
{
var periodicidadStr = peticionDataTableDto.filtros["periodicidad"].ToString().Trim();
switch (periodicidadStr)
{
case "4":
query = CONSULTA_DATA_TABLE_TODOS_ENVIOS;
break;
case "2":
query = CONSULTA_DATA_TABLE_ENVIOS_MENSUAL;
break;
default:
query = CONSULTA_DATA_TABLE_ULTIMOS_ENVIOS;
break;
}
}
else
{
prefixDoc = "T1";
prefixTax = "T1";
}
if (peticionDataTableDto.filtros.ContainsKey("clavePizarra"))
{
query += " AND UPPER(" + prefixDoc + ".ClaveEmisora) LIKE '%' + UPPER('" + peticionDataTableDto.filtros["clavePizarra"] + "') + '%' ";
}
if (peticionDataTableDto.filtros.ContainsKey("taxonomia"))
{
query += " AND " + prefixTax + ".IdTaxonomiaXbrl= " + peticionDataTableDto.filtros["taxonomia"];
}
if (peticionDataTableDto.filtros.ContainsKey("ejercicio"))
{
query += " AND " + prefixDoc + ".Anio= " + peticionDataTableDto.filtros["ejercicio"];
}
if (peticionDataTableDto.filtros.ContainsKey("trimestre"))
{
query += " AND UPPER(isnull(" + prefixDoc + ".Trimestre,'')) = UPPER('" + peticionDataTableDto.filtros["trimestre"] + "') ";
}
if (peticionDataTableDto.filtros.ContainsKey("mensual"))
{
query += " AND MONTH(" + prefixDoc + ".FechaReporte) = " + peticionDataTableDto.filtros["mensual"];
}
if (peticionDataTableDto.filtros.ContainsKey("periodicidad"))
{
query += " AND " + prefixTax + ".IdPeriodicidadReporte = " + peticionDataTableDto.filtros["periodicidad"];
}
if (peticionDataTableDto.filtros.ContainsKey("search"))
{
var search = peticionDataTableDto.filtros["search"];
query += " AND (UPPER(isnull(" + prefixDoc + ".ClaveEmisora, '')) like UPPER('%" + search + "%') or UPPER(" + prefixTax + ".Nombre) like UPPER('%" + search + "%'))";
}
if (peticionDataTableDto.order.Count > 0)
{
var orderQuery = String.Empty;
foreach (var order in peticionDataTableDto.order)
{
if (order.column == 0)
{
if (order.dir == "desc")
{
orderQuery += "," + prefixDoc + ".IdDocumentoInstancia DESC";
}
else
{
orderQuery += "," + prefixDoc + ".IdDocumentoInstancia";
}
}
if (order.column == 2)
{
if (order.dir == "desc")
{
orderQuery += "," + prefixDoc + ".FechaCreacion DESC";
}
else
{
orderQuery += "," + prefixDoc + ".FechaCreacion";
}
}
if (order.column == 3)
{
if (order.dir == "desc")
{
orderQuery += "," + prefixDoc + ".ClaveEmisora DESC";
}
else
{
orderQuery += "," + prefixDoc + ".ClaveEmisora";
}
}
if (order.column == 4)
{
if (order.dir == "desc")
{
orderQuery += ",CAST(YEAR(" + prefixDoc + ".FechaReporte) AS varchar) + isnull(' - ' + " + prefixDoc + ".Trimestre, '') DESC";
}
else
{
orderQuery += ",CAST(YEAR(" + prefixDoc + ".FechaReporte) AS varchar) + isnull(' - ' + " + prefixDoc + ".Trimestre, '')";
}
}
if (order.column == 5)
{
if (order.dir == "desc")
{
orderQuery += "," + prefixTax + ".Taxonomia DESC";
}
else
{
orderQuery += "," + prefixTax + ".Taxonomia";
}
}
}
query += " ORDER BY " + orderQuery.Substring(1);
}
var documentosInstancia = AdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, query,
delegate(IDataReader reader, int rowNum) { return(CrearDocumentoDataTableInstancia(reader, rowNum)); },
builder.GetParameters());
peticionDataTableDto.recordsTotal = documentosInstancia.Count();
peticionDataTableDto.data = documentosInstancia.Skip(peticionDataTableDto.start).Take(peticionDataTableDto.length).ToList();
return(peticionDataTableDto);
}
