Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- namespace Occfinance.Code
- {
- public class Q_Reports
- {
- public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
- System.Web.HttpContext con = HttpContext.Current;
- /// <summary>
- /// Set the jsTree data format
- /// </summary>
- /// <param name="hdnnamearray"></param>
- /// <param name="hdnnamearrayparents"></param>
- public void FormatJsTreeData(ref string hdnnamearray, ref string hdnnamearrayparents)
- {
- if (!string.IsNullOrEmpty(hdnnamearray))
- {
- var negIds = string.Empty;
- var compbranchIds = string.Empty;
- foreach (var ids in hdnnamearray.Split(','))
- {
- var splitIds = ids.ToString().Split('_');
- if (splitIds.Length == 3)
- {
- negIds += splitIds[2].ToString() + ",";
- compbranchIds += splitIds[0].ToString() + "®" + splitIds[1].ToString() + ",";
- }
- }
- hdnnamearray = negIds.Substring(0, negIds.Length - 1);
- hdnnamearrayparents = compbranchIds.Substring(0, compbranchIds.Length - 1);
- }
- }
- /// <summary>
- /// Set company, branch and negotiator ids
- /// </summary>
- /// <param name="hdnnamearray"></param>
- /// <param name="_allNamesparents"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="NegotiatorIds"></param>
- public void SetCompanyBranchAndNegotiatorIds(string hdnnamearray, string[] _allNamesparents, out List<int> CompanyIds, out List<int> BranchIds, out List<int> NegotiatorIds)
- {
- // 2015-10-29 Negotiators, Branches and Companies Ids
- CompanyIds = new List<int>();
- BranchIds = new List<int>();
- NegotiatorIds = new List<int>();
- if (_allNamesparents.Length > 0)
- {
- foreach (var comp_branch in _allNamesparents)
- {
- if (!string.IsNullOrEmpty(comp_branch))
- {
- CompanyIds.Add(Convert.ToInt32(comp_branch.Split('®')[0]));
- BranchIds.Add(Convert.ToInt32(comp_branch.Split('®')[1]));
- }
- }
- CompanyIds = CompanyIds.Distinct().ToList();
- BranchIds = BranchIds.Distinct().ToList();
- if (!string.IsNullOrEmpty(hdnnamearray))
- NegotiatorIds = hdnnamearray.Split(',').Select(Int32.Parse).Distinct().ToList();
- }
- }
- /// <summary>
- /// Get product type details
- /// </summary>
- /// <returns></returns>
- public List<SelectListItem> GetProductTypeDetails()
- {
- var objTblfinancetype = (from ftype in ctx.tblfinancetypes
- select new ProductType
- {
- ProductId = ftype.financetypeid,
- ProductName = ftype.type
- }).ToList();
- var productitems = new List<SelectListItem>();
- foreach (var x in objTblfinancetype)
- {
- productitems.Add(new SelectListItem { Value = x.ProductId.ToString(), Text = x.ProductName });
- }
- productitems.OrderBy(m => m.Value).ToList();
- return productitems;
- }
- /// <summary>
- /// Get all finance type
- /// </summary>
- /// <returns></returns>
- public List<ProductType> GetAllFinanceType()
- {
- var objTblfinancetype = (from ftype in ctx.tblfinancetypes
- select new ProductType
- {
- ProductId = ftype.financetypeid,
- ProductName = ftype.type
- }).ToList();
- return objTblfinancetype;
- }
- /// <summary>
- /// Find negotiation report by contactid
- /// </summary>
- /// <returns></returns>
- public List<contactTbl> FindNegotiationReportByContactId()
- {
- List<contactTbl> negotiationReportList = new List<contactTbl>();
- try
- {
- SqlParameter[] Param = new SqlParameter[0];
- DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetNegotiationReportList_Update", Param);
- negotiationReportList = (from DataRow row in dt.Rows
- select new contactTbl
- {
- negref = row["negref"].ToString()
- }).ToList();
- // result = ctx.tblcontacts.Where(u => u.contactid == id).Select(u => new contactTbl() { negref=u.negref}).ToList();
- return negotiationReportList;
- }
- catch (Exception)
- {
- return negotiationReportList;
- }
- }
- /// <summary>
- /// Getting dataset from the database for Negotiator Report
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="pageNo">Page no</param>
- /// <param name="pageSize">Page size</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorResult(DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[10];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
- Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- Param[9] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReport", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
- return ds = new DataSet();
- }
- }
- /// <summary>
- /// Getting dataset for Negotiator Report with page no
- /// </summary>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user Id</param>
- /// <param name="pageNo">page no</param>
- /// <param name="pageSize">page size</param>
- /// <param name="brokerId">broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <returns></returns>
- public ReportsDataModel GetNegotiatorDataResult(DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- ReportsDataModel dataResult = new ReportsDataModel();
- dataResult.Negotiation = new List<Negotiation>();
- dataResult.NegotiationNotes = new List<NegotiationNotes>();
- try
- {
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
- Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "GetNegotiatorReport", Param);
- ctx.Database.Initialize(force: false);
- ctx.Database.Connection.Open();
- dataResult.Negotiation = ((IObjectContextAdapter)ctx).ObjectContext.Translate<Negotiation>(reader).ToList();
- reader.NextResult();
- dataResult.NegotiationNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- ctx.Database.Connection.Close();
- if (!reader.IsClosed)
- {
- reader.Close();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorDataResult", ex.Message);
- }
- return dataResult;
- }
- /// <summary>
- /// Getting the dataset for Notes Data
- /// </summary>
- /// <param name="userId">User Id</param>
- /// <param name="pageNo">Page No</param>
- /// <param name="pageSize">Page Size</param>
- /// <param name="fromDate">FRom Date</param>
- /// <param name="toDate">To Date</param>
- /// <param name="negotiators">Negotiators</param>
- /// <param name="company">Companies</param>
- /// <param name="branches">branches</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="productTypeID">Product type</param>
- /// <returns></returns>
- public ReadNotes GetNotesData(int userId, int pageNo, int pageSize, DateTime fromDate, DateTime toDate, string negotiators = "", string company = "", string branches = "", string brokers = "", int productTypeID = 0)
- {
- var reportsDN = new ReadNotes();
- try
- {
- DataTable notes = new DataTable();
- SqlParameter[] Param = new SqlParameter[10];
- Param[0] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(negotiators) ? null : negotiators.Trim()));
- Param[1] = new SqlParameter("@Company", (string.IsNullOrEmpty(company) ? null : company.Trim()));
- Param[2] = new SqlParameter("@Branches", (string.IsNullOrEmpty(branches) ? null : branches.Trim()));
- Param[3] = new SqlParameter("@BrokerIds", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[4] = new SqlParameter("@FromDate", fromDate.ToString("dd/MMM/yyyy"));
- Param[5] = new SqlParameter("@ToDate", toDate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@CurrentUserId", userId);
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- Param[9] = new SqlParameter("@productTypeID", productTypeID);
- SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "Usp_GetNotesData", Param);
- ctx.Database.Initialize(force: false);
- ctx.Database.Connection.Open();
- reportsDN.AdvisorNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- reader.NextResult();
- reportsDN.AgentNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- ctx.Database.Connection.Close();
- if (!reader.IsClosed)
- {
- reader.Close();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNotesData", ex.Message);
- }
- return reportsDN; ;
- }
- //added on 2015-02-03
- /// <summary>
- /// Getting Weekly created notes data
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorWeeklyCreatedNotes(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteCreated", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyCreatedNotes", ex.Message);
- ds = new DataSet();
- return ds;
- }
- }
- //added on 2015-02-03
- /// <summary>
- /// Getting weekly updated notes
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorWeeklyUpdatedNotes(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteUpdated", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyUpdatedNotes", ex.Message);
- ds = new DataSet();
- return ds;
- }
- }
- /// <summary>
- /// Getting negotiator data for PDF
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiator(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReport", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiator", ex.Message);
- }
- return ds;
- }
- /********************************************************************
- * Code Updatd : 24/01/2015
- *
- * Description : Method added for weekly report helper for GetNegotiator
- *
- * Optimized code block added
- * ******************************************************************/
- /// <summary>
- /// Getting negotiator weekly report
- /// </summary>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorweekly(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeekly", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorweekly", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- /// <summary>
- /// Getting Adviser Report Dataset
- /// </summary>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="type">Type Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">branches</param>
- /// <returns></returns>
- public DataSet GetAdvisorReport(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, int type = 1, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[6] = new SqlParameter("@Company", Company.Trim());
- Param[7] = new SqlParameter("@Branches", Branches.Trim());
- }
- Param[2] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[3] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrWhiteSpace(brokers))
- Param[4] = new SqlParameter("@BrokerId", brokers);
- Param[5] = new SqlParameter("@Type", type);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAdvisorReport", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAdviserReport", ex.Message);
- }
- return ds;
- }
- /// <summary>
- /// Getting Branch Report
- /// </summary>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="Branch">Brnach Name</param>
- /// <returns></returns>
- public DataSet GetBranchReport(DateTime fromdate, DateTime todate, string Branch = "")
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[3];
- Param[0] = new SqlParameter("@Branch", Branch.Trim());
- Param[1] = new SqlParameter("@FromDate", fromdate);
- Param[2] = new SqlParameter("@ToDate", todate);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBranchReport", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetBranchReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- //modified on 23-12-2014
- /// <summary>
- /// Get dataset for Mortgage case written report
- /// </summary>
- /// <param name="type">type id</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">ProductTypeId</param>
- /// <returns></returns>
- public DataSet GetMortgageCaseWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCaseWrittenReport", ex.Message);
- }
- return ds;
- }
- /// <summary>
- /// Getting dataset for overall conversion report for Introducer Report
- /// </summary>
- /// <param name="totalLeads">Total Leads output parameter</param>
- /// <param name="totalApps">Total Apps Output parameter</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user Id</param>
- /// <param name="brokerId">broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type Id</param>
- public void GetOverAllConversionReport(out int totalLeads, out int totalApps, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- totalLeads = 0;
- totalApps = 0;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", "'" + Negotiator.TrimEnd(',') + "'");
- Param[2] = new SqlParameter("@Company", "'" + Company.TrimEnd(',') + "'");
- Param[3] = new SqlParameter("@Branches", "'" + Branches.TrimEnd(',') + "'");
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[4] = new SqlParameter("@BrokerId", brokers);
- }
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetOverAllConversionForIntroducer", Param);
- if (ds.Tables[0].Rows.Count > 0)
- {
- totalLeads = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalCount"].ToString());
- }
- if (ds.Tables[1].Rows.Count > 0)
- {
- totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"].ToString());
- }
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetOverAllConversionReport", ex.Message);
- }
- }
- #region [Get Insurance Written Report 2014-12-23]
- /// <summary>
- /// method for InsuranceCaseWritten
- /// created on : 03/11/2014
- ///modified on 23-12-2014
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">branches</param>
- /// <param name="productTypeID">product type Id</param>
- /// <returns></returns>
- public DataSet GetInsuranceCaseWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCaseWritten", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceCaseWrittenReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #endregion [Get Insurance Written Report 2014-12-23]
- #region [Get Mortgage Completed Report 2015-03-30]
- /// <summary>
- /// Getting dataset for Mortgage completed report
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">branches</param>
- /// <param name="productTypeID">product type id</param>
- /// <returns></returns>
- public DataSet GetMortgageCompletedReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCompletedReport", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCompletedReport", ex.Message);
- }
- return ds;
- }
- #endregion [Get Mortgage Completed Report 2015-03-30]
- #region [Get Insurance Completed Report 2015-03-30]
- /// <summary>
- /// Dataset for Insurance Completed Report
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">branches</param>
- /// <param name="productTypeID">product type Id</param>
- /// <returns></returns>
- public DataSet GetInsuranceCompletedReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCompletedReport", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceCompletedReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #endregion [Get Insurance Completed Report 2015-03-30]
- // method for InsuranceCaseWritten
- // created on : 12/11/2014
- /// <summary>
- /// Getting WOCC Report
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">branches</param>
- /// <returns></returns>
- public DataSet GetPipelineReviewReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetPipelineReviewReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- /// <summary>
- /// Get new financial information report!
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">Branches</param>
- /// <returns></returns>
- //modified on 23-12-2014
- public DataSet GetNewFinancialInformationReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int FinType = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[7] = new SqlParameter("@FinanceType", FinType);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetWOCCReport", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNewFinancialInformationReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- /// <summary>
- /// Getting Dataset for Remortgage Report
- /// </summary>
- /// <param name="totalApps">Return total apps</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Year">Year filter</param>
- /// <param name="Month">Month filter</param>
- /// <returns></returns>
- public DataSet GetReMortgageReport(out int totalApps, int userid, int brokerId = 0, int Year = 0, int Month = 0)
- {
- DataSet ds;
- totalApps = 0;
- try
- {
- SqlParameter[] Param = new SqlParameter[4];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@BrokerId", brokerId);
- Param[2] = new SqlParameter("@Year", Year);
- Param[3] = new SqlParameter("@Month", Month);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetReMortgageReport_Update", Param);
- if (ds.Tables[1].Rows.Count > 0)
- {
- totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalApps"].ToString());
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetRemortgageReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- /// <summary>
- /// Used to return reports data model for mortgage case written view
- /// </summary>
- /// <param name="userId"></param>
- /// <param name="loggedInUserId"></param>
- /// <returns></returns>
- public ReportsDataModel GetMortgageCaseWrittenDetails(int userId, int loggedInUserId)
- {
- var reportsDM = new ReportsDataModel();
- try
- {
- reportsDM.MortgageTreeModel = new MortgageTreeModel();
- reportsDM.MortgageTreeModel.IsSubmit = 0;
- reportsDM.MortgageTreeModel.Purchase = "Residential Purchase";
- var qSettings = new Q_settings();
- var clients = qSettings.FindBrokerForLoggedInUserWithUserId(userId, loggedInUserId);
- if (clients == null)
- {
- reportsDM.MortgageTreeModel.BItems = new List<Clients>();
- }
- reportsDM.MortgageTreeModel.BItems = clients;
- var qClients = new Q_Client();
- reportsDM.MortgageTreeModel.ClientsOrderBy = qClients.FindClients(userId, loggedInUserId).OrderBy(c => c.companyname).ToList();
- reportsDM.MortgageTreeModel.BrokersWithTeams = qSettings.GetBrokersWithTeams(loggedInUserId);
- reportsDM.MortgageTreeModel.ProductTypeList = GetAllFinanceType();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCaseWrittenDetails", ex.Message);
- }
- return reportsDM;
- }
- /// <summary>
- /// Used to return contact list by account number.
- /// </summary>
- /// <param name="acccountNumber"></param>
- /// <returns></returns>
- public List<tblcontact> GetContactByAccount(int acccountNumber)
- {
- var qNewLeads = new Q_NewLead();
- return qNewLeads.FindContactsForAccount(acccountNumber);
- }
- /// <summary>
- /// Get all companies details
- /// </summary>
- /// <returns></returns>
- public List<clsCompanyDetails> GetAllCompaniesDetails()
- {
- List<clsCompanyDetails> lstCompanyDetails;
- try
- {
- DataTable dtCompanyDetails = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllCompaniesInfo_Update");
- lstCompanyDetails = new List<clsCompanyDetails>(dtCompanyDetails.Rows.Count);
- foreach (DataRow row in dtCompanyDetails.Rows)
- {
- var values = row.ItemArray;
- var category = new clsCompanyDetails()
- {
- AccountId = Convert.ToInt32(values[0]),
- CompanyName = Convert.ToString(values[1]),
- Branch = Convert.ToString(values[2]),
- BranchId = Convert.ToInt32(values[3]),
- Negotiator = Convert.ToString(values[4]),
- NegotiatorId = Convert.ToInt32(values[5])
- };
- lstCompanyDetails.Add(category);
- }
- }
- catch (Exception)
- {
- throw;
- }
- return lstCompanyDetails;
- }
- #region Total written Report
- /// <summary>
- /// Returning dataset for total written report
- /// </summary>
- /// <param name="type">Type</param>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">branches</param>
- /// <param name="productTypeID">ProductType Id</param>
- /// <returns></returns>
- public DataSet GetTotalWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[7];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWritten", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetTotalWrittenReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #endregion
- #region [Optimized Code of Mortgage Case Written Report 2015-01-22]
- /********************************************************
- * Code Updated on : 23/01/2015
- * Report related methods taked from settings
- *
- *
- * *******************************************************/
- /// <summary>
- /// Get all broker details
- /// </summary>
- /// <param name="accid"></param>
- /// <param name="userId"></param>
- /// <returns></returns>
- public List<Clients> GetAllBrokerDetails(int accid, int userId)
- {
- List<Clients> lstBrokers = new List<Clients>();
- try
- {
- int userType = 0;
- var Id = GetUserTypeAndId(userId, out userType);
- SqlParameter[] Param = new SqlParameter[2];
- Param[0] = new SqlParameter("@UserType", userType);
- Param[1] = new SqlParameter("@ID", Id);
- DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUserWithUserId", Param);
- lstBrokers = (from DataRow row in dt.Rows
- select new Clients
- {
- clientid = Convert.ToInt32(row["clientid"]),
- parent = Convert.ToInt32(row["parent"]),
- companyname = Convert.ToString(row["companyname"]),
- TeamName = Convert.ToString(row["TeamName"]),
- TeamId = Convert.ToInt32(row["TeamId"]),
- affiliateid = Convert.ToString(row["affiliateid"]),
- telephone = Convert.ToString(row["telephone"]),
- email = Convert.ToString(row["email"]),
- contactname = Convert.ToString(row["contactname"]),
- address = Convert.ToInt32(row["address"]),
- userid = Convert.ToInt32(row["userid"]),
- AdminId = Convert.ToInt32(row["AdminId"]),
- countLeads = Convert.ToInt32(row["countLeads"])
- }).ToList();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAllBrokerDetails", ex.Message);
- throw;
- }
- return lstBrokers;
- }
- /// <summary>
- /// Get broker details with team
- /// </summary>
- /// <param name="userId"></param>
- /// <returns></returns>
- public List<Clients> GetBrokersWithTeams(int userId)
- {
- List<Clients> lstBrokerTeam = new List<Clients>();
- int userType = 0;
- var Id = GetUserTypeAndId(userId, out userType);
- try
- {
- SqlParameter[] Param = new SqlParameter[2];
- Param[0] = new SqlParameter("@UserType", userType);
- Param[1] = new SqlParameter("@ID", Id);
- DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokersWithTeams", Param);
- lstBrokerTeam = (from DataRow row in dt.Rows
- select new Clients
- {
- clientid = Convert.ToInt32(row["clientid"]),
- parent = Convert.ToInt32(row["parent"]),
- companyname = Convert.ToString(row["companyname"]),
- TeamName = Convert.ToString(row["TeamName"]),
- TeamId = Convert.ToInt32(row["TeamId"]),
- affiliateid = Convert.ToString(row["affiliateid"]),
- telephone = Convert.ToString(row["telephone"]),
- email = Convert.ToString(row["email"]),
- contactname = Convert.ToString(row["contactname"]),
- address = Convert.ToInt32(row["address"]),
- userid = Convert.ToInt32(row["userid"])
- }).ToList();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetBrokersWithTeams", ex.Message);
- throw;
- }
- return lstBrokerTeam;
- }
- /// <summary>
- /// Get user type and id accordingly.
- /// </summary>
- /// <param name="userId"></param>
- /// <param name="userType"></param>
- /// <returns></returns>
- private object GetUserTypeAndId(int userId, out int userType)
- {
- int Id = 0;
- try
- {
- SqlParameter[] Param = new SqlParameter[1] { new SqlParameter("@UserId", userId) };
- DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetUserType", Param);
- Id = Convert.ToInt32(dt.Rows[0]["Id"]);
- userType = Convert.ToInt32(dt.Rows[0]["userType"]);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetUserTypeAndId", ex.Message);
- throw;
- }
- return Id;
- }
- #endregion [Optimized Code of Mortgage Case Written Report 2015-01-22]
- //Insurance Referral report
- /// <summary>
- /// Insurance Referral Report
- /// </summary>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="ProductTypeId">ProductType</param>
- /// <returns></returns>
- public DataSet GetInsuranceReferralResult(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int ProductTypeId = 0)
- {
- DataSet ds;
- try
- {
- List<Insurancereferral> negotiationlist = new List<Models.Insurancereferral>();
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@ProductTypeId", ProductTypeId);
- ds = SqlHelper.ExecuteDataset("GetInsuranceReferralReport", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceReferralResult", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #region [Client Referrals 2015-04-18]
- /// <summary>
- /// Getting count for referral for contact Id
- /// </summary>
- /// <param name="contactId">Contact Id</param>
- /// <returns></returns>
- public int ClientReferralsCount(string contactId)
- {
- var con = contactId.Split('_').ToList();
- int ContactId = 0;
- var contactids = new List<int>();
- if (con.Count() > 1)
- {
- ContactId = Convert.ToInt32(con[0]);
- contactids = ctx.tblcontacts.Where(x => x.parentNegId == ContactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- }
- else
- {
- ContactId = Convert.ToInt32(contactId);
- contactids = ctx.tblcontacts.Where(x => x.parentContactId == ContactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- }
- //var contactids = ctx.tblcontacts.Where(x => x.parentContactId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- int count = 0;
- if (contactids.Count > 0)
- {
- count++;
- contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- while (contactids.Count > 0)
- {
- contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- count++;
- }
- }
- return count;
- }
- /// <summary>
- /// Getting Dataset for Client Referral Report
- /// </summary>
- /// <param name="ContactId">Contact Id</param>
- /// <param name="referralLevel">Referral Level</param>
- /// <param name="referralType">Referral Type</param>
- /// <returns></returns>
- public List<ClientReferralsInfo> ClientReferralInfo(string ContactId = "", int referralLevel = 0, int referralType = 0)
- {
- int contactId = 0;
- var contactids = new List<int>();
- var con = ContactId.Split('_').ToList();
- if (con.Count > 1)
- {
- contactId = Convert.ToInt32(con[0]);
- contactids = ctx.tblcontacts.Where(x => x.parentNegId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- }
- else
- {
- contactId = Convert.ToInt32(ContactId);
- contactids = ctx.tblcontacts.Where(x => x.parentContactId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- }
- int count = 0;
- List<ClientReferralsInfo> referralList = new List<ClientReferralsInfo>();
- if (contactids.Count > 0)
- {
- count++;
- //if (referralLevel >= count || referralLevel == 0)
- //{
- foreach (var c in contactids)
- {
- ClientReferralsInfo objClient = new ClientReferralsInfo();
- objClient.ContactId = c.ToString();
- objClient.ReferralLevel = count;
- referralList.Add(objClient);
- }
- // }
- contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- while (contactids.Count > 0)
- {
- count++;
- //if (referralLevel >= count || referralLevel == 0)
- //{
- foreach (var c in contactids)
- {
- ClientReferralsInfo objClient = new ClientReferralsInfo();
- objClient.ContactId = c.ToString();
- objClient.ReferralLevel = count;
- referralList.Add(objClient);
- }
- //}
- contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
- }
- }
- referralList = referralList.OrderBy(x => x.ReferralLevel).ToList();
- //adding parent contact
- ClientReferralsInfo objClient1 = new ClientReferralsInfo();
- objClient1.ContactId = ContactId;
- objClient1.ReferralLevel = 0;
- referralList.Insert(0, objClient1);
- //adding parent contact
- foreach (var v in referralList)
- {
- var conn = v.ContactId.Split('_').ToList();
- if (conn.Count == 1)
- {
- int cont = Convert.ToInt32(v.ContactId);
- var WrittenCommission = (from f in ctx.tblfinances
- join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
- where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
- && (fs.status == 6 || fs.status == 13) && f.contact == cont
- select new
- {
- f.commission
- }).Sum(x => x.commission);
- var WrittenMortgage = (from f in ctx.tblfinances
- join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
- where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
- && (fs.status == 6) && f.contact == cont
- select new
- {
- f.amount
- }).Sum(x => x.amount);
- var IssuedCommission = (from f in ctx.tblfinances
- join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
- where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
- && (fs.status == 8 || fs.status == 14) && f.contact == cont
- select new
- {
- f.commission
- }).Sum(x => x.commission);
- var Issuedmortgage = (from f in ctx.tblfinances
- join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
- where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
- && (fs.status == 8) && f.contact == cont
- select new
- {
- f.amount
- }).Sum(x => x.amount);
- var name = ctx.tblcontacts.Where(x => x.contactid == cont).Select(x => new { x.firstname, x.lastname, x.parentContactId }).FirstOrDefault();
- var ReferrerName = ctx.tblcontacts.Where(x => x.contactid == name.parentContactId).Select(x => new { x.firstname, x.lastname }).FirstOrDefault();
- v.WrittenCommission = WrittenCommission ?? 0;
- v.WrittenMortgage = WrittenMortgage ?? 0;
- v.IssuedCommission = IssuedCommission ?? 0;
- v.IssuedMortgage = Issuedmortgage ?? 0;
- v.ContactName = (name.firstname ?? "") + " " + (name.lastname ?? "");
- if (ReferrerName != null)
- v.ReferrerName = (ReferrerName.firstname ?? "") + " " + (ReferrerName.lastname ?? "");
- else
- v.ReferrerName = "";
- string parent = "";
- parent = ctx.tblcontacts.Where(x => x.contactid == cont && v.ContactId != ContactId && x.parentContactId != 0).Select(x => x.parentContactId).FirstOrDefault().ToString();
- if (parent == "")
- {
- parent = ctx.tblcontacts.Where(x => x.contactid == cont && v.ContactId != ContactId && x.parentNegId != 0).Select(x => x.parentNegId).FirstOrDefault().ToString();
- if (parent != string.Empty)
- parent = parent + "_neg";
- else
- parent = null;
- }
- v.ParentContactId = parent;
- }
- else
- {
- int neg = Convert.ToInt32(conn[0]);
- v.ContactName = ctx.tblClientReferralNeg.Where(x => x.NegId == neg).FirstOrDefault().NegName;
- }
- }
- referralList = referralList.OrderBy(x => x.ReferralLevel).ThenByDescending(x => x.WrittenCommission).ToList();
- //for new dropdown list of referral type
- int loop = referralType;
- if (referralType == 0)
- {
- loop = referralList.Select(x => x.ReferralLevel).Max();
- }
- foreach (var v in referralList)
- {
- int i = 1;
- var cIds = referralList.Where(x => x.ParentContactId == v.ContactId).Select(x => x.ContactId).ToList();
- var writMort = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
- var writComm = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
- var Issumort = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
- var IssuComm = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
- while (i < loop)
- {
- cIds = referralList.Where(x => cIds.Contains(x.ParentContactId)).Select(x => x.ContactId).ToList();
- if (referralType == 0)
- {
- writMort += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
- writComm += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
- Issumort += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
- IssuComm += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
- }
- if (cIds.Count > 0)
- {
- i++;
- }
- else
- {
- break;
- }
- }
- if (referralType == 0)
- {
- v.RefWrittenMortgage = writMort;
- v.RefWrittenCommission = writComm;
- v.RefIssuedMortgage = Issumort;
- v.RefIssuedCommission = IssuComm;
- }
- else
- {
- v.RefWrittenMortgage = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
- v.RefWrittenCommission = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
- v.RefIssuedMortgage = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
- v.RefIssuedCommission = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
- }
- }
- if (referralLevel > 0)
- {
- referralList = referralList.Where(x => x.ReferralLevel <= referralLevel).ToList();
- }
- return referralList;
- }
- #endregion [Client Referrals 2015-04-18]
- #region[Business report]
- /// <summary>
- /// Getting Business report
- /// </summary>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To date</param>
- /// <param name="type">type</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">negotiator</param>
- /// <param name="brokers">brokers</param>
- /// <param name="Company">company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">ProductTypeId</param>
- /// <returns></returns>
- public DataSet GetBusinessReport(DateTime fromdate, DateTime todate, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBusinessReport", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_report", "GetBusinessReport", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #endregion
- #region[Most Valuable referrer report]
- /// <summary>
- /// Getting most valuable referral Report
- /// </summary>
- /// <param name="type">type</param>
- /// <param name="userid">user id</param>
- /// <param name="brokerId">broker id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product type Id</param>
- /// <returns></returns>
- public DataSet GetMostValuableReport(string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[5];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[2] = new SqlParameter("@Company", Company.Trim());
- Param[3] = new SqlParameter("@Branches", Branches.Trim());
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[4] = new SqlParameter("@BrokerId", brokers);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMostValuableReferrersreport", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMostValuableReport", ex.Message);
- }
- return ds;
- }
- #endregion
- #region [Updated Code of Reports Data Access Methods 2015-10-21]
- #region [Adviser Report]
- /// <summary>
- /// Updated data access code of adviser report. It fetched data on the basis of negotiator, branch and company ids. Updated on 2015-10-21
- /// </summary>
- /// <param name="NegotiatorIds">Contains negotiator's ids</param>
- /// <param name="BranchIds">Contain branch's ids</param>
- /// <param name="CompanyIds">Contain company's ids</param>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User id</param>
- /// <param name="brokerId">Broker ids</param>
- /// <param name="type">Type</param>
- /// <param name="Negotiator">Negotiators</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <returns></returns>
- public DataSet GetAdvisorReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, int type = 1, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- DataSet ds = new DataSet();
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[6] = new SqlParameter("@Company", Company.Trim());
- Param[7] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- Param[2] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[3] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrWhiteSpace(brokers))
- Param[4] = new SqlParameter("@BrokerId", brokers);
- Param[5] = new SqlParameter("@Type", type);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAdvisorReport_Updated", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetAdvisorReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Adviser Report]
- #region [Mortgage Written Report]
- /// <summary>
- /// Get mortgage case written report by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetMortgageCaseWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[12];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetMortgageCaseWrittenReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Mortgage Written Report]
- #region [Insurance Case Written Report]
- /// <summary>
- /// method for InsuranceCaseWritten
- /// created on : 03/11/2014
- ///modified on 23-12-2014
- /// </summary>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetInsuranceCaseWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[12];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCaseWritten_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceCaseWrittenReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Insurance Case Written Report]
- #region [Mortgage Completed Report]
- /// <summary>
- /// Get mortgage completed report by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetMortgageCompletedReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[12];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCompletedReport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetMortgageCompletedReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Mortgage Completed Report]
- #region [Insurance Completed Report]
- /// <summary>
- /// Get insurance completed report by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetInsuranceCompletedReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[12];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCompletedReport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceCompletedReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Insurance Completed Report]
- #region [Pipeline Review Report]
- /// <summary>
- /// Get pipeline review report by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <returns></returns>
- public DataSet GetPipelineReviewReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetPipelineReviewReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Pipeline Review Report]
- #region [WOCC Report]
- /// <summary>
- /// Get WOCC report by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="FinType"></param>
- /// <returns></returns>
- public DataSet GetNewFinancialInformationReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int FinType = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@InsuranceType", type);
- }
- Param[7] = new SqlParameter("@FinanceType", FinType);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetWOCCReport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNewFinancialInformationReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [WOCC Report]
- #region [Total written Report]
- /// <summary>
- /// Get total written report by ids.
- /// </summary>
- /// <param name="type"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetTotalWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[10];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[7] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[8] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[9] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWritten_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetTotalWrittenReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Total written Report]
- #region [Insurance Referral report]
- /// <summary>
- /// Get insurance referral results by ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="ProductTypeId"></param>
- /// <returns></returns>
- public DataSet GetInsuranceReferralResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int ProductTypeId = 0)
- {
- List<Insurancereferral> negotiationlist = new List<Models.Insurancereferral>();
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@ProductTypeId", ProductTypeId);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetInsuranceReferralReport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceReferralResultByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Insurance Referral report]
- #region[Business report]
- /// <summary>
- /// Get business report by ids.
- /// </summary>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="type"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetBusinessReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBusinessReport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetBusinessReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion
- #region[Most Valuable referrer report]
- /// <summary>
- /// Get most valuable report by ids.
- /// </summary>
- /// <param name="type"></param>
- /// <param name="userid"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <param name="productTypeID"></param>
- /// <returns></returns>
- public DataSet GetMostValuableReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[2] = new SqlParameter("@Company", Company.Trim());
- Param[3] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[5] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[6] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[7] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[4] = new SqlParameter("@BrokerId", brokers);
- }
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMostValuableReferrersreport_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetBusinessReportByIds", ex.Message);
- }
- return ds;
- }
- #endregion
- #region [Negotiator Weekly Report]
- /// <summary>
- /// Getting negotiator weekly report
- /// </summary>
- /// <param name="fromdate">From date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorweeklyByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeekly_Update", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorweekly_Update");
- ds = new DataSet();
- }
- return ds;
- }
- #endregion [Negotiator Weekly Report]
- #region [Negotiator Result]
- /// <summary>
- /// Getting dataset from the database for Negotiator Report
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="pageNo">Page no</param>
- /// <param name="pageSize">Page size</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[13];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- Param[9] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[10] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[11] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[12] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReport_Update", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
- return ds = new DataSet();
- }
- }
- #endregion [Negotiator Result]
- #region [Negotiator Data Result]
- /// <summary>
- /// Get negotiator data result by Ids.
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <param name="BranchIds"></param>
- /// <param name="CompanyIds"></param>
- /// <param name="fromdate"></param>
- /// <param name="todate"></param>
- /// <param name="userid"></param>
- /// <param name="pageNo"></param>
- /// <param name="pageSize"></param>
- /// <param name="brokerId"></param>
- /// <param name="Negotiator"></param>
- /// <param name="brokers"></param>
- /// <param name="Company"></param>
- /// <param name="Branches"></param>
- /// <returns></returns>
- public ReportsDataModel GetNegotiatorDataResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
- {
- ReportsDataModel dataResult = new ReportsDataModel();
- dataResult.Negotiation = new List<Negotiation>();
- dataResult.NegotiationNotes = new List<NegotiationNotes>();
- SqlParameter[] Param = new SqlParameter[9];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
- SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "GetNegotiatorReport_Update", Param);
- ctx.Database.Initialize(force: false);
- ctx.Database.Connection.Open();
- dataResult.Negotiation = ((IObjectContextAdapter)ctx).ObjectContext.Translate<Negotiation>(reader).ToList();
- reader.NextResult();
- dataResult.NegotiationNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- ctx.Database.Connection.Close();
- if (!reader.IsClosed)
- {
- reader.Close();
- }
- return dataResult;
- }
- #endregion [Negotiator Data Result]
- #region [Notes Data]
- /// <summary>
- /// Getting the dataset for Notes Data
- /// </summary>
- /// <param name="userId">User Id</param>
- /// <param name="pageNo">Page No</param>
- /// <param name="pageSize">Page Size</param>
- /// <param name="fromDate">FRom Date</param>
- /// <param name="toDate">To Date</param>
- /// <param name="negotiators">Negotiators</param>
- /// <param name="company">Companies</param>
- /// <param name="branches">branches</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="productTypeID">Product type</param>
- /// <returns></returns>
- public ReadNotes GetNotesDataByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, int userId, int pageNo, int pageSize, DateTime fromDate, DateTime toDate, string negotiators = "", string company = "", string branches = "", string brokers = "", int productTypeID = 0)
- {
- var reportsDN = new ReadNotes();
- try
- {
- DataTable notes = new DataTable();
- SqlParameter[] Param = new SqlParameter[13];
- Param[0] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(negotiators) ? null : negotiators.Trim()));
- Param[1] = new SqlParameter("@Company", (string.IsNullOrEmpty(company) ? null : company.Trim()));
- Param[2] = new SqlParameter("@Branches", (string.IsNullOrEmpty(branches) ? null : branches.Trim()));
- Param[3] = new SqlParameter("@BrokerIds", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[4] = new SqlParameter("@FromDate", fromDate.ToString("dd/MMM/yyyy"));
- Param[5] = new SqlParameter("@ToDate", toDate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@CurrentUserId", userId);
- Param[7] = new SqlParameter("@PageNo", pageNo);
- Param[8] = new SqlParameter("@PageSize", pageSize);
- Param[9] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[10] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[11] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[12] = new SqlParameter("@CompanyIds", dtCompanyIds);
- SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "Usp_GetNotesData_Update", Param);
- ctx.Database.Initialize(force: false);
- ctx.Database.Connection.Open();
- reportsDN.AdvisorNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- reader.NextResult();
- reportsDN.AgentNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
- ctx.Database.Connection.Close();
- if (!reader.IsClosed)
- {
- reader.Close();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNotesDataByIds", ex.Message);
- }
- return reportsDN; ;
- }
- #endregion [Notes Data]
- #region [Negotiator Weekly Notes Created]
- //added on 2015-02-03
- /// <summary>
- /// Getting Weekly created notes data
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorWeeklyCreatedNotesByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteCreated_Update", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyCreatedNotesByIds", ex.Message);
- ds = new DataSet();
- return ds;
- }
- }
- #endregion [Negotiator Weekly Notes Created]
- #region [Negotiator Weekly Notes Updated]
- //added on 2015-02-03
- /// <summary>
- /// Getting weekly updated notes
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorWeeklyUpdatedNotesByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteUpdated_Update", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyUpdatedNotesByIds", ex.Message);
- ds = new DataSet();
- return ds;
- }
- }
- #endregion [Negotiator Weekly Notes Updated]
- #region [Negotiator Report]
- /// <summary>
- /// Getting negotiator data for PDF
- /// </summary>
- /// <param name="fromdate">From Date</param>
- /// <param name="todate">To Date</param>
- /// <param name="userid">User Id</param>
- /// <param name="brokerId">Broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Company</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type</param>
- /// <returns></returns>
- public DataSet GetNegotiatorByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
- Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
- Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- ds = SqlHelper.ExecuteDataset("GetNegotiatorReport_Update", Param);
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorByIds", ex.Message);
- }
- return ds;
- }
- #endregion [Negotiator Report]
- #region [Overall Conversion Report]
- /// <summary>
- /// Getting dataset for overall conversion report for Introducer Report
- /// </summary>
- /// <param name="totalLeads">Total Leads output parameter</param>
- /// <param name="totalApps">Total Apps Output parameter</param>
- /// <param name="fromdate">from date</param>
- /// <param name="todate">to date</param>
- /// <param name="userid">user Id</param>
- /// <param name="brokerId">broker Id</param>
- /// <param name="Negotiator">Negotiator</param>
- /// <param name="brokers">Brokers</param>
- /// <param name="Company">Companies</param>
- /// <param name="Branches">Branches</param>
- /// <param name="productTypeID">Product Type Id</param>
- public void GetOverAllConversionReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, out int totalLeads, out int totalApps, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds;
- totalLeads = 0;
- totalApps = 0;
- try
- {
- List<Negotiation> negotiationlist = new List<Models.Negotiation>();
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[1] = new SqlParameter("@Negotiator", "'" + Negotiator.TrimEnd(',') + "'");
- Param[2] = new SqlParameter("@Company", "'" + Company.TrimEnd(',') + "'");
- Param[3] = new SqlParameter("@Branches", "'" + Branches.TrimEnd(',') + "'");
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[4] = new SqlParameter("@BrokerId", brokers);
- }
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- Param[7] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetOverAllConversionForIntroducer_Update", Param);
- if (ds.Tables[0].Rows.Count > 0)
- {
- totalLeads = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalCount"].ToString());
- }
- if (ds.Tables[1].Rows.Count > 0)
- {
- totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"].ToString());
- }
- }
- catch (Exception ex)
- {
- ds = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetOverAllConversionReportByIds", ex.Message);
- }
- }
- ///// <summary>
- ///// Get introducer visit notes on the basis of filters.
- ///// </summary>
- ///// <returns></returns>
- //public List<IntroducerVisitNotes> GetIntroducerVisitNoteDetails(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
- //{
- // List<int> brokerids = new List<int>();
- // if (!string.IsNullOrEmpty(brokers))
- // {
- // brokerids = brokers.Split(',').Select(Int32.Parse).ToList();
- // }
- // var lstIntroducerNotes = new List<IntroducerVisitNotes>();
- // db_occfinance_5572Entities _context = new db_occfinance_5572Entities();
- // try
- // {
- // if (!string.IsNullOrEmpty(brokers) && companyIds!=null && companyIds.Count>0)
- // {
- // lstIntroducerNotes = (from intNotes in _context.tblIntroducerVisitNotes
- // join usr in _context.tblusers on intNotes.CreatedBy equals usr.userid
- // join acct in _context.tblaccounts on usr.account equals acct.accountid
- // where companyIds.Contains(acct.accountid)
- // && brokerids.Contains(usr.userid)
- // select new IntroducerVisitNotes
- // {
- // Id = intNotes.Id,
- // Account = intNotes.Account,
- // UserName = usr.brokername,
- // CreatedOn = intNotes.Created,
- // CreatedBy = intNotes.CreatedBy,
- // Note = intNotes.Note,
- // Introducer = acct.companyname
- // }).ToList();
- // }
- // else if(string.IsNullOrEmpty(brokers) && companyIds != null && companyIds.Count > 0)
- // {
- // lstIntroducerNotes = (from intNotes in _context.tblIntroducerVisitNotes
- // join usr in _context.tblusers on intNotes.CreatedBy equals usr.userid
- // join acct in _context.tblaccounts on usr.account equals acct.accountid
- // where companyIds.Contains(acct.accountid)
- // select new IntroducerVisitNotes
- // {
- // Id = intNotes.Id,
- // Account = intNotes.Account,
- // UserName = usr.brokername,
- // CreatedOn = intNotes.Created,
- // CreatedBy = intNotes.CreatedBy,
- // Note = intNotes.Note,
- // Introducer = acct.companyname
- // }).ToList();
- // }
- // if (lstIntroducerNotes != null && lstIntroducerNotes.Count > 0)
- // {
- // foreach (var intNote in lstIntroducerNotes)
- // {
- // intNote.Created = Convert.ToDateTime(intNote.CreatedOn).ToString("dd/MM/yyyy hh:mm");
- // }
- // }
- // }
- // catch (Exception ex)
- // {
- // Helper.ErrorLog(ex.InnerException, "Q_Reports.cs", "GetIntroducerVisitNoteDetails", ex.Message);
- // }
- // return lstIntroducerNotes;
- //}
- /// <summary>
- /// Get introducer appointment details.
- /// </summary>
- /// <returns></returns>
- public List<IntroducerVisitNotes> GetIntroducerVisitNoteDetails(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
- {
- List<IntroducerVisitNotes> introducerVisits = new List<IntroducerVisitNotes>();
- DataSet dsIntroducerVisits;
- try
- {
- SqlParameter[] Param = new SqlParameter[10];
- Param[0] = new SqlParameter("@CurrentUserId", null);
- Param[1] = new SqlParameter("@Negotiator", null);
- Param[2] = new SqlParameter("@Company", null);
- Param[3] = new SqlParameter("@Branches", null);
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
- Param[5] = new SqlParameter("@CompanyIds", dtCompanyIds);
- Param[6] = new SqlParameter("@BranchIds", null);
- Param[7] = new SqlParameter("@NegotiatorIds", null);
- Param[8] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[9] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsNoteDetails", Param);
- if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
- {
- introducerVisits = DataTableHelper.CreateListFromTableForAll<IntroducerVisitNotes>(dsIntroducerVisits.Tables[0]).ToList();
- foreach (var intNote in introducerVisits)
- {
- intNote.CreateOn = Convert.ToDateTime(intNote.CreatedOn).ToString("dd/MM/yyyy hh:mm");
- }
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports.cs", "GetIntroducerVisitNoteDetails", ex.Message);
- }
- return introducerVisits;
- }
- #endregion [Overall Conversion Report]
- #region [Conversion Rate For Insurance Referrals]
- public DataSet GetConversionRateForInsuranceReferrals(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
- {
- DataSet ds = new DataSet();
- try
- {
- SqlParameter[] Param = new SqlParameter[11];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- if (!string.IsNullOrEmpty(Negotiator))
- {
- Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
- Param[4] = new SqlParameter("@Company", Company.Trim());
- Param[5] = new SqlParameter("@Branches", Branches.Trim());
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
- Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
- DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
- Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
- }
- if (!string.IsNullOrWhiteSpace(brokers))
- {
- Param[6] = new SqlParameter("@BrokerId", brokers);
- }
- if (!string.IsNullOrWhiteSpace(type))
- {
- Param[7] = new SqlParameter("@MortgageType", type);
- }
- //Param[8] = new SqlParameter("@productTypeID", productTypeID);
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetConversionRateForInsuranceReferrals", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetConversionRateForInsuranceReferrals", ex.Message);
- }
- return ds;
- }
- #endregion [Conversion Rate For Insurance Referrals]
- #endregion [Updated Code of Reports Data Access Methods 2015-10-21]
- #region [Get Selected Companies With Broker 2015-10-21]
- /// <summary>
- /// Get selected companies with brokers by ids
- /// </summary>
- /// <param name="_allNamesparents"></param>
- /// <returns></returns>
- public string GetSelectedCompaniesWithBrokerByIds(string[] _allNamesparents)
- {
- var allNamesparents = string.Empty;
- var distinctList = _allNamesparents.Distinct().ToList();
- List<SelectListItem> _list = new List<SelectListItem>();
- for (int i = 0; i < distinctList.Count(); i++)
- {
- if (distinctList[i].Contains("®"))
- {
- string companyName;
- string branchName;
- GetCompanyAndBranchNameByIds(distinctList, i, out companyName, out branchName);
- _list.Add(new SelectListItem
- {
- Text = System.Text.RegularExpressions.Regex.Replace(companyName, "[^a-zA-Z0-9)]+", "", System.Text.RegularExpressions.RegexOptions.Compiled),
- Value = branchName
- });
- }
- }
- // System.Globalization.TextInfo myTI = new System.Globalization.CultureInfo("en-US", false).TextInfo;
- var _companieslist = (from r in _list
- select new
- {
- companyname = System.Text.RegularExpressions.Regex.Replace(r.Text, "[^a-zA-Z0-9)]+", "", System.Text.RegularExpressions.RegexOptions.Compiled).Trim()
- }).Distinct().ToList();
- for (int i = 0; i < _companieslist.Count(); i++)
- {
- var s = string.Join(", ", _list.Where(p => p.Text == _companieslist[i].companyname).Select(p => p.Value.ToString()));
- allNamesparents += string.Format(" <b> {0} </b>( {1} )", _companieslist.ElementAtOrDefault(i).companyname, s);
- }
- return allNamesparents;
- }
- /// <summary>
- /// Get company and branch names by id.
- /// </summary>
- /// <param name="companyAndBranchList"></param>
- /// <param name="index"></param>
- /// <param name="companyName"></param>
- /// <param name="branchName"></param>
- public void GetCompanyAndBranchNameByIds(List<string> companyAndBranchList, int index, out string companyName, out string branchName)
- {
- try
- {
- db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
- var companyId = Convert.ToInt32(companyAndBranchList[index].Split('®')[0]);
- var branchId = Convert.ToInt32(companyAndBranchList[index].Split('®')[1]);
- companyName = ctx.tblaccounts.Where(com => com.accountid == companyId).Select(com => com.companyname).FirstOrDefault();
- branchName = ctx.tblBranches.Where(br => br.Id == branchId).Select(br => br.Branch).FirstOrDefault();
- }
- catch (Exception ex)
- {
- companyName = string.Empty;
- branchName = string.Empty;
- Helper.ErrorLog(ex.InnerException, "ReportsController", "GetCompanyAndBranchNameByIds", ex.Message);
- }
- }
- /// <summary>
- /// Get negotiator name by ids
- /// </summary>
- /// <param name="NegotiatorIds"></param>
- /// <returns></returns>
- public string[] GetNegotiatorsNameByIds(List<int> NegotiatorIds)
- {
- string[] negotiatorNames = new string[NegotiatorIds.Count()];
- try
- {
- db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
- negotiatorNames = ctx.tblNegotiators.Where(neg => NegotiatorIds.Contains(neg.Id)).Select(neg => neg.Negotiator).ToList().ToArray();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "ReportsController", "GetNegotiatorName", ex.Message);
- }
- return negotiatorNames;
- }
- #endregion [Get Selected Companies With Broker 2015-10-21]
- #region [Introducer Visits]
- /// <summary>
- /// Get introducer appointment details.
- /// </summary>
- /// <returns></returns>
- public List<IntroducerVisitsModel> GetIntroducerVisitsForAppointment(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
- {
- List<IntroducerVisitsModel> introducerVisits = new List<IntroducerVisitsModel>();
- DataSet dsIntroducerVisits;
- try
- {
- SqlParameter[] Param = new SqlParameter[10];
- Param[0] = new SqlParameter("@CurrentUserId", null);
- Param[1] = new SqlParameter("@Negotiator", null);
- Param[2] = new SqlParameter("@Company", null);
- Param[3] = new SqlParameter("@Branches", null);
- Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
- DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
- Param[5] = new SqlParameter("@CompanyIds", dtCompanyIds);
- Param[6] = new SqlParameter("@BranchIds", null);
- Param[7] = new SqlParameter("@NegotiatorIds", null);
- Param[8] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
- Param[9] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
- dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsDetails", Param);
- }
- catch (Exception ex)
- {
- dsIntroducerVisits = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetIntroducerVisitsDetails", ex.Message);
- }
- if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
- introducerVisits = DataTableHelper.CreateListFromTableForAll<IntroducerVisitsModel>(dsIntroducerVisits.Tables[0]).ToList();
- return introducerVisits;
- }
- /// <summary>
- /// Get Introducer Visits for admin
- /// </summary>
- /// <param name="companyIds"></param>
- /// <param name="userid"></param>
- /// <param name="brokers"></param>
- /// <returns></returns>
- public List<IntroducerVisitsModel> GetIntroducerVisitsForAdmin(List<int> companyIds, int userid, string brokers, List<int> BranchIds, bool IsBroker)
- {
- List<IntroducerVisitsModel> introducerVisits = new List<IntroducerVisitsModel>();
- DataSet dsIntroducerVisits;
- try
- {
- SqlParameter[] Param = new SqlParameter[5];
- Param[0] = new SqlParameter("@CurrentUserId", userid);
- Param[1] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
- DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
- Param[2] = new SqlParameter("@CompanyIds", dtCompanyIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[3] = new SqlParameter("@BranchIds", dtBranchIds);
- Param[4] = new SqlParameter("@IsBroker", IsBroker);
- dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsDetailsForAdmin", Param);
- }
- catch (Exception ex)
- {
- dsIntroducerVisits = new DataSet();
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetIntroducerVisitsDetailsForAdmin", ex.Message);
- }
- if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
- {
- foreach(DataRow row in dsIntroducerVisits.Tables[0].Rows)
- {
- var model = new IntroducerVisitsModel();
- model.account = Convert.ToInt32(Convert.ToString(row["account"]));
- model.broker = Convert.ToInt32(Convert.ToString(row["broker"]));
- model.Introducer = Convert.ToString(row["Introducer"]);
- model.Branch = Convert.ToString(row["Branch"]);
- model.BranchId = Convert.ToInt32(Convert.ToString(row["BranchId"]));
- model.AdviserAssigned = Convert.ToString(row["AdviserAssigned"]);
- model.Id = Convert.ToInt32(Convert.ToString(row["Id"]));
- model.FrequencyType = Convert.ToInt32(Convert.ToString(row["FrequencyType"]));
- model.FrequencyDay = Convert.ToInt32(Convert.ToString(row["FrequencyDay"]));
- introducerVisits.Add(model);
- }
- }
- return introducerVisits;
- }
- public bool UpdateIntroducerAppointmentDetails(List<IntroducerVisitsModel> lstIntroducerAppointment)
- {
- db_occfinance_5572Entities _context = new db_occfinance_5572Entities();
- try
- {
- lstIntroducerAppointment.RemoveAt(0);//As this contain header of table
- lstIntroducerAppointment = lstIntroducerAppointment.GroupBy(item => new { BranchId = item.BranchId, Account = item.account }).SelectMany(g => g.OrderBy(grp => grp.AdviserAssigned)).ToList();
- int xxx = 0;
- int prevBranchId = 0;
- int prevAccountId = 0;
- foreach (var appointment in lstIntroducerAppointment)
- {
- DayOfWeek day = (DayOfWeek)appointment.FrequencyDay;
- int prev_daysToAdd = ((int)day - (int)DateTime.Now.DayOfWeek + 7) % 7;
- DateTime _VisitDate = DateTime.Now.AddDays(prev_daysToAdd);
- if (appointment.Id > 0)
- {
- var IsRecordExist = _context.tblIntroducerVisits.Where(x => x.Id == appointment.Id).FirstOrDefault();
- if (IsRecordExist != null)
- {
- try
- {
- if (appointment.AssignAdvisers != "checked")
- {
- IsRecordExist.IsActive = false;
- IsRecordExist.IsDeleted = true;
- IsRecordExist.Updated = DateTime.Now;
- _context.SaveChanges();
- }
- else
- {
- IsRecordExist.broker = appointment.broker;
- IsRecordExist.FrequencyDay = appointment.FrequencyDay;
- IsRecordExist.FrequencyType = appointment.FrequencyType;
- IsRecordExist.Updated = DateTime.Now;
- if (prevBranchId != appointment.BranchId && prevAccountId != appointment.account)
- {
- xxx = 0;
- prevBranchId = appointment.BranchId;
- prevAccountId = appointment.account;
- IsRecordExist.VisitDate = _VisitDate;
- }
- else
- {
- xxx = xxx + appointment.FrequencyType;
- IsRecordExist.VisitDate = _VisitDate.AddDays(xxx * 7);
- prevBranchId = appointment.BranchId;
- prevAccountId = appointment.account;
- }
- _context.SaveChanges();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails", ex.Message);
- }
- }
- }
- else
- {
- if (appointment.AssignAdvisers == "checked" && appointment.Id == 0)
- {
- try
- {
- tblIntroducerVisit objAppointment = new tblIntroducerVisit();
- objAppointment.account = appointment.account;
- objAppointment.broker = appointment.broker;
- objAppointment.FrequencyDay = appointment.FrequencyDay;
- objAppointment.FrequencyType = appointment.FrequencyType;
- objAppointment.BranchId = appointment.BranchId;
- objAppointment.Created = DateTime.Now;
- objAppointment.StatusId = 0;
- objAppointment.Updated = DateTime.Now;
- objAppointment.Created = DateTime.Now;
- objAppointment.UpdatedBy = SessionHelper.UserId;
- objAppointment.CreatedBy = SessionHelper.UserId;
- objAppointment.IsActive = true;
- if (prevBranchId != appointment.BranchId && prevAccountId != appointment.account)
- {
- xxx = 0;
- prevBranchId = appointment.BranchId;
- prevAccountId = appointment.account;
- objAppointment.VisitDate = _VisitDate;
- }
- else
- {
- xxx = xxx + appointment.FrequencyType;
- objAppointment.VisitDate = _VisitDate.AddDays(xxx * 7);
- prevBranchId = appointment.BranchId;
- prevAccountId = appointment.account;
- }
- _context.tblIntroducerVisits.Add(objAppointment);
- _context.SaveChanges();
- if (objAppointment.Id > 0)
- {
- tblIntroducerVisitsStatu objStatus = new tblIntroducerVisitsStatu();
- objStatus.IntroducerVisitsId = objAppointment.Id;
- objStatus.Status = (int)IntroducerVisitStatusEnum.Pending;
- objStatus.CreatedBy = SessionHelper.UserId;
- objStatus.CreatedDate = DateTime.Now;
- _context.tblIntroducerVisitsStatus.Add(objStatus);
- _context.SaveChanges();
- if (objStatus.Id > 0)
- {
- var _introducerVisit = _context.tblIntroducerVisits.Where(x => x.Id == objAppointment.Id).FirstOrDefault();
- _introducerVisit.StatusId = objStatus.Id;
- }
- string _connectionstring = Convert.ToString(ConfigurationManager.ConnectionStrings["ConString1"]);
- try
- {
- var _userId = SessionHelper.UserId;
- var _accountId = objAppointment.account;
- string note = "";
- string DateOfVisit = objAppointment.VisitDate.HasValue ? objAppointment.VisitDate.Value.ToString("dd/MMMM/yyyy") : string.Empty;
- note = "A meeting is scheduled with Introducer " + _context.tblaccounts.Where(res => res.accountid == objAppointment.account).Select(res => res.companyname).FirstOrDefault() + " in branch " + _context.tblBranches.Where(x => x.Id == appointment.BranchId).Select(x => x.Branch).FirstOrDefault().ToString() + " on " + DateOfVisit + ".";
- // note = "A meeting is scheduled with Introducer " + _context.tblaccounts.Where(res => res.accountid == objAppointment.account).Select(res => res.companyname).FirstOrDefault() + " in branch " + _context.tblBranches.Where(x => x.Id == appointment.BranchId).Select(x => x.Branch).FirstOrDefault().ToString() + " on " + _VisitDate.ToString("dd/MMMM/yyyy") + ".";
- tblIntroducerVisitNote objNote = new tblIntroducerVisitNote();
- objNote.Account = _accountId;
- objNote.Note = note;
- objNote.Created = DateTime.Now;
- objNote.CreatedBy = _userId;
- _context.tblIntroducerVisitNotes.Add(objNote);
- _context.SaveChanges();
- if (objNote.Id > 0)
- {
- objAppointment.NoteId = objNote.Id;
- objStatus.NoteId = objNote.Id;
- _context.SaveChanges();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails_AddNote", ex.Message);
- }
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails", ex.Message);
- }
- }
- }
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Reports", "UpdateIntroducerAppointmentDetails", ex.Message);
- }
- return true;
- }
- #endregion [Introducer Visits]
- /// <summary>
- /// Get data for London Market Screen Graph.
- /// </summary>
- /// <returns></returns>
- public DataSet GetLondonMarketScreenData()
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[0];
- ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_LondonMarketScreenData", Param);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_report", "sp_LondonMarketScreenData", ex.Message);
- ds = new DataSet();
- }
- return ds;
- }
- #region Account Visits Report
- public AccountVisitsReport GetAccountVisitsReport(List<int> companyIds, int userId, string brokers, List<int> BranchIds, DateTime FromDate, DateTime ToDate)
- {
- List<clsAccountVisits> accountVisits = new List<clsAccountVisits>();
- AccountVisitsReport _reportData = new AccountVisitsReport();
- DataSet dsIntroducerVisits;
- try
- {
- SqlParameter[] Param = new SqlParameter[6];
- Param[0] = new SqlParameter("@CurrentUserId", userId);
- Param[1] = new SqlParameter("@BrokerId", brokers);
- DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
- Param[2] = new SqlParameter("@CompanyIds", dtCompanyIds);
- DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
- Param[3] = new SqlParameter("@BranchIds", dtBranchIds);
- Param[4] = new SqlParameter("@FromDate", FromDate.ToString("dd/MMM/yyyy"));
- Param[5] = new SqlParameter("@ToDate", ToDate.ToString("dd/MMM/yyyy"));
- dsIntroducerVisits = SqlHelper.ExecuteDataset("GetAccountVisitsReport", Param);
- if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
- {
- foreach (DataRow dr in dsIntroducerVisits.Tables[0].Rows)
- {
- var av = new clsAccountVisits();
- av.Introducer = Convert.ToString(dr["CompanyName"]);
- av.Branch = Convert.ToString(dr["Branch"]);
- av.Adviser = Convert.ToString(dr["UserName"]);
- av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
- av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
- av.TransferToAdviser = Convert.ToString(dr["TransferToAdviser"]);
- av.TransferToId = Convert.ToInt32(Convert.ToString(dr["TransferToId"]));
- IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
- av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();
- av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();
- av.Status = Convert.ToString(statusEnum);
- av.Note = Convert.ToString(dr["Notes"]);
- accountVisits.Add(av);
- }
- accountVisits = accountVisits.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
- _reportData.TotalAccountVisitsReport = accountVisits;
- }
- //For Transferred
- if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[1].Rows.Count > 0)
- {
- List<clsAccountVisits> accountVisitsTransfered = new List<clsAccountVisits>();
- foreach (DataRow dr in dsIntroducerVisits.Tables[1].Rows)
- {
- var av = new clsAccountVisits();
- av.Introducer = Convert.ToString(dr["CompanyName"]);
- av.Branch = Convert.ToString(dr["Branch"]);
- av.Adviser = Convert.ToString(dr["UserName"]);
- av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
- av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
- av.TransferToAdviser = Convert.ToString(dr["TransferToAdviser"]);
- av.TransferToId = Convert.ToInt32(Convert.ToString(dr["TransferToId"]));
- IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
- av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();// Convert.ToString(vfEnum);
- av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();// Convert.ToString(dlEnum);
- av.Status = Convert.ToString(statusEnum);
- av.Note = Convert.ToString(dr["Notes"]);
- accountVisitsTransfered.Add(av);
- }
- accountVisitsTransfered = accountVisitsTransfered.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
- _reportData.TotalTransferVisitsReport = accountVisitsTransfered;
- }
- //For Rejected
- if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[2].Rows.Count > 0)
- {
- List<clsAccountVisits> accountVisitsRejected = new List<clsAccountVisits>();
- foreach (DataRow dr in dsIntroducerVisits.Tables[2].Rows)
- {
- var av = new clsAccountVisits();
- av.Introducer = Convert.ToString(dr["CompanyName"]);
- av.Branch = Convert.ToString(dr["Branch"]);
- av.Adviser = Convert.ToString(dr["UserName"]);
- av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
- av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
- av.RejectedOfAdviser = Convert.ToString(dr["RejectedOfAdviser"]);
- IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
- av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();// Convert.ToString(vfEnum);
- av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();// Convert.ToString(dlEnum);
- av.Status = Convert.ToString(statusEnum);
- av.Note = Convert.ToString(dr["Notes"]);
- accountVisitsRejected.Add(av);
- }
- accountVisitsRejected = accountVisitsRejected.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
- _reportData.TotalRejectedVisitsReport = accountVisitsRejected;
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAccountVisitsReport", ex.Message);
- }
- return _reportData;
- }
- #endregion Account Visits Report
- /// <summary>
- /// Where my leads come from
- /// </summary>
- /// <returns></returns>
- public DataSet GetWhereMyLeadscomeFromResult(int loggedinuser, DateTime fromdate, DateTime todate)
- {
- DataSet ds;
- try
- {
- SqlParameter[] Param = new SqlParameter[8];
- Param[0] = new SqlParameter("@CurrentUserId", loggedinuser);
- Param[1] = new SqlParameter("@Negotiator", null);
- Param[2] = new SqlParameter("@Company", null);
- Param[3] = new SqlParameter("@Branches", null);
- Param[4] = new SqlParameter("@BrokerId", null);
- Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
- Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
- Param[7] = new SqlParameter("@productTypeID", 2);
- ds = SqlHelper.ExecuteDataset("sp_WhereMyLeadComesFrom", Param);
- return ds;
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
- return ds = new DataSet();
- }
- }
- }
- }
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using Occfinance_Data;
- using Occfinance.Models;
- using Occfinance.Helpers;
- using System.Data;
- using System.Data.SqlClient;
- namespace Occfinance.Code
- {
- public class Q_NewLead
- {
- public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
- System.Web.HttpContext con = HttpContext.Current;
- #region Find contacts for account
- public List<tblcontact> FindContactsForAccount(int accountid)
- {
- var cntlists = ctx.tblcontacts.Where(c => c.account.HasValue && c.account.Value == accountid).Select(c => c).ToList();
- return cntlists;
- }
- #endregion
- #region Save lead information to database
- public bool SaveNewLead(tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, int mstatusid = 1, string Note = "", string mortage_date = "", string mortage_time = "", int subStatusId = 0)
- {
- bool result = false;
- try
- {
- #region [Add branch and negotiators 2015-11-02]
- data.BranchId = GetBranchId(data.branch);
- data.NegrefId = GetNegotiatorId(data.negref);
- data.branch = string.Empty;
- data.negref = string.Empty;
- #endregion [Add branch and negotiators 2015-11-02]
- #region [Add userId and password for factfind user in Contact]
- int xx = 0;
- while (xx < 2)
- {
- string userName = data.firstname+"-"+ Helper.GetRandomString(4,1);
- if (!string.IsNullOrEmpty(userName))
- {
- if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
- {
- data.ContactUserName = userName;
- data.ContactPassword= "OCCPWD-" + Helper.GetRandomString(6);
- #region send email to ContactUserName
- #endregion send email to ContactUserName
- break; //Exit loop
- }
- }
- }
- #endregion [Add userId and password for factfind user in Contact]
- ctx.tblcontacts.Add(data);
- ctx.SaveChanges();
- int cntid = _contactid = data.contactid;
- if (cntid > 0)
- {
- //Save Finance information to database
- tblfinance dataFinance = new tblfinance();
- dataFinance.contact = cntid;
- dataFinance.status = mstatusid;
- dataFinance.type = 1;
- dataFinance.subFinanceStatus = subStatusId;
- dataFinance.created = System.DateTime.Now;
- try
- {
- if (!string.IsNullOrWhiteSpace(mortage_date))
- dataFinance.followup = Helper.GetFollowUp(mortage_date, string.IsNullOrWhiteSpace(mortage_time) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage_time);
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
- }
- ctx.tblfinances.Add(dataFinance);
- ctx.SaveChanges();
- int financeid = dataFinance.financeid;
- if (financeid > 0)
- {
- //Save mortgage information to database
- tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
- dataFinanceMortgage.financeid = financeid;
- dataFinanceMortgage.purchase = dataM.purchase;
- dataFinanceMortgage.rterm = dataM.rterm;
- dataFinanceMortgage.deposit = dataM.deposit;
- dataFinanceMortgage.income = dataM.income;
- ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
- ctx.SaveChanges();
- //Add Note While get registered from New Lead page***********************
- try
- {
- if (!string.IsNullOrWhiteSpace(Note))
- {
- int loggedinuserid = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
- tblnote _note = new tblnote();
- Q_Application _qA = new Q_Application();
- _note.financeid = financeid;
- _note.contact = cntid;
- _note.note = Note;
- _note.created = System.DateTime.Now;
- _note.UserId = loggedinuserid;
- ctx.tblnotes.Add(_note);
- ctx.SaveChanges();
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
- }
- //********************************
- }
- if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
- {
- _objO.MainContactId = cntid;
- ctx.tblOtherApplicants.Add(_objO);
- ctx.SaveChanges();
- }
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int userid = SessionHelper.UserId;
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk= qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker??userid, userid, data.account ?? 0, cntid, SLA, data.method,out ClientRegistrationID);
- //insert Leaddata to factfind related table
- SaveLeadDataForFactFind(userid, financeid, cntid,Note);
- #endregion Insert to FactFind
- }
- result = true;
- return result;
- }
- catch (Exception ex)
- {
- _contactid = 0;
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
- return result;
- }
- }
- // Updated on 20-10-2014 for Individual Product TYpe Entry
- public bool SaveNewLeadForLife(tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, decimal amount = 0, decimal premium = 0, decimal commission = 0, string policyreference = "", int mstatusid = 1, int lstatusid = 10, string Note = "", string life_followupdate = "", string life_followuptime = "", bool IsPolicyForOtherApplicant = false)
- {
- bool result = false;
- try
- {
- #region [Add branch and negotiators 2015-11-02]
- data.BranchId = GetBranchId(data.branch);
- data.NegrefId = GetNegotiatorId(data.negref);
- data.branch = string.Empty;
- data.negref = string.Empty;
- #endregion [Add branch and negotiators 2015-11-02]
- #region [Add userId and password for factfind user in Contact]
- int xx = 0;
- while (xx < 2)
- {
- string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
- if (!string.IsNullOrEmpty(userName))
- {
- if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
- {
- data.ContactUserName = userName;
- data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
- #region send email to ContactUserName
- #endregion send email to ContactUserName
- break; //Exit loop
- }
- }
- }
- #endregion [Add userId and password for factfind user in Contact]
- ctx.tblcontacts.Add(data);
- ctx.SaveChanges();
- int cntid = _contactid = data.contactid;
- if (cntid > 0)
- {
- //Save Finance information to database Fopr Mortgage
- tblfinance dataFinanceM = new tblfinance();
- dataFinanceM.contact = cntid;
- dataFinanceM.status = mstatusid;
- dataFinanceM.type = 1;
- dataFinanceM.created = System.DateTime.Now;
- ctx.tblfinances.Add(dataFinanceM);
- ctx.SaveChanges();
- int financeid = dataFinanceM.financeid;
- if (financeid > 0)
- {
- //Save mortgage information to database
- tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
- dataFinanceMortgage.financeid = financeid;
- dataFinanceMortgage.purchase = dataM.purchase;
- dataFinanceMortgage.rterm = dataM.rterm;
- dataFinanceMortgage.deposit = dataM.deposit;
- dataFinanceMortgage.income = dataM.income;
- ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
- ctx.SaveChanges();
- }
- //Save Finance information to database For Life
- tblfinance dataFinance = new tblfinance();
- dataFinance.contact = cntid;
- dataFinance.status = lstatusid; // For Life New Lead................
- dataFinance.type = 2; // When finance type is Life
- //While Life ------------------
- dataFinance.amount = amount;
- dataFinance.commission = commission;
- dataFinance.premium = premium;
- dataFinance.policyreference = policyreference;
- //added on 04-04-2015
- dataFinance.isPolicyForOtherApplicant = IsPolicyForOtherApplicant;
- try
- {
- if (!string.IsNullOrWhiteSpace(life_followupdate))
- dataFinance.followup = Helper.GetFollowUp(life_followupdate, string.IsNullOrWhiteSpace(life_followuptime) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life_followuptime);
- }
- catch { }
- dataFinance.created = System.DateTime.Now;
- ctx.tblfinances.Add(dataFinance);
- ctx.SaveChanges();
- int financeidL = dataFinance.financeid;
- if (financeidL > 0)
- {
- //Save To Life table************************************************************
- tblfinance_insurance dataLife = new tblfinance_insurance();
- dataLife.financeid = financeidL;
- dataLife.smoker = false;
- ctx.tblfinance_insurance.Add(dataLife);
- ctx.SaveChanges();
- //******************************************************************************
- //Add Note While get registered from New Lead page***********************
- try
- {
- if (!string.IsNullOrWhiteSpace(Note))
- {
- int loggedinuserid = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
- tblnote _note = new tblnote();
- Q_Application _qA = new Q_Application();
- //int _noteid = _qA.getNoteID();
- //_note.noteid = _noteid;
- _note.financeid = financeidL;
- _note.contact = cntid;
- _note.note = Note;
- _note.created = System.DateTime.Now;
- _note.UserId = loggedinuserid;
- ctx.tblnotes.Add(_note);
- ctx.SaveChanges();
- }
- }
- catch { }
- //********************************
- }
- if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
- {
- _objO.MainContactId = cntid;
- ctx.tblOtherApplicants.Add(_objO);
- ctx.SaveChanges();
- }
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int userid = SessionHelper.UserId;
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userid, userid, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
- #endregion Insert to FactFind
- }
- result = true;
- return result;
- }
- catch (Exception ex)
- {
- _contactid = 0;
- return result;
- }
- }
- // Updated on 20-10-2014 for Individual Product TYpe Entry
- #endregion
- #region Save lead information to database For Mobile
- public bool Mobile_SaveNewLead(int userId, tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, int mstatusid = 1, string Note = "", string mortage_date = "", string mortage_time = "")
- {
- bool result = false;
- try
- {
- #region [Add branch and negotiators 2015-11-02]
- data.BranchId = GetBranchId(data.branch);
- data.NegrefId = GetNegotiatorId(data.negref);
- data.branch = string.Empty;
- data.negref = string.Empty;
- #endregion [Add branch and negotiators 2015-11-02]
- #region [Add userId and password for factfind user in Contact]
- int xx = 0;
- while (xx < 2)
- {
- string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
- if (!string.IsNullOrEmpty(userName))
- {
- if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
- {
- data.ContactUserName = userName;
- data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
- #region send email to ContactUserName
- #endregion send email to ContactUserName
- break; //Exit loop
- }
- }
- }
- #endregion [Add userId and password for factfind user in Contact]
- ctx.tblcontacts.Add(data);
- ctx.SaveChanges();
- int cntid = _contactid = data.contactid;
- if (cntid > 0)
- {
- //Save Finance information to database
- tblfinance dataFinance = new tblfinance();
- dataFinance.contact = cntid;
- dataFinance.status = mstatusid;
- dataFinance.type = 1;
- dataFinance.created = System.DateTime.Now;
- try
- {
- if (!string.IsNullOrWhiteSpace(mortage_date))
- dataFinance.followup = Helper.GetFollowUp(mortage_date, string.IsNullOrWhiteSpace(mortage_time) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage_time);
- }
- catch { }
- ctx.tblfinances.Add(dataFinance);
- ctx.SaveChanges();
- int financeid = dataFinance.financeid;
- if (financeid > 0)
- {
- //Save mortgage information to database
- tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
- dataFinanceMortgage.financeid = financeid;
- dataFinanceMortgage.purchase = dataM.purchase;
- dataFinanceMortgage.rterm = dataM.rterm;
- dataFinanceMortgage.deposit = dataM.deposit;
- dataFinanceMortgage.income = dataM.income;
- ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
- ctx.SaveChanges();
- //Add Note While get registered from New Lead page***********************
- try
- {
- if (!string.IsNullOrWhiteSpace(Note))
- {
- int loggedinuserid = userId;
- tblnote _note = new tblnote();
- Q_Application _qA = new Q_Application();
- //int _noteid = _qA.getNoteID();
- //_note.noteid = _noteid;
- _note.financeid = financeid;
- _note.contact = cntid;
- _note.note = Note;
- _note.created = System.DateTime.Now;
- _note.UserId = loggedinuserid;
- ctx.tblnotes.Add(_note);
- ctx.SaveChanges();
- }
- }
- catch { }
- //********************************
- }
- if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
- {
- _objO.MainContactId = cntid;
- ctx.tblOtherApplicants.Add(_objO);
- ctx.SaveChanges();
- }
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
- //insert Leaddata to factfind related table
- SaveLeadDataForFactFind(userId, financeid, cntid,Note);
- #endregion Insert to FactFind
- }
- result = true;
- return result;
- }
- catch (Exception ex)
- {
- _contactid = 0;
- return result;
- }
- }
- public bool Mobile_SaveNewLeadForLife(int userId, tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, decimal amount = 0, decimal premium = 0, decimal commission = 0, string policyreference = "", int mstatusid = 1, int lstatusid = 10, string Note = "", string life_followupdate = "", string life_followuptime = "", bool IsPolicyForOtherApplicant = false)
- {
- bool result = false;
- try
- {
- #region [Add branch and negotiators 2015-11-02]
- data.BranchId = GetBranchId(data.branch);
- data.NegrefId = GetNegotiatorId(data.negref);
- data.branch = string.Empty;
- data.negref = string.Empty;
- #endregion [Add branch and negotiators 2015-11-02]
- #region [Add userId and password for factfind user in Contact]
- int xx = 0;
- while (xx < 2)
- {
- string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
- if (!string.IsNullOrEmpty(userName))
- {
- if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
- {
- data.ContactUserName = userName;
- data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
- #region send email to ContactUserName
- #endregion send email to ContactUserName
- break; //Exit loop
- }
- }
- }
- #endregion [Add userId and password for factfind user in Contact]
- ctx.tblcontacts.Add(data);
- ctx.SaveChanges();
- int cntid = _contactid = data.contactid;
- if (cntid > 0)
- {
- //Save Finance information to database Fopr Mortgage
- tblfinance dataFinanceM = new tblfinance();
- dataFinanceM.contact = cntid;
- dataFinanceM.status = mstatusid;
- dataFinanceM.type = 1;
- dataFinanceM.created = System.DateTime.Now;
- ctx.tblfinances.Add(dataFinanceM);
- ctx.SaveChanges();
- int financeid = dataFinanceM.financeid;
- if (financeid > 0)
- {
- //Save mortgage information to database
- tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
- dataFinanceMortgage.financeid = financeid;
- dataFinanceMortgage.purchase = dataM.purchase;
- dataFinanceMortgage.rterm = dataM.rterm;
- dataFinanceMortgage.deposit = dataM.deposit;
- dataFinanceMortgage.income = dataM.income;
- ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
- ctx.SaveChanges();
- }
- //Save Finance information to database For Life
- tblfinance dataFinance = new tblfinance();
- dataFinance.contact = cntid;
- dataFinance.status = lstatusid; // For Life New Lead................
- dataFinance.type = 2; // When finance type is Life
- //While Life ------------------
- dataFinance.amount = amount;
- dataFinance.commission = commission;
- dataFinance.premium = premium;
- dataFinance.policyreference = policyreference;
- //added on 04-04-2015
- dataFinance.isPolicyForOtherApplicant = IsPolicyForOtherApplicant;
- try
- {
- if (!string.IsNullOrWhiteSpace(life_followupdate))
- dataFinance.followup = Helper.GetFollowUp(life_followupdate, string.IsNullOrWhiteSpace(life_followuptime) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life_followuptime);
- }
- catch { }
- dataFinance.created = System.DateTime.Now;
- ctx.tblfinances.Add(dataFinance);
- ctx.SaveChanges();
- int financeidL = dataFinance.financeid;
- if (financeidL > 0)
- {
- //Save To Life table************************************************************
- tblfinance_insurance dataLife = new tblfinance_insurance();
- dataLife.financeid = financeidL;
- dataLife.smoker = false;
- ctx.tblfinance_insurance.Add(dataLife);
- ctx.SaveChanges();
- //******************************************************************************
- //Add Note While get registered from New Lead page***********************
- try
- {
- if (!string.IsNullOrWhiteSpace(Note))
- {
- int loggedinuserid = userId;
- tblnote _note = new tblnote();
- Q_Application _qA = new Q_Application();
- //int _noteid = _qA.getNoteID();
- //_note.noteid = _noteid;
- _note.financeid = financeidL;
- _note.contact = cntid;
- _note.note = Note;
- _note.created = System.DateTime.Now;
- _note.UserId = loggedinuserid;
- ctx.tblnotes.Add(_note);
- ctx.SaveChanges();
- }
- }
- catch { }
- //********************************
- }
- if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
- {
- _objO.MainContactId = cntid;
- ctx.tblOtherApplicants.Add(_objO);
- ctx.SaveChanges();
- }
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
- #endregion Insert to FactFind
- }
- result = true;
- return result;
- }
- catch (Exception ex)
- {
- _contactid = 0;
- return result;
- }
- }
- #endregion
- #region [Add/Fetch Branch and Negotiator 2015-11-02]
- /// <summary>
- /// Get negotiator id on the basis of negotiator name. If not already exist then add a new negotiator.
- /// </summary>
- /// <param name="negotiatorName"></param>
- /// <returns></returns>
- public int GetNegotiatorId(string negotiatorName)
- {
- if (string.IsNullOrEmpty(negotiatorName))
- return 0;
- var negotiatorId = 0;
- try
- {
- negotiatorId = ctx.tblNegotiators.Where(negt => negt.Negotiator == negotiatorName).Select(negt => negt.Id).FirstOrDefault();
- if (negotiatorId == 0)
- {
- tblNegotiators negt = new tblNegotiators();
- negt.Negotiator = negotiatorName;
- ctx.tblNegotiators.Add(negt);
- ctx.SaveChanges();
- negotiatorId = negt.Id;
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetNegotiatorId", ex.Message);
- }
- return negotiatorId;
- }
- /// <summary>
- /// Get branch id on the basis of branch name. add new branch if it does not already exist.
- /// </summary>
- /// <param name="branchName"></param>
- /// <returns></returns>
- public int GetBranchId(string branchName)
- {
- if (string.IsNullOrEmpty(branchName))
- return 0;
- var branchId = 0;
- try
- {
- branchId = ctx.tblBranches.Where(brn => brn.Branch == branchName).Select(brn => brn.Id).FirstOrDefault();
- if (branchId == null || branchId == 0)
- {
- tblBranches objBranch = new tblBranches();
- objBranch.Branch = branchName;
- ctx.tblBranches.Add(objBranch);
- ctx.SaveChanges();
- branchId = objBranch.Id;
- }
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchId", ex.Message);
- }
- return branchId;
- }
- /// <summary>
- /// Get branch names on the basis of branch ids.
- /// </summary>
- /// <param name="contactlists"></param>
- /// <returns></returns>
- public List<string> GetBranchNamesFromBranchIds(List<tblcontact> contactlists)
- {
- List<string> branchLists = new List<string>();
- var branchIds = contactlists.Select(brn => brn.BranchId);
- try
- {
- branchLists = ctx.tblBranches.Where(brn => branchIds.Contains(brn.Id)).Select(brn => brn.Branch).Distinct().ToList();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchNamesFromBranchIds", ex.Message);
- }
- return branchLists;
- }
- /// <summary>
- /// Get negotiator names on the basis of negotiator ids.
- /// </summary>
- /// <param name="contactlists"></param>
- /// <returns></returns>
- public List<string> GetNegotiatorNamesFromNegotiatorIds(List<tblcontact> contactlists)
- {
- List<string> negotitorLists = new List<string>();
- var negotiatorIds = contactlists.Select(brn => brn.NegrefId);
- try
- {
- negotitorLists = ctx.tblNegotiators.Where(negt => negotiatorIds.Contains(negt.Id)).Select(negt => negt.Negotiator).Distinct().ToList();
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchNamesFromBranchIds", ex.Message);
- }
- return negotitorLists;
- }
- public DataSet GetBranchAndNegotiatorByAccountId(int accountId)
- {
- SqlParameter[] Param = new SqlParameter[1];
- Param[0] = new SqlParameter("@accountId", accountId);
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBranchAndNegotiatorByAccountId", Param);
- return ds;
- }
- #endregion [Add/Fetch Branch and Negotiator 2015-11-02]
- #region Insert Lead data to FactFind
- public void SaveLeadDataForFactFind(int userId,int financeId,int contactId,string note="")
- {
- try
- {
- SqlParameter[] Param = new SqlParameter[4];
- Param[0] = new SqlParameter("@userId", userId);
- Param[1] = new SqlParameter("@contactId", contactId);
- Param[2] = new SqlParameter("@financeId", financeId);
- Param[3] = new SqlParameter("@note", note);
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SaveNewLeadDataForFactFind", Param);
- }
- catch(Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveLeadDataForFactFind", ex.Message);
- }
- }
- #endregion
- #region Send/Generate factfind
- public List<string> GenerateFactFindLink(int contactId, int userId)
- {
- List<string> lst = new List<string>();
- try
- {
- var data = ctx.tblcontacts.Where(x => x.contactid == contactId).FirstOrDefault();
- var objFinance = ctx.tblfinances.Where(x => x.contact == contactId).OrderBy(x => x.financeid).First();
- #region [Add userId and password for factfind user in Contact]
- int xx = 0;
- while (xx < 2)
- {
- string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
- if (!string.IsNullOrEmpty(userName))
- {
- if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
- {
- data.ContactUserName = userName;
- data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
- lst.Add(userName);
- lst.Add(data.ContactPassword);
- ctx.SaveChanges();
- #region send email to ContactUserName
- #endregion send email to ContactUserName
- xx = 3;
- break; //Exit loop
- }
- }
- }
- #endregion [Add userId and password for factfind user in Contact]
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, contactId, SLA, data.method, out ClientRegistrationID);
- //insert Leaddata to factfind related table
- SaveLeadDataForFactFind(userId, objFinance.financeid, data.contactid, "");
- #endregion Insert to FactFind
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GenerateFactFindLink", ex.Message);
- }
- return lst;
- }
- public int SendFactfindLink(int contactId, int userId)
- {
- int result = 0;
- var data = ctx.tblcontacts.Where(x => x.contactid == contactId).FirstOrDefault();
- var objFinance = ctx.tblfinances.Where(x => x.contact == contactId).OrderBy(x => x.financeid).First();
- try
- {
- #region Insert to FactFind
- string SLA = string.Empty;
- if (data.slamet == true)
- SLA = "1";
- else
- SLA = "0";
- int ClientRegistrationID = 0;
- Q_Client qClient = new Q_Client();
- int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, contactId, SLA, data.method, out ClientRegistrationID);
- //insert Leaddata to factfind related table
- SaveLeadDataForFactFind(userId, objFinance.financeid, data.contactid, "");
- result = 1;
- #endregion Insert to FactFind
- }
- catch (Exception ex)
- {
- Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SendFactfindLink", ex.Message);
- result = -1;
- }
- return result;
- }
- #endregion
- }
- }
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Data;
- using System.Data.SqlClient;
- using Occfinance.Models;
- using Occfinance_Data;
- using System.Reflection;
- using Occfinance.Helpers;
- /*****************************************************
- Operations for MortgageFactFind
- ******************************************************/
- namespace Occfinance.Code
- {
- public class Q_MortgageFactFind
- {
- SqlConnection connection;
- public Q_MortgageFactFind()
- { connection = new SqlConnection(SqlHelper.ConnectionString()); }
- // Add Client Registration
- public int AddClientRegistration(tblClientRegistration clientRegistration, bool useTransaction, SqlTransaction tran, SqlConnection connect)
- {
- int completed = 0;
- string proc = (clientRegistration.ClientRegistrationID == 0 ? "tblInsertClientRegister" : "tblUpdateClientRegister");
- SqlParameter[] clientRegister = new SqlParameter[44];
- clientRegister[0] = new SqlParameter("@ClientName", clientRegistration.ClientName);
- clientRegister[1] = new SqlParameter("@AdviserName", clientRegistration.AdviserName);
- clientRegister[2] = new SqlParameter("@DateCompleted", !string.IsNullOrWhiteSpace(clientRegistration.StrDateCompleted) ? Helper.GetDate(clientRegistration.StrDateCompleted) : null);
- clientRegister[3] = new SqlParameter("@ReferId", clientRegistration.ReferId);
- clientRegister[4] = new SqlParameter("@method", clientRegistration.method);
- clientRegister[5] = new SqlParameter("@slamet", clientRegistration.slamet);
- clientRegister[6] = new SqlParameter("@Title", clientRegistration.Title);
- clientRegister[7] = new SqlParameter("@Forename", clientRegistration.Forename);
- clientRegister[8] = new SqlParameter("@Surname", clientRegistration.Surname);
- clientRegister[9] = new SqlParameter("@PreviousSurname", clientRegistration.PreviousSurname);
- clientRegister[10] = new SqlParameter("@Gender", clientRegistration.Gender);
- clientRegister[11] = new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(clientRegistration.StrDOB) ? Helper.GetDate(clientRegistration.StrDOB) : null);
- clientRegister[12] = new SqlParameter("@PassportHeld", clientRegistration.PassportHeld);
- clientRegister[13] = new SqlParameter("@VisaStatus", clientRegistration.VisaStatus);
- clientRegister[14] = new SqlParameter("@MaritalStatus", clientRegistration.MaritalStatus);
- clientRegister[15] = new SqlParameter("@DoYouSmoke", clientRegistration.DoYouSmoke);
- clientRegister[16] = new SqlParameter("@InGoodHealth", clientRegistration.InGoodHealth);
- clientRegister[17] = new SqlParameter("@Dependants", clientRegistration.Dependants);
- clientRegister[18] = new SqlParameter("@FullHomeAddress", clientRegistration.FullHomeAddress);
- clientRegister[19] = new SqlParameter("@DateMovedToAddress", !string.IsNullOrWhiteSpace(clientRegistration.StrDateMovedToAddress) ? Helper.GetDate(clientRegistration.StrDateMovedToAddress) : null);
- clientRegister[20] = new SqlParameter("@PreviousAddress", clientRegistration.PreviousAddress);
- clientRegister[21] = new SqlParameter("@DateMovedToThisAddress", !string.IsNullOrWhiteSpace(clientRegistration.StrDateMovedToThisAddress) ? Helper.GetDate(clientRegistration.StrDateMovedToThisAddress) : null);
- clientRegister[22] = new SqlParameter("@ResidentialStatus", clientRegistration.ResidentialStatus);
- clientRegister[23] = new SqlParameter("@HomeNumber", clientRegistration.HomeNumber);
- clientRegister[24] = new SqlParameter("@MobileNumber", clientRegistration.MobileNumber);
- clientRegister[25] = new SqlParameter("@WorkNumber", clientRegistration.WorkNumber);
- clientRegister[26] = new SqlParameter("@EmailAddress", clientRegistration.EmailAddress);
- clientRegister[27] = new SqlParameter("@AdditionalInfo", clientRegistration.AdditionalInfo);
- clientRegister[28] = new SqlParameter("@AreYouEmployed", clientRegistration.AreYouEmployed);
- clientRegister[29] = new SqlParameter("@IsActive", clientRegistration.IsActive);
- clientRegister[30] = new SqlParameter("@IsDeleted", clientRegistration.IsDeleted);
- //if (clientRegistration.ClientRegistrationID == 0)
- clientRegister[31] = new SqlParameter("@AddDate", DateTime.Now);
- clientRegister[32] = new SqlParameter("@EntryBy", clientRegistration.EntryBy);
- //if (clientRegistration.ClientRegistrationID !=0)
- clientRegister[33] = new SqlParameter("@UpdateDate", DateTime.Now);
- clientRegister[34] = new SqlParameter("@UpdateBy", clientRegistration.UpdateBy);
- clientRegister[35] = new SqlParameter("@result", SqlDbType.Int);
- clientRegister[35].Direction = ParameterDirection.Output;
- clientRegister[36] = new SqlParameter("@ClientRegistrationID", clientRegistration.ClientRegistrationID);
- clientRegister[37] = new SqlParameter("@PassportHeldOther", clientRegistration.PassportHeldOther);
- clientRegister[38] = new SqlParameter("@NINumber", clientRegistration.NINumber);
- clientRegister[39] = new SqlParameter("@AnticipatedRetirementAge", clientRegistration.AnticipatedRetirementAge);
- clientRegister[40] = new SqlParameter("@Retired", clientRegistration.Retired ?? false);
- clientRegister[41] = new SqlParameter("@HasReference", clientRegistration.HasReference);
- clientRegister[42] = new SqlParameter("@ValidityDate", !string.IsNullOrWhiteSpace(clientRegistration.StrValidityDate) ? Helper.GetDate(clientRegistration.StrValidityDate) : null);
- clientRegister[43] = new SqlParameter("@PassportType", clientRegistration.PassportType);
- if (useTransaction)
- SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, connect, clientRegister);
- else
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, clientRegister);
- // return 1 if executed successfully
- completed = Convert.ToInt32(clientRegister[35] != null ? clientRegister[35].SqlValue.ToString() : "0");
- return completed;
- }
- // Created on 23/01/2015
- //Add and Update address in client registration
- public int AddAddressClientRegistration(AddressInfo address, bool useTransaction, SqlTransaction tran, SqlConnection connect)
- {
- int completed = 0;
- SqlParameter[] clientreg_address = new SqlParameter[]
- {
- new SqlParameter("@FactFindAddressID", address.FactFindAddressID),
- new SqlParameter("@AddressLine1", address.AddressLine1),
- new SqlParameter("@AddressLine2", address.AddressLine2),
- new SqlParameter("@AddressLine3", address.AddressLine3),
- new SqlParameter("@AddressLine4", address.AddressLine4),
- new SqlParameter("@PostCode", address.PostCode),
- new SqlParameter("@County", address.County),
- new SqlParameter("@Country", address.Country),
- new SqlParameter("@NameOfEmployer", address.NameOfEmployer),
- new SqlParameter("@EmployerAddress", address.EmployerAddress),
- new SqlParameter("@Year", address.Year),
- new SqlParameter("@ResidentialStatus", address.ResidentialStatus),
- new SqlParameter("@HomeNumber", address.HomeNumber),
- new SqlParameter("@WorkNumber", address.WorkNumber),
- new SqlParameter("@DateMovedToAddress",!string.IsNullOrWhiteSpace(address.StrDateMovedToAddress) ? Helper.GetDate(address.StrDateMovedToAddress) : null),
- new SqlParameter("@IsDeleted", false),
- new SqlParameter("@ApplicantID",address.ApplicantID),
- new SqlParameter("@result", SqlDbType.Int){Direction=ParameterDirection.Output}
- };
- if (useTransaction)
- SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "FactFind_AddUpdateAddress", tran, connect, clientreg_address);
- else
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_AddUpdateAddress", clientreg_address);
- // return 1 if executed successfully
- completed = Convert.ToInt32(clientreg_address[17] != null ? clientreg_address[17].SqlValue.ToString() : "0");
- return completed;
- }
- public DataSet GetAddressList(int applicantid)
- {
- SqlParameter[] Param = new SqlParameter[1];
- Param[0] = new SqlParameter("@ApplicantID", applicantid);
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetAddressByApplicantID", Param);
- return ds;
- }
- // get dependents
- public DataSet GetDependentList(int applicantid)
- {
- SqlParameter[] Param = new SqlParameter[1];
- Param[0] = new SqlParameter("@ApplicantID", applicantid);
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetDependentsByApplicantID", Param);
- return ds;
- }
- // Add Employement : Generic method for add and update.
- // Add Date and Entry by parameters will be ignord while update
- public int AddEmployement(tblEmployment employement, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (employement.EmploymentId == 0 ? "tblInsertEmployment" : "tblUpdateEmployment");
- SqlParameter[] emp = new SqlParameter[13];
- emp[0] = new SqlParameter("@EmploymentId", employement.EmploymentId);
- emp[1] = new SqlParameter("@Year", employement.Year);
- emp[2] = new SqlParameter("@NameOfEmployer", employement.NameOfEmployer);
- emp[3] = new SqlParameter("@EmployerAddress", employement.EmployerAddress);
- emp[4] = new SqlParameter("@IsActive", employement.IsActive);
- emp[5] = new SqlParameter("@IsDeleted", employement.IsDeleted);
- emp[6] = new SqlParameter("@AddDate", DateTime.Now);
- emp[7] = new SqlParameter("@EntryBy", employement.EntryBy);
- emp[8] = new SqlParameter("@UpdateDate", DateTime.Now);
- emp[9] = new SqlParameter("@UpdateBy", employement.UpdateBy);
- emp[10] = new SqlParameter("@result", SqlDbType.Int);
- emp[10].Direction = ParameterDirection.Output;
- // new parameters
- emp[11] = new SqlParameter("@ClientRegistrationID", employement.ClientRegistrationID);
- emp[12] = new SqlParameter("@ReferId", employement.ReferId);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, emp); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, emp); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(emp[10] != null ? emp[10].SqlValue.ToString() : "0");
- return completed;
- }
- // Add EmploymentAndIncome
- public int AddEmploymentAndIncome(tblEmploymentAndIncome employmentAndIncome, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (employmentAndIncome.EmploymentIncomeId == 0 ? "tblInsertEmploymentAndIncome" : "tblUpdateEmploymentAndIncome");
- SqlParameter[] empandInc = new SqlParameter[39];
- empandInc[0] = new SqlParameter("@ClientRegistrationID", employmentAndIncome.ClientRegistrationID);
- empandInc[1] = new SqlParameter("@JobTitle", employmentAndIncome.JobTitle);
- empandInc[2] = new SqlParameter("@NatureOfOccupation", employmentAndIncome.NatureOfOccupation);
- empandInc[3] = new SqlParameter("@NINumber", employmentAndIncome.NINumber);
- empandInc[4] = new SqlParameter("@AnticipatedRetirementAge", employmentAndIncome.AnticipatedRetirementAge);
- empandInc[5] = new SqlParameter("@EmploymentStatus", employmentAndIncome.EmploymentStatus);
- empandInc[6] = new SqlParameter("@ProbationPeriod", employmentAndIncome.ProbationPeriod);
- empandInc[7] = new SqlParameter("@NameOfEmployer", employmentAndIncome.NameOfEmployer);
- empandInc[8] = new SqlParameter("@AddressOfEmployer", employmentAndIncome.AddressOfEmployer);
- empandInc[9] = new SqlParameter("@AnnualCommission", employmentAndIncome.AnnualCommission);
- empandInc[10] = new SqlParameter("@AnnualOvertime", employmentAndIncome.AnnualOvertime);
- empandInc[11] = new SqlParameter("@Other", employmentAndIncome.Other);
- empandInc[12] = new SqlParameter("@GrossTotal", employmentAndIncome.GrossTotal);
- empandInc[13] = new SqlParameter("@GrossMonthly", employmentAndIncome.GrossMonthly);
- empandInc[14] = new SqlParameter("@IsActive", employmentAndIncome.IsActive);
- empandInc[15] = new SqlParameter("@IsDeleted", employmentAndIncome.IsDeleted);
- empandInc[16] = new SqlParameter("@AddDate", DateTime.Now);
- empandInc[17] = new SqlParameter("@EntryBy", employmentAndIncome.EntryBy);
- empandInc[18] = new SqlParameter("@UpdateDate", DateTime.Now);
- empandInc[19] = new SqlParameter("@UpdateBy", employmentAndIncome.UpdateBy);
- empandInc[20] = new SqlParameter("@GrossBasicSalary", employmentAndIncome.GrossBasicSalary);
- empandInc[21] = new SqlParameter("@PeriodWithEmployer", employmentAndIncome.PeriodWithEmployer);
- empandInc[22] = new SqlParameter("@result", SqlDbType.Int);
- empandInc[22].Direction = ParameterDirection.Output;
- // add id column
- empandInc[23] = new SqlParameter("@EmploymentIncomeId", employmentAndIncome.EmploymentIncomeId);
- empandInc[24] = new SqlParameter("@ReferId", employmentAndIncome.ReferId);
- // bonus
- empandInc[25] = new SqlParameter("@BonusY1", employmentAndIncome.BonusY1);
- empandInc[26] = new SqlParameter("@BonusY2", employmentAndIncome.BonusY2);
- empandInc[27] = new SqlParameter("@BonusY3", employmentAndIncome.BonusY3);
- empandInc[28] = new SqlParameter("@PreviousAddressOfEmployer", employmentAndIncome.PreviousAddressOfEmployer);
- empandInc[29] = new SqlParameter("@AreYouEmployed", employmentAndIncome.AreYouEmployed);
- empandInc[30] = new SqlParameter("@EmploymentStartDate", !string.IsNullOrWhiteSpace(employmentAndIncome.StrEmploymentStartDate) ? Helper.GetDate(employmentAndIncome.StrEmploymentStartDate) : null);
- empandInc[31] = new SqlParameter("@EmploymentEndDate", !string.IsNullOrWhiteSpace(employmentAndIncome.StrEmploymentEndDate) ? Helper.GetDate(employmentAndIncome.StrEmploymentEndDate) : null);
- empandInc[32] = new SqlParameter("@PostcodeofEmployer", employmentAndIncome.PostcodeofEmployer);
- empandInc[33] = new SqlParameter("@CountryofEmployer", employmentAndIncome.CountryofEmployer);
- empandInc[34] = new SqlParameter("@CountyofEmployer", employmentAndIncome.CountyofEmployer);
- empandInc[35] = new SqlParameter("@OtherNatureofOccupation", employmentAndIncome.OtherNatureofOccupation);
- empandInc[36] = new SqlParameter("@OtherEmploymentstatus", employmentAndIncome.OtherEmploymentstatus);
- empandInc[37] = new SqlParameter("@CurrentJob", employmentAndIncome.CurrentJob);
- empandInc[38] = new SqlParameter("@ProbationPeriodDetails", employmentAndIncome.ProbationPeriodDetails);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empandInc); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empandInc); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(empandInc[22] != null ? empandInc[22].SqlValue.ToString() : "0");
- return completed;
- }
- // Add PayslipDeduction
- public int AddPayslipDeduction(tblPayslipDeduction payslipDeduction, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (payslipDeduction.PayslipDeductionId == 0 ? "tblInsertPayslipDeduction" : "tblUpdatetPayslipDeduction");
- SqlParameter[] payslip = new SqlParameter[21];
- payslip[0] = new SqlParameter("@ClientRegistrationID", payslipDeduction.ClientRegistrationID);
- payslip[1] = new SqlParameter("@PensionContributions", payslipDeduction.PensionContributions);
- payslip[2] = new SqlParameter("@EmployerShareSaveSchemes", payslipDeduction.EmployerShareSaveSchemes);
- payslip[3] = new SqlParameter("@ChildcareVouchers", payslipDeduction.ChildcareVouchers);
- payslip[4] = new SqlParameter("@GymMembership", payslipDeduction.GymMembership);
- payslip[5] = new SqlParameter("@OtherBenefits", payslipDeduction.OtherBenefits);
- payslip[6] = new SqlParameter("@TransportBikeLoans", payslipDeduction.TransportBikeLoans);
- payslip[7] = new SqlParameter("@StudentLoanPayment", payslipDeduction.StudentLoanPayment);
- payslip[8] = new SqlParameter("@TotalNetMonthly", payslipDeduction.TotalNetMonthly);
- payslip[9] = new SqlParameter("@IsActive", payslipDeduction.IsActive);
- payslip[10] = new SqlParameter("@IsDeleted", payslipDeduction.IsDeleted);
- payslip[11] = new SqlParameter("@AddDate", DateTime.Now);
- payslip[12] = new SqlParameter("@EntryBy", payslipDeduction.EntryBy);
- payslip[13] = new SqlParameter("@UpdateDate", DateTime.Now);
- payslip[14] = new SqlParameter("@UpdateBy", payslipDeduction.UpdateBy);
- payslip[15] = new SqlParameter("@result", SqlDbType.Int);
- payslip[15].Direction = ParameterDirection.Output;
- // id
- payslip[16] = new SqlParameter("@PayslipDeductionId", payslipDeduction.PayslipDeductionId);
- payslip[17] = new SqlParameter("@ReferId", payslipDeduction.ReferId);
- payslip[18] = new SqlParameter("@FromDate", payslipDeduction.FromDate);
- payslip[19] = new SqlParameter("@ToDate", payslipDeduction.ToDate);
- payslip[20] = new SqlParameter("@AdditionalInfo", payslipDeduction.AdditionalInfo);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, payslip); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, payslip); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(payslip[15] != null ? payslip[15].SqlValue.ToString() : "0");
- return completed;
- }
- // Add SelfEmployed
- // tested on :18/10/2014
- public int AddSelfEmployed(tblSelfEmployed selfEmployed, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (selfEmployed.SelfEmployedId == 0 ? "tblInsertSelfEmployed" : "tblUpdateSelfEmployed");
- SqlParameter[] selfemp = new SqlParameter[28];
- selfemp[0] = new SqlParameter("@ClientRegistrationID", selfEmployed.ClientRegistrationID);
- selfemp[2] = new SqlParameter("@TypeOfSelfEmployment", selfEmployed.TypeOfSelfEmployment);
- selfemp[3] = new SqlParameter("@NameOfComapany", selfEmployed.NameOfComapany);
- selfemp[4] = new SqlParameter("@Shareholding", selfEmployed.Shareholding);
- selfemp[5] = new SqlParameter("@Position", selfEmployed.Position);
- selfemp[6] = new SqlParameter("@IncorporationDate", selfEmployed.IncorporationDate);
- selfemp[7] = new SqlParameter("@IsActive", selfEmployed.IsActive);
- selfemp[8] = new SqlParameter("@IsDeleted", selfEmployed.IsDeleted);
- selfemp[9] = new SqlParameter("@AddDate", DateTime.Now);
- selfemp[10] = new SqlParameter("@EntryBy", selfEmployed.EntryBy);
- selfemp[11] = new SqlParameter("@UpdateDate", DateTime.Now);
- selfemp[12] = new SqlParameter("@UpdateBy", selfEmployed.UpdateBy);
- selfemp[13] = new SqlParameter("@result", SqlDbType.Int);
- selfemp[13].Direction = ParameterDirection.Output;
- // id
- selfemp[14] = new SqlParameter("@SelfEmployedId", selfEmployed.SelfEmployedId);
- selfemp[15] = new SqlParameter("@ReferId", selfEmployed.ReferId);
- // financial YE
- selfemp[16] = new SqlParameter("@TurnoverY1", selfEmployed.TurnoverY1);
- selfemp[17] = new SqlParameter("@TurnoverY2", selfEmployed.TurnoverY2);
- selfemp[18] = new SqlParameter("@TurnoverY3", selfEmployed.TurnoverY3);
- selfemp[19] = new SqlParameter("@NetProfitY1", selfEmployed.NetProfitY1);
- selfemp[20] = new SqlParameter("@NetProfitY2", selfEmployed.NetProfitY2);
- selfemp[21] = new SqlParameter("@NetProfitY3", selfEmployed.NetProfitY3);
- selfemp[22] = new SqlParameter("@SalaryY1", selfEmployed.SalaryY1);
- selfemp[23] = new SqlParameter("@SalaryY2", selfEmployed.SalaryY2);
- selfemp[24] = new SqlParameter("@SalaryY3", selfEmployed.SalaryY3);
- selfemp[25] = new SqlParameter("@DividendsY1", selfEmployed.DividendsY1);
- selfemp[26] = new SqlParameter("@DividendsY2", selfEmployed.DividendsY2);
- selfemp[27] = new SqlParameter("@DividendsY3", selfEmployed.DividendsY3);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, selfemp); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, selfemp); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(selfemp[13] != null ? selfemp[13].SqlValue.ToString() : "0");
- return completed;
- }
- // self employed/unemployment new added by satish
- public int AddSelfEmployed_UnEmployment(tblSelfEmployed selfEmployed, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (selfEmployed.SelfEmployedId == 0 ? "tblInsertSelfEmployed" : "tblUpdateSelfEmployed_UnEmployment");
- SqlParameter[] selfemp = new SqlParameter[7];
- selfemp[0] = new SqlParameter("@ClientRegistrationID", selfEmployed.ClientRegistrationID);
- selfemp[1] = new SqlParameter("@SelfEmployedId", selfEmployed.SelfEmployedId);
- selfemp[2] = new SqlParameter("@ReferId", selfEmployed.ReferId);
- selfemp[3] = new SqlParameter("@DateOfUnemployment", !string.IsNullOrWhiteSpace(selfEmployed.StrDateOfUnemployment) ? Helper.GetDate(selfEmployed.StrDateOfUnemployment) : null);
- selfemp[4] = new SqlParameter("@DateOfReemployment", !string.IsNullOrWhiteSpace(selfEmployed.StrDateOfReemployment) ? Helper.GetDate(selfEmployed.StrDateOfReemployment) : null);
- selfemp[5] = new SqlParameter("@ReasonForUnemployment", selfEmployed.ReasonForUnemployment);
- selfemp[6] = new SqlParameter("@result", SqlDbType.Int);
- selfemp[6].Direction = ParameterDirection.Output;
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, selfemp); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, selfemp); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(selfemp[6] != null ? selfemp[6].SqlValue.ToString() : "0");
- return completed;
- }
- // Add OtherIncomeInformation
- public int AddOtherIncomeInformation(tblOtherIncomeInformation incomeInfo, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (incomeInfo.OtherIncomeInformationId == 0 ? "tblInsertOtherIncomeInformation" : "tblUpdateOtherIncomeInformation");
- SqlParameter[] income = new SqlParameter[16];
- income[0] = new SqlParameter("@SelfEmployedId", incomeInfo.SelfEmployedId);
- income[1] = new SqlParameter("@FirmName", incomeInfo.FirmName);
- income[2] = new SqlParameter("@IndividualContact", incomeInfo.IndividualContact);
- income[3] = new SqlParameter("@PhoneNo", incomeInfo.PhoneNo);
- income[4] = new SqlParameter("@FaxNo", incomeInfo.FaxNo);
- income[5] = new SqlParameter("@Qualification", incomeInfo.Qualification);
- income[6] = new SqlParameter("@IsActive", incomeInfo.IsActive);
- income[7] = new SqlParameter("@IsDeleted", incomeInfo.IsDeleted);
- income[8] = new SqlParameter("@AddDate", DateTime.Now);
- income[9] = new SqlParameter("@EntryBy", incomeInfo.EntryBy);
- income[10] = new SqlParameter("@UpdateDate", DateTime.Now);
- income[11] = new SqlParameter("@UpdateBy", incomeInfo.UpdateBy);
- income[12] = new SqlParameter("@ReferId", incomeInfo.ReferId);
- income[13] = new SqlParameter("@result", SqlDbType.Int);
- income[13].Direction = ParameterDirection.Output;
- income[14] = new SqlParameter("@ClientRegistrationID", incomeInfo.ClientRegistrationID);
- //id
- income[15] = new SqlParameter("@OtherIncomeInformationId", incomeInfo.OtherIncomeInformationId);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, income); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, income); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(income[13] != null ? income[13].SqlValue.ToString() : "0");
- return completed;
- }
- // Add SourceofFundsforPurchase
- public int AddSourceofFundsforPurchase(tblSourceofFundsforPurchase sourceOffund, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (sourceOffund.SourceofFundsforPurchaseId == 0 ? "tblInsertSourceofFundsforPurchase" : "tblUpdateSourceofFundsforPurchase");
- SqlParameter[] fund = new SqlParameter[16];
- fund[0] = new SqlParameter("@ClientRegistrationID", sourceOffund.ClientRegistrationID);
- fund[1] = new SqlParameter("@Cash", sourceOffund.Cash);
- fund[2] = new SqlParameter("@StockShare", sourceOffund.StockShare);
- fund[3] = new SqlParameter("@Gift", sourceOffund.Gift);
- fund[4] = new SqlParameter("@Equity", sourceOffund.Equity);
- fund[5] = new SqlParameter("@IsActive", sourceOffund.IsActive);
- fund[6] = new SqlParameter("@IsDeleted", sourceOffund.IsDeleted);
- fund[7] = new SqlParameter("@AddDate", DateTime.Now);
- fund[8] = new SqlParameter("@EntryBy", sourceOffund.EntryBy);
- fund[9] = new SqlParameter("@UpdateDate", DateTime.Now);
- fund[10] = new SqlParameter("@UpdateBy", sourceOffund.UpdateBy);
- fund[11] = new SqlParameter("@result", SqlDbType.Int);
- fund[11].Direction = ParameterDirection.Output;
- // id
- fund[12] = new SqlParameter("@SourceofFundsforPurchaseId", sourceOffund.SourceofFundsforPurchaseId);
- fund[13] = new SqlParameter("@ReferId", sourceOffund.ReferId);
- fund[14] = new SqlParameter("@GiftFrom", sourceOffund.GiftFrom);
- fund[15] = new SqlParameter("@GiftFromNew", sourceOffund.GiftFromNew);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, fund); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, fund); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(fund[11] != null ? fund[11].SqlValue.ToString() : "0");
- return completed;
- }
- // Add Current Commitments
- public int AddCurrentCommitments(tblCurrentCommitment commitments, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (commitments.CurrentCommitmentsId == 0 ? "tblInsertCurrentCommitments" : "tblUpdateCurrentCommitments");
- SqlParameter[] commitment = new SqlParameter[19];
- commitment[0] = new SqlParameter("@OwnerName", commitments.OwnerName);
- commitment[1] = new SqlParameter("@TypeOfLoan", commitments.TypeOfLoan);
- commitment[2] = new SqlParameter("@Lender", commitments.Lender);
- commitment[3] = new SqlParameter("@AmountoutStanding", commitments.AmountoutStanding);
- commitment[4] = new SqlParameter("@monthlyPayment", commitments.monthlyPayment);
- commitment[5] = new SqlParameter("@IsSecured", commitments.IsSecured);
- commitment[6] = new SqlParameter("@IsToBeRepaid", commitments.IsToBeRepaid);
- commitment[7] = new SqlParameter("@IsActive", commitments.IsActive);
- commitment[8] = new SqlParameter("@IsDeleted", commitments.IsDeleted);
- commitment[9] = new SqlParameter("@AddDate", DateTime.Now);
- commitment[10] = new SqlParameter("@EntryBy", commitments.EntryBy);
- commitment[11] = new SqlParameter("@UpdateDate", DateTime.Now);
- commitment[12] = new SqlParameter("@UpdateBy", commitments.UpdateBy);
- commitment[13] = new SqlParameter("@result", SqlDbType.Int);
- commitment[13].Direction = ParameterDirection.Output;
- //id
- commitment[14] = new SqlParameter("@CurrentCommitmentsId", commitments.CurrentCommitmentsId);
- commitment[15] = new SqlParameter("@ClientRegistrationID", commitments.ClientRegistrationID);
- commitment[16] = new SqlParameter("@ReferId", commitments.ReferId);
- commitment[17] = new SqlParameter("@Owner", commitments.Owner);
- commitment[18] = new SqlParameter("@TypeofLoanOther", commitments.TypeofLoanOther);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(commitment[13] != null ? (commitment[13].SqlValue != null ? commitment[13].SqlValue.ToString() : "0") : "0");
- return completed;
- }
- // Created on 20/01/2015
- //Add and Update New Mortgage And Property Details
- public int AddUpdateNewMortgageandPropertyDetail(tblAdditional_Property_Info addProp, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (addProp.tblAdditional_Property_InfoID == 0 ? "tblInsertbltblAdditional_Property_Info" : "tblUpdateAdditional_Property_Info");
- SqlParameter[] additionPro = new SqlParameter[]{
- new SqlParameter("@ReferId", addProp.ReferId),
- new SqlParameter("@Selling_Agents", addProp.Selling_Agents),
- new SqlParameter("@Solicitors", addProp.Solicitors),
- new SqlParameter("@Bank_Details", addProp.Bank_Details),
- new SqlParameter("@Additional_Info", addProp.Additional_Info),
- new SqlParameter("@IsActive", addProp.IsActive),
- new SqlParameter("@IsDeleted", addProp.IsDeleted),
- new SqlParameter("@AddDate", DateTime.Now),
- new SqlParameter("@EntryBy", addProp.EntryBy),
- new SqlParameter("@UpdateDate", DateTime.Now),
- new SqlParameter("@UpdateBy", addProp.UpdateBy),
- new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
- new SqlParameter("@ClientRegistrationID", addProp.ClientRegistrationID),
- new SqlParameter("@tblAdditional_Property_InfoID", addProp.tblAdditional_Property_InfoID),
- new SqlParameter("@Type_Of_Application", addProp.Type_Of_Application),
- new SqlParameter("@Address_Line1", addProp.Address_Line1),
- new SqlParameter("@Address_Line2", addProp.Address_Line2),
- new SqlParameter("@Address_Line3", addProp.Address_Line3),
- new SqlParameter("@Address_Line4", addProp.Address_Line4),
- new SqlParameter("@Post_Code", addProp.Post_Code),
- new SqlParameter("@Country", addProp.Country),
- };
- var outParameter = additionPro.Where(x => x.Direction == ParameterDirection.Output).FirstOrDefault();
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, additionPro); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, additionPro); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(outParameter != null ? outParameter.SqlValue.ToString() : "0");
- return completed;
- }
- // Created on 20/01/2015
- //Add and Update Investment Portfolio
- public int AddUpdateInvestmentPortfolio(tblOther_Mortgages investmentPortfolio, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (investmentPortfolio.Other_MortgagesID == 0 ? "tblInserttblOther_Mortgages" : "tblUpdatetblOther_Mortgages");
- SqlParameter[] commitment = new SqlParameter[39];
- commitment[0] = new SqlParameter("@Lender", investmentPortfolio.Lender);
- commitment[1] = new SqlParameter("@Account_Number", investmentPortfolio.Account_Number);
- commitment[2] = new SqlParameter("@Prop_Country", investmentPortfolio.Prop_Country);
- commitment[3] = new SqlParameter("@Current_Value", investmentPortfolio.Current_Value);
- commitment[4] = new SqlParameter("@Rental_Income", investmentPortfolio.Rental_Income);
- commitment[5] = new SqlParameter("@Ownership", investmentPortfolio.Ownership);
- commitment[6] = new SqlParameter("@Amount_Outstanding", investmentPortfolio.Amount_Outstanding);
- commitment[7] = new SqlParameter("@Monthly_Payment", investmentPortfolio.Monthly_Payment);
- commitment[8] = new SqlParameter("@Term", investmentPortfolio.Term);
- commitment[9] = new SqlParameter("@Repayment_Method", investmentPortfolio.Repayment_Method);
- commitment[10] = new SqlParameter("@Current_Rate_Info", investmentPortfolio.Current_Rate_Info);
- commitment[11] = new SqlParameter("@ERP_Info", investmentPortfolio.ERP_Info);
- commitment[12] = new SqlParameter("@IsActive", investmentPortfolio.IsActive);
- commitment[13] = new SqlParameter("@IsDeleted", investmentPortfolio.IsDeleted);
- commitment[14] = new SqlParameter("@AddDate", DateTime.Now);
- commitment[15] = new SqlParameter("@EntryBy", investmentPortfolio.EntryBy);
- commitment[16] = new SqlParameter("@UpdateDate", DateTime.Now);
- commitment[17] = new SqlParameter("@UpdateBy", investmentPortfolio.UpdateBy);
- commitment[18] = new SqlParameter("@No_Bedroom", investmentPortfolio.No_Bedrooms);
- commitment[19] = new SqlParameter("@Type_Of_Application", investmentPortfolio.Type_Of_Application);
- commitment[20] = new SqlParameter("@Purch_Price", investmentPortfolio.Purch_Price);
- commitment[21] = new SqlParameter("@Deposit_Available", investmentPortfolio.Deposit_Available);
- commitment[22] = new SqlParameter("@Mortgage_Required", investmentPortfolio.Mortgage_Required);
- commitment[23] = new SqlParameter("@Type_Of_Property", investmentPortfolio.Type_Of_Property);
- commitment[24] = new SqlParameter("@Tenure", investmentPortfolio.Tenure);
- commitment[25] = new SqlParameter("@For_Flats_Maisonette", investmentPortfolio.For_Flats_Maisonette);
- commitment[26] = new SqlParameter("@Additional_Info", investmentPortfolio.Additional_Info);
- commitment[27] = new SqlParameter("@Capital_Raising", investmentPortfolio.Capital_Raising);
- commitment[28] = new SqlParameter("@Reason", investmentPortfolio.Reason);
- commitment[29] = new SqlParameter("@Ex_Local", investmentPortfolio.Ex_Local);
- commitment[30] = new SqlParameter("@result", SqlDbType.Int);
- commitment[30].Direction = ParameterDirection.Output;
- // id
- commitment[31] = new SqlParameter("@Other_MortgagesID", investmentPortfolio.Other_MortgagesID);
- commitment[32] = new SqlParameter("@ClientRegistrationID", investmentPortfolio.ClientRegistrationID);
- commitment[33] = new SqlParameter("@ReferId", investmentPortfolio.ReferId);
- commitment[34] = new SqlParameter("@Prop_Adddress_Line1", investmentPortfolio.Prop_Adddress_Line1);
- commitment[35] = new SqlParameter("@Prop_Adddress_Line2", investmentPortfolio.Prop_Adddress_Line2);
- commitment[36] = new SqlParameter("@Prop_Adddress_Line3", investmentPortfolio.Prop_Adddress_Line3);
- commitment[37] = new SqlParameter("@Prop_Adddress_Line4", investmentPortfolio.Prop_Adddress_Line4);
- commitment[38] = new SqlParameter("@Prop_PostCode", investmentPortfolio.Prop_PostCode);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(commitment[30] != null ? commitment[30].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddMortgagePropertyDetail(tblNew_Mortgage_Property_Details newMortgagePropertyDetails, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- // newMortgagePropertyDetails.New_Mortgage_Property_DetailsID
- string proc = (newMortgagePropertyDetails.New_Mortgage_Property_DetailsID == 0 ? "tblInserttblNew_Mortgage_Property_Details" : "tblUpdatetblNew_Mortgage_Property_Details");
- SqlParameter[] commitment = new SqlParameter[35];
- commitment[0] = new SqlParameter("@ReferId", newMortgagePropertyDetails.ReferId);
- commitment[1] = new SqlParameter("@Type_Of_Application", newMortgagePropertyDetails.Type_Of_Application);
- commitment[2] = new SqlParameter("@Address_Of_Property", newMortgagePropertyDetails.Address_Of_Property);
- commitment[3] = new SqlParameter("@Purch_Price", newMortgagePropertyDetails.Purch_Price);
- commitment[4] = new SqlParameter("@Deposit_Available", newMortgagePropertyDetails.Deposit_Available);
- commitment[5] = new SqlParameter("@Mortgage_Required", newMortgagePropertyDetails.Mortgage_Required);
- commitment[6] = new SqlParameter("@Repayment", newMortgagePropertyDetails.Repayment);
- commitment[7] = new SqlParameter("@Term_Of_Mortgage", newMortgagePropertyDetails.Term_Of_Mortgage);
- commitment[8] = new SqlParameter("@Details_Of_Affordability", newMortgagePropertyDetails.Details_Of_Affordability);
- commitment[9] = new SqlParameter("@Type_Of_Property", newMortgagePropertyDetails.Type_Of_Property);
- commitment[10] = new SqlParameter("@Tenure", newMortgagePropertyDetails.Tenure);
- commitment[11] = new SqlParameter("@Leasehold_Term", newMortgagePropertyDetails.Leasehold_Term);
- commitment[12] = new SqlParameter("@Year_Build", newMortgagePropertyDetails.Year_Build);
- commitment[13] = new SqlParameter("@Construction", newMortgagePropertyDetails.Construction);
- commitment[14] = new SqlParameter("@For_Flats_Maisonette", newMortgagePropertyDetails.For_Flats_Maisonette);
- commitment[15] = new SqlParameter("@Purpose_Built", newMortgagePropertyDetails.Purpose_Built);
- commitment[16] = new SqlParameter("@Conversion", newMortgagePropertyDetails.Conversion);
- commitment[17] = new SqlParameter("@No_Of_Floors", newMortgagePropertyDetails.No_Of_Floors);
- commitment[18] = new SqlParameter("@Any_Commercial", newMortgagePropertyDetails.Any_Commercial);
- commitment[19] = new SqlParameter("@Service_Charge", newMortgagePropertyDetails.Service_Charge);
- commitment[20] = new SqlParameter("@Ground_Rent", newMortgagePropertyDetails.Ground_Rent);
- commitment[21] = new SqlParameter("@Additional_Info", newMortgagePropertyDetails.Additional_Info);
- commitment[22] = new SqlParameter("@IsActive", newMortgagePropertyDetails.IsActive);
- commitment[23] = new SqlParameter("@IsDeleted", newMortgagePropertyDetails.IsDeleted);
- commitment[24] = new SqlParameter("@AddDate", DateTime.Now);
- commitment[25] = new SqlParameter("@EntryBy", newMortgagePropertyDetails.EntryBy);
- commitment[26] = new SqlParameter("@UpdateDate", DateTime.Now);
- commitment[27] = new SqlParameter("@UpdateBy", newMortgagePropertyDetails.UpdateBy);
- commitment[28] = new SqlParameter("@result", SqlDbType.Int);
- commitment[28].Direction = ParameterDirection.Output;
- commitment[29] = new SqlParameter("@Source_Of_Deposit", newMortgagePropertyDetails.Source_Of_Deposit);
- commitment[30] = new SqlParameter("@Capital_Raising", newMortgagePropertyDetails.Capital_Raising);
- commitment[31] = new SqlParameter("@Reason", newMortgagePropertyDetails.Reason);
- commitment[32] = new SqlParameter("@Ex_Local", newMortgagePropertyDetails.Ex_Local);
- //id
- commitment[33] = new SqlParameter("@New_Mortgage_Property_DetailsID", newMortgagePropertyDetails.New_Mortgage_Property_DetailsID);
- commitment[34] = new SqlParameter("@ClientRegistrationID", newMortgagePropertyDetails.ClientRegistrationID);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(commitment[28] != null ? commitment[28].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddResidentialMortgage(tblResidentialMortgage ResidentialMortgage, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (ResidentialMortgage.ResidentialMortgageID == 0 ? "tblInsertblResidentialMortgage" : "tblUpdatetblResidentialMortgage");
- SqlParameter[] resMortgage = new SqlParameter[24];
- resMortgage[0] = new SqlParameter("@ReferId", ResidentialMortgage.ReferId);
- resMortgage[1] = new SqlParameter("@Lender", ResidentialMortgage.Lender);
- resMortgage[2] = new SqlParameter("@Account_Number", ResidentialMortgage.Account_Number);
- resMortgage[3] = new SqlParameter("@Current_Value", ResidentialMortgage.Current_Value);
- resMortgage[4] = new SqlParameter("@Ownership", ResidentialMortgage.Ownership);
- resMortgage[5] = new SqlParameter("@Original_Mortgage", ResidentialMortgage.Original_Mortgage);
- resMortgage[6] = new SqlParameter("@Amount_Outstanding", ResidentialMortgage.Amount_Outstanding);
- resMortgage[7] = new SqlParameter("@Monthly_Payment", ResidentialMortgage.Monthly_Payment);
- resMortgage[8] = new SqlParameter("@Term", ResidentialMortgage.Term);
- resMortgage[9] = new SqlParameter("@Repayment_Method", ResidentialMortgage.Repayment_Method);
- resMortgage[10] = new SqlParameter("@Repayment_Vehicle", ResidentialMortgage.Repayment_Vehicle);
- resMortgage[11] = new SqlParameter("@Current_Rate_Info", ResidentialMortgage.Current_Rate_Info);
- resMortgage[12] = new SqlParameter("@ERP_Info", ResidentialMortgage.ERP_Info);
- resMortgage[13] = new SqlParameter("@Portable", ResidentialMortgage.Portable);
- resMortgage[14] = new SqlParameter("@IsActive", ResidentialMortgage.IsActive);
- resMortgage[15] = new SqlParameter("@IsDeleted", ResidentialMortgage.IsDeleted);
- resMortgage[16] = new SqlParameter("@AddDate", DateTime.Now);
- resMortgage[17] = new SqlParameter("@EntryBy", ResidentialMortgage.EntryBy);
- resMortgage[18] = new SqlParameter("@UpdateDate", DateTime.Now);
- resMortgage[19] = new SqlParameter("@UpdateBy", ResidentialMortgage.UpdateBy);
- resMortgage[20] = new SqlParameter("@result", SqlDbType.Int);
- resMortgage[20].Direction = ParameterDirection.Output;
- // id
- resMortgage[21] = new SqlParameter("@ResidentialMortgageID", ResidentialMortgage.ResidentialMortgageID);
- resMortgage[22] = new SqlParameter("@ClientRegistrationID", ResidentialMortgage.ClientRegistrationID);
- resMortgage[23] = new SqlParameter("@AddressID", ResidentialMortgage.AddressID);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, resMortgage); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, resMortgage); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(resMortgage[20] != null ? resMortgage[20].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddExpenditure(tblExpenditure Expenditure, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = (Expenditure.ExpenditureID == 0 ? "tblInsertblExpenditure" : "tblUpdatetblExpenditure");
- SqlParameter[] expenditure = new SqlParameter[34];
- expenditure[0] = new SqlParameter("@ClientRegistrationID", Expenditure.ClientRegistrationID);
- expenditure[1] = new SqlParameter("@ReferId", Expenditure.ReferId);
- expenditure[2] = new SqlParameter("@Mortgage_Rent", Expenditure.Mortgage_Rent);
- expenditure[3] = new SqlParameter("@Credit_Commitments", Expenditure.Credit_Commitments);
- expenditure[4] = new SqlParameter("@Maintenance", Expenditure.Maintenance);
- expenditure[5] = new SqlParameter("@Food_Drink_clothing", Expenditure.Food_Drink_clothing);
- expenditure[6] = new SqlParameter("@Utilities_Including_Council_Tax", Expenditure.Utilities_Including_Council_Tax);
- expenditure[7] = new SqlParameter("@Miscellaneous_Goods_Services", Expenditure.Miscellaneous_Goods_Services);
- expenditure[8] = new SqlParameter("@Transport", Expenditure.Transport);
- expenditure[9] = new SqlParameter("@Entertainment_Recreation", Expenditure.Entertainment_Recreation);
- expenditure[10] = new SqlParameter("@Holidays", Expenditure.Holidays);
- expenditure[11] = new SqlParameter("@Nursery_College_Uni_fees", Expenditure.Nursery_College_Uni_fees);
- expenditure[12] = new SqlParameter("@Other_Expenditure", Expenditure.Other_Expenditure);
- expenditure[13] = new SqlParameter("@Existing_Life_Insurance_PHI_Premiums", Expenditure.Existing_Life_Insurance_PHI_Premiums);
- expenditure[14] = new SqlParameter("@Discretionary_Expenditure", Expenditure.Discretionary_Expenditure);
- expenditure[15] = new SqlParameter("@Monthly_Expenditure_B", Expenditure.Monthly_Expenditure_B);
- expenditure[16] = new SqlParameter("@Monthly_Disposable_Income_A_B", Expenditure.Monthly_Disposable_Income_A_B);
- expenditure[17] = new SqlParameter("@Remaining_Income", Expenditure.Remaining_Income);
- expenditure[18] = new SqlParameter("@Any_Changes_In_Next_5_Years", Expenditure.Any_Changes_In_Next_5_Years);
- expenditure[19] = new SqlParameter("@Any_Adverse_Credit_History", Expenditure.Any_Adverse_Credit_History);
- expenditure[20] = new SqlParameter("@Total_Income_All_Sources", Expenditure.Total_Income_All_Sources);
- expenditure[21] = new SqlParameter("@Total_Net_Monthly_Income_A", Expenditure.Total_Net_Monthly_Income_A);
- expenditure[22] = new SqlParameter("@IsActive", Expenditure.IsActive);
- expenditure[23] = new SqlParameter("@IsDeleted", Expenditure.IsDeleted);
- expenditure[24] = new SqlParameter("@AddDate", DateTime.Now);
- expenditure[25] = new SqlParameter("@EntryBy", Expenditure.EntryBy);
- expenditure[26] = new SqlParameter("@UpdateDate", DateTime.Now);
- expenditure[27] = new SqlParameter("@UpdateBy", Expenditure.UpdateBy);
- expenditure[28] = new SqlParameter("@result", SqlDbType.Int);
- expenditure[28].Direction = ParameterDirection.Output;
- // id
- expenditure[29] = new SqlParameter("@ExpenditureID", Expenditure.ExpenditureID);
- expenditure[30] = new SqlParameter("@Student_Loan_Payments", Expenditure.Student_Loan_Payments);
- expenditure[31] = new SqlParameter("@FromDate", Expenditure.FromDate);
- expenditure[32] = new SqlParameter("@ToDate", Expenditure.ToDate);
- expenditure[33] = new SqlParameter("@AdditionalInfo", Expenditure.AdditionalInfo);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, expenditure); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, expenditure); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(expenditure[28] != null ? expenditure[28].SqlValue.ToString() : "0");
- return completed;
- }
- // Created on 07/10/2014
- // For add and update Dependent table
- public int AddUpdateDependant(tblDependant dependent, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- if (dependent.DependantID == 0)
- {
- SqlParameter[] par = new SqlParameter[]
- {
- new SqlParameter("@Applicant", dependent.Applicant),
- new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(dependent.StrDependentDOB) ? Helper.GetDate(dependent.StrDependentDOB) : null),
- new SqlParameter("@ClientRegistrationID", dependent.ClientRegistrationID),//set clientid in model after model binding
- new SqlParameter("@AddDate", DateTime.Now),
- new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
- new SqlParameter("@ReferId", dependent.ReferId),
- new SqlParameter("@Name", dependent.Name)
- };
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "tblInsertDependant", tran, con, par); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblInsertDependant", par); }
- completed = Convert.ToInt32(par[4] != null ? par[4].SqlValue.ToString() : "0");
- }
- else
- {
- SqlParameter[] par = new SqlParameter[]
- {
- new SqlParameter("@Applicant", dependent.Applicant),
- new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(dependent.StrDependentDOB) ? Helper.GetDate(dependent.StrDependentDOB) : null),
- new SqlParameter("@UpdateDate", DateTime.Now),
- new SqlParameter("@UpdateBy", dependent.UpdateBy),//set updated by id in model after model binding
- new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
- new SqlParameter("@DependantID", dependent.DependantID),
- new SqlParameter("@ReferId", dependent.ReferId),
- new SqlParameter("@ClientRegistrationID", dependent.ClientRegistrationID),
- new SqlParameter("@Name", dependent.Name)
- };
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "tblUpdateDependant", tran, con, par); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblUpdateDependant", par); }
- completed = Convert.ToInt32(par[4] != null ? par[4].SqlValue.ToString() : "0");
- }
- return completed;
- }
- // new 21/10/2014
- public int AddUpdateAdditionalProperty(tblAdditional_Property_Info addProp, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (addProp.tblAdditional_Property_InfoID == 0 ? "tblInsertbltblAdditional_Property_Info" : "tblUpdateAdditional_Property_Info");
- SqlParameter[] additionPro = new SqlParameter[14];
- additionPro[0] = new SqlParameter("@ReferId", addProp.ReferId);
- additionPro[1] = new SqlParameter("@Selling_Agents", addProp.Selling_Agents);
- additionPro[2] = new SqlParameter("@Solicitors", addProp.Solicitors);
- additionPro[3] = new SqlParameter("@Bank_Details", addProp.Bank_Details);
- additionPro[4] = new SqlParameter("@Additional_Info", addProp.Additional_Info);
- additionPro[5] = new SqlParameter("@IsActive", addProp.IsActive);
- additionPro[6] = new SqlParameter("@IsDeleted", addProp.IsDeleted);
- additionPro[7] = new SqlParameter("@AddDate", DateTime.Now);
- additionPro[8] = new SqlParameter("@EntryBy", addProp.EntryBy);
- additionPro[9] = new SqlParameter("@UpdateDate", DateTime.Now);
- additionPro[10] = new SqlParameter("@UpdateBy", addProp.UpdateBy);
- additionPro[11] = new SqlParameter("@result", SqlDbType.Int);
- additionPro[11].Direction = ParameterDirection.Output;
- additionPro[12] = new SqlParameter("@ClientRegistrationID", addProp.ClientRegistrationID);
- additionPro[13] = new SqlParameter("@tblAdditional_Property_InfoID", addProp.tblAdditional_Property_InfoID);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, additionPro); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, additionPro); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(additionPro[11] != null ? additionPro[11].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddUpdateCurrentSavingArrangements(tblCurrent_Savings_Arrangements savings, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (savings.Current_Savings_ArrangementsID == 0 ? "tblInsertCurrent_Savings_Arrangements" : "tblUpdateCurrent_Savings_Arrangements");
- SqlParameter[] currSaving = new SqlParameter[15];
- currSaving[0] = new SqlParameter("@Current_Savings_ArrangementsID", savings.Current_Savings_ArrangementsID);
- currSaving[1] = new SqlParameter("@ReferId", savings.ReferId);
- currSaving[2] = new SqlParameter("@Total_Amount", savings.Total_Amount);
- currSaving[3] = new SqlParameter("@If_No_Fund", savings.If_No_Fund);
- currSaving[4] = new SqlParameter("@IsActive", savings.IsActive);
- currSaving[5] = new SqlParameter("@IsDeleted", savings.IsDeleted);
- currSaving[6] = new SqlParameter("@AddDate", DateTime.Now);
- currSaving[7] = new SqlParameter("@EntryBy", savings.EntryBy);
- currSaving[8] = new SqlParameter("@UpdateDate", DateTime.Now);
- currSaving[9] = new SqlParameter("@UpdateBy", savings.UpdateBy);
- currSaving[10] = new SqlParameter("@result", SqlDbType.Int);
- currSaving[10].Direction = ParameterDirection.Output;
- currSaving[11] = new SqlParameter("@ClientRegistrationID", savings.ClientRegistrationID);
- currSaving[12] = new SqlParameter("@FromDate", savings.FromDate);
- currSaving[13] = new SqlParameter("@ToDate", savings.ToDate);
- currSaving[14] = new SqlParameter("@AdditionalInfo", savings.AdditionalInfo);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, currSaving); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, currSaving); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(currSaving[10] != null ? currSaving[10].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddUpdateProtection(tblCurrent_Protection_Arrangements Protection, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (Protection.Current_Protection_ArrangementsID == 0 ? "tblInserttblCurrent_Protection_Arrangements" : "tblUpdateCurrent_Protection_Arrangements");
- SqlParameter[] protection = new SqlParameter[18];
- protection[0] = new SqlParameter("@Current_Protection_ArrangementsID", Protection.Current_Protection_ArrangementsID);
- protection[1] = new SqlParameter("@ReferId", Protection.ReferId);
- protection[2] = new SqlParameter("@Owner", Protection.Owner);
- protection[3] = new SqlParameter("@Type_Of_Policy", Protection.Type_Of_Policy);
- protection[4] = new SqlParameter("@Provider", Protection.Provider);
- protection[5] = new SqlParameter("@Sum_Assured", Protection.Sum_Assured);
- protection[6] = new SqlParameter("@Monthly_Premium", Protection.Monthly_Premium);
- protection[7] = new SqlParameter("@End_Date_Term", Protection.End_Date_Term);
- protection[8] = new SqlParameter("@In_Trust", Protection.In_Trust);
- protection[9] = new SqlParameter("@IsActive", Protection.IsActive);
- protection[10] = new SqlParameter("@IsDeleted", Protection.IsDeleted);
- protection[11] = new SqlParameter("@AddDate", DateTime.Now);
- protection[12] = new SqlParameter("@EntryBy", Protection.EntryBy);
- protection[13] = new SqlParameter("@UpdateDate", DateTime.Now);
- protection[14] = new SqlParameter("@UpdateBy", Protection.UpdateBy);
- protection[15] = new SqlParameter("@result", SqlDbType.Int);
- protection[15].Direction = ParameterDirection.Output;
- protection[16] = new SqlParameter("@ClientRegistrationID", Protection.ClientRegistrationID);
- protection[17] = new SqlParameter("@TypeofPolicyOther", Protection.TypeofPolicyOther);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, protection); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, protection); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(protection[15] != null ? protection[15].SqlValue.ToString() : "0");
- return completed;
- }
- // Created on 20/01/2015
- //Add and Update Employment Benefit
- public int AddUpdateEmploymentBenefit(tblEmployment_Benefit emp, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (emp.Employment_BenefitID == 0 ? "tblInserttblEmployment_Benefit" : "tblUpdateEmployment_Benefit");
- SqlParameter[] empBenefit = new SqlParameter[18];
- empBenefit[0] = new SqlParameter("@Employment_BenefitID", emp.Employment_BenefitID);
- empBenefit[1] = new SqlParameter("@ReferId", emp.ReferId);
- empBenefit[2] = new SqlParameter("@Sickness_Benefit", emp.Sickness_Benefit);
- empBenefit[3] = new SqlParameter("@Sikness_Amount_Payable", emp.Sikness_Amount_Payable);
- empBenefit[4] = new SqlParameter("@Death_In_Service", emp.Death_In_Service);
- empBenefit[5] = new SqlParameter("@Death_Amount_Payable", emp.Death_Amount_Payable);
- //empBenefit[6] = new SqlParameter("@Have_You_Made_A_Will", emp.Have_You_Made_A_Will);
- empBenefit[6] = new SqlParameter("@IsActive", emp.IsActive);
- empBenefit[7] = new SqlParameter("@IsDeleted", emp.IsDeleted);
- empBenefit[8] = new SqlParameter("@AddDate", DateTime.Now);
- empBenefit[9] = new SqlParameter("@EntryBy", emp.EntryBy);
- empBenefit[10] = new SqlParameter("@UpdateDate", DateTime.Now);
- empBenefit[11] = new SqlParameter("@UpdateBy", emp.UpdateBy);
- empBenefit[12] = new SqlParameter("@ClientRegistrationID", emp.ClientRegistrationID);
- empBenefit[13] = new SqlParameter("@result", SqlDbType.Int);
- empBenefit[13].Direction = ParameterDirection.Output;
- empBenefit[14] = new SqlParameter("@FromDate", emp.FromDate);
- empBenefit[15] = new SqlParameter("@ToDate", emp.ToDate);
- empBenefit[16] = new SqlParameter("@AdditionalInfo", emp.AdditionalInfo);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empBenefit); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empBenefit); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(empBenefit[13] != null ? empBenefit[13].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddUpdatePensionInvestment(tblCurrent_Pension_Investment_Arrangements pension, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (pension.tblCurrent_Pension_Investment_ArrangementsID == 0 ? "tblInserttblCurrent_Pension_Investment_Arrangements" : "tblUpdateCurrent_Pension_Investment_Arrangements");
- SqlParameter[] pensionInv = new SqlParameter[17];
- pensionInv[0] = new SqlParameter("@tblCurrent_Pension_Investment_ArrangementsID", pension.tblCurrent_Pension_Investment_ArrangementsID);
- pensionInv[1] = new SqlParameter("@ReferId ", pension.ReferId);
- pensionInv[2] = new SqlParameter("@Owner", pension.Owner);
- pensionInv[3] = new SqlParameter("@Type_Of_Policy", pension.Type_Of_Policy);
- pensionInv[4] = new SqlParameter("@Provider", pension.Provider);
- pensionInv[5] = new SqlParameter("@Sum_Assured", pension.Sum_Assured);
- pensionInv[6] = new SqlParameter("@Monthly_Premium", pension.Monthly_Premium);
- pensionInv[7] = new SqlParameter("@End_Date_Term", pension.End_Date_Term);
- pensionInv[8] = new SqlParameter("@In_Trust", pension.In_Trust);
- pensionInv[9] = new SqlParameter("@IsActive", pension.IsActive);
- pensionInv[10] = new SqlParameter("@IsDeleted", pension.IsDeleted);
- pensionInv[11] = new SqlParameter("@AddDate", DateTime.Now);
- pensionInv[12] = new SqlParameter("@EntryBy", pension.EntryBy);
- pensionInv[13] = new SqlParameter("@UpdateDate", DateTime.Now);
- pensionInv[14] = new SqlParameter("@UpdateBy", pension.UpdateBy);
- pensionInv[15] = new SqlParameter("@ClientRegistrationID", pension.ClientRegistrationID);
- pensionInv[16] = new SqlParameter("@result", SqlDbType.Int);
- pensionInv[16].Direction = ParameterDirection.Output;
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, pensionInv); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, pensionInv); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(pensionInv[16] != null ? pensionInv[16].SqlValue.ToString() : "0");
- return completed;
- }
- public int AddUpdateCurrentBuildingContent(tblCurrent_Buildings_Contents_Arrangements building, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (building.Current_Buildings_Contents_ArrangementsID == 0 ? "tblInsertCurrent_Buildings_Contents_Arrangements" : "tblUpdateCurrent_Buildings_Contents_Arrangements");
- SqlParameter[] curBuilding = new SqlParameter[]{
- new SqlParameter("@Current_Buildings_Contents_ArrangementsID", building.Current_Buildings_Contents_ArrangementsID),
- new SqlParameter("@ReferId", building.ReferId),
- new SqlParameter("@Property_Insured", building.Property_Insured),
- new SqlParameter("@Owner", building.Owner),
- new SqlParameter("@Type_Of_Policy", building.Type_Of_Policy),
- new SqlParameter("@Provider", building.Provider),
- new SqlParameter("@Sum_Assured", building.Sum_Assured),
- new SqlParameter("@Monthly_Premium", building.Monthly_Premium),
- new SqlParameter("@Additional_Benefits", building.Additional_Benefits),
- new SqlParameter("@Rebuild_Value", building.Rebuild_Value),
- new SqlParameter("@Value_Of_Contents", building.Value_Of_Contents),
- new SqlParameter("@Specified_Items", building.Specified_Items),
- new SqlParameter("@Include", building.Include),
- new SqlParameter("@Excess", building.Excess),
- new SqlParameter("@Smoke_Alarm", building.Smoke_Alarm),
- new SqlParameter("@Free_From_Subsidence_Flooding", building.Free_From_Subsidence_Flooding),
- new SqlParameter("@Alarm", building.Alarm),
- new SqlParameter("@Locks_Fitted", building.Locks_Fitted),
- new SqlParameter("@Additional_Info", building.Additional_Info),
- new SqlParameter("@Further_Information", building.Further_Information),
- new SqlParameter("@Transfer_Foreign_Currencies_To_Sterling", building.Transfer_Foreign_Currencies_To_Sterling),
- new SqlParameter("@IsActive", building.IsActive),
- new SqlParameter("@IsDeleted", building.IsDeleted),
- new SqlParameter("@AddDate", DateTime.Now),
- new SqlParameter("@EntryBy", building.EntryBy),
- new SqlParameter("@UpdateDate", DateTime.Now),
- new SqlParameter("@UpdateBy", building.UpdateBy),
- new SqlParameter("@ClientRegistrationID", building.ClientRegistrationID),
- new SqlParameter("@result", SqlDbType.Int) { Direction=ParameterDirection.Output},
- new SqlParameter("@RenewalDate", !string.IsNullOrWhiteSpace(building.StrRenewalDate) ? Helper.GetDate(building.StrRenewalDate) : null)
- };
- // get output parameter
- var outPutparam = curBuilding.Where(x => x.Direction == ParameterDirection.Output).FirstOrDefault();
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, curBuilding); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, curBuilding); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(outPutparam != null ? outPutparam.SqlValue.ToString() : "0");
- return completed;
- }
- public DataSet GetCurrentBuildingContentList(int clientregistrationId)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",clientregistrationId)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GET_Current_Buildings_Contents_Arrangements", Param);
- return ds;
- }
- public void DeleteCurrentBuildingContent(int Current_Buildings_Contents_ArrangementsId)
- {
- SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@Current_Buildings_Contents_ArrangementsId", Current_Buildings_Contents_ArrangementsId) };
- var command = "delete from tblCurrent_Buildings_Contents_Arrangements where Current_Buildings_Contents_ArrangementsId = @Current_Buildings_Contents_ArrangementsId";
- SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
- }
- public int AddUpdateMortgageRequirementFactors(tblMortgage_Requirement_Factors mortgage, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- string proc = (mortgage.Mortgage_Requirement_FactorsID == 0 ? "tblInsertblMortgage_Requirement_Factors" : "tblUpdateMortgage_Requirement_Factors");
- SqlParameter[] mortgageReq = new SqlParameter[26];
- mortgageReq[0] = new SqlParameter("@Mortgage_Requirement_FactorsID", mortgage.Mortgage_Requirement_FactorsID);
- mortgageReq[1] = new SqlParameter("@ReferId", mortgage.ReferId);
- mortgageReq[2] = new SqlParameter("@Type_Of_Rate", mortgage.Type_Of_Rate);
- mortgageReq[3] = new SqlParameter("@Term_Of_Deal_Period", mortgage.Term_Of_Deal_Period);
- mortgageReq[4] = new SqlParameter("@Add_Fees", mortgage.Add_Fees);
- mortgageReq[5] = new SqlParameter("@Free_Valuation", mortgage.Free_Valuation);
- mortgageReq[6] = new SqlParameter("@Free_Solicitors", mortgage.Free_Solicitors);
- mortgageReq[7] = new SqlParameter("@Offset", mortgage.Offset);
- mortgageReq[8] = new SqlParameter("@Speed_Of_Completion", mortgage.Speed_Of_Completion);
- mortgageReq[9] = new SqlParameter("@No_Booking_Arrangement_Fee", mortgage.No_Booking_Arrangement_Fee);
- mortgageReq[10] = new SqlParameter("@Flexible", mortgage.Flexible);
- mortgageReq[11] = new SqlParameter("@Lowest_Monthly_Payment", mortgage.Lowest_Monthly_Payment);
- mortgageReq[12] = new SqlParameter("@Lowest_APR", mortgage.Lowest_APR);
- mortgageReq[13] = new SqlParameter("@Lowest_Cost_Over_Term", mortgage.Lowest_Cost_Over_Term);
- mortgageReq[14] = new SqlParameter("@Capped_Upper_Limit", mortgage.Capped_Upper_Limit);
- mortgageReq[15] = new SqlParameter("@Access_To_Cash_Back", mortgage.Access_To_Cash_Back);
- mortgageReq[16] = new SqlParameter("@No_ERP", mortgage.No_ERP);
- mortgageReq[17] = new SqlParameter("@How_Will_You_Repay_The_Mortgage", mortgage.How_Will_You_Repay_The_Mortgage);
- mortgageReq[18] = new SqlParameter("@IsActive", mortgage.IsActive);
- mortgageReq[19] = new SqlParameter("@IsDeleted", mortgage.IsDeleted);
- mortgageReq[20] = new SqlParameter("@AddDate", DateTime.Now);
- mortgageReq[21] = new SqlParameter("@EntryBy", mortgage.EntryBy);
- mortgageReq[22] = new SqlParameter("@UpdateDate", DateTime.Now);
- mortgageReq[23] = new SqlParameter("@UpdateBy", mortgage.UpdateBy);
- mortgageReq[24] = new SqlParameter("@ClientRegistrationID", mortgage.ClientRegistrationID);
- mortgageReq[25] = new SqlParameter("@result", SqlDbType.Int);
- mortgageReq[25].Direction = ParameterDirection.Output;
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, mortgageReq); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, mortgageReq); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(mortgageReq[24] != null ? mortgageReq[24].SqlValue.ToString() : "0");
- return completed;
- }
- public int GetRegistar(string Email, string Password, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed = 0;
- try
- {
- SqlParameter[] sqlParameter = new SqlParameter[3];
- sqlParameter[0] = new SqlParameter("@UserName", Email);
- sqlParameter[1] = new SqlParameter("@Password", Password);
- sqlParameter[2] = new SqlParameter("@ClientRegistrationID", SqlDbType.Int);
- sqlParameter[2].Direction = ParameterDirection.Output;
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "GetClientRegistrationID", sqlParameter);
- // return 1 if executed successfully
- completed = Convert.ToInt32(sqlParameter[2] != null ? sqlParameter[2].SqlValue.ToString() : "0");
- }
- catch { }
- return completed;
- }
- public DataSet GetClientRegistrationID(Int32 ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[1];
- Param[0] = new SqlParameter("@ClientRegistrationID", ClientRegistrationID);
- // commented on 20/10/2014
- // DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "ClientRegistrationID", Param);
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetClientRegistrationListV1", Param);
- return ds;
- }
- // get commitments
- public DataSet GetCommitments(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GelCommitmentList", Param);
- return ds;
- }
- public int DeleteCommitments(int id)
- {
- int result;
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@CurrentCommitmentsId",id)
- };
- result = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCommitments", Param);
- return result;
- }
- // get residential mortgage
- public DataSet GetResidentialMortgage(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetResidentialMortgage", Param);
- return ds;
- }
- public int DeleteResidentialMortgage(int id)
- {
- int completed = 0;
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ResidentialMortgageID",id)
- };
- completed = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteResidentialMortgage", Param);
- return completed;
- }
- // New Mortgage Property Details created on 19/01/2015
- public DataSet GetNewMortgagePropertyDetails(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetNewMortgagePropertyDetails", Param);
- return ds;
- }
- public int DeleteNewMortgagePropertyDetails(int id)
- {
- int result;
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ID",id)
- };
- result = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteNewMortgagePropertyDetails", Param);
- return result;
- }
- public DataSet GetNewMortgagePropertyAddress(int id)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@tblAdditional_Property_InfoID",id)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, "select * from tblAdditional_Property_Info where tblAdditional_Property_InfoID=@tblAdditional_Property_InfoID", Param);
- return ds;
- }
- // Investment Portfolio
- public DataSet GetInvestmentPortfolio(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetOtherMortgage", Param);
- return ds;
- }
- public void DeleteInvestmentPortfolio(int id)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@Other_MortgagesID",id)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteOtherMortgage", Param);
- }
- // current protection
- public DataSet GetCurrentProtection(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetCurrentProtection", Param);
- return ds;
- }
- public void DeleteCurrentProtection(int id)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@Current_Protection_ArrangementsID",id)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCurrentProtection", Param);
- }
- //// Employment Benefit 20-01-2015
- //public DataSet GetEmploymentBenefit(int ClientRegistrationID)
- //{
- // SqlParameter[] Param = new SqlParameter[]
- // {
- // new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- // };
- // DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetEmploymentBenefit", Param);
- // return ds;
- //}
- //public void DeleteEmploymentBenefit(int id)
- //{
- // SqlParameter[] Param = new SqlParameter[]
- // {
- // new SqlParameter("@EmploymentBenefitID",id)
- // };
- // SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteEmploymentBenefit", Param);
- //}
- // current pension arrangements
- public DataSet GetCurrentPensionInvestments(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetCurrentPensionInvestments", Param);
- return ds;
- }
- public void DeleteCurrentPensionInvestments(int id)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@tblCurrent_Pension_Investment_ArrangementsID",id)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCurrentPensionInvestments", Param);
- }
- public int UpdateMortgageFact()
- {
- return 0;
- }
- // update contactid into client registration
- public int UpdateContactIDToClient(int contactid, int clientregistrationid)
- {
- SqlParameter[] par = new SqlParameter[]
- {
- new SqlParameter("@ContactId",contactid),
- new SqlParameter("@ClientRegistrationID",clientregistrationid)
- };
- return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "proc_UpdateClientID", par);
- }
- // update contactid into client registration
- public int DeactivateClientRegistrationOnUpdateByClient(int contactid, int clientregistrationid)
- {
- SqlParameter[] par = new SqlParameter[]
- {
- new SqlParameter("@ContactId",contactid),
- new SqlParameter("@ClientRegistrationID",clientregistrationid)
- };
- return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "DeactivateClientRegistrationOnUpdateByClient", par);
- }
- public void DeleteAddress(int addressID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@FactFindAddressID",addressID)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteAddress", Param);
- }
- public void DeleteDependent(int dependentid)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@DependentID",dependentid)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteDependent", Param);
- }
- public DataSet GetApplicantsAddressForMortgage(int clientregistrationid)
- {
- SqlParameter[] par = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",clientregistrationid)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetApplcantsAddressesForMortgage", par);
- return ds;
- }
- public DataSet GetUser(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityUserDetail", Param);
- return ds;
- }
- public DataSet GetPropertyList(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityPropertyDetails", Param);
- return ds;
- }
- public DataSet GetPropertyAddressList(string postcode)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@postcode",postcode)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityAddressDetails", Param);
- return ds;
- }
- public DataSet GetNewlyAddedPropertyList(int MortgageId)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@MortgageId",MortgageId)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityPropertyDetailsByMortgageId", Param);
- return ds;
- }
- /***********************************************************************
- * Methods for Fact Find
- *
- *
- *
- *
- * Created On : 17/11/2014
- * *********************************************************************/
- public int FactFind_AddEmploymentAndIncome(tblEmploymentAndIncome employmentAndIncome, bool useTransaction, SqlTransaction tran, SqlConnection con)
- {
- int completed;
- string proc = "FactFind_InsertUpdateEmploymentAndIncome";
- SqlParameter[] empandInc = new SqlParameter[29];
- empandInc[0] = new SqlParameter("@ClientRegistrationID", employmentAndIncome.ClientRegistrationID);
- empandInc[1] = new SqlParameter("@JobTitle", employmentAndIncome.JobTitle);
- empandInc[2] = new SqlParameter("@NatureOfOccupation", employmentAndIncome.NatureOfOccupation);
- empandInc[3] = new SqlParameter("@NINumber", employmentAndIncome.NINumber);
- empandInc[4] = new SqlParameter("@AnticipatedRetirementAge", employmentAndIncome.AnticipatedRetirementAge);
- empandInc[5] = new SqlParameter("@EmploymentStatus", employmentAndIncome.EmploymentStatus);
- empandInc[6] = new SqlParameter("@ProbationPeriod", employmentAndIncome.ProbationPeriod);
- empandInc[7] = new SqlParameter("@NameOfEmployer", employmentAndIncome.NameOfEmployer);
- empandInc[8] = new SqlParameter("@AddressOfEmployer", employmentAndIncome.AddressOfEmployer);
- empandInc[9] = new SqlParameter("@AnnualCommission", employmentAndIncome.AnnualCommission);
- empandInc[10] = new SqlParameter("@AnnualOvertime", employmentAndIncome.AnnualOvertime);
- empandInc[11] = new SqlParameter("@Other", employmentAndIncome.Other);
- empandInc[12] = new SqlParameter("@GrossTotal", employmentAndIncome.GrossTotal);
- empandInc[13] = new SqlParameter("@GrossMonthly", employmentAndIncome.GrossMonthly);
- empandInc[14] = new SqlParameter("@IsActive", employmentAndIncome.IsActive);
- empandInc[15] = new SqlParameter("@IsDeleted", employmentAndIncome.IsDeleted);
- empandInc[16] = new SqlParameter("@AddDate", DateTime.Now);
- empandInc[17] = new SqlParameter("@EntryBy", employmentAndIncome.EntryBy);
- empandInc[18] = new SqlParameter("@UpdateDate", DateTime.Now);
- empandInc[19] = new SqlParameter("@UpdateBy", employmentAndIncome.UpdateBy);
- empandInc[20] = new SqlParameter("@GrossBasicSalary", employmentAndIncome.GrossBasicSalary);
- empandInc[21] = new SqlParameter("@PeriodWithEmployer", employmentAndIncome.PeriodWithEmployer);
- empandInc[22] = new SqlParameter("@result", SqlDbType.Int);
- empandInc[22].Direction = ParameterDirection.Output;
- // add id column
- empandInc[23] = new SqlParameter("@EmploymentIncomeId", employmentAndIncome.EmploymentIncomeId);
- empandInc[24] = new SqlParameter("@ReferId", employmentAndIncome.ReferId);
- // bonus
- empandInc[25] = new SqlParameter("@BonusY1", employmentAndIncome.BonusY1);
- empandInc[26] = new SqlParameter("@BonusY2", employmentAndIncome.BonusY2);
- empandInc[27] = new SqlParameter("@BonusY3", employmentAndIncome.BonusY3);
- empandInc[28] = new SqlParameter("@PreviousAddressOfEmployer", employmentAndIncome.PreviousAddressOfEmployer);
- if (useTransaction)
- { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empandInc); }
- else
- { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empandInc); }
- // return 1 if executed successfully
- completed = Convert.ToInt32(empandInc[22] != null ? empandInc[22].SqlValue.ToString() : "0");
- return completed;
- }
- // get commitments
- public DataSet GetSelfEmployment(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@clientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetSelfEmployed", Param);
- return ds;
- }
- public void DeleteSelfEmployment(int id, string TypeOfEmployment)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@selfEmployedId",id),
- new SqlParameter("@typeOfEmployment",TypeOfEmployment)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteSelfEmployed", Param);
- }
- public DataSet GetEmploymentAndIncome(int ClientRegistrationID)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@clientRegistrationID",ClientRegistrationID)
- };
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetEmploymentAndIncome", Param);
- return ds;
- }
- public void DeleteEmploymentIncome(int id)
- {
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@employmentAndIncomeId",id)
- };
- SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteEmploymentAndIncome", Param);
- }
- //end block DeleteEmploymentIncome
- // 02 06 2015
- public List<tblUnEmployment> GetUnEmploymentList(int ClientRegistrationID, int UnEmploymentID)
- {
- string command = "";
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID) ,
- new SqlParameter("@UnEmploymentID",UnEmploymentID)
- };
- if (UnEmploymentID == 0)
- {
- command = "select * from tblUnEmployment where ClientRegistrationID=@ClientRegistrationID";
- }
- else
- {
- command = "select * from tblUnEmployment where ClientRegistrationID=@ClientRegistrationID and UnEmploymentID=@UnEmploymentID";
- }
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
- return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblUnEmployment>(ds.Tables[0]).ToList<tblUnEmployment>();
- }
- public List<tblSelfEmployed> GetSelfEmployedList(int ClientRegistrationID, int SelfEmployedId)
- {
- string command = "";
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID) ,
- new SqlParameter("@SelfEmployedId",SelfEmployedId)
- };
- if (SelfEmployedId == 0)
- {
- command = "select * from tblSelfEmployed where ClientRegistrationID=@ClientRegistrationID";
- }
- else
- {
- command = "select * from tblSelfEmployed where ClientRegistrationID=@ClientRegistrationID and SelfEmployedId=@SelfEmployedId";
- }
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
- return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblSelfEmployed>(ds.Tables[0]).ToList<tblSelfEmployed>();
- }
- public int AddUpdateUnEmployed(tblUnEmployment model)
- {
- var command = "";
- int result;
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@UnEmploymentID",model.UnEmploymentID) ,
- new SqlParameter("@ClientRegistrationID",model.ClientRegistrationID) ,
- new SqlParameter("@DateOfUnEmployment",!string.IsNullOrWhiteSpace(model.StrDateOfUnemployment) ? Helper.GetDate(model.StrDateOfUnemployment) : null),
- new SqlParameter("@DateOfReemployment",!string.IsNullOrWhiteSpace(model.StrDateOfReemployment) ? Helper.GetDate(model.StrDateOfReemployment) : null),
- new SqlParameter("@ReasonForUnemployment",model.ReasonForUnemployment),
- new SqlParameter("@IsActive",true) ,
- new SqlParameter("@UpdatedDate",DateTime.Now),
- new SqlParameter("@UpdatedBy",""),
- // new SqlParameter("@Retired", !string.IsNullOrWhiteSpace(model.Retired) ? model.Retired : "no value")
- new SqlParameter("@Retired", model.Retired)
- };
- if (model.UnEmploymentID == 0)
- {
- command = @"insert into
- tblUnEmployment
- (
- ClientRegistrationID,
- DateOfUnEmployment,
- DateOfReemployment,
- ReasonForUnemployment,
- IsActive,
- UpdatedDate,
- UpdatedBy,
- Retired
- )
- values
- (
- @ClientRegistrationID,
- @DateOfUnEmployment,
- @DateOfReemployment,
- @ReasonForUnemployment,
- @IsActive,
- @UpdatedDate,
- @UpdatedBy,
- @Retired
- )";
- }
- else
- {
- command = @"update tblUnEmployment
- set
- ClientRegistrationID= @ClientRegistrationID,
- DateOfUnEmployment=@DateOfUnEmployment,
- DateOfReemployment=@DateOfReemployment,
- ReasonForUnemployment=@ReasonForUnemployment,
- IsActive= @IsActive,
- UpdatedDate=@UpdatedDate,
- UpdatedBy= @UpdatedBy,
- Retired = @Retired
- where UnEmploymentID= @UnEmploymentID
- ";
- }
- result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
- return result;
- }
- public int AddUpdateSelfEmployed(tblSelfEmployed model)
- {
- var command = "";
- int result;
- SqlParameter[] param = new SqlParameter[]
- {
- new SqlParameter("@SelfEmployedId",model.SelfEmployedId),
- new SqlParameter("@ClientRegistrationID",model.ClientRegistrationID),
- new SqlParameter("@TypeOfSelfEmployment",model.TypeOfSelfEmployment),
- new SqlParameter("@NameOfComapany",model.NameOfComapany),
- new SqlParameter("@Shareholding",model.Shareholding),
- new SqlParameter("@Position",model.Position),
- new SqlParameter("@IncorporationDate", model.IncorporationDate), // TODO : casting
- new SqlParameter("@IsActive",true),
- new SqlParameter("@AddDate",DateTime.Now),
- new SqlParameter("@UpdateDate",null),
- new SqlParameter("@OtherIncomeInfo",model.OtherIncomeInfo),
- new SqlParameter("@AccountantFName",model.AccountantFName),
- new SqlParameter("@AccountantIContact",model.AccountantIContact),
- new SqlParameter("@AccountantPhone",model.AccountantPhone),
- new SqlParameter("@AccountantFAX",model.AccountantFAX),
- new SqlParameter("@AccountantQualification",model.AccountantQualification),
- new SqlParameter("@TurnoverY1",model.TurnoverY1),
- new SqlParameter("@TurnoverY2",model.TurnoverY2),
- new SqlParameter("@TurnoverY3",model.TurnoverY3),
- new SqlParameter("@NetProfitY1",model.NetProfitY1),
- new SqlParameter("@NetProfitY2",model.NetProfitY2),
- new SqlParameter("@NetProfitY3",model.NetProfitY3),
- new SqlParameter("@SalaryY1",model.SalaryY1),
- new SqlParameter("@SalaryY2",model.SalaryY2),
- new SqlParameter("@SalaryY3",model.SalaryY3),
- new SqlParameter("@DividendsY1",model.DividendsY1),
- new SqlParameter("@DividendsY2",model.DividendsY2),
- new SqlParameter("@DividendsY3",model.DividendsY3),
- new SqlParameter("@DateOfUnemployment",model.DateOfReemployment), // TODO : casting
- new SqlParameter("@DateOfReemployment",model.DateOfReemployment), // TODO : casting
- new SqlParameter("@ReasonForUnemployment",model.ReasonForUnemployment),
- new SqlParameter("@FirmName",model.FirmName),
- new SqlParameter("@IndividualContact",model.IndividualContact),
- new SqlParameter("@PhoneNo",model.PhoneNo),
- new SqlParameter("@Fax",model.Fax),
- new SqlParameter("@Qualification",model.Qualification)
- };
- if (model.SelfEmployedId == 0)
- {
- command = @"INSERT INTO [dbo].[tblSelfEmployed]
- ([ClientRegistrationID]
- ,[TypeOfSelfEmployment]
- ,[NameOfComapany]
- ,[Shareholding]
- ,[Position]
- ,[IncorporationDate]
- ,[IsActive]
- ,[AddDate]
- ,[UpdateDate]
- ,[OtherIncomeInfo]
- ,[AccountantFName]
- ,[AccountantIContact]
- ,[AccountantPhone]
- ,[AccountantFAX]
- ,[AccountantQualification]
- ,[TurnoverY1]
- ,[TurnoverY2]
- ,[TurnoverY3]
- ,[NetProfitY1]
- ,[NetProfitY2]
- ,[NetProfitY3]
- ,[SalaryY1]
- ,[SalaryY2]
- ,[SalaryY3]
- ,[DividendsY1]
- ,[DividendsY2]
- ,[DividendsY3]
- ,[DateOfUnemployment]
- ,[DateOfReemployment]
- ,[ReasonForUnemployment]
- ,[FirmName]
- ,[IndividualContact]
- ,[PhoneNo]
- ,[Fax]
- ,[Qualification])
- VALUES
- (
- @ClientRegistrationID
- ,@TypeOfSelfEmployment
- ,@NameOfComapany
- ,@Shareholding
- ,@Position
- ,@IncorporationDate
- ,@IsActive
- ,@AddDate
- ,@UpdateDate
- ,@OtherIncomeInfo
- ,@AccountantFName
- ,@AccountantIContact
- ,@AccountantPhone
- ,@AccountantFAX
- ,@AccountantQualification
- ,@TurnoverY1
- ,@TurnoverY2
- ,@TurnoverY3
- ,@NetProfitY1
- ,@NetProfitY2
- ,@NetProfitY3
- ,@SalaryY1
- ,@SalaryY2
- ,@SalaryY3
- ,@DividendsY1
- ,@DividendsY2
- ,@DividendsY3
- ,@DateOfUnemployment
- ,@DateOfReemployment
- ,@ReasonForUnemployment
- ,@FirmName
- ,@IndividualContact
- ,@PhoneNo
- ,@Fax
- ,@Qualification
- )";
- }
- else
- {
- command = @"update tblSelfEmployed
- set [ClientRegistrationID] = @ClientRegistrationID
- ,[TypeOfSelfEmployment]=@TypeOfSelfEmployment
- ,[NameOfComapany] =@NameOfComapany
- ,[Shareholding] =@Shareholding
- ,[Position] =@Position
- ,[IncorporationDate] =@IncorporationDate
- ,[IsActive] =@IsActive
- ,[AddDate] =@AddDate
- ,[UpdateDate] =@UpdateDate
- ,[OtherIncomeInfo] =@OtherIncomeInfo
- ,[AccountantFName] =@AccountantFName
- ,[AccountantIContact] =@AccountantIContact
- ,[AccountantPhone] =@AccountantPhone
- ,[AccountantFAX] =@AccountantFAX
- ,[AccountantQualification]=@AccountantQualification
- ,[TurnoverY1] =@TurnoverY1
- ,[TurnoverY2] =@TurnoverY2
- ,[TurnoverY3] =@TurnoverY3
- ,[NetProfitY1]=@NetProfitY1
- ,[NetProfitY2]=@NetProfitY2
- ,[NetProfitY3]=@NetProfitY3
- ,[SalaryY1] =@SalaryY1
- ,[SalaryY2] =@SalaryY2
- ,[SalaryY3]=@SalaryY3
- ,[DividendsY1]=@DividendsY1
- ,[DividendsY2]=@DividendsY2
- ,[DividendsY3]=@DividendsY3
- ,[DateOfUnemployment] =@DateOfUnemployment
- ,[DateOfReemployment]=@DateOfReemployment
- ,[ReasonForUnemployment] =@ReasonForUnemployment
- ,[FirmName]=@FirmName
- ,[IndividualContact]=@IndividualContact
- ,[PhoneNo] =@PhoneNo
- ,[Fax] =@Fax
- ,[Qualification] =@Qualification
- where SelfEmployedId= @SelfEmployedId";
- }
- result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, param);
- return result;
- }
- public int DeleteUnEmployed(int UnEmploymentID)
- {
- int result;
- SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@UnEmploymentID", UnEmploymentID) };
- var command = "delete from tblUnEmployment where UnEmploymentID = @UnEmploymentID";
- result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
- return result;
- }
- public int DeleteEmployed(int EmploymentAndIncomeID)
- {
- int result;
- SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@EmploymentAndIncomeID", EmploymentAndIncomeID) };
- var command = "delete from tblEmploymentAndIncome where EmploymentIncomeID = @EmploymentAndIncomeID";
- result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
- return result;
- }
- public int DeleteSelfEmployed(int SelfEmployedId)
- {
- int result;
- SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@SelfEmployedId", SelfEmployedId) };
- var command = "delete from tblSelfEmployed where SelfEmployedId = @SelfEmployedId";
- result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
- return result;
- }
- // employed [employment and income]
- public List<tblEmploymentAndIncome> GetEmployedList(int ClientRegistrationID, int EmploymentAndIncomeID)
- {
- string command = "";
- SqlParameter[] Param = new SqlParameter[]
- {
- new SqlParameter("@ClientRegistrationID",ClientRegistrationID) ,
- new SqlParameter("@EmploymentAndIncomeID",EmploymentAndIncomeID)
- };
- if (EmploymentAndIncomeID == 0)
- {
- command = "select * from tblEmploymentAndIncome where ClientRegistrationID=@ClientRegistrationID";
- }
- else
- {
- command = "select * from tblEmploymentAndIncome where ClientRegistrationID=@ClientRegistrationID and EmploymentIncomeID=@EmploymentAndIncomeID";
- }
- DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
- return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblEmploymentAndIncome>(ds.Tables[0]).ToList<tblEmploymentAndIncome>();
- }
- }
- /*****************************************************
- DataTable helper for MortgageFactFind
- Created On : 20/10/2014
- Ref :http://www.mroma.net/blog/c-helper-functions-to-map-a-datatable-or-datarow-to-a-class-object/
- Modified for Defalut listing objects
- ******************************************************/
- public static class DataTableHelper
- {
- // function that set the given object from the given data row
- public static void SetItemFromRow<T>(T item, DataRow row)
- where T : new()
- {
- // go through each column
- foreach (DataColumn c in row.Table.Columns)
- {
- // find the property for the column
- PropertyInfo p = item.GetType().GetProperty(c.ColumnName);
- // if exists, set the value
- if (p != null && row[c] != DBNull.Value)
- {
- p.SetValue(item, row[c], null);
- }
- }
- }
- // function that creates an object from the given data row
- public static T CreateItemFromRow<T>(DataRow row)
- where T : new()
- {
- // create a new object
- T item = new T();
- // set the item
- SetItemFromRow(item, row);
- // return
- return item;
- }
- // function that creates a list of an object from the given data table only for fact find (don't change)
- public static List<T> CreateListFromTable<T>(DataTable tbl)
- where T : new()
- {
- // define return list
- List<T> lst = new List<T>();
- // alocal variable
- int count = 0;
- // go through each row
- foreach (DataRow r in tbl.Rows)
- {
- // add to the list
- lst.Add(CreateItemFromRow<T>(r));
- count++;
- }
- // set defaults list with 2 members
- if (count == 0)
- { lst = new List<T> { new T(), new T() }; }
- else if (count == 1)
- { lst.Add(new T()); }
- // return the list
- return lst;
- }
- // function that creates a list of an object from the given data table other than fact find
- public static List<T> CreateListFromTableForAll<T>(DataTable tbl)
- where T : new()
- {
- // define return list
- List<T> lst = new List<T>();
- // alocal variable
- int count = 0;
- // go through each row
- foreach (DataRow r in tbl.Rows)
- {
- // add to the list
- lst.Add(CreateItemFromRow<T>(r));
- count++;
- }
- // return the list
- return lst;
- }
- }
- }
Add Comment
Please, Sign In to add comment