context: using System;
  using System.Collections;
  using System.Collections.Generic;
  using System.Data.SqlTypes;
  using System.IO;
  using System.Linq;
  using System.Text;
  using CmsData;
  using CmsData.View;
  using LumenWorks.Framework.IO.Csv;
  using UtilityExtensions;
  
  namespace CmsWeb.Models
  {
      public class UploadPeopleModel
      {
          private Dictionary<string, int> names;
          private List<ChangeDetail> psb;
          private List<ChangeDetail> fsb;
          private CMSDataContext Db;
          private CMSDataContext Db2;
          private int PeopleId;
          private bool noupdate;
  
          public UploadPeopleModel(CMSDataContext Db, int PeopleId, bool noupdate, string connectionstring)
          {
              this.Db = Db;
              Db2 = new CMSDataContext(connectionstring);
              this.PeopleId = PeopleId;
              this.noupdate = noupdate;
          }
  
          private void UpdateField(Family f, string[] a, string prop, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                      f.UpdateValue(fsb, prop, a[names[s]]);
          }
  
          private void UpdateField(Person p, string[] a, string prop, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                      p.UpdateValue(psb, prop, a[names[s]]);
          }
  
          private void UpdateField(Person p, string[] a, string prop, string s, object value)
          {
              if (names.ContainsKey(s))
                  if (value != null)
                      p.UpdateValue(psb, prop, value);
          }
  
          private void SetField(Family f, string[] a, string prop, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                      Util.SetProperty(f, prop, a[names[s]]);
          }
  
          private void SetField(Person p, string[] a, string prop, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                      Util.SetProperty(p, prop, a[names[s]]);
          }
  
          private void SetField(Person p, string[] a, string prop, string s, object value)
          {
              if (names.ContainsKey(s))
                  if (value != null)
                      Util.SetProperty(p, prop, value);
          }
          private void SetField(Family f, string[] a, string prop, string s, object value)
          {
              if (names.ContainsKey(s))
                  if (value != null)
                      Util.SetProperty(f, prop, value);
          }
  
          private string GetDigits(string[] a, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                      return a[names[s]].GetDigits();
              return "";
          }
  
          private DateTime? GetDate(Person p, string[] a, string s)
          {
              if (names.ContainsKey(s))
                  if (a[names[s]].HasValue())
                  {
                      DateTime dt;
                      if (DateTime.TryParse(a[names[s]], out dt))
                      {
                          if (dt.Year < 1800)
                              throw new Exception("error on {0} {1}: [{2}]({3})".Fmt(p.FirstName, p.LastName, names[s], a[names[s]]));
                          return dt;
                      }
                  }
              return null;
          }
  
          private int Gender(string[] a)
          {
              if (names.ContainsKey("gender"))
                  if (a[names["gender"]].HasValue())
                  {
                      var v = a[names["gender"]].ToLower().TrimEnd();
                      switch (v)
                      {
                          case "male":
                          case "m":
                              return 1;
                          case "female":
                          case "f":
                              return 2;
                      }
                  }
              return 0;
          }
  
          private int Marital(string[] a)
          {
              if (names.ContainsKey("marital"))
                  if (a[names["marital"]].HasValue())
                  {
                      var v = a[names["marital"]].ToLower().TrimEnd();
                      switch (v)
                      {
                          case "married":
                          case "m":
                              return 20;
                          case "single":
                          case "s":
                              return 10;
                          case "widowed":
                          case "w":
                              return 50;
                          case "divorced":
                          case "d":
                              return 40;
                          case "separated":
                              return 30;
                      }
                  }
              return 0;
          }
  
          private string Title(string[] a)
          {
              if (names.ContainsKey("title"))
                  if (a[names["title"]].HasValue())
                      return a[names["title"]].Truncate(10).TrimEnd();
              return null;
          }
  
          private int Position(string[] a)
          {
              if (names.ContainsKey("position"))
                  if (a[names["position"]].HasValue())
                  {
                      var v = a[names["position"]].ToLower().TrimEnd();
                      switch (v)
                      {
                          case "primary":
                              return 10;
                          case "secondary":
                              return 20;
                          case "child":
                              return 30;
                      }
                  }
              return 10;
          }
          private int? Campus(string[] a)
          {
              if (names.ContainsKey("campus"))
                  if (a[names["campus"]].HasValue())
                      return Campuses[a[names["campus"]]];
              return null;
          }
  
          private Dictionary<string, int> Campuses;
          public bool DoUpload(string text, bool testing = false)
          {
              var rt = Db2.UploadPeopleRuns.OrderByDescending(mm => mm.Id).First();
              var csv = new CsvReader(new StringReader(text), false, '\t');
              var list = csv.ToList();
  
              var list0 = list.First().Select(kk => kk).ToList();
              names = list0.ToDictionary(i => i.TrimEnd(),
                                         i => list0.FindIndex(s => s == i), StringComparer.OrdinalIgnoreCase);
  
              if (names.ContainsKey("campus"))
              {
                  var campuslist = (from li in list.Skip(1)
                                    where li.Length == names.Count
                                    group li by li[names["campus"]]
                                        into campus
                                        where campus.Key.HasValue()
                                        select campus.Key).ToList();
                  var dbc = from c in campuslist
                            join cp in Db.Campus on c equals cp.Description into j
                            from cp in j.DefaultIfEmpty()
                            select new { cp, c };
                  var clist = dbc.ToList();
                  if (clist.Count > 0)
                  {
                      var maxcampusid = 0;
                      if(Db.Campus.Any())
                          maxcampusid = Db.Campus.Max(c => c.Id);
                      foreach (var i in clist)
                          if (i.cp == null)
                          {
                              var cp = new Campu { Description = i.c, Id = ++maxcampusid };
                              if (!testing)
                                  Db.Campus.InsertOnSubmit(cp);
                          }
                  }
              }
              var now = DateTime.Now;
              if (!testing)
                  Db.SubmitChanges();
              Campuses = Db.Campus.ToDictionary(cp => cp.Description, cp => cp.Id);
  
  
gt:             var q = (from li in list.Skip(1)