{% load i18n %}
{
	"personnel.Employee":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "5":"Gender"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '卡号' %}","{% trans '部门编号' %}","{% trans '部门名称' %}","{% trans '职位编号' %}","{% trans '职位名称' %}","{% trans '性别' %}","{% trans '手机' %}","{% trans '生日' %}", "{% trans '邮箱' %}","{% trans '考勤区域' %}"],
            "db_fields":["badgenumber","name","card_no","code","DeptName", "pcode", "pname","gender", "pager", "Birthday", "email","SUM_AREA"],
            "sql": "
                SELECT badgenumber,name,card_no,code,DeptName, pcode, pname,gender, pager, Birthday, email,SUM_AREA
                            from (
                        SELECT badgenumber,name,card_no,code,DeptName, pcode, pname, gender, pager, Birthday, email,SUM_AREA
                                FROM(
                                    SELECT   
                                            u.defaultdeptid,u.badgenumber, u.name,d.code,d.DeptName,p.code as pcode, p.name as pname,u.gender,pager,
                                            left(u.Birthday,10) as Birthday,u.email,

                                                (SELECT DISTINCT group_concat(areaname,',') 
                                                FROM personnel_area a,userinfo_attarea ua
                                                where ua.area_id=a.id and ua.employee_id=u.userid
                                           
                                            
                            
                                    ) as SUM_AREA,
                                   (select cardno from personnel_issuecard i where cardstatus = '1' and i.userid_id = u.userid) as card_no
                                    from userinfo u left join departments d on u.defaultdeptid=d.deptid
                                    left join personnel_positions p on u.position_id=p.id
                                    where u.defaultdeptid= d.DeptId and
                                    not(u.status=3) and not(u.status=999) order
                                    by u.badgenumber
                                ) AS RECORDS %s 
                                )as b limit %s,%s
                    ",
                    "admin_sql": "
                        SELECT badgenumber,name,card_no,code,DeptName,pcode, pname,gender, pager,Birthday, email,SUM_AREA
                            from (
                        SELECT badgenumber,name,card_no,code,DeptName,pcode, pname,gender, pager,Birthday, email,SUM_AREA
                                FROM(
                                    SELECT   
                                            u.defaultdeptid,u.badgenumber, u.name,d.code,p.code as pcode, p.name as pname,d.DeptName,u.gender,pager,
                                            left(u.Birthday,10) as Birthday,u.email,

                                                (SELECT DISTINCT group_concat(areaname,',') 
                                                FROM personnel_area a,userinfo_attarea ua
                                                where ua.area_id=a.id and ua.employee_id=u.userid
                                           
                                            
                            
                                    ) as SUM_AREA,
                                   (select cardno from personnel_issuecard i where cardstatus = '1' and i.userid_id = u.userid) as card_no
                                    from userinfo u left join departments d on u.defaultdeptid=d.deptid
                                    left join personnel_positions p on u.position_id=p.id
                                    where u.defaultdeptid= d.DeptId and
                                    not(u.status=3) and not(u.status=999) order
                                    by u.badgenumber
                                ) AS RECORDS %s 
                                )as b limit %s,%s
                            "
                    
                    
    },
	"iaccess.AccRTMonitor":{
			"params":"",
			"use_perms":"False", 
	        "verbose_fields":{
	        	"4":"event_type",
	            "10":"verified"
	        },
	        "head":["{% trans '时间' %}","{% trans '区域' %}","{% trans '设备' %}","{% trans '事件点' %}","{% trans '事件描述' %}","{% trans '卡号' %}","{% trans '人员编号' %}","{% trans '姓 名' %}","{% trans '部门' %}","{% trans '状态' %}","{% trans '验证方式' %}","{% trans '备注' %}"],
	        "db_fields":["time","area","device_name","event_point_name","event_type","card_no","pin","full_name","dept","state_name","verified","description"],
	        "sql":"
	            SELECT time,area,device_name,event_point_name,event_type,card_no,pin,concat(firstname,ifnull(lastname,' ')) as full_name,dept,state_name,verified,description 
	            FROM (
	                select a.time,a.area,a.device_name,a.event_point_name,a.event_type,a.card_no,a.pin,a.firstname,a.lastname,a.dept,a.state_name,a.verified,a.description 
	                from acc_monitor_log a left join userinfo u on a.pin = u.badgenumber order by time desc
	            )
	            as records %s limit %s,%s
	        "
	}, 
	"pos.ICConsumerList":{
				"params":"",
				"use_perms":"False", 
				"verbose_fields":{
					"6":"type_name",
					"9":"pos_model",
					"17":"log_flag"
				},
				"head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '部门编号' %}","{% trans '部门名称' %}","{% trans '卡号' %}","{% trans '卡账号' %}","{% trans '类型名称' %}","{% trans '操作金额' %}","{% trans '余额' %}","{% trans '消费类型' %}","{% trans '餐厅名称' %}","{% trans '餐别名称' %}","{% trans '设备序列号' %}","{% trans '设备流水号' %}","{% trans '卡流水号' %}","{% trans '消费时间' %}","{% trans '上传时间' %}","{% trans '记录标志' %}"],
				"db_fields":["user_pin","user_name","code","DeptName","card","sys_card_no","type_name","money","balance","pos_model","din_name","meal_name","dev_sn","dev_serial_num","card_serial_num","pos_time","convey_time","log_flag"],
				"sql":"
				   SELECT user_pin,user_name,code,DeptName,card,sys_card_no,type_name,money,balance,pos_model,din_name,meal_name,dev_sn,dev_serial_num,card_serial_num,pos_time,convey_time,log_flag
				   From( 
				   SELECT user_pin,user_name,
				   code,
				   DeptName, card,sys_card_no,dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,
				   type_name,money,balance,pos_model, din_name, meal_name,log_flag,dining_id,meal_id
				   FROM ( 
				   select user_id,user_pin,user_name,
				   departments.code,
				   departments.DeptName, card,sys_card_no,dev_sn,card_serial_num,dev_serial_num,pos_time,convey_time,
				   type_name,pos_icconsumerlist.money,balance,pos_model, iclock_dininghall.name as din_name,personnel_meal.name as meal_name,log_flag,pos_icconsumerlist.dining_id,pos_icconsumerlist.meal_id 
				   from  pos_icconsumerlist
				   left join iclock_dininghall on  iclock_dininghall.id = pos_icconsumerlist.dining_id  
				   left join departments on departments.deptid = pos_icconsumerlist.dept_id 
				   left join personnel_meal on personnel_meal.id = pos_icconsumerlist.meal_id %s order by pos_time asc) AS RECORDS)
				   as b limit %s,%s 
				"
	},
	"pos.PosLog":{
				"params":"",
				"use_perms":"False",
				"verbose_fields":{
					"11":"posmodel"
				},
				"head":["{% trans '设备名称' %}","{% trans '序列号' %}","{% trans '设备流水号' %}","{% trans '卡流水号' %}","{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '部门编号' %}","{% trans '部门名称' %}","{% trans '卡号' %}","{% trans '餐厅名称' %}","{% trans '餐别名称' %}","{% trans '消费操作' %}","{% trans '消费金额' %}","{% trans '卡上余额' %}","{% trans '消费时间' %}"],
				"db_fields":["devname","sn","serialnum","card_serial_num","badgenumber","user_name","dept_code","dept_name","carno","din_name","meal_name","posmodel","posmoney","blance","posOpTime"],
				"sql":"
					SELECT devname,sn,serialnum,card_serial_num,badgenumber,user_name,dept_code,dept_name,carno,din_name,meal_name,posmodel,posmoney,blance,posOpTime FROM
					( 
						SELECT  devname,sn,serialnum,card_serial_num,badgenumber,user_name,dept_code,dept_name,carno,din_name,meal_name,posmodel,posmoney,blance,posOpTime FROM 
						(  
							SELECT  p.devname,p.sn,p.serialnum,p.card_serial_num,p.badgenumber,p.user_name,p.dept_code,p.dept_name,p.carno,h.name as din_name,m.name as meal_name,p.posmodel,p.posmoney,p.blance,p.posOpTime FROM 
							pos_poslog as p left join iclock_dininghall h on p.dining_id=h.id left join personnel_meal m on p.meal_id=m.id
							%s order by posOpTime asc 
							) AS RECORDS 
						) as b limit %s,%s
				"      
	},
	"pos.Allowance":{
				"params":"",
				"use_perms":"True",
				"verbose_fields":{
					"5":"is_ok",
					"6":"is_pass"
				},
				"head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '补贴金额' %}","{% trans '领取金额' %}","{% trans '补贴批次' %}","{% trans '是否领取' %}","{% trans '是否通过审核' %}","{% trans '审核通过人员' %}","{% trans '补贴领取时间' %}","{% trans '补贴有效日期' %}","{% trans '补贴时间' %}","{% trans '备注' %}"],
				"db_fields":["badgenumber","name","money","receive_money","batch","is_ok","is_pass","pass_name","receive_date","valid_date","allow_date","remark"],
				"sql":"
					SELECT badgenumber,name,money,receive_money,batch,is_ok,is_pass,pass_name,receive_date,valid_date,allow_date,remark  
					FROM (
						SELECT badgenumber,name,money,receive_money,batch,is_ok,is_pass,pass_name,receive_date,valid_date,allow_date,remark  
						FROM ( 
							SELECT u.badgenumber,u.name,i.sys_card_no,a.money,a.receive_money,a.batch,a.is_ok,a.is_pass,a.pass_name,a.receive_date,a.valid_date,allow_date,remark 
							FROM pos_allowance a left join userinfo u on a.user_id = u.userid  
							where 1=1 %s 
							order by allow_date desc 
						) as RECORDS %s
					) as b limit %s,%s
				",
				"admin_sql":"
					SELECT badgenumber,name,money,receive_money,batch,is_ok,is_pass,pass_name,receive_date,valid_date,allow_date,remark  
					FROM (
						SELECT badgenumber,name,money,receive_money,batch,is_ok,is_pass,pass_name,receive_date,valid_date,allow_date,remark  
						FROM ( 
							SELECT u.badgenumber,u.name,a.money,a.receive_money,a.batch,a.is_ok,a.is_pass,a.pass_name,a.receive_date,a.valid_date,allow_date,remark 
							FROM pos_allowance a left join userinfo u on a.user_id = u.userid  
							order by allow_date desc 
						) as RECORDS %s
					) as b limit %s,%s
					
				"
	}
	,
	"att.USER_OF_RUN":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "5":"Gender"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '班次名称' %}","{% trans '开始日期' %}","{% trans '结束日期' %}"],
            "db_fields":["badgenumber","Ename","name", "StartDate","EndDate"],
            "sql": "
                SELECT badgenumber,Ename,name,StartDate,EndDate 
				FROM (                                                
					SELECT badgenumber,Ename,name,StartDate,EndDate
				    FROM (               
						select r.userid,u.badgenumber,u.name as Ename,n.name, r.StartDate,r.EndDate
						    from user_of_run r
						    left join userinfo u on r.userid = u.userid
						    left join num_run n on n.Num_runID = r.NUM_OF_RUN_ID
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,name,StartDate,EndDate 
					FROM (
						SELECT badgenumber,Ename,name,StartDate,EndDate   
						FROM (
							select r.userid,u.badgenumber,u.name as Ename,n.name, r.StartDate,r.EndDate
							    from user_of_run r
							    left join userinfo u on r.userid = u.userid
							    left join num_run n on n.Num_runID = r.NUM_OF_RUN_ID
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
	,
	"att.USER_TEMP_SCH":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "4":"WorkType",
                "5":"Flag"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '开始日期' %}","{% trans '结束日期' %}","{% trans '工作类型' %}","{% trans '当天存在员工排班时' %}"],
			"db_fields":["badgenumber","Ename", "ComeTime","LeaveTime", "WorkType","Flag"],
            "sql": "
                SELECT badgenumber,Ename,ComeTime,LeaveTime,WorkType,Flag 
				FROM (                                                
					SELECT badgenumber,Ename,ComeTime,LeaveTime,WorkType,Flag
				    FROM (               
						select r.userid,u.badgenumber,u.name as Ename, r.ComeTime,r.LeaveTime,r.WorkType,r.Flag
						    from user_temp_sch r
						    left join userinfo u on r.userid = u.userid
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,ComeTime,LeaveTime,WorkType,Flag 
					FROM (
						SELECT badgenumber,Ename,ComeTime,LeaveTime,WorkType,Flag   
						FROM (
							select r.userid,u.badgenumber,u.name as Ename, r.ComeTime,r.LeaveTime,r.WorkType,r.Flag
							    from user_temp_sch r
							    left join userinfo u on r.userid = u.userid
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
	,
	"att.EmpSpecDay":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "7":"audit_status"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '开始日期' %}","{% trans '结束日期' %}","{% trans '请假原因' %}","{% trans '假类名称' %}","{% trans '填写日期' %}","{% trans '审核状态' %}","{% trans '审核原因' %}"],
			"db_fields":["badgenumber","Ename", "StartSpecDay","EndSpecDay", "YUANYING","LeaveName","DATE", "audit_status","audit_reason"],
            "sql": "
                SELECT badgenumber,Ename,StartSpecDay,EndSpecDay,YUANYING,LeaveName,Date,audit_status, audit_reason 
				FROM (                                                
					SELECT badgenumber,Ename,StartSpecDay,EndSpecDay,YUANYING,LeaveName,Date,audit_status, audit_reason 
				    FROM (               
						select r.userid,u.badgenumber,u.name as Ename, r.StartSpecDay,r.EndSpecDay,r.DateID, r.YUANYING,l.LeaveName,r.Date,r.audit_status,r.audit_reason
						    from user_speday r
						    left join userinfo u on r.userid = u.userid
						    left join leaveclass l on l.LeaveID = r.DateID
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,StartSpecDay,EndSpecDay,YUANYING,LeaveName,Date,audit_status, audit_reason 
					FROM (
						SELECT badgenumber,Ename,StartSpecDay,EndSpecDay,YUANYING,LeaveName,Date,audit_status, audit_reason  
						FROM (
							select r.userid,u.badgenumber,u.name as Ename, r.StartSpecDay,r.EndSpecDay,r.DateID,r.YUANYING,l.LeaveName,r.Date,r.audit_status,r.audit_reason
							    from user_speday r
							    left join userinfo u on r.userid = u.userid
							    left join leaveclass l on l.LeaveID = r.DateID
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
	,
	"att.CheckExact":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "3":"CHECKTYPE",
                "5":"audit_status"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '签卡日期' %}","{% trans '考勤状态' %}","{% trans '签卡原因' %}","{% trans '审核状态' %}","{% trans '审核原因' %}"],
			"db_fields":["badgenumber","Ename", "CHECKTIME","CHECKTYPE", "YUYIN","audit_status","audit_reason"],
            "sql": "
                SELECT badgenumber,Ename,CHECKTIME,CHECKTYPE,YUYIN,audit_status, audit_reason 
				FROM (                                                
					SELECT badgenumber,Ename,CHECKTIME,CHECKTYPE,YUYIN,audit_status, audit_reason 
				    FROM (               
						select r.userid,u.badgenumber,u.name as Ename, r.CHECKTIME,r.CHECKTYPE,r.YUYIN,r.audit_status,r.audit_reason
						    from checkexact r
						    left join userinfo u on r.userid = u.userid
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,CHECKTIME,CHECKTYPE,YUYIN,audit_status, audit_reason 
					FROM (
						SELECT badgenumber,Ename,CHECKTIME,CHECKTYPE,YUYIN,audit_status, audit_reason  
						FROM (
							select r.userid,u.badgenumber,u.name as Ename, r.CHECKTIME,r.CHECKTYPE,r.YUYIN,r.audit_status,r.audit_reason
							    from checkexact r
							    left join userinfo u on r.userid = u.userid
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
	,
	"att.OverTime":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
               "4":"audit_status"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '开始时间' %}","{% trans '结束时间' %}","{% trans '加班描述' %}","{% trans '审核状态' %}","{% trans '审核原因' %}"],
			"db_fields":["badgenumber","starttime","endtime", "remark","audit_status","auditreason"],
            "sql": "
                SELECT badgenumber,Ename,starttime,endtime,remark,audit_status, auditreason 
				FROM (                                                
					SELECT badgenumber,Ename,starttime,endtime,remark,audit_status, auditreason 
				    FROM (               
						select r.emp_id,u.badgenumber,u.name as Ename,r.starttime,r.endtime,r.remark,r.audit_status,r.auditreason
						    from att_overtime r
						    left join userinfo u on r.emp_id = u.userid
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,starttime,endtime,remark,audit_status, auditreason 
					FROM (
						SELECT badgenumber,Ename,starttime,endtime,remark,audit_status, auditreason  
						FROM (
							select r.emp_id,u.badgenumber,u.name as Ename,r.starttime,r.endtime,r.remark,r.audit_status,r.auditreason
							    from att_overtime r
							    left join userinfo u on r.emp_id = u.userid
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
    ,
	"att.SetUserAtt":{
            "params":"", 
            "use_perms":"True",  
            "verbose_fields":{
                "4":"atttype"
            },
            "head":["{% trans '人员编号' %}","{% trans '姓名' %}","{% trans '开始时间' %}","{% trans '结束时间' %}"],
			"db_fields":["badgenumber","Ename", "starttime","endtime"],
            "sql": "
                SELECT badgenumber,Ename,starttime,endtime  
				FROM (                                                
					SELECT badgenumber,Ename,starttime,endtime 
				    FROM (               
						select r.UserID_id,u.badgenumber,u.name as Ename, r.starttime,r.endtime
						    from setuseratt r
						    left join userinfo u on r.UserID_id = u.userid
						    where 1=1 %s
						    order by badgenumber asc
						) as RECORDS %s ) as b limit  %s, %s
                 ",
				"admin_sql":"
					SELECT badgenumber,Ename,starttime,endtime 
					FROM (
						SELECT badgenumber,Ename,starttime,endtime  
						FROM (
							select r.UserID_id,u.badgenumber,u.name as Ename, r.starttime,r.endtime
							    from setuseratt r
							    left join userinfo u on r.UserID_id = u.userid
							    order by badgenumber asc
							) as RECORDS %s
						) as b limit %s,%s
					
				"
    }
    
}
