{"flow":{"id":12,"summary":"Check for new users in postgressql, add to Airtable, send email to new users","versions":[23,26,27],"created_by":"rossmccrann","created_at":"2022-07-29T16:16:15.907Z","votes":0,"approved":false,"apps":["postgressql","airtable","smtp"],"value":{"modules":[{"value":{"path":"hub/39/send_get_request","type":"rawscript","content":"export async function main(url: string) {\n\n    let resp = await fetch(\n        url,\n        {\n            method: \"GET\",\n        },\n    );\n\n    let data = await resp.json();\n\n    let new_entries = {};\n    for (let i in data) {\n        new_entries[data[i][\"email\"]] = {\n            username: data[i][\"username\"],\n            name: data[i][\"name\"],\n        };\n    }\n    return new_entries;\n}","language":"deno"},"input_transform":{"url":{"type":"static","value":null}}},{"value":{"type":"rawscript","content":"import * as wmill from \"https://deno.land/x/windmill@v1.19.2/mod.ts\";\nimport { Client } from \"https://deno.land/x/postgres@v0.16.1/mod.ts\";\n\nexport async function main(pg_db: wmill.Resource<\"postgres\">, data: object) {\n  const client = new Client(pg_db);\n\n  await client.connect();\n\n  for (let i = 0; i < data.length; i++) {\n    let insert_query: string =\n      `INSERT INTO temp_user_table2 (email, username, name) VALUES (${\n        data[i][\"email\"]\n      }, ${data[i][\"username\"]}, ${data[i][\"name\"]});`;\n    await client.queryObject(insert_query);\n  }\n\n  const select_distinct_query =\n    \"SELECT DISTINCT on (email) email, username, name FROM temp_user_table1 temp_db WHERE NOT EXISTS (SELECT FROM temp_user_table2 main_db WHERE temp_db.email = main_db.email);\";\n  let users = await client.queryObject(select_distinct_query);\n\n  let users_dict = {};\n  for (let i = 0; i < users.rowCount; i++) {\n    users_dict[users.rows[i][\"email\"]] = {\n      username: users.rows[i][\"username\"],\n      name: users.rows[i][\"name\"],\n    };\n  }\n  for (let key in users_dict) {\n    const insert_query: string =\n      `INSERT INTO temp_user_table1 (email, username, name) VALUES (${key}, ${\n        users_dict[key][\"username\"]\n      }, ${users_dict[key][\"name\"]}) RETURNING lead_id, email, username;`;\n  }\n\n  return users_dict;\n}","language":"deno"},"input_transform":{"data":{"type":"static","value":null},"pg_db":{"type":"static","value":"$res:undefined"}}},{"value":{"path":"hub/65/create_multiple_records","type":"rawscript","content":"import { Airtable } from \"https://deno.land/x/airtable/mod.ts\";\nimport { Field } from \"https://deno.land/x/airtable/mod.ts\";\n\nexport async function main(at_con: wmill.Resource<\"airtable\">, at_base_table: wmill.Resource<\"airtable_table\">, new_record_list: object) {\n    const airtable = new Airtable({...at_con, ...at_base_table});\n    type Fields = {\n        Email: string;\n        Username: string;\n        Name: string;\n    };\n\n    const createMultiple = await airtable.create<Fields>(\n        new_record_list,\n    );\n\n    return new_record_list;\n}","language":"deno"},"input_transform":{"at_con":{"type":"static"},"at_base_table":{"type":"static"},"new_record_list":{"type":"static"}}},{"value":{"path":"hub/116/send_an_email","type":"rawscript","content":"import * as wmill from \"https://deno.land/x/windmill@v1.13.0/index.ts\";\nimport { SmtpClient } from \"https://deno.land/x/smtp/mod.ts\";\n\n/*\n@param: {wmill.Resource<\"email_smtp\">} smtp_con - Resource containing smtp config\nexample:\n{\n      hostname: \"smtp.mail.me.com\",\n      port: 465,\n      username: \"xxxxx@gmail.com\",\n      password: \"xxxxxxxx\",\n    }\n\n*/\n\nexport async function main(\n  smtp_con: wmill.Resource<\"email_smtp\">,\n  subject: string,\n  from_email: string,\n  to_email: string,\n  new_user_list: object,\n\n) {\n  let email_list = [];\n  for (let user_email in new_user_list) {\n    let content_message = `Hi ${\n      new_user_list[user_email][\"name\"]\n    }, \\nThank you for joining Windmill! \\nFrom Windmill Team`;\n\n    email_list.push(new_user_list[user_email][\"name\"]);\n\n    const client = new SmtpClient();\n\n    const connectConfig: any = smtp_con;\n    await client.connectTLS(connectConfig);\n\n    await client.send({\n      \"from\": from_email,\n      \"to\": user_email,\n      \"subject\": subject,\n      \"content\": content_message,\n    });\n  }\n\n  await client.close();\n\n  return { to: email_list };\n}\n","language":"deno"},"input_transform":{"message":{"type":"static","value":null},"subject":{"type":"static","value":null},"to_email":{"type":"static","value":null},"from_email":{"type":"static","value":null},"connectConfig":{"type":"static","value":"$res:undefined"}}}]},"schema":{"type":"object","$schema":"https://json-schema.org/draft/2020-12/schema","required":[],"properties":{}},"description":"Flow for when new users are created.\n\nStep 1: Hits web API and parses JSON data to create user objects.\n\nStep 2: Checks if users are in the postgres database and if not the new users are added.\n\nStep 3: Send record of new users to Airtable table\n\nStep 4: Send new users a welcome message to their email","recording":null,"vcreated_at":"2022-08-03T13:18:57.996Z","vcreated_by":"admin","comments":[]}}