( SELECT 'C' AS tipodoc, MIN( c.contratoid ) AS numdoc, MIN( numversao ) AS versaodoc…
- User
( SELECT 'C' AS tipo_doc, MIN( c.contrato_id ) AS num_doc, MIN( num_versao ) AS versao_doc FROM contrato c INNER JOIN contrato_versao cv ON c.contrato_id = cv.contrato_id WHERE data_versao >= '2024-10-01' AND data_versao < '2024-11-01' AND cv.status_contrato <> 0 GROUP BY num_fatura ) UNION ALL ( SELECT 'IND' AS tipo_doc, indenizacao_id AS num_doc, 0 AS versao_doc FROM indenizacao_proposta ip INNER JOIN cliente cli ON ip.cliente_id = cli.cliente_id WHERE data_emissao_fatura >= '2024-10-01' AND data_emissao_fatura < '2024-11-01' ) UNION ALL ( SELECT 'MANINT' AS tipo_doc, manutencao_id AS num_doc, 0 AS versao_doc FROM manutencao_proposta mp INNER JOIN cliente cli ON mp.cliente_id = cli.cliente_id WHERE data_emissao_fatura >= '2024-10-01' AND data_emissao_fatura < '2024-11-01' ) UNION ALL ( SELECT 'M' AS tipo_doc, contrato_id AS num_doc, CAST( CONCAT( RIGHT ( CAST( YEAR ( '2024-10-01' ) AS CHAR ), 2 ), CAST( LPAD( MONTH ( '2024-10-01' ), 2, 0 ) AS CHAR )) AS INTEGER ) AS versao_doc FROM medicao_fechamentos );
Create a Delphi language function that accepts as parameters the initial and final date (TDateTime) for this SQL query
- Assistant
Certainly! Below is a Delphi function named
GetDocumentsthat accepts twoTDateTimeparameters (InitialDateandFinalDate). This function constructs and executes the provided SQL query, replacing the hard-coded dates with the passed parameters. It uses FireDAC components for database connectivity, but you can adapt it to other database access frameworks if needed.Prerequisites:
- FireDAC Components: Ensure that your project includes FireDAC components. You need a
TFDConnectioncomponent set up and connected to your database. - Database Connection: Replace
YourFDConnectionwith your actualTFDConnectioncomponent.
Delphi Function Implementation:
uses System.SysUtils, System.Classes, FireDAC.Comp.Client, FireDAC.Stan.Param, Data.DB; function GetDocuments(InitialDate, FinalDate: TDateTime; FDConnection: TFDConnection): TFDQuery; var FDQuery: TFDQuery; begin // Create a new FireDAC query instance FDQuery := TFDQuery.Create(nil); try // Assign the existing FireDAC connection FDQuery.Connection := FDConnection; // Construct the SQL query with parameter placeholders FDQuery.SQL.Text := '( SELECT ' + ' ''C'' AS tipo_doc, ' + ' MIN(c.contrato_id) AS num_doc, ' + ' MIN(num_versao) AS versao_doc ' + 'FROM contrato c ' + 'INNER JOIN contrato_versao cv ON c.contrato_id = cv.contrato_id ' + 'WHERE data_versao >= :InitialDate ' + ' AND data_versao < :FinalDate ' + ' AND cv.status_contrato <> 0 ' + 'GROUP BY num_fatura ) ' + 'UNION ALL ' + '( SELECT ' + ' ''IND'' AS tipo_doc, ' + ' indenizacao_id AS num_doc, ' + ' 0 AS versao_doc ' + 'FROM indenizacao_proposta ip ' + 'INNER JOIN cliente cli ON ip.cliente_id = cli.cliente_id ' + 'WHERE data_emissao_fatura >= :InitialDate ' + ' AND data_emissao_fatura < :FinalDate ) ' + 'UNION ALL ' + '( SELECT ' + ' ''MANINT'' AS tipo_doc, ' + ' manutencao_id AS num_doc, ' + ' 0 AS versao_doc ' + 'FROM manutencao_proposta mp ' + 'INNER JOIN cliente cli ON mp.cliente_id = cli.cliente_id ' + 'WHERE data_emissao_fatura >= :InitialDate ' + ' AND data_emissao_fatura < :FinalDate ) ' + 'UNION ALL ' + '( SELECT ' + ' ''M'' AS tipo_doc, ' + ' contrato_id AS num_doc, ' + ' CAST( ' + ' CONCAT( ' + ' RIGHT(CAST(YEAR(:InitialDate) AS CHAR), 2), ' + ' LPAD(CAST(MONTH(:InitialDate) AS CHAR), 2, ''0'') ' + ' ) AS INTEGER ' + ' ) AS versao_doc ' + 'FROM medicao_fechamentos )'; // Define the parameters and assign values FDQuery.Params.Clear; FDQuery.ParamByName('InitialDate').DataType := ftDate; FDQuery.ParamByName('InitialDate').AsDate := InitialDate; FDQuery.ParamByName('FinalDate').DataType := ftDate; FDQuery.ParamByName('FinalDate').AsDate := FinalDate; // Prepare and execute the query FDQuery.Open; // Return the executed query Result := FDQuery; except // If an exception occurs, free the query and re-raise the exception FDQuery.Free; raise; end; end;Usage Example:
Here's how you can use the
GetDocumentsfunction within your application:procedure TForm1.ButtonGetDocumentsClick(Sender: TObject); var DocumentsQuery: TFDQuery; InitialDate, FinalDate: TDateTime; begin // Define the date range InitialDate := EncodeDate(2024, 10, 1); FinalDate := EncodeDate(2024, 11, 1); try // Call the GetDocuments function DocumentsQuery := GetDocuments(InitialDate, FinalDate, FDConnection1); // Use the returned query as needed, for example, display in a grid FDTable1.DataSource := DataSource1; DataSource1.DataSet := DocumentsQuery; except on E: Exception do ShowMessage('Error fetching documents: ' + E.Message); end; end;Explanation:
-
Function Parameters:
InitialDate: The start date (TDateTime) for the query.FinalDate: The end date (TDateTime) for the query.FDConnection: An existingTFDConnectioncomponent connected to your database.
-
SQL Query Construction:
- The SQL query uses
:InitialDateand:FinalDateas parameter placeholders. - These parameters are bound to the function's
InitialDateandFinalDatevalues to ensure proper formatting and to prevent SQL injection.
- The SQL query uses
-
Parameter Binding:
- The parameters are cleared and then defined with the appropriate data types (
ftDate). AsDateis used to assign theTDateTimevalues to the parameters.
- The parameters are cleared and then defined with the appropriate data types (
-
Executing the Query:
FDQuery.Openexecutes the SQL and fetches the results.- The function returns the
TFDQueryinstance containing the result set.
-
Error Handling:
- If an error occurs during query execution, the
TFDQueryis freed, and the exception is re-raised for the calling code to handle.
- If an error occurs during query execution, the
Notes:
-
Connection Management: Ensure that the
TFDConnection(FDConnection1in the usage example) is properly configured and connected before callingGetDocuments. -
Resource Management: The caller of
GetDocumentsis responsible for freeing the returnedTFDQuerywhen it's no longer needed to avoid memory leaks. -
SQL Functions: The SQL uses functions like
YEAR,MONTH,RIGHT,LPAD, andCONCAT. Ensure that your database supports these functions or adjust them accordingly based on your specific SQL dialect. -
Security: Using parameterized queries as shown helps prevent SQL injection and ensures that date formats are correctly handled by the database engine.
By incorporating this function into your Delphi application, you can dynamically retrieve documents based on varying date ranges, enhancing the flexibility and reusability of your data access layer.
- FireDAC Components: Ensure that your project includes FireDAC components. You need a
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.