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