def get_sobjects(self):
columns = ['product_name', 'login_id', 'sample_size', 'labor_average']
sql = '''
select "product_name", "login_id", count("total_hours") "sample_size", avg("total_hours") "labor_average"
from (
select "product_type"."id", "product_type"."product_name",
"subtask_product"."product_quantity",
"subtask"."subtask_letter",
"subtask_labor"."total_hours",
"personal_time_log"."login_id"
FROM "product_type"
LEFT OUTER JOIN "subtask_product"
ON "product_type"."id" = "subtask_product"."product_type_id"
INNER JOIN "subtask"
ON "subtask"."id" = "subtask_product"."subtask_id"
INNER JOIN "subtask_labor"
ON "subtask"."id" = "subtask_labor"."subtask_id"
INNER JOIN "personal_time_log"
ON "subtask_labor"."id" = "personal_time_log"."subtask_labor_id"
) X
GROUP BY X."product_name", X."login_id"
'''
db = DbContainer.get("MMS")
from pyasm.search import Select, Search
search_type = self.kwargs.get('search_type')
select = Select()
select.set_statement(sql)
statement = select.get_statement()
search = Search(search_type)
search.select = select
sobjects = search.get_sobjects()
for sobject in sobjects:
sobject.set_value("id", -1)
return sobjects
def get_sobjects(my):
columns = [
'product_name', 'sample_size', 'labor_average',
'material_average_cost', 'average_time_taken'
]
sql = '''
SELECT
"product_name",
"number_of_each_product_type" "sample_size",
"average_time_taken" "labor_average",
sum("material_cost_per_item") "material_average_cost",
sum("vendor_cost_per_item") "vender_average_cost"
FROM (
SELECT "product_name", "subtask"."id" "subtask_id", "number_of_each_product_type", "average_time_taken",
"product_quantity",
--"total_material_cost", "total_vendor_costs",
"total_material_cost" / "product_quantity" "material_cost_per_item",
"total_vendor_costs" / "product_quantity" "vendor_cost_per_item"
FROM "product_type"
LEFT OUTER JOIN "product_type_aggrgt"
ON "product_type"."id" = "product_type_aggrgt"."product_type_id"
LEFT OUTER JOIN "subtask_product"
ON "product_type"."id" = "subtask_product"."product_type_id"
LEFT OUTER JOIN "subtask"
ON "subtask"."id" = "subtask_product"."subtask_id"
LEFT OUTER JOIN "subtask_material_aggrgt"
ON "subtask"."id" = "subtask_material_aggrgt"."subtask_id"
LEFT OUTER JOIN "subtask_vndrcost_aggrgt"
ON "subtask"."id" = "subtask_vndrcost_aggrgt"."subtask_id"
) X
group by X."product_name", X."number_of_each_product_type", X."average_time_taken",
X."product_quantity", "subtask_id"
order by X."product_name"
'''
db = DbContainer.get("MMS")
from pyasm.search import Select, Search
search_type = my.kwargs.get('search_type')
select = Select()
select.set_statement(sql)
statement = select.get_statement()
search = Search(search_type)
search.select = select
sobjects = search.get_sobjects()
for sobject in sobjects:
sobject.set_value("id", -1)
return sobjects
def get_display(self):
web = WebContainer.get_web()
args = web.get_form_args()
if not self.search_type:
self.search_type = args.get('search_type')
if not self.search_id:
self.search_id = args.get('search_id')
# get from cgi
if not self.search_type:
self.search_type = web.get_form_value("search_type")
self.search_id = web.get_form_value("search_id")
if not self.asset_search_type:
self.asset_search_type = web.get_form_value('asset_search_type')
context_filter = web.get_form_value("context_%s_%s" %
(self.search_type, self.search_id))
versions_filter = web.get_form_value(
"versions_%s_%s" % (self.search_type, self.search_id))
div_id = "history_%s_%s" % (self.search_type, self.search_id)
ajax = AjaxLoader(div_id)
if ajax.is_refresh():
div = Widget()
else:
div = DivWdg()
div.add_color('background', 'background2')
div.add_class('spt_history_wdg')
div.add_style("display: block")
div.add_style("float: right")
div.add_style("width: 95%")
div.add_style("margin-left: 50px")
div.set_id(div_id)
div.add(self.get_filter_wdg(self.search_type, self.search_id, div_id))
# get the sobject
sobject = Search.get_by_id(self.search_type, self.search_id)
# get the snapshots
search = self.get_snapshot_search()
if context_filter:
search.add_filter("context", context_filter)
if not versions_filter:
versions_filter = self.get_default_versions_filter()
self.select.set_value(versions_filter)
if versions_filter == 'current':
search.add_filter("is_current", True)
elif versions_filter == 'latest':
search.add_filter("is_latest", True)
elif versions_filter == 'last 10':
search.add_limit(10)
elif versions_filter == 'today':
from pyasm.search import Select
search.add_where(Select.get_interval_where(versions_filter))
elif versions_filter == 'all':
pass
else:
search.add_filter("is_latest", True)
search.add_order_by("timestamp desc")
snapshots = search.do_search()
div.add(self.get_table(sobject, snapshots))
return div
def get_sobjects(self):
columns = ['product_name', 'sample_size', 'labor_average', 'material_average_cost', 'average_time_taken']
sql = '''
SELECT
"product_name",
"number_of_each_product_type" "sample_size",
"average_time_taken" "labor_average",
sum("material_cost_per_item") "material_average_cost",
sum("vendor_cost_per_item") "vender_average_cost"
FROM (
SELECT "product_name", "subtask"."id" "subtask_id", "number_of_each_product_type", "average_time_taken",
"product_quantity",
--"total_material_cost", "total_vendor_costs",
"total_material_cost" / "product_quantity" "material_cost_per_item",
"total_vendor_costs" / "product_quantity" "vendor_cost_per_item"
FROM "product_type"
LEFT OUTER JOIN "product_type_aggrgt"
ON "product_type"."id" = "product_type_aggrgt"."product_type_id"
LEFT OUTER JOIN "subtask_product"
ON "product_type"."id" = "subtask_product"."product_type_id"
LEFT OUTER JOIN "subtask"
ON "subtask"."id" = "subtask_product"."subtask_id"
LEFT OUTER JOIN "subtask_material_aggrgt"
ON "subtask"."id" = "subtask_material_aggrgt"."subtask_id"
LEFT OUTER JOIN "subtask_vndrcost_aggrgt"
ON "subtask"."id" = "subtask_vndrcost_aggrgt"."subtask_id"
) X
group by X."product_name", X."number_of_each_product_type", X."average_time_taken",
X."product_quantity", "subtask_id"
order by X."product_name"
'''
db = DbContainer.get("MMS")
from pyasm.search import Select, Search
search_type = self.kwargs.get('search_type')
select = Select()
select.set_statement(sql)
statement = select.get_statement()
search = Search(search_type)
search.select = select
sobjects = search.get_sobjects()
for sobject in sobjects:
sobject.set_value("id", -1)
return sobjects
def get_display(my):
web = WebContainer.get_web()
args = web.get_form_args()
if not my.search_type:
my.search_type = args.get('search_type')
if not my.search_id:
my.search_id = args.get('search_id')
# get from cgi
if not my.search_type:
my.search_type = web.get_form_value("search_type")
my.search_id = web.get_form_value("search_id")
if not my.asset_search_type:
my.asset_search_type = web.get_form_value('asset_search_type')
context_filter = web.get_form_value("context_%s_%s" % (my.search_type, my.search_id) )
versions_filter = web.get_form_value("versions_%s_%s" % (my.search_type, my.search_id) )
div_id = "history_%s_%s" % (my.search_type, my.search_id)
ajax = AjaxLoader(div_id)
if ajax.is_refresh():
div = Widget()
else:
div = DivWdg()
div.add_color('background','background2')
div.add_class('spt_history_wdg')
div.add_style("display: block")
div.add_style("float: right")
div.add_style("width: 95%")
div.add_style("margin-left: 50px")
div.set_id( div_id )
div.add( my.get_filter_wdg(my.search_type, my.search_id, div_id) )
# get the sobject
sobject = Search.get_by_id(my.search_type, my.search_id)
# get the snapshots
search = my.get_snapshot_search()
if context_filter:
search.add_filter("context", context_filter)
if not versions_filter:
versions_filter = my.get_default_versions_filter()
my.select.set_value(versions_filter)
if versions_filter == 'current':
search.add_filter("is_current", True)
elif versions_filter == 'latest':
search.add_filter("is_latest", True)
elif versions_filter == 'last 10':
search.add_limit(10)
elif versions_filter == 'today':
from pyasm.search import Select
search.add_where(Select.get_interval_where(versions_filter))
elif versions_filter == 'all':
pass
else:
search.add_filter("is_latest", True)
search.add_order_by("timestamp desc")
snapshots = search.do_search()
div.add(my.get_table(sobject,snapshots) )
return div
