Define msg.topic in function node

General
1

Hello everyone, need help to create one function node
below is the given flow

[{"id":"4476fe10.e2765","type":"simpletime","z":"1f149e22.43a482","name":"","mydate":true,"myymd":true,"myyear":true,"mymonth":true,"mymonthn":true,"mydom":true,"mydoy":true,"myday":true,"myhourpm":true,"myhour":true,"mytime":true,"mytimes":true,"myminute":true,"myminutes":true,"mysecond":true,"mymillis":true,"myepoch":true,"myrawdate":true,"mypm":true,"x":450,"y":960,"wires":[["ed99652.1402f98"]]},{"id":"ed99652.1402f98","type":"template","z":"1f149e22.43a482","name":"date","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"from 01-{{mymonthn}}-{{myyear}} to {{mydom}}-{{mymonthn}}-{{myyear}}","output":"str","x":610,"y":960,"wires":[["e352cec1.fb968"]]},{"id":"e352cec1.fb968","type":"function","z":"1f149e22.43a482","name":"date, time","func":"msg.payload = msg.payload;\nmsg.topic='date';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":760,"y":960,"wires":[["1d1920d3.2a95ef"]]},{"id":"e2a56fda.b415","type":"MSSQL","z":"1f149e22.43a482","mssqlCN":"898d2a6f.9fb7b8","name":"A, P, Q, OEE (all machines)","query":"SELECT \n(((SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT))- SUM (CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT)))/SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)))*100) \nAS availability,\n((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(COALESCE((((CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)-CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT))/(NULLIF(CAST([TOTAL STANDARD CYCLE TIME(Sec.)] AS FLOAT),0)))*60*CAST([PARTS PER CYCLE (U)] AS FLOAT)),0)))*100)\nAS performance,\n((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(CAST([TOTAL PRODUCTION] AS FLOAT)))*100) \nAS quality,\n(((((SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT))- SUM (CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT)))/SUM (CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)))*100) \n*((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(COALESCE((((CAST([MACHINE SHIFT TIME (X)(Min.)] AS FLOAT)-CAST([AVAILABILITY LOSS TIME(Min.)] AS FLOAT))/(NULLIF(CAST([TOTAL STANDARD CYCLE TIME(Sec.)] AS FLOAT),0)))*60*CAST([PARTS PER CYCLE (U)] AS FLOAT)),0)))*100)\n*((SUM(CAST([OK PRODUCTION] AS FLOAT)) / SUM(CAST([TOTAL PRODUCTION] AS FLOAT)))*100))/10000)\nAS oee\nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate())   \nAND datepart(yyyy,DATE) =year(getdate()) \n    AND [MACHINE CODE]='M02CN001'\n    OR [MACHINE CODE]='M02CN002'\n    OR [MACHINE CODE]='M02CN003'\n    OR [MACHINE CODE]='M02CN004'\n    OR [MACHINE CODE]='M02CN005'\n    OR [MACHINE CODE]='M02CN006'\n    OR [MACHINE CODE]='M02CN007'\n    OR [MACHINE CODE]='M02CN008'\n    OR [MACHINE CODE]='M02CN009'\n    OR [MACHINE CODE]='M02CN010'\n    OR [MACHINE CODE]='M02CN011'\n    OR [MACHINE CODE]='M02CN012'\n    OR [MACHINE CODE]='M02CN013'\n    OR [MACHINE CODE]='M02HB001'\n    OR [MACHINE CODE]='M02HB002'\n    OR [MACHINE CODE]='M02HB004'\n    OR [MACHINE CODE]='M02HB006'","outField":"payload","x":500,"y":860,"wires":[["78e7f81e.b0a038","a5820e64.dc55c","48ac6140.2c50c","17534980.bcb117","26cbf43b.40eaec","d95f858f.451bb8"]]},{"id":"78e7f81e.b0a038","type":"debug","z":"1f149e22.43a482","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":850,"y":860,"wires":[]},{"id":"5a910858.f025a8","type":"inject","z":"1f149e22.43a482","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":280,"y":860,"wires":[["e2a56fda.b415","4476fe10.e2765"]]},{"id":"a5820e64.dc55c","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"availability","label":"% age","format":"{{msg.payload[0].availability | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":850,"y":700,"wires":[]},{"id":"48ac6140.2c50c","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"performance","label":"% age","format":"{{msg.payload[0].performance | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":850,"y":740,"wires":[]},{"id":"17534980.bcb117","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"quality","label":"% age","format":"{{msg.payload[0].quality | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":830,"y":780,"wires":[]},{"id":"26cbf43b.40eaec","type":"ui_gauge","z":"1f149e22.43a482","name":"","group":"1c20b4b1.269adb","order":1,"width":"3","height":"3","gtype":"donut","title":"oee","label":"% age","format":"{{msg.payload[0].oee | number: 0}} %","min":0,"max":"100","colors":["#ff0000","#e6e600","#2ed600"],"seg1":"50","seg2":"70","x":830,"y":820,"wires":[]},{"id":"1d1920d3.2a95ef","type":"join","z":"1f149e22.43a482","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":930,"y":940,"wires":[["594910d.0957ef"]]},{"id":"d95f858f.451bb8","type":"function","z":"1f149e22.43a482","name":"","func":"msg.payload.availability=a;\nmsg.topic='availability';\nmsg.payload.performance=b;\nmsg.topic='performance';\nmsg.payload.quality=c;\nmsg.topic='quality';\nmsg.payload.oee=d;\nmsg.tpoic=e;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":760,"y":920,"wires":[["1d1920d3.2a95ef"]]},{"id":"594910d.0957ef","type":"template","z":"1f149e22.43a482","name":"mail","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Hello! This is an auto generated test mail from Node Red.\n<div>OEE for MT-2's Small Shed from date {{payload.date}} is observed to be {{d}} %</div>\n<div>where</div>\n<div>Availability = {{a}} % </div>\n<div>Performance = {{a}} % </div>\n<div>Quality = {{c}} % </div>","output":"str","x":1050,"y":940,"wires":[["deb014af.7400a8"]]},{"id":"deb014af.7400a8","type":"debug","z":"1f149e22.43a482","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1190,"y":980,"wires":[]},{"id":"898d2a6f.9fb7b8","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT02","server":"192.168.21.169\\SCADASQL","port":"1433","encyption":false,"database":"MT02","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"},{"id":"1c20b4b1.269adb","type":"ui_group","name":"Group 2","tab":"5074feb8.5be66","order":3,"disp":true,"width":6},{"id":"5074feb8.5be66","type":"ui_tab","name":"home","icon":"dashboard","order":13,"disabled":false,"hidden":false}]

I don't want to use multiple function nodes can any one help. Also I'm not able to make the first function node to work as my coding skills are not good.

screenshot 5
screenshot 5774×245 19 KB

2

Hi sahsha,

In future you should probably provide more info on what the issue is and what you are trying to do.

Anyhow, I recognise what you are trying to do so i will explain for future -

To avoid having to use join nodes (where not necessary), instead of branching off to separate paths, put everything in series e.g...

inject ->> Database >> move payload somewhere safe >> convert times >> build mail >> send mail.

As I dont have simpletime node, i just did the from + to dates in the function...

image
image1032×258 23.8 KB

Result...
image

flow...

[{"id":"34522b81.0f6f24","type":"inject","z":"167a2e98.ad02c1","name":"demo data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"oee\":78.6,\"quality\":94,\"performance\":92,\"availability\":91}]","payloadType":"json","x":840,"y":860,"wires":[["d95f858f.451bb8"]]},{"id":"d95f858f.451bb8","type":"function","z":"167a2e98.ad02c1","name":"add dateStart and dateEnd","func":"//calculate 1st and last day of month\nvar date = new Date(), y = date.getFullYear(), m = date.getMonth();\nvar firstDay = new Date(y, m, 1);\nvar lastDay = new Date(y, m + 1, 0);\n\n//format date to dd-mmm-yyyy\nmsg.dateStart = formatDate(firstDay);\nmsg.dateEnd = formatDate(lastDay);\n\nmsg.payload = msg.payload[0]; //move 1st data row into payload\n\nreturn msg;\n\n\nfunction formatDate(d) {\n    let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n    let mo = new Intl.DateTimeFormat('en', { month: 'short' }).format(d);\n    let da = new Intl.DateTimeFormat('en', { day: '2-digit' }).format(d);\n    return `${da}-${mo}-${ye}`;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1170,"y":820,"wires":[["594910d.0957ef"]]},{"id":"594910d.0957ef","type":"template","z":"167a2e98.ad02c1","name":"mail","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Hello! This is an auto generated test mail from Node Red.\n<div>OEE for MT-2's Small Shed from '{{dateStart}}' to '{{dateEnd}}' is observed to be {{payload.oee}} %</div>\n<div>where</div>\n<div>Availability = {{payload.availability}} % </div>\n<div>Performance = {{payload.performance}} % </div>\n<div>Quality = {{payload.quality}} % </div>","output":"str","x":1390,"y":820,"wires":[["deb014af.7400a8"]]},{"id":"deb014af.7400a8","type":"debug","z":"167a2e98.ad02c1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1550,"y":820,"wires":[]}]
3

@Steve-Mcl Thankyou soo much. Will keep in mind to describe the problem briefly.

I also need function node to give date as below, but will try it myself & if I fail then will request for help again :pleading_face:

  1. current date
  2. fiscal year i.e. from 'April 2021' to 'March 2022'.
4

Here is a function you can use inline (in series)

It adds msg.dates to the msg object (doesn't alter or change other msg properties like payload)

You can access things like msg.dates.thisMonth.start in a function or you can access values in a template like this FY from {{dates.lastFY.start}} to {{dates.lastFY.end}}

here is a demo...
image

image
image385×543 15.1 KB 

[{"id":"9a4615e5.dbc3f8","type":"inject","z":"45563bb1.635ce4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":540,"y":560,"wires":[["ce281cd5.8b903"]]},{"id":"ce281cd5.8b903","type":"function","z":"45563bb1.635ce4","name":"Get dates","func":"var date = new Date(), y = date.getFullYear(), m = date.getMonth();\nvar aprThisYear = new Date(y, 3, 1);\nvar marNextYear = new Date(y+1, 2, 1);\nvar aprLastYear = new Date(y - 1, 3, 1);\nvar marThisYear = new Date(y, 2, 1);\nvar firstDayOfThisMonth = new Date(y, m, 1);\nvar lastDayOfThisMonth =  new Date(y, m + 1, 0);\nvar firstDayOfLastMonth = new Date(y, m - 1, 1);\nvar lastDayOfLastMonth = new Date(y, m, 0);\n\n\nmsg.dates = {};\n\nmsg.dates.datetime = date;\nmsg.dates.date = formatDateDDMMMYYYY(date);\n\n\nmsg.dates.thisMonth = {\n    start : formatDateDDMMMYYYY(firstDayOfThisMonth),\n    end: formatDateDDMMMYYYY(lastDayOfThisMonth),\n}\n\nmsg.dates.lastMonth = {\n    start: formatDateDDMMMYYYY(firstDayOfLastMonth),\n    end: formatDateDDMMMYYYY(lastDayOfLastMonth),\n}\n\nmsg.dates.thisFY = {\n    start : formatDateMMMMYYYY(aprThisYear),\n    end : formatDateMMMMYYYY(marNextYear)\n}\n\nmsg.dates.lastFY = {\n    start : formatDateMMMMYYYY(aprLastYear),\n    end : formatDateMMMMYYYY(marThisYear)\n}\n\nfunction formatDateMMMMYYYY(d) {\n    let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n    let mo = new Intl.DateTimeFormat('en', { month: 'long' }).format(d);\n    return `${mo}-${ye}`;\n}\n\nfunction formatDateDDMMMYYYY(d) {\n    let ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);\n    let mo = new Intl.DateTimeFormat('en', { month: 'short' }).format(d);\n    let da = new Intl.DateTimeFormat('en', { day: '2-digit' }).format(d);\n    return `${da}-${mo}-${ye}`;\n}\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":560,"wires":[["44619f3b.d76f4","a3542e90.5052"]]},{"id":"44619f3b.d76f4","type":"debug","z":"45563bb1.635ce4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"dates","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":560,"wires":[]},{"id":"a3542e90.5052","type":"template","z":"45563bb1.635ce4","name":"demo usage","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Todays date is '{{dates.date}}'\n\nThis FY starts '{{dates.thisFY.start}}' and ends '{{dates.thisFY.end}}'\nLast FY starts '{{dates.lastFY.start}}' and ends '{{dates.lastFY.end}}'\n\nThis month starts '{{dates.thisMonth.start}}' and ends '{{dates.thisMonth.end}}'\nLast month starts '{{dates.lastMonth.start}}' and ends '{{dates.lastMonth.end}}'","output":"str","x":750,"y":640,"wires":[["70110b99.d28b24"]]},{"id":"70110b99.d28b24","type":"debug","z":"45563bb1.635ce4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":930,"y":640,"wires":[]}]
5

@Steve-Mcl, Thanks again. couldn't have done it with you.

6

@Steve-Mcl , one more question if I want to get previous day date. Then am I doing it right?
:grimacing:
check below image
screenshot 6

7

No. You need to use the date functions...

var yesterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);
8

@Steve-Mcl Arigatogozaimashita, Sensei .... :pray:

9

itashimashite

10

A post was split to a new topic: Combine queries and send email

11

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.