Docpoint synchronization application is a tool that transfers appointment data from Open Dental to Docpoint Web Service, and vice versa. This tool works as a cross vendor database replication to carry out, create, update, and cancel operations, in a cycle that is executed every 1 minute.
Docpoint creates a local database (SQLite) on the client machine which works as an intermediate layer to keep track of every appointment created from Open Dental and Docpoint. This layer also works as a buffer in case of an internet outage.
Transferring data from Open Dental to Docpoint: Does not require any writes on Open Dental database.
SELECT AptNum, PatNum, ProvNum, Date(AptDateTime) as date, Time(AptDateTime) as time, Pattern, ProcDescriptFROM appointmentWHERE Date(AptDateTime) >= ? AND AptNum > ? AND AptStatus = 1 ORDER BY AptNum LIMIT ?
Transferring data from Docpoint to Open Dental: Requires writes on Open Dental Database.
Creating and updating appointments:
INSERT INTO appointment (PatNum, ProvNum, AptDateTime, Pattern, ProcDescript , Confirmed, TimeLocked, Op, AptStatus, ProvHyg, NextAptNum, UnschedStatus, Assistant,ClinicNum, InsPlan1, InsPlan2, ProcsColored,ColorOverride, DateTimeArrived, DateTimeSeated, DateTimeDismissed)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)If the patient does not exist in the system then it will create one using this query:INSERT INTO patient (FName, LName, Gender, Birthdate, Address , City, State, Zip, WkPhone, Email, Guarantor, PriProv, SecProv, FeeSched, BillingType, SchoolName, EmployerNum, ClinicNum, Premed, PreferConfirmMethod, PreferContactMethod,PreferRecallMethod, SchedDayOfWeek, PayPlanDue,SiteNum, ResponsParty, CanadianEligibilityCode,AskToArriveEarly, OnlinePassword, SmokeStatus,PreferContactConfidential, SuperFamily, TxtMsgOk)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)Or it will update the appointment if it already exists through:UPDATE appointment SET AptDateTime=?, Pattern=? WHERE AptNum = ?
UPDATE appointment SET AptStatus=0 WHERE AptNum = ?
The application gives the client an option to make two-way synchronization or just one-way where the application only transfers data from Open Dental to Docpoint without writing anything on the database. If a two-way sync is selected, the application has to write on appointment and patient tables. Also the application comes in with a database backup feature that could be disabled as well. Database backup creates a daily backup folder that contains the Open Dental database.
Example: If Open Dental database resides in: C:\Program Files\MySQL\MySQL Server 5.0\data\cpatientdatethen database would look like C:\Program Files\MySQL\MySQL Server 5.0\data\cpatientdate_2013-9-5
The application handles internet outage gracefully since it stores every event on its local database. It also handles racing condition very well, so when a practice runs the application for the first time, and has 2000 unsynchronized appointments, the application will segment the workload and execute events in manageable bundles.