/// <summary>
/// 分页方法
/// </summary>
/// <param name="allcount">总数</param>
/// <param name="tablename">表名,支持多表联查</param>
/// <param name="fields">字段名</param>
/// <param name="where">where条件 不需要加where</param>
/// <param name="orderby">*排序条件不能为空，不需要加order by</param>
/// <param name="getallcount">获取记录总数,true是，false否</param>
/// <param name="pageindex">当前页,从1开始,默认1</param>
/// <param name="pagesize">每页显示多少条数据，默认15</param>
/// <param name="commandTimeout">执行命令超时时间,默认30秒</param>
/// <returns></returns>
public static List <T> GetPageList(out int allcount, string tablename, string fields = "*", string where = "", string orderby = "",
bool getallcount = true, int pageindex = 1, int pagesize = 15, string connect = "", int commandTimeout = 30)
{
if (pageindex <= 0)
{
pageindex = 1;
}
if (pagesize <= 0)
{
pagesize = 15;
}
var parameters = new DynamicParameters();
parameters.Add("@Table", tablename);
parameters.Add("@Fields", fields);
parameters.Add("@Where", where);
parameters.Add("@OrderBy", orderby);
parameters.Add("@CurrentPage", pageindex);
parameters.Add("@PageSize", pagesize);
parameters.Add("@GetAllCount", getallcount);
parameters.Add("@AllCount", 0, DbType.Int32, ParameterDirection.Output);
MsSqlMapperHepler.commandTimeout = commandTimeout;
var ret = MsSqlMapperHepler.StoredProcWithParams <T>("Proc_DataPagination", parameters, string.IsNullOrWhiteSpace(connect) ? DBKeys.PRX : connect);
allcount = parameters.Get <int>("@AllCount");
return(ret);
}
/// <summary>
/// 获取转让协议未签名时获取的数据
/// </summary>
/// <param name="id"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public static vw_Transfer GetIntKeySign_stare(int id)
{
string strsql = "select top 1 * from vw_Transfer where id = '" + id + "' order by CreateTime desc";
vw_Transfer si = MsSqlMapperHepler.SqlWithParamsSingle <vw_Transfer>(strsql, null, DBKeys.PRX);
return(si);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static Classes GetClassesList()
{
string strsql = "select id from Classes order by CreateTime desc ";
var parameters = new DynamicParameters();
return(MsSqlMapperHepler.SqlWithParamsSingle <Classes>(strsql.ToString(), parameters, DBKeys.PRX));
}
public static string DPGetTableMaxId(string prefix, string field, string tablename, int digit, DBRepository db = null)
{
try
{
string retstr = null;
var parameters = new DynamicParameters();
parameters.Add("@title", prefix); //开头字母，前缀字母
parameters.Add("@pkName", field); //要插入的表字段
parameters.Add("@tableName", tablename); //所在表
parameters.Add("@bitCount", digit); //不包括前缀的位数
if (db == null)
{
retstr = MsSqlMapperHepler.StoredProcWithParamsSingle <string>("sp_createKey", parameters, DBKeys.PRX);
}
else
{
retstr = db.StoredProcWithParamsSingle <string>("sp_createKey", parameters).FirstOrDefault();
}
return(retstr); //返回值
}
catch (Exception ex)
{
throw new Exception(ex.Message + "转换的过程中发生了错误!");
}
}
/// <summary>
/// 获取文件路径
/// </summary>
/// <param name="classId"></param>
/// <param name="classIndex"></param>
/// <returns></returns>
public static List <vw_ClassListJob> ClassListJob(string classid, int classindex)
{
string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构
fields = @" * "; //输出字段
table = @" vw_ClassListJob "; //表或者视图
orderby = "id"; //排序信息
StringBuilder sb = new StringBuilder(); //构建where条件
sb.Append("select * from vw_ClassListJob where ");
sb.Append(" 1=1 ");
if (!string.IsNullOrWhiteSpace(classid))//班级ID
{
sb.Append(" and classid = @ClassID");
}
if (classindex != 0)//班级行号
{
sb.Append(" and classindex = @ClassIndex ");
}
sb.Append(" order by id ");
var parameters = new DynamicParameters();
parameters.Add("@classid", classid);
parameters.Add("@classindex", classindex);
return(MsSqlMapperHepler.SqlWithParams <vw_ClassListJob>(sb.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 保存学员评价
/// </summary>
/// <param name="ar"></param>
/// <returns></returns>
public static bool SaveStudentEvalute_WX(AttendanceRecord ar)
{
bool ret = false;
StringBuilder sb = new StringBuilder();
sb.Append(" update AttendanceRecord set Evaluate=@Evaluate ");
sb.Append(" , UpdateTime=@UpdateTime ");
sb.Append(" , UpdatorId=@UpdatorId ");
sb.Append(" where ClassID=@ClassID ");
sb.Append(" AND ClassIndex=@ClassIndex ");
sb.Append(" AND StudentID=@StudentID ");
var parameters = new DynamicParameters();
parameters.Add("@StudentID", ar.StudentID);
parameters.Add("@Evaluate", ar.Evaluate);
parameters.Add("@ClassID", ar.ClassID);
parameters.Add("@ClassIndex", ar.ClassIndex);
parameters.Add("@UpdateTime", ar.UpdateTime);
parameters.Add("@UpdatorId", ar.UpdatorId);
if (MsSqlMapperHepler.InsertUpdateOrDeleteSql(sb.ToString(), parameters, DBKeys.PRX) > 0)
{
ret = true;
}
return(ret);
}
/// <summary>
/// 获取主键类型是整数的签名对象
/// </summary>
/// <param name="id"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public static SignImage GetIntKeySign(int id, int typeid)
{
string strsql = "select top 1 * from SignImage where IntKey = '" + id + "' and TypeID = " + typeid + " order by CreateTime desc";
SignImage si = MsSqlMapperHepler.SqlWithParamsSingle <SignImage>(strsql, null, DBKeys.PRX);
return(si);
}
/// <summary>
/// 学员信息和预约单绑定
/// </summary>
/// <param name="studi"></param>
/// <param name="apid"></param>
public static void BindStudentforAP(string studi, string apid)
{
Appointment ap = AppointmentData.GetOneByID(apid);
ap.ApStudentID = studi;
MsSqlMapperHepler.Update <Appointment>(ap, DBKeys.PRX);
}
/// <summary>
/// 获取启用课程下拉
/// </summary>
/// <returns></returns>
public static List <CommonEntity> GetCourseIL()
{
List <CommonEntity> ret;
string sql = "select id,CourseName as name from Course";
ret = MsSqlMapperHepler.SqlWithParams <CommonEntity>(sql.ToString(), null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateTransfer(Transfer Rb)
{
Transfer Rbs = TransferData.GetTransferByID(Rb.ID); //获取对象
Cloner <Transfer, Transfer> .CopyTo(Rb, Rbs); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Rbs, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateButton(SYSButton btn)
{
SYSButton btnto = ButtonData.GetButtonByID(btn.BTN_Id); //获取对象
Cloner <SYSButton, SYSButton> .CopyTo(btn, btnto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(btnto, DBKeys.PRX));
}
/// <summary>
/// 保存班级信息
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool Update(Classes Stu)
{
Classes Stuto = ClassesData.GetClassesByID(Stu.ID); //获取对象
Cloner <Classes, Classes> .CopyTo(Stu, Stuto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX));
}
/// <summary>
/// 获取SYS_Account表最大的主键
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static int Max_ACC_Id()
{
string strsql = " select isnull(max(ACC_Id),1)as ID from SYS_Account";
var parameters = new DynamicParameters();
parameters.Add("@id");
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取本次上课信息
/// </summary>
/// <param name="classid"></param>
/// <param name="classindex"></param>
/// <returns></returns>
public static vw_ClassAttendanceList GetOneClassAttendanceList(string classid, int classindex)
{
string strsql = "select * from vw_ClassAttendanceList where ClassID = '" + classid + "' and ClassIndex = " + classindex;
vw_ClassAttendanceList ret = new vw_ClassAttendanceList();
ret = MsSqlMapperHepler.SqlWithParamsSingle <vw_ClassAttendanceList>(strsql, null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 根据班级ID获取学员考勤信息
/// </summary>
/// <param name="classid"></param>
/// <returns></returns>
public static List <vw_AttendanceRecord> GetAttendanceRecordByClassID(string classid, int ClassIndex)
{
string strsql = "select * from vw_AttendanceRecord where ClassID = '" + classid + "' and ClassIndex = " + ClassIndex;
List <vw_AttendanceRecord> ret = new List <vw_AttendanceRecord>();
ret = MsSqlMapperHepler.SqlWithParams <vw_AttendanceRecord>(strsql, null, DBKeys.PRX);
return(ret);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateReimburse(Reimburse Rb)
{
Reimburse Rbs = ReimburseData.GetReimburseByID(Rb.ID); //获取对象
Cloner <Reimburse, Reimburse> .CopyTo(Rb, Rbs); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Rbs, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateDiscount(Discount Dis)
{
Discount Stuto = DiscountData.GetDiscountByID(Dis.ID); //获取对象
Cloner <Discount, Discount> .CopyTo(Dis, Stuto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX));
}
/// <summary>
/// 根据TimePeriod的ID获取开始时间
/// </summary>
/// <param name="perid"></param>
/// <returns></returns>
public static string GetStartTimePeriodByid(int perid)
{
string ret = "";
string strsql = "select DicItemName from DictionaryItem where DicTypeID = 8 and [DicItemID] = " + perid;
ret = " " + MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql, null, DBKeys.PRX).Substring(0, 5);
return(ret);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateRefund(Refund re)
{
Refund Ref = RefundInfoData.GetRefundByID(re.ID); //获取对象
Cloner <Refund, Refund> .CopyTo(re, Ref); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Ref, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateStudent(Students Stu)
{
Students Stuto = StudentData.GetStudentsByID(Stu.ID); //获取对象
Cloner <Students, Students> .CopyTo(Stu, Stuto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateAttendanceRecord(AttendanceRecord att)
{
AttendanceRecord Stuto = StudentScheduleListData.GetAttendanceRecordByID(att.ID); //获取对象
Cloner <AttendanceRecord, AttendanceRecord> .CopyTo(att, Stuto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateSYS_Account(SYSAccount sys)
{
SYSAccount sysa = Accounts_UpdateData.GetSYS_AccountByID(sys.ACC_Id); //获取对象
Cloner <SYSAccount, SYSAccount> .CopyTo(sys, sysa); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(sysa, DBKeys.PRX));
}
public static bool updateCourse(Course course)
{
Course to = CourseData.getCourseById(course.ID);
Cloner <Course, Course> .CopyTo(course, to);
return(MsSqlMapperHepler.Update(to, DBKeys.PRX));
}
/// <summary>
/// 保存
/// </summary>
/// <param name="btn"></param>
/// <returns></returns>
public static bool UpdateMessage(Message Mes)
{
Message Stuto = MessageData.GetMessageByID(Mes.ID); //获取对象
Cloner <Message, Message> .CopyTo(Mes, Stuto); //代码克隆，把前台或者的值也就是变更内容复制到目标对象，不做变更的数据不变
return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX));
}
/// <summary>
/// 跳转查询账号信息
/// </summary>
/// <param name="apid"></param>
/// <returns></returns>
public static List <SYSAccount> GetAccounts_Update(string ACC_Account)
{
String sql = "select * from SYS_Account where ACC_Account = @ACC_Account ";
var dynamic = new DynamicParameters();
dynamic.Add("@ACC_Account", ACC_Account);
return(MsSqlMapperHepler.SqlWithParams <SYSAccount>(sql, dynamic, DBKeys.PRX));
}
/// <summary>
/// 根据绑定的账号获取一名学员信息
/// </summary>
/// <param name="AccountID"></param>
/// <returns></returns>
public static Students GetStudentByAccountID(string AccountID)
{
string strsql = "select TOP 1 * from Students where BindAccount = @AccountID ";
var parameters = new DynamicParameters();
parameters.Add("@AccountID", AccountID);
return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static Students GetStudentsOne(string Mosaic)
{
string strsql = "select top 1 * from Students WHERE ID like @Mosaic + '%' order by CreateTime desc ";
var parameters = new DynamicParameters();
parameters.Add("@Mosaic", Mosaic);
return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 根据预约单号ID的主键 获取列表vw_Appointment
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static vw_Appointment Getvw_AppointmentList(string ID)
{
string strsql = "select top 1 * from vw_Appointment WITH(NOLOCK) where ID=@ID ";
var parameters = new DynamicParameters();
parameters.Add("@ID", ID);
return(MsSqlMapperHepler.SqlWithParamsSingle <vw_Appointment>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 修改判断手机号是唯一
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static int BindPhone_update(string id, string BindPhone)
{
string strsql = "select id from Students where id <> @id and BindPhone = @BindPhone";
var parameters = new DynamicParameters();
parameters.Add("@id", id);
parameters.Add("@BindPhone", BindPhone);
return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX));
}
/// <summary>
/// 新增判断手机号+姓名是唯一
/// </summary>
/// <param name="Stockid"></param>
/// <returns></returns>
public static string BindPhone_insert(string BindPhone, string name)
{
string strsql = "select id from Students WITH(NOLOCK) where BindPhone=@BindPhone and Name = @Name";
var parameters = new DynamicParameters();
parameters.Add("@BindPhone", BindPhone);
parameters.Add("@Name", name);
return(MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql.ToString(), parameters, DBKeys.PRX));
}
