public static List <LanePayment> Search(Lane lane)
{
if (null == lane)
{
return(new List <LanePayment>());
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT LanePayment.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " , Lane.LaneNo ";
cmd += " , User.FullNameEN, User.FullNameTH ";
cmd += " , Payment.PaymentNameEN, User.PaymentNameTH ";
cmd += " FROM LanePayment, TSB, PlazaGroup, Plaza, Lane, User, Payment ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND LanePayment.TSBId = TSB.TSBId ";
cmd += " AND LanePayment.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND LanePayment.PlazaId = Plaza.PlazaId ";
cmd += " AND LanePayment.LaneId = Lane.LaneId ";
cmd += " AND LanePayment.UserId = User.UserId ";
cmd += " AND LanePayment.PaymentId = Payment.PaymentId ";
cmd += " AND LanePayment.LaneId = ? ";
return(NQuery.Query <FKs>(cmd, lane.LaneId).ToList <LanePayment>());
}
}
public static List <LaneAttendance> Search(DateTime date)
{
if (null == date || date == DateTime.MinValue)
{
return(new List <LaneAttendance>());
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT LaneAttendance.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " , Lane.LaneNo ";
cmd += " , User.FullNameEN, User.FullNameTH ";
cmd += " FROM LaneAttendance, TSB, PlazaGroup, Plaza, Lane, User ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.TSBId = TSB.TSBId ";
cmd += " AND LaneAttendance.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND LaneAttendance.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.LaneId = Lane.LaneId ";
cmd += " AND LaneAttendance.UserId = User.UserId ";
cmd += " AND LaneAttendance.Begin >= ? ";
cmd += " AND LaneAttendance.End <= ? ";
return(NQuery.Query <FKs>(cmd, date,
DateTime.MinValue).ToList <LaneAttendance>());
}
}
public static List <LaneAttendance> GetAllNotHasRevenueEntry()
{
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT LaneAttendance.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " , Lane.LaneNo ";
cmd += " , User.FullNameEN, User.FullNameTH ";
cmd += " FROM LaneAttendance, TSB, PlazaGroup, Plaza, Lane, User ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.TSBId = TSB.TSBId ";
cmd += " AND LaneAttendance.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND LaneAttendance.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.LaneId = Lane.LaneId ";
cmd += " AND LaneAttendance.UserId = User.UserId ";
cmd += " AND TSB.Active = 1 ";
cmd += " AND (LaneAttendance.RevenueDate = ?";
cmd += " OR LaneAttendance.RevenueId IS NULL ";
cmd += " OR LaneAttendance.RevenueId = ?)";
return(NQuery.Query <FKs>(cmd, DateTime.MinValue,
string.Empty).ToList <LaneAttendance>());
}
}
/// <summary>
/// Gets TSB Coupon summaries.
/// </summary>
/// <param name="tsb">The target TSB to get balance.</param>
/// <returns>Returns List of TSB Coupon balance. If TSB not found returns null.</returns>
public static NDbResult <List <TSBCouponSummary> > GetTSBCouponSummaries(TSB tsb)
{
var result = new NDbResult <List <TSBCouponSummary> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
if (null == tsb)
{
result.ParameterIsNull();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = @"
SELECT * FROM TSBCouponSummarryView
WHERE TSBId = ? " ;
var rets = NQuery.Query <FKs>(cmd, tsb.TSBId).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static LaneAttendance GetCurrentByLane(Lane lane)
{
if (null == lane)
{
return(null);
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT LaneAttendance.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " , Lane.LaneNo ";
cmd += " , User.FullNameEN, User.FullNameTH ";
cmd += " FROM LaneAttendance, TSB, PlazaGroup, Plaza, Lane, User ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.TSBId = TSB.TSBId ";
cmd += " AND LaneAttendance.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND LaneAttendance.PlazaId = Plaza.PlazaId ";
cmd += " AND LaneAttendance.LaneId = Lane.LaneId ";
cmd += " AND LaneAttendance.UserId = User.UserId ";
cmd += " AND LaneAttendance.LaneId = ? ";
cmd += " AND LaneAttendance.End = ? ";
return(NQuery.Query <FKs>(cmd, lane.LaneId,
DateTime.MinValue).FirstOrDefault <LaneAttendance>());
}
}
/// <summary>
/// Gets UserShift by TSBId and UserId.
/// </summary>
/// <param name="tsbId">Ths TSB id.</param>
/// <param name="userId">The User Id.</param>
/// <returns>Returns UserShift instance.</returns>
public static NDbResult <UserShift> GetUserShift(string tsbId, string userId)
{
var result = new NDbResult <UserShift>();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserShiftView ";
cmd += " WHERE TSBId = ? ";
cmd += " AND UserId = ? ";
cmd += " AND End = ? ";
var ret = NQuery.Query <FKs>(cmd,
tsbId, userId, DateTime.MinValue).FirstOrDefault();
result.data = (null != ret) ? ret.ToModel() : null;
result.Success();
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets Un Close User Shifts.
/// </summary>
/// <param name="tsbid">The TSB Id.</param>
/// <returns>Returns List of User Shifts.</returns>
public static NDbResult <List <UserShift> > GetUnCloseUserShifts(string tsbid)
{
var result = new NDbResult <List <UserShift> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserShiftView ";
cmd += " WHERE TSBId = ? ";
cmd += " AND End = ? ";
var rets = NQuery.Query <FKs>(cmd, tsbid, DateTime.MinValue).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets Active TSB.
/// </summary>
/// <returns>Returns Active TSB instance.</returns>
public static NDbResult <TSB> GetCurrent()
{
var result = new NDbResult <TSB>();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
// inactive all TSBs
string cmd = string.Empty;
cmd += "SELECT * FROM TSB ";
cmd += " WHERE Active = 1 ";
var results = NQuery.Query <TSB>(cmd);
var data = (null != results) ? results.FirstOrDefault() : null;
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets PlazaGroups (By TSBId).
/// </summary>
/// <param name="tsbId">The TSB Id.</param>
/// <returns>Returns List of PlazaGroup.</returns>
public static NDbResult <List <PlazaGroup> > GetTSBPlazaGroups(string tsbId)
{
var result = new NDbResult <List <PlazaGroup> >();
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM PlazaGroupView ";
cmd += " WHERE TSBId = ? ";
var rets = NQuery.Query <FKs>(cmd, tsbId).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Find Users by Group Id.
/// </summary>
/// <param name="groupId">The Group Id.</param>
/// <param name="status">The status.</param>
/// <returns>Returns List of User.</returns>
public static NDbResult <List <User> > FindByGroupId(int groupId, int status)
{
var result = new NDbResult <List <User> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserView ";
cmd += " WHERE GroupId = ? ";
cmd += " AND Status = ? ";
var rets = NQuery.Query <FKs>(cmd, groupId, status).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets All TSB Coupon Balance.
/// </summary>
/// <returns>Returns List fo all TSB Coupon balance.</returns>
public static NDbResult <List <TSBCouponBalance> > GetTSBBalances()
{
var result = new NDbResult <List <TSBCouponBalance> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = @"
SELECT *
FROM TSBCouponBalanceView " ;
var rets = NQuery.Query <FKs>(cmd).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets Users.
/// </summary>
/// <param name="db">The database connection.</param>
/// <returns>Returns List of User.</returns>
public static NDbResult <List <User> > GetUsers(SQLiteConnection db)
{
var result = new NDbResult <List <User> >();
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserView ";
var rets = NQuery.Query <FKs>(cmd).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets by UserId and password.
/// </summary>
/// <param name="userId">The UserId.</param>
/// /// <param name="password">The password in MD5.</param>
/// <returns>Returns User instance.</returns>
public static NDbResult <User> GetByUserId(string userId, string password)
{
var result = new NDbResult <User>();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserView ";
cmd += " WHERE UserId = ? ";
cmd += " AND Password = ? ";
var ret = NQuery.Query <FKs>(cmd, userId, password).FirstOrDefault();
var data = (null != ret) ? ret.ToModel() : null;
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
/// <summary>
/// Gets Plaza Lane.
/// </summary>
/// <param name="plazaId">The plaza Id.</param>
/// <param name="laneNo">The lane number.</param>
/// <returns>Returns match lane.</returns>
public static NDbResult <Lane> GetPlazaLane(string plazaId, int laneNo)
{
var result = new NDbResult <Lane>();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM LaneView ";
cmd += " WHERE PlazaId = ? ";
cmd += " AND LaneNo = ? ";
var ret = NQuery.Query <FKs>(cmd, plazaId, laneNo).FirstOrDefault();
var data = (null != ret) ? ret.ToModel() : null;
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static TSBCouponTransaction GetInitial(TSB tsb)
{
if (null == tsb)
{
return(null);
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT TSBCouponTransaction.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " FROM TSBCouponTransaction, TSB ";
cmd += " WHERE TSBCouponTransaction.TSBId = TSB.TSBId ";
cmd += " AND TSBCouponTransaction.TSBId = ? ";
cmd += " AND TSBCouponTransaction.TransactionType = ? ";
var ret = NQuery.Query <FKs>(cmd,
tsb.TSBId, TransactionTypes.Initial).FirstOrDefault();
TSBCouponTransaction inst;
if (null == ret)
{
inst = Create();
tsb.AssignTo(inst);
inst.TransactionType = TransactionTypes.Initial;
}
else
{
inst = ret.ToTSBCouponTransaction();
}
return(inst);
}
}
/// <summary>
/// Gets TSB Coupon transactions.
/// </summary>
/// <param name="tsb">The target TSB to get coupon transaction.</param>
/// <returns>Returns TSB Coupon transactions. If TSB not found returns null.</returns>
public static List <TSBCouponTransaction> Gets(TSB tsb)
{
if (null == tsb)
{
return(null);
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT TSBCouponTransaction.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " FROM TSBCouponTransaction, TSB ";
cmd += " WHERE TSBCouponTransaction.TSBId = TSB.TSBId ";
cmd += " AND TSBCouponTransaction.TSBId = ? ";
var rets = NQuery.Query <FKs>(cmd, tsb.TSBId).ToList();
if (null == rets)
{
return(new List <TSBCouponTransaction>());
}
else
{
var results = new List <TSBCouponTransaction>();
rets.ForEach(ret =>
{
results.Add(ret.ToTSBCouponTransaction());
});
return(results);
}
}
}
/// <summary>
/// Gets Payments.
/// </summary>
/// <param name="db">The database connection.</param>
/// <returns>Returns List of Payment.</returns>
public static NDbResult <List <Payment> > GetPayments(SQLiteConnection db)
{
var result = new NDbResult <List <Payment> >();
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * FROM Payment ";
var data = NQuery.Query <Payment>(cmd);
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static List <User> FindByRole(string roleId, int status)
{
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT User.* ";
cmd += " , Role.RoleNameEN, Role.RoleNameTH ";
cmd += " FROM User, Role ";
cmd += " WHERE User.RoleId = Role.RoleId ";
cmd += " AND User.RoleId = ? ";
cmd += " AND User.Status = ? ";
var rets = NQuery.Query <FKs>(cmd, roleId, status).ToList();
var results = new List <User>();
if (null != rets)
{
rets.ForEach(ret =>
{
results.Add(ret.ToUser());
});
}
return(results);
}
}
/// <summary>
/// Gets TSB Addition Balance.
/// </summary>
/// <param name="tsb">The target TSB to get balance.</param>
/// <returns>Returns TSB Addition balance. If TSB not found returns null.</returns>
public static TSBAdditionBalance GetCurrent(TSB tsb)
{
if (null == tsb)
{
return(null);
}
lock (sync)
{
string cmd = @"
SELECT TSB.TSBId
, TSB.TSBNameEN
, TSB.TSBNameTH
, ((
SELECT IFNULL(SUM(AdditionalBHTTotal), 0)
FROM TSBAdditionTransaction
WHERE ( TSBAdditionTransaction.TransactionType = 0
OR TSBAdditionTransaction.TransactionType = 1
) -- Initial = 0, Borrow = 1
AND TSBAdditionTransaction.TSBId = TSB.TSBId
) -
(
SELECT IFNULL(SUM(AdditionalBHTTotal), 0)
FROM TSBAdditionTransaction
WHERE TSBAdditionTransaction.TransactionType = 2 -- Returns = 2
AND TSBAdditionTransaction.TSBId = TSB.TSBId
)) AS AdditionalBHTTotal
FROM TSB
WHERE TSB.TSBId = ?
" ;
var ret = NQuery.Query <FKs>(cmd, tsb.TSBId).FirstOrDefault();
return((null != ret) ? ret.ToTSBAdditionBalance() : null);
}
}
/// <summary>
/// Gets Plazas (By PlazaGroup Id).
/// </summary>
/// <param name="tsbId">The TSB Id.</param>
/// <param name="plazaGroupId">The PlazaGroup Id.</param>
/// <returns>Returns List of Plaza.</returns>
public static NDbResult <List <Plaza> > GetPlazaGroupPlazas(
string tsbId,
string plazaGroupId)
{
var result = new NDbResult <List <Plaza> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM PlazaView ";
cmd += " WHERE TSBId = ? ";
cmd += " AND PlazaGroupId = ? ";
var rets = NQuery.Query <FKs>(cmd, tsbId, plazaGroupId).ToList();
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static List <User> Gets(SQLiteConnection db)
{
List <User> insts = new List <User>();
if (null == db)
{
return(insts);
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT User.* ";
cmd += " , Role.RoleNameEN, Role.RoleNameTH ";
cmd += " FROM User, Role ";
cmd += " WHERE User.RoleId = Role.RoleId ";
var rets = NQuery.Query <FKs>(cmd).ToList();
var results = new List <User>();
if (null != rets)
{
rets.ForEach(ret =>
{
results.Add(ret.ToUser());
});
}
return(results);
}
}
public static List <UserShift> GetUnCloseUserShifts()
{
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT UserShift.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , Shift.ShiftNameEN, Shift.ShiftNameTH ";
cmd += " , User.FullNameEN, User.FullNameTH ";
cmd += " FROM UserShift, Shift, User, TSB ";
cmd += " WHERE UserShift.ShiftId = Shift.ShiftId ";
cmd += " AND TSB.Active = 1 ";
cmd += " AND UserShift.UserId = User.UserId ";
cmd += " AND UserShift.TSBId = TSB.TSBId ";
cmd += " AND UserShift.End = ? ";
var rets = NQuery.Query <FKs>(cmd, DateTime.MinValue).ToList();
var results = new List <UserShift>();
if (null != rets)
{
rets.ForEach(ret =>
{
results.Add(ret.ToUserShift());
});
}
return(results);
}
}
/// <summary>
/// Gets Plaza by SCW Id.
/// </summary>
/// <param name="db">The database connection.</param>
/// <param name="scwPlazaId">The SCW Plaza Id</param>
/// <returns>Returns Plaza instance.</returns>
public static NDbResult <Plaza> GetPlazaBySCWPlazaId(SQLiteConnection db, int scwPlazaId)
{
var result = new NDbResult <Plaza>();
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM PlazaView ";
cmd += " WHERE SCWPlazaId = ? ";
var ret = NQuery.Query <FKs>(cmd, scwPlazaId).FirstOrDefault();
var data = (null != ret) ? ret.ToModel() : null;
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static List <Lane> GetPlazaLanes(string tsbId, string plazaGroupId, string plazaId)
{
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT Lane.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " FROM Lane, Plaza, PlazaGroup, TSB ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = Plaza.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND Lane.TSBId = ? ";
cmd += " AND Lane.PlazaGroupId = ? ";
cmd += " AND Lane.PlazaId = ? ";
var rets = NQuery.Query <FKs>(cmd, tsbId, plazaGroupId, plazaId).ToList();
var results = new List <Lane>();
if (null != rets)
{
rets.ForEach(ret =>
{
results.Add(ret.ToLane());
});
}
return(results);
}
}
/// <summary>
/// Gets Shift.
/// </summary>
/// <param name="db">The database connection.</param>
/// <param name="shiftId">The Shift Id.</param>
/// <returns>Returns Shift instance.</returns>
public static NDbResult <Shift> GetShift(SQLiteConnection db, string shiftId)
{
var result = new NDbResult <Shift>();
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * FROM Shift ";
cmd += " WHERE ShiftId = ? ";
var data = NQuery.Query <Shift>(cmd, shiftId).FirstOrDefault();
result.Success(data);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static Lane Get(SQLiteConnection db, string laneId)
{
if (null == db)
{
return(null);
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT Lane.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, PlazaGroup.Direction ";
cmd += " , Plaza.PlazaNameEN, Plaza.PlazaNameTH ";
cmd += " FROM Lane, Plaza, PlazaGroup, TSB ";
cmd += " WHERE PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
cmd += " AND Lane.TSBId = TSB.TSBId ";
cmd += " AND Lane.PlazaGroupId = Plaza.PlazaGroupId ";
cmd += " AND Lane.PlazaId = Plaza.PlazaId ";
cmd += " AND Lane.LaneId = ? ";
var ret = NQuery.Query <FKs>(cmd, laneId).FirstOrDefault();
return((null != ret) ? ret.ToLane() : null);
}
}
public static List <Plaza> Gets(SQLiteConnection db)
{
if (null == db)
{
return(new List <Plaza>());
}
lock (sync)
{
string cmd = string.Empty;
cmd += "SELECT Plaza.* ";
cmd += " , TSB.TSBNameEN, TSB.TSBNameTH ";
cmd += " , PlazaGroup.PlazaGroupNameEN, PlazaGroup.PlazaGroupNameTH, Plaza.Direction ";
cmd += " FROM Plaza, PlazaGroup, TSB ";
cmd += " WHERE Plaza.TSBId = TSB.TSBId ";
cmd += " AND PlazaGroup.TSBId = TSB.TSBId ";
cmd += " AND Plaza.TSBId = TSB.TSBId ";
cmd += " AND Plaza.PlazaGroupId = PlazaGroup.PlazaGroupId ";
var rets = NQuery.Query <FKs>(cmd).ToList();
var results = new List <Plaza>();
if (null != rets)
{
rets.ForEach(ret =>
{
results.Add(ret.ToPlaza());
});
}
return(results);
}
}
/// <summary>
/// Gets Role.
/// </summary>
/// <param name="db">The database connection.</param>
/// <param name="roleId">The Role Id.</param>
/// <returns>Returns Role instance.</returns>
public static NDbResult <Role> GetRole(SQLiteConnection db, string roleId)
{
var result = new NDbResult <Role>();
if (null == db)
{
result.DbConenctFailed();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * FROM Role ";
cmd += " WHERE RoleId = ? ";
var results = NQuery.Query <Role>(cmd, roleId).FirstOrDefault();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
public static TSB GetCurrent()
{
lock (sync)
{
// inactive all TSBs
string cmd = string.Empty;
cmd += "SELECT * FROM TSB ";
cmd += " WHERE Active = 1 ";
var results = NQuery.Query <TSB>(cmd);
return((null != results) ? results.FirstOrDefault() : null);
}
}
/// <summary>
/// Gets List of BHT35 by User in target TSB.
/// </summary>
/// <param name="tsb">The target TSB instance.</param>
/// <param name="user">The user instance.</param>
/// <returns>Returns List of BHT35.</returns>
public static NDbResult <List <UserCouponTransaction> > GetUserBHT35Coupons(
TSB tsb, User user)
{
var result = new NDbResult <List <UserCouponTransaction> >();
SQLiteConnection db = Default;
if (null == db)
{
result.DbConenctFailed();
return(result);
}
if (null == tsb)
{
result.ParameterIsNull();
return(result);
}
lock (sync)
{
MethodBase med = MethodBase.GetCurrentMethod();
try
{
string cmd = string.Empty;
cmd += "SELECT * ";
cmd += " FROM UserCouponTransactionView ";
cmd += " WHERE TSBId = ? ";
cmd += " AND CouponType = ? ";
if (null != user)
{
cmd += " AND UserId = ? ";
}
List <FKs> rets;
if (null != user)
{
rets = NQuery.Query <FKs>(cmd,
tsb.TSBId, CouponType.BHT35, user.UserId).ToList();
}
else
{
rets = NQuery.Query <FKs>(cmd, tsb.TSBId, CouponType.BHT35).ToList();
}
var results = rets.ToModels();
result.Success(results);
}
catch (Exception ex)
{
med.Err(ex);
result.Error(ex);
}
return(result);
}
}
