public ActionResult Import(int MenuId, int id)//id == DocTypeId
{
Menu menu = new MenuService(_unitOfWork).Find(MenuId);
ImportHeader header = _ImportHeaderservice.GetImportHeaderByName(menu.MenuName);
List <ImportLine> lines = _ImportLineService.GetImportLineList(header.ImportHeaderId).ToList();
Dictionary <int, string> DefaultValues = TempData["ImportLayoutDefaultValues"] as Dictionary <int, string>;
TempData["ImportLayoutDefaultValues"] = DefaultValues;
foreach (var item in lines)
{
if (DefaultValues != null && DefaultValues.ContainsKey(item.ImportLineId))
{
item.DefaultValue = DefaultValues[item.ImportLineId];
}
}
ImportMasterViewModel vm = new ImportMasterViewModel();
if (TempData["closeOnSelectOption"] != null)
{
vm.closeOnSelect = (bool)TempData["closeOnSelectOption"];
}
vm.ImportHeader = header;
vm.ImportLine = lines;
vm.ImportHeaderId = header.ImportHeaderId;
vm.DocTypeId = id;
int ImportMessageCnt = (from H in db.ImportMessage where H.ImportHeaderId == header.ImportHeaderId && H.IsActive == true && H.ValueType == "Error" select H).Count();
ViewBag.ImportMessageCnt = ImportMessageCnt;
return(View(vm));
}
public ActionResult Import(FormCollection form, ImportMasterViewModel vm)
{
string[] StrArr = new string[] { };
//int MenuId = Convert.ToInt32(Request.QueryString["MenuId"]);
int ImportHeaderId = vm.ImportHeaderId;
int DocTypeId = vm.DocTypeId;
string ErrorText = "";
//string WarningText = "";
if (Request.Files.Count == 0 || Request.Files[0].FileName == "")
{
ViewBag.id = form["DocTypeId"];
ModelState.AddModelError("", "Please select file.");
return(View("Import", vm));
}
string ConnectionString = (string)System.Web.HttpContext.Current.Session["DefaultConnectionString"];
SqlConnection sqlConnection = new SqlConnection(ConnectionString);
sqlConnection.Open();
var ImportHeader = new ImportHeaderService(_unitOfWork).Find(ImportHeaderId);
for (int filecnt = 0; filecnt <= Request.Files.Count - 1; filecnt++)
{
var file = Request.Files[filecnt];
string filePath = Request.MapPath(ConfigurationManager.AppSettings["ExcelFilePath"] + file.FileName);
file.SaveAs(filePath);
DataTable RecordList = new DataTable();
if (System.IO.Path.GetExtension(file.FileName).ToUpper() == ".XLS" || System.IO.Path.GetExtension(file.FileName).ToUpper() == ".XLSX")
{
var excel = new ExcelQueryFactory();
excel.FileName = filePath;
RecordList = exceldata(filePath);
}
else if (System.IO.Path.GetExtension(file.FileName).ToUpper() == ".TXT")
{
RecordList = GetDataSourceFromFile(filePath);
}
else
{
ModelState.AddModelError("", "File is not in correct format.");
return(View("Import", vm));
}
if (RecordList.Rows.Count == 0)
{
ModelState.AddModelError("", "There is no records to import.");
return(View("Import", vm));
}
var ImportLineList = _ImportLineService.GetImportLineList(ImportHeaderId).Where(m => m.FileNo == (filecnt + 1));
foreach (var ImportLine in ImportLineList)
{
if (ImportLine.DataType != "Input-Text" && ImportLine.DataType != "Input-Number" && ImportLine.DataType != "Input-Date" && ImportLine.DataType != "Single Select" && ImportLine.DataType != "Multi Select")
{
if (ImportLine.IsVisible == true && RecordList.Columns.Contains(ImportLine.FieldName) == false && ImportLine.FileNo == filecnt + 1)
{
ModelState.AddModelError("", ImportLine.FieldName + " is manadatary for file " + ImportLine.Type + ", but file does not contain this column.");
return(View("Import", vm));
}
if (RecordList.Columns.Contains(ImportLine.FieldName))
{
if (ImportLine.DataType == "Number" && (RecordList.Columns[ImportLine.FieldName].DataType.Name.ToString() != "Decimal" &&
RecordList.Columns[ImportLine.FieldName].DataType.Name.ToString() != "Double" &&
RecordList.Columns[ImportLine.FieldName].DataType.Name.ToString() != "Float" &&
RecordList.Columns[ImportLine.FieldName].DataType.Name.ToString() != "Int"))
{
ModelState.AddModelError("", ImportLine.FieldName + " should be number for file " + ImportLine.Type + ".");
return(View("Import", vm));
}
}
}
if (ImportLine.IsMandatory == true && ImportLine.FileNo == filecnt + 1)
{
for (int i = 0; i <= RecordList.Rows.Count - 1; i++)
{
if (RecordList.Rows[i][ImportLine.FieldName] == null)
{
ModelState.AddModelError("", ImportLine.FieldName + " is manadatary, it is blank at row no." + (i + 2).ToString());
return(View("Import", vm));
}
else if (RecordList.Rows[i][ImportLine.FieldName].ToString() == "")
{
ModelState.AddModelError("", ImportLine.FieldName + " is manadatary, it is blank at row no." + (i + 2).ToString());
return(View("Import", vm));
}
}
}
if (ImportLine.MaxLength > 0)
{
for (int i = 0; i <= RecordList.Rows.Count - 1; i++)
{
if (RecordList.Rows[i][ImportLine.FieldName].ToString().Length > ImportLine.MaxLength)
{
ModelState.AddModelError("", ImportLine.FieldName + " should be maximum length of " + ImportLine.MaxLength + ", it is exceeding at row no." + (i + 2).ToString());
return(View("Import", vm));
}
}
}
}
if (ImportLineList.Count() > 0)
{
RecordList.Columns.Add("ImportHeaderId");
RecordList.Columns.Add("DocTypeId");
RecordList.Columns.Add("UserName");
RecordList.Columns.Add("SiteId");
RecordList.Columns.Add("DivisionId");
for (int i = 0; i <= RecordList.Rows.Count - 1; i++)
{
//RecordList.Rows[i]["DocTypeId"] = form["DocTypeId"];
RecordList.Rows[i]["ImportHeaderId"] = vm.ImportHeaderId;
RecordList.Rows[i]["DocTypeId"] = DocTypeId;
RecordList.Rows[i]["UserName"] = User.Identity.Name;
RecordList.Rows[i]["SiteId"] = (int)System.Web.HttpContext.Current.Session["SiteId"];
RecordList.Rows[i]["DivisionId"] = (int)System.Web.HttpContext.Current.Session["DivisionId"];
}
foreach (var ImportLine in ImportLineList)
{
if (RecordList.Columns.Contains(ImportLine.FieldName) == false && ImportLine.FileNo == filecnt + 1)
{
RecordList.Columns.Add(ImportLine.FieldName);
if (ImportLine.DataType == "Input-Text" || ImportLine.DataType == "Input-Number" || ImportLine.DataType == "Input-Date" || ImportLine.DataType == "Single Select" || ImportLine.DataType == "Multi Select")
{
for (int i = 0; i <= RecordList.Rows.Count - 1; i++)
{
RecordList.Rows[i][ImportLine.FieldName] = form[ImportLine.FieldName];
}
}
}
}
}
string TempTableName = "#TempTable_" + (filecnt + 1).ToString();
CreateSqlTableFromDataTable(RecordList, TempTableName, sqlConnection);
}
DataSet ds = new DataSet();
using (sqlConnection)
{
//sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("" + ConfigurationManager.AppSettings["DataBaseSchema"] + "." + ImportHeader.SqlProc))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
for (int filecnt = 0; filecnt <= Request.Files.Count - 1; filecnt++)
{
cmd.Parameters.AddWithValue("@TableName_" + (filecnt + 1).ToString(), "#TempTable_" + (filecnt + 1).ToString());
}
cmd.CommandTimeout = 1000;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
//cmd.Connection.Close();
}
}
List <ImportMessage> MsgList = new List <ImportMessage>();
if (ds.Tables.Count > 0)
{
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
ImportMessage Msg = new ImportMessage();
Msg.Head = ds.Tables[0].Rows[i]["Head"].ToString();
Msg.Value = ds.Tables[0].Rows[i]["Value"].ToString();
Msg.ValueType = ds.Tables[0].Rows[i]["ValueType"].ToString();
MsgList.Add(Msg);
}
}
return(View("Result", MsgList));
//if (ds.Tables.Count == 0)
//{
// ViewBag.id = form["DocTypeId"];
// return View("Sucess");
//}
//List<ImportErrors> ImportErrorList = new List<ImportErrors>();
//if (ds.Tables[0].Rows.Count == 0)
//{
// ViewBag.id = form["DocTypeId"];
// return View("Sucess");
//}
//else
//{
// for (int j = 0; j <= ds.Tables[0].Rows.Count - 1; j++)
// {
// if (ds.Tables[0].Rows[j]["ErrorText"].ToString() != "")
// {
// ErrorText = ErrorText + ds.Tables[0].Rows[j]["ErrorText"].ToString() + "." + Environment.NewLine;
// }
// ImportErrors ImportError = new ImportErrors();
// ImportError.ErrorText = ds.Tables[0].Rows[j]["ErrorText"].ToString();
// ImportError.BarCodes = ds.Tables[0].Rows[j]["Head"].ToString();
// ImportErrorList.Add(ImportError);
// }
// if (ErrorText != "")
// {
// ViewBag.Error = ErrorText; // +WarningText;
// ViewBag.id = form["DocTypeId"];
// //string DataTableSessionVarName = "";
// //DataTableSessionVarName = User.Identity.Name.ToString() + "ImportData" + form["DocTypeId"].ToString();
// //Session[DataTableSessionVarName] = dataTable;
// return View("Error", ImportErrorList);
// }
// return View("Sucess");
//}
}
