//查詢公司預算/實際數
public ActionResult SearchExpSummary()
{
List <ExpenseBudgetSummary> ExpenseSummary = null;
List <ExpenseBudgetSummary> BudgetSummary = null;
ExpenseBudgetSummary Amt = null;
ExpenseBudgetSummary ExpAmt = null;
ExpenseBudgetModel viewModel = new ExpenseBudgetModel();
if (null != Request["budgetyear"])
{
//取得預算數
BudgetSummary = service.getExpBudgetByYear(int.Parse(Request["budgetyear"]));
//取得發生數、
ExpenseSummary = service.getExpSummaryByYear(int.Parse(Request["budgetyear"]));
ExpAmt = service.getTotalOperationExpAmount(int.Parse(Request["budgetyear"]));
Amt = service.getTotalExpBudgetAmount(int.Parse(Request["budgetyear"]));
viewModel.BudgetSummary = BudgetSummary;
viewModel.ExpenseSummary = ExpenseSummary;
TempData["TotalAmt"] = String.Format("{0:#,##0.#}", Amt.TOTAL_BUDGET);
TempData["TotalExpAmt"] = String.Format("{0:#,##0.#}", ExpAmt.TOTAL_OPERATION_EXP);
TempData["budgetYear"] = Request["budgetyear"];
return(View("OperationExpSummary", viewModel));
}
TempData["budgetYear"] = Request["budgetyear"];
return(View("OperationExpSummary"));
}
//取得特定年度之公司費用總預算金額
public ExpenseBudgetSummary getTotalExpBudgetAmount(int year)
{
ExpenseBudgetSummary lstAmount = null;
using (var context = new TopmepEntities())
{
lstAmount = context.Database.SqlQuery <ExpenseBudgetSummary>("SELECT SUM(AMOUNT) AS TOTAL_BUDGET FROM FIN_EXPENSE_BUDGET WHERE BUDGET_YEAR = @year "
, new SqlParameter("year", year)).FirstOrDefault();
}
return(lstAmount);
}
//取得特定專案之工地費用總預算金額
public ExpenseBudgetSummary getSiteBudgetAmountById(string projectid, string budgetYear)
{
ExpenseBudgetSummary lstAmount = null;
string sql = @"SELECT SUM(AMOUNT) AS TOTAL_BUDGET FROM PLAN_SITE_BUDGET
WHERE PROJECT_ID = @projectid
AND (@budgetYear IS NULL OR BUDGET_YEAR=@budgetYear)";
using (var context = new TopmepEntities())
{
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectid", projectid));
parameters.Add(new SqlParameter("budgetYear", (object)budgetYear ?? DBNull.Value));
lstAmount = context.Database.SqlQuery <ExpenseBudgetSummary>(sql, parameters.ToArray()).FirstOrDefault();
}
return(lstAmount);
}
//取得特定專案之工地費用總執行金額
public ExpenseBudgetSummary getTotalSiteExpAmountById(string projectid, string targetYear)
{
string sql = @"SELECT SUM(ei.AMOUNT) AS CUM_YEAR_AMOUNT FROM FIN_EXPENSE_ITEM ei
LEFT JOIN FIN_EXPENSE_FORM ef
ON ei.EXP_FORM_ID = ef.EXP_FORM_ID WHERE ef.PROJECT_ID = @projectid
AND ef.STATUS=@status
AND (@targetYear is Null OR ef.OCCURRED_YEAR = @targetYear)";
ExpenseBudgetSummary lstExpAmount = null;
using (var context = new TopmepEntities())
{
log.Debug("sql=" + sql + ",project_id=" + projectid + ",year=" + targetYear);
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("projectid", projectid));
parameters.Add(new SqlParameter("status", "30"));
parameters.Add(new SqlParameter("targetYear", (object)targetYear ?? DBNull.Value));
lstExpAmount = context.Database.SqlQuery <ExpenseBudgetSummary>(sql, parameters.ToArray()).FirstOrDefault();
}
return(lstExpAmount);
}
//查詢公司預算
public ActionResult Search()
{
List <ExpenseBudgetSummary> ExpBudget = null;
//List<ExpenseBudgetByMonth> BudgetByMonth = null;
ExpenseBudgetModel viewModel = new ExpenseBudgetModel();
ExpenseBudgetSummary Amt = null;
if (null != Request["budgetyear"])
{
//年度預算月分配
ExpBudget = service.getExpBudgetByYear(int.Parse(Request["budgetyear"]));
//年度預算月分配總和
//BudgetByMonth = service.getExpBudgetOfMonthByYear(int.Parse(Request["budgetyear"]));
Amt = service.getTotalExpBudgetAmount(int.Parse(Request["budgetyear"]));
viewModel.BudgetSummary = ExpBudget;
//viewModel.budget = BudgetByMonth;
TempData["TotalAmt"] = Amt.TOTAL_BUDGET;
TempData["budgetYear"] = Request["budgetyear"];
return(View("ExpenseBudget", viewModel));
}
TempData["budgetYear"] = Request["budgetyear"];
return(View("ExpenseBudget"));
}
