中控消费软件数据库表结构.docx

1、中控消费软件数据库表结构消费机数据库构造文档(v2.0)数据表格图 人事根本资料表格 设备根本资料表 消费根本资料表系统管理根本表表详细字段Accounts帐套表字段字段类型字段说明ymaccount_nameVarchar(20)帐套名称begin_dateDatatime开场日期end_dateDatatime完毕日期Assignemp黑白表(挂失、解挂、改密)字段字段类型字段说明sys_idInt系统IDCard_idint卡号Clock_idint机器IDKindint类型(1白， 2黑，4已解挂的)Loss_moneydecimal挂失金额DoorsVarchar(10)无用Leve

2、lint无用Area_idVarchar(12)用户名称PassWordVarchar(12)用户密码PassTimeVarchar(30)无用OpDatedatetime操作日期OpUserVarchar(20)操作用户Emp_idVarchar(20)卡用户IDSegTimeVarchar(50)无用Privageint无用Batchtime批次时间段字段字段类型字段说明SegIDInt批次segnameVarchar(20)批次名称tsIDInt批次序号Start_timeVarchar(5)开场时间End_timeVarchar(5)完毕时间isvalidBit是否有效memoVarc

3、har(10)说明Cardinfo用户卡信息表字段字段类型字段说明empnoVarchar(32)用户代码empnameVarchar(20)用户名称sys_idInt系统idcard_idInt卡号issue_dateDatatime发卡时间card_balancedecimal卡余额isvalidBit是否有效card_sequInt卡流水号charge_verOnt补贴金额OrderdateDatetimeordertypeIntorderlevsmallintCardtype卡类表字段字段类型字段说明SortIDIntID号NameVarchar卡类名称rebateint折扣Dayma

4、xmoneysmallint每天最大金额TimemaxmoneySmallint每次最大金额MealmaxmoneySmallint每餐最大金额DaymaxtimesSmallint每天最大次数MealmaxtimesSmallint每餐最大次数LowlimitSmallint卡余额最低限额MaxlimitSmallint卡余额最大限额MealTypeint可用餐别limitSmallint有效使用的天数UseClockVarchar(255)可用机器BatchNoInt消费时段memoVarchar(50)说明UseFingerCardBit是否使用指纹卡Citys城市资料表字段字段类型字段

5、说明city_idIntID号city_nameVarchar(50)城市名称Clocks设备信息表字段字段类型字段说明Clock_idIntID号Clock_nameVarchar(50)机器名称Clock_typeVarchar(50)机器用途DinRoom_idVarchar(50)餐厅编号DinRoom_nameVarchar(50)餐厅名称PortInt通讯端口BaudRateInt波特率Clock_ModelVarchar(20)RemarkVarchar(50)备注TCPIP_AddressVarchar(15)TCP/IP地址TCPIP_PortintTCP/IP端口subne

6、t_addressVarchar(15)gateway_addressVarchar(15)server_addressVarchar(15)tcpip_typeInt联机方式mKeyint通讯密码hum_base多项表字段字段类型字段说明idint编号bnovarchar状态代码 DD* 打头 民族EE* 职位状态BB* 岗位GG* 离职原因FF* 离职形式AA* 职务bnamevarchar状态名称sortnointparentnovarcharremarkvarcharrelationmoney1decimalrelationmoney2decimalrelationmoney3deci

7、malrelationmoney4decimaloperatorvarchar操作员ModifyTimedatetime最好修改时间hum_change字段字段类型字段说明IdintBillNovarcharEmpNovarcharEmpNamevarcharItemNamevarcharOldValuevarcharNewValuevarcharCausevarcharHappenDtdatetimeremarkvarcharCheckFlagintCheckervarcharCheckTimedatetimeOperatorvarcharModifyTimedatetimeHappenFl

8、aginthum_change_item字段字段类型字段说明IdintItemNamevarcharItemDisplayvarcharremarkvarcharhum_class字段字段类型字段说明idintclassnovarcharclassnamevarcharremarkvarcharparentnovarcharsortnointrelationmoney1decimalrelationmoney2decimaloperatorvarcharmodifytimedatetimehum_department部门表字段字段类型字段说明deptnovarchar部门编号deptnamev

9、archar部门名称parentnovarchar上级部门编号sortnointamountdecimalamount_plandecimaladministratorvarchardeptdutyvarcharremarkvarcharscheduleidvarcharweekrestvarcharschflagvarcharhum_employee员工资料表字段字段类型字段说明EmpNamevarcharidvarchar卡号cardidvarchar物理卡号EmpNovarchar工号EmpSexvarchar性别nationvarchar民族birthdaydatetime出生日期fa

10、mily_addrvarchar地址family_telvarchar联系mobilevarcharnative_placevarchar籍贯CheckinDtdatetime入职时间DeptNovarchar根本资料|部门编号DeptNamevarchar根本资料|部门名称dutyvarchar职位classvarchartitlesvarcharEmpTypevarcharlz_datedatetime离职时间lz_causevarchar离职原因lz_modevarchar离职类型BlackFlagvarcharcontract_startdtdatetimecontract_enddt

11、datetimeofficial_datedatetimestatusvarchar员工状态Insurance_Novarcharwedlockvarchar婚否id_cardvarchar号photovarchar相片agedecimal年龄dormvarcharschoolvarchar毕业院校specialtyvarchardiplomavarchar学历id_card_datedatetimebank_account1varcharbank_account2varcharcuser1varcharcuser2varcharcuser3varcharcuser4varcharcuser5

12、varcharcuser6varcharissuedbit根本资料|是否发卡over_pwdvarcharcardtypetinyint卡类编号last_balancedecimallast_datedatetimecard_idint根本资料|卡号sys_idintissue_datedatetimeisregbit指纹已登记operator其它资料|操作员modifytime其它资料|操作时间remark其它资料|备注isdown指纹已下载表构造与hum_employee 根本一样hum_idaddress字段字段类型字段说明addrnovarchar辖区编号addrnamevarchar

13、辖区名称sortnointparentnovarchar上级辖区编号addrnovarcharKeydetail字段字段类型字段说明clock_idintDevnointKeyIDintPricedoubleMallowance补贴明细表字段字段类型字段说明idintemp_idvarchar工号sys_idintcard_idint卡号clock_idint机号depositdecimal录入金额chargedbit是否补贴downedbit是否下载charge_moneydecimal已补贴金额 charge_datedatetime补贴时间card_balancedecimal卡上余额c

14、ard_sequint卡流水号charge_verint补贴流水号op_uservarchar操作员op_datedatetime录入时间empnamevarcharMealrecords消费记录表字段字段类型字段说明emp_idvarchar工号card_idVarchar卡号deptnoVarchar部门编号sign_timeVarchar消费时间card_consumeVarchar消费金额card_balanceVarchar卡余额Card_SequVarchar卡流水号clock_idVarchar机号Pos_SequInt机器流水号flagbit标志kindVarchar餐别opc

15、ard_idVarchar操作员photoVarchar相片MAllowance补贴记录表字段字段类型字段说明emp_idVarchar工号card_idVarchar卡号empnameVarcharclock_idVarchar机号depositVarchar录入金额downedVarchar是否下载chargedVarchar是否补贴charge_moneyVarchar已补贴金额charge_dateVarchar补贴时间card_balanceVarchar卡上余额card_sequVarchar卡流水号op_userVarchar操作员op_dateVarchar录入时间charge

16、_verVarchar补贴流水号Dinroom餐厅表字段字段类型字段说明DinRoom_idvarchar 餐厅编号DinRoom_namevarchar餐厅名称Memvarchar备注Dinlev餐别字段字段类型字段说明DinLev_idvarchar 餐别编号DinLev_namevarchar 餐别名称DinLevPricedecimal餐别价格beg_timevarchar餐别开场时段end_timevarchar餐别完毕时段iscountbitisvalidbit 是否有效Memvarchar 备注Returncard退卡表字段字段类型字段说明emp_idvarchar工号sys_i

17、dintcard_idint卡号emp_namecard_balancedecimalcard_sequintcard_timeintreturn_datedatetime退卡时间op_uservarchar操作员empnamevarchardeptnovarchar部门编号deptnamevarchar部门名称OrdertimeSec餐别编号字段字段类型字段说明dinlev_idvarchar餐别编号dinlev_namevarchar餐别名称beg_timevarchar定餐开场时间end_timevarchar定餐完毕时间isvalidbit是否有效MemoVarchar备注Mealmo

18、nth月统计表字段字段类型字段说明idintaccount_monthvarchar月份usersvarcharcardcost_stardecimal卡费chargemoney_stardecimal充值金额chargeper_starint人次consume_stardecimal消费金额consumeper_starint人次refund_stardecimal退款金额refundper_starint人次balance_stardecimal卡余额TotalPer_starint期初|人事信息|总人数newaccountPer_starint期初|人事信息|新开户数changecard

19、Per_starint期初|人事信息|补卡数blackPer_starint期初|人事信息|挂失数whitePer_starint期初|人事信息|解挂数cardcostdecimal本期变化量|现金收入|卡费chargemoneydecimal本期变化量|现金收入|充值金额chargeperint本期变化量|现金收入|人次consumedecimal本期变化量|现金支出|消费金额consumeperint本期变化量|现金支出|人次refunddecimal本期变化量|现金支出|退款金额refundperint本期变化量|现金支出|人次balancedecimal本期变化量|卡余额TotalPe

20、rint本期变化量|人事信息|总人数newaccountPerint本期变化量|人事信息|新开户数changecardPerint本期变化量|人事信息|补卡数blackPerint本期变化量|人事信息|挂失数whitePerint本期变化量|人事信息|解挂数Mchargerecords 充值表字段字段类型字段说明idintemp_idvarchar工号sys_idintcard_idint卡号charge_moneydecimal充值金额card_balancedecimal卡余额card_timesintcard_sequint卡流水号Kindint类型op_datedatetimeop_y

21、mddatetimeremarkvarchar备注difine_sequintclock_idint机号opcard_idvarchar操作员empnamevarchardeptnovarchar部门编号deptname varchar部门名称Fixmoney分段定值表字段字段类型字段说明secidvarchar编号beg_timevarchar开场时间end_timevarchar完毕时间pricedecimal定值金额isvalidbit是否有效memovarchar备注Store 商品信息表字段字段类型字段说明StoreNoint商品编号StoreNamevarchar商品名称Bcvar

22、char条码Pricedecimal单价agioint折扣Timesec固定时间段表字段字段类型字段说明secidint时间段编号beg_timevarchar开场时间end_timevarchar完毕时间isvalidbit是否有效memovarchar备注Presskey键值信息表字段字段类型字段说明KeyIDint按键编号Pricedecimal单价Natives省份自治区表字段字段类型字段说明native_idvarchar 编号native_namevarchar名称pany注册公司信息表字段字段类型字段说明pany_idvarchar公司代码pany_ch_namevarchar公

23、司中文名称pany_en_namevarchar公司英文名称pany_addressvarchar公司地址phone_codevarchar联系fax_codevarchar principalvarchar联系人machine_codevarchar机器代码serial_novarchar注册码managervarchar负责人parameterslongtextlogolongbloblate_imagelongtextleave_imagelongtextnocard_imagelongtextimage_sizeintreal_colorlongtexttextfmtlongtexttimeparamlongtextpany_ varchar公司网址pany_login_namevarchar注册中文名称set_fontlongtextset_colorvarcharbuild_datevarchar创立日期version_idvarcharis_real_mealbitweek_paramlongtextmonth_paramlongtextphotovarcharArea区域表字段字段类型字段说明Area_idvarchar区域编号reaNameVarchar区域名称