def age_in_seconds(column_name, org_id):
query = "SELECT {} FROM notch_crm.reports_configuration where orgid ={};".format(
column_name, org_id)
new_company_definition = mySqlConnection.read_query(
mySqlConnection.connection, query)
new_company_definition = new_company_definition[0][0]
return new_company_definition * 86400
def get_user_deals(org_id, user_id):
all_deals_query = "SELECT * FROM notch_crm.deals where orgid = {} and creatorid = {}".format(org_id,user_id)
all_deals = mySqlConnection.read_query(mySqlConnection.connection, all_deals_query)
return all_deals
def get_org_deals(orgId):
all_deals_query = "SELECT * FROM notch_crm.deals where orgid = {};".format(orgId)
all_deals = mySqlConnection.read_query(mySqlConnection.connection, all_deals_query)
return all_deals
from NotchReports import mySqlConnection
from datetime import datetime, date, time
a_query = "SELECT * FROM notch_crm.activity_log where orgid = 16400 and creatorid = 16364;"
#for a in
d_year = 2020
t_query ="SELECT * FROM notch_crm.activity_log where teamid = 16405 and creatorid = 16364"
a = mySqlConnection.read_query(mySqlConnection.connection, a_query)
result = {}
for each in a:
datee = each[5]
datee = datetime.strptime(datee, "%d/%m/%Y %H:%M:%S")
Yr = datee.year
if Yr == 2020:
typpe = each[15]
if typpe in result:
result[typpe] += 1
else: result[typpe] = 1
else: pass
print(result)
def all_deals_lost_yearly(orgid, creatorid, year):
all_deals_lost_yearly = "SELECT creatorid, actual_closed_date, created_date, comment FROM notch_crm.deals where orgid = {} and creatorid = {} " \
"and curr_stage = 'Lost' and actual_closed_date like '{}%';".format(orgid, creatorid,year)
all_deals = mySqlConnection.read_query(mySqlConnection.connection,
all_deals_lost_yearly)
return len(all_deals)
from NotchReports import mySqlConnection
from datetime import datetime
from datetime import date
from math import floor
import calendar
from pprint import pprint
t_query = "SELECT * FROM notch_crm.deals where orgid = 16400 and creatorid = 16364 and curr_stage = 'Won';"
new_query = "SELECT * FROM notch_crm.deals where orgid = 16400 and creatorid = 16364 and curr_stage = 'Won' and actual_closed_date between '2021-01-01' and '2021-19-01';"
all_data = mySqlConnection.read_query(mySqlConnection.connection, new_query)
win_lists = []
if len(all_data) < 1:
print('no data was returned for the query sent')
else:
print('There are ', len(all_data), 'records in this list \n')
for each in all_data:
time_to_win = {}
d_code = each[0]
cr_date = each[8]
cl_date = each[31]
deal_name = each[16]
y = datetime.strptime(cr_date, "%Y-%m-%d %H:%M:%S")
z = calendar.timegm(y.utctimetuple())
x = datetime.strptime(cl_date, "%Y-%m-%d %H:%M:%S")
m = calendar.timegm(x.utctimetuple())
result = m - z
from NotchReports import mySqlConnection
from pprint import pprint
import math
getDealsquery = "SELECT * FROM notch_crm.deals where orgid = 16400;"
org_deals = mySqlConnection.read_query(mySqlConnection.connection,getDealsquery)
get_all_deals = mySqlConnection.read_query(mySqlConnection.connection, getDealsquery)
#SELECT * FROM notch_crm.deals where orgid = 16400
#SELECT * FROM notch_crm.deals where orgid = 16400 and creatorid = 16364 and created_date like '2020%';
#SELECT * FROM notch_crm.deals where orgid = 16400 and creatorid = 16364 and created_date like '2020%' and curr_stage = 'Lost';
#SELECT * FROM notch_crm.deals where orgid = 16400 and creatorid = 16364 and created_date like '2020%' and curr_stage = 'Won';
#ownername = 23
#ownerId: 9
#actual_date =
#created_date = 8
#currstage = 10
#owners [16364, 16376]
#teams []
owner = "16376"
mydeals = []
for each in org_deals:
owner_id = each[9]
if owner_id == owner:
mydeals.append(each)
else: pass
dealinfo = {}