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
Hello Dale,

Looks like the only way to do it is to use a stored procedure and a temporary table:
1) Stored procedure fills the temporary table with records - one record for each label
2) The report is printed from this temporary table
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Dale Accepted Answer
Uhmmm... I don't know if this is the right avenue to ask for help but I am not good in DB handling. I would be glad if you can point me to a sample stored proc that populates a temporary table from a field value of another table?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
You're generating reports from Xojo, right? Maybe (for the start) it will be simpler to populate the intermediate table in Xojo code.
Also, how do you get the quantities for each item? I'll try to create an example to do this task entirely in the Valentina Studio.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Dale Accepted Answer
The quantities are entered from a Xojo app and they are stored in a mysql table.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Dale Accepted Answer
Sergey, should this offers further info, here's the structure of the source table:

CREATE TABLE `purrrdetails` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`RRHeaderID` int(11) NOT NULL COMMENT 'Taken fron bw.PurRR\nHeader.ID',
`InvItemID` int(11) NOT NULL COMMENT 'Taken fron bw.InvItems.ID\n',
`UnitID` int(11) DEFAULT NULL COMMENT 'Taken fron cw.Units.ID\n',
`PackingID` int(11) DEFAULT NULL COMMENT 'Taken fron cw.Units.ID\n',
`UnitCost` decimal(12,2) DEFAULT NULL,
`Qty` decimal(12,4) NOT NULL DEFAULT '1.0000',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Receiving Report composition';

Thank you.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 9
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 10
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 11
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 12
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 13
Dale Accepted Answer
tmpQRLabels is the temporary table that was created using your code that I customized.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 14
Sergey Pashkov Accepted Answer
Have you defined a default value for this parameter in the Valentina Studio?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 15
Dale Accepted Answer
Yes, I followed your screenshots, en toto.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 16
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. more than a month ago
  2. Valentina Studio
  3. # 17
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. more than a month ago
  2. Valentina Studio
  3. # 18
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. more than a month ago
  2. Valentina Studio
  3. # 19
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.
  1. more than a month ago
  2. Valentina Studio
  3. # 20
  • 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. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories