/// <summary> /// 添加记名式交路机组 /// </summary> public InterfaceRet InsertGrp(String Data) { _Ret.Clear(); try { JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer(); InInsertGrp InParams = javaScriptSerializer.Deserialize <InInsertGrp>(Data); //记名式交路机组 RRsNamedGroup NamedGroup = new RRsNamedGroup(); NamedGroup.nCheciOrder = InParams.CheciOrder; NamedGroup.nCheciType = InParams.CheciType; NamedGroup.strCheci1 = InParams.Checi1; NamedGroup.strCheci2 = InParams.Checi2; NamedGroup.strCheciGUID = InParams.CheciGUID; NamedGroup.strTrainmanJiaoluGUID = InParams.TrainmanJiaolu.jiaoluID; NamedGroup.dtLastArriveTime = DateTime.Parse("1899-01-01"); NamedGroup.Group.groupID = Guid.NewGuid().ToString(); DBNamedGroupV2.InsertGrp(InParams.TrainmanJiaolu.jiaoluID, NamedGroup); Group group = NamedGroup.Group; TrainmanList trainmanList = new TrainmanList(); string strContent = string.Format("成功添加一个空的记名式机组"); DBNameBoard.SaveChangeLog(InParams.TrainmanJiaolu, LBoardChangeType.btcAddGroup, strContent, InParams.DutyUser, trainmanList); _Ret.result = 0; } catch (Exception ex) { _Ret.resultStr = ex.Message; _Ret.result = 1; LogClass.log("Interface.AddNamedGroup:" + ex.Message); throw ex; } return(_Ret); }
//从数据行中读取数据到NamedGroup中 public static void NamedGroupFromDB(RRsNamedGroup group, DataRow dr) { GroupFromDB(group.Group, dr); group.dtLastArriveTime = TFParse.DBToDateTime(dr["dtLastArriveTime"], DateTime.Parse("1899-01-01")); group.strCheciGUID = dr["strCheciGUID"].ToString(); group.strTrainmanJiaoluGUID = dr["strTrainmanJiaoluGUID"].ToString(); group.nCheciOrder = TFParse.DBToInt(dr["nCheciOrder"], 0); group.nCheciType = TFParse.DBToInt(dr["nCheciType"], 0); group.strCheci1 = dr["strCheci1"].ToString(); group.strCheci2 = dr["strCheci2"].ToString(); }
/// <summary> /// 插入记名式机组 /// </summary> public InterfaceOutPut InsertGroup(String Data) { InterfaceOutPut output = new InterfaceOutPut(); output.result = 1; try { JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer(); InInsertGroup InParams = javaScriptSerializer.Deserialize <InInsertGroup>(Data); SqlConnection conn = new SqlConnection(SqlHelper.ConnString); conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { //后面的机组顺序移位 string strSql = ""; int nCheciOrder = InParams.NamedGroupOld.nCheciOrder; if (InParams.LeftOrRight == 1) { strSql = "update TAB_Nameplate_TrainmanJiaolu_Named set nCheciOrder = nCheciOrder + 1 where strTrainmanJiaoluGUID = @strTrainmanJiaoluGUID and nCheciOrder >=@nCheciOrder"; } if (InParams.LeftOrRight == 2) { nCheciOrder = nCheciOrder + 1; strSql = "update TAB_Nameplate_TrainmanJiaolu_Named set nCheciOrder = nCheciOrder + 1 where strTrainmanJiaoluGUID = @strTrainmanJiaoluGUID and nCheciOrder > @nCheciOrder"; } SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("strTrainmanJiaoluGUID", InParams.TrainmanJiaoluGUID), new SqlParameter("nCheciOrder", nCheciOrder) }; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParams); RRsNamedGroup namedGroup = InParams.NamedGroupNew; string TrainmanJiaoluGUID = InParams.TrainmanJiaoluGUID; //创建机组 strSql = @"insert into TAB_Nameplate_Group (strGroupGUID,strTrainmanGUID1,strTrainmanGUID2,strTrainmanGUID3,strTrainmanGUID4) values (@strGroupGUID,@strTrainmanGUID1,@strTrainmanGUID2,@strTrainmanGUID3,@strTrainmanGUID4)"; SqlParameter[] sqlParamsGroup = new SqlParameter[] { new SqlParameter("strGroupGUID", namedGroup.Group.groupID), new SqlParameter("strTrainmanGUID1", namedGroup.Group.trainman1.trainmanID), new SqlParameter("strTrainmanGUID2", namedGroup.Group.trainman2.trainmanID), new SqlParameter("strTrainmanGUID3", namedGroup.Group.trainman3.trainmanID), new SqlParameter("strTrainmanGUID4", namedGroup.Group.trainman4.trainmanID) }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsGroup) == 0) { throw new Exception("创建机组错误"); } //获取交路车间信息 strSql = "select strTrainJiaoluGUID,strWorkShopGUID from VIEW_Base_JiaoluRelation where strTrainmanJiaoluGUID = @strTrainmanJiaoluGUID"; SqlParameter[] sqlParamsTMJL = new SqlParameter[] { new SqlParameter("strTrainmanJiaoluGUID", TrainmanJiaoluGUID) }; string trainJiaolu = ""; string workShopGUID = ""; DataTable dtJiaolu = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql, sqlParamsGroup).Tables[0]; if (dtJiaolu.Rows.Count > 0) { trainJiaolu = dtJiaolu.Rows[0]["strTrainJiaoluGUID"].ToString(); workShopGUID = dtJiaolu.Rows[0]["strWorkShopGUID"].ToString(); } //修改人员所属行车区段、车间、状态 if (namedGroup.Group.trainman1.trainmanID != "") { strSql = @"update Tab_Org_Trainman set strTrainJiaoluGUID=@strTrainJiaoluGUID,strWorkShopGUID=@strWorkShopGUID,nTrainmanState = 2 where strTrainmanGUID = @strTrainmanGUID"; SqlParameter[] sqlParamsTM = new SqlParameter[] { new SqlParameter("strTrainJiaoluGUID", trainJiaolu), new SqlParameter("strWorkShopGUID", workShopGUID), new SqlParameter("strTrainmanGUID", namedGroup.Group.trainman1.trainmanID), }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsTM) == 0) { throw new Exception("修改司机状态错误"); } } if (namedGroup.Group.trainman2.trainmanID != "") { strSql = @"update Tab_Org_Trainman set strTrainJiaoluGUID=@strTrainJiaoluGUID,strWorkShopGUID=@strWorkShopGUID,nTrainmanState = 2 where strTrainmanGUID = @strTrainmanGUID"; SqlParameter[] sqlParamsTM = new SqlParameter[] { new SqlParameter("strTrainJiaoluGUID", trainJiaolu), new SqlParameter("strWorkShopGUID", workShopGUID), new SqlParameter("strTrainmanGUID", namedGroup.Group.trainman2.trainmanID), }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsTM) == 0) { throw new Exception("修改副司机状态错误"); } } if (namedGroup.Group.trainman3.trainmanID != "") { strSql = @"update Tab_Org_Trainman set strTrainJiaoluGUID=@strTrainJiaoluGUID,strWorkShopGUID=@strWorkShopGUID,nTrainmanState = 2 where strTrainmanGUID = @strTrainmanGUID"; SqlParameter[] sqlParamsTM = new SqlParameter[] { new SqlParameter("strTrainJiaoluGUID", trainJiaolu), new SqlParameter("strWorkShopGUID", workShopGUID), new SqlParameter("strTrainmanGUID", namedGroup.Group.trainman3.trainmanID), }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsTM) == 0) { throw new Exception("修改学员1状态错误"); } } if (namedGroup.Group.trainman4.trainmanID != "") { strSql = @"update Tab_Org_Trainman set strTrainJiaoluGUID=@strTrainJiaoluGUID,strWorkShopGUID=@strWorkShopGUID,nTrainmanState = 2 where strTrainmanGUID = @strTrainmanGUID"; SqlParameter[] sqlParamsTM = new SqlParameter[] { new SqlParameter("strTrainJiaoluGUID", trainJiaolu), new SqlParameter("strWorkShopGUID", workShopGUID), new SqlParameter("strTrainmanGUID", namedGroup.Group.trainman4.trainmanID), }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsTM) == 0) { throw new Exception("修改学员2状态错误"); } } //添加记名式机组 strSql = @"insert into TAB_Nameplate_TrainmanJiaolu_Named (strCheciGUID,strTrainmanJiaoluGUID,nCheciOrder,nCheciType,strCheci1,strCheci2,strGroupGUID,dtLastArriveTime) (select @CheciGUID,@strTrainmanJiaoluGUID, (case when max(nCheCiOrder) is null then 1 else max(nCheCiOrder) + 1 end) ,@CheciType,@Checi1,@Checi2,@GroupGUID,getdate() from TAB_Nameplate_TrainmanJiaolu_Named where strTrainmanJiaoluGUID=@strTrainmanJiaoluGUID)"; SqlParameter[] sqlParamsNamed = new SqlParameter[] { new SqlParameter("CheciGUID", namedGroup.strCheciGUID), new SqlParameter("strTrainmanJiaoluGUID", namedGroup.strTrainmanJiaoluGUID), new SqlParameter("CheciType", namedGroup.nCheciType), new SqlParameter("Checi1", namedGroup.strCheci1), new SqlParameter("Checi2", namedGroup.strCheci2), new SqlParameter("GroupGUID", namedGroup.Group.groupID), new SqlParameter("strTrainmanJiaoluGUID", namedGroup.strTrainmanJiaoluGUID) }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsNamed) == 0) { throw new Exception("创建记名式机组错误"); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { conn.Close(); } } output.result = 0; } catch (Exception ex) { output.resultStr = ex.Message; LogClass.log("Interface.InsertGroup:" + ex.Message); throw ex; } return(output); }
//添加记名式机组 public static void InsertGrp(string TrainmanJiaoluGUID, RRsNamedGroup namedGroup) { SqlConnection conn = new SqlConnection(SqlHelper.ConnString); conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { //创建机组 string strSql = @"insert into TAB_Nameplate_Group (strGroupGUID,strTrainmanGUID1,strTrainmanGUID2,strTrainmanGUID3,strTrainmanGUID4) values (@strGroupGUID,@strTrainmanGUID1,@strTrainmanGUID2,@strTrainmanGUID3,@strTrainmanGUID4)"; SqlParameter[] sqlParamsGroup = new SqlParameter[] { new SqlParameter("strGroupGUID", namedGroup.Group.groupID), new SqlParameter("strTrainmanGUID1", ""), new SqlParameter("strTrainmanGUID2", ""), new SqlParameter("strTrainmanGUID3", ""), new SqlParameter("strTrainmanGUID4", "") }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsGroup) == 0) { throw new Exception("创建机组错误"); } strSql = " select count(*) from TAB_Nameplate_TrainmanJiaolu_Named where strTrainmanJiaoluGUID='" + namedGroup.strTrainmanJiaoluGUID + "'"; int namedCount = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, strSql)); #region 通过位置找到它在数据库中的实际排序 int nCheciOrder = 0; int nAddOrder = 0; if (namedGroup.nCheciOrder == 1) { strSql = " select top 1 nCheCiOrder from TAB_Nameplate_TrainmanJiaolu_Named where strTrainmanJiaoluGUID='" + namedGroup.strTrainmanJiaoluGUID + "'"; DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql).Tables[0]; if (dt.Rows.Count > 0) { nCheciOrder = Convert.ToInt32(dt.Rows[0]["nCheCiOrder"].ToString()); nAddOrder = nCheciOrder - 1; } } else if (namedGroup.nCheciOrder > 1 && namedGroup.nCheciOrder <= namedCount) { strSql = @" select top " + (namedGroup.nCheciOrder - 1) + " nCheCiOrder from TAB_Nameplate_TrainmanJiaolu_Named where strTrainmanJiaoluGUID='" + namedGroup.strTrainmanJiaoluGUID + "' order by nCheCiOrder"; nCheciOrder = Convert.ToInt32(SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql).Tables[0].Rows[(namedGroup.nCheciOrder - 2)]["nCheCiOrder"].ToString()); nAddOrder = nCheciOrder + 1; } else if (namedGroup.nCheciOrder > namedCount) { strSql = " select top 1 nCheCiOrder from TAB_Nameplate_TrainmanJiaolu_Named where strTrainmanJiaoluGUID='" + namedGroup.strTrainmanJiaoluGUID + "' order by nCheCiOrder desc"; nCheciOrder = Convert.ToInt32(SqlHelper.ExecuteDataset(SqlHelper.ConnString, CommandType.Text, strSql).Tables[0].Rows[0]["nCheCiOrder"].ToString()); nAddOrder = nCheciOrder + 1; } #endregion strSql = " update TAB_Nameplate_TrainmanJiaolu_Named set nCheCiOrder=nCheCiOrder+1 where strTrainmanJiaoluGUID='" + namedGroup.strTrainmanJiaoluGUID + "' and nCheCiOrder>" + nCheciOrder; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql); //添加记名式机组 strSql = @"insert into TAB_Nameplate_TrainmanJiaolu_Named (strCheciGUID,strTrainmanJiaoluGUID,nCheciOrder,nCheciType,strCheci1,strCheci2,strGroupGUID,dtLastArriveTime) values (@CheciGUID,@strTrainmanJiaoluGUID, @nCheCiOrder ,@CheciType,@Checi1,@Checi2,@GroupGUID,getdate())"; SqlParameter[] sqlParamsNamed = new SqlParameter[] { new SqlParameter("CheciGUID", namedGroup.strCheciGUID), new SqlParameter("strTrainmanJiaoluGUID", namedGroup.strTrainmanJiaoluGUID), new SqlParameter("CheciType", namedGroup.nCheciType), new SqlParameter("Checi1", namedGroup.strCheci1), new SqlParameter("Checi2", namedGroup.strCheci2), new SqlParameter("GroupGUID", namedGroup.Group.groupID), new SqlParameter("nCheCiOrder", nAddOrder) }; if (SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, sqlParamsNamed) == 0) { throw new Exception("创建记名式机组失败"); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { conn.Close(); } } }