DROP TRIGGER IF EXISTS `triger_cash_sales`;
DELIMITER //
CREATE TRIGGER `triger_cash_sales` BEFORE INSERT ON `comp_cash_sales`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_cash_sales
                    where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_donn1_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_donn1_add_serialNo` BEFORE INSERT ON `comp_delivery_order`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_delivery_order 
			   where companyID = new.companyID)
//
DELIMITER ;



DROP TRIGGER IF EXISTS `trigger_before_insert_cp_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_cp_add_serialNo` BEFORE INSERT ON `comp_payment`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_payment 
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_payo_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_payo_add_serialNo` BEFORE INSERT ON `comp_payment_others`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_payment_others 
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `mytrigerfopobeforeinsert`;
DELIMITER //
CREATE TRIGGER `mytrigerfopobeforeinsert` BEFORE INSERT ON `comp_purchase_order`
 FOR EACH ROW set new.serialNo = 
(select nextSerialNo from view_serial_comp_purchase_order 
	where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_cq_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_cq_add_serialNo` BEFORE INSERT ON `comp_quotation`
 FOR EACH ROW set new.serialNo = 
(select nextSerialNo from view_serial_comp_quotation 
	where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `triger_BEFORE_Insert_addSerialForReceiveItem`;
DELIMITER //
CREATE TRIGGER `triger_BEFORE_Insert_addSerialForReceiveItem` BEFORE INSERT ON `comp_receive_item`
 FOR EACH ROW set new.serialNo = 
(select nextSerialNo from view_serial_comp_receive_item 
	where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `updateBalanceItem`;
DELIMITER //
CREATE TRIGGER `updateBalanceItem` BEFORE INSERT ON `comp_receive_item_detail`
 FOR EACH ROW if new.balanceitem is null then
set new.balanceItem = new.quantityReceived;
end if
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_rpay_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_rpay_add_serialNo` BEFORE INSERT ON `comp_receive_payment`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_receive_payment
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_rpay_add_others_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_rpay_add_others_serialNo` BEFORE INSERT ON `comp_receive_payment_others`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_receive_payment_others
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_scn_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_scn_add_serialNo` BEFORE INSERT ON `comp_sales_creditnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_creditnote
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_sdn_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sdn_add_serialNo` BEFORE INSERT ON `comp_sales_debitnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_debitnote
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `Addserialsaleorder`;
DELIMITER //
CREATE TRIGGER `Addserialsaleorder` BEFORE INSERT ON `comp_sales_order`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_order
			   where companyID = new.companyID)
//
DELIMITER ;

-- ----------------

DROP TRIGGER IF EXISTS `trigger_before_insert_scno_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_scno_add_serialNo` BEFORE INSERT ON `comp_supplier_creditnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_creditnote
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_sdno_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_sdno_add_serialNo` BEFORE INSERT ON `comp_supplier_debitnote`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_debitnote
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_supinv_add_serialNo`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_supinv_add_serialNo` BEFORE INSERT ON `comp_supplier_invoice`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_supplier_invoice 
			   where companyID = new.companyID)
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_insert_item`;
DELIMITER //
CREATE TRIGGER `trigger_before_insert_item` BEFORE INSERT ON `item`
 FOR EACH ROW begin
if new.incomeID = 0 or new.incomeID is null then 
set new.incomeID = (select accountID from comp_control_acct_setup where ID > 0 and companyID = new.companyID and accountCode='saleacct'); 
end if;
if new.cogsID = 0 or new.cogsID is null then 
set new.cogsID = (select accountID from comp_control_acct_setup where id>0 and companyID = new.companyID and accountCode = 'saleacct');
end if;

end
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_default_created_date`;
DELIMITER //
CREATE TRIGGER `trigger_default_created_date` BEFORE INSERT ON `supplier`
 FOR EACH ROW if ( isnull(new.createddate) ) then
 set new.createddate=curdate();
end if
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trir_befor_inser_SI`;
DELIMITER //
CREATE TRIGGER `trir_befor_inser_SI` BEFORE INSERT ON `comp_sales_invoice`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_sales_invoice
			   where companyID = new.companyID)
//
DELIMITER ;


create or replace VIEW `view_supplier_statement` AS 
select `comp_supplier_invoice`.`ID` AS `ID`,`comp_supplier_invoice`.`ID` AS `parent`,`comp_supplier_invoice`.`companyID` AS `companyID`,`comp_supplier_invoice`.`supplierID` AS `supplierID`,'Invoice' AS `transType`,`comp_supplier_invoice`.`invoiceDate` AS `transDate`,`comp_supplier_invoice`.`supplierInvoiceNo` AS `refNo`,concat(concat(concat(`comp_supplier_invoice`.`formNo`,'['),convert(lpad(`comp_supplier_invoice`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`comp_supplier_invoice`.`totalAmount` AS `cr`,1 AS `seq` from `comp_supplier_invoice` where (`comp_supplier_invoice`.`updateAble` = 0) 
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'DebitNote' AS `transType`,`a`.`debitNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`totalAmount` AS `dr`,NULL AS `cr`,2 AS `seq` from (`comp_supplier_debitnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`)) 
union select `a`.`ID` AS `ID`,`a`.`supplierInvoiceID` AS `parent`,`a`.`companyID` AS `companyID`,`a`.`supplierID` AS `supplierID`,'CreditNote' AS `transType`,`a`.`creditNoteDate` AS `transDate`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `refno`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,NULL AS `dr`,`a`.`totalAmount` AS `cr`,3 AS `seq` from (`comp_supplier_creditnote` `a` join `comp_supplier_invoice` `b`) where ((`a`.`updateAble` = 0) and (`a`.`supplierInvoiceID` = `b`.`ID`)) 

union 
select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,'Payment' AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`b`.`amountPaid` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` where (`b`.`updateAble` = 0) 

union select `b`.`ID` AS `ID`,NULL AS `parent`,`b`.`companyID` AS `companyID`,`b`.`supplierID` AS `supplierID`,'Discount' AS `transType`,`b`.`paymentDate` AS `transDate`,`b`.`referenceNo` AS `refNo`,concat(concat(concat(`b`.`formNo`,'['),convert(lpad(`b`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,
`b`.`totalDiscount` AS `dr`,NULL AS `cr`,4 AS `seq` from `comp_payment` `b` where (`b`.`updateAble` = 0);



create or replace view 
`view_chart_of_acct_updateable` 
AS select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`acctGroupID` AS `acctGroupID`,`a`.`acctCode` AS `acctCode`,`a`.`acctName` AS `acctName`,`a`.`acctDesc` AS `acctDesc`,
if((((select count(`comp_journal_detail`.`ID`) from `comp_journal_detail` 
where (`comp_journal_detail`.`acctCode` = `a`.`ID`)) + (select count(`comp_opening_balance`.`ID`) from `comp_opening_balance` where (`comp_opening_balance`.`accountID` = `a`.`ID`))) > 0),10,1) AS `updateable` 
from `comp_chart_of_acct` `a`;

update _sys_menu set display = 0 where fldid = 14; 


update _sys_menu set display = 0 where fldid = 172; 

create or replace view 
`view_chart_of_acct_updateable` 
AS select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,`a`.`acctGroupID` AS `acctGroupID`,`a`.`acctCode` AS `acctCode`,`a`.`acctName` AS `acctName`,`a`.`acctDesc` AS `acctDesc`,
if((((select count(`comp_journal_detail`.`ID`) from `comp_journal_detail` 
where (`comp_journal_detail`.`acctCode` = `a`.`ID`)) + (select count(`comp_opening_balance`.`ID`) from `comp_opening_balance` where (`comp_opening_balance`.`accountID` = `a`.`ID` and (comp_opening_balance.debit + comp_opening_balance.credit > 0)))) > 0),10,1) AS `updateable` 
from `comp_chart_of_acct` `a`;

update master_code set is_delete = 1 where id = 23;
	
ALTER TABLE `comp_supplier_invoice` CHANGE `amountPaid` `amountPaid` DOUBLE NOT NULL;

CREATE TABLE IF NOT EXISTS `comp_gst_transaction` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` int(11) NOT NULL,
  `companyID` int(11) NOT NULL,
  `formNo` varchar(255) NOT NULL,
  `vendor_cust_name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `gst_number` varchar(255) NOT NULL,
  `Description` text NOT NULL,
  `date` date NOT NULL,
  `reference` varchar(255) NOT NULL,
  `based_amount` double NOT NULL,
  `updateAble` tinyint(1) NOT NULL DEFAULT '1',
  `journalNo` varchar(255) NOT NULL,
  `isDelete` int(11) NOT NULL DEFAULT '0',
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

--
-- Triggers `comp_gst_transaction`
--
DROP TRIGGER IF EXISTS `trir_before_insert_GST`;
DELIMITER //
CREATE TRIGGER `trir_before_insert_GST` BEFORE INSERT ON `comp_gst_transaction`
 FOR EACH ROW set new.serialNo = (select nextSerialNo 
			   from view_serial_comp_gst_transaction
			   where companyID = new.companyID)
//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `comp_gst_transaction_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `gstTransID` int(11) NOT NULL,
  `accountID` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `taxID` int(11) NOT NULL,
  `taxRate` double NOT NULL,
  `taxAmountDr` double NOT NULL DEFAULT '0',
  `taxAmountCr` double NOT NULL DEFAULT '0',
  `createdBy` int(11) NOT NULL,
  `createdTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int(11) NOT NULL,
  `updatedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;


CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_serial_comp_gst_transaction` AS select `c`.`ID` AS `companyID`,ifnull(((select max(`t`.`serialNo`) from `comp_gst_transaction` `t` where (`c`.`ID` = `t`.`companyID`)) + 1),ifnull((select `frm`.`formInitialNo` from `comp_form_control` `frm` where ((`frm`.`companyID` = `c`.`ID`) and (`frm`.`formID` = 118))),1)) AS `nextSerialNo` from `company` `c`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_unpost_gst_transaction` AS select `a`.`ID` AS `paymentID`,concat(concat(concat(`a`.`formNo`,'['),convert(lpad(`a`.`serialNo`,8,'0') using latin1)),']') AS `formNo`,`a`.`companyID` AS `companyID`,NULL AS `supplierID`,NULL AS `purchaseInvoiceID`,concat(`a`.`Description`,'[',`a`.`vendor_cust_name`,']') AS `supplierName`,0 AS `lineNo`,NULL AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`a`.`reference` AS `item`,`b`.`taxAmountDr` AS `amtPaid_amtAppl`,0 AS `discount_bankCharges`,`b`.`accountID` AS `drAccountID`,(select `comp_gst_transaction_detail`.`accountID` from `comp_gst_transaction_detail` where ((`comp_gst_transaction_detail`.`gstTransID` = `a`.`ID`) and (`comp_gst_transaction_detail`.`taxAmountCr` > 0))) AS `crAccountID`,NULL AS `drDiscountAccountID`,NULL AS `crDiscountAccountID` from (`comp_gst_transaction` `a` join `comp_gst_transaction_detail` `b`) where ((`a`.`ID` = `b`.`gstTransID`) and (`b`.`taxAmountDr` > 0) and (`a`.`updateAble` = 1) and (`a`.`isDelete` = 0));