class CheckFilemaker:
OK = 0
WARNING = 1
CRITICAL = 2
UNKNOWN = 3
def connect(self,
hostname,
username,
password,
database,
layout,
debug=False):
auth = username + ':' + password + '@' + hostname
self._fm = FMServer(auth, database, layout)
self._debug = debug
def column(self, **params):
return self._fm.doFind(params)
def check_layouts(self, layouts):
lays = self._fm.getLayoutNames()
return set(lays) & set(layouts)
def check_databases(self, databases):
dbs = self._fm.getDbNames()
return set(dbs) & set(databases)
def get_layouts(self):
from PyFileMaker import FMServer
fm = FMServer('%s:%s@%s' % (self.user, self.password, self.host))
fm.setDb(self.db)
for l in fm.getLayoutNames():
print l
def connect(self,
hostname,
username,
password,
database,
layout,
debug=False):
auth = username + ':' + password + '@' + hostname
self._fm = FMServer(auth, database, layout)
self._debug = debug
def get_layouts(self): # pragma: no cover
from PyFileMaker import FMServer
fm = FMServer('%s:%s@%s' % (self.user, self.password, self.host))
fm.setDb(self.db)
return fm.getLayoutNames()
try:
# tries to load a password file
from pswd import *
except ImportError:
pass
# disable request https warning
# http://stackoverflow.com/questions/27981545/suppress-insecurerequestwarning-unverified-https-request-is-being-made-in-pytho#28002687
import requests
import requests.packages.urllib3.exceptions as ulib
requests.packages.urllib3.disable_warnings(ulib.InsecureRequestWarning)
_pat = re.compile(r'^[0-9A-Z]{6}-[0-9A-Z]{6}$')
from PyFileMaker import FMServer
fm = FMServer(url=INSCRIPTION_URL, debug=False)
fm.setDb('Inscription')
fm.setLayout('XmlPaiement')
FACTURE = u'facture'
def parse_excel(filename):
wb = load_workbook(filename=filename, read_only=True)
ws = wb.active
all_rows = set()
tpl = []
for row in ws.iter_rows(min_row=2, min_col=1, max_col=14):
ukey = row[1].value
if ukey in all_rows:
# raise ValueError("ukey (col B) {} is here twice".format(ukey) )
def test_1(self):
"""Test hostnames with dashes"""
FMServer('login:[email protected]')
FMServer('login:[email protected]')
FMServer('login:[email protected]')
def get_layouts(self): # pragma: no cover
from PyFileMaker import FMServer
fm = FMServer('%s:%s@%s' % (self.user, self.password, self.host))
fm.setDb(self.db)
return fm.getLayoutNames()
FMDBUSER = os.environ.get('FMDBUSER') # FMDBUSER = '******'
FMDBPASSWORD = os.environ.get('FMDBPASSWORD') # FMDBPASSWORD = '******'
SQLDBHOST = os.environ.get('SQLDBHOST') # SQLDBHOST = 'host name or ip address'
SQLDB = os.environ.get('SQLDB') # SQLDB = 'sql database name'
SQLDBUSER = os.environ.get('SQLDBUSER') # SQLDBUSER = '******'
SQLDBPASSWORD = os.environ.get('SQLDBPASSWORD') # SQLDBPASSWORD = '******'
# TC AWS db ( also change db name in sql code manually)
# SQLDBHOST = os.environ.get('TCAWS_SQLDBHOST') # SQLDBHOST = 'host name or ip address'
# SQLDB = os.environ.get('TCAWS_SQLDB') # SQLDB = 'sql database name'
# SQLDBUSER = os.environ.get('TCAWS_SQLDBUSER') # SQLDBUSER = '******'
# SQLDBPASSWORD = os.environ.get('TCAWS_SQLDBPASSWORD') # SQLDBPASSWORD = '******'
FMACCESS = FMDBUSER+':'+FMDBPASSWORD+'@'+FMDBHOST # Facilitate fm db connection
fm = FMServer('http://'+FMACCESS) # Facilitate fm db connection
# fm._debug = True # Enable fms debugging (exposes urls)
fm.setDb(FMDB) # Declare fms db to be used
sqlFields = ["Customer_ID", "name", "province_id", "province_la_en", "district_id", "district_la", "village_id", "village_la", \
"sub_unit", "latitude", "longitude", "phone_1", "phone_2", "notes", "collector_id", "sync_datetime", "update_datetime"]
fmFields = ["Customer_ID", "Customers_Village_Sale.District_ID", "Customers_Village_Sale.Name_Full_Bilingual", "Customers_Village_Sale.Notes", \
"Customers_Village_Sale.Phone_1", "Customers_Village_Sale.Province_ID", "Customers_Village_Sale.Ship_To_Street_Lao","Customers_Village_Sale.Village_ID", \
"Date_Payment_10", "Date_Payment_11", "Date_Payment_12", "Date_Payment_1_Initial", "Date_Payment_2", "Date_Payment_3", "Date_Payment_4", \
"Date_Payment_5", "Date_Payment_6", "Date_Payment_7", "Date_Payment_8", "Date_Payment_9", "Date_Payment_Adjustment_1","Date_Payment_Adjustment_2", \
"Date_Scheduled_Payment_2", "Date_Scheduled_Payment_3", "Date_Scheduled_Payment_4", "Date_Scheduled_Payment_5", "Date_Scheduled_Payment_6", \
"Date_Scheduled_Payment_7", "Date_Scheduled_Payment_8", "Date_Scheduled_Payment_9", "District_Reference_Village_Sales.District_Name_English", \
"District_Reference_Village_Sales.District_Name_Lao", "MODID", "Notes", "Notes_Adjustment_1", "Notes_Adjustment_2", \
FMDBPASSWORD = os.environ.get('FMDBPASSWORD') # FMDBPASSWORD = '******'
# local dev db
# SQLDBHOST = os.environ.get('SQLDBHOST') # SQLDBHOST = 'host name or ip address'
# SQLDB = os.environ.get('SQLDB') # SQLDB = 'sql database name'
# SQLDBUSER = os.environ.get('SQLDBUSER') # SQLDBUSER = '******'
# SQLDBPASSWORD = os.environ.get('SQLDBPASSWORD') # SQLDBPASSWORD = '******'
# TC AWS db ( also change db name in sql code manually)
SQLDBHOST = os.environ.get('TCAWS_SQLDBHOST') # SQLDBHOST = 'host name or ip address'
SQLDB = os.environ.get('TCAWS_SQLDB') # SQLDB = 'sql database name'
SQLDBUSER = os.environ.get('TCAWS_SQLDBUSER') # SQLDBUSER = '******'
SQLDBPASSWORD = os.environ.get('TCAWS_SQLDBPASSWORD') # SQLDBPASSWORD = '******'
FMACCESS = FMDBUSER+':'+FMDBPASSWORD+'@'+FMDBHOST # Facilitate fm db connection
fm = FMServer('http://'+FMACCESS) # Facilitate fm db connection
# fm._debug = True # Enable fms debugging (exposes urls)
fm.setDb(FMDB) # Declare fms db to be used
def now():
return datetime.now()
def initSqldb(): # (re)create a new table for testing
db = MySQLdb.connect(SQLDBHOST,SQLDBUSER,SQLDBPASSWORD,SQLDB) # Open sql connection
cursor = db.cursor() # Prepare a cursor object using cursor() method
try: # setup customer table
cursor.execute("""DROP TABLE IF EXISTS `customer`""")
cursor.execute("""CREATE TABLE `customer` (\
`customer_id` INT(11) NOT NULL COMMENT '_Customer ID',
def main():
import argparse
parser = argparse.ArgumentParser(
description='Exports eInscription into Inscription on Norma.')
parser.add_argument('-f',
'--file',
dest="file",
default="",
required=True,
help='XML File to parse')
parser.add_argument(
'-T',
'--true',
dest="simulate",
action="store_false",
default=True,
required=False,
help='Simulation mode off. Does something on Inscription')
parser.add_argument('--force',
dest="overwrite",
action="store_true",
default=False,
required=False,
help='Overwrites existing Inscription on Norma')
args = parser.parse_args()
print u"Loading XML file {}".format(args.file)
if args.simulate:
print u"Simulation mode ON"
else:
print u"Simulation mode OFF"
fm = FMServer(url=INSCRIPTION_URL, debug=False)
fm.setDb('Inscription')
# fm.setDb( 'InscriptionJeremie' )
fm.setLayout('STDInscription')
def _setattr(item, key, value):
if value is None:
return
if isinstance(value, unicode):
v = value.encode('utf8')
else:
v = value
if getattr(item, key) != value:
setattr(item, key, v)
count = 0
found = 0
updated = 0
skipped = 0
photo = 0
ftp = ftplib.FTP(NORMA_FTP)
ftp.login(NORMA_USER, NORMA_PSWD)
for query in parse(args.file):
uid = query['eleve']['uid']
count += 1
ins = query['inscription']
if not ins:
print u"Pas d'inscirption pour {}".format(uid)
continue
image = query['donneesComplementaires']['photo']
resultset = fm.doFind({'uid': uid})
if not resultset:
print u"uid {} not found in the FMS db".format(uid)
continue
if len(resultset) > 1:
print u"More than one uid found for {} in the FMS db".format(uid)
continue
found += 1
res = resultset[0]
if res['sectionSaisie'] and not args.overwrite:
skipped += 1
continue
if image and not args.simulate:
data = BytesIO(base64.b64decode(image))
name = 'photos_lagapeo/' + uid.lower() + ".jpg"
ftp.storbinary("STOR " + name, data, 1024 * 8)
try:
ecole = ins['formation']
voie = query['previsionVoie']
affectation = query['affectation']
comp = query['donneesComplementaires']
if image:
_setattr(res, 'flagPhotoAUploader', '1')
photo += 1
_setattr(res, 'etabPreavis', etab_preavis(query))
_setattr(res, 'etabRemarque',
query['preavis']['remarquesComplementaires'])
_setattr(res, 'eleveAutreFormation', eleve_autre_formation(ins))
_setattr(res, 'zoneRecrutement',
affectation.get('zoneAffectationAuto', None))
_setattr(res, 'autreZoneAffectation',
affectation.get('zoneAffectationSouhaitee', None))
_setattr(res, 'numeroDemande', query.get('numeroDemande', ''))
# _setattr( res, 'mereEtatCivil', comp.get('etatCivilMere',None) )
# _setattr( res, 'pereEtatCivil', comp.get('etatCivilPere',None) )
# _setattr( res, 'mereProfession', comp.get('professionMere',None) )
# _setattr( res, 'pereProfession', comp.get('professionPere',None) )
# _setattr( res, 'eleveAssurance', comp.get('assurance',None) )
EM = bool(ecole == u'Ecole de maturité')
ECG = bool(ecole == u'Ecole de culture générale')
EC = bool(ecole == u'Ecole de commerce')
if not EM and not EC and not ECG:
print u"L'élève {} n'est inscrit dans aucune école.".format(
uid)
continue
if EM:
_setattr(res, 'sectionSaisie', 'M')
_setattr(res, 'eleveOptionOs', eleve_os(ins))
_setattr(res, 'eleveBilingue', eleve_bilingue(ins))
_setattr(res, 'eleveOptionL2', eleve_langue2_matu(ins))
_setattr(res, 'eleveOptionL3', eleve_langue3_matu(ins))
_setattr(res, 'eleveOptionMa', eleve_math(ins))
_setattr(res, 'etabMoyenneInscription',
voie.get('vpTotal1', None))
_setattr(res, 'etabMoyenneInscriptionFMA',
voie.get('vpTotal2', None))
if EC:
_setattr(res, 'sectionSaisie', 'E')
if ECG:
_setattr(res, 'sectionSaisie', 'D')
if EC or ECG:
_setattr(res, 'eleveOptionL2', eleve_langue2(ins))
_setattr(res, 'etabMoyenneInscription',
voie.get('vgCours2', None))
_setattr(res, 'etabMoyenneInscriptionFMA',
voie.get('vgSem1', None))
if EC or ECG or EM:
_setattr(res, 'elevePrevision', voie.get('pronostic', None))
_setattr(res, 'eleveOptionOa', eleve_art(ins))
_setattr(res, 'eleveClasseSpeciale', eleve_speciale(ins))
except KeyError as e:
print u"Could not process {}".format(uid)
raise
print u"Edition de l'inscription de {}".format(uid)
res.flagInscriptionOK = 1
res.flagEInscription = 1
if args.simulate:
print u"Simulation mode on"
else:
fm.doEdit(res)
updated += 1
print u"Total number of inscription (with photos: {}) in the xml: {}".format(
photo, count)
print u"Total number of uid found in Norma: {}".format(found)
print u"Total number of record skipped in Norma: {}".format(skipped)
print u"Total number of record edited in Norma: {}".format(updated)
from PyFileMaker import FMServer
# Get access details from env / Or set locally (for debugging only)
FMDBHOST = os.environ.get('FMDBHOST') # FMDBHOST = 'host name or ip address'
FMDB = os.environ.get('FMDB') # FMDB = 'fm database name'
FMDBUSER = os.environ.get('FMDBUSER') # FMDBUSER = '******'
FMDBPASSWORD = os.environ.get('FMDBPASSWORD') # FMDBPASSWORD = '******'
SQLDBHOST = os.environ.get(
'SQLDBHOST') # SQLDBHOST = 'host name or ip address'
SQLDB = os.environ.get('SQLDB') # SQLDB = 'sql database name'
SQLDBUSER = os.environ.get('SQLDBUSER') # SQLDBUSER = '******'
SQLDBPASSWORD = os.environ.get(
'SQLDBPASSWORD') # SQLDBPASSWORD = '******'
FMACCESS = FMDBUSER + ':' + FMDBPASSWORD + '@' + FMDBHOST # Facilitate fm db connection
fm = FMServer('http://' + FMACCESS) # Facilitate fm db connection
fm._debug = True # Enable fms debugging (exposes urls)
fm.setDb(FMDB) # Declare fms db to be used
def checkDbserver(): # wake sqlserver when sleeping (beta)
hostname = SQLDBHOST #example
response = os.system("ping -c 1 " + hostname)
#and then check the response...
if response == 0:
print hostname, 'is up!… '
print getDbseverVersion()
else:
from subprocess import call
call(["~/scripts/wakepve.sh"]) # actual script to send wol package