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 Vendor FetchMultiDiscrepancyVendorDetails(Vendor vendor, SafeDataReader dr, bool IsUnbilled) { vendor.VenderReferenceID = dr.GetString("VENDORREF"); vendor.VendorAmount = dr.GetDecimal("VENDORAMOUNT"); vendor.DI = dr.GetString("DI"); vendor.DIRef = dr.GetString("DIREF"); vendor.DIAmount = dr.GetDecimal("DIAMOUNT"); vendor.IsBilled = dr.GetBoolean("BILLED"); vendor.Type = dr.GetString("TYPE"); vendor.Discrepancy = (IsUnbilled ? dr.GetDecimal("UnBilledDiscrepancy") : dr.GetDecimal("Discrepancy")); vendor.VRID = dr.GetInt32("VRID"); vendor.VRDID = dr.GetInt32("VRDID"); vendor.ItemCode = dr.GetString("ItemCode"); vendor.ItemNumber = dr.GetString("ItemNumber"); vendor.Status = dr.GetString("STATUS"); vendor.RowNo = dr.GetInt32("RowNo"); vendor.IsDiscVisible = false; vendor.WOCode = dr.GetString("WOCode"); vendor.InvoiceNumber = dr.GetString("InvoiceNumber"); vendor.ClientName = dr.GetString("ClientName"); vendor.Date = dr.GetString("DATE"); vendor.UsedBy = dr.GetString("SIC"); vendor.GroupName = dr.GetString("GroupName"); return vendor; }
private Vendor FetchMatchedVendorDetails(Vendor vendor, SafeDataReader dr) { vendor.VenderReferenceID = dr.GetString("VENDORREF"); vendor.VendorAmount = dr.GetDecimal("VENDORAMOUNT"); vendor.DI = dr.GetString("DI"); vendor.DIRef = dr.GetString("DIREF"); vendor.DIAmount = dr.GetDecimal("DIAMOUNT"); vendor.IsBilled = dr.GetBoolean("BILLED"); vendor.Type = dr.GetString("TYPE"); vendor.IsVerified = dr.GetBoolean("ISVERIFIED"); vendor.VRID = dr.GetInt32("VRID"); vendor.VRDID = dr.GetInt32("VRDID"); vendor.ItemCode = dr.GetString("ItemCode"); vendor.ItemNumber = dr.GetString("ItemNumber"); vendor.WOCode = dr.GetString("WOCode"); vendor.InvoiceNumber = dr.GetString("InvoiceNumber"); vendor.ClientName = dr.GetString("ClientName"); vendor.Date = dr.GetString("DATE"); vendor.UsedBy = dr.GetString("SIC"); vendor.GroupName = dr.GetString("GroupName"); return vendor; }
private Vendor FetchErrorList(Vendor vendor, SafeDataReader dr) { vendor.VRID = dr.GetInt32("VRID"); vendor.UploadedOn = dr.GetString("TransationDate"); vendor.Code = dr.GetString("Description"); vendor.VenderReferenceID = dr.GetString("ReferenceNo"); vendor.ItemNumber = dr.GetString("Amount"); return vendor; }
/// <summary> /// Created By : SUDHEER /// Created Date : 20 June 2014 /// Multiple UnBilled Discrepancy Calculations /// </summary> /// <param name="MainList"></param> /// <param name="UnBILLEDList"></param> private static void ProcessUnBilledList(List<Vendor> MainList, List<Vendor> UnBILLEDList) { System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame(); System.Reflection.MethodBase methodBase = stackFrame.GetMethod(); log.Debug("Start: " + methodBase.Name); try { int lastRecord = UnBILLEDList.Count() - 1; decimal DIAmount = 0; int MVId = 0; List<Vendor> SubList = new List<Vendor>(); for (int i = 0; i < UnBILLEDList.Count(); i++) { Vendor ObjMVL = new Vendor(); decimal VendorAmount = UnBILLEDList[0].VendorAmount; ObjMVL = UnBILLEDList[i]; if (i != lastRecord) { DIAmount += UnBILLEDList[i].DIAmount; ObjMVL.Discrepancy = UnBILLEDList[i].DIAmount; } else if (i == lastRecord) { ObjMVL.Discrepancy = VendorAmount - DIAmount; ObjMVL.IsDiscVisible = true; MVId = Convert.ToInt32(UnBILLEDList[i].DI); } SubList.Add(ObjMVL); } SubList[0].MVenderId = MVId; foreach (var item in SubList) { MainList.Add(item); } SubList.Clear(); } catch (Exception ex) { log.Error("Error: " + ex); } finally { log.Debug("End: " + methodBase.Name); } }
/// <summary> /// Created By : SUDHEER /// Created Date : 20 June 2014 /// Multiple MultiBilled Discrepancy Calculations /// </summary> /// <param name="MainList"></param> /// <param name="MultiBILLEDList"></param> private static void ProcessMultiBilledList(List<Vendor> MainList, List<Vendor> MultiBILLEDList) { System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame(); System.Reflection.MethodBase methodBase = stackFrame.GetMethod(); log.Debug("Start: " + methodBase.Name); try { var LastUnbilled = (from s in MultiBILLEDList where s.IsBilled == false orderby s.RowNo descending select s.RowNo).ToList().FirstOrDefault(); decimal DIAmount = 0; decimal VendorAmount = 0; int index = 0; int UpdateIndex = 0; int MVID = 0; List<Vendor> SubList = new List<Vendor>(); foreach (var item in MultiBILLEDList) { Vendor ObjMVL = new Vendor(); VendorAmount = MultiBILLEDList[0].VendorAmount; ObjMVL = item; if (LastUnbilled != item.RowNo) { DIAmount += item.DIAmount; ObjMVL.Discrepancy = item.DIAmount; } else if (LastUnbilled == item.RowNo) { ObjMVL.IsDiscVisible = true; UpdateIndex = index; MVID = Convert.ToInt32(item.DI); } SubList.Add(ObjMVL); index++; } SubList[UpdateIndex].Discrepancy = VendorAmount - DIAmount; SubList[0].MVenderId = MVID; foreach (var item in SubList) { Vendor objMainList = new Vendor(); objMainList = item; MainList.Add(objMainList); } SubList.Clear(); } catch (Exception ex) { log.Error("Error: " + ex); } 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); }
private Vendor FetchUnMatchedVendorDetails(Vendor vendor, SafeDataReader dr) { vendor.Date = dr.GetString("TransactionDate"); vendor.UsedBy = dr.GetString("UsedBy"); vendor.Description = dr.GetString("Description"); vendor.VenderReferenceID = dr.GetString("VENDORREF"); vendor.VendorAmount = dr.GetDecimal("VENDORAMOUNT"); vendor.VRID = dr.GetInt32("VRID"); vendor.VRDID = dr.GetInt32("VRDID"); return vendor; }
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); } }
private Vendor FetchVendorReportType1(Vendor vendor, SafeDataReader dr) { vendor.Code = dr.GetString("Code"); vendor.Name = dr.GetString("Name"); vendor.Username = dr.GetString("Username"); vendor.UploadedOn = dr.GetString("UploadedOn"); return vendor; }
private Vendor FetchVendorReportType(Vendor vendor, SafeDataReader dr) { vendor.Code = dr.GetString("Code"); vendor.Name = dr.GetString("Name"); return vendor; }
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); } }
private Vendor FetchService(Vendor vendor, SafeDataReader dr) { vendor.Type = dr.GetString("Type"); vendor.UploadedOn = dr.GetString("UploadedOn"); vendor.Username = dr.GetString("Name"); vendor.VRID = dr.GetInt32("VRID"); vendor.Name = dr.GetString("FileName"); return vendor; }
private Vendor FetchUnmatchedVendorDetails(Vendor vendor, SafeDataReader dr) { vendor.VenderReferenceID = dr.GetString("VENDERREFID"); vendor.VendorAmount = dr.GetDecimal("VENDORAMOUNT"); vendor.DI = dr.GetString("DI"); vendor.DIAmount = dr.GetDecimal("DIAMOUNT"); vendor.IsBilled = dr.GetBoolean("BILLED"); vendor.Type = dr.GetString("TYPE"); vendor.VerifyAmount = dr.GetDecimal("VERIFYAMOUNT"); vendor.WOID = dr.GetInt32("WOID"); vendor.ID = dr.GetInt32("ID"); vendor.IsAdhoc = dr.GetInt32("IsAdhoc"); return vendor; }
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); } }
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); } }