private static int GetReferralSourceId(List <ReferralSourceFromMachform> referrals, string element)
{
SLS_LegalServicesEntities db2 = new SLS_LegalServicesEntities();
string referralSourceName = referrals.Where(w => w.ElementName == element).FirstOrDefault().Option;
int referralSourceId = db2.ReferralSources.Where(w => w.ReferralSource1 == referralSourceName).FirstOrDefault().ReferralSourceId;
return(referralSourceId);
}
static void Main(string[] args)
{
SLS_Intakes_DataPumpEntities db = new SLS_Intakes_DataPumpEntities();
SLS_LegalServicesEntities db2 = new SLS_LegalServicesEntities();
#region Copy Intakes from Website
// Remove all records
db.Database.ExecuteSqlCommand("TRUNCATE TABLE [ap_form_12828_RAW]");
// Get MAX ID from Archive
int maxId = 0;
List <ap_form_12828_RAW> raws = db.ap_form_12828_RAW.ToList();
if (raws.Count() > 0)
{
maxId = db.ap_form_12828_ARCHIVE.Max(m => m.id);
}
// Connect to MySQL
string machServer = "bl-sser-pweb01.ads.iu.edu"; // 10.56.115.70
string machPassword = "******";
string machUser = "******";
string machDB = "machdbp";
string machConnectionString = String.Format("server={0};uid={1};pwd={2};database={3}", machServer, machUser, machPassword, machDB);
MySqlConnection conn = new MySqlConnection();
MySqlCommand cmd;
try
{
conn.ConnectionString = machConnectionString;
conn.Open();
// Pull Intakes with ID greater than MAX ID
string qry = GetIntakesQuery(maxId);
cmd = new MySqlCommand(qry, conn);
cmd.CommandTimeout = 360;
MySqlDataReader rdr = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
dataTable.TableName = "ap_form_12828_RAW";
dataTable.Load(rdr);
rdr.Close();
string connectionString = @"Data Source = IN-UITS-CSLT028; Integrated Security=true; Initial Catalog=SLS_Intakes_DataPump";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "ap_form_12828_RAW";
try
{
bulkCopy.BulkCopyTimeout = 360;
bulkCopy.WriteToServer(dataTable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
#endregion
#region Process Intakes
// ###############
// PROCESS INTAKES
// ###############
// Get Intakes
IEnumerable <ap_form_12828_RAW> ap_Form_12828_RAWs = db.ap_form_12828_RAW.OrderBy(o => o.id);
foreach (ap_form_12828_RAW raw in ap_Form_12828_RAWs)
{
// Get ID
int id = raw.id;
Guid userId = new Guid("A8FC9784-78AC-4B6F-B0DC-987A0BE072A7");
#region Case
// Insert Intake into Cases
Case _case = new Case()
{
Status = "Pending",
CreationDate = DateTime.Now,
CreatedById = userId,
FirstName = raw.element_1_1,
LastName = raw.element_1_2,
IUStudentId = raw.element_2,
Gender = raw.element_52,
Number = raw.id,
SocialStatus = raw.element_51,
MayWeRecordYourInterview = raw.element_32 == "Yes" ? true : false,
EnrollmentVerified = false,
StudentActivityFeeVerified = false
};
db2.Cases.Add(_case);
db2.SaveChanges();
#endregion
#region Case Resources
// Insert Case Resources
List <ReferralSourceFromMachform> referralSources = SetupReferralSources();
foreach (ReferralSourceFromMachform referralSource in referralSources)
{
string rsElement = referralSource.ElementName;
string valueString = raw.GetType().GetProperty(rsElement).GetValue(raw, null).ToString();
bool value = valueString == "1" ? true : false;
if (value)
{
string connectionString = @"Data Source = IN-UITS-CSLT028; Integrated Security=true; Initial Catalog=SLS_LegalServices";
using (SqlConnection _connection = new SqlConnection(connectionString))
{
try
{
_connection.Open();
string qry = "INSERT INTO dbo.CaseReferralSources(ReferralSourceId, CaseId) VALUES(@ReferralSourceId, @CaseId)";
SqlCommand cmd2 = new SqlCommand(qry, _connection);
cmd2.Parameters.Add("@ReferralSourceId", SqlDbType.Int);
cmd2.Parameters["@ReferralSourceId"].Value = GetReferralSourceId(referralSources, rsElement);
cmd2.Parameters.Add("@CaseId", SqlDbType.Int);
cmd2.Parameters["@CaseId"].Value = _case.CaseId;
cmd2.ExecuteNonQuery();
} catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
_connection.Close();
}
}
}
}
#endregion
#region Case Notes
// Insert Case Notes
CaseNote caseNote = new CaseNote()
{
CaseId = _case.CaseId,
Detail = raw.element_30,
IsCriticalDate = false,
CreationDate = DateTime.Now,
CreatedById = userId
};
db2.CaseNotes.Add(caseNote);
db2.SaveChanges();
#endregion
#region Primary Telephone
// Insert Case Primary Telephone
if (!string.IsNullOrWhiteSpace(raw.element_43))
{
Telephone telephone = new Telephone()
{
Type = "Primary",
CaseId = _case.CaseId,
Number = raw.element_43
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
}
#endregion
#region Alternate Telephone
// Insert Case Alternate Telephone
if (!string.IsNullOrWhiteSpace(raw.element_44))
{
Telephone telephone = new Telephone()
{
Type = "Alternate",
CaseId = _case.CaseId,
Number = raw.element_44
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
}
#endregion
#region Case Email
// Insert Case Email
if (!string.IsNullOrWhiteSpace(raw.element_41))
{
Email email = new Email()
{
Type = "Primary",
CaseId = _case.CaseId,
Email1 = raw.element_41
};
db2.Emails.Add(email);
db2.SaveChanges();
}
#endregion
#region Local Address
// Insert Case Local Address
if (!string.IsNullOrWhiteSpace(raw.element_3_1) || !string.IsNullOrWhiteSpace(raw.element_3_2) || !string.IsNullOrWhiteSpace(raw.element_3_3) || !string.IsNullOrWhiteSpace(raw.element_3_4) || !string.IsNullOrWhiteSpace(raw.element_3_5))
{
Address address = new Address()
{
Type = "Local",
CaseId = _case.CaseId,
Address1 = raw.element_3_1,
Address2 = raw.element_3_2,
City = raw.element_3_3,
State = raw.element_3_4,
PostalCode = raw.element_3_5,
Country = raw.element_3_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
}
#endregion
#region Alternate Address
// Insert Case Alternate Address
if (!string.IsNullOrWhiteSpace(raw.element_19_1) || !string.IsNullOrWhiteSpace(raw.element_19_2) || !string.IsNullOrWhiteSpace(raw.element_19_3) || !string.IsNullOrWhiteSpace(raw.element_19_4) || !string.IsNullOrWhiteSpace(raw.element_19_5))
{
Address address = new Address()
{
Type = "Alternate",
CaseId = _case.CaseId,
Address1 = raw.element_19_1,
Address2 = raw.element_19_2,
City = raw.element_19_3,
State = raw.element_19_4,
PostalCode = raw.element_19_5,
Country = raw.element_19_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
}
#endregion
#region AdverseParty
// Insert Adverse Party
if (!string.IsNullOrWhiteSpace(raw.element_5_1) || !string.IsNullOrWhiteSpace(raw.element_5_2))
{
// Insert Case Adverse Party Telephone
int adversePartyTelephoneId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_7))
{
Telephone telephone = new Telephone()
{
Type = "Adverse Party",
CaseId = _case.CaseId,
Number = raw.element_7
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
adversePartyTelephoneId = telephone.TelephoneId;
}
// Insert Case Adverse Party Email
int adversePartyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_9))
{
Email email = new Email()
{
Type = "Adverse Party",
CaseId = _case.CaseId,
Email1 = raw.element_9
};
db2.Emails.Add(email);
db2.SaveChanges();
adversePartyEmailId = email.EmailId;
}
// Insert Case Adverse Party Address
int adversePartyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_6_1) || !string.IsNullOrWhiteSpace(raw.element_6_2) || !string.IsNullOrWhiteSpace(raw.element_6_3) || !string.IsNullOrWhiteSpace(raw.element_6_4) || !string.IsNullOrWhiteSpace(raw.element_6_5))
{
Address address = new Address()
{
Type = "Adverse Party",
CaseId = _case.CaseId,
Address1 = raw.element_6_1,
Address2 = raw.element_6_2,
City = raw.element_6_3,
State = raw.element_6_4,
PostalCode = raw.element_6_5,
Country = raw.element_6_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
adversePartyAddressId = address.AddressId;
}
CaseParty adverseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_5_1 + " " + raw.element_5_2,
PartyType = "Adverse Party",
IsIUStudent = raw.element_20 == "Yes" ? true : false,
};
if (adversePartyAddressId > 0)
{
adverseParty.AddressId = adversePartyAddressId;
}
if (adversePartyEmailId > 0)
{
adverseParty.EmailId = adversePartyEmailId;
}
if (adversePartyTelephoneId > 0)
{
adverseParty.TelephoneId = adversePartyTelephoneId;
}
db2.CaseParties.Add(adverseParty);
db2.SaveChanges();
}
#endregion
#region AP 1
// Insert AP 1
if (!string.IsNullOrWhiteSpace(raw.element_12_1) || !string.IsNullOrWhiteSpace(raw.element_12_2))
{
string partyType = raw.element_21 == "Yes" ? "Adverse Party" : "Additional Party";
// Insert Case Party Telephone - does not exist
// Insert Case Party Email
int partyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_16))
{
Email email = new Email()
{
Type = partyType,
CaseId = _case.CaseId,
Email1 = raw.element_16
};
db2.Emails.Add(email);
db2.SaveChanges();
partyEmailId = email.EmailId;
}
// Insert Case Party Address
int partyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_13_1) || !string.IsNullOrWhiteSpace(raw.element_13_2) || !string.IsNullOrWhiteSpace(raw.element_13_3) || !string.IsNullOrWhiteSpace(raw.element_13_4) || !string.IsNullOrWhiteSpace(raw.element_13_5))
{
Address address = new Address()
{
Type = partyType,
CaseId = _case.CaseId,
Address1 = raw.element_13_1,
Address2 = raw.element_13_2,
City = raw.element_13_3,
State = raw.element_13_4,
PostalCode = raw.element_13_5,
Country = raw.element_13_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
partyAddressId = address.AddressId;
}
CaseParty caseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_12_1 + " " + raw.element_12_2,
PartyType = partyType,
IsIUStudent = raw.element_22 == "Yes" ? true : false,
};
if (partyAddressId > 0)
{
caseParty.AddressId = partyAddressId;
}
if (partyEmailId > 0)
{
caseParty.EmailId = partyEmailId;
}
db2.CaseParties.Add(caseParty);
db2.SaveChanges();
}
#endregion
#region AP 2
if (!string.IsNullOrWhiteSpace(raw.element_25_1) || !string.IsNullOrWhiteSpace(raw.element_25_2))
{
string partyType = raw.element_24 == "Yes" ? "Adverse Party" : "Additional Party";
// Insert Case Party Telephone
int adversePartyTelephoneId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_14))
{
Telephone telephone = new Telephone()
{
Type = partyType,
CaseId = _case.CaseId,
Number = raw.element_14
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
adversePartyTelephoneId = telephone.TelephoneId;
}
// Insert Case Party Email
int partyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_27))
{
Email email = new Email()
{
Type = partyType,
CaseId = _case.CaseId,
Email1 = raw.element_27
};
db2.Emails.Add(email);
db2.SaveChanges();
partyEmailId = email.EmailId;
}
// Insert Case Party Address
int partyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_26_1) || !string.IsNullOrWhiteSpace(raw.element_26_2) || !string.IsNullOrWhiteSpace(raw.element_26_3) || !string.IsNullOrWhiteSpace(raw.element_26_4) || !string.IsNullOrWhiteSpace(raw.element_26_5))
{
Address address = new Address()
{
Type = partyType,
CaseId = _case.CaseId,
Address1 = raw.element_26_1,
Address2 = raw.element_26_2,
City = raw.element_26_3,
State = raw.element_26_4,
PostalCode = raw.element_26_5,
Country = raw.element_26_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
partyAddressId = address.AddressId;
}
CaseParty caseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_25_1 + " " + raw.element_25_2,
PartyType = partyType,
IsIUStudent = raw.element_23 == "Yes" ? true : false,
};
if (partyAddressId > 0)
{
caseParty.AddressId = partyAddressId;
}
if (partyEmailId > 0)
{
caseParty.EmailId = partyEmailId;
}
db2.CaseParties.Add(caseParty);
db2.SaveChanges();
}
#endregion
#region AP 3
if (!string.IsNullOrWhiteSpace(raw.element_70_1) || !string.IsNullOrWhiteSpace(raw.element_70_2))
{
string partyType = raw.element_82 == "Yes" ? "Adverse Party" : "Additional Party";
// Insert Case Party Telephone
int adversePartyTelephoneId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_76))
{
Telephone telephone = new Telephone()
{
Type = partyType,
CaseId = _case.CaseId,
Number = raw.element_76
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
adversePartyTelephoneId = telephone.TelephoneId;
}
// Insert Case Party Email
int partyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_79))
{
Email email = new Email()
{
Type = partyType,
CaseId = _case.CaseId,
Email1 = raw.element_79
};
db2.Emails.Add(email);
db2.SaveChanges();
partyEmailId = email.EmailId;
}
// Insert Case Party Address
int partyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_73_1) || !string.IsNullOrWhiteSpace(raw.element_73_2) || !string.IsNullOrWhiteSpace(raw.element_73_3) || !string.IsNullOrWhiteSpace(raw.element_73_4) || !string.IsNullOrWhiteSpace(raw.element_73_5))
{
Address address = new Address()
{
Type = partyType,
CaseId = _case.CaseId,
Address1 = raw.element_73_1,
Address2 = raw.element_73_2,
City = raw.element_73_3,
State = raw.element_73_4,
PostalCode = raw.element_73_5,
Country = raw.element_73_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
partyAddressId = address.AddressId;
}
CaseParty caseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_70_1 + " " + raw.element_70_2,
PartyType = partyType,
IsIUStudent = raw.element_85 == "Yes" ? true : false,
};
if (partyAddressId > 0)
{
caseParty.AddressId = partyAddressId;
}
if (partyEmailId > 0)
{
caseParty.EmailId = partyEmailId;
}
db2.CaseParties.Add(caseParty);
db2.SaveChanges();
}
#endregion
#region AP 4
if (!string.IsNullOrWhiteSpace(raw.element_69_1) || !string.IsNullOrWhiteSpace(raw.element_69_2))
{
string partyType = raw.element_81 == "Yes" ? "Adverse Party" : "Additional Party";
// Insert Case Party Telephone
int adversePartyTelephoneId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_75))
{
Telephone telephone = new Telephone()
{
Type = partyType,
CaseId = _case.CaseId,
Number = raw.element_75
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
adversePartyTelephoneId = telephone.TelephoneId;
}
// Insert Case Party Email
int partyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_78))
{
Email email = new Email()
{
Type = partyType,
CaseId = _case.CaseId,
Email1 = raw.element_78
};
db2.Emails.Add(email);
db2.SaveChanges();
partyEmailId = email.EmailId;
}
// Insert Case Party Address
int partyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_72_1) || !string.IsNullOrWhiteSpace(raw.element_72_2) || !string.IsNullOrWhiteSpace(raw.element_72_3) || !string.IsNullOrWhiteSpace(raw.element_72_4) || !string.IsNullOrWhiteSpace(raw.element_72_5))
{
Address address = new Address()
{
Type = partyType,
CaseId = _case.CaseId,
Address1 = raw.element_72_1,
Address2 = raw.element_72_2,
City = raw.element_72_3,
State = raw.element_72_4,
PostalCode = raw.element_72_5,
Country = raw.element_72_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
partyAddressId = address.AddressId;
}
CaseParty caseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_69_1 + " " + raw.element_69_2,
PartyType = partyType,
IsIUStudent = raw.element_84 == "Yes" ? true : false,
};
if (partyAddressId > 0)
{
caseParty.AddressId = partyAddressId;
}
if (partyEmailId > 0)
{
caseParty.EmailId = partyEmailId;
}
db2.CaseParties.Add(caseParty);
db2.SaveChanges();
}
#endregion
#region AP 5
if (!string.IsNullOrWhiteSpace(raw.element_68_1) || !string.IsNullOrWhiteSpace(raw.element_68_2))
{
string partyType = raw.element_80 == "Yes" ? "Adverse Party" : "Additional Party";
// Insert Case Party Telephone
int adversePartyTelephoneId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_74))
{
Telephone telephone = new Telephone()
{
Type = partyType,
CaseId = _case.CaseId,
Number = raw.element_74
};
db2.Telephones.Add(telephone);
db2.SaveChanges();
adversePartyTelephoneId = telephone.TelephoneId;
}
// Insert Case Party Email
int partyEmailId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_77))
{
Email email = new Email()
{
Type = partyType,
CaseId = _case.CaseId,
Email1 = raw.element_77
};
db2.Emails.Add(email);
db2.SaveChanges();
partyEmailId = email.EmailId;
}
// Insert Case Party Address
int partyAddressId = 0;
if (!string.IsNullOrWhiteSpace(raw.element_71_1) || !string.IsNullOrWhiteSpace(raw.element_71_2) || !string.IsNullOrWhiteSpace(raw.element_71_3) || !string.IsNullOrWhiteSpace(raw.element_71_4) || !string.IsNullOrWhiteSpace(raw.element_71_5))
{
Address address = new Address()
{
Type = partyType,
CaseId = _case.CaseId,
Address1 = raw.element_71_1,
Address2 = raw.element_71_2,
City = raw.element_71_3,
State = raw.element_71_4,
PostalCode = raw.element_71_5,
Country = raw.element_71_6
};
db2.Addresses.Add(address);
db2.SaveChanges();
partyAddressId = address.AddressId;
}
CaseParty caseParty = new CaseParty()
{
CaseId = _case.CaseId,
OrganizationName = raw.element_68_1 + " " + raw.element_68_2,
PartyType = partyType,
IsIUStudent = raw.element_83 == "Yes" ? true : false,
};
if (partyAddressId > 0)
{
caseParty.AddressId = partyAddressId;
}
if (partyEmailId > 0)
{
caseParty.EmailId = partyEmailId;
}
db2.CaseParties.Add(caseParty);
db2.SaveChanges();
}
#endregion
// Pull Uploads
}
#endregion
}