Getting started

INSERT INTO
  Bill (
    Bill_Number,
    Vendor_Id,
    [Date],
    Due_Date,
    Currency_Code,
    Line_Items
  )
VALUES
  (
    'BILL-002',
    '576438000000118013',
    '2024-06-25',
    '2024-07-25',
    'USD',
    '[
      {
        "item_order": 1,
        "account_name": "IT and Internet Expenses",
        "account_id": "576438000000000415",
        "description": "Dell Laser Paper",
        "rate": 3.5,
        "quantity": 16,
        "item_total": 56
      }
     ]'
  )

[
  {
    "item_order": 1,
    "account_name": "IT and Internet Expenses",
    "account_id": "576438000000000415",
    "description": "Dell Laser Paper",
    "rate": 3.5,
    "quantity": 16,
    "item_total": 56
  }
]

INSERT INTO
  Invoice_Comment (
    Invoice_Id,
    Description,
    Show_Comment_To_Clients,
    Payment_Expected_Date
  )
VALUES
  (
    '579676000000050234',
    'Customer comment.',
    1,
    '2024-08-29'
  )

INSERT INTO Contact (Contact_Name) VALUES ('J. Jones Lumber')

INSERT INTO
  Credit_Note (Customer_Id, Currency_Id, [Date], Line_Items)
values
  (
    '576438000000066678',
    '576438000000000059',
    '2021-06-14',
    '[
      {
        "account_id": "576438000000000376",
        "tax_type": "tax",
        "item_id": "576438000000080083",
        "name": "1000-99-9999-99-99-99-07",
        "tax_id": "576438000000056007",
        "item_order": 1,
        "description": "Easysoft Contract - Renewal",
        "quantity": 1,
        "unit": "pcs",
        "item_total": 404.73,
        "sales_rate": 1,
        "rate": 404.73,
        "tax_percentage": 10,
        "account_name": "Sales",
        "tax_name": "10% Tax",
        "pricing_scheme": "unit",
        "item_type": "sales"
      }
     ]'
  )

[
  {
    "account_id": "576438000000000376",
    "tax_type": "tax",
    "item_id": "576438000000080083",
    "name": "1000-99-9999-99-99-99-07",
    "tax_id": "576438000000056007",
    "item_order": 1,
    "description": "Easysoft Contract - Renewal",
    "quantity": 1,
    "unit": "pcs",
    "item_total": 404.73,
    "sales_rate": 1,
    "rate": 404.73,
    "tax_percentage": 10,
    "account_name": "Sales",
    "tax_name": "10% Tax",
    "pricing_scheme": "unit",
    "item_type": "sales"
  }
]

INSERT INTO
  Customer_Payment (Customer_Id, Payment_Mode, Amount, Invoices)
VALUES
  (
    '576438000000072770',
    'cash',
    373.76,
    '[
      {
        "Invoice_Id": "576438000000104809",
        "invoice_number": "39440",
        "date": "2024-07-03",
        "invoice_amount": "373.76",
        "amount_applied": "373.76",
        "balance_amount": "0"
      }
     ]'
  )

[
  {
    "Invoice_Id": "576438000000104809",
    "invoice_number": "39440",
    "date": "2024-07-03",
    "invoice_amount": "373.76",
    "amount_applied": "373.76",
    "balance_amount": "0"
  }
]

INSERT INTO
  Delivery_Challan (
    [Date],
    Challan_Type,
    Reference_Number,
    Currency_Code,
    Customer_Id,
    Tax_Treatment,
    Place_Of_Supply,
    Line_Items
  )
VALUES
  (
    '2024-09-01',
    'job work',
    'DelChallan2',
    'BHD',
    '633066000000053001',
    'vat_registered',
    'BH',
    '{"description":"A5 Notepad","rate":9.99,"quantity":3}'
  )

INSERT INTO
  Estimate ([Date], Customer_Id, Line_Items)
VALUES
  (
    '2024-07-08',
    '576438000000059096',
    '[
      {
        "header_name": "Leads",
        "description": "USB-C",
        "rate": 2.75,
        "quantity": 33,
        "discount": "5.00%",
        "tax_id": "576438000000056007"
      },
      {
        "header_name": "Leads",
        "description": "USB-B",
        "rate": 0.68,
        "quantity": 127,
        "discount": "11.5%",
        "tax_id": "576438000000056007"
      },
      {
        "header_name": "Paper",
        "description": "A4 (5 x per box)",
        "rate": 9.99,
        "quantity": 5,
        "tax_id": "576438000000056007"
      },
      {
        "header_name": "Paper",
        "description": "A5 (10 x per box)",
        "rate": 6.87,
        "quantity": 12,
        "tax_id": "576438000000056007"
      }
     ]'
  )

[
  {
    "header_name": "Leads",
    "description": "USB-C",
    "rate": 2.75,
    "quantity": 33,
    "discount": "5.00%",
    "tax_id": "576438000000056007"
  },
  {
    "header_name": "Leads",
    "description": "USB-B",
    "rate": 0.68,
    "quantity": 127,
    "discount": "11.5%",
    "tax_id": "576438000000056007"
  },
  {
    "header_name": "Paper",
    "description": "A4 (5 x per box)",
    "rate": 9.99,
    "quantity": 5,
    "tax_id": "576438000000056007"
  },
  {
    "header_name": "Paper",
    "description": "A5 (10 x per box)",
    "rate": 6.87,
    "quantity": 12,
    "tax_id": "576438000000056007"
  }
]

INSERT INTO
  Expense (
    Transaction_Type,
    Account_Id,
    Account_Name,
    Vendor_Id,
    Vendor_Name,
    Currency_Code,
    Line_Items
  )
VALUES
  (
    'expense',
    '576438000000000406',
    'Air Travel Expense',
    '576438000000166003',
    'Air Travel USA',
    'USD',
    '[
      {
        "account_id": "576438000000000406",
        "account_name": "Air Travel Expense",
        "description": "New York to LA",
        "tax_amount": 10,
        "tax_type": "tax",
        "tax_percentage": 10,
        "item_total": 100,
        "amount": 110,
        "item_order": 1
      },
      {
        "account_id": "576438000000000406",
        "account_name": "Air Travel Expense",
        "description": "LA to Miami",
        "tax_amount": 30,
        "tax_type": "tax",
        "tax_percentage": 10,
        "item_total": 300,
        "amount": 330,
        "item_order": 2
      }
     ]'
  )

[
  {
    "account_id": "576438000000000406",
    "account_name": "Air Travel Expense",
    "description": "New York to LA",
    "tax_amount": 10,
    "tax_type": "tax",
    "tax_percentage": 10,
    "item_total": 100,
    "amount": 110,
    "item_order": 1
  },
  {
    "account_id": "576438000000000406",
    "account_name": "Air Travel Expense",
    "description": "LA to Miami",
    "tax_amount": 30,
    "tax_type": "tax",
    "tax_percentage": 10,
    "item_total": 300,
    "amount": 330,
    "item_order": 2
  }
]

INSERT INTO
  Item (
    [Name],
    Description,
    Tax_Id,
    Item_Type,
    Product_Type,
    Unit_Id,
    Rate,
    Account_Id,
    Purchase_Rate,
    Purchase_Description,
    Purchase_Account_Id,
    Vendor_Id
  )
VALUES
  (
    '1TB Hard Disk',
    'WD 1TB HDD 3.5"',
    '579676000000050178',
    'sales_and_purchases',
    'goods',
    '579676000000050038',
    '299',
    '579676000000000376',
    '249',
    '1 x WD 1TB HDD 3.5"',
    '579676000000000509',
    '579676000000081069'
  )

INSERT INTO
  Journal (
    Reference_Number,
    Journal_Date,
    Journal_Type,
    Currency_Id,
    Notes,
    Vat_Treatment,
    Tax_Treatment,
    Product_Type,
    Line_Items
  )
VALUES
  (
    'Bank charge 2',
    '2024-08-06',
    'both',
    '579676000000000065',
    'Standard cheque processing charge.',
    'non_eu',
    'non_eu',
    'service',
    '[
      {
        "account_id": 579676000000000400,
        "customer_id": 579676000000050200,
        "description": "Standard cheque charge",
        "tax_id": 579676000000050200,
        "debit_or_credit": "debit",
        "amount": 4.99
      },
      {
        "account_id": 579676000000000500,
        "customer_id": 579676000000050200,
        "description": "Bank charge",
        "debit_or_credit": "credit",
        "amount": 4.99
      }
     ]'
  )

[
  {
    "account_id": 579676000000000400,
    "customer_id": 579676000000050200,
    "description": "Standard cheque charge",
    "tax_id": 579676000000050200,
    "debit_or_credit": "debit",
    "amount": 4.99
  },
  {
    "account_id": 579676000000000500,
    "customer_id": 579676000000050200,
    "description": "Bank charge",
    "debit_or_credit": "credit",
    "amount": 4.99
  }
]

INSERT INTO
  Purchase_Order (Vendor_Id, [Date], Line_Items)
VALUES
  (
    '579676000000081069',
    '2024-07-30',
    '[
      {
        "description": "2TB Hard Disk",
        "rate": 99,
        "quantity": 3,
        "account_id": "579676000000000415",
        "tax_id": "579676000000050178"
      }
     ]'
  )

[
  {
    "description": "2TB Hard Disk",
    "rate": 99,
    "quantity": 3,
    "account_id": "579676000000000415",
    "tax_id": "579676000000050178"
  }
]

INSERT INTO
  Recurring_Bill (
    Recurrence_Name,
    Start_Date,
    End_Date,
    Recurrence_Frequency,
    Repeat_Every,
    Vendor_Id,
    Line_Items
  )
VALUES
  (
    'A5 notepads',
    '2024-07-08',
    '2026-12-12',
    'months',
    '1',
    '548752000000052031',
    '[
      {
        "account_id": "548752000000000430",
        "description": "A5 Notepad",
        "rate": 9.99,
        "quantity": 3
      }
     ]'
  )

[
  {
    "account_id": "548752000000000430",
    "description": "A5 Notepad",
    "rate": 9.99,
    "quantity": 3
  }
]

INSERT INTO
  Recurring_Expense (
    Recurrence_Name,
    Start_Date,
    End_Date,
    Recurrence_Frequency,
    Repeat_Every,
    Vendor_Id,
    Paid_Through_Account_Id,
    Line_Item
  )
VALUES
  (
    'Window Clean',
    '2024-07-08',
    '2024-12-12',
    'weeks',
    '1',
    '576438000000173100',
    '576438000000118006',
    '[
      {
        "account_id": "576438000000173094",
        "account_name": "Cleaning",
        "description": "Weekly Outside Window Clean",
        "amount": 100
      }
     ]'
  )

[
  {
    "account_id": "576438000000173094",
    "account_name": "Cleaning",
    "description": "Weekly Outside Window Clean",
    "amount": 100
  }
]

UPDATE Bank_Account
SET
  Account_Name = 'COOP Account'
WHERE
  Account_Id = '576438000000114002'

UPDATE Chart_Of_Account
SET
  Description = 'Testing the COA'
WHERE
  Account_Id = '579676000000106021'

UPDATE Contact
SET
  Company_Name = 'J. Jones Lumber'
WHERE
  Contact_Id = '576438000000152001'

UPDATE Credit_Note
SET
  [Date] = '2024-01-01'
WHERE
  Creditnote_Id = '576438000000159004'

UPDATE Customer_Payment
SET
  Description = 'Customer paid at till'
WHERE
  Payment_Id = '576438000000168003'

UPDATE Expense
SET
  [Date] = '2024-07-01'
WHERE
  Expense_Id = '576438000000170042'

UPDATE Invoice_Comment
SET
  Description = 'Invoice updated.'
WHERE
  Invoice_Id = '579676000000050234'
  AND Comment_Id = '579676000000061089'

UPDATE Item SET Rate=Rate*1.1 WHERE product_type = 'goods'

UPDATE Organization
SET
  Address_Street_Address1 = 'Building 1'
WHERE
  organization_id = '20100387237'

UPDATE Purchase_Order
SET
  Line_Items = REPLACE (Line_Items, '{"quantity": 3', '{"quantity": 4')
WHERE
  purchaseorder_id = '579676000000092047'

UPDATE Recurring_Invoice
SET
  Repeat_Every = '2'
WHERE
  Recurring_Invoice_Id = '579676000000076008'

UPDATE Tax
SET
  Tax_Percentage = 17.75
WHERE
  Tax_Id = '579676000000165002'

DELETE FROM Invoice_Comment
WHERE
  Invoice_Id = '579676000000050234'
  AND Comment_Id = '579676000000165022'

DELETE FROM Bill WHERE Bill_Id='579676000000088074'

SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/bills/579676000000089067'
  AND Method = 'DELETE'

DELETE FROM Customer_Payment
WHERE
  Payment_Id = '576438000000168003'

SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/customerpayments/579676000000107086'
  AND Method = 'DELETE'

DELETE FROM Journal
WHERE
  Journal_Id = '579676000000130007'

SELECT
  *
FROM
  API
WHERE
  URL = 'books/v3/journals/579676000000130007/'
  AND Method = 'DELETE'

SELECT
  *
FROM
  API
WHERE
  URL = '/books/v3/contacts/576438000000152001/address/576438000000149024'
  AND Method = 'DELETE'

DELETE FROM Bank_Account WHERE Balance = 0
DELETE FROM Bill WHERE [Date] = '2024-07-01'
DELETE FROM Estimate WHERE [Date] = '2024-07-08'
DELETE FROM Item WHERE [Name] = '1TB Hard Disk'