/// <summary>
/// 向数据表Apply更新一条记录。带事务
/// </summary>
/// <param name="sp">事务对象</param>
/// <param name="_ApplyModel">_ApplyModel</param>
/// <returns>影响的行数</returns>
public int Update(SqlTransaction sp, ApplyEntity _ApplyModel)
{
string sqlStr = "update Apply set [ShopId]=@ShopId,[OpenId]=@OpenId,[Money]=@Money,[Description]=@Description,[BankId]=@BankId,[Status]=@Status,[Reason]=@Reason,[Addtime]=@Addtime,[Updatetime]=@Updatetime where ApplyId=@ApplyId";
SqlParameter[] _param =
{
new SqlParameter("@ApplyId", SqlDbType.Int),
new SqlParameter("@ShopId", SqlDbType.Int),
new SqlParameter("@OpenId", SqlDbType.VarChar),
new SqlParameter("@Money", SqlDbType.Decimal),
new SqlParameter("@Description", SqlDbType.VarChar),
new SqlParameter("@BankId", SqlDbType.Int),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@Reason", SqlDbType.VarChar),
new SqlParameter("@Addtime", SqlDbType.DateTime),
new SqlParameter("@Updatetime", SqlDbType.DateTime)
};
_param[0].Value = _ApplyModel.ApplyId;
_param[1].Value = _ApplyModel.ShopId;
_param[2].Value = _ApplyModel.OpenId;
_param[3].Value = _ApplyModel.Money;
_param[4].Value = _ApplyModel.Description;
_param[5].Value = _ApplyModel.BankId;
_param[6].Value = _ApplyModel.Status;
_param[7].Value = _ApplyModel.Reason;
_param[8].Value = _ApplyModel.Addtime;
_param[9].Value = _ApplyModel.Updatetime;
return(SqlHelper.ExecuteNonQuery(sp, CommandType.Text, sqlStr, _param));
}
///<summery>
/// Provider views all applications
/// (非学生用户)查看所有申请
/// dumei 08.23
///</summery>
public static async Task <ApplyItemEntity> ProViewApps(int provider_id)
{
var apps = new ApplyItemEntity();
apps.total = 0;
apps.ApplyItem = new List <ApplyEntity>();
var query = "SELECT apply_id,user_id,worker_id,work_id,resume_id,status FROM apply WHERE worker_id=@id ORDER BY status ASC";
using var connection = DatabaseConnector.Connect();
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = query;
command.Parameters.AddWithValue("@id", provider_id);
using var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
var temp = new ApplyEntity()
{
apply_id = reader.GetInt32("apply_id"),
student_id = reader.GetInt32("user_id"),
teacher_id = reader.GetInt32("worker_id"),
work_id = reader.GetInt32("work_id"),
resume_id = reader.GetInt32("resume_id"),
status = reader.GetInt32("status")
};
apps.total++;
apps.ApplyItem.Add(temp);
}
return(apps);
}
/// <summary>
/// 向数据库中插入一条新记录。带事务
/// </summary>
/// <param name="sp">事务对象</param>
/// <param name="_ApplyModel">Apply实体</param>
/// <returns>新插入记录的编号</returns>
public int Insert(SqlTransaction sp, ApplyEntity _ApplyModel)
{
string sqlStr = "insert into Apply([ShopId],[OpenId],[Money],[Description],[BankId],[Status],[Reason],[Addtime],[Updatetime]) values(@ShopId,@OpenId,@Money,@Description,@BankId,@Status,@Reason,@Addtime,@Updatetime) select @@identity";
int res;
SqlParameter[] _param =
{
new SqlParameter("@ShopId", SqlDbType.Int),
new SqlParameter("@OpenId", SqlDbType.VarChar),
new SqlParameter("@Money", SqlDbType.Decimal),
new SqlParameter("@Description", SqlDbType.VarChar),
new SqlParameter("@BankId", SqlDbType.Int),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@Reason", SqlDbType.VarChar),
new SqlParameter("@Addtime", SqlDbType.DateTime),
new SqlParameter("@Updatetime", SqlDbType.DateTime)
};
_param[0].Value = _ApplyModel.ShopId;
_param[1].Value = _ApplyModel.OpenId;
_param[2].Value = _ApplyModel.Money;
_param[3].Value = _ApplyModel.Description;
_param[4].Value = _ApplyModel.BankId;
_param[5].Value = _ApplyModel.Status;
_param[6].Value = _ApplyModel.Reason;
_param[7].Value = _ApplyModel.Addtime;
_param[8].Value = _ApplyModel.Updatetime;
res = Convert.ToInt32(SqlHelper.ExecuteScalar(sp, CommandType.Text, sqlStr, _param));
return(res);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="entity">实体</param>
/// <returns></returns>
public bool InsertApply(ApplyEntity entity)
{
using (var db = _dbContext.GetIntance())
{
try
{
db.Ado.BeginTran();
db.Insertable(entity).IgnoreColumns(it => it.applyid).ExecuteCommand();
LogEntity logEntity = new LogEntity()
{
userid = entity.userid,
createtime = DateTime.Now,
logtitle = "新增",
logcontent = "提交下载申请"
};
db.Insertable(logEntity).ExecuteCommand();
db.Ado.CommitTran();
}
catch (Exception)
{
db.Ado.RollbackTran();
return(false);
}
return(true);
}
}
/// <summary>
/// states:1 为取消审核传入 2 为删除传入
/// </summary>
/// <param name="applyids"></param>
/// <param name="states"></param>
/// <returns></returns>
public bool OperationBatch(List <int> applyids, int states)
{
using (var db = _dbContext.GetIntance())
{
try
{
db.Ado.BeginTran();
foreach (var id in applyids)
{
var userid = db.Queryable <ApplyEntity>().Where(it => it.applyid == id).First().userid;
if (states == 1)
{
var model = new ApplyEntity()
{
state = 0
};
db.Updateable(model)
.UpdateColumns(it => new { it.state })
.Where(it => it.applyid == id)
.ExecuteCommand();
LogEntity logEntity = new LogEntity()
{
userid = userid,
createtime = DateTime.Now,
logtitle = "审核管理员操作",
logcontent = "取消审核 该applyid为" + id
};
db.Insertable(logEntity).ExecuteCommand();
}
else
{
db.Deleteable <ApplyEntity>().Where(it => it.applyid == id).ExecuteCommand();
LogEntity logEntity = new LogEntity()
{
userid = userid,
createtime = DateTime.Now,
logtitle = "审核管理员操作",
logcontent = "删除 该applyid为" + id
};
db.Insertable(logEntity).ExecuteCommand();
}
}
db.Ado.CommitTran();
}
catch (Exception)
{
db.Ado.RollbackTran();
return(false);
}
return(true);
}
}
//[AllowAnonymous]
public async Task <ActionResult <ApplyResponse> > ProManageApp(ManageApplyRequest request)
{
var pro_id = Int32.Parse(User.Identity.Name);
if (await UserAccessor.CheckRole(pro_id) == Role.Student)
{
return(BadRequest(new { message = "Students cannot manage application." }));
}
ApplyEntity ae = await ApplyAccessor.Read(request.apply_id);
if (ae == null)
{
return(BadRequest(new { message = "Apply id not found." }));
}
if (ae.status != Constants.ApplyStatus.Applying)
{
return(BadRequest(new { message = "Application have been managed." }));
}
var success_change = await ApplyAccessor.SetApplyStatus(request.apply_id, request.status);
if (success_change > 0)
{
ae.status = request.status; //减少查询
ApplyResponse ar = _mapper.Map <ApplyResponse>(ae);
// 未检查id是否存在
ar.student_name = await UserAccessor.GetUserName(ae.student_id);
ar.teacher_name = await UserAccessor.GetUserName(ae.teacher_id);
ar.work_name = await WorkAccessor.GetWorkName(ae.work_id);
if (request.status == Constants.ApplyStatus.Accepted)
{
// 录用，创建takes
var take = new TakesEntity
{
work_name = ar.work_name,
student_id = ae.student_id,
work_id = ae.work_id,
work_time = await WorkAccessor.GetWorkTotalTime(ae.work_id)
};
await TakesAccessor.Create(take);
}
return(ar);
}
return(BadRequest(new { message = "Update failed" }));
}
/// <summary>
/// 得到 apply 数据实体
/// </summary>
/// <param name="dr">dr</param>
/// <returns>apply 数据实体</returns>
public ApplyEntity Populate_ApplyEntity_FromDr(IDataReader dr)
{
ApplyEntity Obj = new ApplyEntity();
Obj.ApplyId = ((dr["ApplyId"]) == DBNull.Value)?0:Convert.ToInt32(dr["ApplyId"]);
Obj.ShopId = ((dr["ShopId"]) == DBNull.Value)?0:Convert.ToInt32(dr["ShopId"]);
Obj.OpenId = dr["OpenId"].ToString();
Obj.Money = ((dr["Money"]) == DBNull.Value)?0:Convert.ToDecimal(dr["Money"]);
Obj.Description = dr["Description"].ToString();
Obj.BankId = ((dr["BankId"]) == DBNull.Value)?0:Convert.ToInt32(dr["BankId"]);
Obj.Status = ((dr["Status"]) == DBNull.Value)?0:Convert.ToInt32(dr["Status"]);
Obj.Reason = dr["Reason"].ToString();
Obj.Addtime = ((dr["Addtime"]) == DBNull.Value)?Convert.ToDateTime("1900-1-1"):Convert.ToDateTime(dr["Addtime"]);
Obj.Updatetime = ((dr["Updatetime"]) == DBNull.Value)?Convert.ToDateTime("1900-1-1"):Convert.ToDateTime(dr["Updatetime"]);
return(Obj);
}
public bool Examine(int applyid, string reson, int states)
{
using (var db = _dbContext.GetIntance())
{
try
{
db.Ado.BeginTran();
var model = new ApplyEntity()
{
reson = reson,
state = states
};
db.Updateable(model)
.UpdateColumns(it => new { it.reson, it.state })
.Where(it => it.applyid == applyid)
.ExecuteCommand();
int userid = db.Queryable <ApplyEntity>().Where(it => it.applyid == applyid).First().userid;
LogEntity logEntity = new LogEntity()
{
userid = userid,
createtime = DateTime.Now,
logtitle = "审核"
};
switch (states)
{
case 1:
logEntity.logcontent = "通过";
break;
case -1:
logEntity.logcontent = "退回";
break;
}
db.Insertable(logEntity).ExecuteCommand();
db.Ado.CommitTran();
}
catch (Exception ex)
{
string error = ex.Message.ToString();
db.Ado.RollbackTran();
return(false);
}
return(true);
}
}
public static async Task <int> Create(ApplyEntity apply)
{
var query = "INSERT INTO apply(user_id,worker_id,work_id,resume_id,status)VALUES(@student_id,@teacher_id,@work_id,@resume_id,@status)";
using var connection = DatabaseConnector.Connect();
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = query;
command.Parameters.AddWithValue("@student_id", apply.student_id);
command.Parameters.AddWithValue("@teacher_id", apply.teacher_id);
command.Parameters.AddWithValue("@work_id", apply.work_id);
command.Parameters.AddWithValue("@resume_id", apply.resume_id);
command.Parameters.AddWithValue("@status", 0);
await command.ExecuteNonQueryAsync();
return((int)command.LastInsertedId);
}
/// <summary>
/// 根据ID,返回一个Apply对象
/// </summary>
/// <param name="applyId">applyId</param>
/// <returns>Apply对象</returns>
public ApplyEntity Get_ApplyEntity(int applyId)
{
ApplyEntity _obj = null;
SqlParameter[] _param =
{
new SqlParameter("@ApplyId", SqlDbType.Int)
};
_param[0].Value = applyId;
string sqlStr = "select * from Apply with(nolock) where ApplyId=@ApplyId";
using (SqlDataReader dr = SqlHelper.ExecuteReader(WebConfig.WfxRW, CommandType.Text, sqlStr, _param))
{
while (dr.Read())
{
_obj = Populate_ApplyEntity_FromDr(dr);
}
}
return(_obj);
}
/// <summary>
/// 得到 apply 数据实体
/// </summary>
/// <param name="row">row</param>
/// <returns>apply 数据实体</returns>
public ApplyEntity Populate_ApplyEntity_FromDr(DataRow row)
{
ApplyEntity Obj = new ApplyEntity();
if (row != null)
{
Obj.ApplyId = ((row["ApplyId"]) == DBNull.Value)?0:Convert.ToInt32(row["ApplyId"]);
Obj.ShopId = ((row["ShopId"]) == DBNull.Value)?0:Convert.ToInt32(row["ShopId"]);
Obj.OpenId = row["OpenId"].ToString();
Obj.Money = ((row["Money"]) == DBNull.Value)?0:Convert.ToDecimal(row["Money"]);
Obj.Description = row["Description"].ToString();
Obj.BankId = ((row["BankId"]) == DBNull.Value)?0:Convert.ToInt32(row["BankId"]);
Obj.Status = ((row["Status"]) == DBNull.Value)?0:Convert.ToInt32(row["Status"]);
Obj.Reason = row["Reason"].ToString();
Obj.Addtime = ((row["Addtime"]) == DBNull.Value)?Convert.ToDateTime("1900-1-1"):Convert.ToDateTime(row["Addtime"]);
Obj.Updatetime = ((row["Updatetime"]) == DBNull.Value)?Convert.ToDateTime("1900-1-1"):Convert.ToDateTime(row["Updatetime"]);
}
else
{
return(null);
}
return(Obj);
}
public ActionResult CreateApply(ApplyEntity model)
{
RespResultViewModel resultModel = new RespResultCountViewModel();
try
{
#region 验证
if (model.userid == 0 || model.resourceid == 0 || string.IsNullOrEmpty(model.username) || model.depid == 0)
{
resultModel.code = -1;
resultModel.msg = "存在必填项!";
return(Ok(resultModel));
}
#endregion
model.createtime = DateTime.Now;
model.state = 0;
bool result = _specailService.InsertApply(model);
if (result)
{
resultModel.code = 0;
resultModel.msg = "新增成功";
}
else
{
resultModel.code = -1;
resultModel.msg = "新增失败";
}
return(Ok(resultModel));
}
catch (Exception ex)
{
resultModel.code = -1;
resultModel.msg = "操作失败" + ex.ToString();
return(Ok(resultModel));
}
}
public void Update(ApplyEntity applyEntity)
{
applydal.Update(applyEntity);
}
public int Insert(ApplyEntity applyEntity)
{
return(applydal.Insert(applyEntity));
}
public string Download(int applyid, string type)
{
string resourcedir = string.Empty;
if (type == "1")
{
using (var db = _dbContext.GetIntance())
{
try
{
db.Ado.BeginTran();
var model = new ApplyEntity()
{
state = 2
};
db.Updateable(model)
.UpdateColumns(it => new { it.state })
.Where(it => it.applyid == applyid)
.ExecuteCommand();
var resourceid = db.Queryable <ApplyEntity>().Where(it => it.applyid == applyid).First().resourceid;
resourcedir = db.Queryable <ResourceEntity>().Where(it => it.resourceid == resourceid).First().resourcedir;
var userid = db.Queryable <ApplyEntity>().Where(it => it.applyid == applyid).First().userid;
LogEntity logEntity = new LogEntity()
{
userid = userid,
createtime = DateTime.Now,
logtitle = "下载",
logcontent = "已下载"
};
db.Insertable(logEntity).ExecuteCommand();
db.Ado.CommitTran();
}
catch (Exception)
{
db.Ado.RollbackTran();
return("");
}
}
}
if (type == "2")
{
using (var db = _dbContext.GetIntance())
{
try
{
db.Ado.BeginTran();
var model = new ApplyEntity()
{
state = 2
};
db.Updateable(model)
.UpdateColumns(it => new { it.state })
.Where(it => it.applyid == applyid)
.ExecuteCommand();
var resourceid = db.Queryable <ApplyEntity>().Where(it => it.applyid == applyid).First().resourceid;
resourcedir = db.Queryable <ResourceEntity>().Where(it => it.resourceid == resourceid).First().resourcename;
var userid = db.Queryable <ApplyEntity>().Where(it => it.applyid == applyid).First().userid;
LogEntity logEntity = new LogEntity()
{
userid = userid,
createtime = DateTime.Now,
logtitle = "下载",
logcontent = "已下载"
};
db.Insertable(logEntity).ExecuteCommand();
db.Ado.CommitTran();
}
catch (Exception)
{
db.Ado.RollbackTran();
return("");
}
}
}
return(resourcedir);
}
