0

I must write Web Application to reporting worker's overtime, received hours and saldo (overtime minus received hours).

If people make overtime or receive it, they must complete the form, which is saved in the database. I have to make a report for every man, where there will be a number of overtime, received hours and balance(saldo).

The Start and End variables of overtime and received hours are TimeSpan.

My report is a combination of 3 tables: dbo.People, dbo.OverTimes and dbo.ReceivedHours. I done SQL query to this report.

SELECT p.Name,p.Surname, 
ISNULL(SUM(DATEDIFF(minute,o.Start2, o.End2)),0) AS 'Overtime (min)',
ISNULL(SUM(DATEDIFF(minute,r.Start1, r.End1)),0) AS 'received hours (min) ',
ISNULL(SUM(DATEDIFF(minute,o.Start2, o.End2)),0)-ISNULL(SUM(DATEDIFF(minute,r.Start1, r.End1)),0) AS'Saldo (min)',
CONCAT((ISNULL(SUM(DATEDIFF(minute,o.Start2, o.End2)),0)-ISNULL(SUM(DATEDIFF(minute,r.Start1, r.End1)),0))/60, 
':' , (ISNULL(SUM(DATEDIFF(minute,o.Start2, o.End2)),0)-ISNULL(SUM(DATEDIFF(minute,r.Start1, r.End1)),0))%60) AS 'Saldo'
FROM [dbo].[People] AS p 
left join [dbo].OverTimes AS o ON p.PersonID=o.PersonID
left join [dbo].ReceivedHours AS r ON p.PersonID=r.PersonID
GROUP BY p.Name, p.Surname;

This is what the newly created report table looks like

Could you help me, how I can use this SQL query in ASP.NET MVC Controller and then show it in View?

Or maybe can you translate my query to LINQ?

Do you think I have to do new dynamic model?

Thank you for every help.

Martine
  • 1
  • 2
  • You need to break your problem down into small parts. You need a data layer to run your query, and convert the result to some model object (class structure) that represents your data. Your Controller should call that data layer to obtain the data, then pass it to the View as part of a View Model. – mason Sep 05 '18 at 12:24
  • The simplest approach is to make you SQL a stored procedure and then call the stored procedure from c#. – jdweng Sep 05 '18 at 12:34
  • I did procedurę for this, but I don't know how I call it in the controller in view. – Martine Sep 05 '18 at 12:43
  • Try this post: https://stackoverflow.com/questions/39587606/how-to-call-and-execute-stored-procedures-in-asp-net-mvcc – Paritosh Sep 05 '18 at 13:35

0 Answers0