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