package PbuliClass;
import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.filechooser.FileNameExtensionFilter;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import DataBase.DatabaseManagement;
import Frame.AnchorManage;
import Judge.JugeNumber;
import Method.GetNowTime;
import anchor.Anchor_Dell;
import tag.Tag_Dell;
import tag.Tag_Into_Database;

/**±í¸ñµ¼Èëµ¼³öÀà*/

public class ImExport {
	HSSFWorkbook wb = null;//´´½¨EXCEL¶ÔÏó
	InputStream ins=null;	//ÊäÈëÁ÷
	POIFSFileSystem pfs=null;
	HSSFSheet sheet=null ;//ÉêÃ÷±íµ¥
	int num=0;//±íµ¥ÖеÄÐÐÊý
	HSSFRow hssfRow=null;//ÐжÔÏó
	File file=null;
	File saveFile=null;
	String outName;//µ¼³öÎļþµÄÃû³Æ
	JFileChooser chooser=null;// ´´½¨ÎļþÑ¡ÔñÆ÷
	FileNameExtensionFilter filter;//Îļþ¹ýÂËÆ÷
	int option ;
	dataToExcel toexcel;//Êý¾ÝתΪexcelÊä³ö


	public ImExport() {//¹¹Ôì·½·¨
		chooser = new JFileChooser("./savefile/");		
		filter=new FileNameExtensionFilter("Excel", "xls");
		//»ñÈ¡jar°üλÖã¬ÉèÖÃJFileChooserµ±Ç°Â·¾¶

	}

	/**µ¼³ö±í¸ñ·½·¨*/ 
	public void export(JPanel jpan,String sql) {       
		chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷	
		option = chooser.showSaveDialog(jpan);// Èç¹ûÈ·¶¨Ôò·µ»Ø0£¬È¡Ïû»ò¹Ø±Õ·µ»Ø1		        
		if (option == JFileChooser.APPROVE_OPTION){
			//Êý¾Ý±íµÄÐÐÊý
			int size=OutButton.getSize();        	
			if(size >65535) {

				double j=size/65535;						
				int maxPageNumber=(int) Math.ceil(j)+1;
				for(int i=1;i<maxPageNumber+1;i++) {							
					int k=65535;
					String a=String.valueOf(i*k-k);							
					String newsql=sql+" limit "+a+","+k;	
					outName=getFile().getAbsolutePath()+i+".xls";//µ¼³öµÄÎļþÃû×ÖĬÈÏΪexcelÎļþ
					toexcel=new dataToExcel();
					try {
						toexcel.toExcel(newsql, outName, "±êÇ©Áбí");//µ÷Óõ¼³öÎļþ·½·¨						
					} catch (Exception e2) {					
						e2.printStackTrace();
					}

					double k1=100/maxPageNumber;
					OutButton .getProgressBar().setValue((int)Math.ceil(k1)*i);
				}
				OutButton .getProgressBar().setValue(100);
				OutButton .getProgressBar().setBackground(Color.green);
				ShowMessage.zidingyi_24("×ܹ²³É¹¦µ¼³ö"+maxPageNumber+"¸ö±í¸ñ£¡");

			} else {
				outName=getFile().getAbsolutePath()+".xls";//µ¼³öµÄÎļþÃû×ÖĬÈÏΪexcelÎļþ
				toexcel=new dataToExcel();
				try {
					toexcel.toExcel(sql, outName, "±êÇ©Áбí");//µ÷Óõ¼³öÎļþ·½·¨						
				} catch (Exception e2) {					
					e2.printStackTrace();
				}
				OutButton .getProgressBar().setValue(100);
				OutButton .getProgressBar().setBackground(Color.green);
				ShowMessage.zidingyi(outName+"µ¼³ö³É¹¦£¡");
			}    	

		}

	}

	/**»ñÈ¡±£´æÎļþµÄ·½·¨*/
	public File getFile() {
		if(file==null) {
			file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ 
		}
		return file;
	}	


	/**»ñÈ¡±íµ¥¶ÔÏóµÄ·½·¨*/
	public  HSSFSheet getSheet(File file) {// Sheet(ÊõÓ¹¤×÷±í)¾ÍÊÇExcel±í¸ñ×óϽǵÄSheet1,Sheet2,Sheet3µ«ÔÚ³ÌÐòÖÐSheetµÄϱêÊÇ´Ó0¿ªÊ¼

		if(wb == null) {
			try {
				ins = new FileInputStream(file);
				try {
					pfs=new POIFSFileSystem(ins);//Îļþϵͳ¿É½ÓÊÜÒ»¸öÊäÈëÁ÷
				} catch (IOException e) {					
					e.printStackTrace();
				}
			} catch (FileNotFoundException e) {				
				e.printStackTrace();
			}			
			try {
				wb = new HSSFWorkbook(pfs);
				sheet=wb.getSheetAt(0);//»ñÈ¡µÚÒ»ÕÅ±íµ¥
				//				num= sheet.getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄÐÐÊý
				//				hssfRow=sheet.getRow(num);//»ñȡÿÐжÔÏó
			} catch (IOException e) {				
				e.printStackTrace();
			} 

		} 
		return sheet;

	}



	/**¹Ø±ÕÊý¾ÝÁ÷µÄ·½·¨*/
	public void getClose() {
		try {
			wb.close();
		} catch (IOException e) {				
			e.printStackTrace();
		}
		try {
			pfs.close();
		} catch (IOException e) {				
			e.printStackTrace();
		}
		try {
			ins.close();
		} catch (IOException e) {				
			e.printStackTrace();
		}

	}

	/**±êÇ©±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
	public void excelTagToMysql(JPanel jpan) {	


		String power="100";
		String zaixian="ÔÚÏß";
		chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
		option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
		if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
			file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ	
			num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
			//±éÀúrow				
			for(int rowNum=1;rowNum <= num;rowNum++) {					
				String tag_id=String.valueOf(getSheet(file).getRow(rowNum).getCell(1)).substring(0,4);//±êÇ©ID
				String tag_state=String.valueOf(getSheet(file).getRow(rowNum).getCell(2));//±êÇ©°ó¶¨¶ÔÏó
				String tag_posz=String.valueOf(getSheet(file).getRow(rowNum).getCell(3));//±êÇ©¸ß¶È
				String tag_pinglv=String.valueOf(getSheet(file).getRow(rowNum).getCell(4));//±êǩƵÂÊ
				String tag_sudu=String.valueOf(getSheet(file).getRow(rowNum).getCell(5));//±êÇ©ËÙ¶È


				//Èç¹û±êǩ״̬Ϊ¿ÕÔò¸³ÖµÎª·ñ
				if(tag_state=="null") {
					tag_state="δ°ó¶¨";		            	
				}

				if(tag_posz=="null") {
					tag_posz="150";		            	
				}


				if(tag_pinglv=="null") {
					tag_pinglv="1";		            	
				}

				if(tag_sudu=="null") {
					tag_sudu="1000";		            	
				}


				// Ö´ÐÐSQL²éѯÓï¾ä»ñµÃµÄ½á¹û¼¯Èç¹û´æÔÚÖظ´±êǩɾ³ýÔ­À´±êÇ©
				ResultSet haveTag = DatabaseManagement.query("select * from tb_tag where tag_id='" + tag_id.trim()+ "'");
				try {
					if (haveTag.next()) {// ½á¹û¼¯ÖбêÇ©IDÓг¬¹ýÒ»ÌõµÄ¼Ç¼
						String deleteSql="DELETE FROM tb_tag WHERE tag_id='"+tag_id.trim()+"'";
						DatabaseManagement.update(deleteSql);// ɾ³ý±êÇ©ÐÅÏ¢
					}	
				} 
				catch (Exception er) {
					er.printStackTrace();
				}	



				Tag_Into_Database.insert_all_Tag_info_dabase(tag_id, tag_state, power, zaixian,tag_posz,tag_sudu, tag_pinglv);

			}
			getClose();
			JOptionPane.showMessageDialog(jpan, "±í¸ñµ¼Èë³É¹¦£¡", "ÌáʾÐÅÏ¢",	JOptionPane.INFORMATION_MESSAGE);// µ¯³öÌáʾ¿ò
		}					                   



	}

	/**ÈËÔ±ÐÅÏ¢±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
	public void excelPersonToMysql(JPanel jpan) {	

		chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
		option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
		if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
			file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ	
			num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
			//±éÀúrow				
			for(int rowNum=1;rowNum <=num;rowNum++) {					
				String tagids=String.valueOf(getSheet(file).getRow(rowNum).getCell(1)).trim();//±êÇ©ID
				String name=String.valueOf(getSheet(file).getRow(rowNum).getCell(2)).trim();//ÐÕÃû
				String bumen=String.valueOf(getSheet(file).getRow(rowNum).getCell(3)).trim();//²¿ÃÅ
				String sex=String.valueOf(getSheet(file).getRow(rowNum).getCell(4)).trim();//ÐÔ±ð
				String phone=String.valueOf(getSheet(file).getRow(rowNum).getCell(5)).trim();//µç»°


				if (tagids.length()<4){
					ShowMessage.zidingyi("Ìí¼Óʧ°Ü,"+tagids+"±êÇ©Id³¤¶ÈСÓÚ4");
				}
				String tagid=tagids.substring(0,4);

				if(name.length()<2 || name.equals("null")) {
					name="δ°ó¶¨";
				}

				if(bumen.length()<3 || bumen.equals("null")) {
					bumen="ϵͳĬÈÏ";
				}

				if(sex.equals("")|| sex.equals("null")) {
					sex="ÄÐ";
				}

				if(phone.length()<8 || phone.equals("null")) {
					phone="***";
				}

				if(tagid.length()>3) {
					if(Tag_Dell.addtag_person_excel_into(tagid, name, bumen, sex, phone)) {
						// µ¯³öÌáʾ¿ò
						ShowMessage.zidingyi(tagid+"µ¼ÈëÊý¾Ý¿â³É¹¦..."+GetNowTime.now());
					}
				}

			}
			getClose();

		}			                   

	}

	/**»ùÕ¾ÐÅÏ¢±í¸ñµ¼ÈëÊý¾Ý¿âµÄ·½·¨*/
	public void excelAnchorToMysql(JPanel jpan) {	

		chooser.setFileFilter(filter);//Ìí¼ÓÎļþ¹ýÂËÆ÷
		option=chooser.showOpenDialog(jpan);//ÏÔʾѡÔñÎļþ¶Ô»°¿ò
		if(option==JFileChooser.APPROVE_OPTION) {//Èç¹ûÓû§Ñ¡ÔñµÄÊÇ¡°´ò¿ª¡±°´Å¥
			file=chooser.getSelectedFile();//»ñÈ¡Ñ¡ÖеÄÎļþ	
			num= getSheet(file).getLastRowNum();//»ñÈ¡µÚÒ»ÕÅ±íµ¥ÖеÄ×ÜÐÐÊý
			//±éÀúrow				
			for(int rowNum=1;rowNum <=num;rowNum++) {	
				String anchorid=String.valueOf(getSheet(file).getRow(rowNum).getCell(1));//»ùÕ¾id				
				String POSX=String.valueOf(getSheet(file).getRow(rowNum).getCell(2));//»ùÕ¾X×ø±ê
				String POSY=String.valueOf(getSheet(file).getRow(rowNum).getCell(3));//»ùÕ¾Y×ø±ê
				String POSZ=String.valueOf(getSheet(file).getRow(rowNum).getCell(4));//»ùÕ¾Z×ø±ê
				String layer=String.valueOf(getSheet(file).getRow(rowNum).getCell(5));//»ùÕ¾ËùÔÚ²ã
				String anchormode=String.valueOf(getSheet(file).getRow(rowNum).getCell(6));//»ùվ״̬
				String anchorip=String.valueOf(getSheet(file).getRow(rowNum).getCell(7));//»ùÕ¾ipµØÖ·	

				if(!JugeNumber.isDigit(anchorid)) {				
					ShowMessage.zidingyi_24(anchorid+"»ùÕ¾ID±ØÐë´¿Êý×ÖÇëÐ޸ģ¡");
					return;
				}

				if(layer.equals("null")) {
					layer="0";
				}

				if(POSX.equals("null")) {
					POSX="0";
				}

				if(POSY.equals("null")) {
					POSY="0";
				}

				if(POSY.equals("null")) {
					POSZ="0";
				}

				if(anchorip.equals("null")) {
					anchorip="";
				}

				if(Anchor_Dell.get_anchor(anchorid) ==null) {

					String[]  ziduan= {"anchorid","POSX","POSY","POSZ","layer","zu",
							"liangcheng","anchormode","anchorip","baoliu1","baoliu5","GreateTime"};
					String[] zhi= {anchorid,POSX,POSY,POSZ,layer,"0",
							"100000",anchormode,anchorip,"","0",GetNowTime.now()};

					if(DatabaseManagement.insertfast("tb_achor", ziduan, zhi)) {	
						AnchorManage.add_new_anchor_do(anchorid, POSX, POSY, POSZ, layer, "0", "100000", anchorip,"0", anchormode,"0");
						ShowMessage.zidingyi(anchorid+"µ¼ÈëÊý¾Ý¿â³É¹¦..."+GetNowTime.now());
					}
				}

			}
			ShowMessage.zidingyi_24("Êý¾Ýµ¼ÈëÍê±ÏÇëÖØÆôÈí¼þ");
			getClose();
			JOptionPane.showMessageDialog(jpan, "±í¸ñµ¼Èë³É¹¦£¡", "ÌáʾÐÅÏ¢",	JOptionPane.INFORMATION_MESSAGE);// µ¯³öÌáʾ¿ò
		}			                   

	}


}