private void LoadAuditsFile(string fileName)
{
this.Cursor = Cursors.WaitCursor;
bool importTerminated = false;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
Workbook workbook = excelApp.Workbooks.Open(fileName);
//select the first sheet
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
//find the used range in worksheet
Range excelRange = worksheet.UsedRange;
object[,] valueArray = excelRange.Value2;
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
//clean up stuffs
workbook.Close(false);
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
OleDbCommand cmd = new OleDbCommand("", MainForm.connection);
string tempTable = "TEMP_AUDIT_" + MainForm.userName.ToUpper();
string sqlText =
"create table " + tempTable + " (TEMP_ID counter Primary key, " +
"VESSEL_NAME varchar(255), FLEET_TEAM varchar(50), GRADE varchar(50), TITLE varchar(255), " +
"INSPECTED_DATE DateTime, LEAD_AUDITOR varchar(255), DESCRIPTIONS Memo)";
if (!MainForm.tempTableCreate(tempTable, sqlText))
{
MessageBox.Show("Failed to create temporary table", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
int colVessel = 1;
int colFleet = 2;
int colGrade = 3;
int colTitle = 4;
int colDate = 5;
int colAuditor = 6;
int colDescription = 7;
for (int i = 0; i < cols; i++)
{
switch (valueArray[1, i + 1].ToString())
{
case "vessel_name":
colVessel = i + 1;
break;
case "fleet_team":
colFleet = i + 1;
break;
case "grade":
colGrade = i + 1;
break;
case "title":
colTitle = i + 1;
break;
case "inspected_date":
colDate = i + 1;
break;
case "lead_auditor":
colAuditor = i + 1;
break;
case "description":
colDescription = i + 1;
break;
}
}
FormProgress progress = new FormProgress(rows - 1);
progress.Show();
System.Windows.Forms.Application.DoEvents();
int lineCounter = 0;
string cmdText = "";
for (int row = 2; row <= rows; row++)
{
{
lineCounter++;
if (lineCounter < 10)
{
if (cmdText.Length == 0)
{
string str = "";
if (valueArray[row, colVessel] != null)
{
str = valueArray[row, colVessel].ToString();
}
if (str.Length > 0)
{
cmdText =
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, colVessel].ToString()) + "' as VESSEL_NAME, '" +
MainForm.StrToSQLStr(valueArray[row, colFleet].ToString()) + "' as FLEET_TEAM, '" +
MainForm.StrToSQLStr(valueArray[row, colGrade].ToString()) + "' as GRADE, '" +
MainForm.StrToSQLStr(valueArray[row, colTitle].ToString()) + "' as TITLE, " +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, colDate]))) + " as INSPECTED_DATE, '" +
MainForm.StrToSQLStr(valueArray[row, colAuditor].ToString()) + "' as LEAD_AUDITOR, '" +
MainForm.StrToSQLStr(valueArray[row, colDescription].ToString()) + "' as DESCRIPTIONS \n" +
"from OPTIONS";
}
}
else
{
string str = "";
if (valueArray[row, colVessel] != null)
{
str = valueArray[row, colVessel].ToString();
}
if (str.Length > 0)
{
if (cmdText.Length == 0)
{
cmdText =
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, colVessel].ToString()) + "' as VESSEL_NAME, '" +
MainForm.StrToSQLStr(valueArray[row, colFleet].ToString()) + "' as FLEET_TEAM, '" +
MainForm.StrToSQLStr(valueArray[row, colGrade].ToString()) + "' as GRADE, '" +
MainForm.StrToSQLStr(valueArray[row, colTitle].ToString()) + "' as TITLE, " +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, colDate]))) + " as INSPECTED_DATE, '" +
MainForm.StrToSQLStr(valueArray[row, colAuditor].ToString()) + "' as LEAD_AUDITOR, '" +
MainForm.StrToSQLStr(valueArray[row, colDescription].ToString()) + "' as DESCRIPTIONS \n" +
"from OPTIONS";
}
else
{
cmdText = cmdText + "\n union \n" +
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, colVessel].ToString()) + "' as VESSEL_NAME, '" +
MainForm.StrToSQLStr(valueArray[row, colFleet].ToString()) + "' as FLEET_TEAM, '" +
MainForm.StrToSQLStr(valueArray[row, colGrade].ToString()) + "' as GRADE, '" +
MainForm.StrToSQLStr(valueArray[row, colTitle].ToString()) + "' as TITLE, " +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, colDate]))) + " as INSPECTED_DATE, '" +
MainForm.StrToSQLStr(valueArray[row, colAuditor].ToString()) + "' as LEAD_AUDITOR, '" +
MainForm.StrToSQLStr(valueArray[row, colDescription].ToString()) + "' as DESCRIPTIONS \n" +
"from OPTIONS";
}
}
}
}
else
{
string str = "";
if (valueArray[row, colVessel] != null)
{
str = valueArray[row, colVessel].ToString();
}
if (str.Length > 0)
{
if (cmdText.Length == 0)
{
cmdText =
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, colVessel].ToString()) + "' as VESSEL_NAME, '" +
MainForm.StrToSQLStr(valueArray[row, colFleet].ToString()) + "' as FLEET_TEAM, '" +
MainForm.StrToSQLStr(valueArray[row, colGrade].ToString()) + "' as GRADE, '" +
MainForm.StrToSQLStr(valueArray[row, colTitle].ToString()) + "' as TITLE, " +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, colDate]))) + " as INSPECTED_DATE, '" +
MainForm.StrToSQLStr(valueArray[row, colAuditor].ToString()) + "' as LEAD_AUDITOR, '" +
MainForm.StrToSQLStr(valueArray[row, colDescription].ToString()) + "' as DESCRIPTIONS \n" +
"from OPTIONS";
}
else
{
cmdText = cmdText + "\n union \n" +
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, colVessel].ToString()) + "' as VESSEL_NAME, '" +
MainForm.StrToSQLStr(valueArray[row, colFleet].ToString()) + "' as FLEET_TEAM, '" +
MainForm.StrToSQLStr(valueArray[row, colGrade].ToString()) + "' as GRADE, '" +
MainForm.StrToSQLStr(valueArray[row, colTitle].ToString()) + "' as TITLE, " +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, colDate]))) + " as INSPECTED_DATE, '" +
MainForm.StrToSQLStr(valueArray[row, colAuditor].ToString()) + "' as LEAD_AUDITOR, '" +
MainForm.StrToSQLStr(valueArray[row, colDescription].ToString()) + "' as DESCRIPTIONS \n" +
"from OPTIONS";
}
}
if (cmdText.Length > 0)
{
cmd.CommandText =
"insert into " + tempTable + " (VESSEL_NAME, FLEET_TEAM, GRADE, TITLE, INSPECTED_DATE, LEAD_AUDITOR, DESCRIPTIONS) \n" +
"select * from (\n" +
cmdText + "\n)";
MainForm.cmdExecute(cmd);
}
progress.position = row - 1;
System.Windows.Forms.Application.DoEvents();
lineCounter = 0;
cmdText = "";
if (progress.terminate)
{
importTerminated = true;
progress.Close();
//blockButtons = false;
break;
}
}
}
}
if (lineCounter > 0)
{
if (cmdText.Length > 0)
{
cmd.CommandText =
"insert into " + tempTable + " (VESSEL_NAME, FLEET_TEAM, GRADE, TITLE, INSPECTED_DATE, LEAD_AUDITOR, DESCRIPTIONS) \n" +
"select * from (\n" +
cmdText + "\n)";
MainForm.cmdExecute(cmd);
}
progress.position = rows - 1;
System.Windows.Forms.Application.DoEvents();
lineCounter = 0;
if (progress.terminate)
{
importTerminated = true;
progress.Close();
//blockButtons = false;
}
}
if (!importTerminated)
{
progress.Close();
cmd.CommandText =
"select MIN(INSPECTED_DATE) as MIN_DATE, MAX(INSPECTED_DATE) as MAX_DATE \n" +
"from [" + tempTable + "]";
OleDbDataReader reader = cmd.ExecuteReader();
DateTime MinDate = DateTime.MinValue;
DateTime MaxDate = DateTime.MinValue;
if (reader.HasRows)
{
while (reader.Read())
{
MinDate = Convert.ToDateTime(reader["MIN_DATE"]);
MaxDate = Convert.ToDateTime(reader["MAX_DATE"]);
}
}
reader.Close();
if (MinDate > DateTime.MinValue && MaxDate > DateTime.MinValue)
{
cmd.CommandText =
"delete from AUDITS \n" +
"where \n" +
"INSPECTED_DATE >=" + MainForm.DateTimeToQueryStr(MinDate) + " \n" +
"and INSPECTED_DATE<=" + MainForm.DateTimeToQueryStr(MaxDate);
MainForm.cmdExecute(cmd);
}
cmd.CommandText =
"insert into AUDITS(VESSEL_NAME, FLEET_TEAM, GRADE, TITLE, INSPECTED_DATE, LEAD_AUDITOR, DESCRIPTIONS) \n" +
"select Q.VESSEL_NAME, Q.FLEET_TEAM, Q.GRADE, Q.TITLE, Q.INSPECTED_DATE, Q.LEAD_AUDITOR, Q.DESCRIPTIONS \n" +
"from " + tempTable + " as Q left join AUDITS as C \n" +
"on Q.VESSEL_NAME=C.VESSEL_NAME \n" +
"and Q.FLEET_TEAM=C.FLEET_TEAM \n" +
"and Q.GRADE=C.GRADE \n" +
"and Q.TITLE=C.TITLE \n" +
"and Q.INSPECTED_DATE=C.INSPECTED_DATE \n" +
"and Q.LEAD_AUDITOR=C.LEAD_AUDITOR \n" +
"and Left(Q.DESCRIPTIONS,255)=Left(C.DESCRIPTIONS,255) \n" +
"where \n" +
"C.VESSEL_NAME is null \n" +
"and Q.INSPECTED_DATE>DateSerial(2005,1,1)";
MainForm.cmdExecute(cmd);
//MainForm.UpdateVesselID4CrewOnBoard();
UpdateAudits();
}
MainForm.tempTableDrop(tempTable);
this.Cursor = Cursors.Default;
}
private void LoadFromExcel(string fileName)
{
Size minSize = this.MinimumSize;
Size maxSize = this.MaximumSize;
try
{
this.Cursor = Cursors.WaitCursor;
bool importTerminated = false;
blockButtons = true;
this.MaximumSize = this.Size;
this.MinimumSize = this.Size;
this.MaximizeBox = false;
this.MinimizeBox = false;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
Workbook workbook = excelApp.Workbooks.Open(fileName);
//select the first sheet
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
//find the used range in worksheet
Range excelRange = worksheet.UsedRange;
object[,] valueArray = excelRange.Value2;
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
//clean up stuffs
workbook.Close(false);
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
OleDbCommand cmd = new OleDbCommand("", MainForm.connection);
string tempTable = "TEMP_" + MainForm.userName.ToUpper();
//cmd.CommandText = "drop table " + tempTable;
//MainForm.cmdExecute(cmd);
MainForm.tempTableCreate(tempTable, "create table " + tempTable + " (TEMP_ID counter Primary key, CREW_NAME varchar(255), VESSEL_NAME varchar(255), CREW_POSITION varchar(255), DATE_ON DateTime, DATE_OFF DateTime)");
int nameCol = 1;
int vesselCol = 2;
int positionCol = 3;
int onDateCol = 4;
int offDateCol = 5;
FormProgress progress = new FormProgress(rows - 1);
progress.Show();
System.Windows.Forms.Application.DoEvents();
int lineCounter = 0;
string cmdText = "";
for (int row = 1; row <= rows; row++)
{
if (row == 1)
{
for (int col = 1; col <= cols; col++)
{
string fieldName = "";
if (valueArray[row, col] != null)
{
fieldName = valueArray[row, col].ToString();
}
switch (fieldName)
{
case "Name":
nameCol = col;
break;
case "Vessel":
vesselCol = col;
break;
case "Position":
positionCol = col;
break;
case "SIGN ON":
onDateCol = col;
break;
case "SIGN OFF":
offDateCol = col;
break;
}
}
}
else
{
lineCounter++;
if (lineCounter < 10)
{
if (cmdText.Length == 0)
{
cmdText =
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, nameCol].ToString()) + "' as CREW_NAME,'" +
MainForm.StrToSQLStr(valueArray[row, vesselCol].ToString()) + "' as VESSEL_NAME,'" +
MainForm.StrToSQLStr(valueArray[row, positionCol].ToString()) + "' as CREW_POSITION," +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, onDateCol]))) + " as DATE_ON," +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, offDateCol]))) + " as DATE_OFF \n" +
"from OPTIONS";
}
else
{
cmdText = cmdText + "\n union \n" +
"select TOP 1 \n" +
"'" + MainForm.StrToSQLStr(valueArray[row, nameCol].ToString()) + "','" +
MainForm.StrToSQLStr(valueArray[row, vesselCol].ToString()) + "','" +
MainForm.StrToSQLStr(valueArray[row, positionCol].ToString()) + "'," +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, onDateCol]))) + "," +
MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, offDateCol]))) + " \n" +
"from OPTIONS";
}
}
else
{
cmd.CommandText =
"insert into " + tempTable + " (CREW_NAME, VESSEL_NAME, CREW_POSITION, DATE_ON, DATE_OFF) \n" +
"select * from (\n" +
cmdText + "\n)";
MainForm.cmdExecute(cmd);
progress.position = row - 1;
System.Windows.Forms.Application.DoEvents();
lineCounter = 0;
cmdText = "";
if (progress.terminate)
{
importTerminated = true;
progress.Close();
blockButtons = false;
break;
}
}
//cmd.CommandText =
// "insert into " + tempTable + " (CREW_NAME, VESSEL_NAME, CREW_POSITION, DATE_ON, DATE_OFF) \n" +
// "values('" + MainForm.StrToSQLStr(valueArray[row, nameCol].ToString()) + "','" +
// MainForm.StrToSQLStr(valueArray[row, vesselCol].ToString()) + "','" +
// MainForm.StrToSQLStr(valueArray[row, positionCol].ToString()) + "'," +
// MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, onDateCol]))) + "," +
// MainForm.DateTimeToQueryStr(DateTime.FromOADate(Convert.ToDouble(valueArray[row, offDateCol]))) + ")";
}
}
if (lineCounter > 0)
{
cmd.CommandText =
"insert into " + tempTable + " (CREW_NAME, VESSEL_NAME, CREW_POSITION, DATE_ON, DATE_OFF) \n" +
"select * from (\n" +
cmdText + "\n)";
MainForm.cmdExecute(cmd);
progress.position = rows - 1;
System.Windows.Forms.Application.DoEvents();
lineCounter = 0;
if (progress.terminate)
{
importTerminated = true;
progress.Close();
blockButtons = false;
}
}
if (!importTerminated)
{
progress.Close();
cmd.CommandText =
"delete from CREW_ON_BOARD \n" +
"where \n" +
"DATE_OFF is Null";
MainForm.cmdExecute(cmd);
cmd.CommandText =
"insert into CREW_ON_BOARD(CREW_NAME,VESSEL_NAME,CREW_POSITION,DATE_ON,DATE_OFF) \n" +
"select Q.CREW_NAME, Q.VESSEL_NAME, Q.CREW_POSITION, Q.DATE_ON, Q.DATE_OFF \n" +
"from " + tempTable + " as Q left join CREW_ON_BOARD as C \n" +
"on Q.CREW_NAME=C.CREW_NAME \n" +
"and Q.VESSEL_NAME=C.VESSEL_NAME \n" +
"and Q.CREW_POSITION=C.CREW_POSITION \n" +
"and Q.DATE_ON=C.DATE_ON \n" +
"and Q.DATE_OFF=C.DATE_OFF \n" +
"where \n" +
"C.CREW_NAME is null \n" +
"and Q.DATE_ON>DateSerial(2005,1,1)";
MainForm.cmdExecute(cmd);
cmd.CommandText =
"update CREW_ON_BOARD set \n" +
"DATE_OFF=Null \n" +
"where DATE_OFF<DateSerial(2000,1,1)";
MainForm.cmdExecute(cmd);
MainForm.UpdateVesselGuid4CrewOnBoard();
DS.Tables["CREW_ON_BOARD"].Clear();
crewAdapter.Fill(DS, "CREW_ON_BOARD");
}
MainForm.tempTableDrop(tempTable);
}
finally
{
this.MinimizeBox = true;
this.MaximizeBox = true;
this.MaximumSize = maxSize;
this.MinimumSize = minSize;
blockButtons = false;
this.Cursor = Cursors.Default;
}
}