Script to create database entries, folders in iCloud, a folder in Mail, and a set of labels for a new project.

newproject

#!/usr/bin/python

import sqlite3
import os
import sys
from applescript import asrun
from subprocess import Popen, PIPE
import shutil
import requests
import json

# Initialize SQLite database, query, and parent projects directory.
pf = '{}/Library/Mobile Documents/com~apple~CloudDocs/projects'.format(os.environ['HOME'])
pl = '{}/projects.db'.format(pf)
cloudpf = '{}/Library/Mobile Documents/iCloud~com~omz-software~Pythonista3/Documents'.format(os.environ['HOME'])
cloudpl= '{}/projects.db'.format(cloudpf)
db = sqlite3.connect(pl)
query = 'insert into projects values(?, ?, ?, ?, ?, ?, ?, ?)'
pflabels = '{}/Dropbox/bin/pflabels'.format(os.environ['HOME'])

# Initialize Airtable request.
airURL = 'https://api.airtable.com/v0/blahblahblah/projects'
airHeaders = {
	'Authorization': 'Bearer blahblahblah',
	'Content-Type': 'application/json'}


# AppleScript template for getting project contact info.
cScript = '''
tell application "Contacts"
  set match to first item of (people whose {})
  return value of item 1 of (emails of match)
end tell
'''

# AppleScript template for creating new mailbox.
mbScript = '''
tell application "Mail"
	tell account "FastMail"
		make new mailbox with properties {{name:"{}"}}
	end tell
end tell
'''

# Get info from user.
name = raw_input("\033[1mProject name:         \033[22m").decode('utf8')
number = int(raw_input("\033[1mProject number:       \033[22m"))
client = raw_input("\033[1mClient name:          \033[22m").decode('utf8')

# Use client's known email address if in Contacts.
# Otherwise, ask for it.
nameclause = 'name contains "' + \
             '" and name contains "'.join(client.split()) + '"'
nameclause = nameclause.encode('utf8')
try:
  email = asrun(cScript.format(nameclause)).strip()
except RuntimeError:
  email = raw_input("\033[1mClient Email:         \033[22m")

ref = raw_input("\033[1mClient reference:     \033[22m")
subdir = raw_input("\033[1mSubdirectory:         \033[22m")
photos = raw_input("\033[1mPhotos?[y/n]:         \033[22m")[0].lower()
small = raw_input("\033[1mSmall project?[y/n]:  \033[22m")[0].lower()
rc = raw_input("\033[1mLabel position [r,c]: \033[22m")

# Add record to Airtable database.
payload = {'fields': {
	'name': name,
	'number': number,
	'client': client,
	'clientref': ref,
	'directory': subdir,
	'box': 0,
	'email': email}}
r = requests.post(airURL, headers=airHeaders, json=payload)
try:
	print "New record created at {}".format(r.json()['createdTime'])
except KeyError:
	print "Airtable error: {}".format(r.json()['error']['message'])
	sys.exit()

# Add record to SQLite projects database.
project = (name, number, client, ref, subdir, 0, email, '')
test = db.execute(query, project)
db.commit()
db.close()

# Copy the database file to the iCloud Pythonista folder.
shutil.copyfile(pl, cloudpl)

# Create directories.
os.makedirs('{}/{}/invoices'.format(pf, subdir), 0755)
if photos == 'y':
  os.makedirs('{}/{}/Photos'.format(pf, subdir), 0755)

# Make a mailbox folder.
asrun(mbScript.format(name))

# Parse the label starting position.
row, col = rc.split(',')

# Build the input data for the labels. Always make a label for the
# accounting folder. If a small project, make only one label for all
# file material. If not a small project, make labels for the notes
# folder and the separator tab 
labeldata = "#{}|{}\n  \n\n".format(name.encode('utf8'), number)
if small =='y':
  labeldata += "Notes\n";
else:
    labeldata += "Notes\n"
    labeldata += "\n#\\0\\0\\0{}|{}\\0\\0\\0\n".format(name.encode('utf8'), number)

# Make the labels.
proc = Popen([pflabels, '-r', row, '-c', col], stdin=PIPE)
proc.communicate(labeldata)