// Code generated by sqlc. DO NOT EDIT. // versions: // sqlc v1.27.0 // source: distribution.sql package db import ( "context" "github.com/jackc/pgx/v5/pgtype" "github.com/shopspring/decimal" ) const activeShipmentLocations = `-- name: ActiveShipmentLocations :many SELECT DISTINCT ON (s.id) s.id AS shipment_id, s.shipment_number, s.status, s.ship_date, d.do_number, e.latitude, e.longitude, e.event_time AS last_fix_at FROM shipments s JOIN delivery_orders d ON d.id = s.delivery_order_id LEFT JOIN shipment_tracking_events e ON e.shipment_id = s.id WHERE s.status IN ('pending','in_transit') ORDER BY s.id, e.event_time DESC, e.id DESC ` type ActiveShipmentLocationsRow struct { ShipmentID int64 `json:"shipment_id"` ShipmentNumber string `json:"shipment_number"` Status string `json:"status"` ShipDate pgtype.Date `json:"ship_date"` DoNumber string `json:"do_number"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` LastFixAt pgtype.Timestamptz `json:"last_fix_at"` } func (q *Queries) ActiveShipmentLocations(ctx context.Context) ([]ActiveShipmentLocationsRow, error) { rows, err := q.db.Query(ctx, activeShipmentLocations) if err != nil { return nil, err } defer rows.Close() var items []ActiveShipmentLocationsRow for rows.Next() { var i ActiveShipmentLocationsRow if err := rows.Scan( &i.ShipmentID, &i.ShipmentNumber, &i.Status, &i.ShipDate, &i.DoNumber, &i.Latitude, &i.Longitude, &i.LastFixAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const countDeliveryOrders = `-- name: CountDeliveryOrders :one SELECT count(*) FROM delivery_orders WHERE ($1::text IS NULL OR status = $1) AND ($2::bigint IS NULL OR sales_order_id = $2) AND ($3::bigint IS NULL OR $3::bigint <= 0 OR customer_id = $3) AND ($4::text IS NULL OR do_number ILIKE '%' || $4 || '%') ` type CountDeliveryOrdersParams struct { Status pgtype.Text `json:"status"` SalesOrderID pgtype.Int8 `json:"sales_order_id"` CustomerID pgtype.Int8 `json:"customer_id"` Search pgtype.Text `json:"search"` } func (q *Queries) CountDeliveryOrders(ctx context.Context, arg CountDeliveryOrdersParams) (int64, error) { row := q.db.QueryRow(ctx, countDeliveryOrders, arg.Status, arg.SalesOrderID, arg.CustomerID, arg.Search, ) var count int64 err := row.Scan(&count) return count, err } const countFleet = `-- name: CountFleet :one SELECT count(*) FROM fleet WHERE ($1::bool IS NULL OR is_active = $1) ` func (q *Queries) CountFleet(ctx context.Context, isActive pgtype.Bool) (int64, error) { row := q.db.QueryRow(ctx, countFleet, isActive) var count int64 err := row.Scan(&count) return count, err } const createDeliveryOrder = `-- name: CreateDeliveryOrder :one INSERT INTO delivery_orders (do_number, sales_order_id, customer_id, warehouse_id, do_date, priority, status, requested_quantity, shipped_quantity, delivered_quantity, remaining_quantity, notes, created_by) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING id, created_at, updated_at ` type CreateDeliveryOrderParams struct { DoNumber string `json:"do_number"` SalesOrderID int64 `json:"sales_order_id"` CustomerID int64 `json:"customer_id"` WarehouseID int64 `json:"warehouse_id"` DoDate pgtype.Date `json:"do_date"` Priority string `json:"priority"` Status string `json:"status"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` } type CreateDeliveryOrderRow struct { ID int64 `json:"id"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` } func (q *Queries) CreateDeliveryOrder(ctx context.Context, arg CreateDeliveryOrderParams) (CreateDeliveryOrderRow, error) { row := q.db.QueryRow(ctx, createDeliveryOrder, arg.DoNumber, arg.SalesOrderID, arg.CustomerID, arg.WarehouseID, arg.DoDate, arg.Priority, arg.Status, arg.RequestedQuantity, arg.ShippedQuantity, arg.DeliveredQuantity, arg.RemainingQuantity, arg.Notes, arg.CreatedBy, ) var i CreateDeliveryOrderRow err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt) return i, err } const createDeliveryOrderItem = `-- name: CreateDeliveryOrderItem :one INSERT INTO delivery_order_items (delivery_order_id, product_id, requested_quantity, shipped_quantity, delivered_quantity, remaining_quantity) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id ` type CreateDeliveryOrderItemParams struct { DeliveryOrderID int64 `json:"delivery_order_id"` ProductID int64 `json:"product_id"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` } func (q *Queries) CreateDeliveryOrderItem(ctx context.Context, arg CreateDeliveryOrderItemParams) (int64, error) { row := q.db.QueryRow(ctx, createDeliveryOrderItem, arg.DeliveryOrderID, arg.ProductID, arg.RequestedQuantity, arg.ShippedQuantity, arg.DeliveredQuantity, arg.RemainingQuantity, ) var id int64 err := row.Scan(&id) return id, err } const createDeliveryStatusHistory = `-- name: CreateDeliveryStatusHistory :one INSERT INTO delivery_status_history (delivery_order_id, from_status, to_status, reason, reference_type, reference_id, changed_by) VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING id ` type CreateDeliveryStatusHistoryParams struct { DeliveryOrderID int64 `json:"delivery_order_id"` FromStatus string `json:"from_status"` ToStatus string `json:"to_status"` Reason string `json:"reason"` ReferenceType pgtype.Text `json:"reference_type"` ReferenceID pgtype.Int8 `json:"reference_id"` ChangedBy pgtype.Int8 `json:"changed_by"` } func (q *Queries) CreateDeliveryStatusHistory(ctx context.Context, arg CreateDeliveryStatusHistoryParams) (int64, error) { row := q.db.QueryRow(ctx, createDeliveryStatusHistory, arg.DeliveryOrderID, arg.FromStatus, arg.ToStatus, arg.Reason, arg.ReferenceType, arg.ReferenceID, arg.ChangedBy, ) var id int64 err := row.Scan(&id) return id, err } const createDispatch = `-- name: CreateDispatch :one INSERT INTO dispatches (delivery_order_id, shipment_id, fleet_id, priority, queue_order, driver_id, scheduled_for, status, dispatched_at, dispatcher_id) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING id, created_at, updated_at ` type CreateDispatchParams struct { DeliveryOrderID int64 `json:"delivery_order_id"` ShipmentID int64 `json:"shipment_id"` FleetID int64 `json:"fleet_id"` Priority string `json:"priority"` QueueOrder int32 `json:"queue_order"` DriverID pgtype.Int8 `json:"driver_id"` ScheduledFor pgtype.Timestamptz `json:"scheduled_for"` Status string `json:"status"` DispatchedAt pgtype.Timestamptz `json:"dispatched_at"` DispatcherID pgtype.Int8 `json:"dispatcher_id"` } type CreateDispatchRow struct { ID int64 `json:"id"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` } func (q *Queries) CreateDispatch(ctx context.Context, arg CreateDispatchParams) (CreateDispatchRow, error) { row := q.db.QueryRow(ctx, createDispatch, arg.DeliveryOrderID, arg.ShipmentID, arg.FleetID, arg.Priority, arg.QueueOrder, arg.DriverID, arg.ScheduledFor, arg.Status, arg.DispatchedAt, arg.DispatcherID, ) var i CreateDispatchRow err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt) return i, err } const createDriverUser = `-- name: CreateDriverUser :one INSERT INTO users (name, email, password, role, application_access, is_active) VALUES ($1, $2, $3, 'employee', 'driver', true) RETURNING id, name ` type CreateDriverUserParams struct { Name string `json:"name"` Email string `json:"email"` Password string `json:"password"` } type CreateDriverUserRow struct { ID int64 `json:"id"` Name string `json:"name"` } // Driver accounts are users in name only: a random unguessable password (never // surfaced), role 'employee' (this schema's lowest-privilege role, granted only // dashboard.view/hr.clockin/hr.clockout/hr.leaves.store — see catalogSpec), and // application_access='driver' so they never resolve to a real ERP/POS/ecommerce // surface even if somehow logged in. This exists purely so dispatches.driver_id // has a users.id to point at. func (q *Queries) CreateDriverUser(ctx context.Context, arg CreateDriverUserParams) (CreateDriverUserRow, error) { row := q.db.QueryRow(ctx, createDriverUser, arg.Name, arg.Email, arg.Password) var i CreateDriverUserRow err := row.Scan(&i.ID, &i.Name) return i, err } const createFleet = `-- name: CreateFleet :one INSERT INTO fleet (plate_number, vehicle_name, vehicle_type, capacity_weight, capacity_volume, is_active) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id, created_at, updated_at ` type CreateFleetParams struct { PlateNumber string `json:"plate_number"` VehicleName string `json:"vehicle_name"` VehicleType pgtype.Text `json:"vehicle_type"` CapacityWeight decimal.NullDecimal `json:"capacity_weight"` CapacityVolume decimal.NullDecimal `json:"capacity_volume"` IsActive bool `json:"is_active"` } type CreateFleetRow struct { ID int64 `json:"id"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` } func (q *Queries) CreateFleet(ctx context.Context, arg CreateFleetParams) (CreateFleetRow, error) { row := q.db.QueryRow(ctx, createFleet, arg.PlateNumber, arg.VehicleName, arg.VehicleType, arg.CapacityWeight, arg.CapacityVolume, arg.IsActive, ) var i CreateFleetRow err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt) return i, err } const createShipment = `-- name: CreateShipment :one INSERT INTO shipments (shipment_number, delivery_order_id, status, ship_date, notes, created_by, tracking_number, barcode) VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING id, created_at, updated_at ` type CreateShipmentParams struct { ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate pgtype.Date `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } type CreateShipmentRow struct { ID int64 `json:"id"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` } func (q *Queries) CreateShipment(ctx context.Context, arg CreateShipmentParams) (CreateShipmentRow, error) { row := q.db.QueryRow(ctx, createShipment, arg.ShipmentNumber, arg.DeliveryOrderID, arg.Status, arg.ShipDate, arg.Notes, arg.CreatedBy, arg.TrackingNumber, arg.Barcode, ) var i CreateShipmentRow err := row.Scan(&i.ID, &i.CreatedAt, &i.UpdatedAt) return i, err } const createShipmentItem = `-- name: CreateShipmentItem :one INSERT INTO shipment_items (shipment_id, delivery_order_item_id, product_id, shipped_quantity, delivered_quantity) VALUES ($1,$2,$3,$4,0) RETURNING id ` type CreateShipmentItemParams struct { ShipmentID int64 `json:"shipment_id"` DeliveryOrderItemID int64 `json:"delivery_order_item_id"` ProductID int64 `json:"product_id"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` } func (q *Queries) CreateShipmentItem(ctx context.Context, arg CreateShipmentItemParams) (int64, error) { row := q.db.QueryRow(ctx, createShipmentItem, arg.ShipmentID, arg.DeliveryOrderItemID, arg.ProductID, arg.ShippedQuantity, ) var id int64 err := row.Scan(&id) return id, err } const createShipmentTrackingEvent = `-- name: CreateShipmentTrackingEvent :one INSERT INTO shipment_tracking_events (shipment_id, event_time, status, source, latitude, longitude, accuracy_m, speed_mps, heading_deg, note, provider_ref, created_by) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING id, created_at ` type CreateShipmentTrackingEventParams struct { ShipmentID int64 `json:"shipment_id"` EventTime pgtype.Timestamptz `json:"event_time"` Status string `json:"status"` Source string `json:"source"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` AccuracyM decimal.NullDecimal `json:"accuracy_m"` SpeedMps decimal.NullDecimal `json:"speed_mps"` HeadingDeg decimal.NullDecimal `json:"heading_deg"` Note pgtype.Text `json:"note"` ProviderRef pgtype.Text `json:"provider_ref"` CreatedBy pgtype.Int8 `json:"created_by"` } type CreateShipmentTrackingEventRow struct { ID int64 `json:"id"` CreatedAt pgtype.Timestamptz `json:"created_at"` } func (q *Queries) CreateShipmentTrackingEvent(ctx context.Context, arg CreateShipmentTrackingEventParams) (CreateShipmentTrackingEventRow, error) { row := q.db.QueryRow(ctx, createShipmentTrackingEvent, arg.ShipmentID, arg.EventTime, arg.Status, arg.Source, arg.Latitude, arg.Longitude, arg.AccuracyM, arg.SpeedMps, arg.HeadingDeg, arg.Note, arg.ProviderRef, arg.CreatedBy, ) var i CreateShipmentTrackingEventRow err := row.Scan(&i.ID, &i.CreatedAt) return i, err } const dispatchByShipmentID = `-- name: DispatchByShipmentID :one SELECT id, delivery_order_id, shipment_id, fleet_id, driver_id, priority, queue_order, status, dispatched_at FROM dispatches WHERE shipment_id=$1 ORDER BY id DESC LIMIT 1 ` type DispatchByShipmentIDRow struct { ID int64 `json:"id"` DeliveryOrderID int64 `json:"delivery_order_id"` ShipmentID int64 `json:"shipment_id"` FleetID int64 `json:"fleet_id"` DriverID pgtype.Int8 `json:"driver_id"` Priority string `json:"priority"` QueueOrder int32 `json:"queue_order"` Status string `json:"status"` DispatchedAt pgtype.Timestamptz `json:"dispatched_at"` } func (q *Queries) DispatchByShipmentID(ctx context.Context, shipmentID int64) (DispatchByShipmentIDRow, error) { row := q.db.QueryRow(ctx, dispatchByShipmentID, shipmentID) var i DispatchByShipmentIDRow err := row.Scan( &i.ID, &i.DeliveryOrderID, &i.ShipmentID, &i.FleetID, &i.DriverID, &i.Priority, &i.QueueOrder, &i.Status, &i.DispatchedAt, ) return i, err } const fleetLatestLocations = `-- name: FleetLatestLocations :many SELECT DISTINCT ON (f.id) f.id AS fleet_id, f.plate_number, f.vehicle_name, disp.priority, e.latitude, e.longitude, e.event_time AS last_fix_at FROM dispatches disp JOIN fleet f ON f.id = disp.fleet_id JOIN shipments s ON s.id = disp.shipment_id AND s.status = 'in_transit' LEFT JOIN shipment_tracking_events e ON e.shipment_id = s.id ORDER BY f.id, e.event_time DESC, e.id DESC ` type FleetLatestLocationsRow struct { FleetID int64 `json:"fleet_id"` PlateNumber string `json:"plate_number"` VehicleName string `json:"vehicle_name"` Priority string `json:"priority"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` LastFixAt pgtype.Timestamptz `json:"last_fix_at"` } func (q *Queries) FleetLatestLocations(ctx context.Context) ([]FleetLatestLocationsRow, error) { rows, err := q.db.Query(ctx, fleetLatestLocations) if err != nil { return nil, err } defer rows.Close() var items []FleetLatestLocationsRow for rows.Next() { var i FleetLatestLocationsRow if err := rows.Scan( &i.FleetID, &i.PlateNumber, &i.VehicleName, &i.Priority, &i.Latitude, &i.Longitude, &i.LastFixAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const getDeliveryOrderByID = `-- name: GetDeliveryOrderByID :one SELECT d.id, d.do_number, d.sales_order_id, d.customer_id, d.warehouse_id, d.do_date::text AS do_date, d.priority, d.status, d.requested_quantity, d.shipped_quantity, d.delivered_quantity, d.remaining_quantity, d.notes, d.created_by, d.created_at, d.updated_at, COALESCE(c.name, '') AS customer_name, COALESCE(so.so_number, '') AS so_number, so.shipping_address, se.name AS sales_name, COALESCE(c.contact_person, c.name) AS recipient_name, d.proof_photo_url, d.recipient_name AS pod_recipient_name, (SELECT h.created_at FROM delivery_status_history h WHERE h.delivery_order_id = d.id AND h.to_status IN ('delivered','completed') ORDER BY h.id DESC LIMIT 1) AS delivered_at FROM delivery_orders d LEFT JOIN sales_orders so ON so.id = d.sales_order_id LEFT JOIN employees se ON se.id = so.sales_id LEFT JOIN customers c ON c.id = d.customer_id WHERE d.id=$1 ` type GetDeliveryOrderByIDRow struct { ID int64 `json:"id"` DoNumber string `json:"do_number"` SalesOrderID int64 `json:"sales_order_id"` CustomerID int64 `json:"customer_id"` WarehouseID int64 `json:"warehouse_id"` DoDate string `json:"do_date"` Priority string `json:"priority"` Status string `json:"status"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` CustomerName string `json:"customer_name"` SoNumber string `json:"so_number"` ShippingAddress pgtype.Text `json:"shipping_address"` SalesName pgtype.Text `json:"sales_name"` RecipientName string `json:"recipient_name"` ProofPhotoUrl pgtype.Text `json:"proof_photo_url"` PodRecipientName pgtype.Text `json:"pod_recipient_name"` DeliveredAt pgtype.Timestamptz `json:"delivered_at"` } // sales_id FKs employees(id); the salesperson name resolves there, not users. func (q *Queries) GetDeliveryOrderByID(ctx context.Context, id int64) (GetDeliveryOrderByIDRow, error) { row := q.db.QueryRow(ctx, getDeliveryOrderByID, id) var i GetDeliveryOrderByIDRow err := row.Scan( &i.ID, &i.DoNumber, &i.SalesOrderID, &i.CustomerID, &i.WarehouseID, &i.DoDate, &i.Priority, &i.Status, &i.RequestedQuantity, &i.ShippedQuantity, &i.DeliveredQuantity, &i.RemainingQuantity, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.CustomerName, &i.SoNumber, &i.ShippingAddress, &i.SalesName, &i.RecipientName, &i.ProofPhotoUrl, &i.PodRecipientName, &i.DeliveredAt, ) return i, err } const getDeliveryOrderByIDForUpdate = `-- name: GetDeliveryOrderByIDForUpdate :one SELECT d.id, d.do_number, d.sales_order_id, d.customer_id, d.warehouse_id, d.do_date::text AS do_date, d.priority, d.status, d.requested_quantity, d.shipped_quantity, d.delivered_quantity, d.remaining_quantity, d.notes, d.created_by, d.created_at, d.updated_at, COALESCE(c.name, '') AS customer_name, COALESCE(so.so_number, '') AS so_number, so.shipping_address, se.name AS sales_name, COALESCE(c.contact_person, c.name) AS recipient_name, d.proof_photo_url, d.recipient_name AS pod_recipient_name, (SELECT h.created_at FROM delivery_status_history h WHERE h.delivery_order_id = d.id AND h.to_status IN ('delivered','completed') ORDER BY h.id DESC LIMIT 1) AS delivered_at FROM delivery_orders d LEFT JOIN sales_orders so ON so.id = d.sales_order_id LEFT JOIN employees se ON se.id = so.sales_id LEFT JOIN customers c ON c.id = d.customer_id WHERE d.id=$1 FOR UPDATE OF d ` type GetDeliveryOrderByIDForUpdateRow struct { ID int64 `json:"id"` DoNumber string `json:"do_number"` SalesOrderID int64 `json:"sales_order_id"` CustomerID int64 `json:"customer_id"` WarehouseID int64 `json:"warehouse_id"` DoDate string `json:"do_date"` Priority string `json:"priority"` Status string `json:"status"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` CustomerName string `json:"customer_name"` SoNumber string `json:"so_number"` ShippingAddress pgtype.Text `json:"shipping_address"` SalesName pgtype.Text `json:"sales_name"` RecipientName string `json:"recipient_name"` ProofPhotoUrl pgtype.Text `json:"proof_photo_url"` PodRecipientName pgtype.Text `json:"pod_recipient_name"` DeliveredAt pgtype.Timestamptz `json:"delivered_at"` } // Identical column list to GetDeliveryOrderByID so the generated row structs // stay convertible; FOR UPDATE OF d locks only delivery_orders (the outer- // joined sides cannot be locked). // sales_id FKs employees(id); the salesperson name resolves there, not users. func (q *Queries) GetDeliveryOrderByIDForUpdate(ctx context.Context, id int64) (GetDeliveryOrderByIDForUpdateRow, error) { row := q.db.QueryRow(ctx, getDeliveryOrderByIDForUpdate, id) var i GetDeliveryOrderByIDForUpdateRow err := row.Scan( &i.ID, &i.DoNumber, &i.SalesOrderID, &i.CustomerID, &i.WarehouseID, &i.DoDate, &i.Priority, &i.Status, &i.RequestedQuantity, &i.ShippedQuantity, &i.DeliveredQuantity, &i.RemainingQuantity, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.CustomerName, &i.SoNumber, &i.ShippingAddress, &i.SalesName, &i.RecipientName, &i.ProofPhotoUrl, &i.PodRecipientName, &i.DeliveredAt, ) return i, err } const getDeliveryOrderItemsForUpdate = `-- name: GetDeliveryOrderItemsForUpdate :many SELECT id, delivery_order_id, product_id, requested_quantity, shipped_quantity, delivered_quantity, remaining_quantity FROM delivery_order_items WHERE delivery_order_id=$1 ORDER BY product_id FOR UPDATE ` func (q *Queries) GetDeliveryOrderItemsForUpdate(ctx context.Context, deliveryOrderID int64) ([]DeliveryOrderItem, error) { rows, err := q.db.Query(ctx, getDeliveryOrderItemsForUpdate, deliveryOrderID) if err != nil { return nil, err } defer rows.Close() var items []DeliveryOrderItem for rows.Next() { var i DeliveryOrderItem if err := rows.Scan( &i.ID, &i.DeliveryOrderID, &i.ProductID, &i.RequestedQuantity, &i.ShippedQuantity, &i.DeliveredQuantity, &i.RemainingQuantity, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const getFleetByID = `-- name: GetFleetByID :one SELECT id, plate_number, vehicle_name, vehicle_type, capacity_weight, capacity_volume, is_active, created_at, updated_at FROM fleet WHERE id=$1 ` func (q *Queries) GetFleetByID(ctx context.Context, id int64) (Fleet, error) { row := q.db.QueryRow(ctx, getFleetByID, id) var i Fleet err := row.Scan( &i.ID, &i.PlateNumber, &i.VehicleName, &i.VehicleType, &i.CapacityWeight, &i.CapacityVolume, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const getLatestDispatchByDeliveryOrder = `-- name: GetLatestDispatchByDeliveryOrder :one SELECT id, delivery_order_id, shipment_id, fleet_id, priority, queue_order, driver_id, scheduled_for, status, dispatched_at, dispatcher_id, created_at, updated_at FROM dispatches WHERE delivery_order_id=$1 ORDER BY id DESC LIMIT 1 ` func (q *Queries) GetLatestDispatchByDeliveryOrder(ctx context.Context, deliveryOrderID int64) (Dispatch, error) { row := q.db.QueryRow(ctx, getLatestDispatchByDeliveryOrder, deliveryOrderID) var i Dispatch err := row.Scan( &i.ID, &i.DeliveryOrderID, &i.ShipmentID, &i.FleetID, &i.Priority, &i.QueueOrder, &i.DriverID, &i.ScheduledFor, &i.Status, &i.DispatchedAt, &i.DispatcherID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const getSalesOrderForDelivery = `-- name: GetSalesOrderForDelivery :one SELECT id, so_number, customer_id, warehouse_id, order_date::text, status, order_type FROM sales_orders WHERE id=$1 ` type GetSalesOrderForDeliveryRow struct { ID int64 `json:"id"` SoNumber string `json:"so_number"` CustomerID int64 `json:"customer_id"` WarehouseID pgtype.Int8 `json:"warehouse_id"` OrderDate string `json:"order_date"` Status string `json:"status"` OrderType string `json:"order_type"` } func (q *Queries) GetSalesOrderForDelivery(ctx context.Context, id int64) (GetSalesOrderForDeliveryRow, error) { row := q.db.QueryRow(ctx, getSalesOrderForDelivery, id) var i GetSalesOrderForDeliveryRow err := row.Scan( &i.ID, &i.SoNumber, &i.CustomerID, &i.WarehouseID, &i.OrderDate, &i.Status, &i.OrderType, ) return i, err } const getShipmentByBarcode = `-- name: GetShipmentByBarcode :one SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE barcode=$1 ` type GetShipmentByBarcodeRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) GetShipmentByBarcode(ctx context.Context, barcode pgtype.Text) (GetShipmentByBarcodeRow, error) { row := q.db.QueryRow(ctx, getShipmentByBarcode, barcode) var i GetShipmentByBarcodeRow err := row.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ) return i, err } const getShipmentByID = `-- name: GetShipmentByID :one SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE id=$1 ` type GetShipmentByIDRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) GetShipmentByID(ctx context.Context, id int64) (GetShipmentByIDRow, error) { row := q.db.QueryRow(ctx, getShipmentByID, id) var i GetShipmentByIDRow err := row.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ) return i, err } const getShipmentByIDForUpdate = `-- name: GetShipmentByIDForUpdate :one SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE id=$1 FOR UPDATE ` type GetShipmentByIDForUpdateRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) GetShipmentByIDForUpdate(ctx context.Context, id int64) (GetShipmentByIDForUpdateRow, error) { row := q.db.QueryRow(ctx, getShipmentByIDForUpdate, id) var i GetShipmentByIDForUpdateRow err := row.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ) return i, err } const getShipmentByShipmentNumber = `-- name: GetShipmentByShipmentNumber :one SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE shipment_number=$1 ` type GetShipmentByShipmentNumberRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) GetShipmentByShipmentNumber(ctx context.Context, shipmentNumber string) (GetShipmentByShipmentNumberRow, error) { row := q.db.QueryRow(ctx, getShipmentByShipmentNumber, shipmentNumber) var i GetShipmentByShipmentNumberRow err := row.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ) return i, err } const getShipmentByTrackingNumber = `-- name: GetShipmentByTrackingNumber :one SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE tracking_number=$1 ` type GetShipmentByTrackingNumberRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) GetShipmentByTrackingNumber(ctx context.Context, trackingNumber pgtype.Text) (GetShipmentByTrackingNumberRow, error) { row := q.db.QueryRow(ctx, getShipmentByTrackingNumber, trackingNumber) var i GetShipmentByTrackingNumberRow err := row.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ) return i, err } const getShipmentItemsForUpdate = `-- name: GetShipmentItemsForUpdate :many SELECT id, shipment_id, delivery_order_item_id, product_id, shipped_quantity, delivered_quantity FROM shipment_items WHERE shipment_id=$1 ORDER BY delivery_order_item_id FOR UPDATE ` func (q *Queries) GetShipmentItemsForUpdate(ctx context.Context, shipmentID int64) ([]ShipmentItem, error) { rows, err := q.db.Query(ctx, getShipmentItemsForUpdate, shipmentID) if err != nil { return nil, err } defer rows.Close() var items []ShipmentItem for rows.Next() { var i ShipmentItem if err := rows.Scan( &i.ID, &i.ShipmentID, &i.DeliveryOrderItemID, &i.ProductID, &i.ShippedQuantity, &i.DeliveredQuantity, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const getShipmentTrackingEventByProviderRef = `-- name: GetShipmentTrackingEventByProviderRef :one SELECT id, shipment_id, event_time, status, source, latitude, longitude, accuracy_m, speed_mps, heading_deg, note, provider_ref, created_by, created_at FROM shipment_tracking_events WHERE shipment_id=$1 AND provider_ref=$2 ` type GetShipmentTrackingEventByProviderRefParams struct { ShipmentID int64 `json:"shipment_id"` ProviderRef pgtype.Text `json:"provider_ref"` } type GetShipmentTrackingEventByProviderRefRow struct { ID int64 `json:"id"` ShipmentID int64 `json:"shipment_id"` EventTime pgtype.Timestamptz `json:"event_time"` Status string `json:"status"` Source string `json:"source"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` AccuracyM decimal.NullDecimal `json:"accuracy_m"` SpeedMps decimal.NullDecimal `json:"speed_mps"` HeadingDeg decimal.NullDecimal `json:"heading_deg"` Note pgtype.Text `json:"note"` ProviderRef pgtype.Text `json:"provider_ref"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` } func (q *Queries) GetShipmentTrackingEventByProviderRef(ctx context.Context, arg GetShipmentTrackingEventByProviderRefParams) (GetShipmentTrackingEventByProviderRefRow, error) { row := q.db.QueryRow(ctx, getShipmentTrackingEventByProviderRef, arg.ShipmentID, arg.ProviderRef) var i GetShipmentTrackingEventByProviderRefRow err := row.Scan( &i.ID, &i.ShipmentID, &i.EventTime, &i.Status, &i.Source, &i.Latitude, &i.Longitude, &i.AccuracyM, &i.SpeedMps, &i.HeadingDeg, &i.Note, &i.ProviderRef, &i.CreatedBy, &i.CreatedAt, ) return i, err } const listActiveUsersForDriverPicker = `-- name: ListActiveUsersForDriverPicker :many SELECT id, name FROM users WHERE is_active = true AND application_access = 'driver' ORDER BY name ` type ListActiveUsersForDriverPickerRow struct { ID int64 `json:"id"` Name string `json:"name"` } // Scoped to dispatch: there is no "driver" role in this schema (dispatches.driver_id // just FKs users), so a "driver" is tagged via application_access='driver' // (set only by CreateDriverUser below) rather than a real RBAC role — drivers // don't log into the ERP, so they never need one. Gated behind // distribution.dispatches.create in the handler rather than system.users.view, // since a warehouse dispatcher legitimately needs this list without user-admin // rights. func (q *Queries) ListActiveUsersForDriverPicker(ctx context.Context) ([]ListActiveUsersForDriverPickerRow, error) { rows, err := q.db.Query(ctx, listActiveUsersForDriverPicker) if err != nil { return nil, err } defer rows.Close() var items []ListActiveUsersForDriverPickerRow for rows.Next() { var i ListActiveUsersForDriverPickerRow if err := rows.Scan(&i.ID, &i.Name); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listDeliveryOrderItems = `-- name: ListDeliveryOrderItems :many SELECT i.id, i.delivery_order_id, i.product_id, i.requested_quantity, i.shipped_quantity, i.delivered_quantity, i.remaining_quantity, p.code, p.name, COALESCE(soi.unit_price, p.selling_price, 0)::numeric(15,2) AS unit_price, CASE WHEN i.shipped_quantity > 0 THEN (COALESCE(soi.unit_price, p.selling_price, 0) * i.shipped_quantity)::numeric(15,2) ELSE (COALESCE(soi.unit_price, p.selling_price, 0) * i.requested_quantity)::numeric(15,2) END AS subtotal FROM delivery_order_items i JOIN delivery_orders d ON d.id = i.delivery_order_id JOIN products p ON p.id = i.product_id LEFT JOIN LATERAL ( SELECT unit_price FROM sales_order_items WHERE sales_order_id = d.sales_order_id AND product_id = i.product_id LIMIT 1 ) soi ON true WHERE i.delivery_order_id=$1 ORDER BY i.id ` type ListDeliveryOrderItemsRow struct { ID int64 `json:"id"` DeliveryOrderID int64 `json:"delivery_order_id"` ProductID int64 `json:"product_id"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Code string `json:"code"` Name string `json:"name"` UnitPrice decimal.Decimal `json:"unit_price"` Subtotal decimal.Decimal `json:"subtotal"` } func (q *Queries) ListDeliveryOrderItems(ctx context.Context, deliveryOrderID int64) ([]ListDeliveryOrderItemsRow, error) { rows, err := q.db.Query(ctx, listDeliveryOrderItems, deliveryOrderID) if err != nil { return nil, err } defer rows.Close() var items []ListDeliveryOrderItemsRow for rows.Next() { var i ListDeliveryOrderItemsRow if err := rows.Scan( &i.ID, &i.DeliveryOrderID, &i.ProductID, &i.RequestedQuantity, &i.ShippedQuantity, &i.DeliveredQuantity, &i.RemainingQuantity, &i.Code, &i.Name, &i.UnitPrice, &i.Subtotal, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listDeliveryOrders = `-- name: ListDeliveryOrders :many SELECT d.id, d.do_number, d.sales_order_id, d.customer_id, d.warehouse_id, d.do_date::text AS do_date, d.priority, d.status, d.requested_quantity, d.shipped_quantity, d.delivered_quantity, d.remaining_quantity, d.notes, d.created_by, d.created_at, d.updated_at, COALESCE(c.name, '') AS customer_name, COALESCE(so.so_number, '') AS so_number, so.shipping_address, se.name AS sales_name, COALESCE(c.contact_person, c.name) AS recipient_name, d.proof_photo_url, d.recipient_name AS pod_recipient_name, (SELECT h.created_at FROM delivery_status_history h WHERE h.delivery_order_id = d.id AND h.to_status IN ('delivered','completed') ORDER BY h.id DESC LIMIT 1) AS delivered_at FROM delivery_orders d LEFT JOIN sales_orders so ON so.id = d.sales_order_id LEFT JOIN employees se ON se.id = so.sales_id LEFT JOIN customers c ON c.id = d.customer_id WHERE ($3::text IS NULL OR d.status = $3) AND ($4::bigint IS NULL OR d.sales_order_id = $4) AND ($5::bigint IS NULL OR $5::bigint <= 0 OR d.customer_id = $5) AND ($6::text IS NULL OR d.do_number ILIKE '%' || $6 || '%') ORDER BY CASE WHEN $7::text = 'do_number' AND $8::text <> 'desc' THEN d.do_number END ASC, CASE WHEN $7::text = 'do_number' AND $8::text = 'desc' THEN d.do_number END DESC, CASE WHEN $7::text = 'do_date' AND $8::text <> 'desc' THEN d.do_date END ASC, CASE WHEN $7::text = 'do_date' AND $8::text = 'desc' THEN d.do_date END DESC, CASE WHEN $7::text = 'status' AND $8::text <> 'desc' THEN d.status END ASC, CASE WHEN $7::text = 'status' AND $8::text = 'desc' THEN d.status END DESC, CASE WHEN $7::text = 'customer_name' AND $8::text <> 'desc' THEN c.name END ASC, CASE WHEN $7::text = 'customer_name' AND $8::text = 'desc' THEN c.name END DESC, d.id DESC LIMIT $1 OFFSET $2 ` type ListDeliveryOrdersParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` Status pgtype.Text `json:"status"` SalesOrderID pgtype.Int8 `json:"sales_order_id"` CustomerID pgtype.Int8 `json:"customer_id"` Search pgtype.Text `json:"search"` SortBy pgtype.Text `json:"sort_by"` SortDir pgtype.Text `json:"sort_dir"` } type ListDeliveryOrdersRow struct { ID int64 `json:"id"` DoNumber string `json:"do_number"` SalesOrderID int64 `json:"sales_order_id"` CustomerID int64 `json:"customer_id"` WarehouseID int64 `json:"warehouse_id"` DoDate string `json:"do_date"` Priority string `json:"priority"` Status string `json:"status"` RequestedQuantity decimal.Decimal `json:"requested_quantity"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` CustomerName string `json:"customer_name"` SoNumber string `json:"so_number"` ShippingAddress pgtype.Text `json:"shipping_address"` SalesName pgtype.Text `json:"sales_name"` RecipientName string `json:"recipient_name"` ProofPhotoUrl pgtype.Text `json:"proof_photo_url"` PodRecipientName pgtype.Text `json:"pod_recipient_name"` DeliveredAt pgtype.Timestamptz `json:"delivered_at"` } // sales_id FKs employees(id); the salesperson name resolves there, not users. // ERP column sort (sort_by + sort_dir), whitelist-gated like products: the // value is compared in SQL and must never become an identifier. Each pair is // dir-gated so the un-gated ASC clause cannot dominate its DESC twin (the // products ORDER BY bug, fixed 3 Nov). func (q *Queries) ListDeliveryOrders(ctx context.Context, arg ListDeliveryOrdersParams) ([]ListDeliveryOrdersRow, error) { rows, err := q.db.Query(ctx, listDeliveryOrders, arg.Limit, arg.Offset, arg.Status, arg.SalesOrderID, arg.CustomerID, arg.Search, arg.SortBy, arg.SortDir, ) if err != nil { return nil, err } defer rows.Close() var items []ListDeliveryOrdersRow for rows.Next() { var i ListDeliveryOrdersRow if err := rows.Scan( &i.ID, &i.DoNumber, &i.SalesOrderID, &i.CustomerID, &i.WarehouseID, &i.DoDate, &i.Priority, &i.Status, &i.RequestedQuantity, &i.ShippedQuantity, &i.DeliveredQuantity, &i.RemainingQuantity, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.CustomerName, &i.SoNumber, &i.ShippingAddress, &i.SalesName, &i.RecipientName, &i.ProofPhotoUrl, &i.PodRecipientName, &i.DeliveredAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listDeliveryStatusHistory = `-- name: ListDeliveryStatusHistory :many SELECT h.id, h.delivery_order_id, h.from_status, h.to_status, h.reason, h.reference_type, h.reference_id, h.changed_by, h.created_at FROM delivery_status_history h WHERE h.delivery_order_id=$1 ORDER BY h.id DESC ` func (q *Queries) ListDeliveryStatusHistory(ctx context.Context, deliveryOrderID int64) ([]DeliveryStatusHistory, error) { rows, err := q.db.Query(ctx, listDeliveryStatusHistory, deliveryOrderID) if err != nil { return nil, err } defer rows.Close() var items []DeliveryStatusHistory for rows.Next() { var i DeliveryStatusHistory if err := rows.Scan( &i.ID, &i.DeliveryOrderID, &i.FromStatus, &i.ToStatus, &i.Reason, &i.ReferenceType, &i.ReferenceID, &i.ChangedBy, &i.CreatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listDispatchesByDeliveryOrder = `-- name: ListDispatchesByDeliveryOrder :many SELECT id, delivery_order_id, shipment_id, fleet_id, priority, queue_order, driver_id, scheduled_for, status, dispatched_at, dispatcher_id, created_at, updated_at FROM dispatches WHERE delivery_order_id=$1 ORDER BY queue_order, id ` func (q *Queries) ListDispatchesByDeliveryOrder(ctx context.Context, deliveryOrderID int64) ([]Dispatch, error) { rows, err := q.db.Query(ctx, listDispatchesByDeliveryOrder, deliveryOrderID) if err != nil { return nil, err } defer rows.Close() var items []Dispatch for rows.Next() { var i Dispatch if err := rows.Scan( &i.ID, &i.DeliveryOrderID, &i.ShipmentID, &i.FleetID, &i.Priority, &i.QueueOrder, &i.DriverID, &i.ScheduledFor, &i.Status, &i.DispatchedAt, &i.DispatcherID, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listFleet = `-- name: ListFleet :many SELECT id, plate_number, vehicle_name, vehicle_type, capacity_weight, capacity_volume, is_active, created_at, updated_at FROM fleet WHERE ($3::bool IS NULL OR is_active = $3) ORDER BY id LIMIT $1 OFFSET $2 ` type ListFleetParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` IsActive pgtype.Bool `json:"is_active"` } func (q *Queries) ListFleet(ctx context.Context, arg ListFleetParams) ([]Fleet, error) { rows, err := q.db.Query(ctx, listFleet, arg.Limit, arg.Offset, arg.IsActive) if err != nil { return nil, err } defer rows.Close() var items []Fleet for rows.Next() { var i Fleet if err := rows.Scan( &i.ID, &i.PlateNumber, &i.VehicleName, &i.VehicleType, &i.CapacityWeight, &i.CapacityVolume, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listShipmentItems = `-- name: ListShipmentItems :many SELECT si.id, si.shipment_id, si.delivery_order_item_id, si.product_id, si.shipped_quantity, si.delivered_quantity, p.code, p.name FROM shipment_items si JOIN products p ON p.id = si.product_id WHERE si.shipment_id=$1 ORDER BY si.id ` type ListShipmentItemsRow struct { ID int64 `json:"id"` ShipmentID int64 `json:"shipment_id"` DeliveryOrderItemID int64 `json:"delivery_order_item_id"` ProductID int64 `json:"product_id"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` Code string `json:"code"` Name string `json:"name"` } func (q *Queries) ListShipmentItems(ctx context.Context, shipmentID int64) ([]ListShipmentItemsRow, error) { rows, err := q.db.Query(ctx, listShipmentItems, shipmentID) if err != nil { return nil, err } defer rows.Close() var items []ListShipmentItemsRow for rows.Next() { var i ListShipmentItemsRow if err := rows.Scan( &i.ID, &i.ShipmentID, &i.DeliveryOrderItemID, &i.ProductID, &i.ShippedQuantity, &i.DeliveredQuantity, &i.Code, &i.Name, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listShipmentTrackingEvents = `-- name: ListShipmentTrackingEvents :many SELECT id, shipment_id, event_time, status, source, latitude, longitude, accuracy_m, speed_mps, heading_deg, note, provider_ref, created_by, created_at FROM shipment_tracking_events WHERE shipment_id=$1 ORDER BY event_time DESC, id DESC ` type ListShipmentTrackingEventsRow struct { ID int64 `json:"id"` ShipmentID int64 `json:"shipment_id"` EventTime pgtype.Timestamptz `json:"event_time"` Status string `json:"status"` Source string `json:"source"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` AccuracyM decimal.NullDecimal `json:"accuracy_m"` SpeedMps decimal.NullDecimal `json:"speed_mps"` HeadingDeg decimal.NullDecimal `json:"heading_deg"` Note pgtype.Text `json:"note"` ProviderRef pgtype.Text `json:"provider_ref"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` } func (q *Queries) ListShipmentTrackingEvents(ctx context.Context, shipmentID int64) ([]ListShipmentTrackingEventsRow, error) { rows, err := q.db.Query(ctx, listShipmentTrackingEvents, shipmentID) if err != nil { return nil, err } defer rows.Close() var items []ListShipmentTrackingEventsRow for rows.Next() { var i ListShipmentTrackingEventsRow if err := rows.Scan( &i.ID, &i.ShipmentID, &i.EventTime, &i.Status, &i.Source, &i.Latitude, &i.Longitude, &i.AccuracyM, &i.SpeedMps, &i.HeadingDeg, &i.Note, &i.ProviderRef, &i.CreatedBy, &i.CreatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listShipmentsByDeliveryOrder = `-- name: ListShipmentsByDeliveryOrder :many SELECT id, shipment_number, delivery_order_id, status, ship_date::text, notes, created_by, created_at, updated_at, tracking_number, barcode FROM shipments WHERE delivery_order_id=$1 ORDER BY id ` type ListShipmentsByDeliveryOrderRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` ShipDate string `json:"ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) ListShipmentsByDeliveryOrder(ctx context.Context, deliveryOrderID int64) ([]ListShipmentsByDeliveryOrderRow, error) { rows, err := q.db.Query(ctx, listShipmentsByDeliveryOrder, deliveryOrderID) if err != nil { return nil, err } defer rows.Close() var items []ListShipmentsByDeliveryOrderRow for rows.Next() { var i ListShipmentsByDeliveryOrderRow if err := rows.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.ShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listShipmentsByDeliveryOrderNumber = `-- name: ListShipmentsByDeliveryOrderNumber :many SELECT s.id, s.shipment_number, s.delivery_order_id, s.status, s.ship_date::text, s.notes, s.created_by, s.created_at, s.updated_at, s.tracking_number, s.barcode FROM shipments s JOIN delivery_orders d ON d.id = s.delivery_order_id WHERE d.do_number=$1 ORDER BY s.id ` type ListShipmentsByDeliveryOrderNumberRow struct { ID int64 `json:"id"` ShipmentNumber string `json:"shipment_number"` DeliveryOrderID int64 `json:"delivery_order_id"` Status string `json:"status"` SShipDate string `json:"s_ship_date"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` TrackingNumber pgtype.Text `json:"tracking_number"` Barcode pgtype.Text `json:"barcode"` } func (q *Queries) ListShipmentsByDeliveryOrderNumber(ctx context.Context, doNumber string) ([]ListShipmentsByDeliveryOrderNumberRow, error) { rows, err := q.db.Query(ctx, listShipmentsByDeliveryOrderNumber, doNumber) if err != nil { return nil, err } defer rows.Close() var items []ListShipmentsByDeliveryOrderNumberRow for rows.Next() { var i ListShipmentsByDeliveryOrderNumberRow if err := rows.Scan( &i.ID, &i.ShipmentNumber, &i.DeliveryOrderID, &i.Status, &i.SShipDate, &i.Notes, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.TrackingNumber, &i.Barcode, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const setDeliveryOrderStatus = `-- name: SetDeliveryOrderStatus :execrows UPDATE delivery_orders SET status=$2, updated_at=now() WHERE id=$1 ` type SetDeliveryOrderStatusParams struct { ID int64 `json:"id"` Status string `json:"status"` } func (q *Queries) SetDeliveryOrderStatus(ctx context.Context, arg SetDeliveryOrderStatusParams) (int64, error) { result, err := q.db.Exec(ctx, setDeliveryOrderStatus, arg.ID, arg.Status) if err != nil { return 0, err } return result.RowsAffected(), nil } const setShipmentStatus = `-- name: SetShipmentStatus :execrows UPDATE shipments SET status=$2, updated_at=now() WHERE id=$1 ` type SetShipmentStatusParams struct { ID int64 `json:"id"` Status string `json:"status"` } func (q *Queries) SetShipmentStatus(ctx context.Context, arg SetShipmentStatusParams) (int64, error) { result, err := q.db.Exec(ctx, setShipmentStatus, arg.ID, arg.Status) if err != nil { return 0, err } return result.RowsAffected(), nil } const shipmentCustomerID = `-- name: ShipmentCustomerID :one SELECT so.customer_id FROM shipments s JOIN delivery_orders d ON d.id = s.delivery_order_id JOIN sales_orders so ON so.id = d.sales_order_id WHERE s.id=$1 ` func (q *Queries) ShipmentCustomerID(ctx context.Context, id int64) (int64, error) { row := q.db.QueryRow(ctx, shipmentCustomerID, id) var customer_id int64 err := row.Scan(&customer_id) return customer_id, err } const shipmentLatestEvent = `-- name: ShipmentLatestEvent :one SELECT id, shipment_id, event_time, status, source, latitude, longitude, accuracy_m, speed_mps, heading_deg, note, provider_ref, created_by, created_at FROM shipment_tracking_events WHERE shipment_id=$1 ORDER BY event_time DESC, id DESC LIMIT 1 ` type ShipmentLatestEventRow struct { ID int64 `json:"id"` ShipmentID int64 `json:"shipment_id"` EventTime pgtype.Timestamptz `json:"event_time"` Status string `json:"status"` Source string `json:"source"` Latitude decimal.NullDecimal `json:"latitude"` Longitude decimal.NullDecimal `json:"longitude"` AccuracyM decimal.NullDecimal `json:"accuracy_m"` SpeedMps decimal.NullDecimal `json:"speed_mps"` HeadingDeg decimal.NullDecimal `json:"heading_deg"` Note pgtype.Text `json:"note"` ProviderRef pgtype.Text `json:"provider_ref"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` } func (q *Queries) ShipmentLatestEvent(ctx context.Context, shipmentID int64) (ShipmentLatestEventRow, error) { row := q.db.QueryRow(ctx, shipmentLatestEvent, shipmentID) var i ShipmentLatestEventRow err := row.Scan( &i.ID, &i.ShipmentID, &i.EventTime, &i.Status, &i.Source, &i.Latitude, &i.Longitude, &i.AccuracyM, &i.SpeedMps, &i.HeadingDeg, &i.Note, &i.ProviderRef, &i.CreatedBy, &i.CreatedAt, ) return i, err } const updateDeliveryOrderItemTotals = `-- name: UpdateDeliveryOrderItemTotals :execrows UPDATE delivery_order_items SET shipped_quantity=$2, delivered_quantity=$3, remaining_quantity=$4 WHERE id=$1 ` type UpdateDeliveryOrderItemTotalsParams struct { ID int64 `json:"id"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` } func (q *Queries) UpdateDeliveryOrderItemTotals(ctx context.Context, arg UpdateDeliveryOrderItemTotalsParams) (int64, error) { result, err := q.db.Exec(ctx, updateDeliveryOrderItemTotals, arg.ID, arg.ShippedQuantity, arg.DeliveredQuantity, arg.RemainingQuantity, ) if err != nil { return 0, err } return result.RowsAffected(), nil } const updateDeliveryOrderTotals = `-- name: UpdateDeliveryOrderTotals :execrows UPDATE delivery_orders SET shipped_quantity=$2, delivered_quantity=$3, remaining_quantity=$4, status=$5, updated_at=now() WHERE id=$1 ` type UpdateDeliveryOrderTotalsParams struct { ID int64 `json:"id"` ShippedQuantity decimal.Decimal `json:"shipped_quantity"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` RemainingQuantity decimal.Decimal `json:"remaining_quantity"` Status string `json:"status"` } func (q *Queries) UpdateDeliveryOrderTotals(ctx context.Context, arg UpdateDeliveryOrderTotalsParams) (int64, error) { result, err := q.db.Exec(ctx, updateDeliveryOrderTotals, arg.ID, arg.ShippedQuantity, arg.DeliveredQuantity, arg.RemainingQuantity, arg.Status, ) if err != nil { return 0, err } return result.RowsAffected(), nil } const updateShipmentItemDelivered = `-- name: UpdateShipmentItemDelivered :execrows UPDATE shipment_items SET delivered_quantity=$2 WHERE id=$1 ` type UpdateShipmentItemDeliveredParams struct { ID int64 `json:"id"` DeliveredQuantity decimal.Decimal `json:"delivered_quantity"` } func (q *Queries) UpdateShipmentItemDelivered(ctx context.Context, arg UpdateShipmentItemDeliveredParams) (int64, error) { result, err := q.db.Exec(ctx, updateShipmentItemDelivered, arg.ID, arg.DeliveredQuantity) if err != nil { return 0, err } return result.RowsAffected(), nil }