/// <summary> /// 对审核步骤进行审核 /// </summary> /// <returns></returns> public DataSet GetData() { try { //DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); DataSet dsRequest = new DataSet(); ArrayList listAMSendList = new ArrayList(); Dbconn conn = new Dbconn("HZY_ITSM"); string strXML = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); DataSet ds = Common.Common.GetDSByExcelXML(strXML); string strCheck_ID = ""; string strCheck_User = ""; string strCheck_Result = ""; string strCheck_RemarK = ""; string strKey_Value = ""; string strKeyColumn = ""; string strRequestTableName = ""; strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString(); if (strCheck_ID == "") { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "1:您的请求有误!" }); return ds_Return; } DataTable dt = conn.GetDataTable(@" SELECT t1.ID , t1.Key_ID , t1.Seq_Index , t1.Seq_Name , t1.Check_User , t1.Check_Type, t1.Check_Type_Table_Name, t1.Check_Type_Key_Column, t1.Check_Date , t1.Check_Result , t1.Remark , t2.Table_Templete FROM dbo.Check_List t1 LEFT JOIN dbo.check_Table_Type t2 ON t1.Check_Type = t2.Check_Type WHERE t1.id = @Param0 ", new string[1] { strCheck_ID }); if (dt.Rows.Count == 0) { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "2:您的请求有误!" }); return ds_Return; } strKey_Value = dt.Rows[0]["Key_ID"].ToString(); strKeyColumn = dt.Rows[0]["Check_Type_Key_Column"].ToString(); strRequestTableName = dt.Rows[0]["Check_Type_Table_Name"].ToString(); if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET") { ITSM_Server_Request_EditByXML itsm_SR = new ITSM_Server_Request_EditByXML(); strXML = @"<ROOT><OPTYPE>GET</OPTYPE> <LIST> <ROW> <condition>" + strKeyColumn + @"='" + strKey_Value + @"'</condition> </ROW> </LIST> </ROOT>"; DataSet ds_ITSMSR_Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strXML}); itsm_SR.Request = ds_ITSMSR_Request; DataSet ds_ITSMSR_Response = itsm_SR.GetData(); if (ds_ITSMSR_Response.Tables.Count == 0 || ds_ITSMSR_Response.Tables[0].Rows.Count == 0) { ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "3:获取原始单据有误!" }); return ds_Return; } string strTableTemplete = dt.Rows[0]["Table_Templete"].ToString(); for (int i = 0; i < ds_ITSMSR_Response.Tables[0].Columns.Count; i++) { string strTemp = ds_ITSMSR_Response.Tables[0].Rows[0][i].ToString().Replace("\n", "<p>"); if(strTemp == "") { strTemp = " "; } strTableTemplete = strTableTemplete.Replace("[@@" + ds_ITSMSR_Response.Tables[0].Columns[i].ColumnName + "]", strTemp); } dt.Rows[0]["Table_Templete"] = Microsoft.JScript.GlobalObject.escape(strTableTemplete); dt.Columns.Add("timestamps"); dt.Rows[0]["timestamps"] = ds_ITSMSR_Response.Tables[0].Rows[0]["timestamps"]; ds_Return.Tables.Add(dt.Copy()); } else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT") { strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString(); strCheck_User = ds.Tables["LIST"].Rows[0]["Check_User"].ToString(); strCheck_Result = ds.Tables["LIST"].Rows[0]["Check_Result"].ToString(); strCheck_RemarK = ds.Tables["LIST"].Rows[0]["Check_Remark"].ToString(); DataTable dtCheckList = conn.GetDataTable("SELECT * FROM Check_List WHERE ID='" + strCheck_ID + "'"); if (dt.Rows.Count == 0) { throw new Exception("该审核单已不存在,请联系管理员"); } int nSeq_ID = Convert.ToInt32(dt.Rows[0]["Seq_Index"]); //判断是否已经进行后续的审批 string strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + dt.Rows[0]["Key_ID"].ToString() + @"' AND Check_Result is not null AND Seq_Index>" + nSeq_ID.ToString() + ""; if (conn.GetDataTableRowCount(strSql) > 0) { throw new Exception("下一道的流程已经审批,本次审批不能再做修改!"); } dtCheckList.Rows[0]["Check_User"] = strCheck_User; dtCheckList.Rows[0]["Check_Date"] = DateTime.Now; dtCheckList.Rows[0]["Check_Result"] = strCheck_Result; dtCheckList.Rows[0]["Remark"] = strCheck_RemarK; dtCheckList.Rows[0]["updateTime"] = DateTime.Now; ArrayList listTable = new ArrayList(); listTable.Add("Check_List"); try { conn.BeginTransaction(); conn.TableLock(listTable); //判断数据是否为最新数据 strSql = "SELECT timestamps+0 as timestamps from " + strRequestTableName + " WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; string strTimestamps = conn.GetDataTableFirstValue(strSql).ToString(); if (strTimestamps != ds.Tables["LIST"].Rows[0]["timestamps"].ToString()) { throw new Exception("原申请单数据已被修改,请刷新后重试!"); } //dt.Columns.Remove("timestamps"); conn.Update("Check_List", dtCheckList, "ID='" + strCheck_ID + "'"); string[] strColumn = new string[3]; string[] strValue = new string[3]; strColumn[0] = "Check_Type"; strColumn[1] = "Seq_Name"; strColumn[2] = "Key_ID"; strValue[0] = dt.Rows[0]["Check_Type"].ToString(); strValue[1] = dt.Rows[0]["Seq_Name"].ToString(); strValue[2] = dt.Rows[0]["Key_ID"].ToString(); DataSet requestDs = Common.Common.GetRequestDataSet(strColumn, strValue); ITSM_Check_List_Edit checkListEdit = new ITSM_Check_List_Edit(); checkListEdit.Conn = conn; checkListEdit.ListAMSendList = listAMSendList; checkListEdit.Request = requestDs; checkListEdit.Execute(); if (strCheck_Result != "同意") { //更新原表的状态为已驳回 strSql = "UPDATE " + strRequestTableName + " SET State='已驳回' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); } else { //判断是否有已驳回的状态 strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + strKey_Value + @"' AND (Seq_index = " + nSeq_ID.ToString() + @" AND Check_Result='已驳回')"; //没有已驳回的记录 if (conn.GetDataTableRowCount(strSql) == 0) { //判断是否还有后续的流程 strSql = "SELECT '1' as A from Check_List WHERE Check_Type='" + dt.Rows[0]["Check_Type"].ToString() + @"' AND Key_ID='" + strKey_Value + @"' AND (Seq_Index>" + nSeq_ID.ToString() + @" OR (Seq_Index=" + nSeq_ID.ToString() + @" AND Must_Check=1 AND Check_Date IS NULL ))"; if (conn.GetDataTableRowCount(strSql) == 0) { //更新原表的状态为已审核 strSql = "UPDATE " + strRequestTableName + " SET State='已审核' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); ITSM_Common.SendAMMessage("", "马卫清", "有服务器申请表需要操作", "您有有服务器申请表需要操作,请进入EXCEL操作"); } else { //更新原表的状态为审核中 strSql = "UPDATE " + strRequestTableName + " SET State='审核中' WHERE Deleted=0 AND " + strKeyColumn + "='" + strKey_Value + "'"; conn.ExcuteQuerryByTran(strSql); } } } conn.CommitTransaction(); for (int i = 0; i < listAMSendList.Count; i++) { string[] strList = (string[])(listAMSendList[i]); ITSM_Common.SendAMMessage(strList[0], strList[1], strList[2]); } } catch (Exception) { conn.RollbackTransaction(); throw; } ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "true" }); } return ds_Return; } catch { throw; } }
/// <summary> /// 根据XML内容更新服务器申请单 /// </summary> /// <returns></returns> public DataSet GetData() { try { DataSet ds = new DataSet(); DataSet ds_Return = new DataSet(); DataSet dsRequest = new DataSet(); ArrayList listAMSendList = new ArrayList(); Dbconn conn = new Dbconn("HZY_ITSM"); string strXML = ""; string strSQL = ""; strXML = m_request.Tables[0].Rows[0]["XML"].ToString(); ds = Common.Common.GetDSByExcelXML(strXML); if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET") { string strWHERE = " Deleted=0 "; if (ds.Tables["LIST"].Columns.Contains("Condition")) { if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "") { strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")"; } } strSQL = @" SELECT [Request_ID] ,[Request_Code] ,[Request_UserName] ,[Request_Date] ,[Request_Type] ,[Prod] ,[Memory] ,[CPU] ,[Disk_Space] ,[Request_Reason] ,[Request_RequireMent] ,[State] ,[OM_Opration] ,[OM_Opration_Name] ,[OM_Opration_Date] ,[Deleted] ,[timestamps]+0 timestamps , t2.Check_User CIO , t2.Check_Date CIO_Check_Date , t2.Remark AS CIO_Remark , t2.Check_Result AS CIO_Check_Result , t2.ID AS CIO_Check_ID , t3.Check_User OM , t3.Check_Date OM_Check_Date , t3.Check_Result AS OM_Check_Result , t3.ID AS OM_Check_ID , t3.Remark AS OM_Remark FROM Server_Request WITH ( NOLOCK ) LEFT JOIN dbo.Check_List t2 ON Server_Request.Request_Code = t2.Key_ID AND t2.Check_Type = 'Server_Request' AND t2.seq_name = 'CIO' LEFT JOIN dbo.Check_List t3 ON Server_Request.Request_Code = t3.Key_ID AND t3.Check_Type = 'Server_Request' AND t3.seq_name = 'OM' WHERE " + strWHERE; ds_Return = conn.GetDataSet(strSQL); } else { ArrayList tableList = new ArrayList(); tableList.Add("Server_Request"); tableList.Add("Check_List"); conn.BeginTransaction(); conn.TableLock(tableList); try { if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE") { for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { strSQL = @"UPDATE Server_Request SET Deleted=1 WHERE Request_Code='" + ds.Tables["LIST"].Rows[i]["Request_Code"].ToString() + @"' AND Deleted=0 "; conn.ExcuteQuerryByTran(strSQL); } } else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT") { for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++) { DataTable dt = ds.Tables["LIST"].Clone(); dt.ImportRow(ds.Tables["LIST"].Rows[i]); strSQL = @"SELECT * FROM Server_Request WHERE Request_Code=@param0 AND Deleted=0 "; DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["Request_Code"].ToString() }); //update if (dt_SR.Rows.Count > 0) { //运维操作 if (ds.Tables["LIST"].Rows[i]["OM_Opration"].ToString()!= "") { if (!dt.Columns.Contains("State")) { dt.Columns.Add("State"); } if (!dt.Columns.Contains("OM_Opration_Date")) { dt.Columns.Add("OM_Opration_Date"); } dt.Rows[0]["State"] = "已完成"; dt.Rows[0]["OM_Opration_Date"] =DateTime.Now; ITSM_Common.SendAMMessage("", dt_SR.Rows[0]["Request_UserName"].ToString(), "您的申请单已经处理完毕", "您的申请单(" + dt_SR.Rows[0]["Request_Code"].ToString() + ")已经处理完毕,请进入EXCEL查看"); } conn.Update("Server_Request", dt, "Request_Code='" + ds.Tables["LIST"].Rows[i]["Request_Code"].ToString() + @"'"); } else //add { DataTable dtTemp = conn.GetDataTableInTrans( @"SELECT MAX(Request_Code) AS MaxCode ,'SR'+CONVERT(VARCHAR(10),GETDATE(),112) AS PreCode FROM dbo.Server_Request WHERE Request_Code LIKE 'SR'+CONVERT(VARCHAR(10),GETDATE(),112) + '%'"); string strCode = dtTemp.Rows[0]["MaxCode"].ToString(); if (strCode == "") { strCode = dtTemp.Rows[0]["PreCode"].ToString() + "001"; } else { strCode = dtTemp.Rows[0]["PreCode"].ToString() + (Convert.ToInt32(strCode.Substring(strCode.Length - 3)) + 1).ToString("000"); } if (!dt.Columns.Contains("State")) { dt.Columns.Add("State"); } dt.Rows[0]["State"] = "已提交"; dt.Rows[0]["Request_Code"] = strCode; conn.Insert("Server_Request", dt); ITSM_Check_List_Edit checkList = new ITSM_Check_List_Edit(); string[] strColumn = new string[3]; string[] strValue = new string[3]; strColumn[0] = "Check_Type"; strColumn[1] = "Seq_Name"; strColumn[2] = "Key_ID"; strValue[0] = "Server_Request"; strValue[1] = ""; strValue[2] = strCode; checkList.Request = Common.Common.GetRequestDataSet(strColumn,strValue); checkList.Conn = conn; checkList.ListAMSendList = listAMSendList; checkList.Execute(); //listAMSendList = checkList.ListAMSendList; } } } conn.CommitTransaction(); for (int i = 0; i < listAMSendList.Count; i++) { string[] strList= (string[])(listAMSendList[i]); ITSM_Common.SendAMMessage(strList[0], strList[1],strList[2]); } ds_Return = Common.Common.GetRequestDataSet(new string[]{"Result"},new string[]{"true"}); } catch { conn.RollbackTransaction(); throw; } } return ds_Return; } catch { throw; } }