public String insertFromxCVSMT(XcustValueSetMstTbl item, String host)
{
String chk = "";
int VALUE_SET_ID = 0, VALUE_SET_CODE = 1, VALUE_ID = 2, VALUE = 3, DESCRIPTION = 4, ENABLED_FLAG = 5, LAST_UPDATE_DATE = 6, CREATION_DATE = 7;
String sql = "";
//String[] data1 = item.Split(',');
DataTable dt = selectByPk(item.VALUE_SET_ID, item.VALUE_ID);
if (dt.Rows.Count > 0)
{
sql = "Update " + xCVSMT.table + " Set " + xCVSMT.DESCRIPTION + "='" + item.DESCRIPTION + "', " + xCVSMT.ENABLED_FLAG + "='" + item.ENABLED_FLAG + "' " +
"Where " + xCVSMT.VALUE_SET_ID + " = " + item.VALUE_SET_ID + " and " + xCVSMT.VALUE_ID + " = " + item.VALUE_ID + "";
}
else
{
sql = "Insert into " + xCVSMT.table + "(" + xCVSMT.CREATION_DATE + "," + xCVSMT.DESCRIPTION + "," + xCVSMT.ENABLED_FLAG + "," +
xCVSMT.LAST_UPDATE_DATE + "," + xCVSMT.VALUE + "," + xCVSMT.VALUE_ID + "," +
xCVSMT.VALUE_SET_CODE + "," + xCVSMT.VALUE_SET_ID + ") " +
"Values ('" + item.CREATION_DATE + "','" + item.DESCRIPTION + "','" + item.ENABLED_FLAG
+ "','" + item.LAST_UPDATE_DATE + "','" + item.VALUE + "'," + item.VALUE_ID
+ ",'" + item.VALUE_SET_CODE + "'," + item.VALUE_SET_ID + ")";
}
chk = conn.ExecuteNonQuery(sql.ToString(), host);
return(chk);
}
public String insertFromxCVSMT(XcustValueSetMstTbl item, String host)
{
String sql = "", chk = "";
if (selectDupPk(item.VALUE_SET_ID, item.VALUE_ID))
{
deletexCItem(item.VALUE_SET_ID, item.VALUE_ID);
}
chk = insert(item, host);
return(chk);
}
private void initConfig()
{
xCVSMT = new XcustValueSetMstTbl();
xCVSMT.CREATION_DATE = "CREATION_DATE";
xCVSMT.DESCRIPTION = "DESCRIPTION";
xCVSMT.ENABLED_FLAG = "ENABLED_FLAG";
xCVSMT.LAST_UPDATE_DATE = "LAST_UPDATE_DATE";
xCVSMT.VALUE = "VALUE";
xCVSMT.VALUE_ID = "VALUE_ID";
xCVSMT.VALUE_SET_CODE = "VALUE_SET_CODE";
xCVSMT.VALUE_SET_ID = "VALUE_SET_ID";
xCVSMT.table = "XCUST_VALUE_SET_MST_TBL_TEST";
xCVSMT.pkField = "";
}
public XcustValueSetMstTbl setData(DataRow row)
{
XcustValueSetMstTbl item;
item = new XcustValueSetMstTbl();
item.CREATION_DATE = row[xCVSMT.CREATION_DATE].ToString();
item.DESCRIPTION = row[xCVSMT.DESCRIPTION].ToString();
item.ENABLED_FLAG = row[xCVSMT.ENABLED_FLAG].ToString();
item.LAST_UPDATE_DATE = row[xCVSMT.LAST_UPDATE_DATE].ToString();
item.VALUE = row[xCVSMT.VALUE].ToString();
item.VALUE_ID = row[xCVSMT.VALUE_ID].ToString();
item.VALUE_SET_CODE = row[xCVSMT.VALUE_SET_CODE].ToString();
item.VALUE_SET_ID = row[xCVSMT.VALUE_SET_ID].ToString();
return(item);
}
public String insert(XcustValueSetMstTbl item, String host)
{
String sql = "", chk = "";
try
{
sql = "Insert into " + xCVSMT.table + "(" + xCVSMT.VALUE_SET_ID + ","
+ xCVSMT.VALUE_SET_CODE + ","
+ xCVSMT.VALUE_ID + ","
+ xCVSMT.VALUE + ","
+ xCVSMT.DESCRIPTION + ","
+ xCVSMT.ENABLED_FLAG + ","
+ xCVSMT.LAST_UPDATE_DATE + ","
+ xCVSMT.CREATION_DATE + ","
+ xCVSMT.CODE_COMBINATION_ID + ","
+ xCVSMT.ATTRIBUTE3
+ ") " +
"Values ('" + item.VALUE_SET_ID + "','"
+ item.VALUE_SET_CODE + "','"
+ item.VALUE_ID + "','"
+ item.VALUE + "','"
+ item.DESCRIPTION + "','"
+ item.ENABLED_FLAG + "','"
+ item.LAST_UPDATE_DATE + "','"
+ item.CREATION_DATE + "','"
+ item.CODE_COMBINATION_ID + "','"
+ item.ATTRIBUTE3 + "'" +
") ";
//MessageBox.Show(sql);
chk = conn.ExecuteNonQuery(sql, host);
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.ToString(), "insert Doctor");
}
return(chk);
}
public void setXcustValueTbl(MaterialListView lv1, Form form1, MaterialProgressBar pB1)
{
String uri = "", dump = "";
//HttpWebRequest request = CreateWebRequest();
XmlDocument soapEnvelopeXml = new XmlDocument();
const Int32 BufferSize = 128;
String[] filePO;
addListView("setXcustValueTbl ", "Web Service", lv1, form1);
//filePO = Cm.getFileinFolder(Cm.initC.PathZip);
//String text = System.IO.File.ReadAllText(filePO[0]);
//byte[] byteArraytext = Encoding.UTF8.GetBytes(text);
//byte[] toEncodeAsBytestext = System.Text.ASCIIEncoding.ASCII.GetBytes(text);
//String Arraytext = System.Convert.ToBase64String(toEncodeAsBytestext);
//< soapenv:Envelope xmlns:soapenv = "http://schemas xmlsoap org/soap/envelope/" xmlns: v2 = "http://xmlns oracle com/oxp/service/v2" >
uri = @" <soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:pub='http://xmlns.oracle.com/oxp/service/PublicReportService'> " +
"<soapenv:Header/> " +
"<soapenv:Body> " +
"<v2:runReport> " +
"<v2:reportRequest> " +
"<v2:attributeLocale>en-US</v2:attributeLocale> " +
"<v2:attributeTemplate>XCUST_VALUESET_MST_REP</v2:attributeTemplate> " +
"<v2:reportAbsolutePath>/Custom/XCUST_CUSTOM/XCUST_VALUESET_MST_REP.xdo</v2:reportAbsolutePath> " +
"<pub:parameterNameValues> " +
"<pub:item> " +
"<pub:multiValuesAllowed>False</pub:multiValuesAllowed> " +
"<pub:name>p_update_from</pub:name> " +
"<pub:values> " +
"<pub:item>" + Cm.initC.p_update_from + "</pub:item> " +
"</pub:values>" +
"</pub:item>" +
"<pub:item>" +
"<pub:multiValuesAllowed>False</pub:multiValuesAllowed> " +
"<pub:name>p_update_to</pub:name> " +
"<pub:values> " +
"<pub:item>" + Cm.initC.p_update_to + "</pub:item> " +
"</pub:values> " +
"</pub:item> " +
"</pub:parameterNameValues> " +
"</v2:reportRequest> " +
"<v2:userID>" + Cm.initC.usercloud + "</v2:userID> " +
"<v2:password>" + Cm.initC.passcloud + "</v2:password> " +
"</v2:runReport> " +
"</soapenv:Body> " +
"</soapenv:Envelope> ";
//byte[] byteArray = Encoding.UTF8.GetBytes(envelope);
byte[] byteArray = Encoding.UTF8.GetBytes(uri);
addListView("setXcustValueTbl Start", "Web Service", lv1, form1);
// Construct the base 64 encoded string used as credentials for the service call
byte[] toEncodeAsBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(Cm.initC.usercloud + ":" + Cm.initC.passcloud);
string credentials = System.Convert.ToBase64String(toEncodeAsBytes);
// Create HttpWebRequest connection to the service
HttpWebRequest request1 = (HttpWebRequest)WebRequest.Create("https://eglj.fa.us2.oraclecloud.com/xmlpserver/services/PublicReportService");
// Configure the request content type to be xml, HTTP method to be POST, and set the content length
request1.Method = "POST";
request1.ContentType = "text/xml;charset=UTF-8";
request1.ContentLength = byteArray.Length;
// Configure the request to use basic authentication, with base64 encoded user name and password, to invoke the service.
request1.Headers.Add("Authorization", "Basic " + credentials);
// Set the SOAP action to be invoked; while the call works without this, the value is expected to be set based as per standards
request1.Headers.Add("SOAPAction", "https://eglj.fa.us2.oraclecloud.com/xmlpserver/services/PublicReportService");
// Write the xml payload to the request
Stream dataStream = request1.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();
addListView("setXcustValueTbl Request", "Web Service", lv1, form1);
// Get the response and process it; In this example, we simply print out the response XDocument doc;
string actNumber = "";
XDocument doc;
using (WebResponse response = request1.GetResponse())
{
addListView("setXcustValueTbl Response", "Web Service", lv1, form1);
using (Stream stream = response.GetResponseStream())
{
doc = XDocument.Load(stream);
foreach (XNode node in doc.DescendantNodes())
{
if (node is XElement)
{
XElement element = (XElement)node;
if (element.Name.LocalName.Equals("reportBytes"))
{
actNumber = element.ToString().Replace(@"<ns1:reportBytes xmlns:ns1=""http://xmlns.oracle.com/oxp/service/PublicReportService"">", "");
actNumber = actNumber.Replace("</reportBytes>", "").Replace("</result>", "").Replace(@"""", "").Replace("<>", "");
actNumber = actNumber.Replace("<reportBytes>", "").Replace("</ns1:reportBytes>", "");
}
}
}
}
}
actNumber = actNumber.Trim();
actNumber = actNumber.IndexOf("<reportContentType>") >= 0 ? actNumber.Substring(0, actNumber.IndexOf("<reportContentType>")) : actNumber;
addListView("setXcustValueTbl Extract html", "Web Service", lv1, form1);
byte[] data = Convert.FromBase64String(actNumber);
string decodedString = Encoding.UTF8.GetString(data);
//XElement html = XElement.Parse(decodedString);
//string[] values = html.Descendants("table").Select(td => td.Value).ToArray();
//int row = -1;
//var doc1 = new HtmlAgilityPack.HtmlDocument();
//doc1.LoadHtml(html.ToString());
//var nodesTable = doc1.DocumentNode.Descendants("tr");
String[] data1 = decodedString.Split('\n');
//foreach (var nodeTr in nodesTable)
for (int row = 0; row < data1.Length; row++)
{
if (row == 0)
{
continue;
}
if (data1[row].Length <= 0)
{
continue;
}
String[] data2 = data1[row].Split(',');
XcustValueSetMstTbl item = new XcustValueSetMstTbl();
item.VALUE_SET_ID = data2[0].Trim().Replace("\"", "");
item.VALUE_SET_CODE = data2[1].Trim().Replace("\"", "");
item.VALUE_ID = data2[2].Trim().Replace("\"", "");
item.VALUE = data2[3].Trim().Replace("\"", "");
item.ENABLED_FLAG = data2[4].Trim().Replace("\"", "");
item.DESCRIPTION = data2[5].Trim().Replace("\"", "").Replace("'", "");
item.LAST_UPDATE_DATE = data2[6].Trim().Replace("\"", "");
item.CREATION_DATE = data2[7].Trim().Replace("\"", "");
item.CODE_COMBINATION_ID = data2[8].Trim();//.Replace("\"", "");
item.ATTRIBUTE3 = data2[9].Trim().Replace("\"", "");
//MessageBox.Show("111"+item.CREATION_DATE);
xCValueDB.insertFromxCVSMT(item, "kfc_po");
}
Console.WriteLine(decodedString);
}
public void setValueSetMst(MaterialListView lv1, Form form1, MaterialProgressBar pB1)
{
String uri = "", dump = "";
//HttpWebRequest request = CreateWebRequest();
XmlDocument soapEnvelopeXml = new XmlDocument();
const Int32 BufferSize = 128;
String[] filePO;
addListView("setValueSetMst ", "Web Service", lv1, form1);
//filePO = Cm.getFileinFolder(Cm.initC.PathZip);
//String text = System.IO.File.ReadAllText(filePO[0]);
//byte[] byteArraytext = Encoding.UTF8.GetBytes(text);
//byte[] toEncodeAsBytestext = System.Text.ASCIIEncoding.ASCII.GetBytes(text);
//String Arraytext = System.Convert.ToBase64String(toEncodeAsBytestext);
//< soapenv:Envelope xmlns:soapenv = "http://schemas xmlsoap org/soap/envelope/" xmlns: v2 = "http://xmlns oracle com/oxp/service/v2" >
uri = @" <soapenv:Envelope xmlns:soapenv ='http://schemas.xmlsoap.org/soap/envelope/' xmlns:v2='http://xmlns.oracle.com/oxp/service/v2' > " +
"<soapenv:Header/> " +
"<soapenv:Body> " +
"<v2:runReport> " +
"<v2:reportRequest> " +
"<v2:attributeLocale>en-US</v2:attributeLocale> " +
"<v2:attributeTemplate>XCUST_MAS_VALUE_SET_REP2</v2:attributeTemplate> " +
"<v2:reportAbsolutePath>/Custom/XCUST_CUSTOM/XCUST_MAS_VALUE_SET_REP.xdo</v2:reportAbsolutePath>" +
"</v2:reportRequest> " +
"<v2:userID>[email protected]</v2:userID> " +
"<v2:password>icetech@2017</v2:password> " +
"</v2:runReport> " +
"</soapenv:Body> " +
"</soapenv:Envelope> ";
//byte[] byteArray = Encoding.UTF8.GetBytes(envelope);
byte[] byteArray = Encoding.UTF8.GetBytes(uri);
addListView("setValueSetMst Start", "Web Service", lv1, form1);
// Construct the base 64 encoded string used as credentials for the service call
byte[] toEncodeAsBytes = System.Text.ASCIIEncoding.ASCII.GetBytes("*****@*****.**" + ":" + "icetech@2017");
string credentials = System.Convert.ToBase64String(toEncodeAsBytes);
// Create HttpWebRequest connection to the service
HttpWebRequest request1 = (HttpWebRequest)WebRequest.Create("https://eglj-test.fa.us2.oraclecloud.com/xmlpserver/services/PublicReportService");
// Configure the request content type to be xml, HTTP method to be POST, and set the content length
request1.Method = "POST";
request1.ContentType = "text/xml;charset=UTF-8";
request1.ContentLength = byteArray.Length;
// Configure the request to use basic authentication, with base64 encoded user name and password, to invoke the service.
request1.Headers.Add("Authorization", "Basic " + credentials);
// Set the SOAP action to be invoked; while the call works without this, the value is expected to be set based as per standards
request1.Headers.Add("SOAPAction", "http://xmlns.oracle.com/oxp/service/PublicReportService");
// Write the xml payload to the request
Stream dataStream = request1.GetRequestStream();
dataStream.Write(byteArray, 0, byteArray.Length);
dataStream.Close();
addListView("setValueSetMst Request", "Web Service", lv1, form1);
// Get the response and process it; In this example, we simply print out the response XDocument doc;
string actNumber = "";
XDocument doc;
using (WebResponse response = request1.GetResponse())
{
addListView("setValueSetMst Response", "Web Service", lv1, form1);
using (Stream stream = response.GetResponseStream())
{
doc = XDocument.Load(stream);
foreach (XNode node in doc.DescendantNodes())
{
if (node is XElement)
{
XElement element = (XElement)node;
if (element.Name.LocalName.Equals("reportBytes"))
{
actNumber = element.ToString().Replace(@"<reportBytes xmlns=""http://xmlns.oracle.com/oxp/service/v2"">", "");
actNumber = actNumber.Replace("</reportBytes>", "").Replace("</result>", "").Replace(@"""", "").Replace("<>", "");
}
}
}
}
}
addListView("setValueSetMst Extract html", "Web Service", lv1, form1);
byte[] data = Convert.FromBase64String(actNumber);
string decodedString = Encoding.UTF8.GetString(data);
XElement html = XElement.Parse(decodedString);
string[] values = html.Descendants("table").Select(td => td.Value).ToArray();
int row = -1;
var doc1 = new HtmlAgilityPack.HtmlDocument();
doc1.LoadHtml(html.ToString());
var nodesTable = doc1.DocumentNode.Descendants("tr");
foreach (var nodeTr in nodesTable)
{
row++;
if (row == 0)
{
continue;
}
XcustValueSetMstTbl item = new XcustValueSetMstTbl();
HtmlNodeCollection cells = nodeTr.SelectNodes("td");
//String VALUE_SET_ID = cells[0].InnerText.Replace("\r\n","").Trim();
//String VALUE_SET_CODE = cells[1].InnerText.Replace("\r\n", "").Trim();
//String VALUE_ID = cells[2].InnerText.Replace("\r\n", "").Trim();
//String VALUE = cells[3].InnerText.Replace("\r\n", "").Trim();
//String DESCRIPTION = cells[4].InnerText.Replace("\r\n", "").Trim();
//String ENABLED_FLAG = cells[5].InnerText.Replace("\r\n", "").Trim();
//String LAST_UPDATE_DATE = cells[6].InnerText.Replace("\r\n", "").Trim();
//String CREATION_DATE = cells[7].InnerText.Replace("\r\n", "").Trim();
item.VALUE_SET_ID = cells[0].InnerText.Replace("\r\n", "").Trim();
item.VALUE_SET_CODE = cells[1].InnerText.Replace("\r\n", "").Trim();
item.VALUE_ID = cells[2].InnerText.Replace("\r\n", "").Trim();
item.VALUE = cells[3].InnerText.Replace("\r\n", "").Trim();
item.DESCRIPTION = cells[4].InnerText.Replace("\r\n", "").Trim();
item.ENABLED_FLAG = cells[5].InnerText.Replace("\r\n", "").Trim();
item.LAST_UPDATE_DATE = cells[6].InnerText.Replace("\r\n", "").Trim();
item.CREATION_DATE = cells[7].InnerText.Replace("\r\n", "").Trim();
//int VALUE_SET_ID = 0, VALUE_SET_CODE = 1, VALUE_ID = 2, VALUE = 3, DESCRIPTION = 4, ENABLED_FLAG = 5, LAST_UPDATE_DATE = 6, CREATION_DATE = 7;
xCVSMTDB.insertFromxCVSMT(item, "kfc_po");
}
Console.WriteLine(decodedString);
}
