public int Insert(CrRegistration registration)
{
if (Exists(registration.ParticipantId, registration.InitiativeId))
{
return(GetRegistrationId(registration.ParticipantId, registration.InitiativeId));
}
var rc = -1;
const string query = "INSERT INTO dbo.cr_Registrations (Organization_ID,Preferred_Launch_Site_ID,Participant_ID,Initiative_ID,Spouse_Participation,Domain_ID, Additional_Information,_Registration_Creation_Date) " +
"OUTPUT INSERTED.Registration_ID " +
"VALUES (@OrganizationID,@PreferredLaunchSiteID,@ParticipantID,@InitiativeID,@SpouseParticipation,@DomainID, @AddlInfo, @CreationDate) ";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@OrganizationID", SqlDbType.Int).Value = mp.GetOrganizationId(registration.OrganizationName);
cmd.Parameters.Add("@PreferredLaunchSiteID", SqlDbType.Int).Value = mp.GetLocationId(registration.LocationName);
cmd.Parameters.Add("@ParticipantID", SqlDbType.Int).Value = registration.ParticipantId;
cmd.Parameters.Add("@InitiativeID", SqlDbType.Int).Value = registration.InitiativeId;
cmd.Parameters.Add("@SpouseParticipation", SqlDbType.Bit).Value = registration.SpouseParticipation;
cmd.Parameters.Add("@DomainID", SqlDbType.Int).Value = registration.DomainId;
cmd.Parameters.Add("@AddlInfo", SqlDbType.NVarChar).Value = registration.AddlInfo;
cmd.Parameters.Add("@CreationDate", SqlDbType.SmallDateTime).Value = registration.CreationDate;
// open connection, execute INSERT, close connection
cn.Open();
try
{
rc = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
// write to log
log.Error(ex.Message);
}
cn.Close();
}
return(rc);
}
private static void Main(string[] args)
{
if (args.Length <= 1 || args.Length > 5)
{
Console.WriteLine("Usage: LoadProjectData filename [-I:initiative] [-Update] [-O:organization_name] [-L:location_name]");
Console.WriteLine("\t-I:initiative the ID of the initiative");
Console.WriteLine("\t-update: pass this flag if this is the second pass adding additional data.");
Console.WriteLine("\t-O:organization_name: The name of the organization these projects are for. eg. Crossroads or Archdiocese");
Console.WriteLine("\t-L:location_name: The name of the location these projects are for. eg. Oakley or Andover");
Console.Read();
return;
}
foreach (var arg in args)
{
if (arg.ToUpper().StartsWith("-I"))
{
_initiative = int.Parse(arg.Substring(arg.IndexOf(':') + 1));
}
else if (arg.ToUpper().StartsWith("-U"))
{
_updatePass = true;
}
else if (arg.ToUpper().StartsWith("-O"))
{
_orgName = arg.Substring(arg.IndexOf(':') + 1);
}
else if (arg.ToUpper().StartsWith("-L"))
{
_locationName = arg.Substring(arg.IndexOf(':') + 1);
}
else
{
_fileName = arg;
}
}
_projnamecol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Projnamecol"]);
_projtypecol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Projtypecol"]);
_mincol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Mincol"]);
_maxcol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Maxcol"]);
_supermaxcol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Supermaxcol"]);
_locationnamecol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Locationnamecol"]);
_orgnamecol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Orgnamecol"]);
_tcemailscol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Tcemailscol"]);
_projectaddresscol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["ProjectAddressCol"]);
_projectcitycol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["ProjectCityCol"]);
_projectstatecol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["ProjectStateCol"]);
_projectzipcol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["ProjectZipCol"]);
_checkinroomcol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CheckInRoomCol"]);
_notetovol1col = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["NoteToVol1Col"]);
_projectparkingloccol = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["ProjectParkingLocCol"]);
var m = new MpDao();
var package = new ExcelPackage(new FileInfo(_fileName));
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
// loop over each row
for (var row = workSheet.Dimension.Start.Row + 1; row <= workSheet.Dimension.End.Row; row++)
{
//create project with info from the sheet
var p = new CrProject
{
ProjectName = ReadStringFromFile(workSheet, row, _projnamecol),
OrganizationName = _orgName ?? ReadStringFromFile(workSheet, row, _orgnamecol),
ProjectTypeName = ReadStringFromFile(workSheet, row, _projtypecol),
LocationName = _locationName ?? ReadStringFromFile(workSheet, row, _locationnamecol),
MinVol = ReadIntFromFile(workSheet, row, _mincol),
MaxVol = ReadIntFromFile(workSheet, row, _maxcol),
AbsoluteMaxVol = ReadIntFromFile(workSheet, row, _supermaxcol),
DomainId = 1,
InitiativeId = _initiative,
CheckInRoomNumber = _updatePass ? ReadStringFromFile(workSheet, row, _checkinroomcol) : "",
Note1 = _updatePass ? ReadStringFromFile(workSheet, row, _notetovol1col) : "",
ParkingLocation = _updatePass ? ReadStringFromFile(workSheet, row, _projectparkingloccol) : "",
Address1 = ReadStringFromFile(workSheet, row, _projectaddresscol),
City = ReadStringFromFile(workSheet, row, _projectcitycol),
State = ReadStringFromFile(workSheet, row, _projectstatecol),
Zip = ReadStringFromFile(workSheet, row, _projectzipcol)
};
Console.WriteLine(row.ToString());
var projDao = new CrProjectDao();
var projectId = projDao.Insert(p, _updatePass);
if (!_updatePass)
{
//Get a Participant ID for each TC in list
var tcEmailList = ReadStringFromFile(workSheet, row, _tcemailscol).Split(',').ToList();
foreach (var email in tcEmailList)
{
try
{
var participantid = m.GetParticipantId(email);
//create registration
var regDao = new CrRegistrationDao();
var reg = new CrRegistration
{
ParticipantId = participantid,
AddlInfo = "Created By GO Local Import App",
CreationDate = DateTime.Now,
DomainId = 1,
InitiativeId = _initiative,
LocationName = p.LocationName,
OrganizationName = p.OrganizationName,
SpouseParticipation = 0
};
var registrationId = regDao.Insert(reg);
//create GroupConnector
var gc = new CrGroupConnector
{
ProjectId = projectId,
DomainId = 1,
PrimaryRegistration = registrationId
};
var gcdao = new CrGroupConnectorDao();
var groupconnectorid = gcdao.Insert(gc);
//create GroupConnectorRegistration
var gcr = new CrGroupConnectorRegistration
{
GroupConnectorId = groupconnectorid,
RegistrationId = registrationId,
DomainId = 1,
RoleId = 22
};
var gcregdao = new CrGroupConnectorRegistrationDao();
gcregdao.Insert(gcr);
}
catch
(Exception ex)
{
var str = p.ProjectName + ":" + p.LocationName + ":" + email + " not found";
log.Warn(str);
}
}
}
}
}