Migrate EAS to MIS

EAS_MIS

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
namespace importer
{
    class Program
    {


        static string dir = @"EAS_to_mis\";
        static Hashtable subjects = new Hashtable();
        static Hashtable ratings = new Hashtable();

        static void get_codes()
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;
            string workbookPath = dir + @"SUBJECT CODES.xlsx";
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                     true, false, 0, true, false, false);
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
            Excel.Range excelCell =
        (Excel.Range)excelWorksheet.get_Range("G2", "G200");
            int j = 2;
            foreach (string s in excelCell.Value2)
            {
                string cell_s = "B" + j;
                j++;
                Excel.Range vCell =
    (Excel.Range)excelWorksheet.get_Range(cell_s, cell_s);
                if ((s != "") && (s != null))
                {
                    Console.WriteLine(s + "\t" + vCell.Value2);
                    subjects.Add(s, (int)vCell.Value2);
                }
            }
            excelWorkbook.Close(false);
            excelApp.Quit();
        }
        static void get_rating()
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;
            string workbookPath = dir + @"r.xlsx";
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                     true, false, 0, true, false, false);
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
            Excel.Range excelCell =
        (Excel.Range)excelWorksheet.get_Range("F2", "F200");
            int j = 2;
            foreach (string s in excelCell.Value2)
            {
                string cell_s = "H" + j;
                j++;
                Excel.Range vCell = (Excel.Range)excelWorksheet.get_Range(cell_s, cell_s);
                if ((s != "") && (s != null))
                {
                    Console.WriteLine(s + "\t" + vCell.Value2);
                    if (ratings.ContainsKey(s) == false)
                        ratings.Add(s, (int)vCell.Value2);
                }

            }
            excelWorkbook.Close(false);
            excelApp.Quit();
        }
        static void get_EAS()
        {
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;
            string workbookPath = dir + @"EAS result.xls";
            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                     0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                     true, false, 0, true, false, false);
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets[1];
            StreamWriter sw = new StreamWriter(dir + @"output.txt");
            StreamWriter fsw = new StreamWriter(dir + @"failed.txt");
            sw.AutoFlush = true;
            fsw.AutoFlush = true;
            int j = 1;
            for (; ; )
            {
                char i = 'A';
                j++;
                i++;
                string cell_s = i.ToString() + j;

                Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range(cell_s, cell_s);
                string name = excelCell.Value2;
                if ((name == null) || (name == "")) break;

                for (; ; )
                {
                    i++;
                    cell_s = i.ToString() + 1;
                    excelCell = (Excel.Range)excelWorksheet.get_Range(cell_s, cell_s);
                    string subject = (string)excelCell.Value2;
                    if ((subject == null) || (subject == "")) break;
                    //now get the تقدير 
                    cell_s = i.ToString() + j;
                    excelCell = (Excel.Range)excelWorksheet.get_Range(cell_s, cell_s);
                    string rating = (string)excelCell.Value2;
                    //sw.WriteLine(name + "\t" + subject + "\t" + rating);
                    string q = get_query(name, subject, rating);
                    if (q == null)
                        fsw.WriteLine(name + "\t" + subject + "\t" + rating);
                    else sw.WriteLine(q);

                }
            }

            excelWorkbook.Close(false);
            excelApp.Quit();
            sw.Close();
            fsw.Close();
        }

        private static string get_query(string name, string subject, string rating)
        {
            string q = @"UPDATE  ED_STUD_SUBJECT 
            set  ED_CODE_RATING_ID = {0}
            WHERE ED_SUBJECT_ID={1}
            AND ED_STUD_PHASE_ID IN (SELECT G.ED_STUD_PHASE_ID FROM ED_STUD_PHASE G
            WHERE G.ED_STUD_ID IN (SELECT T.ED_STUD_ID FROM ED_STUD  T
            WHERE T.FULL_NAME_AR LIKE '%{2}%') );";
            int r = -1;
            int s = -1;
            if (ratings.ContainsKey(rating))r=(int)ratings[rating];
            if (subjects.ContainsKey(subject)) s = (int)subjects[subject]; 
            if ((r == -1) || (s == -1)) return null;
            return String.Format(q, r, s, name);
        }
        static void Main(string[] args)
        {
            get_codes();
            get_rating();
            get_EAS();
        }
    }
}