public IPdfReportData CreatePdfReport(Report model) { var report = new PdfReport(); report.DocumentPreferences(doc => { doc.RunDirection(PdfRunDirection.RightToLeft); doc.Orientation(model.ReportType == "r2" ? PageOrientation.Landscape : PageOrientation.Portrait); doc.PageSize(PdfPageSize.A4); }); report.DefaultFonts(font => { font.Path(AppPath.ApplicationPath + @"\Content\fonts\mirza-font\Mirza.ttf", Environment.GetEnvironmentVariable("SystemRoot") + @"\fonts\tahoma.ttf"); font.Size(10); }); report.PagesFooter(footer => footer.DefaultFooter(string.Format("تاریخ تهیه گزارش:{0} - تهیه کننده:{1}", PersianDateTime.Now.ToString (PersianDateTimeFormat.FullDateFullTime), _userRepository.GetUser(HttpContext.Current.User.Identity.Name, int.Parse(CookieEncryptor.Decrypt(HttpContext.Current.Request.Cookies["LabRequest-Cookie"].Value))) .PersonFamily))); report.MainTableTemplate(template => template.BasicTemplate(BasicTemplate.SilverTemplate)); switch (model.ReportType) { case "r1": { report.PagesHeader(header => { header.CacheHeader(true); header.XHtmlHeader(rptHeader => { rptHeader.PageHeaderProperties(new XHeaderBasicProperties { RunDirection = PdfRunDirection.RightToLeft, ShowBorder = true }); rptHeader.AddPageHeader(pageHeader => { const string message = "گزارش نتایج درخواستها"; var photo = AppPath.ApplicationPath + @"\Content\images\bipc.png"; var image = string.Format("<img src='{0}' height='100px' width='150px'/>", photo); return string.Format(@"<table style='font-family:Mirza;width:100%;font-size:14pt;text-align:center'> <tr> <td>{0}</td> </tr> <tr> <td>{1}</td> </tr> </table>", image, message); }); rptHeader.GroupHeaderProperties(new XHeaderBasicProperties { RunDirection = PdfRunDirection.RightToLeft, ShowBorder = true, SpacingBeforeTable = 10f }); rptHeader.AddGroupHeader(groupHeader => { var data = groupHeader.NewGroupInfo; var pcode = data.GetSafeStringValueOf<TestResultReport>(x => x.Pcode); var unit = data.GetSafeStringValueOf<TestResultReport>(x => x.ApplicantName); var request = data.GetSafeStringValueOf<TestResultReport>(x => x.RequestNameDes); var sample = data.GetSafeStringValueOf<TestResultReport>(x => x.SampleName); var date = data.GetSafeStringValueOf<TestResultReport>(x => x.ExeDate); return string.Format(@"<table style='width:100%;font-size:10pt;font-family:Mirza'> <tr> <td style='font-family:tahoma'>{0}</td> <td>شماره پیگیری:</td> <td style='font-family:tahoma'>{1}</td> <td>عنوان درخواست:</td> </tr> <tr> <td style='font-family:tahoma'>{2}</td> <td>درخواست کننده:</td> <td style='font-family:tahoma'>{3}'</td> <td>نام نمونه:</td> </tr> <tr> <td style='font-family:tahoma'>{4}</td> <td>تاریخ انجام درخواست:</td> </tr> </table>", !string.IsNullOrEmpty(pcode) ? pcode : string.Empty, !string.IsNullOrEmpty(request) ? request : string.Empty, !string.IsNullOrEmpty(unit) ? unit : string.Empty, !string.IsNullOrEmpty(sample) ? sample : string.Empty, !string.IsNullOrEmpty(date) ? date : string.Empty); }); }); }); report.MainTablePreferences(table => { table.ColumnsWidthsType(TableColumnWidthType.Relative); table.NumberOfDataRowsPerPage(0); table.GroupsPreferences(new GroupsPreferences { GroupType = GroupType.HideGroupingColumns, RepeatHeaderRowPerGroup = true, ShowOneGroupPerPage = true, SpacingBeforeAllGroupsSummary = 5f, NewGroupAvailableSpacingThreshold = 300, SpacingAfterAllGroupsSummary = 5f }); table.SpacingAfter(4f); }); report.MainTableDataSource(datasource => { datasource.StronglyTypedList (_reportrepository.TestResult (model, PersianDateTime.Now.Year.ToString())); }); report.MainTableColumns(columns => { columns.AddColumn(column => { column.PropertyName("rowNo"); column.IsRowNumber(true); column.IsVisible(true); column.Order(0); column.Width(1); column.HeaderCell("#"); }); columns.AddColumn(column => { column.PropertyName<TestResultReport>(x => x.Pcode); column.IsVisible(true); column.Order(1); column.Width(3); column.HeaderCell("شماره پیگیری"); column.Group((g1, g2) => g1.ToString() == g2.ToString()); }); columns.AddColumn(column => { column.PropertyName<TestResultReport>(x => x.OutputName); column.IsVisible(true); column.Order(2); column.Width(3); column.HeaderCell("نام خروجی"); }); columns.AddColumn(column => { column.PropertyName<TestResultReport>(x => x.OutputValue); column.IsVisible(true); column.Order(3); column.Width(3); column.HeaderCell("نتیجه آزمایش"); }); columns.AddColumn(column => { column.PropertyName<TestResultReport>(x => x.MeasurDes); column.IsVisible(true); column.Order(4); column.Width(3); column.HeaderCell("واحد اندازه گیری"); }); }); break; } case "r2": { report.PagesHeader(header => header.DefaultHeader(defaultheader => { //header.CacheHeader(false); defaultheader.Message("لیست نتایج آزمایشهای انجام شده"); defaultheader.ImagePath(AppPath.ApplicationPath + @"Content\images\bipc.png"); defaultheader.RunDirection(PdfRunDirection.RightToLeft); })); report.MainTablePreferences(table => { table.ColumnsWidthsType(TableColumnWidthType.Relative); table.NumberOfDataRowsPerPage(0); table.GroupsPreferences(new GroupsPreferences { GroupType = GroupType.HideGroupingColumns, RepeatHeaderRowPerGroup = false, ShowOneGroupPerPage = false, SpacingBeforeAllGroupsSummary = 1f, NewGroupAvailableSpacingThreshold = 150, SpacingAfterAllGroupsSummary = 1f }); table.SpacingAfter(1f); }); report.MainTableDataSource(datasource => { datasource.StronglyTypedList (_reportrepository.TestResultlist(model, PersianDateTime.Now.Year.ToString())); }); report.MainTableColumns(columns => { columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.RowNumber); //column.PropertyName("rowNo"); //column.IsRowNumber(true); column.IsVisible(true); column.Order(0); column.Width(1); column.HeaderCell("#"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.OutputName); column.IsRowNumber(false); column.IsVisible(true); column.Order(1); column.Width(3); column.HeaderCell("نام خروجی"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.Spec); column.IsRowNumber(false); column.IsVisible(true); column.Order(2); column.Width(2); column.HeaderCell("SPEC"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.OutputValue); column.IsRowNumber(false); column.IsVisible(true); column.Order(3); column.Width(2); column.HeaderCell("نتیجه آزمایش"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.Des); column.IsRowNumber(false); column.IsVisible(true); column.Order(4); column.Width(2); column.HeaderCell("واحد اندازه گیری"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.SampleEName); column.IsRowNumber(false); column.IsVisible(true); column.Order(5); column.Width(2); column.HeaderCell("نام نمونه"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.ExeDate); column.IsRowNumber(false); column.IsVisible(true); column.Order(6); column.Width(2); column.HeaderCell("تاریخ انجام"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.ConfirmDate); column.IsRowNumber(false); column.IsVisible(true); column.Order(7); column.Width(2); column.HeaderCell("تاریخ تایید تست"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.ConfirmTime); column.IsRowNumber(false); column.IsVisible(true); column.Order(8); column.Width(2); column.HeaderCell("ساعت تایید تست"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.Pcode); column.IsRowNumber(false); column.IsVisible(true); column.Order(9); column.Width(3); column.HeaderCell("کد پیگیری"); }); columns.AddColumn(column => { column.PropertyName<UserTestResultListReport>(p => p.TestOutputId); column.IsRowNumber(false); column.IsVisible(false); column.Group((g1, g2) => Convert.ToInt32(g1) == Convert.ToInt32(g2)); }); }); break; } case "r3": { report.PagesHeader(header => header.DefaultHeader(defaultheader => { header.CacheHeader(false); defaultheader.Message("لیست وضعیت تستهای درخواست شده"); defaultheader.ImagePath(AppPath.ApplicationPath + @"Content\images\bipc.png"); defaultheader.RunDirection(PdfRunDirection.RightToLeft); })); report.MainTablePreferences(table => { table.ColumnsWidthsType(TableColumnWidthType.Relative); table.NumberOfDataRowsPerPage(0); table.GroupsPreferences(new GroupsPreferences { GroupType = GroupType.HideGroupingColumns, RepeatHeaderRowPerGroup = false, ShowOneGroupPerPage = false, //SpacingBeforeAllGroupsSummary = 1f, //NewGroupAvailableSpacingThreshold = 150, //SpacingAfterAllGroupsSummary = 1f }); table.SpacingAfter(1f); }); report.MainTableDataSource(datasource => { datasource.StronglyTypedList (_reportrepository.TestStatusList(model, PersianDateTime.Now.Year.ToString())); }); report.MainTableColumns(columns => { columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.RowNumber); //column.PropertyName("rowNo"); column.IsRowNumber(true); column.IsVisible(false); column.Order(0); column.Width(1); column.HeaderCell("#"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.ApplicantName); column.IsRowNumber(false); column.IsVisible(true); column.Order(1); column.Width(2); column.HeaderCell("واحد درخواست کننده"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.SampleName); column.IsRowNumber(false); column.IsVisible(true); column.Order(2); column.Width(3); column.HeaderCell("نام نمونه"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.RequestDate); column.IsRowNumber(false); column.IsVisible(true); column.Order(3); column.Width(2); column.HeaderCell("تاریخ درخواست"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.TestState); column.IsRowNumber(false); column.IsVisible(true); column.Order(4); column.Width(1); column.HeaderCell("وضعیت"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.Pcode); column.IsRowNumber(false); column.IsVisible(true); column.Order(5); column.Width(3); column.HeaderCell("کد پیگیری"); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.LaboratoryId); column.IsRowNumber(false); column.IsVisible(false); column.Order(6); column.Width(2); column.HeaderCell("شماره لابراتوار"); column.Group(true, (g1, g2) => g1.ToString() == g2.ToString()); }); columns.AddColumn(column => { column.PropertyName<UserTestStatuslist>(p => p.UnitId); column.IsRowNumber(false); column.IsVisible(false); column.Order(5); column.Width(2); column.HeaderCell("کد واحد"); column.Group(true, (h1, h2) => h1.ToString() == h2.ToString()); }); }); break; } case "r4": { report.PagesHeader(header => { header.CacheHeader(true); header.XHtmlHeader(rptHeader => { rptHeader.PageHeaderProperties(new XHeaderBasicProperties { RunDirection = PdfRunDirection.RightToLeft, ShowBorder = true }); rptHeader.AddPageHeader(pageHeader => { const string message = "درخواستهای انجام شده شرکتها"; var photo = AppPath.ApplicationPath + @"\Content\images\bipc.png"; var image = string.Format("<img src='{0}' height='100px' width='150px'/>", photo); return string.Format(@"<table style='font-family:Mirza;width:100%;font-size:14pt;text-align:center'> <tr> <td>{0}</td> </tr> <tr> <td>{1}</td> </tr> </table>", image, message); }); rptHeader.GroupHeaderProperties(new XHeaderBasicProperties { RunDirection = PdfRunDirection.RightToLeft, ShowBorder = true, SpacingBeforeTable = 20f }); rptHeader.AddGroupHeader(groupHeader => { var data = groupHeader.NewGroupInfo; var pcode = data.GetSafeStringValueOf<UserCompanyOperation>(x => x.Des); var unit = data.GetSafeStringValueOf<UserCompanyOperation>(x => x.ApplicantName); return string.Format(@"<table style='width:30%;font-size:10pt;font-family:Mirza'> <tr> <td style='font-family:tahoma'>{0}</td> <td>واحد:</td> <td style='font-family:tahoma'>{1}</td> <td>شرکت:</td> </tr> </table>", !string.IsNullOrEmpty(unit) ? unit : string.Empty, !string.IsNullOrEmpty(pcode) ? pcode : string.Empty); }); }); }); report.MainTablePreferences(table => { table.ColumnsWidthsType(TableColumnWidthType.Relative); table.NumberOfDataRowsPerPage(0); table.GroupsPreferences(new GroupsPreferences { GroupType = GroupType.HideGroupingColumns, RepeatHeaderRowPerGroup = false, ShowOneGroupPerPage = false, SpacingBeforeAllGroupsSummary = 1f, NewGroupAvailableSpacingThreshold = 150, SpacingAfterAllGroupsSummary = 1f }); table.SpacingAfter(1f); }); report.MainTableDataSource(datasource => { datasource.StronglyTypedList (_reportrepository.CompanyOperationList(model, PersianDateTime.Now.Year.ToString())); }); report.MainTableSummarySettings(summarySettings => { summarySettings.OverallSummarySettings("مجموع:"); //summarySettings.PreviousPageSummarySettings("Previous Page Summary"); //summarySettings.PageSummarySettings("Page Summary"); }); report.MainTableColumns(columns => { columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.RowNumber); //column.PropertyName("rowNo"); column.IsRowNumber(true); column.IsVisible(true); column.Order(0); column.Width(1); column.HeaderCell("#"); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.Pcode); column.IsRowNumber(false); column.IsVisible(true); column.Order(1); column.Width(3); column.HeaderCell("کد پیگیری"); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.TestName); column.IsRowNumber(false); column.IsVisible(true); column.Order(2); column.Width(2); column.HeaderCell("نام تست"); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.SampleName); column.IsRowNumber(false); column.IsVisible(true); column.Order(3); column.Width(2); column.HeaderCell("نام نمونه"); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.Description); column.IsRowNumber(false); column.IsVisible(true); column.Order(4); column.Width(1); column.HeaderCell("واحد اندازه گیری"); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.Price); column.IsRowNumber(false); column.IsVisible(true); column.Order(5); 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)); }); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.CompanyId); column.IsRowNumber(false); column.IsVisible(false); column.Order(6); column.Width(2); //column.HeaderCell("قیمت"); column.Group((g1, g2) => g1.ToString() == g2.ToString()); }); columns.AddColumn(column => { column.PropertyName<UserCompanyOperation>(p => p.ApplicantName); column.IsRowNumber(false); column.IsVisible(false); column.Order(7); column.Width(2); //column.HeaderCell("قیمت"); column.Group((g1, g2) => g1.ToString() == g2.ToString()); }); }); break; } } report.MainTableEvents( events => events.DataSourceIsEmpty("اطلاعات جهت نمایش یافت نشد دوباره امتحان کنید")); report.Export(export => export.ToExcel()); //var fileName = string.Format("گزارش-{0}.pdf", Guid.NewGuid().ToString("N")); report.Encrypt(encrypt => { encrypt.WithPassword("secret"); encrypt.WithPermissions(new DocumentPermissions { AllowAssembly = false, AllowCopy = false, AllowDegradedPrinting = false, AllowFillIn = false, AllowModifyAnnotations = false, AllowModifyContents = false, AllowPrinting = false, AllowScreenReaders = false }); }); var fileName = string.Format("Report-{0}.pdf", Guid.NewGuid()); return report.Generate(data => { fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); data.FlushInBrowser(fileName, FlushType.Inline); }); #region code comment //events.MainTableCreated(args => // { // var infoTable = new PdfGrid(numColumns: 1) // { // WidthPercentage = 100 // }; // infoTable.AddSimpleRow( // (cellData, properties) => // { // cellData.Value = "Show data before the main table ..."; // properties.PdfFont = events.PdfFont; // properties.RunDirection = PdfRunDirection.LeftToRight; // }); // var table = infoTable.AddBorderToTable(borderColor: BaseColor.LIGHT_GRAY, spacingBefore: 10f); // table.SpacingAfter = 10f; // args.PdfDoc.Add(table); // }); //events.ShouldSkipRow(args => //{ // var rowData = args.TableRowData; // //var previousTableRowData = args.PreviousTableRowData; // var description = rowData.FirstOrDefault(x => x.PropertyName == "Description"); // if (description != null && // description.PropertyValue.ToSafeString() == "Description Description ... 1") // { // return true; // don't render this row. // } // return false; //}); //var pageNumber = 0; //events.ShouldSkipHeader(args => //{ // pageNumber++; // if (pageNumber == 2) // { // return true; // don't render this header row. // } // return false; //}); //events.ShouldSkipFooter(args => //{ // if (pageNumber == 2) // { // return true; // don't render this footer row. // } // return false; //}); //events.MainTableAdded(maintable => //{ // var infoTable = new PdfGrid(numColumns: 2) { WidthPercentage = 80, SpacingAfter = 50, SpacingBefore = 50 }; // infoTable.AddSimpleRow( // (cellData, properties) => // { // cellData.Value = "میثم"; // properties.ShowBorder = false; // properties.BorderWidth = 0; // properties.BorderWidth = 000; // properties.PdfFont = events.PdfFont; // properties.RunDirection = PdfRunDirection.RightToLeft; // }, // (cellData, properties) => // { // cellData.Value = "سامورایی"; // properties.ShowBorder = false; // properties.BorderWidth = 0; // properties.BorderWidth = 000; // properties.PdfFont = events.PdfFont; // properties.RunDirection = PdfRunDirection.RightToLeft; // } // ); // maintable.PdfDoc.Add(infoTable.AddBorderToTable()); //}); #endregion }
public List<UserCompanyOperation> CompanyOperationList(Report report, string year) { var sqlquery = @"SELECT DISTINCT TBLTEST.TESTNAME, TBLTEST.PRICE, TBLBASEINFO_1.DES, TBLAPPLICANT.COMPANYID, TBLAPPLICANT.APPLICANTNAME, TBLBASEINFO.DES DESCRIPTION, TBLTESTREQUEST.SAMPLENAME, TBLREQUESTGEN.PCODE FROM (((((CENTRAL_LAB.TBLAPPLICANT TBLAPPLICANT INNER JOIN CENTRAL_LAB.TBLTESTREQUEST TBLTESTREQUEST ON TBLAPPLICANT.APPLICANTID = TBLTESTREQUEST.APPLICANTID) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO_1 ON TBLAPPLICANT.COMPANYID = TBLBASEINFO_1.IDREC) INNER JOIN CENTRAL_LAB.TBLREQUESTGEN TBLREQUESTGEN ON TBLTESTREQUEST.TESTREQUESTID = TBLREQUESTGEN.TESTREQUESTID) INNER JOIN CENTRAL_LAB.TBLREQTESTS TBLREQTESTS ON TBLREQUESTGEN.REQUESTGENID = TBLREQTESTS.REQUESTGENID) INNER JOIN CENTRAL_LAB.TBLTEST TBLTEST ON TBLREQTESTS.TESTID = TBLTEST.TESTID) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO ON TBLTEST.MEASUREUNIT = TBLBASEINFO.IDREC WHERE TBLREQUESTGEN.PCODE IS NOT NULL"; //"ORDER BY TBLAPPLICANT.COMPANYID,TBLAPPLICANT.APPLICANTNAME" sqlquery += !string.IsNullOrEmpty(report.ReportCompany) ? @" AND TBLAPPLICANT.COMPANYID=" + int.Parse(report.ReportCompany) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportUnit) ? @" AND TBLAPPLICANT.APPLICANTID=" + int.Parse(report.ReportUnit) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportSampleName) ? @" AND tbltestrequest.SAMPLENAME=" + string.Format("'{0}'", report.ReportSampleName) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportFromDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format(">='{0}'", report.ReportFromDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportToDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format("<='{0}'", report.ReportToDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestState) ? @" AND TBLREQTESTS.TESTSTATE=" + int.Parse(report.ReportRequestState) : string.Empty; return _context.OracleContextConnectionString .Query<UserCompanyOperation>(sqlquery) .ToList(); }
public List<TestResultReport> TestResult(Report report, string year) { var sqlquery = @"SELECT TBLAPPLICANT.APPLICANTNAME,TBLBASEINFO.DES, TBLTESTOUTPUT.OUTPUTNAME,TBLOUTPUTVALUE.OUTPUTVALUE, TBLBASEINFO_RequestName.DES RequestNameDes, TBLBASEINFO_MeaSur.DES MeaSurDes , TBLTESTOUTPUT.RATE,TBLREQTESTS.EXEDATE, TBLTESTREQUEST.SAMPLENAME,TBLREQUESTGEN.PCODE,TBLREQTESTS.TESTSTATE FROM (((((((CENTRAL_LAB.TBLTESTREQUEST TBLTESTREQUEST INNER JOIN CENTRAL_LAB.TBLAPPLICANT TBLAPPLICANT ON TBLTESTREQUEST.APPLICANTID = TBLAPPLICANT.APPLICANTID) INNER JOIN CENTRAL_LAB.TBLREQUESTGEN TBLREQUESTGEN ON TBLTESTREQUEST.TESTREQUESTID = TBLREQUESTGEN.TESTREQUESTID) INNER JOIN CENTRAL_LAB.TBLREQTESTS TBLREQTESTS ON TBLREQUESTGEN.REQUESTGENID = TBLREQTESTS.REQUESTGENID) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO_RequestName ON TBLREQTESTS.REQUESTNAMEID = TBLBASEINFO_RequestName.IDREC) INNER JOIN CENTRAL_LAB.TBLOUTPUTVALUE TBLOUTPUTVALUE ON TBLREQTESTS.REQTESTSID = TBLOUTPUTVALUE.REQTESTSID) INNER JOIN CENTRAL_LAB.TBLTESTOUTPUT TBLTESTOUTPUT ON(TBLOUTPUTVALUE.OUTPUTTESTID = TBLTESTOUTPUT.TESTOUTPUTID) AND (TBLOUTPUTVALUE.TESTID = TBLTESTOUTPUT.TESTID)) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO_MeaSur ON TBLOUTPUTVALUE.MEASUREUNITID = TBLBASEINFO_MeaSur.IDREC) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO ON TBLAPPLICANT.COMPANYID = TBLBASEINFO.IDREC WHERE TBLREQUESTGEN.PCODE iS NOT NULL"; //WHERE TBLTESTREQUEST.REQDATE >="+ string.Format("'{0}/01/01'", year); sqlquery += !string.IsNullOrEmpty(report.ReportCompany) ? @" AND TBLBASEINFO.IDREC=" + int.Parse(report.ReportCompany) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportUnit) ? @" AND TBLTESTREQUEST.APPLICANTID=" + int.Parse(report.ReportUnit) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestTitle) ? @" AND TBLREQTESTS.REQUESTNAMEID=" + int.Parse(report.ReportRequestTitle) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportSampleType) ? @" AND TBLTEST.MEASURETYPE=" + int.Parse(report.ReportSampleType) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportTestName) ? @" AND TBLREQTESTS.TESTID =" + int.Parse(report.ReportTestName) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportSampleName) ? @" AND tbltestrequest.SAMPLENAME=" + string.Format("'{0}'", report.ReportSampleName) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportFromDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format(">='{0}'", report.ReportFromDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportToDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format("<='{0}'", report.ReportToDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestState) ? @" AND TBLREQTESTS.TESTSTATE=" + int.Parse(report.ReportRequestState) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestNo) ? @" AND TBLREQUESTGEN.PCODE=" + string.Format("'{0}'", report.ReportRequestNo) : string.Empty; sqlquery += @" ORDER BY TBLREQUESTGEN.PCODE, TBLTESTOUTPUT.RATE"; return _context.OracleContextConnectionString .Query<TestResultReport>(sqlquery).ToList(); }
public List<UserTestStatuslist> TestStatusList(Report report, string year) { var sqlquery = @"SELECT DISTINCT TBLAPPLICANT.APPLICANTNAME, TBLBASEINFO.DES, TBLLABORATORY.LABORATORYID, TBLLABORATORY.LABORATORYNAME, TBLUNIT.UNITID, TBLUNIT.UNITNAME, TBLREQTESTS.TESTSTATE, TBLTESTREQUEST.SAMPLENAME, TBLREQUESTGEN.PCODE, TBLREQUESTGEN.REQUESTDATE--,ROWNUM ROWNUMBER FROM ((((((CENTRAL_LAB.TBLTESTREQUEST TBLTESTREQUEST INNER JOIN CENTRAL_LAB.TBLAPPLICANT TBLAPPLICANT ON TBLTESTREQUEST.APPLICANTID = TBLAPPLICANT.APPLICANTID) INNER JOIN CENTRAL_LAB.TBLREQUESTGEN TBLREQUESTGEN ON TBLTESTREQUEST.TESTREQUESTID = TBLREQUESTGEN.TESTREQUESTID) INNER JOIN CENTRAL_LAB.TBLBASEINFO TBLBASEINFO ON TBLAPPLICANT.COMPANYID = TBLBASEINFO.IDREC) INNER JOIN CENTRAL_LAB.TBLREQTESTS TBLREQTESTS ON TBLREQUESTGEN.REQUESTGENID = TBLREQTESTS.REQUESTGENID) INNER JOIN CENTRAL_LAB.TBLTEST TBLTEST ON TBLREQTESTS.TESTID = TBLTEST.TESTID) INNER JOIN CENTRAL_LAB.TBLUNIT TBLUNIT ON TBLTEST.UNITID = TBLUNIT.UNITID) INNER JOIN CENTRAL_LAB.TBLLABORATORY TBLLABORATORY ON TBLUNIT.LABORATORYID = TBLLABORATORY.LABORATORYID WHERE TBLREQUESTGEN.PCODE iS NOT NULL"; //WHERE TBLTESTREQUEST.REQDATE >=" + string.Format("'{0}/01/01'", year); //WHERE ROWNUM >= 1 AND ROWNUM <= 1000000"; //ORDER BY TBLLABORATORY.LABORATORYID,TBLUNIT.UNITID"; sqlquery += !string.IsNullOrEmpty(report.ReportCompany) ? @" AND TBLBASEINFO.IDREC=" + int.Parse(report.ReportCompany) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportUnit) ? @" AND TBLTESTREQUEST.APPLICANTID=" + int.Parse(report.ReportUnit) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestTitle) ? @" AND TBLREQTESTS.REQUESTNAMEID=" + int.Parse(report.ReportRequestTitle) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportSampleType) ? @" AND TBLTEST.MEASURETYPE=" + int.Parse(report.ReportSampleType) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportTestName) ? @" AND TBLREQTESTS.TESTID =" + int.Parse(report.ReportTestName) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportSampleName) ? @" AND tbltestrequest.SAMPLENAME=" + string.Format("'{0}'", report.ReportSampleName) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportFromDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format(">='{0}'", report.ReportFromDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportToDate) ? @" AND TBLREQUESTGEN.PCODEDATE" + string.Format("<='{0}'", report.ReportToDate) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestState) ? @" AND TBLREQTESTS.TESTSTATE=" + int.Parse(report.ReportRequestState) : string.Empty; sqlquery += !string.IsNullOrEmpty(report.ReportRequestNo) ? @" AND TBLREQUESTGEN.PCODE=" + string.Format("'{0}'", report.ReportRequestNo) : string.Empty; //sqlquery += @" ORDER BY ROWNUM ASC"; return _context.OracleContextConnectionString .Query<UserTestStatuslist>(sqlquery).ToList(); }