delimiter $$
create table lk_country (
id int(11) not null,
nice_name varchar(150) default null,
iso3 varchar(3) default null,
numcode smallint(6) default null,
phone_code int(5) default null,
name varchar(150) default null,
iso varchar(2) default null,
primary key (id)
)
$$
delimiter $$
CREATE TABLE lk_services (
id INT(11) NOT NULL AUTO_INCREMENT,
service_name VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
service_price DECIMAL(18, 3) NOT NULL,
service_cost DECIMAL(18, 3) DEFAULT NULL,
PRIMARY KEY (id)
)
$$
--
-- create table supplier
--
delimiter $$
create table supplier (
id int(11) not null auto_increment,
name varchar(200) not null,
phone varchar(50) not null,
country_id int(11) not null,
email varchar(250) not null,
city varchar(200) not null,
address varchar(250) not null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table supplier
add constraint country_supplier_id foreign key (country_id)
references lk_country(id);
$$
--
-- create table lk_warehouse
--
delimiter $$
create table lk_warehouse (
id int(11) not null auto_increment,
name varchar(200) not null,
country_id int(11) not null,
email varchar(200) not null,
phone varchar(50) not null,
city varchar(200) not null,
zip_code varchar(20) default null,
visible int(11) default 1,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table lk_warehouse
add constraint country_warehouse_id foreign key (country_id)
references lk_country(id);
$$
--
-- create table transfer
--
delimiter $$
create table transfer (
id int(11) not null auto_increment,
date date default null,
from_wharehouse_id int(11) default null,
to_warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(18, 3) default null,
transfer_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default null,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table transfer
add constraint transfer_from_warehouse_id foreign key (from_wharehouse_id)
references lk_warehouse(id) on delete set null;
$$
delimiter $$
alter table transfer
add constraint transfer_to_warehouse_id foreign key (to_warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create table stickyshop_users
--
delimiter $$
create table stickyshop_users (
id int(11) not null auto_increment,
username varchar(255) default null,
password varchar(255) default null,
email varchar(255) default null,
fullname varchar(255) default null,
groupid varchar(255) default null,
active int(11) default null,
data_group_id int(11) not null default 1,
ext_security_id varchar(100) default null,
warehouse_id_ int(11) default null,
warehouse_id int(11) default null,
tel varchar(255) default null,
photo text default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table stickyshop_users
add constraint users_warehouse_id foreign key (warehouse_id_)
references lk_warehouse(id);
$$
--
-- create table return_purchase
--
delimiter $$
create table return_purchase (
id int(11) not null auto_increment,
date date default null,
supplier_id int(11) default null,
warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(18, 3) default null,
purchase_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default 2,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase
add constraint ret_purchase_supplier_id foreign key (supplier_id)
references supplier(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase
add constraint ret_purchase_warehouse_id foreign key (warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create table purchase
--
delimiter $$
create table purchase (
id int(11) not null auto_increment,
date date default null,
supplier_id int(11) default null,
warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(18, 3) default null,
purchase_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default 2,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table purchase
add constraint purchase_supplier_id foreign key (supplier_id)
references supplier(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table purchase
add constraint purchase_warehouse_id foreign key (warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create table customer
--
delimiter $$
create table customer (
id int(11) not null auto_increment,
name varchar(200) not null,
phone varchar(50) not null,
country_id int(11) not null,
email varchar(250) not null,
city varchar(200) not null,
address varchar(250) not null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table customer
add constraint country_customer_id foreign key (country_id)
references lk_country(id);
$$
--
-- create table sale
--
delimiter $$
create table sale (
id int(11) not null auto_increment,
date date default null,
customer_id int(11) default null,
warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(13, 3) default null,
sale_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default 2,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
payment_method int(11) default null
,pos_received_amount int(11) default null
,return_amount decimal(18, 3) default null
,quotation_id int(11) default null
,service_total DECIMAL(18, 3) DEFAULT NULL
,primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table sale
add constraint sale_customer_id foreign key (customer_id)
references customer(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table sale
add constraint sale_warehouse_id foreign key (warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create table sale_det
--
delimiter $$
create table sale_det (
id int(11) not null auto_increment,
sale_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
warehouse_id int(11) default null,
primary key (id)
)
$$
--
-- create index sale_det on table sale_det
--
delimiter $$
alter table sale_det
add index sale_det(sale_id);
$$
--
-- create index sale_discounttype on table sale_det
--
delimiter $$
alter table sale_det
add index sale_discounttype(discounttype_id);
$$
--
-- create index sale_product_id on table sale_det
--
delimiter $$
alter table sale_det
add index sale_product_id(product_id);
$$
--
-- create index sale_taxtype on table sale_det
--
delimiter $$
alter table sale_det
add index sale_taxtype(taxtype);
$$
--
-- create foreign key
--
delimiter $$
alter table sale_det
add constraint sale_sale_det_id foreign key (sale_id)
references sale(id);
$$
--
-- create table quotation
--
delimiter $$
create table quotation (
id int(11) not null auto_increment,
date date default null,
customer_id int(11) default null,
warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(18, 3) default null,
sale_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default 2,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table quotation
add constraint quotation_warehouse_id foreign key (warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table quotation
add constraint customer_id_quotation foreign key (customer_id)
references customer(id) on delete set null;
$$
--
-- create table lk_payment_status
--
delimiter $$
create table lk_payment_status (
id int(11) not null,
name varchar(200) default null,
language varchar(255) not null default '',
primary key (id, language)
)
$$
--
-- create table lk_invoice_status
--
delimiter $$
create table lk_invoice_status (
id int(11) not null,
name varchar(200) default null,
language varchar(255) not null,
primary key (id, language)
)
$$
--
-- create table return_sale
--
delimiter $$
create table return_sale (
id int(11) not null,
date date default null,
customer_id int(11) default null,
warehouse_id int(11) default null,
ordertax decimal(18, 3) default null,
discount decimal(18, 3) default null,
shipping decimal(18, 3) default null,
sale_status_id int(11) default null,
note text character set utf8 collate utf8_unicode_ci default null,
reference varchar(200) default null,
paid decimal(18, 3) default null,
due decimal(18, 3) default null,
grandtotal decimal(18, 3) default null,
payment_status_id int(11) default 2,
totalordertax decimal(18, 3) default null,
totaldiscount decimal(18, 3) default null,
totalshipping decimal(18, 3) default null,
user_add varchar(200) default null,
user_addt datetime default null,
user_upd varchar(200) default null,
user_updt datetime default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale
add constraint return_sale_customer_id foreign key (customer_id)
references customer(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale
add constraint return_sale_payment_status_id_cons foreign key (payment_status_id)
references lk_payment_status(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale
add constraint return_sale_status_id_cons foreign key (sale_status_id)
references lk_invoice_status(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale
add constraint return_sale_warehouse_id foreign key (warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create table lk_products_sell_status
--
delimiter $$
create table lk_products_sell_status (
id int(11) not null,
status varchar(200) default null,
primary key (id)
)
$$
--
-- create table lk_brand
--
delimiter $$
create table lk_brand (
id int(11) not null auto_increment,
name varchar(200) not null,
description text character set utf8 collate utf8_unicode_ci default null,
image text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
--
-- create table product
--
delimiter $$
CREATE TABLE product (
id INT(11) NOT NULL,
name VARCHAR(200) NOT NULL,
codeproduct VARCHAR(500) NOT NULL,
category_id INT(11) NOT NULL,
brand_id INT(11) DEFAULT NULL,
barcodesymbology_id INT(11) DEFAULT NULL,
productcost DECIMAL(18, 3) NOT NULL,
productprice DECIMAL(18, 3) NOT NULL,
productunit INT(11) NOT NULL,
saleunit INT(11) NOT NULL,
purchaseunit INT(11) NOT NULL,
stockalert INT(11) DEFAULT 0,
ordertax DECIMAL(18, 3) DEFAULT 0.000,
taxtype_id INT(11) NOT NULL,
note TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
variant_id TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
image TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
bar_code_auto INT(11) DEFAULT NULL,
product_sell_status INT(11) DEFAULT NULL,
averageproductcost DECIMAL(18, 3) DEFAULT NULL,
is_composite INT(11) DEFAULT NULL,
PRIMARY KEY (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table product
add constraint product_sell_status_id foreign key (product_sell_status)
references lk_products_sell_status(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table product
add constraint product_brand_id foreign key (brand_id)
references lk_brand(id) on delete set null;
$$
--
-- create table productavgcost
--
delimiter $$
create table productavgcost (
id int(11) not null auto_increment,
productid int(11) default null,
productcost decimal(18, 3) default null,
username varchar(4000) default null,
changedate datetime default null,
stockmount decimal(18, 3) default null,
invoice_id int(11) default null,
invoice_type int(11) default null,
primary key (id)
)
$$
--
-- create index idx_productavgcost_productid on table productavgcost
--
delimiter $$
alter table productavgcost
add index idx_productavgcost_productid(productid);
$$
--
-- create foreign key
--
delimiter $$
alter table productavgcost
add constraint fk_productavgcost_productid foreign key (productid)
references product(id);
$$
--
-- create table lk_smsgateway
--
delimiter $$
create table lk_smsgateway (
id int(11) not null auto_increment,
name varchar(200) default null,
primary key (id)
)
$$
--
-- create table lk_papersize
--
delimiter $$
create table lk_papersize (
id int(11) not null,
name varchar(200) default null,
primary key (id)
)
$$
--
-- create table lk_currency
--
delimiter $$
create table lk_currency (
id int(11) not null auto_increment,
currency_code varchar(5) not null,
currency_name varchar(200) not null,
symbol varchar(200) not null,
primary key (id)
)
$$
--
-- create table system_settings
--
delimiter $$
create table system_settings (
id int(11) not null auto_increment,
company_name varchar(250) default null,
default_currency_id int(11) default 278,
default_email varchar(200) default null,
logo text character set utf8 collate utf8_unicode_ci default null,
footer varchar(250) default null,
default_warehouse_id int(11) default null,
default_customer_id int(11) default null,
developed_by varchar(200) default null,
company_address varchar(250) default null,
note_to_customer text character set utf8 collate utf8_unicode_ci default null,
show_phone varchar(1) default '1',
show_address varchar(1) default '1',
show_email varchar(1) default '1',
show_customer varchar(1) default '1',
show_tax_discount varchar(1) default '1',
show_barcode varchar(1) default '1',
show_note_to_customer varchar(1) default '1',
payment_stripe_key text character set utf8 collate utf8_unicode_ci default null,
payment_stripe_secret text character set utf8 collate utf8_unicode_ci default null,
delete_stripe_api_keys varchar(1) default '1',
smtp_host varchar(250) default null,
smpt_port varchar(20) default null,
smtp_username varchar(200) default null,
smtp_password varchar(200) default null,
smtp_encryption varchar(20) default null,
sms_sid varchar(200) default null,
sms_token varchar(250) default null,
sms_from varchar(250) default null,
sms_getway_id int(11) default null,
company_phone varchar(200) default null,
papersize_id int(11) default null,
sell_in_minus varchar(5) default 'no',
db_server varchar(200) default null,
db_port int(11) default null,
db_user varchar(200) default null,
db_password varchar(200) default null,
db_bin_path varchar(200) default null,
default_language int(11) default 1,
current_folder varchar(250) not null,
db_database varchar(255) default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table system_settings
add constraint currency_system_settings_id foreign key (default_currency_id)
references lk_currency(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table system_settings
add constraint customer_system_settings_id foreign key (default_customer_id)
references customer(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table system_settings
add constraint setting_papersize_id foreign key (papersize_id)
references lk_papersize(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table system_settings
add constraint warehouse_system_settings_id foreign key (default_warehouse_id)
references lk_warehouse(id) on delete set null;
$$
--
-- create foreign key
--
delimiter $$
alter table system_settings
add constraint smsgateway_systemsetting_id foreign key (sms_getway_id)
references lk_smsgateway(id);
$$
--
-- create table lk_payment_type
--
delimiter $$
create table lk_payment_type (
id int(11) not null,
name varchar(200) default null,
primary key (id)
)
$$
--
-- create table sale_payment
--
delimiter $$
create table sale_payment (
id int(11) not null auto_increment,
sale_id int(11) default null,
date date default null,
receivedamount decimal(18, 3) default null,
payment_typeid int(11) default null,
payamount decimal(18, 3) default null,
paydocument text character set utf8 collate utf8_unicode_ci default null,
note mediumtext default null,
primary key (id)
)
$$
--
-- create view sales_payment
--
delimiter $$
create view sales_payment
as select * from sale_payment
$$
--
-- create foreign key
--
delimiter $$
alter table sale_payment
add constraint payment_type_cons_sale foreign key (payment_typeid)
references lk_payment_type(id);
$$
--
-- create foreign key
--
delimiter $$
alter table sale_payment
add constraint sale_id foreign key (sale_id)
references sale(id) on delete cascade;
$$
--
-- create table return_sale_payment
--
delimiter $$
create table return_sale_payment (
id int(11) not null auto_increment,
sale_id int(11) default null,
date date default null,
receivedamount decimal(18, 3) default null,
payment_typeid int(11) default null,
payamount decimal(18, 3) default null,
paydocument text character set utf8 collate utf8_unicode_ci default null,
note mediumtext character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale_payment
add constraint return_payment_type_cons_sale foreign key (payment_typeid)
references lk_payment_type(id);
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale_payment
add constraint return_sale_id foreign key (sale_id)
references return_sale(id) on delete cascade;
$$
--
-- create table return_purchase_payment
--
delimiter $$
create table return_purchase_payment (
id int(11) not null auto_increment,
return_purchase_id int(11) default null,
date date default null,
receivedamount decimal(18, 3) default null,
payment_typeid int(11) default null,
payamount decimal(18, 3) default null,
paydocument text character set utf8 collate utf8_unicode_ci default null,
note text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_payment
add constraint return_payment_purchase_id foreign key (return_purchase_id)
references return_purchase(id) on delete cascade;
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_payment
add constraint return_payment_type_cons foreign key (payment_typeid)
references lk_payment_type(id);
$$
--
-- create table quotation_payment
--
delimiter $$
create table quotation_payment (
id int(11) not null auto_increment,
quotation_id int(11) default null,
date date default null,
receivedamount decimal(18, 3) default null,
payment_typeid int(11) default null,
payamount decimal(18, 3) default null,
paydocument text character set utf8 collate utf8_unicode_ci default null,
note text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_payment
add constraint payment_type_cons_quotation foreign key (payment_typeid)
references lk_payment_type(id);
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_payment
add constraint quotation_id foreign key (quotation_id)
references quotation(id) on delete cascade;
$$
--
-- create table purchase_payment
--
delimiter $$
create table purchase_payment (
id int(11) not null auto_increment,
purchase_id int(11) default null,
date date default null,
receivedamount decimal(18, 3) default null,
payment_typeid int(11) default null,
payamount decimal(18, 3) default null,
paydocument text character set utf8 collate utf8_unicode_ci default null,
note text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_payment
add constraint payment_purchase_id foreign key (purchase_id)
references purchase(id) on delete cascade;
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_payment
add constraint payment_type_cons foreign key (payment_typeid)
references lk_payment_type(id);
$$
--
-- create table lk_tax_type
--
delimiter $$
create table lk_tax_type (
id int(11) not null,
name varchar(200) default null,
primary key (id)
)
$$
--
-- create table lk_discounttype
--
delimiter $$
create table lk_discounttype (
id int(11) not null,
name varchar(200) default null,
primary key (id)
)
$$
--
-- create table transfer_det
--
delimiter $$
create table transfer_det (
id int(11) not null auto_increment,
transfer_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
stock decimal(18, 3) default null,
expdate date default null,
to_warehouse_id int(11) default null,
from_wharehouse_id int(11) default null,
primary key (id)
)
$$
delimiter $$
alter table transfer_det
add index transfer_transfer_det(transfer_id);
$$
delimiter $$
alter table transfer_det
add constraint transfer_discounttype foreign key (discounttype_id)
references lk_discounttype(id);
$$
delimiter $$
alter table transfer_det
add constraint transfer_product_id foreign key (product_id)
references product(id);
$$
delimiter $$
alter table transfer_det
add constraint transfer_purchase_det foreign key (transfer_id)
references transfer(id);
$$
delimiter $$
alter table transfer_det
add constraint transfer_taxtype foreign key (taxtype)
references lk_tax_type(id);
$$
delimiter $$
create table return_sale_det (
id int(11) not null auto_increment,
return_sale_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
warehouse_id int(11) default null,
primary key (id)
)
$$
delimiter $$
alter table return_sale_det
add constraint return_sale_det foreign key (return_sale_id)
references return_sale(id);
$$
delimiter $$
alter table return_sale_det
add constraint return_sale_discounttype foreign key (discounttype_id)
references lk_discounttype(id);
$$
delimiter $$
alter table return_sale_det
add constraint return_sale_product_id foreign key (product_id)
references product(id);
$$
--
-- create foreign key
--
delimiter $$
alter table return_sale_det
add constraint return_sale_taxtype foreign key (taxtype)
references lk_tax_type(id);
$$
--
-- create table return_purchase_det
--
delimiter $$
create table return_purchase_det (
id int(11) not null auto_increment,
return_purchase_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
warehouse_id int(11) default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_det
add constraint return_purchase_discounttype foreign key (discounttype_id)
references lk_discounttype(id);
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_det
add constraint return_purchase_product_id foreign key (product_id)
references product(id);
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_det
add constraint return_purchase_purchase_det foreign key (return_purchase_id)
references return_purchase(id);
$$
--
-- create foreign key
--
delimiter $$
alter table return_purchase_det
add constraint return_purchase_taxtype foreign key (taxtype)
references lk_tax_type(id);
$$
--
-- create table quotation_det
--
delimiter $$
create table quotation_det (
id int(11) not null auto_increment,
quotation_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
warehouse_id int(11) default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_det
add constraint quotation_det foreign key (quotation_id)
references quotation(id);
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_det
add constraint quotation_discounttype foreign key (discounttype_id)
references lk_discounttype(id);
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_det
add constraint quotation_product_id foreign key (product_id)
references product(id);
$$
--
-- create foreign key
--
delimiter $$
alter table quotation_det
add constraint quotation_taxtype foreign key (taxtype)
references lk_tax_type(id);
$$
--
-- create table purchase_det
--
delimiter $$
create table purchase_det (
id int(11) not null auto_increment,
purchase_id int(11) default null,
product_id int(11) default null,
taxtype int(11) default null,
ordertax decimal(18, 3) default null,
unitcost decimal(18, 3) default null,
qty decimal(18, 3) default null,
discounttype_id int(11) default null,
discount decimal(18, 3) default null,
subtotal decimal(18, 3) default null,
taxamount decimal(18, 3) default null,
discountamount decimal(18, 3) default null,
grandtotalbeforetaxanddiscount decimal(18, 3) default null,
warehouse_id int(11) default null,
stock decimal(18, 3) default null,
expdate date default null,
primary key (id)
)
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_det
add constraint purchase_discounttype foreign key (discounttype_id)
references lk_discounttype(id);
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_det
add constraint purchase_product_id foreign key (product_id)
references product(id);
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_det
add constraint purchase_purchase_det foreign key (purchase_id)
references purchase(id);
$$
--
-- create foreign key
--
delimiter $$
alter table purchase_det
add constraint purchase_taxtype foreign key (taxtype)
references lk_tax_type(id);
$$
--
-- create table lk_baseunit
--
delimiter $$
create table lk_baseunit (
id int(11) not null auto_increment,
name varchar(200) not null,
primary key (id)
)
$$
delimiter $$
create table lk_unit (
id int(11) not null auto_increment,
name varchar(200) default null,
shortname varchar(20) default null,
baseunit_id int(11) default null,
primary key (id)
)
$$
delimiter $$
alter table lk_unit
add constraint baseunit_unit_id foreign key (baseunit_id)
references lk_baseunit(id) on delete set null;
$$
delimiter $$
create table expenses_category (
id int(11) not null auto_increment,
category varchar(255) not null,
primary key (id)
)
$$
delimiter $$
create table expenses (
id int(11) not null auto_increment,
wharehouse_id int(11) default null,
expensive_category_id int(11) default null,
amount decimal(10, 0) default 18,
details text character set utf8 collate utf8_unicode_ci default null,
date date default null,
reference varchar(200) default null,
primary key (id)
)
$$
delimiter $$
alter table expenses
add constraint expensive_category_id foreign key (expensive_category_id)
references expenses_category(id) on delete set null;
$$
delimiter $$
alter table expenses
add constraint expensive_warehouse_id foreign key (wharehouse_id)
references lk_warehouse(id) on delete set null;
$$
delimiter $$
create table stickyshop_ugrights (
tablename varchar(300) not null,
groupid int(11) not null,
accessmask varchar(10) default null,
page mediumtext default null,
primary key (tablename(50), groupid)
)
$$
delimiter $$
create table stickyshop_ugmembers (
username varchar(300) not null,
groupid int(11) not null,
primary key (username(50), groupid)
)
$$
delimiter $$
create table stickyshop_uggroups (
groupid int(11) not null auto_increment,
label varchar(300) default null,
primary key (groupid)
)
$$
delimiter $$
create table product_warehouse (
warehouse_id int(11) not null,
product_id int(11) not null,
qty decimal(18, 3) default null,
adqty decimal(18, 3) default null,
date date default null,
reference varchar(200) default null,
adjust_type int(11) default null,
primary key (warehouse_id, product_id)
)
$$
delimiter $$
alter table product_warehouse
add index product_warehouse_id(product_id);
$$
delimiter $$
create table lk_template (
id int(11) not null,
purchase_email text character set utf8 collate utf8_unicode_ci default null,
purchase_sms text character set utf8 collate utf8_unicode_ci default null,
sale_email text character set utf8 collate utf8_unicode_ci default null,
sale_sms text character set utf8 collate utf8_unicode_ci default null,
return_purchase_email text character set utf8 collate utf8_unicode_ci default null,
return_purchase_sms text character set utf8 collate utf8_unicode_ci default null,
return_sale_email text character set utf8 collate utf8_unicode_ci default null,
return_sale_sms text character set utf8 collate utf8_unicode_ci default null,
quatation_email text character set utf8 collate utf8_unicode_ci default null,
quatation_sms text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
delimiter $$
create table lk_product_variant (
id int(11) not null auto_increment,
name varchar(200) default null,
primary key (id)
)
$$
delimiter $$
create table lk_messages (
id varchar(2) not null default '',
name varchar(93) default null,
langauge varchar(8) not null default '',
primary key (langauge, id)
)
$$
delimiter $$
create table lk_language (
id int(11) not null auto_increment,
name varchar(200) default null,
primary key (id)
)
$$
delimiter $$
create table lk_invoice_type (
id int(11) not null,
invoice varchar(200) default null,
primary key (id)
)
$$
delimiter $$
create table lk_invoice_type_lang (
id int(11) not null,
invoice varchar(200) default null,
language varchar(200) default null
)
$$
delimiter $$
create table lk_data_group (
id int(11) not null auto_increment,
name varchar(200) not null,
primary key (id)
)
$$
delimiter $$
create table lk_category (
id int(11) not null auto_increment,
code varchar(200) default null,
name varchar(200) default null,
master_cat int(11) default null,
level int(11) default null,
primary key (id)
)
$$
delimiter $$
alter table lk_category
add constraint con_lk_master_cat foreign key (master_cat)
references lk_category(id) on delete set null;
$$
delimiter $$
create table lk_barcode_symbology (
id int(11) not null auto_increment,
symbology varchar(20) default null,
primary key (id)
)
$$
delimiter $$
create table lk_adjust_type (
id int(11) not null,
name varchar(200) default null,
primary key (id)
)
$$
delimiter $$
create table key_license (
id int(11) not null auto_increment,
key_text varchar(500) not null,
primary key (id)
)
$$
delimiter $$
create table `dual` (
x int(11) default null
)
$$
delimiter $$
create table bar_code_generator (
inital_val varchar(6) default null
)
$$
delimiter $$
create table backup (
id int(11) not null auto_increment,
backup_date datetime default null,
file text character set utf8 collate utf8_unicode_ci default null,
primary key (id)
)
$$
delimiter $$
CREATE TABLE composite_product (
product_id INT(11) DEFAULT NULL,
product_id_det INT(11) DEFAULT NULL,
Qty DECIMAL(18, 3) NOT NULL,
id INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)
$$
delimiter $$
ALTER TABLE composite_product
ADD CONSTRAINT FK_composite_product_product_id FOREIGN KEY (product_id)
REFERENCES product(id) ON DELETE NO ACTION
$$
delimiter $$
ALTER TABLE composite_product
ADD CONSTRAINT FK_composite_product_product_id_det FOREIGN KEY (product_id_det)
REFERENCES product(id) ON DELETE NO ACTION
$$
delimiter $$
ALTER TABLE productavgcost ADD INDEX idx_productid_avg_cost (productid);
$$
delimiter $$
ALTER TABLE productavgcost ADD INDEX idx_invoice_type_avg_cost (invoice_type);
$$
delimiter $$
ALTER TABLE productavgcost ADD INDEX idx_invoice_id_avg_cost (invoice_id);
$$
delimiter $$
CREATE TABLE product_manufacturing (
id INT(11) NOT NULL AUTO_INCREMENT,
product_id INT(11) DEFAULT NULL,
warehouse_id INT(11) DEFAULT NULL,
qty DECIMAL(18, 3) DEFAULT NULL,
date DATETIME DEFAULT NULL,
PRIMARY KEY (id)
)
$$
delimiter $$
ALTER TABLE product_manufacturing
ADD CONSTRAINT FK_product_manufacturing_product_id FOREIGN KEY (product_id)
REFERENCES product(id) ON DELETE NO ACTION
$$
delimiter $$
ALTER TABLE product_manufacturing
ADD CONSTRAINT FK_product_manufacturing_warehouse_id FOREIGN KEY (warehouse_id)
REFERENCES lk_warehouse(id) ON DELETE NO ACTION
$$
delimiter $$
CREATE TABLE sale_service_det (
id INT(11) NOT NULL AUTO_INCREMENT,
sale_id INT(11) DEFAULT NULL,
service_id INT(11) DEFAULT NULL,
price DECIMAL(18, 3) DEFAULT NULL,
PRIMARY KEY (id)
)
$$
delimiter $$
ALTER TABLE sale_service_det
ADD CONSTRAINT FK_sale_service_det_sale_id FOREIGN KEY (sale_id)
REFERENCES sale(id) ON DELETE NO ACTION
$$
delimiter $$
ALTER TABLE sale_service_det
ADD CONSTRAINT FK_sale_service_det_service_id FOREIGN KEY (service_id)
REFERENCES lk_services(id) ON DELETE NO ACTION
$$
delimiter $$
CREATE FUNCTION calcstock_without_man(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare number_of_items decimal(18,3);
declare v_total decimal(18,3);
declare v_item_stock decimal(18,3);
declare v_Qty decimal(18,3);
declare is_composite1 int;
declare is_composite2 int;
declare v_product_id_det int;
select count(1) into is_composite1 from product where id=p_product_id and (is_composite is null or is_composite=0);
if is_composite1>0 then
select calcstock_det_without_man(p_product_id ,p_warehouse_id) into v_total;
end if;
select count(1) into is_composite2 from product where id=p_product_id and ( is_composite=1);
IF is_composite2>0 THEN
select calcstock_det_composite(p_product_id ,p_warehouse_id) into v_total;
end if;
return v_total;
end
$$
delimiter $$
CREATE FUNCTION check_composite(p_product_id int,p_warehouse_id int,p_qty int) RETURNS decimal(18,3)
begin
declare number_of_items decimal(18,3);
declare v_total decimal(18,3);
declare v_item_stock decimal(18,3);
declare v_Qty decimal(18,3);
declare is_composite int;
declare v_product_id_det int;
declare d int default 0;
declare get_cur cursor for
select product_id_det,Qty*p_qty as Qty,IFNULL(calcstock(product_id_det,p_warehouse_id),0) as item_stock
from composite_product where product_id=p_product_id ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = 1;
set v_total:=1;
open get_cur;
lbl: loop
fetch get_cur into v_product_id_det,v_Qty,v_item_stock;
if v_Qty>v_item_stock then
set v_total:=0;
end if;
if d = 1 then
leave lbl;
end if;
end loop;
close get_cur;
return v_total;
end
$$
delimiter $$
CREATE FUNCTION calcstock_invoice_product(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare number_of_items decimal(18,3);
declare v_total decimal(18,3);
declare v_item_stock decimal(18,3);
declare v_Qty decimal(18,3);
declare is_composite1 int;
declare is_composite2 int;
declare v_product_id_det int;
select count(1) into is_composite1 from product where id=p_product_id and (is_composite is null or is_composite=0);
if is_composite1>0 then
select calcstock_det(p_product_id ,p_warehouse_id) into v_total;
end if;
select count(1) into is_composite2 from product where id=p_product_id and ( is_composite=1);
IF is_composite2>0 THEN
select calcstock_det_composite(p_product_id ,p_warehouse_id) into v_total;
end if;
return v_total;
end
$$
delimiter $$
create function fn_row_number(reset int)
returns decimal(18, 3)
begin
if reset=1 then
set @var := 0;
else
set @var := ifnull(@var,0) + 1;
end if;
return @var;
end
$$
delimiter $$
create function getpath(cat_id int)
returns text
begin
declare res text;
call getpath(cat_id, res);
return res;
end
$$
--
-- create procedure getpath
--
delimiter $$
create procedure getpath(in cat_id int, out path text)
begin
declare catname varchar(20);
declare temppath text;
declare tempparent int;
declare level int;
declare maxlevel int;
set max_sp_recursion_depth = 255;
select
name,
master_cat,
level
from lk_category c
where id = cat_id into catname, tempparent, level;
if tempparent is null
then
set path = concat(catname);
else
call getpath(tempparent, temppath);
set path = concat(temppath, '/', catname );
end if;
end
$$
delimiter //
create procedure calcproductavgcost (in p_sale_id int(11),in p_user_add varchar(200))
begin
declare v_sale_id int;
declare v_payment_status_id int(11);
declare ref_id varchar(200);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select sale_id, pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from sale_det pd,product p where pd.product_id=p.id) d
where qty>0 and sale_id=p_sale_id ;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
start transaction;
open get_cur;
lbl: loop
fetch get_cur into v_sale_id,v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type)
values(v_product_id,v_unitcost,p_user_add, NOW(),-v_qty,v_sale_id,2) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
commit;
end;//
delimiter ;
delimiter //
create procedure sendtosale (in p_quotation_id int(11),in p_user_add varchar(200))
begin
declare v_sale_id int;
declare v_payment_status_id int(11);
declare ref_id varchar(200);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select sale_id, pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from sale_det pd,product p where pd.product_id=p.id) d
where qty>0 and sale_id=v_sale_id ;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
start transaction;
select payment_status_id into v_payment_status_id from quotation
where id=p_quotation_id;
if (v_payment_status_id=1 and p_quotation_id is not null ) then
delete from sale where quotation_id=p_quotation_id;
insert into sale(date,customer_id,warehouse_id,ordertax,discount
,shipping,sale_status_id,note,reference,paid,due,grandtotal,payment_status_id,totalordertax
,totaldiscount,totalshipping,user_add,user_addt,payment_method,quotation_id)
select date,customer_id,warehouse_id,ordertax,discount
,shipping,sale_status_id,note,reference,paid,due,grandtotal,payment_status_id,totalordertax
,totaldiscount,totalshipping,p_user_add,curdate(),'' payment_method,id from quotation
where id=p_quotation_id;
insert into sale_det(sale_id,product_id,taxtype,ordertax,unitcost,qty,discounttype_id
,discount,subtotal,taxamount,discountamount,grandtotalbeforetaxanddiscount,warehouse_id)
select
(select id from sale where quotation_id=p_quotation_id)
,product_id,taxtype,ordertax,unitcost,qty,discounttype_id
,discount,subtotal,taxamount,discountamount,grandtotalbeforetaxanddiscount,warehouse_id
from quotation_det where quotation_id= p_quotation_id;
insert into sale_payment(sale_id,date,receivedamount,payment_typeid,payamount
,paydocument,note)
select (select id from sale where quotation_id=p_quotation_id),date,receivedamount,payment_typeid,payamount,paydocument
,note
from
quotation_payment where quotation_id= p_quotation_id;
select id into v_sale_id from sale where quotation_id=p_quotation_id;
delete from productavgcost where invoice_id=v_sale_id and invoice_type=2;
open get_cur;
lbl: loop
fetch get_cur into v_sale_id,v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type)
values(v_product_id,v_unitcost,p_user_add, NOW(),-v_qty,v_sale_id,2) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
end if;
commit;
end;//
delimiter ;
--
-- create function calcstocksale
--
delimiter $$
CREATE FUNCTION calcstocksale(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare v_sale_s decimal;
declare v_total decimal;
select
sum(ifnull(pd.qty, 0)) into v_sale_s
from sale p,
sale_det pd
where p.id = pd.sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
set v_total = (ifnull(v_sale_s,0));
return v_total;
end
$$
delimiter $$
CREATE FUNCTION calcstocksale_product(p_product_id int) RETURNS decimal(18,3)
begin
declare v_sale_s decimal;
declare v_total decimal;
select
sum(ifnull(pd.qty, 0)) into v_sale_s
from sale p,
sale_det pd
where p.id = pd.sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.payment_status_id!=2
;
set v_total = (ifnull(v_sale_s,0));
return v_total;
end
$$
--
-- create function calcstockpurchase
--
delimiter $$
CREATE FUNCTION calcstockpurchase(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare v_purchase_s decimal;
declare v_r_purchase_s decimal;
declare v_total decimal;
select
sum(ifnull(pd.qty, 0)) into v_purchase_s
from purchase p,
purchase_det pd
where p.id = pd.purchase_id
and p.purchase_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
set v_total = (ifnull(v_purchase_s,0));
return v_total;
end
$$
--
-- create function calcstockproduct
--
delimiter $$
CREATE FUNCTION calcstockproduct(p_product_id int) RETURNS decimal(18,3)
begin
declare v_total decimal(18,3);
select ifnull(sum(calcstock_invoice(product_id,warehouse_id)),0)
into v_total
from product_warehouse
where product_id = p_product_id;
return v_total;
end
$$
--
-- create function calcstock_det_composite
--
delimiter $$
CREATE FUNCTION calcstock_det_composite(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare v_manufacturing decimal(18,3);
declare v_r_purchase_s decimal(18,3);
declare v_sale_s decimal(18,3);
declare v_r_sale_s decimal(18,3);
declare v_f_transfer decimal(18,3);
declare v_t_transfer decimal(18,3);
declare v_sell_in_minus varchar(50);
declare v_total decimal(18,3);
declare v_adqty decimal(18,3);
declare v_adjust_type int;
declare is_composite int;
select
sum(ifnull(qty, 0)) into v_manufacturing
from product_manufacturing
where
product_id = p_product_id
and warehouse_id = p_warehouse_id
;
select
sum(ifnull(pd.qty, 0)) into v_sale_s
from sale p,
sale_det pd
where p.id = pd.sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_r_sale_s
from return_sale p,
return_sale_det pd
where p.id = pd.return_sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_f_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.from_wharehouse_id = p_warehouse_id
;
select
sum(ifnull(pd.qty, 0)) into v_t_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.to_warehouse_id = p_warehouse_id
;
set v_total = v_manufacturing-(ifnull(v_sale_s,0)-ifnull(v_r_sale_s,0));
if v_f_transfer>0 then
set v_total=v_total-v_f_transfer;
end if;
if v_t_transfer>0 then
set v_total=v_total+v_t_transfer;
end if;
select
ifnull(adqty, 0),
adjust_type into v_adqty, v_adjust_type
from product_warehouse
where warehouse_id = p_warehouse_id
and product_id = p_product_id;
if v_adjust_type=1 then
set v_total:=v_total+(v_adqty);
end if;
if v_adjust_type=2 then
set v_total:=v_total-(v_adqty);
end if;
if v_sell_in_minus='yes' then
set v_total=abs(v_total);
end if;
return v_total;
end
$$
--
-- create function calcstock_det
--
delimiter $$
CREATE FUNCTION calcstock_det(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare v_purchase_s decimal(18,3);
declare v_r_purchase_s decimal(18,3);
declare v_sale_s decimal(18,3);
declare v_r_sale_s decimal(18,3);
declare v_f_transfer decimal(18,3);
declare v_t_transfer decimal(18,3);
declare v_sell_in_minus varchar(50);
declare v_total decimal(18,3);
declare v_adqty decimal(18,3);
declare v_adjust_type int;
declare is_composite int;
declare v_man_q decimal(18,3);
select
sell_in_minus into v_sell_in_minus
from system_settings;
select
sum(ifnull(pd.qty, 0)) into v_purchase_s
from purchase p,
purchase_det pd
where p.id = pd.purchase_id
and p.purchase_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_r_purchase_s
from return_purchase p,
return_purchase_det pd
where p.id = pd.return_purchase_id
and p.purchase_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_sale_s
from sale p,
sale_det pd
where p.id = pd.sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_r_sale_s
from return_sale p,
return_sale_det pd
where p.id = pd.return_sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty*p.Qty, 0)) into v_man_q
from composite_product p,
product_manufacturing pd
where p.product_id = pd.product_id
and p.product_id_det = p_product_id
and pd.warehouse_id = p_warehouse_id
;
select
sum(ifnull(pd.qty, 0)) into v_f_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.from_wharehouse_id = p_warehouse_id
;
select
sum(ifnull(pd.qty, 0)) into v_t_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.to_warehouse_id = p_warehouse_id
;
set v_total = (ifnull(v_purchase_s,0)-ifnull(v_r_purchase_s,0))-(ifnull(v_sale_s,0)-ifnull(v_r_sale_s,0)) -ifnull(v_man_q,0);
if v_f_transfer>0 then
set v_total=v_total-v_f_transfer;
end if;
if v_t_transfer>0 then
set v_total=v_total+v_t_transfer;
end if;
select
ifnull(adqty, 0),
adjust_type into v_adqty, v_adjust_type
from product_warehouse
where warehouse_id = p_warehouse_id
and product_id = p_product_id;
if v_adjust_type=1 then
set v_total:=v_total+(v_adqty);
end if;
if v_adjust_type=2 then
set v_total:=v_total-(v_adqty);
end if;
if v_sell_in_minus='yes' then
set v_total=abs(v_total);
end if;
return v_total;
end
$$
--
-- create function calcstock
--
delimiter $$
CREATE FUNCTION calcstock_det_without_man(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare v_purchase_s decimal(18,3);
declare v_r_purchase_s decimal(18,3);
declare v_sale_s decimal(18,3);
declare v_r_sale_s decimal(18,3);
declare v_f_transfer decimal(18,3);
declare v_t_transfer decimal(18,3);
declare v_sell_in_minus varchar(50);
declare v_total decimal(18,3);
declare v_adqty decimal(18,3);
declare v_adjust_type int;
declare is_composite int;
declare v_sale_comp_qty decimal(18,3);
declare v_r_sale_comp_qty decimal(18,3);
select
sell_in_minus into v_sell_in_minus
from system_settings;
select
sum(ifnull(pd.qty, 0)) into v_purchase_s
from purchase p,
purchase_det pd
where p.id = pd.purchase_id
and p.purchase_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_r_purchase_s
from return_purchase p,
return_purchase_det pd
where p.id = pd.return_purchase_id
and p.purchase_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_sale_s
from sale p,
sale_det pd
where p.id = pd.sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty, 0)) into v_r_sale_s
from return_sale p,
return_sale_det pd
where p.id = pd.return_sale_id
and p.sale_status_id = 1
and pd.product_id = p_product_id
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
;
select
sum(ifnull(pd.qty*cp.Qty, 0)) into v_sale_comp_qty
from sale p,
sale_det pd,
composite_product cp
where p.id = pd.sale_id
and p.sale_status_id = 1
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
and cp.product_id=pd.product_id
and cp.product_id_det=p_product_id
;
select
sum(ifnull(pd.qty*cp.Qty, 0)) into v_r_sale_comp_qty
from return_sale p,
return_sale_det pd,
composite_product cp
where p.id = pd.return_sale_id
and p.sale_status_id = 1
and p.warehouse_id = p_warehouse_id
and p.payment_status_id!=2
and cp.product_id=pd.product_id
and cp.product_id_det=p_product_id
;
select
sum(ifnull(pd.qty, 0)) into v_f_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.from_wharehouse_id = p_warehouse_id
;
select
sum(ifnull(pd.qty, 0)) into v_t_transfer
from transfer p,
transfer_det pd
where p.id = pd.transfer_id
and p.transfer_status_id = 1
and pd.product_id = p_product_id
and p.to_warehouse_id = p_warehouse_id
;
set v_total = (ifnull(v_purchase_s,0)-ifnull(v_r_purchase_s,0))-
(ifnull(v_sale_s,0)-ifnull(v_r_sale_s,0))-(ifnull(v_sale_comp_qty,0)-ifnull(v_r_sale_comp_qty,0));
if v_f_transfer>0 then
set v_total=v_total-v_f_transfer;
end if;
if v_t_transfer>0 then
set v_total=v_total+v_t_transfer;
end if;
select
ifnull(adqty, 0),
adjust_type into v_adqty, v_adjust_type
from product_warehouse
where warehouse_id = p_warehouse_id
and product_id = p_product_id;
if v_adjust_type=1 then
set v_total:=v_total+(v_adqty);
end if;
if v_adjust_type=2 then
set v_total:=v_total-(v_adqty);
end if;
if v_sell_in_minus='yes' then
set v_total=abs(v_total);
end if;
return v_total;
end
$$
--
-- create function calcstock
--
delimiter $$
CREATE FUNCTION calcstock(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare number_of_items decimal(18,3);
declare v_total decimal(18,3);
declare v_item_stock decimal(18,3);
declare v_Qty decimal(18,3);
declare is_composite1 int;
declare is_composite2 int;
declare v_product_id_det int;
select count(1) into is_composite1 from product where id=p_product_id and (is_composite is null or is_composite=0);
if is_composite1>0 then
select calcstock_det(p_product_id ,p_warehouse_id) into v_total;
end if;
select count(1) into is_composite2 from product where id=p_product_id and ( is_composite=1);
IF is_composite2>0 THEN
select calcstock_det_composite(p_product_id ,p_warehouse_id) into v_total;
end if;
return v_total;
end
$$
--
-- create function calcstock_invoice
--
delimiter $$
CREATE FUNCTION calcstock_invoice(p_product_id int,p_warehouse_id int) RETURNS decimal(18,3)
begin
declare number_of_items decimal(18,3);
declare v_total decimal(18,3);
declare v_item_stock decimal(18,3);
declare v_Qty decimal(18,3);
declare is_composite1 int;
declare is_composite2 int;
declare v_product_id_det int;
select count(1) into is_composite1 from product where id=p_product_id and (is_composite is null or is_composite=0);
if is_composite1>0 then
select calcstock_det(p_product_id ,p_warehouse_id) into v_total;
end if;
select count(1) into is_composite2 from product where id=p_product_id and ( is_composite=1);
IF is_composite2>0 THEN
select calcstock_det_composite(p_product_id ,p_warehouse_id) into v_total;
end if;
return v_total;
end
$$
--
-- create trigger set_product_warehouse_product_manufacturing_delete
--
delimiter $$
CREATE TRIGGER set_product_warehouse_product_manufacturing_delete
AFTER DELETE
ON product_manufacturing
FOR EACH ROW
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_product_manufacturing_insert
--
delimiter $$
CREATE TRIGGER set_product_warehouse_product_manufacturing_insert
AFTER INSERT
ON product_manufacturing
FOR EACH ROW
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_product_manufacturing_update
--
delimiter $$
CREATE TRIGGER set_product_warehouse_product_manufacturing_update
AFTER UPDATE
ON product_manufacturing
FOR EACH ROW
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_transfer_det_update
--
delimiter $$
create trigger set_product_warehouse_transfer_det_update
after update
on transfer_det
for each row
begin
declare v_qty_f decimal(18,3);
declare v_qty_t decimal(18,3);
declare v_f1 int;
declare v_f2 int;
select
count(1) into v_f1
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.from_wharehouse_id;
set v_qty_f:=( select
calcstock_invoice(new.product_id, new.from_wharehouse_id));
if v_f1=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.from_wharehouse_id, v_qty_f);
else
update product_warehouse
set qty = v_qty_f
where product_id = new.product_id
and warehouse_id = new.from_wharehouse_id;
end if;
select
count(1) into v_f2
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.to_warehouse_id;
set v_qty_t:=( select
calcstock_invoice(new.product_id, new.to_warehouse_id));
if v_f2=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.to_warehouse_id, v_qty_t);
else
update product_warehouse
set qty = v_qty_t
where product_id = new.product_id
and warehouse_id = new.to_warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_transfer_det_insert
--
delimiter $$
create trigger set_product_warehouse_transfer_det_insert
after insert
on transfer_det
for each row
begin
declare v_qty_f decimal(18,3);
declare v_qty_t decimal(18,3);
declare v_f1 int;
declare v_f2 int;
select
count(1) into v_f1
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.from_wharehouse_id;
set v_qty_f:=( select
calcstock_invoice(new.product_id, new.from_wharehouse_id));
if v_f1=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.from_wharehouse_id, v_qty_f);
else
update product_warehouse
set qty = v_qty_f
where product_id = new.product_id
and warehouse_id = new.from_wharehouse_id;
end if;
select
count(1) into v_f2
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.to_warehouse_id;
set v_qty_t:=( select
calcstock_invoice(new.product_id, new.to_warehouse_id));
if v_f2=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.to_warehouse_id, v_qty_t);
else
update product_warehouse
set qty = v_qty_t
where product_id = new.product_id
and warehouse_id = new.to_warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_transfer_det_delete
--
delimiter $$
create trigger set_product_warehouse_transfer_det_delete
before delete
on transfer_det
for each row
begin
declare v_qty_f decimal(18,3);
declare v_qty_t decimal(18,3);
declare v_f1 int;
declare v_f2 int;
select
count(1) into v_f1
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.from_wharehouse_id;
set v_qty_f:=( select
calcstock_invoice(old.product_id, old.from_wharehouse_id));
if v_f1=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.from_wharehouse_id, v_qty_f);
else
update product_warehouse
set qty = v_qty_f
where product_id = old.product_id
and warehouse_id = old.from_wharehouse_id;
end if;
select
count(1) into v_f2
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.to_warehouse_id;
set v_qty_t:=( select
calcstock_invoice(old.product_id, old.to_warehouse_id));
if v_f2=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.to_warehouse_id, v_qty_t);
else
update product_warehouse
set qty = v_qty_t
where product_id = old.product_id
and warehouse_id = old.to_warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_sale_det_update
--
delimiter $$
create trigger set_product_warehouse_sale_det_update
after update
on sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_sale_det_insert
--
delimiter $$
create trigger set_product_warehouse_sale_det_insert
after insert
on sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_sale_det_delete
--
delimiter $$
create trigger set_product_warehouse_sale_det_delete
after delete
on sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_sale_det_update
--
delimiter $$
create trigger set_product_warehouse_return_sale_det_update
after update
on return_sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_sale_det_insert
--
delimiter $$
create trigger set_product_warehouse_return_sale_det_insert
after insert
on return_sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_sale_det_delete
--
delimiter $$
create trigger set_product_warehouse_return_sale_det_delete
after delete
on return_sale_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_purchase_det_update
--
delimiter $$
create trigger set_product_warehouse_return_purchase_det_update
after update
on return_purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_purchase_det_insert
--
delimiter $$
create trigger set_product_warehouse_return_purchase_det_insert
after insert
on return_purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_return_purchase_det_delete
--
delimiter $$
create trigger set_product_warehouse_return_purchase_det_delete
after delete
on return_purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_quotation_det_update
--
delimiter $$
create trigger set_product_warehouse_quotation_det_update
after update
on quotation_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_quotation_det_insert
--
delimiter $$
create trigger set_product_warehouse_quotation_det_insert
after insert
on quotation_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_quotation_det_delete
--
delimiter $$
create trigger set_product_warehouse_quotation_det_delete
after delete
on quotation_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_purchase_det_update
--
delimiter $$
create trigger set_product_warehouse_purchase_det_update
after update
on purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_purchase_det_insert
--
delimiter $$
create trigger set_product_warehouse_purchase_det_insert
after insert
on purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
set v_qty:=( select
calcstock_invoice(new.product_id, new.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (new.product_id, new.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = new.product_id
and warehouse_id = new.warehouse_id;
end if;
end
$$
--
-- create trigger set_product_warehouse_purchase_det_delete
--
delimiter $$
create trigger set_product_warehouse_purchase_det_delete
after delete
on purchase_det
for each row
begin
declare v_qty decimal(18,3);
declare v_f int;
select
count(1) into v_f
from product_warehouse
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
set v_qty:=( select
calcstock_invoice(old.product_id, old.warehouse_id));
if v_f=0 then
insert into product_warehouse (product_id, warehouse_id, qty)
values (old.product_id, old.warehouse_id, v_qty);
else
update product_warehouse
set qty = v_qty
where product_id = old.product_id
and warehouse_id = old.warehouse_id;
end if;
end
$$
--
-- create view vi_product_total
--
delimiter $$
CREATE VIEW vi_product_total
AS
SELECT
d.product_id AS product_id,
d.warehouse_id AS warehouse_id,
d.date AS date,
d.invoicetype AS invoicetype,
SUM(d.qty) AS qty,
SUM(d.unitcost) AS unitcost,
SUM(d.taxamount) AS taxamount,
SUM(d.discountamount) AS discountamount,
SUM(d.subtotal) AS subtotal
FROM (SELECT
pd.product_id AS product_id,
p.warehouse_id AS warehouse_id,
p.date AS date,
IFNULL(SUM(pd.qty), 0) AS qty,
IFNULL(SUM(pd.unitcost), 0) AS unitcost,
IFNULL(SUM(pd.taxamount), 0) AS taxamount,
IFNULL(SUM(pd.discountamount), 0) AS discountamount,
IFNULL(SUM(pd.subtotal), 0) AS subtotal,
1 AS invoicetype
FROM (purchase p
JOIN purchase_det pd)
WHERE p.id = pd.purchase_id
AND p.purchase_status_id = 1
AND p.payment_status_id <> 2
GROUP BY pd.product_id,
p.warehouse_id,
p.date UNION SELECT
pd.product_id AS product_id,
p.warehouse_id AS warehouse_id,
p.date AS date,
IFNULL(SUM(pd.qty), 0) AS qty,
IFNULL(SUM(pd.unitcost), 0) AS unitprice,
IFNULL(SUM(pd.taxamount), 0) AS taxamount,
IFNULL(SUM(pd.discountamount), 0) AS discountamount,
IFNULL(SUM(pd.subtotal), 0) AS subtotal,
2 AS invoicetype
FROM (sale p
JOIN sale_det pd)
WHERE p.id = pd.sale_id
AND p.sale_status_id = 1
AND p.payment_status_id <> 2
GROUP BY pd.product_id,
p.warehouse_id,
p.date UNION SELECT
pd.product_id AS product_id,
p.warehouse_id AS warehouse_id,
p.date AS date,
IFNULL(SUM(pd.qty), 0) AS qty,
IFNULL(SUM(pd.unitcost), 0) AS unitcost,
IFNULL(SUM(pd.taxamount), 0) AS taxamount,
IFNULL(SUM(pd.discountamount), 0) AS discountamount,
IFNULL(SUM(pd.subtotal), 0) AS subtotal,
3 AS invoicetype
FROM (return_purchase p
JOIN return_purchase_det pd)
WHERE p.id = pd.return_purchase_id
AND p.purchase_status_id = 1
AND p.payment_status_id <> 2
GROUP BY pd.product_id,
p.warehouse_id,
p.date UNION SELECT
pd.product_id AS product_id,
p.warehouse_id AS warehouse_id,
p.date AS date,
IFNULL(SUM(pd.qty), 0) AS qty,
IFNULL(SUM(pd.unitcost), 0) AS unitcost,
IFNULL(SUM(pd.taxamount), 0) AS taxamount,
IFNULL(SUM(pd.discountamount), 0) AS discountamount,
IFNULL(SUM(pd.subtotal), 0) AS subtotal,
4 AS invoicetype
FROM (return_sale p
JOIN return_sale_det pd)
WHERE p.id = pd.return_sale_id
AND p.sale_status_id = 1
AND p.payment_status_id <> 2
GROUP BY pd.product_id,
p.warehouse_id,
p.date UNION SELECT
pd.product_id AS product_id,
p.warehouse_id AS warehouse_id,
p.date AS date,
IFNULL(SUM(pd.qty), 0) AS qty,
IFNULL(SUM(pd.unitcost), 0) AS unitcost,
IFNULL(SUM(pd.taxamount), 0) AS taxamount,
IFNULL(SUM(pd.discountamount), 0) AS discountamount,
IFNULL(SUM(pd.subtotal), 0) AS subtotal,
6 AS invoicetype
FROM (quotation p
JOIN quotation_det pd)
WHERE p.id = pd.quotation_id
AND p.sale_status_id = 1
AND p.payment_status_id <> 2
GROUP BY pd.product_id,
p.warehouse_id,
p.date) d
GROUP BY d.product_id,
d.warehouse_id,
d.date,
d.invoicetype;
$$
--
-- create view vi_total_paid_per_week
--
delimiter $$
create
view vi_total_paid_per_week
as
select
d.total_paid as total_paid,
d.invoicetype as invoicetype,
date_format(d.date, '%d-%m-%y') as date
from (select
ifnull(sum(ifnull( purchase_payment.receivedamount, 0)), 0) as total_paid,
1 as invoicetype,
purchase_payment.date as date
from purchase_payment
where week( purchase_payment.date) = week(curdate())
group by purchase_payment.date union select
ifnull(sum(ifnull( sale_payment.receivedamount, 0)), 0) as total_paid,
2 as invoicetype,
sale_payment.date as date
from sale_payment
where week( sale_payment.date) = week(curdate())
group by sale_payment.date union select
ifnull(sum(ifnull( return_purchase_payment.receivedamount, 0)), 0) as total_paid,
3 as invoicetype,
return_purchase_payment.date as date
from return_purchase_payment
where week( return_purchase_payment.date) = week(curdate())
group by return_purchase_payment.date union select
ifnull(sum(ifnull( return_sale_payment.receivedamount, 0)), 0) as total_paid,
4 as invoicetype,
return_sale_payment.date as date
from return_sale_payment
where week( return_sale_payment.date) = week(curdate())
group by return_sale_payment.date union select
ifnull(sum(ifnull( expenses.amount, 0)), 0) as total_paid,
5 as invoicetype,
expenses.date as date
from expenses
where week( expenses.date) = week(curdate())
group by expenses.date) d
order by date_format(d.date, '%d-%m-%y');
$$
--
-- create view vi_supplier
--
delimiter $$
create
view vi_supplier
as
select
c.name as name,
c.phone as phone,
c.email as email,
c.address as address,
ifnull((select
sum(purchase.grandtotal)
from purchase
where purchase.supplier_id = c.id), 0) as grandtotal,
ifnull((select
sum(purchase.paid)
from purchase
where purchase.supplier_id = c.id), 0) as paid,
ifnull((select
sum(purchase.due)
from purchase
where purchase.supplier_id = c.id), 0) as due,
(select
count(purchase.id)
from purchase
where purchase.supplier_id = c.id) as totalpurchase,
1 as invoice_type
from supplier c union select
c.name as name,
c.phone as phone,
c.email as email,
c.address as address,
ifnull((select
sum(return_purchase.grandtotal)
from return_purchase
where return_purchase.supplier_id = c.id), 0) as grandtotal,
ifnull((select
sum(return_purchase.paid)
from return_purchase
where return_purchase.supplier_id = c.id), 0) as paid,
ifnull((select
sum(purchase.due)
from purchase
where purchase.supplier_id = c.id), 0) as due,
(select
count(return_purchase.id)
from return_purchase
where return_purchase.supplier_id = c.id) as totalpurchase,
3 as invoice_type
from supplier c;
$$
--
-- create view vi_sale_return
--
delimiter $$
create
view vi_sale_return
as
select
return_sale.date as date,
return_sale.reference as reference,
return_sale.customer_id as customer_id,
return_sale.warehouse_id as warehouse_id,
return_sale.sale_status_id as sale_status_id,
return_sale.payment_status_id as payment_status_id,
return_sale.paid as paid,
return_sale.due as due,
return_sale.grandtotal as grandtotal
from return_sale
order by return_sale.date desc;
$$
--
-- create view vi_sale_payment
--
delimiter $$
create
view vi_sale_payment
as
select
p.date as date,
p.reference as reference,
p.customer_id as customer_id,
p.payment_status_id as payment_status_id,
p.grandtotal as grandtotal,
p.paid as paid,
p.due as due,
py.receivedamount as receivedamount,
py.payment_typeid as payment_typeid
from (sale p
join sale_payment py)
where p.id = py.sale_id;
$$
--
-- create view vi_sale2
--
delimiter $$
create
view vi_sale2
as
select
sale.id as id,
sale.date as date,
sale.reference as reference,
sale.customer_id as customer_id,
sale.warehouse_id as warehouse_id,
sale.sale_status_id as sale_status_id,
sale.payment_status_id as payment_status_id,
sale.paid as paid,
sale.due as due,
sale.grandtotal as grandtotal
from sale
order by sale.id desc limit 5;
$$
--
-- create view vi_sale
--
delimiter $$
create
view vi_sale
as
select
sale.id as id,
sale.date as date,
sale.reference as reference,
sale.customer_id as customer_id,
sale.warehouse_id as warehouse_id,
sale.sale_status_id as sale_status_id,
sale.payment_status_id as payment_status_id,
sale.paid as paid,
sale.due as due,
sale.grandtotal as grandtotal
from sale
order by sale.date desc;
$$
--
-- create view vi_return_sale_payment
--
delimiter $$
create
view vi_return_sale_payment
as
select
p.date as date,
p.reference as reference,
p.customer_id as customer_id,
p.payment_status_id as payment_status_id,
p.grandtotal as grandtotal,
p.paid as paid,
p.due as due,
py.receivedamount as receivedamount,
py.payment_typeid as payment_typeid
from (return_sale p
join return_sale_payment py)
where p.id = py.sale_id;
$$
--
-- create view vi_quantityalerts
--
delimiter $$
create
view vi_quantityalerts
as
select
pw.warehouse_id as warehouse_id,
p.name as name,
p.codeproduct as codeproduct,
pw.qty as qty,
p.stockalert as stockalert
from (product_warehouse pw
join product p)
where pw.product_id = p.id
and pw.qty <= p.stockalert;
$$
--
-- create view vi_purchase_return_payment
--
delimiter $$
create
view vi_purchase_return_payment
as
select
p.date as date,
p.reference as reference,
p.supplier_id as supplier_id,
p.payment_status_id as payment_status_id,
p.grandtotal as grandtotal,
p.paid as paid,
p.due as due,
py.receivedamount as receivedamount,
py.payment_typeid as payment_typeid
from (return_purchase p
join return_purchase_payment py)
where p.id = py.return_purchase_id;
$$
--
-- create view vi_purchase_return
--
delimiter $$
create
view vi_purchase_return
as
select
return_purchase.date as date,
return_purchase.reference as reference,
return_purchase.supplier_id as supplier_id,
return_purchase.warehouse_id as warehouse_id,
return_purchase.purchase_status_id as purchase_status_id,
return_purchase.payment_status_id as payment_status_id,
return_purchase.paid as paid,
return_purchase.due as due,
return_purchase.grandtotal as grandtotal
from return_purchase
order by return_purchase.date desc;
$$
--
-- create view vi_purchase_payment
--
delimiter $$
create
view vi_purchase_payment
as
select
p.date as date,
p.reference as reference,
p.supplier_id as supplier_id,
p.payment_status_id as payment_status_id,
p.grandtotal as grandtotal,
p.paid as paid,
p.due as due,
py.receivedamount as receivedamount,
py.payment_typeid as payment_typeid
from (purchase p
join purchase_payment py)
where p.id = py.purchase_id;
$$
--
-- create view vi_purchase
--
delimiter $$
create
view vi_purchase
as
select
purchase.date as date,
purchase.reference as reference,
purchase.supplier_id as supplier_id,
purchase.warehouse_id as warehouse_id,
purchase.purchase_status_id as purchase_status_id,
purchase.payment_status_id as payment_status_id,
purchase.paid as paid,
purchase.due as due,
purchase.grandtotal as grandtotal
from purchase
order by purchase.date desc;
$$
--
-- create view vi_product_warehouse
--
delimiter $$
create
view vi_product_warehouse
as
select
p.id id,
p.name name,
p.codeproduct as codeproduct,
p.productprice as productprice,
p.image as image,
pw.qty as qty,
pw.warehouse_id as warehouse_id,
p.variant_id as variant_id,
p.category_id as category_id,
p.brand_id as brand_id,
p.stockalert as stockalert,
p.taxtype_id as taxtype_id,
p.productcost as productcost,
(select
lk_unit.shortname
from lk_unit
where lk_unit.id = p.saleunit) as shortnamesaleunit,
(select
lk_unit.shortname
from lk_unit
where lk_unit.id = p.purchaseunit) as shortnamepurchaseunit
from (product p
left join product_warehouse pw
on (p.id = pw.product_id))
where p.id = case when (select
system_settings.sell_in_minus
from system_settings) = 'No' then pw.product_id else p.id end
and (calcstock(p.id,pw.warehouse_id) > 0
or (select
system_settings.sell_in_minus
from system_settings) = 'Yes')
and (p.product_sell_status = 1
or p.product_sell_status = 3);
$$
--
-- create view vi_pos_sale_payment
--
delimiter $$
create
view vi_pos_sale_payment
as
select
sale_payment.id as id,
sale_payment.sale_id as sale_id,
sale_payment.date as date,
sale_payment.receivedamount as receivedamount,
sale_payment.payment_typeid as payment_typeid,
sale_payment.payamount as payamount,
sale_payment.paydocument as paydocument,
sale_payment.note as note
from sale_payment;
$$
--
-- create view vi_pos_sale_det
--
delimiter $$
create
view vi_pos_sale_det
as
select
sale_det.id as id,
sale_det.sale_id as sale_id,
sale_det.product_id as product_id,
sale_det.taxtype as taxtype,
sale_det.ordertax as ordertax,
sale_det.unitcost as unitcost,
sale_det.qty as qty,
sale_det.discounttype_id as discounttype_id,
sale_det.discount as discount,
sale_det.subtotal as subtotal,
sale_det.taxamount as taxamount,
sale_det.discountamount as discountamount,
sale_det.grandtotalbeforetaxanddiscount as grandtotalbeforetaxanddiscount,
sale_det.warehouse_id as warehouse_id
from sale_det;
$$
--
-- create view vi_pos_sale
--
delimiter $$
create
view vi_pos_sale
as
select
sale.id as id,
sale.date as date,
sale.customer_id as customer_id,
sale.warehouse_id as warehouse_id,
sale.ordertax as ordertax,
sale.discount as discount,
sale.shipping as shipping,
sale.sale_status_id as sale_status_id,
sale.note as note,
sale.reference as reference,
sale.pos_received_amount as received_amount,
sale.due as due,
sale.grandtotal as grandtotal,
sale.payment_status_id as payment_status_id,
sale.totalordertax as totalordertax,
sale.totaldiscount as totaldiscount,
sale.totalshipping as totalshipping,
sale.user_add as user_add,
sale.user_addt as user_addt,
sale.user_upd as user_upd,
sale.user_updt as user_updt,
sale.payment_method as payment_method
,return_amount
,paid
from sale;
$$
--
-- create view vi_lk_warehouse
--
delimiter $$
create
view vi_lk_warehouse
as
select
lk_warehouse.id as id,
lk_warehouse.name as name,
lk_warehouse.country_id as country_id,
lk_warehouse.email as email,
lk_warehouse.phone as phone,
lk_warehouse.city as city,
lk_warehouse.zip_code as zip_code,
lk_warehouse.visible as visible
from lk_warehouse;
$$
--
-- create view vi_customer
--
delimiter $$
create
view vi_customer
as
select
c.name as name,
c.phone as phone,
c.email as email,
c.address as address,
ifnull((select
sum(sale.grandtotal)
from sale
where sale.customer_id = c.id), 0) as grandtotal,
ifnull((select
sum(sale.paid)
from sale
where sale.customer_id = c.id), 0) as paid,
ifnull((select
sum(sale.due)
from sale
where sale.customer_id = c.id), 0) as due,
(select
count(sale.id)
from sale
where sale.customer_id = c.id) as totalsale,
2 as invoice_type
from customer c union select
c.name as name,
c.phone as phone,
c.email as email,
c.address as address,
ifnull((select
sum(return_sale.grandtotal)
from return_sale
where return_sale.customer_id = c.id), 0) as grandtotal,
ifnull((select
sum(return_sale.paid)
from return_sale
where return_sale.customer_id = c.id), 0) as paid,
ifnull((select
sum(return_sale.due)
from return_sale
where return_sale.customer_id = c.id), 0) as due,
(select
count(return_sale.id)
from return_sale
where return_sale.customer_id = c.id) as totalsale,
4 as invoice_type
from customer c union select
c.name as name,
c.phone as phone,
c.email as email,
c.address as address,
ifnull((select
sum(quotation.grandtotal)
from quotation
where quotation.customer_id = c.id), 0) as grandtotal,
ifnull((select
sum(quotation.paid)
from quotation
where quotation.customer_id = c.id), 0) as paid,
ifnull((select
sum(quotation.due)
from quotation
where quotation.customer_id = c.id), 0) as due,
(select
count(quotation.id)
from quotation
where quotation.customer_id = c.id) as totalsale,
6 as invoice_type
from customer c;
$$
--
-- create view profile
--
delimiter $$
create
view profile
as
select
stickyshop_users.id as id,
stickyshop_users.username as username,
stickyshop_users.password as password,
stickyshop_users.email as email,
stickyshop_users.fullname as fullname,
stickyshop_users.groupid as groupid,
stickyshop_users.active as active,
stickyshop_users.data_group_id as data_group_id,
stickyshop_users.ext_security_id as ext_security_id,
stickyshop_users.warehouse_id_ as warehouse_id_,
stickyshop_users.warehouse_id as warehouse_id,
stickyshop_users.tel as tel,
stickyshop_users.photo as photo
from stickyshop_users;
$$
--
-- create trigger set_warehouse_id_transfer_det
--
delimiter $$
create trigger set_warehouse_id_transfer_det
before insert
on transfer_det
for each row
begin
declare v_to_warehouse_id int(11);
declare v_from_warehouse_id int(11);
set v_to_warehouse_id:=( select
to_warehouse_id
from transfer
where id = new.transfer_id);
set v_from_warehouse_id:=( select
from_wharehouse_id
from transfer
where id = new.transfer_id);
set new.to_warehouse_id=v_to_warehouse_id;
set new.from_wharehouse_id=v_from_warehouse_id;
end;
$$
--
-- create trigger set_warehouse_id_sale_det
--
delimiter $$
create trigger set_warehouse_id_sale_det
before insert
on sale_det
for each row
begin
declare v_warehouse_id int(11);
set v_warehouse_id:=( select
warehouse_id
from sale
where id = new.sale_id);
set new.warehouse_id=v_warehouse_id;
end;
$$
--
-- create trigger set_warehouse_id_return_sale_det
--
delimiter $$
create trigger set_warehouse_id_return_sale_det
before insert
on return_sale_det
for each row
begin
declare v_warehouse_id int(11);
set v_warehouse_id:=( select
warehouse_id
from return_sale
where id = new.return_sale_id);
set new.warehouse_id=v_warehouse_id;
end;
$$
--
-- create trigger set_warehouse_id_return_purchase_det
--
delimiter $$
create trigger set_warehouse_id_return_purchase_det
before insert
on return_purchase_det
for each row
begin
declare v_warehouse_id int(11);
set v_warehouse_id:=( select
warehouse_id
from return_purchase
where id = new.return_purchase_id);
set new.warehouse_id=v_warehouse_id;
end;
$$
--
-- create trigger set_warehouse_id_quotation_det
--
delimiter $$
create trigger set_warehouse_id_quotation_det
before insert
on quotation_det
for each row
begin
declare v_warehouse_id int(11);
set v_warehouse_id:=( select
warehouse_id
from quotation
where id = new.quotation_id);
set new.warehouse_id=v_warehouse_id;
end;
$$
--
-- create trigger set_warehouse_id_purchase_det
--
delimiter $$
create trigger set_warehouse_id_purchase_det
before insert
on purchase_det
for each row
begin
declare v_warehouse_id int(11);
set v_warehouse_id:=( select
warehouse_id
from purchase
where id = new.purchase_id);
set new.warehouse_id=v_warehouse_id;
end;
$$
--
-- create trigger set_transfer_ref
--
delimiter $$
create trigger set_transfer_ref
before insert
on transfer
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("tra_", (select
count(id) + 1
from transfer), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_sale_ref
--
delimiter $$
create trigger set_sale_ref
before insert
on sale
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("sl_", (select
count(id) + 1
from sale), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_ret_sale_ref
--
delimiter $$
create trigger set_ret_sale_ref
before insert
on return_sale
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("sl_r_", (select
count(id) + 1
from return_sale), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_quatation_ref
--
delimiter $$
create trigger set_quatation_ref
before insert
on quotation
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("qt_", (select
count(id) + 1
from quotation), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_purchase_ref_ret
--
delimiter $$
create trigger set_purchase_ref_ret
before insert
on return_purchase
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("pr_r_", (select
count(id) + 1
from return_purchase), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_purchase_ref
--
delimiter $$
create trigger set_purchase_ref
before insert
on purchase
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("pr_", (select
count(id) + 1
from purchase), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger set_expenses_ref
--
delimiter $$
create trigger set_expenses_ref
before insert
on expenses
for each row
begin
declare ref_id varchar(200);
set ref_id:=( select
concat("exp_", (select
count(id) + 1
from expenses), '_', date_format(curdate(), "%d%m%y")));
set new.reference = ref_id;
end;
$$
--
-- create trigger setlevelbeforeinsert
--
delimiter $$
create trigger setlevelbeforeinsert
before insert
on lk_category
for each row
begin
declare plevel int;
if new.master_cat is not null then
select
level into plevel
from lk_category
where id = new.master_cat;
set new.level = plevel + 1;
else
set new.level = 0;
end if;
end;
$$
--
-- create trigger del_transfer_det
--
delimiter $$
create trigger del_transfer_det
before delete
on transfer
for each row
begin
delete
from transfer_det
where transfer_id = old.id;
end;
$$
--
-- create trigger del_sale_det
--
delimiter $$
create trigger del_sale_det
before delete
on sale
for each row
begin
declare v_sale_id int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select sale_id,pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from sale_det pd,product p where pd.product_id=p.id ) d
where qty>0 and sale_id=old.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
delete from productavgcost where invoice_id=old.id and invoice_type=2;
open get_cur;
lbl: loop
fetch get_cur into v_sale_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
delete
from sale_det
where sale_id = old.id;
end;
$$
--
-- create trigger set_return_sale_avg_cost
--
delimiter $$
create trigger set_return_sale_avg_cost
before update
on return_sale
for each row
begin
declare v_return_sale_id int(11);
declare v_f int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select return_sale_id,pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from return_sale_det pd,product p where pd.product_id=p.id ) d
where qty>0 and return_sale_id=new.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
select count(1) into v_f from productavgcost where invoice_id=new.id and invoice_type=4;
if (new.payment_status_id!=2 and v_f=0) then
open get_cur;
lbl: loop
fetch get_cur into v_return_sale_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type)
values(v_product_id,v_unitcost,new.user_upd, NOW(),v_qty,new.id,4) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
end if;
end;
$$
--
-- create trigger del_return_sale_det
--
delimiter $$
create trigger del_return_sale_det
before delete
on return_sale
for each row
begin
declare v_return_sale_id int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select return_sale_id,pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from return_sale_det pd,product p where pd.product_id=p.id ) d
where qty>0 and return_sale_id=old.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
delete from productavgcost where invoice_id=old.id and invoice_type=4;
open get_cur;
lbl: loop
fetch get_cur into v_return_sale_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
delete
from return_sale_det
where return_sale_id = old.id;
end;
$$
--
-- create trigger set_return_purchase_avg_cost
--
delimiter $$
create trigger set_return_purchase_avg_cost
before update
on return_purchase
for each row
begin
declare v_return_purchase_id int(11);
declare v_f int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select return_purchase_id,pd.product_id,pd.qty,pd.unitcost,0 as stock_product
from return_purchase_det pd,product p where pd.product_id=p.id ) d
where qty>0 and return_purchase_id=old.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
select count(1) into v_f from productavgcost where invoice_id=new.id and invoice_type=3;
if (new.payment_status_id!=2 and v_f=0) then
open get_cur;
lbl: loop
fetch get_cur into v_return_purchase_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type )
values(v_product_id,v_unitcost,new.user_upd, NOW(),-v_qty,new.id,3) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
end if;
end;
$$
--
-- create trigger del_return_purchase_det
--
delimiter $$
create trigger del_return_purchase_det
before delete
on return_purchase
for each row
begin
declare v_return_purchase_id int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select return_purchase_id,pd.product_id,pd.qty,pd.unitcost,0 as stock_product
from return_purchase_det pd,product p where pd.product_id=p.id ) d
where qty>0 and return_purchase_id=old.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
delete from productavgcost where invoice_id=old.id and invoice_type=3;
open get_cur;
lbl: loop
fetch get_cur into v_return_purchase_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
delete
from return_purchase_det
where return_purchase_id = old.id;
end;
$$
--
-- create trigger del_quotation_det
--
delimiter $$
create trigger del_quotation_det
before delete
on quotation
for each row
begin
delete
from quotation_det
where quotation_id = old.id;
end;
$$
--
-- create trigger set_sale_det_avg_cost
--
delimiter $$
create trigger set_sale_det_avg_cost
before update
on sale
for each row
begin
declare v_sale_id int(11);
declare v_f int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select d.*,0 stock_product_del
from (select sale_id,pd.product_id,pd.qty,averageproductcost unitcost,0 as stock_product
from sale_det pd,product p where pd.product_id=p.id ) d
where qty>0 and sale_id=new.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
select count(1) into v_f from productavgcost where invoice_id=new.id and invoice_type=2;
if (new.payment_status_id!=2 and v_f=0) then
open get_cur;
lbl: loop
fetch get_cur into v_sale_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type)
values(v_product_id,v_unitcost,new.user_upd, NOW(),-v_qty,new.id,2) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
end if;
end;
$$
--
-- create trigger set_purchase_avg_cost
--
delimiter $$
create trigger set_purchase_avg_cost
before update
on purchase
for each row
begin
declare v_purchase_id int(11);
declare v_f int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select D.*,0 stock_product_del
from (select purchase_id,pd.product_id,pd.qty,pd.unitcost,0
as stock_product from purchase_det pd,product p where pd.product_id=p.id)
D where qty>0 and purchase_id=new.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
select count(1) into v_f from productavgcost where invoice_id=new.id and invoice_type=1;
if (new.payment_status_id!=2 and v_f=0) then
open get_cur;
lbl: loop
fetch get_cur into v_purchase_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
insert into productavgcost(productid,productcost,username,changedate,stockmount,invoice_id,invoice_type)
values(v_product_id,v_unitcost,new.user_upd, NOW(),v_qty,new.id,1) ;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
end if;
end;
$$
--
-- create trigger del_purchase_det
--
delimiter $$
create trigger del_purchase_det
before delete
on purchase
for each row
begin
declare v_purchase_id int(11);
declare v_product_id int(11);
declare v_unitcost decimal(18,3);
declare v_stock_product_del decimal(18,3);
declare v_qty decimal(18,3);
declare v_stock_product decimal(18,3);
declare avg_ptoduct_cost decimal(18,3);
declare d int default 0;
declare get_cur cursor for
select D.*,0 stock_product_del
from (select purchase_id,pd.product_id,pd.qty,pd.unitcost,0
as stock_product from purchase_det pd,product p where pd.product_id=p.id)
D where qty>0 and purchase_id=old.id;
declare continue handler for sqlstate '02000'
set d = 1;
declare continue handler for sqlstate '23000'
set d = 1;
delete from productavgcost where invoice_id=old.id and invoice_type=1;
open get_cur;
lbl: loop
fetch get_cur into v_purchase_id, v_product_id,v_qty,v_unitcost,v_stock_product,v_stock_product_del;
if d = 1 then
leave lbl;
close get_cur;
end if;
select case when sum(stockmount) > 0 then sum(productcost*stockmount)/sum(stockmount) end as avg_cost into avg_ptoduct_cost
from productavgcost where productid=v_product_id;
update product set averageproductcost=avg_ptoduct_cost where id=v_product_id;
end loop;
close get_cur;
delete
from purchase_det
where purchase_id = old.id;
end;
$$
delimiter $$
create
view vi_total_paid
as
select
ifnull(sum(ifnull(purchase.paid, 0)), 0) as total_paid,
1 as invoicetype
from purchase union select
ifnull(sum(ifnull(sale.paid, 0)), 0) as total_paid,
2 as invoicetype
from sale union select
ifnull(sum(ifnull(return_purchase.paid, 0)), 0) as total_paid,
3 as invoicetype
from return_purchase union select
ifnull(sum(ifnull(return_sale.paid, 0)), 0) as total_paid,
4 as invoicetype
from return_sale union select
ifnull(sum(ifnull(expenses.amount, 0)), 0) as total_paid,
5 as invoicetype
from expenses;
$$
--
-- create view vi_total_paid_rep
--
delimiter $$
create
view vi_total_paid_rep
as
select
(select
vi_total_paid.total_paid
from vi_total_paid
where vi_total_paid.invoicetype = 1) as purchase,
(select
vi_total_paid.total_paid
from vi_total_paid
where vi_total_paid.invoicetype = 2) as sale,
(select
vi_total_paid.total_paid
from vi_total_paid
where vi_total_paid.invoicetype = 3) as return_purchase,
(select
vi_total_paid.total_paid
from vi_total_paid
where vi_total_paid.invoicetype = 4) as return_sale,
(select
vi_total_paid.total_paid
from vi_total_paid
where vi_total_paid.invoicetype = 5) as expenses;
$$
--
-- create view vi_sale_product_customer
--
delimiter $$
create
view vi_sale_product_customer
as
select
s.date as date,
sd.product_id as product_id,
s.customer_id as customer_id,
s.paid as paid
from (sale_det sd
join sale s)
where sd.sale_id = s.id;
$$
--
-- create view vi_top_sell_product_year
--
delimiter $$
create view vi_top_sell_product_year
as select d.total_paid as total_paid, d.product_id as product_id, fn_row_number(0) as row_num
from (select ifnull(sum(vi_sale_product_customer.paid),0) as total_paid,vi_sale_product_customer.product_id as product_id,fn_row_number(1) as row_num
from vi_sale_product_customer where (year(vi_sale_product_customer.date) = year(curdate())) group by vi_sale_product_customer.product_id
order by ifnull(sum(vi_sale_product_customer.paid),0) desc limit 5) as d ;
$$
--
-- create view vi_product_top_year
--
delimiter $$
create
view vi_product_top_year
as
select
(select
vi_top_sell_product_year.product_id
from vi_top_sell_product_year
where vi_top_sell_product_year.row_num = 1) as first_product_id,
(select
vi_top_sell_product_year.product_id
from vi_top_sell_product_year
where vi_top_sell_product_year.row_num = 2) as second_product_id,
(select
vi_top_sell_product_year.product_id
from vi_top_sell_product_year
where vi_top_sell_product_year.row_num = 3) as third_product_id,
(select
vi_top_sell_product_year.product_id
from vi_top_sell_product_year
where vi_top_sell_product_year.row_num = 4) as forth_product_id,
(select
vi_top_sell_product_year.product_id
from vi_top_sell_product_year
where vi_top_sell_product_year.row_num = 5) as fivith_product_id;
$$
--
-- create view vi_top_sell_product_month
--
delimiter $$
create view vi_top_sell_product_month as
select d.total_paid as total_paid, d.product_id as product_id, fn_row_number(0) as row_num
from (select ifnull(sum(vi_sale_product_customer.paid),0) as total_paid,vi_sale_product_customer.product_id as product_id,fn_row_number(1) as row_num
from vi_sale_product_customer where (month(vi_sale_product_customer.date) = month(curdate())) group by vi_sale_product_customer.product_id
order by ifnull(sum(vi_sale_product_customer.paid),0) desc limit 5) as d ;
$$
--
-- create view vi_top_sell_costomer_month
--
delimiter $$
create view vi_top_sell_costomer_month as
select d.total_paid as total_paid, d.customer_id as customer_id, fn_row_number(0) as row_num
from (select ifnull(sum(vi_sale_product_customer.paid),0) as total_paid,vi_sale_product_customer.customer_id as customer_id,fn_row_number(1) as row_num
from vi_sale_product_customer where (month(vi_sale_product_customer.date) = month(curdate()))
group by vi_sale_product_customer.customer_id order by ifnull(sum(vi_sale_product_customer.paid),0) desc limit 5) as d ;
$$
--
-- create view vi_top_customer
--
delimiter $$
create
view vi_top_customer
as
select
(select
vi_top_sell_costomer_month.customer_id
from vi_top_sell_costomer_month
where vi_top_sell_costomer_month.row_num = 1) as first_customer_id,
(select
vi_top_sell_costomer_month.customer_id
from vi_top_sell_costomer_month
where vi_top_sell_costomer_month.row_num = 2) as second_customer_id,
(select
vi_top_sell_costomer_month.customer_id
from vi_top_sell_costomer_month
where vi_top_sell_costomer_month.row_num = 3) as third_customer_id,
(select
vi_top_sell_costomer_month.customer_id
from vi_top_sell_costomer_month
where vi_top_sell_costomer_month.row_num = 4) as forth_customer_id,
(select
vi_top_sell_costomer_month.customer_id
from vi_top_sell_costomer_month
where vi_top_sell_costomer_month.row_num = 5) as fivith_customer_id;
$$
--
-- create view vi_profit_and_loss
--
delimiter $$
create
view vi_profit_and_loss
as
select
d.date as date,
d.grandtotal as grandtotal,
d.total_paid as total_paid,
d.duetotal as duetotal,
d.total as total,
d.invoicetype as invoicetype,
d.ordertax as ordertax,
d.discount as discount,
d.shipping as shipping,
d.warehouse_id as warehouse_id
from (select
purchase.date as date,
sum(ifnull( purchase.grandtotal, 0)) as grandtotal,
sum(ifnull( purchase.paid, 0)) as total_paid,
sum(ifnull( purchase.due, 0)) as duetotal,
count( purchase.id) as total,
1 as invoicetype,
sum(ifnull( purchase.ordertax, 0)) as ordertax,
sum(ifnull( purchase.discount, 0)) as discount,
sum(ifnull( purchase.shipping, 0)) as shipping,
purchase.warehouse_id as warehouse_id
from purchase
group by purchase.date,
purchase.warehouse_id union select
sale.date as date,
sum(ifnull( sale.grandtotal, 0)) as grandtotal,
sum(ifnull( sale.paid, 0)) as total_paid,
sum(ifnull( sale.due, 0)) as duetotal,
count( sale.id) as total,
2 as invoicetype,
sum(ifnull( sale.ordertax, 0)) as ordertax,
sum(ifnull( sale.discount, 0)) as discount,
sum(ifnull( sale.shipping, 0)) as shipping,
sale.warehouse_id as warehouse_id
from sale
group by sale.date,
sale.warehouse_id union select
return_purchase.date as date,
sum(ifnull( return_purchase.grandtotal, 0)) as grandtotal,
sum(ifnull( return_purchase.paid, 0)) as total_paid,
sum(ifnull( return_purchase.due, 0)) as duetotal,
count( return_purchase.id) as total,
3 as invoicetype,
sum(ifnull( return_purchase.ordertax, 0)) as ordertax,
sum(ifnull( return_purchase.discount, 0)) as discount,
sum(ifnull( return_purchase.shipping, 0)) as shipping,
return_purchase.warehouse_id as warehouse_id
from return_purchase
group by return_purchase.date,
return_purchase.warehouse_id union select
return_sale.date as date,
sum(ifnull( return_sale.grandtotal, 0)) as grandtotal,
sum(ifnull( return_sale.paid, 0)) as total_paid,
sum(ifnull( return_sale.due, 0)) as duetotal,
count( return_sale.id) as total,
4 as invoicetype,
sum(ifnull( return_sale.ordertax, 0)) as ordertax,
sum(ifnull( return_sale.discount, 0)) as discount,
sum(ifnull( return_sale.shipping, 0)) as shipping,
return_sale.warehouse_id as warehouse_id
from return_sale
group by return_sale.date,
return_sale.warehouse_id union select
expenses.date as date,
sum(ifnull( expenses.amount, 0)) as grandtotal,
sum(ifnull( expenses.amount, 0)) as total_paid,
sum(ifnull( expenses.amount, 0)) as duetotal,
count( expenses.id) as total,
5 as invoicetype,
0 as ordertax,
0 as discount,
0 as shipping,
expenses.wharehouse_id as wharehouse_id
from expenses
group by expenses.date,
expenses.wharehouse_id) d;
$$
--
-- create view vi_profit_and_loss_total
--
delimiter $$
create
view vi_profit_and_loss_total
as
select
vi_profit_and_loss.warehouse_id as warehouse_id,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.grandtotal end) as grandtotal_purchase,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.ordertax end) as ordertax_purchase,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.discount end) as discount_purchase,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.shipping end) as shipping_purchase,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.grandtotal end) as grandtotal_sale,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.ordertax end) as ordertax_sale,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.discount end) as discount_sale,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.shipping end) as shipping_sale,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.grandtotal end) as grandtotal_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.ordertax end) as ordertax_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.discount end) as discount_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.shipping end) as shipping_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.grandtotal end) as grandtotal_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.ordertax end) as ordertax_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.discount end) as discount_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.shipping end) as shipping_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 5 then vi_profit_and_loss.grandtotal end) as grandtotal_expenses,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.total_paid end) as total_paid_purchase,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.total_paid end) as total_paid_sale,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.total_paid end) as total_paid_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.total_paid end) as total_paid_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 5 then vi_profit_and_loss.total_paid end) as total_paid_expenses,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.duetotal end) as duetotal_purchase,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.duetotal end) as duetotal_sale,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.duetotal end) as duetotal_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.duetotal end) as duetotal_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 1 then vi_profit_and_loss.total end) as total_count_purchase,
sum(case when vi_profit_and_loss.invoicetype = 2 then vi_profit_and_loss.total end) as total_count_sale,
sum(case when vi_profit_and_loss.invoicetype = 3 then vi_profit_and_loss.total end) as total_count_return_purchase,
sum(case when vi_profit_and_loss.invoicetype = 4 then vi_profit_and_loss.total end) as total_count_return_sale,
sum(case when vi_profit_and_loss.invoicetype = 5 then vi_profit_and_loss.duetotal end) as duetotal_expenses,
vi_profit_and_loss.date as date
from vi_profit_and_loss
group by vi_profit_and_loss.date,
vi_profit_and_loss.warehouse_id;
$$
--
-- create view vi_profil_loss_dash
--
delimiter $$
create
view vi_profil_loss_dash
as
select
d.date as date,
d.grandtotal as grandtotal,
d.total_paid as total_paid,
d.duetotal as duetotal,
d.total as total,
d.invoicetype as invoicetype,
d.ordertax as ordertax,
d.discount as discount,
d.shipping as shipping
from (select
purchase.date as date,
sum(ifnull( purchase.grandtotal, 0)) as grandtotal,
sum(ifnull( purchase.paid, 0)) as total_paid,
sum(ifnull( purchase.due, 0)) as duetotal,
count( purchase.id) as total,
1 as invoicetype,
sum(ifnull( purchase.ordertax, 0)) as ordertax,
sum(ifnull( purchase.discount, 0)) as discount,
sum(ifnull( purchase.shipping, 0)) as shipping
from purchase
group by purchase.date union select
sale.date as date,
sum(ifnull( sale.grandtotal, 0)) as grandtotal,
sum(ifnull( sale.paid, 0)) as total_paid,
sum(ifnull( sale.due, 0)) as duetotal,
count( sale.id) as total,
2 as invoicetype,
sum(ifnull( sale.ordertax, 0)) as ordertax,
sum(ifnull( sale.discount, 0)) as discount,
sum(ifnull( sale.shipping, 0)) as shipping
from sale
group by sale.date union select
return_purchase.date as date,
sum(ifnull( return_purchase.grandtotal, 0)) as grandtotal,
sum(ifnull( return_purchase.paid, 0)) as total_paid,
sum(ifnull( return_purchase.due, 0)) as duetotal,
count( return_purchase.id) as total,
3 as invoicetype,
sum(ifnull( return_purchase.ordertax, 0)) as ordertax,
sum(ifnull( return_purchase.discount, 0)) as discount,
sum(ifnull( return_purchase.shipping, 0)) as shipping
from return_purchase
group by return_purchase.date union select
return_sale.date as date,
sum(ifnull( return_sale.grandtotal, 0)) as grandtotal,
sum(ifnull( return_sale.paid, 0)) as total_paid,
sum(ifnull( return_sale.due, 0)) as duetotal,
count( return_sale.id) as total,
4 as invoicetype,
sum(ifnull( return_sale.ordertax, 0)) as ordertax,
sum(ifnull( return_sale.discount, 0)) as discount,
sum(ifnull( return_sale.shipping, 0)) as shipping
from return_sale
group by return_sale.date union select
expenses.date as date,
sum(ifnull( expenses.amount, 0)) as grandtotal,
sum(ifnull( expenses.amount, 0)) as total_paid,
sum(ifnull( expenses.amount, 0)) as duetotal,
count( expenses.id) as total,
5 as invoicetype,
0 as ordertax,
0 as discount,
0 as shipping
from expenses
group by expenses.date) d;
$$
--
-- create view vi_profil_loss_dash_total
--
delimiter $$
create
view vi_profil_loss_dash_total
as
select
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.grandtotal end) as grandtotal_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.ordertax end) as ordertax_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.discount end) as discount_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.shipping end) as shipping_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.grandtotal end) as grandtotal_sale,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.ordertax end) as ordertax_sale,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.discount end) as discount_sale,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.shipping end) as shipping_sale,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.grandtotal end) as grandtotal_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.ordertax end) as ordertax_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.discount end) as discount_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.shipping end) as shipping_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.grandtotal end) as grandtotal_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.ordertax end) as ordertax_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.discount end) as discount_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.shipping end) as shipping_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 5 then vi_profil_loss_dash.grandtotal end) as grandtotal_expenses,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.total_paid end) as total_paid_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.total_paid end) as total_paid_sale,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.total_paid end) as total_paid_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.total_paid end) as total_paid_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 5 then vi_profil_loss_dash.total_paid end) as total_paid_expenses,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.duetotal end) as duetotal_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.duetotal end) as duetotal_sale,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.duetotal end) as duetotal_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.duetotal end) as duetotal_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 1 then vi_profil_loss_dash.total end) as total_count_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 2 then vi_profil_loss_dash.total end) as total_count_sale,
sum(case when vi_profil_loss_dash.invoicetype = 3 then vi_profil_loss_dash.total end) as total_count_return_purchase,
sum(case when vi_profil_loss_dash.invoicetype = 4 then vi_profil_loss_dash.total end) as total_count_return_sale,
sum(case when vi_profil_loss_dash.invoicetype = 5 then vi_profil_loss_dash.duetotal end) as duetotal_expenses,
vi_profil_loss_dash.date as date
from vi_profil_loss_dash
group by vi_profil_loss_dash.date;
$$
delimiter $$
INSERT INTO lk_country VALUES
(1, 'Afghanistan', 'AFG', 4, 93, 'AFGHANISTAN', 'AF'),
(2, 'Albania', 'ALB', 8, 355, 'ALBANIA', 'AL'),
(3, 'Algeria', 'DZA', 12, 213, 'ALGERIA', 'DZ'),
(4, 'American Samoa', 'ASM', 16, 1684, 'AMERICAN SAMOA', 'AS'),
(5, 'Andorra', 'AND', 20, 376, 'ANDORRA', 'AD'),
(6, 'Angola', 'AGO', 24, 244, 'ANGOLA', 'AO'),
(7, 'Anguilla', 'AIA', 660, 1264, 'ANGUILLA', 'AI'),
(8, 'Antarctica', NULL, NULL, 0, 'ANTARCTICA', 'AQ'),
(9, 'Antigua and Barbuda', 'ATG', 28, 1268, 'ANTIGUA AND BARBUDA', 'AG'),
(10, 'Argentina', 'ARG', 32, 54, 'ARGENTINA', 'AR'),
(11, 'Armenia', 'ARM', 51, 374, 'ARMENIA', 'AM'),
(12, 'Aruba', 'ABW', 533, 297, 'ARUBA', 'AW'),
(13, 'Australia', 'AUS', 36, 61, 'AUSTRALIA', 'AU'),
(14, 'Austria', 'AUT', 40, 43, 'AUSTRIA', 'AT'),
(15, 'Azerbaijan', 'AZE', 31, 994, 'AZERBAIJAN', 'AZ'),
(16, 'Bahamas', 'BHS', 44, 1242, 'BAHAMAS', 'BS'),
(17, 'Bahrain', 'BHR', 48, 973, 'BAHRAIN', 'BH'),
(18, 'Bangladesh', 'BGD', 50, 880, 'BANGLADESH', 'BD'),
(19, 'Barbados', 'BRB', 52, 1246, 'BARBADOS', 'BB'),
(20, 'Belarus', 'BLR', 112, 375, 'BELARUS', 'BY'),
(21, 'Belgium', 'BEL', 56, 32, 'BELGIUM', 'BE'),
(22, 'Belize', 'BLZ', 84, 501, 'BELIZE', 'BZ'),
(23, 'Benin', 'BEN', 204, 229, 'BENIN', 'BJ'),
(24, 'Bermuda', 'BMU', 60, 1441, 'BERMUDA', 'BM'),
(25, 'Bhutan', 'BTN', 64, 975, 'BHUTAN', 'BT'),
(26, 'Bolivia', 'BOL', 68, 591, 'BOLIVIA', 'BO'),
(27, 'Bosnia and Herzegovina', 'BIH', 70, 387, 'BOSNIA AND HERZEGOVINA', 'BA'),
(28, 'Botswana', 'BWA', 72, 267, 'BOTSWANA', 'BW'),
(29, 'Bouvet Island', NULL, NULL, 0, 'BOUVET ISLAND', 'BV'),
(30, 'Brazil', 'BRA', 76, 55, 'BRAZIL', 'BR'),
(31, 'British Indian Ocean Territory', NULL, NULL, 246, 'BRITISH INDIAN OCEAN TERRITORY', 'IO'),
(32, 'Brunei Darussalam', 'BRN', 96, 673, 'BRUNEI DARUSSALAM', 'BN'),
(33, 'Bulgaria', 'BGR', 100, 359, 'BULGARIA', 'BG'),
(34, 'Burkina Faso', 'BFA', 854, 226, 'BURKINA FASO', 'BF'),
(35, 'Burundi', 'BDI', 108, 257, 'BURUNDI', 'BI'),
(36, 'Cambodia', 'KHM', 116, 855, 'CAMBODIA', 'KH'),
(37, 'Cameroon', 'CMR', 120, 237, 'CAMEROON', 'CM'),
(38, 'Canada', 'CAN', 124, 1, 'CANADA', 'CA'),
(39, 'Cape Verde', 'CPV', 132, 238, 'CAPE VERDE', 'CV'),
(40, 'Cayman Islands', 'CYM', 136, 1345, 'CAYMAN ISLANDS', 'KY'),
(41, 'Central African Republic', 'CAF', 140, 236, 'CENTRAL AFRICAN REPUBLIC', 'CF'),
(42, 'Chad', 'TCD', 148, 235, 'CHAD', 'TD'),
(43, 'Chile', 'CHL', 152, 56, 'CHILE', 'CL'),
(44, 'China', 'CHN', 156, 86, 'CHINA', 'CN'),
(45, 'Christmas Island', NULL, NULL, 61, 'CHRISTMAS ISLAND', 'CX'),
(46, 'Cocos (Keeling) Islands', NULL, NULL, 672, 'COCOS (KEELING) ISLANDS', 'CC'),
(47, 'Colombia', 'COL', 170, 57, 'COLOMBIA', 'CO'),
(48, 'Comoros', 'COM', 174, 269, 'COMOROS', 'KM'),
(49, 'Congo', 'COG', 178, 242, 'CONGO', 'CG'),
(50, 'Congo, the Democratic Republic of the', 'COD', 180, 242, 'CONGO, THE DEMOCRATIC REPUBLIC OF THE', 'CD'),
(51, 'Cook Islands', 'COK', 184, 682, 'COOK ISLANDS', 'CK'),
(52, 'Costa Rica', 'CRI', 188, 506, 'COSTA RICA', 'CR'),
(53, 'Cote D''Ivoire', 'CIV', 384, 225, 'COTE D''IVOIRE', 'CI'),
(54, 'Croatia', 'HRV', 191, 385, 'CROATIA', 'HR'),
(55, 'Cuba', 'CUB', 192, 53, 'CUBA', 'CU'),
(56, 'Cyprus', 'CYP', 196, 357, 'CYPRUS', 'CY'),
(57, 'Czech Republic', 'CZE', 203, 420, 'CZECH REPUBLIC', 'CZ'),
(58, 'Denmark', 'DNK', 208, 45, 'DENMARK', 'DK'),
(59, 'Djibouti', 'DJI', 262, 253, 'DJIBOUTI', 'DJ'),
(60, 'Dominica', 'DMA', 212, 1767, 'DOMINICA', 'DM'),
(61, 'Dominican Republic', 'DOM', 214, 1809, 'DOMINICAN REPUBLIC', 'DO'),
(62, 'Ecuador', 'ECU', 218, 593, 'ECUADOR', 'EC'),
(63, 'Egypt', 'EGY', 818, 20, 'EGYPT', 'EG'),
(64, 'El Salvador', 'SLV', 222, 503, 'EL SALVADOR', 'SV'),
(65, 'Equatorial Guinea', 'GNQ', 226, 240, 'EQUATORIAL GUINEA', 'GQ'),
(66, 'Eritrea', 'ERI', 232, 291, 'ERITREA', 'ER'),
(67, 'Estonia', 'EST', 233, 372, 'ESTONIA', 'EE'),
(68, 'Ethiopia', 'ETH', 231, 251, 'ETHIOPIA', 'ET'),
(69, 'Falkland Islands (Malvinas)', 'FLK', 238, 500, 'FALKLAND ISLANDS (MALVINAS)', 'FK'),
(70, 'Faroe Islands', 'FRO', 234, 298, 'FAROE ISLANDS', 'FO'),
(71, 'Fiji', 'FJI', 242, 679, 'FIJI', 'FJ'),
(72, 'Finland', 'FIN', 246, 358, 'FINLAND', 'FI'),
(73, 'France', 'FRA', 250, 33, 'FRANCE', 'FR'),
(74, 'French Guiana', 'GUF', 254, 594, 'FRENCH GUIANA', 'GF'),
(75, 'French Polynesia', 'PYF', 258, 689, 'FRENCH POLYNESIA', 'PF'),
(76, 'French Southern Territories', NULL, NULL, 0, 'FRENCH SOUTHERN TERRITORIES', 'TF'),
(77, 'Gabon', 'GAB', 266, 241, 'GABON', 'GA'),
(78, 'Gambia', 'GMB', 270, 220, 'GAMBIA', 'GM'),
(79, 'Georgia', 'GEO', 268, 995, 'GEORGIA', 'GE'),
(80, 'Germany', 'DEU', 276, 49, 'GERMANY', 'DE'),
(81, 'Ghana', 'GHA', 288, 233, 'GHANA', 'GH'),
(82, 'Gibraltar', 'GIB', 292, 350, 'GIBRALTAR', 'GI'),
(83, 'Greece', 'GRC', 300, 30, 'GREECE', 'GR'),
(84, 'Greenland', 'GRL', 304, 299, 'GREENLAND', 'GL'),
(85, 'Grenada', 'GRD', 308, 1473, 'GRENADA', 'GD'),
(86, 'Guadeloupe', 'GLP', 312, 590, 'GUADELOUPE', 'GP'),
(87, 'Guam', 'GUM', 316, 1671, 'GUAM', 'GU'),
(88, 'Guatemala', 'GTM', 320, 502, 'GUATEMALA', 'GT'),
(89, 'Guinea', 'GIN', 324, 224, 'GUINEA', 'GN'),
(90, 'Guinea-Bissau', 'GNB', 624, 245, 'GUINEA-BISSAU', 'GW'),
(91, 'Guyana', 'GUY', 328, 592, 'GUYANA', 'GY'),
(92, 'Haiti', 'HTI', 332, 509, 'HAITI', 'HT'),
(93, 'Heard Island and Mcdonald Islands', NULL, NULL, 0, 'HEARD ISLAND AND MCDONALD ISLANDS', 'HM'),
(94, 'Holy See (Vatican City State)', 'VAT', 336, 39, 'HOLY SEE (VATICAN CITY STATE)', 'VA'),
(95, 'Honduras', 'HND', 340, 504, 'HONDURAS', 'HN'),
(96, 'Hong Kong', 'HKG', 344, 852, 'HONG KONG', 'HK'),
(97, 'Hungary', 'HUN', 348, 36, 'HUNGARY', 'HU'),
(98, 'Iceland', 'ISL', 352, 354, 'ICELAND', 'IS'),
(99, 'India', 'IND', 356, 91, 'INDIA', 'IN'),
(100, 'Indonesia', 'IDN', 360, 62, 'INDONESIA', 'ID'),
(101, 'Iran, Islamic Republic of', 'IRN', 364, 98, 'IRAN, ISLAMIC REPUBLIC OF', 'IR'),
(102, 'Iraq', 'IRQ', 368, 964, 'IRAQ', 'IQ'),
(103, 'Ireland', 'IRL', 372, 353, 'IRELAND', 'IE'),
(104, 'Israel', 'ISR', 376, 972, 'ISRAEL', 'IL'),
(105, 'Italy', 'ITA', 380, 39, 'ITALY', 'IT'),
(106, 'Jamaica', 'JAM', 388, 1876, 'JAMAICA', 'JM'),
(107, 'Japan', 'JPN', 392, 81, 'JAPAN', 'JP'),
(108, 'Jordan', 'JOR', 400, 962, 'JORDAN', 'JO'),
(109, 'Kazakhstan', 'KAZ', 398, 7, 'KAZAKHSTAN', 'KZ'),
(110, 'Kenya', 'KEN', 404, 254, 'KENYA', 'KE'),
(111, 'Kiribati', 'KIR', 296, 686, 'KIRIBATI', 'KI'),
(112, 'Korea, Democratic People''s Republic of', 'PRK', 408, 850, 'KOREA, DEMOCRATIC PEOPLE''S REPUBLIC OF', 'KP'),
(113, 'Korea, Republic of', 'KOR', 410, 82, 'KOREA, REPUBLIC OF', 'KR'),
(114, 'Kuwait', 'KWT', 414, 965, 'KUWAIT', 'KW'),
(115, 'Kyrgyzstan', 'KGZ', 417, 996, 'KYRGYZSTAN', 'KG'),
(116, 'Lao People''s Democratic Republic', 'LAO', 418, 856, 'LAO PEOPLE''S DEMOCRATIC REPUBLIC', 'LA'),
(117, 'Latvia', 'LVA', 428, 371, 'LATVIA', 'LV'),
(118, 'Lebanon', 'LBN', 422, 961, 'LEBANON', 'LB'),
(119, 'Lesotho', 'LSO', 426, 266, 'LESOTHO', 'LS'),
(120, 'Liberia', 'LBR', 430, 231, 'LIBERIA', 'LR'),
(121, 'Libyan Arab Jamahiriya', 'LBY', 434, 218, 'LIBYAN ARAB JAMAHIRIYA', 'LY'),
(122, 'Liechtenstein', 'LIE', 438, 423, 'LIECHTENSTEIN', 'LI'),
(123, 'Lithuania', 'LTU', 440, 370, 'LITHUANIA', 'LT'),
(124, 'Luxembourg', 'LUX', 442, 352, 'LUXEMBOURG', 'LU'),
(125, 'Macao', 'MAC', 446, 853, 'MACAO', 'MO'),
(126, 'Macedonia, the Former Yugoslav Republic of', 'MKD', 807, 389, 'MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF', 'MK'),
(127, 'Madagascar', 'MDG', 450, 261, 'MADAGASCAR', 'MG'),
(128, 'Malawi', 'MWI', 454, 265, 'MALAWI', 'MW'),
(129, 'Malaysia', 'MYS', 458, 60, 'MALAYSIA', 'MY'),
(130, 'Maldives', 'MDV', 462, 960, 'MALDIVES', 'MV'),
(131, 'Mali', 'MLI', 466, 223, 'MALI', 'ML'),
(132, 'Malta', 'MLT', 470, 356, 'MALTA', 'MT'),
(133, 'Marshall Islands', 'MHL', 584, 692, 'MARSHALL ISLANDS', 'MH'),
(134, 'Martinique', 'MTQ', 474, 596, 'MARTINIQUE', 'MQ'),
(135, 'Mauritania', 'MRT', 478, 222, 'MAURITANIA', 'MR'),
(136, 'Mauritius', 'MUS', 480, 230, 'MAURITIUS', 'MU'),
(137, 'Mayotte', NULL, NULL, 269, 'MAYOTTE', 'YT'),
(138, 'Mexico', 'MEX', 484, 52, 'MEXICO', 'MX'),
(139, 'Micronesia, Federated States of', 'FSM', 583, 691, 'MICRONESIA, FEDERATED STATES OF', 'FM'),
(140, 'Moldova, Republic of', 'MDA', 498, 373, 'MOLDOVA, REPUBLIC OF', 'MD'),
(141, 'Monaco', 'MCO', 492, 377, 'MONACO', 'MC'),
(142, 'Mongolia', 'MNG', 496, 976, 'MONGOLIA', 'MN'),
(143, 'Montserrat', 'MSR', 500, 1664, 'MONTSERRAT', 'MS'),
(144, 'Morocco', 'MAR', 504, 212, 'MOROCCO', 'MA'),
(145, 'Mozambique', 'MOZ', 508, 258, 'MOZAMBIQUE', 'MZ'),
(146, 'Myanmar', 'MMR', 104, 95, 'MYANMAR', 'MM'),
(147, 'Namibia', 'NAM', 516, 264, 'NAMIBIA', 'NA'),
(148, 'Nauru', 'NRU', 520, 674, 'NAURU', 'NR'),
(149, 'Nepal', 'NPL', 524, 977, 'NEPAL', 'NP'),
(150, 'Netherlands', 'NLD', 528, 31, 'NETHERLANDS', 'NL'),
(151, 'Netherlands Antilles', 'ANT', 530, 599, 'NETHERLANDS ANTILLES', 'AN'),
(152, 'New Caledonia', 'NCL', 540, 687, 'NEW CALEDONIA', 'NC'),
(153, 'New Zealand', 'NZL', 554, 64, 'NEW ZEALAND', 'NZ'),
(154, 'Nicaragua', 'NIC', 558, 505, 'NICARAGUA', 'NI'),
(155, 'Niger', 'NER', 562, 227, 'NIGER', 'NE'),
(156, 'Nigeria', 'NGA', 566, 234, 'NIGERIA', 'NG'),
(157, 'Niue', 'NIU', 570, 683, 'NIUE', 'NU'),
(158, 'Norfolk Island', 'NFK', 574, 672, 'NORFOLK ISLAND', 'NF'),
(159, 'Northern Mariana Islands', 'MNP', 580, 1670, 'NORTHERN MARIANA ISLANDS', 'MP'),
(160, 'Norway', 'NOR', 578, 47, 'NORWAY', 'NO'),
(161, 'Oman', 'OMN', 512, 968, 'OMAN', 'OM'),
(162, 'Pakistan', 'PAK', 586, 92, 'PAKISTAN', 'PK'),
(163, 'Palau', 'PLW', 585, 680, 'PALAU', 'PW'),
(164, 'Palestinian Territory, Occupied', NULL, NULL, 970, 'PALESTINIAN TERRITORY, OCCUPIED', 'PS'),
(165, 'Panama', 'PAN', 591, 507, 'PANAMA', 'PA'),
(166, 'Papua New Guinea', 'PNG', 598, 675, 'PAPUA NEW GUINEA', 'PG'),
(167, 'Paraguay', 'PRY', 600, 595, 'PARAGUAY', 'PY'),
(168, 'Peru', 'PER', 604, 51, 'PERU', 'PE'),
(169, 'Philippines', 'PHL', 608, 63, 'PHILIPPINES', 'PH'),
(170, 'Pitcairn', 'PCN', 612, 0, 'PITCAIRN', 'PN'),
(171, 'Poland', 'POL', 616, 48, 'POLAND', 'PL'),
(172, 'Portugal', 'PRT', 620, 351, 'PORTUGAL', 'PT'),
(173, 'Puerto Rico', 'PRI', 630, 1787, 'PUERTO RICO', 'PR'),
(174, 'Qatar', 'QAT', 634, 974, 'QATAR', 'QA'),
(175, 'Reunion', 'REU', 638, 262, 'REUNION', 'RE'),
(176, 'Romania', 'ROM', 642, 40, 'ROMANIA', 'RO'),
(177, 'Russian Federation', 'RUS', 643, 70, 'RUSSIAN FEDERATION', 'RU'),
(178, 'Rwanda', 'RWA', 646, 250, 'RWANDA', 'RW'),
(179, 'Saint Helena', 'SHN', 654, 290, 'SAINT HELENA', 'SH'),
(180, 'Saint Kitts and Nevis', 'KNA', 659, 1869, 'SAINT KITTS AND NEVIS', 'KN'),
(181, 'Saint Lucia', 'LCA', 662, 1758, 'SAINT LUCIA', 'LC'),
(182, 'Saint Pierre and Miquelon', 'SPM', 666, 508, 'SAINT PIERRE AND MIQUELON', 'PM'),
(183, 'Saint Vincent and the Grenadines', 'VCT', 670, 1784, 'SAINT VINCENT AND THE GRENADINES', 'VC'),
(184, 'Samoa', 'WSM', 882, 684, 'SAMOA', 'WS'),
(185, 'San Marino', 'SMR', 674, 378, 'SAN MARINO', 'SM'),
(186, 'Sao Tome and Principe', 'STP', 678, 239, 'SAO TOME AND PRINCIPE', 'ST'),
(187, 'Saudi Arabia', 'SAU', 682, 966, 'SAUDI ARABIA', 'SA'),
(188, 'Senegal', 'SEN', 686, 221, 'SENEGAL', 'SN'),
(189, 'Serbia and Montenegro', NULL, NULL, 381, 'SERBIA AND MONTENEGRO', 'CS'),
(190, 'Seychelles', 'SYC', 690, 248, 'SEYCHELLES', 'SC'),
(191, 'Sierra Leone', 'SLE', 694, 232, 'SIERRA LEONE', 'SL'),
(192, 'Singapore', 'SGP', 702, 65, 'SINGAPORE', 'SG'),
(193, 'Slovakia', 'SVK', 703, 421, 'SLOVAKIA', 'SK'),
(194, 'Slovenia', 'SVN', 705, 386, 'SLOVENIA', 'SI'),
(195, 'Solomon Islands', 'SLB', 90, 677, 'SOLOMON ISLANDS', 'SB'),
(196, 'Somalia', 'SOM', 706, 252, 'SOMALIA', 'SO'),
(197, 'South Africa', 'ZAF', 710, 27, 'SOUTH AFRICA', 'ZA'),
(198, 'South Georgia and the South Sandwich Islands', NULL, NULL, 0, 'SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS', 'GS'),
(199, 'Spain', 'ESP', 724, 34, 'SPAIN', 'ES'),
(200, 'Sri Lanka', 'LKA', 144, 94, 'SRI LANKA', 'LK'),
(201, 'Sudan', 'SDN', 736, 249, 'SUDAN', 'SD'),
(202, 'Suriname', 'SUR', 740, 597, 'SURINAME', 'SR'),
(203, 'Svalbard and Jan Mayen', 'SJM', 744, 47, 'SVALBARD AND JAN MAYEN', 'SJ'),
(204, 'Swaziland', 'SWZ', 748, 268, 'SWAZILAND', 'SZ'),
(205, 'Sweden', 'SWE', 752, 46, 'SWEDEN', 'SE'),
(206, 'Switzerland', 'CHE', 756, 41, 'SWITZERLAND', 'CH'),
(207, 'Syrian Arab Republic', 'SYR', 760, 963, 'SYRIAN ARAB REPUBLIC', 'SY'),
(208, 'Taiwan, Province of China', 'TWN', 158, 886, 'TAIWAN, PROVINCE OF CHINA', 'TW'),
(209, 'Tajikistan', 'TJK', 762, 992, 'TAJIKISTAN', 'TJ'),
(210, 'Tanzania, United Republic of', 'TZA', 834, 255, 'TANZANIA, UNITED REPUBLIC OF', 'TZ'),
(211, 'Thailand', 'THA', 764, 66, 'THAILAND', 'TH'),
(212, 'Timor-Leste', NULL, NULL, 670, 'TIMOR-LESTE', 'TL'),
(213, 'Togo', 'TGO', 768, 228, 'TOGO', 'TG'),
(214, 'Tokelau', 'TKL', 772, 690, 'TOKELAU', 'TK'),
(215, 'Tonga', 'TON', 776, 676, 'TONGA', 'TO'),
(216, 'Trinidad and Tobago', 'TTO', 780, 1868, 'TRINIDAD AND TOBAGO', 'TT'),
(217, 'Tunisia', 'TUN', 788, 216, 'TUNISIA', 'TN'),
(218, 'Turkey', 'TUR', 792, 90, 'TURKEY', 'TR'),
(219, 'Turkmenistan', 'TKM', 795, 7370, 'TURKMENISTAN', 'TM'),
(220, 'Turks and Caicos Islands', 'TCA', 796, 1649, 'TURKS AND CAICOS ISLANDS', 'TC'),
(221, 'Tuvalu', 'TUV', 798, 688, 'TUVALU', 'TV'),
(222, 'Uganda', 'UGA', 800, 256, 'UGANDA', 'UG'),
(223, 'Ukraine', 'UKR', 804, 380, 'UKRAINE', 'UA'),
(224, 'United Arab Emirates', 'ARE', 784, 971, 'UNITED ARAB EMIRATES', 'AE'),
(225, 'United Kingdom', 'GBR', 826, 44, 'UNITED KINGDOM', 'GB'),
(226, 'United States', 'USA', 840, 1, 'UNITED STATES', 'US'),
(227, 'United States Minor Outlying Islands', NULL, NULL, 1, 'UNITED STATES MINOR OUTLYING ISLANDS', 'UM'),
(228, 'Uruguay', 'URY', 858, 598, 'URUGUAY', 'UY'),
(229, 'Uzbekistan', 'UZB', 860, 998, 'UZBEKISTAN', 'UZ'),
(230, 'Vanuatu', 'VUT', 548, 678, 'VANUATU', 'VU'),
(231, 'Venezuela', 'VEN', 862, 58, 'VENEZUELA', 'VE'),
(232, 'Viet Nam', 'VNM', 704, 84, 'VIET NAM', 'VN'),
(233, 'Virgin Islands, British', 'VGB', 92, 1284, 'VIRGIN ISLANDS, BRITISH', 'VG'),
(234, 'Virgin Islands, U.s.', 'VIR', 850, 1340, 'VIRGIN ISLANDS, U.S.', 'VI'),
(235, 'Wallis and Futuna', 'WLF', 876, 681, 'WALLIS AND FUTUNA', 'WF'),
(236, 'Western Sahara', 'ESH', 732, 212, 'WESTERN SAHARA', 'EH'),
(237, 'Yemen', 'YEM', 887, 967, 'YEMEN', 'YE'),
(238, 'Zambia', 'ZMB', 894, 260, 'ZAMBIA', 'ZM'),
(239, 'Zimbabwe', 'ZWE', 716, 263, 'ZIMBABWE', 'ZW');
$$
--
-- Dumping data for table lk_payment_status
--
delimiter $$
INSERT INTO lk_payment_status VALUES
(1, 'مدفوع كامل', 'Arabic'),
(1, 'Paid', 'English'),
(1, 'Payé', 'French'),
(1, 'Pagado', 'Spanish'),
(2, 'غير مدفوع', 'Arabic'),
(2, 'UnPaid', 'English'),
(2, 'Non payé', 'French'),
(2, 'No pagado', 'Spanish'),
(3, 'مدفوع جزئي', 'Arabic'),
(3, 'Partial Paid', 'English'),
(3, 'Partiellement Payé', 'French'),
(3, 'Pago Parcial', 'Spanish');
$$
delimiter $$
INSERT INTO lk_invoice_status VALUES
(1, 'تم الاستلام', 'Arabic'),
(1, 'Received', 'English'),
(1, 'A reçu', 'French'),
(1, 'Recibida', 'Spanish'),
(2, 'قيد الانتظار', 'Arabic'),
(2, 'Pending', 'English'),
(2, 'En instance', 'French'),
(2, 'Pendiente', 'Spanish'),
(3, 'تم الطلب', 'Arabic'),
(3, 'Ordered', 'English'),
(3, 'Ordonné', 'French'),
(3, 'Ordenado', 'Spanish');
$$
delimiter $$
INSERT INTO supplier VALUES
(19, 'John Doe Company', '+9xxxxxxxx', 73, 'm@m.com', 'Paris', '');
$$
--
-- Dumping data for table lk_warehouse
--
delimiter $$
INSERT INTO lk_warehouse VALUES
(9, 'Warehouse 1 ', 224, 'M@M.COM', '+971XXXXXXXXX', 'Dubai', '123366', 1),
(10, 'Warehouse 2', 187, '', '009660000000', 'Riyadh', '', 1);
$$
delimiter $$
INSERT INTO lk_products_sell_status VALUES
(1, 'All'),
(2, 'Show In Purchuse'),
(3, 'Show In Sale');
$$
--
-- Dumping data for table lk_brand
--
delimiter $$
INSERT INTO lk_brand VALUES
(5, 'Adidas', 'Adidas is more than sportswear and workout clothes', '[{"name":"files\\/iptHUHoJ_400x400 (1)_h7zgk2na.jpg","usrName":"iptHUHoJ_400x400 (1).jpg","size":9999,"type":"image\\/jpeg","thumbnail":"files\\/thiptHUHoJ_400x400 (1)_tmdha1xy.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":2083,"searchStr":"iptHUHoJ_400x400 (1).jpg,!:sStrEnd"}]'),
(6, 'Nike', '', '[{"name":"files\\/download (1)_tbwyrm31.png","usrName":"download (1).png","size":2864,"type":"image\\/png","thumbnail":"files\\/thdownload (1)_nbt3hzri.png","thumbnail_type":"image\\/png","thumbnail_size":3445,"searchStr":"download (1).png,!:sStrEnd"}]');
$$
delimiter $$
INSERT INTO lk_papersize VALUES
(1, '40 per sheet(A4)(1.799*1.003)'),
(2, '30 per sheet(2.625*1)'),
(3, '24 per sheet(A4)(2.48*1.334)'),
(4, '20 per sheet(4*1)'),
(5, '18 per sheet(A4)(2.5*1.835)'),
(6, '14 per sheet(4*1.33)'),
(7, '12 per sheet(A4)(2.5*2.834)'),
(8, '10 per sheet(4*2)');
$$
delimiter $$
INSERT INTO lk_currency VALUES
(1, '', 'Abkhazian apsar', ''),
(3, 'AFN', 'Afghan afghani', '؋'),
(5, 'ALL', 'Albanian lek', 'L'),
(6, '', 'Alderney pound', '£'),
(11, 'AOA', 'Angolan kwanza', 'Kz'),
(14, 'ARS', 'Argentine peso', '$'),
(16, 'AWG', 'Aruban florin', 'ƒ'),
(17, '', 'Ascension pound', '£'),
(21, 'AZN', 'Azerbaijani manat', ''),
(22, 'BSD', 'Bahamian dollar', '$'),
(23, 'BHD', 'Bahraini dinar', '.د.ب'),
(24, 'BDT', 'Bangladeshi taka', '৳'),
(25, 'BBD', 'Barbadian dollar', '$'),
(26, 'BYR', 'Belarusian ruble', 'Br'),
(28, 'BZD', 'Belize dollar', '$'),
(30, 'BMD', 'Bermudian dollar', '$'),
(31, 'BTN', 'Bhutanese ngultrum', 'Nu.'),
(33, 'BOB', 'Bolivian boliviano', 'Bs.'),
(35, 'BAM', 'Bosnia and Herzegovina convertible mark', 'KM'),
(37, 'BRL', 'Brazilian real', 'R$'),
(39, '', 'British Virgin Islands dollar', '$'),
(43, 'BGN', 'Bulgarian lev', 'лв'),
(45, 'BIF', 'Burundian franc', 'Fr'),
(46, 'KHR', 'Cambodian riel', '៛'),
(49, 'CAD', 'Canadian dollar', '$'),
(50, 'CVE', 'Cape Verdean escudo', 'Esc'),
(51, 'KYD', 'Cayman Islands dollar', '$'),
(54, 'CLP', 'Chilean peso', '$'),
(55, 'CNY', 'Chinese yuan', '¥'),
(57, 'COP', 'Colombian peso', '$'),
(58, 'KMF', 'Comorian franc', 'Fr'),
(59, 'CDF', 'Congolese franc', 'Fr'),
(62, '', 'Cook Islands dollar', '$'),
(63, 'CRC', 'Costa Rican colón', '₡'),
(65, 'HRK', 'Croatian kuna', 'kn'),
(66, 'CUC', 'Cuban convertible peso', '$'),
(67, 'CUP', 'Cuban peso', '$'),
(70, 'CZK', 'Czech koruna', 'Kč'),
(72, 'DJF', 'Djiboutian franc', 'Fr'),
(74, 'DOP', 'Dominican peso', '$'),
(78, '', '', ''),
(79, 'EGP', 'Egyptian pound', ' ج.م'),
(82, 'ERN', 'Eritrean nakfa', 'Nfk'),
(84, 'ETB', 'Ethiopian birr', 'Br'),
(85, 'FKP', 'Falkland Islands pound', '£'),
(86, 'DKK', 'Danish krone', 'kr'),
(87, '', 'Faroese króna', 'kr'),
(88, 'FJD', 'Fijian dollar', '$'),
(92, 'XAF', 'Central African CFA franc', 'Fr'),
(93, 'GMD', 'Gambian dalasi', 'D'),
(94, 'GEL', 'Georgian lari', 'ლ'),
(96, 'GHS', 'Ghana cedi', '₵'),
(97, 'GIP', 'Gibraltar pound', '£'),
(100, 'GTQ', 'Guatemalan quetzal', 'Q'),
(102, '', 'Guernsey pound', '£'),
(103, 'GNF', 'Guinean franc', 'Fr'),
(105, 'GYD', 'Guyanese dollar', '$'),
(106, 'HTG', 'Haitian gourde', 'G'),
(107, 'HNL', 'Honduran lempira', 'L'),
(108, 'HKD', 'Hong Kong dollar', '$'),
(109, 'HUF', 'Hungarian forint', 'Ft'),
(110, 'ISK', 'Icelandic króna', 'kr'),
(112, 'IDR', 'Indonesian rupiah', 'Rp'),
(113, 'IRR', 'Iranian rial', '﷼'),
(114, 'IQD', 'Iraqi dinar', 'ع.د'),
(117, '', 'Manx pound', '£'),
(120, 'JMD', 'Jamaican dollar', '$'),
(121, 'JPY', 'Japanese yen', '¥'),
(123, '', 'Jersey pound', '£'),
(125, 'KZT', 'Kazakhstani tenge', ''),
(126, 'KES', 'Kenyan shilling', 'Sh'),
(128, '', 'Kiribati dollar', '$'),
(129, 'KPW', 'North Korean won', '₩'),
(130, 'KRW', 'South Korean won', '₩'),
(132, 'KWD', 'Kuwaiti dinar', 'د.ك'),
(133, 'KGS', 'Kyrgyzstani som', 'лв'),
(134, 'LAK', 'Lao kip', '₭'),
(136, 'LBP', 'Lebanese pound', 'ل.ل'),
(137, 'LSL', 'Lesotho loti', 'L'),
(139, 'LRD', 'Liberian dollar', '$'),
(140, 'LYD', 'Libyan dinar', 'ل.د'),
(143, ' code', 'Currency Name', 'Symbol'),
(144, 'MOP', 'Macanese pataca', 'P'),
(145, 'MKD', 'Macedonian denar', 'ден'),
(146, 'MGA', 'Malagasy ariary', 'Ar'),
(147, 'MWK', 'Malawian kwacha', 'MK'),
(148, 'MYR', 'Malaysian ringgit', 'RM'),
(149, 'MVR', 'Maldivian rufiyaa', '.ރ'),
(154, 'MUR', 'Mauritian rupee', '₨'),
(155, 'MXN', 'Mexican peso', '$'),
(156, 'MXN', 'Micronesian dollar', '$'),
(158, 'MDL', 'Moldovan leu', 'L'),
(160, 'MNT', 'Mongolian tögrög', '₮'),
(164, 'MZN', 'Mozambican metical', 'MT'),
(165, 'MMK', 'Burmese kyat', 'Ks'),
(166, 'AMD', 'Armenian dram', ''),
(167, '', 'Nagorno-Karabakh dram', 'դր.'),
(168, 'NAD', 'Namibian dollar', '$'),
(171, '', 'Nauruan dollar', '$'),
(172, 'NPR', 'Nepalese rupee', '₨'),
(176, 'NIO', 'Nicaraguan córdoba', 'C$'),
(178, 'NGN', 'Nigerian naira', '₦'),
(180, '', 'Niue dollar', '$'),
(182, 'NOK', 'Norwegian krone', 'kr'),
(183, 'OMR', 'Omani rial', 'ر.ع.'),
(184, 'PKR', 'Pakistani rupee', '₨'),
(185, '', 'Palauan dollar', '$'),
(187, 'ILS', 'Israeli new shekel', '₪'),
(188, 'JOD', 'Jordanian dinar', 'د.ا'),
(189, 'PAB', 'Panamanian balboa', 'B/.'),
(191, 'PGK', 'Papua New Guinean kina', 'K'),
(192, 'PYG', 'Paraguayan guaraní', '₲'),
(193, 'PEN', 'Peruvian nuevo sol', 'S/.'),
(194, 'PHP', 'Philippine peso', '₱'),
(195, 'NZD', 'New Zealand dollar', '$'),
(196, 'PID', 'Pitcairn Islands dollar', '$'),
(197, 'PLN', 'Polish złoty', 'zł'),
(199, 'QAR', 'Qatari riyal', 'ر.ق'),
(200, 'RON', 'Romanian leu', 'lei'),
(202, 'RWF', 'Rwandan franc', 'Fr'),
(204, 'DZD', 'Algerian dinar', 'د.ج'),
(205, 'MRO', 'Mauritanian ouguiya', 'UM'),
(206, 'MAD', 'Moroccan dirham', 'د. م.'),
(207, 'PTA', 'Sahrawi peseta', 'Ptas'),
(211, 'XCD', 'East Caribbean dollar', '$'),
(212, 'WST', 'Samoan tālā', 'T'),
(214, 'STD', 'São Tomé and Príncipe dobra', 'Db'),
(215, 'SAR', 'Saudi riyal', 'ر.س'),
(217, 'RSD', 'Serbian dinar', 'дин. or din.'),
(218, 'SCR', 'Seychellois rupee', '₨'),
(219, 'SLL', 'Sierra Leonean leone', 'Le'),
(220, 'BND', 'Brunei dollar', '$'),
(221, 'SGD', 'Singapore dollar', '$'),
(223, 'ANG', 'Netherlands Antillean guilder', 'ƒ'),
(226, 'SBD', 'Solomon Islands dollar', '$'),
(227, 'SOS', 'Somali shilling', 'Sh'),
(228, '', 'Somaliland shilling', 'Sh'),
(231, '', 'South Georgia and the South Sandwich Islands pound', '£'),
(234, 'SSP', 'South Sudanese pound', '£'),
(235, 'LKR', 'Sri Lankan rupee', 'Rs'),
(236, 'SDG', 'Sudanese pound', 'ج.س.'),
(237, 'SRD', 'Surinamese dollar', '$'),
(238, 'SZL', 'Swazi lilangeni', 'L'),
(239, 'SEK', 'Swedish krona', 'kr'),
(240, 'CHF', 'Swiss franc', 'Fr'),
(241, 'SYP', 'Syrian pound', '£ or ل.س'),
(242, 'TWD', 'New Taiwan dollar', '$'),
(243, 'TJS', 'Tajikistani somoni', 'ЅМ'),
(244, 'TZS', 'Tanzanian shilling', 'Sh'),
(245, 'THB', 'Thai baht', '฿'),
(246, 'XOF', 'West African CFA franc', 'Fr'),
(247, 'TOP', 'Tongan paʻanga', 'T$'),
(248, '', 'Transnistrian ruble', 'р.'),
(249, 'TTD', 'Trinidad and Tobago dollar', '$'),
(250, 'SHP', 'Saint Helena pound', '£'),
(251, '', 'Tristan da Cunha pound', '£'),
(252, 'TND', 'Tunisian dinar', 'د.ت'),
(253, 'TRY', 'Turkish lira', ''),
(254, 'TMT', 'Turkmenistan manat', 'm'),
(256, 'AUD', 'Australian dollar', '$'),
(257, '', 'Tuvaluan dollar', '$'),
(258, 'UGX', 'Ugandan shilling', 'Sh'),
(259, 'UAH', 'Ukrainian hryvnia', '₴'),
(260, 'RUB', 'Russian ruble', ''),
(261, 'AED', 'United Arab Emirates dirham', 'د.إ'),
(264, 'UYU', 'Uruguayan peso', '$'),
(265, 'UZS', 'Uzbekistani som', ''),
(266, 'VUV', 'Vanuatu vatu', 'Vt'),
(268, 'VEF', 'Venezuelan bolívar', 'Bs F'),
(269, 'VND', 'Vietnamese đồng', '₫'),
(270, 'XPF', 'CFP franc', 'Fr'),
(271, 'YER', 'Yemeni rial', '﷼'),
(272, 'ZMW', 'Zambian kwacha', 'ZK'),
(273, 'BWP', 'Botswana pula', 'P'),
(274, 'GBP', 'British pound', '£'),
(275, 'EUR', 'Euro', '€'),
(276, 'INR', 'Indian rupee', '₹'),
(277, 'ZAR', 'South African rand', 'R'),
(278, 'USD', 'United States dollar', '$'),
(279, '', '(Zimbabwean Bond)', '');
$$
delimiter $$
INSERT INTO customer VALUES
(7, 'POS Default Customer', '+96700000000', 224, '', 'Dubai', '');
$$
delimiter $$
INSERT INTO lk_payment_type VALUES
(1, 'Cach'),
(2, 'Credit Card'),
(3, 'Cheque'),
(4, 'Bank Transfer'),
(5, 'Wistern Union');
$$
delimiter $$
INSERT INTO lk_tax_type VALUES
(1, 'Exclusive'),
(2, 'Inclusive');
$$
delimiter $$
INSERT INTO lk_discounttype VALUES
(1, 'Percent %'),
(2, 'Fixed');
$$
delimiter $$
INSERT INTO product VALUES
(66, 'Box of Tea (500 GR)', '10001', 36, NULL, 7, 2.000, 2.200, 6, 11, 11, 200, NULL, 1, '', '', '[{"name":"files\\/black-Tea (1)_jn9scteu.jpg","usrName":"black-Tea (1).jpg","size":45655,"type":"image\\/jpeg","thumbnail":"files\\/thblack-Tea (1)_yw3u9vyh.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":19696,"searchStr":"black-Tea (1).jpg,!:sStrEnd"}]', 1, 1, 2.000, NULL),
(67, '5 Liters of Semi-Skimmed Milk', '10002', 35, NULL, 7, 3.000, 3.700, 7, 10, 10, NULL, NULL, 1, '', '', '[{"name":"files\\/5-Liters-of-Semi-Skimmed-Milk (1)_nyu2ga1j.jpg","usrName":"5-Liters-of-Semi-Skimmed-Milk (1).jpg","size":26163,"type":"image\\/jpeg","thumbnail":"files\\/th5-Liters-of-Semi-Skimmed-Milk (1)_9vke4wam.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":12227,"searchStr":"5-Liters-of-Semi-Skimmed-Milk (1).jpg,!:sStrEnd"}]', 1, 1, 3.000, NULL),
(68, '500g Pack of Chicken Breast', '10003', 33, NULL, 7, 0.500, 2.200, 6, 9, 9, 10, NULL, 1, '', '', '[{"name":"files\\/breast_azphdrcn.jpg","usrName":"breast.jpg","size":16403,"type":"image\\/jpeg","thumbnail":"files\\/thbreast_3e5ive78.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":9495,"searchStr":"breast.jpg,!:sStrEnd"}]', 1, 1, 0.500, NULL),
(69, 'Pack of Cod', '10004', 33, NULL, 7, 5.000, 6.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/Cod_e71w847j.jpg","usrName":"Cod.jpg","size":26403,"type":"image\\/jpeg","thumbnail":"files\\/thCod_u9kexzkl.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":11116,"searchStr":"Cod.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(70, 'Pack of Salmon', '10005', 33, NULL, 7, 12.000, 15.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/Salamon_dkfnjfr3.jpg","usrName":"Salamon.jpg","size":31435,"type":"image\\/jpeg","thumbnail":"files\\/thSalamon_ukt8voyi.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":15558,"searchStr":"Salamon.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(71, 'Peanut butter', '10006', 34, NULL, 7, 14.000, 12.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/Peanut-butter_64nyuvvu.jpg","usrName":"Peanut-butter.jpg","size":22799,"type":"image\\/jpeg","thumbnail":"files\\/thPeanut-butter_15gyv6b8.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":15653,"searchStr":"Peanut-butter.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(72, 'Pack of Oatmeal', '10007', 34, NULL, 7, 5.000, 7.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/Oatmeal_hd23b0ik.jpg","usrName":"Oatmeal.jpg","size":38974,"type":"image\\/jpeg","thumbnail":"files\\/thOatmeal_5lx9blee.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":26627,"searchStr":"Oatmeal.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(73, 'Pack of Rice Cakes', '10008', 34, NULL, 7, 15.000, 18.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/pack-of-rice-cakes_61415ox0.jpg","usrName":"pack-of-rice-cakes.jpg","size":22121,"type":"image\\/jpeg","thumbnail":"files\\/thpack-of-rice-cakes_z6cyglvk.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":13608,"searchStr":"pack-of-rice-cakes.jpg,!:sStrEnd"}]', 1, 1, 15.000, NULL),
(74, '1kg of Greek yogurt', '10009', 41, NULL, 7, 1.300, 2.500, 5, 8, 8, NULL, NULL, 1, '', '', '[{"name":"files\\/Greek-yogurt_tluxlnfd.jpg","usrName":"Greek-yogurt.jpg","size":13819,"type":"image\\/jpeg","thumbnail":"files\\/thGreek-yogurt_ap95etj4.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":8587,"searchStr":"Greek-yogurt.jpg,!:sStrEnd"}]', 1, 1, 1.300, NULL),
(75, 'Pack of 30 Eggs', '10010', 40, NULL, 7, 3.000, 2.000, 6, 9, 9, NULL, NULL, 1, '', '', '[{"name":"files\\/pack-of-30-eggs_eu992zuy.jpg","usrName":"pack-of-30-eggs.jpg","size":22178,"type":"image\\/jpeg","thumbnail":"files\\/thpack-of-30-eggs_i6gbjho0.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":11332,"searchStr":"pack-of-30-eggs.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(76, 'Pasta', '10011', 36, NULL, 7, 20.000, 25.000, 5, 5, 5, NULL, NULL, 1, '', '', '[{"name":"files\\/Pasta_23btw357.jpg","usrName":"Pasta.jpg","size":38379,"type":"image\\/jpeg","thumbnail":"files\\/thPasta_4cldlqa1.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":18494,"searchStr":"Pasta.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(77, 'Somali Banana', '10012', 37, NULL, 7, 1.000, 1.500, 5, 5, 5, NULL, NULL, 1, '', '', '[{"name":"files\\/Bananass-large_r3fv1qkl.jpg","usrName":"Bananass-large.jpg","size":15754,"type":"image\\/jpeg","thumbnail":"files\\/thBananass-large_xvtec3xp.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":7878,"searchStr":"Bananass-large.jpg,!:sStrEnd"}]', 1, 1, 1.000, NULL),
(78, 'Red Apple', '10013', 37, NULL, 7, 0.900, 1.300, 5, 5, 5, NULL, NULL, 1, '', '', '[{"name":"files\\/CONC-REDAPPLE-2_49vk6iga.jpg","usrName":"CONC-REDAPPLE-2.jpg","size":17789,"type":"image\\/jpeg","thumbnail":"files\\/thCONC-REDAPPLE-2_8u6ti30r.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":11551,"searchStr":"CONC-REDAPPLE-2.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(79, '1 Bag of Mandarins', '10014', 37, NULL, 7, 2.000, 2.800, 6, 14, 14, NULL, NULL, 1, '', '', '[{"name":"files\\/1-bag-of-mandarins_xsv6m662.jpg","usrName":"1-bag-of-mandarins.jpg","size":33707,"type":"image\\/jpeg","thumbnail":"files\\/th1-bag-of-mandarins_tzqlt0gc.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":14496,"searchStr":"1-bag-of-mandarins.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(80, '1 Packs of Frozen Vegetable', '10015', 38, NULL, 7, 2.300, 2.850, 6, 14, 14, NULL, NULL, 1, '', '', '[{"name":"files\\/packs-of-frozen-vegetable_hvi9stvz.jpg","usrName":"packs-of-frozen-vegetable.jpg","size":42973,"type":"image\\/jpeg","thumbnail":"files\\/thpacks-of-frozen-vegetable_oz0nf6wu.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":27956,"searchStr":"packs-of-frozen-vegetable.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(81, '1 Tube of Toothpaste', '10016', 39, NULL, 7, 5.000, 5.990, 6, 16, 16, NULL, NULL, 1, '', '', '[{"name":"files\\/20150425064948Toothpaste_Tubes_cbg77h7z.jpg","usrName":"20150425064948Toothpaste_Tubes.jpg","size":22395,"type":"image\\/jpeg","thumbnail":"files\\/th20150425064948Toothpaste_Tubes_2va6zpkp.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":6327,"searchStr":"20150425064948Toothpaste_Tubes.jpg,!:sStrEnd"}]', 1, 1, 5.000, NULL),
(82, 'Smile Tissues 150', '10017', 39, NULL, 7, 0.800, 1.000, 6, 11, 11, NULL, NULL, 1, '', '', '[{"name":"files\\/11116-05_0gxd8w3x.jpg","usrName":"11116-05.jpg","size":411087,"type":"image\\/jpeg","thumbnail":"files\\/th11116-05_t9s7zgys.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":16389,"searchStr":"11116-05.jpg,!:sStrEnd"}]', 1, 1, NULL, NULL),
(83, 'Package Meat With Milk', '100112', 34, 6, 7, 0.000, 50.000, 4, 6, 6, 50, 12.000, 1, '', '', '[{"name":"files\\/1_cpc3198c.jpg","usrName":"1.jpg","size":3773408,"type":"image\\/jpeg","thumbnail":"files\\/th1_tolmt68w.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":14314,"searchStr":"1.jpg,!:sStrEnd"}]', 1, 3, NULL, 1),
(84, 'Package Meat With Milk', '100101', 42, 6, NULL, 37.000, 12.200, 4, 6, 6, 50, NULL, 1, '', '', '[{"name":"files\\/WhatsApp Image 2023-01-04 at 1.09.01 PM (2)_0tmgdf62.jpeg","usrName":"WhatsApp Image 2023-01-04 at 1.09.01 PM (2).jpeg","size":28111,"type":"image\\/jpeg","thumbnail":"files\\/thWhatsApp Image 2023-01-04 at 1.09.01 PM (2)_zd0kirk8.jpeg","thumbnail_type":"image\\/jpeg","thumbnail_size":10595,"searchStr":"WhatsApp Image 2023-01-04 at 1.09.01 PM (2).jpeg,!:sStrEnd"}]', 1, 3, NULL, 1),
(85, 'New Composite Product', '100201', 39, NULL, 7, 0.000, 50.000, 6, 7, 7, NULL, NULL, 1, '', '', '[{"name":"files\\/WhatsApp Image 2023-01-04 at 1.08.59 PM_tdvsrkli.jpeg","usrName":"WhatsApp Image 2023-01-04 at 1.08.59 PM.jpeg","size":52908,"type":"image\\/jpeg","thumbnail":"files\\/thWhatsApp Image 2023-01-04 at 1.08.59 PM_23bglds8.jpeg","thumbnail_type":"image\\/jpeg","thumbnail_size":20051,"searchStr":"WhatsApp Image 2023-01-04 at 1.08.59 PM.jpeg,!:sStrEnd"}]', 1, 3, NULL, 1),
(86, 'Water Filter 600 Gallon', '1001011', 33, NULL, 7, 0.000, 3000.000, 6, 9, 9, 30, NULL, 2, '', '', '[{"name":"files\\/Untitled_qhjmq6ap.png","usrName":"Untitled.png","size":83962,"type":"image\\/png","thumbnail":"files\\/thUntitled_pvdt60xo.png","thumbnail_type":"image\\/png","thumbnail_size":56053,"searchStr":"Untitled.png,!:sStrEnd"}]', 1, 3, NULL, 1),
(87, 'Water Filter 400 Gallon', '1001012', 36, NULL, NULL, 0.000, 2000.000, 6, 7, 7, NULL, NULL, 2, '', '', '[{"name":"files\\/IMG_5154_7g8saane.jpeg","usrName":"IMG_5154.jpeg","size":4122237,"type":"image\\/jpeg","thumbnail":"files\\/thIMG_5154_jjkqlkag.jpeg","thumbnail_type":"image\\/jpeg","thumbnail_size":15617,"searchStr":"IMG_5154.jpeg,!:sStrEnd"}]', 1, 3, NULL, 1),
(88, 'Package Meat With Milk', '100014', 38, 5, 7, 7.000, 12.200, 5, 5, 5, 12, 5.000, 2, '', '', '[{"name":"files\\/5_jktpden1.jpg","usrName":"5.jpg","size":3624873,"type":"image\\/jpeg","thumbnail":"files\\/th5_17dnazsk.jpg","thumbnail_type":"image\\/jpeg","thumbnail_size":9678,"searchStr":"5.jpg,!:sStrEnd"}]', 1, 3, NULL, 1);
$$
delimiter $$
INSERT INTO lk_baseunit VALUES
(4, 'Length'),
(5, 'Mass'),
(6, 'Amount'),
(7, 'Volume');
$$
delimiter $$
INSERT INTO system_settings VALUES
(2, 'Sticky Tec Company', 278, 'info@sticky-tec.com', '[{"name":"files\\/STICKY-LOGO-DARK-LETTERS_8du6p5an.png","usrName":"STICKY-LOGO-DARK-LETTERS.png","size":6396,"type":"image\\/png","thumbnail":"files\\/thSTICKY-LOGO-DARK-LETTERS_1o4i6k22.png","thumbnail_type":"image\\/png","thumbnail_size":6396,"searchStr":"STICKY-LOGO-DARK-LETTERS.png,!:sStrEnd"}]', 'This is Footer', 9, 7, '', 'United Arab Emirate - Dubai', '', '1', '1', '1', '1', '1', '1', '1', '', '', '1', '10.0.18.180', '587', '', '', 'tls', 'xxxxxxxxxxxxx', 'xxxxxxxxxxxx', '0096279533486', NULL, '00967000000000', NULL, 'No', 'localhost', 3306, 'root', '', 'C:\\\\xampp\\\\mysql\\\\bin\\\\', 1, '', 'stickyshopdb');
$$
delimiter $$
INSERT INTO stickyshop_users VALUES
(1, 'admin', '123', 'john.doe@gmail.com', 'John Doe', NULL, 1, 1, NULL, NULL, NULL, '0099999999999', '[{"name":"files\\/th-3327020480_odq07pil.png","usrName":"th-3327020480.png","size":14576,"type":"image\\/png","searchStr":"th-3327020480.png,!:sStrEnd"}]');
$$
delimiter $$
INSERT INTO stickyshop_ugrights VALUES
('adjustement', -1, 'AEDSP', ''),
('backup', -1, 'SP', ''),
('composite_product', -1, 'AEDSP', ''),
('customer', -1, 'AEDSPI', ''),
('customer', 5, 'AEDSPI', ''),
('expenses', -1, 'AEDSPI', ''),
('expenses_category', -1, 'AEDSPI', ''),
('expensive', -1, 'AEDSPI', ''),
('expensive_category', -1, 'AEDSPI', ''),
('lk_barcode_symbology', -1, 'AEDSPI', ''),
('lk_baseunit', -1, 'AEDSPI', ''),
('lk_brand', -1, 'AEDSPI', ''),
('lk_brand', 6, 'AEDSPI', ''),
('lk_category', -1, 'AEDSPI', ''),
('lk_category', 6, 'AEDSPI', ''),
('lk_country', -1, 'ADESPIM', NULL),
('lk_currency', -1, 'AEDSPI', ''),
('lk_products_sell_status', -1, 'AEDSPI', ''),
('lk_product_variant', -1, 'AEDSPI', ''),
('lk_product_variant', 6, 'AEDSPI', ''),
('lk_services', -1, 'AEDSP', ''),
('lk_template', -1, 'E', ''),
('lk_unit', -1, 'AEDSPI', ''),
('lk_warehouse', -1, 'ADESPIM', NULL),
('MainDashboard', -1, 'S', ''),
('MainDashboard', 5, 'S', ''),
('MainDashboard', 6, 'S', ''),
('POS', -1, 'S', ''),
('POS', 5, 'S', ''),
('POS', 6, 'S', ''),
('Price And Cost', -1, 'S', ''),
('Price And Cost', 5, 'S', ''),
('Price And Cost', 6, 'S', ''),
('Price And Cost Chart', -1, 'S', ''),
('product', -1, 'AEDSPI', ''),
('product', 6, 'AEDSPI', ''),
('productavgcost', -1, 'SP', ''),
('productavgcost', 6, 'S', ''),
('productimage', -1, 'ADESP', ''),
('product_manufacturing', -1, 'AEDSPI', ''),
('product_warehouse', -1, 'AESP', ''),
('product_warehouse', 6, 'AESP', ''),
('product_warehouse Chart', -1, 'S', ''),
('product_warehouse Chart', 5, 'S', ''),
('product_warehouse Chart', 6, 'S', ''),
('profile', -1, 'ES', ''),
('profile', 5, 'ES', ''),
('profile', 6, 'ES', ''),
('profit_and_loss_Report', -1, 'SP', ''),
('purchase', -1, 'AEDSPI', ''),
('purchase', 6, 'AEDSP', ''),
('purchase_det', -1, 'AEDSPI', ''),
('purchase_det', 6, 'AEDSP', ''),
('purchase_payment', -1, 'ADESPI', ''),
('purchase_payment', 6, 'ADESP', ''),
('quotation', -1, 'AEDSP', ''),
('quotation_det', -1, 'AEDSP', ''),
('quotation_det', 5, 'AEDSP', ''),
('quotation_payment', -1, 'ADESP', ''),
('quotation_payment', 5, 'ADESP', ''),
('return_purchase', -1, 'AEDSP', ''),
('return_purchase', 6, 'AEDSP', ''),
('return_purchase_det', -1, 'AEDSPI', ''),
('return_purchase_det', 6, 'AEDSP', ''),
('return_purchase_payment', -1, 'ADESP', ''),
('return_purchase_payment', 6, 'ADESP', ''),
('return_sale', -1, 'AEDSP', ''),
('return_sale', 5, 'AEDSP', ''),
('return_sale_det', -1, 'AEDSP', ''),
('return_sale_det', 5, 'AEDSP', ''),
('return_sale_payment', -1, 'ADESP', ''),
('sale', -1, 'AEDSP', ''),
('sale', 5, 'AEDSP', ''),
('sale1', -1, 'S', ''),
('sale1', 5, 'S', ''),
('sales_payment', -1, 'AESP', ''),
('sales_payment', 5, 'AESP', ''),
('sale_det', -1, 'AEDSP', ''),
('sale_det', 5, 'AEDSP', ''),
('sale_payment', -1, 'ADSP', ''),
('sale_payment', 5, 'ADSP', ''),
('sale_service_det', -1, 'AEDS', ''),
('stickyshop_users', -1, 'ADESPIM', NULL),
('supplier', -1, 'AEDSPI', ''),
('system_settings', -1, 'E', ''),
('transfer', -1, 'AEDSP', ''),
('transfer', 6, 'AEDSP', ''),
('transfer_det', -1, 'AEDSP', ''),
('transfer_det', 5, 'AEDSP', ''),
('transfer_det', 6, 'AEDSP', ''),
('vi_customer', -1, 'SP', ''),
('vi_customer', 5, 'SP', ''),
('vi_pos_sale', -1, 'ASPI', ''),
('vi_pos_sale', 5, 'ASP', ''),
('vi_pos_sale', 6, 'ASP', ''),
('vi_pos_sale_det', -1, 'AESPI', ''),
('vi_pos_sale_det', 5, 'ASP', ''),
('vi_pos_sale_det', 6, 'ASP', ''),
('vi_pos_sale_payment', -1, 'ASP', ''),
('vi_product_total', -1, 'SPI', ''),
('vi_product_total Report', -1, 'SP', ''),
('vi_product_warehouse', -1, 'S', ''),
('vi_product_warehouse', 5, 'S', ''),
('vi_product_warehouse', 6, 'S', ''),
('vi_profil_loss_dash_total_chart', -1, 'S', ''),
('vi_profil_loss_dash_total_chart', 5, 'S', ''),
('vi_profil_loss_dash_total_chart', 6, 'S', ''),
('vi_profit_and_loss_total', -1, 'SP', ''),
('vi_purchase', -1, 'SP', ''),
('vi_purchase', 6, 'SP', ''),
('vi_purchase_payment', -1, 'SP', ''),
('vi_purchase_payment', 6, 'SP', ''),
('vi_purchase_return', -1, 'SPI', ''),
('vi_purchase_return', 6, 'SP', ''),
('vi_purchase_return_payment', -1, 'SP', ''),
('vi_purchase_return_payment', 6, 'SP', ''),
('vi_quantityalerts', -1, 'SP', ''),
('vi_quantityalerts', 5, 'SP', ''),
('vi_quantityalerts', 6, 'SP', ''),
('vi_return_sale_payment', -1, 'SP', ''),
('vi_return_sale_payment', 5, 'SP', ''),
('vi_sale', -1, 'SPI', ''),
('vi_sale', 5, 'SP', ''),
('vi_sale_payment', -1, 'SP', ''),
('vi_sale_payment', 5, 'SP', ''),
('vi_sale_return', -1, 'SP', ''),
('vi_sale_return', 5, 'SP', ''),
('vi_supplier', -1, 'SP', ''),
('vi_supplier', 6, 'SP', ''),
('vi_top_customer Chart', -1, 'S', ''),
('vi_top_customer Chart', 5, 'S', ''),
('vi_top_customer Chart', 6, 'S', ''),
('vi_top_sell_product_month', -1, 'SPI', ''),
('vi_top_sell_product_month', 5, 'S', ''),
('vi_top_sell_product_month', 6, 'S', ''),
('vi_top_sell_product_year', -1, 'S', ''),
('vi_top_sell_product_year', 5, 'S', ''),
('vi_top_sell_product_year', 6, 'S', ''),
('vi_total_paid_per_week', -1, 'S', ''),
('vi_total_paid_per_week', 5, 'S', ''),
('vi_total_paid_per_week', 6, 'S', ''),
('vi_total_paid_rep', -1, 'S', ''),
('vi_total_paid_rep', 5, 'S', ''),
('vi_total_paid_rep', 6, 'S', ''),
('WarehouseReport', -1, 'S', ''),
('WarehouseReport', 6, 'S', '');
$$
delimiter $$
INSERT INTO stickyshop_ugmembers VALUES
('admin', -1);
$$
delimiter $$
INSERT INTO stickyshop_uggroups VALUES
(5, 'Sales'),
(6, 'Purchases');
$$
delimiter $$
INSERT INTO lk_unit VALUES
(5, 'Kilogram', 'KG', 5),
(6, 'Meter', 'M', 4),
(7, 'Mole', 'Mol', 6),
(8, 'Gram', 'GR', 5),
(9, 'Package', 'Pack', 6),
(10, 'Liter', 'Liter', 7),
(11, 'Box', 'Box', 6),
(12, 'Carton', 'Carton', 6),
(13, 'Can', 'can', 6),
(14, 'Bag', 'Bag', 6),
(15, 'Bottle', 'Bottle', 6),
(16, 'Tube', 'Tube', 6);
$$
delimiter $$
INSERT INTO `lk_template` (`id`, `purchase_email`, `purchase_sms`, `sale_email`, `sale_sms`, `return_purchase_email`, `return_purchase_sms`, `return_sale_email`, `return_sale_sms`, `quatation_email`, `quatation_sms`) VALUES
(1, '[CompanyName]
\r\n\r\n[logo]
\r\n\r\nDear [Supplier]
\r\n\r\nYou have send purchese order [REFERENCE] for:-
\r\n\r\n[purchaseDetails]
\r\n\r\nAnd Payment Status for order [PaymentStatus] :-
\r\n\r\n[purchasePayments]
\r\n\r\nAnd Purchase Status is [purchaseStatus]
\r\n\r\nPaid:[Paid]
\r\nDUE:[DUE]
\r\nGrandTotal:[GrandTotal]
\r\nDate:[DATE]
\r\n', '[CompanyName]\r\nDear [Supplier]\r\nYou have send purchese order [REFERENCE] \r\nAnd Payment Status for order [PaymentStatus]\r\nAnd Purchase Status is [PurchaseStatus]\r\nPaid:[Paid]\r\nDUE:[DUE]\r\nGrandTotal:[GrandTotal]\r\nDate:[DATE]', '[CompanyName]
\r\n\r\n[logo]
\r\n\r\nDear [customer]
\r\n\r\nYou have recive sales order [REFERENCE] for:-
\r\n\r\n[saleDetails]
\r\n\r\nAnd Payment Status for order [PaymentStatus] :-
\r\n\r\n[salePayments]
\r\n\r\nAnd Purchase Status is [saleStatus]
\r\n\r\nPaid:[Paid]
\r\nDUE:[DUE]
\r\nGrandTotal:[GrandTotal]
\r\nDate:[DATE]
\r\n', '[CompanyName]\r\nDear [Supplier]\r\nYou have sale order [REFERENCE] \r\nAnd Payment Status for order [PaymentStatus]\r\nAnd Purchase Status is [saleStatus]\r\nPaid:[Paid]\r\nDUE:[DUE]\r\nGrandTotal:[GrandTotal]\r\nDate:[DATE]', '[CompanyName]
\r\n\r\n[logo]
\r\n\r\nDear [Supplier]
\r\n\r\nYou have send return purchese order [REFERENCE] for:-
\r\n\r\n[return_purchaseDetails]
\r\n\r\nAnd Payment Status for order [PaymentStatus] :-
\r\n\r\n[return_purchasePayments]
\r\n\r\nAnd Purchase Status is [return_purchaseStatus]
\r\n\r\nPaid:[Paid]
\r\nDUE:[DUE]
\r\nGrandTotal:[GrandTotal]
\r\nDate:[DATE]
\r\n', '[CompanyName]\r\nDear [Supplier]\r\nYou have send return purchese order [REFERENCE] \r\nAnd Payment Status for order [PaymentStatus]\r\nAnd Purchase Status is [return_purchaseStatus]\r\nPaid:[Paid]\r\nDUE:[DUE]\r\nGrandTotal:[GrandTotal]\r\nDate:[DATE]', '[CompanyName]
\r\n\r\n[logo]
\r\n\r\nDear [customer]
\r\n\r\nYou have recive sales order [REFERENCE] for:-
\r\n\r\n[return_saleDetails]
\r\n\r\nAnd Payment Status for order [PaymentStatus] :-
\r\n\r\n[return_salePayments]
\r\n\r\nAnd Purchase Status is [return_saleStatus]
\r\n\r\nPaid:[Paid]
\r\nDUE:[DUE]
\r\nGrandTotal:[GrandTotal]
\r\nDate:[DATE]
\r\n', '[CompanyName]\r\nDear [Supplier]\r\nYou have sale order [REFERENCE] \r\nAnd Payment Status for order [PaymentStatus]\r\nAnd Purchase Status is [return_saleStatus]\r\nPaid:[Paid]\r\nDUE:[DUE]\r\nGrandTotal:[GrandTotal]\r\nDate:[DATE]', '[CompanyName]
\r\n\r\n[logo]
\r\n\r\nDear [customer]
\r\n\r\nYou have send Quotation order [REFERENCE] for:-
\r\n\r\n[quotationDetails]
\r\n\r\nAnd Payment Status for order [PaymentStatus] :-
\r\n\r\n[quotationPayments]
\r\n\r\nAnd Purchase Status is [quotationStatus]
\r\n\r\nPaid:[Paid]
\r\nDUE:[DUE]
\r\nGrandTotal:[GrandTotal]
\r\nDate:[DATE]
\r\n', '[CompanyName]\r\nDear [Supplier]\r\nYou have sale order [REFERENCE] \r\nAnd Payment Status for order [PaymentStatus]\r\nAnd Purchase Status is [quotationStatus]\r\nPaid:[Paid]\r\nDUE:[DUE]\r\nGrandTotal:[GrandTotal]\r\nDate:[DATE]');
$$
delimiter $$
INSERT INTO lk_product_variant VALUES
(7, 'Black'),
(8, 'Red'),
(9, 'X-large'),
(10, 'Small'),
(11, 'Large');
$$
delimiter $$
INSERT INTO lk_messages VALUES
('1', 'لا يمكنك حذف العميل الرجاء التحقق من فواتير المبيعات', 'Arabic'),
('10', 'لا يمكنك حذف المستودع الرجاء التحقق من فواتير مردودات المشتريات', 'Arabic'),
('11', 'الرجاء اختيار العميل', 'Arabic'),
('12', 'الرجاء اختيار المستودع', 'Arabic'),
('13', 'المنتج موجود الرجاء التحقق', 'Arabic'),
('14', 'لا يمكن حفظ الفاتورة مساوية للصفر', 'Arabic'),
('15', 'الكمية أكثر من المخزون المتاح', 'Arabic'),
('16', 'لا يمكنك حذف المنتج الرجاء التحقق من فواتير المشتريات', 'Arabic'),
('17', 'لا يمكنك حذف المنتج الرجاء التحقق من فواتير مردودات المشتريات', 'Arabic'),
('18', 'لا يمكنك حذف المنتج الرجاء التحقق من فواتير المبيعات', 'Arabic'),
('19', 'لا يمكنك حذف المنتج الرجاء التحقق من فواتيرمردودات المبيعات', 'Arabic'),
('2', 'لا يمكنك حذف العميل الرجاء التحقق من فواتير مردودات المبيعات', 'Arabic'),
('20', 'لا يمكنك حذف المنتج الرجاء التحقق من العروض', 'Arabic'),
('21', 'لا يمكنك حذف او تعديل فاتروة تم الدفع عليها', 'Arabic'),
('3', 'لا يمكنك حذف المستودع الرجاء التحقق من فواتير المبيعات', 'Arabic'),
('4', 'لا يمكنك حذف المستودع الرجاء التحقق من فواتير مردودات المبيعات', 'Arabic'),
('5', 'لا يمكنك حذف العميل الرجاء التحقق من فواتير العروض', 'Arabic'),
('6', 'لا يمكنك حذف المستودع الرجاء التحقق من فواتير العروض', 'Arabic'),
('7', 'لا يمكنك حذف المورد الرجاء التحقق من فواتير المشتريات', 'Arabic'),
('8', 'لا يمكنك حذف المورد الرجاء التحقق من فواتير مردودات المشتريات', 'Arabic'),
('9', 'لا يمكنك حذف المستودع الرجاء التحقق من فواتير المشريات', 'Arabic'),
('1', 'You cannot delete the customer, please check the sales invoices', 'English'),
('10', 'You cannot delete the warehouse. Please check the purchase returns invoices', 'English'),
('11', 'Please select a customer', 'English'),
('12', 'Please select a warehouse', 'English'),
('13', 'The Product Already Exists Please Check', 'English'),
('14', 'The invoice cannot be equal to zero', 'English'),
('15', 'The Quntity Is more than available stock', 'English'),
('16', 'You cannot delete the product. Please check the purchase invoices', 'English'),
('17', 'You cannot delete the product. Please check the purchase returns invoices', 'English'),
('18', 'You cannot delete the product. Please check the sales invoices', 'English'),
('19', 'You cannot delete the product, please check the sales return invoices', 'English'),
('2', 'You cannot delete the customer, please check the sales return invoices', 'English'),
('20', 'You cannot delete the product, please check the Quotations', 'English'),
('21', 'You can''t update or delete invoice already paid', 'English'),
('3', 'You cannot delete the warehouse. Please check the sales invoices', 'English'),
('4', 'You cannot delete the warehouse, please check the sales return invoices', 'English'),
('5', 'You cannot delete the customer, please check the invoices for the Quotations', 'English'),
('6', 'You cannot delete the repository, please check the invoices for the Quotations', 'English'),
('7', 'You cannot delete the supplier. Please check the purchase invoices', 'English'),
('8', 'You cannot delete the supplier. Please check the purchase returns invoices', 'English'),
('9', 'You cannot delete the warehouse. Please check the purchase invoices', 'English'),
('1', 'Vous ne pouvez pas supprimer le client, veuillez vérifier les factures de vente', 'French'),
('10', 'Vous ne pouvez pas supprimer l''entrepôt. Veuillez vérifier les factures de retour d''achat', 'French'),
('11', 'Veuillez sélectionner un client', 'French'),
('12', 'Veuillez sélectionner un entrepôt', 'French'),
('13', 'Le produit existe déjà, veuillez vérifier', 'French'),
('14', 'La facture ne peut pas être égale à zéro', 'French'),
('15', 'La quantité est supérieure au stock disponible', 'French'),
('16', 'Vous ne pouvez pas supprimer le produit, veuillez vérifier les factures d''achat', 'French'),
('17', 'Vous ne pouvez pas supprimer le produit. Veuillez vérifier les factures de retour d''achat', 'French'),
('18', 'Vous ne pouvez pas supprimer le produit. Veuillez vérifier les factures de vente', 'French'),
('19', 'Vous ne pouvez pas supprimer le produit, veuillez vérifier les factures de retour des ventes', 'French'),
('2', 'Vous ne pouvez pas supprimer le client, veuillez vérifier les factures de retour des ventes', 'French'),
('20', 'Vous ne pouvez pas supprimer le produit, veuillez vérifier les devis', 'French'),
('21', 'Vous ne pouvez pas mettre à jour ou supprimer une facture déjà payée', 'French'),
('3', 'Vous ne pouvez pas supprimer l''entrepôt. Veuillez vérifier les factures de vente', 'French'),
('4', 'Vous ne pouvez pas supprimer l''entrepôt, veuillez vérifier les factures de retour des ventes', 'French'),
('5', 'Vous ne pouvez pas supprimer le client, veuillez vérifier les factures pour les devis', 'French'),
('6', 'Vous ne pouvez pas supprimer le référentiel, veuillez vérifier les factures pour les devis', 'French'),
('7', 'Vous ne pouvez pas supprimer le fournisseur. Veuillez vérifier les factures d''achat', 'French'),
('8', 'Vous ne pouvez pas supprimer le fournisseur. Veuillez vérifier les factures de retour d''achat', 'French'),
('9', 'Vous ne pouvez pas supprimer l''entrepôt. Veuillez vérifier les factures d''achat', 'French'),
('1', 'No puede eliminar el cliente, verifique las facturas de venta', 'Spanish'),
('10', 'No puede eliminar el almacén. Por favor, consulte las facturas de devoluciones de compra', 'Spanish'),
('11', 'Por favor seleccione un cliente', 'Spanish'),
('12', 'Por favor seleccione un almacén', 'Spanish'),
('13', 'El producto ya existe Por favor verifique', 'Spanish'),
('14', 'La factura no puede ser igual a cero', 'Spanish'),
('15', 'La cantidad es más que el stock disponible', 'Spanish'),
('16', 'No puede eliminar el producto. Por favor, consulte las facturas de compra.', 'Spanish'),
('17', 'No se puede eliminar el producto, por favor revisa las facturas de devoluciones de compra', 'Spanish'),
('18', 'No puede eliminar el producto. Por favor, consulte las facturas de venta.', 'Spanish'),
('19', 'No puede eliminar el producto, verifique las facturas de devolución de ventas', 'Spanish'),
('2', 'No puede eliminar el cliente, verifique las facturas de devolución de ventas', 'Spanish'),
('20', 'No puede eliminar el producto, consulte las Cotizaciones', 'Spanish'),
('21', 'No puede actualizar o eliminar la factura ya pagada', 'Spanish'),
('3', 'No se puede eliminar el almacén, por favor verifique las facturas de venta', 'Spanish'),
('4', 'No puede eliminar el almacén, verifique las facturas de devolución de ventas', 'Spanish'),
('5', 'No puede eliminar el cliente, verifique las facturas para las Cotizaciones', 'Spanish'),
('6', 'No puede eliminar el repositorio, verifique las facturas para las Cotizaciones', 'Spanish'),
('7', 'No puede eliminar el proveedor. Por favor revise las facturas de compra', 'Spanish'),
('8', 'No puede eliminar el proveedor. Por favor, consulte las facturas de devoluciones de compra', 'Spanish'),
('9', 'No puede eliminar el almacén. Por favor revise las facturas de compra', 'Spanish'),
('30', 'المبلغ المستلم اقل من المجموع الكلي', 'Arabic'),
('30', 'The Amount Received Is Less Than The Grand Total', 'English'),
('30', 'Le montant reçu est inférieur au grand total', 'French'),
('30', 'La cantidad recibida es menor que el total general', 'Spanish'),
('31', 'لقد تم ارسال الفاتورة الى المبيعات', 'Arabic'),
('31', 'The invoice has been sent to sales', 'English'),
('31', 'La facture a été envoyée aux ventes', 'French'),
('31', 'La factura ha sido enviada a ventas.', 'Spanish'),
('32', 'لا يمكنك حذف مدفوعات الفاتورة المدفوعة بالفعل', 'Arabic'),
('32', 'You cannot delete payments for invoice already paid', 'English'),
('32', 'Vous ne pouvez pas supprimer les paiements d\'une facture déjà payée', 'French'),
('32', 'No puede borrar pagos por factura ya pagada', 'Spanish'),
('29', 'المبلغ المستلم اكبر من المبلغ المدفوع', 'Arabic'),
('29', 'The received amount is more than the paid amount', 'English'),
('29', 'Le montant reçu est supérieur au montant payé', 'French'),
('29', 'La cantidad recibida es mayor que la cantidad pagada', 'Spanish'),
('33', 'الرجاء اتمام عملية الدفع بشكل كامل', 'Arabic'),
('33', 'Please complete the payment process', 'English'),
('33', 'Veuillez compléter le processus de paiement', 'French'),
('33', 'Por favor complete el proceso de pago', 'Spanish')
;
$$
delimiter $$
INSERT INTO lk_language VALUES
(1, 'English'),
(2, 'Arabic'),
(3, 'French'),
(4, 'Spanish');
$$
delimiter $$
INSERT INTO lk_invoice_type VALUES
(1, 'Purchase'),
(2, 'Sale'),
(3, 'Return Purchase'),
(4, 'Return Sale'),
(5, 'Exprenses'),
(6, 'Quotation');
$$
delimiter $$
INSERT INTO lk_invoice_type_lang VALUES
(1, 'Purchases','English'),
(2, 'Sales','English'),
(3, 'Return Purchases','English'),
(4, 'Return Sales','English'),
(1, 'المشتريات','Arabic'),
(2, 'المبيعات','Arabic'),
(3, 'مرتجعات المشتريات','Arabic'),
(4, 'مرتجعات المبيعات','Arabic'),
(1, 'Achats','French'),
(2, 'Ventes','French'),
(3, 'Retourner les achats','French'),
(4, 'Retour des ventes','French'),
(1, 'Compras','Spanish'),
(2, 'Las ventas','Spanish'),
(3, 'Devolución de compras','Spanish'),
(4, 'Ventas de devolución','Spanish');
$$
delimiter $$
INSERT INTO lk_data_group VALUES
(1, 'show all data'),
(2, 'show user data only');
$$
delimiter $$
INSERT INTO lk_category VALUES
(32, 'GS-01', 'Groceries', NULL, 0),
(33, 'MF-01', 'Meat & fish', NULL, 0),
(34, 'BBS-01', 'Bread & bread spreads', NULL, 0),
(35, 'DAIRY-01', 'Dairy', NULL, 0),
(36, 'DR-01', 'Dry Goods', NULL, 0),
(37, 'VF-01', 'Vegetables & Fruit', NULL, 0),
(38, 'FREEZER-01', 'Freezer', NULL, 0),
(39, 'CP-01', 'Care Products', NULL, 0),
(40, 'DRE-01', 'Eggs', 35, 1),
(41, 'DRY-02', 'Yogurt', 35, 1),
(42, 'CG-01', 'Cigerats', NULL, 0);
$$
delimiter $$
INSERT INTO lk_barcode_symbology VALUES
(7, 'p1-klsa');
$$
delimiter $$
INSERT INTO lk_adjust_type VALUES
(1, 'Addition'),
(2, 'Subtraction');
$$
delimiter $$
INSERT INTO bar_code_generator VALUES
('10001');
$$
delimiter $$
INSERT INTO key_license VALUES
(1,'');
$$