Tips: c#

 ctrl+mo :: Function Collapse Code 

Auto Database Backup to Drive

--exec usp_DataBaseBackupgHRM  

CREATE Procedure [dbo].[usp_DataBaseBackupgHRM]  

As  

Begin  

 Declare @BackUpname varchar(255)  

   

  Select @BackUpname='S:\gHRMDBBackup\gHRM_'+RTRIM(LTRIM(Convert(Varchar(50),Getdate(),112)))+'.BAK'  

  BACKUP DATABASE gHRM  

  TO DISK = @BackUpname  

  WITH INIT  

End  

Find Entity Model Errors.

 Get Error in Entity mapping validation::

  var entity = memberService.GetByIdLong(model.MemberID);

                foreach (ModelState modelState in ModelState.Values)

                {

                    foreach (ModelError error in modelState.Errors)

                    {

                        var myerr = error.ErrorMessage;

                    }

                }

jTable API

 jTable: http://jtable.org/ApiReference/Actions#act-listAction

JSON convert FIle::

Reference:: using Newtonsoft.Json; 

public ActionResult DownloadExcelJson(string spName, string date, string txtBranchCode = "")

        {

             

            try

            {

                var FileName = "gBankerData";

                List<MRA_Contract_DetailsViewModel> ListContractDetails = new List<MRA_Contract_DetailsViewModel>();

                var ContractDetailsParam = new { OrgID = LoggedInOrganizationID, OfficeID = LoginUserOfficeID };

                var ContractDetails = accReportService.GetAccDataForReport(ContractDetailsParam, "Proc_Get_ContractDetails");

                ListContractDetails = ContractDetails.Tables[0].AsEnumerable()

                .Select(row => new MRA_Contract_DetailsViewModel

                {

                    MFICODE = row.Field<string>("MFICODE"),

                    ACCOUNTINGDATE = row.Field<DateTime?>("ACCOUNTINGDATE"),

                    PRODUCTIONDATE = row.Field<DateTime?>("PRODUCTIONDATE"),

                    BRANCH_CODE = row.Field<string>("BRANCH_CODE"),

                    MEMBERID = row.Field<string>("MEMBERID"),

                    LOAN_CODE = row.Field<string>("LOAN_CODE"),

                    LOAN_TYPE = row.Field<string>("LOAN_TYPE"),

                    LOAN_DISBURSEMENT_DATE = row.Field<string>("LOAN_DISBURSEMENT_DATE"),

                    END_DATE_CONTRACT = row.Field<string>("END_DATE_CONTRACT"),

                    LAST_INSTALLMENT_PAID_DATE = row.Field<string>("LAST_INSTALLMENT_PAID_DATE"),

                    DISBURSED_AMOUNT = row.Field<decimal>("DISBURSED_AMOUNT"),

                    TOTAL_OUTSTANDING_AMT = row.Field<decimal?>("TOTAL_OUTSTANDING_AMT"),

                    PERIODICITY_PAYMENT = row.Field<string>("PERIODICITY_PAYMENT"),

                    TOTAL_NUM_INSTALLMENT = row.Field<int>("TOTAL_NUM_INSTALLMENT"),

                    INSTALLMENT_AMT = row.Field<decimal?>("INSTALLMENT_AMT"),

                    NUM_REMAINING_INSTALLMENT = row.Field<int?>("NUM_REMAINING_INSTALLMENT"),

                    NUM_OVERDUE_INSTALLMENT = row.Field<decimal?>("NUM_OVERDUE_INSTALLMENT"),

                    OVERDUE_AMT = row.Field<decimal?>("OVERDUE_AMT"),

                    LOAN_STATUS = row.Field<string>("LOAN_STATUS"),

                    RESCHEDULE_NO = row.Field<int>("RESCHEDULE_NO"),

                    LAST_RESCHEDULE_DATE = row.Field<int?>("LAST_RESCHEDULE_DATE"),

                    WRITE_OFF_AMT = row.Field<decimal>("WRITE_OFF_AMT"),

                    WRITE_OFF_DATE = row.Field<string>("WRITE_OFF_DATE"),

                    CONTRACT_PHASE = row.Field<string>("CONTRACT_PHASE"),

                    LOAN_DURATION = row.Field<int>("LOAN_DURATION"),

                    ACTUAL_END_DATE_CONTRACT = row.Field<string>("ACTUAL_END_DATE_CONTRACT"),

                    ECONOMIC_PURPOSE_CODE = row.Field<string>("ECONOMIC_PURPOSE_CODE"),

                    COMPULSORY_SAVING_AMT = row.Field<decimal>("COMPULSORY_SAVING_AMT"),

                    VOLUNTARY_SAVING_AMT = row.Field<decimal>("VOLUNTARY_SAVING_AMT"),

                    TERM_SAVING_AMT = row.Field<decimal>("TERM_SAVING_AMT"),

                    SUBSIDIZED_CREDIT_FLAG = row.Field<string>("SUBSIDIZED_CREDIT_FLAG"),

                    SERVICE_CHARGE_RATE = row.Field<decimal?>("SERVICE_CHARGE_RATE"),

                    PAYMENT_MODE = row.Field<string>("PAYMENT_MODE"),

                    ADVANCE_PAYMENT_AMT = row.Field<decimal?>("ADVANCE_PAYMENT_AMT"),

                    LAW_SUIT = row.Field<string>("LAW_SUIT"),

                    ME = row.Field<string>("ME"),

                    MEMBER_WELFARE_FUND = row.Field<string>("MEMBER_WELFARE_FUND"),

                    INSURENCE_COVERAGE = row.Field<string>("INSURENCE_COVERAGE"),

                }).ToList();

                var MFICode = ListContractDetails.Select(l => l.MFICODE).FirstOrDefault();

                var Contract_Details = new

                {

                    header = new { DataType = "H", MfiCode = MFICode, AccountingDate = DateTime.Now, ProductionDate = DateTime.Now },

                    Details = new { DataType = "C", values = ListContractDetails },

                    footer = new { DataType = "F", ListContractDetails.Count },

                };

                List<MRA_Subject_DetailsViewModel> ListSubjectDetails = new List<MRA_Subject_DetailsViewModel>();

                var SubjectDetailsParam = new { OrgID = LoggedInOrganizationID, OfficeID = LoginUserOfficeID };

                var SubjectDetails = accReportService.GetAccDataForReport(SubjectDetailsParam, "Proc_Get_SubjectDetails");

                ListSubjectDetails = SubjectDetails.Tables[0].AsEnumerable()

                .Select(row => new MRA_Subject_DetailsViewModel

                {

                    MFICODE = row.Field<string>("MFICODE"),

                    ACCOUNTINGDATE = row.Field<DateTime?>("ACCOUNTINGDATE"),

                    PRODUCTIONDATE = row.Field<DateTime?>("PRODUCTIONDATE"),

                    BRANCH_CODE = row.Field<string>("BRANCH_CODE"),

                    MEMBERID = row.Field<string>("MEMBERID"),

                    Name = row.Field<string>("Name"),

                    Occupation = row.Field<int>("Occupation"),

                    FATHERS_NAME = row.Field<string>("FATHERS_NAME"),

                    MOTHERS_NAME = row.Field<string>("MOTHERS_NAME"),

                    MARITAL_STATUS = row.Field<int>("MARITAL_STATUS"),

                    SPOUSE_NAME = row.Field<string>("SPOUSE_NAME"),

                    Gender = row.Field<string>("Gender"),

                    DOB = row.Field<string>("DOB"),

                    NID = row.Field<string>("NID"),

                    SMARTCARD_NO = row.Field<string>("SMARTCARD_NO"),

                    BIRTH_CERTIFICATE_NO = row.Field<string>("BIRTH_CERTIFICATE_NO"),

                    TIN = row.Field<string>("TIN"),

                    Other_ID_Type = row.Field<int>("Other_ID_Type"),

                    OTHER_ID_NO = row.Field<string>("OTHER_ID_NO"),

                    EXPIRY_DATE = row.Field<string>("EXPIRY_DATE"),

                    ISSUE_COUNTRY = row.Field<string>("ISSUE_COUNTRY"),

                    ContactNo = row.Field<string>("ContactNo"),

                    P_ADDRESS = row.Field<string>("P_ADDRESS"),

                    P_THANA = row.Field<string>("P_THANA"),

                    P_DISTRICT = row.Field<string>("P_DISTRICT"),

                    P_COUNTRY = row.Field<string>("P_COUNTRY"),

                    PR_ADDRESS = row.Field<string>("PR_ADDRESS"),

                    PR_THANA = row.Field<string>("PR_THANA"),

                    PR_DISTRICT = row.Field<string>("PR_DISTRICT"),

                    PR_COUNTRY = row.Field<string>("PR_COUNTRY"),

                    ACADEMIC_QUALIFICATION = row.Field<int>("ACADEMIC_QUALIFICATION"),

                }).ToList();

                 

                var Subject = new

                {

                    header = new { DataType = "H", MfiCode = MFICode, AccountingDate = DateTime.Now, ProductionDate = DateTime.Now },

                    Details = new { DataType = "C", values = ListSubjectDetails },

                    footer = new { DataType = "F", ListSubjectDetails.Count },

                };

                 

                var mraCIBData = new { Contract_Details, Subject };

                var jsonString = JsonConvert.SerializeObject(mraCIBData);

                byte[] fileBytes = Encoding.ASCII.GetBytes(jsonString);

                

                string fileName = FileName + ".json";

                //string fileName = "filename.json";

                return File(fileBytes, "application/json", fileName);

            }

            catch (Exception ex)

            {

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

            }

        }

Pivot Tables

-- =============================================         
-- Author:  <KHALID,,>         
-- Create date: <20 July,2020 ,>         
-- Description: <Jakaria bhai asked for District wise and DesignationWise EmployeeCount>         
-- exec SP_GetDistrictWiseEmployeeDesig_Pivot       
-- =============================================         
ALTER PROC [dbo].[SP_GetDistrictWiseEmployeeDesig_Pivot]         
AS         
BEGIN           

DECLARE --Put initial Selected data       
 @InitialData Table(       
  RowNum int IDENTITY(1,1) NOT NULL ,   
  EmployeeId bigint,     
  DistrictCode   nvarchar(50) ,
  DistrictName nvarchar(50),
  designationName nvarchar(50)
         
 )


Insert into @InitialData SELECT
d.EmployeeId ,
d.DistrictCode ,
d.DistrictName ,
d.designationName

 From
 (
SELECT -- dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1) AS [Old District Name],                         
--dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2) AS [Old District Code],
e.EmployeeId,
isNull(dis.District_Code, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2)) AS DistrictCode
, isNUll( dis.district_name_eng, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1)) AS DistrictName
 , ed.designationName
FROM      Employee AS e /* SELECT * FROM Office*/ LEFT JOIN             
                         EmployeeDesignation AS ed ON ed.DesignationId = e.DesignationId LEFT JOIN                         
                         OfficeDesignation od ON od.OfficeDesignationId = e.OfficeDesignationId 
Left Join                         
                         EmployeeAddress AS ea ON ea.EmployeeId = e.EmployeeId AND ea.AddressType = 'Pe' AND ea.IsActive = 1 LEFT JOIN                         
                         LgThana AS th ON th.thana_id = ea.ThanaId LEFT JOIN                         
                         LgUnion AS un ON un.union_id = ea.UnionId LEFT JOIN                         
                         District AS dis ON dis.district_id = ea.DistrictId AND dis.district_id IS NOT NULL LEFT JOIN                         
                         StateOrProvince AS sp ON sp.StateOrProvinceId = ea.StateOrProvinceId LEFT JOIN                         
                         ThanaOldByEmloyee AS toe ON e.EmployeeId = toe.EmployeeId

WHERE e.EmployeeStatus = 'A' AND ed.DesignationId <= 15 


) AS d

--SELECT * FROM @InitialData

 SELECT * FROM       
(     
    SELECT       
EmployeeId ,
DistrictCode ,
DistrictName ,
designationName
     
    FROM     
        @InitialData     
     
) t     
PIVOT(     
   COUNT(EmployeeId)     
    FOR DesignationName IN (     
        [TO],     
        [SPO],     
        [SO],     
        [PO],     
        [MD],     
[GM],     
        [DMD],     
[DGM],     
[D.P.O],     
[AGM],     
[A/C SPECIALIST],     
[NULL]     
         
  )     
) AS pivot_table;

--Order By DistrictCode Asc 

-- SELECT * FROM District
     
     
     
     
     
     
       
END       
-- END Main Loop       
--SP_GetPromotionInfo 838

Age Duration Calculation

ALTER FUNCTION [dbo].[GetDurationYear_Month_Day]         
(         
   @FromDate DATETIME, @ToDate DATETIME         
)         
RETURNS NVARCHAR(300)         
AS         
BEGIN 

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
Declare @currentdatetime datetime 

--SELECT @date = '15-Jun-1986'


set @date = @FromDate
 
set @currentdatetime = @ToDate


 --SELECT
 -- @date = REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-'),
 
 -- @currentdatetime =  REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-')                                 

 --  FROM Employee as e WHERE e.EmployeeId = 26380 --12390
 
   
SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, @currentdatetime) - CASE WHEN (MONTH(@date) > MONTH(@currentdatetime)) OR (MONTH(@date) = MONTH(@currentdatetime) AND DAY(@date) > DAY(@currentdatetime)) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, @currentdatetime) - CASE WHEN DAY(@date) > DAY(@currentdatetime) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, @currentdatetime)



 RETURN CAST(@years AS VARCHAR(4)) + '-' + CAST(@months AS VARCHAR(4)) + '-' + CAST(@days AS VARCHAR(4))


END

Get Value from Datatable

in c# if you get Datatable in single row, it is easy to get the values.
You dont need to map the class.



var param = new { @AccCode = entity.SalaryAccCode };
                var empList = employeeSPService.GetDataWithParameter(param, "SP_PR_Get_AccData");

           
                if (empList.Tables[0].Rows.Count > 0)
                {
                    var v = empList.Tables[0].Rows[0]["AccountCode"].ToString();
                    var AccountName = empList.Tables[0].Rows[0]["AccountName"].ToString();
                    entity.AccountName = AccountName;

                }

WIFI not active Keeps Disconnecting

1. Please check you have enabled the wireless feature of the laptop. There must be a key in the keyboard. That enables and disables the wireless devices.

2. Try to reinstall the driver of the device.  right click on MY computer -> Properties -> manage devicess -> select wireless device. Then Uninstall.

3. Then Scan For Hardware Changes.

4.Check Performance:
Right click on wireless device -> properties ->

5.select Power Management Tab -> Uncheck the Allow the computer performance

6. If Your Network is bridged then unbridge it.

7.Right Click on network -> select Properties -> uncheck bridge.

Delete Tables By Selecting Name

Delete By Selecting table name::

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'NXTRPTEmployeeProfile_%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

Show Data on WebGrid

in cshtml::
Define webgrid::

@model ExcelMigration.WEB.ViewModel.BuroCustomerInfoViewModel
@{
    ViewBag.Title = "Verify Excel Data";
    Layout = "~/Views/Shared/_Layout.cshtml";
    var rowCount = Model.CheckStaffDataTable.Count();
    WebGrid grid = new WebGrid(Model.CheckStaffDataTable, rowsPerPage: rowCount == 0 ? 1 : rowCount);
    Model.BranchCode = Session["BranchCode"].ToString() != null ? Session["BranchCode"].ToString() : null;
    Model.IsSuperAdmin = Convert.ToBoolean(Session["IsSuperAdmin"].ToString() != null ? Session["IsSuperAdmin"].ToString() : "false");
}

Add Styles:: <style type="text/css">

    .webgrid-table {

        font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;

        font-size: 1.2em;

        width: 100%;

        display: table;

        border-collapse: separate;

        border: solid 1px #98BF21;

        background-color: white;

    }

    .webgrid-table td, th {

            border: 1px solid #98BF21;

            padding: 3px 7px 2px;

        }

    .webgrid-header {

        background-color: #A7C942;

        color: #FFFFFF;

        padding-bottom: 4px;

        padding-top: 5px;

        text-align: left;

    }

    .webgrid-footer {

    }

    .webgrid-row-style {

        padding: 3px 7px 2px;

    }

    .webgrid-alternating-row {

        background-color: #EAF2D3;

        padding: 3px 7px 2px;

    }

</style>

Call::

@using (Html.BeginForm("VerifyExcelData", "ExcelUpload", FormMethod.Post, new { enctype = "multipart/form-data", @role = "form" }))

{

 <div class="row frmRow">

                            <div class="col-md-6 col-sm-6">

                                <div class="item form-group">

                                    <label class="control-label col-md-4 input-sm">&nbsp;</label>

                                    <div class="col-md-8">

                                        <button type="submit" class="btn btn-sm btn-success" id="btnCheck">Verify Data</button>

                                    </div>

                                </div>

                            </div>

                        </div>

}

Backed end:: 

view Model:: Add this Property.

 public List<dynamic> CheckStaffDataTable { get; set; }

Call This Function::

 [HttpPost]

        public ActionResult VerifyExcelData(BuroCustomerInfoViewModel entity)//string Id, string Center

        {

            var list = new List<dynamic>();

            MapBranchCodeList(entity);

            try

            {

                if (entity.BranchCode != "" && entity.SpName != "")

                {

                    if (entity.DateTo != "" && entity.DateTo != null)

                    {

                        list = SpDynamicCalling(entity.BranchCode, entity.DateTo, entity.SpName);

                    }

                    else

                    {

                        list = SpDynamicCalling(entity.BranchCode, entity.SpName);

                    }

                }

                entity.Result = 1;

                entity.ReturnMessage = "";

            }

            catch (Exception e)

            {

                entity.Result = 2;

                entity.ReturnMessage = "You provided wrong parameter for this check.";

            }

            entity.CheckStaffDataTable = list.Count > 0 ? list : new List<dynamic>();

            return View(entity);

        }

Avro Keybord online

https://avro.im/

Write bangla online without change keyboard.