public List <TestPaperReportInfo> ReadTheFirstRecordList(string companyID)
{
List <TestPaperReportInfo> tempList = new List <TestPaperReportInfo>();
if (!string.IsNullOrEmpty(companyID))
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from (");
sql.Append("select [UserID],[CateId],[PaperName],[Scorse],[TestDate],[IsPass],row_number() over(partition by [UserID] order by [TestDate]) as rownumber from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where [companyID] in (" + companyID + ")");
sql.Append(") as t where t.rownumber <=1");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
while (dr.Read())
{
TestPaperReportInfo latestTestPaper = new TestPaperReportInfo();
latestTestPaper.UserID = int.Parse(dr["UserId"].ToString());
latestTestPaper.CourseID = int.Parse(dr["CateId"].ToString());
latestTestPaper.CourseName = dr["PaperName"].ToString();
latestTestPaper.Score = decimal.Parse(dr["Scorse"].ToString());
latestTestPaper.TestDate = Convert.ToDateTime(dr["TestDate"]);
latestTestPaper.IsPass = int.Parse(dr["IsPass"].ToString());
tempList.Add(latestTestPaper);
}
}
}
return(tempList);
}
public List <TestPaperInfo> NewReadList(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] ");
List <TestPaperInfo> TempList = new List <TestPaperInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
if (Model.OrderByCondition == string.Empty)
{
sql.Append(" Order by TestPaperId desc");
}
else
{
sql.Append(" Order by " + Model.OrderByCondition);
}
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(dr, TempList);
}
return(TempList);
}
public List <PostPassInfo> ReadPostPassList(PostPassInfo Model, int num)
{
string sqlNumString = "*";
if (num > 0)
{
sqlNumString = "top " + num + " " + sqlNumString;
}
StringBuilder sql = new StringBuilder();
sql.Append("select " + sqlNumString + " from [" + ShopMssqlHelper.TablePrefix + "PassPost] ");
List <PostPassInfo> TempList = new List <PostPassInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order By CreateDate desc");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(dr, TempList);
}
}
return(TempList);
}
public List <ReportPostPassInfo> PostPassRepostList(PostPassInfo Model, string CompanyId)
{
if (Model.SearchStartDate == DateTime.MinValue)
{
Model.SearchStartDate = Convert.ToDateTime("2013-1-1");
}
List <ReportPostPassInfo> TempList = new List <ReportPostPassInfo>();
StringBuilder sql = new StringBuilder();
sql.Append("select [_PassPost].UserId as UserId,[_PassPost].[PostName] as [PassPostName],WorkingPostName,[_User].WorkingPostID as WorkPostId,[_PassPost].PostId as PostId,[_User].RealName as RealName,(select count(distinct PostId) from [" + ShopMssqlHelper.TablePrefix + "PassPost] Where UserId=[_User].ID and IsRZ=1) as PostNum,[_User].StudyPostId as StudyPostId from [" + ShopMssqlHelper.TablePrefix + "PassPost],[" + ShopMssqlHelper.TablePrefix + "User] ");
sql.Append("where [_PassPost].UserId=[_User].ID and [_User].CompanyID in (" + CompanyId + ") and ([_PassPost].CreateDate between '" + Model.SearchStartDate.ToString() + "' and '" + Model.CreateDate.ToString() + "') and [IsRZ]=1");
sql.Append(" Order By [_PassPost].id desc");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
while (dr.Read())
{
ReportPostPassInfo ReportModel = new ReportPostPassInfo();
{
ReportModel.UserId = int.Parse(dr["UserId"].ToString());
ReportModel.PassPostId = dr["PostId"].ToString();
ReportModel.PassPostName = dr["PassPostName"].ToString();
ReportModel.PassPostNum = int.Parse(dr["PostNum"].ToString());
ReportModel.RealName = dr["RealName"].ToString();
ReportModel.WorkingPostName = dr["WorkingPostName"].ToString();
ReportModel.WorkingPostId = int.Parse(dr["WorkPostId"].ToString());
ReportModel.StudyPostId = int.Parse(dr["StudyPostId"].ToString());
}
TempList.Add(ReportModel);
}
}
return(TempList);
}
public CompanyInfo ReadCompany(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] where [CompanyId]=" + id);
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(GetModel(reader));
}
}
//读取信息
public TestSettingInfo ReadTestSetting(int companyId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestSetting] where CompanyId=" + companyId);
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(GetModel(reader));
}
}
//读取信息
public PostPassInfo ReadPostPassInfo(int userID, int postID)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "PassPost] where [UserID]=" + userID + " And [PostID]=" + postID);
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(this.GetModel(reader));
}
}
//读取指定信息
public CourseInfo ReadCourse(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Course] where CourseId=" + Id);
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(GetModel(reader));
}
}
//读取信息
public TestPaperInfo ReadTheOldTestPaperInfo(int UserId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where UserId=" + UserId + " Order by TestDate");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(GetModel(dr));
}
}
public PostPassInfo ReadPostPassInfo()
{
StringBuilder sql = new StringBuilder();
sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "PassPost] where [IsRZ]=1 Order by id desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(this.GetModel(reader));
}
}
/// <summary>
/// 读取所有实体的列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
//public List<T> ReadList<T>() where T : BaseModel
public List <T> ReadList()
{
//Type type = typeof(T);
string sqlText = string.Format("select {0} from [{1}{2}]", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName);
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText))
{
return(this.PrepareList(reader));
}
}
//读取信息
public TestPaperInfo ReadPaper(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where TestPaperId=@TestPaperId");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString(), par))
{
return(GetModel(dr));
}
}
public List <RenZhengCateInfo> ReadRenZhengCateList()
{
List <RenZhengCateInfo> RenZhengCateList = new List <RenZhengCateInfo>();
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "RenZhengCate] Where Del=0 Order by [Order]");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, RenZhengCateList);
}
return(RenZhengCateList);
}
public List <CourseInfo> ReadList()
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Course]");
List <CourseInfo> TempList = new List <CourseInfo>();
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
return(TempList);
}
public List <QuestionInfo> ReadList(int CateId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Question] Where [CateId]=" + CateId.ToString());
List <QuestionInfo> TempList = new List <QuestionInfo>();
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
return(TempList);
}
public List <PostInfo> ReadPostCateAllList()
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Post] ");
sql.Append(" Order by [CompanyID],[ParentId],[OrderIndex],[PostName],[PostId] desc");
List <PostInfo> List = new List <PostInfo>();
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, List);
}
return(List);
}
public SMSRecordInfo ReadSMSRecord(string Mobile, string Code)
{
StringBuilder sql = new StringBuilder();
sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "SMSRecord] where [Mobile]='" + Mobile + "'");
if (!string.IsNullOrEmpty(Code))
{
sql.Append(" and [VerCode]='" + Code + "'");
}
sql.Append(" order by [Id] desc");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
return(GetModel(dr));
}
}
public List <string> ReadProductClassListByProductID(string productID, string inBrandID)
{
List <string> classList = new List <string>();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT [ClassID] FROM [" + ShopMssqlHelper.TablePrefix + "Product] where [ID] in (" + productID + ") And [IsSale]=1 And [dbo].[" + ShopMssqlHelper.TablePrefix + "CompareBrand]([BrandID],'" + inBrandID + "',',') = 1 Group by [ClassID] Order by [ClassID] Desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
while (reader.Read())
{
classList.Add(reader["ClassID"].ToString());
}
}
return(classList);
}
public List <CompanyInfo> ReadCompanyListByParentID(string parentID)
{
List <CompanyInfo> CompanyList = new List <CompanyInfo>();
if (!string.IsNullOrEmpty(parentID) && parentID != ",")
{
StringBuilder sql = new StringBuilder();
parentID = parentID.Replace(" ", "").Replace(",,", ",");
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] ");
sql.Append("where [dbo].[" + ShopMssqlHelper.TablePrefix + "CompareSTR](ParentID,'" + parentID + "',',')=1 And State=0 Order By Sort,CompanyID Desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, CompanyList);
}
}
return(CompanyList);
}
/// <summary>
/// 读取实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
//public T Read<T>(int id) where T : BaseModel
public T Read(int id)
{
//Type type = typeof(T);
string sqlText = string.Format("select {0} from [{1}{2}] where [ID]=@id", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName);
SqlParameter[] pt =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
pt[0].Value = id;
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText, pt))
{
return(this.PrepareModel(reader, type));
}
}
public List <CompanyInfo> ReadCompanyListByCompanyID(string companyID)
{
List <CompanyInfo> CompanyList = new List <CompanyInfo>();
if (!string.IsNullOrEmpty(companyID) && companyID != ",")
{
StringBuilder sql = new StringBuilder();
companyID = companyID.Replace(" ", "").Replace(",,", ",");
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] ");
sql.Append("where CompanyID in (" + companyID + ") Order By Sort,CompanyID Desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, CompanyList);
}
}
return(CompanyList);
}
public List <TestPaperInfo> ReadList(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(*) as TestNum,sum(point) as scorse");
if (Model.GroupCondition != "")
{
sql.Append("," + Model.GroupCondition);
}
sql.Append(" from [" + ShopMssqlHelper.TablePrefix + "TestPaper] ");
List <TestPaperInfo> TempList = new List <TestPaperInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
if (Model.GroupCondition != "")
{
sql.Append(" Group by " + Model.GroupCondition);
}
if (Model.GroupCondition == "cateid")
{
sql.Append(" Order by Cateid ");
}
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
//this.PrepareModel(reader, TempList);
while (dr.Read())
{
TestPaperInfo Model1 = new TestPaperInfo();
{
Model1.TestNum = dr.GetInt32(0);
Model1.Point = Convert.ToDecimal(dr[1].ToString());
Model1.UserId = dr.GetInt32(2);
Model1.CateId = dr.GetInt32(2);
}
TempList.Add(Model1);
}
}
return(TempList);
}
public int ReadQuestionNum(string courseID)
{
int questionsNum = 0;
if (!string.IsNullOrEmpty(courseID))
{
StringBuilder sql = new StringBuilder();
sql.Append("select Count(QuestionId) as [QuestionsNum] from [" + ShopMssqlHelper.TablePrefix + "Question] where [CateId] in (" + courseID + ")");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
}
return(questionsNum);
}
public Dictionary <int, float> ReadTheBestList(int userID, string courseID)
{
Dictionary <int, float> resultData = new Dictionary <int, float>();
if (!string.IsNullOrEmpty(courseID))
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from (");
sql.Append("select [CateId],[Scorse],row_number() over(partition by [CateId] order by [Scorse] desc) as rownumber from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where [Del]=0 and [CateId] in (" + courseID + ") and [UserID]=" + userID);
sql.Append(") as t where t.rownumber <=1");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
while (dr.Read())
{
resultData.Add(int.Parse(dr["CateId"].ToString()), float.Parse(dr["Scorse"].ToString()));
}
}
}
return(resultData);
}
/// <summary>
/// 读取所有实体的列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
//public List<T> ReadList<T>() where T : BaseModel
public List <T> ReadList <S>(S s)
{
//Type type = typeof(T);
string sqlText = string.Format("select {0} from [{1}{2}]", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName);
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, s);
if (!string.IsNullOrEmpty(mssqlCondition.ToString()))
{
sqlText = string.Format("{0} where {1}", sqlText, mssqlCondition.ToString());
}
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText))
{
return(this.PrepareList(reader));
}
}
public List <UserInfo> SearchReportUserList(UserSearchInfo userSearch)
{
List <UserInfo> userList = new List <UserInfo>();
StringBuilder sql = new StringBuilder();
sql.Append("select [ID],[UserName],[UserPassword],[Email],[Sex],[Introduce],[Photo],[MSN],[QQ],[Tel],[Mobile],[RegionID],[Address],[Birthday],[RegisterIP],[RegisterDate],[LastLoginIP],[LastLoginDate],[LoginTimes],[SafeCode],[FindDate],[Status],[OpenID],[GroupID],[RealName],[CompanyID],[WorkingPostID],[StudyPostID],[WorkingPostName],[Department],[IDCard],[PostStartDate],[EntryDate] from [" + ShopMssqlHelper.TablePrefix + "User] ");
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, userSearch);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [CompanyID] Desc, [Department],[WorkingPostID],[WorkingPostName],[StudyPostID],[RegisterDate]");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareUserModel(reader, userList);
}
}
return(userList);
}
public List <CourseCateInfo> ReadCourseCateAllList(CourseCateInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CourseCate] ");
List <CourseCateInfo> CourseCateList = new List <CourseCateInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [OrderIndex] ASC,CateId ASC");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareCourseCateModel(reader, CourseCateList);
}
}
return(CourseCateList);
}
public List <CompanyPostPlanInfo> CompanyPostPlanList(CompanyPostPlanInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CompanyPostPlan] ");
List <CompanyPostPlanInfo> CompanyPostPlanList = new List <CompanyPostPlanInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by StartDate");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, CompanyPostPlanList);
}
}
return(CompanyPostPlanList);
}
public List <QuestionInfo> ReadList(QuestionInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select " + Model.QuestionNum + " from [" + ShopMssqlHelper.TablePrefix + "Question] ");
List <QuestionInfo> TempList = new List <QuestionInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
sql.Append(" Order by newid()");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
return(TempList);
}
public List <TestSettingInfo> ReadList(TestSettingInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestSetting] ");
List <TestSettingInfo> TempList = new List <TestSettingInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [Id] desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
}
return(TempList);
}