public ScanSendFinView SerachCanPcs(ScanSendFinSearchView model) { using (var ctx = new ConXContext()) { //define model view ScanSendFinView view = new ModelViews.ScanSendFinView() { pageIndex = model.pageIndex - 1, itemPerPage = model.itemPerPage, totalItem = 0, datas = new List <ModelViews.ScanSendDataView>() }; string sql = "select a.PCS_BARCODE , a.PROD_CODE ,a.PROD_NAME , a.MODEL_NAME model_desc"; sql += " from MPS_DET_WC a , PDMODEL_MASt b "; sql += " where a.pddsgn_code = b.pdmodel_code"; sql += " and a.mps_st = 'N'"; sql += " and a.fin_by = :p_user_id"; sql += " and trunc(a.fin_date) = trunc(SYSDATE)"; sql += " and a.entity = :p_entity"; sql += " and a.wc_code = :p_wc_code"; sql += " and a.pdsize_code = :p_pdsize_code"; sql += " and b.spring_type = :p_springtype_code"; List <ScanSendDataView> scan = ctx.Database.SqlQuery <ScanSendDataView>(sql, new OracleParameter("p_user_id", model.user_id), new OracleParameter("p_entity", model.entity), new OracleParameter("p_wc_code", model.wc_code), new OracleParameter("p_pdsize_code", model.pdsize_code), new OracleParameter("p_springtype_code", model.springtype_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.ScanSendDataView() { pcs_barcode = i.pcs_barcode, model_desc = i.model_desc, prod_code = i.prod_code, prod_name = i.prod_name }); } //return data to contoller 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); } }