public ActionResult ViewDataOfTransDatabase(string sortorder, string currentFilter, string MonthSearch, int?page, string YearSearch, string searchString)
{
CCIRepository repository = CCIRepository.CreateRepository();
List <Transport> list = ViewDetails();
ViewBag.CurrentSort = sortorder;
ViewBag.PhoneSortParm = String.IsNullOrEmpty(sortorder) ? "Phone_desc" : "";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
var transportdetails = from _model in list
select _model;
if (!String.IsNullOrEmpty(MonthSearch) && !String.IsNullOrEmpty(YearSearch))
{
transportdetails = repository.GetMnthAndYear(MonthSearch, YearSearch);
//transportdetails = transportdetails.Where(A => A.PresentDate.ToString().Contains(MonthSearch.ToString()) && A.PresentDate.ToString().Contains(YearSearch.ToString()));
}
else if (!String.IsNullOrEmpty(searchString))
{
transportdetails = transportdetails.Where(A => A.Name.ToString().Contains(searchString.ToString()));
}
int pageSize = 5;
int pageNumber = (page ?? 1);
return(View(transportdetails.ToPagedList(pageNumber, pageSize)));
}
//public ActionResult Disassociate(string provider, string providerUserId)
//{
// string ownerAccount = OAuthWebSecurity.GetUserName(provider, providerUserId);
// ManageMessageId? message = null;
// // Only disassociate the account if the currently logged in user is the owner
// if (ownerAccount == User.Identity.Name)
// {
// // Use a transaction to prevent the user from deleting their last login credential
// using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
// {
// bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
// if (hasLocalAccount || OAuthWebSecurity.GetAccountsFromUserName(User.Identity.Name).Count > 1)
// {
// OAuthWebSecurity.DeleteAccount(provider, providerUserId);
// scope.Complete();
// message = ManageMessageId.RemoveLoginSuccess;
// }
// }
// }
// return RedirectToAction("Manage", new { Message = message });
//}
//
// GET: /Account/Manage
//public ActionResult Manage(ManageMessageId? message)
//{
// ViewBag.StatusMessage =
// message == ManageMessageId.ChangePasswordSuccess ? "Your password has been changed."
// : message == ManageMessageId.SetPasswordSuccess ? "Your password has been set."
// : message == ManageMessageId.RemoveLoginSuccess ? "The external login was removed."
// : "";
// ViewBag.HasLocalPassword = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
// ViewBag.ReturnUrl = Url.Action("Manage");
// return View();
//}
//
// POST: /Account/Manage
public ActionResult Manage(LocalPasswordModel model)
{
LoginModel model1 = new LoginModel();
CCIRepository _repository = CCIRepository.CreateRepository();
bool hasLocalAccount = AuthCheck(model.OldPassword);
// bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
ViewBag.HasLocalPassword = hasLocalAccount;
ViewBag.ReturnUrl = Url.Action("Manage");
if (hasLocalAccount)
{
if (ModelState.IsValid)
{
// ChangePassword will throw an exception rather than return false in certain failure scenarios.
bool changePasswordSucceeded;
try
{
changePasswordSucceeded = _repository.UpadatePass(model);
//changePasswordSucceeded = WebSecurity.ChangePassword(User.Identity.Name, model.OldPassword, model.NewPassword);
}
catch (Exception)
{
changePasswordSucceeded = false;
}
if (changePasswordSucceeded)
{
return(RedirectToAction("Manage", new { Message = ManageMessageId.ChangePasswordSuccess }));
}
else
{
ModelState.AddModelError("", "The current password is incorrect or the new password is invalid.");
}
}
}
else
{
ModelState.AddModelError("", "The current password is incorrect or the new password is invalid.");
// User does not have a local password so remove any validation errors caused by a missing
// OldPassword field
//ModelState state = ModelState["OldPassword"];
//if (state != null)
//{
// state.Errors.Clear();
//}
//if (ModelState.IsValid)
//{
// try
// {
// WebSecurity.CreateAccount(User.Identity.Name, model.NewPassword);
// return RedirectToAction("Manage", new { Message = ManageMessageId.SetPasswordSuccess });
// }
// catch (Exception)
// {
// ModelState.AddModelError("", String.Format("Unable to create local account. An account with the name \"{0}\" may already exist.", User.Identity.Name));
// }
//}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
public ActionResult ImportExcelFile(HttpPostedFileBase file, FormCollection collection)
{
CCIRepository repository = CCIRepository.CreateRepository();
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string filename = Path.GetFileName(Request.Files["FileUpload"].FileName);
string fileLocation = string.Format("{0}{1}", Server.MapPath("/App_Data/ExcelFiles/"), filename);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
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;
foreach (DataRow row in dt.Rows)
{
excelsheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelsheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
Transport _model = new Transport();
_model.UploadedDate = DateTime.Now;
_model.DateOfCreation = Convert.ToDateTime(collection["DateOfCreation"].ToString());
var temp = repository.InsertTransImportDate(_model);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
_model.PresentDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["Date"]);
_model.Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]);
_model.Place = Convert.ToString(ds.Tables[0].Rows[i]["Place"]);
_model.Driver = Convert.ToString(ds.Tables[0].Rows[i]["Driver"]);
_model.VehicleNumber = Convert.ToInt32(ds.Tables[0].Rows[i]["Vehiclenumber"]);
_model.Amount = Convert.ToInt32(ds.Tables[0].Rows[i]["Amount"]);
_model.CostCentre = Convert.ToInt32(ds.Tables[0].Rows[i]["Cost centre"]);
_model.ImportDateId = temp;
repository.SaveTransData(_model);
}
}
else
{
ModelState.AddModelError("", "Please Select a excel file");
return(null);
}
}
return(RedirectToAction("Success", "Transport"));
}
public ActionResult ImportExcelFile(HttpPostedFileBase file, FormCollection collection)
{
CCIRepository repository = CCIRepository.CreateRepository();
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string filename = Path.GetFileName(Request.Files["FileUpload"].FileName);
string fileLocation = string.Format("{0}{1}", Server.MapPath("/UploadedFile/"), filename);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
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;
foreach (DataRow row in dt.Rows)
{
excelsheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelsheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
AirtelManagementModel _model = new AirtelManagementModel();
_model.UploadedDate = DateTime.Now;
_model.DateOfCreation = Convert.ToDateTime(collection["DateOfCreation"].ToString());
var temp = repository.InsertImportDate(_model);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//AirtelManagementModel _model = new AirtelManagementModel();
_model.MobileAcNumber = Convert.ToInt64(ds.Tables[0].Rows[i]["accountno"]);
_model.AirtelNumber = Convert.ToInt64(ds.Tables[0].Rows[i]["airtelnumber"]);
_model.OneTime = Convert.ToInt32(ds.Tables[0].Rows[i]["Onetimecharges"]);
_model.MonthlyCharges = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["monthlycharges"]);
_model.CallCharges = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["Callcharges"]);
_model.ValueAddedServices = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["Valueaddeservices"]);
_model.MobileInternetUsage = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["mobileinternetusage"]);
_model.Roaming = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["roaming"]);
_model.Discounts = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["discounts"]);
_model.Taxes = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["taxes"]);
_model.TotalCharges = (float)Convert.ToDouble(ds.Tables[0].Rows[i]["totalcharges"]);
_model.WhoUploaded = System.Web.HttpContext.Current.User.Identity.Name;
_model.ImportDateId = temp;
//_model.UploadedDate = DateTime.Now;
//_model.DateOfCreation = collection["DateOfCreation"].ToString();
repository.SaveData(_model);
//MemoryStream streampost = new MemoryStream();
//WebClient clientpost = new WebClient();
//clientpost.Headers["Content-Type"] = "application/json";
//DataContractJsonSerializer serializerpost = new DataContractJsonSerializer(typeof(AirtelManagementModel));
//serializerpost.WriteObject(streampost, _model);
//dynamic serviceUrl = string.Empty;
//string url = string.Format("{0},Save Details", serviceUrl);
//byte[] datapost = clientpost.UploadData(url, "Post", streampost.ToArray());
//streampost = new MemoryStream(datapost);
//serializerpost = new DataContractJsonSerializer(typeof(string));
//string result = (string)serializerpost.ReadObject(streampost);
}
}
else
{
ModelState.AddModelError("", "Please Select a excel file");
return(null);
}
}
return(RedirectToAction("Success", "Success"));
}
public ActionResult ViewDataOfDatabase(string currentFilter, string searchString, int?page, FormCollection collection, int?Year, int?Month, String City)
{
CCIRepository _repository = CCIRepository.CreateRepository();
AirtelManagementModel _Airtelmodel = new AirtelManagementModel();
IEnumerable <CityListClass> CityList = _repository.GetCities();
IEnumerable <SelectListItem> CityNames = from c in CityList
select new SelectListItem()
{
Value = c.CityName.ToString(),
Text = c.CityName.ToString(),
Selected = c.CityName == Request["CityNames"],
};
ViewBag.CityList = CityNames;
IEnumerable <clsYearOfDate> SelectList = GetYears();
//IEnumerable<MonthListClass> SelectMonthList = GetMonths(YearId);
IEnumerable <SelectListItem> Yearitems = (from v in SelectList
select new SelectListItem()
{
Value = v.YearSelectedId.ToString(),
Text = v.YearOfDate.ToString(),
Selected = v.YearOfDate == Request["Yearitems"],
});
ViewBag.SelectList = Yearitems;
int DateId = 0;
string CityName = string.Empty;
//int SelectedYear = Year;
//int SelectedMonth = Month;
CityName = City;
DateId = _repository.GetImportDateId(Year, Month);
//ViewBag.SelectedYear = SelectedYear;
//ViewBag.SelectedMonth = SelectedMonth;
ViewBag.SelectedCity = CityName;
//IEnumerable<SelectListItem> MonthItems = (from m in SelectMonthList
// select new SelectListItem()
// {
// Value = m.MonthSelectedId.ToString(),
// Text = m.MonthName,
// });
//ViewBag.SelectMonthList = MonthItems;
IEnumerable <SelectListItem> MonthItems = Enumerable.Empty <SelectListItem>();
ViewBag.SelectMonthList = MonthItems;
List <AirtelManagementModel> list = ViewDetails();
//ViewBag.CurrentSort = sortorder;
//ViewBag.PhoneSortParm = String.IsNullOrEmpty(sortorder) ? "Phone_desc" : "";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
//if(searchString!=null)
//{
ViewBag.Year = Year;
ViewBag.Month = Month;
ViewBag.City = City;
ViewBag.CurrentFilter = searchString;
var airteldetails = from _model in list
select _model;
if (!String.IsNullOrEmpty(searchString) && DateId != 0 && !String.IsNullOrEmpty(CityName))
{
airteldetails = _repository.FilterAirtelDetails(searchString, DateId, CityName);
int PageSize = 5;
int PageNumber = (page ?? 1);
return(View(airteldetails.ToPagedList(PageNumber, PageSize)));
}
//airteldetails=airteldetails.OrderByDescending(A=>A.AirtelNumber);
int pageSize = 5;
int pageNumber = (page ?? 1);
//return View(airteldetails.ToList());
return(View(airteldetails.ToPagedList(pageNumber, pageSize)));
//}
//if (list.Count > 0)
//{
// var airteldetails = from _model in list
// select _model;
// return View(airteldetails.ToPagedList(pageNumber,pageSize));
//}
//else
//{
// ModelState.AddModelError("Error", "No Data found in Database");
// return RedirectToAction("ImportExcelFile", "AirtelManagement");
//}
}
