///<summary>This query is run with full privileges. This is for commands generated by the main program, and the user will not have access for injection attacks. Result is usually number of rows changed, or can be insert id if requested.</summary>
public static long NonQ(string command,bool getInsertID,params OdSqlParameter[] parameters) {
DataConnection dcon=new DataConnection();
long rowsChanged=dcon.NonQ(command,getInsertID,parameters);
if(getInsertID){
return (long)dcon.InsertID;
}
else{
return rowsChanged;
}
}
///<summary>This query is run with full privileges. This is for commands generated by the main program, and the user will not have access for injection attacks. Result is usually number of rows changed, or can be insert id if requested.</summary>
public static int NonQ(string command, bool getInsertID)
{
DataConnection dcon = new DataConnection();
int rowsChanged = dcon.NonQ(command, getInsertID);
if (getInsertID)
{
return(dcon.InsertID);
}
else
{
return(rowsChanged);
}
}
///<summary>This query is run with full privileges. This is for commands generated by the main program, and the user will not have access for injection attacks. Result is usually number of rows changed, or can be insert id if requested.</summary>
public static long NonQ(string command, bool getInsertID, params OdSqlParameter[] parameters)
{
DataConnection dcon = new DataConnection();
long rowsChanged = dcon.NonQ(command, getInsertID, parameters);
if (getInsertID)
{
return((long)dcon.InsertID);
}
else
{
return(rowsChanged);
}
}
///<summary></summary>
public static int Update(EmailMessage message)
{
string command = "UPDATE emailmessage SET "
+ "PatNum = '" + POut.PInt(message.PatNum) + "' "
+ ",ToAddress = '" + POut.PString(message.ToAddress) + "' "
+ ",FromAddress = '" + POut.PString(message.FromAddress) + "' "
+ ",Subject = '" + POut.PString(message.Subject) + "' "
+ ",BodyText = '" + POut.PString(message.BodyText) + "' "
+ ",MsgDateTime = " + POut.PDateT(message.MsgDateTime) + " "
+ ",SentOrReceived = '" + POut.PInt((int)message.SentOrReceived) + "' "
+ "WHERE EmailMessageNum = " + POut.PInt(message.EmailMessageNum);
DataConnection dcon = new DataConnection();
dcon.NonQ(command);
//now, delete all attachments and recreate.
command = "DELETE FROM emailattach WHERE EmailMessageNum=" + POut.PInt(message.EmailMessageNum);
dcon.NonQ(command);
for (int i = 0; i < message.Attachments.Count; i++)
{
message.Attachments[i].EmailMessageNum = message.EmailMessageNum;
EmailAttachB.Insert(message.Attachments[i]);
}
return(0);
}
///<summary></summary>
public static int Update(Def def)
{
string command = "UPDATE definition SET "
+ "category = '" + POut.PInt((int)def.Category) + "'"
+ ",itemorder = '" + POut.PInt(def.ItemOrder) + "'"
+ ",itemname = '" + POut.PString(def.ItemName) + "'"
+ ",itemvalue = '" + POut.PString(def.ItemValue) + "'"
+ ",itemcolor = '" + POut.PInt(def.ItemColor.ToArgb()) + "'"
+ ",ishidden = '" + POut.PBool(def.IsHidden) + "'"
+ "WHERE defnum = '" + POut.PInt(def.DefNum) + "'";
DataConnection dcon = new DataConnection();
int rowsChanged = dcon.NonQ(command);
return(rowsChanged);
}
///<summary>Sends a non query command to the database and returns the number of rows affected.
///This query is run with full privileges. This is for commands generated by the main program, and the user will not have access for injection attacks.
///If getInsertID is true, then InsertID will be set to the value of the primary key of the newly inserted row.
///WILL NOT RETURN CORRECT PRIMARY KEY for MySQL if the query specifies the primary key.
///Pass in the PK column and table names so that Oracle can correctly lock the table and know which column to return for the Insert ID.</summary>
public static long NonQ(string command, bool getInsertID, string columnNamePK, string tableName, params OdSqlParameter[] parameters)
{
long retval = 0;
using (DataConnection dcon = new DataConnection()) {
retval = ExecuteQueryFunc(() =>
dcon.NonQ(command, getInsertID, columnNamePK, tableName, true, parameters.Select(x => x.GetMySqlParameter()).ToArray())
);
if (getInsertID)
{
retval = dcon.InsertID;
}
}
return(retval);
}
///<summary>Returns the new DefNum</summary>
public static int Insert(Def def)
{
string command = "INSERT INTO definition (category,itemorder,"
+ "itemname,itemvalue,itemcolor,ishidden) VALUES("
+ "'" + POut.PInt((int)def.Category) + "', "
+ "'" + POut.PInt(def.ItemOrder) + "', "
+ "'" + POut.PString(def.ItemName) + "', "
+ "'" + POut.PString(def.ItemValue) + "', "
+ "'" + POut.PInt(def.ItemColor.ToArgb()) + "', "
+ "'" + POut.PBool(def.IsHidden) + "')";
DataConnection dcon = new DataConnection();
dcon.NonQ(command, true);
int defNum = dcon.InsertID; //used in conversion
return(defNum);
}
public static void UnlockWorkstationsForDbs(string[] dbNames)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
throw new ApplicationException("MiscData.UnlockWorkstationsForDbs failed. Updates not allowed from ClientWeb.");
}
DataConnection dcon = null;
for (int i = 0; i < dbNames.Length; i++)
{
try {
dcon = new DataConnection(dbNames[i]);
string command = "UPDATE preference SET ValueString =''"
+ " WHERE PrefName='UpdateInProgressOnComputerName'";
dcon.NonQ(command);
}
catch { }
}
}
internal static void Insert(EmailAttach attach){
if(PrefB.RandomKeys) {
attach.EmailAttachNum=MiscDataB.GetKey("emailattach","EmailAttachNum");
}
string command= "INSERT INTO emailattach (";
if(PrefB.RandomKeys) {
command+="EmailAttachNum,";
}
command+="EmailMessageNum, DisplayedFileName, ActualFileName) VALUES(";
if(PrefB.RandomKeys) {
command+="'"+POut.PInt(attach.EmailAttachNum)+"', ";
}
command+=
"'"+POut.PInt (attach.EmailMessageNum)+"', "
+"'"+POut.PString(attach.DisplayedFileName)+"', "
+"'"+POut.PString(attach.ActualFileName)+"')";
//MessageBox.Show(cmd.CommandText);
DataConnection dcon=new DataConnection();
dcon.NonQ(command);
}
internal static void Insert(ProcNote procNote)
{
if (PrefB.RandomKeys)
{
procNote.ProcNoteNum = MiscDataB.GetKey("procnote", "ProcNoteNum");
}
string command = "INSERT INTO procnote (";
if (PrefB.RandomKeys)
{
command += "ProcNoteNum,";
}
command += "PatNum, ProcNum, EntryDateTime, UserNum, Note, SigIsTopaz, Signature) VALUES(";
if (PrefB.RandomKeys)
{
command += "'" + POut.PInt(procNote.ProcNoteNum) + "', ";
}
command +=
"'" + POut.PInt(procNote.PatNum) + "', "
+ "'" + POut.PInt(procNote.ProcNum) + "', ";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += POut.PDateT(MiscDataB.GetNowDateTime());
}
else //Assume MySQL
{
command += "NOW()";
}
command += ", " //EntryDateTime
+ "'" + POut.PInt(procNote.UserNum) + "', "
+ "'" + POut.PString(procNote.Note) + "', "
+ "'" + POut.PBool(procNote.SigIsTopaz) + "', "
+ "'" + POut.Base64(procNote.Signature) + "')";
//MessageBox.Show(cmd.CommandText);
DataConnection dcon = new DataConnection();
dcon.NonQ(command);
//Debug.WriteLine("Sig length: "+procNote.Signature.Length.ToString());
}
///<summary></summary>
public static int Update(Document doc)
{
string command = "UPDATE document SET "
+ "Description = '" + POut.PString(doc.Description) + "'"
+ ",DateCreated = " + POut.PDate(doc.DateCreated)
+ ",DocCategory = '" + POut.PInt(doc.DocCategory) + "'"
+ ",WithPat = '" + POut.PInt(doc.WithPat) + "'"
+ ",FileName = '" + POut.PString(doc.FileName) + "'"
+ ",ImgType = '" + POut.PInt((int)doc.ImgType) + "'"
+ ",IsFlipped = '" + POut.PBool(doc.IsFlipped) + "'"
+ ",DegreesRotated = '" + POut.PInt(doc.DegreesRotated) + "'"
+ ",ToothNumbers = '" + POut.PString(doc.ToothNumbers) + "'"
+ ",Note = '" + POut.PString(doc.Note) + "'"
+ ",SigIsTopaz = '" + POut.PBool(doc.SigIsTopaz) + "'"
+ ",Signature = '" + POut.PString(doc.Signature) + "'"
+ " WHERE DocNum = '" + POut.PInt(doc.DocNum) + "'";
//MessageBox.Show(cmd.CommandText);
DataConnection dcon = new DataConnection();
dcon.NonQ(command);
return(0);
}
private static void RemoveFromRingGroup(string ringGroup, string extension, List <string> listExtensions, string rawExtensions)
{
string newExtensions = "";
for (int i = 0; i < listExtensions.Count; i++)
{
if (listExtensions[i] == extension) //skip this extension
{
continue;
}
if (newExtensions != "")
{
newExtensions = newExtensions + "-";
}
newExtensions = newExtensions + listExtensions[i];
}
string command = "UPDATE ringgroups SET grplist='" + POut.String(newExtensions) + "' "
+ "WHERE grpnum='" + ringGroup + "' "
+ "AND grplist = '" + POut.String(rawExtensions) + "'";
DataConnection dcon = new DataConnection("192.168.0.197", "asterisk", "opendental", "secret", DatabaseType.MySql);
dcon.NonQ(command);
}
private static void RemoveFromRingGroup(string ringGroup,string extension,List<string> listExtensions,string rawExtensions) {
string newExtensions="";
for(int i=0;i<listExtensions.Count;i++) {
if(listExtensions[i]==extension) {//skip this extension
continue;
}
if(newExtensions!="") {
newExtensions=newExtensions+"-";
}
newExtensions=newExtensions+listExtensions[i];
}
string command="UPDATE ringgroups SET grplist='"+POut.String(newExtensions)+"' "
+"WHERE grpnum='"+ringGroup+"' "
+"AND grplist = '"+POut.String(rawExtensions)+"'";
DataConnection dcon=new DataConnection(ipAddressAsterisk,"asterisk","opendental","secret",DatabaseType.MySql);
dcon.NonQ(command);
}
public static DataTable GetTreeListTableForPatient(string patNum){
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),patNum);
}
DataConnection dcon=new DataConnection();
DataTable table=new DataTable("DocumentList");
DataRow row;
DataTable raw;
string command;
//Rows are first added to the resultSet list so they can be sorted at the end as a larger group, then
//they are placed in the datatable to be returned.
List<Object> resultSet=new List<Object>();
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("DocNum");
table.Columns.Add("MountNum");
table.Columns.Add("DocCategory");
table.Columns.Add("DateCreated");
table.Columns.Add("docFolder");//The folder order to which the Document category corresponds.
table.Columns.Add("description");
table.Columns.Add("ImgType");
//Move all documents which are invisible to the first document category.
command="SELECT DocNum FROM document WHERE PatNum='"+patNum+"' AND "
+"DocCategory<0";
raw=dcon.GetTable(command);
if(raw.Rows.Count>0){//Are there any invisible documents?
command="UPDATE document SET DocCategory='"+DefC.GetList(DefCat.ImageCats)[0].DefNum
+"' WHERE PatNum='"+patNum+"' AND (";
for(int i=0;i<raw.Rows.Count;i++){
command+="DocNum='"+PIn.Long(raw.Rows[i]["DocNum"].ToString())+"' ";
if(i<raw.Rows.Count-1){
command+="OR ";
}
}
command+=")";
dcon.NonQ(command);
}
//Load all documents into the result table.
command="SELECT DocNum,DocCategory,DateCreated,Description,ImgType,MountItemNum FROM document WHERE PatNum='"+patNum+"'";
raw=dcon.GetTable(command);
for(int i=0;i<raw.Rows.Count;i++){
//Make sure hidden documents are never added (there is a small possibility that one is added after all are made visible).
if(DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()))<0){
continue;
}
//Do not add individual documents which are part of a mount object.
if(PIn.Long(raw.Rows[i]["MountItemNum"].ToString())!=0) {
continue;
}
row=table.NewRow();
row["DocNum"]=PIn.Long(raw.Rows[i]["DocNum"].ToString());
row["MountNum"]=0;
row["DocCategory"]=PIn.Long(raw.Rows[i]["DocCategory"].ToString());
row["DateCreated"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString());
row["docFolder"]=DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()));
row["description"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d")+": "
+PIn.String(raw.Rows[i]["Description"].ToString());
row["ImgType"]=PIn.Long(raw.Rows[i]["ImgType"].ToString());
resultSet.Add(row);
}
//Move all mounts which are invisible to the first document category.
command="SELECT MountNum FROM mount WHERE PatNum='"+patNum+"' AND "
+"DocCategory<0";
raw=dcon.GetTable(command);
if(raw.Rows.Count>0) {//Are there any invisible mounts?
command="UPDATE mount SET DocCategory='"+DefC.GetList(DefCat.ImageCats)[0].DefNum
+"' WHERE PatNum='"+patNum+"' AND (";
for(int i=0;i<raw.Rows.Count;i++) {
command+="MountNum='"+PIn.Long(raw.Rows[i]["MountNum"].ToString())+"' ";
if(i<raw.Rows.Count-1) {
command+="OR ";
}
}
command+=")";
dcon.NonQ(command);
}
//Load all mounts into the result table.
command="SELECT MountNum,DocCategory,DateCreated,Description,ImgType FROM mount WHERE PatNum='"+patNum+"'";
raw=dcon.GetTable(command);
for(int i=0;i<raw.Rows.Count;i++){
//Make sure hidden mounts are never added (there is a small possibility that one is added after all are made visible).
if(DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()))<0) {
continue;
}
row=table.NewRow();
row["DocNum"]=0;
row["MountNum"]=PIn.Long(raw.Rows[i]["MountNum"].ToString());
row["DocCategory"]=PIn.Long(raw.Rows[i]["DocCategory"].ToString());
row["DateCreated"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString());
row["docFolder"]=DefC.GetOrder(DefCat.ImageCats,PIn.Long(raw.Rows[i]["DocCategory"].ToString()));
row["description"]=PIn.Date(raw.Rows[i]["DateCreated"].ToString()).ToString("d")+": "
+PIn.String(raw.Rows[i]["Description"].ToString());
row["ImgType"]=PIn.Long(raw.Rows[i]["ImgType"].ToString());
resultSet.Add(row);
}
//We must sort the results after they are returned from the database, because the database software (i.e. MySQL)
//cannot return sorted results from two or more result sets like we have here.
resultSet.Sort(delegate(Object o1,Object o2) {
DataRow r1=(DataRow)o1;
DataRow r2=(DataRow)o2;
int docFolder1=Convert.ToInt32(r1["docFolder"].ToString());
int docFolder2=Convert.ToInt32(r2["docFolder"].ToString());
if(docFolder1<docFolder2){
return -1;
}else if(docFolder1>docFolder2){
return 1;
}
return PIn.Date(r1["DateCreated"].ToString()).CompareTo(PIn.Date(r2["DateCreated"].ToString()));
});
//Finally, move the results from the list into a data table.
for(int i=0;i<resultSet.Count;i++){
table.Rows.Add((DataRow)resultSet[i]);
}
return table;
}
private static DataTable GetTreeListTableForPatient(string patNum)
{
DataConnection dcon = new DataConnection();
DataTable table = new DataTable("DocumentList");
DataRow row;
DataTable raw;
string command;
//Rows are first added to the resultSet list so they can be sorted at the end as a larger group, then
//they are placed in the datatable to be returned.
List <Object> resultSet = new List <Object>();
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("DocNum");
table.Columns.Add("MountNum");
table.Columns.Add("DocCategory");
table.Columns.Add("DateCreated");
table.Columns.Add("docFolder"); //The folder order to which the Document category corresponds.
table.Columns.Add("description");
table.Columns.Add("ImgType");
//Move all documents which are invisible to the first document category.
command = "SELECT DocNum FROM document WHERE PatNum='" + patNum + "' AND "
+ "DocCategory<0";
raw = dcon.GetTable(command);
if (raw.Rows.Count > 0) //Are there any invisible documents?
{
command = "UPDATE document SET DocCategory='" + DefB.Short[(int)DefCat.ImageCats][0].DefNum
+ "' WHERE PatNum='" + patNum + "' AND (";
for (int i = 0; i < raw.Rows.Count; i++)
{
command += "DocNum='" + PIn.PInt(raw.Rows[i]["DocNum"].ToString()) + "' ";
if (i < raw.Rows.Count - 1)
{
command += "OR ";
}
}
command += ")";
dcon.NonQ(command);
}
//Load all documents into the result table.
command = "SELECT DocNum,DocCategory,DateCreated,Description,ImgType,MountItemNum FROM document WHERE PatNum='" + patNum + "'";
raw = dcon.GetTable(command);
for (int i = 0; i < raw.Rows.Count; i++)
{
//Make sure hidden documents are never added (there is a small possibility that one is added after all are made visible).
if (DefB.GetOrder(DefCat.ImageCats, PIn.PInt(raw.Rows[i]["DocCategory"].ToString())) < 0)
{
continue;
}
//Do not add individual documents which are part of a mount object.
if (PIn.PInt(raw.Rows[i]["MountItemNum"].ToString()) != 0)
{
continue;
}
row = table.NewRow();
row["DocNum"] = PIn.PInt(raw.Rows[i]["DocNum"].ToString());
row["MountNum"] = 0;
row["DocCategory"] = PIn.PInt(raw.Rows[i]["DocCategory"].ToString());
row["DateCreated"] = PIn.PDate(raw.Rows[i]["DateCreated"].ToString());
row["docFolder"] = DefB.GetOrder(DefCat.ImageCats, PIn.PInt(raw.Rows[i]["DocCategory"].ToString()));
row["description"] = PIn.PDate(raw.Rows[i]["DateCreated"].ToString()).ToString("d") + ": "
+ PIn.PString(raw.Rows[i]["Description"].ToString());
row["ImgType"] = PIn.PInt(raw.Rows[i]["ImgType"].ToString());
resultSet.Add(row);
}
//Move all mounts which are invisible to the first document category.
command = "SELECT MountNum FROM mount WHERE PatNum='" + patNum + "' AND "
+ "DocCategory<0";
raw = dcon.GetTable(command);
if (raw.Rows.Count > 0) //Are there any invisible mounts?
{
command = "UPDATE mount SET DocCategory='" + DefB.Short[(int)DefCat.ImageCats][0].DefNum
+ "' WHERE PatNum='" + patNum + "' AND (";
for (int i = 0; i < raw.Rows.Count; i++)
{
command += "MountNum='" + PIn.PInt(raw.Rows[i]["MountNum"].ToString()) + "' ";
if (i < raw.Rows.Count - 1)
{
command += "OR ";
}
}
command += ")";
dcon.NonQ(command);
}
//Load all mounts into the result table.
command = "SELECT MountNum,DocCategory,DateCreated,Description,ImgType FROM mount WHERE PatNum='" + patNum + "'";
raw = dcon.GetTable(command);
for (int i = 0; i < raw.Rows.Count; i++)
{
//Make sure hidden mounts are never added (there is a small possibility that one is added after all are made visible).
if (DefB.GetOrder(DefCat.ImageCats, PIn.PInt(raw.Rows[i]["DocCategory"].ToString())) < 0)
{
continue;
}
row = table.NewRow();
row["DocNum"] = 0;
row["MountNum"] = PIn.PInt(raw.Rows[i]["MountNum"].ToString());
row["DocCategory"] = PIn.PInt(raw.Rows[i]["DocCategory"].ToString());
row["DateCreated"] = PIn.PDate(raw.Rows[i]["DateCreated"].ToString());
row["docFolder"] = DefB.GetOrder(DefCat.ImageCats, PIn.PInt(raw.Rows[i]["DocCategory"].ToString()));
row["description"] = PIn.PDate(raw.Rows[i]["DateCreated"].ToString()).ToString("d") + ": "
+ PIn.PString(raw.Rows[i]["Description"].ToString());
row["ImgType"] = PIn.PInt(raw.Rows[i]["ImgType"].ToString());
resultSet.Add(row);
}
//We must soft the results after they are returned from the database, because the database software (i.e. MySQL)
//cannot return sorted results from two or more result sets like we have here.
resultSet.Sort(delegate(Object o1, Object o2) {
DataRow r1 = (DataRow)o1;
DataRow r2 = (DataRow)o2;
int docFolder1 = Convert.ToInt32(r1["docFolder"].ToString());
int docFolder2 = Convert.ToInt32(r2["docFolder"].ToString());
if (docFolder1 < docFolder2)
{
return(-1);
}
else if (docFolder1 > docFolder2)
{
return(1);
}
return(PIn.PDate(r1["DateCreated"].ToString()).CompareTo(PIn.PDate(r2["DateCreated"].ToString())));
});
//Finally, move the results from the list into a data table.
for (int i = 0; i < resultSet.Count; i++)
{
table.Rows.Add((DataRow)resultSet[i]);
}
return(table);
}
///<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)}." );
}
}
}
public static void UnlockWorkstationsForDbs(string[] dbNames) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
throw new ApplicationException("MiscData.UnlockWorkstationsForDbs failed. Updates not allowed from ClientWeb.");
}
DataConnection dcon=null;
for(int i=0;i<dbNames.Length;i++) {
try {
dcon=new DataConnection(dbNames[i]);
string command="UPDATE preference SET ValueString =''"
+" WHERE PrefName='UpdateInProgressOnComputerName'";
dcon.NonQ(command);
}
catch { }
}
}
public static DataTable GetPlannedApt(long patNum) {
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetTable(MethodBase.GetCurrentMethod(),patNum);
}
DataConnection dcon=new DataConnection();
DataTable table=new DataTable("Planned");
DataRow row;
//columns that start with lowercase are altered for display rather than being raw data.
table.Columns.Add("AptNum");
table.Columns.Add("colorBackG");
table.Columns.Add("colorText");
table.Columns.Add("dateSched");
table.Columns.Add("ItemOrder");
table.Columns.Add("minutes");
table.Columns.Add("Note");
table.Columns.Add("ProcDescript");
table.Columns.Add("PlannedApptNum");
//but we won't actually fill this table with rows until the very end. It's more useful to use a List<> for now.
List<DataRow> rows=new List<DataRow>();
//The query below was causing a max join error for big offices. It's fixed now,
//but a better option for next time would be to put SET SQL_BIG_SELECTS=1; before the query.
string command="SELECT plannedappt.AptNum,ItemOrder,PlannedApptNum,appointment.AptDateTime,"
+"appointment.Pattern,appointment.AptStatus,"//COUNT(procedurelog.ProcNum) someAreComplete "//The count won't be accurate, but it will tell us if not zero.
+"(SELECT COUNT(*) FROM procedurelog WHERE procedurelog.PlannedAptNum=plannedappt.AptNum AND procedurelog.ProcStatus=2) someAreComplete "
+"FROM plannedappt "
+"LEFT JOIN appointment ON appointment.NextAptNum=plannedappt.AptNum "
//+"LEFT JOIN procedurelog ON procedurelog.PlannedAptNum=plannedappt.AptNum "//grab all attached completed procs
//+"AND procedurelog.ProcStatus=2 "
+"WHERE plannedappt.PatNum="+POut.Long(patNum)+" "
+"GROUP BY plannedappt.AptNum,ItemOrder,PlannedApptNum,appointment.AptDateTime,"
+"appointment.Pattern,appointment.AptStatus "
+"ORDER BY ItemOrder";
//plannedappt.AptNum does refer to the planned appt, but the other fields in the result are for the linked scheduled appt.
DataTable rawPlannedAppts=dcon.GetTable(command);
DataRow aptRow;
int itemOrder=1;
DateTime dateSched;
ApptStatus aptStatus;
for(int i=0;i<rawPlannedAppts.Rows.Count;i++) {
aptRow=null;
for(int a=0;a<rawApt.Rows.Count;a++) {
if(rawApt.Rows[a]["AptNum"].ToString()==rawPlannedAppts.Rows[i]["AptNum"].ToString()) {
aptRow=rawApt.Rows[a];
break;
}
}
if(aptRow==null) {
continue;//this will have to be fixed in dbmaint.
}
//repair any item orders here rather than in dbmaint. It's really fast.
if(itemOrder.ToString()!=rawPlannedAppts.Rows[i]["ItemOrder"].ToString()) {
command="UPDATE plannedappt SET ItemOrder="+POut.Long(itemOrder)
+" WHERE PlannedApptNum="+rawPlannedAppts.Rows[i]["PlannedApptNum"].ToString();
dcon.NonQ(command);
}
//end of repair
row=table.NewRow();
row["AptNum"]=aptRow["AptNum"].ToString();
dateSched=PIn.Date(rawPlannedAppts.Rows[i]["AptDateTime"].ToString());
//Colors----------------------------------------------------------------------------
aptStatus=(ApptStatus)PIn.Long(rawPlannedAppts.Rows[i]["AptStatus"].ToString());
//change color if completed, broken, or unscheduled no matter the date
if(aptStatus==ApptStatus.Broken || aptStatus==ApptStatus.UnschedList) {
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][15].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][14].ItemColor.ToArgb().ToString();
}
else if(aptStatus==ApptStatus.Complete) {
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString();
}
else if(aptStatus==ApptStatus.Scheduled && dateSched.Date!=DateTime.Today.Date) {
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString();
}
else if(dateSched.Date<DateTime.Today && dateSched!=DateTime.MinValue) {//Past
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][11].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][10].ItemColor.ToArgb().ToString();
}
else if(dateSched.Date == DateTime.Today.Date) { //Today
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][9].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][8].ItemColor.ToArgb().ToString();
}
else if(dateSched.Date > DateTime.Today) { //Future
row["colorBackG"]=DefC.Long[(int)DefCat.ProgNoteColors][13].ItemColor.ToArgb().ToString();
row["colorText"]=DefC.Long[(int)DefCat.ProgNoteColors][12].ItemColor.ToArgb().ToString();
}
else {
row["colorBackG"]=Color.White.ToArgb().ToString();
row["colorText"]=Color.Black.ToArgb().ToString();
}
//end of colors------------------------------------------------------------------------------
if(dateSched.Year<1880) {
row["dateSched"]="";
}
else {
row["dateSched"]=dateSched.ToShortDateString();
}
row["ItemOrder"]=itemOrder.ToString();
row["minutes"]=(aptRow["Pattern"].ToString().Length*5).ToString();
row["Note"]=aptRow["Note"].ToString();
row["PlannedApptNum"]=rawPlannedAppts.Rows[i]["PlannedApptNum"].ToString();
row["ProcDescript"]=aptRow["ProcDescript"].ToString();
if(aptStatus==ApptStatus.Complete) {
row["ProcDescript"]=Lans.g("ContrChart","(Completed) ")+ row["ProcDescript"];
}
else if(dateSched == DateTime.Today.Date) {
row["ProcDescript"]=Lans.g("ContrChart","(Today's) ")+ row["ProcDescript"];
}
else if(rawPlannedAppts.Rows[i]["someAreComplete"].ToString()!="0"){
row["ProcDescript"]=Lans.g("ContrChart","(Some procs complete) ")+ row["ProcDescript"];
}
rows.Add(row);
itemOrder++;
}
for(int i=0;i<rows.Count;i++) {
table.Rows.Add(rows[i]);
}
return table;
}
///<summary>Inserts a new document into db, creates a filename based on Cur.DocNum, and then updates the db with this filename. Also attaches the document to the current patient.</summary>
public static int Insert(Document doc, string patLF, int patNum)
{
if (PrefB.RandomKeys)
{
doc.DocNum = MiscDataB.GetKey("document", "DocNum");
}
string command = "INSERT INTO document (";
if (PrefB.RandomKeys)
{
command += "DocNum,";
}
command += "Description,DateCreated,DocCategory,WithPat,FileName,ImgType,"
+ "IsFlipped,DegreesRotated,ToothNumbers,Note,SigIsTopaz,Signature,CropX,CropY,CropW,CropH,"
+ "WindowingMin,WindowingMax) VALUES(";
if (PrefB.RandomKeys)
{
command += "'" + POut.PInt(doc.DocNum) + "', ";
}
command +=
"'" + POut.PString(doc.Description) + "', "
+ POut.PDate(doc.DateCreated) + ", "
+ "'" + POut.PInt(doc.DocCategory) + "', "
+ "'" + POut.PInt(doc.WithPat) + "', "
+ "'" + POut.PString(doc.FileName) + "', " //this may simply be the extension at this point, or it may be the full filename.
+ "'" + POut.PInt((int)doc.ImgType) + "', "
+ "'" + POut.PBool(doc.IsFlipped) + "', "
+ "'" + POut.PInt(doc.DegreesRotated) + "', "
+ "'" + POut.PString(doc.ToothNumbers) + "', "
+ "'" + POut.PString(doc.Note) + "', "
+ "'" + POut.PBool(doc.SigIsTopaz) + "', "
+ "'" + POut.PString(doc.Signature) + "',"
+ "'" + POut.PInt(doc.CropX) + "',"
+ "'" + POut.PInt(doc.CropY) + "',"
+ "'" + POut.PInt(doc.CropW) + "',"
+ "'" + POut.PInt(doc.CropH) + "',"
+ "'" + POut.PInt(doc.WindowingMin) + "',"
+ "'" + POut.PInt(doc.WindowingMax) + "')";
/*+"'"+POut.PDate (LastAltered)+"', "//will later be used in backups
+"'"+POut.PBool (IsDeleted)+"')";//ditto*/
//MessageBox.Show(cmd.CommandText);
DataConnection dcon = new DataConnection();
if (PrefB.RandomKeys)
{
dcon.NonQ(command);
}
else
{
dcon.NonQ(command, true);
doc.DocNum = dcon.InsertID;
}
//If the current filename is just an extension, then assign it a unique name.
if (doc.FileName == Path.GetExtension(doc.FileName))
{
string extension = doc.FileName;
doc.FileName = "";
string s = patLF; //pat.LName+pat.FName;
for (int i = 0; i < s.Length; i++)
{
if (Char.IsLetter(s, i))
{
doc.FileName += s.Substring(i, 1);
}
}
doc.FileName += doc.DocNum.ToString() + extension; //ensures unique name
//there is still a slight chance that someone manually added a file with this name, so quick fix:
command = "SELECT FileName FROM document WHERE WithPat=" + POut.PInt(doc.WithPat);
DataTable table = dcon.GetTable(command);
string[] usedNames = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
usedNames[i] = PIn.PString(table.Rows[i][0].ToString());
}
while (IsFileNameInList(doc.FileName, usedNames))
{
doc.FileName = "x" + doc.FileName;
}
/*Document[] docList=GetAllWithPat(doc.WithPat);
* while(IsFileNameInList(doc.FileName,docList)) {
* doc.FileName="x"+doc.FileName;
* }*/
Update(doc);
}
DocAttach docAttach = new DocAttach();
docAttach.DocNum = doc.DocNum;
docAttach.PatNum = patNum;
DocAttachB.Insert(docAttach);
return(doc.DocNum);
}
///<summary>Updates only the changed columns.</summary>
public static int Update(Procedure proc, Procedure oldProc)
{
bool comma = false;
string c = "UPDATE procedurelog SET ";
if (proc.PatNum != oldProc.PatNum)
{
c += "PatNum = '" + POut.PInt(proc.PatNum) + "'";
comma = true;
}
if (proc.AptNum != oldProc.AptNum)
{
if (comma)
{
c += ",";
}
c += "AptNum = '" + POut.PInt(proc.AptNum) + "'";
comma = true;
}
if (proc.OldCode != oldProc.OldCode)
{
if (comma)
{
c += ",";
}
c += "OldCode = '" + POut.PString(proc.OldCode) + "'";
comma = true;
}
if (proc.ProcDate != oldProc.ProcDate)
{
if (comma)
{
c += ",";
}
c += "ProcDate = " + POut.PDate(proc.ProcDate);
comma = true;
}
if (proc.ProcFee != oldProc.ProcFee)
{
if (comma)
{
c += ",";
}
c += "ProcFee = '" + POut.PDouble(proc.ProcFee) + "'";
comma = true;
}
if (proc.Surf != oldProc.Surf)
{
if (comma)
{
c += ",";
}
c += "Surf = '" + POut.PString(proc.Surf) + "'";
comma = true;
}
if (proc.ToothNum != oldProc.ToothNum)
{
if (comma)
{
c += ",";
}
c += "ToothNum = '" + POut.PString(proc.ToothNum) + "'";
comma = true;
}
if (proc.ToothRange != oldProc.ToothRange)
{
if (comma)
{
c += ",";
}
c += "ToothRange = '" + POut.PString(proc.ToothRange) + "'";
comma = true;
}
if (proc.Priority != oldProc.Priority)
{
if (comma)
{
c += ",";
}
c += "Priority = '" + POut.PInt(proc.Priority) + "'";
comma = true;
}
if (proc.ProcStatus != oldProc.ProcStatus)
{
if (comma)
{
c += ",";
}
c += "ProcStatus = '" + POut.PInt((int)proc.ProcStatus) + "'";
comma = true;
}
if (proc.ProvNum != oldProc.ProvNum)
{
if (comma)
{
c += ",";
}
c += "ProvNum = '" + POut.PInt(proc.ProvNum) + "'";
comma = true;
}
if (proc.Dx != oldProc.Dx)
{
if (comma)
{
c += ",";
}
c += "Dx = '" + POut.PInt(proc.Dx) + "'";
comma = true;
}
if (proc.PlannedAptNum != oldProc.PlannedAptNum)
{
if (comma)
{
c += ",";
}
c += "PlannedAptNum = '" + POut.PInt(proc.PlannedAptNum) + "'";
comma = true;
}
if (proc.PlaceService != oldProc.PlaceService)
{
if (comma)
{
c += ",";
}
c += "PlaceService = '" + POut.PInt((int)proc.PlaceService) + "'";
comma = true;
}
if (proc.Prosthesis != oldProc.Prosthesis)
{
if (comma)
{
c += ",";
}
c += "Prosthesis = '" + POut.PString(proc.Prosthesis) + "'";
comma = true;
}
if (proc.DateOriginalProsth != oldProc.DateOriginalProsth)
{
if (comma)
{
c += ",";
}
c += "DateOriginalProsth = " + POut.PDate(proc.DateOriginalProsth);
comma = true;
}
if (proc.ClaimNote != oldProc.ClaimNote)
{
if (comma)
{
c += ",";
}
c += "ClaimNote = '" + POut.PString(proc.ClaimNote) + "'";
comma = true;
}
if (proc.DateEntryC != oldProc.DateEntryC)
{
if (comma)
{
c += ",";
}
c += "DateEntryC = ";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
c += POut.PDateT(MiscDataB.GetNowDateTime());
}
else //Assume MySQL
{
c += "NOW()";
}
comma = true;
}
if (proc.ClinicNum != oldProc.ClinicNum)
{
if (comma)
{
c += ",";
}
c += "ClinicNum = '" + POut.PInt(proc.ClinicNum) + "'";
comma = true;
}
if (proc.MedicalCode != oldProc.MedicalCode)
{
if (comma)
{
c += ",";
}
c += "MedicalCode = '" + POut.PString(proc.MedicalCode) + "'";
comma = true;
}
if (proc.DiagnosticCode != oldProc.DiagnosticCode)
{
if (comma)
{
c += ",";
}
c += "DiagnosticCode = '" + POut.PString(proc.DiagnosticCode) + "'";
comma = true;
}
if (proc.IsPrincDiag != oldProc.IsPrincDiag)
{
if (comma)
{
c += ",";
}
c += "IsPrincDiag = '" + POut.PBool(proc.IsPrincDiag) + "'";
comma = true;
}
if (proc.ProcNumLab != oldProc.ProcNumLab)
{
if (comma)
{
c += ",";
}
c += "ProcNumLab = '" + POut.PInt(proc.ProcNumLab) + "'";
comma = true;
}
if (proc.BillingTypeOne != oldProc.BillingTypeOne)
{
if (comma)
{
c += ",";
}
c += "BillingTypeOne = '" + POut.PInt(proc.BillingTypeOne) + "'";
comma = true;
}
if (proc.BillingTypeTwo != oldProc.BillingTypeTwo)
{
if (comma)
{
c += ",";
}
c += "BillingTypeTwo = '" + POut.PInt(proc.BillingTypeTwo) + "'";
comma = true;
}
if (proc.CodeNum != oldProc.CodeNum)
{
if (comma)
{
c += ",";
}
c += "CodeNum = '" + POut.PInt(proc.CodeNum) + "'";
comma = true;
}
if (proc.CodeMod1 != oldProc.CodeMod1)
{
if (comma)
{
c += ",";
}
c += "CodeMod1 = '" + POut.PString(proc.CodeMod1) + "'";
comma = true;
}
if (proc.CodeMod2 != oldProc.CodeMod2)
{
if (comma)
{
c += ",";
}
c += "CodeMod2 = '" + POut.PString(proc.CodeMod2) + "'";
comma = true;
}
if (proc.CodeMod3 != oldProc.CodeMod3)
{
if (comma)
{
c += ",";
}
c += "CodeMod3 = '" + POut.PString(proc.CodeMod3) + "'";
comma = true;
}
if (proc.CodeMod4 != oldProc.CodeMod4)
{
if (comma)
{
c += ",";
}
c += "CodeMod4 = '" + POut.PString(proc.CodeMod4) + "'";
comma = true;
}
if (proc.RevCode != oldProc.RevCode)
{
if (comma)
{
c += ",";
}
c += "RevCode = '" + POut.PString(proc.RevCode) + "'";
comma = true;
}
if (proc.UnitCode != oldProc.UnitCode)
{
if (comma)
{
c += ",";
}
c += "UnitCode = '" + POut.PString(proc.UnitCode) + "'";
comma = true;
}
if (proc.UnitQty != oldProc.UnitQty)
{
if (comma)
{
c += ",";
}
c += "UnitQty = '" + POut.PInt(proc.UnitQty) + "'";
comma = true;
}
if (proc.BaseUnits != oldProc.BaseUnits)
{
if (comma)
{
c += ",";
}
c += "BaseUnits = '" + POut.PInt(proc.BaseUnits) + "'";
comma = true;
}
if (proc.StartTime != oldProc.StartTime)
{
if (comma)
{
c += ",";
}
c += "StartTime = '" + POut.PInt(proc.StartTime) + "'";
comma = true;
}
if (proc.StopTime != oldProc.StopTime)
{
if (comma)
{
c += ",";
}
c += "StopTime = '" + POut.PInt(proc.StopTime) + "'";
comma = true;
}
int rowsChanged = 0;
if (comma)
{
c += " WHERE ProcNum = '" + POut.PInt(proc.ProcNum) + "'";
DataConnection dcon = new DataConnection();
rowsChanged = dcon.NonQ(c);
}
else
{
//rowsChanged=0;//this means no change is actually required.
}
if (proc.Note != oldProc.Note ||
proc.UserNum != oldProc.UserNum ||
proc.SigIsTopaz != oldProc.SigIsTopaz ||
proc.Signature != oldProc.Signature)
{
ProcNote note = new ProcNote();
note.PatNum = proc.PatNum;
note.ProcNum = proc.ProcNum;
note.UserNum = proc.UserNum;
note.Note = proc.Note;
note.SigIsTopaz = proc.SigIsTopaz;
note.Signature = proc.Signature;
ProcNoteB.Insert(note);
}
return(rowsChanged);
}
public static int Insert(Procedure proc)
{
if (PrefB.RandomKeys)
{
proc.ProcNum = MiscDataB.GetKey("procedurelog", "ProcNum");
}
string command = "INSERT INTO procedurelog (";
if (PrefB.RandomKeys)
{
command += "ProcNum,";
}
command += "PatNum, AptNum, OldCode, ProcDate,ProcFee,Surf,"
+ "ToothNum,ToothRange,Priority,ProcStatus,ProvNum,"
+ "Dx,PlannedAptNum,PlaceService,Prosthesis,DateOriginalProsth,ClaimNote,"
+ "DateEntryC,ClinicNum,MedicalCode,DiagnosticCode,IsPrincDiag,ProcNumLab,"
+ "BillingTypeOne,BillingTypeTwo,CodeNum,CodeMod1,CodeMod2,CodeMod3,CodeMod4,RevCode,UnitCode,UnitQty,BaseUnits,StartTime,StopTime) VALUES(";
if (PrefB.RandomKeys)
{
command += "'" + POut.PInt(proc.ProcNum) + "', ";
}
command +=
"'" + POut.PInt(proc.PatNum) + "', "
+ "'" + POut.PInt(proc.AptNum) + "', "
+ "'" + POut.PString(proc.OldCode) + "', "
+ POut.PDate(proc.ProcDate) + ", "
+ "'" + POut.PDouble(proc.ProcFee) + "', "
+ "'" + POut.PString(proc.Surf) + "', "
+ "'" + POut.PString(proc.ToothNum) + "', "
+ "'" + POut.PString(proc.ToothRange) + "', "
+ "'" + POut.PInt(proc.Priority) + "', "
+ "'" + POut.PInt((int)proc.ProcStatus) + "', "
+ "'" + POut.PInt(proc.ProvNum) + "', "
+ "'" + POut.PInt(proc.Dx) + "', "
+ "'" + POut.PInt(proc.PlannedAptNum) + "', "
+ "'" + POut.PInt((int)proc.PlaceService) + "', "
+ "'" + POut.PString(proc.Prosthesis) + "', "
+ POut.PDate(proc.DateOriginalProsth) + ", "
+ "'" + POut.PString(proc.ClaimNote) + "', ";
if (DataConnection.DBtype == DatabaseType.Oracle)
{
command += POut.PDateT(MiscDataB.GetNowDateTime());
}
else //Assume MySQL
{
command += "NOW()";
}
command += ", " //DateEntryC
+ "'" + POut.PInt(proc.ClinicNum) + "', "
+ "'" + POut.PString(proc.MedicalCode) + "', "
+ "'" + POut.PString(proc.DiagnosticCode) + "', "
+ "'" + POut.PBool(proc.IsPrincDiag) + "', "
+ "'" + POut.PInt(proc.ProcNumLab) + "', "
+ "'" + POut.PInt(proc.BillingTypeOne) + "', "
+ "'" + POut.PInt(proc.BillingTypeTwo) + "', "
+ "'" + POut.PInt(proc.CodeNum) + "', "
+ "'" + POut.PString(proc.CodeMod1) + "', "
+ "'" + POut.PString(proc.CodeMod2) + "', "
+ "'" + POut.PString(proc.CodeMod3) + "', "
+ "'" + POut.PString(proc.CodeMod4) + "', "
+ "'" + POut.PString(proc.RevCode) + "', "
+ "'" + POut.PString(proc.UnitCode) + "', "
+ "'" + POut.PInt(proc.UnitQty) + "', "
+ "'" + POut.PInt(proc.BaseUnits) + "', "
+ "'" + POut.PInt(proc.StartTime) + "', "
+ "'" + POut.PInt(proc.StopTime) + "')";
//MessageBox.Show(cmd.CommandText);
DataConnection dcon = new DataConnection();
if (PrefB.RandomKeys)
{
dcon.NonQ(command);
}
else
{
dcon.NonQ(command, true);
proc.ProcNum = dcon.InsertID;
}
if (proc.Note != "")
{
ProcNote note = new ProcNote();
note.PatNum = proc.PatNum;
note.ProcNum = proc.ProcNum;
note.UserNum = proc.UserNum;
note.Note = proc.Note;
ProcNoteB.Insert(note);
}
return(proc.ProcNum);
}
private static void AddToRingGroup(string ringGroup,string extension,string rawExtensions) {
string newExtensions=rawExtensions+"-"+extension;
string command="UPDATE ringgroups SET grplist='"+POut.String(newExtensions)+"' "
+"WHERE grpnum='"+ringGroup+"' "
+"AND grplist = '"+POut.String(rawExtensions)+"'";//this ensures it hasn't changed since we checked it. If it has, then this silently fails.
//A transaction would be better, but no time.
DataConnection dcon=new DataConnection(ipAddressAsterisk,"asterisk","opendental","secret",DatabaseType.MySql);
dcon.NonQ(command);
}
///<summary>This does not actually delete the procedure, but just changes the status to deleted. Throws exception if not allowed to delete.</summary>
public static int Delete(int procNum)
{
//Test to see if any payment at all has been received for this proc
string command = "SELECT COUNT(*) FROM claimproc WHERE ProcNum=" + POut.PInt(procNum)
+ " AND InsPayAmt > 0 AND Status != " + POut.PInt((int)ClaimProcStatus.Preauth);
DataConnection dcon = new DataConnection();
if (dcon.GetCount(command) != "0")
{
throw new Exception(Lan.g("Procedures", "Not allowed to delete a procedure that is attached to a payment."));
}
//delete adjustments
command = "DELETE FROM adjustment WHERE ProcNum='" + POut.PInt(procNum) + "'";
dcon.NonQ(command);
//delete claimprocs
command = "DELETE from claimproc WHERE ProcNum = '" + POut.PInt(procNum) + "'";
dcon.NonQ(command);
//resynch appointment description-------------------------------------------------------------------------------------
command = "SELECT AptNum,PlannedAptNum FROM procedurelog WHERE ProcNum = " + POut.PInt(procNum);
DataTable table = dcon.GetTable(command);
string aptnum = table.Rows[0][0].ToString();
string plannedaptnum = table.Rows[0][1].ToString();
string procdescript;
if (aptnum != "0")
{
command = @"SELECT AbbrDesc FROM procedurecode,procedurelog
WHERE procedurecode.CodeNum=procedurelog.CodeNum
AND ProcNum != " + POut.PInt(procNum)
+ " AND procedurelog.AptNum=" + aptnum;
table = dcon.GetTable(command);
procdescript = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
procdescript += ", ";
}
procdescript += table.Rows[i]["AbbrDesc"].ToString();
}
command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' "
+ "WHERE AptNum=" + aptnum;
dcon.NonQ(command);
}
if (plannedaptnum != "0")
{
command = @"SELECT AbbrDesc FROM procedurecode,procedurelog
WHERE procedurecode.CodeNum=procedurelog.CodeNum
AND ProcNum != " + POut.PInt(procNum)
+ " AND procedurelog.PlannedAptNum=" + plannedaptnum;
table = dcon.GetTable(command);
procdescript = "";
for (int i = 0; i < table.Rows.Count; i++)
{
if (i > 0)
{
procdescript += ", ";
}
procdescript += table.Rows[i]["AbbrDesc"].ToString();
}
command = "UPDATE appointment SET ProcDescript='" + POut.PString(procdescript) + "' "
+ "WHERE NextAptNum=" + plannedaptnum;
dcon.NonQ(command);
}
//set the procedure deleted-----------------------------------------------------------------------------------------
command = "UPDATE procedurelog SET ProcStatus = " + POut.PInt((int)ProcStat.D) + ", "
+ "AptNum=0, "
+ "PlannedAptNum=0 "
+ "WHERE ProcNum = '" + POut.PInt(procNum) + "'";
int rowsChanged = dcon.NonQ(command);
//Recalls.Synch(ProcCur.PatNum);//later
return(rowsChanged);
}
///<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.</summary>
public static long MakeABackup() {
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
return Meth.GetLong(MethodBase.GetCurrentMethod());
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon=new DataConnection();
string command="SELECT database()";
DataTable table=dcon.GetTable(command);
string oldDb=PIn.String(table.Rows[0][0].ToString());
string newDb=oldDb+"backup_"+DateTime.Today.ToString("MM_dd_yyyy");
command="SHOW DATABASES";
table=dcon.GetTable(command);
string[] databases=new string[table.Rows.Count];
for(int i=0;i<table.Rows.Count;i++) {
databases[i]=table.Rows[i][0].ToString();
}
if(Contains(databases,newDb)) {//if the new database name already exists
//find a unique one
int uniqueID=1;
string originalNewDb=newDb;
do {
newDb=originalNewDb+"_"+uniqueID.ToString();
uniqueID++;
}
while(Contains(databases,newDb));
}
command="CREATE DATABASE "+newDb+" CHARACTER SET utf8";
dcon.NonQ(command);
command="SHOW FULL TABLES WHERE Table_type='BASE TABLE'";//Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table=dcon.GetTable(command);
string[] tableName=new string[table.Rows.Count];
for(int i=0;i<table.Rows.Count;i++) {
tableName[i]=table.Rows[i][0].ToString();
}
//switch to using the new database
DataConnection newDcon=new DataConnection(newDb);
for(int i=0;i<tableName.Length;i++) {
command="SHOW CREATE TABLE "+oldDb+"."+tableName[i];//also works with views.
table=newDcon.GetTable(command);
command=PIn.ByteArray(table.Rows[0][1]);
newDcon.NonQ(command);//this has to be run using connection with new database
command="INSERT INTO "+newDb+"."+tableName[i]
+" SELECT * FROM "+oldDb+"."+tableName[i];
newDcon.NonQ(command);
}
return 0;
}
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.
///Does not work for Oracle, due to some MySQL specific commands inside.</summary>
public static long MakeABackup()
{
//This function should always make the backup on the server itself, and since no directories are
//referred to (all handled with MySQL), this function will always be referred to the server from
//client machines.
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetLong(MethodBase.GetCurrentMethod()));
}
//UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users.
//GetStringNoCache() will return blank if pref does not exist.
if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra")
{
return(0);
}
//only used in two places: upgrading version, and upgrading mysql version.
//Both places check first to make sure user is using mysql.
//we have to be careful to throw an exception if the backup is failing.
DataConnection dcon = new DataConnection();
string command = "SELECT database()";
DataTable table = dcon.GetTable(command);
string oldDb = PIn.String(table.Rows[0][0].ToString());
string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy");
command = "SHOW DATABASES";
table = dcon.GetTable(command);
string[] databases = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
databases[i] = table.Rows[i][0].ToString();
}
if (Contains(databases, newDb)) //if the new database name already exists
//find a unique one
{
int uniqueID = 1;
string originalNewDb = newDb;
do
{
newDb = originalNewDb + "_" + uniqueID.ToString();
uniqueID++;
}while(Contains(databases, newDb));
}
command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8";
dcon.NonQ(command);
command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL.
table = dcon.GetTable(command);
string[] tableName = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
tableName[i] = table.Rows[i][0].ToString();
}
//switch to using the new database
DataConnection newDcon = new DataConnection(newDb);
for (int i = 0; i < tableName.Length; i++)
{
//Alert anyone that cares that we are backing up this table.
ODEvent.Fire(new ODEventArgs("BackupProgress", Lans.g("MiscData", "Backing up table") + ": " + tableName[i]));
command = "SHOW CREATE TABLE `" + oldDb + "`.`" + tableName[i] + "`"; //also works with views. Added backticks around table name for unusual characters.
table = newDcon.GetTable(command);
command = PIn.ByteArray(table.Rows[0][1]);
newDcon.NonQ(command); //this has to be run using connection with new database
command = "INSERT INTO `" + newDb + "`.`" + tableName[i] + "` "
+ "SELECT * FROM `" + oldDb + "`.`" + tableName[i] + "`"; //Added backticks around table name for unusual characters.
newDcon.NonQ(command);
}
return(0);
}
