예제 #1
0
 protected void CopySQLTableData()
 {
     SPSecurity.RunWithElevatedPrivileges(delegate()
     {
         try
         {
             HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
             LogAction_SQLDataInsert("Before starting foreach of SQL");
             string sclearsql = string.Concat(@"[dbo].[CopySearchData]");
             //DataTable tempTable = null;
             //tempTable = conn.executeSelectNoParameter(sclearsql);
             conn.executeSelectNoParameter(sclearsql);
         }
         catch (Exception Exception)
         {
             LogAction_SQLDataInsert(Exception.Message);
         }
     });
 }
        protected void DocViewCounts1(string siteObj, string libName)
        {
            #region t1
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                try
                {
                    using (SPSite site = new SPSite(siteObj))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPList list = web.Lists[libName];
                            SPListItemCollection coll = list.GetItems();
                            foreach (SPListItem item in coll)
                            {
                                SPAuditQuery spQuery = new SPAuditQuery(site);
                                spQuery.RestrictToListItem(item);
                                SPAuditEntryCollection auditCol = site.Audit.GetEntries(spQuery);

                                string docName = "";
                                int counter    = 0;

                                foreach (SPAuditEntry entry in auditCol)
                                {
                                    if (entry.ItemType == SPAuditItemType.Document && entry.Event == SPAuditEventType.View)
                                    {
                                        try
                                        {
                                            var1 = entry.DocLocation.Substring(entry.DocLocation.LastIndexOf("/"));
                                            var2 = var1.Substring(var1.LastIndexOf("/"));
                                            var3 = var2.Substring(1);
                                            var4 = var3.Substring(var3.LastIndexOf('.') + 1);
                                            if (var4 != "aspx")
                                            {
                                                if (entry.EventSource == SPAuditEventSource.SharePoint)
                                                {
                                                    if (docName != var3)
                                                    {
                                                        docName = var3;
                                                        counter = 1;

                                                        DataRow drRow          = dtAudit.NewRow();
                                                        drRow["DocName"]       = var3;
                                                        drRow["DocLocation"]   = entry.DocLocation;
                                                        drRow["DownloadCount"] = 1;
                                                        dtAudit.Rows.Add(drRow);
                                                    }
                                                    else
                                                    {
                                                        DataRow[] drExists = dtAudit.Select("DocName = '" + var3 + "' AND DocLocation = '" + entry.DocLocation + "'");
                                                        if (drExists != null && drExists.Length > 0)
                                                        {
                                                            drExists[0]["DownloadCount"] = Convert.ToInt32(drExists[0]["DownloadCount"]) + 1;
                                                        }
                                                        counter = counter + 1;
                                                    }
                                                }
                                            }
                                        }
                                        catch (Exception ee)
                                        {
                                            LogAction(ee.Message);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    using (SPSite docSite = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
                    {
                        using (SPWeb docWeb = docSite.OpenWeb())
                        {
                            SPList docList = docWeb.Lists["TopViewedDocs"];
                            foreach (DataRow rr in dtAudit.Rows)
                            {
                                try
                                {
                                    #region SQL Command for select from [AAES Home].[dbo].[TopViewedDocsTable11]

                                    HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
                                    sqlDocName       = rr["DocName"].ToString();
                                    sqlDocLocation   = rr["DocLocation"].ToString();
                                    string sclearsql = string.Concat("SELECT * FROM [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                     "WHERE DocName = @DocName AND DocLocation = @DocLocation");
                                    SqlParameter[] parameter =
                                    {
                                        new SqlParameter("@DocName", SqlDbType.VarChar)
                                        {
                                            Value = sqlDocName
                                        },
                                        new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                        {
                                            Value = sqlDocLocation
                                        }
                                    };
                                    DataTable tempTable = null;

                                    tempTable = conn.executeSelectQuery(sclearsql, parameter);

                                    if ((tempTable == null) || (tempTable.Rows.Count == 0))
                                    {
                                        //insert
                                        string sclearsqlIns = string.Concat("INSERT INTO [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                            "(DocName, DocLocation, DownloadCount) " +
                                                                            "VALUES(@DocName, @DocLocation, @DownloadCount)");
                                        SqlParameter[] parameterUpd =
                                        {
                                            new SqlParameter("@DocName", SqlDbType.VarChar)
                                            {
                                                Value = rr["DocName"].ToString()
                                            },
                                            new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                            {
                                                Value = rr["DocLocation"].ToString()
                                            },
                                            new SqlParameter("@DownloadCount", SqlDbType.Int)
                                            {
                                                Value = Convert.ToInt32(rr["DownloadCount"])
                                            }
                                        };
                                        bool isInsert = conn.executeInsertQuery(sclearsqlIns, parameterUpd);
                                    }
                                    else
                                    {
                                        //update
                                        string sclearsqlUpd = string.Concat("UPDATE [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                            "SET  DownloadCount = @DownloadCount " +
                                                                            "WHERE DocName = @DocName AND DocLocation = @DocLocation");
                                        SqlParameter[] parameterUpd =
                                        {
                                            new SqlParameter("@DocName", SqlDbType.VarChar)
                                            {
                                                Value = rr["DocName"].ToString()
                                            },
                                            new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                            {
                                                Value = rr["DocLocation"].ToString()
                                            },
                                            new SqlParameter("@DownloadCount", SqlDbType.Int)
                                            {
                                                Value = Convert.ToInt32(rr["DownloadCount"])
                                            }
                                        };
                                        bool isInsert = conn.executeUpdateQuery(sclearsqlUpd, parameterUpd);
                                    }
                                    #endregion
                                }
                                catch (Exception Exception1)
                                {
                                    LogAction(Exception1.Message);
                                }
                            }
                        }
                    }
                }
                catch (Exception eee)
                {
                    LogAction(eee.Message);
                }
            });

            #endregion
        }
        protected void DocViewCounts(string siteObj, string libName)
        {
            #region t1
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite siteLogList = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
                {
                    using (SPWeb webLogList = siteLogList.OpenWeb())
                    {
                        SPList logList          = webLogList.Lists["TimerLogList"];
                        SPListItem logListItems = null;
                        try
                        {
                            using (SPSite site = new SPSite(siteObj))
                            {
                                using (SPWeb web = site.OpenWeb())
                                {
                                    //lbltest.Text += "<br/>" + web.Title.ToString();
                                    SPList list = web.Lists[libName];    //Documents
                                    SPListItemCollection coll = list.GetItems();
                                    //Dictionary<string, int> dictionary = new Dictionary<string, int>();

                                    //dtAudit.Columns.Add("DocName");
                                    //dtAudit.Columns.Add("DocLocation");
                                    //dtAudit.Columns.Add("DownloadCount");

                                    foreach (SPListItem item in coll)
                                    {
                                        SPAuditQuery spQuery = new SPAuditQuery(site);
                                        spQuery.RestrictToListItem(item);
                                        SPAuditEntryCollection auditCol = site.Audit.GetEntries(spQuery);

                                        string docName = "";
                                        int counter    = 0;
                                        foreach (SPAuditEntry entry in auditCol)
                                        {
                                            if (entry.ItemType == SPAuditItemType.Document && entry.Event == SPAuditEventType.View)
                                            {
                                                try
                                                {
                                                    var1 = entry.DocLocation.Substring(entry.DocLocation.LastIndexOf("/"));
                                                    var2 = var1.Substring(var1.LastIndexOf("/"));
                                                    var3 = var2.Substring(1);
                                                    var4 = var3.Substring(var3.LastIndexOf('.') + 1);
                                                    if (var4 != "aspx")
                                                    {
                                                        if (entry.EventSource == SPAuditEventSource.SharePoint)
                                                        {
                                                            if (docName != var3)
                                                            {
                                                                docName = var3;
                                                                counter = 1;

                                                                DataRow drRow          = dtAudit.NewRow();
                                                                drRow["DocName"]       = var3;
                                                                drRow["DocLocation"]   = entry.DocLocation;
                                                                drRow["DownloadCount"] = 1;
                                                                //dictionary.Add(var3, 1);
                                                                dtAudit.Rows.Add(drRow);
                                                            }
                                                            else
                                                            {
                                                                DataRow[] drExists = dtAudit.Select("DocName = '" + var3 + "' AND DocLocation = '" + entry.DocLocation + "'");
                                                                if (drExists != null && drExists.Length > 0)
                                                                {
                                                                    //int cont = drExists[0]["DownloadCount"];
                                                                    drExists[0]["DownloadCount"] = Convert.ToInt32(drExists[0]["DownloadCount"]) + 1;
                                                                    //drExists[0]["DownloadCount"];
                                                                }
                                                                //if (dictionary.TryGetValue(var3, out counter))
                                                                //{
                                                                //    dictionary[var3] = counter + 1;
                                                                //}
                                                                counter = counter + 1;
                                                            }
                                                        }
                                                    }
                                                }
                                                catch (Exception ee)
                                                {
                                                    // SPSecurity.RunWithElevatedPrivileges(delegate()
                                                    // {

                                                    //lbltest.Text += "<br/>" + web.Title.ToString();
                                                    //SPList logList = webLogList.Lists["TimerLogList"]; //Documents
                                                    //SPListItem logListItems =
                                                    logList.Items.Add();
                                                    logListItems["Title"] = "2 :- SPAuditEntry :- " + ee.Message;
                                                    logListItems.Update();
                                                    //  }
                                                    // }
                                                    //});
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            using (SPSite docSite = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
                            {
                                using (SPWeb docWeb = docSite.OpenWeb())
                                {
                                    SPList docList = docWeb.Lists["TopViewedDocs"];
                                    //foreach (KeyValuePair<string, int> pair in dictionary)
                                    foreach (DataRow rr in dtAudit.Rows)
                                    {
                                        #region List Data Insert
                                        ////////htmlStr.Append(("Document Name: " + pair.Key.ToString() + "  -  " + "Views Count: " + pair.Value.ToString()) + "<br>");
                                        //////SPListItem li = docList.Items.Add();
                                        //////li["DocName"] = rr["DocName"].ToString();// pair.Value.ToString();
                                        //////li["DocLocation"] = rr["DocLocation"].ToString();// pair.Value.ToString();
                                        //////li["DocViewCount"] = Convert.ToInt32(rr["DownloadCount"]);// pair.Value.ToString();
                                        //////li["Title"] = "Data Entered at :- " + DateTime.Now.ToString();

                                        ////////li["DocViewCount"] = pair.Value.ToString();
                                        ////////li["Title"] = "Data Entered at :- " + DateTime.Now.ToString();
                                        //////li.Update();
                                        #endregion
                                        try
                                        {
                                            #region SQL Command for select from [AAES Home].[dbo].[TopViewedDocsTable11]

                                            HVEFiles.dbConnection conn = new HVEFiles.dbConnection();
                                            //CustCode conn = new CustCode();
                                            sqlDocName       = rr["DocName"].ToString();
                                            sqlDocLocation   = rr["DocLocation"].ToString();
                                            string sclearsql = string.Concat("SELECT * FROM [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                             "WHERE DocName = @DocName AND DocLocation = @DocLocation");
                                            SqlParameter[] parameter =
                                            {
                                                new SqlParameter("@DocName", SqlDbType.VarChar)
                                                {
                                                    Value = sqlDocName
                                                },
                                                new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                                {
                                                    Value = sqlDocLocation
                                                }
                                            };
                                            DataTable tempTable = null;
                                            //SPListItem logListItems = logList.Items.Add();
                                            logList.Items.Add();
                                            logListItems["Title"] = "3 :- SQL FUnctions :- " + "before executeSelectQuery";
                                            logListItems.Update();
                                            tempTable = conn.executeSelectQuery(sclearsql, parameter);
                                            //SPListItem logListItems = logList.Items.Add();
                                            logList.Items.Add();
                                            logListItems["Title"] = "3 :- SQL FUnctions :- " + "After executeSelectQuery";
                                            logListItems.Update();
                                            if ((tempTable == null) || (tempTable.Rows.Count == 0))
                                            {
                                                //insert
                                                string sclearsqlIns = string.Concat("INSERT INTO [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                                    "(DocName, DocLocation, DownloadCount) " +
                                                                                    "VALUES(@DocName, @DocLocation, @DownloadCount)");
                                                SqlParameter[] parameterUpd =
                                                {
                                                    new SqlParameter("@DocName", SqlDbType.VarChar)
                                                    {
                                                        Value = rr["DocName"].ToString()
                                                    },
                                                    new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                                    {
                                                        Value = rr["DocLocation"].ToString()
                                                    },
                                                    new SqlParameter("@DownloadCount", SqlDbType.Int)
                                                    {
                                                        Value = Convert.ToInt32(rr["DownloadCount"])
                                                    }
                                                };
                                                bool isInsert = conn.executeInsertQuery(sclearsqlIns, parameterUpd);
                                            }
                                            else
                                            {
                                                //update
                                                string sclearsqlUpd = string.Concat("UPDATE [AAES Home].[dbo].[TopViewedDocsTable11] " +
                                                                                    "SET  DownloadCount = @DownloadCount " +
                                                                                    "WHERE DocName = @DocName AND DocLocation = @DocLocation");
                                                SqlParameter[] parameterUpd =
                                                {
                                                    new SqlParameter("@DocName", SqlDbType.VarChar)
                                                    {
                                                        Value = rr["DocName"].ToString()
                                                    },
                                                    new SqlParameter("@DocLocation", SqlDbType.VarChar)
                                                    {
                                                        Value = rr["DocLocation"].ToString()
                                                    },
                                                    new SqlParameter("@DownloadCount", SqlDbType.Int)
                                                    {
                                                        Value = Convert.ToInt32(rr["DownloadCount"])
                                                    }
                                                };
                                                bool isInsert = conn.executeUpdateQuery(sclearsqlUpd, parameterUpd);
                                            }


                                            #endregion
                                        }
                                        catch (Exception Exception1)
                                        {
                                            //SPSecurity.RunWithElevatedPrivileges(delegate()
                                            //{
                                            //    using (SPSite siteLogList = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
                                            //    {
                                            //        using (SPWeb webLogList = siteLogList.OpenWeb())
                                            //        {
                                            //lbltest.Text += "<br/>" + web.Title.ToString();
                                            // SPList logList = webLogList.Lists["TimerLogList"]; //Documents
                                            // SPListItem logListItems = logList.Items.Add();
                                            logList.Items.Add();
                                            logListItems["Title"] = "3 :- SQL FUnctions :- " + Exception1.Message;
                                            logListItems.Update();
                                            //        }
                                            //    }
                                            //});
                                            // Exception1Label.Text = Exception1.Message; //Label1
                                        }
                                        #region SQL Command for insert into SP
                                        //    string sclearsql = "sp_TopViewedDocs"; /*"INSERT INTO TopViewedDocsTable (TraineeName, LoginID,LearnerDSId, TraineeOnboardingDate,SupervisorLogin,FunctionalArea1,TraineeOrganization,RoleFamily1,Role1,FunctionalSpeciality1,ManagerRightsNeeded,ArchiveRole1,ArchiveRole2,ArchiveRole3,TraineeLocation,RoleFamily2,Role2,FunctionalArea2,FunctionalSpeciality2,RoleFamily3,Role3,FunctionalArea3,FunctionalSpeciality3) " +
                                        //"VALUES (@TraineeName, @LoginID,@LearnerDSId, @TraineeOnboardingDate,@SupervisorLogin, @FunctionalArea1, @TraineeOrganization,@RoleFamily1,@Role1,@FunctionalSpeciality1,@ManagerRightsNeeded,@ArchiveRole1,@ArchiveRole2,@ArchiveRole3,@TraineeLocation,@RoleFamily2, @Role2, @FunctionalArea2,@FunctionalSpeciality2,@RoleFamily3,@Role3,@FunctionalArea3,@FunctionalSpeciality3)";*/

                                        //    SqlParameter[] parameter = {
                                        //    new SqlParameter("@DocName", SqlDbType.VarChar) { Value = rr["DocName"].ToString() },
                                        //    new SqlParameter("@DocLocation", SqlDbType.VarChar) { Value = rr["DocLocation"].ToString() },
                                        //    new SqlParameter("@DownloadCount", SqlDbType.Int) { Value = Convert.ToInt32(rr["DownloadCount"]) }
                                        //    };
                                        //    HVE.Files.dbConnection conn = new HVE.Files.dbConnection();
                                        //    bool isInsert = conn.executeInsertQuery(sclearsql, parameter);

                                        #endregion
                                    }
                                }
                            }
                        }
                        catch (Exception eee)
                        {
                            //Console.WriteLine(eee.Message);
                            //SPSecurity.RunWithElevatedPrivileges(delegate()
                            //{
                            //    using (SPSite siteLogList1 = new SPSite("http://win-njfp7te48bn/sites/HVEDev"))
                            //    {
                            //        using (SPWeb webLogList1 = siteLogList1.OpenWeb())
                            //        {
                            //lbltest.Text += "<br/>" + web.Title.ToString();
                            //SPList logList1 = webLogList1.Lists["TimerLogList"]; //Documents
                            //SPListItem logListItems = logList.Items.Add();
                            logList.Items.Add();
                            logListItems["Title"] = eee.Message;
                            logListItems.Update();
                            //        }
                            //    }
                            //});
                        }
                        //LiteralText.Text = htmlStr.ToString();
                    }
                }
            });

            #endregion
        }