public int Insert(CrAddress addr)
{
var rc = -1;
const string query = "INSERT INTO dbo.Addresses (Address_Line_1,City,[State/Region],Postal_Code,Domain_ID) " +
"OUTPUT INSERTED.Address_ID " +
"VALUES (@Address,@City,@State,@Zip,@DomainID) ";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 100).Value = addr.AddressLine1;
cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = addr.City;
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = addr.State;
cmd.Parameters.Add("@Zip", SqlDbType.NVarChar).Value = addr.PostalCode;
cmd.Parameters.Add("@DomainID", SqlDbType.NVarChar).Value = 1;
// 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);
}
public bool Exists(int participantid, int initiativeId)
{
var rc = false;
var count = 0;
const string query = "SELECT count(*) FROM dbo.cr_Registrations WHERE participant_id = @ParticipantId AND Initiative_ID = @InitiativeId";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@ParticipantId", SqlDbType.NVarChar, 100).Value = participantid;
cmd.Parameters.Add("@InitiativeId", SqlDbType.Int).Value = initiativeId;
// open connection, execute INSERT, close connection
cn.Open();
try
{
count = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
// write to log
log.Error(ex.Message);
}
cn.Close();
}
rc = count > 0 ? true : false;
return(rc);
}
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);
}
public int Insert(CrProject project, bool update = false)
{
if (Exists(project.ProjectName, project.InitiativeId))
{
if (update)
{
Update(project);
}
return(GetProjectId(project.ProjectName));
}
var addrDao = new CrAddressDao();
var addr = new CrAddress
{
AddressLine1 = project.Address1,
City = project.City,
PostalCode = project.Zip,
State = project.State
};
var addressID = addrDao.Insert(addr);
var rc = -1;
const string query = "INSERT INTO dbo.cr_Projects (Project_Name,Project_Status_ID,Location_ID,Project_Type_ID," +
"Organization_ID,Initiative_ID,Minimum_Volunteers,Maximum_Volunteers," +
"Absolute_Maximum_Volunteers,Domain_ID," +
"Check_In_Floor,Check_In_Area,Check_In_Room_Number,Note_To_Volunteers_1," +
"Note_To_Volunteers_2,Project_Parking_Location,Address_ID) " +
"OUTPUT INSERTED.Project_ID " +
"VALUES (@ProjectName,@ProjectStatusID,@LocationID,@ProjectTypeID," +
"@OrganizationID,@InitiativeID,@MinimumVolunteers,@MaximumVolunteers," +
"@AbsoluteMaximumVolunteers,@DomainID," +
"@CheckInFloor,@CheckInArea,@CheckInRoomNumber,@NoteToVolunteers1," +
"@NoteToVolunteers2,@ProjectParkingLocation,@Address_ID) ";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 100).Value = project.ProjectName;
cmd.Parameters.Add("@ProjectStatusID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = mp.GetLocationId(project.LocationName);
cmd.Parameters.Add("@ProjectTypeID", SqlDbType.Int).Value = mp.GetProjectTypeId(project.ProjectTypeName);
cmd.Parameters.Add("@OrganizationID", SqlDbType.Int).Value = mp.GetOrganizationId(project.OrganizationName);
cmd.Parameters.Add("@InitiativeID", SqlDbType.Int).Value = project.InitiativeId;
cmd.Parameters.Add("@MinimumVolunteers", SqlDbType.Int).Value = project.MinVol;
cmd.Parameters.Add("@MaximumVolunteers", SqlDbType.Int).Value = project.MaxVol;
cmd.Parameters.Add("@AbsoluteMaximumVolunteers", SqlDbType.Int).Value = project.AbsoluteMaxVol;
cmd.Parameters.Add("@DomainID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@CheckInFloor", SqlDbType.NVarChar, 50).Value = project.CheckInFloor;
cmd.Parameters.Add("@CheckInArea", SqlDbType.NVarChar, 50).Value = project.CheckInArea;
cmd.Parameters.Add("@CheckInRoomNumber", SqlDbType.NVarChar, 50).Value = project.CheckInRoomNumber;
cmd.Parameters.Add("@NoteToVolunteers1", SqlDbType.NVarChar, 500).Value = project.Note1;
cmd.Parameters.Add("@NoteToVolunteers2", SqlDbType.NVarChar, 500).Value = project.Note2;
cmd.Parameters.Add("@ProjectParkingLocation", SqlDbType.NVarChar, 500).Value = project.ParkingLocation;
cmd.Parameters.Add("@Address_ID", SqlDbType.Int).Value = addressID;
// 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);
}
}
}
}
}
