private void QueryDB() { String sqlStr = "SELECT * FROM CAMVIEW_PropertyList "; if (search_type == "pin") { sqlStr = sqlStr + " WHERE pin like '" + search_string + "%' "; } else if (search_type == "address") { sqlStr = sqlStr + " WHERE site_address like '%" + search_string + "%' "; } else if (search_type == "pinlist") { String[] pins = search_string.Split(','); for (int i = 0; i < pins.Length; i++) { pins[i] = "'" + pins[i] + "'"; } String newWhere = String.Join(",", pins); newWhere = "(" + newWhere + ")"; sqlStr = sqlStr + " WHERE pin in " + newWhere; } else if (search_type == "street") { sqlStr = sqlStr + " WHERE site_address like '%" + search_string + "%' "; } else if (search_type == "owner") { sqlStr = sqlStr + " WHERE owner like '" + search_string + "%' "; } else if (search_type == "sub") { sqlStr = ""; sqlStr = sqlStr + "SELECT DISTINCT pl.* FROM CAMVIEW_PropertyList pl "; sqlStr = sqlStr + " LEFT OUTER JOIN CAMVIEW_SubdivisionLookup sb "; sqlStr = sqlStr + " ON pl.PRPROP=sb.PAPROP "; sqlStr = sqlStr + " WHERE SUBDDS like '%" + search_string + "%'"; } else if (search_type == "bus") { sqlStr = ""; sqlStr = sqlStr + "SELECT DISTINCT PIN, Site_Address,LEDESC,HMSTD,Last_Sale,OWFLNAME as owner FROM CAMVIEW_PropertyList pl "; sqlStr = sqlStr + " LEFT OUTER JOIN dbo.PA_WBusName bs "; sqlStr = sqlStr + " ON pl.PRPROP=bs.owflprop "; sqlStr = sqlStr + " WHERE OWFLNAME like '%" + search_string + "%'"; } else if (search_type == "leg") { sqlStr = sqlStr + " WHERE ledesc like '%" + search_string + "%'"; } String pssql = sqlStr.Replace("SELECT", ""); String pageSql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY PIN ) AS RowNum, "; pageSql += pssql; pageSql += String.Format(") AS RowConstrainedResult WHERE RowNum >= {0} AND RowNum <= {1} ORDER BY RowNum ", qpage, qpage + 50); DataTable dt = new DataTable(); SqlConnection cn = null; SqlDataAdapter da = null; cn = new SqlConnection(cgis_connstr); try { cn.Open(); } catch (Exception e) { Console.WriteLine(e.ToString()); } Debug.Print(sqlStr); SqlCommand cmd = new SqlCommand(pageSql, cn); da = new SqlDataAdapter(cmd); dt = new DataTable(); DataSet dSet = new DataSet(); da.Fill(dt); // build labels //MailingLabel if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { try { MailingLabel ml = new MailingLabel(); ml.laddr = (System.String)dt.Rows[i]["Site_Address"]; ml.lname = (System.String)dt.Rows[i]["owner"]; ml.laddr_1 = (System.String)dt.Rows[i]["PEFLADDR1"]; ml.laddr_2 = (System.String)dt.Rows[i]["PEFLADDR2"]; ml.laddr_3 = (System.String)dt.Rows[i]["PEFLADDR3"]; ml.lcity = (System.String)dt.Rows[i]["PEFLCITY"]; ml.lstate = (System.String)dt.Rows[i]["PEFLST"]; ml.lcntry = (System.String)dt.Rows[i]["PEFLCNTRY"]; ml.zip = (System.String)dt.Rows[i]["PEFLZIP5"]; ml.Cleanup(); label_al.Add(ml); } catch { } } } try { cn.Close(); } catch (Exception e) { Console.WriteLine(e.ToString()); } }
private void QueryDBRaw(String sqlStr) { DataTable dt = new DataTable(); SqlConnection cn = null; SqlDataAdapter da = null; cn = new SqlConnection(cgis_connstr); try { cn.Open(); } catch (Exception e) { Console.WriteLine(e.ToString()); } Debug.Print(sqlStr); SqlCommand cmd = new SqlCommand(sqlStr, cn); da = new SqlDataAdapter(cmd); dt = new DataTable(); DataSet dSet = new DataSet(); da.Fill(dt); // build labels //MailingLabel if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { try { MailingLabel ml = new MailingLabel(); //ml.laddr = (System.String)dt.Rows[i]["Site_Address"]; ml.lname = (System.String)dt.Rows[i]["owner"]; ml.laddr_1 = (System.String)dt.Rows[i]["PEFLADDR1"]; ml.laddr_2 = (System.String)dt.Rows[i]["PEFLADDR2"]; ml.laddr_3 = (System.String)dt.Rows[i]["PEFLADDR3"]; ml.lcity = (System.String)dt.Rows[i]["PEFLCITY"]; ml.lstate = (System.String)dt.Rows[i]["PEFLST"]; ml.lcntry = (System.String)dt.Rows[i]["PEFLCNTRY"]; ml.zip = (System.String)dt.Rows[i]["PEFLZIP5"]; ml.Cleanup(); label_al.Add(ml); } catch { } } } try { cn.Close(); } catch (Exception e) { Console.WriteLine(e.ToString()); } }
void GenerateReport(PdfDocument document) { //PdfPage page = document.AddPage(); PdfPage page = document.Pages[0]; XGraphics gfx = XGraphics.FromPdfPage(page); // HACK² gfx.MUH = PdfFontEncoding.Unicode; gfx.MFEH = PdfFontEmbedding.Default; XFont font = new XFont("Verdana", 4, XFontStyle.Regular); // map image origin //gfx.DrawString("O", font, XBrushes.Red, new XRect(20, 42, 12, 12), XStringFormats.Center); //gfx.DrawString("O", font, XBrushes.Blue, new XRect(220, 42, 12, 12), XStringFormats.Center); //gfx.DrawString("O", font, XBrushes.Green, new XRect(20, 120, 12, 12), XStringFormats.Center); //XPen penn = new XPen(XColors.DarkSeaGreen, 1.5); //gfx.DrawRectangle(penn, 420, 42, 153, 63); // ovmap image origin //gfx.DrawString("X", font, XBrushes.Red, new XRect(5, 5, 798, 1144), XStringFormats.Center); //gfx.DrawString("+", font, XBrushes.Red, new XRect(5, 5, 100, 1299), XStringFormats.Center); XPen pen = new XPen(XColors.DarkTurquoise, 0.5); //gfx.DrawRectangle(pen, 20, 42, 150, 40); Document doc = new Document(); // Create a renderer and prepare (=layout) the document MigraDoc.Rendering.DocumentRenderer docRenderer = new DocumentRenderer(doc); docRenderer.PrepareDocument(); int numlabels = label_al.Count; int pagenum = 1; double xp = 0; double yp = 0; double x_marg = 20.0; double y_marg = 42.0; double w_inc = 200; double h_inc = 78; double label_width = 150.0; double rows = 3; double cols = 10; //Debug.Print(String.Format("{0} labels", numlabels)); for (int l = 0; l < numlabels; l++) { // get label MailingLabel ml = (MailingLabel)label_al[l]; // get col and row int irow = 1; int icol = 1; if ((l + 1) <= ((int)rows * (int)cols)) { try { irow = (((int)l / 3)); icol = (l % 3); } catch (Exception ex) { } //Debug.Print(String.Format("{0} -> {1} {2}", l, irow, icol)); } else if ((l + 1) > (((int)rows * (int)cols))) { //if ((l + 1) == (((int)rows * (int)cols) + 1)) int recpp = ((int)rows * (int)cols); int modpp = (l - 1) % (recpp); //Debug.Print(String.Format("{0} mod {1}", l, modpp)); if (modpp == 0) { // print second page page = document.Pages.Add(); //page = document.Pages[1]; gfx = XGraphics.FromPdfPage(page); // HACK² gfx.MUH = PdfFontEncoding.Unicode; gfx.MFEH = PdfFontEmbedding.Default; pagenum++; Debug.Print(String.Format(" pagenum {0}", pagenum)); } try { irow = (((l - ((pagenum - 1) * 30)) / 3)) - 1; icol = ((l - ((pagenum - 1) * 30)) % 3); } catch (Exception ex) { } } double xloc = x_marg + (icol * w_inc); double yloc = y_marg + (irow * h_inc); //Debug.Print(String.Format(" {0} {1}", xloc, yloc)); //gfx.DrawRectangle(pen, xloc, yloc, 150, 60); Section sec = doc.AddSection(); // Add a single paragraph with some text and format information. Paragraph para = sec.AddParagraph(); para.Format.Alignment = ParagraphAlignment.Justify; para.Format.KeepTogether = true; para.Format.Font.Name = "Verdana"; para.Format.Font.Size = Unit.FromPoint(6); para.Format.Font.Color = MigraDoc.DocumentObjectModel.Colors.Black; para.Format.Font.Bold = true; //para.AddText("Duisism odigna acipsum delesenisl "); //para.AddFormattedText("ullum in velenit", TextFormat.Bold); //para.AddText(ml.lname + "\r\n" + ml.laddr); String lbltext = ""; lbltext += ml.lname; para.AddText(ml.lname); Paragraph para2 = sec.AddParagraph(); if (ml.laddr_1 != "") { lbltext += "\r\n" + ml.laddr_1; //para.AddText(ml.laddr_1); //para.AddLineBreak(); para2.Format.Alignment = ParagraphAlignment.Justify; para2.Format.KeepTogether = true; para2.Format.Font.Name = "Verdana"; para2.Format.Font.Size = Unit.FromPoint(6); para2.Format.Font.Color = MigraDoc.DocumentObjectModel.Colors.Black; para2.Format.Font.Bold = true; para2.AddText(ml.laddr_1); } if (ml.laddr_2 != "") { lbltext += "\r\n" + ml.laddr_2; } if (ml.laddr_3 != "") { lbltext += "\r\n" + ml.laddr_3; } if (ml.lcity != "") { lbltext += "\r\n" + ml.lcity + ", " + ml.lstate; } if (ml.lcntry != "") { lbltext += "\r\n" + ml.lcntry; } if (ml.zip != "") { lbltext += "\r\n" + ml.zip; } //para.AddText(ml.lcity + ", " + ml.lstate + " " + ml.zip); //para.AddLineBreak(); //para.AddText(lbltext); Paragraph para3 = sec.AddParagraph(); para3.Format.Alignment = ParagraphAlignment.Justify; para3.Format.KeepTogether = true; para3.Format.Font.Name = "Verdana"; para3.Format.Font.Size = Unit.FromPoint(6); para3.Format.Font.Color = MigraDoc.DocumentObjectModel.Colors.Black; para3.Format.Font.Bold = true; para3.AddText(ml.lcity + ", " + ml.lstate + " " + ml.zip); //para.Format.Borders.Distance = "1pt"; //para.Format.Borders.Color = Colors.Orange; // Create a renderer and prepare (=layout) the document //MigraDoc.Rendering.DocumentRenderer docRenderer = new DocumentRenderer(doc); docRenderer.PrepareDocument(); // Render the paragraph. You can render tables or shapes the same way. 29, 705, 555, 43 docRenderer.RenderObject(gfx, XUnit.FromPoint(xloc), XUnit.FromPoint(yloc + 15.0), XUnit.FromPoint((int)label_width), para); docRenderer.RenderObject(gfx, XUnit.FromPoint(xloc), XUnit.FromPoint(yloc + 23.0), XUnit.FromPoint((int)label_width), para2); docRenderer.RenderObject(gfx, XUnit.FromPoint(xloc), XUnit.FromPoint(yloc + 31.0), XUnit.FromPoint((int)label_width), para3); } // You always need a MigraDoc document for rendering. /* * Section sec = doc.AddSection(); * // Add a single paragraph with some text and format information. * Paragraph para = sec.AddParagraph(); * para.Format.Alignment = ParagraphAlignment.Justify; * para.Format.Font.Name = "Verdana"; * para.Format.Font.Size = Unit.FromPoint(6); * para.Format.Font.Color = MigraDoc.DocumentObjectModel.Colors.DarkGray; * para.Format.Font.Color = MigraDoc.DocumentObjectModel.Colors.DarkGray; * //para.AddText("Duisism odigna acipsum delesenisl "); * //para.AddFormattedText("ullum in velenit", TextFormat.Bold); * * para.AddText("BillyBob\r\n123 Deep Elem Lane"); * * * //para.Format.Borders.Distance = "1pt"; * //para.Format.Borders.Color = Colors.Orange; * * // Create a renderer and prepare (=layout) the document * MigraDoc.Rendering.DocumentRenderer docRenderer = new DocumentRenderer(doc); * docRenderer.PrepareDocument(); * * // Render the paragraph. You can render tables or shapes the same way. 29, 705, 555, 43 * docRenderer.RenderObject(gfx, XUnit.FromPoint(38), XUnit.FromPoint(710), XUnit.FromPoint(535), para); * */ /* * double xp = 0; * double yp = 0; * int numticks = 8; * * double w_inc = page.Width.Value / (double)numticks; * double h_inc = page.Height.Value / (double)numticks; * * for (int x = 0; x < numticks; x++) * { * for (int y = 0; y < numticks; y++) * { * * xp = (double)x * w_inc; * yp = (double)y * h_inc; * * XUnit xu_x = new XUnit(xp, XGraphicsUnit.Point); * XUnit xu_y = new XUnit(yp, XGraphicsUnit.Point); * * xu_x.ConvertType(XGraphicsUnit.Inch); * xu_y.ConvertType(XGraphicsUnit.Inch); * * gfx.DrawString("+", font, XBrushes.Red, new XRect( xp, yp, 5, 5), XStringFormats.Center); * //String lbl = String.Format("{0} \r\n{1} {2}\r\n {3}", (int)xp, (int)yp, xu_x.Value, xu_y.Value); * String lbl = String.Format("{0} {1}",xu_x.Value, xu_y.Value); * gfx.DrawString(lbl, font, XBrushes.Red, new XRect( xp + 5, yp + 5, 5, 5), XStringFormats.Center); * * * } * * } */ }