public void InsertParentFolders()
{
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
foreach (FileElm_Parent fp in FileParent_List)
{
string stmt =
" INSERT INTO FileAdding_Folders " +
" (Folder_ID, Session_ID, FullName) " +
" VALUES " +
" (%Folder_ID, %Session_ID, '%FullName') ";
stmt = stmt.Replace("%Folder_ID",
fp.ID.ToString());
stmt = stmt.Replace("%Session_ID",
m_proc.Session.Session_ID.ToString());
stmt = stmt.Replace("%FullName",
fp.DirectoryInfo.FullName.Replace("'", "''"));
sbm1.AddStatment(stmt);
}
sbm1.ExecuteNonQuery();
}
public void Proceed()
{
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
string stmt =
" declare @Sel_ID int " +
" Set @Sel_ID = 1 " +
" delete from Documents_Sel " +
" where Sel_ID = @Sel_ID " +
" insert into Documents_Sel " +
" (Document_ID, Sel_ID) ";
stmt += this.CoreSelStmt;
string sWhere = PrepareWhereStuff();
stmt = stmt.Replace(
//"^WHERE", " ");
//"^WHERE", " WHERE CoreFiles.CoreFile_ID = 95 ");
"^WHERE", sWhere);
sbm1.AddStatment(stmt);
sbm1.ExecuteNonQuery();
}
protected void RepairIncompleteSessions()
{
ArchiveFileMgr.MoveAwayBadFiles();
string sArcPath = AppCfg.ArchivePath_Core;
DirectoryInfo arcDi = new DirectoryInfo(sArcPath);
FileInfo[] file_Arr = arcDi.GetFiles("*.cor*",
SearchOption.TopDirectoryOnly).OrderBy(f => f.Name).ToArray();
//if (file_Arr.Length > 0)
// throw new Exception("Error! .. Some incomplete sessions found!");
//Array.CreateInstance(
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
sbm1.AddStatment(" Delete from Incomplete_CoreFiles ");
CoreFile [] coreFile_Arr = new CoreFile[file_Arr.Length];
for (int i = 0; i < file_Arr.Length; i++)
{
string sName = file_Arr[i].Name;
int nID = int.Parse(sName.Substring(0, 10));
coreFile_Arr[i] = new CoreFile
{
FileInfo = file_Arr[i],
ID = nID
};
string stmt =
" INSERT INTO Incomplete_CoreFiles " +
" (Incomplete_CoreFile_ID, Name) " +
" VALUES " +
" (%Incomplete_CoreFile_ID, '%Name') ";
stmt = stmt.Replace("%Incomplete_CoreFile_ID", nID.ToString());
stmt = stmt.Replace("%Name", sName);
sbm1.AddStatment(stmt);
}
sbm1.AddStatment(" exec Sp_FixLast_FileAddingSession ");
sbm1.ExecuteNonQuery();
ArchiveFileMgr.MoveCoreTo_BackupFolders();
}
private void btnTrySqlBatchMgr_Click(object sender, RoutedEventArgs e)
{
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecQuery);
{
string delStmt =
" delete from Table_1 ";
sbm1.AddStatment(delStmt);
}
//for (int i = 0; i < 10; i++)
//for (int i = 0; i < 70000; i++)
for (int i = 0; i < 30000; i++)
{
string stmt =
" insert into Table_1 " +
" (Tabel_1_ID) " +
" Values " +
" (%Tabel_1_ID) ";
stmt = stmt.Replace("%Tabel_1_ID",
i.ToString());
sbm1.AddStatment(stmt);
}
{
string stmt =
" select * from Table_1 ";
sbm1.AddStatment(stmt);
}
sbm1.Execute();
}
public static void InspectDocs(
SearchOption a_so = SearchOption.AllDirectories)
{
string sCorePath = AppCfg.ArchivePath_Core;
DirectoryInfo di_Cor = new DirectoryInfo(sCorePath);
var fi_Coll_Core_0 = di_Cor.GetFiles("*.corPdf",
//SearchOption.AllDirectories);
a_so);
if (fi_Coll_Core_0.Length == 0)
{
return;
}
var fi_Coll_Core =
fi_Coll_Core_0.OrderBy(f => f.Name);
DataTable tbl_Docs = null;
{
string stmt =
" SELECT Document_ID, IsInspected " +
" FROM dbo.Documents " +
" WHERE (IsInspected = 0) " +
" Order by Document_ID ";
tbl_Docs = DataHelper.ExecuteSqlCmd(stmt);
}
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
int nIdx_Row = 0;
foreach (FileInfo fi in fi_Coll_Core)
{
int nDocument_ID = int.Parse(
fi.Name.Substring(0, 10));
for (; nIdx_Row < tbl_Docs.Rows.Count; nIdx_Row++)
{
int nDocID_Row =
(int)tbl_Docs.Rows[nIdx_Row]["Document_ID"];
if (nDocID_Row < nDocument_ID)
{
continue;
}
else if (nDocID_Row > nDocument_ID)
{
break;
}
//if (nIdx_Row >= tbl_Docs.Rows.Count)
//break;
// hthm tmp
//if (nDocument_ID < 394)
//continue;
int nofPages = -1;
bool bIsCorrupted = true;
PdfReader pdfReader = null;
try
{
pdfReader = new PdfReader(fi.FullName);
nofPages = pdfReader.NumberOfPages;
bIsCorrupted = false;
//pdfReader.Close();
}
catch (Exception exp)
{
}
finally
{
if (pdfReader != null)
{
pdfReader.Close();
}
}
{
string stmt =
" update Documents set " +
" NofPages = %NofPages, " +
" IsCorrupted = '%IsCorrupted', " +
" IsInspected = 'True' " +
" where Document_ID = %Document_ID ";
stmt = stmt.Replace("%NofPages",
nofPages.ToString());
stmt = stmt.Replace("%IsCorrupted",
bIsCorrupted.ToString());
stmt = stmt.Replace("%Document_ID",
nDocument_ID.ToString());
sbm1.AddStatment(stmt);
}
}
}
sbm1.ExecuteNonQuery();
}
public static void AssignMissingFiles()
{
DataTable tbl_CoreFiles = null;
{
string stmt = " select * from CoreFiles ";
tbl_CoreFiles = DataHelper.ExecuteSqlCmd(stmt);
}
if (tbl_CoreFiles.Rows.Count == 0)
{
return;
}
string sCorePath = AppCfg.ArchivePath_Core;
//string sCorePath = AppCfg.ArchivePath +
//"\\CoreFiles-Org";
DirectoryInfo di_Cor = new DirectoryInfo(sCorePath);
var fi_Coll_Core_0 = di_Cor.GetFiles("*.corPdf",
SearchOption.AllDirectories);
//a_so);
if (fi_Coll_Core_0.Length == 0)
{
return;
}
var fi_Coll_Core =
fi_Coll_Core_0.OrderBy(f => f.Name);
FileInfo[] fi_Arr_Core = fi_Coll_Core.ToArray();
int[] coreID_Disk_Arr = new int[fi_Arr_Core.Length];
for (int i = 0; i < fi_Arr_Core.Length; i++)
{
coreID_Disk_Arr[i] = int.Parse(
fi_Arr_Core[i].Name.Substring(0, 10));
}
List <DataRow> rowUpdate_List = new List <DataRow>(10000);
int nCnt_Row = 0;
int nCnt_Disk = 0;
while (nCnt_Row < tbl_CoreFiles.Rows.Count &&
nCnt_Disk < fi_Arr_Core.Length)
{
DataRow dr = tbl_CoreFiles.Rows[nCnt_Row];
int nID_Row = (int)dr["CoreFile_ID"];
int nID_Disk = coreID_Disk_Arr[nCnt_Disk];
object obj1 = dr["IsMissing"];
bool bIsMissing_Org = (bool)dr["IsMissing"];
bool bIsMissing_New;
if (nID_Row == nID_Disk)
{
bIsMissing_New = false;
nCnt_Row++;
nCnt_Disk++;
}
else if (nID_Row < nID_Disk)
{
bIsMissing_New = true;
nCnt_Row++;
}
else if (nID_Row > nID_Disk)
{
throw new Exception("Invalid nID_Disk");
}
else
{
throw new InvalidOperationException();
}
if (bIsMissing_Org != bIsMissing_New)
{
dr["IsMissing"] = bIsMissing_New;
rowUpdate_List.Add(dr);
}
}
for (; nCnt_Row < tbl_CoreFiles.Rows.Count; nCnt_Row++)
{
DataRow dr = tbl_CoreFiles.Rows[nCnt_Row];
dr["IsMissing"] = false;
rowUpdate_List.Add(dr);
}
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
foreach (DataRow dr in rowUpdate_List)
{
string stmt =
" update CoreFiles set " +
" IsMissing = '%IsMissing' " +
" where CoreFile_ID = %CoreFile_ID ";
stmt = stmt.Replace("%IsMissing",
dr["IsMissing"].ToString());
stmt = stmt.Replace("%CoreFile_ID",
dr["CoreFile_ID"].ToString());
sbm1.AddStatment(stmt);
}
sbm1.ExecuteNonQuery();
}
public void AssignCoreFile_IDs_AndUpdate_DB()
{
List <FileElm> fe_Update_List =
new List <FileElm>(this.FileElm_List.Count);
foreach (FileElm fe in this.FileElm_List)
{
if (fe.Status == FileStatus.Failed)
{
continue;
}
else if (fe.Status == FileStatus.Ready)
{
if (fe.CoreFile_ID == -1)
{
fe.CoreFile_ID = fe.ID;
fe_Update_List.Add(fe);
}
}
else if (fe.Status == FileStatus.Repeated)
{
if (fe.CoreFile_ID == -1)
{
fe.CoreFile_ID = fe.Repeated_FileElm.CoreFile_ID;
fe_Update_List.Add(fe);
}
}
else
{
throw new InvalidOperationException();
}
}
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
//StringBuilder sb1 = new StringBuilder(
//fe_Update_List.Count * 400);
foreach (FileElm fe in fe_Update_List)
{
string updateStmt =
" Update CandidateFiles set " +
" CoreFile_ID = %CoreFile_ID " +
" where CandidateFile_ID = %CandidateFile_ID; ";
updateStmt = updateStmt.Replace(
"%CoreFile_ID", fe.CoreFile_ID.ToString());
updateStmt = updateStmt.Replace(
"%CandidateFile_ID", fe.ID.ToString());
//sb1.Append(updateStmt);
sbm1.AddStatment(updateStmt);
}
sbm1.ExecuteNonQuery();
//if( fe_Update_List.Count > 0)
//DataHelper.ExecuteSqlNonQuery(sb1.ToString());
}
public void InsertCandidateFiles()
{
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
foreach (FileElm fe in FileElm_List)
{
string stmt =
" INSERT INTO CandidateFiles " +
" (CandidateFile_ID ,Session_ID, CoreFile_ID, " +
" Name ,Folder_ID ,UniqueNum_1, UniqueNum_2, " +
" UniqueNum_3, UniqueNum_4, DateModified, " +
" Size, FileAdding_Status_ID, StatusMsg, " +
" MovingAction_ID) " +
" VALUES " +
" (%CandidateFile_ID ,%Session_ID, NULL, " +
" '%Name' ,%Folder_ID ,%UniqueNum_1, %UniqueNum_2, " +
" %UniqueNum_3, %UniqueNum_4, '%DateModified', " +
" %Size, %FileAdding_Status_ID, '%StatusMsg', " +
" %MovingAction_ID) ";
stmt = stmt.Replace("%CandidateFile_ID",
fe.ID.ToString());
stmt = stmt.Replace("%Session_ID",
m_proc.Session.Session_ID.ToString());
stmt = stmt.Replace("%Name",
fe.Name.Replace("'", "''"));
stmt = stmt.Replace("%Folder_ID",
fe.ParentFolder.ID.ToString());
stmt = stmt.Replace("%UniqueNum_1",
((long)fe.UniqueNum_1).ToString());
stmt = stmt.Replace("%UniqueNum_2",
((long)fe.UniqueNum_2).ToString());
stmt = stmt.Replace("%UniqueNum_3",
((long)fe.UniqueNum_3).ToString());
stmt = stmt.Replace("%UniqueNum_4",
((long)fe.UniqueNum_4).ToString());
stmt = stmt.Replace("%DateModified",
fe.DateModified.ToString());
stmt = stmt.Replace("%Size",
fe.Size.ToString());
stmt = stmt.Replace("%FileAdding_Status_ID",
((int)fe.Status.ID).ToString());
stmt = stmt.Replace("%StatusMsg",
fe.StatusMsg);
stmt = stmt.Replace("%MovingAction_ID",
((int)fe.MovingAction.ID).ToString());
sbm1.AddStatment(stmt);
}
sbm1.ExecuteNonQuery();
}
private void btnFixProblem_10Feb12_Click(object sender, RoutedEventArgs e)
{
string sArcPath = AppCfg.ArchivePath_Core;
DirectoryInfo arcDi = new DirectoryInfo(sArcPath);
var fileColl_All = arcDi.GetFiles("*.corPdf",
SearchOption.AllDirectories);
if (fileColl_All.Length == 0)
{
return;
}
List <FileInfo> fileColl_93 = new List <FileInfo>(10000);
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
sbm1.AddStatment(
" delete from BadCoreFiles ");
foreach (FileInfo fi in fileColl_All)
{
if (fi.Length != 93)
{
continue;
}
fileColl_93.Add(fi);
int nBadCoreFile_ID =
int.Parse(fi.Name.Substring(0, 10));
string stmt =
" insert into BadCoreFiles " +
" (BadCoreFile_ID, Name, ParentFullName) " +
" Values " +
" (%BadCoreFile_ID, '%Name', '%ParentFullName') ";
stmt = stmt.Replace("%BadCoreFile_ID",
nBadCoreFile_ID.ToString());
stmt = stmt.Replace("%Name",
fi.Name.Replace("'", "''"));
stmt = stmt.Replace("%ParentFullName",
fi.DirectoryName.Replace("'", "''"));
sbm1.AddStatment(stmt);
}
sbm1.ExecuteNonQuery();
int a;
a = 0;
}
public void Finish_DB_Work()
{
//StringBuilder sb1 = new StringBuilder(
//FileElm_List.Count * 400);
// hthm tmp
//return;
SqlBatchMgr sbm1 = new SqlBatchMgr(
SqlBatchMgr.ExecutionType.ExecNonQuery);
//bool bHasValue = false;
foreach (FileElm fe in FileElm_List)
{
//if (fe.Status != FileStatus.Ready)
//continue;
string stmt =
" update CandidateFiles " +
" set FileAdding_Status_ID = %FileAdding_Status_ID, " +
" StatusMsg = '%StatusMsg', " +
" MovingAction_ID = %MovingAction_ID " +
//" where Session_ID = %Session_ID and ";
" where CandidateFile_ID = %CandidateFile_ID ";
stmt = stmt.Replace("%FileAdding_Status_ID",
((int)(fe.Status.ID)).ToString());
stmt = stmt.Replace("%StatusMsg",
fe.StatusMsg.Replace("'", "''"));
stmt = stmt.Replace("%MovingAction_ID",
((int)(fe.MovingAction.ID)).ToString());
//stmt = stmt.Replace("%Session_ID",
//m_proc.Session.Session_ID.ToString());
stmt = stmt.Replace("%CandidateFile_ID",
fe.ID.ToString());
sbm1.AddStatment(stmt);
}
{
string sTmp = string.Format(
" EXEC Sp_AssignFailed_RepeatedFiles {0} ",
m_proc.Session.Session_ID);
sbm1.AddStatment(sTmp);
}
{
string sTmp = string.Format(
" EXEC Sp_Finish_FileAdding_Session {0} ",
m_proc.Session.Session_ID);
sbm1.AddStatment(sTmp);
}
sbm1.ExecuteNonQuery();
}
