e0SwEtB2

1namespace Occfinance.Code
2{
3    public class Q_Reports
4    {
5        public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
6        System.Web.HttpContext con = HttpContext.Current;
7
8        /// <summary>
9        /// Set the jsTree data format
10        /// </summary>
11        /// <param name="hdnnamearray"></param>
12        /// <param name="hdnnamearrayparents"></param>
13        public void FormatJsTreeData(ref string hdnnamearray, ref string hdnnamearrayparents)
14        {
15            if (!string.IsNullOrEmpty(hdnnamearray))
16            {
17                var negIds = string.Empty;
18                var compbranchIds = string.Empty;
19
20                foreach (var ids in hdnnamearray.Split(','))
21                {
22                    var splitIds = ids.ToString().Split('_');
23                    if (splitIds.Length == 3)
24                    {
25
26                        negIds += splitIds[2].ToString() + ",";
27                        compbranchIds += splitIds[0].ToString() + "®" + splitIds[1].ToString() + ",";
28                    }
29
30                }
31                hdnnamearray = negIds.Substring(0, negIds.Length - 1);
32                hdnnamearrayparents = compbranchIds.Substring(0, compbranchIds.Length - 1);
33            }
34        }
35        /// <summary>
36        /// Set company, branch and negotiator ids
37        /// </summary>
38        /// <param name="hdnnamearray"></param>
39        /// <param name="_allNamesparents"></param>
40        /// <param name="CompanyIds"></param>
41        /// <param name="BranchIds"></param>
42        /// <param name="NegotiatorIds"></param>
43        public void SetCompanyBranchAndNegotiatorIds(string hdnnamearray, string[] _allNamesparents, out List<int> CompanyIds, out List<int> BranchIds, out List<int> NegotiatorIds)
44        {
45            // 2015-10-29 Negotiators, Branches and Companies Ids
46            CompanyIds = new List<int>();
47            BranchIds = new List<int>();
48            NegotiatorIds = new List<int>();
49
50            if (_allNamesparents.Length > 0)
51            {
52                foreach (var comp_branch in _allNamesparents)
53                {
54                    if (!string.IsNullOrEmpty(comp_branch))
55                    {
56                        CompanyIds.Add(Convert.ToInt32(comp_branch.Split('®')[0]));
57                        BranchIds.Add(Convert.ToInt32(comp_branch.Split('®')[1]));
58                    }
59                }
60
61                CompanyIds = CompanyIds.Distinct().ToList();
62                BranchIds = BranchIds.Distinct().ToList();
63                if (!string.IsNullOrEmpty(hdnnamearray))
64                    NegotiatorIds = hdnnamearray.Split(',').Select(Int32.Parse).Distinct().ToList();
65            }
66        }
67        /// <summary>
68        /// Get product type details
69        /// </summary>
70        /// <returns></returns>
71        public List<SelectListItem> GetProductTypeDetails()
72        {
73
74            var objTblfinancetype = (from ftype in ctx.tblfinancetypes
75                                     select new ProductType
76                                     {
77                                         ProductId = ftype.financetypeid,
78                                         ProductName = ftype.type
79                                     }).ToList();
80            var productitems = new List<SelectListItem>();
81            foreach (var x in objTblfinancetype)
82            {
83                productitems.Add(new SelectListItem { Value = x.ProductId.ToString(), Text = x.ProductName });
84            }
85            productitems.OrderBy(m => m.Value).ToList();
86            return productitems;
87        }
88        /// <summary>
89        /// Get all finance type
90        /// </summary>
91        /// <returns></returns>
92        public List<ProductType> GetAllFinanceType()
93        {
94            var objTblfinancetype = (from ftype in ctx.tblfinancetypes
95                                     select new ProductType
96                                     {
97                                         ProductId = ftype.financetypeid,
98                                         ProductName = ftype.type
99                                     }).ToList();
100
101
102            return objTblfinancetype;
103        }
104
105        /// <summary>
106        /// Find negotiation report by contactid
107        /// </summary>
108        /// <returns></returns>
109        public List<contactTbl> FindNegotiationReportByContactId()
110        {
111            List<contactTbl> negotiationReportList = new List<contactTbl>();
112            try
113            {
114                SqlParameter[] Param = new SqlParameter[0];
115                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetNegotiationReportList_Update", Param);
116                negotiationReportList = (from DataRow row in dt.Rows
117                                         select new contactTbl
118                                         {
119                                             negref = row["negref"].ToString()
120                                         }).ToList();
121                //  result = ctx.tblcontacts.Where(u => u.contactid == id).Select(u => new contactTbl() { negref=u.negref}).ToList();
122                return negotiationReportList;
123            }
124            catch (Exception)
125            {
126                return negotiationReportList;
127            }
128        }
129        /// <summary>
130        /// Getting dataset from the database for Negotiator Report 
131        /// </summary>
132        /// <param name="fromdate">From Date</param>
133        /// <param name="todate">To Date</param>
134        /// <param name="userid">User Id</param>
135        /// <param name="pageNo">Page no</param>
136        /// <param name="pageSize">Page size</param>
137        /// <param name="brokerId">Broker Id</param>
138        /// <param name="Negotiator">Negotiator</param>
139        /// <param name="brokers">Brokers</param>
140        /// <param name="Company">Companies</param>
141        /// <param name="Branches">Branches</param>
142        /// <param name="productTypeID">Product type</param>
143        /// <returns></returns>
144        public DataSet GetNegotiatorResult(DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
145        {
146            DataSet ds;
147            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
148            try
149            {
150                SqlParameter[] Param = new SqlParameter[10];
151                Param[0] = new SqlParameter("@CurrentUserId", userid);
152                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
153                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
154                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
155                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
156                Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
157                Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
158                Param[7] = new SqlParameter("@PageNo", pageNo);
159                Param[8] = new SqlParameter("@PageSize", pageSize);
160                Param[9] = new SqlParameter("@productTypeID", productTypeID);
161                ds = SqlHelper.ExecuteDataset("GetNegotiatorReport", Param);
162                return ds;
163            }
164            catch (Exception ex)
165            {
166                Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
167                return ds = new DataSet();
168            }
169        }
170        /// <summary>
171        /// Getting dataset for Negotiator Report with page no
172        /// </summary>
173        /// <param name="fromdate">from date</param>
174        /// <param name="todate">to date</param>
175        /// <param name="userid">user Id</param>
176        /// <param name="pageNo">page no</param>
177        /// <param name="pageSize">page size</param>
178        /// <param name="brokerId">broker Id</param>
179        /// <param name="Negotiator">Negotiator</param>
180        /// <param name="brokers">Brokers</param>
181        /// <param name="Company">Company</param>
182        /// <param name="Branches">Branches</param>
183        /// <returns></returns>
184        public ReportsDataModel GetNegotiatorDataResult(DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
185        {
186            ReportsDataModel dataResult = new ReportsDataModel();
187            dataResult.Negotiation = new List<Negotiation>();
188            dataResult.NegotiationNotes = new List<NegotiationNotes>();
189            try
190            {
191                SqlParameter[] Param = new SqlParameter[9];
192                Param[0] = new SqlParameter("@CurrentUserId", userid);
193                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
194                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
195                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
196                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
197                Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
198                Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
199                Param[7] = new SqlParameter("@PageNo", pageNo);
200                Param[8] = new SqlParameter("@PageSize", pageSize);
201                SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "GetNegotiatorReport", Param);
202                ctx.Database.Initialize(force: false);
203                ctx.Database.Connection.Open();
204                dataResult.Negotiation = ((IObjectContextAdapter)ctx).ObjectContext.Translate<Negotiation>(reader).ToList();
205                reader.NextResult();
206                dataResult.NegotiationNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
207                ctx.Database.Connection.Close();
208                if (!reader.IsClosed)
209                {
210                    reader.Close();
211                }
212            }
213            catch (Exception ex)
214            {
215                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorDataResult", ex.Message);
216            }
217            return dataResult;
218        }
219        /// <summary>
220        /// Getting the dataset for Notes Data
221        /// </summary>
222        /// <param name="userId">User Id</param>
223        /// <param name="pageNo">Page No</param>
224        /// <param name="pageSize">Page Size</param>
225        /// <param name="fromDate">FRom Date</param>
226        /// <param name="toDate">To Date</param>
227        /// <param name="negotiators">Negotiators</param>
228        /// <param name="company">Companies</param>
229        /// <param name="branches">branches</param>
230        /// <param name="brokers">Brokers</param>
231        /// <param name="productTypeID">Product type</param>
232        /// <returns></returns>
233        public ReadNotes GetNotesData(int userId, int pageNo, int pageSize, DateTime fromDate, DateTime toDate, string negotiators = "", string company = "", string branches = "", string brokers = "", int productTypeID = 0)
234        {
235            var reportsDN = new ReadNotes();
236            try
237            {
238                DataTable notes = new DataTable();
239                SqlParameter[] Param = new SqlParameter[10];
240                Param[0] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(negotiators) ? null : negotiators.Trim()));
241                Param[1] = new SqlParameter("@Company", (string.IsNullOrEmpty(company) ? null : company.Trim()));
242                Param[2] = new SqlParameter("@Branches", (string.IsNullOrEmpty(branches) ? null : branches.Trim()));
243                Param[3] = new SqlParameter("@BrokerIds", (string.IsNullOrEmpty(brokers) ? null : brokers));
244                Param[4] = new SqlParameter("@FromDate", fromDate.ToString("dd/MMM/yyyy"));
245                Param[5] = new SqlParameter("@ToDate", toDate.ToString("dd/MMM/yyyy"));
246                Param[6] = new SqlParameter("@CurrentUserId", userId);
247                Param[7] = new SqlParameter("@PageNo", pageNo);
248                Param[8] = new SqlParameter("@PageSize", pageSize);
249                Param[9] = new SqlParameter("@productTypeID", productTypeID);
250                SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "Usp_GetNotesData", Param);
251                ctx.Database.Initialize(force: false);
252                ctx.Database.Connection.Open();
253                reportsDN.AdvisorNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
254                reader.NextResult();
255                reportsDN.AgentNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
256                ctx.Database.Connection.Close();
257                if (!reader.IsClosed)
258                {
259                    reader.Close();
260                }
261            }
262            catch (Exception ex)
263            {
264                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNotesData", ex.Message);
265            }
266            return reportsDN; ;
267        }
268        //added on 2015-02-03
269        /// <summary>
270        /// Getting Weekly created notes data
271        /// </summary>
272        /// <param name="fromdate">From Date</param>
273        /// <param name="todate">To Date</param>
274        /// <param name="userid">User Id</param>
275        /// <param name="brokerId">Broker Id</param>
276        /// <param name="Negotiator">Negotiator</param>
277        /// <param name="brokers">Brokers</param>
278        /// <param name="Company">Company</param>
279        /// <param name="Branches">Branches</param>
280        /// <param name="productTypeID">Product Type</param>
281        /// <returns></returns>
282        public DataSet GetNegotiatorWeeklyCreatedNotes(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
283        {
284            DataSet ds;
285            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
286            try
287            {
288                SqlParameter[] Param = new SqlParameter[8];
289                Param[0] = new SqlParameter("@CurrentUserId", userid);
290                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
291                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
292                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
293                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
294                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
295                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
296                Param[7] = new SqlParameter("@productTypeID", productTypeID);
297                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteCreated", Param);
298                return ds;
299            }
300            catch (Exception ex)
301            {
302                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyCreatedNotes", ex.Message);
303                ds = new DataSet();
304                return ds;
305            }
306        }
307
308        //added on 2015-02-03
309        /// <summary>
310        /// Getting weekly updated notes
311        /// </summary>
312        /// <param name="fromdate">From Date</param>
313        /// <param name="todate">To date</param>
314        /// <param name="userid">User Id</param>
315        /// <param name="brokerId">Broker Id</param>
316        /// <param name="Negotiator">Negotiator</param>
317        /// <param name="brokers">Brokers</param>
318        /// <param name="Company">Company</param>
319        /// <param name="Branches">Branches</param>
320        /// <param name="productTypeID">Product Type</param>
321        /// <returns></returns>
322        public DataSet GetNegotiatorWeeklyUpdatedNotes(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
323        {
324            DataSet ds;
325            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
326            try
327            {
328                SqlParameter[] Param = new SqlParameter[8];
329                Param[0] = new SqlParameter("@CurrentUserId", userid);
330                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
331                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
332                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
333                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
334                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
335                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
336                Param[7] = new SqlParameter("@productTypeID", productTypeID);
337                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteUpdated", Param);
338                return ds;
339            }
340            catch (Exception ex)
341            {
342                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyUpdatedNotes", ex.Message);
343                ds = new DataSet();
344                return ds;
345            }
346        }
347
348        /// <summary>
349        /// Getting negotiator data for PDF
350        /// </summary>
351        /// <param name="fromdate">From Date</param>
352        /// <param name="todate">To Date</param>
353        /// <param name="userid">User Id</param>
354        /// <param name="brokerId">Broker Id</param>
355        /// <param name="Negotiator">Negotiator</param>
356        /// <param name="brokers">Brokers</param>
357        /// <param name="Company">Company</param>
358        /// <param name="Branches">Branches</param>
359        /// <param name="productTypeID">Product Type</param>
360        /// <returns></returns>
361        public DataSet GetNegotiator(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
362        {
363            DataSet ds;
364            try
365            {
366                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
367                SqlParameter[] Param = new SqlParameter[8];
368                Param[0] = new SqlParameter("@CurrentUserId", userid);
369
370                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
371                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
372                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
373                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
374                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
375                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
376                Param[7] = new SqlParameter("@productTypeID", productTypeID);
377                ds = SqlHelper.ExecuteDataset("GetNegotiatorReport", Param);
378            }
379            catch (Exception ex)
380            {
381                ds = new DataSet();
382                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiator", ex.Message);
383            }
384            return ds;
385        }
386        /********************************************************************
387         * Code Updatd : 24/01/2015
388         * 
389         * Description : Method added for weekly report helper for GetNegotiator
390         * 
391         * Optimized code block added 
392         * ******************************************************************/
393        /// <summary>
394        /// Getting negotiator weekly report
395        /// </summary>
396        /// <param name="fromdate">From date</param>
397        /// <param name="todate">To date</param>
398        /// <param name="userid">User Id</param>
399        /// <param name="brokerId">Broker Id</param>
400        /// <param name="Negotiator">Negotiator</param>
401        /// <param name="brokers">Brokers</param>
402        /// <param name="Company">Companies</param>
403        /// <param name="Branches">Branches</param>
404        /// <param name="productTypeID">Product Type</param>
405        /// <returns></returns>
406        public DataSet GetNegotiatorweekly(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
407        {
408            DataSet ds;
409            try
410            {
411                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
412                SqlParameter[] Param = new SqlParameter[8];
413                Param[0] = new SqlParameter("@CurrentUserId", userid);
414                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
415                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
416                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
417                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
418                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
419                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
420                Param[7] = new SqlParameter("@productTypeID", productTypeID);
421                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeekly", Param);
422            }
423            catch (Exception ex)
424            {
425                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorweekly", ex.Message);
426                ds = new DataSet();
427
428            }
429            return ds;
430        }
431        /// <summary>
432        /// Getting Adviser Report Dataset
433        /// </summary>
434        /// <param name="fromdate">From date</param>
435        /// <param name="todate">To Date</param>
436        /// <param name="userid">User Id</param>
437        /// <param name="brokerId">Broker Id</param>
438        /// <param name="type">Type Id</param>
439        /// <param name="Negotiator">Negotiator</param>
440        /// <param name="brokers">Brokers</param>
441        /// <param name="Company">Company</param>
442        /// <param name="Branches">branches</param>
443        /// <returns></returns>
444        public DataSet GetAdvisorReport(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, int type = 1, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
445        {
446            DataSet ds;
447            try
448            {
449                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
450                SqlParameter[] Param = new SqlParameter[8];
451                Param[0] = new SqlParameter("@CurrentUserId", userid);
452                if (!string.IsNullOrEmpty(Negotiator))
453                {
454                    Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
455                    Param[6] = new SqlParameter("@Company", Company.Trim());
456                    Param[7] = new SqlParameter("@Branches", Branches.Trim());
457                }
458                Param[2] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
459                Param[3] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
460                if (!string.IsNullOrWhiteSpace(brokers))
461                    Param[4] = new SqlParameter("@BrokerId", brokers);
462                Param[5] = new SqlParameter("@Type", type);
463                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAdvisorReport", Param);
464            }
465            catch (Exception ex)
466            {
467                ds = new DataSet();
468                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAdviserReport", ex.Message);
469            }
470            return ds;
471        }
472        /// <summary>
473        /// Getting Branch Report
474        /// </summary>
475        /// <param name="fromdate">from date</param>
476        /// <param name="todate">to date</param>
477        /// <param name="Branch">Brnach Name</param>
478        /// <returns></returns>
479        public DataSet GetBranchReport(DateTime fromdate, DateTime todate, string Branch = "")
480        {
481            DataSet ds;
482            try
483            {
484                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
485                SqlParameter[] Param = new SqlParameter[3];
486                Param[0] = new SqlParameter("@Branch", Branch.Trim());
487                Param[1] = new SqlParameter("@FromDate", fromdate);
488                Param[2] = new SqlParameter("@ToDate", todate);
489                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBranchReport", Param);
490            }
491            catch (Exception ex)
492            {
493                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetBranchReport", ex.Message);
494                ds = new DataSet();
495            }
496            return ds;
497        }
498
499        //modified on 23-12-2014
500        /// <summary>
501        /// Get dataset for Mortgage case written report 
502        /// </summary>
503        /// <param name="type">type id</param>
504        /// <param name="fromdate">from date</param>
505        /// <param name="todate">to date</param>
506        /// <param name="userid">user id</param>
507        /// <param name="brokerId">Broker Id</param>
508        /// <param name="Negotiator">Negotiator</param>
509        /// <param name="brokers">brokers</param>
510        /// <param name="Company">company</param>
511        /// <param name="Branches">Branches</param>
512        /// <param name="productTypeID">ProductTypeId</param>
513        /// <returns></returns>
514        public DataSet GetMortgageCaseWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
515        {
516            DataSet ds;
517            try
518            {
519                SqlParameter[] Param = new SqlParameter[9];
520                Param[0] = new SqlParameter("@CurrentUserId", userid);
521                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
522                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
523                if (!string.IsNullOrEmpty(Negotiator))
524                {
525                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
526                    Param[4] = new SqlParameter("@Company", Company.Trim());
527                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
528                }
529                if (!string.IsNullOrWhiteSpace(brokers))
530                {
531                    Param[6] = new SqlParameter("@BrokerId", brokers);
532                }
533                if (!string.IsNullOrWhiteSpace(type))
534                {
535                    Param[7] = new SqlParameter("@MortgageType", type);
536                }
537
538                Param[8] = new SqlParameter("@productTypeID", productTypeID);
539
540                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten", Param);
541            }
542            catch (Exception ex)
543            {
544                ds = new DataSet();
545                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCaseWrittenReport", ex.Message);
546            }
547            return ds;
548        }
549        /// <summary>
550        /// Getting dataset for overall conversion report for Introducer Report
551        /// </summary>
552        /// <param name="totalLeads">Total Leads output parameter</param>
553        /// <param name="totalApps">Total Apps Output parameter</param>
554        /// <param name="fromdate">from date</param>
555        /// <param name="todate">to date</param>
556        /// <param name="userid">user Id</param>
557        /// <param name="brokerId">broker Id</param>
558        /// <param name="Negotiator">Negotiator</param>
559        /// <param name="brokers">Brokers</param>
560        /// <param name="Company">Companies</param>
561        /// <param name="Branches">Branches</param>
562        /// <param name="productTypeID">Product Type Id</param>
563        public void GetOverAllConversionReport(out int totalLeads, out int totalApps, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
564        {
565            DataSet ds;
566            totalLeads = 0;
567            totalApps = 0;
568            try
569            {
570                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
571                SqlParameter[] Param = new SqlParameter[8];
572                Param[0] = new SqlParameter("@CurrentUserId", userid);
573                if (!string.IsNullOrEmpty(Negotiator))
574                {
575                    Param[1] = new SqlParameter("@Negotiator", "'" + Negotiator.TrimEnd(',') + "'");
576                    Param[2] = new SqlParameter("@Company", "'" + Company.TrimEnd(',') + "'");
577                    Param[3] = new SqlParameter("@Branches", "'" + Branches.TrimEnd(',') + "'");
578                }
579                if (!string.IsNullOrWhiteSpace(brokers))
580                {
581                    Param[4] = new SqlParameter("@BrokerId", brokers);
582                }
583                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
584                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
585                Param[7] = new SqlParameter("@productTypeID", productTypeID);
586                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetOverAllConversionForIntroducer", Param);
587                if (ds.Tables[0].Rows.Count > 0)
588                {
589                    totalLeads = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalCount"].ToString());
590                }
591                if (ds.Tables[1].Rows.Count > 0)
592                {
593                    totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"].ToString());
594                }
595            }
596            catch (Exception ex)
597            {
598                ds = new DataSet();
599                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetOverAllConversionReport", ex.Message);
600            }
601
602        }
603
604
605        #region [Get Insurance Written Report 2014-12-23]
606
607        /// <summary>
608        /// method for InsuranceCaseWritten
609        /// created on : 03/11/2014
610        ///modified on 23-12-2014
611        /// </summary>
612        /// <param name="type">type</param>
613        /// <param name="fromdate">from date</param>
614        /// <param name="todate">to date</param>
615        /// <param name="userid">user id</param>
616        /// <param name="brokerId">broker id</param>
617        /// <param name="Negotiator">negotiator</param>
618        /// <param name="brokers">brokers</param>
619        /// <param name="Company">company</param>
620        /// <param name="Branches">branches</param>
621        /// <param name="productTypeID">product type Id</param>
622        /// <returns></returns>
623        public DataSet GetInsuranceCaseWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
624        {
625            DataSet ds;
626            try
627            {
628                SqlParameter[] Param = new SqlParameter[9];
629                Param[0] = new SqlParameter("@CurrentUserId", userid);
630                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
631                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
632                if (!string.IsNullOrEmpty(Negotiator))
633                {
634                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
635                    Param[4] = new SqlParameter("@Company", Company.Trim());
636                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
637                }
638                if (!string.IsNullOrWhiteSpace(brokers))
639                {
640                    Param[6] = new SqlParameter("@BrokerId", brokers);
641                }
642                if (!string.IsNullOrWhiteSpace(type))
643                {
644                    Param[7] = new SqlParameter("@InsuranceType", type);
645                }
646                Param[8] = new SqlParameter("@productTypeID", productTypeID);
647
648                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCaseWritten", Param);
649            }
650            catch (Exception ex)
651            {
652                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceCaseWrittenReport", ex.Message);
653                ds = new DataSet();
654            }
655            return ds;
656        }
657
658        #endregion [Get Insurance Written Report 2014-12-23]
659
660        #region [Get Mortgage Completed Report 2015-03-30]
661        /// <summary>
662        /// Getting dataset for Mortgage completed report
663        /// </summary>
664        /// <param name="type">type</param>
665        /// <param name="fromdate">from date</param>
666        /// <param name="todate">to date</param>
667        /// <param name="userid">user id</param>
668        /// <param name="brokerId">broker id</param>
669        /// <param name="Negotiator">negotiator</param>
670        /// <param name="brokers">brokers</param>
671        /// <param name="Company">company</param>
672        /// <param name="Branches">branches</param>
673        /// <param name="productTypeID">product type id</param>
674        /// <returns></returns>
675        public DataSet GetMortgageCompletedReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
676        {
677            DataSet ds;
678            try
679            {
680                SqlParameter[] Param = new SqlParameter[9];
681                Param[0] = new SqlParameter("@CurrentUserId", userid);
682                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
683                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
684                if (!string.IsNullOrEmpty(Negotiator))
685                {
686                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
687                    Param[4] = new SqlParameter("@Company", Company.Trim());
688                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
689                }
690                if (!string.IsNullOrWhiteSpace(brokers))
691                {
692                    Param[6] = new SqlParameter("@BrokerId", brokers);
693                }
694                if (!string.IsNullOrWhiteSpace(type))
695                {
696                    Param[7] = new SqlParameter("@MortgageType", type);
697                }
698
699                Param[8] = new SqlParameter("@productTypeID", productTypeID);
700
701                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCompletedReport", Param);
702            }
703            catch (Exception ex)
704            {
705                ds = new DataSet();
706                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCompletedReport", ex.Message);
707            }
708            return ds;
709        }
710        #endregion [Get Mortgage Completed Report 2015-03-30]
711
712        #region [Get Insurance Completed Report 2015-03-30]
713        /// <summary>
714        /// Dataset for Insurance Completed Report
715        /// </summary>
716        /// <param name="type">type</param>
717        /// <param name="fromdate">from date</param>
718        /// <param name="todate">to date</param>
719        /// <param name="userid">user id</param>
720        /// <param name="brokerId">broker id</param>
721        /// <param name="Negotiator">negotiator</param>
722        /// <param name="brokers">brokers</param>
723        /// <param name="Company">company</param>
724        /// <param name="Branches">branches</param>
725        /// <param name="productTypeID">product type Id</param>
726        /// <returns></returns>
727        public DataSet GetInsuranceCompletedReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
728        {
729            DataSet ds;
730            try
731            {
732                SqlParameter[] Param = new SqlParameter[9];
733                Param[0] = new SqlParameter("@CurrentUserId", userid);
734                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
735                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
736                if (!string.IsNullOrEmpty(Negotiator))
737                {
738                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
739                    Param[4] = new SqlParameter("@Company", Company.Trim());
740                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
741                }
742                if (!string.IsNullOrWhiteSpace(brokers))
743                {
744                    Param[6] = new SqlParameter("@BrokerId", brokers);
745                }
746                if (!string.IsNullOrWhiteSpace(type))
747                {
748                    Param[7] = new SqlParameter("@InsuranceType", type);
749                }
750                Param[8] = new SqlParameter("@productTypeID", productTypeID);
751
752                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCompletedReport", Param);
753            }
754            catch (Exception ex)
755            {
756                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceCompletedReport", ex.Message);
757                ds = new DataSet();
758            }
759            return ds;
760        }
761
762        #endregion [Get Insurance Completed Report 2015-03-30]
763
764        // method for InsuranceCaseWritten
765        // created on : 12/11/2014
766        /// <summary>
767        /// Getting WOCC Report
768        /// </summary>
769        /// <param name="type">type</param>
770        /// <param name="fromdate">from date</param>
771        /// <param name="todate">to date</param>
772        /// <param name="userid">user id</param>
773        /// <param name="brokerId">broker id</param>
774        /// <param name="Negotiator">negotiator</param>
775        /// <param name="brokers">brokers</param>
776        /// <param name="Company">company</param>
777        /// <param name="Branches">branches</param>
778        /// <returns></returns>
779        public DataSet GetPipelineReviewReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
780        {
781            DataSet ds;
782            try
783            {
784                SqlParameter[] Param = new SqlParameter[8];
785                Param[0] = new SqlParameter("@CurrentUserId", userid);
786                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
787                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
788                if (!string.IsNullOrEmpty(Negotiator))
789                {
790                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
791                    Param[4] = new SqlParameter("@Company", Company.Trim());
792                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
793                }
794                if (!string.IsNullOrWhiteSpace(brokers))
795                {
796                    Param[6] = new SqlParameter("@BrokerId", brokers);
797                }
798                if (!string.IsNullOrWhiteSpace(type))
799                {
800                    Param[7] = new SqlParameter("@MortgageType", type);
801                }
802
803                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten", Param);
804            }
805            catch (Exception ex)
806            {
807                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetPipelineReviewReport", ex.Message);
808                ds = new DataSet();
809            }
810            return ds;
811        }
812
813        /// <summary>
814        /// Get new financial information report!
815        /// </summary>
816        /// <param name="type">type</param>
817        /// <param name="fromdate">from date</param>
818        /// <param name="todate">to date</param>
819        /// <param name="userid">user id</param>
820        /// <param name="brokerId">broker id</param>
821        /// <param name="Negotiator">negotiator</param>
822        /// <param name="brokers">brokers</param>
823        /// <param name="Company">company</param>
824        /// <param name="Branches">Branches</param>
825        /// <returns></returns>
826        //modified on 23-12-2014
827        public DataSet GetNewFinancialInformationReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int FinType = 0)
828        {
829            DataSet ds;
830            try
831            {
832                SqlParameter[] Param = new SqlParameter[8];
833                Param[0] = new SqlParameter("@CurrentUserId", userid);
834                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
835                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
836                if (!string.IsNullOrEmpty(Negotiator))
837                {
838                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
839                    Param[4] = new SqlParameter("@Company", Company.Trim());
840                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
841                }
842                if (!string.IsNullOrWhiteSpace(brokers))
843                {
844                    Param[6] = new SqlParameter("@BrokerId", brokers);
845                }
846                if (!string.IsNullOrWhiteSpace(type))
847                {
848                    Param[7] = new SqlParameter("@InsuranceType", type);
849                }
850                Param[7] = new SqlParameter("@FinanceType", FinType);
851
852                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetWOCCReport", Param);
853            }
854            catch (Exception ex)
855            {
856                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNewFinancialInformationReport", ex.Message);
857                ds = new DataSet();
858            }
859            return ds;
860        }
861        /// <summary>
862        /// Getting Dataset for Remortgage Report
863        /// </summary>
864        /// <param name="totalApps">Return total apps</param>
865        /// <param name="userid">User Id</param>
866        /// <param name="brokerId">Broker Id</param>
867        /// <param name="Year">Year filter</param>
868        /// <param name="Month">Month filter</param>
869        /// <returns></returns>
870        public DataSet GetReMortgageReport(out int totalApps, int userid, int brokerId = 0, int Year = 0, int Month = 0)
871        {
872            DataSet ds;
873            totalApps = 0;
874            try
875            {
876                SqlParameter[] Param = new SqlParameter[4];
877                Param[0] = new SqlParameter("@CurrentUserId", userid);
878                Param[1] = new SqlParameter("@BrokerId", brokerId);
879                Param[2] = new SqlParameter("@Year", Year);
880                Param[3] = new SqlParameter("@Month", Month);
881
882                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetReMortgageReport_Update", Param);
883                if (ds.Tables[1].Rows.Count > 0)
884                {
885                    totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalApps"].ToString());
886                }
887            }
888            catch (Exception ex)
889            {
890                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetRemortgageReport", ex.Message);
891                ds = new DataSet();
892            }
893            return ds;
894        }
895
896        /// <summary>
897        /// Used to return reports data model for mortgage case written view
898        /// </summary>
899        /// <param name="userId"></param>
900        /// <param name="loggedInUserId"></param>
901        /// <returns></returns>
902        public ReportsDataModel GetMortgageCaseWrittenDetails(int userId, int loggedInUserId)
903        {
904            var reportsDM = new ReportsDataModel();
905            try
906            {
907                reportsDM.MortgageTreeModel = new MortgageTreeModel();
908                reportsDM.MortgageTreeModel.IsSubmit = 0;
909                reportsDM.MortgageTreeModel.Purchase = "Residential Purchase";
910                var qSettings = new Q_settings();
911                var clients = qSettings.FindBrokerForLoggedInUserWithUserId(userId, loggedInUserId);
912                if (clients == null)
913                {
914                    reportsDM.MortgageTreeModel.BItems = new List<Clients>();
915                }
916                reportsDM.MortgageTreeModel.BItems = clients;
917                var qClients = new Q_Client();
918                reportsDM.MortgageTreeModel.ClientsOrderBy = qClients.FindClients(userId, loggedInUserId).OrderBy(c => c.companyname).ToList();
919                reportsDM.MortgageTreeModel.BrokersWithTeams = qSettings.GetBrokersWithTeams(loggedInUserId);
920                reportsDM.MortgageTreeModel.ProductTypeList = GetAllFinanceType();
921            }
922            catch (Exception ex)
923            {
924                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMortgageCaseWrittenDetails", ex.Message);
925            }
926            return reportsDM;
927        }
928
929        /// <summary>
930        /// Used to return contact list by account number.
931        /// </summary>
932        /// <param name="acccountNumber"></param>
933        /// <returns></returns>
934        public List<tblcontact> GetContactByAccount(int acccountNumber)
935        {
936            var qNewLeads = new Q_NewLead();
937            return qNewLeads.FindContactsForAccount(acccountNumber);
938        }
939
940        /// <summary>
941        /// Get all companies details
942        /// </summary>
943        /// <returns></returns>
944        public List<clsCompanyDetails> GetAllCompaniesDetails()
945        {
946            List<clsCompanyDetails> lstCompanyDetails;
947            try
948            {
949                DataTable dtCompanyDetails = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetAllCompaniesInfo_Update");
950                lstCompanyDetails = new List<clsCompanyDetails>(dtCompanyDetails.Rows.Count);
951                foreach (DataRow row in dtCompanyDetails.Rows)
952                {
953                    var values = row.ItemArray;
954                    var category = new clsCompanyDetails()
955                    {
956                        AccountId = Convert.ToInt32(values[0]),
957                        CompanyName = Convert.ToString(values[1]),
958                        Branch = Convert.ToString(values[2]),
959                        BranchId = Convert.ToInt32(values[3]),
960                        Negotiator = Convert.ToString(values[4]),
961                        NegotiatorId = Convert.ToInt32(values[5])
962                    };
963                    lstCompanyDetails.Add(category);
964                }
965            }
966            catch (Exception)
967            {
968                throw;
969            }
970            return lstCompanyDetails;
971        }
972        #region Total written Report
973        /// <summary>
974        /// Returning dataset for total written report
975        /// </summary>
976        /// <param name="type">Type</param>
977        /// <param name="fromdate">From Date</param>
978        /// <param name="todate">To date</param>
979        /// <param name="userid">User Id</param>
980        /// <param name="brokerId">Broker Id</param>
981        /// <param name="Negotiator">Negotiator</param>
982        /// <param name="brokers">Brokers</param>
983        /// <param name="Company">Company</param>
984        /// <param name="Branches">branches</param>
985        /// <param name="productTypeID">ProductType Id</param>
986        /// <returns></returns>
987        public DataSet GetTotalWrittenReport(string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
988        {
989            DataSet ds;
990            try
991            {
992                SqlParameter[] Param = new SqlParameter[7];
993                Param[0] = new SqlParameter("@CurrentUserId", userid);
994                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
995                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
996                if (!string.IsNullOrEmpty(Negotiator))
997                {
998                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
999                    Param[4] = new SqlParameter("@Company", Company.Trim());
1000                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1001                }
1002                if (!string.IsNullOrWhiteSpace(brokers))
1003                {
1004                    Param[6] = new SqlParameter("@BrokerId", brokers);
1005                }
1006                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWritten", Param);
1007            }
1008            catch (Exception ex)
1009            {
1010                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetTotalWrittenReport", ex.Message);
1011                ds = new DataSet();
1012            }
1013            return ds;
1014        }
1015        #endregion
1016
1017        #region [Optimized Code of Mortgage Case Written Report 2015-01-22]
1018
1019        /********************************************************
1020         * Code Updated on :  23/01/2015
1021         * Report related methods taked from settings
1022         * 
1023         * 
1024         * *******************************************************/
1025
1026        /// <summary>
1027        /// Get all broker details
1028        /// </summary>
1029        /// <param name="accid"></param>
1030        /// <param name="userId"></param>
1031        /// <returns></returns>
1032        public List<Clients> GetAllBrokerDetails(int accid, int userId)
1033        {
1034            List<Clients> lstBrokers = new List<Clients>();
1035            try
1036            {
1037                int userType = 0;
1038                var Id = GetUserTypeAndId(userId, out userType);
1039
1040                SqlParameter[] Param = new SqlParameter[2];
1041                Param[0] = new SqlParameter("@UserType", userType);
1042                Param[1] = new SqlParameter("@ID", Id);
1043                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "FindBrokerForLoggedInUserWithUserId", Param);
1044
1045                lstBrokers = (from DataRow row in dt.Rows
1046                              select new Clients
1047                              {
1048                                  clientid = Convert.ToInt32(row["clientid"]),
1049                                  parent = Convert.ToInt32(row["parent"]),
1050                                  companyname = Convert.ToString(row["companyname"]),
1051                                  TeamName = Convert.ToString(row["TeamName"]),
1052                                  TeamId = Convert.ToInt32(row["TeamId"]),
1053                                  affiliateid = Convert.ToString(row["affiliateid"]),
1054                                  telephone = Convert.ToString(row["telephone"]),
1055                                  email = Convert.ToString(row["email"]),
1056                                  contactname = Convert.ToString(row["contactname"]),
1057                                  address = Convert.ToInt32(row["address"]),
1058                                  userid = Convert.ToInt32(row["userid"]),
1059                                  AdminId = Convert.ToInt32(row["AdminId"]),
1060                                  countLeads = Convert.ToInt32(row["countLeads"])
1061                              }).ToList();
1062            }
1063            catch (Exception ex)
1064            {
1065                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAllBrokerDetails", ex.Message);
1066                throw;
1067            }
1068            return lstBrokers;
1069        }
1070
1071        /// <summary>
1072        /// Get broker details with team
1073        /// </summary>
1074        /// <param name="userId"></param>
1075        /// <returns></returns>
1076        public List<Clients> GetBrokersWithTeams(int userId)
1077        {
1078            List<Clients> lstBrokerTeam = new List<Clients>();
1079            int userType = 0;
1080            var Id = GetUserTypeAndId(userId, out userType);
1081            try
1082            {
1083                SqlParameter[] Param = new SqlParameter[2];
1084                Param[0] = new SqlParameter("@UserType", userType);
1085                Param[1] = new SqlParameter("@ID", Id);
1086                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetBrokersWithTeams", Param);
1087                lstBrokerTeam = (from DataRow row in dt.Rows
1088                                 select new Clients
1089                                 {
1090                                     clientid = Convert.ToInt32(row["clientid"]),
1091                                     parent = Convert.ToInt32(row["parent"]),
1092                                     companyname = Convert.ToString(row["companyname"]),
1093                                     TeamName = Convert.ToString(row["TeamName"]),
1094                                     TeamId = Convert.ToInt32(row["TeamId"]),
1095                                     affiliateid = Convert.ToString(row["affiliateid"]),
1096                                     telephone = Convert.ToString(row["telephone"]),
1097                                     email = Convert.ToString(row["email"]),
1098                                     contactname = Convert.ToString(row["contactname"]),
1099                                     address = Convert.ToInt32(row["address"]),
1100                                     userid = Convert.ToInt32(row["userid"])
1101                                 }).ToList();
1102            }
1103            catch (Exception ex)
1104            {
1105                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetBrokersWithTeams", ex.Message);
1106                throw;
1107            }
1108            return lstBrokerTeam;
1109        }
1110
1111        /// <summary>
1112        /// Get user type and id accordingly.
1113        /// </summary>
1114        /// <param name="userId"></param>
1115        /// <param name="userType"></param>
1116        /// <returns></returns>
1117        private object GetUserTypeAndId(int userId, out int userType)
1118        {
1119            int Id = 0;
1120            try
1121            {
1122                SqlParameter[] Param = new SqlParameter[1] { new SqlParameter("@UserId", userId) };
1123                DataTable dt = SqlHelper.ExecuteDatatable(CommandType.StoredProcedure, "GetUserType", Param);
1124                Id = Convert.ToInt32(dt.Rows[0]["Id"]);
1125                userType = Convert.ToInt32(dt.Rows[0]["userType"]);
1126            }
1127            catch (Exception ex)
1128            {
1129                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetUserTypeAndId", ex.Message);
1130                throw;
1131            }
1132            return Id;
1133        }
1134
1135        #endregion [Optimized Code of Mortgage Case Written Report 2015-01-22]
1136        //Insurance Referral report
1137        /// <summary>
1138        /// Insurance Referral Report
1139        /// </summary>
1140        /// <param name="fromdate">From date</param>
1141        /// <param name="todate">To date</param>
1142        /// <param name="userid">User Id</param>
1143        /// <param name="brokerId">broker id</param>
1144        /// <param name="Negotiator">Negotiator</param>
1145        /// <param name="brokers">brokers</param>
1146        /// <param name="Company">Company</param>
1147        /// <param name="Branches">Branches</param>
1148        /// <param name="ProductTypeId">ProductType</param>
1149        /// <returns></returns>
1150        public DataSet GetInsuranceReferralResult(DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int ProductTypeId = 0)
1151        {
1152            DataSet ds;
1153            try
1154            {
1155                List<Insurancereferral> negotiationlist = new List<Models.Insurancereferral>();
1156                SqlParameter[] Param = new SqlParameter[8];
1157                Param[0] = new SqlParameter("@CurrentUserId", userid);
1158                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
1159                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
1160                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
1161                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
1162                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1163                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1164                Param[7] = new SqlParameter("@ProductTypeId", ProductTypeId);
1165                ds = SqlHelper.ExecuteDataset("GetInsuranceReferralReport", Param);
1166            }
1167            catch (Exception ex)
1168            {
1169                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetInsuranceReferralResult", ex.Message);
1170                ds = new DataSet();
1171            }
1172            return ds;
1173        }
1174
1175        #region [Client Referrals 2015-04-18]
1176
1177
1178        /// <summary>
1179        /// Getting count for referral for contact Id
1180        /// </summary>
1181        /// <param name="contactId">Contact Id</param>
1182        /// <returns></returns>
1183        public int ClientReferralsCount(string contactId)
1184        {
1185            var con = contactId.Split('_').ToList();
1186            int ContactId = 0;
1187            var contactids = new List<int>();
1188            if (con.Count() > 1)
1189            {
1190                ContactId = Convert.ToInt32(con[0]);
1191                contactids = ctx.tblcontacts.Where(x => x.parentNegId == ContactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1192            }
1193            else
1194            {
1195                ContactId = Convert.ToInt32(contactId);
1196                contactids = ctx.tblcontacts.Where(x => x.parentContactId == ContactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1197            }
1198
1199            //var contactids = ctx.tblcontacts.Where(x => x.parentContactId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1200            int count = 0;
1201            if (contactids.Count > 0)
1202            {
1203                count++;
1204                contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1205                while (contactids.Count > 0)
1206                {
1207                    contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1208                    count++;
1209                }
1210            }
1211
1212            return count;
1213
1214
1215        }
1216
1217        /// <summary>
1218        /// Getting Dataset for Client Referral Report
1219        /// </summary>
1220        /// <param name="ContactId">Contact Id</param>
1221        /// <param name="referralLevel">Referral Level</param>
1222        /// <param name="referralType">Referral Type</param>
1223        /// <returns></returns>
1224        public List<ClientReferralsInfo> ClientReferralInfo(string ContactId = "", int referralLevel = 0, int referralType = 0)
1225        {
1226            int contactId = 0;
1227            var contactids = new List<int>();
1228            var con = ContactId.Split('_').ToList();
1229            if (con.Count > 1)
1230            {
1231                contactId = Convert.ToInt32(con[0]);
1232                contactids = ctx.tblcontacts.Where(x => x.parentNegId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1233            }
1234            else
1235            {
1236                contactId = Convert.ToInt32(ContactId);
1237                contactids = ctx.tblcontacts.Where(x => x.parentContactId == contactId && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1238            }
1239
1240
1241
1242            int count = 0;
1243            List<ClientReferralsInfo> referralList = new List<ClientReferralsInfo>();
1244
1245            if (contactids.Count > 0)
1246            {
1247                count++;
1248                //if (referralLevel >= count || referralLevel == 0)
1249                //{
1250                foreach (var c in contactids)
1251                {
1252                    ClientReferralsInfo objClient = new ClientReferralsInfo();
1253                    objClient.ContactId = c.ToString();
1254                    objClient.ReferralLevel = count;
1255                    referralList.Add(objClient);
1256                }
1257                // }
1258                contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1259                while (contactids.Count > 0)
1260                {
1261                    count++;
1262                    //if (referralLevel >= count || referralLevel == 0)
1263                    //{
1264                    foreach (var c in contactids)
1265                    {
1266                        ClientReferralsInfo objClient = new ClientReferralsInfo();
1267                        objClient.ContactId = c.ToString();
1268                        objClient.ReferralLevel = count;
1269                        referralList.Add(objClient);
1270                    }
1271                    //}
1272                    contactids = ctx.tblcontacts.Where(x => contactids.Contains(x.parentContactId ?? 0) && x.account != 1582971 && x.account == 1583028).Select(x => x.contactid).ToList();
1273
1274                }
1275            }
1276
1277            referralList = referralList.OrderBy(x => x.ReferralLevel).ToList();
1278            //adding parent contact
1279            ClientReferralsInfo objClient1 = new ClientReferralsInfo();
1280            objClient1.ContactId = ContactId;
1281            objClient1.ReferralLevel = 0;
1282            referralList.Insert(0, objClient1);
1283            //adding parent contact
1284            foreach (var v in referralList)
1285            {
1286                var conn = v.ContactId.Split('_').ToList();
1287                if (conn.Count == 1)
1288                {
1289                    int cont = Convert.ToInt32(v.ContactId);
1290                    var WrittenCommission = (from f in ctx.tblfinances
1291                                             join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
1292                                             where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
1293                                             && (fs.status == 6 || fs.status == 13) && f.contact == cont
1294                                             select new
1295                                             {
1296                                                 f.commission
1297                                             }).Sum(x => x.commission);
1298
1299                    var WrittenMortgage = (from f in ctx.tblfinances
1300                                           join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
1301                                           where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
1302                                           && (fs.status == 6) && f.contact == cont
1303                                           select new
1304                                           {
1305                                               f.amount
1306                                           }).Sum(x => x.amount);
1307
1308                    var IssuedCommission = (from f in ctx.tblfinances
1309                                            join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
1310                                            where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
1311                                            && (fs.status == 8 || fs.status == 14) && f.contact == cont
1312                                            select new
1313                                            {
1314                                                f.commission
1315                                            }).Sum(x => x.commission);
1316
1317                    var Issuedmortgage = (from f in ctx.tblfinances
1318                                          join fs in ctx.tblfinance_statusdate on f.financeid equals fs.financeid
1319                                          where f.status != 34 && f.status != 35 && f.status != 36 && f.status != 37 && f.status != 38
1320                                          && (fs.status == 8) && f.contact == cont
1321                                          select new
1322                                          {
1323                                              f.amount
1324                                          }).Sum(x => x.amount);
1325                    var name = ctx.tblcontacts.Where(x => x.contactid == cont).Select(x => new { x.firstname, x.lastname, x.parentContactId }).FirstOrDefault();
1326                    var ReferrerName = ctx.tblcontacts.Where(x => x.contactid == name.parentContactId).Select(x => new { x.firstname, x.lastname }).FirstOrDefault();
1327                    v.WrittenCommission = WrittenCommission ?? 0;
1328                    v.WrittenMortgage = WrittenMortgage ?? 0;
1329                    v.IssuedCommission = IssuedCommission ?? 0;
1330                    v.IssuedMortgage = Issuedmortgage ?? 0;
1331                    v.ContactName = (name.firstname ?? "") + " " + (name.lastname ?? "");
1332
1333                    if (ReferrerName != null)
1334                        v.ReferrerName = (ReferrerName.firstname ?? "") + " " + (ReferrerName.lastname ?? "");
1335                    else
1336                        v.ReferrerName = "";
1337
1338                    string parent = "";
1339
1340                    parent = ctx.tblcontacts.Where(x => x.contactid == cont && v.ContactId != ContactId && x.parentContactId != 0).Select(x => x.parentContactId).FirstOrDefault().ToString();
1341
1342                    if (parent == "")
1343                    {
1344                        parent = ctx.tblcontacts.Where(x => x.contactid == cont && v.ContactId != ContactId && x.parentNegId != 0).Select(x => x.parentNegId).FirstOrDefault().ToString();
1345                        if (parent != string.Empty)
1346                            parent = parent + "_neg";
1347                        else
1348                            parent = null;
1349                    }
1350                    v.ParentContactId = parent;
1351                }
1352                else
1353                {
1354                    int neg = Convert.ToInt32(conn[0]);
1355                    v.ContactName = ctx.tblClientReferralNeg.Where(x => x.NegId == neg).FirstOrDefault().NegName;
1356
1357                }
1358
1359            }
1360            referralList = referralList.OrderBy(x => x.ReferralLevel).ThenByDescending(x => x.WrittenCommission).ToList();
1361
1362            //for new dropdown list of referral type
1363            int loop = referralType;
1364            if (referralType == 0)
1365            {
1366                loop = referralList.Select(x => x.ReferralLevel).Max();
1367            }
1368            foreach (var v in referralList)
1369            {
1370                int i = 1;
1371
1372                var cIds = referralList.Where(x => x.ParentContactId == v.ContactId).Select(x => x.ContactId).ToList();
1373
1374                var writMort = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
1375                var writComm = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
1376                var Issumort = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
1377                var IssuComm = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
1378
1379                while (i < loop)
1380                {
1381                    cIds = referralList.Where(x => cIds.Contains(x.ParentContactId)).Select(x => x.ContactId).ToList();
1382                    if (referralType == 0)
1383                    {
1384                        writMort += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
1385                        writComm += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
1386                        Issumort += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
1387                        IssuComm += referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
1388                    }
1389                    if (cIds.Count > 0)
1390                    {
1391                        i++;
1392                    }
1393                    else
1394                    {
1395                        break;
1396                    }
1397
1398                }
1399                if (referralType == 0)
1400                {
1401                    v.RefWrittenMortgage = writMort;
1402                    v.RefWrittenCommission = writComm;
1403                    v.RefIssuedMortgage = Issumort;
1404                    v.RefIssuedCommission = IssuComm;
1405                }
1406                else
1407                {
1408                    v.RefWrittenMortgage = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenMortgage).Sum();
1409                    v.RefWrittenCommission = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.WrittenCommission).Sum();
1410                    v.RefIssuedMortgage = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedMortgage).Sum();
1411                    v.RefIssuedCommission = referralList.Where(x => cIds.Contains(x.ContactId)).Select(x => x.IssuedCommission).Sum();
1412                }
1413            }
1414
1415
1416            if (referralLevel > 0)
1417            {
1418                referralList = referralList.Where(x => x.ReferralLevel <= referralLevel).ToList();
1419            }
1420
1421
1422            return referralList;
1423        }
1424
1425
1426        #endregion [Client Referrals 2015-04-18]
1427
1428        #region[Business report]
1429        /// <summary>
1430        /// Getting Business report
1431        /// </summary>
1432        /// <param name="fromdate">From date</param>
1433        /// <param name="todate">To date</param>
1434        /// <param name="type">type</param>
1435        /// <param name="userid">user id</param>
1436        /// <param name="brokerId">broker id</param>
1437        /// <param name="Negotiator">negotiator</param>
1438        /// <param name="brokers">brokers</param>
1439        /// <param name="Company">company</param>
1440        /// <param name="Branches">Branches</param>
1441        /// <param name="productTypeID">ProductTypeId</param>
1442        /// <returns></returns>
1443        public DataSet GetBusinessReport(DateTime fromdate, DateTime todate, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1444        {
1445            DataSet ds;
1446            try
1447            {
1448                SqlParameter[] Param = new SqlParameter[8];
1449                Param[0] = new SqlParameter("@CurrentUserId", userid);
1450                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1451                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1452                if (!string.IsNullOrEmpty(Negotiator))
1453                {
1454                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1455                    Param[4] = new SqlParameter("@Company", Company.Trim());
1456                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1457                }
1458                if (!string.IsNullOrWhiteSpace(brokers))
1459                {
1460                    Param[6] = new SqlParameter("@BrokerId", brokers);
1461                }
1462
1463                Param[7] = new SqlParameter("@productTypeID", productTypeID);
1464
1465                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBusinessReport", Param);
1466            }
1467            catch (Exception ex)
1468            {
1469                Helper.ErrorLog(ex.InnerException, "Q_report", "GetBusinessReport", ex.Message);
1470                ds = new DataSet();
1471            }
1472            return ds;
1473        }
1474        #endregion
1475
1476        #region[Most Valuable referrer report]
1477        /// <summary>
1478        /// Getting most valuable referral Report
1479        /// </summary>
1480        /// <param name="type">type</param>
1481        /// <param name="userid">user id</param>
1482        /// <param name="brokerId">broker id</param>
1483        /// <param name="Negotiator">Negotiator</param>
1484        /// <param name="brokers">Brokers</param>
1485        /// <param name="Company">Company</param>
1486        /// <param name="Branches">Branches</param>
1487        /// <param name="productTypeID">Product type Id</param>
1488        /// <returns></returns>
1489        public DataSet GetMostValuableReport(string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1490        {
1491            DataSet ds;
1492            try
1493            {
1494                SqlParameter[] Param = new SqlParameter[5];
1495                Param[0] = new SqlParameter("@CurrentUserId", userid);
1496
1497                if (!string.IsNullOrEmpty(Negotiator))
1498                {
1499                    Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
1500                    Param[2] = new SqlParameter("@Company", Company.Trim());
1501                    Param[3] = new SqlParameter("@Branches", Branches.Trim());
1502                }
1503                if (!string.IsNullOrWhiteSpace(brokers))
1504                {
1505                    Param[4] = new SqlParameter("@BrokerId", brokers);
1506                }
1507                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMostValuableReferrersreport", Param);
1508            }
1509            catch (Exception ex)
1510            {
1511                ds = new DataSet();
1512                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetMostValuableReport", ex.Message);
1513            }
1514            return ds;
1515        }
1516        #endregion
1517
1518        #region [Updated Code of Reports Data Access Methods 2015-10-21]
1519
1520        #region [Adviser Report]
1521
1522        /// <summary>
1523        /// Updated data access code of adviser report. It fetched data on the basis of negotiator, branch and company ids. Updated on 2015-10-21
1524        /// </summary>
1525        /// <param name="NegotiatorIds">Contains negotiator's ids</param>
1526        /// <param name="BranchIds">Contain branch's ids</param>
1527        /// <param name="CompanyIds">Contain company's ids</param>
1528        /// <param name="fromdate">From date</param>
1529        /// <param name="todate">To date</param>
1530        /// <param name="userid">User id</param>
1531        /// <param name="brokerId">Broker ids</param>
1532        /// <param name="type">Type</param>
1533        /// <param name="Negotiator">Negotiators</param>
1534        /// <param name="brokers">Brokers</param>
1535        /// <param name="Company">Companies</param>
1536        /// <param name="Branches">Branches</param>
1537        /// <returns></returns>
1538        public DataSet GetAdvisorReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, int type = 1, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
1539        {
1540            DataSet ds = new DataSet();
1541            try
1542            {
1543                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
1544                SqlParameter[] Param = new SqlParameter[11];
1545                Param[0] = new SqlParameter("@CurrentUserId", userid);
1546                if (!string.IsNullOrEmpty(Negotiator))
1547                {
1548                    Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
1549                    Param[6] = new SqlParameter("@Company", Company.Trim());
1550                    Param[7] = new SqlParameter("@Branches", Branches.Trim());
1551
1552                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1553                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1554                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1555
1556                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1557                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
1558
1559                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1560                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
1561                }
1562                Param[2] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1563                Param[3] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1564                if (!string.IsNullOrWhiteSpace(brokers))
1565                    Param[4] = new SqlParameter("@BrokerId", brokers);
1566
1567                Param[5] = new SqlParameter("@Type", type);
1568
1569
1570                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAdvisorReport_Updated", Param);
1571            }
1572            catch (Exception ex)
1573            {
1574                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetAdvisorReportByIds", ex.Message);
1575            }
1576            return ds;
1577        }
1578
1579        #endregion [Adviser Report]
1580
1581        #region [Mortgage Written Report]
1582
1583        /// <summary>
1584        /// Get mortgage case written report by ids.
1585        /// </summary>
1586        /// <param name="NegotiatorIds"></param>
1587        /// <param name="BranchIds"></param>
1588        /// <param name="CompanyIds"></param>
1589        /// <param name="type"></param>
1590        /// <param name="fromdate"></param>
1591        /// <param name="todate"></param>
1592        /// <param name="userid"></param>
1593        /// <param name="brokerId"></param>
1594        /// <param name="Negotiator"></param>
1595        /// <param name="brokers"></param>
1596        /// <param name="Company"></param>
1597        /// <param name="Branches"></param>
1598        /// <param name="productTypeID"></param>
1599        /// <returns></returns>
1600        public DataSet GetMortgageCaseWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1601        {
1602            DataSet ds = new DataSet();
1603            try
1604            {
1605                SqlParameter[] Param = new SqlParameter[12];
1606                Param[0] = new SqlParameter("@CurrentUserId", userid);
1607                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1608                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1609                if (!string.IsNullOrEmpty(Negotiator))
1610                {
1611                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1612                    Param[4] = new SqlParameter("@Company", Company.Trim());
1613                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1614
1615                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1616                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1617                    Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1618
1619                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1620                    Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
1621
1622                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1623                    Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
1624                }
1625                if (!string.IsNullOrWhiteSpace(brokers))
1626                {
1627                    Param[6] = new SqlParameter("@BrokerId", brokers);
1628                }
1629                if (!string.IsNullOrWhiteSpace(type))
1630                {
1631                    Param[7] = new SqlParameter("@MortgageType", type);
1632                }
1633
1634                Param[8] = new SqlParameter("@productTypeID", productTypeID);
1635
1636                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten_Update", Param);
1637            }
1638            catch (Exception ex)
1639            {
1640                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetMortgageCaseWrittenReportByIds", ex.Message);
1641            }
1642            return ds;
1643        }
1644
1645        #endregion [Mortgage Written Report]
1646
1647        #region [Insurance Case Written Report]
1648
1649        /// <summary>
1650        /// method for InsuranceCaseWritten
1651        /// created on : 03/11/2014
1652        ///modified on 23-12-2014
1653        /// </summary>
1654        /// <param name="type"></param>
1655        /// <param name="fromdate"></param>
1656        /// <param name="todate"></param>
1657        /// <param name="userid"></param>
1658        /// <param name="brokerId"></param>
1659        /// <param name="Negotiator"></param>
1660        /// <param name="brokers"></param>
1661        /// <param name="Company"></param>
1662        /// <param name="Branches"></param>
1663        /// <param name="productTypeID"></param>
1664        /// <returns></returns>
1665        public DataSet GetInsuranceCaseWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1666        {
1667            DataSet ds = new DataSet();
1668            try
1669            {
1670                SqlParameter[] Param = new SqlParameter[12];
1671                Param[0] = new SqlParameter("@CurrentUserId", userid);
1672                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1673                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1674                if (!string.IsNullOrEmpty(Negotiator))
1675                {
1676                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1677                    Param[4] = new SqlParameter("@Company", Company.Trim());
1678                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1679
1680                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1681                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1682                    Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1683
1684                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1685                    Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
1686
1687                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1688                    Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
1689                }
1690                if (!string.IsNullOrWhiteSpace(brokers))
1691                {
1692                    Param[6] = new SqlParameter("@BrokerId", brokers);
1693                }
1694                if (!string.IsNullOrWhiteSpace(type))
1695                {
1696                    Param[7] = new SqlParameter("@InsuranceType", type);
1697                }
1698                Param[8] = new SqlParameter("@productTypeID", productTypeID);
1699
1700                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCaseWritten_Update", Param);
1701            }
1702            catch (Exception ex)
1703            {
1704                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceCaseWrittenReportByIds", ex.Message);
1705            }
1706            return ds;
1707        }
1708
1709        #endregion [Insurance Case Written Report]
1710
1711        #region [Mortgage Completed Report]
1712
1713        /// <summary>
1714        /// Get mortgage completed report by ids.
1715        /// </summary>
1716        /// <param name="NegotiatorIds"></param>
1717        /// <param name="BranchIds"></param>
1718        /// <param name="CompanyIds"></param>
1719        /// <param name="type"></param>
1720        /// <param name="fromdate"></param>
1721        /// <param name="todate"></param>
1722        /// <param name="userid"></param>
1723        /// <param name="brokerId"></param>
1724        /// <param name="Negotiator"></param>
1725        /// <param name="brokers"></param>
1726        /// <param name="Company"></param>
1727        /// <param name="Branches"></param>
1728        /// <param name="productTypeID"></param>
1729        /// <returns></returns>
1730        public DataSet GetMortgageCompletedReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1731        {
1732            DataSet ds = new DataSet();
1733            try
1734            {
1735                SqlParameter[] Param = new SqlParameter[12];
1736                Param[0] = new SqlParameter("@CurrentUserId", userid);
1737                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1738                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1739                if (!string.IsNullOrEmpty(Negotiator))
1740                {
1741                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1742                    Param[4] = new SqlParameter("@Company", Company.Trim());
1743                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1744
1745                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1746                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1747                    Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1748
1749                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1750                    Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
1751
1752                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1753                    Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
1754                }
1755                if (!string.IsNullOrWhiteSpace(brokers))
1756                {
1757                    Param[6] = new SqlParameter("@BrokerId", brokers);
1758                }
1759                if (!string.IsNullOrWhiteSpace(type))
1760                {
1761                    Param[7] = new SqlParameter("@MortgageType", type);
1762                }
1763
1764                Param[8] = new SqlParameter("@productTypeID", productTypeID);
1765
1766                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCompletedReport_Update", Param);
1767            }
1768            catch (Exception ex)
1769            {
1770                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetMortgageCompletedReportByIds", ex.Message);
1771            }
1772            return ds;
1773        }
1774
1775        #endregion [Mortgage Completed Report]
1776
1777        #region [Insurance Completed Report]
1778
1779        /// <summary>
1780        /// Get insurance completed report by ids.
1781        /// </summary>
1782        /// <param name="NegotiatorIds"></param>
1783        /// <param name="BranchIds"></param>
1784        /// <param name="CompanyIds"></param>
1785        /// <param name="type"></param>
1786        /// <param name="fromdate"></param>
1787        /// <param name="todate"></param>
1788        /// <param name="userid"></param>
1789        /// <param name="brokerId"></param>
1790        /// <param name="Negotiator"></param>
1791        /// <param name="brokers"></param>
1792        /// <param name="Company"></param>
1793        /// <param name="Branches"></param>
1794        /// <param name="productTypeID"></param>
1795        /// <returns></returns>
1796        public DataSet GetInsuranceCompletedReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1797        {
1798            DataSet ds = new DataSet();
1799            try
1800            {
1801                SqlParameter[] Param = new SqlParameter[12];
1802                Param[0] = new SqlParameter("@CurrentUserId", userid);
1803                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1804                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1805                if (!string.IsNullOrEmpty(Negotiator))
1806                {
1807                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1808                    Param[4] = new SqlParameter("@Company", Company.Trim());
1809                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1810
1811                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1812                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1813                    Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1814
1815                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1816                    Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
1817
1818                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1819                    Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
1820                }
1821                if (!string.IsNullOrWhiteSpace(brokers))
1822                {
1823                    Param[6] = new SqlParameter("@BrokerId", brokers);
1824                }
1825                if (!string.IsNullOrWhiteSpace(type))
1826                {
1827                    Param[7] = new SqlParameter("@InsuranceType", type);
1828                }
1829                Param[8] = new SqlParameter("@productTypeID", productTypeID);
1830
1831                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetInsuranceCompletedReport_Update", Param);
1832            }
1833            catch (Exception ex)
1834            {
1835                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceCompletedReportByIds", ex.Message);
1836            }
1837            return ds;
1838        }
1839        #endregion [Insurance Completed Report]
1840
1841        #region [Pipeline Review Report]
1842
1843        /// <summary>
1844        /// Get pipeline review report by ids.
1845        /// </summary>
1846        /// <param name="NegotiatorIds"></param>
1847        /// <param name="BranchIds"></param>
1848        /// <param name="CompanyIds"></param>
1849        /// <param name="type"></param>
1850        /// <param name="fromdate"></param>
1851        /// <param name="todate"></param>
1852        /// <param name="userid"></param>
1853        /// <param name="brokerId"></param>
1854        /// <param name="Negotiator"></param>
1855        /// <param name="brokers"></param>
1856        /// <param name="Company"></param>
1857        /// <param name="Branches"></param>
1858        /// <returns></returns>
1859        public DataSet GetPipelineReviewReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
1860        {
1861            DataSet ds = new DataSet();
1862            try
1863            {
1864                SqlParameter[] Param = new SqlParameter[11];
1865                Param[0] = new SqlParameter("@CurrentUserId", userid);
1866                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1867                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1868                if (!string.IsNullOrEmpty(Negotiator))
1869                {
1870                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1871                    Param[4] = new SqlParameter("@Company", Company.Trim());
1872                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1873
1874                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1875                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1876                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1877
1878                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1879                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
1880
1881                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1882                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
1883                }
1884                if (!string.IsNullOrWhiteSpace(brokers))
1885                {
1886                    Param[6] = new SqlParameter("@BrokerId", brokers);
1887                }
1888                if (!string.IsNullOrWhiteSpace(type))
1889                {
1890                    Param[7] = new SqlParameter("@MortgageType", type);
1891                }
1892
1893                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMortgageCaseWritten_Update", Param);
1894            }
1895            catch (Exception ex)
1896            {
1897                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetPipelineReviewReportByIds", ex.Message);
1898            }
1899            return ds;
1900        }
1901
1902        #endregion [Pipeline Review Report]
1903
1904        #region [WOCC Report]
1905
1906        /// <summary>
1907        /// Get WOCC report by ids.
1908        /// </summary>
1909        /// <param name="NegotiatorIds"></param>
1910        /// <param name="BranchIds"></param>
1911        /// <param name="CompanyIds"></param>
1912        /// <param name="type"></param>
1913        /// <param name="fromdate"></param>
1914        /// <param name="todate"></param>
1915        /// <param name="userid"></param>
1916        /// <param name="brokerId"></param>
1917        /// <param name="Negotiator"></param>
1918        /// <param name="brokers"></param>
1919        /// <param name="Company"></param>
1920        /// <param name="Branches"></param>
1921        /// <param name="FinType"></param>
1922        /// <returns></returns>
1923        public DataSet GetNewFinancialInformationReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int FinType = 0)
1924        {
1925            DataSet ds = new DataSet();
1926            try
1927            {
1928                SqlParameter[] Param = new SqlParameter[11];
1929                Param[0] = new SqlParameter("@CurrentUserId", userid);
1930                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1931                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1932                if (!string.IsNullOrEmpty(Negotiator))
1933                {
1934                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1935                    Param[4] = new SqlParameter("@Company", Company.Trim());
1936                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1937
1938                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
1939                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
1940                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
1941
1942                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
1943                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
1944
1945                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
1946                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
1947                }
1948                if (!string.IsNullOrWhiteSpace(brokers))
1949                {
1950                    Param[6] = new SqlParameter("@BrokerId", brokers);
1951                }
1952                if (!string.IsNullOrWhiteSpace(type))
1953                {
1954                    Param[7] = new SqlParameter("@InsuranceType", type);
1955                }
1956                Param[7] = new SqlParameter("@FinanceType", FinType);
1957
1958                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetWOCCReport_Update", Param);
1959            }
1960            catch (Exception ex)
1961            {
1962                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNewFinancialInformationReportByIds", ex.Message);
1963            }
1964            return ds;
1965        }
1966
1967        #endregion [WOCC Report]
1968
1969        #region [Total written Report]
1970        /// <summary>
1971        /// Get total written report by ids.
1972        /// </summary>
1973        /// <param name="type"></param>
1974        /// <param name="fromdate"></param>
1975        /// <param name="todate"></param>
1976        /// <param name="userid"></param>
1977        /// <param name="brokerId"></param>
1978        /// <param name="Negotiator"></param>
1979        /// <param name="brokers"></param>
1980        /// <param name="Company"></param>
1981        /// <param name="Branches"></param>
1982        /// <param name="productTypeID"></param>
1983        /// <returns></returns>
1984        public DataSet GetTotalWrittenReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
1985        {
1986            DataSet ds = new DataSet();
1987            try
1988            {
1989                SqlParameter[] Param = new SqlParameter[10];
1990                Param[0] = new SqlParameter("@CurrentUserId", userid);
1991                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
1992                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
1993                if (!string.IsNullOrEmpty(Negotiator))
1994                {
1995                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
1996                    Param[4] = new SqlParameter("@Company", Company.Trim());
1997                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
1998
1999                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2000                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2001                    Param[7] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2002
2003                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2004                    Param[8] = new SqlParameter("@BranchIds", dtBranchIds);
2005
2006                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2007                    Param[9] = new SqlParameter("@CompanyIds", dtCompanyIds);
2008                }
2009                if (!string.IsNullOrWhiteSpace(brokers))
2010                {
2011                    Param[6] = new SqlParameter("@BrokerId", brokers);
2012                }
2013
2014                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetTotalWritten_Update", Param);
2015            }
2016            catch (Exception ex)
2017            {
2018                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetTotalWrittenReportByIds", ex.Message);
2019            }
2020            return ds;
2021        }
2022
2023        #endregion [Total written Report]
2024
2025        #region [Insurance Referral report]
2026        /// <summary>
2027        /// Get insurance referral results by  ids.
2028        /// </summary>
2029        /// <param name="NegotiatorIds"></param>
2030        /// <param name="BranchIds"></param>
2031        /// <param name="CompanyIds"></param>
2032        /// <param name="fromdate"></param>
2033        /// <param name="todate"></param>
2034        /// <param name="userid"></param>
2035        /// <param name="brokerId"></param>
2036        /// <param name="Negotiator"></param>
2037        /// <param name="brokers"></param>
2038        /// <param name="Company"></param>
2039        /// <param name="Branches"></param>
2040        /// <param name="ProductTypeId"></param>
2041        /// <returns></returns>
2042        public DataSet GetInsuranceReferralResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int ProductTypeId = 0)
2043        {
2044
2045            List<Insurancereferral> negotiationlist = new List<Models.Insurancereferral>();
2046            DataSet ds = new DataSet();
2047            try
2048            {
2049                SqlParameter[] Param = new SqlParameter[11];
2050                Param[0] = new SqlParameter("@CurrentUserId", userid);
2051
2052                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2053                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2054                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2055                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2056                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2057                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2058                Param[7] = new SqlParameter("@ProductTypeId", ProductTypeId);
2059
2060                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2061                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2062                Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2063
2064                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2065                Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2066
2067                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2068                Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2069
2070                ds = SqlHelper.ExecuteDataset("GetInsuranceReferralReport_Update", Param);
2071            }
2072            catch (Exception ex)
2073            {
2074                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetInsuranceReferralResultByIds", ex.Message);
2075            }
2076            return ds;
2077        }
2078        #endregion [Insurance Referral report]
2079
2080        #region[Business report]
2081
2082        /// <summary>
2083        /// Get business report by ids.
2084        /// </summary>
2085        /// <param name="fromdate"></param>
2086        /// <param name="todate"></param>
2087        /// <param name="type"></param>
2088        /// <param name="userid"></param>
2089        /// <param name="brokerId"></param>
2090        /// <param name="Negotiator"></param>
2091        /// <param name="brokers"></param>
2092        /// <param name="Company"></param>
2093        /// <param name="Branches"></param>
2094        /// <param name="productTypeID"></param>
2095        /// <returns></returns>
2096        public DataSet GetBusinessReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2097        {
2098            DataSet ds = new DataSet();
2099            try
2100            {
2101                SqlParameter[] Param = new SqlParameter[11];
2102                Param[0] = new SqlParameter("@CurrentUserId", userid);
2103                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2104                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2105                if (!string.IsNullOrEmpty(Negotiator))
2106                {
2107                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
2108                    Param[4] = new SqlParameter("@Company", Company.Trim());
2109                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
2110
2111                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2112                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2113                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2114
2115                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2116                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2117
2118                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2119                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2120                }
2121                if (!string.IsNullOrWhiteSpace(brokers))
2122                {
2123                    Param[6] = new SqlParameter("@BrokerId", brokers);
2124                }
2125
2126                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2127
2128                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBusinessReport_Update", Param);
2129            }
2130            catch (Exception ex)
2131            {
2132                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetBusinessReportByIds", ex.Message);
2133            }
2134            return ds;
2135        }
2136        #endregion
2137
2138        #region[Most Valuable referrer report]
2139
2140        /// <summary>
2141        /// Get most valuable report by ids.
2142        /// </summary>
2143        /// <param name="type"></param>
2144        /// <param name="userid"></param>
2145        /// <param name="brokerId"></param>
2146        /// <param name="Negotiator"></param>
2147        /// <param name="brokers"></param>
2148        /// <param name="Company"></param>
2149        /// <param name="Branches"></param>
2150        /// <param name="productTypeID"></param>
2151        /// <returns></returns>
2152        public DataSet GetMostValuableReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2153        {
2154            DataSet ds = new DataSet();
2155            try
2156            {
2157                SqlParameter[] Param = new SqlParameter[8];
2158                Param[0] = new SqlParameter("@CurrentUserId", userid);
2159
2160                if (!string.IsNullOrEmpty(Negotiator))
2161                {
2162                    Param[1] = new SqlParameter("@Negotiator", Negotiator.Trim());
2163                    Param[2] = new SqlParameter("@Company", Company.Trim());
2164                    Param[3] = new SqlParameter("@Branches", Branches.Trim());
2165
2166                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2167                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2168                    Param[5] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2169
2170                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2171                    Param[6] = new SqlParameter("@BranchIds", dtBranchIds);
2172
2173                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2174                    Param[7] = new SqlParameter("@CompanyIds", dtCompanyIds);
2175                }
2176                if (!string.IsNullOrWhiteSpace(brokers))
2177                {
2178                    Param[4] = new SqlParameter("@BrokerId", brokers);
2179                }
2180
2181
2182                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetMostValuableReferrersreport_Update", Param);
2183            }
2184            catch (Exception ex)
2185            {
2186                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetBusinessReportByIds", ex.Message);
2187            }
2188            return ds;
2189        }
2190        #endregion
2191
2192        #region [Negotiator Weekly Report]
2193
2194        /// <summary>
2195        /// Getting negotiator weekly report
2196        /// </summary>
2197        /// <param name="fromdate">From date</param>
2198        /// <param name="todate">To date</param>
2199        /// <param name="userid">User Id</param>
2200        /// <param name="brokerId">Broker Id</param>
2201        /// <param name="Negotiator">Negotiator</param>
2202        /// <param name="brokers">Brokers</param>
2203        /// <param name="Company">Companies</param>
2204        /// <param name="Branches">Branches</param>
2205        /// <param name="productTypeID">Product Type</param>
2206        /// <returns></returns>
2207        public DataSet GetNegotiatorweeklyByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2208        {
2209            DataSet ds;
2210            try
2211            {
2212                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2213                SqlParameter[] Param = new SqlParameter[11];
2214                Param[0] = new SqlParameter("@CurrentUserId", userid);
2215                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2216                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2217                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2218                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2219                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2220                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2221                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2222
2223                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2224                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2225                Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2226
2227                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2228                Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2229
2230                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2231                Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2232
2233                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeekly_Update", Param);
2234            }
2235            catch (Exception ex)
2236            {
2237                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorweekly_Update");
2238                ds = new DataSet();
2239
2240            }
2241            return ds;
2242        }
2243
2244        #endregion [Negotiator Weekly Report]
2245
2246        #region [Negotiator Result]
2247
2248        /// <summary>
2249        /// Getting dataset from the database for Negotiator Report 
2250        /// </summary>
2251        /// <param name="fromdate">From Date</param>
2252        /// <param name="todate">To Date</param>
2253        /// <param name="userid">User Id</param>
2254        /// <param name="pageNo">Page no</param>
2255        /// <param name="pageSize">Page size</param>
2256        /// <param name="brokerId">Broker Id</param>
2257        /// <param name="Negotiator">Negotiator</param>
2258        /// <param name="brokers">Brokers</param>
2259        /// <param name="Company">Companies</param>
2260        /// <param name="Branches">Branches</param>
2261        /// <param name="productTypeID">Product type</param>
2262        /// <returns></returns>
2263        public DataSet GetNegotiatorResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2264        {
2265            DataSet ds;
2266            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2267            try
2268            {
2269                SqlParameter[] Param = new SqlParameter[13];
2270                Param[0] = new SqlParameter("@CurrentUserId", userid);
2271                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2272                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2273                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2274                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2275                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2276                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2277                Param[7] = new SqlParameter("@PageNo", pageNo);
2278                Param[8] = new SqlParameter("@PageSize", pageSize);
2279                Param[9] = new SqlParameter("@productTypeID", productTypeID);
2280
2281                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2282                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2283                Param[10] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2284
2285                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2286                Param[11] = new SqlParameter("@BranchIds", dtBranchIds);
2287
2288                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2289                Param[12] = new SqlParameter("@CompanyIds", dtCompanyIds);
2290
2291                ds = SqlHelper.ExecuteDataset("GetNegotiatorReport_Update", Param);
2292                return ds;
2293            }
2294            catch (Exception ex)
2295            {
2296                Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
2297                return ds = new DataSet();
2298            }
2299        }
2300
2301        #endregion [Negotiator Result]
2302
2303        #region [Negotiator Data Result]
2304
2305        /// <summary>
2306        /// Get negotiator data result by Ids.
2307        /// </summary>
2308        /// <param name="NegotiatorIds"></param>
2309        /// <param name="BranchIds"></param>
2310        /// <param name="CompanyIds"></param>
2311        /// <param name="fromdate"></param>
2312        /// <param name="todate"></param>
2313        /// <param name="userid"></param>
2314        /// <param name="pageNo"></param>
2315        /// <param name="pageSize"></param>
2316        /// <param name="brokerId"></param>
2317        /// <param name="Negotiator"></param>
2318        /// <param name="brokers"></param>
2319        /// <param name="Company"></param>
2320        /// <param name="Branches"></param>
2321        /// <returns></returns>
2322        public ReportsDataModel GetNegotiatorDataResultByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int pageNo, int pageSize, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "")
2323        {
2324            ReportsDataModel dataResult = new ReportsDataModel();
2325            dataResult.Negotiation = new List<Negotiation>();
2326            dataResult.NegotiationNotes = new List<NegotiationNotes>();
2327            SqlParameter[] Param = new SqlParameter[9];
2328            Param[0] = new SqlParameter("@CurrentUserId", userid);
2329            Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2330            Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2331            Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2332            Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2333            Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2334            Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2335            Param[7] = new SqlParameter("@PageNo", pageNo);
2336            Param[8] = new SqlParameter("@PageSize", pageSize);
2337
2338            //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2339            DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2340            Param[9] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2341
2342            DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2343            Param[10] = new SqlParameter("@BranchIds", dtBranchIds);
2344
2345            DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2346            Param[11] = new SqlParameter("@CompanyIds", dtCompanyIds);
2347
2348            SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "GetNegotiatorReport_Update", Param);
2349            ctx.Database.Initialize(force: false);
2350
2351            ctx.Database.Connection.Open();
2352            dataResult.Negotiation = ((IObjectContextAdapter)ctx).ObjectContext.Translate<Negotiation>(reader).ToList();
2353            reader.NextResult();
2354            dataResult.NegotiationNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
2355            ctx.Database.Connection.Close();
2356            if (!reader.IsClosed)
2357            {
2358                reader.Close();
2359            }
2360            return dataResult;
2361        }
2362
2363        #endregion [Negotiator Data Result]
2364
2365        #region [Notes Data]
2366
2367        /// <summary>
2368        /// Getting the dataset for Notes Data
2369        /// </summary>
2370        /// <param name="userId">User Id</param>
2371        /// <param name="pageNo">Page No</param>
2372        /// <param name="pageSize">Page Size</param>
2373        /// <param name="fromDate">FRom Date</param>
2374        /// <param name="toDate">To Date</param>
2375        /// <param name="negotiators">Negotiators</param>
2376        /// <param name="company">Companies</param>
2377        /// <param name="branches">branches</param>
2378        /// <param name="brokers">Brokers</param>
2379        /// <param name="productTypeID">Product type</param>
2380        /// <returns></returns>
2381        public ReadNotes GetNotesDataByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, int userId, int pageNo, int pageSize, DateTime fromDate, DateTime toDate, string negotiators = "", string company = "", string branches = "", string brokers = "", int productTypeID = 0)
2382        {
2383            var reportsDN = new ReadNotes();
2384            try
2385            {
2386                DataTable notes = new DataTable();
2387                SqlParameter[] Param = new SqlParameter[13];
2388                Param[0] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(negotiators) ? null : negotiators.Trim()));
2389                Param[1] = new SqlParameter("@Company", (string.IsNullOrEmpty(company) ? null : company.Trim()));
2390                Param[2] = new SqlParameter("@Branches", (string.IsNullOrEmpty(branches) ? null : branches.Trim()));
2391                Param[3] = new SqlParameter("@BrokerIds", (string.IsNullOrEmpty(brokers) ? null : brokers));
2392                Param[4] = new SqlParameter("@FromDate", fromDate.ToString("dd/MMM/yyyy"));
2393                Param[5] = new SqlParameter("@ToDate", toDate.ToString("dd/MMM/yyyy"));
2394                Param[6] = new SqlParameter("@CurrentUserId", userId);
2395                Param[7] = new SqlParameter("@PageNo", pageNo);
2396                Param[8] = new SqlParameter("@PageSize", pageSize);
2397                Param[9] = new SqlParameter("@productTypeID", productTypeID);
2398
2399                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2400                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2401                Param[10] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2402
2403                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2404                Param[11] = new SqlParameter("@BranchIds", dtBranchIds);
2405
2406                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2407                Param[12] = new SqlParameter("@CompanyIds", dtCompanyIds);
2408
2409                SqlDataReader reader = SqlHelper.ExecuteReaderNext(CommandType.StoredProcedure, "Usp_GetNotesData_Update", Param);
2410                ctx.Database.Initialize(force: false);
2411                ctx.Database.Connection.Open();
2412                reportsDN.AdvisorNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
2413                reader.NextResult();
2414                reportsDN.AgentNotes = ((IObjectContextAdapter)ctx).ObjectContext.Translate<NegotiationNotes>(reader).ToList();
2415                ctx.Database.Connection.Close();
2416                if (!reader.IsClosed)
2417                {
2418                    reader.Close();
2419                }
2420            }
2421            catch (Exception ex)
2422            {
2423                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNotesDataByIds", ex.Message);
2424            }
2425            return reportsDN; ;
2426        }
2427
2428        #endregion [Notes Data]
2429
2430        #region [Negotiator Weekly Notes Created]
2431        //added on 2015-02-03
2432        /// <summary>
2433        /// Getting Weekly created notes data
2434        /// </summary>
2435        /// <param name="fromdate">From Date</param>
2436        /// <param name="todate">To Date</param>
2437        /// <param name="userid">User Id</param>
2438        /// <param name="brokerId">Broker Id</param>
2439        /// <param name="Negotiator">Negotiator</param>
2440        /// <param name="brokers">Brokers</param>
2441        /// <param name="Company">Company</param>
2442        /// <param name="Branches">Branches</param>
2443        /// <param name="productTypeID">Product Type</param>
2444        /// <returns></returns>
2445        public DataSet GetNegotiatorWeeklyCreatedNotesByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2446        {
2447            DataSet ds;
2448            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2449            try
2450            {
2451                SqlParameter[] Param = new SqlParameter[11];
2452                Param[0] = new SqlParameter("@CurrentUserId", userid);
2453                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2454                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2455                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2456                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2457                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2458                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2459                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2460
2461                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2462                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2463                Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2464
2465                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2466                Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2467
2468                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2469                Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2470
2471                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteCreated_Update", Param);
2472                return ds;
2473            }
2474            catch (Exception ex)
2475            {
2476                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyCreatedNotesByIds", ex.Message);
2477                ds = new DataSet();
2478                return ds;
2479            }
2480        }
2481
2482        #endregion [Negotiator Weekly Notes Created]
2483
2484        #region [Negotiator Weekly Notes Updated]
2485
2486        //added on 2015-02-03
2487        /// <summary>
2488        /// Getting weekly updated notes
2489        /// </summary>
2490        /// <param name="fromdate">From Date</param>
2491        /// <param name="todate">To date</param>
2492        /// <param name="userid">User Id</param>
2493        /// <param name="brokerId">Broker Id</param>
2494        /// <param name="Negotiator">Negotiator</param>
2495        /// <param name="brokers">Brokers</param>
2496        /// <param name="Company">Company</param>
2497        /// <param name="Branches">Branches</param>
2498        /// <param name="productTypeID">Product Type</param>
2499        /// <returns></returns>
2500        public DataSet GetNegotiatorWeeklyUpdatedNotesByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2501        {
2502            DataSet ds;
2503            List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2504            try
2505            {
2506                SqlParameter[] Param = new SqlParameter[11];
2507                Param[0] = new SqlParameter("@CurrentUserId", userid);
2508                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2509                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2510                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2511                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2512                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2513                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2514                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2515
2516                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2517                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2518                Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2519
2520                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2521                Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2522
2523                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2524                Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2525
2526                ds = SqlHelper.ExecuteDataset("GetNegotiatorReportWeeklyNoteUpdated_Update", Param);
2527                return ds;
2528            }
2529            catch (Exception ex)
2530            {
2531                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorWeeklyUpdatedNotesByIds", ex.Message);
2532                ds = new DataSet();
2533                return ds;
2534            }
2535        }
2536
2537        #endregion [Negotiator Weekly Notes Updated]
2538
2539        #region [Negotiator Report]
2540
2541        /// <summary>
2542        /// Getting negotiator data for PDF
2543        /// </summary>
2544        /// <param name="fromdate">From Date</param>
2545        /// <param name="todate">To Date</param>
2546        /// <param name="userid">User Id</param>
2547        /// <param name="brokerId">Broker Id</param>
2548        /// <param name="Negotiator">Negotiator</param>
2549        /// <param name="brokers">Brokers</param>
2550        /// <param name="Company">Company</param>
2551        /// <param name="Branches">Branches</param>
2552        /// <param name="productTypeID">Product Type</param>
2553        /// <returns></returns>
2554        public DataSet GetNegotiatorByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2555        {
2556            DataSet ds;
2557            try
2558            {
2559                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2560                SqlParameter[] Param = new SqlParameter[11];
2561                Param[0] = new SqlParameter("@CurrentUserId", userid);
2562
2563                Param[1] = new SqlParameter("@Negotiator", (string.IsNullOrEmpty(Negotiator) ? null : Negotiator.Trim()));
2564                Param[2] = new SqlParameter("@Company", (string.IsNullOrEmpty(Company) ? null : Company.Trim()));
2565                Param[3] = new SqlParameter("@Branches", (string.IsNullOrEmpty(Branches) ? null : Branches.Trim()));
2566                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2567                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2568                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2569                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2570
2571                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2572                DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2573                Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2574
2575                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2576                Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2577
2578                DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2579                Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2580
2581                ds = SqlHelper.ExecuteDataset("GetNegotiatorReport_Update", Param);
2582            }
2583            catch (Exception ex)
2584            {
2585                ds = new DataSet();
2586                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetNegotiatorByIds", ex.Message);
2587            }
2588            return ds;
2589        }
2590
2591        #endregion [Negotiator Report]
2592
2593        #region [Overall Conversion Report]
2594
2595        /// <summary>
2596        /// Getting dataset for overall conversion report for Introducer Report
2597        /// </summary>
2598        /// <param name="totalLeads">Total Leads output parameter</param>
2599        /// <param name="totalApps">Total Apps Output parameter</param>
2600        /// <param name="fromdate">from date</param>
2601        /// <param name="todate">to date</param>
2602        /// <param name="userid">user Id</param>
2603        /// <param name="brokerId">broker Id</param>
2604        /// <param name="Negotiator">Negotiator</param>
2605        /// <param name="brokers">Brokers</param>
2606        /// <param name="Company">Companies</param>
2607        /// <param name="Branches">Branches</param>
2608        /// <param name="productTypeID">Product Type Id</param>
2609        public void GetOverAllConversionReportByIds(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, out int totalLeads, out int totalApps, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2610        {
2611            DataSet ds;
2612            totalLeads = 0;
2613            totalApps = 0;
2614            try
2615            {
2616                List<Negotiation> negotiationlist = new List<Models.Negotiation>();
2617                SqlParameter[] Param = new SqlParameter[11];
2618                Param[0] = new SqlParameter("@CurrentUserId", userid);
2619                if (!string.IsNullOrEmpty(Negotiator))
2620                {
2621                    Param[1] = new SqlParameter("@Negotiator", "'" + Negotiator.TrimEnd(',') + "'");
2622                    Param[2] = new SqlParameter("@Company", "'" + Company.TrimEnd(',') + "'");
2623                    Param[3] = new SqlParameter("@Branches", "'" + Branches.TrimEnd(',') + "'");
2624
2625                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2626                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2627                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2628
2629                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2630                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2631
2632                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2633                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2634                }
2635                if (!string.IsNullOrWhiteSpace(brokers))
2636                {
2637                    Param[4] = new SqlParameter("@BrokerId", brokers);
2638                }
2639                Param[5] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2640                Param[6] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2641                Param[7] = new SqlParameter("@productTypeID", productTypeID);
2642                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetOverAllConversionForIntroducer_Update", Param);
2643                if (ds.Tables[0].Rows.Count > 0)
2644                {
2645                    totalLeads = Convert.ToInt32(ds.Tables[0].Rows[0]["TotalCount"].ToString());
2646                }
2647                if (ds.Tables[1].Rows.Count > 0)
2648                {
2649                    totalApps = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"].ToString());
2650                }
2651            }
2652            catch (Exception ex)
2653            {
2654                ds = new DataSet();
2655                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetOverAllConversionReportByIds", ex.Message);
2656            }
2657
2658        }
2659
2660        ///// <summary>
2661        ///// Get introducer visit notes on the basis of filters.
2662        ///// </summary>
2663        ///// <returns></returns>
2664        //public List<IntroducerVisitNotes> GetIntroducerVisitNoteDetails(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
2665        //{
2666        //    List<int> brokerids = new List<int>();
2667        //    if (!string.IsNullOrEmpty(brokers))
2668        //    {
2669        //        brokerids = brokers.Split(',').Select(Int32.Parse).ToList(); 
2670        //    }
2671        //    var lstIntroducerNotes = new List<IntroducerVisitNotes>();
2672        //    db_occfinance_5572Entities _context = new db_occfinance_5572Entities();
2673        //    try
2674        //    {
2675        //        if (!string.IsNullOrEmpty(brokers) && companyIds!=null && companyIds.Count>0)
2676        //        {
2677        //            lstIntroducerNotes = (from intNotes in _context.tblIntroducerVisitNotes
2678        //                                  join usr in _context.tblusers on intNotes.CreatedBy equals usr.userid
2679        //                                  join acct in _context.tblaccounts on usr.account equals acct.accountid
2680        //                                  where companyIds.Contains(acct.accountid)
2681        //                                  && brokerids.Contains(usr.userid)
2682        //                                  select new IntroducerVisitNotes
2683        //                                  {
2684        //                                      Id = intNotes.Id,
2685        //                                      Account = intNotes.Account,
2686        //                                      UserName = usr.brokername,
2687        //                                      CreatedOn = intNotes.Created,
2688        //                                      CreatedBy = intNotes.CreatedBy,
2689        //                                      Note = intNotes.Note,
2690        //                                      Introducer = acct.companyname
2691        //                                  }).ToList();
2692        //        }
2693        //        else if(string.IsNullOrEmpty(brokers) && companyIds != null && companyIds.Count > 0)
2694        //        {
2695        //            lstIntroducerNotes = (from intNotes in _context.tblIntroducerVisitNotes
2696        //                                  join usr in _context.tblusers on intNotes.CreatedBy equals usr.userid
2697        //                                  join acct in _context.tblaccounts on usr.account equals acct.accountid
2698        //                                  where companyIds.Contains(acct.accountid)                                          
2699        //                                  select new IntroducerVisitNotes
2700        //                                  {
2701        //                                      Id = intNotes.Id,
2702        //                                      Account = intNotes.Account,
2703        //                                      UserName = usr.brokername,
2704        //                                      CreatedOn = intNotes.Created,
2705        //                                      CreatedBy = intNotes.CreatedBy,
2706        //                                      Note = intNotes.Note,
2707        //                                      Introducer = acct.companyname
2708        //                                  }).ToList();
2709        //        }
2710        //        if (lstIntroducerNotes != null && lstIntroducerNotes.Count > 0)
2711        //        {
2712        //            foreach (var intNote in lstIntroducerNotes)
2713        //            {
2714        //                intNote.Created = Convert.ToDateTime(intNote.CreatedOn).ToString("dd/MM/yyyy hh:mm");
2715        //            }
2716        //        }
2717        //    }
2718        //    catch (Exception ex)
2719        //    {
2720        //        Helper.ErrorLog(ex.InnerException, "Q_Reports.cs", "GetIntroducerVisitNoteDetails", ex.Message);
2721        //    }
2722        //    return lstIntroducerNotes;
2723        //}
2724
2725        /// <summary>
2726        /// Get introducer appointment details.
2727        /// </summary>
2728        /// <returns></returns>
2729        public List<IntroducerVisitNotes> GetIntroducerVisitNoteDetails(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
2730        {
2731            List<IntroducerVisitNotes> introducerVisits = new List<IntroducerVisitNotes>();
2732            DataSet dsIntroducerVisits;
2733            try
2734            {
2735                SqlParameter[] Param = new SqlParameter[10];
2736                Param[0] = new SqlParameter("@CurrentUserId", null);
2737
2738                Param[1] = new SqlParameter("@Negotiator", null);
2739                Param[2] = new SqlParameter("@Company", null);
2740                Param[3] = new SqlParameter("@Branches", null);
2741                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2742
2743                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2744                DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
2745                Param[5] = new SqlParameter("@CompanyIds", dtCompanyIds);
2746
2747                Param[6] = new SqlParameter("@BranchIds", null);
2748
2749                Param[7] = new SqlParameter("@NegotiatorIds", null);
2750                Param[8] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2751                Param[9] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2752
2753                dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsNoteDetails", Param);
2754
2755
2756                if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
2757                {
2758                    introducerVisits = DataTableHelper.CreateListFromTableForAll<IntroducerVisitNotes>(dsIntroducerVisits.Tables[0]).ToList();
2759
2760                    foreach (var intNote in introducerVisits)
2761                    {
2762                        intNote.CreateOn = Convert.ToDateTime(intNote.CreatedOn).ToString("dd/MM/yyyy hh:mm");
2763                    }
2764                }
2765            }
2766            catch (Exception ex)
2767            {
2768                Helper.ErrorLog(ex.InnerException, "Q_Reports.cs", "GetIntroducerVisitNoteDetails", ex.Message);
2769            }
2770            return introducerVisits;
2771        }
2772
2773        #endregion [Overall Conversion Report]
2774
2775        #region [Conversion Rate For Insurance Referrals]
2776        public DataSet GetConversionRateForInsuranceReferrals(List<int> NegotiatorIds, List<int> BranchIds, List<int> CompanyIds, string type, DateTime fromdate, DateTime todate, int userid, int brokerId = 0, string Negotiator = "", string brokers = "", string Company = "", string Branches = "", int productTypeID = 0)
2777        {
2778            DataSet ds = new DataSet();
2779            try
2780            {
2781                SqlParameter[] Param = new SqlParameter[11];
2782                Param[0] = new SqlParameter("@CurrentUserId", userid);
2783                Param[1] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2784                Param[2] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2785                if (!string.IsNullOrEmpty(Negotiator))
2786                {
2787                    Param[3] = new SqlParameter("@Negotiator", Negotiator.Trim());
2788                    Param[4] = new SqlParameter("@Company", Company.Trim());
2789                    Param[5] = new SqlParameter("@Branches", Branches.Trim());
2790
2791                    //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2792                    DataTable dtNegotiatorIds = Helper.FillDataTable(NegotiatorIds);
2793                    Param[8] = new SqlParameter("@NegotiatorIds", dtNegotiatorIds);
2794
2795                    DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2796                    Param[9] = new SqlParameter("@BranchIds", dtBranchIds);
2797
2798                    DataTable dtCompanyIds = Helper.FillDataTable(CompanyIds);
2799                    Param[10] = new SqlParameter("@CompanyIds", dtCompanyIds);
2800                }
2801                if (!string.IsNullOrWhiteSpace(brokers))
2802                {
2803                    Param[6] = new SqlParameter("@BrokerId", brokers);
2804                }
2805                if (!string.IsNullOrWhiteSpace(type))
2806                {
2807                    Param[7] = new SqlParameter("@MortgageType", type);
2808                }
2809
2810                //Param[8] = new SqlParameter("@productTypeID", productTypeID);
2811
2812                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetConversionRateForInsuranceReferrals", Param);
2813            }
2814            catch (Exception ex)
2815            {
2816                Helper.ErrorLog(ex.InnerException, "Q_Reports", "GetConversionRateForInsuranceReferrals", ex.Message);
2817            }
2818            return ds;
2819        }
2820
2821        #endregion [Conversion Rate For Insurance Referrals]
2822        
2823        #endregion [Updated Code of Reports Data Access Methods 2015-10-21]
2824
2825        #region [Get Selected Companies With Broker 2015-10-21]
2826
2827        /// <summary>
2828        /// Get selected companies with brokers by ids
2829        /// </summary>
2830        /// <param name="_allNamesparents"></param>
2831        /// <returns></returns>
2832        public string GetSelectedCompaniesWithBrokerByIds(string[] _allNamesparents)
2833        {
2834            var allNamesparents = string.Empty;
2835            var distinctList = _allNamesparents.Distinct().ToList();
2836            List<SelectListItem> _list = new List<SelectListItem>();
2837            for (int i = 0; i < distinctList.Count(); i++)
2838            {
2839
2840                if (distinctList[i].Contains("®"))
2841                {
2842                    string companyName;
2843                    string branchName;
2844                    GetCompanyAndBranchNameByIds(distinctList, i, out companyName, out branchName);
2845
2846                    _list.Add(new SelectListItem
2847                    {
2848                        Text = System.Text.RegularExpressions.Regex.Replace(companyName, "[^a-zA-Z0-9)]+", "", System.Text.RegularExpressions.RegexOptions.Compiled),
2849                        Value = branchName
2850                    });
2851                }
2852            }
2853            // System.Globalization.TextInfo myTI = new System.Globalization.CultureInfo("en-US", false).TextInfo;
2854
2855            var _companieslist = (from r in _list
2856                                  select new
2857                                  {
2858                                      companyname = System.Text.RegularExpressions.Regex.Replace(r.Text, "[^a-zA-Z0-9)]+", "", System.Text.RegularExpressions.RegexOptions.Compiled).Trim()
2859
2860                                  }).Distinct().ToList();
2861            for (int i = 0; i < _companieslist.Count(); i++)
2862            {
2863                var s = string.Join(", ", _list.Where(p => p.Text == _companieslist[i].companyname).Select(p => p.Value.ToString()));
2864                allNamesparents += string.Format("&nbsp;<b>&nbsp;{0}&nbsp;</b>(&nbsp;{1}&nbsp;)", _companieslist.ElementAtOrDefault(i).companyname, s);
2865            }
2866            return allNamesparents;
2867        }
2868
2869        /// <summary>
2870        ///  Get company and branch names by id.
2871        /// </summary>
2872        /// <param name="companyAndBranchList"></param>
2873        /// <param name="index"></param>
2874        /// <param name="companyName"></param>
2875        /// <param name="branchName"></param>
2876        public void GetCompanyAndBranchNameByIds(List<string> companyAndBranchList, int index, out string companyName, out string branchName)
2877        {
2878            try
2879            {
2880                db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
2881                var companyId = Convert.ToInt32(companyAndBranchList[index].Split('®')[0]);
2882                var branchId = Convert.ToInt32(companyAndBranchList[index].Split('®')[1]);
2883                companyName = ctx.tblaccounts.Where(com => com.accountid == companyId).Select(com => com.companyname).FirstOrDefault();
2884                branchName = ctx.tblBranches.Where(br => br.Id == branchId).Select(br => br.Branch).FirstOrDefault();
2885            }
2886            catch (Exception ex)
2887            {
2888                companyName = string.Empty;
2889                branchName = string.Empty;
2890                Helper.ErrorLog(ex.InnerException, "ReportsController", "GetCompanyAndBranchNameByIds", ex.Message);
2891            }
2892        }
2893
2894        /// <summary>
2895        /// Get negotiator name by ids
2896        /// </summary>
2897        /// <param name="NegotiatorIds"></param>
2898        /// <returns></returns>
2899        public string[] GetNegotiatorsNameByIds(List<int> NegotiatorIds)
2900        {
2901            string[] negotiatorNames = new string[NegotiatorIds.Count()];
2902            try
2903            {
2904                db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
2905                negotiatorNames = ctx.tblNegotiators.Where(neg => NegotiatorIds.Contains(neg.Id)).Select(neg => neg.Negotiator).ToList().ToArray();
2906
2907            }
2908            catch (Exception ex)
2909            {
2910                Helper.ErrorLog(ex.InnerException, "ReportsController", "GetNegotiatorName", ex.Message);
2911            }
2912            return negotiatorNames;
2913        }
2914
2915
2916
2917
2918
2919        #endregion [Get Selected Companies With Broker 2015-10-21]
2920
2921        #region [Introducer Visits]     
2922
2923        /// <summary>
2924        /// Get introducer appointment details.
2925        /// </summary>
2926        /// <returns></returns>
2927        public List<IntroducerVisitsModel> GetIntroducerVisitsForAppointment(List<int> companyIds, string brokers, DateTime fromdate, DateTime todate)
2928        {
2929            List<IntroducerVisitsModel> introducerVisits = new List<IntroducerVisitsModel>();
2930            DataSet dsIntroducerVisits;
2931            try
2932            {
2933                SqlParameter[] Param = new SqlParameter[10];
2934                Param[0] = new SqlParameter("@CurrentUserId", null);
2935
2936                Param[1] = new SqlParameter("@Negotiator", null);
2937                Param[2] = new SqlParameter("@Company", null);
2938                Param[3] = new SqlParameter("@Branches", null);
2939                Param[4] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2940
2941                //Convert Negotiator, Branch and Company Ids list into datatable and pass them into sql procedure parameter value. -20151021
2942                DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
2943                Param[5] = new SqlParameter("@CompanyIds", dtCompanyIds);
2944
2945                Param[6] = new SqlParameter("@BranchIds", null);
2946
2947                Param[7] = new SqlParameter("@NegotiatorIds", null);
2948                Param[8] = new SqlParameter("@FromDate", fromdate.ToString("dd/MMM/yyyy"));
2949                Param[9] = new SqlParameter("@ToDate", todate.ToString("dd/MMM/yyyy"));
2950
2951                dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsDetails", Param);
2952            }
2953            catch (Exception ex)
2954            {
2955                dsIntroducerVisits = new DataSet();
2956                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetIntroducerVisitsDetails", ex.Message);
2957            }
2958
2959            if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
2960                introducerVisits = DataTableHelper.CreateListFromTableForAll<IntroducerVisitsModel>(dsIntroducerVisits.Tables[0]).ToList();
2961            return introducerVisits;
2962        }
2963
2964        /// <summary>
2965        /// Get Introducer Visits for admin
2966        /// </summary>
2967        /// <param name="companyIds"></param>
2968        /// <param name="userid"></param>
2969        /// <param name="brokers"></param>
2970        /// <returns></returns>
2971        public List<IntroducerVisitsModel> GetIntroducerVisitsForAdmin(List<int> companyIds, int userid, string brokers, List<int> BranchIds, bool IsBroker)
2972        {
2973            List<IntroducerVisitsModel> introducerVisits = new List<IntroducerVisitsModel>();
2974            DataSet dsIntroducerVisits; 
2975            try
2976            {
2977                SqlParameter[] Param = new SqlParameter[5];
2978                Param[0] = new SqlParameter("@CurrentUserId", userid);
2979                Param[1] = new SqlParameter("@BrokerId", (string.IsNullOrEmpty(brokers) ? null : brokers));
2980                DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
2981                Param[2] = new SqlParameter("@CompanyIds", dtCompanyIds);
2982                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
2983                Param[3] = new SqlParameter("@BranchIds", dtBranchIds);
2984                Param[4] = new SqlParameter("@IsBroker", IsBroker);
2985
2986                dsIntroducerVisits = SqlHelper.ExecuteDataset("GetIntroducerVisitsDetailsForAdmin", Param);
2987            }
2988            catch (Exception ex)
2989            {
2990                dsIntroducerVisits = new DataSet();
2991                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetIntroducerVisitsDetailsForAdmin", ex.Message);
2992            }
2993
2994            if (dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
2995            {
2996                foreach(DataRow row in dsIntroducerVisits.Tables[0].Rows)
2997                {
2998                    var model = new IntroducerVisitsModel();
2999                    model.account = Convert.ToInt32(Convert.ToString(row["account"]));
3000                    model.broker = Convert.ToInt32(Convert.ToString(row["broker"]));
3001                    model.Introducer = Convert.ToString(row["Introducer"]);
3002                    model.Branch = Convert.ToString(row["Branch"]);
3003                    model.BranchId = Convert.ToInt32(Convert.ToString(row["BranchId"]));
3004                    model.AdviserAssigned = Convert.ToString(row["AdviserAssigned"]);
3005                    model.Id = Convert.ToInt32(Convert.ToString(row["Id"]));
3006                    model.FrequencyType = Convert.ToInt32(Convert.ToString(row["FrequencyType"]));
3007                    model.FrequencyDay = Convert.ToInt32(Convert.ToString(row["FrequencyDay"]));
3008                    introducerVisits.Add(model);
3009                }
3010            }
3011
3012            return introducerVisits;
3013        }
3014
3015        public bool UpdateIntroducerAppointmentDetails(List<IntroducerVisitsModel> lstIntroducerAppointment)
3016        {
3017            db_occfinance_5572Entities _context = new db_occfinance_5572Entities();
3018            try
3019            {
3020                lstIntroducerAppointment.RemoveAt(0);//As this contain header of table
3021                lstIntroducerAppointment = lstIntroducerAppointment.GroupBy(item => new { BranchId = item.BranchId, Account = item.account }).SelectMany(g => g.OrderBy(grp => grp.AdviserAssigned)).ToList();
3022                int xxx = 0;
3023                int prevBranchId = 0;
3024                int prevAccountId = 0;
3025
3026                foreach (var appointment in lstIntroducerAppointment)
3027                {
3028                    DayOfWeek day = (DayOfWeek)appointment.FrequencyDay;
3029                    int prev_daysToAdd = ((int)day - (int)DateTime.Now.DayOfWeek + 7) % 7;
3030                    DateTime _VisitDate = DateTime.Now.AddDays(prev_daysToAdd);
3031                    if (appointment.Id > 0)
3032                    {
3033                        var IsRecordExist = _context.tblIntroducerVisits.Where(x => x.Id == appointment.Id).FirstOrDefault();
3034                        if (IsRecordExist != null)
3035                        {
3036                            try
3037                            {
3038                                if (appointment.AssignAdvisers != "checked")
3039                                {
3040                                    IsRecordExist.IsActive = false;
3041                                    IsRecordExist.IsDeleted = true;
3042                                    IsRecordExist.Updated = DateTime.Now;
3043                                    _context.SaveChanges();
3044                                }
3045                                else
3046                                {
3047                                    IsRecordExist.broker = appointment.broker;
3048                                    IsRecordExist.FrequencyDay = appointment.FrequencyDay;
3049                                    IsRecordExist.FrequencyType = appointment.FrequencyType;
3050                                    IsRecordExist.Updated = DateTime.Now;
3051                                    if (prevBranchId != appointment.BranchId && prevAccountId != appointment.account)
3052                                    {
3053                                        xxx = 0;
3054                                        prevBranchId = appointment.BranchId;
3055                                        prevAccountId = appointment.account;
3056                                        IsRecordExist.VisitDate = _VisitDate;
3057                                    }
3058                                    else
3059                                    {
3060                                        xxx = xxx + appointment.FrequencyType;
3061                                        IsRecordExist.VisitDate = _VisitDate.AddDays(xxx * 7);
3062                                        prevBranchId = appointment.BranchId;
3063                                        prevAccountId = appointment.account;
3064                                    }
3065
3066                                    _context.SaveChanges();
3067
3068                                }
3069
3070                            }
3071                            catch (Exception ex)
3072                            {
3073                                Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails", ex.Message);
3074
3075                            }
3076                        }
3077                    }
3078                    else
3079                    {
3080                        if (appointment.AssignAdvisers == "checked" && appointment.Id == 0)
3081                        {
3082                            try
3083                            {
3084                                tblIntroducerVisit objAppointment = new tblIntroducerVisit();
3085                                objAppointment.account = appointment.account;
3086                                objAppointment.broker = appointment.broker;
3087                                objAppointment.FrequencyDay = appointment.FrequencyDay;
3088                                objAppointment.FrequencyType = appointment.FrequencyType;
3089                                objAppointment.BranchId = appointment.BranchId;
3090                                objAppointment.Created = DateTime.Now;
3091                                objAppointment.StatusId = 0;
3092                                objAppointment.Updated = DateTime.Now;
3093                                objAppointment.Created = DateTime.Now;
3094                                objAppointment.UpdatedBy = SessionHelper.UserId;
3095                                objAppointment.CreatedBy = SessionHelper.UserId;
3096                                objAppointment.IsActive = true;
3097                                if (prevBranchId != appointment.BranchId && prevAccountId != appointment.account)
3098                                {
3099                                    xxx = 0;
3100                                    prevBranchId = appointment.BranchId;
3101                                    prevAccountId = appointment.account;
3102                                    objAppointment.VisitDate = _VisitDate;
3103                                }
3104                                else
3105                                {
3106                                    xxx = xxx + appointment.FrequencyType;
3107                                    objAppointment.VisitDate = _VisitDate.AddDays(xxx * 7);
3108                                    prevBranchId = appointment.BranchId;
3109                                    prevAccountId = appointment.account;
3110                                }
3111                                _context.tblIntroducerVisits.Add(objAppointment);
3112                                _context.SaveChanges();
3113
3114                                if (objAppointment.Id > 0)
3115                                {
3116                                    tblIntroducerVisitsStatu objStatus = new tblIntroducerVisitsStatu();
3117                                    objStatus.IntroducerVisitsId = objAppointment.Id;
3118                                    objStatus.Status = (int)IntroducerVisitStatusEnum.Pending;
3119                                    objStatus.CreatedBy = SessionHelper.UserId;
3120                                    objStatus.CreatedDate = DateTime.Now;
3121                                    _context.tblIntroducerVisitsStatus.Add(objStatus);
3122                                    _context.SaveChanges();
3123                                    if (objStatus.Id > 0)
3124                                    {
3125                                        var _introducerVisit = _context.tblIntroducerVisits.Where(x => x.Id == objAppointment.Id).FirstOrDefault();
3126                                        _introducerVisit.StatusId = objStatus.Id;
3127                                    }
3128
3129                                    string _connectionstring = Convert.ToString(ConfigurationManager.ConnectionStrings["ConString1"]);
3130                                    try
3131                                    {
3132                                        var _userId = SessionHelper.UserId;
3133                                        var _accountId = objAppointment.account;
3134
3135                                        string note = "";
3136                                        string DateOfVisit = objAppointment.VisitDate.HasValue ? objAppointment.VisitDate.Value.ToString("dd/MMMM/yyyy") : string.Empty;
3137                                        note = "A meeting is scheduled with Introducer " + _context.tblaccounts.Where(res => res.accountid == objAppointment.account).Select(res => res.companyname).FirstOrDefault() + " in branch " + _context.tblBranches.Where(x => x.Id == appointment.BranchId).Select(x => x.Branch).FirstOrDefault().ToString() + " on " + DateOfVisit + ".";
3138                                        // note = "A meeting is scheduled with Introducer " + _context.tblaccounts.Where(res => res.accountid == objAppointment.account).Select(res => res.companyname).FirstOrDefault() + " in branch " + _context.tblBranches.Where(x => x.Id == appointment.BranchId).Select(x => x.Branch).FirstOrDefault().ToString() + " on " + _VisitDate.ToString("dd/MMMM/yyyy") + ".";
3139                                        tblIntroducerVisitNote objNote = new tblIntroducerVisitNote();
3140                                        objNote.Account = _accountId;
3141                                        objNote.Note = note;
3142                                        objNote.Created = DateTime.Now;
3143                                        objNote.CreatedBy = _userId;
3144                                        _context.tblIntroducerVisitNotes.Add(objNote);
3145                                        _context.SaveChanges();
3146                                        if (objNote.Id > 0)
3147                                        {
3148                                            objAppointment.NoteId = objNote.Id;
3149                                            objStatus.NoteId = objNote.Id;
3150                                            _context.SaveChanges();
3151                                        }
3152
3153
3154                                    }
3155                                    catch (Exception ex)
3156                                    {
3157                                        Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails_AddNote", ex.Message);
3158
3159                                    }
3160
3161                                }
3162                            }
3163                            catch (Exception ex)
3164                            {
3165                                Helper.ErrorLog(ex.InnerException, "ReportController", "UpdateIntroducerAppointmentDetails", ex.Message);
3166
3167                            }
3168                        }
3169                    }
3170
3171                }
3172            }
3173            catch (Exception ex)
3174            {
3175                Helper.ErrorLog(ex.InnerException, "Q_Reports", "UpdateIntroducerAppointmentDetails", ex.Message);
3176
3177            }
3178            return true;
3179        }
3180
3181        #endregion [Introducer Visits]
3182
3183
3184
3185        /// <summary>
3186        /// Get data for London Market Screen Graph.
3187        /// </summary>
3188        /// <returns></returns>
3189        public DataSet GetLondonMarketScreenData()
3190        {
3191            DataSet ds;
3192            try
3193            {
3194                SqlParameter[] Param = new SqlParameter[0];
3195                ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_LondonMarketScreenData", Param);
3196            }
3197            catch (Exception ex)
3198            {
3199                Helper.ErrorLog(ex.InnerException, "Q_report", "sp_LondonMarketScreenData", ex.Message);
3200                ds = new DataSet();
3201            }
3202            return ds;
3203        }
3204
3205        #region Account Visits Report
3206
3207        public AccountVisitsReport GetAccountVisitsReport(List<int> companyIds, int userId, string brokers, List<int> BranchIds, DateTime FromDate, DateTime ToDate)
3208        {
3209            List<clsAccountVisits> accountVisits = new List<clsAccountVisits>();
3210            AccountVisitsReport _reportData = new AccountVisitsReport();
3211            DataSet dsIntroducerVisits;
3212            try
3213            {
3214
3215                SqlParameter[] Param = new SqlParameter[6];
3216                Param[0] = new SqlParameter("@CurrentUserId", userId);
3217                Param[1] = new SqlParameter("@BrokerId", brokers);
3218                DataTable dtCompanyIds = Helper.FillDataTable(companyIds);
3219                Param[2] = new SqlParameter("@CompanyIds", dtCompanyIds);
3220                DataTable dtBranchIds = Helper.FillDataTable(BranchIds);
3221                Param[3] = new SqlParameter("@BranchIds", dtBranchIds);
3222                Param[4] = new SqlParameter("@FromDate", FromDate.ToString("dd/MMM/yyyy"));
3223                Param[5] = new SqlParameter("@ToDate", ToDate.ToString("dd/MMM/yyyy"));
3224                dsIntroducerVisits = SqlHelper.ExecuteDataset("GetAccountVisitsReport", Param);
3225                if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[0].Rows.Count > 0)
3226                {
3227                    foreach (DataRow dr in dsIntroducerVisits.Tables[0].Rows)
3228                    {
3229                        var av = new clsAccountVisits();
3230                        av.Introducer = Convert.ToString(dr["CompanyName"]);
3231                        av.Branch = Convert.ToString(dr["Branch"]);
3232                        av.Adviser = Convert.ToString(dr["UserName"]);
3233                        av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
3234                        av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
3235                        av.TransferToAdviser = Convert.ToString(dr["TransferToAdviser"]);
3236                        av.TransferToId = Convert.ToInt32(Convert.ToString(dr["TransferToId"]));
3237                        IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
3238                        av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();
3239                        av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();
3240                        av.Status = Convert.ToString(statusEnum);
3241                        av.Note = Convert.ToString(dr["Notes"]);
3242                        accountVisits.Add(av);
3243                    }
3244                    accountVisits = accountVisits.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
3245                    _reportData.TotalAccountVisitsReport = accountVisits;
3246                    
3247                }
3248                //For Transferred
3249                if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[1].Rows.Count > 0)
3250                {
3251                    List<clsAccountVisits> accountVisitsTransfered = new List<clsAccountVisits>();
3252                    foreach (DataRow dr in dsIntroducerVisits.Tables[1].Rows)
3253                    {
3254                        var av = new clsAccountVisits();
3255                        av.Introducer = Convert.ToString(dr["CompanyName"]);
3256                        av.Branch = Convert.ToString(dr["Branch"]);
3257                        av.Adviser = Convert.ToString(dr["UserName"]);
3258                        av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
3259                        av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
3260                        av.TransferToAdviser = Convert.ToString(dr["TransferToAdviser"]);
3261                        av.TransferToId = Convert.ToInt32(Convert.ToString(dr["TransferToId"]));
3262                        IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
3263                        av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();// Convert.ToString(vfEnum);
3264                        av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();// Convert.ToString(dlEnum);
3265                        av.Status = Convert.ToString(statusEnum);
3266                        av.Note = Convert.ToString(dr["Notes"]);
3267                        accountVisitsTransfered.Add(av);
3268                    }
3269                    accountVisitsTransfered = accountVisitsTransfered.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
3270                    _reportData.TotalTransferVisitsReport = accountVisitsTransfered;
3271                }
3272
3273                //For Rejected
3274                if (dsIntroducerVisits != null && dsIntroducerVisits.Tables.Count > 0 && dsIntroducerVisits.Tables[2].Rows.Count > 0)
3275                {
3276                    List<clsAccountVisits> accountVisitsRejected = new List<clsAccountVisits>();
3277                    foreach (DataRow dr in dsIntroducerVisits.Tables[2].Rows)
3278                    {
3279                        var av = new clsAccountVisits();
3280                        av.Introducer = Convert.ToString(dr["CompanyName"]);
3281                        av.Branch = Convert.ToString(dr["Branch"]);
3282                        av.Adviser = Convert.ToString(dr["UserName"]);
3283                        av.AdviserId = Convert.ToInt32(Convert.ToString(dr["broker"]));
3284                        av.IntroducerVisitsId = Convert.ToInt32(Convert.ToString(dr["id"]));
3285                        av.RejectedOfAdviser = Convert.ToString(dr["RejectedOfAdviser"]);
3286                        IntroducerVisitStatusEnum statusEnum = (IntroducerVisitStatusEnum)Convert.ToInt32(Convert.ToString(dr["Status"]));
3287                        av.VisitFrequency = ((VisitFrequencyEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyType"]))).GetEnumDescription();// Convert.ToString(vfEnum);
3288                        av.VisitFrequencyDay = ((DayListEnum)Convert.ToInt32(Convert.ToString(dr["FrequencyDay"]))).GetEnumDescription();// Convert.ToString(dlEnum);
3289                        av.Status = Convert.ToString(statusEnum);
3290                        av.Note = Convert.ToString(dr["Notes"]);
3291                        accountVisitsRejected.Add(av);
3292                    }
3293                    accountVisitsRejected = accountVisitsRejected.GroupBy(item => new { Introducer = item.Introducer, Branch = item.Branch }).SelectMany(g => g.OrderByDescending(grp => grp.IntroducerVisitsId)).ToList();
3294                    _reportData.TotalRejectedVisitsReport = accountVisitsRejected;
3295                }
3296            }
3297            catch (Exception ex)
3298            {
3299                Helper.ErrorLog(ex.InnerException, "Q_Report", "GetAccountVisitsReport", ex.Message);
3300            }
3301            return _reportData;
3302        }
3303
3304
3305        #endregion Account Visits Report
3306
3307        /// <summary>
3308        /// Where my leads come from  
3309        /// </summary>
3310
3311        /// <returns></returns>
3312        public DataSet GetWhereMyLeadscomeFromResult(int loggedinuser, DateTime fromdate, DateTime todate)
3313        {
3314            DataSet ds;           
3315            try
3316            {
3317                SqlParameter[] Param = new SqlParameter[8];                                
3318                Param[0] = new SqlParameter("@CurrentUserId", loggedinuser);
3319                Param[1] = new SqlParameter("@Negotiator", null);
3320                Param[2] = new SqlParameter("@Company", null);
3321                Param[3] = new SqlParameter("@Branches", null);
3322                Param[4] = new SqlParameter("@BrokerId", null);              
3323                Param[5] = new SqlParameter("@FromDate", fromdate.ToString(Helper.ddMMMyyyy));
3324                Param[6] = new SqlParameter("@ToDate", todate.ToString(Helper.ddMMMyyyy));
3325                Param[7] = new SqlParameter("@productTypeID", 2);
3326                ds = SqlHelper.ExecuteDataset("sp_WhereMyLeadComesFrom", Param);
3327                return ds;
3328            }
3329            catch (Exception ex)
3330            {
3331                Helper.ErrorLog(ex.InnerException, "Q_Report.cs", "GetNegotiatorResult", ex.Message);
3332                return ds = new DataSet();
3333            }
3334        }
3335
3336
3337
3338    }
3339}
3340
3341using System;
3342using System.Collections.Generic;
3343using System.Linq;
3344using System.Web;
3345using Occfinance_Data;
3346using Occfinance.Models;
3347using Occfinance.Helpers;
3348using System.Data;
3349using System.Data.SqlClient;
3350
3351namespace Occfinance.Code
3352{
3353    public class Q_NewLead
3354    {
3355        public db_occfinance_5572Entities ctx = new db_occfinance_5572Entities();
3356
3357        System.Web.HttpContext con = HttpContext.Current;
3358
3359        #region Find contacts for account
3360
3361        public List<tblcontact> FindContactsForAccount(int accountid)
3362        {
3363            var cntlists = ctx.tblcontacts.Where(c => c.account.HasValue && c.account.Value == accountid).Select(c => c).ToList();
3364            return cntlists;
3365        }
3366
3367        #endregion
3368
3369        #region Save lead information to database
3370
3371        public bool SaveNewLead(tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, int mstatusid = 1, string Note = "", string mortage_date = "", string mortage_time = "", int subStatusId = 0)
3372        {
3373            bool result = false;
3374            try
3375            {
3376                #region [Add branch and negotiators 2015-11-02]
3377
3378                data.BranchId = GetBranchId(data.branch);
3379                data.NegrefId = GetNegotiatorId(data.negref);
3380                data.branch = string.Empty;
3381                data.negref = string.Empty;
3382
3383                #endregion [Add branch and negotiators 2015-11-02]
3384
3385                #region [Add userId and password for factfind user in Contact]
3386                int xx = 0;
3387                while (xx < 2)
3388                {
3389                    string userName = data.firstname+"-"+ Helper.GetRandomString(4,1);
3390                    if (!string.IsNullOrEmpty(userName))
3391                    {
3392                        if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
3393                        {
3394                            data.ContactUserName = userName;
3395                            data.ContactPassword= "OCCPWD-" + Helper.GetRandomString(6);
3396
3397                            #region send email to ContactUserName
3398
3399
3400                            #endregion send email to ContactUserName
3401
3402                            break; //Exit loop
3403                        }
3404                    }
3405                }
3406
3407
3408                #endregion  [Add userId and password for factfind user in Contact]
3409
3410                ctx.tblcontacts.Add(data);
3411                ctx.SaveChanges();
3412                int cntid = _contactid = data.contactid;
3413                if (cntid > 0)
3414                {
3415                    //Save Finance information to database
3416                    tblfinance dataFinance = new tblfinance();
3417                    dataFinance.contact = cntid;
3418                    dataFinance.status = mstatusid;
3419                    dataFinance.type = 1;
3420                    dataFinance.subFinanceStatus = subStatusId;
3421                    dataFinance.created = System.DateTime.Now;
3422
3423                    try
3424                    {
3425                        if (!string.IsNullOrWhiteSpace(mortage_date))
3426                            dataFinance.followup = Helper.GetFollowUp(mortage_date, string.IsNullOrWhiteSpace(mortage_time) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage_time);
3427                    }
3428                    catch (Exception ex)
3429                    {
3430                        Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
3431                    }
3432
3433                    ctx.tblfinances.Add(dataFinance);
3434                    ctx.SaveChanges();
3435                    int financeid = dataFinance.financeid;
3436                    if (financeid > 0)
3437                    {
3438                        //Save mortgage information to database
3439                        tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
3440
3441                        dataFinanceMortgage.financeid = financeid;
3442                        dataFinanceMortgage.purchase = dataM.purchase;
3443                        dataFinanceMortgage.rterm = dataM.rterm;
3444                        dataFinanceMortgage.deposit = dataM.deposit;
3445                        dataFinanceMortgage.income = dataM.income;
3446                        ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
3447                        ctx.SaveChanges();
3448                        //Add Note While get registered from New Lead page***********************
3449                        try
3450                        {
3451                            if (!string.IsNullOrWhiteSpace(Note))
3452                            {
3453                                int loggedinuserid = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
3454                                tblnote _note = new tblnote();
3455                                Q_Application _qA = new Q_Application();
3456                                _note.financeid = financeid;
3457                                _note.contact = cntid;
3458                                _note.note = Note;
3459                                _note.created = System.DateTime.Now;
3460                                _note.UserId = loggedinuserid;
3461                                ctx.tblnotes.Add(_note);
3462                                ctx.SaveChanges();
3463                            }
3464                        }
3465                        catch (Exception ex)
3466                        {
3467                            Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
3468                        }
3469                        //********************************
3470                    }
3471
3472                    if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
3473                    {
3474                        _objO.MainContactId = cntid;
3475                        ctx.tblOtherApplicants.Add(_objO);
3476                        ctx.SaveChanges();
3477                    }
3478
3479                    #region Insert to FactFind
3480                    string SLA = string.Empty;
3481                    if (data.slamet == true)
3482                        SLA = "1";
3483                    else
3484                        SLA = "0";
3485                    int userid = SessionHelper.UserId;
3486                    int ClientRegistrationID = 0;
3487                    Q_Client qClient = new Q_Client();
3488                    int chk= qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker??userid, userid, data.account ?? 0, cntid, SLA, data.method,out ClientRegistrationID);
3489
3490                    //insert Leaddata to factfind related table
3491                    SaveLeadDataForFactFind(userid, financeid, cntid,Note);
3492
3493                    #endregion Insert to FactFind
3494
3495
3496
3497                }
3498                result = true;
3499                return result;
3500            }
3501            catch (Exception ex)
3502            {
3503                _contactid = 0;
3504                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveNewLead", ex.Message);
3505                return result;
3506            }
3507        }
3508
3509
3510
3511        // Updated on 20-10-2014 for Individual Product TYpe Entry
3512        public bool SaveNewLeadForLife(tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, decimal amount = 0, decimal premium = 0, decimal commission = 0, string policyreference = "", int mstatusid = 1, int lstatusid = 10, string Note = "", string life_followupdate = "", string life_followuptime = "", bool IsPolicyForOtherApplicant = false)
3513        {
3514            bool result = false;
3515            try
3516            {
3517                #region [Add branch and negotiators 2015-11-02]
3518
3519                data.BranchId = GetBranchId(data.branch);
3520                data.NegrefId = GetNegotiatorId(data.negref);
3521                data.branch = string.Empty;
3522                data.negref = string.Empty;
3523
3524                #endregion [Add branch and negotiators 2015-11-02]
3525
3526                #region [Add userId and password for factfind user in Contact]
3527                int xx = 0;
3528                while (xx < 2)
3529                {
3530                    string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
3531                    if (!string.IsNullOrEmpty(userName))
3532                    {
3533                        if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
3534                        {
3535                            data.ContactUserName = userName;
3536                            data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
3537
3538                            #region send email to ContactUserName
3539
3540
3541                            #endregion send email to ContactUserName
3542
3543                            break; //Exit loop
3544                        }
3545                    }
3546                }
3547
3548
3549                #endregion  [Add userId and password for factfind user in Contact]
3550
3551                ctx.tblcontacts.Add(data);
3552                ctx.SaveChanges();
3553                int cntid = _contactid = data.contactid;
3554                if (cntid > 0)
3555                {
3556
3557
3558                    //Save Finance information to database Fopr Mortgage
3559                    tblfinance dataFinanceM = new tblfinance();
3560                    dataFinanceM.contact = cntid;
3561                    dataFinanceM.status = mstatusid;
3562                    dataFinanceM.type = 1;
3563                    dataFinanceM.created = System.DateTime.Now;
3564                    ctx.tblfinances.Add(dataFinanceM);
3565                    ctx.SaveChanges();
3566
3567                    int financeid = dataFinanceM.financeid;
3568                    if (financeid > 0)
3569                    {
3570                        //Save mortgage information to database
3571                        tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
3572
3573                        dataFinanceMortgage.financeid = financeid;
3574                        dataFinanceMortgage.purchase = dataM.purchase;
3575                        dataFinanceMortgage.rterm = dataM.rterm;
3576                        dataFinanceMortgage.deposit = dataM.deposit;
3577                        dataFinanceMortgage.income = dataM.income;
3578                        ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
3579                        ctx.SaveChanges();
3580
3581
3582                    }
3583
3584                    //Save Finance information to database For Life
3585                    tblfinance dataFinance = new tblfinance();
3586                    dataFinance.contact = cntid;
3587                    dataFinance.status = lstatusid; // For Life New Lead................
3588                    dataFinance.type = 2; // When finance type is Life
3589
3590                    //While Life ------------------
3591                    dataFinance.amount = amount;
3592                    dataFinance.commission = commission;
3593                    dataFinance.premium = premium;
3594                    dataFinance.policyreference = policyreference;
3595                    //added on 04-04-2015
3596                    dataFinance.isPolicyForOtherApplicant = IsPolicyForOtherApplicant;
3597
3598                    try
3599                    {
3600                        if (!string.IsNullOrWhiteSpace(life_followupdate))
3601                            dataFinance.followup = Helper.GetFollowUp(life_followupdate, string.IsNullOrWhiteSpace(life_followuptime) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life_followuptime);
3602
3603                    }
3604                    catch { }
3605
3606                    dataFinance.created = System.DateTime.Now;
3607                    ctx.tblfinances.Add(dataFinance);
3608                    ctx.SaveChanges();
3609
3610                    int financeidL = dataFinance.financeid;
3611                    if (financeidL > 0)
3612                    {
3613                        //Save To Life table************************************************************
3614                        tblfinance_insurance dataLife = new tblfinance_insurance();
3615                        dataLife.financeid = financeidL;
3616                        dataLife.smoker = false;
3617
3618
3619                        ctx.tblfinance_insurance.Add(dataLife);
3620                        ctx.SaveChanges();
3621                        //******************************************************************************
3622
3623                        //Add Note While get registered from New Lead page***********************
3624                        try
3625                        {
3626                            if (!string.IsNullOrWhiteSpace(Note))
3627                            {
3628                                int loggedinuserid = Convert.ToInt32(con.Session["LoggedInUserId"] ?? "0");
3629                                tblnote _note = new tblnote();
3630                                Q_Application _qA = new Q_Application();
3631                                //int _noteid = _qA.getNoteID();
3632                                //_note.noteid = _noteid;
3633                                _note.financeid = financeidL;
3634                                _note.contact = cntid;
3635                                _note.note = Note;
3636                                _note.created = System.DateTime.Now;
3637                                _note.UserId = loggedinuserid;
3638                                ctx.tblnotes.Add(_note);
3639                                ctx.SaveChanges();
3640                            }
3641                        }
3642                        catch { }
3643                        //********************************
3644                    }
3645
3646
3647                    if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
3648                    {
3649                        _objO.MainContactId = cntid;
3650                        ctx.tblOtherApplicants.Add(_objO);
3651                        ctx.SaveChanges();
3652                    }
3653
3654                    #region Insert to FactFind
3655                    string SLA = string.Empty;
3656                    if (data.slamet == true)
3657                        SLA = "1";
3658                    else
3659                        SLA = "0";
3660                    int userid = SessionHelper.UserId;
3661                    int ClientRegistrationID = 0;
3662                    Q_Client qClient = new Q_Client();
3663                    int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userid, userid, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
3664
3665
3666                    #endregion Insert to FactFind
3667
3668                }
3669                result = true;
3670                return result;
3671            }
3672            catch (Exception ex)
3673            {
3674                _contactid = 0;
3675                return result;
3676            }
3677        }
3678        // Updated on 20-10-2014 for Individual Product TYpe Entry
3679        #endregion
3680
3681        #region Save lead information to database For Mobile
3682
3683        public bool Mobile_SaveNewLead(int userId, tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, int mstatusid = 1, string Note = "", string mortage_date = "", string mortage_time = "")
3684        {
3685            bool result = false;
3686            try
3687            {
3688                #region [Add branch and negotiators 2015-11-02]
3689
3690                data.BranchId = GetBranchId(data.branch);
3691                data.NegrefId = GetNegotiatorId(data.negref);
3692                data.branch = string.Empty;
3693                data.negref = string.Empty;
3694
3695                #endregion [Add branch and negotiators 2015-11-02]
3696
3697                #region [Add userId and password for factfind user in Contact]
3698                int xx = 0;
3699                while (xx < 2)
3700                {
3701                    string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
3702                    if (!string.IsNullOrEmpty(userName))
3703                    {
3704                        if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
3705                        {
3706                            data.ContactUserName = userName;
3707                            data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
3708
3709                            #region send email to ContactUserName
3710
3711
3712                            #endregion send email to ContactUserName
3713
3714                            break; //Exit loop
3715                        }
3716                    }
3717                }
3718
3719
3720                #endregion  [Add userId and password for factfind user in Contact]
3721
3722                ctx.tblcontacts.Add(data);
3723                ctx.SaveChanges();
3724                int cntid = _contactid = data.contactid;
3725                if (cntid > 0)
3726                {
3727                    //Save Finance information to database
3728                    tblfinance dataFinance = new tblfinance();
3729                    dataFinance.contact = cntid;
3730                    dataFinance.status = mstatusid;
3731                    dataFinance.type = 1;
3732                    dataFinance.created = System.DateTime.Now;
3733
3734                    try
3735                    {
3736                        if (!string.IsNullOrWhiteSpace(mortage_date))
3737                            dataFinance.followup = Helper.GetFollowUp(mortage_date, string.IsNullOrWhiteSpace(mortage_time) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : mortage_time);
3738                    }
3739                    catch { }
3740
3741                    ctx.tblfinances.Add(dataFinance);
3742                    ctx.SaveChanges();
3743                    int financeid = dataFinance.financeid;
3744                    if (financeid > 0)
3745                    {
3746                        //Save mortgage information to database
3747                        tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
3748
3749                        dataFinanceMortgage.financeid = financeid;
3750                        dataFinanceMortgage.purchase = dataM.purchase;
3751                        dataFinanceMortgage.rterm = dataM.rterm;
3752                        dataFinanceMortgage.deposit = dataM.deposit;
3753                        dataFinanceMortgage.income = dataM.income;
3754                        ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
3755                        ctx.SaveChanges();
3756
3757
3758
3759                        //Add Note While get registered from New Lead page***********************
3760                        try
3761                        {
3762                            if (!string.IsNullOrWhiteSpace(Note))
3763                            {
3764                                int loggedinuserid = userId;
3765                                tblnote _note = new tblnote();
3766                                Q_Application _qA = new Q_Application();
3767                                //int _noteid = _qA.getNoteID();
3768                                //_note.noteid = _noteid;
3769                                _note.financeid = financeid;
3770                                _note.contact = cntid;
3771                                _note.note = Note;
3772                                _note.created = System.DateTime.Now;
3773                                _note.UserId = loggedinuserid;
3774                                ctx.tblnotes.Add(_note);
3775                                ctx.SaveChanges();
3776                            }
3777                        }
3778                        catch { }
3779                        //********************************
3780                    }
3781
3782                    if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
3783                    {
3784                        _objO.MainContactId = cntid;
3785                        ctx.tblOtherApplicants.Add(_objO);
3786                        ctx.SaveChanges();
3787                    }
3788
3789                    #region Insert to FactFind
3790                    string SLA = string.Empty;
3791                    if (data.slamet == true)
3792                        SLA = "1";
3793                    else
3794                        SLA = "0";
3795                    
3796                    int ClientRegistrationID = 0;
3797                    Q_Client qClient = new Q_Client();
3798                    int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
3799
3800                    //insert Leaddata to factfind related table
3801                    SaveLeadDataForFactFind(userId, financeid, cntid,Note);
3802
3803
3804                    #endregion Insert to FactFind
3805
3806                }
3807                result = true;
3808                return result;
3809            }
3810            catch (Exception ex)
3811            {
3812                _contactid = 0;
3813                return result;
3814            }
3815        }
3816
3817        public bool Mobile_SaveNewLeadForLife(int userId, tblcontact data, tblfinance_mortgage dataM, tblOtherApplicant _objO, out int _contactid, decimal amount = 0, decimal premium = 0, decimal commission = 0, string policyreference = "", int mstatusid = 1, int lstatusid = 10, string Note = "", string life_followupdate = "", string life_followuptime = "", bool IsPolicyForOtherApplicant = false)
3818        {
3819            bool result = false;
3820            try
3821            {
3822                #region [Add branch and negotiators 2015-11-02]
3823
3824                data.BranchId = GetBranchId(data.branch);
3825                data.NegrefId = GetNegotiatorId(data.negref);
3826                data.branch = string.Empty;
3827                data.negref = string.Empty;
3828
3829                #endregion [Add branch and negotiators 2015-11-02]
3830
3831                #region [Add userId and password for factfind user in Contact]
3832                int xx = 0;
3833                while (xx < 2)
3834                {
3835                    string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
3836                    if (!string.IsNullOrEmpty(userName))
3837                    {
3838                        if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
3839                        {
3840                            data.ContactUserName = userName;
3841                            data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
3842
3843                            #region send email to ContactUserName
3844
3845
3846                            #endregion send email to ContactUserName
3847
3848                            break; //Exit loop
3849
3850                        }
3851                    }
3852                }
3853
3854
3855                #endregion  [Add userId and password for factfind user in Contact]
3856
3857                ctx.tblcontacts.Add(data);
3858                ctx.SaveChanges();
3859                int cntid = _contactid = data.contactid;
3860                if (cntid > 0)
3861                {
3862
3863
3864                    //Save Finance information to database Fopr Mortgage
3865                    tblfinance dataFinanceM = new tblfinance();
3866                    dataFinanceM.contact = cntid;
3867                    dataFinanceM.status = mstatusid;
3868                    dataFinanceM.type = 1;
3869                    dataFinanceM.created = System.DateTime.Now;
3870                    ctx.tblfinances.Add(dataFinanceM);
3871                    ctx.SaveChanges();
3872
3873                    int financeid = dataFinanceM.financeid;
3874                    if (financeid > 0)
3875                    {
3876                        //Save mortgage information to database
3877                        tblfinance_mortgage dataFinanceMortgage = new tblfinance_mortgage();
3878
3879                        dataFinanceMortgage.financeid = financeid;
3880                        dataFinanceMortgage.purchase = dataM.purchase;
3881                        dataFinanceMortgage.rterm = dataM.rterm;
3882                        dataFinanceMortgage.deposit = dataM.deposit;
3883                        dataFinanceMortgage.income = dataM.income;
3884                        ctx.tblfinance_mortgage.Add(dataFinanceMortgage);
3885                        ctx.SaveChanges();
3886
3887
3888                    }
3889
3890                    //Save Finance information to database For Life
3891                    tblfinance dataFinance = new tblfinance();
3892                    dataFinance.contact = cntid;
3893                    dataFinance.status = lstatusid; // For Life New Lead................
3894                    dataFinance.type = 2; // When finance type is Life
3895
3896                    //While Life ------------------
3897                    dataFinance.amount = amount;
3898                    dataFinance.commission = commission;
3899                    dataFinance.premium = premium;
3900                    dataFinance.policyreference = policyreference;
3901                    //added on 04-04-2015
3902                    dataFinance.isPolicyForOtherApplicant = IsPolicyForOtherApplicant;
3903                    try
3904                    {
3905                        if (!string.IsNullOrWhiteSpace(life_followupdate))
3906                            dataFinance.followup = Helper.GetFollowUp(life_followupdate, string.IsNullOrWhiteSpace(life_followuptime) ? (DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second).ToString() : life_followuptime);
3907
3908                    }
3909                    catch { }
3910
3911                    dataFinance.created = System.DateTime.Now;
3912                    ctx.tblfinances.Add(dataFinance);
3913                    ctx.SaveChanges();
3914
3915                    int financeidL = dataFinance.financeid;
3916                    if (financeidL > 0)
3917                    {
3918                        //Save To Life table************************************************************
3919                        tblfinance_insurance dataLife = new tblfinance_insurance();
3920                        dataLife.financeid = financeidL;
3921                        dataLife.smoker = false;
3922
3923
3924                        ctx.tblfinance_insurance.Add(dataLife);
3925                        ctx.SaveChanges();
3926                        //******************************************************************************
3927
3928                        //Add Note While get registered from New Lead page***********************
3929                        try
3930                        {
3931                            if (!string.IsNullOrWhiteSpace(Note))
3932                            {
3933                                int loggedinuserid = userId;
3934                                tblnote _note = new tblnote();
3935                                Q_Application _qA = new Q_Application();
3936                                //int _noteid = _qA.getNoteID();
3937                                //_note.noteid = _noteid;
3938                                _note.financeid = financeidL;
3939                                _note.contact = cntid;
3940                                _note.note = Note;
3941                                _note.created = System.DateTime.Now;
3942                                _note.UserId = loggedinuserid;
3943                                ctx.tblnotes.Add(_note);
3944                                ctx.SaveChanges();
3945                            }
3946                        }
3947                        catch { }
3948                        //********************************
3949                    }
3950
3951
3952                    if (_objO != null && (!string.IsNullOrWhiteSpace(_objO.FirstName) || !string.IsNullOrWhiteSpace(_objO.SurName) || !string.IsNullOrWhiteSpace(_objO.Email) || !string.IsNullOrWhiteSpace(_objO.MobileNumber)))
3953                    {
3954                        _objO.MainContactId = cntid;
3955                        ctx.tblOtherApplicants.Add(_objO);
3956                        ctx.SaveChanges();
3957                    }
3958
3959                    #region Insert to FactFind
3960                    string SLA = string.Empty;
3961                    if (data.slamet == true)
3962                        SLA = "1";
3963                    else
3964                        SLA = "0";
3965                    
3966                    int ClientRegistrationID = 0;
3967                    Q_Client qClient = new Q_Client();
3968                    int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, cntid, SLA, data.method, out ClientRegistrationID);
3969
3970
3971                    #endregion Insert to FactFind
3972
3973                }
3974                result = true;
3975                return result;
3976            }
3977            catch (Exception ex)
3978            {
3979                _contactid = 0;
3980                return result;
3981            }
3982        }
3983
3984        #endregion
3985
3986        #region [Add/Fetch Branch and Negotiator 2015-11-02]
3987
3988        /// <summary>
3989        /// Get negotiator id on the basis of negotiator name. If not already exist then add a new negotiator.
3990        /// </summary>
3991        /// <param name="negotiatorName"></param>
3992        /// <returns></returns>
3993        public int GetNegotiatorId(string negotiatorName)
3994        {
3995            if (string.IsNullOrEmpty(negotiatorName))
3996                return 0;
3997            var negotiatorId = 0;
3998            try
3999            {
4000                negotiatorId = ctx.tblNegotiators.Where(negt => negt.Negotiator == negotiatorName).Select(negt => negt.Id).FirstOrDefault();
4001                if (negotiatorId == 0)
4002                {
4003                    tblNegotiators negt = new tblNegotiators();
4004                    negt.Negotiator = negotiatorName;
4005                    ctx.tblNegotiators.Add(negt);
4006                    ctx.SaveChanges();
4007                    negotiatorId = negt.Id;
4008                }
4009            }
4010            catch (Exception ex)
4011            {
4012                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetNegotiatorId", ex.Message);
4013            }
4014            return negotiatorId;
4015        }
4016
4017        /// <summary>
4018        /// Get branch id on the basis of branch name. add new branch if it does not already exist.
4019        /// </summary>
4020        /// <param name="branchName"></param>
4021        /// <returns></returns>
4022        public int GetBranchId(string branchName)
4023        {
4024            if (string.IsNullOrEmpty(branchName))
4025                return 0;
4026            var branchId = 0;
4027            try
4028            {
4029                branchId = ctx.tblBranches.Where(brn => brn.Branch == branchName).Select(brn => brn.Id).FirstOrDefault();
4030                if (branchId == null || branchId == 0)
4031                {
4032                    tblBranches objBranch = new tblBranches();
4033                    objBranch.Branch = branchName;
4034                    ctx.tblBranches.Add(objBranch);
4035                    ctx.SaveChanges();
4036                    branchId = objBranch.Id;
4037                }
4038            }
4039            catch (Exception ex)
4040            {
4041                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchId", ex.Message);
4042            }
4043            return branchId;
4044        }
4045
4046        /// <summary>
4047        /// Get branch names on the basis of branch ids.
4048        /// </summary>
4049        /// <param name="contactlists"></param>
4050        /// <returns></returns>
4051        public List<string> GetBranchNamesFromBranchIds(List<tblcontact> contactlists)
4052        {
4053            List<string> branchLists = new List<string>();
4054            var branchIds = contactlists.Select(brn => brn.BranchId);
4055            try
4056            {
4057                branchLists = ctx.tblBranches.Where(brn => branchIds.Contains(brn.Id)).Select(brn => brn.Branch).Distinct().ToList();
4058            }
4059            catch (Exception ex)
4060            {
4061                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchNamesFromBranchIds", ex.Message);
4062            }
4063            return branchLists;
4064        }
4065
4066        /// <summary>
4067        /// Get negotiator names on the basis of negotiator ids.
4068        /// </summary>
4069        /// <param name="contactlists"></param>
4070        /// <returns></returns>
4071        public List<string> GetNegotiatorNamesFromNegotiatorIds(List<tblcontact> contactlists)
4072        {
4073            List<string> negotitorLists = new List<string>();
4074            var negotiatorIds = contactlists.Select(brn => brn.NegrefId);
4075            try
4076            {
4077                negotitorLists = ctx.tblNegotiators.Where(negt => negotiatorIds.Contains(negt.Id)).Select(negt => negt.Negotiator).Distinct().ToList();
4078            }
4079            catch (Exception ex)
4080            {
4081                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GetBranchNamesFromBranchIds", ex.Message);
4082            }
4083            return negotitorLists;
4084        }
4085
4086
4087        public DataSet GetBranchAndNegotiatorByAccountId(int accountId)
4088        {
4089            SqlParameter[] Param = new SqlParameter[1];
4090            Param[0] = new SqlParameter("@accountId", accountId);
4091            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetBranchAndNegotiatorByAccountId", Param);
4092            return ds;
4093        }
4094
4095
4096        #endregion [Add/Fetch Branch and Negotiator 2015-11-02]
4097
4098        #region Insert Lead data to FactFind 
4099       public void SaveLeadDataForFactFind(int userId,int financeId,int contactId,string note="")
4100        {
4101            try
4102            {                
4103                SqlParameter[] Param = new SqlParameter[4];
4104                Param[0] = new SqlParameter("@userId", userId);
4105                Param[1] = new SqlParameter("@contactId", contactId);
4106                Param[2] = new SqlParameter("@financeId", financeId);                
4107                Param[3] = new SqlParameter("@note", note);
4108
4109                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SaveNewLeadDataForFactFind", Param);
4110            }
4111            catch(Exception ex)
4112            {
4113                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SaveLeadDataForFactFind", ex.Message);
4114            }
4115        }
4116
4117
4118        #endregion
4119
4120
4121        #region Send/Generate factfind
4122        public List<string> GenerateFactFindLink(int contactId, int userId)
4123        {
4124            List<string> lst = new List<string>();
4125            try
4126            {
4127                var data = ctx.tblcontacts.Where(x => x.contactid == contactId).FirstOrDefault();
4128                var objFinance = ctx.tblfinances.Where(x => x.contact == contactId).OrderBy(x => x.financeid).First();
4129                #region [Add userId and password for factfind user in Contact]
4130                int xx = 0;
4131                while (xx < 2)
4132                {
4133                    string userName = data.firstname + "-" + Helper.GetRandomString(4, 1);
4134                    if (!string.IsNullOrEmpty(userName))
4135                    {
4136                        if (!ctx.tblcontacts.Where(x => x.ContactUserName == userName).Any())
4137                        {
4138                            data.ContactUserName = userName;
4139                            data.ContactPassword = "OCCPWD-" + Helper.GetRandomString(6);
4140                            lst.Add(userName);
4141                            lst.Add(data.ContactPassword);
4142
4143                            ctx.SaveChanges();
4144                            #region send email to ContactUserName
4145
4146
4147                            #endregion send email to ContactUserName
4148                            xx = 3;
4149                            break; //Exit loop
4150                        }
4151                    }
4152                }
4153
4154
4155                #endregion  [Add userId and password for factfind user in Contact]
4156                #region Insert to FactFind
4157                string SLA = string.Empty;
4158                if (data.slamet == true)
4159                    SLA = "1";
4160                else
4161                    SLA = "0";
4162
4163                int ClientRegistrationID = 0;
4164                Q_Client qClient = new Q_Client();
4165                int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, contactId, SLA, data.method, out ClientRegistrationID);
4166
4167                //insert Leaddata to factfind related table
4168                SaveLeadDataForFactFind(userId, objFinance.financeid, data.contactid, "");
4169
4170                #endregion Insert to FactFind
4171            }
4172            catch (Exception ex)
4173            {
4174                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "GenerateFactFindLink", ex.Message);
4175            }
4176            return lst;
4177        }
4178
4179        public int SendFactfindLink(int contactId, int userId)
4180        {
4181            int result = 0;
4182            var data = ctx.tblcontacts.Where(x => x.contactid == contactId).FirstOrDefault();
4183            var objFinance = ctx.tblfinances.Where(x => x.contact == contactId).OrderBy(x => x.financeid).First();
4184            try
4185            {
4186                #region Insert to FactFind
4187                string SLA = string.Empty;
4188                if (data.slamet == true)
4189                    SLA = "1";
4190                else
4191                    SLA = "0";
4192
4193                int ClientRegistrationID = 0;
4194                Q_Client qClient = new Q_Client();
4195                int chk = qClient.RegisterClient(data.firstname, data.lastname, data.email, data.telephone, data.broker ?? userId, userId, data.account ?? 0, contactId, SLA, data.method, out ClientRegistrationID);
4196
4197                //insert Leaddata to factfind related table
4198                SaveLeadDataForFactFind(userId, objFinance.financeid, data.contactid, "");
4199                result = 1;
4200                #endregion Insert to FactFind
4201            }
4202            catch (Exception ex)
4203            {
4204                Helper.ErrorLog(ex.InnerException, "Q_NewLead", "SendFactfindLink", ex.Message);
4205                result = -1;
4206            }
4207            return result;
4208        }
4209
4210        #endregion
4211    }
4212}
4213
4214using System;
4215using System.Collections.Generic;
4216using System.Linq;
4217using System.Web;
4218using System.Data;
4219using System.Data.SqlClient;
4220using Occfinance.Models;
4221using Occfinance_Data;
4222using System.Reflection;
4223using Occfinance.Helpers;
4224
4225/*****************************************************
4226Operations for MortgageFactFind
4227******************************************************/
4228namespace Occfinance.Code
4229{
4230    public class Q_MortgageFactFind
4231    {
4232        SqlConnection connection;
4233        public Q_MortgageFactFind()
4234        { connection = new SqlConnection(SqlHelper.ConnectionString()); }
4235
4236        // Add Client Registration
4237        public int AddClientRegistration(tblClientRegistration clientRegistration, bool useTransaction, SqlTransaction tran, SqlConnection connect)
4238        {
4239            int completed = 0;
4240            string proc = (clientRegistration.ClientRegistrationID == 0 ? "tblInsertClientRegister" : "tblUpdateClientRegister");
4241            SqlParameter[] clientRegister = new SqlParameter[44];
4242            clientRegister[0] = new SqlParameter("@ClientName", clientRegistration.ClientName);
4243            clientRegister[1] = new SqlParameter("@AdviserName", clientRegistration.AdviserName);
4244            clientRegister[2] = new SqlParameter("@DateCompleted", !string.IsNullOrWhiteSpace(clientRegistration.StrDateCompleted) ? Helper.GetDate(clientRegistration.StrDateCompleted) : null);
4245            clientRegister[3] = new SqlParameter("@ReferId", clientRegistration.ReferId);
4246            clientRegister[4] = new SqlParameter("@method", clientRegistration.method);
4247            clientRegister[5] = new SqlParameter("@slamet", clientRegistration.slamet);
4248            clientRegister[6] = new SqlParameter("@Title", clientRegistration.Title);
4249            clientRegister[7] = new SqlParameter("@Forename", clientRegistration.Forename);
4250            clientRegister[8] = new SqlParameter("@Surname", clientRegistration.Surname);
4251            clientRegister[9] = new SqlParameter("@PreviousSurname", clientRegistration.PreviousSurname);
4252            clientRegister[10] = new SqlParameter("@Gender", clientRegistration.Gender);
4253            clientRegister[11] = new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(clientRegistration.StrDOB) ? Helper.GetDate(clientRegistration.StrDOB) : null);
4254            clientRegister[12] = new SqlParameter("@PassportHeld", clientRegistration.PassportHeld);
4255            clientRegister[13] = new SqlParameter("@VisaStatus", clientRegistration.VisaStatus);
4256            clientRegister[14] = new SqlParameter("@MaritalStatus", clientRegistration.MaritalStatus);
4257            clientRegister[15] = new SqlParameter("@DoYouSmoke", clientRegistration.DoYouSmoke);
4258            clientRegister[16] = new SqlParameter("@InGoodHealth", clientRegistration.InGoodHealth);
4259            clientRegister[17] = new SqlParameter("@Dependants", clientRegistration.Dependants);
4260            clientRegister[18] = new SqlParameter("@FullHomeAddress", clientRegistration.FullHomeAddress);
4261            clientRegister[19] = new SqlParameter("@DateMovedToAddress", !string.IsNullOrWhiteSpace(clientRegistration.StrDateMovedToAddress) ? Helper.GetDate(clientRegistration.StrDateMovedToAddress) : null);
4262            clientRegister[20] = new SqlParameter("@PreviousAddress", clientRegistration.PreviousAddress);
4263            clientRegister[21] = new SqlParameter("@DateMovedToThisAddress", !string.IsNullOrWhiteSpace(clientRegistration.StrDateMovedToThisAddress) ? Helper.GetDate(clientRegistration.StrDateMovedToThisAddress) : null);
4264            clientRegister[22] = new SqlParameter("@ResidentialStatus", clientRegistration.ResidentialStatus);
4265            clientRegister[23] = new SqlParameter("@HomeNumber", clientRegistration.HomeNumber);
4266            clientRegister[24] = new SqlParameter("@MobileNumber", clientRegistration.MobileNumber);
4267            clientRegister[25] = new SqlParameter("@WorkNumber", clientRegistration.WorkNumber);
4268            clientRegister[26] = new SqlParameter("@EmailAddress", clientRegistration.EmailAddress);
4269            clientRegister[27] = new SqlParameter("@AdditionalInfo", clientRegistration.AdditionalInfo);
4270            clientRegister[28] = new SqlParameter("@AreYouEmployed", clientRegistration.AreYouEmployed);
4271            clientRegister[29] = new SqlParameter("@IsActive", clientRegistration.IsActive);
4272            clientRegister[30] = new SqlParameter("@IsDeleted", clientRegistration.IsDeleted);
4273            //if (clientRegistration.ClientRegistrationID == 0)
4274            clientRegister[31] = new SqlParameter("@AddDate", DateTime.Now);
4275            clientRegister[32] = new SqlParameter("@EntryBy", clientRegistration.EntryBy);
4276            //if (clientRegistration.ClientRegistrationID !=0)
4277            clientRegister[33] = new SqlParameter("@UpdateDate", DateTime.Now);
4278            clientRegister[34] = new SqlParameter("@UpdateBy", clientRegistration.UpdateBy);
4279            clientRegister[35] = new SqlParameter("@result", SqlDbType.Int);
4280            clientRegister[35].Direction = ParameterDirection.Output;
4281
4282            clientRegister[36] = new SqlParameter("@ClientRegistrationID", clientRegistration.ClientRegistrationID);
4283            clientRegister[37] = new SqlParameter("@PassportHeldOther", clientRegistration.PassportHeldOther);
4284            clientRegister[38] = new SqlParameter("@NINumber", clientRegistration.NINumber);
4285            clientRegister[39] = new SqlParameter("@AnticipatedRetirementAge", clientRegistration.AnticipatedRetirementAge);
4286            clientRegister[40] = new SqlParameter("@Retired", clientRegistration.Retired ?? false);
4287            clientRegister[41] = new SqlParameter("@HasReference", clientRegistration.HasReference);
4288            clientRegister[42] = new SqlParameter("@ValidityDate", !string.IsNullOrWhiteSpace(clientRegistration.StrValidityDate) ? Helper.GetDate(clientRegistration.StrValidityDate) : null);
4289            clientRegister[43] = new SqlParameter("@PassportType", clientRegistration.PassportType);
4290            if (useTransaction)
4291                SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, connect, clientRegister);
4292            else
4293                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, clientRegister);
4294            // return 1 if executed successfully
4295            completed = Convert.ToInt32(clientRegister[35] != null ? clientRegister[35].SqlValue.ToString() : "0");
4296
4297            return completed;
4298        }
4299
4300        // Created on 23/01/2015
4301        //Add and Update address in client registration
4302        public int AddAddressClientRegistration(AddressInfo address, bool useTransaction, SqlTransaction tran, SqlConnection connect)
4303        {
4304            int completed = 0;
4305
4306            SqlParameter[] clientreg_address = new SqlParameter[]
4307            {
4308            new SqlParameter("@FactFindAddressID", address.FactFindAddressID),
4309            new SqlParameter("@AddressLine1", address.AddressLine1),
4310            new SqlParameter("@AddressLine2", address.AddressLine2),
4311            new SqlParameter("@AddressLine3", address.AddressLine3),
4312            new SqlParameter("@AddressLine4", address.AddressLine4),
4313            new SqlParameter("@PostCode", address.PostCode),
4314            new SqlParameter("@County", address.County),
4315            new SqlParameter("@Country", address.Country),
4316            new SqlParameter("@NameOfEmployer", address.NameOfEmployer),
4317            new SqlParameter("@EmployerAddress", address.EmployerAddress),
4318            new SqlParameter("@Year", address.Year),
4319            new SqlParameter("@ResidentialStatus", address.ResidentialStatus),
4320            new SqlParameter("@HomeNumber", address.HomeNumber),
4321            new SqlParameter("@WorkNumber", address.WorkNumber),
4322            new SqlParameter("@DateMovedToAddress",!string.IsNullOrWhiteSpace(address.StrDateMovedToAddress) ? Helper.GetDate(address.StrDateMovedToAddress) : null),           
4323            new SqlParameter("@IsDeleted", false),
4324            new SqlParameter("@ApplicantID",address.ApplicantID),
4325            new SqlParameter("@result", SqlDbType.Int){Direction=ParameterDirection.Output}
4326            };
4327
4328            if (useTransaction)
4329                SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "FactFind_AddUpdateAddress", tran, connect, clientreg_address);
4330            else
4331                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_AddUpdateAddress", clientreg_address);
4332            // return 1 if executed successfully
4333            completed = Convert.ToInt32(clientreg_address[17] != null ? clientreg_address[17].SqlValue.ToString() : "0");
4334
4335            return completed;
4336        }
4337
4338        public DataSet GetAddressList(int applicantid)
4339        {
4340            SqlParameter[] Param = new SqlParameter[1];
4341            Param[0] = new SqlParameter("@ApplicantID", applicantid);
4342
4343            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetAddressByApplicantID", Param);
4344            return ds;
4345        }
4346
4347        // get dependents
4348        public DataSet GetDependentList(int applicantid)
4349        {
4350            SqlParameter[] Param = new SqlParameter[1];
4351            Param[0] = new SqlParameter("@ApplicantID", applicantid);
4352
4353            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetDependentsByApplicantID", Param);
4354            return ds;
4355        }
4356
4357        // Add Employement : Generic method for add and update.
4358        // Add Date and Entry by parameters will be ignord while update
4359
4360        public int AddEmployement(tblEmployment employement, bool useTransaction, SqlTransaction tran, SqlConnection con)
4361        {
4362            int completed = 0;
4363
4364            string proc = (employement.EmploymentId == 0 ? "tblInsertEmployment" : "tblUpdateEmployment");
4365
4366            SqlParameter[] emp = new SqlParameter[13];
4367            emp[0] = new SqlParameter("@EmploymentId", employement.EmploymentId);
4368            emp[1] = new SqlParameter("@Year", employement.Year);
4369            emp[2] = new SqlParameter("@NameOfEmployer", employement.NameOfEmployer);
4370            emp[3] = new SqlParameter("@EmployerAddress", employement.EmployerAddress);
4371            emp[4] = new SqlParameter("@IsActive", employement.IsActive);
4372            emp[5] = new SqlParameter("@IsDeleted", employement.IsDeleted);
4373            emp[6] = new SqlParameter("@AddDate", DateTime.Now);
4374            emp[7] = new SqlParameter("@EntryBy", employement.EntryBy);
4375            emp[8] = new SqlParameter("@UpdateDate", DateTime.Now);
4376            emp[9] = new SqlParameter("@UpdateBy", employement.UpdateBy);
4377            emp[10] = new SqlParameter("@result", SqlDbType.Int);
4378            emp[10].Direction = ParameterDirection.Output;
4379
4380            // new parameters
4381            emp[11] = new SqlParameter("@ClientRegistrationID", employement.ClientRegistrationID);
4382            emp[12] = new SqlParameter("@ReferId", employement.ReferId);
4383
4384            if (useTransaction)
4385            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, emp); }
4386            else
4387            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, emp); }
4388            // return 1 if executed successfully
4389
4390            completed = Convert.ToInt32(emp[10] != null ? emp[10].SqlValue.ToString() : "0");
4391
4392
4393            return completed;
4394        }
4395
4396
4397        // Add EmploymentAndIncome
4398
4399        public int AddEmploymentAndIncome(tblEmploymentAndIncome employmentAndIncome, bool useTransaction, SqlTransaction tran, SqlConnection con)
4400        {
4401            int completed;
4402            string proc = (employmentAndIncome.EmploymentIncomeId == 0 ? "tblInsertEmploymentAndIncome" : "tblUpdateEmploymentAndIncome");
4403            
4404            SqlParameter[] empandInc = new SqlParameter[39];
4405            empandInc[0] = new SqlParameter("@ClientRegistrationID", employmentAndIncome.ClientRegistrationID);
4406            empandInc[1] = new SqlParameter("@JobTitle", employmentAndIncome.JobTitle);
4407            empandInc[2] = new SqlParameter("@NatureOfOccupation", employmentAndIncome.NatureOfOccupation);
4408            empandInc[3] = new SqlParameter("@NINumber", employmentAndIncome.NINumber);
4409            empandInc[4] = new SqlParameter("@AnticipatedRetirementAge", employmentAndIncome.AnticipatedRetirementAge);
4410            empandInc[5] = new SqlParameter("@EmploymentStatus", employmentAndIncome.EmploymentStatus);
4411            empandInc[6] = new SqlParameter("@ProbationPeriod", employmentAndIncome.ProbationPeriod);
4412            empandInc[7] = new SqlParameter("@NameOfEmployer", employmentAndIncome.NameOfEmployer);
4413            empandInc[8] = new SqlParameter("@AddressOfEmployer", employmentAndIncome.AddressOfEmployer);
4414            empandInc[9] = new SqlParameter("@AnnualCommission", employmentAndIncome.AnnualCommission);
4415            empandInc[10] = new SqlParameter("@AnnualOvertime", employmentAndIncome.AnnualOvertime);
4416            empandInc[11] = new SqlParameter("@Other", employmentAndIncome.Other);
4417            empandInc[12] = new SqlParameter("@GrossTotal", employmentAndIncome.GrossTotal);
4418            empandInc[13] = new SqlParameter("@GrossMonthly", employmentAndIncome.GrossMonthly);
4419            empandInc[14] = new SqlParameter("@IsActive", employmentAndIncome.IsActive);
4420            empandInc[15] = new SqlParameter("@IsDeleted", employmentAndIncome.IsDeleted);
4421            empandInc[16] = new SqlParameter("@AddDate", DateTime.Now);
4422            empandInc[17] = new SqlParameter("@EntryBy", employmentAndIncome.EntryBy);
4423            empandInc[18] = new SqlParameter("@UpdateDate", DateTime.Now);
4424            empandInc[19] = new SqlParameter("@UpdateBy", employmentAndIncome.UpdateBy);
4425            empandInc[20] = new SqlParameter("@GrossBasicSalary", employmentAndIncome.GrossBasicSalary);
4426            empandInc[21] = new SqlParameter("@PeriodWithEmployer", employmentAndIncome.PeriodWithEmployer);
4427
4428
4429            empandInc[22] = new SqlParameter("@result", SqlDbType.Int);
4430            empandInc[22].Direction = ParameterDirection.Output;
4431
4432            // add id column
4433            empandInc[23] = new SqlParameter("@EmploymentIncomeId", employmentAndIncome.EmploymentIncomeId);
4434            empandInc[24] = new SqlParameter("@ReferId", employmentAndIncome.ReferId);
4435            // bonus
4436            empandInc[25] = new SqlParameter("@BonusY1", employmentAndIncome.BonusY1);
4437            empandInc[26] = new SqlParameter("@BonusY2", employmentAndIncome.BonusY2);
4438            empandInc[27] = new SqlParameter("@BonusY3", employmentAndIncome.BonusY3);
4439            empandInc[28] = new SqlParameter("@PreviousAddressOfEmployer", employmentAndIncome.PreviousAddressOfEmployer);
4440            empandInc[29] = new SqlParameter("@AreYouEmployed", employmentAndIncome.AreYouEmployed);
4441            empandInc[30] = new SqlParameter("@EmploymentStartDate", !string.IsNullOrWhiteSpace(employmentAndIncome.StrEmploymentStartDate) ? Helper.GetDate(employmentAndIncome.StrEmploymentStartDate) : null);
4442            empandInc[31] = new SqlParameter("@EmploymentEndDate", !string.IsNullOrWhiteSpace(employmentAndIncome.StrEmploymentEndDate) ? Helper.GetDate(employmentAndIncome.StrEmploymentEndDate) : null);
4443            empandInc[32] = new SqlParameter("@PostcodeofEmployer", employmentAndIncome.PostcodeofEmployer);
4444            empandInc[33] = new SqlParameter("@CountryofEmployer", employmentAndIncome.CountryofEmployer);
4445            empandInc[34] = new SqlParameter("@CountyofEmployer", employmentAndIncome.CountyofEmployer);
4446            empandInc[35] = new SqlParameter("@OtherNatureofOccupation", employmentAndIncome.OtherNatureofOccupation);
4447            empandInc[36] = new SqlParameter("@OtherEmploymentstatus", employmentAndIncome.OtherEmploymentstatus);
4448            empandInc[37] = new SqlParameter("@CurrentJob", employmentAndIncome.CurrentJob);
4449            empandInc[38] = new SqlParameter("@ProbationPeriodDetails", employmentAndIncome.ProbationPeriodDetails);
4450            if (useTransaction)
4451            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empandInc); }
4452            else
4453            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empandInc); }
4454
4455            // return 1 if executed successfully
4456            completed = Convert.ToInt32(empandInc[22] != null ? empandInc[22].SqlValue.ToString() : "0");
4457
4458
4459            return completed;
4460
4461        }
4462
4463        // Add PayslipDeduction
4464
4465        public int AddPayslipDeduction(tblPayslipDeduction payslipDeduction, bool useTransaction, SqlTransaction tran, SqlConnection con)
4466        {
4467            int completed;
4468
4469            string proc = (payslipDeduction.PayslipDeductionId == 0 ? "tblInsertPayslipDeduction" : "tblUpdatetPayslipDeduction");
4470
4471            SqlParameter[] payslip = new SqlParameter[21];
4472            payslip[0] = new SqlParameter("@ClientRegistrationID", payslipDeduction.ClientRegistrationID);
4473            payslip[1] = new SqlParameter("@PensionContributions", payslipDeduction.PensionContributions);
4474            payslip[2] = new SqlParameter("@EmployerShareSaveSchemes", payslipDeduction.EmployerShareSaveSchemes);
4475            payslip[3] = new SqlParameter("@ChildcareVouchers", payslipDeduction.ChildcareVouchers);
4476            payslip[4] = new SqlParameter("@GymMembership", payslipDeduction.GymMembership);
4477            payslip[5] = new SqlParameter("@OtherBenefits", payslipDeduction.OtherBenefits);
4478            payslip[6] = new SqlParameter("@TransportBikeLoans", payslipDeduction.TransportBikeLoans);
4479            payslip[7] = new SqlParameter("@StudentLoanPayment", payslipDeduction.StudentLoanPayment);
4480            payslip[8] = new SqlParameter("@TotalNetMonthly", payslipDeduction.TotalNetMonthly);
4481            payslip[9] = new SqlParameter("@IsActive", payslipDeduction.IsActive);
4482            payslip[10] = new SqlParameter("@IsDeleted", payslipDeduction.IsDeleted);
4483            payslip[11] = new SqlParameter("@AddDate", DateTime.Now);
4484            payslip[12] = new SqlParameter("@EntryBy", payslipDeduction.EntryBy);
4485            payslip[13] = new SqlParameter("@UpdateDate", DateTime.Now);
4486            payslip[14] = new SqlParameter("@UpdateBy", payslipDeduction.UpdateBy);
4487            payslip[15] = new SqlParameter("@result", SqlDbType.Int);
4488            payslip[15].Direction = ParameterDirection.Output;
4489
4490            // id
4491            payslip[16] = new SqlParameter("@PayslipDeductionId", payslipDeduction.PayslipDeductionId);
4492            payslip[17] = new SqlParameter("@ReferId", payslipDeduction.ReferId);
4493            payslip[18] = new SqlParameter("@FromDate", payslipDeduction.FromDate);
4494            payslip[19] = new SqlParameter("@ToDate", payslipDeduction.ToDate);
4495            payslip[20] = new SqlParameter("@AdditionalInfo", payslipDeduction.AdditionalInfo);
4496            if (useTransaction)
4497            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, payslip); }
4498            else
4499            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, payslip); }
4500
4501            // return 1 if executed successfully
4502            completed = Convert.ToInt32(payslip[15] != null ? payslip[15].SqlValue.ToString() : "0");
4503
4504
4505
4506
4507
4508            return completed;
4509        }
4510
4511
4512        // Add SelfEmployed
4513        // tested on :18/10/2014
4514        public int AddSelfEmployed(tblSelfEmployed selfEmployed, bool useTransaction, SqlTransaction tran, SqlConnection con)
4515        {
4516            int completed;
4517
4518            string proc = (selfEmployed.SelfEmployedId == 0 ? "tblInsertSelfEmployed" : "tblUpdateSelfEmployed");
4519
4520            SqlParameter[] selfemp = new SqlParameter[28];
4521            selfemp[0] = new SqlParameter("@ClientRegistrationID", selfEmployed.ClientRegistrationID);
4522            selfemp[2] = new SqlParameter("@TypeOfSelfEmployment", selfEmployed.TypeOfSelfEmployment);
4523            selfemp[3] = new SqlParameter("@NameOfComapany", selfEmployed.NameOfComapany);
4524            selfemp[4] = new SqlParameter("@Shareholding", selfEmployed.Shareholding);
4525            selfemp[5] = new SqlParameter("@Position", selfEmployed.Position);
4526            selfemp[6] = new SqlParameter("@IncorporationDate", selfEmployed.IncorporationDate);
4527            selfemp[7] = new SqlParameter("@IsActive", selfEmployed.IsActive);
4528            selfemp[8] = new SqlParameter("@IsDeleted", selfEmployed.IsDeleted);
4529            selfemp[9] = new SqlParameter("@AddDate", DateTime.Now);
4530            selfemp[10] = new SqlParameter("@EntryBy", selfEmployed.EntryBy);
4531            selfemp[11] = new SqlParameter("@UpdateDate", DateTime.Now);
4532            selfemp[12] = new SqlParameter("@UpdateBy", selfEmployed.UpdateBy);
4533            selfemp[13] = new SqlParameter("@result", SqlDbType.Int);
4534            selfemp[13].Direction = ParameterDirection.Output;
4535
4536            // id
4537            selfemp[14] = new SqlParameter("@SelfEmployedId", selfEmployed.SelfEmployedId);
4538            selfemp[15] = new SqlParameter("@ReferId", selfEmployed.ReferId);
4539
4540            // financial  YE
4541            selfemp[16] = new SqlParameter("@TurnoverY1", selfEmployed.TurnoverY1);
4542            selfemp[17] = new SqlParameter("@TurnoverY2", selfEmployed.TurnoverY2);
4543            selfemp[18] = new SqlParameter("@TurnoverY3", selfEmployed.TurnoverY3);
4544            selfemp[19] = new SqlParameter("@NetProfitY1", selfEmployed.NetProfitY1);
4545            selfemp[20] = new SqlParameter("@NetProfitY2", selfEmployed.NetProfitY2);
4546            selfemp[21] = new SqlParameter("@NetProfitY3", selfEmployed.NetProfitY3);
4547            selfemp[22] = new SqlParameter("@SalaryY1", selfEmployed.SalaryY1);
4548            selfemp[23] = new SqlParameter("@SalaryY2", selfEmployed.SalaryY2);
4549            selfemp[24] = new SqlParameter("@SalaryY3", selfEmployed.SalaryY3);
4550            selfemp[25] = new SqlParameter("@DividendsY1", selfEmployed.DividendsY1);
4551            selfemp[26] = new SqlParameter("@DividendsY2", selfEmployed.DividendsY2);
4552            selfemp[27] = new SqlParameter("@DividendsY3", selfEmployed.DividendsY3);
4553
4554
4555            if (useTransaction)
4556            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, selfemp); }
4557            else
4558            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, selfemp); }
4559
4560            // return 1 if executed successfully
4561            completed = Convert.ToInt32(selfemp[13] != null ? selfemp[13].SqlValue.ToString() : "0");
4562            return completed;
4563
4564        }
4565        // self employed/unemployment new added by satish
4566        public int AddSelfEmployed_UnEmployment(tblSelfEmployed selfEmployed, bool useTransaction, SqlTransaction tran, SqlConnection con)
4567        {
4568            int completed;
4569
4570            string proc = (selfEmployed.SelfEmployedId == 0 ? "tblInsertSelfEmployed" : "tblUpdateSelfEmployed_UnEmployment");
4571
4572            SqlParameter[] selfemp = new SqlParameter[7];
4573
4574            selfemp[0] = new SqlParameter("@ClientRegistrationID", selfEmployed.ClientRegistrationID);
4575            selfemp[1] = new SqlParameter("@SelfEmployedId", selfEmployed.SelfEmployedId);
4576            selfemp[2] = new SqlParameter("@ReferId", selfEmployed.ReferId);
4577
4578            selfemp[3] = new SqlParameter("@DateOfUnemployment", !string.IsNullOrWhiteSpace(selfEmployed.StrDateOfUnemployment) ? Helper.GetDate(selfEmployed.StrDateOfUnemployment) : null);
4579            selfemp[4] = new SqlParameter("@DateOfReemployment", !string.IsNullOrWhiteSpace(selfEmployed.StrDateOfReemployment) ? Helper.GetDate(selfEmployed.StrDateOfReemployment) : null);
4580            selfemp[5] = new SqlParameter("@ReasonForUnemployment", selfEmployed.ReasonForUnemployment);
4581            selfemp[6] = new SqlParameter("@result", SqlDbType.Int);
4582            selfemp[6].Direction = ParameterDirection.Output;
4583
4584            if (useTransaction)
4585            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, selfemp); }
4586            else
4587            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, selfemp); }
4588
4589            // return 1 if executed successfully
4590            completed = Convert.ToInt32(selfemp[6] != null ? selfemp[6].SqlValue.ToString() : "0");
4591            return completed;
4592        }
4593
4594
4595        // Add OtherIncomeInformation
4596
4597        public int AddOtherIncomeInformation(tblOtherIncomeInformation incomeInfo, bool useTransaction, SqlTransaction tran, SqlConnection con)
4598        {
4599            int completed;
4600
4601            string proc = (incomeInfo.OtherIncomeInformationId == 0 ? "tblInsertOtherIncomeInformation" : "tblUpdateOtherIncomeInformation");
4602            SqlParameter[] income = new SqlParameter[16];
4603            income[0] = new SqlParameter("@SelfEmployedId", incomeInfo.SelfEmployedId);
4604            income[1] = new SqlParameter("@FirmName", incomeInfo.FirmName);
4605            income[2] = new SqlParameter("@IndividualContact", incomeInfo.IndividualContact);
4606            income[3] = new SqlParameter("@PhoneNo", incomeInfo.PhoneNo);
4607            income[4] = new SqlParameter("@FaxNo", incomeInfo.FaxNo);
4608            income[5] = new SqlParameter("@Qualification", incomeInfo.Qualification);
4609            income[6] = new SqlParameter("@IsActive", incomeInfo.IsActive);
4610            income[7] = new SqlParameter("@IsDeleted", incomeInfo.IsDeleted);
4611            income[8] = new SqlParameter("@AddDate", DateTime.Now);
4612            income[9] = new SqlParameter("@EntryBy", incomeInfo.EntryBy);
4613            income[10] = new SqlParameter("@UpdateDate", DateTime.Now);
4614            income[11] = new SqlParameter("@UpdateBy", incomeInfo.UpdateBy);
4615            income[12] = new SqlParameter("@ReferId", incomeInfo.ReferId);
4616            income[13] = new SqlParameter("@result", SqlDbType.Int);
4617            income[13].Direction = ParameterDirection.Output;
4618
4619            income[14] = new SqlParameter("@ClientRegistrationID", incomeInfo.ClientRegistrationID);
4620
4621            //id
4622            income[15] = new SqlParameter("@OtherIncomeInformationId", incomeInfo.OtherIncomeInformationId);
4623
4624
4625            if (useTransaction)
4626            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, income); }
4627            else
4628            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, income); }
4629
4630            // return 1 if executed successfully
4631            completed = Convert.ToInt32(income[13] != null ? income[13].SqlValue.ToString() : "0");
4632            return completed;
4633        }
4634
4635        // Add  SourceofFundsforPurchase
4636
4637        public int AddSourceofFundsforPurchase(tblSourceofFundsforPurchase sourceOffund, bool useTransaction, SqlTransaction tran, SqlConnection con)
4638        {
4639            int completed;
4640
4641            string proc = (sourceOffund.SourceofFundsforPurchaseId == 0 ? "tblInsertSourceofFundsforPurchase" : "tblUpdateSourceofFundsforPurchase");
4642            SqlParameter[] fund = new SqlParameter[16];
4643            fund[0] = new SqlParameter("@ClientRegistrationID", sourceOffund.ClientRegistrationID);
4644            fund[1] = new SqlParameter("@Cash", sourceOffund.Cash);
4645            fund[2] = new SqlParameter("@StockShare", sourceOffund.StockShare);
4646            fund[3] = new SqlParameter("@Gift", sourceOffund.Gift);
4647            fund[4] = new SqlParameter("@Equity", sourceOffund.Equity);
4648            fund[5] = new SqlParameter("@IsActive", sourceOffund.IsActive);
4649            fund[6] = new SqlParameter("@IsDeleted", sourceOffund.IsDeleted);
4650            fund[7] = new SqlParameter("@AddDate", DateTime.Now);
4651            fund[8] = new SqlParameter("@EntryBy", sourceOffund.EntryBy);
4652            fund[9] = new SqlParameter("@UpdateDate", DateTime.Now);
4653            fund[10] = new SqlParameter("@UpdateBy", sourceOffund.UpdateBy);
4654            fund[11] = new SqlParameter("@result", SqlDbType.Int);
4655            fund[11].Direction = ParameterDirection.Output;
4656            // id
4657            fund[12] = new SqlParameter("@SourceofFundsforPurchaseId", sourceOffund.SourceofFundsforPurchaseId);
4658            fund[13] = new SqlParameter("@ReferId", sourceOffund.ReferId);
4659            fund[14] = new SqlParameter("@GiftFrom", sourceOffund.GiftFrom);
4660            fund[15] = new SqlParameter("@GiftFromNew", sourceOffund.GiftFromNew);
4661
4662
4663            if (useTransaction)
4664            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, fund); }
4665            else
4666            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, fund); }
4667
4668            // return 1 if executed successfully
4669            completed = Convert.ToInt32(fund[11] != null ? fund[11].SqlValue.ToString() : "0");
4670            return completed;
4671        }
4672
4673        // Add Current Commitments
4674        public int AddCurrentCommitments(tblCurrentCommitment commitments, bool useTransaction, SqlTransaction tran, SqlConnection con)
4675        {
4676            int completed; 
4677            string proc = (commitments.CurrentCommitmentsId == 0 ? "tblInsertCurrentCommitments" : "tblUpdateCurrentCommitments");
4678
4679            SqlParameter[] commitment = new SqlParameter[19];
4680            commitment[0] = new SqlParameter("@OwnerName", commitments.OwnerName);
4681            commitment[1] = new SqlParameter("@TypeOfLoan", commitments.TypeOfLoan);
4682            commitment[2] = new SqlParameter("@Lender", commitments.Lender);
4683            commitment[3] = new SqlParameter("@AmountoutStanding", commitments.AmountoutStanding);
4684            commitment[4] = new SqlParameter("@monthlyPayment", commitments.monthlyPayment);
4685            commitment[5] = new SqlParameter("@IsSecured", commitments.IsSecured);
4686            commitment[6] = new SqlParameter("@IsToBeRepaid", commitments.IsToBeRepaid);
4687            commitment[7] = new SqlParameter("@IsActive", commitments.IsActive);
4688            commitment[8] = new SqlParameter("@IsDeleted", commitments.IsDeleted);
4689            commitment[9] = new SqlParameter("@AddDate", DateTime.Now);
4690            commitment[10] = new SqlParameter("@EntryBy", commitments.EntryBy);
4691            commitment[11] = new SqlParameter("@UpdateDate", DateTime.Now);
4692            commitment[12] = new SqlParameter("@UpdateBy", commitments.UpdateBy);
4693            commitment[13] = new SqlParameter("@result", SqlDbType.Int);
4694            commitment[13].Direction = ParameterDirection.Output;
4695
4696            //id
4697            commitment[14] = new SqlParameter("@CurrentCommitmentsId", commitments.CurrentCommitmentsId);
4698            commitment[15] = new SqlParameter("@ClientRegistrationID", commitments.ClientRegistrationID);
4699            commitment[16] = new SqlParameter("@ReferId", commitments.ReferId);
4700            commitment[17] = new SqlParameter("@Owner", commitments.Owner);
4701            commitment[18] = new SqlParameter("@TypeofLoanOther", commitments.TypeofLoanOther);
4702            if (useTransaction)
4703            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
4704            else
4705            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
4706
4707            // return 1 if executed successfully
4708            completed = Convert.ToInt32(commitment[13] != null ? (commitment[13].SqlValue != null ?  commitment[13].SqlValue.ToString() : "0") : "0");
4709            return completed;
4710        }
4711
4712        // Created on 20/01/2015
4713        //Add and Update New Mortgage And Property Details
4714
4715        public int AddUpdateNewMortgageandPropertyDetail(tblAdditional_Property_Info addProp, bool useTransaction, SqlTransaction tran, SqlConnection con)
4716        {
4717            int completed;
4718            string proc = (addProp.tblAdditional_Property_InfoID == 0 ? "tblInsertbltblAdditional_Property_Info" : "tblUpdateAdditional_Property_Info");
4719
4720            SqlParameter[] additionPro = new SqlParameter[]{
4721              new SqlParameter("@ReferId", addProp.ReferId),
4722              new SqlParameter("@Selling_Agents", addProp.Selling_Agents),
4723              new SqlParameter("@Solicitors", addProp.Solicitors),
4724              new SqlParameter("@Bank_Details", addProp.Bank_Details),
4725              new SqlParameter("@Additional_Info", addProp.Additional_Info),
4726              new SqlParameter("@IsActive", addProp.IsActive),
4727              new SqlParameter("@IsDeleted", addProp.IsDeleted),
4728              new SqlParameter("@AddDate", DateTime.Now),
4729              new SqlParameter("@EntryBy", addProp.EntryBy),
4730              new SqlParameter("@UpdateDate", DateTime.Now),
4731              new SqlParameter("@UpdateBy", addProp.UpdateBy),
4732              new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
4733              new SqlParameter("@ClientRegistrationID", addProp.ClientRegistrationID),
4734              new SqlParameter("@tblAdditional_Property_InfoID", addProp.tblAdditional_Property_InfoID),
4735              new SqlParameter("@Type_Of_Application", addProp.Type_Of_Application),
4736              new SqlParameter("@Address_Line1", addProp.Address_Line1),
4737              new SqlParameter("@Address_Line2", addProp.Address_Line2),
4738              new SqlParameter("@Address_Line3", addProp.Address_Line3),
4739              new SqlParameter("@Address_Line4", addProp.Address_Line4),
4740              new SqlParameter("@Post_Code", addProp.Post_Code),
4741              new SqlParameter("@Country", addProp.Country),
4742
4743            };
4744
4745            var outParameter = additionPro.Where(x => x.Direction == ParameterDirection.Output).FirstOrDefault(); 
4746
4747            if (useTransaction)
4748            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, additionPro); }
4749            else
4750            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, additionPro); }
4751            // return 1 if executed successfully
4752            completed = Convert.ToInt32(outParameter != null ? outParameter.SqlValue.ToString() : "0");
4753
4754            return completed;
4755        }
4756
4757        // Created on 20/01/2015
4758        //Add and Update Investment Portfolio
4759
4760        public int AddUpdateInvestmentPortfolio(tblOther_Mortgages investmentPortfolio, bool useTransaction, SqlTransaction tran, SqlConnection con)
4761        {
4762            int completed;
4763
4764            string proc = (investmentPortfolio.Other_MortgagesID == 0 ? "tblInserttblOther_Mortgages" : "tblUpdatetblOther_Mortgages");
4765            SqlParameter[] commitment = new SqlParameter[39];
4766
4767            commitment[0] = new SqlParameter("@Lender", investmentPortfolio.Lender);
4768            commitment[1] = new SqlParameter("@Account_Number", investmentPortfolio.Account_Number);
4769            commitment[2] = new SqlParameter("@Prop_Country", investmentPortfolio.Prop_Country);
4770            commitment[3] = new SqlParameter("@Current_Value", investmentPortfolio.Current_Value);
4771            commitment[4] = new SqlParameter("@Rental_Income", investmentPortfolio.Rental_Income);
4772            commitment[5] = new SqlParameter("@Ownership", investmentPortfolio.Ownership);
4773            commitment[6] = new SqlParameter("@Amount_Outstanding", investmentPortfolio.Amount_Outstanding);
4774            commitment[7] = new SqlParameter("@Monthly_Payment", investmentPortfolio.Monthly_Payment);
4775            commitment[8] = new SqlParameter("@Term", investmentPortfolio.Term);
4776            commitment[9] = new SqlParameter("@Repayment_Method", investmentPortfolio.Repayment_Method);
4777            commitment[10] = new SqlParameter("@Current_Rate_Info", investmentPortfolio.Current_Rate_Info);
4778            commitment[11] = new SqlParameter("@ERP_Info", investmentPortfolio.ERP_Info);
4779            commitment[12] = new SqlParameter("@IsActive", investmentPortfolio.IsActive);
4780            commitment[13] = new SqlParameter("@IsDeleted", investmentPortfolio.IsDeleted);
4781            commitment[14] = new SqlParameter("@AddDate", DateTime.Now);
4782            commitment[15] = new SqlParameter("@EntryBy", investmentPortfolio.EntryBy);
4783            commitment[16] = new SqlParameter("@UpdateDate", DateTime.Now);
4784            commitment[17] = new SqlParameter("@UpdateBy", investmentPortfolio.UpdateBy);
4785            commitment[18] = new SqlParameter("@No_Bedroom", investmentPortfolio.No_Bedrooms);
4786            commitment[19] = new SqlParameter("@Type_Of_Application", investmentPortfolio.Type_Of_Application);
4787            commitment[20] = new SqlParameter("@Purch_Price", investmentPortfolio.Purch_Price);
4788            commitment[21] = new SqlParameter("@Deposit_Available", investmentPortfolio.Deposit_Available);
4789            commitment[22] = new SqlParameter("@Mortgage_Required", investmentPortfolio.Mortgage_Required);
4790            commitment[23] = new SqlParameter("@Type_Of_Property", investmentPortfolio.Type_Of_Property);
4791            commitment[24] = new SqlParameter("@Tenure", investmentPortfolio.Tenure);
4792            commitment[25] = new SqlParameter("@For_Flats_Maisonette", investmentPortfolio.For_Flats_Maisonette);
4793            commitment[26] = new SqlParameter("@Additional_Info", investmentPortfolio.Additional_Info);
4794            commitment[27] = new SqlParameter("@Capital_Raising", investmentPortfolio.Capital_Raising);
4795            commitment[28] = new SqlParameter("@Reason", investmentPortfolio.Reason);
4796            commitment[29] = new SqlParameter("@Ex_Local", investmentPortfolio.Ex_Local);
4797
4798            commitment[30] = new SqlParameter("@result", SqlDbType.Int);
4799            commitment[30].Direction = ParameterDirection.Output;
4800
4801            // id
4802            commitment[31] = new SqlParameter("@Other_MortgagesID", investmentPortfolio.Other_MortgagesID);
4803            commitment[32] = new SqlParameter("@ClientRegistrationID", investmentPortfolio.ClientRegistrationID);
4804            commitment[33] = new SqlParameter("@ReferId", investmentPortfolio.ReferId);
4805
4806            commitment[34] = new SqlParameter("@Prop_Adddress_Line1", investmentPortfolio.Prop_Adddress_Line1);
4807            commitment[35] = new SqlParameter("@Prop_Adddress_Line2", investmentPortfolio.Prop_Adddress_Line2);
4808            commitment[36] = new SqlParameter("@Prop_Adddress_Line3", investmentPortfolio.Prop_Adddress_Line3);
4809            commitment[37] = new SqlParameter("@Prop_Adddress_Line4", investmentPortfolio.Prop_Adddress_Line4);
4810            commitment[38] = new SqlParameter("@Prop_PostCode", investmentPortfolio.Prop_PostCode);
4811
4812
4813
4814
4815            if (useTransaction)
4816            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
4817            else
4818            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
4819
4820            // return 1 if executed successfully
4821            completed = Convert.ToInt32(commitment[30] != null ? commitment[30].SqlValue.ToString() : "0");
4822            return completed;
4823        }
4824
4825        public int AddMortgagePropertyDetail(tblNew_Mortgage_Property_Details newMortgagePropertyDetails, bool useTransaction, SqlTransaction tran, SqlConnection con)
4826        {
4827            int completed;
4828
4829            // newMortgagePropertyDetails.New_Mortgage_Property_DetailsID
4830            string proc = (newMortgagePropertyDetails.New_Mortgage_Property_DetailsID == 0 ? "tblInserttblNew_Mortgage_Property_Details" : "tblUpdatetblNew_Mortgage_Property_Details");
4831
4832            SqlParameter[] commitment = new SqlParameter[35];
4833            commitment[0] = new SqlParameter("@ReferId", newMortgagePropertyDetails.ReferId);
4834            commitment[1] = new SqlParameter("@Type_Of_Application", newMortgagePropertyDetails.Type_Of_Application);
4835            commitment[2] = new SqlParameter("@Address_Of_Property", newMortgagePropertyDetails.Address_Of_Property);
4836            commitment[3] = new SqlParameter("@Purch_Price", newMortgagePropertyDetails.Purch_Price);
4837            commitment[4] = new SqlParameter("@Deposit_Available", newMortgagePropertyDetails.Deposit_Available);
4838            commitment[5] = new SqlParameter("@Mortgage_Required", newMortgagePropertyDetails.Mortgage_Required);
4839            commitment[6] = new SqlParameter("@Repayment", newMortgagePropertyDetails.Repayment);
4840            commitment[7] = new SqlParameter("@Term_Of_Mortgage", newMortgagePropertyDetails.Term_Of_Mortgage);
4841            commitment[8] = new SqlParameter("@Details_Of_Affordability", newMortgagePropertyDetails.Details_Of_Affordability);
4842            commitment[9] = new SqlParameter("@Type_Of_Property", newMortgagePropertyDetails.Type_Of_Property);
4843            commitment[10] = new SqlParameter("@Tenure", newMortgagePropertyDetails.Tenure);
4844            commitment[11] = new SqlParameter("@Leasehold_Term", newMortgagePropertyDetails.Leasehold_Term);
4845            commitment[12] = new SqlParameter("@Year_Build", newMortgagePropertyDetails.Year_Build);
4846            commitment[13] = new SqlParameter("@Construction", newMortgagePropertyDetails.Construction);
4847            commitment[14] = new SqlParameter("@For_Flats_Maisonette", newMortgagePropertyDetails.For_Flats_Maisonette);
4848            commitment[15] = new SqlParameter("@Purpose_Built", newMortgagePropertyDetails.Purpose_Built);
4849            commitment[16] = new SqlParameter("@Conversion", newMortgagePropertyDetails.Conversion);
4850            commitment[17] = new SqlParameter("@No_Of_Floors", newMortgagePropertyDetails.No_Of_Floors);
4851            commitment[18] = new SqlParameter("@Any_Commercial", newMortgagePropertyDetails.Any_Commercial);
4852            commitment[19] = new SqlParameter("@Service_Charge", newMortgagePropertyDetails.Service_Charge);
4853            commitment[20] = new SqlParameter("@Ground_Rent", newMortgagePropertyDetails.Ground_Rent);
4854            commitment[21] = new SqlParameter("@Additional_Info", newMortgagePropertyDetails.Additional_Info);
4855            commitment[22] = new SqlParameter("@IsActive", newMortgagePropertyDetails.IsActive);
4856            commitment[23] = new SqlParameter("@IsDeleted", newMortgagePropertyDetails.IsDeleted);
4857            commitment[24] = new SqlParameter("@AddDate", DateTime.Now);
4858            commitment[25] = new SqlParameter("@EntryBy", newMortgagePropertyDetails.EntryBy);
4859            commitment[26] = new SqlParameter("@UpdateDate", DateTime.Now);
4860            commitment[27] = new SqlParameter("@UpdateBy", newMortgagePropertyDetails.UpdateBy);
4861            commitment[28] = new SqlParameter("@result", SqlDbType.Int);
4862            commitment[28].Direction = ParameterDirection.Output;
4863            commitment[29] = new SqlParameter("@Source_Of_Deposit", newMortgagePropertyDetails.Source_Of_Deposit);
4864            commitment[30] = new SqlParameter("@Capital_Raising", newMortgagePropertyDetails.Capital_Raising);
4865            commitment[31] = new SqlParameter("@Reason", newMortgagePropertyDetails.Reason);
4866            commitment[32] = new SqlParameter("@Ex_Local", newMortgagePropertyDetails.Ex_Local);
4867
4868            //id
4869            commitment[33] = new SqlParameter("@New_Mortgage_Property_DetailsID", newMortgagePropertyDetails.New_Mortgage_Property_DetailsID);
4870            commitment[34] = new SqlParameter("@ClientRegistrationID", newMortgagePropertyDetails.ClientRegistrationID);
4871
4872            if (useTransaction)
4873            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, commitment); }
4874            else
4875            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, commitment); }
4876
4877            // return 1 if executed successfully
4878            completed = Convert.ToInt32(commitment[28] != null ? commitment[28].SqlValue.ToString() : "0");
4879            return completed;
4880        }
4881
4882
4883        public int AddResidentialMortgage(tblResidentialMortgage ResidentialMortgage, bool useTransaction, SqlTransaction tran, SqlConnection con)
4884        {
4885            int completed;
4886
4887            string proc = (ResidentialMortgage.ResidentialMortgageID == 0 ? "tblInsertblResidentialMortgage" : "tblUpdatetblResidentialMortgage");
4888
4889            SqlParameter[] resMortgage = new SqlParameter[24];
4890            resMortgage[0] = new SqlParameter("@ReferId", ResidentialMortgage.ReferId);
4891            resMortgage[1] = new SqlParameter("@Lender", ResidentialMortgage.Lender);
4892            resMortgage[2] = new SqlParameter("@Account_Number", ResidentialMortgage.Account_Number);
4893            resMortgage[3] = new SqlParameter("@Current_Value", ResidentialMortgage.Current_Value);
4894            resMortgage[4] = new SqlParameter("@Ownership", ResidentialMortgage.Ownership);
4895            resMortgage[5] = new SqlParameter("@Original_Mortgage", ResidentialMortgage.Original_Mortgage);
4896            resMortgage[6] = new SqlParameter("@Amount_Outstanding", ResidentialMortgage.Amount_Outstanding);
4897            resMortgage[7] = new SqlParameter("@Monthly_Payment", ResidentialMortgage.Monthly_Payment);
4898            resMortgage[8] = new SqlParameter("@Term", ResidentialMortgage.Term);
4899            resMortgage[9] = new SqlParameter("@Repayment_Method", ResidentialMortgage.Repayment_Method);
4900            resMortgage[10] = new SqlParameter("@Repayment_Vehicle", ResidentialMortgage.Repayment_Vehicle);
4901            resMortgage[11] = new SqlParameter("@Current_Rate_Info", ResidentialMortgage.Current_Rate_Info);
4902            resMortgage[12] = new SqlParameter("@ERP_Info", ResidentialMortgage.ERP_Info);
4903            resMortgage[13] = new SqlParameter("@Portable", ResidentialMortgage.Portable);
4904            resMortgage[14] = new SqlParameter("@IsActive", ResidentialMortgage.IsActive);
4905            resMortgage[15] = new SqlParameter("@IsDeleted", ResidentialMortgage.IsDeleted);
4906            resMortgage[16] = new SqlParameter("@AddDate", DateTime.Now);
4907            resMortgage[17] = new SqlParameter("@EntryBy", ResidentialMortgage.EntryBy);
4908            resMortgage[18] = new SqlParameter("@UpdateDate", DateTime.Now);
4909            resMortgage[19] = new SqlParameter("@UpdateBy", ResidentialMortgage.UpdateBy);
4910            resMortgage[20] = new SqlParameter("@result", SqlDbType.Int);
4911            resMortgage[20].Direction = ParameterDirection.Output;
4912
4913            // id
4914            resMortgage[21] = new SqlParameter("@ResidentialMortgageID", ResidentialMortgage.ResidentialMortgageID);
4915            resMortgage[22] = new SqlParameter("@ClientRegistrationID", ResidentialMortgage.ClientRegistrationID);
4916            resMortgage[23] = new SqlParameter("@AddressID", ResidentialMortgage.AddressID);
4917
4918            if (useTransaction)
4919            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, resMortgage); }
4920            else
4921            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, resMortgage); }
4922
4923            // return 1 if executed successfully
4924            completed = Convert.ToInt32(resMortgage[20] != null ? resMortgage[20].SqlValue.ToString() : "0");
4925            return completed;
4926        }
4927
4928        public int AddExpenditure(tblExpenditure Expenditure, bool useTransaction, SqlTransaction tran, SqlConnection con)
4929        {
4930            int completed;
4931            string proc = (Expenditure.ExpenditureID == 0 ? "tblInsertblExpenditure" : "tblUpdatetblExpenditure");
4932
4933            SqlParameter[] expenditure = new SqlParameter[34];
4934            expenditure[0] = new SqlParameter("@ClientRegistrationID", Expenditure.ClientRegistrationID);
4935            expenditure[1] = new SqlParameter("@ReferId", Expenditure.ReferId);
4936            expenditure[2] = new SqlParameter("@Mortgage_Rent", Expenditure.Mortgage_Rent);
4937            expenditure[3] = new SqlParameter("@Credit_Commitments", Expenditure.Credit_Commitments);
4938            expenditure[4] = new SqlParameter("@Maintenance", Expenditure.Maintenance);
4939            expenditure[5] = new SqlParameter("@Food_Drink_clothing", Expenditure.Food_Drink_clothing);
4940            expenditure[6] = new SqlParameter("@Utilities_Including_Council_Tax", Expenditure.Utilities_Including_Council_Tax);
4941            expenditure[7] = new SqlParameter("@Miscellaneous_Goods_Services", Expenditure.Miscellaneous_Goods_Services);
4942            expenditure[8] = new SqlParameter("@Transport", Expenditure.Transport);
4943            expenditure[9] = new SqlParameter("@Entertainment_Recreation", Expenditure.Entertainment_Recreation);
4944            expenditure[10] = new SqlParameter("@Holidays", Expenditure.Holidays);
4945            expenditure[11] = new SqlParameter("@Nursery_College_Uni_fees", Expenditure.Nursery_College_Uni_fees);
4946            expenditure[12] = new SqlParameter("@Other_Expenditure", Expenditure.Other_Expenditure);
4947            expenditure[13] = new SqlParameter("@Existing_Life_Insurance_PHI_Premiums", Expenditure.Existing_Life_Insurance_PHI_Premiums);
4948            expenditure[14] = new SqlParameter("@Discretionary_Expenditure", Expenditure.Discretionary_Expenditure);
4949            expenditure[15] = new SqlParameter("@Monthly_Expenditure_B", Expenditure.Monthly_Expenditure_B);
4950            expenditure[16] = new SqlParameter("@Monthly_Disposable_Income_A_B", Expenditure.Monthly_Disposable_Income_A_B);
4951            expenditure[17] = new SqlParameter("@Remaining_Income", Expenditure.Remaining_Income);
4952            expenditure[18] = new SqlParameter("@Any_Changes_In_Next_5_Years", Expenditure.Any_Changes_In_Next_5_Years);
4953            expenditure[19] = new SqlParameter("@Any_Adverse_Credit_History", Expenditure.Any_Adverse_Credit_History);
4954            expenditure[20] = new SqlParameter("@Total_Income_All_Sources", Expenditure.Total_Income_All_Sources);
4955            expenditure[21] = new SqlParameter("@Total_Net_Monthly_Income_A", Expenditure.Total_Net_Monthly_Income_A);
4956            expenditure[22] = new SqlParameter("@IsActive", Expenditure.IsActive);
4957            expenditure[23] = new SqlParameter("@IsDeleted", Expenditure.IsDeleted);
4958            expenditure[24] = new SqlParameter("@AddDate", DateTime.Now);
4959            expenditure[25] = new SqlParameter("@EntryBy", Expenditure.EntryBy);
4960            expenditure[26] = new SqlParameter("@UpdateDate", DateTime.Now);
4961            expenditure[27] = new SqlParameter("@UpdateBy", Expenditure.UpdateBy);
4962
4963            expenditure[28] = new SqlParameter("@result", SqlDbType.Int);
4964            expenditure[28].Direction = ParameterDirection.Output;
4965
4966            // id
4967            expenditure[29] = new SqlParameter("@ExpenditureID", Expenditure.ExpenditureID);
4968
4969            expenditure[30] = new SqlParameter("@Student_Loan_Payments", Expenditure.Student_Loan_Payments);
4970            expenditure[31] = new SqlParameter("@FromDate", Expenditure.FromDate);
4971            expenditure[32] = new SqlParameter("@ToDate", Expenditure.ToDate);
4972            expenditure[33] = new SqlParameter("@AdditionalInfo", Expenditure.AdditionalInfo);
4973            if (useTransaction)
4974            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, expenditure); }
4975            else
4976            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, expenditure); }
4977
4978            // return 1 if executed successfully
4979            completed = Convert.ToInt32(expenditure[28] != null ? expenditure[28].SqlValue.ToString() : "0");
4980            return completed;
4981        }
4982
4983        // Created on 07/10/2014 
4984        // For add and update Dependent table
4985        public int AddUpdateDependant(tblDependant dependent, bool useTransaction, SqlTransaction tran, SqlConnection con)
4986        {
4987            int completed = 0;
4988
4989            if (dependent.DependantID == 0)
4990            {
4991                SqlParameter[] par = new SqlParameter[]
4992                {
4993                    new SqlParameter("@Applicant", dependent.Applicant),
4994                    new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(dependent.StrDependentDOB) ? Helper.GetDate(dependent.StrDependentDOB) : null),
4995                    new SqlParameter("@ClientRegistrationID", dependent.ClientRegistrationID),//set clientid in model after model binding
4996                    new SqlParameter("@AddDate", DateTime.Now),
4997                    new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
4998                    new SqlParameter("@ReferId", dependent.ReferId),
4999                    new SqlParameter("@Name", dependent.Name)
5000                };
5001                if (useTransaction)
5002                { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "tblInsertDependant", tran, con, par); }
5003                else
5004                { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblInsertDependant", par); }
5005                completed = Convert.ToInt32(par[4] != null ? par[4].SqlValue.ToString() : "0");
5006            }
5007            else
5008            {
5009                SqlParameter[] par = new SqlParameter[]
5010                {
5011                    new SqlParameter("@Applicant", dependent.Applicant),
5012                    new SqlParameter("@DOB", !string.IsNullOrWhiteSpace(dependent.StrDependentDOB) ? Helper.GetDate(dependent.StrDependentDOB) : null),
5013                    new SqlParameter("@UpdateDate", DateTime.Now),
5014                    new SqlParameter("@UpdateBy", dependent.UpdateBy),//set updated by id in model after model binding
5015                    new SqlParameter("@result", SqlDbType.Int){Direction = ParameterDirection.Output},
5016                    new SqlParameter("@DependantID", dependent.DependantID),
5017                    new SqlParameter("@ReferId", dependent.ReferId),
5018                    new SqlParameter("@ClientRegistrationID", dependent.ClientRegistrationID),
5019                    new SqlParameter("@Name", dependent.Name)
5020                };
5021                if (useTransaction)
5022                { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, "tblUpdateDependant", tran, con, par); }
5023                else
5024                { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "tblUpdateDependant", par); }
5025                completed = Convert.ToInt32(par[4] != null ? par[4].SqlValue.ToString() : "0");
5026
5027            }
5028
5029            return completed;
5030        }
5031
5032        // new 21/10/2014
5033        public int AddUpdateAdditionalProperty(tblAdditional_Property_Info addProp, bool useTransaction, SqlTransaction tran, SqlConnection con)
5034        {
5035            int completed = 0;
5036            string proc = (addProp.tblAdditional_Property_InfoID == 0 ? "tblInsertbltblAdditional_Property_Info" : "tblUpdateAdditional_Property_Info");
5037
5038            SqlParameter[] additionPro = new SqlParameter[14];
5039            additionPro[0] = new SqlParameter("@ReferId", addProp.ReferId);
5040            additionPro[1] = new SqlParameter("@Selling_Agents", addProp.Selling_Agents);
5041            additionPro[2] = new SqlParameter("@Solicitors", addProp.Solicitors);
5042            additionPro[3] = new SqlParameter("@Bank_Details", addProp.Bank_Details);
5043            additionPro[4] = new SqlParameter("@Additional_Info", addProp.Additional_Info);
5044            additionPro[5] = new SqlParameter("@IsActive", addProp.IsActive);
5045            additionPro[6] = new SqlParameter("@IsDeleted", addProp.IsDeleted);
5046            additionPro[7] = new SqlParameter("@AddDate", DateTime.Now);
5047            additionPro[8] = new SqlParameter("@EntryBy", addProp.EntryBy);
5048            additionPro[9] = new SqlParameter("@UpdateDate", DateTime.Now);
5049            additionPro[10] = new SqlParameter("@UpdateBy", addProp.UpdateBy);
5050            additionPro[11] = new SqlParameter("@result", SqlDbType.Int);
5051            additionPro[11].Direction = ParameterDirection.Output;
5052            additionPro[12] = new SqlParameter("@ClientRegistrationID", addProp.ClientRegistrationID);
5053            additionPro[13] = new SqlParameter("@tblAdditional_Property_InfoID", addProp.tblAdditional_Property_InfoID);
5054
5055            if (useTransaction)
5056            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, additionPro); }
5057            else
5058            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, additionPro); }
5059            // return 1 if executed successfully
5060            completed = Convert.ToInt32(additionPro[11] != null ? additionPro[11].SqlValue.ToString() : "0");
5061
5062            return completed;
5063        }
5064
5065        public int AddUpdateCurrentSavingArrangements(tblCurrent_Savings_Arrangements savings, bool useTransaction, SqlTransaction tran, SqlConnection con)
5066        {
5067            int completed = 0;
5068            string proc = (savings.Current_Savings_ArrangementsID == 0 ? "tblInsertCurrent_Savings_Arrangements" : "tblUpdateCurrent_Savings_Arrangements");
5069
5070            SqlParameter[] currSaving = new SqlParameter[15];
5071            currSaving[0] = new SqlParameter("@Current_Savings_ArrangementsID", savings.Current_Savings_ArrangementsID);
5072            currSaving[1] = new SqlParameter("@ReferId", savings.ReferId);
5073            currSaving[2] = new SqlParameter("@Total_Amount", savings.Total_Amount);
5074            currSaving[3] = new SqlParameter("@If_No_Fund", savings.If_No_Fund);
5075            currSaving[4] = new SqlParameter("@IsActive", savings.IsActive);
5076            currSaving[5] = new SqlParameter("@IsDeleted", savings.IsDeleted);
5077            currSaving[6] = new SqlParameter("@AddDate", DateTime.Now);
5078            currSaving[7] = new SqlParameter("@EntryBy", savings.EntryBy);
5079            currSaving[8] = new SqlParameter("@UpdateDate", DateTime.Now);
5080            currSaving[9] = new SqlParameter("@UpdateBy", savings.UpdateBy);
5081            currSaving[10] = new SqlParameter("@result", SqlDbType.Int);
5082            currSaving[10].Direction = ParameterDirection.Output;
5083            currSaving[11] = new SqlParameter("@ClientRegistrationID", savings.ClientRegistrationID);
5084            currSaving[12] = new SqlParameter("@FromDate", savings.FromDate);
5085            currSaving[13] = new SqlParameter("@ToDate", savings.ToDate);
5086            currSaving[14] = new SqlParameter("@AdditionalInfo", savings.AdditionalInfo);
5087
5088            if (useTransaction)
5089            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, currSaving); }
5090            else
5091            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, currSaving); }
5092            // return 1 if executed successfully
5093            completed = Convert.ToInt32(currSaving[10] != null ? currSaving[10].SqlValue.ToString() : "0");
5094            return completed;
5095        }
5096
5097        public int AddUpdateProtection(tblCurrent_Protection_Arrangements Protection, bool useTransaction, SqlTransaction tran, SqlConnection con)
5098        {
5099            int completed = 0;
5100            string proc = (Protection.Current_Protection_ArrangementsID == 0 ? "tblInserttblCurrent_Protection_Arrangements" : "tblUpdateCurrent_Protection_Arrangements");
5101
5102            SqlParameter[] protection = new SqlParameter[18];
5103            protection[0] = new SqlParameter("@Current_Protection_ArrangementsID", Protection.Current_Protection_ArrangementsID);
5104            protection[1] = new SqlParameter("@ReferId", Protection.ReferId);
5105            protection[2] = new SqlParameter("@Owner", Protection.Owner);
5106            protection[3] = new SqlParameter("@Type_Of_Policy", Protection.Type_Of_Policy);
5107            protection[4] = new SqlParameter("@Provider", Protection.Provider);
5108            protection[5] = new SqlParameter("@Sum_Assured", Protection.Sum_Assured);
5109            protection[6] = new SqlParameter("@Monthly_Premium", Protection.Monthly_Premium);
5110            protection[7] = new SqlParameter("@End_Date_Term", Protection.End_Date_Term);
5111            protection[8] = new SqlParameter("@In_Trust", Protection.In_Trust);
5112            protection[9] = new SqlParameter("@IsActive", Protection.IsActive);
5113            protection[10] = new SqlParameter("@IsDeleted", Protection.IsDeleted);
5114            protection[11] = new SqlParameter("@AddDate", DateTime.Now);
5115            protection[12] = new SqlParameter("@EntryBy", Protection.EntryBy);
5116            protection[13] = new SqlParameter("@UpdateDate", DateTime.Now);
5117            protection[14] = new SqlParameter("@UpdateBy", Protection.UpdateBy);
5118            protection[15] = new SqlParameter("@result", SqlDbType.Int);
5119            protection[15].Direction = ParameterDirection.Output;
5120            protection[16] = new SqlParameter("@ClientRegistrationID", Protection.ClientRegistrationID);
5121            protection[17] = new SqlParameter("@TypeofPolicyOther", Protection.TypeofPolicyOther);
5122
5123            if (useTransaction)
5124            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, protection); }
5125            else
5126            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, protection); }
5127            // return 1 if executed successfully
5128            completed = Convert.ToInt32(protection[15] != null ? protection[15].SqlValue.ToString() : "0");
5129            return completed;
5130        }
5131
5132        // Created on 20/01/2015
5133        //Add and Update Employment Benefit
5134
5135        public int AddUpdateEmploymentBenefit(tblEmployment_Benefit emp, bool useTransaction, SqlTransaction tran, SqlConnection con)
5136        {
5137            int completed = 0;
5138            string proc = (emp.Employment_BenefitID == 0 ? "tblInserttblEmployment_Benefit" : "tblUpdateEmployment_Benefit");
5139
5140            SqlParameter[] empBenefit = new SqlParameter[18];
5141            empBenefit[0] = new SqlParameter("@Employment_BenefitID", emp.Employment_BenefitID);
5142            empBenefit[1] = new SqlParameter("@ReferId", emp.ReferId);
5143            empBenefit[2] = new SqlParameter("@Sickness_Benefit", emp.Sickness_Benefit);
5144            empBenefit[3] = new SqlParameter("@Sikness_Amount_Payable", emp.Sikness_Amount_Payable);
5145            empBenefit[4] = new SqlParameter("@Death_In_Service", emp.Death_In_Service);
5146            empBenefit[5] = new SqlParameter("@Death_Amount_Payable", emp.Death_Amount_Payable);
5147            //empBenefit[6] = new SqlParameter("@Have_You_Made_A_Will", emp.Have_You_Made_A_Will);
5148            empBenefit[6] = new SqlParameter("@IsActive", emp.IsActive);
5149            empBenefit[7] = new SqlParameter("@IsDeleted", emp.IsDeleted);
5150            empBenefit[8] = new SqlParameter("@AddDate", DateTime.Now);
5151            empBenefit[9] = new SqlParameter("@EntryBy", emp.EntryBy);
5152            empBenefit[10] = new SqlParameter("@UpdateDate", DateTime.Now);
5153            empBenefit[11] = new SqlParameter("@UpdateBy", emp.UpdateBy);
5154            empBenefit[12] = new SqlParameter("@ClientRegistrationID", emp.ClientRegistrationID);
5155            empBenefit[13] = new SqlParameter("@result", SqlDbType.Int);
5156            empBenefit[13].Direction = ParameterDirection.Output;
5157            empBenefit[14] = new SqlParameter("@FromDate", emp.FromDate);
5158            empBenefit[15] = new SqlParameter("@ToDate", emp.ToDate);
5159            empBenefit[16] = new SqlParameter("@AdditionalInfo", emp.AdditionalInfo);
5160            if (useTransaction)
5161            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empBenefit); }
5162            else
5163            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empBenefit); }
5164            // return 1 if executed successfully
5165            completed = Convert.ToInt32(empBenefit[13] != null ? empBenefit[13].SqlValue.ToString() : "0");
5166            return completed;
5167        }
5168
5169        public int AddUpdatePensionInvestment(tblCurrent_Pension_Investment_Arrangements pension, bool useTransaction, SqlTransaction tran, SqlConnection con)
5170        {
5171            int completed = 0;
5172            string proc = (pension.tblCurrent_Pension_Investment_ArrangementsID == 0 ? "tblInserttblCurrent_Pension_Investment_Arrangements" : "tblUpdateCurrent_Pension_Investment_Arrangements");
5173
5174            SqlParameter[] pensionInv = new SqlParameter[17];
5175            pensionInv[0] = new SqlParameter("@tblCurrent_Pension_Investment_ArrangementsID", pension.tblCurrent_Pension_Investment_ArrangementsID);
5176            pensionInv[1] = new SqlParameter("@ReferId ", pension.ReferId);
5177            pensionInv[2] = new SqlParameter("@Owner", pension.Owner);
5178            pensionInv[3] = new SqlParameter("@Type_Of_Policy", pension.Type_Of_Policy);
5179            pensionInv[4] = new SqlParameter("@Provider", pension.Provider);
5180            pensionInv[5] = new SqlParameter("@Sum_Assured", pension.Sum_Assured);
5181            pensionInv[6] = new SqlParameter("@Monthly_Premium", pension.Monthly_Premium);
5182            pensionInv[7] = new SqlParameter("@End_Date_Term", pension.End_Date_Term);
5183            pensionInv[8] = new SqlParameter("@In_Trust", pension.In_Trust);
5184            pensionInv[9] = new SqlParameter("@IsActive", pension.IsActive);
5185            pensionInv[10] = new SqlParameter("@IsDeleted", pension.IsDeleted);
5186            pensionInv[11] = new SqlParameter("@AddDate", DateTime.Now);
5187            pensionInv[12] = new SqlParameter("@EntryBy", pension.EntryBy);
5188            pensionInv[13] = new SqlParameter("@UpdateDate", DateTime.Now);
5189            pensionInv[14] = new SqlParameter("@UpdateBy", pension.UpdateBy);
5190            pensionInv[15] = new SqlParameter("@ClientRegistrationID", pension.ClientRegistrationID);
5191            pensionInv[16] = new SqlParameter("@result", SqlDbType.Int);
5192            pensionInv[16].Direction = ParameterDirection.Output;
5193
5194            if (useTransaction)
5195            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, pensionInv); }
5196            else
5197            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, pensionInv); }
5198            // return 1 if executed successfully
5199            completed = Convert.ToInt32(pensionInv[16] != null ? pensionInv[16].SqlValue.ToString() : "0");
5200            return completed;
5201        }
5202
5203        public int AddUpdateCurrentBuildingContent(tblCurrent_Buildings_Contents_Arrangements building, bool useTransaction, SqlTransaction tran, SqlConnection con)
5204        {
5205            int completed = 0;
5206            string proc = (building.Current_Buildings_Contents_ArrangementsID == 0 ? "tblInsertCurrent_Buildings_Contents_Arrangements" : "tblUpdateCurrent_Buildings_Contents_Arrangements");
5207
5208            SqlParameter[] curBuilding = new SqlParameter[]{
5209            new SqlParameter("@Current_Buildings_Contents_ArrangementsID", building.Current_Buildings_Contents_ArrangementsID),
5210            new SqlParameter("@ReferId", building.ReferId),
5211            new SqlParameter("@Property_Insured", building.Property_Insured),
5212            new SqlParameter("@Owner", building.Owner),
5213            new SqlParameter("@Type_Of_Policy", building.Type_Of_Policy),
5214            new SqlParameter("@Provider", building.Provider),
5215            new SqlParameter("@Sum_Assured", building.Sum_Assured),
5216            new SqlParameter("@Monthly_Premium", building.Monthly_Premium),
5217            new SqlParameter("@Additional_Benefits", building.Additional_Benefits),
5218            new SqlParameter("@Rebuild_Value", building.Rebuild_Value),
5219            new SqlParameter("@Value_Of_Contents", building.Value_Of_Contents),
5220            new SqlParameter("@Specified_Items", building.Specified_Items),
5221            new SqlParameter("@Include", building.Include),
5222            new SqlParameter("@Excess", building.Excess),
5223            new SqlParameter("@Smoke_Alarm", building.Smoke_Alarm),
5224            new SqlParameter("@Free_From_Subsidence_Flooding", building.Free_From_Subsidence_Flooding),
5225            new SqlParameter("@Alarm", building.Alarm),
5226            new SqlParameter("@Locks_Fitted", building.Locks_Fitted),
5227            new SqlParameter("@Additional_Info", building.Additional_Info),
5228            new SqlParameter("@Further_Information", building.Further_Information),
5229            new SqlParameter("@Transfer_Foreign_Currencies_To_Sterling", building.Transfer_Foreign_Currencies_To_Sterling),
5230            new SqlParameter("@IsActive", building.IsActive),
5231            new SqlParameter("@IsDeleted", building.IsDeleted),
5232            new SqlParameter("@AddDate", DateTime.Now),
5233            new SqlParameter("@EntryBy", building.EntryBy),
5234            new SqlParameter("@UpdateDate", DateTime.Now),
5235            new SqlParameter("@UpdateBy", building.UpdateBy),
5236            new SqlParameter("@ClientRegistrationID", building.ClientRegistrationID),
5237            new SqlParameter("@result", SqlDbType.Int) { Direction=ParameterDirection.Output},
5238            new SqlParameter("@RenewalDate", !string.IsNullOrWhiteSpace(building.StrRenewalDate) ? Helper.GetDate(building.StrRenewalDate) : null)
5239        };
5240            // get output parameter
5241            var outPutparam = curBuilding.Where(x => x.Direction == ParameterDirection.Output).FirstOrDefault();
5242
5243            if (useTransaction)
5244            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, curBuilding); }
5245            else
5246            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, curBuilding); }
5247            // return 1 if executed successfully
5248            completed = Convert.ToInt32(outPutparam != null ? outPutparam.SqlValue.ToString() : "0");
5249            return completed;
5250        }
5251
5252        public DataSet GetCurrentBuildingContentList(int clientregistrationId)
5253        {
5254            SqlParameter[] Param = new SqlParameter[]
5255            { 
5256            new SqlParameter("@ClientRegistrationID",clientregistrationId)            
5257            };
5258            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GET_Current_Buildings_Contents_Arrangements", Param);
5259            return ds;
5260
5261        }
5262
5263        public void DeleteCurrentBuildingContent(int Current_Buildings_Contents_ArrangementsId)
5264        {
5265            SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@Current_Buildings_Contents_ArrangementsId", Current_Buildings_Contents_ArrangementsId) };
5266
5267            var command = "delete from tblCurrent_Buildings_Contents_Arrangements  where Current_Buildings_Contents_ArrangementsId = @Current_Buildings_Contents_ArrangementsId";
5268            SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
5269
5270
5271        }
5272
5273        public int AddUpdateMortgageRequirementFactors(tblMortgage_Requirement_Factors mortgage, bool useTransaction, SqlTransaction tran, SqlConnection con)
5274        {
5275            int completed = 0;
5276            string proc = (mortgage.Mortgage_Requirement_FactorsID == 0 ? "tblInsertblMortgage_Requirement_Factors" : "tblUpdateMortgage_Requirement_Factors");
5277
5278            SqlParameter[] mortgageReq = new SqlParameter[26];
5279            mortgageReq[0] = new SqlParameter("@Mortgage_Requirement_FactorsID", mortgage.Mortgage_Requirement_FactorsID);
5280            mortgageReq[1] = new SqlParameter("@ReferId", mortgage.ReferId);
5281            mortgageReq[2] = new SqlParameter("@Type_Of_Rate", mortgage.Type_Of_Rate);
5282            mortgageReq[3] = new SqlParameter("@Term_Of_Deal_Period", mortgage.Term_Of_Deal_Period);
5283            mortgageReq[4] = new SqlParameter("@Add_Fees", mortgage.Add_Fees);
5284            mortgageReq[5] = new SqlParameter("@Free_Valuation", mortgage.Free_Valuation);
5285            mortgageReq[6] = new SqlParameter("@Free_Solicitors", mortgage.Free_Solicitors);
5286            mortgageReq[7] = new SqlParameter("@Offset", mortgage.Offset);
5287            mortgageReq[8] = new SqlParameter("@Speed_Of_Completion", mortgage.Speed_Of_Completion);
5288            mortgageReq[9] = new SqlParameter("@No_Booking_Arrangement_Fee", mortgage.No_Booking_Arrangement_Fee);
5289            mortgageReq[10] = new SqlParameter("@Flexible", mortgage.Flexible);
5290            mortgageReq[11] = new SqlParameter("@Lowest_Monthly_Payment", mortgage.Lowest_Monthly_Payment);
5291            mortgageReq[12] = new SqlParameter("@Lowest_APR", mortgage.Lowest_APR);
5292            mortgageReq[13] = new SqlParameter("@Lowest_Cost_Over_Term", mortgage.Lowest_Cost_Over_Term);
5293            mortgageReq[14] = new SqlParameter("@Capped_Upper_Limit", mortgage.Capped_Upper_Limit);
5294            mortgageReq[15] = new SqlParameter("@Access_To_Cash_Back", mortgage.Access_To_Cash_Back);
5295            mortgageReq[16] = new SqlParameter("@No_ERP", mortgage.No_ERP);
5296            mortgageReq[17] = new SqlParameter("@How_Will_You_Repay_The_Mortgage", mortgage.How_Will_You_Repay_The_Mortgage);
5297            mortgageReq[18] = new SqlParameter("@IsActive", mortgage.IsActive);
5298            mortgageReq[19] = new SqlParameter("@IsDeleted", mortgage.IsDeleted);
5299            mortgageReq[20] = new SqlParameter("@AddDate", DateTime.Now);
5300            mortgageReq[21] = new SqlParameter("@EntryBy", mortgage.EntryBy);
5301            mortgageReq[22] = new SqlParameter("@UpdateDate", DateTime.Now);
5302            mortgageReq[23] = new SqlParameter("@UpdateBy", mortgage.UpdateBy);
5303            mortgageReq[24] = new SqlParameter("@ClientRegistrationID", mortgage.ClientRegistrationID);
5304            mortgageReq[25] = new SqlParameter("@result", SqlDbType.Int);
5305            mortgageReq[25].Direction = ParameterDirection.Output;
5306
5307
5308            if (useTransaction)
5309            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, mortgageReq); }
5310            else
5311            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, mortgageReq); }
5312            // return 1 if executed successfully
5313            completed = Convert.ToInt32(mortgageReq[24] != null ? mortgageReq[24].SqlValue.ToString() : "0");
5314            return completed;
5315        }
5316
5317        public int GetRegistar(string Email, string Password, bool useTransaction, SqlTransaction tran, SqlConnection con)
5318        {
5319
5320            int completed = 0;
5321            try
5322            {
5323                SqlParameter[] sqlParameter = new SqlParameter[3];
5324
5325                sqlParameter[0] = new SqlParameter("@UserName", Email);
5326                sqlParameter[1] = new SqlParameter("@Password", Password);
5327
5328                sqlParameter[2] = new SqlParameter("@ClientRegistrationID", SqlDbType.Int);
5329                sqlParameter[2].Direction = ParameterDirection.Output;
5330
5331
5332                SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "GetClientRegistrationID", sqlParameter);
5333
5334                // return 1 if executed successfully
5335                completed = Convert.ToInt32(sqlParameter[2] != null ? sqlParameter[2].SqlValue.ToString() : "0");
5336            }
5337            catch { }
5338            return completed;
5339
5340
5341
5342        }
5343        public DataSet GetClientRegistrationID(Int32 ClientRegistrationID)
5344        {
5345
5346            SqlParameter[] Param = new SqlParameter[1];
5347            Param[0] = new SqlParameter("@ClientRegistrationID", ClientRegistrationID);
5348            // commented on 20/10/2014
5349            //  DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "ClientRegistrationID", Param);
5350            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetClientRegistrationListV1", Param);
5351            return ds;
5352        }
5353
5354        // get commitments
5355        public DataSet GetCommitments(int ClientRegistrationID)
5356        {
5357            SqlParameter[] Param = new SqlParameter[]
5358            { 
5359            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5360            };
5361            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GelCommitmentList", Param);
5362            return ds;
5363        }
5364
5365        public int DeleteCommitments(int id)
5366        {
5367            int result;
5368            SqlParameter[] Param = new SqlParameter[]
5369            { 
5370            new SqlParameter("@CurrentCommitmentsId",id)            
5371            };
5372            result =  SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCommitments", Param);
5373            return result;
5374        }
5375
5376        // get residential mortgage
5377        public DataSet GetResidentialMortgage(int ClientRegistrationID)
5378        {
5379            SqlParameter[] Param = new SqlParameter[]
5380            { 
5381            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5382            };
5383            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetResidentialMortgage", Param);
5384            return ds;
5385        }
5386        public int DeleteResidentialMortgage(int id)
5387        {
5388            int completed = 0;
5389            SqlParameter[] Param = new SqlParameter[]
5390            { 
5391            new SqlParameter("@ResidentialMortgageID",id)            
5392            };
5393            completed = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteResidentialMortgage", Param);
5394            return completed;
5395        }
5396
5397        // New Mortgage Property Details created on 19/01/2015
5398        public DataSet GetNewMortgagePropertyDetails(int ClientRegistrationID)
5399        {
5400            SqlParameter[] Param = new SqlParameter[]
5401            { 
5402            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5403            };
5404            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetNewMortgagePropertyDetails", Param);
5405            return ds;
5406        }
5407        public int DeleteNewMortgagePropertyDetails(int id)
5408        {
5409            int result;
5410            SqlParameter[] Param = new SqlParameter[]
5411            { 
5412            new SqlParameter("@ID",id)            
5413            };
5414            result = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteNewMortgagePropertyDetails", Param);
5415            return result;
5416        }
5417
5418        public DataSet GetNewMortgagePropertyAddress(int id)
5419        {
5420            SqlParameter[] Param = new SqlParameter[]
5421            { 
5422            new SqlParameter("@tblAdditional_Property_InfoID",id)            
5423            };
5424            
5425            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, "select * from tblAdditional_Property_Info where tblAdditional_Property_InfoID=@tblAdditional_Property_InfoID", Param);
5426            return ds;
5427        
5428        }
5429
5430        // Investment Portfolio
5431
5432        public DataSet GetInvestmentPortfolio(int ClientRegistrationID)
5433        {
5434            SqlParameter[] Param = new SqlParameter[]
5435            { 
5436            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5437            };
5438            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetOtherMortgage", Param);
5439            return ds;
5440        }
5441        public void DeleteInvestmentPortfolio(int id)
5442        {
5443            SqlParameter[] Param = new SqlParameter[]
5444            { 
5445            new SqlParameter("@Other_MortgagesID",id)            
5446            };
5447            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteOtherMortgage", Param);
5448        }
5449
5450        // current protection
5451        public DataSet GetCurrentProtection(int ClientRegistrationID)
5452        {
5453            SqlParameter[] Param = new SqlParameter[]
5454            { 
5455            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5456            };
5457            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetCurrentProtection", Param);
5458            return ds;
5459        }
5460        public void DeleteCurrentProtection(int id)
5461        {
5462            SqlParameter[] Param = new SqlParameter[]
5463            { 
5464            new SqlParameter("@Current_Protection_ArrangementsID",id)            
5465            };
5466            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCurrentProtection", Param);
5467        }
5468
5469        //// Employment Benefit 20-01-2015
5470        //public DataSet GetEmploymentBenefit(int ClientRegistrationID)
5471        //{
5472        //    SqlParameter[] Param = new SqlParameter[]
5473        //    { 
5474        //    new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5475        //    };
5476        //    DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetEmploymentBenefit", Param);
5477        //    return ds;
5478        //}
5479        //public void DeleteEmploymentBenefit(int id)
5480        //{
5481        //    SqlParameter[] Param = new SqlParameter[]
5482        //    { 
5483        //    new SqlParameter("@EmploymentBenefitID",id)            
5484        //    };
5485        //    SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteEmploymentBenefit", Param);
5486        //}
5487
5488
5489        // current pension arrangements
5490
5491        public DataSet GetCurrentPensionInvestments(int ClientRegistrationID)
5492        {
5493            SqlParameter[] Param = new SqlParameter[]
5494            { 
5495            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5496            };
5497            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetCurrentPensionInvestments", Param);
5498            return ds;
5499        }
5500
5501        public void DeleteCurrentPensionInvestments(int id)
5502        {
5503            SqlParameter[] Param = new SqlParameter[]
5504            { 
5505            new SqlParameter("@tblCurrent_Pension_Investment_ArrangementsID",id)            
5506            };
5507            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteCurrentPensionInvestments", Param);
5508        }
5509
5510
5511        public int UpdateMortgageFact()
5512        {
5513            return 0;
5514        }
5515
5516        // update contactid into client registration
5517        public int UpdateContactIDToClient(int contactid, int clientregistrationid)
5518        {
5519            SqlParameter[] par = new SqlParameter[]
5520            {
5521            new SqlParameter("@ContactId",contactid),
5522            new SqlParameter("@ClientRegistrationID",clientregistrationid)
5523            };
5524            return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "proc_UpdateClientID", par);
5525        }
5526
5527
5528        // update contactid into client registration
5529        public int DeactivateClientRegistrationOnUpdateByClient(int contactid, int clientregistrationid)
5530        {
5531            SqlParameter[] par = new SqlParameter[]
5532            {
5533            new SqlParameter("@ContactId",contactid),
5534            new SqlParameter("@ClientRegistrationID",clientregistrationid)
5535            };
5536            return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "DeactivateClientRegistrationOnUpdateByClient", par);
5537        }
5538
5539        public void DeleteAddress(int addressID)
5540        {
5541            SqlParameter[] Param = new SqlParameter[]
5542            { 
5543            new SqlParameter("@FactFindAddressID",addressID)            
5544            };
5545            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteAddress", Param);
5546
5547        }
5548
5549        public void DeleteDependent(int dependentid)
5550        {
5551            SqlParameter[] Param = new SqlParameter[]
5552            { 
5553            new SqlParameter("@DependentID",dependentid)            
5554            };
5555            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteDependent", Param);
5556
5557        }
5558
5559        public DataSet GetApplicantsAddressForMortgage(int clientregistrationid)
5560        {
5561            SqlParameter[] par = new SqlParameter[]
5562            {
5563             new SqlParameter("@ClientRegistrationID",clientregistrationid)
5564            };
5565            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetApplcantsAddressesForMortgage", par);
5566            return ds;
5567        }
5568        public DataSet GetUser(int ClientRegistrationID)
5569        {
5570            SqlParameter[] Param = new SqlParameter[]
5571            {
5572            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)
5573            };
5574            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityUserDetail", Param);
5575            return ds;
5576        }
5577
5578        public DataSet GetPropertyList(int ClientRegistrationID)
5579        {
5580            SqlParameter[] Param = new SqlParameter[]
5581            { 
5582            new SqlParameter("@ClientRegistrationID",ClientRegistrationID)            
5583            };
5584            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityPropertyDetails", Param);
5585            return ds;
5586        }
5587
5588        public DataSet GetPropertyAddressList(string postcode)
5589        {
5590            SqlParameter[] Param = new SqlParameter[]
5591            {
5592            new SqlParameter("@postcode",postcode)
5593            };
5594            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityAddressDetails", Param);
5595            return ds;
5596        }
5597
5598        public DataSet GetNewlyAddedPropertyList(int MortgageId)
5599        {
5600            SqlParameter[] Param = new SqlParameter[]
5601            {
5602            new SqlParameter("@MortgageId",MortgageId)
5603            };
5604            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_EquityPropertyDetailsByMortgageId", Param);
5605            return ds;
5606        }
5607
5608        /***********************************************************************
5609         * Methods for Fact Find
5610         *  
5611         * 
5612         * 
5613         * 
5614         * Created On : 17/11/2014
5615         * *********************************************************************/
5616
5617        public int FactFind_AddEmploymentAndIncome(tblEmploymentAndIncome employmentAndIncome, bool useTransaction, SqlTransaction tran, SqlConnection con)
5618        {
5619            int completed;
5620            string proc = "FactFind_InsertUpdateEmploymentAndIncome";
5621
5622            SqlParameter[] empandInc = new SqlParameter[29];
5623            empandInc[0] = new SqlParameter("@ClientRegistrationID", employmentAndIncome.ClientRegistrationID);
5624            empandInc[1] = new SqlParameter("@JobTitle", employmentAndIncome.JobTitle);
5625            empandInc[2] = new SqlParameter("@NatureOfOccupation", employmentAndIncome.NatureOfOccupation);
5626            empandInc[3] = new SqlParameter("@NINumber", employmentAndIncome.NINumber);
5627            empandInc[4] = new SqlParameter("@AnticipatedRetirementAge", employmentAndIncome.AnticipatedRetirementAge);
5628            empandInc[5] = new SqlParameter("@EmploymentStatus", employmentAndIncome.EmploymentStatus);
5629            empandInc[6] = new SqlParameter("@ProbationPeriod", employmentAndIncome.ProbationPeriod);
5630            empandInc[7] = new SqlParameter("@NameOfEmployer", employmentAndIncome.NameOfEmployer);
5631            empandInc[8] = new SqlParameter("@AddressOfEmployer", employmentAndIncome.AddressOfEmployer);
5632            empandInc[9] = new SqlParameter("@AnnualCommission", employmentAndIncome.AnnualCommission);
5633            empandInc[10] = new SqlParameter("@AnnualOvertime", employmentAndIncome.AnnualOvertime);
5634            empandInc[11] = new SqlParameter("@Other", employmentAndIncome.Other);
5635            empandInc[12] = new SqlParameter("@GrossTotal", employmentAndIncome.GrossTotal);
5636            empandInc[13] = new SqlParameter("@GrossMonthly", employmentAndIncome.GrossMonthly);
5637            empandInc[14] = new SqlParameter("@IsActive", employmentAndIncome.IsActive);
5638            empandInc[15] = new SqlParameter("@IsDeleted", employmentAndIncome.IsDeleted);
5639            empandInc[16] = new SqlParameter("@AddDate", DateTime.Now);
5640            empandInc[17] = new SqlParameter("@EntryBy", employmentAndIncome.EntryBy);
5641            empandInc[18] = new SqlParameter("@UpdateDate", DateTime.Now);
5642            empandInc[19] = new SqlParameter("@UpdateBy", employmentAndIncome.UpdateBy);
5643            empandInc[20] = new SqlParameter("@GrossBasicSalary", employmentAndIncome.GrossBasicSalary);
5644            empandInc[21] = new SqlParameter("@PeriodWithEmployer", employmentAndIncome.PeriodWithEmployer);
5645            empandInc[22] = new SqlParameter("@result", SqlDbType.Int);
5646            empandInc[22].Direction = ParameterDirection.Output;
5647
5648            // add id column
5649            empandInc[23] = new SqlParameter("@EmploymentIncomeId", employmentAndIncome.EmploymentIncomeId);
5650            empandInc[24] = new SqlParameter("@ReferId", employmentAndIncome.ReferId);
5651            // bonus
5652            empandInc[25] = new SqlParameter("@BonusY1", employmentAndIncome.BonusY1);
5653            empandInc[26] = new SqlParameter("@BonusY2", employmentAndIncome.BonusY2);
5654            empandInc[27] = new SqlParameter("@BonusY3", employmentAndIncome.BonusY3);
5655            empandInc[28] = new SqlParameter("@PreviousAddressOfEmployer", employmentAndIncome.PreviousAddressOfEmployer);
5656
5657            if (useTransaction)
5658            { SqlHelper.ExecuteNonQueryWithTransaction(CommandType.StoredProcedure, proc, tran, con, empandInc); }
5659            else
5660            { SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, proc, empandInc); }
5661
5662            // return 1 if executed successfully
5663            completed = Convert.ToInt32(empandInc[22] != null ? empandInc[22].SqlValue.ToString() : "0");
5664
5665
5666            return completed;
5667
5668        }
5669
5670
5671        // get commitments
5672
5673
5674        public DataSet GetSelfEmployment(int ClientRegistrationID)
5675        {
5676            SqlParameter[] Param = new SqlParameter[]
5677            { 
5678            new SqlParameter("@clientRegistrationID",ClientRegistrationID)            
5679            };
5680            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetSelfEmployed", Param);
5681            return ds;
5682        }
5683
5684        public void DeleteSelfEmployment(int id, string TypeOfEmployment)
5685        {
5686            SqlParameter[] Param = new SqlParameter[]
5687            { 
5688            new SqlParameter("@selfEmployedId",id),    
5689            new SqlParameter("@typeOfEmployment",TypeOfEmployment)
5690            };
5691            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteSelfEmployed", Param);
5692
5693        }
5694        public DataSet GetEmploymentAndIncome(int ClientRegistrationID)
5695        {
5696            SqlParameter[] Param = new SqlParameter[]
5697            { 
5698            new SqlParameter("@clientRegistrationID",ClientRegistrationID)            
5699            };
5700            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "FactFind_GetEmploymentAndIncome", Param);
5701            return ds;
5702        }
5703
5704        public void DeleteEmploymentIncome(int id)
5705        {
5706            SqlParameter[] Param = new SqlParameter[]
5707            { 
5708            new SqlParameter("@employmentAndIncomeId",id)            
5709            };
5710            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "FactFind_DeleteEmploymentAndIncome", Param);
5711
5712        }
5713        //end block  DeleteEmploymentIncome
5714
5715
5716        // 02 06 2015
5717
5718
5719        public List<tblUnEmployment> GetUnEmploymentList(int ClientRegistrationID, int UnEmploymentID)
5720        {
5721            string command = "";
5722            SqlParameter[] Param = new SqlParameter[]
5723            {
5724                new SqlParameter("@ClientRegistrationID",ClientRegistrationID) , 
5725                new SqlParameter("@UnEmploymentID",UnEmploymentID)         
5726
5727            };
5728
5729            if (UnEmploymentID == 0)
5730            {
5731                command = "select * from tblUnEmployment where  ClientRegistrationID=@ClientRegistrationID";
5732            }
5733            else
5734            {
5735
5736                command = "select * from tblUnEmployment where  ClientRegistrationID=@ClientRegistrationID and UnEmploymentID=@UnEmploymentID";
5737            }
5738
5739            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
5740
5741            return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblUnEmployment>(ds.Tables[0]).ToList<tblUnEmployment>();
5742
5743        }
5744        public List<tblSelfEmployed> GetSelfEmployedList(int ClientRegistrationID, int SelfEmployedId)
5745        {
5746            string command = "";
5747            SqlParameter[] Param = new SqlParameter[]
5748            {
5749                new SqlParameter("@ClientRegistrationID",ClientRegistrationID) , 
5750                new SqlParameter("@SelfEmployedId",SelfEmployedId)         
5751
5752            };
5753
5754            if (SelfEmployedId == 0)
5755            {
5756                command = "select * from tblSelfEmployed where  ClientRegistrationID=@ClientRegistrationID";
5757            }
5758            else
5759            {
5760
5761                command = "select * from tblSelfEmployed where  ClientRegistrationID=@ClientRegistrationID and SelfEmployedId=@SelfEmployedId";
5762            }
5763
5764            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
5765
5766            return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblSelfEmployed>(ds.Tables[0]).ToList<tblSelfEmployed>();
5767
5768        }
5769
5770        public int AddUpdateUnEmployed(tblUnEmployment model)
5771        {
5772            var command = "";
5773            int result;
5774            SqlParameter[] Param = new SqlParameter[]
5775            { 
5776             
5777              new SqlParameter("@UnEmploymentID",model.UnEmploymentID) , 
5778              new SqlParameter("@ClientRegistrationID",model.ClientRegistrationID) , 
5779              new SqlParameter("@DateOfUnEmployment",!string.IsNullOrWhiteSpace(model.StrDateOfUnemployment) ? Helper.GetDate(model.StrDateOfUnemployment) : null), 
5780              new SqlParameter("@DateOfReemployment",!string.IsNullOrWhiteSpace(model.StrDateOfReemployment) ? Helper.GetDate(model.StrDateOfReemployment) : null),  
5781              new SqlParameter("@ReasonForUnemployment",model.ReasonForUnemployment),
5782              new SqlParameter("@IsActive",true)  ,
5783              new SqlParameter("@UpdatedDate",DateTime.Now),
5784              new SqlParameter("@UpdatedBy",""),
5785             // new SqlParameter("@Retired", !string.IsNullOrWhiteSpace(model.Retired) ? model.Retired : "no value")
5786             new SqlParameter("@Retired", model.Retired)
5787            };
5788
5789            if (model.UnEmploymentID == 0)
5790            {
5791                command = @"insert into
5792                            tblUnEmployment
5793                            (                           
5794                            ClientRegistrationID,
5795                            DateOfUnEmployment,
5796                            DateOfReemployment,
5797                            ReasonForUnemployment,
5798                            IsActive,              
5799                            UpdatedDate,
5800                            UpdatedBy,
5801                            Retired
5802                            )
5803                            values
5804                            (                           
5805                            @ClientRegistrationID,
5806                            @DateOfUnEmployment,
5807                            @DateOfReemployment,
5808                            @ReasonForUnemployment,
5809                            @IsActive,                       
5810                            @UpdatedDate,
5811                            @UpdatedBy,
5812                            @Retired
5813                            )";
5814
5815            }
5816            else
5817            {
5818                command = @"update tblUnEmployment
5819                            set                      
5820                            ClientRegistrationID= @ClientRegistrationID,               
5821                            DateOfUnEmployment=@DateOfUnEmployment,
5822                            DateOfReemployment=@DateOfReemployment,
5823                            ReasonForUnemployment=@ReasonForUnemployment,
5824                            IsActive= @IsActive,              
5825                            UpdatedDate=@UpdatedDate,
5826                            UpdatedBy= @UpdatedBy,
5827                            Retired = @Retired
5828                            where UnEmploymentID= @UnEmploymentID
5829                            ";
5830            }
5831
5832           result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
5833            return result;  
5834        }
5835
5836
5837        public int AddUpdateSelfEmployed(tblSelfEmployed model)
5838        {
5839            var command = "";
5840            int result;
5841            SqlParameter[] param = new SqlParameter[]
5842            {
5843                
5844                new SqlParameter("@SelfEmployedId",model.SelfEmployedId),
5845                new SqlParameter("@ClientRegistrationID",model.ClientRegistrationID),
5846                new SqlParameter("@TypeOfSelfEmployment",model.TypeOfSelfEmployment),
5847                new SqlParameter("@NameOfComapany",model.NameOfComapany),
5848                new SqlParameter("@Shareholding",model.Shareholding),
5849                new SqlParameter("@Position",model.Position),
5850                new SqlParameter("@IncorporationDate", model.IncorporationDate),  // TODO : casting
5851                new SqlParameter("@IsActive",true),
5852                new SqlParameter("@AddDate",DateTime.Now),
5853                new SqlParameter("@UpdateDate",null),
5854                new SqlParameter("@OtherIncomeInfo",model.OtherIncomeInfo),
5855                new SqlParameter("@AccountantFName",model.AccountantFName),
5856                new SqlParameter("@AccountantIContact",model.AccountantIContact),
5857                new SqlParameter("@AccountantPhone",model.AccountantPhone),
5858                new SqlParameter("@AccountantFAX",model.AccountantFAX),
5859                new SqlParameter("@AccountantQualification",model.AccountantQualification),
5860                new SqlParameter("@TurnoverY1",model.TurnoverY1),
5861                new SqlParameter("@TurnoverY2",model.TurnoverY2),
5862                new SqlParameter("@TurnoverY3",model.TurnoverY3),
5863                new SqlParameter("@NetProfitY1",model.NetProfitY1),
5864                new SqlParameter("@NetProfitY2",model.NetProfitY2),
5865                new SqlParameter("@NetProfitY3",model.NetProfitY3),
5866                new SqlParameter("@SalaryY1",model.SalaryY1),
5867                new SqlParameter("@SalaryY2",model.SalaryY2),
5868                new SqlParameter("@SalaryY3",model.SalaryY3),
5869                new SqlParameter("@DividendsY1",model.DividendsY1),
5870                new SqlParameter("@DividendsY2",model.DividendsY2),
5871                new SqlParameter("@DividendsY3",model.DividendsY3),
5872                new SqlParameter("@DateOfUnemployment",model.DateOfReemployment),   // TODO : casting
5873                new SqlParameter("@DateOfReemployment",model.DateOfReemployment),   // TODO : casting
5874                new SqlParameter("@ReasonForUnemployment",model.ReasonForUnemployment),
5875                new SqlParameter("@FirmName",model.FirmName),
5876                new SqlParameter("@IndividualContact",model.IndividualContact),
5877                new SqlParameter("@PhoneNo",model.PhoneNo),
5878                new SqlParameter("@Fax",model.Fax),
5879                new SqlParameter("@Qualification",model.Qualification)        
5880            };
5881
5882            if (model.SelfEmployedId == 0)
5883            {
5884                command = @"INSERT INTO [dbo].[tblSelfEmployed]
5885                       ([ClientRegistrationID]
5886                       ,[TypeOfSelfEmployment]
5887                       ,[NameOfComapany]
5888                       ,[Shareholding]
5889                       ,[Position]
5890                       ,[IncorporationDate]
5891                       ,[IsActive]         
5892                       ,[AddDate]         
5893                       ,[UpdateDate]        
5894                       ,[OtherIncomeInfo]
5895                       ,[AccountantFName]
5896                       ,[AccountantIContact]
5897                       ,[AccountantPhone]
5898                       ,[AccountantFAX]
5899                       ,[AccountantQualification]
5900                       ,[TurnoverY1]
5901                       ,[TurnoverY2]
5902                       ,[TurnoverY3]
5903                       ,[NetProfitY1]
5904                       ,[NetProfitY2]
5905                       ,[NetProfitY3]
5906                       ,[SalaryY1]
5907                       ,[SalaryY2]
5908                       ,[SalaryY3]
5909                       ,[DividendsY1]
5910                       ,[DividendsY2]
5911                       ,[DividendsY3]
5912                       ,[DateOfUnemployment]
5913                       ,[DateOfReemployment]
5914                       ,[ReasonForUnemployment]
5915                       ,[FirmName]
5916                       ,[IndividualContact]
5917                       ,[PhoneNo]
5918                       ,[Fax]
5919                       ,[Qualification])
5920                 VALUES
5921                       (
5922                        @ClientRegistrationID
5923                       ,@TypeOfSelfEmployment
5924                       ,@NameOfComapany
5925                       ,@Shareholding
5926                       ,@Position
5927                       ,@IncorporationDate
5928                       ,@IsActive           
5929                       ,@AddDate           
5930                       ,@UpdateDate        
5931                       ,@OtherIncomeInfo
5932                       ,@AccountantFName
5933                       ,@AccountantIContact
5934                       ,@AccountantPhone
5935                       ,@AccountantFAX
5936                       ,@AccountantQualification
5937                       ,@TurnoverY1
5938                       ,@TurnoverY2
5939                       ,@TurnoverY3
5940                       ,@NetProfitY1
5941                       ,@NetProfitY2
5942                       ,@NetProfitY3
5943                       ,@SalaryY1
5944                       ,@SalaryY2
5945                       ,@SalaryY3
5946                       ,@DividendsY1
5947                       ,@DividendsY2
5948                       ,@DividendsY3
5949                       ,@DateOfUnemployment
5950                       ,@DateOfReemployment
5951                       ,@ReasonForUnemployment
5952                       ,@FirmName
5953                       ,@IndividualContact
5954                       ,@PhoneNo
5955                       ,@Fax
5956                       ,@Qualification
5957                       )";
5958            }
5959            else
5960            {
5961                command = @"update tblSelfEmployed	
5962                  set   [ClientRegistrationID]  = @ClientRegistrationID
5963                       ,[TypeOfSelfEmployment]=@TypeOfSelfEmployment
5964                       ,[NameOfComapany] =@NameOfComapany
5965                       ,[Shareholding] =@Shareholding
5966                       ,[Position]  =@Position
5967                       ,[IncorporationDate] =@IncorporationDate
5968                       ,[IsActive]    =@IsActive        
5969                       ,[AddDate]        =@AddDate    
5970                       ,[UpdateDate]       =@UpdateDate   
5971                       ,[OtherIncomeInfo]  =@OtherIncomeInfo
5972                       ,[AccountantFName]    =@AccountantFName
5973                       ,[AccountantIContact] =@AccountantIContact
5974                       ,[AccountantPhone] =@AccountantPhone
5975                       ,[AccountantFAX] =@AccountantFAX
5976                       ,[AccountantQualification]=@AccountantQualification
5977                       ,[TurnoverY1] =@TurnoverY1
5978                       ,[TurnoverY2]  =@TurnoverY2
5979                       ,[TurnoverY3]   =@TurnoverY3
5980                       ,[NetProfitY1]=@NetProfitY1
5981                       ,[NetProfitY2]=@NetProfitY2
5982                       ,[NetProfitY3]=@NetProfitY3
5983                       ,[SalaryY1] =@SalaryY1
5984                       ,[SalaryY2] =@SalaryY2
5985                       ,[SalaryY3]=@SalaryY3
5986                       ,[DividendsY1]=@DividendsY1
5987                       ,[DividendsY2]=@DividendsY2
5988                       ,[DividendsY3]=@DividendsY3
5989                       ,[DateOfUnemployment] =@DateOfUnemployment
5990                       ,[DateOfReemployment]=@DateOfReemployment
5991                       ,[ReasonForUnemployment] =@ReasonForUnemployment
5992                       ,[FirmName]=@FirmName
5993                       ,[IndividualContact]=@IndividualContact
5994                       ,[PhoneNo] =@PhoneNo
5995                       ,[Fax]   =@Fax
5996                       ,[Qualification] =@Qualification
5997                where SelfEmployedId= @SelfEmployedId";
5998            }
5999          result =  SqlHelper.ExecuteNonQuery(CommandType.Text, command, param);
6000            return result;
6001        }
6002
6003        public int DeleteUnEmployed(int UnEmploymentID)
6004        {
6005            int result;
6006            SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@UnEmploymentID", UnEmploymentID) };
6007
6008            var command = "delete from tblUnEmployment  where UnEmploymentID = @UnEmploymentID";
6009            result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
6010            return result;
6011        }
6012
6013        public int DeleteEmployed(int EmploymentAndIncomeID)
6014        {
6015            int result;
6016            SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@EmploymentAndIncomeID", EmploymentAndIncomeID) };
6017
6018            var command = "delete from tblEmploymentAndIncome  where EmploymentIncomeID = @EmploymentAndIncomeID";
6019            result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
6020            return result;
6021        }
6022
6023        public int DeleteSelfEmployed(int SelfEmployedId)
6024        {
6025            int result;
6026            SqlParameter[] Param = new SqlParameter[] { new SqlParameter("@SelfEmployedId", SelfEmployedId) };
6027
6028            var command = "delete from tblSelfEmployed  where SelfEmployedId = @SelfEmployedId";
6029            result = SqlHelper.ExecuteNonQuery(CommandType.Text, command, Param);
6030            return result;
6031        }
6032
6033        // employed [employment and income]
6034
6035        public List<tblEmploymentAndIncome> GetEmployedList(int ClientRegistrationID, int EmploymentAndIncomeID)
6036        {
6037            string command = "";
6038            SqlParameter[] Param = new SqlParameter[]
6039            {
6040                new SqlParameter("@ClientRegistrationID",ClientRegistrationID) , 
6041                new SqlParameter("@EmploymentAndIncomeID",EmploymentAndIncomeID)         
6042
6043            };
6044
6045            if (EmploymentAndIncomeID == 0)
6046            {
6047                command = "select * from tblEmploymentAndIncome where  ClientRegistrationID=@ClientRegistrationID";
6048            }
6049            else
6050            {
6051
6052                command = "select * from tblEmploymentAndIncome where  ClientRegistrationID=@ClientRegistrationID and EmploymentIncomeID=@EmploymentAndIncomeID";
6053            }
6054
6055            DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, command, Param);
6056
6057            return Occfinance.Code.DataTableHelper.CreateListFromTableForAll<tblEmploymentAndIncome>(ds.Tables[0]).ToList<tblEmploymentAndIncome>();
6058
6059        }
6060
6061
6062    }
6063
6064
6065    /*****************************************************
6066    DataTable helper for MortgageFactFind
6067    Created On : 20/10/2014
6068    Ref :http://www.mroma.net/blog/c-helper-functions-to-map-a-datatable-or-datarow-to-a-class-object/
6069    Modified for Defalut listing objects
6070    ******************************************************/
6071
6072    public static class DataTableHelper
6073    {
6074
6075        // function that set the given object from the given data row
6076        public static void SetItemFromRow<T>(T item, DataRow row)
6077            where T : new()
6078        {
6079            // go through each column
6080            foreach (DataColumn c in row.Table.Columns)
6081            {
6082                // find the property for the column
6083                PropertyInfo p = item.GetType().GetProperty(c.ColumnName);
6084
6085                // if exists, set the value
6086                if (p != null && row[c] != DBNull.Value)
6087                {
6088                    p.SetValue(item, row[c], null);
6089                }
6090
6091            }
6092        }
6093
6094        // function that creates an object from the given data row
6095        public static T CreateItemFromRow<T>(DataRow row)
6096            where T : new()
6097        {
6098            // create a new object
6099            T item = new T();
6100
6101            // set the item
6102            SetItemFromRow(item, row);
6103
6104            // return 
6105            return item;
6106        }
6107
6108        // function that creates a list of an object from the given data table only for fact find (don't change)
6109        public static List<T> CreateListFromTable<T>(DataTable tbl)
6110            where T : new()
6111        {
6112            // define return list
6113            List<T> lst = new List<T>();
6114
6115            // alocal variable
6116            int count = 0;
6117
6118            // go through each row
6119            foreach (DataRow r in tbl.Rows)
6120            {
6121                // add to the list
6122                lst.Add(CreateItemFromRow<T>(r));
6123                count++;
6124            }
6125            // set defaults list with 2 members
6126            if (count == 0)
6127            { lst = new List<T> { new T(), new T() }; }
6128            else if (count == 1)
6129            { lst.Add(new T()); }
6130
6131            // return the list
6132            return lst;
6133        }
6134
6135
6136        // function that creates a list of an object from the given data table other than fact find
6137        public static List<T> CreateListFromTableForAll<T>(DataTable tbl)
6138            where T : new()
6139        {
6140            // define return list
6141            List<T> lst = new List<T>();
6142
6143            // alocal variable
6144            int count = 0;
6145
6146            // go through each row
6147            foreach (DataRow r in tbl.Rows)
6148            {
6149                // add to the list
6150                lst.Add(CreateItemFromRow<T>(r));
6151                count++;
6152            }
6153            // return the list
6154            return lst;
6155        }
6156
6157    }
6158
6159}