public override ObservableCollection <InvoiceGrpStatus> GetInvoiceGrpStatuss()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <InvoiceGrpStatus> remits = null;
try
{
string sql = string.Format("select * from sphrsbilling.INVOICE_GRP_STATUS");
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
remits = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(remits);
}
public override ObservableCollection <DeliveryMethod> GetDeliveryMethods()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <DeliveryMethod> deliverys = new ObservableCollection <DeliveryMethod>();
try
{
string sql = "select * from sphrsbilling.delivery_method";
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
DeliveryMethod item = new DeliveryMethod();
item.TheDeliveryMethod = row["DELIVERY_METHOD"].ToString();
item.Descr = row["DESCR"].ToString();
deliverys.Add(item);
}
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(deliverys);
}
public override ReportTypeTable MakeReportTable()
{
string sql = String.Format("select DESCR," +
"REPORT_TYPE_ID," +
"SHORT_NAME," +
"SQL," +
"ALLOW_DYNAMIC_MODS," +
"DATA_DUMP_ONLY," +
"MUST_ENCRYPT " +
"from SPHRSBILLING.REPORT_TYPE");
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
ObservableCollection <ContractRateAlt> rpt = ConvertDataTableToObservableCollection(dt);
ReportTypeTable rtt = new ReportTypeTable();
rtt.AvailableFields = GetAvailableFields();
foreach (ContractRateAlt package in rpt)
{
package.Table = rtt;
package.AvailableFields = rtt.AvailableFields;
rtt.Add(package);
}
return(rtt);
}
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 ObservableCollection <ClientLocation> FetchLocations(int invoiceId, bool exclusive)
{
ObservableCollection <ClientLocation> locations = null;
try
{
string sql;
if (exclusive)
{
sql = string.Format("select * from {0}.EXT_CLIENT\r\n"
+ " WHERE default_invoice_grp_id={1} ORDER BY DESCR", base.SchemaName, invoiceId);
}
else
{
sql = string.Format("select * from {0}.EXT_CLIENT\r\n"
+ " WHERE default_invoice_grp_id IS NULL ORDER BY DESCR", base.SchemaName, invoiceId);
}
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
locations = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(locations);
//return null;
}
public override ObservableCollection <Brand> GetBrands()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <Brand> brands = new ObservableCollection <Brand>();
try
{
const string sql = "select * from sphrsbilling.Brand";
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
Brand item = new Brand
{
BRAND = row["BRAND"].ToString(),
DESCR = row["DESCR"].ToString(),
GRAPHIC_FILENAME = row["GRAPHIC_FILENAME"].ToString()
};
brands.Add(item);
}
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(brands);
}
public override ObservableCollection <AddOnChgType> GetAddOnChgTypes()
{
ObservableCollection <AddOnChgType> AddOnChgTypes;
#if !ORACLE
return(null);
#endif
try
{
string sql = String.Format("Select * from {0}.ADD_ON_CHG_TYPE ORDER BY DESCR", SchemaName);
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
AddOnChgTypes = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException orex)
{
throw orex;
}
catch (Exception ex)
{
throw ex;
}
return(AddOnChgTypes);
}
public override ObservableCollection <PaymentTerm> GetAllPaymentTerms()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <PaymentTerm> PaymentTerms = new ObservableCollection <PaymentTerm>();
try
{
const string sql = "select * from sphrsbilling.Payment_Terms";
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
PaymentTerm item = new PaymentTerm
{
PAYMENT_TERMS = row["PAYMENT_TERMS"].ToString(),
DESCR = row["DESCR"].ToString(),
};
PaymentTerms.Add(item);
}
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(PaymentTerms);
}
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 ObservableCollection <Freq> GetFreqs()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <Freq> freqs = new ObservableCollection <Freq>();
try
{
const string sql = "select * from sphrsbilling.Freq";
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
Freq item = new Freq
{
FREQ = row["FREQ"].ToString(),
DESCR = row["DESCR"].ToString(),
RECURRANCE_RULE = row["RECURRANCE_RULE"].ToString(),
VALID_FOR_ADD_ON_CHG = (row["VALID_FOR_ADD_ON_CHG"].ToString() == "Y") ? true : false
};
freqs.Add(item);
}
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(freqs);
}
public override ObservableCollection <ActiveSchedule> FetchActiveSchedules(decimal invoiceGrpId)
{
#if !ORACLE
return(null);
#endif
ObservableCollection <ActiveSchedule> ActiveSchedules;
try
{
string sql = String.Format("select AO.DESCR as TYPE,A.INVOICE_ID,NVL(A.FOR_PERIOD_BEGINNING,I.BILL_PERIOD_START ) "
+ "as BEGINS_ON from {0}.ADD_ON_CHG A "
+ "JOIN {0}.INVOICE I "
+ " ON I.INVOICE_ID=A.INVOICE_ID "
+ " JOIN {0}.ADD_ON_CHG_TYPE AO "
+ " ON A.ADD_ON_CHG_TYPE_ID=AO.ADD_ON_CHG_TYPE_ID "
+ " WHERE I.INVOICE_GRP_ID={1}", SchemaName, invoiceGrpId);
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
ActiveSchedules = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException orex)
{
throw orex;
}
catch (Exception ex)
{
throw ex;
}
return(ActiveSchedules);
}
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 <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 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 ObservableCollection <string> GetAvailableFields()
{
string sql = "Select cols.column_name as Name " + "FROM " + "ALL_TAB_COLUMNS cols " + "where 1=1 " + "and cols.table_name = 'CSV_DETAILS' " + "and cols.owner = 'SPHRSBILLING' " + "order by Name ";
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
ObservableCollection <string> fields = new ObservableCollection <string>();
foreach (DataRow row in dt.Rows)
{
fields.Add(row["Name"].ToString());
}
return(fields);
}
public override int CountInvoices(decimal grpId)
{
string sql = String.Format("select count(*) as COUNTOF from {0}.INVOICE where invoice_grp_id={1}", SchemaName, grpId);
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
if (dt.Rows.Count > 0)
{
return(CastDbValueRow(dt.Rows[0], "COUNTOF", true, "Int32"));
}
return(0);
}
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 InvoiceGrpReport GetByIds(int reportId, int groupId)
{
string sql = String.Format("select * from SPHRSBILLING.invoice_grp_report where INVOICE_GRP_ID={0} and REPORT_TYPE_ID={1}", reportId, groupId);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
if (dt.Rows.Count > 0)
{
InvoiceGrpReport igr = new InvoiceGrpReport();
RowConverter(igr, dt.Rows[0]);
return(igr);
}
return(null);
}
public override InvoiceGroup Get(InvoiceGroup item)
{
string sql = String.Format("select * from {0}.INVOICE_GRP where INVOICE_GRP_ID={1}", SchemaName, item.InvoiceGrpId);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
if (dt.Rows.Count > 0)
{
RowConverter(item, dt.Rows[0]);
return(item);
}
else
{
throw new RowNotInTableException("Row not found.");
}
}
public override ObservableCollection <ExtSys> GetExtSys()
{
ObservableCollection <ExtSys> extSys;
string sql = String.Format("SELECT * from {0}.Ext_sys", SchemaName);
OracleParameter[] p = null;
if (base.ConnectionString == null)
{
return(null);
}
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
extSys = ConvertDataTableToObservableCollection(dt);
return(extSys);
}
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 ObservableCollection <ClientLocation> FetchClients(string clientFilter, bool notAssignedToGroup = false)
{
ObservableCollection <ClientLocation> clients = null;
try
{
string where = string.Empty;
string descWhere = string.Empty;
if (!string.IsNullOrEmpty(clientFilter))
{
descWhere = String.Format(" UPPER (DESCR) LIKE '%{0}%'", clientFilter.ToUpper());
descWhere = descWhere.Insert(0, "(");
descWhere += ")";
where = Where(where, descWhere);
}
if (notAssignedToGroup)
{
where = Where(where, " default_invoice_grp_id IS NULL");
}
if (where == null)
{
return(null);
}
string sql = string.Format("select * from {0}.ext_client\r\n", SchemaName);
if (!string.IsNullOrEmpty(where))
{
sql += where;
}
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
clients = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(clients);
//return null;
}
public override ObservableCollection <ExtWorkType> GetWorkTypes(string extsys, string extClientKey = null)
{
string sql = String.Format("select ext_sys ,ext_client_key ,ext_work_type \r\n"
+ " ,descr ,tat_threshold \r\n"
+ " ,std_work_type ,invoice_grp_id ,stat_tat_threshold \r\n"
+ " ,rate_nbr ,tally_for_volume_evt ,platform_wt_id \r\n"
+ "from {0}.ext_work_type \r\n"
+ "where ext_sys = '{1}' \r\n"
+ ((extClientKey == null) ? string.Empty : "and ext_client_key='{2}' ")
+ "order by ext_sys ,ext_client_key ,ext_work_type"
, base.SchemaName, extsys, extClientKey);
List <OracleParameter> parameters = new List <OracleParameter>();
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
return(ConvertDataTableToObservableCollection(dt));
}
public override ObservableCollection <OverRideKeySource> GetOverRides()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <OverRideKeySource> overrides = new ObservableCollection <OverRideKeySource>();
string sql = String.Format("Select * from {0}.OVERRIDE_KEY_SOURCE", SchemaName);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
OverRideKeySource item = new OverRideKeySource
{
OVERRIDE_KEY_SOURCE = row["OVERRIDE_KEY_SOURCE"].ToString()
};
overrides.Add(item);
}
return(overrides);
}
public override ContractNote Get(ContractNote item)
{
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_note_id = :contract_note_id"
, SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":contract_note_id", item.ID, OracleType.Int32, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
if (dt.Rows.Count > 0)
{
return(ConvertDataTableToList(dt)[0]);
}
else
{
throw new RowNotInTableException("The note was not found.");
}
}
public override DataTable Get(int reportTypeID, int invoiceID)
{
// DO NOT add "\r\n" newlines to this SQL. Oracle will not compile.
string sql = String.Format(
"declare "
+ " fileTypeSql varchar2(4000);"
+ "begin "
+ " select sql into fileTypeSql from {0}.report_type where report_type_id = :reportTypeID; "
+ " open :workUnits for fileTypeSql using :invoiceID; "
+ "end;"
, base.SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":reportTypeID", reportTypeID, OracleType.Number, ParameterDirection.Input)
, OracleHelper.CreateParameter(":invoiceID", invoiceID, OracleType.Number, 22, ParameterDirection.Input)
, OracleHelper.CreateParameter(":workUnits", null, OracleType.Cursor, ParameterDirection.Output) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p, 300);
return(dt);
}
public override ObservableCollection <ExtWorkTypeSource> GetWorkTypeSources()
{
#if !ORACLE
return(null);
#endif
ObservableCollection <ExtWorkTypeSource> workTypes = new ObservableCollection <ExtWorkTypeSource>();
string sql = String.Format("Select * from {0}.EXT_WORK_TYPE_SOURCE", SchemaName);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
foreach (DataRow row in dt.Rows)
{
ExtWorkTypeSource item = new ExtWorkTypeSource
{
PLATFORM = row["PLATFORM"].ToString(),
DESCR = row["DESCR"].ToString()
};
workTypes.Add(item);
}
return(workTypes);
}
public override ObservableCollection <InvoiceGrpReport> GetByIdXLSOnly(int grpId)
{
ObservableCollection <InvoiceGrpReport> InvoiceGrpReports = new ObservableCollection <InvoiceGrpReport>();
try
{
string sql = string.Format("select * from sphrsbilling.invoice_grp_report where invoice_grp_id={0} and file_type='XLS3'", grpId);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
InvoiceGrpReports = ConvertDataTableToObservableCollection(dt);
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception sysEx)
{
throw sysEx;
}
return(InvoiceGrpReports);
}
public override ObservableCollection <ContractsUsingTatSched> GetContractsUsing(decimal tatSchedId)
{
string sql = String.Format("select DISTINCT TC.CONTRACT_ID,C.DESCR from {0}.TODAYS_CONTRACT TC JOIN {0}.CONTRACT C ON C.CONTRACT_ID=TC.CONTRACT_ID WHERE TAT_SCHED_ID={1}", SchemaName, tatSchedId);
OracleParameter[] p = null;
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
ObservableCollection <ContractsUsingTatSched> contractList = new ObservableCollection <ContractsUsingTatSched>();
foreach (DataRow row in dt.Rows)
{
ContractsUsingTatSched item = new ContractsUsingTatSched();
item.CONTRACT_ID = CastDbValueRow(row, "CONTRACT_ID", true, "decimal");
item.DESCR = CastDbValueRow(row, "DESCR");
contractList.Add(item);
}
return(contractList);
}
public override List <InvoiceReportAddOnCharge> Get(int invoiceID)
{
string sql = String.Format("select aoc.comments_for_billprint ,aoc.qty ,aoc.amt_each \r\n"
+ " ,aoct.descr ,aoc.add_on_chg_type_id \r\n"
+ "from {0}.add_on_chg aoc \r\n"
+ "join {0}.add_on_chg_type aoct \r\n"
+ " on aoct.add_on_chg_type_id = aoc.add_on_chg_type_id \r\n"
+ "where invoice_id = :invoice_id \r\n"
+ " and aoc.add_on_chg_type_id <> 14" //TAT reduction this invoice
, base.SchemaName);
OracleParameter[] p = { OracleHelper.CreateParameter(":invoice_id", invoiceID, OracleType.Int32, ParameterDirection.Input) };
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, p);
//dt.TableName = "InvoiceReportAddOnCharges";
//dt.WriteXml("InvoiceReportAddOnCharges.xml"); // TODO: Remove after testing.
//DataTable dt = new DataTable();
//dt.ReadXml(@"C:\Documents and Settings\ilee\My Documents\PoC Projects\Dynamic Subreporting\Dynamic Subreporting\InvoiceReportAddOnCharges.xml");
return(ConvertDataTableToList(dt));
}