

CREATE TABLE reglements (
  num_reg varchar(20) NOT NULL,
  mov int(1) DEFAULT 1,
  codetier varchar(20) DEFAULT '',
  raison varchar(100) DEFAULT '',
  contact varchar(65) DEFAULT '',
  type int(1) NOT NULL DEFAULT 0,
  mode varchar(20) DEFAULT '',
  date date DEFAULT '0000-00-00',
  num_cheque varchar(20) DEFAULT NULL,
  montant double DEFAULT 0,
  etat int(1) NOT NULL DEFAULT 0,
  pcreate datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  pupdate datetime(3) DEFAULT NULL,
  heure time GENERATED ALWAYS AS (DATE_FORMAT(`pcreate`, '%H:%i:%s')) VIRTUAL,
  pdate datetime(3) GENERATED ALWAYS AS (CONCAT(`date`, ' ', CAST(`pcreate` AS time))) VIRTUAL
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

ALTER TABLE reglements
ADD INDEX date (date);

ALTER TABLE reglements
ADD INDEX etat (etat);

ALTER TABLE reglements
ADD INDEX codetier (codetier);

CREATE TABLE pieces (
  piece_id bigint(20) UNSIGNED NOT NULL,
  mov int(1) DEFAULT 1,
  piece varchar(3) DEFAULT '',
  num_piece varchar(20) DEFAULT '',
  num_org varchar(15) DEFAULT '',
  date date DEFAULT NULL,
  codetier varchar(20) DEFAULT '',
  brute_ht double DEFAULT 0,
  total_ht double DEFAULT 0,
  total_tva double DEFAULT 0,
  ristourne double GENERATED ALWAYS AS (`brute_ht` - `total_ht`) VIRTUAL,
  cagnotte double DEFAULT 0,
  remise double DEFAULT 0,
  remisep double DEFAULT 0,
  remisettc int(1) DEFAULT 0,
  remisetype int(1) DEFAULT 0,
  remise_sur_ht double GENERATED ALWAYS AS (IF(`remisettc` = 1, 0, `remise`)) VIRTUAL,
  remise_sur_ttc double GENERATED ALWAYS AS (IF(`remisettc` = 0, 0, `remise`)) VIRTUAL,
  total_ttc double GENERATED ALWAYS AS (`total_ht` + `total_tva`) VIRTUAL,
  retenuep double DEFAULT 0,
  retenue double GENERATED ALWAYS AS (`total_ttc` * (`retenuep` / 100)) VIRTUAL,
  duree_retenue int(2) DEFAULT 0,
  taxe_supp1 double DEFAULT 0,
  taxe_supp2 double DEFAULT 0,
  taxe_supp3 double DEFAULT 0,
  taxe_supp double GENERATED ALWAYS AS (`taxe_supp1` + `taxe_supp2` + `taxe_supp3`) VIRTUAL,
  timbre double DEFAULT 0,
  net_a_payer double GENERATED ALWAYS AS (`total_ttc` + `taxe_supp` + `timbre` - `remise_sur_ttc` - `retenue`) VIRTUAL,
  transformer int(1) DEFAULT 0,
  pcreate datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  pupdate datetime(3) DEFAULT NULL,
  heure time GENERATED ALWAYS AS (DATE_FORMAT(`pcreate`, '%H:%i:%s')) VIRTUAL,
  pdate datetime(3) GENERATED ALWAYS AS (CONCAT(`date`, ' ', CAST(`pcreate` AS time))) VIRTUAL
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

ALTER TABLE pieces
ADD INDEX mov (mov);

ALTER TABLE pieces
ADD INDEX piece (piece);

ALTER TABLE pieces
ADD INDEX num_piece (num_piece);

ALTER TABLE pieces
ADD INDEX date (date);

ALTER TABLE pieces
ADD INDEX codetier (codetier);


INSERT INTO reglements(num_reg, mov, codetier, raison, contact, type, mode, date, num_cheque, montant, etat, pcreate, pupdate) VALUES
('0021/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-04-03', NULL, 200000, 0, '2022-08-28 15:35:28.895', NULL),
('0184/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-04-10', NULL, 146000, 0, '2022-08-28 15:35:28.895', NULL),
('1027/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-05-15', NULL, 250000, 0, '2022-08-28 15:35:28.895', NULL),
('1820/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-06-02', NULL, 380000, 0, '2022-08-28 15:35:28.895', NULL),
('1875/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-06-09', NULL, 400000, 0, '2022-08-28 15:35:28.895', NULL),
('2572/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-06-29', NULL, 300000, 0, '2022-08-28 15:35:28.895', NULL),
('2888/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-07-17', NULL, 200000, 0, '2022-08-28 15:35:28.895', NULL),
('2889/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-07-17', NULL, 220000, 0, '2022-08-28 15:35:28.895', NULL),
('314/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-04-14', NULL, 100000, 0, '2022-08-28 15:35:28.895', NULL),
('3240/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-07-26', NULL, 400000, 0, '2022-08-28 15:35:28.895', NULL),
('4118/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-08-16', NULL, 280000, 0, '2022-08-28 15:35:28.895', NULL),
('4472/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-08-25', NULL, 200000, 0, '2022-08-28 15:35:28.895', NULL),
('585/2022', 1, '30165', 'EURL GAM MEDICAL', '', 0, 'Espèces', '2022-04-24', NULL, 300000, 0, '2022-08-28 15:35:28.895', NULL);


INSERT INTO pieces(piece_id, mov, piece, num_piece, num_org, date, codetier, brute_ht, total_ht, total_tva, cagnotte, remise, remisep, remisettc, remisetype, retenuep, duree_retenue, taxe_supp1, taxe_supp2, taxe_supp3, timbre, transformer, pcreate, pupdate) VALUES
(213800039, 1, 'RC', '056/2022', '', '2022-05-08', '30165', -3430, -3430, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-08 16:05:51', '2022-05-09 13:46:16'),
(256012833, 1, 'BL', '2651/2022', NULL, '2022-06-29', '30165', 224574, 224574, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-06-29 08:54:01', '2022-06-29 08:54:45'),
(627311138, 1, 'BL', '2142/2022', NULL, '2022-06-14', '30165', 12500, 12500, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-06-14 08:16:01', '2022-06-14 08:17:57'),
(1551091156, 1, 'RC', '053/2022', '', '2022-05-08', '30165', -32500, -32500, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-08 08:23:45', '2022-05-08 17:06:18'),
(2095826749, 1, 'BL', '1649/2022', NULL, '2022-05-31', '30165', 318610, 318610, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-31 12:00:13', '2022-07-26 09:59:39'),
(2155529602, 1, 'BL', '299/2022', NULL, '2022-04-14', '30165', 197963, 197963, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-04-14 10:16:40', '2022-05-29 12:55:23'),
(2186062033, 1, 'BL', '764/2022', NULL, '2022-05-05', '30165', 248886, 248886, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-05 10:02:24', '2022-07-26 09:03:11'),
(2193917017, 1, 'BL', '1940/2022', NULL, '2022-06-08', '30165', 8550, 8550, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-06-08 10:30:52', '2022-06-08 10:31:21'),
(2252426633, 1, 'BL', '0936/2022', NULL, '2022-05-12', '30165', 36300, 36300, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-12 11:42:42', '2022-05-12 11:55:50'),
(3399327516, 1, 'BL', '729/2022', NULL, '2022-04-28', '30165', 82000, 82000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-04-28 15:07:33', '2022-04-28 15:10:05'),
(3552358527, 1, 'BL', '144/2022', NULL, '2022-04-10', '30165', 139315, 139315, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-04-10 09:36:18', '2022-04-21 10:39:19'),
(3567579877, 1, 'BL', '1380/2022', NULL, '2022-05-24', '30165', 13000, 13000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-24 16:31:42', '2022-05-25 08:47:57'),
(4233952946, 1, 'BL', '1090/2022', NULL, '2022-05-16', '30165', 214100, 214100, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2022-05-17 09:29:07', '2022-07-26 10:58:06');