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