Untitled

def get_productivity_cycle_time_metrics_historic(self,shop_id,line_arr=[],subline_arr=[],
                                                     station_arr=[],model_arr=[],variant_arr=[],from_date=None,
                                                     to_date=None,based_on=None,is_delay = None,
                                                     is_tip_dress = None,is_fault=None):
        
        filters = []
        date_filter = []
        filters.append(ParameterMeasurement.shop_id == shop_id)
        filters.append(Parameter.is_deleted.isnot(True))
        if line_arr != None and len(line_arr) != 0:
            filters.append(ParameterMeasurement.line_id.in_(line_arr))
        if subline_arr != None and len(subline_arr) != 0:
            filters.append(ParameterMeasurement.subline_id.in_(subline_arr))
        if station_arr != None and len(station_arr) != 0:
            filters.append(ParameterMeasurement.station_id.in_(station_arr))
        if model_arr != None and len(model_arr) != 0:
            filters.append(ParameterMeasurement.model_id.in_(model_arr))
        if variant_arr != None and len(variant_arr) != 0:
            filters.append(Component.variant_id.in_(variant_arr))
        if  from_date != None and to_date != None:
            date_filter.append(and_(ParameterMeasurement.datetime <= to_date,ParameterMeasurement.datetime >= from_date))
        elif from_date == None and to_date == None:
            date_filter.append(ParameterMeasurement.datetime >= DatetimeHelper.get_ist_datetime().date())
            
        msil_line_alias = aliased(MSILLine)

        with self.session:
            query = self.session.query(
                ParameterMeasurement.id.label('measurement_id'),
                ParameterMeasurement.parameter_id.label('parameter_id'),
                ParameterMeasurement.value.label('measurement_value'),
                Parameter.standard_value.label('standard_station_cycle_time'),
                ParameterMeasurement.station_id.label('station_id'),
                ParameterMeasurement.line_id.label('line_id'),
                ParameterMeasurement.subline_id.label('subline_id'),
                ParameterMeasurement.model_id.label('engine_model_id'),
                EngineModel.name.label('engine_model_name'),
                Component.variant_id,
                Variant.name.label('variant_name'),
                Component.part_id,
                ParameterMeasurement.part_serial_no.label('part_name'),
                ParameterMeasurement.zone,
            ).select_from(ParameterMeasurement)\
            .filter(*date_filter)\
            .join(Parameter,ParameterMeasurement.parameter_id==Parameter.id)\
            .join(Component,ParameterMeasurement.component_id == Component.id)\
            .join(EngineModel,EngineModel.id == ParameterMeasurement.model_id,isouter=True)\
            .join(Part,Part.id == Component.part_id,isouter=True)\
            .join(Variant,Variant.id == Component.variant_id,isouter=True)\
            .order_by(desc(ParameterMeasurement.datetime))\
            .filter(*filters)\
            .filter(Parameter.name == 'Cycle time',
                    Parameter.is_deleted.isnot(True),
                    EngineModel.is_deleted.isnot(True),
                    Part.is_deleted.isnot(True),
                    Variant.is_deleted.isnot(True))\
            .limit(based_on)\
            .subquery()

            station_cycle_time = self.session.query(
                query.c.station_id,
                func.avg(query.c.measurement_value).label('station_cycle_time'),
            ).group_by( query.c.station_id)\
            .subquery()

            standard_line_cycle_time = self.session.query(
                query.c.line_id,
                func.max(LineTarget.target_cycle_time).label('standard_line_cycle_time'),
            )\
            .join(LinePart,and_(LinePart.line_id == query.c.line_id,LinePart.model_id == query.c.engine_model_id))\
            .join(LineTarget,and_(LineTarget.line_id == LinePart.line_id,LineTarget.model_id == LinePart.model_id,LineTarget.variant_id == LinePart.variant_id))\
            .group_by( query.c.line_id)\
            .subquery()

            line_subline_station = self.session.query(
                Station.line_id,
                MSILLine.name.label('line_name'),
                msil_line_alias.name.label('subline_name'),
                Station.subline_id,
                Station.id.label('station_id'),
                Station.name.label('station_name'),
                MSILLine.priority.label('line_priority'),
                msil_line_alias.priority.label('subline_priority'),
                Station.priority.label('station_priority')
            ).join(MSILLine,MSILLine.id == Station.line_id,isouter=True)\
            .join(msil_line_alias,msil_line_alias.id == Station.subline_id,isouter=True )\
            .filter(Station.shop_id == shop_id)\
            .filter(Station.is_deleted.isnot(True),
                    MSILLine.is_deleted.isnot(True),
                    msil_line_alias.is_deleted.isnot(True))\
            .subquery()

            query = self.session.query(
                query.c.parameter_id,
                query.c.parameter_id,
                query.c.standard_station_cycle_time ,
                station_cycle_time.c.station_cycle_time,
                standard_line_cycle_time.c.standard_line_cycle_time,
                query.c.line_id,
                query.c.engine_model_id,
                query.c.engine_model_name,
                query.c.subline_id,
                query.c.variant_id,
                query.c.variant_name,
                query.c.part_id,
                query.c.part_name,  
                query.c.station_id,
                query.c.zone,
            ).join(station_cycle_time,station_cycle_time.c.station_id ==  query.c.station_id,isouter=True)\
            .join(standard_line_cycle_time,standard_line_cycle_time.c.line_id == query.c.line_id,isouter=True)\
            .distinct()\
            .subquery()

            filters = []
            if line_arr != None and len(line_arr) != 0:
                filters.append(line_subline_station.c.line_id.in_(line_arr))
            if subline_arr != None and len(subline_arr) != 0:
                filters.append(line_subline_station.c.subline_id.in_(subline_arr))
            if station_arr != None and len(station_arr) != 0:
                filters.append(line_subline_station.c.station_id.in_(station_arr))
            if model_arr != None and len(model_arr) != 0:
                filters.append(query.c.model_id.in_(model_arr))
            if variant_arr != None and len(variant_arr) != 0:
                filters.append(query.c.variant_id.in_(variant_arr))  
            

            query = self.session.query(
                query.c.parameter_id,
                query.c.parameter_id,
                query.c.standard_station_cycle_time ,
                query.c.station_cycle_time ,
                query.c.standard_line_cycle_time ,
                line_subline_station.c.line_id,
                line_subline_station.c.line_name,
                query.c.engine_model_id,
                query.c.engine_model_name,
                line_subline_station.c.subline_id,
                line_subline_station.c.subline_name,
                query.c.variant_id,
                query.c.variant_name,
                query.c.part_id,
                query.c.part_name,  
                line_subline_station.c.station_id,
                line_subline_station.c.station_name,
                FeederMap.feed_subline_id,
                query.c.zone,                
            ).join(query,and_(query.c.line_id == line_subline_station.c.line_id,query.c.subline_id == line_subline_station.c.subline_id,query.c.station_id == line_subline_station.c.station_id),isouter=True)\
            .join(FeederMap,FeederMap.feeded_to_station_id == line_subline_station.c.station_id,isouter=True)\
            .filter(FeederMap.is_deleted.isnot(True))\
            .filter(*filters)\
            .order_by(line_subline_station.c.line_priority,line_subline_station.c.line_id,line_subline_station.c.subline_priority,line_subline_station.c.subline_id,line_subline_station.c.station_priority,line_subline_station.c.station_id)\
            .distinct()
            
            print(query)
            
  

            return query.all()