/// <summary>
/// Builds a where clause for a specified column/field of a specified type with data as
/// a condition. Returns "" if data is empty.
/// </summary>
/// <param name="colName"></param>
/// <param name="data"></param>
/// <param name="dt"></param>
/// <returns></returns>
private string BuildWhereSingle(string colName, string data, DataTypes dt, DataObject.SearchTypes st)
{
if (data == "")
{
return("");
}
else
{
string safeData = data.Replace("'", "''");
string toReturn = " AND ";
if (dt == DataTypes.Text)
{
if (st == DataObject.SearchTypes.Contains)
{
toReturn += colName + " LIKE '%" + safeData + "%'";
}
else if (st == DataObject.SearchTypes.BeginsWith)
{
toReturn += colName + " LIKE '" + safeData + "%'";
}
else
{
toReturn += colName + " = '" + safeData + "'";
}
}
else if (dt == DataTypes.Numeric)
{
toReturn += colName + " = " + safeData;
}
else if (dt == DataTypes.Date)
{
// Have to do special processing for the date
DateTime compareDate = System.Convert.ToDateTime(safeData);
string dateWhere;
if (st == DataObject.SearchTypes.Before)
{
dateWhere = "'" + CommonFunctions.ToMySQLDateTime(compareDate) + "' > {colname}";
}
else if (st == DataObject.SearchTypes.After)
{
dateWhere = "'" + CommonFunctions.ToMySQLDateTime(compareDate) + "' < {colname}";
}
else
{
dateWhere = "YEAR({colname}) = '" + compareDate.Year + "' AND MONTH({colname}) = '" +
compareDate.Month + "' AND DAY({colname}) = '" + compareDate.Day + "'";
}
dateWhere = dateWhere.Replace("{colname}", colName);
toReturn += dateWhere;
}
return(toReturn);
}
}
internal static void SetLastImportDate(DateTime lastDate)
{
try
{
user u = new user();
u.ExecuteNonQuery("UPDATE system_options SET last_import_date = '" +
CommonFunctions.ToMySQLDateTime(lastDate) + "'");
}
catch (Exception err)
{
LoggingHelper.Log("Error in system_options.SetLastImportDate", LogSeverity.Error, err, true);
}
}
internal static claim_batch FindApexBatchWithDate(DateTime fileDate)
{
claim_batch toReturn = new claim_batch();
string sql = "SELECT * FROM claim_batch WHERE TimeDiff(batch_date, '" + CommonFunctions.ToMySQLDateTime(fileDate) +
"') = 0";
DataTable dt = toReturn.Search(sql);
if (dt.Rows.Count > 0)
{
toReturn.Load(dt.Rows[0]);
if (dt.Rows.Count > 1)
{
System.Diagnostics.Debug.WriteLine("Too many batches returned from the FindApexBatchWithDateFunction");
}
}
else
{
toReturn = null;
}
return(toReturn);
}
private void sendEclaimsDataToolStripMenuItem_Click(object sender, EventArgs e)
{
string XMLPATH = Application.StartupPath + "\\syncdata.xml";
// start by getting the date everything was sent out from the xml file
DateTime lastWrite = new DateTime(1999, 12, 31);
bool okToContinue = true;
if (File.Exists(XMLPATH))
{
XmlDocument toOpen = new XmlDocument();
toOpen.Load(XMLPATH);
try
{
XmlElement ele = toOpen.DocumentElement;
lastWrite = System.Convert.ToDateTime(ele.InnerText);
}
catch
{
// assume the data is corrupt and there was no last write date.
okToContinue = MessageBox.Show(this, "The file that specifies which batches have been sent to eclaims has been corrupted. Would you like to continue and " +
"send all batches to eclaims?", "Sync File Corrupt", MessageBoxButtons.YesNo, MessageBoxIcon.Error) == DialogResult.Yes;
}
}
int count = 0;
if (okToContinue)
{
// Get the claims to process
List <claim_batch> cbList = new List <claim_batch>();
claim_batch cb = new claim_batch();
string searchSQL = "SELECT * FROM claim_batch WHERE batch_date > CAST('" + CommonFunctions.ToMySQLDateTime(lastWrite)
+ "' AS DATETIME) AND SOURCE = 0";
DataTable dt = cb.Search(searchSQL);
count = dt.Rows.Count;
foreach (DataRow aBatch in dt.Rows)
{
cb = new claim_batch();
cb.Load(aBatch);
cbList.Add(cb);
}
// Add the claims as claims in Dentrix
// NHDG_CLAIM_BATCHES - CLAIMBATCHID, BATCH_DATE, HANDLING ("Paper", "Electronic (ApexEDI)"
// NHDG_CLAIM_TRANSACTIONS - CLAIM_ID, CLAIM_DB, CLAIMBATCHID, RESUBMIT_FLAG (char?), BATCH_RESUBMITTED
data_mapping_schema dms = new data_mapping_schema(3);
SqlConnection dbConnection = new SqlConnection(dms.GetConnectionString(false));
try
{
dbConnection.Open();
foreach (claim_batch aBatch in cbList)
{
SqlCommand sc = new SqlCommand("INSERT INTO NHDG_CLAIM_BATCHES (batch_date, handling) VALUES ('" +
CommonFunctions.ToMySQLDateTime(aBatch.batch_date) + "', '" + ConvertHandlingToDentrix(aBatch.handlingAsString) +
"')", dbConnection);
sc.ExecuteNonQuery();
sc.CommandText = "SELECT IDENT_CURRENT('NHDG_CLAIM_BATCHES')";
SqlDataReader getID = sc.ExecuteReader();
getID.Read();
int lastID = System.Convert.ToInt32(getID[0]);
getID.Close();
// Insert all the claims in the batch into nhdg_claim_transactions
foreach (claim aClaim in aBatch.GetMatchingClaims())
{
sc.CommandText = "INSERT INTO NHDG_CLAIM_TRANSACTIONS (CLAIM_ID, CLAIM_DB, CLAIMBATCHID) " +
" VALUES (" + aClaim.claimidnum + "," + aClaim.claimdb + "," + lastID + ")";
sc.ExecuteNonQuery();
}
}
}
catch
{
okToContinue = false;
MessageBox.Show("There was an error getting the data into the Dentrix database. The process cannot continue.");
}
}
if (okToContinue)
{
XmlDocument toSave = new XmlDocument();
XmlNode toChange;
if (File.Exists(XMLPATH))
{
toSave.Load(XMLPATH);
toChange = toSave.DocumentElement;
}
else
{
toChange = toSave.AppendChild(toSave.CreateNode(XmlNodeType.Element, "SyncData", ""));
toChange = toSave.DocumentElement.AppendChild(toSave.CreateTextNode("LastUpdate"));
}
toChange.InnerText = DateTime.Now.ToString();
toSave.Save(XMLPATH);
MessageBox.Show(count + " batches synced successfully.");
}
else
{
MessageBox.Show("Please contact a system administrator to fix the problems you encountered while syncing.");
}
}