# edited by glg import sqlite3 import threading from datetime import datetime, time, date, timedelta from pypos.core.base_model import BaseModel from pypos.core.utils.db_helper import connect_sqlite from pypos.core.utils.config_utils import read_config from pypos.core.utils.path_utils import get_db_path from pypos.core.utils.sql_identifier_utils import quote_sql_identifier from pypos.core.utils.sql_query_builder import ( build_insert_sql, build_sql_with_identifier_in_clause, render_sql_template, ) from pypos.core.database.schema_migrator import run_schema_migrations_once from pypos.modules.penjualan.errors import SettlementProcessError # upgraded: inherit base class class SettlementModel(BaseModel): _PERF_INDEX_INIT_LOCK = threading.Lock() _PERF_INDEX_READY = False def get_detail_transaksi_per_hari_kasir(self, tanggal, kasir): """ # PATCH[FrontEndAgent|DetailSettlement]: Query detail transaksi per hari per kasir # Menampilkan ID, Waktu, Customer, Total, Metode untuk semua transaksi belum settle pada tanggal/kasir tertentu # Reasoning: Untuk menampilkan detail transaksi pada dialog detail dari settlement # PATCH[FrontEndAgent|FixCustomerField]: Gunakan field customers_nama langsung dari tabel transaksi # PATCH[FrontEndAgent|FixFieldNames]: Gunakan transaksi_bulat (bukan total) dan transaksi_jenis (bukan metode) """ conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) self._ensure_return_refund_columns(cursor) self._ensure_voucher_tables(cursor) refund_subquery = """ SELECT r.transaksi_id, SUM(COALESCE(td.produk_ord_hrg, dr.harga, 0) * COALESCE(dr.jumlah, 0)) AS total_refund FROM return_transaksi_penjualan r JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id LEFT JOIN transaksi_data td ON td.transaksi_id = r.transaksi_id AND td.produk_id = dr.produk_id WHERE COALESCE(r.refund_method, 'cash') = 'cash' GROUP BY r.transaksi_id """ query = render_sql_template( """ SELECT t.id, t.nomer AS nomer, strftime('%Y-%m-%d %H:%M:%S', t.dtime) AS waktu, COALESCE(t.customers_nama, 'Tunai') AS customer, MAX( 0, COALESCE(t.transaksi_nilai, t.transaksi_bulat, t.transaksi_dibayar, 0) - COALESCE(r.total_refund, 0) - COALESCE(v.total_voucher, 0) ) AS total, COALESCE(t.bank_nama, t.transaksi_jenis, 'Tunai') AS metode, COALESCE(t.oleh_nama, '-') AS kasir FROM transaksi t LEFT JOIN ({refund_subquery}) r ON r.transaksi_id = t.id LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id WHERE t.oleh_nama = ? AND t.settlement_id = 1 AND IFNULL(t.trash, 0) = 0 ORDER BY t.dtime DESC """, refund_subquery=refund_subquery, voucher_subquery=self._voucher_subquery(), ) params = [kasir] day_range = self._build_day_range(tanggal) if day_range: query = query.replace( "WHERE t.oleh_nama = ? AND t.settlement_id = 1", "WHERE t.oleh_nama = ? AND t.settlement_id = 1 AND t.dtime >= ? AND t.dtime < ?", ) params.extend([day_range[0], day_range[1]]) else: query = query.replace( "WHERE t.oleh_nama = ? AND t.settlement_id = 1", "WHERE t.oleh_nama = ? AND t.settlement_id = 1 AND DATE(t.dtime) = ?", ) params.append(tanggal) cursor.execute(query, params) rows = cursor.fetchall() conn.close() return [dict(row) for row in rows] def __init__( self, db_path=None, settlement_history_service=None, settlement_lock_map_service=None, settlement_finance_calculation_service=None, ): super().__init__() self.db_path = db_path or get_db_path() # edited by glg # HMVC strict: domain histori dan lock-map dipisah ke service. self.settlement_history_service = ( settlement_history_service or self._build_default_settlement_history_service() ) self.settlement_lock_map_service = ( settlement_lock_map_service or self._build_default_settlement_lock_map_service() ) # edited by glg # Dekomposisi hotspot monolitik settlement: perhitungan finansial dipisah ke service. self.settlement_finance_calculation_service = ( settlement_finance_calculation_service or self._build_default_settlement_finance_calculation_service() ) self._auth_service_cls = None # edited by glg def _build_default_settlement_history_service(self): module = __import__( "pypos.modules.penjualan.services.settlement_history_service", fromlist=["SettlementHistoryService"], ) return module.SettlementHistoryService(self) # edited by glg def _build_default_settlement_lock_map_service(self): module = __import__( "pypos.modules.penjualan.services.settlement_lock_map_service", fromlist=["SettlementLockMapService"], ) return module.SettlementLockMapService(self) # edited by glg def _build_default_settlement_finance_calculation_service(self): module = __import__( "pypos.modules.penjualan.services.settlement_finance_calculation_service", fromlist=["SettlementFinanceCalculationService"], ) return module.SettlementFinanceCalculationService() # edited by glg def _get_auth_service_cls(self): if self._auth_service_cls is not None: return self._auth_service_cls module = __import__( "pypos.modules.auth.services.auth_service", fromlist=["AuthService"], ) self._auth_service_cls = module.AuthService return self._auth_service_cls def connect(self): return connect_sqlite(self.db_path) # edited by glg def _ensure_schema_ready_or_fail(self, reason_code="SETTLEMENT_SCHEMA_MIGRATION_FAILED"): ok = run_schema_migrations_once(self.db_path, strict=False) # Kompatibilitas: migrator lama dapat return None untuk kondisi sukses/no-op. if ok is not False: return True message = ( f"{reason_code}: run_schema_migrations_once(strict=False) mengembalikan False " f"untuk db_path={self.db_path}" ) self.log_error(message) raise RuntimeError(message) # edited by glg @staticmethod def _resolve_positive_limit(value, default=0): try: parsed = int(value) except (TypeError, ValueError): parsed = int(default or 0) return parsed if parsed > 0 else 0 # edited by glg def _get_settlement_history_query_limit(self): cfg = read_config() or {} return self._resolve_positive_limit( cfg.get("settlement_history_max_rows", 1200), default=1200, ) # edited by glg def _repair_duplicate_settlement_counters(self, cursor): cursor.execute( """ SELECT counter FROM transaksi_settlement WHERE counter IS NOT NULL AND TRIM(counter) <> '' GROUP BY counter HAVING COUNT(1) > 1 """ ) duplicate_rows = cursor.fetchall() or [] for row in duplicate_rows: counter_value = str((row[0] if row else "") or "").strip() if not counter_value: continue cursor.execute( """ SELECT id FROM transaksi_settlement WHERE counter = ? ORDER BY id ASC """, (counter_value,), ) ids = [int(item[0] or 0) for item in (cursor.fetchall() or []) if item] if len(ids) <= 1: continue # Keep id pertama sebagai canonical; sisanya dipindah ke namespace legacy unik. for duplicate_id in ids[1:]: if duplicate_id <= 0: continue repaired_counter = f"{counter_value}-LEGACY-{duplicate_id}" cursor.execute( "UPDATE transaksi_settlement SET counter = ? WHERE id = ?", (repaired_counter, duplicate_id), ) self.log_warning( f"[SETTLEMENT_COUNTER_DUPLICATE_REPAIRED] id={duplicate_id} " f"counter_baru={repaired_counter}" ) # edited by glg # Index performa settlement agar filter dtime/kasir tidak terus full-scan saat data membesar. def _ensure_performance_indexes(self, cursor): cls = self.__class__ if bool(getattr(cls, "_PERF_INDEX_READY", False)): return with cls._PERF_INDEX_INIT_LOCK: if bool(getattr(cls, "_PERF_INDEX_READY", False)): return try: cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_filter_dtime ON transaksi(settlement_id, trash, dtime) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_filter_kasir_dtime ON transaksi(settlement_id, trash, oleh_nama, dtime) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_filter_bank_dtime ON transaksi(settlement_id, trash, bank_nama, dtime) """ ) # edited by glg # Expression index untuk pola IFNULL(trash,0) agar filter settlement tidak membuat temp sort. cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_expr_dtime ON transaksi(settlement_id, IFNULL(trash, 0), dtime) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_expr_kasir_dtime ON transaksi(settlement_id, IFNULL(trash, 0), oleh_nama, dtime) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_expr_bank_dtime ON transaksi(settlement_id, IFNULL(trash, 0), bank_nama, dtime) """ ) # edited by glg # Index ekspresi tanggal untuk agregasi settlement per hari/kasir. cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_expr_hari_kasir ON transaksi(settlement_id, IFNULL(trash, 0), DATE(dtime), oleh_nama) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_transaksi_settlement_oleh_dtime ON transaksi_settlement(oleh_id, oleh_dtime) """ ) self._repair_duplicate_settlement_counters(cursor) cursor.execute( """ CREATE UNIQUE INDEX IF NOT EXISTS idx_transaksi_settlement_counter_unique ON transaksi_settlement(counter) WHERE counter IS NOT NULL AND TRIM(counter) <> '' """ ) # edited by glg # Pastikan tabel settlement_history sudah siap sebelum index histori dibuat. self._ensure_settlement_history_columns(cursor) self._ensure_settlement_history_map_table(cursor) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_settlement_history_tanggal_id ON settlement_history(tanggal DESC, id DESC) """ ) cursor.connection.commit() cls._PERF_INDEX_READY = True except (sqlite3.Error, RuntimeError, ValueError, TypeError) as exc: self.log_warning(f"[SETTLEMENT_PERF_INDEX_ERROR] Gagal membuat index performa settlement: {exc}") # edited by glg @staticmethod def _build_day_range(day_value): try: base = datetime.strptime(str(day_value or "").strip(), "%Y-%m-%d") except (TypeError, ValueError): return None start_text = base.strftime("%Y-%m-%d 00:00:00") end_text = (base + timedelta(days=1)).strftime("%Y-%m-%d 00:00:00") return start_text, end_text # edited by glg def _build_today_cutoff(self, include_today=True): today_start = datetime.combine(date.today(), time.min) if include_today: cutoff = today_start + timedelta(days=1) else: cutoff = today_start return cutoff.strftime("%Y-%m-%d %H:%M:%S") # edited by glg def _build_datetime_range(self, start_date, end_date): start_bounds = self._build_day_range(start_date) end_bounds = self._build_day_range(end_date) if not start_bounds or not end_bounds: return None try: start_dt = datetime.strptime(start_bounds[0], "%Y-%m-%d %H:%M:%S") end_dt = datetime.strptime(end_bounds[0], "%Y-%m-%d %H:%M:%S") except (TypeError, ValueError): return None if end_dt < start_dt: start_dt, end_dt = end_dt, start_dt return ( start_dt.strftime("%Y-%m-%d %H:%M:%S"), (end_dt + timedelta(days=1)).strftime("%Y-%m-%d %H:%M:%S"), ) # edited by glg def _normalize_day_values(self, values): out = [] seen = set() for raw in values or []: text = str(raw or "").strip() if not text or text in seen: continue bounds = self._build_day_range(text) if not bounds: continue seen.add(text) out.append(text) return out # edited by glg def _append_day_list_filter(self, query, params, column_sql, day_values): normalized_days = self._normalize_day_values(day_values) if normalized_days: clauses = [] for day_text in normalized_days: day_range = self._build_day_range(day_text) if not day_range: continue clauses.append(f"({column_sql} >= ? AND {column_sql} < ?)") params.extend([day_range[0], day_range[1]]) if clauses: query += " AND (" + " OR ".join(clauses) + ")" return query, params if day_values: placeholders = ",".join(["?"] * len(day_values)) query += f" AND DATE({column_sql}) IN ({placeholders})" params.extend(day_values) return query, params def _ensure_pembayaran_non_tunai_column(self, cursor): cursor.execute("PRAGMA table_info(transaksi)") cols = [row[1] for row in cursor.fetchall()] if "pembayaran_non_tunai" not in cols: cursor.execute( "ALTER TABLE transaksi ADD COLUMN pembayaran_non_tunai REAL DEFAULT 0" ) def _ensure_settlement_history_columns(self, cursor): _ = cursor self._ensure_schema_ready_or_fail("SETTLEMENT_SCHEMA_HISTORY_MIGRATION_FAILED") # edited by glg # Map relasi settlement_history -> transaksi_id untuk lock-check cepat tanpa parse JSON berulang. def _ensure_settlement_history_map_table(self, cursor): self._ensure_settlement_history_columns(cursor) cursor.execute( """ CREATE TABLE IF NOT EXISTS settlement_history_transaksi_map ( settlement_history_id INTEGER NOT NULL, transaksi_id INTEGER NOT NULL, PRIMARY KEY (settlement_history_id, transaksi_id) ) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_settlement_history_map_history_id ON settlement_history_transaksi_map(settlement_history_id) """ ) cursor.execute( """ CREATE INDEX IF NOT EXISTS idx_settlement_history_map_transaksi_id ON settlement_history_transaksi_map(transaksi_id) """ ) # edited by glg def _insert_settlement_history_map_rows(self, cursor, settlement_history_id, transaksi_ids): history_id = self._as_positive_int(settlement_history_id, 0) if history_id <= 0: return normalized_ids = self._normalize_transaksi_ids(transaksi_ids) if not normalized_ids: return cursor.executemany( """ INSERT OR IGNORE INTO settlement_history_transaksi_map (settlement_history_id, transaksi_id) VALUES (?, ?) """, [(history_id, int(trx_id)) for trx_id in normalized_ids], ) def _ensure_return_refund_columns(self, cursor): _ = cursor self._ensure_schema_ready_or_fail("SETTLEMENT_SCHEMA_RETURN_MIGRATION_FAILED") def _ensure_voucher_tables(self, cursor): _ = cursor self._ensure_schema_ready_or_fail("SETTLEMENT_SCHEMA_VOUCHER_MIGRATION_FAILED") # edited by glg def _as_positive_int(self, value, default=0): try: parsed = int(str(value).strip()) except (TypeError, ValueError): parsed = int(default or 0) return parsed if parsed > 0 else 0 # edited by glg def _is_blank_value(self, value): if value is None: return True text = str(value).strip().lower() return text in {"", "none", "null"} # edited by glg def _get_table_columns(self, cursor, table_name): try: table_sql = quote_sql_identifier(table_name) cursor.execute(f"PRAGMA table_info({table_sql})") return {str(row[1]) for row in cursor.fetchall() if row and len(row) > 1} except (sqlite3.Error, ValueError): return set() # edited by glg def _lookup_cabang_nama(self, cursor, cabang_id): cabang_int = self._as_positive_int(cabang_id, 0) if cabang_int <= 0: return "" try: cursor.execute( """ SELECT nama FROM per_cabang WHERE id = ? AND COALESCE(status, 1) = 1 AND IFNULL(trash, 0) = 0 LIMIT 1 """, (cabang_int,), ) row = cursor.fetchone() if row and row[0]: return str(row[0]).strip() except (sqlite3.Error, TypeError, ValueError) as exc: self.log_warning(f"[SETTLEMENT_LOOKUP_CABANG_ERROR] cabang_id={cabang_int} err={exc}") return "" return "" # edited by glg def _resolve_ppn_mode_sql_expr(self, cursor, alias="t"): table_cols = self._get_table_columns(cursor, "transaksi") table_alias = str(alias or "t").strip() or "t" has_ppn_mode = "ppn_mode" in table_cols has_diskon_log = "diskon_log" in table_cols if has_ppn_mode and has_diskon_log: return ( "CASE " f"WHEN LOWER(COALESCE({table_alias}.ppn_mode, '')) = 'include' THEN 'include' " f"WHEN LOWER(COALESCE({table_alias}.ppn_mode, '')) = 'exclude' THEN 'exclude' " f"WHEN LOWER(COALESCE({table_alias}.diskon_log, '')) LIKE '%ppn_mode=include%' THEN 'include' " "ELSE 'exclude' END" ) if has_ppn_mode: return ( "CASE " f"WHEN LOWER(COALESCE({table_alias}.ppn_mode, '')) = 'include' THEN 'include' " "ELSE 'exclude' END" ) if has_diskon_log: return ( "CASE " f"WHEN LOWER(COALESCE({table_alias}.diskon_log, '')) LIKE '%ppn_mode=include%' THEN 'include' " "ELSE 'exclude' END" ) return "'exclude'" # edited by glg def _get_ppn_mode_breakdown(self, cursor, transaksi_ids, refund_subquery): if not transaksi_ids: return [] mode_expr = self._resolve_ppn_mode_sql_expr(cursor, alias="t") ppn_prefix = render_sql_template( """ SELECT mode_key AS ppn_mode, COUNT(1) AS jumlah_transaksi, COALESCE(SUM(nilai_bersih), 0) AS total_nilai FROM ( SELECT t.id, {mode_expr} AS mode_key, MAX( 0, COALESCE(t.transaksi_nilai, 0) - COALESCE(r.total_refund, 0) - COALESCE(v.total_voucher, 0) ) AS nilai_bersih FROM transaksi t LEFT JOIN ({refund_subquery}) r ON r.transaksi_id = t.id LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id WHERE """, mode_expr=mode_expr, refund_subquery=refund_subquery, voucher_subquery=self._voucher_subquery(), ) ppn_suffix = render_sql_template( "AND IFNULL(t.trash, 0) = 0 GROUP BY t.id, {mode_expr}) x GROUP BY mode_key ORDER BY mode_key ASC", mode_expr=mode_expr, ) query, query_params = build_sql_with_identifier_in_clause( ppn_prefix, "t.id", transaksi_ids, cast_int=True, positive_only=True, unique=True, sql_suffix=ppn_suffix, ) cursor.execute(query, query_params) rows = cursor.fetchall() or [] return [dict(row) if isinstance(row, sqlite3.Row) else row for row in rows] # edited by glg def _extract_transaksi_ids_blob(self, raw_blob): import json if self._is_blank_value(raw_blob): return [] text = str(raw_blob).strip() try: payload = json.loads(text) except (TypeError, ValueError): payload = [] if not isinstance(payload, list): payload = [] out = [] for item in payload: try: parsed = int(item) except (TypeError, ValueError): continue if parsed > 0: out.append(parsed) return out # edited by glg def _build_transaksi_settlement_insert_payload( self, cursor, *, counter, oleh_id, oleh_dtime, data_transaksi_id, cabang_id, oleh_nama, cabang_nama, approval_id=0, approval_nama="", approval_counter="0", ): table_cols = self._get_table_columns(cursor, "transaksi_settlement") if not table_cols: return {} payload = { "counter": counter, "oleh_id": oleh_id, "oleh_dtime": oleh_dtime, "approval_id": self._as_positive_int(approval_id, 0), "approval_counter": str(approval_counter or "0").strip() or "0", "approval_nama": str(approval_nama or "").strip(), "status": 1, "data_transaksi_id": data_transaksi_id, "cabang_id": cabang_id, "oleh_nama": oleh_nama, "cabang_nama": cabang_nama, } return {k: v for k, v in payload.items() if k in table_cols} # edited by glg def backfill_transaksi_settlement_rows(self): import json conn = self.connect() conn.row_factory = sqlite3.Row cur = conn.cursor() updated = 0 try: cols = self._get_table_columns(cur, "transaksi_settlement") if not cols: return 0 cur.execute("SELECT * FROM transaksi_settlement ORDER BY id ASC") rows = cur.fetchall() or [] for row in rows: row_dict = dict(row) row_id = self._as_positive_int(row_dict.get("id"), 0) if row_id <= 0: continue transaksi_ids = self._extract_transaksi_ids_blob(row_dict.get("data_transaksi_id")) parent_ctx = {} if transaksi_ids: parent_query, parent_params = build_sql_with_identifier_in_clause( "SELECT id, dtime, oleh_id, oleh_nama, cabang_id, cabang_nama FROM transaksi WHERE", "id", transaksi_ids, cast_int=True, positive_only=True, unique=True, sql_suffix="ORDER BY datetime(dtime) ASC LIMIT 1", ) cur.execute(parent_query, parent_params) parent = cur.fetchone() if parent: parent_ctx = dict(parent) update_map = {} if "counter" in cols and self._is_blank_value(row_dict.get("counter")): update_map["counter"] = f"ST-LEGACY-{row_id:06d}" if "status" in cols and self._is_blank_value(row_dict.get("status")): update_map["status"] = 1 if "approval_id" in cols and self._is_blank_value(row_dict.get("approval_id")): update_map["approval_id"] = 0 if "approval_counter" in cols and self._is_blank_value(row_dict.get("approval_counter")): update_map["approval_counter"] = "0" if "data_transaksi_id" in cols and self._is_blank_value(row_dict.get("data_transaksi_id")): update_map["data_transaksi_id"] = json.dumps(transaksi_ids) resolved_oleh_id = self._as_positive_int(row_dict.get("oleh_id"), 0) if resolved_oleh_id <= 0: resolved_oleh_id = self._as_positive_int(parent_ctx.get("oleh_id"), 0) if "oleh_id" in cols and resolved_oleh_id > 0 and self._is_blank_value(row_dict.get("oleh_id")): update_map["oleh_id"] = resolved_oleh_id resolved_oleh_nama = str(row_dict.get("oleh_nama") or "").strip() if not resolved_oleh_nama: resolved_oleh_nama = str(parent_ctx.get("oleh_nama") or "").strip() if not resolved_oleh_nama: resolved_oleh_nama = "-" if "oleh_nama" in cols and self._is_blank_value(row_dict.get("oleh_nama")): update_map["oleh_nama"] = resolved_oleh_nama resolved_cabang_id = self._as_positive_int(row_dict.get("cabang_id"), 0) if resolved_cabang_id <= 0: resolved_cabang_id = self._as_positive_int(parent_ctx.get("cabang_id"), 0) if "cabang_id" in cols and resolved_cabang_id > 0 and self._is_blank_value(row_dict.get("cabang_id")): update_map["cabang_id"] = resolved_cabang_id resolved_cabang_nama = str(row_dict.get("cabang_nama") or "").strip() if not resolved_cabang_nama: resolved_cabang_nama = str(parent_ctx.get("cabang_nama") or "").strip() if not resolved_cabang_nama: resolved_cabang_nama = self._lookup_cabang_nama(cur, resolved_cabang_id) if "cabang_nama" in cols and resolved_cabang_nama and self._is_blank_value(row_dict.get("cabang_nama")): update_map["cabang_nama"] = resolved_cabang_nama if "oleh_dtime" in cols and self._is_blank_value(row_dict.get("oleh_dtime")): fallback_dtime = str(parent_ctx.get("dtime") or datetime.now().strftime("%Y-%m-%d %H:%M:%S")) update_map["oleh_dtime"] = fallback_dtime update_map = {k: v for k, v in update_map.items() if k in cols} if not update_map: continue set_clause = ", ".join([f"{k} = ?" for k in update_map.keys()]) params = list(update_map.values()) + [row_id] update_sql = render_sql_template( "UPDATE transaksi_settlement SET {set_clause} WHERE id = ?", set_clause=set_clause, ) cur.execute( update_sql, params, ) if cur.rowcount > 0: updated += 1 if updated > 0: conn.commit() else: conn.rollback() return updated except (sqlite3.Error, TypeError, ValueError, RuntimeError, KeyError) as exc: self.log_warning(f"[SETTLEMENT_BACKFILL_ERROR] rollback backfill settlement rows: {exc}") conn.rollback() raise finally: conn.close() # edited by glg # Sinkronkan status settlement ke tabel copy agar kompatibel modul legacy. def _sync_settlement_id_to_copy(self, cursor, transaksi_id, settlement_id): cols = self._get_table_columns(cursor, "transaksi_copy") if not cols: return if "settlement_id" not in cols: return try: if "id" in cols: cursor.execute( "UPDATE transaksi_copy SET settlement_id = ? WHERE id = ?", (settlement_id, transaksi_id), ) elif "transaksi_id" in cols: cursor.execute( "UPDATE transaksi_copy SET settlement_id = ? WHERE transaksi_id = ?", (settlement_id, transaksi_id), ) except (sqlite3.Error, RuntimeError, TypeError, ValueError) as exc: self.log_warning( f"[SETTLEMENT_SYNC_COPY_ERROR] Gagal sinkron settlement ke transaksi_copy id={transaksi_id}: {exc}" ) def _voucher_subquery(self): return """ SELECT vu.transaksi_id, SUM(COALESCE(vu.nilai_pakai, 0)) AS total_voucher FROM voucher_usage vu GROUP BY vu.transaksi_id """ def get_transaksi_belum_settle(self): conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) self._ensure_return_refund_columns(cursor) self._ensure_voucher_tables(cursor) # PATCH[BackEndAgent|PerHari]: Group transaksi belum settle per hari, kasir, dan tanggal # PATCH[FrontEndAgent|SameDaySettlement]: Gunakan <= agar transaksi hari ini bisa di-settle query = """ WITH unsettled AS ( SELECT t.id, t.dtime, t.oleh_nama, COALESCE(t.transaksi_nilai, 0) AS transaksi_nilai FROM transaksi t WHERE t.settlement_id = 1 AND t.dtime < ? AND IFNULL(t.trash, 0) = 0 ), refund AS ( SELECT u.id AS transaksi_id, SUM(COALESCE(td.produk_ord_hrg, dr.harga, 0) * COALESCE(dr.jumlah, 0)) AS total_refund FROM unsettled u JOIN return_transaksi_penjualan r ON r.transaksi_id = u.id JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id LEFT JOIN transaksi_data td ON td.transaksi_id = r.transaksi_id AND td.produk_id = dr.produk_id WHERE COALESCE(r.refund_method, 'cash') = 'cash' GROUP BY u.id ), voucher AS ( SELECT u.id AS transaksi_id, SUM(COALESCE(vu.nilai_pakai, 0)) AS total_voucher FROM unsettled u JOIN voucher_usage vu ON vu.transaksi_id = u.id GROUP BY u.id ) SELECT DATE(u.dtime) AS tanggal, strftime('%w', u.dtime) AS hari, u.oleh_nama AS kasir, SUM( COALESCE(u.transaksi_nilai, 0) - COALESCE(r.total_refund, 0) - COALESCE(v.total_voucher, 0) ) AS total, COUNT(u.id) AS jumlah_transaksi FROM unsettled u LEFT JOIN refund r ON r.transaksi_id = u.id LEFT JOIN voucher v ON v.transaksi_id = u.id GROUP BY DATE(u.dtime), u.oleh_nama ORDER BY DATE(u.dtime) ASC """ self.log_debug(f'[DEBUG get_transaksi_belum_settle] {query}') cutoff = self._build_today_cutoff(include_today=True) cursor.execute(query, (cutoff,)) rows = cursor.fetchall() conn.close() result = [dict(row) for row in rows] self.log_debug(f'[DEBUG get_transaksi_belum_settle] Found {len(result)} group hari/kasir') return result def get_transaksi_ids_belum_settle(self, tanggal_list=None, kasir_list=None, include_legacy_non_tunai=False): """ PATCH[FrontEndAgent|SettlementIDs]: Ambil semua ID transaksi yang belum di-settle untuk digunakan dalam proses set_settlement() """ conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) # PATCH[FrontEndAgent|SameDaySettlement]: Gunakan <= agar transaksi hari ini bisa di-settle query = """ SELECT id FROM transaksi WHERE settlement_id = 1 AND dtime < ? AND IFNULL(trash, 0) = 0 """ params = [self._build_today_cutoff(include_today=True)] query, params = self._append_day_list_filter( query=query, params=params, column_sql="dtime", day_values=tanggal_list, ) if kasir_list: placeholders = ",".join(["?"] * len(kasir_list)) query += f" AND oleh_nama IN ({placeholders})" params.extend(kasir_list) query += " ORDER BY dtime ASC" cursor.execute(query, params) rows = cursor.fetchall() result = [row["id"] for row in rows] if include_legacy_non_tunai: legacy_query = """ SELECT id FROM transaksi WHERE settlement_id IN (2, 3) AND dtime < ? AND COALESCE(bank_nama, '') <> '' AND bank_nama <> 'Tunai' AND IFNULL(trash, 0) = 0 """ legacy_params = [self._build_today_cutoff(include_today=True)] legacy_query, legacy_params = self._append_day_list_filter( query=legacy_query, params=legacy_params, column_sql="dtime", day_values=tanggal_list, ) if kasir_list: placeholders = ",".join(["?"] * len(kasir_list)) legacy_query += f" AND oleh_nama IN ({placeholders})" legacy_params.extend(kasir_list) legacy_query += " ORDER BY dtime ASC" cursor.execute(legacy_query, legacy_params) legacy_rows = cursor.fetchall() legacy_ids = [row["id"] for row in legacy_rows] result = list(dict.fromkeys(result + legacy_ids)) conn.close() self.log_debug(f'[DEBUG get_transaksi_ids_belum_settle] Found {len(result)} transaction IDs') return result def get_history_settlement_per_hari(self): conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() # PATCH: History settlement per hari, tampilkan admin verifikasi query = """ SELECT tanggal, kasir_nama, total_harus, total_disetor, selisih, status, admin_verifikasi FROM settlement_history ORDER BY tanggal DESC LIMIT 30 """ cursor.execute(query) rows = cursor.fetchall() conn.close() return [dict(row) for row in rows] def get_detail_transaksi(self, transaksi_id): conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() query = """ SELECT produk_nama AS nama, produk_ord_jml as jumlah, produk_ord_hrg AS total FROM transaksi_data WHERE transaksi_id = ? AND IFNULL(trash, 0) = 0 """ cursor.execute(query, (transaksi_id,)) rows = cursor.fetchall() conn.close() return [dict(row) for row in rows] def get_transaksi_by_ids(self, transaksi_ids): # edited by glg normalized_ids = self._normalize_transaksi_ids(transaksi_ids) if not normalized_ids: return [] conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) self._ensure_return_refund_columns(cursor) self._ensure_voucher_tables(cursor) conn.commit() refund_subquery = """ SELECT r.transaksi_id, SUM(COALESCE(td.produk_ord_hrg, dr.harga, 0) * COALESCE(dr.jumlah, 0)) AS total_refund FROM return_transaksi_penjualan r JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id LEFT JOIN transaksi_data td ON td.transaksi_id = r.transaksi_id AND td.produk_id = dr.produk_id WHERE COALESCE(r.refund_method, 'cash') = 'cash' GROUP BY r.transaksi_id """ query_prefix = render_sql_template( """ SELECT t.id, t.nomer, t.dtime, t.oleh_nama, t.transaksi_nilai, t.bank_nama, MAX( 0, COALESCE(t.transaksi_nilai, t.transaksi_bulat, t.transaksi_dibayar, 0) - COALESCE(r.total_refund, 0) - COALESCE(v.total_voucher, 0) ) AS total, CASE WHEN COALESCE(t.bank_nama, '') = '' OR t.bank_nama = 'Tunai' THEN 'Tunai' ELSE t.bank_nama END AS metode FROM transaksi t LEFT JOIN ({refund_subquery}) r ON r.transaksi_id = t.id LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id WHERE """, refund_subquery=refund_subquery, voucher_subquery=self._voucher_subquery(), ) query, query_params = build_sql_with_identifier_in_clause( query_prefix, "t.id", normalized_ids, cast_int=True, positive_only=True, unique=True, sql_suffix="AND IFNULL(t.trash, 0) = 0 ORDER BY datetime(t.dtime) ASC", ) cursor.execute(query, query_params) rows = cursor.fetchall() conn.close() return [dict(row) for row in rows] # edited by glg def _normalize_transaksi_ids(self, transaksi_ids): out = [] seen = set() for item in transaksi_ids or []: try: parsed = int(item) except (TypeError, ValueError): continue if parsed <= 0: continue if parsed in seen: continue seen.add(parsed) out.append(parsed) return out # edited by glg def _extract_settled_ids_and_map_rows_from_history_rows(self, history_rows): settled_ids = set() map_rows = [] for row in history_rows or []: raw_blob = None history_id = 0 try: if isinstance(row, dict): history_id = self._as_positive_int(row.get("id"), 0) raw_blob = row.get("data_transaksi_id") else: history_id = self._as_positive_int(row["id"], 0) raw_blob = row["data_transaksi_id"] except (TypeError, ValueError, KeyError, IndexError): raw_blob = None for transaksi_id in self._extract_transaksi_ids_blob(raw_blob): trx_id = int(transaksi_id) settled_ids.add(trx_id) if history_id > 0: map_rows.append((history_id, trx_id)) return settled_ids, map_rows # edited by glg # Backfill map settlement secara bertahap untuk pending ID agar tidak bergantung LIMIT statis. def _backfill_settlement_map_for_pending_ids(self, cursor, pending_ids, chunk_size=1000, max_scan_rows=None): unresolved = {int(v) for v in (pending_ids or []) if self._as_positive_int(v, 0) > 0} if not unresolved: return set(), [] scan_offset = 0 scanned_rows = 0 resolved = set() map_rows = [] while unresolved: if max_scan_rows is not None and scanned_rows >= int(max_scan_rows): break cursor.execute( """ SELECT id, data_transaksi_id FROM settlement_history WHERE COALESCE(data_transaksi_id, '') <> '' ORDER BY id DESC LIMIT ? OFFSET ? """, (int(chunk_size), int(scan_offset)), ) rows = cursor.fetchall() or [] if not rows: break scanned_now = len(rows) scanned_rows += scanned_now scan_offset += scanned_now settled_ids, extracted_map_rows = self._extract_settled_ids_and_map_rows_from_history_rows(rows) if settled_ids: intersects = unresolved.intersection(settled_ids) if intersects: resolved.update(intersects) unresolved -= intersects if extracted_map_rows: map_rows.extend(extracted_map_rows) return resolved, map_rows # edited by glg def get_settlement_lock_map(self, transaksi_ids): return self.settlement_lock_map_service.get_settlement_lock_map(transaksi_ids) # edited by glg def is_transaksi_locked_for_mutation(self, transaksi_id): return self.settlement_lock_map_service.is_transaksi_locked_for_mutation(transaksi_id) # edited by glg def hitung_total_per_metode_by_ids(self, transaksi_ids): normalized_ids = self._normalize_transaksi_ids(transaksi_ids) if not normalized_ids: return {"Tunai": 0.0, "Non Tunai": 0.0} conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() try: self._ensure_pembayaran_non_tunai_column(cursor) self._ensure_return_refund_columns(cursor) self._ensure_voucher_tables(cursor) conn.commit() refund_subquery = """ SELECT r.transaksi_id, SUM(COALESCE(td.produk_ord_hrg, dr.harga, 0) * COALESCE(dr.jumlah, 0)) AS total_refund FROM return_transaksi_penjualan r JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id LEFT JOIN transaksi_data td ON td.transaksi_id = r.transaksi_id AND td.produk_id = dr.produk_id WHERE COALESCE(r.refund_method, 'cash') = 'cash' GROUP BY r.transaksi_id """ total_harus_prefix = render_sql_template( """ SELECT COALESCE( SUM( MAX( 0, COALESCE(t.transaksi_nilai, 0) - COALESCE(r.total_refund, 0) - COALESCE(v.total_voucher, 0) ) ), 0 ) AS total_harus FROM transaksi t LEFT JOIN ({refund_subquery}) r ON r.transaksi_id = t.id LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id WHERE """, refund_subquery=refund_subquery, voucher_subquery=self._voucher_subquery(), ) total_harus_query, total_harus_params = build_sql_with_identifier_in_clause( total_harus_prefix, "t.id", normalized_ids, cast_int=True, positive_only=True, unique=True, sql_suffix="AND IFNULL(t.trash, 0) = 0", ) cursor.execute(total_harus_query, total_harus_params) row_total = cursor.fetchone() total_harus = float(row_total["total_harus"] if row_total else 0) total_non_tunai_prefix = render_sql_template( """ SELECT COALESCE( SUM( CASE WHEN COALESCE(t.bank_nama, '') = '' THEN 0 WHEN t.bank_nama = 'Tunai' THEN 0 ELSE MAX( 0, COALESCE(t.pembayaran_non_tunai, t.transaksi_dibayar, t.transaksi_nilai, 0) - COALESCE(v.total_voucher, 0) ) END ), 0 ) AS total_non_tunai FROM transaksi t LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id WHERE """, voucher_subquery=self._voucher_subquery(), ) total_non_tunai_query, total_non_tunai_params = build_sql_with_identifier_in_clause( total_non_tunai_prefix, "t.id", normalized_ids, cast_int=True, positive_only=True, unique=True, sql_suffix="AND IFNULL(t.trash, 0) = 0", ) cursor.execute(total_non_tunai_query, total_non_tunai_params) row_non_tunai = cursor.fetchone() total_non_tunai = float(row_non_tunai["total_non_tunai"] if row_non_tunai else 0) total_tunai = max(0.0, total_harus - total_non_tunai) return { "Tunai": total_tunai, "Non Tunai": max(0.0, total_non_tunai), } finally: conn.close() def get_list_admin(self): conn = self.connect() cursor = conn.cursor() query = """ SELECT nama_login FROM per_employee WHERE COALESCE(oto_settlement, 0) = 1 """ cursor.execute(query) results = cursor.fetchall() conn.close() return [row[0] for row in results] def get_admin_settlement_status(self, nama_login): conn = self.connect() cursor = conn.cursor() query = """ SELECT id, nama_login, COALESCE(oto_settlement, 0) as oto_settlement FROM per_employee WHERE nama_login = ? """ cursor.execute(query, (nama_login,)) row = cursor.fetchone() conn.close() if not row: return None return {"id": row[0], "nama_login": row[1], "oto_settlement": row[2]} def verifikasi_admin(self, nama_admin, password_input): conn = self.connect() cursor = conn.cursor() query = """ SELECT password FROM per_employee WHERE nama_login = ? AND COALESCE(oto_settlement, 0) = 1 """ cursor.execute(query, (nama_admin,)) result = cursor.fetchone() conn.close() if result: password_db = result[0] try: auth_service_cls = self._get_auth_service_cls() ok, new_hash = auth_service_cls.verify_and_upgrade(password_input, password_db) except RuntimeError as e: self.log_error(f"{e}") return False if ok and new_hash: try: conn2 = self.connect() cur2 = conn2.cursor() cur2.execute( "UPDATE per_employee SET password = ? WHERE nama_login = ?", (new_hash, nama_admin) ) conn2.commit() finally: try: cur2.close() conn2.close() except (RuntimeError, TypeError, AttributeError): pass return ok return False def cek_transaksi_settlement(self, include_today=False): conn = None cursor = None try: conn = self.connect() self.log_debug(f"DB Path: {self.db_path}") conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) query = """ SELECT id, dtime as tanggal, customers_nama AS customer, transaksi_nilai AS total, oleh_nama AS kasir FROM transaksi WHERE settlement_id = 1 AND dtime < ? AND IFNULL(trash, 0) = 0 ORDER BY dtime ASC """ self.log_debug(f'[DEBUG Settlement Query] {query}') cutoff = self._build_today_cutoff(include_today=bool(include_today)) cursor.execute(query, (cutoff,)) hasil = cursor.fetchall() # Konversi hasil ke list of dict agar bisa diolah result = [dict(row) for row in hasil] self.log_debug(f"[DEBUG Settlement] Found {len(result)} unsettled transactions from previous days") return result except sqlite3.Error as db_err: self.log_error(f"Kesalahan koneksi atau query SQLite: {db_err}") return [] # Kembalikan list kosong agar tidak error di len(data) finally: if cursor is not None: cursor.close() if conn is not None: conn.close() def has_unsettled_before_today(self): conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) query = """ SELECT id FROM transaksi WHERE settlement_id = 1 AND dtime < ? AND IFNULL(trash, 0) = 0 LIMIT 1 """ cursor.execute(query, (self._build_today_cutoff(include_today=False),)) row = cursor.fetchone() conn.close() return row is not None def get_rekap_settlement(self): """ Menghitung rekap transaksi tunai & non-tunai (per EDC). """ conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() query = """ SELECT settlement_id ,bank_nama AS jenis_edc,bank_from AS jenis_bank_cust,bank_rekening_nama AS rekening_cust, COUNT(id) AS jumlah_Nota, SUM(transaksi_nilai) AS total FROM transaksi WHERE IFNULL(trash, 0) = 0 GROUP BY settlement_id, bank_nama,bank_from, bank_rekening_nama """ cursor.execute(query) rows = cursor.fetchall() conn.close() hasil = {"tunai": 0, "edc": {}} for row in rows: metode = row["settlement_id"] total = row["total"] or 0 # settlement id = 1 artinya tunai # settlement id = 2 artinya kredit # settlement id = 3 artinya debit if metode == 0: #sudah di settle , biarkan saja self.log_info('SUDAH DI SETTLE') elif metode == 1 : hasil["tunai"] += total else: # anggap metode lain adalah EDC edc_name = row["jenis_edc"] or "UNKNOWN" hasil["edc"][edc_name] = hasil["edc"].get(edc_name, 0) + total return hasil def get_ringkasan_non_tunai(self): conn = self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() self._ensure_performance_indexes(cursor) self._ensure_pembayaran_non_tunai_column(cursor) conn.commit() query = """ SELECT CASE WHEN bank_nama LIKE 'Multi:%' THEN 'Multi' ELSE bank_nama END AS metode, COUNT(id) AS jumlah_transaksi, SUM(COALESCE(pembayaran_non_tunai, transaksi_dibayar, transaksi_nilai)) AS total_bayar FROM transaksi WHERE settlement_id = 1 AND dtime < ? AND IFNULL(trash, 0) = 0 AND COALESCE(bank_nama, '') <> '' AND bank_nama <> 'Tunai' GROUP BY metode ORDER BY metode """ cursor.execute(query, (self._build_today_cutoff(include_today=True),)) rows = cursor.fetchall() conn.close() non_tunai = [] multi = None for row in rows: item = dict(row) if item.get("metode") == "Multi": multi = item else: non_tunai.append(item) return {"non_tunai": non_tunai, "multi": multi} # edited by glg @staticmethod def _normalize_trace_id(trace_id=""): text = str(trace_id or "").strip() return text or "-" # edited by glg @staticmethod def _reason_from_error_code(error_code, fallback="settlement_error"): code = str(error_code or "").strip().upper() mapping = { "SETTLEMENT_EMPTY_TRANSAKSI_IDS": "empty_transaksi_ids", "SETTLEMENT_DB_ERROR": "db_error", "SETTLEMENT_DATA_ERROR": "invalid_settlement_data", "SETTLEMENT_UNEXPECTED_ERROR": "runtime_error", "SETTLEMENT_INSERT_DB_ERROR": "db_error", "SETTLEMENT_INSERT_DATA_ERROR": "invalid_settlement_data", "SETTLEMENT_INSERT_UNEXPECTED_ERROR": "runtime_error", "SETTLEMENT_COUNTER_CONFLICT": "counter_conflict", "SETTLEMENT_COUNTER_EMPTY": "counter_empty", "SETTLEMENT_STATUS_UPDATE_FAILED": "status_update_failed", "SETTLEMENT_ATOMIC_DB_ERROR": "db_error", "SETTLEMENT_ATOMIC_DATA_ERROR": "invalid_settlement_data", "SETTLEMENT_ATOMIC_UNEXPECTED_ERROR": "runtime_error", "SETTLEMENT_ATOMIC_PROCESS_ERROR": "settlement_process_error", } return mapping.get(code, str(fallback or "settlement_error")) # edited by glg @staticmethod def _rollback_if_needed(conn, auto_commit): if not bool(auto_commit): return conn.rollback() def set_settlement( self, transaksi_ids, admin, kasir, total_disetor, conn=None, auto_commit=True, trace_id="", ): import json own_conn = conn is None conn = conn or self.connect() conn.row_factory = sqlite3.Row cursor = conn.cursor() trace_tag = self._normalize_trace_id(trace_id) try: self._ensure_pembayaran_non_tunai_column(cursor) self._ensure_settlement_history_columns(cursor) self._ensure_settlement_history_map_table(cursor) self._ensure_return_refund_columns(cursor) self._ensure_voucher_tables(cursor) normalized_ids = self._normalize_transaksi_ids(transaksi_ids) if not normalized_ids: raise SettlementProcessError( "SETTLEMENT_EMPTY_TRANSAKSI_IDS", "Tidak ada transaksi_id untuk proses settlement.", ) totals = self.settlement_finance_calculation_service.calculate_totals( cursor, normalized_ids=normalized_ids, voucher_subquery=self._voucher_subquery(), ) total_harus = float(totals.get("total_harus") or 0.0) total_refund_cash = float(totals.get("total_refund_cash") or 0.0) total_non_tunai = float(totals.get("total_non_tunai") or 0.0) refund_subquery = str( totals.get("refund_subquery") or self.settlement_finance_calculation_service.build_refund_subquery() ) # edited by glg # Observability settlement: audit komposisi transaksi berdasarkan mode PPN. ppn_mode_breakdown = self._get_ppn_mode_breakdown( cursor=cursor, transaksi_ids=normalized_ids, refund_subquery=refund_subquery, ) if ppn_mode_breakdown: parts = [] for item in ppn_mode_breakdown: row = item if isinstance(item, dict) else {} mode = str(row.get("ppn_mode") or "exclude").strip().lower() if mode not in {"include", "exclude"}: mode = "exclude" jumlah = self._as_positive_int(row.get("jumlah_transaksi"), 0) try: total_mode = float(row.get("total_nilai") or 0) except (TypeError, ValueError): total_mode = 0.0 parts.append(f"{mode}:{jumlah} trx/Rp {total_mode:,.0f}") if parts: self.log_info(f"[Settlement] Breakdown mode pajak -> {' | '.join(parts)}") data_transaksi_json = json.dumps(normalized_ids) # simpan transaksi settlement (update transaksi) query_nomer_batch, query_nomer_params = build_sql_with_identifier_in_clause( "SELECT id, nomer FROM transaksi WHERE", "id", normalized_ids, cast_int=True, positive_only=True, unique=True, ) cursor.execute(query_nomer_batch, query_nomer_params) rows = cursor.fetchall() for row in rows: transaksi_id = row['id'] cursor.execute("UPDATE transaksi SET settlement_id = 0 WHERE id = ?", (transaksi_id,)) self._sync_settlement_id_to_copy(cursor, transaksi_id, 0) # edited by glg # Selisih kas harus berbasis kewajiban tunai kasir, bukan total gabungan metode. total_harus_tunai = max(0.0, float(total_harus) - float(total_non_tunai)) selisih = float(total_disetor) - float(total_harus_tunai) if selisih == 0: status = "Sesuai" elif selisih > 0: status = f"Lebih setor {selisih:,.0f}" else: status = f"Kurang setor {abs(selisih):,.0f}" tanggal_settlement = datetime.now().strftime("%Y-%m-%d %H:%M:%S") cursor.execute(""" INSERT INTO settlement_history (tanggal, admin, kasir, total_harus, total_disetor, total_non_tunai, total_refund_cash, selisih, status, data_transaksi_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( tanggal_settlement, admin, kasir, total_harus, total_disetor, total_non_tunai, total_refund_cash, selisih, status, data_transaksi_json )) self._insert_settlement_history_map_rows(cursor, cursor.lastrowid, normalized_ids) self.log_info( f"[Settlement][{trace_tag}] Insert history tanggal={tanggal_settlement} " f"admin={admin} total_trx={len(normalized_ids)}" ) if bool(auto_commit): conn.commit() self.log_info( f"[Settlement][{trace_tag}] set_settlement sukses total_harus={total_harus:,.0f} " f"total_non_tunai={total_non_tunai:,.0f} total_disetor={float(total_disetor or 0):,.0f}" ) return True except SettlementProcessError: self._rollback_if_needed(conn, auto_commit) raise except sqlite3.Error as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_DB_ERROR", "Gagal memproses settlement transaksi.", cause=exc, ) from exc except (TypeError, ValueError, KeyError) as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_DATA_ERROR", "Data settlement transaksi tidak valid.", cause=exc, ) from exc except (AttributeError, RuntimeError) as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_UNEXPECTED_ERROR", "Terjadi kesalahan saat memproses settlement transaksi.", cause=exc, ) from exc finally: if own_conn: conn.close() def get_last_settlements(self, limit=7): return self.settlement_history_service.get_last_settlements(limit=limit) def get_history_settlement_by_range(self, start_date, end_date, limit=None): return self.settlement_history_service.get_history_settlement_by_range( start_date=start_date, end_date=end_date, limit=limit, ) # edited by glg @staticmethod def _parse_settlement_counter_number(counter_text, today_str): text = str(counter_text or "").strip() prefix = f"ST-{today_str}-" if not text.startswith(prefix): return 0 suffix = str(text[len(prefix):] or "").strip() if not suffix.isdigit(): return 0 return int(suffix) # edited by glg def _read_next_settlement_counter_number(self, conn, today_str): cur = conn.cursor() cur.execute( """ SELECT counter FROM transaksi_settlement WHERE counter LIKE ? ORDER BY id DESC LIMIT 120 """, (f"ST-{today_str}-%",), ) rows = cur.fetchall() or [] max_no = 0 for row in rows: raw_counter = row[0] if row else "" parsed = self._parse_settlement_counter_number(raw_counter, today_str) if parsed > max_no: max_no = parsed return int(max_no) + 1 # edited by glg def _build_settlement_counter_candidate(self, conn, base_number=None, retry_offset=0): today_str = date.today().strftime("%Y%m%d") base_no = self._as_positive_int(base_number, 0) if base_no <= 0: base_no = self._read_next_settlement_counter_number(conn, today_str) next_no = int(base_no) + max(0, int(retry_offset or 0)) return f"ST-{today_str}-{int(next_no):03d}", int(base_no) # edited by glg @staticmethod def _is_settlement_counter_conflict(exc): message = str(exc or "").lower() return "transaksi_settlement.counter" in message or "idx_transaksi_settlement_counter_unique" in message def generate_settlement_number(self, conn): """Buat nomor settlement otomatis: ST-YYYYMMDD-XXX.""" counter, _ = self._build_settlement_counter_candidate(conn) return counter # edited by glg @staticmethod def _collect_settlement_filter_lists(transaksi_list): tanggal_list = [] kasir_list = [] for item in transaksi_list or []: if not isinstance(item, dict): continue raw_tanggal = str(item.get("tanggal") or "").strip() raw_kasir = str(item.get("kasir") or "").strip() if raw_tanggal and raw_tanggal not in tanggal_list: tanggal_list.append(raw_tanggal) if raw_kasir and raw_kasir not in kasir_list: kasir_list.append(raw_kasir) return tanggal_list, kasir_list # edited by glg def _resolve_settlement_transaksi_ids(self, *, transaksi_ids_override, tanggal_list, kasir_list): if transaksi_ids_override is not None: transaksi_ids = self._normalize_transaksi_ids(transaksi_ids_override) else: transaksi_ids = self.get_transaksi_ids_belum_settle( tanggal_list=tanggal_list or None, kasir_list=kasir_list or None, include_legacy_non_tunai=True, ) if not transaksi_ids: raise SettlementProcessError( "SETTLEMENT_EMPTY_TRANSAKSI_IDS", "Tidak ada transaksi_id untuk disimpan ke settlement.", ) return transaksi_ids # edited by glg def _resolve_settlement_header_context( self, cursor, *, transaksi_ids, kasir_id, kasir_nama, cabang_id, cabang_nama, approval_id, approval_nama, approval_counter, ): header_query, header_params = build_sql_with_identifier_in_clause( "SELECT id, dtime, oleh_id, oleh_nama, cabang_id, cabang_nama FROM transaksi WHERE", "id", transaksi_ids, cast_int=True, positive_only=True, unique=True, sql_suffix="ORDER BY datetime(dtime) ASC LIMIT 1", ) cursor.execute(header_query, header_params) row = cursor.fetchone() trx_ctx = dict(row) if row else {} resolved_oleh_id = self._as_positive_int(kasir_id, self._as_positive_int(trx_ctx.get("oleh_id"), 0)) resolved_oleh_nama = str(kasir_nama or trx_ctx.get("oleh_nama") or "-").strip() or "-" resolved_cabang_id = self._as_positive_int(cabang_id, self._as_positive_int(trx_ctx.get("cabang_id"), 0)) resolved_cabang_nama = str(cabang_nama or trx_ctx.get("cabang_nama") or "").strip() if resolved_cabang_nama.lower() in {"-", "none", "null"}: resolved_cabang_nama = "" if not resolved_cabang_nama and resolved_cabang_id > 0: resolved_cabang_nama = self._lookup_cabang_nama(cursor, resolved_cabang_id) return { "oleh_id": resolved_oleh_id, "oleh_nama": resolved_oleh_nama, "cabang_id": resolved_cabang_id, "cabang_nama": resolved_cabang_nama, "approval_id": self._as_positive_int(approval_id, 0), "approval_nama": str(approval_nama or "").strip(), "approval_counter": str(approval_counter or "0").strip() or "0", } # edited by glg def _insert_settlement_header_with_retry( self, *, cursor, conn, data_transaksi_id, header_ctx, max_counter_retry=8, ): base_counter_number = None last_conflict_exc = None for retry_idx in range(max(1, int(max_counter_retry or 1))): counter, base_counter_number = self._build_settlement_counter_candidate( conn, base_number=base_counter_number, retry_offset=retry_idx, ) insert_payload = self._build_transaksi_settlement_insert_payload( cursor, counter=counter, oleh_id=header_ctx.get("oleh_id"), oleh_dtime=datetime.now().strftime("%Y-%m-%d %H:%M:%S"), data_transaksi_id=data_transaksi_id, cabang_id=header_ctx.get("cabang_id"), oleh_nama=header_ctx.get("oleh_nama"), cabang_nama=header_ctx.get("cabang_nama"), approval_id=header_ctx.get("approval_id"), approval_nama=header_ctx.get("approval_nama"), approval_counter=header_ctx.get("approval_counter"), ) if not insert_payload: raise SettlementProcessError( "SETTLEMENT_SCHEMA_ERROR", "Schema transaksi_settlement tidak valid untuk penyimpanan settlement.", ) insert_sql = build_insert_sql("transaksi_settlement", list(insert_payload.keys())) try: cursor.execute( insert_sql, list(insert_payload.values()), ) return counter except sqlite3.IntegrityError as exc: if not self._is_settlement_counter_conflict(exc): raise last_conflict_exc = exc self.log_warning( f"[SETTLEMENT_COUNTER_CONFLICT] counter={counter} retry={retry_idx + 1}/{int(max_counter_retry)}" ) raise SettlementProcessError( "SETTLEMENT_COUNTER_CONFLICT", "Gagal menghasilkan nomor settlement unik.", cause=last_conflict_exc, ) def simpan_settlement( self, kasir_id, kasir_nama, cabang_id, cabang_nama, transaksi_list, approval_id=0, approval_nama="", approval_counter="0", transaksi_ids_override=None, conn=None, auto_commit=True, trace_id="", ): """ Simpan hasil settlement ke tabel transaksi_settlement. transaksi_list = list of dict aggregate data (tanpa ID individual) PATCH[FrontEndAgent|SettlementIDs]: Ambil ID transaksi dari query terpisah """ import json from datetime import datetime own_conn = conn is None conn = conn or self.connect() conn.row_factory = sqlite3.Row trace_tag = self._normalize_trace_id(trace_id) try: cur = conn.cursor() self._ensure_performance_indexes(cur) tanggal_list, kasir_list = self._collect_settlement_filter_lists(transaksi_list) transaksi_ids = self._resolve_settlement_transaksi_ids( transaksi_ids_override=transaksi_ids_override, tanggal_list=tanggal_list, kasir_list=kasir_list, ) data_transaksi_id = json.dumps(transaksi_ids) header_ctx = self._resolve_settlement_header_context( cur, transaksi_ids=transaksi_ids, kasir_id=kasir_id, kasir_nama=kasir_nama, cabang_id=cabang_id, cabang_nama=cabang_nama, approval_id=approval_id, approval_nama=approval_nama, approval_counter=approval_counter, ) counter = self._insert_settlement_header_with_retry( cursor=cur, conn=conn, data_transaksi_id=data_transaksi_id, header_ctx=header_ctx, max_counter_retry=8, ) if bool(auto_commit): conn.commit() self.log_info( f"[Settlement][{trace_tag}] Header settlement tersimpan counter={counter} total_trx={len(transaksi_ids)}" ) return counter except SettlementProcessError: self._rollback_if_needed(conn, auto_commit) raise except sqlite3.Error as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_INSERT_DB_ERROR", "Gagal menyimpan header settlement.", cause=exc, ) from exc except (TypeError, ValueError, KeyError) as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_INSERT_DATA_ERROR", "Data settlement tidak valid saat simpan header.", cause=exc, ) from exc except (AttributeError, RuntimeError) as exc: self._rollback_if_needed(conn, auto_commit) raise SettlementProcessError( "SETTLEMENT_INSERT_UNEXPECTED_ERROR", "Terjadi kesalahan saat menyimpan settlement.", cause=exc, ) from exc finally: if own_conn: conn.close() # edited by glg def execute_settlement_atomic( self, *, kasir_id, kasir_nama, cabang_id, cabang_nama, transaksi_list, approval_id=0, approval_nama="", approval_counter="0", transaksi_ids_override=None, admin="", total_disetor=0, trace_id="", ): normalized_ids = self._normalize_transaksi_ids(transaksi_ids_override) trace_tag = self._normalize_trace_id(trace_id) if not normalized_ids: return { "ok": False, "message": "Tidak ada ID transaksi untuk di-settle.", "error_code": "SETTLEMENT_EMPTY_TRANSAKSI_IDS", "reason": "empty_transaksi_ids", "trace_id": trace_tag, "counter": None, "transaksi_ids": [], } conn = self.connect() conn.row_factory = sqlite3.Row try: counter = self.simpan_settlement( kasir_id=kasir_id, kasir_nama=kasir_nama, cabang_id=cabang_id, cabang_nama=cabang_nama, transaksi_list=transaksi_list, approval_id=approval_id, approval_nama=approval_nama, approval_counter=approval_counter, transaksi_ids_override=normalized_ids, conn=conn, auto_commit=False, trace_id=trace_tag, ) if not counter: raise SettlementProcessError( "SETTLEMENT_COUNTER_EMPTY", "Gagal menyimpan settlement.", ) settled_ok = self.set_settlement( normalized_ids, admin, kasir_nama, total_disetor, conn=conn, auto_commit=False, trace_id=trace_tag, ) if settled_ok is False: raise SettlementProcessError( "SETTLEMENT_STATUS_UPDATE_FAILED", "Gagal memperbarui status settlement transaksi.", ) conn.commit() self.log_info( f"[Settlement][{trace_tag}] execute_settlement_atomic sukses counter={counter} total_trx={len(normalized_ids)}" ) return { "ok": True, "message": "", "counter": counter, "transaksi_ids": normalized_ids, "trace_id": trace_tag, } except SettlementProcessError as exc: self.log_error(f"[Settlement][{trace_tag}] Gagal execute_settlement_atomic: {exc}") try: conn.rollback() except sqlite3.Error: pass return { "ok": False, "message": str(exc), "error_code": str(exc.code), "reason": self._reason_from_error_code(str(exc.code), fallback="settlement_process_error"), "trace_id": trace_tag, "counter": None, "transaksi_ids": normalized_ids, } except sqlite3.Error as exc: self.log_error(f"[Settlement][{trace_tag}] Gagal execute_settlement_atomic: {exc}") try: conn.rollback() except sqlite3.Error: pass return { "ok": False, "message": str( SettlementProcessError( "SETTLEMENT_ATOMIC_DB_ERROR", "Terjadi kesalahan database saat menyimpan settlement.", cause=exc, ) ), "error_code": "SETTLEMENT_ATOMIC_DB_ERROR", "reason": "db_error", "trace_id": trace_tag, "counter": None, "transaksi_ids": normalized_ids, } except (TypeError, ValueError, KeyError) as exc: self.log_error(f"[Settlement][{trace_tag}] Gagal execute_settlement_atomic: {exc}") try: conn.rollback() except sqlite3.Error: pass return { "ok": False, "message": str( SettlementProcessError( "SETTLEMENT_ATOMIC_DATA_ERROR", "Data settlement tidak valid.", cause=exc, ) ), "error_code": "SETTLEMENT_ATOMIC_DATA_ERROR", "reason": "invalid_settlement_data", "trace_id": trace_tag, "counter": None, "transaksi_ids": normalized_ids, } except (AttributeError, RuntimeError) as exc: self.log_error(f"[Settlement][{trace_tag}] Gagal execute_settlement_atomic: {exc}") try: conn.rollback() except sqlite3.Error: pass return { "ok": False, "message": str( SettlementProcessError( "SETTLEMENT_ATOMIC_UNEXPECTED_ERROR", "Terjadi kesalahan runtime saat menyimpan settlement.", cause=exc, ) ), "error_code": "SETTLEMENT_ATOMIC_UNEXPECTED_ERROR", "reason": "runtime_error", "trace_id": trace_tag, "counter": None, "transaksi_ids": normalized_ids, } finally: conn.close() def hitung_total_per_metode(self): """Ambil total penjualan per metode hari ini""" conn = self.connect() cur = conn.cursor() self._ensure_performance_indexes(cur) day_range = self._build_day_range(date.today().isoformat()) cur.execute(""" SELECT settlement_id, SUM(transaksi_nilai) FROM transaksi WHERE dtime >= ? AND dtime < ? AND IFNULL(trash, 0) = 0 GROUP BY settlement_id """, day_range if day_range else ("1970-01-01 00:00:00", "1970-01-02 00:00:00")) result = cur.fetchall() conn.close() return {r[0]: r[1] for r in result} def sudah_disettle_hari_ini(self, kasir_id): conn = self.connect() cur = conn.cursor() self._ensure_performance_indexes(cur) day_range = self._build_day_range(date.today().isoformat()) cur.execute(""" SELECT COUNT(*) FROM transaksi_settlement WHERE oleh_id = ? AND oleh_dtime >= ? AND oleh_dtime < ? """, (kasir_id, day_range[0], day_range[1]) if day_range else (kasir_id, "1970-01-01 00:00:00", "1970-01-02 00:00:00")) count = cur.fetchone()[0] conn.close() return count > 0