Data Structures

12076

Table Overview

General Tables

Business Tables

_ui

UI Construction Plan

CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255)  DEFAULT NULL COMMENT 'UI action type, e.g.: fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT 'description',
  `uiActionConfig` text  COMMENT 'UI action data',
  `appDataSchema` text  COMMENT 'UI validation data',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'UI construction plan'

customer

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'business auto-increment ID, 1001++',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID; KH1001',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer name',
  `customerAccountNum` varchar(255)  DEFAULT NULL COMMENT 'bank account number',
  `customerBankName` varchar(255)  DEFAULT NULL COMMENT 'bank name',
  `customerBranchBankName` varchar(255)  DEFAULT NULL COMMENT 'branch bank name',
  `customerAddress` varchar(255)  DEFAULT NULL COMMENT 'customer address',
  `customerPostalCode` varchar(255)  DEFAULT NULL COMMENT 'postal code',
  `customerContactPerson` varchar(255)  DEFAULT NULL COMMENT 'contact person',
  `customerPhoneNum` varchar(255)  DEFAULT NULL COMMENT 'contact phone number',
  `customerCollectionCycle` varchar(255)  DEFAULT NULL COMMENT 'collection period; unit: days',
  `customerBusinessLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'business license',
  `customerMedicalOperatingLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'medical device operating license',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

finance_asset

Finance - Asset Information Table

CREATE TABLE `finance_asset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetId` varchar(255)  DEFAULT NULL COMMENT 'asset ID; ZC10001',
  `assetName` varchar(255)  DEFAULT NULL COMMENT 'asset;',
  `assetSpec` varchar(255)  DEFAULT NULL COMMENT 'asset specification',
  `currentPeriod` varchar(255)  DEFAULT NULL COMMENT 'current accounting period; 2022-02',
  `entryPeriod` varchar(255)  DEFAULT NULL COMMENT 'entry period; 2022-02',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `financeClearSubject` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject; fixed asset clearance',
  `financeDepreciationFeeSubject` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject; management expense - depreciation',
  `startUseAt` varchar(255)  DEFAULT NULL COMMENT 'start use date; 2020-02-02',
  `assetValue` decimal(10, 2) DEFAULT NULL COMMENT 'original asset value',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `estimatedUseMonth` int(11) DEFAULT NULL COMMENT 'estimated use months',
  `depreciatedMonths` int(11) DEFAULT NULL COMMENT 'depreciated months',
  `remainingMonth` int(11) DEFAULT NULL COMMENT 'remaining use months',
  `monthlyDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'monthly depreciation amount',
  `totalDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'accumulated depreciation',
  `currentYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current year accumulated depreciation;',
  `preYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'previous year accumulated depreciation',
  `currentPeriodDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current period depreciation',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `financeClearSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject ID;',
  `financeDepreciationFeeSubjectId` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject ID;',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `deptId` varchar(255)  DEFAULT NULL COMMENT 'department ID;',
  `deptName` varchar(255)  DEFAULT NULL COMMENT 'department name;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Table'

finance_asset_record

Finance - Asset Information Detail Table

CREATE TABLE `finance_asset_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetId` varchar(255)  DEFAULT NULL COMMENT 'asset ID; ZC10001',
  `assetName` varchar(255)  DEFAULT NULL COMMENT 'asset;',
  `assetSpec` varchar(255)  DEFAULT NULL COMMENT 'asset specification',
  `assetOperationType` varchar(255)  DEFAULT 'depreciation' COMMENT 'operation type',
  `currentPeriod` varchar(255)  DEFAULT NULL COMMENT 'current accounting period; 2022-02',
  `entryPeriod` varchar(255)  DEFAULT NULL COMMENT 'entry period; 2022-02',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `financeClearSubject` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject; fixed asset clearance',
  `financeDepreciationFeeSubject` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject; management expense - depreciation',
  `startUseAt` varchar(255)  DEFAULT NULL COMMENT 'start use date; 2020-02-02',
  `assetValue` decimal(10, 2) DEFAULT NULL COMMENT 'original asset value',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `estimatedUseMonth` int(11) DEFAULT NULL COMMENT 'estimated use months',
  `depreciatedMonths` int(11) DEFAULT NULL COMMENT 'depreciated months',
  `remainingMonth` int(11) DEFAULT NULL COMMENT 'remaining use months',
  `monthlyDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'monthly depreciation amount',
  `totalDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'initial accumulated depreciation',
  `currentYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current year accumulated depreciation',
  `preYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'previous year accumulated depreciation',
  `currentPeriodDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current period depreciation',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `financeClearSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject ID;',
  `financeDepreciationFeeSubjectId` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject ID;',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `deptId` varchar(255)  DEFAULT NULL COMMENT 'department ID;',
  `deptName` varchar(255)  DEFAULT NULL COMMENT 'department name;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Detail Table'

finance_asset_type

Finance - Asset Information Table

CREATE TABLE `finance_asset_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Table'

finance_period

Depreciation - Accounting Period Table

CREATE TABLE `finance_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `periodId` varchar(255)  DEFAULT NULL COMMENT 'accounting period; month dimension E.g: 2023-03',
  `financeYear` varchar(255)  DEFAULT NULL COMMENT 'fiscal year;',
  `isDepreciation` varchar(255)  DEFAULT NULL COMMENT 'is depreciation; depreciated, pending depreciation',
  `isPeriodStart` varchar(255)  DEFAULT 'no' COMMENT 'is initial accounting period; yes, no',
  `depreciationVoucherId` varchar(255)  DEFAULT NULL COMMENT 'depreciation voucher ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `periodId_unique` (`periodId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Depreciation - Accounting Period Table'

payment

Receipts & Payments Table;

CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID',
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `paymentId` varchar(255)  DEFAULT NULL COMMENT 'receipt/payment ID; SK10001 FK10001',
  `paymentType` varchar(20)  DEFAULT NULL COMMENT 'type; receipt, payment',
  `paymentAmount` decimal(10, 2) DEFAULT NULL COMMENT 'amount',
  `paymentPayee` varchar(50)  DEFAULT NULL COMMENT 'receipt account; person, company, account number',
  `paymentPayeeAccount` varchar(255)  DEFAULT NULL COMMENT 'receipt account number; ',
  `paymentPayeeBank` varchar(255)  DEFAULT NULL COMMENT 'receipt account bank',
  `paymentPayeeBranchBank` varchar(255)  DEFAULT NULL COMMENT 'receipt account branch bank',
  `paymentPayer` varchar(50)  DEFAULT NULL COMMENT 'payment account; person, company, account number',
  `paymentPayerAccount` varchar(255)  DEFAULT NULL COMMENT 'payment account number;',
  `paymentPayerBank` varchar(255)  DEFAULT NULL COMMENT 'payment account bank',
  `paymentPayerBranchBank` varchar(255)  DEFAULT NULL COMMENT 'payment account branch bank',
  `paymentMethod` varchar(50)  DEFAULT NULL COMMENT 'method',
  `paymentStatus` varchar(20)  NOT NULL DEFAULT 'unpaid' COMMENT 'payment status; unpaid, paid',
  `invoiceStatus` varchar(255)  DEFAULT 'unbilled' COMMENT 'billing status; unbilled, billed',
  `paymentAt` varchar(255)  DEFAULT NULL COMMENT 'time; E.g: 2021-05-28T10:24:54+08:00',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID;',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID;',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 45 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Receipts & Payments Table;'

payment_order

Receipts & Payments Table - Order Deduction Table

CREATE TABLE `payment_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID',
  `paymentId` varchar(255)  DEFAULT NULL COMMENT 'receipt/payment ID; SK10001 FK10001',
  `paymentType` varchar(20)  DEFAULT NULL COMMENT 'type; receipt, payment',
  `orderType` varchar(20)  DEFAULT NULL COMMENT 'order type; purchase order, sales order, purchase return order, sales return order',
  `orderId` varchar(255)  NOT NULL COMMENT 'order ID',
  `deductionAmount` decimal(10, 2) NOT NULL COMMENT 'deduction amount',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 45 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Receipts & Payments Table - Order Deduction Table'

product_price_record

CREATE TABLE `product_price_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer name',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `saleProductSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'sales tax-inclusive unit price/yuan',
  `productSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

purchase_order

Purchase Order Table

CREATE TABLE `purchase_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `purchaseOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID; CG10001',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier',
  `supplierInfo` mediumtext  COMMENT 'supplier information; json',
  `purchaseOrderType` varchar(255)  DEFAULT 'regular purchase' COMMENT 'purchase order type; regular purchase, asset purchase',
  `purchaseOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'purchase order description;',
  `purchaseProductList` text  COMMENT 'purchase product list; [{}, {}]',
  `purchaseProductListCount` int(11) DEFAULT NULL COMMENT 'number of purchase products',
  `purchaseProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of purchase products',
  `purchaseTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `purchaseOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending purchase, purchasing, order completed/order canceled',
  `purchaseOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order cancellation',
  `purchaseCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `purchaseFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `purchaseOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 122 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Purchase Order Table'

purchase_price_history

CREATE TABLE `purchase_price_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchaseOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID',
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `productSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan; purchase price',
  `purchaseProductTaxRate` int(11) DEFAULT NULL COMMENT 'tax rate;',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

purchase_return_order

Purchase Return Order Table

CREATE TABLE `purchase_return_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `returnOrderId` varchar(255)  DEFAULT NULL COMMENT 'refund order ID; XSTK10001',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier',
  `supplierInfo` mediumtext  COMMENT 'supplier information; json',
  `returnOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'return order description;',
  `returnProductList` text  COMMENT 'return product list; [{}, {}]',
  `returnProductListCount` int(11) DEFAULT NULL COMMENT 'number of return products',
  `returnProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of return products',
  `returnTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `returnOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending return, returning, order completed/order canceled',
  `returnOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order cancellation',
  `returnCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `returnFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `returnOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `returnOrderType` varchar(255)  DEFAULT NULL COMMENT 'order type; regular return, asset return',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Purchase Return Order Table'

sale_order

CREATE TABLE `sale_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'primary key ID, 10001++',
  `saleOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID; CG10001',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer',
  `customerInfo` text  COMMENT 'customer',
  `saleOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'sales order description;',
  `saleProductList` text  COMMENT 'sales product list; [{}, {}]',
  `saleProductListCount` int(11) DEFAULT NULL COMMENT 'number of sales products',
  `saleProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of sales products',
  `saleTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `saleOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending sale, selling, order completed/order canceled',
  `saleOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order failure',
  `saleCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `saleOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `saleFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

sale_price_history

CREATE TABLE `sale_price_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `saleOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID',
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `saleProductSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan; sales price',
  `productTaxRate` int(11) DEFAULT NULL COMMENT 'tax rate;',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

sale_return_order

Sales Return Order Table

CREATE TABLE `sale_return_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'primary key ID, 10001++',
  `returnOrderId` varchar(255)  DEFAULT NULL COMMENT 'refund order ID; CGTK10001',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer',
  `customerInfo` text  COMMENT 'customer',
  `returnOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'return order description;',
  `returnProductList` text  COMMENT 'return product list; [{}, {}]',
  `returnProductListCount` int(11) DEFAULT NULL COMMENT 'number of return products',
  `returnProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of return products',
  `returnTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `returnOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending return, returning, order completed/order canceled',
  `returnOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order failure',
  `returnCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `returnOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `returnFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Sales Return Order Table'

subject

Subject Table

CREATE TABLE `subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appaId` varchar(255)  DEFAULT NULL COMMENT 'account set ID',
  `subjectId` varchar(255)  DEFAULT NULL COMMENT 'subject code; asset class 1001++, liability class 2001++..',
  `subjectName` varchar(255)  DEFAULT NULL COMMENT 'subject name;',
  `subjectLabel` varchar(255)  DEFAULT NULL COMMENT 'subject label;',
  `subjectCategory` varchar(255)  DEFAULT NULL COMMENT 'subject category; asset, liability, equity, cost, profit and loss',
  `subjectLevel` int(11) DEFAULT '1' COMMENT 'subject level; level 1 subject, level 2 subject….',
  `subjectHasChildren` varchar(255)  DEFAULT 'no sub-level subject' COMMENT 'subject type; has sub-level subject, no sub-level subject',
  `subjectBalanceDirection` varchar(4)  DEFAULT NULL COMMENT 'subject balance direction; debit, credit',
  `assistList` varchar(255)  DEFAULT NULL COMMENT 'auxiliary accounting configuration list; concatenated with “,” E.g: customer, supplier, staff, project, department, extension 1, extension 2 ',
  `isShown` varchar(255)  DEFAULT 'shown' COMMENT 'is displayed; shown, hidden',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `appaId_subject_unique` (`appaId`, `subjectId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4840 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Subject Table'

subject_balance_period

Subject Balance Table - Accounting Period Dimension

CREATE TABLE `subject_balance_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appaId` varchar(255)  DEFAULT NULL COMMENT 'account set ID',
  `periodId` varchar(255)  DEFAULT NULL COMMENT 'accounting period; E.g: 2023-03',
  `financeYear` varchar(255)  DEFAULT NULL COMMENT 'fiscal year;',
  `subjectId` varchar(255)  DEFAULT NULL COMMENT 'subject code;',
  `startDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'initial balance - debit',
  `startCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'initial balance - credit',
  `occurDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period occurrence - debit',
  `occurCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period occurrence - credit',
  `occurAmount` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period balance/current actual profit and loss occurrence',
  `endDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'ending balance - debit',
  `endCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'ending balance - credit',
  `isPeriodStart` varchar(255)  DEFAULT 'no' COMMENT 'is initial accounting period; yes, no',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `appaId_periodId_subjectId_unique` (`appaId`, `periodId`, `subjectId`) USING BTREE,
  KEY `subjectId_index` (`subjectId`) USING BTREE,
  KEY `periodId_index` (`periodId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Subject Balance Table - Accounting Period Dimension'

supplier

Supplier

CREATE TABLE `supplier` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 1001++',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH1001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `supplierAccountNum` varchar(255)  DEFAULT NULL COMMENT 'payment account number',
  `supplierBankName` varchar(255)  DEFAULT NULL COMMENT 'bank name',
  `supplierBranchBankName` varchar(255)  DEFAULT NULL COMMENT 'branch name',
  `supplierBusinessLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'business license',
  `supplierMedicalOperatingLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'medical device operating license',
  `supplierPaymentCycle` varchar(255)  DEFAULT NULL COMMENT 'payment cycle; unit: days',
  `supplierAddress` varchar(255)  DEFAULT NULL COMMENT 'address',
  `supplierPostalCode` varchar(255)  DEFAULT NULL COMMENT 'postal code',
  `supplierContactPerson` varchar(255)  DEFAULT NULL COMMENT 'contact person',
  `supplierPhoneNum` varchar(255)  DEFAULT NULL COMMENT 'contact phone number',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `supplierId_unique` (`supplierId`) USING BTREE,
  UNIQUE KEY `supplierName_unique` (`supplierName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 47 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Supplier'

supplier_product

Supplier Product Table; productId=supplierId-productName-manufacturerName- concatenated in view or frontend

CREATE TABLE `supplier_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL,
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer',
  `productSpecList` varchar(512)  DEFAULT NULL COMMENT 'specification/model list; “15ml/bag, 20ml/bag”',
  `productUnit` varchar(255)  DEFAULT NULL COMMENT 'unit',
  `productType` varchar(255)  DEFAULT NULL COMMENT 'product type',
  `manufacturerLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'manufacturer license number',
  `manufacturerLicenseExpireAt` varchar(255)  DEFAULT NULL COMMENT 'manufacturer license expiration date; E.g: 2024-05-28',
  `manufacturerProductRCNum` varchar(255)  DEFAULT NULL COMMENT 'product registration certificate number;',
  `manufacturerProductRCExpireAt` varchar(255)  DEFAULT NULL COMMENT 'product registration certificate expiration date; E.g: 2024-03-28',
  `productTransport` varchar(255)  DEFAULT NULL COMMENT 'storage and transportation conditions;',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 936 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Supplier Product Table; productId=supplierId-productName-manufacturerName- concatenated in view or frontend'

ticket

Work Order Table;

CREATE TABLE `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID; T10001',
  `ticketBizId` varchar(255)  DEFAULT NULL COMMENT 'business ID; purchase order ID, sales order ID',
  `ticketType` varchar(255)  DEFAULT NULL COMMENT 'work order type; purchase payment, sales receipt, purchase warehousing, purchase asset warehousing, asset warehousing, sales outbound',
  `ticketDesc` varchar(255)  DEFAULT NULL COMMENT 'work order description;',
  `ticketContentRequest` text  COMMENT 'application content; {}',
  `ticketContent` text  COMMENT 'completion content; {}',
  `ticketCommentList` text  COMMENT 'comment list; [{}, {}] (to be done later)',
  `ticketAmount` int(11) DEFAULT NULL COMMENT 'work order processing amount',
  `ticketRequestAmount` int(11) DEFAULT NULL COMMENT 'application amount',
  `ticketRequestAt` varchar(255)  DEFAULT NULL COMMENT 'application time; E.g: 2021-05-28T10:24:54+08:00',
  `ticketRequestByUserId` varchar(255)  DEFAULT NULL COMMENT 'applicant Id',
  `ticketRequestByUser` varchar(255)  DEFAULT NULL COMMENT 'applicant',
  `ticketStatus` varchar(255)  DEFAULT 'application' COMMENT 'work order status; application, processing, completed, rejected',
  `ticketStatusAt` varchar(255)

# Table Overview

## General Tables

- [_user](/__appId__/page/article/12324#_user)
- [_user_session](/__appId__/page/article/12324#_user_session)
- [_role](/__appId__/page/article/12324#_role)
- [_page](/__appId__/page/article/12324#_page)
- [_resource](/__appId__/page/article/12324#_resource)
- [_group](/__appId__/page/article/12324#_group)
- [_user_group_role](/__appId__/page/article/12324#_user_group_role)
- [_user_group_role_page](/__appId__/page/article/12324#_user_group_role_page)
- [_user_group_role_resource](/__appId__/page/article/12324#_user_group_role_resource)
- [_constant](/__appId__/page/article/12324#_constant)
- [_constant_ui](/__appId__/page/article/12324#_constant_ui)
- [_record](/__appId__/page/article/12324#_record)
- [_record_history](/__appId__/page/article/12324#_record_history)
- [_cache](/__appId__/page/article/12324#_cache)
- [_file](/__appId__/page/article/12324#_file)
- [_view01_user](/__appId__/page/article/12324#_view01_user)

## Business Tables

### _ui

- UI Construction Plan

```sql
CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255)  DEFAULT NULL COMMENT 'UI action type, e.g.: fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT 'description',
  `uiActionConfig` text  COMMENT 'UI action data',
  `appDataSchema` text  COMMENT 'UI validation data',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'UI construction plan'
```

### customer

```sql
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'business auto-increment ID, 1001++',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID; KH1001',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer name',
  `customerAccountNum` varchar(255)  DEFAULT NULL COMMENT 'bank account number',
  `customerBankName` varchar(255)  DEFAULT NULL COMMENT 'bank name',
  `customerBranchBankName` varchar(255)  DEFAULT NULL COMMENT 'branch bank name',
  `customerAddress` varchar(255)  DEFAULT NULL COMMENT 'customer address',
  `customerPostalCode` varchar(255)  DEFAULT NULL COMMENT 'postal code',
  `customerContactPerson` varchar(255)  DEFAULT NULL COMMENT 'contact person',
  `customerPhoneNum` varchar(255)  DEFAULT NULL COMMENT 'contact phone number',
  `customerCollectionCycle` varchar(255)  DEFAULT NULL COMMENT 'collection period; unit: days',
  `customerBusinessLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'business license',
  `customerMedicalOperatingLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'medical device operating license',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin
```

### finance_asset

- Finance - Asset Information Table

```sql
CREATE TABLE `finance_asset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetId` varchar(255)  DEFAULT NULL COMMENT 'asset ID; ZC10001',
  `assetName` varchar(255)  DEFAULT NULL COMMENT 'asset;',
  `assetSpec` varchar(255)  DEFAULT NULL COMMENT 'asset specification',
  `currentPeriod` varchar(255)  DEFAULT NULL COMMENT 'current accounting period; 2022-02',
  `entryPeriod` varchar(255)  DEFAULT NULL COMMENT 'entry period; 2022-02',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `financeClearSubject` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject; fixed asset clearance',
  `financeDepreciationFeeSubject` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject; management expense - depreciation',
  `startUseAt` varchar(255)  DEFAULT NULL COMMENT 'start use date; 2020-02-02',
  `assetValue` decimal(10, 2) DEFAULT NULL COMMENT 'original asset value',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `estimatedUseMonth` int(11) DEFAULT NULL COMMENT 'estimated use months',
  `depreciatedMonths` int(11) DEFAULT NULL COMMENT 'depreciated months',
  `remainingMonth` int(11) DEFAULT NULL COMMENT 'remaining use months',
  `monthlyDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'monthly depreciation amount',
  `totalDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'accumulated depreciation',
  `currentYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current year accumulated depreciation;',
  `preYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'previous year accumulated depreciation',
  `currentPeriodDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current period depreciation',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `financeClearSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject ID;',
  `financeDepreciationFeeSubjectId` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject ID;',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `deptId` varchar(255)  DEFAULT NULL COMMENT 'department ID;',
  `deptName` varchar(255)  DEFAULT NULL COMMENT 'department name;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Table'
```

### finance_asset_record

- Finance - Asset Information Detail Table

```sql
CREATE TABLE `finance_asset_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetId` varchar(255)  DEFAULT NULL COMMENT 'asset ID; ZC10001',
  `assetName` varchar(255)  DEFAULT NULL COMMENT 'asset;',
  `assetSpec` varchar(255)  DEFAULT NULL COMMENT 'asset specification',
  `assetOperationType` varchar(255)  DEFAULT 'depreciation' COMMENT 'operation type',
  `currentPeriod` varchar(255)  DEFAULT NULL COMMENT 'current accounting period; 2022-02',
  `entryPeriod` varchar(255)  DEFAULT NULL COMMENT 'entry period; 2022-02',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `financeClearSubject` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject; fixed asset clearance',
  `financeDepreciationFeeSubject` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject; management expense - depreciation',
  `startUseAt` varchar(255)  DEFAULT NULL COMMENT 'start use date; 2020-02-02',
  `assetValue` decimal(10, 2) DEFAULT NULL COMMENT 'original asset value',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `estimatedUseMonth` int(11) DEFAULT NULL COMMENT 'estimated use months',
  `depreciatedMonths` int(11) DEFAULT NULL COMMENT 'depreciated months',
  `remainingMonth` int(11) DEFAULT NULL COMMENT 'remaining use months',
  `monthlyDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'monthly depreciation amount',
  `totalDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'initial accumulated depreciation',
  `currentYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current year accumulated depreciation',
  `preYearDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'previous year accumulated depreciation',
  `currentPeriodDepreciation` decimal(10, 2) DEFAULT NULL COMMENT 'current period depreciation',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `financeClearSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset clearance subject ID;',
  `financeDepreciationFeeSubjectId` varchar(255)  DEFAULT NULL COMMENT 'depreciation expense subject ID;',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `deptId` varchar(255)  DEFAULT NULL COMMENT 'department ID;',
  `deptName` varchar(255)  DEFAULT NULL COMMENT 'department name;',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Detail Table'
```

### finance_asset_type

- Finance - Asset Information Table

```sql
CREATE TABLE `finance_asset_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `assetTypeId` varchar(255)  DEFAULT NULL COMMENT 'asset type ID; ZCLB10001',
  `assetTypeName` varchar(255)  DEFAULT NULL COMMENT 'asset type name;',
  `depreciationMethod` varchar(255)  DEFAULT NULL COMMENT 'depreciation method; average lifespan method, no depreciation',
  `financeAssetSubject` varchar(255)  DEFAULT NULL COMMENT 'asset subject; fixed assets',
  `financeDepreciationSubject` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject; accumulated depreciation',
  `residualRate` decimal(10, 2) DEFAULT NULL COMMENT 'residual rate',
  `financeAssetSubjectId` varchar(255)  DEFAULT NULL COMMENT 'asset subject ID;',
  `financeDepreciationSubjectId` varchar(255)  DEFAULT NULL COMMENT 'accumulated depreciation subject ID;',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Finance - Asset Information Table'
```

### finance_period

- Depreciation - Accounting Period Table

```sql
CREATE TABLE `finance_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `periodId` varchar(255)  DEFAULT NULL COMMENT 'accounting period; month dimension E.g: 2023-03',
  `financeYear` varchar(255)  DEFAULT NULL COMMENT 'fiscal year;',
  `isDepreciation` varchar(255)  DEFAULT NULL COMMENT 'is depreciation; depreciated, pending depreciation',
  `isPeriodStart` varchar(255)  DEFAULT 'no' COMMENT 'is initial accounting period; yes, no',
  `depreciationVoucherId` varchar(255)  DEFAULT NULL COMMENT 'depreciation voucher ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `periodId_unique` (`periodId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Depreciation - Accounting Period Table'
```

### payment

- Receipts & Payments Table;

```sql
CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID',
  `idSequence` int(255) DEFAULT NULL COMMENT 'sequence id; 10001++',
  `paymentId` varchar(255)  DEFAULT NULL COMMENT 'receipt/payment ID; SK10001 FK10001',
  `paymentType` varchar(20)  DEFAULT NULL COMMENT 'type; receipt, payment',
  `paymentAmount` decimal(10, 2) DEFAULT NULL COMMENT 'amount',
  `paymentPayee` varchar(50)  DEFAULT NULL COMMENT 'receipt account; person, company, account number',
  `paymentPayeeAccount` varchar(255)  DEFAULT NULL COMMENT 'receipt account number; ',
  `paymentPayeeBank` varchar(255)  DEFAULT NULL COMMENT 'receipt account bank',
  `paymentPayeeBranchBank` varchar(255)  DEFAULT NULL COMMENT 'receipt account branch bank',
  `paymentPayer` varchar(50)  DEFAULT NULL COMMENT 'payment account; person, company, account number',
  `paymentPayerAccount` varchar(255)  DEFAULT NULL COMMENT 'payment account number;',
  `paymentPayerBank` varchar(255)  DEFAULT NULL COMMENT 'payment account bank',
  `paymentPayerBranchBank` varchar(255)  DEFAULT NULL COMMENT 'payment account branch bank',
  `paymentMethod` varchar(50)  DEFAULT NULL COMMENT 'method',
  `paymentStatus` varchar(20)  NOT NULL DEFAULT 'unpaid' COMMENT 'payment status; unpaid, paid',
  `invoiceStatus` varchar(255)  DEFAULT 'unbilled' COMMENT 'billing status; unbilled, billed',
  `paymentAt` varchar(255)  DEFAULT NULL COMMENT 'time; E.g: 2021-05-28T10:24:54+08:00',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID;',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID;',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 45 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Receipts & Payments Table;'
```

### payment_order

- Receipts & Payments Table - Order Deduction Table

```sql
CREATE TABLE `payment_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID',
  `paymentId` varchar(255)  DEFAULT NULL COMMENT 'receipt/payment ID; SK10001 FK10001',
  `paymentType` varchar(20)  DEFAULT NULL COMMENT 'type; receipt, payment',
  `orderType` varchar(20)  DEFAULT NULL COMMENT 'order type; purchase order, sales order, purchase return order, sales return order',
  `orderId` varchar(255)  NOT NULL COMMENT 'order ID',
  `deductionAmount` decimal(10, 2) NOT NULL COMMENT 'deduction amount',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 45 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Receipts & Payments Table - Order Deduction Table'
```

### product_price_record

```sql
CREATE TABLE `product_price_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer name',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `saleProductSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'sales tax-inclusive unit price/yuan',
  `productSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin
```

### purchase_order

- Purchase Order Table

```sql
CREATE TABLE `purchase_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `purchaseOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID; CG10001',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier',
  `supplierInfo` mediumtext  COMMENT 'supplier information; json',
  `purchaseOrderType` varchar(255)  DEFAULT 'regular purchase' COMMENT 'purchase order type; regular purchase, asset purchase',
  `purchaseOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'purchase order description;',
  `purchaseProductList` text  COMMENT 'purchase product list; [{}, {}]',
  `purchaseProductListCount` int(11) DEFAULT NULL COMMENT 'number of purchase products',
  `purchaseProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of purchase products',
  `purchaseTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `purchaseOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending purchase, purchasing, order completed/order canceled',
  `purchaseOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order cancellation',
  `purchaseCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `purchaseFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `purchaseOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 122 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Purchase Order Table'
```

### purchase_price_history

```sql
CREATE TABLE `purchase_price_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchaseOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID',
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `productSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan; purchase price',
  `purchaseProductTaxRate` int(11) DEFAULT NULL COMMENT 'tax rate;',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin
```

### purchase_return_order

- Purchase Return Order Table

```sql
CREATE TABLE `purchase_return_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `returnOrderId` varchar(255)  DEFAULT NULL COMMENT 'refund order ID; XSTK10001',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier',
  `supplierInfo` mediumtext  COMMENT 'supplier information; json',
  `returnOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'return order description;',
  `returnProductList` text  COMMENT 'return product list; [{}, {}]',
  `returnProductListCount` int(11) DEFAULT NULL COMMENT 'number of return products',
  `returnProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of return products',
  `returnTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `returnOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending return, returning, order completed/order canceled',
  `returnOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order cancellation',
  `returnCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `returnFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `returnOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `returnOrderType` varchar(255)  DEFAULT NULL COMMENT 'order type; regular return, asset return',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Purchase Return Order Table'
```

### sale_order

```sql
CREATE TABLE `sale_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'primary key ID, 10001++',
  `saleOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID; CG10001',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer',
  `customerInfo` text  COMMENT 'customer',
  `saleOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'sales order description;',
  `saleProductList` text  COMMENT 'sales product list; [{}, {}]',
  `saleProductListCount` int(11) DEFAULT NULL COMMENT 'number of sales products',
  `saleProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of sales products',
  `saleTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `saleOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending sale, selling, order completed/order canceled',
  `saleOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order failure',
  `saleCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `saleOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `saleFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin
```

### sale_price_history

```sql
CREATE TABLE `sale_price_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `saleOrderId` varchar(255)  DEFAULT NULL COMMENT 'order ID',
  `orderType` varchar(255)  DEFAULT NULL COMMENT 'order type',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer name',
  `productSpec` varchar(255)  DEFAULT NULL COMMENT 'product specification',
  `saleProductSpecPrice` decimal(10, 2) DEFAULT NULL COMMENT 'tax-inclusive unit price/yuan; sales price',
  `productTaxRate` int(11) DEFAULT NULL COMMENT 'tax rate;',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin
```

### sale_return_order

- Sales Return Order Table

```sql
CREATE TABLE `sale_return_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'primary key ID, 10001++',
  `returnOrderId` varchar(255)  DEFAULT NULL COMMENT 'refund order ID; CGTK10001',
  `customerId` varchar(255)  DEFAULT NULL COMMENT 'customer ID',
  `customerName` varchar(255)  DEFAULT NULL COMMENT 'customer',
  `customerInfo` text  COMMENT 'customer',
  `returnOrderDesc` varchar(255)  DEFAULT NULL COMMENT 'return order description;',
  `returnProductList` text  COMMENT 'return product list; [{}, {}]',
  `returnProductListCount` int(11) DEFAULT NULL COMMENT 'number of return products',
  `returnProductTotalCount` int(11) DEFAULT NULL COMMENT 'total number of return products',
  `returnTotalAmount` decimal(10, 2) DEFAULT NULL COMMENT 'order amount',
  `returnOrderStatus` varchar(255)  DEFAULT NULL COMMENT 'order status; pending return, returning, order completed/order canceled',
  `returnOrderStatusDesc` varchar(255)  DEFAULT NULL COMMENT 'order status description; e.g.: fill in the reason for order failure',
  `returnCreateAt` varchar(255)  DEFAULT NULL COMMENT 'order creation date',
  `returnOrderRemark` varchar(255)  DEFAULT NULL COMMENT 'order remark',
  `returnFinishAt` varchar(255)  DEFAULT NULL COMMENT 'order completion time',
  `operation` varchar(255) CHARACTER SET utf8 DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Sales Return Order Table'
```

### subject

- Subject Table

```sql
CREATE TABLE `subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appaId` varchar(255)  DEFAULT NULL COMMENT 'account set ID',
  `subjectId` varchar(255)  DEFAULT NULL COMMENT 'subject code; asset class 1001++, liability class 2001++..',
  `subjectName` varchar(255)  DEFAULT NULL COMMENT 'subject name;',
  `subjectLabel` varchar(255)  DEFAULT NULL COMMENT 'subject label;',
  `subjectCategory` varchar(255)  DEFAULT NULL COMMENT 'subject category; asset, liability, equity, cost, profit and loss',
  `subjectLevel` int(11) DEFAULT '1' COMMENT 'subject level; level 1 subject, level 2 subject….',
  `subjectHasChildren` varchar(255)  DEFAULT 'no sub-level subject' COMMENT 'subject type; has sub-level subject, no sub-level subject',
  `subjectBalanceDirection` varchar(4)  DEFAULT NULL COMMENT 'subject balance direction; debit, credit',
  `assistList` varchar(255)  DEFAULT NULL COMMENT 'auxiliary accounting configuration list; concatenated with “,” E.g: customer, supplier, staff, project, department, extension 1, extension 2 ',
  `isShown` varchar(255)  DEFAULT 'shown' COMMENT 'is displayed; shown, hidden',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `appaId_subject_unique` (`appaId`, `subjectId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4840 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Subject Table'
```

### subject_balance_period

- Subject Balance Table - Accounting Period Dimension

```sql
CREATE TABLE `subject_balance_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `appaId` varchar(255)  DEFAULT NULL COMMENT 'account set ID',
  `periodId` varchar(255)  DEFAULT NULL COMMENT 'accounting period; E.g: 2023-03',
  `financeYear` varchar(255)  DEFAULT NULL COMMENT 'fiscal year;',
  `subjectId` varchar(255)  DEFAULT NULL COMMENT 'subject code;',
  `startDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'initial balance - debit',
  `startCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'initial balance - credit',
  `occurDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period occurrence - debit',
  `occurCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period occurrence - credit',
  `occurAmount` decimal(14, 2) DEFAULT '0.00' COMMENT 'current period balance/current actual profit and loss occurrence',
  `endDebit` decimal(14, 2) DEFAULT '0.00' COMMENT 'ending balance - debit',
  `endCredit` decimal(14, 2) DEFAULT '0.00' COMMENT 'ending balance - credit',
  `isPeriodStart` varchar(255)  DEFAULT 'no' COMMENT 'is initial accounting period; yes, no',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `appaId_periodId_subjectId_unique` (`appaId`, `periodId`, `subjectId`) USING BTREE,
  KEY `subjectId_index` (`subjectId`) USING BTREE,
  KEY `periodId_index` (`periodId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Subject Balance Table - Accounting Period Dimension'
```

### supplier

- Supplier

```sql
CREATE TABLE `supplier` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 1001++',
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH1001',
  `supplierName` varchar(255)  DEFAULT NULL COMMENT 'supplier name',
  `supplierAccountNum` varchar(255)  DEFAULT NULL COMMENT 'payment account number',
  `supplierBankName` varchar(255)  DEFAULT NULL COMMENT 'bank name',
  `supplierBranchBankName` varchar(255)  DEFAULT NULL COMMENT 'branch name',
  `supplierBusinessLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'business license',
  `supplierMedicalOperatingLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'medical device operating license',
  `supplierPaymentCycle` varchar(255)  DEFAULT NULL COMMENT 'payment cycle; unit: days',
  `supplierAddress` varchar(255)  DEFAULT NULL COMMENT 'address',
  `supplierPostalCode` varchar(255)  DEFAULT NULL COMMENT 'postal code',
  `supplierContactPerson` varchar(255)  DEFAULT NULL COMMENT 'contact person',
  `supplierPhoneNum` varchar(255)  DEFAULT NULL COMMENT 'contact phone number',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `supplierId_unique` (`supplierId`) USING BTREE,
  UNIQUE KEY `supplierName_unique` (`supplierName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 47 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Supplier'
```

### supplier_product

- Supplier Product Table; productId=supplierId-productName-manufacturerName- concatenated in view or frontend

```sql
CREATE TABLE `supplier_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplierId` varchar(255)  DEFAULT NULL COMMENT 'supplier ID; GH10001',
  `supplierName` varchar(255)  DEFAULT NULL,
  `productName` varchar(255)  DEFAULT NULL COMMENT 'product name',
  `manufacturerName` varchar(255)  DEFAULT NULL COMMENT 'manufacturer',
  `productSpecList` varchar(512)  DEFAULT NULL COMMENT 'specification/model list; “15ml/bag, 20ml/bag”',
  `productUnit` varchar(255)  DEFAULT NULL COMMENT 'unit',
  `productType` varchar(255)  DEFAULT NULL COMMENT 'product type',
  `manufacturerLicenseNum` varchar(255)  DEFAULT NULL COMMENT 'manufacturer license number',
  `manufacturerLicenseExpireAt` varchar(255)  DEFAULT NULL COMMENT 'manufacturer license expiration date; E.g: 2024-05-28',
  `manufacturerProductRCNum` varchar(255)  DEFAULT NULL COMMENT 'product registration certificate number;',
  `manufacturerProductRCExpireAt` varchar(255)  DEFAULT NULL COMMENT 'product registration certificate expiration date; E.g: 2024-03-28',
  `productTransport` varchar(255)  DEFAULT NULL COMMENT 'storage and transportation conditions;',
  `remark` varchar(255)  DEFAULT NULL COMMENT 'remark',
  `rowStatus` varchar(255)  DEFAULT 'normal' COMMENT 'data status; normal, disabled',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 936 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Supplier Product Table; productId=supplierId-productName-manufacturerName- concatenated in view or frontend'
```

### ticket

- Work Order Table;

```sql
CREATE TABLE `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT 'auto-increment ID, 10001++',
  `ticketId` varchar(255)  DEFAULT NULL COMMENT 'work order ID; T10001',
  `ticketBizId` varchar(255)  DEFAULT NULL COMMENT 'business ID; purchase order ID, sales order ID',
  `ticketType` varchar(255)  DEFAULT NULL COMMENT 'work order type; purchase payment, sales receipt, purchase warehousing, purchase asset warehousing, asset warehousing, sales outbound',
  `ticketDesc` varchar(255)  DEFAULT NULL COMMENT 'work order description;',
  `ticketContentRequest` text  COMMENT 'application content; {}',
  `ticketContent` text  COMMENT 'completion content; {}',
  `ticketCommentList` text  COMMENT 'comment list; [{}, {}] (to be done later)',
  `ticketAmount` int(11) DEFAULT NULL COMMENT 'work order processing amount',
  `ticketRequestAmount` int(11) DEFAULT NULL COMMENT 'application amount',
  `ticketRequestAt` varchar(255)  DEFAULT NULL COMMENT 'application time; E.g: 2021-05-28T10:24:54+08:00',
  `ticketRequestByUserId` varchar(255)  DEFAULT NULL COMMENT 'applicant Id',
  `ticketRequestByUser` varchar(255)  DEFAULT NULL COMMENT 'applicant',
  `ticketStatus` varchar(255)  DEFAULT 'application' COMMENT 'work order status; application, processing, completed, rejected',
  `ticketStatusAt` varchar(255)