def __init__(self): self.tables = {} self.tables["Entry"] = Table("Entry") self.tables["File"] = Table("File") self.tables["CodeFragment"] = Table("CodeFragment") self.tables["SearchResult"] = Table("SearchResult") self.func = {} self.func["metaphone"] = CustomFunction('metaphone', ['text', 'len']) self.func["levenshtein"] = CustomFunction('levenshtein', ['source', 'target']) self.func["levenshtein_less_equal"] = CustomFunction( 'levenshtein_less_equal', ['source', 'target', 'max_d'])
def get_loan_amount(filters): total_amount = 0 for doctype in ["Loan Disbursement", "Loan Repayment"]: loan_doc = frappe.qb.DocType(doctype) ifnull = CustomFunction("IFNULL", ["value", "default"]) if doctype == "Loan Disbursement": amount_field = Sum(loan_doc.disbursed_amount) posting_date = (loan_doc.disbursement_date).as_("posting_date") account = loan_doc.disbursement_account salary_condition = loan_doc.docstatus == 1 else: amount_field = Sum(loan_doc.amount_paid) posting_date = (loan_doc.posting_date).as_("posting_date") account = loan_doc.payment_account salary_condition = loan_doc.repay_from_salary == 0 amount = (frappe.qb.from_(loan_doc).select(amount_field).where( loan_doc.docstatus == 1).where(salary_condition).where( account == filters.get("account")).where( posting_date > getdate(filters.get("report_date"))).where( ifnull(loan_doc.clearance_date, "4000-01-01") <= getdate(filters.get("report_date"))).run()[0][0]) total_amount += flt(amount) return total_amount
def get_serial_nos_data_after_transactions(args): from pypika import CustomFunction serial_nos = set() args = frappe._dict(args) sle = frappe.qb.DocType("Stock Ledger Entry") Timestamp = CustomFunction("timestamp", ["date", "time"]) stock_ledger_entries = (frappe.qb.from_(sle).select( "serial_no", "actual_qty").where((sle.item_code == args.item_code) & (sle.warehouse == args.warehouse) & (Timestamp(sle.posting_date, sle.posting_time) < Timestamp(args.posting_date, args.posting_time)) & (sle.is_cancelled == 0)).orderby( sle.posting_date, sle.posting_time, sle.creation).run(as_dict=1)) for stock_ledger_entry in stock_ledger_entries: changed_serial_no = get_serial_nos_data(stock_ledger_entry.serial_no) if stock_ledger_entry.actual_qty > 0: serial_nos.update(changed_serial_no) else: serial_nos.difference_update(changed_serial_no) return "\n".join(serial_nos)
def process_date_diff_args( self, args: str) -> Tuple[CustomFunction, CustomFunction]: arg_list = args.split(",") if len(arg_list) != 2: raise ValueError("Date_diff have invalid args", arg_list) result = [] for arg in arg_list: if arg == "now": date = current_date() else: date_fnc = CustomFunction("date", ["col1"]) if "." in arg: items = arg.split(".") topic_name = items[0].strip() topic = get_topic_by_name(topic_name, None) table = None if self.topic_space_filter: if self.topic_space_filter(self.param.topicId): alias_ = self.topic_space_filter( self.param.topicId)["alias"] table = AliasedQuery(alias_) if table is None: table = build_table_by_topic_id(topic.topicId) factor_name = items[1].strip() date = date_fnc(Field(factor_name, None, table)) else: date = date_fnc(arg) result.append(date) return tuple(result)
def get_loan_entries(filters): loan_docs = [] for doctype in ["Loan Disbursement", "Loan Repayment"]: loan_doc = frappe.qb.DocType(doctype) ifnull = CustomFunction("IFNULL", ["value", "default"]) if doctype == "Loan Disbursement": amount_field = (loan_doc.disbursed_amount).as_("credit") posting_date = (loan_doc.disbursement_date).as_("posting_date") account = loan_doc.disbursement_account salary_condition = loan_doc.docstatus == 1 else: amount_field = (loan_doc.amount_paid).as_("debit") posting_date = (loan_doc.posting_date).as_("posting_date") account = loan_doc.payment_account salary_condition = loan_doc.repay_from_salary == 0 query = (frappe.qb.from_(loan_doc).select( ConstantColumn(doctype).as_("payment_document"), (loan_doc.name).as_("payment_entry"), (loan_doc.reference_number).as_("reference_no"), (loan_doc.reference_date).as_("ref_date"), amount_field, posting_date, ).where(loan_doc.docstatus == 1).where(salary_condition).where( account == filters.get("account")).where( posting_date <= getdate(filters.get("report_date"))).where( ifnull(loan_doc.clearance_date, "4000-01-01") > getdate( filters.get("report_date")))) entries = query.run(as_dict=1) loan_docs.extend(entries) return loan_docs
async def select_all(city): """Fetch all data at once Fetch data from DB args: city: selected city returns: Dictionary that contains the requested data, which is converted by fastAPI to a json object. """ data = Table("data") di_fn = CustomFunction("ROUND", ["number"]) columns = ( # 'lat', 'lon' data["lat"].as_("latitude"), data["lon"].as_("longitude"), data["Crime Rating"].as_("crime"), data["Rent"].as_("rental_price"), data["Air Quality Index"].as_("air_quality_index"), data["Population"].as_("population"), data["Nearest"].as_("nearest_string"), data["Good Days"].as_("good_days"), data["Crime Rate per 1000"].as_("crime_rate_ppt"), di_fn(data["Diversity Index"] * 100).as_("diversity_index"), ) q = (Query.from_(data).select(*columns).where( data.City == city.city).where(data.State == city.state)) value = await database.fetch_one(str(q)) return value
def test_should_return_function_with_arguments(self): DateDiff = CustomFunction("DATE_DIFF", ["interval", "start_date", "end_date"]) self.assertEqual( "DATE_DIFF('day','start_date','end_date')", str(DateDiff("day", "start_date", "end_date")), )
def test_should_fail_use_custom_function_on_select_with_wrong_arguments( self): service = Table("service") DateDiff = CustomFunction("DATE_DIFF", ["interval", "start_date", "end_date"]) with self.assertRaises(FunctionException): Query.from_(service).select(DateDiff("day", service.created_date))
def _date_diff(unit, args_str): args_list = args_str.split(",") if len(args_list) != 2: raise ValueError("Date_diff have invalid args", args_list) date_diff = CustomFunction("DATE_DIFF", ["col1", "col2", "col3"]) arg1 = args_list[0].strip() arg2 = args_list[1].strip() date1 = _process_date_diff_arg(arg1) date2 = _process_date_diff_arg(arg2) return date_diff(unit, date1, date2)
def test_should_use_custom_function_on_select(self): service = Table("service") DateDiff = CustomFunction("DATE_DIFF", ["interval", "start_date", "end_date"]) q = Query.from_(service).select( DateDiff("day", service.created_date, service.updated_date)) self.assertEqual( 'SELECT DATE_DIFF(\'day\',"created_date","updated_date") FROM "service"', str(q), )
def _process_date_diff_arg(arg): date_fnc = CustomFunction("date", ["col1"]) if arg == "now": date = PseudoColumn('current_date') else: if "." in arg: arg_list = arg.split(".") topic_name = arg_list[0].strip() factor_name = arg_list[1].strip() date = Table("topic_" + topic_name).as_(topic_name)[factor_name] else: date = date_fnc(arg) return date
async def select_all(city): data = Table("data") di_fn = CustomFunction("ROUND", ["number"]) columns = ( # 'lat', 'lon' data["lat"].as_("latitude"), data["lon"].as_("longitude"), data["Crime Rating"].as_("crime"), data["Rent"].as_("rental_price"), data["Air Quality Index"].as_("air_quality_index"), data["Population"].as_("population"), data["Nearest"].as_("nearest_string"), data["Good Days"].as_("good_days"), data["Crime Rate per 1000"].as_("crime_rate_ppt"), di_fn(data["Diversity Index"] * 100).as_("diversity_index"), ) q = (Query.from_(data).select(*columns).where( data.City == city.city).where(data.State == city.state)) value = await database.fetch_one(str(q)) return value
def test_should_return_function_with_no_arguments(self): CurrentDate = CustomFunction("CURRENT_DATE") self.assertEqual("CURRENT_DATE()", str(CurrentDate()))
from datetime import datetime from pypika import CustomFunction, MSSQLQuery, Parameter, Table from pypika.functions import Cast, DateDiff from pypika.terms import Function, PseudoColumn from .base import Database _MSSQLDateAdd = CustomFunction('DATEADD', ['date_part', 'number', 'term']) _MSSQLConvert = CustomFunction('CONVERT', ['term', 'expression', 'style']) class MSSQLDatabase(Database): """ Microsoft SQL Server client that uses the pymssql module under the hood. """ # The pypika query class to use for constructing queries query_cls = MSSQLQuery def __init__(self, host='localhost', port=1433, database=None, user=None, password=None, **kwargs): super().__init__(host, port, database, **kwargs) self.user = user self.password = password
def query_generator(self): # special handle here to prevent date messed up during query process~ date_format = CustomFunction('DATE_FORMAT', ['date_field', 'format']) format = '%m/%d/%Y %H:%i:%s' # process join tables - [BODY] tables = [] table_obj_map = {} join_condition_queue = {} base_table_name = "" for table in self.parser.body_all_columns: base_table_name = table if len( base_table_name) == 0 else base_table_name table_obj = Table(table) tables.append(table_obj) table_obj_map[table] = table_obj join_condition_queue[table] = [] from_table = tables[0] join = table_obj_map.copy() join.pop(base_table_name) # print(join) # print(self.all_source_type) # process join conditions - [BODY] for var in self.parser.body_variable_map: # only join when more then 1 table name exist! if len(self.parser.body_variable_map[var]) > 2: previous_table = "" for table_name in self.parser.body_variable_map[var][1:]: if len(previous_table) == 0: previous_table = table_name else: pre = previous_table.split('.') curr = table_name.split('.') exp1 = getattr(table_obj_map[pre[0]], pre[1]) exp2 = getattr(table_obj_map[curr[0]], curr[1]) if self.is_a_time_field(curr[1]): # print("Target Column Type: ", self.all_source_type[curr[1]]) exp1 = date_format(exp1, format) exp2 = date_format(exp2, format) join_condition_queue[curr[0]].append([exp1, exp2]) # process where filters - [BODY] where = [] for table_name in self.parser.body_condition_columns: for condition in self.parser.body_condition_columns[table_name]: # it is not a variable, then it is a filter! Apply it as where statement! if condition[2] not in self.parser.body_variable_map: # TODO: make it handle complicated filter! if condition[2].lower() in ["null", "notnull"]: op = "isnull" if condition[2].lower( ) == "null" else "notnull" column_body = getattr(table_obj_map[table_name], condition[0]) where.append(getattr(column_body, op)()) elif condition[2].lower() in ["true", "false"]: op = True if condition[2].lower() == "true" else False where.append( getattr(table_obj_map[table_name], condition[0]) == op) elif self.is_a_time_field(condition[0]): # Special handle for date field~ where.append( date_format( getattr(table_obj_map[table_name], condition[0]), format) == date_format( condition[2].strip('"'), format)) else: where.append( getattr(table_obj_map[table_name], condition[0]) == condition[2].strip('"')) q = MySQLQuery.from_(from_table) # join must be followed by ON~ multiple joins should be separated as well! for join_table_name in join: # print(join_table_name, join[join_table_name]) # q = q.join(join[join_table_name]) q = q.left_join(join[join_table_name]) j_c = None for j_condition in join_condition_queue[join_table_name]: if j_c is None: j_c = (j_condition[0] == j_condition[1]) else: j_c = j_c & (j_condition[0] == j_condition[1]) # ONLY perform join condition if it is not empty! if j_c is not None: q = q.on(j_c) for w_statement in where: q = q.where(w_statement) # final_query = [] query_objects = {} # be aware! the header might contain more than one event table! for event in self.parser.header_simple_columns: select_object = [] select_col_names = [] # process select - [HEADER] for attribute in self.parser.header_simple_columns[event]: select_object.append( getattr( table_obj_map[ self.parser.reverse_column_map[attribute]], attribute)) select_col_names.append(attribute) # process additional select - [HEADER] for condition in self.parser.header_condition_columns[event]: # TODO: make it handle complicated condition! # Handle Column Renaming Here if condition[2] in self.parser.body_variable_map and len( condition ) < 5: # Ahhhh this is very risky to do this! Nested table statment by min have 5 elements new_name = condition[0] origin_column = self.parser.body_variable_map[ condition[2]][1].split('.') new_col = getattr(table_obj_map[origin_column[0]], origin_column[1]) select_object.append(new_col.as_(new_name)) self.rename_map[condition[2]] = new_name select_col_names.append(new_name) self.query_selected_cols[event] = select_col_names.copy() # process nested table attributes - [HEADER] for event_name_dot_nested_table_name in self.nested_table_elements: if event + "." in event_name_dot_nested_table_name: # in case of multi-sugar~ nest_col_name = [] # print(event_name_dot_nested_table_name) for attribute in self.nested_table_elements[ event_name_dot_nested_table_name]: if isinstance(attribute, str): select_object.append( getattr( table_obj_map[ self.parser. reverse_column_map[attribute]], attribute)) nest_col_name.append(attribute) elif attribute[2] in self.parser.body_variable_map: new_name = attribute[0] origin_column = self.parser.body_variable_map[ attribute[2]][1].split('.') new_col = getattr(table_obj_map[origin_column[0]], origin_column[1]) select_object.append(new_col.as_(new_name)) self.rename_map[attribute[2]] = new_name nest_col_name.append(new_name) self.query_selected_cols[ event_name_dot_nested_table_name] = nest_col_name.copy() mysql_query_obj = q.select(*select_object) query_objects[event] = mysql_query_obj # print('self.nested_table_elements', self.nested_table_elements) # print('self.query_selected_cols', self.query_selected_cols) return query_objects
from pypika import CustomFunction # Presto datetime functions from_iso8601_timestamp = CustomFunction('from_iso8601_timestamp', ['timestamp']) from_iso8601_date = CustomFunction('from_iso8601_date', ['date']) from_unixtime = CustomFunction('from_unixtime', ['time']) to_iso8601 = CustomFunction('to_iso8601', ['date']) to_unixtime = CustomFunction('to_unixtime', ['timestamp']) # TODO: add presto functions # https://prestodb.io/docs/0.172/functions.html
from pypika import CustomFunction _add_subtract_args = ["name", "interval"] ToYYYYMM = CustomFunction("toYYYYMM") AddYears = CustomFunction("addYears", _add_subtract_args) AddMonths = CustomFunction("addMonths", _add_subtract_args) AddWeeks = CustomFunction("addWeeks", _add_subtract_args) AddDays = CustomFunction("addDays", _add_subtract_args) AddHours = CustomFunction("addHours", _add_subtract_args) AddMinutes = CustomFunction("addMinutes", _add_subtract_args) AddSeconds = CustomFunction("addSeconds", _add_subtract_args) AddQuarters = CustomFunction("addQuarters", _add_subtract_args) SubtractYears = CustomFunction("subtractYears", _add_subtract_args) SubtractMonths = CustomFunction("subtractMonths", _add_subtract_args) SubtractWeeks = CustomFunction("subtractWeeks", _add_subtract_args) SubtractDays = CustomFunction("subtractDays", _add_subtract_args) SubtractHours = CustomFunction("subtractHours", _add_subtract_args) SubtractMinutes = CustomFunction("subtractMinutes", _add_subtract_args) SubtractSeconds = CustomFunction("subtractSeconds", _add_subtract_args) SubtractQuarters = CustomFunction("subtractQuarters", _add_subtract_args) FormatDateTime = CustomFunction("formatDateTime", ["name", "dt_format"])
def date_diff(unit: Unit, arg1: CustomFunction, arg2: CustomFunction) -> CustomFunction: func_date_diff = CustomFunction("DATE_DIFF", ["col1", "col2", "col3"]) return func_date_diff(unit, arg1, arg2)
def test_should_fail_with_wrong_arguments(self): DateDiff = CustomFunction("DATE_DIFF", ["interval", "start_date", "end_date"]) with self.assertRaises(FunctionException): DateDiff("foo")
from pypika import CustomFunction is_null = CustomFunction("IS_NULL", ["v_value"]) gb_x = CustomFunction("GB_X", ["v_geom"]) gb_y = CustomFunction("GB_Y", ["v_geom"]) st_x = CustomFunction("ST_X", ["v_geom"]) st_y = CustomFunction("ST_Y", ["v_geom"]) st_round_x = CustomFunction("ST_ROUND_X", ["v_geom"]) st_round_y = CustomFunction("ST_ROUND_Y", ["v_geom"]) st_transform_4326 = CustomFunction("ST_TRANSFORM_4326", ["v_geom"]) to_bit = CustomFunction("TO_BIT", ["v_value"]) to_num = CustomFunction("TO_NUM", ["v_value"]) to_year = CustomFunction("TO_YEAR", ["v_value"]) from_float_to_int = CustomFunction("FROM_FLOAT_TO_INT", ["v_value"]) SQL_FUNCTION_MAPPING = { "IS_NULL": is_null, "GB_X": gb_x, "GB_Y": gb_y, "ST_X": st_x, "ST_Y": st_y, "ST_ROUND_X": st_round_x, "ST_ROUND_Y": st_round_y, "ST_TRANSFORM_4326": st_transform_4326, "TO_BIT": to_bit, "TO_NUM": to_num, "TO_YEAR": to_year, "FROM_FLOAT_TO_INT": from_float_to_int }