How to display total time in gridview

Question

0.00/5 (No votes)

See more:

C#

In      MealIn  MealOut MealBreak TeaIn  TeaOut TeaBreak OutTime TimeSpent
12:52PM	12:55PM	1:05PM	00:10	  1:13PM 1:45PM	00:32	 4:21PM	02:47
12:52PM	12:55PM	1:05PM	00:10	  2:11PM 2:23PM	00:12	 4:21PM	03:07
12:52PM	12:55PM	1:05PM	00:10	  3:11PM 3:15PM	00:04	 4:21PM	03:15
Sub Total	                                                09:09
Grand Total	                                                09:09

This is my output.I am login at (26/10.2016) 12:52PM
first we take meal break for 10 minutes.then I was take tea break 32 minutes so timespent is 2:47.now I am taking another tea break at same date for 12 minutes time and third time I was take tea break for 4 minutes so total break is 48 minutes
My start time is 12:52PM and EndTime is 4:21PM so between start time and endtime duration -total break time like 3:29-48=2:41 so I spent total time 2:41.but in my case i can able to get it.
I want to output like this..

C#

<pre lang="C#">
In      MealIn  MealOut MealBreak TeaIn  TeaOut TeaBreak TeaIn  TeaOut TeaBreak TeaIn  
12:52PM	12:55PM	1:05PM	00:10	  1:13PM 1:45PM	00:32    2:11PM 2:23PM	00:12   3:11PM 
TeaOut TeaBreak  OutTime TimeSpent
3:15PM	00:04	 4:21PM	 02:41
Sub Total	           02:41
Grand Total	           02:41</pre>

I want to output like this in gridview in asp.net .can you please help me.I will send my gridview code and procedure.

What I have tried:

gridview code.

ASP.NET

<asp:GridView ID="grdView" runat="server" AutoGenerateColumns="false" CssClass="table table-bordered" TabIndex="1" Width="100%" 
                                    GridLines="Vertical" OnRowCreated="grdView_RowCreated1" OnRowDataBound="grdView_RowDataBound1">
                                    <Columns>
                                        <asp:BoundField DataField="LoginDate" HeaderText="LoginDate" SortExpression="LoginDate" />
                                        <asp:BoundField DataField="FullName" HeaderText="EmployeeName" SortExpression="FullName" />
                                        <asp:BoundField DataField="STime" HeaderText="In Time" SortExpression="STime" />

                                        <asp:BoundField DataField="MTimeIn" HeaderText="MealIn" SortExpression="MTimeIn" />
                                        <asp:BoundField DataField="MTimeOut" HeaderText="MealOut" SortExpression="MTimeOut" />
                                        <asp:BoundField DataField="MealTimeSpent" HeaderText="MealBreak" SortExpression="MealTimeSpent" />
                                        

                                        <asp:BoundField DataField="TTimeIn" HeaderText="TeaIn" SortExpression="TTimeIn" />
                                        <asp:BoundField DataField="TTimeOut" HeaderText="TeaOut" SortExpression="TTimeOut" />
                                        <asp:BoundField DataField="TeaTimeSpent" HeaderText="TeaBreak" SortExpression="TeaTimeSpent" />
                                        <asp:BoundField DataField="TeaTimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="TeaTimeSpentMinutes" Visible="false" />


                                        <asp:BoundField DataField="OTimeIn" HeaderText="Oth.In" SortExpression="OTimeIn" />
                                        <asp:BoundField DataField="OTimeOut" HeaderText="Oth.Out" SortExpression="OTimeOut" />
                                        <asp:BoundField DataField="OtherTimeSpent" HeaderText="OtherBreak" SortExpression="OtherTimeSpent" />
                                        <asp:BoundField DataField="OtherTimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="OtherTimeSpentMinutes" Visible="false" />

                                        <asp:BoundField DataField="ETime" HeaderText="OutTime" SortExpression="ETime" />
                                        <asp:BoundField DataField="TimeSpentWork" HeaderText="TimeSpent" SortExpression="TimeSpentWork" />
                                        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" SortExpression="EmployeeId" Visible="false"></asp:BoundField>
                                        <asp:BoundField DataField="TimeSpentMinutes" HeaderText="TimeSpentMinutes" SortExpression="TimeSpentMinutes" Visible="false" />
                                    </Columns>
                                </asp:GridView>

procedure.
SELECT CONVERT(VARCHAR, T.CreateDateTime,105) AS [LoginDate],
E.FullName,
T.StartTimeIn,
T.EndTimeOut,

CONVERT(varchar(15),CAST(StartTimeIn AS TIME),100) AS [STime],
CONVERT(varchar(15),CAST(EndTimeOut AS TIME),100) AS [ETime],
CONVERT(varchar(15),CAST(MealTimeIn AS TIME),100) AS [MTimeIn],
CONVERT(varchar(15),CAST(MealTimeOut AS TIME),100) AS [MTimeOut],
CONVERT(varchar(15),CAST(D.TeaTimeIn AS TIME),100) AS [TTimeIn],
CONVERT(varchar(15),CAST(D.TeaTimeOut AS TIME),100) AS [TTimeOut],
CONVERT(varchar(15),CAST(OtherTimeIn AS TIME),100) AS [OTimeIn],
CONVERT(varchar(15),CAST(OtherTimeOut AS TIME),100) AS [OTimeOut],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [TimeSpent] ,
DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [TimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [MealTimeSpent] ,
DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [MealTimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [TeaTimeSpent] ,
DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [TeaTimeSpentMinutes],

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0), 114) AS [OtherTimeSpent] ,
DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))) AS [OtherTimeSpentMinutes],

CONVERT(varchar(5),ISNULL(DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)-
(ISNULL(DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0) +
ISNULL(DATEADD(minute, DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0) +
ISNULL(DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))), 0),0)),114) AS [TimeSpentWork],

DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30'')))-
(ISNULL(DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0)+
ISNULL(DATEDIFF(minute, D.TeaTimeIn, ISNULL(D.TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0)+
ISNULL(DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), ''+05:30''))),0) ) AS [TimeSpentMinutesWork],

E.EmployeeId
FROM
DailyTimeRecord T
INNER JOIN Employees E ON E.EmployeeId = T.EmployeeId
INNER JOIN TeaBreak D ON T.DailyTimeRecordId = D.DailyTimeRecordId

Posted 26-Oct-16 1:37am

Member 12097108

Updated 26-Oct-16 1:54am

Jörgen Andersson

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BIKASH KARMAKAR · Answer 1 · 2016-10-27T02:57:00

Solution 3

ASP.NET

1 .you can to Use This One In For Footer
and in Gridview ShowFooter="True"
 
2 .Add this In GridView
<TemplateField> 
<ItemTemplate> 
 <asp:Label ID="lblsum" runat="server" Text="Label"></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lbltotal" runat="server" Text="Label"></asp:Label>
</FooterTemplate>
</TemplateField>

Posted 27-Oct-16 2:57am

BIKASH KARMAKAR

Comments

Member 12097108 28-Oct-16 1:52am

Hello Sir,I have allready get total value but not in proper manner.I was send my output and procedure so can you pleasecheck my procedure and give some proper changis in query so I can get it.

BIKASH KARMAKAR · Answer 2 · 2016-10-27T02:58:00

C#

3. 
decimal sumFooterValue = 0;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
         string sponsorBonus = ((Label)e.Row.FindControl("Label2")).Text;
         decimal totalvalue = Convert.ToDecimal(sponsorBonus) ;
         e.Row.Cells[6].Text = totalvalue.ToString();
        sumFooterValue += totalvalue 
        }

    if (e.Row.RowType == DataControlRowType.Footer)
        {
           Label lbl = (Label)e.Row.FindControl("lblTotal");
           lbl.Text = sumFooterValue.ToString();
        }

   }

Suggestion Convert label To DateTime Format while Counting in RowDataBound
If you are good in C#

Thank You