public void ModifySelect(Select sql, TableInfo tableInfo) { // Get the ContactTableInfo and SuperOfficeTrainingTableTableInfo ContactTableInfo newConTable = (ContactTableInfo)tableInfo; SuperOfficeTrainingTableTableInfo newCustomTable = MyNetServerCode.Data.CustomTablesInfo.GetSuperOfficeTrainingTableTableInfo(); // Set the restriction sql.RestrictionAnd(newCustomTable.AssociateId.Equal(SuperOffice.Data.S.Parameter(SuperOffice.SoContext.CurrentPrincipal.AssociateId))); // Join the tables Contact and the custom table sql.JoinRestriction.InnerJoin(newConTable.BusinessIdx.Equal(newCustomTable.BusinessId)); }
private void btnLoadFile_Click(object sender, EventArgs e) { if (openFileDialog.ShowDialog() == DialogResult.OK) { using (var _session = SoSession.Authenticate(Username, Password)) { DataGridViewComboBoxColumn rolesColumn = (DataGridViewComboBoxColumn)dtUsersList.Columns["Role"]; rolesColumn.DataSource = roles.ToList(); rolesColumn.DisplayMember = "Name"; DataGridViewComboBoxColumn groupsColumn = (DataGridViewComboBoxColumn)dtUsersList.Columns["UserGroup"]; groupsColumn.DataSource = groups.ToList(); groupsColumn.DisplayMember = "Value"; p = new FileParser(openFileDialog.FileName); MessageBox.Show("File parsed, " + p.UserInfos.Count.ToString() + " users read\n"); foreach (ImportUserInfo ui in p.UserInfos) { ContactTableInfo cti = TablesInfo.GetContactTableInfo(); OwnerContactLinkTableInfo octi = TablesInfo.GetOwnerContactLinkTableInfo(); Select findOc = S.NewSelect("Find OC"); findOc.JoinRestriction.InnerJoin(cti.ContactId.Equal(octi.ContactId)); // if contact name contains a comma, assume that pre-comma is name and post-comma is department (db & file are set up that way) if (ui.Company.Contains(",")) { findOc.Restriction = cti.Name.Equal(S.Parameter(ui.Company.Split(',')[0].Trim())). And(cti.Department.Equal(S.Parameter(ui.Company.Split(',')[1].Trim()))); } else { findOc.Restriction = cti.Name.Equal(S.Parameter(ui.Company)); } findOc.ReturnFields.Add(cti.ContactId); int ocId = QueryExecutionHelper.ExecuteTypedScalar <int>(findOc); if (ocId == 0) { //MessageBox.Show("Owner company " + ui.Company + "(referenced by " + ui.UID + ") does not exist OR is not an Owner Company - setting to License Owner " + SoSystemInfo.GetCurrent().CompanyName); ui.Company = SoSystemInfo.GetCurrent().CompanyName; if (!_contacts.ContainsKey(SoSystemInfo.GetCurrent().CompanyName)) { _contacts.Add(ui.Company, SoSystemInfo.GetCurrent().CompanyId); } } else if (!_contacts.ContainsKey(ui.Company)) { _contacts.Add(ui.Company, ocId); } if (!_roles.ContainsKey(ui.Role)) { ui.Role = DefaultRole; } if (!_groups.ContainsKey(ui.Group)) { ui.Group = DefaultGroup; } } Dictionary <string, int> users = Importer.FindUsers(); dtUsersList.AutoGenerateColumns = false; dtUsersList.Columns["FirstName"].DataPropertyName = "FirstName"; dtUsersList.Columns["LastName"].DataPropertyName = "LastName"; dtUsersList.Columns["FullName"].DataPropertyName = "FullName"; dtUsersList.Columns["UserName"].DataPropertyName = "UID"; dtUsersList.Columns["Email"].DataPropertyName = "Email"; dtUsersList.Columns["Role"].DataPropertyName = "Role"; dtUsersList.Columns["UserGroup"].DataPropertyName = "Group"; dtUsersList.Columns["Company"].DataPropertyName = "Company"; dtUsersList.Columns["AssociateId"].DataPropertyName = "AssociateId"; dtUsers = Importer.ConvertToDataTable <ImportUserInfo>(p.UserInfos); dtUsersList.DataSource = dtUsers; //dtUsersList.DataSource = p.UserInfos; progressBar.Value = 0; foreach (DataGridViewRow item in dtUsersList.Rows) { string username = item.Cells["UserName"].Value.ToString(); if (users.ContainsKey(username)) { item.Cells["Status"].Value = "Exists"; item.Cells["AssociateId"].Value = users[username]; string license = Importer.GetLicense(users[username], LookupList); item.Cells["AssignedLicenses"].Value = license; } else { item.Cells["AssociateId"].Value = DBNull.Value; item.Cells["Status"].Value = "New"; item.Cells["AssignedLicenses"].Value = ""; } } } btnProcess.Enabled = true; progressBar.Value = 0; txtSearch.Text = ""; lblSearch.Visible = true; txtSearch.Visible = true; } }