public JobInProcessScanFinView SerachFinPcs(JobInProcessSearchView model) { using (var ctx = new ConXContext()) { //define model view JobInProcessScanFinView view = new ModelViews.JobInProcessScanFinView() { pageIndex = model.pageIndex - 1, itemPerPage = model.itemPerPage, totalItem = 0, datas = new List <ModelViews.JobInProcessScanView>() }; string sql = "select a.PCS_BARCODE , a.PROD_CODE , b.PROD_TNAME PROD_NAME, b.PDMODEL_DESC PDMODEL_CODE"; sql += " from MPS_DET_IN_PROCESS a , PRODUCT b"; sql += " where a.prod_code = b.prod_code"; sql += " and a.mps_st = 'Y'"; sql += " and a.entity = :p_entity"; sql += " and a.fin_by = :p_user_id"; sql += " and trunc(a.fin_date) = trunc(SYSDATE)"; sql += " and a.wc_code = :p_wc_code"; sql += " and a.mc_code = :p_mc_code"; sql += " and a.req_date = to_date(:p_req_date,'dd/mm/yyyy')"; sql += " and a.spring_grp = :p_spring_grp"; sql += " and a.pdsize_code = :p_pdsize_code"; List <JobInProcessScanView> scan = ctx.Database.SqlQuery <JobInProcessScanView>(sql, new OracleParameter("p_entity", model.entity), new OracleParameter("p_user_id", model.user_id), new OracleParameter("p_wc_code", model.wc_code), new OracleParameter("p_mc_code", model.mc_code), new OracleParameter("p_req_date", model.req_date), new OracleParameter("p_spring_grp", model.spring_grp), new OracleParameter("p_pdsize_code", model.size_code)).ToList(); view.totalItem = scan.Count; scan = scan.Skip(view.pageIndex * view.itemPerPage) .Take(view.itemPerPage) .ToList(); ////prepare model to modelView foreach (var i in scan) { 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 data to contoller return(view); } }
//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); } }