Ejemplo n.º 1
0
        private void fill_cmbProc()
        {
            DataTable table = new DataTable();

            table.Columns.Add("Procedure Code");


            DataRow row;

            string command = @"
				SELECT ProcCode
                FROM procedurecode";

            DataTable raw = ReportsComplex.GetTable(command);

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row = table.NewRow();
                row["Procedure Code"] = raw.Rows[i]["ProcCode"].ToString();
            }

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                cmbProc.Items.Add(raw.Rows[i]["ProcCode"].ToString());
            }
        }
Ejemplo n.º 2
0
        // public static String GetQueryPats()


        public static DataTable GetPendingTreatmentPats(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("PatNum");
            table.Columns.Add("Name");
            table.Columns.Add("Home Phone");
            table.Columns.Add("Cell Phone");
            table.Columns.Add("Wireless Phone");
            table.Columns.Add("Email");


            DataRow row;

            string command = @"
				SELECT DISTINCT p.PatNum, p.LName, p.FName, p.MiddleI, 
                           p.HmPhone, p.WkPhone, p.WirelessPhone, p.Email
                FROM procedurelog pl
                JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
                JOIN appointment a ON a.AptNum = pl.PlannedAptNum
                JOIN patient p ON a.PatNum = p.PatNum
                WHERE pl.AptNum = 0
                AND a.AptStatus = 6
                AND pc.ProcCode != 01202
            ";

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row         = table.NewRow();
                pat         = new Patient();
                pat.LName   = raw.Rows[i]["LName"].ToString();
                pat.FName   = raw.Rows[i]["FName"].ToString();
                pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
                row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() +
                              " " + raw.Rows[i]["LName"].ToString();
                pat.HmPhone       = raw.Rows[i]["HmPhone"].ToString();
                pat.WkPhone       = raw.Rows[i]["WkPhone"].ToString();
                pat.WirelessPhone = raw.Rows[i]["WirelessPhone"].ToString();
                pat.Email         = raw.Rows[i]["Email"].ToString();

                row["PatNum"]     = raw.Rows[i]["PatNum"];
                row["Home Phone"] = raw.Rows[i]["HmPhone"].ToString();
                row["Work Phone"] = raw.Rows[i]["WkPhone"].ToString();
                row["Cell Phone"] = raw.Rows[i]["WirelessPhone"].ToString();
                row["Email"]      = raw.Rows[i]["Email"].ToString();

                table.Rows.Add(row);
            }


            return(table);
        }
Ejemplo n.º 3
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetActiveRecall(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Postal Code");
            table.Columns.Add("Date of Service");
            table.Columns.Add("Frequency");
            table.Columns.Add("Primary Provider");
            DataRow row;
            string  command = @"
				SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, r.ProcDate, p.Birthdate, q.RecallInterval
				FROM patient p 
				JOIN procedurelog r ON r.PatNum = p.PatNum 
                JOIN recall q ON p.PatNum = q.PatNum 
				WHERE r.ProcDate = (SELECT MAX(r2.ProcDate) 
                FROM procedurelog r2
                JOIN procedurecode c ON r2.CodeNum = c.CodeNum 
                WHERE r.PatNum = r2.PatNum AND 
                r2.PlannedAptNum > 0 AND 
                c.ProcCode = 01202 AND 
                r2.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") AND
                q.RecallTypeNum = 1 AND 
                q.IsDisabled = 0 GROUP BY p.PatNum";

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row                     = table.NewRow();
                pat                     = new Patient();
                pat.LName               = raw.Rows[i]["LName"].ToString();
                pat.FName               = raw.Rows[i]["FName"].ToString();
                pat.MiddleI             = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred           = raw.Rows[i]["Preferred"].ToString();
                row["Name"]             = pat.GetNameLF();
                row["Primary Provider"] = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
                Console.Write(raw.Rows[i]["Gender"].ToString());
                row["Gender"]          = genderFormat(raw.Rows[i]["Gender"].ToString());
                row["Postal Code"]     = raw.Rows[i]["Zip"].ToString();
                row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Age"]             = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                Interval frequency = new Interval(Int32.Parse(raw.Rows[i]["RecallInterval"].ToString()));
                row["Frequency"] = frequency.ToString();
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 4
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetPerioRecall(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Postal Code");
            table.Columns.Add("Date of Next Appointment");
            table.Columns.Add("Frequency");
            table.Columns.Add("HygienistID");
            table.Columns.Add("Primary Provider");
            DataRow   row; //Could not find code 43400 so just assumed all procedure codes between 40000 and 50000
            string    command = @"
				SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.RecallInterval, a.AptDateTime, a.ProvHyg
				FROM patient p 
				JOIN procedurelog x ON x.PatNum = p.PatNum 
                JOIN procedurecode c ON c.CodeNum = x.CodeNum 
                JOIN appointment a ON a.AptNum = x.AptNum 
                JOIN recall r ON r.PatNum = p.PatNum 
                WHERE r.IsDisabled = 0 AND 
                r.RecallTypeNum = 3 AND 
                c.ProcCode = 43400 AND 
                a.AptDateTime = (SELECT MAX(a2.AptDateTime) 
                FROM appointment a2 
                WHERE a2.AptNum = a.AptNum AND 
                a2.AptDateTime BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") GROUP BY p.PatNum";
            DataTable raw     = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row                             = table.NewRow();
                pat                             = new Patient();
                pat.LName                       = raw.Rows[i]["LName"].ToString();
                pat.FName                       = raw.Rows[i]["FName"].ToString();
                pat.MiddleI                     = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred                   = raw.Rows[i]["Preferred"].ToString();
                row["Name"]                     = pat.GetNameLF();
                row["Primary Provider"]         = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
                row["Gender"]                   = genderFormat(raw.Rows[i]["Gender"].ToString());
                row["Postal Code"]              = raw.Rows[i]["Zip"].ToString();
                row["Date of Next Appointment"] = raw.Rows[i]["AptDateTime"].ToString().Substring(0, 10);
                Interval frequency = new Interval(Int32.Parse(raw.Rows[i]["RecallInterval"].ToString()));
                row["Frequency"]   = frequency.ToString();
                row["HygienistID"] = raw.Rows[i]["ProvHyg"].ToString();
                row["Age"]         = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 5
0
        public static DataTable GetRecallBacklog(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod()));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Preferred Contact Method");
            table.Columns.Add("Phone");
            table.Columns.Add("Email");
            table.Columns.Add("Hygienist ID (Last appointment)");
            table.Columns.Add("Date of Last Recall");
            table.Columns.Add("Due Date");
            DataRow row;
            string  command = @"
                SELECT p.LName, p.FName, p.MiddleI, p.Preferred, p.Gender, p.PreferContactMethod, p.HmPhone, p.Email, q.DatePrevious, c.ProcCode, r.AptNum, a.ProvHyg, a.AptDateTime, q.DateDue
                FROM patient p 
	                INNER JOIN procedurelog r ON r.PatNum = p.PatNum
	                INNER JOIN procedureCode c ON r.CodeNum = c.CodeNum
                    INNER JOIN recall q ON p.PatNum = q.PatNum
                    INNER JOIN plannedappt pa on pa.PatNum = p.PatNum
                    INNER JOIN appointment a ON r.PatNum = a.PatNum  WHERE c.ProcCode=01202 AND a.AptStatus=6 AND r.PlannedAptNum = pa.AptNum AND r.AptNum = 0
                    or a.AptStatus=3 AND c.ProcCode=1202
                    OR a.AptStatus=5 AND c.ProcCode=1202
                    AND r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) +
                              @" GROUP BY p.PatNUm";



            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row           = table.NewRow();
                pat           = new Patient();
                pat.LName     = raw.Rows[i]["LName"].ToString();
                pat.FName     = raw.Rows[i]["FName"].ToString();
                pat.MiddleI   = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred = raw.Rows[i]["Preferred"].ToString();
                row["Name"]   = pat.GetNameLF();
                row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString());
                row["Preferred Contact Method"] = getContactMethod(raw.Rows[i]["PreferContactMethod"].ToString());
                row["Phone"] = raw.Rows[i]["HmPhone"].ToString();
                row["Email"] = raw.Rows[i]["Email"].ToString();
                row["Hygienist ID (Last appointment)"] = raw.Rows[i]["ProvHyg"].ToString();
                row["Date of Last Recall"]             = raw.Rows[i]["DatePrevious"].ToString().Substring(0, 10);
                row["Due Date"] = raw.Rows[i]["DateDue"].ToString().Substring(0, 10);
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 6
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetNoShowAppt(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Postal Code");
            table.Columns.Add("Date of Service");
            table.Columns.Add("Primary Provider");
            table.Columns.Add("Procedure Description");
            DataRow row;
            string  command = @"
				SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.ProcDate, a.ProcDescript
                FROM patient p 
                JOIN procedurelog r ON r.PatNum = p.PatNum
                JOIN procedurecode c ON r.CodeNum = c.CodeNum
                JOIN appointment a ON a.AptNum = r.AptNum 
                WHERE c.ProcCode = 99999 AND 
                r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd);

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row                          = table.NewRow();
                pat                          = new Patient();
                pat.LName                    = raw.Rows[i]["LName"].ToString();
                pat.FName                    = raw.Rows[i]["FName"].ToString();
                pat.MiddleI                  = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred                = raw.Rows[i]["Preferred"].ToString();
                row["Name"]                  = pat.GetNameLF();
                row["Primary Provider"]      = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
                row["Gender"]                = genderFormat(raw.Rows[i]["Gender"].ToString());
                row["Postal Code"]           = raw.Rows[i]["Zip"].ToString();
                row["Date of Service"]       = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Procedure Description"] = raw.Rows[i]["ProcDescript"].ToString();
                if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150)
                {
                    row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                }
                else
                {
                    row["Age"] = "N/A";
                }
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 7
0
        /*
         * Completed cases
         * List of patients compiled from the Treatment Plan module whose treatment has been
         * completed within the period designated by the operator
         */

        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetCompletedCases(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Date of Service");

            table.Columns.Add("Treatment Code");
            table.Columns.Add("Treatment Completed");
            table.Columns.Add("Billed");

            DataRow row;


            string command = @"
				SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, pl.ProcDate, pl.ProcNum, pc.ProcCode, 
                        pc.Descript, pl.ProcFee
                FROM procedurelog pl
                JOIN appointment a ON pl.PlannedAptNum = a.AptNum
                JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum
                JOIN patient p ON p.PatNum = pl.PatNum
                WHERE a.AptStatus = 6
                AND pl.ProcStatus = 2
                AND (pl.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @")";



            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row         = table.NewRow();
                pat         = new Patient();
                pat.LName   = raw.Rows[i]["LName"].ToString();
                pat.FName   = raw.Rows[i]["FName"].ToString();
                pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();
                row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() +
                              " " + raw.Rows[i]["LName"].ToString();
                row["Date of Service"]     = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Treatment Code"]      = raw.Rows[i]["ProcCode"].ToString();
                row["Treatment Completed"] = raw.Rows[i]["Descript"].ToString();
                row["Billed"] = raw.Rows[i]["ProcFee"].ToString();

                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 8
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetNewPatients(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Date of Service");
            DataRow row;
            string  command = @"
                SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Preferred, r.ProcDate, p.Birthdate  
                FROM patient p 
                INNER JOIN procedurelog r ON r.PatNum = p.PatNum 
                INNER JOIN procedurecode c ON c.CodeNum = r.CodeNum
                WHERE (c.ProcCode = 01101 OR c.ProcCode = 01102 OR c.ProcCode = 01103) AND
                (r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @")
                GROUP BY p.PatNum";

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row           = table.NewRow();
                pat           = new Patient();
                pat.LName     = raw.Rows[i]["LName"].ToString();
                pat.FName     = raw.Rows[i]["FName"].ToString();
                pat.MiddleI   = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred = raw.Rows[i]["Preferred"].ToString();
                row["Name"]   = pat.GetNameLF();
                row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString());

                if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150)
                {
                    row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                }
                else
                {
                    row["Age"] = "N/A";
                }

                row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString();
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 9
0
        public static DataTable GetPatients(DateTime dateStart, DateTime dateEnd, string query)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("PatNum");
            table.Columns.Add("Name");
            table.Columns.Add("Home Phone");
            table.Columns.Add("Work Phone");
            table.Columns.Add("Wireless Phone");
            table.Columns.Add("Email");


            DataRow row;

            DataTable raw = ReportsComplex.GetTable(query);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row         = table.NewRow();
                pat         = new Patient();
                pat.LName   = raw.Rows[i]["LName"].ToString();
                pat.FName   = raw.Rows[i]["FName"].ToString();
                pat.MiddleI = raw.Rows[i]["MiddleI"].ToString();

                row["Name"] = raw.Rows[i]["FName"].ToString() + " " + raw.Rows[i]["MiddleI"].ToString() +
                              " " + raw.Rows[i]["LName"].ToString();
                pat.HmPhone       = raw.Rows[i]["HmPhone"].ToString();
                pat.WkPhone       = raw.Rows[i]["WkPhone"].ToString();
                pat.WirelessPhone = raw.Rows[i]["WirelessPhone"].ToString();
                pat.Email         = raw.Rows[i]["Email"].ToString();

                row["PatNum"]         = raw.Rows[i]["PatNum"].ToString();
                row["Home Phone"]     = raw.Rows[i]["HmPhone"].ToString();
                row["Work Phone"]     = raw.Rows[i]["WkPhone"].ToString();
                row["Wireless Phone"] = raw.Rows[i]["WirelessPhone"].ToString();
                row["Email"]          = raw.Rows[i]["Email"].ToString();

                table.Rows.Add(row);
            }


            return(table);
        }
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetRecTreatmentYYY(DateTime dateStart, DateTime dateEnd, long pnum, String pc)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Date of Service");
            table.Columns.Add("Name");
            table.Columns.Add("Procedure");
            table.Columns.Add("Priority");
            table.Columns.Add("Status of Pre-Authorization");


            DataRow row;
            string  command = @"
				SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus
                FROM patient p
                JOIN procedurelog r ON r.PatNum = p.PatNum
                JOIN treatplan t ON t.PatNum = p.PatNum
                JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum
                JOIN procedurecode rc ON rc.CodeNum = r.CodeNum
                WHERE r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @"
                       AND rc.ProcCode = " + "'" + POut.String(pc) + "'" + @"
                       AND p.PatNum = " + POut.Long(pnum) + @"
                ORDER BY r.ProcDate";

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row       = table.NewRow();
                pat       = new Patient();
                pat.LName = raw.Rows[i]["LName"].ToString();
                pat.FName = raw.Rows[i]["FName"].ToString();

                row["Date of Service"]             = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Name"]                        = pat.LName + ", " + pat.FName;
                row["Procedure"]                   = raw.Rows[i]["ProcCode"].ToString();
                row["Priority"]                    = raw.Rows[i]["Priority"].ToString();
                row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"];
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 11
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static float GetConversionRate(DateTime dateStart, DateTime dateEnd, String pc)
        {
            // Find all procedures for that ProcCode
            string command = @"

                SELECT COUNT(*)
                FROM appointment a
                JOIN procedurelog pl ON pl.PlannedAptNum = a.NextAptNum 
                JOIN procedurecode x ON x.CodeNum = pl.CodeNum 
                WHERE x.ProcCode = " + "'" + POut.String(pc) + "'" + @" 
                    AND pl.DateTP BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @"";
            // Find procedures that have been planned.
            string command2 = @"
                SELECT COUNT(*)
                FROM appointment a
                JOIN procedurelog pl ON pl.PlannedAptNum = a.AptNum 
                JOIN procedurecode x ON x.CodeNum = pl.CodeNum 
                WHERE x.ProcCode = " + "'" + POut.String(pc) + "'" + @" 
                    AND pl.DateTP BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @"
                    AND pl.AptNum = 0";


            DataTable raw       = ReportsComplex.GetTable(command);
            DataTable raw2      = ReportsComplex.GetTable(command2);
            float     returnval = 0;
            // No Procedures At All


            float TOTALPROCEDURES      = float.Parse(raw.Rows[0]["COUNT(*)"].ToString());
            float PROCEDURESPLANNED    = float.Parse(raw2.Rows[0]["COUNT(*)"].ToString());
            float TOTALCOUNTPROCEDURES = TOTALPROCEDURES + PROCEDURESPLANNED;

            if (TOTALCOUNTPROCEDURES == 0)
            {
                return(9999);
            }
            else
            {
                System.Diagnostics.Debug.Write(TOTALCOUNTPROCEDURES);
                System.Diagnostics.Debug.Write(PROCEDURESPLANNED);

                //    (Count of all procedures - Count of Planned Procedures)  / Count of All Procedures
                //  Simply put:    (Count of procedures scheduled) / Count of All procedures
                returnval = ((TOTALCOUNTPROCEDURES - PROCEDURESPLANNED) / TOTALCOUNTPROCEDURES) * 100;
                return((float)Math.Round(returnval, 2));
            }
        }
        public static DataTable GetRecTreatmentNNN()
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod()));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Date of Service");
            table.Columns.Add("Name");
            table.Columns.Add("Procedure");
            table.Columns.Add("Priority");
            table.Columns.Add("Status of Pre-Authorization");


            DataRow row;
            string  command = @"
				SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus
                FROM patient p
                JOIN procedurelog r ON r.PatNum = p.PatNum
                JOIN treatplan t ON t.PatNum = p.PatNum
                JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum
                JOIN procedurecode rc ON rc.CodeNum = r.CodeNum
                ORDER BY r.ProcDate";

            System.Diagnostics.Debug.WriteLine(command);

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row       = table.NewRow();
                pat       = new Patient();
                pat.LName = raw.Rows[i]["LName"].ToString();
                pat.FName = raw.Rows[i]["FName"].ToString();

                row["Date of Service"]             = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Name"]                        = pat.LName + ", " + pat.FName;
                row["Procedure"]                   = raw.Rows[i]["ProcCode"].ToString();
                row["Priority"]                    = raw.Rows[i]["Priority"].ToString();
                row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"];
                table.Rows.Add(row);
            }
            return(table);
        }
        public static DataTable GetRecTreatmentYNN(DateTime dateStart, DateTime dateEnd)
        {
            DataTable table = new DataTable();

            table.Columns.Add("Date of Service");
            table.Columns.Add("Name");
            table.Columns.Add("Procedure");
            table.Columns.Add("Priority");
            table.Columns.Add("Status of Pre-Authorization");


            DataRow row;
            string  command = @"
				SELECT r.ProcDate, p.LName, p.FName, p.MiddleI, rc.ProcCode, tpa.Priority, r.ProcStatus
                FROM patient p
                JOIN procedurelog r ON r.PatNum = p.PatNum
                JOIN treatplan t ON t.PatNum = p.PatNum
                JOIN treatplanattach tpa ON tpa.ProcNum = r.ProcNum AND tpa.TreatPlanNum = t.TreatPlanNum
                JOIN procedurecode rc ON rc.CodeNum = r.CodeNum
                WHERE r.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @"
                ORDER BY r.ProcDate";

            System.Diagnostics.Debug.WriteLine(command);

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row       = table.NewRow();
                pat       = new Patient();
                pat.LName = raw.Rows[i]["LName"].ToString();
                pat.FName = raw.Rows[i]["FName"].ToString();

                row["Date of Service"]             = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Name"]                        = pat.LName + ", " + pat.FName;
                row["Procedure"]                   = raw.Rows[i]["ProcCode"].ToString();
                row["Priority"]                    = raw.Rows[i]["Priority"].ToString();
                row["Status of Pre-Authorization"] = raw.Rows[i]["ProcStatus"];
                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 14
0
        public static DataTable GetCompletedCasesPerPat(DateTime dateStart, DateTime dateEnd, String patNum)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Date of Service");
            table.Columns.Add("Treatment Code");
            table.Columns.Add("Treatment Completed");
            table.Columns.Add("Billed");

            DataRow row;

            string command = @"
				SELECT  pc.ProcCode, pl.ProcDate,
                        pc.Descript, pl.ProcFee
                FROM procedurelog pl
                JOIN appointment a ON pl.PlannedAptNum = a.AptNum
                JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum
                JOIN patient p ON p.PatNum = pl.PatNum
                WHERE a.AptStatus = 6
                AND pl.ProcStatus = 2
                AND (pl.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @")
                AND p.PatNum = '" + patNum + @"'
                ";

            DataTable raw = ReportsComplex.GetTable(command);

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row = table.NewRow();
                row["Date of Service"]     = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Treatment Code"]      = raw.Rows[i]["ProcCode"].ToString();
                row["Treatment Completed"] = raw.Rows[i]["Descript"].ToString();
                double a = (double)raw.Rows[i]["ProcFee"];
                row["Billed"] = a;

                table.Rows.Add(row);
            }
            return(table);
        }
Ejemplo n.º 15
0
        ///<summary> dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetDowntime(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Provider");
            table.Columns.Add("Provider Number");
            table.Columns.Add("Total Down-time");

            DataRow row;
            string  command = @"
            
            SELECT  appointment.ProvNum , sec_to_time((sum((CHAR_LENGTH(appointment.Pattern) - CHAR_LENGTH(REPLACE(appointment.Pattern, 'X', ''))) ))*5*60)  AS DownTime
				FROM appointment 
                           
                WHERE EXISTS (	SELECT *
								FROM procedurelog
                                LEFT JOIN procedurecode ON (procedurecode.CodeNum = procedurelog.CodeNum)
                                WHERE procedurelog.AptNum = appointment.AptNum
								AND (procedurecode.ProcCode = 99999 OR procedurecode.ProcCode = 99998) 
                                )
                AND appointment.AptStatus = 5
				AND appointment.AptDateTime BETWEEN "                 + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @"
                GROUP BY appointment.ProvNum";

            DataTable raw = ReportsComplex.GetTable(command);

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row                    = table.NewRow();
                row["Provider"]        = Providers.GetFormalName(PIn.Long(raw.Rows[i]["ProvNum"].ToString()));
                row["Provider Number"] = raw.Rows[i]["ProvNum"].ToString();
                row["Total Down-time"] = raw.Rows[i]["DownTime"].ToString();
                table.Rows.Add(row);
            }

            return(table);
        }
Ejemplo n.º 16
0
        public static DataTable GetPendingTreatmentProcsPerPat(DateTime dateStart, DateTime dateEnd, String patNum)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Procedure Code");
            table.Columns.Add("Treatment Planned");


            DataRow row;

            string command = @"
				SELECT  pc.Descript, pc.ProcCode 
                FROM procedurelog pl
                JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
                JOIN appointment a ON a.AptNum = pl.PlannedAptNum
                JOIN patient p ON a.PatNum = p.PatNum
                WHERE pl.AptNum = 0
                AND a.AptStatus = 6
                AND pc.ProcCode != 01202
                AND p.PatNum = '" + patNum + @"'
            ";

            DataTable raw = ReportsComplex.GetTable(command);

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row = table.NewRow();
                row["Procedure Code"]    = raw.Rows[i]["ProcCode"].ToString();
                row["Treatment Planned"] = raw.Rows[i]["Descript"].ToString();

                table.Rows.Add(row);
            }


            return(table);
        }
Ejemplo n.º 17
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetByReferralSource(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Date of Service");
            table.Columns.Add("Referral Source");
            DataRow row;
            string  command = @"
                SELECT p.LName AS PLName, p.FName AS PFName, p.MiddleI, p.Gender, p.Preferred, l.ProcDate, p.Birthdate, 
						r.LName, r.FName, r.IsDoctor
		        FROM patient p
                    INNER JOIN procedurelog l ON l.PatNum = p.PatNum 
                    INNER JOIN procedurecode c ON c.CodeNum = l.CodeNum
                    INNER JOIN refattach a ON p.PatNum = a.PatNum
                    INNER JOIN referral r ON a.ReferralNum = r.ReferralNum
                        WHERE (c.ProcCode = 01101 OR c.ProcCode = 01102 OR c.ProcCode = 01103) AND
                        (l.ProcDate BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") 
                        GROUP BY p.PatNum";

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;
            String    referralsource;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                row            = table.NewRow();
                pat            = new Patient();
                referralsource = raw.Rows[i]["Lname"].ToString() + ", " + raw.Rows[i]["FName"].ToString();
                if (raw.Rows[i]["IsDoctor"].ToString() == "1")
                {
                    referralsource += " (Doctor)";
                }
                pat.LName     = raw.Rows[i]["PLName"].ToString();
                pat.FName     = raw.Rows[i]["PFName"].ToString();
                pat.MiddleI   = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred = raw.Rows[i]["Preferred"].ToString();

                row["Name"]   = pat.GetNameLF();
                row["Gender"] = genderFormat(raw.Rows[i]["Gender"].ToString());

                if (birthdate_to_age(raw.Rows[i]["Birthdate"].ToString()) < 150)
                {
                    row["Age"] = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                }
                else
                {
                    row["Age"] = "N/A";
                }

                row["Date of Service"] = raw.Rows[i]["ProcDate"].ToString();
                row["Referral Source"] = referralsource;
                table.Rows.Add(row);
            }
            return(resort(table, "Referral Source", "ASC"));
        }
Ejemplo n.º 18
0
        ///<summary>If not using clinics then supply an empty list of clinicNums. dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static DataTable GetNewToRecall(DateTime dateStart, DateTime dateEnd)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Type of Recall");
            DataRow row;
            string  command = @"
               SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Preferred, p.Birthdate, t1.Description  
	FROM patient p 
		INNER JOIN procedurelog l ON l.PatNum = p.PatNum 
		INNER JOIN procedurecode c ON c.CodeNum = l.CodeNum
        INNER JOIN recalltrigger t2 ON c.CodeNum = t2.CodeNum
        INNER JOIN recalltype t1 ON t2.RecallTypeNum = t1.RecallTypeNum
                WHERE c.ProcCode = 01202 AND
					  (l.ProcDate BETWEEN "                     + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd) + @") AND
                      l.PatNum IN (SELECT p2.PatNum
								FROM patient p2
									INNER JOIN procedurelog l2 ON l2.PatNum = p2.PatNum 
									INNER JOIN procedurecode c2 ON c2.CodeNum = l2.CodeNum
								WHERE (c2.ProcCode = 01101 OR c2.ProcCode = 01102 OR c2.ProcCode = 01103) AND
										l2.ProcDate < l.ProcDate AND
                                        l2.ProcDate > (DATE_SUB(l.ProcDate,INTERVAL 1 YEAR)))
                GROUP BY p.PatNum";

            DataTable rawrecall = ReportsComplex.GetTable(command);

            Patient pat;
            String  recalltype;

            for (int j = 0; j < rawrecall.Rows.Count; j++)
            {
                row = table.NewRow();
                pat = new Patient();

                recalltype = rawrecall.Rows[j]["Description"].ToString();
                if (recalltype == null || recalltype == "")
                {
                    recalltype = "Default";
                }
                pat.LName     = rawrecall.Rows[j]["LName"].ToString();
                pat.FName     = rawrecall.Rows[j]["FName"].ToString();
                pat.MiddleI   = rawrecall.Rows[j]["MiddleI"].ToString();
                pat.Preferred = rawrecall.Rows[j]["Preferred"].ToString();

                row["Name"]   = pat.GetNameLF();
                row["Gender"] = genderFormat(rawrecall.Rows[j]["Gender"].ToString());

                if (birthdate_to_age(rawrecall.Rows[j]["Birthdate"].ToString()) < 150)
                {
                    row["Age"] = birthdate_to_age(rawrecall.Rows[j]["Birthdate"].ToString());
                }
                else
                {
                    row["Age"] = "N/A";
                }

                row["Type of Recall"] = recalltype;
                table.Rows.Add(row);
            }

            return(table);
        }
        ///<summary> dateStart and dateEnd can be MinVal/MaxVal to indicate "forever".</summary>
        public static List <object> GetNonProductivePracticeTime(DateTime dateStart, DateTime dateEnd)
        {
            List <object> tableAndTotal = new List <object>();

            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                tableAndTotal.Add(Meth.GetTable(MethodBase.GetCurrentMethod(), dateStart, dateEnd));
                tableAndTotal.Add("");
                return(tableAndTotal);
            }
            DataTable table = new DataTable();

            table.Columns.Add("Name");
            table.Columns.Add("Gender");
            table.Columns.Add("Age");
            table.Columns.Add("Postal Code");
            table.Columns.Add("Date of Service");
            table.Columns.Add("Primary Provider");
            table.Columns.Add("Procedure Description");
            table.Columns.Add("Non-Productive Practice Time");
            DataRow row;
            string  command = @"
            SELECT p.LName, p.FName, p.MiddleI, p.Gender, p.Zip, p.PriProv, p.Preferred, p.Birthdate, r.ProcDate, a.ProcDescript, a.Pattern
                FROM patient p 
                JOIN procedurelog r ON r.PatNum = p.PatNum
                JOIN procedurecode c ON r.CodeNum = c.CodeNum
                JOIN appointment a ON a.AptNum = r.AptNum
                WHERE c.ProcCode = 99999 OR 99998 AND 
                a.AptStatus = 5 AND 
                a.AptDateTime BETWEEN " + POut.DateT(dateStart) + @" AND " + POut.DateT(dateEnd);

            int runningTimeTotal = 0;

            DataTable raw = ReportsComplex.GetTable(command);
            Patient   pat;

            for (int i = 0; i < raw.Rows.Count; i++)
            {
                runningTimeTotal = runningTimeTotal + PatternToSeconds(raw.Rows[i]["Pattern"].ToString());
                row                                 = table.NewRow();
                pat                                 = new Patient();
                pat.LName                           = raw.Rows[i]["LName"].ToString();
                pat.FName                           = raw.Rows[i]["FName"].ToString();
                pat.MiddleI                         = raw.Rows[i]["MiddleI"].ToString();
                pat.Preferred                       = raw.Rows[i]["Preferred"].ToString();
                row["Name"]                         = pat.GetNameLF();
                row["Primary Provider"]             = Providers.GetAbbr(PIn.Long(raw.Rows[i]["PriProv"].ToString()));
                row["Gender"]                       = genderFormat(raw.Rows[i]["Gender"].ToString());
                row["Postal Code"]                  = raw.Rows[i]["Zip"].ToString();
                row["Date of Service"]              = raw.Rows[i]["ProcDate"].ToString().Substring(0, 10);
                row["Procedure Description"]        = raw.Rows[i]["ProcDescript"].ToString();
                row["Age"]                          = birthdate_to_age(raw.Rows[i]["Birthdate"].ToString());
                row["Non-Productive Practice Time"] = sec_to_time(PatternToSeconds(raw.Rows[i]["Pattern"].ToString()));
                table.Rows.Add(row);
            }

            tableAndTotal.Add(table);
            tableAndTotal.Add(sec_to_time(runningTimeTotal));

            return(tableAndTotal);
        }