/// <summary>
/// 人员第一次进入的时候，向数据库插入数据
/// </summary>
/// <param name="jwUsedetail"></param>
/// <returns></returns>
public int FirstCheckIn(JW_Usedetail jwUsedetail)
{
//1.获取当前监居区所在单位的ID
string sqlPoliceUnitId = string.Format(@"
select pa.unit_id from JW_Apply ja
join Base_PoliceArea pa on ja.PoliceArea_id=pa.PoliceArea_id
where ja.apply_id='{0}'", jwUsedetail.apply_id);
DataTable dtPoliceUnitId = SqlHelper.DataTable(sqlPoliceUnitId, CommandType.Text);
string unit_id = dtPoliceUnitId.Rows[0]["unit_id"].ToString();
JW_Apply_room jwApplyRoom = new JW_Apply_room()
{
apply_room_id = Guid.NewGuid().ToString(),
unit_id = unit_id,
apply_id = jwUsedetail.apply_id,
adduser_id = jwUsedetail.adduser_id,
adddate = jwUsedetail.addDate,
Room_id = jwUsedetail.room_id,
startdate = jwUsedetail.startdate,
state = 1
};
string sqlInsert = string.Format(@"
insert into JW_Apply_room(apply_room_id,unit_id,apply_id,adduser_id,adddate,Room_id,startdate,state)
values(@apply_room_id,@unit_id,@apply_id,@adduser_id,@adddate,@Room_id,@startdate,@state)");
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@apply_room_id", jwApplyRoom.apply_room_id),
new SqlParameter("@unit_id", jwApplyRoom.unit_id),
new SqlParameter("@apply_id", jwApplyRoom.apply_id),
new SqlParameter("@adduser_id", jwApplyRoom.adduser_id),
new SqlParameter("@adddate", jwApplyRoom.adddate == null?(object)DBNull.Value:jwApplyRoom.adddate),
new SqlParameter("@Room_id", jwApplyRoom.Room_id),
new SqlParameter("@startdate", jwApplyRoom.startdate == null?(object)DBNull.Value:jwApplyRoom.startdate),
new SqlParameter("@state", jwApplyRoom.state),
};
try
{
int r = SqlHelper.ExecuteNonQuery(sqlInsert, CommandType.Text, pars);
return(r);
}
catch (Exception)
{
return(0);
}
}
/// <summary>
/// 人员进入 保存
/// </summary>
/// <param name="apply_id"></param>
/// <param name="user_id"></param>
/// <param name="unit_id"></param>
/// <returns></returns>
public int SubmitCheckInForm(JW_Usedetail jwUsedetail, string unit_id, JW_Apply jwApply)
{
//先判断JW_Usedetail表中是否有该apply_id的记录
string checksql = string.Format(@"select * from JW_Usedetail where apply_id='{0}' and isend=0", jwUsedetail.apply_id);
try
{
int r = SqlHelper.DataTable(checksql, CommandType.Text).Rows.Count;
if (r > 0)
{
//有数据
return(-1);
}
}
catch (Exception)
{
//数据异常
return(-2);
}
//获取JW_Apply相关数据信息
string getApplySql = string.Format(@"select * from JW_Apply where apply_id='{0}'", jwUsedetail.apply_id);
string apply_unit_id = string.Empty;
string apply_policeArea = string.Empty;
try
{
DataTable dtApply = SqlHelper.DataTable(getApplySql, CommandType.Text);
if (dtApply.Rows.Count > 0)
{
apply_unit_id = dtApply.Rows[0]["unit_id"].ToString();
apply_policeArea = dtApply.Rows[0]["PoliceArea_id"].ToString();
}
else
{
//数据异常
return(-2);
}
}
catch (Exception)
{
//数据异常
return(-2);
}
//插入JW_Usedetail，更新JW_Apply状态，更新JW_SendPolice的状态
string sql = string.Format(@"insert into JW_Usedetail(Usedetail_id,unit_id,PoliceArea_id,apply_id,adduser_id,addDate,room_id,startdate,isend) values(NEWID(),@unit_id,@PoliceArea_id,@apply_id,@adduser_id,@addDate,@room_id,@startdate,@isend);
update JW_Apply set state=4,fact_indate=@startdate, xyr_name=@xyr_name, xyr_sex=@xyr_sex, xyr_sfz_id=@xyr_sfz_id, xyr_address=@xyr_address where apply_id=@apply_id;
update JW_SendPolice set state=2 where type=1 and Object_id=@apply_id and user_id=@adduser_id and state=1;
insert into JW_SafetyCheck(SafetyCheck_id,unit_id,PoliceArea_id,apply_id,adduser_id,addDate,room_id,checkdate,detail)
values(NEWID(),@unit_id,@PoliceArea_id,@apply_id,@adduser_id,GETDATE(),@room_id,GETDATE(),'安全')
");
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@unit_id", apply_unit_id),
new SqlParameter("@PoliceArea_id", apply_policeArea),
new SqlParameter("@apply_id", jwUsedetail.apply_id),
new SqlParameter("@adduser_id", jwUsedetail.adduser_id),
new SqlParameter("@addDate", jwUsedetail.addDate),
new SqlParameter("@room_id", jwUsedetail.room_id),
new SqlParameter("@startdate", jwUsedetail.startdate == null?(object)DBNull.Value:jwUsedetail.startdate),
new SqlParameter("@isend", jwUsedetail.isend),
new SqlParameter("@xyr_name", jwApply.xyr_name == null?"":jwApply.xyr_name),
new SqlParameter("@xyr_sex", jwApply.xyr_sex == null?"":jwApply.xyr_sex),
new SqlParameter("@xyr_sfz_id", jwApply.xyr_sfz_id == null?"":jwApply.xyr_sfz_id),
new SqlParameter("@xyr_address", jwApply.xyr_address == null?"":jwApply.xyr_address)
};
try
{
int r = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars);
if (r > 0)
{
return(r);
}
else
{
return(-2);
}
}
catch (Exception)
{
return(-2);
}
}
