1. Dale
  2. Valentina Studio
  3. Friday, December 07 2018, 07:10 AM
  4.  Subscribe via email
Hi,

I created a label to print qr code from a receiving list. This receiving list has quantity of items received. From there, I need to print the number of qr codes per item. For example, I have the following;

Items Quantity
------- ------------
Item A 15
Item B 10
Item C 25

Although I only have 3 items, I need to print 50 qr codes total (15 for item a, 10 for item b and 25 for item c). How to do this on Valentina Studio Pro?
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
You're welcome!
Comment
There are no comments made yet.
Dale Accepted Answer
My Dear Sergey,

Well, I'll be damned! You hit the nail on the head by suggesting to pass an explicit value instead of a variable. I just found out that I did not handle well the variable used to pass for the parameter to catch. Now my report works! You are a God-send, Sergey! Loads of thanks to you!!!
Attachments (1)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hi Dale,

Thank you for the details, I think I recreated your structure but it still works for me from Xojo.

The report query is not executed in the MakeNewReport, but only when it is necessary - like getting the number of pages or printing, so it should not be a problem.

Have you tried also to pass a constant value to parameter, like:
mReport.SetParameterValue("pHeaderID", "1" )
Comment
There are no comments made yet.
Dale Accepted Answer
Update: I tried changing the parameter pHeaderID value to "1" in Valentina Studio,still the result shows a blank report.
Comment
There are no comments made yet.
Dale Accepted Answer
It is working from Xojo if I revert to [SQL] in the report's pre-build with the statement "Call genqrlabels(1)". The thing is, I have to provide an explicit value as a parameter for the stored proc. If I switch to [Javascript] with this statement:

report.datasource.sqlSelect( "CALL genqrlabels(" + report.parameterValue( "pHeaderID" ) + ");" );


... this one works from Valentina Studio because I provide the value of pHeaderID at runtime. From xojo, it shows nothing. I suspect that the parameter passing from Xojo does not get into consideration during the initialization of the report in the statement:

mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)


This is an excerpt from my xojo code:


mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)

mReport.SetParameterValue("cCompany",ReportModule.CompanyName)
mReport.SetParameterValue("cAddress",ReportModule.CompanyAddress)
mReport.SetParameterValue("cEmail",ReportModule.CompanyEmail)
mReport.SetParameterValue("cWebsite",ReportModule.CompanyWebsite)
mReport.SetParameterValue("cLandLine",ReportModule.CompanyLandline)
mReport.SetParameterValue("cMobile",ReportModule.CompanyMobile)
mReport.SetParameterValue("cTIN",ReportModule.CompanyTIN)
mReport.SetParameterValue("pHeaderID",Str(me.HeaderID))


pHeaderID is passed AFTER the creation of the report not before, so I think the pre-build takes the default value instead and use it and cannot find the HeaderID value of "333" (your given default). Thus the report is blank.
Comment
There are no comments made yet.
Dale Accepted Answer
Sergey, this is the stored proc:


CREATE DEFINER=`root`@`localhost` PROCEDURE `genqrlabels`(IN pHeaderID INT(11))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE vitemcode varchar(50) DEFAULT "";
DECLARE vdescription varchar(100) DEFAULT "";
DECLARE vuniqueid varchar(20) DEFAULT "";
DECLARE vexpiry varchar(10);
DECLARE vqtyreceived decimal(12,4);
DECLARE vqrlabel varchar(250) DEFAULT "";

DECLARE i INTEGER DEFAULT 0;
DECLARE rrdetails_cursor CURSOR FOR
SELECT itemcode,description,uniqueid, DATE_FORMAT(expiry, '%Y-%m-%d') AS expiry,qtyreceived,Concat('Item Code: ',itemcode,'\n','Desc: ',Description,'\n','Batch ID: ',uniqueid,'\n','Expiry: ',DATE_FORMAT(expiry, '%Y-%m-%d')) AS qrlabel FROM bw01.vwrrdetails Where HeaderID = pHeaderID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

DROP TABLE IF EXISTS tmpQRLabels;
CREATE TEMPORARY TABLE tmpQRLabels
(itemcode VARCHAR(50),description varchar(100),uniqueid varchar(20),expiry varchar (10),qtyreceived decimal(12,4),qrlabel varchar(200));
#CREATE TEMPORARY TABLE tmpQRLabels SELECT * FROM tblItems LIMIT 0;

OPEN rrdetails_cursor;
get_item: LOOP
FETCH rrdetails_cursor INTO vitemcode,vdescription,vuniqueid,vexpiry,vqtyreceived,vqrlabel;

IF v_finished = 1 THEN LEAVE get_item; END IF;

SET i = 1;
WHILE i <= vqtyreceived DO
INSERT INTO tmpQRLabels VALUES( vitemcode,vdescription,vuniqueid,vexpiry,vqtyreceived,vqrlabel);
SET i = i + 1;
END WHILE;
END LOOP get_item;

CLOSE rrdetails_cursor;
END
Comment
There are no comments made yet.
Dale Accepted Answer
Oh, sorry, please ignore the latest post I made above. I wanted to edit one of my previous posts but instead it created a new one. Sorry.
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 7
Dale Accepted Answer
My code (and my approach) works with simple reports. It doesn't in this particular case. The only difference is that, with this one I used stored proc plus other instructions that you suggested. This app I am developing is a web app so a temporary table is a must unless I can find another way how to print multiple qr codes from a simple parent - children tables.

I am near to accomplish this thing since it is already working in Valentina Studio.
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 8
Sergey Pashkov Accepted Answer
And what is the difference with the simpler report?

I'll check what could be wrong.
Also, if you need this report as soon as possible, you can call this procedure in Xojo code before MakeNewReport, but the table should not be TEMPORARY in that case.
Comment
There are no comments made yet.
  1. one week ago
  2. Valentina Studio
  3. # 9
Dale Accepted Answer
Yes, I followed your screenshots, en toto.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Have you defined a default value for this parameter in the Valentina Studio?
Comment
There are no comments made yet.
Dale Accepted Answer
tmpQRLabels is the temporary table that was created using your code that I customized.
Comment
There are no comments made yet.
Dale Accepted Answer
Sergey, thank you for bearing with me all this time. The parameter now works in VS Pro but from Xojo, it shows a blank report. I guess there's a problem with my parameters calling the MakeNewReport project method. This is how I do it:

mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)

This works on other simple report that I already made but not on this one. No error generated, except it shows nothing. Probably there is a problem with the strSelect. At runting strSelect has this value: "select * from tmpQRLabels". Is this correct?
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Yes, it is possible with JavaScript, just switch the type of pre_build script to JavaScript and write the following code:
report.datasource.sqlSelect( "CALL genqrlabels(" + report.parameterValue( "pHeaderID" ) + ");" );


To test it in the Valentina Studio, you can add this parameter on the left panel and make it interactive.
Attachments (2)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hi Dale,

Only the string is accepted in this method.
I think a bit of JavaScript will be necessary, I’ll give an example
Comment
There are no comments made yet.
Dale Accepted Answer
I tried doing this from Xojo:

mReport.SetParameterValue("pHeaderID",me.HeaderID)

I've got this error:
ModalPrint.GenerateReport, line 139
Parameter "inValue" expects type String, but this is type Integer.
mReport.SetParameterValue("pHeaderID",me.HeaderID)
Comment
There are no comments made yet.
Dale Accepted Answer
If I placed a literal value, like, "call genqrlabels(2)" now the report runs. How can I pass a header ID from Xojo?
Comment
There are no comments made yet.
Dale Accepted Answer
Yes, Sergey, the quantities are stored from the source table... and your code, wow! Really, thanks to you I am making huge improvements. I customized your code and I already managed to run the script from mysql itself and also from Valentina Studio Query Builder. I even made the stored proc parameterized so that only the detail records of a particular receiving report can be retrieved. However, I still cannot run the report. What is the syntax for including a parameter in the pre-build? I put "call genqrlabels(pHeaderID)" where gengrlables is the stored proc name and pHeaderID should be the ID of the header/parent record.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Didn't use your structure in the script.
Are the quantities stored in another table?
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
I created an example to do it entirely in the Valentina Studio.

1. I added the stored procedure.
It creates a temporary table with the same structure as original and fills it.


CREATE PROCEDURE create_source_table()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE id varchar(100) DEFAULT "";
DECLARE quantity INTEGER DEFAULT 0;
DECLARE i INTEGER DEFAULT 0;
DECLARE items_cursor CURSOR FOR SELECT * FROM tblItems;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

DROP TABLE IF EXISTS tblLabelsSource;
CREATE TEMPORARY TABLE tblLabelsSource SELECT * FROM tblItems LIMIT 0;

OPEN items_cursor;
get_item: LOOP
FETCH items_cursor INTO id, quantity;

IF v_finished = 1 THEN LEAVE get_item; END IF;

SET i = 1;
WHILE i <= quantity DO
INSERT INTO tblLabelsSource VALUES( id, quantity );
SET i = i + 1;
END WHILE;
END LOOP get_item;

CLOSE items_cursor;
END;


2. Checked that it works:


CALL create_source_table();


3. Created a query based on the original table: SELECT * FROM tblItems

4. Created a report with this query

5. Changed query to SELECT * FROM tblLabelsSource

6. Changed "pre_build" script of the report to call the procedure (screenshot)

Now before building each report tblLabelsSource is filled with data.
Attachments (1)
Comment
There are no comments made yet.
  • Page :
  • 1
  • 2


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories