public HttpResponseMessage AddMRRequest(MRApprovalRequest mRApprovalRequest)
{
OracleConnection con = new OracleConnection(ConnectionString);
try
{
con.Open();
OracleCommand cmd = null;
cmd = new OracleCommand("INSERT_MR_APPROVAL");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("V_JOB_ID", OracleType.Number).Value = mRApprovalRequest.JobId;
cmd.Parameters.Add("V_REVISION_NO", OracleType.Number).Value = mRApprovalRequest.RevisionId;
cmd.Parameters.Add("V_REQUESTED_BY", OracleType.VarChar).Value = mRApprovalRequest.RequestedBy;
cmd.Parameters.Add("V_REMARKS", OracleType.VarChar).Value = mRApprovalRequest.Remarks;
cmd.Parameters.Add("V_APPROVE_BRANCH_TYPE", OracleType.VarChar).Value = mRApprovalRequest.ApproveBranchCode;
cmd.Parameters.Add("V_REQUESTED_MR", OracleType.Number).Value = mRApprovalRequest.RequestedMr;
cmd.ExecuteNonQuery();
con.Close();
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (Exception ex)
{
con.Close();
return(null);
}
}
public MRApprovalRequest Get(int id)
{
MRApprovalRequest mRApprovalRequest = new MRApprovalRequest();
OracleConnection con = new OracleConnection(ConnectionString);
OracleDataReader dr = null;
string sql = " SELECT " +
"MMA.SEQ_ID ," + //0
"CASE WHEN MMA.JOB_ID IS NULL THEN 0 ELSE MMA.JOB_ID END," + //1
"CASE WHEN MMA.REVISION_NO IS NULL THEN 0 ELSE MMA.REVISION_NO END," + //2
"CASE WHEN MMA.REQUESTED_BY IS NULL THEN '' ELSE MMA.REQUESTED_BY END," + //3
"CASE WHEN MMA.RECOMENDED_BY IS NULL THEN '' ELSE MMA.RECOMENDED_BY END," + //4
"CASE WHEN MMA.APPROVED_BY IS NULL THEN '' ELSE MMA.APPROVED_BY END,"+ //5
"CASE WHEN MMA.REMARKS IS NULL THEN '' ELSE MMA.REMARKS END," + //6
"CASE WHEN MMA.APPROVE_BRANCH_TYPE IS NULL THEN '' ELSE MMA.APPROVE_BRANCH_TYPE END," + //7
"CASE WHEN MMA.REQUEST_STATUS IS NULL THEN '' ELSE MMA.REQUEST_STATUS END," + //8
"CASE WHEN MMA.REQUESTED_MR IS NULL THEN 0 ELSE MMA.REQUESTED_MR END," + //9
"CASE WHEN MMA.APPROVE_REJECT_REASON IS NULL THEN '' ELSE MMA.APPROVE_REJECT_REASON END," + //10
"CASE WHEN MMA.APPROVE_BRANCH_CODE IS NULL THEN '' ELSE MMA.APPROVE_BRANCH_CODE END," + //11
"CASE WHEN WAU.USER_NAME IS NULL THEN '' ELSE WAU.USER_NAME END, " + //13
"CASE WHEN WAU.USER_EMAIL IS NULL THEN '' ELSE WAU.USER_EMAIL END " + //12
" FROM MNBQ_MR_APPROVAL MMA " +
" INNER JOIN WF_ADMIN_USERS WAU ON MMA.REQUESTED_BY=WAU.USER_CODE " +
" WHERE MMA.JOB_ID=:V_JOB_ID";
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add(new OracleParameter("V_JOB_ID", id));
// cmd.Parameters.Add(new OracleParameter("V_REVISION_NO", id));
con.Open();
try
{
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
mRApprovalRequest.SeqId = Convert.ToInt32(dr[0].ToString());
mRApprovalRequest.JobId = Convert.ToInt32(dr[1].ToString());
mRApprovalRequest.RequestedBy = dr[3].ToString();
mRApprovalRequest.RecomendedBy = dr[4].ToString();
mRApprovalRequest.ApprovedBy = dr[5].ToString();
mRApprovalRequest.Remarks = dr[6].ToString();
mRApprovalRequest.ApproveBranchType = dr[7].ToString();
mRApprovalRequest.RequestStatus = dr[8].ToString();
mRApprovalRequest.RequestedMr = Convert.ToDouble(dr[9].ToString());
mRApprovalRequest.ApproveRejectReason = dr[10].ToString();
mRApprovalRequest.ApproveBranchCode = dr[11].ToString();
mRApprovalRequest.UserName = dr[12].ToString();
mRApprovalRequest.UserEmail = dr[13].ToString();
dr.Close();
con.Close();
}
else
{
return(null);
}
}
catch (Exception exception)
{
if (dr != null || con.State == ConnectionState.Open)
{
dr.Close();
con.Close();
}
}
finally
{
con.Close();
}
return(mRApprovalRequest);
}
