public void InternalCustomerOrdersStats()
{
Sql.Clear();
Sql.Append("select * from fv_user.stats_ordenes_clientesinternos ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
this.DataTable = querySelect.ExecuteQuery();
}
public IList <OfferDto> PublishOffersToApi()
{
Sql.Clear();
Sql.Append("select * from fv_user.vwObtenerOfertas where estado_oferta=1 ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
return(GetOfferDataToPublish(querySelect.ExecuteQuery()));
}
public void UsersStats()
{
Sql.Clear();
Sql.Append("select * from fv_user.stats_usuarios ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
this.DataTable = querySelect.ExecuteQuery();
}
public void FindOfferDetailByOffer(string offerId)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwObtenerDetalleOfertas where id_oferta=:pIdOferta ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdOferta", offerId, DbType.String);
DataTable = querySelect.ExecuteQuery();
}
public void FindOffersByStatus(int status)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwObtenerOfertas where estado_oferta =:pEstado ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pEstado", status, DbType.Int32);
DataTable = querySelect.ExecuteQuery();
}
public IList <OfferDetail> SearchDetailsFromOffer(string offerId)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwObtenerDetalleOfertas where id_oferta=:pId ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pId", offerId, DbType.String);
return(GetOrderDetailDataToOrder(querySelect.ExecuteQuery()));
}
public OfferDto FindOfferById(string offerId)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwObtenerOfertas where id_oferta=:pIdOferta ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdOferta", offerId, DbType.String);
return(GetOfferDataByIdFromDataTable(querySelect.ExecuteQuery()));
}
public DataTable LossesByYear(int rolId, int year)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwMermas ");
Sql.Append("where id_rol=:pIdRol and to_char(fecha_orden, 'yyyy')=:pYear ");
Sql.Append("order by fecha_orden ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pYear", year, DbType.Int32);
return(querySelect.ExecuteQuery());
}
public DataTable LossesByDate(int rolId, DateTime date)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwMermas ");
Sql.Append("where id_rol=:pIdRol and fecha_orden=:pFecha ");
Sql.Append("order by fecha_orden ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pFecha", date, DbType.Date);
return(querySelect.ExecuteQuery());
}
public DataTable SalesByRange(int rolId, DateTime dateFrom, DateTime dateTo)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwVentas ");
Sql.Append("where id_rol=:pIdRol and to_char(fecha_orden, 'dd-MM-yyyy') between :pFechaDesde and :pFechaHasta ");
Sql.Append("order by fecha_orden ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pFechaDesde", dateFrom, DbType.Date);
querySelect.AddParameter("pFechaHasta", dateTo, DbType.Date);
return(querySelect.ExecuteQuery());
}
public DataTable SalesBySemesterAndYear(int rolId, int semester, int year)
{
Sql.Clear();
Sql.Append("select * from fv_user.vwVentas ");
Sql.Append("where id_rol=:pIdRol and trunc(((to_number(to_char(fecha_orden, 'MM'))-1)/6))=:pSemester and to_char(fecha_orden, 'yyyy')=:pYear ");
Sql.Append("order by fecha_orden ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pSemester", semester, DbType.Int32);
querySelect.AddParameter("pYear", year, DbType.Int32);
return(querySelect.ExecuteQuery());
}
public DataTable ResumeSalesByYear(int rolId, int year)
{
Sql.Clear();
Sql.Append("select to_char(fecha_orden, 'dd-MM-yyyy') as fecha_compra, sum(monto_pagado) as monto ");
Sql.Append("from fv_user.vwVentas ");
Sql.Append("where id_rol=:pIdRol and to_char(fecha_orden, 'yyyy')=:pYear ");
Sql.Append("group by to_char(fecha_orden, 'dd-MM-yyyy') ");
Sql.Append("having sum(monto_pagado)>0 ");
Sql.Append("order by to_char(fecha_orden, 'dd-MM-yyyy')");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pYear", year, DbType.Int32);
return(querySelect.ExecuteQuery());
}
public DataTable ResumeLossesByYear(int rolId, int year)
{
Sql.Clear();
Sql.Append("select id_rol, ");
Sql.Append("fecha_orden as fecha_orden, ");
Sql.Append("productor as productor,");
Sql.Append("sum(precio_productos) as total_mermas ");
Sql.Append("from fv_user.vwmermas ");
Sql.Append("where id_rol=:pIdRol and to_char(fecha_orden, 'yyyy')=:pYear ");
Sql.Append("group by id_rol, fecha_orden, productor ");
Sql.Append("having sum(precio_productos)>0 ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pYear", year, DbType.Int32);
return(querySelect.ExecuteQuery());
}
public DataTable ResumeLossesByRange(int rolId, DateTime dateFrom, DateTime dateTo)
{
Sql.Clear();
Sql.Append("select id_rol, ");
Sql.Append("fecha_orden as fecha_orden, ");
Sql.Append("productor as productor,");
Sql.Append("sum(precio_productos) as total_mermas ");
Sql.Append("from fv_user.vwmermas ");
Sql.Append("where id_rol=:pIdRol and fecha_orden between :pFechaDesde and :pFechaHasta ");
Sql.Append("group by id_rol, fecha_orden, productor ");
Sql.Append("having sum(precio_productos)>0 ");
IQuerySelect querySelect = DefineQuerySelect(Sql.ToString());
querySelect.AddParameter("pIdRol", rolId, DbType.Int32);
querySelect.AddParameter("pFechaDesde", dateFrom, DbType.Date);
querySelect.AddParameter("pFechahasta", dateTo, DbType.Date);
return(querySelect.ExecuteQuery());
}
