public static bool UpdateUserById(int Id, bool status)
{
try
{
bool result = false;
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var usr = (from p in db.AppUsers
where p.ID == Id
select p).FirstOrDefault();
if (status)
{
usr.isActive = false;
}
else
{
usr.isActive = true;
}
db.SaveChanges();
result = true;
}
return(result);
}
catch (Exception ex)
{
Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static IEnumerable <DirectBudgetATC> GetDirectBudgetATCList(string batchId = "")
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var result = db.DirectBudgetATCs.Include("Department").OrderBy(o => o.BudgetItem).AsQueryable();
if (!string.IsNullOrEmpty(batchId))
{
result = result.Where(r => r.ATCBatchId == batchId).AsQueryable();
}
return(result.ToList());
//if (deptId == 0)
//{
// return db.IndirectBudgets.Include("BudgetYear").Include("IndirectBudgetItem.IndirectType").Include("Department").Where(d => d.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).ToList();
//}
//else
//{
// return db.IndirectBudgets.Include("BudgetYear").Include("IndirectBudgetItem.IndirectType").Include("Department").Where(j => j.DepartmentID == deptId && j.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).ToList();
//}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static IEnumerable <GSS_SalesTbl> GetMySalesList(int userId, int status, DateTime filter, int cust = 0)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
if (status == (int)Utility.SalesStatus.Pending_Capture_Approval)
{
var result = db.GSS_SalesTbl.Include("GSS_Customer").Include("AppUser").Where(p => p.CapturedBy == userId && p.Status == status).OrderByDescending(o => o.DateCaptured).AsQueryable();
if (cust != 0)
{
result = result.Where(c => c.CustomerID == cust).AsQueryable();
}
if (DateTime.Now.AddDays(31) >= filter)
{
result = result.Where(c => c.DateCaptured >= filter && c.DateCaptured <= filter).AsQueryable();
}
return(result.ToList());
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static string GetUsersEmailAdd(string RoleName, int DeptID = 0)
{
try
{
string to = "";
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var urs = db.AppUsers.Where(u => u.UserRole == RoleName).ToList();
//select u;
if (DeptID != 0)
{
urs = urs.Where(p => p.DepartmentID == DeptID).ToList();
}
if (urs.Count() > 0)
{
foreach (AppUser us in urs)
{
to += us.Email + ",";
}
to = to.TrimEnd(new char[] { ',' });
}
}
return(to);
}
catch (Exception ex)
{
throw ex;
}
}
public static IEnumerable <BudgetSummaryView> GetBudgetSummaryList(int budgetYrID, int deptId = 0, int dirID = 0)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var result = db.BudgetSummaryViews.Include("BudgetYear").Include("Department").Where(d => d.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).AsQueryable();
if (deptId != 0)
{
result = result.Where(j => j.DepartmentID == deptId).OrderBy(o => o.BudgetElement).AsQueryable();
}
if (dirID != 0)
{
result = result.Where(j => j.Department.DirectorateID == dirID).OrderBy(o => o.BudgetElement).AsQueryable();
}
return(result.ToList());
//else
//{
// return db.BudgetSummaryViews.Include("BudgetYear").Include("Department").Where(j => j.DepartmentID == deptId && j.BudgetYrID == budgetYrID).OrderBy(o => o.BudgetElement).ToList();
//}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static IEnumerable <CapexBudget> GetCapexBudgetList(int budgetYrID, int deptId = 0, string batchId = "")
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var result = db.CapexBudgets.Include("BudgetYear").Include("CapexExpenseItem.CapexType").Include("Department").Where(d => d.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).AsQueryable();
if (deptId != 0)
{
result = result.Where(r => r.DepartmentID == deptId).AsQueryable();
}
if (!string.IsNullOrEmpty(batchId))
{
result = result.Where(r => r.ATCBatchID == batchId).AsQueryable();
}
return(result.ToList());
//if (deptId == 0)
//{
// return db.CapexBudgets.Include("BudgetYear").Include("CapexExpenseItem.CapexType").Include("Department").Where(d => d.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).ToList();
//}
//else
//{
// return db.CapexBudgets.Include("BudgetYear").Include("CapexExpenseItem.CapexType").Include("Department").Where(j => j.DepartmentID == deptId && j.BudgetYrID == budgetYrID).OrderBy(o => o.Department.Name).ToList();
//}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static IEnumerable <ExistingStaff> GetExistStaffList(int budgetYrID, int deptId = 0, string batchId = "")
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var result = db.ExistingStaffs.Include("BudgetYear").Include("Grade").Include("Department").Where(d => d.BudgetYrID == budgetYrID).OrderByDescending(o => o.Grade.CostPerMonth).AsQueryable();
if (deptId != 0)
{
result = result.Where(r => r.DepartmentID == deptId).AsQueryable();
}
if (!string.IsNullOrEmpty(batchId))
{
result = result.Where(r => r.ATCBatchID == batchId).AsQueryable();
}
return(result.ToList());
//if (deptId == 0)
//{
// return db.ExistingStaffs.Include("BudgetYear").Include("Grade").Include("Department").Where(d=>d.BudgetYrID==budgetYrID).OrderByDescending(o => o.Grade.CostPerMonth).ToList();
//}
//else
//{
// return db.ExistingStaffs.Include("BudgetYear").Include("Grade").Include("Department").Where(j => j.DepartmentID == deptId && j.BudgetYrID==budgetYrID).OrderByDescending(o => o.Grade.CostPerMonth).ToList();
//}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static ExistingStaff GetExistingStaff(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.ExistingStaffs.Include("Grade").Where(p => p.ID == Id).FirstOrDefault());;
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Department GetDepartment(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.Departments.Find(Id));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Directorate GetDirectorate(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.Directorates.Find(Id));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static IEnumerable <Directorate> GetDirectorateList()
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.Directorates.OrderBy(o => o.Name).ToList());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static GSS_SalesTbl GetGasSale(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.GSS_SalesTbl.Find(Id));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static AssetBudget GetAssetBudget(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.AssetBudgets.Include("Asset").Where(k => k.ID == Id).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static ExpenseProjection GetExpensePRojection(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.ExpenseProjections.Find(Id));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static CapexBudget GetCapexBudget(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.CapexBudgets.Include("CapexExpenseItem").Where(k => k.ID == Id).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static DirectBudgetATC GetDirectBudgetATC(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.DirectBudgetATCs.Where(k => k.ID == Id).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static ATCRequestHeader GetATCHeader(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.ATCRequestHeaders.Include("Department").Include("BudgetMenuItem").Where(k => k.ID == Id).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static RevenueProjection GetRevenue(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.RevenueProjections.Include("CustomerType").Where(k => k.ID == Id).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public static AppUser GetByID(int Id)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
return(db.AppUsers.Find(Id));
}
}
catch (Exception ex)
{
Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static List <AppUser> GetUsersList()
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var usrList = from p in db.AppUsers.Include("Department").Include("Department.Directorate") select p;
return(usrList.ToList <AppUser>());
}
}
catch (Exception ex)
{
Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static AppUser GetUserByUserName(string username)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
var user = from usr in db.AppUsers.Include("Department") where usr.StaffID == username
select usr;
return(user.FirstOrDefault <AppUser>());
}
}
catch (Exception ex)
{
Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddDirectExpenseATC(DirectBudgetATC astBud)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.DirectBudgetATCs.Add(astBud);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddATCHeader(ATCRequestHeader astBud)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.ATCRequestHeaders.Add(astBud);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddDepartment(Department dept)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.Departments.Add(dept);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddDirectorate(Directorate dir)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.Directorates.Add(dir);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddExistingStaff(ExistingStaff estf)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.ExistingStaffs.Add(estf);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddRevenue(RevenueProjection recpro)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.RevenueProjections.Add(recpro);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddSalesData(GSS_SalesTbl byr)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.GSS_SalesTbl.Add(byr);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
// Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool AddUser(AppUser usr)
{
try
{
using (BudgetCaptureDBEntities db = new BudgetCaptureDBEntities())
{
db.AppUsers.Add(usr);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
Utility.WriteError("Error Msg: " + ex.Message);
throw ex;
}
}
public static bool DeleteGasSales(GSS_SalesTbl staff)
{
try
{
bool rst = false;
using (var db = new BudgetCaptureDBEntities())
{
db.Entry(staff).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
rst = true;
}
return(rst);
}
catch (Exception ex)
{
throw ex;
}
}