001-server

umuro

Jul 3rd, 2025

129

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

Rust 207.94 KB | None | 0 0

raw download clone embed print report

This file is a merged representation of a subset of the codebase, containing specifically included files, combined into a single document by Repomix.
================================================================
File Summary
================================================================
Purpose:
--------
This file contains a packed representation of a subset of the repository's contents that is considered the most important context.
It is designed to be easily consumable by AI systems for analysis, code review,
or other automated processes.
File Format:
------------
The content is organized as follows:
1. This summary section
2. Repository information
3. Directory structure
4. Repository files (if enabled)
5. Multiple file entries, each consisting of:
a. A separator line (================)
b. The file path (File: path/to/file)
c. Another separator line
d. The full contents of the file
e. A blank line
Usage Guidelines:
-----------------
- This file should be treated as read-only. Any changes should be made to the
original repository files, not this packed version.
- When processing this file, use the file path to distinguish
between different files in the repository.
- Be aware that this file may contain sensitive information. Handle it with
the same level of security as you would the original repository.
Notes:
------
- Some files may have been excluded based on .gitignore rules and Repomix's configuration
- Binary files are not included in this packed representation. Please refer to the Repository Structure section for a complete list of file paths, including binary files
- Only files matching these patterns are included: gmm_client/src/tables
- Files matching patterns in .gitignore are excluded
- Files matching default ignore patterns are excluded
- Files are sorted by Git change count (files with more changes are at the bottom)
================================================================
Directory Structure
================================================================
gmm_client/
src/
tables/
assets/
mod.rs
query.rs
resource.rs
dashboardjibs/
mod.rs
query.rs
resource.rs
dashboardrevenue/
mod.rs
query.rs
resource.rs
dashboardtotalreportedpayments/
mod.rs
query.rs
resource.rs
incominglocation/
mod.rs
query.rs
resource.rs
incomingproducts/
mod.rs
query.rs
resource.rs
incomingtable1/
mod.rs
query.rs
resource.rs
incomingtable2/
mod.rs
query.rs
resource.rs
iwv_qc_queries/
incomingtables/
mod.rs
query.rs
resource.rs
taxyearrev1099/
mod.rs
query.rs
resource.rs
mod.rs
jibdetails/
mod.rs
query.rs
resource.rs
jibnetting/
mod.rs
query.rs
resource.rs
jibsummary/
mod.rs
query.rs
resource.rs
leases/
mod.rs
query.rs
resource.rs
pieleasestatus/
mod.rs
query.rs
resource.rs
pieproducingwellscounty/
mod.rs
query.rs
resource.rs
pieproducingwellsstate/
mod.rs
query.rs
resource.rs
pierevenuebycounty/
mod.rs
query.rs
resource.rs
pierevenuebyoperator/
mod.rs
query.rs
resource.rs
pierevenuebyproduct/
mod.rs
query.rs
resource.rs
pierevenuebystate/
mod.rs
query.rs
resource.rs
piewellstatus/
mod.rs
query.rs
resource.rs
revenue/
mod.rs
query.rs
resource.rs
revjibdetails/
mod.rs
query.rs
resource.rs
revjibsummary/
mod.rs
query.rs
resource.rs
statslistofcounts/
mod.rs
query.rs
resource.rs
statsproduction/
mod.rs
query.rs
resource.rs
statstopoperators/
mod.rs
query.rs
resource.rs
surfacerevenue/
mod.rs
query.rs
resource.rs
taxyear1099details/
mod.rs
query.rs
resource.rs
taxyearfullyeartotals/
mod.rs
query.rs
resource.rs
taxyearinterestbystate/
mod.rs
query.rs
resource.rs
taxyearrevenueand1099s/
mod.rs
query.rs
resource.rs
taxyearwellsbystate/
mod.rs
query.rs
resource.rs
wellops/
mod.rs
query.rs
resource.rs
wellrev/
mod.rs
query.rs
resource.rs
ytdcurrentyear/
mod.rs
query.rs
resource.rs
ytdpasttaxyear/
mod.rs
query.rs
resource.rs
ytdpfibyoperator/
mod.rs
query.rs
resource.rs
ytdpfibyproduct/
mod.rs
query.rs
resource.rs
ytdrevenuebyoperator/
mod.rs
query.rs
resource.rs
mod.rs
================================================================
Files
================================================================
================
File: gmm_client/src/tables/assets/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/assets/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the assets report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct AssetsRow {
pub canonical: String,
pub state: Option<String>,
pub county: Option<String>,
pub township: Option<String>,
pub land_range: Option<String>,
pub section: Option<String>,
pub portion_of_section: Option<String>,
pub survey_name: Option<String>,
pub abstract_number: Option<String>,
pub block: Option<String>,
pub full_legal_description: Option<String>,
pub interest_type: Option<String>,
pub area_share_nma: Option<f64>,
pub conveyance_type: Option<String>,
pub document: Option<String>,
}
/// Fetches asset-related data from multiple tables.
#[server]
pub async fn assets_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<AssetsRow>, ServerFnError> {
pub use AssetsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, AssetsRow>(
r#"
SELECT
asset.state,
asset.county,
asset.township,
asset.land_range,
asset.section,
asset.portion_of_section,
survey.name AS survey_name,
asset.abstract_number,
asset.block,
asset.full_legal_description,
mineral_interest_type.name AS interest_type,
ROUND(SUM(1.0 * asset.net_mineral_acres), 2) AS area_share_nma,
(
SELECT GROUP_CONCAT(DISTINCT doc_type.name)
FROM document
LEFT JOIN doc_type ON document.doc_type_id = doc_type.id
WHERE document.asset_id = asset.asset_id
) AS conveyance_type,
(
SELECT GROUP_CONCAT(DISTINCT document.file_archive)
FROM document
WHERE document.asset_id = asset.asset_id
) AS document
FROM asset
LEFT JOIN mineral_interest_type ON asset.mineral_interest_type_id = mineral_interest_type.id
LEFT JOIN survey ON survey.id = asset.survey_id
LEFT JOIN client ON asset.client_id = client.id
WHERE client.canonical = ?
GROUP BY
asset.state,
asset.county,
asset.township,
asset.land_range,
asset.section,
asset.portion_of_section,
survey.name,
asset.abstract_number,
asset.block,
asset.full_legal_description,
mineral_interest_type.name
ORDER BY
asset.state, asset.county;
"#
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/assets/resource.rs
================
use super::query::AssetsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching asset-related data.
#[derive(Clone)]
pub(crate) struct AssetsResource {
pub revenue_rows: Resource<String, Result<Vec<AssetsRow>, ServerFnError>>,
}
use super::query::*;
impl AssetsResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
assets_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/dashboardjibs/mod.rs
================
pub mod query;
pub mod resource;
================
File: gmm_client/src/tables/dashboardjibs/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct DashboardJibsRow {
pub operator_name: String,
pub jib_amount: f64,
pub netted_amount: Option<f64>,
pub difference: Option<f64>,
pub month: String,
}
#[cfg(feature = "ssr")]
use sqlx::{SqlitePool, query_as};
#[cfg(feature = "ssr")]
pub async fn get_dashboard_jibs_data(
pool: &SqlitePool,
client_canonical: &str,
) -> Result<Vec<DashboardJibsRow>, leptos::ServerFnError> {
let query = r#"
WITH standardize_date AS (
SELECT
TRIM(pay_month) AS pay_month,
payor,
payee,
amount,
CASE
WHEN pay_month LIKE '____-__-__' THEN pay_month
WHEN pay_month LIKE '__/__/____' OR pay_month LIKE '_/__/____' OR pay_month LIKE '__/_/____' OR pay_month LIKE '_/_/____' THEN
CASE
WHEN LENGTH(pay_month) = 10 THEN strftime('%Y-%m-%d', substr(pay_month, 7, 4) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN LENGTH(pay_month) = 9 THEN strftime('%Y-%m-%d', substr(pay_month, 6, 4) || '-0' || substr(pay_month, 1, 1) || '-' || substr(pay_month, 3, 2))
WHEN LENGTH(pay_month) = 8 THEN strftime('%Y-%m-%d', substr(pay_month, 5, 4) || '-' || substr(pay_month, 1, 2) || '-0' || substr(pay_month, 4, 1))
ELSE strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-0' || substr(pay_month, 1, 1) || '-0' || substr(pay_month, 3, 1))
END
WHEN pay_month LIKE '___ __, ____' THEN strftime('%Y-%m-%d',
substr(pay_month, -4, 4) || '-' ||
CASE
WHEN substr(pay_month, 1, 3) = 'Jan' THEN '01'
WHEN substr(pay_month, 1, 3) = 'Feb' THEN '02'
WHEN substr(pay_month, 1, 3) = 'Mar' THEN '03'
WHEN substr(pay_month, 1, 3) = 'Apr' THEN '04'
WHEN substr(pay_month, 1, 3) = 'May' THEN '05'
WHEN substr(pay_month, 1, 3) = 'Jun' THEN '06'
WHEN substr(pay_month, 1, 3) = 'Jul' THEN '07'
WHEN substr(pay_month, 1, 3) = 'Aug' THEN '08'
WHEN substr(pay_month, 1, 3) = 'Sep' THEN '09'
WHEN substr(pay_month, 1, 3) = 'Oct' THEN '10'
WHEN substr(pay_month, 1, 3) = 'Nov' THEN '11'
WHEN substr(pay_month, 1, 3) = 'Dec' THEN '12'
ELSE '01'
END || '-' ||
printf('%02d', CAST(trim(substr(pay_month, 5, 2)) AS INTEGER))
)
WHEN pay_month LIKE '__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 4, 2) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__/__' THEN strftime('%Y-%m-%d', replace(pay_month, '/', '-'))
WHEN pay_month LIKE '____-__' THEN pay_month || '-15'
WHEN pay_month LIKE '__/__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 7, 2) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN pay_month LIKE '__-__-____' OR pay_month LIKE '_-__-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
printf('%02d', CAST(substr(pay_month, 1, instr(pay_month, '-') - 1) AS INTEGER)) || '-' ||
printf('%02d', CAST(substr(pay_month, instr(pay_month, '-') + 1, 2) AS INTEGER))
)
WHEN pay_month LIKE '__/____' OR pay_month LIKE '__-____' THEN
strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__' THEN
strftime('%Y-%m-%d', substr(pay_month, 1, 4) || '-' || substr(pay_month, 6, 2) || '-15')
WHEN pay_month LIKE '__-___-____' OR pay_month LIKE '_-___-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
CASE
WHEN INSTR(UPPER(pay_month), '-JAN-') > 0 THEN '01'
WHEN INSTR(UPPER(pay_month), '-FEB-') > 0 THEN '02'
WHEN INSTR(UPPER(pay_month), '-MAR-') > 0 THEN '03'
WHEN INSTR(UPPER(pay_month), '-APR-') > 0 THEN '04'
WHEN INSTR(UPPER(pay_month), '-MAY-') > 0 THEN '05'
WHEN INSTR(UPPER(pay_month), '-JUN-') > 0 THEN '06'
WHEN INSTR(UPPER(pay_month), '-JUL-') > 0 THEN '07'
WHEN INSTR(UPPER(pay_month), '-AUG-') > 0 THEN '08'
WHEN INSTR(UPPER(pay_month), '-SEP-') > 0 THEN '09'
WHEN INSTR(UPPER(pay_month), '-OCT-') > 0 THEN '10'
WHEN INSTR(UPPER(pay_month), '-NOV-') > 0 THEN '11'
WHEN INSTR(UPPER(pay_month), '-DEC-') > 0 THEN '12'
ELSE '01'
END || '-' ||
printf('%02d', CAST(substr(pay_month, 1, instr(pay_month, '-') - 1) AS INTEGER))
)
ELSE NULL
END AS normalized_date
FROM jibs
),
summary_jibs AS (
SELECT
payee,
STRFTIME('%Y-%m', normalized_date) AS normalized_month,
ROUND(SUM(1.0 * amount), 2) AS total_jib_amount
FROM standardize_date
WHERE payor = ?
GROUP BY payee, normalized_month
),
summary_revenue AS (
SELECT
operator_purchaser,
STRFTIME('%Y-%m', check_date) AS revenue_month,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net_revenue
FROM revenue_raw
WHERE owner_name_client = ?
AND interest_type = 'jib net'
GROUP BY operator_purchaser, revenue_month
)
SELECT
COALESCE(op.name, sj.payee) AS operator_name,
sj.total_jib_amount AS jib_amount,
sr.total_share_net_revenue AS netted_amount,
CASE
WHEN sr.total_share_net_revenue IS NOT NULL
AND ABS(sj.total_jib_amount + sr.total_share_net_revenue) < 0.01 THEN NULL
WHEN sr.total_share_net_revenue IS NULL THEN sj.total_jib_amount
ELSE ROUND(sj.total_jib_amount + sr.total_share_net_revenue, 2)
END AS difference,
sj.normalized_month AS month
FROM summary_jibs sj
LEFT JOIN summary_revenue sr
ON sj.payee = sr.operator_purchaser
AND sj.normalized_month = sr.revenue_month
LEFT JOIN operator op
ON LOWER(TRIM(op.canonical)) = LOWER(TRIM(sj.payee))
ORDER BY operator_name, month;
"#;
let rows = query_as::<_, DashboardJibsRow>(query)
.bind(client_canonical)
.bind(client_canonical)
.fetch_all(pool)
.await
.map_err(|e| leptos::ServerFnError::new(format!("Database error: {}", e)))?;
Ok(rows)
}
#[server]
pub async fn dashboardjibs_fetch_all(
client_canonical: String,
) -> Result<Vec<DashboardJibsRow>, ServerFnError> {
#[cfg(feature = "ssr")]
{
let pool = crate::state::ssr::app_pools()?.land_pool;
return get_dashboard_jibs_data(&pool, &client_canonical).await;
}
#[cfg(not(feature = "ssr"))]
{
Err(ServerFnError::new("This function is only available on the server."))
}
}
================
File: gmm_client/src/tables/dashboardjibs/resource.rs
================
use leptos::*;
use crate::client_main_app::ctx::*;
use crate::tables::dashboardjibs::query::{DashboardJibsRow, dashboardjibs_fetch_all};
/// Leptos resource for fetching JIB vs revenue netting for dashboard display.
#[derive(Clone)]
pub(crate) struct DashboardJibsResource {
pub rows: Resource<String, Result<Vec<DashboardJibsRow>, ServerFnError>>,
}
impl DashboardJibsResource {
pub fn new() -> Self {
let rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
dashboardjibs_fetch_all(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/dashboardrevenue/mod.rs
================
pub mod query;
pub mod resource;
================
File: gmm_client/src/tables/dashboardrevenue/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct DashboardRevenueRow {
pub operator_purchaser: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_amount: Option<f64>,
pub payment_date: String,
}
#[cfg(feature = "ssr")]
use sqlx::{SqlitePool, query_as};
#[cfg(feature = "ssr")]
pub async fn get_dashboard_revenue_data(
pool: &SqlitePool,
client_canonical: &str,
) -> Result<Vec<DashboardRevenueRow>, leptos::ServerFnError> {
let query = r#"
SELECT
operator_purchaser,
SUM(1.0 * share_gross_revenue) AS gross_revenue,
SUM(share_taxes) AS taxes,
SUM(share_deductions) AS deductions,
SUM(1.0 * share_net_revenue) AS net_revenue,
1.0 * share_check_amount AS payment_amount,
check_date AS payment_date
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('e', 'h')
AND check_date >= date('now', '-1 month')
AND check_date < date('now')
GROUP BY
operator_purchaser,
check_date,
share_check_amount,
check_reference_number
ORDER BY
operator_purchaser ASC,
check_date ASC;
"#;
let rows = query_as::<_, DashboardRevenueRow>(query)
.bind(client_canonical)
.fetch_all(pool)
.await
.map_err(|e| leptos::ServerFnError::new(format!("Database error: {}", e)))?;
Ok(rows)
}
#[server]
pub async fn dashboardrevenue_fetch_all(
client_canonical: String,
) -> Result<Vec<DashboardRevenueRow>, ServerFnError> {
#[cfg(feature = "ssr")]
{
let pool = crate::state::ssr::app_pools()?.land_pool;
return crate::tables::dashboardrevenue::query::get_dashboard_revenue_data(&pool, &client_canonical).await;
}
#[cfg(not(feature = "ssr"))]
{
Err(ServerFnError::new("This function is only available on the server."))
}
}
================
File: gmm_client/src/tables/dashboardrevenue/resource.rs
================
use leptos::*;
use crate::client_main_app::ctx::*;
use crate::tables::dashboardrevenue::query::{DashboardRevenueRow, dashboardrevenue_fetch_all};
/// Leptos resource for fetching recent dashboard revenue data.
#[derive(Clone)]
pub(crate) struct DashboardRevenueResource {
pub rows: Resource<String, Result<Vec<DashboardRevenueRow>, ServerFnError>>,
}
impl DashboardRevenueResource {
pub fn new() -> Self {
let rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
dashboardrevenue_fetch_all(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/dashboardtotalreportedpayments/mod.rs
================
pub mod query;
pub mod resource;
================
File: gmm_client/src/tables/dashboardtotalreportedpayments/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct DashboardTotalReportedPaymentsRow {
pub total: f64,
pub count: u8,
pub year_month: String,
}
#[cfg(feature = "ssr")]
use sqlx::{SqlitePool, query_as};
#[cfg(feature = "ssr")]
pub async fn get_dashboard_total_reported_payments(
pool: &SqlitePool,
client_canonical: &str,
) -> Result<DashboardTotalReportedPaymentsRow, leptos::ServerFnError> {
let query = r#"
SELECT
SUM(1.0 * share_check_amount) * 1.0 AS total,
COUNT(*) AS count,
strftime('%Y-%m', 'now', '-1 month') AS year_month
FROM (
SELECT DISTINCT operator_purchaser, check_date, share_check_amount
FROM revenue_raw
WHERE
owner_name_client = ? AND
check_date >= date('now', '-1 month', 'start of month') AND
check_date < date('now', 'start of month')
);
"#;
let row = query_as::<_, DashboardTotalReportedPaymentsRow>(query)
.bind(client_canonical)
.fetch_one(pool)
.await
.map_err(|e| leptos::ServerFnError::new(format!("Database error: {}", e)))?;
Ok(row)
}
#[server]
pub async fn dashboard_total_reported_payments_fetch(
client_canonical: String,
) -> Result<DashboardTotalReportedPaymentsRow, ServerFnError> {
#[cfg(feature = "ssr")]
{
let pool = crate::state::ssr::app_pools()?.land_pool;
crate::tables::dashboardtotalreportedpayments::query::get_dashboard_total_reported_payments(&pool, &client_canonical).await
}
#[cfg(not(feature = "ssr"))]
{
Err(ServerFnError::new("This function is only available on the server."))
}
}
================
File: gmm_client/src/tables/dashboardtotalreportedpayments/resource.rs
================
use leptos::*;
use crate::client_main_app::ctx::*;
use crate::tables::dashboardtotalreportedpayments::query::{DashboardTotalReportedPaymentsRow, dashboard_total_reported_payments_fetch};
/// Leptos resource for fetching recent dashboard revenue data.
#[derive(Clone)]
pub(crate) struct DashboardTotalReportedPaymentsResource {
pub row: Resource<String, Result<DashboardTotalReportedPaymentsRow, ServerFnError>>,
}
impl DashboardTotalReportedPaymentsResource {
pub fn new() -> Self {
let row = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
dashboard_total_reported_payments_fetch(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { row }
}
}
================
File: gmm_client/src/tables/incominglocation/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/incominglocation/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the incoming location report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct IncomingLocationRow {
pub owner_name_client: String,
pub state: Option<String>,
pub county: Option<String>,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_date: Option<String>,
}
/// Fetches incoming location data from `revenue_raw` by `canonical_name`.
#[server]
pub async fn incominglocation_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<IncomingLocationRow>, ServerFnError> {
pub use IncomingLocationRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, IncomingLocationRow>(
"SELECT
owner_name_client,
state,
county,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(share_taxes), 2) AS taxes,
ROUND(SUM(share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS net_revenue,
strftime('%Y-%m', check_date) AS payment_date
FROM
revenue_raw
WHERE
aggregation_level IN ('i', 'e', 'h')
AND
owner_name_client = ?
GROUP BY
state,
county,
strftime('%Y-%m', check_date)
ORDER BY
payment_date ASC,
state ASC,
county ASC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/incominglocation/resource.rs
================
use super::query::IncomingLocationRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching incoming location data.
#[derive(Clone)]
pub(crate) struct IncomingLocationResource {
pub revenue_rows: Resource<String, Result<Vec<IncomingLocationRow>, ServerFnError>>,
}
use super::query::*;
impl IncomingLocationResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
incominglocation_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/incomingproducts/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/incomingproducts/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the incoming products report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct IncomingProductsRow {
pub owner_name_client: String,
pub product: Option<String>,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_date: Option<String>,
}
/// Fetches incoming products data from `revenue_raw` by `canonical_name`.
#[server]
pub async fn incomingproducts_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<IncomingProductsRow>, ServerFnError> {
pub use IncomingProductsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, IncomingProductsRow>(
"SELECT
owner_name_client,
product,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(share_taxes), 2) AS taxes,
ROUND(SUM(share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS net_revenue,
strftime('%Y-%m', check_date) AS payment_date
FROM
revenue_raw
WHERE
aggregation_level IN ('i', 'e', 'h')
AND
owner_name_client = ?
GROUP BY
product,
strftime('%Y-%m', check_date)
ORDER BY
payment_date ASC,
product ASC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/incomingproducts/resource.rs
================
use super::query::IncomingProductsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching incoming products data.
#[derive(Clone)]
pub(crate) struct IncomingProductsResource {
pub revenue_rows: Resource<String, Result<Vec<IncomingProductsRow>, ServerFnError>>,
}
use super::query::*;
impl IncomingProductsResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
incomingproducts_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/incomingtable1/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/incomingtable1/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the incoming table report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct IncomingTable1Row {
pub owner_name_client: String,
pub operator_name: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_amount: Option<f64>,
pub payment_date: Option<String>,
pub depletion_15: Option<f64>,
pub net_post_depletion_15: Option<f64>,
pub depletion_275: Option<f64>,
pub net_post_depletion_275: Option<f64>,
}
/// Fetches incoming table data from `revenue_raw` by `canonical_name`.
#[server]
pub async fn incomingtable1_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<IncomingTable1Row>, ServerFnError> {
pub use IncomingTable1Row;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, IncomingTable1Row>(
"SELECT
rr.owner_name_client,
o.name AS operator_name,
ROUND(SUM(1.0 * rr.share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(rr.share_taxes), 2) AS taxes,
ROUND(SUM(rr.share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * rr.share_net_revenue), 2) AS net_revenue,
1.0 * rr.share_check_amount AS payment_amount,
rr.check_date AS payment_date,
ROUND(SUM(rr.share_gross_revenue * 0.15), 2) AS depletion_15,
ROUND(SUM(rr.share_gross_revenue - (rr.share_gross_revenue * 0.15)), 2) AS net_post_depletion_15,
ROUND(SUM(rr.share_gross_revenue * 0.275), 2) AS depletion_275,
ROUND(SUM(rr.share_gross_revenue - (rr.share_gross_revenue * 0.275)), 2) AS net_post_depletion_275
FROM
revenue_raw rr
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.aggregation_level = 'i'
AND rr.owner_name_client = ?
GROUP BY
rr.owner_name_client,
o.name,
rr.check_date,
rr.share_check_amount,
rr.check_reference_number
ORDER BY
operator_name ASC,
rr.check_date ASC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/incomingtable1/resource.rs
================
use super::query::IncomingTable1Row;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching incoming table data.
#[derive(Clone)]
pub(crate) struct IncomingTable1Resource {
pub revenue_rows: Resource<String, Result<Vec<IncomingTable1Row>, ServerFnError>>,
}
use super::query::*;
impl IncomingTable1Resource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
incomingtable1_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/incomingtable2/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/incomingtable2/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct IncomingTable2Row {
pub owner_name_client: String,
pub operator_name: String,
pub description: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_amount: Option<f64>,
pub payment_date: Option<String>,
}
#[server]
pub async fn incomingtable2_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<IncomingTable2Row>, ServerFnError> {
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, IncomingTable2Row>(
"SELECT
rr.owner_name_client,
o.name AS operator_name,
rr.well_property_name AS description,
ROUND(SUM(1.0 * rr.share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(rr.share_taxes), 2) AS taxes,
ROUND(SUM(rr.share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * rr.share_net_revenue), 2) AS net_revenue,
1.0 * rr.share_check_amount AS payment_amount,
rr.check_date AS payment_date
FROM
revenue_raw rr
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.aggregation_level IN ('e', 'h')
AND rr.owner_name_client = ?
GROUP BY
o.name,
rr.well_property_name,
rr.check_date,
rr.share_check_amount,
rr.check_reference_number
ORDER BY
operator_name ASC,
rr.check_date ASC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/incomingtable2/resource.rs
================
use super::query::IncomingTable2Row;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching incoming table 2 data.
#[derive(Clone)]
pub(crate) struct IncomingTable2Resource {
pub rows: Resource<String, Result<Vec<IncomingTable2Row>, ServerFnError>>,
}
use super::query::*;
impl IncomingTable2Resource {
pub fn new() -> Self {
let rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
incomingtable2_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/iwv_qc_queries/incomingtables/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/iwv_qc_queries/incomingtables/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the payment discrepancy report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PaymentDiscrepancyRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_amount: Option<f64>,
pub difference: Option<f64>,
pub payment_date: Option<String>,
pub issues: Option<String>,
}
/// Fetches payment discrepancy data by canonical name.
#[server]
pub async fn paymentdiscrepancy_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PaymentDiscrepancyRow>, ServerFnError> {
pub use PaymentDiscrepancyRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
SELECT
owner_name_client,
operator_purchaser,
gross_revenue,
taxes,
deductions,
net_revenue,
payment_amount,
ABS(net_revenue - payment_amount) AS difference,
payment_date,
CASE
WHEN owner_name_client = 'paul.c.bundy.interests.llc'
AND operator_purchaser = 'own.resources'
THEN 'known discrepancy from operator'
WHEN net_revenue = 0 THEN 'no net revenue provided by operator'
WHEN ROUND(ABS(net_revenue - payment_amount), 1) = ROUND(payment_amount, 1)
THEN 'possible duplicate'
ELSE NULL
END AS issues
FROM (
SELECT
owner_name_client,
operator_purchaser,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS net_revenue,
1.0 * share_check_amount AS payment_amount,
check_date AS payment_date
FROM
revenue_raw
WHERE
aggregation_level IN ('i', 'e', 'h')
AND
owner_name_client = ?
GROUP BY
operator_purchaser,
check_date,
share_check_amount,
check_reference_number
) AS aggregated_results
WHERE
ABS(net_revenue - payment_amount) > 0.01
ORDER BY
operator_purchaser,
payment_date DESC
"#;
let records = sqlx::query_as::<_, PaymentDiscrepancyRow>(query)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/iwv_qc_queries/incomingtables/resource.rs
================
use leptos::*;
use super::query::PaymentDiscrepancyRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching payment discrepancy data.
#[derive(Clone)]
pub(crate) struct PaymentDiscrepancyResource {
pub rows: Resource<String, Result<Vec<PaymentDiscrepancyRow>, ServerFnError>>,
}
use super::query::*;
impl PaymentDiscrepancyResource {
pub fn new() -> Self {
let rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
paymentdiscrepancy_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
}
);
Self { rows }
}
}
================
File: gmm_client/src/tables/iwv_qc_queries/taxyearrev1099/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/iwv_qc_queries/taxyearrev1099/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
use sqlx::FromRow;
use sqlx::query_as;
/// Represents a row in the Tax Year Revenue vs 1099 comparison report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(FromRow))]
pub struct TaxYearRev1099Row {
pub client: String,
pub operator: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deducts: Option<f64>,
pub net_revenue: Option<f64>,
pub amount_1099: Option<f64>,
pub difference_dollars: Option<f64>,
pub difference_percent: Option<f64>,
pub material_difference: Option<String>,
}
/// Fetches the tax year revenue and 1099 comparison data.
#[server]
pub async fn taxyearrev1099_fetch_all() -> Result<Vec<TaxYearRev1099Row>, ServerFnError> {
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
SELECT
client,
operator,
ROUND(share_gross_revenue, 2) AS gross_revenue,
ROUND(share_taxes, 2) AS taxes,
ROUND(share_deductions, 2) AS deducts,
ROUND(share_net_revenue, 2) AS net_revenue,
ROUND(reported_amount, 2) AS amount_1099,
ROUND(difference_dollars, 2) AS difference_dollars,
CASE
WHEN ROUND(SUM(difference_dollars)) IS NULL OR ROUND(SUM(difference_dollars)) = 0 THEN NULL
ELSE ROUND(
CASE
WHEN share_net_revenue = 0 THEN NULL
ELSE ROUND((difference_dollars * 100.0) / share_net_revenue, 2)
END, 2
)
END AS difference_percent,
CASE
WHEN ROUND(SUM(difference_dollars)) = 0 THEN NULL
ELSE CASE
WHEN SUM(reported_amount) IS NULL OR SUM(reported_amount) = 0 THEN 'Outstanding 1099'
WHEN (share_gross_revenue IS NULL OR share_gross_revenue = 0)
AND (share_taxes IS NULL OR share_taxes = 0)
AND (share_deductions IS NULL OR share_deductions = 0)
AND (share_net_revenue IS NULL OR share_net_revenue = 0) THEN 'Outstanding Revenue'
WHEN share_gross_revenue = SUM(reported_amount) OR share_net_revenue = SUM(reported_amount) THEN ''
WHEN (ABS(SUM(difference_dollars)) * 100.0) / NULLIF(share_net_revenue, 0) < 25 THEN 'No'
ELSE 'Yes'
END
END AS material_difference
FROM (
SELECT * FROM (
SELECT
COALESCE(rr.payee, i.payee) AS client,
CASE
WHEN rr.share_net_revenue < 600 THEN COALESCE(rr.operator_purchaser, i.operator) || '*'
ELSE COALESCE(rr.operator_purchaser, i.operator)
END AS operator,
1.0 * rr.share_gross_revenue AS share_gross_revenue,
1.0 * rr.share_taxes AS share_taxes,
1.0 * rr.share_deductions AS share_deductions,
1.0 * rr.share_net_revenue AS share_net_revenue,
ROUND(SUM(1.0 * i.reported_amount), 2) AS reported_amount,
ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) AS difference_dollars,
CASE
WHEN 1.0 * rr.share_net_revenue = 0 THEN NULL
ELSE (ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) * 100.0) / (1.0 * rr.share_net_revenue)
END AS difference_percent,
1 AS source_table
FROM
(SELECT DISTINCT
payee,
payor AS operator,
1.0 * REPLACE(REPLACE(reported_amount, '$', ''), ',', '') AS reported_amount,
tax_year
FROM items1099
WHERE
payee = ?
AND tax_year = 2024
AND box_name NOT LIKE '%withheld%'
AND box_number NOT LIKE 'nil'
) AS i
LEFT JOIN
(SELECT
owner_name_client AS payee,
operator_purchaser,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS share_gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS share_taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS share_deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS share_net_revenue,
strftime('%Y', check_date), 2) AS year
FROM revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND strftime('%Y', check_date) = '2024'
GROUP BY owner_name_client, operator_purchaser, year
) AS rr
ON rr.payee = i.payee AND rr.operator_purchaser = i.operator
GROUP BY client, operator, share_gross_revenue, share_taxes, share_deductions, share_net_revenue
UNION ALL
SELECT
COALESCE(rr.payee, i.payee) AS client,
CASE
WHEN rr.share_net_revenue < 600 THEN COALESCE(rr.operator_purchaser, i.operator) || '*'
ELSE COALESCE(rr.operator_purchaser, i.operator)
END AS operator,
1.0 * rr.share_gross_revenue AS share_gross_revenue,
1.0 * rr.share_taxes AS share_taxes,
1.0 * rr.share_deductions AS share_deductions,
1.0 * rr.share_net_revenue AS share_net_revenue,
ROUND(SUM(1.0 * i.reported_amount), 2) AS reported_amount,
ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) AS difference_dollars,
CASE
WHEN 1.0 * rr.share_net_revenue = 0 THEN NULL
ELSE (ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) * 100.0) / (1.0 * rr.share_net_revenue)
END AS difference_percent,
2 AS source_table
FROM
(SELECT
payee,
payor AS operator,
1.0 * REPLACE(REPLACE(reported_amount, '$', ''), ',', '') AS reported_amount,
tax_year
FROM items1099
WHERE
payee = ?
AND tax_year = 2024
AND box_name NOT LIKE '%withheld%'
AND box_number NOT LIKE 'nil'
) AS i
RIGHT JOIN
(SELECT
owner_name_client AS payee,
operator_purchaser,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS share_gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS share_taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS share_deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS share_net_revenue,
strftime('%Y', check_date), 2) AS year
FROM revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND strftime('%Y', check_date) = '2024'
GROUP BY owner_name_client, operator_purchaser, year
) AS rr
ON rr.payee = i.payee AND rr.operator_purchaser = i.operator
GROUP BY client, operator, share_gross_revenue, share_taxes, share_deductions, share_net_revenue
) AS combined_data_with_dupes
GROUP BY client, operator, source_table
) AS combined_data
GROUP BY client, operator, share_gross_revenue, share_taxes, share_deductions, share_net_revenue
HAVING ROUND(COALESCE(difference_dollars, 0), 2) != 0
AND difference_dollars IS NOT NULL;
"#;
let records = query_as::<_, TaxYearRev1099Row>(query)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/iwv_qc_queries/taxyearrev1099/resource.rs
================
use leptos::*;
use super::query::TaxYearRev1099Row;
use crate::tables::iwv_qc_queries::taxyearrev1099::query::taxyearrev1099_fetch_all;
/// Leptos resource for fetching tax year revenue vs 1099 data.
#[derive(Clone)]
pub(crate) struct TaxYearRev1099Resource {
pub taxyear_rows: Resource<(), Result<Vec<TaxYearRev1099Row>, ServerFnError>>,
}
impl TaxYearRev1099Resource {
pub fn new() -> Self {
let taxyear_rows = create_resource(
|| (), // No canonical_name needed because query is fetch_all
|_| async move {
taxyearrev1099_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { taxyear_rows }
}
}
================
File: gmm_client/src/tables/iwv_qc_queries/mod.rs
================
pub(crate) mod incomingtables;
// pub(crate) mod taxyearrev1099;
================
File: gmm_client/src/tables/jibdetails/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/jibdetails/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the JIB details report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct JibDetailsRow {
pub payor: String,
pub payee: String,
pub payment_date: Option<String>,
pub invoice_number: Option<String>,
pub cost_center: Option<String>,
pub venture_number: Option<String>,
pub description: Option<String>,
pub total_amount: Option<f64>,
}
/// Fetches JIB detail data from `jibs` table.
#[server]
pub async fn jibdetails_fetch_by_canonical_name(
_canonical_name: String,
) -> Result<Vec<JibDetailsRow>, ServerFnError> {
pub use JibDetailsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, JibDetailsRow>(
"WITH jibs_with_date AS (
SELECT
payor,
payee,
CASE
WHEN TRIM(pay_month) LIKE '____-__-__' THEN TRIM(pay_month)
WHEN TRIM(pay_month) LIKE '__/__/____' OR TRIM(pay_month) LIKE '_/__/____' OR TRIM(pay_month) LIKE '__/_/____' OR TRIM(pay_month) LIKE '_/_/____' THEN
CASE
WHEN length(TRIM(pay_month)) = 10 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 7, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-' || substr(TRIM(pay_month), 4, 2))
WHEN length(TRIM(pay_month)) = 9 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 6, 4) || '-0' || substr(TRIM(pay_month), 1, 1) || '-' || substr(TRIM(pay_month), 3, 2))
WHEN length(TRIM(pay_month)) = 8 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 5, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-0' || substr(TRIM(pay_month), 4, 1))
ELSE strftime('%Y-%m-%d', substr(TRIM(pay_month), 4, 4) || '-0' || substr(TRIM(pay_month), 1, 1) || '-0' || substr(TRIM(pay_month), 3, 1))
END
WHEN TRIM(pay_month) LIKE '___ __, ____' THEN strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4, 4) || '-' ||
CASE
WHEN substr(TRIM(pay_month), 1, 3) = 'Jan' THEN '01'
WHEN substr(TRIM(pay_month), 1, 3) = 'Feb' THEN '02'
WHEN substr(TRIM(pay_month), 1, 3) = 'Mar' THEN '03'
WHEN substr(TRIM(pay_month), 1, 3) = 'Apr' THEN '04'
WHEN substr(TRIM(pay_month), 1, 3) = 'May' THEN '05'
WHEN substr(TRIM(pay_month), 1, 3) = 'Jun' THEN '06'
WHEN substr(TRIM(pay_month), 1, 3) = 'Jul' THEN '07'
WHEN substr(TRIM(pay_month), 1, 3) = 'Aug' THEN '08'
WHEN substr(TRIM(pay_month), 1, 3) = 'Sep' THEN '09'
WHEN substr(TRIM(pay_month), 1, 3) = 'Oct' THEN '10'
WHEN substr(TRIM(pay_month), 1, 3) = 'Nov' THEN '11'
WHEN substr(TRIM(pay_month), 1, 3) = 'Dec' THEN '12'
END || '-' ||
CASE
WHEN length(trim(substr(TRIM(pay_month), 5, 2))) = 1 THEN '0' || substr(TRIM(pay_month), 5, 1)
ELSE substr(TRIM(pay_month), 5, 2)
END
)
WHEN TRIM(pay_month) LIKE '__/__' THEN strftime('%Y-%m-%d', '20' || substr(TRIM(pay_month), 4, 2) || '-' || substr(TRIM(pay_month), 1, 2) || '-15')
WHEN TRIM(pay_month) LIKE '____/__/__' THEN strftime('%Y-%m-%d', replace(TRIM(pay_month), '/', '-'))
WHEN TRIM(pay_month) LIKE '% %' AND LENGTH(TRIM(pay_month)) > 5 THEN
CASE
WHEN INSTR(UPPER(TRIM(pay_month)), 'JAN') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-01-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'FEB') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-02-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'MAR') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-03-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'APR') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-04-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'MAY') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-05-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'JUN') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-06-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'JUL') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-07-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'AUG') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-08-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'SEP') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-09-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'OCT') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-10-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'NOV') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-11-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'DEC') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-12-15'
END
WHEN TRIM(pay_month) LIKE '____-__' THEN TRIM(pay_month) || '-15'
WHEN TRIM(pay_month) LIKE '__/__/__' THEN strftime('%Y-%m-%d', '20' || substr(TRIM(pay_month), 7, 2) || '-' || substr(TRIM(pay_month), 1, 2) || '-' || substr(TRIM(pay_month), 4, 2))
WHEN TRIM(pay_month) LIKE '__-__-____' OR TRIM(pay_month) LIKE '_-__-____' THEN
strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4) || '-' ||
CASE WHEN length(TRIM(pay_month)) = 10 THEN substr(TRIM(pay_month), 1, 2) ELSE '0' || substr(TRIM(pay_month), 1, 1) END || '-' ||
CASE WHEN length(TRIM(pay_month)) = 10 THEN substr(TRIM(pay_month), 4, 2) ELSE substr(TRIM(pay_month), 3, 2) END
)
WHEN TRIM(pay_month) LIKE '__/____' OR TRIM(pay_month) LIKE '__-____' THEN
strftime('%Y-%m-%d', substr(TRIM(pay_month), 4, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-15')
WHEN TRIM(pay_month) LIKE '____/__' THEN
strftime('%Y-%m-%d', substr(TRIM(pay_month), 1, 4) || '-' || substr(TRIM(pay_month), 6, 2) || '-15')
WHEN TRIM(pay_month) LIKE '__-___-____' OR TRIM(pay_month) LIKE '_-___-____' THEN
strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4) || '-' ||
CASE
WHEN INSTR(UPPER(TRIM(pay_month)), '-JAN-') > 0 THEN '01'
WHEN INSTR(UPPER(TRIM(pay_month)), '-FEB-') > 0 THEN '02'
WHEN INSTR(UPPER(TRIM(pay_month)), '-MAR-') > 0 THEN '03'
WHEN INSTR(UPPER(TRIM(pay_month)), '-APR-') > 0 THEN '04'
WHEN INSTR(UPPER(TRIM(pay_month)), '-MAY-') > 0 THEN '05'
WHEN INSTR(UPPER(TRIM(pay_month)), '-JUN-') > 0 THEN '06'
WHEN INSTR(UPPER(TRIM(pay_month)), '-JUL-') > 0 THEN '07'
WHEN INSTR(UPPER(TRIM(pay_month)), '-AUG-') > 0 THEN '08'
WHEN INSTR(UPPER(TRIM(pay_month)), '-SEP-') > 0 THEN '09'
WHEN INSTR(UPPER(TRIM(pay_month)), '-OCT-') > 0 THEN '10'
WHEN INSTR(UPPER(TRIM(pay_month)), '-NOV-') > 0 THEN '11'
WHEN INSTR(UPPER(TRIM(pay_month)), '-DEC-') > 0 THEN '12'
END || '-' ||
CASE
WHEN length(TRIM(pay_month)) = 11 THEN substr(TRIM(pay_month), 1, 2)
ELSE '0' || substr(TRIM(pay_month), 1, 1)
END
)
END AS payment_date,
invoice_number,
cost_center,
venture_number,
description,
amount
FROM jibs
)
SELECT
o.name AS operator_name,
j.payment_date,
j.invoice_number,
j.cost_center,
j.venture_number,
j.description,
SUM(1.0 * j.amount) AS total_amount
FROM jibs_with_date j
JOIN operator o ON o.canonical = j.payee
WHERE j.payor = ?
AND j.payment_date IS NOT NULL
GROUP BY
operator_name, j.payment_date, j.invoice_number, j.cost_center, j.venture_number, j.description"
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/jibdetails/resource.rs
================
use super::query::JibDetailsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching JIB details data.
#[derive(Clone)]
pub(crate) struct JibDetailsResource {
pub revenue_rows: Resource<String, Result<Vec<JibDetailsRow>, ServerFnError>>,
}
use super::query::*;
impl JibDetailsResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
jibdetails_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/jibnetting/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/jibnetting/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the JIB netting report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct JibNettingRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub check_date: Option<String>,
pub share_net_revenue: Option<f64>,
}
/// Fetches JIB netting data from `revenue_raw` for interest_type = 'jib net'.
#[server]
pub async fn jibnetting_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<JibNettingRow>, ServerFnError> {
use JibNettingRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, JibNettingRow>(
"SELECT
rr.owner_name_client,
o.name AS operator_name,
rr.check_date,
1.0 * rr.share_net_revenue AS share_net_revenue
FROM
revenue_raw rr
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.owner_name_client = ?
AND rr.interest_type = 'jib net'
ORDER BY
rr.check_date DESC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/jibnetting/resource.rs
================
use super::query::JibNettingRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching JIB netting data.
#[derive(Clone)]
pub(crate) struct JibNettingResource {
pub revenue_rows: Resource<String, Result<Vec<JibNettingRow>, ServerFnError>>,
}
use super::query::*;
impl JibNettingResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
jibnetting_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/jibsummary/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/jibsummary/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the JIB summary report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct JibSummaryRow {
pub payor: String,
pub payment_date: Option<String>,
pub invoice_number: Option<String>,
pub total_amount: Option<f64>,
}
/// Fetches JIB summary data from `jibs`.
#[server]
pub async fn jibsummary_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<JibSummaryRow>, ServerFnError> {
pub use JibSummaryRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, JibSummaryRow>(
"WITH jibs_with_date AS (
SELECT
payor,
payee,
CASE
WHEN TRIM(pay_month) LIKE '____-__-__' THEN TRIM(pay_month)
WHEN TRIM(pay_month) LIKE '__/__/____' OR TRIM(pay_month) LIKE '_/__/____' OR TRIM(pay_month) LIKE '__/_/____' OR TRIM(pay_month) LIKE '_/_/____' THEN
CASE
WHEN length(TRIM(pay_month)) = 10 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 7, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-' || substr(TRIM(pay_month), 4, 2))
WHEN length(TRIM(pay_month)) = 9 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 6, 4) || '-0' || substr(TRIM(pay_month), 1, 1) || '-' || substr(TRIM(pay_month), 3, 2))
WHEN length(TRIM(pay_month)) = 8 THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 5, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-0' || substr(TRIM(pay_month), 4, 1))
ELSE strftime('%Y-%m-%d', substr(TRIM(pay_month), 4, 4) || '-0' || substr(TRIM(pay_month), 1, 1) || '-0' || substr(TRIM(pay_month), 3, 1))
END
WHEN TRIM(pay_month) LIKE '___ __, ____' THEN strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4, 4) || '-' ||
CASE
WHEN substr(TRIM(pay_month), 1, 3) = 'Jan' THEN '01'
WHEN substr(TRIM(pay_month), 1, 3) = 'Feb' THEN '02'
WHEN substr(TRIM(pay_month), 1, 3) = 'Mar' THEN '03'
WHEN substr(TRIM(pay_month), 1, 3) = 'Apr' THEN '04'
WHEN substr(TRIM(pay_month), 1, 3) = 'May' THEN '05'
WHEN substr(TRIM(pay_month), 1, 3) = 'Jun' THEN '06'
WHEN substr(TRIM(pay_month), 1, 3) = 'Jul' THEN '07'
WHEN substr(TRIM(pay_month), 1, 3) = 'Aug' THEN '08'
WHEN substr(TRIM(pay_month), 1, 3) = 'Sep' THEN '09'
WHEN substr(TRIM(pay_month), 1, 3) = 'Oct' THEN '10'
WHEN substr(TRIM(pay_month), 1, 3) = 'Nov' THEN '11'
WHEN substr(TRIM(pay_month), 1, 3) = 'Dec' THEN '12'
END || '-' ||
CASE
WHEN length(trim(substr(TRIM(pay_month), 5, 2))) = 1 THEN '0' || substr(TRIM(pay_month), 5, 1)
ELSE substr(TRIM(pay_month), 5, 2)
END
)
WHEN TRIM(pay_month) LIKE '__/__' THEN strftime('%Y-%m-%d', '20' || substr(TRIM(pay_month), 4, 2) || '-' || substr(TRIM(pay_month), 1, 2) || '-15')
WHEN TRIM(pay_month) LIKE '____/__/__' THEN strftime('%Y-%m-%d', replace(TRIM(pay_month), '/', '-'))
WHEN TRIM(pay_month) LIKE '% %' AND LENGTH(TRIM(pay_month)) > 5 THEN
CASE
WHEN INSTR(UPPER(TRIM(pay_month)), 'JAN') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-01-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'FEB') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-02-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'MAR') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-03-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'APR') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-04-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'MAY') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-05-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'JUN') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-06-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'JUL') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-07-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'AUG') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-08-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'SEP') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-09-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'OCT') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-10-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'NOV') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-11-15'
WHEN INSTR(UPPER(TRIM(pay_month)), 'DEC') > 0 THEN SUBSTR(TRIM(pay_month), -4) || '-12-15'
END
WHEN TRIM(pay_month) LIKE '____-__' THEN TRIM(pay_month) || '-15'
WHEN TRIM(pay_month) LIKE '__/__/__' THEN strftime('%Y-%m-%d', '20' || substr(TRIM(pay_month), 7, 2) || '-' || substr(TRIM(pay_month), 1, 2) || '-' || substr(TRIM(pay_month), 4, 2))
WHEN TRIM(pay_month) LIKE '__-__-____' OR TRIM(pay_month) LIKE '_-__-____' THEN
strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4) || '-' ||
CASE WHEN length(TRIM(pay_month)) = 10 THEN substr(TRIM(pay_month), 1, 2) ELSE '0' || substr(TRIM(pay_month), 1, 1) END || '-' ||
CASE WHEN length(TRIM(pay_month)) = 10 THEN substr(TRIM(pay_month), 4, 2) ELSE substr(TRIM(pay_month), 3, 2) END
)
WHEN TRIM(pay_month) LIKE '__/____' OR TRIM(pay_month) LIKE '__-____' THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 4, 4) || '-' || substr(TRIM(pay_month), 1, 2) || '-15')
WHEN TRIM(pay_month) LIKE '____/__' THEN strftime('%Y-%m-%d', substr(TRIM(pay_month), 1, 4) || '-' || substr(TRIM(pay_month), 6, 2) || '-15')
WHEN TRIM(pay_month) LIKE '__-___-____' OR TRIM(pay_month) LIKE '_-___-____' THEN
strftime('%Y-%m-%d',
substr(TRIM(pay_month), -4) || '-' ||
CASE
WHEN INSTR(UPPER(TRIM(pay_month)), '-JAN-') > 0 THEN '01'
WHEN INSTR(UPPER(TRIM(pay_month)), '-FEB-') > 0 THEN '02'
WHEN INSTR(UPPER(TRIM(pay_month)), '-MAR-') > 0 THEN '03'
WHEN INSTR(UPPER(TRIM(pay_month)), '-APR-') > 0 THEN '04'
WHEN INSTR(UPPER(TRIM(pay_month)), '-MAY-') > 0 THEN '05'
WHEN INSTR(UPPER(TRIM(pay_month)), '-JUN-') > 0 THEN '06'
WHEN INSTR(UPPER(TRIM(pay_month)), '-JUL-') > 0 THEN '07'
WHEN INSTR(UPPER(TRIM(pay_month)), '-AUG-') > 0 THEN '08'
WHEN INSTR(UPPER(TRIM(pay_month)), '-SEP-') > 0 THEN '09'
WHEN INSTR(UPPER(TRIM(pay_month)), '-OCT-') > 0 THEN '10'
WHEN INSTR(UPPER(TRIM(pay_month)), '-NOV-') > 0 THEN '11'
WHEN INSTR(UPPER(TRIM(pay_month)), '-DEC-') > 0 THEN '12'
END || '-' ||
CASE
WHEN length(TRIM(pay_month)) = 11 THEN substr(TRIM(pay_month), 1, 2)
ELSE '0' || substr(TRIM(pay_month), 1, 1)
END
)
END AS payment_date,
invoice_number,
amount
FROM jibs
)
SELECT
o.name AS operator_name,
j.payment_date,
j.invoice_number,
ROUND(SUM(1.0 * j.amount), 2) AS total_amount
FROM jibs_with_date j
JOIN operator o ON o.canonical = j.payor
WHERE j.payor = ?
AND j.payment_date IS NOT NULL
GROUP BY
operator_name, j.payment_date, j.invoice_number"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/jibsummary/resource.rs
================
use super::query::JibSummaryRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching JIB summary data.
#[derive(Clone)]
pub(crate) struct JibSummaryResource {
pub revenue_rows: Resource<String, Result<Vec<JibSummaryRow>, ServerFnError>>,
}
use super::query::*;
impl JibSummaryResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
jibsummary_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/leases/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/leases/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the leases report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct LeasesRow {
pub client: String,
pub lessee: String,
pub effective: Option<String>,
pub expiration: Option<String>,
pub term: Option<i64>,
pub royalty: Option<f64>,
pub lease_type: Option<String>,
pub status: Option<String>,
}
/// Fetches lease data by client canonical name.
#[server]
pub async fn leases_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<LeasesRow>, ServerFnError> {
use LeasesRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
SELECT DISTINCT
client.canonical AS client,
document.grantee_lessee AS lessee,
DATE(document.effective_date) AS effective,
DATE(lease.expiration_date) AS expiration,
lease.term,
1.0 * lease.royalty_proportion AS royalty,
lease_type.name AS lease_type,
lease_status.name AS status
FROM
lease
JOIN
lease_type ON lease_type.id = lease.lease_type_id
JOIN
client ON client.id = lease.client_id
JOIN
lease_status ON lease_status.id = lease.lease_status_id
JOIN
document ON document.asset_id = lease.asset_id
WHERE
client.canonical = ?
AND document.grantee_lessee NOT IN ('', '-')
AND lease.term NOT IN ('', '-')
ORDER BY
lease.expiration_date DESC,
document.grantee_lessee;
"#;
let records = sqlx::query_as::<_, LeasesRow>(query)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/leases/resource.rs
================
use super::query::LeasesRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching lease data.
#[derive(Clone)]
pub(crate) struct LeasesResource {
pub revenue_rows: Resource<String, Result<Vec<LeasesRow>, ServerFnError>>,
}
use super::query::*;
impl LeasesResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
leases_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pieleasestatus/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pieleasestatus/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the pie Lease Status report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieLeaseStatusRow {
pub canonical: String,
pub status_name: String,
pub percentage: f64,
}
/// Fetches lease status data for clients.
#[server]
pub async fn pieleasestatus_fetch_by_canonical_name(
_canonical_name: String,
) -> Result<Vec<PieLeaseStatusRow>, ServerFnError> {
use PieLeaseStatusRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
WITH filtered_leases AS (
SELECT
client.canonical,
lease_status.name AS status_name
FROM
client
JOIN
lease ON lease.client_id = client.id
JOIN
lease_status ON lease.lease_status_id = lease_status.id
WHERE
client.canonical = ?
AND lease_status.name != '-'
),
status_counts AS (
SELECT
canonical,
status_name,
COUNT(*) AS status_count
FROM
filtered_leases
GROUP BY
canonical, status_name
),
total_counts AS (
SELECT
canonical,
COUNT(*) AS total_count
FROM
filtered_leases
GROUP BY
canonical
),
percentage_counts AS (
SELECT
c.canonical,
c.status_name,
c.status_count,
t.total_count,
ROUND((c.status_count * 100.0 / t.total_count), 2) AS raw_percentage
FROM
status_counts c
JOIN
total_counts t ON c.canonical = t.canonical
)
SELECT
p.canonical,
p.status_name,
ROUND(
CASE
WHEN p.row_num = p.total_rows THEN
100 - SUM(p.raw_percentage) OVER (PARTITION BY p.canonical) + p.raw_percentage
ELSE p.raw_percentage
END, 2
) AS percentage
FROM (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY p.canonical ORDER BY p.status_name) AS row_num,
COUNT(*) OVER (PARTITION BY p.canonical) AS total_rows
FROM
percentage_counts p
) AS p
ORDER BY
p.canonical, p.status_name;
"#;
let records = sqlx::query_as::<_, PieLeaseStatusRow>(query)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pieleasestatus/resource.rs
================
use leptos::*;
use super::query::{PieLeaseStatusRow, pieleasestatus_fetch_by_canonical_name};
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching lease status distribution data.
#[derive(Clone)]
pub(crate) struct PieLeaseStatusResource {
pub revenue_rows: Resource<String, Result<Vec<PieLeaseStatusRow>, ServerFnError>>,
}
impl PieLeaseStatusResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pieleasestatus_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pieproducingwellscounty/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pieproducingwellscounty/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the producing wells distribution by county report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieProducingWellsCountyRow {
pub canonical: String,
pub county: String,
pub percentage: Option<f64>,
}
/// Fetches producing wells distribution data by county.
#[server]
pub async fn pieproducingwellscounty_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieProducingWellsCountyRow>, ServerFnError> {
pub use PieProducingWellsCountyRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieProducingWellsCountyRow>(
"WITH filtered_wells AS (
SELECT
client.canonical,
well.county
FROM
client
JOIN
well_interest ON well_interest.client_id = client.id
JOIN
well ON well_interest.well_id = well.well_id
JOIN
well_status ON well.well_status_id = well_status.id
WHERE
client.canonical = ?
AND well_status.name = 'Producing'
AND well.county != ''
AND well.api_or_uwi != ''
),
county_counts AS (
SELECT
canonical,
county,
COUNT(*) AS county_count
FROM
filtered_wells
GROUP BY
canonical, county
),
total_counts AS (
SELECT
canonical,
COUNT(*) AS total_count
FROM
filtered_wells
GROUP BY
canonical
),
percentage_counts AS (
SELECT
c.canonical,
c.county,
c.county_count,
ROUND((c.county_count * 100.0 / t.total_count), 2) AS raw_percentage
FROM
county_counts c
JOIN
total_counts t ON c.canonical = t.canonical
)
SELECT
p.canonical,
p.county,
CASE
WHEN p.row_num = p.total_rows THEN ROUND(100 - SUM(p.raw_percentage) OVER (PARTITION BY p.canonical) + p.raw_percentage, 2)
ELSE p.raw_percentage
END AS percentage
FROM (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY p.canonical ORDER BY p.county) AS row_num,
COUNT(*) OVER (PARTITION BY p.canonical) AS total_rows
FROM
percentage_counts p
) AS p
ORDER BY
p.canonical, p.county"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pieproducingwellscounty/resource.rs
================
use leptos::*;
use super::query::PieProducingWellsCountyRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching producing wells distribution data by county.
#[derive(Clone)]
pub(crate) struct PieProducingWellsCountyResource {
pub revenue_rows: Resource<String, Result<Vec<PieProducingWellsCountyRow>, ServerFnError>>,
}
use super::query::*;
impl PieProducingWellsCountyResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pieproducingwellscounty_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pieproducingwellsstate/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pieproducingwellsstate/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the producing wells distribution by state report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieProducingWellsStateRow {
pub canonical: String,
pub state: String,
pub percentage: Option<f64>,
}
/// Fetches producing wells distribution data by state.
#[server]
pub async fn pieproducingwellsstate_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieProducingWellsStateRow>, ServerFnError> {
pub use PieProducingWellsStateRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieProducingWellsStateRow>(
"WITH filtered_wells AS (
SELECT
client.canonical,
state.name AS state_name
FROM
client
JOIN
well_interest ON well_interest.client_id = client.id
JOIN
well ON well_interest.well_id = well.well_id
JOIN
well_status ON well.well_status_id = well_status.id
JOIN
state ON state.state = well.state
WHERE
client.canonical = ?
AND well_status.name = 'Producing'
AND well.state != ''
AND well.api_or_uwi != ''
),
state_counts AS (
SELECT
canonical,
state_name AS state,
COUNT(*) AS state_count
FROM
filtered_wells
GROUP BY
canonical, state_name
),
total_counts AS (
SELECT
canonical,
COUNT(*) AS total_count
FROM
filtered_wells
GROUP BY
canonical
),
percentage_counts AS (
SELECT
c.canonical,
c.state,
c.state_count,
ROUND((c.state_count * 100.0 / t.total_count), 2) AS raw_percentage
FROM
state_counts c
JOIN
total_counts t ON c.canonical = t.canonical
)
SELECT
p.canonical,
p.state,
CASE
WHEN p.row_num = p.total_rows THEN ROUND(100 - SUM(p.raw_percentage) OVER (PARTITION BY p.canonical) + p.raw_percentage, 2)
ELSE p.raw_percentage
END AS percentage
FROM (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY p.canonical ORDER BY p.state) AS row_num,
COUNT(*) OVER (PARTITION BY p.canonical) AS total_rows
FROM
percentage_counts p
) AS p
ORDER BY
p.canonical, p.state;"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pieproducingwellsstate/resource.rs
================
use leptos::*;
use super::query::PieProducingWellsStateRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching producing wells distribution data by state.
#[derive(Clone)]
pub(crate) struct PieProducingWellsStateResource {
pub revenue_rows: Resource<String, Result<Vec<PieProducingWellsStateRow>, ServerFnError>>,
}
use super::query::*;
impl PieProducingWellsStateResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pieproducingwellsstate_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pierevenuebycounty/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pierevenuebycounty/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revenue distribution by county report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieRevenueByCountyRow {
pub owner_name_client: String,
pub county: String,
pub share_net_percentage: Option<f64>,
}
/// Fetches revenue distribution data by county.
#[server]
pub async fn pierevenuebycounty_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieRevenueByCountyRow>, ServerFnError> {
pub use PieRevenueByCountyRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieRevenueByCountyRow>(
"WITH total AS (
SELECT
owner_name_client,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net
FROM revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * share_net_revenue > 0
AND (
(check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
GROUP BY owner_name_client
),
revenue_data AS (
SELECT
rd.owner_name_client,
COALESCE(NULLIF(rd.county, ''), 'unreported') AS county,
1.0 * rd.share_net_revenue AS share_net_revenue,
ROUND((1.0 * rd.share_net_revenue * 100.0 / t.total_share_net), 2) AS raw_percentage
FROM revenue_raw rd
JOIN total t ON rd.owner_name_client = t.owner_name_client
WHERE
rd.owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * rd.share_net_revenue > 0
AND (
(rd.check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND rd.check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', rd.check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
)
SELECT
p.owner_name_client,
p.county,
ROUND(SUM(p.raw_percentage) * 100.0 / SUM(SUM(p.raw_percentage)) OVER (PARTITION BY p.owner_name_client), 2) AS share_net_percentage
FROM revenue_data p
GROUP BY p.owner_name_client, p.county
ORDER BY p.owner_name_client, p.county"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pierevenuebycounty/resource.rs
================
use leptos::*;
use super::query::PieRevenueByCountyRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching revenue distribution data by county.
#[derive(Clone)]
pub(crate) struct PieRevenueByCountyResource {
pub revenue_rows: Resource<String, Result<Vec<PieRevenueByCountyRow>, ServerFnError>>,
}
use super::query::*;
impl PieRevenueByCountyResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pierevenuebycounty_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pierevenuebyoperator/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pierevenuebyoperator/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revenue distribution by operator report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieRevenueByOperatorRow {
pub owner_name_client: String,
pub operator_name: String,
pub share_net_percentage: Option<f64>,
}
/// Fetches revenue distribution data by operator.
#[server]
pub async fn pierevenuebyoperator_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieRevenueByOperatorRow>, ServerFnError> {
pub use PieRevenueByOperatorRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieRevenueByOperatorRow>(
r#"
WITH total AS (
SELECT
owner_name_client,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * share_net_revenue > 0
AND (
(check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
GROUP BY
owner_name_client
),
revenue_data AS (
SELECT
rd.owner_name_client,
o.name AS operator_name,
1.0 * rd.share_net_revenue AS share_net_revenue,
ROUND((1.0 * rd.share_net_revenue * 100.0 / t.total_share_net), 2) AS raw_percentage
FROM
revenue_raw rd
JOIN
operator o ON o.canonical = rd.operator_purchaser
JOIN
total t ON rd.owner_name_client = t.owner_name_client
WHERE
rd.owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * rd.share_net_revenue > 0
AND (
(rd.check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND rd.check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', rd.check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
)
SELECT
p.owner_name_client,
p.operator_name,
ROUND(SUM(p.raw_percentage), 2) AS share_net_percentage
FROM
revenue_data p
GROUP BY
p.owner_name_client, p.operator_name
ORDER BY
p.owner_name_client, p.operator_name
"#
)
.bind(&canonical_name) // for total
.bind(&canonical_name) // for revenue_data
.fetch_all(&land_pool)
.await
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pierevenuebyoperator/resource.rs
================
use leptos::*;
use super::query::PieRevenueByOperatorRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching revenue distribution data by operator.
#[derive(Clone)]
pub(crate) struct PieRevenueByOperatorResource {
pub revenue_rows: Resource<String, Result<Vec<PieRevenueByOperatorRow>, ServerFnError>>,
}
use super::query::*;
impl PieRevenueByOperatorResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pierevenuebyoperator_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pierevenuebyproduct/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pierevenuebyproduct/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revenue distribution by product report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieRevenueByProductRow {
pub owner_name_client: String,
pub product: String,
pub share_net_percentage: Option<f64>,
}
/// Fetches revenue distribution data by product.
#[server]
pub async fn pierevenuebyproduct_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieRevenueByProductRow>, ServerFnError> {
pub use PieRevenueByProductRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieRevenueByProductRow>(
"WITH total AS (
SELECT
owner_name_client,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net
FROM revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * share_net_revenue > 0
AND (
(check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
GROUP BY owner_name_client
),
revenue_data AS (
SELECT
rd.owner_name_client,
rd.product,
1.0 * rd.share_net_revenue AS share_net_revenue,
ROUND((1.0 * rd.share_net_revenue * 100.0 / t.total_share_net), 2) AS raw_percentage
FROM revenue_raw rd
JOIN total t ON rd.owner_name_client = t.owner_name_client
WHERE
rd.owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * rd.share_net_revenue > 0
AND (
(rd.check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND rd.check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', rd.check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
)
SELECT
p.owner_name_client,
p.product,
ROUND(SUM(p.raw_percentage), 2) AS share_net_percentage
FROM revenue_data p
GROUP BY p.owner_name_client, p.product
ORDER BY p.owner_name_client, p.product"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pierevenuebyproduct/resource.rs
================
use leptos::*;
use super::query::PieRevenueByProductRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching revenue distribution data by product.
#[derive(Clone)]
pub(crate) struct PieRevenueByProductResource {
pub revenue_rows: Resource<String, Result<Vec<PieRevenueByProductRow>, ServerFnError>>,
}
use super::query::*;
impl PieRevenueByProductResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pierevenuebyproduct_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/pierevenuebystate/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/pierevenuebystate/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revenue distribution by state report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieRevenueByStateRow {
pub owner_name_client: String,
pub state: String,
pub share_net_percentage: Option<f64>,
}
/// Fetches revenue distribution data by state.
#[server]
pub async fn pierevenuebystate_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieRevenueByStateRow>, ServerFnError> {
pub use PieRevenueByStateRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieRevenueByStateRow>(
"WITH total AS (
SELECT
owner_name_client,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net
FROM revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * share_net_revenue > 0
AND (
(check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
GROUP BY owner_name_client
),
revenue_data AS (
SELECT
rd.owner_name_client,
COALESCE(NULLIF(rd.state, ''), 'unreported') AS state,
1.0 * rd.share_net_revenue AS share_net_revenue,
ROUND((1.0 * rd.share_net_revenue * 100.0 / t.total_share_net), 2) AS raw_percentage
FROM revenue_raw rd
JOIN total t ON rd.owner_name_client = t.owner_name_client
WHERE
rd.owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND 1.0 * rd.share_net_revenue > 0
AND (
(rd.check_date >= strftime('%Y-%m-01', 'now', 'start of month', '-1 month')
AND rd.check_date < strftime('%Y-%m-01', 'now'))
OR
(strftime('%Y-%m', rd.check_date) = strftime('%Y-%m', 'now', '-1 month'))
)
)
SELECT
p.owner_name_client,
p.state,
ROUND(SUM(p.raw_percentage) * 100.0 / SUM(SUM(p.raw_percentage)) OVER (PARTITION BY p.owner_name_client), 2) AS share_net_percentage
FROM revenue_data p
GROUP BY p.owner_name_client, p.state
ORDER BY p.owner_name_client, p.state"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/pierevenuebystate/resource.rs
================
use leptos::*;
use super::query::PieRevenueByStateRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching revenue distribution data by state.
#[derive(Clone)]
pub(crate) struct PieRevenueByStateResource {
pub revenue_rows: Resource<String, Result<Vec<PieRevenueByStateRow>, ServerFnError>>,
}
use super::query::*;
impl PieRevenueByStateResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
pierevenuebystate_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e))) // Fixed conversion
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/piewellstatus/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/piewellstatus/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the well status distribution report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct PieWellStatusRow {
pub canonical: String,
pub status_name: String,
pub percentage: Option<f64>,
}
/// Fetches well status distribution data from multiple tables.
#[server]
pub async fn piewellstatus_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<PieWellStatusRow>, ServerFnError> {
pub use PieWellStatusRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, PieWellStatusRow>(
"WITH filtered_wells AS (
SELECT
client.canonical,
well_status.name AS status_name
FROM
client
JOIN
well_interest ON well_interest.client_id = client.id
JOIN
well ON well_interest.well_id = well.well_id
JOIN
well_status ON well.well_status_id = well_status.id
WHERE
client.canonical = ?
AND well_status.name != ''
AND well.api_or_uwi != ''
),
status_counts AS (
SELECT
canonical,
status_name,
COUNT(*) AS status_count
FROM
filtered_wells
GROUP BY
canonical, status_name
),
total_counts AS (
SELECT
canonical,
COUNT(*) AS total_count
FROM
filtered_wells
GROUP BY
canonical
),
percentage_counts AS (
SELECT
c.canonical,
c.status_name,
c.status_count,
ROUND((c.status_count * 100.0 / t.total_count), 2) AS raw_percentage
FROM
status_counts c
JOIN
total_counts t ON c.canonical = t.canonical
)
SELECT
p.canonical,
p.status_name,
CASE
WHEN p.row_num = p.total_rows THEN ROUND(100 - SUM(p.raw_percentage) OVER (PARTITION BY p.canonical) + p.raw_percentage, 2)
ELSE p.raw_percentage
END AS percentage
FROM (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY p.canonical ORDER BY p.status_name) AS row_num,
COUNT(*) OVER (PARTITION BY p.canonical) AS total_rows
FROM
percentage_counts p
) AS p
ORDER BY
p.canonical, p.status_name"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/piewellstatus/resource.rs
================
use leptos::*;
use super::query::PieWellStatusRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching well status distribution data.
#[derive(Clone)]
pub(crate) struct PieWellStatusResource {
pub revenue_rows: Resource<String, Result<Vec<PieWellStatusRow>, ServerFnError>>,
}
use super::query::*;
impl PieWellStatusResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx.map(|ctx| ctx.account_memo.get().canonical_name.clone()).unwrap_or_default()
},
|canonical_name| async move {
piewellstatus_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
}
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/revenue/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/revenue/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revenue report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct RevenueRow {
pub original_file_path: String,
pub production_start_date: Option<String>,
pub production_end_date: Option<String>,
pub sale_date: Option<String>,
pub payment_date: Option<String>,
pub product: Option<String>,
pub pie_gross_revenue: Option<f64>,
pub share_net_revenue: Option<f64>,
}
/// Fetches revenue data from `revenue_raw` by `canonical_name`.
#[server]
pub async fn revenue_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<RevenueRow>, ServerFnError> {
// use leptos::*;
pub use RevenueRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, RevenueRow>(
"SELECT original_file_path, production_start_date, production_end_date,
sale_date, payment_date, product,
pie_gross_revenue, share_net_revenue
FROM revenue_raw
WHERE well_canonical = ?
ORDER BY sale_date DESC",
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/revenue/resource.rs
================
use super::query::RevenueRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching revenue report data.
#[derive(Clone)]
pub(crate) struct RevenueResource {
pub revenue_rows: Resource<String, Result<Vec<RevenueRow>, ServerFnError>>,
}
use super::query::*;
impl RevenueResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
revenue_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/revjibdetails/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/revjibdetails/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revjibdetails report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct RevJibDetailsRow {
pub client: String,
pub well_name: String,
pub total_share_gross_revenue: f64,
pub total_share_taxes: f64,
pub total_share_deductions: f64,
pub total_share_net_revenue: f64,
pub total_jib_amount: f64,
pub year: String,
}
/// Fetches unmatched JIB and Revenue details.
#[server]
pub async fn revjibdetails_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<RevJibDetailsRow>, ServerFnError> {
use RevJibDetailsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
WITH standardize_date AS (
SELECT
TRIM(pay_month) AS pay_month,
payor,
payee,
amount,
CASE
WHEN pay_month LIKE '____-__-__' THEN pay_month
WHEN pay_month LIKE '__/__/____' OR pay_month LIKE '_/__/____' OR pay_month LIKE '__/_/____' OR pay_month LIKE '_/_/____' THEN
CASE
WHEN length(pay_month) = 10 THEN strftime('%Y-%m-%d', substr(pay_month, 7, 4) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN length(pay_month) = 9 THEN strftime('%Y-%m-%d', substr(pay_month, 6, 4) || '-0' || substr(pay_month, 1, 1) || '-' || substr(pay_month, 3, 2))
WHEN length(pay_month) = 8 THEN strftime('%Y-%m-%d', substr(pay_month, 5, 4) || '-' || substr(pay_month, 1, 2) || '-0' || substr(pay_month, 4, 1))
ELSE strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-0' || substr(pay_month, 1, 1) || '-0' || substr(pay_month, 3, 1))
END
WHEN pay_month LIKE '___ __, ____' THEN strftime('%Y-%m-%d',
substr(pay_month, -4, 4) || '-' ||
CASE
WHEN substr(pay_month, 1, 3) = 'Jan' THEN '01'
WHEN substr(pay_month, 1, 3) = 'Feb' THEN '02'
WHEN substr(pay_month, 1, 3) = 'Mar' THEN '03'
WHEN substr(pay_month, 1, 3) = 'Apr' THEN '04'
WHEN substr(pay_month, 1, 3) = 'May' THEN '05'
WHEN substr(pay_month, 1, 3) = 'Jun' THEN '06'
WHEN substr(pay_month, 1, 3) = 'Jul' THEN '07'
WHEN substr(pay_month, 1, 3) = 'Aug' THEN '08'
WHEN substr(pay_month, 1, 3) = 'Sep' THEN '09'
WHEN substr(pay_month, 1, 3) = 'Oct' THEN '10'
WHEN substr(pay_month, 1, 3) = 'Nov' THEN '11'
WHEN substr(pay_month, 1, 3) = 'Dec' THEN '12'
ELSE '01'
END || '-' ||
CASE
WHEN length(trim(substr(pay_month, 5, 2))) = 1 THEN '0' || substr(pay_month, 5, 1)
ELSE substr(pay_month, 5, 2)
END
)
WHEN pay_month LIKE '__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 4, 2) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__/__' THEN strftime('%Y-%m-%d', replace(pay_month, '/', '-'))
WHEN pay_month LIKE '____-__' THEN pay_month || '-15'
WHEN pay_month LIKE '__/__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 7, 2) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN pay_month LIKE '__-__-____' OR pay_month LIKE '_-__-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
CASE
WHEN length(pay_month) = 10 THEN substr(pay_month, 1, 2)
ELSE '0' || substr(pay_month, 1, 1)
END || '-' ||
CASE
WHEN length(pay_month) = 10 THEN substr(pay_month, 4, 2)
ELSE substr(pay_month, 3, 2)
END
)
WHEN pay_month LIKE '__/____' OR pay_month LIKE '__-____' THEN
strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__' THEN
strftime('%Y-%m-%d', substr(pay_month, 1, 4) || '-' || substr(pay_month, 6, 2) || '-15')
WHEN pay_month LIKE '__-___-____' OR pay_month LIKE '_-___-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
CASE
WHEN INSTR(UPPER(pay_month), '-JAN-') > 0 THEN '01'
WHEN INSTR(UPPER(pay_month), '-FEB-') > 0 THEN '02'
WHEN INSTR(UPPER(pay_month), '-MAR-') > 0 THEN '03'
WHEN INSTR(UPPER(pay_month), '-APR-') > 0 THEN '04'
WHEN INSTR(UPPER(pay_month), '-MAY-') > 0 THEN '05'
WHEN INSTR(UPPER(pay_month), '-JUN-') > 0 THEN '06'
WHEN INSTR(UPPER(pay_month), '-JUL-') > 0 THEN '07'
WHEN INSTR(UPPER(pay_month), '-AUG-') > 0 THEN '08'
WHEN INSTR(UPPER(pay_month), '-SEP-') > 0 THEN '09'
WHEN INSTR(UPPER(pay_month), '-OCT-') > 0 THEN '10'
WHEN INSTR(UPPER(pay_month), '-NOV-') > 0 THEN '11'
WHEN INSTR(UPPER(pay_month), '-DEC-') > 0 THEN '12'
ELSE '01'
END || '-' ||
CASE
WHEN length(pay_month) = 11 THEN substr(pay_month, 1, 2)
ELSE '0' || substr(pay_month, 1, 1)
END
)
ELSE NULL
END AS normalized_date
FROM jibs
),
summary_jibs AS (
SELECT
payor,
payee,
STRFTIME('%Y', normalized_date) AS normalized_year,
ROUND(SUM(1.0 * amount), 2) AS total_jib_amount
FROM standardize_date
GROUP BY payee, normalized_year
),
summary_revenue AS (
SELECT
owner_name_client,
well_property_name,
STRFTIME('%Y', check_date) AS revenue_year,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS total_share_gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS total_share_taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS total_share_deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net_revenue
FROM revenue_raw
WHERE aggregation_level IN ('i', 'e', 'h')
GROUP BY well_property_name, revenue_year
)
SELECT
sj.payee AS well_name,
0.0 AS total_share_gross_revenue,
0.0 AS total_share_taxes,
0.0 AS total_share_deductions,
0.0 AS total_share_net_revenue,
sj.total_jib_amount,
sj.normalized_year AS year
FROM summary_jibs AS sj
LEFT JOIN summary_revenue AS sr
ON sj.payee = sr.well_property_name
AND sj.normalized_year = sr.revenue_year
AND sj.payor = sr.owner_name_client = ?
WHERE sr.well_property_name IS NULL
UNION ALL
SELECT
sr.well_property_name AS well_name,
sr.total_share_gross_revenue,
sr.total_share_taxes,
sr.total_share_deductions,
sr.total_share_net_revenue,
0.0 AS total_jib_amount,
sr.revenue_year AS year
FROM summary_revenue AS sr
LEFT JOIN summary_jibs AS sj
ON sr.well_property_name = sj.payee
AND sr.revenue_year = sj.normalized_year
AND sr.owner_name_client = sj.payor = ?
WHERE sj.payee IS NULL
ORDER BY well_name, year;
"#;
let records = sqlx::query_as::<_, RevJibDetailsRow>(query)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/revjibdetails/resource.rs
================
use super::query::RevJibDetailsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching revenue and JIB details data.
#[derive(Clone)]
pub(crate) struct RevJibDetailsResource {
pub revenue_rows: Resource<String, Result<Vec<RevJibDetailsRow>, ServerFnError>>,
}
use super::query::*;
impl RevJibDetailsResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
revjibdetails_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/revjibsummary/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/revjibsummary/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row in the revjibsummary report.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct RevJibSummaryRow {
pub client: String,
pub operator_name: String,
pub total_share_net_revenue: f64,
pub total_jib_amount: f64,
pub year: String,
}
/// Fetches JIB/Revenue summary comparisons by client/operator/year.
#[server]
pub async fn revjibsummary_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<RevJibSummaryRow>, ServerFnError> {
use RevJibSummaryRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let query = r#"
WITH standardize_date AS (
SELECT
TRIM(pay_month) AS pay_month,
payor,
payee,
amount,
CASE
WHEN pay_month LIKE '____-__-__' THEN pay_month
WHEN pay_month LIKE '__/__/____' OR pay_month LIKE '_/__/____' OR pay_month LIKE '__/_/____' OR pay_month LIKE '_/_/____' THEN
CASE
WHEN length(pay_month) = 10 THEN strftime('%Y-%m-%d', substr(pay_month, 7, 4) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN length(pay_month) = 9 THEN strftime('%Y-%m-%d', substr(pay_month, 6, 4) || '-0' || substr(pay_month, 1, 1) || '-' || substr(pay_month, 3, 2))
WHEN length(pay_month) = 8 THEN strftime('%Y-%m-%d', substr(pay_month, 5, 4) || '-' || substr(pay_month, 1, 2) || '-0' || substr(pay_month, 4, 1))
ELSE strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-0' || substr(pay_month, 1, 1) || '-0' || substr(pay_month, 3, 1))
END
WHEN pay_month LIKE '___ __, ____' THEN strftime('%Y-%m-%d',
substr(pay_month, -4, 4) || '-' ||
CASE
WHEN substr(pay_month, 1, 3) = 'Jan' THEN '01'
WHEN substr(pay_month, 1, 3) = 'Feb' THEN '02'
WHEN substr(pay_month, 1, 3) = 'Mar' THEN '03'
WHEN substr(pay_month, 1, 3) = 'Apr' THEN '04'
WHEN substr(pay_month, 1, 3) = 'May' THEN '05'
WHEN substr(pay_month, 1, 3) = 'Jun' THEN '06'
WHEN substr(pay_month, 1, 3) = 'Jul' THEN '07'
WHEN substr(pay_month, 1, 3) = 'Aug' THEN '08'
WHEN substr(pay_month, 1, 3) = 'Sep' THEN '09'
WHEN substr(pay_month, 1, 3) = 'Oct' THEN '10'
WHEN substr(pay_month, 1, 3) = 'Nov' THEN '11'
WHEN substr(pay_month, 1, 3) = 'Dec' THEN '12'
ELSE '01'
END || '-' ||
CASE
WHEN length(trim(substr(pay_month, 5, 2))) = 1 THEN '0' || substr(pay_month, 5, 1)
ELSE substr(pay_month, 5, 2)
END
)
WHEN pay_month LIKE '__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 4, 2) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__/__' THEN strftime('%Y-%m-%d', replace(pay_month, '/', '-'))
WHEN pay_month LIKE '____-__' THEN pay_month || '-15'
WHEN pay_month LIKE '__/__/__' THEN strftime('%Y-%m-%d', '20' || substr(pay_month, 7, 2) || '-' || substr(pay_month, 1, 2) || '-' || substr(pay_month, 4, 2))
WHEN pay_month LIKE '__-__-____' OR pay_month LIKE '_-__-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
CASE
WHEN length(pay_month) = 10 THEN substr(pay_month, 1, 2)
ELSE '0' || substr(pay_month, 1, 1)
END || '-' ||
CASE
WHEN length(pay_month) = 10 THEN substr(pay_month, 4, 2)
ELSE substr(pay_month, 3, 2)
END
)
WHEN pay_month LIKE '__/____' OR pay_month LIKE '__-____' THEN
strftime('%Y-%m-%d', substr(pay_month, 4, 4) || '-' || substr(pay_month, 1, 2) || '-15')
WHEN pay_month LIKE '____/__' THEN
strftime('%Y-%m-%d', substr(pay_month, 1, 4) || '-' || substr(pay_month, 6, 2) || '-15')
WHEN pay_month LIKE '__-___-____' OR pay_month LIKE '_-___-____' THEN
strftime('%Y-%m-%d',
substr(pay_month, -4) || '-' ||
CASE
WHEN INSTR(UPPER(pay_month), '-JAN-') > 0 THEN '01'
WHEN INSTR(UPPER(pay_month), '-FEB-') > 0 THEN '02'
WHEN INSTR(UPPER(pay_month), '-MAR-') > 0 THEN '03'
WHEN INSTR(UPPER(pay_month), '-APR-') > 0 THEN '04'
WHEN INSTR(UPPER(pay_month), '-MAY-') > 0 THEN '05'
WHEN INSTR(UPPER(pay_month), '-JUN-') > 0 THEN '06'
WHEN INSTR(UPPER(pay_month), '-JUL-') > 0 THEN '07'
WHEN INSTR(UPPER(pay_month), '-AUG-') > 0 THEN '08'
WHEN INSTR(UPPER(pay_month), '-SEP-') > 0 THEN '09'
WHEN INSTR(UPPER(pay_month), '-OCT-') > 0 THEN '10'
WHEN INSTR(UPPER(pay_month), '-NOV-') > 0 THEN '11'
WHEN INSTR(UPPER(pay_month), '-DEC-') > 0 THEN '12'
ELSE '01'
END || '-' ||
CASE
WHEN length(pay_month) = 11 THEN substr(pay_month, 1, 2)
ELSE '0' || substr(pay_month, 1, 1)
END
)
ELSE NULL
END AS normalized_date
FROM jibs
),
summary_jibs AS (
SELECT
payor,
payee,
STRFTIME('%Y', normalized_date) AS normalized_year,
ROUND(SUM(1.0 * amount), 2) AS total_jib_amount
FROM standardize_date
GROUP BY payee, normalized_year
),
summary_revenue AS (
SELECT
owner_name_client,
operator_purchaser,
STRFTIME('%Y', check_date) AS revenue_year,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net_revenue
FROM revenue_raw
WHERE aggregation_level IN ('i', 'e', 'h')
GROUP BY operator_purchaser, revenue_year
)
SELECT
COALESCE(op1.name, op2.name) AS operator_name,
COALESCE(sr.total_share_net_revenue, 0.0) AS total_share_net_revenue,
COALESCE(sj.total_jib_amount, 0.0) AS total_jib_amount,
COALESCE(sj.normalized_year, sr.revenue_year) AS year
FROM summary_jibs sj
LEFT JOIN summary_revenue sr
ON sj.payee = sr.operator_purchaser
AND sj.normalized_year = sr.revenue_year
AND sj.payor = sr.owner_name_client = ?
LEFT JOIN operator op1
ON op1.canonical = sj.payee
LEFT JOIN operator op2
ON op2.canonical = sr.operator_purchaser
GROUP BY operator_name, year
HAVING year IS NOT NULL
UNION ALL
SELECT
COALESCE(op2.name, op1.name) AS operator_name,
COALESCE(sr.total_share_net_revenue, 0.0) AS total_share_net_revenue,
COALESCE(sj.total_jib_amount, 0.0) AS total_jib_amount,
COALESCE(sr.revenue_year, sj.normalized_year) AS year
FROM summary_revenue sr
LEFT JOIN summary_jibs sj
ON sr.operator_purchaser = sj.payee
AND sr.revenue_year = sj.normalized_year
AND sr.owner_name_client = sj.payor = ?
LEFT JOIN operator op1
ON op1.canonical = sj.payee
LEFT JOIN operator op2
ON op2.canonical = sr.operator_purchaser
WHERE sj.payee IS NULL
GROUP BY operator_name, year
HAVING year IS NOT NULL
ORDER BY operator_name, year;
"#;
let records = sqlx::query_as::<_, RevJibSummaryRow>(query)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/revjibsummary/resource.rs
================
use super::query::RevJibSummaryRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching revenue and JIB summary data.
#[derive(Clone)]
pub(crate) struct RevJibSummaryResource {
pub revenue_rows: Resource<String, Result<Vec<RevJibSummaryRow>, ServerFnError>>,
}
use super::query::*;
impl RevJibSummaryResource {
pub fn new() -> Self {
let revenue_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
revjibsummary_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
// Fixed conversion
},
);
Self { revenue_rows }
}
}
================
File: gmm_client/src/tables/statslistofcounts/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/statslistofcounts/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a summary row of asset/well/lease stats for a client.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct StatsListOfCountsRow {
pub client: String,
pub number_of_states: Option<i64>,
pub number_of_counties: Option<i64>,
pub number_of_assets: Option<i64>,
pub number_of_leases: Option<i64>,
pub number_of_wells: Option<i64>,
}
/// Fetches client-level stats on wells, leases, assets, etc.
#[server]
pub async fn statslistofcounts_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<StatsListOfCountsRow>, ServerFnError> {
pub use StatsListOfCountsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, StatsListOfCountsRow>(
"WITH metrics AS (
SELECT
client.canonical AS client,
'Number of States' AS metric,
COUNT(DISTINCT well.state) AS value
FROM well
JOIN well_status ON well_status.id = well.well_status_id
JOIN well_interest ON well_interest.well_id = well.well_id
JOIN client ON client.id = well_interest.client_id
WHERE LOWER(well_status.name) = 'producing' AND well.state != ''
AND client.canonical = ?
UNION ALL
SELECT
client.canonical AS client,
'Number of Counties' AS metric,
COUNT(DISTINCT well.county) AS value
FROM well
JOIN well_status ON well_status.id = well.well_status_id
JOIN well_interest ON well_interest.well_id = well.well_id
JOIN client ON client.id = well_interest.client_id
WHERE LOWER(well_status.name) = 'producing' AND well.state != ''
AND client.canonical = ?
UNION ALL
SELECT
client.canonical AS client,
'Number of Assets' AS metric,
COUNT(asset.asset_id) AS value
FROM asset
JOIN client ON client.id = asset.client_id
WHERE client.canonical = ?
UNION ALL
SELECT
client.canonical AS client,
'Number of Leases' AS metric,
COUNT(lease.lease_id) AS value
FROM lease
JOIN client ON client.id = lease.client_id
WHERE client.canonical = ?
UNION ALL
SELECT
client.canonical AS client,
'Number of Wells' AS metric,
COUNT(DISTINCT well_interest.well_id) AS value
FROM well_interest
JOIN client ON client.id = well_interest.client_id
WHERE client.canonical = ?
)
SELECT
client,
MAX(CASE WHEN metric = 'Number of States' THEN value ELSE NULL END) AS number_of_states,
MAX(CASE WHEN metric = 'Number of Counties' THEN value ELSE NULL END) AS number_of_counties,
MAX(CASE WHEN metric = 'Number of Assets' THEN value ELSE NULL END) AS number_of_assets,
MAX(CASE WHEN metric = 'Number of Leases' THEN value ELSE NULL END) AS number_of_leases,
MAX(CASE WHEN metric = 'Number of Wells' THEN value ELSE NULL END) AS number_of_wells
FROM metrics"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/statslistofcounts/resource.rs
================
use super::query::StatsListOfCountsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching portfolio summary statistics.
#[derive(Clone)]
pub(crate) struct StatsListOfCountsResource {
pub stats_rows: Resource<String, Result<Vec<StatsListOfCountsRow>, ServerFnError>>,
}
use super::query::*;
impl StatsListOfCountsResource {
pub fn new() -> Self {
let stats_rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
statslistofcounts_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { stats_rows }
}
}
================
File: gmm_client/src/tables/statsproduction/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/statsproduction/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of product-level production and pricing statistics across all clients.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct StatsProductionRow {
pub product: String,
pub avg_price: Option<f64>,
pub min_price: Option<f64>,
pub max_price: Option<f64>,
pub price_volatility: Option<f64>, // actually price variance
pub avg_prod_rate: Option<f64>,
pub min_prod_rate: Option<f64>,
pub max_prod_rate: Option<f64>,
pub prod_volatility: Option<f64>, // actually production variance
pub gross_avg_rate: Option<f64>,
}
/// Fetches production and pricing statistics by product across all clients.
#[server]
pub async fn statsproduction_fetch_all() -> Result<Vec<StatsProductionRow>, ServerFnError> {
pub use StatsProductionRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, StatsProductionRow>(
r#"
WITH base AS (
SELECT
product,
1.0 * unit_price AS unit_price,
1000.0 * 1.0 * share_volume / (julianday('now') - julianday('2019-11-01')) AS daily_prod_rate,
1.0 * unit_price * (1000.0 * 1.0 * share_volume / (julianday('now') - julianday('2019-11-01'))) AS daily_revenue
FROM revenue_raw
WHERE
1.0 * unit_price > 0 AND
1.0 * share_volume > 0
),
avg_stats AS (
SELECT
product,
AVG(unit_price) AS avg_price,
AVG(daily_prod_rate) AS avg_prod_rate
FROM base
GROUP BY product
),
volatility_stats AS (
SELECT
b.product,
-- Variance of price
AVG((b.unit_price - a.avg_price) * (b.unit_price - a.avg_price)) AS price_volatility,
-- Variance of production
AVG((b.daily_prod_rate - a.avg_prod_rate) * (b.daily_prod_rate - a.avg_prod_rate)) AS prod_volatility
FROM base b
JOIN avg_stats a ON b.product = a.product
GROUP BY b.product
),
final_stats AS (
SELECT
product,
MIN(unit_price) AS min_price,
MAX(unit_price) AS max_price,
MIN(daily_prod_rate) AS min_prod_rate,
MAX(daily_prod_rate) AS max_prod_rate,
AVG(daily_revenue) AS gross_avg_rate
FROM base
GROUP BY product
)
SELECT
a.product,
ROUND(a.avg_price, 2) AS avg_price,
ROUND(f.min_price, 2) AS min_price,
ROUND(f.max_price, 2) AS max_price,
ROUND(v.price_volatility, 2) AS price_volatility,
ROUND(a.avg_prod_rate, 2) AS avg_prod_rate,
ROUND(f.min_prod_rate, 2) AS min_prod_rate,
ROUND(f.max_prod_rate, 2) AS max_prod_rate,
ROUND(v.prod_volatility, 2) AS prod_volatility,
ROUND(f.gross_avg_rate, 2) AS gross_avg_rate
FROM avg_stats a
JOIN volatility_stats v ON a.product = v.product
JOIN final_stats f ON a.product = f.product
ORDER BY a.product;
"#
)
.fetch_all(&land_pool)
.await
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/statsproduction/resource.rs
================
use super::query::StatsProductionRow;
use crate::tables::statsproduction::query::statsproduction_fetch_all;
use leptos::*;
/// Leptos resource for fetching product-level production and pricing statistics.
#[derive(Clone)]
pub(crate) struct StatsProductionResource {
pub stats_rows: Resource<(), Result<Vec<StatsProductionRow>, ServerFnError>>,
}
impl StatsProductionResource {
pub fn new() -> Self {
let stats_rows = create_resource(
|| (),
|_| async move {
statsproduction_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { stats_rows }
}
}
================
File: gmm_client/src/tables/statstopoperators/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/statstopoperators/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row showing top operators by client revenue.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct StatsTopOperatorsRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub owner_net_revenue: Option<f64>,
pub exponential_mean: Option<f64>,
pub scatter: Option<f64>,
}
/// Fetches top operators by revenue per client for the last month.
#[server]
pub async fn statstopoperators_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<StatsTopOperatorsRow>, ServerFnError> {
pub use StatsTopOperatorsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, StatsTopOperatorsRow>(
r#"
WITH total AS (
SELECT
AVG(1.0 * share_net_revenue) AS avg_share_net
FROM
revenue_raw
WHERE
owner_name_client = ?
AND 1.0 * share_net_revenue > 0
AND strftime('%Y-%m', check_date) = strftime('%Y-%m', 'now', '-1 month')
),
operator_stats AS (
SELECT
rr.owner_name_client,
o.name AS operator_name,
ROUND(SUM(1.0 * rr.share_net_revenue), 2) AS total_share_net,
AVG(1.0 * rr.share_net_revenue) AS exponential_mean,
AVG((1.0 * rr.share_net_revenue - t.avg_share_net) *
(1.0 * rr.share_net_revenue - t.avg_share_net)) AS scatter
FROM
revenue_raw rr,
total t
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.owner_name_client = ?
AND 1.0 * rr.share_net_revenue > 0
AND strftime('%Y-%m', rr.check_date) = strftime('%Y-%m', 'now', '-1 month')
GROUP BY
o.name
),
ranked_operator_stats AS (
SELECT
owner_name_client,
operator_name,
total_share_net,
exponential_mean,
scatter,
ROW_NUMBER() OVER (PARTITION BY owner_name_client ORDER BY total_share_net DESC) AS row_num
FROM
operator_stats
)
SELECT
owner_name_client,
operator_name,
total_share_net AS owner_net_revenue,
exponential_mean,
scatter
FROM
ranked_operator_stats
WHERE
row_num <= 10
ORDER BY
row_num
"#
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/statstopoperators/resource.rs
================
use super::query::StatsTopOperatorsRow;
use crate::client_main_app::ctx::*;
use leptos::*;
/// Leptos resource for fetching top operators by client revenue.
#[derive(Clone)]
pub(crate) struct StatsTopOperatorsResource {
pub top_operators: Resource<String, Result<Vec<StatsTopOperatorsRow>, ServerFnError>>,
}
use super::query::*;
impl StatsTopOperatorsResource {
pub fn new() -> Self {
let top_operators = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
statstopoperators_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { top_operators }
}
}
================
File: gmm_client/src/tables/surfacerevenue/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/surfacerevenue/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct SurfaceRevenueRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub description: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub payment_amount: Option<f64>,
pub payment_date: Option<String>,
}
#[server]
pub async fn surfacerevenue_fetch_by_canonical_name(
canonical_name: String,
) -> Result<Vec<SurfaceRevenueRow>, ServerFnError> {
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, SurfaceRevenueRow>(
"SELECT
rr.owner_name_client,
o.name AS operator_name,
rr.well_property_name AS description,
ROUND(SUM(1.0 * rr.share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(rr.share_taxes), 2) AS taxes,
ROUND(SUM(rr.share_deductions), 2) AS deductions,
ROUND(SUM(1.0 * rr.share_net_revenue), 2) AS net_revenue,
1.0 * rr.share_check_amount AS payment_amount,
rr.check_date AS payment_date
FROM
revenue_raw rr
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.owner_name_client = ?
AND rr.aggregation_level = 'u'
GROUP BY
o.name, rr.well_property_name, rr.check_date, rr.share_check_amount, rr.check_reference_number
ORDER BY
operator_name ASC, rr.check_date ASC"
)
.bind(&canonical_name)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/surfacerevenue/resource.rs
================
use leptos::*;
use super::query::SurfaceRevenueRow;
use crate::client_main_app::ctx::*;
/// Leptos resource for fetching summarized surface revenue data by operator and property.
#[derive(Clone)]
pub(crate) struct SurfaceRevenueResource {
pub rows: Resource<String, Result<Vec<SurfaceRevenueRow>, ServerFnError>>,
}
use super::query::*;
impl SurfaceRevenueResource {
pub fn new() -> Self {
let rows = create_resource(
move || {
let account_ctx = AccountCtx::use_context();
account_ctx
.map(|ctx| ctx.account_memo.get().canonical_name.clone())
.unwrap_or_default()
},
|canonical_name| async move {
surfacerevenue_fetch_by_canonical_name(canonical_name)
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/taxyear1099details/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/taxyear1099details/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of 1099 tax details for the prior year.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct TaxYear1099DetailsRow {
pub payee: String,
pub payor: String,
pub tin: Option<String>,
pub box_number: Option<String>,
pub box_name: Option<String>,
pub reported_amount: Option<f64>,
}
/// Fetches 1099 tax details from the prior tax year.
#[server]
pub async fn taxyear1099details_fetch_all() -> Result<Vec<TaxYear1099DetailsRow>, ServerFnError> {
pub use TaxYear1099DetailsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, TaxYear1099DetailsRow>(
"SELECT
o.name AS operator_name,
i.payor,
i.tin,
i.box_number,
i.box_name,
i.reported_amount
FROM
items1099 i
JOIN
operator o ON o.canonical = i.payee
WHERE
i.payee = ?
AND i.tax_year = strftime('%Y', 'now', '-1 year')
ORDER BY
i.payor"
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/taxyear1099details/resource.rs
================
use super::query::TaxYear1099DetailsRow;
use leptos::*;
/// Leptos resource for fetching 1099 details for the prior tax year.
#[derive(Clone)]
pub(crate) struct TaxYear1099DetailsResource {
pub details_rows: Resource<(), Result<Vec<TaxYear1099DetailsRow>, ServerFnError>>,
}
use super::query::*;
impl TaxYear1099DetailsResource {
pub fn new() -> Self {
let details_rows = create_resource(
|| (),
|_| async move {
taxyear1099details_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { details_rows }
}
}
================
File: gmm_client/src/tables/taxyearfullyeartotals/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/taxyearfullyeartotals/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of tax year totals for a client.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct TaxYearFullYearTotalsRow {
pub owner_name_client: String,
pub gross_share: Option<f64>,
pub share_taxes: Option<f64>,
pub share_deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub jibs: Option<f64>,
}
/// Fetches full-year tax totals from the previous calendar year.
#[server]
pub async fn taxyearfullyeartotals_fetch_all() -> Result<Vec<TaxYearFullYearTotalsRow>, ServerFnError> {
pub use TaxYearFullYearTotalsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, TaxYearFullYearTotalsRow>(
"WITH CalculatedValues AS (
SELECT
owner_name_client,
1.0 * share_gross_revenue AS share_gross_revenue,
1.0 * share_taxes AS share_taxes,
1.0 * share_deductions AS share_deductions,
1.0 * share_net_revenue AS share_net_revenue,
1.0 * jibs.amount AS amount
FROM
revenue_raw
JOIN
jibs ON jibs.payor = revenue_raw.owner_name_client
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', '-1 year', 'start of year')
AND check_date < date('now', 'start of year')
)
SELECT
owner_name_client,
ROUND(SUM(share_gross_revenue), 2) AS gross_share,
ROUND(SUM(share_taxes), 2) AS share_taxes,
ROUND(SUM(share_deductions), 2) AS share_deductions,
ROUND(SUM(share_net_revenue), 2) AS net_revenue,
ROUND(SUM(amount), 2) AS jibs
FROM
CalculatedValues"
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/taxyearfullyeartotals/resource.rs
================
use super::query::TaxYearFullYearTotalsRow;
use leptos::*;
/// Leptos resource for fetching tax year full year totals.
#[derive(Clone)]
pub(crate) struct TaxYearFullYearTotalsResource {
pub totals_rows: Resource<(), Result<Vec<TaxYearFullYearTotalsRow>, ServerFnError>>,
}
use super::query::*;
impl TaxYearFullYearTotalsResource {
pub fn new() -> Self {
let totals_rows = create_resource(
|| (),
|_| async move {
taxyearfullyeartotals_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { totals_rows }
}
}
================
File: gmm_client/src/tables/taxyearinterestbystate/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/taxyearinterestbystate/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of tax year revenue by state and interest type.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct TaxYearInterestByStateRow {
pub owner_name_client: String,
pub state: String,
pub interest_type: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
}
/// Fetches tax year totals by state and interest type.
#[server]
pub async fn taxyearinterestbystate_fetch_all() -> Result<Vec<TaxYearInterestByStateRow>, ServerFnError> {
pub use TaxYearInterestByStateRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, TaxYearInterestByStateRow>(
"WITH CalculatedValues AS (
SELECT
owner_name_client,
state,
interest_type,
1.0 * share_gross_revenue AS share_gross_revenue,
1.0 * share_taxes AS share_taxes,
1.0 * share_deductions AS share_deductions,
1.0 * share_net_revenue AS share_net_revenue
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', '-1 year', 'start of year')
AND check_date < date('now', 'start of year')
),
StateRevenue AS (
SELECT
owner_name_client,
state,
interest_type,
ROUND(SUM(share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(share_taxes), 2) AS taxes,
ROUND(SUM(share_deductions), 2) AS deductions,
ROUND(SUM(share_net_revenue), 2) AS net_revenue
FROM
CalculatedValues
GROUP BY
state, interest_type
)
SELECT
owner_name_client,
state,
interest_type,
gross_revenue,
taxes,
deductions,
net_revenue
FROM
StateRevenue
ORDER BY
state ASC"
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/taxyearinterestbystate/resource.rs
================
use super::query::TaxYearInterestByStateRow;
use leptos::*;
/// Leptos resource for fetching tax year interest-type revenue data grouped by state.
#[derive(Clone)]
pub(crate) struct TaxYearInterestByStateResource {
pub state_rows: Resource<(), Result<Vec<TaxYearInterestByStateRow>, ServerFnError>>,
}
use super::query::*;
impl TaxYearInterestByStateResource {
pub fn new() -> Self {
let state_rows = create_resource(
|| (),
|_| async move {
taxyearinterestbystate_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { state_rows }
}
}
================
File: gmm_client/src/tables/taxyearrevenueand1099s/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/taxyearrevenueand1099s/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row comparing revenue and 1099s for a client/operator.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct TaxYearRevenueAnd1099sRow {
pub client: String,
pub operator: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deducts: Option<f64>,
pub net_revenue: Option<f64>,
pub amount_1099: Option<f64>,
pub difference_dollars: Option<f64>,
pub difference_percent: Option<f64>,
pub material_difference: Option<String>,
}
/// Fetches reconciled revenue and 1099 values for the previous tax year.
#[server]
pub async fn taxyearrevenueand1099s_fetch_all() -> Result<Vec<TaxYearRevenueAnd1099sRow>, ServerFnError> {
pub use TaxYearRevenueAnd1099sRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, TaxYearRevenueAnd1099sRow>(
r#"
SELECT
combined_data.client,
CASE
WHEN combined_data.share_net_revenue < 600 THEN COALESCE(o1.name, o2.name, combined_data.operator) || '*'
ELSE COALESCE(o1.name, o2.name, combined_data.operator)
END AS operator_name,
combined_data.share_gross_revenue AS gross_revenue,
combined_data.share_taxes AS taxes,
combined_data.share_deductions AS deducts,
combined_data.share_net_revenue AS net_revenue,
combined_data.reported_amount AS amount_1099,
ROUND(combined_data."difference ($)", 2) AS difference_dollars,
CASE
WHEN combined_data.share_net_revenue = 0 THEN NULL
ELSE ROUND((combined_data."difference ($)" * 100.0) / combined_data.share_net_revenue, 2)
END AS difference_percent,
CASE
WHEN combined_data."difference ($)" = 0 THEN NULL
ELSE CASE
WHEN combined_data.reported_amount IS NULL OR combined_data.reported_amount = 0 THEN 'Outstanding 1099'
WHEN (combined_data.share_gross_revenue IS NULL OR combined_data.share_gross_revenue = 0)
AND (combined_data.share_taxes IS NULL OR combined_data.share_taxes = 0)
AND (combined_data.share_deductions IS NULL OR combined_data.share_deductions = 0)
AND (combined_data.share_net_revenue IS NULL OR combined_data.share_net_revenue = 0) THEN 'Outstanding Revenue'
WHEN combined_data.share_gross_revenue = combined_data.reported_amount
OR combined_data.share_net_revenue = combined_data.reported_amount THEN ''
WHEN (ABS(combined_data."difference ($)") * 100.0) / NULLIF(combined_data.share_net_revenue, 0) < 25 THEN 'No'
ELSE 'Yes'
END
END AS material_difference
FROM (
SELECT * FROM (
SELECT
COALESCE(rr.owner_name_client, i.payee) AS client,
COALESCE(rr.operator_purchaser, i.payor) AS operator,
rr.share_gross_revenue,
rr.share_taxes,
rr.share_deductions,
rr.share_net_revenue,
ROUND(SUM(i.reported_amount), 2) AS reported_amount,
ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) AS "difference ($)",
CASE WHEN 1.0 * rr.share_net_revenue = 0 THEN NULL
ELSE (ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) * 100.0) / (1.0 * rr.share_net_revenue)
END AS "difference (%)",
1 AS source_table
FROM
(SELECT DISTINCT payee, payor, 1.0 * REPLACE(reported_amount, ',', '') AS reported_amount, tax_year
FROM items1099
WHERE tax_year = 2024 AND box_name NOT LIKE '%withheld%' AND box_number NOT LIKE 'nil') AS i
LEFT JOIN
(SELECT owner_name_client, operator_purchaser,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS share_gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS share_taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS share_deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS share_net_revenue,
strftime('%Y', check_date), 2) AS year
FROM revenue_raw
WHERE owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND strftime('%Y', check_date) = '2024'
GROUP BY operator_purchaser, year) AS rr
ON rr.owner_name_client = i.payee AND rr.operator_purchaser = i.payor
GROUP BY client, operator, rr.share_gross_revenue, rr.share_taxes, rr.share_deductions, rr.share_net_revenue
UNION ALL
SELECT
COALESCE(rr.owner_name_client, i.payee) AS client,
COALESCE(rr.operator_purchaser, i.payor) AS operator,
rr.share_gross_revenue,
rr.share_taxes,
rr.share_deductions,
rr.share_net_revenue,
ROUND(SUM(i.reported_amount), 2) AS reported_amount,
ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) AS "difference ($)",
CASE WHEN 1.0 * rr.share_net_revenue = 0 THEN NULL
ELSE (ABS(1.0 * rr.share_gross_revenue - SUM(1.0 * i.reported_amount)) * 100.0) / (1.0 * rr.share_net_revenue)
END AS "difference (%)",
2 AS source_table
FROM
(SELECT payee, payor, 1.0 * REPLACE(reported_amount, ',', '') AS reported_amount, tax_year
FROM items1099
WHERE tax_year = 2024
AND payee = ?
AND box_name NOT LIKE '%withheld%'
AND box_number NOT LIKE 'nil') AS i
RIGHT JOIN
(SELECT owner_name_client, operator_purchaser,
ROUND(SUM(1.0 * share_gross_revenue), 2) AS share_gross_revenue,
ROUND(SUM(1.0 * share_taxes), 2) AS share_taxes,
ROUND(SUM(1.0 * share_deductions), 2) AS share_deductions,
ROUND(SUM(1.0 * share_net_revenue), 2) AS share_net_revenue,
strftime('%Y', check_date), 2) AS year
FROM revenue_raw
WHERE owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND strftime('%Y', check_date) = '2024'
GROUP BY owner_name_client, operator_purchaser, year) AS rr
ON rr.owner_name_client = i.payee AND rr.operator_purchaser = i.payor
GROUP BY client, operator, rr.share_gross_revenue, rr.share_taxes, rr.share_deductions, rr.share_net_revenue
) AS combined_data_with_dupes
GROUP BY client, operator, source_table
) AS combined_data
LEFT JOIN operator o1 ON o1.canonical = combined_data.operator
LEFT JOIN operator o2 ON o2.canonical = combined_data.operator
WHERE combined_data.client = ?
GROUP BY combined_data.client, operator_name, combined_data.share_gross_revenue, combined_data.share_taxes, combined_data.share_deductions, combined_data.share_net_revenue
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/taxyearrevenueand1099s/resource.rs
================
use leptos::*;
use super::query::TaxYearRevenueAnd1099sRow;
/// Leptos resource for comparing revenue and 1099 data.
#[derive(Clone)]
pub(crate) struct TaxYearRevenueAnd1099sResource {
pub rows: Resource<(), Result<Vec<TaxYearRevenueAnd1099sRow>, ServerFnError>>,
}
use super::query::*;
impl TaxYearRevenueAnd1099sResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
taxyearrevenueand1099s_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/taxyearwellsbystate/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/taxyearwellsbystate/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents tax year revenue summary by state for a client.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct TaxYearWellsByStateRow {
pub owner_name_client: String,
pub state: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub percentage_of_portfolio: Option<f64>,
}
/// Fetches full-year state-level tax data and computes percentage of revenue per state.
#[server]
pub async fn taxyearwellsbystate_fetch_all() -> Result<Vec<TaxYearWellsByStateRow>, ServerFnError> {
pub use TaxYearWellsByStateRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, TaxYearWellsByStateRow>(
r#"
WITH CalculatedValues AS (
SELECT
owner_name_client,
state,
1.0 * share_gross_revenue AS share_gross_revenue,
1.0 * share_taxes AS share_taxes,
1.0 * share_deductions AS share_deductions,
1.0 * share_net_revenue AS share_net_revenue
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', '-1 year', 'start of year')
AND check_date < date('now', 'start of year')
),
StateRevenue AS (
SELECT
owner_name_client,
state,
ROUND(SUM(share_gross_revenue), 2) AS gross_revenue,
ROUND(SUM(share_taxes), 2) AS taxes,
ROUND(SUM(share_deductions), 2) AS deductions,
ROUND(SUM(share_net_revenue), 2) AS net_revenue
FROM
CalculatedValues
GROUP BY
state
),
TotalRevenuePerClient AS (
SELECT
owner_name_client,
ROUND(SUM(net_revenue), 2) AS total_net_revenue
FROM
StateRevenue
)
SELECT
sr.owner_name_client,
sr.state,
sr.gross_revenue,
sr.taxes,
sr.deductions,
sr.net_revenue,
ROUND((sr.net_revenue * 100.0) / NULLIF(trc.total_net_revenue, 0), 2) AS percentage_of_portfolio
FROM
StateRevenue sr
JOIN
TotalRevenuePerClient trc ON sr.owner_name_client = trc.owner_name_client
ORDER BY
sr.state ASC
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/taxyearwellsbystate/resource.rs
================
use super::query::TaxYearWellsByStateRow;
use leptos::*;
/// Leptos resource for tax year revenue distribution by state.
#[derive(Clone)]
pub(crate) struct TaxYearWellsByStateResource {
pub rows: Resource<(), Result<Vec<TaxYearWellsByStateRow>, ServerFnError>>,
}
use super::query::*;
impl TaxYearWellsByStateResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
taxyearwellsbystate_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/wellops/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/wellops/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a well's operational and location details.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct WellOpsRow {
pub client: String,
pub api: String,
pub operator: String,
pub status: String,
pub well_name: String,
pub state: String,
pub county: String,
pub township: Option<String>,
pub land_range: Option<String>,
pub section: Option<String>,
pub portion_of_section: Option<String>,
pub name: Option<String>, // survey name
pub abstract_number: Option<String>,
pub block: Option<String>,
}
/// Fetches detailed information about wells and their operators.
#[server]
pub async fn wellops_fetch_all() -> Result<Vec<WellOpsRow>, ServerFnError> {
pub use WellOpsRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, WellOpsRow>(
r#"
SELECT
client.canonical AS client,
well.api_or_uwi AS api,
operator.name,
well_status.name AS status,
well.well_name,
well.state,
well.county,
well.township,
well.land_range,
well.section,
well.portion_of_section,
survey.name,
well.abstract_number,
well.block
FROM
well
JOIN
operator ON operator.id = well.operator_id
JOIN
well_status ON well_status.id = well.well_status_id
JOIN
well_interest ON well_interest.well_id = well.well_id
JOIN
client ON client.id = well_interest.client_id
JOIN
survey ON survey.id = well.survey_id
WHERE
client.canonical = ?
ORDER BY
operator.name
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/wellops/resource.rs
================
use super::query::WellOpsRow;
use leptos::*;
/// Leptos resource for fetching well operations and metadata.
#[derive(Clone)]
pub(crate) struct WellOpsResource {
pub rows: Resource<(), Result<Vec<WellOpsRow>, ServerFnError>>,
}
use super::query::*;
impl WellOpsResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
wellops_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/wellrev/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/wellrev/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents monthly revenue details for a well.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct WellRevRow {
pub owner_name_client: String,
pub operator: String,
pub well_property_name: String,
pub well_number: Option<String>,
pub production_date: Option<String>,
pub product: Option<String>,
pub unit_price: Option<f64>,
pub pie_gross_volume: Option<f64>,
pub owner_proportion: Option<f64>,
pub share_revenue: Option<f64>,
pub share_taxes: Option<f64>,
pub share_deductions: Option<f64>,
pub net_revenue: Option<f64>,
}
/// Fetches revenue data by well for the previous month.
#[server]
pub async fn wellrev_fetch_all() -> Result<Vec<WellRevRow>, ServerFnError> {
pub use WellRevRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, WellRevRow>(
r#"
SELECT
rr.owner_name_client,
o.name AS operator_name,
rr.well_property_name AS well_property_name,
rr.well_number,
rr.production_date,
rr.product,
1.0 * rr.unit_price AS unit_price,
1.0 * rr.pie_volume AS pie_gross_volume,
ROUND(rr.owner_decimal, 8) AS owner_proportion,
1.0 * rr.share_gross_revenue AS share_revenue,
1.0 * rr.share_taxes AS share_taxes,
1.0 * rr.share_deductions AS share_deductions,
1.0 * rr.share_net_revenue AS net_revenue
FROM
revenue_raw rr
JOIN
operator o ON o.canonical = rr.operator_purchaser
WHERE
rr.owner_name_client = ?
AND rr.aggregation_level IN ('i', 'e', 'h')
AND rr.check_date >= date('now', 'start of month', '-1 month')
AND rr.check_date < date('now', 'start of month')
ORDER BY
operator_name
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/wellrev/resource.rs
================
use super::query::WellRevRow;
use leptos::*;
/// Leptos resource for fetching last month’s revenue details by well.
#[derive(Clone)]
pub(crate) struct WellRevResource {
pub rows: Resource<(), Result<Vec<WellRevRow>, ServerFnError>>,
}
use super::query::*;
impl WellRevResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
wellrev_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/ytdcurrentyear/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/ytdcurrentyear/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents year-to-date revenue data grouped by month for each client.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct YTDCurrentYearRow {
pub owner_name_client: String,
pub month: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub depletion_15: Option<f64>,
pub net_post_depletion_15: Option<f64>,
pub depletion_275: Option<f64>,
pub net_post_depletion_275: Option<f64>,
}
/// Fetches YTD revenue summary with depletion values grouped by client and month.
#[server]
pub async fn ytdcurrentyear_fetch_all() -> Result<Vec<YTDCurrentYearRow>, ServerFnError> {
pub use YTDCurrentYearRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, YTDCurrentYearRow>(
r#"
WITH CalculatedValues AS (
SELECT
owner_name_client,
strftime('%Y', check_date) AS check_year,
strftime('%m', check_date) AS check_month,
1.0 * share_gross_revenue AS gross_revenue,
1.0 * share_taxes AS taxes,
1.0 * share_deductions AS deductions,
1.0 * share_net_revenue AS net_revenue,
1.0 * share_gross_revenue * 0.15 AS depletion_15,
1.0 * share_gross_revenue - (1.0 * share_gross_revenue * 0.15) AS net_post_depletion_15,
1.0 * share_gross_revenue * 0.275 AS depletion_275,
1.0 * share_gross_revenue - (1.0 * share_gross_revenue * 0.275) AS net_post_depletion_275
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', 'start of year')
AND check_date < date('now')
)
SELECT
owner_name_client,
CASE
WHEN check_month = '01' THEN 'Jan'
WHEN check_month = '02' THEN 'Feb'
WHEN check_month = '03' THEN 'Mar'
WHEN check_month = '04' THEN 'Apr'
WHEN check_month = '05' THEN 'May'
WHEN check_month = '06' THEN 'Jun'
WHEN check_month = '07' THEN 'Jul'
WHEN check_month = '08' THEN 'Aug'
WHEN check_month = '09' THEN 'Sep'
WHEN check_month = '10' THEN 'Oct'
WHEN check_month = '11' THEN 'Nov'
WHEN check_month = '12' THEN 'Dec'
END AS month,
ROUND(SUM(gross_revenue), 2) AS gross_revenue,
ROUND(SUM(taxes), 2) AS taxes,
ROUND(SUM(deductions), 2) AS deductions,
ROUND(SUM(net_revenue), 2) AS net_revenue,
ROUND(SUM(depletion_15), 2) AS depletion_15,
ROUND(SUM(net_post_depletion_15), 2) AS net_post_depletion_15,
ROUND(SUM(depletion_275), 2) AS depletion_275,
ROUND(SUM(net_post_depletion_275), 2) AS net_post_depletion_275
FROM
CalculatedValues
GROUP BY
check_month
ORDER BY
CAST(check_month AS INTEGER)
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/ytdcurrentyear/resource.rs
================
use super::query::YTDCurrentYearRow;
use leptos::*;
/// Leptos resource for fetching year-to-date revenue summaries.
#[derive(Clone)]
pub(crate) struct YTDCurrentYearResource {
pub rows: Resource<(), Result<Vec<YTDCurrentYearRow>, ServerFnError>>,
}
use super::query::*;
impl YTDCurrentYearResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
ytdcurrentyear_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/ytdpasttaxyear/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/ytdpasttaxyear/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of YTD revenue data for the past tax year.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct YTDPastTaxYearRow {
pub owner_name_client: String,
pub month: String,
pub gross_revenue: Option<f64>,
pub taxes: Option<f64>,
pub deductions: Option<f64>,
pub net_revenue: Option<f64>,
pub depletion_15: Option<f64>,
pub net_post_depletion_15: Option<f64>,
pub depletion_275: Option<f64>,
pub net_post_depletion_275: Option<f64>,
}
/// Fetches YTD revenue summary for the previous calendar year grouped by client and month.
#[server]
pub async fn ytdpasttaxyear_fetch_all() -> Result<Vec<YTDPastTaxYearRow>, ServerFnError> {
pub use YTDPastTaxYearRow;
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, YTDPastTaxYearRow>(
r#"
WITH CalculatedValues AS (
SELECT
owner_name_client,
strftime('%Y', check_date) AS check_year,
strftime('%m', check_date) AS check_month,
1.0 * share_gross_revenue AS gross_revenue,
1.0 * share_taxes AS taxes,
1.0 * share_deductions AS deductions,
1.0 * share_net_revenue AS net_revenue,
1.0 * share_gross_revenue * 0.15 AS depletion_15,
1.0 * share_gross_revenue - (1.0 * share_gross_revenue * 0.15) AS net_post_depletion_15,
1.0 * share_gross_revenue * 0.275 AS depletion_275,
1.0 * share_gross_revenue - (1.0 * share_gross_revenue * 0.275) AS net_post_depletion_275
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', 'start of year', '-1 year')
AND check_date < date('now', 'start of year')
)
SELECT
owner_name_client,
CASE
WHEN check_month = '01' THEN 'Jan'
WHEN check_month = '02' THEN 'Feb'
WHEN check_month = '03' THEN 'Mar'
WHEN check_month = '04' THEN 'Apr'
WHEN check_month = '05' THEN 'May'
WHEN check_month = '06' THEN 'Jun'
WHEN check_month = '07' THEN 'Jul'
WHEN check_month = '08' THEN 'Aug'
WHEN check_month = '09' THEN 'Sep'
WHEN check_month = '10' THEN 'Oct'
WHEN check_month = '11' THEN 'Nov'
WHEN check_month = '12' THEN 'Dec'
END AS month,
ROUND(SUM(gross_revenue), 2) AS gross_revenue,
ROUND(SUM(taxes), 2) AS taxes,
ROUND(SUM(deductions), 2) AS deductions,
ROUND(SUM(net_revenue), 2) AS net_revenue,
ROUND(SUM(depletion_15), 2) AS depletion_15,
ROUND(SUM(net_post_depletion_15), 2) AS net_post_depletion_15,
ROUND(SUM(depletion_275), 2) AS depletion_275,
ROUND(SUM(net_post_depletion_275), 2) AS net_post_depletion_275
FROM
CalculatedValues
GROUP BY
check_month
ORDER BY
CAST(check_month AS INTEGER)
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/ytdpasttaxyear/resource.rs
================
use super::query::YTDPastTaxYearRow;
use leptos::*;
/// Leptos resource for fetching YTD revenue summaries for the previous tax year.
#[derive(Clone)]
pub(crate) struct YtdPastTaxYearResource {
pub rows: Resource<(), Result<Vec<YTDPastTaxYearRow>, ServerFnError>>, // ✅ updated
}
use super::query::*;
impl YtdPastTaxYearResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
ytdpasttaxyear_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/ytdpfibyoperator/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/ytdpfibyoperator/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct YTDPFIByOperatorRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub oil_volume: Option<f64>,
pub natural_gas_volume: Option<f64>,
pub oil_volume_60: Option<f64>,
pub oil_volume_75: Option<f64>,
pub oil_volume_90: Option<f64>,
pub natural_gas_volume_2: Option<f64>,
pub natural_gas_volume_25: Option<f64>,
pub natural_gas_volume_3: Option<f64>,
}
#[server]
pub async fn ytdpfibyoperator_fetch_all() -> Result<Vec<YTDPFIByOperatorRow>, ServerFnError> {
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, YTDPFIByOperatorRow>(
r#"
WITH CalculatedVolumes AS (
SELECT
owner_name_client,
operator_purchaser,
ROUND(SUM(CASE WHEN product = 'oil' THEN 1.0 * share_volume / 12.0 ELSE 0 END), 2) AS oil_volume,
ROUND(SUM(CASE WHEN product = 'natural gas' THEN 1.0 * share_volume / 12.0 ELSE 0 END), 2) AS natural_gas_volume
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND check_date >= date('now', '-12 month')
AND product IN ('oil', 'natural gas')
GROUP BY
operator_purchaser
)
SELECT
cv.owner_name_client,
o.name AS operator_name,
cv.oil_volume,
cv.natural_gas_volume,
cv.oil_volume * 60 AS oil_volume_60,
cv.oil_volume * 75 AS oil_volume_75,
cv.oil_volume * 90 AS oil_volume_90,
cv.natural_gas_volume * 2 AS natural_gas_volume_2,
cv.natural_gas_volume * 2.50 AS natural_gas_volume_25,
cv.natural_gas_volume * 3 AS natural_gas_volume_3
FROM
CalculatedVolumes cv
JOIN
operator o ON o.canonical = cv.operator_purchaser
WHERE
cv.oil_volume != 0 OR cv.natural_gas_volume != 0
ORDER BY
operator_name ASC
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/ytdpfibyoperator/resource.rs
================
use super::query::YTDPFIByOperatorRow;
use leptos::*;
#[derive(Clone)]
pub(crate) struct YtdPfiByOperatorResource {
pub rows: Resource<(), Result<Vec<YTDPFIByOperatorRow>, ServerFnError>>, // ✅ updated
}
use super::query::*;
impl YtdPfiByOperatorResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
ytdpfibyoperator_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/ytdpfibyproduct/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/ytdpfibyproduct/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of average net revenue per product category.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct YTDPFIByProductRow {
pub owner_name_client: String,
pub product_category: String,
pub last_month_avg_share_net: Option<f64>,
pub three_months_avg_share_net: Option<f64>,
pub six_months_avg_share_net: Option<f64>,
pub twelve_months_avg_share_net: Option<f64>,
}
#[server]
pub async fn ytdpfibyproduct_fetch_all() -> Result<Vec<YTDPFIByProductRow>, ServerFnError> {
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, YTDPFIByProductRow>(
r#"
WITH ranked_products AS (
SELECT
owner_name_client,
CASE
WHEN product = 'oil' THEN 'oil'
WHEN product = 'natural gas' THEN 'natural gas'
ELSE 'other'
END AS product_category,
ROUND(SUM(CASE WHEN strftime('%Y-%m', check_date) >= strftime('%Y-%m', 'now', '-1 month') THEN 1.0 * share_net_revenue ELSE 0 END), 2) AS last_month_avg_share_net,
ROUND(SUM(CASE WHEN strftime('%Y-%m', check_date) >= strftime('%Y-%m', 'now', '-3 month') THEN 1.0 * share_net_revenue ELSE 0 END) / 3.0, 2) AS three_months_avg_share_net,
ROUND(SUM(CASE WHEN strftime('%Y-%m', check_date) >= strftime('%Y-%m', 'now', '-6 month') THEN 1.0 * share_net_revenue ELSE 0 END) / 6.0, 2) AS six_months_avg_share_net,
ROUND(SUM(CASE WHEN strftime('%Y-%m', check_date) >= strftime('%Y-%m', 'now', '-12 month') THEN 1.0 * share_net_revenue ELSE 0 END) / 12.0, 2) AS twelve_months_avg_share_net,
RANK() OVER (
PARTITION BY owner_name_client
ORDER BY
SUM(CASE WHEN strftime('%Y-%m', check_date) >= strftime('%Y-%m', 'now', '-12 month') THEN 1.0 * share_net_revenue ELSE 0 END) DESC
) AS rank_num
FROM revenue_raw
WHERE owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
GROUP BY product_category
)
SELECT
owner_name_client,
product_category,
last_month_avg_share_net,
three_months_avg_share_net,
six_months_avg_share_net,
twelve_months_avg_share_net
FROM ranked_products
WHERE rank_num <= 3
ORDER BY product_category
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/ytdpfibyproduct/resource.rs
================
use super::query::YTDPFIByProductRow;
use leptos::*;
#[derive(Clone)]
pub(crate) struct YtdPfiByProductResource {
pub rows: Resource<(), Result<Vec<YTDPFIByProductRow>, ServerFnError>>, // ✅ updated
}
use super::query::*;
impl YtdPfiByProductResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
ytdpfibyproduct_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/ytdrevenuebyoperator/mod.rs
================
pub(crate) mod query;
pub(crate) mod resource;
================
File: gmm_client/src/tables/ytdrevenuebyoperator/query.rs
================
use leptos::*;
use serde::{Deserialize, Serialize};
/// Represents a row of YTD revenue totals per operator.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct YTDRevenueByOperatorRow {
pub owner_name_client: String,
pub operator_purchaser: String,
pub total_share_net: Option<f64>,
}
#[server]
pub async fn ytdrevenuebyoperator_fetch_all() -> Result<Vec<YTDRevenueByOperatorRow>, ServerFnError>
{
let land_pool = crate::state::ssr::app_pools()?.land_pool;
let records = sqlx::query_as::<_, YTDRevenueByOperatorRow>(
r#"
WITH RevenueTotals AS (
SELECT
owner_name_client,
operator_purchaser,
ROUND(SUM(1.0 * share_net_revenue), 2) AS total_share_net
FROM
revenue_raw
WHERE
owner_name_client = ?
AND aggregation_level IN ('i', 'e', 'h')
AND strftime('%Y', check_date) = strftime('%Y', 'now')
GROUP BY
operator_purchaser
)
SELECT
rt.owner_name_client,
o.name AS operator_name,
rt.total_share_net
FROM
RevenueTotals rt
JOIN
operator o ON o.canonical = rt.operator_purchaser
ORDER BY
operator_name
"#
)
.fetch_all(&land_pool)
.await
// log successful row count, then pass rows through
.map(|rows| {
log::debug!("Query succeeded: {} rows", rows.len());
rows
})
.map_err(|e| {
log::error!("Query failed: {}", e);
ServerFnError::new(format!("Database error: {}", e))
})?;
Ok(records)
}
================
File: gmm_client/src/tables/ytdrevenuebyoperator/resource.rs
================
use super::query::YTDRevenueByOperatorRow;
use leptos::*;
#[derive(Clone)]
pub(crate) struct YtdRevenueByOperatorResource {
pub rows: Resource<(), Result<Vec<YTDRevenueByOperatorRow>, ServerFnError>>, // ✅ updated
}
use super::query::*;
impl YtdRevenueByOperatorResource {
pub fn new() -> Self {
let rows = create_resource(
|| (),
|_| async move {
ytdrevenuebyoperator_fetch_all()
.await
.map_err(|e| ServerFnError::new(format!("Database error: {}", e)))
},
);
Self { rows }
}
}
================
File: gmm_client/src/tables/mod.rs
================
// pub(crate) mod revenue;
pub(crate) mod assets;
pub(crate) mod dashboardtotalreportedpayments;
pub(crate) mod dashboardjibs;
pub(crate) mod dashboardrevenue;
pub(crate) mod incominglocation;
pub(crate) mod incomingproducts;
pub(crate) mod incomingtable1;
pub(crate) mod incomingtable2;
// pub(crate) mod iwv_qc_queries;
pub(crate) mod jibdetails;
pub(crate) mod jibnetting;
pub(crate) mod jibsummary;
pub(crate) mod leases;
pub(crate) mod pieleasestatus;
pub(crate) mod pieproducingwellscounty;
pub(crate) mod pieproducingwellsstate;
pub(crate) mod pierevenuebycounty;
pub(crate) mod pierevenuebyoperator;
pub(crate) mod pierevenuebyproduct;
pub(crate) mod pierevenuebystate;
pub(crate) mod piewellstatus;
pub(crate) mod revjibdetails;
pub(crate) mod revjibsummary;
pub(crate) mod statslistofcounts;
pub(crate) mod statsproduction;
pub(crate) mod statstopoperators;
// pub(crate) mod surfacerevenue;
pub(crate) mod taxyear1099details;
pub(crate) mod taxyearfullyeartotals;
pub(crate) mod taxyearinterestbystate;
pub(crate) mod taxyearrevenueand1099s;
pub(crate) mod taxyearwellsbystate;
pub(crate) mod wellops;
pub(crate) mod wellrev;
pub(crate) mod ytdcurrentyear;
pub(crate) mod ytdpasttaxyear;
pub(crate) mod ytdpfibyoperator;
pub(crate) mod ytdpfibyproduct;
pub(crate) mod ytdrevenuebyoperator;
================================================================
End of Codebase
================================================================

Add Comment

Please, Sign In to add comment