package com.cwbuyer.main;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.Calendar;
import android.annotation.SuppressLint;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.ProgressDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.SystemClock;
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 com.cwbuyer.lib.DBCloud;
import com.cwbuyer.lib.DateUtil;
import com.cwbuyer.lib.TbName;
import com.cwbuyer.lib.Utilis;
import com.pwbuyer.main.R;

@SuppressLint("InflateParams")
public class MainTainManage extends Activity {
	
//	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;
	
	String firstY=DateUtil.getCurrentDate().substring(0,4);
	String nowY=DateUtil.getCurrentDate().substring(0,4);
	String runY=DateUtil.getCurrentDate().substring(0,4);
	//先預設 後續由 USER 確認執行的年度
	private String [] listYear ;//年度清單
	private int mChoice=0;
	String [] arrayQyear;	
	String [] RarrayQyear;	
	
	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,10,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;

	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(DateUtil.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.formatDate(mMonth1) + "/" + Utilis.formatDate(mDay1);
		            	String endDay = "" + mYear2 + "/" + Utilis.formatDate(mMonth2) + "/" + Utilis.formatDate(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.formatDate(mMonth1) + "/" + Utilis.formatDate(mDay1);
			            	String endDay = "" + mYear2 + "/" + Utilis.formatDate(mMonth2) + "/" + Utilis.formatDate(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({ "unused", "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
					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:  //最右下 年度謄檔
				{
    				/**2021 思考將每一個年度紀錄 反排序
    				 *	@@@20151111
					每10000筆 約3Mb 計算	綜合判斷:
					如果現行資料庫>1500000筆 則建議執行年度謄檔
					包涵3年以上資料時,才需要執行年度謄檔
					每年>十月,執行年度謄檔時,是最佳時機
					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;
					建議年度謄檔 每月檢查一次,提醒一次為限
					@@程序安排
					先計算出現行資料庫共有幾個年度,並顯示SPINNER
					讓用戶選擇年度謄檔做到哪一個年度,經確認後
					執行如下的年度謄檔
    				 */
				//目前的年度
					String thisYear=DateUtil.getCurrentDate().substring(0,4)+"-01-01";
				String isQyear=Utilis.getIni(MainTainManage.this, "SYS", "DEPT_SEND", 7);
				if (isQyear.equalsIgnoreCase("1")){ //執行年度謄檔
					
						SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
							
							Cursor cs = db.rawQuery("select distinct substr(FORMDATE,1,4) from qhead where CREATEDATETIME not like '%1231235959' and FORMDATE<'"+thisYear+"' order by FORMDATE",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;
									db.close();
								}
							}
						if (QY.toString().indexOf(";")>0){//表示有2個年度以上 才需要執行年度謄檔
								Toast.makeText(MainTainManage.this, "..."+QY.toString(), Toast.LENGTH_LONG).show();
								listYear =QY.toString().split(";"); 
								//製作一個對話框 詢問年度謄檔到哪一個年度 DoYear
								//且經過確認,再進行年度謄檔
								mChoice=0;
								AlertDialog.Builder sortDlg = new AlertDialog.Builder(MainTainManage.this);
						        sortDlg.setTitle("請選擇將要轉入歷史檔的年度")
						        .setSingleChoiceItems(listYear, 0, new DialogInterface.OnClickListener() {
						            public void onClick(DialogInterface dialog, int item) {
						            	mChoice = item;
						            	
				            	/*檔案資料夾清單顯示,選取確認後
				            	 * 直接開啟CSV檔,並一一檢查明細貨號是否OK
								 * 如果有任何異狀,將列示錯誤的清冊
				            	 *不OK的將顯示在LIST中,提示以外,能否存入另一個檔案
								 * 再度確認後,開出所有條件 
								 * 單別 門市出貨單 門市轉貨單,銷貨單,廠商進貨單
								 * 以RadioGroupBox 唯一選項,決定欄位一欄位二名稱
								 *門市轉貨單 需要 [轉出門市](參數預設) [轉入門市](參數預設)
								 *門市出貨單(一定是[總倉出貨]出給 [門市](參數預設)  
								 *廠商進貨單 一定是[總倉進貨] [廠商](參數預設)
								 *銷貨單 選擇[銷貨門市](參數預設) 選擇[會員](參數預設)
								 *折數 帶入 (可以編輯)
								 *經手人(參數預設) 
				            	 * 
				            	 */
						            }
						        }) 
						        .setPositiveButton(getString(R.string.str_ok), new DialogInterface.OnClickListener() {
						            public void onClick(DialogInterface dialog, int which) { //確認

						      	      myDialog = ProgressDialog.show(MainTainManage.this,"資料處理中.....","請稍候.............",true);			  
						    		  
						    	      new Thread()
						    	      { 
						    	        public void run()
						    	        { 
						    	          try
						    	          {
						            	
										SQLiteDatabase db = Utilis.getDB(MainTainManage.this);
										//若選[2]2019
						            	runY=listYear[mChoice];
						            	firstY=listYear[0];
						            	String allY=listYear[0];
						            	for (int i=1;i<=mChoice;i++){
						            		allY+=";"+listYear[i];
						            	}
						            	arrayQyear=allY.split(";");
						            	
						            	allY=listYear[mChoice];
						            	for (int i=mChoice-1;i>=0;i--){
						            		allY+=";"+listYear[i];
						            	}
						            	RarrayQyear=allY.split(";");
						            	//排序 [0]2017;[1]2018;[2]2019
						            	//反排序 [0]2019;[1]2018;[2]2017
						            	//當選了 [2]2019 runY
										String SQL="";
										for (int x=0;x<RarrayQyear.length;x++){
											//Toast.makeText(MainTainManage.this, "第一波__"+RarrayQyear[x], Toast.LENGTH_LONG).show();
											
											if (doesTableExist(db, "qhead_"+RarrayQyear[x])==false){
												SQL="create table IF NOT EXISTS qhead_"+RarrayQyear[x]+" as select * from qhead where FORMDATE>='"+RarrayQyear[x]+"-01-01'"+" and FORMDATE<='"+RarrayQyear[x]+"-12-31'";
												//將各年度資料一一轉入 應有的年度QHEAD資料表
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
											}else{//已存在資料庫 只能insert into qhead
			//有可能 系統更新後 資料結構改變 引發後續回寫時,對應不到相對的欄位而出錯
			//應先檢查原先的欄位 與 目前的 欄位 數是否一樣,萬一不一樣(只會增加欄位)
			//不足欄位應先補足後,並將新增的欄位初始化才可以進一步插入新的資料
												db.beginTransaction();
												SQL="insert into qhead_"+RarrayQyear[x]+"  (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',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" +
														" from qhead where  (FORMDATE>='"+RarrayQyear[x]+"-01-01'"+" and FORMDATE<='"+RarrayQyear[x]+"-12-31') and ((ISCONFIRM='Y' and ISCHECK='N') or " +
														"(ISCONFIRM='N' and ISCHECK='Y')) and CREATEDATETIME not like '%1231235959'";
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
											}
						                    SystemClock.sleep(3000);

											if (doesTableExist(db, "qitems_"+RarrayQyear[x])==false){
												SQL="create table IF NOT EXISTS qitems_"+RarrayQyear[x]+" as select * from qitems where FORMDATE>='"+RarrayQyear[x]+"-01-01'"+" and FORMDATE<='"+RarrayQyear[x]+"-12-31'";
												//將各年度資料一一轉入 應有的年度QITEMS資料表
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
											}else{//已存在資料庫 只能insert into qitems
												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,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 from qitems"
														+ " WHERE FORMDATE>='"+RarrayQyear[x]+"-01-01'"+" and FORMDATE<='"+RarrayQyear[x]+"-12-31'" +
														 " and ((ISCONFIRM='Y' and ISCHECK='N') or (ISCONFIRM='N' and ISCHECK='Y')) and CREATEDATETIME<>'191231235959'";
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
											}
						                    SystemClock.sleep(3000);

										}
										//以上完成各年度的交易檔 歸檔功能
										//將填入跨年的前期末轉本期初
											//Toast.makeText(MainTainManage.this, "第二波__"+runY, Toast.LENGTH_LONG).show();
											
											SQL="delete from qhead where ((FORMDATE>='"+firstY+"-01-01') and (FORMDATE<='"+runY+"-12-31'))";
											try{
												db.beginTransaction();
												db.execSQL(SQL);
												db.setTransactionSuccessful();
											}catch(Exception e){
											}finally{
												db.endTransaction();
											}
						                    SystemClock.sleep(3000);
											
											SQL="delete from qitems where  ((FORMDATE>='"+firstY+"-01-01') and (FORMDATE<='"+runY+"-12-31'))";
											try{
												db.beginTransaction();
												db.execSQL(SQL);
												db.setTransactionSuccessful();
											}catch(Exception e){
											}finally{
												db.endTransaction();
											}
						                    SystemClock.sleep(3000);

										
											for (int x=0;x<mChoice;x++){
												
												SQL="insert into qhead_"+arrayQyear[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'" +
														",'"+arrayQyear[x]+"-12-31','"+arrayQyear[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_"+arrayQyear[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();

												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
							                    SystemClock.sleep(2000);
	
												SQL="insert into qitems_"+arrayQyear[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','"+arrayQyear[x]+"-12-31','"+arrayQyear[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_"+arrayQyear[x]+" where ((ISCONFIRM='Y') or (ISCONFIRM='N' and ISCHECK='Y')) " +
														" group by  QKIND,ISCONFIRM,ISCHECK,USER,USERNAME,TRADETYPE,DEPTNO,GOODSNO,GOODSNOS,SOURCENO,UNIT1" +
														",UNIT2,UNITPRICE,SUPPLY,GOODSTYPE,SEASON,COUNTRY,YEAR";
												
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
							                    SystemClock.sleep(3000);
												//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'" +
													",'"+runY+"-12-31','"+runY.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_"+runY+" 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();

											//try{
											//	db.beginTransaction();
												db.execSQL(SQL);
											//	db.setTransactionSuccessful();
											//}catch(Exception e){
											//}finally{
											//	db.endTransaction();
											//}
						                    SystemClock.sleep(3000);
											
											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','"+runY+"-12-31','"+runY.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_"+runY+" 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 ";
											
											//try{
											//	db.beginTransaction();
												db.execSQL(SQL);
											//	db.setTransactionSuccessful();
											//}catch(Exception e){
											//}finally{
											//	db.endTransaction();
											//}
						                    SystemClock.sleep(3000);
											//Toast.makeText(MainTainManage.this, "第六波__"+SQL, Toast.LENGTH_LONG).show();

											
											
										//製作QDETAIL_HIS
											
											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";
												//將現行的qdetail 存入_his 只限首次
												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
												SystemClock.sleep(3000);
											}else{	

												//Toast.makeText(MainTainManage.this, "qdetail_his 維護中>....", Toast.LENGTH_LONG).show();
												//忽略 MID 自動產生流水號問題
												SQL="INSERT INTO qdetail_his	(TR,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 TR,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 FROM qdetail where qdetail.GOODSNO not in (select distinct GOODSNO from qdetail_his)";

												try{
													db.beginTransaction();
													db.execSQL(SQL);
													db.setTransactionSuccessful();
												}catch(Exception e){
												}finally{
													db.endTransaction();
												}
												SystemClock.sleep(3000);
												
											}
											//Toast.makeText(MainTainManage.this, "qdetail 維護中清除較早的資料>....", Toast.LENGTH_LONG).show();
											SQL="delete from qdetail where (qdetail.CREATEDATETIME<'"+runY+"-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+")))";
											try{
												db.beginTransaction();
												db.execSQL(SQL);
												db.setTransactionSuccessful();
											}catch(Exception e){
											}finally{
												db.endTransaction();
											}
											SystemClock.sleep(3000);
											
										db.close();
						    	          }
						    	          catch (Exception e)
						    	          {
						    	            e.printStackTrace();
						    	          }
						    	          finally
						    	          {
						    	            // 卸載所建立的myDialog物件。
						    	            myDialog.dismiss();
						    	          }
						    	        }
						    	      }.start(); 
										
						            }
						          })
						        .setNegativeButton("取消", new DialogInterface.OnClickListener() {
						            public void onClick(DialogInterface dialog, int which) { //取消

						            }
						          }).show();
						}
					}
					break;
				}
				
				default:
				break;
			}
		}
	}
}
