I developed this code which works fine in my current system which has office 2007, but when I run in word 2000 this doesn’t work. Can you please help me to code word automation using late binding. I want to create text, tables.
Word.Applicatio n WApp = new Word.Applicatio n(); // Open Microsoft Word application
object objMissing = System.Reflecti on.Missing.Valu e; // create the place holder object
Word.Document WDoc = WApp.Documents. Add(ref objMissing, ref objMissing, ref objMissing, ref objMissing); // create word document
Word.Paragraph WPara = WDoc.Paragraphs .Add(ref objMissing); // adding new paragraph
object start = 0; object end = 0;
int TableCount = 1;
String Str = "";
FormatStyle1(WP ara);
WPara.Range.Tex t = "Batch Run Report:"; // Adding Text to paragraph
WPara.Range.Ins ertParagraphAft er(); // placing cursor on new paragraph
FormatStyle2(WP ara);
WPara.Range.Tex t = "The following jobs were run on " + DateTime.Now.To ShortDateString () + " and the status is as follows:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle1(WP ara);
WPara.Range.Tex t = "Request Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'O' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Weekly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'W' order by dt_create; "; ;
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Quarterly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'Q' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Bi Monthly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'B' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Monthly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'M' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
FormatStyle1(WP ara);
WPara.Range.Tex t = "Daily Run Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'D' or cd_freq is null order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Reports:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "The following reports were run successfully and the PDF files have been generated.";
WPara.Range.Ins ertParagraphAft er();
CreateReportsTa ble(WDoc, WPara, TableCount);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
FormatStyle1(WP ara);
WPara.Range.Tex t = "Forms:";
WPara.Range.Ins ertParagraphAft er();
CreateFormsTabl e(WDoc, WPara, TableCount);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Check Details:";
WPara.Range.Ins ertParagraphAft er();
CreateCheckDeta ilsTable(WDoc, WPara, TableCount);
TableCount++;
WPara.Range.Tex t = "EFT and FTP Details:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "EFT disbursement file, the Pay issue file and check file have been generated and the FTP's were sent to bank of america and commercial mail.";
WPara.Range.Ins ertParagraphAft er();
FormatStyle1(WP ara);
WPara.Range.Tex t = "Comments:" ;
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "Batch was successful.";
WPara.Range.Ins ertParagraphAft er();
WPara.Range.Tex t = "Batch Start Time: " + DateTime.Now.To String("M") + ", 6:00 PM";
WPara.Range.Ins ertParagraphAft er();
WPara.Range.Tex t = "Batch End Time: " + DateTime.Now.To String("M") + ", " + DateTime.Now.To LongTimeString( );
WPara.Range.Ins ertParagraphAft er();
ExitDoc(WDoc, WApp, objMissing);
}
public static void CreateJobsTable (Word.Document WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 3, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f; // table within table
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
// Insert header text and format the columns.
Tbl.Columns[1].SetWidth(30, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "No.";
Word.Range rngCell;
rngCell = Tbl.Cell(1, 2).Range;
Tbl.Columns[2].SetWidth(160, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
rngCell.Text = "Job Name";
rngCell = Tbl.Cell(1, 3).Range;
Tbl.Columns[3].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
rngCell.Text = "Job Status";
}
public static void FillJobsTable(W ord.Document WDoc, int TableCount, string Str)
{
Word.Table Tbl = WDoc.Tables[TableCount];
Object beforeRow = Type.Missing;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
cnn = new SqlConnection(" Data Source=sqlp1; Database=PROD1; " + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(Str, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
if (dr.HasRows)
{
int RNow = 2;
int j = 1;
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = "" + j + "";
Tbl.Cell(RNow, 2).Range.Text = dr[0].ToString();
Tbl.Cell(RNow, 3).Range.Text = "Successful ";
TableCellFormat ting(Tbl, RNow);
RNow++;
j = j + 1;
}
if (dr != null)
{
dr.Close();
dr.Dispose();
}
}
else
{
dr.Close();
dr.Dispose();
cnn.Close();
}
}
public static void FormatStyle1(Wo rd.Paragraph WPara)
{
WPara.Range.Fon t.Name = "Times New Roman";
WPara.Range.Fon t.Size = 10;
WPara.Range.Fon t.Bold = 1;
WPara.Range.Fon t.Underline = Word.WdUnderlin e.wdUnderlineSi ngle;
}
public static void FormatStyle2(Wo rd.Paragraph WPara)
{
WPara.Range.Fon t.Name = "Times New Roman";
WPara.Range.Fon t.Size = 11;
WPara.Range.Fon t.Bold = 0;
WPara.Range.Fon t.Underline = Word.WdUnderlin e.wdUnderlineNo ne;
}
public static void TableCellFormat ting(Word.Table Tbl, int RNow)
{
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
}
public static void TableCellFormat ting2(Word.Tabl e Tbl, int RNow)
{
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
}
public static void CreateReportsTa ble(Word.Docume nt WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 5, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Report Name";
Tbl.Cell(1, 1).Range.Font.B old = 1;
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "set Nocount on; " + "declare @dt_run datetime; " + " select @dt_run = dt_run from prod1..v$parm where id_record = 'datetime';" +
" select substring(name, (charindex('_R' , name, 1)+1),(len(name )- charindex('_R', name, 1))) as RName into #temp1 from argent..ajs_job where charindex('_R', name, 1) > 0 and (name not like '%REST_%') and (name not like '%READONLY_%'); " +
" update #temp1 set RName = 'RDB15' where RName like '%RDB15_%'; " +
" delete from #temp1 where RName in ('RR3040_RECON_ FILE_REQUEST', 'REPORTS_ARCHIV E', 'REPORTS_ARCHIV E', 'REPORT'); " +
" select id_record from prod1..Tbl_Batc h_Report_Print_ Summary where DT_Report = @dt_run " +
" union " +
" select distinct id_job from prod1..v$berr where dt_run = @dt_run and id_job like 'R%' " +
" union " +
" select distinct RName from #temp1 " +
" union " +
" select substring(name, (charindex('_N' , name, 1)+1),(len(name )- charindex('_N', name, 1))) as RName from argent..ajs_job where charindex('_N', name, 1) > 0 and name like '%_NPAGE%'" +
" union " +
" select substring(name, (charindex('_S' , name, 1)+1),(len(name )- charindex('_S', name, 1))) as RName from argent..ajs_job where charindex('_S', name, 1) > 0 and name like '%_SDU%' and name not like '%SDU_OCSE%' and name not like '%SDUP_BACKUP%' and name not like '%SDU_IN%';";
cnn = new SqlConnection(" Data Source=sqlp1; Database=ARGENT ;" + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
int j = 1;
Tbl.Rows.Add(re f beforeRow);
while (dr.Read())
{
Tbl.Cell(RNow, j).Range.Font.B old = 0;
Tbl.Cell(RNow, j).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, j).SetWidth(70, Microsoft.Offic e.Interop.Word. WdRulerStyle.wd AdjustSameWidth );
Tbl.Cell(RNow, j).Range.Text = dr[0].ToString();
j++;
if (j>5)
{
j = 1;
RNow++;
Tbl.Rows.Add(re f beforeRow);
}
}
Tbl.Cell(1, 1).Merge(Tbl.Ce ll(1, 5));
Tbl.Cell(1, 1).SetWidth(350 , Microsoft.Offic e.Interop.Word. WdRulerStyle.wd AdjustSameWidth );
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Report Name";
Tbl.Cell(1, 1).Range.Font.B old = 1;
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void CreateFormsTabl e(Word.Document WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 2, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Form Name";
Tbl.Columns[2].SetWidth(50, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 2).Range.Text = "Count";
/* filling the form tables*/
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "set Nocount on;" +
"Declare @RUNDATE datetime; Select @RUNDATE = Convert(Char(10 ), Dt_Run, 121) + ' 16:00:00.000' From V$PARM Where Id_Record = 'DATETIME';" +
"select ID_FORM, count(*) Count_each into #temp1 from vfrrq where DT_CREATE_FMRQ >= @RUNDATE and cd_mode_print=' b' group by id_form order by id_form;" +
"insert into #temp1 select 'Total', count(*) from vfrrq where DT_CREATE_FMRQ > @RUNDATE and cd_mode_print=' B';" +
"select * from #temp1 order by 1;";
cnn = new SqlConnection(" Data Source=sqlp1; Database=PROD1; " + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = dr[0].ToString();
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Range.Text = dr[1].ToString();
RNow++;
}
RNow = RNow - 1;
Tbl.Cell(RNow, 1).Range.Font.B old = 1;
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void CreateCheckDeta ilsTable(Word.D ocument WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 5, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(80, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "CASE TYPE";
Tbl.Columns[2].SetWidth(92, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 2).Range.Text = "STARTING CHECK NUMBER";
Tbl.Columns[3].SetWidth(98, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 3).Range.Text = "ENDING CHECK NUMBER";
Tbl.Columns[4].SetWidth(88, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 4).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 4).Range.Text = "TOTAL NO. OF CHECKS";
Tbl.Columns[5].SetWidth(88, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 5).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 5).Range.Text = "TOTAL AMOUNT DISBURSED";
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "exec Select_RDB01A_H eader";
cnn = new SqlConnection(" Data Source=OCSECMDE V; Database=CMSTAG ING1;" + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = "IVD";
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Range.Text = dr[1].ToString();
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 3).Range.Text = dr[2].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 4).Range.Text = dr[3].ToString();
Tbl.Cell(RNow, 5).Range.Font.B old = 0;
Tbl.Cell(RNow, 5).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 5).Range.Text = dr[4].ToString();
RNow++;
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Text = "MONITORING ";
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Range.Text = dr[5].ToString();
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Text = dr[6].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Range.Text = dr[7].ToString();
Tbl.Cell(RNow, 5).Range.Font.B old = 0;
Tbl.Cell(RNow, 5).Range.Text = dr[8].ToString();
WPara.Range.Ins ertParagraphAft er();
RNow++;
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Merge(Tbl.Ce ll(RNow, 2));
Tbl.Cell(RNow, 1).Borders.Enab le = 0;
Tbl.Cell(RNow, 2).Range.Font.B old = 1;
Tbl.Cell(RNow, 2).Range.Text = "GRAND TOTAL";
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Text = dr[11].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Range.Text = dr[12].ToString();
}
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void ExitDoc(Word.Do cument WDoc, Word.Applicatio n WApp, object objMissing)
{
object FileName = "c:\\Daily Batch Run Report " + (DateTime.Now.T oShortDateStrin g()).Replace("/", "-") + ".docx";
WDoc.SaveAs(ref FileName,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing);
((Word._Documen t)WDoc).Close(r ef objMissing, ref objMissing, ref objMissing);
((Word._Applica tion)WApp).Quit (ref objMissing, ref objMissing, ref objMissing);
}
Word.Applicatio n WApp = new Word.Applicatio n(); // Open Microsoft Word application
object objMissing = System.Reflecti on.Missing.Valu e; // create the place holder object
Word.Document WDoc = WApp.Documents. Add(ref objMissing, ref objMissing, ref objMissing, ref objMissing); // create word document
Word.Paragraph WPara = WDoc.Paragraphs .Add(ref objMissing); // adding new paragraph
object start = 0; object end = 0;
int TableCount = 1;
String Str = "";
FormatStyle1(WP ara);
WPara.Range.Tex t = "Batch Run Report:"; // Adding Text to paragraph
WPara.Range.Ins ertParagraphAft er(); // placing cursor on new paragraph
FormatStyle2(WP ara);
WPara.Range.Tex t = "The following jobs were run on " + DateTime.Now.To ShortDateString () + " and the status is as follows:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle1(WP ara);
WPara.Range.Tex t = "Request Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'O' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Weekly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'W' order by dt_create; "; ;
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Quarterly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'Q' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Bi Monthly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'B' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Monthly Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'M' order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
FormatStyle1(WP ara);
WPara.Range.Tex t = "Daily Run Jobs:";
WPara.Range.Ins ertParagraphAft er();
CreateJobsTable (WDoc, WPara, TableCount);
Str = "set nocount on; " + " declare @dt_run datetime;" + " select @dt_run = dt_run from v$parm where id_record = 'datetime';" +
"select distinct id_job, isnull(dt_creat e, dt_run) dt_create into #temp from v$berr where dt_run >= @dt_run order by dt_create;" +
" alter table #temp alter column id_job char(10); " +
" insert into #temp select id_job, dt_create from batch_Job_Run_H istory where dt_run >= @dt_run order by dt_create; " +
" select upper(id_job) id_job, max(dt_create) dt_create into #temp1 from #temp where id_job like 'NEB%' or id_job like 'ARB%' group by id_job order by 2; " +
" select a.id_job, a.dt_create, b.cd_freq cd_freq from #temp1 a left outer join v$parm b on a.id_job = b.id_record where cd_freq = 'D' or cd_freq is null order by dt_create; ";
FillJobsTable(W Doc, TableCount, Str);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Reports:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "The following reports were run successfully and the PDF files have been generated.";
WPara.Range.Ins ertParagraphAft er();
CreateReportsTa ble(WDoc, WPara, TableCount);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
FormatStyle1(WP ara);
WPara.Range.Tex t = "Forms:";
WPara.Range.Ins ertParagraphAft er();
CreateFormsTabl e(WDoc, WPara, TableCount);
WPara.Range.Ins ertParagraphAft er();
TableCount++;
WPara.Range.Tex t = "Check Details:";
WPara.Range.Ins ertParagraphAft er();
CreateCheckDeta ilsTable(WDoc, WPara, TableCount);
TableCount++;
WPara.Range.Tex t = "EFT and FTP Details:";
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "EFT disbursement file, the Pay issue file and check file have been generated and the FTP's were sent to bank of america and commercial mail.";
WPara.Range.Ins ertParagraphAft er();
FormatStyle1(WP ara);
WPara.Range.Tex t = "Comments:" ;
WPara.Range.Ins ertParagraphAft er();
FormatStyle2(WP ara);
WPara.Range.Tex t = "Batch was successful.";
WPara.Range.Ins ertParagraphAft er();
WPara.Range.Tex t = "Batch Start Time: " + DateTime.Now.To String("M") + ", 6:00 PM";
WPara.Range.Ins ertParagraphAft er();
WPara.Range.Tex t = "Batch End Time: " + DateTime.Now.To String("M") + ", " + DateTime.Now.To LongTimeString( );
WPara.Range.Ins ertParagraphAft er();
ExitDoc(WDoc, WApp, objMissing);
}
public static void CreateJobsTable (Word.Document WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 3, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f; // table within table
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
// Insert header text and format the columns.
Tbl.Columns[1].SetWidth(30, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "No.";
Word.Range rngCell;
rngCell = Tbl.Cell(1, 2).Range;
Tbl.Columns[2].SetWidth(160, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
rngCell.Text = "Job Name";
rngCell = Tbl.Cell(1, 3).Range;
Tbl.Columns[3].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
rngCell.Text = "Job Status";
}
public static void FillJobsTable(W ord.Document WDoc, int TableCount, string Str)
{
Word.Table Tbl = WDoc.Tables[TableCount];
Object beforeRow = Type.Missing;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
cnn = new SqlConnection(" Data Source=sqlp1; Database=PROD1; " + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(Str, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
if (dr.HasRows)
{
int RNow = 2;
int j = 1;
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = "" + j + "";
Tbl.Cell(RNow, 2).Range.Text = dr[0].ToString();
Tbl.Cell(RNow, 3).Range.Text = "Successful ";
TableCellFormat ting(Tbl, RNow);
RNow++;
j = j + 1;
}
if (dr != null)
{
dr.Close();
dr.Dispose();
}
}
else
{
dr.Close();
dr.Dispose();
cnn.Close();
}
}
public static void FormatStyle1(Wo rd.Paragraph WPara)
{
WPara.Range.Fon t.Name = "Times New Roman";
WPara.Range.Fon t.Size = 10;
WPara.Range.Fon t.Bold = 1;
WPara.Range.Fon t.Underline = Word.WdUnderlin e.wdUnderlineSi ngle;
}
public static void FormatStyle2(Wo rd.Paragraph WPara)
{
WPara.Range.Fon t.Name = "Times New Roman";
WPara.Range.Fon t.Size = 11;
WPara.Range.Fon t.Bold = 0;
WPara.Range.Fon t.Underline = Word.WdUnderlin e.wdUnderlineNo ne;
}
public static void TableCellFormat ting(Word.Table Tbl, int RNow)
{
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
}
public static void TableCellFormat ting2(Word.Tabl e Tbl, int RNow)
{
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
}
public static void CreateReportsTa ble(Word.Docume nt WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 5, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Report Name";
Tbl.Cell(1, 1).Range.Font.B old = 1;
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "set Nocount on; " + "declare @dt_run datetime; " + " select @dt_run = dt_run from prod1..v$parm where id_record = 'datetime';" +
" select substring(name, (charindex('_R' , name, 1)+1),(len(name )- charindex('_R', name, 1))) as RName into #temp1 from argent..ajs_job where charindex('_R', name, 1) > 0 and (name not like '%REST_%') and (name not like '%READONLY_%'); " +
" update #temp1 set RName = 'RDB15' where RName like '%RDB15_%'; " +
" delete from #temp1 where RName in ('RR3040_RECON_ FILE_REQUEST', 'REPORTS_ARCHIV E', 'REPORTS_ARCHIV E', 'REPORT'); " +
" select id_record from prod1..Tbl_Batc h_Report_Print_ Summary where DT_Report = @dt_run " +
" union " +
" select distinct id_job from prod1..v$berr where dt_run = @dt_run and id_job like 'R%' " +
" union " +
" select distinct RName from #temp1 " +
" union " +
" select substring(name, (charindex('_N' , name, 1)+1),(len(name )- charindex('_N', name, 1))) as RName from argent..ajs_job where charindex('_N', name, 1) > 0 and name like '%_NPAGE%'" +
" union " +
" select substring(name, (charindex('_S' , name, 1)+1),(len(name )- charindex('_S', name, 1))) as RName from argent..ajs_job where charindex('_S', name, 1) > 0 and name like '%_SDU%' and name not like '%SDU_OCSE%' and name not like '%SDUP_BACKUP%' and name not like '%SDU_IN%';";
cnn = new SqlConnection(" Data Source=sqlp1; Database=ARGENT ;" + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
int j = 1;
Tbl.Rows.Add(re f beforeRow);
while (dr.Read())
{
Tbl.Cell(RNow, j).Range.Font.B old = 0;
Tbl.Cell(RNow, j).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, j).SetWidth(70, Microsoft.Offic e.Interop.Word. WdRulerStyle.wd AdjustSameWidth );
Tbl.Cell(RNow, j).Range.Text = dr[0].ToString();
j++;
if (j>5)
{
j = 1;
RNow++;
Tbl.Rows.Add(re f beforeRow);
}
}
Tbl.Cell(1, 1).Merge(Tbl.Ce ll(1, 5));
Tbl.Cell(1, 1).SetWidth(350 , Microsoft.Offic e.Interop.Word. WdRulerStyle.wd AdjustSameWidth );
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Report Name";
Tbl.Cell(1, 1).Range.Font.B old = 1;
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void CreateFormsTabl e(Word.Document WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 2, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(70, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "Form Name";
Tbl.Columns[2].SetWidth(50, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 2).Range.Text = "Count";
/* filling the form tables*/
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "set Nocount on;" +
"Declare @RUNDATE datetime; Select @RUNDATE = Convert(Char(10 ), Dt_Run, 121) + ' 16:00:00.000' From V$PARM Where Id_Record = 'DATETIME';" +
"select ID_FORM, count(*) Count_each into #temp1 from vfrrq where DT_CREATE_FMRQ >= @RUNDATE and cd_mode_print=' b' group by id_form order by id_form;" +
"insert into #temp1 select 'Total', count(*) from vfrrq where DT_CREATE_FMRQ > @RUNDATE and cd_mode_print=' B';" +
"select * from #temp1 order by 1;";
cnn = new SqlConnection(" Data Source=sqlp1; Database=PROD1; " + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = dr[0].ToString();
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Range.Text = dr[1].ToString();
RNow++;
}
RNow = RNow - 1;
Tbl.Cell(RNow, 1).Range.Font.B old = 1;
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void CreateCheckDeta ilsTable(Word.D ocument WDoc, Word.Paragraph WPara, int TableCount)
{
object objDefaultBehav iorWord8 = Word.WdDefaultT ableBehavior.wd Word8TableBehav ior;
object objAutoFitFixed = Word.WdAutoFitB ehavior.wdAutoF itFixed;
WPara.Range.Tab les.Add(WPara.R ange, 1, 5, ref objDefaultBehav iorWord8, ref objAutoFitFixed );
Word.Table Tbl = WDoc.Tables[TableCount];
Tbl.Range.Font. Size = 10;
Tbl.Range.Font. Name = "Times New Roman";
Tbl.Range.Parag raphFormat.Alig nment = Word.WdParagrap hAlignment.wdAl ignParagraphLef t;
Tbl.Range.Font. Underline = Word.WdUnderlin e.wdUnderlineNo ne;
Object style = "Table Grid 1";
Tbl.set_Style(r ef style);
Tbl.Spacing = 1.1f;
Tbl.ApplyStyleF irstColumn = false;
Tbl.ApplyStyleL astColumn = false;
Tbl.ApplyStyleL astRow = false;
Tbl.Columns[1].SetWidth(80, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 1).Range.Text = "CASE TYPE";
Tbl.Columns[2].SetWidth(92, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 2).Range.Text = "STARTING CHECK NUMBER";
Tbl.Columns[3].SetWidth(98, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 3).Range.Text = "ENDING CHECK NUMBER";
Tbl.Columns[4].SetWidth(88, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 4).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 4).Range.Text = "TOTAL NO. OF CHECKS";
Tbl.Columns[5].SetWidth(88, Word.WdRulerSty le.wdAdjustSame Width);
Tbl.Cell(1, 5).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor Gray40;
Tbl.Cell(1, 5).Range.Text = "TOTAL AMOUNT DISBURSED";
Object beforeRow = Type.Missing;
int RNow = 2;
SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr = null;
System.IO.Strin gWriter sw = new System.IO.Strin gWriter();
string strSQL = "exec Select_RDB01A_H eader";
cnn = new SqlConnection(" Data Source=OCSECMDE V; Database=CMSTAG ING1;" + "Integrated Security=true") ;
cnn.Open();
cmd = new SqlCommand(strS QL, cnn);
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion);
while (dr.Read())
{
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 1).Range.Text = "IVD";
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 2).Range.Text = dr[1].ToString();
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 3).Range.Text = dr[2].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 4).Range.Text = dr[3].ToString();
Tbl.Cell(RNow, 5).Range.Font.B old = 0;
Tbl.Cell(RNow, 5).Shading.Back groundPatternCo lor = Microsoft.Offic e.Interop.Word. WdColor.wdColor White;
Tbl.Cell(RNow, 5).Range.Text = dr[4].ToString();
RNow++;
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Range.Font.B old = 0;
Tbl.Cell(RNow, 1).Range.Text = "MONITORING ";
Tbl.Cell(RNow, 2).Range.Font.B old = 0;
Tbl.Cell(RNow, 2).Range.Text = dr[5].ToString();
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Text = dr[6].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Range.Text = dr[7].ToString();
Tbl.Cell(RNow, 5).Range.Font.B old = 0;
Tbl.Cell(RNow, 5).Range.Text = dr[8].ToString();
WPara.Range.Ins ertParagraphAft er();
RNow++;
Tbl.Rows.Add(re f beforeRow);
Tbl.Cell(RNow, 1).Merge(Tbl.Ce ll(RNow, 2));
Tbl.Cell(RNow, 1).Borders.Enab le = 0;
Tbl.Cell(RNow, 2).Range.Font.B old = 1;
Tbl.Cell(RNow, 2).Range.Text = "GRAND TOTAL";
Tbl.Cell(RNow, 3).Range.Font.B old = 0;
Tbl.Cell(RNow, 3).Range.Text = dr[11].ToString();
Tbl.Cell(RNow, 4).Range.Font.B old = 0;
Tbl.Cell(RNow, 4).Range.Text = dr[12].ToString();
}
if (dr != null)
{
dr.Close();
dr.Dispose();
}
strSQL = "";
cnn.Close();
}
public static void ExitDoc(Word.Do cument WDoc, Word.Applicatio n WApp, object objMissing)
{
object FileName = "c:\\Daily Batch Run Report " + (DateTime.Now.T oShortDateStrin g()).Replace("/", "-") + ".docx";
WDoc.SaveAs(ref FileName,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing, ref objMissing,
ref objMissing);
((Word._Documen t)WDoc).Close(r ef objMissing, ref objMissing, ref objMissing);
((Word._Applica tion)WApp).Quit (ref objMissing, ref objMissing, ref objMissing);
}