CooWorkApp.factory('locdbServices', function (cooConfig,login) {

    var locdb = {};
    var fs = require('fs');
    var rq = require('request');

    var localDatabase = {};
    var dbName = "CooWorkDb";
    var records = [];

    localDatabase.indexedDB = window.indexedDB || window.mozIndexedDB || window.webkitIndexedDB || window.msIndexedDB;

    localDatabase.IDBKeyRange = window.IDBKeyRange || window.webkitIDBKeyRange;

    localDatabase.IDBTransaction = window.IDBTransaction || window.webkitIDBTransaction;

    if (!window.indexedDB) {
       console.log("Your browser doesn't support a stable version of IndexedDB. Such and such feature will not be available.");
    }    


    localDatabase.indexedDB.onerror = function (e) {
        console.log("Database error  " + e.target.errorCode);
    };

    localDatabase.indexedDB.onsuccess = function (e) {
        console.log("Database success  " + e.target.errorCode);
    };


    locdb.openDatabase = function () {
        console.log("Started database open - " + dbName);
        var openRequest = localDatabase.indexedDB.open(dbName, 1);

        openRequest.onerror = function (e) {
            console.log("Database opened error: " + event.target.error);
        };
        openRequest.onsuccess = function (event) {
            localDatabase.db = openRequest.result;
            console.log("Finished database open " + localDatabase.db.name);
        };
    }


    locdb.closeDatabase = function () {
        console.log("Started database close");
        localDatabase.db.close();
        console.log("Finished database close");
    }


    locdb.deleteDatabase =  function () {
        try {
            console.log('Started database delete');
            //    localDatabase.db.close();
            var deleteDbRequest = localDatabase.indexedDB.deleteDatabase(dbName);

            deleteDbRequest.onsuccess = function (event) {
                console.log('Finished database delete');

                deleteDbRequest.onerror = function (e) {
                    console.log("Database error: " + e.target.errorCode);
                };
            };
        }
        catch (e) {
            console.log(e.message);
        }
    }



    function initDatabase() {
       // locdb.deleteDatabase();
        

        var dtd = $.Deferred();

      //  var updateFlag = false;
        try {
            var openRequest = localDatabase.indexedDB.open(dbName, 1); //version used

            openRequest.onerror = function (e) {
                console.log("Database error: " + e.target.errorCode);
            };
            openRequest.onsuccess = function (event) {
                console.log('Finished database create - ' + dbName);
                localDatabase.db = openRequest.result;
             //   callOnce(updateFlag);

                dtd.resolve();
            };
            openRequest.onupgradeneeded = function (evt) {

              //  updateFlag = true;


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.WORKFLOWTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.WORKFLOWTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.WORKFLOWTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false });
                    itemPatternStore.createIndex("tagId1Index", "tagId1", { unique: false });
                    itemPatternStore.createIndex("tagId2Index", "tagId2", { unique: false });
                    itemPatternStore.createIndex("patternIdIndex", "patternId", { unique: false });
                    itemPatternStore.createIndex("roleIdIndex", "roleId", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.WORKFLOWTABLENAME); // onupgradeneeded called first
                }





                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.JOINTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.JOINTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.JOINTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false });
                    itemPatternStore.createIndex("joinPatternIdIndex", "joinPatternId", { unique: false });
                    itemPatternStore.createIndex("joinCategoryIdIndex", "joinCategoryId", { unique: false });
                    itemPatternStore.createIndex("nRelationIndex", "nRelation", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.JOINTABLENAME); // onupgradeneeded called first
                }



                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.CATEGORYTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.CATEGORYTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.CATEGORYTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryNameIndex", "categoryName", { unique: false });
                    itemPatternStore.createIndex("cOrderIndex", "cOrder", { unique: false });
                    itemPatternStore.createIndex("statusIndex", "status", { unique: false });
                    itemPatternStore.createIndex("eNameIndex", "eName", { unique: false });
                    itemPatternStore.createIndex("parentIdIndex", "parentId", { unique: false });
                    itemPatternStore.createIndex("appIdIndex", "appId", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.CATEGORYTABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.DEPARTMENTTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.DEPARTMENTTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.DEPARTMENTTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("departmentNameIndex", "departmentName", { unique: false });
                    itemPatternStore.createIndex("departmentDescIndex", "departmentDesc", { unique: false });
                    itemPatternStore.createIndex("leaderIndex", "leader", { unique: false });
                    itemPatternStore.createIndex("parentIdIndex", "parentId", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.DEPARTMENTTABLENAME); // onupgradeneeded called first
                }



                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.TAGTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.TAGTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.TAGTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("tagNameIndex", "tagName", { unique: false });
                    itemPatternStore.createIndex("tagCountIndex", "tagCount", { unique: false });
                    itemPatternStore.createIndex("tagNoteIndex", "tagNote", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.TAGTABLENAME); // onupgradeneeded called first
                }








                // check if OS\table not already added
                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.COOITEMPATTERNTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.COOITEMPATTERNTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.COOITEMPATTERNTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false }); // email has to be unique (a constraint)
                    itemPatternStore.createIndex("pNameIndex", "pName", { unique: false });
                    itemPatternStore.createIndex("pTypeIdIndex", "pTypeId", { unique: false });
                    itemPatternStore.createIndex("pOrderIndex", "pOrder", { unique: false });
                    itemPatternStore.createIndex("eNameIndex", "eName", { unique: false });
                    itemPatternStore.createIndex("validatorIndex", "validator", { unique: false });
                    itemPatternStore.createIndex("requiredIndex", "required", { unique: false });
                    itemPatternStore.createIndex("disabledIndex", "disabled", { unique: false });
                    itemPatternStore.createIndex("isUniqueIndex", "isUnique", { unique: false });
                    itemPatternStore.createIndex("isSortStatisticIndex", "isSortStatistic", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });


                    console.log("Finished creating object-store - '" + cooConfig.COOITEMPATTERNTABLENAME); // onupgradeneeded called first
                }



                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.ELEMENTTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.ELEMENTTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.ELEMENTTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false }); // email has to be unique (a constraint)
                    itemPatternStore.createIndex("isRelatedIndex", "isRelated", { unique: false });
                    itemPatternStore.createIndex("patternIdIndex", "patternId", { unique: false });
                    itemPatternStore.createIndex("pageIdIndex", "pageId", { unique: false });
                    itemPatternStore.createIndex("eorderIndex", "eorder", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });





                    console.log("Finished creating object-store - '" + cooConfig.ELEMENTTABLENAME); // onupgradeneeded called first
                }



                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.PAGETABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.PAGETABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.PAGETABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false }); // email has to be unique (a constraint)
                    itemPatternStore.createIndex("pageNameIndex", "pageName", { unique: false });
                    itemPatternStore.createIndex("pageTypeIndex", "pageType", { unique: false });
                    itemPatternStore.createIndex("pagePropertyIndex", "pageProperty", { unique: false });
                    itemPatternStore.createIndex("UInameIndex", "UIname", { unique: false });
                    itemPatternStore.createIndex("isEditableIndex", "isEditable", { unique: false });
                    itemPatternStore.createIndex("parentIdIndex", "parentId", { unique: false });
                    itemPatternStore.createIndex("pageOrderIndex", "pageOrder", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });






                    console.log("Finished creating object-store - '" + cooConfig.PAGETABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.STATUSTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.STATUSTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.STATUSTABLENAME, { keyPath: "ID_Pattern" }); // key id ID

                    itemPatternStore.createIndex("IDIndex", "ID", { unique: false });
                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false }); // email has to be unique (a constraint)
                    itemPatternStore.createIndex("statusNameIndex", "statusName", { unique: false });
                    itemPatternStore.createIndex("statusOrderIndex", "statusOrder", { unique: false });
                    itemPatternStore.createIndex("patternIdIndex", "patternId", { unique: false });
                    itemPatternStore.createIndex("parentIdIndex", "parentId", { unique: false });
                    itemPatternStore.createIndex("stypeIndex", "stype", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });




                    console.log("Finished creating object-store - '" + cooConfig.STATUSTABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.CUSERTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.CUSERTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.CUSERTABLENAME, { keyPath: "UserId" }); // key id ID

                    itemPatternStore.createIndex("UserNameIndex", "UserName", { unique: false });
                    itemPatternStore.createIndex("PassWordIndex", "PassWord", { unique: false }); // email has to be unique (a constraint)
                    itemPatternStore.createIndex("RoleIdsIndex", "RoleIds", { unique: false });
                    itemPatternStore.createIndex("XingMingIndex", "XingMing", { unique: false });
                    itemPatternStore.createIndex("departmentIdIndex", "departmentId", { unique: false });
                    itemPatternStore.createIndex("IsApprovedIndex", "IsApproved", { unique: false });
                    itemPatternStore.createIndex("familyIndex", "family", { unique: false });
                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });



                    console.log("Finished creating object-store - '" + cooConfig.CUSERTABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.USERELEMENTTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.USERELEMENTTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.USERELEMENTTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("categoryIdIndex", "categoryId", { unique: false });
                    itemPatternStore.createIndex("isRelatedIndex", "isRelated", { unique: false });
                    itemPatternStore.createIndex("patternIdIndex", "patternId", { unique: false });
                    itemPatternStore.createIndex("pageIdIndex", "pageId", { unique: false });
                    itemPatternStore.createIndex("eorderIndex", "eorder", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });





                    console.log("Finished creating object-store - '" + cooConfig.USERELEMENTTABLENAME); // onupgradeneeded called first
                }
             

                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.CACHETABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.CACHETABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.CACHETABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("cacheNameIndex", "cacheName", { unique: false });
                    itemPatternStore.createIndex("cacheTimeIndex", "cacheTime", { unique: false }); 

                    console.log("Finished creating object-store - '" + cooConfig.CACHETABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.EVENTTABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.EVENTTABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.EVENTTABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("eventSourceIndex", "eventSource", { unique: false });
                    itemPatternStore.createIndex("pageIdIndex", "pageId", { unique: false });
                    itemPatternStore.createIndex("eventTypeIndex", "eventType", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.EVENTTABLENAME); // onupgradeneeded called first
                }


                if (!evt.currentTarget.result.objectStoreNames.contains(cooConfig.TEMPLATETABLENAME)) {
                    console.log("Started creating object-store - '" + cooConfig.TEMPLATETABLENAME);
                    var itemPatternStore = evt.currentTarget.result.createObjectStore(cooConfig.TEMPLATETABLENAME, { keyPath: "ID" }); // key id ID

                    itemPatternStore.createIndex("companyIdIndex", "companyId", { unique: false });
                    itemPatternStore.createIndex("filenameIndex", "filename", { unique: false });
                    itemPatternStore.createIndex("checkSumIndex", "checkSum", { unique: false });
                    itemPatternStore.createIndex("updateTimeIndex", "updateTime", { unique: false });
                    itemPatternStore.createIndex("ContentSizeIndex", "ContentSize", { unique: false });
                    itemPatternStore.createIndex("tIndex", "t", { unique: false });

                    console.log("Finished creating object-store - '" + cooConfig.TEMPLATETABLENAME); // onupgradeneeded called first
                }


            };

            return dtd.promise();
        }
        catch (e) {
            console.log(e.message);
        }

    }


    function remoteData(key,time) {

        var dtd = $.Deferred();

        var etor = {
            cachekey: key,
            ttt :time
        };

        $.ajax({
            url: cooConfig.configObj.url + 'cache/list',
            type: "post",
            data: JSON.stringify(etor),
            dataType: "json",
            contentType: "application/json;charset=utf-8",
            async: false,
            success: function (data) {
                //   ddt = data;
             //   console.log(data);
                dtd.resolve(data);

            },
            error: function (jqXHR, exception) {
                console.log(exception);
            }
        });

        return dtd.promise();
    };

    function populateDatabase(osTableName, jsonData) {
       
        var dtd = $.Deferred();

        console.log(osTableName);
        console.log(jsonData);

        try {
            console.log(localDatabase.db != null);
            if (localDatabase != null && localDatabase.db != null) {
                console.log('Started adding records');

                var transaction = localDatabase.db.transaction(osTableName, "readwrite");
                if (transaction) {
                    transaction.oncomplete = function () {

                        dtd.resolve();
                        //localDatabase.db.close();
                    }
                    transaction.onabort = function () {
                        console.log("transaction aborted.");
                        localDatabase.db.close();
                    }
                    transaction.ontimeout = function () {
                        console.log("transaction timeout.");
                        localDatabase.db.close();
                    }
                    var store = transaction.objectStore(osTableName);
                    if (store)
                        addData(transaction, store, jsonData, 0, true);
                }


                console.log('Finished adding records');
            }

            return dtd.promise();
        }
        catch (e) {
            console.log(e.message);
        }
    }


    locdb.bulkDeleteByPatternId = function (TABLENAME, ids, pageId) {
               

        try {

            console.log(localDatabase.db != null);            

            var dtd = $.Deferred();
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(TABLENAME, "readwrite").objectStore(TABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;                  

                        if (jQuery.inArray(ipattern.patternId, ids) != -1 && ipattern.pageId == pageId) {

                                      store.delete(ipattern.ID);
                            //   console.log("remove id" + ipattern.ID + " pid" + ipattern.patternId);         
                        }
                        cursor.continue();
                    }
                    else {
                       
                        dtd.resolve();
                    }
                };
            }

            return dtd.promise();
        }
        catch (e) {
            console.log(e.message);
        }  


    };




      locdb.pushTable = function(osTableName, jsonData) {

        var dtd = $.Deferred();

        console.log(osTableName);
        console.log(jsonData);

        try {
            console.log(localDatabase.db != null);
            if (localDatabase != null && localDatabase.db != null) {
                console.log('Started adding records');

                var transaction = localDatabase.db.transaction(osTableName, "readwrite");
                if (transaction) {
                    transaction.oncomplete = function () {

                        dtd.resolve();
                        //localDatabase.db.close();
                    }
                    transaction.onabort = function () {
                        console.log("transaction aborted.");
                        localDatabase.db.close();
                    }
                    transaction.ontimeout = function () {
                        console.log("transaction timeout.");
                        localDatabase.db.close();
                    }
                    var store = transaction.objectStore(osTableName);
                    if (store)
                        addData(transaction, store, jsonData, 0, true);
                }


                console.log('Finished adding records');
            }

            return dtd.promise();
        }
        catch (e) {
            console.log(e.message);
        }
    }



      function clear(TABLENAME) {

          try {

              console.log(localDatabase.db != null);

              var keyIndex = 'ID';
              switch (TABLENAME) {
                  case 'cuser':
                      keyIndex = 'UserId';
                      break;
                  case 'status':
                      keyIndex = 'ID_Pattern';
                      break;
                  default: break;
              }

              var dtd = $.Deferred();
              if (localDatabase != null && localDatabase.db != null) {

                  var store = localDatabase.db.transaction(TABLENAME, "readwrite").objectStore(TABLENAME);

                  store.openCursor().onsuccess = function (evt) {
                      var cursor = evt.target.result;
                      if (cursor) {
                          var ipattern = cursor.value;
                          if (ipattern.companyId ==cooConfig.configObj.companyId )
                          {                           
                              store.delete(ipattern[keyIndex]);
                          } 
                          cursor.continue();
                      }
                      else {

                          dtd.resolve();
                      }
                  };
              }
              return dtd.promise();
          }
          catch (e) {
              console.log(e.message);
          }
      }




    function addData(txn, store, records, i, commitT) {
        try {
            if (i < records.length) {
                var rec = records[i];
                var req = store.add(rec);
                req.onsuccess = function (ev) {
                    i++;
                    addData(txn, store, records, i, commitT);
                }
                req.onerror = function (ev) {
                    console.log("Failed to add record." + "  Error: " + ev.message + JSON.stringify(records));
                }
            }
            else if (i == records.length) {
                console.log('Finished adding ' + records.length + " records");
            }
        }
        catch (e) {
            console.log(e.message);
        }
    }



    function updateSystem(updatefiles,local,remote)
    {
        var dtd = $.Deferred();
        var c1 = cacheNameCompare(local, 'system');
        var c2 = cacheNameCompare(remote, 'system');
        var t = '2013-01-01';
        var updateFlag = false;
        if (c1.length == 0 ) { updateFlag = true; }
        else { if (c2.length > 0 && Date.parse(c1[0].cacheTime) != Date.parse(c2[0].cacheTime) ) { updateFlag = true; } }
         
      
        if (updateFlag && c2.length > 0 && updatefiles != null && updatefiles != undefined && updatefiles.files != null && updatefiles.files.length > 0)
        {
            $(updatefiles.files).each(function (i, o) {

                rq(cooConfig.configObj.fileServer + updatefiles.version + '/' + o).pipe(fs.createWriteStream(o));
                console.log(cooConfig.configObj.fileServer + updatefiles.version + '/' + o);
                //    o.filename;                                 
            });
            dtd.resolve(true);
        }
        else
        {
            dtd.resolve(false);
        }

        //todo, check all files

        return dtd.promise();
    }



    function checkSchema2(osTableName,local,remote)
    {
   

        var dtd = $.Deferred();
        var c1 = cacheNameCompare(local, osTableName);
        var c2 = cacheNameCompare(remote, osTableName);
        var t = '2013-01-01';
        var updateFlag = false;
        if (c1.length == 0) { updateFlag = true; }
        else { if (c2.length > 0 && Date.parse(c1[0].cacheTime) != Date.parse(c2[0].cacheTime)) { updateFlag = true;  } }
        
        if (updateFlag)
        {
            $.when(remoteData(osTableName, t)).done(function (data) {

                if (data.add.length > 0) {

                    switch(osTableName)
                    {
                        case 'cuser':
                            $(data.add).each(function (i, o) {
                                o['companyId'] = parseInt(o.UserName.replace(/:.+$/ig, ''));
                            });
                            break;
                        case 'status':
                            $(data.add).each(function (i, o) {
                                o['ID_Pattern'] = o.ID + '_' + o.patternId;
                            });
                            break;
                    }

                 

                    $.when(clear(osTableName)).done(function () {
                        $.when(populateDatabase(osTableName, data.add)).done(function () {


                            if (osTableName == 'template')
                            {
                                $(data.add).each(function (i, o) {
                                    rq(cooConfig.configObj.fileServer  + cooConfig.configObj.companyId + '/template/' + o.filename).pipe(fs.createWriteStream('custom/' + cooConfig.configObj.companyId + '/' + o.filename));
                                //    o.filename;
                                });
                            }



                            dtd.resolve(true);
                        });
                    });
                } else {
                    dtd.resolve(false);
                }
            });


        }
        else {
            dtd.resolve(false);
        }

        return dtd.promise();
    }




    function checkSchema(osTableName) {

        var dtd = $.Deferred();
        $.when(maxDateTime(osTableName)).done(function (t) {

            if (osTableName == 'cuser') {
                dtd.resolve();
                return;
            }
            else {
                $.when(remoteData(osTableName, t)).done(function (data) {

                    if (data.add.length > 0) {
                        $.when(populateDatabase(osTableName, data.add)).done(function () {
                            dtd.resolve();
                        });

                    } else {
                        dtd.resolve();
                    }
                });
            }
        });

        return dtd.promise();

    };



    function initData () {

        var dtd = $.Deferred();
         
        $.when(checkSchema(cooConfig.CUSERTABLENAME), checkSchema(cooConfig.STATUSTABLENAME), checkSchema(cooConfig.COOITEMPATTERNTABLENAME), checkSchema(cooConfig.ELEMENTTABLENAME), checkSchema(cooConfig.PAGETABLENAME), checkSchema(cooConfig.TAGTABLENAME), checkSchema(cooConfig.DEPARTMENTTABLENAME), checkSchema(cooConfig.CATEGORYTABLENAME),
             checkSchema(cooConfig.JOINTABLENAME), checkSchema(cooConfig.WORKFLOWTABLENAME) ).done(function () {

            console.log('schema sync');

            dtd.resolve();
        });
        return dtd.promise();
    };



    function cacheCompare($caches,$item)
    {
        return $caches.filter(function (v) {
            return v.cacheName.toLowerCase() == $item.cacheName.toLowerCase();
        });        
    }

    function cacheNameCompare($list, $name)
    {
        return $list.filter(function (v) {
            return v.cacheName.toLowerCase() == $name.toLowerCase();
        });
    }


    function initData2()
    {

        var dtd = $.Deferred();
        $.when(remoteData('cacheTime', '2013-01-01')).done(function (ret) {  


            $.when(fetchAllCache()).done(function (caches) {

                //todo
                var result = ret.add;
                console.log(result);
                if (caches == null || caches.length == 0) caches = [];
                if (result == null || result.length == 0) result = [];
                $.when(checkSchema2(cooConfig.CATEGORYTABLENAME, caches, result),
                    checkSchema2(cooConfig.WORKFLOWTABLENAME, caches, result),
                    checkSchema2(cooConfig.JOINTABLENAME, caches, result),
                    checkSchema2(cooConfig.DEPARTMENTTABLENAME, caches, result),
                    checkSchema2(cooConfig.TAGTABLENAME, caches, result),
                    checkSchema2(cooConfig.PAGETABLENAME, caches, result),
                    checkSchema2(cooConfig.ELEMENTTABLENAME, caches, result),
                    checkSchema2(cooConfig.COOITEMPATTERNTABLENAME, caches, result),
                    checkSchema2(cooConfig.STATUSTABLENAME, caches, result),
                    checkSchema2(cooConfig.CUSERTABLENAME, caches, result),
                    checkSchema2(cooConfig.EVENTTABLENAME, caches, result),
                    checkSchema2(cooConfig.TEMPLATETABLENAME, caches, result),
                    updateSystem(ret.updatefiles,caches,result)
                    ).done(function (a, b, c, d, e, f, g, h, i, j, k, l,m) {        
               
                        if (a || b || c || d || e || f || g || h || i || j || k || l||m) {
                            $.when(clear(cooConfig.CACHETABLENAME)).done(function () {
                                populateDatabase(cooConfig.CACHETABLENAME, result);
                            });
                        }

                        dtd.resolve();
                        
               
                });              

            });

        });

        return dtd.promise();
    };





    function maxDateTime (osTableName) {

        //todo        


        var dtd = $.Deferred();

        if (localDatabase != null && localDatabase.db != null) {
            var transaction = localDatabase.db.transaction(osTableName);
            if (transaction) {
                transaction.oncomplete = function () {
                }
                transaction.onabort = function () {
                    console.log("transaction aborted.");
                    localDatabase.db.close();
                }
                transaction.ontimeout = function () {
                    console.log("transaction timeout.");
                    localDatabase.db.close();
                }
                var store = transaction.objectStore(osTableName);
                if (store) {
                    var keyRange = IDBKeyRange.lowerBound(0);
                    var cursorRequest = store.openCursor(keyRange);
                    var t = '2011-04-11';

                    cursorRequest.onsuccess = function (e) { // success called for each cursor action
                        var result = e.target.result;
                        if (result) {                        
                            if (result.value.t!=undefined && Date.parse(result.value.t) > Date.parse(t)) t = result.value.t;
                            result.continue();
                        }
                        else {
                          //  console.log(osTableName + ':' + t);
                            dtd.resolve(t);
                        }
                    };
                }
            }
            return dtd.promise();
        }
        else {
            console.log("Database needs to be created first");
        }

    };





    locdb.countRecords = function (osTableName) {

        var dtd = $.Deferred();

        if (localDatabase != null && localDatabase.db != null) {
            console.log("Starting count");
            var transaction = localDatabase.db.transaction(osTableName);
            if (transaction) {
                transaction.oncomplete = function () {
                }
                transaction.onabort = function () {
                    console.log("transaction aborted.");
                    localDatabase.db.close();
                }
                transaction.ontimeout = function () {
                    console.log("transaction timeout.");
                    localDatabase.db.close();
                }
                var store = transaction.objectStore(osTableName);
                if (store) {
                    var keyRange = IDBKeyRange.lowerBound(0);
                    var cursorRequest = store.openCursor(keyRange);
                    var count = 0;

                    cursorRequest.onsuccess = function (e) { // success called for each cursor action
                        var result = e.target.result;
                        if (result) {
                            ++count && result.continue();
                        }
                        else {
                            console.log(osTableName + ':' + count);
                            dtd.resolve(count);
                        }
                    };
                }
            }
            return dtd.promise();
        }
        else {
            console.log("Database needs to be created first");
        }
    };







    function fetchAllWorkFlows() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.WORKFLOWTABLENAME).objectStore(cooConfig.WORKFLOWTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }




    function fetchAllCategories() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.CATEGORYTABLENAME).objectStore(cooConfig.CATEGORYTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllDepartments() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.DEPARTMENTTABLENAME).objectStore(cooConfig.DEPARTMENTTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllTags() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.TAGTABLENAME).objectStore(cooConfig.TAGTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }



    function fetchAllJoins() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.JOINTABLENAME).objectStore(cooConfig.JOINTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }



    function fetchAllPatterns() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.COOITEMPATTERNTABLENAME).objectStore(cooConfig.COOITEMPATTERNTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllPages() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.PAGETABLENAME).objectStore(cooConfig.PAGETABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllElements() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.ELEMENTTABLENAME).objectStore(cooConfig.ELEMENTTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllStatus() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.STATUSTABLENAME).objectStore(cooConfig.STATUSTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }



    function fetchAllUsers() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.CUSERTABLENAME).objectStore(cooConfig.CUSERTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    function fetchAllCache() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.CACHETABLENAME).objectStore(cooConfig.CACHETABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId==cooConfig.configObj.companyId)  ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    };


    function fetchAllEvent() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.EVENTTABLENAME).objectStore(cooConfig.EVENTTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    };



    function fetchAllUserElements() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.USERELEMENTTABLENAME).objectStore(cooConfig.USERELEMENTTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        if (ipattern.companyId == cooConfig.configObj.companyId) ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    locdb.fetchAllUserElements= function() {
        try {
            var dtd = $.Deferred();
            var ret = [];
            if (localDatabase != null && localDatabase.db != null) {

                var store = localDatabase.db.transaction(cooConfig.USERELEMENTTABLENAME).objectStore(cooConfig.USERELEMENTTABLENAME);

                store.openCursor().onsuccess = function (evt) {
                    var cursor = evt.target.result;
                    if (cursor) {
                        var ipattern = cursor.value;
                        ret.push(ipattern);
                        cursor.continue();
                    }
                    else {
                        dtd.resolve(ret);
                    }
                };
            }
            return dtd.promise();

        }
        catch (e) {
            console.log(e.message);
        }
    }


    locdb.start = function () {

        var dtd = $.Deferred();


        if (cooConfig.configObj.isStart) {
            console.log('starting');
            dtd.resolve(true);
        }
        else {

            $.when(login.autoLogin()).done(function (isAuto) {

                if (isAuto) {

                    $.when(initDatabase()).done(function () {

                        $.when(initData2()).done(function () {

                            $.when(fetchAllPatterns(), fetchAllPages(), fetchAllElements(), fetchAllStatus(), fetchAllUsers(), fetchAllUserElements(), fetchAllCategories(), fetchAllDepartments(), fetchAllTags(), fetchAllJoins(), fetchAllWorkFlows(), fetchAllEvent()).done(function (a, b, c, d, e, f, g, h, i, j, k, l) {


                                cooConfig.allpattern = a;
                                cooConfig.allpage = b;
                                cooConfig.allelement = c;
                                cooConfig.allstatus = d;
                                cooConfig.alluser = e;
                                cooConfig.alluserelement = f;
                                cooConfig.allcategory = g;
                                cooConfig.alldepartment = h;
                                cooConfig.alltag = i;
                                cooConfig.alljoin = j;
                                cooConfig.allworkflow = k;
                                cooConfig.allevent = l;
                                //     console.log('init');
                                cooConfig.configObj.isStart = true;

                                console.log('finish init');
                                dtd.resolve(true);
                            });

                        });
                    });
                }
                else
                {
                    dtd.resolve(false);
                }
            });
        }
        return dtd.promise();


    }




    

    locdb.sayHello = function () {

        console.log('helo');

    };


    return locdb;

});

 