Auto generate Graph file from MySQL database To PDF when a PLC tag goe

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 854 Views - Last Post: 02 July 2019 - 02:26 PM

#1 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 24 June 2019 - 09:24 PM

When an operator begins a wash of a tank I need to track temperature of water used. This will be saved to a mysql database. I would like from there to generate a PDF file of this information in the form of graph. This should only generate when a tag in the PLC goes high.

This is for a ice cream plant which cleans there tanks via a CIP system. They are running FactoryTalk View and have looked into Crystal and SSRS but I would like a more lightweight option

I would like something lightweight, serverside, like PHP or C#.Net if possible. But am open to any options.

Just to clearify I am not looking at someone giving me code but more an idea as to what would be the best option to do this. As I am aware if I do this based off of a webpage that page needs to be opened all the time. However, this is not what I want but rather a server running in the background doing all the work for me. Would it be best if I went C#.Net way or more Python with Flask?

Is This A Good Question/Topic? 0
  • +

Replies To: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15168
  • View blog
  • Posts: 60,699
  • Joined: 12-June 08

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 07:04 AM

Quote

This should only generate when a tag in the PLC goes high.

Hopefully this isn't a warning system, right?


Given that information, and that this isn't a real time warning system for being too high of a temp, a c# app that runs in the background every X minutes with the 'task scheduler' would do fine.

App loads, skims the db for any high values over X time, verifies the sql id for those events are not in a 'done' table, pulls the data, formats it, writes out the pdf, and adds the key to the 'done' table, and exits.

Plenty of ways to write a PDF.. using the 'reporting services', or mock it all up in HTML and feed that into something like NReco's html to pdf.
Was This Post Helpful? 0
  • +
  • -

#3 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 01:16 PM

No no just a reporting graph which should show me the information. Is there a way that I can have this running all the time in the background. Also I shoul dmention there could be up to six of these things running at a time so I would need possibly 6 different reports being saved at different times depending on whats finishing first.

This post has been edited by Skydiver: 25 June 2019 - 05:28 PM
Reason for edit:: Removed unnecessary quote. No need to quote the post above yours.

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15168
  • View blog
  • Posts: 60,699
  • Joined: 12-June 08

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 01:18 PM

That's the joy of a scheduled task. You can have some app called indefinitely ever X amount of time. I have nearly a hundred or so running on our server that range from run every ten minutes, once a day, or once a week.
Was This Post Helpful? 0
  • +
  • -

#5 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 01:39 PM

And you dont have any performance issues? If say I ran them every 10 seconds?

This post has been edited by Skydiver: 25 June 2019 - 05:29 PM
Reason for edit:: Removed unnecessary quote. No need to quote the post above yours.

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15168
  • View blog
  • Posts: 60,699
  • Joined: 12-June 08

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 01:43 PM

Ten seconds, seems.. oddly small window. Remember the idea is the app collects all the data in a DB from a given ID, graphs it, formats it, and saves it out. I am certain a tank doesn't take 10 seconds to wash out. I would think something more reasonable would be better suited.
Was This Post Helpful? 0
  • +
  • -

#7 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 02:19 PM

Ok that is true. Would you say this is a better approach then just running something in the background all the time? I don't even know if that is possible. But maybe something running in the background that's constantly checking.

This post has been edited by Skydiver: 25 June 2019 - 05:30 PM
Reason for edit:: Removed unnecessary quote. No need to quote the post above yours.

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15168
  • View blog
  • Posts: 60,699
  • Joined: 12-June 08

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 02:44 PM

Well.. what I proposed is, for all intent and purpose, running all the time.

Again, and you never really clarified, this is _NOT_ for some sort of real time alert or notification app, right? Just a report dumping out data if a criteria is met?
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6985
  • View blog
  • Posts: 23,756
  • Joined: 05-May 12

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 25 June 2019 - 05:40 PM

brandonm222: There is no need to quote the post above yours. Just use the big Reply button or the Fast Reply area. If you must quote, trim down to the specific thing you want to address.

That's really odd, a .PDF file generated every 10 seconds. So you get 6 PDF a minute, 360 PDFs files an hour, 8640 PDFs per day. Recall that PDF files are big when they contain graphics, even vector graphics for a graph. So the small amount of bytes that you stashed away in the MySQL file gets duplicated and magnified in a practically geometric manner of tens if not hundreds of kilobytes to create a PDF.

My recommendation is to keep the data is MySQL and only generate the PDF on demand.

Also, consider using Splunk. It does wonders for aggregating and visualizing logging data -- which is basically what you have. Furthermore, it also has very sophisticated alerting functions so you can configure it to look for particular patterns and or trends in the log data and either send you alerts, generate graphs, etc.
Was This Post Helpful? 0
  • +
  • -

#10 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 12:01 AM

Thank you for that information I will definetly look into Splunk it may be exactly what I need. However, just to clarify my question I do not want to create a pdf every 10 seconds but rather check if my condition in the PLC is met. To give you the exact scenario there will be 6 CIP systems these systems clean different parts of a ice cream plant. Just for demonstration purposes lets say CIP 1 cleans Tank A, CIP 2 cleans Tank B, CIP 3 cleans Tank C, CIP 4 cleans the Ice cream jug filler, CIP 5 cleans the truck bringing in the milk, and CIP 6 cleans the HTST where the whole process of making ice cream happens. So now a completely other program will begin recording the CIPs cleaning cycle (Tempurature, Flow, Conductivity) into its designated database CIP1_Table, CIP2_Table... and so on. Now these CIPs will only start recording to the database when it begins washing and a wash can take 10 minutes or 4 hours some even a day just depends on the type of wash and if there are no issues during the wash and also they can start the different CIPs all at different times or together or just one all depends what they used that day. Now my program I want it to check every 10 seconds or a minute depending on overhead and see if the CIP is complete if it is, it will know because it will read a tag from the PLC telling it the CIP is complete and once it sees this tag it should then and only then create a report in PDF (report should show 3 seperate graphs for each piece Temp, Flow, Conductivity) and then send a flag or tag to the PLC telling it the report is done. This is why I would rather the program either run as quick as possible or always as they can't start a new wash until that report is done. I hope that makes sense. And sorry for using the quoting wrong. Thank you again to both of you for helping me out with this. Also for the C# program would you use a object oriented one or just a terminal program.

This post has been edited by Skydiver: 26 June 2019 - 06:13 AM
Reason for edit:: Removed unnecessary quote...again. There is no need to quote the post above yours.

Was This Post Helpful? 0
  • +
  • -

#11 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6985
  • View blog
  • Posts: 23,756
  • Joined: 05-May 12

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 02:54 PM

View Postbrandonm222, on 26 June 2019 - 03:01 AM, said:

Now my program I want it to check every 10 seconds or a minute depending on overhead and see if the CIP is complete if it is, it will know because it will read a tag from the PLC telling it the CIP is complete and once it sees this tag it should then and only then create a report in PDF (report should show 3 seperate graphs for each piece Temp, Flow, Conductivity) and then send a flag or tag to the PLC telling it the report is done. This is why I would rather the program either run as quick as possible or always as they can't start a new wash until that report is done. I hope that makes sense.

That doesn't make sense. Why can't a new wash start until a report is done?

First of all: Generating a report shouldn't take that long unless:
- your database is slow
- your connection to you database is slow
- your connection to the file system where the PDF will be written to is slow
- your machine is slow
If any of the above is true, then you likely have other issues.

Second: If data is logged into the respective databases are appropriately timestamped, then the first step of generating the report is simply getting the data from the time the last report was generated to the time the PLC sends the signal. If additional data comes in while the report is being generated it doesn't matter because the report is only about a particular time span. The only thing that I can think off is that somebody designed the system such that after a report is generated, the database table is truncated. Don't do that.

View Postbrandonm222, on 26 June 2019 - 03:01 AM, said:

So now a completely other program will begin recording the CIPs cleaning cycle (Tempurature, Flow, Conductivity) into its designated database CIP1_Table, CIP2_Table... and so on.

Is there any particular reason why you have 6 different tables? Why can't you just have a single table which has a column that includes the CIP number?
Was This Post Helpful? 0
  • +
  • -

#12 brandonm222   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 18-October 14

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 02:57 PM

The reason they can't start a new wash is they need to verify on that report that temperatures, flows and conductivity were reached. If not that same wash needs to be done again. It is one of the requirements for a production plant.

Second generating the report shouldnt take more then a couple of seconds really. I don't understand where you are seeing that it is taking a while. I just need to wait for the information in the database and then the flag from the plc to complete. It is just a matter of how long the wash is going to be.

Finally, my thought was to truncate the report once complete. Why would you say that is a bad way of going? Once the report is made I do not need that information in the database.

Again thank you for all the information up until now it is helping me a lot.
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15168
  • View blog
  • Posts: 60,699
  • Joined: 12-June 08

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 03:02 PM

My usual MO is ALWAYS keep your data in your DB.. for backups, historical review, and so on. Substantially better to see the hard minted data later than try and go through a bajillion reports.

This also seems to want a fancy dashboard with pretty gauges.. and a flashing 'warning!' if something is out of spec. hmmm.. gauges.
Was This Post Helpful? 0
  • +
  • -

#14 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6985
  • View blog
  • Posts: 23,756
  • Joined: 05-May 12

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 03:13 PM

View Postbrandonm222, on 26 June 2019 - 05:57 PM, said:

The reason they can't start a new wash is they need to verify on that report that temperatures, flows and conductivity were reached. If not that same wash needs to be done again. It is one of the requirements for a production plant.

So the long end of the pole here is not the report generation, but rather some human noticing that a report was generated, and approving/accepting it before the new wash can start. You didn't state that previously. All you stated was that a new wash couldn't start until a report was generated.

View Postbrandonm222, on 26 June 2019 - 05:57 PM, said:

Second generating the report shouldnt take more then a couple of seconds really. I don't understand where you are seeing that it is taking a while.

I wasn't seeing generating the report as taking a long time. That's why I said:

View PostSkydiver, on 26 June 2019 - 05:54 PM, said:

First of all: Generating a report shouldn't take that long unless:
:


I was only pointing out those conditions because in your previous posts you were really concerned about the system being able to poll at the rate of at least every 10 seconds. There must have been something that was bring up this concern for you.

Frankly, I don't quite see the value of being able to poll at that rate, if you're going to have to wait for a human to notice that a report was generated, and for them to review the report. Unless the human has a pager attached to his belt, or the only job of the plant operator is to sit in front of a screen with gauges and wait for the report to come in, does it really matter if the report is generated instantly or at the top of the minute?

Also if the human just takes cursory look at the graphs and then accepts the reports to allow the new wash to continue, then yeah, I can see the time from generating a report, to seeing a report available, to scanning the graphs, to accepting it taking less than 20 seconds, if not less than 10 seconds. But if all the operator is really looking for is are particular "bad shapes or spikes" in the graph, why not simply train an AI to read the raw data and provide alerts earlier in the process instead of waiting for the PDF report?
Was This Post Helpful? 0
  • +
  • -

#15 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6985
  • View blog
  • Posts: 23,756
  • Joined: 05-May 12

Re: Auto generate Graph file from MySQL database To PDF when a PLC tag goe

Posted 26 June 2019 - 03:19 PM

Or is that AI idea the thing you are holding in your backpocket when you are angling for a raise or year end bonus?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2