using System;
using iTextSharp.text;
using PdfRpt.Core.Contracts;
using PdfRpt.Core.Helper;
using PdfRpt.FluentInterface;

namespace PdfReportSamples.CustomCellTemplate
{
    public class CustomCellTemplatePdfReport
    {
        public IPdfReportData CreatePdfReport()
        {
            return new PdfReport().DocumentPreferences(doc =>
            {
                doc.RunDirection(PdfRunDirection.RightToLeft);
                doc.Orientation(PageOrientation.Portrait);
                doc.PageSize(PdfPageSize.A4);
                doc.DocumentMetadata(new DocumentMetadata { Author = "Vahid", Application = "PdfRpt", Keywords = "Test", Subject = "Test Rpt", Title = "Test" });
                doc.Compression(new CompressionSettings
                               {
                                   CompressionLevel = CompressionLevel.BestCompression,
                                   EnableCompression = true
                               });
            })
             .DefaultFonts(fonts =>
             {
                 fonts.Path(AppPath.ApplicationPath + "\\fonts\\irsans.ttf",
                            Environment.GetEnvironmentVariable("SystemRoot") + "\\fonts\\verdana.ttf");
             })
             .PagesFooter(footer =>
             {
                 footer.DefaultFooter(DateTime.Now.ToString("MM/dd/yyyy"));
             })
             .PagesHeader(header =>
             {
                 header.DefaultHeader(defaultHeader =>
                 {
                     defaultHeader.ImagePath(AppPath.ApplicationPath + "\\Images\\01.png");
                     defaultHeader.Message("گزارش جدید ما");
                 });
             })
             .MainTableTemplate(template =>
             {
                 template.BasicTemplate(BasicTemplate.SnowyPineTemplate);
             })
             .MainTablePreferences(table =>
             {
                 table.ColumnsWidthsType(TableColumnWidthType.Relative);
                 table.MultipleColumnsPerPage(new MultipleColumnsPerPage
                 {
                     ColumnsGap = 20,
                     ColumnsPerPage = 2,
                     ColumnsWidth = 250,
                     IsRightToLeft = true,
                     TopMargin = 7
                 });
             })
             .MainTableDataSource(dataSource =>
             {
                 var table = new System.Data.DataTable("لیست حقوق");
                 table.Columns.Add("شخص", typeof(string));
                 table.Columns.Add("ماه", typeof(int));
                 table.Columns.Add("مبلغ", typeof(decimal));

                 var rnd = new Random();
                 for (int i = 0; i < 200; i++)
                     table.Rows.Add("شخص " + i, rnd.Next(1, 12), rnd.Next(400, 2000));

                 dataSource.DataTable(table);
             })
             .MainTableEvents(events =>
             {
                 events.DataSourceIsEmpty(message: "There is no data available to display.");
                 events.CellCreated(args =>
                     {
                         //change the background color of the cell based on the value
                         if (args.RowType == RowType.DataTableRow && args.Cell.RowData.Value != null && args.Cell.RowData.Value is decimal)
                         {
                             if ((decimal)args.Cell.RowData.Value <= 1000)
                                 args.Cell.BasicProperties.BackgroundColor = BaseColor.CYAN;
                         }
                     });
             })
             .MainTableSummarySettings(summary =>
             {
                 summary.OverallSummarySettings("جمع کل");
                 summary.PageSummarySettings("جمع صفحه");
                 summary.PreviousPageSummarySettings("نقل از ستون قبل");
             })
             .MainTableColumns(columns =>
             {
                 columns.AddColumn(column =>
                 {
                     column.PropertyName("rowNo");
                     column.IsRowNumber(true);
                     column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                     column.IsVisible(true);
                     column.Order(0);
                     column.Width(1);
                     column.HeaderCell("ردیف");
                 });

                 columns.AddColumn(column =>
                 {
                     column.PropertyName("شخص");
                     column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                     column.IsVisible(true);
                     column.Order(1);
                     column.Width(3);
                     column.HeaderCell("شخص");
                     column.ColumnItemsTemplate(t => t.CustomTemplate(new MyCustomCellTemplate()));
                 });

                 columns.AddColumn(column =>
                 {
                     column.PropertyName("ماه");
                     column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                     column.IsVisible(true);
                     column.Order(2);
                     column.Width(2);
                     column.HeaderCell("ماه");
                     column.ColumnItemsTemplate(template =>
                     {
                         template.TextBlock();
                         template.ConditionalFormatFormula(list =>
                         {
                             var cellValue = int.Parse(list.GetSafeStringValueOf("ماه", nullValue: "0"));
                             if (cellValue == 7)
                             {
                                 return new CellBasicProperties
                                 {
                                     PdfFontStyle = DocumentFontStyle.Bold | DocumentFontStyle.Underline,
                                     FontColor = new BaseColor(System.Drawing.Color.Brown),
                                     BackgroundColor = new BaseColor(System.Drawing.Color.Yellow)
                                 };
                             }
                             return new CellBasicProperties { PdfFontStyle = DocumentFontStyle.Normal };
                         });
                     });
                 });

                 columns.AddColumn(column =>
                 {
                     column.PropertyName("مبلغ");
                     column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                     column.IsVisible(true);
                     column.Order(3);
                     column.Width(2);
                     column.HeaderCell("مبلغ");
                     column.ColumnItemsTemplate(template =>
                     {
                         template.TextBlock();
                         template.DisplayFormatFormula(obj => obj == null ? string.Empty : string.Format("{0:n0}", obj));
                     });
                     column.AggregateFunction(aggregateFunction =>
                     {
                         aggregateFunction.NumericAggregateFunction(AggregateFunction.Sum);
                         aggregateFunction.DisplayFormatFormula(obj => obj == null ? string.Empty : string.Format("{0:n0}", obj));
                     });
                 });
             })
             .Export(export =>
                 {
                     export.ToXml();
                     export.ToExcel();
                 })
             .Generate(data => data.AsPdfFile(AppPath.ApplicationPath + "\\Pdf\\RptDataTableSample.pdf"));
        }
    }
}

using System;
using System.Collections.Generic;
using iTextSharp.text;
using iTextSharp.text.pdf;
using PdfRpt.Core.Contracts;
using PdfRpt.Core.Helper;

namespace PdfReportSamples.CustomCellTemplate
{
    public class MyCustomCellTemplate : IColumnItemsTemplate
    {
        Random _rnd = new Random();

        public void CellRendered(PdfPCell cell, Rectangle position, PdfContentByte[] canvases, CellAttributes attributes)
        {
        }

        public CellBasicProperties BasicProperties { set; get; }
        public Func<IList<CellData>, CellBasicProperties> ConditionalFormatFormula { set; get; }

        public PdfPCell RenderingCell(CellAttributes attributes)
        {
            var pdfCell = new PdfPCell();
            var table = new PdfPTable(1) { RunDirection = PdfWriter.RUN_DIRECTION_RTL };

            var filePath = AppPath.ApplicationPath + "\\Images\\" + _rnd.Next(1, 5).ToString("00") + ".png";
            var photo = PdfImageHelper.GetITextSharpImageFromImageFile(filePath);
            table.AddCell(new PdfPCell(photo, fit: false)
            {
                Border = 0,                
                VerticalAlignment = Element.ALIGN_BOTTOM,
                HorizontalAlignment = Element.ALIGN_CENTER
            });

            var name = attributes.RowData.TableRowData.GetSafeStringValueOf("شخص");
            table.AddCell(new PdfPCell(attributes.BasicProperties.PdfFont.FontSelector.Process(name))
            {
                Border = 0,
                HorizontalAlignment = Element.ALIGN_CENTER
            });

            pdfCell.AddElement(table);

            return pdfCell;
        }
    }
}

events.CellCreated(args =>
    {
         //change the background color of the cell based on the value
         if (args.RowType == RowType.DataTableRow && args.Cell.RowData.Value != null && args.Cell.RowData.Value is decimal)
         {
              if ((decimal)args.Cell.RowData.Value <= 1000)
                   args.Cell.BasicProperties.BackgroundColor = BaseColor.CYAN;
          }
      });

var productsList1 = ctx.Products.Where(product => product.Id > 1)
    .Include(product => product.Category)
    .Include(product => product.User)
    .Where(
        product =>
            product.Category.Title.Contains("t") && product.Category.Id > 1 && product.Price > 100)
    .OrderBy(product => product.Price)
    .ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent3].[Id] AS [Id1],
    [Extent3].[Name] AS [Name1],
    [Extent3].[Title] AS [Title],
    [Extent3].[UserId] AS [UserId1],
    [Extent4].[Id] AS [Id2],
    [Extent4].[Name] AS [Name2]
    FROM    [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Id]
    LEFT OUTER JOIN [dbo].[Users] AS [Extent4] ON [Extent1].[UserId] = [Extent4].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent1].[CategoryId] > 1) AND ([Extent1].[Price] > 100)
    ORDER BY [Extent1].[Price] ASC

var query2 = from product in ctx.Products
             let category = product.Category
             where product.Id > 1
             where category.Title.Contains("t") && category.Id > 1 && product.Price > 100
             select new { product, category }; 
var productsList2 = query2.ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent2].[Id] AS [Id1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[Title] AS [Title],
    [Extent2].[UserId] AS [UserId1]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent2].[Id] > 1) AND ([Extent1].[Price] > 100)

var query2ChainedVersion = ctx.Products
     .Select(product => new { product, category = product.Category })
     .Where(@t => @t.product.Id > 1)
     .Where(@t => @t.category.Title.Contains("t") && @t.category.Id > 1 && @t.product.Price > 100)
     .Select(@t => new { @t.product , @t.category });

DELETE FROM table_name
WHERE some_column=some_value

DELETE FROM Customers
WHERE Country=’USA’
GO

اما نکته مهمی که دستور Delete دارد این است کار این دستور به شکل Transactional می‌باشد. یعنی یا کلیه رکوردهایی که Country آنها USA است حذف می‌شود و یا هیچکدام از آنها. پس اگر شما 200000 رکورد داشته باشید که در این شرط صدق کند اگر وسط کار Delete (البته اگر عملیات حذف طولانی باشد) منصرف شوید می‌توانید با Cancel کردن این دستور عملیات Rollback Transaction  را به خودکار توسط SQL Server داشته باشید. در صورتیکه عملیات Cancel را انجام دهید SQL Server از Log File برای بازگرداندن مقادیر حذف شده استفاده خواهد کرد.

TRUNCATE TABLE  table_name
GO

TRUNCATE TABLE Customers
GO

TempData["id"]=id;

report.RegBusinessObject("driverReport", driver);

report.Load(Server.MapPath("~/Content/Reports/driver.mrt"));