Bell Archives - IT Blog

jQuery DataTables renders table into two parts: header, <div class="dataTables_scrollHeadInner"><div class="dataTable"> body, <div class="dataTables_scrollBody"> DataTables measures the current screen size and set the fixed width. In this case when resizing the browser, the table header and body are not resized automatically. The solution is to enforce their width to be set in percentage. The following code is the final. Note: file: \WebSites\WebBusinessSuite\Scripts\Main\BusinessSuit.css

2018-10-24 0 Comments 2888 Views 0 Like IT Team Read more

Taking report from web page function sendReport() { var dataInput = {}; reportBody = $(".dataTables_scroll"); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Main/BusinessSuite.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/BootStrap/css/bootstrap.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Css/Table/tableenhacements.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryUI/jquery-ui.css" type="text/css" />'); dataInput.ReportName = $("#ddlReports option:selected").attr("data-report-name"); dataInput.ReportPage = reportBody.html().replace(/height: 0px;/g, 'height: 30px;padding:5px 0 !important;') .replace(/dataTables_scrollHeadInner"/g, 'dataTables_scrollHeadInner" style="display:none"') .replace(/<table/g, '<table style="font-size:12px !important;"') .replace(/<th/g, '<th style="align:left;"') .replace(/<div class="dataTables_scrollHeadInner".*?(<\/div>)/gm,''); var ajaxParameters = { methodUrl: "Mail/SendReport", dataInput: dataInput }; new BaseClass().AjaxCall( ajaxParameters, function () { }, function (data) { } ); return false; Stored Proc: ALTER proc [dbo].[usp_BS_SendMail]( @PRM_LAN_ID VARCHAR(100)='', @REPORT_NAME nvarchar(150) =null, @REPORT_PAGE nvarchar(max) =null ) as if coalesce(@REPORT_NAME,'')='' or coalesce(@REPORT_PAGE,'')='' return; declare @typeid int,@subject nvarchar(255); select @typeid=ReportTypeID from [dbo].[tbl_BS_Reports_Configuration] where ReportName = @REPORT_NAME insert dbo.tbl_ReportPage (ReportTypeID, ReportName,ReportPage,CreatedBy) values (@typeid,@REPORT_NAME,@REPORT_PAGE,@PRM_LAN_ID) set @subject = 'Report: ' + @REPORT_NAME; EXEC msdb.dbo.sp_send_dbmail @profile_name = null, --@recipients = '"Vat, Moh" <abc.vat@ball.ca>;"Pill, Balak" <def.pill@ball.ca>', @recipients = 'xxxxx@gmail.com', @body = @REPORT_PAGE, @body_format = 'HTML', @subject = @subject; Calling sp using (var fnReturn = new DataTable()) { fnReturn.RunStoredProcedure(filters.Conn, "[dbo].[usp_BS_SendMail]", new Dictionary<string, object>() { { "@PRM_LAN_ID", filters.Lan_ID?? "" }, { "@REPORT_NAME", filters.ReportName}, { "@REPORT_PAGE", filters.ReportPage} }); return fnReturn; } ========================== report contents: <link href="http://localhost:1282/Scripts/Libs/JQueryUI/jquery-ui.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Css/Table/tableenhacements.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/BootStrap/css/bootstrap.css"…

2018-06-26 2 Comments 1398 Views 0 Like IT Team Read more

Problem: If only a few columns, the space is too big. $tblContainer.html("<table class='dataTable striped border hovered'></table>"); var reportName = JSON.parse(this.data).ReportName; if (reportName.indexOf("Automation")>-1) { table = $tblContainer.DataTable({ "bSort": false, "createdRow": function (row, data, dataIndex) { if (data[0].indexOf("<b>") > -1) { $(row).addClass('header-lightblue'); } row.cells[0].width = '400px'; //first column if (data.length < 5) { for (var i = 1; i < data.length - 1; i++) { row.cells[i].width = '200px'; } } },

2018-06-13 0 Comments 799 Views 0 Like IT Team Read more

Senario: Date input field (startmonth/endmonth) using jquery datetimepicker, and require to restrict users to pick invalid date from the picker. The data is retrieved from api. The valid date is based on the 'loaded' date returned. Once the startmonth selected, the endmonth should not be earlier than startmonth. var $hierarchycontrols = $("select[data-link-name='AutomationReportFilter']", that.mainContainer); var ajaxParameters = { methodUrl: "Reporting/GetAutomationReportFilter", dataInput: that.userInput }; that.AjaxCall(ajaxParameters, function () { $hierarchycontrols.prop("disabled", true); }, function (data) { $.each(data, function (i1, v1) { var count = 0; var currentName = v1.ParameterName; var $currentControl = $('select[data-filter-name="' + currentName + '"]', that.mainContainer); if (that.modifiedhiecontrolname !== currentName || $('#ddl' + that.modifiedhiecontrolname).val() == '') { $currentControl.html(""); if (!$("#txt" + currentName).is(":hidden")) { if (v1.ParameterName == 'ar_startmonth' || v1.ParameterName == 'ar_endmonth') { var mindate = '9999-12-31', maxdate = ''; $.each(v1.ParameterValue, function (i, v) { mindate = mindate > v.Value ? v.Value : mindate; maxdate = maxdate < v.Value ? v.Value : maxdate; }); $("#txtar_startmonth").data("DateTimePicker").minDate(mindate); $("#txtar_startmonth").data("DateTimePicker").maxDate(maxdate); if (mindate < $("#txtar_startmonth").val()) mindate = $("#txtar_startmonth").val(); $("#txtar_endmonth").data("DateTimePicker").minDate(mindate); $("#txtar_endmonth").data("DateTimePicker").maxDate(maxdate); } else { $.each(v1.ParameterValue, function (i2, v2) { $currentControl.append("<option " + (v2.Selected == true ? "selected" : "") + ' value="' + v2.Value + '">' +v2.Text + ""); count++; }); } } if (count > 1 || (count == 1 && $currentControl[0]!= undefined && $currentControl[0].textContent != '')) { $currentControl.prop("disabled", false); } } else { $currentControl.prop("disabled", false); } }); Ref: project - BusinessSuite file - FilterBuilder.js

2018-06-11 0 Comments 1105 Views 0 Like IT Team Read more

Report.js: if (JSON.parse(this.data).ReportName == "Automation Scorecard") { table = $tblContainer.DataTable({ "bSort": false, "createdRow": function (row, data, dataIndex) { if (filters.ReportName == "Automation Scorecard" && data[0].indexOf("") > -1) { $(row).addClass('header-lightblue'); } }, 'rowCallback': function (row, data, index) { for (i = 1; i < 11; i++) { if (data[i].indexOf('%') > -1 && data[i].indexOf(' ') == -1 && data[i] < '70%') { $(row).find('td:eq('+i+')').css('color', 'red'); } } }, Change title font size: header title font size: .header-title{ font-size:18px; font-weight:bold; }

2018-05-30 0 Comments 718 Views 0 Like IT Team Read more

UDF Function -- ============================================= -- Author: Albert He -- Create date: 2018-05-18 -- Description: make up columns for Automation Report -- example: dbo.ufn_AR_makeupColumns('2017-08','2018-03','cast(sum([%1]) as varchar) [%1],',0) -- ============================================= ALTER function [dbo].[ufn_AR_makeupColumnsTemp](@startMon varchar(20),@endMon varchar(20),@template varchar(max), @monthName bit=1) returns varchar(max) as begin declare @i int, @sql varchar(max)=''; declare @startYr int,@endYr int,@yr char(2),@mon int; declare @fld varchar(max) select @startYr=left(@startMon,4),@endYr=left(@endMon,4), @i = @startYr*12+right(@startMon,2),@yr=right(@startYr,2) while @i <= @endYr*12+right(@endMon,2) --@endYr=2018,@endMon=18-06 begin set @mon = (@i-@startYr*12-1)%12+1 set @fld = case when @monthName=1 then @yr+'-'+left(DateName(month, DateAdd(month, @mon, -1)),3) else @yr+'-'+ case when @mon<10 then '0' else '' end+cast(@mon as varchar) end set @sql += cast(replace(@template,'%1',@fld) as varchar(max)) set @i+=1; if @mon%12=0 set @yr=cast(cast(@yr as int)+1 as varchar) end return @sql end Usage - definition @sql+='create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255)'+dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1] float',0)+',Total float) Result - definition create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),[17-01] float,[17-02] float,[17-03] float,[17-04] float,[17-05] float,[17-06] float,[17-07] float,[17-08] float,[17-09] float,[17-10] float,[17-11] float,[17-12] float,[18-01] float,[18-02] float,[18-03] float,[18-04] float,[18-05] float,[18-06] float,[18-07] float,[18-08] float,[18-09] float,[18-10] float,[18-11] float,[18-12] float,Total float) Usage - column list select @strColumns = dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1]',0) Result - column list ,[17-01],[17-02],[17-03],[17-04],[17-05],[17-06],[17-07],[17-08],[17-09],[17-10],[17-11],[17-12],[18-01],[18-02],[18-03],[18-04],[18-05],[18-06],[18-07],[18-08],[18-09],[18-10],[18-11],[18-12]

2018-05-19 0 Comments 753 Views 0 Like IT Team Read more

EF 6.0 overview Entity Framework 6.0 maintains connection and transaction internally when SaveChanges() method is called. It maintains a transaction for multiple entity insert, update and delete in a single SaveChanges() method. Each time calling this method EF creates a new connection and transaction. This become an issue in our UNDO feature, because context_info only available in the same transaction scope. In earlier version of EF, System.Transaction.TransactionScope was used to control the transaction. So passing information to triggers is as easy as adding one additional call to a stored procedure. However, in EF6 this approach was out. New API with EF 6.0 EF 6.0 introduced two new APIs for transaction. DbContext.Database.BeginTransaction: It allows us to start a transaction manually. It allows us to combine several operations to be combined within the same transaction and hence all the transactions are either committed or rolled back. This method allows us to specify the isolation level for the transaction. It returns a DbContextTransaction object. The BeginTransaction method has two overloads, one has no argument and the other accepts an explicit Isolation Level. DbContext.Database.UseTransaction: It allows DbContext to use a transaction that was started outside of the Entity Framework. It means using this API we can use any existing transaction with Entity Framework. Implementation In application: using(var tran = _context.Database.BeginTransaction()) { try { await CallProcAsync("dbo.USP_HIS_ContextInfo @empId={0}, @recordId={1}", UserId, 0); await _context.SaveChangesAsync(); tran.Commit(); } catch { tran.Rollback(); } } In trigger: ALTER trigger [dbo].[trg_update_TBL_E2DIALER_MSI_CellCodes] on [dbo].[TBL_E2DIALER_MSI_CellCodes] after update as begin SET NOCOUNT ON declare @empId varchar(50), @recordId int, @changeID int, @info binary(128) select @info = context_info(),@empId=replace(convert(varchar(255),@info),0x0,'')…

2017-11-29 0 Comments 1335 Views 0 Like IT Team Read more

There are 4 types actions in database operation, called CRUD – Create, Read, Update, Delete. Basically there are only 3 actions would change the data, insert/update/delete. The data would be permanently changed once a transaction committed. How do we recover unwanted changes that was made by mistake on database? With database trigger we can easily solve this problem. TYPE OF TRIGGERS AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF INSERT Trigger INSTEAD OF UPDATE Trigger INSTEAD OF DELETE Trigger We will use AFTER triggers to UNDO the changes. KEY POINTS In order to log the user who made changes to the data, we need to get user identity from the application. There are many options to pass user information to trigger. add a column to store user id on each table, but when perform delete action the record would not be there; create a separate table to store user id on each action, this looks ugly; passing user id to database then trigger can get this user id. That is context_info. As we know, the build-in function context_info can carry some information on session bases. Create a store procedure to allow application to pass user id to database and keep it in the session: create proc dbo.USP_HIS_ContextInfo @empId varchar(110) as begin set nocount on declare @info binary(128) set @info = cast(@empId as binary(128)) set context_info @info end In undo stored procedure we can catch it like this: declare @info varchar(255) set @info = replace(cast(context_info() as varchar(255)),0x0,'') In order to avoid large data would be stored in the…

2017-11-14 0 Comments 1035 Views 0 Like IT Team Read more

Fixing jQuery DataTables header non-responsive issue

Send web report by email from report page

Changing DataTable cell width

Set minDate and maxDate on datetimepicker from ajax dataset

JQuery DataTable.js customization

Make up table definition with [yr-mth] columns and column list

Develop UNDO feature with Entity Framework 6 and trigger

Develop undo feature with trigger