Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }