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)); }