public TaxonomyReportModel[] GetTaxonomyReportsByTaxonomyId(string taxonomyId)
{
string query = @"SELECT *
FROM XMS_TAXONOMY_REPORTS
WHERE TAXONOMY_ID = :TaxonomyId
ORDER BY ID";
return(Connection.Query(query, new
{
TaxonomyId = taxonomyId
}).Select(dbRow => new TaxonomyReportModel()
{
Currency = dbRow.CURRENCY,
Decimals = dbRow.DECIMALS,
DecimalDecimals = dbRow.DECIMAL_DECIMALS,
Description = dbRow.DESCRIPTION,
EntityIdentifier = dbRow.ENTITY_IDENTIFIER,
EntitySchema = dbRow.ENTITY_SCHEMA,
EntryUri = dbRow.ENTRY_URI,
FileName = dbRow.FILE_NAME,
Id = dbRow.ID,
IntegerDecimals = dbRow.INTEGER_DECIMALS,
MonetaryDecimals = dbRow.MONETARY_DECIMALS,
PeriodType = OracleHelpers.StringToPeriodType(dbRow.PERIOD_TYPE),
PureDecimals = dbRow.PURE_DECIMALS,
SharesDecimals = dbRow.SHARES_DECIMALS,
TaxonomyId = dbRow.TAXONOMY_ID,
TnProcessorId = dbRow.TN_PROCESSOR_ID,
TnRevisionId = dbRow.TN_REVISION_ID,
}).ToArray());
}
public static bool RegisterWithoutEmail(string username, string password)
{
// CREATE TABLE earthfusion_users(
// user_id NUMBER,
// user_name VARCHAR2(50),
// user_email VARCHAR2(50),
// user_password_hashed VARCHAR2(66),
// user_status VARCHAR2(50),
// user_role VARCHAR2(50),
// PRIMARY KEY(user_id)
// );
// table structure:
// user_id: uuid. This is the unique identification of an user.
// user_name: username/nickname
// user_email: user's email
// user_password_hashed: hashed password. SHA256 only.
// user_status: is the user enabled? "enabled"/"disabled"
// user_role: administrator/user
string oracleSpatialAdminUsername = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_ADMIN_DB_USERNAME"];
string oracleSpatialAdminPassword = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_ADMIN_DB_PASSWORD"];
OracleConnection conn = OracleHelpers.GetOracleConnection(oracleSpatialAdminUsername, oracleSpatialAdminPassword, false);
// currently the frontend register without email address
string emailAddress = "*****@*****.**";
return(CreateUserRow(conn, username, emailAddress, password));
}
public static UserInformation Login(string username, string password)
{
string oracleUsername = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_DB_USERNAME"];
string oraclePassword = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_DB_PASSWORD"];
OracleConnection conn = OracleHelpers.GetOracleConnection(oracleUsername, oraclePassword, false);
// Get user information
List <UserInformation> selectedResult = GetUserInformation(conn, username);
if (selectedResult.Count < 1)
{
Logging.Warning("EarthFusion.SessionHelpers.Login", "No matching username in raw data");
return(null);
}
string userPasswordHashed = GenericHelpers.ComputeSha256Hash(password);
Logging.Info("EarthFusion.SessionHelpers.Login", "request has a hashed password of " + userPasswordHashed);
foreach (UserInformation userInformation in selectedResult)
{
Logging.Info("EarthFusion.SessionHelpers.Login", "comparing user with uuid " + userInformation.userId.ToString());
Logging.Info("EarthFusion.SessionHelpers.Login", "This user has a hashed password of " + userInformation.userPasswordHashed);
if (userPasswordHashed == userInformation.userPasswordHashed)
{
Logging.Info("EarthFusion.SessionHelpers.Login", "uuid " + userInformation.userId.ToString() + " seems good!");
return(userInformation);
}
}
return(null);
}
public static UserInformation Validate(string sessionId)
{
string oracleUsername = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_DB_USERNAME"];
string oraclePassword = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_DB_PASSWORD"];
OracleConnection conn = OracleHelpers.GetOracleConnection(oracleUsername, oraclePassword, false);
return(ValidateSession(conn, sessionId));
}
public PeriodTypeModel[] GetPeriodTypes()
{
return(Connection.Query("SELECT ID, DESCRIPTION FROM XMS_PERIOD_TYPES").Select(
q => new PeriodTypeModel()
{
PeriodType = OracleHelpers.StringToPeriodType(q.ID),
Description = q.DESCRIPTION
}
).ToArray());
}
public static bool CreateUserRow(OracleConnection conn, string username, string emailAddress, string password)
{
// insert into earthfusion_users
// (user_id, user_name, user_email, USER_PASSWORD_HASHED, USER_STATUS, USER_ROLE)
// values
// (1, 'marshmallow', '*****@*****.**', '5a9fee2cb0e686d7d9022dfc72ccb160d533c668059d1acfcf5da53d517f2d46', 'enabled', 'administrator');
// check duplicate username
if (OracleHelpers.IsRowExistInColumnInTableName(conn, username, "SPATIAL_ADMIN.EARTHFUSION_USERS", "user_name"))
{
return(false);
}
// generate uuid
int uuid = 0;
Random random = new System.Random();
while (true)
{
uuid = random.Next(2, 114514);
// tests proved that we can use string to query int
if (!OracleHelpers.IsRowExistInColumnInTableName(conn, uuid.ToString(), "SPATIAL_ADMIN.EARTHFUSION_USERS", "user_id"))
{
break;
}
}
string hashedUserPassword = GenericHelpers.ComputeSha256Hash(password);
string insertString = "insert into earthfusion_users ";
insertString += "(user_id, user_name, user_email, USER_PASSWORD_HASHED, USER_STATUS, USER_ROLE) ";
insertString += "values ";
insertString += "(";
insertString += uuid.ToString();
insertString += ", '";
insertString += username;
insertString += "', '";
insertString += emailAddress;
insertString += "', '";
insertString += hashedUserPassword;
insertString += "', '";
insertString += "enabled";
insertString += "', '";
insertString += "user";
insertString += "')";
OracleCommand command = new OracleCommand(insertString, conn);
conn.Open();
OracleDataReader reader = command.ExecuteReader();
conn.Close();
return(true);
}
public void Update(TaxonomyReportModel entity)
{
LogLine(string.Format("update {0} - {1} in database.", entity.Id, entity.Description));
int rowsAffected = Connection.Execute(@"UPDATE XMS_TAXONOMY_REPORTS
SET DESCRIPTION =: Description,
PERIOD_TYPE = :PeriodType,
ENTRY_URI = :EntryUri,
FILE_NAME = :FileName,
ENTITY_IDENTIFIER = :EntityIdentifire,
CURRENCY = :Currency,
DECIMALS = :Decimals,
ENTITY_SCHEMA = :EntitySchema,
DECIMAL_DECIMALS = :DecimalDecimals,
INTEGER_DECIMALS = :IntegerDecimals,
MONETARY_DECIMALS = :MonetaryDecimals,
PURE_DECIMALS = :PureDecimals,
SHARES_DECIMALS = :SharesDecimals,
TN_PROCESSOR_ID = :TnProcessorId,
TN_REVISION_ID = :TnRevisonId
WHERE TAXONOMY_ID = :TaxonomyId and ID = :Id",
new
{
Description = entity.Description,
PeriodType = OracleHelpers.PeriodTypeToString(entity.PeriodType),
TaxonomyId = entity.TaxonomyId,
Id = entity.Id,
EntryUri = entity.EntryUri,
FileName = entity.FileName,
EntityIdentifire = entity.EntityIdentifier,
Currency = entity.Currency,
Decimals = entity.Decimals,
EntitySchema = entity.EntitySchema,
DecimalDecimals = entity.DecimalDecimals,
IntegerDecimals = entity.IntegerDecimals,
MonetaryDecimals = entity.MonetaryDecimals,
PureDecimals = entity.PureDecimals,
SharesDecimals = entity.SharesDecimals,
TnProcessorId = entity.TnProcessorId,
TnRevisonId = entity.TnRevisionId,
}
);
if (rowsAffected == 0)
{
throw new NoRowsAffectedException("Update TaxonomyReport failed rowsAffected :" + rowsAffected);
}
}
public BussinessVitalityReport AnalysisBussinessVitalityReport(string sessionId, double ullog, double ullat, double lrlog, double lrlat, int rowNum, int colNum)
{
UserInformation user = SessionHelpers.Validate(sessionId);
if (user == null)
{
return(null);
}
if (rowNum * colNum > 200)
{
return(null);
}
BussinessVitalityReport report = new BussinessVitalityReport();
report.userId = user.userId;
report.rowNum = rowNum;
report.colNum = colNum;
report.date = DateTime.Now;
report.ulLongitude = ullog;
report.ulLatitude = ullat;
report.ldLongitude = lrlog;
report.ldLatitude = lrlat;
int i, j;
double dellog = (lrlog - ullog) / colNum;
double dellat = (ullat - lrlat) / rowNum;
for (i = 0; i < colNum; i++)
{
for (j = 0; j < rowNum; j++)
{
double cullog, cullat, clrlog, clrlat;
cullog = ullog + dellog * i;
cullat = ullat - dellat * j;
clrlog = cullog + dellog;
clrlat = cullat - dellat;
int ctrafficAccessibilit = OracleHelpers.TrafficAccessibilityRegionQuery(cullog, cullat, clrlog, clrlat) * 80
+ OracleHelpers.TrafficAccessibilityRegionQuery(cullog - 4.5 * dellog, cullat + 4.5 * dellog, clrlog + 4.5 * dellog, clrlat - 4.5 * dellog);
int ccompetitiveness = OracleHelpers.CompetitivenessRegionQuery(cullog, cullat, clrlog, clrlat) * 80
+ OracleHelpers.CompetitivenessRegionQuery(cullog - 4.5 * dellog, cullat + 4.5 * dellog, clrlog + 4.5 * dellog, clrlat - 4.5 * dellog);
report.trafficAccessibility += ctrafficAccessibilit + ",";
report.competitiveness += ccompetitiveness + ",";
}
}
return(report);
}
public BussinessDistrictReport AnalysisBussinessDistricReport(string sessionId, double log, double lat)
{
UserInformation user = SessionHelpers.Validate(sessionId);
if (user == null)
{
return(null);
}
BussinessDistrictReport report = new BussinessDistrictReport();
report.userId = user.userId;
report.longitude = log;
report.latitude = lat;
report.reportId = 1;
report.trafficAccessibility = OracleHelpers.TrafficAccessibilityPointQuery(log, lat, 1000) * 5 + OracleHelpers.TrafficAccessibilityPointQuery(log, lat, 2000);
report.competitiveness = OracleHelpers.CompetitivenessPointQuery(log, lat);
report.date = DateTime.Now;
return(report);
}
public void Add(LocalReportModel entity)
{
LogLine(string.Format("adding {0} - {1} to database.", entity.Id, entity.Description));
int rowsAffected = Connection.Execute(@"INSERT INTO XMS_LOCAL_REPORTS (TAXONOMY_ID,
SOURCE_ID,
ID,
ENTRY_URI,
FILE_NAME,
DESCRIPTION,
ENTITY_IDENTIFIER,
PERIOD_TYPE,
CURRENCY,
DECIMALS,
ENTITY_SCHEMA,
DECIMAL_DECIMALS,
INTEGER_DECIMALS,
MONETARY_DECIMALS,
PURE_DECIMALS,
SHARES_DECIMALS,
TN_PROCESSOR_ID,
TN_REVISION_ID,
UPDATE_USER,
UPDATE_USER_DSC,
UPDATE_DATE)
VALUES (:TaxonomyId,
:SourceId,
:Id,
:EntryUri,
:FileName,
:Description,
:EntityIdentifire,
:PeriodType,
:Currency,
:Decimals,
:EntitySchema,
:DecimalDecimals,
:IntegerDecimals,
:MonetaryDecimals,
:PureDecimals,
:SharesDecimals,
:TnProcessorId,
:TnRevisionId,
:UpdateDate,
:UpdateUser,
:UpdateUserDsc)",
new
{
Description = entity.Description,
PeriodType = OracleHelpers.PeriodTypeToString(entity.PeriodType),
TaxonomyId = entity.TaxonomyId,
Id = entity.Id,
SourceId = entity.SourceId,
EntryUri = entity.EntryUri,
FileName = entity.FileName,
EntityIdentifire = entity.EntityIdentifier,
Currency = entity.Currency,
Decimals = entity.Decimals,
EntitySchema = entity.EntitySchema,
DecimalDecimals = entity.DecimalDecimals,
IntegerDecimals = entity.IntegerDecimals,
MonetaryDecimals = entity.MonetaryDecimals,
PureDecimals = entity.PureDecimals,
SharesDecimals = entity.SharesDecimals,
TnProcessorId = entity.TnProcessorId,
TnRevisionId = entity.TnRevisionId,
UpdateDate = entity.UpdateDate,
UpdateUser = entity.UpdateUser,
UpdateUserDsc = entity.UpdateUserDsc,
});
if (rowsAffected == 0)
{
throw new NoRowsAffectedException("Add LocalReport failed rowsAffected :" + rowsAffected);
}
}
public BussinessDistrictReport GetBussinessDistricReportByReportID(string sessionId, int reportId)
{
UserInformation user = GetSession(sessionId).userInformation;
if (user == null)
{
return(null);
}
BussinessDistrictReport report = new BussinessDistrictReport();
string oracleSpatialAdminUsername = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_ADMIN_DB_USERNAME"];
string oracleSpatialAdminPassword = earthfusion_backend.Globals.config["EARTH_FUSION_SPATIAL_ADMIN_DB_PASSWORD"];
OracleConnection conn = OracleHelpers.GetOracleConnection(oracleSpatialAdminUsername, oracleSpatialAdminPassword, false);
string QueryString = ("select * from nemo.BussinessDistricReport where user_id=" + user.userId).ToString();
Logging.Info("GetBussinessDistricReportByReportID", "Constructed query: " + QueryString);
// constructs command from string
OracleCommand command = new OracleCommand(QueryString, conn);
// open db connection
conn.Open();
// then, executes the data reader
OracleDataReader reader = command.ExecuteReader();
if (reader.RowSize == 0)
{
return(null);
}
try
{
/*
*
* CREATE TABLE nemo.BussinessDistricReport
* (
* user_id int,
* bd_report_id int,
* bd_report_log float,
* bd_report_lat float,
* bd_report_time date,
* bd_competitiveness int,
* bd_traffic_accessibility int,
* PRIMARY KEY(bd_report_id)
*
* )
*/
while (reader.Read())
{
report.userId = reader.GetInt32(0);
report.reportId = reader.GetInt32(1);
report.longitude = reader.GetFloat(2);
report.latitude = reader.GetFloat(3);
report.date = reader.GetDateTime(4);
report.competitiveness = reader.GetInt32(5);
report.trafficAccessibility = reader.GetInt32(6);
}
}
finally
{
// always call Close when done reading.
reader.Close();
}
conn.Close();
return(report);
}
