/// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/1/13
        /// Retrieve user_name and created person of users
        /// </summary>
        /// <param name="userType"></param>
        /// <returns>userLogin object</returns>
        public List<UserLogin> getUserByType(int levelId, int userId)
        {
            List<UserLogin> UserList = new List<UserLogin>();
            DataHandler dataHandler = new DataHandler();
            int userRole = getUserRole(userId);
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@level_id", levelId });
            paramertList.Add(new object[] { "@user_id", userId });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetUserLoginDetailsByType");
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        UserLogin user = new UserLogin();
                        user.loginId = userId;
                        user.userId = int.Parse(dataRow["user_id"].ToString());
                        user.userName = dataRow["user_name"].ToString();

                        user.createdBy = int.Parse(dataRow["created_by"].ToString());
                        user.createdByRole = getUserRole(user.createdBy);

                        user.createdName = getUserNameById(user.createdBy);
                        user.roleId = int.Parse(dataRow["role_id"].ToString());

                        if (userRole == 1)
                        {
                            user.isEdit = true;
                        }

                        else if ((userRole == 2) && (levelId == 1))
                        {
                            user.isEdit = false;
                        }
                        else if ((userRole == 2) && (levelId == 2))
                        {
                            user.isEdit = true;
                        }
                        else if ((userRole == 2) && (levelId == 3))
                        {
                            user.isEdit = true;
                        }

                        UserList.Add(user);
                    }

                    return UserList;
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy: MAM. IRFAN
        /// CreatedDate: 2016/01/16
        /// 
        /// Getting all branches
        /// 
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/04
        /// removed existing connection open method and set parameter to object list and pass stored procedure name to
        /// call DataHandler class method and getting dataset object,
        /// create and return branche object list using that dataset
        /// 
        /// </summary>
        /// <returns> a list contain all branches</returns>
        /// 
        public List<Branch> getBranches(int companyId)
        {
            List<Branch> branchesLists = new List<Branch>();
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@companyId", companyId });
            try
            {
            DataSet dataSet = dataHandler.GetDataSet("spGetBranchesByCompanyId", paramertList);
            if (dataSet != null && dataSet.Tables.Count != 0)
            {
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    Branch branch = new Branch();
                    branch.BranchId = Convert.ToInt32(dataRow["branch_id"].ToString());
                    branch.BranchName = dataRow["branch_name"].ToString();
                    branch.BranchCode = dataRow["branch_code"].ToString();
                    branch.BranchAddress1 = dataRow["branch_address_1"].ToString();

                    branchesLists.Add(branch);
                }

                return branchesLists;
            }
            else
            {
                return null;
            }
        }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*

   Frontend page: Fee Page
   Title: Getting Curtailment Shedule
   Designed: Irfan Mam
   User story:
   Developed: Irfan MAM
   Date created: 4/22/2016

*/

        public bool GetFeesDueDates(int loanId, out string advPayDueDate, out string monPayDueDate, out string lotPayDueDate)
        {
            try
            {
                advPayDueDate = "";
                monPayDueDate = "";
                lotPayDueDate = "";

                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@loan_id", loanId });


                DataSet dataSet = dataHandler.GetDataSet("spGetFeesDueDates", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        advPayDueDate = dataRow["adv_payment_due_date"].ToString();
                        monPayDueDate = dataRow["mon_payment_due_date"].ToString();
                        lotPayDueDate = dataRow["lot_payment_due_date"].ToString();
                    }
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy:  Kasun
        /// CreatedDate:05/04/2016
        /// 
        /// get loan details by loan code
        /// </summary>
        /// <param name="loanCode"></param>
        /// <returns></returns>
        public Loan GetLoanByLoanCode(int loanId,string loanCode)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@loan_code", loanCode });
            paramertList.Add(new object[] { "@loan_id", loanId });

            Loan loan = new Loan();
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetLoanByLoanCode", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    DataRow dataRow = dataSet.Tables[0].Rows[0];

                    loan.LoanId = Convert.ToInt32(dataRow["loan_id"]);
                    loan.LoanAmount = Convert.ToDecimal(dataRow["loan_amount"]);
                    loan.LoanCode = dataRow["loan_code"].ToString();
                    loan.MaturityDate = Convert.ToDateTime(dataRow["maturity_date"]);
                    loan.StartDate = Convert.ToDateTime(dataRow["start_date"]);
                    loan.LoanStatus = Convert.ToBoolean(dataRow["loan_status"]);                    
                    loan.LoanNumber = dataRow["loan_number"].ToString();

                    return loan;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Kasun Samarawickrama
        /// CreatedDate: 2016/01/16
        /// 
        /// Get all rights in database
        /// </summary>
        /// <param name="userId"></param>
        /// <returns>Right list</returns>
        /// 
        public List<Right> getRights()
        {
            List<Right> RightsLists = new List<Right>();
            DataHandler dataHandler = new DataHandler();
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetRights");
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        Right right = new Right();
                        right.rightId = dataRow["right_id"].ToString();
                        right.active = false;
                        right.description = dataRow["description"].ToString();

                        RightsLists.Add(right);
                    }

                    return RightsLists;
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:4/27/2016
        /// </summary>
        /// <param name="company_Id"></param>
        /// <returns></returns>
        public List<UserRole> GetAllUserRoles(int company_Id)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_id", company_Id });

            List<UserRole> userRoleList = new List<UserRole>();
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetAllUserRolsByCompany", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        UserRole role = new UserRole
                        {
                            RoleId = Convert.ToInt32(dataRow["role_id"]),
                            RoleName = dataRow["role_name"].ToString()
                        };
                        userRoleList.Add(role);
                    }
                    return userRoleList;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Frontend page:    dashboard page
        /// title:              checking is atleast one permission for report access
        /// designed:           irfan mam
        /// User story:         DFP 476
        /// developed:          irfan mam
        /// date creaed:        6/23/2016
        /// 
        /// </summary>
        /// 
        /// <returns>
        /// if there is no loan has user rights -> false
        ///  if there is atleast one user right for any loan -> true
        /// </returns>
        public bool IsAtleastOnePermissionForReport( int userId)
        {

            bool ret = false; // set ret value false as default 
            DataHandler dataHandler = new DataHandler(); 
            List<object[]> paramertList = new List<object[]>(); // argument list
           
            // add user id to argument list
            paramertList.Add(new object[] { "@user_id", userId });
            try
            {
                // if stored proceture return 1
                if( dataHandler.ExecuteSQLReturn("isAtleastOnePermissionForReport", paramertList)== 1)
                {
                    // set return value to true
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // return the ret value
            return ret;


        }

        /// <summary>
        /// CreatedBy:kasun
        /// CreatedDate:2016/4/22
        /// 
        /// Get all titles for a loan
        /// </summary>
        /// <param name="loanId"></param>
        /// <returns></returns>
        public List<TitleUpload> GetUploadTitlesByLoanId(string unitId)
        {

            DataHandler dataHandler = new DataHandler();
            List<object[]> parameterList = new List<object[]>();
            List<TitleUpload> titleList = new List<TitleUpload>();

            parameterList.Add(new object[] { "@unit_id", unitId });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetUploadTitlesByUnitId", parameterList);

                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    foreach (DataRow reader in dataSet.Tables[0].Rows)
                    {
                        TitleUpload title = new TitleUpload();

                        title.FilePath = reader["file_path"].ToString();
                        title.UnitId = reader["unit_id"].ToString();
                        title.OriginalFileName = reader["original_file_name"].ToString();

                        titleList.Add(title);
                    }
                    return titleList;
                }
                else {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Frontend Page : Join Dealer
        /// Title: Get all users for given company id
        /// Designed: Piyumi Perera
        /// User story:
        /// Developed: Piyumi Perera
        /// Date Created: 05/26/2016
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public List<User> GetUserListByCompany(int companyId)
        {
            List<User> users = new List<User>();
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@company_id", companyId });

            DataSet dataSet = dataHandler.GetDataSet("spGetUsersbyCompany2", paramertList);
            if (dataSet != null && dataSet.Tables.Count != 0)
            {
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    User user = new User();
                    user.UserId = Convert.ToInt32(dataRow["user_id"].ToString());
                    user.UserName = dataRow["user_name"].ToString();
                    user.Password = dataRow["password"].ToString();
                    user.FirstName = dataRow["first_name"].ToString();
                    user.LastName = dataRow["last_name"].ToString();
                    user.NewEmail = dataRow["email"].ToString();
                    user.PhoneNumber = dataRow["phone_no"].ToString();
                    user.BranchId = Convert.ToInt32(dataRow["branch_id"].ToString());
                    user.RoleId = Convert.ToInt32(dataRow["role_id"].ToString());

                    users.Add(user);
                }

                return users;
            }
            else
            {
                return null;
            }
        }

        internal string updateCurtailmets(SelectedCurtailmentList curtailmentScheduleModel, int loanId, string dealerEmail)
        {
            try
            {
                int i = 1;
                XElement xEle = new XElement("Curtailments",
                    from curtailmentShedule in curtailmentScheduleModel.SelectedCurtailmentSchedules
                    select new XElement("CurtailmentShedule",
                        new XElement("CurtNo", curtailmentShedule.CurtNumber),
                        new XElement("UnitId", curtailmentShedule.UnitId),
                        new XElement("CurtAmount", curtailmentShedule.CurtAmount),
                        new XElement("PayDate", curtailmentShedule.PayDate),
                        new XElement("id", i++)
                        ));
                string xmlDoc = xEle.ToString();


                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList2 = new List<object[]>();
                paramertList2.Add(new object[] { "@loan_id", loanId });
                paramertList2.Add(new object[] { "@dealer_email", dealerEmail ?? ""});
                paramertList2.Add(new object[] { "@Input", xmlDoc });


                return dataHandler.ExecuteSQLWithStringReturnVal("spUpdateCurtailmentSchedule", paramertList2);


            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// CreatedBy:Irfan MAM
        /// CreatedDate:2016/2/9
        /// check the loan number is unique for a branch
        /// </summary>
        /// <returns>true or false</returns>
        public bool IsUniqueLoanNumberForBranch(string loanNumber, int RegisteredBranchId, User user,int loanId)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@loan_number", loanNumber });
                paramertList.Add(new object[] { "@branch_id", RegisteredBranchId });
                paramertList.Add(new object[] { "@loan_id", loanId });
                DataSet dataSet = dataHandler.GetDataSet("spIsUniqueLoanNumberForBranch", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// CreatedBy:Irfan MAM
        /// CreatedDate:2016/2/9
        /// getting all unit types
        /// </summary>
        /// <returns>IList<UnitType></returns>
        /// UpdatedBy:Asanka Senarathna
        /// 
        internal IList<UnitType> getAllUnitTypes()
        {
            List<UnitType> unitTypes = new List<UnitType>();
            DataHandler dataHandler = new DataHandler();

            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetAllUnitTypes", null);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        UnitType unitType = new UnitType();
                        unitType.unitTypeName = dataRow["unit_type_name"].ToString();

                        unitType.unitTypeId = int.Parse(dataRow["unit_type_id"].ToString());
                        unitType.isSelected = false;
                        unitTypes.Add(unitType);
                    }

                    return unitTypes;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// 
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/06
        /// removed existing connection open method
        /// call DataHandler class method and getting dataset object,
        /// create and return state object list using that dataset 
        /// </summary>
        /// <returns></returns>
        public List<State> GetAllStates()
        {
            List<State> stateList = new List<State>();
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetState");
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        State state = new State();
                        state.StateId = Convert.ToInt32(dataRow["state_id"]);
                        state.StateName = dataRow["state_name"].ToString();

                        stateList.Add(state);
                    }
                    return stateList;
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Kanishka SHM
        /// CreatedDate: 2016/01/17
        /// 
        /// Get all company types
        /// 
        /// argument : None
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/06
        /// removed existing connection open method
        /// call DataHandler class method and getting dataset object,
        /// create and return company type object list using that dataset
        /// 
        /// </summary>
        /// <returns>List<CompanyType></returns>
        public List<CompanyType> GetAllCompanyType()
        {
            List<CompanyType> ctList = new List<CompanyType>();
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetAllCompanyType");
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        CompanyType ct = new CompanyType();
                        ct.TypeId = Convert.ToInt32(dataRow["company_type_id"]);
                        ct.TypeName = dataRow["company_type_name"].ToString();

                        ctList.Add(ct);
                    }
                    return ctList;
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Kasun Samarawickrama
        /// CreatedDate: 2016/01/17
        /// 
        /// Get user permission permission string which contain rightId's
        /// </summary>
        /// <param name="userId"> Profile edit users id</param>
        /// <returns>Right List,  but first Right contain the string, If List have more than 1 value it is going to be an unAuthorize one</returns>
        /// 
        public List<Right> getRightsString(int userId, int loanId)
        {
            List<Right> RightsLists = new List<Right>();
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@userId", userId });
            paramertList.Add(new object[] { "@loanId", loanId });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetRightsStringByUserId", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        Right right = new Right();
                        right.userId = int.Parse(dataRow["user_id"].ToString());
                        right.rightsPermissionString = dataRow["right_id"].ToString();
                        right.reportRightsPermissionString = dataRow["report_rights"].ToString();
                        RightsLists.Add(right);
                    }

                    return RightsLists;
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Nadeeka
        /// CreatedDate: 2016/02/09
        /// 
        /// retreive Curtailment Detail By CurtailmentId
        /// 
        /// argument : curtailment_id (int)
        /// 
        /// </summary>
        /// <returns>curtailment list</returns>
        public List<Curtailment> retreiveCurtailmentByLoanId(int loanId)
        {
            try
            {
                List<Curtailment> lstCurtailment = new List<Curtailment>();
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@loan_id", loanId });

                DataSet dataSet = dataHandler.GetDataSet("spRetrieveCurtailmentByLoanId", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        Curtailment curtailment = new Curtailment();
                        curtailment.CurtailmentId = int.Parse(dataRow["curtailment_id"].ToString());
                        curtailment.LoanId = int.Parse(dataRow["loan_id"].ToString());
                        curtailment.TimePeriod = Convert.ToInt32(dataRow["time_period"]);
                        curtailment.Percentage = Convert.ToInt32(dataRow["percentage"].ToString());
                        lstCurtailment.Add(curtailment);
                    }
                    return lstCurtailment;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// CreatedBy : Kanishka SHM
        /// CreatedDate: 2016/01/16
        /// 
        /// Insert User details
        /// 
        /// argument : user (User)
        /// 
        /// 
        /// UpdatedBy : nadeeka
        /// UpdatedDate: 2016/03/03
        /// removed existing connection open method and set parameter's to object list and pass stored procedure name to
        /// call DataHandler class to save user object
        /// </summary>
        /// <returns>1</returns>
        public int InsertUser(User user)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_Id", user.UserId });
            paramertList.Add(new object[] { "@user_name", user.UserName });
            paramertList.Add(new object[] { "@password", user.Password });
            paramertList.Add(new object[] { "@first_name", user.FirstName });
            paramertList.Add(new object[] { "@last_name", user.LastName });
            paramertList.Add(new object[] { "@email", user.Email });
            paramertList.Add(new object[] { "@phone_no", user.PhoneNumber });
            paramertList.Add(new object[] { "@status", user.Status });
            paramertList.Add(new object[] { "@is_delete", user.IsDelete });
            paramertList.Add(new object[] { "@created_by", user.CreatedBy });
            paramertList.Add(new object[] { "@create_Date", DateTime.Now });
            paramertList.Add(new object[] { "@branch_id", user.BranchId });
            paramertList.Add(new object[] { "@role_id", user.RoleId });
            paramertList.Add(new object[] { "@Company_id", user.Company_Id });
            paramertList.Add(new object[] { "@step_status", user.step_status });

            try
            {
                return dataHandler.ExecuteSQLReturn("spInsertUser", paramertList);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 /// <summary>
 /// CreatedBy : Kasun Smarawickrama
 /// CreatedDate: 2016/01/14
 /// 
 /// Retrive User Level by userid
 /// </summary>
 /// <param name="userId">userid</param>
 /// <returns></returns>
 /// UpdatedBy : Asanka Senarathna
 public int GetUserLevelByUserId(int userId)
 {
     DataHandler dataHandler = new DataHandler();
     List<object[]> paramertList = new List<object[]>();
     paramertList.Add(new object[] { "@userId", userId });
     try
     {
         return dataHandler.ExecuteSQLReturn("spGetUserLevelByUserId", paramertList);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 public DataSet SelectRequestAns(User user)
 {
     try
     {
         DataHandler dataHandler = new DataHandler();
         List<object[]> paramertList = new List<object[]>();
         paramertList.Add(new object[] { "@user_id", user.UserId });
         return dataHandler.GetDataSet("spGetUserReque", paramertList);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 /// <summary>
 /// CreatedBy : Kasun Samarawickrama
 /// CreatedDate: 2016/01/14
 /// 
 /// user login authentication
 /// 
 /// UpdatedBy : Asanka
 /// UpdatedDate: 2016/03/04
 /// removed existing connection open method and set parameter's to object list and pass stored procedure name to
 /// call DataHandler class to save user object
 /// </summary>
 /// <param name="username"></param>
 /// <param name="password"></param>
 /// 
 /// <returns>userid</returns>
 public DataSet CheckUserLogin(User user)
 {
     DataHandler dataHandler = new DataHandler();
     List<object[]> paramertList = new List<object[]>();
     
     paramertList.Add(new object[] { "@userName", user.UserName });
     try
     {
         return dataHandler.GetDataSet("spUserLogin", paramertList);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }