public override IDataReader Get(IDbConnection cnn, string platform, DateRange dateRange, string siteID = "")
{
string siteFilter = "";
if (!String.IsNullOrEmpty(siteID))
{
siteFilter = String.Format(" AND EXT_CLIENT_KEY in ('{0}') \r\n", siteID); // TODO: If list then make sure single-quotes surround each number.
}
string sql = String.Format("SELECT * \r\n"
+ "FROM {0}.WORK_UNIT \r\n"
+ "WHERE EXT_SYS = :EXT_SYS \r\n"
+ " AND TAT_ENDS_ON >= :LOWER_BOUND \r\n"
+ " AND TAT_ENDS_ON < :UPPER_BOUND \r\n"
+ "{1}"
+ "ORDER BY cast(EXT_WORK_UNIT_KEY as number)", SchemaName, siteFilter);
if (cnn == null)
{
cnn = new OracleConnection(ConnectionString.Value);
}
OpenConnection(cnn);
using (OracleCommand cmd = new OracleCommand(sql, (OracleConnection)cnn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60 * 60;
OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", platform, OracleType.VarChar, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":LOWER_BOUND", dateRange.BeginsOn, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":UPPER_BOUND", dateRange.EndsBefore, OracleType.DateTime, ParameterDirection.Input);
return(cmd.ExecuteReader());
}
}
private List <InvoiceDetailFileTypeItem> GetFileTypes(string id)
{
// See if an InvoiceGrpId was passed
string whereSql = "";
if (!string.IsNullOrEmpty(id))
{
whereSql = "where file_type = :fileType;";
}
using (OracleConnection cnn = new OracleConnection(base.ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("select file_type, descr, is_available, extension, sql \r\n"
+ "from {0}.file_type \r\n"
+ "{1} \r\n"
+ "order by file_type;"
, base.SchemaName
, whereSql);
if (!String.IsNullOrEmpty(id))
{
OracleHelper.AddCommandParameter(cmd, ":fileType", id, OracleType.VarChar);
}
cmd.Connection = cnn;
cnn.Open();
return(ConvertDataReaderToList(cmd.ExecuteReader()));
}
}
public override bool Remove(string extSys, string extWorkUnitKey, DateRange range)
{
using (IDbConnection cnn = OpenConnection())
using (IDbCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"delete {0}.work_unit \r\n"
+ "where tat_ends_on >= :begins_on \r\n"
+ " and tat_ends_on < :ends_before \r\n"
+ " and ext_sys = :ext_sys \r\n"
+ " and ext_work_unit_key = :ext_work_unit_key \r\n"
+ " and invoice_id is null"
, SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
OracleHelper.AddCommandParameter(cmd, ":begins_on", range.BeginsOn, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ends_before", range.EndsBefore, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_sys", extSys, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_work_unit_key", extWorkUnitKey, OracleType.VarChar, 60, ParameterDirection.Input);
int rowsEffected = cmd.ExecuteNonQuery();
if (rowsEffected == 0)
{
return(false);
}
cnn.Close();
}
return(true);
}
public override void Update(ExtWorkType item)
{
using (IDbConnection cnn = new OracleConnection(ConnectionString.Value))
using (IDbCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"update {0}.ext_work_type set \r\n"
+ " INVOICE_GRP_ID = :INVOICE_GRP_ID \r\n"
+ " ,RATE_NBR = :RATE_NBR \r\n"
+ " ,descr = :descr \r\n"
+ " ,tat_threshold = :tat_threshold \r\n"
+ " ,stat_tat_threshold = :stat_tat_threshold \r\n"
+ " ,platform_wt_id = :platform_wt_id \r\n"
+ "where ext_sys = :ext_sys \r\n"
+ " and ext_client_key = :ext_client_key \r\n"
+ " and ext_work_type = :ext_work_type"
, SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
OracleHelper.AddCommandParameter(cmd, ":INVOICE_GRP_ID", CheckNull(item.INVOICE_GRP_ID), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":RATE_NBR", CheckNull(item.RATE_NBR), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":descr", item.DESCR, OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":tat_threshold", CheckNull(item.TAT_THRESHOLD), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":stat_tat_threshold", CheckNull(item.STAT_TAT_THRESHOLD), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":platform_wt_id", CheckNull(item.PLATFORM_WT_ID), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_sys", item.EXT_SYS, OracleType.Char, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_client_key", item.EXT_CLIENT_KEY, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_work_type", item.EXT_WORK_TYPE, OracleType.VarChar, 20, ParameterDirection.Input);
cnn.Open();
cmd.ExecuteNonQuery();
}
}
public override void Delete(string id)
{
using (OracleConnection cnn = new OracleConnection(base.ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("delete from {0}.file_type \r\n"
+ "where file_type = :fileType; \r\n"
+ "commit;"
, base.SchemaName);
OracleHelper.AddCommandParameter(cmd, ":fileType", id, OracleType.Char, 4);
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
}
}
public override int Add(BatchJob newItem)
{
using (OracleConnection cnn = new OracleConnection(base.ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"insert into {0}.batch_job \r\n"
+ "( submitted_on ,completed_on ,batch_status \r\n"
+ " ,submission_type ,submitted_by ,err_msg \r\n"
+ " ,comments ,batch_job_type ,timespan_ge \r\n"
+ " ,timespan_lt ,nbr_sel ,nbr_ins \r\n"
+ " ,nbr_upd ,nbr_del ,nbr_reject \r\n"
+ " ,ext_sys) \r\n"
+ "values \r\n"
+ "( sysdate ,:completed_on ,:batch_status \r\n"
+ " ,:submission_type ,user ,:err_msg \r\n"
+ " ,:comments ,:batch_job_type ,:timespan_ge \r\n"
+ " ,:timespan_lt ,:nbr_sel ,:nbr_ins \r\n"
+ " ,:nbr_upd ,:nbr_del ,:nbr_reject \r\n"
+ " ,:ext_sys) \r\n"
+ "returning batch_job_id into :batch_job_id"
, base.SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
OracleHelper.AddCommandParameter(cmd, ":completed_on", CheckNull(newItem.COMPLETED_ON, new DateTime(1900, 1, 1)), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":batch_status", CheckNull(newItem.BATCH_STATUS), OracleType.Char, 4, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":submission_type", CheckNull(newItem.SUBMISSION_TYPE), OracleType.Char, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":err_msg", CheckNull(newItem.ERR_MSG), OracleType.VarChar, 4000, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":comments", CheckNull(newItem.COMMENTS), OracleType.VarChar, 200, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":batch_job_type", CheckNull(newItem.BATCH_JOB_TYPE), OracleType.Char, 4, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":timespan_ge", CheckNull(newItem.TIMESPAN_GE), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":timespan_lt", CheckNull(newItem.TIMESPAN_LT), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_sel", CheckNull(newItem.NBR_SEL, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_ins", CheckNull(newItem.NBR_INS, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_upd", CheckNull(newItem.NBR_UPD, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_del", CheckNull(newItem.NBR_DEL, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_reject", CheckNull(newItem.NBR_REJECT, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_sys", CheckNull(newItem.EXT_SYS), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":batch_job_id", OracleType.Number, ParameterDirection.ReturnValue);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
return(int.Parse(cmd.Parameters[":batch_job_id"].Value.ToString())); // Return the InvoiceGrpId of the new record.
}
}
public override void Add(ExtWorkType client)
{
string sql = String.Format("insert into {0}.ext_work_type \r\n "
+ "( ext_sys ,ext_client_key ,ext_work_type \r\n"
+ " ,descr ,tat_threshold ,std_work_type \r\n"
+ " ,invoice_grp_id ,stat_tat_threshold ,rate_nbr \r\n"
+ " ,tally_for_volume_evt ,platform_wt_id) \r\n"
+ "values \r\n"
+ "( :ext_sys ,:ext_client_key ,:ext_work_type \r\n"
+ " ,:descr ,:tat_threshold ,:std_work_type \r\n"
+ " ,:invoice_grp_id ,:stat_tat_threshold ,:rate_nbr \r\n"
+ " ,:tally_for_volume_evt ,:platform_wt_id)"
, base.SchemaName);
using (IDbConnection cnn = new OracleConnection(ConnectionString.Value))
using (IDbCommand cmd = new OracleCommand(sql, (OracleConnection)cnn))
{
cmd.CommandType = CommandType.Text;
OracleHelper.AddCommandParameter(cmd, ":ext_sys", client.EXT_SYS, OracleType.Char, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_client_key", client.EXT_CLIENT_KEY, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ext_work_type", client.EXT_WORK_TYPE, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":descr", CheckNull(client.DESCR), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":tat_threshold", CheckNull(client.TAT_THRESHOLD), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":std_work_type", CheckNull(client.STD_WORK_TYPE), OracleType.VarChar, 4, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":invoice_grp_id", CheckNull(client.INVOICE_GRP_ID), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":stat_tat_threshold", CheckNull(client.STAT_TAT_THRESHOLD), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":rate_nbr", CheckNull(client.RATE_NBR), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":tally_for_volume_evt", (client.TALLY_FOR_VOLUME_EVT == true) ? "Y" : "N", OracleType.VarChar, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":platform_wt_id", client.PLATFORM_WT_ID, OracleType.VarChar, 20, ParameterDirection.Input);
cnn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
if (ex.ErrorCode == -2146232008 && ex.Code == 1) // TODO: Find out why this is needed.
{
}
throw ex;
}
}
}
public override string Save(InvoiceDetailFileTypeItem item)
{
using (OracleConnection cnn = new OracleConnection(base.ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format("declare \r\n"
+ " cnt number; \r\n"
+ "begin \r\n"
+ " select count(*) \r\n"
+ " into cnt \r\n"
+ " from {0}.file_type \r\n"
+ " where file_type = :fileType; \r\n"
+ "\r\n"
+ " if( cnt = 0 ) then \r\n"
+ " insert into {0}.file_type (file_type, descr, is_available, extension, sql) \r\n"
+ " values (:fileType, :descr, :isAvailable, :fileExtension, :sql); \r\n"
+ " else \r\n"
+ " update {0}.file_type \r\n"
+ " set descr = :descr \r\n"
+ " ,is_available = :isAvailable \r\n"
+ " ,extension = :fileExtension \r\n"
+ " ,sql = :sql \r\n"
+ " where file_type = :fileType; \r\n"
+ " end if; \r\n"
+ "end; \r\n"
+ "commit;"
, base.SchemaName);
OracleHelper.AddCommandParameter(cmd, ":fileType", item.Id, OracleType.Char, 4);
OracleHelper.AddCommandParameter(cmd, ":descr", item.Description, OracleType.VarChar, 100);
char isAvailable = item.IsAvailable ? 'Y' : 'N';
OracleHelper.AddCommandParameter(cmd, ":isAvailable", isAvailable, OracleType.Char, 1);
OracleHelper.AddCommandParameter(cmd, ":fileExtension", item.FileExtension, OracleType.VarChar, 4);
OracleHelper.AddCommandParameter(cmd, ":sql", item.Sql, OracleType.VarChar, 4000);
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
return(item.Id);
}
}
public override IDataReader GetReader(IDbConnection cnn, string platform)
{
string sql = String.Format("select ext_sys ,ext_client_key ,default_invoice_grp_id \r\n"
+ " ,descr ,cost_code ,ext_work_type_source_descr \r\n"
+ " ,override_key_source ,default_tat_threshold \r\n"
+ "from {0}.ext_client \r\n"
+ "where ext_sys = :ext_sys \r\n"
+ "order by ext_sys, ext_client_key"
, base.SchemaName);
if (cnn == null)
{
cnn = new OracleConnection(ConnectionString.Value);
}
OpenConnection(cnn);
using (IDbCommand cmd = new OracleCommand(sql, (OracleConnection)cnn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60 * 60;
OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", platform, OracleType.VarChar, ParameterDirection.Input);
return(cmd.ExecuteReader());
}
}
public override bool Add(ClientLocation client)
{
string sql = String.Format("INSERT INTO {0}.EXT_CLIENT \r\n"
+ " (EXT_SYS ,EXT_CLIENT_KEY ,DEFAULT_INVOICE_GRP_ID \r\n"
+ " ,DESCR ,COST_CODE ,EXT_WORK_TYPE_SOURCE_DESCR \r\n"
+ " ,OVERRIDE_KEY_SOURCE ,DEFAULT_TAT_THRESHOLD) \r\n"
+ "VALUES \r\n"
+ "( :EXT_SYS ,:EXT_CLIENT_KEY ,:DEFAULT_INVOICE_GRP_ID \r\n"
+ " ,:DESCR ,:COST_CODE ,:EXT_WORK_TYPE_SOURCE_DESCR \r\n"
+ " ,:OVERRIDE_KEY_SOURCE ,:DEFAULT_TAT_THRESHOLD)"
, base.SchemaName);
using (IDbConnection cnn = new OracleConnection(ConnectionString.Value))
using (IDbCommand cmd = new OracleCommand(sql, (OracleConnection)cnn))
{
cmd.CommandType = CommandType.Text;
OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", CheckNull(client.ExtSys), OracleType.Char, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_CLIENT_KEY", CheckNull(client.ExtClientKey), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEFAULT_INVOICE_GRP_ID", CheckNull(client.DefaultInvoiceGrpID), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DESCR", CheckNull(client.Description), OracleType.VarChar, 100, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":COST_CODE", CheckNull(client.CostCode), OracleType.VarChar, 10, ParameterDirection.Input);
if (String.IsNullOrEmpty(client.ExtWorkTypeSourceDescr))
{
client.ExtWorkTypeSourceDescr = "<DEFAULT>";
}
OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_TYPE_SOURCE_DESCR", client.ExtWorkTypeSourceDescr, OracleType.VarChar, 30, ParameterDirection.Input);
if (String.IsNullOrEmpty(client.OverrideKeySource))
{
client.OverrideKeySource = "<DEFAULT>";
}
OracleHelper.AddCommandParameter(cmd, ":OVERRIDE_KEY_SOURCE", client.OverrideKeySource, OracleType.VarChar, 30, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEFAULT_TAT_THRESHOLD", CheckNull(client.DefaultTatThreshold), OracleType.Number, ParameterDirection.Input);
cnn.Open();
return(cmd.ExecuteNonQuery() > 0);
}
}
public override bool Update(BatchJob batch)
{
using (OracleConnection cnn = new OracleConnection(ConnectionString.Value))
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"update {0}.batch_job set \r\n"
+ " completed_on = :completed_on \r\n"
+ " ,batch_status = :batch_status \r\n"
+ " ,err_msg = :err_msg \r\n"
+ " ,comments = :comments \r\n"
+ " ,nbr_sel = :nbr_sel \r\n"
+ " ,nbr_ins = :nbr_ins \r\n"
+ " ,nbr_upd = :nbr_upd \r\n"
+ " ,nbr_del = :nbr_del \r\n"
+ " ,nbr_reject = :nbr_reject \r\n"
+ "where batch_job_id = :batch_job_id"
, SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
OracleHelper.AddCommandParameter(cmd, ":completed_on", CheckNull(batch.COMPLETED_ON, DateTime.Now), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":batch_status", CheckNull(batch.BATCH_STATUS), OracleType.Char, 4, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":err_msg", CheckNull(batch.ERR_MSG), OracleType.VarChar, 4000, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":comments", CheckNull(batch.COMMENTS), OracleType.VarChar, 200, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_sel", CheckNull(batch.NBR_SEL, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_ins", CheckNull(batch.NBR_INS, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_upd", CheckNull(batch.NBR_UPD, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_del", CheckNull(batch.NBR_DEL, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":nbr_reject", CheckNull(batch.NBR_REJECT, 0), OracleType.Number, 9, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":batch_job_id", batch.BATCH_JOB_ID, OracleType.Number, ParameterDirection.Input);
cnn.Open();
int recsEffected = cmd.ExecuteNonQuery();
cnn.Close();
return(recsEffected > 0);
}
}
public override bool Update(WorkUnit item, DateRange range)
{
using (IDbConnection cnn = OpenConnection())
using (IDbCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"UPDATE {0}.WORK_UNIT SET \r\n"
+ " EXT_CLIENT_KEY = :EXT_CLIENT_KEY \r\n"
+ " ,EXT_WORK_TYPE = :EXT_WORK_TYPE \r\n"
+ " ,WORK_UNIT_STATUS = :WORK_UNIT_STATUS \r\n"
+ " ,JOB_NBR = :JOB_NBR \r\n"
+ " ,URGENCY = :URGENCY \r\n"
+ " ,MT_ID = :FIRST_MT_ID \r\n"
+ " ,FIRST_MT_ID = :FIRST_MT_ID \r\n"
+ " ,MT_NAME = :MT_NAME \r\n"
+ " ,DICTATION_ID = :DICTATION_ID "
+ " ,DICTATOR_ID = :DICTATOR_ID "
+ " ,TAT_BEGINS_ON = :TAT_BEGINS_ON "
+ " ,TAT_ENDS_ON = :TAT_ENDS_ON "
+ " ,PATIENT_ID = :PATIENT_ID "
//+ " ,CHARS_TOTAL = :CHARS_TOTAL "
+ " ,CHARS_NOSPACES = :CHARS_NOSPACES "
//+ " ,INVOICE_GRP_OVERRIDE_KEY = :INVOICE_GRP_OVERRIDE_KEY "
+ " ,PATIENT_LAST = :PATIENT_LAST "
//+ " ,ENTRY_LOC = :ENTRY_LOC "
+ " ,DICTATOR_NAME = :DICTATOR_NAME "
+ " ,DEPARTMENT = :DEPARTMENT "
//+ " ,CUSTOMER_ACCT_NBR = :CUSTOMER_ACCT_NBR "
+ " ,DICTATOR_SPECIALTY = :DICTATOR_SPECIALTY \r\n"
//+ " ,LINES_GROSS = :LINES_GROSS "
+ " ,DICTATION_SECONDS = :DICTATION_SECONDS "
+ " ,DICTATOR_SR_SCORE = :DICTATOR_SR_SCORE "
+ " ,DOCUMENT_SR_SCORE = :DOCUMENT_SR_SCORE "
+ " ,SERVICE_PROVIDER = :SERVICE_PROVIDER "
//+ " ,VIRTUAL_LOCATION_ID = :VIRTUAL_LOCATION_ID "
//+ " ,LOCATION_NAME = :LOCATION_NAME "
//+ " ,CLIENT_DICTATOR_ID = :CLIENT_DICTATOR_ID "
//+ " ,CLIENT_WORKTYPE_ID = :CLIENT_WORKTYPE_ID "
+ " ,SERVICE_PROVIDER_NAME = :SERVICE_PROVIDER_NAME "
+ " ,CUSTOM1 = :CUSTOM1 "
+ " ,CUSTOM2 = :CUSTOM2 "
+ " ,CUSTOM3 = :CUSTOM3 "
//+ " ,DICTATING_PHYSICIAN_ID = :DICTATING_PHYSICIAN_ID "
//+ " ,PAT_COSTCENTER = :PAT_COSTCENTER "
//+ " ,PAT_LOC = :PAT_LOC "
//+ " ,PAT_ROOM = :PAT_ROOM "
+ " ,ACTIVITY_TYPE = :ACTIVITY_TYPE \r\n"
+ " ,CHANGED_ON = SYSDATE "
+ " ,DEP_COUNT = :DEP_COUNT \r\n"
+ "WHERE EXT_SYS = :EXT_SYS \r\n"
+ " AND EXT_WORK_UNIT_KEY = :EXT_WORK_UNIT_KEY \r\n"
+ " AND TAT_ENDS_ON >= :RANGE_BEGIN \r\n"
+ " AND TAT_ENDS_ON < :RANGE_END \r\n"
+ " AND INVOICE_ID IS NULL"
, SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
OracleHelper.AddCommandParameter(cmd, ":EXT_CLIENT_KEY", item.ExtClientKey, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_TYPE", item.ExtWorkType, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":WORK_UNIT_STATUS", item.WorkUnitStatus, OracleType.Char, 2, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":JOB_NBR", item.JobNbr, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":URGENCY", item.Urgency, OracleType.Char, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":FIRST_MT_ID", CheckNull(item.FirstMTID), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":MT_NAME", CheckNull(item.MTName), OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATION_ID", CheckNull(item.DictationID), OracleType.VarChar, 64, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_ID", CheckNull(item.DictatorID), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":TAT_BEGINS_ON", CheckNull(item.TatBeginsOn), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":TAT_ENDS_ON", CheckNull(item.TatEndsOn), OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PATIENT_ID", CheckNull(item.PatientID), OracleType.VarChar, 40, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":CHARS_TOTAL", CheckNull(item.CharsTotal), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CHARS_NOSPACES", CheckNull(item.CharsNoSpaces), OracleType.Number, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":INVOICE_GRP_OVERRIDE_KEY", CheckNull(item.InvoiceGrpOverrideKey), OracleType.VarChar, 80, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PATIENT_LAST", CheckNull(item.PatientLast), OracleType.VarChar, 40, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":ENTRY_LOC", CheckNull(item.EntryLocation), OracleType.Char, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_NAME", CheckNull(item.DictatorName), OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEPARTMENT", CheckNull(item.Department), OracleType.VarChar, 50, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":CUSTOMER_ACCT_NBR", CheckNull(item.CustomerAcctNbr), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_SPECIALTY", CheckNull(item.DictatorSpecialty), OracleType.VarChar, 64, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":LINES_GROSS", CheckNull(item.LinesGross, OracleType.Number), ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATION_SECONDS", CheckNull(item.DictationSeconds), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_SR_SCORE", CheckNull(item.DictatorSRScore), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DOCUMENT_SR_SCORE", CheckNull(item.DocumentSRScore), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":SERVICE_PROVIDER", CheckNull(item.ServiceProvider), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM1", CheckNull(item.Custom1), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM2", CheckNull(item.Custom2), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM3", CheckNull(item.Custom3), OracleType.VarChar, 20, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":VIRTUAL_LOCATION_ID", CheckNull(item.VirtualLocationID), OracleType.Int32, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":LOCATION_NAME", CheckNull(item.LocationName), OracleType.VarChar, 50, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":CLIENT_DICTATOR_ID", CheckNull(item.ClientDicatatorID), OracleType.VarChar, 50, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":CLIENT_WORKTYPE_ID", CheckNull(item.ClientWorkTypeID), OracleType.Int32, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":SERVICE_PROVIDER_NAME", CheckNull(item.ServiceProviderName), OracleType.VarChar, 50, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":DICTATING_PHYSICIAN_ID", CheckNull(item.DictatingPhysicianID), OracleType.Int32, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":PAT_COSTCENTER", CheckNull(item.PatientCostCenter), OracleType.VarChar, 255, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":PAT_LOC", CheckNull(item.PatientLocation), OracleType.VarChar, 255, ParameterDirection.Input);
//OracleHelper.AddCommandParameter(cmd, ":PAT_ROOM", CheckNull(item.PatientRoom), OracleType.VarChar, 255, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ACTIVITY_TYPE", CheckNull(item.ActivityType), OracleType.Char, 2, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEP_COUNT", CheckNull(item.DepCount), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", item.ExtSys, OracleType.Char, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_UNIT_KEY", item.ExtWorkUnitKey, OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":RANGE_BEGIN", range.BeginsOn, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":RANGE_END", range.EndsBefore, OracleType.DateTime, ParameterDirection.Input);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("ExtWorkUnitKey = {0}", item.ExtWorkUnitKey);
Console.WriteLine("ExtClientKey = {0}", item.ExtClientKey);
throw;
}
finally
{
cnn.Close();
}
}
return(true);
}
public override bool Add(WorkUnit item)
{
using (IDbConnection cnn = OpenConnection())
{
/*
* // Check for existance of an existing matching row.
* // TODO: Figure out how to execute this and the insert in one DB call.
* int cnt = 0;
* using (IDbCommand cmd = new OracleCommand())
* {
* cmd.CommandText = String.Format( "SELECT COUNT(*) AS CNT "
+ "FROM {0}.WORK_UNIT "
+ "WHERE EXT_SYS = :EXT_SYS "
+ " AND EXT_WORK_UNIT_KEY = :EXT_WORK_UNIT_KEY "
+ " AND TAT_ENDS_ON >= :JDATE - 1 "
+ " AND TAT_ENDS_ON < :JDATE + 2"
+ , SchemaName);
+ cmd.CommandType = CommandType.Text;
+ cmd.Connection = (OracleConnection)cnn;
+ OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", item.ExtSys, OracleType.Char, 20, ParameterDirection.Input);
+ OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_UNIT_KEY", item.ExtWorkUnitKey, OracleType.VarChar, 60, ParameterDirection.Input);
+ OracleHelper.AddCommandParameter(cmd, ":JDATE", item.TatEndsOn, OracleType.DateTime, ParameterDirection.Input);
+ IDataReader dr = cmd.ExecuteReader();
+ dr.Read();
+ cnt = int.Parse(dr["CNT"].ToString());
+ dr.Close();
+ dr.Dispose();
+ }
+ if (cnt > 0)
+ {
+ cnn.Close();
+ return false;
+ }
*/
// Add a new row.
using (IDbCommand cmd = new OracleCommand())
{
cmd.CommandText = String.Format(
"insert into {0}.work_unit( EXT_SYS ,EXT_WORK_UNIT_KEY ,EXT_CLIENT_KEY "
+ " ,EXT_WORK_TYPE ,WORK_UNIT_STATUS ,JOB_NBR "
+ " ,URGENCY ,MT_ID ,FIRST_MT_ID "
+ " ,MT_NAME ,DICTATION_ID ,DICTATOR_ID "
+ " ,TAT_BEGINS_ON ,TAT_ENDS_ON ,PATIENT_ID "
+ " ,CHARS_TOTAL ,CHARS_NOSPACES "
+ " ,INVOICE_GRP_OVERRIDE_KEY ,PATIENT_LAST ,ENTRY_LOC "
+ " ,DICTATOR_NAME ,DEPARTMENT ,CUSTOMER_ACCT_NBR "
+ " ,DICTATOR_SPECIALTY ,LINES_GROSS ,DICTATION_SECONDS "
+ " ,SERVICE_PROVIDER ,VIRTUAL_LOCATION_ID ,LOCATION_NAME "
+ " ,CUSTOM1 ,CUSTOM2 ,CUSTOM3 "
+ " ,CLIENT_DICTATOR_ID ,CLIENT_WORKTYPE_ID ,SERVICE_PROVIDER_NAME "
+ " ,DICTATING_PHYSICIAN_ID ,PAT_COSTCENTER ,PAT_LOC "
+ " ,PAT_ROOM ,DICTATOR_SR_SCORE ,DOCUMENT_SR_SCORE "
+ " ,INSERTED_ON ,CHANGED_ON ,ACTIVITY_TYPE "
+ " ,DEP_COUNT ) "
+ "values ( :EXT_SYS ,:EXT_WORK_UNIT_KEY ,:EXT_CLIENT_KEY "
+ " ,:EXT_WORK_TYPE ,:WORK_UNIT_STATUS ,:JOB_NBR "
+ " ,:URGENCY ,:FIRST_MT_ID ,:FIRST_MT_ID "
+ " ,:MT_NAME ,:DICTATION_ID ,:DICTATOR_ID "
+ " ,:TAT_BEGINS_ON ,:TAT_ENDS_ON ,:PATIENT_ID "
+ " ,:CHARS_TOTAL ,:CHARS_NOSPACES "
+ " ,:INVOICE_GRP_OVERRIDE_KEY ,:PATIENT_LAST ,:ENTRY_LOC "
+ " ,:DICTATOR_NAME ,:DEPARTMENT ,:CUSTOMER_ACCT_NBR "
+ " ,:DICTATOR_SPECIALTY ,:LINES_GROSS ,:DICTATION_SECONDS "
+ " ,:SERVICE_PROVIDER ,:VIRTUAL_LOCATION_ID ,:LOCATION_NAME "
+ " ,:CUSTOM1 ,:CUSTOM2 ,:CUSTOM3 "
+ " ,:CLIENT_DICTATOR_ID ,:CLIENT_WORKTYPE_ID ,:SERVICE_PROVIDER_NAME "
+ " ,:DICTATING_PHYSICIAN_ID ,:PAT_COSTCENTER ,:PAT_LOC "
+ " ,:PAT_ROOM ,:DICTATOR_SR_SCORE ,:DOCUMENT_SR_SCORE "
+ " ,SYSDATE ,SYSDATE ,:ACTIVITY_TYPE "
+ " ,:DEP_COUNT ) "
+ "returning work_unit_id into :work_unit_id"
, SchemaName);
cmd.CommandType = CommandType.Text;
cmd.Connection = (OracleConnection)cnn;
OracleHelper.AddCommandParameter(cmd, ":EXT_SYS", item.ExtSys, OracleType.Char, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_UNIT_KEY", item.ExtWorkUnitKey, OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_CLIENT_KEY", item.ExtClientKey, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":EXT_WORK_TYPE", item.ExtWorkType, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":WORK_UNIT_STATUS", item.WorkUnitStatus, OracleType.Char, 2, ParameterDirection.Input);
if (item.WorkUnitStatus == null)
{
((OracleParameter)cmd.Parameters[":WORK_UNIT_STATUS"]).Value = "NT";
}
OracleHelper.AddCommandParameter(cmd, ":JOB_NBR", item.JobNbr, OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":URGENCY", item.Urgency, OracleType.Char, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":FIRST_MT_ID", CheckNull(item.FirstMTID), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":MT_NAME", CheckNull(item.MTName), OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATION_ID", CheckNull(item.DictationID), OracleType.VarChar, 64, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_ID", CheckNull(item.DictatorID), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":TAT_BEGINS_ON", item.TatBeginsOn, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":TAT_ENDS_ON", item.TatEndsOn, OracleType.DateTime, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PATIENT_ID", CheckNull(item.PatientID), OracleType.VarChar, 40, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CHARS_TOTAL", CheckNull(item.CharsTotal), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CHARS_NOSPACES", CheckNull(item.CharsNoSpaces), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":INVOICE_GRP_OVERRIDE_KEY", CheckNull(item.InvoiceGrpOverrideKey), OracleType.VarChar, 80, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PATIENT_LAST", CheckNull(item.PatientLast), OracleType.VarChar, 40, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ENTRY_LOC", CheckNull(item.EntryLocation), OracleType.Char, 1, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_NAME", CheckNull(item.DictatorName), OracleType.VarChar, 60, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEPARTMENT", CheckNull(item.Department), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOMER_ACCT_NBR", CheckNull(item.CustomerAcctNbr), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_SPECIALTY", CheckNull(item.DictatorSpecialty), OracleType.VarChar, 64, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":LINES_GROSS", CheckNull(item.LinesGross), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATION_SECONDS", CheckNull(item.DictationSeconds), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":SERVICE_PROVIDER", CheckNull(item.ServiceProvider), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":VIRTUAL_LOCATION_ID", CheckNull(item.VirtualLocationID), OracleType.Int32, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":LOCATION_NAME", CheckNull(item.LocationName), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM1", CheckNull(item.Custom1), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM2", CheckNull(item.Custom2), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CUSTOM3", CheckNull(item.Custom3), OracleType.VarChar, 20, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CLIENT_DICTATOR_ID", CheckNull(item.ClientDicatatorID), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":CLIENT_WORKTYPE_ID", CheckNull(item.ClientWorkTypeID), OracleType.Int32, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":SERVICE_PROVIDER_NAME", CheckNull(item.ServiceProviderName), OracleType.VarChar, 50, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DICTATING_PHYSICIAN_ID", CheckNull(item.DictatingPhysicianID), OracleType.Int32, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PAT_COSTCENTER", CheckNull(item.PatientCostCenter), OracleType.VarChar, 255, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PAT_LOC", CheckNull(item.PatientLocation), OracleType.VarChar, 255, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":PAT_ROOM", CheckNull(item.PatientRoom), OracleType.VarChar, 255, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":WORK_UNIT_ID", OracleType.Number, ParameterDirection.ReturnValue);
OracleHelper.AddCommandParameter(cmd, ":DICTATOR_SR_SCORE", CheckNull(item.DictatorSRScore), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DOCUMENT_SR_SCORE", CheckNull(item.DocumentSRScore), OracleType.Number, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":ACTIVITY_TYPE", CheckNull(item.ActivityType), OracleType.Char, 2, ParameterDirection.Input);
OracleHelper.AddCommandParameter(cmd, ":DEP_COUNT", CheckNull(item.DepCount), OracleType.Number, ParameterDirection.Input);
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
if (ex.ErrorCode == -2146232008 && ex.Code == 1) // record already exists.
{
return(false);
}
Console.WriteLine("ExtWorkUnitKey = {0}", item.ExtWorkUnitKey);
Console.WriteLine("ExtClientKey = {0}", item.ExtClientKey);
throw;
}
finally
{
cnn.Close();
}
item.WorkUnitID = long.Parse(((OracleParameter)cmd.Parameters[":work_unit_id"]).Value.ToString()); // Return the InvoiceGrpId of the new record.
return(true);
}
}
}