public async Task<ActionResult> popUpSearch(string keywordId, string keywordSearch, int maxRows, int pageStart)
{
string sql = "WITH cte AS ( ";
sql += "SELECT ROW_NUMBER() OVER(ORDER BY poId) AS rn, ";
sql += "a.poId, a.poDate, b.vendorID, b.vendorName, a.poUrgent ";
sql += "FROM [dbo].[PurchaseOrderHeaders] a WITH(NOLOCK) ";
sql += "INNER JOIN [dbo].[Vendors] b ON b.vendorID = a.vendorId ";
sql += "WHERE a.poId NOT IN (SELECT poId FROM [dbo].[PurchaseReceives]) AND a.proposedStatus = 1";
if (keywordId != string.Empty && keywordSearch != string.Empty && !keywordId.Contains("a.poDate"))
{
sql += "AND " + keywordId + " LIKE '%" + keywordSearch + "%' ";
}
else
{
sql += "AND " + keywordId + keywordSearch;
}
sql += ") ";
sql += "SELECT * FROM cte ";
string sqlAll = sql;
sql += "WHERE rn BETWEEN " + ((pageStart * maxRows) - (maxRows - 1)) + " AND " + (pageStart * maxRows);
DataTable dataAll = con.executeReader(sqlAll);
ViewBag.totalRows = dataAll.Rows.Count;
ViewBag.currentPage = pageStart;
DataTable popUp = con.executeReader(sql);
var model = new purchaseOrderHeader();
if (popUp != null)
{
foreach (DataRow dr in popUp.Rows)
{
var editor = new purchaseOrderHeader.POpopUp()
{
poId = dr["poId"].ToString(),
poDate = Convert.ToDateTime(dr["poDate"].ToString()),
vendorId = dr["vendorId"].ToString(),
vendorName = dr["vendorName"].ToString(),
poUrgent = dr["poUrgent"].ToString(),
};
model.PopUpPO.Add(editor);
}
ViewBag.POPopUp = model.PopUpPO.ToList();
}
return PartialView("_PartialPagePopUpPOSearchSub");
}
public async Task<ActionResult> LoadPopUpPO(string poId)
{
var x = db.purchaseOrderHeaders
.Join(db.vendors, a => a.vendorId, b => b.vendorID, (a, b) => new { a, b })
.Select(c => new { c.a.poId, c.a.poDate, c.a.vendorId, c.b.vendorName, c.a.poUrgent }).ToList();
if(poId != string.Empty)
x = db.purchaseOrderHeaders.Where(po=>po.poId == poId)
.Join(db.vendors, a => a.vendorId, b => b.vendorID, (a, b) => new { a, b })
.Select(c => new { c.a.poId, c.a.poDate, c.a.vendorId, c.b.vendorName, c.a.poUrgent }).ToList();
var model = new purchaseOrderHeader();
foreach(var y in x as IEnumerable<Models.purchaseOrderHeader.POpopUp>)
{
var editor = new purchaseOrderHeader.POpopUp()
{
poId = y.poId,
poDate = y.poDate,
vendorId = y.vendorId,
vendorName = y.vendorName,
poUrgent = y.poUrgent
};
model.PopUpPO.Add(editor);
}
ViewBag.PopUpPO = model.PopUpPO.ToList();
return PartialView("~/Views/Shared/_PartialPagePopUpPO.cshtml");
}
public ActionResult Edit(purchaseOrderHeader purchaseorderheader)
{
if (acm.cekSession() == false)
return RedirectToAction("Logout", "Account");
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
if (acm.cekValidation(Url.Action().ToString()) == false && lvm.isAdmin == false)
return RedirectToAction("NotAuthorized", "Account", new { menu = Url.Action().ToString() });
#region
//ViewBag.ddlVendor = DDLVendor(purchaseorderheader.vendorId);
ViewBag.ddlVendor = DDLVendorByPRId(purchaseorderheader.prId);
loadPO(purchaseorderheader.poId);
#endregion
if (ModelState.IsValid)
{
var countChk = 0;
var countChkMultiple = 0;
bool isProposed = false;
for (int i = 0; i < Request.Form.Count; i++)
{
if (Request.Form.AllKeys.ToList()[i].Contains("txtProductID_"))
countChk++;
else if (Request.Form.AllKeys.ToList()[i].Contains("hdnTypeSubmit"))
if (Request.Form["hdnTypeSubmit"].ToString() == "requested")
isProposed = true;
if (purchaseorderheader.topType == "Multiple")
if (Request.Form.AllKeys.ToList()[i].Contains("txtBuyDate_"))
countChkMultiple++;
}
try
{
using (TransactionScope ts = new TransactionScope())
{
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
purchaseorderheader.proposedStatus = isProposed;
purchaseorderheader.modifiedDate = DateTime.Now;
purchaseorderheader.modifiedUser = lvm.userID;
db.Entry(purchaseorderheader).State = EntityState.Modified;
#region insertDetail
bool runFirst = true;
for (int i = 1; i <= countChk; i++)
{
if (runFirst == true)
{
db.purchaseOrderDetails.RemoveRange(db.purchaseOrderDetails.Where(x => x.poId == purchaseorderheader.poId));
runFirst = false;
}
var colVal = Request.Form["txtProductID_" + i];
if (colVal == "")
break;
int t_productID = int.Parse(Request.Form["txtProductID_" + i].ToString());
int t_qty = Request.Form["txtQty_" + i].ToString() != "" ? Convert.ToInt32(Request.Form["txtQty_" + i].ToString().Replace('.', ',')) : 0;
decimal t_price = Request.Form["txtPrice_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtPrice_" + i].ToString().Replace('.', ','))) : 0;
decimal t_disc = Request.Form["txtDisc_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtDisc_" + i].ToString().Replace('.', ','))) : 0;
var editorx = new purchaseOrderHeader.purchaseOrderDetail()
{
poId = purchaseorderheader.poId,
productID = t_productID,
qty = t_qty,
price = t_price,
disc = t_disc
};
db.purchaseOrderDetails.Add(editorx);
}
#endregion
#region insertTOPMultiple
if (purchaseorderheader.topType == "Multiple")
{
runFirst = true;
for (int i = 0; i <= countChkMultiple; i++)
{
if (runFirst == true)
{
db.purchaseOrderTOPs.RemoveRange(db.purchaseOrderTOPs.Where(x => x.poId == purchaseorderheader.poId));
runFirst = false;
}
var colVal = Request.Form["txtBuyDate_" + i];
if (colVal == "")
break;
DateTime t_BuyDate = Convert.ToDateTime(Request.Form["txtBuyDate_" + i].ToString());
decimal t_BuyPercent = Request.Form["txtBuyPercent_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtBuyPercent_" + i].ToString().Replace('.', ','))) : 0;
var editort = new purchaseOrderHeader.purchaseOrderTop()
{
poId = purchaseorderheader.poId,
buyDate = t_BuyDate,
buyPercent = t_BuyPercent
};
db.purchaseOrderTOPs.Add(editort);
}
}
#endregion
db.SaveChanges();
ts.Complete();
}
return RedirectToAction("Index");
}
catch (Exception exc)
{
string a = exc.Message;
}
}
return View(purchaseorderheader);
}
public ActionResult Create(purchaseOrderHeader purchaseorderheader)
{
if (acm.cekSession() == false)
return RedirectToAction("Logout", "Account");
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
if (acm.cekValidation(Url.Action().ToString()) == false && lvm.isAdmin == false)
return RedirectToAction("NotAuthorized", "Account", new { menu = Url.Action().ToString() });
//ViewBag.ddlVendor = DDLVendor(string.Empty);
ViewBag.ddlVendor = DDLVendorByPRId(purchaseorderheader.prId);
var xz = db.purchaseRequestHeaders.AsNoTracking().Where(x => !db.purchaseOrderHeaders.Any(y => y.prId == x.prId) && x.proposedStatus == true && x.approvedStatus == true)
//.Join(db.departments, a => a.requestDeptId, b => b.deptID, (a, b) => new { a, b }) ~old process ~sweeping department kebalik dgn divisi
.Join(db.divisis, a => a.requestDeptId, b => b.divisiID, (a, b) => new { a, b })
.Select(c => new { c.a.prId, c.a.requestDeptId, deptName = c.b.divisiName, c.a.typeOrder, c.a.proposalInclude, c.a.specialInstruction, c.a.projectTimeDelivery }).ToList();
ViewData["PRPopUp"] = xz;
var model = new purchaseOrderHeader();
var editor = new purchaseOrderHeader.prPopUp()
{
prId = xz[0].prId,
requestDeptId = xz[0].requestDeptId,
deptName = xz[0].deptName,
typeOrder = xz[0].typeOrder,
proposalInclude = xz[0].proposalInclude,
specialInstruction = xz[0].specialInstruction,
projectTimeDelivery = xz[0].projectTimeDelivery
};
model.PopUpPR.Add(editor);
ViewBag.PRPopUp = model.PopUpPR.ToList();
var modelx = new purchaseRequestHeader();
foreach (var a in db.purchaseRequestDetails.Where(x => x.prId == purchaseorderheader.prId).ToList() as IEnumerable<purchaseRequestHeader.purchaseRequestDetail>)
{
var editorx = new purchaseRequestHeader.purchaseRequestDetail()
{
productId = a.productId,
description = a.description,
qty = a.qty,
unit = a.unit
};
modelx.detailPurchaseRequest.Add(editorx);
}
ViewData["dataDetail"] = modelx.detailPurchaseRequest.ToList();
if (ModelState.IsValid)
{
var countChk = 0;
var countChkMultiple = 0;
bool isProposed = false;
for (int i = 0; i < Request.Form.Count; i++)
{
if (Request.Form.AllKeys.ToList()[i].Contains("txtProductID_"))
countChk++;
else if (Request.Form.AllKeys.ToList()[i].Contains("hdnTypeSubmit"))
if (Request.Form["hdnTypeSubmit"].ToString() == "requested")
isProposed = true;
if (purchaseorderheader.topType == "Multiple")
if (Request.Form.AllKeys.ToList()[i].Contains("txtBuyDate_"))
countChkMultiple++;
}
try
{
using (TransactionScope ts = new TransactionScope())
{
string generateID = prefix + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("d2");
var prIDCreated = db.purchaseOrderHeaders.Where(x => x.poId.Contains(generateID)).OrderByDescending(x => x.createdDate).Select(x => x.poId).ToList();
if (prIDCreated.Count == 0)
{
generateID = generateID + "0001";
}
else
{
generateID = generateID + (Convert.ToInt32(prIDCreated[0].Substring((prIDCreated[0].Length - 4))) + 1).ToString().PadLeft(4, '0');
}
#region ubah vendorID ke vendorCode
int idVendor = 0;
try{
idVendor = int.Parse(purchaseorderheader.vendorId) * 1;
}
catch
{
}
if(idVendor > 0 )
{
var ven = db.vendors.AsNoTracking().Where(x => x.id == idVendor).ToList();
purchaseorderheader.vendorId = ven[0].vendorID;
}
#endregion
purchaseorderheader.poId = generateID;
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
purchaseorderheader.createdDate = DateTime.Now;
purchaseorderheader.createdUser = lvm.userID;
purchaseorderheader.proposedBy = lvm.userID;
purchaseorderheader.proposedDate = DateTime.Now;
purchaseorderheader.proposedStatus = isProposed;
db.purchaseOrderHeaders.Add(purchaseorderheader);
#region insertDetail
for (int i = 1; i <= countChk; i++)
{
var colVal = Request.Form["txtProductID_" + i];
if (colVal == "")
break;
int t_productID = int.Parse(Request.Form["txtProductID_" + i].ToString());
int t_qty = Request.Form["txtQty_" + i].ToString() != "" ? Convert.ToInt32(Request.Form["txtQty_" + i].ToString().Replace('.', ',')) : 0;
decimal t_price = Request.Form["txtPrice_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtPrice_" + i].ToString().Replace('.', ','))) : 0;
decimal t_disc = Request.Form["txtDisc_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtDisc_" + i].ToString().Replace('.', ','))) : 0;
var editorx = new purchaseOrderHeader.purchaseOrderDetail()
{
poId = purchaseorderheader.poId,
productID = t_productID,
qty = t_qty,
price = t_price,
disc = t_disc
};
db.purchaseOrderDetails.Add(editorx);
}
#endregion
#region insertTOPMultiple
if(purchaseorderheader.topType == "Multiple")
{
for (int i = 0; i < countChkMultiple; i++)
{
var colVal = Request.Form["txtBuyDate_" + i];
if (colVal == "")
break;
DateTime t_BuyDate = Convert.ToDateTime(Request.Form["txtBuyDate_" + i].ToString());
decimal t_BuyPercent = Request.Form["txtBuyPercent_" + i].ToString() != "" ? Convert.ToDecimal(Convert.ToDouble(Request.Form["txtBuyPercent_" + i].ToString().Replace('.', ','))) : 0;
var editort = new purchaseOrderHeader.purchaseOrderTop()
{
poId = purchaseorderheader.poId,
buyDate = t_BuyDate,
buyPercent = t_BuyPercent
};
db.purchaseOrderTOPs.Add(editort);
}
}
#endregion
db.SaveChanges();
ts.Complete();
}
return RedirectToAction("Index");
}
catch (Exception exc)
{
string a = exc.Message;
}
}
return View(purchaseorderheader);
}
public async Task<ActionResult> saveMultipleTOP(string dataDetail, string poId)
{
string[] data = dataDetail.Split('|');
var model = new purchaseOrderHeader();
for (int i = 0; i < data.Count(); i++)
{
string[] value = data[i].Split(';');
if (value[1].ToString() == "")
break;
DateTime t_BuyDate = Convert.ToDateTime(value[1].ToString());
decimal t_BuyPercent =Convert.ToDecimal(Convert.ToDouble(value[2].ToString().Replace('.', ',')));
var editor = new purchaseOrderHeader.purchaseOrderTop()
{
id = (i+1),
poId = poId,
buyDate = t_BuyDate,
buyPercent = t_BuyPercent
};
model.detailPurchaseTop.Add(editor);
}
ViewData["dataDetailMultiple"] = model.detailPurchaseTop.ToList();
Session["topMultiple"] = model.detailPurchaseTop.ToList();
return PartialView("_PartialPageMultipleTOP");
}
public void getListPR(string keywordId, string keywordSearch, int? maxRows, int? pageStart, bool isResultAll)
{
DataTable dtPRforPO = new DataTable();
DataTable dtPRforPOAll = new DataTable();
using (SqlConnection conn = new SqlConnection(con.conString))
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[sp_pr_for_po]";
cmd.Parameters.AddWithValue("@keywordId",string.IsNullOrEmpty(keywordId) ? (object)DBNull.Value : keywordId);
cmd.Parameters.AddWithValue("@keywordSearch",string.IsNullOrEmpty(keywordSearch) ? (object)DBNull.Value : keywordSearch);
cmd.Parameters.AddWithValue("@maxRows", maxRows == null ? (object)DBNull.Value : maxRows);
cmd.Parameters.AddWithValue("@pageStart", pageStart == null ? (object)DBNull.Value : pageStart);
cmd.Parameters.AddWithValue("@isResultAll", false);
conn.Open();
da.SelectCommand = cmd;
da.Fill(dtPRforPO);
}
using (SqlConnection conn = new SqlConnection(con.conString))
{
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[sp_pr_for_po]";
cmd.Parameters.AddWithValue("@keywordId", string.IsNullOrEmpty(keywordId) ? (object)DBNull.Value : keywordId);
cmd.Parameters.AddWithValue("@keywordSearch", string.IsNullOrEmpty(keywordSearch) ? (object)DBNull.Value : keywordSearch);
cmd.Parameters.AddWithValue("@maxRows", maxRows == null ? (object)DBNull.Value : maxRows);
cmd.Parameters.AddWithValue("@pageStart", pageStart == null ? (object)DBNull.Value : pageStart);
cmd.Parameters.AddWithValue("@isResultAll", true);
conn.Open();
da.SelectCommand = cmd;
da.Fill(dtPRforPOAll);
}
var model = new purchaseOrderHeader();
foreach(DataRow dr in dtPRforPO.Rows)
{
var editor = new purchaseOrderHeader.prPopUp()
{
prId = dr["prId"].ToString(),
requestDeptId = int.Parse(dr["requestDeptId"].ToString()),
deptName = dr["deptName"].ToString(),
typeOrder = dr["typeOrder"].ToString(),
proposalInclude = bool.Parse(dr["proposalInclude"].ToString()),
specialInstruction = dr["specialInstruction"].ToString(),
projectTimeDelivery = dr["projectTimeDelivery"].ToString()
};
model.PopUpPR.Add(editor);
}
ViewBag.PRPopUp = model.PopUpPR.ToList();
ViewBag.totalRows = dtPRforPOAll.Rows.Count;
ViewBag.currentPage = pageStart;
}
public ActionResult Approval(string id)
{
if (acm.cekSession() == false)
return RedirectToAction("Logout", "Account");
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
ViewBag.ApprovalAuth = false;
foreach (string b in lvm.listAuthorize as List<string>)
{
string[] auth = b.Split('_');
if (auth[0].ToString() == "A1")
if (b.Contains((Url.Action("")).Replace("/", "")))
{
ViewBag.ApprovalAuth = true;
}
}
if (ViewBag.ApprovalAuth == false && lvm.isAdmin == true)
ViewBag.ApprovalAuth = true;
if (ViewBag.ApprovalAuth == false && lvm.isAdmin == false)
return RedirectToAction("NotAuthorized", "Account", new { menu = Url.Action().ToString() });
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
purchaseReceive purchasereceive = db.purchaseReceives.Find(id);
if (purchasereceive == null)
{
return HttpNotFound();
}
var popUp = db.purchaseOrderHeaders.AsNoTracking().Where(x => !db.purchaseReceives.Any(y => y.poId == x.poId))
.Join(db.vendors, a => a.vendorId, b => b.vendorID, (a, b) => new { a, b })
.Select(c => new { c.a.poId, c.a.poDate, c.b.vendorID, c.b.vendorName, c.a.poUrgent }).ToList();
var aa = db.purchaseReceives.Where(x => x.receiveNo == "RCV").ToList();
var model = new purchaseOrderHeader();
for (int i = 0; i < popUp.Count; i++)
{
var editor = new purchaseOrderHeader.POpopUp()
{
poId = popUp[i].poId,
poDate = popUp[i].poDate,
vendorId = popUp[i].vendorID,
vendorName = popUp[i].vendorName,
poUrgent = popUp[i].poUrgent
};
model.PopUpPO.Add(editor);
}
ViewBag.POPopUp = model.PopUpPO.ToList();
loadRCV(purchasereceive.receiveNo);
return View(purchasereceive);
}
// GET: /PurchaseReceive/Delete/5
public ActionResult Delete(string id)
{
if (acm.cekSession() == false)
return RedirectToAction("Logout", "Account");
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
if (acm.cekValidation(Url.Action().ToString()) == false && lvm.isAdmin == false)
return RedirectToAction("NotAuthorized", "Account", new { menu = Url.Action().ToString() });
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
purchaseReceive purchasereceive = db.purchaseReceives.Find(id);
if (purchasereceive == null)
{
return HttpNotFound();
}
var popUp = db.purchaseOrderHeaders.AsNoTracking().Where(x => !db.purchaseReceives.Any(y => y.poId == x.poId))
.Join(db.vendors, a => a.vendorId, b => b.vendorID, (a, b) => new { a, b })
.Select(c => new { c.a.poId, c.a.poDate, c.b.vendorID, c.b.vendorName, c.a.poUrgent }).ToList();
var aa = db.purchaseReceives.Where(x => x.receiveNo == "RCV").ToList();
var model = new purchaseOrderHeader();
for (int i = 0; i < popUp.Count; i++)
{
var editor = new purchaseOrderHeader.POpopUp()
{
poId = popUp[i].poId,
poDate = popUp[i].poDate,
vendorId = popUp[i].vendorID,
vendorName = popUp[i].vendorName,
poUrgent = popUp[i].poUrgent
};
model.PopUpPO.Add(editor);
}
ViewBag.POPopUp = model.PopUpPO.ToList();
loadRCV(purchasereceive.receiveNo);
return View(purchasereceive);
}
// GET: /PurchaseReceive/Create
public ActionResult Create()
{
if (acm.cekSession() == false)
return RedirectToAction("Logout", "Account");
lvm = Session["sessionUserLogin"] as LoginViewModel.userLogin;
if (acm.cekValidation(Url.Action().ToString()) == false && lvm.isAdmin == false)
return RedirectToAction("NotAuthorized", "Account", new { menu = Url.Action().ToString() });
var popUp = db.purchaseOrderHeaders.AsNoTracking().Where(x => !db.purchaseReceives.Any(y => y.poId == x.poId) && x.proposedStatus == true && x.approvedStatus == true)
.Join(db.vendors, a => a.vendorId, b => b.vendorID, (a, b) => new { a, b })
.Select(c => new { c.a.poId, c.a.poDate, c.b.vendorID, c.b.vendorName, c.a.poUrgent}).ToList();
var aa = db.purchaseReceives.Where(x => x.receiveNo == "RCV").ToList();
var model = new purchaseOrderHeader();
for (int i = 0; i < popUp.Count; i++)
{
var editor = new purchaseOrderHeader.POpopUp()
{
poId = popUp[i].poId,
poDate = popUp[i].poDate,
vendorId = popUp[i].vendorID,
vendorName = popUp[i].vendorName,
poUrgent = popUp[i].poUrgent
};
model.PopUpPO.Add(editor);
}
ViewBag.POPopUp = model.PopUpPO.ToList();
ViewBag.totalRows = model.PopUpPO.Count;
return View();
}
