www-huanghe/modules/common/service/SqliteStorageService.js

angular.module('push')
  .factory('SqliteStorageService', function (UtilService, $q, ConfigService, UserService) {
    var db;
    var tableDefinde = {
      //数据库版本表
      dbversion: {
        localid: {type: 'text'},
        version: {type: 'text'}//版本号
      },
      //存放用户登录信息
      userinfo: {
        id: {type: 'integer'},
        password: {type: 'text'},
        mobile: {type: 'text'},
        updatetime: {type: 'text'},
        access_token: {type: 'text'},
        refresh_token: {type: 'text'},
        openid: {type: 'text'},
        unionid: {type: 'text'},
        type: {type: 'text'} //1：账号、2：微信、3：QQ
      },
      //用户操作记录表
      operation: {
        userid: {type: 'integer'},
        isedit: {type: 'integer'},//0：未操作，1：已操作
        updatetime: {type: 'text'}//操作时间
      },
      //分类（文件夹）表
      conninfofolder: {
        localid: {type: 'text', pk: true},//本地id
        id: {type: 'text'},
        localparentid: {type: 'text'},
        parentid: {type: 'text'},
        // ispublic: {type: 'integer'},//是否公开 1-非公开，2-公开'
        level: {type: 'integer'},
        infocount: {type: 'integer'},
        userid: {type: 'integer'},
        usercomefrom: {type: 'text'},
        name: {type: 'text'},
        createtime: {type: 'text'},
        status: {type: 'integer'},
        isCloud: {type: 'integer'},// 0本地1云共享
        synchrotype: {type: 'integer'},//记录状态 0:已同步,1:新增,2:修改,3:逻辑删除,4:物理删除
        updatetime: {type: 'text'},//修改时间
        counts: {type: 'integer'},
        moduleid: {type: 'integer'},//对应模块表 1：需求，2：成果，6：企业，8：人才，81：对接宝
        type: {type: 'integer'},//1-对接宝，2-资源库
        fixtype: {type: 'integer'},//1-公共库，2-私有库
        content: {type: 'text'},//文件夹简介
        favourcount: {type: 'integer'},//点赞数
        visitcount: {type: 'integer'},//浏览数
        commcount: {type: 'integer'}//评论数
      },
      //对接信息表
      conninfo: {
        localid: {type: 'text', pk: true},
        localclaid: {type: 'text'},
        id: {type: 'integer'},
        claid: {type: 'integer'},//归属文件夹id
        // ispublic: {type: 'integer'},//是否公开 1-非公开，2-公开'
        conntime: {type: 'text'},//对接时间
        title: {type: 'text'},//信息标题
        content: {type: 'text'},//信息想起
        createtime: {type: 'text'},//创建时间
        creator: {type: 'text'},//创建者
        creatorcomefrom: {type: 'text'},
        updatetime: {type: 'text'},
        status: {type: 'integer'},
        flag: {type: 'integer'},//是否标记
        synchrotype: {type: 'integer'},
        image: {type: 'text'},
        defaultFlag: {type: 'integer'},//是否属于系统发送信息：0-否；1-是
        remark: {type: 'text'},
        favourcount: {type: 'integer'},
        visitcount: {type: 'integer'},
        commcount: {type: 'integer'}
      },
      //对接信息-图片表
      conninfopicture: {
        localid: {type: 'text'},
        id: {type: 'integer'},
        infoid: {type: 'integer'},
        localinfoid: {type: 'text'},
        title: {type: 'text'},
        photo_name: {type: 'text'},
        original_name: {type: 'text'},
        source_name: {type: 'text'},
        source_size: {type: 'text'}
      },
      //对接信息-名片表
      conninfounit: {
        localid: {type: 'text', pk: true},
        localinfoid: {type: 'text'},
        id: {type: 'integer'},
        infoid: {type: 'integer'},
        synchrotype: {type: 'integer'},
        createtime: {type: 'text'},
        updatetime: {type: 'text'},
        categoryid: {type: 'integer'},
        categorydetail: {type: 'text'},
        address: {type: 'text'},
        itemkey: {type: 'text'},
        name: {type: 'text'},
        presentation: {type: 'text'},
        type: {type: 'integer'},
        creator: {type: 'text'},
        creatorcomefrom: {type: 'text'}
      },
      //对接信息-人员表
      conninfoperson: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'text'},
        contact: {type: 'text'},
        phone: {type: 'text'},
        categoryid: {type: 'integer'},
        email: {type: 'text'},
        rank: {type: 'text'},
        introduce: {type: 'text'}
      },
      //对接信息-主题表
      conninfotheme: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'text'},
        itemkey: {type: 'text'},
        title: {type: 'text'},
        content: {type: 'text'},
        type: {type: 'integer'}
      },
      //对接信息-产品表
      conninfoproduct: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'text'},
        productname: {type: 'text'},
        productdescription: {type: 'text'}
      },
      //对接信息- 产学研经历表
      conninfoiur: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'integer'},
        cooperateschool: {type: 'text'},
        cooperatedetail: {type: 'text'}
      },
      //对接信息- 重大科研项目表
      conninforesearch: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'integer'},
        productname: {type: 'text'},
        productyear: {type: 'text'}
      },
      //对接信息- 历年申报项目表
      yearproject: {
        localid: {type: 'text'},
        localuid: {type: 'text'},
        id: {type: 'integer'},
        uid: {type: 'integer'},
        infoid: {type: 'integer'},
        year: {type: 'text'},
        name: {type: 'text'},
        partners: {type: 'text'},
        type: {type: 'integer'},
        sector: {type: 'text'},
        money: {type: 'integer'},
        annualspan: {type: 'text'},
        isknot: {type: 'integer'},
        description: {type: 'text'},
        isindustrialization: {type: 'text'},
        createtime: {type: 'text'},
        creator: {type: 'integer'},
        creatorcomefrom: {type: 'text'},
        updatetime: {type: 'text'},
        updater: {type: 'integer'},
        updatercomefrom: {type: 'text'}


      },
      //需求,成果,企业,人才（混合）总表
      resource: {
        localid: {type: 'text', pk: true},
        localclaid: {type: 'text'},
        id: {type: 'text'}, //唯一标识，爬虫为url，其他为Mysql的表ID
        claid: {type: 'text'},
        synchrotype: {type: 'integer'},
        // ispublic: {type: 'integer'},//是否公开 1-非公开，2-公开'
        /*---------------------------------------*/
        organizationId: {type: 'integer'},//机构id
        publisher: {type: 'integer'},//发布者id
        isOpen: {type: 'integer'},//单选项：0完全可见/1企业可见/2院校可见/3仅管理人员可见
        openlevel: {type: 'integer'},//选项：1全网可见，2为具体节点
        creator: {type: 'integer'}, //创建者id
        creatorcomefrom: {type: 'text'},//创建者节点号
        creatorname: {type: 'text'},
        sitename: {type: 'text'},//节点名称
        logo: {type: 'text'},//logo图片/头像
        contactor: {type: 'text'},//联系人
        tel: {type: 'text'},//电话
        email: {type: 'text'},//邮箱
        orgName: {type: 'text'}, //发布机构名
        proficiencyName: {type: 'text'}, //技术成熟度中文名
        cperlist: {type: 'text'}, //合作模式中文名list
        cpername: {type: 'text'}, //合作模式中文名(私人库合作模式为单选)
        cperid: {type: 'integer'}, //合作模式id
        categoryid: {type: 'text'},//行业分类id （见category表）
        category: {type: 'text'},//行业分类
        categoryname: {type: 'text'},//行业分类
        //picturelist;//图片列表List<ResourcePicture>
        delpiclist: {type: 'text'}, //要删除的图片id列表（带参用，逗号分隔）
        /*----------------------------------------*/
        problemdescription: {type: 'text'},//问题描述
        exceptdescription: {type: 'text'},//期望效果
        exceptcooperator: {type: 'text'},//意向合作单位
        scoreaAuto: {type: 'integer'},//[算]平台系统计算的评分
        scoretotal: {type: 'text'},//项目信息的评分
        evaluateStatus: {type: 'integer'},//评价状态。0、没有评价 1、已经评价。
        comefrom: {type: 'text'},//来源节点
        techtype: {type: 'integer'},//需求类别（字典表）
        level: {type: 'text'},//评级时用的等级
        organizationName: {type: 'text'},//游客发布信息的机构名称
        imgname: {type: 'text'},//项目配图文件名
        score: {type: 'integer'},//初始分值
        ishaspatent: {type: 'text'}, //是否有专利

        //企业
        enterprisescale: {type: 'integer'}, //企业规模
        enterprisescalename: {type: 'text'},//企业规模名称
        enterprisetype: {type: 'integer'}, //企业类型
        enterprisetypename: {type: 'text'},//企业类型名称
        star_type: {type: 'integer'},
        star_type_name: {type: 'text'},//高企认定
        region: {type: 'text'}, //企业地址（省市）
        code: {type: 'text'},//组织机构代码
        org_name: {type: 'text'},//机构名称
        bigcategories: {type: 'text'},//行业门类
        bigcategoriesname: {type: 'text'},//行业名称
        categories: {type: 'text'},//行业大类
        tag: {type: 'text'},//机构标签
        about: {type: 'text'},//机构简介
        url: {type: 'text'},//网址
        qq: {type: 'text'},//QQ号
        contact: {type: 'text'},//联系人
        phone: {type: 'text'},//手机号码
        faxNo: {type: 'text'},//传真号码
        checkStatus: {type: 'integer'},//状态：0-未认证，1-已认证，2-优质企业
        deleteFlag: {type: 'integer'},//是否可用：0-可用，1-不可用
        createTime: {type: 'text'},//创建时间
        updateTime: {type: 'text'},//更新时间
        updater: {type: 'integer'},//更新者
        scale: {type: 'integer'},//企业规模
        //人才
        academicdegree: {type: 'integer'}, //学位
        academicdegreename: {type: 'text'}, //学位名称
        positionaltitles: {type: 'integer'}, //职称
        sex: {type: 'integer'}, //性别：0-未选择，1-男，2-女code
        birthday: {type: 'text'}, //出生年月
        //企业库详情页
        category_zh: {type: 'text'}, //领域名称
        itemkey: {type: 'text'}, //企业,高校类别
        categoryCode: {type: 'text'},//行业分类的代码（A,B,C,D...）
        //人才库详情
        domain: {type: 'text'}, //研究领域
        schName: {type: 'text'}, //所属学校名称
        orgId: {type: 'integer'}, //所属高校id
        oid: {type: 'integer'},//关联高校id
        photo: {type: 'text'},//人才头像
        name: {type: 'text'},//人才姓名
        rank: {type: 'text'},//职称
        fetchurl: {type: 'text'},//爬取时间
        //resource内容
        content: {type: 'text'}, //内容，用于检索使用
        timestamp: {type: 'text'}, //创建时间
        source: {type: 'text'}, //来源
        clickthrough: {type: 'integer'},  //点击数  T:170714 By:cc 新版本该字段为创建者id
        compositescore: {type: 'text'}, //评分
        status: {type: 'integer'},  // 状态
        deadline: {type: 'text'}, // 项目有效期
        capitalScale: {type: 'text'}, // 投入预算
        address: {type: 'text'}, // 地址
        school: {type: 'text'}, // 学校
        isLeaf: {type: 'text'}, //是不是详情页
        summary: {type: 'text'}, //摘要
        title: {type: 'text'}, //标题
        publishTime: {type: 'text'},  //发布时间
        type: {type: 'integer'},//1：需求，2：成果，6：企业，8：人才
        proficiency: {type: 'integer'}, //技术成熟度
        isipr: {type: 'integer'}, //是否有知识产权1有/0无
        cooperationmodel: {type: 'text'},//合作模式
        visitcount: {type: 'integer'}, //浏览数
        commcount: {type: 'integer'}, //评论数
        favourcount: {type: 'integer'}, //点赞数
        fixtype: {type: 'integer'} //资源库共有私有标识：1-公共库，2-私有库
      }
    };
    var sqlFunction = {
      //若本地数据修改，则刷新operation中isedit为1
      updateEditFlg: function (flg) {
        var qu_sql = "select * from operation where userid = " + UserService.id;
        sqlFunction.queryData(qu_sql).then(function (result) {
          if (result.length > 0) {
            var updatedate = UtilService.formatDate().formattime + "";
            var up_sql = "update operation set isedit = " + flg + ",updatetime = '" + updatedate + "' where userid = " + UserService.id;
            sqlFunction.queryData(up_sql).then(function () {
              console.log("更新操作标志成功")
            }, function (err) {
              console.log(err);
            });
          } else {
            var insertdate = UtilService.formatDate();
            var opera = {
              userid: UserService.id,
              isedit: flg,
              updatetime: insertdate.formattime
            };
            sqlFunction.insertSingleData("operation", opera).then(function () {
              console.log("插入操作标志成功")
            }, function (err) {
              console.log(err);
            });
          }
        }, function (err) {
          console.log(err);
        });
      },
      //查询数据
      queryData: function (sql) {
        var dataList = [];
        var deferred = $q.defer();
        db.executeSql(sql, [], function (resultSet) {
          var len = resultSet.rows.length;
          if (len > 0) {
            for (var i = 0; i < len; i++) {
              dataList.push(resultSet.rows.item(i));
            }
          }
          deferred.resolve(dataList);
        }, function (error) {
          deferred.reject(error)
        });
        return deferred.promise;
      },
      //查询数据
      executeSqlList: function (sqllist) {
        var dataList = [];
        var deferred = $q.defer();
        var execSql = function (index) {
          if (index < sqllist.length) {
            db.executeSql(sqllist[index], [], function (resultSet) {
              var len = resultSet.rows.length;
              if (len > 0) {
                for (var i = 0; i < len; i++) {
                  dataList.push(resultSet.rows.item(i));
                }
              }
              execSql(index + 1);
            }, function (error) {
              deferred.reject(error)
            });
          } else {
            deferred.resolve(dataList);
          }
        };
        execSql(0);
        return deferred.promise;
      },
      //插入单个数据
      insertSingleData: function (tablename, obj) {
        var deferred = $q.defer();
        var keys = "";
        var vals = [];
        var q_str = "";
        for (var attr in obj) {
          if (tableDefinde[tablename][attr]) {
            if (typeof(obj[attr]) == 'object' && obj[attr] != null) {
              vals.push(angular.toJson(obj[attr]));
              keys += attr + ',';
              q_str += "?" + ",";
            } else if (obj[attr] != null && obj[attr] != undefined) {
              vals.push(obj[attr]);
              keys += attr + ',';
              q_str += "?" + ",";
            }
          }
        }
        keys = keys.substring(0, keys.length - 1);
        q_str = q_str.substring(0, q_str.length - 1);
        var query = "insert into " + tablename + "(" + keys + ") values(" + q_str + ")";
        db.executeSql(query, vals, function (resultSet) {
          var dataList = [];
          var len = resultSet.rows.length;
          if (len > 0) {
            for (var i = 0; i < len; i++) {
              dataList.push(resultSet.rows.item(i));
            }
          }
          // console.log("插入单个数据成功");
          deferred.resolve(dataList);
        }, function (error) {
          console.log(error);
        });
        return deferred.promise;
      },
      //更新单个数据
      updateSingleData: function (tablename, obj) {
        var deferred = $q.defer();
        var keys = "";
        var vals = [];
        for (var attr in obj) {
          if (tableDefinde[tablename][attr] && attr != 'localid') {
            if (typeof(obj[attr]) == 'object' && obj[attr] != null) {
              vals.push(angular.toJson(obj[attr]));
              keys += attr + '=?,';
            } else if (obj[attr] != null && obj[attr] != undefined) {
              vals.push(obj[attr]);
              keys += attr + '=?,';
            }
          }
        }
        keys = keys.substring(0, keys.length - 1);
        var query = "update " + tablename + " set " + keys + " where localid = ?";
        vals.push(obj.localid);
        db.executeSql(query, vals, function (resultSet) {
          var dataList = [];
          var len = resultSet.rows.length;
          if (len > 0) {
            for (var i = 0; i < len; i++) {
              dataList.push(resultSet.rows.item(i));
            }
          }
          // console.log("更新单个数据成果");
          deferred.resolve(dataList);
        }, function (err) {
          console.log(err);
          deferred.reject(err);
        });
        return deferred.promise;
      },
      //删除数据
      deleteData: function (sql) {
        var deferred = $q.defer();
        db.executeSql(sql, [], function (res) {
          // console.log("删除数据成功");
          deferred.resolve(res);
        }, function (err) {
          console.log(err);
          deferred.reject(err);
        });
        return deferred.promise;
      },
      //批量插入数据
      insertBatchData: function (tablename, datalist) {
        var deferred = $q.defer();
        var len = datalist.length;
        var insertArray = [];
        for (var i = 0; i < len; i++) {
          var obj = datalist[i];
          var keys = "";
          var vals = [];
          var q_str = "";
          for (var attr in obj) {
            if (tableDefinde[tablename][attr]) {
              if (typeof(obj[attr]) == 'object' && obj[attr] != null) {
                vals.push(angular.toJson(obj[attr]));
                keys += attr + ',';
                q_str += "?" + ",";
              } else if (obj[attr] != null && obj[attr] != undefined) {
                vals.push(obj[attr]);
                keys += attr + ',';
                q_str += "?" + ",";
              }
            }
          }
          keys = keys.substring(0, keys.length - 1);
          q_str = q_str.substring(0, q_str.length - 1);
          var query = "insert into " + tablename + "(" + keys + ") values(" + q_str + ")";
          var newarray = [query, vals];
          insertArray.push(newarray);
        }
        db.sqlBatch(insertArray, function () {
          // console.log(tablename + "--数据批量插入成功");
          deferred.resolve();
        }, function (err) {
          deferred.reject(err);
          // console.log(err);
        });
        return deferred.promise;
      },
      //批量更新数据
      updateBatchData: function (tablename, datalist) {
        var deferred = $q.defer();
        var len = datalist.length;
        var updateArray = [];
        for (var i = 0; i < len; i++) {
          var obj = datalist[i];
          var keys = "";
          var vals = [];
          for (var attr in obj) {
            if (tableDefinde[tablename][attr] && attr != 'localid') {
              if (typeof(obj[attr]) == 'object' && obj[attr] != null) {
                vals.push(angular.toJson(obj[attr]));
                keys += attr + '=?,';
              } else if (obj[attr] != null && obj[attr] != undefined) {
                vals.push(obj[attr]);
                keys += attr + '=?,';
              }
            }
          }
          keys = keys.substring(0, keys.length - 1);
          var query = "update " + tablename + " set " + keys;
          query += " where localid = ?";
          vals.push(obj.localid);
          updateArray.push([query, vals]);
        }
        db.sqlBatch(updateArray, function () {
          // console.log(tablename + "--数据批量更新成功");
          deferred.resolve();
        }, function (err) {
          console.log(err);
          deferred.reject();
        });
        return deferred.promise;
      },
      //批量删除数据
      deleteBatchData: function (tablename, datalist) {
        var deferred = $q.defer();
        var deleteArray = [];
        for (var i = 0; i < datalist.length; i++) {
          var localid = datalist[i].localid;
          var vals = [];
          var query = "delete from " + tablename + " where localid = ?";
          vals.push(localid);
          deleteArray.push([query, vals]);
        }
        // console.log(deleteArray);
        db.sqlBatch(deleteArray, function (res) {
          // console.log(tablename + "--数据批量删除成功");
          deferred.resolve();
        }, function (err) {
          console.log(err);
          deferred.reject(err);
        });
        return deferred.promise;
      },
      //清除单个表
      dropTable: function (tablename) {
        var sql = "drop table " + tablename;
        var deferred = $q.defer();
        db.executeSql(sql, [], function (resultSet) {
          // console.log(tablename + "--清除数据成功!");
          deferred.resolve(resultSet);
        }, function (error) {
          deferred.reject(error)
        });
        return deferred.promise;
      },
      //清除表(清除所有数据)
      dropAllTable: function () {
        var dropTab = [];
        for (var table in tableDefinde) {
          var sql = "drop table " + table;
          dropTab.push(sql);
        }
        db.sqlBatch(dropTab, function () {
          console.log("所有数据清除成功!");
          //清表后重新创建
          sqlFunction.initDBTable();
        }, function (err) {
          console.log(err);
          sqlFunction.initDBTable();
        })
      },
      //删除用户数据
      deletDataOfUser: function () {
        var deleteTable = [];
        var tablenames = ["userinfo", "operation", "conninfofolder", "conninfo", "conninfopicture", "conninfounit", "conninfoperson",
          "conninfotheme", "resource", "patent", "person", "product", "info_doc"];
        for (var i = 0; i < tablenames.length; i++) {
          var delsql = "delete from " + tablenames[i];
          deleteTable.push(delsql);
        }
        sqlFunction.executeSqlList(deleteTable);
      },
      //检查数据库版本
      checkVersion: function () {
        // console.log("检查本地版本");
        var version = {};
        var querysql = "select localid,version from dbversion";
        db.executeSql(querysql, [], function (resultSet) {
          version = resultSet.rows.item(0);
          // console.log(version);
          if (!UtilService.isDefined(version)) {
            //第一次使用数据库，更新版本表
            version = {localid: ConfigService.versionno, version: ConfigService.versionno};
            sqlFunction.insertSingleData("dbversion", version);
          } else if (version.version != ConfigService.versionno) {
            //数据库结构更新
            /*var sqlList = [
              "alter table conninfounit add column categorydetail text"
            ];
            sqlFunction.executeSqlList(sqlList).then(function () {
              console.log("添加字段成功");
              //旧数据处理
              /!*var sqlList2 = [
               "update conninfounit set categorydetail = '' where categorydetail is null"
               ];
               sqlFunction.executeSqlList(sqlList2);*!/
              //数据库版本更新
              version.version = ConfigService.versionno;
              sqlFunction.updateSingleData("dbversion", version);
            }, function (err) {
              console.log(err);
            });*/
            version.version = ConfigService.versionno;
            sqlFunction.updateSingleData("dbversion", version);
          }
        }, function (error) {
        });
      },
      //初始化数据库表结构
      initDBTable: function () {
        var tables = [];
        //通过tableDefinde，拼接生成建表语句。
        for (var tableName in tableDefinde) {
          var table = tableDefinde[tableName];
          var sql = "CREATE TABLE IF NOT EXISTS " + tableName + "(";
          for (var fieldName in table) {
            sql += fieldName + " " + table[fieldName].type;
            if (table[fieldName].pk == true) {
              sql += " primary key";
            }
            /*if (table[fieldName].autoincrement == true) {
             sql += " autoincrement";
             }*/
            sql += ",";
          }
          sql = sql.slice(0, sql.length - 1);//去掉最后一个逗号
          sql += ")";
          tables.push(sql);
        }
        //$cordovaSQLite.execute(db, tables);
        //sqlFunction.checkVersion();
        db.sqlBatch(tables, function () {
          // console.log("初始化表结构");
          //设置版本号
          sqlFunction.checkVersion();
        }, function (err) {
          console.log(err);
        });
      },
      //初始化数据库
      initDataBase: function () {
        db = window.sqlitePlugin.openDatabase({
          name: 'pushkeji.db',
          location: 'default',
          androidDatabaseImplementation: 2,
          androidLockWorkaround: 1
        });
        sqlFunction.initDBTable();
        // db = window.$cordovaSQLite.openDB({
        //   name: 'pushkeji.db',
        //   location: 'default',
        //   androidDatabaseImplementation: 2,
        //   androidLockWorkaround: 1
        // });
        // sqlFunction.initDBTable();
      }
      //   if (window.cordova) {
      //     db = $cordovaSQLite.openDB({
      //       name: 'pushkeji.db',
      //       location: 1
      //     });
      //   } else {
      //     db = window.openDatabase("pushkeji.db", '1', 'pushkeji', 1024 * 1024 * 100); // browser
      //   }
      //   sqlFunction.initDBTable();
      //   $cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS guidepage(firstname text, lastname text)");
      //   $rootScope.db = db;
      // }
    };
    return sqlFunction;
  });