Transactions
Generated schema file.
Select
The 本製品 will use the Smaregi API to process WHERE clause conditions and selected columns. Operators: =, >=, <=, AND, ORDER BY are processed server-side. All other operators are processed client-side.
NOTE: One or more of the following filtering conditions are required in the request. The Smaregi API restricts most date/datetime range filters to a 31-day window, and the TransactionHeadId range to 100,000 IDs.
- Transaction Head ID [TransactionHeadId] — supports =, From-To (range within 100,000).
- Transaction Date Time [TransactionDateTime] — From-To (period within 31 days).
- Terminal Transaction Date Time [TerminalTranDateTime] — From-To (period within 31 days).
- Adjustment Date Time [AdjustmentDateTime] — supports = only.
- Sum Date [SumDate] — supports =, From-To (period within 31 days).
- Updated Date Time [UpdDateTime] — From-To (period within 31 days).
The following columns additionally support equality (=) filtering on SELECT: Barcode, CustomerCode, StoreId, TransactionUuid.
For example, the following queries are processed server-side:
SELECT * FROM Transactions WHERE TransactionHeadId >= 1 AND TransactionHeadId <= 100000 SELECT * FROM Transactions WHERE TransactionHeadId = 1375 SELECT * FROM Transactions WHERE TransactionDateTime >= '2024-03-01 00:00:00' AND TransactionDateTime <= '2024-03-31 00:00:00' SELECT * FROM Transactions WHERE TerminalTranDateTime >= '2024-03-01 00:00:00' AND TerminalTranDateTime <= '2024-03-31 00:00:00' SELECT * FROM Transactions WHERE SumDate >= '2024-03-01' AND SumDate <= '2024-03-31' SELECT * FROM Transactions WHERE UpdDateTime >= '2024-03-01 00:00:00' AND UpdDateTime <= '2024-03-31 00:00:00' SELECT * FROM Transactions WHERE AdjustmentDateTime = '2024-03-15 12:00:00' SELECT * FROM Transactions WHERE StoreId = 1 AND SumDate >= '2024-03-01' AND SumDate <= '2024-03-31'
Retrieving Related Resources with Pseudo-Columns
The Transactions table exposes a set of input-only pseudo-columns that map to the corresponding with_* query parameters on the Smaregi GET /transactions endpoint. Use them in the WHERE clause to include related resources inline with each transaction, avoiding per-row lookups against the child tables.
The following pseudo-columns are supported:
- WithCustomer — Includes customer information on each transaction (maps to with_customer).
- WithCustomerGroups — Includes customer group information (maps to with_customer_groups).
- WithDiscounts — Includes discount information (maps to with_discounts).
- WithStore — Includes store information (maps to with_store).
- WithStaff — Includes staff information (maps to with_staff).
- WithRecordedStaff — Includes recorded staff information (maps to with_recorded_staff).
- WithLayway — Includes layaway information (maps to with_layaway).
- WithLayways — Includes all layaways information (maps to with_layaways).
- WithLaywayPickUp — Includes layaway pick-up information (maps to with_layaway_pick_ups).
- WithMoneyControl — Includes money control information (maps to with_money_control).
- WithDepositOthers — Includes deposit-others (mixed payment) information (maps to with_deposit_others). Accepted values: all, none (default).
NOTE: Pseudo-columns are input-only — they do not appear as columns in the result set. Refer to the Smaregi GET /transactions API reference for the full list of accepted values per parameter.
For example, the following query retrieves transactions together with their deposit-others details in a single request, instead of joining against the TransactionDepositOthers child table:
SELECT * FROM Transactions WHERE WithDepositOthers = 'all' AND SumDate >= '2024-03-01' AND SumDate <= '2024-03-31'
Insert
To add a Transaction, specify the StoreId, TerminalId, TerminalTranId, TerminalTranDateTime, TransactionHeadDivision, Subtotal, Total, and Details fields.
Using temporary tables:
INSERT INTO TransactionDetails#TEMP (ProductId, TransactionDetailId, TransactionDetailDivision, SalesPrice, Quantity) VALUES (1, 1, 1, 1300, 4); INSERT INTO TransactionDetails#TEMP (ProductId, TransactionDetailId, TransactionDetailDivision, SalesPrice, Quantity) VALUES (2, 2, 1, 500, 10); INSERT INTO Coupons#TEMP (TerminalTranCouponId, CouponId, DiscountPrice) VALUES (1, 3, 100); INSERT INTO Transactions (SumDivision, StoreId, TerminalId, TerminalTranId, CancelDivision, TerminalTranDateTime, SumDate, TransactionHeadDivision, Subtotal, Total, Details, Coupons) VALUES (2, 1, 22, 6, 0, '2025/05/10', '2025/05/10', '1', 10200, 9600, 'TransactionDetails#TEMP', 'Coupons#TEMP');
Update
To update a Transaction, specify the TransactionHeadId in the WHERE clause. Only a subset of columns (for example, Memo, ReceiptMemo, StaffId, GuestNumbers, Tags, and CustomerGroupId) are updatable.
UPDATE Transactions SET Memo = 'updated memo' WHERE TransactionHeadId = 1375
Delete
Deleting a Transaction cancels the transaction at the Smaregi API (HTTP POST to /transactions/{TransactionHeadId}/cancel). Specify the TransactionHeadId in the WHERE clause.
DELETE FROM Transactions WHERE TransactionHeadId = 1375
Columns
| Name | Type | ReadOnly | References | Description |
| TransactionHeadId [KEY] | Integer | True | ||
| AdjustmentDateTime | Datetime | True | ||
| AdjustmentMile | Integer | False | ||
| AdjustmentMileDivision | String | False | ||
| AdjustmentMileValue | Integer | False | ||
| Amount | Integer | True | ||
| AuthDate | Date | True | ||
| AuthNumber | String | True | ||
| Barcode | String | False | ||
| CancelDateTime | Datetime | True | ||
| CancelDivision | String | False | ||
| CancelSlipNumber | String | True | ||
| CardCompany | String | True | ||
| Carriage | Integer | False | ||
| CashTotal | Integer | True | ||
| Change | Integer | False | ||
| ChangeDifference | Integer | True | ||
| Commission | Integer | False | ||
| CostTotal | Decimal | True | ||
| CouponDiscount | String | True | ||
| CreditDivision | String | True | ||
| CreditTotal | String | True | ||
| CurrentMile | Integer | False | ||
| Customer | String | True | ||
| CustomerCode | String | False | ||
| CustomerGroupId | Integer | False | ||
| CustomerGroupId2 | Integer | False | ||
| CustomerGroupId3 | Integer | False | ||
| CustomerGroupId4 | Integer | False | ||
| CustomerGroupId5 | Integer | False | ||
| CustomerGroups | String | True | ||
| CustomerId | Long | False | ||
| CustomerPinCode | String | False | ||
| CustomerRank | String | False | ||
| Denomination | String | True | ||
| Deposit | Integer | False | ||
| DepositCash | Integer | False | ||
| DepositCredit | Integer | False | ||
| DiscountRoundingDivision | String | False | ||
| Discounts | String | True | ||
| DisposeDivision | String | True | ||
| DisposeServerTransactionHeadId | Integer | True | ||
| EarnMile | Integer | False | ||
| EnterDateTime | Datetime | False | ||
| ExchangeTicketNo | String | False | ||
| GiftReceiptValidDays | Integer | False | ||
| GuestNumbers | Integer | False | ||
| GuestNumbersFemale | Integer | False | ||
| GuestNumbersMale | Integer | False | ||
| GuestNumbersUnknown | Integer | False | ||
| InTaxSalesTotal | Integer | True | ||
| Layaway | String | True | ||
| Layaways | String | True | ||
| LayawayPickUp | String | True | ||
| Memo | String | False | ||
| MileageDivision | String | False | ||
| MileageLabel | String | False | ||
| MoneyControls | String | True | ||
| NetTaxFreeConsumableTaxExclude | Integer | False | ||
| NetTaxFreeConsumableTaxInclude | Integer | False | ||
| NetTaxFreeGeneralTaxExclude | Integer | False | ||
| NetTaxFreeGeneralTaxInclude | Integer | False | ||
| NewPoint | Integer | False | ||
| NonSalesTargetAmount | Integer | True | ||
| NonSalesTargetCostTotal | Decimal | True | ||
| NonSalesTargetInTaxTotal | Integer | True | ||
| NonSalesTargetOutTaxTotal | Integer | True | ||
| NonSalesTargetReturnAmount | Integer | True | ||
| NonSalesTargetTaxFreeTotal | Integer | True | ||
| NonSalesTargetTotal | Integer | True | ||
| NonTaxSalesTotal | Integer | True | ||
| OutTaxSalesTotal | Integer | True | ||
| PaymentCount | Integer | True | ||
| Point | Integer | False | ||
| PointDiscount | Integer | False | ||
| PointGivingDivision | String | False | ||
| PointGivingUnit | Decimal | False | ||
| PointGivingUnitPrice | Integer | False | ||
| PointSpendDivision | String | False | ||
| ReceiptMemo | String | False | ||
| RecordedStaffs | String | True | ||
| ReturnAmount | String | True | ||
| ReturnSales | String | True | ||
| RoundingDivision | String | False | ||
| RoundingPrice | Integer | False | ||
| SalesHeadDivision | String | True | ||
| SellDivision | String | False | ||
| SlipNumber | String | True | ||
| SpendPoint | Integer | False | ||
| Staff | String | True | ||
| StaffCode | String | True | ||
| StaffId | Integer | False | ||
| StaffName | String | True | ||
| Store | String | True | ||
| StoreCode | String | True | ||
| StoreId | Integer | False | ||
| Subtotal | Integer | False | ||
| SubtotalDiscountDivision | Integer | False | ||
| SubtotalDiscountPrice | Integer | False | ||
| SubtotalDiscountRate | Integer | False | ||
| SubtotalForDiscount | String | True | ||
| SumDate | Date | False | ||
| SumDivision | String | False | ||
| Tags | String | False | ||
| TaxExclude | Integer | False | ||
| TaxFreeSalesDivision | String | False | ||
| TaxInclude | Integer | False | ||
| TaxRate | Decimal | False | ||
| TaxRounding | String | False | ||
| TerminalId | Integer | False | ||
| TerminalTranDateTime | Datetime | False | ||
| TerminalTranId | Integer | False | ||
| TipCash | Integer | True | ||
| TipCredit | Integer | True | ||
| Total | Integer | False | ||
| TotalMile | Integer | False | ||
| TotalPoint | Integer | False | ||
| TransactionDateTime | Datetime | True | ||
| TransactionHeadDivision | String | False | ||
| TransactionUuid | String | False | ||
| UnitBargainDiscountsubtotal | Integer | True | ||
| UnitDiscountsubtotal | Integer | True | ||
| UnitNonDiscountsubtotal | Integer | True | ||
| UnitStaffDiscountsubtotal | Integer | True | ||
| UpdDateTime | Datetime | True | ||
| Details | String | False | ||
| DepositOthers | String | False | ||
| Coupons | String | False | ||
| CouponItems | String | False | ||
| LayawayPickUps | String | True |
Pseudo-Columns
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるデータを詳細に制御することができます。
| Name | Type | Description |
| WithCustomer | String | |
| WithCustomerGroups | String | |
| WithDiscounts | String | |
| WithStore | String | |
| WithStaff | String | |
| WithRecordedStaff | String | |
| WithLayway | String | |
| WithLayways | String | |
| WithLaywayPickUp | String | |
| WithMoneyControl | String | |
| WithDepositOthers | String |
使用できる値は次のとおりです。all, none |