📄 jishutongji.java
字号:
if(weijieqing.isSelected()){
where=" where zhifuyue!=0";
strbuf.append(" 账目未结清合同统计");
}
buf=buf.append("select yewuname,xinghao,qiandingriqi,hetongqixian,hetongzonge,zhifuzonge,zhifuyue,kehuname from (select distinct yewuname,xinghao,qiandingriqi,hetongqixian, hetongzonge,(sum(zhifujine)) as 'zhifuzonge',(hetongzonge-sum(zhifujine)) as 'zhifuyue' ,kehuname from hetongTable where yewuname='"+yewuyuanChoice.getSelectedItem().trim()+"' and convert(nvarchar(10),qiandingriqi,121) like '%"+sss.toString()+"%' group by yewuname,kehuname,hetongzonge,xinghao,qiandingriqi,hetongqixian) as tt ");
buf=buf.append(where);
}
}
}
if(zhengtitongji.isSelected()){
vec.add("业务员");
vec.add("信息总数");
vec.add("出差天数");
vec.add("差旅总额");
vec.add("签订总数");
vec.add("合同总额");
vec.add("已收货款");
vec.add("尚欠货款");
}
philosophers = new DefaultTableModel(vec,0); //创建0行
if(mingxitongji.isSelected()){
ResultSet resSet;
Connection dbConn;
Statement stat;
label.setText(strbuf.toString());
sqltt.setText(null);
String sqlmingxi=buf.toString();
sqltt.setText(sqlmingxi);///-----------------------不显示
try {
Class.forName(Yewu.forName);
dbConn=DriverManager.getConnection(Yewu.jdbc);
stat=dbConn.createStatement();
resSet=stat.executeQuery(sqlmingxi);
int m=1;
Vector vec1=new Vector();
while(resSet.next()){
Vector vec2=new Vector();
m++;
vec2.add((resSet.getObject("yewuname")));//0
vec2.add((resSet.getObject("xinghao")));//0
vec2.add((resSet.getObject("qiandingriqi")));//1
vec2.add((resSet.getObject("hetongqixian")));//1
vec2.add((resSet.getObject("hetongzonge")));//1
vec2.add((resSet.getObject("zhifuzonge")));//2
vec2.add((resSet.getObject("zhifuyue")));//2
vec2.add((resSet.getObject("kehuname")));//3
vec1.add(vec2);
}
if(m>1){
for(int i=0;i<vec1.size();i++){
philosophers.addRow(((Vector)vec1.get(i)).toArray());
}
}else{
JOptionPane.showMessageDialog(null,"没有满足查询条件合同信息");
}
stat.close();
dbConn.close();
}catch(Exception ex){
System.out.println(ex);
}
}
if(zhengtitongji.isSelected()){
Connection dbConn;
Statement stat;
ResultSet resSet1;
ResultSet resSet2;
ResultSet resSet3;
StringBuffer sql1=new StringBuffer();
StringBuffer sql2=new StringBuffer();
StringBuffer sql3=new StringBuffer();
if(yewuyuanChoice.getSelectedItem().trim().equals("全部业务员")){
strbuf=new StringBuffer();
if(sss.toString().equals("全部")){
strbuf.append("全部业务员 入厂至今 信息/差旅/合同 整体统计");
sql1.append("select yewuname,count(kehuname) as kehucount from yewuTab group by yewuname ");
sql2.append("select yewuname,count(hetongzonge) as yiqianding, sum(hetongzonge) as hetongzonge,sum(zhifuzonge) as zhifuzonge , sum(zhifuyue) as zhifuyue from (select distinct yewuname,hetongzonge,(sum(zhifujine)) as 'zhifuzonge',(hetongzonge-sum(zhifujine)) as 'zhifuyue' ,kehuname from hetongTable group by yewuname,kehuname,hetongzonge) as tt group by yewuname");
sql3.append("select yewuname,sum(tianshu) as tianshu ,sum(feiyong) as feiyong from chailvTable group by yewuname");
}else{
strbuf.append("全部业务员 ").append(sss.toString()).append(" 信息/差旅/合同 整体统计");
sql1.append("select yewuname, count(kehuname) as kehucount from yewuTab group by yewuname ");
sql2.append("select yewuname,count(hetongzonge) as yiqianding, sum(hetongzonge) as hetongzonge,sum(zhifuzonge) as zhifuzonge , sum(zhifuyue) as zhifuyue from (select distinct yewuname,hetongzonge,(sum(zhifujine)) as 'zhifuzonge',(hetongzonge-sum(zhifujine)) as 'zhifuyue' ,kehuname from hetongTable where convert(nvarchar(10),qiandingriqi,121) like '%").append(sss.toString()).append("%' group by yewuname,kehuname,hetongzonge) as tt group by yewuname");
sql3.append("select yewuname,sum(tianshu) as tianshu ,sum(feiyong) as feiyong from chailvTable where convert(nvarchar(10),huiTime,121) like '%").append(sss.toString()).append("%' group by yewuname");
}
}else{
strbuf=new StringBuffer();
if(sss.toString().equals("全部")){
strbuf.append(yewuyuanChoice.getSelectedItem().trim()).append(" 入厂至今 信息/差旅/合同 整体统计");
sql1.append("select yewuname, count(kehuname) as kehucount from yewuTab where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' group by yewuname ");
sql2.append("select yewuname,count(hetongzonge) as yiqianding,yewuname, sum(hetongzonge) as hetongzonge,sum(zhifuzonge) as zhifuzonge , sum(zhifuyue) as zhifuyue from (select distinct yewuname,hetongzonge,(sum(zhifujine)) as 'zhifuzonge',(hetongzonge-sum(zhifujine)) as 'zhifuyue' ,kehuname from hetongTable where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' group by yewuname,kehuname,hetongzonge) as tt group by yewuname");
sql3.append("select yewuname,sum(tianshu) as tianshu ,sum(feiyong) as feiyong from chailvTable where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' group by yewuname");
}else{
strbuf.append(yewuyuanChoice.getSelectedItem().trim()).append(" ").append(sss.toString()).append(" 信息/差旅/合同 整体统计");
sql1.append("select yewuname, count(kehuname) as kehucount from yewuTab where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' group by yewuname ");
sql2.append("select yewuname,count(hetongzonge) as yiqianding,yewuname, sum(hetongzonge) as hetongzonge,sum(zhifuzonge) as zhifuzonge , sum(zhifuyue) as zhifuyue from (select distinct yewuname,hetongzonge,(sum(zhifujine)) as 'zhifuzonge',(hetongzonge-sum(zhifujine)) as 'zhifuyue' ,kehuname from hetongTable where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' and convert(nvarchar(10),qiandingriqi,121) like '%").append(sss.toString()).append("%' group by yewuname,kehuname,hetongzonge) as tt group by yewuname");
sql3.append("select yewuname,sum(tianshu) as tianshu ,sum(feiyong) as feiyong from chailvTable where yewuname='").append(yewuyuanChoice.getSelectedItem().trim()).append("' and convert(nvarchar(10),huiTime,121) like '%").append(sss.toString()).append("%' group by yewuname");
}
}
label.setText(strbuf.toString());
try {
Class.forName(Yewu.forName);
dbConn=DriverManager.getConnection(Yewu.jdbc);
stat=dbConn.createStatement();
resSet1=stat.executeQuery(sql1.toString());
Vector vec9=new Vector();
while(resSet1.next()){
Vector vec1=new Vector();
vec1.add((resSet1.getObject("yewuname")));
vec1.add((resSet1.getObject("kehucount")));
vec9.add(vec1);
}
Vector vec5=new Vector();
resSet3=stat.executeQuery(sql3.toString());
int flgfeiyong=0;
while(resSet3.next()){
Vector vec4=new Vector();
vec4.add((resSet3.getObject("yewuname")));
vec4.add((resSet3.getObject("tianshu")));
vec4.add((resSet3.getObject("feiyong")));
vec5.add(vec4);
flgfeiyong++;
}
resSet2=stat.executeQuery(sql2.toString());
Vector vec8=new Vector();
while(resSet2.next()){
Vector vec3=new Vector();
vec3.add((resSet2.getObject("yewuname")));
vec3.add((resSet2.getObject("yiqianding")));
vec3.add((resSet2.getObject("hetongzonge")));
vec3.add((resSet2.getObject("zhifuzonge")));
vec3.add((resSet2.getObject("zhifuyue")));
vec8.add(vec3);
}
for(int i=0;i<vec9.size();i++){
for(int k=0;k<vec5.size();k++){
if(((Vector)(vec9.get(i))).get(0).toString().trim().equals(((Vector)(vec5.get(k))).get(0).toString().trim())){
((Vector)(vec9.get(i))).add(((Vector)(vec5.get(k))).get(1));
((Vector)(vec9.get(i))).add(((Vector)(vec5.get(k))).get(2));
}
}
}
if(flgfeiyong==0){
for(int i=0;i<vec9.size();i++){
((Vector)(vec9.get(i))).add("0");
((Vector)(vec9.get(i))).add("0");
}
}
for(int i=0;i<vec9.size();i++){
for(int k=0;k<vec8.size();k++){
if(((Vector)(vec9.get(i))).get(0).toString().trim().equals(((Vector)(vec8.get(k))).get(0).toString().trim())){
((Vector)(vec9.get(i))).add(((Vector)(vec8.get(k))).get(1));
((Vector)(vec9.get(i))).add(((Vector)(vec8.get(k))).get(2));
((Vector)(vec9.get(i))).add(((Vector)(vec8.get(k))).get(3));
((Vector)(vec9.get(i))).add(((Vector)(vec8.get(k))).get(4));
}
}
philosophers.addRow(((Vector)(vec9.get(i))).toArray());
}
stat.close();
dbConn.close();
}catch(Exception ex){
System.out.println(ex);
}
}
table = new JTable( philosophers );
table.setFont(font);
if(jspn!=null){
con.remove(jspn);
}
jspn=new JScrollPane(table);
JViewport jvp=new JViewport();
jvp.setView(new RowNumberHeader(table));
jspn.setRowHeader(jvp);
con.add(jspn,BorderLayout.CENTER);
con.validate();
}
if(e.getSource()==dayin){
if(sqltt.getText().trim().length()==0){
JOptionPane.showMessageDialog(null,"请先查询出您要打印的内容,只打印明细统计内容");
}else{
String strall=sqltt.getText().trim();
int all=strall.indexOf("qiandingriqi");
StringBuffer ff=new StringBuffer();
ff.append(strall.substring(0,all)).append("convert(nvarchar(10),qiandingriqi,121) ").append(strall.substring(all));
String ggg=ff.toString();
int agg=ggg.indexOf("hetongqixian");
StringBuffer gg=new StringBuffer();
gg.append(ggg.substring(0,agg)).append("convert(nvarchar(10),hetongqixian,121) ").append(ggg.substring(agg));
String[] str1={"yewuname","xinghao","qiandingriqi","hetongqixian","hetongzonge","zhifuzonge","zhifuyue","kehuname"};
String[] str2={"序号","业务员","产品型号","签订日期","清收期限","合同总额","已收货款","尚欠货款","客户名称"};
int FieldMaxLength[]={2,4,12,10,10,7,7,7,20};//80
//int FieldMaxLength[]={2,6,14,11,11,11,11,14};//80
(new PrintExample1(gg.toString(),str1,str2,strbuf.toString(),FieldMaxLength)).show();
}
}
}
public void itemStateChanged(ItemEvent e){ //选择事件
if(e.getSource()==yiqianding&&yiqianding.isSelected()){
weijieqing.setSelected(false);
yijieqing.setSelected(false);
}
if(e.getSource()==weijieqing&&weijieqing.isSelected()){
yijieqing.setSelected(false);
yiqianding.setSelected(false);
zhengtitongji.setSelected(false);
mingxitongji.setSelected(true);
}
if(e.getSource()==yijieqing&&yijieqing.isSelected()){
yiqianding.setSelected(false);
weijieqing.setSelected(false);
zhengtitongji.setSelected(false);
mingxitongji.setSelected(true);
}
if(e.getSource()==zhengtitongji||e.getSource()==mingxitongji)
if(e.getSource()==zhengtitongji&&zhengtitongji.isSelected()){
mingxitongji.setSelected(false);
weijieqing.setSelected(false);
yijieqing.setSelected(false);
yiqianding.setSelected(true);
}
if(e.getSource()==mingxitongji&&mingxitongji.isSelected()){
zhengtitongji.setSelected(false);
}
if(e.getSource()==nianChoice){
if(nianChoice.getSelectedItem().trim().equals("全部")){
yueChoice.select("全部");
yueChoice.setEnabled(false);
}else{
yueChoice.setEnabled(true);
}
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -