INTRO: DropBox is a project that I have delivered for school users. It contains three parts.

DropBox

 

1. Server Part, a C# application read the data from SQL server and create folders on file server based on Class name, then create user group and assign access mappings based on name-Class mapping. 

The folder stucture is like this :

FolderMapping

2. Student side, at student login, DropBox will run and find the folders that associated with student logon name, then create shortcut inside DropBox folder on student`s desktop. Student can easilly access their class folder and submit their assignments, get resource from teacher and share their own resource through DropBox folder.

3. Teacher side, at teacher login, Dropbox will create DropBox desktop folder contains all their classes, they can access individual student assignment folder through students folder, collect assignments from assignment folder and share resource through resource folder. Finally access studentzone as they want.


 

Part 2, Details and Codes for the project.

2 VBS to generate CSV from timeTabler:
 Set oCn = CreateObject( "ADODB.Connection" )
 Set oRs = CreateObject( "ADODB.Recordset"  )
 Set oFso = CreateObject("Scripting.FileSystemObject")
 dim currentTime
CurrentTime = year(now)&Right("0"&month(now),2)&Right("0"&day(now),2)&"_" &hour(now)&minute(now)&second(now)
 
oCn.ConnectionString = "Data Source=<Path>\ Timetable.tdfx;Provider=Microsoft.Jet.OLEDB.4.0;"
oCn.open
oRs.Open "Select  [Class Code],[Student Code]  from CLesson ", oCn
'connection is done
'create a file to save the data
set oFileOut = oFso.CreateTextFile("StudClass.csv", True)

'write the data to the file just created
Dim i
 
While Not oRs.EOF

  For i = 0 to oRs.Fields.Count - 1
    sLineOut = sLineOut & oRs.Fields(i).Value
    If i < oRs.Fields.Count - 1  Then
    	sLineOut = sLineOut & ","
    End If
  Next
  
  oFileOut.WriteLine(sLineOut)
  sLineOut = ""
  oRS.MoveNext 
Wend
oRs.Close
oFileOut.Close

oRs.Open "Select [Class Code],[Teacher Code] from TCourse", oCn
set oFileOut = oFso.CreateTextFile("TeacherClass.csv", True)
While Not oRs.EOF
  ' Get the column data for each column in the row.
  For i = 0 to oRs.Fields.Count - 1
    sLineOut = sLineOut & oRs.Fields(i).Value
    ' Separate the fields with commas
    If i < oRs.Fields.Count - 1  Then
    	sLineOut = sLineOut & ","
    End If
  Next

  oFileOut.WriteLine(sLineOut)
  sLineOut = ""
  oRS.MoveNext
Wend

oRs.Close
oFileOut.Close

oRs.Open "Select [Class Code],[Subject Code],[Class Name],[Subject Name] from Tclasses", oCn
set oFileOut = oFso.CreateTextFile("Classes.csv", True)
While Not oRs.EOF
  ' Get the column data for each column in the row.
  For i = 0 to oRs.Fields.Count - 1
    sLineOut = sLineOut & oRs.Fields(i).Value
    ' Separate the fields with commas
    If i < oRs.Fields.Count - 1  Then
    	sLineOut = sLineOut & ","
    End If
  Next

  oFileOut.WriteLine(sLineOut)
  sLineOut = ""
  oRS.MoveNext
Wend
oRs.Close
oFileOut.Close


Set oFso = CreateObject("Scripting.FileSystemObject")
If oFso.FileExists("<DataFolder>\StudClass.csv") Then
   oFso.MoveFile "<DataFolder>\StudClass.csv", "<DataFolder>\databackup\"&CurrentTime&"_StudClass.csv"
End If
If oFso.FileExists("<DataFolder>\TeacherClass.csv") Then
   oFso.MoveFile "<DataFolder>\TeacherClass.csv", "<DataFolder>\databackup\"&CurrentTime&"_TeacherClass.csv"
End If
If oFso.FileExists("<DataFolder>\Classes.csv") Then
   oFso.MoveFile "<DataFolder>\Classes.csv", "<DataFolder>\databackup\"&CurrentTime&"_Classes.csv"
End If

oFso.MoveFile "*.csv", "<DataFolder>\resources\"

 

 

4. Import CSV to SQL Server

Schedule a job at SQL Server, SQL Scripts as follow:

USE <DataBaseName>
GO

--Drop table
drop table teacherclass
go


-- create table
create table TeacherClass
(
ClassCode VARCHAR(20),
TeacherCode VARCHAR(20))
GO

--Import DATA

BULK 
INSERT TeacherClass
FROM 'C:\CSV\TeacherClass.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
go

drop table StuClass
go

create table StuClass
(
ClassCode VARCHAR(20),
StuCode VARCHAR(20)
)
GO

BULK 
INSERT StuClass
FROM 'C:\CSV\StudClass.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
go

 Step 5. Server side C# code for Dropbox

    public static void dropbox()
            {
                BW1_GroupCreation();
                BW2_AddStudents();
                BW3_AddTeachers();
                BW4_CreateFolderStructure();
                BW5_GrantPermissions();
                BW6_SubFolders();
                BW7_copyFiles();
            }
            private static void BW1_GroupCreation()
            {
                writeToLogs("Group Creation Started", dropboxlog);
                try
                {
                    var lineCount = CountLinesInFile(classcsv);
                    var reader = new StreamReader(System.IO.File.OpenRead(classcsv));
                    for (int i = 0; i < lineCount; i++)
                    {
                        var line = reader.ReadLine();
                        var values = line.Split(',');
                        string teachergrp = values[0] + "admin";
                        if (!ifGroupExist(values[0]))
                            writeToLogs(createGroup(studentclassgroupou, values[0]), dropboxlog);
                        else
                            writeToLogs(values[0] + "exist", dropboxlog);
                        if (!ifGroupExist(teachergrp))
                            writeToLogs(createGroup(staffclassgroupou, teachergrp), dropboxlog);
                        else
                            writeToLogs(teachergrp + "exist", dropboxlog);
                    }
                }
                catch (Exception E)
                {
                    writeToLogs(E.ToString(), dropboxlog);
                }
            }
            private static void BW2_AddStudents()
            {
                writeToLogs("Add Students Started", dropboxlog);
                var lineCount = System.IO.File.ReadLines(studclasscsv).Count();
                var reader = new StreamReader(System.IO.File.OpenRead(studclasscsv));
                for (int i = 0; i < lineCount; i++)
                {
                    var line = reader.ReadLine();
                    var values = line.Split(',');
                    string groupname = values[0];
                    string studentname = values[1];
                    try
                    {
                        AddUserToGroup(studentname, groupname);
                        writeToLogs(studentname + "is added to " + groupname, dropboxlog);
                    }
                    catch (Exception E)
                    {
                        writeToLogs(E.ToString() + " when try to add: " + studentname + " to " + groupname, dropboxlog);
                    }
                }
            }
            private static void BW3_AddTeachers()
            {
                writeToLogs("Add Teachers Started", dropboxlog);
                var lineCount = System.IO.File.ReadLines(TeacherClass).Count();
                var reader = new StreamReader(System.IO.File.OpenRead(TeacherClass));
                for (int i = 0; i < lineCount; i++)
                {
                    var line = reader.ReadLine();
                    var values = line.Split(',');
                    string groupname = values[0] + "admin";
                    string teachername = values[1];
                    try
                    {
                        AddUserToGroup(teachername, groupname);
                        writeToLogs(teachername + "is added to " + groupname, dropboxlog);
                    }
                    catch (Exception E)
                    {
                        writeToLogs(E.ToString(), dropboxlog);
                    }
                }
            }
            private static void BW4_CreateFolderStructure()
            {
                writeToLogs("Create Folder Structure started", dropboxlog);
                try
                {
                    var lineCount = System.IO.File.ReadLines(classcsv).Count();
                    var reader = new StreamReader(System.IO.File.OpenRead(classcsv));
                    for (int i = 0; i < lineCount; i++)
                    {
                        var line = reader.ReadLine();
                        var values = line.Split(',');
                        String dir = dropboxfolder + values[0];
                        String dir_ASSIGNMENTS = dir + "\\ASSIGNMENTS";
                        String dir_MARKED = dir + "\\ASSIGNMENTS\\MARKED";
                        String dir_RESOURCES = dir + "\\RESOURCES";
                        String dir_STUDENTS = dir + "\\STUDENTS";
                        String dir_LOGS = dir + "\\ASSIGNMENTS\\ARCHIVE\\LOGS";
                        String dir_STUDENTZONE = dir + "\\STUDENTZONE";
                        if (!Directory.Exists(dir_STUDENTZONE))
                        {
                            createDir(dir);
                            createDir(dir_RESOURCES);
                            createDir(dir_STUDENTS);
                            createDir(dir_ASSIGNMENTS);
                            System.IO.File.Copy(csvfolder + "Collect.vbs", dir_ASSIGNMENTS + "\\COLLECT.VBS", true);
                            createDir(dir_MARKED);
                            System.IO.File.Copy(csvfolder + "SENDBACK.vbs", dir_MARKED + "\\SENDBACK.VBS", true);
                            createDir(dir_LOGS);
                            createDir(dir_STUDENTZONE);
                        }
                        writeToLogs(dir + " is processed", dropboxlog);
                    }
                }
                catch (Exception E)
                {
                    writeToLogs(E.ToString(), dropboxlog);
                }
            }
            private static void BW5_GrantPermissions()
            {
                writeToLogs("Grant Permissions started", dropboxlog);
                try
                {
                    var lineCount = System.IO.File.ReadLines(classcsv).Count();
                    var reader = new StreamReader(System.IO.File.OpenRead(classcsv));
                    for (int i = 0; i < lineCount; i++)
                    {
                        var line = reader.ReadLine();
                        var values = line.Split(',');
                        String dir = dropboxfolder + values[0].TrimEnd().TrimStart();
                        String stuGrp = values[0].TrimEnd().TrimStart();
                        String teacherGrp = stuGrp + "admin";
                        String dir_res = dir + "\\RESOURCES";
                        String dir_stuzone = dir + "\\STUDENTZONE";
                        AddDirectorySecurity(dir, teacherGrp, FileSystemRights.Modify);
                        AddDirectorySecurity(dir_res, stuGrp, FileSystemRights.Read);
                        AddDirectorySecurity(dir_stuzone, stuGrp, FileSystemRights.Modify);
                        writeToLogs(dir + "permission granted", dropboxlog);
                    }
                }
                catch (Exception E)
                {
                    writeToLogs(E.ToString(), dropboxlog);
                }
            }
            private static void BW6_SubFolders()
            {
                writeToLogs("Started Creating SubFolders and permissions", dropboxlog);
                try
                {
                    var lineCount = System.IO.File.ReadLines(studclasscsv).Count();
                    var reader = new StreamReader(System.IO.File.OpenRead(studclasscsv));
                    for (int i = 0; i < lineCount; i++)
                    {
                        var line = reader.ReadLine();
                        var values = line.Split(',');
                        String dir = dropboxfolder + values[0].TrimEnd().TrimStart() + "\\STUDENTS\\" + values[1].TrimEnd().TrimStart();
                        createDir(dir);
                        AddDirectorySecurity(dir, values[1].TrimStart().TrimEnd(), FileSystemRights.Modify);
                        writeToLogs(dir + "added sub security", dropboxlog);
                    }
                }
                catch (Exception E)
                {
                    writeToLogs(E.ToString(), dropboxlog);
                }
                writeToLogs("6 complete", dropboxlog);
            }
            private static void BW7_copyFiles()
            {
                writeToLogs("Create Folder Structure started", dropboxlog);
                try
                {
                    var lineCount = System.IO.File.ReadLines(classcsv).Count();
                    var reader = new StreamReader(System.IO.File.OpenRead(classcsv));
                    for (int i = 0; i < lineCount; i++)
                    {
                        var line = reader.ReadLine();
                        var values = line.Split(',');
                        String dir = dropboxfolder + values[0];
                        String dir_ASSIGNMENTS = dir + "\\ASSIGNMENTS";
                        String dir_MARKED = dir + "\\ASSIGNMENTS\\MARKED";
                        System.IO.File.Copy(csvfolder + "Collect.vbs", dir_ASSIGNMENTS + "\\COLLECT.VBS", true);
                        System.IO.File.Copy(csvfolder + "SENDBACK.vbs", dir_MARKED + "\\SENDBACK.VBS", true);
                    }

                }

                catch (Exception E)
                {
                    writeToLogs(E.ToString(), dropboxlog);
                }
            }

 

Step 9, Student Dropbox VBS client code

'connection to the sql server

Option Explicit
dim currentTime
Dim sServer
Dim sLogin
Dim sPwd
Dim oCn       
Dim oRs
Dim sNames
Dim oFso
Dim oFileOut
Dim sLineOut, Wl, sql ,i, fileName,yearlevel
Dim wshShell
Dim objNetwork,strLocalDrive,strRemoteShare,objReg,objfso

sServer = "DataBaseName"
sLogin = "<ReadOnlyUserName>"
sPwd = "<Password>"

Set oCn = CreateObject( "ADODB.Connection" )
Set oRs = CreateObject( "ADODB.Recordset"  )

Set oFso = CreateObject("Scripting.FileSystemObject")
set Wl = WScript.CreateObject("WScript.Shell")

oCn.ConnectionString = "PROVIDER=SQLOLEDB" & _
                       ";SERVER=" & sServer   & _
                       ";UID="    & sLogin  & _
                       ";PWD="    & sPwd    & _
                       ";DATABASE=bsc"
oCn.open
sql= "Select ClassCode, StuCode from dbo.StuClass where StuCode = '"& Wl.expandenvironmentstrings("%username%") & "'"
fileName = Wl.expandenvironmentstrings("%userprofile%") & "\StudCls.csv" 
oRs.Open sql, oCn
'connection is done
'create a file to save the data
set oFileOut = oFso.CreateTextFile(fileName, True)
While Not oRs.EOF
  ' Get the column data for each column in the row.
  For i = 0 to oRs.Fields.Count - 1
    sLineOut = sLineOut & oRs.Fields(i).Value
    ' Separate the fields with commas
    If i < oRs.Fields.Count - 1  Then
    	sLineOut = sLineOut & ","
    End If
  Next

  oFileOut.WriteLine(sLineOut)
  sLineOut = ""
  oRS.MoveNext
Wend

oRs.Close
oFileOut.Close
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set WshShell = CreateObject("WScript.Shell") 
WshShell.Run chr(34) & "<DropBoxBatchFolder>\DropBoxStud.bat" & Chr(34), 0

 

Dropbox Batch file for Student:

@echo off
mkdir %USERPROFILE%\Desktop\Dropbox2016\ %USERPROFILE%\sctemp > nul 2>&1
copy \\<fileServer>\shortcut.exe %USERPROFILE%\sctemp /y > nul 2>&1
move  %USERPROFILE%\studCls.csv  %USERPROFILE%\sctemp\ > nul 2>&1

 cd /d %USERPROFILE%\sctemp > nul 2>&1
 for /f "tokens=1-2 delims=, " %%d in (StudCls.csv) do  (
 mkdir %USERPROFILE%\Desktop\Dropbox2016\%%d > nul 2>&1 
shortcut /F:"%USERPROFILE%\Desktop\Dropbox2016\%%d\assignments.lnk" /A:c /T:\\8807fs2\Dropbox2016\%%d\STUDENTS\%%e\  > nul 2>&1
shortcut /F:"%USERPROFILE%\Desktop\Dropbox2016\%%d\SAC.lnk" /A:c /T:\\8807fs2\Dropbox2016\%%d\SAC\%%e\  > nul 2>&1
shortcut /F:"%USERPROFILE%\Desktop\Dropbox2016\%%d\Resources.lnk" /A:c /T:\\8807fs2\Dropbox2016\%%d\Resources\ > nul 2>&1
shortcut /F:"%USERPROFILE%\Desktop\Dropbox2016\%%d\studentZone.lnk" /A:c /T:\\8807fs2\Dropbox2016\%%d\studentzone\ > nul 2>&1

)

cd .. > nul 2>&1
del sctemp /q /s  > nul 2>&1



exit

 

13. Dropbox Teacher side BATCH

mkdir %USERPROFILE%\Desktop\Dropbox2016\ 
mkdir %USERPROFILE%\sctemp
copy \\8807dc01\tools\* %USERPROFILE%\sctemp /y > nul 2>&1
cd /d %USERPROFILE%\sctemp
for /f "tokens=1-2 delims=, " %%d in (TeacherClass.csv) do if /i %%e== %username%  shortcut /F:"%USERPROFILE%\Desktop\Dropbox2016\%%d.lnk" /A:c /T:<FileServer>\Dropbox2016\%%d\  > nul 2>&1
cd ..
del sctemp /q /s

 

code:

 

 

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today47
Yesterday83
This week671
This month1967
Total416327

Visitor Info

  • IP: 54.227.51.103
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

1
Online

2017-12-17

Login