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