public Array getWardBankDocs(WardDocument[] documentArray, OracleConnection connection, decimal ward, string enddate)
{
/*
*
* DOCUMENTS
* DOCNUM 1234
* DOCTYPE OTHGL
* WARDNUM 4962
* VENDORNUM 0
* DOCID 07-2281
* DOCDATE 26-SEP-
* DATESCANNED 02-OCT-07
* USERSCANNED OPS$OPERATOR
* NOTES null
* MIMETYPE application/pdf
* BASEPATH 0
* DOCPATH 2007\10\2\4962-OTHLGL-0--1234.pdf
*
*/
string strSQL = "SELECT BASEPATH,DOCPATH,DOCNUM,DOCTYPE,WARDNUM,STORAGEPATH,replace(replace(REPLACE(REPLACE(WARD_NAME,',','_'),' ','_'),'\"',''),'*','') WARD_NAME ";
strSQL += "FROM DOCUMENTS d, docpath dp,WARD W WHERE WARDNUM =:p_ward and ";
strSQL += "DP.PATHNUM = D.BASEPATH and W.WARD_NUMBER = D.WARDNUM ";
strSQL += "AND (doctype = 'MELLON' OR doctype = 'BANK' or doctype = 'TRUST' Or Doctype = 'OTHTST') ";
strSQL += "AND trunc(docdate) = (SELECT MAX(docdate) FROM documents WHERE (doctype = 'MELLON' OR doctype = 'BANK' or doctype = 'TRUST' Or Doctype = 'OTHTST') ";
strSQL += "AND extract(year from docdate) = extract(year from to_date(:p_enddate,'mm/dd/yyyy')) AND wardnum = W.WARD_NUMBER)";
OracleCommand cmd = null;
OracleDataReader rs = null;
try
{
cmd = new OracleCommand();
cmd.CommandText = strSQL;
cmd.Connection = connection;
OracleParameter p_ward = new OracleParameter();
p_ward.Value = Convert.ToDecimal(ward);
cmd.Parameters.Add(p_ward);
OracleParameter p_enddate = new OracleParameter();
p_enddate.Value = enddate;
cmd.Parameters.Add(p_enddate);
rs = cmd.ExecuteReader();
Console.WriteLine("Reading Ward Documents");
WardDocument wardDoc;
decimal basepath;
string docpath;
decimal docnum;
string doctype;
decimal wardnum;
string storagepath;
string wardName;
int kount = 0;
if (rs.HasRows)
{
while (rs.Read())
{
basepath = rs.GetDecimal(0);
docpath = rs.GetString(1);
docnum = rs.GetDecimal(2);
doctype = rs.GetString(3);
wardnum = rs.GetDecimal(4);
storagepath = rs.GetString(5);
wardName = rs.GetString(6);
wardDoc = new WardDocument(basepath, docpath, docnum, doctype, wardnum, storagepath, wardName);
documentArray[kount] = wardDoc;
kount++;
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception Caught {0}", e.ToString());
rs.Dispose();
cmd.Dispose();
Environment.Exit(-1);
}
rs.Dispose();
cmd.Dispose();
return(documentArray);
}
public Array getLetterOfGuardianship(WardDocument[] documentArray, OracleConnection connection, decimal ward)
{
/*
*
* DOCUMENTS
* DOCNUM 1234
* DOCTYPE GRDLET
* WARDNUM 4962
* VENDORNUM 0
* DOCID 07-2281
* DOCDATE 26-SEP-
* DATESCANNED 02-OCT-07
* USERSCANNED OPS$OPERATOR
* NOTES null
* MIMETYPE application/pdf
* BASEPATH 0
* DOCPATH 2007\10\2\4962-OTHANN-0--1234.pdf
*
*/
string strSQL = "select q.* from ( ";
strSQL += "SELECT docnum ,doctype ,WARDNUM,vendornum,DOCID,DOCDATE,DATESCANNED,USERSCANNED,NOTES,MIMETYPE ";
strSQL += ",BASEPATH,(select storagepath from docpath where pathnum = basepath) ||''||docpath docpath FROM DOCUMENTS ";
strSQL += "where DOCTYPE = 'GRDLET' and docid = 24 and wardnum = :p_ward order by docdate desc) q where rownum = 1";
/*
* strSQL += " union select q.* from ( ";
* strSQL += "SELECT docnum ,doctype ,WARDNUM,vendornum,DOCID,DOCDATE,DATESCANNED,USERSCANNED,NOTES,MIMETYPE ";
* strSQL += ",BASEPATH,(select storagepath from docpath where pathnum = basepath) ||''||docpath docpath FROM DOCUMENTS ";
* strSQL += " where DOCTYPE = 'CJIS' and wardnum = :p_ward order by docdate desc) q where rownum = 1 order by 2 asc";
*/
OracleCommand cmd = null;
OracleDataReader rs = null;
try
{
cmd = new OracleCommand();
cmd.CommandText = strSQL;
cmd.Connection = connection;
OracleParameter p_ward = new OracleParameter();
p_ward.Value = Convert.ToDecimal(ward);
cmd.Parameters.Add(p_ward);
rs = cmd.ExecuteReader();
Console.WriteLine("Looking for Letter of Guardianship");
WardDocument wardDoc;
decimal basepath;
string docpath;
decimal docnum;
string doctype;
decimal wardnum;
string storagepath;
string wardName;
int kount = 0;
if (rs.HasRows)
{
while (rs.Read())
{
basepath = rs.GetDecimal(10);
docpath = rs.GetString(11);
docnum = rs.GetDecimal(0);
doctype = rs.GetString(1);
wardnum = rs.GetDecimal(2);
storagepath = rs.GetString(11);
wardName = "Ward Name";
wardDoc = new WardDocument(basepath, docpath, docnum, doctype, wardnum, storagepath, wardName);
documentArray[kount] = wardDoc;
kount++;
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception Caught {0}", e.ToString());
rs.Dispose();
cmd.Dispose();
Environment.Exit(-1);
}
rs.Dispose();
cmd.Dispose();
return(documentArray);
}
public Array getWardPhysicianReport(WardDocument[] documentArray, OracleConnection connection, decimal ward)
{
/*
*
* DOCUMENTS
* DOCNUM 1234
* DOCTYPE OTHANN
* WARDNUM 4962
* VENDORNUM 0
* DOCID 07-2281
* DOCDATE 26-SEP-
* DATESCANNED 02-OCT-07
* USERSCANNED OPS$OPERATOR
* NOTES null
* MIMETYPE application/pdf
* BASEPATH 0
* DOCPATH 2007\10\2\4962-OTHANN-0--1234.pdf
*
*/
string strSQL = "SELECT BASEPATH,DOCPATH,DOCNUM,DOCTYPE,WARDNUM,STORAGEPATH,REPLACE(REPLACE(REPLACE(REPLACE(WARD_NAME,',','_'),' ','_'),'\"',''),'*','') WARD_NAME ";
strSQL += "FROM DOCUMENTS d, docpath dp,WARD W WHERE WARDNUM =:p_ward and DOCID = '223' and DP.PATHNUM = D.BASEPATH AND W.WARD_NUMBER = D.WARDNUM ";
strSQL += " And TRUNC(DOCDATE) > sysdate - (SELECT company_docltr_days FROM company)";
OracleCommand cmd = null;
OracleDataReader rs = null;
try
{
cmd = new OracleCommand();
cmd.CommandText = strSQL;
cmd.Connection = connection;
OracleParameter p_ward = new OracleParameter();
p_ward.Value = Convert.ToDecimal(ward);
cmd.Parameters.Add(p_ward);
rs = cmd.ExecuteReader();
Console.WriteLine("Reading Ward Physicians Report Documents");
WardDocument wardDoc;
decimal basepath;
string docpath;
decimal docnum;
string doctype;
decimal wardnum;
string storagepath;
string wardName;
int kount = 0;
if (rs.HasRows)
{
while (rs.Read())
{
basepath = rs.GetDecimal(0);
docpath = rs.GetString(1);
docnum = rs.GetDecimal(2);
doctype = rs.GetString(3);
wardnum = rs.GetDecimal(4);
storagepath = rs.GetString(5);
wardName = rs.GetString(6);
wardDoc = new WardDocument(basepath, docpath, docnum, doctype, wardnum, storagepath, wardName);
documentArray[kount] = wardDoc;
kount++;
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception Caught {0}", e.ToString());
rs.Dispose();
cmd.Dispose();
Environment.Exit(-1);
}
rs.Dispose();
cmd.Dispose();
return(documentArray);
}
private static void createCJISPDF(Ward ward, DocHelper dh, string outputFolder, OracleConnection connection, string logFile)
{
decimal guardianLetterCount = 0;
decimal cjisMemoCount = 0;
WardDocument[] wardDocArray;
string pdfTargetFolder = null;
PdfDocument profilePDF = null;
MergeDocument document = new MergeDocument();
string cjisProfileURL = ConfigurationManager.AppSettings["CJIS_ProfileURL"];
guardianLetterCount = dh.getguardianLetterCount(ward.getWardNumber(), connection); // count all of the documents we need, doctype "GRDLET"
wardDocArray = new WardDocument[(int)guardianLetterCount]; // create an array large enough to contain all of the necessary documents
pdfTargetFolder = System.IO.Path.Combine(outputFolder, ward.getWardName() + "_" + ward.getWardNumber());
if (!System.IO.Directory.Exists(pdfTargetFolder)) // create output directory as needed for each ward
{
System.IO.Directory.CreateDirectory(pdfTargetFolder);
}
string profileDocumentPath = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_Profile.pdf"); // the document created by Jasper
/* C R E A T E T H E 3 B A S E C J I S R E P O R T S */
WebClient client = new WebClient();
string url = cjisProfileURL + ward.getWardNumber();
client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
client.DownloadFile(url, profileDocumentPath);
profilePDF = new PdfDocument(profileDocumentPath);
document = new MergeDocument(profilePDF);
if (wardDocArray.Length > 0 && ward.getStatus() != "X") // if we have a letter of Guardianship
{
dh.getLetterOfGuardianship(wardDocArray, connection, ward.getWardNumber()); // populate wardDocArray with the documents
Console.WriteLine("Ward has {0} Letter(s) of Guardianship to copy", guardianLetterCount);
// B E G I N M E R G E O F P D F D O C U M E N T S
Console.WriteLine("output folder is: {0}", pdfTargetFolder);
// loop over any additional documents here and merge with the Ward Profile pdf
try
{
foreach (WardDocument w in wardDocArray)
{
if (File.Exists(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath())))
{
document.Append(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath()));
}
else
{
System.IO.File.AppendAllText(@logFile, "Could not find ward document: " + w.getStoragePath() + "\\" + w.getDocPath() + " - " + DateTime.Now + "\r\n");
}
}
}
catch (Exception e)
{
Console.WriteLine("Letters of Guardianship should exist but could not be found for ward {0}", ward.getWardNumber());
}
}
else
{
Console.WriteLine("LOG: => No Letters of Guardianship found for ward {0}", ward.getWardNumber());
System.IO.File.AppendAllText(@logFile, "Ward: " + ward.getWardNumber() + " Missing Letter of Gurardianship or CJIS Memorandum " + DateTime.Now + "\r\n");
}
document.Draw(System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_CJISMemo_" + document.Pages.Count.ToString() + ".pdf")); // name of the final output document
File.Delete(profileDocumentPath); // delete the profile.pdf
dh.updateCJISStatus(ward.getWardNumber(), guardianLetterCount, cjisMemoCount, connection, ward.getStatus());
connection.Close();
}
} /* end of createAccountingPDF */
private static void createAnnualPlanPDF(Ward ward, DocHelper dh, string reportURL, string outputFolder, string StartDate, string EndDate, OracleConnection connection, string logFile)
{
decimal physicianDocCount = 0;
WardDocument[] wardDocArray;
string[] types = null;
types = new string[] { "pdf" };
string pdfTargetFolder = null;
Console.WriteLine("Processing Annual Plan");
physicianDocCount = dh.getPhysicianDocumentCount(ward.getWardNumber(), connection); // count all of the documents we need, doctype "MELLON" and "BANK"
wardDocArray = new WardDocument[(int)physicianDocCount];
pdfTargetFolder = System.IO.Path.Combine(outputFolder, ward.getWardName() + "_" + ward.getFileNumber());
if (!System.IO.Directory.Exists(pdfTargetFolder)) // create output directory as needed for each ward
{
System.IO.Directory.CreateDirectory(pdfTargetFolder);
}
string document1 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_deleteME.pdf"); // the document created by the RDL component
string document2;
MergeDocument document;
/* C R E A T E T H E B A S E A N N U A L P L A N R E P O R T */
WebClient client = new WebClient();
string url = reportURL + ward.getWardNumber();;
client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
client.DownloadFile(url, document1);
if (physicianDocCount > 0)
{
dh.getWardPhysicianReport(wardDocArray, connection, ward.getWardNumber()); // populate wardDocArray with the documents
Console.WriteLine("Ward has {0} Physician Report(s) to copy", physicianDocCount);
dh.updatePlanStatus(ward.getWardNumber(), connection);
connection.Close();
// create the AnnualAccounting PDF document
document = new MergeDocument(document1);
// B E G I N M E R G E O F P D F D O C U M E N T S
Console.WriteLine("output folder is: {0}", pdfTargetFolder);
// loop over any additional documents here and merge with the AnnualAccouting pdf
foreach (WardDocument w in wardDocArray)
{
if (File.Exists(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath())))
{
document.Append(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath()));
}
else
{
System.IO.File.AppendAllText(@"AnnualAccountingLog.txt", "Could not find ward document: " + w.getStoragePath() + "\\" + w.getDocPath() + " - " + DateTime.Now + "\r\n");
}
}
ceTe.DynamicPDF.PageList pl = new PageList(); // find out how many pages the resulting PDF document has so we can add the count to the final filename
pl = document.Pages;
Console.WriteLine("File has {0} Pages", pl.Count);
document2 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_AnnualPlan_" + pl.Count.ToString() + ".pdf"); // name of the final output document
document.Draw(document2);
File.Delete(document1); // delete the 9999_temporary.pdf
document2 = null;
}
else
{
Console.WriteLine("LOG: => No Physician Documents found for ward {0}", ward.getWardNumber());
System.IO.File.AppendAllText(@logFile, "Ward: " + ward.getWardNumber() + " has no Doctor Reports to process " + DateTime.Now + "\r\n");
ceTe.DynamicPDF.PageList pl = new PageList();
document = new MergeDocument(document1);
pl = document.Pages;
document2 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_AnnualPlan_" + pl.Count.ToString() + ".pdf"); // name of the final output document
document.Draw(document2);
File.Delete(document1); // delete the 9999_temporary.pdf
document2 = null;
dh.updatePlanStatus(ward.getWardNumber(), connection);
connection.Close();
}
}
} /* end of main */
private static void createAccountingPDF(ReportRunner rr, Ward ward, DocHelper dh, string[] files, string outputFolder, string StartDate, string EndDate, OracleConnection connection)
{
decimal wardDocCount = 0;
WardDocument[] wardDocArray;
string[] types = null;
types = new string[] { "pdf" };
string pdfTargetFolder = null;
wardDocCount = dh.getBankStatementCount(ward.getWardNumber(), connection, EndDate); // count all of the documents we need, doctype "MELLON" and "BANK"
wardDocArray = new WardDocument[(int)wardDocCount];
pdfTargetFolder = System.IO.Path.Combine(outputFolder, ward.getWardName() + "_" + ward.getFileNumber());
if (!System.IO.Directory.Exists(pdfTargetFolder)) // create output directory as needed for each ward
{
System.IO.Directory.CreateDirectory(pdfTargetFolder);
}
string document1 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_temporary.pdf"); // the document created by the RDL component
string document2;
MergeDocument document;
if (wardDocCount > 0)
{
dh.getWardBankDocs(wardDocArray, connection, ward.getWardNumber(), EndDate); // populate wardDocArray with the documents
Console.WriteLine("Ward has {0} Bank document(s) to copy", wardDocCount);
// rc.returnCode = returnCode; // MBG 08/15/15
rr.DoRender(pdfTargetFolder, files, types, ward.getWardNumber().ToString(), StartDate, EndDate); // create the AnnualAccounting PDF document
document = new MergeDocument(document1);
document = new MergeDocument(System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_temporary.pdf"));
// B E G I N M E R G E O F P D F D O C U M E N T S
Console.WriteLine("output folder is: {0}", pdfTargetFolder);
// loop over any additional documents here and merge with the AnnualAccouting pdf
foreach (WardDocument w in wardDocArray)
{
if (File.Exists(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath())))
{
document.Append(System.IO.Path.Combine(w.getStoragePath(), w.getDocPath()));
}
else
{
System.IO.File.AppendAllText(@"P:\Annuals\AnnualAccountingLog.txt", "Could not find ward document: " + w.getStoragePath() + "/" + w.getDocPath() + " - " + DateTime.Now + "\r\n");
}
}
ceTe.DynamicPDF.PageList pl = new PageList(); // find out how many pages the resulting PDF document has so we can add the count to the final filename
pl = document.Pages;
Console.WriteLine("File has {0} Pages", pl.Count);
document2 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_AnnualAccounting_" + pl.Count.ToString() + ".pdf"); // name of the final output document
document.Draw(document2);
File.Delete(document1); // delete the 9999_temporary.pdf
document2 = null;
}
else
{
Console.WriteLine("LOG: => No Bank Documents found for ward {0}", ward.getWardNumber());
System.IO.File.AppendAllText(@"C:\Annuals\AnnualAccountingLog.txt", "Ward: " + ward.getWardNumber() + " has no Bank Statements to process " + DateTime.Now + "\r\n");
// rc.returnCode = returnCode; // MBG 08/15/15
rr.DoRender(pdfTargetFolder, files, types, ward.getWardNumber().ToString(), StartDate, EndDate);
ceTe.DynamicPDF.PageList pl = new PageList();
document = new MergeDocument(document1);
pl = document.Pages;
document2 = System.IO.Path.Combine(pdfTargetFolder, ward.getWardNumber() + "_AnnualAccounting_" + pl.Count.ToString() + ".pdf"); // name of the final output document
document.Draw(document2);
File.Delete(document1); // delete the 9999_temporary.pdf
document2 = null;
connection.Close();
}
dh.updateAnnualAccounting(ward.getWardNumber(), connection);
connection.Close();
} /* end of createAccountingPDF */
