/// <summary> /// 人员离开 /// </summary> /// <param name="jwSecurityCheck"></param> /// <returns></returns> public int SubmitLeaveForm(JW_Apply jwApply, string user_id, string unit_id, string fact_outdate) { string sql = string.Format(@"update JW_Apply set fact_outdate=@fact_outdate,Whereabouts=@Whereabouts,state=@state where apply_id=@apply_id; update JW_Usedetail set isend=1, enddate=@enddate where apply_id=@apply_id; --update JW_SendPolice set state=3 where type=1 and Object_id=@apply_id --and user_id=@user_id "); SqlParameter[] pars = new SqlParameter[] { new SqlParameter("@apply_id", jwApply.apply_id), new SqlParameter("@fact_outdate", jwApply.fact_outdate == null?(object)DBNull.Value:jwApply.fact_outdate), new SqlParameter("@Whereabouts", jwApply.Whereabouts), new SqlParameter("@state", jwApply.state), new SqlParameter("@user_id", user_id), new SqlParameter("@enddate", fact_outdate) }; try { int r = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); return(r); } catch (Exception) { return(0); } }
/// <summary> /// 回退 /// </summary> /// <param name="jwApply"></param> /// <param name="submitType"></param> /// <returns></returns> public string BackApplyForm(JW_Apply jwApply, string sendPolice_id) { #region 回退 //string sql = string.Format(@"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userType=@userType,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); string sql = string.Format(@"update JW_Apply set depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,state=@state where apply_id=@apply_id"); SqlParameter[] pars = new SqlParameter[] { //new SqlParameter("@type",jwApply.type), //new SqlParameter("@case_id",jwApply.case_id), //new SqlParameter("@unit_id",jwApply.unit_id), //new SqlParameter("@unitname",jwApply.unitname), new SqlParameter("@dep_id", jwApply.dep_id), new SqlParameter("@depname", jwApply.depname), new SqlParameter("@asker_id", jwApply.asker_id), new SqlParameter("@PoliceArea_id", jwApply.PoliceArea_id), new SqlParameter("@adduser_id", jwApply.adduser_id), new SqlParameter("@adddate", jwApply.adddate == null?(object)DBNull.Value:jwApply.adddate), new SqlParameter("@startdate", jwApply.startdate == null?(object)DBNull.Value:jwApply.startdate), new SqlParameter("@enddate", jwApply.enddate == null?(object)DBNull.Value:jwApply.enddate), new SqlParameter("@docCode", jwApply.docCode), new SqlParameter("@roomdetail", jwApply.roomdetail), new SqlParameter("@userName", jwApply.userName), new SqlParameter("@userSex", jwApply.userSex == null?(object)DBNull.Value:jwApply.userSex), new SqlParameter("@userAge", jwApply.userAge), new SqlParameter("@userCode", jwApply.userCode), new SqlParameter("@userNation", jwApply.userNation), //new SqlParameter("@userType",jwApply.userType), new SqlParameter("@userPoliticalstatus", jwApply.userPoliticalstatus), new SqlParameter("@userIsNPCmember", jwApply.userIsNPCmember), new SqlParameter("@userEducation", jwApply.userEducation), new SqlParameter("@userWork", jwApply.userWork), new SqlParameter("@userDuty", jwApply.userDuty), new SqlParameter("@userHome", jwApply.userHome), new SqlParameter("@userHealthy", jwApply.userHealthy), new SqlParameter("@detail", jwApply.detail), new SqlParameter("@ApprovalDetail", jwApply.ApprovalDetail), new SqlParameter("@fjuser_id", jwApply.fjuser_id), new SqlParameter("@fjdate", jwApply.fjdate == null?(object)DBNull.Value:jwApply.fjdate), new SqlParameter("@fjdetail", jwApply.fjdetail), //new SqlParameter("@leaderuser_id",jwApply.leaderuser_id), //new SqlParameter("@leaderdate",jwApply.leaderdate==null?(object)DBNull.Value:jwApply.leaderdate), //new SqlParameter("@leaderdetail",jwApply.leaderdetail), new SqlParameter("@state", "-1"), new SqlParameter("@apply_id", jwApply.apply_id) }; try { int r1 = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); } catch (Exception) { return("数据异常,回退失败"); } #endregion return("回退成功"); }
/// <summary> /// 确定审批 /// </summary> /// <param name="jwApply"></param> /// <param name="submitType"></param> /// <returns></returns> public int SubmitJW(JW_Apply jwApply) { string sql = string.Format(@"update JW_Apply set leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); SqlParameter[] pars = new SqlParameter[] { //new SqlParameter("@type",jwApply.type), //new SqlParameter("@case_id",jwApply.case_id), //new SqlParameter("@unit_id",jwApply.unit_id), //new SqlParameter("@unitname",jwApply.unitname), //new SqlParameter("@dep_id",jwApply.dep_id), //new SqlParameter("@depname",jwApply.depname), //new SqlParameter("@asker_id",jwApply.asker_id), //new SqlParameter("@PoliceArea_id",jwApply.PoliceArea_id), //new SqlParameter("@adduser_id",jwApply.adduser_id), //new SqlParameter("@adddate",jwApply.adddate==null?(object)DBNull.Value:jwApply.adddate), //new SqlParameter("@startdate",jwApply.startdate==null?(object)DBNull.Value:jwApply.startdate), //new SqlParameter("@enddate",jwApply.enddate==null?(object)DBNull.Value:jwApply.enddate), //new SqlParameter("@docCode",jwApply.docCode), //new SqlParameter("@roomdetail",jwApply.roomdetail), //new SqlParameter("@userName",jwApply.userName), //new SqlParameter("@userSex",jwApply.userSex==null?(object)DBNull.Value:jwApply.userSex), //new SqlParameter("@userAge",jwApply.userAge), //new SqlParameter("@userCode",jwApply.userCode), //new SqlParameter("@userNation",jwApply.userNation), //new SqlParameter("@userType",jwApply.userType), //new SqlParameter("@userPoliticalstatus",jwApply.userPoliticalstatus), //new SqlParameter("@userIsNPCmember",jwApply.userIsNPCmember), //new SqlParameter("@userEducation",jwApply.userEducation), //new SqlParameter("@userWork",jwApply.userWork), //new SqlParameter("@userDuty",jwApply.userDuty), //new SqlParameter("@userHome",jwApply.userHome), //new SqlParameter("@userHealthy",jwApply.userHealthy), //new SqlParameter("@detail",jwApply.detail), //new SqlParameter("@ApprovalDetail",jwApply.ApprovalDetail), //new SqlParameter("@fjuser_id",jwApply.fjuser_id), //new SqlParameter("@fjdate",jwApply.fjdate==null?(object)DBNull.Value:jwApply.fjdate), //new SqlParameter("@fjdetail",jwApply.fjdetail), new SqlParameter("@leaderuser_id", jwApply.leaderuser_id), new SqlParameter("@leaderdate", jwApply.leaderdate == null?(object)DBNull.Value:jwApply.leaderdate), new SqlParameter("@leaderdetail", jwApply.leaderdetail), new SqlParameter("@state", jwApply.state), new SqlParameter("@apply_id", jwApply.apply_id) }; try { int r = SqlHelper.ExecuteNonQuery(sql, 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); } }
/// <summary> /// 确定修改 /// </summary> /// <param name="jwApply"></param> /// <param name="submitType"></param> /// <returns></returns> public string SubmitJW(JW_Apply jwApply, string sendPolice_id) { #region 审批:现在对以前的申请表不做任何的处理;此段代码已注销 //string sql = string.Format(@"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userType=@userType,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); //SqlParameter[] pars = new SqlParameter[] // { // new SqlParameter("@type",jwApply.type), // new SqlParameter("@case_id",jwApply.case_id), // new SqlParameter("@unit_id",jwApply.unit_id), // new SqlParameter("@unitname",jwApply.unitname), // new SqlParameter("@dep_id",jwApply.dep_id), // new SqlParameter("@depname",jwApply.depname), // new SqlParameter("@asker_id",jwApply.asker_id), // new SqlParameter("@PoliceArea_id",jwApply.PoliceArea_id), // new SqlParameter("@adduser_id",jwApply.adduser_id), // new SqlParameter("@adddate",jwApply.adddate==null?(object)DBNull.Value:jwApply.adddate), // new SqlParameter("@startdate",jwApply.startdate==null?(object)DBNull.Value:jwApply.startdate), // new SqlParameter("@enddate",jwApply.enddate==null?(object)DBNull.Value:jwApply.enddate), // new SqlParameter("@docCode",jwApply.docCode), // new SqlParameter("@roomdetail",jwApply.roomdetail), // new SqlParameter("@userName",jwApply.userName), // new SqlParameter("@userSex",jwApply.userSex==null?(object)DBNull.Value:jwApply.userSex), // new SqlParameter("@userAge",jwApply.userAge), // new SqlParameter("@userCode",jwApply.userCode), // new SqlParameter("@userNation",jwApply.userNation), // new SqlParameter("@userType",jwApply.userType), // //new SqlParameter("@userbiref",jwApply.userbiref), // new SqlParameter("@userPoliticalstatus",jwApply.userPoliticalstatus), // new SqlParameter("@userIsNPCmember",jwApply.userIsNPCmember), // new SqlParameter("@userEducation",jwApply.userEducation), // new SqlParameter("@userWork",jwApply.userWork), // new SqlParameter("@userDuty",jwApply.userDuty), // new SqlParameter("@userHome",jwApply.userHome), // new SqlParameter("@userHealthy",jwApply.userHealthy), // //new SqlParameter("@remark",jwApply.remark), // //new SqlParameter("@usercardcode",jwApply.usercardcode), // new SqlParameter("@detail",jwApply.detail), // new SqlParameter("@ApprovalDetail",jwApply.ApprovalDetail), // new SqlParameter("@fjuser_id",jwApply.fjuser_id), // new SqlParameter("@fjdate",jwApply.fjdate==null?(object)DBNull.Value:jwApply.fjdate), // new SqlParameter("@fjdetail",jwApply.fjdetail), // new SqlParameter("@leaderuser_id",jwApply.leaderuser_id), // new SqlParameter("@leaderdate",jwApply.leaderdate==null?(object)DBNull.Value:jwApply.leaderdate), // new SqlParameter("@leaderdetail",jwApply.leaderdetail), // new SqlParameter("@state",jwApply.state), // new SqlParameter("@apply_id",jwApply.apply_id) // }; //try //{ // int r1 = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); //} //catch (Exception) //{ // return "数据异常,审批失败"; //} #endregion if (sendPolice_id != "" && sendPolice_id != " ") { //对派警的处理 #region 除;此段代码已注销 // //1.先删除原来的数据 // string sqlDel = string.Format(@" // delete JW_SendPolice where Unit_id='{0}' and SendUser_id='{1}' and type='{2}' and Object_id='{3}'; // " // , jwApply.unit_id // , jwApply.adduser_id // , "1" // , jwApply.apply_id // ); // sqlDel = string.Format(@" // declare @errorNumber int=0 // begin tran // {0} // set @errorNumber+=@@ERROR // if(@errorNumber>0) // begin // rollback tran // end // else // begin // commit tran // end // " // , sqlDel.ToString() // ); // try // { // int r2 = SqlHelper.ExecuteNonQuery(sqlDel, CommandType.Text); // } // catch (Exception) // { // return "数据异常,派警失败"; // } // //2.先拿到当前单位的简称 // string sqlGetUnit = string.Format(@" select * from Base_Unit where Base_Unit_id='{0}' ", jwApply.unit_id); // string unitShotName = SqlHelper.DataTable(sqlGetUnit, CommandType.Text).Rows[0]["longname"].ToString(); // //3.拿到数据库中当前单位的最大的流水号 // string code = string.Empty; // string sqlGetCode = // string.Format( // @" select MAX(CONVERT(int,SUBSTRING(SendCode,6,7))) code from JW_SendPolice where Unit_id='{0}' ", // jwApply.unit_id); // DataTable dtCode = SqlHelper.DataTable(sqlGetCode, CommandType.Text); // if (dtCode == null || dtCode.Rows.Count <= 0 || dtCode.Rows[0]["code"].ToString() == string.Empty) // { // code = DateTime.Now.Year.ToString() + "001"; // } // else // { // string year = dtCode.Rows[0]["code"].ToString().Substring(0, 4); // string co = dtCode.Rows[0]["code"].ToString().Substring(4, 3); // if (year == DateTime.Now.Year.ToString()) // { // code = (Convert.ToInt32(year + co) + 1).ToString(); // } // else // { // code = DateTime.Now.Year.ToString() + "001"; // } // } #endregion #region 添加;此段代码已注销 // StringBuilder sb = new StringBuilder(); // //再添加新的数据 // if (!sendPolice_id.Contains(",")) // { // //只选择了一个人 // sb.AppendFormat( // @" insert into JW_SendPolice(SendPolice_id,Unit_id,SendCode,SendUser_id,SendDate,type,Object_id,user_id,state) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'); " // , jwApply.unit_id // , unitShotName + "检警派【" + code + "】号" // , jwApply.adduser_id // , jwApply.adddate // , "1" // , jwApply.apply_id // , sendPolice_id // , "0" // ); // } // else // { // //选择了多个人 // string[] ids = sendPolice_id.Split(','); // for (int i = 0; i < ids.Length; i++) // { // sb.AppendFormat( // @" insert into JW_SendPolice(SendPolice_id,Unit_id,SendCode,SendUser_id,SendDate,type,Object_id,user_id,state) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'); " // , jwApply.unit_id // , unitShotName + "检警派【" + (Convert.ToInt32(code) + i) + "】号" // , jwApply.adduser_id // , jwApply.adddate // , "1" // , jwApply.apply_id // , ids[i] // , "0" // ); // } // } // string sqlInsert = string.Format(@" // declare @errorNumber int=0 // begin tran // {0} // set @errorNumber+=@@ERROR // if(@errorNumber>0) // begin // rollback tran // end // else // begin // commit tran // end // " // , sb.ToString() // ); // try // { // int r3 = SqlHelper.ExecuteNonQuery(sqlInsert, CommandType.Text); // } // catch (Exception) // { // return "数据异常,派警失败"; // } #endregion #region 新的逻辑处理代码 try { //1.先获取数据库中已经派警的数据记录(法警的主键,派警的状态) //List<string> oldPoliceList = new List<string>(); Dictionary <string, string> oldPoliceDic = new Dictionary <string, string>(); string sqlOldPolice = string.Format(@"select * from JW_SendPolice where Object_id='{0}'", jwApply.apply_id); DataTable dtOldPolice = SqlHelper.DataTable(sqlOldPolice, CommandType.Text); foreach (DataRow row in dtOldPolice.Rows) { //oldPoliceList.Add(row["user_id"] + "," + row["state"]); oldPoliceDic.Add(row["user_id"].ToString(), row["state"].ToString()); } //2.获取新传递过来的派警的数据(法警的主键) List <string> newPoliceList = new List <string>(); if (!sendPolice_id.Contains(",")) { newPoliceList.Add(sendPolice_id); } else { string[] ids = sendPolice_id.Split(','); newPoliceList = ids.ToList(); } //逻辑处理:新旧进行对比 foreach (string oldPolice in oldPoliceDic.Keys) { //1.数据库中的人不在新的选择的人中,需要检测当前数据库中这个人的状态 //如果这个人的状态是 已实施 的话,那么状态变更成 实施完成,其他的状态 if (!newPoliceList.Contains(oldPolice)) { //选择的人不在数据库中,对数据库中的人进行的处理 string sqlProOldPolice = string.Empty; if (oldPoliceDic[oldPolice].ToString() == "2") //已实施 → 已完成 { sqlProOldPolice = string.Format( @"update JW_SendPolice set state = 3 where Object_id='{0}' and user_id='{1}' ", jwApply.apply_id, oldPolice); SqlHelper.ExecuteNonQuery(sqlProOldPolice, CommandType.Text); } else if (oldPoliceDic[oldPolice].ToString() == "1") //已确认待实施 → 取消任务 { sqlProOldPolice = string.Format( @"update JW_SendPolice set state = -2 where Object_id='{0}' and user_id='{1}' ", jwApply.apply_id, oldPolice); SqlHelper.ExecuteNonQuery(sqlProOldPolice, CommandType.Text); } else if (oldPoliceDic[oldPolice].ToString() == "0") //待确认 → 删除 { sqlProOldPolice = string.Format(@"delete JW_SendPolice where Object_id='{0}' and user_id='{1}'", jwApply.apply_id, oldPolice); SqlHelper.ExecuteNonQuery(sqlProOldPolice, CommandType.Text); } } else { //如果选择的人,同时也是数据库中 string sqlProOldPolice = string.Empty; if (oldPoliceDic[oldPolice].ToString() == "3" || oldPoliceDic[oldPolice].ToString() == "-1" || oldPoliceDic[oldPolice].ToString() == "-2") //实施完成、确认退回、取消任务 → 待确认 { sqlProOldPolice = string.Format( @"update JW_SendPolice set state = 0 where Object_id='{0}' and user_id='{1}' ", jwApply.apply_id, oldPolice); SqlHelper.ExecuteNonQuery(sqlProOldPolice, CommandType.Text); } } } foreach (string newPolice in newPoliceList) { //2.新选择的人不在数据库中,插入数据库中 if (!oldPoliceDic.Keys.Contains(newPolice)) { #region MyRegion //先拿到当前单位的简称 string sqlGetUnit = string.Format(@" select * from Base_Unit where Base_Unit_id='{0}' ", jwApply.unit_id); string unitShotName = SqlHelper.DataTable(sqlGetUnit, CommandType.Text).Rows[0]["longname"].ToString(); //拿到数据库中当前单位的最大的流水号 string code = string.Empty; string sqlGetCode = string.Format( @" select MAX(CONVERT(int,SUBSTRING(SendCode,6,7))) code from JW_SendPolice where Unit_id='{0}' ", jwApply.unit_id); DataTable dtCode = SqlHelper.DataTable(sqlGetCode, CommandType.Text); if (dtCode == null || dtCode.Rows.Count <= 0 || dtCode.Rows[0]["code"].ToString() == string.Empty) { code = DateTime.Now.Year.ToString() + "001"; } else { string year = dtCode.Rows[0]["code"].ToString().Substring(0, 4); string co = dtCode.Rows[0]["code"].ToString().Substring(4, 3); if (year == DateTime.Now.Year.ToString()) { code = (Convert.ToInt32(year + co) + 1).ToString(); } else { code = DateTime.Now.Year.ToString() + "001"; } } #endregion string sqlInsertNewPolice = string.Format(@"insert into JW_SendPolice(SendPolice_id,Unit_id,SendCode,SendUser_id,SendDate,type,Object_id,user_id,state) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", jwApply.unit_id, unitShotName + "检警派【" + code + "】号", jwApply.adduser_id, jwApply.adddate, jwApply.type, jwApply.apply_id, newPolice, "0"); SqlHelper.ExecuteNonQuery(sqlInsertNewPolice, CommandType.Text); } } return("1"); } catch (Exception) { //失败 return("0"); } #endregion } else { //失败 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_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="keyValue"></param> /// <returns></returns> public int BackApplyForm(JW_Apply jwApply) { //string sql = string.Format(@" update JW_Apply set state=-2 where apply_id='{0}' ", keyValue); //try //{ // int r = SqlHelper.ExecuteNonQuery(sql, CommandType.Text); // return r; //} //catch (Exception) //{ // return 0; //} string sql = string.Format(@"update JW_Apply set leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); SqlParameter[] pars = new SqlParameter[] { //new SqlParameter("@type",jwApply.type), //new SqlParameter("@case_id",jwApply.case_id), //new SqlParameter("@unit_id",jwApply.unit_id), //new SqlParameter("@unitname",jwApply.unitname), //new SqlParameter("@dep_id",jwApply.dep_id), //new SqlParameter("@depname",jwApply.depname), //new SqlParameter("@asker_id",jwApply.asker_id), //new SqlParameter("@PoliceArea_id",jwApply.PoliceArea_id), //new SqlParameter("@adduser_id",jwApply.adduser_id), //new SqlParameter("@adddate",jwApply.adddate==null?(object)DBNull.Value:jwApply.adddate), //new SqlParameter("@startdate",jwApply.startdate==null?(object)DBNull.Value:jwApply.startdate), //new SqlParameter("@enddate",jwApply.enddate==null?(object)DBNull.Value:jwApply.enddate), //new SqlParameter("@docCode",jwApply.docCode), //new SqlParameter("@roomdetail",jwApply.roomdetail), //new SqlParameter("@userName",jwApply.userName), //new SqlParameter("@userSex",jwApply.userSex==null?(object)DBNull.Value:jwApply.userSex), //new SqlParameter("@userAge",jwApply.userAge), //new SqlParameter("@userCode",jwApply.userCode), //new SqlParameter("@userNation",jwApply.userNation), //new SqlParameter("@userType",jwApply.userType), //new SqlParameter("@userPoliticalstatus",jwApply.userPoliticalstatus), //new SqlParameter("@userIsNPCmember",jwApply.userIsNPCmember), //new SqlParameter("@userEducation",jwApply.userEducation), //new SqlParameter("@userWork",jwApply.userWork), //new SqlParameter("@userDuty",jwApply.userDuty), //new SqlParameter("@userHome",jwApply.userHome), //new SqlParameter("@userHealthy",jwApply.userHealthy), //new SqlParameter("@detail",jwApply.detail), //new SqlParameter("@ApprovalDetail",jwApply.ApprovalDetail), //new SqlParameter("@fjuser_id",jwApply.fjuser_id), //new SqlParameter("@fjdate",jwApply.fjdate==null?(object)DBNull.Value:jwApply.fjdate), //new SqlParameter("@fjdetail",jwApply.fjdetail), new SqlParameter("@leaderuser_id", jwApply.leaderuser_id), new SqlParameter("@leaderdate", jwApply.leaderdate == null?(object)DBNull.Value:jwApply.leaderdate), new SqlParameter("@leaderdetail", jwApply.leaderdetail), new SqlParameter("@state", "-2"), new SqlParameter("@apply_id", jwApply.apply_id) }; try { int r = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); } catch (Exception) { return(0); } //删除派警单 string sqlDel = string.Format(@"delete JW_SendPolice where type=1 and Object_id='{0}'", jwApply.apply_id); try { int r = SqlHelper.ExecuteNonQuery(sqlDel, CommandType.Text); } catch (Exception) { return(0); } return(1); }
/// <summary> /// 确定审批 /// </summary> /// <param name="jwApply"></param> /// <param name="submitType"></param> /// <returns></returns> public string SubmitJW(JW_Apply jwApply, string sendPolice_id) { #region 审批 if (jwApply.type == 1 || jwApply.type == 2) { //string sql = // string.Format( // @"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userType=@userType,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); string sql = string.Format( @"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userType=@userType,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,state=@state where apply_id=@apply_id"); SqlParameter[] pars = new SqlParameter[] { new SqlParameter("@type", jwApply.type), new SqlParameter("@case_id", jwApply.case_id), new SqlParameter("@unit_id", jwApply.unit_id), new SqlParameter("@unitname", jwApply.unitname), new SqlParameter("@dep_id", jwApply.dep_id), new SqlParameter("@depname", jwApply.depname), new SqlParameter("@asker_id", jwApply.asker_id), new SqlParameter("@PoliceArea_id", jwApply.PoliceArea_id), new SqlParameter("@adduser_id", jwApply.adduser_id), new SqlParameter("@adddate", jwApply.adddate == null ? (object)DBNull.Value : jwApply.adddate), new SqlParameter("@startdate", jwApply.startdate == null ? (object)DBNull.Value : jwApply.startdate), new SqlParameter("@enddate", jwApply.enddate == null ? (object)DBNull.Value : jwApply.enddate), new SqlParameter("@docCode", jwApply.docCode), new SqlParameter("@roomdetail", jwApply.roomdetail), new SqlParameter("@userName", jwApply.userName), new SqlParameter("@userSex", jwApply.userSex == null ? (object)DBNull.Value : jwApply.userSex), new SqlParameter("@userAge", jwApply.userAge), new SqlParameter("@userCode", jwApply.userCode), new SqlParameter("@userNation", jwApply.userNation), new SqlParameter("@userType", jwApply.userType), new SqlParameter("@userPoliticalstatus", jwApply.userPoliticalstatus), new SqlParameter("@userIsNPCmember", jwApply.userIsNPCmember), new SqlParameter("@userEducation", jwApply.userEducation), new SqlParameter("@userWork", jwApply.userWork), new SqlParameter("@userDuty", jwApply.userDuty), new SqlParameter("@userHome", jwApply.userHome), new SqlParameter("@userHealthy", jwApply.userHealthy), new SqlParameter("@detail", jwApply.detail), new SqlParameter("@ApprovalDetail", jwApply.ApprovalDetail), new SqlParameter("@fjuser_id", jwApply.fjuser_id), new SqlParameter("@fjdate", jwApply.fjdate == null ? (object)DBNull.Value : jwApply.fjdate), new SqlParameter("@fjdetail", jwApply.fjdetail), //new SqlParameter("@leaderuser_id", jwApply.leaderuser_id), //new SqlParameter("@leaderdate",jwApply.leaderdate == null ? (object) DBNull.Value : jwApply.leaderdate), //new SqlParameter("@leaderdetail", jwApply.leaderdetail), new SqlParameter("@state", jwApply.state), new SqlParameter("@apply_id", jwApply.apply_id) }; try { int r1 = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); } catch (Exception) { return("数据异常,审批失败"); } } else { //string sql = string.Format(@"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userbiref=@userbiref,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,remark=@remark,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,leaderuser_id=@leaderuser_id,leaderdate=@leaderdate,leaderdetail=@leaderdetail,state=@state where apply_id=@apply_id"); string sql = string.Format(@"update JW_Apply set type=@type,case_id=@case_id,unit_id=@unit_id,unitname=@unitname,dep_id=@dep_id,depname=@depname,asker_id=@asker_id,PoliceArea_id=@PoliceArea_id,adduser_id=@adduser_id,adddate=@adddate,startdate=@startdate,enddate=@enddate,docCode=@docCode,roomdetail=@roomdetail,userName=@userName,userSex=@userSex,userAge=@userAge,userCode=@userCode,userNation=@userNation,userbiref=@userbiref,userPoliticalstatus=@userPoliticalstatus,userIsNPCmember=@userIsNPCmember,userEducation=@userEducation,userWork=@userWork,userDuty=@userDuty,userHome=@userHome,userHealthy=@userHealthy,remark=@remark,detail=@detail,ApprovalDetail=@ApprovalDetail,fjuser_id=@fjuser_id,fjdate=@fjdate,fjdetail=@fjdetail,state=@state where apply_id=@apply_id"); SqlParameter[] pars = new SqlParameter[] { new SqlParameter("@type", jwApply.type), new SqlParameter("@case_id", jwApply.case_id), new SqlParameter("@unit_id", jwApply.unit_id), new SqlParameter("@unitname", jwApply.unitname), new SqlParameter("@dep_id", jwApply.dep_id), new SqlParameter("@depname", jwApply.depname), new SqlParameter("@asker_id", jwApply.asker_id), new SqlParameter("@PoliceArea_id", jwApply.PoliceArea_id), new SqlParameter("@adduser_id", jwApply.adduser_id), new SqlParameter("@adddate", jwApply.adddate == null?(object)DBNull.Value:jwApply.adddate), new SqlParameter("@startdate", jwApply.startdate == null?(object)DBNull.Value:jwApply.startdate), new SqlParameter("@enddate", jwApply.enddate == null?(object)DBNull.Value:jwApply.enddate), new SqlParameter("@docCode", jwApply.docCode), new SqlParameter("@roomdetail", jwApply.roomdetail), new SqlParameter("@userName", jwApply.userName), new SqlParameter("@userSex", jwApply.userSex == null?(object)DBNull.Value:jwApply.userSex), new SqlParameter("@userAge", jwApply.userAge), new SqlParameter("@userCode", jwApply.userCode), new SqlParameter("@userNation", jwApply.userNation), new SqlParameter("@userbiref", jwApply.userbiref), new SqlParameter("@userPoliticalstatus", jwApply.userPoliticalstatus), new SqlParameter("@userIsNPCmember", jwApply.userIsNPCmember), new SqlParameter("@userEducation", jwApply.userEducation), new SqlParameter("@userWork", jwApply.userWork), new SqlParameter("@userDuty", jwApply.userDuty), new SqlParameter("@userHome", jwApply.userHome), new SqlParameter("@userHealthy", jwApply.userHealthy), new SqlParameter("@remark", jwApply.remark), new SqlParameter("@detail", jwApply.detail), new SqlParameter("@ApprovalDetail", jwApply.ApprovalDetail), new SqlParameter("@fjuser_id", jwApply.fjuser_id), new SqlParameter("@fjdate", jwApply.fjdate == null?(object)DBNull.Value:jwApply.fjdate), new SqlParameter("@fjdetail", jwApply.fjdetail), //new SqlParameter("@leaderuser_id",jwApply.leaderuser_id), //new SqlParameter("@leaderdate",jwApply.leaderdate==null?(object)DBNull.Value:jwApply.leaderdate), //new SqlParameter("@leaderdetail",jwApply.leaderdetail), new SqlParameter("@state", jwApply.state), new SqlParameter("@apply_id", jwApply.apply_id) }; try { int r1 = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pars); } catch (Exception) { return("数据异常,审批失败"); } } #endregion if (sendPolice_id != "" && sendPolice_id != " ") { //对派警的处理 #region 除 //1.先删除原来的数据 // string sqlDel = string.Format(@" // delete JW_SendPolice where Unit_id='{0}' and SendUser_id='{1}' and type='{2}' and Object_id='{3}'; // " // , jwApply.unit_id // , jwApply.fjuser_id // , jwApply.type // , jwApply.apply_id // ); string sqlDel = string.Format(@" delete JW_SendPolice where Unit_id='{0}' and type='{2}' and Object_id='{3}'; " , jwApply.unit_id , jwApply.fjuser_id , jwApply.type , jwApply.apply_id ); //2017.2.28---jwApply.addUser_id改为jwApply.fjuser_id sqlDel = string.Format(@" declare @errorNumber int=0 begin tran {0} set @errorNumber+=@@ERROR if(@errorNumber>0) begin rollback tran end else begin commit tran end " , sqlDel.ToString() ); try { int r2 = SqlHelper.ExecuteNonQuery(sqlDel, CommandType.Text); } catch (Exception) { return("数据异常,派警失败"); } //2.先拿到当前单位的简称 string sqlGetUnit = string.Format(@" select * from Base_Unit where Base_Unit_id='{0}' ", jwApply.unit_id); string unitShotName = SqlHelper.DataTable(sqlGetUnit, CommandType.Text).Rows[0]["longname"].ToString(); //3.拿到数据库中当前单位的最大的流水号 string code = string.Empty; string sqlGetCode = string.Format( @" select MAX(CONVERT(int,SUBSTRING(SendCode,6,7))) code from JW_SendPolice where Unit_id='{0}' ", jwApply.unit_id); DataTable dtCode = SqlHelper.DataTable(sqlGetCode, CommandType.Text); if (dtCode == null || dtCode.Rows.Count <= 0 || dtCode.Rows[0]["code"].ToString() == string.Empty) { code = DateTime.Now.Year.ToString() + "001"; } else { string year = dtCode.Rows[0]["code"].ToString().Substring(0, 4); string co = dtCode.Rows[0]["code"].ToString().Substring(4, 3); if (year == DateTime.Now.Year.ToString()) { code = (Convert.ToInt32(year + co) + 1).ToString(); } else { code = DateTime.Now.Year.ToString() + "001"; } } #endregion #region 添加 StringBuilder sb = new StringBuilder(); //再添加新的数据 if (!sendPolice_id.Contains(",")) { //只选择了一个人 sb.AppendFormat( @" insert into JW_SendPolice(SendPolice_id,Unit_id,SendCode,SendUser_id,SendDate,type,Object_id,user_id,state) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'); " , jwApply.unit_id , unitShotName + "检警派【" + code + "】号" , jwApply.fjuser_id , jwApply.adddate , jwApply.type , jwApply.apply_id , sendPolice_id , "0" ); //2017.2.28---jwApply.addUser_id改为jwApply.fjuser_id } else { //选择了多个人 string[] ids = sendPolice_id.Split(','); for (int i = 0; i < ids.Length; i++) { sb.AppendFormat( @" insert into JW_SendPolice(SendPolice_id,Unit_id,SendCode,SendUser_id,SendDate,type,Object_id,user_id,state) values(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'); " , jwApply.unit_id , unitShotName + "检警派【" + (Convert.ToInt32(code) + i) + "】号" , jwApply.fjuser_id , jwApply.adddate , jwApply.type , jwApply.apply_id , ids[i] , "0" ); //2017.2.28---jwApply.addUser_id改为jwApply.fjuser_id } } string sqlInsert = string.Format(@" declare @errorNumber int=0 begin tran {0} set @errorNumber+=@@ERROR if(@errorNumber>0) begin rollback tran end else begin commit tran end " , sb.ToString() ); try { int r3 = SqlHelper.ExecuteNonQuery(sqlInsert, CommandType.Text); } catch (Exception) { return("数据异常,派警失败"); } #endregion } return("审批成功"); }