// /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // STEP2 - SUMMARY
        //
        // ////////////////////////////////////////////////////////////////////////
        // STEP2 - SUMMARY - METHODS
        //
        private void StepSummaryIn()
        {
            // Set instruction
            Label instruction = (Label)this.Master.FindControl("lblInstruction");
            instruction.Text = "Summary";

            // Initialize summary
            BulkUploadTDS dataSet = new BulkUploadTDS();
            dataSet.BulkUpload.Merge(bulkUpload, true);
            BulkUpload model = new BulkUpload(dataSet);

            tbxSummary.Text = model.GetSectionsSummary(hdfProjectLocation.Value.ToString());
        }
        private void Save()
        {
            // process sections
            BulkUploadTDS dataSet = new BulkUploadTDS();
            dataSet.BulkUpload.Merge(bulkUpload, true);
            BulkUpload model = new BulkUpload(dataSet);

            // get parameters
            Int64 countryId = int.Parse(hdfCountryId.Value);
            Int64? provinceId = null; if (hdfProvinceId.Value != "") provinceId = Int64.Parse(hdfProvinceId.Value);
            Int64? countyId = null; if (hdfCountyId.Value != "") countyId = Int64.Parse(hdfCountyId.Value);
            Int64? cityId = null; if (hdfCityId.Value != "") cityId = Int64.Parse(hdfCityId.Value);
            int loginId = Convert.ToInt32(Session["loginID"]);

            // save to database
            DB.Open();
            DB.BeginTransaction();
            try
            {
                model.Save(int.Parse(hdfProjectId.Value), countryId, provinceId, countyId, cityId, int.Parse(hdfCompanyId.Value), loginId);

                DB.CommitTransaction();
            }
            catch (Exception ex)
            {
                DB.RollbackTransaction();

                string url = string.Format("./../../error_page.aspx?error={0}", ex.Message.Replace('\n', ' '));
                Response.Redirect(url);
            }
        }
        // /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // STEP1 - BEGIN
        //
        // ////////////////////////////////////////////////////////////////////////
        // STEP1 - BEGIN - METHODS
        //
        private void StepBeginIn()
        {
            // Set instruction
            Label instruction = (Label)this.Master.FindControl("lblInstruction");
            instruction.Text = "Please select a Microsoft Excel file";

            //--- Initialize
            BulkUploadTDS dataSet = new BulkUploadTDS();
            dataSet.BulkUpload.Merge(bulkUpload, true);
            BulkUpload model = new BulkUpload(dataSet);
            model.Table.Rows.Clear();
            bulkUpload = dataSet.BulkUpload;
            Session["bulkUpload"] = dataSet.BulkUpload;
        }
        private bool ProcessBulkUpload(string fName, out string bulkUploadResultMessage)
        {
            bool bulkUploadProccessed = true;
            bulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            string excelConnectionString = appSettingReader.GetValue("ExcelConnectionString", typeof(System.String)).ToString() + fName + ";";
            OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbCommand command = null;
            OleDbDataReader dataReader = null;

            try
            {
                ArrayList validatedIds = new ArrayList();

                try
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            string raWork = "";
                            try
                            {
                                raWork = dataReader.GetValue(dataReader.GetOrdinal("RAWork")).ToString().ToUpper().Trim(); }
                            catch
                            {
                            }

                            string fllWork = "";
                            try
                            {
                                fllWork = dataReader.GetValue(dataReader.GetOrdinal("FLLWork")).ToString().ToUpper().Trim();
                            }
                            catch
                            {
                            }

                            string jlWork = "";
                            try
                            {
                                jlWork = dataReader.GetValue(dataReader.GetOrdinal("JLWork")).ToString().ToUpper().Trim();
                            }
                            catch
                            {
                            }

                            if (bulkUploadProccessed)
                            {
                                if (((raWork != null) && (raWork != "")) || ((fllWork != null) && (fllWork != "")) || ((jlWork != null) && (jlWork != "")))
                                {
                                    if ((raWork.ToUpper() == "YES") || (fllWork.ToUpper() == "YES") || (jlWork.ToUpper() == "YES"))
                                    {
                                        bulkUploadProccessed = true;
                                    }
                                    else
                                    {
                                        bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED.";
                                        bulkUploadProccessed = false;
                                    }
                                }
                                else
                                {
                                    bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED.";
                                    bulkUploadProccessed = false;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    bulkUploadResultMessage = "You did not define the 'sections' data range. Bulk upload ABORTED. Original message: " + ex.Message;
                    bulkUploadProccessed = false;

                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }

                //--- Process bulk upload
                if (bulkUploadProccessed)
                {
                    connection.Open();
                    command = new OleDbCommand("select * from [sections]", connection);
                    dataReader = command.ExecuteReader();

                    int id = 0;

                    while (dataReader.Read())
                    {
                        if (!IsEmptyRow(dataReader))
                        {
                            string street = "";
                            string clientId = "";
                            string subArea = "";
                            string usmh = "";
                            string dsmh = "";
                            string mapSize = "";
                            string confirmedSize = "";
                            string mapLength = "";
                            string actualLength = "";
                            bool raWork = false;
                            string raComments = "";
                            bool fllWork = false;
                            string fllComments = "";
                            bool jlWork = false;
                            string dataCell = null;
                            string dataCellToUpper = null;

                            //--- ... fill section row
                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                dataCell = dataReader.GetValue(i).ToString().Trim();
                                dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper();

                                switch (dataReader.GetName(i).Trim())
                                {
                                    case "Street":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            street = dataCell;
                                        }
                                        else
                                        {
                                            street = "";
                                        }
                                        break;

                                    case "Client ID":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            clientId = dataCell;
                                        }
                                        else
                                        {
                                            clientId = "";
                                        }
                                        break;

                                    case "SubArea":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            subArea = dataCell;
                                        }
                                        else
                                        {
                                            subArea = "";
                                        }
                                        break;

                                    case "USMH":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            usmh = dataCell;
                                        }
                                        else
                                        {
                                            usmh = "";
                                        }
                                        break;

                                    case "DSMH":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            dsmh = dataCell;
                                        }
                                        else
                                        {
                                            dsmh = "";
                                        }
                                        break;

                                    case "MapSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'MapSize' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            mapSize = "";
                                        }
                                        break;

                                    case "ConfirmedSize":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                confirmedSize = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ConfirmedSize' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            confirmedSize = "";
                                        }
                                        break;

                                    case "MapLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                mapLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'MapLength' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            mapLength = "";
                                        }
                                        break;

                                    case "ActualLength":
                                        if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                        {
                                            if (Distance.IsValidDistance(dataCell))
                                            {
                                                actualLength = dataCell;
                                            }
                                            else
                                            {
                                                bulkUploadResultMessage = "Invalid value in 'ActualLength' column (" + id + "). Bulk upload ABORTED.";
                                                bulkUploadProccessed = false;
                                            }
                                        }
                                        else
                                        {
                                            actualLength = "";
                                        }
                                        break;

                                    case "RAWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            raWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'RAWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    case "RAComments":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            raComments = dataCell;
                                        }
                                        else
                                        {
                                            raComments = "";
                                        }
                                        break;

                                    case "FLLWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            fllWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'FLLWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    case "FLLComments":
                                        if (dataCellToUpper != "NULL")
                                        {
                                            fllComments = dataCell;
                                        }
                                        else
                                        {
                                            fllComments = "";
                                        }
                                        break;

                                    case "JLWork":
                                        if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO"))
                                        {
                                            jlWork = (dataCellToUpper == "YES") ? true : false;
                                        }
                                        else
                                        {
                                            bulkUploadResultMessage = "Invalid value in 'JLWork' column (" + id + "). Bulk upload ABORTED.";
                                            bulkUploadProccessed = false;
                                        }
                                        break;

                                    default:
                                        bulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range.";
                                        bulkUploadProccessed = false;
                                        break;
                                }

                                if (!bulkUploadProccessed)
                                {
                                    break;
                                }
                            }

                            if (bulkUploadProccessed)
                            {
                                //--- Initialize
                                BulkUploadTDS dataSet = new BulkUploadTDS();
                                dataSet.BulkUpload.Merge(bulkUpload, true);
                                BulkUpload model = new BulkUpload(dataSet);
                                id = id + 1;
                                model.Insert(id.ToString(), street, clientId, subArea, usmh, dsmh, mapSize, confirmedSize, mapLength, actualLength, raWork, raComments, fllWork, fllComments, jlWork);
                                bulkUpload = dataSet.BulkUpload;
                                Session["bulkUpload"] = dataSet.BulkUpload;
                            }
                        }
                    }

                    dataReader.Close();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                }

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }

                throw ex;
            }

            return (bulkUploadProccessed) ? true : false;
        }