private void button1_Click_1(object sender, EventArgs e)
{
Excel.Workbook MyBook = null;
Excel.Application MyApp = null;
Excel.Worksheet MySheet = null;
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(@"C:\Users\Francis\Desktop\IBPExcelApp\IBPExcelApplication\bin\Debug\IBP MEMBERS06182014-only.xlsx");
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
int lastRow = 1164;
var list = new List<Lawyer>();
long success = 0;
long fail = 0;
int init = int.Parse(txtstart.Text);
int end = int.Parse(txtend.Text);
new Thread(() =>
{
for (int i = init; i <= end; i++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + i.ToString(), "R" + i.ToString()).Cells.Value;
var data = new Lawyer();
data.RollNumber = MyValues.GetValue(1, 1).ToString() == "-" ? "" : MyValues.GetValue(1, 1).ToString();
data.Chapter = MyValues.GetValue(1, 2).ToString() == "-" ? "" : MyValues.GetValue(1, 2).ToString();
data.Name = MyValues.GetValue(1, 3).ToString() == "-" ? "" : MyValues.GetValue(1, 3).ToString();
data.Address = MyValues.GetValue(1, 4).ToString() == "-" ? "" : MyValues.GetValue(1, 4).ToString();
data.HomePhone = MyValues.GetValue(1, 5).ToString() == "-" ? "" : MyValues.GetValue(1, 5).ToString();
data.WorkAddress = MyValues.GetValue(1, 6).ToString() == "-" ? "" : MyValues.GetValue(1, 6).ToString();
data.WorkPhone = MyValues.GetValue(1, 7).ToString() == "-" ? "" : MyValues.GetValue(1, 7).ToString();
data.LawSchool = MyValues.GetValue(1, 8).ToString() == "-" ? "" : MyValues.GetValue(1, 8).ToString();
data.Occupation = MyValues.GetValue(1, 9).ToString() == "-" ? "" : MyValues.GetValue(1, 9).ToString();
data.Specialization = MyValues.GetValue(1, 10).ToString() == "-" ? "" : MyValues.GetValue(1, 10).ToString();
data.Status = MyValues.GetValue(1, 11).ToString() == "-" ? "" : MyValues.GetValue(1, 11).ToString();
data.BirthDate = (DateTime)MyValues.GetValue(1, 12);
data.BirthPlace = MyValues.GetValue(1, 13).ToString() == "-" ? "" : MyValues.GetValue(1, 13).ToString();
data.Sex = MyValues.GetValue(1, 14).ToString() == "-" ? "" : MyValues.GetValue(1, 14).ToString();
data.Email = MyValues.GetValue(1, 16).ToString() == "-" ? "" : MyValues.GetValue(1, 16).ToString();
data.LifeTimeRoll = MyValues.GetValue(1, 17) == null ? "" : MyValues.GetValue(1, 17).ToString();
data.YearAdmitted = MyValues.GetValue(1, 18).ToString() == "-" ? "" : MyValues.GetValue(1, 18).ToString();
var record = EncodeLawyer(data);
ProcessLawyer.Add(new ProcessData { Data = data, RollNumber = data.RollNumber, Success = record.Success, Index = i });
listView1.Invoke((Action)(() =>
{
ListViewItem lvitem = new ListViewItem(i.ToString());
lvitem.SubItems.Add(record.RollNumber);
lvitem.SubItems.Add(record.Type);
lvitem.SubItems.Add(record.Message);
lvitem.BackColor = record.Success ? Color.LimeGreen : Color.DarkRed;
listView1.Items.Add(lvitem);
}));
success = record.Success ? success + 1 : success;
fail = !record.Success ? fail + 1 : fail;
label1.Invoke((Action)(() =>
{
label1.Text = string.Format("Current Index is: {0}", (i - init));
}));
label2.Invoke((Action)(() =>
{
label2.Text = string.Format("Success Data: {0}", success);
}));
label3.Invoke((Action)(() =>
{
label3.Text = string.Format("Fail Data: {0}", fail);
}));
}
}).Start();
}
private dynamic EncodeLawyer(Lawyer Data)
{
dynamic response = new ExpandoObject();
response.RollNumber = Data.RollNumber;
response.Success = false;
response.Message = "";
try
{
using (TransactionScope tx = new TransactionScope())
{
var dbs = new basecampprodEntities();
var lawyerinfo = new UserData();
bool IsNew = false;
var basicinfo = (from item in dbs.users
where item.LoginID.Equals(Data.RollNumber)
select item).FirstOrDefault();
response.Type = basicinfo == null ? Type.Insert : Type.Update;
response.Message = basicinfo == null ? string.Format("succesfully {0} the record", Type.Insert) : string.Format("succesfully {0} the record", Type.Update);
#region User Type
IsNew = basicinfo == null ? true : false;
basicinfo = basicinfo == null ? new user() : basicinfo;
string[] fullname = Data.Name.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
string[] name = fullname[1].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
string firstname = "";
for (int ii = 0; ii <= name.Length - 2; ii++)
{
firstname = firstname + " " + name[ii];
}
basicinfo.LastName = fullname[0];
basicinfo.FirstName = firstname;
basicinfo.MiddleName = name[name.Length - 1];
basicinfo.LoginID = Data.RollNumber;
basicinfo.MembershipType = Data.LifeTimeRoll == "" ? "Annual" : "LifeTime";
basicinfo.IBPNumber = Data.LifeTimeRoll;
basicinfo.AddToBar = int.Parse(Data.YearAdmitted);
basicinfo.IsLiving = true;
basicinfo.isLawyer = true;
if (IsNew) { dbs.users.Add(basicinfo); }
dbs.SaveChanges();
IsNew = false;
#endregion
#region UserInfo Type
var OtherInfo = (from item in dbs.userinfoes
where item.UserID == basicinfo.UserID
select item).FirstOrDefault();
IsNew = OtherInfo == null ? true : false;
OtherInfo = OtherInfo == null ? new userinfo() : OtherInfo;
var ChapterDetail = (from item in dbs.chapters
where item.Name.ToUpper() == Data.Chapter.ToUpper()
select item).FirstOrDefault();
if (ChapterDetail != null)
{
OtherInfo.UserID = basicinfo.UserID;
OtherInfo.ChapterID = ChapterDetail.ChapterID;
OtherInfo.Birthday = Data.BirthDate;
OtherInfo.Address = Data.HomePhone;
OtherInfo.Sex = Data.Sex.Substring(0, 1);
OtherInfo.CivilStatus = Data.Status;
OtherInfo.CreatedDate = DateTime.UtcNow;
if (IsNew) { dbs.userinfoes.Add(OtherInfo); }
dbs.SaveChanges();
IsNew = false;
}
else
{
throw new Exception(string.Format("{0} Chapter is not Existing in Database.", Data.Chapter));
}
#endregion
#region PhoneInfo Type
var phones = (from item in dbs.userinfophones
where item.UserID == basicinfo.UserID
select item).ToList();
foreach (var item in phones) { dbs.userinfophones.Remove(item); }
string[] homephones = Data.HomePhone.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
string[] offphones = Data.WorkPhone.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string phone in homephones)
{
var HomePhone = new userinfophone();
HomePhone.UserID = basicinfo.UserID;
HomePhone.Type = "Home";
HomePhone.SecurityType = "Private";
HomePhone.Number = string.Format("{0}/{1}Home", phone, basicinfo.UserID);
dbs.userinfophones.Add(HomePhone);
}
foreach (string phone in offphones)
{
var EmployePhone = new userinfophone();
EmployePhone.UserID = basicinfo.UserID;
EmployePhone.Type = "Office";
EmployePhone.SecurityType = "Private";
EmployePhone.Number = string.Format("{0}/{1}Office", phone, basicinfo.UserID);
dbs.userinfophones.Add(EmployePhone);
}
dbs.SaveChanges();
#endregion
#region EmailInfo Type
if (Data.Email != "")
{
var emails = (from item in dbs.userinfoemails
where item.UserID == basicinfo.UserID
select item).ToList();
foreach (var item in emails) { dbs.userinfoemails.Remove(item); }
var email = new userinfoemail();
email.UserID = basicinfo.UserID;
email.Type = "personal";
email.IsPrimary = false;
email.EmailAddress = Data.Email;
email.SecurityType = "Private";
dbs.userinfoemails.Add(email);
dbs.SaveChanges();
}
#endregion
#region College Type
var CollegeSchool = (from item in dbs.colleges
where item.Name.ToUpper() == Data.LawSchool.ToUpper()
select item).FirstOrDefault();
IsNew = CollegeSchool == null ? true : false;
CollegeSchool = CollegeSchool == null ? new college() : CollegeSchool;
CollegeSchool.Name = Data.LawSchool;
if (IsNew) { dbs.colleges.Add(CollegeSchool); }
dbs.SaveChanges();
var CollegeSchools = (from item in dbs.userinfocolleges
where item.UserID == basicinfo.UserID
select item).ToList();
foreach (var item in CollegeSchools) { dbs.userinfocolleges.Remove(item); }
var collegeInfo = new userinfocollege();
collegeInfo.UserID = basicinfo.UserID;
collegeInfo.ID = CollegeSchool.ID;
collegeInfo.Year = new DateTime();
dbs.userinfocolleges.Add(collegeInfo);
dbs.SaveChanges();
#endregion
#region Employer Type
var EmployerInfo = new employer();
EmployerInfo.Address = Data.WorkAddress;
EmployerInfo.Name = "";
EmployerInfo.Description = Data.Occupation;
dbs.employers.Add(EmployerInfo);
dbs.SaveChanges();
var empInfo = new userinfoemployer();
empInfo.UserID = basicinfo.UserID;
empInfo.ID = EmployerInfo.ID;
empInfo.StartYear = new DateTime();
empInfo.StillEmployed = true;
dbs.userinfoemployers.Add(empInfo);
dbs.SaveChanges();
#endregion
#region Specialization
if (Data.Specialization != "")
{
var specialization = (from item in dbs.professions
where item.Name.ToUpper() == Data.Specialization.ToUpper()
select item).FirstOrDefault();
IsNew = specialization == null ? true : false;
specialization = specialization == null ? new profession() : specialization;
specialization.Name = Data.Specialization;
if (IsNew)
{
dbs.professions.Add(specialization);
}
basicinfo.professions.Add(specialization);
dbs.SaveChanges();
}
#endregion
response.Success = true;
tx.Complete();
return response;
}
}
catch (Exception e)
{
response.Type = Type.Unknown;
string message = "";
if (e.InnerException != null)
{
if (e.InnerException.InnerException != null)
{
message = e.InnerException.InnerException.Message;
}
else
{
message = e.InnerException.Message;
}
}
else
{
message = e.Message;
}
response.Message = message;
return response;
}
}