Announcement

Collapse
No announcement yet.

Help with Database to hpdf

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with Database to hpdf

    I am here again asking for your help.
    I'm trying to generate a report to be saved to PDF from a database using SQLite3 and HPDF, but when I run the test I'm getting the following error message:
    "Attempt to perform arithmetic on field 'Rows' (a nil value)
    Stack Traceback:
    1: [Report -> Button_GeraPDF -> On Click] Line: 86 in main chunck "

    The code is as follows:

    require "hpdf"
    require "common"

    -------------------- FUNÇÕES --------------------
    function S_Logotipo (SPage)
    image = hpdf.LoadPngImageFromFile(Relatorio_pdf, "AutoPlay\\Images\\Controle de Acesso LOGO.png");
    local iw = hpdf.Image_GetWidth(image);
    local ih = hpdf.Image_GetHeight(image);
    hpdf.Page_DrawImage(SPage, image, 40, 700, 150, 150);

    end



    function S_Relatorio(SPage)
    hpdf.Page_SetLineWidth(SPage, 0.2);
    hpdf.Page_Rectangle(SPage, 310, 605, 230, 70);
    hpdf.Page_Stroke(SPage);

    end



    function sCabeceira(sPAGINA,sLINEA)
    hpdf.Page_BeginText(sPAGINA);
    font5=hpdf.GetFont(Relatorio_pdf, "Times-Bold","WinAnsiEncoding");
    hpdf.Page_SetFontAndSize(sPAGINA, font5, 9);
    hpdf.Page_TextOut(sPAGINA, 45,sLINEA,"NOME VISITANTE");
    hpdf.Page_TextOut(sPAGINA, 100,sLINEA,"EMPRESA VISITANTE");
    hpdf.Page_TextOut(sPAGINA, 200,sLINEA,"EMPREGADO VISITADO");
    hpdf.Page_TextOut(sPAGINA, 300,sLINEA,"ÁREA VISITADA");
    hpdf.Page_TextOut(sPAGINA, 375,sLINEA,"HORA DE ENTRADA");
    hpdf.Page_TextOut(sPAGINA, 450,sLINEA,"HORA DE SAÍDA");
    hpdf.Page_EndText(sPAGINA)

    end



    function s_Controle (sPAGINA)
    hpdf.Page_BeginText(S_NOMEPAGINA);
    font4=hpdf.GetFont(Factura_pdf, "Times-Bold","WinAnsiEncoding");
    hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font4, 12);
    hpdf.Page_TextOut(S_NOMEPAGINA, 400,800,"RELATÓRIO DE VISITAS NO DIA "..vDiaRelatorio);
    hpdf.Page_EndText(S_NOMEPAGINA);

    end



    function sPie (fPAGINA,fNUMERODEPAGINA,fTOTALPAGINAS)
    hpdf.Page_BeginText(fPAGINA);
    font3=hpdf.GetFont(Relatorio_pdf, "Courier","WinAnsiEncoding");
    hpdf.Page_SetFontAndSize(fPAGINA, font3, 7);
    hpdf.Page_TextOut(fPAGINA, 435,35,"Página "..fNUMERODEPAGINA.." de "..fTOTALPAGINAS);
    hpdf.Page_EndText(fPAGINA)

    end



    function sEspera ()
    StatusDlg.Show();
    StatusDlg.SetTitle("GERANDO PÁGINAS ");
    StatusDlg.SetMeterRange(1, S_NUMEROS_DE_PAGINAS);
    StatusDlg.SetMeterPos(S_CONTADOR);
    StatusDlg.SetStatusText("PÁGINA. . . " .. S_CONTADOR.." DE "..S_NUMEROS_DE_PAGINAS );

    end


    -------------------- INÍCIO --------------------

    Relatorio_pdf = hpdf.New()


    if Relatorio_pdf then

    Opendb()
    for row in db:nrows("SELECT * FROM Controle WHERE Dia LIKE '%"..vDiaRelatorio.."%' ") do
    tbResults = row.Dia
    --tbResults = SQLite3.QueryToTable(sDatabase, "SELECT * FROM Controle WHERE Dia LIKE '%"..vDiaRelatorio.."%' ")

    s_LINHAS_POR_PAGINA = 59 ;

    S_NUMEROS_DE_PAGINAS = tbResults.Rows/s_LINHAS_POR_PAGINA;

    S_NUMEROS_DE_PAGINAS =Math.Floor (S_NUMEROS_DE_PAGINAS );

    S_NUMEROS_DE_PAGINAS = S_NUMEROS_DE_PAGINAS + 1;

    S_LINHAS_ADICIONALES = S_NUMEROS_DE_PAGINAS * 8;

    S_PAGINAS_ADICIONAIS = S_LINHAS_ADICIONALES / 59;

    S_PAGINAS_ADICIONAIS = Math.Floor (S_PAGINAS_ADICIONAIS )+ 1;

    S_NUMEROS_DE_PAGINAS = S_NUMEROS_DE_PAGINAS + S_PAGINAS_ADICIONAIS ;

    --Dialog.Message("páginas",tostring(S_NUMEROS_DE_PAG INAS))

    S_CONTADOR = 0;

    S_CONTADOR2 = 1;

    nRow =0 ;

    sLinea = 690 ;
    sLinea2 = 675 ;

    while S_CONTADOR < S_NUMEROS_DE_PAGINAS do


    font=hpdf.GetFont(Relatorio_pdf, "Helvetica","WinAnsiEncoding");

    S_CONTADOR = S_CONTADOR + 1

    sEspera ()



    S_NOMEPAGINA = "página"..S_CONTADOR;

    S_NOMEPAGINA = hpdf.AddPage(Relatorio_pdf);
    hpdf.Page_SetWidth (S_NOMEPAGINA,595.276);
    hpdf.Page_SetHeight (S_NOMEPAGINA,841.89);
    hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font, 10);


    s_Relatorio (S_NOMEPAGINA) ;

    S_Logotipo (S_NOMEPAGINA);

    sCabeceira(S_NOMEPAGINA,sLinea);
    sPie (S_NOMEPAGINA,S_CONTADOR,S_NUMEROS_DE_PAGINAS);


    for S_CONTADOR2 = 1, tbResults.Rows do

    nRow = nRow + 1;
    font2=hpdf.GetFont(Relatorio_pdf, "Courier","WinAnsiEncoding");
    hpdf.Page_SetFontAndSize(S_NOMEPAGINA, font2, 8);

    hpdf.Page_BeginText(S_NOMEPAGINA);


    hpdf.Page_TextOut(S_NOMEPAGINA, 45,sLinea2,(tbResults.Data[nRow]['Nome']));
    hpdf.Page_TextOut(S_NOMEPAGINA, 100,sLinea2,(tbResults.Data[nRow]['Empresa']));
    hpdf.Page_TextOut(S_NOMEPAGINA, 200,sLinea2,(tbResults.Data[nRow]['Empregado']));
    hpdf.Page_TextOut(S_NOMEPAGINA, 300,sLinea2,(tbResults.Data[nRow]['Area']));
    hpdf.Page_TextOut(S_NOMEPAGINA, 375,sLinea2,(tbResults.Data[nRow]['HoraEntrada']));
    hpdf.Page_TextOut(S_NOMEPAGINA, 450,sLinea2,(tbResults.Data[nRow]['HoraSaida']));

    sLinea2 = sLinea2 - 12 ;
    hpdf.Page_EndText(S_NOMEPAGINA);


    if nRow == tbResults.Rows or sLinea2 < 59 then
    sLinea2 = 675
    break
    end


    end


    end

    StatusDlg.Hide ( )

    local res = hpdf.SaveToFile(Relatorio_pdf, _TempFolder.."\\Relatorio_"..vPDFRelatorio..".pdf" );
    hpdf.Free(Relatorio_pdf)
    if (res == 0) then
    File.Open(_TempFolder.."\\Relatorio_"..vPDFRelator io..".pdf", _TempFolder, SW_SHOWNORMAL);
    else
    Dialog.Message("ERRO", "Há um documento aberto com o mesmo nome.".."\r\n".."Salve e/ou feche o arquivo para gerar um novo.", MB_OK, MB_ICONSTOP);
    end
    end
    else

    Dialog.Message("ERRO", "Erro ao criar o documento PDF.", MB_OK, MB_ICONSTOP);

    end
    --end
    --db:close()

    Can anyone give me a light on where I am getting wrong in code?
    Thanks in advance for your patience and willingness to help this newbie.

  • #2
    Next time, please attach the exported project. It is very hard to read the code embedded in a post, with extra spaces, no indenting and no line numbers. There is no way to see where line 86 is in this post.

    Your code cannot work, because you have not decided which engine you want to use to access the database. Or you use LuaSQL, or you use a plugin. You cannot perform the SELECT using LuaSQL and a few lines later attempt to walk over the resultset using features offered by the SQLite3 plugin. Or you use the SQLite3 plugin for everything, or you need to use a cursor to process the resultset obtained via LuaSQL.

    Ulrich

    Comment


    • #3
      Dear Ulrich,
      Follow the apt file for you to review.
      Can you help me correct the beginner mistakes I made?
      Thank you for your attention and willingness to give up your business to help me.
      Thank you.
      Attached Files

      Comment


      • #4
        Dear Ulrich,
        I forgot to tell you that the application is to access, read and generate reports from a database generated for SQLite3. I've tried using SQlite, but it returns error saying that the database is not recognized or corrupted.
        Thank you.

        Comment


        • #5
          Okay, first please uninstall the current SQLite3 plugin you are using in AutoPlay Media Studio, as you were using code compatible with my version in your project, but using a plugin with the same name provided by a different developer. You can download my SQLite3 plugin from my web server: http://www.mindquake.com.br/en/actions/sqlite3 . You need the free version for this.

          If have corrected the scripts so now they work. I have commented out incorrect code and left the remaining fixing for you - have a look at the page count, which is wrong. If you loop over the results longer as there are records to fetch, you will get a runtime error.

          When you select a date which has at least one record in your database, you will see the result in the Grid object:
          Click image for larger version

Name:	SCRN-2017-02-14-01.png
Views:	189
Size:	13.5 KB
ID:	297587


          and upon clicking the button, the report is generated as PDF:
          Click image for larger version

Name:	SCRN-2017-02-14-02.png
Views:	94
Size:	79.5 KB
ID:	297588


          Ulrich

          Comment


          • #6
            Thanks for the help Ulrich, you took a huge weight off my head. I was already thinking of starting all over again.
            You're a beast!
            Thank you so much.

            Comment


            • #7
              The version of the SQLite3 plugin I use is the one that is available in the part of Indigo Rose plugins, and it's also free.
              But I'm already picking up on Mindquake.

              By the way, are you from Taboão da Serra?
              If, for me, I'm using the silly translator! ... I'm from MG.

              Comment


              • #8
                I am from Germany, but I live in Taboão da Serra, you are correct. While I understand Brazilian Portuguese, here we should keep the conversations in English.

                Ulrich

                Comment

                Working...
                X