public HttpResponseMessage postCancelPcs(DataEntrySearchView model) { try { var result = inprocSvc.CancelPcs(model); return(Request.CreateResponse(HttpStatusCode.OK, result)); } catch (Exception ex) { return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message.ToString())); } }
//public void CancelPcs(DataEntrySearchView model) //{ // using (var ctx = new ConXContext()) // { // var ventity = model.entity; // var vreq_date = model.req_date; // var vwc_code = model.wc_code; // var vmc_code = model.mc_code; // var vuser_id = model.user_id; // var vspring_grp = model.spring_grp; // var vsize_code = model.size_code; // var vqty = model.qty; // string sqlc = "select count(*) from mps_det_in_process"; // sqlc += " where entity = :p_entity"; // sqlc += " and req_date=to_date(:p_req_date,'dd/mm/yyyy')"; // sqlc += " and wc_code = :p_wc_code"; // sqlc += " and mc_code = :p_mc_code"; // sqlc += " and pdsize_code = :p_size_code"; // sqlc += " and spring_grp = :p_spring_grp"; // sqlc += " and mps_st='Y'"; // int cnt = ctx.Database.SqlQuery<int>(sqlc, new OracleParameter("p_entity", ventity), new OracleParameter("p_req_date", vreq_date), new OracleParameter("p_wc_code", vwc_code), new OracleParameter("p_mc_code", vmc_code), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp)).FirstOrDefault(); ; // if (vqty > cnt) // { // throw new Exception("ยกเลิกเกินจำนวน"); // } // 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", ventity), // new OracleParameter("p_req_date", vreq_date), // new OracleParameter("p_wc_code", vwc_code), // new OracleParameter("p_mc_code", vmc_code), // new OracleParameter("p_size_code", vsize_code), // new OracleParameter("p_spring_grp", vspring_grp), // new OracleParameter("p_user_id", vuser_id), // new OracleParameter("p_qty", vqty) // }; // oraCommand.BindByName = true; // oraCommand.Parameters.AddRange(param); // oraCommand.CommandText = "update MPS_DET_IN_PROCESS 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 req_date = to_date(:p_req_date,'dd/mm/yyyy') and wc_code =:p_wc_code and mc_code = :p_mc_code and pdsize_code = :p_size_code and spring_grp = :p_spring_grp and mps_st='Y' and rownum <= :p_qty "; // //oraCommand.ExecuteReader(CommandBehavior.SingleRow); // oraCommand.ExecuteNonQuery(); // conn.Close(); // scope.Complete(); // } // } //} public JobInProcessScanFinView CancelPcs(DataEntrySearchView model) { using (var ctx = new ConXContext()) { var ventity = model.entity; var vreq_date = model.req_date; var vwc_code = model.wc_code; var vmc_code = model.mc_code; var vuser_id = model.user_id; var vspring_grp = model.spring_grp; var vspringtype_code = model.springtype_code; var vsize_code = model.size_code; var vqty = model.qty; JobInProcessScanFinView view = new ModelViews.JobInProcessScanFinView() { pageIndex = 0, itemPerPage = 10, totalItem = 0, datas = new List <ModelViews.JobInProcessScanView>() }; string sqlc = "select count(*) from mps_det_in_process"; sqlc += " where entity = :p_entity"; sqlc += " and req_date=to_date(:p_req_date,'dd/mm/yyyy')"; sqlc += " and wc_code = :p_wc_code"; sqlc += " and mc_code = :p_mc_code"; sqlc += " and pdsize_code = :p_size_code"; sqlc += " and spring_grp = :p_spring_grp"; sqlc += " and springtype_code = :p_springtype_code"; sqlc += " and mps_st='Y'"; int cnt = ctx.Database.SqlQuery <int>(sqlc, new OracleParameter("p_entity", ventity), new OracleParameter("p_req_date", vreq_date), new OracleParameter("p_wc_code", vwc_code), new OracleParameter("p_mc_code", vmc_code), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_springtype_code", vspringtype_code)).FirstOrDefault();; if (vqty > cnt) { throw new Exception("ยกเลิกเกินจำนวน"); } string sqlp = "select pcs_barcode , spring_grp springtype_code , pdsize_desc ,prod_code"; sqlp += " from mps_det_in_process"; sqlp += " where spring_grp = :p_spring_grp"; sqlp += " and springtype_code = :p_springtype_code"; sqlp += " and pdsize_code = :p_size_code"; sqlp += " and req_date = to_date(:p_req_date,'dd/mm/yyyy')"; sqlp += " and entity = :p_entity"; sqlp += " and wc_code =:p_wc_code"; sqlp += " and mc_code =:p_mc_code"; sqlp += " and mps_st = 'Y'"; sqlp += " and rownum <= :p_qty"; List <JobInProcessScanView> mps_in_process = ctx.Database.SqlQuery <JobInProcessScanView>(sqlp, new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_springtype_code", vspringtype_code), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_req_date", model.req_date), new OracleParameter("p_entity", model.entity), new OracleParameter("p_wc_code", model.wc_code), new OracleParameter("p_mc_code", model.mc_code), new OracleParameter("p_qty", vqty)).ToList(); 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", ventity), new OracleParameter("p_req_date", vreq_date), new OracleParameter("p_wc_code", vwc_code), new OracleParameter("p_mc_code", vmc_code), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_springtype_code", vspringtype_code), new OracleParameter("p_user_id", vuser_id), new OracleParameter("p_qty", vqty) }; oraCommand.BindByName = true; oraCommand.Parameters.AddRange(param); oraCommand.CommandText = "update MPS_DET_IN_PROCESS 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 req_date = to_date(:p_req_date,'dd/mm/yyyy') and wc_code =:p_wc_code and mc_code = :p_mc_code and pdsize_code = :p_size_code and spring_grp = :p_spring_grp and springtype_code = :p_springtype_code and mps_st='Y' and rownum <= :p_qty "; //oraCommand.ExecuteReader(CommandBehavior.SingleRow); oraCommand.ExecuteNonQuery(); conn.Close(); scope.Complete(); foreach (var i in mps_in_process) { view.datas.Add(new ModelViews.JobInProcessScanView() { pcs_barcode = i.pcs_barcode, //pdmodel_code = i.pdmodel_code, prod_code = i.prod_code //prod_name = i.prod_name }); } } return(view); } }
//public void UpdatePcs(DataEntrySearchView model) //{ // using (var ctx = new ConXContext()) // { // var ventity = model.entity; // var vreq_date = model.req_date; // var vwc_code = model.wc_code; // var vmc_code = model.mc_code; // var vuser_id = model.user_id; // var vspring_grp = model.spring_grp; // var vsize_code = model.size_code; // var vqty = model.qty; // //DateTime vreq_date = Convert.ToDateTime(model.req_date); // //Check QP QTY // string sqlp = "select d.WC_PREV from PD_WCCTL_SEQ d where d.pd_entity = :p_entity and d.wc_code = :p_wc_code"; // string vprev_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 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 b.spring_type = :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 ='N'"; // //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 e.spring_type = :p_spring_grp2"; // sql += " and f.wc_code = :p_prev_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 = 'Y')"; // sql += " and rownum <= :p_qty"; // List<ScanPcsDataView> pcs = ctx.Database.SqlQuery<ScanPcsDataView>(sql, new OracleParameter("p_req_date", vreq_date), new OracleParameter("p_entity", ventity), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_wc_code", vwc_code), new OracleParameter("p_req_date2",vreq_date), new OracleParameter("p_entity2", ventity), new OracleParameter("p_size_code2", vsize_code), new OracleParameter("p_spring_grp2", vspring_grp), new OracleParameter("p_prev_wc", vprev_wc), new OracleParameter("p_qty", vqty)) // .ToList(); // using (TransactionScope scope = new TransactionScope()) // { // string strConn = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString; // var dataConn = new OracleConnectionStringBuilder(strConn); // OracleConnection conn = new OracleConnection(dataConn.ToString()); // conn.Open(); // foreach (var i in pcs) // { // OracleCommand oraCommand = conn.CreateCommand(); // OracleParameter[] param = new OracleParameter[] // { // new OracleParameter("p_entity", ventity), // new OracleParameter("p_user_id", vuser_id), // new OracleParameter("p_pcs_barcode", i.pcs_barcode), // new OracleParameter("p_wc_code", vwc_code) // }; // oraCommand.BindByName = true; // oraCommand.Parameters.AddRange(param); // oraCommand.CommandText = "update MPS_DET_WC set mps_st='Y' , 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(); // } // } //} public ScanSendFinView UpdatePcs(DataEntrySearchView model) { using (var ctx = new ConXContext()) { var ventity = model.entity; var vreq_date = model.req_date; var vwc_code = model.wc_code; var vmc_code = model.mc_code; var vuser_id = model.user_id; var vspring_grp = model.spring_grp; var vsize_code = model.size_code; var vqty = model.qty; ScanSendFinView view = new ModelViews.ScanSendFinView() { pageIndex = 0, itemPerPage = 10, totalItem = 0, datas = new List <ModelViews.ScanSendDataView>() }; string sqlp = "select d.WC_PREV from PD_WCCTL_SEQ d where d.pd_entity = :p_entity and d.wc_code = :p_wc_code"; string vprev_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 b.spring_type = :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 ='N'"; //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 e.spring_type = :p_spring_grp2"; sql += " and f.wc_code = :p_prev_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 = 'Y')"; sql += " and rownum <= :p_qty"; List <ScanPcsDataView> pcs = ctx.Database.SqlQuery <ScanPcsDataView>(sql, new OracleParameter("p_req_date", vreq_date), new OracleParameter("p_entity", ventity), new OracleParameter("p_size_code", vsize_code), new OracleParameter("p_spring_grp", vspring_grp), new OracleParameter("p_wc_code", vwc_code), new OracleParameter("p_req_date2", vreq_date), new OracleParameter("p_entity2", ventity), new OracleParameter("p_size_code2", vsize_code), new OracleParameter("p_spring_grp2", vspring_grp), new OracleParameter("p_prev_wc", vprev_wc), new OracleParameter("p_qty", vqty)) .ToList(); using (TransactionScope scope = new TransactionScope()) { string strConn = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString; var dataConn = new OracleConnectionStringBuilder(strConn); OracleConnection conn = new OracleConnection(dataConn.ToString()); conn.Open(); foreach (var i in pcs) { OracleCommand oraCommand = conn.CreateCommand(); OracleParameter[] param = new OracleParameter[] { new OracleParameter("p_entity", ventity), new OracleParameter("p_user_id", vuser_id), new OracleParameter("p_pcs_barcode", i.pcs_barcode), new OracleParameter("p_wc_code", vwc_code) }; oraCommand.BindByName = true; oraCommand.Parameters.AddRange(param); oraCommand.CommandText = "update MPS_DET_WC set mps_st='Y' , 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(); foreach (var i in pcs) { view.datas.Add(new ModelViews.ScanSendDataView() { pcs_barcode = i.pcs_barcode, //pdmodel_code = i.pdmodel_code, prod_code = i.prod_code //prod_name = i.prod_name }); } } return(view); } }