Tuesday, 10 December 2013

Get Immediate and Ultimate Parents with levels

WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl) as
   (SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name, 0 lvl
    FROM mytable
       WHERE parent_id = id -- identifies a root
UNION ALL
    SELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl + 1
    FROM r join mytable m on m.parent_id = r.id  -- joins child with parent
    WHERE m.parent_id <> m.id -- to avoid cycles
   )
SELECT * FROM r ;
 
The first part of the subquery fetches the roots and the second part connects 
the children.  
Parent is the immediate parent and
ultimate_parent, the ultimate parent. 

Thursday, 17 January 2013

How to Read TOC Headings from word document using C#

Microsoft.Office.Interop.Word.Application wordApplication = new Microsoft.Office.Interop.Word.Application();

object paramMissing = Type.Missing;wordApplication.DisplayAlerts = Microsoft.Office.Interop.Word.WdAlertLevel.wdAlertsNone;
WdExportFormat paramExportFormat = WdExportFormat.wdExportFormatPDF;


bool paramOpenAfterExport = false;WdExportOptimizeFor paramExportOptimizeFor =
WdExportOptimizeFor.wdExportOptimizeForPrint;
WdExportRange paramExportRange = WdExportRange.wdExportAllDocument;

int paramStartPage = 0;
int paramEndPage = 0;WdExportItem paramExportItem = WdExportItem.wdExportDocumentContent;

bool paramIncludeDocProps = true;
bool paramKeepIRM = true;WdExportCreateBookmarks paramCreateBookmarks =
WdExportCreateBookmarks.wdExportCreateWordBookmarks;

bool paramDocStructureTags = true;
bool paramBitmapMissingFonts = true;
bool paramUseISO19005_1 = false;
try{
// Open the source document.wordDocument = wordApplication.Documents.Open(
ref paramSourceDocPath, ref paramMissing, ref paramMissing,
ref paramMissing, ref paramMissing, ref paramMissing,
ref paramMissing, ref paramMissing, ref paramMissing,
ref paramMissing, ref paramMissing, ref paramMissing,
ref paramMissing, ref paramMissing, ref paramMissing,
ref paramMissing);
// Page Countint PageCount = wordDocument.ComputeStatistics(WdStatistic.wdStatisticPages, ref paramMissing);
string[] paragraphs = null;
int seqNo = 0;
string tocType = "";
if (wordDocument.TablesOfContents.Count > 0){

for (int i = 1; i <= wordDocument.TablesOfContents.Count; i++){

if (i == 1)tocType =
"Main";
else if (i == 2)tocType =
"Tables";
else if (i == 3)tocType =
"Figures";seqNo = 1;
Microsoft.Office.Interop.Word.Range oRangeTOC = (Microsoft.Office.Interop.Word.Range)wordDocument.TablesOfContents[i].Range;

string strDelimiters = "\r";
string strDelimitertab = "\t";
string strTOC = oRangeTOC.Text;strTOC = strTOC.TrimEnd(strDelimiters.ToCharArray());
paragraphs = strTOC.Split(strDelimiters.ToCharArray());


for (int j = 0; j < paragraphs.Length; j++){

string[] str = paragraphs[j].Split(strDelimitertab.ToCharArray());
if (str.Length > 1){

string strTOC=str[0].ToString().Replace("Figure " + seqNo.ToString() + ": ", "").Replace("Table " + seqNo.ToString() + ": ", "");
string Pageno= str[1].ToString();seqNo++;
}
}
}}}

catch (Exception ex){
}


finally{
// Close and release the Document object.if (wordDocument != null){
wordDocument.Close(
ref paramMissing, ref paramMissing,
ref paramMissing);wordDocument =
null;}

// Quit Word and release the ApplicationClass object.if (wordApplication != null){
wordApplication.Quit(
ref paramMissing, ref paramMissing,
ref paramMissing);wordApplication =
null;}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}