Takes a specifically defined json object and converts it to a sql query.

json_to_sql.rs

use anyhow::Error;
use serde_json::{json, Value};

use crate::utils::merge_json;

pub async fn report_to_sql(report: Value) -> Result<String, Error> {
    let table = report["table"].as_object();

    if table.is_none() {
        return Err(Error::msg("Table not found."));
    }

    let table = table.unwrap();

    let mut query_string = String::from("SELECT");

    let columns = report["columns"].as_array();
    let references = report["references"].as_array();
    let filters = report["filters"].as_array();
    let sorts = report["sorts"].as_array();

    match columns {
        Some(columns) => {
            for column_idx in 0..columns.len() {
                let column = columns.get(column_idx);

                match column {
                    Some(column) => {
                        query_string = format!(
                            "{}\n\tt1.{} AS '{}',",
                            query_string,
                            column["column_name"].as_str().unwrap_or("-"),
                            column["custom_column_name"].as_str().unwrap_or(
                                format!("t1{}", column["column_name"].as_str().unwrap_or("-"))
                                    .as_str()
                            )
                        );
                    }
                    None => {}
                }
            }
        }
        None => {}
    };

    match references {
        Some(references) => {
            for reference_idx in 0..references.len() {
                let reference = references.get(reference_idx);

                match reference {
                    Some(reference) => {
                        let columns = reference["referenced_columns"].as_array();

                        match columns {
                            Some(columns) => {
                                for column_idx in 0..columns.len() {
                                    let column = columns.get(column_idx);

                                    match column {
                                        Some(column) => {
                                            query_string = format!(
                                                "{}\n\tt{}.{} AS '{}',",
                                                query_string,
                                                reference_idx + 2,
                                                column["column_name"].as_str().unwrap_or("-"),
                                                column["custom_column_name"].as_str().unwrap_or(
                                                    format!(
                                                        "t1{}",
                                                        column["column_name"]
                                                            .as_str()
                                                            .unwrap_or("-")
                                                    )
                                                    .as_str()
                                                )
                                            );
                                        }
                                        None => {}
                                    }
                                }
                            }
                            None => {}
                        };
                    }
                    None => {}
                }
            }
        }
        None => {}
    }

    query_string = format!(
        "{}\nFROM {} t1",
        query_string.strip_suffix(",").unwrap_or("-"),
        table["table_name"].as_str().unwrap_or("-")
    );

    match references {
        Some(references) => {
            for reference_idx in 0..references.len() {
                let reference = references.get(reference_idx);

                match reference {
                    Some(reference) => {
                        query_string = format!(
                            "{}\nLEFT JOIN {} t{} ON t{}.{} = t1.{}",
                            query_string,
                            reference["referenced_table"].as_str().unwrap_or("-"),
                            reference_idx + 2,
                            reference_idx + 2,
                            reference["referenced_table_column"].as_str().unwrap_or("-"),
                            reference["primary_table_column"].as_str().unwrap_or("-")
                        );
                    }
                    None => {}
                }
            }
        }
        None => {}
    }

    let mut primary_filters: Vec<Value> = Vec::new();
    let mut reference_filters: Vec<Value> = Vec::new();

    match filters {
        Some(filters) => {
            if filters.len() > 0 {
                primary_filters.extend(filters.to_owned());
                query_string = format!("{}\nWHERE", query_string);

                for filter_idx in 0..filters.len() {
                    let filter = filters.get(filter_idx);

                    match filter {
                        Some(filter) => {
                            if filter_idx < filters.len() && filter_idx > 0 {
                                query_string = format!("{},\n\tAND", query_string);
                            }

                            let filter_type = filter["filter_type"].as_str().unwrap_or("-");

                            match filter_type {
                                "sw" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} LIKE '{}%'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "ew" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} LIKE '%{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "incl" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} LIKE '%{}%'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "eq" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} = '{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "gt" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} > '{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "gte" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} >= '{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "lt" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} < '{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                "lte" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} <= '{}'",
                                        query_string,
                                        filter["column_name"].as_str().unwrap_or("-"),
                                        filter["filter_value"].as_str().unwrap_or("-")
                                    );
                                }
                                _ => {}
                            }
                        }
                        None => {}
                    }
                }
            }
        }
        None => {}
    }

    match references {
        Some(references) => {
            for reference_idx in 0..references.len() {
                let reference = references.get(reference_idx);

                match reference {
                    Some(reference) => {
                        let filters = reference["referenced_filters"].as_array();

                        match filters {
                            Some(filters) => {
                                for filter_idx in 0..filters.len() {
                                    let filter = filters.get(filter_idx);

                                    match filter {
                                        Some(filter) => {
                                            let mut old_filter = filter.clone();

                                            merge_json(
                                                &mut old_filter,
                                                &json!({ "reference_table_number": reference_idx + 2 }),
                                            );

                                            reference_filters.push(old_filter.to_owned());
                                        }
                                        None => {}
                                    }
                                }
                            }
                            None => {}
                        }
                    }
                    None => {}
                }
            }
        }
        None => {}
    }

    if primary_filters.len() == 0 && reference_filters.len() > 0 {
        query_string = format!("{}\nWHERE", query_string);
    }

    if primary_filters.len() > 0 && reference_filters.len() > 0 {
        query_string = format!("{}\n\tAND", query_string);
    }

    for filter_idx in 0..reference_filters.len() {
        let filter = reference_filters.get(filter_idx);

        match filter {
            Some(filter) => {
                if filter_idx < reference_filters.len() && filter_idx > 0 {
                    query_string = format!("{},\n\tAND", query_string);
                }

                let reference_table_number =
                    filter["reference_table_number"].as_i64().unwrap_or(1);
                let filter_type = filter["filter_type"].as_str().unwrap_or("-");

                match filter_type {
                    "sw" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "ew" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "incl" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "eq" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "gt" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "gte" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "lt" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    "lte" => {
                        query_string = format!(
                            "{}\n\tt{}.{} LIKE '{}%'",
                            query_string,
                            reference_table_number,
                            filter["column_name"].as_str().unwrap_or("-"),
                            filter["filter_value"].as_str().unwrap_or("-")
                        );
                    }
                    _ => {}
                }
            }
            None => {}
        }
    }

    let mut primary_sorts: Vec<Value> = Vec::new();
    let mut reference_sorts: Vec<Value> = Vec::new();

    match sorts {
        Some(sorts) => {
            if sorts.len() > 0 {
                primary_sorts.extend(sorts.to_owned());

                query_string = format!("{}\nORDER BY", query_string);

                for sort_idx in 0..sorts.len() {
                    let sort = sorts.get(sort_idx);

                    match sort {
                        Some(sort) => {
                            if sort_idx < sorts.len() && sort_idx > 0 {
                                query_string = format!("{},", query_string);
                            }

                            let sort_type = sort["sort_type"].as_str().unwrap_or("-");

                            match sort_type {
                                "asc" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} ASC",
                                        query_string,
                                        sort["column_name"].as_str().unwrap_or("-"),
                                    );
                                }
                                "desc" => {
                                    query_string = format!(
                                        "{}\n\tt1.{} DESC",
                                        query_string,
                                        sort["column_name"].as_str().unwrap_or("-"),
                                    );
                                }
                                _ => {}
                            }
                        }
                        None => {}
                    }
                }
            }
        }
        None => {}
    }

    match references {
        Some(references) => {
            for reference_idx in 0..references.len() {
                let reference = references.get(reference_idx);

                match reference {
                    Some(reference) => {
                        let sorts = reference["referenced_sorts"].as_array();

                        match sorts {
                            Some(sorts) => {
                                for sort_idx in 0..sorts.len() {
                                    let sort = sorts.get(sort_idx);

                                    match sort {
                                        Some(sort) => {
                                            let mut old_sort = sort.clone();

                                            merge_json(
                                                &mut old_sort,
                                                &json!({ "reference_table_number": reference_idx + 2 }),
                                            );

                                            reference_sorts.push(old_sort);
                                        }
                                        None => {}
                                    }
                                }
                            }
                            None => {}
                        }
                    }
                    None => {}
                }
            }
        }
        None => {}
    }

    if primary_sorts.len() == 0 && reference_sorts.len() > 0 {
        query_string = format!("{}\nORDER BY", query_string);
    }

    if primary_sorts.len() > 0 && reference_sorts.len() > 0 {
        query_string = format!("{},", query_string);
    }

    for sort_idx in 0..reference_sorts.len() {
        let sort = reference_sorts.get(sort_idx);

        match sort {
            Some(sort) => {
                if sort_idx < reference_sorts.len() && sort_idx > 0 {
                    query_string = format!("{},", query_string);
                }

                let reference_table_number = sort["reference_table_number"].as_i64().unwrap_or(1);
                let sort_type = sort["sort_type"].as_str().unwrap_or("-");

                match sort_type {
                    "asc" => {
                        query_string = format!(
                            "{}\n\tt{}.{} ASC",
                            query_string,
                            reference_table_number,
                            sort["column_name"].as_str().unwrap_or("-"),
                        );
                    }
                    "desc" => {
                        query_string = format!(
                            "{}\n\tt{}.{} DESC",
                            query_string,
                            reference_table_number,
                            sort["column_name"].as_str().unwrap_or("-"),
                        );
                    }
                    _ => {}
                }
            }
            None => {}
        }
    }

    Ok(query_string)
}