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;  
    }  
}