public HttpResponseMessage postSearchScanCancelPcs(ScanPcsSearchView model) { try { var result = sendSvc.SearchScanCanclePcs(model); return(Request.CreateResponse(HttpStatusCode.OK, result)); } catch (Exception ex) { return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message.ToString())); } }
public ScanPcsView SearchScanCanclePcs(ScanPcsSearchView model) { using (var ctx = new ConXContext()) { String[] strlist = model.pcs_barcode.Split('|'); string vspring_grp = strlist[0]; string vsize_code = strlist[1]; //DateTime vreq_date = Convert.ToDateTime(model.req_date); //Check ส่งมอบหน่วยถัดไปแล้ว string sqlp = "select d.WC_NEXT from PD_WCCTL_SEQ d where d.pd_entity = :p_entity and d.wc_code = :p_wc_code"; string vnext_wc = ctx.Database.SqlQuery <string>(sqlp, new OracleParameter("p_entity", model.entity), new OracleParameter("p_wc_code", model.wc_code)) .FirstOrDefault(); string sql = "select a.PCS_BARCODE , a.prod_code from MPS_DET a , PDMODEL_MAST b , MPS_DET_WC c"; sql += " where a.req_date = to_date(:p_req_date,'dd/mm/yyyy')"; sql += " and a.entity = :p_entity"; sql += " and a.pdsize_code = :p_size_code"; sql += " and substr(b.spring_type,1,2) = :p_spring_grp"; sql += " and c.wc_code = :p_wc_code"; sql += " and a.pddsgn_code = b.pdmodel_code"; sql += " and a.entity = c.entity"; sql += " and a.req_date = c.req_date"; sql += " and a.pcs_no = c.pcs_no"; //sql += " and c.mps_st <> 'OCL'; sql += " and c.mps_st ='Y'"; sql += " and rownum = 1"; sql += " and a.pcs_barcode in (select d.pcs_barcode from MPS_DET d, PDMODEL_MAST e , MPS_DET_WC f"; sql += " where d.req_date = to_date(:p_req_date2,'dd/mm/yyyy')"; sql += " and d.entity = :p_entity2"; sql += " and d.pdsize_code = :p_size_code2"; sql += " and substr(e.spring_type,1,2) = :p_spring_grp2"; sql += " and f.wc_code = :p_next_wc"; sql += " and d.pddsgn_code = e.pdmodel_code"; sql += " and d.entity = f.entity"; sql += " and d.req_date = f.req_date"; sql += " and d.pcs_no = f.pcs_no"; sql += " and f.mps_st = 'N')"; sql += " and rownum = 1"; ScanPcsView pcs = ctx.Database.SqlQuery <ScanPcsView>(sql, new OracleParameter("p_req_date", model.req_date), new OracleParameter("p_entity", model.entity), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_wc_code", model.wc_code), new OracleParameter("p_req_date2", model.req_date), new OracleParameter("p_entity2", model.entity), new OracleParameter("p_size_code2", vsize_code), new OracleParameter("p_spring_grp2", vspring_grp), new OracleParameter("p_wc_next", vnext_wc)) .FirstOrDefault(); if (pcs == null) { throw new Exception("ยกเลิกไม่ได้ มีการรับในหน่วยถัดไปแล้ว"); } ////define model view ScanPcsView view = new ModelViews.ScanPcsView() { pcs_barcode = pcs.pcs_barcode, spring_grp = vspring_grp, size_desc = vsize_code, qty = 1, prod_code = pcs.prod_code, //datas = new List<ModelViews.JobInProcessScanView>() }; using (TransactionScope scope = new TransactionScope()) { string strConn = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString; var dataConn = new OracleConnectionStringBuilder(strConn); OracleConnection conn = new OracleConnection(dataConn.ToString()); conn.Open(); OracleCommand oraCommand = conn.CreateCommand(); OracleParameter[] param = new OracleParameter[] { new OracleParameter("p_entity", model.entity), new OracleParameter("p_user_id", model.user_id), new OracleParameter("p_pcs_barcode", view.pcs_barcode), new OracleParameter("p_wc_code", model.wc_code) }; oraCommand.BindByName = true; oraCommand.Parameters.AddRange(param); oraCommand.CommandText = "update MPS_DET_WC set mps_st='N' , fin_by =:p_user_id , fin_date = SYSDATE , upd_by =:p_user_id , upd_date = SYSDATE where entity = :p_entity and pcs_barcode = :p_pcs_barcode and wc_code =:p_wc_code"; //oraCommand.ExecuteReader(CommandBehavior.SingleRow); oraCommand.ExecuteNonQuery(); conn.Close(); scope.Complete(); } //return data to contoller return(view); } }