def get_total_adj_hrs(sid, start_date, end_date):
total_hrs = Decimal('0.00')
x = start_date
start_date_ts = date(x.year, x.month, 1)
#get all involved timesheet
sql = text("""
SELECT id, month_year FROM timesheet
WHERE status IN ('open', 'locked')
AND subcontractors_id = :sid
AND month_year BETWEEN :start_date AND :end_date
""")
conn = engine.connect()
timesheets = conn.execute(sql, sid=sid, start_date=start_date_ts, end_date=end_date).fetchall()
for ts in timesheets:
sql = text("""
SELECT day, adj_hrs FROM timesheet_details
WHERE timesheet_id = :tid
ORDER BY day
""")
ts_details = conn.execute(sql, tid=ts.id).fetchall()
for ts_detail in ts_details:
x = ts.month_year
y = datetime(x.year, x.month, ts_detail.day)
if (y >= start_date) and (y <= end_date):
if ts_detail.adj_hrs == None:
continue
adj_hrs = Decimal('%0.2f' % ts_detail.adj_hrs)
total_hrs += adj_hrs
conn.close()
return "%0.2f" % total_hrs
def run(self, client_id):
"""given the client_id, return the admin
returns None if not found
"""
admin_data = None
sql = text(
"""SELECT a.admin_id, a.userid, a.admin_fname, a.admin_lname,
a.admin_email, a.status,
a.signature_notes, a.signature_contact_nos, a.signature_company, a.signature_websites
FROM leads AS l
JOIN admin AS a
ON l.csro_id = a.admin_id
WHERE l.id = :client_id
""")
conn = engine.connect()
admin_data = conn.execute(sql, client_id=client_id).fetchone()
if admin_data != None:
if admin_data.status == 'REMOVED':
#cough out to log
message = 'Please fix csro assignment for leads.id # %s\ncsro_id:%s <%s> status has been REMOVED.' % (
client_id, admin_data.admin_id, admin_data.admin_email)
CSROAssignmentAlert.delay('CSRO Assignment Alert', message)
else:
message = 'Please fix csro assignment for leads.id # %s\nNo CSRO assigned.' % (
client_id)
CSROAssignmentAlert.delay('CSRO Assignment Alert', message)
conn.close()
return admin_data
def process_query(sql):
conn = engine.connect()
subcons = conn.execute(sql).fetchall()
data = []
for s in subcons:
data.append(int(s.subcon_id))
return data
def get_totals(timesheet_id):
"""
given the timesheet_id, return total hours in decimal format
"""
sql = text("""
SELECT
SUM(regular_rostered)AS sum_regular_rostered,
SUM(hrs_charged_to_client) AS sum_hrs_charged_to_client,
SUM(adj_hrs) AS sum_adj_hrs,
SUM(diff_charged_to_client) as sum_diff_charged_to_client
FROM timesheet_details
WHERE timesheet_id = :timesheet_id
""")
conn = engine.connect()
sum_hours = conn.execute(sql, timesheet_id=timesheet_id).fetchone()
conn.close()
return_data = dict(
sum_regular_rostered = sum_hours.sum_regular_rostered,
sum_hrs_charged_to_client = sum_hours.sum_hrs_charged_to_client,
sum_adj_hrs = sum_hours.sum_adj_hrs,
sum_diff_charged_to_client = sum_hours.sum_diff_charged_to_client
)
return return_data
def run():
"""
return a list of items suitable for invoicing
"""
#get timesheets
sql = text("""
SHOW FULL PROCESSLIST
""")
conn = engine.connect()
data = conn.execute(sql).fetchall()
for d in data:
if d['Command'] != 'Query':
continue
if d['Time'] >= SECONDS_KILL_QUERY:
report = """Sorry guys, I need to kill this query.\n\n%s
""" % (pformat(d.items(), 4))
send_task("notify_devs.send", ("KILL QUERY REPORT", report))
sql = text("""
KILL :process_id
""")
conn.execute(sql, process_id=d['Id'])
continue
if d['Time'] > SECONDS_SLOW_QUERY:
report = """Please optimize this query.\n\n%s
""" % (pformat(d.items(), 4))
send_task("notify_devs.send", ("SLOW QUERY REPORT", report))
conn.close()
def get_currency_adjustment_peso_per_staff(subcon_id):
conn = engine.connect()
subcon = get_contract_detail(subcon_id)
forex = get_forex_rate_client(subcon.leads_id)
forex_rate = float(forex.rate)
subcon_current_rate = float(subcon.current_rate)
return float("{0:.2f}".format((subcon_current_rate - forex_rate)))
def get_forex_rate_client(leads_id):
conn = engine.connect()
couch_currency, apply_gst = get_currency_apply_gst_client(leads_id)
currency_sql = text("""
SELECT rate FROM currency_adjustments WHERE active='yes' AND currency=:currency
""")
forex = conn.execute(currency_sql, currency=couch_currency).fetchone()
return forex
def check_subcon_records():
"""will be called using celery beat every 5mins
"""
# collect couchdb records
db = s['rssc']
subcontractor_ids_from_couchdb = []
for r in db.view('subcontractor/active'):
sid = r.get('id')
sid = re.sub('subcon-', '', sid)
sid = int(sid)
subcontractor_ids_from_couchdb.append(sid)
# collect mysql records
conn = engine.connect()
sql = text("""
SELECT id
FROM subcontractors
WHERE status = 'ACTIVE'
""")
sql_update_status = text("""
UPDATE subcontractors
SET status = 'ACTIVE'
WHERE id = :sid
""")
data = conn.execute(sql)
for d in data:
sid = int(d.id)
if sid not in subcontractor_ids_from_couchdb:
logging.info('%s not active in couchdb' % sid)
#temporarily disabled notify devs
#send_task('notify_devs.send', ['rssc subcon-%s out of sync' % sid, 'syncing it now.'])
doc = db.get('subcon-%s' % sid)
if doc == None: #no records on rssc yet trigger it by setting the status once again
logging.info('%s not on couchdb yet, update status to sync' %
sid)
conn.execute(sql_update_status, sid=sid)
else:
logging.info(
'%s already on couchdb, updating active field to Y and adding history'
% sid)
doc['active'] = 'Y'
history = doc.get('history')
if history == None:
history = []
now = get_ph_time()
history.append(
dict(
note=
'updates by celery rssc_server.check_subcon_records, set status from N to Y',
date=now.strftime('%Y-%m-%d %H:%M:%S')))
doc['history'] = history
db.save(doc)
conn.close()
def get_rates_from_mysql(subcontractors_id, datetime_req):
"""Fallback function querying mysql database
##~ >>> get_rates_from_mysql(3159, datetime(2012,11,7,0,0,0))
##~ Decimal('4.32')
>>> get_rates_from_mysql(3000, datetime.now())
Decimal('0.00')
>>> get_rates_from_mysql(3159, datetime.now())
Decimal('8.64')
>>> get_rates_from_mysql(2714, datetime(2012,9,2,0,0,0))
Decimal('4.11')
>>> get_rates_from_mysql(3205, datetime(2012,11,21,0,0,0))
Decimal('6.82')
"""
sql = text("""
SELECT rate AS client_price, work_status
FROM subcontractors_client_rate
WHERE subcontractors_id = :subcontractors_id
AND start_date <= :datetime_req
AND (end_date > :datetime_req OR end_date IS NULL)
""")
conn = engine.connect()
data = conn.execute(sql,
subcontractors_id=subcontractors_id,
datetime_req=datetime_req).fetchall()
if len(data) == 1: #retrieve from subcontractors table
client_price, work_status = data[0]
if work_status == None: #old data, no associated work_status, get it from subcontractors table
sql = text("""
SELECT work_status
FROM subcontractors
WHERE id = :subcontractors_id
""")
data = conn.execute(
sql, subcontractors_id=subcontractors_id).fetchone()
work_status = data.work_status
elif len(data) == 0: #retrieve from subcontractors table
sql = text("""
SELECT client_price, work_status
FROM subcontractors
WHERE id = :subcontractors_id
""")
data = conn.execute(sql,
subcontractors_id=subcontractors_id).fetchone()
client_price, work_status = data
else:
send_task('notify_devs.send', [
'multiple records found for subcontractors_client_rate',
'Please check subcontractors_id %s. Multiple records from subcontractors_client_rate found for date %s!'
% (subcontractors_id, datetime_req)
])
client_price, work_status = data[0]
hourly_rate = compute_hourly_rate(client_price, work_status)
conn.close()
return hourly_rate
def get_contract_detail(subcon_id):
#get subcon details
subcon_sql = text("""
SELECT id, leads_id, userid, current_rate, client_price, work_status FROM subcontractors WHERE id = :subcon_id
""")
conn = engine.connect()
subcon = conn.execute(subcon_sql, subcon_id=subcon_id).fetchone()
return subcon
def get_total_log_hrs(sid, start_date, end_date):
now = get_ph_time()
total_work_hours = Decimal('0.00')
#get all involved timesheet
sql = text("""SELECT userid FROM subcontractors WHERE id = :sid """)
conn = engine.connect()
subcon = conn.execute(sql, sid=sid).fetchone()
s = couchdb.Server(settings.COUCH_DSN)
db = s['rssc_time_records']
userid = int(int(subcon.userid))
r = db.view('rssc_reports/userid_timein',
startkey=[userid, [ int(start_date.strftime('%Y')), int(start_date.strftime('%m')), int(start_date.strftime('%d')),0,0,0,0]],
endkey=[userid, [ int(end_date.strftime('%Y')), int(end_date.strftime('%m')), int(end_date.strftime('%d')),23,59,59,0]],
ascending=True
)
phtz = timezone('Asia/Manila')
timezone_ref = phtz
for row in r.rows:
record_type, b, leads_id, subcon_id = row['value']
userid, a = row['key']
if subcon_id != None:
if record_type == 'quick break':
continue
if int(sid) != int(subcon_id):
continue
start = datetime(a[0], a[1], a[2], a[3], a[4], a[5], tzinfo=phtz)
if b == None or b == False:
end = datetime(now.year, now.month, now.day, now.hour, now.minute, now.second, tzinfo=phtz)
else:
end = datetime(b[0], b[1], b[2], b[3], b[4], b[5], tzinfo=phtz)
start = start.astimezone(timezone_ref)
if end != None:
end = end.astimezone(timezone_ref)
#update totals
time_diff = end - start
time_diff_decimal = Decimal('%s' % (time_diff.seconds / 3600.0)).quantize(TWOPLACES, rounding=ROUND_HALF_UP)
if record_type == 'time record':
total_work_hours += time_diff_decimal
elif record_type == 'lunch record':
total_work_hours -= time_diff_decimal
conn.close()
return "%0.2f" % total_work_hours
def process_doc_id(doc_id):
logging.info('checking %s' % doc_id)
s = couchdb.Server(settings.COUCH_DSN)
db = s['subconlist_reporting']
doc = db.get(doc_id)
if doc == None:
raise Exception('subconlist_reporting document not found : %s' %
doc_id)
start_date = datetime.strptime(doc['start_date'], '%Y-%m-%d')
end_date = datetime.strptime(doc['end_date'], '%Y-%m-%d')
subcontractor_ids = doc['subcontractor_ids']
#return subcontractor_ids
random_string_exists = True
DATE_SEARCH = []
while random_string_exists:
if start_date <= end_date:
DATE_SEARCH.append('%s' % start_date.strftime('%Y-%m-%d'))
start_date = start_date + timedelta(days=1)
random_string_exists = True
else:
random_string_exists = False
data = []
couch_result = {}
total_reg_hours = {}
total_log_hours = {}
total_adj_hours = {}
total_login_types = {}
worked_hrs_rate_percentage = {}
adjusted_hrs_rate_percentage = {}
tasks = []
for sid in subcontractor_ids:
sql = text(
"""SELECT userid, leads_id FROM subcontractors WHERE id = :sid """)
conn = engine.connect()
subcon = conn.execute(sql, sid=sid).fetchone()
t = get_dates(int(sid), int(subcon.userid), int(subcon.leads_id),
DATE_SEARCH)
tasks.append(t)
job = TaskSet(tasks=tasks)
#result = job.apply_async()
#data = result.join()
couch_result = {}
for i in range(len(subcontractor_ids)):
couch_result[int(subcontractor_ids[i])] = job[i]
doc = db.get(doc_id)
doc['result'] = couch_result
doc['result_date_time'] = get_ph_time().strftime('%Y-%m-%d %H:%M:%S')
db.save(doc)
def get_hourly_rate_per_staff(subcon_id):
subcon_sql = text("""
SELECT id, client_price, work_status FROM subcontractors WHERE id = :subcon_id
""")
conn = engine.connect()
subcon = conn.execute(subcon_sql, subcon_id=subcon_id).fetchone()
client_price = float(subcon.client_price)
if subcon.work_status == "Part-Time":
return float("{0:.2f}".format((client_price * 12) / 52 / 5 / 4))
else:
return float("{0:.2f}".format((client_price * 12) / 52 / 5 / 8))
def check_date(TEST):
#print scheduled_date(now.year, months, TEST)
current_date = '%s' % now.strftime('%Y-%m-%d')
#print current_date
if current_date in scheduled_date(now.year, months, TEST):
#print 'meron'
conn = engine.connect()
sql = text(
"""SELECT id, work_days, mon_start, tue_start, wed_start, thu_start, fri_start, sat_start, sun_start FROM subcontractors WHERE status IN ('ACTIVE', 'suspended')
AND client_timezone IN (
'Australia/Adelaide',
'Australia/Brisbane',
'Australia/Broken_Hill',
'Australia/Currie',
'Australia/Darwin',
'Australia/Eucla',
'Australia/Hobart',
'Australia/Lindeman',
'Australia/Lord_Howe',
'Australia/Melbourne',
'Australia/Perth',
'Australia/Sydney'
)
""")
subcons = conn.execute(sql).fetchall()
#print '%s no. of subcons' % len(subcons)
subcons_regular_sched = [] #The staff has a thesame schedule everyday
for s in subcons:
staff_start_time = []
if s.work_days:
work_days = s.work_days.split(',')
for w in work_days:
dayname_str = '%s_start' % w
staff_start_time.append('%s' % s[dayname_str])
myset = set(staff_start_time)
if len(myset) == 1:
subcons_regular_sched.append(int(s.id))
conn.close()
#send message to celery
ph_tz = timezone('Asia/Manila')
eta = datetime(int(now.year),
int(now.month),
int(now.day),
2,
0,
0,
tzinfo=ph_tz)
def run(self, client_id):
"""given the client_id, return the admin.admin_email
"""
admin_email = None
sql = text(
"""SELECT a.admin_id, a.admin_email, a.status FROM leads AS l
JOIN admin AS a
ON l.csro_id = a.admin_id
WHERE l.id = :client_id
""")
conn = engine.connect()
admin_data = conn.execute(sql, client_id=client_id).fetchone()
if admin_data != None:
admin_id, admin_email, admin_status = admin_data
if admin_status == 'REMOVED':
#cough out to log
admin_status_message = 'Please fix csro assignment for leads.id # %s\ncsro_id:%s <%s> status has been REMOVED.' % (
client_id, admin_id, admin_email)
logging.info(admin_status_message)
#send email
msg = MIMEText(admin_status_message)
s = smtplib.SMTP(host=settings.MAILGUN_CONFIG['server'],
port=settings.MAILGUN_CONFIG['port'])
s.starttls()
s.login(settings.MAILGUN_CONFIG['username'],
settings.MAILGUN_CONFIG['password'])
recipients = [
'*****@*****.**', '*****@*****.**',
'*****@*****.**'
]
msg['Subject'] = 'CSRO Assignment Alert'
if settings.DEBUG:
recipients = ['*****@*****.**']
msg['Subject'] = 'TEST CSRO Assignment Alert'
msg['To'] = string.join(recipients, ',')
s.sendmail('*****@*****.**', recipients,
msg.as_string())
s.quit()
admin_email = None
conn.close()
return admin_email
def expire_mass_responder_code(userid, mass_responder_code):
logging.info('Executing schedule.expire_mass_responder_code (%s , %s) ' %
(userid, mass_responder_code))
conn = engine.connect()
#get existing mass_responder_code
sql = text(
"""SELECT mass_responder_code FROM personal WHERE userid=:userid""")
r = conn.execute(sql, userid=userid).fetchone()
if r[0] == mass_responder_code:
sql = text(
"""UPDATE personal SET mass_responder_code=NULL WHERE userid=:userid"""
)
conn.execute(sql, userid=userid)
conn.close()
def get_clients_with_prepaid_accounts():
sql = text("""
SELECT DISTINCT(leads_id) FROM subcontractors
WHERE prepaid = 'yes'
""")
conn = engine.connect()
r = conn.execute(sql).fetchall()
client_ids = []
for x in r:
client_ids.append(x[0])
#retrieve leads_id of timesheet records for the past 90 days
#this is to prevent subcontractors record recently terminated and does not show up on adjustment
now = get_ph_time()
prev_date = now - timedelta(days=90)
now_string = now.strftime('%F %T')
prev_date_string = prev_date.strftime('%F %T')
sql = text("""
SELECT DISTINCT(t.leads_id)
FROM timesheet AS t
JOIN subcontractors AS s
ON t.leads_id = s.leads_id
WHERE s.prepaid = 'yes'
AND month_year BETWEEN :prev_date_string AND :now_string
""")
r = conn.execute(sql,
prev_date_string=prev_date_string,
now_string=now_string)
for x in r:
client_id = x[0]
if client_id not in client_ids:
client_ids.append(client_id)
tasks = []
for client_id in client_ids:
y = get_client_details.subtask((client_id, ))
tasks.append(y)
job = TaskSet(tasks=tasks)
result = job.apply_async()
data = result.join()
conn.close()
return data
def run(self, client_id):
address_to = ''
sql = text("""SELECT address_to, default_email_field, cc_emails
FROM leads_send_invoice_setting
WHERE leads_id = :client_id
""")
conn = engine.connect()
client_data = conn.execute(sql, client_id=client_id).fetchone()
# records found on leads_send_invoice_setting
if client_data != None:
if client_data.address_to == 'main_acct_holder':
sql = text("""SELECT fname, lname,
%s AS email
FROM leads
WHERE id = :client_id
""" % client_data.default_email_field)
a = conn.execute(sql, client_id=client_id).fetchone()
address_to = '%s %s' % (string.capitalize(
a.fname), string.capitalize(a.lname))
else:
sql = text("""SELECT * FROM leads
where id = :client_id
""")
result = conn.execute(sql, client_id=client_id).fetchone()
address_to = '%s' % (result[client_data.address_to])
else:
sql = text("""SELECT fname, lname, email, acct_dept_email1,
acct_dept_email2
FROM leads
WHERE id = :client_id
""")
a = conn.execute(sql, client_id=client_id).fetchone()
if a == None:
address_to = 'Invalid client_id:%s' % client_id
address_to = '%s %s' % (string.capitalize(
a.fname), string.capitalize(a.lname))
conn.close()
return address_to
def run(self, userid):
"""given the userid, return a list of admin emails
"""
admin_emails = []
sql = text("""
SELECT DISTINCT(leads_id)
FROM subcontractors
WHERE userid = :userid
AND STATUS = 'active'
""")
conn = engine.connect()
result = conn.execute(sql, userid=userid).fetchall()
csro_email = GetCsroEmail()
for r in result:
admin_email = csro_email.run(r[0])
if admin_email != None:
admin_emails.append(admin_email)
conn.close()
return admin_emails
def get_skype_ids_for_dashboard_alerts():
"""
>>> get_skype_ids_for_dashboard_alerts()
[u'remotestaff.wilmie.u', u'remotestaff.kimberly']
"""
conn = engine.connect()
sql = text("""
SELECT skype_id
FROM admin
WHERE rssc_dashboard_alerts = 'Y'
AND status NOT IN ('PENDING', 'REMOVED')
AND skype_id IS NOT NULL
""")
data = conn.execute(sql)
conn.close()
skype_ids = []
for skype_id in data:
skype_ids.append(skype_id[0])
return skype_ids
def compare_hourly_rates(subcontractors_id, current_hourly_rate):
"""
no return value, just some notification if subcontractors record is not the same as current_hourly_rate
"""
conn = engine.connect()
sql = text("""
SELECT client_price, work_status
FROM subcontractors
WHERE id = :subcontractors_id
""")
data = conn.execute(sql, subcontractors_id=subcontractors_id).fetchone()
client_price, work_status = data
hourly_rate = compute_hourly_rate(client_price, work_status)
conn.close()
if hourly_rate != current_hourly_rate:
logging.info(
'compare_hourly_rates for sid:%s are not the same. Fetched: %s vs Recorded on subcontractors table:%s'
% (subcontractors_id, current_hourly_rate, hourly_rate))
send_task('notify_devs.send', [
'Hourly Rates not the same for sid:%s' % subcontractors_id,
'Recorded on subcontractors table: %s\nCurrently used by staff: %s'
% (hourly_rate, current_hourly_rate)
])
def process(id):
logging.info(
'executing get_staff_daily_attendance_result.process %s on %s' %
(id, get_ph_time()))
try:
conn = engine.connect()
sql = "SELECT mongodb_doc_id FROM mongo_staff_daily_attendance m WHERE id=%s;" % id
row = conn.execute(sql).fetchone()
doc_id = row['mongodb_doc_id']
conn.close()
except:
return "error in retrieving mongo_staff_daily_attendance.id=%s " % id
if settings.DEBUG:
mongo_client = MongoClient(host=settings.MONGO_TEST)
#mongo_client = MongoClient()
else:
mongo_client = MongoClient(host=settings.MONGO_PROD, port=27017)
#mongo_client = MongoClient(host=MONGO_TEST)
#connect to the test db
db = mongo_client.reports
#retrieve the person collection
col = db.staff_daily_attendance
try:
doc = col.find_one({"_id": ObjectId(doc_id)})
except:
return 'invalid mongodb document id'
if not doc:
return 'mongodb document id not found'
#return doc
subcontractor_ids = doc['subcontractor_ids']
DATE_SEARCH = doc['date_search'][0]
complete = False
#return DATE_SEARCH
str = "\n\nResults:\n"
compliance_result = {}
registered_hrs_result = {}
conn = engine.connect()
total_absent = 0
total_approve_leave = 0
total_working = 0
total_not_working = 0
total_marked_absent = 0
result = []
for c in doc['currencies']:
str += '=> %s\n' % (c['currency'])
currency = '%s' % c['currency']
absent = 0
approve_leave = 0
working = 0
not_working = 0
marked_absent = 0
subcons = []
subcons_working = []
subcons_not_working = []
for sid in c['subcons']:
userid = doc['subcon_userid'][sid]
leads_id = doc['subcon_leads_id'][sid]
starting_date = doc['subcon_starting_date'][sid]
work_days = doc['subcon_work_days'][sid].split(',')
subcon_working_days = doc['subcon_working_days'][sid]
subcon_client_working_hours = doc['subcon_client_working_hours'][
sid]
dates = []
registered_hrs = []
compliance = get_compliance_result(conn, sid, DATE_SEARCH, userid,
leads_id, starting_date,
work_days, subcon_working_days,
subcon_client_working_hours,
True)
if compliance['working_status'] == 'working':
working = working + 1
subcons_working.append(
dict(
subcon_id=int(sid),
record=compliance,
))
if compliance['working_status'] == 'not working':
not_working = not_working + 1
subcons_not_working.append(
dict(
subcon_id=int(sid),
record=compliance,
))
if compliance['compliance'] == 'marked absent':
marked_absent = marked_absent + 1
if compliance['compliance'] == 'approved leave':
approve_leave = approve_leave + 1
if compliance['compliance'] == 'absent':
absent = absent + 1
subcons.append(dict(
subcon_id=int(sid),
record=compliance,
))
str += '=> %s %s\n\n' % (sid, dates)
total_working = total_working + working
total_not_working = total_not_working + not_working
total_absent = total_absent + absent
total_approve_leave = total_approve_leave + approve_leave
total_marked_absent = total_marked_absent + marked_absent
result.append(
dict(currency=currency,
subcons=subcons,
working=working,
not_working=not_working,
approve_leave=approve_leave,
marked_absent=marked_absent,
absent=absent,
subcons_working=subcons_working,
subcons_not_working=subcons_not_working))
#return str
#update the document
col.update({"_id": ObjectId(doc_id)}, {"$set": {'result': result}})
#col.update({"_id" : ObjectId(doc_id)}, {"$set":{'result' : compliance_result}})
col.update({"_id": ObjectId(doc_id)},
{"$set": {
'absent': '%s' % total_absent
}})
col.update({"_id": ObjectId(doc_id)},
{"$set": {
'working': '%s' % total_working
}})
col.update({"_id": ObjectId(doc_id)},
{"$set": {
'not_working': '%s' % total_not_working
}})
col.update({"_id": ObjectId(doc_id)},
{"$set": {
'marked_absent': '%s' % total_marked_absent
}})
col.update({"_id": ObjectId(doc_id)},
{"$set": {
'approve_leave': '%s' % total_approve_leave
}})
query = "UPDATE mongo_staff_daily_attendance SET status='executed', date_executed='%s' WHERE id='%s';" % (
'%s' % get_ph_time(), id)
conn.execute(query)
conn.close()
def work_schedule(subcon_id, date_str):
conn = engine.connect()
date_str = datetime.strptime(date_str, '%Y-%m-%d')
dayname_str = '%s' % date_str.strftime('%a')
dayname_str = '%s' % dayname_str.lower()
#get subcon
sql = text(
"""SELECT id, work_status, staff_working_timezone, client_timezone, work_days,
mon_start, mon_finish, mon_number_hrs,
tue_start, tue_finish, tue_number_hrs,
wed_start, wed_finish, wed_number_hrs,
thu_start, thu_finish, thu_number_hrs,
fri_start, fri_finish, fri_number_hrs,
sat_start, sat_finish, sat_number_hrs,
sun_start, sun_finish, sun_number_hrs,
client_start_work_hour, client_finish_work_hour, starting_date, flexi
FROM subcontractors
WHERE id=:subcon_id""")
subcon = conn.execute(sql, subcon_id=subcon_id).fetchone()
try:
ph_tz = timezone(
subcon.staff_working_timezone) #timezone('Asia/Manila')
except TypeError:
pass
except:
ph_tz = timezone('Asia/Manila')
else:
ph_tz = timezone(
subcon.staff_working_timezone) #timezone('Asia/Manila')
client_tz = timezone(subcon.client_timezone)
work_days = ['mon', 'tue', 'wed', 'thu', 'fri']
staff_start_work_hour = None
staff_finish_work_hour = ""
client_start_work_hour = ""
client_finish_work_hour = ""
have_sched = True
regular_contract_hrs = 0
if subcon.work_days:
work_days = subcon.work_days.split(',')
if dayname_str in work_days:
have_sched = True
if dayname_str == 'mon':
staff_start_work_hour = subcon.mon_start
staff_finish_work_hour = subcon.mon_finish
regular_contract_hrs = subcon.mon_number_hrs
if dayname_str == 'tue':
staff_start_work_hour = subcon.tue_start
staff_finish_work_hour = subcon.tue_finish
regular_contract_hrs = subcon.tue_number_hrs
if dayname_str == 'wed':
staff_start_work_hour = subcon.wed_start
staff_finish_work_hour = subcon.wed_finish
regular_contract_hrs = subcon.wed_number_hrs
if dayname_str == 'thu':
staff_start_work_hour = subcon.thu_start
staff_finish_work_hour = subcon.thu_finish
regular_contract_hrs = subcon.thu_number_hrs
if dayname_str == 'fri':
staff_start_work_hour = subcon.fri_start
staff_finish_work_hour = subcon.fri_finish
regular_contract_hrs = subcon.fri_number_hrs
if dayname_str == 'sat':
staff_start_work_hour = subcon.sat_start
staff_finish_work_hour = subcon.sat_finish
regular_contract_hrs = subcon.sat_number_hrs
if dayname_str == 'sun':
staff_start_work_hour = subcon.sun_start
staff_finish_work_hour = subcon.sun_finish
regular_contract_hrs = subcon.sun_number_hrs
staff_start_work_hour = str(staff_start_work_hour)
staff_finish_work_hour = str(staff_finish_work_hour)
if dayname_str not in work_days:
have_sched = False
if subcon.client_start_work_hour:
client_start_work_hour = '%s %s' % (
datetime.now().strftime('%Y-%m-%d'),
(subcon.client_start_work_hour))
client_start_work_hour = client_tz.localize(
datetime.strptime(client_start_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(client_tz)
#client_start_work_hour = client_start_work_hour.strftime('%I:%M %p')
#Staff working hours
if not staff_start_work_hour or not have_sched:
staff_start_work_hour = '%s %s' % (datetime.now().strftime('%Y-%m-%d'),
(subcon.client_start_work_hour))
staff_start_work_hour = client_tz.localize(
datetime.strptime(staff_start_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(ph_tz)
staff_start_work_hour = staff_start_work_hour.strftime('%H:%M:%S')
if subcon.client_finish_work_hour:
client_finish_work_hour = '%s %s' % (
datetime.now().strftime('%Y-%m-%d'),
(subcon.client_finish_work_hour))
client_finish_work_hour = client_tz.localize(
datetime.strptime(client_finish_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(client_tz)
#client_finish_work_hour = client_finish_work_hour.strftime('%I:%M %p')
if not staff_finish_work_hour or not have_sched:
staff_finish_work_hour = '%s %s' % (
datetime.now().strftime('%Y-%m-%d'),
(subcon.client_finish_work_hour))
staff_finish_work_hour = client_tz.localize(
datetime.strptime(staff_finish_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(ph_tz)
staff_finish_work_hour = staff_finish_work_hour.strftime('%H:%M:%S')
staff_start_work_hour = '%s %s' % (date_str.strftime('%Y-%m-%d'),
staff_start_work_hour)
staff_start_work_hour = datetime.strptime(staff_start_work_hour,
'%Y-%m-%d %H:%M:%S')
staff_finish_work_hour = '%s %s' % (date_str.strftime('%Y-%m-%d'),
staff_finish_work_hour)
staff_finish_work_hour = datetime.strptime(staff_finish_work_hour,
'%Y-%m-%d %H:%M:%S')
#return staff_start_work_hour
return dict(client_start_work_hour=client_start_work_hour,
client_finish_work_hour=client_finish_work_hour,
client_timezone=subcon.client_timezone,
staff_start_work_hour=staff_start_work_hour,
staff_finish_work_hour=staff_finish_work_hour,
staff_working_timezone=subcon.staff_working_timezone,
regular_contract_hrs=regular_contract_hrs,
work_days=work_days,
starting_date=subcon.starting_date,
flexi=subcon.flexi)
def get_compliance_result(subcon_id,
date_str,
userid,
leads_id,
complete=False):
work = ""
work = work_schedule(subcon_id, date_str)
starting_date = work['starting_date']
work_days = work['work_days']
flexi = work['flexi']
compliance = ""
timein = ""
num_of_leave = 0
num_of_marked_absent = 0
conn = engine.connect()
sql = "SELECT COUNT(l.id)AS num_of_leave FROM leave_request_dates l JOIN leave_request r ON r.id = l.leave_request_id WHERE l.date_of_leave='%s' AND l.status='approved' AND r.userid=%s and r.leads_id=%s" % (
date_str, userid, leads_id)
num_of_leave = conn.execute(sql).fetchone()
num_of_leave = int('%d' % num_of_leave.num_of_leave)
sql = "SELECT COUNT(l.id)AS num_of_marked_absent FROM leave_request_dates l JOIN leave_request r ON r.id = l.leave_request_id WHERE l.date_of_leave='%s' AND l.status='absent' AND r.userid=%s and r.leads_id=%s" % (
date_str, userid, leads_id)
num_of_marked_absent = conn.execute(sql).fetchone()
num_of_marked_absent = int('%d' %
num_of_marked_absent.num_of_marked_absent)
s = couchdb.Server(settings.COUCH_DSN)
db = s['rssc_time_records']
d = datetime.strptime(date_str, '%Y-%m-%d')
from_year = '%s' % d.strftime('%Y')
from_month = '%s' % d.strftime('%m')
from_day = '%s' % d.strftime('%d')
time_in = ""
userid = int(userid)
r = db.view(
'rssc_reports/userid_timein',
startkey=[
userid,
[int(from_year),
int(from_month),
int(from_day), 0, 0, 0, 0]
],
endkey=[
userid,
[int(from_year),
int(from_month),
int(from_day), 23, 59, 59, 0]
],
descending=False)
for row in r.rows:
mode, time_out, leads_id, subcon_id = row['value']
if mode == 'time record':
userid, time_in = row['key']
break
day = d.strftime('%a')
day = day.lower()
five_minutes_before_time = ""
five_minutes_after_time = ""
two_hours_after_time = ""
ten_minutes_before_time = ""
current_time = datetime.now()
#return work['staff_start_work_hour']
if time_in:
login = datetime(int(time_in[0]), int(time_in[1]), int(time_in[2]),
int(time_in[3]), int(time_in[4]), int(time_in[5]))
if d.strftime('%Y-%m-%d') == login.strftime('%Y-%m-%d'):
timein = login
five_minutes_before_time = get_five_minutes_before_time(
work['staff_start_work_hour'])
five_minutes_after_time = get_five_minutes_after_time(
work['staff_start_work_hour'])
if day not in work_days:
if flexi == 'no':
compliance = 'extra day'
else:
compliance = 'flexi'
if day in work_days:
if flexi == 'no':
if timein < five_minutes_before_time:
compliance = 'early login'
if timein > five_minutes_after_time:
compliance = 'late'
if timein >= five_minutes_before_time and timein <= five_minutes_after_time:
compliance = 'present'
else:
compliance = 'flexi'
else:
if int(num_of_marked_absent) > 0:
compliance = 'absent'
if int(num_of_marked_absent) == 0:
if day in work_days:
if int(num_of_leave) > 0:
compliance = 'on leave'
else:
two_hours_after_time = get_two_hours_after_time(
work['staff_start_work_hour'])
if flexi == 'no':
if complete: # used by Running Late page
if current_time < two_hours_after_time:
if work['staff_start_work_hour'] < current_time:
compliance = 'running late'
else:
if current_time >= get_ten_minutes_before_time(
work['staff_start_work_hour']
) and current_time <= work[
'staff_start_work_hour']:
compliance = '10 minutes'
else:
compliance = "not yet working"
else:
if int(num_of_leave) > 0:
compliance = 'on leave'
else:
compliance = 'absent'
else:
if current_time < two_hours_after_time:
compliance = "not yet working"
else:
compliance = 'absent'
else:
compliance = 'flexi'
if day not in work_days:
if int(num_of_leave) > 0:
compliance = 'on leave'
else:
compliance = 'no schedule'
if datetime(starting_date.year, starting_date.month,
starting_date.day) > datetime(d.year, d.month, d.day):
compliance = "not yet working"
data = dict(compliance=compliance,
work_schedule=work,
staff_start_work_hour=work['staff_start_work_hour'],
timein=timein)
return data
def process(date_str=None):
logging.info('executing get_staff_daily_attendance.process %s' %
get_ph_time())
if date_str:
now = datetime.strptime(date_str, '%Y-%m-%d')
else:
now = get_ph_time()
now = now - timedelta(days=1)
start_date = now
end_date = now
#return now
weekday_name = now.strftime('%a')
weekday_name = weekday_name.lower()
weekday_start = '%s_start' % weekday_name
weekday_finish = '%s_finish' % weekday_name
weekday_number_hrs = '%s_number_hrs' % weekday_name
#return weekday_name
conn = engine.connect()
query = "SELECT s.id, s.userid, s.starting_date, s.end_date, s.status FROM subcontractors s JOIN leads l ON l.id = s.leads_id WHERE s.status in ('ACTIVE', 'suspended', 'terminated', 'resigned')"
subcons = conn.execute(query).fetchall()
#str="\n\nResults:\n"
subcons_str = ""
for s in subcons:
sid = '%d' % s.id
starting_date = '%s' % s.starting_date
status = s.status
ending_date = '%s' % s.end_date
sid = int(sid)
starting_date = datetime.strptime(starting_date, '%Y-%m-%d')
if status == 'ACTIVE' or status == 'suspended':
if starting_date <= end_date:
subcons_str += '%d,' % sid
#str += 'subcon_id : %s starting_date : %s status : %s end_date : %s\n\n' % (sid, starting_date, status, ending_date)
if status == 'terminated' or status == 'resigned':
if ending_date != 'None':
ending_date = datetime.strptime(ending_date,
'%Y-%m-%d %H:%M:%S')
ending_date = datetime.strptime(
'%s' % ending_date.strftime('%Y-%m-%d'), '%Y-%m-%d')
if starting_date <= end_date:
if ending_date >= start_date and ending_date >= end_date:
subcons_str += '%d,' % sid
#str += 'subcon_id : %s starting_date : %s status : %s end_date : %s\n\n' % (sid, starting_date, status, ending_date)
if ending_date >= start_date and ending_date <= end_date:
subcons_str += '%d,' % sid
#str += 'subcon_id : %s starting_date : %s status : %s end_date : %s\n\n' % (sid, starting_date, status, ending_date)
order_by_str = 'order by p.fname ASC'
sql = "select s.id, s.userid, s.leads_id, s.starting_date, s.work_days, s.flexi, "
sql += "staff_working_timezone, client_timezone, client_start_work_hour, client_finish_work_hour, "
sql += "(%s)AS staff_start_hr, (%s)as staff_finish_hr, (%s)as staff_num_hrs " % (
weekday_start, weekday_finish, weekday_number_hrs)
sql += "from subcontractors s "
sql += "left join personal p on p.userid=s.userid "
sql += "left join leads l on l.id=s.leads_id "
sql += "where s.id not in (select subcon_id from client_subcontractors_lookup where subcon_id is not null) "
sql += "and s.id in(%s) " % subcons_str[:-1]
sql += "and date(s.starting_date)<='%s' order by p.fname;" % now.strftime(
'%Y-%m-%d')
#sql += "and s.status in('ACTIVE', 'suspended', 'terminated', 'resigned')"
#sql += "and s.status in('ACTIVE', 'suspended')"
#sql += "and s.id in(1345, 1713, 3142, 3612)"
#sql += "and s.leads_id not in(11)"
#sql += "and s.leads_id in(11, 8300, 9587)"
#sql += "and date(s.starting_date)<='%s' order by p.fname;" % now.strftime('%Y-%m-%d')
#return sql
subcons = conn.execute(sql).fetchall()
#return subcons
data = []
subcon_userid = {}
subcon_leads_id = {}
subcon_starting_date = {}
subcon_work_days = {}
subcon_working_days = {}
subcon_client_working_hours = {}
subcon_staff_timezone = {}
staff_start_work_hour = None
staff_finish_work_hour = None
have_sched = True
staff_start_hr = None
staff_num_hrs = 0
str = "\n\nResults:\n"
subcon_client_currency = {}
currency = ""
aud_currency = 0
usd_currency = 0
gbp_currency = 0
no_currency = 0
currencies = []
subcon_aud_currencies = []
subcon_usd_currencies = []
subcon_gbp_currencies = []
subcon_none_currencies = []
for s in subcons:
sid = '%d' % s.id
userid = '%d' % s.userid
leads_id = '%d' % s.leads_id
work_days_str = '%s' % s.work_days
currency = getClientCouchCurrencyandGst(leads_id)
if currency == "AUD":
aud_currency = aud_currency + 1
subcon_aud_currencies.append(sid)
if currency == "USD":
usd_currency = usd_currency + 1
subcon_usd_currencies.append(sid)
if currency == "GBP":
gbp_currency = gbp_currency + 1
subcon_gbp_currencies.append(sid)
if currency == "None":
no_currency = no_currency + 1
subcon_none_currencies.append(sid)
if not work_days_str:
work_days_str = 'mon,tue,wed,thu,fri'
staff_start_work_hour = s.staff_start_hr
staff_finish_work_hour = s.staff_finish_hr
if s.staff_num_hrs:
staff_num_hrs = s.staff_num_hrs
ph_tz = timezone('%s' % s.staff_working_timezone)
client_tz = timezone('%s' % s.client_timezone)
data.append(sid)
subcon_userid[sid] = userid
subcon_leads_id[sid] = leads_id
subcon_work_days[sid] = work_days_str
subcon_starting_date[sid] = '%s' % s.starting_date
subcon_client_currency[sid] = '%s' % currency
work_days = work_days_str.split(",")
working_days = []
if weekday_name in work_days:
have_sched = True
if weekday_name not in work_days:
have_sched = False
client_working_hours = {
'client_start_work_hour': '%s' % s.client_start_work_hour,
'client_finish_work_hour': '%s' % s.client_finish_work_hour,
'client_timezone': '%s' % s.client_timezone,
'staff_working_timezone': '%s' % s.staff_working_timezone,
'flexi': '%s' % s.flexi
}
if staff_start_work_hour == None or not have_sched:
staff_start_work_hour = '%s %s' % (now.strftime('%Y-%m-%d'),
(s.client_start_work_hour))
staff_start_work_hour = client_tz.localize(
datetime.strptime(staff_start_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(ph_tz)
staff_start_work_hour = '%s' % staff_start_work_hour.strftime(
'%H:%M:%S')
if staff_finish_work_hour == None or not have_sched:
staff_finish_work_hour = '%s %s' % (now.strftime('%Y-%m-%d'),
(s.client_finish_work_hour))
staff_finish_work_hour = client_tz.localize(
datetime.strptime(staff_finish_work_hour,
'%Y-%m-%d %H:%M:%S')).astimezone(ph_tz)
staff_finish_work_hour = '%s' % staff_finish_work_hour.strftime(
'%H:%M:%S')
working_days.append(
dict(day='%s' % weekday_name,
staff_start_work_hour='%s' % staff_start_work_hour,
staff_finish_work_hour='%s' % staff_finish_work_hour,
regular_contract_hrs='%d' % staff_num_hrs))
subcon_working_days[sid] = working_days
subcon_client_working_hours[sid] = client_working_hours
subcon_staff_timezone[sid] = '%s' % s.staff_working_timezone
#str +='=> %s %s %s \n' % (sid, staff_start_work_hour, staff_finish_work_hour)
doc_id = ""
query = ""
if settings.DEBUG:
#mongo_client = MongoClient(host=settings.MONGO_TEST)
#mongo_client = MongoClient()
try:
mongo_client = MongoClient()
except:
mongo_client = MongoClient(host=settings.MONGO_TEST)
else:
mongo_client = MongoClient(host=settings.MONGO_PROD, port=27017)
#mongo_client = MongoClient(host=MONGO_TEST)
#connect to the test db
db = mongo_client.reports
#retrieve the person collection
col = db.staff_daily_attendance
#creating a document - CREATE
#col.insert({"requested_on": '%s' % now}, {"subcon_userid": {"key1" : 1, "key2": 2}})
currencies = [
{
'currency': 'AUD',
'total': '%s' % aud_currency,
'subcons': subcon_aud_currencies
},
{
'currency': 'USD',
'total': '%s' % usd_currency,
'subcons': subcon_usd_currencies
},
{
'currency': 'GBP',
'total': '%s' % gbp_currency,
'subcons': subcon_gbp_currencies
},
{
'currency': 'None',
'total': '%s' % no_currency,
'subcons': subcon_none_currencies
},
]
record = {
'requested_on': '%s' % get_ph_time(),
'subcontractor_ids': data,
'subcon_userid': subcon_userid,
'subcon_leads_id': subcon_leads_id,
'subcon_work_days': subcon_work_days,
'subcon_starting_date': subcon_starting_date,
'subcon_working_days': subcon_working_days,
'subcon_client_working_hours': subcon_client_working_hours,
'subcon_staff_timezone': subcon_staff_timezone,
'date_search': [now.strftime('%Y-%m-%d')],
'subcon_client_currency': subcon_client_currency,
'currencies': currencies,
}
doc_id = col.insert(record)
#retrieve a random document - READ
#doc = col.find_one()
#doc_id = '%s' % doc['_id']
#return doc_id
query = "INSERT INTO mongo_staff_daily_attendance(mongodb_doc_id, search_date, date_created, status) VALUES('%s', '%s', '%s', '%s');" % (
doc_id, '%s' % now.strftime('%Y-%m-%d'), '%s' % get_ph_time(),
'waiting')
mongo_staff_daily_attendance_id = conn.execute(query).lastrowid
conn.close()
#send_task("get_staff_daily_attendance_result.process", [mongo_staff_daily_attendance_id])
get_staff_daily_attendance_result.process(mongo_staff_daily_attendance_id)
def run(self, doc_id):
"""given the doc_id, send email
"""
logging.info('Sending topup order %s ' % doc_id)
couch_server = couchdb.Server(settings.COUCH_DSN)
db_client_docs = couch_server['client_docs']
doc = db_client_docs.get(doc_id)
#get the template
t = Template(file="templates/client_topup_invoice_created.tmpl")
#assign variables to the template file
t.client_fname = doc['client_fname']
t.client_lname = doc['client_lname']
#get currency
s = text("""SELECT sign from currency_lookup
WHERE code = :code
""")
conn = engine.connect()
r = conn.execute(s, code=doc['currency']).fetchone()
t.currency_sign = r.sign
#get client details
s = text(
"""SELECT company_name, company_address, acct_dept_email1, acct_dept_email2
FROM leads
WHERE id = :client_id
""")
client_data = conn.execute(s, client_id=doc['client_id']).fetchone()
t.client_data = client_data
items_converted_date = []
items = doc['items']
for item in items:
a = item.copy()
if a.has_key('start_date'):
b = a['start_date']
a['start_date'] = date(b[0], b[1], b[2])
else:
a['start_date'] = None
if a.has_key('end_date'):
c = a['end_date']
a['end_date'] = date(c[0], c[1], c[2])
else:
a['end_date'] = None
amount = Decimal(a['amount'])
a['amount'] = locale.format('%0.2f', amount, True)
items_converted_date.append(a)
t.invoice_items = items_converted_date
t.order_id = doc['order_id']
t.sub_total = locale.format('%0.2f', Decimal(doc['sub_total']), True)
t.currency = doc['currency']
t.gst_amount = locale.format('%0.2f', Decimal(doc['gst_amount']), True)
t.total_amount = locale.format('%0.2f', Decimal(doc['total_amount']),
True)
t.DEBUG = settings.DEBUG
t.doc_id = doc['_id']
t.status = doc['status']
html_message = '%s' % t
msg = MIMEMultipart()
part1 = MIMEText('%s' % html_message, 'html')
part1.add_header('Content-Disposition', 'inline')
msg.attach(part1)
#check acct_dept_email1 and acct_dept_email2 fields and other sorts using task GetEmails
if settings.DEBUG:
result = send_task("EmailSender.GetEmails", [doc['client_id']])
else:
celery = Celery()
celery.config_from_object(sc_celeryconfig)
result = celery.send_task("EmailSender.GetEmails",
[doc['client_id']])
email_recipients = result.get()
msg['To'] = email_recipients['to']
if email_recipients['cc'] != '':
msg['Cc'] = email_recipients['cc']
msg['From'] = '*****@*****.**'
msg['Reply-To'] = '*****@*****.**'
subject = 'REMOTE STAFF PREPAID ORDER # %s' % doc['order_id']
recipients = email_recipients['recipients']
recipients.append('*****@*****.**')
recipients.append('*****@*****.**')
if settings.DEBUG:
subject = 'TEST %s' % subject
recipients = [settings.EMAIL_ALERT]
msg['Subject'] = subject
s = smtplib.SMTP(host=settings.MAILGUN_CONFIG['server'],
port=settings.MAILGUN_CONFIG['port'])
s.starttls()
s.login(settings.MAILGUN_CONFIG['username'],
settings.MAILGUN_CONFIG['password'])
s.sendmail('*****@*****.**', recipients, msg.as_string())
s.quit()
#store the email for archiving purposes
history = []
history.append(
dict(timestamp=get_ph_time().strftime('%F %H:%M:%S'),
changes='Order created',
by='TopupOrder celery task'))
doc_order_archive = dict(
type='order archive',
email_recipients=email_recipients,
history=history,
client_id=int(doc['client_id']),
subject='REMOTE STAFF PREPAID ORDER # %s' % doc['order_id'],
)
db_client_docs.save(doc_order_archive)
#attach message
db_client_docs.put_attachment(doc_order_archive, html_message,
'message.html')
#add history that the document was sent
doc = db_client_docs.get(doc_id)
if doc.has_key('history') == False:
history = []
else:
history = doc['history']
history.append(
dict(timestamp=get_ph_time().strftime('%F %H:%M:%S'),
changes='Email sent to %s.' %
string.join(email_recipients['recipients'], ','),
by='TopupOrder celery task'))
doc['history'] = history
doc['mongo_synced'] = False
db_client_docs.save(doc)
conn.close()
def run(self, client_id):
return_data = dict(
to=[],
cc=[],
)
sql = text("""SELECT address_to, default_email_field, cc_emails
FROM leads_send_invoice_setting
WHERE leads_id = :client_id
""")
conn = engine.connect()
client_data = conn.execute(sql, client_id=client_id).fetchone()
# records found on leads_send_invoice_setting
if client_data != None:
if client_data.address_to == 'main_acct_holder':
if client_data.default_email_field == 'email':
sql = text("""SELECT fname, lname,
email
FROM leads
WHERE id = :client_id
""")
elif client_data.default_email_field == 'acct_dept_email1':
sql = text("""SELECT fname, lname,
acct_dept_email1 as email
FROM leads
WHERE id = :client_id
""")
else:
sql = text("""SELECT fname, lname,
%s as email
FROM leads
WHERE id = :client_id
""" % client_data.default_email_field)
a = conn.execute(sql, client_id=client_id).fetchone()
return_data['to'].append(string.strip(a.email))
else:
sql = text("""SELECT %s FROM leads
where id = :client_id
""" % client_data.default_email_field)
result = conn.execute(sql, client_id=client_id).fetchone()
return_data['to'].append(
result[client_data.default_email_field])
#process cc_emails
cc = []
cc_emails = client_data.cc_emails
if cc_emails not in ['', None]:
cc_emails = string.split(cc_emails, ',')
for cc_email in cc_emails:
sql = text("""SELECT %s FROM leads
where id = :client_id
""" % cc_email)
b = conn.execute(sql, client_id=client_id).fetchone()
email = b[0]
if validate_email(email):
cc.append(string.strip(email))
else:
logging.info('Invalid leads.%s:%s for client_id:%s ' %
(cc_email, email, client_id))
return_data['cc'] = cc
else:
cc = []
sql = text("""SELECT fname, lname, email, acct_dept_email1,
acct_dept_email2
FROM leads
WHERE id = :client_id
""")
a = conn.execute(sql, client_id=client_id).fetchone()
if a == None:
return_data['to'].append('*****@*****.**')
return_data['to'].append('Invalid client_id:%s' % client_id)
return return_data
to = []
if a.email not in ['', None]:
if validate_email(a.email):
to.append(string.strip(a.email))
else:
logging.info('Invalid leads.email:%s for client_id:%s ' %
(a.email, client_id))
if a.acct_dept_email1 not in ['', None]:
if validate_email(a.acct_dept_email1):
cc.append(string.strip(a.acct_dept_email1))
else:
logging.info(
'Invalid leads.acct_dept_email1:%s for client_id:%s' %
(a.acct_dept_email1, client_id))
if a.acct_dept_email2 not in ['', None]:
if validate_email(a.acct_dept_email2):
cc.append(string.strip(a.acct_dept_email2))
else:
logging.info(
'Invalid leads.acct_dept_email2:%s for client_id:%s' %
(a.acct_dept_email2, client_id))
return_data['to'] = to
return_data['cc'] = cc
conn.close()
return return_data
def create_overpayment_invoice(doc_id):
"""generate an invoice based on over payment
return a document copy for further processing
"""
logging.info("prepaid_send_receipt.create_overpayment_invoice %s" % doc_id)
#couchdb settings
s = couchdb.Server(settings.COUCH_DSN)
db = s['client_docs']
doc = db.get(doc_id)
if doc == None:
raise Exception('Failed to Create Over Payment Invoice', '%s not found' % doc_id)
if doc.has_key('input_amount') == False:
raise Exception('Failed to Create Over Payment Invoice', 'input_amount field for %s not found' % doc_id)
leads_id = doc['client_id']
#check if client has couchdb settings
now = get_ph_time(as_array = True)
r = db.view('client/settings', startkey=[leads_id, now],
endkey=[leads_id, [2011,1,1,0,0,0,0]],
descending=True, limit=1)
if len(r.rows) == 0: #no client settings, send alert
raise Exception('FAILED to create Prepaid Based Invoice', 'Please check leads_id : %s\r\nNo couchdb client settings found.' % (leads_id))
currency, apply_gst = r.rows[0]['value']
#check clients running balance
r = db.view('client/running_balance', key=leads_id)
if len(r.rows) == 0:
running_balance = Decimal('0.00')
else:
running_balance = Decimal('%0.2f' % r.rows[0].value)
#get fname, lname
sql = text("""SELECT fname, lname, email, registered_domain from leads
WHERE id = :leads_id
""")
conn = engine.connect()
client_fname, client_lname, client_email, registered_domain = conn.execute(sql, leads_id = leads_id).fetchone()
#get last order id
r = db.view('client/last_order_id', startkey=[leads_id, "%s-999999999" % leads_id],
endkey=[leads_id, ""], descending=True, limit=1)
if len(r.rows) == 0:
last_order_id = 1
else:
last_order_id_str = r.rows[0].key[1]
x, last_order_id = string.split(last_order_id_str, '-')
last_order_id = int(last_order_id)
last_order_id += 1
order_id = '%s-%08d' % (leads_id, last_order_id)
doc_total_amount = Decimal(doc['total_amount'])
doc_input_amount = Decimal(doc['input_amount'])
total_amount = doc_input_amount - doc_total_amount
if apply_gst == 'Y':
sub_total = total_amount / Decimal('1.1')
gst_amount = total_amount - sub_total
else:
sub_total = total_amount
gst_amount = Decimal('0.00')
invoice_item = dict(
item_id = 1,
unit_price = '%0.2f' % sub_total,
qty = '1.00',
amount = '%0.2f' % sub_total,
description = 'overpayment from Invoice # %s' % doc['order_id'],
)
invoice_items = [invoice_item]
doc_order = dict(
added_by = 'celery overpayment',
apply_gst = apply_gst,
client_id = leads_id,
history = [],
type = 'order',
added_on = now,
items = invoice_items,
status = 'paid',
date_paid = get_ph_time().strftime('%F %H:%M:%S'),
payment_date = get_ph_time(as_array = True),
order_id = order_id,
sub_total = '%0.2f' % sub_total,
input_amount = '%0.2f' % total_amount,
total_amount = '%0.2f' % total_amount,
gst_amount = '%0.2f' % gst_amount,
client_fname = client_fname,
client_lname = client_lname,
client_email = client_email,
registered_domain = registered_domain,
currency = currency,
overpayment_from = doc['order_id'],
overpayment_from_doc_id = doc['_id'],
payment_mode = doc['payment_mode'],
admin_id = None,
admin_name = None,
set_paid_by = "system"
)
doc_order['running_balance'] = '%0.2f' % running_balance
db.save(doc_order)
logging.info('created overpayment %s' % doc_order['_id'])
insert_invoice_payment_collection(doc_order)
#sync auto paid invoice to xero
import pycurl
c = pycurl.Curl()
c.setopt(c.URL, settings.API_URL+'/mongo-index/sync-client-invoice-by-order-id/')
try:
# python 3
from urllib.parse import urlencode
except ImportError:
# python 2
from urllib import urlencode
post_data = {'order_id': order_id, 'xero_sync':"1"}
logging.info("Executing /mongo-index/sync-client-invoice-by-order-id?order_id=%s&xero_sync=1" % order_id)
# Form data must be provided already urlencoded.
postfields = urlencode(post_data)
# Sets request method to POST,
# Content-Type header to application/x-www-form-urlencoded
# and data to send in request body.
c.setopt(c.POSTFIELDS, postfields)
c.perform()
c.close()
#send_task('notify_devs.send', ['Created Overpayment for Invoice %s' % doc_order['order_id'], 'Please check :\n%r' % doc_order.copy()])
conn.close()
return doc_order.copy()
def run(self, client_id):
"""
>>> r = GetEmails()
>>> r.run(8745)
"""
return_data = dict(
recipients=[],
to='',
cc='',
)
sql = text("""SELECT address_to, default_email_field, cc_emails
FROM leads_send_invoice_setting
WHERE leads_id = :client_id
""")
conn = engine.connect()
client_data = conn.execute(sql, client_id=client_id).fetchone()
# records found on leads_send_invoice_setting
if client_data != None:
recipients = []
if client_data.address_to == 'main_acct_holder':
sql = text("""SELECT fname, lname,
%s AS email
FROM leads
WHERE id = :client_id
""" % client_data.default_email_field)
a = conn.execute(sql, client_id=client_id).fetchone()
return_data['to'] = '"%s %s" <%s>' % (string.capitalize(
a.fname), string.capitalize(a.lname), a.email)
recipients.append(a.email)
else:
sql = text("""SELECT * FROM leads
where id = :client_id
""")
result = conn.execute(sql, client_id=client_id).fetchone()
return_data['to'] = '"%s" <%s>' % (
result[client_data.address_to],
result[client_data.default_email_field])
recipients.append(result[client_data.default_email_field])
#process cc_emails
cc = []
cc_emails = client_data.cc_emails
if cc_emails not in ['', None]:
cc_emails = string.split(cc_emails, ',')
for cc_email in cc_emails:
sql = text("""SELECT %s FROM leads
where id = :client_id
""" % cc_email)
b = conn.execute(sql, client_id=client_id).fetchone()
email = b[0]
if validate_email(email):
cc.append(email)
recipients.append(email)
else:
logging.info('Invalid leads.%s:%s for client_id:%s ' %
(cc_email, email, client_id))
return_data['recipients'] = recipients
return_data['cc'] = string.join(cc, ',')
else:
cc = []
sql = text("""SELECT fname, lname, email, acct_dept_email1,
acct_dept_email2
FROM leads
WHERE id = :client_id
""")
a = conn.execute(sql, client_id=client_id).fetchone()
if a == None:
return_data['recipients'] = ['*****@*****.**']
return_data['to'] = 'Invalid client_id:%s' % client_id
return return_data
recipients = []
if a.email not in ['', None]:
if validate_email(a.email):
recipients.append(a.email)
else:
logging.info('Invalid leads.email:%s for client_id:%s ' %
(a.email, client_id))
if a.acct_dept_email1 not in ['', None]:
if validate_email(a.acct_dept_email1):
recipients.append(a.acct_dept_email1)
cc.append(a.acct_dept_email1)
else:
logging.info(
'Invalid leads.acct_dept_email1:%s for client_id:%s' %
(a.acct_dept_email1, client_id))
if a.acct_dept_email2 not in ['', None]:
if validate_email(a.acct_dept_email2):
recipients.append(a.acct_dept_email2)
cc.append(a.acct_dept_email2)
else:
logging.info(
'Invalid leads.acct_dept_email2:%s for client_id:%s' %
(a.acct_dept_email2, client_id))
return_data['recipients'] = recipients
return_data['to'] = '"%s %s" <%s>' % (string.capitalize(
a.fname), string.capitalize(a.lname), a.email)
return_data['cc'] = string.join(cc, ',')
conn.close()
return return_data
def process_doc_id(doc_id):
logging.info(
'compliance.process_doc_id checking %s from sc.remotestaff.com.au' %
doc_id)
s = couchdb.Server(settings.COUCH_DSN)
#send notice to devs
now = get_ph_time(as_array=False)
to = ['*****@*****.**']
couch_mailbox = s['mailbox']
date_created = [
now.year, now.month, now.day, now.hour, now.minute, now.second
]
mailbox = dict(
sent=False,
bcc=None,
cc=None,
created=date_created,
generated_by='celery compliance.process_doc_id',
html=None,
text='executing celery task compliance.process_doc_id %s' % doc_id,
subject='executing celery task compliance.process_doc_id %s' % doc_id,
to=to)
mailbox['from'] = '*****@*****.**'
#couch_mailbox.save(mailbox)
#subconlist_reporting doc
db = s['subconlist_reporting']
doc = db.get(doc_id)
if doc == None:
raise Exception('subconlist_reporting document not found : %s' %
doc_id)
subcontractor_ids = doc['subcontractor_ids']
DATE_SEARCH = doc['date_search']
page_usage = ""
complete = False
if 'page_usage' in doc:
page_usage = doc['page_usage']
if page_usage == "running late":
complete = True
str = ""
compliance_result = {}
registered_hrs_result = {}
conn = engine.connect()
for sid in subcontractor_ids:
userid = doc['subcon_userid'][sid]
leads_id = doc['subcon_leads_id'][sid]
starting_date = doc['subcon_starting_date'][sid]
work_days = doc['subcon_work_days'][sid].split(',')
subcon_working_days = doc['subcon_working_days'][sid]
subcon_client_working_hours = doc['subcon_client_working_hours'][sid]
dates = []
registered_hrs = []
for d in DATE_SEARCH:
if sid:
compliance = get_compliance_result(
conn, sid, d, userid, leads_id, starting_date, work_days,
subcon_working_days, subcon_client_working_hours, True)
#return compliance
dates.append(
dict(date=d,
compliance=compliance['compliance'],
timein=compliance['timein']))
date = datetime.strptime(d, '%Y-%m-%d')
day = date.strftime('%a')
day = day.lower()
staff_start_work_hour = ""
staff_finish_work_hour = ""
regular_contract_hrs = 0
for work in subcon_working_days:
if work['day'] == day:
staff_start_work_hour = work['staff_start_work_hour']
staff_finish_work_hour = work['staff_finish_work_hour']
regular_contract_hrs = work['regular_contract_hrs']
break
registered_hrs.append(
dict(date=d,
staff_start_work_hour=staff_start_work_hour,
staff_finish_work_hour=staff_finish_work_hour,
regular_contract_hrs=regular_contract_hrs))
#str +='\n sid=>%s %s' % (sid, compliance)
compliance_result[int(sid)] = dates
registered_hrs_result[int(sid)] = registered_hrs
doc['compliance_result'] = compliance_result
doc['registered_hrs_result'] = registered_hrs_result
conn.close()
db.save(doc)
if page_usage == "attendance report":
if settings.DEBUG:
send_task("subcon_adj_hours.process_doc_id", [doc_id])
else:
celery = Celery()
celery.config_from_object(sc_celeryconfig)
celery.send_task("subcon_adj_hours.process_doc_id", [doc_id])
logging.info('executing subcon_adj_hours.process_doc_id %s' % doc_id)
#send email
to = ['*****@*****.**']
couch_mailbox = s['mailbox']
date_created = [
now.year, now.month, now.day, now.hour, now.minute, now.second
]
mailbox = dict(
sent=False,
bcc=None,
cc=None,
created=date_created,
generated_by='celery compliance.process_doc_id',
html=None,
text='sending task to subcon_adj_hours.process_doc_id %s' % doc_id,
subject='sending task to subcon_adj_hours.process_doc_id %s' %
doc_id,
to=to)
mailbox['from'] = '*****@*****.**'