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 HttpResponseMessage postSerachCancelPcs(JobInProcessSearchView model)
{
try
{
var result = inprocSvc.SerachCancelPcs(model);
return(Request.CreateResponse(HttpStatusCode.OK, result));
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.ToString()));
}
}
public JobInProcessView SearchScanCancelPcs(JobInProcessSearchView 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);
string sqlp = "select pcs_barcode , springtype_code , pdsize_desc ,prod_code";
sqlp += " from mps_det_in_process";
sqlp += " where springtype_code = :p_spring_grp";
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 = 1";
JobInProcessView mps_in_process = ctx.Database.SqlQuery <JobInProcessView>(sqlp, new OracleParameter("p_spring_grp", vspring_grp), 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)).FirstOrDefault();;
if (mps_in_process == null)
{
throw new Exception("PSC Barcodeไม่ถูกต้อง");
}
//define model view
JobInProcessView view = new ModelViews.JobInProcessView()
{
pcs_barcode = mps_in_process.pcs_barcode,
springtype_code = mps_in_process.springtype_code,
pdsize_desc = mps_in_process.pdsize_desc,
prod_code = mps_in_process.prod_code,
qty = 1,
//datas = new List<ModelViews.JobInProcessScanView>()
};
// Udpate
using (TransactionScope scope = new TransactionScope())
{
// mps_det_in_process updateObj = ctx.mps_in_process
//.Where(z => z.PCS_BARCODE == mps_in_process.pcs_barcode
// && z.ENTITY == model.entity
// && z.WC_CODE == model.wc_code).SingleOrDefault();
// updateObj.MPS_ST = "N";
// updateObj.UPD_BY = model.user_id;
// updateObj.UPD_DATE = DateTime.Now;
// updateObj.PROCESS_TAG_QR = model.pcs_barcode;
// ctx.Configuration.AutoDetectChangesEnabled = true;
// ctx.SaveChanges();
// scope.Complete();
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", mps_in_process.pcs_barcode),
new OracleParameter("p_process_tag_qr", model.pcs_barcode),
new OracleParameter("p_wc_code", model.wc_code)
};
oraCommand.BindByName = true;
oraCommand.Parameters.AddRange(param);
oraCommand.CommandText = "update mps_det_in_process set mps_st='N' , process_tag_qr = :p_process_tag_qr , 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();
}
////////////////////////////////////
//DateTime vreq_date = DateTime.Now;
//query data
//string sql = "select a.PCS_BARCODE , a.PROD_CODE , b.PROD_TNAME , b.PDMODEL_DESC";
//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.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.mc_code = :p_mc_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)).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);
}
}
