1. Dale
  2. Valentina Studio
  3. Пятница, Декабрь 07 2018, 07:10 AM
  4.  Подписаться через 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?
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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.
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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" )
Комментарий
There are no comments made yet.
Dale Ответ принят
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!!!
Вложения
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
You're welcome!
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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
Комментарий
There are no comments made yet.
Dale Ответ принят
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?
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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.
Комментарий
There are no comments made yet.
Dale Ответ принят
The quantities are entered from a Xojo app and they are stored in a mysql table.
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Didn't use your structure in the script.
Are the quantities stored in another table?
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Dale Ответ принят
If I placed a literal value, like, "call genqrlabels(2)" now the report runs. How can I pass a header ID from Xojo?
Комментарий
There are no comments made yet.
Dale Ответ принят
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)
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hi Dale,

Only the string is accepted in this method.
I think a bit of JavaScript will be necessary, I’ll give an example
Комментарий
There are no comments made yet.
Dale Ответ принят
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?
Комментарий
There are no comments made yet.
Dale Ответ принят
tmpQRLabels is the temporary table that was created using your code that I customized.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Have you defined a default value for this parameter in the Valentina Studio?
Комментарий
There are no comments made yet.
Dale Ответ принят
Yes, I followed your screenshots, en toto.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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.
Комментарий
There are no comments made yet.
  • Страница :
  • 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