IT工程師數位筆記本

CREATE TABLE [dbo].[CrashDB](
    [id] [float] NULL,
    [device_type] [float] NULL,
    [client_type] [float] NULL,
    [page_name] [nvarchar](255) NULL,
    [exception_name] [nvarchar](255) NULL,
    [crash_type] [float] NULL,
    [app_version] [nvarchar](255) NULL,
    [os_version] [nvarchar](255) NULL,
    [device_model] [nvarchar](255) NULL,
    [device_id] [nvarchar](255) NULL,
    [network_type] [float] NULL,
    [partner] [float] NULL,
    [memory_info] [nvarchar](255) NULL,
    [crash_time] [nvarchar](255) NULL,
    [exception_stack] [nvarchar](max) NULL,
) ON [PRIMARY]

CREATE TABLE [dbo].[CrashDB](
    [id] [float] NULL,
    [device_type] [float] NULL,
    [client_type] [float] NULL,
    [page_name] [nvarchar](255) NULL,
    [exception_name] [nvarchar](255) NULL,
    [crash_type] [float] NULL,
    [app_version] [nvarchar](255) NULL,
    [os_version] [nvarchar](255) NULL,
    [device_model] [nvarchar](255) NULL,
    [device_id] [nvarchar](255) NULL,
    [network_type] [float] NULL,
    [partner] [float] NULL,
    [memory_info] [nvarchar](255) NULL,
    [crash_time] [nvarchar](255) NULL,
    [exception_stack] [nvarchar](max) NULL,
    [crash_desc] [nvarchar](255) NULL,
    [dis_info] [nvarchar](max) NULL,
    [crash_category_id] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[CrashStore](
    [categoty_id] [int] IDENTITY(1,1) NOT NULL,
    [page_name] [nvarchar](255) NULL,
    [sub_crash_desc] [nvarchar](500) NULL,
    [sub_crash_length] [int] NULL,
    [app_version] [nvarchar](50) NULL,
    [fix_status] [nchar](10) NULL,
    [first_find_date] [datetime] NULL,
 CONSTRAINT [PK_CrashStore] PRIMARY KEY CLUSTERED 
(
    [categoty_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CrashStore] ADD  CONSTRAINT [DF_CrashStore_first_find_date]  DEFAULT (getdate()) FOR [first_find_date]
GO

CREATE PROCEDURE [dbo].[UpdateCrashDesc]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

update CrashDB
set crash_desc = '內存溢出'
where exception_stack like '%OutOfMemory%'
and crash_desc is null

update CrashDB
set crash_desc = 'ClassCastException'
where crash_desc is null
and exception_stack like '%java.lang.ClassCastException%'

update CrashDB
set crash_desc = '數組越界'
where crash_desc is null
and exception_stack like '%OutOfBoundsException%'

update CrashDB
set crash_desc = 'java.lang.VerifyError'
where crash_desc is null
and exception_stack like '%java.lang.VerifyError%'

update CrashDB
set crash_desc = '各個頁面的空指針'
where crash_desc is null
and exception_stack like '%NullPointerException%'

update CrashDB
set crash_desc = 'is your activity running?'
where crash_desc is null
and exception_stack like '%is your activity running?%'

update CrashDB
set crash_desc = 'doInBackground'
where crash_desc is null
and exception_stack like '%doInBackground%'

update CrashDB
set crash_desc = 'view not attached to window manager'
where crash_desc is null
and exception_stack like '%not attached to window manager%'

update CrashDB
set crash_desc = 'Package manager has died'
where crash_desc is null
and exception_stack like '%Package manager has died%'

update CrashDB
set crash_desc = 'NoSuchMethodError'
where crash_desc is null
and exception_stack like '%NoSuchMethodError%'

update CrashDB
set crash_desc = 'SQLiteException相關'
where crash_desc is null
and exception_stack like '%SQLite%'

update CrashDB
set crash_desc = 'Looper.prepare()'
where crash_desc is null
and exception_stack like '%Looper.prepare()%'

update CrashDB
set crash_desc = 'UnsatisfiedLinkError'
where crash_desc is null
and exception_stack like '%UnsatisfiedLinkError:%'

update CrashDB
set crash_desc = 'Resources$NotFoundException'
where crash_desc is null
and exception_stack like '%Resources$NotFoundException%'

update CrashDB
set crash_desc = 'InflateException'
where crash_desc is null
and exception_stack like '%InflateException%'

update CrashDB
set crash_desc = 'Failure delivering result ResultInfo'
where crash_desc is null
and exception_stack like '%Failure delivering result ResultInfo%'

update CrashDB
set crash_desc = 'ListView刷新數據'
where crash_desc is null
and exception_stack like '%The content of the adapter has changed but ListView did not receive a notification%'

update CrashDB
set crash_desc = 'NumberFormatException'
where crash_desc is null
and exception_stack like '%NumberFormatException%'

update CrashDB
set crash_desc = '各個頁面的空指針'
where crash_desc is null
and exception_stack like '%NullPointerException%'

update CrashDB
set crash_desc = '窗體泄露'
where crash_desc is null
and exception_stack like '%The specified child already has a parent. You must call removeView()%'

update CrashDB
set crash_desc = 'TransactionTooLargeException'
where crash_desc is null
and exception_stack like '%TransactionTooLargeException%'

update CrashDB
set crash_desc = 'Fragment相關1: Can not perform this action after onSaveInstanceState'
where crash_desc is null
and exception_stack like '%Can not perform this action after onSaveInstanceState%'

update CrashDB
set crash_desc = 'Fragment相關2: Fragment already added'
where crash_desc is null
and exception_stack like '%Fragment already added%'


update CrashDB
set crash_desc = 'ActivityNotFoundException:'
where crash_desc is null
and exception_stack like '%ActivityNotFoundException:%'

update CrashDB
set crash_desc = 'parameter must be a descendant of this view'
where crash_desc is null
and exception_stack like '%parameter must be a descendant of this view%'

update CrashDB
set crash_desc = 'Permission相關'
where crash_desc is null
and exception_stack like '%permission%'

update CrashDB
set crash_desc = 'libcore.io.ErrnoException: getsockname failed'
where crash_desc is null
and exception_stack like '%libcore.io.ErrnoException: getsockname failed%'

update CrashDB
set crash_desc = 'libcore.io.DiskLruCache'
where crash_desc is null
and exception_stack like '%libcore.io.DiskLruCache%'

update CrashDB
set crash_desc = 'Unknown component'
where crash_desc is null
and exception_stack like '%Unknown component%'

update CrashDB
set crash_desc = 'Unable to initialize media recorder'
where crash_desc is null
and exception_stack like '%Unable to initialize media recorder%'

update CrashDB
set crash_desc = 'Could not read input channel file descriptors from parcel'
where crash_desc is null
and exception_stack like '%Could not read input channel file descriptors from parcel%'

update CrashDB
set crash_desc = 'Unable to add window'
where crash_desc is null
and exception_stack like '%Unable to add window%'

update CrashDB
set crash_desc = 'NoClassDefFoundError'
where crash_desc is null
and exception_stack like '%java.lang.NoClassDefFoundError%'

update CrashDB
set crash_desc = 'EGL相關'
where crash_desc is null
and exception_stack like '%Could not create an EGL context%'

update CrashDB
set crash_desc = 'NoSuchFieldError'
where crash_desc is null
and exception_stack like '%NoSuchFieldError%'

update CrashDB
set crash_desc = '百度相關'
where crash_desc is null
and exception_stack like '%baidu%'

update CrashDB
set crash_desc = 'Unable to create application'
where crash_desc is null
and exception_stack like '%Unable to create application%'

update CrashDB
set crash_desc = 'GoogleAnalytics相關1: Package manager has die'
where exception_stack like '%google%' and crash_desc='Package manager has die'

update CrashDB
set crash_desc = 'GoogleAnalytics相關2: 空指針'
where exception_stack like '%google%' and crash_desc='各個頁面的空指針'

update CrashDB
set crash_desc = 'GoogleAnalytics相關3: TransactionTooLargeException'
where exception_stack like '%google%' and crash_desc='TransactionTooLargeException'

update CrashDB
set crash_desc = 'GoogleAnalytics相關4: 不明覺厲'
where exception_stack like '%google%' and crash_desc is null

update CrashDB
set crash_desc = 'TimeoutException相關'
where crash_desc is null and exception_stack like '%TimeoutException%' 

update CrashDB
set crash_desc = 'jpush相關'
where crash_desc is null and exception_stack like '%jpush%' 



update CrashDB
set crash_desc = 'bitmap size exceeds 32bits'
where exception_stack like '%bitmap size exceeds 32bits%'
and crash_desc is null



update CrashDB
set crash_desc = 'Method called after release()  at android.hardware.Camera.startPreview'
where exception_stack like '%Method called after release()  at android.hardware.Camera.startPreview%'
and crash_desc is null


update CrashDB
set crash_desc = 'Service Intent must be explicit: Intent { act=com.sina.weibo.remotessoservice }'
where exception_stack like '%Service Intent must be explicit: Intent { act=com.sina.weibo.remotessoservice }%'
and crash_desc is null




update CrashDB
set crash_desc = 'EventBusException'
where exception_stack like '%EventBusException%'
and crash_desc is null


update CrashDB
set crash_desc = 'divide by zero'
where exception_stack like '%divide by zero%'
and crash_desc is null


update CrashDB
set crash_desc = ' Broadcast already finished'
where exception_stack like '% Broadcast already finished%'
and crash_desc is null


update CrashDB
set crash_desc = 'java.lang.UnsupportedOperationException  at android.view.GLES20Canvas.clipPath'
where exception_stack like '%java.lang.UnsupportedOperationException  at android.view.GLES20Canvas.clipPath%'
and crash_desc is null

update CrashDB
set crash_desc = 'java.lang.IllegalArgumentException: height must be > 0  at android.graphics.Bitmap.checkWidthHeight'
where exception_stack like '%java.lang.IllegalArgumentException: height must be > 0  at android.graphics.Bitmap.checkWidthHeight%'
and crash_desc is null

update CrashDB
set crash_desc = 'native typeface cannot be made'
where exception_stack like '%native typeface cannot be made%'
and crash_desc is null


update CrashDB
set crash_desc = 'Fragment not attached to Activity'
where exception_stack like '%not attached to Activity%'
and crash_desc is null


update CrashDB
set crash_desc = 'java.lang.StackOverflowError  at android.text.StaticLayout.generate'
where exception_stack like '%java.lang.StackOverflowError  at android.text.StaticLayout.generate%'
and crash_desc is null

update CrashDB
set crash_desc = '不明覺厲'
where crash_desc is null

END

CREATE PROCEDURE [dbo].[GroupOnlineCrash]
AS
BEGIN
    SET NOCOUNT ON;

select * into #temp1 from CrashDB
where client_type=20
order by page_name, exception_name, exception_stack

select crash_desc, COUNT(crash_desc) as count from #temp1
group by crash_desc
order by COUNT(crash_desc) desc


END

CREATE PROCEDURE [dbo].[UpdateCrashStore]
@version varchar(30)
AS
BEGIN
    SET NOCOUNT ON;

select * into #temp1 from CrashDB

-- 1. 排除java.lang.VerifyError之類Crash對去重結果的影響
delete from #temp1
where SUBSTRING(dis_info, 1, 38) 
    = 'java.lang.VerifyError: Rejecting class'

-- 1.x 這里可以添加其它排除語句，減少對去重邏輯的干擾

-- 2. 取dis_info的前150個字符，去重
select distinct page_name, 
    SUBSTRING(dis_info, 1, 150) as sub_crash_desc 
    into #temp2 from #temp1
    order by page_name, SUBSTRING(dis_info, 1, 150)

-- 3. 在CrashStore表中，取出當前版本的之前已經統計過的Crash
select * into #tempCrashStore from CrashStore
    where app_version=@version

-- 4. 使用left join語句，篩選出今天的、未統計過的Crash
select t.page_name, t.sub_crash_desc 
    into #temp4 from #temp2 t 
    left join #tempCrashStore c
    on c.page_name = t.page_name 
    and c.sub_crash_desc = t.sub_crash_desc
    where c.categoty_id is null 

-- 5. 將今天統計的Crash放入CrashStore表
insert CrashStore(page_name, sub_crash_desc, 
        sub_crash_length, app_version)
    select distinct page_name, sub_crash_desc,
    150, @version from #temp4

END

CREATE TABLE [dbo].[PageOwner](
    [Activity] [nvarchar](100) NOT NULL,
    [Owner] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_PageOwner] PRIMARY KEY CLUSTERED 
(
    [Activity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

package com.youngheart.engine;

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.lang.Thread.UncaughtExceptionHandler;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import android.app.ActivityManager;
import android.app.ActivityManager.MemoryInfo;
import android.content.Context;
import android.content.pm.ApplicationInfo;
import android.content.pm.PackageInfo;
import android.content.pm.PackageManager;
import android.content.pm.PackageManager.NameNotFoundException;
import android.net.ConnectivityManager;
import android.net.NetworkInfo;
import android.os.Environment;
import android.os.Looper;
import android.provider.Settings.Secure;
import android.telephony.TelephonyManager;
import android.text.TextUtils;
import android.util.Log;
import android.widget.Toast;

import com.infrastructure.net.RequestParameter;
import com.infrastructure.net.URLData;

/**
 * UncaughtException處理類,當程序發生Uncaught異常的時候,
 * 由該類來接管程序,并記錄發送錯誤報告.
 * 需要在Application中注冊，為了要在程序啟動器就監控整個程序。
 */
public class CrashHandler implements UncaughtExceptionHandler {
    public static final String TAG = "CrashHandler";
    public static final String APP_CACHE_PATH = 
            Environment.getExternalStorageDirectory().getPath() 
            + "/YoungHeart/crash/";

    // 系統默認的UncaughtException處理類
    private Thread.UncaughtExceptionHandler mDefaultHandler;
    // CrashHandler實例
    private static CrashHandler instance;
    // 程序的Context對象
    private Context context;
    // 用來存儲設備信息和異常信息
    private Map<String, String> infos = new HashMap<String, String>();

    // 用于格式化日期,作為日志文件名的一部分
    private DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");

    /** 保證只有一個CrashHandler實例 */
    private CrashHandler() {
    }

    /** 獲取CrashHandler實例 ,單例模式 */
    public static CrashHandler getInstance() {
        if (instance == null)
            instance = new CrashHandler();
        return instance;
    }

    /**
     * 初始化
     */
    public void init(Context context) {
        // 獲取系統默認的UncaughtException處理器
        mDefaultHandler = Thread.getDefaultUncaughtExceptionHandler();
        // 設置該CrashHandler為程序的默認處理器
        Thread.setDefaultUncaughtExceptionHandler(this);
    }
    
    /*
     * 切換發生Crash所在的Activity
     */
    public void switchCrashActivity(Context context) {
        this.context = context;
    }

    /**
     * 當UncaughtException發生時會轉入該函數來處理
     */
    @Override
    public void uncaughtException(Thread thread, Throwable ex) {
        if (!handleException(ex) && mDefaultHandler != null) {
            // 如果用戶沒有處理則讓系統默認的異常處理器來處理
            mDefaultHandler.uncaughtException(thread, ex);
        } else {
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e) {
                Log.e(TAG, "error : ", e);
            }
            // 退出程序
            android.os.Process.killProcess(android.os.Process.myPid());
            System.exit(1);
        }
    }

    /**
     * 自定義錯誤處理,收集錯誤信息 發送錯誤報告等操作均在此完成.
     * 
     * @param ex
     * @return true:如果處理了該異常信息;否則返回false.
     */
    private boolean handleException(Throwable ex) {
        if (ex == null) {
            return false;
        }

        //把crash發送到服務器
        sendCrashToServer(context, ex);
        
        // 使用Toast來顯示異常信息
        new Thread() {
            @Override
            public void run() {
                Looper.prepare();
                Toast.makeText(context, "很抱歉,程序出現異常,即將退出.", 
                        Toast.LENGTH_SHORT).show();
                Looper.loop();
            }
        }.start();

        // 保存日志文件
        saveCrachInfoInFile(ex);
        return true;
    }

    /**
     * 保存錯誤信息到文件中
     * 
     * @param ex
     * @return 返回文件名稱,便于將文件傳送到服務器
     */
    private String saveCrachInfoInFile(Throwable ex) {
        StringBuffer sb = new StringBuffer();
        for (Map.Entry<String, String> entry : infos.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            sb.append(key + "=" + value + "\n");
        }
        Writer writer = new StringWriter();
        PrintWriter printWriter = new PrintWriter(writer);
        ex.printStackTrace(printWriter);
        Throwable cause = ex.getCause();
        while (cause != null) {
            cause.printStackTrace(printWriter);
            cause = cause.getCause();
        }
        printWriter.close();
        String result = writer.toString();
        sb.append(result);
        try {
            long timestamp = System.currentTimeMillis();
            String time = formatter.format(new Date());
            String fileName = "crash-" + time + "-" + timestamp + ".log";

            if (Environment.getExternalStorageState().equals(
                    Environment.MEDIA_MOUNTED)) {
                File dir = new File(APP_CACHE_PATH);
                if (!dir.exists()) {
                    dir.mkdirs();
                }
                FileOutputStream fos = new FileOutputStream(APP_CACHE_PATH + fileName);
                fos.write(sb.toString().getBytes());
                fos.close();
            }

            return fileName;
        } catch (Exception e) {
            Log.e(TAG, "an error occured while writing file...", e);
        }
        return null;
    }
    
    /**
     * 收集程序崩潰的相關信息
     * 
     * @param ctx
     */
    public void sendCrashToServer(Context ctx, Throwable ex) {                    
        //取出版本號
        PackageManager pm = ctx.getPackageManager();
        PackageInfo pi = null;
        try {
            pi = pm.getPackageInfo(ctx.getPackageName(),
                    PackageManager.GET_ACTIVITIES);
            if (pi != null) {
                String versionName = pi.versionName == null ? "null"
                        : pi.versionName;
                String versionCode = pi.versionCode + "";
                infos.put("versionName", versionName);
                infos.put("versionCode", versionCode);
            }
        } catch (NameNotFoundException e1) {
            e1.printStackTrace();
        }        
        
        HashMap<String, String> exceptionInfo = new HashMap<String, String>();
        
        try {
            ActivityManager am = (ActivityManager) ctx
                    .getSystemService(Context.ACTIVITY_SERVICE);
            String pageName = ctx.getClass().getName();
            MemoryInfo mi = new MemoryInfo();
            am.getMemoryInfo(mi);
            String memoryInfo = "Memory info:" + mi.availMem + ",app holds:"
                    + mi.threshold + ",Low Memory:" + mi.lowMemory;

            ApplicationInfo appInfo = ctx.getPackageManager()
                    .getApplicationInfo(ctx.getPackageName(),
                            PackageManager.GET_META_DATA);
            String channelId = appInfo.metaData.getString("UMENG_CHANNEL");

            String version = ctx.getPackageManager().getPackageInfo(
                    ctx.getPackageName(), 0).versionName;

            
            exceptionInfo.put("PageName", pageName);
            exceptionInfo.put("ExceptionName", ex.getClass().getName());
            exceptionInfo.put("ExceptionType", "1");
            exceptionInfo.put("ExceptionsStackDetail", getStackTrace(ex));
            exceptionInfo.put("AppVersion", version);
            exceptionInfo.put("OSVersion",  android.os.Build.VERSION.RELEASE);
            exceptionInfo.put("DeviceModel", android.os.Build.MODEL);
            exceptionInfo.put("DeviceId", getDeviceID(ctx));            
            exceptionInfo.put("NetWorkType", String.valueOf(isWifi(context)));
            exceptionInfo.put("ChannelId", channelId);
            exceptionInfo.put("ClientType", "100");
            exceptionInfo.put("MemoryInfo", memoryInfo);

            final String rquestParam = exceptionInfo.toString();
            new Thread() {

                @Override
                public void run() {
                    ArrayList<RequestParameter> params = new ArrayList<RequestParameter>();
                    RequestParameter rp1 = new RequestParameter("cityaA2", "111");
                    params.add(rp1);

//                    URLData urlData = new URLData();
//                    urlData.setKey("sed");
                    
                    RemoteService.getInstance().invoke("getWeatherInfo", params);
                }

            }.start();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 
     * @Title: getDeviceID
     * @Description: 獲取手機設備號
     * @param context
     * @return String
     * @throws
     */
    public static final String getDeviceID(Context context) {
        String deviceId = null;
        try {
            TelephonyManager tm = (TelephonyManager) context
                    .getSystemService(Context.TELEPHONY_SERVICE);
            deviceId = tm.getDeviceId();
            tm = null;
            if (TextUtils.isEmpty(deviceId)) {
                deviceId = Secure.getString(context.getContentResolver(),
                        Secure.ANDROID_ID);
            }

        } catch (Exception e) {
            deviceId = Secure.getString(context.getContentResolver(),
                    Secure.ANDROID_ID);
        }

        return deviceId;
    }
    
    private String getStackTrace(Throwable th) {
        final Writer result = new StringWriter();
        final PrintWriter printWriter = new PrintWriter(result);

        // If the exception was thrown in a background thread inside
        // AsyncTask, then the actual exception can be found with getCause
        Throwable cause = th;
        while (cause != null) {
            cause.printStackTrace(printWriter);
            cause = cause.getCause();
        }
        final String stacktraceAsString = result.toString();
        printWriter.close();

        return stacktraceAsString;
    }
    
    private static int isWifi(Context mContext) {  
        ConnectivityManager connectivityManager = (ConnectivityManager) mContext  
                .getSystemService(Context.CONNECTIVITY_SERVICE);  
        NetworkInfo activeNetInfo = connectivityManager.getActiveNetworkInfo();  
        if (activeNetInfo != null  
                && activeNetInfo.getType() == ConnectivityManager.TYPE_WIFI) {  
            return 1;  
        }  
        return 0;  
    }  
}