Online Entity To Model Converter

 http://tmt.rejawebs.com/

C# Jagged Array

 Arrays in Array..

/// <summary>

        /// C# Jagged Array Example

        /// </summary>

        static void Main()

        {

            int[][] arr = new int[4][];// Declare the array  

            arr[0] = new int[] { 11, 21, 56, 78 };// Initialize the array          

            arr[1] = new int[] { 42, 61, 37, 41, 59, 63 };

            arr[2] = new int[] { 11 };

            arr[3] = new int[] { 12 }; 

            // Traverse array elements  

            for (int i = 0; i < arr.Length; i++)

            {

                for (int j = 0; j < arr[i].Length; j++)

                {

                    System.Console.Write(arr[i][j] + " ");

                }

                System.Console.WriteLine();

            }

        }

Get JSON and Populate Table in MVC: Vue JS

ADD Reference: <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js"></script> 

JQUERY: 

$("#btnView").click(function (e) {

            $('tbody').empty();

            e.preventDefault();

            var DateFrom = $("#txtFromDt").val();

            var DateTo = $("#txtToDt").val();

            var url;

            if (DateFrom != '' && DateTo != '') {

              //  var url = '/GroupwiseReport/GenerateStaffwiseStatementReport?DateFrom=' + DateFrom + '&DateTo=' + DateTo;

                var url = '/GroupwiseReport/EmployeeServiceBookReport?DateFrom=' + DateFrom + '&DateTo=' + DateTo;

               // window.open(url, 'mywindow', 'fullscreen=yes, scrollbars=auto');

                 //$('tbody').remove();

                $.getJSON(url,

                    function (json) {

                        var tr;

                        //Append each row to html table  

                        for (var i = 0; i < json.length; i++) {

                            tr = $('<tr/>');

                            tr.append("<td>" + json[i].Recoverable + "</td>");

                            tr.append("<td>" + json[i].CurRecovery + "</td>");

                            tr.append("<td>" + json[i].DueMembersLoanBalance + "</td>");

                            tr.append("<td>" + json[i].DisbursementAmount + "</td>");

                            $('tbody').append(tr);

                        }

                    });

                 

            }

            else {

                alert('Date field cannot be blank')

            }

        });

HTML: TABLE

 <div class="row">

        <table class="table table-bordered table-condensed table-hover table-striped">

            <thead>

                <tr>

                    <th>Recoverable</th>

                    <th>CurRecovery</th>

                    <th>DueMembersLoanBalance</th>

                    <th>DisbursementAmount</th>

                </tr>

            </thead>

            <tbody></tbody>

        </table>

    </div>

--------------------------------------------------------

IN Controller:: 

 [HttpGet]

        public JsonResult EmployeeServiceBookReport(string DateFrom, string DateTo)

        {

            try

            {

                var filter = new SearchFilterData{ OfficeID = (int)SessionHelper.LoginUserOfficeID, DateFrom = Convert.ToDateTime(DateFrom), DateTo = Convert.ToDateTime(DateTo) };

                var listings = employeespService.GetStaffwiseStatementDSKNewByFilter(filter);

                //return Content(string.Empty);

                return Json(listings, JsonRequestBehavior.AllowGet);

            }

            catch (Exception ex)

            {

                return Json(new { Result = "ERROR", Message = ex.Message });

            }

        }

Shortcut of SQL Database Tables

 

--Search with Table Name

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE LOWER(TABLE_NAME) LIKE '%' + LOWER('prod%note') + '%'

--Search with Column Name

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE LOWER(COLUMN_NAME) LIKE '%' + LOWER('prod%note') + '%'

--Search with Table and Column Name

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE

LOWER(TABLE_NAME) LIKE '%' + LOWER('SalesOrder') + '%'

AND

LOWER(COLUMN_NAME) LIKE '%' + LOWER('Project') + '%'

--Search with Constraint

SELECT

   OBJECT_NAME(o.parent_object_id)

FROM

   sys.objects o

WHERE

   o.name = 'MyConstraintName' AND o.parent_object_id <> 0

Auto Mapper : LIST

  var param = new { OfficeID = OfficeID, CenterID = CenterID};

                var getData = ultimateReportService.GetDataWithParameter(param, "getCollectionEntryScreen");

                var detail = getData.Tables[0].AsEnumerable().Select(p => new OLRSAccChartMappingViewModel

                {

                    Id = p.Field<int>("Id"),

                    AccCodeOLRS = p.Field<string>("AccCodeOLRS"),

                    AccChartCode = p.Field<string>("AccCode")

                }).ToList();

Update Parent Name gHRM Plus

 

DECLARE --Put initial Selected data     

 @InitialRawData Table(    

  RowNum   int  IDENTITY(1,1) NOT NULL ,     

  EmployeeCode   nvarchar(200) , 

  MotherName nVarchar(300) NULL    

  --MotherName nVarchar(300) NULL ,    

  --SpouseName nVarchar (300) NULL ,  

  --Height nVarchar(300) ,

  -- Blood nvarchar(20)

 )     

 

INSERT INTO @InitialRawData    

SELECT      

EmployeeCode, [Mothers Name] 

FROM  UpdateAdvice as ls    

    

 SELECT * FROM  @InitialRawData

DECLARE     @LeaveSELLRow INT    

     ,@CountLS INT 

 SET @LeaveSELLRow = 1    

SET @CountLS = (SELECT COUNT(*) FROM @InitialRawData)    

WHILE @LeaveSELLRow <= @CountLS - 1    

BEGIN  

Declare @EmpId bigint,  @CurrEmployeeCode nvarchar(200), @CurrMotherName nvarchar(20), @Gender nvarchar(10)  

SELECT @CurrEmployeeCode =  EmployeeCode, @CurrMotherName = MotherName FROM @InitialRawData Where RowNum = @LeaveSELLRow

SELECT @Gender = Gender, @EmpId = EmployeeId FROM Employee WHERE EmployeeCode =  @CurrEmployeeCode

 

--F

--M

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'M'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrMotherName WHERE EmployeeId = @EmpId AND Relation = 'M'

END

ELSE -- INSERT

BEGIN

Insert INTO EmployeeFamilyInfo

(

EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate

)

Values

( 

 @EmpId, @CurrMotherName, 'M',      'F',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

END

SET @LeaveSELLRow += 1    

END  

Update Spouse Name gHRMPlus

 

DECLARE --Put initial Selected data     

 @InitialRawData Table(    

  RowNum   int  IDENTITY(1,1) NOT NULL ,     

  EmployeeCode   nvarchar(200) , 

  SpouseName nVarchar(300) NULL    

  --MotherName nVarchar(300) NULL ,    

  --SpouseName nVarchar (300) NULL ,  

  --Height nVarchar(300) ,

  -- Blood nvarchar(20)

 )     

 

INSERT INTO @InitialRawData    

SELECT      

EmployeeCode, [Spouse Name] 

FROM  UpdateAdvice as ls    

    

 SELECT * FROM  @InitialRawData

DECLARE     @LeaveSELLRow INT    

     ,@CountLS INT 

 SET @LeaveSELLRow = 1    

SET @CountLS = (SELECT COUNT(*) FROM @InitialRawData)    

WHILE @LeaveSELLRow <= @CountLS - 1    

BEGIN  

Declare @EmpId bigint,  @CurrEmployeeCode nvarchar(200), @CurrSpouseName nvarchar(20), @Gender nvarchar(10)  

SELECT @CurrEmployeeCode =  EmployeeCode, @CurrSpouseName = SpouseName FROM @InitialRawData Where RowNum = @LeaveSELLRow

SELECT @Gender = Gender, @EmpId = EmployeeId FROM Employee WHERE EmployeeCode =  @CurrEmployeeCode

 

--F

--M

IF(@Gender = 'M')

BEGIN -- WIFE

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'W'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrSpouseName WHERE EmployeeId = @EmpId AND Relation = 'W'

END

ELSE -- INSERT

BEGIN

Insert INTO EmployeeFamilyInfo

(

EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate

)

Values

( 

 @EmpId, @CurrSpouseName, 'W',      'F',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

 

END

END

ELSE -- Husband

BEGIN 

 --SELECT 4246 - 3764 Rows

IF EXISTS

(

    SELECT *

    FROM  EmployeeFamilyInfo

    WHERE EmployeeId = @EmpId AND Relation = 'H'

)

BEGIN -- Update

         Update EmployeeFamilyInfo SET Name = @CurrSpouseName WHERE EmployeeId = @EmpId AND Relation = 'H'

END

ELSE -- INSERT

BEGIN

Insert INTO EmployeeFamilyInfo

(

EmployeeId, Name, Relation, Gender, DateOfBirth, EducationalQualification, Occupation, IsActive, InActiveDate, IsApproved, IsRejected, ApprovedOrRejectedBy, ApprovalOrRejectDate, CreateUser, CreateDate, UpdateUser, UpdateDate

)

Values

( 

 @EmpId, @CurrSpouseName, 'H',      'M',      NULL,                 NULL,              NULL,        1,        NULL,      1,             0,          NULL,                NULL,              3,    '2018-07-11 12:53:00', NULL, NULL

)

END

END

SET @LeaveSELLRow += 1    

END