def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = [
'partner_code', 'partner_name', 'total_revenue', 'month',
'partner_status'
]
self.Partner_Status = ["True", "ep.is_active", "not ep.is_active"]
def get(self, request, *args, **kwargs):
QUERY_SUMMARY = '''select distinct((select count(id) from cfms_contract where contract_status ~ 'Active' and start_date >= '2013-01-01' AND end_date <= '2020-01-01')) as Contracts,
(select count(*) from asset_title where status = TRUE and added_on BETWEEN '2013-01-01' AND '2020-01-01') as Titles,
(select sum(calculated_revenue_usd) from cfms_earningsplit) as Top_Line_Revenue,
(select sum(calculated_revenue_usd) from cfms_earningsplit where partner_id NOT IN (2,3)) as Gross_Revenue,
(select sum(calculated_revenue_usd) from cfms_earningsplit where partner_id IN (2,3)) as Partner_Revenue from cfms_contract as a;'''
QUERY_OUTSTANDING_CONTRACTS = '''SELECT cfms_contract.code,sum(cfms_earning.revenue) as a FROM cfms_contract
INNER JOIN cfms_earning ON cfms_contract.id = cfms_earning.contract_id where cfms_contract.contract_status ~ 'Active'
group by cfms_contract.code order by a desc limit 10;'''
QUERY_OUTSTANDING_PLATFORMS = '''SELECT generic_platform.platform_name, sum(cfms_earning.revenue) as a
FROM generic_platform INNER JOIN cfms_earning ON cfms_earning.platform_id_id = generic_platform.plat_id
group by generic_platform.plat_id order by a desc limit 10;'''
QUERY_OUTSTANDING_PARTNER = '''SELECT extras_partner.partner_name, sum(cfms_earningsplit.calculated_revenue_usd) as a
FROM extras_partner INNER JOIN cfms_earningsplit ON cfms_earningsplit.partner_id = extras_partner.id group by extras_partner.id order by a desc limit 10;'''
self.psy = PostgreSQLConnector()
response_summary = self.psy._custom(QUERY_SUMMARY, "select",
"named_tuple")
response_contracts_summary = self.psy._custom(
QUERY_OUTSTANDING_CONTRACTS, "select", 'named_tuple')
response_platforms_summary = self.psy._custom(
QUERY_OUTSTANDING_PLATFORMS, "select", 'named_tuple')
response_partner_summary = self.psy._custom(QUERY_OUTSTANDING_PARTNER,
"select", 'named_tuple')
return render(
request, "cfms/home.html", {
'summary': response_summary['data'],
'rcs': response_contracts_summary['data'],
'rpls': response_platforms_summary['data'],
'rpas': response_partner_summary['data']
})
def get(self, request, *args, **kwargs):
self.psy = PostgreSQLConnector()
QUERY_SUMMARY = '''Select count(Distinct(a.album_id)) as Total_Albums, sum(a.acquisition_cost) as Total_acquisition_cost, count(distinct(a.title_id)) as Total_Music_Tracks from
(SELECT aa.album_name, aat.album_id, at.acquisition_cost, aat.title_id from asset_title as at
join generic_asset_type as gat on at.type_id = gat.asset_id
join asset_album_titles as aat on aat.title_id = at.id
join asset_album as aa on aa.id = aat.album_id where gat.asset_type ~ 'Track') as a'''
response_summary = self.psy._custom(QUERY_SUMMARY, "select",
"named_tuple")
QUERY_ALBUM_SUMMARY = '''SELECT aa.album_name, aat.album_id, count(aat.title_id) as Total_Albums, floor(EXTRACT(EPOCH FROM current_timestamp-aa.added_on)/3600) as time_diff_hours from asset_title as at
join generic_asset_type as gat on at.type_id = gat.asset_id
join asset_album_titles as aat on aat.title_id = at.id
join asset_album as aa on aa.id = aat.album_id where gat.asset_type ~ 'Track' group by aa.album_name, aat.album_id, aa.added_on order by aa.added_on desc
'''
recent_albums = self.psy._custom(QUERY_ALBUM_SUMMARY, "select",
"named_tuple")
QUERY_TRACK_SUMMARY = '''SELECT at.id, at.display_name, floor(EXTRACT(EPOCH FROM current_timestamp-at.added_on)/3600) as time_diff_hours
from asset_title as at join generic_asset_type as gat on at.type_id = gat.asset_id where gat.asset_type ~ 'Track' order by at.added_on desc'''
recent_tracks = self.psy._custom(QUERY_TRACK_SUMMARY, "select",
"named_tuple")
u = request.user.get_full_name
return render(
request, "onemusic/home.html", {
'data': response_summary['data'],
'u': u,
'recent_albums': recent_albums,
'recent_tracks': recent_tracks
})
def get(self, request, *args, **kwargs):
USERNAME = str(request.user.id)
#if request.session.is_empty():
# return redirect("authme/")
#else:
psy = PostgreSQLConnector()
query = "select sa.name, sa.status, sa.about, sa.id, saa.profile_name, saa.description, sa.url from system_app sa \
inner join system_apps_assignment saa on saa.app_id_fk_id=sa.id where saa.user_id_fk_id=" + USERNAME
data = psy._custom(query, "select", "named_tuple")
return render(request, "home/home.html", {'data': data})
def update( request ):
USERID = request.user.id
for key, value in request.POST.items():
pass
tCol = key.split("_")
id = tCol.pop( )
colName = "_".join( tCol )
string = colName + " = '%s'" % ( value )
query2Update = "update dam_cloudfile set "+ string +" where code ~'%s' " % ( id )
psy = PostgreSQLConnector( )
response = psy._custom( query2Update,"update","json" )
return JsonResponse( response, safe = False )
def get(self, request, *args, **kwargs):
self.psy = PostgreSQLConnector()
QUERY_HCPC = "select keyword, cost_per_click from kpt_hcpc order by cost_per_click desc limit 20"
hcpc_result = self.psy._custom(QUERY_HCPC, "select", "named_tuple")
QUERY_HCPC_CNT = "select count(keyword) from kpt_hcpc"
hcpc_cnt_result = self.psy._custom(QUERY_HCPC_CNT, "select", "json")
QUERY_TCPC = "select keyword, cost_per_click from kpt_hcpc order by cost_per_click desc limit 20"
tcpc_result = self.psy._custom(QUERY_TCPC, "select", "named_tuple")
QUERY_TCPC_CNT = "select count(keyword) from kpt_hcpc"
tcpc_cnt_result = self.psy._custom(QUERY_TCPC_CNT, "select", "json")
return render(
request, "kpt/home.html", {
"hcpc": hcpc_result,
"tcpc": tcpc_result,
"hcpc_cnt_result": hcpc_cnt_result,
"tcpc_cnt_result": tcpc_cnt_result
})
def update(request):
USERID = request.user.id
for key, value in request.POST.items():
pass
tCol = key.split("_")
video_id = tCol.pop()
colName = "_".join(tCol)
if colName == "moderator_remark":
string = colName + " = '%s', remark_from_id = %d" % (value, USERID)
else:
string = colName + " = '%s', qced_by_id = %d" % (value.upper(),
USERID)
query2Update = "update qc_qclog set " + string + " where video_id_id IN (select id from property_youtube_videos where video_id ~ '%s')" % (
video_id)
psy = PostgreSQLConnector()
response = psy._custom(query2Update, "update", "json")
return JsonResponse(response, safe=False)
def postLogin(self, userID, request):
response = HttpResponseRedirect('/')
psy = PostgreSQLConnector()
query = '''select password from auth_user where id={} '''.format(
userID)
password_hash = psy._custom(query, "select")['data'][0][0]
eToken = str(
jwt.encode(
{
'user_id': userID,
'pswdhash': password_hash,
'ip': request.META['REMOTE_ADDR'],
'status': 'is_actvie'
},
"SECRET",
algorithm="HS256"), 'utf-8')
request.session['token'] = eToken
request.session.modified = True
response.set_cookie("ctoken", eToken, max_age=604800)
return response
class Partner_Performance_Report_DetailPage:
def __init__(self, partner_id):
self.psy = PostgreSQLConnector()
self.partner_id = int(partner_id)
def getPartnerBasicInfo( self ):
QUERY = '''select * from extras_partner where id = %d''' % (self.partner_id)
return self.psy._custom(QUERY,"select","named_tuple")
def getPartnerContracts( self ):
QUERY = '''select ccs.rev_percentage, ccs.signer, cc.id as contract_id, cc.code, cc.contract_status,
concat(gct.prefix,'-',gct.main_category,'/',gct.sub_category) as contract_type, cc.sign_date, cc.start_date,
cc.end_date, cc.perpetual, ec.id as contact_id, ec.official_name, ec.primary_contact as contact_m_pri,
ec.secondary_contact as contact_m_sec, ec.primary_email as contact_e_pri, ec.secondary_email as contact_e_sec, ec.code as contact_code,
gat.name as contact_association_name from cfms_contract_signatories as ccs
join cfms_contract as cc on cc.id = ccs.contract_id
join extras_contact as ec on ec.id = ccs.contact_id
join generic_contract_type as gct on gct.id = cc.nature_id
join generic_association_type as gat on gat.id = ec.contact_type_id where partner_id = %d''' % (self.partner_id)
return self.psy._custom(QUERY,"select","named_tuple")
def post( self, request, *args, **kwargs ):
self.psy = PostgreSQLConnector( )
CURRENT_URL = '/dam/lookups'
draw = request.POST.get("draw", 1 )
columns = request.POST.get("columns","")
order = request.POST.get("order","")
START = request.POST.get("start",0)
LENGTH = request.POST.get("length",10)
SEARCH = str(request.POST.get("search[value]",""))
USERID = request.user.id
Columns = []
DSTR = ""
QUERY_COL = '''select sa.available_operations, sa.permission_str \
from system_apps_assignment saa join system_user_form_level_permission sa \
on saa.id=sa.app_assignment_id_fk_id where saa.user_id_fk_id = %d and sa.form_url='%s' ''' % ( USERID, CURRENT_URL )
response = self.psy._custom( QUERY_COL, "select","named_tuple" )
res_perm_fields = response['data'][0][1]
for item in res_perm_fields:
l = [ item["perm"], item["ctype"], item["id"] ]
p = {}
p[item["display_name"]] = l
p["title"] = item["display_name"]
DSTR = DSTR + item["table_alias"]+"."+item["id"] + ","
Columns.append(p)
t_data = self._formatData(DSTR.rstrip(","), SEARCH, USERID, START, LENGTH, request )
data_dump = json.dumps(t_data)
data = json.loads(data_dump)
if not data['data']:
total_data = 0
else:
total_data = data['data'][0][-1]
for j in data["data"]:
j.pop()
new_response = {"columns":Columns, "data":data['data'], "draw":draw, "recordsTotal":data['count'], "recordsFiltered":total_data}
return JsonResponse( new_response, safe = False)
class QCHome(View):
def get(self, request, *args, **kwargs):
self.psy = PostgreSQLConnector()
QUERY_SUMMARY = '''SELECT distinct((SELECT COUNT(*) FROM qc_qclog WHERE qc_status=TRUE and qced_by_id = 1)) as Total_Qced,
(SELECT COUNT(*) FROM qc_qclog WHERE qc_status=FALSE and qced_by_id = 1) as Pending_QC,
(SELECT COUNT(*) FROM qc_qclog WHERE qced_by_id=1) as Total_Items,
(select count(*) from raci_property_ownership_channel_name_id_fk where property_ownership_id =
(select id as raci_po from raci_property_ownership where user_id_fk_id = 1)) as Total_CMS ,
(select count(*) from raci_property_ownership_channel_name_id_fk as raci_yt_ch where raci_yt_ch.property_ownership_id = (select id from raci_property_ownership where user_id_fk_id = 1)) as Total_Channels FROM qc_qclog;'''
QUERY_CHANNEL_SUMMARY_VIEW = '''select b.channel_name, max( CASE WHEN b.qc_status = 'TRUE' THEN count(b.*) ELSE 0 END ) AS QCED,
max( CASE WHEN b.qc_status = 'FALSE' THEN count(b.*) ELSE 0 END ) AS notQCED,
max( CASE WHEN b.qc_status IS NULL THEN count(b.*) ELSE 0 END ) AS notFlagged
from (select pc.channel_name, qc.qc_status, count(pyv.video_title) from raci_property_ownership_channel_name_id_fk as rpocn
join property_channel as pc on pc.id = rpocn.channel_id join property_youtube_videos as pyv on pyv.channel_id_fk_id = pc.id
full join qc_qclog as qc on qc.video_id_id = pyv.id where property_ownership_id IN
(select id from raci_property_ownership where user_id_fk_id = 1) group by qc.qc_status, pc.channel_name) as b group by b.channel_name'''
QUERY_CMS_SUMMARY_VIEW = '''select b.cms_name, max( CASE WHEN b.qc_status = 'TRUE' THEN count(b.*) ELSE 0 END ) AS QCED,
max( CASE WHEN b.qc_status = 'FALSE' THEN count(b.*) ELSE 0 END ) AS notQCED,
max( CASE WHEN b.qc_status IS NULL THEN count(b.*) ELSE 0 END ) AS notFlagged
from (select pc.cms_name, qc.qc_status, count(pyv.video_title) from raci_property_ownership_cms_name_id_fk as rpocn
join property_cms as pc on pc.id= rpocn.cms_id join property_youtube_videos as pyv on pyv.channel_id_fk_id = pc.id
full join qc_qclog as qc on qc.video_id_id = pyv.id where property_ownership_id IN
(select id from raci_property_ownership where user_id_fk_id = 1) group by qc.qc_status, pc.cms_name) as b group by b.cms_name'''
response_summary = self.psy._custom(QUERY_SUMMARY, "select",
"named_tuple")
response_channel_summary = self.psy._custom(QUERY_CHANNEL_SUMMARY_VIEW,
"select", 'named_tuple')
response_cms_summary = self.psy._custom(QUERY_CMS_SUMMARY_VIEW,
"select", "named_tuple")
return render(
request, "qc/home.html", {
'data': response_summary['data'],
'channel_view': response_channel_summary['data'],
'cms_view': response_cms_summary
})
class HighCPCKeywords(View):
def __init__(self):
self.psy = PostgreSQLConnector()
def get(self, request, *args, **kwargs):
return render(request, "kpt/high_cpc_keywords.html")
def post(self, request, *args, **kwargs):
draw = request.POST.get("draw", 1)
columns = request.POST.get("columns", "")
order = request.POST.get("order", "")
START = request.POST.get("start", 0)
LENGTH = request.POST.get("length", 10)
SEARCH = str(request.POST.get("search[value]", ""))
USERID = request.user.id
_fields2hide = []
Columns = []
CURRENT_URL = '/kpt/hcpc'
QUERY_COL = '''select sa.available_operations, sa.permission_str \
from system_apps_assignment saa join system_user_form_level_permission sa \
on saa.id=sa.app_assignment_id_fk_id where saa.user_id_fk_id = %d AND sa.form_url ='%s' ''' % (
USERID, CURRENT_URL)
response = self.psy._custom(QUERY_COL, "select", "named_tuple")
res_perm_fields = response['data'][0][1]
for item in res_perm_fields:
if item["perm"] != "hidden":
l = [item["perm"], item["ctype"], item["id"]]
p = {}
p[item["display_name"]] = l
p["title"] = item["display_name"]
Columns.append(p)
for item in res_perm_fields:
for key, val in item.items():
if val == "hidden":
_fields2hide.append(item['id'])
t_data = self._formatData(_fields2hide, SEARCH, USERID, START, LENGTH,
request)
data_dump = json.dumps(t_data)
data = json.loads(data_dump)
if not data['data']:
total_data = 0
else:
total_data = data['data'][0][-1]
for j in data["data"]:
j.pop()
new_response = {
"columns": Columns,
"data": data['data'],
"draw": draw,
"recordsTotal": data['count'],
"recordsFiltered": total_data
}
return JsonResponse(new_response, safe=False)
def filterOutHiddenFields(self, FIELDS_TO_HIDE, SELECT_FIELDS_STR):
temp = SELECT_FIELDS_STR.replace(" ", "").split(",")
for item in temp:
t = item.split(".")
if t[1] in FIELDS_TO_HIDE:
temp.remove(item)
return ','.join(temp)
def _formatData(self, FIELDS_TO_HIDE, SEARCH, USERID, START, LENGTH,
request):
SELECT_FIELDS_STR = '''keyword, volume, keyword_difficulty, cost_per_click, competitive_density, results, serp_features'''
filter_flag = request.POST.get('filter')
if filter_flag:
v = request.POST
else:
WHERE = '''keyword like '%%%s%%' ''' % (SEARCH)
QUERY = '''SELECT %s, count(*) over() as full_count from kpt_hcpc where %s LIMIT %d OFFSET %d''' % (
SELECT_FIELDS_STR, WHERE, int(LENGTH), int(START))
response = self.psy._custom(
QUERY.replace("\n", " ").replace("\r", ""), "select",
"named_tuple")
return response
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = ['plat_id', 'platform_name', 'gross_earning', 'month']
def get(self, request, *args, **kwargs): self.psy = PostgreSQLConnector( ) return render(request,"dam/lookups.html")
class DAMLookups( View ):
def download(self):
return HttpResponse("Hello, I am download!!!")
def nudge(self):
return HttpResponse("Hello, I am Nudgy!!!")
def place_request(self):
return HttpResponse("Hello, I am Place request!!!")
def get(self, request, *args, **kwargs):
self.psy = PostgreSQLConnector( )
return render(request,"dam/lookups.html")
def update( request ):
USERID = request.user.id
for key, value in request.POST.items():
pass
tCol = key.split("_")
id = tCol.pop( )
colName = "_".join( tCol )
string = colName + " = '%s'" % ( value )
query2Update = "update dam_cloudfile set "+ string +" where code ~'%s' " % ( id )
psy = PostgreSQLConnector( )
response = psy._custom( query2Update,"update","json" )
return JsonResponse( response, safe = False )
def post( self, request, *args, **kwargs ):
self.psy = PostgreSQLConnector( )
CURRENT_URL = '/dam/lookups'
draw = request.POST.get("draw", 1 )
columns = request.POST.get("columns","")
order = request.POST.get("order","")
START = request.POST.get("start",0)
LENGTH = request.POST.get("length",10)
SEARCH = str(request.POST.get("search[value]",""))
USERID = request.user.id
Columns = []
DSTR = ""
QUERY_COL = '''select sa.available_operations, sa.permission_str \
from system_apps_assignment saa join system_user_form_level_permission sa \
on saa.id=sa.app_assignment_id_fk_id where saa.user_id_fk_id = %d and sa.form_url='%s' ''' % ( USERID, CURRENT_URL )
response = self.psy._custom( QUERY_COL, "select","named_tuple" )
res_perm_fields = response['data'][0][1]
for item in res_perm_fields:
l = [ item["perm"], item["ctype"], item["id"] ]
p = {}
p[item["display_name"]] = l
p["title"] = item["display_name"]
DSTR = DSTR + item["table_alias"]+"."+item["id"] + ","
Columns.append(p)
t_data = self._formatData(DSTR.rstrip(","), SEARCH, USERID, START, LENGTH, request )
data_dump = json.dumps(t_data)
data = json.loads(data_dump)
if not data['data']:
total_data = 0
else:
total_data = data['data'][0][-1]
for j in data["data"]:
j.pop()
new_response = {"columns":Columns, "data":data['data'], "draw":draw, "recordsTotal":data['count'], "recordsFiltered":total_data}
return JsonResponse( new_response, safe = False)
def _formatData( self, SELECT_FIELDS, SEARCH, USERID, START, LENGTH, request ):
filter_flag = request.POST.get('filter')
if filter_flag:
pass
else:
WHERE = '''dam_cloudfile.nas_path like '%%%s%%' ''' % ( SEARCH )
QUERY = '''SELECT %s, count(*) over() as full_count from dam_cloudfile where %s LIMIT %d OFFSET %d''' % (SELECT_FIELDS, WHERE, int(LENGTH), int( START))
response = self.psy._custom( QUERY.replace("\n"," ").replace("\r",""), "select", "named_tuple" )
return response
def __init__(self, partner_id): self.psy = PostgreSQLConnector() self.partner_id = int(partner_id)
def getUserPayload(self):
psy = PostgreSQLConnector()
query = "select sa.name, sa.status, sa.about, sa._id, saa.profile_name, saa.description from system_app sa \
inner join system_apps_assignment saa on saa.app_id_fk_id=sa._id where saa.user_id_fk_id=2;"
return psy._custom(query, "select", "named_tuple")
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = ['contract_code', 'total_revenue', 'month']
self.Contract_Status = ['', 'Active', 'Inactive', 'Draft']
class CFMSHome:
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = ['contract_code', 'total_revenue', 'month']
self.Contract_Status = ['', 'Active', 'Inactive', 'Draft']
def get_date_string(self, date_str, option):
dt_obj = datetime.strptime(date_str, '%Y-%m-%d')
if option == "first_day_of_month":
return str(dt_obj.year) + "-" + str(dt_obj.month) + "-01"
elif option == "last_day_of_month":
next_month = dt_obj.replace(day=28) + timedelta(days=4)
return str(next_month - timedelta(days=next_month.day))
def get_home_stats(self, request):
DRAW = request.POST.get("draw", 1)
ORDER = self.Columns[int(request.POST.get("order[0][column]", 1))]
ORDER_DIR = request.POST.get("order[0][dir]", "desc")
START = request.POST.get("start", 0)
LENGTH = request.POST.get("length", 10)
SEARCH = str(request.POST.get("search[value]", ""))
USERID = request.user.id
VIEW_TYPE = request.POST.get("view_type", "lifetime")
CUSTOM_FILTERS = request.POST.get("custom_filter", False)
START_DATE = self.get_date_string(
request.POST.get('start_date', '1991-08-07'), "first_day_of_month")
END_DATE = self.get_date_string(
request.POST.get('end_date',
datetime.today().strftime("%Y-%m-%d")),
"last_day_of_month")
C_STATUS = 0
CONTRACT_STATUS_STR = ''' and cc.contract_status ~ '%s' ''' % (
self.Contract_Status[0])
if CUSTOM_FILTERS:
CONTRACT_STATUS_STR = ''' and cc.contract_status ~ '%s' ''' % (
self.Columns[C_STATUS])
filter_str = parse_qs(CUSTOM_FILTERS)
contract_status = filter_str[
'contract_status'] if 'contract_status' in filter_str.keys(
) else ['0']
CONTRACT_STATUS_STR = ''' and cc.contract_status ~ '%s' ''' % (
self.Contract_Status[int(contract_status[0])])
dt_range = filter_str['f_daterange']
s = dt_range[0].split(" - ")
START_DATE = s[0]
END_DATE = s[1]
if (VIEW_TYPE == "lifetime"):
SUB_QUERY = MONTH_GROUPING = ""
elif (VIEW_TYPE == "monthly"):
SUB_QUERY = " , TO_CHAR(ce.month, 'Mon YYYY') as month "
MONTH_GROUPING = " ,to_char(ce.month, 'Mon YYYY') "
QUERY = '''
select ce.contract_id as contract_id, cc.code as contract_code, sum(ce.revenue) as total_revenue %s
from cfms_earning as ce inner join cfms_contract as cc on ce.contract_id = cc.id where
ce.month > '%s' and ce.month < '%s' and cc.code LIKE '%%%s%%' %s GROUP BY contract_id, contract_code %s ORDER BY %s %s LIMIT %d OFFSET %d
''' % (str(SUB_QUERY), str(START_DATE), str(END_DATE),
str(SEARCH), CONTRACT_STATUS_STR, str(MONTH_GROUPING), str(ORDER),
str(ORDER_DIR), int(LENGTH), int(START))
result = self.psy._custom(QUERY, "select", "named_tuple")
if (len(result['data']) == 0):
return {
"data": [],
"draw": DRAW,
"recordsTotal": 0,
"recordsFiltered": 0
}
data = []
for item in result['data']:
T = {}
T['id'] = item.contract_id
T['contract_code'] = item.contract_code
T['total_revenue'] = item.total_revenue
if (VIEW_TYPE == "monthly"):
T['month'] = item.month
data.append(T)
return {
"data": data,
"draw": DRAW,
"recordsTotal": result['count'],
"recordsFiltered": result['count']
}
class Asset_Performance_Report:
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = [
'title_code', 'title_name', 'title_type', 'gross_earning', 'month'
]
def get_date_string(self, date_str, option):
dt_obj = datetime.strptime(date_str, '%Y-%m-%d')
if option == "first_day_of_month":
return str(dt_obj.year) + "-" + str(dt_obj.month) + "-01"
elif option == "last_day_of_month":
next_month = dt_obj.replace(day=28) + timedelta(days=4)
return str(next_month - timedelta(days=next_month.day))
def processMe(self, request):
DRAW = request.POST.get("draw", 1)
ORDER = self.Columns[int(request.POST.get("order[0][column]", 2))]
ORDER_DIR = request.POST.get("order[0][dir]", "desc")
START = request.POST.get("start", 0)
LENGTH = request.POST.get("length", 10)
SEARCH = str(request.POST.get("search[value]", ""))
USERID = request.user.id
VIEW_TYPE = request.POST.get("view_type", "lifetime")
CUSTOM_FILTERS = request.POST.get("custom_filter", False)
START_DATE = self.get_date_string(
request.POST.get('start_date', '1991-08-07'), "first_day_of_month")
END_DATE = self.get_date_string(
request.POST.get('end_date',
datetime.today().strftime("%Y-%m-%d")),
"last_day_of_month")
if CUSTOM_FILTERS:
filter_str = parse_qs(CUSTOM_FILTERS)
if 'f_daterange' in filter_str:
dt_range = filter_str['f_daterange']
s = dt_range[0].split(" - ")
START_DATE = s[0]
END_DATE = s[1]
if (VIEW_TYPE == "lifetime"):
SUB_QUERY = MONTH_GROUPING = ""
elif (VIEW_TYPE == "monthly"):
SUB_QUERY = " , to_char(ce.month, 'Mon YYYY') as month "
MONTH_GROUPING = " ,to_char(ce.month, 'Mon YYYY') "
QUERY = '''
select at.id as title_id, at.code as title_code, at.display_name as title_name,
gat.asset_type as title_type, sum(ce.revenue) as gross_earning %s
from cfms_earning as ce join asset_title as at on at.id = ce.title_id_id
join generic_asset_type as gat on gat.asset_id = at.type_id
where ce.month > '%s' and ce.month < '%s' and (at.display_name like '%%%s%%' or at.code like '%%%s%%')
group by title_name, title_id,title_type %s order by %s %s LIMIT %d OFFSET %d
''' % (str(SUB_QUERY), str(START_DATE), str(END_DATE), str(SEARCH),
str(SEARCH), str(MONTH_GROUPING), str(ORDER), str(ORDER_DIR),
int(LENGTH), int(START))
print(QUERY)
result = self.psy._custom(QUERY, "select", "named_tuple")
if (len(result['data']) == 0):
return {
"data": [],
"draw": DRAW,
"recordsTotal": 0,
"recordsFiltered": 0
}
data = []
for item in result['data']:
T = {}
T['id'] = item.title_id
T['title_code'] = item.title_code
T['title_name'] = item.title_name
T['title_type'] = item.title_type
T['gross_earning'] = item.gross_earning
if (VIEW_TYPE == "monthly"):
T['month'] = item.month
data.append(T)
return {
"data": data,
"draw": DRAW,
"recordsTotal": result['count'],
"recordsFiltered": result['count']
}
def __init__(self):
self.psy = PostgreSQLConnector()
class QCList(View):
def __init__(self):
self.psy = PostgreSQLConnector()
def filter(request):
flag = request.GET.get('filter')
str = ""
return JsonResponse(str, safe=False)
def update(request):
USERID = request.user.id
for key, value in request.POST.items():
pass
tCol = key.split("_")
video_id = tCol.pop()
colName = "_".join(tCol)
if colName == "moderator_remark":
string = colName + " = '%s', remark_from_id = %d" % (value, USERID)
else:
string = colName + " = '%s', qced_by_id = %d" % (value.upper(),
USERID)
query2Update = "update qc_qclog set " + string + " where video_id_id IN (select id from property_youtube_videos where video_id ~ '%s')" % (
video_id)
psy = PostgreSQLConnector()
response = psy._custom(query2Update, "update", "json")
return JsonResponse(response, safe=False)
def get(self, request, *args, **kwargs):
obj = PagePermissions("/qc/list", request.user.id)
response = obj.get_filter()
filters = obj.get_filter()
p_response = []
for item in filters:
temp = {}
if item == "channel":
QUERY = '''select a.channel_id, pc.channel_name from raci_property_ownership_channel_name_id_fk as a join property_channel as pc
on pc.id = a.channel_id where a.property_ownership_id IN (select id from raci_property_ownership where user_id_fk_id = 1)'''
r = self.psy._custom(QUERY, "select", "json")
temp[item] = r["data"]
p_response.append(temp)
elif item == "cms":
QUERY = '''select a.cms_id, pc.cms_name from raci_property_ownership_cms_name_id_fk as a join property_cms as pc on pc.id = a.cms_id
where a.property_ownership_id IN (select id from raci_property_ownership where user_id_fk_id = 1)'''
r = self.psy._custom(QUERY, "select", "json")
temp[item] = r['data']
p_response.append(temp)
elif item == "qc_by":
QUERY = '''select id, "first_name" || ' ' || "last_name" as user from auth_user'''
r = self.psy._custom(QUERY, "select", "json")
temp[item] = r['data']
p_response.append(temp)
elif item == "qc_status":
pass
elif item == "date":
pass
return render(request, "qc/list.html", {"data": p_response})
def post(self, request, *args, **kwargs):
draw = request.POST.get("draw", 1)
columns = request.POST.get("columns", "")
order = request.POST.get("order", "")
START = request.POST.get("start", 0)
LENGTH = request.POST.get("length", 10)
SEARCH = str(request.POST.get("search[value]", ""))
USERID = request.user.id
Columns = []
DSTR = ""
obj = PagePermissions("/qc/list", request.user.id)
res_perm_fields = obj.get_permission_str()
for item in res_perm_fields:
l = [item["perm"], item["ctype"], item["id"]]
p = {}
p[item["display_name"]] = l
p["title"] = item["display_name"]
DSTR = DSTR + item["table_alias"] + "." + item["id"] + ","
Columns.append(p)
t_data = self._formatData(DSTR.rstrip(","), SEARCH, USERID, START,
LENGTH, request)
data_dump = json.dumps(t_data)
data = json.loads(data_dump)
if not data['data']:
total_data = 0
else:
total_data = data['data'][0][-1]
for j in data["data"]:
j.pop()
new_response = {
"columns": Columns,
"data": data['data'],
"draw": draw,
"recordsTotal": data['count'],
"recordsFiltered": total_data
}
return JsonResponse(new_response, safe=False)
def tuple_format(self, value1, value2):
str = ''
if len(value1) == 1 and len(value2) == 1:
str += "(property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN ({0}) ) or property_youtube_videos.channel_id_fk_id IN ({1})) and ".format(
value1[0], value2[0])
elif len(value1) == 1:
str += "(property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN ({0}) ) or property_youtube_videos.channel_id_fk_id IN {1}) and ".format(
value1[0], value2)
else:
str += "(property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN {0} ) or property_youtube_videos.channel_id_fk_id IN ({1})) and ".format(
value1, value2[0])
return str
def _formatData(self, SELECT_FIELDS, SEARCH, USERID, START, LENGTH,
request):
filter_flag = request.POST.get('filter')
if filter_flag:
v = request.POST
str = ""
value1, value2 = tuple(filter(None, v.getlist('f_cms'))), tuple(
filter(None, v.getlist('f_channel')))
if ('f_cms' in v and v['f_cms']) and ('f_channel' in v
and v['f_channel']):
if len(value1) != 1 and len(value2) != 1:
str += "(property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN {0} ) or property_youtube_videos.channel_id_fk_id IN {1}) and ".format(
value1, value2)
else:
str += self.tuple_format(value1, value2)
elif ('f_channel' in v and v['f_channel']):
a = "property_youtube_videos.channel_id_fk_id IN {0} and ".format(
tuple(filter(None, v.getlist('f_channel'))))
str += "property_youtube_videos.channel_id_fk_id IN ({0}) and ".format(
tuple(filter(
None,
v.getlist('f_channel')))[0]) if len(value2) == 1 else a
elif ('f_cms' in v and v['f_cms']):
b = "property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN {0} ) and ".format(
tuple(filter(None, v.getlist('f_cms'))))
str += "property_youtube_videos.channel_id_fk_id IN ( select id from property_channel where cms_id_id IN ({0}) ) and ".format(
tuple(filter(
None,
v.getlist('f_cms')))[0]) if len(value1) == 1 else b
for key, value in v.items():
if (key == "f_qc_by"):
c = "qc_qclog.qced_by_id IN {0} and ".format(
tuple(filter(None, v.getlist('f_qc_by'))))
str += "qc_qclog.qced_by_id IN ({0}) and ".format(
tuple(filter(None, v.getlist('f_qc_by')))[0]) if len(
tuple(filter(None,
v.getlist('f_qc_by')))) == 1 else c
elif (key == "f_daterange"):
raw_daterange = v.get('f_daterange').split(" - ")
str += "qced_on between '{0}'::timestamp and '{1}'::timestamp and ".format(
raw_daterange[0], raw_daterange[1])
WHERE = "property_youtube_videos.video_title like '%%{0}%%' and property_cms.is_active = True and property_channel.is_active = True and {1} ".format(
SEARCH, str)
WHERE += "True"
else:
WHERE = '''property_youtube_videos.video_title like '%%%s%%' and property_cms.is_active = True and property_channel.is_active = True and qc_qclog.qc_status= False and property_youtube_videos.channel_id_fk_id IN (1,2) AND property_youtube_videos.channel_id_fk_id
IN (select raci_yt_ch.channel_id as channel_id from raci_property_ownership_channel_name_id_fk as raci_yt_ch
where raci_yt_ch.property_ownership_id = (select id from raci_property_ownership where user_id_fk_id = %d)
union select id as channel_idB from property_channel where cms_id_id IN
(select cms_id_id from raci_property_ownership_channel_name_id_fk where property_ownership_id =
(select id as raci_po from raci_property_ownership where user_id_fk_id = %d)))''' % (
SEARCH, USERID, USERID)
QUERY = '''SELECT %s, count(*) over() as full_count from property_youtube_videos join property_channel on property_youtube_videos.channel_id_fk_id = property_channel.id
join property_cms on property_channel.cms_id_id = property_cms.id join qc_qclog on qc_qclog.video_id_id = property_youtube_videos.id where %s LIMIT %d OFFSET %d''' % (
SELECT_FIELDS, WHERE, int(LENGTH), int(START))
response = self.psy._custom(
QUERY.replace("\n", " ").replace("\r", ""), "select",
"named_tuple")
return response
class Partner_Performance_Report:
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = [
'partner_code', 'partner_name', 'total_revenue', 'month',
'partner_status'
]
self.Partner_Status = ["True", "ep.is_active", "not ep.is_active"]
def get_date_string(self, date_str, option):
dt_obj = datetime.strptime(date_str, '%Y-%m-%d')
if option == "first_day_of_month":
return str(dt_obj.year) + "-" + str(dt_obj.month) + "-01"
elif option == "last_day_of_month":
next_month = dt_obj.replace(day=28) + timedelta(days=4)
return str(next_month - timedelta(days=next_month.day))
def processMe(self, request):
DRAW = request.POST.get("draw", 1)
ORDER = self.Columns[int(request.POST.get("order[0][column]", 2))]
ORDER_DIR = request.POST.get("order[0][dir]", "desc")
START = request.POST.get("start", 0)
LENGTH = request.POST.get("length", 10)
SEARCH = str(request.POST.get("search[value]", ""))
USERID = request.user.id
VIEW_TYPE = request.POST.get("view_type", "lifetime")
CUSTOM_FILTERS = request.POST.get("custom_filter", False)
START_DATE = self.get_date_string(
request.POST.get('start_date', '1991-08-07'), "first_day_of_month")
END_DATE = self.get_date_string(
request.POST.get('end_date',
datetime.today().strftime("%Y-%m-%d")),
"last_day_of_month")
PARTNER_STATUS_STR = self.Partner_Status[0]
if CUSTOM_FILTERS:
filter_str = parse_qs(CUSTOM_FILTERS)
if 'f_daterange' in filter_str:
dt_range = filter_str['f_daterange']
s = dt_range[0].split(" - ")
START_DATE = s[0]
END_DATE = s[1]
if 'partner_status' in filter_str:
partner_status = filter_str['partner_status']
PARTNER_STATUS_STR = self.Partner_Status[int(
partner_status[0])]
if (VIEW_TYPE == "lifetime"):
SUB_QUERY = MONTH_GROUPING = ""
elif (VIEW_TYPE == "monthly"):
SUB_QUERY = " , to_char(ce.month, 'Mon YYYY') as month "
MONTH_GROUPING = " ,to_char(ce.month, 'Mon YYYY') "
QUERY = '''
select ces.partner_id as partner_id, ep.code as partner_code, ep.partner_name as partner_name, sum(ces.calculated_revenue_usd) as total_revenue, ep.is_active as partner_status %s from cfms_earning as ce
join cfms_earningsplit as ces on ce.id=ces.earning_ref_id join extras_partner as ep on ep.id = ces.partner_id
join cfms_contract as cc on cc.id = ce.contract_id where ce.month > '%s' and ce.month < '%s' and ep.partner_name like '%%%s%%' and %s
group by partner_id, partner_name, partner_code, ep.is_active %s order by %s %s LIMIT %d OFFSET %d
''' % (str(SUB_QUERY), str(START_DATE), str(END_DATE), str(SEARCH),
str(PARTNER_STATUS_STR), str(MONTH_GROUPING), str(ORDER),
str(ORDER_DIR), int(LENGTH), int(START))
result = self.psy._custom(QUERY, "select", "named_tuple")
if (len(result['data']) == 0):
return {
"data": [],
"draw": DRAW,
"recordsTotal": 0,
"recordsFiltered": 0
}
data = []
for item in result['data']:
T = {}
T['id'] = item.partner_id
T['partner_code'] = item.partner_code
T['partner_name'] = item.partner_name
T['total_revenue'] = item.total_revenue
T['partner_status'] = item.partner_status
if (VIEW_TYPE == "monthly"):
T['month'] = item.month
data.append(T)
return {
"data": data,
"draw": DRAW,
"recordsTotal": result['count'],
"recordsFiltered": result['count']
}
def __init__(self):
self.psy = PostgreSQLConnector()
self.Columns = [
'title_code', 'title_name', 'title_type', 'gross_earning', 'month'
]
def __init__(self, contract_id): self.psy = PostgreSQLConnector() self.contract_id = int(contract_id)
class Contract_Performance_Report_DetailPage:
def __init__(self, contract_id):
self.psy = PostgreSQLConnector()
self.contract_id = int(contract_id)
def ContractOverallPerformanceReview(self):
QUERY = '''select sum(ce.revenue) as total_revenue, efe.currency_from_currency, efe.currency_to_currency, (efe.currency_to * sum(ce.revenue)) as gross_earning from cfms_earning as ce join cfms_contract as cc on cc.id = ce.contract_id
join extras_fexchange as efe on efe.id = ce.currency_id where ce.contract_id = %d group by ce.contract_id, efe.currency_to, efe.currency_from_currency, efe.currency_to_currency''' % (self.contract_id)
return self.psy._custom( QUERY, "select", "named_tuple")
def CheckIfContractExists(self):
QUERY = '''SELECT count(*) from cfms_contract where id = %d''' % ( self.contract_id )
r = self.psy._custom(QUERY, "select")
if len(r['data']) != 0 and r['data'][0][0] != 0:
return True
return False
def ContractRightsGroup( self ):
QUERY = '''SELECT cip.entity, cip.right_type, cip.exclusivity, cip.inclusivity from
cfms_iprightgroup_ip_right as ciprt join cfms_ipright as cip on cip.id = ciprt.ipright_id
where ciprt.iprightgroup_id = (select rights_group_id from cfms_contract where id = %d)''' % ( self.contract_id )
return self.psy._custom(QUERY, "select","named_tuple")
def get_date_string( self, date_str , option ):
dt_obj = datetime.strptime(date_str, '%Y-%m-%d')
if option=="first_day_of_month":
return str(dt_obj.year)+"-"+str(dt_obj.month)+"-01"
elif option == "last_day_of_month":
next_month = dt_obj.replace(day=28) + timedelta(days=4)
return str(next_month - timedelta(days=next_month.day))
def ContractSignatories( self ):
QUERY = '''SELECT concat( ep.partner_name,' ', (ep.code)) as signatory, ccs.rev_percentage,ep.id as partner_id,
concat(ec.first_name,' ',ec.middle_name,' ',ec.last_name) as contact, ec.id as contact_id from cfms_contract_signatories as ccs
join extras_partner as ep on ccs.partner_id = ep.id
join extras_contact as ec on ccs.contact_id = ec.id
where ccs.contract_id = %d ''' % ( self.contract_id )
return self.psy._custom(QUERY,"select","named_tuple")
def ContractInfo( self ):
QUERY = '''select cc.id, cc.code, cc.sign_date, cc.start_date, cc.end_date, cc.perpetual, cc.notes,
concat(gct.prefix,' ',gct.main_category,' ',gct.sub_category) as contract_nature, cc.contract_status
from cfms_contract as cc join generic_contract_type as gct on cc.nature_id = gct.id where cc.id = %d ''' % ( self.contract_id )
return self.psy._custom(QUERY,"select","named_tuple")
def MonthlyAggregateEarning( self ):
QUERY = '''select TO_CHAR(ce.month, 'Mon YYYY') as month, ce.contract_id as contract_id, sum(ce.revenue) as total_revenue,
concat(efe.currency_from,' ',efe.currency_from_currency,' = ', efe.currency_to,' ', efe.currency_to_currency) as currency_exchange,
efe.currency_from_currency, efe.currency_to_currency from cfms_earning as ce join cfms_contract as cc on cc.id = ce.contract_id
join extras_fexchange as efe on efe.id = ce.currency_id where ce.contract_id = %d group by TO_CHAR(ce.month, 'Mon YYYY'),
ce.contract_id, currency_exchange, efe.currency_from_currency, efe.currency_to_currency order by month asc''' % (self.contract_id)
return self.psy._custom(QUERY,"select","named_tuple")
def getTableHeader(self):
r_columns = [
{"title": "Title Code", "data": "title_code","orderable":False, "searchable":True},
{"title": "Title Name", "orderable":False, "data": "title_name", "searchable":True},
{"title": "Platform Name", "orderable":False, "data": "platform_name", "searchable":True},
];
QUERY = '''select ep.code from cfms_contract_signatories as ccs
join extras_partner as ep on ep.id = ccs.partner_id where ccs.contract_id = %d''' % (self.contract_id)
r = self.psy._custom(QUERY, "select","named_tuple")
for item in r["data"]:
t = {}
t["title"] = item.code
t["orderable"]=False
t["data"] = str(item.code)
t["searchable"] = False
r_columns.append(t)
r_columns.append({"title":"Total Gross Earning", "orderable":False,"data":"gross_earning","searchable":False})
return r_columns
def get_formated_data ( self, r ):
FINAL = []
if( len(r['data'])!=0 ):
for idx,item in enumerate(r['data']):
title_code = str(item.title_code)
Main = {}
Node = {}
if not any(d['title_code'] == title_code for d in FINAL):
SNode = {}
Node["title_code"] = item.title_code
Node["title_name"] = item.title_name
Node["platform_name"] = item.platform_name
Node['gross_earning'] = item.gross_earning
Node[str(item.partner_code)] = item.gross_earning
FINAL.append( Node )
else:
offset = len(FINAL) - 1
FINAL[offset]['gross_earning'] = FINAL[offset]['gross_earning'] + item.gross_earning
FINAL[offset][str(item.partner_code)] = item.gross_earning
return FINAL
def getGivenMonthRevBreakup ( self, request):
DRAW = request.POST.get("draw", 1 )
START = request.POST.get("start",0)
LENGTH = request.POST.get("length",10)
SEARCH = str(request.POST.get("search[value]",""))
USERID = request.user.id
month = request.POST.get("custom_filter[for_month]","Aug 1991")
currency_to = request.POST.get("custom_filter[converted_currency]",False)
if currency_to == "yes":
SUM = ', (efe.currency_to * sum(ces.calculated_revenue_usd)) as gross_earning '
GRP = ", efe.currency_to"
else:
SUM = ", sum(ces.calculated_revenue_usd) as gross_earning"
GRP = ""
QUERY = '''SELECT at.code as title_code, at.display_name as title_name, ep.code as partner_code, gp.platform_name %s from cfms_earningsplit as ces
join cfms_earning as ce on ce.id = ces.earning_ref_id join extras_partner as ep on ep.id = ces.partner_id
join generic_platform as gp on gp.plat_id = ce.platform_id_id join extras_fexchange as efe on efe.id = ce.currency_id
join asset_title as at on at.id = ce.title_id_id join cfms_contract as cc on cc.id = ce.contract_id
where (at.code like '%%%s%%' or at.display_name like '%%%s%%') and ce.contract_id = %d and TO_CHAR(ce.month, 'Mon YYYY') LIKE '%s'
group by at.code, at.display_name , ep.code, ce.month, gp.platform_name %s LIMIT %d OFFSET %d''' % ( str(SUM), str(SEARCH), str(SEARCH), self.contract_id, str(month), str(GRP), int(LENGTH), int(START) )
r = self.psy._custom( QUERY, "select","named_tuple" )
FINAL = self.get_formated_data( r )
return {"data":FINAL, "draw":DRAW, "recordsTotal":r['count'],"recordsFiltered":r['count']}