public override void Run()
{
ReportTitle = "Количество лицензий по ПО";
var departments = DepartmentsDataModel.GetInstance().SelectVisibleDepartments();
var departmentIds = "";
foreach (DataRow department in departments.Rows)
{
if (department.Field <bool>("AllowSelect"))
{
departmentIds += department.Field <int>("ID Department") + ",";
}
}
departmentIds = departmentIds.TrimEnd(',');
var arguments = new Dictionary <string, string>
{
{
"config",
Path.Combine(LicenseSoftwareSettings.ActivityManagerConfigsPath, "licenses_by_soft_count.xml")
},
{ "connectionString", LicenseSoftwareSettings.ConnectionString },
{ "departmentIds", departmentIds }
};
base.Run(arguments);
}
public static IEnumerable <int> GetDepartmentSubunits(int department)
{
var departments = DepartmentsDataModel.GetInstance();
var departmentIDs = new List <int>();
foreach (DataRow row in departments.Select().Rows)
{
if (row.RowState != DataRowState.Deleted &&
row.RowState != DataRowState.Detached &&
row["ID Parent Department"] != DBNull.Value && (int)row["ID Parent Department"] == department)
{
departmentIDs.Add((int)row["ID Department"]);
}
}
var subUnits = new List <IEnumerable <int> >();
foreach (var departmentId in departmentIDs)
{
subUnits.Add(GetDepartmentSubunits(departmentId));
}
foreach (var subUnit in subUnits)
{
departmentIDs = departmentIDs.Union(subUnit).ToList();
}
return(departmentIDs);
}
public static IEnumerable <int> GetComputerIDsByDepartment(int idDepartment)
{
var childDepartments = from department in FilterRows(DepartmentsDataModel.GetInstance().Select())
where department.Field <int?>("ID Parent Department") == idDepartment
select department.Field <int>("ID Department");
var computers = childDepartments.SelectMany(GetComputerIDsByDepartment);
return(computers.Concat(from computerRow in FilterRows(DevicesDataModel.GetInstance().Select())
where computerRow.Field <int>("ID Department") == idDepartment
select computerRow.Field <int>("ID Device")));
}
public override void Run(List<string> args)
{
ReportTitle = "Отчет для каждого ПК";
var departments = DepartmentsDataModel.GetInstance().SelectVisibleDepartments();
var installationIds = "";
foreach (var str in args)
installationIds += str + ",";
installationIds = installationIds.TrimEnd(',');
var arguments = new Dictionary<string, string>();
arguments.Add("config", Path.Combine(LicenseSoftwareSettings.ActivityManagerConfigsPath, "pc_report.xml"));
arguments.Add("connectionString", LicenseSoftwareSettings.ConnectionString);
//arguments.Add("departmentIds", departmentIds);
arguments.Add("installationIds", installationIds);
base.Run(arguments);
}
private void PreLoadData()
{
toolStripProgressBar.Maximum = 13;
DepartmentsDataModel.GetInstance(toolStripProgressBar, 1);
DevicesDataModel.GetInstance(toolStripProgressBar, 1);
SoftwareDataModel.GetInstance(toolStripProgressBar, 1);
SoftVersionsDataModel.GetInstance(toolStripProgressBar, 1);
SoftInstallationsDataModel.GetInstance(toolStripProgressBar, 1);
SoftInstallatorsDataModel.GetInstance(toolStripProgressBar, 1);
SoftLicDocTypesDataModel.GetInstance(toolStripProgressBar, 1);
SoftLicensesDataModel.GetInstance(toolStripProgressBar, 1);
SoftLicKeysDataModel.GetInstance(toolStripProgressBar, 1);
SoftLicTypesDataModel.GetInstance(toolStripProgressBar, 1);
SoftMakersDataModel.GetInstance(toolStripProgressBar, 1);
SoftSuppliersDataModel.GetInstance(toolStripProgressBar, 1);
SoftTypesDataModel.GetInstance(toolStripProgressBar, 1);
}
public static IEnumerable <int> GetLicenseIDsByCondition(Func <DataRow, bool> condition, EntityType entity)
{
var software = FilterRows(SoftwareDataModel.GetInstance().Select());
var softVersions = FilterRows(SoftVersionsDataModel.GetInstance().Select());
var departments = from departmentsRow in FilterRows(DepartmentsDataModel.GetInstance().SelectVisibleDepartments())
where departmentsRow.Field <bool>("AllowSelect")
select departmentsRow.Field <int>("ID Department");
var licenses = from licensesRow in FilterRows(SoftLicensesDataModel.GetInstance().Select())
where departments.Contains(licensesRow.Field <int>("ID Department"))
select licensesRow;
var result = from softwareRow in software
join softVersionRow in softVersions
on softwareRow.Field <int>("ID Software") equals softVersionRow.Field <int>("ID Software")
join licensesRow in licenses
on softVersionRow.Field <int>("ID Version") equals licensesRow.Field <int>("ID Version")
where entity == EntityType.Software ? condition(softwareRow) :
entity == EntityType.SoftVersion ? condition(softVersionRow) :
entity == EntityType.License && condition(licensesRow)
select licensesRow.Field <int>("ID License");
return(result);
}
internal override string GetFilter()
{
var filter = "";
List <int> includedSoftwareIds = null;
if (checkBoxLicTypeEnable.Checked && (comboBoxLicType.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID LicType] = '{0}'", comboBoxLicType.SelectedValue);
}
if (checkBoxLicDocTypeEnable.Checked && (comboBoxLicDocType.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID DocType] = '{0}'", comboBoxLicDocType.SelectedValue);
}
if (checkBoxSupplierEnable.Checked && (comboBoxSupplierID.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Supplier] = '{0}'", comboBoxSupplierID.SelectedValue);
}
if (checkBoxDepartmentEnable.Checked && (comboBoxDepartmentID.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
var selectedDepartments = DataModelHelper.GetDepartmentSubunits((int)comboBoxDepartmentID.SelectedValue).
Union(new List <int> {
(int)comboBoxDepartmentID.SelectedValue
});
var accessibleDepartments = from departmentsRow in DataModelHelper.
FilterRows(DepartmentsDataModel.GetInstance().SelectVisibleDepartments())
where departmentsRow.Field <bool>("AllowSelect")
select departmentsRow.Field <int>("ID Department");
var departments = selectedDepartments.Intersect(accessibleDepartments).ToList();
if (!departments.Any())
{
throw new ViewportException("Вы не состоите ни в одном из департаментов.");
}
filter += "[ID Department] IN (";
foreach (var id in departments)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
}
else
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
var accessibleDepartments = (from departmentsRow in DataModelHelper.FilterRows(DepartmentsDataModel.GetInstance().SelectVisibleDepartments())
where departmentsRow.Field <bool>("AllowSelect")
select departmentsRow.Field <int>("ID Department")).ToList();
if (!accessibleDepartments.Any())
{
throw new ViewportException("Вы не состоите ни в одном из департаментов.");
}
filter += "[ID Department] IN (";
foreach (var id in accessibleDepartments)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
}
if (checkBoxDocNumberEnable.Checked)
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "DocNumber LIKE'%{0}%'", textBoxDocNumber.Text.Trim().Replace("'", ""));
}
if (checkBoxSoftwareNameEnable.Checked && (comboBoxSoftwareName.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Version] = '{0}'", comboBoxSoftwareName.SelectedValue);
}
if (checkBoxBuyLicenseDateEnable.Checked)
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "BuyLicenseDate {0} #{1}#",
ConvertDisplayEqExprToSql(
comboBoxOpBuyLicenseDate.SelectedItem.ToString()),
dateTimePickerBuyLicenseDate.Value.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture));
}
if (checkBoxExpireLicenseDateEnable.Checked)
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "ExpireLicenseDate {0} #{1}#",
ConvertDisplayEqExprToSql(
comboBoxOpExpireLicenseDate.SelectedItem.ToString()),
dateTimePickerExpireLicenseDate.Value.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture));
}
if (checkBoxSoftwareMakerEnable.Checked && (comboBoxSoftwareMaker.SelectedValue != null))
{
var ids = DataModelHelper.GetSoftwareIDsBySoftMaker((int)comboBoxSoftwareMaker.SelectedValue);
includedSoftwareIds = DataModelHelper.Intersect(null, ids).ToList();
}
if (checkBoxSoftwareTypeEnable.Checked && (comboBoxSoftwareType.SelectedValue != null))
{
var ids = DataModelHelper.GetSoftwareIDsBySoftType((int)comboBoxSoftwareType.SelectedValue);
includedSoftwareIds = DataModelHelper.Intersect(includedSoftwareIds, ids).ToList();
}
if (checkBoxLicKeyEnable.Checked && (comboBoxLicKey.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID License] = '{0}'", comboBoxLicKey.SelectedValue);
}
if (checkBoxOnlyAvailableInstallations.Checked)
{
var installationsCount =
from installRow in DataModelHelper.FilterRows(SoftInstallationsDataModel.GetInstance().Select())
group installRow by installRow.Field <int>("ID License")
into gs
select new
{
idLicense = gs.Key,
istallationsCount = gs.Count()
};
var notAvailableLicenses =
(from licensesRow in DataModelHelper.FilterRows(SoftLicensesDataModel.GetInstance().Select())
join installRow in installationsCount
on licensesRow.Field <int>("ID License") equals installRow.idLicense
where licensesRow.Field <int?>("InstallationsCount") != null &&
(licensesRow.Field <int>("InstallationsCount") - installRow.istallationsCount <= 0)
select licensesRow.Field <int>("ID License")).ToList();
if (notAvailableLicenses.Any())
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += "[ID License] NOT IN (0";
foreach (var id in notAvailableLicenses)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
}
}
if (includedSoftwareIds == null || !includedSoftwareIds.Any())
{
return(filter);
}
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += "[ID Version] IN (0";
foreach (var id in includedSoftwareIds)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
return(filter);
}
public SearchLicensesForm()
{
InitializeComponent();
var softMakers = SoftMakersDataModel.GetInstance();
var softTypes = SoftTypesDataModel.GetInstance();
var softSuppliers = SoftSuppliersDataModel.GetInstance();
var softLicTypes = SoftLicTypesDataModel.GetInstance();
var softLicDocTypes = SoftLicDocTypesDataModel.GetInstance();
var departments = DepartmentsDataModel.GetInstance();
// Ожидаем дозагрузки, если это необходимо
softMakers.Select();
softTypes.Select();
softSuppliers.Select();
softLicTypes.Select();
softLicDocTypes.Select();
departments.Select();
var vSoftMakers = new BindingSource
{
DataMember = "SoftMakers",
DataSource = DataSetManager.DataSet
};
var vSoftTypes = new BindingSource
{
DataMember = "SoftTypes",
DataSource = DataSetManager.DataSet
};
var vSoftSuppliers = new BindingSource
{
DataMember = "SoftSuppliers",
DataSource = DataSetManager.DataSet
};
var vSoftLicTypes = new BindingSource
{
DataMember = "SoftLicTypes",
DataSource = DataSetManager.DataSet
};
var vSoftLicDocTypes = new BindingSource
{
DataMember = "SoftLicDocTypes",
DataSource = DataSetManager.DataSet
};
_vDepartments = new BindingSource {
DataSource = departments.SelectVisibleDepartments()
};
comboBoxDepartmentID.DataSource = _vDepartments;
comboBoxDepartmentID.ValueMember = "ID Department";
comboBoxDepartmentID.DisplayMember = "Department";
comboBoxSoftwareMaker.DataSource = vSoftMakers;
comboBoxSoftwareMaker.ValueMember = "ID SoftMaker";
comboBoxSoftwareMaker.DisplayMember = "SoftMaker";
comboBoxSupplierID.DataSource = vSoftSuppliers;
comboBoxSupplierID.ValueMember = "ID Supplier";
comboBoxSupplierID.DisplayMember = "Supplier";
comboBoxSoftwareType.DataSource = vSoftTypes;
comboBoxSoftwareType.ValueMember = "ID SoftType";
comboBoxSoftwareType.DisplayMember = "SoftType";
comboBoxLicType.DataSource = vSoftLicTypes;
comboBoxLicType.ValueMember = "ID LicType";
comboBoxLicType.DisplayMember = "LicType";
comboBoxLicDocType.DataSource = vSoftLicDocTypes;
comboBoxLicDocType.ValueMember = "ID DocType";
comboBoxLicDocType.DisplayMember = "DocType";
comboBoxOpBuyLicenseDate.SelectedIndex = 0;
comboBoxOpExpireLicenseDate.SelectedIndex = 0;
foreach (Control control in Controls)
{
control.KeyDown += (sender, e) =>
{
var comboBox = sender as ComboBox;
if (comboBox != null && comboBox.DroppedDown)
{
return;
}
if (e.KeyCode == Keys.Enter)
{
vButtonSearch_Click(sender, e);
}
else
if (e.KeyCode == Keys.Escape)
{
DialogResult = DialogResult.Cancel;
}
}
}
;
}
internal override string GetFilter()
{
var filter = "";
IEnumerable <int> includedLicensesIds = null;
if (checkBoxSoftwareNameEnable.Checked && (comboBoxSoftwareName.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID Version") == (int)comboBoxSoftwareName.SelectedValue, Entities.EntityType.SoftVersion);
includedLicensesIds = DataModelHelper.Intersect(null, ids);
}
if (checkBoxSoftwareMakerEnable.Checked && (comboBoxSoftwareMaker.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID SoftMaker") == (int)comboBoxSoftwareMaker.SelectedValue, Entities.EntityType.Software);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxSoftwareTypeEnable.Checked && (comboBoxSoftwareType.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID SoftType") == (int)comboBoxSoftwareType.SelectedValue, Entities.EntityType.Software);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxSupplierEnable.Checked && (comboBoxSupplierID.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID Supplier") == (int)comboBoxSupplierID.SelectedValue, Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxLicTypeEnable.Checked && (comboBoxLicType.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID LicType") == (int)comboBoxLicType.SelectedValue, Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxLicDocTypeEnable.Checked && (comboBoxLicDocType.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <int>("ID DocType") == (int)comboBoxLicDocType.SelectedValue, Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxDepartmentLicEnable.Checked && (comboBoxDepartmentLicID.SelectedValue != null))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => DataModelHelper.GetDepartmentSubunits((int)comboBoxDepartmentLicID.SelectedValue).Union(
new List <int> {
(int)comboBoxDepartmentLicID.SelectedValue
}).Contains(
row.Field <int>("ID Department")), Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxDocNumberEnable.Checked && !string.IsNullOrEmpty(textBoxDocNumber.Text.Trim()))
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row => row.Field <string>("DocNumber").ToUpper(CultureInfo.InvariantCulture)
.Contains(textBoxDocNumber.Text.Trim().ToUpper(CultureInfo.InvariantCulture)), Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxExpireLicenseDateEnable.Checked)
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row =>
{
if (row.Field <DateTime?>("ExpireLicenseDate") == null)
{
return(false);
}
switch (comboBoxOpExpireLicenseDate.SelectedItem.ToString())
{
case "=": return(row.Field <DateTime>("ExpireLicenseDate") == dateTimePickerExpireLicenseDate.Value);
case "≥": return(row.Field <DateTime>("ExpireLicenseDate") >= dateTimePickerExpireLicenseDate.Value);
case "≤": return(row.Field <DateTime>("ExpireLicenseDate") <= dateTimePickerExpireLicenseDate.Value);
}
return(false);
}, Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
if (checkBoxBuyLicenseDateEnable.Checked)
{
var ids = DataModelHelper.GetLicenseIDsByCondition(
row =>
{
if (row.Field <DateTime?>("BuyLicenseDate") == null)
{
return(false);
}
switch (comboBoxOpBuyLicenseDate.SelectedItem.ToString())
{
case "=": return(row.Field <DateTime>("BuyLicenseDate") == dateTimePickerBuyLicenseDate.Value);
case "≥": return(row.Field <DateTime>("BuyLicenseDate") >= dateTimePickerBuyLicenseDate.Value);
case "≤": return(row.Field <DateTime>("BuyLicenseDate") <= dateTimePickerBuyLicenseDate.Value);
}
return(false);
}, Entities.EntityType.License);
includedLicensesIds = DataModelHelper.Intersect(includedLicensesIds, ids);
}
var allowedDepartments = from departmentsRow in DataModelHelper.FilterRows(DepartmentsDataModel.GetInstance().SelectVisibleDepartments())
where departmentsRow.Field <bool>("AllowSelect")
select departmentsRow.Field <int>("ID Department");
var allowedComputers =
from devicesRow in DataModelHelper.FilterRows(DevicesDataModel.GetInstance().Select())
join depRow in allowedDepartments
on devicesRow.Field <int>("ID Department") equals depRow
select devicesRow.Field <int>("ID Device");
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += "[ID Computer] IN (0";
foreach (var id in allowedComputers)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
if (checkBoxInstallDateEnable.Checked)
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "InstallationDate {0} #{1}#",
ConvertDisplayEqExprToSql(
comboBoxOpInstallDate.SelectedItem.ToString()),
dateTimePickerInstallDate.Value.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture));
}
if (checkBoxInstallatorEnable.Checked && (comboBoxInstallator.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Installator] = '{0}'", comboBoxInstallator.SelectedValue);
}
if (checkBoxLicKeyEnable.Checked && (comboBoxLicKey.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID LicenseKey] = '{0}'", comboBoxLicKey.SelectedValue);
}
if (checkBoxComputerEnable.Checked && (comboBoxComputer.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Computer] = '{0}'", comboBoxComputer.SelectedValue);
}
if (checkBoxInvNumEnable.Checked && (comboBoxInvNum.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Computer] = '{0}'", comboBoxInvNum.SelectedValue);
}
if (checkBoxSerialNumEnable.Checked && (comboBoxSerialNum.SelectedValue != null))
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += string.Format(CultureInfo.InvariantCulture, "[ID Computer] = '{0}'", comboBoxSerialNum.SelectedValue);
}
if (checkBoxDepartmentInstallEnable.Checked && (comboBoxDepartmentInstallID.SelectedValue != null))
{
var computerIds = DataModelHelper.GetComputerIDsByDepartment((int)comboBoxDepartmentInstallID.SelectedValue);
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += "[ID Computer] IN (0";
foreach (var id in computerIds)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
}
if (includedLicensesIds != null)
{
if (!string.IsNullOrEmpty(filter.Trim()))
{
filter += " AND ";
}
filter += "[ID License] IN (0";
foreach (var id in includedLicensesIds)
{
filter += id.ToString(CultureInfo.InvariantCulture) + ",";
}
filter = filter.TrimEnd(',') + ")";
}
return(filter);
}