Video

Development of the organization's information system module. Integration of enterprise information systems

Built from many modules Information system allowed the Nizhny Novgorod automobile plant "Chaika-Service" to implement the idea of production that takes into account the wishes of customers who placed their orders at the enterprise as fully as possible

The information system created from a variety of modules allowed the Nizhny Novgorod automobile plant "Chaika-Service" to implement the idea of production that takes into account the wishes of customers who placed their orders at the enterprise as fully as possible.

The main task of the IT department of a company doing business in these difficult times is to reduce IT costs and provide management with the tools to help them successfully overcome the crisis. So says Alexey Ganin, head of the IT department of the Nizhny Novgorod automobile plant Chaika-Service, which specializes in the production of serial and unique special vehicles.

The company grew rapidly in 2006, when a part of the old plant was acquired and the development of the second territory began. Naturally, the task was to unite both territories into a single information field. Started by creating a VPN network, but as the number of users increased, bandwidth the channel was missing. Then a fiber-optic cable was laid between the two territories.

With the onset of the crisis, the need for network resources decreased, this allowed to reduce the purchase of active equipment for the telecommunications infrastructure of the enterprise. Another significant source of cost savings has been the shift away from outsourcing and the implementation of IT tasks in-house.

In addition, the company optimizes Internet costs, analyzes and limits traffic consumption. The company has branches in Krasnodar and Moscow, all sites are combined into an IP network with a single numbering. And now it is this internal network that is used for calls within the enterprise, which is much more economical than long-distance calls.

Of the tools that will be provided to management in the near future, Ganin named, first of all, the cost calculation system. It has already been developed and will serve a common global goal - to reduce costs. An updated calculation of the cost of products is planned to be carried out on the basis of the engineering data management system. This will provide detailed and up-to-date cost data (previously calculated on the basis of accounting data). The company produces quite complex products, only the final versions of cars with various modifications, there are about one and a half thousand. Naturally, the parts from which they are assembled are two orders of magnitude larger.

From accounting to production

The first step towards automation was the acquisition in 2002 of the product "1C: Accounting 6.0" and the CAD system "Compass" of the company "Ascon". The next step was the automation of production activities. The Rarus NN company, by order of the enterprise, began adapting the ERP system "1C: Production Enterprise Management 8" ("1C: SCP 8") to the needs and characteristics of the enterprise. The aim of the project was to build a unified database and implement the management of all business processes based on a unified information system. The decisive factor in the success of its implementation was the direct support of the top management of the enterprise - the general director, who initiated and supported the project at all its stages.

When automating production activities, special attention was paid to the adequacy of displaying the progress of the production process in the system. Implementation team specialists developed terms of reference with detailed description, which car in which configuration the client should receive and what needs to be done for each order. The type of car, its model, the list of required technological operations, their sequence, the list of control operations, etc. were entered into the document. This approach made it possible to make the company more client-oriented, since the technical specifications were formed by the managers of the commercial department, who tried to take into account the wishes of the client as much as possible, and only then the tasks went into production.

The specialists of the IT department, together with the technologists, developed a block of specifications for production and technological maps. On their basis and on the basis of the monthly plan for the release of finished products, the need for materials for a certain period was determined, taking into account current balances. All this made it possible to competently plan the work of the supply department.

Employees of the IT department have developed a module for "1C: UPP 8" to import the "tree" of the product from the Compass system, which is used by the designers of the enterprise. The work algorithm turned out to be the following: the design bureau, using the Compass, develops a drawing and creates a 3D model of the object, then the product structure is imported into the ERP system using the developed module, after which the product specification is built based on the imported data. If designers make changes to any node, then these changes are automatically reflected in all systems.

At first, as Ganin admitted, he and his specialists wanted to make an engineering data management system on their own, but soon found out that the Appius group of companies, a partner of 1C, was developing its own replicable PDM solution (it was called 1C: PDM Engineering Data Management ").

Feedback

The next task was to obtain operational feedback from production, this is important, since the technological cycle of manufacturing a product can take one to two weeks. Previously, the status of the order was found out simply by phone, but now the relevant information is received by means of the information system.

The first step in this direction was the development of a system for monitoring the state of the terms of reference. Some production processes were changed, in particular, QCD employees were obliged to submit reports on accepted work to the operator, and he entered data about them into the ERP system. As a result, the system began to display the progress of the production order by stages with the indication of responsible persons, this allowed managers to provide customers with truthful information about at what stage their orders are and when they will be ready.

The next step was the introduction of the production planning module. Earlier planning was using Excel, often there were inconsistencies, errors. After the ERP-module of production planning was launched, the managers received at their disposal the actual data generated on the basis of the received technical specifications. This made it possible to quickly track the loading of each section. As a result, the accuracy and efficiency of production planning has increased.

Soon there was a need for more operational information about the status of production processes, in particular about downtime. To solve this problem, a system was implemented that allows tracking the progress of production processes based on barcoding: each technological operation, each technical task and each employee was assigned its own barcode, terminals equipped with a barcode scanner were installed at the production site.

The production process is now built as follows. Before starting work, the foreman or worker approaches the terminal, reads his barcode, the barcode of the technical task and the technological operation. From the point of view of the system, this means that the employee has started work. After its completion, the employee repeats his actions with barcodes.

“This is a universal solution, besides, it does not require computer literacy from workers,” Ganin notes. “The car is the main and most expensive component of our production, reducing downtime has made it possible to dramatically speed up the execution of orders.” A convenient and simple tool for loss analysis has appeared at the enterprise: the system automatically generates work diagrams for each vehicle, allowing you to track when work on this vehicle began, when it ended, how long the vehicle just stood waiting for the next operation. If the allowable time is exceeded, the causes and the search for the perpetrators of such a long downtime begin. As a result, the personal responsibility of the performers has increased.

On the basis of "1C: SCP 8", the specialists of the enterprise implemented a block of work planning design office. Technical tasks created in the system are sent to the chief designer, who analyzes them, distributes their development among his designers and determines the time for each task. Such an organization of work gives the chief designer and managers who form the order base the ability to track the degree of workload of the design bureau, and this, in turn, allows you to compare the workload of production and designers and rationally distribute the available human and production resources.

Data on the work performed, obtained thanks to barcodes, enters the payroll calculation unit for performers. The system records the time of work, facilitating the calculation of weekends and overtime hours. All this contributes to fast and accurate payroll calculation.

It is important to emphasize that the company took the path of expansion

The basic configuration of an ERP system with additional blocks without changing its internal structure. It became possible, in particular, to carry out its updates without problems.

To manage the archive of design documentation, the enterprise implemented the 1C: PDM Engineering Data Management system (developed by Appius Group of Companies) and integrated it with 1C: UPP 8. In addition to work on the creation of new products, the engineering data management system is planned to be used to more accurately calculate the cost of products.

Multi-faceted integration

The company has implemented GPS navigation to track supply vehicles and goods vehicles that travel long distances. This allows you to optimize routes, reduce fuel costs, and more clearly maintain the discipline of deliveries.

"Chaika-Service" plans to connect all branches via videoconferencing system into a single network - two in Nizhny Novgorod and one each in Moscow, Krasnodar and Naberezhnye Chelny. This will improve the efficiency of decision-making by top management and significantly reduce the financial and time costs of business trips.

“We are also planning to introduce a solution based on 1C: UPP 8 to interact with the traffic police, prepare and print out PTS, transit numbers,” Ganin notes. - All data will be grouped in a single place of information storage - a car card, where all its identification numbers, color, body number, etc. will be entered, then this data will be used in terms of reference, when printing PTS, numbers, references-accounts. Such integration will give the company's customers the opportunity to receive ready-made titles and transit numbers along with the car, which will make it possible to register cars with the traffic police faster.

Automated workstations

performance requirements

List of generated reports

4.4.2. Requirements for the system of planning and production control

The information system should provide enterprise resource planning and order production management.

IS functionality requirements:

1. Configuration management of finished products (FP):

Maintenance of regulatory and reference information on the composition of the GP with the possibility of specifying the period of relevance of the specification and with the possibility of being in production of GP with several different specifications;

Maintenance of regulatory and reference information on the manufacturing technology of products that are part of the GP with the possibility of specifying the period of technology relevance and with the possibility of being in the production of GP with several different technologies;

2. Sales management:

Viewing the history of customer relationships;

Registration/adjustment of the client's application indicating the list of GP, volumes, date of shipment, sale price and any additional conditions;

Viewing the current economic indicators (calculation) of the ordered GP;

3. Production planning:

Formation of an equipment availability schedule indicating the number of available standard hours for each day of the planning period;

Formation of a production plan indicating the manufactured product, its quantity, equipment used, division for each day of the planning period;

Formation of a plan for the needs of production in materials and components;

Control and management of equipment loading according to the generated production plan;

Making adjustments to the production plan during its execution;

Plan-fact analysis of the production plan;

4. Production management:

Formation of shift tasks (work orders) for the manufacture of products;

Assignment/reassignment of performers to outfits and fixing the execution of outfits with an indication of the number of manufactured products, the number of defective products and the reasons for the marriage;

Management of storage and movement of inventory items (inventory and materials) in production;

5. Supply management:

Based on the plan for the need for materials and components, the formation of an application for the purchase indicating the supplier, the range of goods and materials, the quantity and delivery time;

Formation of purchase orders based on single orders for goods and materials from departments;

Monitoring and tracking the process of fulfilling purchase orders;

Operational control of residues;

Plan-fact analysis of deliveries;

6. Cost management:

Formation of the planned (normative) cost of GP;

Fixing actual production costs;

Calculation of the actual cost of GP;

Plan-fact analysis of costs.

Requirements for calculating the standard cost of an order

The standard cost of the product and the entire order is calculated using the following method:

1. The direct material component of the standard cost of a product is formed on the basis of information about the standard composition of this product (specification) and the established discount prices for the goods and materials included in this specification. For the specification, it is allowed to use several items of material costs.

2. The amount of direct wages is calculated on the basis of the standard operational composition of the product. The following are set: the standard duration of each operation, the profession of the worker required for this operation, as well as the category of the worker. Also, monetary rates of standard hours are entered into the system according to the professions of workers and their categories.

3. The normative value of indirect costs is calculated as a percentage of the given base (the value of direct costs for the specified item).

To carry out this calculation, the following data must be available in the Information System:

1. Specification for the manufacture of the product (as well as specifications for the manufacture of all semi-finished products of its own production included in this product);

2. Manufacturing technology of the product and semi-finished products included in it: what operations should be performed and in what time. In addition, for each operation, the profession and rank of the worker necessary for its implementation (for the release of this particular product) are specified;

3. Protocol of accounting prices for used goods and materials;

4. Monetary rates of standard hours for professions and categories.

Requirements for calculating the actual cost of an order

The actual cost of the product and the entire order is calculated using the following method:

1. Direct material costs for the release of the product are calculated on the basis of actual data on the consumption of materials by the shop for production stages. In this case, the cost of all semi-finished products included in this product is first calculated. The sum assessment is carried out in accordance with the methodology adopted in the accounting policy of the enterprise.

2. The wages of direct production workers are calculated based on the data on the closure of shop orders. In the event that records of orders in the IS are not kept, wages are related to direct costs subject to distribution, i.e. is distributed among manufactured products according to a certain base.

3. Depreciation of direct production equipment is included in direct costs if for each stage the equipment (machine) used in this stage is indicated.

4. Direct costs to be distributed:

Basic materials that are consumed less frequently than for each processing stage (for example, chemicals, the rate of which per unit of production is so small that it makes no sense to take into account their cross-cutting consumption even at this rate);

The wages of workers in the absence of information on its distribution by division;

Depreciation of direct equipment in the event that only its total monthly amount is available without breakdown by processing units.

Such costs are allocated to manufactured products according to the selected distribution base (for example, in proportion to direct material costs).

1. General production costs (account BU 25): are distributed to manufactured products in proportion to the selected distribution base. The share of such expenses may or may not remain as part of work in progress in accordance with the Accounting Policy adopted by the enterprise.

2. General business expenses and sales expenses (accounts 26 and 44) are recognized as expenses of the current period and are related to sales expenses. The distribution of such costs to the cost of finished products can be seen using a special report.

performance requirements Information system

<Раздел должен содержать требования к производительности Информационной системы. Вводится в шаблон>.

Reliability Requirements

<Раздел должен содержать требования к надежности Информационной системы. Например:>

Requirements for ensuring reliable (sustainable) functioning of the Information system

Reliable (sustainable) functioning of the Information System must be ensured by the Customer's implementation of a set of organizational and technical measures, the list of which is given below:

1. Organization uninterruptible power supply technical means;

2. Use of licensed software;

3. Regular implementation of the recommendations of the Ministry of Labor and social development of the Russian Federation, set out in the Decree of July 23, 1998 "On the approval of intersectoral standard norms for time for work on after-sales service PC and office equipment and software support”;

4. Regular fulfillment of the requirements of GOST 51188-98. "Data protection. Testing software for availability computer viruses»;

5. Regular backup of databases of the Information System by means of the Information System itself or by means of the used database management system.

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http://www.allbest.ru/

Department of Automation and Information Systems

EXPLANATORY NOTE

to the course project

Topic: Designing an IS module enterprise management

Discipline: "IP Design"

Introduction

1 Information support of a set of tasks

1.1.1 Infological or information model (data schema) and its description

1.2.1. Formalization of calculations (calculation algorithms and problem solving)

2 Technological support

2.1 Scheme of the technological process of collecting, transmitting, processing and issuing information

3 Software set of tasks

3.1 General

Conclusion

List of used literature

INTRODUCTION

Currently, there is a widespread creation and implementation of automated information systems (AIS) at enterprises of various types. AIS has proven its effectiveness in processing information various types and structures. Such systems perform the most routine processes in the shortest possible time.

Over the past twenty years, the volume and circulation of information has significantly increased in all spheres of human life: economic, financial, political, spiritual. And the process of accumulation, processing and use of knowledge is constantly accelerating. Scientists say that every ten years the amount of information doubles. As a result, there is a need to use automatic means that allow you to efficiently store, process and distribute the accumulated data. Despite computerization a large number trade enterprises, employees of these institutions have to perform a large amount of routine work on accounting for goods and customers and the exchange of information between departments. The need to introduce an information system (IS), which automates the main functions of the educational process, is currently beyond doubt.

There are three ways to create an IS:

1 Building an IS based on ERP systems.

2 Development of own IS.

Each of these areas has both strengths and weaknesses. The disadvantage of the first approach, in addition to the extremely high cost of a license for an ERP system, is the significant laboriousness of setting up and adapting the system, which inevitably entails the need for consulting support, maintenance and implementation of IS, which further increases its cost.

The IS module "Enterprise Management" is designed to automate the work of employees of trade enterprises. The system has a database containing information about suppliers, employees and customers.

automated system out-of-machine in-machine

1 INFORMATION SUPPORT OF THE COMPLEX OF TASKS

1.1 Off-machine information provision

To create a practical user application on a computer and work with it in some subject area data from the off-machine sphere must be transferred to a machine medium, where they form an on-machine information base.

Off-machine information support (Figure 1) includes an off-machine information base (IB) and its maintenance tools.

Posted on http://www.allbest.ru/

Figure 1 Off-machine information provision

IS combines the actual information of the non-machine sphere of the subject area, and the means of organizing and maintaining it are designed to ensure work with it. The off-machine information base is formed by the data contained in the documents.

The off-machine information base includes normative-reference, planned (that is, conditionally permanent) information and operational (accounting) information of a certain subject area. An approximate typical composition of information of one and another type is shown in Figure 2.

Solutions for information base involve determining the composition of the information necessary to solve user problems. In addition, the logical structure of information should be identified, which allows you to proceed to the stage of formalization and data modeling necessary for automated processing. The structuring of information in the non-machine sphere is reflected in its presentation by separate structural units, their grouping in documents, and ordering according to classification criteria.

Posted on http://www.allbest.ru/

Figure 2 Composition of the off-machine infobase

1.1.1 Infological or information model (data schema) and its description.

Database design consists of building a set of interrelated data models. The most important stage in database design is the development of an infological (information-logical) model of the subject area that is not DBMS-oriented. In the infological model, by means of data structures, the composition and structure of data, as well as information needs, are reflected in an integrated form.

The information-logical model of the subject area reflects the subject area in the form of a set of information objects and their structural relationships.

The infological domain model is built first. A preliminary infological model is built at the pre-project stage and then refined at later stages of database design. Then, on its basis, conceptual (logical), internal (physical) and external models are built.

The purpose of infological modeling is to provide the most natural ways for a person to collect and present the information that is supposed to be stored in created base data.

The design of infological modeling consists in obtaining semantic models that reflect information content specific subject area. At this stage, abstraction, study, perception, description and limitation of the subject area is performed. Further, the knowledge gained is presented in the form of mathematical formulas, diagrams, relationships, etc.

The goal of normalization is to eliminate logical errors. The importance of normalization lies in the fact that it allows you to break up large relationships, as a rule, containing a lot of information redundancy. Into smaller logical units that group only data that is combined only "by nature". Thus, each table in a relational database satisfies the condition that the position at the intersection of each row and column of the table always contains a single value, and there can never be multiple such values.

In this course project, input documents are used: databases for personnel, goods and customers.

As a result of studying the input documents, an infological data model (IDM) was created, a graphical representation of the ILM in a canonical form, clearly showing the hierarchical relationship of subordination of information objects (Figure 3).

Posted on http://www.allbest.ru/

Figure 3 Infological model of the IS "Enterprise Management" module

1.1.2 Characteristics of the input information

The input information is understood as all the information necessary to solve the problem and located on various media: primary documents, machine media, in the memory of a personal computer.

From the rational organization of the input information of a manufacturing enterprise, the methods of collecting, registering, transmitting, storing and processing information, its composition and timely receipt depend on the efficiency and effectiveness of managing production processes.

Input information for developing in the course project automated system is an:

Information about the type of material;

Material color information;

Information about the texture of the material;

Information about the manufacturer;

Information about employees of the enterprise;

Information about positions;

Information about the amount of material;

Information about the price of the material;

Information about the width of the canvas;

The database consists of 7 tables and 5 directories. The table "Materials" (Figure 4) is used to store material data. Information in this table is entered when the goods arrive at the warehouse.

Figure 4 Table "Materials"

The "Customers" table (Figure 5) is used to store information about customers.

Figure 5 "Customers" table

Table "Employees" (Figure 6) is used to store information about employees this enterprise. Information in the table "Employees" is entered by the employment of a new employee.

Figure 6 Employees table

The "Orders" table (Figure 7) is used to store information about orders for the installation of stretch ceilings.

Figure 7 Orders table

Table "Manufacturer" (Figure 8) is used to store information about suppliers of materials.

Figure 8 "Manufacturer" table

The "Incoming" table (Figure 9) and the "Consumption" table (Figure 10) are used to store information about the receipt and consumption of material.

Figure 9 "Arrival"

Figure 10 "Consumption"

Reference books "Type of material" (Figure 11), "Color" (Figure 12), "Texture" (Figure 13), "Position" (Figure 14), "Status" (Figure 15) are used to clarify the data in the tables presented in the form of codes.

Figure 11 Directory "Type of material"

Figure 12 "Color" reference book

Figure 13 Directory "Invoice"

Figure 14 Directory "Position"

Figure 15 Directory "Status"

1.1.3 Characteristics of the result information

The output information during the operation of the information system will be the data displayed on the site page. The designed software product will provide the ability to add, delete, sort, search for information. The user can calculate the cost of installing a stretch ceiling, as well as calculate the remaining material, indicating the date of receipt of the material. Data on material consumption for the past period will be displayed in the form of a chart.

1.2 Intramachine implementation of a set of tasks

The information that the information system will operate on is organized in the form of a database created using MySQL tools (Figure 16).

Figure 16 Database schema created using MySQL

1.2.1 Formalization of calculations (calculation algorithms and problem solving)

To obtain output documentation, the input data is converted according to a certain algorithm.

When calculating the cost of installing a stretch ceiling, you must fill out the "Cost" form, which contains 3 fields and data entry: ceiling width, ceiling length, material texture. After filling in these fields, the program requests from the database MySQL data, data on the cost of the material with this invoice. The cost is calculated by multiplying the area of the ceiling covering by the cost of the material per 1 m 2.

In the "Expenses" form, when you click on the "Remains" button, the program module calculates the amount of material remaining in the warehouse. When filling in two fields in the "Balance" form: date of receipt of material, date of consumption of material, from the database MySQL query data are taken on the amount of material receipt in this month and how much you spend this month. The rest of the material is calculated by the difference between the receipt and consumption of material for a specific period of time.

1.2.2 Structural scheme using the software package (dialog tree)

System menu - is the main form of dialogue in applied systems data processing, containing commands designed to perform specific tasks.

The developed application has an intuitive menu. To work with database tables, the IS "Enterprise Management" module consists of:

viewing and editing forms;

forms for calculating the cost of the ceiling;

forms of presentation of graphic information.

2 TECHNOLOGICAL SUPPORT

Technological support (TO) includes a description of the organization of the technology for collecting, transmitting, processing and issuing information, TO reflects the sequence of operations, starting from the method of collecting primary information, including two types of documents (documents, data from which are used to correct reference information and documents, representing operational information used for calculations) and ending with the formation of result information. It also provides a diagram of the technological process of collecting, transmitting, processing and issuing information and instructional maps of the main operations of the technological process, reflecting the step-by-step description of the technology.

3 SOFTWARE OF THE COMPLEX OF TASKS

The software of the complex of tasks was executed in the PHP programming environment. The choice of environment is due to the wide possibilities of this programming language for creating applications designed to work with electronic archives (databases).

Due to its compact size and a narrow range of tasks to be solved, the project at this design stage is devoid of an installation package, and therefore, the distribution of the software product is carried out by direct copying.

3.1 General

The IS module "Enterprise Management" is made using the technology based its services on Windows-systems, with access to MySQL.

MySQL is currently one of the most popular DBMS. Some of the reasons for this popularity include:

A high degree of versatility and reasonableness of the interface, which is designed to work with users of various qualifications. In particular, a system for managing database objects has been implemented, which makes it possible to flexibly and quickly switch from the design mode to the mode of their direct operation;

Deeply developed possibilities of integration with other software products;

It should be noted that, a significant reason for such a wide distribution of MySQL is the availability of this software product.

3.2 Description of software modules

The information system includes modules that perform all the necessary operations on data. These modules contain forms for adding, deleting, sorting and searching for information. In addition, the "Orders" module calculates the price for installing a stretch ceiling, entering only its dimensions, and in the "Expenses" module, you can see a chart of material costs depending on the month.

When you open the "Products" page, we get to the form, where there are 6 main modules of the program: "Orders", "Materials", "Expense", "Incoming", "Customers", "Employees".

When you press the buttons "Orders", "Materials", "Expense", "Incoming", "Customers", "Employees", we get to the appropriate form in which you can add, delete, sort, search for information in the database. When you click on one of these buttons, we automatically get to the form with lines for filling in information for deleting, sorting, adding, or searching, after filling, which displays the summary information.

In the "Orders" form there is a "Cost" button, when clicked, you go to the form in which you need to fill in the field for the length, width of the room and the texture of the material, the result will display the cost of installing a stretch ceiling.

In the "Expense" form, when you click on the "Expense Schedule" button, a chart image is displayed with graphical information about the expenses of a relative time period.

CONCLUSION

The result of writing a course project is the creation of an application for employees of trade enterprises. The developed program can significantly increase labor productivity and automate their work.

Using PHP, HTML and MySQL tools to create applications that work in the operating room Windows system and, in particular, database applications, has made it possible to create a software product that is maximally oriented towards the end user.

All the necessary work on the implementation of methods for accessing information stored in the database, modifying it, maintaining the database in its entirety is open to the user in order to successfully solve the whole range of emerging problems related to the use of information stored in the database. Moreover, the software interface makes it as easy as possible to work with the database.

All functions performed by the information system have been carefully checked and tested during the development process.

BIBLIOGRAPHY

1. Welling L. Development of Web applications using PHP and MySQL. Williams Publishing House, 2003. - 288 p.: ill.

2. References for PHP and MySQL. http://www.php.su/books/.

3. Help files for phpMySQL_Admin

4. http://www.php.ru

5. http://www.mysql.ru

Hosted on Allbest.ru

The relationship of information subsystems of the enterprise

How are they related Information Systems within the enterprise? The usual path for a medium-sized Russian company is to start implementing information technologies with the automation of the work of accounting, personnel department and workflow. The data of these systems is the most formalized, the processes are easily automated. The widely used packages "1C: Accounting", "Boss: HR", "LanDocs", "LanStaff", "Salary", etc. allow you to build yourself up with any applications and, thus, integrate them into the general information system of the enterprise. Rice. 7.1 shows how the modules of a company's information system are related to each other. The TPS module serves the main production and auxiliary processes and is usually the main source for other information modules. ESS is the main recipient of data and internal systems from the external environment.

Rice. 7.1.

Other systems also exchange data. And here comes one of the most difficult questions for a leader - search for the optimal degree of integration. It's tempting to have a completely integrated system, but such integration is extremely time consuming and expensive. And it’s better not to even say what the maintenance of such a system costs. Therefore, the need for integrated systems must be weighed against the difficulty and cost of a large-scale IC. There is no standard level of integration or centralization - each leader must(or with the help of a consulting firm) to solve this difficult problem.

Relationships between DSS and TPS collection, KWS,

Introduction

1. Analytical part

1.1 Organizational structure of the company

2 Analysis of the software and hardware of the customer service department and the work department

Design part

2.1 Description of the subject area

2 Feasibility study of development and implementation methods

3 Database design

4 Conceptual data model in the Chen standard

5 ER diagram in ERwin environment

6 Model analysis

7 Physical design stage

8 Implementation of basic queries

Conclusion

List of information sources

Introduction

Numerous firms and enterprises need the services of cleaning companies.

A cleaning company is a company that provides comprehensive cleaning services. A set of activities designed to clean and maintain cleanliness in residential, commercial and industrial premises, including cleaning facades, washing shop windows and other exterior surfaces of buildings, is a very complex process consisting of many different stages involving a large number of participants. The effectiveness of the organization of cleaning needs clear coordination of actions in relation to all its employees.

The advantage of specialized companies is determined by the following factors:

high quality of services;

the cost of services is not higher than the cost of maintaining your own cleaning service;

· expenses for the services of cleaning companies are deductible from taxable income;

· specialists of cleaning companies perform exclusive and complex specialized works (for example, crystallization of marble coatings);

Efficiency - cleaning is done at a time when it is convenient for the Customer.

Today, cleaning is one of the most dynamically developing and stable business sectors in Russia. More and more companies are interested in purchasing high-quality and reliable cleaning services, which places great demands on competent and balanced work with clients.

All the advantages listed above require the use of modern information technologies in the work of cleaning companies, such as specialized information systems, such as CRM systems.

Customer Relationship Management (CRM, CRM-system, short for Customer Relationship Management) is an application software for organizations designed to automate strategies for interacting with customers (clients), in particular, to increase sales, optimize marketing and improve customer service by storing information about customers and the history of relationships with them, establishing and improving business processes and subsequent analysis of the results. CRM system is applicable in any business where the client is personalized, where competition is high and success depends on providing the most favorable conditions for the client. - an interaction model that believes that the center of the entire business philosophy is the client, and the main activities are measures to support effective marketing, sales and customer service. Support for these business goals includes the collection, storage and analysis of information about consumers, suppliers, partners, as well as about the company's internal processes. Functions to support these business goals include sales, marketing, consumer support.

The "heart" of any CRM-system is a database of both individuals and legal entities that interact with your company within the framework of the enterprise. These are not only customers, but also company branches, partners, suppliers, competitors. The customer database is a valuable asset in itself, and competent management data in the CRM system allows you to use information in your work with maximum efficiency. The client base is consolidated, the organization receives full information about its customers and their preferences and, based on this information, builds an interaction strategy.

A single database of clients and a complete history of relationships with them, together with powerful CRM analytical tools, allows you to retain and develop existing clients, identifying the most valuable, and attract new customers.

The main function of a CRM system is to help managers plan sales, organize transparent transaction management and optimize sales channels. The system stores a complete history of communication with customers, which helps sales departments to analyze customer behavior, create suitable offers for them, and win loyalty. Other features of CRM systems are presented below:

Planning and coordinating contacts with clients;

collection and typing of all possible information about customers;

control over long-term or complex transactions;

analysis of each stage of project implementation or deal making;

· Formalization of all processes focused on interaction with buyers.

This variety software products is most suitable for those organizations that lead long-term and multi-stage projects that involve a large number of employees or several departments. Since the number of contracts concluded per unit of time is small, each transaction takes many days and even months. This means that each project requires an exceptionally individual approach. In such conditions, it is necessary to take care of customer loyalty. To do this, it is necessary not only to ensure an individual approach, but also strict adherence to the designated deadlines, the terms of the contract, as well as the coordinated work and punctuality of all involved employees.

1. Analytical part

The MAKS group of companies was founded in 2000. The main activity is a diversified complex services sector. Since the formation of the MAKS Group of Companies, a number of types of services offered have been constantly expanding. To date, MAKS Group offers the following types of services (Fig. 1):

· protection of objects, groups of fast reaction, console protection, private detective services;

Ensuring the most efficient and uninterrupted operation of serviced real estate objects;

Preliminary analysis of existing security systems;

Comprehensive cleaning.

Rice. 1. Service sectors of MAKS Group

"MAKS PROTECTION" developed along with the sphere of non-state security. Object security, rapid response teams, remote security, private detective services, all this is included in the standard package of services. The division employs specialists in IT infrastructure who are engaged in emergency assistance to foreign citizens and know all the nuances of escorting goods and valuables.

Practice shows that security is not just uniformed guards, but a whole range of measures aimed at preventing and preventing potential threats. These measures are part of the "MAX PROTECTION" integrated security system, developed by experts with many years of experience.

For customers, an integrated approach to security means that communication with all security services goes through a single contractor. The company relieves customers of the need to coordinate the work of several unrelated organizations. The presence of a single operational center allows you to instantly and effectively respond to any emergency situations.

"MAX PROTECTION" is constantly improving and developing new concepts for security. Advanced foreign technologies are being introduced into our work and we develop our own solutions for specific tasks. However, even the most advanced automated security systems cannot completely replace a person. Therefore, special attention is paid to improving the professionalism of MAX PROTECTION employees. Specialists regularly attend advanced training courses, and their level of training is tested during internal audits. Strict selection of personnel allows minimizing the influence of the human factor in the services of "MAKS PROTECTION".

The management team of "MAKS PROTECTION" is aimed at an individual approach to clients. Specialists conduct a multi-level analysis and, based on its results, offer the most effective solutions to ensure the security of the client. At the same time, the field of activity of "MAKS PROTECTION" is not limited to private clients. Employees are familiar with the protection of state facilities and have proven themselves in this area.

The division's approach to end-to-end security services is unparalleled and ensures maximum efficiency in any situation.

The "MAX OPERATION" division has been operating on the market for more than ten years. The main task is to ensure the most efficient and uninterrupted operation of serviced real estate objects. At the same time, the statute of limitations for putting an object into operation and the value of the level of complexity of the equipment do not play a role.

The comprehensive service offered by "MAX OPERATION" includes technical support equipment and engineering systems, as well as security proposals. This list includes maintenance and other scheduled and unscheduled work; design and installation of fire alarms and video surveillance systems, as well as a range of repair and construction services. MAX OPERATION clients receive a full package of services from one contractor, which can significantly reduce costs and facilitate interaction between services.

The specialists of this division are always ready to offer several options for working with the object, depending on its specifics and the wishes of the client. An individual approach allows the customer to rely on full mutual understanding with the contractor and, as a result, obtaining the highest quality result.

V modern world equipment and equipment of modern buildings is becoming more diverse and complex. The main goal of this division is to provide comprehensive services for the operation of buildings. All employees understand the latest achievements of science and technology and use them in accordance with the recommendations and standards of manufacturers. This is the only way to ensure that the operability and security of an object of any complexity is maintained at the proper level.

The MAX CONSULTING subdivision has been engaged in consulting and auditing in the field of security for over 10 years. The main task of the division is a comprehensive analysis of the situation, as well as a miscalculation of development prospects, taking into account the existing problems of the company, as well as the individual characteristics of the business.

Employees of "MAX CONSULTING" carry out a preliminary analysis of existing security systems. Naturally, such work is carried out in close cooperation with the customer's employees, who will bring you up to date and help take into account all the features.

The audit of the company's activities is carried out by highly qualified specialists who will not only be able to identify critical points in an already used system, but also offer ways to eliminate them. All consultants have served in law enforcement agencies in their time and have significant experience in Russia and abroad.

Also, MAX CONSULTING specialists provide assistance in eliminating crisis situations, increase the efficiency of business processes and effectively manage information systems and IT risks of the company.

The department's consultants set themselves the task of building mutually beneficial relationships in order to continue cooperation with the client on a long-term basis. "MAX CONSULTING" offers its clients audit services, legal services in the field of criminal, labor, tax, and other types of law.

The scope of services provided by the company "MAX CLEANING" is the cleaning of premises. In Russia, the first cleaning companies appeared in 1992-1994. along with the emergence of the first joint ventures, which at that time were the only consumers of their services. During 2012, the increase in the volume of the cleaning services market in Russia in monetary terms amounted to about 9 billion rubles - in 2011 its volume was 45 billion rubles, and by the end of 2012, the cleaning market was already estimated by them at 54 billion rubles. During the past year, 2014, the volumes Russian market cleaning services increased, according to analysts, by about 10-11 billion rubles, and amount to about 65 billion rubles at the end of last year.

Among the largest regions for the provision of cleaning services are Moscow, Krasnoyarsk Territory, Tyumen region, Republic of Bashkortostan, St. Petersburg, Khanty-Mansi Autonomous Okrug. Moscow accounts for 100-150 million dollars of the domestic cleaning market.

According to experts, there are more than 300 cleaning companies operating in the capital's market. According to the size of the business, they can be conditionally divided into 4 large categories.

· the largest operators (the number of employees is more than 500 people) ~ 67%;

· large operators (staff up to 500 people) ~ 15%;

· average operators (staff up to 200 people) ~ 7%;

· small operators (staff up to 50 people) ~ 11%.

The range of standard services provided by almost all market players includes daily complex cleaning, as well as washing of vertical surfaces. Further, about 93% of all cleaning companies offer services for grinding and polishing granite, marble, porcelain stoneware and ceramic tiles, as well as cleaning the surrounding areas and specialized cleaning services after construction work and fires. About 87% of the companies participating in the study provide additional services and personnel, for example, packers, loaders and others, and 80% of cleaning services market players also provide their customers with services for landscaping adjacent and internal territories, garbage and snow removal. The main clients of cleaning companies are supermarkets and shopping centers, manufacturing enterprises, transport and storage companies, medical and sports facilities, office and business centers, large international and Russian companies, banks, government agencies, hotel complexes, entertainment facilities (cinemas, clubs , casino), railway stations and airports.

Rice. 2. Segmentation of consumers of cleaning services

The most lagging market segment, as many studies show, is cleaning in healthcare facilities. This state of affairs is explained by established habits, more stringent standards that exist in such organizations and adopted in them. specific requirements imposed on the cleaning performed by the personnel of the cleaning company.

The criteria for choosing a cleaning company are: authority in the market, the cost and range of services provided, the level of equipment, technologies and chemicals used in the work, the qualifications of the staff, the system for monitoring cleaning performance.

House cleaning is a production necessity in today's business world. Daily cleaning on a contract basis is a fairly developed area of this company. The ability to order both one-time cleaning and draw up a contract for cleaning the premises at regular intervals, with a convenient work schedule for the client, is a very common activity today that requires a large amount of data and is the best suited for an information system design example. Employees of the company pay great attention to the control and quality of cleaning performed. Openness is also an important part of the company's work. The client has the right to know what, how and with what he does this company"MAX CLEANING". All chemicals have hygienic certificates, partners and customers can visit the production base and get acquainted with the technological capabilities and organization of work at the enterprise.

Cleaning is a modern and widely demanded service provided by various cleaning companies. Such cleaning is carried out by specially trained workers and is assessed according to high European standards.

Cleaning appeared in Russia in the early 1990s and, first of all, began to be in demand among large Western companies that were accustomed to professional cleaning in their homeland. The service combines a full range of activities designed to clean and maintain cleanliness in residential, commercial and industrial premises, including cleaning facades, washing shop windows and other exterior surfaces of buildings. The list of services usually also includes garbage collection and snow removal from roofs and adjacent areas.

Currently, all services are carried out by qualified personnel with the help of modern technologies cleaning, using the latest products, specially selected for each surface separately, taking into account its physical, chemical and technical features. The Russian cleaning market has expanded significantly and has great prospects for further development. In a couple of decades, people will forget what self-cleaning is and will turn to a company that provides these services. Indeed, not only the appearance itself, but also the health of the owner depends on the cleanliness of the premises.

The heads of many large and small companies have already managed to make sure that the cleaning offered by specialized companies is much superior in quality to cleaning in the usual sense. Professional cleaning involves bringing the premises to an impeccable condition, such cleaning ultimately saves time, increases the service life of finishing materials and enhances the prestige of the company.

Employees and managers of the company work as a single mechanism, highly appreciate not only the high quality of the services offered, but also analyze the ordered services, forecast the situation on the service market, and, as a result, offer a constructive solution to emergency situations and the most effective solutions to customer needs.

In 2003, in order to improve the quality of customer service, it was decided to open a new division that will provide additional professional cleaning services. MAX CLEANING made its first steps in Moscow. The experience gained in the capital was used to enter the regions. And although at first there were enough problems, the company managed to cope with all the difficulties. In 2005, the company signed a contract with the METRO supermarket chain, becoming one of the first companies to provide cleaning services to supermarkets and hypermarkets. The staff of the company by 2010 consisted of more than 400 people. The staff works at sites throughout Moscow. Already by 2012 "MAX CLEANING" goes to the regions of Russia. The first branches are opened in Krasnoyarsk and Kazan. The quality of professional office cleaning and shopping centers exceeds the expectations of regional customers. The company approached its 10th anniversary with a staff of 700 people. All further experience gave unique knowledge in the field of cleaning.

The reputation of the MAX CLEANING company, which has been established in recent years, is of great importance. It requires taking into account the attitude of each of the employees to their work.

Interaction and communication with customers are essential components of success. It is very important to keep records of contracts with customers, all customer orders that reflect their needs.

Flexible pricing policy, professional level of staff training, use of a variety of equipment and chemicals, allows us to take a leading position in this service sector.

The cleaning company provides its services throughout the Russian Federation. The main part of the service area is the central part of the Russian Federation. The company works with many large legal entities, shown below in the figure:

Rice. 3. Client companies "MAX-CLEANING"

.1 Organizational structure of the company

The MAX CLEANING company consists of different divisions that perform different functions and tasks, the organizational structure of the MAX CLEANING company is shown in Figure 4.

Rice. 4. Structure of the organization

The General Director and his Deputy manage the MAX-CLEANING company, which consists of many departments, including:

· Department of work with clients;

· Department of works.

Customer Service

The scope of work with clients of the company "MAKS-CLEANING" is extensive: its main task is long-term cooperation and service of legal entities, analysis of their needs, level and direction. Negotiating with client companies, getting acquainted with the terms of sale of services provided by "MAX-CLEANING", control over the performance of work - all this is an integral part of the work of this department.

The client company can apply to "MAX-CLEANING" and use the services of complex cleaning of the premises. The manager of the company will help to conclude a contract for long-term cooperation or place a one-time order. He is also obliged to find out all the necessary information from the client, namely:

Information about the company of which he is a representative;

Data on property (premises) in need of complex cleaning;

The client's desire to choose a cleaning class;

Special comments on working with the premises (All information received is processed by the manager and is strictly confidential).

In case of successful conclusion of an agreement or order, the performance of the service will be transferred to the works department, the function of which is the timely and high-quality provision (performance) of cleaning services.

Works department

This department consists of a senior foreman and cleaners, forming separate teams. After the transfer of the contract (order) by the manager of the company to the senior foreman, the stage of distribution of work on cleaning the premises between the cleaners-foremen follows.

Cleaning of the client's premises is carried out within the established time limits specified in the previously executed contract (order). A report on the performance of the service is provided to the senior foreman, who evaluates the work of each cleaner who is part of the team that performed this cleaning. In addition, the senior foreman undertakes to notify the account manager of the status of this order.

After that, the quality of work is checked by the client himself. In case of an unsatisfactory assessment, the manager informs the management of the company for appropriate decisions to be made.

In the course of the analysis of the activities of the departments of the MAX-CLEANING company described above, a business process diagram was built, shown below in Figure 5.

Rice. 5. Business process "MAX-CLEANING"

Both departments require prompt customer service and transactions through workflow automation, as well as rapid reporting and analytical information to provide timely and quality services.

1.2 Analysis of the software and hardware of the customer service department and the work department

Each account manager has a personal computer, telephone and various peripheral devices to work with the necessary information. The company has the Internet, access to which is available to all employees of MAX-CLEANING. All PCs have Windows 7 operating system installed, required package Microsoft office, which includes Access, as well as drivers for working with numerous peripheral devices HP DesktJet. The software and hardware of the customer service department and the work department are shown in Figure 6.

Rice. 6. Software company "MAX-CLEANING"

The company uses a network based on the "client-server" concept. For modern DBMS, the client-server architecture has become the de facto standard.

The basic principle of the "client-server" technology is to divide the functions of a standard interactive application into four groups:

functions of data input and display;

· application functions, characteristic of the subject area;

· fundamental functions of storage and management of resources (databases);

service functions.

The advantages of this system:

· No duplication of server program code by client programs.

· Since all calculations are performed on the server, the requirements for the computers on which the client is installed are reduced.

· All data is stored on the server, which, as a rule, is much better protected than most clients. On the server, it is easier to organize permission control to allow access to data only to clients with the appropriate access rights.

The client provides information to the company's manager, who fixes it on his PC using Microsoft Word 2010, prints out the contract (order) and keeps the document in paper form. Thus, there is a risk of losing all the necessary information, creating the problem of further search for information about the client, his premises and contacts. In addition, the manager does not have the ability to automatically calculate not only the cost of cleaning, depending on its class, the size of the room and the period of maintenance of the contract, but also the number of teams required for timely and high-quality cleaning of the premises.

The basic idea is to divide a network application into several components, each of which implements a specific set of services. The components of such an application can run on different computers, performing server and/or client functions. This improves the reliability, security, and performance of network applications and the network as a whole. The module of the information system developed by me for the MAX-CLEANING company will help to increase the profitability of the enterprise by in-depth analysis of information about its customers, sales systems, will allow the company's management to track key indicators of the quality of work performed under the contract, which is necessary for making strategically important business decisions and effective appraisal of the work of each employee.

The purpose of my final classification work is to develop an information system module for accounting for complex cleaning of various premises.

To achieve this goal, it is necessary to perform the following tasks:

Analyze the activities of the company "MAKS" for cleaning;

Create a list of requirements for database development;

Develop a database using the ER diagram method and the Erwin CASE tool;

Implement queries to manipulate data in the database.

The information system module being developed must meet the following requirements:

· Create and store data on contracts with automatic calculation of the cost of service for the period of the contract;

· Create and store data on orders with automatic calculation of the cost of cleaning, depending on the size of the room and cleaning class;

· Record data on each cleaning;

Keep information about the quality of cleaning of each employee of the brigade;

Calculate the assessment of the team's performance for the company manager;

· Automatic calculation of the number of crews required for cleaning the premises.

I'm guessing the implementation similar system will increase the speed of processing all the necessary documents for the services of a cleaning company, as well as reduce the number of errors when working with clients.

To solve the tasks in the final classification work, the following are used:

· database design method - ER modeling. He is graphic description subject area in terms of "object - property - connection". The use of ER modeling provides many advantages: it makes the analysis of the subject area more focused and specific; allows you to design AIS without reference to a specific target DBMS and select the latter at any time; when changing the used DBMS, it is not necessary to carry out the design again, you only need to take the step of transferring the ER model to the target one (if the target DBMS you have chosen is supported by this CASE tool, then such a transition will generally be performed automatically);

· CASE - means Erwin. The advantage is the ability to create database structure diagrams that allow you to automatically resolve issues related to maintaining its integrity, as well as the independence of the logical model from the used DBMS, which allows you to apply universal methods for exporting it to specific DBMS.

· MS Access DBMS is selected as the target DBMS. Access is a control system relational base data, including all necessary tools to create a local database, a shared database in local network with a file server or a database on a SQL server, as well as for creating user applications that work with these databases. The Access DBMS includes a variety and numerous relatively autonomous software, focused on creating database objects and user applications. Graphical design tools allow the user to create database objects and application objects using numerous graphical elements without programming.

2. Design part

.1 Description of the subject area

The company has its own fleet in Moscow, which includes 5 VOLVO cars, as well as 6 drivers assigned to each car. Teams (3) performing complex cleaning of the premises, in the amount of 3 people each.

Company Services:

· Comprehensive cleaning of premises (commercial, industrial, warehouse and industrial);

The client, which can only be entity, refers to an employee of the company "MAKS" in the position of an order manager. The company provides its services, both at the request of the client himself, and for a long time (from 7 days and more).

With the order manager, the client discusses the following:

The client provides data on the premises in need of maintenance (type of premises, area, location);

The price, measured in rubles, is determined in the contract (100% prepayment):

Ø At the request of the client (one-time) - depends on the area of \u200b\u200bthe premises, the type of cleaning and the number of teams involved.

Ø For a long period - depends on the service period, frequency and area of \u200b\u200bthe room.

・Customer's personal data

・Information about the room

Service life

The number of teams involved

The quality of service will be checked by the client and the company employee who controls the result of the work of the team. In the case of a matching, unsatisfactory assessment of the client and the employee, the working group will be reprimanded up to and including dismissal.

The selection of cars available in the company's fleet will be carried out by a specialist in the selection of vehicles. He will evaluate the required number of crews to service the premises and the vehicles needed for transportation working group to the destination. The selection of machines is carried out immediately after the data provided on the premises by the client before the conclusion of the contract.

.2 Feasibility Study of Development and Implementation Methods

For database design service center It was decided to use the ER-diagram method. This method design was chosen by me, based on the following factors:

The ER-diagram method was taught to us in the discipline of the Database.

The design method using ER diagrams has a number of advantages, namely, visibility; the ability to design a database with a large number of objects and attributes;

ER-diagrams provide an opportunity to analyze the subject area more specifically;

The requirements for knowledge of the SQL language are reduced.

The ER model is based on three elements:

Essence

Attribute

I chose ERwin Data Modeler as the database computer-aided design system.

As a DBMS was chosen Microsoft Access. This DBMS I chose it based on the following factors:

Microsoft Access was taught to me during my training;

Access allows you to quickly and easily create table queries;

When processing Access data uses the SQL query language, which was also taught to me in the Database discipline.

.3 Database Design

Concept design stage

Description of entities.

Entity selection.

Description of links.

Essence		Essence

Premises	Provided
	Serve	Employees

	Need (Included)
	exposed


	consulted	Employees
	signs


	Rates


Employees	Make out
	accept
	Perform
	control
	Perform



	Allows

2.4 Conceptual data model in the Chen standard

2.5 ER diagram in ERwin environment

.6 Model analysis

Composite attribute: - no

Multivalued attribute: Phone (company, representative)

Derived attribute:

Order - cost = Type of cleaning (cost) * Room (area)

Agreement - price = Agreement (term of service / frequency) * Premises (area)

Recursive links: - no

1:1 Relationship: Order Requires Cleaning

Redundant connections: yes

The entity Representative of the company appears:

The client consults with the employee

The client evaluates the Cleaning

The client signs the Agreement

The customer places an order

Premises are maintained by employees

Premises are being cleaned

Employees fulfill the Order

Employees supervise cleaning

Contract obliges Cleaning

M:n connection: yes

Employees are cleaning

Rice. 8. Final model in Erwin

7 Physical design stage

Data schema in the environment of the selected DBMS

Rice. 9. Database schema

2.8 Implementation of basic queries

cleaning database manipulation

Rice. 9. Implementation of the main requests

Displaying a list of contracts for company managers

This request will allow you to view the list of contracts issued by him by the personnel number of the manager who enters the user. Such a request will allow you to quickly view information about the work of a particular manager of the company.

Employees.Tab_Number, Employees.Name, Employees.Phone, Employees.Position, Employees.Department_Number, Contract.Agreement_Number FROM Employees INNER JOIN Contract ON Employees.Tab_Number = Contract.Tab_Number WHERE (((Employees.Tab_Number)=[Enter the personnel number of the manager :]));

Viewing the assessment of the work of cleaners-foremen

This request will be sent to view the assessment of the work of cleaners-foremen. According to the assessment given by the client and the manager of the company, comments and measures to prevent further errors in the work of the cleaners can be put forward.

SELECT Employees.Name, Employees.Position, Cleaning.Customer_Rate, Cleaning.Manager_Rating, Employees.Team_Number FROM Cleaning INNER JOIN (Employees INNER JOIN Dc_Employee ON Employees.Tab_Number = Dec_Employee.Tab_Number) ON Cleaning.CleaningNumber = Dec_Employee.Clean_Number WHERE ((( Cleaning.Cleaning_number)=[Enter cleaning number]));

Displaying a list of orders by the type of cleaning entered

This request will allow you to view the details of the order made by the client.

SELECT [Cleaning Type].[Cleaning Class], [Cleaning Type].Cost, Order.OrderNumber FROM [Cleaning Type] INNER JOIN Order ON [Cleaning Type].[Cleaning Class] = Order.[Cleaning Class] WHERE (([ Cleaning type].[Cleaning class])=[Enter cleaning class:]);

Search for a company - a client in the database

This request will allow the employee to enter the name of the client's company and display a list of previously executed contracts and orders, if any. Thanks to such a request, it will be possible to avoid re-entering information about the client's company.

SELECT Client.[Company Name], Client.Legal_Address, Client.Company_Phone, [Company Representative].Name, [Company Representative].E-mail FROM Client INNER JOIN [Company Representative] ON Client.TR = [Company Representative].TR WHERE ( ((Customer.[Company name])=[Enter company name:]));

Search for not / completed orders

This query will allow you to display a list of completed and unfulfilled orders according to the status entered by the user.

SELECT Order.Order_Number, Order.Status, Order.Date, Order.[Cleaning Class], Order.Due_Date FROM Order WHERE (((Order.Status)=[Enter order status:]));

List of premises by type

This query will allow you to display a list of rooms by the entered

room type by the user.

SELECT Rooms.Address, Rooms.Area, Rooms.Type, Rooms.NC FROM Rooms WHERE (((Rooms.Type)=[Enter room type:]));

List of contracts with a service period of more than 30 days

This query will allow you to display a list of contracts, the service period of which exceeds 30 days.

SELECT Contract.Contract_Number, Contract.Order_Date, Contract.Address, Contract.[Service Term (Days)]FROM Contract WHERE (((Contract.[Service Term (Days)])>30));

· Calculate the cost of orders

This request will allow you to display the cost of the order by the number entered by the user. Thanks to this request, it will be possible to avoid errors when calculating the cost of orders.

SELECT Order.Order_No., ([Cleaning Type].[Cost]+[Cleaning Type].[Number of crews per unit area])*[Rooms].[Area] AS Order_Cost, Order.[Cleaning Class], Order.Status FROM Rooms INNER JOIN ([Cleaning Type] INNER JOIN Order ON [Cleaning Type].[Cleaning Class] = Order.[Cleaning Class]) ON Rooms.Address = Order.Address WHERE (((Order.Order_Number)=[Enter order number :]));