Exemplo n.º 1
0
        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;
        }
Exemplo n.º 2
0
        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;
            }
        }