public Boolean ValidateLogin(String username, String password)
{
Boolean loginFlag = false;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
//Retrieve usrs whose username and password match db - Linq-to-Entities Query Syntax
var users = (from u in context.Usrs
where u.Username == username && u.Password == password
select u).ToList();
if (users.Count == 1)
{
loginFlag = true;
}
else
{
loginFlag = false;
}
}
return(loginFlag);
}
public void InsertInitialFormItems(ICollection <FormItems> formItemList, int msrId)
{
using (var context = new MSR_MaxEntities())
{
foreach (Domain.FormItems item in formItemList)
{
var tempFormItem = new FormItem
{
ItemCode = item.ItemCode,
ItemDesc = item.ItemDesc,
Quantity = Double.Parse(item.Quantity),
Unit = item.Unit,
UnitPrice = String.IsNullOrEmpty(item.UnitPrice) ? (double?)null : Convert.ToDouble(item.UnitPrice),
Currency = item.Currency,
ROS_Date = item.ROSDate,
Comments = item.Comments,
MSRId = msrId,
AC_No = item.ACNo
};
context.FormItems.Add(tempFormItem);
}
context.SaveChanges();
}
}
internal void UpdateMSRNeedReview(int msrId, String stateFlag)
{
using (var context = new MSR_MaxEntities())
{
var msr_db = context.MSRs.Find(msrId);
if (msr_db != null)
{
msr_db.StateFlag = stateFlag;
context.SaveChanges();
}
}
}
public void UpdateMSRWaitForApproval(int msrId, String path, String commentFromPath, DateTime approvalDate, String stateFlag)
{
if (path.Equals("Approve"))
{
using (var context = new MSR_MaxEntities())
{
var msr_db = context.MSRs.Find(msrId);
if (msr_db != null)
{
msr_db.Appr_Date = approvalDate;
msr_db.StateFlag = stateFlag;
context.SaveChanges();
}
}
}
else if (path.Equals("Send for Review"))
{
using (var context = new MSR_MaxEntities())
{
var msr_db = context.MSRs.Find(msrId);
if (msr_db != null)
{
msr_db.Review_Comment = commentFromPath;
msr_db.StateFlag = stateFlag;
context.SaveChanges();
}
}
}
else if (path.Equals("Decline"))
{
using (var context = new MSR_MaxEntities())
{
var msr_db = context.MSRs.Find(msrId);
if (msr_db != null)
{
msr_db.Decline_Comment = commentFromPath;
msr_db.StateFlag = stateFlag;
context.SaveChanges();
}
}
}
else
{
}
}
public String GetOriginatorID(String msrId)
{
String OgId = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
var msr_db = context.MSRs.Find(Int32.Parse(msrId));
OgId = msr_db.Usr_RO.ToString();
}
return(OgId);
}
public void DeleteFormItemsByMSRId(String msrId)
{
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
var msr_db = context.MSRs
.Include("FormItems")
.Where(x => x.MSRId.ToString().Equals(msrId))
.First();
var formItemsToDelete = msr_db.FormItems;
context.FormItems.RemoveRange(formItemsToDelete);
context.SaveChanges();
}
}
public DateTime GetDateTime()
{
DateTime dateTime = DateTime.MinValue;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
var dateTimeVar = (from dt in context.Database.SqlQuery <DateTime>("Select Getdate() AS DateTime")
select dt).FirstOrDefault();
dateTime = dateTimeVar;
}
return(dateTime);
}
public ICollection <V_BP_AC_DEPT> GetBudgetInfoAccessByDeptId(int deptId)
{
ICollection <V_BP_AC_DEPT> v_BP_DEPT_List = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
var v_BP_DEPT_db = (from v in context.V_BP_AC_DEPT
where v.DeptId == deptId
select v).ToList();
v_BP_DEPT_List = v_BP_DEPT_db;
}
return(v_BP_DEPT_List);
}
public void InsertIntialMSR(Domain.ApproverInfo approverInfo, String project, String vwl, String comments, String budgetYear, String bpNo, String afe, String sugVendor, String contactVendor, DateTime reqDate)
{
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
//First obtain referenced users
var usr_RO = context.Usrs.Find(BusinessAPI.BusinessSingleton.Instance.userInfo_EF.UserId);
var usr_CA = context.Usrs.Find(Int32.Parse(approverInfo.UserId));
//Initialize new MSR
var tempMSR = new MSR
{
Project = project,
WVL = vwl,
Comments = comments,
BudgetYear = Int32.Parse(budgetYear),
BP_No = bpNo,
AFE = afe,
SugVendor = sugVendor,
ContactVendor = contactVendor,
Usr_RO = usr_RO,
Usr_CA = usr_CA,
Req_Date = reqDate,
PUR_Comment = "",
Decline_Comment = "",
Review_Comment = "",
StateFlag = Domain.WorkFlowTrace.WAIT_FOR_APPROVAL
};
//Adds the newly created MSR
context.MSRs.Add(tempMSR);
context.SaveChanges();
//Gets the just inserted MSR
int MSRId = tempMSR.MSRId;
//Attaches the FormItems to that MSR just created
InsertInitialFormItems(BusinessAPI.BusinessSingleton.Instance.formItemListCreateMSR, MSRId);
}
}
public ICollection <Domain.ShowMSRItem> GetShowMSRListProcure(String stateFlag)
{
ICollection <Domain.ShowMSRItem> showMSRData = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
showMSRData = new List <Domain.ShowMSRItem>();
var showMSRData_db = (from d in context.V_ShowMSR
where (d.StateFlag.Equals(stateFlag))
select new Domain.ShowMSRItem {
MSRId = d.MSRId.ToString(), BpNo = d.BP_No, DeptName = d.DeptName, Originator = d.Originator, Approver = d.Approver, ReqDate = d.Req_Date, Comments = d.Comments, ApprDate = d.Appr_Date.ToString()
}).ToList();
showMSRData = showMSRData_db;
}
return(showMSRData);
}
public ICollection <V_BH_BI> GetACAccessByBPList(ICollection <V_BP_AC_DEPT> v_BP_DEPT_List)
{
ICollection <V_BH_BI> v_BH_BI_Access_List = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
//Grab the list from context first
ICollection <V_BH_BI> v_BH_BI_db = context.V_BH_BI.ToList();
//Perform the match between two lists
ICollection <V_BH_BI> v_BH_BI_Access_temp = v_BH_BI_db.Where(x =>
v_BP_DEPT_List.Any(y => y.BP_No.Equals(x.BP_No))).ToList();
v_BH_BI_Access_List = v_BH_BI_Access_temp;
}
return(v_BH_BI_Access_List);
}
public MSR GetMSRByMSRId(String msrId)
{
MSR msr = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
var msr_db = context.MSRs
.Include("FormItems")
.Include("Usr_RO")
.Include("Usr_CA")
.Include("Usr_RecieveBy")
.Where(x => x.MSRId.ToString().Equals(msrId))
.First();
msr = msr_db;
}
return(msr);
}
public ICollection <Domain.StockItems> GetStockItemList(String bpNo)
{
ICollection <Domain.StockItems> stockItemData = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
stockItemData = new List <Domain.StockItems>();
var stockItemData_db = (from s in context.V_StockItem_BudgetInfo
where s.BP_No.ToString().Equals(bpNo)
select new Domain.StockItems {
BudgetPool = s.BP_No, ItemCode = s.ItemCode, ItemDesc = s.ItemDesc, LookUp = s.LookUp, BarCode = s.BarCode, ACNo = s.AC_No, Unit = s.Unit, ActiveFlag = s.ActiveFlag.ToString()
}).ToList();
stockItemData = stockItemData_db;
}
return(stockItemData);
}
public ICollection <Domain.ApproverInfo> GetBudgetHolderList(String bpNo)
{
ICollection <Domain.ApproverInfo> budgetHolderData = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
budgetHolderData = new List <Domain.ApproverInfo>();
var budgetHolderData_db = (from a in context.V_Approver_BP
where a.BP_No.Equals(bpNo)
select new Domain.ApproverInfo {
UserId = a.UserId.ToString(), Username = a.Username, FullName = a.FullName, Email = a.Email, DeptId = a.DeptId.ToString()
}).ToList();
budgetHolderData = budgetHolderData_db;
}
return(budgetHolderData);
}
public Domain.ApproverInfo GetApproverInfoByUsername(String username)
{
Domain.ApproverInfo approverInfo = null;
using (var context = new MSR_MaxEntities())
{
Usr usr_db = context.Usrs.SingleOrDefault(u => u.Username.Equals(username));
Domain.ApproverInfo approverInfoTemp = new Domain.ApproverInfo
{
UserId = usr_db.UserId.ToString(),
Username = usr_db.Username,
FullName = usr_db.FullName,
Email = usr_db.Email,
DeptId = usr_db.DeptId.ToString()
};
approverInfo = approverInfoTemp;
}
return(approverInfo);
}
public Usr GetUsrByUsername(String username)
{
Usr usr = null;
using (var context = new MSR_MaxEntities())
{
//Log DB commands to console
context.Database.Log = Console.WriteLine;
//Retrieve usrs whose username and password match db - Linq-to-Entities Query Syntax
var usr_db = (from u in context.Usrs
.Include("Department")
.Include("Group")
.Include("BudgetInfoes")
where u.Username == username
select u).FirstOrDefault <Usr>();
usr = usr_db;
}
return(usr);
}