private string GetConnectionString() { return(CommonThings.GetConnectionString()); }
//public string FinalInsert() //{ // DateTime flightdeparture = (DateTime.Now.AddHours(2)); // DateTime startTime, endTime, aStartTime, aEndTime; // string sd, ed, asd, aed; // sd = DateTime.Now.ToString("MM/dd/yyyy"); // ed = DateTime.Now.ToString("MM/dd/yyyy"); // asd = DateTime.Now.ToString("MM/dd/yyyy"); // aed = DateTime.Now.ToString("MM/dd/yyyy"); // string st, et, ast, aet; // string start, end, aStart, aEnd; // string _path = @"C:\Users\hpadmin\Desktop\Standard\modified.xlsx"; // FileStream stream = new FileStream(_path, FileMode.Open, FileAccess.Read); // SqlConnection con = new SqlConnection("Server=HIB30BWAX2; Initial Catalog = OneTimeProgress; User ID = sa; Password = Passw0rd@12;"); // var reader = ExcelReaderFactory.CreateReader(stream); // if (reader.Name == "Tasks")//Tasks Page // { // int j; // reader.Read(); // con.Open(); // while (reader.Read()) // { // SqlCommand cmd = new SqlCommand("InsertIntoTaskList @flightNumber,@taskDetail,@duration,@startTime,@endTime,@statusOfTask,@actualStartTime,@actualEndTime,@timeDifference,@department,@staffName", con); // cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(0)); // cmd.Parameters.AddWithValue("@taskDetail", reader.GetString(1)); // cmd.Parameters.AddWithValue("@duration", reader.GetDouble(2)); // st = reader.GetDateTime(3).ToString("hh:mm"); // start = sd + " " + st; // startTime = Convert.ToDateTime(start); // cmd.Parameters.AddWithValue("@startTime", startTime); // et = reader.GetDateTime(4).ToString("hh:mm"); // end = ed + " " + et; // endTime = Convert.ToDateTime(end); // cmd.Parameters.AddWithValue("@endTime", endTime); // cmd.Parameters.AddWithValue("@statusOfTask", reader.GetString(5)); // ast = reader.GetDateTime(6).ToString("hh:mm"); // aStart = asd + " " + ast; // aStartTime = Convert.ToDateTime(aStart); // cmd.Parameters.AddWithValue("@actualStartTime", aStartTime); // aet = reader.GetDateTime(7).ToString("hh:mm"); // aEnd = aed + " " + aet; // aEndTime = Convert.ToDateTime(end); // cmd.Parameters.AddWithValue("@actualEndTime", aEndTime); // cmd.Parameters.AddWithValue("@timeDifference", reader.GetDouble(8)); // cmd.Parameters.AddWithValue("@department", reader.GetString(9)); // cmd.Parameters.AddWithValue("@staffName", reader.GetString(10)); // j = cmd.ExecuteNonQuery(); // } // con.Close(); // } // reader.NextResult(); // if (reader.Name == "Flights")//Flights sheet // { // DateTime taskStartTime, departureTime; // string ts, dt; // ts = DateTime.Now.ToString("MM/dd/yyyy"); // dt = DateTime.Now.ToString("MM/dd/yyyy"); // string tst, tdt; // string tstartTime, tdepTime; // int j; // reader.Read(); // con.Open(); // while (reader.Read()) // { // SqlCommand cmd = new SqlCommand("InsertIntoAllFlightDetails @equipmentName,@flightNumber,@airCraftModel,@currentStation,@bayNumber,@taskStartTime,@departureTime", con); // cmd.Parameters.AddWithValue("@equipmentName", reader.GetString(0)); // cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(1)); // cmd.Parameters.AddWithValue("@airCraftModel", reader.GetString(2)); // cmd.Parameters.AddWithValue("@currentStation", reader.GetString(3)); // cmd.Parameters.AddWithValue("@bayNumber", reader.GetDouble(4)); // tst = reader.GetDateTime(5).ToString("hh:mm"); // tstartTime = ts + " " + tst; // taskStartTime = Convert.ToDateTime(tstartTime); // cmd.Parameters.AddWithValue("@taskStartTime", taskStartTime); // tdt = reader.GetDateTime(6).ToString("hh:mm"); // tdepTime = dt + " " + tdt; // departureTime = Convert.ToDateTime(tdepTime); // cmd.Parameters.AddWithValue("@departureTime", departureTime); // j = cmd.ExecuteNonQuery(); // } // con.Close(); // } // reader.NextResult(); // if (reader.Name == "Employees")//Employees sheet // { // int j; // reader.Read(); // con.Open(); // while (reader.Read()) // { // SqlCommand cmd = new SqlCommand("InsertIntoLoginDetails @email,@secretPassword,@userName,@userType,@UserDepartment", con); // cmd.Parameters.AddWithValue("@email", reader.GetString(0)); // cmd.Parameters.AddWithValue("@secretPassword", reader.GetString(1)); // cmd.Parameters.AddWithValue("@userName", reader.GetString(2)); // cmd.Parameters.AddWithValue("@userType", reader.GetString(3)); // cmd.Parameters.AddWithValue("@UserDepartment", reader.GetString(4)); // j = cmd.ExecuteNonQuery(); // } // con.Close(); // } // reader.NextResult(); // if (reader.Name == "Departments")//For airport manager view // { // DateTime dSheduledStartTime, dSheduledEndTime, dActualStartTime, dActualEndTime; // string currentDate; // currentDate = DateTime.Now.ToString("MM/dd/yyyy"); // string tst, tdt; // string dsstartTime, dsendTime; // int j; // reader.Read(); // con.Open(); // while (reader.Read()) // { // SqlCommand cmd = new SqlCommand("InsertIntoDepartments @flightNumber,@departmentName,@superVisorName,@sheduledStartTime,@sheduledEndTime,@sheduledDuration,@actualStartTime,@actualEndTime,@statusOfDepartment", con); // cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(0)); // cmd.Parameters.AddWithValue("@departmentName", reader.GetString(1)); // cmd.Parameters.AddWithValue("@superVisorName", reader.GetString(2)); // tst = reader.GetDateTime(3).ToString("hh:mm"); // dsstartTime = currentDate + " " + tst; // dSheduledStartTime = Convert.ToDateTime(dsstartTime); // cmd.Parameters.AddWithValue("@sheduledStartTime", dSheduledStartTime); // tdt = reader.GetDateTime(4).ToString("hh:mm"); // dsendTime = currentDate + " " + tdt; // dSheduledEndTime = Convert.ToDateTime(dsendTime); // cmd.Parameters.AddWithValue("@sheduledEndTime", dSheduledEndTime); // cmd.Parameters.AddWithValue("@sheduledDuration", reader.GetInt16(5)); // cmd.Parameters.AddWithValue("@statusOfDepartment", reader.GetString(8)); // j = cmd.ExecuteNonQuery(); // } // con.Close(); // } // return "Inserted"; //} public string InsertData(string path) { string _path = path; FileStream stream = new FileStream(_path, FileMode.Open, FileAccess.Read); SqlConnection con = new SqlConnection(CommonThings.GetConnectionString()); var reader = ExcelReaderFactory.CreateReader(stream); DateTime For101, For121, For343, For360, For144, For511, taskStartTime, departureTime, tempo; For101 = DateTime.Now; For121 = DateTime.Now; For343 = DateTime.Now; For360 = DateTime.Now; For144 = DateTime.Now; For511 = DateTime.Now; DateTime dateTimeStarter = DateTime.Now; int adder; if (dateTimeStarter.Minute <= 30) { adder = 30 - dateTimeStarter.Minute; dateTimeStarter = dateTimeStarter.AddMinutes(adder); tempo = dateTimeStarter; } else { adder = 60 - dateTimeStarter.Minute; dateTimeStarter = dateTimeStarter.AddMinutes(adder); tempo = dateTimeStarter; } if (reader.Name == "Flights")//Flights sheet { double taskStart, Departure; int j; reader.Read(); con.Open(); while (reader.Read()) { SqlCommand cmd = new SqlCommand("InsertIntoAllFlightDetails @equipmentName,@flightNumber,@airCraftModel,@currentStation,@bayNumber,@taskStartTime,@departureTime", con); cmd.Parameters.AddWithValue("@equipmentName", reader.GetString(0)); cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(1)); cmd.Parameters.AddWithValue("@airCraftModel", reader.GetString(2)); cmd.Parameters.AddWithValue("@currentStation", reader.GetString(3)); cmd.Parameters.AddWithValue("@bayNumber", reader.GetDouble(4)); taskStart = reader.GetDouble(7); taskStartTime = dateTimeStarter.AddMinutes(taskStart); dateTimeStarter = tempo; cmd.Parameters.AddWithValue("@taskStartTime", taskStartTime); Departure = reader.GetDouble(8); departureTime = dateTimeStarter.AddMinutes(Departure); dateTimeStarter = tempo; cmd.Parameters.AddWithValue("@departureTime", departureTime); if (reader.GetDouble(1) == 101) { For101 = departureTime; } else if (reader.GetDouble(1) == 121) { For121 = departureTime; } else if (reader.GetDouble(1) == 343) { For343 = departureTime; } else if (reader.GetDouble(1) == 360) { For360 = departureTime; } else if (reader.GetDouble(1) == 144) { For144 = departureTime; } else { For511 = departureTime; } j = cmd.ExecuteNonQuery(); } con.Close(); } reader.NextResult(); if (reader.Name == "Tasks")//Tasks sheet { int j; double taskDuration, sheduledStart, actualStart, actualTimeDifference; reader.Read(); con.Open(); while (reader.Read()) { SqlCommand cmd = new SqlCommand("InsertIntoTaskList @flightNumber,@taskDetail,@duration,@startTime,@endTime,@statusOfTask,@actualStartTime,@actualEndTime,@timeDifference,@department,@staffName", con); cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(0)); cmd.Parameters.AddWithValue("@taskDetail", reader.GetString(1)); taskDuration = reader.GetDouble(2); actualTimeDifference = reader.GetDouble(8); sheduledStart = reader.GetDouble(11); actualStart = reader.GetDouble(12); DateTime sst, set, ast, aet; cmd.Parameters.AddWithValue("@duration", taskDuration); cmd.Parameters.AddWithValue("@statusOfTask", reader.GetString(5)); if (reader.GetDouble(0) == 101) { sst = For101.AddMinutes(-sheduledStart); set = For101.AddMinutes(-sheduledStart + taskDuration); ast = For101.AddMinutes(-actualStart); aet = For101.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } else if (reader.GetDouble(0) == 121) { sst = For121.AddMinutes(-sheduledStart); set = For121.AddMinutes(-sheduledStart + taskDuration); ast = For121.AddMinutes(-actualStart); aet = For121.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } else if (reader.GetDouble(0) == 343) { sst = For343.AddMinutes(-sheduledStart); set = For343.AddMinutes(-sheduledStart + taskDuration); ast = For343.AddMinutes(-actualStart); aet = For343.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } else if (reader.GetDouble(0) == 360) { sst = For360.AddMinutes(-sheduledStart); set = For360.AddMinutes(-sheduledStart + taskDuration); ast = For360.AddMinutes(-actualStart); aet = For360.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } else if (reader.GetDouble(0) == 144) { sst = For144.AddMinutes(-sheduledStart); set = For144.AddMinutes(-sheduledStart + taskDuration); ast = For144.AddMinutes(-actualStart); aet = For144.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } else { sst = For511.AddMinutes(-sheduledStart); set = For511.AddMinutes(-sheduledStart + taskDuration); ast = For511.AddMinutes(-actualStart); aet = For511.AddMinutes(-actualStart + actualTimeDifference); cmd.Parameters.AddWithValue("@startTime", sst); cmd.Parameters.AddWithValue("@endTime", set); cmd.Parameters.AddWithValue("@actualStartTime", ast); cmd.Parameters.AddWithValue("@actualEndTime", aet); } cmd.Parameters.AddWithValue("@timeDifference", actualTimeDifference); cmd.Parameters.AddWithValue("@department", reader.GetString(9)); cmd.Parameters.AddWithValue("@staffName", reader.GetString(10)); j = cmd.ExecuteNonQuery(); } con.Close(); } reader.NextResult(); if (reader.Name == "Employees")//Employees sheet { int j; reader.Read(); con.Open(); while (reader.Read()) { SqlCommand cmd = new SqlCommand("InsertIntoLoginDetails @email,@secretPassword,@userName,@userType,@UserDepartment", con); cmd.Parameters.AddWithValue("@email", reader.GetString(0)); cmd.Parameters.AddWithValue("@secretPassword", reader.GetString(1)); cmd.Parameters.AddWithValue("@userName", reader.GetString(2)); cmd.Parameters.AddWithValue("@userType", reader.GetString(3)); cmd.Parameters.AddWithValue("@UserDepartment", reader.GetString(4)); j = cmd.ExecuteNonQuery(); } con.Close(); } reader.NextResult(); if (reader.Name == "Departments")//Departments sheet { int j; reader.Read(); con.Open(); while (reader.Read()) { SqlCommand cmd = new SqlCommand("InsertIntoDepartments @flightNumber,@departmentName,@superVisorName,@sheduledStartTime,@sheduledEndTime,@sheduledDuration,@actualStartTime,@actualEndTime,@statusOfDepartment", con); cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(0)); cmd.Parameters.AddWithValue("@departmentName", reader.GetString(1)); cmd.Parameters.AddWithValue("@superVisorName", reader.GetString(2)); if (reader.GetDouble(0) == 121) { cmd.Parameters.AddWithValue("@sheduledStartTime", For121.AddMinutes(-(reader.GetDouble(9)))); cmd.Parameters.AddWithValue("@sheduledEndTime", For121.AddMinutes(-(reader.GetDouble(9)) + reader.GetDouble(5))); } if (reader.GetDouble(0) == 343) { cmd.Parameters.AddWithValue("@sheduledStartTime", For343.AddMinutes(-(reader.GetDouble(9)))); cmd.Parameters.AddWithValue("@sheduledEndTime", For343.AddMinutes(-(reader.GetDouble(9)) + reader.GetDouble(5))); } cmd.Parameters.AddWithValue("@sheduledDuration", reader.GetDouble(5)); cmd.Parameters.AddWithValue("@actualStartTime", reader.GetDateTime(6).ToString("hh:mm")); cmd.Parameters.AddWithValue("@actualEndTime", reader.GetDateTime(7).ToString("hh:mm")); cmd.Parameters.AddWithValue("@statusOfDepartment", reader.GetString(8)); j = cmd.ExecuteNonQuery(); } con.Close(); } reader.NextResult(); if (reader.Name == "DummyData") { int j; reader.Read(); con.Open(); while (reader.Read()) { SqlCommand cmd = new SqlCommand("InsertIntoDummyTasks @flightNumber,@departmentName,@superVisor,@duration,@startTime,@endTime,@actualStartTime,@actualEndTime,@statusOfDepartment", con); cmd.Parameters.AddWithValue("@flightNumber", reader.GetDouble(0)); cmd.Parameters.AddWithValue("@departmentName", reader.GetString(1)); cmd.Parameters.AddWithValue("@superVisor", reader.GetString(2)); cmd.Parameters.AddWithValue("@duration", reader.GetDouble(3)); if (reader.GetDouble(0) == 101) { cmd.Parameters.AddWithValue("@startTime", For101.AddMinutes(-reader.GetDouble(9))); cmd.Parameters.AddWithValue("@endTime", For101.AddMinutes(-reader.GetDouble(9) + reader.GetDouble(3))); cmd.Parameters.AddWithValue("@actualStartTime", For101.AddMinutes(-reader.GetDouble(10))); cmd.Parameters.AddWithValue("@actualEndTime", For101.AddMinutes(-reader.GetDouble(11))); } else if (reader.GetDouble(0) == 121) { cmd.Parameters.AddWithValue("@startTime", For121.AddMinutes(-reader.GetDouble(9))); cmd.Parameters.AddWithValue("@endTime", For121.AddMinutes(-reader.GetDouble(9) + reader.GetDouble(3))); cmd.Parameters.AddWithValue("@actualStartTime", For121.AddMinutes(-reader.GetDouble(10))); cmd.Parameters.AddWithValue("@actualEndTime", For121.AddMinutes(-reader.GetDouble(11))); } else if (reader.GetDouble(0) == 343) { cmd.Parameters.AddWithValue("@startTime", For343.AddMinutes(-reader.GetDouble(9))); cmd.Parameters.AddWithValue("@endTime", For343.AddMinutes(-reader.GetDouble(9) + reader.GetDouble(3))); cmd.Parameters.AddWithValue("@actualStartTime", For343.AddMinutes(-reader.GetDouble(10))); cmd.Parameters.AddWithValue("@actualEndTime", For343.AddMinutes(-reader.GetDouble(11))); } else if (reader.GetDouble(0) == 360) { cmd.Parameters.AddWithValue("@startTime", For360.AddMinutes(-reader.GetDouble(9))); cmd.Parameters.AddWithValue("@endTime", For360.AddMinutes(-reader.GetDouble(9) + reader.GetDouble(3))); cmd.Parameters.AddWithValue("@actualStartTime", For360.AddMinutes(-reader.GetDouble(10))); cmd.Parameters.AddWithValue("@actualEndTime", For360.AddMinutes(-reader.GetDouble(11))); } else { cmd.Parameters.AddWithValue("@startTime", For144.AddMinutes(-reader.GetDouble(9))); cmd.Parameters.AddWithValue("@endTime", For144.AddMinutes(-reader.GetDouble(9) + reader.GetDouble(3))); cmd.Parameters.AddWithValue("@actualStartTime", For144.AddMinutes(-reader.GetDouble(10))); cmd.Parameters.AddWithValue("@actualEndTime", For144.AddMinutes(-reader.GetDouble(11))); } cmd.Parameters.AddWithValue("@statusOfDepartment", reader.GetString(8)); j = cmd.ExecuteNonQuery(); } con.Close(); } return("Inserted"); }