/// <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); } }