{
	"pluginname": "Payroll",
	"systemplugin": "0",
	"iswebsite":0,
	"version": "1.0",
	"icon": "mif-delicious",
	"table_def": [
		{
			"name": "nbbi_beneficiary",
			"importtabledef":{"pluginname":"NBBI Management"}
		},{
			"name":"payroll_db",
			"saveid":"1sv","getid":"1gt","delid":"1dt",
			"form":{
				"0":{"title":"Payroll","icon":"mif-delicious","type":"Inline Form",
					"fields":"staffid,payrolldate,workdays,basic,da,hra,conveyance,otherallowance,deduction_food_housing,deduction_epf,deduction_mut,deduction_medical,deduction_other,deduction_total"
				}
			},
			"list":{
				"1lt":{
					"name":"Payroll List Project Wise",
					"tbl":"nbbi_beneficiary b",
					"fld":"b.beneficiaryname,b.designation,b.dateofadmission,basic,da,hra,conveyance,otherallowance,gross,deduction_food_housing,deduction_epf,deduction_mut,deduction_medical,deduction_other,deduction_total,netpay",
					"join":"LEFT JOIN ||thistbl|| a ON a.staffid=b.id",
					"whr":"b.recordstatus=1 and b.project='||project||' and DATE_FORMAT(a.payrolldate,'%y-%m')=DATE_FORMAT('||payrolldate||','%y-%m')",
					"header":"Name,Designation,DOJ,Basic,DA,HRA,Conveyance,Other,Gross,Food & Accom.,EPF,MUT,Medical,Other,Deduction Total,Net Pay",
					"prepend":"<b>Project: </b>||project|| Payroll Month:||payrollmonth||"
				},
				"1lt1":{
					"name":"Payroll List Workdays Entry",
					"tbl":"||thistbl|| a",
					"fld":"b.beneficiaryname,b.designation,b.dateofadmission,workdays",
					"join":"INNER JOIN nbbi_beneficiary b ON a.staffid=b.id",
					"whr":"b.recordstatus=1 and b.project='||project||' and DATE_FORMAT(a.payrolldate,'%y-%m')='||yearmonth||'",
					"header":"Name,Designation,DOJ,Work Days Count",
					"prepend":"<b>Project: </b>||project|| Payroll Month:||payrollmonth||"
				}
			},
			"fields":[
				{"name":"staffid","type":"lookup","label":"Staff","lookup":{"sqlid":"1stlup","tbl":"nbbi_beneficiary","fld":"CONCAT(beneficiaryname,'-',project) as beneficiaryname","whr":"recordtype='Staff' and recordstatus=1"}},
				{"name":"payrolldate","type":"date","label":"Pay Date (Last date for which payment is made)"},
				{"name":"workdays","type":"int","other":"NOT NULL DEFAULT 0","label":"Work Days #"},
				{"name":"basic","type":"int","other":"NOT NULL DEFAULT 0","label":"Basic"},
				{"name":"da","type":"int","other":"NOT NULL DEFAULT 0","label":"DA"},
				{"name":"hra","type":"int","other":"NOT NULL DEFAULT 0","label":"HRA"},
				{"name":"conveyance","type":"int","other":"NOT NULL DEFAULT 0","label":"Conveyance"},								
				{"name":"otherallowance","type":"int","other":"NOT NULL DEFAULT 0","label":"Other Allowance"},
				{"name":"gross","type":"int","fieldtype":"readonly","other":"GENERATED ALWAYS AS (floor((workdays/30)*(basic+da))+hra+conveyance+otherallowance)","label":"Gross"},				
				{"name":"deduction_food_housing","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - Food and Housing"},
				{"name":"deduction_epf","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - EPF"},
				{"name":"deduction_mut","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - MUT"},
				{"name":"deduction_medical","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - Medical"},
				{"name":"deduction_other","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - Other"},				
				{"name":"deduction_total","type":"int","fieldtype":"readonly","other":"GENERATED ALWAYS AS (deduction_food_housing+deduction_epf+deduction_mut+deduction_medical+deduction_other)","label":"Deduction - Total"},				
				{"name":"netpay","type":"int","fieldtype":"readonly","other":"GENERATED ALWAYS AS ((floor((workdays/30)*(basic+da))+hra+conveyance+otherallowance)-(deduction_food_housing+deduction_epf+deduction_mut+deduction_medical+deduction_other))","label":"Net Pay"}	
			]
		},{
			"name":"payroll_allowance",
			"saveid":"2sv","getid":"2gt","delpermanentid":"2dl",
			"form":{
				"0":{"title":"Payroll Allowance","icon":"mif-delicious","type":"Inline Form",
					"fields":"*"
				}
			},
			"list":{
				"2lt":{
					"tbl":"nbbi_beneficiary b","getform":"||thistbl|| 0",
					"includeeditdelete":true,
					"primarytablenamealias":"a",
					"join":"LEFT JOIN ||thistbl|| a ON a.staffid=b.id",
					"fld":"b.beneficiaryname,a.basic,a.da,a.hra,a.conveyance,a.other,deduction_food_housing,deduction_epf,deduction_mut,deduction_medical",
					"header":"Staff,Basic,DA,HRA,Conveyance,Other Allowance,Deducation-Food,Deducation-EPF,Deduction-MUT,Deduction-Medical",
					"whr":[
						"b.recordstatus=1 and b.project='||project||' and b.recordtype='Staff'",
						{"if":"||location||","op":"!=","value":"BLANK","then":"and b.location='||location||'"},
						{"if":"||epf||","op":"!=","value":"BLANK","then":"and b.epf=||epf||"}
					],
					"editablefields":"a.basic,a.da,a.hra,a.conveyance,a.other,deduction_food_housing,deduction_epf,deduction_mut,deduction_medical"
				}
			},
			"fields":[
				{"name":"staffid","type":"lookup","label":"Staff","lookupdetails":{"sqlid":"1stlup","tbl":"nbbi_beneficiary","fld":"CONCAT(beneficiaryname,'-',project) as beneficiaryname","whr":"recordtype='Staff' and recordstatus=1"}},
				{"name":"basic","type":"int","other":"NOT NULL DEFAULT 0","label":"Basic"},
				{"name":"da","type":"int","other":"NOT NULL DEFAULT 0","label":"DA"},
				{"name":"hra","type":"int","other":"NOT NULL DEFAULT 0","label":"HRA"},
				{"name":"conveyance","type":"int","other":"NOT NULL DEFAULT 0","label":"Conveyance"},
				{"name":"other","type":"int","other":"NOT NULL DEFAULT 0","label":"Other Allowance"},
				{"name":"deduction_food_housing","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - Food and Housing"},
				{"name":"deduction_epf","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - EPF"},
				{"name":"deduction_mut","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - MUT"},
				{"name":"deduction_medical","type":"int","other":"NOT NULL DEFAULT 0","label":"Deduction - Medical"}
			]
		}
	],
	"sqlcommands_oninstall": [],
	"form":[],
	"form_html": "",
	"navbutton": [{
		"customid": "1",
		"caption": "Payroll",
		"icon": "mif-delicious",
		"btntype": "ribbon-button",
		"btngroup": "Others",
		"btntab": "Developer",
		"btnorder": "1",
		"onclick": "initializePlugin()"

	}],
	"script": "",
	"formbuttons": [{
			"name": "payroll",
			"caption": "Payroll",
			"icon": "mif-delicious fg-red",
			"onclick": "Eirene.runStmt('1pymnu')"
		}
	],
	"sqlstatement": [
		{
			"id":"1pymnu",
			"name":"Payroll Main Menu",
			"sql":{
				"action":"Return","outputto":"html","output":"#workareaFilter",
				"value":[
					"<ul class='h-menu'>",
						"<li><a href='#' class='dropdown-toggle'>Payroll</a>",
							"<ul class='d-menu' data-role='dropdown'>",							
								"<li onclick='Eirene.runStmt(&apos;1pyrl&apos;)'><a href='#' >Workdays Entry</a></li>",
								
							"</ul>",
						"</li>",						
						"<li><a href='#' onclick='printHTML(&apos;workareaTablebox&apos;)'>Print</a></li>"
				],
				"onsuccess":"cmd:dom,fun:show;#workareaFilter-cmd:dom,fun:show;#workareaFilter1"
			}
		},{
			"id":"1stflt",
			"name":"Staff List",
			"sql":{
				"action":"Return","outputto":"php","output":"res",
				"value":[
					{"call":"37stf","whr":"recordstatus =1 and project='||project||' and recordtype='Staff'"}
				]				
			}
		},{
			"id":"1st1lt",
			"name":"Staff List For WorkDays Entry",
			"sql":{
				"action":"Get Table","output":"#workareaTablebox",
				"tbl":"nbbi_beneficiary a","getform":"nbbi_beneficiary 2",
				"fld":"a.beneficiaryname,a.project",
				"functionfield":[
					{
						"type":"text",
						"caption":"WorkDay",
						"value":"<b.workdays>",
						"onclick":"Eirene.runStmt('1svwdy',{workdays:$(this).val(),staffid:&apos;<a.id>&apos;,payrolldate:&apos;||payrolldate||&apos;})"						
					}
					
				],
				"join":"LEFT JOIN payroll_db b ON a.id=b.staffid",
				"includeeditdelete":true,
				"header":"Employee ID,Name,Type,Appointment Date,Gender,State",
				"whr":"a.recordstatus =1 and a.project='||project||' and a.recordtype='Staff' and DATE_FORMAT(b.payrolldate,'%Y-%m')=DATE_FORMAT('||payrolldate||','%Y-%m')"
			}
		},{
			"id":"1svwdy",
			"name":"Save Payroll Workdays",
			"sql":{
				"action":"Return","outputto":"php","output":"res",
				"value":[
					{"sqlstatement":{"a":"SELECT id FROM payroll_db WHERE staffid='||staffid||' and DATE_FORMAT(payrolldate,'%Y-%m')=DATE_FORMAT('||payrolldate||','%Y-%m') and recordstatus=1"}},
					{"sqlstatement":{"runif":{"if":"||GET_a||","op":"!=","value":"BLANK"},"b":"UPDATE payroll_db SET workdays=||workdays|| where id='||GET_a||'"}},
					{"sqlstatement":{"runif":{"if":"||GET_a||","op":"=","value":"BLANK"},"b":"INSERT INTO payroll_db (id,staffid,payrolldate,workdays,basic,da,hra,conveyance,otherallowance,deduction_food_housing,deduction_epf,deduction_mut,deduction_medical,deduction_other,deduction_total) select UUID(),'||staffid||','||payrolldate||',||workdays||,basic,da,hra,conveyance,otherallowance,deduction_food_housing,deduction_epf,deduction_mut,deduction_other,(deduction_food_housing+deduction_epf+deduction_mut+deduction_medical+deduction_other) as deduction_total FROM payroll_db WHERE staffid='||staffid||' order by createdon DESC limit1"}}
				]
				
			}
		},{
			"id":"1pyrl",
			"name":"Payroll Filter",
			"sql":{
				"action":"Return","outputto":"html","output":"#workareaFilter1",
				"value":[					
					{"elem":{							
							"parent":"div class='row'",					
							"elem":[
									{"name":"1pyrl1_lbl1","id":"1pyrl1_lbl1","class":"mr-2","type":"div","value":"Project"},
									{"name":"1pyrl1_staff","id":"1pyrl1_project","class":"mr-2","type":"select","option":"SELECT DISTINCT project FROM nbbi_beneficiary where recordtype='Staff'"},
									{"name":"1pyrl1_lbllocation","id":"1pyrl1_lbllocation","class":"mr-2","type":"div","value":"Location"},
									{"name":"1pyrl1_location","id":"1pyrl1_location","class":"mr-2","type":"select","option":"SELECT DISTINCT location FROM nbbi_beneficiary where recordtype='Staff'"},
									{"name":"1pyrl1_lblepf","id":"1pyrl1_lblepf","class":"mr-2","type":"div","value":"EPF"},
									{"name":"1pyrl1_epf","id":"1pyrl1_epf","class":"mr-2","type":"select","option":",0:Non-EPF,1:EPF"},
									{"name":"1pyrl1_lbl2","id":"1pyrl1_lbl2","class":"mr-2","type":"div","value":"Payroll Date (Last Date of Month)"},
									{"name":"1pyrl1_payrolldate","id":"1pyrl1_payrolldate","class":"mr-2","type":"datepicker"},
									{"name":"1pyrl1_btn","id":"1pyrl1_btn","type":"button","value":"Ok","onclick":"Eirene.runStmt('1st1lt',{project:$('#1pyrl1_project').val(),payrolldate:$('#1pyrl1_payrolldate').val()})"},
									{"name":"1pyrl1_allowancebtn","id":"1pyrl1_allowancebtn","type":"button","value":"Allowance","onclick":"Eirene.runStmt('2lt',{project:$('#1pyrl1_project').val(),location:$('#1pyrl1_location').val(),epf:$('#1pyrl1_epf').val()})"}
									
									
							]
							
						}
					}
				]
			}
		}
	]
}