/// <summary>
/// This method retrieves workorder's info based on the supplied parameters.
/// </summary>
/// <param name="siteId">Site id</param>
/// <param name="siteCode">Site code</param>
/// <param name="workOrderNumber">Work order number</param>
/// <returns>DetailedWorkOrderSchema.WorkOrderRow</returns>
public DetailedWorkOrderSchema.WorkOrderRow GetWorkOrderInfo(int siteId, string siteCode, string workOrderNumber)
{
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch { throw new LogonException(); }
// create the sql statement
StringBuilder sql = new StringBuilder();
sql.Append("select wo.work_order_number, wo.account_number, wo.date_entered, wo.salesman_number, ");
sql.Append("wo.wo_comment_line_01,wo.wo_comment_line_02, wo.wo_comment_line_03, wo.sales_reason, wo.q_code, wo.problem_code_01, ");
sql.Append("wo.problem_code_02, wo.problem_code_03, wo.problem_code_04, wo.problem_code_05, wo.schedule_date, wo.time_slot, wo.stage_code, ");
sql.Append("wo.campaign_code, cm.first_name, cm.last_name, cm.monthly_amount, hm.addr_location, hm.street, hm.addr_city, ");
sql.Append("hm.addr_state, hm.addr_zip_5, hm.apartment, hm.dwelling_type, hm.signal_access_code, hm.unserviceable_address, hm.replace_drop_date ");
sql.AppendFormat("from {0}_work_order_master wo, {1}_customer_master cm, {2}_house_master hm ", siteCode, siteCode, siteCode);
sql.Append("where wo.account_number = cm.account_number and cm.house_number = hm.house_number and wo.work_order_number = ");
sql.AppendFormat(workOrderNumber);
sql.Append(" and wo.site_id = ");
sql.AppendFormat(siteId.ToString());
// build the command object
using (OracleCommand cmd = new OracleCommand(sql.ToString(), oracleConn))
{
cmd.CommandType = CommandType.Text;
// build the dataadapter
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
DetailedWorkOrderSchema ds = new DetailedWorkOrderSchema();
// now fill it
da.Fill(ds.WorkOrder);
// all done, return
return(ds.WorkOrder.Count == 1 ? ds.WorkOrder[0] : null);
}
}
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
}
/// <summary>
/// This method retrieves workorder's detail info based on the supplied parameters
/// from Oracle Phastage ICOMS 7.1
/// </summary>
/// <param name="siteId">Site id</param>
/// <param name="siteCode">Site code</param>
/// <param name="workOrderNumber">Work order number</param>
/// <returns>DetailedWorkOrderSchema.WorkOrderDetailDataTable</returns>
public DetailedWorkOrderSchema.WorkOrderDetailDataTable GetWorkOrderDetailsFromIcoms7Dot1(int siteId, string siteCode, string workOrderNumber)
{
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try { oracleConn.Open(); }
catch { throw new LogonException(); }
// create the sql statement
StringBuilder sql = new StringBuilder();
sql.Append("select distinct wd.work_order_number, wd.service_code, sc.short_description, sc.long_description, wd.category, ");
sql.Append("wd.old_rate, wd.new_rate, wd.work_order_occurrence, wd.from_quantity, wd.to_quantity, wddt.iybic1 as campaign_code, wd.employee_number ");
sql.AppendFormat("from {0}_work_order_detail wd join {0}_CF_Service_Codes sc on (wd.Service_code = sc.Service_code and wd.Site_id = sc.Site_id) ", siteCode);
sql.AppendFormat("left join {0}_cgiycpp wddt ", siteCode);
sql.Append("on (wd.site_id = wddt.iynrov and wd.work_order_number = wddt.iywonr and wd.account_number = wddt.iycnbr and wd.service_code = wddt.iysvcd ");
sql.Append("and wd.service_category_code = wddt.iycek6 and wddt.iycama = 'Y' and wddt.iybrcn = 'C')");
sql.Append("where wd.service_code = sc.service_code and wd.site_id = sc.site_id ");
sql.AppendFormat("and wd.work_order_number = {0} and wd.site_id = {1} ", workOrderNumber, siteId);
sql.AppendFormat("order by wd.Service_code, wd.Work_order_occurrence");
// build the command object
using (OracleCommand cmd = new OracleCommand(sql.ToString(), oracleConn))
{
cmd.CommandType = CommandType.Text;
// build the dataadapter
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
// create the dataset to fill
DetailedWorkOrderSchema ds = new DetailedWorkOrderSchema();
// now fill it
da.Fill(ds.WorkOrderDetail);
// all done, return
return(ds.WorkOrderDetail);
}
}
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
}
