public List <VcrTest> QueryList(string vcrid)
{
_sb = new StringBuilder();
_sb.Append(@"SELECT Id
, VcrId
, Qustion
, Answer
, AnswerLetter
, Analyze
, Maker
, MakeDay
, UpdateDay
, IsEnabled
, IsCorrect
FROM VcrTests ");
_sb.AppendFormat("where VcrId='{0}' ", vcrid);
dbFunc.ConnectionString = connstr;
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <VcrTest> .FillModel(dt));
}
return(null);
}
public List <FinCardDto> QueryFinCardDtos(string whr, string orderby, int pg, out int ttl, int pgsz = 10)
{
_sb = new StringBuilder();
StringBuilder sb = new StringBuilder();
_sb.Append(@"SELECT ROW_NUMBER() over (Order by ActivatedDay desc) od, Id
,(select unitprice from FINCardConfigs where Id=CardConfigId) UnitPrice
,(select username from AspNetUsers where Id=UserId) as UserId
,ActivatedDay
,Status,Password
,StatusDay
,CardConfigId
,EndDay
FROM FINCards");
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where " + whr);
}
ttl = base.GetRecordCount(_sb.ToString());
sb.Append("with tmp as(" + _sb);
sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
_dbfunc.ConnectionString = connstr;
var dt = _dbfunc.ExecuteDataTable(sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <FinCardDto> .FillModel(dt));
}
return(null);
}
public IEnumerable <TrainBaseLesson> Query(string whr, string orderby, int?top)
{
_sb = new StringBuilder();
_sb.Append("select ");
if (top.HasValue)
{
_sb.Append("top " + top.ToString());
}
_sb.Append(" * from Trainbaselessons");
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where " + whr);
}
if (!string.IsNullOrEmpty(orderby))
{
_sb.Append(" order by " + orderby);
}
_dbfunc.ConnectionString = connstr;
DataTable dt = _dbfunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <TrainBaseLesson> .FillModel(dt));
}
return(null);
}
/// <summary>
/// get general user list.
/// </summary>
/// <param name="whr"></param>
/// <param name="orderby"></param>
/// <param name="pg"></param>
/// <param name="ttl"></param>
/// <param name="pgsz"></param>
/// <returns></returns>
public List <Aspnetuser> QueryByRole(string whr, string orderby, int pg, out int ttl, int pgsz = 10)
{
_sb = new StringBuilder();
StringBuilder sb = new StringBuilder();
sb.Append(@"select row_number() over (order by Avatar) od,
u.Id,
Avatar,
UserName,
PhoneNumber,
Email
from AspNetUserRoles r
join AspNetUsers u
on r.UserId=u.Id");
sb.Append(" where " + whr);
ttl = GetRecordCount(sb.ToString());
_sb.Append("with tmp as(" + sb);
_sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
_dbFunc.ConnectionString = connstr;
System.Data.DataTable dt = _dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Aspnetuser> .FillModel(dt));
}
return(null);
}
public IEnumerable <Entity.UserLesson.UserLesson> GetList(int pgsz, string whr, out int ttl, string orderby, bool isAsc = false, int pg = 1)
{
_sb = new StringBuilder();
if (string.IsNullOrEmpty(orderby))
{
orderby = " EventDateTime";
}
_sb.AppendFormat(@"SELECT ROW_NUMBER() over (order by {0} {1}) od,UserId, TrainBaseLessonId,EventDateTime, VcrId, TimeSpanViewed, Memo FROM UserLessons", orderby, isAsc?"asc":"desc");
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where " + whr);
}
ttl = GetRecordCount(_sb.ToString());
StringBuilder stringBuilder2 = new StringBuilder();
stringBuilder2.Append("with tmp as(");
stringBuilder2.Append(_sb);
stringBuilder2.Append($")select * from tmp where od>{(pg - 1) * pgsz} and od<={ pg * pgsz }");
_dbFun.ConnectionString = connstr;
var dt = _dbFun.ExecuteDataTable(stringBuilder2.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Entity.UserLesson.UserLesson> .FillModel(dt));
}
return(null);
}
public IEnumerable <Aspnetuser> NoRoleUser(out int ttl, int pg)
{
_sb = new StringBuilder();
_sb.Append(@"select row_number() over (order by Avatar) od,
u.Id,
Avatar,
UserName,
PhoneNumber,
Email
from AspNetUsers u
left join AspNetUserRoles ur
on
u.Id=ur.userid
where ur.RoleId is null");
ttl = GetRecordCount(_sb.ToString());
StringBuilder sb = new StringBuilder();
sb.Append("with tmp as(" + _sb);
sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
_dbFunc.ConnectionString = connstr;
System.Data.DataTable dt = _dbFunc.ExecuteDataTable(sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Aspnetuser> .FillModel(dt));
}
return(null);
}
public List <UserMessage> Query(string whr, string orderby, int pg, out int ttl, int pgsz = 10)
{
_sb = new StringBuilder();
_sb.Append(@"SELECT ROW_NUMBER() over(order by MakeDay desc) od,Id
, Memo
, NickName
, Connects
, MakeDay
FROM UserMsgs ");
if (!string.IsNullOrEmpty(whr))
{
_sb.Append("where " + whr);
}
StringBuilder sb = new StringBuilder();
ttl = base.GetRecordCount(_sb.ToString());
sb.AppendFormat(@" with tmp as (" + _sb + ") select * from tmp where od > {0} and od < {1}", (pg - 1) * pgsz, pg * pgsz);
_dbfunc.ConnectionString = connstr;
var dt = this._dbfunc.ExecuteDataTable(sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <UserMessage> .FillModel(dt));
}
return(null);
}
public List <FINCardConfig> QueryWithCardCount(string whr, string orderby, int pg, out int ttl, int pgsz)
{
_sb = new StringBuilder();
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT ROW_NUMBER() over (Order by c.MakeDay desc) od, c.Id
,CardPrefix
,u.UserName as Maker
,c.Memo
,c.UnitPrice
,ValidPeriod
,BatchCardStatus
,MakeDay
,Start
,c.StatusDay
,COUNT(d.Id) Count
FROM FINCardConfigs c left
join FINCards d
on c.Id=d.CardConfigId
join aspnetusers u
on u.Id=c.maker
");
if (!string.IsNullOrEmpty(whr))
{
sb.AppendFormat(" where {0}", whr);
}
sb.Append(@" group by
c.Id
, CardPrefix
, u.username
, c.Memo
, c.UnitPrice
, ValidPeriod
, BatchCardStatus
, MakeDay
, Start
,c.StatusDay");
ttl = base.GetRecordCount(sb.ToString());
_sb.Append("with tmp as(" + sb);
_sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
_dbFunc.ConnectionString = connstr;
var dt = _dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <FINCardConfig> .FillModel(dt));
}
return(null);
}
public IEnumerable <TestItem> QueryAnswer(string vcrid)
{
_sb = new StringBuilder();
_sb.Append(@"SELECT Id , Answer as AnswerLetter FROM VcrTests ");
_sb.AppendFormat("where VcrId='{0}' ", vcrid);
dbFunc.ConnectionString = connstr;
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <TestItem> .FillModel(dt));
}
return(null);
}
public List <Vcr> Query(string whr, string orderby, int pg, out int ttl, int pgsz = 10)
{
_sb = new StringBuilder();
if (string.IsNullOrEmpty(orderby))
{
orderby = "coalesce(OrderCode ,10000) asc,MakeDay ";
}
_sb.AppendFormat(@" SELECT ROW_NUMBER() over (order by {0} ) od,Id
, LessonId
, VideoPath
, OrderCode
, MakeDay
, UpdateTime
, IsFree
, ViewTimes
, UpdatedBy
, FileOk
, HasTest
, TrainerId
, TitleOrName
, Maker
, Memo
, IsEnabled
FROM Vcrs ", orderby);
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where " + whr);
}
ttl = base.GetRecordCount(_sb.ToString());
StringBuilder stringBuilder2 = new StringBuilder();
stringBuilder2.Append("with tmp as(");
stringBuilder2.Append(_sb);
stringBuilder2.Append($")select * from tmp where od>{(pg - 1) * pgsz} and od<={ pg * pgsz }");
_dbFun.ConnectionString = connstr;
var dt = _dbFun.ExecuteDataTable(stringBuilder2.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Vcr> .FillModel(dt));
}
return(null);
}
public List <FINCard> Query(string whr, string orderby, int pg, out int ttl, bool isAsc, int pgsz = 10)
{
orderby = string.IsNullOrWhiteSpace(orderby) ? "endday" : orderby;
_sb = new StringBuilder();
StringBuilder sb = new StringBuilder();
_sb.Append(@"SELECT ROW_NUMBER() over (Order by ActivatedDay desc) od, Id
,Memo
,(select username from AspNetUsers where Id=UserId) as UserId
,ActivatedDay
,Status,Password
,StatusDay
,CardConfigId
,EndDay
FROM FINCards");
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where " + whr);
}
ttl = base.GetRecordCount(_sb.ToString());
sb.Append("with tmp as(" + _sb);
sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
if (!string.IsNullOrWhiteSpace(orderby))
{
sb.Append(" order by " + orderby);
}
if (!isAsc)
{
sb.Append(" desc");
}
_dbfunc.ConnectionString = connstr;
var dt = _dbfunc.ExecuteDataTable(sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <FINCard> .FillModel(dt));
}
return(null);
}
public IEnumerable <VcrFile> QueryList(string vid)
{
_sb = new StringBuilder();
_sb.AppendFormat(@"SELECT Id
, VcrId
, Name
, Path
, MakeDay
, Maker
, FileOk,FileSize
FROM VcrFiles where VcrId='{0}' ", vid);
dbFunc.ConnectionString = connstr;
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <VcrFile> .FillModel(dt));
}
return(null);
}
public IEnumerable <TrainBaseLesson> QueryByBindingId(string bindId, int pg, out int ttl, int pgsz)
{
_sb = new StringBuilder();
_sb.Append(@"SELECT ROW_NUMBER() over (Order by makeday desc) od, Id
, TitleOrName
, ImagePath
, Price
, DiscountPrice
, MakeDay
, Base_DataBindId
, ClickTimes
, VideoCount
, Maker
, Memo
, IsEnabled
, OrderCode
, IsBasic
FROM TrainBaseLessons");
_sb.Append($" where Base_DataBindId='{bindId}'");
_dbfunc.ConnectionString = connstr;
ttl = base.GetRecordCount(_sb.ToString());
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"with tmp as(");
stringBuilder.Append(_sb);
stringBuilder.Append($") select * from tmp where od>{(pg-1)* pgsz} and od <{pg*pgsz} ");
var dt = _dbfunc.ExecuteDataTable(stringBuilder.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <TrainBaseLesson> .FillModel(dt));
}
return(null);
throw new NotImplementedException();
}
public List <FINCardConfig> Query(string whr, string orderby, int pg, out int ttl, int pgsz = 10)
{
_sb = new StringBuilder();
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT ROW_NUMBER() over (Order by MakeDay desc) od, Id
,Count
,CardPrefix
,Maker
,Memo
,MakeDay
,UnitPrice
,ValidPeriod
,Start
,BatchCardStatus
FROM FINCardConfigs");
if (!string.IsNullOrEmpty(whr))
{
sb.Append(" where " + whr);
}
ttl = base.GetRecordCount(sb.ToString());
_sb.Append("with tmp as(" + sb);
_sb.AppendFormat(") select * from tmp where od > {0} and od <= {1}", (pg - 1) * 10, pg * 10);
_dbFunc.ConnectionString = connstr;
var dt = _dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <FINCardConfig> .FillModel(dt));
}
return(null);
}
public IEnumerable <Base_Period> Query(string whr)
{
_sb = new StringBuilder();
_sb.Append(forepart);
if (!string.IsNullOrEmpty(whr))
{
_sb.Append(" where" + whr);
}
_sb.Append(" order by coalesce(OrderCode ,10000),OrderCode asc");
dbFunc = new DbFunc();
dbFunc.ConnectionString = connstr;
var dt = dbFunc.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Base_Period> .FillModel(dt));
}
else
{
return(null);
}
}
public List <Vcr> Query(string lessonid)
{
_sb = new StringBuilder();
_sb.Append(@"select Id, LessonId
, VideoPath
, OrderCode
, MakeDay
, UpdateTime
, IsFree
, ViewTimes
, UpdatedBy
, FileOk
, HasTest
, TrainerId
, TitleOrName
, Maker
, Memo
, IsEnabled
FROM Vcrs ");
if (!string.IsNullOrEmpty(lessonid))
{
_sb.AppendFormat(" where LessonId='{0}'", lessonid);
}
_sb.Append(" order by coalesce(OrderCode ,10000) asc ,MakeDay asc");
_dbFun.ConnectionString = connstr;
var dt = _dbFun.ExecuteDataTable(_sb.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(TableToModel <Vcr> .FillModel(dt));
}
return(null);
}
public List <City> GetDownCountriesMdl(string parentId)
{
return(TableToModel <City> .FillModel(GetDownCountries(parentId)));
}
/// <summary>
/// get all base type of schools
/// </summary>
/// <returns></returns>
public IEnumerable <CitySchoolType> GetSchoolType()
{
return(TableToModel <CitySchoolType> .FillModel(_citySchoolDAL.GetSchoolType()));
}
/// <summary>
/// cities in the province.
/// </summary>
/// <param name="provinceid"></param>
/// <returns></returns>
public List <City> GetCitiesMdl(string provinceid)
{
return(TableToModel <City> .FillModel(GetCity(provinceid)));
}
public List <TrainBaseLesson> Query(string whr, int pg, out int ttl, int pgsz)
{
var mdl = _Dal.Query(whr, pg, out ttl, pgsz);
return(TableToModel <TrainBaseLesson> .FillModel(mdl));
}
/// <summary>
/// provinces
/// </summary>
/// <returns></returns>
public List <City> GetModelList()
{
return(TableToModel <City> .FillModel(GetProvince()));
}