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

            // Initialize summary
            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
            WincanBulkUpload model = new WincanBulkUpload(dataSet);

            tbxSummary.Text = model.GetSectionsSummary(hdfProjectLocation.Value.ToString());
        }
        private bool StepBeginNext()
        {
            string fName = null;
            HttpPostedFile postedFile = htmlInputFile.PostedFile;

            if ((postedFile != null) && (postedFile.ContentLength > 0))
            {
                //--- Post file
                byte[] buffer = new byte[postedFile.ContentLength];
                postedFile.InputStream.Read(buffer, 0, postedFile.ContentLength);

                //--- Save posted file
                string physicalApplicationPath = Request.PhysicalApplicationPath;
                if (Request.PhysicalApplicationPath.Substring(Request.PhysicalApplicationPath.Length - 1, 1) != "\\")
                {
                    physicalApplicationPath += "\\";
                }
                fName = physicalApplicationPath + "export\\" + Path.GetFileName(postedFile.FileName);
                postedFile.SaveAs(fName);

                //--- Process bulk upload
                string wincanBulkUploadResultMessage;

                if (!ProcessBulkUpload(fName, out wincanBulkUploadResultMessage))
                {
                    lblResults.Text = wincanBulkUploadResultMessage;
                    File.Delete(fName);
                    return false;
                }
                else
                {
                    File.Delete(fName);

                    // create dataset
                    WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
                    dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);

                    Session["wincanBulkUpload"] = wincanBulkUpload;

                    return true;
                }
            }
            else
            {
                lblResults.Text = "Please select a file";
                return false;
            }
        }
        // /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // STEP1 - BEGIN
        //
        // ////////////////////////////////////////////////////////////////////////
        // STEP1 - BEGIN - METHODS
        //
        private void StepBeginIn()
        {
            // Set instruction
            Label instruction = (Label)this.Master.FindControl("lblInstruction");
            instruction.Text = "Please select a Microsoft Access file";

            //--- Initialize
            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
            WincanBulkUpload model = new WincanBulkUpload(dataSet);
            model.Table.Rows.Clear();
            wincanBulkUpload = dataSet.WincanBulkUpload;
            Session["wincanBulkUpload"] = dataSet.WincanBulkUpload;
        }
        private void Save()
        {
            // process sections
            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
            WincanBulkUpload model = new WincanBulkUpload(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);
            }
        }
        private bool ProcessBulkUpload(string fName, out string wincanBulkUploadResultMessage)
        {
            bool wincanBulkUploadProccessed = true;
            wincanBulkUploadResultMessage = "";

            AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway();

            AppSettingsReader appSettingReader = new AppSettingsReader();
            //string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Data Source=" + fName + ";";
            string accessConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + fName + ";";

            //OleDbConnection connection = new OleDbConnection(excelConnectionString);
            OleDbConnection connection = new OleDbConnection(accessConnectionString);
            string sql = "SELECT SI_T.SI_InspName AS SectionID, SO_T.SO_OpCode AS State, SI_T.SI_InspectionDir AS Direction, SI_T.SI_InspectedLengthFeet AS Distance, SO_T.SO_PositionFeet AS VideoDistance, SO_T.SO_ClockPosition1 AS ClockPosition "+
                            " , SO_T.SO_Dim1 AS DistanceToCentre, SO_T.SO_ToGoFeet AS ReverseSetup, SO_T.SO_Remark AS Comments" +
                            " FROM SI_T INNER JOIN SO_T ON SI_T.SI_ID = SO_T.SO_Inspecs_ID;";
            OleDbCommand command = null;

            OleDbDataReader dataReader = null;

            try
            {
                ArrayList validatedIds = new ArrayList();

                connection.Open();
                command = new OleDbCommand(sql, connection);
                dataReader = command.ExecuteReader();

                int id = 0;

                while (dataReader.Read())
                {
                    if (!IsEmptyRow(dataReader))
                    {
                        string sectionId = "";
                        string state = "";
                        string distance = "";
                        string direction = "";
                        string videoDistance = "";
                        string clockPosition = "";
                        string distanceToCentre = "";
                        string reverseSetup = "";
                        string comments = "";

                        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 "SectionID":
                                    if (dataCellToUpper != "NULL")
                                    {
                                        sectionId = dataCell;
                                    }
                                    else
                                    {
                                        sectionId = "";
                                        wincanBulkUploadProccessed = false;
                                    }
                                    break;

                                case "State":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        state = dataCell;
                                    }
                                    else
                                    {
                                        state = "Undefined";
                                    }
                                    break;

                                case "Direction":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        direction = dataCell;
                                    }
                                    else
                                    {
                                        direction = "U";
                                    }
                                    break;

                                case "Distance":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distance = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'Distance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distance = "";
                                    }
                                    break;

                                case "VideoDistance":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            videoDistance = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'VideoDistance' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        videoDistance = "";
                                    }
                                    break;

                                case "ClockPosition":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        clockPosition = dataCell;
                                    }
                                    else
                                    {
                                        clockPosition = "";
                                    }
                                    break;

                                case "DistanceToCentre":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            distanceToCentre = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'DistanceToCentre' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        distanceToCentre = "";
                                    }
                                    break;

                                case "ReverseSetup":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        if (Distance.IsValidDistance(dataCell))
                                        {
                                            reverseSetup = dataCell;
                                        }
                                        else
                                        {
                                            wincanBulkUploadResultMessage = "Invalid value in 'ReverseSetup' column (" + id + "). Wincan Bulk Upload ABORTED.";
                                            wincanBulkUploadProccessed = false;
                                        }
                                    }
                                    else
                                    {
                                        reverseSetup = "";
                                    }
                                    break;

                                case "Comments":
                                    if (dataCellToUpper != "NULL" && dataCellToUpper != "")
                                    {
                                        comments = dataCell;
                                    }
                                    else
                                    {
                                        comments = "";
                                    }
                                    break;

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

                            if (!wincanBulkUploadProccessed)
                            {
                                break;
                            }
                        }

                        if (wincanBulkUploadProccessed)
                        {
                            //--- Initialize
                            WincanBulkUploadTDS dataSet = new WincanBulkUploadTDS();
                            dataSet.WincanBulkUpload.Merge(wincanBulkUpload, true);
                            WincanBulkUpload model = new WincanBulkUpload(dataSet);
                            id = id + 1;
                            model.Insert(id.ToString(), sectionId, state, direction, distance, videoDistance, clockPosition, distanceToCentre, reverseSetup, comments);
                            wincanBulkUpload = dataSet.WincanBulkUpload;
                            Session["wincanBulkUpload"] = dataSet.WincanBulkUpload;
                        }
                    }
                }

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

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

                throw ex;
            }

            return (wincanBulkUploadProccessed) ? true : false;
        }
 /// <summary>
 /// InitData
 /// </summary>
 protected override void InitData()
 {
     _data = new WincanBulkUploadTDS();
 }