Database

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
	UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

	INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
	SET param_record_identity = LAST_INSERT_ID();

	IF (param_is_contract = TRUE) THEN
	BEGIN
		UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
		UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
	END;
	END IF;

	IF (param_is_recurring = TRUE) THEN
	BEGIN
		REPEAT
			SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);

			-- insert recurring job orders;
			INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
			SET param_record_identity = LAST_INSERT_ID();

			IF (param_is_contract = TRUE) THEN
			BEGIN
				UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
				UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
			END;
			END IF;

			-- add event log;
			CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
			CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
		UNTIL param_job_order_date >= param_max_recurring_date
		END REPEAT;
	END;
	END IF;
END

<pre lang="c#"> String intime = "";
                String outtime = "";
             
                String indatetime=System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                String outdatetime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                using (MySqlConnection con = new MySqlConnection(ConnectionString))
                {
                    String query = "select * from visitor where card_id=@id ORDER BY card_id DESC LIMIT 1";
                    MySqlCommand command = new MySqlCommand(query, con);
                    command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
                    con.Open();
                    using (MySqlDataReader rdr = command.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            intime = rdr["in_time"].ToString();
                            outtime = rdr["out_time"].ToString();
                        }
                    }

                }

                if (((intime == "") && (outtime == "")) || ((intime!="")&&(outtime !="")))
                {
                    using (MySqlConnection con = new MySqlConnection(ConnectionString))
                    {
                        String query = "insert into visitor(card_id,name,age,address,id_proof,contact_person,purpose,in_time) values(@id,@name,@age,@address,@idproof,@contact,@purpose,@in)";
                        MySqlCommand command = new MySqlCommand(query, con);
                        command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
                        command.Parameters.AddWithValue("@name", txtAddVisitorName.Text);
                        command.Parameters.AddWithValue("@age", txtAddVisitorAge.Text);
                        command.Parameters.AddWithValue("@address", txtAddVisitorAddress.Text);
                        command.Parameters.AddWithValue("@idproof", txtAddVisitorIDProof.Text);
                        command.Parameters.AddWithValue("@contact", txtAddVisitorContactPerson.Text);
                        command.Parameters.AddWithValue("@purpose", txtAddVisitorPurpose.Text);
                        command.Parameters.AddWithValue("@in", indatetime);
                        con.Open();
                        command.ExecuteNonQuery();
                        MessageBox.Show("Visitor Added Successfully");

                    }
                }
                else if ((intime != "") && (outtime == ""))
                {
                    using (MySqlConnection con = new MySqlConnection(ConnectionString))
                    {
                        String query = "update visitor set out_time=@out where card_id=@id ORDER BY card_id DESC LIMIT 1";
                        MySqlCommand command = new MySqlCommand(query, con);
                        command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
                        command.Parameters.AddWithValue("@out", outdatetime);
                        con.Open();
                        command.ExecuteNonQuery();
                    }

                }
            }