using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Globalization;
using System.Threading;
using System.Reflection;
using System.Resources;
public partial class Transactions_TA_UI_Transactions_DataProcess : Base.BasePage
{
    //ResourceManager rm; 
    public SqlConnection sqlConn = new SqlConnection();
    public SqlCommand sqlComm = new SqlCommand();
    public SqlDataAdapter sqlAdap = new SqlDataAdapter();
    public SqlDataAdapter sqlAdap1 = new SqlDataAdapter();
    public string sqlConStr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
    protected override void Page_Load(object sender, EventArgs e)
    {
        // session checking and reset date format
        base.Page_Load(sender, e);    
    }
    protected void btn_Submit_Click(object sender, EventArgs e)
    {
        try
        {
            sqlConn = new SqlConnection(sqlConStr);
            // Open the connection, if the connection state is closed
            if (sqlConn.State == ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            // Process the RawData from Clock Detailstable
            ProcessData();
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    /// <summary>
    /// Process the RawData
    /// </summary>
    private void ProcessData()
    {
        try
        {
            sqlComm = new SqlCommand("Select * From TA_Tbl_Employee where IsDeleted = 1", sqlConn);
            sqlAdap = new SqlDataAdapter(sqlComm);
            DataSet dsEmpDetails = new DataSet();
            sqlAdap.Fill(dsEmpDetails);
            foreach (DataRow drEmployee in dsEmpDetails.Tables[0].Rows)
            {
                string sqlDates = "SELECT * FROM TA_Tbl_ProcessedData";
                sqlAdap = new SqlDataAdapter(sqlDates, sqlConn);
                DataTable dtDate = new DataTable();
                sqlAdap.Fill(dtDate);
                if (dtDate.Rows.Count > 0)
                {
                    DataView dvDate = new DataView(dtDate, "", "Date", DataViewRowState.CurrentRows);
                    DataTable dtDates = dvDate.ToTable(true, "Date");
                    for (int j = 0; j < dtDates.Rows.Count; j++)
                    {
                        if (dtDates.Rows[j]["Date"].ToString() != "")
                        {
                            string sqlunprocessed = "SELECT * FROM TA_Tbl_ProcessedData where Employee_Code_Id = '" + drEmployee["SwipeCardNo"] + "' and Date = '" + dtDates.Rows[j]["Date"] + "'";
                            sqlAdap = new SqlDataAdapter(sqlunprocessed, sqlConn);
                            DataTable dtUnProcess = new DataTable();
                            sqlAdap.Fill(dtUnProcess);
                            if (dtUnProcess.Rows.Count > 0)
                            {
                                string _FirstIn = "", _LastOut = "", strDate = "";
                                string strLateInTime = "0";
                                string strEarlyExit = "0";
                                DateTime dtProcessDate = Convert.ToDateTime(dtDates.Rows[j]["Date"]);
                                strDate = dtProcessDate.ToShortDateString();
                                if ((dtUnProcess.Rows[0]["InTime"].ToString() != "") && (dtUnProcess.Rows[0]["OutTime"].ToString() != ""))
                                {
                                    if ((dtUnProcess.Rows[0]["InTime"].ToString() != "0") && (dtUnProcess.Rows[0]["OutTime"].ToString() != "0"))
                                    {
                                        _FirstIn = dtUnProcess.Rows[0]["InTime"].ToString();
                                        _LastOut = dtUnProcess.Rows[0]["OutTime"].ToString();
                                        TimeSpan FirstInTime = DateTime.Parse(_FirstIn.ToString()).TimeOfDay;
                                        TimeSpan LastOutTime = DateTime.Parse(_LastOut.ToString()).TimeOfDay;
                                        //Get the WorkHrs data from Shift table. Now it is hardcoded to shiftid 1.
                                        string sqlShiftWorkHrs = "Select * From TA_Tbl_Shift Where Shift_Id = 1 and Isdeleted = 1";
                                        sqlAdap = new SqlDataAdapter(sqlShiftWorkHrs, sqlConn);
                                        DataTable dtShift = new DataTable();
                                        sqlAdap.Fill(dtShift);
                                        // Get actual Working hours,shift in time and shift out time from shift table
                                        //int WorkHrs, ShftGraceInTime = 0, ShftGraceOutTime = 0;
                                        DateTime WorkHrs = new DateTime();
                                        int ShftGraceOutTime = 0, ShftGraceInTime = 0;
                                        int ShftWorkOverTime = 0, ShftWeekOffOverTime = 0, ShftHolyOverTime = 0, ShftMinOTTime = 0, ShftMaxOTTime = 0;
                                        string ActualShfInTime = "", ActualShfOutTime = "";
                                        int TotalHrs = 0, Totalmin = 0;
                                        //int  ShiftStartHrs = 0, ShiftStartMin = 0;
                                        if (dtShift.Rows.Count > 0)
                                        {
                                            if (dtShift.Rows[0]["Shift_TotalWorkingHours"].ToString() != "")
                                            {
                                                WorkHrs = Convert.ToDateTime(dtShift.Rows[0]["Shift_TotalWorkingHours"]);
                                                TotalHrs = WorkHrs.Hour;
                                                Totalmin = WorkHrs.Minute;
                                            }
                                            if (dtShift.Rows[0]["Shift_StartTime"].ToString() != "")
                                            {
                                                ActualShfInTime = dtShift.Rows[0]["Shift_StartTime"].ToString();
                                            }
                                            if (dtShift.Rows[0]["Shift_EndTime"].ToString().ToString() != "")
                                            {
                                                ActualShfOutTime = dtShift.Rows[0]["Shift_EndTime"].ToString();
                                            }
                                            if (dtShift.Rows[0]["Shift_GraceInTime"].ToString() != "")
                                            {
                                                ShftGraceInTime = Convert.ToInt32(dtShift.Rows[0]["Shift_GraceInTime"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_GraceOutTime"].ToString() != "")
                                            {
                                                ShftGraceOutTime = Convert.ToInt32(dtShift.Rows[0]["Shift_GraceOutTime"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_WorkingDay_OverTime"].ToString() != "")
                                            {
                                                ShftWorkOverTime = Convert.ToInt32(dtShift.Rows[0]["Shift_WorkingDay_OverTime"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_WeekOff_OverTime"].ToString() != "")
                                            {
                                                ShftWeekOffOverTime = Convert.ToInt32(dtShift.Rows[0]["Shift_WeekOff_OverTime"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_HoliDay_OverTime"].ToString() != "")
                                            {
                                                ShftHolyOverTime = Convert.ToInt32(dtShift.Rows[0]["Shift_HoliDay_OverTime"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_MinTimeOT"].ToString() != "")
                                            {
                                                ShftMinOTTime = Convert.ToInt32(dtShift.Rows[0]["Shift_MinTimeOT"]);
                                            }
                                            if (dtShift.Rows[0]["Shift_MaxTimeOT"].ToString() != "")
                                            {
                                                ShftMaxOTTime = Convert.ToInt32(dtShift.Rows[0]["Shift_MaxTimeOT"]);
                                            }
                                        }
                                        //Calculate the Total working hours for the Employee
                                        string strTotalWorkHours = "";
                                        string sTotHrsWorked = "select Clk_InTime,Clk_OutTime from TA_Tbl_ClockDetails where Clk_EmpId='" + drEmployee["SwipeCardNo"] + "' and Clk_InDate='" + strDate + "'";
                                        sqlAdap1 = new SqlDataAdapter(sTotHrsWorked, sqlConn);
                                        DataTable dtClockDetails = new DataTable();
                                        sqlAdap1.Fill(dtClockDetails);
                                        double dTotalMinsWorked = 0;
                                        if (dtClockDetails.Rows.Count > 0)
                                        {
                                            for (int k = 0; k < dtClockDetails.Rows.Count; k++)
                                            {
                                                if ((dtClockDetails.Rows[k]["Clk_InTime"].ToString() != "") && (dtClockDetails.Rows[k]["Clk_OutTime"].ToString() != ""))
                                                {
                                                    TimeSpan InTime = DateTime.Parse(dtClockDetails.Rows[k]["Clk_InTime"].ToString()).TimeOfDay;
                                                    TimeSpan OutTime = DateTime.Parse(dtClockDetails.Rows[k]["Clk_OutTime"].ToString()).TimeOfDay;
                                                    dTotalMinsWorked += double.Parse((OutTime.Subtract(InTime)).TotalMinutes.ToString());
                                                }
                                            }
                                        }
                                        double dTotHrs = Math.Floor(dTotalMinsWorked / 60);
                                        double dTotMins = dTotalMinsWorked % 60;
                                        int iTotHrs = Convert.ToInt32(dTotHrs);
                                        int iTotMins = Convert.ToInt32(dTotMins);
                                        if (Convert.ToInt32(dTotMins) >= 60)
                                        {
                                            iTotMins = Convert.ToInt32(dTotMins) % 60;
                                            iTotHrs += Convert.ToInt32(dTotMins) / 60;
                                        }
                                        strTotalWorkHours = iTotHrs.ToString("00") + ":" + iTotMins.ToString("00");
                                        //Calculate Less Hours Work,Extra Hours Work and Over Time
                                        string strHrsOT = "0", strNorOT = "0", strWeekOffOT = "0";
                                        string strLessHours = "0", strExtraHours = "0";
                                        string strHoliDay = "Select * From TA_Tbl_Holiday";
                                        sqlAdap = new SqlDataAdapter(strHoliDay, sqlConn);
                                        DataTable dtHoliday = new DataTable();
                                        sqlAdap.Fill(dtHoliday);
                                        DataView dvHoly = new DataView();
                                        if (dtHoliday.Rows.Count > 0)
                                        {
                                            string strFilter = "Holiday_FromDate='" + strDate + "' or Holiday_ToDate='" + strDate + "'";
                                            dvHoly = new DataView(dtHoliday, strFilter, "", DataViewRowState.CurrentRows);
                                        }
                                        string strHoliOT;
                                        if (dvHoly.Count > 0)
                                        {
                                            strHoliOT = iTotHrs.ToString() + ":" + iTotMins.ToString();
                                        }
                                        else
                                        {
                                            strHoliOT = "0";
                                            //Calculate Weekly Off OT
                                            DateTime dtCurrent = DateTime.Parse(strDate);
                                            DayOfWeek dw = dtCurrent.DayOfWeek;
                                            if ((dw.ToString() == "Sunday") && (Convert.ToInt32(dTotalMinsWorked) >= (ShftWeekOffOverTime * 60)))
                                            {
                                                strWeekOffOT = iTotHrs.ToString() + ":" + iTotMins.ToString();
                                            }
                                            else
                                            {
                                                strWeekOffOT = "0";
                                                // Calculate Late InTime
                                                double dAInTotalMins, dCInTotalMins;
                                                TimeSpan ActualShiftStartTime = DateTime.Parse(ActualShfInTime.ToString()).TimeOfDay;
                                                TimeSpan LateIn = new TimeSpan();
                                                dAInTotalMins = double.Parse(ActualShiftStartTime.TotalMinutes.ToString());
                                                dCInTotalMins = double.Parse(FirstInTime.TotalMinutes.ToString());
                                                if (dAInTotalMins < dCInTotalMins)
                                                {
                                                    if ((dCInTotalMins - dAInTotalMins) > ShftGraceInTime)
                                                    {
                                                        LateIn = FirstInTime.Subtract(ActualShiftStartTime);
                                                        strLateInTime = LateIn.ToString();
                                                    }
                                                }
                                                //Calculate Early Exit
                                                double dAOutTotalMins, dCOutTotalMins;
                                                TimeSpan ActualShiftEndTime = DateTime.Parse(ActualShfOutTime.ToString()).TimeOfDay;
                                                TimeSpan EarlyOut = new TimeSpan();
                                                dAOutTotalMins = double.Parse(ActualShiftEndTime.TotalMinutes.ToString());
                                                dCOutTotalMins = double.Parse(LastOutTime.TotalMinutes.ToString());
                                                if (dCOutTotalMins < dAOutTotalMins)
                                                {
                                                    if ((dAOutTotalMins - dCOutTotalMins) > ShftGraceOutTime)
                                                    {
                                                        EarlyOut = ActualShiftEndTime.Subtract(LastOutTime);
                                                        strEarlyExit = EarlyOut.ToString().Substring(0, 5);
                                                    }
                                                }
                                                // Calculate Less Hours Work and Extra Hours Work
                                                double dTotalWorkMins, dActWorkMins = 0, dMinLess, dMinExtra;
                                                //Response.Write(strTotalWorkHours + "<br>");
                                                if (strTotalWorkHours != ":")
                                                {
                                                    if (dTotHrs >= 0 && dTotHrs <= 24)
                                                    {
                                                        TimeSpan ActWorkHrs = DateTime.Parse(strTotalWorkHours.ToString()).TimeOfDay;
                                                        dActWorkMins = double.Parse(ActWorkHrs.TotalMinutes.ToString());
                                                    }
                                                }
                                                dTotalWorkMins = (TotalHrs * 60) + Totalmin;
                                                //Calculate Less hours work
                                                if (dActWorkMins < dTotalWorkMins)
                                                {
                                                    dMinLess = dTotalWorkMins - dActWorkMins;
                                                    double dLessHrs = Math.Floor(dMinLess / 60);
                                                    double dLessMins = dMinLess % 60;
                                                    int iLessHrs = Convert.ToInt32(dLessHrs);
                                                    int iLessMins = Convert.ToInt32(dLessMins);
                                                    strLessHours = iLessHrs.ToString() + ":" + iLessMins.ToString();
                                                }
                                                //Calculate Extra hours working for the Employee and Over Time
                                                if (dActWorkMins > dTotalWorkMins)
                                                {
                                                    dMinExtra = dActWorkMins - dTotalWorkMins;
                                                    double dExtraHrs = Math.Floor(dMinExtra / 60);
                                                    double dExtraMins = dMinExtra % 60;
                                                    int iExtraHrs = Convert.ToInt32(dExtraHrs);
                                                    int iExtraMins = Convert.ToInt32(dExtraMins);
                                                    //Hrs OT
                                                    if (iExtraHrs >= ShftMinOTTime)
                                                    {
                                                        strHrsOT = iExtraHrs.ToString() + ":" + iExtraMins.ToString();
                                                    }
                                                    //Normal OT
                                                    if (iExtraHrs >= ShftWorkOverTime)
                                                    {
                                                        strNorOT = iExtraHrs.ToString() + ":" + iExtraMins.ToString();
                                                    }
                                                    strExtraHours = iExtraHrs.ToString() + ":" + iExtraMins.ToString();
                                                }
                                            }
                                        }
                                        //Update the All the calculated data to the Processed Data table
                                        string sqlProcessUpdate = "UPDATE TA_Tbl_ProcessedData SET InTime = '" + _FirstIn + "',OutTime = '" + _LastOut + "',Shift_Id = 1,HrsWork = '" + strTotalWorkHours + "',HrsOT = '" + strHrsOT + "', NormOT = '" + strNorOT + "', ";
                                        sqlProcessUpdate += "OffOT = '" + strWeekOffOT + "', HolOT = '" + strHoliOT + "',EarlyOut = '" + strEarlyExit + "', LateIn = '" + strLateInTime + "', LeaveType = 'P', ";
                                        sqlProcessUpdate += "WorkHrs = '" + WorkHrs.ToLongTimeString() + "', HrsLess = '" + strLessHours + "',HrsExtra = '" + strExtraHours + "' ";
                                        sqlProcessUpdate += "WHERE Employee_Code_Id = '" + drEmployee["SwipeCardNo"] + "' AND Date = '" + strDate + "'";
                                        sqlComm = new SqlCommand(sqlProcessUpdate, sqlConn);
                                        sqlComm.ExecuteNonQuery();
                                        // Update the Clk_Process field to Y in TA_Tbl_ClockDetails Table 
                                        // string sqlClockUpdate = "Update TA_Tbl_ClockDetails Set Clk_Process = 'Y' where Clk_EmpId = '" + dtUnProcess.Rows[0]["Clk_EmpId"].ToString() + "' and Clk_InDate = '" + strDate + "'";
                                        //sqlComm = new SqlCommand(sqlClockUpdate, sqlConn);
                                        //sqlComm.ExecuteNonQuery();
                                    }
                                }
                            }
                            else
                            {
                                string sqlProcess = "Select * From TA_Tbl_ProcessedData";
                                sqlAdap = new SqlDataAdapter(sqlProcess, sqlConn);
                                DataTable dtProcess = new DataTable();
                                sqlAdap.Fill(dtProcess);
                                DataView dvProcess = null;
                                DateTime dtCurrent = DateTime.Now;
                                string strLeaveDate = dtCurrent.ToShortDateString();
                                if (dtProcess.Rows.Count > 0)
                                {
                                    string strFilter = "Employee_Code_Id = '" + drEmployee["Employee_ID"] + "' And Date='" + dtDates.Rows[j]["Date"] + "'";
                                    dvProcess = new DataView(dtProcess, strFilter, "", DataViewRowState.CurrentRows);
                                }
                                //Find which employee is leave and update the data into Processed data table
                                if (dvProcess == null || dvProcess.Count == 0)
                                {
                                    string sqlProceeInsert = "Insert Into TA_Tbl_ProcessedData (Employee_Code_Id, [Date], Shift_Id, InTime, OutTime, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, LeaveType, WorkHrs, HrsLess,HrsExtra)";
                                    sqlProceeInsert += "Values('" + drEmployee["Employee_Id"] + "','" + DateTime.Parse(dtDates.Rows[j]["Date"].ToString()).ToShortDateString() + "',1,'0','0','0','0','0','0','0','0','0','A','0','0','0')";
                                    sqlComm = new SqlCommand(sqlProceeInsert, sqlConn);
                                    sqlComm.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }
            //Close the connection state
            if (sqlConn.State == ConnectionState.Open)
            {
                sqlConn.Close();
            }
            lblMessage.Text = "Datas has been Processed";
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
}
