using irisHelper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using irisDataBase.Model;
using System.Windows.Forms;

namespace irisDataBase.Service.mysqlImpl
{
    public class IrisSynchDataServiceImpl:IrisSynchDataService
    {
        //用批量提交事务方式同步
        public int updateTable(ref string resultString)
        {
            int re = -1;
            resultString = "success";
            string tableName = "";
            string[] tableNames = { "iris_data", "iris_person_photo", "sys_person", "sys_dept" , "acs_door",
                "acs_permission", "acs_strategy", "bus_device"};
            MySqlTransaction tx = null;
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter aAdapter = new MySqlDataAdapter();
            try
            {
                for (int t = 0; t < tableNames.Count(); t++)
                {
                    tableName = tableNames[t];

                    tx = DatabaseLocal.mySqlConnect.BeginTransaction();
                    if (tableName == "sys_person") cmd = new MySqlCommand("SELECT * FROM " + tableName + " WHERE DELFLAG=0", 
                        DatabaseServer.mySqlConnectionServer);
                    else cmd = new MySqlCommand("SELECT * FROM " + tableName, DatabaseServer.mySqlConnectionServer);
                    aAdapter.SelectCommand = cmd;
                    DataTable tempTable = new DataTable();
                    aAdapter.Fill(tempTable);

                    //绑定事务
                    cmd.Transaction = tx;

                    //清空表
                    cmd = new MySqlCommand("truncate table " + tableName, DatabaseLocal.mySqlConnect);
                    cmd.ExecuteNonQuery();

                    //服务器没有数据，则直接清空
                    if (tempTable.Rows.Count == 0)
                    {
                        tx.Commit();
                    }
                    else
                    {
                        //插入数据
                        string colNames = "";
                        string param = "";
                        if (tableName == "iris_data")
                        {
                            colNames = "ID,PERSON_ID,ID_CARD_NO,LEFT_IRIS_CODE1,LEFT_IRIS_CODE2,LEFT_IRIS_CODE3,RIGHT_IRIS_CODE1,RIGHT_IRIS_CODE2,RIGHT_IRIS_CODE3,LEFT_IMAGE1,LEFT_IMAGE2,LEFT_IMAGE3,RIGHT_IMAGE1,RIGHT_IMAGE2,RIGHT_IMAGE3";
                        }
                        else if (tableName == "iris_person_photo")
                        {
                            colNames = "PERSON_ID,PHOTO_DATA";
                        }
                        else if (tableName == "sys_person")
                        {
                            colNames = "ID,DELFLAG,CREATETIME,UPDATETIME,NAME,SEX,DEPTID,ID_CARD_NO,REMARKS,PERSON_TYPE,PERSON_CODE,PHOTO,OPRATION_ID,DUTY,NATION,BIRTHDAY,CARD_TYPE";
                        }
                        else if (tableName == "acs_door")
                        {
                            colNames = "ID,DOOR_CODE,DOOR_NAME,DESCRIPTION,DEPTID,OPEN_STATUS,BEGIN_DATETIME,END_DATETIME";
                        }
                        else if (tableName == "acs_permission")
                        {
                            colNames = "ID,DOOR_CODE,PERSON_ID,STRATEGY_ID,CREATE_TIME,CREATE_USER";
                        }
                        else if (tableName == "acs_strategy")
                        {
                            colNames = "ID,NAME,TYPE,BEGIN_DATETIME,END_DATETIME,WEEK,PERIOD,BEGIN_TIME,END_TIME,DESCRIPTION";
                        }
                        else if (tableName == "bus_device")
                        {
                            colNames = "ID,DEV_CODE,DEV_NAME,DEV_IP,DEV_TYPE,DOOR_CODE,INOUT_TYPE,DESCRIPTION";
                        }
                        else if (tableName == "sys_dept")
                        {
                            colNames = "ID,NUM,PID,PIDS,SIMPLENAME,FULLNAME,TIPS,VERSION,DEPTTYPE,STATUS,IS_PARENT,TENANT_ID,target_id_sync,target_pid_sync,IS_SYNC,IP_SEGMENT";
                        }


                        int colCount = colNames.Split(',').Count();
                        for (int i = 0; i < colCount; i++)
                        {
                            if (param == "") param = param + "@" + i;
                            else param = param + ",@" + i;
                        }

                        string sql = "insert into " + tableName + "(" + colNames + ") values (" + param + ")";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        int index = 0;
                        int resultCount = 0;
                        foreach (DataRow row in tempTable.Rows)
                        {
                            cmd.Parameters.Clear();

                            for (int c = 0; c < colCount; c++)
                            {
                                cmd.Parameters.AddWithValue("@" + c, row[c]);
                            }
                            resultCount = resultCount + cmd.ExecuteNonQuery();
                            if (index > 0 && (index % 100 == 0 || index == tempTable.Rows.Count - 1))
                            {
                                tx.Commit();
                                if (index != tempTable.Rows.Count - 1) tx = DatabaseLocal.mySqlConnect.BeginTransaction();
                            }
                            index++;
                        }

                        resultString = resultString + "," + tableName + "=" + resultCount;

                    }//end else
                }//end for

                re = 0;

            }
            catch (MySqlException exSql)
            {
                re = -1;
                resultString = "fail";
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTable " + tableName + exSql.Message);
            }
            catch (Exception ex)
            {
                re = -1;
                resultString = "fail";
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTable " + tableName + " EXCEPTION: " + ex.Message);

            }
            finally
            {
                tx.Dispose();
                aAdapter.Dispose();
                cmd.Dispose();
            }

            return re;
        }



        // 更新离线采集端发来的数据
        public int updateTableFromOffline(string data)
        {            
            int re = -1;

            List<CollListView> collListViewList = JsonConvert.DeserializeObject<List<CollListView>>(data);
            MySqlTransaction tx = null;
            MySqlCommand cmd = new MySqlCommand();
            try
            {
                tx = DatabaseLocal.mySqlConnect.BeginTransaction();

                //绑定事务
                cmd.Transaction = tx;

                foreach (CollListView collListView in collListViewList)
                {
                    //sys_person表                    
                    string sql = "delete from sys_person where ID=@ID";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.AddWithValue("@ID", collListView.id);
                    cmd.ExecuteNonQuery();

                    sql = "insert into sys_person (ID,DELFLAG,NAME,SEX,DEPTID,ID_CARD_NO,REMARKS,PERSON_CODE,NATION,BIRTHDAY) values"
                        + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9)";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@var0", collListView.id);
                    cmd.Parameters.AddWithValue("@var1", "0");
                    cmd.Parameters.AddWithValue("@var2", collListView.name);
                    cmd.Parameters.AddWithValue("@var3", collListView.sex);
                    cmd.Parameters.AddWithValue("@var4", collListView.deptId);
                    cmd.Parameters.AddWithValue("@var5", collListView.idCardNo);
                    cmd.Parameters.AddWithValue("@var6", collListView.remarks);
                    cmd.Parameters.AddWithValue("@var7", collListView.staffNo);
                    cmd.Parameters.AddWithValue("@var8", collListView.nation);
                    cmd.Parameters.AddWithValue("@var9", collListView.birthday);
                    cmd.ExecuteNonQuery();

                    //iris_person_photo表                    
                    sql = "delete from iris_person_photo where PERSON_ID=@ID";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.AddWithValue("@ID", collListView.id);
                    cmd.ExecuteNonQuery();

                    sql = "insert into iris_person_photo (PERSON_ID,PHOTO_DATA) values"
                        + "(@var0,@var1)";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@var0", collListView.id);
                    cmd.Parameters.AddWithValue("@var1", collListView.photoData);
                    cmd.ExecuteNonQuery();


                    //iris_data表                    
                    sql = "delete from iris_data where ID=@IRIS_ID";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.AddWithValue("@IRIS_ID", collListView.irisId);
                    cmd.ExecuteNonQuery();

                    sql = "insert into iris_data (ID,PERSON_ID,ID_CARD_NO,LEFT_IRIS_CODE1,LEFT_IRIS_CODE2,RIGHT_IRIS_CODE1,RIGHT_IRIS_CODE2) values"
                        + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6)";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@var0", collListView.irisId);
                    cmd.Parameters.AddWithValue("@var1", collListView.id);
                    cmd.Parameters.AddWithValue("@var2", collListView.idCardNo);
                    cmd.Parameters.AddWithValue("@var3", String.IsNullOrEmpty(collListView.irisL1)?null:Convert.FromBase64String(collListView.irisL1));
                    cmd.Parameters.AddWithValue("@var4", null);
                    cmd.Parameters.AddWithValue("@var5", String.IsNullOrEmpty(collListView.irisR1) ? null : Convert.FromBase64String(collListView.irisR1));
                    cmd.Parameters.AddWithValue("@var6", null);
                    cmd.ExecuteNonQuery();

                    //sys_dept表                    
                    sql = "delete from sys_dept where ID=@ID";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.AddWithValue("@ID", collListView.deptId);
                    cmd.ExecuteNonQuery();

                    sql = "insert into sys_dept (ID,SIMPLENAME) values"
                        + "(@var0,@var1)";
                    cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@var0", collListView.deptId);
                    cmd.Parameters.AddWithValue("@var1", collListView.simplename);
                    cmd.ExecuteNonQuery();                    
                }

                tx.Commit();
                re = 0;
            }
            catch (MySqlException exSql)
            {
                re = -1;
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTableFromOffline "  + exSql.Message);
            }
            catch (Exception ex)
            {
                re = -1;
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTableFromOffline " + " EXCEPTION: " + ex.Message);

            }
            finally
            {
                tx.Dispose();
                cmd.Dispose();
            }

            return re;
        }

        // 更新后台发来的数据
        public int updateTableFromServer(string data)
        {
            int re = -1;
            MySqlTransaction tx = null;
            MySqlCommand cmd = new MySqlCommand();

            LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "data= " + data);

            try
            {
                tx = DatabaseLocal.mySqlConnect.BeginTransaction();
                //绑定事务
                cmd.Transaction = tx;

                // 新增人员
                if (data.Contains("addPerson:"))
                {
                    data = data.Replace("addPerson:", "");
                    JObject json = (JObject)JsonConvert.DeserializeObject(data);//或者JObject jo = JObject.Parse(jsonText);                    

                    //sys_person表 
                    List<Person> personList = JsonConvert.DeserializeObject<List<Person>>(json["sys_person"].ToString());
                    foreach (Person person in personList)
                    {
                        string sql = "delete from sys_person where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", person.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into sys_person (ID,DELFLAG,CREATETIME,UPDATETIME,NAME,SEX,DEPTID,ID_CARD_NO,REMARKS,PERSON_TYPE,PERSON_CODE,PHOTO,OPRATION_ID,DUTY,NATION,BIRTHDAY,CARD_TYPE) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12,@var13,@var14,@var15,@var16)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", person.Id);
                        cmd.Parameters.AddWithValue("@var1", "0");
                        cmd.Parameters.AddWithValue("@var2", person.Createtime);
                        cmd.Parameters.AddWithValue("@var3", person.Updatetime);
                        cmd.Parameters.AddWithValue("@var4", person.Name);
                        cmd.Parameters.AddWithValue("@var5", person.Sex);
                        cmd.Parameters.AddWithValue("@var6", person.Deptid);
                        cmd.Parameters.AddWithValue("@var7", person.IdCardNo);
                        cmd.Parameters.AddWithValue("@var8", person.Remarks);
                        cmd.Parameters.AddWithValue("@var9", person.PersonType);
                        cmd.Parameters.AddWithValue("@var10", person.PersonCode);
                        cmd.Parameters.AddWithValue("@var11", person.Photo);
                        cmd.Parameters.AddWithValue("@var12", person.OprationId);
                        cmd.Parameters.AddWithValue("@var13", person.Duty);
                        cmd.Parameters.AddWithValue("@var14", person.Nation);
                        cmd.Parameters.AddWithValue("@var15", person.Birthday);
                        cmd.Parameters.AddWithValue("@var16", person.CardType);
                        cmd.ExecuteNonQuery();
                    }

                    //iris_person_photo表 
                    List<IrisPersonPhoto> irisPersonPhotoList = JsonConvert.DeserializeObject<List<IrisPersonPhoto>>(json["iris_person_photo"].ToString());
                    foreach (IrisPersonPhoto irisPersonPhoto in irisPersonPhotoList)
                    {
                        string sql = "delete from iris_person_photo where PERSON_ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", irisPersonPhoto.PersonId);
                        cmd.ExecuteNonQuery();

                        sql = "insert into iris_person_photo (PERSON_ID,PHOTO_DATA) values"
                            + "(@var0,@var1)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", irisPersonPhoto.PersonId);
                        cmd.Parameters.AddWithValue("@var1", irisPersonPhoto.PhotoData);
                        cmd.ExecuteNonQuery();
                    }

                    //iris_data表 
                    List<IrisData> irisDataList = JsonConvert.DeserializeObject<List<IrisData>>(json["iris_data"].ToString());
                    foreach (IrisData irisData in irisDataList)
                    {
                        string sql = "delete from iris_data where ID=@IRIS_ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@IRIS_ID", irisData.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into iris_data (ID,PERSON_ID,ID_CARD_NO,LEFT_IRIS_CODE1,LEFT_IRIS_CODE2,LEFT_IRIS_CODE3,RIGHT_IRIS_CODE1,RIGHT_IRIS_CODE2,RIGHT_IRIS_CODE3,LEFT_IMAGE1,LEFT_IMAGE2,LEFT_IMAGE3,RIGHT_IMAGE1,RIGHT_IMAGE2,RIGHT_IMAGE3) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12,@var13,@var14)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", irisData.Id);
                        cmd.Parameters.AddWithValue("@var1", irisData.PersonId);
                        cmd.Parameters.AddWithValue("@var2", irisData.IdCardNo);
                        cmd.Parameters.AddWithValue("@var3", irisData.LeftIrisCode1);
                        cmd.Parameters.AddWithValue("@var4", irisData.LeftIrisCode2);
                        cmd.Parameters.AddWithValue("@var5", irisData.LeftIrisCode3);
                        cmd.Parameters.AddWithValue("@var6", irisData.RightIrisCode1);
                        cmd.Parameters.AddWithValue("@var7", irisData.RightIrisCode2);
                        cmd.Parameters.AddWithValue("@var8", irisData.RightIrisCode3);
                        cmd.Parameters.AddWithValue("@var9", irisData.LeftImage1);
                        cmd.Parameters.AddWithValue("@var10", irisData.LeftImage2);
                        cmd.Parameters.AddWithValue("@var11", irisData.LeftImage3);
                        cmd.Parameters.AddWithValue("@var12", irisData.RightImage1);
                        cmd.Parameters.AddWithValue("@var13", irisData.RightImage2);
                        cmd.Parameters.AddWithValue("@var14", irisData.RightImage3);
                        cmd.ExecuteNonQuery();
                    }

                    //sys_dept表 
                    List<Dept> deptList = JsonConvert.DeserializeObject<List<Dept>>(json["sys_dept"].ToString());
                    foreach (Dept dept in deptList)
                    {
                        string sql = "delete from sys_dept where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", dept.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into sys_dept (ID,NUM,PID,PIDS,SIMPLENAME,FULLNAME,TIPS,VERSION,DEPTTYPE,STATUS,IS_PARENT,TENANT_ID,target_id_sync,target_pid_sync,IS_SYNC,IP_SEGMENT) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12,@var13,@var14,@var15)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", dept.Id);
                        cmd.Parameters.AddWithValue("@var1", dept.Num);
                        cmd.Parameters.AddWithValue("@var2", dept.Pid);
                        cmd.Parameters.AddWithValue("@var3", dept.Pids);
                        cmd.Parameters.AddWithValue("@var4", dept.Simplename);
                        cmd.Parameters.AddWithValue("@var5", dept.Fullname);
                        cmd.Parameters.AddWithValue("@var6", dept.Tips);
                        cmd.Parameters.AddWithValue("@var7", dept.Version);
                        cmd.Parameters.AddWithValue("@var8", dept.Depttype);
                        cmd.Parameters.AddWithValue("@var9", dept.Status);
                        cmd.Parameters.AddWithValue("@var10", dept.IsParent);
                        cmd.Parameters.AddWithValue("@var11", dept.TenantId);
                        cmd.Parameters.AddWithValue("@var12", dept.TargetIdSync);
                        cmd.Parameters.AddWithValue("@var13", dept.TargetPidSync);
                        cmd.Parameters.AddWithValue("@var14", dept.IsSync);
                        cmd.Parameters.AddWithValue("@var15", dept.IpSegment);
                        cmd.ExecuteNonQuery();
                    }
                }

                // 新增授权
                else if (data.Contains("addPermission:"))
                {
                    data = data.Replace("addPermission:", "");

                    JObject json = (JObject)JsonConvert.DeserializeObject(data);//或者JObject jo = JObject.Parse(jsonText);

                    //acs_strategy表 
                    List<Strategy> strategyList = JsonConvert.DeserializeObject<List<Strategy>>(json["acs_strategy"].ToString());

                    foreach (Strategy strategy in strategyList)
                    {
                        string sql = "delete from acs_strategy where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", strategy.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into acs_strategy (ID,NAME,TYPE,BEGIN_DATETIME,END_DATETIME,WEEK,PERIOD,BEGIN_TIME,END_TIME,DESCRIPTION) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", strategy.Id);
                        cmd.Parameters.AddWithValue("@var1", strategy.Name);
                        cmd.Parameters.AddWithValue("@var2", strategy.Type);
                        cmd.Parameters.AddWithValue("@var3", strategy.BeginDatetime);
                        cmd.Parameters.AddWithValue("@var4", strategy.EndDatetime);
                        cmd.Parameters.AddWithValue("@var5", strategy.Week);
                        cmd.Parameters.AddWithValue("@var6", strategy.Period);
                        cmd.Parameters.AddWithValue("@var7", strategy.BeginTime);
                        cmd.Parameters.AddWithValue("@var8", strategy.EndTime);
                        cmd.Parameters.AddWithValue("@var9", strategy.Description);
                        cmd.ExecuteNonQuery();

                    }


                    //acs_door表 
                    List<Door> doorList = JsonConvert.DeserializeObject<List<Door>>(json["acs_door"].ToString());
                    foreach (Door door in doorList)
                    {
                        string sql = "delete from acs_door where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", door.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into acs_door (ID,DOOR_CODE,DOOR_NAME,DESCRIPTION,DEPTID,OPEN_STATUS,BEGIN_DATETIME,END_DATETIME) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", door.Id);
                        cmd.Parameters.AddWithValue("@var1", door.DoorCode);
                        cmd.Parameters.AddWithValue("@var2", door.DoorName);
                        cmd.Parameters.AddWithValue("@var3", door.Description);
                        cmd.Parameters.AddWithValue("@var4", door.Deptid);
                        cmd.Parameters.AddWithValue("@var5", door.OpenStatus);
                        cmd.Parameters.AddWithValue("@var6", door.BeginDatetime);
                        cmd.Parameters.AddWithValue("@var7", door.EndDatetime);
                        cmd.ExecuteNonQuery();

                    }

                    //acs_permission表 
                    List<Permission> permissionList = JsonConvert.DeserializeObject<List<Permission>>(json["acs_permission"].ToString());
                    foreach (Permission permission in permissionList)
                    {
                        string sql = "delete from acs_permission where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", permission.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into acs_permission (ID,DOOR_CODE,PERSON_ID,STRATEGY_ID,CREATE_TIME,CREATE_USER) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", permission.Id);
                        cmd.Parameters.AddWithValue("@var1", permission.DoorCode);
                        cmd.Parameters.AddWithValue("@var2", permission.PersonId);
                        cmd.Parameters.AddWithValue("@var3", permission.StrategyId);
                        cmd.Parameters.AddWithValue("@var4", permission.CreateTime);
                        cmd.Parameters.AddWithValue("@var5", permission.CreateUser);
                        cmd.ExecuteNonQuery();
                    }

                    //bus_device表 
                    List<Device> deviceList = JsonConvert.DeserializeObject<List<Device>>(json["bus_device"].ToString());
                    foreach (Device device in deviceList)
                    {
                        string sql = "delete from bus_device where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", device.Id);
                        cmd.ExecuteNonQuery();

                        sql = "insert into bus_device (ID,DEV_CODE,DEV_NAME,DEV_IP,DEV_TYPE,DOOR_CODE,INOUT_TYPE,DESCRIPTION) values"
                            + "(@var0,@var1,@var2,@var3,@var4,@var5,@var6,@var7)";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@var0", device.Id);
                        cmd.Parameters.AddWithValue("@var1", device.DevCode);
                        cmd.Parameters.AddWithValue("@var2", device.DevName);
                        cmd.Parameters.AddWithValue("@var3", device.DevIp);
                        cmd.Parameters.AddWithValue("@var4", device.DevType);
                        cmd.Parameters.AddWithValue("@var5", device.DoorCode);
                        cmd.Parameters.AddWithValue("@var6", device.InoutType);
                        cmd.Parameters.AddWithValue("@var7", device.Description);
                        cmd.ExecuteNonQuery();
                    }

                }


                // 取消授权
                else if (data.Contains("deletePermission:"))
                {
                    data = data.Replace("deletePermission:", "");
                    data = data.Insert(7, "\"");
                    data = data.Insert(data.Length - 1, "\"");
                    JObject json = (JObject)JsonConvert.DeserializeObject(data);//或者JObject jo = JObject.Parse(jsonText);

                    string[] ids = json["ids"].ToString().Replace("[","").Replace("]","").Split(',');
                    for (int i = 0; i < ids.Length; i++)
                    {
                        string sql = "delete from acs_permission where ID=@ID";
                        cmd = new MySqlCommand(sql, DatabaseLocal.mySqlConnect);
                        cmd.Parameters.AddWithValue("@ID", ids[i]);
                        cmd.ExecuteNonQuery();
                    }
                }

                tx.Commit(); // 提交事务
                re = 0;
            }
            catch (MySqlException exSql)
            {
                re = -1;
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTableFromServer " + exSql.Message);
            }
            catch (Exception ex)
            {
                re = -1;
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "updateTableFromServer " + " EXCEPTION: " + ex.Message);

            }
            finally
            {
                tx.Dispose();
                cmd.Dispose();
            }

            return re;
        }

        public int clearTableFromOffline()
        {
            int re = -1;
            string tableName = "";
            string[] tableNames = { "sys_person", "iris_person_photo", "iris_data", "sys_dept" };
            MySqlTransaction tx = null;
            MySqlCommand cmd = new MySqlCommand();

            try
            {
                //绑定事务
                cmd.Transaction = tx;

                //清空表
                cmd = new MySqlCommand("truncate table sys_person", DatabaseLocal.mySqlConnect);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand("truncate table iris_person_photo", DatabaseLocal.mySqlConnect);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand("truncate table iris_data", DatabaseLocal.mySqlConnect);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand("truncate table sys_dept", DatabaseLocal.mySqlConnect);
                cmd.ExecuteNonQuery();

                //提交事务          
                tx.Commit();

                re = 0;
            }
            catch (Exception ex)
            {
                re = -1;
                tx.Rollback();
                LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "clearTableFromOffline " + tableName + " EXCEPTION: " + ex.Message);

            }
            finally
            {
                tx.Dispose();
                cmd.Dispose();
            }
            return re;
           
        }
       
    }
}