En en desarrollo de esta tarea programada hemos durado aproximadamente 50 horas.
Lo que mas tiempo nos tomó fue los stored procedures grandes, el diario y el de faturacion ya que se debe pensar mucho y realizar muchas pruebas para ver que todo valla funcionando bien y cada vez que se hacia una prueba o corrida de los sp de debia vaciar la base de datos y volver a cargar a informacion de los archivos xml que contenian la informacion de 6 meses aproximadamente.
A lo largo del camino nos topamos con gran cantidad de errores los cuales eran causados por la existencia de nulos, por lo que nos fue de mucha ayuda la funcion ISNULL, que nos permite remplazar el valor de una variable por otra, si la primera es nula, por ejemplo isnull(@valor,valor_remplazo).
Investigando dimos con algunas funciones propias del sql que nos fueron de gran utilidad como por ejemplo la funcion DATEADD que nos permite sumar una valor a una fecha, ya sea dias, horas, meses, etc de una manera muy facil, por ejemplo DATEADD(mes,1,@fecha), mas informacion en esta página http://www.w3schools.com/sql/func_dateadd.asp. Otra funcion de gran utilidad fue DATEIFF que nos retorna la cantidad de dias, meses, minutos, años , etc entre 2 fechas, por ejemplo DATEIFF(minute,@fecha1,@fecha2), mas info aquí http://www.w3schools.com/sql/func_datediff.asp. Otra es SUM(), que nos permite sumar los valores de una columna de varios registros, mas informacion aqui http://www.w3schools.com/sql/sql_func_sum.asp.
Tambien nos ayudo mucho las siguientes pagina:
1) http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql donde podemos ver como hacer una sentencia update con un join.
2) http://www.w3schools.com/sql/sql_orderby.asp donde se explica el uso de la sentencia order by para el ordenamiento de consultas.
Qué no funciona? La parte de los egresos, ya que vimos que era muy complicado eliminar un resgistro porque habia que eliminar en cascada y a estas alturas ya era muy tarde para empezar a implementar esa eliminación, pero al final estabamos pensando que hubieramos creado un nuevo campo en la tabla de cuenta para poder definir cuando estaba activa o inactiva y así solo se cambiaba ese estado, pero igual ya es tarde para eso, ya estamos casi sobre tiempo de entrega del proyecto. Si lo hubieramos pensado desde un principio hubiera sido mas simple y lo hubieramos podido implementar.
En conclusion realizar este proyecto nos deja una gran experiencia en la creacion de stored procedures masivos, además de que aprendimos a usar la operacion join que aunque es cara, es de gran ayuda en situaciones como esta donde hay que tomar informacion desde varias tablas a la vez.
Bitácora # 3 Olman-Jimmy
miércoles, 22 de junio de 2016
Creacion del sp facturacion.
21 de junio de 8am - 12md y de 1pm a 8pm
y 22 de Junio de 2pm a 11pm
Creacion del sp faturacion.
Al igual que en el sp diario tuvimos que crear funciones que nos ayudaran a calcular algunas cosas como por ejemplo el costo total de las llamadas, mensajes, internet, etc estos debian tomar en cuenta el limite de los minutos de internet y de llamadas de direferentes tipos con los que contaba el contrato con el cual estaba asociado la cuenta, esto para cobrar adicionalmente los excedentes. Ademas se aplicaban los costos fijos. Esto se ejecuta si el dia que se corre el sp es el dia de corte y si es así, ademas de calcular todo y poner los totales en la factura, debe crear una nueva a la cual se irán agregando movimientos segun el sp diario.
y 22 de Junio de 2pm a 11pm
Creacion del sp faturacion.
Al igual que en el sp diario tuvimos que crear funciones que nos ayudaran a calcular algunas cosas como por ejemplo el costo total de las llamadas, mensajes, internet, etc estos debian tomar en cuenta el limite de los minutos de internet y de llamadas de direferentes tipos con los que contaba el contrato con el cual estaba asociado la cuenta, esto para cobrar adicionalmente los excedentes. Ademas se aplicaban los costos fijos. Esto se ejecuta si el dia que se corre el sp es el dia de corte y si es así, ademas de calcular todo y poner los totales en la factura, debe crear una nueva a la cual se irán agregando movimientos segun el sp diario.
lunes, 20 de junio de 2016
Creacion del sp masivo diario
19 de junio de 8am - 12md y de 2pm a 8pm
20 de junio de 8am - 12md y de 1pm a 7pm
Se crea el procedimiento almacenado diario, tuvimos algunos problemas en la implementacion, algunos errores minimos, otros no tanto.
La duracion fue extensa ya que la complejidad del algoritmo para hacer todos los calculos era muy complejo, nos tomo horas pensar algunos métodos
Por ejemplo en las fechas nos ayudaron varias funciones como por ejemplo DATEADD(month, 1,@fecha) sum() DATEDIFF(minute,@fecha1,@fecha2) las cuales se explicaran en el informe final mas detalladamente.
tuvimos que crear funciones tambien que nos ayudaran a calcular la cantidad de minutos que duraba una llamada por ejemplo, tambien el costo, si era familiar, extrangera, especial, etc, al igual que con los mensajes y demás.
use telefonia
go
create procedure sp_diario
@fecha_proceso datetime
as
begin
begin try
declare @temp table(
sec int primary key identity,
id int,
id_cuenta int,
id_factura int,
fk_tipo_movimientos int,
fecha datetime,
tipo int,
numero_telefono bigint,
costo money,
minutos float
)
declare @cantida int,@ultimo int
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select llamada.id,cuenta.id,llamada.fecha,llamada.fk_tipos_operaciones,llamada.numero_telefono
from llamada inner join cuenta on cuenta.telefono=llamada.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select llamadadXcobrar.id,cuenta.id,llamadadXcobrar.fecha,llamadadXcobrar.fk_tipos_operaciones,llamadadXcobrar.numero_telefono
from llamadadXcobrar inner join cuenta on cuenta.telefono=llamadadXcobrar.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select conexion_internet.id,cuenta.id,conexion_internet.fecha,conexion_internet.fk_tipos_operaciones,conexion_internet.numero_telefono
from conexion_internet inner join cuenta on cuenta.telefono=conexion_internet.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
.
.
.
.
.
update @temp set fk_tipo_movimientos=dbo.calculo_tipo_movimiento(tipo,id,id_cuenta),costo=dbo.calculo_costo(tipo,id,id_cuenta),minutos=dbo.calculo_minutos(tipo,id,id_cuenta)
set transaction isolation level read uncommitted
begin transaction
insert cuenta(fk_tipo_contrato,dia_corte,telefono)
select ingreso.tipo_contrato,ingreso.fecha_corte,ingreso.numero_telefono
from ingreso where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
set @cantida=(select count(1) from ingreso where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso))
insert factura(fk_cuenta,mes,fecha_maxima_pago,cm_normales,cm_reducidos,cm_internet,cm_gratuita,cm_mensajes,cd_pago_factura,total,estado)
select cuenta.id,MONTH(DATEADD(month, 1,@fecha_proceso)),DATEADD(month, 1,DATEADD(day,(-1*DAY(@fecha_proceso))+cuenta.dia_corte+15,@fecha_proceso)),0,0,0,0,0,0,0,'facturando' from cuenta order by id DESC OFFSET 0 ROWS FETCH NEXT @cantida ROWS ONLY
update @temp set id_factura=(select factura.id from factura join @temp on id_cuenta=factura.fk_cuenta order by id DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
insert movimientos (fk_cuenta,fk_factura,fk_tipo_movimientos,fecha_hora,costo,minutos,postin,postby,postDate)
select id_cuenta,id_factura,fk_tipo_movimientos,fecha,costo,minutos,'localhost','localhost',GETDATE() from @temp
commit
return 1
end try
begin catch
if @@TRANCOUNT > 0
begin
rollback
end
return -1*@@error
end catch
end
20 de junio de 8am - 12md y de 1pm a 7pm
Se crea el procedimiento almacenado diario, tuvimos algunos problemas en la implementacion, algunos errores minimos, otros no tanto.
La duracion fue extensa ya que la complejidad del algoritmo para hacer todos los calculos era muy complejo, nos tomo horas pensar algunos métodos
Por ejemplo en las fechas nos ayudaron varias funciones como por ejemplo DATEADD(month, 1,@fecha) sum() DATEDIFF(minute,@fecha1,@fecha2) las cuales se explicaran en el informe final mas detalladamente.
tuvimos que crear funciones tambien que nos ayudaran a calcular la cantidad de minutos que duraba una llamada por ejemplo, tambien el costo, si era familiar, extrangera, especial, etc, al igual que con los mensajes y demás.
use telefonia
go
create procedure sp_diario
@fecha_proceso datetime
as
begin
begin try
declare @temp table(
sec int primary key identity,
id int,
id_cuenta int,
id_factura int,
fk_tipo_movimientos int,
fecha datetime,
tipo int,
numero_telefono bigint,
costo money,
minutos float
)
declare @cantida int,@ultimo int
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select llamada.id,cuenta.id,llamada.fecha,llamada.fk_tipos_operaciones,llamada.numero_telefono
from llamada inner join cuenta on cuenta.telefono=llamada.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select llamadadXcobrar.id,cuenta.id,llamadadXcobrar.fecha,llamadadXcobrar.fk_tipos_operaciones,llamadadXcobrar.numero_telefono
from llamadadXcobrar inner join cuenta on cuenta.telefono=llamadadXcobrar.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
insert @temp (id,id_cuenta,fecha,tipo,numero_telefono)
select conexion_internet.id,cuenta.id,conexion_internet.fecha,conexion_internet.fk_tipos_operaciones,conexion_internet.numero_telefono
from conexion_internet inner join cuenta on cuenta.telefono=conexion_internet.numero_telefono
where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
.
.
.
.
.
update @temp set fk_tipo_movimientos=dbo.calculo_tipo_movimiento(tipo,id,id_cuenta),costo=dbo.calculo_costo(tipo,id,id_cuenta),minutos=dbo.calculo_minutos(tipo,id,id_cuenta)
set transaction isolation level read uncommitted
begin transaction
insert cuenta(fk_tipo_contrato,dia_corte,telefono)
select ingreso.tipo_contrato,ingreso.fecha_corte,ingreso.numero_telefono
from ingreso where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso)
set @cantida=(select count(1) from ingreso where day(fecha)=day(@fecha_proceso) and month(fecha)=month(@fecha_proceso) and year(fecha)=year(@fecha_proceso))
insert factura(fk_cuenta,mes,fecha_maxima_pago,cm_normales,cm_reducidos,cm_internet,cm_gratuita,cm_mensajes,cd_pago_factura,total,estado)
select cuenta.id,MONTH(DATEADD(month, 1,@fecha_proceso)),DATEADD(month, 1,DATEADD(day,(-1*DAY(@fecha_proceso))+cuenta.dia_corte+15,@fecha_proceso)),0,0,0,0,0,0,0,'facturando' from cuenta order by id DESC OFFSET 0 ROWS FETCH NEXT @cantida ROWS ONLY
update @temp set id_factura=(select factura.id from factura join @temp on id_cuenta=factura.fk_cuenta order by id DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
insert movimientos (fk_cuenta,fk_factura,fk_tipo_movimientos,fecha_hora,costo,minutos,postin,postby,postDate)
select id_cuenta,id_factura,fk_tipo_movimientos,fecha,costo,minutos,'localhost','localhost',GETDATE() from @temp
commit
return 1
end try
begin catch
if @@TRANCOUNT > 0
begin
rollback
end
return -1*@@error
end catch
end
sábado, 18 de junio de 2016
Modificacion de scripts para cargar informacion de los archivos a la base de datos
8am - 11am
Modificamos los scripts de carga de datos bridado por Brandom y Mario para que se adaparan a las tablas que habiamos creado en nuestra base de datos.
Ademas para que primero eliminaran todo lo que habia en las tablas y reseteara el identity de cada tabla para que los registros empezaran a insertarsen desde el id 1.
La siguiente pagina nos fue de gran ayuda http://www.ashishblog.com/importexport-excel-xlsx-or-xls-file-into-sql-server/ , aqui enseñan como importar datos desde un archivo xlsx(excel).
Tuvimos varios problemas con el sql management studio a la hora de cargar los datos. Mucho rato duramos tratando de solucionar el error, pero además de eso surgieron otros problemas ("Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error") este lo solucionamos mucho rato despues de estar averiguando, la informacion contenida en estas paginas fue de gran ayuda para solucionarlo:
https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/475c4d52-e60b-4f3e-bb60-c1be56e34fd4/how-resolve-error-ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned?forum=transactsql.
Luego, como ya los script de carga de datos funcionan con normalidad podemos continuar ya que lo que sigue es empezar a implementar los sp masivos.
Modificamos los scripts de carga de datos bridado por Brandom y Mario para que se adaparan a las tablas que habiamos creado en nuestra base de datos.
Ademas para que primero eliminaran todo lo que habia en las tablas y reseteara el identity de cada tabla para que los registros empezaran a insertarsen desde el id 1.
La siguiente pagina nos fue de gran ayuda http://www.ashishblog.com/importexport-excel-xlsx-or-xls-file-into-sql-server/ , aqui enseñan como importar datos desde un archivo xlsx(excel).
Tuvimos varios problemas con el sql management studio a la hora de cargar los datos. Mucho rato duramos tratando de solucionar el error, pero además de eso surgieron otros problemas ("Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error") este lo solucionamos mucho rato despues de estar averiguando, la informacion contenida en estas paginas fue de gran ayuda para solucionarlo:
https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/475c4d52-e60b-4f3e-bb60-c1be56e34fd4/how-resolve-error-ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned?forum=transactsql.
Luego, como ya los script de carga de datos funcionan con normalidad podemos continuar ya que lo que sigue es empezar a implementar los sp masivos.
viernes, 17 de junio de 2016
Interfaz Grafica y Clases necesarias para la conexion y obtencion de datos
9am - 12am Creacion de la interfaz grafica para las consultas.
Se implementaron 2 elementos gridview, en el primero se muestra las ultimas 6 facturas referentes al número que se consulta el cual se ingresa por el textbox. Esta tabla tiene una columna de botones la cual permite que se seleccione una fila específica y así poder mostrar de manera detallada todos los movimientos anclados a esta factura seleccionada.
Además creamos las clases necesarias para conectar con la base de datos, procesar la informacion, llamar a los sp y ademas almacenar la informacion que devuelven los sp.
Tuvimos problemas a la hora de convertir de tipo money a algun formato en c#, decidimos gracias a este post (http://stackoverflow.com/questions/693372/what-is-the-best-data-type-to-use-for-money-in-c) que lo mas conveniente es usar el tipo decimal en c# para la conversion de numeros tipo money en sql server. Tambien averiguamos como funcionaba el inner join para obtener el nombre del tipo de movimiento de la tabla tipo_movimientos junto con los demas datos de la tabla movimientos segun la factura seleccionada (la aplicacion de android "aprende a programar" ayudó bastante con la sintaxis de la consulta).
Se implementaron 2 elementos gridview, en el primero se muestra las ultimas 6 facturas referentes al número que se consulta el cual se ingresa por el textbox. Esta tabla tiene una columna de botones la cual permite que se seleccione una fila específica y así poder mostrar de manera detallada todos los movimientos anclados a esta factura seleccionada.
Además creamos las clases necesarias para conectar con la base de datos, procesar la informacion, llamar a los sp y ademas almacenar la informacion que devuelven los sp.
Tuvimos problemas a la hora de convertir de tipo money a algun formato en c#, decidimos gracias a este post (http://stackoverflow.com/questions/693372/what-is-the-best-data-type-to-use-for-money-in-c) que lo mas conveniente es usar el tipo decimal en c# para la conversion de numeros tipo money en sql server. Tambien averiguamos como funcionaba el inner join para obtener el nombre del tipo de movimiento de la tabla tipo_movimientos junto con los demas datos de la tabla movimientos segun la factura seleccionada (la aplicacion de android "aprende a programar" ayudó bastante con la sintaxis de la consulta).
jueves, 16 de junio de 2016
Creacion de los scripts sql para crear las tablas necesarias para la base de datos y sp seleccionar movimientos y facturas
8am - 11:30am y 1pm - 3pm
Se crean los script para la creacion de la base de datos.
Decidimos crear tablas para cargar en estas la información de los archivos de datos xml, para que de esta manera nos fuera mas facil consultarlos y hacer operaciones con estos datos, asi no tendriamos que estar leyendo los archivos xml cada vez que corremos los sp masivos y se completaria de una manera más rápida la ejecucion de los mismos.
5pm - 5:30pm
Se empieza a implementar los stored procedures para las consultas de facturas y movimientos los cuales se usarán para consultar la informacion que se necesita mostrar en las tablas de la interfaz grafica, estos 2 sp reciben como parámetro el número de telefono que se desea consultar.
Suscribirse a:
Entradas (Atom)