def ks_set_date(self, ks_dashboard_id):
if self._context.get('ksDateFilterSelection', False):
ks_date_filter_selection = self._context['ksDateFilterSelection']
if ks_date_filter_selection == 'l_custom':
self = self.with_context(
ksDateFilterStartDate=fields.datetime.strptime(
self._context['ksDateFilterStartDate'],
"%Y-%m-%dT%H:%M:%S.%fz"))
self = self.with_context(
ksDateFilterEndDate=fields.datetime.strptime(
self._context['ksDateFilterEndDate'],
"%Y-%m-%dT%H:%M:%S.%fz"))
else:
ks_date_filter_selection = self.browse(
ks_dashboard_id).ks_date_filter_selection
self = self.with_context(ksDateFilterStartDate=self.browse(
ks_dashboard_id).ks_dashboard_start_date)
self = self.with_context(ksDateFilterEndDate=self.browse(
ks_dashboard_id).ks_dashboard_end_date)
self = self.with_context(
ksDateFilterSelection=ks_date_filter_selection)
if ks_date_filter_selection not in ['l_custom', 'l_none']:
ks_date_data = ks_get_date(ks_date_filter_selection, self)
self = self.with_context(
ksDateFilterStartDate=ks_date_data["selected_start_date"])
self = self.with_context(
ksDateFilterEndDate=ks_date_data["selected_end_date"])
return self
def ks_get_list_data_orderby_extend(self, domain={}):
ks_filter_domain = domain.get('ks_domain_1', [])
orderid = self._context.get('field', False)
ks_field = self._context.get('field', False)
sort_order = self._context.get('sort_order', False)
offset = self._context.get('offset', 0)
initial_count = self._context.get('initial_count', 0)
ks_domain = []
if orderid:
orderby = self.env['ir.model.fields'].search([('id', '=', orderid)
]).id
else:
orderby = False
if self._context.get('ksDateFilterSelection', False):
ks_date_filter_selection = self._context['ksDateFilterSelection']
if ks_date_filter_selection == 'l_custom':
self = self.with_context(
ksDateFilterStartDate=fields.datetime.strptime(
self._context['ksDateFilterStartDate'],
"%Y-%m-%d %H:%M:%S"))
self = self.with_context(
ksDateFilterEndDate=fields.datetime.strptime(
self._context['ksDateFilterEndDate'],
"%Y-%m-%d %H:%M:%S"))
self = self.with_context(ksIsDefultCustomDateFilter=False)
else:
ks_date_filter_selection = self.ks_dashboard_ninja_board_id.ks_date_filter_selection
self = self.with_context(
ksDateFilterStartDate=self.ks_dashboard_ninja_board_id.
ks_dashboard_start_date)
self = self.with_context(
ksDateFilterEndDate=self.ks_dashboard_ninja_board_id.
ks_dashboard_end_date)
self = self.with_context(
ksDateFilterSelection=ks_date_filter_selection)
if ks_date_filter_selection not in ['l_custom', 'l_none']:
ks_date_data = ks_get_date(ks_date_filter_selection, self,
'datetime')
self = self.with_context(
ksDateFilterStartDate=ks_date_data["selected_start_date"])
self = self.with_context(
ksDateFilterEndDate=ks_date_data["selected_end_date"])
self = self.with_context(ksIsDefultCustomDateFilter=True)
ks_proper_domain = self.ks_convert_into_proper_domain(
self.ks_domain, self, ks_filter_domain)
if len(ks_proper_domain) > 0:
ks_domain = ks_domain + ks_proper_domain
list_view_data = self.get_list_view_record(orderby,
sort_order,
ks_domain,
ksoffset=initial_count - 1,
initial_count=initial_count)
return list_view_data
def ks_get_next_offset(self, ks_item_id, offset, item_domain=[]):
record = self.browse(ks_item_id)
ks_offset = offset['offset']
selected_start_date = False
selected_end_date = False
if self._context.get('ksDateFilterSelection', False):
ksDateFilterSelection = self._context.get('ksDateFilterSelection',
False)
if ksDateFilterSelection == 'l_custom':
ks_timezone = self._context.get('tz') or self.env.user.tz
selected_start_date = self._context['ksDateFilterStartDate']
selected_end_date = self._context['ksDateFilterEndDate']
selected_start_date = ks_convert_into_utc(
selected_start_date, ks_timezone)
selected_end_date = ks_convert_into_utc(
selected_end_date, ks_timezone)
if ksDateFilterSelection not in ['l_custom', 'l_none']:
ks_get_date_ranges = ks_get_date(ksDateFilterSelection, self,
'datetime')
selected_start_date = ks_get_date_ranges['selected_start_date']
selected_end_date = ks_get_date_ranges['selected_end_date']
if self.ks_data_calculation_type == 'custom':
return super(KsDashboardNinjaItemAdvance,
self).ks_get_next_offset(ks_item_id, offset,
item_domain)
else:
ks_query = str(self.ks_custom_query)
ks_start_date = record.ks_query_start_date
ks_end_date = record.ks_query_end_date
if selected_start_date or selected_end_date:
ks_start_date = selected_start_date
ks_end_date = selected_end_date
ks_query_result = self.ks_get_list_query_result(
ks_query, ks_start_date, ks_end_date, ks_offset=int(ks_offset))
ks_list_view_data = self.ks_format_query_result(ks_query_result)
return {
'ks_list_view_data':
json.dumps(ks_list_view_data),
'offset':
int(ks_offset) + 1,
'next_offset':
int(ks_offset) + len(ks_list_view_data['data_rows']),
'limit':
record.ks_record_data_limit if record.ks_record_data_limit else 0,
}
def ks_get_list_query_result(self,
ks_query,
selected_start_date,
selected_end_date,
ks_offset=0,
ks_export_all=False):
try:
type_code = []
conn = sql_db.db_connect(self.env.cr.dbname)
new_env = api.Environment(conn.cursor(), self.env.uid,
self.env.context)
limit = self.ks_pagination_limit
if ks_query and "{#MYCOMPANY}" in ks_query:
ks_query = ks_query.replace("{#MYCOMPANY}",
str(self.env.user.company_id.id))
if ks_query and "{#UID}" in ks_query:
ks_query = ks_query.replace("{#UID}", str(self.env.user.id))
if ks_export_all:
if self._context.get('ksDateFilterSelection', False):
ksDateFilterSelection = self._context.get(
'ksDateFilterSelection', False)
if ksDateFilterSelection == 'l_custom':
ks_timezone = self._context.get(
'tz') or self.env.user.tz
selected_start_date = self._context[
'ksDateFilterStartDate']
selected_end_date = self._context[
'ksDateFilterEndDate']
selected_start_date = ks_convert_into_utc(
selected_start_date, ks_timezone)
selected_end_date = ks_convert_into_utc(
selected_end_date, ks_timezone)
if ksDateFilterSelection not in ['l_custom', 'l_none']:
ks_get_date_ranges = ks_get_date(
ksDateFilterSelection, self, 'datetime')
selected_start_date = ks_get_date_ranges[
'selected_start_date']
selected_end_date = ks_get_date_ranges[
'selected_end_date']
if self.ks_is_date_ranges:
start_date = self.ks_query_start_date
end_date = self.ks_query_end_date
if selected_end_date or selected_start_date:
start_date = selected_start_date if selected_start_date else selected_end_date - relativedelta.relativedelta(
years=1000)
end_date = selected_end_date if selected_end_date else selected_start_date + relativedelta.relativedelta(
years=1000)
new_env.cr.execute(
"with ks_list_query as (" + ks_query + ")" +
"select * from ks_list_query limit "
"%(ks_limit)s offset %(ks_offset)s", {
'ks_start_date':
str(start_date -
relativedelta.relativedelta(years=10)),
'ks_end_date':
str(end_date + relativedelta.relativedelta(years=10)),
'ks_limit':
limit,
'ks_offset':
ks_offset
})
header_rec = [col.name for col in new_env.cr.description]
result = new_env.cr.dictfetchall()
if result:
for header_key in header_rec:
if type(result[0][header_key]).__name__ == 'float' or \
type(result[0][header_key]).__name__ == 'int':
type_code.append('numeric')
else:
type_code.append('string')
if ks_export_all:
new_env.cr.execute(
"with ks_list_query as (" + ks_query + ")" +
"select * from ks_list_query "
" offset %(ks_offset)s", {
'ks_start_date': str(start_date),
'ks_end_date': str(end_date),
'ks_offset': ks_offset
})
else:
new_env.cr.execute(
"with ks_list_query as (" + ks_query + ")" +
"select * from ks_list_query limit "
"%(ks_limit)s offset %(ks_offset)s", {
'ks_start_date': str(start_date),
'ks_end_date': str(end_date),
'ks_limit': limit,
'ks_offset': ks_offset
})
header = [col.name for col in new_env.cr.description]
else:
if ks_export_all:
new_env.cr.execute(
"with ks_list_query as (" + ks_query + ")" +
"select * from ks_list_query "
" offset %(ks_offset)s", {'ks_offset': ks_offset})
else:
new_env.cr.execute(
"with ks_list_query as (" + ks_query + ")" +
"select * from ks_list_query limit "
"%(ks_limit)s offset %(ks_offset)s", {
'ks_limit': limit,
'ks_offset': ks_offset
})
header = [col.name for col in new_env.cr.description]
records = new_env.cr.dictfetchall()
if records:
type_code.clear()
for header_key in header:
if type(records[0][header_key]).__name__ == 'float' or \
type(records[0][header_key]).__name__ == 'int':
type_code.append('numeric')
else:
type_code.append('string')
except ProgrammingError as e:
if e.args[0] == 'no results to fetch':
raise ValidationError(
_("You can only read the Data from Database"))
else:
raise ValidationError(_(e))
except Exception as e:
if type(e).__name__ == 'KeyError':
raise ValidationError(
_('Wrong date variables, Please use ks_start_date and ks_end_date in custom query'
))
raise ValidationError(_(e))
finally:
conn.cursor().close()
new_env.cr.closed
for res in records:
for key in res:
if type(res[key]).__name__ == 'datetime':
res[key] = res[key].strftime(
DEFAULT_SERVER_DATETIME_FORMAT)
elif type(res[key]).__name__ == 'date':
res[key] = res[key].strftime(DEFAULT_SERVER_DATE_FORMAT)
return json.dumps({
'header': header,
'records': records,
'type_code': type_code,
'ks_is_group_column': False
})
def ks_run_query(self):
selected_start_date = False
selected_end_date = False
if self._context.get('ksDateFilterSelection', False):
ksDateFilterSelection = self._context.get('ksDateFilterSelection',
False)
if ksDateFilterSelection == 'l_custom':
selected_start_date = self._context['ksDateFilterStartDate']
selected_end_date = self._context['ksDateFilterEndDate']
if ksDateFilterSelection not in ['l_custom', 'l_none']:
ks_get_date_ranges = ks_get_date(ksDateFilterSelection, self)
selected_start_date = ks_get_date_ranges['selected_start_date']
selected_end_date = ks_get_date_ranges['selected_end_date']
for rec in self:
if rec.ks_dashboard_item_type == 'ks_bar_chart' or rec.ks_dashboard_item_type == 'ks_horizontalBar_chart':
ks_is_group_column = True
else:
ks_is_group_column = False
with api.Environment.manage():
if rec.ks_data_calculation_type == 'query' and rec.ks_dashboard_item_type not in ['ks_tile','ks_kpi']\
and rec.ks_custom_query:
ks_query = rec.ks_custom_query
try:
type_code = []
conn = sql_db.db_connect(self.env.cr.dbname)
new_env = api.Environment(conn.cursor(), self.env.uid,
self.env.context)
if rec.ks_is_date_ranges:
start_date = rec.ks_query_start_date
end_date = rec.ks_query_end_date
if selected_end_date or selected_start_date:
start_date = selected_start_date if selected_start_date else selected_end_date - relativedelta.relativedelta(
years=1000)
end_date = selected_end_date if selected_end_date else selected_start_date + relativedelta.relativedelta(
years=1000)
new_env.cr.execute(
ks_query, {
'ks_start_date':
str(start_date -
relativedelta.relativedelta(years=10)),
'ks_end_date':
str(end_date +
relativedelta.relativedelta(years=10))
})
header_rec = [
col.name for col in new_env.cr.description
]
result = new_env.cr.dictfetchall()
if result:
for header_key in header_rec:
if type(result[0][header_key]).__name__ == 'float' or \
type(result[0][header_key]).__name__ == 'int':
type_code.append('numeric')
else:
type_code.append('string')
new_env.cr.execute(
ks_query, {
'ks_start_date': str(start_date),
'ks_end_date': str(end_date)
})
header = [
col.name for col in new_env.cr.description
]
else:
new_env.cr.execute(ks_query)
header = [
col.name for col in new_env.cr.description
]
records = new_env.cr.dictfetchall()
if records:
type_code.clear()
for header_key in header:
if type(records[0][header_key]).__name__ == 'float' or \
type(records[0][header_key]).__name__ == 'int':
type_code.append('numeric')
else:
type_code.append('string')
except ProgrammingError as e:
if e.args[0] == 'no results to fetch':
raise ValidationError(
_("You can only read the Data from Database"))
else:
raise ValidationError(_(e))
except Exception as e:
if type(e).__name__ == 'KeyError':
raise ValidationError(
_('Wrong date variables, Please use ks_start_date and ks_end_date in custom query'
))
raise ValidationError(_(e))
finally:
new_env.cr.close()
for res in records:
for key in res:
if type(res[key]).__name__ == 'datetime':
res[key] = res[key].strftime(
DEFAULT_SERVER_DATETIME_FORMAT)
elif type(res[key]).__name__ == 'date':
res[key] = res[key].strftime(
DEFAULT_SERVER_DATE_FORMAT)
rec.ks_query_result = json.dumps({
'header':
header,
'records':
records,
'type_code':
type_code,
'ks_is_group_column':
ks_is_group_column
})
else:
rec.ks_query_result = False