///<summary>Returns all DashbaordAR(s) for the given time period. Caution, this will run aging and calculate a/r if a month within the given range is missing.
///This can take several seconds per month missing.</summary>
public static List <DashboardAR> GetAR(DateTime dateFrom, DateTime dateTo, List <DashboardAR> listDashAR)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <DashboardAR> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listDashAR));
}
//assumes that dateFrom is the first of the month.
string command;
List <DashboardAR> listRet = new List <DashboardAR>();
#if DEBUG
_elapsedTimeAR = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeAR = "Elapsed time for GetAR:\r\n";
stopWatchTotal.Start();
#endif
int months = 0;
while (dateTo >= dateFrom.AddMonths(months)) //calculate the number of months between the two dates.
{
months++;
}
for (int i = 0; i < months; i++)
{
DateTime dateLastOfMonth = dateFrom.AddMonths(i + 1).AddDays(-1);
DashboardAR dash = null;
for (int d = 0; d < listDashAR.Count; d++)
{
if (listDashAR[d].DateCalc != dateLastOfMonth)
{
continue;
}
dash = listDashAR[d];
}
if (dash != null) //we found a DashboardAR object from the database for this month, so use it.
{
listRet.Add(dash);
continue;
}
#if DEBUG
stopWatch.Restart();
#endif
//run historical aging on all patients based on the date entered.
command = "SELECT SUM(Bal_0_30+Bal_31_60+Bal_61_90+BalOver90),SUM(InsEst) "
+ "FROM (" + Ledgers.GetAgingQueryString(dateLastOfMonth, isHistoric: true) + ") guarBals";
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(command));
#if DEBUG
stopWatch.Stop();
_elapsedTimeAR += "Aging using Ledgers.GetHistoricAgingQueryString() #" + i + " : " + stopWatch.Elapsed.ToString() + "\r\n";
#endif
dash = new DashboardAR();
dash.DateCalc = dateLastOfMonth;
dash.BalTotal = PIn.Double(table.Rows[0][0].ToString());
dash.InsEst = PIn.Double(table.Rows[0][1].ToString());
DashboardARs.Insert(dash); //save it to the db for later.
if (!string.IsNullOrEmpty(PrefC.ReportingServer.Server)) //only attempt to insert into the reporting server if the reporting server is set up.
{
ReportsComplex.RunFuncOnReportServer(() => (DashboardARs.Insert(dash))); //save it to the db for later.
}
listRet.Add(dash); //and also use it now.
}
#if DEBUG
stopWatchTotal.Stop();
_elapsedTimeAR += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeAR);
}
#endif
return(listRet);
}
public static List <List <int> > GetProdInc(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
#if DEBUG
_elapsedTimeProdInc = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdInc = "Elapsed time for GetProdInc:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
string command;
command = @"SELECT procedurelog.ProcDate,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(procedurelog.ProcDate)" ;
DataTable tableProduction = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableProduction: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"SELECT AdjDate,
SUM(AdjAmt)
FROM adjustment
WHERE AdjDate >= " + POut.Date(dateFrom) + @"
AND AdjDate <= " + POut.Date(dateTo) + @"
GROUP BY MONTH(AdjDate)" ;
DataTable tableAdj = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableAdj: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
if (PrefC.GetBool(PrefName.ReportsPPOwriteoffDefaultToProcDate)) //use procdate
{
command = "SELECT "
+ "claimproc.ProcDate,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.ProcDate >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.ProcDate <= " + POut.Date(dateTo) + " "
+ "AND (claimproc.Status=1 OR claimproc.Status=4 OR claimproc.Status=0) " //received or supplemental or notreceived
+ "GROUP BY MONTH(claimproc.ProcDate)";
}
else
{
command = "SELECT "
+ "claimproc.DateCP,"
+ "SUM(claimproc.WriteOff) "
+ "FROM claimproc "
+ "WHERE claimproc.DateCP >= " + POut.Date(dateFrom) + " "
+ "AND claimproc.DateCP <= " + POut.Date(dateTo) + " "
+ "AND (claimproc.Status=1 OR claimproc.Status=4) " //Received or supplemental
+ "GROUP BY MONTH(claimproc.DateCP)";
}
DataTable tableWriteoff = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tableWriteoff: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT "
+ "paysplit.DatePay,"
+ "SUM(paysplit.SplitAmt) "
+ "FROM paysplit "
+ "WHERE paysplit.IsDiscount=0 "
+ "AND paysplit.DatePay >= " + POut.Date(dateFrom) + " "
+ "AND paysplit.DatePay <= " + POut.Date(dateTo) + " "
+ "GROUP BY MONTH(paysplit.DatePay)";
DataTable tablePay = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdInc += "tablePay: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT claimpayment.CheckDate,SUM(claimproc.InsPayamt) "
+ "FROM claimpayment,claimproc WHERE "
+ "claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum "
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " "
+ " GROUP BY claimpayment.CheckDate ORDER BY checkdate";
DataTable tableIns = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdInc += "tableIns: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdInc += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdInc);
}
#endif
//production--------------------------------------------------------------------
List <int> listInt;
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
decimal adjust = 0;
decimal inswriteoff = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProduction.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableProduction.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableProduction.Rows[j][0].ToString()).Month)
{
prod += PIn.Decimal(tableProduction.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableAdj.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableAdj.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableAdj.Rows[j][0].ToString()).Month)
{
adjust += PIn.Decimal(tableAdj.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableWriteoff.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableWriteoff.Rows[j][0].ToString()).Month)
{
inswriteoff += PIn.Decimal(tableWriteoff.Rows[j][1].ToString());
}
}
listInt.Add((int)(prod + adjust - inswriteoff));
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
//income----------------------------------------------------------------------
listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
decimal ptincome = 0;
decimal insincome = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tablePay.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tablePay.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tablePay.Rows[j][0].ToString()).Month)
{
ptincome += PIn.Decimal(tablePay.Rows[j][1].ToString());
}
}
for (int j = 0; j < tableIns.Rows.Count; j++) //
{
if (datePeriod.Year == PIn.Date(tableIns.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableIns.Rows[j][0].ToString()).Month)
{
insincome += PIn.Decimal(tableIns.Rows[j][1].ToString());
}
}
listInt.Add((int)(ptincome + insincome));
}
retVal.Add(listInt);
return(retVal);
}
///<summary>CAUTION. This does not perform all validations. Throws exceptions.</summary>
public static void Delete(Def def)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), def);
return;
}
string command;
List <string> listCommands = new List <string>();
switch (def.Category)
{
case DefCat.ClaimCustomTracking:
listCommands.Add("SELECT COUNT(*) FROM securitylog WHERE DefNum=" + POut.Long(def.DefNum));
listCommands.Add("SELECT COUNT(*) FROM claim WHERE CustomTracking=" + POut.Long(def.DefNum));
break;
case DefCat.ClaimErrorCode:
listCommands.Add("SELECT COUNT(*) FROM claimtracking WHERE TrackingErrorDefNum=" + POut.Long(def.DefNum));
break;
case DefCat.InsurancePaymentType:
listCommands.Add("SELECT COUNT(*) FROM claimpayment WHERE PayType=" + POut.Long(def.DefNum));
break;
case DefCat.SupplyCats:
listCommands.Add("SELECT COUNT(*) FROM supply WHERE Category=" + POut.Long(def.DefNum));
break;
case DefCat.AccountQuickCharge:
break; //Users can delete AcctProcQuickCharge entries. Nothing has an FKey to a AcctProcQuickCharge Def so no need to check anything.
case DefCat.AutoNoteCats:
AutoNotes.RemoveFromCategory(def.DefNum); //set any autonotes assinged to this category to 0 (unassigned), user already warned about this
listCommands.Add("SELECT COUNT(*) FROM autonote WHERE Category=" + POut.Long(def.DefNum)); //just in case update failed or concurrency issue
break;
case DefCat.WebSchedNewPatApptTypes:
//Do not let the user delete the last WebSchedNewPatApptTypes definition. Must be at least one.
command = "SELECT COUNT(*) FROM definition WHERE Category=" + POut.Int((int)DefCat.WebSchedNewPatApptTypes);
if (PIn.Int(Db.GetCount(command), false) <= 1)
{
throw new ApplicationException("NOT Allowed to delete the last def of this type.");
}
break;
default:
throw new ApplicationException("NOT Allowed to delete this type of def.");
}
for (int i = 0; i < listCommands.Count; i++)
{
if (Db.GetCount(listCommands[i]) != "0")
{
throw new ApplicationException(Lans.g("Defs", "Def is in use. Not allowed to delete."));
}
}
command = "DELETE FROM definition WHERE DefNum=" + POut.Long(def.DefNum);
Db.NonQ(command);
command = "UPDATE definition SET ItemOrder=ItemOrder-1 "
+ "WHERE Category=" + POut.Long((int)def.Category)
+ " AND ItemOrder > " + POut.Long(def.ItemOrder);
Db.NonQ(command);
}
public static List <List <int> > GetProdProvs(DateTime dateFrom, DateTime dateTo)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <List <int> > >(MethodBase.GetCurrentMethod(), dateFrom, dateTo));
}
Random rnd = new Random();
string rndStr = rnd.Next(1000000).ToString();
string command;
#if DEBUG
_elapsedTimeProdProvs = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeProdProvs = "Elapsed time for GetProdProvs:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//this table will contain approx 12x3xProv rows if there was production for each prov in each month.
command = @"CREATE TABLE tempdash" + rndStr + @" (
DatePeriod date NOT NULL,
ProvNum bigint NOT NULL,
production decimal NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//procs. Inserts approx 12xProv rows
command = @"INSERT INTO tempdash" + rndStr + @"
SELECT procedurelog.ProcDate,procedurelog.ProvNum,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits))-IFNULL(SUM(claimproc.WriteOff),0)
FROM procedurelog USE INDEX(indexPNPD)
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
AND claimproc.Status='7' /*only CapComplete writeoffs are subtracted here*/
WHERE procedurelog.ProcStatus = '2'
AND procedurelog.ProcDate >= " + POut.Date(dateFrom) + @"
AND procedurelog.ProcDate <= " + POut.Date(dateTo) + @"
GROUP BY procedurelog.ProvNum,MONTH(procedurelog.ProcDate)" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//todo 2 more tables
//get all the data as 12xProv rows
command = @"SELECT DatePeriod,ProvNum,SUM(production) prod
FROM tempdash" + rndStr + @"
GROUP BY ProvNum,MONTH(DatePeriod)" ;//this fails with date issue
DataTable tableProd = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "tableProd: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
#if DEBUG
stopWatch.Stop();
_elapsedTimeProdProvs += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
Db.NonQ(command);
command = @"SELECT ProvNum
FROM provider WHERE IsHidden=0
ORDER BY ItemOrder" ;
DataTable tableProv = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeProdProvs += "SELECT ProvNum FROM provider: " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeProdProvs += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeProdProvs);
}
#endif
List <List <int> > retVal = new List <List <int> >();
for (int p = 0; p < tableProv.Rows.Count; p++) //loop through each provider
{
long provNum = PIn.Long(tableProv.Rows[p]["ProvNum"].ToString());
List <int> listInt = new List <int>(); //12 items
for (int i = 0; i < 12; i++)
{
decimal prod = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableProd.Rows.Count; j++)
{
if (provNum == PIn.Long(tableProd.Rows[j]["ProvNum"].ToString()) &&
datePeriod.Month == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Month &&
datePeriod.Year == PIn.Date(tableProd.Rows[j]["DatePeriod"].ToString()).Year)
{
prod = PIn.Decimal(tableProd.Rows[j]["prod"].ToString());
break;
}
}
listInt.Add((int)(prod));
}
retVal.Add(listInt);
}
return(retVal);
}
///<summary>Initialize the sender helper for the given PatComms and appointments.</summary>
///<param name="clinicNum">The clinic that is doing the sending.</param>
///<param name="dtSlotStart">The date time of the time slot for which this list is being sent.</param>
///<param name="dtStartSend">The date time when the list should be sent out. This time will be adjusted if necessary.</param>
internal AsapListSender(SendMode sendMode, List <PatComm> listPatComms, long clinicNum, DateTime dtSlotStart,
DateTime dtStartSend)
{
_sendMode = sendMode;
_dictPatComms = listPatComms.GroupBy(x => x.PatNum).ToDictionary(x => x.Key, x => x.First());
_dictPatDetails = listPatComms.GroupBy(x => x.PatNum).ToDictionary(x => x.Key, x => new PatientDetail(x.First()));
_dictPatAsapComms = GetForPats(listPatComms.Select(x => x.PatNum).ToList()).GroupBy(x => x.PatNum).ToDictionary(x => x.Key, x => x.ToList());
TimeSpan timeAutoCommStart = PrefC.GetDateT(PrefName.AutomaticCommunicationTimeStart).TimeOfDay;
TimeSpan timeAutoCommEnd = PrefC.GetDateT(PrefName.AutomaticCommunicationTimeEnd).TimeOfDay;
DtSendEmail = dtStartSend; //All emails will be sent immediately.
DtStartSendText = dtStartSend;
if (PrefC.DoRestrictAutoSendWindow)
{
//If the time to start sending is before the automatic send window, set the time to start to the beginning of the send window.
if (DtStartSendText.TimeOfDay < timeAutoCommStart)
{
DtStartSendText = DtStartSendText.Date.Add(timeAutoCommStart);
IsOutsideSendWindow = true;
}
else if (DtStartSendText.TimeOfDay > timeAutoCommEnd)
{
//If the time to start sending is after the automatic send window, set the time to start to the beginning of the send window the next day.
DtStartSendText = DtStartSendText.Date.AddDays(1).Add(timeAutoCommStart);
IsOutsideSendWindow = true;
}
}
string maxTextsPrefVal = ClinicPrefs.GetPrefValue(PrefName.WebSchedAsapTextLimit, clinicNum);
_maxTextsPerDay = String.IsNullOrWhiteSpace(maxTextsPrefVal) ? int.MaxValue : PIn.Int(maxTextsPrefVal); //The pref may be set to blank to have no limit
DtTextSendEnd = DtStartSendText.Date.Add(timeAutoCommEnd);
_dtSlotStart = dtSlotStart;
SetMinutesBetweenTexts(dtSlotStart);
ListAsapComms = new List <AsapComm>();
}
///<summary>Gets a list of AsapComms (along with a few more fields) for use in the Web Sched History window. To view for all patients or clinics,
///pass in null for those parameters.</summary>
public static List <AsapCommHist> GetHist(DateTime dateFrom, DateTime dateTo, List <long> listPatNums = null, List <long> listClinicNums = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <AsapCommHist> >(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listPatNums, listClinicNums));
}
string command = @"
SELECT asapcomm.*," + DbHelper.Concat("patient.LName", "', '", "patient.FName") + @" PatientName,COALESCE(schedule.StartTime,'00:00:00') StartTime,
COALESCE(schedule.StopTime,'00:00:00') StopTime,COALESCE(schedule.SchedDate,'0001-01-01') SchedDate,
COALESCE(emailmessage.BodyText,'') EmailMessageText,COALESCE(smstomobile.MsgText,'') SMSMessageText
FROM asapcomm
INNER JOIN patient ON patient.PatNum=asapcomm.PatNum
LEFT JOIN schedule ON schedule.ScheduleNum=asapcomm.ScheduleNum
LEFT JOIN emailmessage ON emailmessage.EmailMessageNum=asapcomm.EmailMessageNum
LEFT JOIN smstomobile ON smstomobile.GuidMessage=asapcomm.GuidMessageToMobile
WHERE " + DbHelper.BetweenDates("asapcomm.DateTimeEntry", dateFrom, dateTo) + " ";
if (listPatNums != null)
{
if (listPatNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.PatNum IN(" + string.Join(",", listPatNums.Select(x => POut.Long(x))) + ") ";
}
if (listClinicNums != null)
{
if (listClinicNums.Count == 0)
{
return(new List <AsapCommHist>());
}
command += "AND asapcomm.ClinicNum IN(" + string.Join(",", listClinicNums.Select(x => POut.Long(x))) + ") ";
}
DataTable table = Db.GetTable(command);
List <AsapCommHist> listHists = Crud.AsapCommCrud.TableToList(table).Select(x => new AsapCommHist()
{
AsapComm = x
}).ToList();
for (int i = 0; i < listHists.Count; i++)
{
listHists[i].PatientName = PIn.String(table.Rows[i]["PatientName"].ToString());
listHists[i].DateTimeSlotStart = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StartTime"].ToString()));
listHists[i].DateTimeSlotEnd = PIn.Date(table.Rows[i]["SchedDate"].ToString()).Add(PIn.Time(table.Rows[i]["StopTime"].ToString()));
listHists[i].EmailMessageText = PIn.String(table.Rows[i]["EmailMessageText"].ToString());
listHists[i].SMSMessageText = PIn.String(table.Rows[i]["SMSMessageText"].ToString());
}
return(listHists);
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, long clinicNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNum));
}
DataTable table = new DataTable();
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("amountDue");
table.Columns.Add("balTotal");
table.Columns.Add("billingType");
table.Columns.Add("insEst");
table.Columns.Add("IsSent");
table.Columns.Add("lastStatement");
table.Columns.Add("mode");
table.Columns.Add("name");
table.Columns.Add("PatNum");
table.Columns.Add("payPlanDue");
table.Columns.Add("StatementNum");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT BalTotal,BillingType,FName,InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN statement s2 ON s2.PatNum=patient.PatNum "
+ "AND s2.IsSent=1 ";
if (PrefC.GetBool(PrefName.BillingIgnoreInPerson))
{
command += "AND s2.Mode_ !=1 ";
}
if (orderBy == 0) //BillingType
{
command += "LEFT JOIN definition ON patient.BillingType=definition.DefNum ";
}
command += "WHERE statement.IsSent=" + POut.Bool(isSent) + " ";
//if(dateFrom.Year>1800){
command += "AND statement.DateSent>=" + POut.Date(dateFrom) + " "; //greater than midnight this morning
//}
//if(dateFrom.Year>1800){
command += "AND statement.DateSent<" + POut.Date(dateTo.AddDays(1)) + " "; //less than midnight tonight
//}
if (clinicNum > 0)
{
command += "AND patient.ClinicNum=" + clinicNum + " ";
}
command += "GROUP BY BalTotal,BillingType,FName,InsEst,statement.IsSent,"
+ "LName,MiddleI,statement.Mode_,PayPlanDue,Preferred,"
+ "statement.PatNum,statement.StatementNum ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,LName,FName,MiddleI,PayPlanDue";
}
else
{
command += "ORDER BY LName,FName";
}
DataTable rawTable = Db.GetTable(command);
Patient pat;
StatementMode mode;
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
for (int i = 0; i < rawTable.Rows.Count; i++)
{
row = table.NewRow();
balTotal = PIn.Double(rawTable.Rows[i]["BalTotal"].ToString());
insEst = PIn.Double(rawTable.Rows[i]["InsEst"].ToString());
payPlanDue = PIn.Double(rawTable.Rows[i]["PayPlanDue"].ToString());
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = DefC.GetName(DefCat.BillingTypes, PIn.Long(rawTable.Rows[i]["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawTable.Rows[i]["IsSent"].ToString();
lastStatement = PIn.Date(rawTable.Rows[i]["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
mode = (StatementMode)PIn.Long(rawTable.Rows[i]["Mode_"].ToString());
row["mode"] = Lans.g("enumStatementMode", mode.ToString());
pat = new Patient();
pat.LName = rawTable.Rows[i]["LName"].ToString();
pat.FName = rawTable.Rows[i]["FName"].ToString();
pat.Preferred = rawTable.Rows[i]["Preferred"].ToString();
pat.MiddleI = rawTable.Rows[i]["MiddleI"].ToString();
row["name"] = pat.GetNameLF();
row["PatNum"] = rawTable.Rows[i]["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawTable.Rows[i]["StatementNum"].ToString();
rows.Add(row);
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<summary>Inserts a new document into db, creates a filename based on Cur.DocNum, and then updates the db with this filename. Also attaches the document to the current patient.</summary>
public static int Insert(Document doc, string patLF, int patNum)
{
if (PrefB.RandomKeys)
{
doc.DocNum = MiscDataB.GetKey("document", "DocNum");
}
string command = "INSERT INTO document (";
if (PrefB.RandomKeys)
{
command += "DocNum,";
}
command += "Description,DateCreated,DocCategory,PatNum,FileName,ImgType,"
+ "IsFlipped,DegreesRotated,ToothNumbers,Note,SigIsTopaz,Signature,CropX,CropY,CropW,CropH,"
+ "WindowingMin,WindowingMax,MountItemNum) VALUES(";
if (PrefB.RandomKeys)
{
command += "'" + POut.PInt(doc.DocNum) + "', ";
}
command +=
"'" + POut.PString(doc.Description) + "', "
+ POut.PDate(doc.DateCreated) + ", "
+ "'" + POut.PInt(doc.DocCategory) + "', "
+ "'" + POut.PInt(doc.PatNum) + "', "
+ "'" + POut.PString(doc.FileName) + "', " //this may simply be the extension at this point, or it may be the full filename.
+ "'" + POut.PInt((int)doc.ImgType) + "', "
+ "'" + POut.PBool(doc.IsFlipped) + "', "
+ "'" + POut.PInt(doc.DegreesRotated) + "', "
+ "'" + POut.PString(doc.ToothNumbers) + "', "
+ "'" + POut.PString(doc.Note) + "', "
+ "'" + POut.PBool(doc.SigIsTopaz) + "', "
+ "'" + POut.PString(doc.Signature) + "',"
+ "'" + POut.PInt(doc.CropX) + "',"
+ "'" + POut.PInt(doc.CropY) + "',"
+ "'" + POut.PInt(doc.CropW) + "',"
+ "'" + POut.PInt(doc.CropH) + "',"
+ "'" + POut.PInt(doc.WindowingMin) + "',"
+ "'" + POut.PInt(doc.WindowingMax) + "',"
+ "'" + POut.PInt(doc.MountItemNum) + "')";
/*+"'"+POut.PDate (LastAltered)+"', "//will later be used in backups
+"'"+POut.PBool (IsDeleted)+"')";//ditto*/
//MessageBox.Show(cmd.CommandText);
DataConnection dcon = new DataConnection();
if (PrefB.RandomKeys)
{
dcon.NonQ(command);
}
else
{
dcon.NonQ(command, true);
doc.DocNum = dcon.InsertID;
}
//If the current filename is just an extension, then assign it a unique name.
if (doc.FileName == Path.GetExtension(doc.FileName))
{
string extension = doc.FileName;
doc.FileName = "";
string s = patLF; //pat.LName+pat.FName;
for (int i = 0; i < s.Length; i++)
{
if (Char.IsLetter(s, i))
{
doc.FileName += s.Substring(i, 1);
}
}
doc.FileName += doc.DocNum.ToString() + extension; //ensures unique name
//there is still a slight chance that someone manually added a file with this name, so quick fix:
command = "SELECT FileName FROM document WHERE PatNum=" + POut.PInt(doc.PatNum);
DataTable table = dcon.GetTable(command);
string[] usedNames = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
usedNames[i] = PIn.PString(table.Rows[i][0].ToString());
}
while (IsFileNameInList(doc.FileName, usedNames))
{
doc.FileName = "x" + doc.FileName;
}
/*Document[] docList=GetAllWithPat(doc.PatNum);
* while(IsFileNameInList(doc.FileName,docList)) {
* doc.FileName="x"+doc.FileName;
* }*/
Update(doc);
}
DocAttach docAttach = new DocAttach();
docAttach.DocNum = doc.DocNum;
docAttach.PatNum = patNum;
DocAttachB.Insert(docAttach);
return(doc.DocNum);
}
public static DataTable GetListOrderBy2014(List <EhrPatListElement2014> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate, ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5))) AS Age";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
//TODO Units
from += ",ehrlab AS ehrlab" + i + ",ehrlabresult AS ehrlabresult" + i + " ";
where += "AND ehrlab" + i + ".PatNum=patient.PatNum AND ehrlab" + i + ".EhrLabNum=ehrlabresult" + i + ".EhrLabNum "; //join
where += "AND ('" + elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierID OR '"
+ elementList[i].CompareString + "'=ehrlabresult" + i + ".ObservationIdentifierIDAlt) "; //filter, LOINC of lab observation
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime >=" + POut.Date(elementList[i].StartDate) + " "; //on or after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND ehrlabresult" + i + ".ObservationDateTime <=" + POut.Date(elementList[i].EndDate) + " "; //on or before this date
}
switch (elementList[i].LabValueType)
{
//CE and CWE should be SNOMEDCT codes, string compare elementList[i].LabValue to ehrlabresult.ObservationValueCodedElementID or ObservationValueCodedElementIDAlt
case HL70125.CE:
case HL70125.CWE:
select += ",(CASE WHEN ehrlabresult" + i + ".ObservationValueCodedElementID='' THEN ehrlabresult" + i + ".ObservationValueCodedElementIDAlt ELSE ehrlabresult" + i + ".ObservationValueCodedElementID END) AS LabValue";
where += "AND (ehrlabresult" + i + ".ObservationValueCodedElementID='" + elementList[i].LabValue + "' OR "
+ "ehrlabresult" + i + ".ObservationValueCodedElementIDAlt='" + elementList[i].LabValue + "') "
+ "AND (ehrlabresult" + i + ".ValueType='CWE' OR ehrlabresult" + i + ".ValueType='CE') ";
break;
//DT is stored as a string in ehrlabresult.ObservationValueDateTime as YYYY[MM[DD]]
case HL70125.DT:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateFormatColumn("RPAD(ehrlabresult"+i+".ObservationValueDateTime,8,'01')","%m/%d/%Y");
where += "AND " + DbHelper.DateColumn("RPAD(ehrlabresult" + i + ".ObservationValueDateTime,8,'01')")
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='DT' ";
break;
//TS is YYYYMMDDHHMMSS, string compare
case HL70125.TS:
select += ",ehrlabresult" + i + ".ObservationValueDateTime "; //+DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s");
where += "AND ehrlabresult" + i + ".ObservationValueDateTime " //+POut.DateT(PIn.DateT(DbHelper.DateTFormatColumn("ehrlabresult"+i+".ObservationValueDateTime","%m/%d/%Y %H:%i:%s")))
+ GetOperandText(elementList[i].Operand) + "'" + POut.String(elementList[i].LabValue) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TS' ";
break;
//00:00:00
case HL70125.TM:
select += ",ehrlabresult" + i + ".ObservationValueTime";
where += "AND ehrlabresult" + i + ".ObservationValueTime" + GetOperandText(elementList[i].Operand) + "'" + POut.TSpan(PIn.TSpan(elementList[i].LabValue)) + "' "
+ "AND ehrlabresult" + i + ".ValueType='TM' ";
break;
case HL70125.SN:
select += ",CONCAT(CONCAT(CONCAT(ehrlabresult" + i + ".ObservationValueComparator,ehrlabresult" + i + ".ObservationValueNumber1),ehrlabresult" + i + ".ObservationValueSeparatorOrSuffix),ehrlabresult" + i + ".ObservationValueNumber2)";
where += "AND ehrlabresult" + i + ".ValueType='SN' ";
break;
case HL70125.NM:
select += ",ehrlabresult" + i + ".ObservationValueNumeric";
where += "AND ehrlabresult" + i + ".ObservationValueNumeric" + GetOperandText(elementList[i].Operand) + POut.Double(PIn.Double(elementList[i].LabValue)) + " "
+ "AND ehrlabresult" + i + ".ValueType='NM' ";
break;
case HL70125.FT:
case HL70125.ST:
case HL70125.TX:
select += ",ehrlabresult" + i + ".ObservationValueText";
//where+="AND ehrlabresult"+i+".ObservationValueText"+GetOperandText(elementList[i].Operand)+POut.String(elementList[i].LabValue)+" "
where += "AND (ehrlabresult" + i + ".ValueType='FT' OR ehrlabresult" + i + ".ValueType='ST' OR ehrlabresult" + i + ".ValueType='TX') ";
break;
}
select += ",ehrlabresult" + i + ".ObservationDateTime ";
//select+=",labresult"+i+".ObsValue,labresult"+i+".DateTimeTest";//format column name when filling grid.
//from+=",labresult AS labresult"+i+", labpanel AS labpanel"+i;
//where+="AND labpanel"+i+".LabpanelNum=labresult"+i+".LabpanelNum AND patient.PatNum=labpanel"+i+".PatNum ";//join
//where+="AND labresult"+i+".TestId='"+elementList[i].CompareString+"' "
// +"AND labresult"+i+".ObsValue"+GetOperandText(elementList[i].Operand)+"'"+PIn.String(elementList[i].LabValue)+"' ";//filter
//if(elementList[i].StartDate!=null && elementList[i].StartDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest>"+POut.Date(elementList[i].StartDate)+" ";//after this date
//}
//if(elementList[i].EndDate!=null && elementList[i].EndDate.Year>1880) {
// where+="AND labresult"+i+".DateTimeTest<"+POut.Date(elementList[i].EndDate)+" ";//before this date
//}
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",medicationpat" + i + ".DateStart"; //Name of medication will be in column title.
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND medicationpat" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",disease" + i + ".DateStart"; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND (diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' OR diseasedef" + i + ".SnomedCode='" + PIn.String(elementList[i].CompareString) + "') "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND disease" + i + ".DateStart<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.Allergy: //-----------------------------------------------------------------------------------------------------------------------------
select += ",allergy" + i + ".DateAdverseReaction"; //Name of allergy will be in column title.
from += ",allergy AS allergy" + i + ", allergydef AS allergydef" + i;
where += "AND allergydef" + i + ".AllergyDefNum=allergy" + i + ".AllergyDefNum AND allergy" + i + ".PatNum=patient.PatNum "; //join
where += "AND allergydef" + i + ".Description='" + PIn.String(elementList[i].CompareString) + "' "; //filter
if (elementList[i].StartDate != null && elementList[i].StartDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction>" + POut.Date(elementList[i].StartDate) + " "; //after this date
}
if (elementList[i].EndDate != null && elementList[i].EndDate.Year > 1880)
{
where += "AND allergy" + i + ".DateAdverseReaction<" + POut.Date(elementList[i].EndDate) + " "; //before this date
}
break;
case EhrRestrictionType.CommPref: //----------------------------------------------------------------------------------------------------------------------------
select += ",patient.PreferContactConfidential";
from += ""; //only selecting from patient table
where += "AND patient.PreferContactConfidential=" + PIn.Int(contactMethodHelper(elementList[i].CompareString)) + " ";
break;
default:
//should never happen.
continue;
}
}
string command = select + " " + from + " " + where;
return(Db.GetTable(command));
}
///<summary>This is a potential fix to be backported to 13.2 so that patient lists can be used for MU1 2013. on large databases these queries take way to long to run. (At least several minutes).</summary>
public static DataTable GetListOrderBy2014Retro(List <EhrPatListElement> elementList)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), elementList));
}
DataTable table = new DataTable();
string select = "SELECT patient.PatNum,patient.LName,patient.FName";
string from = "FROM patient";
string where = "WHERE TRUE "; //Makes formatting easier when adding additional clauses because they will all be AND clauses.
for (int i = 0; i < elementList.Count; i++)
{
switch (elementList[i].Restriction)
{
case EhrRestrictionType.Birthdate: //---------------------------------------------------------------------------------------------------------------------------
select += ",patient.BirthDate ";
from += ""; //only selecting from patient table
where += "AND ((YEAR(CURDATE())-YEAR(DATE(patient.Birthdate))) - (RIGHT(CURDATE(),5)<RIGHT(DATE(patient.Birthdate),5)))" + GetOperandText(elementList[i].Operand) + "" + PIn.String(elementList[i].CompareString) + " ";
break;
case EhrRestrictionType.Gender: //------------------------------------------------------------------------------------------------------------------------------
select += ",patient.Gender"; //will look odd if user adds multiple gender columns, enum needs to be "decoded" when filling grid.
break;
case EhrRestrictionType.LabResult: //---------------------------------------------------------------------------------------------------------------------------
select += ",labresult" + i + ".ObsValue"; //format column name when filling grid.
from += ",labresult AS labresult" + i + ", labpanel AS labpanel" + i;
where += "AND labpanel" + i + ".LabpanelNum=labresult" + i + ".LabpanelNum AND patient.PatNum=labpanel" + i + ".PatNum "; //join
where += "AND labresult" + i + ".TestId='" + elementList[i].CompareString + "' "
+ "AND labresult" + i + ".ObsValue" + GetOperandText(elementList[i].Operand) + "'" + PIn.String(elementList[i].LabValue) + "' "; //filter
break;
case EhrRestrictionType.Medication: //--------------------------------------------------------------------------------------------------------------------------
select += ",'X'"; //select+=",medicationpat"+i+".DateStart";
from += ",medication AS medication" + i + ", medicationpat AS medicationpat" + i;
where += "AND medicationpat" + i + ".PatNum=patient.PatNum "; //join
//This is unusual. Part of the join logic is in the code below because medicationPat.MedicationNum might be 0 if it came from newcrop.
where += "AND ((medication" + i + ".MedicationNum=MedicationPat" + i + ".MedicationNum AND medication" + i + ".MedName LIKE '%" + PIn.String(elementList[i].CompareString) + "%') "
+ " OR (medication" + i + ".MedicationNum=0 AND medicationpat" + i + ".MedDescript LIKE '%" + PIn.String(elementList[i].CompareString) + "%')) ";
break;
case EhrRestrictionType.Problem: //-----------------------------------------------------------------------------------------------------------------------------
select += ",Concat('(',diseasedef" + i + ".ICD9Code,') - ',diseasedef" + i + ".DiseaseName) as Disease" + i + " "; //Name of problem will be in column title.
from += ",disease AS disease" + i + ", diseasedef AS diseasedef" + i;
where += "AND diseasedef" + i + ".DiseaseDefNum=disease" + i + ".DiseaseDefNum AND disease" + i + ".PatNum=patient.PatNum "; //join
where += "AND diseasedef" + i + ".ICD9Code='" + PIn.String(elementList[i].CompareString) + "' "; //filter
break;
default:
//Can happen because EhrRestrictionType was expanded for 2014.
//If we reach this point in the code, we will effectively just ignore the pat list element.
continue;
}
}
string command = select + " " + from + " " + where + " ORDER BY Patient.LName, Patient.FName";
return(Db.GetTable(command));
}
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.
///Does not work for Oracle, due to some MySQL specific commands inside.</summary>
public static long MakeABackup()
{
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
//UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users.
//GetStringNoCache() will return blank if pref does not exist.
if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra")
{
return(0);
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon = new DataConnection();
string command = "SELECT database()";
DataTable table = dcon.GetTable(command);
string oldDb = PIn.String(table.Rows[0][0].ToString());
string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy");
command = "SHOW DATABASES";
table = dcon.GetTable(command);
string[] databases = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
databases[i] = table.Rows[i][0].ToString();
}
if (Contains(databases, newDb)) //if the new database name already exists
//find a unique one
{
int uniqueID = 1;
string originalNewDb = newDb;
do
{
newDb = originalNewDb + "_" + uniqueID.ToString();
uniqueID++;
}while(Contains(databases, newDb));
}
command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8";
dcon.NonQ(command);
command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table = dcon.GetTable(command);
string[] tableName = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
tableName[i] = table.Rows[i][0].ToString();
}
//switch to using the new database
DataConnection newDcon = new DataConnection(newDb);
for (int i = 0; i < tableName.Length; i++)
{
//Alert anyone that cares that we are backing up this table.
ODEvent.Fire(new ODEventArgs("BackupProgress", Lans.g("MiscData", "Backing up table") + ": " + tableName[i]));
command = "SHOW CREATE TABLE `" + oldDb + "`.`" + tableName[i] + "`"; //also works with views. Added backticks around table name for unusual characters.
table = newDcon.GetTable(command);
command = PIn.ByteArray(table.Rows[0][1]);
newDcon.NonQ(command); //this has to be run using connection with new database
command = "INSERT INTO `" + newDb + "`.`" + tableName[i] + "` "
+ "SELECT * FROM `" + oldDb + "`.`" + tableName[i] + "`"; //Added backticks around table name for unusual characters.
newDcon.NonQ(command);
}
return(0);
}
/// <summary>Only Called only from FormPayment.butOK click. Only called if the user did not enter any splits. Usually just adds one split for the current patient. But if that would take the balance negative, then it loops through all other family members and creates splits for them. It might still take the current patient negative once all other family members are zeroed out.</summary>
public static List <PaySplit> Allocate(Payment pay) //double amtTot,int patNum,Payment payNum){
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <PaySplit> >(MethodBase.GetCurrentMethod(), pay));
}
string command =
"SELECT Guarantor FROM patient "
+ "WHERE PatNum = " + POut.Long(pay.PatNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return(new List <PaySplit>());
}
command =
"SELECT patient.PatNum,EstBalance,PriProv,SUM(InsPayEst)+SUM(Writeoff) insEst_ "
+ "FROM patient "
+ "LEFT JOIN claimproc ON patient.PatNum=claimproc.PatNum "
+ "AND Status=0 " //NotReceived
+ "WHERE Guarantor = " + table.Rows[0][0].ToString() + " "
+ "GROUP BY patient.PatNum,EstBalance,PriProv";
//+" ORDER BY PatNum!="+POut.PInt(pay.PatNum);//puts current patient in position 0 //Oracle does not allow
table = Db.GetTable(command);
List <Patient> pats = new List <Patient>();
Patient pat;
//first, put the current patient at position 0.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
}
//then, do all the rest of the patients.
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i]["PatNum"].ToString() == pay.PatNum.ToString())
{
continue;
}
pat = new Patient();
pat.PatNum = PIn.Long(table.Rows[i][0].ToString());
pat.EstBalance = PIn.Double(table.Rows[i][1].ToString());
if (!PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
pat.EstBalance -= PIn.Double(table.Rows[i]["insEst_"].ToString());
}
pat.PriProv = PIn.Long(table.Rows[i][2].ToString());
pats.Add(pat.Copy());
}
//first calculate all the amounts
double amtRemain = pay.PayAmt; //start off with the full amount
double[] amtSplits = new double[pats.Count];
//loop through each family member, starting with current
for (int i = 0; i < pats.Count; i++)
{
if (pats[i].EstBalance == 0 || pats[i].EstBalance < 0)
{
continue; //don't apply paysplits to anyone with a negative balance
}
if (amtRemain < pats[i].EstBalance) //entire remainder can be allocated to this patient
{
amtSplits[i] = amtRemain;
amtRemain = 0;
break;
}
else //amount remaining is more than or equal to the estBal for this family member
{
amtSplits[i] = pats[i].EstBalance;
amtRemain -= pats[i].EstBalance;
}
}
//add any remainder to the split for this patient
amtSplits[0] += amtRemain;
//now create a split for each non-zero amount
PaySplit PaySplitCur;
List <PaySplit> retVal = new List <PaySplit>();
for (int i = 0; i < pats.Count; i++)
{
if (amtSplits[i] == 0)
{
continue;
}
PaySplitCur = new PaySplit();
PaySplitCur.PatNum = pats[i].PatNum;
PaySplitCur.PayNum = pay.PayNum;
PaySplitCur.DatePay = pay.PayDate;
PaySplitCur.ClinicNum = pay.ClinicNum;
PaySplitCur.ProvNum = Patients.GetProvNum(pats[i]);
PaySplitCur.SplitAmt = Math.Round(amtSplits[i], CultureInfo.CurrentCulture.NumberFormat.CurrencyDecimalDigits);
//PaySplitCur.InsertOrUpdate(true);
retVal.Add(PaySplitCur);
}
//finally, adjust each EstBalance, but no need to do current patient
//This no longer works here. Must do it when closing payment window somehow
/*for(int i=1;i<pats.Length;i++){
* if(amtSplits[i]==0){
* continue;
* }
* command="UPDATE patient SET EstBalance=EstBalance-"+POut.PDouble(amtSplits[i])
+" WHERE PatNum="+POut.PInt(pats[i].PatNum);
* Db.NonQ(command);
* }*/
return(retVal);
}
private static DataTable GetProgNotes(int patNum, bool isAuditMode)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("ProgNotes");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("ADACode");
table.Columns.Add("aptDateTime", typeof(DateTime));
table.Columns.Add("colorBackG");
table.Columns.Add("colorText");
table.Columns.Add("CommlogNum");
table.Columns.Add("description");
table.Columns.Add("dx");
table.Columns.Add("Dx");
table.Columns.Add("note");
table.Columns.Add("Priority");
table.Columns.Add("procDate");
table.Columns.Add("ProcDate", typeof(DateTime));
table.Columns.Add("procFee");
table.Columns.Add("ProcNum");
table.Columns.Add("ProcNumLab");
table.Columns.Add("procStatus");
table.Columns.Add("ProcStatus");
table.Columns.Add("prov");
table.Columns.Add("RxNum");
table.Columns.Add("signature");
table.Columns.Add("Surf");
table.Columns.Add("toothNum");
table.Columns.Add("ToothNum");
table.Columns.Add("ToothRange");
table.Columns.Add("user");
//table.Columns.Add("");
//but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now.
List <DataRow> rows = new List <DataRow>();
//Procedures-----------------------------------------------------------------------------------------------------
string command = "SELECT ProcDate,ProcStatus,ToothNum,Surf,Dx,procedurelog.ADACode,ProcNum,procedurecode.Descript,"
+ "provider.Abbr,ProcFee,ProcNumLab,appointment.AptDateTime,Priority,ToothRange "
+ "FROM procedurelog "
+ "LEFT JOIN procedurecode ON procedurecode.ADACode=procedurelog.ADACode "
+ "LEFT JOIN provider ON provider.ProvNum=procedurelog.ProvNum "
+ "LEFT JOIN appointment ON appointment.AptNum=procedurelog.AptNum "
+ "AND (appointment.AptStatus=" + POut.PInt((int)ApptStatus.Scheduled)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.ASAP)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Broken)
+ " OR appointment.AptStatus=" + POut.PInt((int)ApptStatus.Complete)
+ ") WHERE procedurelog.PatNum=" + POut.PInt(patNum);
if (!isAuditMode)
{
command += " AND ProcStatus !=6"; //don't include deleted
}
command += " ORDER BY ProcDate"; //we'll just have to reorder it anyway
DataTable rawProcs = dcon.GetTable(command);
command = "SELECT ProcNum,EntryDateTime,UserNum,Note,"
+ "CASE WHEN Signature!='' THEN 1 ELSE 0 END AS SigPresent "
+ "FROM procnote WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY EntryDateTime"; // but this helps when looping for notes
DataTable rawNotes = dcon.GetTable(command);
DateTime dateT;
List <DataRow> labRows = new List <DataRow>(); //Canadian lab procs, which must be added in a loop at the very end.
for (int i = 0; i < rawProcs.Rows.Count; i++)
{
row = table.NewRow();
row["ADACode"] = rawProcs.Rows[i]["ADACode"].ToString();
row["aptDateTime"] = PIn.PDateT(rawProcs.Rows[i]["AptDateTime"].ToString());
row["colorBackG"] = Color.White.ToArgb();
if (((DateTime)row["aptDateTime"]).Date == DateTime.Today)
{
row["colorBackG"] = DefB.Long[(int)DefCat.MiscColors][6].ItemColor.ToArgb().ToString();
}
switch ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString()))
{
case ProcStat.TP:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][0].ItemColor.ToArgb().ToString();
break;
case ProcStat.C:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][1].ItemColor.ToArgb().ToString();
break;
case ProcStat.EC:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][2].ItemColor.ToArgb().ToString();
break;
case ProcStat.EO:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][3].ItemColor.ToArgb().ToString();
break;
case ProcStat.R:
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][4].ItemColor.ToArgb().ToString();
break;
case ProcStat.D:
row["colorText"] = Color.Black.ToArgb().ToString();
break;
}
row["CommlogNum"] = 0;
row["description"] = rawProcs.Rows[i]["Descript"].ToString();
row["dx"] = DefB.GetValue(DefCat.Diagnosis, PIn.PInt(rawProcs.Rows[i]["Dx"].ToString()));
row["Dx"] = rawProcs.Rows[i]["Dx"].ToString();
//note-----------------------------------------------------------------------------------------------------------
if (isAuditMode) //we will include all notes for each proc. We will concat and make readable.
{
for (int n = 0; n < rawNotes.Rows.Count; n++) //loop through each note
{
if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString())
{
continue;
}
if (row["Note"].ToString() != "") //if there is an existing note
{
row["note"] += "\r\n------------------------------------------------------\r\n"; //start a new line
}
row["note"] += PIn.PDateT(rawNotes.Rows[n]["EntryDateTime"].ToString()).ToString();
row["note"] += " " + UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["note"] += " " + Lan.g("ChartModule", "(signed)");
}
row["note"] += "\r\n" + rawNotes.Rows[n]["Note"].ToString();
}
}
else //we just want the most recent note
{
for (int n = rawNotes.Rows.Count - 1; n >= 0; n--) //loop through each note, backwards.
{
if (rawProcs.Rows[i]["ProcNum"].ToString() != rawNotes.Rows[n]["ProcNum"].ToString())
{
continue;
}
row["user"] = UserodB.GetName(PIn.PInt(rawNotes.Rows[n]["UserNum"].ToString()));
row["note"] = rawNotes.Rows[n]["Note"].ToString();
if (rawNotes.Rows[n]["SigPresent"].ToString() == "1")
{
row["signature"] = Lan.g("ChartModule", "Signed");
}
else
{
row["signature"] = "";
}
break; //out of note loop.
}
}
row["Priority"] = rawProcs.Rows[i]["Priority"].ToString();
dateT = PIn.PDateT(rawProcs.Rows[i]["ProcDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["procFee"] = PIn.PDouble(rawProcs.Rows[i]["ProcFee"].ToString()).ToString("F");
row["ProcNum"] = rawProcs.Rows[i]["ProcNum"].ToString();
row["ProcNumLab"] = rawProcs.Rows[i]["ProcNumLab"].ToString();
row["procStatus"] = Lan.g("enumProcStat", ((ProcStat)PIn.PInt(rawProcs.Rows[i]["ProcStatus"].ToString())).ToString());
row["ProcStatus"] = rawProcs.Rows[i]["ProcStatus"].ToString();
row["prov"] = rawProcs.Rows[i]["Abbr"].ToString();
row["RxNum"] = 0;
row["Surf"] = rawProcs.Rows[i]["Surf"].ToString();
row["toothNum"] = Tooth.ToInternat(rawProcs.Rows[i]["ToothNum"].ToString());
row["ToothNum"] = rawProcs.Rows[i]["ToothNum"].ToString();
row["ToothRange"] = rawProcs.Rows[i]["ToothRange"].ToString();
if (rawProcs.Rows[i]["ProcNumLab"].ToString() == "0") //normal proc
{
rows.Add(row);
}
else
{
row["description"] = "-----" + row["description"].ToString();
labRows.Add(row); //these will be added in the loop at the end
}
}
//Commlog-----------------------------------------------------------------------------------------------------------
command = "SELECT CommlogNum,CommDateTime,CommType,Note FROM commlog WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY CommDateTime";
DataTable rawComm = dcon.GetTable(command);
for (int i = 0; i < rawComm.Rows.Count; i++)
{
row = table.NewRow();
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][6].ItemColor.ToArgb().ToString();
row["CommlogNum"] = rawComm.Rows[i]["CommlogNum"].ToString();
row["description"] = Lan.g("ChartModule", "Comm - ")
+ Lan.g("enumCommItemType", ((CommItemType)PIn.PInt(rawComm.Rows[i]["CommType"].ToString())).ToString());
row["note"] = rawComm.Rows[i]["Note"].ToString();
dateT = PIn.PDateT(rawComm.Rows[i]["CommDateTime"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["ProcNum"] = 0;
row["RxNum"] = 0;
rows.Add(row);
}
//Rx------------------------------------------------------------------------------------------------------------------
command = "SELECT RxNum,RxDate,Drug,Disp,ProvNum,Notes FROM rxpat WHERE PatNum=" + POut.PInt(patNum)
+ " ORDER BY RxDate";
DataTable rawRx = dcon.GetTable(command);
for (int i = 0; i < rawRx.Rows.Count; i++)
{
row = table.NewRow();
row["colorBackG"] = Color.White.ToArgb();
row["colorText"] = DefB.Long[(int)DefCat.ProgNoteColors][5].ItemColor.ToArgb().ToString();
row["CommlogNum"] = 0;
row["description"] = Lan.g("ChartModule", "Rx - ") + rawRx.Rows[i]["Drug"].ToString() + " - #" + rawRx.Rows[i]["Disp"].ToString();
row["note"] = rawRx.Rows[i]["Notes"].ToString();
dateT = PIn.PDate(rawRx.Rows[i]["RxDate"].ToString());
if (dateT.Year < 1880)
{
row["procDate"] = "";
}
else
{
row["procDate"] = dateT.ToShortDateString();
}
row["ProcDate"] = dateT;
row["ProcNum"] = 0;
row["RxNum"] = rawRx.Rows[i]["RxNum"].ToString();
rows.Add(row);
}
//Sorting
rows.Sort(CompareChartRows);
//Canadian lab procedures need to come immediately after their corresponding proc---------------------------------
for (int i = 0; i < labRows.Count; i++)
{
for (int r = 0; r < rows.Count; r++)
{
if (rows[r]["ProcNum"].ToString() == labRows[i]["ProcNumLab"].ToString())
{
rows.Insert(r + 1, labRows[i]);
break;
}
}
}
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
public static List <JobEmail> GetCustomerEmails(Job job)
{
Dictionary <long, JobEmail> retVal = new Dictionary <long, JobEmail>();
foreach (JobQuote jobQuote in job.ListJobQuotes)
{
long patNum = jobQuote.PatNum;
if (!retVal.ContainsKey(patNum))
{
Patient pat = Patients.GetPat(patNum);
if (pat == null)
{
continue;
}
string phones = "Hm:" + pat.HmPhone + "\r\nMo:" + pat.WirelessPhone + "\r\nWk:" + pat.WkPhone;
retVal[patNum] = new JobEmail()
{
Pat = pat, EmailAddress = pat.Email, PhoneNums = phones, IsSend = false
};
}
retVal[patNum].IsQuote = true;
}
foreach (JobLink jobLink in job.ListJobLinks.FindAll(x => x.LinkType == JobLinkType.Task))
{
Task task = Tasks.GetOne(jobLink.FKey);
if (task == null || task.KeyNum == 0 || task.ObjectType != TaskObjectType.Patient)
{
continue;
}
long patNum = task.KeyNum;
if (!retVal.ContainsKey(patNum))
{
Patient pat = Patients.GetPat(patNum);
if (pat == null)
{
continue;
}
string phones = "Hm:" + pat.HmPhone + "\r\nMo:" + pat.WirelessPhone + "\r\nWk:" + pat.WkPhone;
retVal[patNum] = new JobEmail()
{
Pat = pat, EmailAddress = pat.Email, PhoneNums = phones, IsSend = true
};
}
retVal[patNum].IsTask = true;
}
foreach (JobLink jobLink in job.ListJobLinks.FindAll(x => x.LinkType == JobLinkType.Request))
{
DataTable tableFR = GetFeatureRequestContact(jobLink.FKey);
foreach (DataRow row in tableFR.Rows)
{
long patNum = PIn.Long(row["ODPatNum"].ToString());
Patient pat = Patients.GetPat(patNum);
if (!retVal.ContainsKey(patNum))
{
string phones = "Hm:" + row["HmPhone"].ToString() + "\r\nMo:" + row["WirelessPhone"].ToString() + "\r\nWk:" + row["WkPhone"].ToString();
retVal[patNum] = new JobEmail()
{
Pat = pat, EmailAddress = row["Email"].ToString(), PhoneNums = phones, IsSend = true
};
}
retVal[patNum].IsFeatureReq = true;
retVal[patNum].Votes += PIn.Int(row["Votes"].ToString());
retVal[patNum].PledgeAmount += PIn.Double(row["AmountPledged_"].ToString());
}
}
return(retVal.Select(x => x.Value).ToList());
}
///<summary>Gets all of the connection setting information from the FreeDentalConfig.xml. Throws exceptions.</summary>
public static void GetChooseDatabaseConnectionSettings(out CentralConnection centralConnection, out string connectionString, out YN noShow
, out DatabaseType dbType, out List <string> listAdminCompNames, out bool useDynamicMode, out bool allowAutoLogin)
{
//No remoting role check; out parameters are used.
centralConnection = new CentralConnection();
connectionString = "";
noShow = YN.Unknown;
dbType = DatabaseType.MySql;
listAdminCompNames = new List <string>();
useDynamicMode = false;
allowAutoLogin = true;
string xmlPath = ODFileUtils.CombinePaths(Application.StartupPath, "FreeDentalConfig.xml");
#region Permission Check
//Improvement should be made here to avoid requiring admin priv.
//Search path should be something like this:
//1. /home/username/.opendental/config.xml (or corresponding user path in Windows)
//2. /etc/opendental/config.xml (or corresponding machine path in Windows) (should be default for new installs)
//3. Application Directory/FreeDentalConfig.xml (read-only if user is not admin)
if (!File.Exists(xmlPath))
{
FileStream fs;
try {
fs = File.Create(xmlPath);
}
catch (Exception) {
//No translation right here because we typically do not have a database connection yet.
throw new ODException("The very first time that the program is run, it must be run as an Admin. "
+ "If using Vista, right click, run as Admin.");
}
fs.Close();
}
#endregion
XmlDocument document = new XmlDocument();
try {
document.Load(xmlPath);
XPathNavigator Navigator = document.CreateNavigator();
XPathNavigator nav;
#region Nodes with No UI
//Always look for these settings first in order to always preserve them correctly.
nav = Navigator.SelectSingleNode("//AdminCompNames");
if (nav != null)
{
listAdminCompNames.Clear(); //this method gets called more than once
XPathNodeIterator navIterator = nav.SelectChildren(XPathNodeType.All);
for (int i = 0; i < navIterator.Count; i++)
{
navIterator.MoveNext();
listAdminCompNames.Add(navIterator.Current.Value); //Add this computer name to the list.
}
}
//See if there's a UseXWebTestGateway
nav = Navigator.SelectSingleNode("//UseXWebTestGateway");
if (nav != null)
{
OpenDentBusiness.WebTypes.Shared.XWeb.XWebs.UseXWebTestGateway = nav.Value.ToLower() == "true";
}
//See if there's a AllowAutoLogin node
nav = Navigator.SelectSingleNode("//AllowAutoLogin");
if (nav != null && nav.Value.ToLower() == "false")
{
//Node must be specifically set to false to change the allowAutoLogin bool.
allowAutoLogin = false;
}
#endregion
#region Nodes from Choose Database Window
#region Nodes with No Group Box
//Database Type
nav = Navigator.SelectSingleNode("//DatabaseType");
dbType = DatabaseType.MySql;
if (nav != null && nav.Value == "Oracle")
{
dbType = DatabaseType.Oracle;
}
//ConnectionString
nav = Navigator.SelectSingleNode("//ConnectionString");
if (nav != null)
{
//If there is a ConnectionString, then use it.
connectionString = nav.Value;
}
//UseDynamicMode
nav = Navigator.SelectSingleNode("//UseDynamicMode");
if (nav != null)
{
//If there is a node, take in its value
useDynamicMode = PIn.Bool(nav.Value);
}
#endregion
#region Connection Settings Group Box
//See if there's a DatabaseConnection
nav = Navigator.SelectSingleNode("//DatabaseConnection");
if (nav != null)
{
//If there is a DatabaseConnection, then use it.
centralConnection.ServerName = nav.SelectSingleNode("ComputerName").Value;
centralConnection.DatabaseName = nav.SelectSingleNode("Database").Value;
centralConnection.MySqlUser = nav.SelectSingleNode("User").Value;
centralConnection.MySqlPassword = nav.SelectSingleNode("Password").Value;
XPathNavigator encryptedPwdNode = nav.SelectSingleNode("MySQLPassHash");
//If the Password node is empty, but there is a value in the MySQLPassHash node, decrypt the node value and use that instead
string _decryptedPwd;
if (centralConnection.MySqlPassword == "" &&
encryptedPwdNode != null &&
encryptedPwdNode.Value != "" &&
CDT.Class1.Decrypt(encryptedPwdNode.Value, out _decryptedPwd))
{
//decrypted value could be an empty string, which means they don't have a password set, so textPassword will be an empty string
centralConnection.MySqlPassword = _decryptedPwd;
}
XPathNavigator noshownav = nav.SelectSingleNode("NoShowOnStartup");
if (noshownav != null)
{
if (noshownav.Value == "True")
{
noShow = YN.Yes;
}
else
{
noShow = YN.No;
}
}
}
#endregion
#region Connect to Middle Tier Group Box
nav = Navigator.SelectSingleNode("//ServerConnection");
/* example:
* <ServerConnection>
* <URI>http://server/OpenDentalServer/ServiceMain.asmx</URI>
* <UsingEcw>True</UsingEcw>
* </ServerConnection>
*/
if (nav != null)
{
//If there is a ServerConnection, then use it.
centralConnection.ServiceURI = nav.SelectSingleNode("URI").Value;
XPathNavigator ecwnav = nav.SelectSingleNode("UsingEcw");
if (ecwnav != null && ecwnav.Value == "True")
{
noShow = YN.Yes;
centralConnection.WebServiceIsEcw = true;
}
XPathNavigator autoLoginNav = nav.SelectSingleNode("UsingAutoLogin");
//Retrieve the user from the Windows password vault for the current ServiceURI that was last to successfully single sign on.
//If credentials are found then log the user in. This is safe to do because the password vault is unique per Windows user and workstation.
//There is code elsewhere that will handle password vault management (only storing the last valid single sign on per ServiceURI).
//allowAutoLogin defaults to true unless the office specifically set it to false.
if (autoLoginNav != null && autoLoginNav.Value == "True" && allowAutoLogin)
{
if (!PasswordVaultWrapper.TryRetrieveUserName(centralConnection.ServiceURI, out centralConnection.OdUser))
{
centralConnection.OdUser = nav.SelectSingleNode("User").Value; //No username found. Use the User in FreeDentalConfig (preserve old behavior).
}
//Get the user's password from Windows Credential Manager
try {
centralConnection.OdPassword =
PasswordVaultWrapper.RetrievePassword(centralConnection.ServiceURI, centralConnection.OdUser);
//Must set this so FormChooseDatabase() does not launch
noShow = YN.Yes;
centralConnection.IsAutomaticLogin = true;
}
catch (Exception ex) {
ex.DoNothing(); //We still want to display the server URI and the user name if getting the password fails.
}
}
}
#endregion
#endregion
}
catch (Exception) {
//Common error: root element is missing
centralConnection.ServerName = "localhost";
if (ODBuild.IsTrial())
{
centralConnection.DatabaseName = "demo";
}
else
{
centralConnection.DatabaseName = "opendental";
}
centralConnection.MySqlUser = "******";
}
}
