package com.cwbuyer.main;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import android.app.Activity;
import android.app.AlertDialog;
import android.app.ProgressDialog;
import android.content.ComponentName;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.ServiceConnection;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.Bundle;
import android.os.IBinder;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.DatePicker;
import android.widget.LinearLayout;
import android.widget.Toast;
import net.posprinter.posprinterface.IMyBinder;
import net.posprinter.posprinterface.ProcessData;
import net.posprinter.posprinterface.UiExecute;
import net.posprinter.utils.DataForSendToPrinterPos80;

import com.cwbuyer.lib.DBCloud;
import com.cwbuyer.lib.DateUtil;
import com.cwbuyer.lib.TbName;
import com.cwbuyer.lib.Utilis;
import com.pwbuyer.main.R;

public class MainTainManage extends Activity {

	public static IMyBinder binder ;
	
    ServiceConnection conn= new ServiceConnection() {
    	
		@Override
		public void onServiceConnected(ComponentName name, IBinder iBinder) {
			// TODO Auto-generated method stub
            //Bind successfully
            binder= (IMyBinder) iBinder;
            Log.e("binder","connected");
			
		}

		@Override
		public void onServiceDisconnected(ComponentName name) {
			// TODO Auto-generated method stub
            Log.e("disbinder","disconnected");
			
		}
    };

	
//	DateAdapter mAdapter = null;
	int nPart=0; //預設裝置為[0總倉][1門市][2業務裝置]理論上較像外部裝置,但是只可以盤點(不一定提供圖檔)
	String myImportPath =android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+Utilis.getIni(this, "SYS", "YEAR",7);
	int of_line=Utilis.toInt(Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 3));
	boolean success=false;	

	DatePicker dp1;
	DatePicker dp2;
	private int mYear1 = 0;
	private int mYear2 = 0;
	private int mMonth1 = 0;
	private int mMonth2 = 0;
	private int mDay1 = 0;
	private int mDay2 = 0;
	String mEndDay = null;
	String mStartDay = null;
	
	
	//綱要:
	//轉檔的欄位製作陣列int 
	//未來需考量分年度,分成2部分,1為現行檔 1為歷史檔
	
	//一旦製作分年度,最大的難題
	//1結算點假設為 20120731
	//2.必須將20120731後的所有商品只要有交易或出現的商品只要庫存>0者,都要做轉入新的年度(現行資料庫)
	//3.以上2條件符合的商品,必須建立20120731的期初庫存,HOW?
	//4先算出所有商品總倉總進貨量(100),總倉總出貨量-(610)+(600)-總倉的(210),總倉總存貨量(在庫)
	//再以<=20120731之前的(100)-(610)+(600)-總倉的(210)不為0者,建立20120731的(100)單
	//以各門市算出所有商品總進貨量(610)-(600)-(210),計算出門市存貨量(在庫)
	//再以<=20120731之前的(600)-(610)-(210)不為0者,建立20120731的該門市期初(610)單
	//帳面須注意到,批發客戶的未結帳
	//門市(專櫃)的20120731結算點的庫存金額及應收帳款
	//廠商應付帳款結算期末紀錄
	//員工自購帳面也要清算紀錄
	//5所有數據存入歷史年度資料夾(但是只存至20120731為止),未來可以製作各年度統計分析
	private ArrayList<Integer> mCountryIdList = new ArrayList<Integer>();	
	private ArrayList<String> mCountryList = new ArrayList<String>();

	private ArrayList<String> mColorIdList = new ArrayList<String>();	
	private ArrayList<String> mColorList = new ArrayList<String>();
	private ArrayList<String> mColorPicList = new ArrayList<String>();

	private ArrayList<String> mFnoList = new ArrayList<String>();	
	private ArrayList<String> mKindList = new ArrayList<String>();

	
	private StringBuffer TypeStr =new StringBuffer();

//	final String uriAPI = "http://www.cwbuyer.net/php/qc_color_insert.php";
	private String uriAPIpath = "http://"+Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 1)+"/"+Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 2);
	private String uriAPI =null;
	protected static final int REFRESH_DATA = 0x00000001;
    private int UpDn=0; //0.update 1.download
	
	String mUid=Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 2);  //ex:3g001
	
	String mTR="30";
	double dGPS=0.0;
	String BANKNO="1";  //2人民幣 3美金 4韓幣 5富邦 6卡
	String BANKNAME=Utilis.getIni(this,"SYS","DEPT",1)+Utilis.getIni(this,"SYS","DEPT",2);
	String COUNTRY ="1"; //台灣
	String countryArray []={"","台灣","大陸","韓國","日本","香港"};
	String bankArray []={"","現金","人民幣","韓幣","日幣","港幣"};
	String mTrade="5";
	
	String F1000=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/basic1000.csv";
	String F6000=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/basic6000.csv";
	String F7000=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/basic7000.csv";
	String FPHRA=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/phrase.csv";
	String FPHOTO=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/pict.xml";
	
	String ADP000=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/ADP000.csv";
	String ADP001=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/ADP001.csv";
	
	String TBDP000=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/TBDP000.csv";	
	String TBDP001=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/TBDP001.csv";	

	String AD2001=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/ADETAIL.csv";
	//將貨品明細檔,轉入到採購新品,以建檔日為批號,並想辦法湊出QDETAIL
	int D1000qcu10Array[]={6,7,9,33,12,11,55,42,17,28, 8,34,13,46};
	String qcuArray[]={"CUSTNO","CUSTNAME","MOBIL","EMAIL","MSN","GMAIL","DEPTNO","EMPID","DISCOUNT","ADDRESS","PS","COUNTRY","TRADETYPE","CREATEDATE"};
	//國家需轉換以陣列做轉存代號
	
	int D7000qcu20Array[]={7,8,13,34,10,12,43,18,29,20,35,15,47};
	String qdpArray[]={"CUSTNO","CUSTNAME","MOBIL","EMAIL","MSN","GMAIL","EMPID","DISCOUNT","ADDRESS","PS","COUNTRY","TRADETYPE","CREATEDATE"};	
	//補充預設欄位 TR=20,GPSLA=0,GPSLO=0,BANKNO=1,BANKNAME='現金',(17)COUNTRY需比對 STATE=0 PIC=  C1C2C3M1M2M3S1S2S3
	//只挑(5)為"門市"
	//總倉一律為"AA"
	
	int D7000qfaArray[]={7,8,13,34,10,12,43,18,29,20,35,15,47};
	String qfaArray[]={"FACTNO","FACTNAME","MOBIL","EMAIL","MSN","GMAIL","EMPID","DISCOUNT","ADDRESS","PS","COUNTRY","TRADETYPE","CREATEDATETIME"};	
	//補充預設欄位 TR=10,GPSLA=0,GPSLO=0,BANKNO=3,BANKNAME='外幣',(16)COUNTRY需比對 STATE=0 PIC=
	//只挑(5)成品廠商
	
	int D6000q40Array[]={6,8,9,16,23,24,18,32,40};
	String qemArray[]={"CUSTNO","CUSTNAME","GMAIL","MOBIL","EMAIL","DEPTNO","ADDRESS","BIRTHDAY","CREATEDATE"};	
	//補充預設欄位 TRADETYPE=5 DISCOUNT=70 TR=40,GPSLA=0,GPSLO=0,BANKNO=1,BANKNAME='現金',(17)COUNTRY需比對 STATE=0 PIC=  C1C2C3M1M2M3S1S2S3
	
	int ADP000Array[]={0,1,2,3,4,5,6,7,8,9,10,11};
	String AqheadArray[]={"FORMDATE","DEPTNO","USER","USERNAME","QKIND","FORMNO","EMPID","TRADETYPE","ASUM","COSTS","CASH","DISMONEY"};

	int ADP001Array[]={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,24,25,26,27,28,29};
	String AqitemsArray[]={"FORMDATE","DEPTNO","QKIND","FORMNO","USER","USERNAME","EMPID","GOODSNO","GOODSNOS","GOODSNAME","GOODSTYPE","SIZE","UNIT1","HANDNO","P0","P1","P2","P3","P4","P5","SUPPLY","UNITPRICE","DISCOUNT","SUBPRICE","ACOST","SOURCENO","PIC","ACOST","ORDDATE"};
											//0'FORMDATE',1'DEPTNO',2'POSHEAD',3'POSBODY',4'VIPNO',5'CUSTNAME',6'COUNTERNO',7'PARTNO',8'HUMANSERNO',9'PARTNAME',0'PARTTYPE',1'SIZECODE',2'COLORNO',3'PARTSERNO',4'BILLUNIT',5'P1',6'P2',7'P3',8'P4',9'P5',20'LTDNO',1'UNITP','DISC',3'SUNIT',4'SCOST',5'SOURCENO',6'PARTCODE',7'SCOST',8"PACKAGE"
	

	int BDP000Array[]={0,1,2,3,4,5,6,7};
	String BqheadArray[]={"DEPTNO","QKIND","USER","USERNAME","ASUM","COSTS","CASH","DISMONEY"};

	int BDP001Array[]={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,20,21,22,23,24};
	String BqitemsArray[]={"GOODSNOS","QKIND","DEPTNO","USER","USERNAME","GOODSNO","GOODSNAME","GOODSTYPE","SIZE","UNIT1","P0","P1","P2","P3","P4","P5","SUPPLY","UNITPRICE","DISCOUNT","SUBPRICE","ACOST","SOURCENO","PIC","ACOST"};
	//												'HUMANSERNO','POSHEAD','DEPTNO','VIPNO','CUSTNAME','PARTNO','PARTNAME','PARTTYPE','SIZECODE','COLORNO','BILLUNIT','P1','P2','P3','P4','P5','LTDNO','SUNITP','DISC','SUNIT','SSUB','SCOST','SOURCENO','PARTCODE','SCOST'	
	//F商品類別 只用到類別名稱
	int AD2001Array[]={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18};
	String AdetailArray[]={"SUPPLY","SOURCENO","GOODSNO","GOODSNAME","OUTPO","GOODSTYPE","PIC","COLOR","SIZE","P1","P2","P3","P4","P5","P0","BILLRATE","CREATEDATETIME","ATLIST","ORDDATE"};
//@@@應加入兩個欄位	歷史,段數LTDNO,SOURCENO,PARTNO,		PARTNAME,	PARTSERNO,PARTTYPE,		PARTCODE,COLOR,SPECSIZE,P1,P2,P3,P4,P5,BILL,BILLRATE,CREATEDATE,NETRECEIPT,PACKAGE
	
	//J顏色代號 只用到 色NO 色中文
	
	int nDept=1; //預設門市盤點 0.總倉 1.門市 2.廠商  盤點
	int nSuccess=0;
	int nCompares=1;  //表示上盤日期比本盤日期還大,是不可以執行
	int nTradetype=5; //預設為零售價
	int nPandnK=0; //0詳式 1簡式 2盤價
	int nDiscount=100;
	int nID=0;
	boolean isNew=true;
	boolean ISCONNECT=false;

	String Ldate="2014-12-31";
	String Ddate=null;
	String mWho=null;

	private final int RESULT_QCUSTFF = 9999;		//會員檔回傳口令
	private final int RESULT_QFACT = 1020;		//廠商檔回傳口令
	
	public ProgressDialog myDialog = null;
	
	@Override
	protected void onActivityResult(int requestCode, int resultCode, Intent dataR) {
		// TODO Auto-generated method stub
		super.onActivityResult(requestCode, resultCode, dataR);
		switch (requestCode){
		case RESULT_QCUSTFF:  //結帳返回的各種可能性
			{
				if (resultCode == RESULT_OK) {
				}
				break;
			}
		case RESULT_QFACT:
			{
				if (resultCode == RESULT_OK) {
				}
				break;				
			}
		}
	}
	
	
    public void onDestroy(){
    	super.onDestroy();
    }
	
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.maintain);
        
        setTitle(getString(R.string.app_name_version));

        ((LinearLayout)findViewById(R.id.layout_transpd)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_aapndn)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_searchpndn)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_addpndn)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_transout)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_transin)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_addyear)).setOnClickListener(new MainClick());
        ((LinearLayout)findViewById(R.id.layout_a)).setOnClickListener(new MainClick());
        
		SQLiteDatabase db = Utilis.getDB(this);
		getCountryData(this,db);
		getColorData(this,db);
		getTypeData(this,db);
		db.close();
        if (!Utilis.getIni(this,"SYS","DEPT",1).equalsIgnoreCase(Utilis.getIni(this,"SYS","DEPT",6))) nPart=1;  //門市版
		
    }

	// 搜尋日期 Dialog
    private View makeDateDialog() {
    	View view = LayoutInflater.from(this).inflate(R.layout.search_date, null, false);
    	Calendar c = Calendar.getInstance();
    	c.set(Calendar.DAY_OF_MONTH, 1); // 關鍵
    	
	    mYear1 = c.get(Calendar.YEAR);
		mMonth1 = c.get(Calendar.MONTH) + 1;
		mDay1 = Utilis.toInt(Utilis.getCurrentDate().substring(8,10));
		mYear2 = c.get(Calendar.YEAR);
		mMonth2 = c.get(Calendar.MONTH) + 1;
		mDay2 = mDay1;
		
		dp1 = (DatePicker)view.findViewById(R.id.dp1);
		dp1.init(mYear1, mMonth1 - 1, mDay1, new DatePicker.OnDateChangedListener() {
			@Override
			public void onDateChanged(DatePicker view, int year,
					int monthOfYear, int dayOfMonth) {
				mYear1 = year;
				mMonth1 = monthOfYear + 1;
				mDay1 = dayOfMonth;
			}
		});
		
		dp2 = (DatePicker)view.findViewById(R.id.dp2);
		dp2.init(mYear2, mMonth2 - 1, mDay2, new DatePicker.OnDateChangedListener() {
			@Override
			public void onDateChanged(DatePicker view, int year,
					int monthOfYear, int dayOfMonth) {
				mYear2 = year;
				mMonth2 = monthOfYear + 1;
				mDay2 = dayOfMonth;
			}
		});
		
		return view;
	}

   
	public void getCountryData(Context context,SQLiteDatabase db){
		if(mCountryList != null){
			mCountryList.clear();
		}
		if(mCountryIdList != null){
			mCountryIdList.clear();
		}
		Cursor cursor = db.rawQuery("select _ID,NAME,PIC from qc_country order by _ID", null);
		if(cursor != null){
			try{
				if(cursor.getCount() > 0){
					cursor.moveToFirst();
					for(int i = 0; i < cursor.getCount(); i++){
						int id = cursor.getInt(cursor.getColumnIndex("_ID"));
						String name = cursor.getString(cursor.getColumnIndex("NAME"));
						mCountryIdList.add(id);
						mCountryList.add(name);
						cursor.moveToNext();
					}
				}
			}catch(Exception e){
				
			}finally{
				cursor.close();
				cursor = null;
			}
		}
	}

	private String getNFormno(String formf){
		int flowNumber = 0;
		SQLiteDatabase db = Utilis.getDB(this);
		try{
			Cursor cursor = db.rawQuery("select FORMNO from " + TbName.QHEAD + " where FORMNO like '"+formf+"%' order by FORMNO DESC limit 1", null);
			if(cursor != null){
				try{
					if(cursor.getCount() > 0){
						cursor.moveToFirst();
						flowNumber = Utilis.toInt(cursor.getString(cursor.getColumnIndex("FORMNO")).substring(formf.length()))+1;
					}
				}catch(Exception e){
					e.printStackTrace();
				}
				cursor.close();
			}else{ //新的開始單號
				flowNumber=1;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		if(db != null){
			db.close();
		}
		return formf+Utilis.formatFlow(flowNumber);
	}
	
	public boolean doesTableExist(SQLiteDatabase db, String tableName) {
        Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);

    if (cursor != null) {
        if (cursor.getCount() > 0) {
            cursor.close();
            return true;
        }
        cursor.close();
    }
    return false;
}
	
	public void getTypeData(Context context,SQLiteDatabase db){
		if(TypeStr.toString() != null){
			TypeStr.delete(0, TypeStr.toString().length());
		}
		Cursor cursor = db.rawQuery("select GOODTYPE,_ID from qc_type group by GOODTYPE", null);
		if(cursor != null){
			try{
				if(cursor.getCount() > 0){
					cursor.moveToFirst();
					for(int i = 0; i < cursor.getCount(); i++){
						String name = cursor.getString(cursor.getColumnIndex("GOODTYPE"));
						TypeStr.append(name).append(",").append(cursor.getString(cursor.getColumnIndex("_ID"))).append(";");
						cursor.moveToNext();
					}
				}
			}catch(Exception e){
				
			}finally{
				cursor.close();
				cursor = null;
			}
		}
	}
	   
		public void getColorData(Context context,SQLiteDatabase db){
			if(mColorList != null){
				mColorList.clear();
			}
			if(mColorIdList != null){
				mColorIdList.clear();
			}
			if(mColorPicList != null){
				mColorPicList.clear();
			}
			
			Cursor cursor = db.rawQuery("select NO,TXT,PIC from qc_color order by NO", null);
			if(cursor != null){
				try{
					if(cursor.getCount() > 0){
						cursor.moveToFirst();
						for(int i = 0; i < cursor.getCount(); i++){
							String id = cursor.getString(cursor.getColumnIndex("NO"));
							String name = cursor.getString(cursor.getColumnIndex("TXT"));
							String pic = cursor.getString(cursor.getColumnIndex("PIC"));
							
							mColorIdList.add(id);
							mColorList.add(name);
							mColorPicList.add(pic);
							cursor.moveToNext();
						}
					}
				}catch(Exception e){
					
				}finally{
					cursor.close();
					cursor = null;
				}
			}
		}

		
		private String getColN(String Indx){
			String RETN="00";
			for (int xx=0;xx<mColorList.size();xx++){
			 if (mColorList.get(xx).toString().equalsIgnoreCase(Indx)){
				 RETN=mColorIdList.get(xx).toString();
			 }
			}
			return RETN; 
		}
		
		//3g001,Q1-Q30,Local_(SYS,ALBUN,n)LAST_DAY
		  private 	void mupdate_qc(String gUid,int updn,String UPD){
			  //[updn] 0.update   1.download
			  if (updn==0){
				  
				new AlertDialog.Builder(this).setTitle("請指定重新上傳交易單據的日期範圍")
				.setView(makeDateDialog())
				.setNegativeButton(getString(R.string.str_cancel), new DialogInterface.OnClickListener() {
		            public void onClick(DialogInterface dialog, int which) {
		            }
		          }).setPositiveButton(getString(R.string.str_ok), new DialogInterface.OnClickListener() {
		            public void onClick(DialogInterface dialog, int which) {
		            	String startDay = "" + mYear1 + "/" + Utilis.format(mMonth1) + "/" + Utilis.format(mDay1);
		            	String endDay = "" + mYear2 + "/" + Utilis.format(mMonth2) + "/" + Utilis.format(mDay2);
		            	
		        		int of_line=Utilis.toInt(Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 3));
		        		if (Utilis.haveInternet(MainTainManage.this) && of_line!=0){
			            	mStartDay = startDay.replace("/", "-");
			            	mEndDay = endDay.replace("/", "-");
			            	String SQL="update qhead set TR=QKIND where FORMDATE>='"+mStartDay+"' and FORMDATE<='"+mEndDay+"' and BANKNAME='"+BANKNAME+"'";
							SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
							db.execSQL(SQL);
							db.close();
							DBCloud.aa1_aa0_update_HI(MainTainManage.this);
		        		}		            	
		            }
		          }).setNegativeButton(getString(R.string.str_cancel), new DialogInterface.OnClickListener() {
			            public void onClick(DialogInterface dialog, int which) {
			            }
			    }).show();
				
				DBCloud.aa1_aa0_qfact(this);
				DBCloud.aa1_aa0_qcust(this);
			  }else{
					DBCloud.aa0_aa1_auto_qfact(this);
					DBCloud.aa0_aa1_auto_qcust(this);
					//DBCloud.aa0_aa1_auto_HI(this,20);
					 //@@@2015-11-11 將被以下取代 
					//將開寫 高效的 下載維護 的程序
					//搭配 aa0_aa1_dn_HI.php

					new AlertDialog.Builder(this).setTitle("請指定重新下載交易單據的日期範圍")
					.setView(makeDateDialog())
					.setNegativeButton(getString(R.string.str_cancel), new DialogInterface.OnClickListener() {
			            public void onClick(DialogInterface dialog, int which) {
			            }
			          }).setPositiveButton(getString(R.string.str_ok), new DialogInterface.OnClickListener() {
			            public void onClick(DialogInterface dialog, int which) {
			            	String startDay = "" + mYear1 + "/" + Utilis.format(mMonth1) + "/" + Utilis.format(mDay1);
			            	String endDay = "" + mYear2 + "/" + Utilis.format(mMonth2) + "/" + Utilis.format(mDay2);
			            	
			            	mStartDay = startDay.replace("/", "-");
			            	mEndDay = endDay.replace("/", "-");
							//先諮詢日期範圍,以月為單位
			        		int of_line=Utilis.toInt(Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 3));
			        		if (Utilis.haveInternet(MainTainManage.this) && of_line!=0){
			        			//SERVER的時間與LOCAL時間不同調,不可混談
			        			String mBankn=Utilis.getIni(MainTainManage.this, "SYS", "DEPT",1)+Utilis.getIni(MainTainManage.this, "SYS", "DEPT",2);//門市代號+機號		

			        				//aa0_aa1_dn_hi.php
			        			String AUPDAY=Utilis.getIni(MainTainManage.this, "SYS", "AUPDATE", 3);
			        			if (AUPDAY.length()>=5){
			        				AUPDAY=AUPDAY.substring(0, 4)+"00000000";
			        			}  //更新只限於月為單位
			        				StringBuffer bfstr = new StringBuffer();
			        				StringBuffer bfstrF = new StringBuffer();
			        					bfstr.append(Utilis.getIni(MainTainManage.this, "SYS", "IMPORT", 2)).append(";");
			        					bfstrF.append("UID").append(";");
			        					bfstr.append(mBankn).append(";");
			        					bfstrF.append("QDEPT").append(";");
			        					bfstr.append(Utilis.getIni(MainTainManage.this,"SYS","DEPT",1)).append(";");
			        					bfstrF.append("DEPTNO").append(";");
			        					bfstr.append(Utilis.getIni(MainTainManage.this,"SYS","DEPT",6)).append(";");
			        					bfstrF.append("QPART").append(";");
			        					bfstr.append(Utilis.getIni(MainTainManage.this, "SYS", "YEAR", 1)+Utilis.getIni(MainTainManage.this, "SYS", "YEAR", 2)).append(";");
			        					bfstrF.append("QYEARS").append(";");
				    					bfstr.append(Utilis.getIni(MainTainManage.this, "SYS", "TRADENAME", 8)).append(";");
				    					bfstrF.append("ALONE").append(";");
			        					bfstr.append(mStartDay).append(";");
			        					bfstrF.append("BGDATE").append(";");
			        					bfstr.append(mEndDay);
			        					bfstrF.append("ENDATE");
			        				Thread t = new Thread(new DBCloud.sendPostRunnableQC(MainTainManage.this,bfstr.toString(),bfstrF.toString(),21));
			        				t.start();
			        		}
			            	
			            }
			          }).setNegativeButton(getString(R.string.str_cancel), new DialogInterface.OnClickListener() {
				            public void onClick(DialogInterface dialog, int which) {
				            }
				    }).show();
			  }
			
		 //代碼設定檔 的 updn= 1下載 0上傳 
				StringBuffer bfstr = new StringBuffer();
				StringBuffer bfstrF = new StringBuffer();
					bfstr.append(gUid).append(";");
					bfstrF.append("UID").append(";");
					bfstr.append("0").append(";");
					bfstrF.append("ID").append(";");
					bfstr.append(UPD);
					bfstrF.append("LUPDATE");
					Toast.makeText(MainTainManage.this, "..."+bfstr.toString(), Toast.LENGTH_LONG).show();

					Thread t = new Thread(new DBCloud.sendPostRunnable(MainTainManage.this,bfstr.toString(),bfstrF.toString(),uriAPI,updn));
				t.start();
		  }
		
	class MainClick implements OnClickListener {
		@SuppressWarnings("null")
		@Override
		public void onClick(View v) {
			Utilis.runVibrate(MainTainManage.this);
			int id = v.getId();
			switch (id) {
				case R.id.layout_transpd:  //PD轉檔的功能
				{  //未來需考量分年度,分成2部分,1為現行檔 1為歷史檔
					SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
					
					String encodout = "BIG5";
					FileInputStream fis =null;
					
					try{    
						//商品類別VS顏色
						File file = new File(FPHRA);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(FPHRA);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
							while ((ch = in.read()) > -1) {
									buffers.append((char)ch);
								}
							in.close();  //
							
							int loca=0;
							int locaO=buffers.toString().indexOf("\r\n",0)+2;  //因為第一row是CSV的抬頭欄位名稱,不要轉入
							String fields=null;
							
							while((loca =buffers.toString().indexOf("\r\n",locaO))>0)
							{
									fields=buffers.toString().substring(locaO,loca-2);
								if (fields==null || fields.length()<=0) {}	else{
									String[] arrayField = fields.toString().split(",");
									if(arrayField[0].substring(1,arrayField[0].length()-1).equalsIgnoreCase("F")){  //商品類別
										if (TypeStr.toString().indexOf(arrayField[2].substring(1,arrayField[2].length()-1))<0){  //表示不存在,需建立之
											ContentValues valueF = new ContentValues();
											valueF.put("GOODTYPE",arrayField[2].substring(1,arrayField[2].length()-1));
											valueF.put("COUNTRY","3");
											valueF.put("SEASON","4");
											valueF.put("ADDCOST",0.0);
											valueF.put("PIC","drawable/icon");
											db.insert("qc_type", null, valueF);
										}	
									}else if(arrayField[0].substring(1,arrayField[0].length()-1).equalsIgnoreCase("J")){  //商品顏色
										if (mColorList.indexOf(arrayField[2].substring(1,arrayField[2].length()-1))<0){  //表示顏色不存在,需建立之
											if (mColorIdList.indexOf(arrayField[1].substring(1,arrayField[1].length()-1))>=0){
												db.execSQL("update qc_color set TXT="+arrayField[2]+"  where NO="+arrayField[1]);
											}else{	
												ContentValues valueF = new ContentValues();
												valueF.put("NO",arrayField[1].substring(1,arrayField[1].length()-1));
												valueF.put("TXT",arrayField[2].substring(1,arrayField[2].length()-1));
												valueF.put("PIC"," 0XFFFFFFFF");
												db.insert("qc_color", null, valueF);
											}
										//否則顏色名稱存在,需要檢查顏色代號是否一致
											//如果不一致,則需要修改代號
											//?缺點有可能會相同代號,不同顏色?
										}else{  //
											int indexC=mColorList.indexOf(arrayField[2].substring(1,arrayField[2].length()-1));
											if (!mColorIdList.get(indexC).equalsIgnoreCase(arrayField[1].substring(1,arrayField[1].length()-1))){
												db.execSQL("update qc_color set NO="+arrayField[1]+"  where TXT="+arrayField[2]);								
											}	
										} 	
									}
								}									
									locaO=loca+2;
							}
							
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					getColorData(MainTainManage.this,db);
					getTypeData(MainTainManage.this,db);
					
					try{    
						//客戶主檔

						File file = new File(F1000);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(F1000);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
							int Rcount=0;
							
							db.beginTransaction();
						try{
							
							while ((ch = in.read()) > -1) {
									buffers.append((char)ch);
									if (buffers.toString().indexOf("\r\n")>0){
										Rcount+=1;
										if (Rcount>1){  //第一 筆為欄位列
											//以下尚在實驗階段String fields=buffers.toString();
											//20130628_@@@
											String fields=buffers.toString().substring(0,buffers.toString().length()-2);
											if (fields==null || fields.length()<=0) {}	else{
												String[] arrayField = fields.toString().split(",");
												
//												Cursor csC=db.rawQuery("select "+qcuArray[0]+" from qcust where "+qcuArray[0]+"="+arrayField[D1000qcu10Array[0]], null);
//												if (csC.getCount()<=0){  //沒有重複的客戶編號
													
													ContentValues valueF = new ContentValues();
													for(int ii=0;ii<D1000qcu10Array.length;ii++){  //轉入必要欄位
														valueF.put(qcuArray[ii],arrayField[D1000qcu10Array[ii]].replace("'", ""));
													}
													//存入預設的欄位
													valueF.put("TR","30");
													valueF.put("GPSLA",0);
													valueF.put("GPSLO",0);
													valueF.put("BANKNO","1");
													valueF.put("BANKNAME",BANKNAME);
													valueF.put("STATE","1");
													valueF.put("COUNTRY","1");
													
													valueF.put("C1","1");valueF.put("C2","0");valueF.put("C3","0");
													valueF.put("M1","1");valueF.put("M2","0");valueF.put("M3","0");
													valueF.put("S1","1");valueF.put("S2","0");valueF.put("S3","0");
													valueF.put("PIC","drawable/icon");
													db.insert("qcust", null, valueF);
//												}
												
											}											
										}
										buffers.delete(0, buffers.toString().length()	);
									}
								}
							
								in.close();  //
				            db.setTransactionSuccessful();
						}
						catch(Exception e){
						 
						}
						 
						finally{
						           //finish
						          db.endTransaction();
						}
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					
					
					try{    
						//櫃員主檔

						File file = new File(F6000);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(F6000);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
							int Rcount=0;
							while ((ch = in.read()) > -1) {
									buffers.append((char)ch);
									if (buffers.toString().indexOf("\r\n")>0){
										Rcount+=1;
										if (Rcount>1){  //第一 筆為欄位列
											String fields=buffers.toString().substring(0,buffers.toString().length()-2);
											if (fields==null || fields.length()<=0) {}	else{
											String[] arrayField = fields.toString().split(",");
											Cursor csC=db.rawQuery("select "+qemArray[0]+" from qcust where "+qcuArray[0]+"="+arrayField[D6000q40Array[0]], null);
											if (csC.getCount()<=0){  //沒有重複的客戶編號
												
												ContentValues valueF = new ContentValues();
												for(int ii=0;ii<D6000q40Array.length;ii++){  //轉入必要欄位
													valueF.put(qemArray[ii],arrayField[D6000q40Array[ii]].replace("'", ""));
												}
												//存入預設的欄位
													valueF.put("TR","40");
													valueF.put("GPSLA",0);
													valueF.put("GPSLO",0);
													valueF.put("BANKNO","1");
													valueF.put("BANKNAME",BANKNAME);
													valueF.put("STATE","1");
													valueF.put("COUNTRY","1");
													valueF.put("TRADETYPE","5");
													valueF.put("DISCOUNT",70);
													valueF.put("C1","1");valueF.put("M1","1");valueF.put("S1","1");
													valueF.put("C2","0");valueF.put("M2","0");valueF.put("S2","0");
													valueF.put("C3","0");valueF.put("M3","0");valueF.put("S3","0");
													valueF.put("PIC","drawable/icon");
													db.insert("qcust", null, valueF);
/*
										}else{  //更新
												
												StringBuffer buff=new StringBuffer();
												buff.append("update qcust set ");
												for (int ij=0;ij<D6000q40Array.length-1;ij++){
													buff.append(qemArray[ij]+"="+arrayField[D6000q40Array[ij]]+",");
												}
												buff.append(qemArray[D6000q40Array.length-1]+"="+arrayField[D6000q40Array[D6000q40Array.length-1]]);
												buff.append(" where "+qemArray[0]+"="+arrayField[D6000q40Array[0]]);
												db.execSQL(buff.toString(),null);
*/												
										}
											
										}
									}
										buffers.delete(0, buffers.toString().length()	);
									}
								}
							in.close();  //
							
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					
					try{    
						//門市專櫃主檔 或 廠商主檔
						File file = new File(F7000);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(F7000);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
							int Rcount=0;
							db.beginTransaction();
							try{
							
								while ((ch = in.read()) > -1) {
									buffers.append((char)ch);
										if(buffers.toString().indexOf("\r\n")>0){
											Rcount+=1;
											if(Rcount>1){  //第一 筆為欄位列
												String fields=buffers.toString().substring(0,buffers.toString().length()-2);
												if (fields==null || fields.length()<=0) {}	else{
												String[] arrayField = fields.toString().split(",");
												if (arrayField[5].substring(1, arrayField[5].length()-1).equalsIgnoreCase("門市")){ //專櫃門市	
													Cursor csC=db.rawQuery("select "+qdpArray[0]+" from qcust where "+qdpArray[0]+"="+arrayField[D7000qcu20Array[0]]+" and TR='20'", null);
													if (csC.getCount()<=0){  //沒有重複的門市編號
														
														ContentValues valueF = new ContentValues();
														for(int ii=0;ii<D7000qcu20Array.length;ii++){  //轉入必要欄位
															valueF.put(qdpArray[ii],arrayField[D7000qcu20Array[ii]].replace("'", ""));
														}
														//存入預設的欄位
															valueF.put("TR","20");
															valueF.put("DEPTNO","AA");
															valueF.put("GPSLA",0);
															valueF.put("GPSLO",0);
															valueF.put("BANKNO","1");
															valueF.put("BANKNAME",BANKNAME);
															valueF.put("STATE","1");
															valueF.put("COUNTRY","1");
															valueF.put("TRADETYPE","5");
															valueF.put("DISCOUNT",70);
															valueF.put("C1","1");valueF.put("M1","1");valueF.put("S1","1");
															valueF.put("C2","0");valueF.put("M2","0");valueF.put("S2","0");
															valueF.put("C3","0");valueF.put("M3","0");valueF.put("S3","0");
															valueF.put("PIC","drawable/icon");
															db.insert("qcust", null, valueF);
													}
	
	//			 	int D7000qfaArray[]={7,8,13,34,10,12,43,18,29,20,35,15,47};
	//				String qfaArray[]={"FACTNO","FACTNAME","MOBIL","EMAIL","MSN","GMAIL","EMPID","DISCOUNT","ADDRESS","PS","COUNTRY","TRADETYPE","CREATEDATE"};	
					//補充預設欄位 TR=10,GPSLA=0,GPSLO=0,BANKNO=3,BANKNAME='外幣',(16)COUNTRY需比對 STATE=0 PIC=
					//只挑(5)成品廠商
				 									
				 
												}else if (arrayField[5].substring(1, arrayField[5].length()-1).equalsIgnoreCase("成品廠商") || arrayField[5].substring(1, arrayField[5].length()-1).equalsIgnoreCase("原料廠商")){ //廠商
													Cursor csC=db.rawQuery("select "+qfaArray[0]+" from qfact where "+qfaArray[0]+"="+arrayField[D7000qfaArray[0]], null);
													if (csC.getCount()<=0){  //沒有重複的門市編號
														
														ContentValues valueF = new ContentValues();
														for(int ii=0;ii<D7000qfaArray.length;ii++){  //轉入必要欄位
															valueF.put(qfaArray[ii],arrayField[D7000qfaArray[ii]].replace("'", ""));
														}
														//存入預設的欄位
															valueF.put("TR","10");
															valueF.put("DEPTNO","AA");
															valueF.put("GPSLA",0);
															valueF.put("GPSLO",0);
															valueF.put("BANKNO","1");
															valueF.put("BANKNAME",BANKNAME);
															valueF.put("STATE","1");
															valueF.put("COUNTRY","3");
															valueF.put("PIC","drawable/icon");
															db.insert("qfact", null, valueF);
													}
													
												}
												}												
											}
											buffers.delete(0, buffers.toString().length()	);
										}
									}
								in.close();  //
					            db.setTransactionSuccessful();
							}
							catch(Exception e){
							 
							}
							 
							finally{
							           //finish
							          db.endTransaction();
							}
							
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					
					try{    
						//現行資料庫存檔DP000
						File file = new File(ADP000);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(ADP000);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
							int Rcount=0;
							db.beginTransaction();
						try{
							
							while ((ch = in.read()) > -1){
								buffers.append((char)ch);
									if(buffers.toString().indexOf("\r\n")>0){
										Rcount+=1;
										if(Rcount>1){  //第一 筆為欄位列
											String fields=buffers.toString().substring(0,buffers.toString().length()-2);
											if (fields==null || fields.length()<=0) {}	else{
											String[] arrayField = fields.toString().split(",");
											ContentValues valueF = new ContentValues();
											//共通欄位
											valueF.put("TR","Y0");
											valueF.put("ID","1000");
											valueF.put("HANDNO","+1");
											valueF.put("ISCONFIRM","Y");
											valueF.put("ISCHECK","N");
											valueF.put("CREATEDATETIME","30101010101581");
											valueF.put("ORDCASH","0");
											valueF.put("ORDCARD","0");
											valueF.put("CARD","0");
											valueF.put("ATOT","1");
											valueF.put("BILLRATE","1");
											valueF.put("ORDCASH","0");
											valueF.put("ATOT","1");
											valueF.put("PS","");
											valueF.put("PS1","");
											valueF.put("PS2","");
											valueF.put("ATM","0");
											valueF.put("ATMDATE","");
											valueF.put("TRCASH","0");
											valueF.put("BANKNO","1");
											valueF.put("BANKNAME",BANKNAME);
/*
 	int ADP000Array[]={0,1,2,3,4,5,6,7,8,9,10};
	String AqheadArray[]={"FORMDATE","DEPTNO","USER","USERNAME","QKIND","FORMNO","EMPID","TRADETAYPE","ASUM","COSTS","CASH"};
	ATM=0,TRCASH=0,PS='',PS1='',PS2=''
	
 */
											String HNO="";  //單號初始
											//日期 & 結帳日
											String fDate=arrayField[ADP000Array[0]];valueF.put(AqheadArray[0],fDate.substring(1,5)+"-"+fDate.substring(5,7)+"-"+fDate.substring(7,9));
											if (Utilis.toInt(arrayField[ADP000Array[10]].replace("'",""))!=0){
												valueF.put("FINEDATE",fDate.substring(1,5)+"-"+fDate.substring(5,7)+"-"+fDate.substring(7,9));
											}
											//DEPTNO
											if (arrayField[ADP000Array[4]].equalsIgnoreCase("'100'")){  //100
												valueF.put(AqheadArray[1],"AA");
												valueF.put(AqheadArray[4],"10");
												valueF.put(AqheadArray[7],"1");
												HNO="AA113"+arrayField[ADP000Array[5]].substring(arrayField[ADP000Array[5]].length()-7,arrayField[ADP000Array[5]].length()-1);
											}else if (arrayField[ADP000Array[1]].equalsIgnoreCase("'00'") && arrayField[ADP000Array[4]].equalsIgnoreCase("'210'")){ //總倉銷貨
												valueF.put(AqheadArray[1],"AA");
												valueF.put(AqheadArray[4],"30");
												HNO="AA133"+arrayField[ADP000Array[5]].substring(arrayField[ADP000Array[5]].length()-7,arrayField[ADP000Array[5]].length()-1);
												valueF.put(AqheadArray[7],arrayField[ADP000Array[7]].replace("'", ""));
											}else if (arrayField[ADP000Array[4]].equalsIgnoreCase("'610'")){ //門市轉貨單
												valueF.put(AqheadArray[1],"AA");
												valueF.put(AqheadArray[4],"20");
												HNO="AA123"+arrayField[ADP000Array[5]].substring(arrayField[ADP000Array[5]].length()-7,arrayField[ADP000Array[5]].length()-1);
												valueF.put(AqheadArray[7],"1");
											}else if (arrayField[ADP000Array[4]].equalsIgnoreCase("'600'")){ //門市轉回單(20)
												valueF.put(AqheadArray[1],"AA");
												valueF.put(AqheadArray[4],"20");
												HNO="AA123"+arrayField[ADP000Array[5]].substring(arrayField[ADP000Array[5]].length()-7,arrayField[ADP000Array[5]].length()-1);
												valueF.put(AqheadArray[7],"1");
												//789值為負 
											}else{  //門市銷貨單(31)
												valueF.put(AqheadArray[1],arrayField[ADP000Array[1]].replace("'",""));
												valueF.put(AqheadArray[4],"31");
												HNO=arrayField[ADP000Array[1]].substring(1,arrayField[ADP000Array[1]].length()-1)+"133"+arrayField[ADP000Array[5]].substring(arrayField[ADP000Array[5]].length()-7,arrayField[ADP000Array[5]].length()-1);
												valueF.put(AqheadArray[7],arrayField[ADP000Array[7]].replace("'", ""));
											}
												valueF.put(AqheadArray[2],arrayField[ADP000Array[2]].replace("'",""));
												valueF.put(AqheadArray[3],arrayField[ADP000Array[3]].replace("'",""));
												valueF.put(AqheadArray[5],HNO); //各式單據單號
												valueF.put(AqheadArray[6],arrayField[ADP000Array[6]].replace("'",""));
												if(arrayField[ADP000Array[4]].equalsIgnoreCase("'600'")){
													valueF.put(AqheadArray[8],""+Utilis.toInt(arrayField[ADP000Array[8]].replace("'",""))*-1);
													valueF.put(AqheadArray[9],""+Utilis.toInt(arrayField[ADP000Array[9]].replace("'",""))*-1);													
													valueF.put(AqheadArray[10],""+Utilis.toInt(arrayField[ADP000Array[10]].replace("'",""))*-1);
													valueF.put(AqheadArray[11],""+Utilis.toInt(arrayField[ADP000Array[11]].replace("'",""))*-1);
													valueF.put("TURESUM",""+Utilis.toInt(arrayField[ADP000Array[9]].replace("'",""))*-1);
													valueF.put("BSUM",""+(Utilis.toInt(arrayField[ADP000Array[8]].replace("'",""))-Utilis.toInt(arrayField[ADP000Array[11]].replace("'","")))*-1);													
												}else{
													valueF.put(AqheadArray[8],arrayField[ADP000Array[8]].replace("'",""));
													valueF.put(AqheadArray[9],arrayField[ADP000Array[9]].replace("'",""));
													valueF.put(AqheadArray[10],arrayField[ADP000Array[10]].replace("'",""));
													valueF.put(AqheadArray[11],""+arrayField[ADP000Array[11]].replace("'",""));
													valueF.put("TURESUM",arrayField[ADP000Array[9]].replace("'",""));
													valueF.put("BSUM",""+(Utilis.toInt(arrayField[ADP000Array[8]].replace("'",""))-Utilis.toInt(arrayField[ADP000Array[11]].replace("'",""))));													
												}

											db.insert("qhead", null, valueF);
											
										}
										buffers.delete(0, buffers.toString().length()	);
									}
								}
							}
							
							in.close();  //
				            db.setTransactionSuccessful();
						}
						catch(Exception e){
						 
						}
						 
						finally{
						           //finish
						          db.endTransaction();
						}
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					
					try{    
						//現行資料庫存檔DP000
						File file = new File(TBDP000);
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							fis = new FileInputStream(TBDP000);
							InputStreamReader isr = new InputStreamReader(fis, encodout);
							Reader in = new BufferedReader(isr);
							int ch;

 							int Rcount=0;
 							while ((ch = in.read()) > -1){
								buffers.append((char)ch);
									if(buffers.toString().indexOf("\r\n")>0){
										Rcount+=1;
										if(Rcount>1){  //第一 筆為欄位列
											String fields=buffers.toString().substring(0,buffers.toString().length()-2);
											String[] arrayField = fields.toString().split(",");
											ContentValues valueF = new ContentValues();
											//共通欄位
											valueF.put("TR","Y0");
											valueF.put("ID","1000");
											valueF.put("HANDNO","+1");
											valueF.put("ISCONFIRM","Y");
											valueF.put("ISCHECK","N");
											valueF.put("CREATEDATETIME","30101010101581");
											valueF.put("ORDCASH","0");
											valueF.put("ORDCARD","0");
											valueF.put("CARD","0");
											valueF.put("ATOT","1");
											valueF.put("BILLRATE","1");
											valueF.put("ORDCASH","0");
											valueF.put("DISMONEY","0");
											valueF.put("BANKNO","1");
											valueF.put("BANKNAME",BANKNAME);
											String HNO="";  //單號初始
												valueF.put("FORMDATE",Ldate);
											//DEPTNO
											if (arrayField[BDP000Array[1]].equalsIgnoreCase("'100'")){  //廠商進貨單
												valueF.put(BqheadArray[0],"AA");
												valueF.put(BqheadArray[1],"10");
												valueF.put("TRADETYPE","1");
												HNO="AA112"; HNO=getNFormno(HNO);
											}else if (arrayField[BDP000Array[0]].equalsIgnoreCase("'00'") && arrayField[BDP000Array[1]].equalsIgnoreCase("'210'")){ //總倉銷貨
												valueF.put(BqheadArray[0],"AA");
												valueF.put(BqheadArray[1],"30");
												HNO="AA132"; HNO=getNFormno(HNO);
												valueF.put("TRADETYPE","3");
											}else if (arrayField[BDP000Array[1]].equalsIgnoreCase("'610'")){ //門市轉貨單
												valueF.put(BqheadArray[0],"AA");
												valueF.put(BqheadArray[1],"20");
												HNO="AA122"; HNO=getNFormno(HNO);
												valueF.put("TRADETYPE","1");
											}else if (arrayField[BDP000Array[1]].equalsIgnoreCase("'600'")){ //門市轉回單(20)
												valueF.put(BqheadArray[0],"AA");
												valueF.put(BqheadArray[1],"20");
												HNO="AA122"; HNO=getNFormno(HNO);
												valueF.put("TRADETYPE","1");
												//789值為負 
											}else{  //門市銷貨單(31)
												valueF.put(BqheadArray[0],arrayField[BDP000Array[0]].replace("'",""));
												valueF.put(BqheadArray[1],"31");
												HNO=arrayField[BDP000Array[0]].substring(1,arrayField[BDP000Array[0]].length()-1)+"132"; HNO=getNFormno(HNO);
												valueF.put("TRADETYPE","5");
											}
												valueF.put(BqheadArray[2],arrayField[BDP000Array[2]].replace("'",""));
												valueF.put(BqheadArray[3],arrayField[BDP000Array[3]].replace("'",""));
												valueF.put("FORMNO",HNO); //各式單據單號
												if(arrayField[BDP000Array[1]].equalsIgnoreCase("'600'")){
													valueF.put(BqheadArray[4],""+Utilis.toInt(arrayField[BDP000Array[4]].replace("'",""))*-1);
													valueF.put(BqheadArray[5],""+Utilis.toInt(arrayField[BDP000Array[5]].replace("'",""))*-1);													
													valueF.put(BqheadArray[6],""+Utilis.toInt(arrayField[BDP000Array[6]].replace("'",""))*-1);
													valueF.put(BqheadArray[7],""+Utilis.toInt(arrayField[BDP000Array[7]].replace("'",""))*-1);
													valueF.put("TURESUM",""+Utilis.toInt(arrayField[BDP000Array[5]].replace("'",""))*-1);
													valueF.put("BSUM",""+Utilis.toInt(arrayField[BDP000Array[4]].replace("'",""))*-1);													
												}else{
													valueF.put(BqheadArray[4],arrayField[BDP000Array[4]].replace("'",""));
													valueF.put(BqheadArray[5],arrayField[BDP000Array[5]].replace("'",""));													
													valueF.put(BqheadArray[6],arrayField[BDP000Array[6]].replace("'",""));
													valueF.put(BqheadArray[7],arrayField[BDP000Array[7]].replace("'",""));
													valueF.put("TURESUM",arrayField[BDP000Array[5]].replace("'",""));
													valueF.put("BSUM",arrayField[BDP000Array[4]].replace("'",""));													
												}

											db.insert("qhead", null, valueF);
											
										}
										buffers.delete(0, buffers.toString().length()	);
									}
							}
							in.close();  //
						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
					
						//現行資料庫存檔DP001
					          try
					          { 
									File file = new File(ADP001);
									if(file.exists() == true){
										String encodouts = "BIG5";

										StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
										
										FileInputStream fiss = new FileInputStream(ADP001);
										InputStreamReader isr = new InputStreamReader(fiss, encodouts);
										Reader in = new BufferedReader(isr);
										int ch;
			 							int Rcount=0;
										db.beginTransaction();
									try{
			 							while ((ch = in.read()) > -1){
											buffers.append((char)ch);
												if(buffers.toString().indexOf("\r\n")>0){
													Rcount+=1;
													if(Rcount>1){  //第一 筆為欄位列
														String fields=buffers.toString().substring(0,buffers.toString().length()-2);
														String[] arrayField = fields.toString().split(",");
														ContentValues valueF = new ContentValues();
														//共通欄位
														valueF.put("TR","Y0");
														valueF.put("ID","0");
														valueF.put("ISCONFIRM","Y");
														valueF.put("ISCHECK","N");
														valueF.put("CREATEDATETIME","30101010101581");
														valueF.put("COLORS",arrayField[12].replace("'",""));
														valueF.put("SIZES",arrayField[11].replace("'",""));
													if (arrayField[8].lastIndexOf("-")>2 ){
														valueF.put("COLOR",arrayField[8].substring(arrayField[8].lastIndexOf("-")-2,arrayField[8].lastIndexOf("-")));
														valueF.put("UNIT2",arrayField[8].substring(arrayField[8].lastIndexOf("-")-2,arrayField[8].lastIndexOf("-")));
													} else{
														valueF.put("COLOR","00");
														valueF.put("UNIT2","00");
													}
														valueF.put("BATCH",arrayField[7].replace("'",""));
														valueF.put("RATE","1");
														valueF.put("SEASON","0");
														valueF.put("COUNTRY","韓國");
														valueF.put("YEAR","2012");
													for(int ii=4;ii<=22;ii++){
														valueF.put(AqitemsArray[ii],arrayField[ADP001Array[ii]].replace("'",""));
													}
													//段數
													valueF.put(AqitemsArray[28],arrayField[ADP001Array[28]].replace("'",""));
													
													//增設來源編號
													if (arrayField[ADP001Array[25]] !=null && arrayField[ADP001Array[25]].length()>0)
														valueF.put(AqitemsArray[25],arrayField[ADP001Array[25]].replace("'",""));
													else
														valueF.put(AqitemsArray[25],"NONAME");
													
													valueF.put(AqitemsArray[26],myImportPath+File.separator+"PHOTO"+File.separator+arrayField[ADP001Array[26]].replace("'","")+".jpg");
														String HNO="";  //單號初始
														//日期 & 結帳日
														String fDate=arrayField[ADP001Array[0]];valueF.put(AqitemsArray[0],fDate.substring(1,5)+"-"+fDate.substring(5,7)+"-"+fDate.substring(7,9));
														//DEPTNO
														if (arrayField[ADP001Array[2]].equalsIgnoreCase("'100'")){  //100
															valueF.put(AqitemsArray[1],"AA");
															valueF.put(AqitemsArray[2],"10");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT10",arrayField[23].replace("'",""));
															HNO="AA113"+arrayField[ADP001Array[3]].substring(arrayField[ADP001Array[3]].length()-7,arrayField[ADP001Array[3]].length()-1);
														}else if (arrayField[ADP001Array[1]].equalsIgnoreCase("'00'") && arrayField[ADP001Array[2]].equalsIgnoreCase("'210'")){ //總倉銷貨
															valueF.put(AqitemsArray[1],"AA");
															valueF.put(AqitemsArray[2],"30");
															valueF.put("TRADETYPE","3");
															valueF.put("UNIT30",arrayField[23].replace("'",""));
															HNO="AA133"+arrayField[ADP001Array[3]].substring(arrayField[ADP001Array[3]].length()-7,arrayField[ADP001Array[3]].length()-1);
														}else if (arrayField[ADP001Array[2]].equalsIgnoreCase("'610'")){ //門市轉貨單
															valueF.put(AqitemsArray[1],"AA");
															valueF.put(AqitemsArray[2],"20");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT20",arrayField[23].replace("'",""));
															HNO="AA123"+arrayField[ADP001Array[3]].substring(arrayField[ADP001Array[3]].length()-7,arrayField[ADP001Array[3]].length()-1);
														}else if (arrayField[ADP001Array[2]].equalsIgnoreCase("'600'")){ //門市轉回單(20)
															valueF.put(AqitemsArray[1],"AA");
															valueF.put(AqitemsArray[2],"20");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT20",""+Utilis.toInt(arrayField[23].replace("'",""))*-1);
															HNO="AA123"+arrayField[ADP001Array[3]].substring(arrayField[ADP001Array[3]].length()-7,arrayField[ADP001Array[3]].length()-1);
															//789值為負 
														}else{  //門市銷貨單(31)
															valueF.put(AqitemsArray[1],arrayField[ADP001Array[1]].replace("'",""));
															valueF.put(AqitemsArray[2],"31");
															valueF.put("UNIT30",arrayField[23].replace("'",""));
															HNO=arrayField[ADP001Array[1]].substring(1,arrayField[ADP001Array[1]].length()-1)+"133"+arrayField[ADP001Array[3]].substring(arrayField[ADP001Array[3]].length()-7,arrayField[ADP001Array[3]].length()-1);
															valueF.put("TRADETYPE","5");
														}
															valueF.put(AqitemsArray[3],HNO); //各式單據單號
//															valueF.put(AqitemsArray[6],arrayField[ADP001Array[6]].substring(1,arrayField[ADP001Array[6]].length()-1));
															if(arrayField[ADP001Array[4]].equalsIgnoreCase("'600'")){
																valueF.put(AqitemsArray[23],""+Utilis.toInt(arrayField[ADP001Array[23]].replace("'",""))*-1);													
																valueF.put(AqitemsArray[24],""+Utilis.toInt(arrayField[ADP001Array[24]].replace("'",""))*-1);
															}else{
																valueF.put(AqitemsArray[23],arrayField[ADP001Array[23]].replace("'",""));
																valueF.put(AqitemsArray[24],arrayField[ADP001Array[24]].replace("'",""));
															}
														db.insert("qitems", null, valueF);
														
													}
													buffers.delete(0, buffers.toString().length()	);
												}
										}
										in.close();  //
							            db.setTransactionSuccessful();
									}
									catch(Exception e){
									 
									}
									 
									finally{
									           //finish
									          db.endTransaction();
									}
									}
					        	} catch (Exception e) {
					        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
					        	}
					          
					          
/*					          
					try{    
						//現行期初商品明細資料TBDP001
						
*/					        	
					          try
					          {
				        	  
									File file = new File(TBDP001);
									if(file.exists() == true){
										String encodouts = "BIG5";

										StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
										
										FileInputStream fiss = new FileInputStream(TBDP001);
										InputStreamReader isr = new InputStreamReader(fiss, encodouts);
										Reader in = new BufferedReader(isr);
										int ch;
			 							int Rcount=0;
			 							while ((ch = in.read()) > -1){
											buffers.append((char)ch);
												if(buffers.toString().indexOf("\r\n")>0){
													Rcount+=1;
													if(Rcount>1){  //第一 筆為欄位列
													String fields=buffers.toString().substring(0,buffers.toString().length()-2);
													String[] arrayField = fields.toString().split(",");
													Cursor csi=db.rawQuery("select GOODSNO from qitems where GOODSNOS="+arrayField[0]+" limit 1", null);
											
											if(csi.getCount()>0){
												//表示有找到本期交易的商品,須建立期初數據
														ContentValues valueF = new ContentValues();
														//共通欄位
														for(int ii=3;ii<=18;ii++){
															valueF.put(BqitemsArray[ii],arrayField[BDP001Array[ii]].substring(1,arrayField[BDP001Array[ii]].length()-1));
														}
														//SOURCENO
														if (arrayField[BDP001Array[21]] !=null && arrayField[BDP001Array[21]].length()>0)
															valueF.put(BqitemsArray[21],arrayField[BDP001Array[21]].replace("'", ""));
														else
															valueF.put(BqitemsArray[21],"NONAME");
														
														valueF.put(BqitemsArray[22],myImportPath+File.separator+"PHOTO"+File.separator+arrayField[BDP001Array[22]].replace("'", "")+".jpg");
														
														valueF.put("GOODSNOS",arrayField[0].replace("'", ""));
														valueF.put("TR","Y0");
														valueF.put("ID","0");
														valueF.put("ISCONFIRM","Y");
														valueF.put("ISCHECK","N");
														valueF.put("CREATEDATETIME","30101010101581");
														valueF.put("COLORS",arrayField[9].replace("'", ""));
														valueF.put("SIZES",arrayField[8].replace("'", ""));
													if (arrayField[0].lastIndexOf("-")>2 ){
														valueF.put("COLOR",arrayField[0].substring(arrayField[0].lastIndexOf("-")-2,arrayField[0].lastIndexOf("-")));
														valueF.put("UNIT2",arrayField[0].substring(arrayField[0].lastIndexOf("-")-2,arrayField[0].lastIndexOf("-")));
													} else{
														valueF.put("COLOR","00");
														valueF.put("UNIT2","00");
													}
													//日期 & 結帳日
														valueF.put("FORMDATE",Ldate);
														valueF.put("BATCH",arrayField[5].replace("'", ""));
														valueF.put("RATE","1");
														valueF.put("SEASON","0");
														valueF.put("COUNTRY","韓國");
														valueF.put("YEAR","2012");
														String HNO="";  //單號初始

														//DEPTNO&QKIND
														if (arrayField[BDP001Array[1]].equalsIgnoreCase("'100'")){  //100
															valueF.put(BqitemsArray[2],"AA");
															valueF.put(BqitemsArray[1],"10");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT10",arrayField[19].replace("'", ""));
															HNO="AA112"; HNO=getNFormno(HNO);
														}else if (arrayField[BDP001Array[2]].equalsIgnoreCase("'00'") && arrayField[BDP001Array[1]].equalsIgnoreCase("'210'")){ //總倉銷貨
															valueF.put(BqitemsArray[2],"AA");
															valueF.put(BqitemsArray[1],"30");
															valueF.put("TRADETYPE","3");
															valueF.put("UNIT30",arrayField[19].replace("'", ""));
															HNO="AA132"; HNO=getNFormno(HNO);
														}else if (arrayField[BDP001Array[1]].equalsIgnoreCase("'610'")){ //門市轉貨單
															valueF.put(BqitemsArray[2],"AA");
															valueF.put(BqitemsArray[1],"20");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT20",arrayField[19].replace("'", ""));
															HNO="AA122"; HNO=getNFormno(HNO);
														}else if (arrayField[BDP001Array[1]].equalsIgnoreCase("'600'")){ //門市轉回單(20)
															valueF.put(BqitemsArray[2],"AA");
															valueF.put(BqitemsArray[1],"20");
															valueF.put("TRADETYPE","1");
															valueF.put("UNIT20",""+Utilis.toInt(arrayField[19].replace("'", ""))*-1);
															HNO="AA122"; HNO=getNFormno(HNO);
															//789值為負 
														}else{  //門市銷貨單(31)
															valueF.put(BqitemsArray[2],arrayField[BDP001Array[2]].replace("'", ""));
															valueF.put(BqitemsArray[1],"31");
															valueF.put("UNIT30",arrayField[19].replace("'", ""));
															HNO=arrayField[BDP001Array[1]].replace("'", "")+"132"; HNO=getNFormno(HNO);
															valueF.put("TRADETYPE","5");
														}
															valueF.put("FORMNO",HNO); //各式單據單號
															if(arrayField[BDP001Array[1]].equalsIgnoreCase("'600'")){
																valueF.put(BqitemsArray[19],""+Utilis.toInt(arrayField[BDP001Array[19]].replace("'", ""))*-1);													
																valueF.put(BqitemsArray[20],""+Utilis.toInt(arrayField[BDP001Array[20]].replace("'", ""))*-1);
															}else{
																valueF.put(BqitemsArray[19],arrayField[BDP001Array[19]].replace("'", ""));
																valueF.put(BqitemsArray[20],arrayField[BDP001Array[20]].replace("'", ""));
															}
														db.insert("qitems", null, valueF);
														
													}
													}
													buffers.delete(0, buffers.toString().length()	);
												}
										}
										in.close();  //
									
									}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}

					          try
					          { 
									File file = new File(AD2001);
									if(file.exists() == true){
										String encodouts = "BIG5";
										getTypeData(MainTainManage.this,db);
										getColorData(MainTainManage.this,db);
										StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
										
										FileInputStream fiss = new FileInputStream(AD2001);
										InputStreamReader isr = new InputStreamReader(fiss, encodouts);
										Reader in = new BufferedReader(isr);
										int ch;
			 							int Rcount=0;
			 							String mGoods="";
			 							String mGoodO="";
			 							String mBatch="";
			 							String mBatchO="";
			 							String mCol="";
			 							String mColN="";
			 							String mSiz="";
			 							int Same=0;
			 							int nSiz=0;
			 							int nCol=0;
			 							String[] arrayField_Last=new String[17];	

										
			 							while ((ch = in.read()) > -1){
											buffers.append((char)ch);
											if(buffers.toString().indexOf("\r\n")>0){
												Rcount+=1;
													String fields=buffers.toString().substring(0,buffers.toString().length()-2);
													String[] arrayField = fields.toString().split(",");
													mGoods=arrayField[2].replace("'", "");  //主貨編號
													mBatch=arrayField[16].replace("'","").replace("/", "");
													if (Rcount==1)	{
														mGoodO=mGoods;
														mBatchO=mBatch;
													}
												if (mGoods.equalsIgnoreCase(mGoodO)){
														if (Same==0) {
															mCol=arrayField[7].replace("'", "");
															mSiz=arrayField[8].replace("'", "");
															mColN=getColN(arrayField[7]).replace("'", "");
															nSiz=1;
															nCol=1;
														}else{
															if (mCol.indexOf(arrayField[7].replace("'", ""))<0){
																mCol=mCol+"，"+arrayField[7].replace("'", "");
																nCol=nCol+1;
																mColN=mColN+"，"+getColN(arrayField[7].replace("'", ""));
															}
															if (mSiz.indexOf(arrayField[8].replace("'", ""))<0){
																mSiz=mSiz+","+arrayField[8].replace("'", "");
																nSiz=nSiz+1;
															}
														}
														Same=Same+1;
												}else{//不同貨號時 或 不同的批號NEW_ITEMS
													
													ContentValues valueF = new ContentValues();
													
													for(int ii=0;ii<=4;ii++){
														valueF.put(AdetailArray[ii],arrayField_Last[AD2001Array[ii]].replace("'",""));
													}
													for(int ii=9;ii<=17;ii++){
														valueF.put(AdetailArray[ii],arrayField_Last[AD2001Array[ii]].replace("'",""));
													}
													
													int Tx=TypeStr.toString().indexOf(arrayField_Last[AD2001Array[5]].replace("'",""));
													if (Tx>=0){
														String aTypeStr=TypeStr.toString().substring(Tx);
														int Ty=aTypeStr.indexOf(",");
														int Tz=aTypeStr.indexOf(";");
														if (Tz>0)	valueF.put(AdetailArray[5],aTypeStr.substring(Ty+1,Tz));
													}else{
														valueF.put(AdetailArray[5],"117");  //default T恤
													}
													
													if (arrayField_Last[AD2001Array[16]].replace("'","").equalsIgnoreCase("1")){
														//是否為實收判斷
														valueF.remove(AdetailArray[3]);
														valueF.put(AdetailArray[3],("*"+arrayField_Last[AD2001Array[3]].replace("'","")));
													}
													
													valueF.put(AdetailArray[6],myImportPath+File.separator+"PHOTO"+File.separator+arrayField_Last[AD2001Array[6]].replace("'","")+".jpg");
													valueF.put("BATCH",mBatchO);
													valueF.put("CREATEDATETIME",arrayField_Last[16].replace("'","").replace("/", "-"));
													valueF.put("SEASON","4");
													valueF.put("COUNTRY","3");
													valueF.put("UNIT2","2012");
													valueF.put("UNIT","0");
													valueF.put("UNIT1","888");
													valueF.put("IMPO","99");
													valueF.put("STATE","0");
													valueF.put("QUALITY_ID","1");
													valueF.put("WASH_ID","1");
													valueF.put("ADDCOST","30");
													valueF.put("ACCOUNT","4");
													
													valueF.put("COLORNO",mColN);
													valueF.put("COLOR",mCol);
													valueF.put("SIZE",mSiz);
													valueF.put("IMPOS","0");
													valueF.put("OUTPOS","0");
													valueF.put("SPOS","0");
													valueF.put("SPOSS","0");
													valueF.put("IM_OUTSTK","0");
													valueF.put("IM_POSSTK","0");
													valueF.put("ORD","0");
													StringBuffer Inlist=new StringBuffer();
													for (int xx=0;xx<nCol;xx++){
														StringBuffer aInlist=new StringBuffer();
														for (int yy=0;yy<nSiz;yy++){
															 if (yy==0) aInlist.append("0"); else aInlist.append(",0");
														}
														if (xx==0)  Inlist.append(aInlist.toString()); else Inlist.append("，"+aInlist.toString());
													}
													valueF.put("INLIST",Inlist.toString());
													valueF.put("ATLIST",Inlist.toString());
													db.insert("qdetail", null, valueF);
													
													mCol=arrayField[7].replace("'", "");
													mSiz=arrayField[8].replace("'", "");
													mColN=getColN(arrayField[7].replace("'", ""));
													nSiz=1;
													nCol=1;
													Same=1;
													mGoods=arrayField[2].replace("'", "");  //主貨編號
													mGoodO=mGoods;
													mBatch=arrayField[16].replace("'","").replace("/", "");
													mBatchO=mBatch;
												}
												arrayField_Last=arrayField;
												buffers.delete(0, buffers.toString().length()	);
											}
			 							}
				 						//應該還有最後一筆
										ContentValues valueF = new ContentValues();
										for(int ii=0;ii<=4;ii++){
											valueF.put(AdetailArray[ii],arrayField_Last[AD2001Array[ii]].replace("'",""));
										}
										for(int ii=9;ii<=15;ii++){
											valueF.put(AdetailArray[ii],arrayField_Last[AD2001Array[ii]].replace("'",""));
										}
										
										int Tx=TypeStr.toString().indexOf(arrayField_Last[AD2001Array[5]].replace("'",""));
										if (Tx>=0){
											String aTypeStr=TypeStr.toString().substring(Tx,40);
											int Ty=aTypeStr.indexOf(",");
											int Tz=aTypeStr.indexOf(";");
											if (Tz>0)	valueF.put(AdetailArray[5],aTypeStr.substring(Ty+1,Tz-1));
										}else{
											valueF.put(AdetailArray[5],"117");  //default T恤
										}
										valueF.put(AdetailArray[6],myImportPath+File.separator+"PHOTO"+File.separator+arrayField_Last[AD2001Array[6]].replace("'","")+".jpg");
										valueF.put("BATCH",mBatchO);
										valueF.put("CREATEDATETIME",arrayField_Last[16].replace("'","").replace("/", "-"));
										valueF.put("SEASON","4");
										valueF.put("COUNTRY","3");
										valueF.put("UNIT2","2012");
										valueF.put("UNIT","0");
										valueF.put("UNIT1","888");
										valueF.put("IMPO","99");
										valueF.put("STATE","0");
										valueF.put("QUALITY_ID","1");
										valueF.put("WASH_ID","1");
										valueF.put("ADDCOST","30");
										valueF.put("ACCOUNT","4");
										
										valueF.put("COLORNO",mColN);
										valueF.put("COLOR",mCol);
										valueF.put("SIZE",mSiz);
										valueF.put("IMPOS","0");
										valueF.put("OUTPOS","0");
										valueF.put("SPOS","0");
										valueF.put("SPOSS","0");
										valueF.put("IM_OUTSTK","0");
										valueF.put("IM_POSSTK","0");
										valueF.put("ORD","0");
										
										StringBuffer Inlist=new StringBuffer();
										for (int xx=0;xx<nCol;xx++){
											StringBuffer aInlist=new StringBuffer();
											for (int yy=0;yy<nSiz;yy++){
												 if (yy==0) aInlist.append("0"); else aInlist.append(",0");
											}
											if (xx==0)  Inlist.append(aInlist.toString()); else Inlist.append("，"+aInlist.toString());
										}
										
										valueF.put("INLIST",Inlist.toString());
										valueF.put("ATLIST",Inlist.toString());
										db.insert("qdetail", null, valueF);
										
										in.close();  //
										
									}
					        	} catch (Exception e) {
					        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
					        	}
					          
								//臨時給CHswag 實收正常價轉換
								//NETPRICE.csv
								//'3019','雙口袋牛仔料襯外','2'
								
								
								try{    
									//歷史價
									File file = new File(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/data/NETPRICE.csv");
									if(file.exists() == true){
										
										StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
										FileInputStream fisp = new FileInputStream(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/data/NETPRICE.csv");
										InputStreamReader isr = new InputStreamReader(fisp, encodout);
										Reader in = new BufferedReader(isr);
										int ch;
			 							while ((ch = in.read()) > -1){
											buffers.append((char)ch);
											if(buffers.toString().indexOf("\r\n")>0){
													String fields=buffers.toString().substring(0,buffers.toString().length()-2);
													String[] arrayField = fields.toString().split(",");
													String Goodsno=arrayField[0].replace("'", "");
													
													if (!arrayField[2].replace("'", "").equalsIgnoreCase("2")){
													db.execSQL("update qdetail set GOODSNAME=('*' || GOODSNAME)  where GOODSNO='"+Goodsno+"' and GOODSNAME not LIKE '*%' ");
													db.execSQL("update qitems set GOODSNAME=('*' || GOODSNAME) where GOODSNO='"+Goodsno+"' and GOODSNAME not LIKE '*%' ");
													}
													db.execSQL("update qdetail set UNIT='"+arrayField[3].replace("'", "")+"' where GOODSNO='"+Goodsno+"'");
													db.execSQL("update qitems set ORDDATE='"+arrayField[3].replace("'", "")+"' where GOODSNO='"+Goodsno+"'");
													
													buffers.delete(0, buffers.toString().length());
											}
										}
									in.close();  //

									}
						        	} catch (Exception e) {
						        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
						        	}
								
					          db.close();
					
					break;
				}
				case R.id.layout_aapndn:  //上右 雲端同步
				{
					//雲端 && 總倉(店) 需先download them
					
					if (Utilis.haveInternet(MainTainManage.this)){  //有網路的狀態
						if (of_line!=0){ 
							UpDn=0; //0.update   1.download
							uriAPI = uriAPIpath+"/mupdate_yn.php";
							mupdate_qc(mUid,UpDn,"110000");  //可以傳回server_Q1~Q7的update時間130801165959
						}
					}
					
					break;					
				}
				
				case R.id.layout_a:  //下左4 顏色尺寸整合
				{
					//先蒐集QITEMS的所有主貨號 再一一整理出顏色尺寸組
					SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
					String SQL="update qitems set " +
							"COLORS=(select distinct COLOR from qdetail where qitems.GOODSNO=qdetail.GOODSNO)," +
							"SIZES=(select distinct SIZE from qdetail where qitems.GOODSNO=qdetail.GOODSNO )," +
							"UNIT2=(select distinct COLORNO from qdetail where qitems.GOODSNO=qdetail.GOODSNO)" ;
					 db.execSQL(SQL);

					 SQL="update qitems set GOODSNOS=(GOODSNO || '-' || COLOR || '-' || SIZE) where GOODSNOS not like '%-%'";
					 db.execSQL(SQL);
					 
					 db.close();
					break;					
				}

			case R.id.layout_addpndn:  //中右 下載雲端同步
				{
/*					//這裡是原始程式 臨停 作為測試 墊子發票機					
					if (Utilis.haveInternet(MainTainManage.this)){  //有網路的狀態
						if (of_line!=0){
							UpDn=1; //0.update   1.download					
							uriAPI = uriAPIpath+"/mupdate_yn.php";
							mupdate_qc(mUid,UpDn,"110000");  //可以傳回server_Q1~Q7的update時間130801165959
						} 
					}
					//雲端 && 總倉(店) 需先download them
*/
					binder.connectNetPort("192.168.1.97",9100, new UiExecute(){

						@Override
						public void onfailed() {
							// TODO Auto-generated method stub
			        		Log.i("POSConnect", "Dis_connect" );
						}

						@Override
						public void onsucess() {
							// TODO Auto-generated method stub
		                    ISCONNECT=true;
			        		Log.i("POSConnect", "connect_success" );
						/*	
		                    binder.acceptdatafromprinter(new UiExecute() {
								@Override
								public void onfailed() {
									// TODO Auto-generated method stub
		                            ISCONNECT=false;
									Toast.makeText(MainTainManage.this, "Dis_connect", Toast.LENGTH_LONG).show();
						            if (ISCONNECT){
						                binder.disconnectCurrentPort(new UiExecute() {
						                    @Override
						                    public void onsucess() {
												Toast.makeText(MainTainManage.this, "Dis_connect", Toast.LENGTH_LONG).show();
						                    }

						                    @Override
						                    public void onfailed() {
												Toast.makeText(MainTainManage.this, "connect_Failure", Toast.LENGTH_LONG).show();
						                    }
						                });
						            }else {
										Toast.makeText(MainTainManage.this, "Dis_connect", Toast.LENGTH_LONG).show();
						            }									
								}
								@Override
								public void onsucess() {
									// TODO Auto-generated method stub
									binder.writeDataByYouself(
							                new UiExecute() {
							                    @Override
							                    public void onsucess() {

							                    }

							                    @Override
							                    public void onfailed() {

							                    }
							                }, new ProcessData() {
							                    @Override
							                    public List<byte[]> processDataBeforeSend() {

							                        List<byte[]> list=new ArrayList<byte[]>();
							                        //creat a text ,and make it to byte[],
							                        String str="test is Vary Good ";
							                        if (str.equals(null)||str.equals("")){
														Toast.makeText(MainTainManage.this, str, Toast.LENGTH_LONG).show();
							                        }else {
							                            //  initialize the printer
							                            //  list.add( DataForSendToPrinterPos58.initializePrinter());

							                            list.add(DataForSendToPrinterPos80.initializePrinter());

							                            byte[] data1= Utilis.strTobytes(str, "Big5");

							                            list.add(data1);
							                            //should add the command of print and feed line,because print only when one line is complete, not one line, no print
							                            list.add(DataForSendToPrinterPos80.printAndFeedLine());
							                            //cut pager
							                            list.add(DataForSendToPrinterPos80.selectCutPagerModerAndCutPager(66,1));
							                            return list;
							                        }
							                        return null;
							                    }
							                });									
								}
		                    });
						 */									
						}
					});
/*
		            if (ISCONNECT){
		                binder.disconnectCurrentPort(new UiExecute() {
		                    @Override
		                    public void onsucess() {
								Toast.makeText(MainTainManage.this, "After_Dis_connect", Toast.LENGTH_LONG).show();
		                    }

		                    @Override
		                    public void onfailed() {
								Toast.makeText(MainTainManage.this, "After_connect_Failure", Toast.LENGTH_LONG).show();
		                    }
		                });
		            }else {
						Toast.makeText(MainTainManage.this, "After_Dis_connect", Toast.LENGTH_LONG).show();
		            }									
*/
					
					break;					
				}
				case R.id.layout_searchpndn:  //中左-暫存單維護
				{
					
					if(mFnoList != null){
						mFnoList.clear();
					}
					if(mKindList != null){
						mKindList.clear();
					}
					
					SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
					
					
					StringBuffer mySqlBuf = new StringBuffer();
			//以上QHEAD完成維護,如下畫面+件 -件 但是不會重算 ASUM BSUM				

					mySqlBuf.append("select FORMNO,QKIND  from qhead where FORMDATE>'2012-12-31' ");
					//先算出的
					String mySql=mySqlBuf.toString();
					Cursor csH= db.rawQuery(mySqlBuf.toString(), null);
					if (csH.getCount()>0){
					 while (csH.moveToNext()){
						 mFnoList.add(csH.getString(0));
						 mKindList.add(csH.getString(1));
						 }
					 
					 for (int xx=0;xx<mFnoList.size();xx++){
						 String msFno=mFnoList.get(xx);
						 String msKind=mKindList.get(xx);
						 int mHandP=0;
						 int mHandM=0;
					
						mySqlBuf.delete(0, mySqlBuf.toString().length());
						mySqlBuf.append("select sum(UNIT"+msKind.substring(0, 1)+"0) as UNITS");
						mySqlBuf.append(" from qitems where FORMNO ='" + msFno+"'");
						//先算出的
						mySql=mySqlBuf.toString();
						mySqlBuf.append(" and UNIT"+msKind.substring(0, 1)+"0 >=0 ");
						
						Cursor cursory = db.rawQuery(mySqlBuf.toString(), null);									
						if (cursory.getCount()>0) { //必然找到該單所有ITEMS,重新統計一次
							cursory.moveToFirst();
							mHandP=cursory.getInt(cursory.getColumnIndex("UNITS"));
						}else {
							mHandP=0;
							}
						
						cursory.close();
						
						mySqlBuf.delete(0, mySqlBuf.toString().length());
						mySqlBuf.append(mySql); //共用片段
						
						mySqlBuf.append(" and UNIT"+msKind.substring(0, 1)+"0 <0 ");
						
						Cursor cursorz = db.rawQuery(mySqlBuf.toString(), null);									
						if (cursorz.getCount()>0) { //必然找到該單所有ITEMS,重新統計一次
							cursorz.moveToFirst();
							mHandM=cursorz.getInt(cursorz.getColumnIndex("UNITS"));				
						}else {
							mHandM=0;
						}
						cursorz.close();
						String mHandno="+0";
							if (mHandM==0){
								mHandno="+"+mHandP;
							}else {
								mHandno="+"+mHandP+" "+mHandM;
							}
							ContentValues value = new ContentValues();

							value.put("ATOT",""+(mHandP+mHandM));
							value.put("HANDNO",mHandno);

							String[] args = {msFno};
							int success = db.update(TbName.QHEAD, value,"FORMNO=? ", args);
							
					}
					}
					db.close();
					mFnoList.clear();
					mKindList.clear();
					Toast.makeText(MainTainManage.this, "試算HANDNO_完成", Toast.LENGTH_LONG).show();
					
					
					break;					
				}
				case R.id.layout_transout:  //下左4 轉圖檔 
				{
					//pict.xml
					//PARTCODE INDEX FOR GOODSNO
					try{    
						//String encodout = "BIG5";
						String encodout1 = "ISO_8859_1";
						//將XML圖檔DATA轉成JPG檔的重要根據
						FileInputStream fis =null;
						
						String jpgNameS="";
						
 						File file = new File(FPHOTO);
						if(file.exists() == true){
							SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
							Cursor cs =null;
							FileOutputStream os =null;
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文

							fis = new FileInputStream(FPHOTO);
							InputStreamReader isr = new InputStreamReader(fis, encodout1);
							
							Reader in = new BufferedReader(isr);
							int ch;
							int Rcount=0;
							int Fncnt=0;
							StringBuffer PicName= new StringBuffer();
							byte[] buffer = new byte[1000000];
							while ((ch = in.read()) > -1) {
									buffers.append((char)ch);
									if (buffers.toString().indexOf("PARTCODE]]></name><value><![CDATA[")>0 && Fncnt<15){
										if (Fncnt>0) PicName.append((char)ch);
										Fncnt=Fncnt+1;
										 if (Fncnt==15){

											 jpgNameS=myImportPath+File.separator+ "PHOTO/"+PicName.toString()+".jpg";
											 
									          buffer = new byte[1000000];
									          os = new FileOutputStream(jpgNameS);
										 }
									} 
									//檢查是否在Qdetail Qitems 存在 再進行轉圖
									if (buffers.toString().indexOf("CDATA[PICT1]]></name><value><![CDATA[")>0 && Fncnt==15){
										if (buffers.toString().indexOf("]></value><datatype><![CDATA[ftBlob]]></")<0){
											//表示為圖檔的DATA
											buffer[Rcount]=(byte)ch;
											Rcount=Rcount+1;

										}else{
											//int serch=0;
											//cs = db.rawQuery("select distinct PIC from qdetail where PIC='"+jpgNameS+"'",null);
											//if (cs.getCount()>0) serch=1;
											//if (serch==0){
											//	cs = db.rawQuery("select distinct PIC from qitems where PIC='"+jpgNameS+"'",null);
											//	if (cs.getCount()>0) serch=1;
											//}
											
											//if (serch==1){
												Rcount=Rcount-40;
												os.write(buffer, 1, Rcount);
												os.flush();
											//}
												//os.close();
												buffers.delete(0, buffers.toString().length());
												PicName.delete(0, PicName.toString().length());
												Rcount=0;
												Fncnt=0;
										}
									}
									if (buffers.toString().indexOf("</row>")>0) buffers.delete(0, buffers.toString().length()	);

							}
						if (os!=null) os.close();
						in.close();  //
						fis.close();
						db.close();
						cs.close();
					}
		        	} catch (Exception e) {
		        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
		        	}
					
				break;	
				}
				case R.id.layout_transin:  //下右 畫面檔維護更新後執行
				{
					SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
					try{    
						//歷史價
						String encodout = "BIG5";
						File file = new File(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/data/NETPRICE.csv");
						if(file.exists() == true){
							
							StringBuffer buffers = new StringBuffer();  //buffer 讀取檔案全文
							FileInputStream fisp = new FileInputStream(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/data/NETPRICE.csv");
							InputStreamReader isr = new InputStreamReader(fisp, encodout);
							Reader in = new BufferedReader(isr);
							int ch;
 							while ((ch = in.read()) > -1){
								buffers.append((char)ch);
								if(buffers.toString().indexOf("\r\n")>0){
										String fields=buffers.toString().substring(0,buffers.toString().length()-2);
										String[] arrayField = fields.toString().split(",");
										String Goodsno=arrayField[0].replace("'", "");
										
										if (!arrayField[2].replace("'", "").equalsIgnoreCase("2")){
										db.execSQL("update qdetail set GOODSNAME=('*' || GOODSNAME)  where GOODSNO='"+Goodsno+"' and GOODSNAME not LIKE '*%' ");
										db.execSQL("update qitems set GOODSNAME=('*' || GOODSNAME) where GOODSNO='"+Goodsno+"' and GOODSNAME not LIKE '*%' ");
										}
										db.execSQL("update qdetail set UNIT='"+arrayField[3].replace("'", "")+"' where GOODSNO='"+Goodsno+"'");
										db.execSQL("update qitems set ORDDATE='"+arrayField[3].replace("'", "")+"' where GOODSNO='"+Goodsno+"'");
										
										buffers.delete(0, buffers.toString().length());
								}
							}
						in.close();  //

						}
			        	} catch (Exception e) {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());
			        	}
		          db.close();
				break;	
				}
				case R.id.layout_addyear:  //最右下 年度謄檔
				{
					//@@@20151111
					//每10000筆 約3Mb 計算
					//目前評估 每一家 年約 100000筆 約30Mb
					//目前評估 每一家 年約 150000筆 約45Mb

					//綜合判斷
					//如果現行資料庫>150000筆 則建議執行年度謄檔
					//包涵3年以上資料時,才需要執行年度謄檔
					//每年>六月,執行年度謄檔時,是最佳時機
					//每年<=六月,執行年度謄檔時,需保留多一年的資料
					//系統自動檢查,如果資料超過200000萬筆時且超過六月,建議執行年度謄檔
					//int numRows = db.rawQuery("SELECT FORMNO FROM QHEAD", null).getCount();
					//numRows += db.rawQuery("SELECT FORMNO FROM QITEMS", null).getCount();
					//Calendar c = Calendar.getInstance();
					//Integer mMonth = c.get(Calendar.MONTH)+1;
					//
					//if (numRows>150000 && mMonth>6) 
					//建議年度謄檔 每月檢查一次,提醒一次為限
					
					
				String isQyear=Utilis.getIni(MainTainManage.this, "SYS", "DEPT_SEND", 7);
				if (isQyear.equalsIgnoreCase("1")){ //執行年度謄檔
					//先強制備份
			      myDialog = ProgressDialog.show(MainTainManage.this,"年度謄檔即將開始","異地備份中,請稍候.............",true);//		setAutoRotationMode();			  
				  
			      new Thread()
			      { 
			        public void run()
			        { 
			          try
			          { 
						
	        			String DBZipPath=Utilis.copyDBtoSDCard(MainTainManage.this);
	    				if (DBZipPath.length()>0){
		    			    Uri U=Uri.parse("file://"+DBZipPath);  //重要的範例
	    					Intent send = new Intent(Intent.ACTION_SEND);
	    					send.setType("image/jpeg");
	    				    String uriText = "https://www.google.com/cloudprint/addpublicprinter.html?printerid=bb160853-6274-0f65-eb1f-34ae2c9ed6e1&key=19901109";
	    				    send.putExtra(Intent.ACTION_SENDTO,uriText);
	    				    send.putExtra(Intent.EXTRA_STREAM,U);
	    				    startActivity(Intent.createChooser(send, uriText));
	    				    
	    				}
			          }
			          catch (Exception e)
			          {
			        		Log.i("DB", "DB_DIR_Exception:_1 " + e.toString());					          }
			          finally
			          
			          {
			            // 卸載所建立的myDialog物件。
			            myDialog.dismiss();
			          }
			        }
			      }.start();
    				
						SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
						Cursor cs = db.rawQuery("select distinct substr(FORMDATE,1,4) from qhead order by FORMDATE desc",null);
						StringBuffer QY =new StringBuffer();
						if(cs != null){
							try{
								if(cs.getCount() > 0){
									cs.moveToFirst();
									QY.append(cs.getString(0));
									while (cs.moveToNext()){
										QY.append(";").append(cs.getString(0));
									}
								}
							}catch(Exception e){
								
							}finally{
								cs.close();
								cs = null;
							}
						}
						
							Toast.makeText(MainTainManage.this, "..."+QY.toString(), Toast.LENGTH_LONG).show();
							
							String[] arrayQyear =QY.toString().split(";"); //取得各年度的陣列 且由大而小
							//第一陣列 通常為 最大,不可騰檔除檔
							
							cs = db.rawQuery("select distinct substr(FORMDATE,1,4) from qhead order by FORMDATE",null);
							StringBuffer QY1 =new StringBuffer();
							if(cs != null){
								try{
									if(cs.getCount() > 0){
										cs.moveToFirst();
										QY1.append(cs.getString(0));
										while (cs.moveToNext()){
											QY1.append(";").append(cs.getString(0));
										}
									}
								}catch(Exception e){
									
								}finally{
									cs.close();
									cs = null;
								}
							}
						if (QY1.toString().indexOf(";")>0){//表示有2個年度以上 才需要執行年度謄檔
								Toast.makeText(MainTainManage.this, "..."+QY1.toString(), Toast.LENGTH_LONG).show();
								
								String[] arrayQyear1 =QY1.toString().split(";"); //取得各年度的陣列 且由大而小
								//第一陣列 通常為 最大,不可騰檔除檔
							boolean isit=true;
							String SQL="";
							for (int x=1;x<arrayQyear.length;x++){
								Toast.makeText(MainTainManage.this, "第一波__"+arrayQyear[x], Toast.LENGTH_LONG).show();
								
								if (doesTableExist(db, "qhead_"+arrayQyear[x])==false){
									SQL="create table IF NOT EXISTS qhead_"+arrayQyear[x]+" as select * from qhead where FORMDATE>='"+arrayQyear[x]+"-01-01'"+" and FORMDATE<='"+arrayQyear[x]+"-12-31'";
									//將各年度資料一一轉入 應有的年度QHEAD資料表
									db.execSQL(SQL);
									isit=false;
								}
								
								if (doesTableExist(db, "qitems_"+arrayQyear[x])==false){
									SQL="create table IF NOT EXISTS qitems_"+arrayQyear[x]+" as select * from qitems where FORMDATE>='"+arrayQyear[x]+"-01-01'"+" and FORMDATE<='"+arrayQyear[x]+"-12-31'";
									//將各年度資料一一轉入 應有的年度QITEMS資料表
									db.execSQL(SQL);
									isit=false;
								}

							}
							//以上完成各年度的交易檔 歸檔功能
							//將填入跨年的前期末轉本期初
							
							if (isit==false){
								
								String thisY=DateUtil.getCurrentDate().substring(0,4);
								String lastY=""+(Utilis.toInt(thisY)-1);
								Toast.makeText(MainTainManage.this, "第二波__"+thisY, Toast.LENGTH_LONG).show();
								
								SQL="delete from qhead where FORMDATE<'"+thisY+"-01-01'";
								db.execSQL(SQL);
								
								SQL="delete from qitems where FORMDATE<'"+thisY+"-01-01'";
								db.execSQL(SQL);

							
								for (int x=0;x<arrayQyear1.length-2;x++){
									
									SQL="insert into qhead_"+arrayQyear1[x+1]+" (TR,ID,QKIND,FORMNO,HANDNO,FORMDATE,CREATEDATETIME,ISCONFIRM,ISCHECK,ISLIST," +
											"EMPID,EMPNAME,USER,USERNAME,TRADETYPE,DEPTNO,ORDCASH,ORDCARD,CASH,CARD,ATOT,ORDDATE,FINEDATE," +
											"ASUM,BSUM,BILLRATE,COSTS,TURESUM,DISMONEY,BANKNO,BANKNAME,ATM,ATMDATE,TRCASH,PS,PS1,PS2) select 'Y0',0,QKIND,MAX(FORMNO),'+0'" +
											",'"+arrayQyear1[x]+"-12-31','"+arrayQyear1[x].substring(2)+"1231235959',ISCONFIRM,ISCHECK,'','SYS','SYS',USER,USERNAME,TRADETYPE,DEPTNO,sum(ORDCASH),sum(ORDCARD),sum(CASH),sum(CARD),sum(ATOT)," +
											"max(ORDDATE),max(FINEDATE),sum(ASUM),sum(BSUM),max(BILLRATE),sum(COSTS),0,sum(DISMONEY)," +
											"1,'AA0',sum(ATM),'',sum(TRCASH),'','','' from qhead_"+arrayQyear1[x]+" where ((ISCONFIRM='Y' and ISCHECK='N') or " +
											"(ISCONFIRM='N' and ISCHECK='Y')) group by QKIND,ISCONFIRM,ISCHECK,USER,USERNAME,TRADETYPE,DEPTNO";
	
									Toast.makeText(MainTainManage.this, "第三波__"+SQL, Toast.LENGTH_LONG).show();

									db.execSQL(SQL);
									
									SQL="insert into qitems_"+arrayQyear1[x+1]+" (TR,ID,QKIND,FORMNO,HANDNO,FORMDATE,CREATEDATETIME,ISCONFIRM,ISCHECK,EMPID," +
											"EMPNAME,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,UNIT,UNITPRICE," +
											"DISCOUNT,ACOST,RATE,RATEPRICE,REALSUM,SUBPRICE,P0,P1,P2,P3,P4,P5,COLOR,SIZE,ORDDATE,FINEDATE,BATCH," +
											"SUPPLY,GOODSTYPE,GOODSNAME,COLORS,SIZES,UNIT10,UNIT20,UNIT30,UNIT40,PIC,PS,SEASON,COUNTRY,YEAR" +
											") select 'Y0',0,QKIND,MAX(FORMNO),'+0','"+arrayQyear1[x]+"-12-31','"+arrayQyear1[x].substring(2)+"1231235959',ISCONFIRM,ISCHECK,'SYS','SYS'," +
											"USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,sum(UNIT),UNITPRICE,100" +
											",sum(ACOST),max(RATE),sum(RATEPRICE),sum(REALSUM),sum(SUBPRICE),max(P0),max(P1),max(P2)" +
											",max(P3),max(P4),max(P5),max(COLOR),max(SIZE),max(ORDDATE),max(FINEDATE),max(BATCH),SUPPLY" +
											",GOODSTYPE,max(GOODSNAME),max(COLORS),max(SIZES),sum(UNIT10),sum(UNIT20),sum(UNIT30),sum(UNIT40)" +
											",max(PIC),'',SEASON,COUNTRY,YEAR from qitems_"+arrayQyear1[x]+" WHERE ((ISCONFIRM='Y') or (ISCONFIRM='N'" +
											" and ISCHECK='Y')) and GOODSNO in (select DISTINCT GOODSNO from qitems_"+arrayQyear1[x+1]+")" +
											" group by  QKIND,ISCONFIRM,ISCHECK,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1" +
											",UNIT2,UNITPRICE,SUPPLY,GOODSTYPE,SEASON,COUNTRY,YEAR";
									
									db.execSQL(SQL);
									Toast.makeText(MainTainManage.this, "第四波__"+SQL, Toast.LENGTH_LONG).show();
									
								}

								SQL="insert into qhead (TR,ID,QKIND,FORMNO,HANDNO,FORMDATE,CREATEDATETIME,ISCONFIRM,ISCHECK,ISLIST," +
										"EMPID,EMPNAME,USER,USERNAME,TRADETYPE,DEPTNO,ORDCASH,ORDCARD,CASH,CARD,ATOT,ORDDATE,FINEDATE," +
										"ASUM,BSUM,BILLRATE,COSTS,TURESUM,DISMONEY,BANKNO,BANKNAME,ATM,ATMDATE,TRCASH,PS,PS1,PS2) select 'Y0',0,QKIND,MAX(FORMNO),'+0'" +
										",'"+lastY+"-12-31','"+lastY.substring(2)+"1231235959',ISCONFIRM,ISCHECK,'','SYS','SYS',USER,USERNAME,TRADETYPE,DEPTNO,sum(ORDCASH),sum(ORDCARD),sum(CASH),sum(CARD),sum(ATOT)," +
										"max(ORDDATE),max(FINEDATE),sum(ASUM),sum(BSUM),max(BILLRATE),sum(COSTS),0,sum(DISMONEY)," +
										"1,'AA0',sum(ATM),'',sum(TRCASH),'','','' from qhead_"+lastY+" where ((ISCONFIRM='Y' and ISCHECK='N') or " +
										"(ISCONFIRM='N' and ISCHECK='Y')) group by QKIND,ISCONFIRM,ISCHECK,USER,USERNAME,TRADETYPE,DEPTNO";

								Toast.makeText(MainTainManage.this, "第五波__"+SQL, Toast.LENGTH_LONG).show();

								db.execSQL(SQL);
								
								SQL="insert into qitems (TR,ID,QKIND,FORMNO,HANDNO,FORMDATE,CREATEDATETIME,ISCONFIRM,ISCHECK,EMPID," +
										"EMPNAME,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,UNIT,UNITPRICE," +
										"DISCOUNT,ACOST,RATE,RATEPRICE,REALSUM,SUBPRICE,P0,P1,P2,P3,P4,P5,COLOR,SIZE,ORDDATE,FINEDATE,BATCH," +
										"SUPPLY,GOODSTYPE,GOODSNAME,COLORS,SIZES,UNIT10,UNIT20,UNIT30,UNIT40,PIC,PS,SEASON,COUNTRY,YEAR" +
										") select 'Y0',0,QKIND,MAX(FORMNO),'+0','"+lastY+"-12-31','"+lastY.substring(2)+"1231235959',ISCONFIRM,ISCHECK,'SYS','SYS'," +
										"USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1,UNIT2,sum(UNIT),UNITPRICE,100" +
										",sum(ACOST),max(RATE),sum(RATEPRICE),sum(REALSUM),sum(SUBPRICE),max(P0),max(P1),max(P2)" +
										",max(P3),max(P4),max(P5),max(COLOR),max(SIZE),max(ORDDATE),max(FINEDATE),max(BATCH),SUPPLY" +
										",GOODSTYPE,max(GOODSNAME),max(COLORS),max(SIZES),sum(UNIT10),sum(UNIT20),sum(UNIT30),sum(UNIT40)" +
										",max(PIC),'',SEASON,COUNTRY,YEAR from qitems_"+lastY+" WHERE ((ISCONFIRM='Y') or (ISCONFIRM='N'" +
										" and ISCHECK='Y')) and GOODSNO in (select DISTINCT GOODSNO from qitems)" +
										" group by  QKIND,ISCONFIRM,ISCHECK,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1" +
										",UNIT2,UNITPRICE,SUPPLY,GOODSTYPE,SEASON,COUNTRY,YEAR ";
								
								db.execSQL(SQL);
								Toast.makeText(MainTainManage.this, "第六波__"+SQL, Toast.LENGTH_LONG).show();

								
								
							//製作QDETAIL_HIS
								isit=true;
								if (doesTableExist(db, "qdetail_his")==false){
									Toast.makeText(MainTainManage.this, "qdetail_his 初次建立中>....", Toast.LENGTH_LONG).show();
									SQL="create table IF NOT EXISTS qdetail_his as select * from qdetail";
									//將各年度資料一一轉入 應有的年度QITEMS資料表
									db.execSQL(SQL);
									isit=false;
								}
								
								
								if (isit==true){
									
									Toast.makeText(MainTainManage.this, "qdetail_his 維護中>....", Toast.LENGTH_LONG).show();
								
									
									SQL="INSERT INTO qdetail_his	(TR,MID,SUPPLY,COUNTRY,SOURCENO,GOODSNO,GOODSTYPE,GOODSNAME,UNIT1,UNIT2,UNIT,BATCH,BILL,BILLRATE,ADDCOST," +
									",P0,P1,P2,P3,P4,P5,SEASON,PS,CREATEDATETIME,STATE,PIC,PICTURE,QUALITY_ID,WASH_ID,COLORNO,COLOR" +
									",SIZE,INLIST,IMPO,IMPOS,OUTPO,OUTPOS,SPOS,SPOSS,IM_OUTSTK,IM_POSSTK,ORD,ACCOUNT,ATLIST) " +
									" SELECT qdetail.TR,qdetail.MID,qdetail.SUPPLY,qdetail.COUNTRY,qdetail.SOURCENO,qdetail.GOODSNO,qdetail.GOODSTYPE," +
									"qdetail.GOODSNAME,qdetail.UNIT1,qdetail.UNIT2,qdetail.UNIT,qdetail.BATCH,qdetail.BILL,qdetail.BILLRATE,qdetail.ADDCOST," +
									"qdetail.P0,qdetail.P1,qdetail.P2,qdetail.P3,qdetail.P4,qdetail.P5,qdetail.SEASON,qdetail.PS,qdetail.CREATEDATETIME,qdetail.STATE" +
									",qdetail.PIC,qdetail.PICTURE,qdetail.QUALITY_ID,qdetail.WASH_ID,COLORNO,qdetail.COLOR,qdetail.SIZE,qdetail.INLIST," +
									"qdetail.IMPO,qdetail.IMPOS,qdetail	.OUTPO,qdetail.OUTPOS,qdetail.SPOS,qdetail.SPOSS,qdetail.IM_OUTSTK,qdetail.IM_POSSTK,ORD," +
									"qdetail.ACCOUNT,qdetail.ATLIST FROM qdetail where qdetail.GOODSNO not in (select distinct GOODSNO from qdetail_his)";

									db.execSQL(SQL);
								}
								
								Toast.makeText(MainTainManage.this, "qdetail 維護中清除較早的資料>....", Toast.LENGTH_LONG).show();
								SQL="delete from qdetail where (qdetail.CREATEDATETIME<'"+lastY+"-01-01') and (GOODSNO NOT IN (select distinct GOODSNO from qitems))";
								//SQL="delete from qdetail where (qdetail.CREATEDATETIME<'"+lastY+"-01-01') and ((GOODSNO NOT IN (select distinct GOODSNO from qitems)) and (GOODSNO NOT IN (select distinct GOODSNO from qitems"+"_"+lastY+")))";
								db.execSQL(SQL);
								
							}
							
						}
						
						db.close();
						//cs.close();
						  
						 
					}
					
					break;
				}
				
				default:
				break;
			}
		}
	}
	
	
}