[Authorize]
public class ImportDataController : Controller
{
SMSNotificationService smsNotification = new SMSNotificationService();
//
GET: ImportData
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult ImportUsers(HttpPostedFileBase file, string sendSms)
{
try
{
string ImportSMSAlertMessage = new LookupService().GetConfigurationKeyValue("ImportSMSAlertMessage");
string role_id = new UserService().GetUserRoleByRoleKey("guide");
string senderName = "daifnaqaba";
string usersAlreadyExists = "";
DataSet ds = new DataSet();
string sendSMS = Request["sendSms"];
if (Request.Files["file"].ContentLength >
0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation =
Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString
= string.Empty;
excelConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + fileLocation + ";Extended Properties=\"Excel
12.0;HDR=Yes;IMEX=2\"";
//connection String
for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + fileLocation + ";Extended Properties=\"Excel
8.0;HDR=Yes;IMEX=2\"";
}
//connection String
for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + fileLocation + ";Extended Properties=\"Excel
12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to
Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in
temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] =
row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from
[{0}]",
excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query,
excelConnection1))
{
dataAdapter.Fill(ds);
}
excelConnection.Close();
}
for (int i = 1; i <
ds.Tables[0].Rows.Count; i++)
{
string conn = ConfigurationManager.ConnectionStrings["AverosAVLProd"].ConnectionString;
SqlConnection con = new SqlConnection(conn);
string first_name = Convert.ToString(ds.Tables[0].Rows[i][0]);
string email = Convert.ToString(ds.Tables[0].Rows[i][1]);
string password = Convert.ToString(ds.Tables[0].Rows[i][2]);
string
user_external_identifier = email;
string contact_number =
password;
DateTime created_on = DateTime.Now;
bool userExists = new UserService().CheckIfUserExists(email);
if (!userExists)
{
string query = "Insert into
users(user_external_identifier,first_name,last_name,email,password,contact_number,role_id,created_on,updated_on,status)
Values('" + user_external_identifier + "',N'" + first_name + "','','" + email + "','" + password + "','" + contact_number + "'," + role_id + ",'" + created_on.ToString("yyyy-MM-dd
HH:mm:ss") + "','" + created_on.ToString("yyyy-MM-dd HH:mm:ss") + "','A')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
int isSaved = 0;
isSaved =
cmd.ExecuteNonQuery();
if (isSaved != 0)
{
TempData["SuccessMessage"] = "users created
successfully.";
}
con.Close();
}
else
{
usersAlreadyExists
+= email + ",
";
}
if (sendSms == "on")
{
string contactNumber =
smsNotification.AdjustPhoneNumber(contact_number);
string
ImportSMSAlertMessageTranslated = ImportSMSAlertMessage.Replace("{U}", email).Replace("{P}", password);
string sentStatus =
smsNotification.SendSMS(contactNumber, ImportSMSAlertMessageTranslated,
senderName);
if (true || sentStatus != "")
{
string query = "Insert into
sms_logs(sms_status, sent_number,sent_message,sent_date) Values(N'" +
sentStatus.ToString() + "','" + contact_number + "',N'" +
ImportSMSAlertMessageTranslated + "','" +
created_on.ToString("yyyy-MM-dd HH:mm:ss") + "')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
if (!string.IsNullOrEmpty(usersAlreadyExists))
{
TempData["UsersAlreadyExist"] =
usersAlreadyExists;
}
return RedirectToAction("Index");
}
catch (Exception ex)
{
new LogService().LogException(ex);
TempData["SuccessMessage"] = ex.Message;
return RedirectToAction("Index");
}
finally
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
}
}
}
