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();
}
/// <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;
}