How to get bank reports through web application?

0

Where I work there are some procedures in the database (SQL Server 2008) that take hours to execute and return large amounts of information that are always copied to a worksheet and sent to the end user.

There are even some desktop applications that can run them but this is not very practical;

Processed example:

Isthereanywaytoautomatethesereportsthroughawebapplication?

Itwouldbeperfectiftherewassomewayfortheusertotriggertheprocedureandleaveitrunningthroughthewebsothatatanothertimehecouldre-enterthewebsiteandgetthereport.

IknowalittleofASP.NET,butIhavenoideaifyoucandothis...

Nowreportsareleftrunningontheuser'smachinethroughadesktopapplicationthatgeneratesanExcelfileorthroughtheSQLServerManagementStudioitselfwherethesupporterconnectstothedatabase...

p>

Leavetheprocedurerunningontheserver

And when it finishes executing, it copies the result to an Excel spreadsheet and sends it to the user who requested it.

But many of these procedures are time-consuming and so I'd like you to have a way to let the user do this for a web application.

Any ideas?

    
asked by anonymous 23.01.2015 / 15:23

1 answer

1
  

For this answer I will have to give general guidelines, because the detail of the implementation would be very extensive.

What we have in your question is a Stored Procedure in Microsoft SQL Server that is called, processes information, and returns a table that is converted to worksheet as a return. Before you even write a Web application to deal with it, you can first promote some Stored Procedure changes that will greatly help your work.

1. Create a table of report requests

I do not know what your data format looks like, but I think of something like this:

CREATE TABLE REQUISICAO_RELATORIO
(
    REQUISICAO_RELATORIO_ID INT PRIMARY KEY IDENTITY,
    USUARIO_ID INT, -- Eu estou chutando que o usuário tenha isso, só para exemplo.
    DATA_REQUISICAO DATETIME DEFAULT getdate(),
    STATUS VARCHAR(100), -- Pode ser 'Iniciado', 'Concluido', 'Falha',
    TIPO_RELATORIO VARCHAR(100) -- Aparentemente tem tipos de relatórios. Use esse campo para preencher.
)

Each report request will insert a row in this table. The Stored Procedure will process the data normally and, upon completing the task, will update the data in this table.

2. Enter the return data into a table referenced by REQUISICAO_RELATORIO

Make a new table by inserting a foreign key column that refers to REQUISICAO_RELATORIO . This new table will have exactly the same return data that goes into your Excel worksheet.

To select the data, you can even select all data at any time, like this:

SELECT * FROM TABELA_COM_DADOS_DO_EXCEL
WHERE REQUISICAO_RELATORIO_ID = 1

Or using other fields. The important thing is to preserve the data to be selected at any time.

3. Create tasks that read REQUISICAO_RELATORIO and run the Stored Procedure

To learn how to create tasks in SQL Server , see this link .

So you can schedule processing and do not need to stick with a Microsoft SQL Server Management Studio window.

Note that after these 3 steps, implementing a Web application becomes completely optional.

    
23.01.2015 / 18:29