public StringBuilder GetInwardExcelDataUserBased(string users_Json, string product_Json, string product_type_name, string division_name)
{
try
{
CreateFileName(product_type_name, division_name);
string company_code = _objCurrentInfo.GetCompanyCode();
string[] hiddenColumns = new string[] { };
string[] editableColumnNames = new string[] { };
string[] hiddenColumnNames = new string[] { };
_objExcelConverter = new ExcelFactory();
BLMaster objBlMaster = new BLMaster();
DataSet dsInwardExcelData = new DataSet();
dsInwardExcelData = objBlMaster.GetInwardExcelDataUserBased(company_code, users_Json, product_Json);
if (dsInwardExcelData.Tables != null && dsInwardExcelData.Tables[0].Rows != null && dsInwardExcelData.Tables[0].Rows.Count > 0)
{
dsInwardExcelData.Tables[0].Columns.Add("Quantity", typeof(string));
DataRowCollection dr = dsInwardExcelData.Tables[0].Rows;
DataColumnCollection dc = dsInwardExcelData.Tables[0].Columns;
_objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, _fileNameString.ToString(), dsInwardExcelData, hiddenColumnNames, hiddenColumns, false, editableColumnNames);
}
else
{
StringBuilder str = new StringBuilder("No Data Found.");
return(str);
}
StringBuilder MasterfilenameStrings = GetInwardLookupValuesExcel();
return(_fileNameString.Append("^").Append(MasterfilenameStrings));
}
catch (Exception ex)
{
return(_fileNameString.Append(ex.Message));
}
}
private string GetProductMasterExcel(DataSet dsProdMaster)
{
string company_code = _objCurrentInfo.GetCompanyCode();
string[] hiddenColumns = new string[] { };
string[] editableColumnNames = new string[] { };
string[] hiddenColumnNames = new string[] { };
_objExcelConverter = new ExcelFactory();
_objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, PRODUCT_MASTER_FILE_NAME, dsProdMaster, hiddenColumnNames, hiddenColumns, false, editableColumnNames);
return(PRODUCT_MASTER_FILE_NAME);
}
private string GetEmployeeMasterExcel(DataSet dsEmployeeMaster)
{
string fileNamewithPath = string.Empty;
try
{
string company_code = _objCurrentInfo.GetCompanyCode();
string[] hiddenColumns = new string[] { };
string[] editableColumnNames = new string[] { };
string[] hiddenColumnNames = new string[] { };
_objExcelConverter = new ExcelFactory();
_objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, EMPLOYEE_USER_MASTER_FILE_NAME, dsEmployeeMaster, hiddenColumnNames, hiddenColumns, false, editableColumnNames);
return(EMPLOYEE_USER_MASTER_FILE_NAME);
}
catch (Exception ex)
{
return(ex.Message);
}
}
public ActionResult UploadExcelFile(HttpPostedFileBase fileUpload, HttpPostedFileBase fileUploadUser, FormCollection collection)
{
string fileName = "";
try
{
// Declare and assign the variables.
string errorCode = "-1";
string companyCode = _objCurrentInfo.GetCompanyCode();
string userCode = _objCurrentInfo.GetUserCode();
string subDomain = _objCurrentInfo.GetSubDomain();
string user_Type_Code = string.Empty;
string product_Type_Code = string.Empty;
string inward_Date = "";
string screenMode = collection["ScreenMode"].ToString();
if (screenMode == "USER TYPE")
{
inward_Date = collection["txtInward"].ToString();
fileName = fileUpload.FileName;
}
else
{
inward_Date = collection["txtUserInwardDate"].ToString();
fileName = fileUploadUser.FileName;
}
_objExcelConverter = new ExcelFactory();
// Save the file to server path.
string fileNameWithPath = fileProvider.GetFilePathToSave("ExcelUploadPath", fileName);
if (screenMode == "USER TYPE")
{
fileUpload.SaveAs(fileNameWithPath);
}
else
{
fileUploadUser.SaveAs(fileNameWithPath);
}
// Set Excel column Names.
Hashtable hashExcelColumnsTable = new Hashtable();
hashExcelColumnsTable.Add("Row_No", "");
hashExcelColumnsTable.Add("User_Code", "");
hashExcelColumnsTable.Add("Product_Code", "");
hashExcelColumnsTable.Add("Product_Name", "");
hashExcelColumnsTable.Add("Employee_Name", "");
hashExcelColumnsTable.Add("Employee_Number", "");
hashExcelColumnsTable.Add("UOM_Name", "");
hashExcelColumnsTable.Add("Ref_Key1", "");
hashExcelColumnsTable.Add("Internal_Reference_Key", "");
hashExcelColumnsTable.Add("Region_Name", "");
hashExcelColumnsTable.Add("Quantity", "");
// Check column headers are correct.
if (!_objExcelConverter.IsExcelColumnHeadrsAreCorrect(hashExcelColumnsTable, INWARD_EXCEL_SHEET_NAME, fileNameWithPath))
{
errorCode = "1";
ViewBag.ErrorCode = errorCode;
UploadedFileDelete(fileNameWithPath);
return(View("UploadExcelFile"));
}
// Retrieve columns from excel.
string[] excelRetrieveColumns = new string[] { "Row_No", "User_Code", "Product_Code", "Employee_Number", "Ref_Key1", "Internal_Reference_Key", "Quantity " };
string[] excelHiddenColumns = new string[] { "*" };
// Then convert the excel to datatable.
DataTable dt = _objExcelConverter.ExcelToDataSet(excelRetrieveColumns, INWARD_EXCEL_SHEET_NAME, INWARD_EXCEL_WHERE_QRY, fileNameWithPath, "Quantity");
dt.Columns.Add("BPID", typeof(Guid));
dt.Columns["Internal_Reference_Key"].ColumnName = "Internal_Refer_Key";
dt.Columns["Quantity"].ColumnName = "Qty";
Guid NewId = Guid.NewGuid();
dt.Columns.Add("Company_Code", typeof(string));
dt.Columns.Add("BP_Type", typeof(string));
for (int index = 0; index < dt.Rows.Count; index++)
{
dt.Rows[index]["Company_Code"] = companyCode;
dt.Rows[index]["BPID"] = NewId;
dt.Rows[index]["BP_Type"] = BP_TYPE;
}
try
{
for (int index = 0; index < dt.Rows.Count; index++)
{
Convert.ToInt32(dt.Rows[index]["Row_No"]);
}
}
catch
{
errorCode = "4";
UploadedFileDelete(fileNameWithPath);
ViewBag.ErrorCode = errorCode;
return(View("UploadExcelFile"));
}
// set the error code to view bag. if errorcode is -1 validation is pass, otherwise fail.
ViewBag.ErrorCode = errorCode;
if (errorCode == "-1")
{
// Call the Async method.
Task task = Task.Factory.StartNew(() => _objSPData.InsertInwardBulkUploadAsync(subDomain, dt, companyCode, NewId, userCode, fileName, BP_TYPE, user_Type_Code, product_Type_Code, inward_Date));
}
// _objSPData.InsertInwardBulkUploadAsync(dt, companyCode, Guid.NewGuid(), userCode, fileName, BP_TYPE, user_Type_Code, product_Type_Code, inward_Date);
// Inserts the excel rows in to the Inward staging table.
UploadedFileDelete(fileNameWithPath);
return(View("UploadExcelFile"));
}
catch (Exception ex)
{
string fileNameWithPath = fileProvider.GetFilePathToSave("ExcelUploadPath", fileName);
UploadedFileDelete(fileNameWithPath);
if (ex.GetType() == typeof(System.Data.OleDb.OleDbException))
{
ViewBag.ErrorCode = "3";
return(View("UploadExcelFile"));
}
ViewBag.ErrorCode = ex.Message;
return(View("UploadExcelFile"));
}
}
