def GetFilesByMetadataViaHTSQL(htquery):
#return htquery
rows = htdb.produce('/%s' % (htquery))
from htsql.core.fmt.emit import emit
with htdb:
text = ''.join(emit('x-htsql/json', rows))
return text
return jsonify(results=rows)
return htquery
def get_nested():
test = HTSQL("mysql://*****:*****@localhost/nestedsetspoc")
#rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion)
rows = test.produce("/clinical_data{id, patient_sid :as sid,string_value :as value,attribute{attribute_value}}")
# rows = test.produce("/attribute{attribute_value, clinical_data{patient_sid :as sid,string_value :as value}}")
with test:
text = ''.join(emit('x-htsql/json', rows))
#print text, rows
return text
def get_factor():
#test = HTSQL("mysql://*****:*****@localhost/test")
test = HTSQL("pgsql://*****:*****@localhost/test")
rows = test.produce("/factor")
with test:
text = ''.join(emit('x-htsql/json', rows))
if mode == 'test':
print text, rows
return text
def action_htsql_query(self, query):
"""Executes a HTSQL Query"""
try:
from htsql.core.fmt.emit import emit
from htsql.core.error import Error as HTSQL_Error
from htsql import HTSQL
except ImportError:
return False, "HTSQL installation not found"
# Resolve RDBMSs to their respective HTSQL engines
engines = {
'postgres': 'pgsql',
'sqlite': 'sqlite',
'mysql': 'mysql',
'oracle': 'oracle',
'mssql': 'mssql',
}
if db_settings.password:
password = "******" + urllib.quote_plus(db_settings.password)
else:
password = ""
authority = '%s%s@%s:%s' % (
db_settings.username, password, db_settings.address,
db_settings.port)
uri = '%s://%s/%s' % (
engines[db_settings.rdbms], authority, db_settings.dbname)
exts = [{
'tweak.override': {
'unique_keys': 'product(sellable_id)',
'globals': {
'between($date, $start, $end)': '($date >= $start & $date <= $end)',
'trunc_hour($d)': 'datetime(year($d), month($d), day($d), hour($d))',
'trunc_day($d)': 'datetime(year($d), month($d), day($d))',
'trunc_month($d)': 'datetime(year($d), month($d), 01)',
}},
}]
store = HTSQL(uri, *exts)
try:
rows = store.produce(query)
except HTSQL_Error as e:
return False, str(e)
with store:
json = ''.join(emit('x-htsql/json', rows))
return True, json
def action_htsql_query(self, query):
"""Executes a HTSQL Query"""
try:
from htsql.core.fmt.emit import emit
from htsql.core.error import Error as HTSQL_Error
from htsql import HTSQL
except ImportError:
return False, "HTSQL installation not found"
# Resolve RDBMSs to their respective HTSQL engines
engines = {"postgres": "pgsql", "sqlite": "sqlite", "mysql": "mysql", "oracle": "oracle", "mssql": "mssql"}
if db_settings.password:
password = "******" + urllib.quote_plus(db_settings.password)
else:
password = ""
authority = "%s%s@%s:%s" % (db_settings.username, password, db_settings.address, db_settings.port)
uri = "%s://%s/%s" % (engines[db_settings.rdbms], authority, db_settings.dbname)
# FIXME We should find a way to share this piece of code between Stoq
# Portal and Stoq Server, without having to duplicate it every time
exts = [
{
"tweak.override": {
"field-labels": {
# Sellable fixes
"sellable.price": (
"(if (on_sale_start_date <= now() &"
"now() <= on_sale_end_date, on_sale_price,"
"base_price))"
),
# Sale fixes
# When the sale is not confirmed yet, the total_amount will be 0
# (just like the discount and surcharge).
"sale.subtotal": "(total_amount + discount_value - surcharge_value)",
"sale.discount_percentage": "(if(subtotal > 0," " (discount_value / subtotal), 0))",
"sale.surcharge_percentage": "(if(subtotal > 0," " (surcharge_value / subtotal), 0))",
"sale.cmv": "(sum(sale_item.total_cost))",
"sale.returned_items_cost": "(sum(returned_sale_via_new_sale."
" returned_sale_item.sale_item.total_cost))",
"sale.profit_margin": "(if((cmv - returned_items_cost) > 0,"
" ((total_amount /"
" (cmv - returned_items_cost)) - 1) * 100, 0))",
# SaleItem Fixes
"sale_item.total_price": "(price + ipi_info.v_ipi)",
"sale_item.sale_discount": "(total_price * sale.discount_percentage)",
"sale_item.sale_surcharge": "(total_price * sale.surcharge_percentage)",
"sale_item.price_with_discount": ("(total_price - sale_discount + sale_surcharge)"),
"sale_item.subtotal": "(total_price * quantity)",
"sale_item.total_with_discount": "(price_with_discount * quantity)",
"sale_item.total_cost": "(quantity * if(average_cost > 0,"
" average_cost,"
" sellable.cost))",
# Other fixes
"branch.main_address": "(person.address.filter(is_main_address))",
"transfer_order.branch": "(source_branch)",
"branch.description": (
"(if(is_null(person.company.fancy_name),"
" person.name,"
" person.company.fancy_name))"
),
},
"globals": {
"identifier_str": (
"branch.acronym + head('0000', 5 - " "length(string(identifier)))" "+ string(identifier)"
),
"trunc_month($d)": "datetime(year($d), month($d), 01)",
"trunc_day($d)": "datetime(year($d), month($d), day($d))",
"trunc_hour($d)": "datetime(year($d), month($d), day($d), hour($d))",
"between($date, $start, $end)": "($date >= $start & $date <= $end)",
# FIXME supplier name cannot be on field labels because
# databases without nfe schema will cause an error when
# this method is called
"supplier_name": (
"(if(is_null(nfe_supplier.name)," " nfe_supplier.fancy_name," " nfe_supplier.name))"
),
},
}
}
]
# FIXME: This is to support old stoq versions, which didn't have
# a UNIQUE constraint on product.sellable_id column
if stoq.stoq_version < (1, 10, 90):
exts[0]["tweak.override"]["unique_keys"] = "product(sellable_id)"
store = HTSQL(uri, *exts)
try:
rows = store.produce(query)
except HTSQL_Error as e:
return False, str(e)
with store:
json = "".join(emit("x-htsql/json", rows))
return True, json
def action_htsql_query(self, query):
"""Executes a HTSQL Query"""
try:
from htsql.core.fmt.emit import emit
from htsql.core.error import Error as HTSQL_Error
from htsql import HTSQL
except ImportError:
return False, "HTSQL installation not found"
# Resolve RDBMSs to their respective HTSQL engines
engines = {
'postgres': 'pgsql',
'sqlite': 'sqlite',
'mysql': 'mysql',
'oracle': 'oracle',
'mssql': 'mssql',
}
if db_settings.password:
password = "******" + urllib.quote_plus(db_settings.password)
else:
password = ""
authority = '%s%s@%s:%s' % (db_settings.username, password,
db_settings.address, db_settings.port)
uri = '%s://%s/%s' % (engines[db_settings.rdbms], authority,
db_settings.dbname)
# FIXME We should find a way to share this piece of code between Stoq
# Portal and Stoq Server, without having to duplicate it every time
exts = [{
'tweak.override': {
'field-labels': {
# Sellable fixes
'sellable.price':
('(if (on_sale_start_date <= now() &'
'now() <= on_sale_end_date, on_sale_price,'
'base_price))'),
# Sale fixes
# When the sale is not confirmed yet, the total_amount will be 0
# (just like the discount and surcharge).
'sale.subtotal':
'(total_amount + discount_value - surcharge_value)',
'sale.discount_percentage':
'(if(subtotal > 0,'
' (discount_value / subtotal), 0))',
'sale.surcharge_percentage':
'(if(subtotal > 0,'
' (surcharge_value / subtotal), 0))',
'sale.cmv':
'(sum(sale_item.total_cost))',
'sale.returned_items_cost':
'(sum(returned_sale_via_new_sale.'
' returned_sale_item.sale_item.total_cost))',
'sale.profit_margin':
'(if((cmv - returned_items_cost) > 0,'
' ((total_amount /'
' (cmv - returned_items_cost)) - 1) * 100, 0))',
# SaleItem Fixes
'sale_item.total_price':
'(price + ipi_info.v_ipi)',
'sale_item.sale_discount':
'(total_price * sale.discount_percentage)',
'sale_item.sale_surcharge':
'(total_price * sale.surcharge_percentage)',
'sale_item.price_with_discount':
('(total_price - sale_discount + sale_surcharge)'),
'sale_item.subtotal':
'(total_price * quantity)',
'sale_item.total_with_discount':
'(price_with_discount * quantity)',
'sale_item.total_cost':
'(quantity * if(average_cost > 0,'
' average_cost,'
' sellable.cost))',
# Other fixes
'branch.main_address':
'(person.address.filter(is_main_address))',
'transfer_order.branch':
'(source_branch)',
'branch.description':
('(if(is_null(person.company.fancy_name),'
' person.name,'
' person.company.fancy_name))'),
},
'globals': {
'identifier_str': ("branch.acronym + head('0000', 5 - "
"length(string(identifier)))"
"+ string(identifier)"),
'trunc_month($d)':
'datetime(year($d), month($d), 01)',
'trunc_day($d)':
'datetime(year($d), month($d), day($d))',
'trunc_hour($d)':
'datetime(year($d), month($d), day($d), hour($d))',
'between($date, $start, $end)':
'($date >= $start & $date <= $end)',
# FIXME supplier name cannot be on field labels because
# databases without nfe schema will cause an error when
# this method is called
'supplier_name': ('(if(is_null(nfe_supplier.name),'
' nfe_supplier.fancy_name,'
' nfe_supplier.name))'),
}
}
}]
# FIXME: This is to support old stoq versions, which didn't have
# a UNIQUE constraint on product.sellable_id column
if stoq.stoq_version < (1, 10, 90):
exts[0]['tweak.override']['unique_keys'] = 'product(sellable_id)'
store = HTSQL(uri, *exts)
try:
rows = store.produce(query)
except HTSQL_Error as e:
return False, str(e)
with store:
json = ''.join(emit('x-htsql/json', rows))
return True, json
def get_data_json(arg):
rows = connection.produce(arg)
with connection:
return ''.join(emit('x-htsql/json', rows))
def get_htsql():
#if mode == 'test':
# print criterion
#test = HTSQL("mysql://*****:*****@localhost/test")
test = HTSQL("pgsql://*****:*****@localhost/test")
#rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion)
#rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}")
#test = HTSQL("mysql://*****:*****@localhost/test")
rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}")
# rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}")
# http://127.0.0.1:8080/evaluation{comments,site{geoposition{id}?latitude=46&longitude%3E47},site{geoposition{accuracy}?latitude=46&longitude%3E47},evaluator{first_name}}?evaluator.first_name='you'/:sql
# http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'}
# http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'}?site_name!='Home'
# http://127.0.0.1:8080/person{first_name + ' ' + last_name :as name, count(evaluation) :as 20'N visits'}?count(evaluation)>0
# /evaluation{comments,site{geoposition?accuracy=46.0&latitude%3C42},evaluator{first_name}}?evaluator.first_name='you'
#
# SELECT "evaluation"."comments",
# "site"."?_1",
# "site"."?_2",
# "site"."id_1",
# "site"."latitude",
# "site"."longitude",
# "site"."accuracy",
# "site"."timestamp",
# "person_2"."?",
# "person_2"."first_name"
# FROM "evaluation"
# LEFT OUTER JOIN "person" AS "person_1"
# ON ("evaluation"."evaluator_id" = "person_1"."id")
# LEFT OUTER JOIN (SELECT TRUE AS "?_1",
# "geoposition"."?" AS "?_2",
# "geoposition"."id" AS "id_1",
# "geoposition"."latitude",
# "geoposition"."longitude",
# "geoposition"."accuracy",
# "geoposition"."timestamp",
# "site"."id" AS "id_2"
# FROM "site"
# LEFT OUTER JOIN (SELECT TRUE AS "?",
# "geoposition"."id",
# "geoposition"."latitude",
# "geoposition"."longitude",
# "geoposition"."accuracy",
# "geoposition"."timestamp"
# FROM "geoposition"
# WHERE ("geoposition"."accuracy" = 46.0::FLOAT8)
# AND ("geoposition"."latitude" < 42.0::FLOAT8)) AS "geoposition"
# ON ("site"."geoposition_id" = "geoposition"."id")) AS "site"
# ON ("evaluation"."site_id" = "site"."id_2")
# LEFT OUTER JOIN (SELECT TRUE AS "?",
# "person"."first_name",
# "person"."id"
# FROM "person") AS "person_2"
# ON ("evaluation"."evaluator_id" = "person_2"."id")
# WHERE ("person_1"."first_name" = 'you')
# ORDER BY "evaluation"."id" ASC
with test:
text = ''.join(emit('x-htsql/json', rows))
if mode == 'test':
print text, rows
return text
