public override void Add(AddOnChgSched r)
{
string sql = String.Format("insert into {0}.ADD_ON_CHG_SCHED "
+ "("
+ " ADD_ON_CHG_SCHED_ID"
+ ", ADD_ON_CHG_TYPE_ID "
+ ", ADDED_BY "
+ ", AMT_EACH "
+ ", COMMENTS_FOR_BILLPRINT "
+ ", STARTS_ON "
+ ", ENDS_AFTER "
+ ", INVOICE_GRP_ID"
+ ", NOTES "
+ ", QTY "
+ ", QTY_RULE "
+ ", FREQ "
+ " ) "
+ "values "
+ " ( "
+ " {0}.ADD_ON_CHG_SCHED_ID.nextval"
+ ", :ADD_ON_CHG_TYPE_ID"
+ ", :ADDED_BY"
+ ", :AMT_EACH"
+ ", :COMMENTS_FOR_BILLPRINT"
+ ", :STARTS_ON "
+ ", :ENDS_AFTER"
+ ", :INVOICE_GRP_ID"
+ ", :NOTES "
+ ", :QTY "
+ ", :QTY_RULE "
+ ", :FREQ "
+ " ) "
+ "returning ADD_ON_CHG_SCHED_ID into :ADD_ON_CHG_SCHED_ID"
, SchemaName);
try
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(OracleHelper.CreateParameter(":ADD_ON_CHG_SCHED_ID", OracleType.Number, ParameterDirection.InputOutput));
parameters.Add(OracleHelper.CreateParameter(":ADD_ON_CHG_TYPE_ID", CheckNull(r.ADD_ON_CHG_TYPE_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ADDED_BY", CheckNull(r.ADDED_BY), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":AMT_EACH", CheckNull(r.AMT_EACH), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":COMMENTS_FOR_BILLPRINT", CheckNull(r.COMMENTS_FOR_BILLPRINT), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":STARTS_ON", CheckNull(r.STARTS_ON), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":ENDS_AFTER", CheckNull(r.ENDS_AFTER), OracleType.DateTime, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":FREQ", CheckNull(r.FREQ), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":INVOICE_GRP_ID", CheckNull(r.INVOICE_GRP_ID), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":NOTES", CheckNull(r.NOTES), OracleType.VarChar, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":QTY", CheckNull(r.QTY), OracleType.Number, ParameterDirection.Input));
parameters.Add(OracleHelper.CreateParameter(":QTY_RULE", CheckNull(r.QTY_RULE), OracleType.VarChar, ParameterDirection.Input));
OracleParameterCollection outParams = OracleHelper.ExecuteNonQuery(base.ConnectionString.Value, sql, parameters.ToArray <OracleParameter>());
r.ADD_ON_CHG_SCHED_ID = int.Parse(parameters[0].Value.ToString());
r.Modified = false;
}
catch (OracleException ex)
{
throw ex;
}
catch (Exception x)
{
throw x;
}
}
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));
}