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_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