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);
//}
}
}
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;
}
}
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;
}
}
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);
}
}