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