API tools faq
paste
Guest User

Untitled

a guest
Nov 16th, 2016
38
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 321.58 KB | None | 0 0
raw download clone embed print report
  1. public class Q_Application
  2. {
  3. #region [Declarations]
  4.  
  5. public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
  6. System.Web.HttpContext con = HttpContext.Current;
  7.  
  8. #endregion [Declarations]
  9.  
  10. #region Find Broker for logged in user
  11. /// <summary>
  12. /// Getting Finance Type
  13. /// </summary>
  14. /// <returns></returns>
  15. public List<tblfinancetype> GetFinanceType()
  16. {
  17. List<tblfinancetype> financetype = new List<tblfinancetype>();
  18. try
  19. {
  20. financetype = ctx.tblfinancetypes.ToList();
  21. return financetype;
  22. }
  23. catch (Exception ex)
  24. {
  25. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetFinanceType", ex.Message);
  26. return financetype;
  27. }
  28. }
  29. #endregion
  30.  
  31. #region Check Whether the status of the product is changed or not
  32. /// <summary>
  33. /// Checking Status Update
  34. /// </summary>
  35. /// <param name="financeId"></param>
  36. /// <param name="newStatusId"></param>
  37. /// <param name="oldStatusId"></param>
  38. /// <returns></returns>
  39. public bool CheckStatusUpdate(int financeId, int newStatusId, out int oldStatusId)
  40. {
  41. bool _chk = false;
  42. oldStatusId = 0;
  43. try
  44. {
  45. var _oldStatusId = 0;
  46. var oldstatusIddata = ctx.tblfinances.Where(c => c.financeid == financeId).FirstOrDefault();
  47. if (oldstatusIddata != null)
  48. {
  49. _oldStatusId = oldstatusIddata.status ?? 0;
  50. oldStatusId = _oldStatusId;
  51. }
  52.  
  53. if (_oldStatusId != newStatusId)
  54. {
  55. _chk = true;
  56. }
  57.  
  58. }
  59. catch (Exception ex)
  60. {
  61. Helper.ErrorLog(ex.InnerException, "Q_Application", "CheckStatusUpdate", ex.Message);
  62. }
  63. return _chk;
  64. }
  65. #endregion
  66.  
  67. #region Find Filter By Status According to ApplicationId
  68. /// <summary>
  69. /// Find Filter by status
  70. /// </summary>
  71. /// <param name="applicationid"></param>
  72. /// <returns></returns>
  73. public List<tblfinancestatu> FindFilterByStatus(int applicationid)
  74. {
  75. var cntlists = ctx.tblfinancestatus.Where(c => c.type == applicationid).ToList();
  76. return cntlists;
  77. }
  78. /// <summary>
  79. /// Getting Filter by statuss
  80. /// </summary>
  81. /// <param name="applicationId"></param>
  82. /// <param name="account"></param>
  83. /// <param name="broker"></param>
  84. /// <returns></returns>
  85. public List<tblfinancestatu> GetFilterByStatus(int applicationId, int account, int broker)
  86. {
  87. List<tblfinancestatu> fstatus = new List<tblfinancestatu>();
  88. SqlParameter[] Param = new SqlParameter[3];
  89. Param[0] = new SqlParameter("@applicationId", applicationId);
  90. Param[1] = new SqlParameter("@account", account);
  91. Param[2] = new SqlParameter("@broker", broker);
  92. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetFilterByStatus", Param);
  93.  
  94. fstatus = (from DataRow row in dt.Rows
  95. select new tblfinancestatu
  96. {
  97. type = Convert.ToInt32(((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]).ToString()),
  98. statusid = Convert.ToInt32(((row["statusid"] == DBNull.Value || row["statusid"] == null) ? "0" : row["statusid"]).ToString()),
  99. name = ((row["name"] == DBNull.Value || row["name"] == null) ? " " : row["name"]).ToString()
  100. }).ToList();
  101. return fstatus;
  102. }
  103. /// <summary>
  104. /// Get Finances list by type
  105. /// </summary>
  106. /// <param name="financetype"></param>
  107. /// <param name="userid"></param>
  108. /// <returns></returns>
  109. public List<tblfinance> getFinancesListByType(int financetype = 1, int userid = 0)
  110. {
  111. var data = ctx.tblfinances.Where(c => c.type == financetype && c.status.Value != 34 && c.status.Value != 35 && c.status.Value != 36 && c.status.Value != 37 && c.status.Value != 38).ToList();
  112.  
  113. if (data != null)
  114. if (this.IsAdmin(userid))
  115. data = data.Where(x => x.type.Value == financetype).ToList();
  116. else
  117. data = data.Where(x => x.type.Value == financetype && x.tblcontact.broker == userid).ToList();
  118.  
  119. return data;
  120. }
  121. /// <summary>
  122. /// Getting Finances
  123. /// </summary>
  124. /// <param name="financetype"></param>
  125. /// <returns></returns>
  126. public List<tblfinance> getFinances(int financetype = 1)
  127. {
  128. return ctx.tblfinances.Where(c => c.type == financetype && c.status.Value != 34 && c.status.Value != 35 && c.status.Value != 36 && c.status.Value != 37 && c.status.Value != 38).ToList();
  129. }
  130. /// <summary>
  131. /// Counting Finance Type
  132. /// </summary>
  133. /// <param name="type"></param>
  134. /// <param name="status"></param>
  135. /// <param name="userid"></param>
  136. /// <returns></returns>
  137. public int countFinancetype(int type, int status, int userid)
  138. {
  139. var data = ctx.tblfinances.Where(c => c.type == type && c.status.Value != 34 && c.status.Value != 35 && c.status.Value != 36 && c.status.Value != 37 && c.status.Value != 38).GroupBy(x => x.contact).SelectMany(x => x.Take(1)).ToList();
  140. if (data != null)
  141. if (this.IsAdmin(userid))
  142. return data.Where(x => x.type.Value == type && x.status.Value == status).Count();
  143. else
  144. return data.Where(x => x.type.Value == type && x.status.Value == status && x.tblcontact.broker == userid).Count();
  145. else
  146. return 0;
  147. }
  148. /// <summary>
  149. /// Getting List of finance type count
  150. /// </summary>
  151. /// <param name="type"></param>
  152. /// <param name="userid"></param>
  153. /// <returns></returns>
  154. public List<tblfinance> ListcountFinancetype(int type, int userid)
  155. {
  156. var data = ctx.tblfinances.Where(c => c.type == type && c.status.Value != 34 && c.status.Value != 35 && c.status.Value != 36 && c.status.Value != 37 && c.status.Value != 38).GroupBy(x => x.contact).SelectMany(x => x.Take(1)).ToList();
  157. if (data != null)
  158. if (this.IsAdmin(userid))
  159. data = data.Where(x => x.type.Value == type).ToList();
  160. else
  161. data = data.Where(x => x.type.Value == type && x.tblcontact.broker == userid).ToList();
  162.  
  163.  
  164. return data;
  165. }
  166. /// <summary>
  167. /// Getting count method for Finance type on load
  168. /// </summary>
  169. /// <param name="type"></param>
  170. /// <param name="status"></param>
  171. /// <param name="userid"></param>
  172. /// <param name="loggedinuserId"></param>
  173. /// <returns></returns>
  174. public int countFinancetypeonLoad(int type, int status, int userid, int loggedinuserId)
  175. {
  176. var data = ctx.tblfinances.GroupBy(x => x.contact).SelectMany(x => x.Take(1)).ToList();
  177. if (data != null)
  178. if (this.IsAdmin(userid))
  179. return data.Where(x => x.type.Value == type && x.status.Value == status && (x.followup == null || x.followup.Value < System.DateTime.Now)).Count();
  180. else
  181. return data.Where(x => x.type.Value == type && x.status.Value == status && x.tblcontact.account == userid && (x.followup == null || x.followup.Value < System.DateTime.Now)).Count();
  182. else
  183. return 0;
  184. }
  185.  
  186. #endregion
  187.  
  188. #region Get all lead from database
  189. /// <summary>
  190. /// Getting All lead
  191. /// </summary>
  192. /// <param name="ListStatusCountData"></param>
  193. /// <param name="accountid"></param>
  194. /// <param name="userId"></param>
  195. /// <param name="type"></param>
  196. /// <param name="SearchText"></param>
  197. /// <param name="ClientId"></param>
  198. /// <param name="BrokerId"></param>
  199. /// <param name="Status"></param>
  200. /// <param name="Lender"></param>
  201. /// <param name="Solicitor"></param>
  202. /// <param name="isHideNotRequire"></param>
  203. /// <returns></returns>
  204. public IQueryable<ApplicationLead> GetAllLead(out List<StatusCount> ListStatusCountData, int accountid, int userId, int type = 1, string SearchText = "", int ClientId = 0, string BrokerId = "", string Status = "", string Lender = "", string Solicitor = "", int isHideNotRequire = 1,string substatus = "",int financeId=0)
  205. {
  206. Q_Application _obj = new Q_Application();
  207. IQueryable<ApplicationLead> leadData = null;
  208. ListStatusCountData = new List<StatusCount>();
  209.  
  210.  
  211. int teamId = 0;
  212. string brokersIds = "";
  213. int _userType = 0;
  214. int _accountId = 0;
  215. int ID = 0;
  216. _userType = _obj.CheckUserType(userId, out teamId, out _accountId, out brokersIds);
  217.  
  218.  
  219. if (_userType == (int)UserType.GeneralUser) // General user
  220. ID = accountid;
  221. else if (_userType == (int)UserType.Admin) // Admin
  222. ID = accountid;
  223. else if (_userType == (int)UserType.Manager)// Manager
  224. ID = teamId;
  225. else if (_userType == (int)UserType.Broker) // Broker
  226. ID = userId;
  227.  
  228.  
  229. SqlParameter[] Param = new SqlParameter[12];
  230. Param[0] = new SqlParameter("@UserType", _userType);
  231. Param[1] = new SqlParameter("@ID", ID);
  232. Param[2] = new SqlParameter("@Type", type);
  233. Param[3] = new SqlParameter("@isHideNotRequire", isHideNotRequire);
  234.  
  235. if (!string.IsNullOrWhiteSpace(SearchText))
  236. Param[4] = new SqlParameter("@SearchText", SearchText);
  237.  
  238. if (ClientId > 0)
  239. Param[5] = new SqlParameter("@ClientId", ClientId);
  240.  
  241. if (!string.IsNullOrWhiteSpace(BrokerId))
  242. Param[6] = new SqlParameter("@BrokerId", BrokerId);
  243.  
  244. if (!string.IsNullOrWhiteSpace(Status))
  245. Param[7] = new SqlParameter("@Status", Status);
  246.  
  247. if (!string.IsNullOrWhiteSpace(Lender))
  248. Param[8] = new SqlParameter("@Lender", Lender);
  249.  
  250. if (!string.IsNullOrWhiteSpace(Solicitor))
  251. Param[9] = new SqlParameter("@Solicitor", Solicitor);
  252.  
  253. if (!string.IsNullOrWhiteSpace(substatus))
  254. Param[10] = new SqlParameter("@SubStatus", substatus);
  255. if (financeId > 0)
  256. Param[11] = new SqlParameter("@FinanceId", financeId);
  257.  
  258. DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAllAppData_Update", Param);
  259.  
  260. if (ds.Tables.Count > 0)
  261. {
  262. leadData = (from DataRow row in ds.Tables[0].Rows
  263. select new ApplicationLead()
  264. {
  265. AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
  266. financeid = Convert.ToInt32(((row["financeid"] == DBNull.Value || row["financeid"] == null) ? "0" : row["financeid"]).ToString()),
  267. clientId = Convert.ToInt32(((row["clientId"] == DBNull.Value || row["clientId"] == null) ? "0" : row["clientId"]).ToString()),
  268. contactId = Convert.ToInt32(((row["contactId"] == DBNull.Value || row["contactId"] == null) ? "0" : row["contactId"]).ToString()),
  269. brokerId = Convert.ToInt32(((row["brokerId"] == DBNull.Value || row["brokerId"] == null) ? "0" : row["brokerId"]).ToString()),
  270. brokerName = ((row["brokerName"] == DBNull.Value || row["brokerName"] == null) ? " " : row["brokerName"]).ToString(),
  271. clientname = ((row["clientname"] == DBNull.Value || row["clientname"] == null) ? " " : row["clientname"]).ToString(),
  272. firstName = ((row["firstName"] == DBNull.Value || row["firstName"] == null) ? " " : row["firstName"]).ToString(),
  273. lastName = ((row["lastName"] == DBNull.Value || row["lastName"] == null) ? " " : row["lastName"]).ToString(),
  274. Add = (row["Add"] == DBNull.Value || row["Add"] == null) ? (DateTime?)null : Convert.ToDateTime(row["Add"]),
  275. Updated = (row["Updated"] == DBNull.Value || row["Updated"] == null) ? (DateTime?)null : Convert.ToDateTime(row["Updated"]),
  276. contact = (row["contact"] == DBNull.Value || row["contact"] == null) ? (DateTime?)null : Convert.ToDateTime(row["contact"]),
  277. followUp = (row["followUp"] == DBNull.Value || row["followUp"] == null) ? (DateTime?)null : Convert.ToDateTime(row["followUp"]),
  278. email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
  279. tel = ((row["tel"] == DBNull.Value || row["tel"] == null) ? " " : row["tel"]).ToString(),
  280. sla = ((row["sla"] == DBNull.Value || row["sla"] == null) ? " " : row["sla"]).ToString(),
  281. lead = ((row["lead"] == DBNull.Value || row["lead"] == null) ? " " : row["lead"]).ToString(),
  282. neg = ((row["neg"] == DBNull.Value || row["neg"] == null) ? " " : row["neg"]).ToString(),
  283. branch = ((row["branch"] == DBNull.Value || row["branch"] == null) ? " " : row["branch"]).ToString(),
  284. status = Convert.ToInt32(((row["status"] == DBNull.Value || row["status"] == null) ? "0" : row["status"]).ToString()),
  285. type = Convert.ToInt32(((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]).ToString()),
  286. StatusName = ((row["StatusName"] == DBNull.Value || row["StatusName"] == null) ? " " : row["StatusName"]).ToString(),
  287. Lender = ((row["Lender"] == DBNull.Value || row["Lender"] == null) ? " " : row["Lender"]).ToString(),
  288. Soliciter = ((row["Soliciter"] == DBNull.Value || row["Soliciter"] == null) ? " " : row["Soliciter"]).ToString(),
  289. BrokerEmail = ((row["BrokerEmail"] == DBNull.Value || row["BrokerEmail"] == null) ? " " : row["BrokerEmail"]).ToString(),
  290. PolicyNumber = ((row["PolicyNumber"] == DBNull.Value || row["PolicyNumber"] == null) ? " " : row["PolicyNumber"]).ToString(),
  291. TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
  292. OtherApplicantEmail = ((row["OtherApplicantEmail"] == DBNull.Value || row["OtherApplicantEmail"] == null) ? " " : row["OtherApplicantEmail"]).ToString(),
  293. OtherApplicantId = Convert.ToInt32(((row["OtherApplicantId"] == DBNull.Value || row["OtherApplicantId"] == null) ? "0" : row["OtherApplicantId"]).ToString()),
  294. OtherApplicantFname = ((row["OtherApplicantFname"] == DBNull.Value || row["OtherApplicantFname"] == null) ? " " : row["OtherApplicantFname"]).ToString(),
  295. OtherApplicantMobile = ((row["OtherApplicantMobile"] == DBNull.Value || row["OtherApplicantMobile"] == null) ? " " : row["OtherApplicantMobile"]).ToString(),
  296. OtherApplicantSName = ((row["OtherApplicantSName"] == DBNull.Value || row["OtherApplicantSName"] == null) ? " " : row["OtherApplicantSName"]).ToString(),
  297. AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
  298. statusinterval = Convert.ToInt32(((row["statusinterval"] == DBNull.Value || row["statusinterval"] == null) ? 0 : row["statusinterval"]).ToString()),
  299. RecurrenceID = !string.IsNullOrEmpty(row["RecurrenceID"].ToString()) ? Convert.ToInt32(row["RecurrenceID"].ToString()) : new Nullable<int>(),
  300. RecurrenceException = !string.IsNullOrEmpty(row["RecurrenceException"].ToString()) ? row["RecurrenceException"].ToString() : null,
  301. RecurrenceRule = !string.IsNullOrEmpty(row["RecurrenceRule"].ToString()) ? row["RecurrenceRule"].ToString() : null,
  302. RID = null,
  303. PrimaryContact = ((row["PrimaryContact"] == DBNull.Value || row["PrimaryContact"] == null) ? " " : row["PrimaryContact"]).ToString(),
  304. SecondaryContact = ((row["SecondaryContact"] == DBNull.Value || row["SecondaryContact"] == null) ? " " : row["SecondaryContact"]).ToString(),
  305. Address1 = ((row["Address1"] == DBNull.Value || row["Address1"] == null) ? " " : row["Address1"]).ToString(),
  306. Address2 = ((row["Address2"] == DBNull.Value || row["Address2"] == null) ? " " : row["Address2"]).ToString(),
  307. OtherDetails = ((row["OtherDetails"] == DBNull.Value || row["OtherDetails"] == null) ? " " : row["OtherDetails"]).ToString(),
  308. Title = ((row["Title"] == DBNull.Value || row["Title"] == null) ? " " : row["Title"]).ToString(),
  309. FAX = ((row["FAX"] == DBNull.Value || row["FAX"] == null) ? " " : row["FAX"]).ToString(),
  310. Qualification = ((row["Qualification"] == DBNull.Value || row["Qualification"] == null) ? " " : row["Qualification"]).ToString(),
  311. LastNote = ((row["LastNote"] == DBNull.Value || row["LastNote"] == null) ? " " : row["LastNote"]).ToString(),
  312. FinanceAddress = ((row["FinanceAddress"] == DBNull.Value || row["FinanceAddress"] == null) ? " " : row["FinanceAddress"]).ToString(),
  313. SubStatusName = ((row["SubStatusName"] == DBNull.Value || row["SubStatusName"] == null) ? "" : row["SubStatusName"]).ToString(),
  314. SubStatusId = Convert.ToInt32(((row["SubStatusId"] == DBNull.Value || row["SubStatusId"] == null) ? "0" : row["status"]).ToString()),
  315. }).AsQueryable();
  316.  
  317. }
  318.  
  319. if (ds.Tables.Count > 1)
  320. {
  321. ListStatusCountData = (from DataRow row in ds.Tables[1].Rows
  322. select new StatusCount()
  323. {
  324. Type = Convert.ToInt32(((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]).ToString()),
  325. StatusId = Convert.ToInt32(((row["statusid"] == DBNull.Value || row["statusid"] == null) ? "0" : row["statusid"]).ToString()),
  326. TotalCount = Convert.ToInt32(((row["TotalCount"] == DBNull.Value || row["TotalCount"] == null) ? "0" : row["TotalCount"]).ToString()),
  327. StatusName = ((row["name"] == DBNull.Value || row["name"] == null) ? " " : row["name"]).ToString()
  328.  
  329. }).ToList();
  330. }
  331.  
  332. List<StatusCount> ListSubStatusCountData = new List<StatusCount>();
  333.  
  334. if (ds.Tables.Count > 2)
  335. {
  336. ListSubStatusCountData = (from DataRow row in ds.Tables[2].Rows
  337. select new StatusCount()
  338. {
  339. Type = Convert.ToInt32(((row["StatusId"] == DBNull.Value || row["StatusId"] == null) ? "0" : row["StatusId"]).ToString()),
  340. StatusId = Convert.ToInt32(((row["SubstatusId"] == DBNull.Value || row["SubstatusId"] == null) ? "0" : row["SubstatusId"]).ToString()),
  341. TotalCount = Convert.ToInt32(((row["ToTalCount"] == DBNull.Value || row["ToTalCount"] == null) ? "0" : row["ToTalCount"]).ToString()),
  342. StatusName = ((row["Substatusname"] == DBNull.Value || row["Substatusname"] == null) ? " " : row["Substatusname"]).ToString()
  343.  
  344. }).ToList();
  345. }
  346.  
  347. HttpContext.Current.Session["ListSubStatusCountData"] = ListSubStatusCountData;
  348.  
  349. return leadData;
  350. }
  351.  
  352.  
  353. /// <summary>
  354. /// GetAll Save Events
  355. /// </summary>
  356. /// <param name="accountid"></param>
  357. /// <param name="userId"></param>
  358. /// <returns></returns>
  359. public IQueryable<ApplicationLead> RecurredSavedEvent()
  360. {
  361. IQueryable<ApplicationLead> leadData = null;
  362. DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAllAppDataWithSaveEvents_Update");
  363. if (ds.Tables.Count > 0)
  364. {
  365. leadData = (from DataRow row in ds.Tables[0].Rows
  366. select new ApplicationLead()
  367. {
  368. AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
  369. financeid = Convert.ToInt32(((row["financeid"] == DBNull.Value || row["financeid"] == null) ? "0" : row["financeid"]).ToString()),
  370. clientId = Convert.ToInt32(((row["clientId"] == DBNull.Value || row["clientId"] == null) ? "0" : row["clientId"]).ToString()),
  371. contactId = Convert.ToInt32(((row["contactId"] == DBNull.Value || row["contactId"] == null) ? "0" : row["contactId"]).ToString()),
  372. brokerId = Convert.ToInt32(((row["brokerId"] == DBNull.Value || row["brokerId"] == null) ? "0" : row["brokerId"]).ToString()),
  373. brokerName = ((row["brokerName"] == DBNull.Value || row["brokerName"] == null) ? " " : row["brokerName"]).ToString(),
  374. clientname = ((row["clientname"] == DBNull.Value || row["clientname"] == null) ? " " : row["clientname"]).ToString(),
  375. firstName = ((row["firstName"] == DBNull.Value || row["firstName"] == null) ? " " : row["firstName"]).ToString(),
  376. lastName = ((row["lastName"] == DBNull.Value || row["lastName"] == null) ? " " : row["lastName"]).ToString(),
  377. Add = (row["Add"] == DBNull.Value || row["Add"] == null) ? (DateTime?)null : Convert.ToDateTime(row["Add"]),
  378. Updated = (row["Updated"] == DBNull.Value || row["Updated"] == null) ? (DateTime?)null : Convert.ToDateTime(row["Updated"]),
  379. contact = (row["contact"] == DBNull.Value || row["contact"] == null) ? (DateTime?)null : Convert.ToDateTime(row["contact"]),
  380. followUp = (row["followUp"] == DBNull.Value || row["followUp"] == null) ? (DateTime?)null : Convert.ToDateTime(row["followUp"]),
  381. email = ((row["email"] == DBNull.Value || row["email"] == null) ? " " : row["email"]).ToString(),
  382. tel = ((row["tel"] == DBNull.Value || row["tel"] == null) ? " " : row["tel"]).ToString(),
  383. sla = ((row["sla"] == DBNull.Value || row["sla"] == null) ? " " : row["sla"]).ToString(),
  384. lead = ((row["lead"] == DBNull.Value || row["lead"] == null) ? " " : row["lead"]).ToString(),
  385. neg = ((row["neg"] == DBNull.Value || row["neg"] == null) ? " " : row["neg"]).ToString(),
  386. branch = ((row["branch"] == DBNull.Value || row["branch"] == null) ? " " : row["branch"]).ToString(),
  387. status = Convert.ToInt32(((row["status"] == DBNull.Value || row["status"] == null) ? "0" : row["status"]).ToString()),
  388. type = Convert.ToInt32(((row["type"] == DBNull.Value || row["type"] == null) ? "0" : row["type"]).ToString()),
  389. StatusName = ((row["StatusName"] == DBNull.Value || row["StatusName"] == null) ? " " : row["StatusName"]).ToString(),
  390. Lender = ((row["Lender"] == DBNull.Value || row["Lender"] == null) ? " " : row["Lender"]).ToString(),
  391. Soliciter = ((row["Soliciter"] == DBNull.Value || row["Soliciter"] == null) ? " " : row["Soliciter"]).ToString(),
  392. BrokerEmail = ((row["BrokerEmail"] == DBNull.Value || row["BrokerEmail"] == null) ? " " : row["BrokerEmail"]).ToString(),
  393. PolicyNumber = ((row["PolicyNumber"] == DBNull.Value || row["PolicyNumber"] == null) ? " " : row["PolicyNumber"]).ToString(),
  394. TeamId = Convert.ToInt32(((row["TeamId"] == DBNull.Value || row["TeamId"] == null) ? "0" : row["TeamId"]).ToString()),
  395. OtherApplicantEmail = ((row["OtherApplicantEmail"] == DBNull.Value || row["OtherApplicantEmail"] == null) ? " " : row["OtherApplicantEmail"]).ToString(),
  396. OtherApplicantId = Convert.ToInt32(((row["OtherApplicantId"] == DBNull.Value || row["OtherApplicantId"] == null) ? "0" : row["OtherApplicantId"]).ToString()),
  397. OtherApplicantFname = ((row["OtherApplicantFname"] == DBNull.Value || row["OtherApplicantFname"] == null) ? " " : row["OtherApplicantFname"]).ToString(),
  398. OtherApplicantMobile = ((row["OtherApplicantMobile"] == DBNull.Value || row["OtherApplicantMobile"] == null) ? " " : row["OtherApplicantMobile"]).ToString(),
  399. OtherApplicantSName = ((row["OtherApplicantSName"] == DBNull.Value || row["OtherApplicantSName"] == null) ? " " : row["OtherApplicantSName"]).ToString(),
  400. AdminId = Convert.ToInt32(((row["AdminId"] == DBNull.Value || row["AdminId"] == null) ? "0" : row["AdminId"]).ToString()),
  401.  
  402. statusinterval = Convert.ToInt32(((row["statusinterval"] == DBNull.Value || row["statusinterval"] == null) ? 0 : row["statusinterval"]).ToString()),
  403. RecurrenceID = !string.IsNullOrEmpty(row["RecurrenceID"].ToString()) ? Convert.ToInt32(row["RecurrenceID"].ToString()) : new Nullable<int>(),
  404. RecurrenceException = !string.IsNullOrEmpty(row["RecurrenceException"].ToString()) ? row["RecurrenceException"].ToString() : null,
  405. RecurrenceRule = !string.IsNullOrEmpty(row["RecurrenceRule"].ToString()) ? row["RecurrenceRule"].ToString() : null,
  406. RID = !string.IsNullOrEmpty(row["ID"].ToString()) ? Convert.ToInt32(row["ID"].ToString()) : new Nullable<int>(),
  407. //Convert.ToInt32(((row["ID"] == DBNull.Value || row["ID"] == null) ? "0" : row["ID"]).ToString()),
  408.  
  409.  
  410. }).AsQueryable();
  411. }
  412.  
  413. return leadData;
  414.  
  415.  
  416. }
  417.  
  418. public tbluser LoggedUserName(int UserId)
  419. {
  420. return ctx.tblusers.Where(x => x.userid == UserId && x.active == true).SingleOrDefault();
  421. }
  422.  
  423. #endregion
  424.  
  425. #region Get Individua lead from database
  426. /// <summary>
  427. /// getting Single lead for application page
  428. /// </summary>
  429. /// <param name="accountid"></param>
  430. /// <param name="contactId"></param>
  431. /// <param name="financeid"></param>
  432. /// <returns></returns>
  433. public ApplicationJsonLead GetSingleLead(int accountid, int contactId = 0, int financeid = 0)
  434. {
  435. var q = (from c in ctx.tblcontacts
  436. join a in ctx.tblaccounts on c.account equals a.accountid
  437. join u in ctx.tblusers on c.broker equals u.userid
  438. join ft in ctx.tblfinances on c.contactid equals ft.contact
  439. join fs in ctx.tblfinancestatus on ft.status equals fs.statusid
  440. join o in ctx.tblOtherApplicants on c.contactid equals o.MainContactId into othercontact
  441. from o in othercontact.DefaultIfEmpty()
  442. orderby c.address descending
  443. select new ApplicationJsonLead()
  444. {
  445. AccountId = u.account,
  446. financeid = ft.financeid,
  447. clientId = a.accountid,
  448. contactId = c.contactid,
  449. brokerId = u.userid,
  450. brokerName = u.brokername,
  451. clientname = a.companyname,
  452. firstName = c.firstname,
  453. lastName = c.lastname,
  454. Add = c.created,
  455. Updated = c.modified,
  456. contact = c.lastcontact,
  457. followUp = ft.followup,
  458. email = c.email,
  459. tel = c.telephone,
  460. sla = c.slamet == true ? "Y" : "",
  461. lead = "D",
  462. neg = ctx.tblNegotiators.Where(negt => negt.Id == c.NegrefId).FirstOrDefault().Negotiator,//c.negref,
  463. branch = ctx.tblBranches.Where(brn => brn.Id == c.BranchId).FirstOrDefault().Branch,//c.branch,
  464. parentContactId = c.parentContactId ?? 0,
  465. status = ft.status,
  466. type = ft.type,
  467. StatusName = fs.name,
  468. Lender = ft.provider,
  469. Soliciter = ctx.tblfinance_mortgage.Where(x => x.financeid == ft.financeid).FirstOrDefault().solicitor,
  470. BrokerEmail = u.username,
  471. PolicyNumber = ft.policyreference ?? "",
  472. TeamId = u.TeamId ?? 0,
  473. OtherApplicantEmail = o.Email,
  474. OtherApplicantId = o.OtherApplicantId == null ? 0 : o.OtherApplicantId,
  475. OtherApplicantFname = o.FirstName ?? "",
  476. OtherApplicantMobile = o.MobileNumber ?? "",
  477. OtherApplicantSName = o.SurName ?? "",
  478. AdminId = u.AdminId ?? 0,
  479. PrimaryContact = u.PrimaryContact ?? "",
  480. SecondaryContact = u.SecondaryContact ?? "",
  481. Address1 = u.Address1 ?? "",
  482. Address2 = u.Address2 ?? "",
  483. OtherDetails = u.OtherDetails ?? "",
  484. Title = u.Title ?? "",
  485. FAX = u.FAX ?? "",
  486. Qualification = u.Qualification ?? "",
  487. FinanceAddress = ft.Address1 ?? "",
  488. SubStatusId = ft.subFinanceStatus ?? 0
  489. }).Where(c => c.contactId == contactId && c.financeid == financeid).FirstOrDefault();
  490.  
  491. if(q != null)
  492. {
  493. db_occfinance_5572Entities _ctx = new db_occfinance_5572Entities();
  494. var v = _ctx.tblfinancesubstatus.Where(x => x.SubstatusId == q.SubStatusId).FirstOrDefault();
  495. if (v != null)
  496. q.SubStatusName = v.Substatusname;
  497. else
  498. q.SubStatusName = "";
  499. }
  500.  
  501. return q ?? new ApplicationJsonLead();
  502. }
  503.  
  504. #endregion
  505.  
  506. #region [Update Application Form 2015-03-31]
  507.  
  508. /// <summary>
  509. /// Update application form
  510. /// </summary>
  511. /// <param name="MortgageList"></param>
  512. /// <param name="LifeList"></param>
  513. /// <param name="BuildinbContentList"></param>
  514. /// <param name="PensionList"></param>
  515. /// <param name="InvestmentList"></param>
  516. /// <param name="_address"></param>
  517. /// <param name="Contactdetails"></param>
  518. /// <param name="userid"></param>
  519. /// <param name="OtherApplicant"></param>
  520. public void UpdateApplicationForm(Boolean mortage_notes_allow, string client, string branch, string neg, List<Mortgage> MortgageList, List<Life> LifeList, List<BuildingContent> BuildinbContentList, List<Pensions> PensionList, List<Investment> InvestmentList, Address _address, ApplicationForm Contactdetails, out int leadTransfer, int? userid = null, OtherApplicant OtherApplicant = null)
  521. {
  522. try
  523. {
  524. Q_NewLead QNewLead = new Q_NewLead();
  525. var negrefid = QNewLead.GetNegotiatorId(neg);
  526. var branchid = QNewLead.GetBranchId(branch);
  527.  
  528. leadTransfer = 0;
  529. //Added By Kabir 31_01_2014
  530. int financeId = 0;
  531. int status = 0;
  532. string newremortage = null;
  533. string remortgageContact = null;
  534. //*************************
  535.  
  536. //int _noteid = getNoteID();
  537. int addrid = 0;
  538.  
  539. #region finance_Address
  540. //Address
  541. int addressid = ctx.tblcontacts.Where(x => x.contactid == _address.contactid).Select(x => x.address.Value).FirstOrDefault();
  542. tbladdress address = ctx.tbladdresses.Where(x => x.addressid == addressid).FirstOrDefault();
  543. if (addressid != 0)
  544. {
  545. tbladdress newaddress = new tbladdress();
  546. if (address != null)
  547. {
  548. //update
  549. address.address1 = _address.address_line1;
  550. address.address2 = _address.address_line2;
  551. address.town = _address.address_city;
  552. address.country = "";
  553. address.postcode = _address.address_postcode;
  554. ctx.SaveChanges();
  555. addrid = addressid;
  556. }
  557. else
  558. {
  559. //insert
  560. //newaddress.addressid = _address.contactid;
  561. newaddress.address1 = _address.address_line1;
  562. newaddress.address2 = _address.address_line2;
  563. newaddress.town = _address.address_city;
  564. newaddress.postcode = _address.address_postcode;
  565. ctx.tbladdresses.Add(newaddress);
  566. ctx.SaveChanges();
  567. addrid = newaddress.addressid;
  568. }
  569.  
  570. }
  571. else
  572. {
  573. tbladdress newaddress = new tbladdress();
  574. newaddress.address1 = _address.address_line1;
  575. newaddress.address2 = _address.address_line2;
  576. newaddress.town = _address.address_city;
  577. newaddress.postcode = _address.address_postcode;
  578. ctx.tbladdresses.Add(newaddress);
  579. ctx.SaveChanges();
  580. addrid = newaddress.addressid;
  581. }
  582.  
  583. #endregion
  584.  
  585. #region update Contact details
  586. var isBroker = ctx.tblusers.Where(x => x.userid == userid && x.broker == true && x.administrator == false && x.active == true).FirstOrDefault();
  587. var contact = ctx.tblcontacts.Where(x => x.contactid == Contactdetails.contactid).FirstOrDefault();
  588. bool isInsuranceBroker = IsInsuranceBroker(userid ?? 0);
  589. bool isNotInsuranceBroker = IsInsuranceBroker(Contactdetails.Broker);
  590. if (contact != null)
  591. {
  592.  
  593. if (isBroker != null)
  594. {
  595. if (isInsuranceBroker == isNotInsuranceBroker)
  596. {
  597. if (contact.broker != Contactdetails.Broker)
  598. {
  599. if (Contactdetails.Broker != userid)
  600. {
  601.  
  602. var IsAlready = ctx.tblLeadTransferHistories.Where(x => x.contactId == Contactdetails.contactid && x.status == 1).FirstOrDefault();
  603.  
  604. if (IsAlready == null)
  605. {
  606. tblLeadTransferHistory leadRequest = new tblLeadTransferHistory();
  607. leadRequest.contactId = Contactdetails.contactid;
  608. leadRequest.odlBroker = contact.broker;
  609. leadRequest.newBroker = Contactdetails.Broker;
  610. leadRequest.createDate = System.DateTime.Now;
  611. leadRequest.status = 1;
  612. ctx.tblLeadTransferHistories.Add(leadRequest);
  613. leadTransfer = 3;//Request has been sent successfully
  614. }
  615. else
  616. {
  617. leadTransfer = 2;// For this contact request is already pending
  618. }
  619. }
  620. else
  621. {
  622. leadTransfer = 1;//broker not transfered
  623. }
  624. }
  625. }
  626. else
  627. {
  628. leadTransfer = 0;//this is the case when insurance broker is updating the record of mortgage broker
  629. contact.broker = Contactdetails.Broker;
  630. }
  631. }
  632. else
  633. {
  634. leadTransfer = 0;//not broker
  635. contact.broker = Contactdetails.Broker;
  636. }
  637. if (Convert.ToInt32(client) == 1583028)
  638. {
  639. var pCid = Contactdetails.parentContactId.Split('_').ToList();
  640. int PId;
  641. int.TryParse(pCid[0], out PId);
  642. if (PId == 0)
  643. {
  644. if (negrefid != 0)
  645. {
  646.  
  647. var parentContact = ctx.tblcontacts.Where(x => ((x.firstname.ToUpper().Trim() ?? "") + " " + (x.lastname.ToUpper().Trim() ?? "")).Trim() == neg.ToUpper().Trim() && x.contactid != contact.contactid && x.account != 1582971).OrderByDescending(x => x.contactid).Select(x => x.contactid).ToList();
  648. if (parentContact.Count > 1)
  649. {
  650. if (((contact.parentContactId ?? 0) == 0) && ((contact.parentNegId ?? 0) == 0))
  651. {
  652. contact.parentContactId = parentContact.FirstOrDefault();
  653. }
  654. }
  655. else
  656. {
  657. if (parentContact.Count == 1)
  658. {
  659. if (contact.NegrefId != negrefid)
  660. {
  661. contact.parentContactId = parentContact.FirstOrDefault();
  662. contact.parentNegId = 0;
  663. }
  664.  
  665. if (((contact.parentContactId ?? 0) == 0) && ((contact.parentNegId ?? 0) == 0))
  666. {
  667. contact.parentContactId = parentContact.FirstOrDefault();
  668. contact.parentNegId = 0;
  669. }
  670. }
  671. else
  672. {
  673. int parentNegId = ctx.tblClientReferralNeg.Where(x => x.NegName.Trim().ToUpper() == neg.Trim().ToUpper()).Select(x => x.NegId).FirstOrDefault();
  674. contact.parentNegId = parentNegId;
  675. contact.parentContactId = 0;
  676. }
  677. }
  678. }
  679. else
  680. {
  681. contact.parentContactId = 0;
  682. contact.parentNegId = 0;
  683. }
  684. }
  685. else
  686. {
  687. if (pCid.Count == 1)
  688. {
  689. contact.parentContactId = PId;
  690. contact.parentNegId = 0;
  691. }
  692. else
  693. {
  694. contact.parentNegId = PId;
  695. contact.parentContactId = 0;
  696. }
  697.  
  698. }
  699. }
  700. else
  701. {
  702. contact.parentContactId = 0;
  703. contact.parentNegId = 0;
  704. }
  705.  
  706. if (isInsuranceBroker)
  707. {
  708. var LI = ctx.tblLinkInsuranceHistory.Where(x => x.ContactId == Contactdetails.contactid && x.MortgageBroker == Contactdetails.Broker).FirstOrDefault();
  709. if (LI != null)
  710. LI.UpdatedDate = System.DateTime.Now;
  711. }
  712.  
  713.  
  714. contact.BranchId = branchid;
  715. contact.account = Convert.ToInt32(client);
  716. contact.NegrefId = negrefid;
  717. contact.firstname = Contactdetails.FirstName;
  718. contact.lastname = Contactdetails.LastName;
  719.  
  720. contact.email = Contactdetails.Email;
  721. contact.telephone = Contactdetails.Telephone;
  722. if (Contactdetails.ContactIn2Hrs == 0)
  723. contact.slamet = null;
  724. if (Contactdetails.ContactIn2Hrs == 1)
  725. contact.slamet = true;
  726. if (Contactdetails.ContactIn2Hrs == 2)
  727. contact.slamet = false;
  728. contact.ninumber = _address.ninnumber;
  729. contact.dob = Helper.GetDate(_address.dob);
  730. contact.jobtitle = _address.jobtitle;
  731. contact.employer = _address.employer;
  732. contact.accountdetails = _address.accountant;
  733. contact.married = Helper.GetDate(_address.weddinganniversary);
  734. contact.dependants = _address.dependents;
  735. contact.method = Contactdetails.Method;
  736. contact.address = addrid;
  737.  
  738. if (Contactdetails.IsOfferQualified == true)
  739. {
  740. contact.offerqualified = true;
  741. }
  742. else
  743. {
  744. contact.offerqualified = false;
  745. }
  746. if (Contactdetails.IsContactTodayY == true)
  747. {
  748. contact.lastcontact = System.DateTime.Now;
  749. }
  750. ctx.SaveChanges();
  751.  
  752.  
  753. //Other Contact Data: Date- 12/6/2014
  754. if (OtherApplicant != null)
  755. {
  756. if (OtherApplicant.OtherApplicantId > 0) // Update
  757. {
  758. var _Other = ctx.tblOtherApplicants.Where(c => c.OtherApplicantId == OtherApplicant.OtherApplicantId).FirstOrDefault();
  759. if (_Other != null)
  760. {
  761. _Other.Email = OtherApplicant.Email;
  762. _Other.FirstName = OtherApplicant.FirstName;
  763. _Other.SurName = OtherApplicant.SurName;
  764. _Other.MobileNumber = OtherApplicant.MobileNumber;
  765. _Other.UpdateDate = DateTime.Now;
  766. _Other.UpdateBy = userid;
  767. _Other.address1 = OtherApplicant.address_line1;
  768. _Other.address2 = OtherApplicant.address_line2;
  769. _Other.town = OtherApplicant.address_city;
  770. _Other.postcode = OtherApplicant.address_postcode;
  771. _Other.ninumber = OtherApplicant.ninnumber;
  772. _Other.jobtitle = OtherApplicant.jobtitle;
  773. _Other.dependants = OtherApplicant.dependents;
  774. _Other.employer = OtherApplicant.employer;
  775. _Other.accountdetails = OtherApplicant.accountant;
  776. _Other.dob = Helper.GetDate(OtherApplicant.dob);
  777. _Other.married = Helper.GetDate(OtherApplicant.weddinganniversary);
  778. // Delete condition, when all the values are null
  779. if (string.IsNullOrWhiteSpace(_Other.FirstName) && string.IsNullOrWhiteSpace(_Other.SurName) && string.IsNullOrWhiteSpace(_Other.MobileNumber) && string.IsNullOrWhiteSpace(_Other.Email) && string.IsNullOrWhiteSpace(_Other.address1) && string.IsNullOrWhiteSpace(_Other.address2) && string.IsNullOrWhiteSpace(_Other.town) && string.IsNullOrWhiteSpace(_Other.postcode) && string.IsNullOrWhiteSpace(_Other.ninumber) && string.IsNullOrWhiteSpace(_Other.jobtitle) && (_Other.married) == null && string.IsNullOrWhiteSpace(_Other.dependants) && string.IsNullOrWhiteSpace(_Other.accountdetails) && string.IsNullOrWhiteSpace(_Other.employer) && (_Other.dob) == null)
  780. {
  781. ctx.tblOtherApplicants.Remove(_Other);
  782. }
  783. ctx.SaveChanges();
  784. }
  785. }
  786. else // Add
  787. {
  788. if ((OtherApplicant != null && (!string.IsNullOrWhiteSpace(OtherApplicant.FirstName) || !string.IsNullOrWhiteSpace(OtherApplicant.SurName) || !string.IsNullOrWhiteSpace(OtherApplicant.Email) || !string.IsNullOrWhiteSpace(OtherApplicant.MobileNumber) || !string.IsNullOrWhiteSpace(OtherApplicant.address_line1) || !string.IsNullOrWhiteSpace(OtherApplicant.address_line2) || !string.IsNullOrWhiteSpace(OtherApplicant.address_city) || !string.IsNullOrWhiteSpace(OtherApplicant.address_postcode) || !string.IsNullOrWhiteSpace(OtherApplicant.ninnumber) || !string.IsNullOrWhiteSpace(OtherApplicant.jobtitle) || (OtherApplicant.weddinganniversary) != null || !string.IsNullOrWhiteSpace(OtherApplicant.dependents) || !string.IsNullOrWhiteSpace(OtherApplicant.accountant) || (OtherApplicant.dob) != null || !string.IsNullOrWhiteSpace(OtherApplicant.employer))) && !ctx.tblOtherApplicants.Where(c => c.MainContactId == contact.contactid).Any())
  789. {
  790. var _Other = new tblOtherApplicant();
  791. _Other.Email = OtherApplicant.Email;
  792. _Other.FirstName = OtherApplicant.FirstName;
  793. _Other.SurName = OtherApplicant.SurName;
  794. _Other.MobileNumber = OtherApplicant.MobileNumber;
  795. _Other.AddDate = DateTime.Now;
  796. _Other.EntryBy = userid;
  797. _Other.IsDelete = false;
  798. _Other.IsActive = true;
  799. _Other.MainContactId = contact.contactid;
  800. _Other.address1 = OtherApplicant.address_line1;
  801. _Other.address2 = OtherApplicant.address_line2;
  802. _Other.town = OtherApplicant.address_city;
  803. _Other.postcode = OtherApplicant.address_postcode;
  804. _Other.ninumber = OtherApplicant.ninnumber;
  805. _Other.jobtitle = OtherApplicant.jobtitle;
  806. _Other.dependants = OtherApplicant.dependents;
  807. _Other.employer = OtherApplicant.employer;
  808. _Other.accountdetails = OtherApplicant.accountant;
  809. _Other.dob = Helper.GetDate(OtherApplicant.dob);
  810. _Other.married = Helper.GetDate(OtherApplicant.weddinganniversary);
  811. ctx.tblOtherApplicants.Add(_Other);
  812. ctx.SaveChanges();
  813. }
  814. }
  815. }
  816. //***********************************
  817. }
  818.  
  819.  
  820.  
  821. #endregion
  822.  
  823. #region finance_mortage
  824. foreach (Mortgage mortage in MortgageList)
  825. {
  826. int oldStatusId;
  827. var _chkUpdate = (CheckStatusUpdate(mortage.mortage_financeid, mortage.mortage_mortgagesubtype, out oldStatusId));
  828.  
  829. if (mortage.mortage_financeid != 0)
  830. {
  831.  
  832. tblfinance finance = ctx.tblfinances.Where(x => x.financeid == mortage.mortage_financeid).FirstOrDefault();
  833. if (finance != null)
  834. {
  835.  
  836. //tblfinance_mortgage
  837. tblfinance_mortgage finance_mortgage = ctx.tblfinance_mortgage.Where(x => x.financeid == mortage.mortage_financeid).FirstOrDefault();
  838. if (finance_mortgage != null && mortage.mortage_mortgagesubtype != 0)
  839. {
  840. //update mortage
  841. finance_mortgage.solicitor = mortage.mortage_solicitor;
  842. finance_mortgage.purchase = mortage.mortage_propertyvalue;
  843. finance_mortgage.mortgagesubtype = mortage.mortage_reason;
  844. finance_mortgage.deposit = mortage.mortage_deposit;
  845. finance_mortgage.income = mortage.mortage_income;
  846. finance_mortgage.rterm = mortage.mortage_rterm;
  847. finance_mortgage.completed = Helper.GetDate(mortage.mortage_completiondate);
  848. finance_mortgage.exchanged = Helper.GetDate(mortage.mortage_exchangedate);
  849. finance_mortgage.remortgagedate = Helper.GetDate(mortage.mortage_remortagedate);
  850. newremortage = mortage.newremortgagedate;
  851. //Added on 2014-11-24
  852. finance_mortgage.remortgagecontact = Helper.GetDate(mortage.mortgage_remortgagecontact);
  853. remortgageContact = mortage.remortgagecontact;
  854. finance_mortgage.Rate = mortage.mortage_rate;
  855. finance_mortgage.RateType = mortage.RateType;
  856.  
  857.  
  858. //update finance.
  859. if (finance != null)
  860. {
  861. var flag = false;
  862. if (Convert.ToInt32(finance.status) != mortage.mortage_mortgagesubtype)
  863. {
  864. flag = true;
  865. }
  866. else if (finance.subtype != mortage.mortage_type)
  867. {
  868. flag = true;
  869. }
  870. else if (finance.policyreference != mortage.mortage_mortageaccountno)
  871. {
  872. flag = true;
  873. }
  874. else if (finance.followup != Helper.GetFollowUp(mortage.mortage_date, mortage.mortage_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage.mortage_time))
  875. {
  876. flag = true;
  877. }
  878. else if (finance.provider != mortage.mortage_lender)
  879. {
  880. flag = true;
  881. }
  882. else if (finance.amount != mortage.mortage_amount)
  883. {
  884. flag = true;
  885. }
  886. else if (finance.premium != mortage.mortage_premium)
  887. {
  888. flag = true;
  889. }
  890. else if (finance.commission != mortage.mortage_commission)
  891. {
  892. flag = true;
  893. }
  894. else if (finance.datelive != Helper.GetDate(mortage.mortage_datelive))
  895. {
  896. flag = true;
  897. }
  898. else if (finance.dateexpire != Helper.GetDate(mortage.mortage_expires))
  899. {
  900. flag = true;
  901. }
  902. else if (finance.fundvalue != mortage.mortage_propertyvalue)
  903. {
  904. flag = true;
  905. }
  906. else if (finance.Address1 != mortage.mortgage_address1)
  907. {
  908. flag = true;
  909. }
  910. else if (finance.Address2 != mortage.mortgage_address2)
  911. {
  912. flag = true;
  913. }
  914. else if (finance.Town != mortage.mortgage_town)
  915. {
  916. flag = true;
  917. }
  918. else if (finance.County != mortage.mortgage_county)
  919. {
  920. flag = true;
  921. }
  922. else if (finance.Postcode != mortage.mortgage_postcode)
  923. {
  924. flag = true;
  925. }
  926. else if(finance.subFinanceStatus != mortage.mortgage_SubFinanceStatus)
  927. {
  928. flag = true;
  929. }
  930. else if (finance.ClientFee != null || mortage.mortgage_Clientfee != 0)
  931. {
  932. if (finance.ClientFee != mortage.mortgage_Clientfee)
  933. {
  934. flag = true;
  935. }
  936.  
  937. }
  938. else
  939. {
  940. flag = false;
  941. }
  942. if (flag)
  943. {
  944. finance.status = mortage.mortage_mortgagesubtype;
  945. finance.subtype = mortage.mortage_type;
  946. finance.policyreference = mortage.mortage_mortageaccountno;
  947.  
  948. finance.followup = Helper.GetFollowUp(mortage.mortage_date, mortage.mortage_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage.mortage_time);
  949. finance.provider = mortage.mortage_lender;
  950. finance.amount = mortage.mortage_amount;
  951. finance.premium = mortage.mortage_premium;
  952. finance.commission = mortage.mortage_commission;
  953. finance.details = null;
  954. finance.updated = System.DateTime.Now;
  955. finance.datelive = Helper.GetDate(mortage.mortage_datelive);
  956. finance.dateexpire = Helper.GetDate(mortage.mortage_expires);
  957. finance.fundvalue = mortage.mortage_propertyvalue;
  958. finance.Address1 = mortage.mortgage_address1;
  959. finance.Address2 = mortage.mortgage_address2;
  960. finance.Town = mortage.mortgage_town;
  961. finance.County = mortage.mortgage_county;
  962. finance.Postcode = mortage.mortgage_postcode;
  963. finance.ClientFee = mortage.mortgage_Clientfee;
  964. finance.subFinanceStatus = mortage.mortgage_SubFinanceStatus;
  965.  
  966. }
  967. }
  968. }
  969. else
  970. {
  971. tblfinance_mortgage newfinancemortage = new tblfinance_mortgage();
  972. newfinancemortage.financeid = mortage.mortage_financeid;
  973. newfinancemortage.solicitor = mortage.mortage_solicitor;
  974. newfinancemortage.purchase = mortage.mortage_propertyvalue;
  975. newfinancemortage.mortgagesubtype = mortage.mortage_mortgagesubtype;
  976. newfinancemortage.deposit = mortage.mortage_deposit;
  977. newfinancemortage.income = mortage.mortage_income;
  978. newfinancemortage.rterm = mortage.mortage_rterm;
  979. newfinancemortage.completed = Helper.GetDate(mortage.mortage_completiondate);
  980. newfinancemortage.exchanged = Helper.GetDate(mortage.mortage_exchangedate);
  981. newfinancemortage.remortgagedate = Helper.GetDate(mortage.mortage_remortagedate);
  982. //Added on 2014-11-24
  983. newfinancemortage.remortgagecontact = Helper.GetDate(mortage.mortgage_remortgagecontact);
  984. newfinancemortage.Rate = mortage.mortage_rate;
  985. newfinancemortage.RateType = mortage.RateType;
  986. ctx.tblfinance_mortgage.Add(newfinancemortage);
  987.  
  988. }
  989.  
  990. }
  991. }
  992. else
  993. {
  994. if (mortage.mortage_mortgagesubtype != 0)
  995. {
  996. tblfinance newfinance = new tblfinance();
  997. newfinance.status = mortage.mortage_mortgagesubtype;
  998. newfinance.subtype = mortage.mortage_type;
  999. newfinance.policyreference = mortage.mortage_mortageaccountno;
  1000. if (mortage.mortage_mortgagesubtype == (int)MortgageStatus.Remortgage)
  1001. {
  1002. newfinance.followup = (mortage.mortage_date != null ? mortage.mortage_date.GetDateTime() : null);
  1003. }
  1004. else
  1005. newfinance.followup = Helper.GetFollowUp(mortage.mortage_date, mortage.mortage_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage.mortage_time);
  1006. newfinance.parentFinanceId = mortage.mortage_remortgageComplete;
  1007. newfinance.provider = mortage.mortage_lender;
  1008. newfinance.amount = mortage.mortage_amount;
  1009. newfinance.premium = mortage.mortage_premium;
  1010. newfinance.commission = mortage.mortage_commission;
  1011. newfinance.details = null;
  1012. newfinance.updated = System.DateTime.Now;
  1013. newfinance.datelive = Helper.GetDate(mortage.mortage_datelive);
  1014. newfinance.dateexpire = Helper.GetDate(mortage.mortage_expires);
  1015. newfinance.fundvalue = mortage.mortage_propertyvalue;
  1016. newfinance.contact = _address.contactid;
  1017. newfinance.type = (int)FinanceTypes.Mortgages;
  1018. newfinance.created = System.DateTime.Now;
  1019. newfinance.Address1 = mortage.mortgage_address1;
  1020. newfinance.Address2 = mortage.mortgage_address2;
  1021. newfinance.Town = mortage.mortgage_town;
  1022. newfinance.County = mortage.mortgage_county;
  1023. newfinance.Postcode = mortage.mortgage_postcode;
  1024. newfinance.ClientFee = mortage.mortgage_Clientfee;
  1025. newfinance.subFinanceStatus = mortage.mortgage_SubFinanceStatus;
  1026. ctx.tblfinances.Add(newfinance);
  1027. tblfinance_mortgage newfinancemortage = new tblfinance_mortgage();
  1028. newfinancemortage.solicitor = mortage.mortage_solicitor;
  1029. newfinancemortage.purchase = mortage.mortage_propertyvalue;
  1030. newfinancemortage.mortgagesubtype = mortage.mortage_mortgagesubtype;
  1031. newfinancemortage.deposit = mortage.mortage_deposit;
  1032. newfinancemortage.income = mortage.mortage_income;
  1033. newfinancemortage.rterm = mortage.mortage_rterm;
  1034. newfinancemortage.completed = Helper.GetDate(mortage.mortage_completiondate);
  1035. newfinancemortage.exchanged = Helper.GetDate(mortage.mortage_exchangedate);
  1036. newfinancemortage.remortgagedate = Helper.GetDate(mortage.mortage_remortagedate);
  1037. //Added on 2014-11-24
  1038. newfinancemortage.remortgagecontact = Helper.GetDate(mortage.mortgage_remortgagecontact);
  1039. newfinancemortage.Rate = mortage.mortage_rate;
  1040. newfinancemortage.RateType = mortage.RateType;
  1041. ctx.tblfinance_mortgage.Add(newfinancemortage);
  1042. }
  1043. }
  1044.  
  1045.  
  1046. if (mortage.mortage_mortgagesubtype != 0)
  1047. {
  1048. financeId = mortage.mortage_financeid;
  1049. status = mortage.mortage_mortgagesubtype;
  1050. if (mortage.mortage_notes != null)
  1051. {
  1052. tblnote mortagenote = new tblnote();
  1053. mortagenote.financeid = mortage.mortage_financeid;
  1054. mortagenote.contact = _address.contactid;
  1055. mortagenote.note = mortage.mortage_notes.Length > 8000 ? mortage.mortage_notes.Substring(0, 7999) : mortage.mortage_notes;
  1056. mortagenote.created = System.DateTime.Now;
  1057. mortagenote.UserId = userid;
  1058.  
  1059. mortagenote.allowInReport = mortage_notes_allow;
  1060. ctx.tblnotes.Add(mortagenote);
  1061. }
  1062. else
  1063. {
  1064. if (_chkUpdate)
  1065. {
  1066. tblnote mortagenoteforstatus = new tblnote();
  1067. mortagenoteforstatus.financeid = mortage.mortage_financeid;
  1068. mortagenoteforstatus.contact = _address.contactid;
  1069. if (oldStatusId != 0)
  1070. {
  1071. mortagenoteforstatus.note = string.Format("Status changes from {0} to {1} on {2} at {3} by {4}", ctx.tblfinancestatus.Where(x => x.statusid == oldStatusId).Select(x => x.name).FirstOrDefault(), ctx.tblfinancestatus.Where(x => x.statusid == mortage.mortage_mortgagesubtype).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1072. }
  1073. else
  1074. {
  1075. mortagenoteforstatus.note = string.Format(" Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == mortage.mortage_mortgagesubtype).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1076.  
  1077. }
  1078. mortagenoteforstatus.created = System.DateTime.Now;
  1079. mortagenoteforstatus.UserId = userid;
  1080. ctx.tblnotes.Add(mortagenoteforstatus);
  1081. //_noteid++;
  1082. }
  1083.  
  1084. }
  1085. }
  1086.  
  1087. ctx.SaveChanges();
  1088. }
  1089. #endregion
  1090.  
  1091. #region finance_Life
  1092. //Insurance or life
  1093.  
  1094. foreach (Life life in LifeList)
  1095. {
  1096. int oldStatusId;
  1097. var _chkLifeupdate = CheckStatusUpdate(life.life_financeid, life.life_status, out oldStatusId);
  1098. if (life.life_financeid != 0 && life.life_status != 0)
  1099. {
  1100. var queryInsurance = (from c in ctx.tblcontacts
  1101. join ft in ctx.tblfinances on c.contactid equals ft.contact
  1102. join fi in ctx.tblfinance_insurance on ft.financeid equals fi.financeid
  1103. where ft.contact == _address.contactid && ft.type == life.life_financetype && ft.financeid == life.life_financeid
  1104. select new { fi }).FirstOrDefault();
  1105.  
  1106. if (queryInsurance != null && life.life_financeid > 0)
  1107. {
  1108. tblfinance_insurance finance_insurance = ctx.tblfinance_insurance.Where(x => x.financeid == queryInsurance.fi.financeid).FirstOrDefault();
  1109. if (finance_insurance != null)
  1110. {
  1111. //update
  1112. finance_insurance.smoker = life.life_issmoker;
  1113. finance_insurance.height = life.life_height;
  1114. finance_insurance.weight = life.life_weight;
  1115. finance_insurance.doctor = life.life_doctor;
  1116. }
  1117. else
  1118. {
  1119. //insert
  1120. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  1121. newfinance_insurance.smoker = life.life_issmoker;
  1122. newfinance_insurance.height = life.life_height;
  1123. newfinance_insurance.weight = life.life_weight;
  1124. newfinance_insurance.doctor = life.life_doctor;
  1125. newfinance_insurance.financeid = life.life_financeid;
  1126. ctx.tblfinance_insurance.Add(newfinance_insurance);
  1127. }
  1128. //update finance.
  1129. tblfinance finance_life = ctx.tblfinances.Where(x => x.financeid == queryInsurance.fi.financeid).FirstOrDefault();
  1130. if (finance_life != null)
  1131. {
  1132. var flag = false;
  1133. if (finance_life.status != life.life_status)
  1134. {
  1135. flag = true;
  1136. }
  1137. else if (finance_life.subtype != life.life_policytype)
  1138. {
  1139. flag = true;
  1140. }
  1141. else if (finance_life.policyreference != life.life_policyref)
  1142. {
  1143. flag = true;
  1144. }
  1145. else if (finance_life.followup != Helper.GetFollowUp(life.life_date, life.life_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life.life_time))
  1146. {
  1147. flag = true;
  1148. }
  1149. else if (finance_life.provider != life.life_provider)
  1150. {
  1151. flag = true;
  1152. }
  1153. else if (finance_life.amount != life.life_amount)
  1154. {
  1155. flag = true;
  1156. }
  1157. else if (finance_life.premium != life.life_premium)
  1158. {
  1159. flag = true;
  1160. }
  1161. else if (finance_life.commission != life.life_commission)
  1162. {
  1163. flag = true;
  1164. }
  1165. else if (finance_life.details != life.life_medical)
  1166. {
  1167. flag = true;
  1168. }
  1169. else if (finance_life.datelive != Helper.GetDate(life.life_datelive))
  1170. {
  1171. flag = true;
  1172. }
  1173. else if (finance_life.dateexpire != Helper.GetDate(life.life_expires))
  1174. {
  1175. flag = true;
  1176. }
  1177. else if (finance_life.isPolicyForOtherApplicant != life.life_isPolicyForOtherApplicant)
  1178. {
  1179. flag = true;
  1180. }
  1181. else if (finance_life.ClientFee != null || life.life_Clientfee != 0)
  1182. {
  1183. if (finance_life.ClientFee != life.life_Clientfee)
  1184. {
  1185. flag = true;
  1186. }
  1187. }
  1188. else
  1189. {
  1190. flag = false;
  1191. }
  1192. if (flag)
  1193. {
  1194. finance_life.status = life.life_status;
  1195. finance_life.subtype = life.life_policytype;
  1196. finance_life.policyreference = life.life_policyref;
  1197. finance_life.followup = Helper.GetFollowUp(life.life_date, life.life_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life.life_time);
  1198. finance_life.provider = life.life_provider;
  1199. finance_life.amount = life.life_amount;
  1200. finance_life.premium = life.life_premium;
  1201. finance_life.commission = life.life_commission;
  1202. finance_life.details = life.life_medical;
  1203. finance_life.updated = System.DateTime.Now;
  1204. finance_life.datelive = Helper.GetDate(life.life_datelive);
  1205. finance_life.dateexpire = Helper.GetDate(life.life_expires);
  1206.  
  1207. //Added Client Fee 27-10-2014
  1208. finance_life.ClientFee = life.life_Clientfee;
  1209. //********************************
  1210. //added on 03-04-2015
  1211. finance_life.isPolicyForOtherApplicant = life.life_isPolicyForOtherApplicant;
  1212.  
  1213. }
  1214. }
  1215.  
  1216. }
  1217. else
  1218. {
  1219. tblfinance newfinance = new tblfinance();
  1220. newfinance.contact = _address.contactid;
  1221. newfinance.type = (int)FinanceTypes.Life;
  1222. newfinance.status = life.life_status;
  1223. newfinance.subtype = life.life_policytype;
  1224. newfinance.policyreference = life.life_policyref;
  1225. newfinance.followup = Helper.GetFollowUp(life.life_date, life.life_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life.life_time);
  1226. newfinance.provider = life.life_provider;
  1227. newfinance.amount = life.life_amount;
  1228. newfinance.premium = life.life_premium;
  1229. newfinance.commission = life.life_commission;
  1230. newfinance.details = life.life_medical;
  1231. newfinance.updated = System.DateTime.Now;
  1232. newfinance.datelive = Helper.GetDate(life.life_datelive);
  1233. newfinance.dateexpire = Helper.GetDate(life.life_expires);
  1234. newfinance.created = System.DateTime.Now;
  1235.  
  1236. //Added Client Fee 27-10-2014
  1237. newfinance.ClientFee = life.life_Clientfee;
  1238. //********************************
  1239. //added on 03-04-2015
  1240. newfinance.isPolicyForOtherApplicant = life.life_isPolicyForOtherApplicant;
  1241.  
  1242. ctx.tblfinances.Add(newfinance);
  1243.  
  1244.  
  1245. //insert
  1246. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  1247. newfinance_insurance.smoker = life.life_issmoker;
  1248. newfinance_insurance.height = life.life_height;
  1249. newfinance_insurance.weight = life.life_weight;
  1250. newfinance_insurance.doctor = life.life_doctor;
  1251. ctx.tblfinance_insurance.Add(newfinance_insurance);
  1252. }
  1253. }
  1254. else
  1255. {
  1256. if (life.life_status != 0)
  1257. {
  1258. tblfinance newfinance = new tblfinance();
  1259. newfinance.contact = _address.contactid;
  1260. newfinance.type = (int)FinanceTypes.Life;
  1261. newfinance.status = life.life_status;
  1262. newfinance.subtype = life.life_policytype;
  1263. newfinance.policyreference = life.life_policyref;
  1264. newfinance.followup = Helper.GetFollowUp(life.life_date, life.life_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life.life_time);
  1265. newfinance.provider = life.life_provider;
  1266. newfinance.amount = life.life_amount;
  1267. newfinance.premium = life.life_premium;
  1268. newfinance.commission = life.life_commission;
  1269. newfinance.details = life.life_medical;
  1270. newfinance.updated = System.DateTime.Now;
  1271. newfinance.datelive = Helper.GetDate(life.life_datelive);
  1272. newfinance.dateexpire = Helper.GetDate(life.life_expires);
  1273. newfinance.created = System.DateTime.Now;
  1274.  
  1275. //Added Client Fee 27-10-2014
  1276. newfinance.ClientFee = life.life_Clientfee;
  1277. //********************************
  1278. newfinance.isPolicyForOtherApplicant = life.life_isPolicyForOtherApplicant;
  1279.  
  1280. ctx.tblfinances.Add(newfinance);
  1281.  
  1282. //insert
  1283. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  1284. newfinance_insurance.smoker = life.life_issmoker;
  1285. newfinance_insurance.height = life.life_height;
  1286. newfinance_insurance.weight = life.life_weight;
  1287. newfinance_insurance.doctor = life.life_doctor;
  1288. ctx.tblfinance_insurance.Add(newfinance_insurance);
  1289. }
  1290. }
  1291.  
  1292. if (life.life_status != 0)
  1293. {
  1294.  
  1295. financeId = life.life_financeid;
  1296. status = life.life_status;
  1297.  
  1298. if (life.life_notes != null)
  1299. {
  1300. tblnote lifenote = new tblnote();
  1301. lifenote.financeid = life.life_financeid;
  1302. lifenote.contact = _address.contactid;
  1303. lifenote.note = life.life_notes.Length > 8000 ? life.life_notes.Substring(0, 7999) : life.life_notes;
  1304. lifenote.created = System.DateTime.Now;
  1305. lifenote.UserId = userid;
  1306. lifenote.allowInReport = mortage_notes_allow;
  1307. ctx.tblnotes.Add(lifenote);
  1308.  
  1309. }
  1310. else
  1311. {
  1312. if (_chkLifeupdate)
  1313. {
  1314. tblnote lifenoteforstatus = new tblnote();
  1315. lifenoteforstatus.financeid = life.life_financeid;
  1316. lifenoteforstatus.contact = _address.contactid;
  1317. if (oldStatusId != 0)
  1318. {
  1319. lifenoteforstatus.note = string.Format("Status changes from {0} to {1} on {2} at {3} by {4}", ctx.tblfinancestatus.Where(x => x.statusid == oldStatusId).Select(x => x.name).FirstOrDefault(), ctx.tblfinancestatus.Where(x => x.statusid == life.life_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1320. }
  1321. else
  1322. {
  1323. lifenoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == life.life_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1324. }
  1325. lifenoteforstatus.created = System.DateTime.Now;
  1326. lifenoteforstatus.UserId = userid;
  1327. ctx.tblnotes.Add(lifenoteforstatus);
  1328.  
  1329. }
  1330. }
  1331. }
  1332. ctx.SaveChanges();
  1333. }
  1334. #endregion
  1335.  
  1336. #region finance_Building
  1337. foreach (BuildingContent buildingcontent in BuildinbContentList)
  1338. {
  1339. int oldStatusId;
  1340. var _chkbuildingUpdate = CheckStatusUpdate(buildingcontent.buildingcontent_financeid, buildingcontent.buildingcontent_status, out oldStatusId);
  1341. if (buildingcontent.buildingcontent_financeid != 0 && buildingcontent.buildingcontent_status != 0)
  1342. {
  1343. var queryBuilding = (from c in ctx.tblcontacts
  1344. join ft in ctx.tblfinances on c.contactid equals ft.contact
  1345. join fb in ctx.tblfinance_buildings on ft.financeid equals fb.financeid
  1346. where ft.contact == _address.contactid && ft.type == buildingcontent.buildingcontent_financetype && ft.financeid == buildingcontent.buildingcontent_financeid
  1347. select new { fb }).FirstOrDefault();
  1348.  
  1349. if (queryBuilding != null || buildingcontent.buildingcontent_financeid > 0)
  1350. {
  1351. tblfinance_buildings buildings = ctx.tblfinance_buildings.Where(x => x.financeid == queryBuilding.fb.financeid).FirstOrDefault();
  1352.  
  1353. if (buildings != null)
  1354. {
  1355. //update
  1356. buildings.rebuild = buildingcontent.buildingcontent_rebuildvalue;
  1357. buildings.alarm = buildingcontent.buildingcontent_isalarmed;
  1358. }
  1359. else
  1360. {
  1361. //insert
  1362. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  1363. newfinance_building.rebuild = buildingcontent.buildingcontent_rebuildvalue;
  1364. newfinance_building.alarm = buildingcontent.buildingcontent_isalarmed;
  1365. newfinance_building.financeid = buildingcontent.buildingcontent_financeid;
  1366. ctx.tblfinance_buildings.Add(newfinance_building);
  1367. }
  1368.  
  1369. //update finance.
  1370. tblfinance finance_building = ctx.tblfinances.Where(x => x.financeid == queryBuilding.fb.financeid).FirstOrDefault();
  1371. if (finance_building != null)
  1372. {
  1373. var flag = false;
  1374. if (finance_building.status != buildingcontent.buildingcontent_status)
  1375. {
  1376. flag = true;
  1377. }
  1378. else if (finance_building.policyreference != buildingcontent.buildingcontent_policyref)
  1379. {
  1380. flag = true;
  1381. }
  1382. else if (finance_building.followup != Helper.GetFollowUp(buildingcontent.buildingcontent_date, buildingcontent.buildingcontent_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : buildingcontent.buildingcontent_time))
  1383. {
  1384. flag = true;
  1385. }
  1386. else if (finance_building.provider != buildingcontent.buildingcontent_provider)
  1387. {
  1388. flag = true;
  1389. }
  1390. else if (finance_building.amount != buildingcontent.buildingcontent_amount)
  1391. {
  1392. flag = true;
  1393. }
  1394. else if (finance_building.premium != buildingcontent.buildingcontent_premium)
  1395. {
  1396. flag = true;
  1397. }
  1398. else if (finance_building.commission != buildingcontent.buildingcontent_commission)
  1399. {
  1400. flag = true;
  1401. }
  1402. else if (finance_building.subtype != buildingcontent.buildingcontent_type)
  1403. {
  1404. flag = true;
  1405. }
  1406. else if (finance_building.details != buildingcontent.buildingcontent_addcover)
  1407. {
  1408. flag = true;
  1409. }
  1410. else if (finance_building.datelive != Helper.GetDate(buildingcontent.buildingcontent_datelive))
  1411. {
  1412. flag = true;
  1413. }
  1414. else if (finance_building.dateexpire != Helper.GetDate(buildingcontent.buildingcontent_expires))
  1415. {
  1416. flag = true;
  1417. }
  1418. else if (finance_building.isPolicyForOtherApplicant != buildingcontent.buildingcontent_isPolicyForOtherApplicant)
  1419. {
  1420. flag = true;
  1421. }
  1422. else if (finance_building.ClientFee != null || buildingcontent.building_Clientfee != 0)
  1423. {
  1424. if (finance_building.ClientFee != buildingcontent.building_Clientfee)
  1425. {
  1426. flag = true;
  1427. }
  1428. }
  1429. else
  1430. {
  1431. flag = false;
  1432. }
  1433. if (flag)
  1434. {
  1435. finance_building.status = buildingcontent.buildingcontent_status;
  1436. finance_building.policyreference = buildingcontent.buildingcontent_policyref;
  1437. finance_building.followup = Helper.GetFollowUp(buildingcontent.buildingcontent_date, buildingcontent.buildingcontent_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : buildingcontent.buildingcontent_time);
  1438. finance_building.provider = buildingcontent.buildingcontent_provider;
  1439. finance_building.amount = buildingcontent.buildingcontent_amount;
  1440. finance_building.premium = buildingcontent.buildingcontent_premium;
  1441. finance_building.commission = buildingcontent.buildingcontent_commission;
  1442. finance_building.subtype = buildingcontent.buildingcontent_type;
  1443. finance_building.details = buildingcontent.buildingcontent_addcover;
  1444. finance_building.updated = System.DateTime.Now;
  1445. finance_building.datelive = Helper.GetDate(buildingcontent.buildingcontent_datelive);
  1446. finance_building.dateexpire = Helper.GetDate(buildingcontent.buildingcontent_expires);
  1447.  
  1448. //Added Client Fee 27-10-2014
  1449. finance_building.ClientFee = buildingcontent.building_Clientfee;
  1450. //********************************
  1451. finance_building.isPolicyForOtherApplicant = buildingcontent.buildingcontent_isPolicyForOtherApplicant;
  1452.  
  1453. }
  1454. }
  1455. }
  1456. else
  1457. {
  1458. if (buildingcontent.buildingcontent_status != 0)
  1459. {
  1460. //finance
  1461. tblfinance newfinance = new tblfinance();
  1462. newfinance.contact = _address.contactid;
  1463. newfinance.type = (int)FinanceTypes.BuildingsOrContents;
  1464. newfinance.status = buildingcontent.buildingcontent_status;
  1465. newfinance.subtype = buildingcontent.buildingcontent_type;
  1466. newfinance.policyreference = buildingcontent.buildingcontent_policyref;
  1467. newfinance.followup = Helper.GetFollowUp(buildingcontent.buildingcontent_date, buildingcontent.buildingcontent_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : buildingcontent.buildingcontent_time);
  1468. newfinance.provider = buildingcontent.buildingcontent_provider;
  1469. newfinance.amount = buildingcontent.buildingcontent_amount;
  1470. newfinance.premium = buildingcontent.buildingcontent_premium;
  1471. newfinance.commission = buildingcontent.buildingcontent_commission;
  1472. newfinance.details = buildingcontent.buildingcontent_addcover;
  1473. newfinance.updated = System.DateTime.Now;
  1474. newfinance.datelive = Helper.GetDate(buildingcontent.buildingcontent_datelive);
  1475. newfinance.dateexpire = Helper.GetDate(buildingcontent.buildingcontent_expires);
  1476. newfinance.created = System.DateTime.Now;
  1477.  
  1478. //Added Client Fee 27-10-2014
  1479. newfinance.ClientFee = buildingcontent.building_Clientfee;
  1480. //********************************
  1481. newfinance.isPolicyForOtherApplicant = buildingcontent.buildingcontent_isPolicyForOtherApplicant;
  1482.  
  1483. ctx.tblfinances.Add(newfinance);
  1484.  
  1485. //insert
  1486. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  1487. newfinance_building.rebuild = buildingcontent.buildingcontent_rebuildvalue;
  1488. newfinance_building.alarm = buildingcontent.buildingcontent_isalarmed;
  1489. ctx.tblfinance_buildings.Add(newfinance_building);
  1490. }
  1491. }
  1492. }
  1493. else
  1494. {
  1495. if (buildingcontent.buildingcontent_status != 0)
  1496. {
  1497. //finance
  1498. tblfinance newfinance = new tblfinance();
  1499. newfinance.contact = _address.contactid;
  1500. newfinance.type = (int)FinanceTypes.BuildingsOrContents;
  1501. newfinance.status = buildingcontent.buildingcontent_status;
  1502. newfinance.subtype = buildingcontent.buildingcontent_type;
  1503. newfinance.policyreference = buildingcontent.buildingcontent_policyref;
  1504. newfinance.followup = Helper.GetFollowUp(buildingcontent.buildingcontent_date, buildingcontent.buildingcontent_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : buildingcontent.buildingcontent_time);
  1505. newfinance.provider = buildingcontent.buildingcontent_provider;
  1506. newfinance.amount = buildingcontent.buildingcontent_amount;
  1507. newfinance.premium = buildingcontent.buildingcontent_premium;
  1508. newfinance.commission = buildingcontent.buildingcontent_commission;
  1509. newfinance.details = buildingcontent.buildingcontent_addcover;
  1510. newfinance.updated = System.DateTime.Now;
  1511. newfinance.created = System.DateTime.Now;
  1512. newfinance.datelive = Helper.GetDate(buildingcontent.buildingcontent_datelive);
  1513. newfinance.dateexpire = Helper.GetDate(buildingcontent.buildingcontent_expires);
  1514.  
  1515. //Added Client Fee 27-10-2014
  1516. newfinance.ClientFee = buildingcontent.building_Clientfee;
  1517. //********************************
  1518. newfinance.isPolicyForOtherApplicant = buildingcontent.buildingcontent_isPolicyForOtherApplicant;
  1519.  
  1520. ctx.tblfinances.Add(newfinance);
  1521.  
  1522. //insert
  1523. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  1524. newfinance_building.rebuild = buildingcontent.buildingcontent_rebuildvalue;
  1525. newfinance_building.alarm = buildingcontent.buildingcontent_isalarmed;
  1526. ctx.tblfinance_buildings.Add(newfinance_building);
  1527. }
  1528. }
  1529.  
  1530. if (buildingcontent.buildingcontent_status != 0)
  1531. {
  1532.  
  1533. financeId = buildingcontent.buildingcontent_financeid;
  1534. status = buildingcontent.buildingcontent_status;
  1535.  
  1536.  
  1537.  
  1538. if (buildingcontent.buildingcontent_notes != null)
  1539. {
  1540. tblnote buildingcontentnote = new tblnote();
  1541. buildingcontentnote.financeid = buildingcontent.buildingcontent_financeid;
  1542. buildingcontentnote.contact = _address.contactid;
  1543. buildingcontentnote.note = buildingcontent.buildingcontent_notes.Length > 8000 ? buildingcontent.buildingcontent_notes.Substring(0, 7999) : buildingcontent.buildingcontent_notes;
  1544. buildingcontentnote.created = System.DateTime.Now;
  1545. buildingcontentnote.UserId = userid;
  1546. buildingcontentnote.allowInReport = mortage_notes_allow;
  1547. ctx.tblnotes.Add(buildingcontentnote);
  1548.  
  1549. }
  1550. else
  1551. {
  1552. if (_chkbuildingUpdate)
  1553. {
  1554. tblnote buildingcontentnoteforstatus = new tblnote();
  1555. buildingcontentnoteforstatus.financeid = buildingcontent.buildingcontent_financeid;
  1556. buildingcontentnoteforstatus.contact = _address.contactid;
  1557. if (oldStatusId != 0)
  1558. {
  1559. buildingcontentnoteforstatus.note = string.Format("Status changes from {0} to {1} on {2} at {3} by {4}", ctx.tblfinancestatus.Where(x => x.statusid == oldStatusId).Select(x => x.name).FirstOrDefault(), ctx.tblfinancestatus.Where(x => x.statusid == buildingcontent.buildingcontent_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1560. }
  1561. else
  1562. {
  1563. buildingcontentnoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == buildingcontent.buildingcontent_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1564. }
  1565. buildingcontentnoteforstatus.created = System.DateTime.Now;
  1566. buildingcontentnoteforstatus.UserId = userid;
  1567. ctx.tblnotes.Add(buildingcontentnoteforstatus);
  1568.  
  1569. }
  1570. }
  1571. }
  1572. ctx.SaveChanges();
  1573. }
  1574. #endregion
  1575.  
  1576. #region finance_Pension
  1577. foreach (Pensions pension in PensionList)
  1578. {
  1579. int oldStatusId;
  1580. var _chkPensionUpdate = CheckStatusUpdate(pension.pension_financeid, pension.pension_status, out oldStatusId);
  1581. if (pension.pension_financeid != 0 && pension.pension_status != 0)
  1582. {
  1583. var queryPension = (from c in ctx.tblcontacts
  1584. join ft in ctx.tblfinances on c.contactid equals ft.contact
  1585. join fp in ctx.tblfinance_pension on ft.financeid equals fp.financeid
  1586. where ft.contact == _address.contactid && ft.type == pension.pension_financetype && ft.financeid == pension.pension_financeid
  1587. select new { fp }).FirstOrDefault();
  1588. if (queryPension != null || pension.pension_financeid > 0)
  1589. {
  1590. tblfinance_pension pensionnew = ctx.tblfinance_pension.Where(x => x.financeid == queryPension.fp.financeid).FirstOrDefault();
  1591. if (pensionnew != null)
  1592. {
  1593. //update
  1594. pensionnew.incomereq = pension.pension_incomereq;
  1595.  
  1596. }
  1597. else
  1598. {
  1599. //insert
  1600. tblfinance_pension newfinance_pension = new tblfinance_pension();
  1601. newfinance_pension.incomereq = pension.pension_incomereq;
  1602. newfinance_pension.financeid = pension.pension_financeid;
  1603. ctx.tblfinance_pension.Add(newfinance_pension);
  1604. }
  1605.  
  1606. tblfinance finance_pensions = ctx.tblfinances.Where(x => x.financeid == queryPension.fp.financeid).FirstOrDefault();
  1607. if (finance_pensions != null)
  1608. {
  1609. //update
  1610.  
  1611. //finance
  1612. var flag = false;
  1613. if (finance_pensions.status != pension.pension_status)
  1614. {
  1615. flag = true;
  1616. }
  1617. else if (finance_pensions.subtype != pension.pension_riskprofile)
  1618. {
  1619. flag = true;
  1620. }
  1621. else if (finance_pensions.policyreference != pension.pension_policyref)
  1622. {
  1623. flag = true;
  1624. }
  1625. else if (finance_pensions.followup != Helper.GetFollowUp(pension.pension_date, pension.pension_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : pension.pension_time))
  1626. {
  1627. flag = true;
  1628. }
  1629. else if (finance_pensions.provider != pension.pension_provider)
  1630. {
  1631. flag = true;
  1632. }
  1633. else if (finance_pensions.amount != pension.pension_amount)
  1634. {
  1635. flag = true;
  1636. }
  1637. else if (finance_pensions.premium != pension.pension_contributors)
  1638. {
  1639. flag = true;
  1640. }
  1641. else if (finance_pensions.commission != pension.pension_commission)
  1642. {
  1643. flag = true;
  1644. }
  1645. else if (finance_pensions.datelive != Helper.GetDate(pension.pension_datelive))
  1646. {
  1647. flag = true;
  1648. }
  1649. else if (finance_pensions.dateexpire != Helper.GetDate(pension.pension_expires))
  1650. {
  1651. flag = true;
  1652. }
  1653. else if (finance_pensions.fundvalue != pension.pension_fundvalue)
  1654. {
  1655. flag = true;
  1656. }
  1657. else if (finance_pensions.isPolicyForOtherApplicant != pension.pension_isPolicyForOtherApplicant)
  1658. {
  1659. flag = true;
  1660. }
  1661. else if (finance_pensions.ClientFee != null || pension.pension_Clientfee != 0)
  1662. {
  1663. if (finance_pensions.ClientFee != pension.pension_Clientfee)
  1664. {
  1665. flag = true;
  1666. }
  1667. }
  1668. else
  1669. {
  1670. flag = false;
  1671. }
  1672.  
  1673. if (flag)
  1674. {
  1675. finance_pensions.status = pension.pension_status;
  1676. finance_pensions.subtype = pension.pension_riskprofile;
  1677. finance_pensions.policyreference = pension.pension_policyref;
  1678. finance_pensions.followup = Helper.GetFollowUp(pension.pension_date, pension.pension_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : pension.pension_time);
  1679. finance_pensions.provider = pension.pension_provider;
  1680. finance_pensions.amount = pension.pension_amount;
  1681. finance_pensions.premium = pension.pension_contributors;
  1682. finance_pensions.commission = pension.pension_commission;
  1683. finance_pensions.details = null;
  1684. finance_pensions.updated = System.DateTime.Now;
  1685. finance_pensions.datelive = Helper.GetDate(pension.pension_datelive);
  1686. finance_pensions.dateexpire = Helper.GetDate(pension.pension_expires);
  1687. finance_pensions.fundvalue = pension.pension_fundvalue;
  1688.  
  1689. //Added Client Fee 27-10-2014
  1690. finance_pensions.ClientFee = pension.pension_Clientfee;
  1691. //********************************
  1692. finance_pensions.isPolicyForOtherApplicant = pension.pension_isPolicyForOtherApplicant;
  1693.  
  1694. }
  1695. }
  1696. }
  1697. else
  1698. {
  1699. if (pension.pension_status != 0)
  1700. {
  1701. //finance
  1702. tblfinance newfinance = new tblfinance();
  1703. newfinance.contact = _address.contactid;
  1704. newfinance.type = (int)FinanceTypes.Pensions;
  1705. newfinance.status = pension.pension_status;
  1706. newfinance.subtype = pension.pension_riskprofile;
  1707. newfinance.policyreference = pension.pension_policyref;
  1708. newfinance.followup = Helper.GetFollowUp(pension.pension_date, pension.pension_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : pension.pension_time);
  1709. newfinance.provider = pension.pension_provider;
  1710. newfinance.amount = pension.pension_amount;
  1711. newfinance.premium = pension.pension_contributors;
  1712. newfinance.commission = pension.pension_commission;
  1713. newfinance.details = null;
  1714. newfinance.updated = System.DateTime.Now;
  1715. newfinance.created = System.DateTime.Now;
  1716. newfinance.datelive = Helper.GetDate(pension.pension_datelive);
  1717. newfinance.dateexpire = Helper.GetDate(pension.pension_expires);
  1718. newfinance.fundvalue = pension.pension_fundvalue;
  1719.  
  1720.  
  1721. //Added Client Fee 27-10-2014
  1722. newfinance.ClientFee = pension.pension_Clientfee;
  1723. //********************************
  1724. newfinance.isPolicyForOtherApplicant = pension.pension_isPolicyForOtherApplicant;
  1725.  
  1726. ctx.tblfinances.Add(newfinance);
  1727.  
  1728. //insert
  1729. tblfinance_pension newfinance_pension = new tblfinance_pension();
  1730. newfinance_pension.incomereq = pension.pension_incomereq;
  1731. ctx.tblfinance_pension.Add(newfinance_pension);
  1732. }
  1733. }
  1734. }
  1735. else
  1736. {
  1737. if (pension.pension_status != 0)
  1738. {
  1739. //finance
  1740. tblfinance newfinance = new tblfinance();
  1741. newfinance.contact = _address.contactid;
  1742. newfinance.type = (int)FinanceTypes.Pensions;
  1743. newfinance.status = pension.pension_status;
  1744. newfinance.subtype = pension.pension_riskprofile;
  1745. newfinance.policyreference = pension.pension_policyref;
  1746. newfinance.followup = Helper.GetFollowUp(pension.pension_date, pension.pension_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : pension.pension_time);
  1747. newfinance.provider = pension.pension_provider;
  1748. newfinance.amount = pension.pension_amount;
  1749. newfinance.premium = pension.pension_contributors;
  1750. newfinance.commission = pension.pension_commission;
  1751. newfinance.details = null;
  1752. newfinance.updated = System.DateTime.Now;
  1753. newfinance.created = System.DateTime.Now;
  1754. newfinance.datelive = Helper.GetDate(pension.pension_datelive);
  1755. newfinance.dateexpire = Helper.GetDate(pension.pension_expires);
  1756. newfinance.fundvalue = pension.pension_fundvalue;
  1757.  
  1758. //Added Client Fee 27-10-2014
  1759. newfinance.ClientFee = pension.pension_Clientfee;
  1760. //********************************
  1761. newfinance.isPolicyForOtherApplicant = pension.pension_isPolicyForOtherApplicant;
  1762.  
  1763. ctx.tblfinances.Add(newfinance);
  1764.  
  1765. //insert
  1766. tblfinance_pension newfinance_pension = new tblfinance_pension();
  1767. newfinance_pension.incomereq = pension.pension_incomereq;
  1768. ctx.tblfinance_pension.Add(newfinance_pension);
  1769. }
  1770. }
  1771.  
  1772. if (pension.pension_status != 0)
  1773. {
  1774.  
  1775. financeId = pension.pension_financeid;
  1776. status = pension.pension_status;
  1777.  
  1778.  
  1779. if (pension.pension_notes != null)
  1780. {
  1781. tblnote pensionnote = new tblnote();
  1782. pensionnote.financeid = pension.pension_financeid;
  1783. pensionnote.contact = _address.contactid;
  1784. pensionnote.note = pension.pension_notes.Length > 8000 ? pension.pension_notes.Substring(0, 7999) : pension.pension_notes;
  1785.  
  1786. pensionnote.created = System.DateTime.Now;
  1787. pensionnote.UserId = userid;
  1788. pensionnote.allowInReport = mortage_notes_allow;
  1789. ctx.tblnotes.Add(pensionnote);
  1790.  
  1791. }
  1792. else
  1793. {
  1794. if (_chkPensionUpdate)
  1795. {
  1796. tblnote pensionnoteforstatus = new tblnote();
  1797. pensionnoteforstatus.financeid = pension.pension_financeid;
  1798. pensionnoteforstatus.contact = _address.contactid;
  1799. if (oldStatusId != 0)
  1800. {
  1801. pensionnoteforstatus.note = string.Format("Status changes from {0} to {1} on {2} at {3} by {4}", ctx.tblfinancestatus.Where(x => x.statusid == oldStatusId).Select(x => x.name).FirstOrDefault(), ctx.tblfinancestatus.Where(x => x.statusid == pension.pension_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1802. }
  1803. else
  1804. {
  1805. pensionnoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == pension.pension_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  1806.  
  1807. }
  1808. pensionnoteforstatus.created = System.DateTime.Now;
  1809. pensionnoteforstatus.UserId = userid;
  1810. ctx.tblnotes.Add(pensionnoteforstatus);
  1811. }
  1812. }
  1813.  
  1814. }
  1815. ctx.SaveChanges();
  1816. }
  1817. #endregion
  1818.  
  1819. #region finance_Investment
  1820. // Investment
  1821. foreach (Investment investment in InvestmentList)
  1822. {
  1823. int oldStatusId;
  1824. var _chkInvestUpdate = CheckStatusUpdate(investment.investment_financeid, investment.investment_status, out oldStatusId);
  1825. if (investment.investment_financeid != 0 && investment.investment_status != 0)
  1826. {
  1827. var queryInvestment = (from c in ctx.tblcontacts
  1828. join ft in ctx.tblfinances on c.contactid equals ft.contact
  1829. join finv in ctx.tblfinance_investments on ft.financeid equals finv.financeid
  1830. where ft.contact == _address.contactid && ft.type == investment.investment_financetype && ft.financeid == investment.investment_financeid
  1831. select new { finv }).FirstOrDefault();
  1832. if (queryInvestment != null || investment.investment_financeid > 0)
  1833. {
  1834. tblfinance_investments investmentnew = ctx.tblfinance_investments.Where(x => x.financeid == queryInvestment.finv.financeid).FirstOrDefault();
  1835. if (investmentnew == null)
  1836. {
  1837. tblfinance_investments newfinanceinvestment = new tblfinance_investments();
  1838. newfinanceinvestment.financeid = investment.investment_financeid;
  1839. ctx.tblfinance_investments.Add(newfinanceinvestment);
  1840. }
  1841.  
  1842. tblfinance finance_investment = ctx.tblfinances.Where(x => x.financeid == queryInvestment.finv.financeid).FirstOrDefault();
  1843. if (finance_investment != null)
  1844. {
  1845. var flag = false;
  1846. if (finance_investment.status != investment.investment_status)
  1847. {
  1848. flag = true;
  1849. }
  1850. else if (finance_investment.subtype != investment.investment_riskprofiles)
  1851. {
  1852. flag = true;
  1853. }
  1854. else if (finance_investment.policyreference != investment.investment_policyref)
  1855. {
  1856. flag = true;
  1857. }
  1858. else if (finance_investment.followup != Helper.GetFollowUp(investment.investment_date, investment.investment_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : investment.investment_time))
  1859. {
  1860. flag = true;
  1861. }
  1862. else if (finance_investment.provider != investment.investment_provider)
  1863. {
  1864. flag = true;
  1865. }
  1866. else if (finance_investment.amount != investment.investment_amount)
  1867. {
  1868. flag = true;
  1869. }
  1870. else if (finance_investment.premium != investment.investment_contributors)
  1871. {
  1872. flag = true;
  1873. }
  1874. else if (finance_investment.commission != investment.investment_commission)
  1875. {
  1876. flag = true;
  1877. }
  1878. else if (finance_investment.datelive != Helper.GetDate(investment.investment_datelive))
  1879. {
  1880. flag = true;
  1881. }
  1882. else if (finance_investment.dateexpire != Helper.GetDate(investment.investment_expires))
  1883. {
  1884. flag = true;
  1885. }
  1886. else if (finance_investment.fundvalue != investment.investment_fundvalue)
  1887. {
  1888. flag = true;
  1889. }
  1890. else if (finance_investment.isPolicyForOtherApplicant != investment.investment_isPolicyForOtherApplicant)
  1891. {
  1892. flag = true;
  1893. }
  1894. else if (finance_investment.ClientFee != null || investment.investment_Clientfee != 0)
  1895. {
  1896. if (finance_investment.ClientFee != investment.investment_Clientfee)
  1897. {
  1898. flag = true;
  1899. }
  1900. }
  1901. else
  1902. {
  1903. flag = false;
  1904. }
  1905. if (flag)
  1906. {
  1907. finance_investment.status = investment.investment_status;
  1908. finance_investment.subtype = investment.investment_riskprofiles;
  1909. finance_investment.policyreference = investment.investment_policyref;
  1910. finance_investment.followup = Helper.GetFollowUp(investment.investment_date, investment.investment_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : investment.investment_time);
  1911. finance_investment.provider = investment.investment_provider;
  1912. finance_investment.amount = investment.investment_amount;
  1913. finance_investment.premium = investment.investment_contributors;
  1914. finance_investment.commission = investment.investment_commission;
  1915. finance_investment.details = null;
  1916. finance_investment.updated = System.DateTime.Now;
  1917. finance_investment.datelive = Helper.GetDate(investment.investment_datelive);
  1918. finance_investment.dateexpire = Helper.GetDate(investment.investment_expires);
  1919. finance_investment.fundvalue = investment.investment_fundvalue;
  1920.  
  1921. //Added Client Fee 27-10-2014
  1922. finance_investment.ClientFee = investment.investment_Clientfee;
  1923. //********************************
  1924. finance_investment.isPolicyForOtherApplicant = investment.investment_isPolicyForOtherApplicant;
  1925.  
  1926. }
  1927. }
  1928. }
  1929. else
  1930. {
  1931. if (investment.investment_status != 0)
  1932. {
  1933. //insert
  1934. tblfinance_investments newfinanceinvestment = new tblfinance_investments();
  1935. ctx.tblfinance_investments.Add(newfinanceinvestment);
  1936. //finance
  1937. tblfinance newfinance = new tblfinance();
  1938. newfinance.contact = _address.contactid;
  1939. newfinance.type = (int)FinanceTypes.Investments;
  1940. newfinance.status = investment.investment_status;
  1941. newfinance.subtype = investment.investment_riskprofiles;
  1942. newfinance.policyreference = investment.investment_policyref;
  1943. newfinance.followup = Helper.GetFollowUp(investment.investment_date, investment.investment_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : investment.investment_time);
  1944. newfinance.provider = investment.investment_provider;
  1945. newfinance.amount = investment.investment_amount;
  1946. newfinance.premium = investment.investment_contributors;
  1947. newfinance.commission = investment.investment_commission;
  1948. newfinance.details = null;
  1949. newfinance.updated = System.DateTime.Now;
  1950. newfinance.created = System.DateTime.Now;
  1951. newfinance.datelive = Helper.GetDate(investment.investment_datelive);
  1952. newfinance.dateexpire = Helper.GetDate(investment.investment_expires);
  1953. newfinance.fundvalue = investment.investment_fundvalue;
  1954.  
  1955. //Added Client Fee 27-10-2014
  1956. newfinance.ClientFee = investment.investment_Clientfee;
  1957. //********************************
  1958. newfinance.isPolicyForOtherApplicant = investment.investment_isPolicyForOtherApplicant;
  1959.  
  1960. ctx.tblfinances.Add(newfinance);
  1961. }
  1962. }
  1963. }
  1964. else
  1965. {
  1966. if (investment.investment_status != 0)
  1967. {
  1968. //insert
  1969. tblfinance_investments newfinanceinvestment = new tblfinance_investments();
  1970. ctx.tblfinance_investments.Add(newfinanceinvestment);
  1971. //finance
  1972. tblfinance newfinance = new tblfinance();
  1973. newfinance.contact = _address.contactid;
  1974. newfinance.type = (int)FinanceTypes.Investments;
  1975. newfinance.status = investment.investment_status;
  1976. newfinance.subtype = investment.investment_riskprofiles;
  1977. newfinance.policyreference = investment.investment_policyref;
  1978. newfinance.followup = Helper.GetFollowUp(investment.investment_date, investment.investment_time == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : investment.investment_time);
  1979. newfinance.provider = investment.investment_provider;
  1980. newfinance.amount = investment.investment_amount;
  1981. newfinance.premium = investment.investment_contributors;
  1982. newfinance.commission = investment.investment_commission;
  1983. newfinance.details = null;
  1984. newfinance.updated = System.DateTime.Now;
  1985. newfinance.created = System.DateTime.Now;
  1986. newfinance.datelive = Helper.GetDate(investment.investment_datelive);
  1987. newfinance.dateexpire = Helper.GetDate(investment.investment_expires);
  1988. newfinance.fundvalue = investment.investment_fundvalue;
  1989.  
  1990. //Added Client Fee 27-10-2014
  1991. newfinance.ClientFee = investment.investment_Clientfee;
  1992. //********************************
  1993. newfinance.isPolicyForOtherApplicant = investment.investment_isPolicyForOtherApplicant;
  1994.  
  1995. ctx.tblfinances.Add(newfinance);
  1996. }
  1997. }
  1998.  
  1999. if (investment.investment_status != 0)
  2000. {
  2001.  
  2002. financeId = investment.investment_financeid;
  2003. status = investment.investment_status;
  2004.  
  2005.  
  2006. if (investment.investment_notes != null)
  2007. {
  2008. tblnote investmentnote = new tblnote();
  2009. investmentnote.financeid = investment.investment_financeid;
  2010. investmentnote.contact = _address.contactid;
  2011. investmentnote.note = investment.investment_notes.Length > 8000 ? investment.investment_notes.Substring(0, 7999) : investment.investment_notes;
  2012. investmentnote.created = System.DateTime.Now;
  2013. investmentnote.UserId = userid;
  2014. investmentnote.allowInReport=mortage_notes_allow;
  2015. ctx.tblnotes.Add(investmentnote);
  2016.  
  2017. }
  2018. else
  2019. {
  2020. if (_chkInvestUpdate)
  2021. {
  2022. tblnote investmentnoteforstatus = new tblnote();
  2023. investmentnoteforstatus.financeid = investment.investment_financeid;
  2024. investmentnoteforstatus.contact = _address.contactid;
  2025. if (oldStatusId != 0)
  2026. {
  2027. investmentnoteforstatus.note = string.Format("Status changes from {0} to {1} on {2} at {3} by {4}", ctx.tblfinancestatus.Where(x => x.statusid == oldStatusId).Select(x => x.name).FirstOrDefault(), ctx.tblfinancestatus.Where(x => x.statusid == investment.investment_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  2028. }
  2029. else
  2030. {
  2031. investmentnoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == investment.investment_status).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userid).Select(x => x.brokername).FirstOrDefault());
  2032. }
  2033. investmentnoteforstatus.created = System.DateTime.Now;
  2034. investmentnoteforstatus.UserId = userid;
  2035. ctx.tblnotes.Add(investmentnoteforstatus);
  2036.  
  2037. }
  2038. }
  2039. }
  2040. ctx.SaveChanges();
  2041. }
  2042. #endregion
  2043.  
  2044. }
  2045. catch (Exception ex)
  2046. {
  2047. Helper.ErrorLog(ex.InnerException, "Q_Application", "UpdateApplicationform", ex.Message);
  2048. throw;
  2049. }
  2050. }
  2051.  
  2052.  
  2053. #endregion [Update Application Form 2015-03-31]
  2054.  
  2055. #region [Get application form details 2015-03-31]
  2056.  
  2057. /// <summary>
  2058. /// Get application form details
  2059. /// </summary>
  2060. /// <param name="appid"></param>
  2061. /// <param name="contactid"></param>
  2062. /// <param name="financeid"></param>
  2063. /// <returns></returns>
  2064. public ApplicationForm GetApplicationForm(int appid, int contactid, int financeid)
  2065. {
  2066.  
  2067. int count = 1;
  2068. ApplicationForm formdata = new ApplicationForm();
  2069. int mortage_statusid = GetFinanceType(FinanceType.Mortgages);
  2070. int life_statusid = GetFinanceType(FinanceType.Life);
  2071. int buildingcontent_statusid = GetFinanceType(FinanceType.BuildingsOrContents);
  2072. int pension_statusid = GetFinanceType(FinanceType.Pensions);
  2073. int investment_statusid = GetFinanceType(FinanceType.Investments);
  2074. AddProducts addproducts = new AddProducts();
  2075. #region contact
  2076. //Contact
  2077. tblcontact contact = ctx.tblcontacts.Where(x => x.contactid == contactid).FirstOrDefault();
  2078.  
  2079. if (contact != null)
  2080. {
  2081. formdata.FirstName = contact.firstname;
  2082. formdata.LastName = contact.lastname;
  2083. formdata.Email = contact.email;
  2084. formdata.Telephone = contact.telephone;
  2085. formdata.Email = contact.email;
  2086. formdata.Method = contact.method;
  2087. formdata.Broker = contact.broker ?? 0;
  2088. formdata.ContactUserName = contact.ContactUserName ?? "";
  2089. formdata.ContactPassword = contact.ContactPassword ?? "";
  2090. formdata.Companyname = contact.tblaccount.companyname;
  2091. if (contact.offerqualified.HasValue && contact.offerqualified != null)
  2092. {
  2093. formdata.IsOfferQualified = contact.offerqualified.Value;
  2094. }
  2095. if (contact.slamet != null)
  2096. {
  2097. if (contact.slamet.Value == true)
  2098. formdata.ContactIn2Hrs = 1;
  2099. else
  2100. formdata.ContactIn2Hrs = 2;
  2101. }
  2102. else
  2103. formdata.ContactIn2Hrs = 0;
  2104. formdata.clientname = contact.firstname + " " + contact.lastname;
  2105. formdata.negotiator = GetNegotiatorNameById(contact.NegrefId);//contact.negref;
  2106. //added on 2015-03-03
  2107. formdata.branch = GetBranchNameById(contact.BranchId);//contact.branch;
  2108. formdata.account = Convert.ToInt32(contact.account);
  2109. formdata.parentContactId = (contact.parentContactId ?? 0).ToString();
  2110. var parentContact = ctx.tblcontacts.Where(x => x.contactid == contact.parentContactId).Select(x =>
  2111. new
  2112. {
  2113. x.firstname,
  2114. x.lastname,
  2115. x.email,
  2116. x.telephone
  2117. }).FirstOrDefault();
  2118. if (parentContact != null)
  2119. formdata.parentContactName = (parentContact.firstname ?? "") + " " + (parentContact.lastname ?? "") + ", " + parentContact.email.GetStringForReferral() + ", " + parentContact.telephone.GetStringForReferral();
  2120. }
  2121. #endregion
  2122.  
  2123. #region Mortage/Finance
  2124. var mortageids = ctx.tblfinancestatus.Where(x => x.type == mortage_statusid && x.statusid != 34).ToList().Select(s => s.statusid);
  2125.  
  2126.  
  2127. var mortagequery = ctx.tblfinances.Where(x => mortageids.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2128.  
  2129. var FirstFinanceId = 0;
  2130. if (mortagequery.Count() > 0)
  2131. FirstFinanceId = (int)mortagequery[0];
  2132.  
  2133. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2134. int indexOfCurrentF = mortagequery.IndexOf(financeid);
  2135. if (indexOfCurrentF > -1)
  2136. FirstFinanceId = mortagequery[indexOfCurrentF];
  2137. //End:- Updated On 2-4-2014 regarding tab and product selection
  2138.  
  2139. tblfinance financedata = ctx.tblfinances.Where(x => x.financeid == FirstFinanceId).FirstOrDefault();
  2140. //Bind Mortgage tab from finance table
  2141. if (financedata != null)
  2142. {
  2143.  
  2144. Mortgage mortgage = new Mortgage();
  2145. mortgage.mortage_financeid = financedata.financeid;
  2146. formdata.contactid = financedata.contact ?? 0;
  2147. formdata.AppicationType = financedata.type ?? 0;
  2148. mortgage.mortage_mortgagesubtype = financedata.status ?? 0;
  2149. mortgage.mortage_mortageaccountno = financedata.policyreference;
  2150. mortgage.mortage_lender = financedata.provider;
  2151. mortgage.mortage_amount = financedata.amount.HasValue ? (decimal?)Math.Round(financedata.amount.Value, 2) : null;
  2152. mortgage.mortage_premium = financedata.premium.HasValue ? (decimal?)Math.Round(financedata.premium.Value, 2) : null;
  2153. mortgage.mortage_commission = financedata.commission.HasValue ? (decimal?)Math.Round(financedata.commission.Value, 2) : null;
  2154. mortgage.mortage_type = financedata.subtype ?? 0;
  2155. mortgage.mortage_datelive = Helper.GetDateString(financedata.datelive);
  2156. mortgage.mortage_expires = Helper.GetDateString(financedata.dateexpire);
  2157. mortgage.mortage_date = Helper.GetDateString(financedata.followup);
  2158. mortgage.mortage_time = financedata.followup.HasValue ? financedata.followup.Value.ToString("HH:mm") : string.Empty;
  2159. mortgage.mortage_contactid = financedata.contact ?? 0;
  2160. mortgage.mortage_AppicationType = financedata.type ?? 0;
  2161. mortgage.mortgage_address1 = financedata.Address1;
  2162. mortgage.mortgage_address2 = financedata.Address2;
  2163. mortgage.mortgage_town = financedata.Town;
  2164. mortgage.mortgage_county = financedata.County;
  2165. mortgage.mortgage_postcode = financedata.Postcode;
  2166. mortgage.mortgage_Clientfee = financedata.ClientFee ?? 0;
  2167. mortgage.mortgage_SubFinanceStatus = financedata.subFinanceStatus ?? 0;
  2168. //from mortage table
  2169. tblfinance_mortgage financemortage = ctx.tblfinance_mortgage.Where(x => x.financeid == FirstFinanceId).FirstOrDefault();
  2170. if (financemortage != null)
  2171. {
  2172. mortgage.mortage_reason = financemortage.mortgagesubtype ?? 0;
  2173. mortgage.mortage_financetype = financedata.status ?? 0;
  2174. mortgage.mortage_solicitor = financemortage.solicitor;
  2175. mortgage.mortage_deposit = financemortage.deposit.HasValue ? (decimal?)Math.Round(financemortage.deposit.Value, 2) : null;
  2176. mortgage.mortage_income = financemortage.income.HasValue ? (decimal?)Math.Round(financemortage.income.Value, 2) : null;
  2177. mortgage.mortage_rterm = financemortage.rterm ?? 0;
  2178. mortgage.mortage_completed = Helper.GetDateString(financemortage.completed);
  2179. mortgage.mortage_exchanged = financemortage.exchanged.HasValue ? Helper.GetDateString(financemortage.exchanged.Value) : string.Empty;
  2180. mortgage.mortage_remortgagedate = financemortage.remortgagedate.HasValue ? Helper.GetDateString(financemortage.remortgagedate.Value) : string.Empty;
  2181. //Added on 2014-11-24
  2182. mortgage.mortgage_remortgagecontact = financemortage.remortgagecontact.HasValue ? Helper.GetDateString(financemortage.remortgagecontact.Value) : string.Empty;
  2183. mortgage.mortage_propertyvalue = financemortage.purchase.HasValue ? (decimal?)Math.Round(financemortage.purchase.Value, 2) : null;
  2184. mortgage.mortage_financetype = GetFinanceType(FinanceType.Mortgages);
  2185. mortgage.mortage_rate = financemortage.Rate;
  2186. mortgage.RateType = financemortage.RateType ?? 0;
  2187. int index = mortagequery.IndexOf(financedata.financeid) + 1;
  2188. mortgage.Mortage_Name = string.Format("{0}{1}", index, index == 1 ? "st Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 2 ? "nd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 3 ? "rd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : "th Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid));
  2189. }
  2190. else
  2191. {
  2192.  
  2193. mortgage.mortage_financetype = GetFinanceType(FinanceType.Mortgages);
  2194. }
  2195. formdata.Mortgage = mortgage;
  2196.  
  2197. }
  2198.  
  2199.  
  2200. List<financeID> mids = new List<financeID>();
  2201.  
  2202. if (mortagequery.Count > 0)
  2203. {
  2204. count = 1;
  2205. foreach (int id in mortagequery)
  2206. {
  2207. financeID _financeid = new financeID();
  2208. _financeid.financeid = id;
  2209. if (count == 1)
  2210. {
  2211. _financeid.name = count + "st Mortgage" + DisplayAddressAndLender(_financeid.financeid);
  2212. }
  2213. else if (count == 2)
  2214. {
  2215. _financeid.name = count + "nd Mortgage" + DisplayAddressAndLender(_financeid.financeid);
  2216. }
  2217. else if (count == 3)
  2218. {
  2219. _financeid.name = count + "rd Mortgage" + DisplayAddressAndLender(_financeid.financeid);
  2220. }
  2221. else
  2222. {
  2223. _financeid.name = count + "th Mortgage" + DisplayAddressAndLender(_financeid.financeid);
  2224. }
  2225. mids.Add(_financeid);
  2226. addproducts.MortgageProduct = mids;
  2227. count = count + 1;
  2228.  
  2229. }
  2230. }
  2231. else
  2232. {
  2233. financeID _financeid = new financeID();
  2234. _financeid.financeid = 0;
  2235. _financeid.name = "1st Mortgage";
  2236. mids.Add(_financeid);
  2237. addproducts.MortgageProduct = mids;
  2238. }
  2239.  
  2240. #endregion
  2241.  
  2242. #region Life
  2243. //Life
  2244. appid = 2;
  2245. var ids = ctx.tblfinancestatus.Where(x => x.type == life_statusid && x.statusid != 35).ToList().Select(s => s.statusid);
  2246.  
  2247. var lifegequery = ctx.tblfinances.Where(x => ids.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2248. int fid = 0;
  2249.  
  2250. if (lifegequery.Count > 0)
  2251. {
  2252. fid = (int)lifegequery[0];
  2253. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2254. int indexOfCurrentFForLife = lifegequery.IndexOf(financeid);
  2255. if (indexOfCurrentFForLife > -1)
  2256. fid = lifegequery[indexOfCurrentFForLife];
  2257. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2258.  
  2259. tblfinance_insurance finance_insurance = ctx.tblfinance_insurance.Where(x => x.financeid == fid).FirstOrDefault();
  2260. if (finance_insurance == null)
  2261. {
  2262. //insert
  2263. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  2264. newfinance_insurance.financeid = fid;
  2265. newfinance_insurance.smoker = null;
  2266. newfinance_insurance.height = null;
  2267. newfinance_insurance.weight = null;
  2268. newfinance_insurance.doctor = null;
  2269. ctx.tblfinance_insurance.Add(newfinance_insurance);
  2270. ctx.SaveChanges();
  2271. }
  2272. }
  2273.  
  2274. var query = (from c in ctx.tblcontacts
  2275. join ft in ctx.tblfinances.Where(s => ids.Contains(s.status.Value)) on c.contactid equals ft.contact
  2276. join fi in ctx.tblfinance_insurance on ft.financeid equals fi.financeid
  2277. where ft.contact == contactid && ft.type == appid && ft.financeid == fid
  2278. select new { ft, fi, c }).FirstOrDefault();
  2279.  
  2280.  
  2281.  
  2282.  
  2283.  
  2284. if (query != null)
  2285. {
  2286. Life life = new Life();
  2287. life.life_financeid = query.ft.financeid;
  2288. life.life_status = query.ft.status ?? 0;
  2289. life.life_provider = query.ft.provider;
  2290. life.life_policyref = query.ft.policyreference;
  2291. life.life_amount = query.ft.amount.HasValue ? (decimal?)Math.Round(query.ft.amount.Value, 2) : null;
  2292. life.life_commission = query.ft.commission.HasValue ? (decimal?)Math.Round(query.ft.commission.Value, 2) : null;
  2293. life.life_policytype = query.ft.subtype ?? 0;
  2294. life.life_premium = query.ft.premium.HasValue ? (decimal?)Math.Round(query.ft.premium.Value, 2) : null;
  2295. life.life_height = query.fi.height;
  2296. life.life_weight = query.fi.weight;
  2297. life.life_issmoker = query.fi.smoker.HasValue ? query.fi.smoker.Value : false;
  2298. life.life_doctor = query.fi.doctor;
  2299. life.life_medical = query.ft.details;
  2300. life.life_datelive = Helper.GetDateString(query.ft.datelive);
  2301. life.life_expires = Helper.GetDateString(query.ft.dateexpire);
  2302. life.life_date = query.ft.followup.HasValue ? Helper.GetDateString(query.ft.followup) : string.Empty;
  2303. life.life_time = query.ft.followup.HasValue ? query.ft.followup.Value.ToString("HH:mm") : string.Empty;
  2304. life.life_financetype = GetFinanceType(FinanceType.Life);
  2305. life.life_contactid = financedata.contact ?? 0;
  2306. life.life_AppicationType = financedata.type ?? 0;
  2307. int index = lifegequery.IndexOf(query.ft.financeid) + 1;
  2308. life.life_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(life.life_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(life.life_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(life.life_financeid) : "th Policy" + DisplayAddressAndLender(life.life_financeid));
  2309. //Added New Client Fee 27-10-2014
  2310. life.life_Clientfee = query.ft.ClientFee ?? 0;
  2311. //******************************
  2312. //Added on 03-04-2015
  2313. life.life_isPolicyForOtherApplicant = query.ft.isPolicyForOtherApplicant.HasValue ? query.ft.isPolicyForOtherApplicant.Value : false;
  2314.  
  2315. formdata.Life = life;
  2316. }
  2317. else
  2318. {
  2319. Life life = new Life();
  2320. life.life_financetype = GetFinanceType(FinanceType.Life);
  2321. formdata.Life = life;
  2322. }
  2323.  
  2324.  
  2325. List<financeID> _lifes = new List<financeID>();
  2326.  
  2327. if (lifegequery.Count > 0)
  2328. {
  2329. count = 1;
  2330. foreach (int id in lifegequery)
  2331. {
  2332. financeID _financeid = new financeID();
  2333. _financeid.financeid = id;
  2334. if (count == 1)
  2335. {
  2336. _financeid.name = count + "st Policy" + DisplayAddressAndLender(_financeid.financeid);
  2337. }
  2338. else if (count == 2)
  2339. {
  2340. _financeid.name = count + "nd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2341. }
  2342. else if (count == 3)
  2343. {
  2344. _financeid.name = count + "rd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2345. }
  2346. else
  2347. {
  2348. _financeid.name = count + "th Policy" + DisplayAddressAndLender(_financeid.financeid);
  2349. }
  2350. _lifes.Add(_financeid);
  2351. addproducts.LifeProduct = _lifes;
  2352. count = count + 1;
  2353. }
  2354. }
  2355. else
  2356. {
  2357. financeID _financeid = new financeID();
  2358. _financeid.financeid = 0;
  2359. _financeid.name = "1st Policy";
  2360. _lifes.Add(_financeid);
  2361. addproducts.LifeProduct = _lifes;
  2362. }
  2363. #endregion
  2364.  
  2365. #region Building/Content
  2366. //Building Contents
  2367. appid = 3;
  2368. var idBuildings = ctx.tblfinancestatus.Where(x => x.type == buildingcontent_statusid && x.statusid != 36).ToList().Select(s => s.statusid);
  2369.  
  2370.  
  2371.  
  2372. var builingquery = ctx.tblfinances.Where(x => idBuildings.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2373.  
  2374. int fidB = 0;
  2375. if (builingquery.Count > 0)
  2376. {
  2377. fidB = (int)builingquery[0];
  2378.  
  2379. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2380. int indexOfCurrentFForbuilding = builingquery.IndexOf(financeid);
  2381. if (indexOfCurrentFForbuilding > -1)
  2382. fidB = builingquery[indexOfCurrentFForbuilding];
  2383. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2384.  
  2385. tblfinance_buildings buildingcontent = ctx.tblfinance_buildings.Where(x => x.financeid == fidB).FirstOrDefault();
  2386. if (buildingcontent == null)
  2387. {
  2388. //insert
  2389. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  2390. newfinance_building.rebuild = null;
  2391. newfinance_building.alarm = null;
  2392. newfinance_building.financeid = fidB;
  2393. ctx.tblfinance_buildings.Add(newfinance_building);
  2394. ctx.SaveChanges();
  2395. }
  2396. }
  2397.  
  2398. var queryBuilding = (from c in ctx.tblcontacts
  2399. join ft in ctx.tblfinances.Where(s => idBuildings.Contains(s.status.Value)) on c.contactid equals ft.contact
  2400. join fb in ctx.tblfinance_buildings on ft.financeid equals fb.financeid
  2401. where ft.contact == contactid && ft.type == appid && ft.financeid == fidB
  2402. select new { ft, fb, c }).FirstOrDefault();
  2403.  
  2404.  
  2405. if (queryBuilding != null)
  2406. {
  2407. BuildingContent buildingcontent = new BuildingContent();
  2408. buildingcontent.buildingcontent_financeid = queryBuilding.ft.financeid;
  2409. buildingcontent.buildingcontent_status = queryBuilding.ft.status ?? 0;
  2410. buildingcontent.buildingcontent_provider = queryBuilding.ft.provider;
  2411. buildingcontent.buildingcontent_policyref = queryBuilding.ft.policyreference;
  2412. buildingcontent.buildingcontent_amount = queryBuilding.ft.amount.HasValue ? (decimal?)Math.Round(queryBuilding.ft.amount.Value, 2) : null;
  2413. buildingcontent.buildingcontent_commission = queryBuilding.ft.commission.HasValue ? (decimal?)Math.Round(queryBuilding.ft.commission.Value, 2) : null;
  2414. buildingcontent.buildingcontent_type = queryBuilding.ft.subtype ?? 0;
  2415. buildingcontent.buildingcontent_premium = queryBuilding.ft.premium.HasValue ? (decimal?)Math.Round(queryBuilding.ft.premium.Value, 2) : null;
  2416. buildingcontent.buildingcontent_rebuildvalue = queryBuilding.fb.rebuild.HasValue ? (decimal?)Math.Round(queryBuilding.fb.rebuild.Value, 2) : null;
  2417. buildingcontent.buildingcontent_isalarmed = queryBuilding.fb.alarm.HasValue ? queryBuilding.fb.alarm.Value : false;
  2418. buildingcontent.buildingcontent_addcover = queryBuilding.ft.details;
  2419. buildingcontent.buildingcontent_datelive = Helper.GetDateString(queryBuilding.ft.datelive); ;
  2420. buildingcontent.buildingcontent_expires = Helper.GetDateString(queryBuilding.ft.dateexpire);
  2421. buildingcontent.buildingcontent_date = Helper.GetDateString(queryBuilding.ft.followup);
  2422. buildingcontent.buildingcontent_time = queryBuilding.ft.followup.HasValue ? queryBuilding.ft.followup.Value.ToString("HH:mm") : string.Empty;
  2423. buildingcontent.buildingcontent_financetype = GetFinanceType(FinanceType.BuildingsOrContents);
  2424. buildingcontent.buildingContent_contactid = financedata.contact ?? 0;
  2425. buildingcontent.buildingContent_AppicationType = financedata.type ?? 0;
  2426. int index = builingquery.IndexOf(queryBuilding.ft.financeid) + 1;
  2427. buildingcontent.buildingcontent_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : "th Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid));
  2428.  
  2429. //Added New Client Fee 27-10-2014
  2430. buildingcontent.building_Clientfee = queryBuilding.ft.ClientFee ?? 0;
  2431. //******************************
  2432. buildingcontent.buildingcontent_isPolicyForOtherApplicant = queryBuilding.ft.isPolicyForOtherApplicant.HasValue ? queryBuilding.ft.isPolicyForOtherApplicant.Value : false;
  2433. formdata.BuildingContent = buildingcontent;
  2434. }
  2435. else
  2436. {
  2437. BuildingContent buildingcontent = new BuildingContent();
  2438. buildingcontent.buildingcontent_financetype = GetFinanceType(FinanceType.BuildingsOrContents);
  2439. formdata.BuildingContent = buildingcontent;
  2440. }
  2441.  
  2442.  
  2443. List<financeID> _buildings = new List<financeID>();
  2444. if (builingquery.Count > 0)
  2445. {
  2446. count = 1;
  2447. foreach (int id in builingquery)
  2448. {
  2449. financeID _financeid = new financeID();
  2450. _financeid.financeid = id;
  2451. if (count == 1)
  2452. {
  2453. _financeid.name = count + "st Policy" + DisplayAddressAndLender(_financeid.financeid);
  2454. }
  2455. else if (count == 2)
  2456. {
  2457. _financeid.name = count + "nd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2458. }
  2459. else if (count == 3)
  2460. {
  2461. _financeid.name = count + "rd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2462. }
  2463. else
  2464. {
  2465. _financeid.name = count + "th Policy" + DisplayAddressAndLender(_financeid.financeid);
  2466. }
  2467. _buildings.Add(_financeid);
  2468. addproducts.BuildingContentProduct = _buildings;
  2469. count = count + 1;
  2470. }
  2471. }
  2472. else
  2473. {
  2474. financeID _financeid = new financeID();
  2475. _financeid.financeid = 0;
  2476. _financeid.name = "1st Policy";
  2477. _buildings.Add(_financeid);
  2478. addproducts.BuildingContentProduct = _buildings;
  2479. }
  2480. #endregion
  2481.  
  2482. #region Pension
  2483. //Pension
  2484. appid = 4;
  2485. var idPensions = ctx.tblfinancestatus.Where(x => x.type == pension_statusid && x.statusid != 37).ToList().Select(s => s.statusid);
  2486. var pensiongquery = ctx.tblfinances.Where(x => idPensions.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2487.  
  2488. int fidP = 0;
  2489. if (pensiongquery.Count > 0)
  2490. {
  2491. fidP = (int)pensiongquery[0];
  2492. int indexOfCurrentFForPension = pensiongquery.IndexOf(financeid);
  2493. if (indexOfCurrentFForPension > -1)
  2494. fidP = pensiongquery[indexOfCurrentFForPension];
  2495. tblfinance_pension buildingcontent = ctx.tblfinance_pension.Where(x => x.financeid == fidP).FirstOrDefault();
  2496. if (buildingcontent == null)
  2497. {
  2498. //insert
  2499. tblfinance_pension newfinance_pension = new tblfinance_pension();
  2500. newfinance_pension.incomereq = null;
  2501. newfinance_pension.financeid = fidP;
  2502. ctx.tblfinance_pension.Add(newfinance_pension);
  2503. ctx.SaveChanges();
  2504. }
  2505. }
  2506.  
  2507. var queryPension = (from c in ctx.tblcontacts
  2508. join ft in ctx.tblfinances.Where(s => idPensions.Contains(s.status.Value)) on c.contactid equals ft.contact
  2509. join fp in ctx.tblfinance_pension on ft.financeid equals fp.financeid
  2510. where ft.contact == contactid && ft.type == appid && ft.financeid == fidP
  2511. select new { ft, fp, c }).FirstOrDefault();
  2512.  
  2513. if (queryPension != null)
  2514. {
  2515. Pensions pension = new Pensions();
  2516. pension.pension_financeid = queryPension.ft.financeid;
  2517. pension.pension_amount = queryPension.ft.amount.HasValue ? (decimal?)Math.Round(queryPension.ft.amount.Value, 2) : null;
  2518. pension.pension_commission = queryPension.ft.commission.HasValue ? (decimal?)Math.Round(queryPension.ft.commission.Value, 2) : null;
  2519. pension.pension_contributors = queryPension.ft.premium.HasValue ? (decimal?)Math.Round(queryPension.ft.premium.Value, 2) : null;
  2520. pension.pension_datelive = Helper.GetDateString(queryPension.ft.datelive);
  2521. pension.pension_expires = Helper.GetDateString(queryPension.ft.dateexpire);
  2522. pension.pension_fundvalue = queryPension.ft.fundvalue.HasValue ? (decimal?)Math.Round(queryPension.ft.fundvalue.Value, 2) : null;
  2523. pension.pension_incomereq = queryPension.fp.incomereq.HasValue ? (decimal?)Math.Round(queryPension.fp.incomereq.Value, 2) : null;
  2524. pension.pension_policyref = queryPension.ft.policyreference;
  2525. pension.pension_provider = queryPension.ft.provider;
  2526. pension.pension_riskprofile = queryPension.ft.subtype ?? 0;
  2527. pension.pension_status = queryPension.ft.status ?? 0;
  2528. pension.pension_date = Helper.GetDateString(queryPension.ft.followup);
  2529. pension.pension_time = queryPension.ft.followup.HasValue ? queryPension.ft.followup.Value.ToString("HH:mm") : string.Empty;
  2530. pension.pension_financetype = GetFinanceType(FinanceType.Pensions);
  2531. pension.pension_contactid = financedata.contact ?? 0;
  2532. pension.pension_AppicationType = financedata.type ?? 0;
  2533. int index = pensiongquery.IndexOf(queryPension.ft.financeid) + 1;
  2534. pension.pension_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(pension.pension_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(pension.pension_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(pension.pension_financeid) : "th Policy" + DisplayAddressAndLender(pension.pension_financeid));
  2535.  
  2536. //Added New Client Fee 27-10-2014
  2537. pension.pension_Clientfee = queryPension.ft.ClientFee ?? 0;
  2538. //******************************
  2539. pension.pension_isPolicyForOtherApplicant = queryPension.ft.isPolicyForOtherApplicant.HasValue ? queryPension.ft.isPolicyForOtherApplicant.Value : false;
  2540. formdata.Pension = pension;
  2541. }
  2542. else
  2543. {
  2544. Pensions pension = new Pensions();
  2545. pension.pension_financetype = GetFinanceType(FinanceType.Pensions);
  2546. formdata.Pension = pension;
  2547. }
  2548.  
  2549.  
  2550. List<financeID> _pensions = new List<financeID>();
  2551. if (pensiongquery.Count > 0)
  2552. {
  2553. count = 1;
  2554. foreach (int id in pensiongquery)
  2555. {
  2556. financeID _financeid = new financeID();
  2557. _financeid.financeid = id;
  2558. if (count == 1)
  2559. {
  2560. _financeid.name = count + "st Policy" + DisplayAddressAndLender(_financeid.financeid);
  2561. }
  2562. else if (count == 2)
  2563. {
  2564. _financeid.name = count + "nd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2565. }
  2566. else if (count == 3)
  2567. {
  2568. _financeid.name = count + "rd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2569. }
  2570. else
  2571. {
  2572. _financeid.name = count + "th Policy" + DisplayAddressAndLender(_financeid.financeid);
  2573. }
  2574. _pensions.Add(_financeid);
  2575. addproducts.PensionProduct = _pensions;
  2576. count = count + 1;
  2577. }
  2578. }
  2579. else
  2580. {
  2581. financeID _financeid = new financeID();
  2582. _financeid.financeid = 0;
  2583. _financeid.name = "1st Policy";
  2584. _pensions.Add(_financeid);
  2585. addproducts.PensionProduct = _pensions;
  2586. }
  2587. #endregion
  2588.  
  2589. #region Investment
  2590. //Investment
  2591. appid = 5;
  2592. var idInvestments = ctx.tblfinancestatus.Where(x => x.type == investment_statusid && x.statusid != 38).ToList().Select(s => s.statusid);
  2593.  
  2594. var investmentquery = ctx.tblfinances.Where(x => idInvestments.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2595.  
  2596.  
  2597. int fidI = 0;
  2598.  
  2599. if (investmentquery.Count > 0)
  2600. {
  2601. fidI = (int)investmentquery[0];
  2602.  
  2603. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2604. int indexOfCurrentFForInvestment = investmentquery.IndexOf(financeid);
  2605. if (indexOfCurrentFForInvestment > -1)
  2606. fidI = investmentquery[indexOfCurrentFForInvestment];
  2607. //Start:- Updated On 2-4-2014 regarding tab and product selection
  2608.  
  2609. tblfinance_investments pinvets = ctx.tblfinance_investments.Where(x => x.financeid == fidI).FirstOrDefault();
  2610. if (pinvets == null)
  2611. {
  2612. //insert
  2613. tblfinance_investments newfinanceinvestment = new tblfinance_investments();
  2614. newfinanceinvestment.financeid = fidI;
  2615. ctx.tblfinance_investments.Add(newfinanceinvestment);
  2616. ctx.SaveChanges();
  2617. }
  2618. }
  2619.  
  2620. var queryInvestment = (from c in ctx.tblcontacts
  2621. join ft in ctx.tblfinances.Where(s => idInvestments.Contains(s.status.Value)) on c.contactid equals ft.contact
  2622. join finv in ctx.tblfinance_investments on ft.financeid equals finv.financeid
  2623. where ft.contact == contactid && ft.type == appid && ft.financeid == fidI
  2624. select new { ft, finv, c }).FirstOrDefault();
  2625.  
  2626.  
  2627. if (queryInvestment != null)
  2628. {
  2629. Investment investment = new Investment();
  2630. investment.investment_financeid = queryInvestment.ft.financeid;
  2631. investment.investment_amount = queryInvestment.ft.amount.HasValue ? (decimal?)Math.Round(queryInvestment.ft.amount.Value, 2) : null;
  2632. investment.investment_commission = queryInvestment.ft.commission.HasValue ? (decimal?)Math.Round(queryInvestment.ft.commission.Value, 2) : null;
  2633. investment.investment_contributors = queryInvestment.ft.premium.HasValue ? (decimal?)Math.Round(queryInvestment.ft.premium.Value, 2) : null;
  2634. investment.investment_date = queryInvestment.ft.followup.HasValue ? Helper.GetDateString(queryInvestment.ft.followup.Value) : string.Empty;
  2635. investment.investment_datelive = queryInvestment.ft.datelive.HasValue ? Helper.GetDateString(queryInvestment.ft.datelive.Value) : string.Empty;
  2636. investment.investment_expires = queryInvestment.ft.dateexpire.HasValue ? Helper.GetDateString(queryInvestment.ft.dateexpire.Value) : string.Empty;
  2637. investment.investment_financetype = GetFinanceType(FinanceType.Investments);
  2638. investment.investment_fundvalue = queryInvestment.ft.fundvalue.HasValue ? (decimal?)Math.Round(queryInvestment.ft.fundvalue.Value, 2) : null;
  2639. investment.investment_policyref = queryInvestment.ft.policyreference != null ? queryInvestment.ft.policyreference : string.Empty;
  2640. investment.investment_provider = queryInvestment.ft.provider != null ? queryInvestment.ft.provider : string.Empty; ;
  2641. investment.investment_riskprofiles = queryInvestment.ft.subtype ?? 0;
  2642. investment.investment_status = queryInvestment.ft.status ?? 0;
  2643. investment.investment_contactid = financedata.contact ?? 0;
  2644. investment.investment_AppicationType = financedata.type ?? 0;
  2645. investment.investment_time = queryInvestment.ft.followup.HasValue ? queryInvestment.ft.followup.Value.ToString("HH:mm") : string.Empty;
  2646. int index = investmentquery.IndexOf(queryInvestment.ft.financeid) + 1;
  2647. investment.Investment_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(investment.investment_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(investment.investment_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(investment.investment_financeid) : "th Policy" + DisplayAddressAndLender(investment.investment_financeid));
  2648.  
  2649. //Added New Client Fee 27-10-2014
  2650. investment.investment_Clientfee = queryInvestment.ft.ClientFee ?? 0;
  2651. //******************************
  2652. investment.investment_isPolicyForOtherApplicant = queryInvestment.ft.isPolicyForOtherApplicant.HasValue ? queryInvestment.ft.isPolicyForOtherApplicant.Value : false;
  2653. formdata.Investment = investment;
  2654. }
  2655. else
  2656. {
  2657. Investment investment = new Investment();
  2658. investment.investment_financetype = GetFinanceType(FinanceType.Investments);
  2659. formdata.Investment = investment;
  2660. }
  2661.  
  2662. List<financeID> _investments = new List<financeID>();
  2663. if (investmentquery.Count > 0)
  2664. {
  2665. count = 1;
  2666. foreach (int id in investmentquery)
  2667. {
  2668. financeID _financeid = new financeID();
  2669. _financeid.financeid = id;
  2670. if (count == 1)
  2671. {
  2672. _financeid.name = count + "st Policy" + DisplayAddressAndLender(_financeid.financeid);
  2673. }
  2674. else if (count == 2)
  2675. {
  2676. _financeid.name = count + "nd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2677. }
  2678. else if (count == 3)
  2679. {
  2680. _financeid.name = count + "rd Policy" + DisplayAddressAndLender(_financeid.financeid);
  2681. }
  2682. else
  2683. {
  2684. _financeid.name = count + "th Policy" + DisplayAddressAndLender(_financeid.financeid);
  2685. }
  2686. _investments.Add(_financeid);
  2687. addproducts.InvestmentProduct = _investments;
  2688. count = count + 1;
  2689. }
  2690. }
  2691. else
  2692. {
  2693. financeID _financeid = new financeID();
  2694. _financeid.financeid = 0;
  2695. _financeid.name = "1st Policy";
  2696. _investments.Add(_financeid);
  2697. addproducts.InvestmentProduct = _investments;
  2698. }
  2699. #endregion
  2700.  
  2701. #region Address
  2702. //Address
  2703. int addressid = ctx.tblcontacts.Where(x => x.contactid == contactid).Select(x => x.address.Value).FirstOrDefault();
  2704. var address = ctx.tbladdresses.Where(x => x.addressid == addressid).FirstOrDefault();
  2705. if (address != null)
  2706. {
  2707. Address addr = new Address();
  2708. addr.address_line1 = address.address1;
  2709. addr.address_line2 = address.address2;
  2710. addr.address_city = address.town;
  2711. addr.address_postcode = address.postcode;
  2712. if (contact != null)
  2713. {
  2714. addr.ninnumber = contact.ninumber;
  2715. addr.dob = contact.dob.HasValue ? Helper.GetDateString(contact.dob.Value) : string.Empty;
  2716. addr.jobtitle = contact.jobtitle;
  2717. addr.employer = contact.employer;
  2718. addr.accountant = contact.accountdetails;
  2719. addr.weddinganniversary = contact.married.HasValue ? Helper.GetDateString(contact.married.Value) : string.Empty;
  2720. addr.dependents = contact.dependants;
  2721. }
  2722.  
  2723. formdata.Address = addr;
  2724. }
  2725. #endregion
  2726.  
  2727. #region notes
  2728. #endregion
  2729. formdata.addproducts = addproducts;
  2730. return formdata;
  2731. }
  2732.  
  2733. #region [Get branch and negotiator names by Id]
  2734.  
  2735. /// <summary>
  2736. /// Get negotiator name by id
  2737. /// </summary>
  2738. /// <param name="negotiatorId"></param>
  2739. /// <returns></returns>
  2740. public string GetNegotiatorNameById(int? negotiatorId)
  2741. {
  2742. var negotiatorName = string.Empty;
  2743. try
  2744. {
  2745. negotiatorName = ctx.tblNegotiators.Where(negt => negt.Id == negotiatorId).Select(negt => negt.Negotiator).FirstOrDefault();
  2746. }
  2747. catch (Exception ex)
  2748. {
  2749. Helper.ErrorLog(ex.InnerException, "GetNegotiatorNameByIds", "Q_Application", ex.Message);
  2750. }
  2751. return negotiatorName;
  2752. }
  2753.  
  2754. /// <summary>
  2755. /// Get branch name by id
  2756. /// </summary>
  2757. /// <param name="branchId"></param>
  2758. /// <returns></returns>
  2759. public string GetBranchNameById(int? branchId)
  2760. {
  2761. var branchName = string.Empty;
  2762. try
  2763. {
  2764. branchName = ctx.tblBranches.Where(brn => brn.Id == branchId).Select(brn => brn.Branch).FirstOrDefault();
  2765. }
  2766. catch (Exception ex)
  2767. {
  2768. Helper.ErrorLog(ex.InnerException, "GetBranchNameById", "Q_Application", ex.Message);
  2769. }
  2770. return branchName;
  2771. }
  2772.  
  2773.  
  2774.  
  2775. #endregion [Get branch and negotiator names by Id]
  2776.  
  2777. /// <summary>
  2778. /// Get user name
  2779. /// </summary>
  2780. /// <param name="userId"></param>
  2781. /// <param name="contactId"></param>
  2782. /// <returns></returns>
  2783. public string GetUserName(int userId, int contactId)
  2784. {
  2785. string _uName = "";
  2786. try
  2787. {
  2788. if (userId > 0)
  2789. _uName = ctx.tblusers.Where(c => c.userid == userId).FirstOrDefault().brokername;
  2790. else
  2791. _uName = (from c in ctx.tblcontacts
  2792. join u in ctx.tblusers on c.broker ?? 0 equals u.userid
  2793. where c.contactid == contactId
  2794. select new { u }).FirstOrDefault().u.brokername;
  2795. }
  2796. catch (Exception ex)
  2797. {
  2798. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetUserName", ex.Message, ex.Source, ex.StackTrace, ex.HelpLink);
  2799. }
  2800. return _uName;
  2801. }
  2802.  
  2803. /// <summary>
  2804. /// Get finance status details
  2805. /// </summary>
  2806. /// <param name="applicationid"></param>
  2807. /// <returns></returns>
  2808. public List<tblfinancestatu> GetFinancestatus(int applicationid)
  2809. {
  2810. return ctx.tblfinancestatus.Where(x => x.type == applicationid).ToList();
  2811. }
  2812.  
  2813. /// <summary>
  2814. /// Get finance sub status details
  2815. /// </summary>
  2816. /// <param name="applicationid"></param>
  2817. /// <returns></returns>
  2818. public List<tblfinancesubstatus> GetFinanceSubstatus(int applicationid)
  2819. {
  2820. return ctx.tblfinancesubstatus.ToList();
  2821. }
  2822.  
  2823. /// <summary>
  2824. /// Get all finance status details
  2825. /// </summary>
  2826. /// <returns></returns>
  2827. public List<tblfinancestatu> GetAllFinancestatus()
  2828. {
  2829. return ctx.tblfinancestatus.ToList();
  2830. }
  2831.  
  2832. /// <summary>
  2833. /// Get finance type details
  2834. /// </summary>
  2835. /// <param name="financetype"></param>
  2836. /// <returns></returns>
  2837. public int GetFinanceType(string financetype)
  2838. {
  2839. return ctx.tblfinancetypes.Where(x => x.type.ToLower() == financetype.ToLower()).Select(x => x.financetypeid).FirstOrDefault();
  2840. }
  2841.  
  2842. /// <summary>
  2843. /// Get other applicant details
  2844. /// </summary>
  2845. /// <param name="ContactId"></param>
  2846. /// <returns></returns>
  2847. public OtherApplicant GetOtherApplicant(int ContactId)
  2848. {
  2849. var oData = new OtherApplicant();
  2850. try
  2851. {
  2852. var d = ctx.tblOtherApplicants.Where(x => x.MainContactId == ContactId & x.IsActive == true).FirstOrDefault();
  2853. if (d != null)
  2854. {
  2855. oData.AddDate = d.AddDate;
  2856. oData.Email = d.Email;
  2857. oData.EntryBy = d.EntryBy;
  2858. oData.FirstName = d.FirstName;
  2859. oData.IsActive = d.IsActive;
  2860. oData.IsDelete = d.IsDelete;
  2861. oData.MainContactId = d.MainContactId;
  2862. oData.MobileNumber = d.MobileNumber;
  2863. oData.OtherApplicantId = d.OtherApplicantId;
  2864. oData.SurName = d.SurName;
  2865. oData.UpdateBy = d.UpdateBy;
  2866. oData.UpdateDate = d.UpdateDate;
  2867. oData.address_line1 = d.address1;
  2868. oData.address_line2 = d.address2;
  2869. oData.address_city = d.town;
  2870. oData.address_postcode = d.postcode;
  2871. oData.dependents = d.dependants;
  2872. oData.employer = d.employer;
  2873. oData.jobtitle = d.jobtitle;
  2874. oData.ninnumber = d.ninumber;
  2875. oData.weddinganniversary = d.married.HasValue ? Helper.GetDateString(d.married.Value) : string.Empty;
  2876. oData.dob = d.dob.HasValue ? Helper.GetDateString(d.dob.Value) : string.Empty;
  2877. oData.accountant = d.accountdetails;
  2878. }
  2879. }
  2880. catch(Exception ex)
  2881. {
  2882. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetOtherApplicant", ex.Message);
  2883. }
  2884.  
  2885. return oData ?? new OtherApplicant();
  2886. }
  2887.  
  2888. /// <summary>
  2889. /// Get address details
  2890. /// </summary>
  2891. /// <param name="contactid"></param>
  2892. /// <returns></returns>
  2893. public Address GetAddress(int contactid)
  2894. {
  2895. //Address
  2896. var address = ctx.tbladdresses.Where(x => x.addressid == contactid).FirstOrDefault();
  2897. Address addr = new Address();
  2898. address = null;
  2899. if (address != null)
  2900. {
  2901. addr.address_line1 = address.address1;
  2902. addr.address_line2 = address.address2;
  2903. addr.address_city = address.town;
  2904. addr.address_postcode = address.postcode;
  2905. }
  2906. return addr;
  2907. }
  2908.  
  2909. /// <summary>
  2910. /// Get notes details
  2911. /// </summary>
  2912. /// <param name="contactid"></param>
  2913. /// <param name="financeid"></param>
  2914. /// <returns></returns>
  2915. public List<Notes> GetNotes(int contactid, int financeid)
  2916. {
  2917. List<Notes> _NoteList = new List<Notes>();
  2918. //Added on 2015-03-17
  2919. if (financeid > 0)
  2920. {
  2921. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  2922. }
  2923. if (financeid != 0)
  2924. {
  2925. SqlParameter[] Param = new SqlParameter[2];
  2926. Param[0] = new SqlParameter("@ContactId", contactid);
  2927. Param[1] = new SqlParameter("@FinanceId", financeid);
  2928. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetNotes", Param);
  2929.  
  2930. _NoteList = (from DataRow row in dt.Rows
  2931. select new Notes
  2932. {
  2933. contact = Convert.ToInt32(((row["contact"] == DBNull.Value || row["contact"] == null) ? "0" : row["contact"]).ToString()),
  2934. created = (row["created"] == DBNull.Value || row["created"] == null) ? string.Empty : Convert.ToDateTime(row["created"]).ToString("dd/MM/yyyy HH:mm"),
  2935. noteid = Convert.ToInt32(((row["noteid"] == DBNull.Value || row["noteid"] == null) ? "0" : row["noteid"]).ToString()),
  2936. userid = Convert.ToInt32(((row["userid"] == DBNull.Value || row["userid"] == null) ? "0" : row["userid"]).ToString()),
  2937. notes = ((row["notes"] == DBNull.Value || row["notes"] == null) ? " " : row["notes"]).ToString(),
  2938. UserName = ((row["UserName"] == DBNull.Value || row["UserName"] == null) ? " " : row["UserName"]).ToString()
  2939. }).ToList();
  2940.  
  2941. }
  2942. else
  2943. {
  2944. _NoteList = new List<Notes>();
  2945. }
  2946. return _NoteList;
  2947. }
  2948.  
  2949. #endregion [Get application form details 2015-03-31]
  2950.  
  2951. #region [Get various product details 2015-03-31]
  2952.  
  2953. /// <summary>
  2954. /// Get mortgage product details
  2955. /// </summary>
  2956. /// <param name="appid"></param>
  2957. /// <param name="contactid"></param>
  2958. /// <param name="financeid"></param>
  2959. /// <returns></returns>
  2960. public Mortgage GetMortgage(int appid, int contactid, int financeid)
  2961. {
  2962. int mortage_statusid = GetFinanceType(FinanceType.Mortgages);
  2963. var mortageids = ctx.tblfinancestatus.Where(x => x.type == mortage_statusid && x.statusid != 34).ToList().Select(s => s.statusid);
  2964. //Added on 2015-03-17
  2965. if (financeid > 0)
  2966. {
  2967. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  2968. }
  2969. var mortagequery = ctx.tblfinances.Where(x => mortageids.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  2970. Mortgage mortgage = new Mortgage();
  2971. int indexOfCurrentF = mortagequery.IndexOf(financeid);
  2972. if (indexOfCurrentF > -1)
  2973. financeid = mortagequery[indexOfCurrentF];
  2974. else
  2975. {
  2976. if (mortagequery.Count() > 0)
  2977. if (financeid > 0)
  2978. financeid = (int)mortagequery[0];
  2979. }
  2980. //End:- Updated On 2-4-2014 regarding tab and product selection
  2981. tblfinance financedata = ctx.tblfinances.Where(x => x.financeid == financeid).FirstOrDefault();
  2982. if (financedata != null)
  2983. {
  2984. mortgage.mortage_financeid = financedata.financeid;
  2985. mortgage.mortage_mortgagesubtype = financedata.status ?? 0;
  2986. mortgage.mortage_mortageaccountno = financedata.policyreference;
  2987. mortgage.mortage_lender = financedata.provider;
  2988. mortgage.mortage_amount = financedata.amount.HasValue ? (decimal?)Math.Round(financedata.amount.Value, 2) : null;
  2989. mortgage.mortage_premium = financedata.premium.HasValue ? (decimal?)Math.Round(financedata.premium.Value, 2) : null;
  2990. mortgage.mortage_commission = financedata.commission.HasValue ? (decimal?)Math.Round(financedata.commission.Value, 2) : null;
  2991. mortgage.mortage_type = financedata.subtype ?? 0;
  2992. mortgage.mortage_datelive = Helper.GetDateString(financedata.datelive);
  2993. mortgage.mortage_expires = Helper.GetDateString(financedata.dateexpire);
  2994. mortgage.mortage_date = Helper.GetDateString(financedata.followup);
  2995. mortgage.mortage_time = financedata.followup.HasValue ? financedata.followup.Value.ToString("HH:mm") : string.Empty;
  2996. mortgage.mortage_contactid = contactid;
  2997. mortgage.mortage_AppicationType = appid;
  2998. mortgage.mortgage_address1 = financedata.Address1;
  2999. mortgage.mortgage_address2 = financedata.Address2;
  3000. mortgage.mortgage_town = financedata.Town;
  3001. mortgage.mortgage_county = financedata.County;
  3002. mortgage.mortgage_postcode = financedata.Postcode;
  3003. //Added Client Fee 27-10-2014
  3004. mortgage.mortgage_Clientfee = financedata.ClientFee;
  3005. mortgage.mortgage_SubFinanceStatus = financedata.subFinanceStatus ?? 0;
  3006. //********************************
  3007.  
  3008. //from mortage table
  3009. tblfinance_mortgage financemortage = ctx.tblfinance_mortgage.Where(x => x.financeid == financeid).FirstOrDefault();
  3010. if (financemortage != null)
  3011. {
  3012. mortgage.mortage_reason = financemortage.mortgagesubtype ?? 0;
  3013. mortgage.mortage_financetype = financedata.status ?? 0;
  3014. mortgage.mortage_solicitor = financemortage.solicitor;
  3015. mortgage.mortage_deposit = financemortage.deposit.HasValue ? (decimal?)Math.Round(financemortage.deposit.Value, 2) : null;
  3016. mortgage.mortage_income = financemortage.income.HasValue ? (decimal?)Math.Round(financemortage.income.Value, 2) : null;
  3017. mortgage.mortage_rterm = financemortage.rterm ?? 0;
  3018. mortgage.mortage_completiondate = Helper.GetDateString(financemortage.completed);
  3019. mortgage.mortage_exchangedate = financemortage.exchanged.HasValue ? Helper.GetDateString(financemortage.exchanged.Value) : string.Empty;
  3020. mortgage.mortage_remortagedate = financemortage.remortgagedate.HasValue ? Helper.GetDateString(financemortage.remortgagedate.Value) : string.Empty;
  3021. //Added on 2014-11-24
  3022. mortgage.mortgage_remortgagecontact = financemortage.remortgagecontact.HasValue ? Helper.GetDateString(financemortage.remortgagecontact.Value) : string.Empty;
  3023. mortgage.mortage_propertyvalue = financemortage.purchase.HasValue ? (decimal?)Math.Round(financemortage.purchase.Value, 2) : null;
  3024. mortgage.mortage_financetype = GetFinanceType(FinanceType.Mortgages);
  3025. int index = mortagequery.IndexOf(financedata.financeid) + 1;
  3026. mortgage.Mortage_Name = string.Format("{0}{1}", index, index == 1 ? "st Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 2 ? "nd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 3 ? "rd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : "th Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid));
  3027. //Added new remortgage contact 24-11-2014
  3028. mortgage.mortgage_remortgagecontact = financemortage.remortgagecontact.HasValue ? Helper.GetDateString(financemortage.remortgagecontact.Value) : string.Empty;
  3029.  
  3030. mortgage.mortage_rate = financemortage.Rate;
  3031. mortgage.RateType = financemortage.RateType ?? 0;
  3032.  
  3033.  
  3034. }
  3035. else
  3036. {
  3037. mortgage.mortage_financetype = GetFinanceType(FinanceType.Mortgages);
  3038. int index = mortagequery.IndexOf(financedata.financeid) + 1;
  3039. mortgage.Mortage_Name = string.Format("{0}{1}", index, index == 1 ? "st Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 2 ? "nd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : index == 3 ? "rd Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid) : "th Mortgage" + DisplayAddressAndLender(mortgage.mortage_financeid));
  3040. }
  3041.  
  3042.  
  3043. }
  3044. return mortgage;
  3045. }
  3046.  
  3047. /// <summary>
  3048. /// Get life product details
  3049. /// </summary>
  3050. /// <param name="appid"></param>
  3051. /// <param name="contactid"></param>
  3052. /// <param name="financeid"></param>
  3053. /// <returns></returns>
  3054. public Life GetLife(int appid, int contactid, int financeid)
  3055. {
  3056.  
  3057. int life_statusid = GetFinanceType(FinanceType.Life);
  3058. var ids = ctx.tblfinancestatus.Where(x => x.type == life_statusid && x.statusid != 35).ToList().Select(s => s.statusid);
  3059. //Added on 2015-03-17
  3060. if (financeid > 0)
  3061. {
  3062. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  3063. }
  3064. var lifegequery = ctx.tblfinances.Where(x => ids.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  3065.  
  3066. if (lifegequery.Count > 0)
  3067. {
  3068. int fid = (int)lifegequery[0];
  3069.  
  3070. //Start:- Updated On 3-4-2014 regarding tab and product selection
  3071.  
  3072. int indexOfCurrentF = lifegequery.IndexOf(financeid);
  3073. if (indexOfCurrentF > -1)
  3074. {
  3075. financeid = lifegequery[indexOfCurrentF];
  3076. fid = lifegequery[indexOfCurrentF];
  3077. }
  3078. else
  3079. {
  3080. if (financeid > 0)
  3081. financeid = fid;
  3082. }
  3083. //End:- Updated On 2-4-2014 regarding tab and product selection
  3084.  
  3085. tblfinance_insurance finance_insurance = ctx.tblfinance_insurance.Where(x => x.financeid == fid).FirstOrDefault();
  3086. if (finance_insurance == null)
  3087. {
  3088. //insert
  3089. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  3090. newfinance_insurance.financeid = fid;
  3091. newfinance_insurance.smoker = null;
  3092. newfinance_insurance.height = null;
  3093. newfinance_insurance.weight = null;
  3094. newfinance_insurance.doctor = null;
  3095. ctx.tblfinance_insurance.Add(newfinance_insurance);
  3096. ctx.SaveChanges();
  3097. }
  3098. }
  3099.  
  3100. var query = (from c in ctx.tblcontacts
  3101. join ft in ctx.tblfinances.Where(s => ids.Contains(s.status.Value)) on c.contactid equals ft.contact
  3102. join fi in ctx.tblfinance_insurance on ft.financeid equals fi.financeid
  3103. where ft.contact == contactid && ft.type == appid && ft.financeid == financeid
  3104. select new { ft, fi, c }).FirstOrDefault();
  3105.  
  3106.  
  3107.  
  3108. Life life = new Life();
  3109. if (query != null)
  3110. {
  3111. life.life_financeid = query.ft.financeid;
  3112. life.life_status = query.ft.status ?? 0;
  3113. life.life_provider = query.ft.provider;
  3114. life.life_policyref = query.ft.policyreference;
  3115. life.life_amount = query.ft.amount.HasValue ? (decimal?)Math.Round(query.ft.amount.Value, 2) : null;
  3116. life.life_commission = query.ft.commission.HasValue ? (decimal?)Math.Round(query.ft.commission.Value, 2) : null;
  3117. life.life_policytype = query.ft.subtype ?? 0;
  3118. life.life_premium = query.ft.premium.HasValue ? (decimal?)Math.Round(query.ft.premium.Value, 2) : null;
  3119. life.life_height = query.fi.height;
  3120. life.life_weight = query.fi.weight;
  3121. life.life_issmoker = query.fi.smoker.HasValue ? query.fi.smoker.Value : false;
  3122. life.life_doctor = query.fi.doctor;
  3123. life.life_medical = query.ft.details;
  3124. life.life_datelive = Helper.GetDateString(query.ft.datelive);
  3125. life.life_expires = Helper.GetDateString(query.ft.dateexpire);
  3126. life.life_date = query.ft.followup.HasValue ? Helper.GetDateString(query.ft.followup) : string.Empty;
  3127. life.life_time = query.ft.followup.HasValue ? query.ft.followup.Value.ToString("HH:mm") : string.Empty;
  3128. life.life_financetype = GetFinanceType(FinanceType.Life);
  3129. int index = lifegequery.IndexOf(query.ft.financeid) + 1;
  3130. life.life_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(life.life_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(life.life_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(life.life_financeid) : "th Policy" + DisplayAddressAndLender(life.life_financeid));
  3131. life.life_contactid = contactid;
  3132. life.life_AppicationType = appid;
  3133.  
  3134. //Added Client Fee 27-10-2014
  3135. life.life_Clientfee = query.ft.ClientFee;
  3136. //********************************
  3137. //Added on 03-04-2015
  3138. life.life_isPolicyForOtherApplicant = query.ft.isPolicyForOtherApplicant.HasValue ? query.ft.isPolicyForOtherApplicant.Value : false;
  3139. }
  3140. else
  3141. {
  3142. life.life_financetype = GetFinanceType(FinanceType.Life);
  3143. }
  3144.  
  3145. return life;
  3146. }
  3147.  
  3148. /// <summary>
  3149. /// Get building / content product details
  3150. /// </summary>
  3151. /// <param name="appid"></param>
  3152. /// <param name="contactid"></param>
  3153. /// <param name="financeid"></param>
  3154. /// <returns></returns>
  3155. public BuildingContent GetBuildingContent(int appid, int contactid, int financeid)
  3156. {
  3157.  
  3158. int buildingcontent_statusid = GetFinanceType(FinanceType.BuildingsOrContents);
  3159. //Building Contents
  3160. var idBuildings = ctx.tblfinancestatus.Where(x => x.type == buildingcontent_statusid && x.statusid != 36).ToList().Select(s => s.statusid);
  3161. //Added on 2015-03-17
  3162. if (financeid > 0)
  3163. {
  3164. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  3165. }
  3166.  
  3167. var builingquery = ctx.tblfinances.Where(x => idBuildings.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  3168.  
  3169. if (builingquery.Count > 0)
  3170. {
  3171. int fid = (int)builingquery[0];
  3172.  
  3173.  
  3174. //Start:- Updated On 3-4-2014 regarding tab and product selection
  3175.  
  3176. int indexOfCurrentF = builingquery.IndexOf(financeid);
  3177. if (indexOfCurrentF > -1)
  3178. {
  3179. financeid = builingquery[indexOfCurrentF];
  3180. fid = builingquery[indexOfCurrentF];
  3181. }
  3182. else
  3183. {
  3184. if (financeid > 0)
  3185. financeid = fid;
  3186. }
  3187. //End:- Updated On 2-4-2014 regarding tab and product selection
  3188.  
  3189. tblfinance_buildings buildings = ctx.tblfinance_buildings.Where(x => x.financeid == fid).FirstOrDefault();
  3190. if (buildings == null)
  3191. {
  3192. //insert
  3193. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  3194. newfinance_building.rebuild = null;
  3195. newfinance_building.alarm = null;
  3196. newfinance_building.financeid = fid;
  3197. ctx.tblfinance_buildings.Add(newfinance_building);
  3198. ctx.SaveChanges();
  3199. }
  3200. }
  3201.  
  3202.  
  3203.  
  3204.  
  3205. var queryBuilding = (from c in ctx.tblcontacts
  3206. join ft in ctx.tblfinances on c.contactid equals ft.contact
  3207. join fb in ctx.tblfinance_buildings on ft.financeid equals fb.financeid
  3208. where ft.contact == contactid && ft.type == appid && ft.financeid == financeid
  3209. select new { ft, fb, c }).FirstOrDefault();
  3210.  
  3211.  
  3212. BuildingContent buildingcontent = new BuildingContent();
  3213. if (queryBuilding != null)
  3214. {
  3215.  
  3216. buildingcontent.buildingcontent_financeid = queryBuilding.ft.financeid;
  3217. buildingcontent.buildingcontent_status = queryBuilding.ft.status ?? 0;
  3218. buildingcontent.buildingcontent_provider = queryBuilding.ft.provider;
  3219. buildingcontent.buildingcontent_policyref = queryBuilding.ft.policyreference;
  3220. buildingcontent.buildingcontent_amount = queryBuilding.ft.amount.HasValue ? (decimal?)Math.Round(queryBuilding.ft.amount.Value, 2) : null;
  3221. buildingcontent.buildingcontent_commission = queryBuilding.ft.commission.HasValue ? (decimal?)Math.Round(queryBuilding.ft.commission.Value, 2) : null;
  3222. buildingcontent.buildingcontent_type = queryBuilding.ft.subtype ?? 0;
  3223. buildingcontent.buildingcontent_premium = queryBuilding.ft.premium.HasValue ? (decimal?)Math.Round(queryBuilding.ft.premium.Value, 2) : null;
  3224. buildingcontent.buildingcontent_rebuildvalue = queryBuilding.fb.rebuild.HasValue ? (decimal?)Math.Round(queryBuilding.fb.rebuild.Value, 2) : null;
  3225. buildingcontent.buildingcontent_isalarmed = queryBuilding.fb.alarm.HasValue ? queryBuilding.fb.alarm.Value : false;
  3226. buildingcontent.buildingcontent_addcover = queryBuilding.ft.details;
  3227. buildingcontent.buildingcontent_datelive = Helper.GetDateString(queryBuilding.ft.datelive); ;
  3228. buildingcontent.buildingcontent_expires = Helper.GetDateString(queryBuilding.ft.dateexpire);
  3229. buildingcontent.buildingcontent_date = Helper.GetDateString(queryBuilding.ft.followup);
  3230. buildingcontent.buildingcontent_time = queryBuilding.ft.followup.HasValue ? queryBuilding.ft.followup.Value.ToString("HH:mm") : string.Empty;
  3231. buildingcontent.buildingcontent_financetype = GetFinanceType(FinanceType.BuildingsOrContents);
  3232. buildingcontent.buildingContent_contactid = contactid;
  3233. buildingcontent.buildingContent_AppicationType = appid;
  3234. // Update on 23-6-2014 due to performance issue as the GetNOte method get called Explicitly
  3235. int index = builingquery.IndexOf(queryBuilding.ft.financeid) + 1;
  3236. buildingcontent.buildingcontent_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid) : "th Policy" + DisplayAddressAndLender(buildingcontent.buildingcontent_financeid));
  3237.  
  3238. //Added Client Fee 27-10-2014
  3239. buildingcontent.building_Clientfee = queryBuilding.ft.ClientFee;
  3240. //********************************
  3241. buildingcontent.buildingcontent_isPolicyForOtherApplicant = queryBuilding.ft.isPolicyForOtherApplicant.HasValue ? queryBuilding.ft.isPolicyForOtherApplicant.Value : false;
  3242. }
  3243. else
  3244. {
  3245. buildingcontent.buildingcontent_financetype = GetFinanceType(FinanceType.BuildingsOrContents);
  3246. }
  3247. return buildingcontent;
  3248. }
  3249.  
  3250. /// <summary>
  3251. /// Get pension product details
  3252. /// </summary>
  3253. /// <param name="appid"></param>
  3254. /// <param name="contactid"></param>
  3255. /// <param name="financeid"></param>
  3256. /// <returns></returns>
  3257. public Pensions GetPension(int appid, int contactid, int financeid)
  3258. {
  3259.  
  3260. Pensions pension = new Pensions();
  3261. int pension_statusid = GetFinanceType(FinanceType.Pensions);
  3262. var idPensions = ctx.tblfinancestatus.Where(x => x.type == pension_statusid && x.statusid != 37).ToList().Select(s => s.statusid);
  3263. //Added on 2015-03-17
  3264. if (financeid > 0)
  3265. {
  3266. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  3267. }
  3268. var pensiongquery = ctx.tblfinances.Where(x => idPensions.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  3269.  
  3270. if (pensiongquery.Count > 0)
  3271. {
  3272. int fid = (int)pensiongquery[0];
  3273.  
  3274. //Start:- Updated On 3-4-2014 regarding tab and product selection
  3275.  
  3276. int indexOfCurrentF = pensiongquery.IndexOf(financeid);
  3277. if (indexOfCurrentF > -1)
  3278. {
  3279. financeid = pensiongquery[indexOfCurrentF];
  3280. fid = pensiongquery[indexOfCurrentF];
  3281. }
  3282. else
  3283. {
  3284. if (financeid > 0)
  3285. financeid = fid;
  3286. }
  3287. //End:- Updated On 2-4-2014 regarding tab and product selection
  3288.  
  3289. tblfinance_pension _obj = ctx.tblfinance_pension.Where(x => x.financeid == fid).FirstOrDefault();
  3290. if (_obj == null)
  3291. {
  3292. //insert
  3293. tblfinance_pension newfinance_pension = new tblfinance_pension();
  3294. newfinance_pension.incomereq = null;
  3295. newfinance_pension.financeid = fid;
  3296. ctx.tblfinance_pension.Add(newfinance_pension);
  3297. ctx.SaveChanges();
  3298. }
  3299. }
  3300.  
  3301.  
  3302. var queryPension = (from c in ctx.tblcontacts
  3303. join ft in ctx.tblfinances on c.contactid equals ft.contact
  3304. join fp in ctx.tblfinance_pension on ft.financeid equals fp.financeid
  3305. where ft.contact == contactid && ft.type == appid && ft.financeid == financeid
  3306. select new { ft, fp, c }).FirstOrDefault();
  3307.  
  3308.  
  3309.  
  3310. if (queryPension != null)
  3311. {
  3312. pension.pension_financeid = queryPension.ft.financeid;
  3313. pension.pension_amount = queryPension.ft.amount.HasValue ? (decimal?)Math.Round(queryPension.ft.amount.Value, 2) : null;
  3314. pension.pension_commission = queryPension.ft.commission.HasValue ? (decimal?)Math.Round(queryPension.ft.commission.Value, 2) : null;
  3315. pension.pension_contributors = queryPension.ft.premium.HasValue ? (decimal?)Math.Round(queryPension.ft.premium.Value, 2) : null;
  3316. pension.pension_datelive = Helper.GetDateString(queryPension.ft.datelive);
  3317. pension.pension_expires = Helper.GetDateString(queryPension.ft.dateexpire);
  3318. pension.pension_fundvalue = queryPension.ft.fundvalue.HasValue ? (decimal?)Math.Round(queryPension.ft.fundvalue.Value, 2) : null;
  3319. pension.pension_incomereq = queryPension.fp.incomereq.HasValue ? (decimal?)Math.Round(queryPension.fp.incomereq.Value, 2) : null;
  3320. pension.pension_policyref = queryPension.ft.policyreference;
  3321. pension.pension_provider = queryPension.ft.provider;
  3322. pension.pension_riskprofile = queryPension.ft.subtype ?? 0;
  3323. pension.pension_status = queryPension.ft.status ?? 0;
  3324. pension.pension_date = Helper.GetDateString(queryPension.ft.followup);
  3325. pension.pension_time = queryPension.ft.followup.HasValue ? queryPension.ft.followup.Value.ToString("HH:mm") : string.Empty;
  3326. pension.pension_financetype = GetFinanceType(FinanceType.Pensions);
  3327. // Update on 23-6-2014 due to performance issue as the GetNOte method get called Explicitly
  3328. pension.pension_contactid = contactid;
  3329. pension.pension_AppicationType = appid;
  3330. int index = pensiongquery.IndexOf(queryPension.ft.financeid) + 1;
  3331. pension.pension_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(pension.pension_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(pension.pension_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(pension.pension_financeid) : "th Policy" + DisplayAddressAndLender(pension.pension_financeid));
  3332.  
  3333. //Added Client Fee 27-10-2014
  3334. pension.pension_Clientfee = queryPension.ft.ClientFee;
  3335. //********************************
  3336. pension.pension_isPolicyForOtherApplicant = queryPension.ft.isPolicyForOtherApplicant.HasValue ? queryPension.ft.isPolicyForOtherApplicant.Value : false;
  3337. }
  3338. else
  3339. {
  3340. pension.pension_financetype = GetFinanceType(FinanceType.Pensions);
  3341. }
  3342. return pension;
  3343. }
  3344.  
  3345. /// <summary>
  3346. /// Get investment product details
  3347. /// </summary>
  3348. /// <param name="appid"></param>
  3349. /// <param name="contactid"></param>
  3350. /// <param name="financeid"></param>
  3351. /// <returns></returns>
  3352. public Investment GetInvestment(int appid, int contactid, int financeid)
  3353. {
  3354.  
  3355. int investment_statusid = GetFinanceType(FinanceType.Investments);
  3356. var idInvestments = ctx.tblfinancestatus.Where(x => x.type == investment_statusid && x.statusid != 38).ToList().Select(s => s.statusid);
  3357. //Added on 2015-03-17
  3358. if (financeid > 0)
  3359. {
  3360. contactid = Convert.ToInt32(ctx.tblfinances.Where(res => res.financeid == financeid).Select(res => res.contact).FirstOrDefault());
  3361. }
  3362. var investmentquery = ctx.tblfinances.Where(x => idInvestments.Contains(x.status.Value) && x.contact == contactid && x.type == appid).OrderBy(c => c.financeid).Select(x => x.financeid).ToList();
  3363.  
  3364. if (investmentquery.Count > 0)
  3365. {
  3366. int fid = (int)investmentquery[0];
  3367.  
  3368. //Start:- Updated On 3-4-2014 regarding tab and product selection
  3369.  
  3370. int indexOfCurrentF = investmentquery.IndexOf(financeid);
  3371. if (indexOfCurrentF > -1)
  3372. {
  3373. financeid = investmentquery[indexOfCurrentF];
  3374. fid = investmentquery[indexOfCurrentF];
  3375. }
  3376. else
  3377. {
  3378. if (financeid > 0)
  3379. financeid = fid;
  3380. }
  3381. //End:- Updated On 2-4-2014 regarding tab and product selection
  3382.  
  3383. tblfinance_investments _obj = ctx.tblfinance_investments.Where(x => x.financeid == fid).FirstOrDefault();
  3384. if (_obj == null)
  3385. {
  3386. //insert
  3387. tblfinance_investments newfinanceinvestment = new tblfinance_investments();
  3388. newfinanceinvestment.financeid = fid;
  3389. ctx.tblfinance_investments.Add(newfinanceinvestment);
  3390. ctx.SaveChanges();
  3391. }
  3392. }
  3393.  
  3394. var queryInvestment = (from c in ctx.tblcontacts
  3395. join ft in ctx.tblfinances on c.contactid equals ft.contact
  3396. join finv in ctx.tblfinance_investments on ft.financeid equals finv.financeid
  3397. where ft.contact == contactid && ft.type == appid && ft.financeid == financeid
  3398. select new { ft, finv, c }).FirstOrDefault();
  3399.  
  3400. Investment investment = new Investment();
  3401. if (queryInvestment != null)
  3402. {
  3403.  
  3404. investment.investment_financeid = queryInvestment.ft.financeid;
  3405. investment.investment_amount = queryInvestment.ft.amount.HasValue ? (decimal?)Math.Round(queryInvestment.ft.amount.Value, 2) : null;
  3406. investment.investment_commission = queryInvestment.ft.commission.HasValue ? (decimal?)Math.Round(queryInvestment.ft.commission.Value, 2) : null;
  3407. investment.investment_contributors = queryInvestment.ft.premium.HasValue ? (decimal?)Math.Round(queryInvestment.ft.premium.Value, 2) : null;
  3408. investment.investment_date = queryInvestment.ft.followup.HasValue ? Helper.GetDateString(queryInvestment.ft.followup.Value) : string.Empty;
  3409. investment.investment_datelive = queryInvestment.ft.datelive.HasValue ? Helper.GetDateString(queryInvestment.ft.datelive.Value) : string.Empty;
  3410. investment.investment_expires = queryInvestment.ft.dateexpire.HasValue ? Helper.GetDateString(queryInvestment.ft.dateexpire.Value) : string.Empty;
  3411. investment.investment_financetype = GetFinanceType(FinanceType.Investments);
  3412. investment.investment_fundvalue = queryInvestment.ft.fundvalue.HasValue ? (decimal?)Math.Round(queryInvestment.ft.fundvalue.Value, 2) : null;
  3413. investment.investment_policyref = queryInvestment.ft.policyreference != null ? queryInvestment.ft.policyreference : string.Empty;
  3414. investment.investment_provider = queryInvestment.ft.provider != null ? queryInvestment.ft.provider : string.Empty; ;
  3415. investment.investment_riskprofiles = queryInvestment.ft.subtype ?? 0;
  3416. investment.investment_status = queryInvestment.ft.status ?? 0;
  3417. investment.investment_time = queryInvestment.ft.followup.HasValue ? queryInvestment.ft.followup.Value.ToString("HH:mm") : string.Empty;
  3418. // Update on 23-6-2014 due to performance issue as the GetNOte method get called Explicitly
  3419. // investment.InvestmentNotes = GetNotes(contactid, financeid);
  3420. investment.investment_contactid = contactid;
  3421. investment.investment_AppicationType = appid;
  3422. int index = investmentquery.IndexOf(queryInvestment.ft.financeid) + 1;
  3423. investment.Investment_Name = string.Format("{0}{1}", index, index == 1 ? "st Policy" + DisplayAddressAndLender(investment.investment_financeid) : index == 2 ? "nd Policy" + DisplayAddressAndLender(investment.investment_financeid) : index == 3 ? "rd Policy" + DisplayAddressAndLender(investment.investment_financeid) : "th Policy" + DisplayAddressAndLender(investment.investment_financeid));
  3424.  
  3425. //Added Client Fee 27-10-2014
  3426. investment.investment_Clientfee = queryInvestment.ft.ClientFee;
  3427. //********************************
  3428. investment.investment_isPolicyForOtherApplicant = queryInvestment.ft.isPolicyForOtherApplicant.HasValue ? queryInvestment.ft.isPolicyForOtherApplicant.Value : false;
  3429. }
  3430. else
  3431. {
  3432. investment.investment_financetype = GetFinanceType(FinanceType.Investments);
  3433. }
  3434. return investment;
  3435. }
  3436.  
  3437. #endregion [Get various product details 2015-03-31]
  3438.  
  3439. #region Reports
  3440. /// <summary>
  3441. /// Method for Brroker Lead Report
  3442. /// </summary>
  3443. /// <param name="Conditions">Dynamic query for main report</param>
  3444. /// <param name="accountid">Account Id</param>
  3445. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3446. /// <param name="dateFilter">Dynamic Date filter condition</param>
  3447. /// <returns>Broker List</returns>
  3448. public List<BrokerLeadTable> GetBrokerLeadReport(string Conditions, int accountid, string Conditions1 = "", string dateFilter = "")
  3449. {
  3450. List<BrokerLeadTable> BrokerLeadTableList = new List<BrokerLeadTable>();
  3451. try
  3452. {
  3453. SqlParameter[] Param = new SqlParameter[3];
  3454. Param[0] = new SqlParameter("@conditions", Conditions);
  3455. Param[1] = new SqlParameter("@conditions1", Conditions1);
  3456. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  3457. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllBrokerReport", Param);
  3458.  
  3459. BrokerLeadTableList = (from DataRow row in dt.Rows
  3460. select new BrokerLeadTable
  3461. {
  3462. Brokerid = Convert.ToInt32(row["brokerid"]),
  3463. BrokerName = Convert.ToString(row["broker"]),
  3464. LeadPercent = "0",
  3465. NoOfLeads = Convert.ToInt32(row["TotalCount"].ToString()),
  3466. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString())
  3467. }).ToList();
  3468.  
  3469.  
  3470. return BrokerLeadTableList.OrderByDescending(x => x.NoOfLeads).ToList();
  3471. }
  3472. catch (Exception ex)
  3473. {
  3474. Helper.ErrorLog(ex.InnerException, "QApplication", "GetBrokerLeadReport", ex.Message);
  3475. return BrokerLeadTableList.ToList();
  3476. }
  3477. }
  3478.  
  3479. /// <summary>
  3480. /// Method for Agent Lead Report
  3481. /// </summary>
  3482. /// <param name="Conditions">Dynamic query for main report</param>
  3483. /// <param name="accountid">Account Id</param>
  3484. /// <param name="isNeg">Negotiator</param>
  3485. /// <param name="Condition1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3486. /// <param name="dateFilter">Dynamic Date filter condition</param>
  3487. /// <returns>Agent List</returns>
  3488. public List<AgentLeadTable> GetAfflicateLeadReport(string Conditions, int accountid, bool isNeg, string Condition1 = "", string dateFilter = "")
  3489. {
  3490. List<AgentLeadTable> AgentLeadList = new List<AgentLeadTable>();
  3491. try
  3492. {
  3493. SqlParameter[] Param = new SqlParameter[3];
  3494. Param[0] = new SqlParameter("@conditions", Conditions);
  3495. if (!string.IsNullOrEmpty(Condition1))
  3496. Param[1] = new SqlParameter("@conditions1", Condition1);
  3497. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  3498. string _procName = "GetAllAfflicateReport";
  3499. //Check For Is Negotiation When the negotiator selected the below procedure will be selected elase the old
  3500.  
  3501. if (isNeg)
  3502. _procName = "GetAllAfflicateReport_Neg_Update";
  3503. //****************************************
  3504.  
  3505. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  3506.  
  3507. AgentLeadList = (from DataRow row in dt.Rows
  3508. select new AgentLeadTable
  3509. {
  3510. AgentId = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? " " : row["account"]).ToString()),
  3511. AgentName = ((row["agentname"] == DBNull.Value || row["agentname"] == null) ? " " : row["agentname"]).ToString(),
  3512. LeadPercent = "0",
  3513. NoOfLeads = Convert.ToInt32(((row["TotalCount"] == DBNull.Value || row["TotalCount"] == null) ? " " : row["TotalCount"]).ToString()),
  3514. NegName = (isNeg == true ? ((row["negref"] == DBNull.Value || row["negref"] == null) ? " " : row["negref"]).ToString() : ""),
  3515. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString()),
  3516. }).ToList();
  3517.  
  3518. return AgentLeadList.OrderByDescending(x => x.NoOfLeads).ToList();
  3519. }
  3520. catch (Exception ex)
  3521. {
  3522. Helper.ErrorLog(ex.InnerException, "QApplication", "GetAfflicateLeadReport", ex.Message);
  3523. return AgentLeadList.ToList();
  3524. }
  3525. }
  3526.  
  3527. //added 13-01-2015
  3528. /// <summary>
  3529. /// Method for Agent Lead Report with Branch
  3530. /// </summary>
  3531. /// <param name="Conditions">Dynamic query for main report</param>
  3532. /// <param name="accountid">Account Id</param>
  3533. /// <param name="isBranch">Branch</param>
  3534. /// <param name="Condition1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3535. /// <param name="dateFilter">Dynamic Date filter condition</param>
  3536. /// <returns></returns>
  3537. public List<AgentLeadTableBranch> GetAfflicateLeadReport_Branch(string Conditions, int accountid, bool isBranch, string Condition1 = "", string dateFilter = "")
  3538. {
  3539. List<AgentLeadTableBranch> AgentLeadBranchList = new List<AgentLeadTableBranch>();
  3540. try
  3541. {
  3542. SqlParameter[] Param = new SqlParameter[3];
  3543. Param[0] = new SqlParameter("@conditions", Conditions);
  3544. Param[1] = new SqlParameter("@conditions1", Condition1);
  3545. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  3546. string _procName = "GetAllAfflicateReport_NullBranch";
  3547. //Check For Is Negotiation When the negotiator selected the below procedure will be selected elase the old
  3548. if (isBranch)
  3549. _procName = "GetAllAfflicateReport_Branch_Update";
  3550.  
  3551.  
  3552. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  3553.  
  3554. AgentLeadBranchList = (from DataRow row in dt.Rows
  3555. select new AgentLeadTableBranch
  3556. {
  3557. AgentId = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? " " : row["account"]).ToString()),
  3558. AgentName = ((row["agentname"] == DBNull.Value || row["agentname"] == null) ? " " : row["agentname"]).ToString(), //GetAgentName(Convert.ToInt32(row["account"])),
  3559. LeadPercent = "0",
  3560. NoOfLeads = Convert.ToInt32(((row["TotalCount"] == DBNull.Value || row["TotalCount"] == null) ? " " : row["TotalCount"]).ToString()),
  3561. Branch = (isBranch == true ? ((row["branch"] == DBNull.Value || row["branch"] == null) ? " " : row["branch"]).ToString() : ""),
  3562. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString()),
  3563. }).ToList();
  3564.  
  3565. return AgentLeadBranchList.OrderByDescending(x => x.NoOfLeads).ToList();
  3566. }
  3567. catch (Exception ex)
  3568. {
  3569. Helper.ErrorLog(ex.InnerException, "Qapplication", "GetafflicateLeadReportBanch", ex.Message);
  3570. return AgentLeadBranchList;
  3571. }
  3572.  
  3573. }
  3574. /// <summary>
  3575. /// Method for product Report
  3576. /// </summary>
  3577. /// <param name="Conditions">Dynamic query for product report</param>
  3578. /// <param name="accountid">Account Id</param>
  3579. /// <param name="fromdate">From Date</param>
  3580. /// <param name="todate">To Date</param>
  3581. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3582. /// <param name="dateFilter">Dynamic Date filter condition</param>
  3583. /// <returns>Roduct List</returns>
  3584. public List<ProductReport> GetProductReport(string Conditions, int accountid, DateTime? fromdate, DateTime? todate, string Conditions1 = "")
  3585. {
  3586. List<ProductReport> ProductReportList = new List<ProductReport>();
  3587. SqlParameter[] Param = new SqlParameter[2];
  3588. Param[0] = new SqlParameter("@conditions", Conditions);
  3589. Param[1] = new SqlParameter("@conditions1", Conditions1);
  3590. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetProductReport", Param);
  3591. ProductReportList = (from DataRow row in dt.Rows
  3592. select new ProductReport
  3593. {
  3594. Product = GetFinanceTableData(row["type"].ToString()).type,
  3595. NoOfLeads = Convert.ToInt32(row["TotalLeads"]),
  3596. InProgress = GetFinanceTableData(row["type"].ToString(), (int)MortgageStatus.NewLead, fromdate, todate),
  3597. Complete = GetFinanceTableData(row["type"].ToString(), (int)MortgageStatus.Completed, fromdate, todate),
  3598. TotalCommission = row["TotalCommission"].ToString() != "" ? Math.Round(Convert.ToDecimal(row["TotalCommission"].ToString()), 2).ToString() : "0",
  3599. AvgCommission = row["AvgCommission"].ToString() != "" ? Math.Round(Convert.ToDecimal(row["AvgCommission"].ToString()), 2).ToString() : "0",
  3600. }).ToList();
  3601. return ProductReportList.OrderByDescending(x => x.AvgCommission).ToList();
  3602. }
  3603.  
  3604. private tblfinancetype GetFinanceTableData(string type)
  3605. {
  3606. int _type = Convert.ToInt32(type);
  3607. var d = ctx.tblfinancetypes.Where(x => x.financetypeid == _type).FirstOrDefault();
  3608. if (d == null)
  3609. d = new tblfinancetype(); ;
  3610.  
  3611. return d;
  3612. }
  3613.  
  3614. private int GetFinanceTableData(string type, int status, DateTime? fromdate, DateTime? todate)
  3615. {
  3616. int _type = Convert.ToInt32(type);
  3617. var d = ctx.tblfinances.Where(x => x.type == _type && x.status == status && (x.created.Value >= fromdate.Value && x.created.Value <= todate.Value));
  3618.  
  3619. return d.Count();
  3620. }
  3621. /// <summary>
  3622. /// Method for status Report in the Main Report
  3623. /// </summary>
  3624. /// <param name="Conditions">Dynamic query for product report</param>
  3625. /// <param name="accountid">Accout Id</param>
  3626. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3627. /// <param name="dateFilter">Dynamic Date filter condition</param>
  3628. /// <returns>Status report List</returns>
  3629. public List<statusReport> GetStatusReport(string Conditions, int accountid, string Conditions1 = "")
  3630. {
  3631. List<statusReport> StatusReportList = new List<statusReport>();
  3632. try
  3633. {
  3634. SqlParameter[] Param = new SqlParameter[2];
  3635. Param[0] = new SqlParameter("@conditions", Conditions);
  3636. Param[1] = new SqlParameter("@conditions1", Conditions1);
  3637. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetStatusReport", Param);
  3638. StatusReportList = (from DataRow row in dt.Rows
  3639. select new statusReport
  3640. {
  3641. status = row["StatusName"].ToString(),
  3642. NoOfLeads = Convert.ToInt32(row["TotalLeads"]),
  3643. }).ToList();
  3644.  
  3645. return StatusReportList;
  3646. }
  3647. catch (Exception ex)
  3648. {
  3649. Helper.ErrorLog(ex.InnerException, "QApplication", "GetStatusRepot", ex.Message);
  3650. return StatusReportList;
  3651. }
  3652. }
  3653. /// <summary>
  3654. /// Method for date for main report chart
  3655. /// </summary>
  3656. /// <param name="Conditions">Dynamic query for main report</param>
  3657. /// <param name="accountid">Accoutnt Id</param>
  3658. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  3659. /// <returns>MAin Report Chart Data</returns>
  3660. public List<ReportDataPoint> GetLeadReport(string Conditions, int accountid, string Conditions1 = "")
  3661. {
  3662. List<ReportDataPoint> ReportdataList = new List<ReportDataPoint>();
  3663. try
  3664. {
  3665. SqlParameter[] Param = new SqlParameter[2];
  3666. Param[0] = new SqlParameter("@conditions", Conditions);
  3667. Param[1] = new SqlParameter("@conditions1", Conditions1);
  3668.  
  3669. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "LeadReport", Param);
  3670.  
  3671. ReportdataList = (from DataRow row in dt.Rows
  3672. select new ReportDataPoint
  3673. {
  3674. Dated = row.IsNull("created") ? DateTime.MinValue : DateTime.Parse(row["created"].ToString()),
  3675. Value = Convert.ToInt32(row["TotalCount"]),
  3676. }).ToList();
  3677.  
  3678.  
  3679. return ReportdataList;
  3680. }
  3681. catch (Exception ex)
  3682. {
  3683. Helper.ErrorLog(ex.InnerException, "QApplicaition", "GetLeadreport", ex.Message);
  3684. return ReportdataList;
  3685. }
  3686. }
  3687.  
  3688.  
  3689. public List<string> GetProviderOrSoliciter(int accountId, int datatype = 0, int financetype = 0, string inputtext = "")
  3690. {
  3691.  
  3692. List<string> namelist = new List<string>();
  3693. try
  3694. {
  3695. SqlParameter[] Param = new SqlParameter[4];
  3696. Param[0] = new SqlParameter("@inputtext", inputtext);
  3697. Param[1] = new SqlParameter("@datatype", datatype);
  3698. Param[2] = new SqlParameter("@Type", financetype);
  3699. Param[3] = new SqlParameter("@parentuserid", accountId);
  3700. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetProviderOrSoliciter", Param);
  3701.  
  3702. if (dt != null)
  3703. namelist = dt.AsEnumerable().Select(dr => dr.Field<string>("Name")).ToList();
  3704. }
  3705. catch(Exception ex)
  3706. {
  3707. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetProviderOrSoliciter", ex.Message);
  3708. }
  3709. return namelist;
  3710. }
  3711.  
  3712. public List<string> GetProviderOrSoliciter(int accountId, int pageno, out int Total, int datatype = 0, int financetype = 0, string inputtext = "")
  3713. {
  3714. Total = 20;
  3715. List<string> namelist = new List<string>();
  3716. try
  3717. {
  3718. SqlParameter[] Param = new SqlParameter[5];
  3719. Param[0] = new SqlParameter("@inputtext", inputtext);
  3720. Param[1] = new SqlParameter("@datatype", datatype);
  3721. Param[2] = new SqlParameter("@Type", financetype);
  3722. Param[3] = new SqlParameter("@parentuserid", accountId);
  3723. Param[4] = new SqlParameter("@pageno", pageno);
  3724. var ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetProviderOrSoliciterDemo", Param);
  3725. if (ds.Tables[0] != null)
  3726. {
  3727. namelist = ds.Tables[0].AsEnumerable().Select(dr => dr.Field<string>("Name")).ToList();
  3728. Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
  3729. }
  3730.  
  3731. }
  3732. catch
  3733. {
  3734. }
  3735. return namelist;
  3736. }
  3737.  
  3738.  
  3739. /// <summary>
  3740. /// Getting broker name
  3741. /// </summary>
  3742. /// <param name="Brokerid"></param>
  3743. /// <returns></returns>
  3744. public string GetBrokerName(int Brokerid)
  3745. {
  3746. return ctx.tblusers.Where(x => x.userid == Brokerid).Select(x => x.brokername).FirstOrDefault();
  3747. }
  3748.  
  3749. public string GetAgentName(int Agentid)
  3750. {
  3751. return ctx.tblaccounts.Where(x => x.accountid == Agentid).Select(x => x.companyname).FirstOrDefault();
  3752. }
  3753.  
  3754. /// <summary>
  3755. /// Checking for broker
  3756. /// </summary>
  3757. /// <param name="UserId"></param>
  3758. /// <returns></returns>
  3759. public bool IsBroker(int UserId)
  3760. {
  3761. return ctx.tblusers.Any(x => x.userid == UserId && x.active == true && x.broker == true);
  3762. }
  3763. /// <summary>
  3764. /// Checking for insurance broker
  3765. /// </summary>
  3766. /// <param name="UserId"></param>
  3767. /// <returns></returns>
  3768. public bool IsInsuranceBroker(int UserId)
  3769. {
  3770. return ctx.tblusers.Any(x => x.userid == UserId && x.active == true && x.broker == true && x.account == 880);
  3771. }
  3772. /// <summary>
  3773. /// Checking for Mortgage Broker
  3774. /// </summary>
  3775. /// <param name="UserId"></param>
  3776. /// <returns></returns>
  3777. public bool IsMortgageBroker(int UserId)
  3778. {
  3779. return ctx.tblusers.Any(x => x.userid == UserId && x.active == true && x.broker == true && x.account == 6569);
  3780. }
  3781. /// <summary>
  3782. /// Checking for admin
  3783. /// </summary>
  3784. /// <param name="UserId"></param>
  3785. /// <returns></returns>
  3786. public bool IsAdmin(int UserId)
  3787. {
  3788. return ctx.tblusers.Any(x => x.userid == UserId && x.active == true && (x.administrator.HasValue == true && x.administrator.Value == true) && (x.broker.HasValue == false || x.broker.Value == false));
  3789. }
  3790. /// <summary>
  3791. /// Checking for User
  3792. /// </summary>
  3793. /// <param name="UserId"></param>
  3794. /// <returns></returns>
  3795. public bool IsUser(int UserId)
  3796. {
  3797. return ctx.tblusers.Any(u => u.userid == UserId && u.active == true && (u.administrator.HasValue == false || u.administrator.Value == false) && (u.broker.HasValue == false || u.broker.Value == false));
  3798. }
  3799. /// <summary>
  3800. /// Checking for super admin
  3801. /// </summary>
  3802. /// <param name="UserId"></param>
  3803. /// <returns></returns>
  3804. public bool IsSuperAdmin(int UserId)
  3805. {
  3806. return ctx.tblusers.Any(x => x.userid == UserId && x.active == true && (x.administrator.HasValue == true && x.administrator.Value == true) && (x.broker.HasValue == false || x.broker.Value == false) && (!x.AdminId.HasValue || x.AdminId.Value == 0));
  3807. }
  3808. /// <summary>
  3809. /// Checking for Manager
  3810. /// </summary>
  3811. /// <param name="UserId"></param>
  3812. /// <param name="teamId"></param>
  3813. /// <param name="brokers"></param>
  3814. /// <returns></returns>
  3815. public bool IsManager(int UserId, out int teamId, out string brokers)
  3816. {
  3817. bool _chk = false;
  3818. teamId = 0;
  3819. brokers = "";
  3820. try
  3821. {
  3822. if (ctx.tblusers.Any(u => u.userid == UserId && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == true && u.broker.Value == true)))
  3823. {
  3824. _chk = true;
  3825. teamId = ctx.tblusers.Where(c => c.userid == UserId).FirstOrDefault() != null ? (ctx.tblusers.Where(c => c.userid == UserId).FirstOrDefault().TeamId ?? 0) : 0;
  3826. }
  3827.  
  3828. if (teamId > 0)
  3829. {
  3830. SqlParameter[] Param = new SqlParameter[2];
  3831. Param[0] = new SqlParameter("@TeamId", teamId);
  3832.  
  3833. var dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetTeamMembersIdsByTeamId", Param);
  3834. if (dt != null && dt.Rows.Count > 0)
  3835. {
  3836. brokers = Convert.ToString(dt.Rows[0]["BrokersIds"]).Trim().TrimEnd(',');
  3837. }
  3838.  
  3839. }
  3840.  
  3841. }
  3842. catch(Exception ex)
  3843. {
  3844. Helper.ErrorLog(ex.InnerException, "Q_Application", "IsManager", ex.Message);
  3845. }
  3846. return _chk;
  3847. }
  3848. /// <summary>
  3849. /// Checking for administrator
  3850. /// </summary>
  3851. /// <param name="UserId"></param>
  3852. /// <param name="brokers"></param>
  3853. /// <returns></returns>
  3854. public bool IsAdministrator(int UserId, out string brokers)
  3855. {
  3856. bool _chk = false;
  3857. brokers = "";
  3858. try
  3859. {
  3860. if (ctx.tblusers.Any(u => u.userid == UserId && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == false || u.broker.Value == false)))
  3861. {
  3862. _chk = true;
  3863.  
  3864. SqlParameter[] Param = new SqlParameter[1];
  3865. Param[0] = new SqlParameter("@AdminId", UserId);
  3866.  
  3867. var dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokersIdsByAdminId", Param);
  3868. if (dt != null && dt.Rows.Count > 0)
  3869. {
  3870. brokers = Convert.ToString(dt.Rows[0]["BrokersIds"]).Trim().TrimEnd(',');
  3871. }
  3872.  
  3873. }
  3874.  
  3875. }
  3876. catch(Exception ex)
  3877. {
  3878. Helper.ErrorLog(ex.InnerException, "Q_Application", "IsAdministrator", ex.Message);
  3879. }
  3880. return _chk;
  3881. }
  3882. /// <summary>
  3883. /// Is creating admin user
  3884. /// </summary>
  3885. /// <param name="accountId"></param>
  3886. /// <param name="UserId"></param>
  3887. /// <param name="adminaccId"></param>
  3888. /// <param name="brokers"></param>
  3889. /// <returns></returns>
  3890. public bool IsCreatedAdminUser(int accountId, int UserId, out int adminaccId, out string brokers)
  3891. {
  3892. bool _chk = false;
  3893. brokers = "";
  3894. adminaccId = 0;
  3895. try
  3896. {
  3897. if (ctx.tblusers.Any(u => u.userid == UserId && u.AdminId.HasValue && u.AdminId.Value > 0 && u.active == true && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == false || u.broker.Value == false)))
  3898. {
  3899. _chk = true;
  3900. adminaccId = ctx.tblusers.Where(c => c.userid == UserId).FirstOrDefault().AdminId.Value;
  3901. SqlParameter[] Param = new SqlParameter[1];
  3902. Param[0] = new SqlParameter("@AdminId", accountId);
  3903.  
  3904. var dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAdminTeamMembersIds", Param);
  3905. if (dt != null && dt.Rows.Count > 0)
  3906. {
  3907. brokers = Convert.ToString(dt.Rows[0]["Brokers"]).Trim().TrimEnd(',');
  3908. }
  3909.  
  3910. }
  3911.  
  3912. }
  3913. catch
  3914. {
  3915. }
  3916. return _chk;
  3917. }
  3918. /// <summary>
  3919. /// Method for checking User type
  3920. /// </summary>
  3921. /// <param name="UserId">User Id</param>
  3922. /// <param name="teamId">Team Id</param>
  3923. /// <param name="accountId">Account Id</param>
  3924. /// <param name="brokers">Brokersg</param>
  3925. /// <returns>User Type </returns>
  3926. public int CheckUserType(int UserId, out int teamId, out int accountId, out string brokers)
  3927. {
  3928. int _uType = 0; // 1- Admin, 2- Manager, 3- Broker, 4- general User
  3929. teamId = 0;
  3930. accountId = 0;
  3931. brokers = "";
  3932. var _objU = ctx.tblusers.Where(x => x.userid == UserId).FirstOrDefault();
  3933. if (_objU != null)
  3934. {
  3935. if (IsAdministrator(UserId, out brokers))
  3936. {
  3937. _uType = (int)UserType.Admin;
  3938. accountId = _objU.account;
  3939.  
  3940. }
  3941. else if (IsManager(UserId, out teamId, out brokers))
  3942. {
  3943.  
  3944. _uType = (int)UserType.Manager;
  3945. accountId = _objU.account;
  3946. }
  3947. else if ((!_objU.administrator.HasValue || !_objU.administrator.Value) && _objU.broker.HasValue && _objU.broker.Value)
  3948. {
  3949. _uType = (int)UserType.Broker;
  3950. accountId = _objU.account;
  3951. }
  3952. else if ((!_objU.administrator.HasValue || !_objU.administrator.Value) && (!_objU.broker.HasValue || !_objU.broker.Value))
  3953. {
  3954. _uType = (int)UserType.GeneralUser;
  3955. accountId = _objU.account;
  3956. }
  3957. }
  3958. return _uType;
  3959. }
  3960.  
  3961. /// <summary>
  3962. /// Method for checking User type
  3963. /// </summary>
  3964. /// <param name="UserId">User Id</param>
  3965. /// <param name="teamId">Team Id</param>
  3966. /// <param name="accountId">Account Id</param>
  3967. /// <param name="brokers">Brokersg</param>
  3968. /// <returns>User Type </returns>
  3969. public int CheckUserType1(int UserId, out int Id)
  3970. {
  3971. int _uType = 0; // 1- Admin, 2- Manager, 3- Broker, 4- general User
  3972. int teamId = 0;
  3973. int accountId = 0;
  3974. string brokers = "";
  3975. Id = 0;
  3976. var _objU = ctx.tblusers.Where(x => x.userid == UserId).FirstOrDefault();
  3977. if (_objU != null)
  3978. {
  3979. if (IsAdministrator(UserId, out brokers))
  3980. {
  3981. _uType = (int)UserType.Admin;
  3982. accountId = _objU.account;
  3983. Id = _objU.account;
  3984.  
  3985. }
  3986. else if (IsManager(UserId, out teamId, out brokers))
  3987. {
  3988.  
  3989. _uType = (int)UserType.Manager;
  3990. accountId = _objU.account;
  3991. Id = teamId;
  3992. }
  3993. else if ((!_objU.administrator.HasValue || !_objU.administrator.Value) && _objU.broker.HasValue && _objU.broker.Value)
  3994. {
  3995. _uType = (int)UserType.Broker;
  3996. accountId = _objU.account;
  3997. Id = UserId;
  3998. }
  3999. else if ((!_objU.administrator.HasValue || !_objU.administrator.Value) && (!_objU.broker.HasValue || !_objU.broker.Value))
  4000. {
  4001. _uType = (int)UserType.GeneralUser;
  4002. //accountId = _objU.account;
  4003. int account = ctx.tblaccounts.Where(u => u.accountid == UserId).Select(u => u.parent.HasValue ? u.parent.Value : 0).FirstOrDefault();
  4004. Id = account;
  4005. }
  4006. }
  4007. return _uType;
  4008. }
  4009. #endregion
  4010.  
  4011. #region Yearly Holiday Settings
  4012. /// <summary>
  4013. /// Saving Yearly Holiday setting
  4014. /// </summary>
  4015. /// <param name="model"></param>
  4016. /// <param name="AccountId"></param>
  4017. /// <returns></returns>
  4018. public int YearlyHolidaySave(YearlyHolidaySettingsModel model, int AccountId)
  4019. {
  4020. int _chk = 0;
  4021.  
  4022. try
  4023. {
  4024. using (SqlConnection con = new SqlConnection(Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"])))
  4025. {
  4026. con.Open();
  4027. SqlDataAdapter com = new SqlDataAdapter("USPYearlyHolidaySettingsAddUpdate", con);
  4028. com.SelectCommand.CommandType = CommandType.StoredProcedure;
  4029. com.SelectCommand.Parameters.AddWithValue("@Id", model.ID.HasValue ? model.ID.Value : 0);
  4030. com.SelectCommand.Parameters.AddWithValue("@Year", model.Year.HasValue ? model.Year.Value : 0);
  4031. com.SelectCommand.Parameters.AddWithValue("@Date", model.Date.GetDate());
  4032. com.SelectCommand.Parameters.AddWithValue("@HolidayTitle", model.Title);
  4033. com.SelectCommand.Parameters.AddWithValue("@HolidayDescription", model.Description);
  4034. com.SelectCommand.Parameters.AddWithValue("@EntryBy", model.EntryBy);
  4035. com.SelectCommand.Parameters.AddWithValue("@UpdatedBy", model.UpdateBy);
  4036. com.SelectCommand.Parameters.AddWithValue("@IsActive", true);
  4037. com.SelectCommand.Parameters.AddWithValue("@IsDeleted", false);
  4038. com.SelectCommand.Parameters.AddWithValue("@AddDate", DateTime.Now);
  4039. com.SelectCommand.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
  4040. com.SelectCommand.Parameters.AddWithValue("@ActionType", 1);
  4041. com.SelectCommand.Parameters.AddWithValue("@AccountId", AccountId);
  4042. //@ReturnMessage
  4043. SqlParameter outParam1 = com.SelectCommand.Parameters.Add("@Chk", SqlDbType.Int);
  4044. outParam1.Direction = ParameterDirection.Output;
  4045. com.SelectCommand.ExecuteNonQuery();
  4046. _chk = Convert.ToInt32((outParam1.SqlValue != DBNull.Value || outParam1.SqlValue != null) ? outParam1.SqlValue.ToString() : "0");
  4047. }
  4048. }
  4049. catch (Exception ex)
  4050. {
  4051. Helper.ErrorLog(ex.InnerException, "Q_Application", "YearlyHolidaysave", ex.Message);
  4052. _chk = 0;
  4053. }
  4054. return _chk;
  4055. }
  4056. /// <summary>
  4057. /// Deleting yearly holiday setting
  4058. /// </summary>
  4059. /// <param name="model"></param>
  4060. /// <param name="AccountId"></param>
  4061. /// <returns></returns>
  4062. public int YearlyHolidayDelete(YearlyHolidaySettingsModel model, int AccountId)
  4063. {
  4064. int _chk = 0;
  4065. try
  4066. {
  4067. using (SqlConnection con = new SqlConnection(Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"])))
  4068. {
  4069. con.Open();
  4070. SqlDataAdapter com = new SqlDataAdapter("USPYearlyHolidaySettingsAddUpdate", con);
  4071. com.SelectCommand.CommandType = CommandType.StoredProcedure;
  4072. com.SelectCommand.Parameters.AddWithValue("@Id", model.ID.Value);
  4073. com.SelectCommand.Parameters.AddWithValue("@Year", model.Year.HasValue ? model.Year.Value : 0);
  4074. com.SelectCommand.Parameters.AddWithValue("@Date", DateTime.Now);
  4075. com.SelectCommand.Parameters.AddWithValue("@HolidayTitle", model.Title);
  4076. com.SelectCommand.Parameters.AddWithValue("@HolidayDescription", model.Description);
  4077. com.SelectCommand.Parameters.AddWithValue("@EntryBy", model.EntryBy);
  4078. com.SelectCommand.Parameters.AddWithValue("@UpdatedBy", model.UpdateBy);
  4079. com.SelectCommand.Parameters.AddWithValue("@IsActive", true);
  4080. com.SelectCommand.Parameters.AddWithValue("@IsDeleted", false);
  4081. com.SelectCommand.Parameters.AddWithValue("@AddDate", DateTime.Now);
  4082. com.SelectCommand.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
  4083. com.SelectCommand.Parameters.AddWithValue("@ActionType", 2);
  4084. com.SelectCommand.Parameters.AddWithValue("@AccountId", AccountId);
  4085. SqlParameter outParam1 = com.SelectCommand.Parameters.Add("@Chk", SqlDbType.Int);
  4086. outParam1.Direction = ParameterDirection.Output;
  4087. com.SelectCommand.ExecuteNonQuery();
  4088. _chk = Convert.ToInt32((outParam1.SqlValue != DBNull.Value || outParam1.SqlValue != null) ? outParam1.SqlValue.ToString() : "0");
  4089. }
  4090. }
  4091. catch(Exception ex)
  4092. {
  4093. Helper.ErrorLog(ex.InnerException, "Q_Application", "YearlyHolidayDelete", ex.Message);
  4094. _chk = 0;
  4095. }
  4096. return _chk;
  4097. }
  4098. /// <summary>
  4099. /// Getting Yearly Holidays
  4100. /// </summary>
  4101. /// <param name="model"></param>
  4102. /// <param name="AccountId"></param>
  4103. /// <returns></returns>
  4104. public List<YearlyHolidaySettingsModel> GetYearlyHolidays(YearlyHolidayModel model, int AccountId)
  4105. {
  4106. List<YearlyHolidaySettingsModel> _yearlyholidays = new List<YearlyHolidaySettingsModel>();
  4107. string date = System.DateTime.Now.ToString("MM/dd/yyyy");
  4108. if (!string.IsNullOrWhiteSpace(model.Date))
  4109. date = model.Date.GetDate().HasValue ? model.Date.GetDate().Value.ToString("MM/dd/yyyy") : null;
  4110. else
  4111. date = null;
  4112. try
  4113. {
  4114. SqlParameter[] Param = new SqlParameter[3];
  4115. Param[0] = new SqlParameter("@Year", model.Year.HasValue ? model.Year.Value : 0);
  4116. Param[1] = new SqlParameter("@Date", date);
  4117. Param[2] = new SqlParameter("@AccountId", AccountId);
  4118.  
  4119. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "USPGetYearlyHolidays", Param);
  4120. _yearlyholidays = (from DataRow row in dt.Rows
  4121. select new YearlyHolidaySettingsModel
  4122. {
  4123. ID = Convert.ToInt32(((row["Id"] == DBNull.Value || row["Id"] == null) ? 0 : row["Id"]).ToString()),
  4124. Year = Convert.ToInt32(((row["Year"] == DBNull.Value || row["Year"] == null) ? "" : row["Year"]).ToString()),
  4125. Date = Convert.ToString(((row["Date"] == DBNull.Value || row["Date"] == null) ? "" : row["Date"]).ToString()),
  4126. Title = Convert.ToString(((row["Title"] == DBNull.Value || row["Title"] == null) ? "" : row["Title"]).ToString()),
  4127. Description = Convert.ToString(((row["Description"] == DBNull.Value || row["Description"] == null) ? "" : row["Description"]).ToString())
  4128. }).ToList();
  4129. }
  4130. catch (Exception ex)
  4131. {
  4132. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetYearlyHolidays", ex.Message);
  4133.  
  4134. }
  4135. return _yearlyholidays;
  4136. }
  4137. /// <summary>
  4138. /// Get Yearly Holidays
  4139. /// </summary>
  4140. /// <param name="SearchText"></param>
  4141. /// <returns></returns>
  4142. public List<string> GetYearsForHolidays(string SearchText)
  4143. {
  4144. List<string> _years = new List<string>();
  4145. try
  4146. {
  4147. SqlParameter[] Param = new SqlParameter[1];
  4148. Param[0] = new SqlParameter("@SearchText", SearchText);
  4149. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetYearsForHolidays", Param);
  4150. _years = (from DataRow row in dt.Rows
  4151. select Convert.ToString(((row["Year"] == DBNull.Value || row["Year"] == null) ? "" : row["Year"]).ToString())
  4152. ).ToList();
  4153. }
  4154. catch (Exception ex)
  4155. {
  4156. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetYersForHolidays", ex.Message);
  4157. }
  4158.  
  4159. return _years;
  4160. }
  4161. #endregion
  4162.  
  4163. #region Over All Broker Summary
  4164. /// <summary>
  4165. /// Getting All broker summary details
  4166. /// </summary>
  4167. /// <param name="BrokerId"></param>
  4168. /// <param name="FromDate"></param>
  4169. /// <param name="ToDate"></param>
  4170. /// <returns></returns>
  4171. public OverAllBrokerSummary GetOverAllBrokerSummary(int BrokerId, string FromDate = null, string ToDate = null)
  4172. {
  4173. OverAllBrokerSummary _OverAllBrokerSummary = new OverAllBrokerSummary();
  4174. try
  4175. {
  4176. SqlParameter[] Param = new SqlParameter[3];
  4177. Param[0] = new SqlParameter("@BrokerId", BrokerId);
  4178. if (!string.IsNullOrWhiteSpace(FromDate))
  4179. {
  4180. Param[1] = new SqlParameter("@FromDate", FromDate.GetDate().HasValue ? FromDate.GetDate().Value.ToString("MM/dd/yyyy") : null);
  4181. }
  4182. if (!string.IsNullOrWhiteSpace(ToDate))
  4183. {
  4184. Param[2] = new SqlParameter("@ToDate", ToDate.GetDate().HasValue ? ToDate.GetDate().Value.ToString("MM/dd/yyyy") : null);
  4185. }
  4186.  
  4187. string _procName = "GetOverAllBrokerSummary";
  4188.  
  4189. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  4190.  
  4191. if (dt != null && dt.Rows.Count > 0)
  4192. {
  4193. string subPath = System.Configuration.ConfigurationManager.AppSettings["BrokerImages"] ?? "~/Content/BrokerImages/";
  4194. _OverAllBrokerSummary = (from DataRow row in dt.Rows
  4195. select new OverAllBrokerSummary
  4196. {
  4197. BrokerId = Convert.ToInt32(((row["BrokerId"] == DBNull.Value || row["BrokerId"] == null) ? "0" : row["BrokerId"]).ToString()),
  4198. BrokerName = ((row["BrokerName"] == DBNull.Value || row["BrokerName"] == null) ? " " : row["BrokerName"]).ToString(),
  4199. DetailInfo = ((row["DetailInfo"] == DBNull.Value || row["DetailInfo"] == null) ? " " : row["DetailInfo"]).ToString(),
  4200. BrokerImage = subPath + ((row["BrokerImage"] == DBNull.Value || row["BrokerImage"] == null) ? "noimage.jpg" : row["BrokerImage"]).ToString(),
  4201. TotalDays = Convert.ToInt32(((row["TotalDays"] == DBNull.Value || row["TotalDays"] == null) ? "0" : row["TotalDays"]).ToString()),
  4202. NewLead = Convert.ToInt32(((row["NewLead"] == DBNull.Value || row["NewLead"] == null) ? "0" : row["NewLead"]).ToString()),
  4203. Remortgage = Convert.ToInt32(((row["Remortgage"] == DBNull.Value || row["Remortgage"] == null) ? "0" : row["Remortgage"]).ToString()),
  4204. CallBack = Convert.ToInt32(((row["CallBack"] == DBNull.Value || row["CallBack"] == null) ? "0" : row["CallBack"]).ToString()),
  4205. Meeting = Convert.ToInt32(((row["Meeting"] == DBNull.Value || row["Meeting"] == null) ? "0" : row["Meeting"]).ToString()),
  4206. DIP = Convert.ToInt32(((row["DIP"] == DBNull.Value || row["DIP"] == null) ? "0" : row["DIP"]).ToString()),
  4207. PreOfferProcessing = Convert.ToInt32(((row["PreOfferProcessing"] == DBNull.Value || row["PreOfferProcessing"] == null) ? "0" : row["PreOfferProcessing"]).ToString()),
  4208. PostOfferProcessing = Convert.ToInt32(((row["PostOfferProcessing"] == DBNull.Value || row["PostOfferProcessing"] == null) ? "0" : row["PostOfferProcessing"]).ToString()),
  4209. Completed = Convert.ToInt32(((row["Completed"] == DBNull.Value || row["Completed"] == null) ? "0" : row["Completed"]).ToString()),
  4210. Prospect = Convert.ToInt32(((row["Prospect"] == DBNull.Value || row["Prospect"] == null) ? "0" : row["Prospect"]).ToString()),
  4211. Deleted = Convert.ToInt32(((row["Deleted"] == DBNull.Value || row["Deleted"] == null) ? "0" : row["Deleted"]).ToString()),
  4212. Total = Convert.ToInt32(((row["Total"] == DBNull.Value || row["Deleted"] == null) ? "0" : row["Total"]).ToString()),
  4213. TotalLife = Convert.ToInt32(((row["TotalLife"] == DBNull.Value || row["TotalLife"] == null) ? "0" : row["TotalLife"]).ToString()),
  4214. CompletedLife = Convert.ToInt32(((row["CompletedLife"] == DBNull.Value || row["CompletedLife"] == null) ? "0" : row["CompletedLife"]).ToString()),
  4215. DaysPerMortgaeDefault = Convert.ToDecimal(((row["DaysPerMortgaeDefault"] == DBNull.Value || row["DaysPerMortgaeDefault"] == null) ? "0" : row["DaysPerMortgaeDefault"]).ToString()),
  4216. LeadToMeetingDefault = Convert.ToDecimal(((row["LeadToMeetingDefault"] == DBNull.Value || row["LeadToMeetingDefault"] == null) ? "0" : row["LeadToMeetingDefault"]).ToString()),
  4217. MeetingToDIPDefault = Convert.ToDecimal(((row["MeetingToDIPDefault"] == DBNull.Value || row["MeetingToDIPDefault"] == null) ? "0" : row["MeetingToDIPDefault"]).ToString()),
  4218. DIPToAppDefault = Convert.ToDecimal(((row["DIPToAppDefault"] == DBNull.Value || row["DIPToAppDefault"] == null) ? "0" : row["DIPToAppDefault"]).ToString()),
  4219. SubmittedToCompletedDefault = Convert.ToDecimal(((row["SubmittedToCompletedDefault"] == DBNull.Value || row["SubmittedToCompletedDefault"] == null) ? "0" : row["SubmittedToCompletedDefault"]).ToString()),
  4220. MortgaeToProtectionDefault = Convert.ToDecimal(((row["MortgaeToProtectionDefault"] == DBNull.Value || row["MortgaeToProtectionDefault"] == null) ? "0" : row["MortgaeToProtectionDefault"]).ToString()),
  4221.  
  4222.  
  4223. }).ToList()[0];
  4224. }
  4225.  
  4226. }
  4227. catch(Exception ex)
  4228. {
  4229. Helper.ErrorLog(ex.InnerException,"Q_Application","GetOverallBrokerSummary",ex.Message);
  4230. }
  4231. return _OverAllBrokerSummary ?? new OverAllBrokerSummary();
  4232. }
  4233. /// <summary>
  4234. /// Getting broker default setting
  4235. /// </summary>
  4236. /// <param name="AccountId"></param>
  4237. /// <returns></returns>
  4238. public BrokerDefaultReportSetting GetBrokerDefaultReportSetting(int AccountId)
  4239. {
  4240. BrokerDefaultReportSetting _BrokerDefaultReportSetting = new BrokerDefaultReportSetting();
  4241. try
  4242. {
  4243. SqlParameter[] Param = new SqlParameter[1];
  4244. Param[0] = new SqlParameter("@AccountId", AccountId);
  4245.  
  4246. string _procName = "GetBrokerDefaultReportSetting";
  4247.  
  4248. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  4249.  
  4250. if (dt != null && dt.Rows.Count > 0)
  4251. {
  4252. _BrokerDefaultReportSetting = (from DataRow row in dt.Rows
  4253. select new BrokerDefaultReportSetting
  4254. {
  4255. DefaultId = Convert.ToInt32(((row["DefaultId"] == DBNull.Value || row["DefaultId"] == null) ? "0" : row["DefaultId"]).ToString()),
  4256. AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
  4257. DaysPerMortgae = Convert.ToDecimal(((row["DaysPerMortgae"] == DBNull.Value || row["DaysPerMortgae"] == null) ? "0" : row["DaysPerMortgae"]).ToString()),
  4258. LeadToMeeting = Convert.ToDecimal(((row["LeadToMeeting"] == DBNull.Value || row["LeadToMeeting"] == null) ? "0" : row["LeadToMeeting"]).ToString()),
  4259. MeetingToDIP = Convert.ToDecimal(((row["MeetingToDIP"] == DBNull.Value || row["MeetingToDIP"] == null) ? "0" : row["MeetingToDIP"]).ToString()),
  4260. DIPToApp = Convert.ToDecimal(((row["DIPToApp"] == DBNull.Value || row["DIPToApp"] == null) ? "0" : row["DIPToApp"]).ToString()),
  4261. SubmittedToCompleted = Convert.ToDecimal(((row["SubmittedToCompleted"] == DBNull.Value || row["SubmittedToCompleted"] == null) ? "0" : row["SubmittedToCompleted"]).ToString()),
  4262. MortgaeToProtection = Convert.ToDecimal(((row["MortgaeToProtection"] == DBNull.Value || row["MortgaeToProtection"] == null) ? "0" : row["MortgaeToProtection"]).ToString()),
  4263.  
  4264.  
  4265. }).ToList()[0];
  4266. }
  4267.  
  4268. }
  4269. catch(Exception ex)
  4270. {
  4271. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetBrokerDefaultReportSetting", ex.Message);
  4272. }
  4273. return _BrokerDefaultReportSetting ?? new BrokerDefaultReportSetting();
  4274. }
  4275.  
  4276.  
  4277. /// <summary>
  4278. /// Broker Default Report setting Adding update
  4279. /// </summary>
  4280. /// <param name="model"></param>
  4281. /// <returns></returns>
  4282. public int BrokerDefaultReportSettingAddUpdate(BrokerDefaultReportSetting model)
  4283. {
  4284. int _chk = 0;
  4285. try
  4286. {
  4287. using (SqlConnection con = new SqlConnection(Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"])))
  4288. {
  4289. con.Open();
  4290. SqlDataAdapter com = new SqlDataAdapter("BrokerDefaultReportSettingAddUpdate", con);
  4291. com.SelectCommand.CommandType = CommandType.StoredProcedure;
  4292. com.SelectCommand.Parameters.AddWithValue("@AccountId", model.AccountId);
  4293. com.SelectCommand.Parameters.AddWithValue("@DaysPerMortgae", model.DaysPerMortgae);
  4294. com.SelectCommand.Parameters.AddWithValue("@LeadToMeeting", model.LeadToMeeting);
  4295. com.SelectCommand.Parameters.AddWithValue("@MeetingToDIP", model.MeetingToDIP);
  4296. com.SelectCommand.Parameters.AddWithValue("@DIPToApp", model.DIPToApp);
  4297. com.SelectCommand.Parameters.AddWithValue("@SubmittedToCompleted", model.SubmittedToCompleted);
  4298. com.SelectCommand.Parameters.AddWithValue("@MortgaeToProtection", model.MortgaeToProtection);
  4299. com.SelectCommand.Parameters.AddWithValue("@EntryBy", model.EntryBy);
  4300. com.SelectCommand.Parameters.AddWithValue("@UpdateBy", model.UpdateBy);
  4301. _chk = com.SelectCommand.ExecuteNonQuery();
  4302.  
  4303. }
  4304. }
  4305. catch(Exception ex)
  4306. {
  4307. Helper.ErrorLog(ex.InnerException, "Q_Application", "BrokerDefaultReportSettingAddUpdate", ex.Message);
  4308. _chk = 0;
  4309. }
  4310. return _chk;
  4311. }
  4312. #endregion
  4313.  
  4314. #region Default Holiday Settings
  4315. /// <summary>
  4316. /// Getting Broker Default Holiday Setting
  4317. /// </summary>
  4318. /// <param name="AccountId"></param>
  4319. /// <returns></returns>
  4320. public BrokerDefaultHolidaySetting GetBrokerDefaultHolidaySetting(int AccountId)
  4321. {
  4322. BrokerDefaultHolidaySetting _BrokerDefaultHolidaySetting = new BrokerDefaultHolidaySetting();
  4323. try
  4324. {
  4325. SqlParameter[] Param = new SqlParameter[1];
  4326. Param[0] = new SqlParameter("@AccountId", AccountId);
  4327.  
  4328. string _procName = "GetBrokerDefaultHolidaySetting";
  4329.  
  4330. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  4331.  
  4332. if (dt != null && dt.Rows.Count > 0)
  4333. {
  4334. _BrokerDefaultHolidaySetting = (from DataRow row in dt.Rows
  4335. select new BrokerDefaultHolidaySetting
  4336. {
  4337. DefaultId = Convert.ToInt32(((row["DefaultId"] == DBNull.Value || row["DefaultId"] == null) ? "0" : row["DefaultId"]).ToString()),
  4338. AccountId = Convert.ToInt32(((row["AccountId"] == DBNull.Value || row["AccountId"] == null) ? "0" : row["AccountId"]).ToString()),
  4339. DaysOff = Convert.ToDecimal(((row["DaysOff"] == DBNull.Value || row["DaysOff"] == null) ? "0" : row["DaysOff"]).ToString()),
  4340. HoursOff = Convert.ToDecimal(((row["HoursOff"] == DBNull.Value || row["HoursOff"] == null) ? "0" : row["HoursOff"]).ToString()),
  4341.  
  4342.  
  4343. }).ToList()[0];
  4344. }
  4345.  
  4346. }
  4347. catch(Exception ex)
  4348. {
  4349. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetBrokerDefualtHolidaysetting", ex.Message);
  4350. }
  4351. return _BrokerDefaultHolidaySetting ?? new BrokerDefaultHolidaySetting();
  4352. }
  4353.  
  4354. /// <summary>
  4355. /// getting broker default holiday setting add update
  4356. /// </summary>
  4357. /// <param name="model"></param>
  4358. /// <returns></returns>
  4359. public int BrokerDefaultHolidaySettingAddUpdate(BrokerDefaultHolidaySetting model)
  4360. {
  4361. int _chk = 0;
  4362. try
  4363. {
  4364. using (SqlConnection con = new SqlConnection(Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["ConString1"])))
  4365. {
  4366. con.Open();
  4367. SqlDataAdapter com = new SqlDataAdapter("BrokerDefaultHolidaySettingAddUpdate", con);
  4368. com.SelectCommand.CommandType = CommandType.StoredProcedure;
  4369. com.SelectCommand.Parameters.AddWithValue("@AccountId", model.AccountId);
  4370. com.SelectCommand.Parameters.AddWithValue("@DaysOff", model.DaysOff);
  4371. com.SelectCommand.Parameters.AddWithValue("@HoursOff", model.HoursOff);
  4372. com.SelectCommand.Parameters.AddWithValue("@EntryBy", model.EntryBy);
  4373. com.SelectCommand.Parameters.AddWithValue("@UpdateBy", model.UpdateBy);
  4374. _chk = com.SelectCommand.ExecuteNonQuery();
  4375.  
  4376. }
  4377. }
  4378. catch(Exception ex)
  4379. {
  4380. Helper.ErrorLog(ex.InnerException, "Q_Application", "BrokerDefaultHolidaySettingAddUpdate", ex.Message);
  4381. _chk = 0;
  4382. }
  4383. return _chk;
  4384. }
  4385.  
  4386. #endregion
  4387.  
  4388. #region [Various Checks 2015-03-31]
  4389.  
  4390. /// <summary>
  4391. /// Check if credential email has already sent to the contact.
  4392. /// </summary>
  4393. /// <param name="contactId"></param>
  4394. /// <param name="email"></param>
  4395. /// <returns></returns>
  4396. public bool IsCredentialMailAlreadySend(int contactId)
  4397. {
  4398. return ctx.tblClientRegistrations.Any(res => res.ContactId == contactId && res.Password != null);
  4399. }
  4400.  
  4401. /// <summary>
  4402. /// Check if super admin
  4403. /// </summary>
  4404. /// <param name="userId"></param>
  4405. /// <returns></returns>
  4406. public int CheckIsSuperAdmin(int userId)
  4407. {
  4408. int _userTYpe = 0; // 1 - Super Admin, 2- Created Admin, 3 - Manager, 4 - Broker, 5 - General User
  4409. try
  4410. {
  4411. if (ctx.tblusers.Any(u => u.userid == userId && u.active == true && (u.AdminId.HasValue == false || u.AdminId.Value == 0) && (u.administrator.HasValue == true && u.administrator.Value == true) && (u.broker.HasValue == false || u.broker.Value == false)))
  4412. {
  4413. _userTYpe = 1;
  4414. }
  4415. }
  4416. catch(Exception ex)
  4417. {
  4418. Helper.ErrorLog(ex.InnerException, "Q_Application", "CheckIsSuperAdmin", ex.Message);
  4419. }
  4420. return _userTYpe;
  4421. }
  4422.  
  4423. #endregion [Various Checks 2015-03-31]
  4424.  
  4425. #region [Lead Product Data Duplicity Check]
  4426.  
  4427.  
  4428. /// <summary>
  4429. /// Added on 2015-03-14 to check lead data duplicity and lead product data transfer
  4430. /// </summary>
  4431. /// <param name="mortgageList"></param>
  4432. /// <param name="lifeList"></param>
  4433. /// <param name="buildingContentList"></param>
  4434. /// <param name="pensionList"></param>
  4435. /// <param name="investmentList"></param>
  4436. /// <param name="address"></param>
  4437. /// <returns></returns>
  4438. public bool CheckLeadDataDuplication(List<Mortgage> mortgageList, List<Life> lifeList, List<BuildingContent> buildingContentList, List<Pensions> pensionList, List<Investment> investmentList, Address address,int? userId=0)
  4439. {
  4440. string msg = "";
  4441. var dbOccfinance5572Entities = new db_occfinance_5572Entities();
  4442. var result = false;
  4443. var contactId = address.contactid;
  4444. try
  4445. {
  4446. foreach (var contact in (from mort in mortgageList where mort.mortage_financeid > 0 select dbOccfinance5572Entities.tblfinances.Where(res => res.financeid == mort.mortage_financeid).Select(res => res.contact).FirstOrDefault()).Where(contact => contact != contactId))
  4447. {
  4448. ErrorLog(Convert.ToString("Data Swapping Error. Actual Contact Id : " + contactId + " New Contact Id: " + CheckDynamicVariableForNull(contact)),userId??0);
  4449. result = true;
  4450. break;
  4451. }
  4452. if (result == false)
  4453. {
  4454. foreach (var contact in lifeList.Where(life => life.life_financeid > 0).Select(life => dbOccfinance5572Entities.tblfinances.Where(res => res.financeid == life.life_financeid).Select(res => res.contact).FirstOrDefault()).Where(contact => contact != contactId))
  4455. {
  4456. ErrorLog(Convert.ToString("Data Swapping Error. Actual Contact Id : " + contactId + " New Contact Id: " + CheckDynamicVariableForNull(contact)), userId ?? 0);
  4457. result = true;
  4458. break;
  4459. }
  4460. if (result == false)
  4461. {
  4462. foreach (var contact in buildingContentList.Where(build => build.buildingcontent_financeid > 0).Select(build => dbOccfinance5572Entities.tblfinances.Where(res => res.financeid == build.buildingcontent_financeid).Select(res => res.contact).FirstOrDefault()).Where(contact => contact != contactId))
  4463. {
  4464. ErrorLog(Convert.ToString("Data Swapping Error. Actual Contact Id : " + contactId + " New Contact Id: " + CheckDynamicVariableForNull(contact)), userId ?? 0);
  4465. result = true;
  4466. break;
  4467. }
  4468. if (result == false)
  4469. {
  4470. foreach (var contact in pensionList.Where(pen => pen.pension_financeid > 0).Select(pen => dbOccfinance5572Entities.tblfinances.Where(res => res.financeid == pen.pension_financeid).Select(res => res.contact).FirstOrDefault()).Where(contact => contact != contactId))
  4471. {
  4472. ErrorLog(Convert.ToString("Data Swapping Error. Actual Contact Id : " + contactId + " New Contact Id: " + CheckDynamicVariableForNull(contact)), userId ?? 0);
  4473. result = true;
  4474. break;
  4475. }
  4476. if (result == false)
  4477. {
  4478. foreach (var contact in investmentList.Where(invst => invst.investment_financeid > 0).Select(invst => dbOccfinance5572Entities.tblfinances.Where(res => res.financeid == invst.investment_financeid).Select(res => res.contact).FirstOrDefault()).Where(contact => contact != contactId))
  4479. {
  4480. ErrorLog(Convert.ToString("Data Swapping Error. Actual Contact Id : " + contactId + " New Contact Id: " + CheckDynamicVariableForNull(contact)), userId ?? 0);
  4481. result = true;
  4482. break;
  4483. }
  4484. }
  4485. }
  4486. }
  4487. }
  4488. }
  4489. catch(Exception ex)
  4490. {
  4491.  
  4492. ErrorLog("Exception occurred while testing duplicity of lead product data. Actual Exception : " + ex.Message);
  4493. }
  4494.  
  4495. return result;
  4496. }
  4497.  
  4498. #region [Lead Product Data Duplication Check 20150720]
  4499. /// <summary>
  4500. /// Check Lead Data duplication
  4501. /// </summary>
  4502. /// <param name="mortgageList"></param>
  4503. /// <param name="lifeList"></param>
  4504. /// <param name="buildingContentList"></param>
  4505. /// <param name="pensionList"></param>
  4506. /// <param name="investmentList"></param>
  4507. /// <param name="address"></param>
  4508. /// <returns></returns>
  4509. public bool CheckLeadProductDataDuplication(List<Mortgage> mortgageList, List<Life> lifeList, List<BuildingContent> buildingContentList, List<Pensions> pensionList, List<Investment> investmentList, Address address)
  4510. {
  4511. var result = false;
  4512. try
  4513. {
  4514. if (IsMortgageProductDataDuplicated(mortgageList))
  4515. result = true;
  4516. if (result == false)
  4517. {
  4518. if (IsLifeProductDataDuplicated(lifeList))
  4519. result = true;
  4520.  
  4521. if (result == false)
  4522. {
  4523. if (IsBuildingContentProductDataDuplicated(buildingContentList))
  4524. result = true;
  4525.  
  4526. if (result == false)
  4527. {
  4528. if (IsPensionProductDataDuplicated(pensionList))
  4529. result = true;
  4530.  
  4531.  
  4532. if (result == false)
  4533. {
  4534. if (IsInvestmentProductDataDuplicated(investmentList))
  4535. result = true;
  4536. }
  4537. }
  4538. }
  4539. }
  4540. }
  4541. catch
  4542. {
  4543. ErrorLog("Exception occurred while testing duplicity of lead product data.");
  4544. }
  4545. if (result)
  4546. ErrorLog(Convert.ToString("Data swapping error occurs between products of contact id" + address.contactid + "."));
  4547. return result;
  4548. }
  4549.  
  4550. public bool IsMortgageProductDataDuplicated(IReadOnlyList<Mortgage> mList)
  4551. {
  4552. if (mList == null || mList.Count == 0) return false;
  4553. for (var i = 0; i < mList.Count - 1; i++)
  4554. {
  4555. if ((mList[i].mortgage_address1 == mList[i + 1].mortgage_address1)
  4556. && (mList[i].mortgage_address2 == mList[i + 1].mortgage_address2)
  4557. && (mList[i].mortgage_county == mList[i + 1].mortgage_county)
  4558. && (mList[i].mortgage_town == mList[i + 1].mortgage_town)
  4559. && (mList[i].mortgage_postcode == mList[i + 1].mortgage_postcode)
  4560. && (mList[i].mortage_date == mList[i + 1].mortage_date)
  4561. && (mList[i].mortage_solicitor == mList[i + 1].mortage_solicitor)
  4562. && (mList[i].mortage_lender == mList[i + 1].mortage_lender)
  4563. && (mList[i].mortage_amount == mList[i + 1].mortage_amount)
  4564. && (mList[i].mortage_commission == mList[i + 1].mortage_commission)
  4565. && (mList[i].mortage_rate == mList[i + 1].mortage_rate)
  4566. && (mList[i].mortgage_Clientfee == mList[i + 1].mortgage_Clientfee)
  4567. && (mList[i].mortage_premium == mList[i + 1].mortage_premium)
  4568. && (mList[i].mortage_deposit == mList[i + 1].mortage_deposit)
  4569. && (mList[i].mortage_income == mList[i + 1].mortage_income)
  4570. && (mList[i].mortage_propertyvalue == mList[i + 1].mortage_propertyvalue)
  4571. && (mList[i].mortage_reason == mList[i + 1].mortage_reason)
  4572. && (mList[i].mortage_rterm == mList[i + 1].mortage_rterm)
  4573. && (mList[i].mortage_remortagedate == mList[i + 1].mortage_remortagedate)
  4574. && (mList[i].RateType == mList[i + 1].RateType)
  4575. && (mList[i].mortage_mortgagesubtype == mList[i + 1].mortage_mortgagesubtype))
  4576. {
  4577. if (!CheckIfMortgageProductsAreEmpty(mList[i], mList[i + 1]))
  4578. {
  4579. return true;
  4580. }
  4581. }
  4582. }
  4583. return false;
  4584. }
  4585.  
  4586. private bool CheckIfMortgageProductsAreEmpty(Mortgage mortgage1, Mortgage mortgage2)
  4587. {
  4588. if ((string.IsNullOrEmpty(mortgage1.mortgage_address1))
  4589. && (string.IsNullOrEmpty(mortgage2.mortgage_address1))
  4590. && (string.IsNullOrEmpty(mortgage1.mortgage_address2))
  4591. && (string.IsNullOrEmpty(mortgage2.mortgage_address2))
  4592. && (string.IsNullOrEmpty(mortgage1.mortgage_postcode))
  4593. && (string.IsNullOrEmpty(mortgage2.mortgage_postcode))
  4594. && (string.IsNullOrEmpty(mortgage1.mortgage_county))
  4595. && (string.IsNullOrEmpty(mortgage2.mortgage_county))
  4596. && (string.IsNullOrEmpty(mortgage1.mortgage_town))
  4597. && (string.IsNullOrEmpty(mortgage2.mortgage_town))
  4598. && (mortgage1.mortage_amount == null || mortgage1.mortage_amount == 0)
  4599. && (mortgage2.mortage_amount == null || mortgage2.mortage_amount == 0)
  4600. && (mortgage1.mortage_commission == null || mortgage1.mortage_commission == 0)
  4601. && (mortgage2.mortage_commission == null || mortgage2.mortage_commission == 0)
  4602. && (mortgage1.mortgage_Clientfee == null || mortgage1.mortgage_Clientfee == 0)
  4603. && (mortgage2.mortgage_Clientfee == null || mortgage2.mortgage_Clientfee == 0)
  4604. && (mortgage1.mortage_premium == null || mortgage1.mortage_premium == 0)
  4605. && (mortgage2.mortage_premium == null || mortgage2.mortage_premium == 0)
  4606. && (mortgage1.mortage_deposit == null || mortgage1.mortage_deposit == 0)
  4607. && (mortgage2.mortage_deposit == null || mortgage2.mortage_deposit == 0)
  4608. && (mortgage1.mortage_income == null || mortgage1.mortage_income == 0)
  4609. && (mortgage2.mortage_income == null || mortgage2.mortage_income == 0))
  4610. {
  4611. return true;
  4612. }
  4613. return false;
  4614. }
  4615.  
  4616. private static bool IsLifeProductDataDuplicated(IReadOnlyList<Life> lList)
  4617. {
  4618. if (lList == null || lList.Count == 0) return false;
  4619. for (var i = 0; i < lList.Count - 1; i++)
  4620. {
  4621. if ((lList[i].life_AppicationType == lList[i + 1].life_AppicationType)
  4622. && (lList[i].life_financetype == lList[i + 1].life_financetype)
  4623. && (lList[i].life_isPolicyForOtherApplicant == lList[i + 1].life_isPolicyForOtherApplicant)
  4624. && (lList[i].life_status == lList[i + 1].life_status)
  4625. && (lList[i].life_provider == lList[i + 1].life_provider)
  4626. && (lList[i].life_policyref == lList[i + 1].life_policyref)
  4627. && (lList[i].life_policytype == lList[i + 1].life_policytype)
  4628. && (lList[i].life_amount == lList[i + 1].life_amount)
  4629. && (lList[i].life_commission == lList[i + 1].life_commission)
  4630. && (lList[i].life_contactid == lList[i + 1].life_contactid)
  4631. && (lList[i].life_date == lList[i + 1].life_date)
  4632. && (lList[i].life_datelive == lList[i + 1].life_datelive)
  4633. && (lList[i].life_expires == lList[i + 1].life_expires)
  4634. && (lList[i].life_Clientfee == lList[i + 1].life_Clientfee)
  4635. && (lList[i].life_financetype == lList[i + 1].life_financetype)
  4636. && (lList[i].life_height == lList[i + 1].life_height)
  4637. && (lList[i].life_weight == lList[i + 1].life_weight)
  4638. && (lList[i].life_issmoker == lList[i + 1].life_issmoker)
  4639. && (lList[i].life_doctor == lList[i + 1].life_doctor)
  4640. && (lList[i].life_notes == lList[i + 1].life_notes)
  4641. && (lList[i].life_medical == lList[i + 1].life_medical))
  4642. {
  4643. if (!CheckIfLifeProductsAreEmpty(lList[i], lList[i + 1]))
  4644. {
  4645. return true;
  4646. }
  4647. }
  4648. }
  4649. return false;
  4650. }
  4651. /// <summary>
  4652. /// Check If life Products are emply
  4653. /// </summary>
  4654. /// <param name="life1"></param>
  4655. /// <param name="life2"></param>
  4656. /// <returns></returns>
  4657. private static bool CheckIfLifeProductsAreEmpty(Life life1, Life life2)
  4658. {
  4659. if ((life1.life_amount == null || life1.life_amount == 0)
  4660. && (life2.life_amount == null || life2.life_amount == 0)
  4661. && (life1.life_commission == null || life1.life_commission == 0)
  4662. && (life2.life_commission == null || life2.life_commission == 0)
  4663. && (life1.life_Clientfee == null || life1.life_Clientfee == 0)
  4664. && (life2.life_Clientfee == null || life2.life_Clientfee == 0)
  4665. )
  4666. {
  4667. return true;
  4668. }
  4669. return false;
  4670. }
  4671. /// <summary>
  4672. /// Check If the building products are duplicated
  4673. /// </summary>
  4674. /// <param name="bcList"></param>
  4675. /// <returns></returns>
  4676. private static bool IsBuildingContentProductDataDuplicated(IReadOnlyList<BuildingContent> bcList)
  4677. {
  4678. if (bcList == null || bcList.Count == 0) return false;
  4679. for (var i = 0; i < bcList.Count - 1; i++)
  4680. {
  4681. if ((bcList[i].buildingcontent_status == bcList[i + 1].buildingcontent_status)
  4682. && (bcList[i].buildingcontent_financetype == bcList[i + 1].buildingcontent_financetype)
  4683. && (bcList[i].buildingcontent_isPolicyForOtherApplicant == bcList[i + 1].buildingcontent_isPolicyForOtherApplicant)
  4684. && (bcList[i].buildingcontent_provider == bcList[i + 1].buildingcontent_provider)
  4685. && (bcList[i].buildingcontent_policyref == bcList[i + 1].buildingcontent_policyref)
  4686. && (bcList[i].buildingcontent_amount == bcList[i + 1].buildingcontent_amount)
  4687. && (bcList[i].buildingcontent_commission == bcList[i + 1].buildingcontent_commission)
  4688. && (bcList[i].buildingcontent_type == bcList[i + 1].buildingcontent_type)
  4689. && (bcList[i].buildingcontent_premium == bcList[i + 1].buildingcontent_premium)
  4690. && (bcList[i].buildingcontent_rebuildvalue == bcList[i + 1].buildingcontent_rebuildvalue)
  4691. && (bcList[i].buildingcontent_isalarmed == bcList[i + 1].buildingcontent_isalarmed)
  4692. && (bcList[i].buildingcontent_addcover == bcList[i + 1].buildingcontent_addcover)
  4693. && (bcList[i].buildingcontent_datelive == bcList[i + 1].buildingcontent_datelive)
  4694. && (bcList[i].buildingcontent_expires == bcList[i + 1].buildingcontent_expires)
  4695. && (bcList[i].buildingcontent_followup == bcList[i + 1].buildingcontent_followup)
  4696. && (bcList[i].buildingcontent_date == bcList[i + 1].buildingcontent_date)
  4697. && (bcList[i].buildingcontent_time == bcList[i + 1].buildingcontent_time)
  4698. && (bcList[i].buildingcontent_notes == bcList[i + 1].buildingcontent_notes)
  4699. && (bcList[i].buildingContent_contactid == bcList[i + 1].buildingContent_contactid)
  4700. && (bcList[i].buildingContent_AppicationType == bcList[i + 1].buildingContent_AppicationType)
  4701. && (bcList[i].building_Clientfee == bcList[i + 1].building_Clientfee))
  4702. {
  4703. if (!CheckIfBuildingsProductsAreEmpty(bcList[i], bcList[i + 1]))
  4704. {
  4705. return true;
  4706. }
  4707. }
  4708. }
  4709. return false;
  4710. }
  4711. /// <summary>
  4712. /// Checking if the building products are empty
  4713. /// </summary>
  4714. /// <param name="buildingContent1"></param>
  4715. /// <param name="buildingContent2"></param>
  4716. /// <returns></returns>
  4717. private static bool CheckIfBuildingsProductsAreEmpty(BuildingContent buildingContent1, BuildingContent buildingContent2)
  4718. {
  4719. if ((buildingContent1.buildingcontent_amount == null || buildingContent1.buildingcontent_amount == 0)
  4720. && (buildingContent2.buildingcontent_amount == null || buildingContent2.buildingcontent_amount == 0)
  4721. && (buildingContent1.building_Clientfee == null || buildingContent1.building_Clientfee == 0)
  4722. && (buildingContent2.building_Clientfee == null || buildingContent2.building_Clientfee == 0)
  4723. && (buildingContent1.buildingcontent_commission == null || buildingContent1.buildingcontent_commission == 0)
  4724. && (buildingContent2.buildingcontent_commission == null || buildingContent2.buildingcontent_commission == 0)
  4725. && (buildingContent1.buildingcontent_premium == null || buildingContent1.buildingcontent_premium == 0)
  4726. && (buildingContent2.buildingcontent_premium == null || buildingContent2.buildingcontent_premium == 0)
  4727. )
  4728. {
  4729. return true;
  4730. }
  4731. return false;
  4732. }
  4733. /// <summary>
  4734. /// Checking if the pension product data are dupllicated
  4735. /// </summary>
  4736. /// <param name="pList"></param>
  4737. /// <returns></returns>
  4738. private static bool IsPensionProductDataDuplicated(IReadOnlyList<Pensions> pList)
  4739. {
  4740. if (pList == null || pList.Count == 0) return false;
  4741. for (var i = 0; i < pList.Count - 1; i++)
  4742. {
  4743. if ((pList[i].pension_status == pList[i + 1].pension_status)
  4744. && (pList[i].pension_financetype == pList[i + 1].pension_financetype)
  4745. && (pList[i].pension_isPolicyForOtherApplicant == pList[i + 1].pension_isPolicyForOtherApplicant)
  4746. && (pList[i].pension_provider == pList[i + 1].pension_provider)
  4747. && (pList[i].pension_policyref == pList[i + 1].pension_policyref)
  4748. && (pList[i].pension_amount == pList[i + 1].pension_amount)
  4749. && (pList[i].pension_commission == pList[i + 1].pension_commission)
  4750. && (pList[i].pension_riskprofile == pList[i + 1].pension_riskprofile)
  4751. && (pList[i].pension_riskprofile == pList[i + 1].pension_riskprofile)
  4752. && (pList[i].pension_contributors == pList[i + 1].pension_contributors)
  4753. && (pList[i].pension_fundvalue == pList[i + 1].pension_fundvalue)
  4754. && (pList[i].pension_incomereq == pList[i + 1].pension_incomereq)
  4755. && (pList[i].pension_datelive == pList[i + 1].pension_datelive)
  4756. && (pList[i].pension_expires == pList[i + 1].pension_expires)
  4757. && (pList[i].pension_followup == pList[i + 1].pension_followup)
  4758. && (pList[i].pension_date == pList[i + 1].pension_date)
  4759. && (pList[i].pension_time == pList[i + 1].pension_time)
  4760. && (pList[i].pension_notes == pList[i + 1].pension_notes)
  4761. // && (pList[i].pension_Name == pList[i + 1].pension_Name)
  4762. && (pList[i].pension_contactid == pList[i + 1].pension_contactid)
  4763. && (pList[i].pension_AppicationType == pList[i + 1].pension_AppicationType)
  4764. && (pList[i].pension_Clientfee == pList[i + 1].pension_Clientfee))
  4765. {
  4766. if (!CheckIfPensionProductsAreEmpty(pList[i], pList[i + 1]))
  4767. {
  4768. return true;
  4769. }
  4770. }
  4771. }
  4772. return false;
  4773. }
  4774. /// <summary>
  4775. /// Checking if the pension product are empty
  4776. /// </summary>
  4777. /// <param name="pensions1"></param>
  4778. /// <param name="pensions2"></param>
  4779. /// <returns></returns>
  4780. private static bool CheckIfPensionProductsAreEmpty(Pensions pensions1, Pensions pensions2)
  4781. {
  4782. if (
  4783. (pensions1.pension_amount == null || pensions1.pension_amount == 0)
  4784. && (pensions2.pension_amount == null || pensions2.pension_amount == 0)
  4785. && (pensions1.pension_Clientfee == null || pensions1.pension_Clientfee == 0)
  4786. && (pensions2.pension_Clientfee == null || pensions2.pension_Clientfee == 0)
  4787. && (pensions1.pension_commission == null || pensions1.pension_commission == 0)
  4788. && (pensions2.pension_commission == null || pensions2.pension_commission == 0)
  4789. && (pensions1.pension_fundvalue == null || pensions1.pension_fundvalue == 0)
  4790. && (pensions2.pension_fundvalue == null || pensions2.pension_fundvalue == 0)
  4791. )
  4792. {
  4793. return true;
  4794. }
  4795. return false;
  4796. }
  4797. /// <summary>
  4798. /// Checking is the Investment Prodct are dupliated
  4799. /// </summary>
  4800. /// <param name="iList"></param>
  4801. /// <returns></returns>
  4802. private static bool IsInvestmentProductDataDuplicated(IReadOnlyList<Investment> iList)
  4803. {
  4804. if (iList == null || iList.Count == 0) return false;
  4805. for (var i = 0; i < iList.Count - 1; i++)
  4806. {
  4807. if ((iList[i].investment_isPolicyForOtherApplicant == iList[i + 1].investment_isPolicyForOtherApplicant)
  4808. && (iList[i].investment_financetype == iList[i + 1].investment_financetype)
  4809. && (iList[i].investment_status == iList[i + 1].investment_status)
  4810. && (iList[i].investment_provider == iList[i + 1].investment_provider)
  4811. && (iList[i].investment_policyref == iList[i + 1].investment_policyref)
  4812. && (iList[i].investment_amount == iList[i + 1].investment_amount)
  4813. && (iList[i].investment_commission == iList[i + 1].investment_commission)
  4814. && (iList[i].investment_riskprofiles == iList[i + 1].investment_riskprofiles)
  4815. && (iList[i].investment_contributors == iList[i + 1].investment_contributors)
  4816. && (iList[i].investment_fundvalue == iList[i + 1].investment_fundvalue)
  4817. && (iList[i].investment_datelive == iList[i + 1].investment_datelive)
  4818. && (iList[i].investment_expires == iList[i + 1].investment_expires)
  4819. && (iList[i].investment_followup == iList[i + 1].investment_followup)
  4820. && (iList[i].investment_date == iList[i + 1].investment_date)
  4821. && (iList[i].investment_time == iList[i + 1].investment_time)
  4822. && (iList[i].investment_notes == iList[i + 1].investment_notes)
  4823. // && (iList[i].Investment_Name == iList[i + 1].Investment_Name)
  4824. && (iList[i].investment_contactid == iList[i + 1].investment_contactid)
  4825. && (iList[i].investment_AppicationType == iList[i + 1].investment_AppicationType)
  4826. && (iList[i].investment_Clientfee == iList[i + 1].investment_Clientfee))
  4827. {
  4828. if (!CheckIfInvestmentProductsAreEmpty(iList[i], iList[i + 1]))
  4829. {
  4830. return true;
  4831. }
  4832. }
  4833. }
  4834. return false;
  4835. }
  4836. /// <summary>
  4837. /// Check if the Investment Products are emply
  4838. /// </summary>
  4839. /// <param name="investment1"></param>
  4840. /// <param name="investment2"></param>
  4841. /// <returns></returns>
  4842. private static bool CheckIfInvestmentProductsAreEmpty(Investment investment1, Investment investment2)
  4843. {
  4844. if ((investment1.investment_amount == null || investment1.investment_amount == 0)
  4845. && (investment2.investment_amount == null || investment2.investment_amount == 0)
  4846. && (investment1.investment_commission == null || investment1.investment_commission == 0)
  4847. && (investment2.investment_commission == null || investment2.investment_commission == 0)
  4848. && (investment1.investment_fundvalue == null || investment1.investment_fundvalue == 0)
  4849. && (investment2.investment_fundvalue == null || investment2.investment_fundvalue == 0)
  4850. && (investment1.investment_Clientfee == null || investment1.investment_Clientfee == 0)
  4851. && (investment2.investment_Clientfee == null || investment2.investment_Clientfee == 0)
  4852. )
  4853. {
  4854. return true;
  4855. }
  4856. return false;
  4857. }
  4858.  
  4859. #endregion [Lead Product Data Duplication Check 20150720]
  4860.  
  4861. public static int CheckDynamicVariableForNull(dynamic value)
  4862. {
  4863. if (value != null)
  4864. return value;
  4865. else
  4866. return 0;
  4867.  
  4868. }
  4869.  
  4870. /// <summary>
  4871. /// Enter errors in error log table.
  4872. /// </summary>
  4873. /// <param name="inEx"></param>
  4874. public void ErrorLog(string inEx,int? userId=0)
  4875. {
  4876. var dbOccfinance5572Entities = new db_occfinance_5572Entities();
  4877. string _Name = "", _EmailId = "";
  4878. int _AddBy =0;
  4879. if (HttpContext.Current.Session != null)
  4880. {
  4881. _Name = Convert.ToString(HttpContext.Current.Session["ContactName"] ?? "");
  4882. _AddBy = Convert.ToInt32(HttpContext.Current.Session["LoggedInUserId"] ?? "0");
  4883. _EmailId = Convert.ToString(HttpContext.Current.Session["ContactEmail"] ?? "");
  4884. }
  4885. else
  4886. {
  4887. _AddBy =userId??0;
  4888. }
  4889.  
  4890. var errlg = new tblErrorLog
  4891. {
  4892. Action = "CheckLeadDataDuplication",
  4893. AddBy = _AddBy,
  4894. AddDate = DateTime.Now,
  4895. Controller = "AppTrack Controller",
  4896. EmailId = _EmailId,
  4897. ErrorDate = DateTime.Now,
  4898. HelpLink = "Data Duplicity Error on Application page",
  4899. InnerMsg = inEx,
  4900. IsActive = true,
  4901. IsDeleted = false,
  4902. IsResolved = false,
  4903. Message = inEx,
  4904. Name = _Name,
  4905. Source = inEx,
  4906. Trace = inEx,
  4907. URL = "Data Duplicity Error on Application Page"
  4908. };
  4909.  
  4910. dbOccfinance5572Entities.tblErrorLog.Add(errlg);
  4911. dbOccfinance5572Entities.SaveChanges();
  4912. }
  4913.  
  4914.  
  4915.  
  4916. #endregion [Lead Data Duplicity Check]
  4917.  
  4918. #region [Delete Leads 2015-03-19]
  4919.  
  4920. /// <summary>
  4921. /// Delete lead records
  4922. /// </summary>
  4923. /// <param name="financeId"></param>
  4924. /// <returns></returns>
  4925. public bool DeleteLeads(int financeId)
  4926. {
  4927. bool result = false;
  4928. var lead = ctx.tblfinances.Where(res => res.financeid == financeId).FirstOrDefault();
  4929. if (lead != null)
  4930. {
  4931. if (lead.type == 1)
  4932. {
  4933. lead.status = 34;
  4934. result = true;
  4935. }
  4936. if (lead.type == 2)
  4937. {
  4938. lead.status = 35;
  4939. result = true;
  4940. }
  4941. if (lead.type == 3)
  4942. {
  4943. lead.status = 36;
  4944. result = true;
  4945. }
  4946. if (lead.type == 4)
  4947. {
  4948. lead.status = 37;
  4949. result = true;
  4950. }
  4951. if (lead.status == 5)
  4952. {
  4953. lead.status = 38;
  4954. result = true;
  4955. }
  4956. ctx.SaveChanges();
  4957. }
  4958. return result;
  4959. }
  4960. #endregion [Delete Leads 2015-03-19]
  4961.  
  4962. #region [Display Address and Lender Details 2015-03-20]
  4963.  
  4964. public enum ProductType
  4965. {
  4966. Mortgage = 1,
  4967. Life = 2,
  4968. Building = 3,
  4969. Pension = 4,
  4970. Investment = 5
  4971. }
  4972.  
  4973. public enum LifePolicyType
  4974. {
  4975. LifeInsurance = 1,
  4976. CriticalIllness = 2,
  4977. IncomeProtection = 3
  4978. }
  4979.  
  4980. public enum BuildingPolicyType
  4981. {
  4982. House = 1,
  4983. Flat = 2
  4984. }
  4985.  
  4986. public enum PensionPolicyType
  4987. {
  4988. High = 1,
  4989. Medium = 2,
  4990. Low = 3
  4991. }
  4992.  
  4993. public enum InvestmentPolicyType
  4994. {
  4995. High = 1,
  4996. Medium = 2,
  4997. Low = 3
  4998. }
  4999.  
  5000. /// <summary>
  5001. /// Display address and lender details
  5002. /// </summary>
  5003. /// <param name="financeId"></param>
  5004. /// <returns></returns>
  5005. public string DisplayAddressAndLender(int financeId)
  5006. {
  5007. try
  5008. {
  5009. string firstSection = string.Empty;
  5010. string secondSection = string.Empty;
  5011. var product = (from con in ctx.tblcontacts
  5012. join fin in ctx.tblfinances on con.contactid equals fin.contact
  5013. where fin.financeid == financeId
  5014. select new
  5015. {
  5016. fin.type,
  5017. fin.Address1,
  5018. fin.provider,
  5019. fin.Address2,
  5020. fin.subtype
  5021. }).FirstOrDefault();
  5022. //Set second section
  5023. if (product.provider != null && product.provider != "")
  5024. {
  5025. secondSection = product.provider.StringToLength(15);
  5026. }
  5027. else
  5028. {
  5029. secondSection = "N/A";
  5030. }
  5031.  
  5032. //Set first section
  5033. if (product.type == (int)ProductType.Mortgage)
  5034. {
  5035. if ((product.Address1 != null && product.Address1 != ""))
  5036. {
  5037. firstSection = product.Address1.StringToLength(22);
  5038. }
  5039. else
  5040. {
  5041. firstSection = "N/A";
  5042. }
  5043. }
  5044. else if (product.type == (int)ProductType.Life)
  5045. {
  5046. if (product.subtype == (int)LifePolicyType.CriticalIllness)
  5047. {
  5048. firstSection = LifePolicyType.CriticalIllness.ToString().StringToLength(14);
  5049. }
  5050. else if (product.subtype == (int)LifePolicyType.IncomeProtection)
  5051. {
  5052. firstSection = LifePolicyType.IncomeProtection.ToString().StringToLength(14);
  5053. }
  5054. else if (product.subtype == (int)LifePolicyType.LifeInsurance)
  5055. {
  5056. firstSection = LifePolicyType.LifeInsurance.ToString().StringToLength(14);
  5057. }
  5058. else
  5059. {
  5060. firstSection = "N/A";
  5061. }
  5062. }
  5063. else if (product.type == (int)ProductType.Building)
  5064. {
  5065. if (product.subtype == (int)BuildingPolicyType.Flat)
  5066. {
  5067. firstSection = BuildingPolicyType.Flat.ToString().StringToLength(14);
  5068. }
  5069. else if (product.subtype == (int)BuildingPolicyType.House)
  5070. {
  5071. firstSection = BuildingPolicyType.House.ToString().StringToLength(14);
  5072. }
  5073. else
  5074. {
  5075. firstSection = "N/A";
  5076. }
  5077. }
  5078. else if (product.type == (int)ProductType.Pension)
  5079. {
  5080. if (product.subtype == (int)PensionPolicyType.High)
  5081. {
  5082. firstSection = PensionPolicyType.High.ToString().StringToLength(14);
  5083. }
  5084. else if (product.subtype == (int)PensionPolicyType.Low)
  5085. {
  5086. firstSection = PensionPolicyType.Low.ToString().StringToLength(14);
  5087. }
  5088. else if (product.subtype == (int)PensionPolicyType.Medium)
  5089. {
  5090. firstSection = PensionPolicyType.Medium.ToString().StringToLength(14);
  5091. }
  5092. else
  5093. {
  5094. firstSection = "N/A";
  5095. }
  5096. }
  5097. else if (product.type == (int)ProductType.Investment)
  5098. {
  5099. if (product.subtype == (int)InvestmentPolicyType.High)
  5100. {
  5101. firstSection = InvestmentPolicyType.High.ToString().StringToLength(14);
  5102. }
  5103. else if (product.subtype == (int)InvestmentPolicyType.Low)
  5104. {
  5105. firstSection = InvestmentPolicyType.Low.ToString().StringToLength(14);
  5106. }
  5107. else if (product.subtype == (int)InvestmentPolicyType.Medium)
  5108. {
  5109. firstSection = InvestmentPolicyType.Medium.ToString().StringToLength(14);
  5110. }
  5111. else
  5112. {
  5113. firstSection = "N/A";
  5114. }
  5115. }
  5116.  
  5117.  
  5118. return (", " + firstSection + ", " + secondSection);
  5119.  
  5120. }
  5121. catch (Exception ex)
  5122. {
  5123. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetUserName", ex.Message, ex.Source, ex.StackTrace, ex.HelpLink);
  5124. return string.Empty;
  5125. }
  5126. }
  5127.  
  5128. #endregion [Display Address and Lender Details 2015-03-20]
  5129.  
  5130. #region [Delete All Product of Lead 2015-04-10]
  5131. public int DeleteLeadProducts(int contactId)
  5132. {
  5133. int result = 0;
  5134. try
  5135. {
  5136. var lead = ctx.tblcontacts.Where(x => x.contactid == contactId).FirstOrDefault();
  5137. var leadMortgage = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 1).ToList();
  5138. var leadLife = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 2).ToList();
  5139. var leadBuilding = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 3).ToList();
  5140. var leadPension = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 4).ToList();
  5141. var leadInvestment = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 5).ToList();
  5142. if (lead != null)
  5143. {
  5144. lead.IsDeleted = true;
  5145. lead.modified = System.DateTime.Now;
  5146. }
  5147. foreach (var productMortgage in leadMortgage)
  5148. {
  5149. productMortgage.status = 34;
  5150. productMortgage.updated = System.DateTime.Now;
  5151. }
  5152. foreach (var productLife in leadLife)
  5153. {
  5154. productLife.status = 35;
  5155. productLife.updated = System.DateTime.Now;
  5156. }
  5157. foreach (var productbuildng in leadBuilding)
  5158. {
  5159. productbuildng.status = 36;
  5160. productbuildng.updated = System.DateTime.Now;
  5161. }
  5162. foreach (var productPolicy in leadPension)
  5163. {
  5164. productPolicy.status = 37;
  5165. productPolicy.updated = System.DateTime.Now;
  5166. }
  5167. foreach (var productInvstmnt in leadInvestment)
  5168. {
  5169.  
  5170. productInvstmnt.status = 38;
  5171. productInvstmnt.updated = System.DateTime.Now;
  5172. }
  5173. result = ctx.SaveChanges();
  5174. }
  5175. catch
  5176. {
  5177.  
  5178. }
  5179. return result;
  5180.  
  5181. }
  5182. #endregion [Delete All Product of Lead 2015-04-10]
  5183.  
  5184.  
  5185. #region [Get All Contacts for Referring Client 2015]
  5186. public List<Clients> GetAllContact()
  5187. {
  5188. List<Clients> list = new List<Clients>();
  5189. list = ctx.tblcontacts.Where(x => (x.IsDeleted ?? false) == false && (x.firstname != null || x.lastname != null)).OrderBy(x => x.firstname).ThenBy(x => x.lastname).Select(x => new Clients { contactId = x.contactid, contactname = (x.firstname ?? "") + " " + (x.lastname ?? ""), email = x.email, telephone = x.telephone }).ToList();
  5190. return list;
  5191. }
  5192. #endregion[Get All Contacts for Referring Client 2015]
  5193.  
  5194. #region [Get All Contacts for Referring Client in report 2015]
  5195. public List<Clients> GetAllContactForReport()
  5196. {
  5197. //1583028 is the account id of client Referral Account
  5198.  
  5199. var ReferrerList = ctx.tblcontacts.Where(x => x.parentContactId != null && x.parentContactId != 0 && x.account != 1582971 && x.account == 1583028 && (x.IsDeleted ?? false) == false).Select(x => x.parentContactId).Distinct().ToList();
  5200.  
  5201. var ReferrerList1 = ctx.tblcontacts.Where(x => x.parentNegId != null && x.parentNegId != 0 && x.account != 1582971 && x.account == 1583028 && (x.IsDeleted ?? false) == false).Select(x => x.parentNegId).Distinct().ToList();
  5202. List<Clients> list = new List<Clients>();
  5203. List<Clients> list1 = new List<Clients>();
  5204. list = ctx.tblcontacts.Where(x => (ReferrerList.Contains(x.contactid)) && x.account != 1582971).OrderBy(x => x.firstname).ThenBy(x => x.lastname).ToList().Select(x => new Clients { affiliateid = x.contactid.ToString(), contactname = (x.firstname ?? "") + " " + (x.lastname ?? ""), email = x.email, telephone = x.telephone }).ToList();
  5205.  
  5206. list1 = ctx.tblClientReferralNeg.Where(x => (ReferrerList1.Contains(x.NegId))).ToList().Select(x => new Clients { affiliateid = (x.NegId.ToString() + "_neg"), contactname = x.NegName }).ToList();
  5207.  
  5208. var list2 = list.Concat(list1).ToList();
  5209.  
  5210. return list2;
  5211.  
  5212. }
  5213. #endregion[Get All Contacts for Referring Client in report 2015]
  5214.  
  5215.  
  5216. public bool NewLifeProduct(int contactId, int userId)
  5217. {
  5218. var result = false;
  5219. try
  5220. {
  5221. bool IsLifeProductExist = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 2 && x.status != 35).Any();
  5222.  
  5223. if (!IsLifeProductExist)
  5224. {
  5225. var _chkLifeupdate = true;
  5226. tblfinance newfinance = new tblfinance();
  5227. newfinance.contact = contactId;
  5228. newfinance.type = (int)FinanceTypes.Life;
  5229. newfinance.status = 10;
  5230. newfinance.subtype = 0;
  5231. newfinance.policyreference = null;
  5232. newfinance.followup = Helper.GetFollowUp(null, null == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : null);
  5233. newfinance.provider = null;
  5234. newfinance.amount = null;
  5235. newfinance.premium = null;
  5236. newfinance.commission = null;
  5237. newfinance.details = null;
  5238. newfinance.updated = System.DateTime.Now;
  5239. newfinance.datelive = Helper.GetDate(null);
  5240. newfinance.dateexpire = Helper.GetDate(null);
  5241. newfinance.created = System.DateTime.Now;
  5242.  
  5243. //Added Client Fee 27-10-2014
  5244. newfinance.ClientFee = null;
  5245. //********************************
  5246. newfinance.isPolicyForOtherApplicant = false;
  5247.  
  5248. ctx.tblfinances.Add(newfinance);
  5249.  
  5250. //insert
  5251. tblfinance_insurance newfinance_insurance = new tblfinance_insurance();
  5252. newfinance_insurance.smoker = false;
  5253. newfinance_insurance.height = null;
  5254. newfinance_insurance.weight = null;
  5255. newfinance_insurance.doctor = null;
  5256. ctx.tblfinance_insurance.Add(newfinance_insurance);
  5257.  
  5258. if (_chkLifeupdate)
  5259. {
  5260. tblnote lifenoteforstatus = new tblnote();
  5261. lifenoteforstatus.financeid = newfinance.financeid;
  5262. lifenoteforstatus.contact = contactId;
  5263. lifenoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == 10).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userId).Select(x => x.brokername).FirstOrDefault());
  5264. lifenoteforstatus.created = System.DateTime.Now;
  5265. lifenoteforstatus.UserId = userId;
  5266. ctx.tblnotes.Add(lifenoteforstatus);
  5267.  
  5268. }
  5269. ctx.SaveChanges();
  5270. result = true;
  5271. }
  5272. else
  5273. {
  5274. result = true;
  5275. }
  5276. }
  5277. catch
  5278. {
  5279.  
  5280. }
  5281. return result;
  5282. }
  5283. public bool NewBuildingProduct(int contactId, int userId)
  5284. {
  5285. var result = false;
  5286. try
  5287. {
  5288. bool IsBuildingProductExist = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 3).Any();
  5289. bool IsPensionProductExist = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 4).Any();
  5290. bool IsInvestmentProductExist = ctx.tblfinances.Where(x => x.contact == contactId && x.type == 5).Any();
  5291. if (!IsBuildingProductExist)
  5292. {
  5293. tblfinance newfinance = new tblfinance();
  5294. newfinance.contact = contactId;
  5295. newfinance.type = (int)FinanceTypes.BuildingsOrContents;
  5296. newfinance.status = 16;
  5297. newfinance.subtype = 0;
  5298. newfinance.policyreference = null;
  5299. newfinance.followup = Helper.GetFollowUp(null, null == null ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : null);
  5300. newfinance.provider = null;
  5301. newfinance.amount = null;
  5302. newfinance.premium = null;
  5303. newfinance.commission = null;
  5304. newfinance.details = null;
  5305. newfinance.updated = System.DateTime.Now;
  5306. newfinance.created = System.DateTime.Now;
  5307. newfinance.datelive = Helper.GetDate(null);
  5308. newfinance.dateexpire = Helper.GetDate(null);
  5309.  
  5310. //Added Client Fee 27-10-2014
  5311. newfinance.ClientFee = null;
  5312. //********************************
  5313. newfinance.isPolicyForOtherApplicant = false;
  5314. ctx.tblfinances.Add(newfinance);
  5315.  
  5316. //insert
  5317. tblfinance_buildings newfinance_building = new tblfinance_buildings();
  5318. newfinance_building.rebuild = null;
  5319. newfinance_building.alarm = false;
  5320. ctx.tblfinance_buildings.Add(newfinance_building);
  5321.  
  5322. //insert note
  5323. tblnote buildingcontentnoteforstatus = new tblnote();
  5324. //buildingcontentnoteforstatus.noteid = _noteid;
  5325. buildingcontentnoteforstatus.financeid = newfinance.financeid;
  5326. buildingcontentnoteforstatus.contact = contactId;
  5327. buildingcontentnoteforstatus.note = string.Format("Initial status is set as {0} on {1} at {2} by {3}", ctx.tblfinancestatus.Where(x => x.statusid == 16).Select(x => x.name).FirstOrDefault(), System.DateTime.Now.ToString("dd/MM/yyyy"), System.DateTime.Now.ToString("HH:mm"), ctx.tblusers.Where(x => x.userid == userId).Select(x => x.brokername).FirstOrDefault());
  5328.  
  5329. buildingcontentnoteforstatus.created = System.DateTime.Now;
  5330. buildingcontentnoteforstatus.UserId = userId;
  5331. ctx.tblnotes.Add(buildingcontentnoteforstatus);
  5332.  
  5333.  
  5334. ctx.SaveChanges();
  5335. result = true;
  5336. }
  5337. else
  5338. {
  5339. result = true;
  5340. }
  5341. }
  5342. catch
  5343. {
  5344.  
  5345. }
  5346. return result;
  5347. }
  5348.  
  5349. #region [Adding Negotiator in Client Referral Negotiator table]
  5350. public int AddNeg(string Neg)
  5351. {
  5352. int Id = 0;
  5353. try
  5354. {
  5355. var IsAlready = ctx.tblClientReferralNeg.Where(x => x.NegName.Trim().ToUpper() == Neg.Trim().ToUpper()).FirstOrDefault();
  5356. if (IsAlready == null)
  5357. {
  5358. tblClientReferralNeg obj = new tblClientReferralNeg();
  5359. obj.NegName = Neg.Trim();
  5360. obj.CreatedDate = DateTime.Now;
  5361. ctx.tblClientReferralNeg.Add(obj);
  5362. ctx.SaveChanges();
  5363. Id = obj.NegId;
  5364. }
  5365. else
  5366. {
  5367. Id = IsAlready.NegId;
  5368. }
  5369. return Id;
  5370. }
  5371. catch
  5372. {
  5373. return Id;
  5374. }
  5375. }
  5376. #endregion
  5377.  
  5378. public int SaveBasicIntellicalcDetails(string com, string con, string tel, string email)
  5379. {
  5380. int Id = 0;
  5381. try
  5382. {
  5383. SqlParameter[] Param = new SqlParameter[5];
  5384. Param[0] = new SqlParameter("@CompanyName", com);
  5385. Param[1] = new SqlParameter("@ContactName", con);
  5386. Param[2] = new SqlParameter("@Email", email);
  5387. Param[3] = new SqlParameter("@Telephone", tel);
  5388.  
  5389. SqlParameter id = new SqlParameter();
  5390. id.SqlDbType = SqlDbType.Int;
  5391. id.Direction = ParameterDirection.Output;
  5392. id.ParameterName = "@new_identity";
  5393. Param[4] = id;
  5394. string _procedurename = "IntellicalcBasicDetailsAdd";
  5395.  
  5396. SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, _procedurename, Param);
  5397. Id = Convert.ToInt32(Param[4].Value);
  5398. }
  5399. catch
  5400. {
  5401.  
  5402. }
  5403. return Id;
  5404. }
  5405.  
  5406. #region [Main Lead Report Updated Procedures 2015-11-06]
  5407.  
  5408. /// <summary>
  5409. /// Method for Brroker Lead Report
  5410. /// </summary>
  5411. /// <param name="Conditions">Dynamic query for main report</param>
  5412. /// <param name="accountid">Account Id</param>
  5413. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5414. /// <param name="dateFilter">Dynamic Date filter condition</param>
  5415. /// <returns>Broker List</returns>
  5416. public List<BrokerLeadTable> GetBrokerLeadReportByIds(string Conditions, int accountid, string Conditions1 = "", string dateFilter = "")
  5417. {
  5418. List<BrokerLeadTable> BrokerLeadTableList = new List<BrokerLeadTable>();
  5419. try
  5420. {
  5421. SqlParameter[] Param = new SqlParameter[3];
  5422. Param[0] = new SqlParameter("@conditions", Conditions);
  5423. Param[1] = new SqlParameter("@conditions1", Conditions1);
  5424. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  5425. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllBrokerReport_Update", Param);
  5426.  
  5427. BrokerLeadTableList = (from DataRow row in dt.Rows
  5428. select new BrokerLeadTable
  5429. {
  5430. Brokerid = Convert.ToInt32(row["brokerid"]),
  5431. BrokerName = Convert.ToString(row["broker"]),
  5432. LeadPercent = "0",
  5433. NoOfLeads = Convert.ToInt32(row["TotalCount"].ToString()),
  5434. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString())
  5435. }).ToList();
  5436.  
  5437.  
  5438. return BrokerLeadTableList.OrderByDescending(x => x.NoOfLeads).ToList();
  5439. }
  5440. catch (Exception ex)
  5441. {
  5442. Helper.ErrorLog(ex.InnerException, "QApplication", "GetBrokerLeadReport", ex.Message);
  5443. return BrokerLeadTableList.ToList();
  5444. }
  5445. }
  5446.  
  5447. /// <summary>
  5448. /// Method for Agent Lead Report
  5449. /// </summary>
  5450. /// <param name="Conditions">Dynamic query for main report</param>
  5451. /// <param name="accountid">Account Id</param>
  5452. /// <param name="isNeg">Negotiator</param>
  5453. /// <param name="Condition1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5454. /// <param name="dateFilter">Dynamic Date filter condition</param>
  5455. /// <returns>Agent List</returns>
  5456. public List<AgentLeadTable> GetAfflicateLeadReportByIds(string Conditions, int accountid, bool isNeg, string Condition1 = "", string dateFilter = "")
  5457. {
  5458. List<AgentLeadTable> AgentLeadList = new List<AgentLeadTable>();
  5459. try
  5460. {
  5461. SqlParameter[] Param = new SqlParameter[3];
  5462. Param[0] = new SqlParameter("@conditions", Conditions);
  5463. if (!string.IsNullOrEmpty(Condition1))
  5464. Param[1] = new SqlParameter("@conditions1", Condition1);
  5465. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  5466. string _procName = "GetAllAfflicateReport_Update";
  5467. //Check For Is Negotiation When the negotiator selected the below procedure will be selected elase the old
  5468.  
  5469. if (isNeg)
  5470. _procName = "GetAllAfflicateReport_Neg_Update";
  5471. //****************************************
  5472.  
  5473. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  5474.  
  5475. AgentLeadList = (from DataRow row in dt.Rows
  5476. select new AgentLeadTable
  5477. {
  5478. AgentId = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? " " : row["account"]).ToString()),
  5479. AgentName = ((row["agentname"] == DBNull.Value || row["agentname"] == null) ? " " : row["agentname"]).ToString(),
  5480. LeadPercent = "0",
  5481. NoOfLeads = Convert.ToInt32(((row["TotalCount"] == DBNull.Value || row["TotalCount"] == null) ? " " : row["TotalCount"]).ToString()),
  5482. NegName = (isNeg == true ? ((row["negref"] == DBNull.Value || row["negref"] == null) ? " " : row["negref"]).ToString() : ""),
  5483. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString()),
  5484. }).ToList();
  5485.  
  5486. return AgentLeadList.OrderByDescending(x => x.NoOfLeads).ToList();
  5487. }
  5488. catch (Exception ex)
  5489. {
  5490. Helper.ErrorLog(ex.InnerException, "QApplication", "GetAfflicateLeadReport_Update", ex.Message);
  5491. return AgentLeadList.ToList();
  5492. }
  5493. }
  5494.  
  5495. /// <summary>
  5496. /// Method for Agent Lead Report with Branch
  5497. /// </summary>
  5498. /// <param name="Conditions">Dynamic query for main report</param>
  5499. /// <param name="accountid">Account Id</param>
  5500. /// <param name="isBranch">Branch</param>
  5501. /// <param name="Condition1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5502. /// <param name="dateFilter">Dynamic Date filter condition</param>
  5503. /// <returns></returns>
  5504. public List<AgentLeadTableBranch> GetAfflicateLeadReport_BranchByIds(string Conditions, int accountid, bool isBranch, string Condition1 = "", string dateFilter = "")
  5505. {
  5506. List<AgentLeadTableBranch> AgentLeadBranchList = new List<AgentLeadTableBranch>();
  5507. try
  5508. {
  5509. SqlParameter[] Param = new SqlParameter[3];
  5510. Param[0] = new SqlParameter("@conditions", Conditions);
  5511. Param[1] = new SqlParameter("@conditions1", Condition1);
  5512. Param[2] = new SqlParameter("@DateFilter", dateFilter);
  5513. string _procName = "GetAllAfflicateReport_NullBranch";
  5514. //Check For Is Negotiation When the negotiator selected the below procedure will be selected elase the old
  5515. if (isBranch)
  5516. _procName = "GetAllAfflicateReport_Branch_Update";
  5517.  
  5518.  
  5519. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, _procName, Param);
  5520.  
  5521. AgentLeadBranchList = (from DataRow row in dt.Rows
  5522. select new AgentLeadTableBranch
  5523. {
  5524. AgentId = Convert.ToInt32(((row["account"] == DBNull.Value || row["account"] == null) ? " " : row["account"]).ToString()),
  5525. AgentName = ((row["agentname"] == DBNull.Value || row["agentname"] == null) ? " " : row["agentname"]).ToString(), //GetAgentName(Convert.ToInt32(row["account"])),
  5526. LeadPercent = "0",
  5527. NoOfLeads = Convert.ToInt32(((row["TotalCount"] == DBNull.Value || row["TotalCount"] == null) ? " " : row["TotalCount"]).ToString()),
  5528. Branch = (isBranch == true ? ((row["branch"] == DBNull.Value || row["branch"] == null) ? " " : row["branch"]).ToString() : ""),
  5529. OfferQualification = Convert.ToInt32(((row["offerqualified"] == DBNull.Value || row["offerqualified"] == null) ? " " : row["offerqualified"]).ToString()),
  5530. }).ToList();
  5531.  
  5532. return AgentLeadBranchList.OrderByDescending(x => x.NoOfLeads).ToList();
  5533. }
  5534. catch (Exception ex)
  5535. {
  5536. Helper.ErrorLog(ex.InnerException, "Qapplication", "GetafflicateLeadReportBanch_Update", ex.Message);
  5537. return AgentLeadBranchList;
  5538. }
  5539.  
  5540. }
  5541.  
  5542. /// <summary>
  5543. /// Method for product Report
  5544. /// </summary>
  5545. /// <param name="Conditions">Dynamic query for product report</param>
  5546. /// <param name="accountid">Account Id</param>
  5547. /// <param name="fromdate">From Date</param>
  5548. /// <param name="todate">To Date</param>
  5549. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5550. /// <param name="dateFilter">Dynamic Date filter condition</param>
  5551. /// <returns>Roduct List</returns>
  5552. public List<ProductReport> GetProductReportByIds(string Conditions, int accountid, DateTime? fromdate, DateTime? todate, string Conditions1 = "")
  5553. {
  5554. List<ProductReport> ProductReportList = new List<ProductReport>();
  5555. try
  5556. {
  5557. SqlParameter[] Param = new SqlParameter[2];
  5558. Param[0] = new SqlParameter("@conditions", Conditions);
  5559. Param[1] = new SqlParameter("@conditions1", Conditions1);
  5560. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetProductReport_Update", Param);
  5561. ProductReportList = (from DataRow row in dt.Rows
  5562. select new ProductReport
  5563. {
  5564. Product = GetFinanceTableData(row["type"].ToString()).type,
  5565. NoOfLeads = Convert.ToInt32(row["TotalLeads"]),
  5566. InProgress = GetFinanceTableData(row["type"].ToString(), (int)MortgageStatus.NewLead, fromdate, todate),
  5567. Complete = GetFinanceTableData(row["type"].ToString(), (int)MortgageStatus.Completed, fromdate, todate),
  5568. TotalCommission = row["TotalCommission"].ToString() != "" ? Math.Round(Convert.ToDecimal(row["TotalCommission"].ToString()), 2).ToString() : "0",
  5569. AvgCommission = row["AvgCommission"].ToString() != "" ? Math.Round(Convert.ToDecimal(row["AvgCommission"].ToString()), 2).ToString() : "0",
  5570. }).ToList();
  5571. return ProductReportList.OrderByDescending(x => x.AvgCommission).ToList();
  5572. }
  5573. catch (Exception ex)
  5574. {
  5575. Helper.ErrorLog(ex.InnerException, "Q_Application", "GetProductReport_Update", ex.Message);
  5576. }
  5577. return ProductReportList;
  5578. }
  5579.  
  5580. /// <summary>
  5581. /// Method for status Report in the Main Report
  5582. /// </summary>
  5583. /// <param name="Conditions">Dynamic query for product report</param>
  5584. /// <param name="accountid">Accout Id</param>
  5585. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5586. /// <param name="dateFilter">Dynamic Date filter condition</param>
  5587. /// <returns>Status report List</returns>
  5588. public List<statusReport> GetStatusReportByIds(string Conditions, int accountid, string Conditions1 = "")
  5589. {
  5590. List<statusReport> StatusReportList = new List<statusReport>();
  5591. try
  5592. {
  5593. SqlParameter[] Param = new SqlParameter[2];
  5594. Param[0] = new SqlParameter("@conditions", Conditions);
  5595. Param[1] = new SqlParameter("@conditions1", Conditions1);
  5596. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetStatusReport_Update", Param);
  5597. StatusReportList = (from DataRow row in dt.Rows
  5598. select new statusReport
  5599. {
  5600. status = row["StatusName"].ToString(),
  5601. NoOfLeads = Convert.ToInt32(row["TotalLeads"]),
  5602. }).ToList();
  5603.  
  5604. return StatusReportList;
  5605. }
  5606. catch (Exception ex)
  5607. {
  5608. Helper.ErrorLog(ex.InnerException, "QApplication", "GetStatusRepot", ex.Message);
  5609. return StatusReportList;
  5610. }
  5611. }
  5612.  
  5613. /// <summary>
  5614. /// Method for date for main report chart
  5615. /// </summary>
  5616. /// <param name="Conditions">Dynamic query for main report</param>
  5617. /// <param name="accountid">Accoutnt Id</param>
  5618. /// <param name="Conditions1">Dynamic query for main report for link mortgage to insurance functionality</param>
  5619. /// <returns>MAin Report Chart Data</returns>
  5620. public List<ReportDataPoint> GetLeadReportByIds(string Conditions, int accountid, string Conditions1 = "")
  5621. {
  5622. List<ReportDataPoint> ReportdataList = new List<ReportDataPoint>();
  5623. try
  5624. {
  5625. SqlParameter[] Param = new SqlParameter[2];
  5626. Param[0] = new SqlParameter("@conditions", Conditions);
  5627. Param[1] = new SqlParameter("@conditions1", Conditions1);
  5628.  
  5629. DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "LeadReport_Update", Param);
  5630.  
  5631. ReportdataList = (from DataRow row in dt.Rows
  5632. select new ReportDataPoint
  5633. {
  5634. Dated = row.IsNull("created") ? DateTime.MinValue : DateTime.Parse(row["created"].ToString()),
  5635. Value = Convert.ToInt32(row["TotalCount"]),
  5636. }).ToList();
  5637.  
  5638.  
  5639. return ReportdataList;
  5640. }
  5641. catch (Exception ex)
  5642. {
  5643. Helper.ErrorLog(ex.InnerException, "QApplicaition", "GetLeadreport", ex.Message);
  5644. return ReportdataList;
  5645. }
  5646. }
  5647.  
  5648. #endregion [Main Lead Report Updated Procedures 2015-11-06]
  5649.  
  5650.  
  5651. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!