mirror of
https://github.com/wavelog/wavelog
synced 2026-08-13 18:41:16 -04:00
876 lines
33 KiB
PHP
876 lines
33 KiB
PHP
<?php
|
|
defined('BASEPATH') or exit('No direct script access allowed');
|
|
|
|
use Wavelog\Label\tFPDF;
|
|
|
|
class Qslpostcard_model extends CI_Model {
|
|
|
|
const MAX_BG_IMAGE_BYTES = 5 * 1024 * 1024; // 5 MB
|
|
|
|
function __construct() {
|
|
$this->load->driver('cache', [
|
|
'adapter' => $this->config->item('cache_adapter') ?? 'file',
|
|
'backup' => $this->config->item('cache_backup') ?? 'file',
|
|
'key_prefix' => $this->config->item('cache_key_prefix') ?? ''
|
|
]);
|
|
}
|
|
|
|
public function list_templates() {
|
|
$sql = "SELECT id, name FROM qsl_postcard_templates WHERE user_id = ? ORDER BY updated_at DESC";
|
|
return $this->db->query($sql, [$this->session->userdata('user_id')])->result_array();
|
|
}
|
|
|
|
public function get_template($id) {
|
|
$sql = "SELECT * FROM qsl_postcard_templates WHERE id = ? AND user_id = ?";
|
|
return $this->db->query($sql, [$id, $this->session->userdata('user_id')])->row_array();
|
|
}
|
|
|
|
public function save_template($id, $name, $layout_json, $preview_image = null) {
|
|
$row = [
|
|
'name' => $name,
|
|
'layout_json' => $layout_json,
|
|
'user_id' => $this->session->userdata('user_id'),
|
|
];
|
|
|
|
if ($preview_image !== null) {
|
|
$row['preview_image'] = $preview_image;
|
|
}
|
|
|
|
if ($id > 0) {
|
|
$uid = $this->session->userdata('user_id');
|
|
// Don't report success for a template that doesn't belong to the user.
|
|
// Check ownership explicitly (a no-op update reports 0 affected rows, so
|
|
// affected_rows() can't distinguish "not owned" from "unchanged").
|
|
$owns = $this->db->query(
|
|
"SELECT 1 FROM qsl_postcard_templates WHERE id = ? AND user_id = ?",
|
|
[$id, $uid]
|
|
)->row_array();
|
|
if (!$owns) {
|
|
return false;
|
|
}
|
|
$this->db->where('id', $id)->where('user_id', $uid)->update('qsl_postcard_templates', $row);
|
|
return $id;
|
|
} else {
|
|
$row['orientation'] = 'landscape';
|
|
$row['width_in'] = 6.00;
|
|
$row['height_in'] = 4.00;
|
|
$this->db->insert('qsl_postcard_templates', $row);
|
|
return (int)$this->db->insert_id();
|
|
}
|
|
}
|
|
|
|
// Duplicate a saved template (layout + its own copy of the background image)
|
|
// into a new row owned by the same user, appending " (copy)" to the name. The
|
|
// image file is duplicated on disk (see duplicate_preview_image), never shared,
|
|
// so deleting either template removes only its own file.
|
|
public function copy_template($id) {
|
|
$uid = $this->session->userdata('user_id');
|
|
|
|
$src = $this->db->query(
|
|
"SELECT * FROM qsl_postcard_templates WHERE id = ? AND user_id = ?",
|
|
[$id, $uid]
|
|
)->row_array();
|
|
|
|
if (!$src) {
|
|
return false;
|
|
}
|
|
|
|
$copyName = mb_substr(rtrim((string)$src['name']) . ' (' . __('Copy') . ')', 0, 100);
|
|
|
|
// Give the copy its own image file — never the source's path — so the two
|
|
// templates stay fully independent.
|
|
$preview_image = $this->duplicate_preview_image($src['preview_image'] ?? null);
|
|
|
|
$row = [
|
|
'name' => $copyName,
|
|
'layout_json' => $src['layout_json'],
|
|
'preview_image' => $preview_image,
|
|
'orientation' => $src['orientation'] ?? 'landscape',
|
|
'width_in' => $src['width_in'] ?? 6.00,
|
|
'height_in' => $src['height_in'] ?? 4.00,
|
|
'user_id' => $uid,
|
|
];
|
|
|
|
$this->db->insert('qsl_postcard_templates', $row);
|
|
return (int)$this->db->insert_id();
|
|
}
|
|
|
|
// Duplicate a template's background image to a new file in this user's image
|
|
// dir, returning the new userdata-relative ('u') path. Returns null when the
|
|
// source has no image or the copy can't be made — never the original path, so
|
|
// a failure can't silently create a shared reference between templates.
|
|
private function duplicate_preview_image($preview_image) {
|
|
if (empty($preview_image)) {
|
|
return null;
|
|
}
|
|
|
|
try {
|
|
$file = basename((string)$preview_image);
|
|
$ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
|
|
if (!in_array($ext, ['jpg', 'jpeg', 'png'])) {
|
|
return null;
|
|
}
|
|
|
|
$dir = $this->paths->getUserdataPath('qslpostcard_images', 'p');
|
|
if ($dir === false) {
|
|
log_message('error', 'QSLPOSTCARD copy: image dir unavailable');
|
|
return null;
|
|
}
|
|
|
|
$srcPath = $dir . '/' . $file;
|
|
if (!file_exists($srcPath) || filesize($srcPath) > self::MAX_BG_IMAGE_BYTES) {
|
|
log_message('error', 'QSLPOSTCARD copy: source image missing or too large: ' . $srcPath);
|
|
return null;
|
|
}
|
|
|
|
// Random unique filename (equivalent to CI's encrypt_name), keeping the
|
|
// original extension. The loop guards the astronomical collision case.
|
|
$destName = bin2hex(random_bytes(16)) . '.' . $ext;
|
|
$destPath = $dir . '/' . $destName;
|
|
$guard = 0;
|
|
while (file_exists($destPath) && $guard++ < 5) {
|
|
$destName = bin2hex(random_bytes(16)) . '.' . $ext;
|
|
$destPath = $dir . '/' . $destName;
|
|
}
|
|
|
|
if (!@copy($srcPath, $destPath)) {
|
|
log_message('error', 'QSLPOSTCARD copy: could not copy image ' . $srcPath . ' -> ' . $destPath);
|
|
return null;
|
|
}
|
|
|
|
return $this->paths->getUserdataPath('qslpostcard_images', 'u') . '/' . $destName;
|
|
} catch (Exception $e) {
|
|
log_message('error', 'QSLPOSTCARD copy: error duplicating image: ' . $e->getMessage());
|
|
return null;
|
|
}
|
|
}
|
|
|
|
// TODO: Remove
|
|
// v1 demo: fetch last N QSOs from the logbook table.
|
|
// You will adjust table/column names based on your schema.
|
|
public function get_sample_qsos($limit = 25) {
|
|
$table = $this->config->item('table_name');
|
|
|
|
// Scope to the logged-in user's stations only
|
|
$sql = "SELECT {$table}.*, station_profile.station_callsign
|
|
FROM {$table}
|
|
INNER JOIN station_profile ON station_profile.station_id = {$table}.station_id
|
|
WHERE station_profile.user_id = ?
|
|
ORDER BY {$table}.COL_TIME_ON DESC
|
|
LIMIT " . (int)$limit;
|
|
|
|
$q = $this->db->query($sql, [$this->session->userdata('user_id')]);
|
|
|
|
if (!$q) {
|
|
$db_error = $this->db->error();
|
|
throw new Exception('DB query failed in get_sample_qsos(): ' . json_encode($db_error));
|
|
}
|
|
|
|
$rows = $q->result_array();
|
|
|
|
if (!empty($rows)) {
|
|
log_message('debug', 'QSLPOSTCARD sample row keys: ' . implode(', ', array_keys($rows[0])));
|
|
} else {
|
|
log_message('debug', 'QSLPOSTCARD get_sample_qsos() returned 0 rows');
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
private function resolve_address($callsign) {
|
|
if (!$this->load->is_loaded('callbook')) {
|
|
$this->load->library('callbook');
|
|
}
|
|
|
|
$callsign = strtoupper(trim($callsign));
|
|
if (empty($callsign)) return null;
|
|
|
|
// cache first
|
|
$cache = $this->cache->get('callbook_cache_' . md5($callsign));
|
|
if ($cache) {
|
|
return json_decode($cache['address_json'], true);
|
|
}
|
|
|
|
$callbookData = $this->callbook->getCallbookData($callsign);
|
|
|
|
$adr = $this->returnAddressFromXml($callbookData, $callsign);
|
|
|
|
if ($this->is_mailable_address($adr)) {
|
|
$this->cache_address($callsign, 'callbook', $adr);
|
|
return $adr;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private function returnAddressFromXml($callbookData, $callsign = '') {
|
|
$name = trim($callbookData['name'] ?? '');
|
|
if ($name === '') $name = trim($callbookData['nickname'] ?? '');
|
|
if ($name === '') $name = $callsign;
|
|
|
|
return [
|
|
'name' => $name,
|
|
'addr1' => $callbookData['addr1'] ?? '',
|
|
'addr2' => $callbookData['addr2'] ?? '',
|
|
'city' => $callbookData['city'] ?? '',
|
|
'state' => $callbookData['state'] ?? '',
|
|
'zip' => $callbookData['zip'] ?? '',
|
|
'country' => $callbookData['country'] ?? '',
|
|
];
|
|
}
|
|
|
|
private function is_mailable_address($callbookData) {
|
|
if (!is_array($callbookData)) return false;
|
|
|
|
$name = trim($callbookData['name'] ?? '');
|
|
$addr1 = trim($callbookData['addr1'] ?? '');
|
|
$city = trim($callbookData['city'] ?? '');
|
|
$state = trim($callbookData['state'] ?? '');
|
|
$zip = trim($callbookData['zip'] ?? '');
|
|
$country = trim($callbookData['country'] ?? '');
|
|
|
|
// Must at least have street line plus either
|
|
// city/state/zip info or country
|
|
if (empty($addr1)) {
|
|
return false;
|
|
}
|
|
|
|
$has_locality = !empty($city) || !empty($state) || !empty($zip);
|
|
$has_country = !empty($country);
|
|
|
|
return $has_locality || $has_country;
|
|
}
|
|
|
|
private function cache_address($callsign, $source, $addr) {
|
|
$addr['source'] = $source; // TODO: this is duped.. check if we can reduce this
|
|
$data = [
|
|
'callsign' => $callsign,
|
|
'source' => $source,
|
|
'address_json' => json_encode($addr, JSON_UNESCAPED_SLASHES),
|
|
];
|
|
|
|
$this->cache->save('callbook_cache_' . md5($callsign), $data, 60 * 60 * 24 * 20); // cache for 20 days
|
|
}
|
|
|
|
private function normalize_qso_datetime($qso) {
|
|
$rawDate = trim($qso['COL_QSO_DATE'] ?? $qso['qso_date'] ?? '');
|
|
$rawTime = trim($qso['COL_TIME_ON'] ?? $qso['time_on'] ?? '');
|
|
|
|
// Case 1: COL_TIME_ON already contains a full datetime
|
|
if ($rawTime !== '') {
|
|
$dt = $this->try_parse_datetime($rawTime);
|
|
if ($dt !== null) {
|
|
return $dt;
|
|
}
|
|
}
|
|
|
|
// Case 2: Separate date + time fields
|
|
if ($rawDate !== '') {
|
|
$dateDigits = preg_replace('/[^0-9]/', '', $rawDate);
|
|
$timeDigits = preg_replace('/[^0-9]/', '', $rawTime);
|
|
|
|
if (strlen($dateDigits) >= 8) {
|
|
$yyyy = substr($dateDigits, 0, 4);
|
|
$mm = substr($dateDigits, 4, 2);
|
|
$dd = substr($dateDigits, 6, 2);
|
|
|
|
if (strlen($timeDigits) >= 6) {
|
|
$hh = substr($timeDigits, 0, 2);
|
|
$mi = substr($timeDigits, 2, 2);
|
|
$ss = substr($timeDigits, 4, 2);
|
|
} elseif (strlen($timeDigits) >= 4) {
|
|
$hh = substr($timeDigits, 0, 2);
|
|
$mi = substr($timeDigits, 2, 2);
|
|
$ss = '00';
|
|
} else {
|
|
$hh = '00';
|
|
$mi = '00';
|
|
$ss = '00';
|
|
}
|
|
|
|
$str = sprintf('%s-%s-%s %s:%s:%s', $yyyy, $mm, $dd, $hh, $mi, $ss);
|
|
$dt = DateTime::createFromFormat('Y-m-d H:i:s', $str, new DateTimeZone('UTC'));
|
|
if ($dt !== false) {
|
|
return $dt;
|
|
}
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private function try_parse_datetime($raw) {
|
|
$raw = trim($raw);
|
|
if ($raw === '') return null;
|
|
|
|
$formats = [
|
|
'Y-m-d H:i:s',
|
|
'Y-m-d H:i',
|
|
'YmdHis',
|
|
'YmdHi',
|
|
'Ymd',
|
|
];
|
|
|
|
foreach ($formats as $fmt) {
|
|
$dt = DateTime::createFromFormat($fmt, $raw, new DateTimeZone('UTC'));
|
|
if ($dt !== false) {
|
|
return $dt;
|
|
}
|
|
}
|
|
|
|
// final fallback: strip to digits and try compact formats
|
|
$digits = preg_replace('/[^0-9]/', '', $raw);
|
|
|
|
foreach (['YmdHis', 'YmdHi', 'Ymd'] as $fmt) {
|
|
$dt = DateTime::createFromFormat($fmt, $digits, new DateTimeZone('UTC'));
|
|
if ($dt !== false) {
|
|
return $dt;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
private const FONT_MAP = [
|
|
'Helvetica' => 'DejaVuSans',
|
|
'Times' => 'DejaVuSerif',
|
|
'Courier' => 'DejaVuSansMono',
|
|
];
|
|
|
|
// --- PDF render (tFPDF, UTF-8 via embedded TrueType fonts) ---
|
|
public function render_pdf_from_layout($layout, $qsos, $mark_sent = false, $background = null, $noaddress = false) {
|
|
$tfpdfPath = FCPATH . 'src/Label/tfpdf.php';
|
|
if (!file_exists($tfpdfPath)) {
|
|
throw new Exception('tFPDF not found at ' . $tfpdfPath);
|
|
}
|
|
require_once($tfpdfPath);
|
|
|
|
// tFPDF reads FPDF_FONTPATH in its constructor; TTFs live in font/unifont/.
|
|
if (!defined('FPDF_FONTPATH')) {
|
|
define('FPDF_FONTPATH', FCPATH . 'src/Label/font/');
|
|
}
|
|
|
|
$w_mm = 139.7; // 5.5 in (standard QSL card size, landscape)
|
|
$h_mm = 88.9; // 3.5 in
|
|
|
|
$pdf = new tFPDF('L', 'mm', [$w_mm, $h_mm]);
|
|
$pdf->SetAutoPageBreak(false);
|
|
|
|
// Register each DejaVu family once, regular + bold (uni=true embeds a subset).
|
|
$pdf->AddFont('DejaVuSans', '', 'DejaVuSans.ttf', true);
|
|
$pdf->AddFont('DejaVuSans', 'B', 'DejaVuSans-Bold.ttf', true);
|
|
$pdf->AddFont('DejaVuSerif', '', 'DejaVuSerif.ttf', true);
|
|
$pdf->AddFont('DejaVuSerif', 'B', 'DejaVuSerif-Bold.ttf', true);
|
|
$pdf->AddFont('DejaVuSansMono', '', 'DejaVuSansMono.ttf', true);
|
|
$pdf->AddFont('DejaVuSansMono', 'B', 'DejaVuSansMono-Bold.ttf', true);
|
|
|
|
$cal = $layout['calibration'] ?? ['offset_x_in' => 0, 'offset_y_in' => 0];
|
|
$ox = (float)($cal['offset_x_in'] ?? 0);
|
|
$oy = (float)($cal['offset_y_in'] ?? 0);
|
|
|
|
// Template options (see layout.options). When qsos_per_card > 1, several
|
|
// QSOs of the SAME callsign share one card; "repeats per QSO" elements
|
|
// print once per QSO at a vertical pitch, the rest print once per card.
|
|
$opts = $layout['options'] ?? [];
|
|
$perCard = max(1, (int)($opts['qsos_per_card'] ?? 1));
|
|
$pitch = (float)($opts['row_pitch_in'] ?? 0.3);
|
|
|
|
// Background image (FPDF supports jpg/png/gif only)
|
|
$bgPath = null;
|
|
if (!empty($background)) {
|
|
// preview_image is untrusted client input; basename() + the user's own dir make ../ impossible
|
|
$dir = $this->paths->getUserdataPath('qslpostcard_images', 'p');
|
|
$candidate = $dir . '/' . basename($background);
|
|
$ext = strtolower(pathinfo($candidate, PATHINFO_EXTENSION));
|
|
if ($dir !== false && file_exists($candidate) && in_array($ext, ['jpg', 'jpeg', 'png']) && filesize($candidate) <= self::MAX_BG_IMAGE_BYTES && @getimagesize($candidate) !== false) {
|
|
$bgPath = $candidate;
|
|
} else {
|
|
log_message('error', 'QSLPOSTCARD background image not usable: ' . $candidate);
|
|
}
|
|
}
|
|
|
|
// Only resolve addresses when the template actually prints one: without
|
|
// any addr.* element the callbook lookups would be pointless work (and
|
|
// remote API calls). Same addr.* predicate as in the element loop below.
|
|
// Only needs to be done if $noaddress is not already true.
|
|
if (!$noaddress) {
|
|
$hasAddrElement = false;
|
|
foreach (($layout['elements'] ?? []) as $el) {
|
|
if (($el['type'] ?? 'field') !== 'text' && str_starts_with($el['field'] ?? '', 'addr.')) {
|
|
$hasAddrElement = true;
|
|
break;
|
|
}
|
|
}
|
|
if (!$hasAddrElement) {
|
|
log_message('debug', 'QSLPOSTCARD template has no addr.* elements, skipping address resolution');
|
|
$noaddress = true;
|
|
}
|
|
}
|
|
|
|
// A QSL card is addressed to a single station, so QSOs are ALWAYS grouped
|
|
// by callsign — a card never mixes contacts with different callsigns. Each
|
|
// group is then split into cards of $perCard QSOs, spilling onto further
|
|
// cards when a callsign has more QSOs than fit. The first QSO of each chunk
|
|
// drives the per-card address resolution below.
|
|
$groups = [];
|
|
foreach ($qsos as $qso) {
|
|
$key = strtoupper(trim($qso['COL_CALL'] ?? ''));
|
|
if ($key === '') {
|
|
continue;
|
|
}
|
|
$groups[$key][] = $qso;
|
|
}
|
|
|
|
foreach ($groups as $groupQsos) {
|
|
foreach (array_chunk($groupQsos, $perCard) as $chunk) {
|
|
$qso = $chunk[0];
|
|
$call = strtoupper(trim($qso['COL_CALL'] ?? ''));
|
|
if ($noaddress) {
|
|
$addr = null;
|
|
} else {
|
|
$addr = $call ? $this->resolve_address($call) : null;
|
|
// No usable mailing address: still print the card, but mark the
|
|
// address as not found instead of silently dropping the whole card
|
|
// (which produced an empty PDF when no callsign resolved to a
|
|
// mailable address).
|
|
if (!$this->is_mailable_address($addr)) {
|
|
log_message('debug', 'QSLPOSTCARD no usable address for ' . $call . ', printing placeholder');
|
|
$addr = [
|
|
'name' => $call,
|
|
'addr1' => _pgettext("QSL Card Designer; No mailable address found", "!!! NO MAILABLE ADDRESS FOUND !!!"),
|
|
];
|
|
}
|
|
}
|
|
|
|
// Only create a page after address is confirmed
|
|
$pdf->AddPage();
|
|
|
|
if ($bgPath !== null) {
|
|
$pdf->Image($bgPath, 0, 0, $w_mm, $h_mm);
|
|
}
|
|
|
|
foreach (($layout['elements'] ?? []) as $el) {
|
|
$type = $el['type'] ?? 'field';
|
|
$field = $el['field'] ?? '';
|
|
// addr.* fields are always static (one address per card); other
|
|
// "repeats per QSO" fields print once per QSO in the chunk.
|
|
$isAddr = ($type !== 'text') && str_starts_with($field, 'addr.');
|
|
$repeat = !empty($el['repeat_per_qso']) && !$isAddr;
|
|
$targets = $repeat ? $chunk : [$chunk[0]];
|
|
|
|
$font = self::FONT_MAP[$el['font'] ?? 'Helvetica'] ?? 'DejaVuSans';
|
|
$pt = (float)($el['font_pt'] ?? 11);
|
|
$bold = !empty($el['bold']) ? 'B' : '';
|
|
$wrap_w_in = isset($el['wrap_w_in']) ? (float)$el['wrap_w_in'] : 0;
|
|
[$cr, $cg, $cb] = $this->hex_to_rgb($el['color'] ?? '#000000');
|
|
|
|
foreach ($targets as $rowIdx => $qso) {
|
|
if ($type === 'text') {
|
|
$val = $el['text'] ?? '';
|
|
} else {
|
|
$val = $this->resolve_field($field, $qso, $addr, $el);
|
|
}
|
|
|
|
if ($val === '') {
|
|
continue;
|
|
}
|
|
|
|
$x_in = (float)($el['x_in'] ?? 0) + $ox;
|
|
$y_in = (float)($el['y_in'] ?? 0) + $oy + ($repeat ? $pitch * $rowIdx : 0);
|
|
|
|
$x_mm = $x_in * 25.4;
|
|
$y_mm = $y_in * 25.4;
|
|
|
|
$pdf->SetFont($font, $bold, $pt);
|
|
$pdf->SetTextColor($cr, $cg, $cb);
|
|
|
|
if ($wrap_w_in > 0) {
|
|
$w = $wrap_w_in * 25.4;
|
|
$pdf->SetXY($x_mm, $y_mm);
|
|
$pdf->MultiCell($w, 4.5, $val);
|
|
} else {
|
|
$pdf->Text($x_mm, $y_mm + ($pt * 0.30), $val);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$tmp = sys_get_temp_dir() . '/qsl_postcards_' . uniqid() . '.pdf';
|
|
$pdf->Output('F', $tmp);
|
|
|
|
if (!file_exists($tmp)) {
|
|
throw new Exception('FPDF completed but temp PDF was not created');
|
|
}
|
|
|
|
return $tmp;
|
|
}
|
|
|
|
// "#rrggbb" (or "#rgb") → [r, g, b]; falls back to black on anything malformed.
|
|
private function hex_to_rgb($hex) {
|
|
$hex = ltrim((string)$hex, '#');
|
|
if (strlen($hex) === 3) {
|
|
$hex = $hex[0] . $hex[0] . $hex[1] . $hex[1] . $hex[2] . $hex[2];
|
|
}
|
|
if (!preg_match('/^[0-9a-fA-F]{6}$/', $hex)) {
|
|
return [0, 0, 0];
|
|
}
|
|
return [
|
|
hexdec($hex[0] . $hex[1]),
|
|
hexdec($hex[2] . $hex[3]),
|
|
hexdec($hex[4] . $hex[5]),
|
|
];
|
|
}
|
|
|
|
public function get_qsl_queue_qsos($filters = []) {
|
|
$table = $this->config->item('table_name');
|
|
|
|
// Scope to the logged-in user's stations only
|
|
$binding = [$this->session->userdata('user_id')];
|
|
|
|
// Most likely starting point for physical cards:
|
|
// requested cards or unsent cards
|
|
// Adjust after we confirm your queue logic.
|
|
$sql = "SELECT {$table}.*, station_profile.station_callsign
|
|
FROM {$table}
|
|
INNER JOIN station_profile ON station_profile.station_id = {$table}.station_id
|
|
WHERE station_profile.user_id = ?
|
|
AND ({$table}.COL_QSL_SENT IN ('R', 'Q', '') OR {$table}.COL_QSL_SENT IS NULL)";
|
|
|
|
if (!empty($filters['station_id'])) {
|
|
$sql .= " AND {$table}.station_id = ?";
|
|
$binding[] = $filters['station_id'];
|
|
}
|
|
|
|
if (!empty($filters['band'])) {
|
|
$sql .= " AND {$table}.COL_BAND = ?";
|
|
$binding[] = $filters['band'];
|
|
}
|
|
|
|
if (!empty($filters['mode'])) {
|
|
$sql .= " AND {$table}.COL_MODE = ?";
|
|
$binding[] = $filters['mode'];
|
|
}
|
|
|
|
if (!empty($filters['call'])) {
|
|
$sql .= " AND {$table}.COL_CALL LIKE ?";
|
|
$binding[] = '%' . $filters['call'] . '%';
|
|
}
|
|
|
|
$sql .= " ORDER BY {$table}.COL_TIME_ON DESC";
|
|
|
|
$q = $this->db->query($sql, $binding);
|
|
if (!$q) {
|
|
$db_error = $this->db->error();
|
|
throw new Exception('DB query failed in get_qsl_queue_qsos(): ' . json_encode($db_error));
|
|
}
|
|
|
|
return $q->result_array();
|
|
}
|
|
|
|
public function get_qsos_by_ids(array $ids) {
|
|
$ids = array_values(array_filter(array_map('intval', $ids)));
|
|
|
|
if (empty($ids)) {
|
|
return [];
|
|
}
|
|
|
|
$table = $this->config->item('table_name');
|
|
$placeholders = implode(',', array_fill(0, count($ids), '?'));
|
|
|
|
// Scope to the logged-in user's stations only
|
|
$sql = "SELECT {$table}.*, station_profile.station_callsign
|
|
FROM {$table}
|
|
INNER JOIN station_profile ON station_profile.station_id = {$table}.station_id
|
|
WHERE station_profile.user_id = ?
|
|
AND {$table}.COL_PRIMARY_KEY IN (" . $placeholders . ")
|
|
ORDER BY {$table}.COL_TIME_ON DESC";
|
|
|
|
$binding = array_merge([$this->session->userdata('user_id')], $ids);
|
|
|
|
$q = $this->db->query($sql, $binding);
|
|
|
|
if (!$q) {
|
|
$db_error = $this->db->error();
|
|
throw new Exception('DB query failed in get_qsos_by_ids(): ' . json_encode($db_error));
|
|
}
|
|
|
|
return $q->result_array();
|
|
}
|
|
|
|
private function pretty_sat_mode($sat_mode) {
|
|
return (strlen($sat_mode ?? '') == 2) ? (strtoupper($sat_mode[0]) . '/' . strtoupper($sat_mode[1])) : strtoupper($sat_mode ?? '');
|
|
}
|
|
|
|
private function resolve_band($qso) {
|
|
$prop = strtoupper(trim($qso['COL_PROP_MODE'] ?? ''));
|
|
$sat = trim($qso['COL_SAT_NAME'] ?? '');
|
|
if ($prop === 'SAT' && $sat !== '') {
|
|
$mode = $this->pretty_sat_mode($qso['COL_SAT_MODE'] ?? '');
|
|
return $mode !== '' ? $sat . ' ' . $mode : $sat; // e.g. "AO-7 U/V"
|
|
}
|
|
return $qso['COL_BAND'] ?? $qso['band'] ?? '';
|
|
}
|
|
|
|
private function resolve_mode($qso) {
|
|
$sub = trim($qso['COL_SUBMODE'] ?? '');
|
|
return $sub !== '' ? $sub : ($qso['COL_MODE'] ?? $qso['mode'] ?? '');
|
|
}
|
|
|
|
private function resolve_field($field, $qso, $addr, $el = []) {
|
|
|
|
// Address computed fields
|
|
if ($field === 'addr.source') {
|
|
return $addr['source'] ?? '';
|
|
}
|
|
if ($field === 'addr.name') return $addr['name'] ?? '';
|
|
if ($field === 'addr.addr1') return $addr['addr1'] ?? '';
|
|
if ($field === 'addr.addr2') return $addr['addr2'] ?? '';
|
|
if ($field === 'addr.city') return $addr['city'] ?? '';
|
|
if ($field === 'addr.state') return $addr['state'] ?? '';
|
|
if ($field === 'addr.zip') return $addr['zip'] ?? '';
|
|
if ($field === 'addr.country') return $addr['country'] ?? '';
|
|
if ($field === 'addr.city_state_zip') {
|
|
$city = trim($addr['city'] ?? '');
|
|
$state = trim($addr['state'] ?? '');
|
|
$zip = trim($addr['zip'] ?? '');
|
|
|
|
if ($city !== '' && $state !== '' && $zip !== '') {
|
|
return $city . ', ' . $state . ' ' . $zip;
|
|
}
|
|
|
|
if ($city !== '' && $state !== '') {
|
|
return $city . ', ' . $state;
|
|
}
|
|
|
|
if ($city !== '' && $zip !== '') {
|
|
return $city . ' ' . $zip;
|
|
}
|
|
|
|
if ($city !== '') {
|
|
return $city;
|
|
}
|
|
|
|
if ($state !== '' && $zip !== '') {
|
|
return $state . ' ' . $zip;
|
|
}
|
|
|
|
return trim($state . ' ' . $zip);
|
|
}
|
|
// QSO fields (adjust keys to match your DB)
|
|
if ($field === 'qso.call') return strtoupper($qso['COL_CALL'] ?? $qso['call'] ?? '');
|
|
if ($field === 'qso.qso_date') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('Y-m-d') : '';
|
|
}
|
|
if ($field === 'qso.time_on') return $qso['COL_TIME_ON'] ?? $qso['time_on'] ?? '';
|
|
if ($field === 'qso.band') return $this->resolve_band($qso);
|
|
if ($field === 'qso.mode') return $this->resolve_mode($qso);
|
|
if ($field === 'qso.sat_name') return trim($qso['COL_SAT_NAME'] ?? '');
|
|
if ($field === 'qso.sat_mode') return $this->pretty_sat_mode($qso['COL_SAT_MODE'] ?? '');
|
|
if ($field === 'qso.freq') {
|
|
$hz = $qso['COL_FREQ'] ?? $qso['freq'] ?? '';
|
|
if ($hz === '' || $hz === null || (float)$hz == 0) {
|
|
return '';
|
|
}
|
|
$unit = $el['freq_format'] ?? 'MHz';
|
|
if (!in_array($unit, ['Hz', 'kHz', 'MHz'], true)) {
|
|
$unit = 'MHz';
|
|
}
|
|
if (!$this->load->is_loaded('frequency')) {
|
|
$this->load->library('frequency');
|
|
}
|
|
$r_option = !empty($el['freq_no_unit']) ? 0 : 1;
|
|
return $this->frequency->qrg_conversion((float)$hz, $r_option, 'Hz', $unit) ?? '';
|
|
}
|
|
if ($field === 'qso.rst_sent') return $qso['COL_RST_SENT'] ?? $qso['rst_sent'] ?? '';
|
|
if ($field === 'qso.rst_rcvd') return $qso['COL_RST_RCVD'] ?? $qso['rst_rcvd'] ?? '';
|
|
if ($field === 'qso.r_sent') return substr($qso['COL_RST_SENT'] ?? $qso['rst_sent'] ?? '', 0, 1);
|
|
if ($field === 'qso.s_sent') return substr($qso['COL_RST_SENT'] ?? $qso['rst_sent'] ?? '', 1, 1);
|
|
if ($field === 'qso.t_sent') return substr($qso['COL_RST_SENT'] ?? $qso['rst_sent'] ?? '', 2, 1);
|
|
if ($field === 'qso.qsl_message') {
|
|
return $qso['COL_QSLMSG'] ?? '';
|
|
}
|
|
|
|
if ($field === 'qso.antenna') {
|
|
return $qso['COL_MY_ANTENNA'] ?? $qso['COL_MY_ANTENNA_INTL'] ?? '';
|
|
}
|
|
|
|
if ($field === 'qso.tx_power') {
|
|
$pwr = trim($qso['COL_TX_PWR'] ?? '');
|
|
return $pwr !== '' ? $pwr . ' W' : '';
|
|
}
|
|
|
|
if ($field === 'qso.rx_power') {
|
|
$pwr = trim($qso['COL_RX_PWR'] ?? '');
|
|
return $pwr !== '' ? $pwr . ' W' : '';
|
|
}
|
|
|
|
if ($field === 'qso.my_rig') {
|
|
return $qso['COL_MY_RIG'] ?? '';
|
|
}
|
|
|
|
if ($field === 'qso.rig') {
|
|
return $qso['COL_RIG'] ?? $qso['station_profile_name'] ?? $qso['rig'] ?? '';
|
|
}
|
|
|
|
if ($field === 'qso.comment') {
|
|
return $qso['COL_COMMENT'] ?? $qso['COL_NOTES'] ?? $qso['comment'] ?? '';
|
|
}
|
|
|
|
if ($field === 'qso.month_name') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? strtoupper($dt->format('M')) : '';
|
|
}
|
|
|
|
if ($field === 'qso.time_utc') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('H:i') . ' UTC' : '';
|
|
}
|
|
|
|
if ($field === 'qso.time') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('H:i') : '';
|
|
}
|
|
|
|
if ($field === 'qso.day') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('d') : '';
|
|
}
|
|
|
|
if ($field === 'qso.month') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('m') : '';
|
|
}
|
|
|
|
if ($field === 'qso.year') {
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
return $dt ? $dt->format('Y') : '';
|
|
}
|
|
|
|
if ($field === 'qso.pota_ref') return $qso['COL_POTA_REF'] ?? '';
|
|
|
|
if ($field === 'qso.my_pota_ref') return $qso['COL_MY_POTA_REF'] ?? '';
|
|
|
|
if ($field === 'qso.pota_line') {
|
|
$ref = trim($qso['COL_MY_POTA_REF'] ?? '');
|
|
return $ref !== '' ? 'From POTA: ' . $ref : '';
|
|
}
|
|
|
|
if ($field === 'qso.sota_ref') return $qso['COL_SOTA_REF'] ?? '';
|
|
|
|
if ($field === 'qso.my_sota_ref') return $qso['COL_MY_SOTA_REF'] ?? '';
|
|
|
|
if ($field === 'qso.sota_line') {
|
|
$ref = trim($qso['COL_MY_SOTA_REF'] ?? '');
|
|
return $ref !== '' ? 'From SOTA: ' . $ref : '';
|
|
}
|
|
|
|
if ($field === 'qso.iota_ref') return $qso['COL_IOTA'] ?? '';
|
|
|
|
if ($field === 'qso.my_iota_ref') return $qso['COL_MY_IOTA'] ?? '';
|
|
|
|
if ($field === 'qso.my_grid') return trim($qso['COL_MY_GRIDSQUARE'] ?? '');
|
|
|
|
if ($field === 'qso.station_callsign') {
|
|
return strtoupper($qso['station_callsign'] ?? $qso['COL_STATION_CALLSIGN'] ?? '');
|
|
}
|
|
|
|
if ($field === 'qso.operator') {
|
|
$op = strtoupper(trim($qso['COL_OPERATOR'] ?? ''));
|
|
if ($op !== '') {
|
|
return $op;
|
|
}
|
|
return strtoupper($qso['station_callsign'] ?? $qso['COL_STATION_CALLSIGN'] ?? '');
|
|
}
|
|
|
|
if ($field === 'qso.iota_line') {
|
|
$ref = trim($qso['COL_MY_IOTA'] ?? '');
|
|
return $ref !== '' ? 'From IOTA: ' . $ref : '';
|
|
}
|
|
|
|
if ($field === 'qso.qsl_via') return $qso['COL_QSL_VIA'] ?? $qso['qsl_via'] ?? '';
|
|
|
|
if ($field === 'qso.summary') {
|
|
$c = strtoupper($qso['COL_CALL'] ?? $qso['call'] ?? '');
|
|
$dt = $this->normalize_qso_datetime($qso);
|
|
$d = $dt ? $dt->format('Y-m-d') : '';
|
|
$t = $qso['COL_TIME_ON'] ?? $qso['time_on'] ?? '';
|
|
$b = $this->resolve_band($qso);
|
|
$m = $this->resolve_mode($qso);
|
|
$rs = $qso['COL_RST_SENT'] ?? $qso['rst_sent'] ?? '';
|
|
$rr = $qso['COL_RST_RCVD'] ?? $qso['rst_rcvd'] ?? '';
|
|
return trim("$c $d $t $b $m $rs/$rr");
|
|
}
|
|
|
|
if ($field === 'qso.pse_qsl' || $field === 'qso.tnx_qsl') {
|
|
$received = in_array(strtoupper(trim($qso['COL_QSL_RCVD'] ?? '')), ['Y', 'V'], true);
|
|
return ($field === 'qso.tnx_qsl') === $received ? 'X' : '';
|
|
}
|
|
|
|
if ($field === 'qso.pse_qsl_tnx_text') {
|
|
$received = in_array(strtoupper(trim($qso['COL_QSL_RCVD'] ?? '')), ['Y', 'V'], true);
|
|
return ($received ? 'TNX QSL' : 'PSE QSL');
|
|
}
|
|
|
|
if ($field === 'qso.portable') {
|
|
$mycall = strtoupper(trim($qso['COL_STATION_CALLSIGN'] ?? ''));
|
|
return ($mycall !== '' && preg_match('#/P$#', $mycall)) ? 'X' : '';
|
|
}
|
|
|
|
if ($field === 'qso.mobile') {
|
|
$mycall = strtoupper(trim($qso['COL_STATION_CALLSIGN'] ?? ''));
|
|
return ($mycall !== '' && preg_match('#/M$#', $mycall)) ? 'X' : '';
|
|
}
|
|
|
|
return '';
|
|
}
|
|
|
|
function delete_template($id) {
|
|
try {
|
|
$uid = $this->session->userdata('user_id');
|
|
|
|
$sql = "SELECT preview_image FROM qsl_postcard_templates WHERE id = ? AND user_id = ?";
|
|
$tpl = $this->db->query($sql, [$id, $uid])->row_array();
|
|
|
|
$sql = "DELETE FROM qsl_postcard_templates WHERE id = ? AND user_id = ?";
|
|
$this->db->query($sql, [$id, $uid]);
|
|
|
|
if (!empty($tpl['preview_image'])) {
|
|
$this->unlink_preview_image($uid, $tpl['preview_image']);
|
|
}
|
|
|
|
return true;
|
|
} catch (Exception $e) {
|
|
log_message('error', 'Error deleting QSL postcard template: ' . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function unlink_preview_image($uid, $preview_image) {
|
|
try {
|
|
$file = basename($preview_image);
|
|
$ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
|
|
if (!in_array($ext, ['jpg', 'jpeg', 'png'])) {
|
|
return;
|
|
}
|
|
|
|
$sql = "SELECT COUNT(1) AS cnt FROM qsl_postcard_templates WHERE user_id = ? AND preview_image LIKE ?";
|
|
$row = $this->db->query($sql, [$uid, '%' . $file])->row_array();
|
|
if (!empty($row['cnt'])) {
|
|
return;
|
|
}
|
|
|
|
$dir = $this->paths->getUserdataPath('qslpostcard_images', 'p');
|
|
if ($dir === false) {
|
|
return;
|
|
}
|
|
$candidate = $dir . '/' . $file;
|
|
if (file_exists($candidate) && !@unlink($candidate)) {
|
|
log_message('error', 'QSLPOSTCARD could not unlink background image: ' . $candidate);
|
|
}
|
|
} catch (Exception $e) {
|
|
log_message('error', 'QSLPOSTCARD error unlinking background image: ' . $e->getMessage());
|
|
}
|
|
}
|
|
}
|