format( 'Y' ); $month = (int) $when->format( 'n' ); $start = $month >= 4 ? $year : $year - 1; return substr( (string) $start, 2 ) . '-' . substr( (string) ( $start + 1 ), 2 ); } /** * Format a register datetime (stored in the site timezone) for display without a timezone shift. * * @param string $mysql_datetime Y-m-d H:i:s as stored in the documents table. * @param string $format PHP date format; defaults to the site date format. * @return string */ public static function format_date( $mysql_datetime, $format = '' ) { $dt = date_create_immutable( (string) $mysql_datetime, wp_timezone() ); if ( ! $dt ) { return ''; } return wp_date( '' !== $format ? $format : (string) get_option( 'date_format' ), $dt->getTimestamp() ); } /** * Fetch the document of a type for an order. * * @param int $order_id Order ID. * @param string $type Document type. * @return array|null */ public function get_by_order( $order_id, $type = self::TYPE_INVOICE ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE order_id = %d AND doc_type = %s ORDER BY id ASC LIMIT 1", (int) $order_id, $type ), ARRAY_A ); return $row ? $row : null; } /** * Fetch a document by ID. * * @param int $id Document ID. * @return array|null */ public function get( $id ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE id = %d", (int) $id ), ARRAY_A ); return $row ? $row : null; } /** * Fetch the document of a type created from a source object (e.g. a refund). * * @param int $source_id Source ID (refund ID for credit notes). * @param string $type Document type. * @return array|null */ public function get_by_source( $source_id, $type = self::TYPE_CREDIT_NOTE ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE source_id = %d AND doc_type = %s ORDER BY id ASC LIMIT 1", (int) $source_id, $type ), ARRAY_A ); return $row ? $row : null; } /** * All documents of an order (invoice and credit notes), oldest first. * * @param int $order_id Order ID. * @param string $type Document type or '' for all. * @return array[] */ public function list_by_order( $order_id, $type = '' ) { global $wpdb; if ( '' !== $type ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE order_id = %d AND doc_type = %s ORDER BY id ASC", (int) $order_id, $type ), ARRAY_A ); } else { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE order_id = %d ORDER BY id ASC", (int) $order_id ), ARRAY_A ); } return $rows ? $rows : array(); } /** * Resolve {FY}, {YYYY}, {YY} placeholders in a number prefix. * * @param string $prefix Prefix with placeholders. * @param \DateTimeInterface|null $when Point in time (defaults to now). * @return string */ public static function resolve_prefix( $prefix, $when = null ) { if ( ! $when instanceof \DateTimeInterface ) { $when = current_datetime(); } return str_replace( array( '{FY}', '{YYYY}', '{YY}' ), array( self::fy_label( $when ), $when->format( 'Y' ), $when->format( 'y' ) ), (string) $prefix ); } /** * Issue a tax invoice for an order. Idempotent: returns the existing one if present. * * @param \WC_Order $order Order. * @param array $summary Totals and buyer data from Order_GST::compute(). * @param Settings $settings Settings. * @return array|\WP_Error Document row. */ public function issue_invoice( \WC_Order $order, array $summary, Settings $settings ) { $existing = $this->get_by_order( $order->get_id(), self::TYPE_INVOICE ); if ( $existing ) { return $existing; } $now = current_datetime(); $fy = $settings->is_on( 'invoice_fy_reset' ) ? self::fy_label( $now ) : ''; $padding = max( 1, min( 8, (int) $settings->get( 'invoice_padding', 4 ) ) ); $prefix = self::resolve_prefix( (string) $settings->get( 'invoice_prefix', 'INV/' ), $now ); $totals = isset( $summary['totals'] ) ? $summary['totals'] : array(); $row = array( 'order_id' => $order->get_id(), 'doc_type' => self::TYPE_INVOICE, 'fy' => $fy, 'issued_at' => $now->format( 'Y-m-d H:i:s' ), 'buyer_gstin' => ! empty( $summary['buyer_gstin'] ) ? $summary['buyer_gstin'] : null, 'pos_code' => isset( $summary['pos_code'] ) ? (string) $summary['pos_code'] : '', 'supply_type' => isset( $summary['supply_type'] ) ? (string) $summary['supply_type'] : '', 'taxable' => isset( $totals['taxable'] ) ? (float) $totals['taxable'] : 0, 'cgst' => isset( $totals['cgst'] ) ? (float) $totals['cgst'] : 0, 'sgst' => isset( $totals['sgst'] ) ? (float) $totals['sgst'] : 0, 'igst' => isset( $totals['igst'] ) ? (float) $totals['igst'] : 0, 'cess' => isset( $totals['cess'] ) ? (float) $totals['cess'] : 0, 'total' => (float) $order->get_total(), 'ref_doc_id' => null, 'source_id' => null, 'created_at' => $now->format( 'Y-m-d H:i:s' ), ); return $this->insert_with_sequence( $row, $prefix, $padding ); } /** * Issue any document type (used by GSTLayer Pro for credit notes). Idempotent per source. * * @param array $row Row with order_id, doc_type, buyer_gstin, pos_code, supply_type, totals, ref_doc_id, source_id. * @param string $prefix Prefix with placeholders. * @param int $padding Sequence padding. * @param bool $fy_reset Whether sequences restart every financial year. * @return array|\WP_Error */ public function issue( array $row, $prefix, $padding, $fy_reset = true ) { if ( ! empty( $row['source_id'] ) && ! empty( $row['doc_type'] ) ) { $existing = $this->get_by_source( (int) $row['source_id'], (string) $row['doc_type'] ); if ( $existing ) { return $existing; } } $now = current_datetime(); $defaults = array( 'order_id' => 0, 'doc_type' => self::TYPE_CREDIT_NOTE, 'fy' => $fy_reset ? self::fy_label( $now ) : '', 'issued_at' => $now->format( 'Y-m-d H:i:s' ), 'buyer_gstin' => null, 'pos_code' => '', 'supply_type' => '', 'taxable' => 0, 'cgst' => 0, 'sgst' => 0, 'igst' => 0, 'cess' => 0, 'total' => 0, 'ref_doc_id' => null, 'source_id' => null, 'created_at' => $now->format( 'Y-m-d H:i:s' ), ); $row = array_intersect_key( array_merge( $defaults, $row ), $defaults ); $padding = max( 1, min( 8, (int) $padding ) ); return $this->insert_with_sequence( $row, self::resolve_prefix( $prefix, $now ), $padding ); } /** * Insert a row with the next sequence number. Retries on unique-key collisions, * which makes it safe under concurrent checkouts on MySQL and SQLite alike. * * @param array $row Row without seq/number. * @param string $prefix Resolved prefix. * @param int $padding Sequence padding. * @return array|\WP_Error */ public function insert_with_sequence( array $row, $prefix, $padding ) { global $wpdb; $table = $this->table(); for ( $attempt = 0; $attempt < 6; $attempt++ ) { $max = (int) $wpdb->get_var( $wpdb->prepare( "SELECT MAX(seq) FROM {$wpdb->prefix}gstlayer_documents WHERE fy = %s AND doc_type = %s", $row['fy'], $row['doc_type'] ) ); $seq = $max + 1; $row['seq'] = $seq; $row['number'] = $prefix . str_pad( (string) $seq, $padding, '0', STR_PAD_LEFT ); $formats = array(); foreach ( $row as $key => $value ) { if ( in_array( $key, array( 'order_id', 'seq', 'ref_doc_id', 'source_id' ), true ) ) { $formats[] = '%d'; } elseif ( in_array( $key, array( 'taxable', 'cgst', 'sgst', 'igst', 'cess', 'total' ), true ) ) { $formats[] = '%f'; } else { $formats[] = '%s'; } } $suppress = $wpdb->suppress_errors( true ); $inserted = $wpdb->insert( $table, $row, $formats ); $wpdb->suppress_errors( $suppress ); if ( false !== $inserted ) { $row['id'] = (int) $wpdb->insert_id; return $row; } $error = strtolower( (string) $wpdb->last_error ); if ( false === strpos( $error, 'duplicate' ) && false === strpos( $error, 'unique' ) ) { return new \WP_Error( 'gstlayer_insert_failed', $wpdb->last_error ? $wpdb->last_error : __( 'Could not write to the invoice register.', 'gstlayer' ) ); } } return new \WP_Error( 'gstlayer_sequence_busy', __( 'Could not allocate an invoice number after several attempts. Please try again.', 'gstlayer' ) ); } /** * Documents issued between two datetimes (site timezone), oldest first. * * @param string $from Y-m-d H:i:s. * @param string $to Y-m-d H:i:s. * @param string $type Document type or '' for all. * @param int $limit Max rows. * @return array[] */ public function list_between( $from, $to, $type = '', $limit = 5000 ) { global $wpdb; if ( '' !== $type ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE issued_at >= %s AND issued_at <= %s AND doc_type = %s ORDER BY issued_at ASC, id ASC LIMIT %d", $from, $to, $type, (int) $limit ), ARRAY_A ); } else { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}gstlayer_documents WHERE issued_at >= %s AND issued_at <= %s ORDER BY issued_at ASC, id ASC LIMIT %d", $from, $to, (int) $limit ), ARRAY_A ); } return $rows ? $rows : array(); } }