private void GenerateAudit() { string SystemName = this.tabControlSystems.SelectedTab.Name; if (textBoxPrincipalAccess.Text != "" || ((textBoxPrincipalAccess.Text != "" || textBoxFBUAccess.Text != "") && SystemName == "FBUManager")) { string DatabaseToUse = ""; string BusinessUnitTableName = ""; string SQLRequest = ""; switch (SystemName) { case "ED": DatabaseToUse = "EnterpriseDirectories"; BusinessUnitTableName = "BusinessUnit"; break; case "TRM": DatabaseToUse = "TRMSys"; BusinessUnitTableName = "[FinancialBusinessUnit] as BusinessUnit"; break; case "Invoicing": DatabaseToUse = "Invoicing"; BusinessUnitTableName = "BusinessUnit"; break; case "FBUManager": DatabaseToUse = "EnterpriseDirectories"; BusinessUnitTableName = "BusinessUnit"; break; } if (SystemName == "ED" || SystemName == "TRM" || SystemName == "Invoicing") { SQLRequest = SQLQueriesTemplates.SQLAccessAudit(); SQLRequest = SQLRequest.Replace("#DatabaseToUse", DatabaseToUse); SQLRequest = SQLRequest.Replace("#BusinessUnitTableName", BusinessUnitTableName); SQLRequest = SQLRequest.Replace("#Condition", textBoxPrincipalAccess.Text); SQLRequest = SQLRequest.Replace("[Authorization].[dbo]", " [auth]"); SQLRequest = SQLRequest.Replace("[AuthorizationAudit].[dbo]", " [authAudit]"); SQLRequest = SQLRequest.Replace("[dbo]", " [app]"); } else { string Condition = ""; if (textBoxPrincipalAccess.Text != "") { Condition = Condition + "Employee.login = '******'"; } if (textBoxFBUAccess.Text != "") { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + "businessUnit.name = '" + textBoxFBUAccess.Text + "'"; } SQLRequest = SQLQueriesTemplates.SQLFBUManagerAudit(); SQLRequest = SQLRequest.Replace("#Condition", Condition); } ExecuteSQLScript(SQLRequest, SystemName); } }
private string GetReplacedSQLRequest(string SystemName) { string Condition = ""; string ConditionActive = ""; string SQLRequestData = ""; string FBUTable = "BusinessUnit"; string FBUAncestorTable = "BusinessUnitAncestorLink"; string DBName = "EnterpriseDirectories"; switch (SystemName) { case "ED": SQLRequestData = SQLQueriesTemplates.SQLEDAccess(); break; case "TRM": SQLRequestData = SQLQueriesTemplates.SQLTRMAccess(); FBUTable = "FinancialBusinessUnit"; FBUAncestorTable = "FinancialBusinessUnitAncestorLink"; DBName = "TRMSys"; break; case "Invoicing": SQLRequestData = SQLQueriesTemplates.SQLInvoicingAccess(); DBName = "Invoicing"; break; case "FBUManager": SQLRequestData = SQLQueriesTemplates.SQLCurrentFBUManager(); break; case "FBUTree": SQLRequestData = SQLQueriesTemplates.SQLTreeCompare(); break; case "FBUVersion": SQLRequestData = SQLQueriesTemplates.SQLFBUVersionSearch(); break; } //FBUSearch if (SystemName == "FBUTree") { string TreeType = "TreeOld"; if (radioButtonOldTree.Checked == false) { TreeType = "TreeNew"; } if (textBoxFBUAccess.Text != "") { Condition = Condition + TreeType + ".name like '%" + textBoxFBUAccess.Text + "%'"; } if (textBoxFBUPath.Text != "") { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + TreeType + ".Childs like '%" + textBoxFBUPath.Text + "%'"; } if (textBoxHorizontal.Text != "") { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + TreeType + ".Horizontal = '" + textBoxHorizontal.Text + "'"; } //active if (radioButtonFBUActiveYes.Checked == true) { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + "TreeNew.active = 1"; } if (radioButtonFBUActiveNo.Checked == true) { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + "TreeNew.active = 0"; } SQLRequestData = SQLRequestData.Replace("#Condition", Condition); SQLRequestData = SQLRequestData.Replace("#OldTreeDate", dateTimePickerOldTree.Value.ToString("yyyyMMdd")); SQLRequestData = SQLRequestData.Replace("#NewTreeDate", dateTimePickerNewTree.Value.ToString("yyyyMMdd")); return(SQLRequestData); } //FBUVersion if (textBoxFBUAccess.Text != "" && SystemName == "FBUVersion") { Condition = textBoxFBUAccess.Text; SQLRequestData = SQLRequestData.Replace("#Condition", Condition); return(SQLRequestData); } //all other if (textBoxPrincipalAccess.Text != "") { if (SystemName == "ED" || SystemName == "TRM" || SystemName == "Invoicing") { Condition = "PermissionsAll."; } Condition = Condition + "Login like '%" + textBoxPrincipalAccess.Text + "%'"; } if (textBoxFBUAccess.Text != "") { if (Condition != "") { Condition = Condition + " and "; } if (checkBoxParentFBU.Checked) { string SQLFBUParentFilter = SQLQueriesTemplates.SQLFBUParentFilter(); SQLFBUParentFilter = SQLFBUParentFilter.Replace("#ConditionFBU", textBoxFBUAccess.Text); SQLFBUParentFilter = SQLFBUParentFilter.Replace("#BusinessUnitTableName", FBUTable); SQLFBUParentFilter = SQLFBUParentFilter.Replace("#BusinessUnitAncestorTableName", FBUAncestorTable); SQLFBUParentFilter = SQLFBUParentFilter.Replace("#DBName", DBName); Condition = Condition + "BU in (" + SQLFBUParentFilter + ")"; } else { Condition = Condition + "BU = '" + textBoxFBUAccess.Text + "'"; } } if (textBoxFBUPath.Text != "") { if (Condition != "") { Condition = Condition + " and "; } Condition = Condition + "BU_path like '%" + textBoxFBUPath.Text + "%'"; } if (textBoxRoleAccess.Text != "") { if (Condition != "") { Condition = Condition + " and "; } //Condition = Condition + "Role like '%" + textBoxRoleAccess.Text + "%'"; Condition = Condition + "Role = '" + textBoxRoleAccess.Text + "'"; } if (checkBoxActiveFBU.Checked == true && SystemName != "FBUManager") { ConditionActive = ConditionActive + " and BusinessUnit.active = 1"; } if (SystemName == "ED") { //собираем список ролей string LTRoles = GetRoleList(listBoxLT); string LHRoles = GetRoleList(listBoxLH); string EDRoles = GetRoleList(listBoxED); //Список ролей собрали, теперь собираем запрос if (checkBoxRoleED.Checked == false || checkBoxLT.Checked == false || checkBoxLH.Checked == false) { if (checkBoxLT.Checked == false) { Condition = Condition + @" and Role not in (" + LTRoles + ")"; } //else { Condition = Condition + @" and Role in (" + LTRoles + ")"; } if (checkBoxLH.Checked == false) { Condition = Condition + @" and Role not in (" + LHRoles + ")"; } //else { Condition = Condition + @" and Role in (" + LHRoles + ")"; } if (checkBoxRoleED.Checked == false) { Condition = Condition + @" and Role not in (" + EDRoles + ")"; } //else { Condition = Condition + @" and Role in (" + EDRoles + ")"; } } } SQLRequestData = SQLRequestData.Replace("#ConditionActiveFBU", ConditionActive); SQLRequestData = SQLRequestData.Replace("#Condition", Condition); return(SQLRequestData); }