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);
}
}
