Esempio n. 1
0
        private void refreshLabour()
        {

            #region extract the sheet name and FSH and LSH of the extract
            ATPMain.VkExcel excel = new ATPMain.VkExcel(false);


            bool XLSX_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.MiningType + BusinessLanguage.Period.Trim() + ".xlsx");
            bool XLS_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.MiningType + BusinessLanguage.Period.Trim() + ".xls");

            if (XLSX_exists.Equals(true))
            {
                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.MiningType + BusinessLanguage.Period.Trim() + ".xlsx", "BONTS2011");
                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            if (XLS_exists.Equals(true))
            {

                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.MiningType + BusinessLanguage.Period.Trim() + ".xls", "BONTS2011");

                excel.SaveFile(BusinessLanguage.Period.Trim(),strServerPath);
                excel.CloseFile();
            }

            excel.stopExcel();

            string FilePath = "";

            string FilePath_XLSX = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\Engineering_" + BusinessLanguage.Period.Trim() + ".xlsx";

            string FilePath_XLS = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\Engineering_" + BusinessLanguage.Period.Trim() + ".xls";

            XLSX_exists = File.Exists(FilePath_XLSX);
            XLS_exists = File.Exists(FilePath_XLS);

            if (XLS_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\Engineering_" + BusinessLanguage.Period.Trim() + ".xls";
            }

            if (XLSX_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\Engineering_" + BusinessLanguage.Period.Trim() + ".xlsx";
            }
            //excel.GetExcelSheets();
            string[] sheetNames = GetExcelSheetNames(FilePath);
            string sheetName = sheetNames[0];
            #endregion

            #region import Clockshifts
            this.Cursor = Cursors.WaitCursor;
            DataTable dt = new DataTable();

            OleDbConnection con = new OleDbConnection();
            OleDbDataAdapter da;
            con.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source="
                    + FilePath + ";Extended Properties='Excel 8.0;'";

            /*"HDR=Yes;" indicates that the first row contains columnnames, not data.
            * "HDR=No;" indicates the opposite.
            * "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. 
            * Note that this option might affect excel sheet write access negative.
            */

            da = new OleDbDataAdapter("select * from [" + sheetName + "]", con); //read first sheet named Sheet1
            da.Fill(dt);
            IEnumerable<DataRow> query1 = from locks in dt.AsEnumerable()
                                          where locks.Field<string>("GANG NAME").Substring(2, 1) == "L" ||
                                                locks.Field<string>("GANG NAME").Substring(0, 6) == "JJPPRE"
                                          select locks;

            //Temp will contain a list of the gangs for the section
            DataTable Tempdt = query1.CopyToDataTable<DataRow>();

            dt = Tempdt.Copy();
            #region remove invalid records

            //extract the column names with length less than 3.  These columns must be deleted.
            string[] columnNames = new String[dt.Columns.Count];

            for (int i = 0; i <= dt.Columns.Count - 1; i++)
            {
                if (dt.Columns[i].ColumnName.Length <= 2)
                {
                    columnNames[i] = dt.Columns[i].ColumnName;
                }
            }

            for (Int16 i = 0; i <= columnNames.GetLength(0) - 1; i++)
            {
                if (string.IsNullOrEmpty(columnNames[i]))
                {

                }
                else
                {
                    dt.Columns.Remove(columnNames[i].ToString().Trim());
                    dt.AcceptChanges();
                }
            }

            dt.Columns.Remove("INDUSTRY NUMBER");
            dt.AcceptChanges();
            #endregion

            string strSheetFSH = string.Empty;
            string strSheetLSH = string.Empty;
            DateTime SheetFSH;
            DateTime SheetLSH;

            //Extract the dates from the spreadsheet - the name of the spreadsheet contains the the start and enddate of the extract
            string strSheetFSHx = sheetName.Substring(0, sheetName.IndexOf("_TO")).Replace("_", "-").Replace("'", "").Trim(); ;
            string strSheetLSHx = sheetName.Substring(sheetName.IndexOf("_TO") + 4).Replace("$", "").Replace("_", "-").Replace("'", "").Trim(); ;

            //Correct the dates and calculate the number of days extracted.
            if (strSheetFSHx.Substring(6, 1) == "-")
            {
                strSheetFSH = strSheetFSHx.Substring(0, 5) + "0" + strSheetFSHx.Substring(5);
            }
            else
            {
                strSheetFSH = strSheetFSHx.ToString();
            }

            if (strSheetLSHx.Substring(6, 1) == "-")
            {
                strSheetLSH = strSheetLSHx.Substring(0, 5) + "0" + strSheetLSHx.Substring(5);
            }
            else
            {
                strSheetLSH = strSheetLSHx.ToString();
            }

            SheetFSH = Convert.ToDateTime(strSheetFSH.ToString());
            SheetLSH = Convert.ToDateTime(strSheetLSH.ToString());



            //If the intNoOfDays < 40 then the days up to 40 must be filled with '-'
            int intNoOfDays = Base.calcNoOfDays(SheetLSH, SheetFSH);

            if (intNoOfDays <= 40)
            {
                for (int j = intNoOfDays + 1; j <= 40; j++)
                {
                    dt.Columns.Add("DAY" + j);
                }
            }
            else
            {

            }
            #region Change the column names
            //Change the column names to the correct column names.
            Dictionary<string, string> dictNames = new Dictionary<string, string>();
            DataTable varNames = TB.createDataTableWithAdapter(Base.AnalysisConnectionString,
                                 "Select * from varnames");
            dictNames.Clear();

            dictNames = TB.loadDict(varNames, dictNames);
            int counter = 0;

            //If it is a column with a date as a name.
            foreach (DataColumn column in dt.Columns)
            {
                if (column.ColumnName.Substring(0, 1) == "2")
                {
                    if (counter == 0)
                    {
                        strSheetFSH = column.ColumnName.ToString().Replace("/", "-");
                        column.ColumnName = "DAY" + counter;
                        counter = counter + 1;
                    }
                    else
                    {
                        if (column.Ordinal == dt.Columns.Count - 1)
                        {

                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;

                        }
                        else
                        {
                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;
                        }
                    }
                }
                else
                {
                    if (dictNames.Keys.Contains<string>(column.ColumnName.Trim().ToUpper()))
                    {
                        column.ColumnName = dictNames[column.ColumnName.Trim().ToUpper()];
                    }
                }
            }

            //Add the extra columns
            dt.Columns.Add("BUSSUNIT");
            dt.Columns.Add("FSH");
            dt.Columns.Add("LSH");
            dt.Columns.Add("SECTION");
            dt.Columns.Add("EMPLOYEETYPE");
            dt.Columns.Add("PERIOD");      //xxxxxxxx
            dt.AcceptChanges();

            MessageBox.Show("no of rows imported: " + dt.Rows.Count, "Information", MessageBoxButtons.OK);
            foreach (DataRow row in dt.Rows)
            {
                row["BUSSUNIT"] = BusinessLanguage.BussUnit.Trim();
                row["FSH"] = strSheetFSH;
                row["LSH"] = strSheetLSH;
                row["MININGTYPE"] = "ENGINEERING";
                row["PERIOD"] = BusinessLanguage.Period;   //xxx
                if (row["GANG"].ToString().Length > 0)
                {
                    row["SECTION"] = "ENG";
                }
                else
                {
                    row["SECTION"] = "XXX";
                }
                if (row["WAGECODE"].ToString().Trim() == "")
                {
                    row["WAGECODE"] = "00000";
                }
                else
                {
                }

                row["EMPLOYEETYPE"] = Base.extractEmployeeType(Configs, row["WAGECODE"].ToString());

                //Replace all the null columns with a "-"
                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    if (string.IsNullOrEmpty(row[i].ToString()) || row[i].ToString() == "")
                    {
                        row[i] = "-";
                    }
                }
            }

            //On BonusShifts the column PERIOD is part of the primary key.  Therefore must be moved xxxxxxxxx
            DataColumn dcBussunit = new DataColumn();
            dcBussunit.ColumnName = "BUSSUNIT";
            dt.Columns.Remove("BUSSUNIT");
            dt.AcceptChanges();
            InsertAfter(dt.Columns, dt.Columns["BONUSTYPE"], dcBussunit);

            foreach (DataRow dr in dt.Rows)
            {
                dr["BUSSUNIT"] = BusinessLanguage.BussUnit.Trim();
            }


            #endregion
            //exportToExcel("c:\\", dt);
            //Write to the database
            TB.saveCalculations2(dt, Base.DBConnectionString, "", "CLOCKEDSHIFTS");

            TB.InsertData(Base.DBConnectionString, "update clockedshifts set employeetype = '5 - ARMS' " +
                          "where employee_no in (select employee_no from employeelist where tablename = 'ARMS')");
            //==================================================================================
            Application.DoEvents();

            //TEMP HARD CODING

            grdClocked.DataSource = dt;
            #endregion

            #region Calculate the shifts per employee en output to bonusshifts

            string strSQL = "Select *,'SUBSECTION' as SUBSECTION,'DEPARTMENT' as DEPARTMENT,'HODMODEL' as HODMODEL,'0' as SHIFTS_WORKED,'0' as AWOP_SHIFTS," +
                            "'0' as Q_SHIFTS " +
                            " from Clockedshifts where section = '" +
                            txtSelectedSection.Text.Trim() + "' order by employee_no";

            string strSQLFix = "Select *,'0' as SHIFTS_WORKED from Clockedshifts";

            //jvdw laai die hele clockedshift table
            fixShifts = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQLFix);
            //==============================================================================
            BonusShifts = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQL);
            //exportToExcel("c:\\", BonusShifts);
            string strCalendarFSH = dateTimePicker1.Value.ToString("yyyy-MM-dd");
            string strCalendarLSH = dateTimePicker2.Value.ToString("yyyy-MM-dd");

            DateTime CalendarFSH = Convert.ToDateTime(strCalendarFSH.ToString());
            DateTime CalendarLSH = Convert.ToDateTime(strCalendarLSH.ToString());

            sheetfhs = SheetFSH;//jvdw
            sheetlhs = SheetLSH;//jvdw
            int intStartDay = Base.calcNoOfDays(CalendarFSH, SheetFSH);
            int intEndDay = Base.calcNoOfDays(CalendarLSH, SheetLSH);
            int intStopDay = 0;

            if (intStartDay < 0)
            {
                //The calendarFSH falls outside the startdate of the sheet.
                intStartDay = 0;
            }
            else
            {
            }

            if (intEndDay < 0 && intEndDay < -40)
            {
                intStopDay = 0;
            }
            else
            {
                if (intEndDay < 0)
                {
                    //the LSH of the measuring period falls within the spreadsheet
                    intStopDay = intNoOfDays + intEndDay;

                }
                else
                {
                    //The LSH of the measuring period falls outside the spreadsheet
                    intStopDay = 40;
                }


                //If intStartDay < 0 then the SheetFSH is bigger than the calendarFSH.  Therefore some of the Calendar's shifts 
                //were not imported.

                #region count the shifts
                //Count the shifts
                Shared.evaluateDataTable(Base, "CLOCKEDSHIFTS");

                DialogResult result = MessageBox.Show("Do you want to REPLACE the current BONUSSHIFTS for section " + txtSelectedSection.Text.Trim() + " ?", "QUESTION", MessageBoxButtons.OKCancel);

                switch (result)
                {
                    case DialogResult.OK:
                        Shared.evaluateDataTable(Base, "BONUSSHIFTS");
                        extractAndCalcShifts(intStartDay, intStopDay);
                        MessageBox.Show("Shifts were imported successfully", "Information", MessageBoxButtons.OK);
                        evaluateShifts();
                        break;

                    case DialogResult.Cancel:
                        break;

                }

                #endregion

            #endregion


                this.Cursor = Cursors.Arrow;

                MessageBox.Show("Shifts were imported successfully", "Information", MessageBoxButtons.OK);
                //}
            }

        }
Esempio n. 2
0
        private void refreshLabour3()
        {
            #region extract the sheet name and FSH and LSH of the extract
            ATPMain.VkExcel excel = new ATPMain.VkExcel(false);

            bool XLSX_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx");
            bool XLS_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls");

            if (XLSX_exists.Equals(true))
            {
                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx", "BONTS2011");
                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            if (XLS_exists.Equals(true))
            {

                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls", "BONTS2011");

                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            excel.stopExcel();

            string FilePath = "";

            string FilePath_XLSX = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";

            string FilePath_XLS = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";

            XLSX_exists = File.Exists(FilePath_XLSX);
            XLS_exists = File.Exists(FilePath_XLS);

            if (XLS_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";
            }

            if (XLSX_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";
            }

            if (FilePath.Trim().Length == 0)
            {
                MessageBox.Show("ADTeam spreadsheet does not exist", "Information", MessageBoxButtons.OK);
                this.Cursor = Cursors.Arrow;
            }
            else
            {
                //excel.GetExcelSheets();
                string[] sheetNames = GetExcelSheetNames(FilePath);
                string sheetName = sheetNames[0];
            #endregion

                #region import Clockshifts
                this.Cursor = Cursors.WaitCursor;
                DataTable dt = new DataTable();

                OleDbConnection con = new OleDbConnection();
                OleDbDataAdapter da;
                con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                        + FilePath + ";Extended Properties='Excel 12.0;'";

                /*"HDR=Yes;" indicates that the first row contains columnnames, not data.
                * "HDR=No;" indicates the opposite.
                * "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.
                * Note that this option might affect excel sheet write access negative.
                */

                da = new OleDbDataAdapter("select * from [" + sheetName + "]", con); //read first sheet named Sheet1
                da.Fill(dt);

                IEnumerable<DataRow> query1 = from locks in dt.AsEnumerable()
                                              where locks.Field<string>("WAGE CODE").TrimEnd().Length > 0 &&
                                                    locks.Field<string>("WAGE CODE").TrimEnd().Substring(0, 1) != "0"
                                              select locks;

                //Temp will contain a list of the gangs for the section
                DataTable Tempdt = query1.CopyToDataTable<DataRow>();

                dt = Tempdt.Copy();

                #region remove invalid records

                //extract the column names with length less than 3.  These columns must be deleted.
                string[] columnNames = new String[dt.Columns.Count];

                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    if (dt.Columns[i].ColumnName.Length <= 2)
                    {
                        columnNames[i] = dt.Columns[i].ColumnName;
                    }
                }

                for (Int16 i = 0; i <= columnNames.GetLength(0) - 1; i++)
                {
                    if (string.IsNullOrEmpty(columnNames[i]))
                    {

                    }
                    else
                    {
                        dt.Columns.Remove(columnNames[i].ToString().Trim());
                        dt.AcceptChanges();
                    }
                }

                if (dt.Columns.Contains("INDUSTRY NUMBER"))
                {
                    dt.Columns.Remove("INDUSTRY NUMBER");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 1"))
                {
                    dt.Columns.Remove("BONUS 1");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 2"))
                {
                    dt.Columns.Remove("BONUS 2");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 3"))
                {
                    dt.Columns.Remove("BONUS 3");
                    dt.AcceptChanges();
                }

                #endregion

                string strSheetFSH = string.Empty;
                string strSheetLSH = string.Empty;

                //Extract the dates from the spreadsheet - the name of the spreadsheet contains the the start and enddate of the extract
                string strSheetFSHx = sheetName.Substring(0, sheetName.IndexOf("_TO")).Replace("_", "-").Replace("'", "").Trim(); ;
                string strSheetLSHx = sheetName.Substring(sheetName.IndexOf("_TO") + 4).Replace("$", "").Replace("_", "-").Replace("'", "").Trim(); ;

                //Correct the dates and calculate the number of days extracted.
                if (strSheetFSHx.Substring(6, 1) == "-")
                {
                    strSheetFSH = strSheetFSHx.Substring(0, 5) + "0" + strSheetFSHx.Substring(5);
                }
                else
                {
                    strSheetFSH = strSheetFSHx;
                }

                if (strSheetLSHx.Substring(6, 1) == "-")
                {
                    strSheetLSH = strSheetLSHx.Substring(0, 5) + "0" + strSheetLSHx.Substring(5);
                }
                else
                {
                    strSheetLSH = strSheetLSHx;
                }

                DateTime SheetFSH = Convert.ToDateTime(strSheetFSH.ToString());
                DateTime SheetLSH = Convert.ToDateTime(strSheetLSH.ToString());

                TB.InsertData(Base.DBConnectionString, "Update calendar set FSH = '" + strSheetFSH.ToString().Trim() +
                                                      "', LSH = '" + strSheetLSH.ToString() + "' where section = 'OFF'");

                //If the intNoOfDays < 44 then the days up to 44 must be filled with '-'
                int intNoOfDays = Base.calcNoOfDays(SheetLSH, SheetFSH);

                if (intNoOfDays <= 44)
                {
                    for (int j = intNoOfDays + 1; j <= 44; j++)
                    {
                        dt.Columns.Add("DAY" + j);
                    }
                }
                else
                {

                }

                #region Change the column names
                //Change the column names to the correct column names.
                Dictionary<string, string> dictNames = new Dictionary<string, string>();
                DataTable varNames = TB.createDataTableWithAdapter(Base.AnalysisConnectionString,
                                     "Select * from varnames");
                dictNames.Clear();

                dictNames = TB.loadDict(varNames, dictNames);
                int counter = 0;

                //If it is a column with a date as a name.
                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName.Substring(0, 1) == "2")
                    {
                        if (counter == 0)
                        {
                            strSheetFSH = column.ColumnName.ToString().Replace("/", "-");
                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;

                        }
                        else
                        {
                            if (column.Ordinal == dt.Columns.Count - 1)
                            {

                                column.ColumnName = "DAY" + counter;
                                counter = counter + 1;

                            }
                            else
                            {
                                column.ColumnName = "DAY" + counter;
                                counter = counter + 1;
                            }
                        }

                    }
                    else
                    {
                        if (dictNames.Keys.Contains<string>(column.ColumnName.Trim().ToUpper()))
                        {
                            column.ColumnName = dictNames[column.ColumnName.Trim().ToUpper()];
                        }

                    }
                }

                dt.Columns.Add("FSH");
                dt.Columns.Add("LSH");
                dt.Columns.Add("SECTION");
                dt.Columns.Add("EMPLOYEETYPE");
                dt.Columns.Add("PERIOD");      //xxxxxxxx
                dt.Columns.Add("BUSSUNIT");
                dt.AcceptChanges();

                foreach (DataRow row in dt.Rows)
                {

                    row["FSH"] = strSheetFSH;
                    row["LSH"] = strSheetLSH;
                    row["MININGTYPE"] = "OFFICIALS";
                    row["BONUSTYPE"] = "SERVICES";
                    row["PERIOD"] = BusinessLanguage.Period;   //xxx
                    row["BUSSUNIT"] = BusinessLanguage.BussUnit;
                    row["SECTION"] = "OFF";

                    if (row["WAGECODE"].ToString().Trim() == "")
                    {
                        row["WAGECODE"] = "00000";
                    }
                    else
                    {
                    }
                    row["EMPLOYEETYPE"] = Base.extractEmployeeType(Configs, row["WAGECODE"].ToString());

                    for (int i = 0; i <= dt.Columns.Count - 1; i++)
                    {
                        if (string.IsNullOrEmpty(row[i].ToString()) || row[i].ToString() == "")
                        {
                            row[i] = "-";
                        }
                    }
                }

                #endregion
                //exportToExcel("c:\\", dt);
                //Write to the database
                dt.Columns["BUSSUNIT"].SetOrdinal(11);
                TB.saveCalculations2(dt, Base.DBConnectionString, "", "CLOCKEDSHIFTS");

                Application.DoEvents();

                #region Apply offdays

                if (dt.Rows.Count > 0)
                {
                    Clocked = dt.Copy();
                    //Update clockedshifts with offday calendar data
                    UpdateClockedShifts();
                    dt = TB.createDataTableWithAdapterSelectAll(Base.DBConnectionString, "Clockedshifts");

                    Application.DoEvents();

                    //grdClocked.DataSource = dt;
                }
                #endregion

                #endregion

                #region Calculate the shifts per employee en output to bonusshifts

                string strSQL = " Select distinct t1.*,'0' as SHIFTS_WORKED, " +
                                " '0' as AWOP_SHIFTS,'0' as Q_SHIFTS,t2.MEASSECTION,t2.FSH as FSH_Participant,t2.LSH as LSH_Participant  " +
                                " from Clockedshifts AS T1, PARTICIPANTS AS T2 where T1.section = 'OFF' " +
                                " AND T1.SECTION = T2.SECTION " +
                                " AND T1.EMPLOYEE_NO = T2.EMPLOYEE_NO " +
                                " AND T1.PERIOD = T2.PERIOD;  ";

                DataTable noDups = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQL);

                BonusShifts = removeDuplicateRecords(noDups);

                #region count the shifts
                //Count the the shifts

                DialogResult result = MessageBox.Show(@"Do you want to REPLACE the current BONUSSHIFTS for section " +
                                                      txtSelectedSection.Text.Trim() + @" ?", @"QUESTION", MessageBoxButtons.OKCancel);

                switch (result)
                {
                    case DialogResult.OK:

                        #region Calculate the shifts per employee en output to bonusshifts
                        //Then count each employee's shifts individually

                        foreach (DataRow dr in BonusShifts.Rows)
                        {
                            DateTime drFSH = Convert.ToDateTime(dr["FSH"].ToString());
                            DateTime drLSH = Convert.ToDateTime(dr["LSH"].ToString());
                            DateTime drFSH_Participant = Convert.ToDateTime(dr["FSH_Participant"].ToString());
                            DateTime drLSH_Participant = Convert.ToDateTime(dr["LSH_Participant"].ToString());

                            int intStart = Base.calcNoOfDays(drFSH_Participant, drFSH);
                            int intEnd = Base.calcNoOfDays(drLSH_Participant, drLSH);

                            if (intStart <= 0)
                            {
                                intStart = 0;
                            }

                            if (intEnd <= 0)
                            {
                                intEnd = 44 + intEnd;
                            }
                            else
                            {
                                intEnd = 44;
                            }

                            extractAndCalcShifts(intStart, intEnd, dr);

                        }

                        #endregion

                        //On BonusShifts the column PERIOD is part of the primary key.  Therefore must be moved xxxxxxxxx
                        DataColumn dcPeriod = new DataColumn();
                        dcPeriod.ColumnName = "PERIOD";
                        BonusShifts.Columns.Remove("PERIOD");
                        BonusShifts.AcceptChanges();
                        InsertAfter(BonusShifts.Columns, BonusShifts.Columns["BONUSTYPE"], dcPeriod);

                        foreach (DataRow dr in BonusShifts.Rows)
                        {
                            dr["PERIOD"] = BusinessLanguage.Period;
                        }

                        BonusShifts.Columns.Remove("FSH_Participant");
                        BonusShifts.Columns.Remove("LSH_Participant");
                        BonusShifts.AcceptChanges();

                        string strDelete = " where section = '" + txtSelectedSection.Text.Trim() +
                                           "' and period = '" + BusinessLanguage.Period.Trim() + "'";

                        TB.saveCalculations2(BonusShifts, Base.DBConnectionString, strDelete, "BONUSSHIFTS");

                        // Update the linerespcode on bonusshifts to the meassection on participants
                        TB.InsertData(Base.DBConnectionString, "UPDATE Bonusshifts SET Bonusshifts.linerespcode = Participants.meassection " +
                                      " FROM Bonusshifts  INNER JOIN  Participants ON Bonusshifts.Section = Participants.Section " +
                                      " and Bonusshifts.Period =  Participants.PERIOD and Bonusshifts.Period = '" + BusinessLanguage.Period.Trim() +
                                      "' and Bonusshifts.employee_no = Participants.employee_no");

                        break;

                    case DialogResult.Cancel:
                        break;

                }

                #endregion

                #endregion

                this.Cursor = Cursors.Arrow;
            }
        }
Esempio n. 3
0
        private void refreshShifts()
        {
            pictBox.Visible = true;

            #region extract the sheet name and FSH and LSH of the extract
            //MessageBox.Show("maak nou instance van excel");
            ATPMain.VkExcel excel = new ATPMain.VkExcel(false);

            bool XLSX_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx");
            bool XLS_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls");

            if (XLSX_exists.Equals(true))
            {
                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx", "BONTS2011");
                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            if (XLS_exists.Equals(true))
            {
                //MessageBox.Show("nou in xls filepath");
                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls", "BONTS2011");

                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            excel.stopExcel();

            string FilePath = "";

            string FilePath_XLSX = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";

            string FilePath_XLS = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";

            XLSX_exists = File.Exists(FilePath_XLSX);
            XLS_exists = File.Exists(FilePath_XLS);

            if (XLS_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";
            }

            if (XLSX_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\Development\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";
            }

            //excel.GetExcelSheets();
            string[] sheetNames = GetExcelSheetNames(FilePath);
            string sheetName = sheetNames[0];

            string testString = sheetName.Substring(0, 3).ToString().Trim();

            if (sheetName.Substring(0, 3).ToString().Trim() != "'20")
            {
                sheetName = sheetNames[1];
            }

            if (sheetName.Substring(0, 3).ToString().Trim() != "'20")
            {
                sheetName = sheetNames[2];
            }

            if (sheetName.Substring(0, 3).ToString().Trim() != "'20")
            {
                sheetName = sheetNames[3];
            }
            #endregion

            #region import Clockshifts
            this.Cursor = Cursors.WaitCursor;
            DataTable dt = new DataTable();

            OleDbConnection con = new OleDbConnection();
            OleDbDataAdapter da;
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                    + FilePath + ";Extended Properties='Excel 8.0;'";

            /*"HDR=Yes;" indicates that the first row contains columnnames, not data.
            * "HDR=No;" indicates the opposite.
            * "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.
            * Note that this option might affect excel sheet write access negative.
            */

            da = new OleDbDataAdapter("select * from [" + sheetName + "]", con); //read first sheet named Sheet1
            da.Fill(dt);
            IEnumerable<DataRow> query1 = from locks in dt.AsEnumerable()
                                          where locks.Field<string>("MINING PROCESS").TrimEnd() != "Development"
                                          select locks;

            //Temp will contain a list of the gangs for the section
            DataTable Tempdt = query1.CopyToDataTable<DataRow>();

            dt = Tempdt.Copy();
            #region remove invalid records

            //extract the column names with length less than 3.  These columns must be deleted.
            string[] columnNames = new String[dt.Columns.Count];

            for (int i = 0; i <= dt.Columns.Count - 1; i++)
            {
                if (dt.Columns[i].ColumnName.Length <= 2)
                {
                    columnNames[i] = dt.Columns[i].ColumnName;
                }
            }

            for (Int16 i = 0; i <= columnNames.GetLength(0) - 1; i++)
            {
                if (string.IsNullOrEmpty(columnNames[i]))
                {

                }
                else
                {
                    dt.Columns.Remove(columnNames[i].ToString().Trim());
                    dt.AcceptChanges();
                }
            }

            dt.Columns.Remove("INDUSTRY NUMBER");
            dt.AcceptChanges();
            #endregion

            string strSheetFSH = string.Empty;
            string strSheetLSH = string.Empty;

            //Extract the dates from the spreadsheet - the name of the spreadsheet contains the the start and enddate of the extract
            string strSheetFSHx = sheetName.Substring(0, sheetName.IndexOf("_TO")).Replace("_", "-").Replace("'", "").Trim(); ;
            string strSheetLSHx = sheetName.Substring(sheetName.IndexOf("_TO") + 4).Replace("$", "").Replace("_", "-").Replace("'", "").Trim(); ;

            //Correct the dates and calculate the number of days extracted.
            string tested = strSheetFSHx.Substring(6, 1);
            if (strSheetFSHx.Substring(6, 1) == "-")
            {
                strSheetFSH = strSheetFSHx.Substring(0, 5) + "0" + strSheetFSHx.Substring(5);
            }
            else
            {
                strSheetFSH = strSheetFSHx;
            }

            if (strSheetLSHx.Substring(6, 1) == "-")
            {
                strSheetLSH = strSheetLSHx.Substring(0, 5) + "0" + strSheetLSHx.Substring(5);
            }
            else
            {
                strSheetLSH = strSheetLSHx;
            }

            DateTime SheetFSH = Convert.ToDateTime(strSheetFSH.ToString());
            DateTime SheetLSH = Convert.ToDateTime(strSheetLSH.ToString());

            //If the intNoOfDays < 40 then the days up to 40 must be filled with '-'
            int intNoOfDays = Base.calcNoOfDays(SheetLSH, SheetFSH);

            if (intNoOfDays <= 44)
            {
                for (int j = intNoOfDays + 1; j <= 44; j++)
                {
                    dt.Columns.Add("DAY" + j);
                }
            }
            else
            {

            }

            #region Change the column names
            //Change the column names to the correct column names.
            Dictionary<string, string> dictNames = new Dictionary<string, string>();
            DataTable varNames = TB.createDataTableWithAdapter(Base.AnalysisConnectionString,
                                 "Select * from varnames");
            dictNames.Clear();

            dictNames = TB.loadDict(varNames, dictNames);
            int counter = 0;

            //If it is a column with a date as a name.
            foreach (DataColumn column in dt.Columns)
            {
                if (column.ColumnName.Substring(0, 1) == "2")
                {
                    if (counter == 0)
                    {
                        strSheetFSH = column.ColumnName.ToString().Replace("/", "-");
                        column.ColumnName = "DAY" + counter;
                        counter = counter + 1;

                    }
                    else
                    {
                        if (column.Ordinal == dt.Columns.Count - 1)
                        {

                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;

                        }
                        else
                        {
                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;
                        }
                    }

                }
                else
                {
                    if (dictNames.Keys.Contains<string>(column.ColumnName.Trim().ToUpper()))
                    {
                        column.ColumnName = dictNames[column.ColumnName.Trim().ToUpper()];
                    }

                }
            }

            //Add the extra columns
            dt.Columns.Add("FSH");
            dt.Columns.Add("LSH");
            dt.Columns.Add("SECTION");
            dt.Columns.Add("EMPLOYEETYPE");
            dt.AcceptChanges();

            foreach (DataRow row in dt.Rows)
            {
                row["FSH"] = strSheetFSH;
                row["LSH"] = strSheetLSH;
                row["MININGTYPE"] = "STOPING";
                row["SECTION"] = row["GANG"].ToString().Substring(0, 5);
                row["EMPLOYEETYPE"] = Base.extractEmployeeType(Configs, row["WAGECODE"].ToString());

                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    if (string.IsNullOrEmpty(row[i].ToString()) || row[i].ToString() == "")
                    {
                        row[i] = "-";
                    }
                }
            }
            #endregion
            //exportToExcel("c:\\", dt);
            //Write to the database
            TB.saveCalculations2(dt, Base.DBConnectionString, "", "CLOCKEDSHIFTS");

            Application.DoEvents();
            Clocked = dt.Copy();
            grdClocked.DataSource = Clocked;

            #endregion

            #region Calculate the shifts per employee en output to bonusshifts

            string strSQL = "Select *,'0' as SHIFTS_WORKED,'0' as AWOP_SHIFTS, '0' as STRIKE_SHIFTS," +
                            "'0' as DRILLERIND,'0' AS DRILLERSHIFTS from Clockedshifts where section = '" +
                            txtSelectedSection.Text.Trim() + "'";

            string strSQLFix = "Select *,'0' as SHIFTS_WORKED from Clockedshifts";

            if (BusinessLanguage.MiningType == "STOPE")
            {
                //strSQL = strSQL.Trim() + " and bonustype = 'Stoping' ";
            }
            else
            {
                //if (BusinessLanguage.MiningType == "DEVELOPMENT")
                //{
                strSQL = strSQL.Trim();
                //+ " and bonustype = 'Development' ";
                //}
            }

            fixShifts = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQLFix);
            BonusShifts = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQL);
            //exportToExcel("c:\\", BonusShifts);
            string strCalendarFSH = dateTimePicker1.Value.ToString("yyyy-MM-dd");
            string strCalendarLSH = dateTimePicker2.Value.ToString("yyyy-MM-dd");

            DateTime CalendarFSH = Convert.ToDateTime(strCalendarFSH.ToString());
            DateTime CalendarLSH = Convert.ToDateTime(strCalendarLSH.ToString());

            sheetfhs = SheetFSH;
            sheetlhs = SheetLSH;
            int intStartDay = Base.calcNoOfDays(CalendarFSH, SheetFSH);
            int intEndDay = Base.calcNoOfDays(CalendarLSH, SheetLSH);
            int intStopDay = 0;

            if (intStartDay < 0)
            {
                //The calendarFSH falls outside the startdate of the sheet.
                intStartDay = 0;
            }
            else
            {

            }

            if (intEndDay < 0 && intEndDay < -44)
            {
                intStopDay = 0;
            }
            else
            {
                if (intEndDay < 0)
                {
                    //the LSH of the measuring period falls within the spreadsheet
                    intStopDay = intNoOfDays + intEndDay;

                }
                else
                {
                    //The LSH of the measuring period falls outside the spreadsheet
                    intStopDay = 44;
                }

                //If intStartDay < 0 then the SheetFSH is bigger than the calendarFSH.  Therefore some of the Calendar's shifts
                //were not imported.

                #region count the shifts
                //Count the the shifts

                DialogResult result = MessageBox.Show("Do you want to REFRESH the current BONUSSHIFTS for section " + txtSelectedSection.Text.Trim() + " ?", "QUESTION", MessageBoxButtons.OKCancel);

                switch (result)
                {
                    case DialogResult.OK:
                        extractAndCalcShiftsForRefresh(intStartDay, intStopDay);
                        break;

                    case DialogResult.Cancel:
                        break;

                }

                #endregion

            #endregion

                #region Extract the ganglinking of the current section
                //Remember a previous section could have been imported and calculated.  Therefore a delete can not be done on the table
                //before checking.  If a calc has run on the table, the insert must be updated with the necessary calc columns.
                //This is done in the methord extractGangLink

                //DataTable temp = TB.createDataTableWithAdapterSelectAll(Base.DBConnectionString, "GANGLINK", strWhere);

                //if (temp.Rows.Count > 0)
                //{
                //    result = MessageBox.Show("Do you want to REPLACE the current ganglinking for section " + txtSelectedSection.Text.Trim() + " ?", "QUESTION", MessageBoxButtons.OKCancel);

                //    switch (result)
                //    {
                //        case DialogResult.OK:

                //            Base.dropTrigger(Base.DBConnectionString, "Ganglink");
                //            extractGangLink();
                //            Base.createTrigger(Base.DBConnectionString, "Ganglink");
                //            break;

                //        case DialogResult.Cancel:
                //            break;

                //    }
                //}
                //else
                //{
                //    Base.dropTrigger(Base.DBConnectionString, "Ganglink");
                //    extractGangLink();
                //    Base.createTrigger(Base.DBConnectionString, "Ganglink");
                //}

                //cboMinersGangNo.Items.Clear();
                //lstNames = TB.loadDistinctValuesFromColumn(Labour, "Gang");
                //if (lstNames.Count > 1)
                //{

                //    foreach (string s in lstNames)
                //    {
                //        if (cboMinersGangNo.Items.Contains(s))
                //        { }
                //        else
                //        {
                //            cboMinersGangNo.Items.Add(s.Trim());
                //        }
                //    }
                //}

                #endregion

                #region Extract the miners of the current section
                //Remember a previous section could have been imported and calculated.  Therefore a delete can not be done on the table
                //before checking.  If a calc has run on the table, the insert must be updated with the necessary calc columns.
                //This is done in the method extractMiners

                //temp = TB.createDataTableWithAdapterSelectAll(Base.DBConnectionString, "MINERS", strWhere);

                //if (temp.Rows.Count > 0)
                //{
                //    result = MessageBox.Show("Do you want to REPLACE the current MINERS for section " + txtSelectedSection.Text.Trim() + " ?", "QUESTION", MessageBoxButtons.OKCancel);

                //    switch (result)
                //    {
                //        case DialogResult.OK:
                //            Base.dropTrigger(Base.DBConnectionString, "Miners");
                //            extractMiners();
                //            Base.createTrigger(Base.DBConnectionString, "Miners");
                //            break;

                //        case DialogResult.Cancel:
                //            break;

                //    }
                //}
                //else
                //{
                //    Base.dropTrigger(Base.DBConnectionString, "Miners");
                //    extractMiners();
                //    Base.createTrigger(Base.DBConnectionString, "Miners");

                //}
                #endregion

                #region Extract the ganglinking

                //strSQL = "BEGIN transaction; Delete from ganglink where section = '" + txtSelectedSection.Text.Trim() + "';Select '" +
                //         BusinessLanguage.BussUnit + "' as BUSSUNIT,'" + BusinessLanguage.MiningType + "' AS MININGTYPE,'" +
                //         BusinessLanguage.BonusType + "' AS BONUSTYPE,'" + txtSelectedSection.Text.Trim() +
                //         "' AS SECTION,t1.period AS PERIOD,t1.workplace,t1.gang AS GANG," +
                //         "'0'  AS SAFETYIND,'DEVELOPMENT' AS GANGTYPE " +
                //         " from production as t1" +
                //         " where t1.section = '" + txtSelectedSection.Text.Trim() + "' UNION Select '" +
                //         BusinessLanguage.BussUnit + "' as BUSSUNIT,'" + BusinessLanguage.MiningType + "' AS MININGTYPE,'" +
                //         BusinessLanguage.BonusType + "' AS BONUSTYPE,'" + txtSelectedSection.Text.Trim() +
                //         "' AS SECTION,t1.period AS PERIOD,t1.workplace,t1.gang AS GANG," +
                //         "'0'  AS SAFETYIND,'RIGGING' AS GANGTYPE " +
                //         " from production as t1" +
                //         " where t1.section = '" + txtSelectedSection.Text.Trim() + "' UNION Select '" +
                //         BusinessLanguage.BussUnit + "' as BUSSUNIT,'" + BusinessLanguage.MiningType + "' AS MININGTYPE,'" +
                //         BusinessLanguage.BonusType + "' AS BONUSTYPE,'" + txtSelectedSection.Text.Trim() +
                //         "' AS SECTION,t1.period AS PERIOD,t1.workplace,t1.gang AS GANG," +
                //         "'0'  AS SAFETYIND,'EQUIPPING' AS GANGTYPE " +
                //         " from production as t1" +
                //         " where t1.section = '" + txtSelectedSection.Text.Trim() + "' UNION Select '" +
                //         BusinessLanguage.BussUnit + "' as BUSSUNIT,'" + BusinessLanguage.MiningType + "' AS MININGTYPE,'" +
                //         BusinessLanguage.BonusType + "' AS BONUSTYPE,'" + txtSelectedSection.Text.Trim() +
                //         "' AS SECTION,t1.period AS PERIOD,t1.workplace,t1.gang AS GANG," +
                //         "'0'  AS SAFETYIND,'TRAMMING' AS GANGTYPE " +
                //         " from production as t1" +
                //         " where t1.section = '" + txtSelectedSection.Text.Trim() + "'; Commit Transaction;";

                //DataTable TmpGanglink = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQL);

                //foreach (DataRow row in TmpGanglink.Rows)
                //{
                //    if (row["GANGTYPE"].ToString() == "RIGGING")
                //    {
                //        row["GANG"] = row["GANG"].ToString().Substring(0, 5) + "C" + row["GANG"].ToString().Substring(6);

                //    }
                //    else
                //    {
                //        if (row["GANGTYPE"].ToString() == "TRAMMING")
                //        {
                //            row["GANG"] = row["GANG"].ToString().Substring(0, 5) + "E" + row["GANG"].ToString().Substring(6);

                //        }
                //        else
                //        {
                //            if (row["GANGTYPE"].ToString() == "EQUIPPING")
                //            {
                //                row["GANG"] = row["GANG"].ToString().Substring(0, 5) + "B" + row["GANG"].ToString().Substring(6);

                //            }
                //            else
                //            {
                //                row["GANGTYPE"] = "DEVELOPMENT";
                //            }
                //        }
                //    }
                //}

                //if (TmpGanglink.Rows.Count > 0)
                //{

                //    TB.saveCalculations2(TmpGanglink, Base.DBConnectionString, strWhere, "GANGLINK");
                //    Application.DoEvents();

                //}
                //else
                //{
                //    MessageBox.Show("No records for ganglinking were extracted for section: " + txtSelectedSection.Text.Trim(), "Information", MessageBoxButtons.OK);
                //}

                #endregion

                this.Cursor = Cursors.Arrow;
                File.Delete(FilePath);
                pictBox.Visible = false;
            }
        }
Esempio n. 4
0
        private void refreshLabour()
        {
            #region extract the sheet name and FSH and LSH of the extract
            ATPMain.VkExcel excel = new ATPMain.VkExcel(false);

            bool XLSX_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx");
            bool XLS_exists = File.Exists("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls");

            if (XLSX_exists.Equals(true))
            {
                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xlsx", "BONTS2011");
                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            if (XLS_exists.Equals(true))
            {

                string status = excel.OpenFile("C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\master" + BusinessLanguage.Period.Trim() + ".xls", "BONTS2011");

                excel.SaveFile(BusinessLanguage.Period.Trim(), strServerPath);
                excel.CloseFile();
            }

            excel.stopExcel();

            string FilePath = "";

            string FilePath_XLSX = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";

            string FilePath_XLS = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";

            XLSX_exists = File.Exists(FilePath_XLSX);
            XLS_exists = File.Exists(FilePath_XLS);

            if (XLS_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xls";
            }

            if (XLSX_exists.Equals(true))
            {
                FilePath = "C:\\iCalc\\Harmony\\Phakisa\\" + strServerPath + "\\Data\\adteam_" + BusinessLanguage.Period.Trim() + ".xlsx";
            }

            if (FilePath.Trim().Length == 0)
            {
                MessageBox.Show("ADTeam spreadsheet does not exist", "Information", MessageBoxButtons.OK);
                this.Cursor = Cursors.Arrow;
            }
            else
            {
                //excel.GetExcelSheets();
                string[] sheetNames = GetExcelSheetNames(FilePath);
                string sheetName = sheetNames[0];
            #endregion

            #region import Clockshifts
                this.Cursor = Cursors.WaitCursor;
                DataTable dt = new DataTable();

                OleDbConnection con = new OleDbConnection();
                OleDbDataAdapter da;
                con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                        + FilePath + ";Extended Properties='Excel 12.0;'";

                /*"HDR=Yes;" indicates that the first row contains columnnames, not data.
                * "HDR=No;" indicates the opposite.
                * "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.
                * Note that this option might affect excel sheet write access negative.
                */

                da = new OleDbDataAdapter("select * from [" + sheetName + "]", con); //read first sheet named Sheet1
                da.Fill(dt);

                IEnumerable<DataRow> query1 = from locks in dt.AsEnumerable()
                                              where locks.Field<string>("WAGE CODE").TrimEnd().Length > 0 &&
                                                    locks.Field<string>("WAGE CODE").TrimEnd().Substring(0, 1) != "0"
                                              select locks;

                //Temp will contain a list of the gangs for the section
                DataTable Tempdt = query1.CopyToDataTable<DataRow>();

                dt = Tempdt.Copy();

                #region remove invalid records

                //extract the column names with length less than 3.  These columns must be deleted.
                string[] columnNames = new String[dt.Columns.Count];

                for (int i = 0; i <= dt.Columns.Count - 1; i++)
                {
                    if (dt.Columns[i].ColumnName.Length <= 2)
                    {
                        columnNames[i] = dt.Columns[i].ColumnName;
                    }
                }

                for (Int16 i = 0; i <= columnNames.GetLength(0) - 1; i++)
                {
                    if (string.IsNullOrEmpty(columnNames[i]))
                    {

                    }
                    else
                    {
                        dt.Columns.Remove(columnNames[i].ToString().Trim());
                        dt.AcceptChanges();
                    }
                }

                if (dt.Columns.Contains("INDUSTRY NUMBER"))
                {
                    dt.Columns.Remove("INDUSTRY NUMBER");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 1"))
                {
                    dt.Columns.Remove("BONUS 1");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 2"))
                {
                    dt.Columns.Remove("BONUS 2");
                    dt.AcceptChanges();
                }
                if (dt.Columns.Contains("BONUS 3"))
                {
                    dt.Columns.Remove("BONUS 3");
                    dt.AcceptChanges();
                }

                #endregion

                string strSheetFSH = string.Empty;
                string strSheetLSH = string.Empty;

                //Extract the dates from the spreadsheet - the name of the spreadsheet contains the the start and enddate of the extract
                string strSheetFSHx = sheetName.Substring(0, sheetName.IndexOf("_TO")).Replace("_", "-").Replace("'", "").Trim(); ;
                string strSheetLSHx = sheetName.Substring(sheetName.IndexOf("_TO") + 4).Replace("$", "").Replace("_", "-").Replace("'", "").Trim(); ;

                //Correct the dates and calculate the number of days extracted.
                if (strSheetFSHx.Substring(6, 1) == "-")
                {
                    strSheetFSH = strSheetFSHx.Substring(0, 5) + "0" + strSheetFSHx.Substring(5);
                }
                else
                {
                    strSheetFSH = strSheetFSHx;
                }

                if (strSheetLSHx.Substring(6, 1) == "-")
                {
                    strSheetLSH = strSheetLSHx.Substring(0, 5) + "0" + strSheetLSHx.Substring(5);
                }
                else
                {
                    strSheetLSH = strSheetLSHx;
                }

                DateTime SheetFSH = Convert.ToDateTime(strSheetFSH.ToString());
                DateTime SheetLSH = Convert.ToDateTime(strSheetLSH.ToString());

                TB.InsertData(Base.DBConnectionString, "Update calendar set FSH = '" + strSheetFSH.ToString().Trim() +
                                                      "', LSH = '" + strSheetLSH.ToString() + "' where section = 'OFF'");

                //If the intNoOfDays < 44 then the days up to 44 must be filled with '-'
                int intNoOfDays = Base.calcNoOfDays(SheetLSH, SheetFSH);

                if (intNoOfDays <= 44)
                {
                    for (int j = intNoOfDays + 1; j <= 44; j++)
                    {
                        dt.Columns.Add("DAY" + j);
                    }
                }
                else
                {

                }

                #region Change the column names
                //Change the column names to the correct column names.
                Dictionary<string, string> dictNames = new Dictionary<string, string>();
                DataTable varNames = TB.createDataTableWithAdapter(Base.AnalysisConnectionString,
                                     "Select * from varnames");
                dictNames.Clear();

                dictNames = TB.loadDict(varNames, dictNames);
                int counter = 0;

                //If it is a column with a date as a name.
                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName.Substring(0, 1) == "2")
                    {
                        if (counter == 0)
                        {
                            strSheetFSH = column.ColumnName.ToString().Replace("/", "-");
                            column.ColumnName = "DAY" + counter;
                            counter = counter + 1;

                        }
                        else
                        {
                            if (column.Ordinal == dt.Columns.Count - 1)
                            {

                                column.ColumnName = "DAY" + counter;
                                counter = counter + 1;

                            }
                            else
                            {
                                column.ColumnName = "DAY" + counter;
                                counter = counter + 1;
                            }
                        }

                    }
                    else
                    {
                        if (dictNames.Keys.Contains<string>(column.ColumnName.Trim().ToUpper()))
                        {
                            column.ColumnName = dictNames[column.ColumnName.Trim().ToUpper()];
                        }

                    }
                }

                dt.Columns.Add("FSH");
                dt.Columns.Add("LSH");
                dt.Columns.Add("SECTION");
                dt.Columns.Add("EMPLOYEETYPE");
                dt.Columns.Add("PERIOD");      //xxxxxxxx
                dt.AcceptChanges();

                foreach (DataRow row in dt.Rows)
                {

                    row["FSH"] = strSheetFSH;
                    row["LSH"] = strSheetLSH;
                    row["MININGTYPE"] = "OFFICIALS";
                    row["BONUSTYPE"] = "SERVICES";
                    row["PERIOD"] = BusinessLanguage.Period;   //xxx

                    row["SECTION"] = "OFF";

                    if (row["WAGECODE"].ToString().Trim() == "")
                    {
                        row["WAGECODE"] = "00000";
                    }
                    else
                    {
                    }
                    row["EMPLOYEETYPE"] = Base.extractEmployeeType(Configs, row["WAGECODE"].ToString());

                    for (int i = 0; i <= dt.Columns.Count - 1; i++)
                    {
                        if (string.IsNullOrEmpty(row[i].ToString()) || row[i].ToString() == "")
                        {
                            row[i] = "-";
                        }
                    }
                }

                #endregion
                //exportToExcel("c:\\", dt);
                //Write to the database
                TB.saveCalculations2(dt, Base.DBConnectionString, "", "CLOCKEDSHIFTS");

                Application.DoEvents();

                //grdClocked.DataSource = dt;

               // MessageBox.Show("Clockedshifts records are : " + dt.Rows.Count, "INFO", MessageBoxButtons.OK);

                #endregion

            #region UPDATE the participants
                //update the table participants with the correct section.
                //The correct section's FSH and LSH must be saved.

                DataTable temp = new DataTable();
                DataTable temp2 = new DataTable();

                foreach (DataRow dr in Participants.Rows)
                {
                    IEnumerable<DataRow> query5 = from locks in Clocked.AsEnumerable()
                                                  where locks.Field<string>("EMPLOYEE_NO").TrimEnd() == dr["EMPLOYEE_NO"].ToString().Trim()
                                                  select locks;

                    try
                    {
                        temp = query5.CopyToDataTable<DataRow>();
                        //Change only if section = 'XXX'
                        if (dr["SECTION"].ToString().Trim() == "XXX")
                        {
                            dr["MEASSECTION"] = temp.Rows[0]["MEASSECTION"];
                        }
                        IEnumerable<DataRow> query6 = from locks in Calendar.AsEnumerable()
                                                      where locks.Field<string>("SECTION").TrimEnd() == temp.Rows[0]["MEASSECTION"].ToString().Trim()
                                                      select locks;
                        try
                        {
                            temp2 = query6.CopyToDataTable<DataRow>();
                            //Change only if = 0
                            if (dr["FSH"].ToString().Trim() == "0")
                            {
                                dr["FSH"] = temp2.Rows[0]["FSH"];
                            }
                            if (dr["LSH"].ToString().Trim() == "0")
                            {
                                dr["LSH"] = temp2.Rows[0]["LSH"];
                            }
                            if (dr["MONTHSHIFTS"].ToString().Trim() == "0")
                            {
                                dr["MONTHSHIFTS"] = temp2.Rows[0]["MONTHSHIFTS"];
                            }

                        }
                        catch
                        {
                            IEnumerable<DataRow> query7 = from locks in Calendar.AsEnumerable()
                                                          where locks.Field<string>("SECTION").TrimEnd() == txtSelectedSection.Text.Trim()
                                                          select locks;

                            try
                            {
                                temp = query7.CopyToDataTable<DataRow>();
                                //Change only if = 0
                                if (dr["FSH"].ToString().Trim() == "0")
                                {
                                    dr["FSH"] = temp.Rows[0]["FSH"];
                                }
                                if (dr["LSH"].ToString().Trim() == "0")
                                {
                                    dr["LSH"] = temp.Rows[0]["LSH"];
                                }
                                if (dr["MONTHSHIFTS"].ToString().Trim() == "0")
                                {
                                    dr["MONTHSHIFTS"] = temp.Rows[0]["MONTHSHIFTS"];
                                }
                            }
                            catch
                            {
                                 IEnumerable<DataRow> query8 = from locks in Calendar.AsEnumerable()
                                                          where locks.Field<string>("SECTION").TrimEnd() == "OFF"
                                                          select locks;

                                 try
                                 {
                                     temp = query8.CopyToDataTable<DataRow>();
                                     //Change only if = 0
                                     if (dr["FSH"].ToString().Trim() == "0")
                                     {
                                         dr["FSH"] = temp.Rows[0]["FSH"];
                                     }
                                     if (dr["LSH"].ToString().Trim() == "0")
                                     {
                                         dr["LSH"] = temp.Rows[0]["LSH"];
                                     }
                                     if (dr["MONTHSHIFTS"].ToString().Trim() == "0")
                                     {
                                         dr["MONTHSHIFTS"] = temp.Rows[0]["MONTHSHIFTS"];
                                     }
                                 }
                                 catch
                                 {

                                 }
                            }
                        }
                    }
                    catch
                    {
                        IEnumerable<DataRow> query8 = from locks in Calendar.AsEnumerable()
                                                      where locks.Field<string>("SECTION").TrimEnd() == "OFF"
                                                      select locks;

                        try
                        {
                            temp = query8.CopyToDataTable<DataRow>();
                            //Change only if = 0
                            dr["SECTION"] = "OFF";
                            dr["FSH"] = temp.Rows[0]["FSH"];
                            dr["LSH"] = temp.Rows[0]["LSH"];
                            dr["MONTHSHIFTS"] = temp.Rows[0]["MONTHSHIFTS"];

                        }
                        catch
                        {

                        }

                    }

                    Participants.AcceptChanges();
                }

                Participants.AcceptChanges();
                grdParticipants.DataSource = Participants;
                TB.saveCalculations2(Participants, Base.DBConnectionString, "", "PARTICIPANTS");

                string strSQL = "Select *,'0' as SHIFTS_WORKED,'0' as AWOP_SHIFTS, '0' as STRIKE_SHIFTS," +
                    "substring(gang,1,5) as MEASSECTION from Clockedshifts where employee_no in (select distinct employee_no from participants)";

                BonusShifts = TB.createDataTableWithAdapter(Base.DBConnectionString, strSQL);

                //Update bonusshifts with the correct FSH and LSH extracted from participants
                //The FSH and LSH is updatable by the Mining Manager and therefore it is taken from
                //Participants and not from calendar.

                foreach (DataRow dr in BonusShifts.Rows)
                {
                    IEnumerable<DataRow> query10 = from locks in Participants.AsEnumerable()
                                                  where locks.Field<string>("Employee_No").TrimEnd() == dr["EMPLOYEE_NO"].ToString().Trim()
                                                  select locks;
                    try
                    {
                        temp2 = query10.CopyToDataTable<DataRow>();
                        dr["FSH"] = temp2.Rows[0]["FSH"];
                        dr["LSH"] = temp2.Rows[0]["LSH"];
                        dr["MONTHSHIFTS"] = temp2.Rows[0]["MONTHSHIFTS"];
                    }
                    catch
                    {
                    }
                }

                BonusShifts.AcceptChanges();

            #endregion

            #region Calculate the shifts per employee en output to bonusshifts
                //Then count each employee's shifts individually

                foreach (DataRow dr in BonusShifts.Rows)
                {

                    string strCalendarFSH = dr["FSH"].ToString();
                    string strCalendarLSH = dr["LSH"].ToString();
                    if (strCalendarFSH.Substring(0,2) == "20")
                    {
                        DateTime CalendarFSH = Convert.ToDateTime(strCalendarFSH.ToString());
                        DateTime CalendarLSH = Convert.ToDateTime(strCalendarLSH.ToString());

                        sheetfhs = SheetFSH;
                        sheetlhs = SheetLSH;
                        int intStartDay = Base.calcNoOfDays(CalendarFSH, SheetFSH);
                        int intEndDay = Base.calcNoOfDays(CalendarLSH, SheetLSH);
                        int intStopDay = 0;

                        #region Check FSH and LSH differance
                        if (intStartDay < 0)
                        {
                            //The calendarFSH falls outside the startdate of the sheet.
                            intStartDay = 0;
                        }
                        else
                        {

                        }

                        if (intEndDay < 0 && intEndDay < -44)
                        {
                            intStopDay = 0;
                        }
                        else
                        {
                            if (intEndDay < 0)
                            {
                                //the LSH of the measuring period falls within the spreadsheet
                                intStopDay = intNoOfDays + intEndDay;

                            }
                            else
                            {
                                //The LSH of the measuring period falls outside the spreadsheet
                                intStopDay = 44;
                            }

                            //If intStartDay < 0 then the SheetFSH is bigger than the calendarFSH.  Therefore some of the Calendar's shifts
                            //were not imported.
                        #endregion

                        #region count the shifts
                            //Count the the shifts

                            extractAndCalcShifts(intStartDay, intStopDay, dr);

                            }

                            #endregion

                     }

                    #endregion
                }

                //On BonusShifts the column PERIOD is part of the primary key.  Therefore must be moved xxxxxxxxx
                DataColumn dcPeriod = new DataColumn();
                dcPeriod.ColumnName = "PERIOD";
                BonusShifts.Columns.Remove("PERIOD");
                BonusShifts.AcceptChanges();
                InsertAfter(BonusShifts.Columns, BonusShifts.Columns["BONUSTYPE"], dcPeriod);

                foreach (DataRow dr in BonusShifts.Rows)
                {
                    dr["PERIOD"] = BusinessLanguage.Period;
                }

                TB.saveCalculations2(BonusShifts, Base.DBConnectionString, "", "BONUSSHIFTS");

                this.Cursor = Cursors.Arrow;
                File.Delete(FilePath);
                }
        }