The only additional piece of software needed to make this happen, other than Open Dental, is a little piece of software called SendEmail, which allows you to send emails (with or without attachments) from the command line (or as a batch file).
Here's a walk-through. The following should be done from your server:
- Decide which query you would like emailed (my report is at the bottom of this post) and save it as a text file with the extension .sql. If you're using Word you have to make sure that you save the file as text only, but I suggest using Notepad or Notepad++. If you already have a report you'd like to use you can just add INTO OUTFILE "C:\\Reports\\KPIThisWeek.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'; to the end of the report. NOTE: You need to have that line before any commands to DROP tables AND pay attention to the semi-colon. This step will create a CSV spreadsheet to the folder of your choosing.
- Create a batch file with commands to run the above query from the command line (see mine at the bottom of this post). The batch file will need three simple commands. First, it will tell MySQL to run the query mentioned above. Second, it will run SendEmail with the query result spreadsheet as an attachment.
- Create a scheduled task from Windows Task Scheduler to run the batch file at the time(s) of your choosing. Click the Windows button (or press the Windows key on your keyboard), type "task", and Task Scheduler should be the top application you can select. From there:
Name your task and (optionally) add a description.
Set the time(s) you'd like your report to run in trigger.
When prompted to choose an action, select Start a program
Find the batch file you created.
Click Finish.
You can test your setup by selecting the task you just created in the "Task Scheduler Library" (it's on the upper left of the window) then clicking run. If everything worked right you will get an email in your inbox with the csv attachment.
Let me know if you have any questions about this setup. Below is the code I have in my reports:
MySQL Query for our KPI for this week It saves it to a CSV file in C:\Reports\. Save this file as C:\Reports\KPIThisWeek.sql
Code: Select all
DROP TABLE IF EXISTS temp1,temp2,temp3;
SET @FromDate=adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY), @ToDate=adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY);
CREATE TABLE temp1(
Date date,
$Production double NOT NULL DEFAULT 0,
$Scheduled double NOT NULL DEFAULT 0,
$Total double NOT NULL DEFAULT 0,
$Gross double NOT NULL DEFAULT 0,
$WriteOffs double NOT NULL DEFAULT 0,
$SchedProd double NOT NULL DEFAULT 0,
$SchdWrtOff double NOT NULL DEFAULT 0,
$Adjustments double NOT NULL DEFAULT 0,
$PatIncome double NOT NULL DEFAULT 0,
$InsIncome double NOT NULL DEFAULT 0,
$Collection double NOT NULL DEFAULT 0,
NPs double NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
/*Load Dates of Current Week*/
INSERT INTO temp1(Date)
VALUES (@FromDate),(ADDDATE(@FromDate, INTERVAL 1 DAY)),(ADDDATE(@FromDate, INTERVAL 2 DAY)),(ADDDATE(@FromDate, INTERVAL 3 DAY)),(ADDDATE(@FromDate, INTERVAL 4 DAY)),(ADDDATE(@FromDate, INTERVAL 5 DAY)),(@ToDate);
/*Gross Prod*/
CREATE TABLE temp2
SELECT DATE(pl.ProcDate) AS 'Date', SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) AS 'Gross'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND DATE(pl.ProcDate) BETWEEN @FromDate AND @ToDate
GROUP BY DATE(pl.ProcDate);
UPDATE temp1,temp2 SET temp1.$Gross=temp2.Gross WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Sched Prod and writeoffs*/
CREATE TABLE temp2
SELECT DATE(ap.AptDateTime) AS 'Date', SUM(pl.procfee) AS 'SchedProd', SUM(IF((cp.WriteOffEstOverride=-1),IF((cp.WriteOffEst=-1),0,cp.WriteOffEst), cp.WriteOffEstOverride)) AS 'SchdWrtOff'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=1 AND ap.AptStatus=1 AND DATE(ap.AptDateTime) BETWEEN @FromDate AND @ToDate AND (ISNULL(cp.Status) OR cp.Status=6 /*estimate*/)
GROUP BY DATE(ap.AptDateTime);
UPDATE temp1,temp2 SET temp1.$SchedProd=temp2.SchedProd , temp1.$SchdWrtOff=-temp2.SchdWrtOff WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Adj*/
CREATE TABLE temp2
SELECT DATE(a.AdjDate) AS 'Date', SUM(a.AdjAmt) AS 'Adjustments'
FROM adjustment a WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
GROUP BY DATE(a.AdjDate);
UPDATE temp1,temp2 SET temp1.$Adjustments=temp2.Adjustments WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*PatInc*/
CREATE TABLE temp2
SELECT DATE(pp.DatePay) AS 'Date', SUM(pp.SplitAmt) AS 'PatIncome' FROM paysplit pp
WHERE pp.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY DATE(pp.DatePay);
UPDATE temp1,temp2 SET temp1.$PatIncome=temp2.PatIncome WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Writeoffs*/
CREATE TABLE temp2
SELECT DATE(cp.ProcDate) AS 'Date', SUM(cp.WriteOff) AS 'WriteOffs'
FROM claimproc cp WHERE (cp.Status=1 OR cp.Status=4 OR cp.Status=0) AND (cp.ProcDate BETWEEN @FromDate AND @ToDate)
GROUP BY DATE(cp.ProcDate);
UPDATE temp1,temp2 SET temp1.$WriteOffs=-temp2.WriteOffs WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*InsIncome*/
CREATE TABLE temp2
SELECT DATE(cpay.CheckDate) AS 'Date', SUM(cp.InsPayAmt) AS 'InsIncome'
FROM claimproc cp INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cpay.CheckDate BETWEEN @FromDate AND @ToDate AND cp.Status IN(1,4)
GROUP BY DATE(cpay.CheckDate);
UPDATE temp1,temp2 SET temp1.$InsIncome=temp2.InsIncome WHERE temp1.Date=temp2.Date;
UPDATE temp1 SET $Production=ROUND($Gross+$Adjustments+$WriteOffs,0), $Scheduled=ROUND($SchedProd+$SchdWrtOff,0), $Collection=ROUND($InsIncome+$PatIncome,0);
DROP TABLE IF EXISTS temp2;
ALTER TABLE temp1 DROP $Gross, DROP $WriteOffs, DROP $SchedProd, DROP $SchdWrtOff, DROP $Adjustments, DROP $PatIncome, DROP $InsIncome;
/* NPs */
CREATE TABLE temp2
SELECT DATE(p.DateFirstVisit) AS 'Date', COUNT(DISTINCT p.PatNum) AS 'NPs'
FROM patient p LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum LEFT JOIN appointment a ON p.PatNum=a.PatNum AND a.AptStatus=1 /*sched*/
WHERE (pl.ProcDate BETWEEN @FromDate AND @ToDate AND pl.ProcStatus=2 AND p.DateFirstVisit >= @FromDate) OR (a.AptDateTime BETWEEN @FromDate AND @ToDate AND p.DateFirstVisit >= @FromDate AND a.AptDateTime >= p.DateFirstVisit)
GROUP BY DATE(p.DateFirstVisit);
UPDATE temp1,temp2 SET temp1.NPs=temp2.NPs WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Reappt Rate*/
CREATE TABLE temp2(Date date,Seen int NOT NULL DEFAULT 0,Sched int NOT NULL DEFAULT 0);
INSERT INTO temp2(Date,Seen)
SELECT DATE(AptDateTime) AS 'Date', COUNT(DISTINCT PatNum) AS 'Seen'
FROM appointment WHERE (DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AND AptStatus=2
GROUP BY Date;
CREATE TABLE temp3
SELECT DATE(a1.AptDateTime) AS 'Date', COUNT(DISTINCT a2.PatNum) AS 'Sched'
FROM appointment a1 LEFT JOIN appointment a2 ON a1.PatNum=a2.PatNum
WHERE (DATE(a1.AptDateTime) BETWEEN @FromDate AND @ToDate AND a1.AptStatus=2) AND (DATE(a2.AptDateTime)>DATE(a1.AptDateTime) AND a2.AptStatus=1)
GROUP BY Date;
UPDATE temp2,temp3 SET temp2.Sched=temp3.Sched WHERE temp2.Date=temp3.Date;
UPDATE temp1,temp2 SET temp1.Rate=CONCAT(ROUND((temp2.Sched/temp2.Seen*100),0),'%') WHERE temp1.Date=temp2.Date;
UPDATE temp1 SET $Total=$Production+$Scheduled;
SELECT 'Date','$Production','$Scheduled','$Total','$Collect','NPs','Reappt'
UNION
SELECT * FROM temp1
UNION
SELECT 'Total', SUM(A.$Production), SUM(A.$Scheduled), SUM(A.$Total), SUM(A.$Collection), SUM(A.NPs), CONCAT(ROUND((SUM(B.Sched)/SUM(B.Seen)*100),0),'%') FROM temp1 A LEFT JOIN temp2 B ON A.Date=B.Date
INTO OUTFILE "C:\\Reports\\KPIThisWeek.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
DROP TABLE IF EXISTS temp1,temp2,temp3;Code: Select all
@ECHO OFF
REM Update File locations below if necessary
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysql" -u "root" "opendental" < "C:\Reports\KPIThisWeek.sql"
REM Update email info below
"C:\sendEmail\sendEmail.exe" -f SentFromEmail@SomeDomain.com -t SentToEmail@SomeDomain.com -s smtp.gmail.com:587 -xu USERNAME -xp PASSWORD -u "Email Subject Line" -m "Message body." -a "C:\Reports\KPIThisWeek.csv"
del "C:\Reports\KPIThisWeek.csv"Code: Select all
DROP TABLE IF EXISTS temp1,temp2,temp3;
SET @FromDate=adddate(curdate(), INTERVAL 1-DAYOFWEEK(curdate()) DAY), @ToDate=adddate(curdate(), INTERVAL 7-DAYOFWEEK(curdate()) DAY);
CREATE TABLE temp1(
Date date,
$Production double NOT NULL DEFAULT 0,
$Scheduled double NOT NULL DEFAULT 0,
$Total double NOT NULL DEFAULT 0,
$Gross double NOT NULL DEFAULT 0,
$WriteOffs double NOT NULL DEFAULT 0,
$SchedProd double NOT NULL DEFAULT 0,
$SchdWrtOff double NOT NULL DEFAULT 0,
$Adjustments double NOT NULL DEFAULT 0,
$PatIncome double NOT NULL DEFAULT 0,
$InsIncome double NOT NULL DEFAULT 0,
$Collection double NOT NULL DEFAULT 0,
NPs double NOT NULL DEFAULT 0,
Rate CHAR(4) NOT NULL DEFAULT 0);
/*Load Dates of Current Week*/
INSERT INTO temp1(Date)
VALUES (@FromDate),(ADDDATE(@FromDate, INTERVAL 1 DAY)),(ADDDATE(@FromDate, INTERVAL 2 DAY)),(ADDDATE(@FromDate, INTERVAL 3 DAY)),(ADDDATE(@FromDate, INTERVAL 4 DAY)),(ADDDATE(@FromDate, INTERVAL 5 DAY)),(@ToDate);
/*Gross Prod*/
CREATE TABLE temp2
SELECT DATE(pl.ProcDate) AS 'Date', SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) AS 'Gross'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND DATE(pl.ProcDate) BETWEEN @FromDate AND @ToDate
GROUP BY DATE(pl.ProcDate);
UPDATE temp1,temp2 SET temp1.$Gross=temp2.Gross WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Sched Prod and writeoffs*/
CREATE TABLE temp2
SELECT DATE(ap.AptDateTime) AS 'Date', SUM(pl.procfee) AS 'SchedProd', SUM(IF((cp.WriteOffEstOverride=-1),IF((cp.WriteOffEst=-1),0,cp.WriteOffEst), cp.WriteOffEstOverride)) AS 'SchdWrtOff'
FROM appointment ap INNER JOIN procedurelog pl ON pl.AptNum=ap.AptNum LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum
WHERE pl.ProcStatus=1 AND ap.AptStatus=1 AND DATE(ap.AptDateTime) BETWEEN @FromDate AND @ToDate AND (ISNULL(cp.Status) OR cp.Status=6 /*estimate*/)
GROUP BY DATE(ap.AptDateTime);
UPDATE temp1,temp2 SET temp1.$SchedProd=temp2.SchedProd , temp1.$SchdWrtOff=-temp2.SchdWrtOff WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Adj*/
CREATE TABLE temp2
SELECT DATE(a.AdjDate) AS 'Date', SUM(a.AdjAmt) AS 'Adjustments'
FROM adjustment a WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
GROUP BY DATE(a.AdjDate);
UPDATE temp1,temp2 SET temp1.$Adjustments=temp2.Adjustments WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*PatInc*/
CREATE TABLE temp2
SELECT DATE(pp.DatePay) AS 'Date', SUM(pp.SplitAmt) AS 'PatIncome' FROM paysplit pp
WHERE pp.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY DATE(pp.DatePay);
UPDATE temp1,temp2 SET temp1.$PatIncome=temp2.PatIncome WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Writeoffs*/
CREATE TABLE temp2
SELECT DATE(cp.ProcDate) AS 'Date', SUM(cp.WriteOff) AS 'WriteOffs'
FROM claimproc cp WHERE (cp.Status=1 OR cp.Status=4 OR cp.Status=0) AND (cp.ProcDate BETWEEN @FromDate AND @ToDate)
GROUP BY DATE(cp.ProcDate);
UPDATE temp1,temp2 SET temp1.$WriteOffs=-temp2.WriteOffs WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*InsIncome*/
CREATE TABLE temp2
SELECT DATE(cpay.CheckDate) AS 'Date', SUM(cp.InsPayAmt) AS 'InsIncome'
FROM claimproc cp INNER JOIN claimpayment cpay ON cpay.ClaimPaymentNum=cp.ClaimPaymentNum
WHERE cpay.CheckDate BETWEEN @FromDate AND @ToDate AND cp.Status IN(1,4)
GROUP BY DATE(cpay.CheckDate);
UPDATE temp1,temp2 SET temp1.$InsIncome=temp2.InsIncome WHERE temp1.Date=temp2.Date;
UPDATE temp1 SET $Production=ROUND($Gross+$Adjustments+$WriteOffs,0), $Scheduled=ROUND($SchedProd+$SchdWrtOff,0), $Collection=ROUND($InsIncome+$PatIncome,0);
DROP TABLE IF EXISTS temp2;
ALTER TABLE temp1 DROP $Gross, DROP $WriteOffs, DROP $SchedProd, DROP $SchdWrtOff, DROP $Adjustments, DROP $PatIncome, DROP $InsIncome;
/* NPs */
CREATE TABLE temp2
SELECT DATE(p.DateFirstVisit) AS 'Date', COUNT(DISTINCT p.PatNum) AS 'NPs'
FROM patient p LEFT JOIN procedurelog pl ON p.PatNum=pl.PatNum LEFT JOIN appointment a ON p.PatNum=a.PatNum AND a.AptStatus=1 /*sched*/
WHERE (pl.ProcDate BETWEEN @FromDate AND @ToDate AND pl.ProcStatus=2 AND p.DateFirstVisit >= @FromDate) OR (a.AptDateTime BETWEEN @FromDate AND @ToDate AND p.DateFirstVisit >= @FromDate AND a.AptDateTime >= p.DateFirstVisit)
GROUP BY DATE(p.DateFirstVisit);
UPDATE temp1,temp2 SET temp1.NPs=temp2.NPs WHERE temp1.Date=temp2.Date;
DROP TABLE IF EXISTS temp2;
/*Reappt Rate*/
CREATE TABLE temp2(Date date,Seen int NOT NULL DEFAULT 0,Sched int NOT NULL DEFAULT 0);
INSERT INTO temp2(Date,Seen)
SELECT DATE(AptDateTime) AS 'Date', COUNT(DISTINCT PatNum) AS 'Seen'
FROM appointment WHERE (DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AND AptStatus=2
GROUP BY Date;
CREATE TABLE temp3
SELECT DATE(a1.AptDateTime) AS 'Date', COUNT(DISTINCT a2.PatNum) AS 'Sched'
FROM appointment a1 LEFT JOIN appointment a2 ON a1.PatNum=a2.PatNum
WHERE (DATE(a1.AptDateTime) BETWEEN @FromDate AND @ToDate AND a1.AptStatus=2) AND (DATE(a2.AptDateTime)>DATE(a1.AptDateTime) AND a2.AptStatus=1)
GROUP BY Date;
UPDATE temp2,temp3 SET temp2.Sched=temp3.Sched WHERE temp2.Date=temp3.Date;
UPDATE temp1,temp2 SET temp1.Rate=CONCAT(ROUND((temp2.Sched/temp2.Seen*100),0),'%') WHERE temp1.Date=temp2.Date;
UPDATE temp1 SET $Total=$Production+$Scheduled;
SELECT * FROM temp1
UNION
SELECT 'Total', SUM(A.$Production), SUM(A.$Scheduled), SUM(A.$Total), SUM(A.$Collection), SUM(A.NPs), CONCAT(ROUND((SUM(B.Sched)/SUM(B.Seen)*100),0),'%') FROM temp1 A LEFT JOIN temp2 B ON A.Date=B.Date;
DROP TABLE IF EXISTS temp1,temp2,temp3;
