academy-chauffeurs - calls.go

     1  package main
     2  
     3  import (
     4  	"crypto/sha1"
     5  	"database/sql"
     6  	"errors"
     7  	"net/rpc"
     8  	"sort"
     9  	"strings"
    10  	"sync"
    11  	"time"
    12  
    13  	_ "github.com/mattn/go-sqlite3"
    14  )
    15  
    16  type Driver struct {
    17  	ID                                    int64
    18  	Name, RegistrationNumber, PhoneNumber string
    19  	Pos                                   int
    20  	Show                                  bool
    21  }
    22  
    23  type Company struct {
    24  	ID            int64
    25  	Name, Address string
    26  	Colour        uint32
    27  }
    28  
    29  type Client struct {
    30  	ID, CompanyID                                int64
    31  	Name, PhoneNumber, Reference, Email, Address string
    32  }
    33  
    34  type Event struct {
    35  	ID, DriverID, ClientID                               int64
    36  	Start, End                                           int64
    37  	Other, From, To, ClientReference, Booker, FlightTime string
    38  	MessageSent                                          bool
    39  	InvoiceNote, InvoiceFrom, InvoiceTo                  string
    40  	Profile                                              uint64
    41  }
    42  
    43  const (
    44  	CreateDriver = iota
    45  	CreateCompany
    46  	CreateClient
    47  	CreateEvent
    48  	CreateFromAddress
    49  	CreateToAddress
    50  
    51  	ReadDriver
    52  	ReadCompany
    53  	ReadClient
    54  	ReadEvent
    55  	ReadEventFinals
    56  	ReadFromAddress
    57  	ReadToAddress
    58  
    59  	UpdateDriver
    60  	UpdateCompany
    61  	UpdateClient
    62  	UpdateEvent
    63  	UpdateEventFinals
    64  
    65  	DeleteDriver
    66  	DeleteCompany
    67  	DeleteClient
    68  	DeleteEvent
    69  
    70  	DeleteDriverEvents
    71  	DeleteClientEvents
    72  	DeleteCompanyClients
    73  	DeleteCompanyEvents
    74  
    75  	GetDriverNote
    76  	GetCompanyNote
    77  	GetClientNote
    78  	GetEventNote
    79  
    80  	SetDriverNote
    81  	SetCompanyNote
    82  	SetClientNote
    83  	SetEventNote
    84  
    85  	SetDriverShowPos
    86  
    87  	GetSettings
    88  	SetSettings
    89  
    90  	GetProfiles
    91  	CreateProfile
    92  	UpdateProfile
    93  	DeleteProfile
    94  	DefaultProfile
    95  
    96  	DriverList
    97  	DriverEvents
    98  	ClientEvents
    99  	CompanyEvents
   100  	DriversEvents
   101  	EventOverlap
   102  	CompanyList
   103  	ClientList
   104  	ClientForCompanyList
   105  	UnsentMessages
   106  	MessageSent
   107  	DisambiguateClients
   108  
   109  	NumClientsForCompany
   110  	NumEventsForCompany
   111  	NumEventsForClient
   112  	NumEventsForDriver
   113  
   114  	CompanyColourFromClient
   115  
   116  	AutocompleteFromAddress
   117  	AutocompleteToAddress
   118  
   119  	AlarmTime
   120  	CalendarData
   121  
   122  	UnassignedCount
   123  	FirstUnassigned
   124  
   125  	GetUsers
   126  	AddUser
   127  	UpdateUser
   128  	RemoveUser
   129  
   130  	TotalStmts
   131  )
   132  
   133  type Calls struct {
   134  	mu         sync.Mutex
   135  	db         *sql.DB
   136  	statements [TotalStmts]*sql.Stmt
   137  }
   138  
   139  func newCalls(dbFName string) (*Calls, error) {
   140  	db, err := sql.Open("sqlite3", dbFName)
   141  	if err != nil {
   142  		return nil, err
   143  	}
   144  
   145  	_, err = db.Exec("PRAGMA foreign_keys = ON;")
   146  	if err != nil {
   147  		return nil, err
   148  	}
   149  
   150  	// Tables
   151  
   152  	for _, ct := range []string{
   153  		"[Driver]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT, [RegistrationNumber] TEXT, [PhoneNumber] TEXT, [Note] TEXT NOT NULL DEFAULT '', [Deleted] BOOLEAN DEFAULT 0 NOT NULL CHECK ([Deleted] IN (0,1)));",
   154  		"[Company]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT, [Address] TEXT, [Note] TEXT NOT NULL DEFAULT '', [Colour] INTEGER, [Deleted] BOOLEAN DEFAULT 0 NOT NULL CHECK ([Deleted] IN (0,1)));",
   155  		"[Client]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [CompanyID] INTEGER, [Name] TEXT, [PhoneNumber] TEXT, [Reference] TEXT, [Note] TEXT NOT NULL DEFAULT '', [Deleted] BOOLEAN DEFAULT 0 NOT NULL CHECK ([Deleted] IN (0,1)));",
   156  		"[Event]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [DriverID] INTEGER, [ClientID] INTEGER, [Start] INTEGER, [End] INTEGER, [From] INTEGER, [To] INTEGER, [InCar] INTEGER DEFAULT 0, [Parking] INTEGER DEFAULT 0, [Waiting] INTEGER DEFAULT 0, [Drop] INTEGER DEFAULT 0, [Miles] INTEGER DEFAULT 0, [Trip] INTEGER DEFAULT 0, [DriverHours] INTEGER DEFAULT 0, [Price] INTEGER DEFAULT 0, [Sub] INTEGER DEFAULT 0, [MessageSent] BOOLEAN DEFAULT 0 NOT NULL CHECK ([MessageSent] IN (0,1)), [Note] TEXT NOT NULL DEFAULT '', [FinalsSet] BOOLEAN DEFAULT 0 NOT NULL, [Created] INTEGER, [Updated] INTEGER, [Deleted] BOOLEAN DEFAULT 0 NOT NULL CHECK ([Deleted] IN (0,1)));",
   157  		"[Settings]([TMUsername] TEXT, [TMPassword] TEXT, [TMTemplate] TEXT, [TMUseNumber] BOOLEAN DEFAULT 0 NOT NULL CHECK ([TMUseNumber] IN (0,1)), [TMFrom] TEXT, [VATPercent] REAL, [AdminPercent] REAL, [Port] INTEGER, [Unassigned] INTEGER, [AlarmTime] INTEGER);",
   158  		"[FromAddresses]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Address] TEXT);",
   159  		"[ToAddresses]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Address] TEXT);",
   160  	} {
   161  		if _, err = db.Exec("CREATE TABLE IF NOT EXISTS " + ct); err != nil {
   162  			return nil, err
   163  		}
   164  	}
   165  
   166  	if err := upgradeDB(db); err != nil {
   167  		return nil, err
   168  	}
   169  
   170  	c := &Calls{
   171  		db: db,
   172  	}
   173  
   174  	for n, ps := range []string{
   175  		// Create
   176  
   177  		"INSERT INTO [Driver]([Name], [RegistrationNumber], [PhoneNumber]) VALUES (?, ?, ?);",
   178  		"INSERT INTO [Company]([Name], [Address], [Colour]) VALUES (?, ?, ?);",
   179  		"INSERT INTO [Client]([CompanyID], [Name], [PhoneNumber], [Reference], [Email], [Address]) VALUES (?, ?, ?, ?, ?, ?);",
   180  		"INSERT INTO [Event]([DriverID], [ClientID], [Start], [End], [From], [To], [Other], [Note], [ClientReference], [Booker], [FlightTime], [Profile], [Created], [Updated]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
   181  		"INSERT INTO [FromAddresses]([Address]) VALUES (?);",
   182  		"INSERT INTO [ToAddresses]([Address]) VALUES (?);",
   183  
   184  		// Read
   185  
   186  		"SELECT [Name], [RegistrationNumber], [PhoneNumber], IFNULL([Pos], 0), [Show] FROM [Driver] WHERE [ID] = ? AND [Deleted] = 0;",
   187  		"SELECT [Name], [Address], [Colour] FROM [Company] WHERE [ID] = ? AND [Deleted] = 0;",
   188  		"SELECT [CompanyID], [Name], [PhoneNumber], [Reference], [Email], [Address] FROM [Client] WHERE [ID] = ? AND [Deleted] = 0;",
   189  		"SELECT [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[InvoiceNote], [Event].[InvoiceFrom], [Event].[InvoiceTo], [Event].[Other], [Event].[ClientReference], [Event].[Booker], [Event].[FlightTime], [Event].[Profile], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[ID] = ? AND [Event].[Deleted] = 0;",
   190  		"SELECT [FinalsSet], [InCar], [Parking], [Waiting], [Drop], [Miles], [Trip], [DriverHours], [Price], [Sub], [InvoiceNote], [InvoiceFrom], [InvoiceTo] FROM [Event] WHERE [ID] = ? AND [Deleted] = 0;",
   191  		"SELECT [ID] FROM [FromAddresses] WHERE [Address] = ?;",
   192  		"SELECT [ID] FROM [ToAddresses] WHERE [Address] = ?;",
   193  
   194  		// Update
   195  
   196  		"UPDATE [Driver] SET [Name] = ?, [RegistrationNumber] = ?, [PhoneNumber] = ? WHERE [ID] = ?;",
   197  		"UPDATE [Company] SET [Name] = ?, [Address] = ?, [Colour] = ? WHERE [ID] = ?;",
   198  		"UPDATE [Client] SET [CompanyID] = ?, [Name] = ?, [PhoneNumber] = ?, [Reference] = ?, [Email] = ?, [Address] = ? WHERE [ID] = ?;",
   199  		"UPDATE [Event] SET [DriverID] = ?, [ClientID] = ?, [Start] = ?, [End] = ?, [From] = ?, [To] = ?, [Other] = ?, [ClientReference] = ?, [Booker] = ?, [FlightTime] = ?, [Profile] = ?, [Updated] = ? WHERE [ID] = ?;",
   200  		"UPDATE [Event] SET [FinalsSet] = 1, [InCar] = ?, [Parking] = ?, [Waiting] = ?, [Drop] = ?, [Miles] = ?, [Trip] = ?, [DriverHours] = ?, [Price] = ?, [Sub] = ?, [InvoiceNote] = ?, [InvoiceFrom] = ?, [InvoiceTo] = ? WHERE [ID] = ?;",
   201  
   202  		// Delete (set deleted)
   203  
   204  		"UPDATE [Driver] SET [Deleted] = 1 WHERE [ID] = ?;",
   205  		"UPDATE [Company] SET [Deleted] = 1 WHERE [ID] = ?;",
   206  		"UPDATE [Client] SET [Deleted] = 1 WHERE [ID] = ?;",
   207  		"UPDATE [Event] SET [Deleted] = 1, [Updated] = ? WHERE [ID] = ?;",
   208  
   209  		"UPDATE [Event] SET [Deleted] = 1, [Updated] = ? WHERE [DriverID] = ?;",
   210  		"UPDATE [Event] SET [Deleted] = 1, [Updated] = ? WHERE [ClientID] = ?;",
   211  		"UPDATE [Client] SET [Deleted] = 1 WHERE [CompanyID] = ?;",
   212  		"UPDATE [Event] SET [Deleted] = 1, [Updated] = ? WHERE [ClientID] IN (SELECT [ID] FROM [Client] WHERE [CompanyID] = ?);",
   213  
   214  		// Get Notes
   215  
   216  		"SELECT [Note] FROM [Driver] WHERE [ID] = ?;",
   217  		"SELECT [Note] FROM [Company] WHERE [ID] = ?;",
   218  		"SELECT [Note] FROM [Client] WHERE [ID] = ?;",
   219  		"SELECT [Note] FROM [Event] WHERE [ID] = ?;",
   220  
   221  		// Set Notes
   222  
   223  		"UPDATE [Driver] SET [Note] = ? WHERE [ID] = ?;",
   224  		"UPDATE [Company] SET [Note] = ? WHERE [ID] = ?;",
   225  		"UPDATE [Client] SET [Note] = ? WHERE [ID] = ?;",
   226  		"UPDATE [Event] SET [Note] = ? WHERE [ID] = ?;",
   227  
   228  		// Set Driver Show/Pos
   229  
   230  		"UPDATE [Driver] SET [Show] = ?, [Pos] = ? WHERE [ID] = ?;",
   231  
   232  		// Settings
   233  
   234  		"SELECT [TMUsername], [TMPassword], [TMTemplate], [TMUseNumber], [TMFrom], [Port], [Unassigned], [AlarmTime], [EmailSMTP], [EmailUsername], [EmailPassword], [EmailTemplate], [DefaultProfile] FROM [Settings];",
   235  		"UPDATE [Settings] SET [TMUsername] = ?, [TMPassword] = ?, [TMTemplate] = ?, [TMUseNumber] = ?, [TMFrom] = ?, [Port] = ?, [Unassigned] = ?, [AlarmTime] = ?, [EmailSMTP] = ?, [EmailUsername] = ?, [EmailPassword] = ?, [EmailTemplate] = ?, [DefaultProfile] = ?;",
   236  
   237  		// Profiles
   238  
   239  		"SELECT [ID], [Name], [VATPercent], [AdminPercent], [InvoiceHeader], [InvoiceFooter] FROM [Profiles] ORDER BY [ID] ASC;",
   240  		"INSERT INTO [Profiles]([Name], [VATPercent], [AdminPercent], [InvoiceHeader], [InvoiceFooter]) VALUES (?, ?, ?, ?, ?);",
   241  		"UPDATE [Profiles] SET [Name] = ?, [VATPercent] = ?, [AdminPercent] = ?, [InvoiceHeader] = ?, [InvoiceFooter] = ? WHERE [ID] = ?;",
   242  		"DELETE FROM [Profiles] WHERE [ID] = ?;",
   243  		"UPDATE [Event] SET [Profile] = 0 WHERE Profile = ?;",
   244  
   245  		// Searches
   246  
   247  		// All Drivers
   248  		"SELECT [ID], [Name], [RegistrationNumber], [PhoneNumber], IFNULL([Pos], 0), [Show] FROM [Driver] WHERE [Deleted] = 0 ORDER BY [ID] ASC;",
   249  
   250  		// Row of Events for driver
   251  		"SELECT [Event].[ID], [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[Other], [Event].[ClientReference], [Event].[Booker], [Event].[FlightTime], [Event].[Profile], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[DriverID] = ? AND [Event].[Deleted] = 0 AND [Event].[Start] >= ? AND [Event].[Start] < ? AND ? IN(-1, [Event].[Profile]) ORDER BY [Event].[Start] ASC;",
   252  
   253  		// Row of Events for client
   254  		"SELECT [Event].[ID], [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[Other], [Event].[InvoiceTo], [Event].[InvoiceFrom], [Event].[InvoiceNote], [Event].[Profile], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[ClientID] = ? AND [Event].[Deleted] = 0 AND [Event].[Start] >= ? AND [Event].[Start] < ? AND ? IN(-1, [Event].[Profile]) ORDER BY [Event].[Start] ASC;",
   255  
   256  		// Row of Events for company
   257  		"SELECT [Event].[ID], [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[Other], [Event].[ClientReference], [Event].[InvoiceTo], [Event].[InvoiceFrom], [Event].[InvoiceNote], [Event].[Profile], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[ClientID] IN (SELECT [ID] FROM [Client] WHERE [CompanyID] = ?) AND [Event].[Deleted] = 0 AND [Event].[Start] >= ? AND [Event].[Start] < ? AND ? IN(-1, [Event].[Profile]) ORDER BY [Event].[Start] ASC;",
   258  
   259  		// Row of Events for drivers
   260  		"SELECT [Event].[ID], [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[Other], [Event].[ClientReference], [Event].[InvoiceTo], [Event].[InvoiceFrom], [Event].[InvoiceNote], [Event].[Profile], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[DriverID] = ? AND [Event].[Deleted] = 0 AND [Event].[Start] >= ? AND [Event].[Start] < ? AND ? IN(-1, [Event].[Profile]) ORDER BY [Event].[Start] ASC;",
   261  
   262  		// Event Overlaps
   263  		"SELECT COUNT(1) FROM [Event] WHERE [ID] != ? AND [Deleted] = 0 AND [DriverID] = ? AND MAX([Start], ?) < MIN([End], ?);",
   264  
   265  		// Company List
   266  		"SELECT [ID], [Name], [Address], [Colour] FROM [Company] WHERE [Deleted] = 0 ORDER BY [Name] ASC;",
   267  
   268  		// Client List
   269  		"SELECT [ID], [CompanyID], [Name], [PhoneNumber], [Reference], [Email], [Address] FROM [Client] WHERE [Deleted] = 0 ORDER BY [Name] ASC;",
   270  
   271  		// Clients for company
   272  		"SELECT [ID], [CompanyID], [Name], [PhoneNumber], [Reference] FROM [Client] WHERE [CompanyID] = ? AND [Deleted] = 0 ORDER BY [Name] ASC;",
   273  
   274  		// Events with unsent messages
   275  		"SELECT [Event].[ID], [Event].[DriverID], [Event].[ClientID], [Event].[Start], [Event].[End], [Event].[MessageSent], [FromAddresses].[Address], [ToAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[Start] > ? AND [Event].[DriverID] > 0 AND [Event].[Deleted] = 0 ORDER BY [Event].Start ASC;",
   276  
   277  		// Mark message as sent
   278  		"UPDATE [Event] SET [MessageSent] = 1 WHERE [ID] = ?;",
   279  
   280  		// Disambiguate clients
   281  		"SELECT [Company].[Name], [Client].[Reference] FROM [Client] JOIN [Company] ON [Client].[CompanyID] = [Company].[ID] WHERE [Client].[ID] = ?;",
   282  
   283  		// Num Clients for company
   284  		"SELECT COUNT(1) FROM [Client] WHERE [CompanyID] = ? AND [Deleted] = 0;",
   285  
   286  		// Num Events for company
   287  		"SELECT COUNT(1) FROM [Event] JOIN [Client] ON [Event].[ClientID] = [Client].[ID] WHERE [Client].[CompanyID] = ? AND [Client].[Deleted] = 0 AND [Event].[Deleted] = 0;",
   288  
   289  		// Num Events for client
   290  		"SELECT COUNT(1) FROM [Event] WHERE [ClientID] = ? AND [Deleted] = 0;",
   291  
   292  		// Num Events for driver
   293  		"SELECT COUNT(1) FROM [Event] WHERE [DriverID] = ? AND [Deleted] = 0;",
   294  
   295  		// Company Colour from ClientID
   296  		"SELECT [Company].[Colour] FROM [Client] LEFT JOIN [Company] ON ([Client].[CompanyID] = [Company].[ID]) WHERE [Client].[ID] = ?;",
   297  
   298  		// Autocomplete From Address
   299  		"SELECT [FromAddresses].[Address] FROM [Event] LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) WHERE [Event].[ClientID] = ? GROUP BY [Event].[From] ORDER BY COUNT(1) DESC LIMIT ?;",
   300  
   301  		// Autocomplete To Address
   302  		"SELECT [ToAddresses].[Address] FROM [Event] LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[ClientID] = ? GROUP BY [Event].[From] ORDER BY COUNT(1) DESC LIMIT ?;",
   303  
   304  		// Alarm Time Setting
   305  		"SELECT [AlarmTime] FROM [Settings];",
   306  
   307  		// All event data for calendar output
   308  		"SELECT [Event].[ID], [Event].[Start], [Event].[End], [FromAddresses].[Address], [ToAddresses].[Address], [Event].[Created], [Event].[Updated], [Event].[Note], [Event].[Other], [Driver].[Name], [Client].[Name], [Company].[Name], [Client].[PhoneNumber], [Event].[FlightTime] FROM [Event] LEFT JOIN [Driver] ON ([Driver].[ID] = [Event].[DriverID]) LEFT JOIN [Client] ON ([Client].ID = [Event].[ClientID]) LEFT JOIN [Company] ON ([Company].ID = [Client].[CompanyID]) LEFT JOIN [FromAddresses] ON ([FromAddresses].[ID] = [Event].[From]) LEFT JOIN [ToAddresses] ON ([ToAddresses].[ID] = [Event].[To]) WHERE [Event].[Start] > ? AND [Event].[Deleted] = 0;",
   309  
   310  		// Unassigned events
   311  		"SELECT COUNT(1) FROM [Event] WHERE [DriverID] = 0 AND [Deleted] = 0;",
   312  
   313  		"SELECT [Start] FROM [Event] WHERE [DriverID] = 0 AND [Deleted] = 0 ORDER BY [Start] ASC LIMIT 1;",
   314  
   315  		// Users
   316  		"SELECT [Username], [Password] FROM [Users];",
   317  		"INSERT INTO [Users]([Username], [Password]) VALUES (?, ?);",
   318  		"UPDATE [Users] SET [Password] = ? WHERE [Username] = ?;",
   319  		"DELETE FROM [Users] WHERE [Username] = ?;",
   320  	} {
   321  		stmt, err := db.Prepare(ps)
   322  		if err != nil {
   323  			return nil, errors.New(err.Error() + "\n" + ps)
   324  		}
   325  		c.statements[n] = stmt
   326  	}
   327  
   328  	count := 0
   329  	err = db.QueryRow("SELECT COUNT(1) FROM [Settings];").Scan(&count)
   330  	if err != nil {
   331  		return nil, err
   332  	} else if count == 0 {
   333  		_, err = db.Exec("INSERT INTO [Settings] ([TMUsername], [TMPassword], [TMTemplate], [TMUseNumber], [TMFrom], [VATPercent], [AdminPercent], [Port], [Unassigned], [AlarmTime]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", "username", "password", DefaultTemplate, 1, "Academy Chauffeurs", 20, 10, 8080, 7, -15)
   334  		if err != nil {
   335  			return nil, err
   336  		}
   337  		setMessageVars("username", "password", DefaultTemplate, "Academy Chauffeurs", true)
   338  		setEmailVars("server", "username", "password", DefaultTemplate)
   339  	} else {
   340  		var (
   341  			username, password, text, from                           string
   342  			useNumber                                                bool
   343  			emailServer, emailUsername, emailPassword, emailTemplete string
   344  		)
   345  		err := db.QueryRow("SELECT [TMUsername], [TMPassword], [TMTemplate], [TMUseNumber], [TMFrom], [EmailSMTP], [EmailUsername], [EmailPassword], [EmailTemplate] FROM [Settings];").Scan(&username, &password, &text, &useNumber, &from, &emailServer, &emailUsername, &emailPassword, &emailTemplete)
   346  		if err != nil {
   347  			return nil, err
   348  		}
   349  		setMessageVars(username, password, text, from, useNumber)
   350  		setEmailVars(emailServer, emailUsername, emailPassword, emailTemplete)
   351  	}
   352  	users, err := c.statements[GetUsers].Query()
   353  	if err != nil {
   354  		return nil, err
   355  	}
   356  	for users.Next() {
   357  		var (
   358  			username string
   359  			password [sha1.Size]byte
   360  			pwd      []byte
   361  		)
   362  		if err = users.Scan(&username, &pwd); err != nil {
   363  			return nil, err
   364  		}
   365  		copy(password[:], pwd)
   366  		authMap.Set(username, password)
   367  	}
   368  	if err = users.Close(); err != nil {
   369  		return nil, err
   370  	}
   371  
   372  	err = rpc.Register(c)
   373  	if err != nil {
   374  		return nil, err
   375  	}
   376  	return c, nil
   377  }
   378  
   379  func (c *Calls) close() {
   380  	c.db.Close()
   381  }
   382  
   383  func (c *Calls) NumClients(id int64, num *int64) error {
   384  	c.mu.Lock()
   385  	defer c.mu.Unlock()
   386  	return c.statements[NumClientsForCompany].QueryRow(id).Scan(num)
   387  }
   388  
   389  func (c *Calls) NumClientsForCompanies(ids []int64, total *int64) error {
   390  	c.mu.Lock()
   391  	defer c.mu.Unlock()
   392  	for _, id := range ids {
   393  		var num int64
   394  		err := c.statements[NumClientsForCompany].QueryRow(id).Scan(&num)
   395  		if err != nil {
   396  			return err
   397  		}
   398  		*total += num
   399  	}
   400  	return nil
   401  }
   402  
   403  func (c *Calls) NumEvents(id int64, num *int64) error {
   404  	c.mu.Lock()
   405  	defer c.mu.Unlock()
   406  	return c.statements[NumEventsForCompany].QueryRow(id).Scan(num)
   407  }
   408  
   409  func (c *Calls) NumEventsForCompanies(ids []int64, total *int64) error {
   410  	c.mu.Lock()
   411  	defer c.mu.Unlock()
   412  	for _, id := range ids {
   413  		var num int64
   414  		err := c.statements[NumEventsForCompany].QueryRow(id).Scan(&num)
   415  		if err != nil {
   416  			return err
   417  		}
   418  		*total += num
   419  	}
   420  	return nil
   421  }
   422  
   423  func (c *Calls) NumEventsClient(id int64, num *int64) error {
   424  	c.mu.Lock()
   425  	defer c.mu.Unlock()
   426  	return c.statements[NumEventsForClient].QueryRow(id).Scan(num)
   427  }
   428  
   429  func (c *Calls) NumEventsDriver(id int64, num *int64) error {
   430  	c.mu.Lock()
   431  	defer c.mu.Unlock()
   432  	return c.statements[NumEventsForDriver].QueryRow(id).Scan(num)
   433  }
   434  
   435  func (c *Calls) GetDriverNote(id int64, note *string) error {
   436  	c.mu.Lock()
   437  	defer c.mu.Unlock()
   438  	return c.statements[GetDriverNote].QueryRow(id).Scan(note)
   439  }
   440  
   441  func (c *Calls) GetCompanyNote(id int64, note *string) error {
   442  	c.mu.Lock()
   443  	defer c.mu.Unlock()
   444  	return c.statements[GetCompanyNote].QueryRow(id).Scan(note)
   445  }
   446  
   447  func (c *Calls) GetClientNote(id int64, note *string) error {
   448  	c.mu.Lock()
   449  	defer c.mu.Unlock()
   450  	return c.statements[GetClientNote].QueryRow(id).Scan(note)
   451  }
   452  
   453  func (c *Calls) GetEventNote(id int64, note *string) error {
   454  	c.mu.Lock()
   455  	defer c.mu.Unlock()
   456  	return c.statements[GetEventNote].QueryRow(id).Scan(note)
   457  }
   458  
   459  type NoteID struct {
   460  	ID   int64
   461  	Note string
   462  }
   463  
   464  func (c *Calls) SetDriverNote(nid NoteID, _ *struct{}) error {
   465  	c.mu.Lock()
   466  	defer c.mu.Unlock()
   467  	_, err := c.statements[SetDriverNote].Exec(nid.Note, nid.ID)
   468  	return err
   469  }
   470  
   471  func (c *Calls) SetCompanyNote(nid NoteID, _ *struct{}) error {
   472  	c.mu.Lock()
   473  	defer c.mu.Unlock()
   474  	_, err := c.statements[SetCompanyNote].Exec(nid.Note, nid.ID)
   475  	return err
   476  }
   477  
   478  func (c *Calls) SetClientNote(nid NoteID, _ *struct{}) error {
   479  	c.mu.Lock()
   480  	defer c.mu.Unlock()
   481  	_, err := c.statements[SetClientNote].Exec(nid.Note, nid.ID)
   482  	return err
   483  }
   484  
   485  func (c *Calls) SetEventNote(nid NoteID, _ *struct{}) error {
   486  	c.mu.Lock()
   487  	defer c.mu.Unlock()
   488  	_, err := c.statements[SetEventNote].Exec(nid.Note, nid.ID)
   489  	return err
   490  }
   491  
   492  type is []interface{}
   493  
   494  func (c *Calls) getList(sqlStmt int, params is, get func() is) error {
   495  	c.mu.Lock()
   496  	defer c.mu.Unlock()
   497  	rows, err := c.statements[sqlStmt].Query(params...)
   498  	if err != nil {
   499  		return err
   500  	}
   501  	for rows.Next() {
   502  		if err = rows.Scan(get()...); err != nil {
   503  			return err
   504  		}
   505  	}
   506  	return rows.Err()
   507  }
   508  
   509  type EventsFilter struct {
   510  	ID      int64 `form:"id,post"`
   511  	Start   int64 `form:"startTime,post"`
   512  	End     int64 `form:"endTime,post"`
   513  	Profile int64 `form:"profile,post"`
   514  }
   515  
   516  func (c *Calls) DriverEvents(f EventsFilter, events *[]Event) error {
   517  	*events = make([]Event, 0)
   518  	return c.getList(DriverEvents, is{f.ID, f.Start, f.End, f.Profile}, func() is {
   519  		pos := len(*events)
   520  		*events = append(*events, Event{})
   521  		return is{
   522  			&(*events)[pos].ID,
   523  			&(*events)[pos].DriverID,
   524  			&(*events)[pos].ClientID,
   525  			&(*events)[pos].Start,
   526  			&(*events)[pos].End,
   527  			&(*events)[pos].Other,
   528  			&(*events)[pos].ClientReference,
   529  			&(*events)[pos].Booker,
   530  			&(*events)[pos].FlightTime,
   531  			&(*events)[pos].Profile,
   532  			&(*events)[pos].From,
   533  			&(*events)[pos].To,
   534  		}
   535  	})
   536  }
   537  
   538  func (c *Calls) ClientEvents(f EventsFilter, events *[]Event) error {
   539  	*events = make([]Event, 0)
   540  	return c.getList(ClientEvents, is{f.ID, f.Start, f.End, f.Profile}, func() is {
   541  		pos := len(*events)
   542  		*events = append(*events, Event{})
   543  		return is{
   544  			&(*events)[pos].ID,
   545  			&(*events)[pos].DriverID,
   546  			&(*events)[pos].ClientID,
   547  			&(*events)[pos].Start,
   548  			&(*events)[pos].End,
   549  			&(*events)[pos].Other,
   550  			&(*events)[pos].InvoiceTo,
   551  			&(*events)[pos].InvoiceFrom,
   552  			&(*events)[pos].InvoiceNote,
   553  			&(*events)[pos].Profile,
   554  			&(*events)[pos].From,
   555  			&(*events)[pos].To,
   556  		}
   557  	})
   558  }
   559  
   560  func (c *Calls) CompanyEvents(f EventsFilter, events *[]Event) error {
   561  	*events = make([]Event, 0)
   562  	return c.getList(CompanyEvents, is{f.ID, f.Start, f.End, f.Profile}, func() is {
   563  		pos := len(*events)
   564  		*events = append(*events, Event{})
   565  		return is{
   566  			&(*events)[pos].ID,
   567  			&(*events)[pos].DriverID,
   568  			&(*events)[pos].ClientID,
   569  			&(*events)[pos].Start,
   570  			&(*events)[pos].End,
   571  			&(*events)[pos].Other,
   572  			&(*events)[pos].ClientReference,
   573  			&(*events)[pos].InvoiceTo,
   574  			&(*events)[pos].InvoiceFrom,
   575  			&(*events)[pos].InvoiceNote,
   576  			&(*events)[pos].Profile,
   577  			&(*events)[pos].From,
   578  			&(*events)[pos].To,
   579  		}
   580  	})
   581  }
   582  
   583  type CEventsFilter struct {
   584  	IDs     []int64 `form:"id,post"`
   585  	Start   int64   `form:"startTime,post"`
   586  	End     int64   `form:"endTime,post"`
   587  	Profile int64   `form:"profile,post"`
   588  }
   589  
   590  type sortEvents []Event
   591  
   592  func (s sortEvents) Len() int {
   593  	return len(s)
   594  }
   595  
   596  func (s sortEvents) Less(i, j int) bool {
   597  	return s[i].Start < s[j].Start
   598  }
   599  
   600  func (s sortEvents) Swap(i, j int) {
   601  	s[i], s[j] = s[j], s[i]
   602  }
   603  
   604  func (c *Calls) DriversEvents(f CEventsFilter, events *[]Event) error {
   605  	return c.driverCompanyEvents(DriversEvents, f, events)
   606  }
   607  
   608  func (c *Calls) CompaniesEvents(f CEventsFilter, events *[]Event) error {
   609  	return c.driverCompanyEvents(CompanyEvents, f, events)
   610  }
   611  
   612  func (c *Calls) driverCompanyEvents(stmtID int, f CEventsFilter, events *[]Event) error {
   613  	*events = make([]Event, 0)
   614  	for _, id := range f.IDs {
   615  		err := c.getList(stmtID, is{id, f.Start, f.End, f.Profile}, func() is {
   616  			pos := len(*events)
   617  			*events = append(*events, Event{})
   618  			return is{
   619  				&(*events)[pos].ID,
   620  				&(*events)[pos].DriverID,
   621  				&(*events)[pos].ClientID,
   622  				&(*events)[pos].Start,
   623  				&(*events)[pos].End,
   624  				&(*events)[pos].Other,
   625  				&(*events)[pos].ClientReference,
   626  				&(*events)[pos].InvoiceTo,
   627  				&(*events)[pos].InvoiceFrom,
   628  				&(*events)[pos].InvoiceNote,
   629  				&(*events)[pos].Profile,
   630  				&(*events)[pos].From,
   631  				&(*events)[pos].To,
   632  			}
   633  		})
   634  		if err != nil {
   635  			return err
   636  		}
   637  	}
   638  	sort.Sort(sortEvents(*events))
   639  	return nil
   640  }
   641  
   642  func (c *Calls) Drivers(_ struct{}, drivers *[]Driver) error {
   643  	*drivers = make([]Driver, 0)
   644  	return c.getList(DriverList, is{}, func() is {
   645  		pos := len(*drivers)
   646  		*drivers = append(*drivers, Driver{})
   647  		return is{
   648  			&(*drivers)[pos].ID,
   649  			&(*drivers)[pos].Name,
   650  			&(*drivers)[pos].RegistrationNumber,
   651  			&(*drivers)[pos].PhoneNumber,
   652  			&(*drivers)[pos].Pos,
   653  			&(*drivers)[pos].Show,
   654  		}
   655  	})
   656  }
   657  
   658  func (c *Calls) Companies(_ struct{}, companies *[]Company) error {
   659  	*companies = make([]Company, 0)
   660  	return c.getList(CompanyList, is{}, func() is {
   661  		pos := len(*companies)
   662  		*companies = append(*companies, Company{})
   663  		return is{
   664  			&(*companies)[pos].ID,
   665  			&(*companies)[pos].Name,
   666  			&(*companies)[pos].Address,
   667  			&(*companies)[pos].Colour,
   668  		}
   669  	})
   670  }
   671  
   672  func (c *Calls) Clients(_ struct{}, clients *[]Client) error {
   673  	*clients = make([]Client, 0)
   674  	return c.getList(ClientList, is{}, func() is {
   675  		pos := len(*clients)
   676  		*clients = append(*clients, Client{})
   677  		return is{
   678  			&(*clients)[pos].ID,
   679  			&(*clients)[pos].CompanyID,
   680  			&(*clients)[pos].Name,
   681  			&(*clients)[pos].PhoneNumber,
   682  			&(*clients)[pos].Reference,
   683  			&(*clients)[pos].Email,
   684  			&(*clients)[pos].Address,
   685  		}
   686  	})
   687  }
   688  
   689  func (c *Calls) ClientsForCompany(companyID int64, clients *[]Client) error {
   690  	*clients = make([]Client, 0)
   691  	return c.getList(ClientForCompanyList, is{companyID}, func() is {
   692  		pos := len(*clients)
   693  		*clients = append(*clients, Client{})
   694  		return is{
   695  			&(*clients)[pos].ID,
   696  			&(*clients)[pos].CompanyID,
   697  			&(*clients)[pos].Name,
   698  			&(*clients)[pos].PhoneNumber,
   699  			&(*clients)[pos].Reference,
   700  		}
   701  	})
   702  }
   703  
   704  type sortClients []Client
   705  
   706  func (s sortClients) Len() int {
   707  	return len(s)
   708  }
   709  
   710  func (s sortClients) Less(i, j int) bool {
   711  	return s[i].Name < s[j].Name
   712  }
   713  
   714  func (s sortClients) Swap(i, j int) {
   715  	s[i], s[j] = s[j], s[i]
   716  }
   717  
   718  func (c *Calls) ClientsForCompanies(ids []int64, clients *[]Client) error {
   719  	*clients = make([]Client, 0)
   720  	for _, companyID := range ids {
   721  		err := c.getList(ClientForCompanyList, is{companyID}, func() is {
   722  			pos := len(*clients)
   723  			*clients = append(*clients, Client{})
   724  			return is{
   725  				&(*clients)[pos].ID,
   726  				&(*clients)[pos].CompanyID,
   727  				&(*clients)[pos].Name,
   728  				&(*clients)[pos].PhoneNumber,
   729  				&(*clients)[pos].Reference,
   730  			}
   731  		})
   732  		if err != nil {
   733  			return err
   734  		}
   735  	}
   736  	sort.Sort(sortClients(*clients))
   737  	return nil
   738  }
   739  
   740  func (c *Calls) UnsentMessages(_ struct{}, events *[]Event) error {
   741  	*events = make([]Event, 0)
   742  	return c.getList(UnsentMessages, is{time.Now().Unix() * 1000}, func() is {
   743  		var (
   744  			e   Event
   745  			pos = len(*events)
   746  		)
   747  		*events = append(*events, e)
   748  		return is{
   749  			&(*events)[pos].ID,
   750  			&(*events)[pos].DriverID,
   751  			&(*events)[pos].ClientID,
   752  			&(*events)[pos].Start,
   753  			&(*events)[pos].End,
   754  			&(*events)[pos].MessageSent,
   755  			&(*events)[pos].From,
   756  			&(*events)[pos].To,
   757  		}
   758  	})
   759  }
   760  
   761  type AutocompleteValue struct {
   762  	ID                    int64
   763  	Value, Disambiguation string
   764  }
   765  
   766  func makeAutocompleteSQL(column, table string, includeDeleted bool, notIDs []int64) string {
   767  	sql := "SELECT [ID], [" + column + "] FROM [" + table + "] WHERE [" + column + "] LIKE ?"
   768  	if !includeDeleted {
   769  		sql += " AND [Deleted] = 0"
   770  	}
   771  	if len(notIDs) > 0 {
   772  		sql += " AND [ID] NOT IN ("
   773  		for n := range notIDs {
   774  			if n > 0 {
   775  				sql += ", "
   776  			}
   777  			sql += "?"
   778  		}
   779  		sql += ")"
   780  	}
   781  	return sql + " LIMIT ?;"
   782  }
   783  
   784  const MAXRETURN = 10
   785  
   786  func (c *Calls) autocomplete(vals *[]AutocompleteValue, column, table, partial string, includeDeleted bool, notIDs ...int64) error {
   787  	if len(*vals) >= MAXRETURN {
   788  		return nil
   789  	}
   790  	c.mu.Lock()
   791  	defer c.mu.Unlock()
   792  	stmt, err := c.db.Prepare(makeAutocompleteSQL(column, table, includeDeleted, notIDs))
   793  	if err != nil {
   794  		return err
   795  	}
   796  	params := make([]interface{}, 1, len(notIDs)+2)
   797  	params[0] = partial
   798  	for _, n := range notIDs {
   799  		params = append(params, n)
   800  	}
   801  	params = append(params, MAXRETURN-len(notIDs))
   802  	rows, err := stmt.Query(params...)
   803  	if err != nil {
   804  		return err
   805  	}
   806  	for rows.Next() {
   807  		var a AutocompleteValue
   808  		err := rows.Scan(&a.ID, &a.Value)
   809  		if err != nil {
   810  			return err
   811  		}
   812  		*vals = append(*vals, a)
   813  	}
   814  	if err := rows.Err(); err != nil {
   815  		return err
   816  	}
   817  	return stmt.Close()
   818  }
   819  
   820  func (c *Calls) AutocompleteCompanyName(partial string, vals *[]AutocompleteValue) error {
   821  	*vals = make([]AutocompleteValue, 0, MAXRETURN)
   822  	err := c.autocomplete(vals, "Name", "Company", partial+"%", false)
   823  	if err != nil || len(*vals) >= MAXRETURN {
   824  		return err
   825  	}
   826  	notIDs := make([]int64, 0, MAXRETURN)
   827  	for _, v := range *vals {
   828  		notIDs = append(notIDs, v.ID)
   829  	}
   830  	return c.autocomplete(vals, "Name", "Company", "%"+partial+"%", false, notIDs...)
   831  }
   832  
   833  func (c *Calls) AutocompleteClientName(partial string, vals *[]AutocompleteValue) error {
   834  	*vals = make([]AutocompleteValue, 0, MAXRETURN)
   835  	err := c.autocomplete(vals, "Name", "Client", partial+"%", false)
   836  	if err != nil || len(*vals) >= MAXRETURN {
   837  		return err
   838  	}
   839  	notIDs := make([]int64, 0, MAXRETURN)
   840  	for _, v := range *vals {
   841  		notIDs = append(notIDs, v.ID)
   842  	}
   843  	err = c.autocomplete(vals, "Name", "Client", "%"+partial+"%", false, notIDs...)
   844  	if err != nil {
   845  		return err
   846  	}
   847  	for n := range *vals {
   848  		var cName, ref string
   849  		err := c.statements[DisambiguateClients].QueryRow((*vals)[n].ID).Scan(&cName, &ref)
   850  		if err != nil {
   851  			return err
   852  		}
   853  		(*vals)[n].Disambiguation = cName + " (" + ref + ")"
   854  	}
   855  	return nil
   856  }
   857  
   858  func filterDupes(vals *[]AutocompleteValue) {
   859  	filtered := make([]AutocompleteValue, 0, cap(*vals))
   860  Loop:
   861  	for i, val := range *vals {
   862  		thisVal := strings.ToLower(val.Value)
   863  		for j := 0; j < i; j++ {
   864  			if thisVal == strings.ToLower((*vals)[j].Value) {
   865  				continue Loop
   866  			}
   867  		}
   868  		filtered = append(filtered, val)
   869  	}
   870  	*vals = filtered
   871  }
   872  
   873  type AutocompleteAddressRequest struct {
   874  	ClientID int64
   875  	Priority int
   876  	Partial  string
   877  }
   878  
   879  func (c *Calls) AutocompleteAddress(req AutocompleteAddressRequest, vals *[]AutocompleteValue) error {
   880  	*vals = make([]AutocompleteValue, 0, MAXRETURN)
   881  	var first, second string
   882  	if req.Priority == 0 {
   883  		first = "From"
   884  		second = "To"
   885  	} else {
   886  		first = "To"
   887  		second = "From"
   888  	}
   889  	if req.Partial == "" {
   890  		if req.ClientID > 0 {
   891  			var stmt int
   892  			if req.Priority == 0 {
   893  				stmt = AutocompleteFromAddress
   894  			} else {
   895  				stmt = AutocompleteToAddress
   896  			}
   897  			rows, err := c.statements[stmt].Query(req.ClientID, MAXRETURN)
   898  			if err != nil {
   899  				return err
   900  			}
   901  			for rows.Next() {
   902  				var address string
   903  				err = rows.Scan(&address)
   904  				if err != nil {
   905  					return err
   906  				}
   907  				*vals = append(*vals, AutocompleteValue{Value: address})
   908  			}
   909  		}
   910  		return nil
   911  	}
   912  	err := c.autocomplete(vals, "Address", first+"Addresses", req.Partial+"%", true)
   913  	if err != nil || len(*vals) >= MAXRETURN {
   914  		return err
   915  	}
   916  	notIDsOne := make([]int64, 0, MAXRETURN)
   917  	for _, v := range *vals {
   918  		notIDsOne = append(notIDsOne, v.ID)
   919  	}
   920  	filterDupes(vals)
   921  	preLen := len(*vals)
   922  	err = c.autocomplete(vals, "Address", second+"Addresses", req.Partial+"%", true)
   923  	filterDupes(vals)
   924  	if err != nil || len(*vals) >= MAXRETURN {
   925  		return err
   926  	}
   927  	notIDsTwo := make([]int64, 0, MAXRETURN)
   928  	for _, v := range (*vals)[preLen:] {
   929  		notIDsTwo = append(notIDsTwo, v.ID)
   930  	}
   931  	err = c.autocomplete(vals, "Address", first+"Addresses", "%"+req.Partial+"%", true, notIDsOne...)
   932  	filterDupes(vals)
   933  	if err != nil || len(*vals) >= MAXRETURN {
   934  		return err
   935  	}
   936  	err = c.autocomplete(vals, "Address", second+"Addresses", "%"+req.Partial+"%", true, notIDsTwo...)
   937  	filterDupes(vals)
   938  	if err != nil || len(*vals) >= MAXRETURN {
   939  		return err
   940  	}
   941  	return err
   942  }
   943  
   944  type EventFinals struct {
   945  	ID, InCar, Parking, Waiting, Drop, Miles, Trip, DriverHours, Price, Sub int64
   946  	InvoiceNote, InvoiceFrom, InvoiceTo                                     string
   947  	FinalsSet                                                               bool
   948  }
   949  
   950  func (c *Calls) GetEventFinals(id int64, e *EventFinals) error {
   951  	c.mu.Lock()
   952  	err := c.statements[ReadEventFinals].QueryRow(id).Scan(&e.FinalsSet, &e.InCar, &e.Parking, &e.Waiting, &e.Drop, &e.Miles, &e.Trip, &e.DriverHours, &e.Price, &e.Sub, &e.InvoiceNote, &e.InvoiceFrom, &e.InvoiceTo)
   953  	c.mu.Unlock()
   954  	return err
   955  }
   956  
   957  func (c *Calls) SetEventFinals(e EventFinals, _ *struct{}) error {
   958  	c.mu.Lock()
   959  	_, err := c.statements[UpdateEventFinals].Exec(e.InCar, e.Parking, e.Waiting, e.Drop, e.Miles, e.Trip, e.DriverHours, e.Price, e.Sub, e.InvoiceNote, e.InvoiceFrom, e.InvoiceTo, e.ID)
   960  	c.mu.Unlock()
   961  	return err
   962  }
   963  
   964  type Settings struct {
   965  	Port, Unassigned                                       uint16
   966  	TMUseNumber                                            bool
   967  	TMUsername, TMPassword, TMTemplate, TMFrom             string
   968  	UploadCalendar                                         bool
   969  	AlarmTime                                              int
   970  	EmailSMTP, EmailUsername, EmailPassword, EmailTemplate string
   971  	DefaultProfile                                         uint64
   972  	Profiles
   973  }
   974  
   975  func (c *Calls) GetSettings(_ struct{}, s *Settings) error {
   976  	c.mu.Lock()
   977  	err := c.statements[GetSettings].QueryRow().Scan(&s.TMUsername, &s.TMPassword, &s.TMTemplate, &s.TMUseNumber, &s.TMFrom, &s.Port, &s.Unassigned, &s.AlarmTime, &s.EmailSMTP, &s.EmailUsername, &s.EmailPassword, &s.EmailTemplate, &s.DefaultProfile)
   978  	c.mu.Unlock()
   979  	if err != nil {
   980  		return err
   981  	}
   982  	return c.GetProfiles(struct{}{}, &s.Profiles)
   983  }
   984  
   985  func (c *Calls) SetSettings(s Settings, errStr *string) error {
   986  	if err := setMessageVars(s.TMUsername, s.TMPassword, s.TMTemplate, s.TMFrom, s.TMUseNumber); err != nil {
   987  		*errStr = err.Error()
   988  		return nil
   989  	}
   990  	if err := setEmailVars(s.EmailSMTP, s.EmailUsername, s.EmailPassword, s.EmailTemplate); err != nil {
   991  		*errStr = err.Error()
   992  	}
   993  	_, err := c.statements[SetSettings].Exec(s.TMUsername, s.TMPassword, s.TMTemplate, s.TMUseNumber, s.TMFrom, s.Port, s.Unassigned, s.AlarmTime, s.EmailSMTP, s.EmailUsername, s.EmailPassword, s.EmailTemplate, s.DefaultProfile)
   994  	return err
   995  }
   996  
   997  type Profiles []Profile
   998  
   999  type Profile struct {
  1000  	ID                           int64
  1001  	Name                         string
  1002  	VATPercent, AdminPercent     float64
  1003  	InvoiceHeader, InvoiceFooter string
  1004  }
  1005  
  1006  func (c *Calls) GetProfiles(_ struct{}, ps *Profiles) error {
  1007  	c.mu.Lock()
  1008  	defer c.mu.Unlock()
  1009  	rows, err := c.statements[GetProfiles].Query()
  1010  	if err != nil {
  1011  		return err
  1012  	}
  1013  	for rows.Next() {
  1014  		var p Profile
  1015  		if err := rows.Scan(&p.ID, &p.Name, &p.VATPercent, &p.AdminPercent, &p.InvoiceHeader, &p.InvoiceFooter); err != nil {
  1016  			return err
  1017  		}
  1018  		*ps = append(*ps, p)
  1019  	}
  1020  	return rows.Close()
  1021  }
  1022  
  1023  func (c *Calls) SetProfile(p Profile, newID *int64) error {
  1024  	if p.ID < 0 {
  1025  		c.mu.Lock()
  1026  		res, err := c.statements[CreateProfile].Exec(p.Name, p.VATPercent, p.AdminPercent, p.InvoiceHeader, p.InvoiceFooter)
  1027  		if err == nil {
  1028  			*newID, err = res.LastInsertId()
  1029  		}
  1030  		c.mu.Unlock()
  1031  		return err
  1032  	} else {
  1033  		c.mu.Lock()
  1034  		_, err := c.statements[UpdateProfile].Exec(p.Name, p.VATPercent, p.AdminPercent, p.InvoiceHeader, p.InvoiceFooter, p.ID)
  1035  		c.mu.Unlock()
  1036  		*newID = p.ID
  1037  		return err
  1038  	}
  1039  }
  1040  
  1041  var ErrNoRemoveDefault = errors.New("cannot remove default profile")
  1042  
  1043  func (c *Calls) RemoveProfile(pid uint64, _ *struct{}) error {
  1044  	if pid == 0 {
  1045  		return ErrNoRemoveDefault
  1046  	}
  1047  	c.mu.Lock()
  1048  	_, err := c.statements[DeleteProfile].Exec(pid)
  1049  	if err == nil {
  1050  		_, err = c.statements[DefaultProfile].Exec(pid)
  1051  	}
  1052  	c.mu.Unlock()
  1053  	return err
  1054  }
  1055  
  1056  func (c *Calls) CompanyColour(clientID int64, colour *uint32) error {
  1057  	c.mu.Lock()
  1058  	err := c.statements[CompanyColourFromClient].QueryRow(clientID).Scan(colour)
  1059  	c.mu.Unlock()
  1060  	return err
  1061  }
  1062  
  1063  func (c *Calls) FirstUnassigned(_ struct{}, t *int64) error {
  1064  	c.mu.Lock()
  1065  	err := c.statements[FirstUnassigned].QueryRow().Scan(t)
  1066  	c.mu.Unlock()
  1067  	if err != sql.ErrNoRows {
  1068  		return err
  1069  	}
  1070  	return nil
  1071  }
  1072  
  1073  func (c *Calls) UnassignedCount(_ struct{}, n *uint64) error {
  1074  	c.mu.Lock()
  1075  	err := c.statements[UnassignedCount].QueryRow().Scan(n)
  1076  	c.mu.Unlock()
  1077  	return err
  1078  }
  1079  
  1080  func (c *Calls) getPort() (uint16, error) {
  1081  	var port uint16
  1082  	c.mu.Lock()
  1083  	err := c.db.QueryRow("SELECT [Port] FROM [Settings];").Scan(&port)
  1084  	c.mu.Unlock()
  1085  	return port, err
  1086  }
  1087  
  1088  func (c *Calls) GetUsers(_ struct{}, u *[]string) error {
  1089  	users := make([]string, 1, len(authMap.users))
  1090  	users[0] = "admin"
  1091  	for username := range authMap.users {
  1092  		if username != "admin" {
  1093  			users = append(users, username)
  1094  		}
  1095  	}
  1096  	sort.Strings(users[1:])
  1097  	*u = users
  1098  	return nil
  1099  }
  1100  
  1101  type UsernamePassword struct {
  1102  	Username, Password string
  1103  }
  1104  
  1105  func (c *Calls) SetUser(up UsernamePassword, _ *struct{}) error {
  1106  	var err error
  1107  	password := sha1.Sum([]byte(up.Password))
  1108  	c.mu.Lock()
  1109  	if authMap.Set(up.Username, password) {
  1110  		_, err = c.statements[UpdateUser].Exec(password[:], up.Username)
  1111  	} else {
  1112  		_, err = c.statements[AddUser].Exec(up.Username, password[:])
  1113  	}
  1114  	c.mu.Unlock()
  1115  	return err
  1116  }
  1117  
  1118  func (c *Calls) RemoveUser(username string, _ *struct{}) error {
  1119  	if username == "admin" {
  1120  		return errors.New("cannot remove admin")
  1121  	}
  1122  	authMap.Remove(username)
  1123  	c.mu.Lock()
  1124  	_, err := c.statements[RemoveUser].Exec(username)
  1125  	c.mu.Unlock()
  1126  	return err
  1127  }
  1128  
  1129  func (c *Calls) UsersOnline(_ struct{}, users *map[string]uint) error {
  1130  	*users = userMap.Copy()
  1131  	return nil
  1132  }
  1133  
  1134  type DriverShowPos struct {
  1135  	ID   int
  1136  	Show bool
  1137  	Pos  int
  1138  }
  1139  
  1140  func (c *Calls) SetDriverPosShows(dsps []DriverShowPos, _ *struct{}) error {
  1141  	c.mu.Lock()
  1142  	defer c.mu.Unlock()
  1143  	tx, err := c.db.Begin()
  1144  	if err != nil {
  1145  		return err
  1146  	}
  1147  	stmt, err := tx.Prepare("UPDATE [Driver] SET [Show] = ?, [Pos] = ? WHERE [ID] = ?;")
  1148  	if err != nil {
  1149  		return err
  1150  	}
  1151  	for _, dsp := range dsps {
  1152  		_, err := stmt.Exec(dsp.Show, dsp.Pos, dsp.ID)
  1153  		if err != nil {
  1154  			return err
  1155  		}
  1156  	}
  1157  	return tx.Commit()
  1158  }
  1159