/*

   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: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;
            }
        }
        /// <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 : 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>
        /// 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;
            }
        }
        /// <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>
        /// 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: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;
            }
        }
        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>
        /// 
        /// 
        /// 
        /// 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: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 : 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 : 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: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;
            }
        }
 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 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;
     }
 }
 /// <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;
     }
 }
        /*

Frontend page: Reset Password page
Title:  Verifying the user account using usedId and token
Designed: Irfan Mam
User story: 
Developed: Irfan MAM
Date created: 1/17/2016

*/
        public bool verifyAccount(int userId, string token)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@user_id", userId });
            paramertList.Add(new object[] { "@token", token });
            paramertList.Add(new object[] { "@expired_date", DateTime.Now });

            try
            {
                return dataHandler.ExecuteSQLWithReturnVal("spverifyAccountBytoken", paramertList) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Created by :Asanka Senarathna
        /// Createddate : 2016/03/30
        /// Update User Request (Add Answer to User Question)
        /// </summary>
        /// <param name="userRequest"></param>
        /// <returns></returns>
        public int UpdateUserRequestAnswer(UserRequest userRequest)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@request_id", userRequest.request_id });
                paramertList.Add(new object[] { "@answer", userRequest.answer });
                paramertList.Add(new object[] { "@answer_user_id", userRequest.answer_user_id });

                return dataHandler.ExecuteSQL("spUpdateUserRequestAnswer", paramertList) ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/2/5
        /// get all accrual methods to a list
        /// </summary>
        /// <returns>methodList</returns>
        public List<AccrualMethods> GetAllAccrualMethods()
        {
            List<AccrualMethods> methodList = new List<AccrualMethods>();
            DataHandler dataHandler = new DataHandler();            

            DataSet dataSet = dataHandler.GetDataSetBySQL("spGetAllAccrualMethods");
            if (dataSet != null && dataSet.Tables.Count != 0)
            {
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    AccrualMethods methods = new AccrualMethods()
                    {
                        MethodId = Convert.ToInt32(dataRow["accrual_method_id"]),
                        MethodName = dataRow["accrual_method_name"].ToString()
                    };
                    methodList.Add(methods);
                }

                return methodList;
            }
            else
            {
                return null;
            }
           

            //using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["AutoDealersConnection"].ToString()))
            //{
            //    var command = new SqlCommand("spGetAllAccrualMethods", con) { CommandType = CommandType.StoredProcedure };
            //    con.Open();
            //    using (var reader = command.ExecuteReader())
            //    {
            //        while (reader.Read())
            //        {
            //            AccrualMethods methods = new AccrualMethods()
            //            {
            //                MethodId = Convert.ToInt32(reader["accrual_method_id"]),
            //                MethodName = reader["accrual_method_name"].ToString()
            //            };
            //            methodList.Add(methods);
            //        }
            //    }
            //}
            //return methodList;
        }
        /// <summary>
        /// CreatedBy: Piyumi
        /// CreatedDate: 3/30/2016
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int GetLoanCount(int para, int roleId)
        {
            //int loanCount = 0;
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();
            paramertList.Add(new object[] { "@para", para });
            paramertList.Add(new object[] { "@role", roleId });
            try
            {
                return dataHandler.ExecuteSQLReturn("spGetLoanCount", paramertList);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        /*

    Frontend page: Report Page
    Title: Get active account details of (Super Admin/ Admin)
    Designed: Irfan Mam
    User story: DFP-446
    Developed: Irfan MAM
    Date created: 6/23/2016

*/

        public List<Account> GetAccountDetails(int companyIdORBranchId, int userRole)
        {
            List<Account> Accounts = new List<Account>();

            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();

            // if user is super admin pass company id for companyIdORBranchId
            // if user is a admin pass  branch id for companyIdORBranchId
            paramertList.Add(new object[] { "@comp_or_branch_id", companyIdORBranchId });
            paramertList.Add(new object[] { "@user_role", userRole });

            // calling stored procedure
            DataSet dataSet = dataHandler.GetDataSet("GetAccountDetailsByUserRole", paramertList);

            // biding data to list
            if (dataSet != null && dataSet.Tables.Count != 0)
            {
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    Account account = new Account();
                    account.LoanId = Convert.ToInt32(dataRow["loan_id"]);
                    account.LoanCode = dataRow["loan_code"].ToString();
                    account.BranchId = Convert.ToInt32(dataRow["branch_id"]);
                    account.LoanNumber = dataRow["loan_number"].ToString();
                    account.BranchName = dataRow["branch_name"].ToString();
                    account.PatBranchName = dataRow["nr_branch_name"].ToString();
                    account.LoanAmount = Convert.ToDecimal(dataRow["loan_amount"].ToString());
                    account.UsedAmount = Convert.ToDecimal(dataRow["used_amount"].ToString());
                    account.ActiveUnits = Convert.ToInt32(dataRow["active_units"]);
                    account.PatBranchAddress1 = dataRow["branch_address_1"].ToString();
                    account.PatBranchAddress2 = dataRow["branch_address_2"].ToString();
                    account.PatCity = dataRow["city"].ToString();
                    Accounts.Add(account);

                }


            }


            // returning the List of account details of relevant user (Super Admin/ Admin)
            return Accounts;
        }
        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/2/23
        /// Get all not advanced unit details by loan id
        /// </summary>
        /// <param name="loanId"></param>
        /// <returns>unitList</returns>
        public List<Unit> GetNotAdvancedUnitDetailsByLoanId(int loanId)
        {

            DataHandler dataHandler = new DataHandler();
            List<object[]> parameterList = new List<object[]>();
            List<Unit> unitList = new List<Unit>();

            parameterList.Add(new object[] { "@loan_id", loanId });
            try
            {
                DataSet dataSet =  dataHandler.GetDataSet("spGetNotAdvancedUnitDetailsByLoanId", parameterList);

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

                        unit.UnitId = reader["unit_id"].ToString();
                        unit.CreatedDate = Convert.ToDateTime(reader["created_date"].ToString());
                        unit.IdentificationNumber = reader["identification_number"].ToString();
                        unit.Year = Convert.ToInt32(reader["year"].ToString());
                        unit.Make = reader["make"].ToString();
                        unit.Model = reader["model"].ToString();
                        unit.Cost = Convert.ToDecimal(reader["cost"].ToString());
                        unit.AdvanceAmount = Convert.ToDecimal(reader["advance_amount"].ToString());
                        unit.IsTitleUploaded = Convert.ToBoolean(reader["IsTitleUploaded"]) ? 1 : 0;

                        unitList.Add(unit);
                    }
                    return unitList;
                }
                else {
                    return unitList;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/2/5
        /// insert details of interest which related to a loan
        /// </summary>
        /// <returns>countval</returns>
        public int insertInterestDetails(Interest interest)
        {
            DataHandler dataHandler = new DataHandler();
            List<object[]> paramertList = new List<object[]>();            

            paramertList.Add(new object[] { "@interest_rate", interest.InterestRate });
            paramertList.Add(new object[] { "@paid_date", interest.PaidDate });
            paramertList.Add(new object[] { "@payment_period", interest.PaymentPeriod });
            paramertList.Add(new object[] { "@auto_remind_email", interest.AutoRemindEmail });
            paramertList.Add(new object[] { "@auto_remind_period", interest.RemindPeriod });
            paramertList.Add(new object[] { "@loan_id", interest.LoanId });
            paramertList.Add(new object[] { "@accrual_method_id", interest.AccrualMethodId });           

            try
            {
                return dataHandler.ExecuteSQLReturn("spInsertInterestDetails", paramertList);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Created by : Asanka Senarathna
        /// Created date :2016/03/30
        /// Insert User request message
        /// </summary>
        /// <param name="userRequest"></param>
        /// <returns></returns>
        public int InsertUserRequest(UserRequest userRequest)
        {
            try
            {
                DataHandler dataHandler = new DataHandler();
                List<object[]> paramertList = new List<object[]>();
                paramertList.Add(new object[] { "@company_id", userRequest.company_id });
                paramertList.Add(new object[] { "@branch_id", userRequest.branch_id });
                paramertList.Add(new object[] { "@user_id", userRequest.user_id });
                paramertList.Add(new object[] { "@role_id", userRequest.role_id });
                paramertList.Add(new object[] { "@loan_code", userRequest.loan_code });
                paramertList.Add(new object[] { "@page_name", userRequest.page_name });
                paramertList.Add(new object[] { "@topic", userRequest.topic });
                paramertList.Add(new object[] { "@message", userRequest.message });
                paramertList.Add(new object[] { "@priority_level", userRequest.priority_level });

                return dataHandler.ExecuteSQL("spInsertUserRequest", paramertList) ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// CreatedBy:
        /// CreatedDate:4/24/2016
        ///insert log in every transaction
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int InsertLog(Log log)
        {
            try {
                if (log != null)
                {
                    DataHandler dataHandler = new DataHandler();
                    List<object[]> paramertList = new List<object[]>();

                    paramertList.Add(new object[] { "@date_time", log.DateTime });
                    paramertList.Add(new object[] { "@user_id", log.UserId });
                    paramertList.Add(new object[] { "@company_id", log.CompanyId });
                    paramertList.Add(new object[] { "@branch_id", log.BranchId });
                    paramertList.Add(new object[] { "@loan_id", log.LoanId });
                    paramertList.Add(new object[] { "@description", log.Description });
                    paramertList.Add(new object[] { "@page", log.Page });


                    try
                    {
                        return dataHandler.ExecuteSQLReturn("spInsertLog", paramertList);
                    }
                    catch
                    {
                        return 0;
                    }
                    // return 1;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<LoanIdNumber> GetLoanNumbersWithBranch(int companyId)
        {
            List<LoanIdNumber> loanNumbers = new List<LoanIdNumber>();

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

            DataSet dataSet = dataHandler.GetDataSet("spGetLoanNumbersWithBranch", paramertList);

            if (dataSet != null && dataSet.Tables.Count != 0)
            {
                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    LoanIdNumber loanNumber = new LoanIdNumber();
                    loanNumber.LoanId = Convert.ToInt32(dataRow["loan_id"]);
                    loanNumber.LoanNumberB = dataRow["LoanDisplay"].ToString();
                    loanNumber.BranchId = Convert.ToInt32(dataRow["branch_id"]);
                    loanNumbers.Add(loanNumber);
                }
            }

            return loanNumbers;
        }
        /*
         *
         * Frontend page: Fee Page
         * Title: Getting Fees by due date
         * Designed: Nadeeka
         * User story:
         * Developed: Nadeeka
         * Date created: 4/21/2016
         *
         */
        public List <Fees> GetFeesByDueDate(int loanId, DateTime dueDate, string type)
        {
            try
            {
                List <Fees>     lstFee       = new List <Fees>();
                DataHandler     dataHandler  = new DataHandler();
                List <object[]> paramertList = new List <object[]>();
                paramertList.Add(new object[] { "@loan_id", loanId });
                paramertList.Add(new object[] { "@bill_due_date", dueDate });
                paramertList.Add(new object[] { "@type", type });

                DataSet dataSet = dataHandler.GetDataSet("spGetFeesByDueDate", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        Fees fee = new Fees();
                        fee.FeeId       = int.Parse(dataRow["fee_id"].ToString());
                        fee.UnitId      = dataRow["unit_id"].ToString();
                        fee.LoanId      = int.Parse(dataRow["loan_id"].ToString());
                        fee.Type        = dataRow["type"].ToString();
                        fee.Amount      = Convert.ToDecimal(dataRow["amount"].ToString());
                        fee.Description = dataRow["description"].ToString();
                        fee.BillDueDate = Convert.ToDateTime(dataRow["bill_due_date"].ToString());
                        fee.FeeDueDate  = Convert.ToDateTime(dataRow["due_date"].ToString());

                        if (type == "advanceFee")
                        {
                            string[] info = fee.Description.Split(',');
                            if (info != null && info.Length > 0)
                            {
                                if (info[1] != "")
                                {
                                    fee.IdentificationNumber = info[1];
                                }
                                if (info.Length > 2 && info[2] != "")
                                {
                                    fee.Year = Convert.ToInt32(info[2]);
                                }
                                if (info.Length > 3 && info[3] != "")
                                {
                                    fee.Make = info[3];
                                }
                                if (info.Length > 4 && info[4] != "")
                                {
                                    fee.Model = info[4];
                                }
                                if (info.Length > 5 && info[5] != "")
                                {
                                    fee.AdvanceDate = Convert.ToDateTime(info[5]);
                                }
                                else
                                {
                                    fee.AdvanceDate = Convert.ToDateTime(dataRow["due_date"].ToString());
                                }
                            }
                        }

                        lstFee.Add(fee);
                    }
                    return(lstFee);
                }
                else
                {
                    return(null);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate:2016/1/13
        ///
        /// Get user details of a given user
        ///
        /// EditedBy: Kanishka
        /// EditedDate:2016/2/26
        ///
        /// Add user company code and branch code
        ///
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns>GetDetails Object</returns>
        public GetDetails getUserById(int user_id)
        {
            DataHandler     dataHandler  = new DataHandler();
            List <object[]> paramertList = new List <object[]>();

            paramertList.Add(new object[] { "@user_id", user_id });

            //Company company = new Company();
            try
            {
                DataSet dsCompany = dataHandler.GetDataSet("spGetUserDetailsById", paramertList);
                if (dsCompany != null && dsCompany.Tables.Count != 0)
                {
                    GetDetails dtl = new GetDetails();
                    //company.CompanyId = int.Parse(dsCompany.Tables[0].Rows[0]["company_Id"].ToString());
                    //company.CompanyName = dsCompany.Tables[0].Rows[0]["company_name"].ToString();
                    //company.CompanyCode = dsCompany.Tables[0].Rows[0]["company_code"].ToString();
                    //company.CompanyAddress1 = dsCompany.Tables[0].Rows[0]["company_address_1"].ToString();
                    //company.CompanyAddress2 = dsCompany.Tables[0].Rows[0]["company_address_2"].ToString();
                    //company.StateId = int.Parse(dsCompany.Tables[0].Rows[0]["stateId"].ToString());
                    //company.City = dsCompany.Tables[0].Rows[0]["city"].ToString();
                    //company.Zip = dsCompany.Tables[0].Rows[0]["zip"].ToString();

                    //string[] zipWithExtention = company.Zip.Split('-');

                    //if (zipWithExtention[0] != null) company.ZipPre = zipWithExtention[0];
                    //if (zipWithExtention.Count() >= 2 && zipWithExtention[1] != null) company.Extension = zipWithExtention[1];

                    //company.Email = dsCompany.Tables[0].Rows[0]["email"].ToString();
                    //company.PhoneNum1 = dsCompany.Tables[0].Rows[0]["phone_num_1"].ToString();
                    //company.PhoneNum2 = dsCompany.Tables[0].Rows[0]["phone_num_2"].ToString();
                    //company.PhoneNum3 = dsCompany.Tables[0].Rows[0]["phone_num_3"].ToString();
                    //company.Fax = dsCompany.Tables[0].Rows[0]["fax"].ToString();
                    //company.WebsiteUrl = dsCompany.Tables[0].Rows[0]["website_url"].ToString();
                    //company.TypeId = int.Parse(dsCompany.Tables[0].Rows[0]["company_type"].ToString());

                    dtl.userId    = int.Parse(dsCompany.Tables[0].Rows[0]["user_id"].ToString());
                    dtl.firstName = dsCompany.Tables[0].Rows[0]["first_name"].ToString();
                    dtl.lastName  = dsCompany.Tables[0].Rows[0]["last_name"].ToString();
                    dtl.email     = dsCompany.Tables[0].Rows[0]["email"].ToString();
                    dtl.phoneNo   = dsCompany.Tables[0].Rows[0]["phone_no"].ToString();
                    if (bool.Parse(dsCompany.Tables[0].Rows[0]["status"].ToString()))
                    {
                        dtl.status = "Active";
                    }
                    else
                    {
                        dtl.status = "Not Active";
                    }
                    DateTime day = DateTime.Parse(dsCompany.Tables[0].Rows[0]["created_date"].ToString());
                    dtl.createdDate = day.ToShortDateString();

                    dtl.roleName = dsCompany.Tables[0].Rows[0]["role_name"].ToString();
                    if (dsCompany.Tables[0].Rows[0]["branch_name"].ToString() != "")
                    {
                        dtl.branchName = dsCompany.Tables[0].Rows[0]["branch_name"].ToString();
                        dtl.BranchId   = Convert.ToInt32(dsCompany.Tables[0].Rows[0]["branch_id"]);
                        dtl.BranchCode = dsCompany.Tables[0].Rows[0]["BranchCode"].ToString();
                    }
                    else
                    {
                        dtl.branchName = "";
                        dtl.BranchId   = 0;
                        dtl.BranchCode = "";
                    }
                    dtl.CompanyCode = dsCompany.Tables[0].Rows[0]["CompanyCode"].ToString();

                    return(dtl);
                }
                else
                {
                    return(null);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <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;
            }
        }
Example #34
0
        /// <summary>
        /// CreatedBy: MAM. IRFAN
        /// CreatedDate: 2016/02/05
        ///
        /// Getting all non reistered company branches by Registered company id
        ///
        ///
        /// 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 partner branch object list using that dataset
        ///
        /// </summary>
        /// <returns> a list contain all branches</returns>
        ///
        public List <NonRegBranch> getNonRegBranches(int companyId)
        {
            List <NonRegBranch> branchesLists = new List <NonRegBranch>();
            DataHandler         dataHandler   = new DataHandler();
            List <object[]>     paramertList  = new List <object[]>();

            paramertList.Add(new object[] { "@companyId", companyId });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetNonRegBranchesByCompanyId", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        NonRegBranch branch = new NonRegBranch();

                        branch.NonRegBranchId = int.Parse(dataRow["non_reg_branch_id"].ToString());

                        branch.BranchName     = dataRow["branch_name"].ToString();
                        branch.BranchCode     = dataRow["branch_code"].ToString();
                        branch.BranchId       = int.Parse(dataRow["branch_id"].ToString());
                        branch.BranchAddress1 = dataRow["branch_address_1"].ToString();
                        branch.BranchAddress2 = dataRow["branch_address_2"].ToString();
                        branch.StateId        = Convert.ToInt32(dataRow["state_id"].ToString());
                        branch.BranchCity     = dataRow["city"].ToString();
                        branch.BranchZip      = dataRow["zip"].ToString();

                        string[] zipWithExtention = branch.BranchZip.Split('-');

                        if (zipWithExtention[0] != null)
                        {
                            branch.ZipPre = zipWithExtention[0];
                        }
                        if (zipWithExtention.Count() >= 2 && zipWithExtention[1] != null)
                        {
                            branch.Extention = zipWithExtention[1];
                        }
                        else if (zipWithExtention.Count() == 1)
                        {
                            branch.Extention = "";
                        }

                        branch.NonRegCompanyName     = dataRow["company_name"].ToString();
                        branch.CompanyNameBranchName = dataRow["company_name"].ToString() + " - " + dataRow["branch_name"].ToString();
                        branch.BranchEmail           = dataRow["email"].ToString();
                        branch.BranchPhoneNum1       = dataRow["phone_num_1"].ToString();
                        branch.BranchPhoneNum2       = dataRow["phone_num_2"].ToString();
                        branch.BranchPhoneNum3       = dataRow["phone_num_3"].ToString();
                        branch.BranchFax             = dataRow["fax"].ToString();
                        branch.BranchCompany         = Convert.ToInt32(dataRow["company_id"]);
                        branchesLists.Add(branch);
                    }
                    return(branchesLists);
                }
                else
                {
                    return(null);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #35
0
        public List <Branch> GetLoansByBranches(int branchIdL)
        {
            List <Branch>   branchesLists = new List <Branch>();
            DataHandler     dataHandler   = new DataHandler();
            List <object[]> paramertList  = new List <object[]>();

            paramertList.Add(new object[] { "@branch_id", branchIdL });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetLoanByBranchId", 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.LoanId     = int.Parse(dataRow["loan_id"].ToString());
                        branch.LoanNumber = dataRow["loan_number"].ToString();
                        if (!string.IsNullOrEmpty(dataRow["is_title_tracked"].ToString()))
                        {
                            branch.IsTitleTrack = bool.Parse(dataRow["is_title_tracked"].ToString());
                        }
                        else
                        {
                            branch.IsTitleTrack = false;
                        }
                        if (!string.IsNullOrEmpty(dataRow["has_advance_fee"].ToString()) || !string.IsNullOrEmpty(dataRow["has_monthly_loan_fee"].ToString()) || !string.IsNullOrEmpty(dataRow["has_lot_inspection_fee"].ToString()))
                        {
                            branch.HasAdvanceFee = (dataRow["has_advance_fee"]) != DBNull.Value ? (bool)dataRow["has_advance_fee"] : false;
                            branch.HasMonthlyFee = (dataRow["has_monthly_loan_fee"]) != DBNull.Value ? (bool)dataRow["has_monthly_loan_fee"] : false;
                            branch.HasLotFee     = (dataRow["has_lot_inspection_fee"]) != DBNull.Value ? (bool)dataRow["has_lot_inspection_fee"] : false;
                            if (branch.HasAdvanceFee || branch.HasMonthlyFee || branch.HasLotFee)
                            {
                                branch.HasFee = true;
                            }
                            else
                            {
                                branch.HasFee = false;
                            }
                        }
                        else
                        {
                            branch.HasFee = false;
                        }
                        branchesLists.Add(branch);
                    }

                    return(branchesLists);
                }
                else
                {
                    return(null);
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #36
0
        /// <summary>
        /// CreatedBy: Piyumi
        /// CreatedDate: 3/30/2016
        /// get loan details
        /// </summary>
        /// <param name="id"></param>
        /// <param name="role"></param>
        /// <returns>Loan</returns>
        public Loan GetLoanDetails(int id, int role)
        {
            DataHandler     dataHandler  = new DataHandler();
            List <object[]> paramertList = new List <object[]>();

            paramertList.Add(new object[] { "@para", id });
            paramertList.Add(new object[] { "@role", role });
            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetLoan", paramertList);
                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    Loan loanObj = new Loan();
                    loanObj.LoanNumber = dataSet.Tables[0].Rows[0]["loan_number"].ToString();
                    //loanObj.PartnerName = dataSet.Tables[0].Rows[0]["company_name"].ToString()+" - " + dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString(); comment by asanka
                    loanObj.PartnerName = dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString();
                    loanObj.LoanCode    = dataSet.Tables[0].Rows[0]["loan_code"].ToString();

                    loanObj.PartnerType = int.Parse(dataSet.Tables[0].Rows[0]["company_type"].ToString());
                    loanObj.BranchName  = dataSet.Tables[0].Rows[0]["branch_name"].ToString();
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
                        {
                            loanObj.IsTitleTrack = 1;
                        }
                        else
                        {
                            loanObj.IsTitleTrack = 0;
                        }
                    }

                    else
                    {
                        loanObj.IsTitleTrack = 0;
                    }
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
                        {
                            loanObj.LotInspectionFee = 1;
                        }
                        else
                        {
                            loanObj.LotInspectionFee = 0;
                        }
                    }

                    else
                    {
                        loanObj.LotInspectionFee = 0;
                    }
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
                        {
                            loanObj.MonthlyLoanFee = 1;
                        }
                        else
                        {
                            loanObj.MonthlyLoanFee = 0;
                        }
                    }

                    else
                    {
                        loanObj.MonthlyLoanFee = 0;
                    }
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
                        {
                            loanObj.AdvanceFee = 1;
                        }
                        else
                        {
                            loanObj.AdvanceFee = 0;
                        }
                        if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["payment_due_method"].ToString()))
                        {
                            if (dataSet.Tables[0].Rows[0]["payment_due_method"].ToString().Contains("Vehicle Payoff"))
                            {
                                loanObj.AdvanceFeePayAtPayoff = true;
                            }
                            else
                            {
                                loanObj.AdvanceFeePayAtPayoff = false;
                            }
                        }
                    }

                    else
                    {
                        loanObj.AdvanceFee = 0;
                    }
                    if (role == 3)
                    {
                        if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["right_id"].ToString()))
                        {
                            loanObj.Rights = dataSet.Tables[0].Rows[0]["right_id"].ToString();
                        }
                    }

                    loanObj.NonRegBranchId = int.Parse(dataSet.Tables[0].Rows[0]["non_reg_branch_id"].ToString());
                    loanObj.LoanId         = int.Parse(dataSet.Tables[0].Rows[0]["loan_id"].ToString());

                    return(loanObj);
                }
                return(null);
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #37
0
        /// <summary>
        /// Frontend page: Title(loan setup)
        /// Title: Get details of title related to given loanId
        /// Designed: Piyumi
        /// User story:
        /// Date created: 2016/2/9
        /// </summary>
        /// <param name="loanId"></param>
        /// <returns>TitleObject</returns>
        public Title getTitleDetails(int loanId)
        {
            DataHandler     dataHandler  = new DataHandler();
            List <object[]> paramertList = new List <object[]>();

            paramertList.Add(new object[] { "@loan_id", loanId });

            try
            {
                DataSet dataSet = dataHandler.GetDataSet("spGetTitleDetailsByLoanId", paramertList);
                Title   obj1    = new Title();
                //Check dataset is not null and table count >0
                if (dataSet != null && dataSet.Tables.Count != 0)
                {
                    foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                    {
                        obj1.LoanId       = int.Parse(dataRow["loan_id"].ToString());
                        obj1.IsTitleTrack = bool.Parse(dataRow["is_title_tracked"].ToString());
                        //Check whether title need to be tracked
                        if (obj1.IsTitleTrack)
                        {
                            obj1.ReceivedTimeLimit = dataRow["title_received_time_period"].ToString();
                            //Check auto remind period is null or empty
                            if (!string.IsNullOrEmpty(dataRow["auto_remind_period"].ToString()))
                            {
                                obj1.RemindPeriod = int.Parse(dataRow["auto_remind_period"].ToString());
                            }
                            //Check auto remind email is null or empty
                            if (!string.IsNullOrEmpty(dataRow["auto_remind_email"].ToString()))
                            {
                                obj1.RemindEmail = dataRow["auto_remind_email"].ToString();
                            }
                            else
                            {
                                //get auto remind email given in loan details page
                                LoanSetupAccess st = new LoanSetupAccess();
                                obj1.RemindEmail = st.getAutoRemindEmailByLoanId(obj1.LoanId);
                            }
                        }
                        else
                        {
                            //get auto remind email given in loan details page
                            LoanSetupAccess st = new LoanSetupAccess();
                            obj1.RemindEmail = st.getAutoRemindEmailByLoanId(obj1.LoanId);
                        }
                        obj1.IsReceipRequired = bool.Parse(dataRow["is_receipt_required"].ToString());
                        //Check whether receipts are required
                        if (obj1.IsReceipRequired)
                        {
                            obj1.ReceiptRequiredMethod = dataRow["receipt_required_method"].ToString();
                        }
                        //Check need scan copy value is null or empty
                        if (!string.IsNullOrEmpty(dataRow["need_scan_copy"].ToString()))
                        {
                            obj1.NeedScanCopy = bool.Parse(dataRow["need_scan_copy"].ToString());
                            //Check need scan copy value is true
                            if (obj1.NeedScanCopy)
                            {
                                obj1.TitleAcceptMethod = "Scanned Title Adequate";
                            }
                        }
                    }
                    return(obj1);
                }
                else
                {
                    return(obj1);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #38
0
        /// <summary>
        /// CreatedBy:Piyumi
        /// CreatedDate: 03/16/2016
        /// get titles list by identification number
        /// </summary>
        /// <param name="identificationNumber"></param>
        /// <returns>resultList</returns>
        public List <Unit> SearchTitle(string loanCode, string identificationNumber)
        {
            DataHandler     dataHandler  = new DataHandler();
            List <object[]> paramertList = new List <object[]>();

            List <Unit> resultList = new List <Unit>();

            if (!string.IsNullOrEmpty(identificationNumber))
            {
                paramertList.Add(new object[] { "@loan_code", loanCode });
                paramertList.Add(new object[] { "@identification_number", identificationNumber });
                try
                {
                    DataSet dataSet = dataHandler.GetDataSet("spGetTitlesByIdentificationNumber", paramertList);
                    if (dataSet != null && dataSet.Tables.Count != 0)
                    {
                        foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                        {
                            Unit title = new Unit();
                            if (!string.IsNullOrEmpty(dataRow["unit_status"].ToString()) && int.Parse(dataRow["unit_status"].ToString()) == 0)
                            {
                                title.AdvanceDateStr = "";
                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(dataRow["advance_date"].ToString()))
                                {
                                    title.AdvanceDateStr = Convert.ToDateTime(dataRow["advance_date"]).ToString("MM/dd/yyy");
                                }
                            }

                            //else
                            //{
                            //    title.AdvanceDate = "";
                            //}
                            title.UnitId = dataRow["unit_id"].ToString();
                            title.IdentificationNumber = dataRow["identification_number"].ToString();
                            title.Year  = int.Parse(dataRow["year"].ToString());
                            title.Make  = dataRow["make"].ToString();
                            title.Model = dataRow["model"].ToString();

                            if (!string.IsNullOrEmpty(dataRow["title_status"].ToString()))
                            {
                                if (int.Parse(dataRow["title_status"].ToString()) == 0)
                                {
                                    title.CurrentTitleStatus = "Not Received";
                                }
                                else if (int.Parse(dataRow["title_status"].ToString()) == 1)
                                {
                                    title.CurrentTitleStatus = "Received";
                                }
                                else if (int.Parse(dataRow["title_status"].ToString()) == 2)
                                {
                                    title.CurrentTitleStatus = "Returned to Dealer";
                                }
                                else if (int.Parse(dataRow["title_status"].ToString()) == 3)
                                {
                                    title.CurrentTitleStatus = "Sent to Bank";
                                }
                            }
                            else
                            {
                                title.CurrentTitleStatus = "";
                            }
                            if (!string.IsNullOrEmpty(dataRow["unit_status"].ToString()))
                            {
                                title.UnitStatus = int.Parse(dataRow["unit_status"].ToString());
                                if (int.Parse(dataRow["unit_status"].ToString()) == 0)
                                {
                                    title.CurrentUnitStatus = "InActive";
                                }
                                else if (int.Parse(dataRow["unit_status"].ToString()) == 1)
                                {
                                    title.CurrentUnitStatus = "Active";
                                }
                                else if (int.Parse(dataRow["unit_status"].ToString()) == 2)
                                {
                                    title.CurrentUnitStatus = "Paid";
                                }
                            }

                            else
                            {
                                title.CurrentUnitStatus = "";
                            }
                            resultList.Add(title);
                        }

                        return(resultList);
                    }
                    else
                    {
                        return(null);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return(null);
        }
Example #39
0
        /*
         *
         * Frontend page: DashBoard Page
         * Title: Get a inactive loan by loan code
         * Designed: Irfan Mam
         * User story: DFP-484
         * Developed: Irfan MAM
         * Date created: 7/3/2016
         *
         */

        public Loan GetAInActiveLoanDetailsbyLoanCode(string loancode, int role)
        {
            DataHandler     dataHandler  = new DataHandler();
            List <object[]> paramertList = new List <object[]>();

            // add a parameters to a list
            paramertList.Add(new object[] { "@role", role });
            paramertList.Add(new object[] { "@loan_code", loancode });
            try
            {
                // excecute sp and return the datas
                DataSet dataSet = dataHandler.GetDataSet("spGetAInActiveLoanByLoanCode", paramertList);

                // if data exists bind it to loan object
                if (dataSet != null && dataSet.Tables.Count != 0 && dataSet.Tables[0].Rows.Count != 0)
                {
                    Loan loanObj = new Loan();
                    loanObj.LoanNumber  = dataSet.Tables[0].Rows[0]["loan_number"].ToString();
                    loanObj.PartnerName = dataSet.Tables[0].Rows[0]["non_reg_branch_name"].ToString();
                    loanObj.LoanCode    = dataSet.Tables[0].Rows[0]["loan_code"].ToString();
                    loanObj.PartnerType = int.Parse(dataSet.Tables[0].Rows[0]["company_type"].ToString());
                    loanObj.BranchName  = dataSet.Tables[0].Rows[0]["branch_name"].ToString();
                    // if is_title_tracked has a value
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
                    {
                        // check that value
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["is_title_tracked"].ToString()))
                        {
                            loanObj.IsTitleTrack = 1;
                        }
                        else
                        {
                            loanObj.IsTitleTrack = 0;
                        }
                    }
                    //  if is_title_tracked has not a value
                    else
                    {
                        loanObj.IsTitleTrack = 0;
                    }

                    // if has_lot_inspection_fee has a value
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_lot_inspection_fee"].ToString()))
                        {
                            loanObj.LotInspectionFee = 1;
                        }
                        else
                        {
                            loanObj.LotInspectionFee = 0;
                        }
                    }

                    // if has_lot_inspection_fee has not a value
                    else
                    {
                        loanObj.LotInspectionFee = 0;
                    }

                    // if has_monthly_loan_fee has a value
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_monthly_loan_fee"].ToString()))
                        {
                            loanObj.MonthlyLoanFee = 1;
                        }
                        else
                        {
                            loanObj.MonthlyLoanFee = 0;
                        }
                    }
                    // if has_monthly_loan_fee  not a value
                    else
                    {
                        loanObj.MonthlyLoanFee = 0;
                    }

                    // if has_advance_fee has a value
                    if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
                    {
                        if (bool.Parse(dataSet.Tables[0].Rows[0]["has_advance_fee"].ToString()))
                        {
                            loanObj.AdvanceFee = 1;
                        }
                        else
                        {
                            loanObj.AdvanceFee = 0;
                        }
                        if (!string.IsNullOrEmpty(dataSet.Tables[0].Rows[0]["payment_due_method"].ToString()))
                        {
                            if (dataSet.Tables[0].Rows[0]["payment_due_method"].ToString().Contains("Vehicle Payoff"))
                            {
                                loanObj.AdvanceFeePayAtPayoff = true;
                            }
                            else
                            {
                                loanObj.AdvanceFeePayAtPayoff = false;
                            }
                        }
                    }
                    // if has_advance_fee has not a value
                    else
                    {
                        loanObj.AdvanceFee = 0;
                    }


                    loanObj.StartDate      = Convert.ToDateTime(dataSet.Tables[0].Rows[0]["start_date"].ToString());
                    loanObj.LoanAmount     = Convert.ToDecimal(dataSet.Tables[0].Rows[0]["loan_amount"].ToString());
                    loanObj.MaturityDate   = Convert.ToDateTime(dataSet.Tables[0].Rows[0]["maturity_date"].ToString());
                    loanObj.NonRegBranchId = int.Parse(dataSet.Tables[0].Rows[0]["non_reg_branch_id"].ToString());
                    loanObj.LoanId         = int.Parse(dataSet.Tables[0].Rows[0]["loan_id"].ToString());

                    // return the loan object
                    return(loanObj);
                }

                // if there is no data return null
                return(null);
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }