public int Insert(CrGroupConnector groupconnector)
        {
            if (Exists(groupconnector.PrimaryRegistration, groupconnector.ProjectId))
            {
                return(GetGroupConnectorId(groupconnector.PrimaryRegistration, groupconnector.ProjectId));
            }

            var          rc    = -1;
            const string query = "INSERT INTO dbo.cr_Group_Connectors (Project_ID,Primary_Registration,Domain_ID) " +
                                 "OUTPUT INSERTED.Group_Connector_ID " +
                                 "VALUES (@ProjectID,@PrimaryRegistration,@DomainID) ";

            using (var cn = new SqlConnection(_connectionString))
                using (var cmd = new SqlCommand(query, cn))
                {
                    cmd.Parameters.Add("@ProjectID", SqlDbType.Int).Value           = groupconnector.ProjectId;
                    cmd.Parameters.Add("@PrimaryRegistration", SqlDbType.Int).Value = groupconnector.PrimaryRegistration;
                    cmd.Parameters.Add("@DomainID", SqlDbType.Int).Value            = groupconnector.DomainId;

                    // 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);
        }
Beispiel #2
0
        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);
                        }
                    }
                }
            }
        }