public IEnumerable <RequestStatusChange> Select(List <int> ids)
{
var requests = new List <RequestStatusChange>();
foreach (var item in ids)
{
int id = item;
using (OracleConnection connection = new OracleConnection(OracleWcfService.CONNECTION_STRING))
{
connection.Open();
OracleCommand oraCommand = new OracleCommand("SELECT * "
+ "FROM system.REQUEST_STATUS_CHANGE WHERE REQUEST_ID = :id", connection);
oraCommand.Parameters.Add("id", id);
OracleDataReader oraReader = oraCommand.ExecuteReader();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
var temp = new RequestStatusChange();
temp.id = oraReader.GetInt32(0);
temp.old_status = null;
temp.old_status = oraReader.GetValue(1) == DBNull.Value ? temp.old_status = null : (int)oraReader.GetInt32(1);
temp.new_status = oraReader.GetValue(2) == DBNull.Value ? temp.new_status = null : (int)oraReader.GetInt32(2);
temp.change_date = oraReader.GetDateTime(3);
temp.request_id = oraReader.GetInt32(4);
requests.Add(temp);
}
}
}
}
return(requests);
}
public RequestStatusChange SelectLastStatus(int id) //конкретная заявка
{
var request = new RequestStatusChange();
using (OracleConnection connection = new OracleConnection(OracleWcfService.CONNECTION_STRING))
{
connection.Open();
OracleCommand oraCommand = new OracleCommand("SELECT req.REQUEST_ID, req_st.REQUEST_STATUS_ID_ACTUAL, req_st.REQUEST_STATUS_ID_OLD FROM system.REQUEST req"
+ " join system.REQUEST_STATUS_CHANGE req_st on req.request_id = req_st.REQUEST_ID"
+ " WHERE req.REQUEST_ID = :id AND req.REQUEST_ID IN(SELECT req_st.REQUEST_ID from system.REQUEST_STATUS_CHANGE req_st"
+ " GROUP BY req_st.REQUEST_ID HAVING MAX(req_st.REQUEST_STATUS_ID_ACTUAL) = 1)", connection);
oraCommand.Parameters.Add("id", id);
OracleDataReader oraReader = oraCommand.ExecuteReader();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
var temp = new RequestStatusChange();
temp.request_id = oraReader.GetInt32(0);
temp.new_status = oraReader.GetValue(1) == DBNull.Value ? temp.old_status = null : (int)oraReader.GetInt32(1);
temp.old_status = oraReader.GetValue(2) == DBNull.Value ? temp.new_status = null : (int)oraReader.GetInt32(2);
request = temp;
}
}
}
return(request);
}
public int Insert(RequestStatusChange input)
{
List <OracleParameter> parameters = new List <OracleParameter>();
parameters.Add(new OracleParameter("request_status_id_old", input.old_status));
parameters.Add(new OracleParameter("request_status_id_actual", input.new_status));
parameters.Add(new OracleParameter("request_id", input.request_id));
parameters.Add(new OracleParameter("change_date", DateTime.Now.ToString()));
int res = ExecuteNonQuery("INSERT INTO system.request_status_change (request_status_change_id, request_status_id_old, request_status_id_actual, request_id, change_date) "
+ " VALUES (request_status_change_seq.NEXTVAL, :request_status_id_old, :request_status_id_actual, :request_id, :change_date)", parameters);
if (res == 1)
{
return(SelectCurrentFromSequence("request_status_change_seq"));
}
else
{
return(-1);
}
}
