///<summary></summary>
public static void Update(SmsPhone smsPhone)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), smsPhone);
return;
}
Crud.SmsPhoneCrud.Update(smsPhone);
}
///<summary></summary>
public static long Insert(SmsPhone smsPhone)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
smsPhone.SmsPhoneNum = Meth.GetLong(MethodBase.GetCurrentMethod(), smsPhone);
return(smsPhone.SmsPhoneNum);
}
return(Crud.SmsPhoneCrud.Insert(smsPhone));
}
///<summary>This will only be called by HQ via the listener in the event that this number has been cancelled.</summary>
public static void UpdateToInactive(string phoneNumber)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod(), phoneNumber);
return;
}
SmsPhone smsPhone = GetByPhone(phoneNumber);
if (smsPhone == null)
{
return;
}
smsPhone.DateTimeInactive = DateTime.Now;
Crud.SmsPhoneCrud.Update(smsPhone);
}
///<summary>Find all phones in the db (by PhoneNumber) and sync with listPhonesSync. If a given PhoneNumber does not already exist then insert the SmsPhone.
///If a given PhoneNumber exists in the local db but does not exist in the HQ-provided listPhoneSync, then deacitvate that phone locallly.
///Return true if a change has been made to the database.</summary>
public static bool UpdateOrInsertFromList(List <SmsPhone> listPhonesSync)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetBool(MethodBase.GetCurrentMethod(), listPhonesSync));
}
//Get all phones so we can filter as needed below.
string command = "SELECT * FROM smsphone";
List <SmsPhone> listPhonesDb = Crud.SmsPhoneCrud.SelectMany(command);
bool isChanged = false;
//Deal with phones that occur in the HQ-supplied list.
foreach (SmsPhone phoneSync in listPhonesSync)
{
SmsPhone phoneOld = listPhonesDb.FirstOrDefault(x => x.PhoneNumber == phoneSync.PhoneNumber);
//Upsert.
if (phoneOld != null) //This phone already exists. Update it to look like the phone we are trying to insert.
{
phoneOld.ClinicNum = phoneSync.ClinicNum; //The clinic may have changed so set it to the new clinic.
phoneOld.CountryCode = phoneSync.CountryCode;
phoneOld.DateTimeActive = phoneSync.DateTimeActive;
phoneOld.DateTimeInactive = phoneSync.DateTimeInactive;
phoneOld.InactiveCode = phoneSync.InactiveCode;
Update(phoneOld);
}
else //This phone is new so insert it.
{
Insert(phoneSync);
}
isChanged = true;
}
//Deal with phones which are in the local db but that do not occur in the HQ-supplied list.
foreach (SmsPhone phoneNotFound in listPhonesDb.FindAll(x => !listPhonesSync.Any(y => y.PhoneNumber == x.PhoneNumber)))
{
//This phone not found at HQ so deactivate it.
phoneNotFound.DateTimeInactive = DateTime.Now;
phoneNotFound.InactiveCode = "Phone not found at HQ";
Update(phoneNotFound);
isChanged = true;
}
return(isChanged);
}
public static DataTable GetSmsUsageLocal(List <long> listClinicNums, DateTime dateMonth, List <SmsPhone> listPhones)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateMonth, listPhones));
}
#region Initialize retVal DataTable
string strNoActivePhones = "No Active Phones";
List <SmsPhone> listSmsPhones = listPhones.Where(x => x.ClinicNum.In(listClinicNums)).ToList();
DateTime dateStart = dateMonth.Date.AddDays(1 - dateMonth.Day); //remove time portion and day of month portion. Remainder should be midnight of the first of the month
DateTime dateEnd = dateStart.AddMonths(1); //This should be midnight of the first of the following month.
//This query builds the data table that will be filled from several other queries, instead of writing one large complex query.
//It is written this way so that the queries are simple to write and understand, and makes Oracle compatibility easier to maintain.
string command = @"SELECT
0 ClinicNum,
' ' PhoneNumber,
' ' CountryCode,
0 SentMonth,
0.0 SentCharge,
0.0 SentDiscount,
0.0 SentPreDiscount,
0 ReceivedMonth,
0.0 ReceivedCharge
FROM
DUAL" ; //this is a cute way to get a data table with the correct layout without having to query any real data.
DataTable retVal = Db.GetTable(command).Clone(); //use .Clone() to get schema only, with no rows.
retVal.TableName = "SmsUsageLocal";
for (int i = 0; i < listClinicNums.Count; i++)
{
DataRow row = retVal.NewRow();
row["ClinicNum"] = listClinicNums[i];
row["PhoneNumber"] = strNoActivePhones;
SmsPhone firstActivePhone = listSmsPhones
.Where(x => x.ClinicNum == listClinicNums[i]) //phones for this clinic
.Where(x => x.DateTimeInactive.Year < 1880) //that are active
.OrderByDescending(x => x.IsPrimary)
.ThenBy(x => x.DateTimeActive)
.FirstOrDefault();
if (firstActivePhone != null)
{
row["PhoneNumber"] = firstActivePhone.PhoneNumber;
row["CountryCode"] = firstActivePhone.CountryCode;
}
row["SentMonth"] = 0;
row["SentCharge"] = 0.0;
row["SentDiscount"] = 0.0;
row["SentPreDiscount"] = 0.0;
row["ReceivedMonth"] = 0;
row["ReceivedCharge"] = 0.0;
retVal.Rows.Add(row);
}
#endregion
#region Fill retVal DataTable
//Sent Last Month
command = "SELECT ClinicNum, COUNT(*), ROUND(SUM(MsgChargeUSD),2),ROUND(SUM(MsgDiscountUSD),2)"
+ ",SUM(CASE SmsPhoneNumber WHEN '" + POut.String(SmsPhones.SHORTCODE) + "' THEN 1 ELSE 0 END) FROM smstomobile "
+ "WHERE DateTimeSent >=" + POut.Date(dateStart) + " "
+ "AND DateTimeSent<" + POut.Date(dateEnd) + " "
+ "AND MsgChargeUSD>0 GROUP BY ClinicNum";
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["SentMonth"] = table.Rows[i][1]; //.ToString();
retVal.Rows[j]["SentCharge"] = table.Rows[i][2]; //.ToString();
retVal.Rows[j]["SentDiscount"] = table.Rows[i][3];
retVal.Rows[j]["SentPreDiscount"] = PIn.Double(retVal.Rows[j]["SentCharge"].ToString()) + PIn.Double(retVal.Rows[j]["SentDiscount"].ToString());
//No active phone but at least one of these messages sent from Short Code
if (retVal.Rows[j]["PhoneNumber"].ToString() == strNoActivePhones && PIn.Long(table.Rows[i][4].ToString()) > 0)
{
retVal.Rows[j]["PhoneNumber"] = POut.String(SmsPhones.SHORTCODE); //display "SHORTCODE" as primary number.
}
break;
}
}
//Received Month
command = "SELECT ClinicNum, COUNT(*),SUM(CASE SmsPhoneNumber WHEN '" + POut.String(SmsPhones.SHORTCODE) + "' THEN 1 ELSE 0 END) FROM smsfrommobile "
+ "WHERE DateTimeReceived >=" + POut.Date(dateStart) + " "
+ "AND DateTimeReceived<" + POut.Date(dateEnd) + " "
+ "GROUP BY ClinicNum";
table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["ReceivedMonth"] = table.Rows[i][1].ToString();
retVal.Rows[j]["ReceivedCharge"] = "0";
//No active phone but at least one of these messages sent from Short Code
if (retVal.Rows[j]["PhoneNumber"].ToString() == strNoActivePhones && PIn.Long(table.Rows[i][2].ToString()) > 0)
{
retVal.Rows[j]["PhoneNumber"] = POut.String(SmsPhones.SHORTCODE); //display "SHORTCODE" as primary number.
}
break;
}
}
#endregion
return(retVal);
}
///<summary>Attempts to find exact match for patient. If found, creates commlog, associates Patnum, and inserts into DB.
///Otherwise, it simply inserts SmsFromMobiles into the DB. ClinicNum should have already been set before calling this function.</summary>
public static void ProcessInboundSms(List <SmsFromMobile> listMessages)
{
if (listMessages == null || listMessages.Count == 0)
{
return;
}
List <SmsBlockPhone> listBlockedPhones = SmsBlockPhones.GetDeepCopy();
for (int i = 0; i < listMessages.Count; i++)
{
SmsFromMobile sms = listMessages[i];
if (listBlockedPhones.Any(x => TelephoneNumbers.AreNumbersEqual(x.BlockWirelessNumber, sms.MobilePhoneNumber)))
{
continue; //The office has blocked this number.
}
sms.DateTimeReceived = DateTime.Now;
SmsPhone smsPhone = SmsPhones.GetByPhone(sms.SmsPhoneNumber);
string countryCode = CultureInfo.CurrentCulture.Name.Substring(CultureInfo.CurrentCulture.Name.Length - 2);
if (smsPhone != null)
{
sms.ClinicNum = smsPhone.ClinicNum;
countryCode = smsPhone.CountryCode;
}
//First try the clinic that belongs to this phone.
List <long> listClinicNums = new List <long>();
if (sms.ClinicNum != 0)
{
listClinicNums.Add(sms.ClinicNum);
}
List <long[]> listPatNums = FindPatNums(sms.MobilePhoneNumber, countryCode, listClinicNums);
if (listPatNums.Count == 0 && listClinicNums.Count > 0) //Could not find that patient in this clinic so try again for all clinics.
{
listPatNums = FindPatNums(sms.MobilePhoneNumber, countryCode);
}
sms.MatchCount = listPatNums.Count;
//Item1=PatNum; Item2=Guarantor
if (listPatNums.Count == 0 || listPatNums.Select(x => x[1]).Distinct().ToList().Count != 1)
{
//We could not find definitive match, either 0 matches found, or more than one match found with different garantors
Insert(sms);
continue;
}
if (listPatNums.Count == 1)
{
sms.PatNum = listPatNums[0][0]; //PatNum
}
else
{
sms.PatNum = listPatNums[0][1]; //GuarantorNum; more than one match, but all have the same garantor.
}
Commlog comm = new Commlog()
{
CommDateTime = sms.DateTimeReceived,
Mode_ = CommItemMode.Text,
Note = sms.MsgText,
PatNum = sms.PatNum,
CommType = Commlogs.GetTypeAuto(CommItemTypeAuto.TEXT),
SentOrReceived = CommSentOrReceived.Received
};
sms.CommlogNum = Commlogs.Insert(comm);
Insert(sms);
}
UpdateSmsNotification();
}
///<summary>Attempts to find exact match for patient. If found, creates commlog, associates Patnum, and inserts into DB.
///Otherwise, it simply inserts SmsFromMobiles into the DB. ClinicNum should have already been set before calling this function.</summary>
public static void ProcessInboundSms(List <SmsFromMobile> listMessages)
{
if (listMessages == null || listMessages.Count == 0)
{
return;
}
List <SmsBlockPhone> listBlockedPhones = SmsBlockPhones.GetDeepCopy();
for (int i = 0; i < listMessages.Count; i++)
{
SmsFromMobile sms = listMessages[i];
if (listBlockedPhones.Any(x => TelephoneNumbers.AreNumbersEqual(x.BlockWirelessNumber, sms.MobilePhoneNumber)))
{
continue; //The office has blocked this number.
}
sms.DateTimeReceived = DateTime.Now;
string countryCode = CultureInfo.CurrentCulture.Name.Substring(CultureInfo.CurrentCulture.Name.Length - 2);
if (sms.SmsPhoneNumber != SmsPhones.SHORTCODE)
{
SmsPhone smsPhone = SmsPhones.GetByPhone(sms.SmsPhoneNumber);
if (smsPhone != null)
{
sms.ClinicNum = smsPhone.ClinicNum;
countryCode = smsPhone.CountryCode;
}
}
if (!PrefC.HasClinicsEnabled)
{
//We want customer side records of this message to list SmsPhones.SHORTCODE as the number on which the message was sent. This ensures we do
//not record this communication on a different valid SmsPhone/VLN that it didn't truly take place on. However, on the HQ side, we want
//records of this communication to be listed as having taken place on the actual Short Code number. In the case of a Short Code,
//sms.SmsPhoneNumber will read "SHORTCODE", which won't be found in the customer's SmsPhone table. As a result, the code to use the
//customer's SmsPhone.ClinicNum and Country code cannot be used. Since this code was intended to handle the case where the customer had
//turned clinics on->off, we will specifically check if the customer has disabled clinics and only then change the sms.ClinicNum.
//Otherwise, trust HQ sent the correct ClinicNum. Since we expect to only use Short Codes in the US/Canada, we will trust the server we
//are processing inbound sms will have the correct country code, which will be used here.
sms.ClinicNum = 0;
}
//First try the clinic that belongs to this phone.
List <long> listClinicNums = new List <long>();
if (sms.ClinicNum != 0)
{
listClinicNums.Add(sms.ClinicNum);
}
List <long[]> listPatNums = FindPatNums(sms.MobilePhoneNumber, countryCode, listClinicNums);
if (listPatNums.Count == 0 && listClinicNums.Count > 0) //Could not find that patient in this clinic so try again for all clinics.
{
listPatNums = FindPatNums(sms.MobilePhoneNumber, countryCode);
}
sms.MatchCount = listPatNums.Count;
//Item1=PatNum; Item2=Guarantor
if (listPatNums.Count == 0 || listPatNums.Select(x => x[1]).Distinct().ToList().Count != 1)
{
//We could not find definitive match, either 0 matches found, or more than one match found with different garantors
Insert(sms);
//Alert ODMobile where applicable.
PushNotificationUtils.ODM_NewTextMessage(sms);
continue;
}
if (listPatNums.Count == 1)
{
sms.PatNum = listPatNums[0][0]; //PatNum
}
else
{
sms.PatNum = listPatNums[0][1]; //GuarantorNum; more than one match, but all have the same garantor.
}
Commlog comm = new Commlog()
{
CommDateTime = sms.DateTimeReceived,
Mode_ = CommItemMode.Text,
Note = sms.MsgText,
PatNum = sms.PatNum,
CommType = Commlogs.GetTypeAuto(CommItemTypeAuto.TEXT),
SentOrReceived = CommSentOrReceived.Received
};
sms.CommlogNum = Commlogs.Insert(comm);
Insert(sms);
//Alert ODMobile where applicable.
PushNotificationUtils.ODM_NewTextMessage(sms, sms.PatNum);
}
//We used to update the SmsNotification indicator via a queries and a signal here. Now managed by the eConnector.
}
public static DataTable GetSmsUsageLocal(List <long> listClinicNums, DateTime dateMonth)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetTable(MethodBase.GetCurrentMethod(), listClinicNums, dateMonth));
}
#region Initialize retVal DataTable
List <SmsPhone> listSmsPhones = GetForClinics(listClinicNums);
DateTime dateStart = dateMonth.Date.AddDays(1 - dateMonth.Day); //remove time portion and day of month portion. Remainder should be midnight of the first of the month
DateTime dateEnd = dateStart.AddMonths(1); //This should be midnight of the first of the following month.
//This query builds the data table that will be filled from several other queries, instead of writing one large complex query.
//It is written this way so that the queries are simple to write and understand, and makes Oracle compatibility easier to maintain.
string command = @"SELECT
0 ClinicNum,
' ' PhoneNumber,
' ' CountryCode,
0 SentMonth,
0.0 SentCharge,
0 ReceivedMonth,
0.0 ReceivedCharge
FROM
DUAL" ; //this is a simple way to get a data table with the correct layout without having to query any real data.
DataTable retVal = Db.GetTable(command).Clone(); //use .Clone() to get schema only, with no rows.
retVal.TableName = "SmsUsageLocal";
for (int i = 0; i < listClinicNums.Count; i++)
{
DataRow row = retVal.NewRow();
row["ClinicNum"] = listClinicNums[i];
row["PhoneNumber"] = "No Active Phones";
SmsPhone firstActivePhone = listSmsPhones
.Where(x => x.ClinicNum == listClinicNums[i]) //phones for this clinic
.Where(x => x.DateTimeInactive.Year < 1880) //that are active
.FirstOrDefault(x => x.DateTimeActive == listSmsPhones //and have the smallest active date (the oldest/first phones activated)
.Where(y => y.ClinicNum == x.ClinicNum)
.Where(y => y.DateTimeInactive.Year < 1880)
.Min(y => y.DateTimeActive));
if (firstActivePhone != null)
{
row["PhoneNumber"] = firstActivePhone.PhoneNumber;
row["CountryCode"] = firstActivePhone.CountryCode;
}
row["SentMonth"] = 0;
row["SentCharge"] = 0.0;
row["ReceivedMonth"] = 0;
row["ReceivedCharge"] = 0.0;
retVal.Rows.Add(row);
}
#endregion
#region Fill retVal DataTable
//Sent Last Month
command = "SELECT ClinicNum, COUNT(*), ROUND(SUM(MsgChargeUSD),2) FROM smstomobile "
+ "WHERE DateTimeSent >=" + POut.Date(dateStart) + " "
+ "AND DateTimeSent<" + POut.Date(dateEnd) + " "
+ "AND MsgChargeUSD>0 GROUP BY ClinicNum";
DataTable table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["SentMonth"] = table.Rows[i][1]; //.ToString();
retVal.Rows[j]["SentCharge"] = table.Rows[i][2]; //.ToString();
break;
}
}
//Received Month
command = "SELECT ClinicNum, COUNT(*) FROM smsfrommobile "
+ "WHERE DateTimeReceived >=" + POut.Date(dateStart) + " "
+ "AND DateTimeReceived<" + POut.Date(dateEnd) + " "
+ "GROUP BY ClinicNum";
table = Db.GetTable(command);
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < retVal.Rows.Count; j++)
{
if (retVal.Rows[j]["ClinicNum"].ToString() != table.Rows[i]["ClinicNum"].ToString())
{
continue;
}
retVal.Rows[j]["ReceivedMonth"] = table.Rows[i][1].ToString();
retVal.Rows[j]["ReceivedCharge"] = "0";
break;
}
}
#endregion
return(retVal);
}