public string getLastDoc(string Connection, string OfficeSpaceId, string DatabaseName, string TableName, int ItemId) { WorkSpace.Service WS = new WorkSpace.Service(); WS.Url = getWebUrl(); string OutputXML = WS.SelectLastDocument(Connection, OfficeSpaceId, DatabaseName, TableName, ItemId, UserName); ErrorLog = WS.getError(); return(OutputXML); }
static void Main(string[] args) { string ResultTableName = "TableData"; SqlConnection DBConnect = DBConnection.GetDBConnection(); SqlCommand DBCommand = new SqlCommand(); DBCommand.Connection = DBConnect; //DBCommand.CommandType = CommandType.Text; //DBCommand.CommandText = "select ID, BookNo, PageNo from [Page] P with(nolock)" // + " where not exists(select BookNo, PageNo from Record with(nolock)" // + " where BookNo = P.BookNo and PageNo = P.PageNo" // + " group by BookNo, PageNo)" // + " And BookNo in (select BookNo from Record with(nolock)" // + " group by BookNo)" // + " and BookNo>290" // //+ " and PageNo<152" // + " order by BookNo, PageNo"; DBCommand.CommandType = CommandType.Text; DBCommand.CommandText = "SELECT BB.ID,BB.BookNo,BB.PageNo,AA.PageNo FROM" + " (SELECT [BookNo]" + " ,[PageNo]" + " FROM [GunRegBook].[dbo].[Record] With(nolock)" + " Group By [BookNo]" + " ,[PageNo]) AA" + " RIGHT JOIN" + " (SELECT [ID],[BookNo],[PageNo] FROM [dbo].[Page]" + " WHERE [BookNo] in" + " (Select distinct([BookNo])" + " FROM [GunRegBook].[dbo].[Record] With(nolock))) BB ON AA.BookNo = BB.BookNo and AA.PageNo = BB.PageNo" + " Where AA.PageNo is null" + " ORDER BY BB.BookNo,BB.PageNo"; //DBCommand.CommandText = "SELECT *" + // " INTO #tmpData" + // " FROM (" + // " SELECT [BookNo],[GunRegID]" + // " , row_number() OVER(PARTITION BY [BookNo],[PageNo] ORDER BY [GunRegID]) AS [RowNo] FROM [GunRegBook].[dbo].[Record] With(nolock)" + // " WHERE [PageNo] = 1) C" + // " WHERE [RowNo] = 1" + // " SELECT BB.ID,BB.BookNo,BB.PageNo" + // " INTO #tmpData2" + // " FROM" + // " (SELECT [BookNo]" + // " ,[PageNo]" + // " FROM [GunRegBook].[dbo].[Record] With(nolock)" + // " Group By[BookNo]" + // " ,[PageNo]) AA" + // " RIGHT JOIN" + // " (SELECT [ID],[BookNo],[PageNo] FROM [dbo].[Page]" + // " WHERE [BookNo] in" + // " (Select distinct([BookNo])" + // " FROM [GunRegBook].[dbo].[Record] With(nolock))) BB ON AA.BookNo = BB.BookNo and AA.PageNo = BB.PageNo" + // " Where AA.PageNo is null" + // " ORDER BY BB.BookNo,BB.PageNo" + // " SELECT BB.*,AA.GunRegID FROM #tmpData AA" + // " right join #tmpData2 BB ON AA.BookNo=BB.BookNo" + // " Order By BB.BookNo,BB.PageNo" + // " DROP TABLE #tmpData" + // " DROP TABLE #tmpData2"; //DBCommand.Parameters.Add(new SqlParameter("@DateBegin", "0")); //DBCommand.Parameters.Add(new SqlParameter("@DateEnd", "2560071415")); //DBCommand.Parameters.Add(new SqlParameter("@Status", "Save")); DBCommand.CommandTimeout = 7200; SqlDataReader DBReader = default(SqlDataReader); DataTable TableResult = default(DataTable); try { DBConnect.Open(); DBReader = DBCommand.ExecuteReader(); if (DBReader.HasRows) { TableResult = new DataTable(); TableResult.Load(DBReader); TableResult.TableName = ResultTableName; } else { TableResult = new DataTable(); TableResult.Load(DBReader); TableResult.TableName = ResultTableName; } string Connection = "NextwaverDatabase"; string OfficeSpaceId = "OF.0001"; string DatabaseName = "GunBook"; string RootPathPage = "//Document/Data/Section[@ID='1']/Items[@Name='Page']"; XmlDocument xDoc; foreach (DataRow item in TableResult.Rows) { string ID = item["ID"].ToString(); WorkSpace.Service WS = new WorkSpace.Service(); xDoc = new XmlDocument(); String strDoc = WS.SelectLastDocument(Connection, OfficeSpaceId, DatabaseName, "Page", int.Parse(ID), "System"); strDoc = strDoc.Replace("null", ""); xDoc.LoadXml(strDoc); string BOOKNO = "" + GetDataXmlNode(xDoc, RootPathPage + "/Item[@Name='BookNo']"); string PAGENO = "" + GetDataXmlNode(xDoc, RootPathPage + "/Item[@Name='PageNo']"); string STATUS = "" + GetDataXmlNode(xDoc, RootPathPage + "/Item[@Name='PageStatus']"); string IMGURL = "" + GetDataXmlNode(xDoc, RootPathPage + "/Item[@Name='ImgUrl']"); if (BOOKNO == item["BookNo"].ToString() && PAGENO == item["PageNo"].ToString()) { string XPathDataRecord = "//Document/Data/Section[@ID='2']/Items[@Name='RecordInfo']"; XmlNode nodeDataGrid = xDoc.SelectSingleNode(XPathDataRecord); XmlNodeList listItem = nodeDataGrid.SelectNodes("./Item"); if (listItem.Count > 0 && listItem.Count <= 20) { NextwaverDB.NColumns NCS = new NextwaverDB.NColumns(); NCS.Add(new NextwaverDB.NColumn("PAGESTATUS", STATUS)); NextwaverDB.NWheres NWS = new NextwaverDB.NWheres(); NWS.Add(new NextwaverDB.NWhere("ID", ID)); strDoc = xDoc.OuterXml; string[] OP = WS.UpdateData(Connection, OfficeSpaceId, DatabaseName, "Page", NCS.ExportString(), NWS.ExportString(), strDoc, "System"); if (OP[0].ToUpper() == "OK") { Console.WriteLine(ID + "Book " + BOOKNO + " Page " + PAGENO + " ::Success"); } else { Console.WriteLine(ID + "Book " + BOOKNO + " Page " + PAGENO + " ::Fail"); } } } } } catch (Exception exDb) { } finally { DBConnect.Close(); } }
static void Main(string[] args) { string Connection = "NextwaverDatabase"; string OfficeSpaceId = "OF.0001"; string DatabaseName = "GunBook"; string UserName = "******"; string strHostName = System.Net.Dns.GetHostName(); string pathImgGun = System.Configuration.ConfigurationSettings.AppSettings["pathImgGun"]; bool isSuccess = false; String[] EditImageTemplate = System.IO.File.ReadAllLines("BookEdit.txt"); //NextwaverDB.NWheres NWSTest = new NextwaverDB.NWheres(); //NWSTest.Add(new NextwaverDB.NWhere("PAGESTATUS", "Create")); //DataTable dttest = WS.SelectAllColumnByWhere(Connection, OfficeSpaceId, DatabaseName, "Page", NWSTest.ExportString(), UserName); for (int index = 0; index < 20; index++) { try { WorkSpace.Service WS = new WorkSpace.Service(); string RootPathPage = "//Document/Data/Section[@ID='1']/Items[@Name='Page']"; foreach (string item in EditImageTemplate) { string Bookno = getParam(item, "BOOKNO"); NextwaverDB.NWheres NWS = new NextwaverDB.NWheres(); NWS.Add(new NextwaverDB.NWhere("BOOKNO", Bookno)); NextwaverDB.NColumns NCS_S = new NextwaverDB.NColumns(); NCS_S.Add(new NextwaverDB.NColumn("ID")); NCS_S.Add(new NextwaverDB.NColumn("BOOKNO")); NCS_S.Add(new NextwaverDB.NColumn("PAGENO")); NCS_S.Add(new NextwaverDB.NColumn("IMGURL")); string NCS_Encrypt = new EncryptDecrypt.CryptorEngine().Encrypt(NCS_S.ExportString(), true); string NWS_Encrypt = new EncryptDecrypt.CryptorEngine().Encrypt(NWS.ExportString(), true); DataTable dt = WS.SelectByColumnAndWhere(Connection, OfficeSpaceId, DatabaseName, "Page", NCS_Encrypt, NWS_Encrypt, UserName); int intPageNo = 0; int intRowInPage = 20; int intBegin = 0; int.TryParse(getParam(item, "BEGINID"), out intBegin); int.TryParse(getParam(item, "ROWINPAGE"), out intRowInPage); if (intRowInPage == 0) { intRowInPage = 20; } foreach (DataRow itemData in dt.Rows) { intPageNo = Convert.ToInt16(itemData["PAGENO"]); if (itemData["IMGURL"].ToString().Trim() != "") { XmlDocument xDocPage = new XmlDocument(); String strDoc = WS.SelectLastDocument(Connection, OfficeSpaceId, DatabaseName, "Page", int.Parse(itemData["ID"].ToString()), UserName); xDocPage.LoadXml(strDoc); string XPathDataRecord = "//Document/Data/Section[@ID='2']/Items[@Name='RecordInfo']"; XmlNode nodeDataGrid = xDocPage.SelectSingleNode(XPathDataRecord); XmlNodeList listItem = nodeDataGrid.SelectNodes("./Item"); if (listItem.Count == 0) { NextwaverDB.NColumns NColumns = new NextwaverDB.NColumns(); NColumns.Add(new NextwaverDB.NColumn("UPDATEDATE", convertDatetime(DateTime.Now))); NColumns.Add(new NextwaverDB.NColumn("UPDATEBY", UserName)); AddDataXmlNode(xDocPage, RootPathPage + "/Item[@Name='UpdateDate']", convertDatetime(DateTime.Now)); AddDataXmlNode(xDocPage, RootPathPage + "/Item[@Name='UpdateBy']", UserName); XmlAttribute att; XmlNode nodeItem; for (int i = intBegin + ((intPageNo - 1) * intRowInPage); i < (intPageNo * intRowInPage) + intBegin; i++) { nodeItem = xDocPage.CreateElement("Item"); att = xDocPage.CreateAttribute("C00"); att.Value = i.ToString(); nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C01"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C02"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C03"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C04"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C05"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C06"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C07"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C08"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C09"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C10"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C11"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C12"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C13"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C14"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C15"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C16"); att.Value = ""; nodeItem.Attributes.Append(att); att = xDocPage.CreateAttribute("C17"); att.Value = ""; nodeItem.Attributes.Append(att); nodeDataGrid.AppendChild(nodeItem); } string strDocPage = xDocPage.OuterXml; NextwaverDB.NWheres NWheres = new NextwaverDB.NWheres(); NWheres.Add(new NextwaverDB.NWhere("ID", itemData["ID"].ToString())); string[] msgOutput = WS.UpdateData(Connection, OfficeSpaceId, DatabaseName, "Page", NColumns.ExportString(), NWheres.ExportString(), strDocPage, UserName); if (msgOutput[0].ToUpper() == "OK") { System.Console.WriteLine("Update ID -->> BookNo " + itemData["BOOKNO"] + " PageNo " + itemData["PAGENO"]); } else { File.AppendAllText("Log.txt", Environment.NewLine + Environment.NewLine + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " :: Book:" + itemData["BOOKNO"] + " :: Page:" + itemData["PAGENO"] + " --> " + msgOutput[1]); } } } } } isSuccess = true; } catch (Exception ex) { File.AppendAllText("Log.txt", Environment.NewLine + Environment.NewLine + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + " --> " + ex.Message); } if (isSuccess) { break; } else { System.Threading.Thread.Sleep(500); } } }