static void writeMaster(InvoiceStruct capture)
{
//this is a mimic of FindConnectionString, with a few exceptions in the masterPDF specification.
SqlConnection conn = GetDBConnection(@"Data Source=LAPTOP-GCAMI11Q\MSSQLSERVER01;Initial Catalog=TutorialDB;Integrated Security=True");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
string path = @"..\..\collate\newmaster.pdf";
cmd.CommandText = "DELETE FROM invoiceTable2 WHERE userID=\'" + capture.userTwo + "\' AND isMaster = 1;";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO invoiceTable2 VALUES(" + capture.userTwo.ToString() + ", " + capture.finalPrice.ToString() + ", ";
cmd.CommandText = cmd.CommandText + "\'" + capture.date + "\', \'" + capture.address + "\', ";
cmd.CommandText = cmd.CommandText + "(SELECT * FROM OPENROWSET(BULK N\'";
cmd.CommandText = cmd.CommandText + Path.GetFullPath(path);
cmd.CommandText = cmd.CommandText + "\', SINGLE_BLOB) AS binary_file), ";
cmd.CommandText = cmd.CommandText + capture.userTwo.ToString();
cmd.CommandText = cmd.CommandText + ", ";
cmd.CommandText = cmd.CommandText + "1, 1);";
//build = build + ", \'[email protected]\')";
cmd.CommandText = cmd.CommandText.Replace("\n", "").Replace("\r", "");
cmd.ExecuteNonQuery();
string[] FileArray = Directory.GetFiles(@"..\..\collate", "*.pdf");
//Console.ReadLine();
for (int r = 0; r < FileArray.Length; r++)
{
File.Delete(FileArray[r]);
}
conn.Close();
}
static int FindUserId(InvoiceStruct capture, SqlCommand cmd)
{
capture.userTwo = capture.userTwo.Replace("\n", "").Replace("\r", "");//deletes \n from the address header
cmd.CommandText = "SELECT userID FROM userids WHERE companyHeading= \'" + capture.userTwo + "\';";
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
int result = reader.GetInt32(0);//Get the user ID associated with the company heading in the database
reader.Close();
return(result);
}
else
{
reader.Close(); //If the number doesn't exist, that means a new one is needed.
Random rand = new Random();
int randNum = rand.Next(1000000); //generate a random 6 digit number
cmd.CommandText = "SELECT userID FROM userids WHERE userID= \'" + randNum.ToString() + "\';";
reader = cmd.ExecuteReader();
while (reader.Read())
{
reader.Close();
randNum = rand.Next(1000000);//If the number was already selected, regenerate and check again
cmd.CommandText = "SELECT userID FROM userids WHERE userID= \'" + randNum.ToString() + "\';";
reader = cmd.ExecuteReader();
}
reader.Close();
cmd.CommandText = "INSERT INTO userids VALUES (\'" + capture.userTwo + "\', " + randNum.ToString() + ", NULL, NULL);";
cmd.ExecuteNonQuery();//add the new values to the database and return the newly generated number
return(randNum);
}
}
public static string FindSetupString(InvoiceStruct capture, string inputFile)
{
string build = "";
capture.date = capture.date.Replace("/", "-");//basically a long append to build the query string
build = "INSERT INTO invoiceTable2 VALUES(";
build = build + capture.invoiceNo.ToString() + ", ";
build = build + capture.finalPrice.ToString() + ", \'";
build = build + capture.date;
build = build + "\'";
build = build + ", \'";
build = build + capture.address + "\', ";
build = build + "(SELECT * FROM OPENROWSET(BULK N\'";
build = build + inputFile;
build = build + "\', SINGLE_BLOB) AS binary_file), ";
build = build + capture.userTwo.ToString();
build = build + ", ";
build = build + "1, 0);";
build = build.Replace("\n", "").Replace("\r", "");
return(build);
}
static void CollatePDF(int compnumber, InvoiceStruct capture)
{
fileSetup(compnumber);
bool isMiddle;
PDDocument child;//the newly addeed pdf, and the masterpdf
PDDocument master;
child = PDDocument.load(@"..\..\collate\child.pdf");
if (!File.Exists(Path.GetFullPath(@"..\..\collate\master.pdf")))
{
child.save(@"..\..\collate\newmaster.pdf");//if the master doesn't exist, the child is the master
writeMaster(capture);
child.close();
return;
}
master = PDDocument.load(@"..\..\collate\master.pdf");//if exists, load master
PDFTextStripper strip = new PDFTextStripper();
Splitter split = new Splitter();
PDFMergerUtility merge = new PDFMergerUtility();
int pageNumber = master.getNumberOfPages() + 1;
isMiddle = false;
for (int x = 1; x <= master.getNumberOfPages(); x++)
{
strip.setStartPage(x);
strip.setEndPage(x);//only extracting the specified page
string text = strip.getText(master);
string markerS = text.Substring(text.IndexOf("Invoice #") + 11, 6);
int idNo = Int32.Parse(markerS);
if (compnumber < idNo) //get the invoice number. If it's greater than the new imported one, then that's where it is spliced in.
{
isMiddle = true;
pageNumber = x;
break;
}
}
java.util.List splittedDocuments = split.split(master);
if (!isMiddle)
{
merge.appendDocument(master, child);//if the page number goes at the end, the master is collated on the child, no issues.
master.save(@"..\..\collate\newmaster.pdf");
}
else
{
PDDocument result;
result = PDDocument.load(@"..\..\blank.pdf");
for (int y = 1; y < master.getNumberOfPages(); y++)
{
if (pageNumber == y)
{
merge.appendDocument(result, child);//once we reach the right page number, the child is appended there
}
merge.appendDocument(result, (PDDocument)splittedDocuments.get(y - 1));
//we have to cast to PDDocument because of Java and .NET have clashes in syntax and we can't initialize them that way.
}
result.removePage(0);//A blank page is used at the beginning because of issues with IKVM and empty PDFs.
result.save(@"..\..\collate\newmaster.pdf");
result.close();
}
//}
child.close();
master.close();
writeMaster(capture);
}