CREATE DATABASE SinoChemPosDatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

#============================create tables==============================
USE SinoChemPosDatabase;
CREATE TABLE IF NOT EXISTS jy_info (
	jihao varchar(10) NULL,
	status varchar(10) NULL,
	youpin varchar(12) NULL,
	qty decimal NULL,
	amount decimal NULL,
	fzqty decimal NULL,
	fzamount decimal NULL
);


USE SinoChemPosDatabase;
CREATE TABLE IF NOT EXISTS xiaofei2(
	jihao int NULL,
	youpin varchar(12) NULL,
	qty decimal NULL,
	danjia decimal NULL,
	amount decimal NULL,
	xf_date datetime NULL,
	xf_time varchar(8) NULL,
	liushuino double NULL,
	fzqty decimal NULL,
	fzamount decimal NULL,
	cardno varchar(128) NULL
);


USE SinoChemPosDatabase;
#====in some sites, the table name could be: yjpz
CREATE TABLE IF NOT EXISTS jyjpz(
	jihao int NULL,
	ABtype varchar(12) NULL,
	qianghao int NULL,
	luojiqh int NULL
);


#============================create procedures==============================
drop procedure if exists insert_jy_info_by_jihao;
delimiter $$

create procedure insert_jy_info_by_jihao(
	IN input_jihao varchar(10), 
	IN input_status varchar(10),
    IN input_youpin varchar(12),
    IN input_qty decimal,
    IN input_amount decimal,
    IN input_fzqty decimal,
    IN input_fzamount decimal)
begin
IF NOT EXISTS (select * from jy_info where jihao = input_jihao) THEN
    insert into jy_info (jihao, status, youpin, qty, amount, fzqty, fzamount)
		values(input_jihao, input_status, input_youpin, input_qty, input_amount,  input_fzqty, input_fzamount);
END IF;

end $$



#============================test==============================
#call insert_jy_info_by_jihao('2','failed','92#',4,5,6,7);