public IList <SupplierItem> GetSupplierList(string purchaseRequisitionID)
{
DateTime now = DateTime.Now;
//排除時間
now = new DateTime(now.Year, now.Month, now.Day);
using (PMSAEntities db = new PMSAEntities())
{
var supq = from pr in db.PurchaseRequisition
join prd in db.PurchaseRequisitionDtl
on new { pr.PurchaseRequisitionID, ID = pr.PurchaseRequisitionID } equals
new { prd.PurchaseRequisitionID, ID = purchaseRequisitionID }
join sl in db.SourceList
on prd.PartNumber equals sl.PartNumber
where !(from prdRel in db.PRPORelation
select prdRel.PurchaseRequisitionDtlCode).Contains(prd.PurchaseRequisitionDtlCode) &&
sl.SourceListDtl.Where(d => d.DiscountBeginDate <= now && d.DiscountEndDate >= now).Any()
group sl by new { sl.SupplierCode, sl.SupplierInfo.SupplierName } into g
orderby g.Key.SupplierCode
select new SupplierItem
{
SupplierCode = g.Key.SupplierCode,
SupplierName = "(" + g.Key.SupplierCode + ") " + g.Key.SupplierName,
PurchaseRequisitionID = purchaseRequisitionID
};
IList <SupplierItem> sups = supq.ToList();
return(sups);
}
}
public async Task <ActionResult> getAllEmpToIndexAjax()
{
List <ApplicationUser> usersWithEmpID = await UserManager.Users.Where(x => x.UserName.Contains("C")).ToListAsync();
//return View(await UserManager.Users.ToListAsync());
PMSAEntities db = new PMSAEntities();
List <object> emps = new List <object>();
//datatime 要轉型
var js = new JsonSerializerSettings()
{
DateTimeZoneHandling = DateTimeZoneHandling.Utc
};
foreach (var x in usersWithEmpID)
{
var user = new
{
EmployeeID = x.UserName,
Name = x.RealName,
Role = await UserManager.GetRolesAsync(x.Id),
Email = x.Email,
Mobile = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.Mobile).FirstOrDefaultAsync(),
Tel = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.Tel).FirstOrDefaultAsync(),
AccountStatus = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.AccountStatus).FirstOrDefaultAsync(),
ModifiedDate = JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.ModifiedDate).FirstOrDefaultAsync(), js),
ManagerID = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.ManagerID).FirstOrDefaultAsync(),
CreateDate = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.CreateDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.CreateDate).FirstOrDefaultAsync(), js),
SendLetterDate = await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.SendLetterDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == x.UserName).Select(e => e.SendLetterDate).FirstOrDefaultAsync(), js),
LastPasswordChangedDate = await UserManager.Users.Where(e => e.UserName == x.UserName).Select(e => e.LastPasswordChangedDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await UserManager.Users.Where(e => e.UserName == x.UserName).Select(e => e.LastPasswordChangedDate).FirstOrDefaultAsync(), js),
EmailConfirm = x.EmailConfirmed
};
emps.Add(user);
}
return(Json(emps, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 查詢料件的貨源清單
/// </summary>
/// <param name="PartNumber"></param>
/// <returns></returns>
public IEnumerable <POCSourceListViewModel> GetPOCSourceListViewModel(string partNumber)
{
using (PMSAEntities db = new PMSAEntities())
{
var slq = from sl in db.SourceList
where sl.PartNumber == partNumber
select new POCSourceListViewModel
{
SourceListID = sl.SourceListID,
SupplierName = sl.SupplierInfo.SupplierName,
RatingName = sl.SupplierInfo.SupplierRating.RatingName,
QtyPerUnit = sl.QtyPerUnit,
MOQ = sl.MOQ,
UnitPrice = sl.UnitPrice,
UnitsInStock = sl.UnitsInStock,
SourceListDtlItem = (from sld in sl.SourceListDtl
select new POCSourceListDtlItem
{
QtyDemanded = sld.QtyDemanded,
Discount = sld.Discount
})
};
return(slq.ToList());
}
}
public SupplierHomeController()
{
db = new PMSAEntities();
//supplierCode = "S00001";
//supplierAccount = "SE00001";
POChangedCategoryCodeShipped = "S";
RequesterRoleSupplier = "S";
}
private IEnumerable <Employee> GetEmpID(string EmpID)
{
using (PMSAEntities db = new PMSAEntities())
{
var query = db.Employee.Where(x => x.EmployeeID == EmpID);
return(query.ToList());
}
}
public IList <PurchaseRequisitionItem> GetPurchaseRequisitionList()
{
using (PMSAEntities db = new PMSAEntities())
{
var prq = from pr in db.PurchaseRequisition
where pr.ProcessStatus == "N" && pr.EmployeeID == emp.EmployeeID
select new PurchaseRequisitionItem
{
PurchaseRequisitionIdDisplay = pr.PurchaseRequisitionID,
PurchaseRequisitionIdValue = pr.PurchaseRequisitionID
};
return(prq.ToList());
}
}
/// <summary>
/// 查詢請購單基本資料
/// </summary>
/// <param name="purchaseRequisitionID"></param>
/// <returns></returns>
public PRInfoViewModel GetPRInfoViewModel(string purchaseRequisitionID)
{
using (PMSAEntities db = new PMSAEntities())
{
var infoq = from pr in db.PurchaseRequisition
where pr.PurchaseRequisitionID == purchaseRequisitionID
select new PRInfoViewModel
{
Name = pr.Employee.Name,
PRBeginDate = pr.PRBeginDate,
Email = pr.Employee.Email,
Tel = pr.Employee.Tel
};
return(infoq.FirstOrDefault());
}
}
public async Task <ActionResult> getEmpByIDIndexAjax(string EmpId)
{
var usersWithEmpID = await UserManager.Users.Where(x => x.UserName.Contains("C") && x.UserName == EmpId).FirstOrDefaultAsync();
PMSAEntities db = new PMSAEntities();
//取得此 empId 找Role 加到 RolesList
var user = await UserManager.FindByIdAsync(usersWithEmpID.Id);
var userRoles = await UserManager.GetRolesAsync(user.Id);
//datatime 要轉型
var js = new JsonSerializerSettings()
{
DateTimeZoneHandling = DateTimeZoneHandling.Utc
};
var userA = new
{
EmployeeID = user.UserName,
Name = user.RealName,
Role = await UserManager.GetRolesAsync(user.Id),
Email = user.Email,
Mobile = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.Mobile).FirstOrDefaultAsync(),
Tel = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.Tel).FirstOrDefaultAsync(),
AccountStatus = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.AccountStatus).FirstOrDefaultAsync(),
ModifiedDate = JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.ModifiedDate).FirstOrDefaultAsync(), js),
ManagerID = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.ManagerID).FirstOrDefaultAsync(),
CreateDate = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.CreateDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.CreateDate).FirstOrDefaultAsync(), js),
SendLetterDate = await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.SendLetterDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await db.Employee.Where(e => e.EmployeeID == user.UserName).Select(e => e.SendLetterDate).FirstOrDefaultAsync(), js),
LastPasswordChangedDate = await UserManager.Users.Where(e => e.UserName == user.UserName).Select(e => e.LastPasswordChangedDate).FirstOrDefaultAsync() == null ? null : JsonConvert.SerializeObject(await UserManager.Users.Where(e => e.UserName == user.UserName).Select(e => e.LastPasswordChangedDate).FirstOrDefaultAsync(), js),
//取得 所有Role列表，若有出現在該userA中 Selected = true
RolesList = RoleManager.Roles.ToList().Select(r => new
{
Selected = userRoles.Contains(r.Name),
Text = r.Description,
Value = r.Name
}),
EmailConfirm = user.EmailConfirmed
};
return(Json(userA, JsonRequestBehavior.AllowGet));
}
public static string GetTitle(this IIdentity identity)
{
if (identity == null)
{
return(string.Empty);
}
string id = identity.GetUserName();
//供應商帳號SE開頭
if (id.StartsWith("SE"))
{
return("供應商");
}
using (PMSAEntities db = new PMSAEntities())
{
Employee emp = db.Employee.Find(id);
return(emp.Title);
}
}
public static SupplierAccount GetSupplierAccount(this IIdentity identity)
{
if (identity == null)
{
return(null);
}
string id = identity.GetUserName();
//供應商帳號SE開頭
if (!id.StartsWith("SE"))
{
return(null);
}
using (PMSAEntities db = new PMSAEntities())
{
SupplierAccount sa = db.SupplierAccount.Find(id);
return(sa);
}
}
public static Employee GetEmployee(this IIdentity identity)
{
if (identity == null)
{
return(null);
}
string id = identity.GetUserName();
//員工帳號CE開頭
if (!id.StartsWith("CE"))
{
return(null);
}
using (PMSAEntities db = new PMSAEntities())
{
Employee emp = db.Employee.Find(id);
return(emp);
}
}
public IEnumerable <PurchaseOrderDtlItem> GetPurchaseOrderDtlList(string purchaseRequisitionID, string supplierCode)
{
IEnumerable <PurchaseOrderDtlItem> pods = null;
DateTime now = DateTime.Now;
//排除時間
now = new DateTime(now.Year, now.Month, now.Day);
//取得顯示資料
using (PMSAEntities db = new PMSAEntities())
{
var podq = from pr in db.PurchaseRequisition
join prd in db.PurchaseRequisitionDtl
on new { pr.PurchaseRequisitionID, ID = pr.PurchaseRequisitionID } equals
new { prd.PurchaseRequisitionID, ID = purchaseRequisitionID }
join sl in db.SourceList
on new { prd.PartNumber, SupplierCode = supplierCode } equals
new { sl.PartNumber, sl.SupplierCode }
join p in db.Part
on sl.PartNumber equals p.PartNumber
where !(from prdRel in db.PRPORelation
select prdRel.PurchaseRequisitionDtlCode).Contains(prd.PurchaseRequisitionDtlCode) &&
sl.SourceListDtl.Where(d => d.DiscountBeginDate <= now && d.DiscountEndDate >= now).Any()
orderby prd.PurchaseRequisitionDtlCode
select new PurchaseOrderDtlItem
{
PartNumber = prd.PartNumber,
PartName = p.PartName,
PartSpec = p.PartSpec,
QtyPerUnit = sl.QtyPerUnit,
OriginalUnitPrice = sl.UnitPrice,
Qty = sl.MOQ.HasValue && prd.Qty < sl.MOQ.Value ? sl.MOQ.Value : prd.Qty,
Discount = 0M,
DateRequired = prd.DateRequired,
SourceListID = sl.SourceListID,
PurchaseRequisitionDtlCode = prd.PurchaseRequisitionDtlCode
};
pods = podq.ToList();
}
//設定折扣
foreach (PurchaseOrderDtlItem item in pods)
{
using (PMSAEntities db = new PMSAEntities())
{
IEnumerable <SourceListDtl> sldq = db.SourceListDtl.Where(s =>
s.SourceListID == item.SourceListID &&
s.DiscountBeginDate <= now &&
s.DiscountEndDate >= now).OrderBy(o => o.QtyDemanded);
foreach (SourceListDtl sld in sldq)
{
if (item.Qty >= sld.QtyDemanded)
{
item.Discount = sld.Discount;
}
}
item.TotalPartQty = item.QtyPerUnit * item.Qty;
item.PurchaseUnitPrice = (int)Math.Ceiling(item.OriginalUnitPrice * (1 - item.Discount));
item.Total = item.PurchaseUnitPrice * item.Qty;
item.DateRequired = item.DateRequired.AddDays(-7);
}
}
//寫入暫存資料表
using (PMSAEntities db = new PMSAEntities())
{
//TODO: 多人新增相同請購單來源會有刪除同一筆資料的問題，請購單需要設定[新增中]的狀態
//移除現有資料
var rortq = db.PRPORelationTemp.Where(p => p.PurchaseRequisitionID == purchaseRequisitionID);
int?poOid = rortq.FirstOrDefault()?.PurchaseOrderOID;
if (poOid.HasValue)
{
db.PRPORelationTemp.RemoveRange(rortq);
var podtq = db.PurchaseOrderDtlTemp.Where(p => p.PurchaseOrderOID == poOid);
db.PurchaseOrderDtlTemp.RemoveRange(podtq);
var potq = db.PurchaseOrderTemp.Find(poOid);
db.PurchaseOrderTemp.Remove(potq);
db.SaveChanges();
}
//新增暫存資料
PurchaseOrderTemp pot = new PurchaseOrderTemp
{
SupplierCode = supplierCode,
EmployeeID = emp.EmployeeID,
CreateDate = DateTime.Now
};
db.PurchaseOrderTemp.Add(pot);
db.SaveChanges();
//更新暫存OID
foreach (var item in pods)
{
item.PurchaseOrderOID = pot.PurchaseOrderOID;
}
foreach (var item in pods)
{
PurchaseOrderDtlTemp podt = new PurchaseOrderDtlTemp
{
PurchaseOrderOID = pot.PurchaseOrderOID,
PartNumber = item.PartNumber,
PartName = item.PartName,
PartSpec = item.PartSpec,
QtyPerUnit = item.QtyPerUnit,
TotalPartQty = item.TotalPartQty,
OriginalUnitPrice = item.OriginalUnitPrice,
Discount = item.Discount,
PurchaseUnitPrice = item.PurchaseUnitPrice,
Qty = item.Qty,
PurchasedQty = 0,
GoodsInTransitQty = 0,
Total = item.Total,
SourceListID = item.SourceListID
};
db.PurchaseOrderDtlTemp.Add(podt);
db.SaveChanges();
item.PurchaseOrderDtlOID = podt.PurchaseOrderDtlOID;
PRPORelationTemp rort = new PRPORelationTemp
{
PurchaseRequisitionID = purchaseRequisitionID,
PurchaseRequisitionDtlCode = item.PurchaseRequisitionDtlCode,
PurchaseOrderOID = pot.PurchaseOrderOID,
PurchaseOrderDtlOID = podt.PurchaseOrderDtlOID
};
db.PRPORelationTemp.Add(rort);
db.SaveChanges();
}
}
return(pods);
}
public PurchaseOrdersController()
{
db = new PMSAEntities();
db.Database.Log = Console.Write;
session = PurchaseOrderCreateSession.Current;
}
public SupplierStocksController()
{
db = new PMSAEntities();
//supplierCode = "S00001";
//supplierAccount = "SE00001";
}
public PurchaseOrdersController()
{
db = new PMSAEntities();
db.Database.Log = Console.Write;
}
public ActionResult Create([Bind(Include = "PurchaseOrderOID,PurchaseRequisitionID,CheckedResultSetVM")] PurchaseOrderCreateViewModel model)
{
if (model == null || model.CheckedResultSetVM.Count(s => s.Checked) == 0)
{
TempData["ErrorMessage"] = "採購細項請至少勾選一項";
return(RedirectToAction("Create"));
}
//從暫存新增至正式資料表
DateTime now = DateTime.Now;
using (PMSAEntities db = new PMSAEntities())
{
//新增採購單
PurchaseOrderTemp pot = db.PurchaseOrderTemp.Find(model.PurchaseOrderOID);
db.Entry(pot).State = EntityState.Detached;
string poId = $"PO-{now:yyyyMMdd}-";
int count = db.PurchaseOrder.Where(i => i.PurchaseOrderID.StartsWith(poId)).Count();
count++;
poId = $"{poId}{count:000}";
PurchaseOrder po = new PurchaseOrder
{
PurchaseOrderID = poId,
SupplierCode = pot.SupplierCode,
EmployeeID = pot.EmployeeID,
CreateDate = now,
PurchaseOrderStatus = "N"
};
db.PurchaseOrder.Add(po);
db.SaveChanges();
//新增採購單明細
int index = 0;
foreach (var item in model.CheckedResultSetVM)
{
if (!item.Checked)
{
continue;
}
var podt = db.PurchaseOrderDtlTemp.Find(item.PurchaseOrderDtlOID);
index++;
PurchaseOrderDtl pod = new PurchaseOrderDtl
{
PurchaseOrderDtlCode = $"{poId}-{index:000}",
PurchaseOrderID = poId,
PartNumber = podt.PartNumber,
PartName = podt.PartName,
PartSpec = podt.PartSpec,
QtyPerUnit = podt.QtyPerUnit,
TotalPartQty = podt.TotalPartQty,
OriginalUnitPrice = podt.OriginalUnitPrice,
Discount = podt.Discount,
PurchaseUnitPrice = podt.PurchaseUnitPrice,
Qty = podt.Qty,
PurchasedQty = podt.PurchasedQty,
GoodsInTransitQty = podt.GoodsInTransitQty,
Total = podt.Total,
DateRequired = podt.DateRequired,
CommittedArrivalDate = podt.CommittedArrivalDate,
ShipDate = podt.ShipDate,
ArrivedDate = podt.ArrivedDate,
SourceListID = podt.SourceListID
};
db.PurchaseOrderDtl.Add(pod);
db.SaveChanges();
//請購單與採購單關聯
//TODO: 應從暫存取出，目前暫以傳入方式處理
PRPORelation rel = new PRPORelation
{
PurchaseOrderID = poId,
PurchaseOrderDtlCode = pod.PurchaseOrderDtlCode,
PurchaseRequisitionID = model.PurchaseRequisitionID,
PurchaseRequisitionDtlCode = item.PurchaseRequisitionDtlCode,
};
db.PRPORelation.Add(rel);
db.SaveChanges();
//採購單異動總表
POChanged poc = new POChanged
{
PurchaseOrderID = poId,
POChangedCategoryCode = "N",
RequestDate = now,
RequesterRole = "P",
RequesterID = pot.EmployeeID
};
db.POChanged.Add(poc);
db.SaveChanges();
//更新PurchaseOrderDtl.POChangedOID
pod.POChangedOID = poc.POChangedOID;
db.Entry(pod).Property(podp => podp.POChangedOID).IsModified = true;
db.SaveChanges();
}
//刪除暫存資料
var PRPORelationTemps = db.PRPORelationTemp.Where(i => i.PurchaseOrderOID == model.PurchaseOrderOID);
db.PRPORelationTemp.RemoveRange(PRPORelationTemps);
db.SaveChanges();
var PurchaseOrderDtlTemps = db.PurchaseOrderDtlTemp.Where(i => i.PurchaseOrderOID == model.PurchaseOrderOID);
db.PurchaseOrderDtlTemp.RemoveRange(PurchaseOrderDtlTemps);
var PurchaseOrderOld = db.PurchaseOrderTemp.Find(model.PurchaseOrderOID);
db.PurchaseOrderTemp.Remove(PurchaseOrderOld);
}
return(RedirectToAction("Index"));
}
public Repository(Employee emp, PMSAEntities ent)
{
this.emp = emp;
this.db = ent;
this.session = PurchaseOrderCreateSession.Current;
}