/// <summary>
/// 人员进入 保存
/// </summary>
/// <param name="apply_id"></param>
/// <param name="user_id"></param>
/// <param name="unit_id"></param>
/// <returns></returns>
public int SubmitCheckInForm(JW_Apply_room jwApplyRoom, string unit_id, JW_Apply jwApply)
{
//先判断JW_Apply_room表中是否有该apply_id的记录
string checksql = string.Format(@"select * from JW_Apply_room where apply_id='{0}'", jwApplyRoom.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}'", jwApplyRoom.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;
//}
//获取监居区所在单位的主键
string sqlGetAreaUnit = string.Format(@"select bu.*,ja.PoliceArea_id from Base_Unit bu
join JW_Apply ja on bu.Base_Unit_id=ja.unit_id
where ja.apply_id='{0}'", jwApplyRoom.apply_id);
string policeAreaId = SqlHelper.DataTable(sqlGetAreaUnit, CommandType.Text).Rows[0]["PoliceArea_id"].ToString();
string unitId = SqlHelper.DataTable(sqlGetAreaUnit, CommandType.Text).Rows[0]["Base_Unit_id"].ToString();
//插入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 where apply_id=@apply_id;
//update JW_SendPolice set state=2 where type=3 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)
// };
//插入JW_Apply_room，更新JW_Apply状态，更新JW_SendPolice的状态
string sql = string.Format(@"insert into JW_Apply_room(apply_room_id,unit_id,apply_id,adduser_id,adddate,Room_id,startdate,state)
values(NEWID(),@unit_id,@apply_id,@adduser_id,@adddate,@Room_id ,@startdate,@state);
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=3 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", unitId),
new SqlParameter("@apply_id", jwApplyRoom.apply_id),
new SqlParameter("@adduser_id", jwApplyRoom.adduser_id),
new SqlParameter("@adddate", jwApplyRoom.adddate),
new SqlParameter("@Room_id", jwApplyRoom.Room_id),
new SqlParameter("@startdate", jwApplyRoom.startdate),
new SqlParameter("@state", jwApplyRoom.state),
new SqlParameter("@PoliceArea_id", policeAreaId),
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);
}
}
/// <summary>
/// 人员进入 保存
/// </summary>
/// <param name="apply_id"></param>
/// <param name="user_id"></param>
/// <param name="unit_id"></param>
/// <returns></returns>
public int SubmitCheckInForm(JW_Apply_room jwApplyRoom, string unit_id)
{
//先判断JW_Apply_room表中是否有该apply_id的记录
DataTable dt = new DataTable();
string checksql = string.Format(@" select * from JW_Apply_room where apply_id='{0}' and enddate is null ", jwApplyRoom.apply_id);
try
{
dt = SqlHelper.DataTable(checksql, CommandType.Text);
if (dt.Rows.Count == 1)
{
//有数据
if (dt.Rows[0]["state"].ToString() != "1")
{
return(Convert.ToInt32(dt.Rows[0]["state"].ToString()));
}
}
else
{
//数据异常
return(-2);
}
}
catch (Exception)
{
//数据异常
return(-2);
}
//获取监居区所在单位的主键
string sqlGetAreaUnit = string.Format(@"select bu.*,ja.PoliceArea_id from Base_Unit bu
join JW_Apply ja on bu.Base_Unit_id=ja.unit_id
where ja.apply_id='{0}'", jwApplyRoom.apply_id);
string policeAreaId = SqlHelper.DataTable(sqlGetAreaUnit, CommandType.Text).Rows[0]["PoliceArea_id"].ToString();
string unitId = SqlHelper.DataTable(sqlGetAreaUnit, CommandType.Text).Rows[0]["Base_Unit_id"].ToString();
//1.更新JW_Apply_room; 2.插入JW_Apply_room； 场所安全检查
string sql = string.Format(@"
update JW_Apply_room set enddate=@startdate,state=6 where apply_room_id=@apply_room_id;
insert into JW_Apply_room(apply_room_id,unit_id,apply_id,adduser_id,adddate,Room_id,startdate,state)
values(NEWID(),@unit_id,@apply_id,@adduser_id,@adddate,@Room_id ,@startdate,@state);
--update JW_SendPolice set state=2 where type=3 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("@apply_room_id", dt.Rows[0]["apply_room_id"].ToString()),
new SqlParameter("@unit_id", unitId),
new SqlParameter("@apply_id", jwApplyRoom.apply_id),
new SqlParameter("@adduser_id", jwApplyRoom.adduser_id),
new SqlParameter("@adddate", jwApplyRoom.adddate),
new SqlParameter("@Room_id", jwApplyRoom.Room_id),
new SqlParameter("@startdate", jwApplyRoom.startdate),
new SqlParameter("@state", jwApplyRoom.state),
new SqlParameter("@PoliceArea_id", policeAreaId)
};
try
{
int r = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars);
if (r > 0)
{
return(1);
}
else
{
return(-2);
}
}
catch (Exception)
{
return(-2);
}
}
