public string UploadFile()
{
if (HttpContext.Current.Request.Files.AllKeys.Any())
{
logger.Info("start Item Upload Exel File: ");
var identity = User.Identity as ClaimsIdentity;
int compid = 1, userid = 0;
// Access claims
foreach (Claim claim in identity.Claims)
{
if (claim.Type == "compid")
{
compid = int.Parse(claim.Value);
}
if (claim.Type == "userid")
{
userid = int.Parse(claim.Value);
}
}
// Get the uploaded image from the Files collection
System.Web.HttpPostedFile httpPostedFile = HttpContext.Current.Request.Files["file"];
if (httpPostedFile != null)
{
// Validate the uploaded image(optional)
byte[] buffer = new byte[httpPostedFile.ContentLength];
using (BinaryReader br = new BinaryReader(httpPostedFile.InputStream))
{
br.Read(buffer, 0, buffer.Length);
}
XSSFWorkbook hssfwb;
using (MemoryStream memStream = new MemoryStream())
{
BinaryFormatter binForm = new BinaryFormatter();
memStream.Write(buffer, 0, buffer.Length);
memStream.Seek(0, SeekOrigin.Begin);
hssfwb = new XSSFWorkbook(memStream);
string sSheetName = hssfwb.GetSheetName(0);
ISheet sheet = hssfwb.GetSheet(sSheetName);
AuthContext context = new AuthContext();
IRow rowData;
ICell cellData = null;
try
{
List <ItemMaster> ItemCollection = new List <ItemMaster>();
List <Category> wcat = new List <Category>();
for (int iRowIdx = 0; iRowIdx <= sheet.LastRowNum; iRowIdx++)
{
if (iRowIdx == 0)
{
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
string field = string.Empty;
field = rowData.GetCell(0).ToString();
if (field != "CityName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(1).ToString();
if (field != "Cityid")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(2).ToString();
if (field != "CategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(3).ToString();
if (field != "CategoryCode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(4).ToString();
if (field != "SubcategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(5).ToString();
if (field != "SubsubcategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(6).ToString();
if (field != "BrandCode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(7).ToString();
if (field != "itemname")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(8).ToString();
if (field != "itemcode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(9).ToString();
if (field != "Number")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(10).ToString();
if (field != "SellingSku")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(11).ToString();
if (field != "price")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(12).ToString();
if (field != "PurchasePrice")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(13).ToString();
if (field != "UnitPrice")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(14).ToString();
if (field != "MinOrderQty")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(15).ToString();
if (field != "SellingUnitName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(16).ToString();
if (field != "PurchaseMinOrderQty")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(17).ToString();
if (field != "StoringItemName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(18).ToString();
if (field != "PurchaseSku")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(19).ToString();
if (field != "PurchaseUnitName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(20).ToString();
if (field != "SupplierName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(21).ToString();
if (field != "SUPPLIERCODES")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(22).ToString();
if (field != "BaseCategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(23).ToString();
if (field != "TGrpName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(24).ToString();
if (field != "TotalTaxPercentage")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(25).ToString();
if (field != "WarehouseName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(26).ToString();
if (field != "HindiName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(27).ToString();
if (field != "SizePerUnit")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(28).ToString();
if (field != "Barcode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(29).ToString();
if (field != "Active")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(30).ToString();
if (field != "Deleted")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(31).ToString();
if (field != "Margin")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(32).ToString();
if (field != "PromoPoint")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
}
}
else
{
rowData = sheet.GetRow(iRowIdx);
cellData = rowData.GetCell(0);
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
ItemMaster item = new ItemMaster();
try
{
item.CompanyId = 1;
Category cat = null;
SubCategory subcat = null;
SubsubCategory subsubcat = new SubsubCategory();
BaseCategory basecat = null;
//City Name -1
cellData = rowData.GetCell(0);
col0 = cellData == null ? "" : cellData.ToString();
if (col0.Trim() == "")
{
break;
}
item.CityName = col0.Trim();
//City Code -2
cellData = rowData.GetCell(1);
col1 = cellData == null ? "" : cellData.ToString();
string CityCode = col1.Trim();
City city = context.Cities.Where(x => x.CityName.Trim().ToLower() == item.CityName.Trim().ToLower()).SingleOrDefault();
if (city != null)
{
item.Cityid = city.Cityid;
}
//Warehouse Name 26
cellData = rowData.GetCell(25);
col25 = cellData == null ? "" : cellData.ToString();
item.WarehouseName = col25.Trim();
Warehouse w = context.Warehouses.Where(x => x.WarehouseName.Trim().ToLower() == item.WarehouseName.Trim().ToLower()).SingleOrDefault();
if (w != null)
{
item.warehouse_id = w.Warehouseid;
}
//base Category Name - 23
cellData = rowData.GetCell(22);
col22 = cellData == null ? "" : cellData.ToString();
string basecategory = col22.Trim();
basecat = context.BaseCategoryDb.Where(x => x.BaseCategoryName.ToLower().Equals(basecategory.ToLower())).SingleOrDefault();
if (basecat == null && col22.Trim() != "")
{
basecat = new BaseCategory();
basecat.BaseCategoryName = basecategory;
basecat.CompanyId = item.CompanyId;
if (w != null)
{
basecat.Warehouseid = w.Warehouseid;
}
else
{
basecat.Warehouseid = 1;
}
basecat.IsActive = true;
basecat = context.AddBaseCategory(basecat);
}
item.BaseCategoryName = basecat.BaseCategoryName;
item.BaseCategoryid = basecat.BaseCategoryId;
//Category Name -3
cellData = rowData.GetCell(2);
col2 = cellData == null ? "" : cellData.ToString();
item.CategoryName = col2.Trim();
//Category Code -4
cellData = rowData.GetCell(3);
col3 = cellData == null ? "" : cellData.ToString();
string CategoryCode = col3.Trim();
cat = context.Categorys.Where(x => x.CategoryName.Trim().Equals(col2.Trim())).SingleOrDefault();
if (cat == null && col3.Trim() != "")
{
cat = new Category();
cat.CategoryName = col2.Trim();
cat.Code = CategoryCode;
cat.BaseCategoryId = basecat.BaseCategoryId;
cat.CompanyId = item.CompanyId;
if (w != null)
{
cat.Warehouseid = w.Warehouseid;
}
else
{
cat.Warehouseid = 0;
}
cat.CompanyId = compid;
cat.IsActive = true;
cat = context.AddCategory(cat);
var wc = wcat.Where(x => x.Categoryid == cat.Categoryid).FirstOrDefault();
if (wc == null)
{
wcat.Add(cat);
}
}
else
{
if (w != null)
{
cat.Warehouseid = w.Warehouseid;
}
else
{
cat.Warehouseid = 0;
}
cat.CompanyId = compid;
cat.IsActive = true;
var wc = wcat.Where(x => x.Categoryid == cat.Categoryid).FirstOrDefault();
if (wc == null)
{
wcat.Add(cat);
}
}
item.Categoryid = cat.Categoryid;
item.CategoryName = cat.CategoryName;
//SubCategory Name - 5
cellData = rowData.GetCell(4);
col4 = cellData == null ? "" : cellData.ToString();
item.SubcategoryName = col4.Trim();
subcat = context.SubCategorys.Where(x => x.SubcategoryName.Trim().ToLower().Equals(col4.Trim().ToLower()) && x.Categoryid.Equals(cat.Categoryid)).SingleOrDefault();
if (subcat == null && col4.Trim() != "")
{
subcat = new SubCategory();
subcat.CompanyId = compid;
subcat.SubcategoryName = col4;
subcat.CategoryName = cat.CategoryName;
subcat.Categoryid = cat.Categoryid;
subcat.IsActive = true;
subcat = context.AddSubCategory(subcat);
}
item.SubCategoryId = subcat.SubCategoryId;
item.SubcategoryName = subcat.SubcategoryName;
//Brand Name ie. SubSubCategory - 6
cellData = rowData.GetCell(5);
col5 = cellData == null ? "" : cellData.ToString();
item.SubsubcategoryName = col5.Trim();
//Brand Code -7
cellData = rowData.GetCell(6);
col6 = cellData == null ? "" : cellData.ToString();
string BrandCode = col6.Trim();
subsubcat = context.SubsubCategorys.Where(x => x.SubsubcategoryName.Trim().ToLower().Equals(col5.Trim().ToLower()) && x.Categoryid.Equals(x.Categoryid) && x.SubCategoryId.Equals(subcat.SubCategoryId)).FirstOrDefault();
if (subsubcat == null && col6.Trim() != "")
{
subsubcat = new SubsubCategory();
subsubcat.CompanyId = compid;
subsubcat.SubsubcategoryName = col5;
subsubcat.SubCategoryId = subcat.SubCategoryId;
subsubcat.Code = col6;
subsubcat.SubcategoryName = col4;
subsubcat.CategoryName = cat.CategoryName;
subsubcat.Categoryid = cat.Categoryid;
subsubcat.IsActive = true;
subsubcat = context.AddSubsubCat(subsubcat);
}
item.SubsubCategoryid = subsubcat.SubsubCategoryid;
item.SubsubcategoryName = subsubcat.SubsubcategoryName;
//Item Name - 8
cellData = rowData.GetCell(7);
col7 = cellData == null ? "" : cellData.ToString();
item.itemname = col7.Trim();
//Item Code - 9
cellData = rowData.GetCell(8);
col8 = cellData == null ? "" : cellData.ToString();
item.itemcode = col8.Trim();
//Item Number - 10
cellData = rowData.GetCell(9);
col9 = cellData == null ? "" : cellData.ToString();
if (col9.Trim() == "" || col9 == null || col9 == "null")
{
break;
}
item.Number = col9.Trim();
//selling Sku - 11
cellData = rowData.GetCell(10);
col10 = cellData == null ? "" : cellData.ToString();
if (col10.Trim() == "" || col10 == null || col10 == "null")
{
break;
}
item.SellingSku = col10.Trim();
//MRP -12
cellData = rowData.GetCell(11);
col11 = cellData == null ? "" : cellData.ToString();
try {
item.price = Convert.ToDouble(col11);
}
catch (Exception e) { }
// Purchase Price - 13
cellData = rowData.GetCell(12);
col12 = cellData == null ? "" : cellData.ToString();
try
{
item.PurchasePrice = Convert.ToDouble(col12);
}
catch (Exception e) { }
//Selling Price - 14
cellData = rowData.GetCell(13);
col13 = cellData == null ? "" : cellData.ToString();
try
{
item.UnitPrice = Convert.ToDouble(col13);
}
catch (Exception e) { }
//selling MOQ -15
cellData = rowData.GetCell(14);
col14 = cellData == null ? "" : cellData.ToString();
try
{
item.MinOrderQty = Convert.ToInt32(col14);
}
catch (Exception e) { }
//selling Unit -16
cellData = rowData.GetCell(15);
col15 = cellData == null ? "" : cellData.ToString();
item.SellingUnitName = col15.Trim();
//ConversionFactor ,purchase qty -17
cellData = rowData.GetCell(16);
col16 = cellData == null ? "" : cellData.ToString();
try
{
item.PurchaseMinOrderQty = int.Parse(col16);
}
catch (Exception e) { }
//StoringItemName -18
cellData = rowData.GetCell(17);
col17 = cellData == null ? "" : cellData.ToString();
item.StoringItemName = col17.Trim();
//purchase sku -19
cellData = rowData.GetCell(18);
col18 = cellData == null ? "" : cellData.ToString();
if (col18.Trim() == "" || col18 == null || col18 == "null")
{
break;
}
item.PurchaseSku = col18.Trim();
//Purchase Unit -20
cellData = rowData.GetCell(19);
col19 = cellData == null ? "" : cellData.ToString();
item.PurchaseUnitName = col19.Trim();
//Supplier -21
cellData = rowData.GetCell(20);
col20 = cellData == null ? "" : cellData.ToString();
string spname = col20.Trim();
//Supplier code -22
cellData = rowData.GetCell(21);
col21 = cellData == null ? "" : cellData.ToString();
string sp_code = col21.Trim();
Supplier supplier = null;
supplier = context.Suppliers.Where(x => x.SUPPLIERCODES.Trim().Equals(sp_code.Trim())).SingleOrDefault();
if (supplier == null && col21.Trim() != "")
{
supplier = new Supplier();
supplier.Name = spname;
supplier.SUPPLIERCODES = sp_code;
supplier.CompanyId = compid;
supplier = context.AddSupplier(supplier);
}
item.SupplierId = supplier.SupplierId;
item.SupplierName = supplier.Name;
item.SUPPLIERCODES = supplier.SUPPLIERCODES;
// TaxGroup -24
cellData = rowData.GetCell(23);
col23 = cellData == null ? "" : cellData.ToString();
TaxGroup grp = context.DbTaxGroup.Where(x => x.TGrpName.Trim().ToLower().Equals(col23.Trim().ToLower())).SingleOrDefault();
item.TGrpName = col23.Trim();
if (grp != null)
{
item.GruopID = grp.GruopID;
}
// TotalTaxPercentage -25
cellData = rowData.GetCell(24);
col24 = cellData == null ? "" : cellData.ToString();
try
{
item.TotalTaxPercentage = double.Parse(col24);
}
catch (Exception e) { }
//item.active = true;
//hindi Nmae -27
cellData = rowData.GetCell(26);
col26 = cellData == null ? "" : cellData.ToString();
item.HindiName = col26.Trim();
//Sizeperunit Area-28
cellData = rowData.GetCell(27);
col27 = cellData == null ? "" : cellData.ToString();
try
{
item.SizePerUnit = double.Parse(col27);
}
catch (Exception e) {
item.SizePerUnit = 0;
}
//barcode -29
cellData = rowData.GetCell(28);
col28 = cellData == null ? "" : cellData.ToString();
item.Barcode = col28.Trim();
cellData = rowData.GetCell(29);
col29 = cellData == null ? "" : cellData.ToString();
if (col29.Trim().ToLower() == "true")
{
item.active = true;
}
else if (col29.Trim().ToLower() == "false")
{
item.active = false;
}
else
{
item.active = false;
}
cellData = rowData.GetCell(30);
col30 = cellData == null ? "" : cellData.ToString();
if (col30.Trim().ToLower() == "true")
{
item.Deleted = true;
}
else if (col30.Trim().ToLower() == "false")
{
item.Deleted = false;
}
else
{
item.Deleted = false;
}
cellData = rowData.GetCell(31);
col31 = cellData == null ? "" : cellData.ToString();
try
{
item.Margin = double.Parse(col31);
}
catch (Exception e)
{
item.Margin = 0;
}
cellData = rowData.GetCell(32);
col32 = cellData == null ? "" : cellData.ToString();
try
{
item.promoPoint = int.Parse(col32);
}
catch (Exception e)
{
item.promoPoint = 0;
}
//barcode -29
cellData = rowData.GetCell(33);
col33 = cellData == null ? "" : cellData.ToString();
item.HSNCode = col33.Trim();
ItemCollection.Add(item);
}
catch (Exception ex)
{
logger.Error("Error adding item in collection " + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace + item.itemname);
}
}
}
}
List <WarehouseCategory> wc1 = new List <WarehouseCategory>();
foreach (Category c in wcat)
{
Warehouse w = context.Warehouses.Where(x => x.Warehouseid == c.Warehouseid).SingleOrDefault();
WarehouseCategory wc = new WarehouseCategory();
wc.Categoryid = c.Categoryid;
wc.CategoryName = c.CategoryName;
wc.Warehouseid = w.Warehouseid;
wc.WarehouseName = w.WarehouseName;
wc.Stateid = w.Stateid;
wc.Cityid = w.Cityid;
wc.IsVisible = true;
wc1.Add(wc);
}
context.AddWarehouseCategory(wc1, "");
context.AddBulkItemMaster(ItemCollection);
try
{
var ass = context.AllWarehouseCategory(compid).ToList();
foreach (var a in ass)
{
AngularJSAuthentication.API.Helper.refreshItemMaster(a.Warehouseid, a.Categoryid);
}
}
catch (Exception ex)
{
logger.Error("Error in update Cache " + ex.Message);
}
}
catch (Exception ex)
{
logger.Error("Error in update Cache " + ex.Message);
}
}
var FileUrl = Path.Combine(HttpContext.Current.Server.MapPath("~/UploadedFiles"), httpPostedFile.FileName);
httpPostedFile.SaveAs(FileUrl);
}
}
if (msgitemname != null)
{
return(msgitemname);
}
msg = "Your Exel data is succesfully saved";
return(msg);
}