// GET: api/Cover //[TDABasicAuthenticationFilter(false)] public IEnumerable <Cover> Get() { var uIdentity = Thread.CurrentPrincipal.Identity; UserAccountController uc = new UserAccountController(); UserAccount user = uc.getUserFromUserName(uIdentity.Name.ToString()); List <Cover> coverList = new List <Cover>(); DataTable dt = new DataTable(); OracleDataReader dr = null; OracleConnection con = new OracleConnection(ConnectionString); //string sql = "SELECT T.ID,T.COVER_CODE,T.COVER FROM mnbq_covers T " + // " WHERE T.COVER_CODE IN (SELECT ALLOWED_COVER_CODES FROM MNB_ALLOWED_COVERS WHERE COMPANY=:V_COMPANY) " + // " order by t.COVER_CODE "; //string sql = "SELECT T.ID,T.COVER_CODE,T.COVER FROM mnbq_covers T " + // " WHERE T.COVER_CODE IN ('chk4','chk5','chk7','chk8','chk11','chk12','chk13','chk17') " + // " order by t.COVER_CODE "; string sql = "SELECT T.ID,ac.COVER_CODE,T.COVER " + " FROM MNB_ALLOWED_COVERS ac " + " inner join mnbq_covers T on t.cover_code = ac.cover_code " + " WHERE ac.COMPANY = :V_COMPANY"; OracleCommand command = new OracleCommand(sql, con); command.Parameters.Add(new OracleParameter("V_COMPANY", user.Company)); try { con.Open(); dr = command.ExecuteReader(); dt.Load(dr); dr.Close(); con.Close(); coverList = (from DataRow drow in dt.Rows select new Cover() { CoverId = Convert.ToInt32(drow["ID"].ToString()), CoverCode = drow["COVER_CODE"].ToString(), CoverName = drow["COVER"].ToString() }).ToList(); } catch (Exception exception) { if (dr != null || con.State == ConnectionState.Open) { dr.Close(); con.Close(); } } return(coverList); }
public HttpResponseMessage AddQuotationCoverDetails(PostQuotationCoverRequest postQuotationCoverRequest) { // api/QuotationCover/AddQuotationCoverDetails?jobId=54163&RevisionId=0 var uIdentity = Thread.CurrentPrincipal.Identity; UserAccountController uc = new UserAccountController(); UserAccount user = uc.getUserFromUserName(uIdentity.Name.ToString()); QuotationMainTakafulController quotationMainController = new QuotationMainTakafulController(); QuotationMainTakaful quotationMain; CoverController coverController = new CoverController(); List <Cover> allowedCoverList = coverController.GetAllowedCovers(user.Company); DeleteCovers(Convert.ToInt32(postQuotationCoverRequest.JobId), Convert.ToInt32(postQuotationCoverRequest.RevisionId)); foreach (QuotationCover cover in postQuotationCoverRequest.QuotationCovers) { if (cover.Cover == "chk4" || cover.Cover == "chk5" || cover.Cover == "chk8") { if (!validateCoverAmount(cover.Cover, cover.Amount)) { var message = string.Format("Amount of cover {0} not in the allowed range", cover.Cover); HttpError err = new HttpError(message); return(Request.CreateResponse(HttpStatusCode.NotAcceptable, err)); } } //To validate the allowed covers var item = allowedCoverList.Find(x => x.CoverCode == cover.Cover); if (item == null) { continue; } /////////// OracleConnection con = new OracleConnection(ConnectionString); try { con.Open(); OracleCommand cmd = null; cmd = new OracleCommand("MNB_T_INSERT_MTR_COVER_DETAILS"); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.Parameters.Add("jobid", OracleType.Int32).Value = cover.JobId; cmd.Parameters.Add("revision_id", OracleType.VarChar).Value = cover.RevisionId; cmd.Parameters.Add("cover", OracleType.VarChar).Value = cover.Cover; cmd.Parameters.Add("pol_type", OracleType.VarChar).Value = cover.Type; cmd.Parameters.Add("amount", OracleType.VarChar).Value = cover.Amount; cmd.ExecuteNonQuery(); con.Close(); //saveDefaultCovers(postQuotationCoverRequest.JobId, user); } catch (Exception ex) { con.Close(); return(Request.CreateResponse(HttpStatusCode.ExpectationFailed)); } } return(Request.CreateResponse(HttpStatusCode.OK)); }