public override Contract GetByContractID(int contractID)
{
Contract contract = null;
try
{
string sql = string.Format("select * from {0}.CONTRACT where CONTRACT_ID=:CID", SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":cid", contractID, OracleType.Number, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
if (dt.Rows.Count > 0)
{
contract = new Contract();
RowConverter(contract, dt.Rows[0]);
return(contract);
}
return(null);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(contract);
}
public override void Update(AdjType r)
{
try
{
string sql = String.Format("update {0}.ADJ_TYPE set "
+ " ADJ_TYPE = :ADJ_TYPE "
+ " ,APPLY_AFTER_FINAL_COST = :APPLY_AFTER_FINAL_COST "
+ " ,DESCR = :DESCR "
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":ADJ_TYPE", CheckNull(r.ADJ_TYPE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":APPLY_AFTER_FINAL_COST", (r.APPLY_AFTER_FINAL_COST == true) ? 'Y' : 'N', OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Remove(InvoiceGroup item)
{
string sql = String.Format("delete {0}.INVOICE_GRP where INVOICE_GRP_ID = :INVOICE_GRP_ID", SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":INVOICE_GRP_ID", item.InvoiceGrpId, OracleType.Int32, ParameterDirection.Input) };
OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, p);
}
public override void Remove(Contract entity)
{
string sql = String.Format("delete {0}.CONTRACT where CONTRACT_ID = :CONTRACT_ID", SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":CONTRACT_ID", entity.CONTRACT_ID, OracleType.Number, ParameterDirection.Input) };
OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, p);
}
public override void Update(BatchJobType r)
{
if (string.IsNullOrEmpty(r.BATCH_JOB_TYPE))
{
throw new ArgumentNullException("Item.BATCH_JOB_TYPE", "An item BATCH_JOB_TYPE was not provided for the update.");
}
try
{
string sql = String.Format("update {0}.BATCH_JOB_TYPE set "
+ " BATCH_JOB_TYPE = :BATCH_JOB_TYPE "
+ " ,DESCR = :DESCR "
+ " ,PLATFORM = :PLATFORM "
+ " where BATCH_JOB_TYPE = :BATCH_JOB_TYPE", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":BATCH_JOB_TYPE", CheckNull(r.BATCH_JOB_TYPE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":PLATFORM", CheckNull(r.PLATFORM), OracleType.VarChar, ParameterDirection.Input));
OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
/// <summary>
/// Special note on this. The keys are
/// CONTRACT_ID
/// TAT_SCHED_ID
/// BEGIN_ON
///
/// The fields TAT_SCHED_ID and BEGIN_ON are editable and represent a condition
/// where they keys may collide - Current software checks this condition and
/// displays a warning to avoid it.
/// </summary>
/// <param name="r"></param>
public override void Update(InvoiceGrpOverride r)
{
try
{
string sql = String.Format("update {0}.INVOICE_GRP_OVERRIDE set "
+ " DEFAULT_INVOICE_GRP_ID = :DEFAULT_INVOICE_GRP_ID "
+ " ,OVERRIDE_KEY = :OVERRIDE_KEY "
+ " ,SEND_TO_INVOICE_GRP_ID = :SEND_TO_INVOICE_GRP_ID "
+ " where "
+ " OVERRIDE_KEY = :OVERRIDE_KEY AND "
+ " DEFAULT_INVOICE_GRP_ID = :DEFAULT_INVOICE_GRP_ID "
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":DEFAULT_INVOICE_GRP_ID", CheckNull(r.DEFAULT_INVOICE_GRP_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":OVERRIDE_KEY", CheckNull(r.OVERRIDE_KEY), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SEND_TO_INVOICE_GRP_ID", CheckNull(r.SEND_TO_INVOICE_GRP_ID), OracleType.Number, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Update(ReportTypePackage package)
{
if (package.Id == 0)
{
throw new ArgumentNullException("Item.InvoiceGrpId", "An item InvoiceGrpId was provided for the update.");
}
try
{
string sql = String.Format("update {0}.REPORT_TYPE set "
+ " DESC = :DESC "
+ " REPORT_TYPE_ID = :REPORT_TYPE_ID "
+ " SHORT_NAME = :SHORT_NAME "
+ " SQL = :SQL "
+ " ,ALLOW_DYNAMIC_MODS = ALLOW_DYNAMIC_MODS "
+ " ,DATA_DUMP_ONLY = DATA_DUMP_ONLY "
+ " ,MUST_ENCRYPT = :MUST_ENCRYPT "
+ "where REPORT_TYPE_ID = :REPORT_TYPE_ID"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":DESC", package.Description, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":REPORT_TYPE_ID", null, OracleType.Int32, ParameterDirection.InputOutput));
parameters.Add(OracleHelper.CreateParameter(":SHORT_NAME", package.ShortName, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SQL", package.Sql, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ALLOW_DYNAMIC_MODS", package.AllowDynamicMod, OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DATA_DUMP_ONLY", package.DataDumpOnly, OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":MUST_ENCRYPT", package.MustEncrypt, OracleType.Char, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
public override void Update(TatCompMethod package)
{
try
{
string sql = String.Format("update {0}.TAT_COMP_METHOD set "
+ " DESCR = :DESCR "
+ " ,SHORT_DESCR = :SHORT_DESCR"
+ " ,LONG_DESCR = :LONG_DESCR "
+ " ,SHOW_TAT_COLUMN_ON_INVOICE = :SHOW_TAT_COLUMN_ON_INVOICE "
+ "where TAT_COMP_METHOD = :TAT_COMP_METHOD"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":DESCR", package.DESCR, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SHORT_DESCR", package.SHORT_DESCR, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":LONG_DESCR", package.LONG_DESCR, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SHOW_TAT_COLUMN_ON_INVOICE", (package.SHOW_TAT_COLUMN_ON_INVOICE)?'Y':'N', OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":TAT_COMP_METHOD", package.TAT_COMP_METHOD, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
//throw ex;
}
}
public override void Remove(ContractNote item)
{
string sql = String.Format("delete {0}.contract_note where contract_note_id = :contract_note_id", SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":contract_note_id", item.ID, OracleType.Int32, ParameterDirection.Input) };
OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, p);
}
public override List <InvoiceStyleColumn> GetByInvoiceID(int invoiceID)
{
string sql = String.Format("select ig.invoice_style \r\n"
+ " ,isc.column_id \r\n"
+ " ,nvl(isco.column_index, isc.column_index) as column_index \r\n"
+ " ,nvl(isco.column_name, isc.column_name) as column_name \r\n"
+ " ,nvl(isco.sql_column_name, isc.sql_column_name) as sql_column_name \r\n"
+ " ,nvl(isco.grouping_index, isc.grouping_index) as grouping_index \r\n"
+ " ,nvl(isco.sort_index, isc.sort_index) as sort_index \r\n"
+ " ,nvl(isco.width, isc.width) as width \r\n"
+ "from {0}.invoice_grp ig \r\n"
+ "join {0}.invoice_style_column isc \r\n"
+ " on ig.invoice_style = isc.invoice_style \r\n"
+ "left join {0}.invoice_grp_is_column_override isco \r\n"
+ " on isco.invoice_grp_id = ig.invoice_grp_id \r\n"
+ " and isco.column_id = isc.column_id \r\n"
+ "where ig.invoice_grp_id in (select invoice_grp_id \r\n"
+ " from {0}.invoice \r\n"
+ " where invoice_id = :invoice_id) \r\n"
, base.SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":invoice_id", invoiceID, OracleType.Int32, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
return(ConvertDataTableToList(dt));
}
public override ObservableCollection <InvoiceCount> FetchCount(DateTime start, DateTime end)
{
string sql = String.Format("select bill_period_start, BILL_PERIOD_END_BEFORE, count(*) as THECOUNT"
+ " from {0}.invoice "
+ " where BILL_PERIOD_START >= :BILL_PERIOD_START"
+ " and BILL_PERIOD_END_BEFORE <= :BILL_PERIOD_END_BEFORE"
+ " group by BILL_PERIOD_START, BILL_PERIOD_END_BEFORE"
+ " order by BILL_PERIOD_START DESC", SchemaName);
ObservableCollection <InvoiceCount> invoiceCount = new ObservableCollection <InvoiceCount>();
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":BILL_PERIOD_START", CheckNull(start), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":BILL_PERIOD_END_BEFORE", CheckNull(end), OracleType.DateTime, ParameterDirection.Input));
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
foreach (DataRow row in dt.Rows)
{
InvoiceCount item = new InvoiceCount
{
BILL_PERIOD_START = CastDbValueRow(row, "BILL_PERIOD_START", true, "DateTime"),
BILL_PERIOD_END_BEFORE = CastDbValueRow(row, "BILL_PERIOD_END_BEFORE", true, "DateTime"),
COUNT = CastDbValueRow(row, "THECOUNT", true, "Int32")
};
invoiceCount.Add(item);
}
return(invoiceCount);
}
public override void Update(ScopeRule r)
{
try
{
string sql = String.Format("update {0}.SCOPE_RULE set "
+ " SCOPE_RULE = :SCOPE_RULE "
+ " ,DESCR = :DESCR "
+ " where "
+ " SCOPE_RULE = :SCOPE_RULE"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":SCOPE_RULE", CheckNull(r.SCOPE_RULE), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Update(AddOnChgType r)
{
try
{
string sql = String.Format("update {0}.ADD_ON_CHG_TYPE set "
+ " DESCR = :DESCR "
+ " where "
+ " ADD_ON_CHG_TYPE_ID = :ADD_ON_CHG_TYPE_ID"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":ADD_ON_CHG_TYPE_ID", CheckNull(r.ADD_ON_CHG_TYPE_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DESCR", CheckNull(r.DESCR), OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Update(ContractNote item)
{
if (item.ID == 0)
{
throw new ArgumentNullException("Item.InvoiceGrpId", "An item InvoiceGrpId was provided for the update.");
}
try
{
string sql = String.Format("update {0}.contract_note set "
+ " contract_id = :contract_id "
+ " ,added_dt = sysdate "
+ " ,added_by = user "
+ " ,note = :note "
+ "where contract_note_id = :contract_note_id"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":contract_note_id", item.ID, OracleType.Int32, ParameterDirection.InputOutput));
parameters.Add(OracleHelper.CreateParameter(":contract_id", item.ContractID, OracleType.Int32, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":note", item.Note, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
public override ObservableCollection <BatchJob> GetByFilter(DateTime?from, DateTime?through, string jobTypeFilter)
{
ObservableCollection <BatchJob> batchJobs;
try
{
string sql;
// SUBMITTED_ON
// COMPLETED_ON
if (through == null)
{
through = DateTime.Now;
}
if (String.IsNullOrEmpty(jobTypeFilter) && from != null && through != null)
{
sql = String.Format("Select * from {0}.BATCH_JOB where SUBMITTED_ON >= :SUBMITTED_ON and COMPLETED_ON < :COMPLETED_ON", SchemaName);
}
else if (from != null && through != null && !String.IsNullOrEmpty(jobTypeFilter))
{
sql = String.Format("Select * from {0}.BATCH_JOB where SUBMITTED_ON >=:SUBMITTED_ON and COMPLETED_ON < :COMPLETED_ON and BATCH_JOB_TYPE=:BATCH_JOB_TYPE", SchemaName);
}
else if (!String.IsNullOrEmpty(jobTypeFilter))
{
sql = String.Format("Select * from {0}.BATCH_JOB where BATCH_JOB_TYPE=:BATCH_JOB_TYPE", SchemaName);
}
else
{
sql = String.Format("Select * from {0}.BATCH_JOB", SchemaName);
}
List <OracleParameter> parameters = new List <OracleParameter>();
if (from != null)
{
parameters.Add(OracleHelper.CreateParameter(":SUBMITTED_ON", CheckNull(from), OracleType.DateTime, ParameterDirection.Input));
}
if (through != null && from != null)
{
parameters.Add(OracleHelper.CreateParameter(":COMPLETED_ON", CheckNull(through), OracleType.DateTime, ParameterDirection.Input));
}
if (!String.IsNullOrEmpty(jobTypeFilter))
{
parameters.Add(OracleHelper.CreateParameter(":BATCH_JOB_TYPE", CheckNull(jobTypeFilter), OracleType.VarChar, ParameterDirection.Input));
}
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
batchJobs = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException orex)
{
throw orex;
}
catch (Exception ex)
{
throw ex;
}
return(batchJobs);
}
public override void Remove(ClientLocation client)
{
string sql = String.Format("DELETE FROM {0}.EXT_CLIENT WHERE EXT_SYS = :EXT_SYS and EXT_CLIENT_KEY = :EXT_CLIENT_KEY", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":EXT_SYS", client.ExtSys, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_CLIENT_KEY", client.ExtClientKey, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
public override void Remove(ExtWorkType entity)
{
string sql = String.Format("DELETE FROM {0}.ext_work_type WHERE EXT_SYS = :EXT_SYS and EXT_CLIENT_KEY = :EXT_CLIENT_KEY and EXT_WORK_TYPE = :EXT_WORK_TYPE", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":EXT_SYS", entity.EXT_SYS, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_CLIENT_KEY", entity.EXT_CLIENT_KEY, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":EXT_WORK_TYPE", entity.EXT_WORK_TYPE, OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
public override ObservableCollection <ContractNote> GetByContractID(int contractID)
{
string sql = String.Format("select contract_note_id, contract_id, added_dt, added_by, note \r\n"
+ "from {0}.contract_note \r\n"
+ "where contract_id = :contract_id"
, SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":contract_id", contractID, OracleType.Int32, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
return(ConvertDataTableToObservableCollection(dt));
}
public override void Add(ContractVolumeEvt r)
{
string sql = String.Format("insert into {0}.CONTRACT_VOLUME_EVT "
+ "("
+ " CONTRACT_ID"
+ ", CONTRACT_VOLUME_EVT_ID "
+ ", ENDS_AFTER"
+ ", STARTS_ON "
+ ", TALLY_STAT "
+ ", VOLUME_EVT_TYPE_ID "
+ " ) "
+ "values "
+ " ( "
+ " :CONTRACT_ID"
+ ", :CONTRACT_VOLUME_EVT_ID"
+ ", :ENDS_AFTER"
+ ", :STARTS_ON"
+ ", :TALLY_STAT"
+ ", :VOLUME_EVT_TYPE_ID "
+ " ) "
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":CONTRACT_ID", CheckNull(r.CONTRACT_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":CONTRACT_VOLUME_EVT_ID", CheckNull(r.CONTRACT_VOLUME_EVT_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ENDS_AFTER", CheckNull(r.ENDS_AFTER), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":STARTS_ON", CheckNull(r.STARTS_ON), OracleType.DateTime, ParameterDirection.Input));
if (r.TALLY_STAT == null)
{
parameters.Add(OracleHelper.CreateParameter(":TALLY_STAT", DBNull.Value, OracleType.Char, ParameterDirection.Input));
}
else
{
parameters.Add(OracleHelper.CreateParameter(":TALLY_STAT", (r.TALLY_STAT == true)?'Y':'N', OracleType.Char, ParameterDirection.Input));
}
parameters.Add(OracleHelper.CreateParameter(":VOLUME_EVT_TYPE_ID", CheckNull(r.VOLUME_EVT_TYPE_ID), OracleType.Number, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Add(VolumeEvtRate r)
{
string sql = String.Format("insert into {0}.VOLUME_EVT_RATE "
+ "("
+ " CONTRACT_VOLUME_EVT_ID"
+ ", TIER "
+ ", ADJ "
+ ", ADJ_INHOUSE "
+ ", ADJ_OFFSHORE "
+ ", ADJ_SR"
+ ", ADJ_SR_INHOUSE "
+ ", ADJ_SR_OFFSHORE "
+ " ) "
+ "values "
+ " ( "
+ " :CONTRACT_VOLUME_EVT_ID"
+ ", :TIER"
+ ", :ADJ"
+ ", :ADJ_INHOUSE"
+ ", :ADJ_OFFSHORE"
+ ", :ADJ_SR"
+ ", :ADJ_SR_INHOUSE "
+ ", :ADJ_SR_OFFSHORE "
+ " ) "
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":CONTRACT_VOLUME_EVT_ID", CheckNull(r.CONTRACT_VOLUME_EVT_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":TIER", CheckNull(r.TIER), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ", CheckNull(r.ADJ), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_INHOUSE", CheckNull(r.ADJ_INHOUSE), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_OFFSHORE", CheckNull(r.ADJ_OFFSHORE), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_SR", CheckNull(r.ADJ_SR), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_SR_INHOUSE", CheckNull(r.ADJ_SR_INHOUSE), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADJ_SR_OFFSHORE", CheckNull(r.ADJ_SR_OFFSHORE), OracleType.Number, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override ObservableCollection <ErrNoValidContract> GetContractErrors(DateTime asOf, bool showOnlyActiveIGs)
{
ObservableCollection <ErrNoValidContract> errNoValidContracts;
try
{
string sql = String.Format("Select "
+ " IG.INVOICE_GRP_ID "
+ " ,IG.DESCR "
+ " ,IGS.DESCR AS INVOICE_GRP_STATUS "
+ " ,P.DESCR AS PRIMARY_PLATFORM "
+ " ,F.DESCR AS BILLING_FREQ"
+ " FROM {0}.INVOICE_GRP IG "
+ " JOIN {0}.INVOICE_GRP_STATUS IGS "
+ " ON IGS.INVOICE_GRP_STATUS = IG.INVOICE_GRP_STATUS "
+ " JOIN {0}.PLATFORM P "
+ " ON P.PLATFORM = IG.PRIMARY_PLATFORM "
+ " JOIN {0}.FREQ F "
+ " ON IG.BILLING_FREQ = F.FREQ "
+ " WHERE INVOICE_GRP_ID NOT IN "
+ " (SELECT INVOICE_GRP_ID "
+ " FROM {0}.BASIC_CONTRACT"
+ " WHERE :I_AS_OF >= RATE_BEGIN_ON "
+ " AND :I_AS_OF <= RATE_END_AFTER "
+ " AND :I_AS_OF >= TAT_SCHED_BEGIN_ON "
+ " AND :I_AS_OF <= TAT_SCHED_END_AFTER) "
+ " AND IG.INVOICE_GRP_STATUS LIKE NVL(:I_INVOICE_GRP_STATUS, '%') ", SchemaName);
string status = "ACTV";
if (!showOnlyActiveIGs)
{
status = "";
}
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":I_AS_OF", CheckNull(asOf), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":I_INVOICE_GRP_STATUS", status, OracleType.Char, ParameterDirection.Input));
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
errNoValidContracts = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException ox)
{
throw ox;
}
catch (Exception x)
{
throw x;
}
return(errNoValidContracts);
}
public override void Remove(TatSched package)
{
try
{
string sql = String.Format("DELETE FROM {0}.TAT_SCHED WHERE TAT_SCHED_ID = :TAT_SCHED_ID", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":TAT_SCHED_ID", package.TAT_SCHED_ID, OracleType.Int32, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
public override void Add(ContractTatSched r)
{
string sql = String.Format("insert into {0}.CONTRACT_TAT_SCHED "
+ "("
+ " CONTRACT_ID"
+ ", TAT_SCHED_ID "
+ ", BEGIN_ON"
+ ", END_AFTER "
+ ", AUTO_CHG "
+ " ) "
+ "values "
+ " ( "
+ " :CONTRACT_ID"
+ ", :TAT_SCHED_ID"
+ ", :BEGIN_ON"
+ ", :END_AFTER "
+ ", :AUTO_CHG "
+ " ) "
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":CONTRACT_ID", CheckNull(r.CONTRACT_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":TAT_SCHED_ID", CheckNull(r.TAT_SCHED_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":BEGIN_ON", CheckNull(r.BEGIN_ON), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":END_AFTER", CheckNull(r.END_AFTER), OracleType.DateTime, ParameterDirection.Input));
if (r.AUTO_CHG != null)
{
parameters.Add(OracleHelper.CreateParameter(":AUTO_CHG", (r.AUTO_CHG)?'Y':'N', OracleType.Char, ParameterDirection.Input));
}
else
{
parameters.Add(OracleHelper.CreateParameter(":AUTO_CHG", DBNull.Value, OracleType.Char, ParameterDirection.Input));
}
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.bWasAdded = r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
public override void Delete(ReportTypePackage package)
{
try
{
string sql = String.Format("DELETE FROM {0}.REPORT_TYPE WHERE REPORT_TYPE_ID = :REPORT_TYPE_ID", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":REPORT_TYPE_ID", package.Id, OracleType.Int32, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
public override void Remove(InvoiceGrpReport entity)
{
try
{
string sql = String.Format("DELETE FROM {0}.invoice_grp_report WHERE REPORT_TYPE_ID = :REPORT_TYPE_ID and INVOICE_GRP_ID = :INVOICE_GRP_ID", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":REPORT_TYPE_ID", entity.REPORT_TYPE_ID, OracleType.Int32, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":INVOICE_GRP_ID", entity.INVOICE_GRP_ID, OracleType.Int32, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
/// <summary>
/// COST_CODE VARCHAR
/// DEFAULT_INVOICE_GRP_ID NUMBER
/// DEFAULT_TAT_THRESHOLD NUMBER
/// DESCR VARCHAR
/// EXT_WORK_TYPE_SOURCE_DESCR VARCHAR
/// OVERRIDE_KEY_SOURCE VARCHAR
/// </summary>
/// <param name="loc"></param>
/// <returns></returns>
public override ClientLocation Update(ClientLocation loc)
{
try
{
string sql = String.Format("update {0}.EXT_CLIENT set "
+ " DEFAULT_INVOICE_GRP_ID = :id "
+ " ,DEFAULT_TAT_THRESHOLD = :default_tat_threshold "
+ " ,COST_CODE = :cost_code "
+ " ,DESCR = :descr "
+ " ,EXT_WORK_TYPE_SOURCE_DESCR = :ext_work_type_source_descr "
+ " ,OVERRIDE_KEY_SOURCE = :override_key_source "
+ " where ext_sys = :ext_sys and ext_client_key = :extclientkey"
, SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":id", loc.DefaultInvoiceGrpID, OracleType.Number, ParameterDirection.Input));
object v;
if (loc.DefaultTatThreshold == null)
{
v = DBNull.Value;
}
else
{
v = loc.DefaultTatThreshold;
}
parameters.Add(OracleHelper.CreateParameter(":default_tat_threshold", NullableInput(loc.DefaultTatThreshold), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":cost_code", NullableInput(loc.CostCode), OracleType.VarChar, 10, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":descr", NullableInput(loc.Description), OracleType.VarChar, 100, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ext_work_type_source_descr", NullableInput(loc.ExtWorkTypeSourceDescr), OracleType.VarChar, 100, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":override_key_source", NullableInput(loc.OverrideKeySource), OracleType.VarChar, 30, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ext_sys", loc.ExtSys, OracleType.VarChar, 20, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":extclientkey", loc.ExtClientKey, OracleType.VarChar, 20, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
return(loc);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
public override void Remove(BatchJobType package)
{
string sql;
try
{
sql = String.Format("DELETE FROM {0}.BATCH_JOB_TYPE WHERE BATCH_JOB_TYPE = :BATCH_JOB_TYPE", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":BATCH_JOB_TYPE", package.BATCH_JOB_TYPE, OracleType.VarChar, ParameterDirection.Input));
OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
}
}
public override List <InvoiceGroupReportsInfoQueryResult> Get(int invoiceGroupID)
{
string sql = String.Format("select igr.invoice_grp_id ,igr.report_type_id ,igr.tab_order \r\n"
+ " ,rt.short_name ,igr.file_type ,rt.data_dump_only \r\n"
+ "from {0}.invoice_grp_report igr \r\n"
+ "join {0}.report_type rt \r\n"
+ " on rt.report_type_id = igr.report_type_id \r\n"
+ "where igr.invoice_grp_id = :invoiceGroupID \r\n"
+ "order by igr.tab_order"
, base.SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":invoiceGroupID", invoiceGroupID, OracleType.Number, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
return(base.ConvertDataTableToList(dt));
}
public override void Remove(ContractRateAlt package)
{
try
{
string sql = String.Format("DELETE FROM {0}.CONTRACT_RATE_ALT WHERE CONTRACT_RATE_ID = :CONTRACT_RATE_ID and RATE_NBR = :RATE_NBR", SchemaName);
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":CONTRACT_RATE_ID", package.CONTRACT_RATE_ID, OracleType.Int32, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":RATE_NBR", package.RATE_NBR, OracleType.Int32, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
}
catch (OracleException ex)
{
throw ex;
//throw ex;
}
}
public override void Add(ReportTypePackage package)
{
// DESCR VARCHAR2
// REPORT_TYPE_ID INTEGER
// SHORT_NAME VARCHAR2
// SQL VARCHAR2
// ALLOW_DYNAMIC_MODS CHAR1
// DATA_DUMP_ONLY CHAR1
// MUST_ENCRYPT CHAR1
string sql = String.Format("insert into {0}.REPORT_TYPE "
+ " (DESCR"
+ " ,REPORT_TYPE_ID"
+ " ,SHORT_NAME"
+ " ,SQL"
+ " ,ALLOW_DYNAMIC_MODS"
+ " ,DATA_DUMP_ONLY"
+ " ,MUST_ENCRYPT) "
+ "values "
+ " (:DESCR"
+ " ,{0}.REPORT_TYPE_ID.nextval"
+ " ,:SHORT_NAME"
+ " ,:SQL"
+ " ,:ALLOW_DYNAMIC_MODS"
+ " ,:DATA_DUMP_ONLY"
+ " ,:MUST_ENCRYPT)"
+ " returning REPORT_TYPE_ID into :NEWID"
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":DESCR", package.Description, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SHORT_NAME", package.ShortName, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":SQL", package.Sql, OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ALLOW_DYNAMIC_MODS", (package.AllowDynamicMod) ? 'Y' : 'N', OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":DATA_DUMP_ONLY", (package.DataDumpOnly) ? 'Y' : 'N', OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":MUST_ENCRYPT", (package.MustEncrypt) ? 'Y' : 'N', OracleType.Char, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":NEWID", OracleType.Int32, ParameterDirection.InputOutput));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
package.Id = int.Parse(parameters[parameters.Count - 1].Value.ToString());
}
catch (OracleException ex)
{
throw ex;
}
}