private void XMLGenerate_SAPInsert(ProductGroupViewModels model)
{
try
{
ApplicationUser currentUser = ApplicationUserManager.GetApplicationUser(User.Identity.Name, HttpContext.GetOwinContext());
//unique id generation
Guid GuidRandomNo = Guid.NewGuid();
string UniqueID = GuidRandomNo.ToString();
//fill view model
Viewmodel_AddProductGroup xmlAddProductGroup = new Viewmodel_AddProductGroup();
xmlAddProductGroup.UniqueID = UniqueID.ToString();
xmlAddProductGroup.Productgroup_Name = model.ProductGroup.Product_Group_Name;
xmlAddProductGroup.CreatedUser = currentUser.Id.ToString();
xmlAddProductGroup.CreatedBranch = currentUser.Created_Branch_Id.ToString();
xmlAddProductGroup.CreatedDateTime = DateTime.Now.ToString();
//generate xml
productgroupRepository.GenerateXML(xmlAddProductGroup, UniqueID);
}
catch (Exception ex)
{
ExceptionHandler.LogException(ex);
ViewBag.AppErrorMessage = ex.Message;
}
}
public ActionResult Upload(HttpPostedFileBase file)
{
if (file != null && file.ContentLength > 0)
{
try
{
ApplicationUser currentUser = ApplicationUserManager.GetApplicationUser(User.Identity.Name, HttpContext.GetOwinContext());
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
string fileName = System.IO.Path.GetFileName(Request.Files["file"].FileName.ToString());
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
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["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();
string exquery;
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++;
}
DataSet ds = new DataSet();
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
exquery = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(exquery, excelConnection1))
{
dataAdapter.Fill(ds); //fill dataset
}
if (ds != null)
{
#region Check Product Group Name
foreach (DataRow dr in ds.Tables[0].Rows)
{
string mExcelPrGrp_Name = Convert.ToString(dr["Product Group Name"]);
if (mExcelPrGrp_Name != null && mExcelPrGrp_Name != "")
{
var data = productgroupRepository.CheckDuplicateName(mExcelPrGrp_Name);
if (data != null)
{
return(Json(new { success = true, Message = "Product Group Name: " + mExcelPrGrp_Name + " - already exists in the master." }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(Json(new { success = false, Error = "Product Group name cannot be null it the excel sheet" }, JsonRequestBehavior.AllowGet));
}
}
#endregion
#region Check Level
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["Level"].ToString() != null && dr["Level"].ToString() != "")
{
int mExcelProdGrp_Level = Convert.ToInt32(dr["Level"]);
if (mExcelProdGrp_Level >= 0 && mExcelProdGrp_Level <= 100)
{
}
else
{
return(Json(new { success = true, Message = "Allowed range for Level is 0 to 100" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(Json(new { success = false, Error = "Level cannot be null it the excel sheet" }, JsonRequestBehavior.AllowGet));
}
}
#endregion
# region Already exists in sheet
int i = 1;
int ii = 1;
string itemc = string.Empty;
foreach (DataRow dr in ds.Tables[0].Rows)
{
itemc = Convert.ToString(dr["Product Group Name"]);
if ((itemc == null) || (itemc == ""))
{
}
else
{
foreach (DataRow drd in ds.Tables[0].Rows)
{
if (ii == i)
{
}
else
{
if (itemc == Convert.ToString(drd["Product Group Name"]))
{
return(Json(new { success = true, Message = "Product Group Name: " + itemc + " - already exists in the excel." }, JsonRequestBehavior.AllowGet));
}
}
ii = ii + 1;
}
}
i = i + 1;
ii = 1;
}
#endregion
#region BulkInsert
if (ds.Tables[0].Rows.Count > 0)
{
List <ProductGroup> mlist = new List <ProductGroup>();
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
ProductGroup mItem = new ProductGroup();
if (ds.Tables[0].Rows[j]["Product Group Name"] != null)
{
mItem.Product_Group_Name = ds.Tables[0].Rows[j]["Product Group Name"].ToString();
}
if (ds.Tables[0].Rows[j]["Level"] != null)
{
mItem.Level = Convert.ToInt32(ds.Tables[0].Rows[j]["Level"]);
}
mItem.IsActive = "Y";
mItem.Created_User_Id = currentUser.Id; // 1; //GetUserId();
mItem.Created_Branc_Id = currentUser.Created_Branch_Id; // 2; //GetBranchId();
mItem.Created_Dte = DateTime.Now;
mlist.Add(mItem);
//unique id generation
Guid GuidRandomNo = Guid.NewGuid();
string UniqueID = GuidRandomNo.ToString();
//fill viewmodel
Viewmodel_AddProductGroup xmlAddProductGroup = new Viewmodel_AddProductGroup();
xmlAddProductGroup.UniqueID = UniqueID.ToString();
xmlAddProductGroup.Productgroup_Name = ds.Tables[0].Rows[j]["Product Group Name"].ToString();
xmlAddProductGroup.CreatedUser = currentUser.Id.ToString();
xmlAddProductGroup.CreatedBranch = currentUser.Created_Branch_Id.ToString();
xmlAddProductGroup.CreatedDateTime = DateTime.Now.ToString();
//generate xml
productgroupRepository.GenerateXML(xmlAddProductGroup, UniqueID);
}
if (productgroupRepository.InsertFileUploadDetails(mlist))
{
//return Json(new { success = true, Message = mlist.Count + " Records Uploaded Successfully" }, JsonRequestBehavior.AllowGet);
return(RedirectToAction("Index", "ProductGroup"));
}
}
else
{
return(Json(new { success = false, Error = "Excel file is empty" }, JsonRequestBehavior.AllowGet));
}
#endregion
}
else
{
return(Json(new { success = false, Error = "Excel file is empty" }, JsonRequestBehavior.AllowGet));
}
}
}