///<summary>Makes sure the tablename is valid. Goes through each column and makes sure that the column is present and that the type in the database is a supported type for this C# data type. Throws exception if it fails.</summary>
public static void ValidateTypes(Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return; //can't validate
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
//We also are going to check to make sure there are not any extra columns in the database that are not in the code.
HashSet <string> setDbColumns = new HashSet <string>(table.Select().Select(x => x["COLUMN_NAME"].ToString()));
FieldInfo[] fields = typeClass.GetFields();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
ValidateColumn(dbName, tablename, fields[i], table);
setDbColumns.Remove(fields[i].Name);
}
if (setDbColumns.Count > 0)
{
throw new Exception("Table " + tablename + " has columns that are not a part of its corresponding TableType class: "
+ string.Join(", ", setDbColumns));
}
}
///<summary>This gets all new fields which are found in the table definition but not in the database. Result will be empty if the table itself is not in the database.</summary>
public static List <FieldInfo> GetNewFields(FieldInfo[] fields, Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return(new List <FieldInfo>());
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
List <FieldInfo> retVal = new List <FieldInfo>();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
bool found = false;;
for (int t = 0; t < table.Rows.Count; t++)
{
if (table.Rows[t]["COLUMN_NAME"].ToString().ToLower() == fields[i].Name.ToLower())
{
found = true;
}
}
if (!found)
{
retVal.Add(fields[i]);
}
}
return(retVal);
}
private void cmb_name_SelectedIndexChanged(object sender, EventArgs e)
{
//Birthday
//Appt Confirmation
//Appt Reminder
//Appt Recall
//Insurance Service
//Custom Query
txt_msgbody.Text = "";
if (cmb_name.Text.Trim() == "Birthday")
{
cmb_daysbefore_after.Enabled = false;
cmb_daysbefore_after.Value = 0;
txt_msgbody.Text = DataCore.GetScalar("select valuestring from preference where prefname='BirthdayPostcardMsg'");
}
else if (cmb_name.Text == "Appt Confirmation")
{
cmb_daysbefore_after.Enabled = true;
txt_msgbody.Text = DataCore.GetScalar("select valuestring from preference where prefname='ConfirmTextMessage'");
}
else if (cmb_name.Text == "Appt Reminder")
{
cmb_daysbefore_after.Enabled = true;
txt_msgbody.Text = DataCore.GetScalar("select valuestring from preference where prefname='ConfirmTextMessage'").Replace("confirm", "remind");;
}
else if (cmb_name.Text == "Appt Recall")
{
cmb_daysbefore_after.Enabled = true;
txt_msgbody.Text = DataCore.GetScalar("select valuestring from preference where prefname='RecallPostcardMessage'");
}
else
{
cmb_daysbefore_after.Enabled = true;
}
}
///<summary>Inserts the IntrospectionItems preference into the database if it is missing. Runs an update statement if the pref already exists.
///Always sets ValueString to the value passed in. Manually refreshes the preference cache so that the change instantly takes place.</summary>
public static void UpsertPref(string valueString = "")
{
string command = "SELECT COUNT(*) FROM preference WHERE PrefName='IntrospectionItems'";
if (DataCore.GetScalar(command) == "0")
{
command = "INSERT INTO preference (PrefName,ValueString) VALUES('IntrospectionItems','" + POut.String(valueString) + "')";
DataCore.NonQ(command);
}
else
{
command = "UPDATE preference SET ValueString='" + POut.String(valueString) + "' WHERE PrefName='IntrospectionItems'";
DataCore.NonQ(command);
}
Prefs.RefreshCache();
Introspection.ClearDictOverrides();
}
///<summary>Manually adds the few CDCREC codes necessary for the HL7 unit tests.</summary>
private static void AddCdcrecCodes()
{
string command = "SELECT COUNT(*) FROM cdcrec";
if (DataCore.GetScalar(command) == "0")
{
Cdcrecs.Insert(new Cdcrec()
{
CdcrecCode = "2106-3",
HeirarchicalCode = "R5",
Description = "WHITE"
});
Cdcrecs.Insert(new Cdcrec()
{
CdcrecCode = "2135-2",
HeirarchicalCode = "E1",
Description = "HISPANIC OR LATINO"
});
}
}
///<summary>Makes sure the tablename is valid. Goes through each column and makes sure that the column is present and that the type in the database is a supported type for this C# data type. Throws exception if it fails.</summary>
public static void ValidateTypes(Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return; //can't validate
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
FieldInfo[] fields = typeClass.GetFields();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
ValidateColumn(dbName, tablename, fields[i], table);
}
}
///<summary>Writes any necessary queries to the end of the ConvertDatabase file. Usually zero or one. The convertDbFile could also be the one in the Mobile folder.</summary>
///<param name="logFileName">If this is blank, will display the results in a MessageBox, otherwise writes the results to the file.</param>
public static void Write(string convertDbFile, Type typeClass, string dbName, bool isMobile, bool doRunQueries, bool doAppendToConvertDbFile,
string logFileName = "")
{
StringBuilder strb;
FieldInfo[] fields = typeClass.GetFields(); //We can't assume they are in the correct order.
FieldInfo priKey = null;
FieldInfo priKey1 = null;
FieldInfo priKey2 = null;
if (isMobile)
{
priKey1 = CrudGenHelper.GetPriKeyMobile1(fields, typeClass.Name);
priKey2 = CrudGenHelper.GetPriKeyMobile2(fields, typeClass.Name);
}
else
{
priKey = CrudGenHelper.GetPriKey(fields, typeClass.Name);
}
string tablename = CrudGenHelper.GetTableName(typeClass); //in lowercase now.
string priKeyParam = null;
string priKeyParam1 = null;
string priKeyParam2 = null;
if (isMobile)
{
priKeyParam1 = priKey1.Name.Substring(0, 1).ToLower() + priKey1.Name.Substring(1); //lowercase initial letter. Example customerNum
priKeyParam2 = priKey2.Name.Substring(0, 1).ToLower() + priKey2.Name.Substring(1); //lowercase initial letter. Example patNum
}
else
{
priKeyParam = priKey.Name.Substring(0, 1).ToLower() + priKey.Name.Substring(1); //lowercase initial letter. Example patNum
}
string obj = typeClass.Name.Substring(0, 1).ToLower() + typeClass.Name.Substring(1); //lowercase initial letter. Example feeSched or feeSchedm
List <FieldInfo> fieldsExceptPri = null;
if (isMobile)
{
fieldsExceptPri = CrudGenHelper.GetFieldsExceptPriKey(fields, priKey2); //for mobile, only excludes PK2
}
else
{
fieldsExceptPri = CrudGenHelper.GetFieldsExceptPriKey(fields, priKey);
}
CrudSpecialColType specialType;
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
if (!CrudGenHelper.IsMissingInGeneral(typeClass))
{
strb = new StringBuilder();
strb.Append("This table was not found in the database:"
+ rn + tablename);
if (doAppendToConvertDbFile)
{
strb.Append(rn + "Query will be found at the end of " + Path.GetFileName(convertDbFile));
}
if (doRunQueries)
{
strb.Append(rn + "Table will be added to database.");
}
if (string.IsNullOrEmpty(logFileName))
{
MessageBox.Show(strb.ToString());
}
else
{
File.AppendAllText(logFileName, strb.ToString() + "\r\n");
}
strb = new StringBuilder();
strb.Append(rn + rn + t4 + "/*");
List <DbSchemaCol> cols = null;
if (isMobile)
{
cols = CrudQueries.GetListColumns(priKey1.Name, priKey2.Name, fieldsExceptPri, true);
}
else
{
cols = CrudQueries.GetListColumns(priKey.Name, null, fieldsExceptPri, false);
}
strb.Append("\r\n" + CrudSchemaRaw.AddTable(tablename, cols, 4, isMobile, doRunQueries));
strb.Append(rn + t4 + "*/");
if (doAppendToConvertDbFile)
{
File.AppendAllText(convertDbFile, strb.ToString());
}
}
}
List <FieldInfo> newColumns = CrudGenHelper.GetNewFields(fields, typeClass, dbName);
if (newColumns.Count > 0)
{
strb = new StringBuilder();
strb.Append("The following columns were not found in the database.");
for (int f = 0; f < newColumns.Count; f++)
{
strb.Append(rn + tablename + "." + newColumns[f].Name);
}
if (doAppendToConvertDbFile)
{
strb.Append(rn + "Query will be found at the end of " + Path.GetFileName(convertDbFile));
}
if (doRunQueries)
{
strb.Append(rn + "Column will be added to table.");
}
if (string.IsNullOrEmpty(logFileName))
{
MessageBox.Show(strb.ToString()); //one message for all new columns in a table.
}
else
{
File.AppendAllText(logFileName, strb.ToString() + "\r\n");
}
strb = new StringBuilder();
strb.Append(rn + rn + t4 + "/*");
for (int f = 0; f < newColumns.Count; f++)
{
specialType = CrudGenHelper.GetSpecialType(newColumns[f]);
OdDbType odtype = GetOdDbTypeFromColType(newColumns[f].FieldType, specialType);
TextSizeMySqlOracle textsize = TextSizeMySqlOracle.Small;
if (specialType.HasFlag(CrudSpecialColType.TextIsClob))
{
textsize = TextSizeMySqlOracle.Medium;
}
DbSchemaCol col = new DbSchemaCol(newColumns[f].Name, odtype, textsize);
strb.Append(CrudSchemaRaw.AddColumnEnd(tablename, col, 4, doRunQueries, typeClass));
}
strb.Append(rn + t4 + "*/");
if (doAppendToConvertDbFile)
{
File.AppendAllText(convertDbFile, strb.ToString());
}
}
}
public void FillSchedules(Boolean IsFirstTime)
{
try
{
SchedulesView.Items.Clear();
ListViewItem lv = null;
EventList = new List <Shcedule>();
string command = "";
string XMLStr = DataCore.GetScalar("select valuestring from preference where prefname='ConfigXMLFile'");
if (IsFirstTime)
{
if (string.IsNullOrEmpty(XMLStr))
{
return;
}
else
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(XMLStr);
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml"))
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml");
}
doc.Save(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml");
}
}
else
{
if (string.IsNullOrEmpty(XMLStr))
{
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml"))
{
#region // inserting
String xmlText = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml");
command = "insert into preference (prefname,valuestring) values ('ConfigXMLFile','" + xmlText.ToString().Trim().Replace("'", "") + "')";
DataCore.NonQ(command.ToString(), false);
#endregion
}
}
else
{
String xmlText = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml");
DataCore.NonQ("update preference set valuestring='" + xmlText.ToString().Trim().Replace("'", "") + "' where prefname='ConfigXMLFile'");
}
}
string path = AppDomain.CurrentDomain.BaseDirectory + "\\config.xml";
DataSet ds = new DataSet();
ds.ReadXml(path);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
EventList.Add(new Shcedule
{
ScheduleName = dr["ScheduleName"].ToString(),
StartDate = Convert.ToDateTime(dr["StartDate"]),
RunDays = Convert.ToInt16(dr["RunDays"]),
RunDaysBeforeAfter = Convert.ToInt16(dr["RunDaysBeforeAfter"]),
Type = dr["Type"].ToString(),
Message = dr["Message"].ToString()
});
lv = SchedulesView.Items.Add(dr["Type"].ToString());
lv.SubItems.Add(dr["ScheduleName"].ToString());
lv.SubItems.Add(Convert.ToDateTime(dr["StartDate"]).ToShortTimeString());
lv.SubItems.Add(dr["RunDaysBeforeAfter"].ToString());
lv.SubItems.Add(dr["Message"].ToString());
}
}
}
catch (Exception pcex)
{
// clsErrorLog.CatchException(pcex.Message.ToString(), pcex.StackTrace.ToString(), pcex.HelpLink != null ? pcex.HelpLink.ToString() : "", pcex.InnerException != null ? pcex.InnerException.ToString() : "", pcex.Source.ToString(), pcex.TargetSite.ToString());
}
}
private void CreateBtn_Click(object sender, EventArgs e)
{
CreateBtn.Enabled = false;
try
{
string XMLStr = DataCore.GetScalar("select valuestring from preference where prefname='ConfigXMLFile'");
if (string.IsNullOrEmpty(XMLStr))
{
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml"))
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + "\\config.xml");
}
}
string path = AppDomain.CurrentDomain.BaseDirectory + "\\config.xml";
if (cmb_name.Text == "---Select---")
{
MessageBox.Show("Please select name.");
}
else if (string.IsNullOrEmpty(txtfrequency.Text))
{
MessageBox.Show("Please enter frequency.");
txtfrequency.Focus();
}
else if (string.IsNullOrEmpty(txt_msgbody.Text))
{
MessageBox.Show("Please enter message body.");
txt_msgbody.Focus();
}
else
{
string Schedule = "";
if (RunDaily.Checked)
{
Schedule = "RunDaily";
}
else if (RunIntervals.Checked)
{
Schedule = "RunParticularDate";
}
if (File.Exists(path) == false)
{
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Indent = true;
xmlWriterSettings.NewLineOnAttributes = true;
using (XmlWriter xmlWriter = XmlWriter.Create(path, xmlWriterSettings))
{
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("Schedules");
xmlWriter.WriteStartElement("Schedule");
xmlWriter.WriteElementString("ScheduleName", Schedule);
xmlWriter.WriteElementString("StartDate", m_startTimePicker.Value.ToString());
xmlWriter.WriteElementString("Type", cmb_name.Text);
xmlWriter.WriteElementString("RunDays", txtfrequency.Text.Trim());
xmlWriter.WriteElementString("RunDaysBeforeAfter", cmb_daysbefore_after.Value.ToString());
xmlWriter.WriteElementString("Message", txt_msgbody.Text.Trim().ToString());
xmlWriter.WriteEndElement(); // Schedule
xmlWriter.WriteEndElement(); // Schedules
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
xmlWriter.Close();
}
}
else
{
XDocument xDocument = XDocument.Load(path);
XElement root = xDocument.Element("Schedules");
IEnumerable <XElement> rows = root.Descendants("Schedule");
XElement firstRow = rows.FirstOrDefault();
if (firstRow != null)
{
firstRow.AddBeforeSelf(
new XElement("Schedule",
new XElement("ScheduleName", Schedule),
new XElement("StartDate", m_startTimePicker.Value.ToString()),
new XElement("Type", cmb_name.Text),
new XElement("RunDays", txtfrequency.Text.Trim()),
new XElement("RunDaysBeforeAfter", cmb_daysbefore_after.Value.ToString()),
new XElement("Message", txt_msgbody.Text)));
xDocument.Save(path);
}
else
{
XmlTextWriter xmlWriter = new XmlTextWriter(path, null);
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("Schedules");
xmlWriter.WriteStartElement("Schedule");
xmlWriter.WriteElementString("ScheduleName", Schedule);
xmlWriter.WriteElementString("StartDate", m_startTimePicker.Value.ToString());
xmlWriter.WriteElementString("Type", cmb_name.Text);
xmlWriter.WriteElementString("RunDays", txtfrequency.Text.Trim());
xmlWriter.WriteElementString("RunDaysBeforeAfter", cmb_daysbefore_after.Value.ToString());
xmlWriter.WriteElementString("Message", txt_msgbody.Text.Trim().ToString());
xmlWriter.WriteEndElement(); // Schedule
xmlWriter.WriteEndElement(); // Schedules
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
xmlWriter.Close();
}
}
FillSchedules(false);
MessageBox.Show("Create Successfully.", "ConvergedComm", MessageBoxButtons.OK, MessageBoxIcon.Information);
// Ashok Patel 8 July 2016--Comment because Cliend /Server machine Issue , Service is running on Server , so how to start/stop service here.
if (IsServerMachine)
{
// Re-Start Service
startService("HRDSQPlugin");
}
else
{
MessageBox.Show("Please re-start HRDSQPlugin service on server machine.", "ConvergedComm", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception pcex)
{
// clsErrorLog.CatchException(pcex.Message.ToString(), pcex.StackTrace.ToString(), pcex.HelpLink != null ? pcex.HelpLink.ToString() : "", pcex.InnerException != null ? pcex.InnerException.ToString() : "", pcex.Source.ToString(), pcex.TargetSite.ToString());
}
finally
{
CreateBtn.Enabled = true;
}
}
public void AlertItems_CreateAlertsForNewWebmail()
{
//Test Sections:
//Create 5 users, part of 2 providers.
//Test adding an email for each provider, then clear alerts table.
//Test adding 4 emails for each provider
//Test adding 3 additional emails for 1 provider
//Test marking 2 emails as read for 1 provider
//Test marking all emails as read for 1 provider
EmailMessageT.ClearEmailMessageTable(); //Clear out the emailmessage table
List <Userod> listTestUsers = new List <Userod>();
//Create or reuse 5 users, and set their provnum to 1 or 2. There'll be 3 provnum=1 and 2 provnum=2
//In queries always filter by usernum because there may be users left over from other/old tests.
for (int i = 0; i < 5; i++)
{
Userod user = UserodT.CreateUser();
user.ProvNum = i % 2 + 1;
listTestUsers.Add(user);
Userods.Update(user);
}
listTestUsers = listTestUsers.Distinct().ToList();
long examplePatnum = 2; //Patnum can be anything, needed for webmail.
//Create one email for each provider.
foreach (long provnum in listTestUsers.Select(x => x.ProvNum).Distinct())
{
EmailMessageT.CreateWebMail(provnum, examplePatnum);
}
AlertItems_CreateAlertsForWebmailMethodCall();
//Count the total # of alertitem entries, not what the description is.
string alertCount = DataCore.GetScalar("SELECT COUNT(*) FROM alertitem WHERE UserNum IN (" + string.Join(",", listTestUsers.Select(x => POut.Long(x.UserNum)))
+ ") AND Type=" + POut.Int((int)AlertType.WebMailRecieved));
Assert.AreEqual("5", alertCount);
//
//Clear out ALERT table and add some new emails
AlertItemT.ClearAlertItemTable();
foreach (long provnum in listTestUsers.Select(x => x.ProvNum).Distinct())
{
EmailMessageT.CreateWebMail(provnum, examplePatnum);
EmailMessageT.CreateWebMail(provnum, examplePatnum);
EmailMessageT.CreateWebMail(provnum, examplePatnum);
EmailMessageT.CreateWebMail(provnum, examplePatnum);
}
//This section tests adding more unread emails, and changing the description of the alertitem
Userod selectedUser = listTestUsers.First();
AlertItems_CreateAlertsForWebmailMethodCall();
alertCount = DataCore.GetScalar("SELECT Description FROM alertitem WHERE Type=" + POut.Int((int)AlertType.WebMailRecieved) + " AND UserNum=" + selectedUser.UserNum);
Assert.AreEqual("5", alertCount);
//
//Add 3 more unread emails.
EmailMessageT.CreateWebMail(selectedUser.ProvNum, examplePatnum);
EmailMessageT.CreateWebMail(selectedUser.ProvNum, examplePatnum);
EmailMessageT.CreateWebMail(selectedUser.ProvNum, examplePatnum);
AlertItems_CreateAlertsForWebmailMethodCall();
alertCount = DataCore.GetScalar("SELECT Description FROM alertitem WHERE Type=" + POut.Int((int)AlertType.WebMailRecieved) + " AND UserNum=" + selectedUser.UserNum);
Assert.AreEqual("8", alertCount);
//
//Mark 2 of the emails as read, to decrease the amount of unread emails
string command = "UPDATE emailmessage SET SentOrReceived=" + POut.Int((int)EmailSentOrReceived.WebMailRecdRead) +
" WHERE SentOrReceived=" + POut.Int((int)EmailSentOrReceived.WebMailReceived) + " AND ProvNumWebMail=" + POut.Long(selectedUser.ProvNum) + " LIMIT 2";
DataCore.NonQ(command);
AlertItems_CreateAlertsForWebmailMethodCall();
alertCount = DataCore.GetScalar("SELECT Description FROM alertitem WHERE Type=" + POut.Int((int)AlertType.WebMailRecieved) + " AND UserNum=" + selectedUser.UserNum);
Assert.AreEqual("6", alertCount);
//
//Now we mark all of this user's emails as read, as if that user has read all of their webmail.
command = "UPDATE emailmessage SET SentOrReceived=" + POut.Int((int)EmailSentOrReceived.WebMailRecdRead) +
" WHERE SentOrReceived=" + POut.Int((int)EmailSentOrReceived.WebMailReceived) + " AND ProvNumWebMail=" + POut.Long(selectedUser.ProvNum);
DataCore.NonQ(command);
AlertItems_CreateAlertsForWebmailMethodCall();
alertCount = DataCore.GetScalar("SELECT COUNT(*) FROM alertitem WHERE Type=" + POut.Int((int)AlertType.WebMailRecieved) + " AND UserNum=" + selectedUser.UserNum);
Assert.AreEqual("0", alertCount);
}
