///<summary>Also deletes all journal entries for the transaction. Will later throw an error if journal entries attached to any reconciles. Be sure to surround with try-catch.</summary>
public static void Delete(Transaction trans)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), trans);
return;
}
string command = "SELECT IsLocked FROM journalentry j, reconcile r WHERE j.TransactionNum=" + POut.Long(trans.TransactionNum)
+ " AND j.ReconcileNum = r.ReconcileNum";
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
if (PIn.Int(table.Rows[0][0].ToString()) == 1)
{
throw new ApplicationException(Lans.g("Transactions", "Not allowed to delete transactions because it is attached to a reconcile that is locked."));
}
}
command = "DELETE FROM journalentry WHERE TransactionNum=" + POut.Long(trans.TransactionNum);
Db.NonQ(command);
command = "DELETE FROM transaction WHERE TransactionNum=" + POut.Long(trans.TransactionNum);
Db.NonQ(command);
}
///<summary></summary>
public static List <LabTurnaround> GetForLab(long laboratoryNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <LabTurnaround> >(MethodBase.GetCurrentMethod(), laboratoryNum));
}
string command = "SELECT * FROM labturnaround WHERE LaboratoryNum=" + POut.Long(laboratoryNum);
DataTable table = Db.GetTable(command);
List <LabTurnaround> retVal = new List <LabTurnaround>();
LabTurnaround lab;
for (int i = 0; i < table.Rows.Count; i++)
{
lab = new LabTurnaround();
lab.LabTurnaroundNum = PIn.Long(table.Rows[i][0].ToString());
lab.LaboratoryNum = PIn.Long(table.Rows[i][1].ToString());
lab.Description = PIn.String(table.Rows[i][2].ToString());
lab.DaysPublished = PIn.Int(table.Rows[i][3].ToString());
lab.DaysActual = PIn.Int(table.Rows[i][4].ToString());
retVal.Add(lab);
}
return(retVal);
}
///<summary></summary>
private static List <ClaimPaySplit> ClaimPaySplitTableToList(DataTable table)
{
//No need to check RemotingRole; no call to db.
List <ClaimPaySplit> splits = new List <ClaimPaySplit>();
ClaimPaySplit split;
for (int i = 0; i < table.Rows.Count; i++)
{
split = new ClaimPaySplit();
split.DateClaim = PIn.Date(table.Rows[i]["DateService"].ToString());
split.ProvAbbr = Providers.GetAbbr(PIn.Long(table.Rows[i]["ProvTreat"].ToString()));
split.PatName = PIn.String(table.Rows[i]["patName_"].ToString());
split.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
split.Carrier = PIn.String(table.Rows[i]["CarrierName"].ToString());
split.FeeBilled = PIn.Double(table.Rows[i]["feeBilled_"].ToString());
split.InsPayAmt = PIn.Double(table.Rows[i]["insPayAmt_"].ToString());
split.ClaimNum = PIn.Long(table.Rows[i]["ClaimNum"].ToString());
split.ClaimPaymentNum = PIn.Long(table.Rows[i]["ClaimPaymentNum"].ToString());
split.PaymentRow = PIn.Int(table.Rows[i]["PaymentRow"].ToString());
splits.Add(split);
}
return(splits);
}
///<summary>Surround with try-catch.</summary>
public static void DeleteObject(Supplier supp)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), supp);
return;
}
//validate that not already in use.
string command = "SELECT COUNT(*) FROM supplyorder WHERE SupplierNum=" + POut.Long(supp.SupplierNum);
int count = PIn.Int(Db.GetCount(command));
if (count > 0)
{
throw new ApplicationException(Lans.g("Supplies", "Supplier is already in use on an order. Not allowed to delete."));
}
command = "SELECT COUNT(*) FROM supply WHERE SupplierNum=" + POut.Long(supp.SupplierNum);
count = PIn.Int(Db.GetCount(command));
if (count > 0)
{
throw new ApplicationException(Lans.g("Supplies", "Supplier is already in use on a supply. Not allowed to delete."));
}
Crud.SupplierCrud.Delete(supp.SupplierNum);
}
///<summary>Timespans that might be invalid time of day. Can be + or - and can be up to 800+ hours. Stored in Oracle as varchar2.</summary>
public static TimeSpan TSpan(string myString)
{
if (string.IsNullOrEmpty(myString))
{
return(System.TimeSpan.Zero);
}
try {
if (DataConnection.DBtype == DatabaseType.Oracle)
{
//return System.TimeSpan.Parse(myString); //Does not work. Confuses hours with days and an exception is thrown in our large timespan test.
bool isNegative = false;
if (myString.StartsWith("-"))
{
isNegative = true;
myString = myString.Substring(1); //remove the '-'
}
string[] timeValues = myString.Split(new char[] { ':' });
if (timeValues.Length != 3)
{
return(System.TimeSpan.Zero);
}
TimeSpan retval = new TimeSpan(PIn.Int(timeValues[0]), PIn.Int(timeValues[1]), PIn.Int(timeValues[2]));
if (isNegative)
{
return(retval.Negate());
}
return(retval);
}
else //mysql
{
return(System.TimeSpan.Parse(myString));
}
}
catch {
return(System.TimeSpan.Zero);
}
}
///<summary>Upserts the InvalidType.SmsTextMsgReceivedUnreadCount signal which tells all client machines to update the received unread SMS
///message count. There should only be max one of this signal IType in the database.</summary>
public static List <SmsFromMobiles.SmsNotification> UpsertSmsNotification()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <SmsFromMobiles.SmsNotification> >(MethodBase.GetCurrentMethod()));
}
string command = "SELECT ClinicNum,COUNT(*) AS CountUnread FROM smsfrommobile WHERE SmsStatus=0 AND IsHidden=0 GROUP BY ClinicNum "
+ "ORDER BY ClinicNum";
List <SmsFromMobiles.SmsNotification> ret = Db.GetTable(command).AsEnumerable()
.Select(x => new SmsFromMobiles.SmsNotification()
{
ClinicNum = PIn.Long(x["ClinicNum"].ToString()),
Count = PIn.Int(x["CountUnread"].ToString()),
}).ToList();
//Insert as structured data signal so all workstations won't have to query the db to get the counts. They will get it directly from Signalod.MsgValue.
string json = SmsFromMobiles.SmsNotification.GetJsonFromList(ret);
//FKeyType SmsMsgUnreadCount is written to db as a string.
command = "SELECT * FROM signalod WHERE IType=" + POut.Int((int)InvalidType.SmsTextMsgReceivedUnreadCount)
+ " AND FKeyType='" + POut.String(KeyType.SmsMsgUnreadCount.ToString()) + "' ORDER BY SigDateTime DESC LIMIT 1";
DataTable table = Db.GetTable(command);
Signalod sig = Crud.SignalodCrud.TableToList(table).FirstOrDefault();
if (sig != null && sig.MsgValue == json) //No changes, not need to insert a new signal.
{
return(ret); //Return the list of notifications, but do not update the existing signal.
}
Signalods.Insert(new Signalod()
{
IType = InvalidType.SmsTextMsgReceivedUnreadCount,
FKeyType = KeyType.SmsMsgUnreadCount,
MsgValue = json,
RemoteRole = RemotingClient.RemotingRole
});
return(ret);
}
///<summary>Surround with try/catch. Checks dependencies first. Throws exception if can't delete.</summary>
public static void Delete(long labCaseNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), labCaseNum);
return;
}
//check for dependencies
string command = "SELECT count(*) FROM sheet,sheetfield "
+ "WHERE sheet.SheetNum=sheetfield.SheetNum"
+ " AND sheet.PatNum= (SELECT patnum FROM labcase WHERE labcase.LabCaseNum=" + POut.Long(labCaseNum) + ")"
+ " AND sheet.SheetType=" + POut.Long((int)SheetTypeEnum.LabSlip)
+ " AND sheetfield.FieldType=" + POut.Long((int)SheetFieldType.Parameter)
+ " AND sheetfield.FieldName='LabCaseNum' "
+ "AND sheetfield.FieldValue='" + POut.Long(labCaseNum) + "'";
if (PIn.Int(Db.GetCount(command)) != 0)
{
throw new Exception(Lans.g("LabCases", "Cannot delete LabCase because lab slip is still attached."));
}
//delete
command = "DELETE FROM labcase WHERE LabCaseNum = " + POut.Long(labCaseNum);
Db.NonQ(command);
}
///<summary>Returns a blank string if there were no errors while attempting to update internal carriers using iTrans n-cpl.json file..</summary>
public static string TryCarrierUpdate(bool isAutomatic = true, ItransImportFields fieldsToImport = ItransImportFields.None)
{
Clearinghouse clearinghouse = Clearinghouses.GetDefaultDental();
if (clearinghouse.CommBridge != EclaimsCommBridge.ITRANS ||
string.IsNullOrEmpty(clearinghouse.ResponsePath) ||
!File.Exists(ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe")) ||
(isAutomatic && PrefC.GetString(PrefName.WebServiceServerName).ToLower() != Dns.GetHostName().ToLower())) //Only server can run when isOnlyServer is true.
{
return(Lans.g("Clearinghouse", "ITRANS must be the default dental clearinghouse and your Report Path must be set first."));
}
Process process = new Process {
StartInfo = new ProcessStartInfo {
FileName = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe"),
Arguments = " --getncpl"
}
};
process.Start();
process.WaitForExit();
string ncplFilePath = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "n-cpl.json");
string json = File.ReadAllText(ncplFilePath);//Read n-cpl.json
EtransMessageText msgTextPrev = EtransMessageTexts.GetMostRecentForType(EtransType.ItransNcpl);
if (msgTextPrev != null && msgTextPrev.MessageText == json)
{
return(Lans.g("Clearinghouse", "Carrier list has not changed since last checked.")); //json has not changed since we last checked, no need to update.
}
//Save json as new etrans entry.
Etrans etrans = Etranss.CreateEtrans(File.GetCreationTime(ncplFilePath), clearinghouse.HqClearinghouseNum, json, 0);
etrans.Etype = EtransType.ItransNcpl;
Etranss.Insert(etrans);
ItransNCpl iTransNCpl = null;
try {
iTransNCpl = JsonConvert.DeserializeObject <ItransNCpl>(json); //Deserialize n-cpl.json
}
catch (Exception ex) {
ex.DoNothing();
return(Lans.g("Clearinghouse", "Failed to import json."));
}
foreach (ItransNCpl.Carrier jsonCarrier in iTransNCpl.ListCarriers) //Update providers.
{
OpenDentBusiness.Carrier odCarrier = Carriers.GetByElectId(jsonCarrier.Bin); //Cached
if (odCarrier == null) //Carrier can not be matched to internal Carrier based on ElectID.
{
if (!fieldsToImport.HasFlag(ItransImportFields.AddMissing))
{
continue;
}
OpenDentBusiness.Carrier carrierNew = new OpenDentBusiness.Carrier();
carrierNew.ElectID = jsonCarrier.Bin;
carrierNew.IsCDA = true;
carrierNew.CarrierName = jsonCarrier.Name.En;
carrierNew.Phone = TelephoneNumbers.ReFormat(jsonCarrier.Telephone?.First().Value);
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
carrierNew.Address = add.Street1;
carrierNew.Address2 = add.Street2;
carrierNew.City = add.City;
carrierNew.State = add.Province;
carrierNew.Zip = add.PostalCode;
}
carrierNew.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
carrierNew.CarrierName = jsonCarrier.Name.En;
try {
Carriers.Insert(carrierNew);
}
catch (Exception ex) {
ex.DoNothing();
}
continue;
}
else if (!odCarrier.IsCDA)
{
continue;
}
OpenDentBusiness.Carrier odCarrierOld = odCarrier.Copy();
odCarrier.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
odCarrier.CDAnetVersion = POut.Int(jsonCarrier.Versions.Max(x => PIn.Int(x)));
List <ItransImportFields> listFields = Enum.GetValues(typeof(ItransImportFields)).Cast <ItransImportFields>().ToList();
foreach (ItransImportFields field in listFields)
{
if (fieldsToImport == ItransImportFields.None)
{
break; //No point in looping.
}
if (field == ItransImportFields.None || !fieldsToImport.HasFlag(field))
{
continue;
}
switch (field)
{
case ItransImportFields.Phone:
if (jsonCarrier.Telephone.Count > 0)
{
odCarrier.Phone = TelephoneNumbers.ReFormat(jsonCarrier.Telephone.First().Value);
}
break;
case ItransImportFields.Address:
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
odCarrier.Address = add.Street1;
odCarrier.Address2 = add.Street2;
odCarrier.City = add.City;
odCarrier.State = add.Province;
odCarrier.Zip = add.PostalCode;
}
break;
case ItransImportFields.Name:
odCarrier.CarrierName = jsonCarrier.Name.En;
break;
}
}
try {
long userNum = 0;
if (!isAutomatic)
{
userNum = Security.CurUser.UserNum;
}
Carriers.Update(odCarrier, odCarrierOld, userNum);
}
catch (Exception ex) {
ex.DoNothing();
}
}
return(""); //Blank string represents a completed update.
}
///<summary>For orderBy, use 0 for BillingType and 1 for PatientName.</summary>
public static DataTable GetBilling(bool isSent, int orderBy, DateTime dateFrom, DateTime dateTo, List <long> clinicNums)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), isSent, orderBy, dateFrom, dateTo, clinicNums));
}
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");
table.Columns.Add("SuperFamily");
table.Columns.Add("ClinicNum");
string command = "SELECT guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "IFNULL(MAX(s2.DateSent)," + POut.Date(DateTime.MinValue) + ") LastStatement,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily,patient.ClinicNum "
+ "FROM statement "
+ "LEFT JOIN patient ON statement.PatNum=patient.PatNum "
+ "LEFT JOIN patient guar ON guar.PatNum=patient.Guarantor "
+ "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 (clinicNums.Count > 0)
{
command += "AND patient.ClinicNum IN (" + string.Join(",", clinicNums) + ") ";
}
command += "GROUP BY guar.BalTotal,patient.BillingType,patient.FName,guar.InsEst,statement.IsSent,"
+ "patient.LName,patient.MiddleI,statement.Mode_,guar.PayPlanDue,patient.Preferred,"
+ "statement.PatNum,statement.StatementNum,statement.SuperFamily ";
if (orderBy == 0) //BillingType
{
command += "ORDER BY definition.ItemOrder,patient.LName,patient.FName,patient.MiddleI,guar.PayPlanDue";
}
else
{
command += "ORDER BY patient.LName,patient.FName";
}
DataTable rawTable = Db.GetTable(command);
double balTotal;
double insEst;
double payPlanDue;
DateTime lastStatement;
List <Patient> listFamilyGuarantors;
foreach (DataRow rawRow in rawTable.Rows)
{
row = table.NewRow();
if (rawRow["SuperFamily"].ToString() == "0") //not a super statement, just get bal info from guarantor
{
balTotal = PIn.Double(rawRow["BalTotal"].ToString());
insEst = PIn.Double(rawRow["InsEst"].ToString());
payPlanDue = PIn.Double(rawRow["PayPlanDue"].ToString());
}
else //super statement, add all guar positive balances to get bal total for super family
{
listFamilyGuarantors = Patients.GetSuperFamilyGuarantors(PIn.Long(rawRow["SuperFamily"].ToString())).FindAll(x => x.HasSuperBilling);
//exclude fams with neg balances in the total for super family stmts (per Nathan 5/25/2016)
if (PrefC.GetBool(PrefName.BalancesDontSubtractIns))
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => x.BalTotal > 0);
insEst = 0;
}
else
{
listFamilyGuarantors = listFamilyGuarantors.FindAll(x => (x.BalTotal - x.InsEst) > 0);
insEst = listFamilyGuarantors.Sum(x => x.InsEst);
}
balTotal = listFamilyGuarantors.Sum(x => x.BalTotal);
payPlanDue = listFamilyGuarantors.Sum(x => x.PayPlanDue);
}
row["amountDue"] = (balTotal - insEst).ToString("F");
row["balTotal"] = balTotal.ToString("F");;
row["billingType"] = Defs.GetName(DefCat.BillingTypes, PIn.Long(rawRow["BillingType"].ToString()));
if (insEst == 0)
{
row["insEst"] = "";
}
else
{
row["insEst"] = insEst.ToString("F");
}
row["IsSent"] = rawRow["IsSent"].ToString();
lastStatement = PIn.Date(rawRow["LastStatement"].ToString());
if (lastStatement.Year < 1880)
{
row["lastStatement"] = "";
}
else
{
row["lastStatement"] = lastStatement.ToShortDateString();
}
row["mode"] = Lans.g("enumStatementMode", ((StatementMode)PIn.Int(rawRow["Mode_"].ToString())).ToString());
row["name"] = Patients.GetNameLF(rawRow["LName"].ToString(), rawRow["FName"].ToString(), rawRow["Preferred"].ToString(), rawRow["MiddleI"].ToString());
row["PatNum"] = rawRow["PatNum"].ToString();
if (payPlanDue == 0)
{
row["payPlanDue"] = "";
}
else
{
row["payPlanDue"] = payPlanDue.ToString("F");
}
row["StatementNum"] = rawRow["StatementNum"].ToString();
row["SuperFamily"] = rawRow["SuperFamily"].ToString();
row["ClinicNum"] = rawRow["ClinicNum"].ToString();
table.Rows.Add(row);
}
return(table);
}
public static void AddUnreads(long taskNum, long curUserNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), taskNum, curUserNum);
return;
}
//if the task is done, don't add unreads
string command = "SELECT TaskStatus,UserNum,ReminderGroupId,DateTimeEntry," + DbHelper.Now() + " DbTime "
+ "FROM task WHERE TaskNum = " + POut.Long(taskNum);
DataTable table = Db.GetTable(command);
if (table.Rows.Count == 0)
{
return; //only happens when a task was deleted by one user but left open on another user's computer.
}
TaskStatusEnum taskStatus = (TaskStatusEnum)PIn.Int(table.Rows[0]["TaskStatus"].ToString());
long userNumOwner = PIn.Long(table.Rows[0]["UserNum"].ToString());
if (taskStatus == TaskStatusEnum.Done) //
{
return;
}
//Set it unread for the original owner of the task.
if (userNumOwner != curUserNum) //but only if it's some other user
{
SetUnread(userNumOwner, taskNum);
}
//Set it for this user if a future repeating task, so it will be new when "due". Doing this here so we don't check every row below.
//Only for future dates because we don't want to mark as new if it was already "due" and you added a note or something.
if ((PIn.String(table.Rows[0]["ReminderGroupId"].ToString()) != "") && //Is a reminder
(PIn.DateT(table.Rows[0]["DateTimeEntry"].ToString()) > PIn.DateT(table.Rows[0]["DbTime"].ToString()))) //Is "due" in the future by DbTime
{
SetUnread(curUserNum, taskNum); //Set unread for current user only, other users dealt with below.
}
//Then, for anyone subscribed
long userNum;
bool isUnread;
//task subscriptions are not cached yet, so we use a query.
//Get a list of all subscribers to this task
command = @"SELECT
tasksubscription.UserNum,
(CASE WHEN taskunread.UserNum IS NULL THEN 0 ELSE 1 END) IsUnread
FROM tasksubscription
INNER JOIN tasklist ON tasksubscription.TaskListNum = tasklist.TaskListNum
INNER JOIN taskancestor ON taskancestor.TaskListNum = tasklist.TaskListNum
AND taskancestor.TaskNum = " + POut.Long(taskNum) + " ";
command += "LEFT JOIN taskunread ON taskunread.UserNum = tasksubscription.UserNum AND taskunread.TaskNum=taskancestor.TaskNum";
table = Db.GetTable(command);
List <long> listUserNums = new List <long>();
for (int i = 0; i < table.Rows.Count; i++)
{
userNum = PIn.Long(table.Rows[i]["UserNum"].ToString());
isUnread = PIn.Bool(table.Rows[i]["IsUnread"].ToString());
if (userNum == userNumOwner || //already set
userNum == curUserNum || //If the current user is subscribed to this task. User has obviously already read it.
listUserNums.Contains(userNum) ||
isUnread) //Unread currently exists
{
continue;
}
listUserNums.Add(userNum);
}
SetUnreadMany(listUserNums, taskNum); //This no longer results in duplicates like it used to
}
///<summary>Not for claim types, just other types, including Eligibility. This function gets run first. Then, the messagetext is created and an attempt is made to send the message. Finally, the messagetext is added to the etrans. This is necessary because the transaction numbers must be incremented and assigned to each message before creating the message and attempting to send. If it fails, we will need to roll back. Provide EITHER a carrierNum OR a canadianNetworkNum. Many transactions can be sent to a carrier or to a network.</summary>
public static Etrans CreateCanadianOutput(long patNum, long carrierNum, long canadianNetworkNum, long clearinghouseNum, EtransType etype, long planNum, long insSubNum)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Etrans>(MethodBase.GetCurrentMethod(), patNum, carrierNum, canadianNetworkNum, clearinghouseNum, etype, planNum, insSubNum));
}
//validation of carrier vs network
if (etype == EtransType.Eligibility_CA)
{
//only carrierNum is allowed (and required)
if (carrierNum == 0)
{
throw new ApplicationException("Carrier not supplied for Etranss.CreateCanadianOutput.");
}
if (canadianNetworkNum != 0)
{
throw new ApplicationException("NetworkNum not allowed for Etranss.CreateCanadianOutput.");
}
}
Etrans etrans = new Etrans();
//etrans.DateTimeTrans handled automatically
etrans.ClearingHouseNum = clearinghouseNum;
etrans.Etype = etype;
etrans.ClaimNum = 0; //no claim involved
etrans.PatNum = patNum;
//CanadianNetworkNum?
etrans.CarrierNum = carrierNum;
etrans.PlanNum = planNum;
etrans.InsSubNum = insSubNum;
//Get next OfficeSequenceNumber-----------------------------------------------------------------------------------------
etrans.OfficeSequenceNumber = 0;
string command = "SELECT MAX(OfficeSequenceNumber) FROM etrans";
DataTable table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
etrans.OfficeSequenceNumber = PIn.Int(table.Rows[0][0].ToString());
if (etrans.OfficeSequenceNumber == 999999) //if the office has sent > 1 million messages, and has looped back around to 1.
//get the date of the most recent max
//This works, but it got even more complex for CarrierTransCounter, so we will just throw an exception for now.
/*command="SELECT MAX(DateTimeTrans) FROM etrans WHERE OfficeSequenceNumber=999999";
* table=Db.GetTable(command);
* DateTime maxDateT=PIn.PDateT(table.Rows[0][0].ToString());
* //then, just get the max that's newer than that.
* command="SELECT MAX(OfficeSequenceNumber) FROM etrans WHERE DateTimeTrans > '"+POut.PDateT(maxDateT)+"'";
* table=Db.GetTable(command);
* if(table.Rows.Count>0) {
* etrans.OfficeSequenceNumber=PIn.PInt(table.Rows[0][0].ToString());
* }*/
{
throw new ApplicationException("OfficeSequenceNumber has maxed out at 999999. This program will need to be enhanced.");
}
}
#if DEBUG
etrans.OfficeSequenceNumber = PIn.Int(File.ReadAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt"));
File.WriteAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt", (etrans.OfficeSequenceNumber + 1).ToString());
#endif
etrans.OfficeSequenceNumber++;
//if(etype==EtransType.Eligibility_CA){ //The counter must be incremented for all transaction types, according to the documentation for field A09 Carrier Transaction Counter.
//find the next CarrierTransCounter------------------------------------------------------------------------------------
etrans.CarrierTransCounter = 0;
command = "SELECT MAX(CarrierTransCounter) FROM etrans "
+ "WHERE CarrierNum=" + POut.Long(etrans.CarrierNum);
table = Db.GetTable(command);
int tempcounter = 0;
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter)
{
etrans.CarrierTransCounter = tempcounter;
}
command = "SELECT MAX(CarrierTransCounter2) FROM etrans "
+ "WHERE CarrierNum2=" + POut.Long(etrans.CarrierNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter)
{
etrans.CarrierTransCounter = tempcounter;
}
if (etrans.CarrierTransCounter == 99999)
{
throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced.");
//maybe by adding a reset date to the preference table which will apply to all counters as a whole.
}
etrans.CarrierTransCounter++;
//}
Insert(etrans);
return(GetEtrans(etrans.EtransNum)); //Since the DateTimeTrans is set upon insert, we need to read the record again in order to get the date.
}
///<summary>Called after file is downloaded. Throws exceptions. It is assumed that this is called from a worker thread. Progress delegate will be called every 100th iteration to inform thread of current progress. Quit flag can be set at any time in order to quit importing prematurely.</summary>
public static void ImportLoinc(string tempFileName, ProgressArgs progress, ref bool quit, ref int numCodesImported, ref int numCodesUpdated,
bool updateExisting)
{
if (tempFileName == null)
{
return;
}
Dictionary <string, Loinc> dictLoincs = Loincs.GetAll().ToDictionary(x => x.LoincCode, x => x);
string[] lines = File.ReadAllLines(tempFileName);
string[] arrayLoinc;
Loinc oldLoinc = new Loinc();
Loinc newLoinc = new Loinc();
for (int i = 0; i < lines.Length; i++) //each loop should read exactly one line of code. and each line of code should be a unique code
{
if (quit)
{
return;
}
if (i % 100 == 0)
{
progress(i + 1, lines.Length);
}
arrayLoinc = lines[i].Split('\t');
newLoinc.LoincCode = arrayLoinc[0];
newLoinc.Component = arrayLoinc[1];
newLoinc.PropertyObserved = arrayLoinc[2];
newLoinc.TimeAspct = arrayLoinc[3];
newLoinc.SystemMeasured = arrayLoinc[4];
newLoinc.ScaleType = arrayLoinc[5];
newLoinc.MethodType = arrayLoinc[6];
newLoinc.StatusOfCode = arrayLoinc[7];
newLoinc.NameShort = arrayLoinc[8];
newLoinc.ClassType = arrayLoinc[9];
newLoinc.UnitsRequired = arrayLoinc[10] == "Y";
newLoinc.OrderObs = arrayLoinc[11];
newLoinc.HL7FieldSubfieldID = arrayLoinc[12];
newLoinc.ExternalCopyrightNotice = arrayLoinc[13];
newLoinc.NameLongCommon = arrayLoinc[14];
newLoinc.UnitsUCUM = arrayLoinc[15];
newLoinc.RankCommonTests = PIn.Int(arrayLoinc[16]);
newLoinc.RankCommonOrders = PIn.Int(arrayLoinc[17]);
if (dictLoincs.ContainsKey(arrayLoinc[0])) //code already exists; arrayLoinc[0]==Loinc Code
{
oldLoinc = dictLoincs[arrayLoinc[0]];
if (updateExisting &&
(oldLoinc.LoincCode != arrayLoinc[0] ||
oldLoinc.Component != arrayLoinc[1] ||
oldLoinc.PropertyObserved != arrayLoinc[2] ||
oldLoinc.TimeAspct != arrayLoinc[3] ||
oldLoinc.SystemMeasured != arrayLoinc[4] ||
oldLoinc.ScaleType != arrayLoinc[5] ||
oldLoinc.MethodType != arrayLoinc[6] ||
oldLoinc.StatusOfCode != arrayLoinc[7] ||
oldLoinc.NameShort != arrayLoinc[8] ||
oldLoinc.ClassType != arrayLoinc[9] ||
oldLoinc.UnitsRequired != (arrayLoinc[10] == "Y") ||
oldLoinc.OrderObs != arrayLoinc[11] ||
oldLoinc.HL7FieldSubfieldID != arrayLoinc[12] ||
oldLoinc.ExternalCopyrightNotice != arrayLoinc[13] ||
oldLoinc.NameLongCommon != arrayLoinc[14] ||
oldLoinc.UnitsUCUM != arrayLoinc[15] ||
oldLoinc.RankCommonTests != PIn.Int(arrayLoinc[16]) ||
oldLoinc.RankCommonOrders != PIn.Int(arrayLoinc[17])))
{
Loincs.Update(newLoinc);
numCodesUpdated++;
}
continue;
}
Loincs.Insert(newLoinc);
numCodesImported++;
}
}
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>Checks the phone.ClockStatus to determine if screenshot should be saved. Returns extension if true and zero if false.</summary>
public static int IsOnClock(string ipAddress, string computerName)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetInt(MethodBase.GetCurrentMethod(), ipAddress, computerName));
}
string command = "SELECT * FROM phoneempdefault WHERE ComputerName='" + POut.String(computerName) + "'";
PhoneEmpDefault ped = Crud.PhoneEmpDefaultCrud.SelectOne(command);
if (ped != null) //we found that computername entered as an override
{
if (ped.IsPrivateScreen)
{
Phones.SetScreenshot(ped.PhoneExt, "", null);
return(0);
}
command = "SELECT ClockStatus FROM phone "
+ "WHERE Extension = " + POut.Long(ped.PhoneExt);
try {
ClockStatusEnum status = (ClockStatusEnum)Enum.Parse(typeof(ClockStatusEnum), PIn.String(Db.GetScalar(command)));
if (status == ClockStatusEnum.Available ||
status == ClockStatusEnum.Backup ||
status == ClockStatusEnum.OfflineAssist ||
status == ClockStatusEnum.TeamAssist ||
status == ClockStatusEnum.Training ||
status == ClockStatusEnum.WrapUp)
{
return(ped.PhoneExt);
}
}
catch {
return(0);
}
return(0); //on break or clocked out
}
//there is no computername override entered by staff, so figure out what the extension should be
int extension = 0;
if (ipAddress.Contains("192.168.0.2"))
{
extension = PIn.Int(ipAddress.ToString().Substring(10)) - 100; //eg 205-100=105
}
else if (ipAddress.ToString().Contains("10.10.20.1"))
{
extension = PIn.Int(ipAddress.ToString().Substring(9)); //eg 105
}
if (extension == 0)
{
//we don't have a good extension
return(0);
}
//make sure the extension isn't overridden with a computername
//Example: this is computer .204, and ext 104 has a computername override. This computer should not save screenshot on behalf of 104.
//command="SELECT COUNT(*) FROM phoneempdefault WHERE PhoneExt= "+POut.Long(extension)+" "
// +"AND ComputerName!=''";//there exists a computername override for the extension
//if(Db.GetScalar(command)!="0") {
// return 0;
//}
command = "SELECT * FROM phoneempdefault WHERE PhoneExt= " + POut.Long(extension);
ped = Crud.PhoneEmpDefaultCrud.SelectOne(command);
if (ped != null && ped.IsPrivateScreen)
{
Phones.SetScreenshot(ped.PhoneExt, "", null);
return(0);
}
command = "SELECT ClockStatus FROM phone "
+ "WHERE Extension = " + POut.Long(extension);
try {
ClockStatusEnum status2 = (ClockStatusEnum)Enum.Parse(typeof(ClockStatusEnum), PIn.String(Db.GetScalar(command)));
if (status2 == ClockStatusEnum.Available ||
status2 == ClockStatusEnum.Backup ||
status2 == ClockStatusEnum.OfflineAssist ||
status2 == ClockStatusEnum.TeamAssist ||
status2 == ClockStatusEnum.Training ||
status2 == ClockStatusEnum.WrapUp)
{
return(extension);
}
}
catch {
return(0);
}
return(0); //on break or clocked out
}
///<summary>We use this for queries that return a single value that is an int. If there are no results, it will return 0.</summary>
internal static int GetInt(string command)
{
return(PIn.Int((GetLong(command).ToString()), false));
}
///<summary>Throws exceptions.
///When etrans.Etype is associated to a Canadian request EType, this runs multiple queries to set etrans.CarrierTransCounter and
///etrans.CarrierTransCounter2. Otherwise returns without making any changes.
///The etrans.CarrierNum, etrans.CarrierNum2 and etrans.Etype columns must be set prior to running this.</summary>
public static Etrans SetCanadianEtransFields(Etrans etrans,bool hasSecondary=true) {
if(!etrans.Etype.GetAttributeOrDefault<EtransTypeAttr>().IsCanadaType || !etrans.Etype.GetAttributeOrDefault<EtransTypeAttr>().IsRequestType) {
return etrans;
}
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetObject<Etrans>(MethodBase.GetCurrentMethod(),etrans,hasSecondary);
}
etrans.OfficeSequenceNumber=0;
//find the next officeSequenceNumber
string command="SELECT MAX(OfficeSequenceNumber) FROM etrans";
DataTable table=Db.GetTable(command);
if(table.Rows.Count>0) {
etrans.OfficeSequenceNumber=PIn.Int(table.Rows[0][0].ToString());
if(etrans.OfficeSequenceNumber==999999){//if the office has sent > 1 million messages, and has looped back around to 1.
throw new ApplicationException("OfficeSequenceNumber has maxed out at 999999. This program will need to be enhanced.");
}
}
etrans.OfficeSequenceNumber++;
//find the next CarrierTransCounter for the primary carrier
#region CarrierTransCounter
etrans.CarrierTransCounter=0;
command="SELECT MAX(CarrierTransCounter) FROM etrans "
+"WHERE CarrierNum="+POut.Long(etrans.CarrierNum);
table=Db.GetTable(command);
int tempcounter=0;
if(table.Rows.Count>0) {
tempcounter=PIn.Int(table.Rows[0][0].ToString());
}
if(tempcounter>etrans.CarrierTransCounter) {
etrans.CarrierTransCounter=tempcounter;
}
command="SELECT MAX(CarrierTransCounter2) FROM etrans "
+"WHERE CarrierNum2="+POut.Long(etrans.CarrierNum);
table=Db.GetTable(command);
if(table.Rows.Count>0) {
tempcounter=PIn.Int(table.Rows[0][0].ToString());
}
if(tempcounter>etrans.CarrierTransCounter) {
etrans.CarrierTransCounter=tempcounter;
}
if(etrans.CarrierTransCounter==99999){
throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced.");
//maybe by adding a reset date to the preference table which will apply to all counters as a whole.
}
etrans.CarrierTransCounter++;
#endregion CarrierTransCounter
if(!hasSecondary || etrans.CarrierNum2==0) {
return etrans;
}
#region CarrierTransCounter2
etrans.CarrierTransCounter2=1;
command="SELECT MAX(CarrierTransCounter) FROM etrans "
+"WHERE CarrierNum="+POut.Long(etrans.CarrierNum2);
table=Db.GetTable(command);
if(table.Rows.Count>0) {
tempcounter=PIn.Int(table.Rows[0][0].ToString());
}
if(tempcounter>etrans.CarrierTransCounter2) {
etrans.CarrierTransCounter2=tempcounter;
}
command="SELECT MAX(CarrierTransCounter2) FROM etrans "
+"WHERE CarrierNum2="+POut.Long(etrans.CarrierNum2);
table=Db.GetTable(command);
if(table.Rows.Count>0) {
tempcounter=PIn.Int(table.Rows[0][0].ToString());
}
if(tempcounter>etrans.CarrierTransCounter2) {
etrans.CarrierTransCounter2=tempcounter;
}
if(etrans.CarrierTransCounter2==99999) {
throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced.");
}
etrans.CarrierTransCounter2++;
#endregion
return etrans;
}
///<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 void MakeABackup(string serverName = "", string user = "", string pass = "", bool doVerify = false)
{
//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)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), serverName, user, pass, doVerify);
return;
}
//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;
}
string currentServerName = DataConnection.GetServerName().ToLower();
bool useSameServer = string.IsNullOrWhiteSpace(serverName) || currentServerName.Equals(serverName, StringComparison.CurrentCultureIgnoreCase);
if (!string.IsNullOrWhiteSpace(serverName) && currentServerName == "localhost" && serverName.ToLower() != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = serverName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
if (serverName.ToLower() == "localhost" && currentServerName != "localhost") //there could be a mismatch but technically the same server
{
useSameServer = currentServerName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase);
}
//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();
//if they provided a different server where they want their backup to be, we need a separate connection for that
DataConnection dconBackupServer = useSameServer ? new DataConnection() : new DataConnection(serverName, "", user, pass, DatabaseType.MySql);
//Check that the backup server does not already contain this database
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 = dconBackupServer.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";
dconBackupServer.NonQ(command); //create the backup db on the backup server
//But get the tables from the current, not the backup server
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);
//Set the connection to the new database now that it has been created
dconBackupServer = useSameServer?new DataConnection(newDb):new DataConnection(serverName, newDb, user, pass, DatabaseType.MySql);
foreach (DataRow row in table.Rows)
{
string tableName = row[0].ToString();
//First create the table on the new db
MiscDataEvent.Fire(ODEventType.MiscData, $"Backing up table: {tableName}");
//also works with views. Added backticks around table name for unusual characters.
command = $"SHOW CREATE TABLE `{oldDb}`.`{tableName}`";
DataTable dtCreate = dcon.GetTable(command);
command = PIn.ByteArray(dtCreate.Rows[0][1]);
//The backup database tables will be MyISAM because it is significantly faster at doing bulk inserts.
command = command.Replace("ENGINE=InnoDB", "ENGINE=MyISAM");
dconBackupServer.NonQ(command);
//Then copy the data into the new table
if (useSameServer)
{
//If on the same server we can select into directly, which is faster
command = $"INSERT INTO `{newDb}`.`{tableName}` SELECT * FROM `{oldDb}`.`{tableName}`"; //Added backticks around table name for unusual characters.
dconBackupServer.NonQ(command);
}
else
{
long count = PIn.Long(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int limit = 10000;
if (tableName == "documentmisc") //This table can have really large rows so just to be safe, handle the backup one row at a time
{
limit = 1;
}
int offset = 0;
while (count > offset)
{
DataTable dtOld = dcon.GetTable($" SELECT * FROM `{oldDb}`.`{tableName}` LIMIT {limit} OFFSET {offset}");
offset += dtOld.Rows.Count;
dconBackupServer.BulkCopy(dtOld, tableName);
}
}
}
//Verify that the old database and the new backup have the same number of rows
if (doVerify)
{
List <string> listTablesFailed = new List <string>();
foreach (DataRow dbTable in table.Rows)
{
string tableName = dbTable[0].ToString();
MiscDataEvent.Fire(ODEventType.MiscData, $"Verifying backup: {tableName}");
int ctOld = PIn.Int(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`"));
int ctNew = PIn.Int(dconBackupServer.GetCount($"SELECT COUNT(*) FROM `{newDb}`.`{tableName}`"));
if (ctOld != ctNew)
{
listTablesFailed.Add(tableName);
}
}
if (listTablesFailed.Count > 0)
{
throw new Exception($@"Failed to create database backup because the following tables contained a different number of rows than expected:
{string.Join(", ",listTablesFailed)}." );
}
}
}
///<summary>Gets a pref of type int32. Used when the pref is an enumeration, itemorder, etc. Also used for historical queries in ConvertDatabase.</summary>
public static int GetInt(PrefName prefName)
{
return(PIn.Int(Prefs.GetOne(prefName).ValueString));
}
///<summary>Result will contain strings in the following order: 0 Patient Last Name (NM103), 1 Patient First Name (NM104), 2 Patient Middle Name (NM105),
///3 Claim Status (STC03), 4 Payor's Claim Control Number (REF02), 5 Institutional Type of Bill (REF02), 6 Claim Date Service Start (DTP03),
///7 Claim Date Service End (DTP03), 8 Reason (STC01-2), 9 Amount (STC04)</summary>
public string[] GetClaimInfo(string trackingNumber)
{
string[] result = new string[10];
for (int i = 0; i < result.Length; i++)
{
result[i] = "";
}
for (int i = 0; i < segNumsClaimTrackingNumberTRN.Count; i++)
{
int segNum = segNumsClaimTrackingNumberTRN[i];
X12Segment seg = segments[segNum]; //TRN segment.
if (seg.Get(2) == trackingNumber) //TRN02
//Locate the NM1 segment corresponding to the claim tracking number. One NM1 segment can be shared with multiple TRN segments.
//The strategy is to locate the NM1 segment furthest down in the message that is above the TRN segment for the tracking number.
{
int segNumNM1 = segNumsPatientDetailNM1[segNumsPatientDetailNM1.Count - 1]; //very last NM1 segment
for (int j = 0; j < segNumsPatientDetailNM1.Count - 1; j++)
{
if (segNum > segNumsPatientDetailNM1[j] && segNum < segNumsPatientDetailNM1[j + 1])
{
segNumNM1 = segNumsPatientDetailNM1[j];
break;
}
}
seg = segments[segNumNM1]; //NM1 segment.
result[0] = seg.Get(3); //NM103 Last Name
result[1] = seg.Get(4); //NM104 First Name
result[2] = seg.Get(5); //NM105 Middle Name
segNum++;
seg = segments[segNum]; //STC segment. At least one, maybe multiple, but we only care about the first one.
string[] stc01 = seg.Get(1).Split(new string[] { Separators.Subelement }, StringSplitOptions.None);
result[3] = GetStringForExternalSourceCode507(stc01[0]);
if (stc01.Length > 1)
{
result[8] = GetStringForExternalSourceCode508(stc01[1]); //STC01-2
}
result[9] = seg.Get(4);
//Skip the remaining STC segments (if any).
segNum++;
if (segNum >= segments.Count)
{
return(result); //End of file
}
seg = segments[segNum];
while (seg.SegmentID == "STC")
{
segNum++;
seg = segments[segNum];
}
while (seg.SegmentID == "REF")
{
string refIdQualifier = seg.Get(1);
if (refIdQualifier == "1K")
{
result[4] = seg.Get(2); //REF02 Payor's Claim Control Number.
}
else if (refIdQualifier == "D9")
{
//REF02 Claim Identifier Number for Clearinghouse and Other Transmission Intermediary from the 837.
//When we send this it is the same as the claim identifier/claim tracking number, so we don't use this for now.
}
else if (refIdQualifier == "BLT")
{
//REF02 Institutional Type of Bill that was sent in the 837.
result[5] = seg.Get(2);
}
segNum++;
if (segNum >= segments.Count)
{
return(result); //End of file
}
seg = segments[segNum];
}
//The DTP segment for the date of service will not be present when an invalid date was originally sent to the carrier (even though the specifications have it marked as a required segment).
if (seg.SegmentID == "DTP")
{
string dateServiceStr = seg.Get(3);
int dateServiceStartYear = PIn.Int(dateServiceStr.Substring(0, 4));
int dateServiceStartMonth = PIn.Int(dateServiceStr.Substring(4, 2));
int dateServiceStartDay = PIn.Int(dateServiceStr.Substring(6, 2));
result[6] = (new DateTime(dateServiceStartYear, dateServiceStartMonth, dateServiceStartDay)).ToShortDateString();
if (dateServiceStr.Length == 17) //Date range.
{
int dateServiceEndYear = PIn.Int(dateServiceStr.Substring(9, 4));
int dateServiceEndMonth = PIn.Int(dateServiceStr.Substring(13, 2));
int dateServiceEndDay = PIn.Int(dateServiceStr.Substring(15, 2));
result[7] = (new DateTime(dateServiceEndYear, dateServiceEndMonth, dateServiceEndDay)).ToShortDateString();
}
}
}
}
return(result);
}
///<summary>Gets a color from an int32 pref.</summary>
public static Color GetColor(PrefName prefName)
{
return(Color.FromArgb(PIn.Int(Prefs.GetOne(prefName).ValueString)));
}
///<summary></summary>
public static DataTable GetOrderTable(long patNum, bool includeDiscontinued)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), patNum, includeDiscontinued));
}
DataTable table = new DataTable("orders");
DataRow row;
table.Columns.Add("date");
table.Columns.Add("DateTime", typeof(DateTime));
table.Columns.Add("description");
table.Columns.Add("MedicalOrderNum");
table.Columns.Add("MedicationPatNum");
table.Columns.Add("prov");
table.Columns.Add("status");
table.Columns.Add("type");
List <DataRow> rows = new List <DataRow>();
string command = "SELECT DateTimeOrder,Description,IsDiscontinued,MedicalOrderNum,MedOrderType,ProvNum "
+ "FROM medicalorder WHERE PatNum = " + POut.Long(patNum);
if (!includeDiscontinued) //only include current orders
{
command += " AND IsDiscontinued=0"; //false
}
DataTable rawOrder = Db.GetTable(command);
DateTime dateT;
MedicalOrderType medOrderType;
long medicalOrderNum;
bool isDiscontinued;
for (int i = 0; i < rawOrder.Rows.Count; i++)
{
row = table.NewRow();
dateT = PIn.DateT(rawOrder.Rows[i]["DateTimeOrder"].ToString());
medOrderType = (MedicalOrderType)PIn.Int(rawOrder.Rows[i]["MedOrderType"].ToString());
medicalOrderNum = PIn.Long(rawOrder.Rows[i]["MedicalOrderNum"].ToString());
row["DateTime"] = dateT;
row["date"] = dateT.ToShortDateString();
row["description"] = PIn.String(rawOrder.Rows[i]["Description"].ToString());
if (medOrderType == MedicalOrderType.Laboratory)
{
List <LabPanel> listPanelsForOrder = LabPanels.GetPanelsForOrder(medicalOrderNum);
for (int p = 0; p < listPanelsForOrder.Count; p++)
{
row["description"] += "\r\n "; //new row for each panel
List <LabResult> listResults = LabResults.GetForPanel(listPanelsForOrder[p].LabPanelNum);
if (listResults.Count > 0)
{
row["description"] += listResults[0].DateTimeTest.ToShortDateString() + " - ";
}
row["description"] += listPanelsForOrder[p].ServiceName;
}
}
row["MedicalOrderNum"] = medicalOrderNum.ToString();
row["MedicationPatNum"] = "0";
row["prov"] = Providers.GetAbbr(PIn.Long(rawOrder.Rows[i]["ProvNum"].ToString()));
isDiscontinued = PIn.Bool(rawOrder.Rows[i]["IsDiscontinued"].ToString());
if (isDiscontinued)
{
row["status"] = "Discontinued";
}
else
{
row["status"] = "Active";
}
row["type"] = medOrderType.ToString();
rows.Add(row);
}
//Medications are deprecated for 2014 edition
//MedicationPats
//command="SELECT DateStart,DateStop,MedicationPatNum,CASE WHEN medication.MedName IS NULL THEN medicationpat.MedDescript ELSE medication.MedName END MedName,PatNote,ProvNum "
// +"FROM medicationpat "
// +"LEFT OUTER JOIN medication ON medication.MedicationNum=medicationpat.MedicationNum "
// +"WHERE PatNum = "+POut.Long(patNum);
//if(!includeDiscontinued) {//exclude invalid orders
// command+=" AND DateStart > "+POut.Date(new DateTime(1880,1,1))+" AND PatNote !='' "
// +"AND (DateStop < "+POut.Date(new DateTime(1880,1,1))+" "//no date stop
// +"OR DateStop >= "+POut.Date(DateTime.Today)+")";//date stop hasn't happened yet
//}
//DataTable rawMed=Db.GetTable(command);
//DateTime dateStop;
//for(int i=0;i<rawMed.Rows.Count;i++) {
// row=table.NewRow();
// dateT=PIn.DateT(rawMed.Rows[i]["DateStart"].ToString());
// row["DateTime"]=dateT;
// if(dateT.Year<1880) {
// row["date"]="";
// }
// else {
// row["date"]=dateT.ToShortDateString();
// }
// row["description"]=PIn.String(rawMed.Rows[i]["MedName"].ToString())+", "
// +PIn.String(rawMed.Rows[i]["PatNote"].ToString());
// row["MedicalOrderNum"]="0";
// row["MedicationPatNum"]=rawMed.Rows[i]["MedicationPatNum"].ToString();
// row["prov"]=Providers.GetAbbr(PIn.Long(rawMed.Rows[i]["ProvNum"].ToString()));
// dateStop=PIn.Date(rawMed.Rows[i]["DateStop"].ToString());
// if(dateStop.Year<1880 || dateStop>DateTime.Today) {//not stopped or in the future
// row["status"]="Active";
// }
// else {
// row["status"]="Discontinued";
// }
// row["type"]="Medication";
// rows.Add(row);
//}
//Sorting-----------------------------------------------------------------------------------------
rows.Sort(new MedicalOrderLineComparer());
for (int i = 0; i < rows.Count; i++)
{
table.Rows.Add(rows[i]);
}
return(table);
}
///<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>();
}
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.DtimeToDate("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>Gets all feature request. Optionally pass in a list of Request IDs to only get those feature requests.</summary>
public static List <FeatureRequest> GetAll(List <long> listRequestIDs = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <FeatureRequest> >(MethodBase.GetCurrentMethod(), listRequestIDs));
}
DataTable table = new DataTable();
DataAction.RunBugsHQ(() => {
#region WebServiceCustomerUpdates.FeatureRequestGetList
string command = "SELECT request.RequestId,Approval,Description,Difficulty,"
+ "vote.AmountPledged,IFNULL(vote.Points,0) AS points,vote.IsCritical,request.PatNum,"
+ "TotalCritical,TotalPledged,TotalPoints,Weight "
+ "FROM request "
+ "LEFT JOIN vote ON vote.PatNum=1486 AND vote.RequestId=request.RequestId "
+ "WHERE Approval IN (" + POut.Int((int)ApprovalEnum.New)
+ "," + POut.Int((int)ApprovalEnum.Approved)
+ "," + POut.Int((int)ApprovalEnum.InProgress)
+ "," + POut.Int((int)ApprovalEnum.Complete) + ") ";
if (!listRequestIDs.IsNullOrEmpty())
{
command += $"AND request.RequestId IN ({string.Join(",",listRequestIDs)}) ";
}
command += "ORDER BY Approval, Weight DESC, points DESC";
DataTable raw = Db.GetTable(command);
DataRow row;
table.TableName = "Table";
table.Columns.Add("approval");
table.Columns.Add("Description");
table.Columns.Add("Difficulty");
table.Columns.Add("isMine");
table.Columns.Add("myVotes");
table.Columns.Add("RequestId");
table.Columns.Add("totalVotes");
table.Columns.Add("Weight");
table.Columns.Add("personalVotes");
table.Columns.Add("personalCrit");
table.Columns.Add("personalPledged");
double myPledge;
bool myCritical;
double totalPledged;
int totalCritical;
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
row["RequestId"] = raw.Rows[i]["RequestId"].ToString();
//myVotes,myCritical,myPledge------------------------------------------------------
row["myVotes"] = raw.Rows[i]["Points"].ToString();
row["personalVotes"] = raw.Rows[i]["Points"].ToString();
if (row["myVotes"].ToString() == "0")
{
row["myVotes"] = "";
}
myCritical = PIn.Bool(raw.Rows[i]["IsCritical"].ToString());
if (myCritical == true)
{
row["personalCrit"] = "1";
if (row["myVotes"].ToString() != "")
{
row["myVotes"] += "\r\n";
}
row["myVotes"] += "Critical";
}
else
{
row["personalCrit"] = "0";
}
myPledge = PIn.Double(raw.Rows[i]["AmountPledged"].ToString());
if (myPledge != 0)
{
if (row["myVotes"].ToString() != "")
{
row["myVotes"] += "\r\n";
}
row["myVotes"] += myPledge.ToString("c0");
row["personalPledged"] = myPledge.ToString();
}
else
{
row["personalPledged"] = "0";
}
//TotalPoints,TotalCritical,TotalPledged-----------------------------------------------
row["totalVotes"] = raw.Rows[i]["TotalPoints"].ToString();
if (row["totalVotes"].ToString() == "0")
{
row["totalVotes"] = "";
}
totalCritical = PIn.Int(raw.Rows[i]["TotalCritical"].ToString());
if (totalCritical != 0)
{
if (row["totalVotes"].ToString() != "")
{
row["totalVotes"] += "\r\n";
}
row["totalVotes"] += "Critical:" + totalCritical.ToString();
}
totalPledged = PIn.Double(raw.Rows[i]["TotalPledged"].ToString());
if (totalPledged != 0)
{
if (row["totalVotes"].ToString() != "")
{
row["totalVotes"] += "\r\n";
}
row["totalVotes"] += totalPledged.ToString("c0");
}
//end
row["approval"] = ((ApprovalEnum)PIn.Int(raw.Rows[i]["Approval"].ToString())).ToString();
if (raw.Rows[i]["PatNum"].ToString() == "1486")
{
row["isMine"] = "X";
}
else
{
row["isMine"] = "";
}
row["Difficulty"] = raw.Rows[i]["Difficulty"].ToString();
row["Description"] = raw.Rows[i]["Description"].ToString();
row["Weight"] = raw.Rows[i]["Weight"].ToString();
table.Rows.Add(row);
}
#endregion
}, false);
List <FeatureRequest> listFeatureRequests = new List <FeatureRequest>();
foreach (DataRow dataRow in table.Rows)
{
FeatureRequest req = new FeatureRequest();
#region Convert DataTable Into FeatureRequest
long.TryParse(dataRow["RequestId"].ToString(), out req.FeatReqNum);
string[] votes = dataRow["totalVotes"].ToString().Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
string vote = votes.FirstOrDefault(x => !x.StartsWith("Critical") && !x.StartsWith("$"));
if (!string.IsNullOrEmpty(vote))
{
long.TryParse(vote, out req.Votes);
}
vote = votes.FirstOrDefault(x => x.StartsWith("Critical"));
if (!string.IsNullOrEmpty(vote))
{
long.TryParse(vote, out req.Critical);
}
vote = votes.FirstOrDefault(x => x.StartsWith("$"));
if (!string.IsNullOrEmpty(vote))
{
float.TryParse(vote, out req.Pledge);
}
req.Difficulty = PIn.Long(dataRow["Difficulty"].ToString());
req.Weight = PIn.Float(dataRow["Weight"].ToString());
req.Approval = dataRow["Weight"].ToString();
req.Description = dataRow["Description"].ToString();
#endregion
listFeatureRequests.Add(req);
}
return(listFeatureRequests);
}
///<summary>Sets the status of the claim to sent, usually as part of printing. Also makes an entry in etrans. If this is Canadian eclaims, then this function gets run first. If the claim is to be sent elecronically, then the messagetext is created after this method and an attempt is made to send the claim. Finally, the messagetext is added to the etrans. This is necessary because the transaction numbers must be incremented and assigned to each claim before creating the message and attempting to send. For Canadians, it will always record the attempt as an etrans even if claim is not set to status of sent.</summary>
public static Etrans SetClaimSentOrPrinted(long claimNum, long patNum, long clearinghouseNum, EtransType etype, int batchNumber)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <Etrans>(MethodBase.GetCurrentMethod(), claimNum, patNum, clearinghouseNum, etype, batchNumber));
}
string command;
Etrans etrans = new Etrans();
//etrans.DateTimeTrans handled automatically
etrans.ClearingHouseNum = clearinghouseNum;
etrans.Etype = etype;
etrans.ClaimNum = claimNum;
etrans.PatNum = patNum;
//Get the primary and secondary carrierNums for this claim.
command = "SELECT carrier1.CarrierNum,carrier2.CarrierNum AS CarrierNum2 FROM claim "
+ "LEFT JOIN insplan insplan1 ON insplan1.PlanNum=claim.PlanNum "
+ "LEFT JOIN carrier carrier1 ON carrier1.CarrierNum=insplan1.CarrierNum "
+ "LEFT JOIN insplan insplan2 ON insplan2.PlanNum=claim.PlanNum2 "
+ "LEFT JOIN carrier carrier2 ON carrier2.CarrierNum=insplan2.CarrierNum "
+ "WHERE claim.ClaimNum=" + POut.Long(claimNum);
DataTable table = Db.GetTable(command);
etrans.CarrierNum = PIn.Long(table.Rows[0][0].ToString());
etrans.CarrierNum2 = PIn.Long(table.Rows[0][1].ToString()); //might be 0 if no secondary on this claim
etrans.BatchNumber = batchNumber;
//if(X837.IsX12(messageText)) {
// X837 x837=new X837(messageText);
// etrans.TransSetNum=x837.GetTransNum(claimNum);
//}
if (etype == EtransType.Claim_CA || etype == EtransType.ClaimCOB_CA || etype == EtransType.Predeterm_CA || etype == EtransType.PredetermEOB_CA)
{
etrans.OfficeSequenceNumber = 0;
//find the next officeSequenceNumber
command = "SELECT MAX(OfficeSequenceNumber) FROM etrans";
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
etrans.OfficeSequenceNumber = PIn.Int(table.Rows[0][0].ToString());
if (etrans.OfficeSequenceNumber == 999999) //if the office has sent > 1 million messages, and has looped back around to 1.
{
throw new ApplicationException
("OfficeSequenceNumber has maxed out at 999999. This program will need to be enhanced.");
}
}
#if DEBUG
etrans.OfficeSequenceNumber = PIn.Int(File.ReadAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt"));
File.WriteAllText(@"..\..\..\TestCanada\LastOfficeSequenceNumber.txt", (etrans.OfficeSequenceNumber + 1).ToString());
#endif
etrans.OfficeSequenceNumber++;
//find the next CarrierTransCounter for the primary carrier
etrans.CarrierTransCounter = 0;
command = "SELECT MAX(CarrierTransCounter) FROM etrans "
+ "WHERE CarrierNum=" + POut.Long(etrans.CarrierNum);
table = Db.GetTable(command);
int tempcounter = 0;
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter)
{
etrans.CarrierTransCounter = tempcounter;
}
command = "SELECT MAX(CarrierTransCounter2) FROM etrans "
+ "WHERE CarrierNum2=" + POut.Long(etrans.CarrierNum);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter)
{
etrans.CarrierTransCounter = tempcounter;
}
if (etrans.CarrierTransCounter == 99999)
{
throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced.");
}
etrans.CarrierTransCounter++;
if (etrans.CarrierNum2 > 0) //if there is secondary coverage on this claim
{
etrans.CarrierTransCounter2 = 1;
command = "SELECT MAX(CarrierTransCounter) FROM etrans "
+ "WHERE CarrierNum=" + POut.Long(etrans.CarrierNum2);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter2)
{
etrans.CarrierTransCounter2 = tempcounter;
}
command = "SELECT MAX(CarrierTransCounter2) FROM etrans "
+ "WHERE CarrierNum2=" + POut.Long(etrans.CarrierNum2);
table = Db.GetTable(command);
if (table.Rows.Count > 0)
{
tempcounter = PIn.Int(table.Rows[0][0].ToString());
}
if (tempcounter > etrans.CarrierTransCounter2)
{
etrans.CarrierTransCounter2 = tempcounter;
}
if (etrans.CarrierTransCounter2 == 99999)
{
throw new ApplicationException("CarrierTransCounter has maxed out at 99999. This program will need to be enhanced.");
}
etrans.CarrierTransCounter2++;
}
}
command = "UPDATE claim SET ClaimStatus = 'S',"
+ "DateSent= " + POut.Date(MiscData.GetNowDateTime())
+ " WHERE claimnum = " + POut.Long(claimNum);
Db.NonQ(command);
EtransMessageText etransMessageText = new EtransMessageText();
etransMessageText.MessageText = "";
EtransMessageTexts.Insert(etransMessageText);
etrans.EtransMessageTextNum = etransMessageText.EtransMessageTextNum;
Etranss.Insert(etrans);
return(GetEtrans(etrans.EtransNum)); //Since the DateTimeTrans is set upon insert, we need to read the record again in order to get the date.
}
///<summary></summary>
private static void UpdateCarrierInDb(OpenDentBusiness.Carrier odCarrier, Carrier jsonCarrier, List <CanadianNetwork> listCanadianNetworks
, ItransImportFields fieldsToImport, string jsonCarrierPhone, bool isAutomatic)
{
OpenDentBusiness.Carrier odCarrierOld = odCarrier.Copy();
odCarrier.CanadianEncryptionMethod = 1; //Default. Deprecated for all Canadian carriers and will never be any other value.
TrySetCanadianNetworkNum(jsonCarrier, odCarrier, listCanadianNetworks);
odCarrier.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
odCarrier.CDAnetVersion = POut.Int(jsonCarrier.Versions.Max(x => PIn.Int(x))).PadLeft(2, '0');//Version must be in 2 digit format. ex. 02.
List <ItransImportFields> listFields = Enum.GetValues(typeof(ItransImportFields)).Cast <ItransImportFields>().ToList();
foreach (ItransImportFields field in listFields)
{
if (fieldsToImport == ItransImportFields.None)
{
break; //No point in looping.
}
if (field == ItransImportFields.None || !fieldsToImport.HasFlag(field))
{
continue;
}
switch (field)
{
case ItransImportFields.Phone:
odCarrier.Phone = TelephoneNumbers.AutoFormat(jsonCarrierPhone);
break;
case ItransImportFields.Address:
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
odCarrier.Address = add.Street1;
odCarrier.Address2 = add.Street2;
odCarrier.City = add.City;
odCarrier.State = add.Province;
odCarrier.Zip = add.Postal_Code;
}
break;
case ItransImportFields.Name:
odCarrier.CarrierName = jsonCarrier.Name.En;
break;
}
}
try {
long userNum = 0;
if (!isAutomatic)
{
userNum = Security.CurUser.UserNum;
}
Carriers.Update(odCarrier, odCarrierOld, userNum);
}
catch (Exception ex) {
ex.DoNothing();
}
}
public static List <List <int> > GetNewPatients(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();
#if DEBUG
_elapsedTimeNewPatients = "";
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
System.Diagnostics.Stopwatch stopWatchTotal = new System.Diagnostics.Stopwatch();
_elapsedTimeNewPatients = "Elapsed time for GetNewPatients:\r\n";
stopWatch.Restart();
stopWatchTotal.Restart();
#endif
string command;
command = "DROP TABLE IF EXISTS tempdash" + rndStr + @";";
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "DROP TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = @"CREATE TABLE tempdash" + rndStr + @" (
PatNum bigint NOT NULL PRIMARY KEY,
dateFirstProc datetime NOT NULL
) DEFAULT CHARSET=utf8" ;
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "CREATE TABLE: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
//table full of individual patients and their dateFirstProcs.
command = @"INSERT INTO tempdash" + rndStr + @"
SELECT PatNum, MIN(ProcDate) dateFirstProc
FROM procedurelog USE INDEX(indexPatNum)
WHERE ProcStatus=2 GROUP BY PatNum
HAVING dateFirstProc >= " + POut.Date(dateFrom) + " "
+ "AND dateFirstProc <= " + POut.Date(dateTo);
Db.NonQ(command);
#if DEBUG
stopWatch.Stop();
_elapsedTimeNewPatients += "INSERT INTO: " + stopWatch.Elapsed.ToString() + "\r\n";
stopWatch.Restart();
#endif
command = "SELECT dateFirstProc,COUNT(*) "
+ "FROM tempdash" + rndStr + @" "
+ "GROUP BY MONTH(dateFirstProc)";
DataTable tableCounts = Db.GetTable(command);
#if DEBUG
stopWatch.Stop();
stopWatchTotal.Stop();
_elapsedTimeNewPatients += "SELECT dateFirstProc,COUNT(*): " + stopWatch.Elapsed.ToString() + "\r\n";
_elapsedTimeNewPatients += "Total: " + stopWatchTotal.Elapsed.ToString();
if (_showElapsedTimesForDebug)
{
System.Windows.Forms.MessageBox.Show(_elapsedTimeNewPatients);
}
#endif
List <int> listInt = new List <int>();
for (int i = 0; i < 12; i++)
{
int ptcount = 0;
DateTime datePeriod = dateFrom.AddMonths(i); //only the month and year are important
for (int j = 0; j < tableCounts.Rows.Count; j++)
{
if (datePeriod.Year == PIn.Date(tableCounts.Rows[j][0].ToString()).Year &&
datePeriod.Month == PIn.Date(tableCounts.Rows[j][0].ToString()).Month)
{
ptcount += PIn.Int(tableCounts.Rows[j][1].ToString());
}
}
listInt.Add(ptcount);
}
List <List <int> > retVal = new List <List <int> >();
retVal.Add(listInt);
return(retVal);
}
///<summary>Returns a blank string if there were no errors while attempting to update internal carriers using iTrans n-cpl.json file.</summary>
public static string TryCarrierUpdate(bool isAutomatic = true, ItransImportFields fieldsToImport = ItransImportFields.None)
{
string json;
DateTime dateTimeTrans = DateTime.Now;
Clearinghouse clearinghouse = Clearinghouses.GetDefaultDental();
if (clearinghouse == null)
{
return(Lans.g("Clearinghosue", "Unable to update. No default dental clearinghouse set."));
}
//If ITRANS2 is fully setup, then use the local ITRANS2 install on server to import carrier data.
if (clearinghouse.CommBridge == EclaimsCommBridge.ITRANS && !string.IsNullOrEmpty(clearinghouse.ResponsePath))
{
if (!File.Exists(ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe")))
{
return(Lans.g("Clearinghouse", "Unable to find 'ITRANS Claims Director.exe'. Make sure the file exists and the path is correct."));
}
if (isAutomatic && PrefC.GetString(PrefName.WebServiceServerName).ToLower() != Dns.GetHostName().ToLower()) //Only server can run when isOnlyServer is true.
{
return(Lans.g("Clearinghouse", "Update can only run on the web service server " + PrefC.GetString(PrefName.WebServiceServerName)) +
". " + Lans.g("Clearinghouse", "Connect to the server and try again."));
}
Process process = new Process {
StartInfo = new ProcessStartInfo {
FileName = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "ITRANS Claims Director.exe"),
Arguments = " --getncpl"
}
};
process.Start();
process.WaitForExit();
string ncplFilePath = ODFileUtils.CombinePaths(clearinghouse.ResponsePath, "n-cpl.json");
json = File.ReadAllText(ncplFilePath); //Read n-cpl.json
dateTimeTrans = File.GetCreationTime(ncplFilePath);
}
else //ITRANS2 not used or not setup correctly, go to HQ for file content.
{
try {
string result = WebServiceMainHQProxy.GetWebServiceMainHQInstance().CanadaCarrierUpdate(PayloadHelper.CreatePayload("", eServiceCode.Undefined));
json = WebSerializer.DeserializePrimitiveOrThrow <string>(result);
}
catch (Exception ex) {
return(Lans.g("Clearinghouse", "Unable to update carrier list from HQ web services.") + "\r\n" + ex.Message.ToString());
}
}
EtransMessageText msgTextPrev = EtransMessageTexts.GetMostRecentForType(EtransType.ItransNcpl);
if (msgTextPrev != null && msgTextPrev.MessageText == json)
{
if (isAutomatic ||
ODMessageBox.Show("Carrier list has not changed since last checked.\r\nContinue?", "", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return(Lans.g("Clearinghouse", "Carrier list has not changed since last checked.")); //json has not changed since we last checked, no need to update.
}
}
//Save json as new etrans entry.
Etrans etrans = Etranss.CreateEtrans(dateTimeTrans, clearinghouse.HqClearinghouseNum, json, 0);
etrans.Etype = EtransType.ItransNcpl;
Etranss.Insert(etrans);
ItransNCpl iTransNCpl = null;
try {
iTransNCpl = JsonConvert.DeserializeObject <ItransNCpl>(json); //Deserialize n-cpl.json
}
catch (Exception ex) {
ex.DoNothing();
return(Lans.g("Clearinghouse", "Failed to import json."));
}
List <CanadianNetwork> listCanadianNetworks = CanadianNetworks.GetDeepCopy();
//List of carriers from json file that were matched by electId to multiple internal carriers
List <Carrier> listUnmatchedJsonCarriers = new List <Carrier>();
List <long> listMatchedDbCarrierNums = new List <long> ();
foreach (ItransNCpl.Carrier jsonCarrier in iTransNCpl.ListCarriers) //Update carriers.
{
string jsonCarrierPhone = jsonCarrier.Telephone?.First().Value; //Will be empty string if not found.
List <OpenDentBusiness.Carrier> listDbCarriers = Carriers.GetAllByElectId(jsonCarrier.Bin).FindAll(x => x.IsCDA);
if (listDbCarriers.Count > 1) //Some Canadian carriers share ElectId, need to filter further. This happens with carrier resellers.
{
#region Additional matching based on phone numbers, 'continues' loop if a single match is not found.
List <OpenDentBusiness.Carrier> listPhoneMatchedDbCarriers = listDbCarriers.FindAll(x =>
TelephoneNumbers.AreNumbersEqual(x.Phone, jsonCarrierPhone)
);
if (listPhoneMatchedDbCarriers.Count != 1) //Either 0 or multiple matches, either way do not update any carriers.
//When 0 matches found: jsonCarrier changed their phone number, can not determine which carrier to update.
//E.G. - JsonCarrier A matched to OD carriers B and C by electId.
//Phone number from JsonCarrier A did not match either carrier B or C due to jsonCarrier phone number change.
//Future iterations for jsonCarrier D might match to carrier B or C if phone number for jsonCarrier D did not change.
//If jsonCarrier D is matched to single OD carrier, then jsonCarrier A will attempt to match near end of method to a unmatched internal carrier.
//If ther are no future matches to OD carrier B or C then all unmatched jsonCarriers will not be imported and no OD carries will not be updated.
//----------------------------------------------------------------------//
//When greater than 1: jsonCarrier number not changed and both internal carriers have matching electIds and phone numbers.
//This should be rare, most likely a setup error.
//There should not be multiple carriers that share electId and phone numbers. User should change or remove one of the matched carriers.
{
listUnmatchedJsonCarriers.Add(jsonCarrier);
continue;
}
listDbCarriers = listPhoneMatchedDbCarriers;
#endregion
}
//At this point listDbCarriers should either be empty or contain a single OD carrier.
OpenDentBusiness.Carrier carrierInDb = listDbCarriers.FirstOrDefault(); //Null if list is empty.
if (carrierInDb == null) //Carrier can not be matched to internal Carrier based on ElectID.
{
#region Insert new carrier
if (!fieldsToImport.HasFlag(ItransImportFields.AddMissing))
{
continue;
}
OpenDentBusiness.Carrier carrierNew = new OpenDentBusiness.Carrier();
carrierNew.CanadianEncryptionMethod = 1; //Default. Deprecated for all Canadian carriers and will never be any other value.
TrySetCanadianNetworkNum(jsonCarrier, carrierNew, listCanadianNetworks);
carrierNew.ElectID = jsonCarrier.Bin;
carrierNew.IsCDA = true;
carrierNew.CarrierName = jsonCarrier.Name.En;
carrierNew.Phone = TelephoneNumbers.AutoFormat(jsonCarrierPhone);
if (jsonCarrier.Address.Count() > 0)
{
Address add = jsonCarrier.Address.First();
carrierNew.Address = add.Street1;
carrierNew.Address2 = add.Street2;
carrierNew.City = add.City;
carrierNew.State = add.Province;
carrierNew.Zip = add.Postal_Code;
}
carrierNew.CanadianSupportedTypes = GetSupportedTypes(jsonCarrier);
carrierNew.CDAnetVersion = POut.Int(jsonCarrier.Versions.Max(x => PIn.Int(x))).PadLeft(2, '0'); //Version must be in 2 digit format. ex. 02.
carrierNew.CarrierName = jsonCarrier.Name.En;
try {
Carriers.Insert(carrierNew);
}
catch (Exception ex) {
ex.DoNothing();
}
#endregion
continue;
}
listMatchedDbCarrierNums.Add(carrierInDb.CarrierNum);
UpdateCarrierInDb(carrierInDb, jsonCarrier, listCanadianNetworks, fieldsToImport, jsonCarrierPhone, isAutomatic);
}
foreach (Carrier jsonCarrier in listUnmatchedJsonCarriers)
{
List <OpenDentBusiness.Carrier> listDbCarriers = Carriers.GetWhere(x => x.IsCDA &&
x.ElectID == jsonCarrier.Bin && !listMatchedDbCarrierNums.Contains(x.CarrierNum)
);
if (listDbCarriers.Count != 1) //Either 0 or multiple matches, either way do not update any carriers.
{
continue;
}
OpenDentBusiness.Carrier carrierInDb = listDbCarriers.FirstOrDefault();
string jsonCarrierPhone = jsonCarrier.Telephone?.First().Value;
UpdateCarrierInDb(carrierInDb, jsonCarrier, listCanadianNetworks, fieldsToImport, jsonCarrierPhone, isAutomatic);
}
return(""); //Blank string represents a completed update.
}
///<summary>If not using clinics then supply an empty list of clinicNums. listClinicNums must have at least one item if using clinics.</summary>
public static DataTable GetInsTable(DateTime dateFrom, DateTime dateTo, List <long> listProvNums, List <long> listClinicNums,
List <long> listInsuranceTypes, List <long> listClaimPayGroups, bool hasAllProvs, bool hasAllClinics, bool hasInsuranceTypes, bool isGroupedByPatient,
bool hasAllClaimPayGroups, bool doShowProvSeparate)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), dateFrom, dateTo, listProvNums, listClinicNums, listInsuranceTypes, listClaimPayGroups,
hasAllProvs, hasAllClinics, hasInsuranceTypes, isGroupedByPatient, hasAllClaimPayGroups, doShowProvSeparate));
}
string whereProv = "";
if (!hasAllProvs)
{
whereProv += " AND claimproc.ProvNum IN(";
for (int i = 0; i < listProvNums.Count; i++)
{
if (i > 0)
{
whereProv += ",";
}
whereProv += POut.Long(listProvNums[i]);
}
whereProv += ") ";
}
string whereClin = "";
//reports should no longer use the cache
bool hasClinicsEnabled = ReportsComplex.RunFuncOnReportServer(() => Prefs.HasClinicsEnabledNoCache);
if (hasClinicsEnabled)
{
whereClin += " AND claimproc.ClinicNum IN(";
for (int i = 0; i < listClinicNums.Count; i++)
{
if (i > 0)
{
whereClin += ",";
}
whereClin += POut.Long(listClinicNums[i]);
}
whereClin += ") ";
}
string whereClaimPayGroup = "";
if (!hasAllClaimPayGroups)
{
whereClaimPayGroup = " AND PayGroup IN (" + String.Join(",", listClaimPayGroups) + ") ";
}
string queryIns =
@"SELECT claimproc.DateCP,carrier.CarrierName,MAX("
+ DbHelper.Concat("patient.LName", "', '", "patient.FName", "' '", "patient.MiddleI") + @") lfname,GROUP_CONCAT(DISTINCT provider.Abbr) Provider, ";
if (hasClinicsEnabled)
{
queryIns += "clinic.Abbr Clinic, ";
}
queryIns += @"claimpayment.CheckNum,SUM(claimproc.InsPayAmt) amt,claimproc.ClaimNum,claimpayment.PayType,COUNT(DISTINCT claimproc.PatNum) countPats
FROM claimproc
LEFT JOIN insplan ON claimproc.PlanNum = insplan.PlanNum
LEFT JOIN patient ON claimproc.PatNum = patient.PatNum
LEFT JOIN carrier ON carrier.CarrierNum = insplan.CarrierNum
LEFT JOIN provider ON provider.ProvNum=claimproc.ProvNum
LEFT JOIN claimpayment ON claimproc.ClaimPaymentNum = claimpayment.ClaimPaymentNum " ;
if (hasClinicsEnabled)
{
queryIns += "LEFT JOIN clinic ON clinic.ClinicNum=claimproc.ClinicNum ";
}
queryIns += "WHERE (claimproc.Status=1 OR claimproc.Status=4) " //received or supplemental
+ whereProv
+ whereClin
+ whereClaimPayGroup
+ "AND claimpayment.CheckDate >= " + POut.Date(dateFrom) + " "
+ "AND claimpayment.CheckDate <= " + POut.Date(dateTo) + " ";
if (!hasInsuranceTypes && listInsuranceTypes.Count > 0)
{
queryIns += "AND claimpayment.PayType IN (";
for (int i = 0; i < listInsuranceTypes.Count; i++)
{
if (i > 0)
{
queryIns += ",";
}
queryIns += POut.Long(listInsuranceTypes[i]);
}
queryIns += ") ";
}
queryIns += @"GROUP BY claimproc.DateCP,claimproc.ClaimPaymentNum,";
if (doShowProvSeparate)
{
queryIns += @"provider.ProvNum,";
}
if (hasClinicsEnabled)
{
queryIns += "claimproc.ClinicNum,clinic.Abbr,";
}
queryIns += "carrier.CarrierName,claimpayment.CheckNum";
if (isGroupedByPatient)
{
queryIns += ",patient.PatNum";
}
queryIns += " ORDER BY claimpayment.PayType,claimproc.DateCP,lfname";
if (!hasInsuranceTypes && listInsuranceTypes.Count == 0)
{
queryIns = DbHelper.LimitOrderBy(queryIns, 0);
}
DataTable table = ReportsComplex.RunFuncOnReportServer(() => Db.GetTable(queryIns));
foreach (DataRow row in table.Rows)
{
//If there is more than one patient attached to a check, we will append an asterisk to the end.
int countPats = PIn.Int(row["countPats"].ToString());
if (countPats > 1)
{
row["lfname"] = row["lfname"].ToString().TrimEnd() + "*";
}
}
table.Columns.Remove("countPats"); //Remove this column because we don't want it to show on the report
return(table);
}
public static List <FeatureRequest> GetAll()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <FeatureRequest> >(MethodBase.GetCurrentMethod()));
}
//Create an ODThread so that we can safely change the database connection settings without affecting the calling method's connection.
ODThread odThread = new ODThread(new ODThread.WorkerDelegate((ODThread o) => {
//Always set the thread static database connection variables to set the serviceshq db conn.
#if DEBUG
new DataConnection().SetDbT("localhost", "bugs", "root", "", "", "", DatabaseType.MySql, true);
#else
new DataConnection().SetDbT("server", "bugs", "root", "", "", "", DatabaseType.MySql, true);
#endif
#region WebServiceCustomerUpdates.FeatureRequestGetList
string command = "SELECT request.RequestId,Approval,Description,Difficulty,"
+ "vote.AmountPledged,IFNULL(vote.Points,0) AS points,vote.IsCritical,request.PatNum,"
+ "TotalCritical,TotalPledged,TotalPoints,Weight "
+ "FROM request "
+ "LEFT JOIN vote ON vote.PatNum=1486 AND vote.RequestId=request.RequestId "
+ "WHERE (Approval=" + POut.Int((int)ApprovalEnum.New) + " "
+ "OR Approval=" + POut.Int((int)ApprovalEnum.Approved) + " "
+ "OR Approval=" + POut.Int((int)ApprovalEnum.InProgress) + " "
+ "OR Approval=" + POut.Int((int)ApprovalEnum.Complete) + ") "
+ "ORDER BY Approval, Weight DESC, points DESC";
DataTable raw = Db.GetTable(command);
DataRow row;
DataTable table = new DataTable();
table.TableName = "Table";
table.Columns.Add("approval");
table.Columns.Add("Description");
table.Columns.Add("Difficulty");
table.Columns.Add("isMine");
table.Columns.Add("myVotes");
table.Columns.Add("RequestId");
table.Columns.Add("totalVotes");
table.Columns.Add("Weight");
table.Columns.Add("personalVotes");
table.Columns.Add("personalCrit");
table.Columns.Add("personalPledged");
double myPledge;
bool myCritical;
double totalPledged;
int totalCritical;
for (int i = 0; i < raw.Rows.Count; i++)
{
row = table.NewRow();
row["RequestId"] = raw.Rows[i]["RequestId"].ToString();
//myVotes,myCritical,myPledge------------------------------------------------------
row["myVotes"] = raw.Rows[i]["Points"].ToString();
row["personalVotes"] = raw.Rows[i]["Points"].ToString();
if (row["myVotes"].ToString() == "0")
{
row["myVotes"] = "";
}
myCritical = PIn.Bool(raw.Rows[i]["IsCritical"].ToString());
if (myCritical == true)
{
row["personalCrit"] = "1";
if (row["myVotes"].ToString() != "")
{
row["myVotes"] += "\r\n";
}
row["myVotes"] += "Critical";
}
else
{
row["personalCrit"] = "0";
}
myPledge = PIn.Double(raw.Rows[i]["AmountPledged"].ToString());
if (myPledge != 0)
{
if (row["myVotes"].ToString() != "")
{
row["myVotes"] += "\r\n";
}
row["myVotes"] += myPledge.ToString("c0");
row["personalPledged"] = myPledge.ToString();
}
else
{
row["personalPledged"] = "0";
}
//TotalPoints,TotalCritical,TotalPledged-----------------------------------------------
row["totalVotes"] = raw.Rows[i]["TotalPoints"].ToString();
if (row["totalVotes"].ToString() == "0")
{
row["totalVotes"] = "";
}
totalCritical = PIn.Int(raw.Rows[i]["TotalCritical"].ToString());
if (totalCritical != 0)
{
if (row["totalVotes"].ToString() != "")
{
row["totalVotes"] += "\r\n";
}
row["totalVotes"] += "Critical:" + totalCritical.ToString();
}
totalPledged = PIn.Double(raw.Rows[i]["TotalPledged"].ToString());
if (totalPledged != 0)
{
if (row["totalVotes"].ToString() != "")
{
row["totalVotes"] += "\r\n";
}
row["totalVotes"] += totalPledged.ToString("c0");
}
//end
row["approval"] = ((ApprovalEnum)PIn.Int(raw.Rows[i]["Approval"].ToString())).ToString();
if (raw.Rows[i]["PatNum"].ToString() == "1486")
{
row["isMine"] = "X";
}
else
{
row["isMine"] = "";
}
row["Difficulty"] = raw.Rows[i]["Difficulty"].ToString();
row["Description"] = raw.Rows[i]["Description"].ToString();
row["Weight"] = raw.Rows[i]["Weight"].ToString();
table.Rows.Add(row);
}
o.Tag = table;
#endregion
}));
odThread.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception e) => { })); //Do nothing
odThread.Name = "featureMarkAsInProgressThread";
odThread.Start(true);
if (!odThread.Join(2000)) //Give this thread up to 2 seconds to complete.
{
return(new List <FeatureRequest>());
}
DataTable tableRequests = (DataTable)odThread.Tag;
List <FeatureRequest> listFeatureRequests = new List <FeatureRequest>();
foreach (DataRow dataRow in tableRequests.Rows)
{
FeatureRequest req = new FeatureRequest();
#region Convert DataTable Into FeatureRequest
long.TryParse(dataRow["RequestId"].ToString(), out req.FeatReqNum);
string[] votes = dataRow["totalVotes"].ToString().Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
string vote = votes.FirstOrDefault(x => !x.StartsWith("Critical") && !x.StartsWith("$"));
if (!string.IsNullOrEmpty(vote))
{
long.TryParse(vote, out req.Votes);
}
vote = votes.FirstOrDefault(x => x.StartsWith("Critical"));
if (!string.IsNullOrEmpty(vote))
{
long.TryParse(vote, out req.Critical);
}
vote = votes.FirstOrDefault(x => x.StartsWith("$"));
if (!string.IsNullOrEmpty(vote))
{
float.TryParse(vote, out req.Pledge);
}
req.Difficulty = PIn.Long(dataRow["Difficulty"].ToString());
req.Weight = PIn.Float(dataRow["Weight"].ToString());
req.Approval = dataRow["Weight"].ToString();
req.Description = dataRow["Description"].ToString();
#endregion
listFeatureRequests.Add(req);
}
return(listFeatureRequests);
}
