protected override void RowConverter(InvoiceStyleColumn item, DataRow row)
{
item.InvoiceStyle = row["invoice_style"].ToString();
item.ID = int.Parse(row["column_id"].ToString());
if (!String.IsNullOrEmpty(row["column_index"].ToString()))
{
item.Index = int.Parse(row["column_index"].ToString());
}
item.Name = row["column_name"].ToString();
item.SqlColumn = row["sql_column_name"].ToString();
if (!String.IsNullOrEmpty(row["grouping_index"].ToString()))
{
item.GroupingIndex = int.Parse(row["grouping_index"].ToString());
}
if (!String.IsNullOrEmpty(row["sort_index"].ToString()))
{
item.SortIndex = int.Parse(row["sort_index"].ToString());
}
item.Width = row["width"].ToString();
}
public override void Remove(InvoiceStyleColumn item)
{
throw new NotImplementedException();
}
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));
}
