今天好! 我只想知道如何根據文本框StartDate和EndDate中的輸入，將特定資料庫列中的所有數值相加/求和？文本框 "dayworked "只顯示根據值和EmployeeId的總和，但不顯示根據StartDate和EndDate的總和。我想根據EmployeeId、StartDate和EndDate來顯示一個特定資料庫列的總和。
這是我的視圖，我創建的腳本將發布我的總和和Employeeid。
@using PayrollWeb.Models
@model PayrollWeb.Models.PayrollFormModel
@{
ViewBag.Title = "AddOrEdit"/span>。
Layout = "~/Views/Shared/_Layout.cshtml" ;
}
@using (Html. BeginForm("AddOrEdit"/span>, "Payroll"/span>, FormMethod. Post, new { onsubmit = "return SubmitFormPayroll(this)" }）)
{
@Html.HiddenFor(model => model.Id)
<h1 style="text-align: center;"><span class="fa fa-calculator"></span> Payroll< /h1>
<br />
<div class="panel-group col-md-12" >
<div class="panel panel-primary" >
<div class="panel-heading">
Employee Details
</div>
<div class="panel-body" >
<div class="row">
<div class="col-md-3" >
<div class="form-group">
@Html.LabelFor(x => x. EmployeeId, new { @class = "form-label" })
@Html.DropDownListFor(x => x.EmployeeId, Model。 Employees, "", new { @class = "form-control" , @id="employeeid"})
@Html.ValidationMessageFor(x => x. EmployeeId, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-3">
<div class="form-group">
@Html.LabelFor(x => x. StartDate, new { @class = "form-label" })
@Html.TextBoxFor(x => x. StartDate, new { @class = "form-control"。@id = "startdate", @type = "date" })
@Html.ValidationMessageFor（x => x. StartDate, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-3">
<div class="form-group">
@Html.LabelFor(x => x. EndDate, new { @class = "form-label" })
@Html.TextBoxFor(x => x. EndDate, new { @class = "form-control"。@id = "enddate", @type = "date" })
@Html.ValidationMessageFor（x => x. EndDate, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-3">
<div class="form-group">
@Html.LabelFor(x => x. PayDate, new { @class = "form-label" })
@Html.TextBoxFor(x => x. PayDate, new { @class = "form-control"。@id = "datepickertodin", @type = "date" })
@Html.ValidationMessageFor（x => x. PayDate, null, new { @class = "text-danger" })
</div>
</div>
</div>
<div class="row">
<div class="col-md-3" >
<div class="form-group">
@Html.LabelFor(x => x. PayFrequency, new { @class = "form-label" })
@Html.TextBoxFor(x => x. PayFrequency, new { @class = "form-control"。@Value = "BI-WEEKLY", @readonly = "readonly" })
@Html.ValidationMessageFor(x => x. PayFrequency, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-3">
<div class="form-group">
@Html.LabelFor(x => x. DaysWorked, new { @class = "form-label" })
@Html.TextBoxFor(x => x. DaysWorked, new { @class = "form-control", @id="dayworked" })
@Html.ValidationMessageFor（x => x. DaysWorked, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-3">
<div class="form-group">
@Html.LabelFor(x => x. MonthlyRate, new { @class = "form-label" })
@Html.TextBoxFor(x => x. MonthlyRate , new { @class = "form-control" , @id="monthlyrate"})
@Html.ValidationMessageFor(x => x. MonthlyRate, null, new { @class = "text-danger" })
</div>
</div>
</div>
</div>
</div>
</div>
@*<div class="form-group"/span>>
@Html.LabelFor(x => x. SalaryPerDay, new { @class = "form-label" })
@Html.TextBoxFor(x => x. SalaryPerDay, new { @class = "form-control" , @id = "salaryperday" })
</div>
<div class="form-group">
@Html.LabelFor(x => x. WorkDays, new { @class = "form-label" })
@Html.TextBoxFor(x => x. WorkDays, new { @class = "form-control", @id = "workdays" })
</div>*@
<div class="panel-group col-md-4"/span>>
<div class="panel panel-primary" >
<div class="panel-heading">
Income
</div>
<div class="panel-body"/span>>
<div class="row">
<div class="col-md-6"> </div>
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(x => x. BasicPayAmount, new { @class = "form-label" })
@Html.TextBoxFor(x => x. BasicPayAmount, new { @class = "form-control", @id = "basicpayamount" })
@Html.ValidationMessageFor（x => x. BasicPayAmount, null, new { @class = "text-danger" })
</div>
</div>
</div>
</div>
</div>
</div>
<div class="panel-group col-md-4"/span>>
<div class="panel panel-primary" >
<div class="panel-heading">
Deductions
</div>
<div class="panel-body"/span>>
<div class="form-group">
@Html.LabelFor(x => x. WitholdingTax, new { @class = "form-label" })
@Html.TextBoxFor(x => x. WitholdingTax, new { @class = "form-control" })
@Html.ValidationMessageFor(x => x. WitholdingTax, null, new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(x => x. PagibigContribution, new { @class = "form-label" })
@Html.TextBoxFor(x => x. PagibigContribution, new { @class = "form-control", @Value = "100. 00", @readonly = "readonly" })
@Html.ValidationMessageFor(x => x. PagibigContribution, null, new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(x => x. PhilhealthContribution, new { @class = "form-label" })
@Html.TextBoxFor(x => x. PhilhealthContribution, new { @class = "form-control", @Value = "191. 25", @readonly = "readonly" })
@Html.ValidationMessageFor(x => x. PhilhealthContribution, null, new { @class = "text-danger" })
</div>
<div class="form-group">
@Html.LabelFor(x => x. SSSContribution, new { @class = "form-label" })
@Html.TextBoxFor(x => x. SSSContribution, new { @class = "form-control" , @Value = "560. 00", @readonly = "readonly" })
@Html.ValidationMessageFor(x => x. SSSContribution, null, new { @class = "text-danger" })
</div>
<div class="row">。
<div class="col-md-6" >
<div class="form-group">
@Html.LabelFor(x => x. Deduction, new { @class = "form-label" })
@Html.TextBoxFor(x => x. Deduction, new { @class = "form-control"。@id = "deduction" })
@Html.ValidationMessageFor（x => x. Deduction, null, new { @class = "text-danger" })
</div>
</div>
<div class="col-md-6">
< 輸入id="btnAdd" type="button" value="Add" onclick="AddTextBox()" class="btn btn-info"/span> style="margin-top: 25px;" />
</div>
</div>
<div id="TextBoxContainer" style="margin-top: 6%; ">
<!--Textboxes將被添加到這里-->。
</div>
<br />
<div class="form-group"/span>>
@Html.LabelFor(x => x. CashLoan, new { @class = "form-label" })
@Html.TextBoxFor(x => x. CashLoan, new { @class = "form-control" })
@Html.ValidationMessageFor(x => x. CashLoan, null, new { @class = "text-danger" })
</div>
</div>
</div>
</div>
<div class="panel-group col-md-4"/span>>
<div class="panel panel-primary" >
<div class="panel-heading">
Summary>
</div>
<div class="panel-body"/span>>
<div class="form-group">
@Html.LabelFor(x => x. TotalSalary, new { @class = "form-label" })
@Html.TextBoxFor(x => x. TotalSalary, new { @class = "form-control"。@id = "totalalsalary" })
@Html.ValidationMessageFor（x => x. TotalSalary, null, new { @class = "text-danger" })
</div>
</div>
</div>
</div>
<div class="row"/span>>
<div class="col-md-4"> </div>
<div class="col-md-4"> </div>
<div class="col-md-2"> </div>
<div class="col-md-2">
<div class="form-group">
< 輸入 type="submit" value="submit" class="btn btn-primary" />
< 輸入 type="reset" value="Reset" class="btn btn-primary" />
</div>
</div>
</div>
<a href="@Url. Action("Index", "Payroll")"> < span class="fa fa-backward"/span>> </span> Back to Dashboard</a>
<腳本 type="text/javascript"/span>>
功能 GetDynamicTextBox(value) {
var div = $("<div />").attr("class"/span>, "row"/span>) attr("style","margin-top: 6px;") 。
var textBox = $("<input />").attr("type", "textbox") 。 attr("name", "DynamicTextBox").attr("style", "width:30%;margin-left:5%;" ) 。
textBox.val（value）。
div.append(textBox)。
var textBox1 = $("<input />").attr("type", "textbox") 。 attr("name", "DynamicTextBox1").attr("style", "width:30%;margin-left:5%;" ) 。
textBox1.val（value）。
div.append(textBox1)。
var button = $("<input />").attr("type", "button") 。 attr("value", "Remove").attr("style", "width:20%;margin-left:5%;" );
button.attr("onclick", "RemoveTextBox（this）")。
div.append(button)。
return div;
}
功能 AddTextBox() {
var div = GetDynamicTextBox("") 。
$("#TextBoxContainer").append（div）。
}
功能 RemoveTextBox(button) {
$(button).parent().remove()。
}
$('#employeeid').change( function () {
var employeeid = $（this）.val（）。
//var startdatedata = $('#startdate').val();.
//var enddatedata = $('#enddate').val();
$.ajax({
type。'POST',
url: "@Url.Action("LoadDetails", "Payroll") "。
資料。{
employeeid:employeeid。
//startdatedata: startdatedata,.
//enddatedata: enddatedata。
},
dataType。'json',
success: function (data) {
$('#employeeid').val（data.EmployeeId）。
//$('#startdate').val(data.StartDate);.
//$('#enddate').val(data.EndDate);
$('#daysworked').val（data.DaysWorked）。
}
});
});
@*$(function () {
var values = eval('@Html.Raw(ViewBag.Values) ')。
if (values != null) {
$("#TextBoxContainer").html（""）。
$(value).each(function () {
$("#TextBoxContainer").append(GetDynamicTextBox(this) 。)
});
}
});*@
//$(function () {)
//$('#datepickerto').datepicker({ //datepicker startdate)
//dateFormat: "yy/mm/dd",
// changeMonth: true,
// changeYear: true,
// showOn: "both",
// buttonText : "<i class='fa fa-calendar'></i>"
// });
//});
//$(function () {
//$('#datepickertodin').datepicker({ //datepicker enddate)
//dateFormat: "yy/mm/dd",
// changeMonth: true,
// changeYear: true,
// showOn: "both",
// buttonText: "<i class='fa fa-calendar'></i>"/span>
// });
//});
//$(function () { //calculate)
//$("#salaryperday,#workdays,#deduction").keyup(function (e) {)
//var salary = $("#salaryperday").val();
//var work = $("#workdays").val();
//var deduction = $("#deduction").val();
//var result = "";
// if (salary !=="" && work !=="" && deduction !=="" && $.isNumeric(salary) && $.isNumeric(work) && $.isNumeric(deduction) ) {
// result = parseFloat(salary) * parseFloat(work) - parseFloat(deduction);
// }
// $("#totalsalary").val(result);
// });
//});
$(function () { //計算
$("#monthlyrate"/span>).keyup(function (e) {
var monthlyrate = $("#monthlyrate").val() 。
var result = ""。
if (monthlyrate !== " && $.isNumeric(monthlyrate)) {
result = parseFloat(monthlyrate) / 2;
}
$("#basicpayamount").val（結果）。
});
});
</script>
}
這是我的PayrollController，我有我的LoadDetails函式，我的linq.
。using PayrollWeb.DB.Core;
using PayrollWeb.DB.Data;
using System.Collections.Generic;
using System.Data.Entity.Migrations;
using System.Web.Script.Serialization;
namespacePayrollWeb.Controllers
{
public class PayrollController : Controller
{
// GET: Payroll
private PayrollwebContext db = new PayrollwebContext(); //connection
public ActionResult Index()
{
returnView（）。
}
public JsonResult GetData()
{
var payrolls = (from p in db.Payrolls.ToList()
join e in db.Employees.ToList() on p.EmployeeIdequals e.Id
select new PayrollListModel
{
Id = p.Id,
EmployeeName = e.FirstName " "/span> e.MiddleName " "/span> e.LastName。
StartDate = Convert.ToDateTime(p.StartDate),
EndDate = Convert.ToDateTime(p.EndDate),
Deduction = p.Deduction,
總薪資 = p.總薪資
});
return Json(new { data = payrolls.ToList() }, JsonRequestBehavior.AllowGet) 。
}
public ActionResult AddOrEdit(int? id)
{
var model = new PayrollFormModel() 。
if (id != null)
model.Id = Convert.ToInt32(id);
model = PreparePayrollFormModel(model);
return View（model）。
}
//[Authorize(Roles = "Admin")]
[HttpPost] 。
public ActionResult AddOrEdit(PayrollFormel model, string[] DynamicTextBox, int[] DynamicTextBox1)
{
JavaScriptSerializer serializer = new JavaScriptSerializer（）。
ViewBag.Values = serializer.Serialize(DynamicTextBox);
ViewBag.Values = serializer.Serialize(DynamicTextBox1);
//bool returnloop = false;
//foreach (string textboxValue in DynamicTextBox) //category
//{
//foreach (int textboxValue1 in DynamicTextBox1) //amount
//{>
using (var db = new PayrollwebContext()
{
if (model.Id > 0)
{ // update (model.Id > 0)
var EmployeePayroll = db.Payrolls.FirstOrDefault(x => x.Id == model.Id)。
EmployeePayroll.Id = model.Id;
EmployeePayroll.EmployeeId = (int)model.EmployeeId;
EmployeePayroll.StartDate = model.StartDate;
EmployeePayroll.EndDate = model.EndDate;
EmployeePayroll.PayFrequency = model.PayFrequency;
EmployeePayroll.PayDate = model.PayDate;
EmployeePayroll.DaysWorked = model.DaysWorked;
EmployeePayroll.MonthlyRate = model.MonthlyRate;
EmployeePayroll.WitholdingTax = model.WitholdingTax;
EmployeePayroll.PagibigContribution = model.PagibigContribution;
EmployeePayroll.PhilhealthContribution = model.PhilhealthContribution;
EmployeePayroll.SSSContribution = model.SSSContribution;
//EmployeePayroll.SalaryPerDay = model.SalaryPerDay;
//EmployeePayroll.WorkDays = model.WorkDays;
EmployeePayroll.Deduction = model.Deduction;
EmployeePayroll.CashLoan = model.CashLoan;
EmployeePayroll.TotalSalary = model.TotalSalary;
db.Payrolls.Add或Update(EmployeePayroll)。
db.SaveChanges()。
return Json(new { success = true, message = "Update Successfully" }, JsonRequestBehavior.AllowGet) 。
}
else
{ //插入
var payrolls = new Payroll
{
EmployeeId = (int)model.EmployeeId。
StartDate = model.StartDate。
EndDate = model.EndDate,
PayFrequency = model.PayFrequency,
PayDate = model.PayDate,
作業天數 = model.DaysWorked,
MonthlyRate = model.MonthlyRate,
BasicPayAmount = model.BasicPayAmount,
折疊稅 = model.WitholdingTax,
PagibigContribution = model.PagibigContribution,
PhilhealthContribution = model.PhilhealthContribution,
SSSContribution = model.SSSContribution。
/SalaryPerDay = model.SalaryPerDay,
//WorkDays = model.WorkDays,。
Deduction = model.Deduction,
CashLoan = model.CashLoan.CashLoan,
總工資 = model.TotalSalary
};
db.Payrolls.Add(payrolls)。
List<Deductions> list = new List<Deductions>（）。
for (int i = 0; i < DynamicTextBox.Length; i )
{
var deduct = new Deductions
{
EmployeeId = (int)payrolls.EmployeeId。
類別 = DynamicTextBox[i],
Amount = Convert.ToInt32(DynamicTextBox1[i])。
};
list.Add(deduct);
}
db.Deductionss.AddRange(list);
db.SaveChanges();
//return Json(new { success = true, message = "Saved Successfully" }, JsonRequestBehavior.AllowGet);/span>
}
}
model = PreparePayrollFormModel(model)。
return View（model）。
}
public PayrollFormModel PreparePayrollFormModel PreparePayrollFormModel(PayrollFormModel model) /model null
{
model.Employees = db.Employees.Select(x => new SelectListItem //laman ng dropdown ?
{
Text = x.FirstName " "/span> x.MiddleName " "/span> x.LastName,
值 = x.Id.ToString()
});
if (model.Id > 0) //edit din to para magreturn ng value[/span
{
var payroll = db.Payrolls.Find(model.Id); //null {
model.EmployeeId = payroll.EmployeeId。
model.StartDate = payroll.StartDate;
model.EndDate = payroll.EndDate。
//model.SalaryPerDay = payroll.SalaryPerDay;
//model.WorkDays = payroll.WorkDays;/span>
model.Deduction = payroll.Deduction;
model.TotalSalary = payroll.TotalSalary;
model.Employees = db.Employees.Select(x => new SelectListItem / laman ng dropdown
{
Text = x.FirstName " "/span> x.MiddleName " "/span> x.LastName,
值 = x.Id.ToString()
});
}
return 模型。
}
[HttpPost] 。
public ActionResult Delete(PayrollFormel model)
{
if (model.Id > 0)
{
var orders = db.Payrolls.FirstOrDefault(x => x.Id == model.Id)。
db.Payrolls.Remove(orders)。
db.SaveChanges()。
return Json(new { success = true, message = "Deleted Successfully" }, JsonRequestBehavior.AllowGet) 。
}
return View（model）。
}
public ActionResult LoadDetails(int employeeid, DateTime? startdatedata, DateTime? enddatedata）。
{
//var getEmployee = db.Employees.FirstOrDefault(x => x.Id == employeeid); // nakuha ko na dito yung id ni customer
//var getAttendace = db.Attendances.FirstOrDefault(x => x.EmployeeId == getEmployee.Id);
//var getPayroll = db.Payrolls.FirstOrDefault(x => x.EmployeeId == employeeid);
//example lang to aa depende sayo kung decimal ba or int or var
decimal? sum = db.Attendances
.Where(x => x.EmployeeId == employeeid)
//&& x.Date >= startdatedata &&
//x.Date <= enddatedata)
.Sum(x => x.Overtime)。
var LoadDetails = new PayrollFormModel
{
EmployeeId = employeeid,
//StartDate = startdatedata,/span>
//EndDate = enddatedata,/span>
作業天數=總和。
};
return Json(LoadDetails, JsonRequestBehavior.AllowGet) 。
}
}
我希望有人能幫助我。提前謝謝你。祝你有個愉快的一天！
uj5u.com熱心網友回復：
var sum = db.Attendances. Where(x => x.EmployeeId == employeeid & & x. Date >= startdatedata && x.Date <= enddatedata）。) Sum(y => y.Overtime)
應該可以了。如果有任何錯誤，請嘗試.Where().ToList().Sum()
。uj5u.com熱心網友回復：
我已經知道我的代碼有什么問題了。我只需改變這個
decimal? sum = db.Attendances。
.Where(x => x.EmployeeId == employeeid)
//&& x.Date >= startdatedata &&
//x.Date <= enddatedata)
.Sum(x => x.Overtime) 。
到此為止：
decimal? sum = db.Attendances
.Where(x => x.Date >= startdatedata & &
x.Date <= enddatedata && x.EmployeeId == employeeid)
.Sum(x => x.Overtime)。
謝謝你！
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/320356.html
標籤：