How to fix sumarizing leave time and sickness time proportionally?

Question

prochotap 0 Newbie Poster

5 Years Ago

In mysql database i created "leave" table:

CREATE TABLE `leave` ( 
`ID_LEAVE` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime DEFAULT NULL,
`END_DATE` datetime DEFAULT NULL
);

INSERT INTO `leave` 
(`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(1, 26, '2019-03-19 07:00:00', '2019-03-19 15:00:00'),
(6, 26, '2019-03-22 07:00:00', '2019-03-25 15:00:00');

"Sickness" table:

CREATE TABLE `sickness` (
  `ID_SICKNESS` int(11) NOT NULL,
  `ID_WORKER` int(11) NOT NULL,
  `BEGIN_DATE` datetime DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL
) 

INSERT INTO `sickness` (`ID_SICKNESS`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(5, 25, '2019-03-19 07:00:00', '2019-03-20 15:00:00'),

"Workers" table:

CREATE TABLE `workers` (
`ID_WORKER` int(11) NOT NULL,
`FNAME` varchar(20) NOT NULL,
`LNAME` varchar(20) NOT NULL
);

INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
(24, 'ADAM', 'GREEN'),
(25, 'ANDREW', 'WORM'),
(26, 'ADAM', 'GAX');

"Orders" table:

CREATE TABLE `orders` (
`ID_ORDER` int(11) NOT NULL,
`DESC_ORDER` varchar(50) NOT NULL,
`NUMBER_ORDER` varchar(30) NOT NULL
);

INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
(19, 'TEST', 'TEST'),
(20, 'TEST2', 'TEST2'),

"Order_status" table:

CREATE TABLE `order_status` (
`ID_STATUS` int(11) NOT NULL,
`ID_ORDER` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime NOT NULL,
`END_DATE` datetime NOT NULL,
) ;

INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(30, 19, 26, '2019-03-18 06:40:21', '2019-03-18 15:22:32' ),
(31, 19, 25, '2019-03-18 06:51:23', '2019-03-18 15:11:10' ),
(32, 19, 26, '2019-03-20 06:23:32', '2019-03-20 15:33:11' ),
(33, 19, 25, '2019-03-20 06:44:42', '2019-03-20 15:16:22' ),
(34, 19, 26, '2019-03-22 06:44:32', '2019-03-22 11:44:54' ),
(35, 19, 25, '2019-03-22 06:59:22', '2019-03-22 12:33:33' ),
(42, 20, 22, '2019-03-18 06:44:23', '2019-03-18 15:11:23' ),
(43, 20, 23, '2019-03-18 06:50:45', '2019-03-18 15:15:44' ),
(44, 20, 22, '2019-03-19 06:40:20', '2019-03-19 15:23:30' ),
(45, 20, 22, '2019-03-21 06:55:45', '2019-03-21 16:03:23' ),
(46, 20, 22, '2019-03-22 06:55:45', '2019-03-22 13:23:44' ),
(47, 20, 23, '2019-03-19 06:45:23', '2019-03-19 15:33:23' ),
(48, 20, 23, '2019-03-20 06:45:23', '2019-03-20 15:22:23' ),
(49, 20, 23, '2019-03-21 06:50:33', '2019-03-21 15:33:11' ),
(50, 20, 23, '2019-03-22 06:44:23', '2019-03-22 12:22:44' ),
(64, 19, 25, '2019-03-19 06:50:32', '2019-03-19 15:33:44' ),
(65, 19, 26, '2019-03-19 06:44:50', '2019-03-19 15:22:33' ),
(66, 19, 25, '2019-03-21 06:50:50', '2019-03-21 15:33:33' );

In desktop application is concerned i wrote that code which it's based on Algorithm which calculates sickness time AND leave time proportionally and the pattern is in below picture:

And the details of that code is here:

private sick_leaves calculate_sickness_leaves(string NrOrder)
        {

        string query1 = string.Format("SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE) FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE orders.ORDER_NUMBER = '" + NrOrder + "';");

        SQLdata.connection.Open();
        using (var command = new MySqlCommand(query1, SQLdata.connection))
        {
            using (var reader1 = command.ExecuteReader())
            {
                while (reader1.Read())
                {
                    date_min = Convert.ToDateTime(reader1[0]);
                    date_max = Convert.ToDateTime(reader1[1]);
                }
            }
        }
        TimeSpan time_of_sickness_to_order = new TimeSpan(0, 0, 0);
        TimeSpan time_of_leave_to_order = new TimeSpan(0, 0, 0);

        for (DateTime date = date_min; date <= date_max; date = date.AddDays(1))
        {
            long hours_sickness_total = 0;
            long hours_leave_total = 0;
            TimeSpan time_of_all_orders;
            TimeSpan time_of_order;

            string query2 = $"SELECT COUNT(sickness.ID_WORKER) AS 'AMOUNT OF WORKERS ON SICKNESS' FROM project1.sickness WHERE DATE(sickness.BEGIN_DATE) <= '{date}' AND DATE(sickness.END_DATE) >= '{date}';";
            hours_sickness_total = SQLdata.load_amount_sql(query2) * 8;

            string query3 = $"SELECT COUNT(leaves.ID_WORKER) AS 'AMOUNT OF WORKERS ON LEAVE' FROM project1.;leaves WHERE DATE(leaves.BEGIN_DATE) <= '{date}' AND DATE(leaves.END_DATE) >= '{date}';";
            hours_leave_total = SQLdata.load_amount_sql(query3) * 8;

            string query4 = $"SELECT IFNULL(TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s'), '00:00:00') AS 'REALIZATION TIME OF ALL ORDERS IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '{date}' AND DATE(order_status.END_DATE) = '{date}');";

            time_of_all_orders = SQLdata.load_time_sql(query4);

            string query5 = $"SELECT IFNULL(TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s'), '00:00:00') AS 'REALIZATION TIME OF ORDER IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '{date}' AND DATE(order_status.END_DATE) = '{ date}') AND orders.ORDER_NUMBER = '{NrOrder}';";

            time_of_order = SQLdata.load_time_sql(query5);

            hours_sickness_total = ((long)(time_of_order.TotalSeconds / time_of_all_orders.TotalSeconds) * hours_sickness_total);
            time_of_sickness_to_order += new TimeSpan(hours_sickness_total * 10000000 * 60 * 60);

            hours_leave_total = ((long)(time_of_order.TotalSeconds / time_of_all_orders.TotalSeconds) * hours_leave_total);
            time_of_leave_to_order += new TimeSpan(hours_leave_total * 10000000 * 60 * 60);

        }

        SQLdata.connection.Close();
        sick_leaves x = new sick_leaves();
        x.sickness_time = time_of_sickness_to_order;
        x.leave_time = time_of_leave_to_order;
        return x;         
    }

And i had to write additional code to load data of:

1) time of order which user can select (on each day) load_time_sql and time of all orders (on each day too) during the selected order.

2) total times of sickness time and leave time of each day during the selected order.

public static TimeSpan load_time_sql(string query)
{
    TimeSpan x = new TimeSpan(0, 0, 0);
    using (var command = new MySqlCommand(query, SQLdata.connection))
    {
        using (var reader1 = command.ExecuteReader())
        {
            while (reader1.Read())
            {
                x = Convert.ToDateTime(reader1[0]).TimeOfDay;
            }
            return x;
        }
    }
}

public static int load_amount_sql(string query)
{
    int x = 0;
    using (var command = new MySqlCommand(query, SQLdata.connection))
    {
        using (var reader1 = command.ExecuteReader())
        {
            while (reader1.Read())
            {
                x = Convert.ToInt32(reader1[0]);
            }
            return x;
        }
    }
 }

Now in this application I'd like to select the "TEST" order. As for as the showing sickness times and leave times of workers in the order is written in below code (sorry for the long lines of code):

 MySqlDataAdapter adapter4 = new MySqlDataAdapter("SELECT workers.FNAME, workers.LNAME, IFNULL(leavesAgg.BEGIN_DATE, 'EMPTY') AS 'BEGIN DATE', IFNULL(leavesAggAgg.END_DATE, 'BRAK') AS 'END DATE', time_format(IFNULL(SEC_TO_TIME(SUM(leavesAggAgg.vtime)),'00:00:00'), '%H:%i:%s') AS 'LEAVES SUM TIME' FROM project1.workers LEFT JOIN (SELECT leaves.ID_WORKER, leaves.BEGIN_DATE, leaves.END_DATE, (SUM(TIME_TO_SEC(TIMEDIFF(TIME(leaves.END_DATE), TIME(leaves.BEGIN_DATE))))) AS vtime FROM (SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) calendar INNER JOIN project1.leaves ON calendar.date_value BETWEEN DATE(leaves.BEGIN_DATE) AND DATE(leaves.END_DATE) LEFT JOIN (SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' FROM project1.order_status GROUP BY ID_WORKER) ordstat ON leaves.ID_WORKER = ordstat.ID_WORKER WHERE NOT WEEKDAY(date_value) IN (5, 6) AND NOT DATE(date_value) IN (SELECT HOLIDAY_DATE FROM project1.holidays) AND leaves.END_DATE <= MAX_END_DATE AND leaves.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY leaves.ID_WORKER) leavesAgg ON leavesAgg.ID_WORKER = workers.ID_WORKER LEFT JOIN (SELECT order_status.ID_WORKER, orders.ORDER_NUMBER, orders.DESC_ORDER, SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime FROM project1.order_status LEFT JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER GROUP BY order_status.ID_WORKER) order_statusAgg ON workers.ID_WORKER = order_statusAgg.ID_WORKER WHERE order_statusAgg.ORDER_NUMBER ='" + txt_NrOrder.Text + "' GROUP BY workers.ID_WORKER;", connection);

MySqlDataAdapter adapter5 = new MySqlDataAdapter("SELECT workers.FNAME, workers.LNAME, IFNULL(sicknessAgg.BEGIN_DATE, 'EMPTY') AS 'BEGIN DATE', IFNULL(sicknessAgg.END_DATE, 'EMPTY') AS 'END DATE', time_format(IFNULL(SEC_TO_TIME(SUM(sicknessAgg.ctime)),'00:00:00'), '%H:%i:%s') AS 'SUM SICKNESS TIME' FROM project1.workers LEFT JOIN (SELECT sickness.ID_WORKER, sickness.BEGIN_DATE, sickness.END_DATE, (SUM(TIME_TO_SEC(TIMEDIFF(TIME(sickness.END_DATE), TIME(sickness.BEGIN_DATE))))) AS ctime FROM (SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) calendar INNER JOIN project1.sickness ON calendar.date_value BETWEEN DATE(sickness.BEGIN_DATE) AND DATE(sickness.END_DATE) LEFT JOIN (SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' FROM project1.order_status GROUP BY ID_WORKER) ordstat ON sickness.ID_WORKER = ordstat.ID_WORKER WHERE NOT WEEKDAY(date_value) IN (5, 6) AND NOT DATE(date_value) IN (SELECT HOLIDAY_DATE FROM proba1.holidays) AND sickness.END_DATE <= MAX_END_DATE AND sickness.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY sickness.ID_WORKER) sicknessAgg ON sicknessAgg.ID_WORKER = workers.ID_WORKER LEFT JOIN (SELECT order_status.ID_WORKER, orders.ORDER_NUMBER, orders.DESC_ORDER, SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER GROUP BY order_status.ID_WORKER) order_statusAgg ON workers.ID_WORKER = order_statusAgg.ID_WORKER WHERE order_statusAgg.NUMBER_ORDER ='" + txt_NrOrder.Text + "' GROUP BY workers.ID_WORKER;", connection);

DataSet ds4 = new DataSet();
adapter4.Fill(ds4, "workers");
dataGridView4.DataSource = ds4.Tables["workers"];
DataSet ds5 = new DataSet();
adapter5.Fill(ds5, "workers");
dataGridView5.DataSource = ds5.Tables["workers"];

Then generating results on the datagridview from "private sick_leaves calculate_sickness_leaves" is written like this:

 sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

 cu = calculate_sickness_leaves(txt_NrOrder.Text);
 var source = new BindingSource();
 source.DataSource = cu;
 dataGridView2.DataSource = source;

After generating raport of this order i get the results in below:

+---------+---------+---------------+---------------+------------+------------+
|  FNAME  | LNAME   |  BEGIN_DATE        | END_DATE              | LEAVE_TIME | 
+---------+---------+---------------+---+-----------+------------+------------+
|  ADAM   |  GAX    | 2019-03-19 7:00:00 | 2019-03-19 15:00:00   | 08:00:00   |        
+---------+---------+---------------+---------------+------------+------------+
|  ANDREW |  WORM   | EMPTY              | EMPTY                 | 00:00:00   |
+---------+---------+---------------+---------------+------------+------------+

+---------+---------+---------------+---------------+------------+---------------+
|  FNAME  | LNAME   |  BEGIN_DATE        | END_DATE              | SICKNESS_TIME | 
+---------+---------+---------------+---+-----------+------------+---------------+
|  ADAM   |  GAX    | EMPTY              | EMPTY                 | 00:00:00      |        
+---------+---------+---------------+---------------+------------+---------------+
|  ANDREW |  WORM   | 2019-03-19 7:00:00 | 2019-03-20 15:00:00   | 16:00:00      |
+---------+---------+---------------+---------------+------------+---------------+

+----------------+--------------+
| sickness_time  | leave_time   |   
+----------------+--------------+
|  00:00:00      | 00:00:00     |
+----------------+--------------+

And as for as the results of sickness_time and leave_time are concerned in my opinion are incorrect. These should be other values than "00:00:00" because i saw the leave by ADAM GAX and sickness by ANDREW WORM. Then the calculates should be correct and calculated proportionally of course.

I have a question: How can i fix this error of sumarizing leave time and sickness time proportionally and correctly? Any ideas? Can i count for any help or advice? Thanks in advance.

There are my codes :
-First code is in (*code snippet)

Second code SQLData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql;
using MySql.Data.MySqlClient;

namespace ControlDataBase
{
    class SQLdata
    {

    public static MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");

    public static TimeSpan load_time_sql(string query)
    {
        TimeSpan x = new TimeSpan(0, 0, 0);
        using (var command = new MySqlCommand(query, SQLdata.connection))
        {
            using (var reader1 = command.ExecuteReader())
            {
                while (reader1.Read())
                {
                    x = Convert.ToDateTime(reader1[0]).TimeOfDay;
                }
                return x;
            }
        }
    }

    public static int load_amount_sql(string query)
    {
        int x = 0;
        using (var command = new MySqlCommand(query, SQLdata.connection))
        {
            using (var reader1 = command.ExecuteReader())
            {
                while (reader1.Read())
                {
                    x = Convert.ToInt32(reader1[0]);
                }
                return x;
            }
        }
    }
}
}

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Windows.Forms.DataVisualization.Charting;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Diagnostics;

namespace ControlDataBase
{
    public partial class GenerateChartsOfOrders : Form
    {
        DateTime date_max;
        DateTime date_min;
        sick_leaves cu = new sick_leaves();
        public GenerateChartsOfOrders()
        {
            InitializeComponent();
        }

        private void GenerateChartsOfOrders_Load(object sender, EventArgs e)
        {
            MySqlConnection connection = new MySqlConnection();
            connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=";

            connection.Open();
            string selectQuery = "SELECT * FROM project1.orders";
            MySqlCommand command = new MySqlCommand(selectQuery, connection);
            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                cmbNr_Order.Items.Add(reader.GetString("ORDER_NUMBER"));
            }

            connection.Close();

            txt_NrOrder.Enabled = false;
            txt_NrOrder.Visible = false;
        }

        private void cmbNr_Order_SelectedIndexChanged(object sender, EventArgs e)
        {
            MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
            MySqlCommand command = new MySqlCommand("SELECT * FROM project1.zlecenia WHERE ORDER_NUMBER='" + cmbNr_Order.Text + "'", connection);
            connection.Open();
            command.ExecuteNonQuery();
            MySqlDataReader dr;
            dr = command.ExecuteReader();
            while (dr.Read())
            {
                string number = (string)dr["ORDER_NUMBER"].ToString();
                txt_NrOrder.Text = number;
            }
            connection.Close();
        }

        public void loaddata2()
        {
            MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
            MySqlDataAdapter adapter4 = new MySqlDataAdapter("SELECT workers.FNAME, workers.LNAME, IFNULL(leavesAgg.BEGIN_DATE, 'EMPTY') AS 'BEGIN DATE', IFNULL(leavesAggAgg.END_DATE, 'BRAK') AS 'END DATE', time_format(IFNULL(SEC_TO_TIME(SUM(leavesAggAgg.vtime)),'00:00:00'), '%H:%i:%s') AS 'LEAVES SUM TIME' FROM project1.workers LEFT JOIN (SELECT leaves.ID_WORKER, leaves.BEGIN_DATE, leaves.END_DATE, (SUM(TIME_TO_SEC(TIMEDIFF(TIME(leaves.END_DATE), TIME(leaves.BEGIN_DATE))))) AS vtime FROM (SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) calendar INNER JOIN project1.leaves ON calendar.date_value BETWEEN DATE(leaves.BEGIN_DATE) AND DATE(leaves.END_DATE) LEFT JOIN (SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' FROM project1.order_status GROUP BY ID_WORKER) ordstat ON leaves.ID_WORKER = ordstat.ID_WORKER WHERE NOT WEEKDAY(date_value) IN (5, 6) AND NOT DATE(date_value) IN (SELECT HOLIDAY_DATE FROM project1.holidays) AND leaves.END_DATE <= MAX_END_DATE AND leaves.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY leaves.ID_WORKER) leavesAgg ON leavesAgg.ID_WORKER = workers.ID_WORKER LEFT JOIN (SELECT order_status.ID_WORKER, orders.ORDER_NUMBER, orders.DESC_ORDER, SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime FROM project1.order_status LEFT JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER GROUP BY order_status.ID_WORKER) order_statusAgg ON workers.ID_WORKER = order_statusAgg.ID_WORKER WHERE order_statusAgg.ORDER_NUMBER ='" + txt_NrOrder.Text + "' GROUP BY workers.ID_WORKER;", connection);
            MySqlDataAdapter adapter5 = new MySqlDataAdapter("SELECT workers.FNAME, workers.LNAME, IFNULL(sicknessAgg.BEGIN_DATE, 'EMPTY') AS 'BEGIN DATE', IFNULL(sicknessAgg.END_DATE, 'EMPTY') AS 'END DATE', time_format(IFNULL(SEC_TO_TIME(SUM(sicknessAgg.ctime)),'00:00:00'), '%H:%i:%s') AS 'SUM SICKNESS TIME' FROM project1.workers LEFT JOIN (SELECT sickness.ID_WORKER, sickness.BEGIN_DATE, sickness.END_DATE, (SUM(TIME_TO_SEC(TIMEDIFF(TIME(sickness.END_DATE), TIME(sickness.BEGIN_DATE))))) AS ctime FROM (SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) calendar INNER JOIN project1.sickness ON calendar.date_value BETWEEN DATE(sickness.BEGIN_DATE) AND DATE(sickness.END_DATE) LEFT JOIN (SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' FROM project1.order_status GROUP BY ID_WORKER) ordstat ON sickness.ID_WORKER = ordstat.ID_WORKER WHERE NOT WEEKDAY(date_value) IN (5, 6) AND NOT DATE(date_value) IN (SELECT HOLIDAY_DATE FROM proba1.holidays) AND sickness.END_DATE <= MAX_END_DATE AND sickness.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY sickness.ID_WORKER) sicknessAgg ON sicknessAgg.ID_WORKER = workers.ID_WORKER LEFT JOIN (SELECT order_status.ID_WORKER, orders.ORDER_NUMBER, orders.DESC_ORDER, SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER GROUP BY order_status.ID_WORKER) order_statusAgg ON workers.ID_WORKER = order_statusAgg.ID_WORKER WHERE order_statusAgg.NUMBER_ORDER ='" + txt_NrOrder.Text + "' GROUP BY workers.ID_WORKER;", connection);

            DataSet ds4 = new DataSet();
            adapter4.Fill(ds4, "workers");
            dataGridView4.DataSource = ds4.Tables["workers"];
            DataSet ds5 = new DataSet();
            adapter5.Fill(ds5, "workers");
            dataGridView5.DataSource = ds5.Tables["workers"];

            sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

            cu = calculate_sickness_leaves(txt_NrOrder.Text);
            var source = new BindingSource();
            source.DataSource = cu;
            dataGridView2.DataSource = source;
        }

        //ALGORITHM TO CALCULATE SICKNESS AND LEAVES
        private sick_leaves calculate_sickness_leaves(string NrOrder)
        {
            string query1 = string.Format("SELECT MIN(order_status.BEGIN_DATE), MAX(order_status.END_DATE) FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE orders.ORDER_NUMBER = '" + NrOrder + "';");

            SQLdata.connection.Open();
            using (var command = new MySqlCommand(query1, SQLdata.connection))
            {
                using (var reader1 = command.ExecuteReader())
                {
                    while (reader1.Read())
                    {
                        date_min = Convert.ToDateTime(reader1[0]);
                        date_max = Convert.ToDateTime(reader1[1]);
                    }
                }
            }
            TimeSpan time_of_sickness_to_order = new TimeSpan(0, 0, 0);
            TimeSpan time_of_leave_to_order = new TimeSpan(0, 0, 0);

            for (DateTime date = date_min; date <= date_max; date = date.AddDays(1))
            {
                long hours_sickness_total = 0;
                long hours_leave_total = 0;
                TimeSpan time_of_all_orders;
                TimeSpan time_of_order;

                string query2 = $"SELECT COUNT(sickness.ID_WORKER) AS 'AMOUNT OF WORKERS ON SICKNESS' FROM project1.sickness WHERE DATE(sickness.BEGIN_DATE) <= '{date}' AND DATE(sickness.END_DATE) >= '{date}';";
                hours_sickness_total = SQLdata.load_amount_sql(query2) * 8;

                string query3 = $"SELECT COUNT(leaves.ID_WORKER) AS 'AMOUNT OF WORKERS ON LEAVE' FROM project1.;leaves WHERE DATE(leaves.BEGIN_DATE) <= '{date}' AND DATE(leaves.END_DATE) >= '{date}';";
                hours_leave_total = SQLdata.load_amount_sql(query3) * 8;

                string query4 = $"SELECT IFNULL(TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s'), '00:00:00') AS 'REALIZATION TIME OF ALL ORDERS IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '{date}' AND DATE(order_status.END_DATE) = '{date}');";

                time_of_all_orders = SQLdata.load_time_sql(query4);

                string query5 = $"SELECT IFNULL(TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))), '%H:%i:%s'), '00:00:00') AS 'REALIZATION TIME OF ORDER IN DAY' FROM project1.order_status INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER WHERE (DATE(order_status.BEGIN_DATE) = '{date}' AND DATE(order_status.END_DATE) = '{ date}') AND orders.ORDER_NUMBER = '{NrOrder}';";

                time_of_order = SQLdata.load_time_sql(query5);

                hours_sickness_total = ((long)(time_of_order.TotalSeconds / time_of_all_orders.TotalSeconds) * hours_sickness_total);
                time_of_sickness_to_order += new TimeSpan(hours_sickness_total * 10000000 * 60 * 60);

                hours_leave_total = ((long)(time_of_order.TotalSeconds / time_of_all_orders.TotalSeconds) * hours_leave_total);
                time_of_leave_to_order += new TimeSpan(hours_leave_total * 10000000 * 60 * 60);

            }

            SQLdata.connection.Close();
            sick_leaves x = new sick_leaves();
            x.sickness_time = time_of_sickness_to_order;
            x.leave_time = time_of_leave_to_order;
            return x;         
        }

        private void Generate_raport_Click(object sender, EventArgs e)
        {
            loaddata2();
        }

        private void Zamknij_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Edited 5 Years Ago by Dani because: Not a working code snippet

2 Contributors
1 Reply
409 Views
1 Week Discussion Span
Latest Post 5 Years Ago Latest Post by Qzmo

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Qzmo 0 Newbie Poster · Answer 1 · 2019-07-16T08:39:47+00:00

Hi,

I've looked all over the Internet and couldn't find any example of a DateTime value being converted to seconds using the TIME_TO_SEC() function as used in your calculation algorithm (Line 118/122: "TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE) ") .

It seems only Time values may be converted to seconds, and that may be causing an automatic null result. Maybe try using TIMEDIFF() or TIMESTAMPDIFF() functions instead; they seem suited to what you want to do.

Regards

References:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-to-sec

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

https://www.w3schools.com/sql/func_mysql_time_to_sec.asp

https://www.w3schools.com/sql/func_mysql_timediff.asp