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