public static Dictionary <string, AcccelaDBModel> GetAccelaDatabaseMapper()
{
Dictionary <string, AcccelaDBModel> AccelaDBMapper = new Dictionary <string, AcccelaDBModel>();
// C# 连接 Oracle 的几种方式: http://www.cnblogs.com/storys/archive/2013/03/06/2945914.html
string connString = "User ID=dbmread;Password=dbmread;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.156)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dbs156)))";
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT DB_TYPE,
CUSTOMER,
VERSION_ORI,
DB_IP,
DB_PORT,
DB_SID,
DB_NAME,
DB_USER,
DB_PASS,
USER_REQUESTOR,
DB_USAGE,
AGENCY
FROM DB_SHEET
WHERE DB_STATUS = 'USING'
ORDER BY DB_CREATED DESC ";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
AcccelaDBModel accelaDBModel = new AcccelaDBModel();
accelaDBModel.DBType = reader.GetOracleString(0).ToString(); // DB_TYPE
accelaDBModel.Customer = reader.GetOracleString(1).ToString(); // CUSTOMER
accelaDBModel.Version = reader.GetOracleString(2).ToString(); // VERSION_CUR
accelaDBModel.IP = reader.GetOracleString(3).ToString(); // DB_IP
accelaDBModel.Port = reader.GetOracleString(4).ToString(); // DB_PORT
accelaDBModel.SID = reader.GetOracleString(5).ToString(); // DB_SID
accelaDBModel.DBName = reader.GetOracleString(6).ToString(); // DB_NAME
accelaDBModel.User = reader.GetOracleString(7).ToString(); // DB_USER
accelaDBModel.Password = reader.GetOracleString(8).ToString(); // DB_PASS
accelaDBModel.Owner = reader.GetOracleString(9).ToString(); // USER_REQUESTOR
accelaDBModel.SFCase = reader.GetOracleString(10).ToString(); // DB_USAGE
if (!AccelaDBMapper.ContainsKey(accelaDBModel.Customer))
{
if (accelaDBModel.DBName.IndexOf("_jet", StringComparison.InvariantCultureIgnoreCase) < 0)
{
AccelaDBMapper.Add(accelaDBModel.Customer, accelaDBModel);
}
}
}
return(AccelaDBMapper);
}
private async void btnSync_Click(object sender, EventArgs e)
{
this.btnSync.Enabled = false;
// C# 连接 Oracle 的几种方式: http://www.cnblogs.com/storys/archive/2013/03/06/2945914.html
string connString = "User ID=dbmread;Password=dbmread;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.156)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dbs156)))";
OracleConnection conn = new OracleConnection(connString);
try
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT DB_TYPE,
CUSTOMER,
VERSION_ORI,
VERSION_CUR,
DB_IP,
DB_PORT,
DB_SID,
DB_NAME,
DB_USER,
DB_PASS,
DB_CREATED,
USER_REQUESTOR,
DB_USAGE,
AGENCY
FROM DB_SHEET
WHERE DB_GROUP = 'MAINTENANCE' ";
OracleDataReader reader = cmd.ExecuteReader();
List <AcccelaDBModel> accelaDBModelList = new List <AcccelaDBModel>();
while (reader.Read())
{
AcccelaDBModel accelaDBModel = new AcccelaDBModel();
accelaDBModel.DBType = reader.GetOracleString(0).ToString(); // DB_TYPE
accelaDBModel.Customer = reader.GetOracleString(1).ToString(); // CUSTOMER
accelaDBModel.Version = reader.GetOracleString(2).ToString(); // VERSION_ORI
accelaDBModel.IP = reader.GetOracleString(4).ToString(); // DB_IP
accelaDBModel.Port = reader.GetOracleString(5).ToString(); // DB_PORT
accelaDBModel.SID = reader.GetOracleString(6).ToString(); // DB_SID
accelaDBModel.DBType = reader.GetOracleString(7).ToString(); // DB_NAME
accelaDBModel.DBName = reader.GetOracleString(8).ToString(); // DB_USER
accelaDBModel.User = reader.GetOracleString(9).ToString(); // DB_PASS
accelaDBModel.Password = reader.GetDateTime(10).ToShortDateString();
//accelaDBModel.CreatedDate = reader.GetOracleString(11).ToString();// DB_CREATED
accelaDBModel.Owner = reader.GetOracleString(12).ToString(); // USER_REQUESTOR
accelaDBModel.SFCase = reader.GetOracleString(13).ToString(); // DB_USAGE
accelaDBModel.SID = accelaDBModel.IP + "_" + accelaDBModel.Port + "_" + accelaDBModel.SID + "_" + accelaDBModel.User + "_" + accelaDBModel.Password;
accelaDBModelList.Add(accelaDBModel);
}
XmlDocument xmlDoc = new XmlDocument();
//创建Xml声明部分，即<?xml version="1.0" encoding="utf-8" ?>
XmlDeclaration Declaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);
//创建根节点
XmlNode rootNode = xmlDoc.CreateElement("AccelaDBList");
xmlDoc.AppendChild(rootNode);
//附加根节点
xmlDoc.InsertBefore(Declaration, xmlDoc.DocumentElement);
foreach (AcccelaDBModel accelaDBModel in accelaDBModelList)
{
string type = accelaDBModel.DBType;
string customer = accelaDBModel.Customer;
string version = accelaDBModel.Version;
string ip = accelaDBModel.IP;
string port = accelaDBModel.Port;
string sid = accelaDBModel.SID;
string name = accelaDBModel.SID;
string user = accelaDBModel.User;
string password = accelaDBModel.Password;
string createdDate = accelaDBModel.CreatedDate.ToShortTimeString();
string owner = accelaDBModel.Owner;
string sfCase = accelaDBModel.SFCase;
string agencyList = accelaDBModel.AgencyList;
//
XmlNode accelaDB = xmlDoc.CreateElement("AccelaDB");
rootNode.AppendChild(accelaDB);
XmlAttribute typeNode = xmlDoc.CreateAttribute("Type");
typeNode.Value = type;
accelaDB.Attributes.Append(typeNode);
XmlAttribute ipNode = xmlDoc.CreateAttribute("IP");
ipNode.Value = ip;
accelaDB.Attributes.Append(ipNode);
XmlAttribute portNode = xmlDoc.CreateAttribute("Port");
portNode.Value = port;
accelaDB.Attributes.Append(portNode);
XmlAttribute sidNode = xmlDoc.CreateAttribute("SID");
sidNode.Value = sid;
accelaDB.Attributes.Append(sidNode);
XmlAttribute nameNode = xmlDoc.CreateAttribute("Name");
nameNode.Value = name;
accelaDB.Attributes.Append(nameNode);
XmlAttribute userNode = xmlDoc.CreateAttribute("User");
userNode.Value = user;
accelaDB.Attributes.Append(userNode);
XmlAttribute passwordNode = xmlDoc.CreateAttribute("Password");
passwordNode.Value = password;
accelaDB.Attributes.Append(passwordNode);
XmlAttribute createdDateNode = xmlDoc.CreateAttribute("CreatedDate");
createdDateNode.Value = createdDate;
accelaDB.Attributes.Append(createdDateNode);
XmlAttribute ownerNode = xmlDoc.CreateAttribute("Owner");
ownerNode.Value = owner;
accelaDB.Attributes.Append(ownerNode);
XmlAttribute sfCaseNode = xmlDoc.CreateAttribute("SFCase");
sfCaseNode.Value = sfCase;
accelaDB.Attributes.Append(sfCaseNode);
var GetCustomer = await getCustomerOrAccountBySF(sfCase);
XmlAttribute customerNode = xmlDoc.CreateAttribute("Customer");
customerNode.Value = GetCustomer;
accelaDB.Attributes.Append(customerNode);
string versionAndAgencyList = getVersionAndAgencyList(type, ip, port, sid, user, password);
int index = versionAndAgencyList.IndexOf(";");
if (index > 0)
{
XmlAttribute versionNode = xmlDoc.CreateAttribute("Version");
versionNode.Value = versionAndAgencyList.Substring(0, index);
accelaDB.Attributes.Append(versionNode);
XmlAttribute agencyListNode = xmlDoc.CreateAttribute("AgencyList");
agencyListNode.Value = versionAndAgencyList.Substring(index + 1);;
accelaDB.Attributes.Append(agencyListNode);
}
}
xmlDoc.AppendChild(rootNode);
//保存Xml文档
xmlDoc.Save(@".\AccelaDBSheet.xml");
Console.WriteLine("已保存Xml文档");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
MessageBox.Show("Sync finish!");
conn.Close();
}
this.btnSync.Enabled = true;
}
private async void btnCheck_Click(object sender, EventArgs e)
{
//
this.btnCheck.Enabled = false;
string strSFID = this.txtSFID.Text.Trim();
var GetCaseInfoByID = SalesforceProxy.GetCaseInfoByID(strSFID);
var caseInfo = await GetCaseInfoByID;
AccelaCase[] accelaCaseList = caseInfo.ToArray();
if (accelaCaseList.Length == 0)
{
MessageBox.Show("No case is found.");
}
else
{
this.txtCustomerInfo.Text = accelaCaseList[0].Customer != null ? accelaCaseList[0].Customer.Name : (accelaCaseList[0].Account != null ? accelaCaseList[0].Account.Name : "");
this.txtVersion.Text = accelaCaseList[0].CurrentVersion;
this.chbAccelaHostedFlag.Checked = (accelaCaseList[0].Hosted != null && accelaCaseList[0].Hosted.IndexOf("Accela") >= 0 ? true : false);
this.txtCaseOwner.Text = accelaCaseList[0].CreatedBy.Name;
this.txtIssueSubject.Text = accelaCaseList[0].Subject;
this.txtProduct.Text = accelaCaseList[0].Product;
this.txtPriority.Text = "";
if (accelaCaseList[0].Priority.IndexOf("Critical") >= 0)
{
this.txtPriority.Text = "Critical";
}
if (accelaCaseList[0].Priority.IndexOf("High") >= 0)
{
this.txtPriority.Text = "High";
}
if (accelaCaseList[0].Priority.IndexOf("Medium") >= 0)
{
this.txtPriority.Text = "Medium";
}
if (accelaCaseList[0].Priority.IndexOf("Low") >= 0)
{
this.txtPriority.Text = "Low";
}
}
var GetIssueByID = JiraProxy.GetIssueByID("ENGSUPP", "", strSFID);
var issueInfo = await GetIssueByID;
if (issueInfo == null)
{
return;
}
this.txtEngsuppID.Text = issueInfo.key;
this.txtReviewer.Text = issueInfo.fields.assignee.name;
bool hasOldDB = issueInfo.fields.labels.IndexOf("DB") >= 0;
if (hasOldDB && AccelaDBMapper.ContainsKey(this.txtCustomerInfo.Text))
{
AcccelaDBModel acccelaDBInfo = AccelaDBMapper[this.txtCustomerInfo.Text];
this.txtDBType.Text = acccelaDBInfo.DBType;
this.txtDBServerIP.Text = acccelaDBInfo.IP;
this.txtDBServerPort.Text = acccelaDBInfo.Port;
this.txtDBInstance.Text = acccelaDBInfo.DBName;
this.txtDBVersion.Text = acccelaDBInfo.Version;
this.txtDBUser.Text = acccelaDBInfo.User;
this.txtDBPassword.Text = acccelaDBInfo.Password;
this.txtRelatedCase.Text = acccelaDBInfo.SFCase;
}
var GetDBTaskBySFID = JiraProxy.GetDatabaseTaskByCaseID("DATABASE", "Task", strSFID);
var taskInfo = await GetDBTaskBySFID;
if (taskInfo != null)
{
this.txtDatabaseID.Text = taskInfo.key;
}
this.btnRequest.Enabled = true;
this.btnCheck.Enabled = true;
}