啊关的企业在处理用友erp人事数据与税务报税时转换数据时被停滞了，一个小时内完成他的需求
1 # -*- coding:utf-8 -*-
2 # soctt.bin created 2011.8.29
3 # sw2us.com @2011
4 #
5
6 import sys,os,os.path,time,struct,traceback,threading,datetime,string,datetime,calendar
7 import xlrd
8
9 start_year=0
10 start_month= 0
11 start_day =1
12 end_day = start_day
13
14 end_year= 0
15 end_month = 0
16
17 employee_importFile=u'111111111.XLS'
18 tax_importFile=u'题桥工资格式.xls'
19
20 employee_exportFile=u'empolyees.txt'
21 tax_exportFile=u'personTax.txt'
22 employeelistfile='employee_simplelist.txt'
23
24 fixDeduct = 2000.00 #扣除额
25
26 #人员归档
27 def employeeAchive():
28 title = u'工号~~姓名~~证件类型~~证件号~~性别~~出生日期~~国家、地区~~开票标志~~申报方式~~职务~~地址~~含税标志~~邮政编码~~调入调离~~备注'
29 #rowfmt = u"%s~~ %s~~ 1 ~~%s ~~0 ~~%s ~~142 ~~1 ~~0 ~~ ~~ ~~1 ~~ ~~0 ~~0"
30 rowfmt = u"%s~~ %s~~ 1 ~~%s ~~0 ~~%s ~~142 ~~1 ~~0 ~~ ~~ ~~1 ~~ ~~0 ~~0"
31 rowfmt = rowfmt.replace(' ','')
32
33 wb = xlrd.open_workbook(employee_importFile)
34 sh = wb.sheet_by_index(0)
35 file = open(employee_exportFile,'w')
36 title = title.encode('gbk')
37 file.write(title)
38 file.write('\n')
39
40 file2 = open(employeelistfile,'w') #清单表2
41 for r in range(1,sh.nrows):
42 v = sh.row_values(r)
43 v = map(string.strip,v)
44
45
46 birth = ''
47 try:
48 y,m,d = v[4].split('-')
49 birth = "%04d%02d%02d"%(int(y),int(m),int(d))
50 except:
51 print u'出生年月空缺 (%s,%s)'%(v[1],v[2])
52
53 txt = rowfmt%(v[1],v[2],v[5],birth)
54 txt = txt.encode('gbk')
55 #print len(txt)
56 file.write(txt+'\n')
57
58 txt = "%s~~%s~~%s\n"%(v[1],v[2],v[5])
59 txt = txt.encode('gbk')
60
61 file2.write(txt)
62
63 file.close()
64 file2.close()
65
66
67 def precess_parameters():
68 global start_year,start_month,end_year,end_month,start_day,end_day
69
70 cur = datetime.datetime.now()
71 start_year = cur.year
72 start_month = cur.month
73 #print len(sys.argv)
74 if len(sys.argv) == 4 and sys.argv[1]=='tax':
75 start_year = int(sys.argv[2])
76 start_month = int(sys.argv[3])
77
78 start_day = 1
79 x,end_day= calendar.monthrange(start_year,start_month)
80
81
82
83 def salaryTax():
84 global start_year,start_month,end_year,end_month,start_day,end_day
85
86 precess_parameters()
87
88 hashemployee = {}
89 file = open(employeelistfile,'r')
90 lines = file.readlines()
91 file.close()
92 for line in lines:
93 line = line.strip().split('~~')
94 k = line[0]
95 v = line[2]
96 hashemployee[k] = v
97 #以上建立员工查找表
98
99
100 title = u'证件类型~~证件号码~~税目代码~~含税标志~~所属期起~~所属期止~~天数~~收入额~~扣除额~~应缴税额~~国家地区~~减免税额~~实缴税额'
101 #rowfmt = u"1 ~~%s ~~010000 ~~1 ~~%s ~~%s ~~%s ~~%s ~~%s ~~%s ~~142 ~~0 ~~%s"
102 rowfmt = u"1 ~~%s ~~010000 ~~1 ~~%s ~~%s ~~%s ~~%s ~~%s ~~%s ~~142 ~~0 ~~%s"
103 rowfmt = rowfmt.replace(' ','')
104
105 wb = xlrd.open_workbook(tax_importFile)
106 sh = wb.sheet_by_index(0)
107 file = open(tax_exportFile,'w')
108 title = title.encode('gbk')
109 file.write(title)
110 file.write('\n')
111
112 for r in range(1,sh.nrows):
113 v = sh.row_values(r)
114
115 v = map(unicode,v)
116 v = map(string.strip,v)
117 sid = '' #身份证编号
118 try:
119 sid = hashemployee[v[1]]
120 except:
121 print u'处理异常中断: 工号不能匹配! 工号: %s'%(v[1])
122 return
123 sys.exit(0)
124 start = "%04d%02d%02d"%(start_year,start_month,start_day)
125 end = "%04d%02d%02d"%(start_year,start_month,end_day)
126 txt = rowfmt%(sid,start,end, end_day-start_day+1,v[22],fixDeduct,v[24],v[24] ) #应发工资 W（v[22])
127 txt = txt.encode('gbk')
128 file.write(txt+'\n')
129 file.close()
130
131
132 if __name__=='__main__':
133 employeeAchive()
134 salaryTax()
135