def formfield_for_dbfield(self, db_field, **kwargs): if db_field.name == 'project': company_ids = getAllCompanyIds(self) kwargs['queryset'] = Project.objects.filter( company_id__in=company_ids) field = super(ProjectSettingAdmin, self).formfield_for_dbfield(db_field, **kwargs) return field
def build_query(self, year, company): ##for mysql query = """SELECT company.name AS company_name, project.name AS project_name, project.material_amount AS estimate_total, IFNULL(setting.online_before_amount,0) as 'online_before_amount', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='01' then receiving.total else 0 end) as 'one_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='02' then receiving.total else 0 end) as 'two_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='03' then receiving.total else 0 end) as 'three_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='04' then receiving.total else 0 end) as 'four_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='05' then receiving.total else 0 end) as 'five_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='06' then receiving.total else 0 end) as 'six_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='07' then receiving.total else 0 end) as 'seven_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='08' then receiving.total else 0 end) as 'eight_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='09' then receiving.total else 0 end) as 'night_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='10' then receiving.total else 0 end) as 'ten_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='11' then receiving.total else 0 end) as 'eleven_month', sum(case when YEAR(checkaccount.end_date) = {0} and MONTH(checkaccount.end_date)='12' then receiving.total else 0 end) as 'twelve_month' FROM project_project project LEFT JOIN setting_projectsetting setting ON project.id = setting.project_id LEFT JOIN company_company company ON project.company_id = company.id LEFT JOIN order_order orders ON project.id = orders.project_id LEFT JOIN order_orderline orderline ON orders.id = orderline.order_id LEFT JOIN order_receivingline receiving ON orderline.id = receiving.orderLine_id LEFT JOIN order_checkaccount checkaccount ON receiving.checkAccount_id = checkaccount.id AND YEAR(checkaccount.end_date)<= '{0}' AND receiving.checkAccount_id is not null """.format(year) if len(company)>0: query += " WHERE company.name = '%s'" % company else: company_ids = getAllCompanyIds(self) query += " WHERE company.id in %s " % str(company_ids).replace("[", "(").replace("]", ")") query += " GROUP BY company.id, project.id ORDER BY project.id DESC" return query
def formfield_for_dbfield(self, db_field, **kwargs): if db_field.name == 'project': company_ids = getAllCompanyIds(self) kwargs['queryset'] = Project.objects.filter(company_id__in=company_ids) field = super(ProjectSettingAdmin, self).formfield_for_dbfield(db_field, **kwargs) return field