eversal_lib/database/
inv_market_group.rs

1use log::error;
2use sqlx::{Postgres, QueryBuilder};
3
4use crate::{LibResult, Error};
5
6const TABLE_NAME: &str = "inv_market_groups";
7
8#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
9pub struct MarketGroupDb {
10  #[serde(rename = "marketGroupId")]
11  pub market_group_id: i32,
12  #[serde(rename = "parentGroupId", skip_serializing_if = "Option::is_none")]
13  pub parent_group_id: Option<i32>,
14  #[serde(rename = "marketGroupName")]
15  pub market_group_name: String,
16  #[serde(skip_serializing_if = "Option::is_none")]
17  pub description: Option<String>,
18  #[serde(rename = "iconId")]
19  pub icon_id: Option<i32>,
20  #[serde(rename = "hasTypes")]
21  pub has_types: bool,
22}
23
24impl MarketGroupDb {
25  pub async fn get_by_id(id: i32) -> LibResult<Self> {
26    let pool = crate::pool();
27    let market_group = sqlx::query_as::<_, Self>(&format!(
28      "SELECT * FROM {} WHERE market_group_id = $1",
29      TABLE_NAME
30    ))
31    .bind(id)
32    .fetch_one(pool)
33    .await?;
34
35    Ok(market_group)
36  }
37
38  pub async fn count(params: &MarketGroupFilter) -> LibResult<i64> {
39    let pool = crate::pool();
40    let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
41    query = Self::build_query(query, params);
42    let query = query.build_query_scalar();
43
44    match query.fetch_one(pool).await {
45      Ok(count) => Ok(count),
46      Err(err) => {
47        error!("Error counting market groups: {}", err);
48        return Err(Error::new(
49          500,
50          format!("Error counting market groups: {}", err),
51        ));
52      }
53    }
54  }
55
56  pub async fn get_multiple(filter: &MarketGroupFilter) -> LibResult<Vec<Self>> {
57    let page = filter.page.unwrap_or(1);
58    let limit = filter.limit.unwrap_or(100);
59
60    let pool = crate::pool();
61    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
62    query = Self::build_query(query, filter);
63    query.push(" ORDER BY market_group_id LIMIT ");
64    query.push_bind(limit);
65    query.push(" OFFSET ");
66    query.push_bind((page - 1) * limit);
67    let query = query.build_query_as();
68
69    match query.fetch_all(pool).await {
70      Ok(market_groups) => Ok(market_groups),
71      Err(err) => {
72        error!("Error fetching market groups: {}", err);
73        return Err(Error::new(
74          500,
75          format!("Error fetching market groups: {}", err),
76        ));
77      }
78    }
79  }
80
81  fn build_query<'a>(
82    mut query: QueryBuilder<'a, Postgres>,
83    filter: &'a MarketGroupFilter,
84  ) -> QueryBuilder<'a, Postgres> {
85    let mut has_where = false;
86
87    // Parse the market_group_name query parameter
88    if let Some(market_group_names) = &filter.market_group_name {
89      let market_group_names: Vec<&str> = market_group_names.split(',').collect();
90      if !has_where {
91        query.push(" WHERE");
92        has_where = true;
93      } else {
94        query.push(" AND");
95      }
96      query.push(" (");
97      for (i, market_group_name) in market_group_names.iter().enumerate() {
98        if i > 0 {
99          query.push(" OR");
100        }
101        query.push(" market_group_name ILIKE ");
102        query.push_bind(format!("%{}%", market_group_name));
103      }
104      query.push(" )");
105    }
106
107    // Parse the id query parameter
108    if let Some(ids) = &filter.market_group_id {
109      let ids: Vec<&str> = ids.split(',').collect();
110      if !has_where {
111        query.push(" WHERE");
112      } else {
113        query.push(" AND");
114      }
115      query.push(" (");
116      for (i, id) in ids.iter().enumerate() {
117        // Parse the ID as an integer
118        let id = match id.parse::<i32>() {
119          Ok(id) => id,
120          Err(_) => continue,
121        };
122        if i > 0 {
123          query.push(" OR");
124        }
125        query.push(" market_group_id = ");
126        query.push_bind(id);
127      }
128      query.push(" )");
129    }
130
131    query
132  }
133
134  pub async fn insert(&self) -> LibResult<()> {
135    let pool = crate::pool();
136    sqlx::query(&format!(
137      "INSERT INTO {} (
138        market_group_id,
139        parent_group_id,
140        market_group_name,
141        description,
142        icon_id,
143        has_types
144      ) VALUES (
145        $1, $2, $3, $4, $5, $6
146      )",
147      TABLE_NAME
148    ))
149    .bind(self.market_group_id)
150    .bind(self.parent_group_id)
151    .bind(&self.market_group_name)
152    .bind(&self.description)
153    .bind(self.icon_id)
154    .bind(self.has_types)
155    .execute(pool)
156    .await?;
157    Ok(())
158  }
159
160  pub async fn insert_multiple(market_groups: &Vec<Self>) -> LibResult<()> {
161    let pool = crate::pool();
162    let step = 1000;
163    for i in (0..market_groups.len()).step_by(step) {
164      let mut query = sqlx::QueryBuilder::new(format!(
165        r#"
166        INSERT INTO {} (market_group_id, parent_group_id, market_group_name, description, icon_id, has_types)
167        VALUES
168        "#,
169        TABLE_NAME
170      ));
171
172      for j in 0..step {
173        if i + j >= market_groups.len() {
174          break;
175        }
176        if j > 0 {
177          query.push(", ");
178        }
179        let market_group = &market_groups[i + j];
180        query
181          .push(" (")
182          .push_bind(market_group.market_group_id)
183          .push(", ")
184          .push_bind(market_group.parent_group_id)
185          .push(", ")
186          .push_bind(&market_group.market_group_name)
187          .push(", ")
188          .push_bind(&market_group.description)
189          .push(", ")
190          .push_bind(market_group.icon_id)
191          .push(", ")
192          .push_bind(market_group.has_types)
193          .push(") ");
194      }
195
196      query
197        .push("ON CONFLICT (market_group_id) DO UPDATE SET ")
198        .push("parent_group_id = EXCLUDED.parent_group_id, ")
199        .push("market_group_name = EXCLUDED.market_group_name, ")
200        .push("description = EXCLUDED.description, ")
201        .push("icon_id = EXCLUDED.icon_id, ")
202        .push("has_types = EXCLUDED.has_types");
203      query.build().execute(pool).await?;
204    }
205    Ok(())
206  }
207
208  pub async fn update(&self) -> LibResult<()> {
209    let pool = crate::pool();
210    sqlx::query(&format!(
211      "UPDATE {} SET
212        parent_group_id = $2,
213        market_group_name = $3,
214        description = $4,
215        icon_id = $5,
216        has_types = $6
217      WHERE market_group_id = $1",
218      TABLE_NAME
219    ))
220    .bind(self.market_group_id)
221    .bind(&self.parent_group_id)
222    .bind(&self.market_group_name)
223    .bind(&self.description)
224    .bind(&self.icon_id)
225    .bind(&self.has_types)
226    .execute(pool)
227    .await?;
228    Ok(())
229  }
230
231  pub async fn delete(id: i32) -> LibResult<()> {
232    let pool = crate::pool();
233    sqlx::query(&format!(
234      "DELETE FROM {} WHERE market_group_id = $1",
235      TABLE_NAME
236    ))
237    .bind(id)
238    .execute(pool)
239    .await?;
240    Ok(())
241  }
242}
243
244#[derive(Debug, serde::Serialize, serde::Deserialize)]
245pub struct MarketGroupFilter {
246  #[serde(rename = "marketGroupId")]
247  pub market_group_id: Option<String>,
248  #[serde(rename = "marketGroupName")]
249  pub market_group_name: Option<String>,
250  pub page: Option<i64>,
251  pub limit: Option<i64>,
252}
eversal_lib/database/inv_market_group.rs

eversal_lib/database/
inv_market_group.rs
