About the Easysoft ODBC-FreeAgent Driver

SELECT * FROM Invoices

-- Which different customers do we invoice?
SELECT DISTINCT Contact_Name AS Different_Contact_Name FROM Invoices
-- How many different customers do we invoice?
SELECT COUNT(DISTINCT Contact_Name) AS Different_Contact_Total FROM Invoices

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoice
WHERE
  Currency = 'USD'

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  Currency = 'USD'
  OR Currency = 'EUR'

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  Currency = 'USD'
  AND Total_Value < 1000

cursor.execute("SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  Currency = ?", ['USD'])

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  NOT Currency = 'USD'

SELECT
  Id,
  Contact_Name,
  Net_Value
FROM
  Invoices
ORDER BY
  Net_Value ASC

SELECT
  *
FROM
  Contacts
ORDER BY
  (
    CASE
      WHEN Last_Name IS NULL THEN Organisation_Name
      ELSE Last_Name
    END
  );

SELECT
    COUNT(Id) As "Number",
    Currency
FROM
    Invoices
GROUP BY
    Currency

SELECT
  COUNT(Id) As "Number",
  Currency
FROM
  Invoices
GROUP BY
  Currency
HAVING
  COUNT(Id) > 10;

SELECT Max(Total_Value) FROM Invoices
SELECT Sum(Total_Value) FROM Invoices

SELECT CAST(Total_Value AS Char(100))FROM Invoices
SELECT {fn CONVERT(Total_Value, SQL_CHAR)} FROM Invoices

SELECT Id, Contact_Name FROM Invoices WHERE Total_Value BETWEEN 10 AND 20

SELECT
    Group_Description,
    Description,
    Id,
    Url,
    Nominal_Code,
    Allowable_For_Tax,
    Tax_Reporting_Name,
    Auto_Sales_Tax_Rate
FROM
    Categories_Admin_Expenses
UNION ALL
SELECT
    Group_Description,
    Description,
    Id,
    Url,
    Nominal_Code,
    Allowable_For_Tax,
    Tax_Reporting_Name,
    Auto_Sales_Tax_Rate
FROM
    Categories_Cost_Of_Sales
UNION ALL
SELECT
    REPLACE (Url, Url, 'General') AS Group_Description,
    Description,
    Id,
    Url,
    Nominal_Code,
    CAST(NULL AS bit) AS Allowable_For_Tax,
    Tax_Reporting_Name,
    CAST(NULL AS double) AS Auto_Sales_Tax_Rate
FROM
    Categories_General
UNION ALL
SELECT
    Group_Description,
    Description,
    Id,
    Url,
    Nominal_Code,
    CAST(NULL AS bit) AS Allowable_For_Tax,
    REPLACE (Url, Url, 'N/A') AS Tax_Reporting_Name,
    Auto_Sales_Tax_Rate
FROM
    Categories_Income

SELECT
    a.Name,
    bt.*
FROM
    Bank_accounts a
    LEFT JOIN Bank_Transactions bt ON bt.Bank_Account = a.URL

SELECT Id FROM Invoices WHERE Contact_Name LIKE 'E%'
SELECT Id FROM Invoices WHERE Contact_Name LIKE '_asysoft'

SELECT Id, Contact_Name, Total_Value FROM Invoices WHERE Include_Estimates IS NULL

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  Currency = 'USD'
  OR Currency = 'EUR'
  OR Currency = 'GBP'

SELECT
  Id,
  Contact_Name,
  Total_Value
FROM
  Invoices
WHERE
  Currency IN ('USD', 'EUR', 'GBP');

SELECT TOP 10 Id, Contact_Name FROM Invoices

SELECT
  Contact_Name
FROM
  Invoices
WHERE
  EXISTS (
    SELECT
      First_Name,
      Last_Name
    FROM
      Contacts
    WHERE
      Invoices.Contact_Id = Contacts.Id
      AND Currency = 'USD'
  )

INSERT INTO
    Bills (
        Contact,
        Reference,
        Dated_On,
        Due_On,
        Bill_Items
    )
VALUES
    (
        'https://api.freeagent.com/v2/contacts/18727314',
        'YEB021',
        '2025-01-16',
        '2025-01-16',
        '[{"category":"https://api.freeagent.com/v2/categories/291","description":"YEB-line-2-standing-charge","sales_tax_rate":"20","total_value_ex_tax":6.99}]'
    )

EXEC ('INSERT INTO Bills (Contact, Reference, Dated_On, Due_On, Bill_Items)
VALUES (''https://api.freeagent.com/v2/contacts/18727314'' , ''YEB021'' , ''2025-01-16'' , ''2025-01-16'' , ''[{"category":"https://api.freeagent.com/v2/categories/291","description":"YEB-line-2-standing-charge","sales_tax_rate":"20","total_value_ex_tax":6.99}]'')')

DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@FreeAgentLink
('INSERT INTO Bills (Contact, Reference, Dated_On, Due_On, Bill_Items) VALUES (''https://api.freeagent.com/v2/contacts/18727314'', ''YEB021'', ''2025-01-16'', ''2025-01-16'', ''[{"category":"https://api.freeagent.com/v2/categories/291","description":"YEB-line-2-standing-charge","sales_tax_rate":"20","total_value_ex_tax":6.99}]'')');
END;
/

$stmt = odbc_prepare($cnx, "INSERT INTO Bills (Contact, Reference, Dated_On, Due_On, Bill_Items) VALUES (?, ?, ?, ?, ?)");

$success = odbc_execute($stmt, array('https://api.freeagent.com/v2/contacts/18727314', 'YEB021', '2025-01-16', '2025-01-16', '[{"category":"https://api.freeagent.com/v2/categories/291","description":"YEB-line-2-standing-charge","sales_tax_rate":"20","total_value_ex_tax":6.99}]'));

UPDATE Bills
SET
  Contact = 'Standing charges for second line.'
WHERE
  Account_Id = '31962267'

my $sth = $dbh->prepare('UPDATE Bills SET Contact = \'Standing charges for second line.\' WHERE Id = ?')
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('31962267');

-- Delete (mark inactive) a bank account
DELETE FROM Bills WHERE Id = '31962267'

sql = "DELETE FROM Bills WHERE Id = ?"
cursor.execute(sql, '31962267')