public override Dictionary <string, string> Get(int invoiceID)
{
string sql = String.Format("select i.created_on as \"InvoiceDate\" "
+ " ,ig.descr as \"Location\" "
+ " ,ig.invoice_grp_id || '-' || i.invoice_id as \"InvoiceNumber\" "
+ " ,pt.descr as \"PaymentTerms\" "
+ " ,ig.attn_line_heading as \"AttnLineHeading\" "
+ " ,ig.attn_line as \"AttnLine\" "
+ " ,rt.line1 as \"RemitToLine1\" "
+ " ,rt.line2 as \"RemitToLine2\" "
+ " ,rt.line3 as \"RemitToLine3\" "
+ " ,rt.line4 as \"RemitToLine4\" "
+ " ,i.bill_period_start as \"InvoiceBeginDate\" "
+ " ,i.bill_period_end_before as \"InvoiceEndDate\" "
+ " ,bs.display_title as \"BillSpecialistTitle\" "
+ " ,bs.email as \"BillSpecialistEmail\" "
+ " ,bs.name as \"BillSpecialistName\" "
+ " ,bs.phone as \"BillSpecialistPhone\" "
+ " ,ig.invoice_style as \"InvoiceStyle\" "
+ " ,ccm.invoice_column_header as \"UnitsTitle\" "
+ " ,ig.bw_invoice_style as \"IsBWInvoice\" "
+ " ,ccm.invoice_column_header as \"UnitsColumn\" "
+ " ,ig.invoice_grp_id as \"InvoiceGroupID\" "
+ " ,ig.descr as \"InvoiceGroupDescription\" "
+ " ,ig.default_bill_file_path as \"DefaultFilePath\" "
+ " ,case when tcm.show_tat_column_on_invoice = 'N' or cts.auto_chg = 'N' then 0 else 1 end \"ShowTatColumn\" "
+ " ,ist.engine_version as \"EngineVersion\" "
+ "from {0}.invoice i "
+ "join {0}.invoice_grp ig "
+ " on ig.invoice_grp_id = i.invoice_grp_id "
+ "join {0}.invoice_style ist "
+ " on ist.invoice_style = ig.invoice_style "
+ "join {0}.contract c "
+ " on c.contract_id = ig.contract_id "
+ "join {0}.payment_terms pt "
+ " on pt.payment_terms = c.payment_terms "
+ "join {0}.remit_to rt "
+ " on rt.remit_to_id = ig.remit_to_id "
+ "join {0}.bill_specialist bs "
+ " on bs.bill_specialist_id = ig.bill_specialist_id "
+ "join {0}.char_comp_method ccm "
+ " on ccm.char_comp_method = i.char_comp_method "
+ "join {0}.contract_tat_sched cts "
+ " on cts.contract_id = c.contract_id "
+ " and cts.end_after >= i.bill_period_start "
+ " and cts.begin_on <= i.bill_period_start "
+ "join {0}.tat_sched ts "
+ " on ts.tat_sched_id = cts.tat_sched_id "
+ "join {0}.tat_comp_method tcm "
+ " on tcm.tat_comp_method = ts.tat_comp_method "
+ "where i.invoice_id = :invoice_id "
, base.SchemaName);
OracleParameter[] parameters = { OracleHelper.CreateParameter(":invoice_id", invoiceID, System.Data.OracleClient.OracleType.Number, System.Data.ParameterDirection.Input) };
DataTable t = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, parameters);
Debug.Assert(t.Rows.Count == 1, "There should only be one row returned by this query.");
if (t.Rows.Count == 0)
{
throw new InvoiceNotFoundException(invoiceID);
}
Dictionary <string, string> rptParams = DataHelper.ConvertRowToDictionary(t.Rows[0], t.Columns);
// Add invoice column names.
OracleInvoiceStyleColumnRepository rep = new OracleInvoiceStyleColumnRepository();
List <InvoiceStyleColumn> cols = rep.GetByInvoiceID(invoiceID);
foreach (InvoiceStyleColumn col in cols.Where(c => c.Index > 0))
{
int ndx = col.Index;
rptParams.Add(String.Format("Column{0}Name", ndx), col.Name);
}
return(rptParams);
}
public override List <InvoiceReportTranscriptionLineItem> Get(int invoiceID)
{
// Get invoice columns.
List <InvoiceStyleColumn> columns;
using (OracleInvoiceStyleColumnRepository rep = new OracleInvoiceStyleColumnRepository())
{
columns = rep.GetByInvoiceID(invoiceID);
}
InvoiceStyleColumn defCol = new InvoiceStyleColumn();
// Build SQL.
string groupingSql = "";
if (GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.GroupingIndex == 1)) != "''")
{
groupingSql = String.Format("group by {0}, {1}, {2}"
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.GroupingIndex == 1))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.GroupingIndex == 2))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.GroupingIndex == 3)));
}
string sortSql = "";
if (GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.SortIndex == 1)) != "''")
{
sortSql = String.Format("order by {0}, {1}, {2}"
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.SortIndex == 1))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.SortIndex == 2))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.SortIndex == 3)));
}
string sql = String.Format("SELECT {3} as column1 \r\n"
+ " ,{4} as column2 \r\n"
+ " ,{5} as column3 \r\n"
+ " ,COUNT(*) AS JOB_COUNT \r\n"
+ " ,SUM(WU.TAT_REDUCTION_CHARGED) AS TAT_REDUCTION_CHARGED \r\n"
+ " ,SUM(WU.QTY_BILLABLE) AS QTY_BILLABLE \r\n"
+ " ,sum(wu.raw_cost) as subtotal \r\n"
+ " ,sum(wu.raw_cost - wu.tat_reduction_charged) as total \r\n"
+ " ,{2} AS GROUPING_COLUMN \r\n"
+ "FROM {0}.WORK_UNIT WU \r\n"
+ "JOIN {0}.EXT_CLIENT EC \r\n"
+ " ON EC.EXT_SYS = WU.EXT_SYS \r\n"
+ " AND EC.EXT_CLIENT_KEY = WU.EXT_CLIENT_KEY \r\n"
+ "JOIN {0}.EXT_WORK_TYPE EWT \r\n"
+ " ON EWT.EXT_SYS = WU.EXT_SYS \r\n"
+ " AND EWT.EXT_CLIENT_KEY = WU.EXT_CLIENT_KEY \r\n"
+ " AND EWT.EXT_WORK_TYPE = WU.EXT_WORK_TYPE \r\n"
+ "WHERE WU.INVOICE_ID = {1} \r\n"
+ "{6} \r\n"
+ "{7}"
, base.SchemaName
, invoiceID
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.GroupingIndex == 1 && c.Index == 0))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.Index == 1))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.Index == 2))
, GetColumnProperty(columns, (a => a.SqlColumn.ToString()), (c => c.Index == 3))
, groupingSql
, sortSql);
DataTable dt = OracleHelper.ExecuteQuery(base.ConnectionString.Value, sql, null);
//dt.TableName = "InvoiceReportTranscriptionLines";
//dt.WriteXml("InvoiceReportTranscriptionLines.xml");
//DataTable dt = new DataTable();
//dt.ReadXml(@"C:\Documents and Settings\ilee\My Documents\PoC Projects\Dynamic Subreporting\Dynamic Subreporting\InvoiceReportTranscriptionLines.xml");
return(base.ConvertDataTableToList(dt));
}
