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);
}
