private void RegisterProjectUptimes()
{
ProjectUptimesHelper dbHelper = new ProjectUptimesHelper(_appSettings.Secrets.DbConnectionString);
try
{
foreach (var item in ProjectList)
{
ProjectUptimesData data = new ProjectUptimesData();
data.WorkDate = DateTime.Parse(TargetDay);
data.ProjectId = item.ProjectId;
data.UserId = HttpContext.Session.GetString(Constants.Session.EmployeeId);
data.WorkMinute = (int)(item.WorkMinute * 60);
data.Remarks = item.Remarks;
if (item.PrimaryId.Equals(null))
{
dbHelper.InsertData(data);
}
else
{
var key = (int)item.PrimaryId;
dbHelper.UpdateData(data, key);
}
}
}
catch (Exception ex)
{
_logger.LogError("Error RegisterProjectUptimes: {0}", ex.Message);
}
}
public int InsertData(ProjectUptimesData data)
{
var ret = -1;
var query = $"insert into ProjectUptimes (WorkDate, ProjectId, UserId, WorkMinute, Remarks) " +
$"values ('{data.WorkDate}', '{data.ProjectId}', '{data.UserId}', '{data.WorkMinute}', '{data.Remarks}')";
ret = dbHelper.ExecuteQuery(query);
return(ret);
}
public int UpdateData(ProjectUptimesData data, int key)
{
var ret = -1;
var query = $"update ProjectUptimes" +
$" set WorkDate = '{data.WorkDate}'" +
$" , ProjectId = '{data.ProjectId}'" +
$" , UserId = '{data.UserId}'" +
$" , WorkMinute = '{data.WorkMinute}'" +
$" , Remarks = '{data.Remarks}'" +
$" where PrimaryId = '{key}'";
ret = dbHelper.ExecuteQuery(query);
return(ret);
}
public List <ProjectUptimesData> SelectProjectData(string projectId)
{
var dataList = new List <ProjectUptimesData>();
using var dataTable = dbHelper.GetDataTable($"select ProjectId, UserId, Sum(WorkMinute) as TotalMinute from ProjectUptimes where ProjectId = '{projectId}' group by ProjectId, UserId order by ProjectId, UserId;");
foreach (var row in dataTable.Rows.Cast <DataRow>())
{
var data = new ProjectUptimesData();
data.ProjectId = row["ProjectId"].ToString();
data.UserId = row["UserId"].ToString();
data.WorkMinute = int.Parse(row["TotalMinute"].ToString());
dataList.Add(data);
}
return(dataList);
}
public ProjectUptimesData SelectData(string employeeId)
{
var data = new ProjectUptimesData();
using var dataTable = dbHelper.GetDataTable($"select * from ProjectUptimes where UserId = '{employeeId}'");
foreach (var row in dataTable.Rows.Cast <DataRow>())
{
data.PrimaryId = int.Parse(row["PrimaryId"].ToString());
data.WorkDate = DateTime.Parse(row["WorkDate"].ToString());
data.ProjectId = row["ProjectId"].ToString();
data.UserId = row["UserId"].ToString();
data.WorkMinute = int.Parse(row["WorkMinute"].ToString());
data.Remarks = row["Remarks"].ToString();
}
return(data);
}
