public JsonResult GetVendorUploadDetails(string fileName, string type,string file)
{
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
string path = Path.Combine(Server.MapPath("~/VendorReportsExcelTemplate"), fileName);
//var existingFile = new FileInfo(path);
DataTable dtVendor = new DataTable();
DataTable dtErrorRec = new DataTable();
Vendor ObjVd = new Vendor();
var VendorInfo = new VendorInfo();
using (var pck = new OfficeOpenXml.ExcelPackage())
{
try
{
var workBook = pck.Workbook;
if (workBook != null)
{
using (var stream = System.IO.File.OpenRead(path))
{
pck.Load(stream);
}
var currentWorksheet = pck.Workbook.Worksheets.First();
int Count=0; DataSet ds = new DataSet();
Count = ValidateExcel(type, currentWorksheet, Count, ds);
if (Count > 0)
{
string data = JsonConvert.SerializeObject(ds, Formatting.Indented);
return Json(data);
}
dtVendor = CreateVendorDataTable();
dtErrorRec = CreateErrorDataTable();
for (int rowNumber = ExcelStartRow + 1; rowNumber <= currentWorksheet.Dimension.End.Row; rowNumber++)
// read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
{
//Column 4 : VendorRefNo And Column 5 : Amount ARE MANDITORY
if (currentWorksheet.Cells[rowNumber, 4].Value != null && currentWorksheet.Cells[rowNumber, 5].Value != null)
{
try
{
DataRow dr = dtVendor.NewRow();
currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
dtVendor.Rows.Add(dr);
}
catch (Exception)
{
DataRow dr = dtErrorRec.NewRow();
currentWorksheet.Cells[rowNumber, 1].Style.Numberformat.Format = "mm/dd/yyyy";
dr["Date"] = (currentWorksheet.Cells[rowNumber, 1].Value == null ? DBNull.Value.ToString() : currentWorksheet.Cells[rowNumber, 1].Text);
dr["UsedBy"] = (currentWorksheet.Cells[rowNumber, 2].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 2].Value);
dr["Description"] = (currentWorksheet.Cells[rowNumber, 3].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 3].Value);
dr["ReferenceNo"] = (currentWorksheet.Cells[rowNumber, 4].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 4].Value);
dr["Amount"] = (currentWorksheet.Cells[rowNumber, 5].Value == null ? DBNull.Value : currentWorksheet.Cells[rowNumber, 5].Value);
dtErrorRec.Rows.Add(dr);
}
}
}
}
ObjVd.dtvendor = dtVendor;
ObjVd.dtErrorRec = dtErrorRec;
ObjVd.RecordCount = dtVendor.Rows.Count;
ObjVd.Type = type;
ObjVd.Name = file;
string UserIDSession = Convert.ToString(System.Web.HttpContext.Current.Session["UserID"]);
bool checkForEmptyExcel = (dtVendor.Rows.Count == 0 && dtErrorRec.Rows.Count == 0) ? false : true;
if (!string.IsNullOrEmpty(UserIDSession))
{
if (checkForEmptyExcel)
{
ObjVd.UploadedBy = Convert.ToInt32(UserIDSession);
VendorInfo = ObjVd.InsertVendorRecords(ObjVd);
}
}
}
catch (Exception ex)
{
log.Error("Error: " + ex);
VendorInfo.ExceptionMessage = "exception";
}
finally
{
log.Debug("End: " + methodBase.Name);
if (System.IO.File.Exists(path))
System.IO.File.Delete(path);
}
}
return Json(VendorInfo);
}
private void FetchVendorDetails(VendorInfo data, SqlDataReader reader, SafeDataReader safe)
{
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
//Matched DI Records
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchMatchedVendorDetails(ObjGetVendor, safe);
data.MatchedDIList.Add(ObjGetVendor);
}
//UnMatched DI Records
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchUnMatchedVendorDetails(ObjGetVendor, safe);
data.UnMatchedDIList.Add(ObjGetVendor);
}
//Over Billed
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchMatchedVendorDetails(ObjGetVendor, safe);
data.OverBilledList.Add(ObjGetVendor);
}
//Billed Discrepancy
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchDiscrepancyVendorDetails(ObjGetVendor, safe, false);
data.BilledDiscrepancyList.Add(ObjGetVendor);
}
//Unbilled Discrepancy
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchDiscrepancyVendorDetails(ObjGetVendor, safe, true);
data.UnbilledDiscrepancyList.Add(ObjGetVendor);
}
//Multple Discrepancy
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchMultiDiscrepancyVendorDetails(ObjGetVendor, safe, false);
data.MultpleDiscrepancy.Add(ObjGetVendor);
}
//Method is Use to Calculate Multiple Discripancy
ProcessMultipleDiscrepancy(data.MultpleUpdatedDiscrepancy, data.MultpleDiscrepancy);
//Error List
reader.NextResult();
while (reader.Read())
{
var ObjGetVendor = new Vendor();
ObjGetVendor.FetchErrorList(ObjGetVendor, safe);
data.ErrorList.Add(ObjGetVendor);
}
//File Name
reader.NextResult();
while (reader.Read())
{
data.FileName = Convert.ToString(reader["FileName"]);
}
}
catch (Exception ex)
{
log.Error("Error: " + ex);
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public VendorInfo UplateMultipleDiscrepancy(Vendor objVd)
{
var data = new VendorInfo();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
SqlParameter[] sqlParams = new SqlParameter[4];
sqlParams[0] = new SqlParameter("@tblVendor", SqlDbType.Structured)
{
Value = objVd.dtvendor
};
sqlParams[1] = new SqlParameter("@VRID", objVd.VRID);
sqlParams[2] = new SqlParameter("@CreatedBy", objVd.UploadedBy);
sqlParams[3] = new SqlParameter("@Action", objVd.Name);
var reader = SqlHelper.ExecuteReader(ConnectionUtility.GetConnectionString(), CommandType.StoredProcedure, "SpUpdate_MultiBilled_Discrepancy", sqlParams);
// reader.NextResult();
var safe = new SafeDataReader(reader);
FetchVendorDetails(data, reader, safe);
return data;
}
catch (Exception ex)
{
log.Error("Error: " + ex);
return data;
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public VendorInfo InsertVendorRecords(Vendor ObjVendor)
{
//int result = 0;
var data = new VendorInfo();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
SqlParameter[] sqlParams = new SqlParameter[6];
sqlParams[0] = new SqlParameter("@tblVendor", SqlDbType.Structured)
{
Value = ObjVendor.dtvendor
};
sqlParams[1] = new SqlParameter("@TBLErrorVENDOR", SqlDbType.Structured)
{
Value = ObjVendor.dtErrorRec
};
sqlParams[2] = new SqlParameter("@Type", ObjVendor.Type);
sqlParams[3] = new SqlParameter("@UploadedBy", ObjVendor.UploadedBy);
sqlParams[4] = new SqlParameter("@RecordCount", ObjVendor.RecordCount);
sqlParams[5] = new SqlParameter("@FileName", ObjVendor.Name);
var reader = SqlHelper.ExecuteReader(ConnectionUtility.GetConnectionString(), CommandType.StoredProcedure, "SpInsertVendorDetails", sqlParams);
// reader.NextResult();
var safe = new SafeDataReader(reader);
while (reader.Read())
{
data.IsAlreadyUploaded = Convert.ToString(reader["IsAlreadyUplaoded"]);
}
reader.NextResult();
FetchVendorDetails(data, reader, safe);
return data;
}
catch (Exception ex)
{
log.Error("Error: " + ex);
data.ExceptionMessage = "exception";
return data;
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public VendorInfo getVendorUploadedDetails(Vendor ObjVendor)
{
//int result = 0;
var data = new VendorInfo();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
SqlParameter[] sqlParams = new SqlParameter[4];
sqlParams[0] = new SqlParameter("@VRID", ObjVendor.VRID);
sqlParams[1] = new SqlParameter("@GroupName", ObjVendor.GroupName);
sqlParams[2] = new SqlParameter("@VENDORREF", ObjVendor.VenderReferenceID);
sqlParams[3] = new SqlParameter("@ClientName", ObjVendor.ClientName);
var reader = SqlHelper.ExecuteReader(ConnectionUtility.GetConnectionString(), CommandType.StoredProcedure, "SpGetVendorUploadDetails", sqlParams);
// reader.NextResult();
var safe = new SafeDataReader(reader);
FetchVendorDetails(data, reader, safe);
return data;
}
catch (Exception ex)
{
log.Error("Error: " + ex);
return data;
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
/// <summary>
/// Description : To Get Vendor Upload Details By Type
/// Created By : Pavan
/// Created Date : 6 FEB 2015
/// Modified By :
/// Modified Date:
/// </summary>
public static VendorInfo SpGetVendorUploadedDetailsByType(string Type)
{
var data = new VendorInfo();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@Type", Type);
var reader = SqlHelper.ExecuteReader(ConnectionUtility.GetConnectionString(), CommandType.StoredProcedure, "[SpGetVendorUploadedDetailsByType]", sqlParams);
var safe = new SafeDataReader(reader);
while (reader.Read())
{
var vendor = new Vendor();
vendor.FetchVendorReportType1(vendor, safe);
data.VendorList.Add(vendor);
}
return data;
}
catch (Exception ex)
{
log.Error("Error: " + ex);
return data;
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public static VendorInfo GetAllVendorDetails(int startPage, int resultPerPage, string OrderBy,string FromDate,string ToDate)
{
var data = new VendorInfo();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
SqlParameter[] sqlParams = new SqlParameter[5];
sqlParams[0] = new SqlParameter("@startPage", startPage);
sqlParams[1] = new SqlParameter("@resultPerPage", resultPerPage);
sqlParams[2] = new SqlParameter("@OrderBy", OrderBy);
sqlParams[3] = new SqlParameter("@FromDate", HelperClasses.ConvertDateFormat(FromDate));
sqlParams[4] = new SqlParameter("@ToDate", HelperClasses.ConvertDateFormat(ToDate));
var reader = SqlHelper.ExecuteReader(ConnectionUtility.GetConnectionString(), CommandType.StoredProcedure, "[SpGetAllVendors]", sqlParams);
var safe = new SafeDataReader(reader);
while (reader.Read())
{
var vendor = new Vendor();
vendor.FetchService(vendor, safe);
data.VendorList.Add(vendor);
data.VendorCount = Convert.ToInt32(reader["VendorCount"]);
}
return data;
}
catch (Exception ex)
{
log.Error("Error: " + ex);
return data;
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public ActionResult FileUpload(string Title)
{
var ObjVendorInfo = new VendorInfo();
if (Request.Files["file"].ContentLength > 0)
{
string extension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
string path = string.Empty;
if (extension.ToUpper() == ".XLSX" || extension.ToUpper() == ".XLS")
{
try
{
string FileName = DateTime.Now.Ticks + extension;
path = string.Format("{0}{1}", Server.MapPath("~/VendorReportsExcelTemplate/"), FileName);
Request.Files["file"].SaveAs(path);
TempData["FileName"] = FileName + "|" + Title + "|" + Request.Files["file"].FileName;
TempData["Message"] = "FileUploaded";
}
catch (Exception)
{
TempData["AlertMessage"] = "error";
if (System.IO.File.Exists(path))
System.IO.File.Delete(path);
}
}
else
TempData["Message"] = "FileNotValid";
}
return RedirectToAction("VendorUpload");
}
public JsonResult UpdateMultipleDI(List<DIFields> DIFields, string VendorID, string Action)
{
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
try
{
int checkSession = UserLogin.AuthenticateRequest();
if (checkSession == 0)
{
return Json(checkSession);
}
else
{
Vendor objVd = new Vendor();
var VendorInfo = new VendorInfo();
DataTable dt = HelperClasses.ListToDataTable<DIFields>(DIFields);
objVd.dtvendor = dt;
objVd.VRID = Convert.ToInt32(VendorID);
objVd.UploadedBy = Convert.ToInt32(Session["UserID"]);
objVd.Name = Action;
VendorInfo = objVd.UplateMultipleDiscrepancy(objVd);
return Json(VendorInfo);
}
}
catch (Exception ex)
{
log.Error("Error: " + ex);
return Json("");
}
finally
{
log.Debug("End: " + methodBase.Name);
}
}
public JsonResult LoadVendorUploadHistory(string VRID, string GroupCode, string ClientName, string VendorRef)
{
var objVendor = new Vendor();
System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame();
System.Reflection.MethodBase methodBase = stackFrame.GetMethod();
log.Debug("Start: " + methodBase.Name);
var VendorInfo = new VendorInfo();
using (var pck = new OfficeOpenXml.ExcelPackage())
{
try
{
string UserIDSession = Convert.ToString(System.Web.HttpContext.Current.Session["UserID"]);
if (!string.IsNullOrEmpty(UserIDSession))
{
objVendor.VRID = Convert.ToInt32(VRID);
objVendor.GroupName=GroupCode;
objVendor.ClientName = ClientName;
objVendor.VenderReferenceID = HttpUtility.UrlDecode(VendorRef);
VendorInfo = objVendor.getVendorUploadedDetails(objVendor);
}
}
catch (Exception ex)
{
log.Error("Error: " + ex);
VendorInfo.ExceptionMessage = "exception";
}
}
log.Debug("End: " + methodBase.Name);
return Json(VendorInfo);
}
