-- akmal 20151128 create view script only used for tekun project. 

CREATE TABLE IF NOT EXISTS `tbl_useradmin` (
  `fldid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `password` varchar(32) CHARACTER SET utf8 DEFAULT '0',
  `username` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
  `position` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `homepage` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `phone_off` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `phone_ext` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `phone_mobile` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `department` varchar(18) CHARACTER SET utf8 DEFAULT NULL,
  `section` varchar(18) CHARACTER SET utf8 DEFAULT NULL,
  `role` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
  `status` smallint(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `imageFile` varchar(254) CHARACTER SET utf8 NOT NULL,
  `online` int(5) NOT NULL,
  `last_seen` datetime NOT NULL,
  `access_comp` varchar(255) CHARACTER SET utf8 NOT NULL,
  `last_seen_ip` varchar(30) CHARACTER SET utf8 NOT NULL,
  `create_dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `create_user` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
  `modified_dt` date DEFAULT NULL,
  `modified_user` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
  `is_delete` smallint(6) DEFAULT '0',
  `language` varchar(10) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`fldid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

--
-- Dumping data for table `tbl_useradmin`
--

INSERT INTO `tbl_useradmin` (`fldid`, `userid`, `password`, `username`, `position`, `email`, `homepage`, `phone_off`, `phone_ext`, `phone_mobile`, `department`, `section`, `role`, `status`, `birthday`, `imageFile`, `online`, `last_seen`, `access_comp`, `last_seen_ip`, `create_dt`, `create_user`, `modified_dt`, `modified_user`, `is_delete`, `language`) VALUES
(1, 'admin ', 'fe01ce2a7fbac8fafaed7c982a04e229', 'Demo for Demo', '0', 'info@salihin.com.my', NULL, '0', NULL, '0174426998', '0', '0', 'ADMIN', NULL, NULL, '1-1448937345-logo.png', 0, '0000-00-00 00:00:00', '', '', '2014-01-24 07:55:05', 'yahaya', '2015-12-01', 'demo ', 0, 'en');


create or replace VIEW `view_search` AS select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,
concat(if((`a`.`updateAble` = 1),'arTransaction/editcashsale/','arReports/cashsalelistDetail/'),`a`.`ID`) AS `searchLink`,
concat('Cash Sale&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`salesInvoiceDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`paymentFrom`) AS `searchKey`,
`a`.`salesInvoiceDate` AS `transDate` 
from `comp_cash_sales` `a` 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'sotransaction/SalesDO/','soreport/DeliveryOrderDetail/'),`a`.`ID`) AS `searchLink`,concat('Delivery Order&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`deliveryOrderDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`deliveryOrderDate` AS `transDate` from (`comp_delivery_order` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 

union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateable` = 1),'locationtransfer/edit/','locationtransfer/reportdetail/'),`a`.`ID`) AS `searchLink`,concat('Inventory Transfer&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(`a`.`formNo` using utf8),'[',convert(lpad(`a`.`serialNo`,8,'0') using utf8),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`dateTransfer`,'%d/%m/%Y') using utf8),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`code`,'&nbsp',`b`.`desc`) AS `searchKey`,`a`.`dateTransfer` AS `transDate` from (`comp_location_transfer` `a` join `tbl_location` `b`) where (`a`.`fromLocationID` = `b`.`fldid`) 

union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'aptransaction/editPayment/','Apreports/supplierpaymentDetail/'),`a`.`ID`) AS `searchLink`,concat('Payment&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`paymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`paymentDate` AS `transDate` from (`comp_payment` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`) 

union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,
(case a.formNo when a.formNo like '%OR%' then 
concat(if((`a`.`updateAble` = 1),'sales/edit_sale/','sales/print_sale/'),`a`.`ID`) 
when a.formNo like '%OP-C%' then 
concat(if((`a`.`updateAble` = 1),'expenses/addNew/cogs/','expenses/printDetail/'),`a`.`ID`,'/cogs') 
when a.formNo like '%OP-E%' then 
concat(if((`a`.`updateAble` = 1),'expenses/addNew/expenses/','expenses/printDetail/'),`a`.`ID`,'/operational') 
when a.formNo like '%OP-L%' then 
concat(if((`a`.`updateAble` = 1),'expenses/addNew/loan/','expenses/printDetail/'),`a`.`ID`,'/loan') 
when a.formNo like '%OP-A%' then 
concat(if((`a`.`updateAble` = 1),'expenses/addNew/asset/','expenses/printDetail/'),`a`.`ID`,'/asset') 
when a.formNo like '%TT%' then 
concat(if((`a`.`updateAble` = 1),'transfer/','transfer/print_transfer/'),`a`.`ID`) 
when a.formNo like '%LR%' then 
concat(if((`a`.`updateAble` = 1),'loanReceive/','loanReceive/print_loanReceive/'),`a`.`ID`) 
else 
concat(if((`a`.`updateAble` = 1),'othersPayment/editPayWithoutItem/','othersPayment/otherpaywithoutDetail/'),`a`.`ID`) 
end ) AS `searchLink`,
concat('Payment Others&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`paymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`payTo`) AS `searchKey`,`a`.`paymentDate` AS `transDate` from `comp_payment_others` `a` 

union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'PO_Transaction/edit/','purchaseReports/PODetails/'),`a`.`ID`) AS `searchLink`,concat('Purchase Order&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`poDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`poDate` AS `transDate` from (`comp_purchase_order` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('soreport/SalesQuotationDetail/',`a`.`ID`) AS `searchLink`,concat('Quotation&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`quotationDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`quotationDate` AS `transDate` from (`comp_quotation` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'receiveItems/editReceiveItem/','receiveItems/ReceiveItemDetails/'),`a`.`ID`) AS `searchLink`,concat('Receive Item&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`deliveryDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`deliveryDate` AS `transDate` from (`comp_receive_item` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'artransaction/editReceivePayment/','arReports/compRecPaymentDetail/'),`a`.`ID`) AS `searchLink`,concat('Receive Payment&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`receivePaymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`receivePaymentDate` AS `transDate` from (`comp_receive_payment` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'gltransaction/editotherreceive/','glreports/otherReceiveAdviceRpt/'),`a`.`ID`) AS `searchLink`,concat('Receive Payment Others&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`receivePaymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`receiveFrom`) AS `searchKey`,`a`.`receivePaymentDate` AS `transDate` from `comp_receive_payment_others` `a` 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'artransaction/editSaleCreditNote/','arreports/salescreditnoteDetail/'),`a`.`ID`) AS `searchLink`,concat('Sales Credit Note&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`creditNoteDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`creditNoteDate` AS `transDate` from (`comp_sales_creditnote` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'artransaction/editSaleDebitNote/','arreports/salesdebitnoteDetail/'),`a`.`ID`) AS `searchLink`,concat('Sales Debit Note&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`debitNoteDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`debitNoteDate` AS `transDate` from (`comp_sales_debitnote` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('arReports/salesInvoiceDetail/',`a`.`ID`) AS `searchLink`,concat('Sales Invoice&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`salesInvoiceDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`salesInvoiceDate` AS `transDate` from (`comp_sales_invoice` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('soreport/SalesOrderDetail/',`a`.`ID`) AS `searchLink`,concat('Sales Order&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`salesOrderDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`customerName`) AS `searchKey`,`a`.`salesOrderDate` AS `transDate` from (`comp_sales_order` `a` join `customer` `b`) where (`a`.`customerID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'aptransaction/editCreditNote/','Apreports/creditNoteDetail/'),`a`.`ID`) AS `searchLink`,concat('Supplier Credit Note&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`creditNoteDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`creditNoteDate` AS `transDate` from (`comp_supplier_creditnote` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat(if((`a`.`updateAble` = 1),'aptransaction/editDebitNote/','Apreports/debitNoteDetail/'),`a`.`ID`) AS `searchLink`,concat('Supplier Debit Note&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`debitNoteDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`debitNoteDate` AS `transDate` from (`comp_supplier_debitnote` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`) 
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('apreports/purchaseInvoiceDetail/',`a`.`ID`) AS `searchLink`,concat('Purchase Invoice&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`invoiceDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`supplierName`) AS `searchKey`,`a`.`invoiceDate` AS `transDate` from (`comp_supplier_invoice` `a` join `supplier` `b`) where (`a`.`supplierID` = `b`.`ID`)

union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('sales/print_sale/',`a`.`ID`) AS `searchLink`,
concat('Sale&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`receivePaymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`description`) AS `searchKey`,`a`.`receivePaymentDate` AS `transDate` 
from (`comp_receive_payment_others` `a` join `comp_receive_payment_others_detail` `b`) 
where (`a`.`ID` = `b`.`receivePaymentOthersID`)
union select `a`.`ID` AS `ID`,`a`.`companyID` AS `companyID`,concat('expenses/printDetail/',`a`.`ID`) AS `searchLink`,
concat('Sale&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`a`.`formNo`,'[',convert(lpad(`a`.`serialNo`,8,'0') using latin1),']&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',convert(date_format(`a`.`paymentDate`,'%d/%m/%Y') using latin1),'&nbsp<i class="i-play" aria-hidden="true"></i>&nbsp',`b`.`itemDescription`) AS `searchKey`,`a`.`paymentDate` AS `transDate` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) 
where (`a`.`ID` = `b`.`otherPaymentID`)
;

create or replace view view_sme_cash_flow as 
select a.companyID, a.acctGroupID, a.acctCode, a.acctName, 
if(year(a.createdTS)=c.year,
	a.openingBalanceDr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1=c.year),0)) as openingBalanceDr, 
if(year(a.createdTS)=c.year,
	a.openingBalanceCr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1=c.year),0)) as openingBalanceCr, c.year, 
ifnull(sum(if(c.period=1,c.amount_dr,0)),0) - ifnull(sum(if(c.period=1 and c.formNo like '%TT%',c.amount_cr,0)),0) as january, 
ifnull(sum(if(c.period=2,c.amount_dr,0)),0) - ifnull(sum(if(c.period=2 and c.formNo like '%TT%',c.amount_cr,0)),0) as february, 
ifnull(sum(if(c.period=3,c.amount_dr,0)),0) - ifnull(sum(if(c.period=3 and c.formNo like '%TT%',c.amount_cr,0)),0) as march, 
ifnull(sum(if(c.period=4,c.amount_dr,0)),0) - ifnull(sum(if(c.period=4 and c.formNo like '%TT%',c.amount_cr,0)),0) as april, 
ifnull(sum(if(c.period=5,c.amount_dr,0)),0) - ifnull(sum(if(c.period=5 and c.formNo like '%TT%',c.amount_cr,0)),0) as may, 
ifnull(sum(if(c.period=6,c.amount_dr,0)),0) - ifnull(sum(if(c.period=6 and c.formNo like '%TT%',c.amount_cr,0)),0) as june, 
ifnull(sum(if(c.period=7,c.amount_dr,0)),0) - ifnull(sum(if(c.period=7 and c.formNo like '%TT%',c.amount_cr,0)),0) as july, 
ifnull(sum(if(c.period=8,c.amount_dr,0)),0) - ifnull(sum(if(c.period=8 and c.formNo like '%TT%',c.amount_cr,0)),0) as august, 
ifnull(sum(if(c.period=9,c.amount_dr,0)),0) - ifnull(sum(if(c.period=9 and c.formNo like '%TT%',c.amount_cr,0)),0) as september, 
ifnull(sum(if(c.period=10,c.amount_dr,0)),0) - ifnull(sum(if(c.period=10 and c.formNo like '%TT%',c.amount_cr,0)),0) as october, 
ifnull(sum(if(c.period=11,c.amount_dr,0)),0) - ifnull(sum(if(c.period=11 and c.formNo like '%TT%',c.amount_cr,0)),0) as november, 
ifnull(sum(if(c.period=12,c.amount_dr,0)),0) - ifnull(sum(if(c.period=12 and c.formNo like '%TT%',c.amount_cr,0)),0) as december 
from (comp_chart_of_acct a left outer join comp_journal_detail c on a.id = c.acctCode) left outer join comp_journal b on b.journalID = c.journalID and c.companyid = b.companyid   
where 
a.is_delete = 0 and b.is_delete = 0 and a.acctGroupID in (2,3,4,8,10)
group by a.companyID, a.acctGroupID, a.acctCode, a.acctName, a.openingBalanceDr, a.openingBalanceCr, c.year;

create or replace view view_asset_report as 
select a.ID as acctID, a.companyID, a.acctGroupID, a.acctCode, a.acctName, 
if(year(a.createdTS)=c.year,
	a.openingBalanceDr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1=c.year),0)) as openingBalanceDr, 
a.openingBalanceCr, c.year,
(if(year(a.createdTS)=c.year,
	a.openingBalanceDr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1 = c.year),0)) 
+ ifnull(sum(if(c.formNo like '%OP-A%',amount_dr,0)),0) ) as balanceAsset, 
ifnull(sum(if(c.period=1,c.amount_dr,0)),0) as january, 
ifnull(sum(if(c.period=2,c.amount_dr,0)),0)  as february, 
ifnull(sum(if(c.period=3,c.amount_dr,0)),0)  as march, 
ifnull(sum(if(c.period=4,c.amount_dr,0)),0)  as april, 
ifnull(sum(if(c.period=5,c.amount_dr,0)),0)  as may, 
ifnull(sum(if(c.period=6,c.amount_dr,0)),0)  as june, 
ifnull(sum(if(c.period=7,c.amount_dr,0)),0)  as july, 
ifnull(sum(if(c.period=8,c.amount_dr,0)),0)  as august, 
ifnull(sum(if(c.period=9,c.amount_dr,0)),0)  as september, 
ifnull(sum(if(c.period=10,c.amount_dr,0)),0) as october, 
ifnull(sum(if(c.period=11,c.amount_dr,0)),0)  as november, 
ifnull(sum(if(c.period=12,c.amount_dr,0)),0)  as december 
from (comp_chart_of_acct a left outer join comp_journal_detail c on a.id = c.acctCode) left outer join comp_journal b on b.journalID = c.journalID and c.companyid = b.companyid   
where 
a.is_delete = 0 and b.is_delete = 0 and a.acctGroupID in (2)
group by a.companyID, a.acctGroupID, a.acctCode, a.acctName, a.openingBalanceDr, a.openingBalanceCr, c.year;


create or replace view view_loan_report as 
select a.ID as acctID, a.companyID, a.acctGroupID, a.acctCode, a.acctName, 
(if(year(a.createdTS)=c.year,
	a.openingBalanceCr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1 = c.year),0)
	)) as openingBalanceCr, 
(
if(year(a.createdTS)=c.year,
	a.openingBalanceCr,
	ifnull((select balance from view_total_debitCredit where ID = a.ID and year-1 = c.year),0)) 
+ ifnull(sum(if(c.formNo like '%LR%',amount_cr,0)),0)
- ifnull(sum(c.amount_dr),0) ) as balanceLoan,
ifnull(sum(if(c.formNo like '%LR%',amount_cr,0)),0) as newloan,
c.year, 

ifnull(sum(if(c.period=1,c.amount_dr,0)),0) as january, 
ifnull(sum(if(c.period=2,c.amount_dr,0)),0) as february, 
ifnull(sum(if(c.period=3,c.amount_dr,0)),0) as march, 
ifnull(sum(if(c.period=4,c.amount_dr,0)),0) as april, 
ifnull(sum(if(c.period=5,c.amount_dr,0)),0) as may, 
ifnull(sum(if(c.period=6,c.amount_dr,0)),0) as june, 
ifnull(sum(if(c.period=7,c.amount_dr,0)),0) as july, 
ifnull(sum(if(c.period=8,c.amount_dr,0)),0) as august, 
ifnull(sum(if(c.period=9,c.amount_dr,0)),0) as september, 
ifnull(sum(if(c.period=10,c.amount_dr,0)),0) as october, 
ifnull(sum(if(c.period=11,c.amount_dr,0)),0) as november, 
ifnull(sum(if(c.period=12,c.amount_dr,0)),0) as december ,
max(b.effective_date) as lastPaymentDate,

(
ifnull((select e.amount_dr 
  from comp_journal_detail e 
  join comp_journal s on s.journalID = e.journalID
  where e.acctCode = a.ID 
  and e.amount_dr > 0 
  and e.formNo like '%OP-L%' 
  order by s.effective_date DESC
  limit 0,1),0)
) as lastPaymentAmount 

from (comp_chart_of_acct a left outer join comp_journal_detail c on a.id = c.acctCode) 

left outer join comp_journal b on b.journalID = c.journalID and c.companyid = b.companyid and b.is_delete = 0 ,
view_total_debitCredit vt  
where 
a.is_delete = 0 and a.acctGroupID in (4)
and a.ID = vt.ID and c.year = vt.year  
group by a.companyID, a.acctGroupID, a.acctCode, a.acctName, a.openingBalanceDr, a.openingBalanceCr, c.year;


create or replace view view_total_debitCredit as
select a.ID, a.companyID, a.acctGroupID, a.acctCode, a.acctName, 
(if(year(a.createdTS)= ifnull(c.year,year(a.createdTS)),
	ifnull(a.openingBalanceDr,0) - ifnull(a.openingBalanceCr,0),
	(ifnull((select ifnull(openingBalanceDr,0) - ifnull(openingBalanceCr,0) from comp_chart_of_acct where ID = a.ID and year(createdTS) < c.year),0))
	+ (select ifnull(sum(amount_dr),0) from comp_journal_detail where acctCode = a.ID and year < c.year)
	- (select ifnull(sum(amount_cr),0) from comp_journal_detail where acctCode = a.ID and year < c.year)
	)) as openingBalanceDr,
--	ifnull((select ifnull(openingBalanceDr,0) - ifnull(openingBalanceCr,0) from comp_chart_of_acct where ID = a.ID and year(createdTS) < c.year),0) as aa, 
--	(select ifnull(sum(amount_dr),0) from comp_journal_detail where acctCode = a.ID and year < c.year) as bb ,
-- (select ifnull(sum(amount_cr),0) from comp_journal_detail where acctCode = a.ID and year < c.year) as cc,	
a.openingBalanceCr, ifnull(c.year,year(a.createdTS)) as year ,
ifnull(sum(c.amount_dr),0) as totalDebit,
ifnull(sum(c.amount_cr),0) as totalCredit,
(if(year(a.createdTS)=c.year,
	a.openingBalanceDr - a.openingBalanceCr,
	(ifnull(a.openingBalanceDr,0) - ifnull(a.openingBalanceCr,0)
	+ (select ifnull(sum(amount_dr),0) from comp_journal_detail where acctCode = a.ID and year < c.year)
	- (select ifnull(sum(amount_cr),0) from comp_journal_detail where acctCode = a.ID and year < c.year))
	))
	+ifnull(sum(c.amount_dr),0) - ifnull(sum(c.amount_cr),0) as balance
from (comp_chart_of_acct a left outer join comp_journal_detail c on a.id = c.acctCode) left outer join comp_journal b on b.journalID = c.journalID and c.companyid = b.companyid and b.is_delete = 0  
where 
a.is_delete = 0  
group by a.ID, a.companyID, a.acctGroupID, a.acctCode, a.acctName, a.openingBalanceDr, a.openingBalanceCr, c.year;


CREATE or replace VIEW `view_unpost_other_payment` AS 
select `a`.`ID` AS `othersPaymentID`,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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,
`b`.`accountID` AS `drAccountID`,
if(a.formNo like '%LR%',a.bankID,(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`))) AS `crAccountID`,
(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crTaxAccountID` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0)) 
union select `a`.`ID` AS `othersPaymentID`,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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,`b`.`taxAmount` AS `taxamount`,`b`.`accountID` AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crAccountID`,
(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crTaxAccountID` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and (`a`.`simplifiedTax` = 1) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= `b`.`lineNo`))) <= (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`))) 
union select `a`.`ID` AS `othersPaymentID`,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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,((select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`) - (select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` < `b`.`lineNo`)))) AS `taxamount`,
`b`.`accountID` AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crAccountID`,
(select `tax_master`.`taxAccount` from `tax_master` where (`tax_master`.`ID` = `b`.`taxID`)) AS `drTaxAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crTaxAccountID` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and (`a`.`simplifiedTax` = 1) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= `b`.`lineNo`))) > (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`)) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` < `b`.`lineNo`))) < (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`))) 
union select `a`.`ID` AS `othersPaymentID`,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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,(select (coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) - (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`)) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= `b`.`lineNo`))) AS `taxamount`,
`b`.`accountID` AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'gstnoclaim'))) AS `drTaxAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crTaxAccountID` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and (`a`.`simplifiedTax` = 1) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= `b`.`lineNo`))) > (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`)) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` < `b`.`lineNo`))) < (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`))) 
union select `a`.`ID` AS `othersPaymentID`,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`,`b`.`ID` AS `paymentDetailID`,NULL AS `supplierInvoiceNo`,`b`.`lineNo` AS `lineNo`,`b`.`itemdescription` AS `itemdescription`,`b`.`amountExcludedTax` AS `amountExcludedtax`,`b`.`taxID` AS `taxid`,(select (coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) - (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`)) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= `b`.`lineNo`))) AS `taxamount`,
`b`.`accountID` AS `drAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crAccountID`,
(select `comp_control_acct_setup`.`accountID` from `comp_control_acct_setup` where ((`comp_control_acct_setup`.`companyID` = `a`.`companyID`) and (`comp_control_acct_setup`.`accountCode` = 'gstnoclaim'))) AS `drTaxAccountID`,
(select `comp_bank`.`linkAccountID` from `comp_bank` where (`comp_bank`.`ID` = `a`.`bankId`)) AS `crTaxAccountID` 
from (`comp_payment_others` `a` join `comp_payment_others_without_item` `b`) where ((`a`.`ID` = `b`.`otherPaymentID`) and (`a`.`updateAble` = 1) and (`a`.`is_delete` = 0) and (`a`.`simplifiedTax` = 1) and ((select coalesce(sum(`comp_payment_others_without_item`.`taxAmount`),0) from `comp_payment_others_without_item` where ((`comp_payment_others_without_item`.`otherPaymentID` = `a`.`ID`) and (`comp_payment_others_without_item`.`lineNo` <= (`b`.`lineNo` + 1)))) > (select `MaxInputTaxClaim` from `comp_gst_preference` `c` where `c`.`companyID` = `a`.`companyID`)));
