/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
MemberHistory ret = (MemberHistory)OracleDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Insert_MemberHistory",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemID", memberHistory.MemberID, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemName", memberHistory.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemIsSuccess", ((memberHistory.IsSuccess) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemMessage", memberHistory.Message, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter <OracleDbType>(providerName, ":OutputData", null, OracleDbType.RefCursor, ParameterDirection.Output)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
return((MemberHistory)MsSqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"INSERT INTO Product.dbo.MemberHistory " +
@"( MemberID, MemberName, Successful, Message, InsertedDate ) " +
@"VALUES " +
@"( @MemberID, @MemberName, @Successful, @Message, GETDATE() ) " +
@" " +
@"SELECT Sequence, MemberID, MemberName, Successful, Message, InsertedDate " +
@"FROM Product.dbo.MemberHistory " +
@"WHERE MemberID = @MemberID AND MemberName = @MemberName " +
@" AND Successful = @Successful AND Message = @Message " +
@"ORDER BY InsertedDate DESC ",
MsSqlParameterHelperFactory.CreateParameter <SqlDbType>(providerName, "@MemberID", memberHistory.MemberID, SqlDbType.Int, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", memberHistory.MemberName, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@Successful", memberHistory.IsSuccess, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@Message", memberHistory.Message, ParameterDirection.Input)
));
}
}
catch (Exception ex)
{
throw ex;
}
}
public MemberHistory SelectMemberHistory(int seq)
{
try
{
using (connection)
{
connection.Open();
MemberHistory ret = (MemberHistory)OracleDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Seq, MemberID, MemberName, IsSuccess, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE Seq = :Seq ",
OracleParameterHelperFactory.CreateParameter(providerName, ":Seq", seq, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public bool InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int ret = (int)OracleDataHelperFactory.Execute(connection,
CommandType.Text,
@"INSERT INTO Product.MemberHistory " +
@"( Seq, MemberID, MemberName, IsSuccess, Message, InsertedDate ) " +
@"VALUES " +
@"( Product.SEQ_MemberHistory.NEXTVAL, :MemberID, :MemberName, :IsSuccess, :Message, SYSDATE ) ",
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberID", memberHistory.MemberID, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":MemberName", memberHistory.MemberName, ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":IsSuccess", ((memberHistory.IsSuccess) ? 1 : 0), ParameterDirection.Input),
OracleParameterHelperFactory.CreateParameter(providerName, ":Message", memberHistory.Message, ParameterDirection.Input)
);
return((ret == 1) ? true : false);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int insertedSequence = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.StoredProcedure,
"Product.sp_Memberships_Insert_MemberHistory",
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemID", memberHistory.MemberID, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemName", memberHistory.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemSuccessful", memberHistory.IsSuccess, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemMessage", memberHistory.Message, ParameterDirection.Input)
)
);
MemberHistory ret = (MemberHistory)MySqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.sp_Memberships_Select_MemberHistory_By_Sequence",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@Seq", insertedSequence, MySqlDbType.Int32, ParameterDirection.Input));
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
int insertedSequence = Convert.ToInt32(
MySqlDataHelperFactory.SelectScalar(connection,
CommandType.Text,
@"INSERT INTO Product.MemberHistory " +
@"( MemberID, MemberName, Successful, Message, InsertedDate ) " +
@"VALUES " +
@"( @MemberID, @MemberName, @Successful, @Message, SYSDATE() ); " +
@" " +
@"SELECT LAST_INSERT_ID(); ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@MemberID", memberHistory.MemberID, MySqlDbType.Int32, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", memberHistory.MemberName, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Successful", memberHistory.IsSuccess, ParameterDirection.Input),
MySqlParameterHelperFactory.CreateParameter(providerName, "@Message", memberHistory.Message, ParameterDirection.Input)
)
);
MemberHistory ret = (MemberHistory)MySqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.Text,
@"SELECT Sequence, MemberID, MemberName, Successful, Message, InsertedDate " +
@"FROM Product.MemberHistory " +
@"WHERE Sequence = @Sequence; ",
MySqlParameterHelperFactory.CreateParameter <MySqlDbType>(providerName, "@Sequence", insertedSequence, MySqlDbType.Int32, ParameterDirection.Input)
);
return(ret);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Route> GetNavi(int memberId, int sourceCoordinateId, int destinationCoordinteId)
{
var co1 = db.Coordinates
.Where(r => r.Id == sourceCoordinateId)
.SingleOrDefault();
var co2 = db.Coordinates
.Where(r => r.Id == destinationCoordinteId)
.SingleOrDefault();
var source = db.Routes
.Include(r => r.RouteCoordinates)
.Where(r => r.RouteCoordinates.Any(rc => rc.CoordinateId == sourceCoordinateId))
.ToList();
var result = source.Where(r => r.RouteCoordinates.Any(rc => rc.CoordinateId == destinationCoordinteId))
.Select(r => new Route
{
Id = r.Id,
FlagColor = r.FlagColor,
ColorCode = r.ColorCode,
PriceDesc = r.PriceDesc,
DurationMinute = GetDuration(r.Id, co1, co2)
})
.OrderBy(r => r.DurationMinute)
.ToList();
if (result.Count() > 0)
{
var mh = new MemberHistory();
mh.MemberId = memberId;
mh.Datetime = DateTime.Now;
mh.SourceCoordinateId = sourceCoordinateId;
mh.DestinationCoordinteId = destinationCoordinteId;
db.Add(mh);
db.SaveChanges();
}
return(result);
}
/// <summary>
/// InsertMemberHistory method
/// - Insert MemberHistory table row from member history information
/// </summary>
/// <param name="member">Member history information</param>
/// <returns></returns>
public MemberHistory InsertMemberHistory(MemberHistory memberHistory)
{
try
{
using (connection)
{
connection.Open();
return((MemberHistory)MsSqlDataHelperFactory.SelectSingleEntity <MemberHistory>(connection,
typeof(MemberHistory),
CommandType.StoredProcedure,
"Product.dbo.sp_Memberships_Insert_MemberHistory",
MsSqlParameterHelperFactory.CreateParameter(providerName, "@MemberID", memberHistory.MemberID, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@MemberName", memberHistory.MemberName, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@Successful", memberHistory.IsSuccess, ParameterDirection.Input),
MsSqlParameterHelperFactory.CreateParameter(providerName, "@Message", memberHistory.Message, ParameterDirection.Input)
));
}
}
catch (Exception ex)
{
throw ex;
}
}
