package com.cwbuyer.lib;

import java.io.File;
import java.util.LinkedList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.widget.Toast;

import com.cwbuyer.format.DefaultData;
import com.cwbuyer.format.ItemData;
import com.cwbuyer.format.PItemData;
import com.cwbuyer.main.ManageAccount.AccountData;

public class DBUtil {
	
	public static class Default{
		// 取得預設參數
		public static DefaultData getDefaultData(Context cnt){
			DefaultData data = null;
	    	SQLiteDatabase db = Utilis.getDB(cnt);
	    	if(db != null){
				Cursor cursor = db.rawQuery("Select B.Name,B.PIC,A.* from qc_default A inner join qc_country B on A.COUNTRY=B._ID", null);
				if(cursor != null){
					try{
						if(cursor.getCount() > 0){
							cursor.moveToFirst();
							data = new DefaultData();
							data.nCountry = cursor.getInt(cursor.getColumnIndex("COUNTRY"));
							data.country = cursor.getString(cursor.getColumnIndex("NAME"));
							data.strCountryPic = cursor.getString(cursor.getColumnIndex("PIC"));
							data.dBillRate = cursor.getDouble(cursor.getColumnIndex("BILLRATE"));
							data.batch = cursor.getString(cursor.getColumnIndex("BATCH"));
							data.factno = cursor.getString(cursor.getColumnIndex("FACTNO"));
							data.nItemState = cursor.getInt(cursor.getColumnIndex("STATE"));
							data.nSourceNumber = cursor.getInt(cursor.getColumnIndex("SOURCENO"));
							data.nItemProduct = cursor.getInt(cursor.getColumnIndex("PRODUCT"));
							data.nItemFirst = cursor.getInt(cursor.getColumnIndex("ITEMFIRST"));
							data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
							data.nYear = cursor.getInt(cursor.getColumnIndex("YEAR"));
							data.employ = cursor.getString(cursor.getColumnIndex("EMPLOY"));
							data.nAccountId = cursor.getInt(cursor.getColumnIndex("ACCOUNT"));
							data.nHid = cursor.getString(cursor.getColumnIndex("HID"));
						}
					}catch(Exception e){
						e.printStackTrace();
					}finally{
						cursor.close();
					}
				}
				db.close();
	    	}
	    	return data;
	    }
	}
	
	public static class CFact{
		public static boolean isQFactExist(Context cnt, String supply,String mobil,int iKind){
			SQLiteDatabase db = Utilis.getDB(cnt);
			boolean bExist = false;
			@SuppressWarnings("unused")
			int serch=0;
			String fMobil=""; 
			if(db != null){
				try{
					StringBuffer buf = new StringBuffer();
					if (iKind==10){
						buf.append("Select FACTNO,MOBIL from qfact where FACTNO='").append(supply).append("'");
					}else {
						buf.append("Select CUSTNO,CUSTNAME from qcust where CUSTNO='").append(supply).append("'");
					}
					Cursor cursor = db.rawQuery(buf.toString(), null);
					if(cursor != null && cursor.getCount()>0){
							bExist = true;
							cursor.moveToFirst();
							fMobil=cursor.getString(1);
					}
						cursor.close();
					if (iKind==10 && !mobil.equalsIgnoreCase(fMobil) && mobil.length()>0){
						db.execSQL("update qfact set MOBIL='"+mobil+"',STATE='1' where FACTNO='"+supply+"'");
					}
					
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					db.close();
				}
			}
			return bExist;
		}
		
		// FactNo, Country, nCountry
		public static boolean createNewFact(Context cnt, String factno, String mPS,int nCountry,int iKind){
			SQLiteDatabase db = Utilis.getDB(cnt);
	    	long result =0;
			if(db != null){
				try{
					ContentValues value = new ContentValues();
					if (iKind==10){//廠商進貨 採購新品()
						value.put("TR", (""+iKind).substring(0,1)+"0");
						value.put("FACTNO", factno);
						value.put("FACTNAME", factno);
						value.put("COUNTRY", nCountry);
				    	value.put("CREATEDATETIME", DateUtil.getSystemTime().substring(2,14));
				    	value.put("TRADETYPE", Utilis.getIni(cnt, "INIH", "USERTRADE", 1));
				    	value.put("STATE", "1");
						value.put("DISCOUNT",100);
						value.put("GPSLA",100);
						value.put("GPSLO",100);
						value.put("DEPTNO", Utilis.getIni(cnt,"SYS","DEPT",6));
				    	value.put("BANKNO", Utilis.getIni(cnt, "INIH", "BANK", 1));
				    	value.put("BANKNAME", Utilis.getIni(cnt,"SYS","DEPT",1)+Utilis.getIni(cnt,"SYS","DEPT",2));
				    	value.put("PIC", android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+"/cwbuyer/para/04.jpg");
						value.put("PS","");
						value.put("BIRTHDAY","");
						value.put("ADDRESS","");
						value.put("EMPID","1");
						value.put("MOBIL",mPS);
						value.put("EMAIL","");
						value.put("MSN","");
						value.put("GMAIL","");
				    	result = db.insert(TbName.QFACT, null, value);
					}else if (iKind==31){//在總倉或門市 門市銷貨
						Cursor cus=db.rawQuery("select * from qcust where CUSTNO='"+factno+"'",null);
			    		Toast.makeText(cnt, "該門市銷貨代號=="+factno, Toast.LENGTH_SHORT).show();
						
						if (cus.getCount()<=0){ 
							Cursor cursorC=db.rawQuery("select * from qcust where CUSTNO='"+factno.substring(1)+"' and TR='20'",null);
				    		Toast.makeText(cnt, "該門市是否找到_筆_"+factno.substring(1)+"___"+cursorC.getCount(), Toast.LENGTH_SHORT).show();
							if (cursorC.getCount()>0){
								cursorC.moveToFirst();
								int csCount=cursorC.getColumnCount();
								ContentValues valueC = new ContentValues();
								
								for(int ij=1;ij<csCount;ij++){ //將qfact存入
									valueC.put(cursorC.getColumnName(ij),cursorC.getString(ij)) ;
								}
								valueC.remove("TR");valueC.put(cursorC.getColumnName(1),"30");
								valueC.remove("CUSTNO");valueC.put(cursorC.getColumnName(2),factno);
								valueC.remove("DEPTNO");valueC.put("DEPTNO",factno.substring(1).trim());
								valueC.remove("STATE");valueC.put(cursorC.getColumnName(19),"1");
								result = db.insert(TbName.QCUST, null, valueC);
								cursorC.close();
							}
						}
						cus.close();
						
					}else{//總倉銷貨30,門市出貨20,門市轉貨21
						value.put("TR", (""+iKind).substring(0,1)+"0");
						value.put("CUSTNO", factno);
						value.put("CUSTNAME",factno);
						value.put("COUNTRY", nCountry);
				    	value.put("CREATEDATE", DateUtil.getCurrentDate());
				    	value.put("CREATETIME", DateUtil.getSystemTime().substring(2,14));
				    	value.put("TRADETYPE", Utilis.getIni(cnt, "INIH", "USERTRADE", (iKind/10)));
				    	value.put("STATE", "1");
						value.put("DISCOUNT",100);
						value.put("GPSLA",1);
						value.put("GPSLO",1);
						value.put("DEPTNO", mPS);
				    	value.put("BANKNO", Utilis.getIni(cnt, "INIH", "BANK", (iKind/10)));
				    	value.put("BANKNAME", Utilis.getIni(cnt,"SYS","DEPT",1)+Utilis.getIni(cnt,"SYS","DEPT",2));
				    	value.put("PIC", android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+"/cwbuyer/para/04.jpg");
						value.put("PS","");
						value.put("BIRTHDAY","");
						value.put("ADDRESS","");
						value.put("EMPID","A11");
						value.put("MOBIL","");
						value.put("EMAIL","");
						value.put("MSN","");
						value.put("GMAIL","");
						value.put("C1",1);
						value.put("C2",0);
						value.put("C3",0);
						value.put("M1",1);
						value.put("M2",0);
						value.put("M3",0);
						value.put("S1",1);
						value.put("S2",0);
						value.put("S3",0);
				    	
				    	result = db.insert(TbName.QCUST, null, value);
					}
			    	

			    	if(result > 0){
						if (iKind==10){
				    		Toast.makeText(cnt, "新增廠商資料成功", Toast.LENGTH_SHORT).show();
							//DBCloud.aa1_aa0_qfact(cnt);
						}else{
				    		Toast.makeText(cnt, "新增會員資料成功", Toast.LENGTH_SHORT).show();
							DBCloud.aa1_aa0_qcust(cnt);
						}
			    	}else{
			    		Toast.makeText(cnt, "新增廠商或客戶資料失敗", Toast.LENGTH_SHORT).show();
			    	}
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					db.close();
				}
			}
			return false;
		}
	}
	
	public static class CItem{
		
		/**
		 * 特別取得該 Mid 的 ItemData
		 * @param context
		 * @param mid
		 * @param isFull
		 * @return
		 */
		public static ItemData getData(Context context, int mid, boolean isFull){
			ItemData data = null;
			SQLiteDatabase db = Utilis.getDB(context);
			if(db != null){
				try{
					Cursor cursor = db.rawQuery("Select B.Name,A.* from qdetail A inner join qc_country B on A.COUNTRY=B._ID where A.MID="+mid, null);
					if(cursor != null){
						if(cursor.getCount() > 0){
							cursor.moveToFirst();
							data = new ItemData();
							for(int i = 0; i < cursor.getCount(); i ++){
								data = new ItemData();
								data.nTR = cursor.getInt(cursor.getColumnIndex("TR"));
								data.nMid = cursor.getInt(cursor.getColumnIndex("MID"));
								data.supply = cursor.getString(cursor.getColumnIndex("SUPPLY"));
								data.nCountry = cursor.getInt(cursor.getColumnIndex("COUNTRY"));
								data.country = cursor.getString(cursor.getColumnIndex("NAME"));
								data.sourceNo = cursor.getString(cursor.getColumnIndex("SOURCENO"));
								data.GoodsNo = cursor.getString(cursor.getColumnIndex("GOODSNO"));
								data.nGoodsType = cursor.getInt(cursor.getColumnIndex("GOODSTYPE"));
								data.goodsname = cursor.getString(cursor.getColumnIndex("GOODSNAME"));
								data.nUnit1 = cursor.getString(cursor.getColumnIndex("UNIT1"));
								data.nUnit2 = cursor.getString(cursor.getColumnIndex("UNIT2"));
								data.nUnit = cursor.getInt(cursor.getColumnIndex("UNIT"));
								data.batch = cursor.getString(cursor.getColumnIndex("BATCH"));
								data.dBill = cursor.getDouble(cursor.getColumnIndex("BILL"));
								data.dBillRate = cursor.getDouble(cursor.getColumnIndex("BILLRATE"));
								data.dAddCost = cursor.getDouble(cursor.getColumnIndex("ADDCOST"));
								data.dP0 = cursor.getDouble(cursor.getColumnIndex("P0"));
								data.dP1 = cursor.getDouble(cursor.getColumnIndex("P1"));
								data.dP2 = cursor.getDouble(cursor.getColumnIndex("P2"));
								data.dP3 = cursor.getDouble(cursor.getColumnIndex("P3"));
								data.dP4 = cursor.getDouble(cursor.getColumnIndex("P4"));
								data.dP5 = cursor.getDouble(cursor.getColumnIndex("P5"));
								data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
								data.mPS = cursor.getString(cursor.getColumnIndex("PS"));
								data.time = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
								data.nState = cursor.getInt(cursor.getColumnIndex("STATE"));
								data.pic1 = cursor.getString(cursor.getColumnIndex("PIC"));
								data.pic2 = cursor.getString(cursor.getColumnIndex("PICTURE"));
								data.nQualityID = cursor.getInt(cursor.getColumnIndex("QUALITY_ID"));
								data.nWashID = cursor.getInt(cursor.getColumnIndex("WASH_ID"));
								data.nTotalCount = cursor.getInt(cursor.getColumnIndex("IMPO"));
								data.nAccountId = cursor.getInt(cursor.getColumnIndex("ACCOUNT"));
								if(isFull == true){
									data.mColors = cursor.getString(cursor.getColumnIndex("COLOR"));
									data.mColorsNo = cursor.getString(cursor.getColumnIndex("COLORNO"));
									data.mCountList = cursor.getString(cursor.getColumnIndex("INLIST"));
									data.mSizes = cursor.getString(cursor.getColumnIndex("SIZE"));
								}
							}
						}
					}
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					db.close();
				}
			}
			return data;
		}
		
		// 標頭檔使用 table_qdetail
		public static ItemData parseData(Cursor cursor, boolean isFull){
			ItemData data = new ItemData();
			try{
				data.nTR = cursor.getInt(cursor.getColumnIndex("TR"));
				data.nMid = cursor.getInt(cursor.getColumnIndex("MID"));
				data.supply = cursor.getString(cursor.getColumnIndex("SUPPLY"));
				data.nCountry = cursor.getInt(cursor.getColumnIndex("COUNTRY"));
				data.country = cursor.getString(cursor.getColumnIndex("PS"));
				data.sourceNo = cursor.getString(cursor.getColumnIndex("SOURCENO"));
				data.GoodsNo = cursor.getString(cursor.getColumnIndex("GOODSNO"));
				data.nGoodsType = cursor.getInt(cursor.getColumnIndex("GOODSTYPE"));
				data.goodsname = cursor.getString(cursor.getColumnIndex("GOODSNAME"));
				data.nUnit1 = cursor.getString(cursor.getColumnIndex("UNIT1"));
				data.nUnit2 = cursor.getString(cursor.getColumnIndex("UNIT2"));
				data.nUnit = cursor.getInt(cursor.getColumnIndex("UNIT"));
				data.batch = cursor.getString(cursor.getColumnIndex("BATCH"));
				data.dBill = cursor.getDouble(cursor.getColumnIndex("BILL"));
				data.dBillRate = cursor.getDouble(cursor.getColumnIndex("BILLRATE"));
				data.dAddCost = cursor.getDouble(cursor.getColumnIndex("ADDCOST"));
				data.dP0 = cursor.getDouble(cursor.getColumnIndex("P0"));
				data.dP1 = cursor.getDouble(cursor.getColumnIndex("P1"));
				data.dP2 = cursor.getDouble(cursor.getColumnIndex("P2"));
				data.dP3 = cursor.getDouble(cursor.getColumnIndex("P3"));
				data.dP4 = cursor.getDouble(cursor.getColumnIndex("P4"));
				data.dP5 = cursor.getDouble(cursor.getColumnIndex("P5"));
				data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
				data.mPS = cursor.getString(cursor.getColumnIndex("PS"));
				data.time = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
				data.nState = cursor.getInt(cursor.getColumnIndex("STATE"));
				data.pic1 = cursor.getString(cursor.getColumnIndex("PIC"));
				data.pic2 = cursor.getString(cursor.getColumnIndex("PICTURE"));
				data.nQualityID = cursor.getInt(cursor.getColumnIndex("QUALITY_ID"));
				data.nWashID = cursor.getInt(cursor.getColumnIndex("WASH_ID"));
				data.nTotalCount = cursor.getInt(cursor.getColumnIndex("IMPO"));
				data.nAccountId = cursor.getInt(cursor.getColumnIndex("ACCOUNT"));
				if(isFull == true){
					data.mColors = cursor.getString(cursor.getColumnIndex("COLOR"));
					data.mColorsNo = cursor.getString(cursor.getColumnIndex("COLORNO"));
					data.mCountList = cursor.getString(cursor.getColumnIndex("INLIST"));
					data.mSizes = cursor.getString(cursor.getColumnIndex("SIZE"));
				}
			}catch(Exception e){
				e.printStackTrace();
			}
			return data;
		}
		
		// qdetail use
		public static ItemData parseDetailData(Cursor cursor){
			ItemData data = new ItemData();
			try{
				data.nTR = cursor.getInt(cursor.getColumnIndex("TR"));
				data.nMid = cursor.getInt(cursor.getColumnIndex("MID"));
				data.supply = cursor.getString(cursor.getColumnIndex("SUPPLY"));
				data.nCountry = cursor.getInt(cursor.getColumnIndex("COUNTRY"));
				data.country = cursor.getString(cursor.getColumnIndex("PS"));
				data.sourceNo = cursor.getString(cursor.getColumnIndex("SOURCENO"));
				data.GoodsNo = cursor.getString(cursor.getColumnIndex("GOODSNO"));
				data.nGoodsType = cursor.getInt(cursor.getColumnIndex("GOODSTYPE"));
				data.goodsname = cursor.getString(cursor.getColumnIndex("GOODSNAME"));
				data.nUnit1 = cursor.getString(cursor.getColumnIndex("UNIT1"));
				data.nUnit2 = cursor.getString(cursor.getColumnIndex("UNIT2"));
				data.nUnit = cursor.getInt(cursor.getColumnIndex("UNIT"));
				data.batch = cursor.getString(cursor.getColumnIndex("BATCH"));
				data.dBill = cursor.getDouble(cursor.getColumnIndex("BILL"));
				data.dBillRate = cursor.getDouble(cursor.getColumnIndex("BILLRATE"));
				data.dAddCost = cursor.getDouble(cursor.getColumnIndex("ADDCOST"));
				data.dP0 = cursor.getDouble(cursor.getColumnIndex("P0"));
				data.dP1 = cursor.getDouble(cursor.getColumnIndex("P1"));
				data.dP2 = cursor.getDouble(cursor.getColumnIndex("P2"));
				data.dP3 = cursor.getDouble(cursor.getColumnIndex("P3"));
				data.dP4 = cursor.getDouble(cursor.getColumnIndex("P4"));
				data.dP5 = cursor.getDouble(cursor.getColumnIndex("P5"));
				data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
				data.mPS = cursor.getString(cursor.getColumnIndex("PS"));
				data.time = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
				data.nState = cursor.getInt(cursor.getColumnIndex("STATE"));
				data.pic1 = cursor.getString(cursor.getColumnIndex("PIC"));
				data.pic2 = cursor.getString(cursor.getColumnIndex("PICTURE"));
				data.nQualityID = cursor.getInt(cursor.getColumnIndex("QUALITY_ID"));
				data.nWashID = cursor.getInt(cursor.getColumnIndex("WASH_ID"));
				data.nTotalCount = cursor.getInt(cursor.getColumnIndex("IMPO"));
				data.nAccountId = cursor.getInt(cursor.getColumnIndex("ACCOUNT"));
				data.mColors = cursor.getString(cursor.getColumnIndex("COLOR"));
				data.mColorsNo = cursor.getString(cursor.getColumnIndex("COLORNO"));
				data.mCountList = cursor.getString(cursor.getColumnIndex("INLIST"));
				data.mSizes = cursor.getString(cursor.getColumnIndex("SIZE"));
			}catch(Exception e){
				e.printStackTrace();
			}
			return data;
		}
	}

	public static class PItem{
		
		/**
		 * 特別取得該 FORMNO 的 ItemData
		 */
		public static PItemData getData(Context context, String mformno, String mKind,boolean isHead){
			PItemData data = null;
			//isFull==true 單頭  false 單身    
			SQLiteDatabase db = Utilis.getDB(context);
			if(db != null){
				try{
					if(isHead == true){
						Cursor cursor = db.rawQuery("Select * from qhead where FORMNO='"+mformno+"' and QKIND LIKE '"+mKind.substring(0,1)+"%'" , null);
						if(cursor != null){
							if(cursor.getCount() > 0){
								cursor.moveToFirst();
								data = new PItemData();
								for(int i = 0; i < cursor.getCount(); i ++){
									data.mTR = cursor.getString(cursor.getColumnIndex("TR"));
									data.nID = cursor.getInt(cursor.getColumnIndex("ID"));
									data.mFno = cursor.getString(cursor.getColumnIndex("FORMNO"));
									//@@@
									//data.mKind = cursor.getString(cursor.getColumnIndex("QKIND"));
									data.mFormdate = cursor.getString(cursor.getColumnIndex("FORMDATE"));
									data.mTime = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
									data.mIsconfirm = cursor.getString(cursor.getColumnIndex("ISCONFIRM"));
									data.mIscheck = cursor.getString(cursor.getColumnIndex("ISCHECK"));
									data.mEmpid = cursor.getString(cursor.getColumnIndex("EMPID"));
									data.mEmpname = cursor.getString(cursor.getColumnIndex("EMPNAME"));
									data.mUser = cursor.getString(cursor.getColumnIndex("USER"));
									data.mUsername = cursor.getString(cursor.getColumnIndex("USERNAME"));
									data.nTrade = cursor.getInt(cursor.getColumnIndex("TRADETYPE"));
									data.mDeptno = cursor.getString(cursor.getColumnIndex("DEPTNO"));
									data.mOrddate = cursor.getString(cursor.getColumnIndex("ORDDATE"));
									data.mFinedate = cursor.getString(cursor.getColumnIndex("FINEDATE"));
									//以上16欄位為共用 以下為13欄位為HEAD專用
									data.mIslist = cursor.getString(cursor.getColumnIndex("ISLIST"));
									data.mHandno=cursor.getString(cursor.getColumnIndex("HANDNO"));
									data.mBankno = cursor.getString(cursor.getColumnIndex("BANKNO"));
									data.mBankname = cursor.getString(cursor.getColumnIndex("BANKNAME"));
									data.dOrdcash = cursor.getDouble(cursor.getColumnIndex("ORDCASH"));
									data.dOrdcard = cursor.getDouble(cursor.getColumnIndex("ORDCARD"));
									data.dCash = cursor.getDouble(cursor.getColumnIndex("CASH"));
									data.dCard = cursor.getDouble(cursor.getColumnIndex("CARD"));
									data.dAtot = cursor.getDouble(cursor.getColumnIndex("ATOT"));
									data.dAsum = cursor.getDouble(cursor.getColumnIndex("ASUM"));
									data.dBsum = cursor.getDouble(cursor.getColumnIndex("BSUM"));
									data.dCosts = cursor.getDouble(cursor.getColumnIndex("COSTS"));
									data.dTuresum = cursor.getDouble(cursor.getColumnIndex("TURESUM"));
									data.dDismoney = cursor.getDouble(cursor.getColumnIndex("DISMONEY"));
									
									data.dAtm =  cursor.getDouble(cursor.getColumnIndex("ATM"));
									data.mAtmdate = cursor.getString(cursor.getColumnIndex("ATMDATE"));
									data.dTrcash =  cursor.getDouble(cursor.getColumnIndex("TRCASH"));
									data.mhPs = cursor.getString(cursor.getColumnIndex("PS"));
									data.mhPs1 = cursor.getString(cursor.getColumnIndex("PS1"));
									data.mhPs2 = cursor.getString(cursor.getColumnIndex("PS2"));
								}
							}
						}
						
					} else {
						Cursor cursor = db.rawQuery("Select * from qitems where FORMNO='"+mformno+"'", null);
						if(cursor != null){
							if(cursor.getCount() > 0){
								cursor.moveToFirst();
								data = new PItemData();
									data.mTR = cursor.getString(cursor.getColumnIndex("TR"));
									data.nID = cursor.getInt(cursor.getColumnIndex("ID"));
									data.mFno = cursor.getString(cursor.getColumnIndex("FORMNO"));
									//@@@
									//data.mKind = cursor.getString(cursor.getColumnIndex("QKIND"));
									data.mFormdate = cursor.getString(cursor.getColumnIndex("FORMDATE"));
									data.mTime = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
									data.mIsconfirm = cursor.getString(cursor.getColumnIndex("ISCONFIRM"));
									data.mIscheck = cursor.getString(cursor.getColumnIndex("ISCHECK"));
									data.mEmpid = cursor.getString(cursor.getColumnIndex("EMPID"));
									data.mEmpname = cursor.getString(cursor.getColumnIndex("EMPNAME"));
									data.mUser = cursor.getString(cursor.getColumnIndex("USER"));
									data.mUsername = cursor.getString(cursor.getColumnIndex("USERNAME"));
									data.nTrade = cursor.getInt(cursor.getColumnIndex("TRADETYPE"));
									data.mDeptno = cursor.getString(cursor.getColumnIndex("DEPTNO"));
									data.mOrddate = cursor.getString(cursor.getColumnIndex("ORDDATE"));
									data.mFinedate = cursor.getString(cursor.getColumnIndex("FINEDATE"));
									//以上16欄位為共用 以下為欄位為ITEMS專用									
									data.mSupply = cursor.getString(cursor.getColumnIndex("SUPPLY"));
									data.mSourceNo = cursor.getString(cursor.getColumnIndex("SOURCENO"));
									data.mGoodsNo = cursor.getString(cursor.getColumnIndex("GOODSNO"));									
									data.mGoodsNos = cursor.getString(cursor.getColumnIndex("GOODSNOS"));
									data.mGoodsType = cursor.getString(cursor.getColumnIndex("GOODSTYPE"));
									data.mGoodsName = cursor.getString(cursor.getColumnIndex("GOODSNAME"));
									data.dUnit = cursor.getDouble(cursor.getColumnIndex("UNIT"));
									data.dUnit10=cursor.getDouble(cursor.getColumnIndex("UNIT10"));
									data.dUnit20=cursor.getDouble(cursor.getColumnIndex("UNIT20"));
									data.dUnit30=cursor.getDouble(cursor.getColumnIndex("UNIT30"));
									data.dUnit40=cursor.getDouble(cursor.getColumnIndex("UNIT40"));
									data.dUnitprice=cursor.getDouble(cursor.getColumnIndex("UNITPRICE"));
									data.dAcost=cursor.getDouble(cursor.getColumnIndex("ACOST"));
									data.dDiscount=cursor.getDouble(cursor.getColumnIndex("DISCOUNT"));							
									data.dRateprice=cursor.getDouble(cursor.getColumnIndex("RATEPRICE"));
									data.dRealsum=cursor.getDouble(cursor.getColumnIndex("REALSUM"));
									data.dsubprice=cursor.getDouble(cursor.getColumnIndex("SUBPRICE"));
									data.dP0 = cursor.getDouble(cursor.getColumnIndex("P0"));
									data.dP1 = cursor.getDouble(cursor.getColumnIndex("P1"));
									data.dP2 = cursor.getDouble(cursor.getColumnIndex("P2"));
									data.dP3 = cursor.getDouble(cursor.getColumnIndex("P3"));
									data.dP4 = cursor.getDouble(cursor.getColumnIndex("P4"));
									data.dP5 = cursor.getDouble(cursor.getColumnIndex("P5"));
									
									data.mColors=cursor.getString(cursor.getColumnIndex("COLORS"));
									data.mSizes=cursor.getString(cursor.getColumnIndex("SIZES"));							
									data.dRate=cursor.getDouble(cursor.getColumnIndex("RATE"));
									data.mPic = cursor.getString(cursor.getColumnIndex("PIC"));
									data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
									data.mUnit1 = cursor.getString(cursor.getColumnIndex("UNIT1"));
									data.mUnit2 = cursor.getString(cursor.getColumnIndex("UNIT2"));
									data.mBatch = cursor.getString(cursor.getColumnIndex("BATCH"));
									data.mCountry = cursor.getString(cursor.getColumnIndex("COUNTRY"));							
									data.mYear = cursor.getString(cursor.getColumnIndex("YEAR"));
									data.mPS = cursor.getString(cursor.getColumnIndex("PS"));
							}
						}
					}	
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					db.close();
				}
			}
			return data;
		}

		
		
		// 目前QAPOS Plist.add()使用中 
		public static PItemData parseData(Cursor cursor){
			PItemData data = new PItemData();
			try{
				data.mUser = cursor.getString(cursor.getColumnIndex("USER"));
				data.mSupply = cursor.getString(cursor.getColumnIndex("SUPPLY"));
				data.mSourceNo = cursor.getString(cursor.getColumnIndex("SOURCENO"));
				data.mGoodsNo = cursor.getString(cursor.getColumnIndex("GOODSNO"));
				data.mGoodsNos = cursor.getString(cursor.getColumnIndex("GOODSNOS"));				
				data.mGoodsType = cursor.getString(cursor.getColumnIndex("GOODSTYPE"));
				data.mGoodsName = cursor.getString(cursor.getColumnIndex("GOODSNAME"));
				data.mTime = cursor.getString(cursor.getColumnIndex("CREATEDATETIME"));
				data.nTrade = cursor.getInt(cursor.getColumnIndex("TRADETYPE"));
				data.dUnit = cursor.getDouble(cursor.getColumnIndex("UNIT"));
				data.dUnit10=cursor.getDouble(cursor.getColumnIndex("UNIT10"));
				data.dUnit20=cursor.getDouble(cursor.getColumnIndex("UNIT20"));
				data.dUnit30=cursor.getDouble(cursor.getColumnIndex("UNIT30"));
				data.dUnit40=cursor.getDouble(cursor.getColumnIndex("UNIT40"));
				data.dUnitprice=cursor.getDouble(cursor.getColumnIndex("UNITPRICE"));
				data.dAcost=cursor.getDouble(cursor.getColumnIndex("ACOST"));
				data.dDiscount=cursor.getDouble(cursor.getColumnIndex("DISCOUNT"));							
				data.dRateprice=cursor.getDouble(cursor.getColumnIndex("RATEPRICE"));
				data.dRealsum=cursor.getDouble(cursor.getColumnIndex("REALSUM"));
				data.dsubprice=cursor.getDouble(cursor.getColumnIndex("SUBPRICE"));
				data.dP0 = cursor.getDouble(cursor.getColumnIndex("P0"));
				data.dP1 = cursor.getDouble(cursor.getColumnIndex("P1"));
				data.dP2 = cursor.getDouble(cursor.getColumnIndex("P2"));
				data.dP3 = cursor.getDouble(cursor.getColumnIndex("P3"));
				data.dP4 = cursor.getDouble(cursor.getColumnIndex("P4"));
				data.dP5 = cursor.getDouble(cursor.getColumnIndex("P5"));
				data.mOrddate= cursor.getString(cursor.getColumnIndex("ORDDATE"));
				data.mFinedate= cursor.getString(cursor.getColumnIndex("FINEDATE"));
				data.mColor=cursor.getString(cursor.getColumnIndex("COLOR"));
				data.mSize=cursor.getString(cursor.getColumnIndex("SIZE"));							
				data.dRate=cursor.getDouble(cursor.getColumnIndex("RATE"));
				data.mPic = cursor.getString(cursor.getColumnIndex("PIC"));
				data.nSeason = cursor.getInt(cursor.getColumnIndex("SEASON"));
				data.mUnit1 = cursor.getString(cursor.getColumnIndex("UNIT1"));
				data.mUnit2 = cursor.getString(cursor.getColumnIndex("UNIT2"));
				data.mBatch = cursor.getString(cursor.getColumnIndex("BATCH"));
				data.mCountry = cursor.getString(cursor.getColumnIndex("COUNTRY"));							
				data.mYear = cursor.getString(cursor.getColumnIndex("YEAR"));
				data.mPS = cursor.getString(cursor.getColumnIndex("PS"));
				
				
			}catch(Exception e){
				e.printStackTrace();
			}
			return data;
		}
		
	}
	
	
	public static class CAccount{
		public static LinkedList<AccountData> getAccountData(Context context){
			LinkedList<AccountData> list = new LinkedList<AccountData>();
			SQLiteDatabase db = Utilis.getDB(context);
			if(db != null){
				try{
					Cursor cursor = db.rawQuery("select * from " + TbName.ACCOUNT + " order by accsort DESC", null);
					if(cursor != null){
						try{
							if(cursor.getCount() > 0){
								cursor.moveToFirst();
								for(int i = 0; i < cursor.getCount(); i++){
									AccountData data = new AccountData();
									data.nID =  cursor.getInt(cursor.getColumnIndex("_id"));
									data.strText =  cursor.getString(cursor.getColumnIndex("acctext"));
									data.strRate =  cursor.getString(cursor.getColumnIndex("accrate"));
									data.strNote =  cursor.getString(cursor.getColumnIndex("accnote"));
									data.nMoney =  cursor.getInt(cursor.getColumnIndex("accmoney"));
									data.nSort =  cursor.getInt(cursor.getColumnIndex("accsort"));
									data.nPic =  cursor.getInt(cursor.getColumnIndex("accpic"));
									data.nInit =  cursor.getInt(cursor.getColumnIndex("accinit"));
									data.strShowInfo = data.strText + " $" + data.nMoney;
									list.add(data);
									cursor.moveToNext();
								}
							}
						}catch(Exception e){
							e.printStackTrace();
						}finally{
							cursor.close();
						}
					}
				}catch(Exception e){
					e.printStackTrace();
				}finally{
					db.close();
				}
			}
			return list;
		}
	}
	
	
	
	
	public static boolean isDataExistQDetail(SQLiteDatabase db, String param, String value, boolean bIsString){
		boolean bExist = false;
		try{
			StringBuffer buf = new StringBuffer();
			buf.append("Select * from qdetail where ").append(param);
			if(bIsString == true){
				buf.append(" like '%").append(value).append("%'");
			}else{
				buf.append("=").append(value);
			}
			buf.append(" limit 1");
			Cursor cursor = db.rawQuery(buf.toString(), null);
			if(cursor != null){
				if(cursor.getCount() > 0){
					bExist = true;
				}
				cursor.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return bExist;
	}
	
	public static boolean isDataExistQFact(SQLiteDatabase db, String param, String value, boolean bIsString){
		boolean bExist = false;
		try{
			StringBuffer buf = new StringBuffer();
			buf.append("Select * from qfact where ").append(param);
			if(bIsString == true){
				buf.append(" like '%").append(value).append("%'");
			}else{
				buf.append("=").append(value);
			}
			buf.append(" limit 1");
			Cursor cursor = db.rawQuery(buf.toString(), null);
			if(cursor != null){
				if(cursor.getCount() > 0){
					bExist = true;
				}
				cursor.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return bExist;
	}
	
	@SuppressWarnings("unused")
	private Context mCnt;
	
	public DBUtil(Context cnt){
		mCnt = cnt;
	}
	
	public static Cursor GVSQL(Context context,SQLiteDatabase db,String aInput,int Source,int mode,int nState) {
		
		String serch=aInput;if (aInput.substring(0, 1).equalsIgnoreCase("*")) serch=aInput.substring(1);
		String SQLGVPQdetail="select distinct GOODSNO,PIC,'0' from qdetail";
		String SQLGVQdetail="select MID,PIC,GOODSNO,BATCH from qdetail";
		String mOrderByQdetail=" order by CREATEDATETIME DESC ,GOODSNO DESC LIMIT 300";
		String ASK="";
		String mOrderBy="";
		
		Cursor cs=null;
		StringBuffer mySqlBuf =new StringBuffer();

		//GVP
		if (mode==0){
			ASK=SQLGVPQdetail;
			mOrderBy=mOrderByQdetail;
		}
		else{ //GV 0.display 1.displaypart0 
			ASK=SQLGVQdetail;
			mOrderBy=mOrderByQdetail;
		}
		mySqlBuf.append(ASK);
		if(aInput.indexOf("-")>0){
			serch=aInput.substring(0,aInput.indexOf("-")); 
			mySqlBuf.append(" where GOODSNO ='" + serch+"'");
		}else if (Source==1) {//優先查詢 來源編號
				if (aInput.substring(0, 1).equalsIgnoreCase("*")){
					mySqlBuf.append(" where GOODSNO like  '%" + serch+"%'");
				}else{
					mySqlBuf.append(" where SOURCENO like '" + serch+"%'");
				}
		}else{//優先查詢 貨號
				if (aInput.substring(0, 1).equalsIgnoreCase("*")){
					mySqlBuf.append(" where SOURCENO like '" + serch+"%'");
				}else{
					mySqlBuf.append(" where GOODSNO  like  '%" + serch+"%'");
				}
		}
		mySqlBuf.append(mOrderBy);
		
		int catched = 0;
		cs = db.rawQuery(mySqlBuf.toString(), null);
		if (cs.getCount()>0)	catched=1;
		
		if (catched==0) {
			mySqlBuf.delete(0, mySqlBuf.toString().length());
			mySqlBuf.append(ASK);
			mySqlBuf.append(" where GOODSNAME like  '%" + serch+"%'");
			mySqlBuf.append(mOrderBy);
			cs = null;
			cs = db.rawQuery(mySqlBuf.toString(), null);
			if (cs.getCount()>0)	catched=1;
		}

		if (catched==0) {
			mySqlBuf.delete(0, mySqlBuf.toString().length());
			mySqlBuf.append(ASK);
			mySqlBuf.append(" where SUPPLY like  '%" + serch+"%'");
			mySqlBuf.append(mOrderBy);
			cs = null;
			cs = db.rawQuery(mySqlBuf.toString(), null);
			if (cs.getCount()>0)	catched=1;
		}
		
		if (catched==0) {
			mySqlBuf.delete(0, mySqlBuf.toString().length());
			mySqlBuf.append(ASK);
			mySqlBuf.append(" where PS like  '%" + serch+"%'");
			mySqlBuf.append(mOrderBy);
			cs = null;
			cs = db.rawQuery(mySqlBuf.toString(), null);
			if (cs.getCount()>0)	catched=1;
		}
		return cs;
	}
	
	public static String ColorGetTxt(Context context,String key){
		String colorn="00;混色;"+Color.rgb(208, 208, 208);
		SQLiteDatabase db = Utilis.getDB(context);
		Cursor cs = null;					
		StringBuffer mySqlBuf = new StringBuffer();
			mySqlBuf.append("select distinct NO,TXT,PIC from qc_color where NO='"+key+"' or TXT='"+key+"'");
			cs = db.rawQuery(mySqlBuf.toString(), null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				if (cs.getString(1).indexOf("黑")<0)
					colorn=cs.getString(0)+";"+cs.getString(1)+";"+cs.getInt(2);
				else
					colorn=cs.getString(0)+";"+cs.getString(1)+";"+Color.rgb(208, 208, 208);
			}
			db.close();
			cs.close();
		return colorn;	
	}
	
	public static String getStk(Context context,SQLiteDatabase db,int nKind,String Deptno,String User,String Goodsnos,int U00){
		String getStk="";
		Cursor cs=null;
		int U20=0;int U21=0;int U30=0;int U40=0;
		if (nKind==20){//總倉&門市 出退貨時  Deptno=AA User=A1  
			cs=db.rawQuery("select sum(UNIT20),sum(UNIT30),sum(UNIT40) from qitems where GOODSNOS='"+Goodsnos+"' and DEPTNO='"+User+"' and (ISCONFIRM='Y' or ISCONFIRM='R')", null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				U21=cs.getInt(0);U30=cs.getInt(1);U40=cs.getInt(2);
			}				
			cs=db.rawQuery("select sum(UNIT20) from qitems where GOODSNOS='"+Goodsnos+"' and USER='"+User+"' and ISCONFIRM='Y' and (QKIND='20' or QKIND='21')", null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				U20=cs.getInt(0);
			}				
			getStk="進."+(U20-U21)+" 銷."+(U30+U40)+" 存."+(U20-U21-U30-U40);
			if (U00<0 && (U20-U21-U30-U40)+U00<0)getStk+=" 庫存不足!!";
			
		}else if (nKind==21){//以門市轉貨單  出貨時 是扣庫存
			cs=db.rawQuery("select sum(UNIT20),sum(UNIT30),sum(UNIT40) from qitems where GOODSNOS='"+Goodsnos+"' and DEPTNO='"+Deptno+"' and (ISCONFIRM='Y' or ISCONFIRM='R')", null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				U21=cs.getInt(0);U30=cs.getInt(1);U40=cs.getInt(2);
			}				
			cs=db.rawQuery("select sum(UNIT20) from qitems where GOODSNOS='"+Goodsnos+"' and USER='"+Deptno+"' and ISCONFIRM='Y' and (QKIND='20' or QKIND='21')", null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				U20=cs.getInt(0);
			}				
			getStk="進."+(U20-U21)+" 銷."+(U30+U40)+" 存."+(U20-U21-U30-U40);
			if (U00>0 && (U20-U21-U30-U40-U00)<0)getStk+=" 庫存不足!!";
			
		}else if (nKind==30){//總倉會員銷貨
			cs=db.rawQuery("select sum(UNIT30),sum(UNIT40) from qitems where GOODSNOS='"+Goodsnos+"' and (DEPTNO='"+Deptno+"' and USER='"+User+"') and (QKIND='30' or QKIND='40')", null);
			if (cs.getCount()>0){
				cs.moveToFirst();
				U30=cs.getInt(0);U40=cs.getInt(1);
			}
			if (U00<0 && (U30+U40+U00)<0 ) getStk="累計銷."+(U30+U40)+" 庫存不足!!"; else getStk="累計銷."+(U30+U40);
			
			
		}else if (nKind==31){//門市實體銷貨 算出目前門市庫存 Deptno=A1 User=ZA1
			//總倉進行實銷
			if (!Utilis.getIni(context, "SYS", "DEPT", 1).equalsIgnoreCase(Deptno)){
				cs=db.rawQuery("select sum(UNIT20),sum(UNIT30),sum(UNIT40) from qitems where GOODSNOS='"+Goodsnos+"' and DEPTNO='"+Deptno+"' and (ISCONFIRM='Y' or ISCONFIRM='R')", null);
				if (cs.getCount()>0){
					cs.moveToFirst();
					U21=cs.getInt(0);U30=cs.getInt(1);U40=cs.getInt(2);
				}				
				cs=db.rawQuery("select sum(UNIT20) from qitems where GOODSNOS='"+Goodsnos+"' and USER='"+Deptno+"' and ISCONFIRM='Y' and (QKIND='20' or QKIND='21')", null);
				if (cs.getCount()>0){
					cs.moveToFirst();
					U20=cs.getInt(0);
				}				
				getStk="進."+(U20-U21)+" 銷."+(U30+U40)+" 存."+(U20-U21-U30-U40);
				if (U00>0 && (U20-U21-U30-U40-U00)<0)getStk+=" 庫存不足!!";
			} else{//門市銷貨
				cs=db.rawQuery("select sum(UNIT30),sum(UNIT40) from qitems where GOODSNOS='"+Goodsnos+"' and (DEPTNO='"+Deptno+"' and USER='"+User+"') and (ISCONFIRM='Y' or ISCONFIRM='R')", null);
				if (cs.getCount()>0){
					cs.moveToFirst();
					U30=cs.getInt(0);U40=cs.getInt(1);
				}
				if (U00<0 && (U30+U40+U00)<0 ) getStk="累計銷."+(U30+U40)+" 庫存不足!!"; else getStk="累計銷."+(U30+U40);
			}
		}
		if (cs!=null) cs.close();
		
		return getStk;
	}
	
	@SuppressWarnings("unused")
	public static void initContentDB(SQLiteDatabase db, Context cnt){
		int version = db.getVersion();
		if (version >= 57) {
			return ;
		}
		switch (version){
			case 22:
				try{
					//進行更新後執行
				      File file = new File(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+ "/cwbuyer/para/ini.sdb");
				      SQLiteDatabase dbini = SQLiteDatabase.openOrCreateDatabase(file, null);
				      
//				      db.execSQL("update system set T7='0',T8='0',T9='0',PS='交易名稱以外,7外加猜色尺寸' where _ID=9");
				      //鞋 viviancode T7=1
				      
				      dbini.execSQL("update system set T1='門市轉貨單',T2='總倉出貨單',T3='員工自購',T4='盤點',T5='故障品登錄(保留)',T6='門市對帳單(保留)',T7='(保留)',T8='(保留)',T9='(保留)',PS='' where _ID=17");
					
				      dbini.execSQL("update system set T1='大圖編輯(唯讀)',T2='明細清冊(唯讀)',T3='重返結帳作業',T4='各門市庫存表',T5='追蹤',T6='明細&補印條碼',T7='保留',T8='保留',T9='保留',PS='' where _ID=70");
					
				      dbini.execSQL("update system set T1='大圖編輯',T2='明細清冊',T3='重返結帳作業',T4='各門市庫存表',T5='追蹤',T6='明細&補印條碼',T7='保留',T8='保留',T9='保留',PS='' where _ID=73");
					
				      dbini.execSQL("update system set T1='大圖編輯',T2='明細清冊',T3='收尾款結帳',T4='各門市庫存表',T5='追蹤',T6='明細&補印條碼',T7='保留',T8='保留',T9='保留',PS='' where _ID=71");
					
				      dbini.execSQL("update system set T1='大圖編輯',T2='明細清冊',T3='結帳',T4='各門市庫存表',T5='追蹤',T6='明細&補印條碼',T7='保留',T8='保留',T9='保留',PS='' where _ID=72");
					
				      dbini.execSQL("update system set T1='採購參數設定',T2='採購單(新品&追加)',T3='廠商進貨',T4='門市出貨',T5='門市銷貨',T6='員工自購',T7='代碼設定',T8='門市轉貨單',T9='盤點',PS='' where _ID=21");
				      
				      dbini.execSQL("update system set T1='折數一',T2='折數二',T3='折數三',T4='折數四',T5='折數五',T6='折數六',T7='折數七',T8='折數八',T9='折數九',PS='常用折數設定' where _ID=1400");

				      dbini.execSQL("update system set T1='條碼機種類:1GODEX',T2='條碼機設定IP(198)',T3='條碼機設定PORT(9101)',T4='出貨單列印指定IP(199)',T5='出貨單列印指定PORT(9102)',T6='錢櫃控制指定IP(232)',T7='錢櫃控制指定PORT(9600)',T8='條碼機印製排數',T9='熱感列印:0.大明細1.小明細2.大主貨3.小主貨4.來源明細5.來源主貨',PS='條碼機的相關設定,及錢櫃設定等' where _ID=1550");
					
				      dbini.execSQL("update system set T9='出單0不印 1.(60)印名稱 2.(60)印LOGO 3(80)印LOGO 4(80)單等長 5-6(100) 9印A4單' where _ID=1300");

				      dbini.execSQL("update system set T9='開單時優先搜尋0貨號1來源編號2貨號+直銷  ' where _ID=1201");

				      dbini.execSQL("update system set T1='0批發1零售',T7='各門市會員是否同步0.同步(1.不同步)',T8='數位相機角度(0)(90)(180)(270)',T9='(0)歷史價 (1)取較小 (2)標準價(指定密碼) ' where _ID=1250");

				      dbini.execSQL("update system set T1='0.採購雲端輸出選項1.預設雲端',T4='0.輸出品名1.輸出備註(出圖預設品名)',T5='0.輸出貨號1.輸出來源(條碼預設貨號)',T6='0.輸出貨號1.輸出來源(訂購出圖貨號)',T7='交易優先拜訪0.單據1.採購單',T8='網頁上架 7預設上架 0不上架',T9='採購單顯示為0.品名1.備註'  where _ID=1620");

				      dbini.execSQL("update system set T1='0.門市同進貨輸出1.不詢問',T2='(總倉)電子郵件',T3='(採購)電子郵件',T4='門市業績(0本地端)(1雲端)',T5='折數是否隱藏 0不隱藏 1要隱藏',T6='預購留貨算應收帳款(0不計)(1要計)(2全不計)',T7='年度謄檔0不啟動 1啟動',T8='雲端預購留貨0不啟動 1.啟動',T9='0.建新品不馬上存入 1.即時存入伺服器',PS='門市結帳設定&門市業績統計方式' where _ID=1650");
					
				      dbini.execSQL("update system set T3='預購' where _ID=52");
					
				      dbini.execSQL("update system set T1='交易備註一',T2='交易備註二',T3='交易備註三',T4='交易備註四',T5='交易備註五',T6='交易備註六',T7='交易備註七',T8='交易備註八',T9='交易備註九',PS='交易時,常用的備註詞彙' where _ID=1500");
					
				      dbini.execSQL("update system set T6='0.賣價=定價策略 1.賣價=定價策略+管銷 2.公式',T7='0.整數 1.小數23位',T8='0.原價 1.無條件捨去 5.四捨五入法 9.無條件進入法',T9='(成本*)中盤價:批價 (批價*)網購價:零售' where _ID=1680");
					
				      dbini.close();

					db.execSQL("UPDATE qitems SET COUNTRY='台灣' WHERE COUNTRY='1'");					
					db.execSQL("UPDATE qitems SET COUNTRY='中國' WHERE COUNTRY='2'");					
					db.execSQL("UPDATE qitems SET COUNTRY='韓國' WHERE COUNTRY='3'");					
					db.execSQL("UPDATE qitems SET COUNTRY='日本' WHERE COUNTRY='4'");					
					db.execSQL("UPDATE qitems SET COUNTRY='泰國' WHERE COUNTRY='6'");					
					db.execSQL("UPDATE qitems SET UNIT10=0 WHERE UNIT10 isnull");
					db.execSQL("UPDATE qitems SET UNIT20=0 WHERE UNIT20 isnull");
					db.execSQL("UPDATE qitems SET UNIT30=0 WHERE UNIT30 isnull");
					db.execSQL("UPDATE qitems SET UNIT40=0 WHERE UNIT40 isnull");
					db.execSQL("UPDATE qitems SET TR='Y0' WHERE TR isnull or TR<' ' or TR=QKIND");
					db.execSQL("UPDATE qitems SET FINEDATE='' WHERE FINEDATE isnull ");
					db.execSQL("UPDATE qitems SET PS='' WHERE PS isnull ");
					db.execSQL("UPDATE qdetail SET UNIT='1' WHERE UNIT isnull");
					db.execSQL("UPDATE qdetail SET PS='' WHERE PS isnull");
					
					db.execSQL("UPDATE qhead SET ORDDATE='' WHERE ORDDATE isnull");
					db.execSQL("UPDATE qhead SET FINEDATE='' WHERE FINEDATE isnull");
					db.execSQL("UPDATE qhead SET PS='' WHERE PS isnull");
					db.execSQL("UPDATE qhead SET PS1='' WHERE PS1 isnull");
					db.execSQL("UPDATE qhead SET PS2='' WHERE PS2 isnull");
					db.execSQL("UPDATE qhead SET ISLIST='N' WHERE ISLIST isnull");
					
					db.setVersion(23);
					
					if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
						String FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa0_aa1_auto_hi_n.php";
						String serverFileH ="/aa0_aa1_auto_hi_n.php";
			        	boolean success=false;
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa1_aa0_hi_update_n.php";
						serverFileH ="/aa1_aa0_hi_update_n.php";
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa1_aa0_batch_qdetail_n.php";
						serverFileH ="/aa1_aa0_batch_qdetail_n.php";
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/admin_checkout.php";
						serverFileH ="/web/admin/admin_checkout.php";
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/mem_psn.php";
						serverFileH ="/web/member/mem_psn.php";
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/msg_message_insert.php";
						serverFileH ="/web/msg/msg_message_insert.php";
			        	success=false;
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
						 FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/msg_message.js";
						serverFileH ="/web/js/msg_message.js";
						success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
						
					}
				}catch(Exception e){
					e.printStackTrace();
				}
				
			case 23:	db.setVersion(24);
			
			case 24:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					SQLite.initVersion24(db,cnt);
				}
				SQLite.createQacc(db);
				SQLite.createQMacc(db);
				db.setVersion(25);
			case 25:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/msg_message.php";
					String serverFileH ="/web/msg/msg_message.php";
		        	boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/msg_message_content.php";
					serverFileH ="/web/msg/msg_message_content.php";
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/admin_message.php";
					serverFileH ="/web/admin/admin_message.php";
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/mem_psn.php";
					serverFileH ="/web/member/mem_psn.php";
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(26);
			case 26:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa0_aa1_auto_qmacc.php";
					String serverFileH ="/aa0_aa1_auto_qmacc.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa1_aa0_qmacc_update.php";
					serverFileH ="/aa1_aa0_qmacc_update.php";
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(27);
			case 27:
			      File file = new File(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+ "/cwbuyer/para/ini.sdb");
			      SQLiteDatabase dbini = SQLiteDatabase.openOrCreateDatabase(file, null);
			      dbini.execSQL("update system set T5='採購單的排序 0.依輸入順序(預設) 1.依貨號 2.依來源編號' where _ID=1680");
			      dbini.execSQL("update system set T5='0' where _ID=1681");
			      dbini.close();
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/getweb_pic.php";
					String serverFileH ="/getweb_pic.php";
		        	boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}
				db.setVersion(28);
			case 28:		
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep06_1.php";
					String serverFileH ="/qrep06_1.php";
		        	boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}
				db.setVersion(29);
			case 29:{	
				String mDBPath = android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"cwbuyer/data/20123/qmtrio.sdb";		 
				File fileQmtrio = new File(mDBPath);
				SQLiteDatabase pdb = SQLiteDatabase.openOrCreateDatabase(fileQmtrio, null);
		         Cursor res = null;
		        try {
		            res = pdb.rawQuery("Select * from qpandn limit 1", null);
		            int colIndex =0; 
		            colIndex =res.getColumnIndex("SDT");
		            if (colIndex==-1){
						pdb.execSQL("ALTER TABLE qpandn add COLUMN SDT varchar(12)");
		            }
		            colIndex =res.getColumnIndex("BANKNAME");
		            if (colIndex==-1){
						pdb.execSQL("ALTER TABLE qpandn add COLUMN BANKNAME varchar(12)");
		            }
		            colIndex =res.getColumnIndex("TR");
		            if (colIndex==-1){
						pdb.execSQL("ALTER TABLE qpandn add COLUMN TR varchar(6)");
		            }
		
		        } catch (Exception e) {
		        } finally {
		            try { if (res !=null){ res.close(); } } catch (Exception e1) {}
		        }
		        pdb.close();
				
				Utilis.putIni(cnt, "INIH", "QHIS",DateUtil.getCurrentDate(),7);
				Utilis.putIni(cnt, "INIH", "QHIS",DateUtil.getCurrentDate(),8);
				SQLite.initVersion29(cnt);
				db.setVersion(30);
			}
			case 30:{
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qmtrio.php";
					String serverFileH ="/qmtrio.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(31);
			}
			case 31:
			case 32:
			case 33:
			case 34:
			case 35:	
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qpandngetdat.php";
					String serverFileH ="/qpandngetdat.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep06_1.php";
					serverFileH ="/qrep06_1.php";
		        	success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(36);
			case 36:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa0_aa1_auto_hi_n.php";
					String serverFileH ="/aa0_aa1_auto_hi_n.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/aa0_aa1_dn_hi.php";
					serverFileH ="/aa0_aa1_dn_hi.php";
		        	success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(37);
			case 37:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qpandngetdat.php";
					String serverFileH ="/qpandngetdat.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}				
				db.setVersion(38);
			case 38:
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					SQLite.initVersion38(db,cnt);
				}
				SQLite.createPpri(db);
				db.setVersion(39);
			case 39:{
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep01_30.php";
					String serverFileH ="/qrep01_30.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep01_3.php";
					serverFileH ="/qrep01_3.php";
					success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}
				db.setVersion(40);
			}	
			case 40:{
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep_today.php";
					String serverFileH ="/qrep_today.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					SQLite.initVersion38(db,cnt);
				}
				SQLite.createPpri(db);
				db.setVersion(41);
			}	
			case 41:{
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qdeptrep_30_N.php";
					String serverFileH ="/qdeptrep_30_N.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qrep_today.php";
					serverFileH ="/qrep_today.php";
					success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);

					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qdeptrep_31_N.php";
					serverFileH ="/qdeptrep_31_N.php";
					success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);

					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qdeptrep_32_N.php";
					serverFileH ="/qdeptrep_32_N.php";
					success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}
				db.setVersion(42);
			}	
			case 42:{
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/getweb_bsumreport.php";
					String serverFileH ="/getweb_bsumreport.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
				}
				db.setVersion(43);
			}	
			case 43:{
			      File fileini = new File(android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+ "/cwbuyer/para/ini.sdb");
			      SQLiteDatabase dbini2 = SQLiteDatabase.openOrCreateDatabase(fileini, null);
			      dbini2.execSQL("update system set T5='請輸入條碼四IP位址(192.168.x.97)',T6='請輸入條碼四Port(9103)' where _ID=1203");
			      dbini2.execSQL("update system set T5='192.168.x.97',T6='9103' where _ID=1205");
			      dbini2.close();
				
				if (Utilis.toInt(Utilis.getIni(cnt, "SYS", "IMPORT", 3))==1){
					String  FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qdeptrep_30_N44.php";
					String serverFileH ="/qdeptrep_30_N44.php";
					boolean success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					FileH=android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+File.separator+"CWBuyer/para/qdeptrep_32_N44.php";
					serverFileH ="/qdeptrep_32_N44.php";
					success=false;
					success=SQLite.ftpSendServer(cnt,FileH,serverFileH);
					
					
				}
				db.setVersion(44);
			}	
			case 44:{
				if (SQLite.initVersion44(db,cnt)==true)
					db.setVersion(45);
			}
			case 45:
			case 46:
			case 47:	
			case 48:	
			{
			      if (SQLite.initVersion45(db,cnt)==true)
			      db.setVersion(49);
			}
			case 49:
			case 50:
			case 51:
			{
			      if (SQLite.initVersion49(db,cnt)==true)
			      db.setVersion(52);
			}
			case 52:
			{
			      if (SQLite.initVersion53(db,cnt)==true)
			      db.setVersion(53);
			}
			case 53:
			{
				if (SQLite.initVersion54(db,cnt)==true)
			      db.setVersion(54);
			}
			case 54:
			{
				if (SQLite.initVersion55(db,cnt)==true)
			      db.setVersion(55);
			}
			case 55:
			{
				if (SQLite.initVersion56(db,cnt)==true)
			      db.setVersion(56);
			}
			case 56:
			{
			      db.execSQL("update qitems set SIZES=(select distinct SIZE from qdetail where qitems.GOODSNO=qdetail.GOODSNO ) where SIZES<>'F' and SIZES not like '%,%';");
			      db.setVersion(57);
			}

		}
	}
}
