@feedback.get("/ios-avg-team-feedbacks/{team}")
async def get_teams_avg_weekly(team: str, db: Session = Depends(get_db)):
    try:
        team_name = team
        current_week = int(datetime.now().date().strftime('%W'))
        disable_six_rule=(
            select([TeamMaster.disable_six_rule])
            .where(TeamMaster.team_name == team_name)
        )
        disable_rule_result=db.execute(disable_six_rule).fetchone()
        # Fetch all relevant data
        subquery = (
            db.query(
                Meta.feedback_week,
                func.sum(Meta.feedback_response_value).label("f_count"),
                func.count().label("weekly_average")
            )
            .filter(Meta.team_name == team_name)
            .filter(Meta.feedback_week.between(current_week - 5, current_week))
            .filter(
                or_(

                    Meta.feedback_date.in_(
                        db.query(Meta.feedback_date)
                        .filter(Meta.team_name == team_name)
                        .group_by(Meta.feedback_date)
                        .having(func.count(Meta.feedback_response_value) >= 6) if not disable_rule_result or not disable_rule_result["disable_six_rule"] else None
                    ),
                    disable_rule_result is None,
                    disable_rule_result["disable_six_rule"] == 0
                )
            )
            .group_by(Meta.feedback_week, Meta.feedback_response_value)
            .subquery()
        )
 
        rows = db.query(subquery.c.feedback_week, (func.sum(subquery.c.f_count) / cast(func.sum(subquery.c.weekly_average), Float)).label("average")).group_by(subquery.c.feedback_week).all()
 
        result = dict(rows)
 
        # Check if the count of weeks is less than 5
        if len(result) < 5:
            # Fetch additional weeks only if needed
            additional_weeks = 5 - len(result)
 
            additional_subquery = (
                db.query(
                    Meta.feedback_week,
                    func.sum(Meta.feedback_response_value).label("f_count"),
                    func.count().label("weekly_average")
                )
                .filter(Meta.team_name == team_name)
                .filter(Meta.feedback_week.between(51 - additional_weeks, 51))
                .filter(
                    or_(
                        Meta.feedback_date.in_(
                            db.query(Meta.feedback_date)
                            .filter(Meta.team_name == team_name)
                            .group_by(Meta.feedback_date)
                            .having(func.count(Meta.feedback_response_value) >= 6) if not disable_rule_result or not disable_rule_result["disable_six_rule"] else None
                        ),
                        disable_rule_result is None,
                        disable_rule_result["disable_six_rule"]==0
                    )
                )
                .group_by(Meta.feedback_week, Meta.feedback_response_value)
                .subquery()
            )
 
            additional_rows = db.query(additional_subquery.c.feedback_week, (func.sum(additional_subquery.c.f_count) / cast(func.sum(additional_subquery.c.weekly_average), Float)).label("average")).group_by(additional_subquery.c.feedback_week).all()
 
            additional_result = dict(additional_rows)
 
            # Merge the two results
            result.update(additional_result)
 
        # Sort the result by the latest weeks
        format_result = {k.zfill(2): result[k] for k in result}
        sorted_result = {k: format_result[k] for k in sorted(format_result, key=lambda x: (datetime.now().year, int(x) if int(x) <= current_week else int(x) - 52))}
        if sorted_result:
            formatted_result = {"Data": [{"Week": week, "Value": value} for week, value in sorted_result.items()][::-1], "Message": ""}
            return formatted_result
        else:
            formatted_result = {"Data": [], "Message": "No data available for this team"}
            return formatted_result
 
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail="Internal Server Error")
		
@feedback.get("/ios-team-feedbacks/{team}")
async def get_teams(team: str, db: Session = Depends(get_db)):
    try:
        team_name = team
        current_week = int(datetime.now().date().strftime('%W'))
        disable_six_rule=(
            select([TeamMaster.disable_six_rule])
            .where(TeamMaster.team_name == team_name)
        )
        disable_rule_result=db.execute(disable_six_rule).fetchone()

        result = []
 
        subquery = (
            db.query(
                Meta.feedback_week,
                Meta.feedback_response_value,
                func.count().label("count")
            )
            .filter(Meta.team_name == team_name)
            .filter(Meta.feedback_week.between(current_week - 5, current_week))
            .filter(
                or_(
                Meta.feedback_date.in_(
                    db.query(Meta.feedback_date)
                    .filter(Meta.team_name == team_name)
                    .group_by(Meta.feedback_date)
                    .having(func.count(Meta.feedback_response_value) >= 6) if not disable_rule_result or not disable_rule_result["disable_six_rule"] else None
                ),
                    disable_rule_result is None,
                    disable_rule_result["disable_six_rule"] == 0
                )
            )
            .group_by(Meta.feedback_week, Meta.feedback_response_value)
            .subquery()
        )
 
        rows = db.query(subquery).all()
 
        for row in rows:
            feedback_week, feedbackvalue, count = row
            existing_week = next((item for item in result if item["Week"] == feedback_week), None)
 
            if existing_week is None:
                result.append({"Week": feedback_week, "Values": {}})
                existing_week = result[-1]
 
            existing_week["Values"][str(feedbackvalue)] = count
 
        if len(result) < 5:
            additional_weeks = 5 - len(result)
 
            additional_subquery = (
                db.query(
                    Meta.feedback_week,
                    Meta.feedback_response_value,
                    func.count().label("count")
                )
                .filter(Meta.team_name == team_name)
                .filter(Meta.feedback_week.between(51 - additional_weeks, 51))
                .filter(
                    or_(
                    Meta.feedback_date.in_(
                        db.query(Meta.feedback_date)
                        .filter(Meta.team_name == team_name)
                        .group_by(Meta.feedback_date)
                        .having(func.count(Meta.feedback_response_value) >= 6) if not disable_rule_result or not disable_rule_result["disable_six_rule"] else None
                   ),
                    disable_rule_result is None,
                    disable_rule_result["disable_six_rule"] == 0
                )
                )
                .group_by(Meta.feedback_week, Meta.feedback_response_value)
                .subquery()
            )
 
            additional_rows = db.query(additional_subquery).all()
 
            for row in additional_rows:
                feedback_week, feedbackvalue, count = row
                existing_week = next((item for item in result if item["Week"] == feedback_week), None)
 
                if existing_week is None:
                    result.append({"Week": feedback_week, "Values": {}})
                    existing_week = result[-1]
 
                existing_week["Values"][str(feedbackvalue)] = count
 
        sorted_result = sorted(result, key=lambda x: (datetime.now().year, int(x["Week"]) if int(x["Week"]) <= current_week else int(x["Week"]) - 52))
 
        formatted_result = [{"Week": item["Week"], "Values": item["Values"]} for item in sorted_result][::-1]
        return {"Data":formatted_result,"Message":""} if formatted_result else {"Data":[],"Message":"No data available for this team"}

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))
		
		
[2:51 PM] Vijay, Deeban (623-Extern-MICROGENESIS)
#------------------------------------------------------ Gives avg voting for current week and past five weeks ----------------------------------------------

@feedback.get("/avg-team-feedbacks/{team}")

async def get_teams_avg_weekly(team: str, db: Session = Depends(get_db)):

    try:

        team_name = team

        current_week = int(datetime.now().date().strftime('%W'))

        disable_six_rule=(

            select([TeamMaster.disable_six_rule])

            .where(TeamMaster.team_name == team_name)

        )

        disable_rule_result=db.execute(disable_six_rule).fetchone()

        # Fetch all relevant data

        subquery = (

            db.query(

                Meta.feedback_week,

                func.sum(Meta.feedback_response_value).label("f_count"),

                func.count().label("weekly_average")

            )

            .filter(Meta.team_name == team_name)

            .filter(Meta.feedback_week.between(current_week - 5, current_week))

            .filter(

                or_(
 
                    Meta.feedback_date.in_(

                        db.query(Meta.feedback_date)

                        .filter(Meta.team_name == team_name)

                        .group_by(Meta.feedback_date)

                        .having(func.count(Meta.feedback_response_value) >= 6)

                    ),

                    disable_rule_result is None,

                    disable_rule_result["disable_six_rule"] == 0

                )

            )

            .group_by(Meta.feedback_week, Meta.feedback_response_value)

            .subquery()

        )

        rows = db.query(subquery.c.feedback_week, (func.sum(subquery.c.f_count) / cast(func.sum(subquery.c.weekly_average), Float)).label("average")).group_by(subquery.c.feedback_week).all()

        result = dict(rows)

        # Check if the count of weeks is less than 5

        if len(result) < 5:

            # Fetch additional weeks only if needed

            additional_weeks = 5 - len(result)

            additional_subquery = (

                db.query(

                    Meta.feedback_week,

                    func.sum(Meta.feedback_response_value).label("f_count"),

                    func.count().label("weekly_average")

                )

                .filter(Meta.team_name == team_name)

                .filter(Meta.feedback_week.between(51 - additional_weeks, 51))

                .filter(

                    or_(

                        Meta.feedback_date.in_(

                            db.query(Meta.feedback_date)

                            .filter(Meta.team_name == team_name)

                            .group_by(Meta.feedback_date)

                            .having(func.count(Meta.feedback_response_value) >= 6)

                        ),

                        disable_rule_result is None,

                        disable_rule_result["disable_six_rule"]==0

                    )

                )

                .group_by(Meta.feedback_week, Meta.feedback_response_value)

                .subquery()

            )

            additional_rows = db.query(additional_subquery.c.feedback_week, (func.sum(additional_subquery.c.f_count) / cast(func.sum(additional_subquery.c.weekly_average), Float)).label("average")).group_by(additional_subquery.c.feedback_week).all()

            additional_result = dict(additional_rows)

            # Merge the two results

            result.update(additional_result)

        # Sort the result by the latest weeks

        format_result = {k.zfill(2): result[k] for k in result}

        sorted_result = {k: format_result[k] for k in sorted(format_result, key=lambda x: (datetime.now().year, int(x) if int(x) <= current_week else int(x) - 52))}

        if sorted_result:

            formatted_result = {week:value for week, value in sorted_result.items()}

            return formatted_result

        else:

            return {}

    except SQLAlchemyError as e:

        logger.log_exception(e)

        raise HTTPException(status_code=500, detail="Internal Server Error")
[2:51 PM] Vijay, Deeban (623-Extern-MICROGENESIS)


@feedback.get("/team-feedbacks/{team}")

async def get_teams(team: str, db: Session = Depends(get_db)):

    try:

        team_name = team

        current_week = int(datetime.now().date().strftime('%W'))

        disable_six_rule=(

            select([TeamMaster.disable_six_rule])

            .where(TeamMaster.team_name == team_name)

        )

        disable_rule_result=db.execute(disable_six_rule).fetchone()

        result = {}

        subquery = (

            db.query(

                Meta.feedback_week,

                Meta.feedback_response_value,

                func.count().label("count")

            )

            .filter(Meta.team_name == team_name)

            .filter(Meta.feedback_week.between(current_week - 5, current_week))

            .filter(

                or_(

                    Meta.feedback_date.in_(

                        db.query(Meta.feedback_date)

                        .filter(Meta.team_name == team_name)

                        .group_by(Meta.feedback_date)

                        .having(func.count(Meta.feedback_response_value) >= 6)

                    ),

                    disable_rule_result is None,

                    disable_rule_result["disable_six_rule"] == 0

                )

            )

            .group_by(Meta.feedback_week, Meta.feedback_response_value)

            .subquery()

        )

        rows = db.query(subquery).all()

        for row in rows:

            feedback_week, feedbackvalue, count = row

            if feedback_week not in result:

                result[feedback_week] = {}

            result[feedback_week][str(feedbackvalue)] = count

        if len(result) < 5:

            additional_weeks = 5 - len(result)

            additional_subquery = (

                db.query(

                    Meta.feedback_week,

                    Meta.feedback_response_value,

                    func.count().label("count")

                )

                .filter(Meta.team_name == team_name)

                .filter(Meta.feedback_week.between(51 - additional_weeks, 51))

                .filter(

                    or_(

                        Meta.feedback_date.in_(

                            db.query(Meta.feedback_date)

                            .filter(Meta.team_name == team_name)

                            .group_by(Meta.feedback_date)

                            .having(func.count(Meta.feedback_response_value) >= 6)

                        ),

                        disable_rule_result is None,

                        disable_rule_result["disable_six_rule"] == 0

                    )

            )

            .group_by(Meta.feedback_week, Meta.feedback_response_value)

            .subquery()

        )

            additional_rows = db.query(additional_subquery).all()

            for row in additional_rows:

                feedback_week, feedbackvalue, count = row

                if feedback_week not in result:

                    result[feedback_week] = {}

                result[feedback_week][str(feedbackvalue)] = count

        sorted_result = {k: result[k] for k in sorted(result, key=lambda x: (datetime.now().year, int(x) if int(x) <= current_week else int(x) - 52))}

        return sorted_result

    except Exception as e:

        logger.log_exception(e)

        raise HTTPException(status_code=500, detail=str(e))