from pymongo import MongoClient

# For every project located in "Stafford", list the project number, the 
# controlling department number, and the department manager's last name, 
# birth date, and address.

def query(projs,depts,emps):
  result = []
  query = {}
  query["plocation"] = "Stafford"
  ps = list(projs.find(query,{"pnumber": 1, "controllingDepartment": 1, "_id": 0}))
  for p in ps:
    pno = int(p["pnumber"])
    dno = int(p["controllingDepartment"])
    query = {}
    query['dno'] = dno
    mssn = list(depts.find(query,{"manager": 1, "_id": 0}))[0]['manager']['mssn']
    query = {}
    query['ssn'] = mssn
    es = list(emps.find(query,{"lname": 1, "dob": 1, "address": 1, "_id": 0}))
    lname = es[0]["lname"]
    dob = es[0]["dob"]
    address = es[0]["address"]
    result.append([pno, dno, lname, dob, address]) 
  return result

def main():
  client = MongoClient()
  db = client.companyDB
  projs = db.projects
  depts = db.departments
  emps = db.employees
  result = query(projs,depts,emps)
  print("For every project located in \"Stafford\", list the project number, the")
  print("controlling department number, and the department manager's last name,")
  print('birth date, and address.')
  for x in result:
    print('%4s %4s %15s %10s %30s'%(x[0],x[1],x[2],x[3],x[4]))

main()