自制U8简易总账工具
一个客户的U8软件为10.1版本，但是
在使用总账工具的时候后台账套库莫名其妙会出现重复的凭证，
重装U8软件也不行，最后觉得应该打上补丁就可以解决，由于SPS过期导致补丁打不上，最后想给他们
做个简易的总账工具加以解决，虽然当时我在后台账套库加了一个触发器解决了这个问题，但是还是将这个总账工具贴出来
这里面比用友的总账 工具多了一个“异常清理”按钮，主要是用来 消除 数据库出现重复的凭证
后台用的是python 写的
源代码打包传送门：https://github.com/WhsGhsot/U8GLTool
Server.py
#!C:\Users\Administrator\AppData\Local\Programs\Python\Python36\python.exe
import cgi
from OpeDatabase import *
from Database import *
import json
form =cgi.FieldStorage()
opeDatabase=OpeDatabase()
#接收前台传来的连接数据库信息
print('Content-type:text/html\n')
flag=form['flag'].value
if(flag=='connServer'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
status=opeDatabase.connData(server=serName,password=serPas)
if(status=='fail'):
print('fail')
else:
databases=opeDatabase.selDatabases('master','1=1')
jsonStr=json.dumps(databases)
print(jsonStr)
elif(flag=='ino_ids'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
iyear=form['iyear'].value
iperiod=form['iperiod'].value
condition=(' iyear=%s and iperiod=%s'%(iyear,iperiod))
data=opeDatabase.selIno_ids(sourceDatabase,condition)
#导入档案
condition=(' ccode not in (select ccode from [%s].[dbo].[gl_accvouch] where iyear=%s and iperiod=%s)'
%(targetDatabase,iyear,iperiod))
opeDatabase.InsCode(sourceDatabase,targetDatabase,condition)
condition=(' cdepcode not in (select cdept_id from [%s].[dbo].[gl_accvouch] where iyear=%s and iperiod=%s)'
%(targetDatabase,iyear,iperiod))
opeDatabase.InsDepartment(sourceDatabase,targetDatabase,condition)
condition=(' ccuscode not in (select ccus_id from [%s].[dbo].[gl_accvouch] where iyear=%s and iperiod=%s)'
%(targetDatabase,iyear,iperiod))
opeDatabase.InsCustomer(sourceDatabase,targetDatabase,condition)
opeDatabase.InsVc(sourceDatabase,targetDatabase,'1=1')
condition=(' cvencode not in (select csup_id from [%s].[dbo].[gl_accvouch] where iyear=%s and iperiod=%s)'
%(targetDatabase,iyear,iperiod))
opeDatabase.InsVendor(sourceDatabase,targetDatabase,condition)
condition=(' cpersoncode not in (select cperson_id from [%s].[dbo].[gl_accvouch] where iyear=%s and iperiod=%s)'
%(targetDatabase,iyear,iperiod))
opeDatabase.InsPerson(sourceDatabase,targetDatabase,condition)
opeDatabase.InsHip(sourceDatabase,targetDatabase,'1=1')
if(len(data)==0):
print('fail')
else:
jsonStr=json.dumps(data)
print(jsonStr)
elif(flag=='copy'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
iyear=form['iyear'].value
iperiod=form['iperiod'].value
ino_id=form['ino_id'].value
condition=(' iyear=%s and iperiod=%s and ino_id=%s'%(iyear,iperiod,ino_id))
status=opeDatabase.InsertGl_vch(sourceDatabase,targetDatabase,condition)
print(status)
elif(flag=='clear'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
targetDatabase=form['targetDatabase'].value
status=opeDatabase.DelGlVch(targetDatabase,'1=1')
print(status)
elif(flag=='codeInput'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
status=opeDatabase.InsCode(sourceDatabase,targetDatabase,'1=1')
print(status)
elif(flag=='customerInput'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
status=opeDatabase.InsCustomer(sourceDatabase,targetDatabase,'1=1')
print(status)
elif(flag=='vendorInput'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
status=opeDatabase.InsVc(sourceDatabase,targetDatabase,'1=1')
status=opeDatabase.InsVendor(sourceDatabase,targetDatabase,'1=1')
print(status)
elif(flag=='personInput'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
status=opeDatabase.InsPerson(sourceDatabase,targetDatabase,'1=1')
status=opeDatabase.InsHip(sourceDatabase,targetDatabase,'1=1')
print(status)
elif(flag=='departmentInput'):
serName=form['serName'].value
serPas=form['serPas'].value
if(serPas=='空'):
serPas=''
if(serName=='空'):
serName=''
opeDatabase.connData(server=serName,password=serPas)
sourceDatabase=form['sourceDatabase'].value
targetDatabase=form['targetDatabase'].value
status=opeDatabase.InsDepartment(sourceDatabase,targetDatabase,'1=1')
print(status)
Database.py
"""底层数据API"""
"""数据库类 封装关于数据库的各种操作方法"""
import pymssql
from Table import *
class Database:
#生成游标
def CreateCursor(self):
self.cursor=self.conn.cursor()
return self.cursor
#关闭数据库
def CloseConn(self,conn):
conn.close()
#server 数据库服务器名称
#user 用户名
#password 密码
#database 数据库名称
#connectState 连接状态 success表示成功 fail表示失败
def ConnData(self,server,password,user="sa",database="master"):
try:
#print(server,password)
self.conn=pymssql.connect(server,user,password,database)
#生成游标
self.CreateCursor()
return 'success'
except:
return 'fail'
"""数据库 insert select update delete """
#选择操作,根据传来的flag进行查询 flag='code','customer','vendorclass','vendor','person','hr_hi_person' 'sysdatabases'
#targetDatabase 目标数据库 candition 查询条件
def selTable(self,targetDatabase,condition,flag):
sqlStr=''
if(flag=='code'):
sqlStr= sqlStr=('select ccode from [%s].[dbo].[code] where %s'%(targetDatabase,condition))
elif(flag=='customer'):
sqlStr=('select ccuscode from [%s].[dbo].[customer] where %s'%(targetDatabase,condition))
elif(flag=='vendorclass'):
sqlStr=('select cvccode from [%s].[dbo].[vendorclass] where %s'%(targetDatabase,condition))
elif(flag=='vendor'):
sqlStr=('select cvencode from [%s].[dbo].[vendor] where %s'%(targetDatabase,condition))
elif(flag=='person'):
sqlStr=('select cpersoncode from [%s].[dbo].[person] where %s'%(targetDatabase,condition))
elif(flag=='hr_hi_person'):
sqlStr=('select cpsn_num from [%s].[dbo].[hr_hi_person] where %s'%(targetDatabase,condition))
elif(flag=='sysdatabases'):
sqlStr=('select name from [%s].[dbo].[sysdatabases] where %s'
%(targetDatabase,"name like 'UFDATA_%' and "+condition))
#print("查询语句:",sqlStr)
elif(flag=='maxIno_id'):
sqlStr=('select max(ino_id) from [%s].[dbo].[gl_accvouch] where %s'
%(targetDatabase,condition))
elif(flag=='ino_id'):
sqlStr=('select ino_id from [%s].[dbo].[gl_accvouch] where %s group by ino_id '
%(targetDatabase,condition))
elif(flag=='department'):
sqlStr=('select cdepcode from [%s].[dbo].[department] where %s'%(targetDatabase,condition))
self.cursor.execute(sqlStr)
values=[]
row=self.cursor.fetchone()
while row:
values.append(row[0])
row=self.cursor.fetchone()
return values
#主要是插入基础档案的
#插入操作,根据传来的flag进行查询 flag='code','customer','vendorclass','vendor','person','hr_hi_person'
#targetDatabase 目标数据库 condition 查询条件
def InsTable(self,sourceData,targetData,condition,flag):
sqlStr=''
if(flag=='code'):
sqlStr=(r'insert into [%s].[dbo].[code](%s) select %s from [%s].[dbo].[code] where %s'
r' and ccode not in (select ccode from [%s].[dbo].[code])'
%(targetData,Table.table['code'],Table.table['code'],sourceData,condition,targetData))
elif(flag=='customer'):
sqlStr=(r'insert into [%s].[dbo].[customer](%s) select %s from [%s].[dbo].[customer] where %s'
r' and ccuscode not in (select ccuscode from [%s].[dbo].[customer])'
%(targetData,Table.table['customer'],Table.table['customer'],sourceData,condition,targetData))
elif(flag=='vendorclass'):
sqlStr=(r'insert into [%s].[dbo].[vendorclass](%s) select %s from [%s].[dbo].[vendorclass] where %s'
r' and cvccode not in (select cvccode from [%s].[dbo].[vendorclass])'
%(targetData,Table.table['vendorclass'],Table.table['vendorclass'],sourceData,condition,targetData))
elif(flag=='vendor'):
sqlStr=(r'insert into [%s].[dbo].[vendor](%s) select %s from [%s].[dbo].[vendor] where %s and '
r' cvencode not in (select cvencode from [%s].[dbo].[vendor])'
%(targetData,Table.table['vendor'],Table.table['vendor'],sourceData,condition,targetData))
elif(flag=='person'):
sqlStr=(r'insert into [%s].[dbo].[person](%s) select %s from [%s].[dbo].[person] where %s and '
r' cpersoncode not in (select cpersoncode from [%s].[dbo].[person])'
%(targetData,Table.table['person'],Table.table['person'],sourceData,condition,targetData))
elif(flag=='hr_hi_person'):
sqlStr=(r'insert into [%s].[dbo].[hr_hi_person](%s) select %s from [%s].[dbo].[hr_hi_person] where %s and cpsn_num not in (select cpsn_num from [%s].[dbo].[hr_hi_person] )'
%(targetData,Table.table['hr_hi_person'],Table.table['hr_hi_person'],sourceData,condition,targetData))
elif(flag=='department'):
sqlStr=(r'insert into [%s].[dbo].[department](%s) select %s from [%s].[dbo].[department] where %s '
r' and cdepcode not in (select cdepcode from [%s].[dbo].[department])'
%(targetData,Table.table['department'],Table.table['department'],sourceData,condition, targetData))
try:
self.cursor.execute(sqlStr)
self.conn.commit()
return 'success'
except:
return 'fail'
#插入凭证专用方法
#sourceData:源数据库 targetData：目标数据库 condition:插入条件
def InsertGl_vch(self,sourceData,targetData,condition):
#得到最大Ino_id
maxIno_id=0
ino_id=self.selTable(targetData,'1=1','maxIno_id')
if(len(ino_id)>=1):
if(ino_id[0]!=None):
maxIno_id=ino_id[0]
ino_Id="'"+str(int(maxIno_id)+1)+"'"
strs=(Table.table['gl_InsVch'] %(ino_Id))
sqlStr=('insert into [%s].[dbo].[gl_accvouch](%s) select %s from [%s].[dbo].[gl_accvouch] where %s'
%(targetData,Table.table['gl_accvouch'],strs,sourceData,condition))
try:
self.cursor.execute(sqlStr)
self.conn.commit()
return 'success'
except :
return 'fail'
#删除,根据传来的flag进行查询 flag='code','customer','vendorclass','vendor','person','hr_hi_person'
#targetDatabase 目标数据库 sourceData 源数据库 condition 查询条件
def DelTable(self,targetData,condition,flag):
sqlStr=('delete from [%s].[dbo].[%s] where %s'%(targetData,flag,condition))
if(flag=='gl_accvouch'):
sqlStr=('delete from [%s].[dbo].[%s] where %s and ibook=0'%(targetData,flag,condition))
try:
self.cursor.execute(sqlStr)
self.conn.commit()
return 'success'
except:
return 'fail'
OpeDatabase.py
"""中间层 传输数据"""
"""select update insert delete """
from Database import *
class OpeDatabase:
#初始化创建数据库实例
def __init__(self):
self.conn=Database()
#连接数据库,连接成功后建立游标 server 为数据库实例 password为数据库密码
def connData(self,server,password):
status=self.conn.ConnData(server,password)
#数据库连接成功 生成游标
if(status=='success'):
self.CreateCursor()
return 'success'
#数据库连接失败 返回给用户
else:
return 'fail'
#生成游标
def CreateCursor(self):
self.cursor= self.conn.CreateCursor()
#关闭数据库
def CloseConn(self):
self.conn.CloseConn(self.conn)
#选择目的账套的会计科目的id targetDatabase为目的账套 candition为条件 --未实现
#return ccodes:[]
def selCodeId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'code')
#选择目的账套的客户档案的id targetDatabase为目的账套 candition为条件 --未实现
#return cccodes:[]
def selCusId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'customer')
#选择目的账套的供应商分类的id targetDatabase为目的账套 candition为条件 --未实现
#return cvccodes:[]
def selVCId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'vendorclass')
#选择目的账套的供应商id targetDatabase为目的账套 candition为条件
def selVId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'vendor')
#选择目的账套个人档案id targetDatabase为目的账套 candition为条件 --未实现
def selPId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'person')
#选择目的账套部门档案id targetDatabase为目的账套 candition为条件 --未实现
def selDeId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'department')
#选择目的账套人事档案的个人id targetDatabase为目的账套 candition为条件 --未实现
def selHiId(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'hr_hi_person')
#选择全部账套信息
def selDatabases(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'sysdatabases')
#选择目的账套最大的ino_id
def selMaxIno_id(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'maxIno_id')
#选择目标账套的ino_ids return ino_ids:[]
def selIno_ids(self,targetDatabase,condition):
return self.conn.selTable(targetDatabase,condition,'ino_id')
#插入code sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsCode(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'code')
#插入Customer sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsCustomer(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'customer')
#插入Vendorclass sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsVc(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'vendorclass')
#插入Vendor sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsVendor(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'vendor')
#插入person sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsPerson(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'person')
#插入hr_hi_person sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def InsHip(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'hr_hi_person')
def InsertGl_vch(self,sourceData,targetData,condition):
return self.conn.InsertGl_vch(sourceData,targetData,condition)
def InsDepartment(self,sourceData,targetData,condition):
return self.conn.InsTable(sourceData,targetData,condition,'department')
#删除code sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelCode(self,targetData,condition):
return self.conn.DelTable(targetData,condition,'code')
#删除Customer sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelCustomer(self,targetData,condition):
return self.conn.DelTable(targetData,condition,'customer')
#删除Vendorclass sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelVc(self,targetData,condition):
return self.conn.DelTable(targetData,condition,'vendorclass')
#删除Vendor sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelVendor(self,sourceData,targetData,condition):
return self.conn.DelTable(targetData,condition,'vendor')
#删除person sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelPerson(self,targetData,condition):
return self.conn.DelTable(sourceData,targetData,condition,'person')
#删除hr_hi_person sourceData 为源数据库 TargetData为目的数据库 candition为条件 -未实现
def DelHip(self,targetData,condition):
return self.conn.DelTable(targetData,condition,'hr_hi_person')
#删除异常(未记账凭证)
def DelGlVch(self,targetData,condition):
return self.conn.DelTable(targetData,condition,'gl_accvouch')
Table.py
"""数据库表"""
class Table:
table={'gl_InsVch':r"""[iperiod],[csign],[isignseq],%s as ino_id,[inid],[dbill_date],[idoc],[cbill],[ccheck],
[cbook],[ibook],[ccashier],[iflag],[ctext1],[ctext2],[cdigest],[ccode],[cexch_name],[md],[mc],[md_f],[mc_f],
[nfrat],[nd_s],[nc_s],[csettle],[cn_id],[dt_date],[cdept_id],[cperson_id],[ccus_id],[csup_id],[citem_id]
,[citem_class],[cname],[ccode_equal],[iflagbank],[iflagPerson],[bdelete],[coutaccset],[ioutyear],
[coutsysname],[coutsysver],[doutbilldate],[ioutperiod],[coutsign],[coutno_id],[doutdate],[coutbillsign],
[coutid],[bvouchedit],[bvouchAddordele],[bvouchmoneyhold],[bvalueedit],[bcodeedit],[ccodecontrol],[bPCSedit],
[bDeptedit],[bItemedit],[bCusSupInput],[dReceive],[cWLDZFlag],[dWLDZTime],[bFlagOut],[iBG_OverFlag],
[cBG_Auditor],[dBG_AuditTime],[cBG_AuditOpinion],[bWH_BgFlag],[ssxznum],[CErrReason],[BG_AuditRemark],
[cBudgetBuffer],[iBG_ControlResult],[NCVouchID],[daudit_date],[cBankReconNo],[iyear],[iYPeriod],[wllqDate],
[wllqPeriod],[tvouchtime],[cblueoutno_id],[ccodeexch_equal],[cpzchcode]""",
'gl_accvouch':r"""[iperiod],[csign],[isignseq],ino_id,[inid],[dbill_date],[idoc],[cbill],[ccheck],
[cbook],[ibook],[ccashier],[iflag],[ctext1],[ctext2],[cdigest],[ccode],[cexch_name],[md],[mc],[md_f],[mc_f],
[nfrat],[nd_s],[nc_s],[csettle],[cn_id],[dt_date],[cdept_id],[cperson_id],[ccus_id],[csup_id],[citem_id]
,[citem_class],[cname],[ccode_equal],[iflagbank],[iflagPerson],[bdelete],[coutaccset],[ioutyear],
[coutsysname],[coutsysver],[doutbilldate],[ioutperiod],[coutsign],[coutno_id],[doutdate],[coutbillsign],
[coutid],[bvouchedit],[bvouchAddordele],[bvouchmoneyhold],[bvalueedit],[bcodeedit],[ccodecontrol],[bPCSedit],
[bDeptedit],[bItemedit],[bCusSupInput],[dReceive],[cWLDZFlag],[dWLDZTime],[bFlagOut],[iBG_OverFlag],
[cBG_Auditor],[dBG_AuditTime],[cBG_AuditOpinion],[bWH_BgFlag],[ssxznum],[CErrReason],[BG_AuditRemark],
[cBudgetBuffer],[iBG_ControlResult],[NCVouchID],[daudit_date],[cBankReconNo],[iyear],[iYPeriod],[wllqDate],
[wllqPeriod],[tvouchtime],[cblueoutno_id],[ccodeexch_equal],[cpzchcode]""",
'code':r"""[cclass],[cclass_engl],[ccode],[ccode_name],[igrade],[bproperty],[cbook_type],
[cbook_type_engl] ,[cmeasure],[bperson],[bcus],[bsup],[bdept],[bitem],[cass_item],[br],[be],
[bend],[bexchange],[bcash],[bbank],[bused],[bd_c],[dbegin],[dend],[itrans],[bclose],
[cother],[iotherused],[iViewItem],[bGCJS],[bCashItem],[bReport],[cUserDefineType],[iyear],
[dModifyDate],[bparacc]""",
'customer':r""" [cCusCode]
,[cCusName]
,[cCusAbbName]
,[cCCCode]
,[cDCCode]
,[cTrade]
,[cCusAddress]
,[cCusPostCode]
,[cCusRegCode]
,[cCusBank]
,[cCusAccount]
,[dCusDevDate]
,[cCusLPerson]
,[cCusEmail]
,[cCusPerson]
,[cCusPhone]
,[cCusFax]
,[cCusBP]
,[cCusHand]
,[cCusPPerson]
,[iCusDisRate]
,[cCusCreGrade]
,[iCusCreLine]
,[iCusCreDate]
,[cCusPayCond]
,[cCusOAddress]
,[cCusOType]
,[cCusHeadCode]
,[cCusWhCode]
,[cCusDepart]
,[iARMoney]
,[dLastDate]
,[iLastMoney]
,[dLRDate]
,[iLRMoney]
,[dEndDate]
,[iFrequency]
,[cCusDefine1]
,[cCusDefine2]
,[cCusDefine3]
,[iCostGrade]
,[cCreatePerson]
,[cModifyPerson]
,[dModifyDate]
,[cRelVendor]
,[iId]
,[cPriceGroup]
,[cOfferGrade]
,[iOfferRate]
,[cCusDefine4]
,[cCusDefine5]
,[cCusDefine6]
,[cCusDefine7]
,[cCusDefine8]
,[cCusDefine9]
,[cCusDefine10]
,[cCusDefine11]
,[cCusDefine12]
,[cCusDefine13]
,[cCusDefine14]
,[cCusDefine15]
,[cCusDefine16]
,[cInvoiceCompany]
,[bCredit]
,[bCreditDate]
,[bCreditByHead]
,[bLicenceDate]
,[dLicenceSDate]
,[dLicenceEDate]
,[iLicenceADays]
,[bBusinessDate]
,[dBusinessSDate]
,[dBusinessEDate]
,[iBusinessADays]
,[bProxy]
,[dProxySDate]
,[dProxyEDate]
,[iProxyADays]
,[cMemo]
,[bLimitSale]
,[cCusContactCode]
,[cCusCountryCode]
,[cCusEnName]
,[cCusEnAddr1]
,[cCusEnAddr2]
,[cCusEnAddr3]
,[cCusEnAddr4]
,[cCusPortCode]
,[cPrimaryVen]
,[fCommisionRate]
,[fInsueRate]
,[bHomeBranch]
,[cBranchAddr]
,[cBranchPhone]
,[cBranchPerson]
,[cCusTradeCCode]
,[CustomerKCode]
,[bCusState]
,[bShop]
,[cCusBankCode]
,[cCusExch_name]
,[iCusGSPType]
,[iCusGSPAuth]
,[cCusGSPAuthNo]
,[cCusBusinessNo]
,[cCusLicenceNo]
,[bCusDomestic]
,[bCusOverseas]
,[cCusCreditCompany]
,[cCusSAProtocol]
,[cCusExProtocol]
,[cCusOtherProtocol]
,[fCusDiscountRate]
,[cCusSSCode]
,[cCusCMProtocol]
,[dCusCreateDatetime]
,[cCusAppDocNo]
,[cCusMnemCode]
,[fAdvancePaymentRatio]
,[bServiceAttribute]
,[bRequestSign]
,[bOnGPinStore]
,[cCusMngTypeCode]
,[account_type]
,[cCusImAgentProtocol]
,[iSourceType]
,[iSourceId]
,[fExpense]
,[fApprovedExpense]
,[dTouchedTime]
,[dRecentlyInvoiceTime]
,[dRecentlyQuoteTime]
,[dRecentlyActivityTime]
,[dRecentlyChanceTime]
,[dRecentlyContractTime]
,[cLtcCustomerCode]
,[bTransFlag]
,[cLtcPerson]
,[dLtcDate]
,[cLocationSite]
,[iCusTaxRate]
,[alloct_dept_time]
,[allot_user_time]
,[recyle_dept_time]
,[recyle_pub_time]
,[cLicenceNo]
,[cLicenceRange]
,[cCusBusinessRange]
,[cCusGSPAuthRange]
,[dCusGSPEDate]
,[dCusGSPSDate]
,[iCusGSPADays]
,[bIsCusAttachFile]
,[dRecentContractDate]
,[dRecentDeliveryDate]
,[dRecentOutboundDate]
,[cProvince]
,[cCity]
,[cCounty]
,[cCusAddressGUID]
,[cAddCode]
,[cCreditAddCode]
,[cRegCash]
,[dDepBeginDate]
,[iEmployeeNum]
,[cURL]
,[PictureGUID]""",
'person':r"""[cPersonCode]
,[cPersonName]
,[cDepCode]
,[cPersonProp]
,[fCreditQuantity]
,[iCreDate]
,[cCreGrade]
,[iLowRate]
,[cOfferGrade]
,[iOfferRate]
,[cPersonEmail]
,[cPersonPhone]
,[dPValidDate]
,[dPInValidDate]""",
'hr_hi_person':r"""[cPsn_Num]
,[cPsn_Name]
,[cDept_num]
,[iRecordID]
,[rPersonType]
,[rSex]
,[dBirthDate]
,[rNativePlace]
,[rNational]
,[rhealthStatus]
,[rMarriStatus]
,[vIDNo]
,[MPicture]
,[rPerResidence]
,[vAliaName]
,[dJoinworkDate]
,[dEnterDate]
,[dRegularDate]
,[vSSNo]
,[rworkAttend]
,[vCardNo]
,[rtbmRule]
,[rCheckInFlag]
,[dLastDate]
,[vstatus1]
,[nstatus2]
,[bPsnPerson]
,[cPsnMobilePhone]
,[cPsnFPhone]
,[cPsnOPhone]
,[cPsnInPhone]
,[cPsnEmail]
,[cPsnPostAddr]
,[cPsnPostCode]
,[cPsnFAddr]
,[cPsnQQCode]
,[cPsnURL]
,[CpsnOSeat]
,[dEnterUnitDate]
,[cPsnProperty]
,[cPsnBankCode]
,[cPsnAccount]
,[pk_hr_hi_person]
,[bProbation]
,[cDutyclass]
,[bTakeTM]
,[MPictureqpb]
,[rIDType]
,[rCountry]
,[dLeaveDate]
,[rFigure]
,[rWorkStatus]
,[EmploymentForm]
,[rPersonParameters]
,[bDutyLock]
,[bpsnshop]
,[CardState]
,[FirstHTBeginDate]
,[LastHTEndDate]
,[cPsn_NameEN]
,[sysAge]
,[SysCompage]
,[SysWorkAge]
,[CRegion]
,[NatrueType]
,[cShiftType]
,[cDefaultBC]
,[dEnterDutyClassDate]
,[cRestType]
,[JobNumber]
,[PersonDepart]
,[CardEffectiveBeginTime]
,[CardEffectiveEndTime]
,[KeyPerson]
,[cLeaveReson]
,[rEmployState]
,[cDutyLocker]
,[cDutyLockerNum]
,[cJobCode]
,[cJobRankCode]
,[cJobGradeCode]
,[cPsnNowAddress]
,[cUrgentPsnName]
,[cUrgentPsnPhone]
,[bLongIDCard]
,[cLeaveType]
,[cDutyCode]
,[cFax]
,[cTitle]
,[cReportTo]
,[cStartWorkTime]
,[cEndWorkTime]
,[cLoginSubIDs]
,[cpersonbarcode]
,[bMobileCard]
,[cMobileSites]
,[vOldCard]
,[MaxLeadNum]
,[MaxPAccountNum]
,[MaxAccountNum]
,[MaxOpportunityNum]
,[LeadRcyReg]
,[PAccountRcyReg]
,[OpportunityRcyReg]
,[cESpaceUserID]
,[cWeixinid]
,[FAccountRcyReg]""",
'vendorclass':r"""[cVCCode]
,[cVCName]
,[iVCGrade]
,[bVCEnd]""",
'vendor':r"""[cVenCode]
,[cVenName]
,[cVenAbbName]
,[cVCCode]
,[cDCCode]
,[cTrade]
,[cVenAddress]
,[cVenPostCode]
,[cVenRegCode]
,[cVenBank]
,[cVenAccount]
,[dVenDevDate]
,[cVenLPerson]
,[cVenPhone]
,[cVenFax]
,[cVenEmail]
,[cVenPerson]
,[cVenBP]
,[cVenHand]
,[cVenPPerson]
,[iVenDisRate]
,[iVenCreGrade]
,[iVenCreLine]
,[iVenCreDate]
,[cVenPayCond]
,[cVenIAddress]
,[cVenIType]
,[cVenHeadCode]
,[cVenWhCode]
,[cVenDepart]
,[iAPMoney]
,[dLastDate]
,[iLastMoney]
,[dLRDate]
,[iLRMoney]
,[dEndDate]
,[iFrequency]
,[bVenTax]
,[cVenDefine1]
,[cVenDefine2]
,[cVenDefine3]
,[cCreatePerson]
,[cModifyPerson]
,[dModifyDate]
,[cRelCustomer]
,[iId]
,[cBarCode]
,[cVenDefine4]
,[cVenDefine5]
,[cVenDefine6]
,[cVenDefine7]
,[cVenDefine8]
,[cVenDefine9]
,[cVenDefine10]
,[cVenDefine11]
,[cVenDefine12]
,[cVenDefine13]
,[cVenDefine14]
,[cVenDefine15]
,[cVenDefine16]
,[fRegistFund]
,[iEmployeeNum]
,[iGradeABC]
,[cMemo]
,[bLicenceDate]
,[dLicenceSDate]
,[dLicenceEDate]
,[iLicenceADays]
,[bBusinessDate]
,[dBusinessSDate]
,[dBusinessEDate]
,[iBusinessADays]
,[bProxyDate]
,[dProxySDate]
,[dProxyEDate]
,[iProxyADays]
,[bPassGMP]
,[bVenCargo]
,[bProxyForeign]
,[bVenService]
,[cVenTradeCCode]
,[cVenBankCode]
,[cVenExch_name]
,[iVenGSPType]
,[iVenGSPAuth]
,[cVenGSPAuthNo]
,[cVenBusinessNo]
,[cVenLicenceNo]
,[bVenOverseas]
,[bVenAccPeriodMng]
,[cVenPUOMProtocol]
,[cVenOtherProtocol]
,[cVenCountryCode]
,[cVenEnName]
,[cVenEnAddr1]
,[cVenEnAddr2]
,[cVenEnAddr3]
,[cVenEnAddr4]
,[cVenPortCode]
,[cVenPrimaryVen]
,[fVenCommisionRate]
,[fVenInsueRate]
,[bVenHomeBranch]
,[cVenBranchAddr]
,[cVenBranchPhone]
,[cVenBranchPerson]
,[cVenSSCode]
,[cOMWhCode]
,[cVenCMProtocol]
,[cVenIMProtocol]
,[iVenTaxRate]
,[dVenCreateDatetime]
,[cVenMnemCode]
,[cVenContactCode]
,[cvenbankall]
,[bIsVenAttachFile]
,[cLicenceRange]
,[cBusinessRange]
,[cVenGSPRange]
,[dVenGSPEDate]
,[dVenGSPSDate]
,[iVenGSPADays]""",
'department':r"""[cDepCode]
,[bDepEnd]
,[cDepName]
,[iDepGrade]
,[cDepPerson]
,[cDepProp]
,[cDepPhone]
,[cDepAddress]
,[cDepMemo]
,[iCreLine]
,[cCreGrade]
,[iCreDate]
,[cOfferGrade]
,[iOfferRate]
,[bShop]
,[cDepGUID]
,[dDepBeginDate]
,[dDepEndDate]
,[vAuthorizeDoc]
,[vAuthorizeUnit]
,[cDepFax]
,[cDepPostCode]
,[cDepEmail]
,[cDepType]
,[bInheritDutyBasic]
,[bInheritWorkCalendar]
,[cDutyCode]
,[cRestCode]
,[bIM]
,[cDepNameEn]
,[bRetail]
,[cDepFullName]
,[iDepOrder]
,[cDepLeader]
,[dModifyDate]
,[cESpaceMembID]"""
}index.html
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title></title>
</head>
<style type="text/css">
.section_title
{
text-align: center;
font-size: 20px;
color:#A8A8A8;
}
.section_server,.section_tools,.section_detail
{
margin-left: 30%;
}
button
{
margin-left: 10px;
}
.operition
{
margin-left: 20%;
}
thead
{
align: center;
color:#D5B959;
}
tr td:nth-child(1)
{
width: 100px;
}
td{
text-align: center;
}
</style>
<script type="text/javascript">
var ipConfig='127.0.0.1'
var cookieUtil={
get: function (name) {
var cookieName=encodeURIComponent(name)+'=',
cookieStart=document.cookie.indexOf(cookieName),
cookieValue=null;
if(cookieStart>-1)
{
var cookieEnd=document.cookie.indexOf(';',cookieStart);
if(cookieEnd==-1)
{
cookieEnd=document.cookie.length;
}
cookieValue=decodeURIComponent(document.cookie.substring(cookieStart+cookieName.length,cookieEnd));
}
return cookieValue;
},
set:function(name,value)
{
var cookieText=encodeURIComponent(name)+"="+encodeURIComponent(value);
document.cookie=cookieText;
}
}
/*加载页面后调用函数*/
function load()
{
var serName=cookieUtil.get('serName');
var serPas=cookieUtil.get('serPas');
ipConfig=cookieUtil.get('ip')==null?'127.0.0.1':cookieUtil.get('ip');
(document.getElementsByName("serName")[0]).value=serName;
(document.getElementsByName("serPas")[0]).value=serPas;
(document.getElementById('ip')).value=ipConfig;
/*初始化禁用所有按钮*/
var button=document.getElementsByTagName('button');
for(var i=0;i<button.length;i++)
{
button[i].disabled='disabled';
}
}
/*url字符串连接*/
function addURLParam(url,name,value)
{
url+=(url.indexOf('?')==-1?'?':'&');
url+=encodeURIComponent(name)+'='+encodeURIComponent(value);
return url;
}
/*服务器连接函数*/
function connServer()
{
//得到服务器IP
var ip=(document.getElementById("ip")).value;
if(ip=="")
{
alert('请输入服务器IP');
return;
}
//将Ip地址加入cookie
cookieUtil.set('ip',ip);
var serName=(document.getElementsByName("serName")[0]).value;
var serPas=(document.getElementsByName("serPas")[0]).value;
serPas=serPas==""?"空":serPas;
serName=serName==""?"空":serName;
var xhr=new XMLHttpRequest();
var url="http://"+ip+":8080/cgi-bin/Server.py";
url=addURLParam(url,'flag','connServer');
url=addURLParam(url,'serName',serName);
url=addURLParam(url,'serPas',serPas);
xhr.open("get",url,false);
xhr.send(null);
//转换json字符串 serObj:{'ufdata':ufdata}
var responseText=xhr.responseText;
if(responseText.trim()!='fail')
{
//设置cookie
cookieUtil.set('serName',serName=='空'?'':serName);
cookieUtil.set('serPas',serPas=='空'?'':serPas);
//禁用服务器配置项
(document.getElementsByName("serName")[0]).disabled="disabled";
(document.getElementsByName("serPas")[0]).disabled="disabled";
(document.getElementById('ip')).disabled='disabled';
var serObj=eval(responseText);
return serObj;
}
else
return false;
}
/*点击源账套时触发函数*/
function selSouData()
{
var serObj=connServer();
var sourceSel=document.getElementById('source');
var targetSel=document.getElementById('target');
if(serObj==false)
{
alert('请确认服务器配置项正确');
}
else
{
//清除单击事件
(document.getElementsByTagName("select")[0]).onclick=null;
serObj.forEach(function (item,index,arr) {
var option=document.createElement("option");
var option_2=document.createElement("option");
option.value=item;
option.text=item;
option_2.value=item;
option_2.text=item;
sourceSel.appendChild(option_2);
targetSel.appendChild(option);
//使按钮可用
var button=document.getElementsByTagName('button');
for(var i=0;i<button.length;i++)
{
button[i].disabled='';
}
})
}
}
/*点击凭证导入时触发函数*/
function book()
{
//flag为标记
//取得select选择的源数据库和目的数据库
var source=document.getElementById("source");
var target=document.getElementById("target");
var sourceDatabase=source.value;
var targetDatabase=target.value;
//取得年月
var month=document.getElementById('month');
var iyear=(month.value).split('-')[0];
var imonth=(month.value).split('-')[1];
if(iyear==""||imonth==undefined)
{
alert('请选择年月...');
return;
}
/*得到ino_id的操作*/
var url="http://"+ipConfig+":8080/cgi-bin/Server.py";
var serName=cookieUtil.get('serName');
var serPas=cookieUtil.get('serPas')==""?"空":cookieUtil.get('serPas');
url=addURLParam(url,'serName',serName);
url=addURLParam(url,'serPas',serPas);
url=addURLParam(url,'sourceDatabase',sourceDatabase);
url=addURLParam(url,'targetDatabase',targetDatabase);
url=addURLParam(url,'iyear',iyear);
url=addURLParam(url,'iperiod',imonth);
url=addURLParam(url,'flag','ino_ids');
var xhr=new XMLHttpRequest();
xhr.open("get",url,false);
xhr.send(null)
//返回{"bookAll":"总数","ino_ids":[账套的inoid]}
var response=xhr.responseText;
var ino_ids="";
if(response.trim()!='fail')
{
ino_ids=eval(response);
}
else
{
alert('源账套的起始年月无效,请核对....');
return;
}
var all=ino_ids.length;
var bookAll=document.getElementById("bookAll");
var bookCopy=document.getElementById("bookCopy");
bookAll.innerHTML="共"+all+"张凭证"
//凭证复制成功或失败的标志 flag="success"为成功 "fail"为失败
var flag="success";
ino_ids.forEach(function (item,index,arr) {
/*插入凭证的操作*/
var url="http://"+ipConfig+":8080/cgi-bin/Server.py";
var serName=cookieUtil.get('serName');
var serPas=cookieUtil.get('serPas')==""?"空":cookieUtil.get('serPas');
url=addURLParam(url,'serName',serName);
url=addURLParam(url,'serPas',serPas);
url=addURLParam(url,'sourceDatabase',sourceDatabase);
url=addURLParam(url,'targetDatabase',targetDatabase);
url=addURLParam(url,'iyear',iyear);
url=addURLParam(url,'iperiod',imonth);
url=addURLParam(url,'flag','copy');
//复制凭证
bookCopy.innerHTML="正在复制第"+ parseInt(index+1)+"张凭证"
url=addURLParam(url,'ino_id',item);
var xhr= new XMLHttpRequest();
xhr.open('get',url,false);
xhr.send(null);
var info=xhr.responseText;
if(info=="fail")
{
flag="fail";
alert("由于目标账套档案缺失,凭证复制失败，请勾选“缺省档案复制按钮”");
return;
}
});
if(flag=="success")
{
alert("凭证复制成功....");
}
}
/*点击异常清理时触发函数*/
function clearEx()
{
if(confirm("确认目标账套其他凭证已经记账?"))
{
var target=document.getElementById("target");
var targetDatabase=target.value;
var xhr=new XMLHttpRequest();
var url="http://127.0.0.1:8080/cgi-bin/Server.py"
var serName=cookieUtil.get('serName');
var serPas=cookieUtil.get('serPas')==""?"空":cookieUtil.get('serPas');
url=addURLParam(url,'serName',serName);
url=addURLParam(url,'serPas',serPas);
url=addURLParam(url,'targetDatabase',targetDatabase);
url=addURLParam(url,'flag','clear')
xhr.open("get",url,false);
xhr.send(null);
var info=xhr.responseText;
if(info.trim()=="success")
{
alert("异常清理成功....")
}
else
{
alert("异常清理失败....")
}
}
}
/*档案导入*/
function fileData(e)
{
var style= e.id;
var xhr=new XMLHttpRequest();
var url="http://"+ipConfig+":8080/cgi-bin/Server.py";
var source=document.getElementById("source");
var target=document.getElementById("target");
var sourceDatabase=source.value;
var targetDatabase=target.value;
var flag=null;
var serName=cookieUtil.get('serName');
var serPas=cookieUtil.get('serPas')==""?"空":cookieUtil.get('serPas');
url=addURLParam(url,'serName',serName);
url=addURLParam(url,'serPas',serPas);
switch(style)
{
case 'code':flag='codeInput';break;
case 'customer':flag='customerInput';break;
case 'vendor':flag='vendorInput';break;
case 'person':flag='personInput';break;
case 'department':flag='departmentInput';break;
}
url=addURLParam(url,'sourceDatabase',sourceDatabase);
url=addURLParam(url,'targetDatabase',targetDatabase);
url=addURLParam(url,'flag',flag);
xhr.open('get',url,false);
xhr.send(null);
var status=xhr.responseText;
if(status.trim()!='fail')
{
alert('导入成功');
}
else
{
alert('导入失败');
}
}
</script>
<body onload="load()">
<div class="main">
<section class="section_title">自助工具</section>
<!---服务器配置项-->
<section class="section_server">
<table class="server">
<thead><tr><th colspan="2">服务器配置</th></tr></thead>
<tr><td>服务器IP:</td><td><input id="ip" placeholder="请输入服务器IP" /></td></tr>
<tr><td>数据库实例:</td><td><input name="serName" placeholder="请输入数据库实例" /></td></tr>
<tr><td>数据库密码:</td><td><input name="serPas" placeholder="请输入数据库密码" /></td></tr>
<thead><tr><th colspan="2">数据操作</th></tr></thead>
<tr><td>数据源:</td><td>
<select onclick="selSouData()" id="source">
</select>
</td></tr>
<tr>
<td>目标数据:</td><td><select id="target">
</select></td>
</tr>
<tr><td>年度-月份:</td><td><input type="month" id="month"></td></tr>
<tr>
<td id="bookAll"></td>
<td id="bookCopy"></td>
</tr>
</table>
</section>
<section class="operition">
<button onclick="book()">凭证导入</button><button onclick="clearEx()">异常清理</button>
<button onclick="fileData(this)" id="code">会计科目导入</button>
<button onclick="fileData(this)" id="customer">客户档案导入</button>
<button onclick="fileData(this)" id="vendor">供应商档案导入</button>
<button onclick="fileData(this)" id="department">部门档案导入</button>
<button onclick="fileData(this)" id="person">人员档案导入</button>
</section>
</div>
</body>
</html>