6 07 2016

Usecase: import multiple entries into “Billings OSX”. We are using sqlite3 (as Billings OSX saves all its data into a Sqlite3 Database). This is just my very personal cheatsheet but anayone who’s interested might just use it for learning purposes.

sqlite3 "/Users/user/Library/Application Support/Billings/Database/billings.bid"
.table   # OR: .tables
.schema Invoice

--
.mode list
.separator ", "  # OR:  .separator |
--
.mode line
--
.mode column
--

.fullschema
PRAGMA table_info(Invoice);
.dump
.quit


SELECT * FROM Client;
SELECT * FROM Invoice;
select i.invoiceNumber, strftime('%Y-%m-%d', i.invoiceDate/86400.0 + 2440587.5) as invdate, i.totalCached, c.company, c.firstName, c.lastName, c.addressStreet, c.addressZIP, c.addressCity, c.addressCountry FROM Invoice i left join Client c on c._rowid=i.clientID WHERE invdate LIKE '2016%';


SELECT * FROM Project;
SELECT * FROM Timeslip;

--
.mode line
SELECT * FROM Project WHERE name LIKE '%InterSSL%';
# _rowid = 13100
# clientID = 4400
# createDate = 1442221820.36794
# uuid = ACEF1208-B9A6-433D-92E0-6C1A9C94D3FD
# name = InterSSL/RapidSSL Zertifikate (Gutschrift für InterSSL)
--
# _rowid = 13000
# clientID = 4400
# createDate = 1442221820.36794
# uuid = 78635FBA-E58A-4F74-AB2A-CDA591F25EED
# name = InterSSL/RapidSSL Zertifikate
--

# InterSSL/RapidSSL Zertifikate
SELECT * FROM TimeSlip WHERE projectID = 13000; 
# InterSSL/RapidSSL Zertifikate (Gutschrift für InterSSL)
SELECT * FROM TimeSlip WHERE projectID = 13100;


SELECT * FROM EO_PK WHERE tableName='TimeSlip';
#   _rowid = 26
#tableName = TimeSlip
# nextPKey = 243500

# To increment the Counter (Billings seems to use setps of 100's):
UPDATE EO_PK SET nextPKey=(nextPKey+100) WHERE tableName='TimeSlip';




Konkretes Beispiel:
sqlite> SELECT * FROM TimeSlip WHERE projectID = 13000;
        activeForTiming = 1
             categoryID = 
                 userID = 1
                comment = 
   compoundSecondaryTax = 
      consolidatedTaxID = 4
             createDate = 1467809266.69955
               discount = 0.0
               distance = 0.0
                dueDate = 
               duration = 0.0
            endDateTime = 
   foreignAppEntityName = 
     foreignAppImportID = 
foreignAppLastTouchDate = 
         foreignAppName = 
         foreignAppUser = 
                iCalUID = 
              invoiceID = 
           invoicedDate = 
                 markup = 0.0
            mileageType = 1
             modifyDate = 1467809529.16966
                   name = test
                 nature = 101
              projectID = 13000
                   rate = 90.0
              roundTime = 15
          startDateTime = 
                   tax1 = 
               tax1Name = 
                   tax2 = 
               tax2Name = 
                 _rowid = 243300
          timeSlipRefID = 
                  total = 0.0
                 typeID = 1
                  units = 0.0
                useTax1 = 
                useTax2 = 
                   uuid = 28DCAA24-B0B3-4406-8E65-FDDAE3201532
      invoicedProjectID = 
            totalCached = 351.0
         durationCached = 11700
       geoLocationStart = 
         geoLocationEnd = 




Full Insert Example - How to Get the _rowid:
---
sqlite3 "/Users/user/Library/Application Support/Billings/Database/billings.bid"
.mode line
SELECT name, _rowid FROM Project WHERE name LIKE '%InterSSL%';

The UUIDs for inserting into table TimeSlip are created from scratch (random). When using type 2 (=fixed) no entries in TimeEntry are required. Otherwise you will also need to add entries there. Full Insert Example (this has been produced by some PHP script of mine):
--
INSERT INTO TimeSlip (_rowid, activeForTiming, userID, consolidatedTaxID, createDate, discount, distance, duration, markup, mileageType, modifyDate, name, nature, projectID, rate, roundTime, total, typeID, units, uuid, totalCached, durationCached) VALUES ((SELECT nextPKey FROM EO_PK WHERE tableName='TimeSlip'), 1, 1, 4, '1467809266.69955', 0.0, 0.0, 60.0, 0.0, 1, '1467809266.69955', 'InterSSL #YYYY vom 11.01.2016 www.somedomain.at', 101, 13000, 14.99, 15, 0.0, 2, 0.0, 'D40119D7-3E55-472A-9887-2BAD45470ECE', 14.99, 3600); 
UPDATE EO_PK SET nextPKey=(nextPKey+100) WHERE tableName='TimeSlip';
INSERT INTO TimeSlip (_rowid, activeForTiming, userID, consolidatedTaxID, createDate, discount, distance, duration, markup, mileageType, modifyDate, name, nature, projectID, rate, roundTime, total, typeID, units, uuid, totalCached, durationCached) VALUES ((SELECT nextPKey FROM EO_PK WHERE tableName='TimeSlip'), 1, 1, 4, '1467809266.69955', 0.0, 0.0, 60.0, 0.0, 1, '1467809266.69955', 'Gutschrift zu InterSSL #XXXX vom 11.01.2016 www.anotherdomain.at', 101, 13100, 14.99, 15, 0.0, 2, 0.0, 'D40119D7-3E55-472A-9887-2BAD45470ECE', 14.99, 3600); 
UPDATE EO_PK SET nextPKey=(nextPKey+100) WHERE tableName='TimeSlip'; 

.
.
.
UPDATE Project SET totalCached=359.65 WHERE _rowid=13000; 
UPDATE Project SET totalCached=359.65 WHERE _rowid=13100;

Here’s the UUID generator (found it somewhere on the web – can’t remember where, sorry about that):

function gen_uuid() 
{
    return sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
        // 32 bits for "time_low"
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),

        // 16 bits for "time_mid"
        mt_rand( 0, 0xffff ),

        // 16 bits for "time_hi_and_version",
        // four most significant bits holds version number 4
        mt_rand( 0, 0x0fff ) | 0x4000,

        // 16 bits, 8 bits for "clk_seq_hi_res",
        // 8 bits for "clk_seq_low",
        // two most significant bits holds zero and one for variant DCE1.1
        mt_rand( 0, 0x3fff ) | 0x8000,

        // 48 bits for "node"
        mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff )
    );
}

Headset Microphone Audio quality vs. MacBook’s internal Mic VirtualBox: controlling VMs from command line shell (bash)