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)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 137,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table supplier
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 20,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE supplier 
  ADD CONSTRAINT country_supplier_id FOREIGN KEY (country_id)
    REFERENCES lk_country(ID);

--
-- Create table lk_warehouse
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 11,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE lk_warehouse 
  ADD CONSTRAINT Country_Warehouse_ID FOREIGN KEY (Country_Id)
    REFERENCES lk_country(ID);

--
-- Create table transfer
--
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 latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 111,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE transfer 
  ADD CONSTRAINT TRANSFER_FROM_warehouse_id FOREIGN KEY (FROM_WHAREHOUSE_ID)
    REFERENCES lk_warehouse(ID) ON DELETE SET NULL;

--
-- Create foreign key
--
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
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 2,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

--
-- Create foreign key
--
ALTER TABLE stickyshop_users 
  ADD CONSTRAINT users_warehouse_id FOREIGN KEY (warehouse_id_)
    REFERENCES lk_warehouse(ID);

--
-- Create table return_purchase
--
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 latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 23,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE return_purchase 
  ADD CONSTRAINT ret_purchase_supplier_id FOREIGN KEY (Supplier_ID)
    REFERENCES supplier(id) ON DELETE SET NULL;

--
-- Create foreign key
--
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
--
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 latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 189,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE purchase 
  ADD CONSTRAINT purchase_supplier_id FOREIGN KEY (Supplier_ID)
    REFERENCES supplier(id) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE purchase 
  ADD CONSTRAINT purchase_warehouse_id FOREIGN KEY (Warehouse_ID)
    REFERENCES lk_warehouse(ID) ON DELETE SET NULL;

--
-- Create table customer
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 8,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE customer 
  ADD CONSTRAINT country_customer_id FOREIGN KEY (country_id)
    REFERENCES lk_country(ID);

--
-- Create table sale
--
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 latin1 COLLATE latin1_swedish_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,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 612,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE sale 
  ADD CONSTRAINT sale_customer_id FOREIGN KEY (customer_id)
    REFERENCES customer(id) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE sale 
  ADD CONSTRAINT sale_warehouse_id FOREIGN KEY (Warehouse_ID)
    REFERENCES lk_warehouse(ID) ON DELETE SET NULL;

--
-- Create table sale_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 1406,
AVG_ROW_LENGTH = 1489,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create index Sale_Det on table sale_det
--
ALTER TABLE sale_det 
  ADD INDEX Sale_Det(Sale_ID);

--
-- Create index Sale_DiscountType on table sale_det
--
ALTER TABLE sale_det 
  ADD INDEX Sale_DiscountType(DiscountType_ID);

--
-- Create index Sale_Product_Id on table sale_det
--
ALTER TABLE sale_det 
  ADD INDEX Sale_Product_Id(Product_ID);

--
-- Create index Sale_TaxType on table sale_det
--
ALTER TABLE sale_det 
  ADD INDEX Sale_TaxType(TaxType);

--
-- Create foreign key
--
ALTER TABLE sale_det 
  ADD CONSTRAINT sale_sale_det_id FOREIGN KEY (Sale_ID)
    REFERENCES sale(ID);

--
-- Create table quotation
--
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 latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 10,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE quotation 
  ADD CONSTRAINT Quotation_warehouse_id FOREIGN KEY (Warehouse_ID)
    REFERENCES lk_warehouse(ID) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE quotation 
  ADD CONSTRAINT customer_id_quotation FOREIGN KEY (customer_id)
    REFERENCES customer(id) ON DELETE SET NULL;

--
-- Create table lk_payment_status
--
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)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1365,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_invoice_status
--
CREATE TABLE lk_invoice_status (
  ID INT(11) NOT NULL,
  Name VARCHAR(200) DEFAULT NULL,
  Language VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID, Language)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1365,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table return_sale
--
CREATE TABLE return_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(18, 3) DEFAULT NULL,
  sale_Status_Id INT(11) DEFAULT NULL,
  Note TEXT CHARACTER SET latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 18,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE return_sale 
  ADD CONSTRAINT return_sale_customer_id FOREIGN KEY (customer_id)
    REFERENCES customer(id) ON DELETE SET NULL;

--
-- Create foreign key
--
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
--
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
--
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
--
CREATE TABLE lk_products_sell_status (
  id INT(11) NOT NULL,
  status VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_brand
--
CREATE TABLE lk_brand (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Name VARCHAR(200) NOT NULL,
  Description TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  Image TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 15,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table product
--
CREATE TABLE product (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  Variant_Id TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  Image TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  BAR_CODE_AUTO INT(11) DEFAULT NULL,
  product_sell_status INT(11) DEFAULT NULL,
  AverageProductCost DECIMAL(18, 3) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 83,
AVG_ROW_LENGTH = 2184,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
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
--
ALTER TABLE product 
  ADD CONSTRAINT product_Brand_id FOREIGN KEY (Brand_Id)
    REFERENCES lk_brand(ID) ON DELETE SET NULL;

--
-- Create table productavgcost
--
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,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 70,
AVG_ROW_LENGTH = 2048,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create index IDX_productavgcost_ProductID on table productavgcost
--
ALTER TABLE productavgcost 
  ADD INDEX IDX_productavgcost_ProductID(ProductID);

--
-- Create foreign key
--
ALTER TABLE productavgcost 
  ADD CONSTRAINT FK_ProductAvgCost_ProductID FOREIGN KEY (ProductID)
    REFERENCES product(ID);

--
-- Create table lk_smsgateway
--
CREATE TABLE lk_smsgateway (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_papersize
--
CREATE TABLE lk_papersize (
  ID INT(11) NOT NULL,
  Name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 2048,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_currency
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 280,
AVG_ROW_LENGTH = 91,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table system_settings
--
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 latin1 COLLATE latin1_swedish_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 latin1 COLLATE latin1_swedish_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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  payment_stripe_secret TEXT CHARACTER SET latin1 COLLATE latin1_swedish_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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 3,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
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
--
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
--
ALTER TABLE system_settings 
  ADD CONSTRAINT Setting_PaperSize_Id FOREIGN KEY (Papersize_ID)
    REFERENCES lk_papersize(ID) ON DELETE SET NULL;

--
-- Create foreign key
--
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
--
ALTER TABLE system_settings 
  ADD CONSTRAINT smsGateWay_SystemSetting_ID FOREIGN KEY (sms_getway_id)
    REFERENCES lk_smsgateway(ID);

--
-- Create table lk_payment_type
--
CREATE TABLE lk_payment_type (
  ID INT(11) NOT NULL,
  name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table sale_payment
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  note MEDIUMTEXT DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 537,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE sale_payment 
  ADD CONSTRAINT Payment_Type_Cons_sale FOREIGN KEY (Payment_TypeId)
    REFERENCES lk_payment_type(ID);

--
-- Create foreign key
--
ALTER TABLE sale_payment 
  ADD CONSTRAINT sale_ID FOREIGN KEY (sale_ID)
    REFERENCES sale(ID) ON DELETE CASCADE;

--
-- Create table return_sale_payment
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  note MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 10,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE return_sale_payment 
  ADD CONSTRAINT return_Payment_Type_Cons_sale FOREIGN KEY (Payment_TypeId)
    REFERENCES lk_payment_type(ID);

--
-- Create foreign key
--
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
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  note TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 19,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
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
--
ALTER TABLE return_purchase_payment 
  ADD CONSTRAINT return_Payment_Type_Cons FOREIGN KEY (Payment_TypeId)
    REFERENCES lk_payment_type(ID);

--
-- Create table quotation_payment
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  note TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 8,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE quotation_payment 
  ADD CONSTRAINT Payment_Type_Cons_Quotation FOREIGN KEY (Payment_TypeId)
    REFERENCES lk_payment_type(ID);

--
-- Create foreign key
--
ALTER TABLE quotation_payment 
  ADD CONSTRAINT Quotation_ID FOREIGN KEY (Quotation_ID)
    REFERENCES quotation(ID) ON DELETE CASCADE;

--
-- Create table purchase_payment
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  note TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 79,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE purchase_payment 
  ADD CONSTRAINT Payment_Purchase_ID FOREIGN KEY (Purchase_ID)
    REFERENCES purchase(ID) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE purchase_payment 
  ADD CONSTRAINT Payment_Type_Cons FOREIGN KEY (Payment_TypeId)
    REFERENCES lk_payment_type(ID);

--
-- Create table lk_tax_type
--
CREATE TABLE lk_tax_type (
  ID INT(11) NOT NULL,
  Name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_discounttype
--
CREATE TABLE lk_discounttype (
  ID INT(11) NOT NULL,
  NAME VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table transfer_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 394,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create index Transfer_Transfer_Det on table transfer_det
--
ALTER TABLE transfer_det 
  ADD INDEX Transfer_Transfer_Det(transfer_ID);

--
-- Create foreign key
--
ALTER TABLE transfer_det 
  ADD CONSTRAINT Transfer_DiscountType FOREIGN KEY (DiscountType_ID)
    REFERENCES lk_discounttype(ID);

--
-- Create foreign key
--
ALTER TABLE transfer_det 
  ADD CONSTRAINT Transfer_Product_Id FOREIGN KEY (Product_ID)
    REFERENCES product(ID);

--
-- Create foreign key
--
ALTER TABLE transfer_det 
  ADD CONSTRAINT Transfer_Purchase_Det FOREIGN KEY (transfer_ID)
    REFERENCES transfer(ID);

--
-- Create foreign key
--
ALTER TABLE transfer_det 
  ADD CONSTRAINT Transfer_TaxType FOREIGN KEY (TaxType)
    REFERENCES lk_tax_type(ID);

--
-- Create table return_sale_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 22,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE return_sale_det 
  ADD CONSTRAINT return_Sale_Det FOREIGN KEY (return_Sale_ID)
    REFERENCES return_sale(ID);

--
-- Create foreign key
--
ALTER TABLE return_sale_det 
  ADD CONSTRAINT return_Sale_DiscountType FOREIGN KEY (DiscountType_ID)
    REFERENCES lk_discounttype(ID);

--
-- Create foreign key
--
ALTER TABLE return_sale_det 
  ADD CONSTRAINT return_Sale_Product_Id FOREIGN KEY (Product_ID)
    REFERENCES product(ID);

--
-- Create foreign key
--
ALTER TABLE return_sale_det 
  ADD CONSTRAINT return_Sale_TaxType FOREIGN KEY (TaxType)
    REFERENCES lk_tax_type(ID);

--
-- Create table return_purchase_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 24,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE return_purchase_det 
  ADD CONSTRAINT return_Purchase_DiscountType FOREIGN KEY (DiscountType_ID)
    REFERENCES lk_discounttype(ID);

--
-- Create foreign key
--
ALTER TABLE return_purchase_det 
  ADD CONSTRAINT return_Purchase_Product_Id FOREIGN KEY (Product_ID)
    REFERENCES product(ID);

--
-- Create foreign key
--
ALTER TABLE return_purchase_det 
  ADD CONSTRAINT return_Purchase_Purchase_Det FOREIGN KEY (Return_Purchase_ID)
    REFERENCES return_purchase(ID);

--
-- Create foreign key
--
ALTER TABLE return_purchase_det 
  ADD CONSTRAINT return_Purchase_TaxType FOREIGN KEY (TaxType)
    REFERENCES lk_tax_type(ID);

--
-- Create table quotation_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 14,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE quotation_det 
  ADD CONSTRAINT Quotation_Det FOREIGN KEY (Quotation_ID)
    REFERENCES quotation(ID);

--
-- Create foreign key
--
ALTER TABLE quotation_det 
  ADD CONSTRAINT Quotation_DiscountType FOREIGN KEY (DiscountType_ID)
    REFERENCES lk_discounttype(ID);

--
-- Create foreign key
--
ALTER TABLE quotation_det 
  ADD CONSTRAINT Quotation_Product_Id FOREIGN KEY (Product_ID)
    REFERENCES product(ID);

--
-- Create foreign key
--
ALTER TABLE quotation_det 
  ADD CONSTRAINT Quotation_TaxType FOREIGN KEY (TaxType)
    REFERENCES lk_tax_type(ID);

--
-- Create table purchase_det
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 92,
AVG_ROW_LENGTH = 1170,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE purchase_det 
  ADD CONSTRAINT Purchase_DiscountType FOREIGN KEY (DiscountType_ID)
    REFERENCES lk_discounttype(ID);

--
-- Create foreign key
--
ALTER TABLE purchase_det 
  ADD CONSTRAINT Purchase_Product_Id FOREIGN KEY (Product_ID)
    REFERENCES product(ID);

--
-- Create foreign key
--
ALTER TABLE purchase_det 
  ADD CONSTRAINT Purchase_Purchase_Det FOREIGN KEY (Purchase_ID)
    REFERENCES purchase(ID);

--
-- Create foreign key
--
ALTER TABLE purchase_det 
  ADD CONSTRAINT Purchase_TaxType FOREIGN KEY (TaxType)
    REFERENCES lk_tax_type(ID);

--
-- Create table lk_baseunit
--
CREATE TABLE lk_baseunit (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Name VARCHAR(200) NOT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 8,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_unit
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 17,
AVG_ROW_LENGTH = 1489,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE lk_unit 
  ADD CONSTRAINT BaseUnit_Unit_ID FOREIGN KEY (BaseUnit_ID)
    REFERENCES lk_baseunit(ID) ON DELETE SET NULL;

--
-- Create table expenses_category
--
CREATE TABLE expenses_category (
  id INT(11) NOT NULL AUTO_INCREMENT,
  category VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 5,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table expenses
--
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 latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  date DATE DEFAULT NULL,
  reference VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 8,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE expenses 
  ADD CONSTRAINT expensive_category_id FOREIGN KEY (expensive_category_id)
    REFERENCES expenses_category(id) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE expenses 
  ADD CONSTRAINT expensive_warehouse_id FOREIGN KEY (wharehouse_id)
    REFERENCES lk_warehouse(ID) ON DELETE SET NULL;

--
-- Create table stickyshop_ugrights
--
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)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 124,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

--
-- Create table stickyshop_ugmembers
--
CREATE TABLE stickyshop_ugmembers (
  UserName VARCHAR(300) NOT NULL,
  GroupID INT(11) NOT NULL,
  PRIMARY KEY (UserName(50), GroupID)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

--
-- Create table stickyshop_uggroups
--
CREATE TABLE stickyshop_uggroups (
  GroupID INT(11) NOT NULL AUTO_INCREMENT,
  Label VARCHAR(300) DEFAULT NULL,
  PRIMARY KEY (GroupID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 7,
AVG_ROW_LENGTH = 16384,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

--
-- Create table product_warehouse
--
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)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 2520,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create index product_warehouse_id on table product_warehouse
--
ALTER TABLE product_warehouse 
  ADD INDEX product_warehouse_id(product_id);

--
-- Create table lk_template
--
CREATE TABLE lk_template (
  ID INT(11) NOT NULL,
  purchase_email TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  purchase_sms TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  sale_email TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  sale_sms TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  return_purchase_email TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  return_purchase_sms TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  return_sale_email TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  return_sale_sms TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  quatation_email TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  quatation_sms TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_product_variant
--
CREATE TABLE lk_product_variant (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 12,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_messages
--
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)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 215,
CHARACTER SET utf8,
COLLATE utf8_general_ci;

--
-- Create table lk_language
--
CREATE TABLE lk_language (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 5,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_invoice_type
--
CREATE TABLE lk_invoice_type (
  id INT(11) NOT NULL,
  invoice VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_data_group
--
CREATE TABLE lk_data_group (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 6,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_category
--
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)
)
ENGINE = INNODB,
AUTO_INCREMENT = 43,
AVG_ROW_LENGTH = 2048,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create foreign key
--
ALTER TABLE lk_category 
  ADD CONSTRAINT CON_LK_MASTER_CAT FOREIGN KEY (Master_Cat)
    REFERENCES lk_category(ID) ON DELETE SET NULL;

--
-- Create table lk_barcode_symbology
--
CREATE TABLE lk_barcode_symbology (
  id INT(11) NOT NULL AUTO_INCREMENT,
  Symbology VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 8,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table lk_adjust_type
--
CREATE TABLE lk_adjust_type (
  ID INT(11) NOT NULL,
  name VARCHAR(200) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table key_license
--
CREATE TABLE key_license (
  Id INT(11) NOT NULL AUTO_INCREMENT,
  key_text VARCHAR(500) NOT NULL,
  PRIMARY KEY (Id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 2,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table dual
--
CREATE TABLE `dual` (
  x INT(11) DEFAULT NULL
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table bar_code_generator
--
CREATE TABLE bar_code_generator (
  INITAL_VAL VARCHAR(6) DEFAULT NULL
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

--
-- Create table backup
--
CREATE TABLE backup (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Backup_Date DATETIME DEFAULT NULL,
  File TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 17,
AVG_ROW_LENGTH = 1024,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

-- 
-- Dumping data for table lk_country
--
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
--
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');

-- 
-- Dumping data for table lk_invoice_status
--
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');

-- 
-- Dumping data for table supplier
--
INSERT INTO supplier VALUES
(19, 'John Doe Company', '+9xxxxxxxx', 73, 'm@m.com', 'Paris', '');

-- 
-- Dumping data for table lk_warehouse
--
INSERT INTO lk_warehouse VALUES
(9, 'Warehouse 1 ', 224, 'M@M.COM', '+971XXXXXXXXX', 'Dubai', '123366', 1),
(10, 'Warehouse 2', 187, '', '009660000000', 'Riyadh', '', 1);

-- 
-- Dumping data for table lk_products_sell_status
--
INSERT INTO lk_products_sell_status VALUES
(1, 'All'),
(2, 'Show In Purchuse'),
(3, 'Show In Sale');

-- 
-- Dumping data for table lk_brand
--
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"}]');

-- 
-- Dumping data for table transfer
--
-- Table  transfer does not contain any data (it is empty)

-- 
-- Dumping data for table lk_smsgateway
--
-- Table  lk_smsgateway does not contain any data (it is empty)

-- 
-- Dumping data for table lk_papersize
--
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)');

-- 
-- Dumping data for table lk_currency
--
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)', '');

-- 
-- Dumping data for table customer
--
INSERT INTO customer VALUES
(7, 'POS Default Customer', '+96700000000', 224, '', 'Dubai', '');



-- 
-- Dumping data for table lk_payment_type
--
INSERT INTO lk_payment_type VALUES
(1, 'Cach'),
(2, 'Credit Card'),
(3, 'Cheque'),
(4, 'Bank Transfer'),
(5, 'Wistern Union');

-- 
-- Dumping data for table purchase
--

-- 
-- Dumping data for table lk_tax_type
--
INSERT INTO lk_tax_type VALUES
(1, 'Exclusive'),
(2, 'Inclusive');

-- 
-- Dumping data for table lk_discounttype
--
INSERT INTO lk_discounttype VALUES
(1, 'Percent %'),
(2, 'Fixed');

-- 
-- Dumping data for table product
--
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,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, 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, 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),
(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),
(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),
(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),
(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, 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, 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),
(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),
(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, 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),
(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),
(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),
(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, 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);

-- 
-- Dumping data for table lk_baseunit
--
INSERT INTO lk_baseunit VALUES
(4, 'Length'),
(5, 'Mass'),
(6, 'Amount'),
(7, 'Volume');

-- 
-- Dumping data for table expenses_category
--
-- Table  expenses_category does not contain any data (it is empty)

-- 
-- Dumping data for table transfer_det
--
-- Table  transfer_det does not contain any data (it is empty)

-- 
-- Dumping data for table system_settings
--
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');

-- 
-- Dumping data for table stickyshop_users
--
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"}]');

-- 
-- Dumping data for table stickyshop_ugrights
--
INSERT INTO stickyshop_ugrights VALUES
('adjustement', -1, 'AEDSP', ''),
('backup', -1, 'SP', ''),
('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_product_variant', -1, 'AEDSPI', ''),
('lk_product_variant', 6, 'AEDSPI', ''),
('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', ''),
('productimage', -1, 'ADESP', ''),
('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, 'ADSP', ''),
('quotation', -1, 'AEDSP', ''),
('quotation_det', -1, 'AEDSP', ''),
('quotation_det', 5, 'AEDSP', ''),
('quotation_payment', -1, 'ADSP', ''),
('quotation_payment', 5, 'ADSP', ''),
('return_purchase', -1, 'AEDSP', ''),
('return_purchase', 6, 'AEDSP', ''),
('return_purchase_det', -1, 'AEDSPI', ''),
('return_purchase_det', 6, 'AEDSP', ''),
('return_purchase_payment', -1, 'ADSP', ''),
('return_purchase_payment', 6, 'ADSP', ''),
('return_sale', -1, 'AEDSP', ''),
('return_sale', 5, 'AEDSP', ''),
('return_sale_det', -1, 'AEDSP', ''),
('return_sale_det', 5, 'AEDSP', ''),
('return_sale_payment', -1, 'ADSP', ''),
('sale', -1, 'AEDSP', ''),
('sale', 5, 'AEDSP', ''),
('sale1', -1, 'S', ''),
('sale1', 5, 'S', ''),
('sale_det', -1, 'AEDSP', ''),
('sale_det', 5, 'AEDSP', ''),
('sale_payment', -1, 'ADSP', ''),
('sale_payment', 5, 'ADSP', ''),
('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_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', '');

-- 
-- Dumping data for table stickyshop_ugmembers
--
INSERT INTO stickyshop_ugmembers VALUES
('admin', -1);

-- 
-- Dumping data for table stickyshop_uggroups
--
INSERT INTO stickyshop_uggroups VALUES
(5, 'Sales'),
(6, 'Purchases');

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);

-- 
-- Dumping data for table lk_template
--
INSERT INTO lk_template VALUES
(1, '<p><strong>[CompanyName]</strong></p>\r\n\r\n<p><strong>[logo]</strong></p>\r\n\r\n<p>Dear <strong>[Supplier]</strong></p>\r\n\r\n<p>You have send purchese order [REFERENCE] for:-</p>\r\n\r\n<p>[PurchaseDetails]</p>\r\n\r\n<p>And Payment Status for order <strong>[PaymentStatus]</strong> :-&nbsp;</p>\r\n\r\n<p>[PurchasePayments]</p>\r\n\r\n<p>And Purchase Status is <strong>[PurchaseStatus]</strong></p>\r\n\r\n<p><strong>Paid:[Paid]<br />\r\nDUE:[DUE]<br />\r\nGrandTotal:[GrandTotal]<br />\r\nDate:[DATE]</strong></p>\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]', '', '', '', '', '', '', '', '');

-- 
-- Dumping data for table lk_product_variant
--
INSERT INTO lk_product_variant VALUES
(7, 'Black'),
(8, 'Red'),
(9, 'X-large'),
(10, 'Small'),
(11, 'Large');

-- 
-- Dumping data for table lk_messages
--
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 kept 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 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 maintenue é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 modifier une facture payée le', '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 se puede mantener 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 modificar una factura que ha sido pagada el', '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');

-- 
-- Dumping data for table lk_language
--
INSERT INTO lk_language VALUES
(1, 'English'),
(2, 'Arabic'),
(3, 'French'),
(4, 'Spanish');

-- 
-- Dumping data for table lk_invoice_type
--
INSERT INTO lk_invoice_type VALUES
(1, 'Purchase'),
(2, 'Sale'),
(3, 'Return Purchase'),
(4, 'Return Sale'),
(5, 'Exprenses'),
(6, 'Quotation');

-- 
-- Dumping data for table lk_data_group
--
INSERT INTO lk_data_group VALUES
(1, 'show all data'),
(2, 'show user data only');

-- 
-- Dumping data for table lk_category
--
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);

-- 
-- Dumping data for table lk_barcode_symbology
--
INSERT INTO lk_barcode_symbology VALUES
(7, 'p1-klsa');

-- 
-- Dumping data for table lk_adjust_type
--
INSERT INTO lk_adjust_type VALUES
(1, 'Addition'),
(2, 'Subtraction');

-- 
-- Dumping data for table key_license
--
INSERT INTO key_license VALUES
(1, 'FF9ED-F90A5-6077D-7632B-34514');

-- 
-- Dumping data for table expenses
--
-- Table  expenses does not contain any data (it is empty)

-- 
-- Dumping data for table dual
--
-- Table  dual does not contain any data (it is empty)

-- 
-- Dumping data for table bar_code_generator
--
INSERT INTO bar_code_generator VALUES
('10001');

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
$$

--
-- Create function CalcStockSale
--
DELIMITER $$
CREATE FUNCTION CalcStockSale(P_Product_ID INT,P_Warehouse_ID int)
  RETURNS DECIMAL(10, 0)
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;
 
 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(10, 0)
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;
 
 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(10, 0)
BEGIN
 DECLARE v_Total DECIMAL(18,3);
 DECLARE V_ADQTY DECIMAL(18,3);
 DECLARE V_Adjust_Type INT;
 DECLARE d INT DEFAULT 0;
  
 DECLARE Get_cur CURSOR FOR SELECT
  IFNULL(ADQTY, 0),
  Adjust_Type
FROM product_warehouse
WHERE product_id = P_Product_ID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET d = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SET d = 1;
SELECT
  IFNULL(SUM(qty), 0) INTO v_Total
FROM product_warehouse
WHERE product_id = P_Product_ID;

OPEN Get_cur;
  
lbl: LOOP  
IF d = 1 THEN  
LEAVE lbl;
  
END IF;
  
IF NOT d = 1 THEN  
  
FETCH Get_cur INTO V_ADQTY, V_Adjust_Type;
  
 IF V_Adjust_Type=1 AND V_ADQTY IS NOT NULL THEN
 SET v_Total:=v_Total+(V_ADQTY);
 END IF;
 
 IF V_Adjust_Type=2 AND V_ADQTY IS NOT NULL THEN
 SET v_Total:=v_Total-(V_ADQTY);
 END IF;
END IF;
  
END LOOP;
  
  
CLOSE Get_cur;
 


 
RETURN v_Total;
END
$$

--
-- Create function CalcStock
--
DELIMITER $$
CREATE FUNCTION CalcStock(P_Product_ID INT,P_Warehouse_ID int)
  RETURNS DECIMAL(10, 0)
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;

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;

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;

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;

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;


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));
 
 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_Invoice
--
DELIMITER $$
CREATE FUNCTION CalcStock_Invoice(P_Product_ID INT,P_Warehouse_ID int)
  RETURNS DECIMAL(10, 0)
BEGIN

 DECLARE v_purchase_s DECIMAL;
 DECLARE v_r_purchase_s DECIMAL;
 DECLARE v_sale_s DECIMAL;
 DECLARE v_r_sale_s DECIMAL;
 DECLARE v_f_transfer DECIMAL;
 DECLARE v_t_transfer DECIMAL;
 DECLARE V_SELL_IN_MINUS VARCHAR(50);
 DECLARE v_Total DECIMAL;

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;

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;

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;

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;


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));
 
 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;

 IF V_SELL_IN_MINUS='Yes' THEN
  SET v_Total=ABS(v_Total);
 END IF;

 RETURN v_Total;
 
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_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 AS ID,
  p.name AS 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 (pw.Qty > 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.Paid AS Paid,
  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
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
DELETE
  FROM sale_det
WHERE sale_ID = old.ID;
END;
$$
--
-- Create trigger Del_return_sale_Det
--
DELIMITER $$
CREATE TRIGGER Del_return_sale_Det
	BEFORE DELETE
	ON return_sale
	FOR EACH ROW
BEGIN
DELETE
  FROM return_sale_det
WHERE return_sale_ID = old.ID;
END;
$$
--
-- Create trigger Del_return_purchase_Det
--
DELIMITER $$
CREATE TRIGGER Del_return_purchase_Det
	BEFORE DELETE
	ON return_purchase
	FOR EACH ROW
BEGIN
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 Del_purchase_Det
--
DELIMITER $$
CREATE TRIGGER Del_purchase_Det
	BEFORE DELETE
	ON purchase
	FOR EACH ROW
BEGIN
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,
  ROW_NUMBER() OVER (ORDER BY d.Total_Paid DESC) 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
  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) 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,
  ROW_NUMBER() OVER (ORDER BY d.Total_Paid DESC) 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
  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) 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,
  ROW_NUMBER() OVER (ORDER BY d.Total_Paid DESC) 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
  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) 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;
$$
