public void DeleteCompany(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("delete from [" + ShopMssqlHelper.TablePrefix + "Company] where [CompanyId]=" + Id);
DbSQLHelper.ExecuteSql(sql.ToString());
}
public void UpdateCompany(string Field, string Value, string Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("update [" + ShopMssqlHelper.TablePrefix + "Company] set [" + Field + "]=" + Value + " where [CompanyId] in (" + Id + ")");
DbSQLHelper.ExecuteSql(sql.ToString());
}
public void UpdateQuestionChecked(string Id, string Value)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "Question] set Checked=" + Value + " where QuestionId in (" + Id + ")");
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 通过课程ID删除考题
/// </summary>
/// <param name="Id"></param>
public void DeleteQuestionByCateId(int CateId)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "Question] where CateId=" + CateId);
DbSQLHelper.ExecuteSql(sql.ToString());
}
public void DeletePost(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "Post] where [PostId]=" + Id);
DbSQLHelper.ExecuteSql(sql.ToString());
}
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 void DeleteCourseByCatId(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "Course] where CateId=" + Id);
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 删除记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int DeleteCompanyRecord(string Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete From [" + ShopMssqlHelper.TablePrefix + "CompanyRecord] Where Id in (" + Id + ")");
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
public void UpdatePaperCompanyId(int UserId, int CompanyId)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "TestPaper] set CompanyId=" + CompanyId + " where UserId=" + UserId);
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 更新记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int UpdateCompanyPostPlan(CompanyPostPlanInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("update [" + ShopMssqlHelper.TablePrefix + "CompanyPostPlan] set StartDate=" + Model.StartDate + ",PostId=" + Model.PostId);
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
public void UpdateIsRZ(PostPassInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "PassPost] set [CreateDate]='" + DateTime.Now + "',[IsRZ]=" + Model.IsRZ + " where [UserId]=" + Model.UserId + " and [PostId]=" + Model.PostId);
DbSQLHelper.ExecuteSql(sql.ToString());
}
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 void UpdateCourse(string courseID, int parentID)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "Course] Set [CateId]=" + parentID + " Where [CourseId] in (" + courseID + ")");
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 更新记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int UpdateCompanyRule(CompanyRuleInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("update CompanyRule set CourseNum=" + Model.CourseNum + ",Frequency=" + Model.Frequency + ",StartDate=" + Model.StartDate + ",EndDate=" + Model.EndDate + ",PostId=" + Model.PostId);
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
public void DeleteQuestion(string IdStr)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "Question] where QuestionId in (" + IdStr + ")");
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 更新记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int UpdateCompanyRecord(CompanyRecordInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("update [" + ShopMssqlHelper.TablePrefix + "CompanyRecord] set ChangeNum=" + Model.ChangeNum + ",Reason=" + Model.Reason + ",PostId=" + Model.PostId);
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
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 void DeleteTestSetting(int Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "TestSetting] where Id=" + Id);
DbSQLHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 删除记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int DeleteCompanyRule(string Id)
{
StringBuilder sql = new StringBuilder();
sql.Append("Delete From CompanyRule Where Id in (" + Id + ")");
return(DbSQLHelper.ExecuteSql(sql.ToString()));
}
/// <summary>
/// 获取通过的岗位数
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public int ReadPostPassNum(int userID)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(1) from [" + ShopMssqlHelper.TablePrefix + "PassPost] where [UserID]=" + userID + " and [IsRZ]=1 and [postid] in (select [postid] from [_post])");
return((int)DbSQLHelper.GetSingle(sql.ToString()));
}
public void UpdatePostPassInfo(PostPassInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "PassPost] set [UserId]=@UserId,[PostId]=@PostId,[PostName]=@PostName where [Id]=@Id");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
DbSQLHelper.ExecuteSql(sql.ToString(), par);
}
public int AddPostPassInfo(PostPassInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Insert into [" + ShopMssqlHelper.TablePrefix + "PassPost] ([UserId],[PostId],[IsRZ],[PostName]) values(@UserId,@PostId,@IsRZ,@PostName)");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
return(DbSQLHelper.ExecuteSql(sql.ToString(), par));
}
public void UpdatePaper(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "TestPaper] set PaperName=@PaperName,CateId=@CateId,QuestionStyle=@QuestionStyle,QuestionId=@QuestionId,Answer=@Answer,Locked=@Locked,CompanyId=@CompanyId,UserId=@UserId,Scorse=@Scorse,Point=@Point,[IsPass]=@IsPass where TestPaperId=@TestPaperId");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
DbSQLHelper.ExecuteSql(sql.ToString(), par);
}
public void UpdateTestSetting(TestSettingInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "TestSetting] set [CourseId]=@courseId,[PaperScore]=@paperScore,[TestQuestionsCount]=@testQuestionsCount,[TestTimeLength]=@testTimeLength,[LowScore]=@lowScore,[TestStartTime]=@testStartTime,[TestEndTime]=@testEndTime,[CompanyId]=@companyId,[TestInterval]=@testInterval where [Id]=@id");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
DbSQLHelper.ExecuteSql(sql.ToString(), par);
}
/// <summary>
///
/// </summary>
/// <param name="Id"></param>
public void DeletePaper(int Id)
{
StringBuilder sql = new StringBuilder();
//sql.Append("Delete from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where TestPaperId=" + Id);
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "TestPaper] Set [Del]=1 where TestPaperId=" + Id);
DbSQLHelper.ExecuteSql(sql.ToString());
}
public void UpdateCourse(CourseInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update [" + ShopMssqlHelper.TablePrefix + "Course] set CourseName=@CourseName,CourseCode=@CourseCode,CourseScore=@CourseScore,CateId=@CateId,OrderIndex=@OrderIndex,CompanyId=@CompanyId,BrandId=@BrandId where CourseId=@CourseId");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
DbSQLHelper.ExecuteSql(sql.ToString(), par);
}
public int AddSMSRecord(SMSRecordInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into [" + ShopMssqlHelper.TablePrefix + "SMSRecord]([Mobile],[VerCode],[DataCreateDate]) values(@Mobile,@VerCode,@DataCreateDate)");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
return(DbSQLHelper.ExecuteSql(sql.ToString(), par));
}
/// <summary>
/// 添加记录信息
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public int AddUserRecord(ProRecordInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into [" + ShopMssqlHelper.TablePrefix + "ProRecord] (UserId,ChangeNum,Reson) values(@UserId,@ChangeNum,@Reson)");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
return(DbSQLHelper.ExecuteSql(sql.ToString(), par));
}
public int AddCompanyRule(CompanyRuleInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into CompanyRule (CompanyId,CourseNum,Frequency,StartDate,EndDate,PostId) values(@CompanyId,@CourseNum,@Frequency,@StartDate,@EndDate,@PostId)");
SqlParameter[] par = (SqlParameter[])this.ValueParas(Model);
return(DbSQLHelper.ExecuteSql(sql.ToString(), par));
}
