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