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 bulkUploadResultMessage;

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

                    // create dataset
                    BulkUploadTDS dataSet = new BulkUploadTDS();
                    dataSet.BulkUpload.Merge(bulkUpload, true);

                    Session["bulkUpload"] = bulkUpload;

                    return true;
                }
            }
            else
            {
                lblResults.Text = "Please select a file";
                return false;
            }
        }
        // /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // 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());
        }
        // /////////////////////////////////////////////////////////////////////////////////////////////////////
        //
        // 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 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);
            }
        }
        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;
        }
 /// <summary>
 /// InitData. 
 /// </summary>
 protected override void InitData()
 {
     _data = new BulkUploadTDS();
 }
Example #7
0
        /// <summary>
        /// Save a section
        /// </summary>
        /// <param name="row">row</param>
        /// <param name="projectId">projectId</param>
        /// <param name="countryId">countryId</param>
        /// <param name="provinceId">provinceId</param>
        /// <param name="countyId">countyId</param>
        /// <param name="cityId">cityId</param>
        /// <param name="companyId">companyId</param>
        /// <returns>section_assetId</returns>
        private int SaveSection(BulkUploadTDS.BulkUploadRow row, int projectId, Int64? countryId, Int64? provinceId, Int64? countyId, Int64? cityId, int companyId)
        {
            string street = ""; if (!row.IsStreetNull()) street = row.Street;
            string subArea = ""; if (!row.IsSubAreaNull()) subArea = row.SubArea;
            string usmh = ""; if (!row.IsUSMHNull()) usmh = row.USMH;
            string dsmh = ""; if (!row.IsDSMHNull()) dsmh = row.DSMH;
            string mapLength = ""; if (!row.IsMapLengthNull()) mapLength = row.MapLength;
            string actualLength = ""; if (!row.IsActualLengthNull()) actualLength = row.ActualLength;
            string mapSize = ""; if (!row.IsMapSizeNull()) mapSize = row.MapSize;
            string confirmedSize = ""; if (!row.IsConfirmedSizeNull()) confirmedSize = row.ConfirmedSize;
            string thickness = "";

            // insert usmh (if not exists)
            int? usmh_assetId = null;
            if (usmh != "")
            {
                LfsAssetSewerMH lfsAssetSewerUsmh = new LfsAssetSewerMH(null);
                usmh_assetId = lfsAssetSewerUsmh.InsertDirect(countryId, provinceId, countyId, cityId, usmh, "", "", "", false, companyId, "", "", null, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", null, "", "", null);
            }

            // insert dsmh (if not exists)
            int? dsmh_assetId = null;
            if (dsmh != "")
            {
                LfsAssetSewerMH lfsAssetSewerDsmh = new LfsAssetSewerMH(null);
                dsmh_assetId = lfsAssetSewerDsmh.InsertDirect(countryId, provinceId, countyId, cityId, dsmh, "", "", "", false, companyId, "", "", null, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", null, "", "", null);
            }

            // insert section
            LfsAssetSewerSection lfsAssetSewerSection = new LfsAssetSewerSection(null);
            int section_assetId = lfsAssetSewerSection.InsertDirect(countryId, provinceId, countyId, cityId, row.ID, street, usmh_assetId, dsmh_assetId, mapSize, confirmedSize, mapLength, actualLength, null, null, "", "", "", "", "", actualLength, "", "", "", "", "", "", "", "", "", "", "", "", false, companyId, subArea, thickness, -1, "", DateTime.Now);

            return section_assetId;
        }