public string getSubtractFormula(AuditColumns input, int initRow, bool negateSign) { string result = ""; string processOperator = "-"; if (negateSign) { processOperator = "+"; } if (input.discount != "") { result += processOperator + "IF(" + input.discount + initRow.ToString() + "<0," + input.discount + initRow.ToString() + ",0)"; } if (input.coupon != "") { result += processOperator + input.coupon + initRow.ToString(); } if (input.redeemedPoints != "") { result += processOperator + input.redeemedPoints + initRow.ToString(); } if (input.uniqueCode != "") { result += processOperator + input.uniqueCode + initRow.ToString(); } return(result); }
public string getAddingFormula(AuditColumns input, int initRow, bool negateSign) { string result = "#"; if (input.commissionRevenue != "") { result += "+" + input.commissionRevenue + initRow.ToString(); } if (input.transactionFee != "") { result += "+" + input.transactionFee + initRow.ToString(); } if (input.premium != "") { result += "+IF(" + input.premium + initRow.ToString() + ">0," + input.premium + initRow.ToString() + ",0)"; } if (input.refundFee != "") { result += "+" + input.refundFee + initRow.ToString(); } if (input.rescheduleFee != "") { result += "+" + input.rescheduleFee + initRow.ToString(); } if (input.rebookCost != "") { result += "+" + input.rebookCost + initRow.ToString(); } return(result); }
public async Task <SQLResult> ChangePassword(ChangePassword pModel, AuditColumns pAuditColumns) { SQLResult result = new SQLResult(); _Context.Database.BeginTransaction(); try { string csql = @" EXEC spmUserChangePassword @pi_mUserId , @pi_UserName , @pi_Password , @pi_PasswordHash , @pi_PasswordSalt , @pi_UserId , @pi_HostName , @pi_IPAddress , @pi_DeviceType , @pi_MACAddress"; List <SqlParameter> sqlparam = new List <SqlParameter>() { new SqlParameter("@pi_mUserId", pModel.UserId), new SqlParameter("@pi_UserName", pModel.Username), new SqlParameter("@pi_Password", pModel.Password), new SqlParameter("@pi_PasswordHash", pModel.PasswordHash), new SqlParameter("@pi_PasswordSalt", pModel.PasswordSalt), new SqlParameter("@pi_UserId", pAuditColumns.UserId), new SqlParameter("@pi_HostName", pAuditColumns.HostName), new SqlParameter("@pi_IPAddress", pAuditColumns.IPAddress), new SqlParameter("@pi_DeviceType", pAuditColumns.DeviceType), new SqlParameter("@pi_MACAddress", pAuditColumns.MACAddress), }; result = await _Context.DBResult.FromSql(csql, sqlparam.ToArray()).SingleOrDefaultAsync(); if (result.ErrorNo != 0) { _Context.Database.RollbackTransaction(); } else { _Context.Database.CommitTransaction(); } } catch (Exception ex) { _Context.Database.RollbackTransaction(); result.ErrorNo = 9999999999; result.ErrorMessage = ex.Message.ToString(); result.SQLErrorNumber = ex.HResult; result.SQLErrorMessage = ex.Source.ToString(); } return(result); }
public string getColumnNotFoundWarning(AuditColumns input) { string result = ""; PropertyInfo[] properties = input.GetType().GetProperties(); foreach (PropertyInfo property in properties) { string colValue = input.GetType().GetProperty(property.Name).GetValue(input).ToString(); if (colValue == "") { result += property.Name + ", "; } } return(result); }
public AuditColumns cleanUpNonExistingColumn(AuditColumns input) { AuditColumns result = new AuditColumns(); PropertyInfo[] properties = input.GetType().GetProperties(); foreach (PropertyInfo property in properties) { string colValue = input.GetType().GetProperty(property.Name).GetValue(input).ToString(); if (colValue.Length > 2 || colValue.Length <= 0) { result.GetType().GetProperty(property.Name).SetValue(result, ""); } else { result.GetType().GetProperty(property.Name).SetValue(result, colValue); } } return(result); }
public IActionResult CreateDocument([Bind()] InputModel inputModel) { string lookupDirectory = @"E:\temp\tvlk\files\"; string suffix = " - Processed"; inputModel.ProcessResult = new List <ProcessResult>(); if (inputModel.Directory != null) { if (inputModel.Directory.Length > 0) { lookupDirectory = inputModel.Directory; } } if (inputModel.OutputSuffix != null) { if (inputModel.OutputSuffix.Length > 0) { suffix = inputModel.OutputSuffix; } } string[] files = Directory.GetFiles(lookupDirectory, "*" + inputModel.SearchString + "*"); foreach (string file in files) { if (!file.Contains(suffix)) { ProcessResult processResult = new ProcessResult(); try { //FileStream fileStream = new FileStream(file, FileMode.Open); using (FileStream fileStream = new FileStream(file, FileMode.Open)) { //setting up application ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; application.EnableIncrementalFormula = true; //setting up workbook & shits IWorkbook workbook = application.Workbooks.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); worksheet.UsedRangeIncludesFormatting = false; //define initial values int colCount = worksheet.UsedRange.LastColumn; int rowCount = worksheet.UsedRange.LastRow; //int colCount = 39; //int rowCount = 10; AuditColumns colName = new AuditColumns(); AuditColumns cleanColName = new AuditColumns(); AuditColumns colBase = new AuditColumns(); ExcelColumnEnum en = new ExcelColumnEnum(); //get column alphabet by header text for (int i = 1; i <= colCount; i++) { string[] date = colBase.date.Split(','); foreach (string split in date) { if (worksheet[1, i].Value == split) { colName.date = en.TranslateIndex(i); break; } } string[] bid = colBase.bid.Split(','); foreach (string split in bid) { if (worksheet[1, i].Value == split) { colName.bid = en.TranslateIndex(i); break; } } string[] locale = colBase.locale.Split(','); foreach (string split in locale) { if (worksheet[1, i].Value == split) { colName.locale = en.TranslateIndex(i); break; } } string[] contractEntity = colBase.contractEntity.Split(','); foreach (string split in contractEntity) { if (worksheet[1, i].Value == split) { colName.contractEntity = en.TranslateIndex(i); break; } } string[] contractCurrency = colBase.contractCurrency.Split(','); foreach (string split in contractCurrency) { if (worksheet[1, i].Value == split) { colName.contractCurrency = en.TranslateIndex(i); break; } } string[] collectEntity = colBase.collectEntity.Split(','); foreach (string split in collectEntity) { if (worksheet[1, i].Value == split) { colName.collectEntity = en.TranslateIndex(i); break; } } string[] collectCurrency = colBase.collectCurrency.Split(','); foreach (string split in collectCurrency) { if (worksheet[1, i].Value == split) { colName.collectCurrency = en.TranslateIndex(i); break; } } string[] commissionRevenue = colBase.commissionRevenue.Split(','); foreach (string split in commissionRevenue) { if (worksheet[1, i].Value == split) { colName.commissionRevenue = en.TranslateIndex(i); break; } } string[] transactionFee = colBase.transactionFee.Split(','); foreach (string split in transactionFee) { if (worksheet[1, i].Value == split) { colName.transactionFee = en.TranslateIndex(i); break; } } string[] premium = colBase.premium.Split(','); foreach (string split in premium) { if (worksheet[1, i].Value == split) { colName.premium = en.TranslateIndex(i); break; } } string[] discount = colBase.discount.Split(','); foreach (string split in discount) { if (worksheet[1, i].Value == split) { colName.discount = en.TranslateIndex(i); break; } } string[] coupon = colBase.coupon.Split(','); foreach (string split in coupon) { if (worksheet[1, i].Value == split) { colName.coupon = en.TranslateIndex(i); break; } } string[] redeemedPoints = colBase.redeemedPoints.Split(','); foreach (string split in redeemedPoints) { if (worksheet[1, i].Value == split) { colName.redeemedPoints = en.TranslateIndex(i); break; } } string[] uniqueCode = colBase.uniqueCode.Split(','); foreach (string split in uniqueCode) { if (worksheet[1, i].Value == split) { colName.uniqueCode = en.TranslateIndex(i); break; } } string[] installmentFee = colBase.installmentFee.Split(','); foreach (string split in installmentFee) { if (worksheet[1, i].Value == split) { colName.installmentFee = en.TranslateIndex(i); break; } } string[] deliveryFee = colBase.deliveryFee.Split(','); foreach (string split in deliveryFee) { if (worksheet[1, i].Value == split) { colName.deliveryFee = en.TranslateIndex(i); break; } } string[] invoiceAmount = colBase.invoiceAmount.Split(','); foreach (string split in invoiceAmount) { if (worksheet[1, i].Value == split) { colName.invoiceAmount = en.TranslateIndex(i); break; } } string[] refundFee = colBase.refundFee.Split(','); foreach (string split in refundFee) { if (worksheet[1, i].Value == split) { colName.refundFee = en.TranslateIndex(i); break; } } string[] rescheduleFee = colBase.rescheduleFee.Split(','); foreach (string split in rescheduleFee) { if (worksheet[1, i].Value == split) { colName.rescheduleFee = en.TranslateIndex(i); break; } } string[] rebookCost = colBase.rebookCost.Split(','); foreach (string split in rebookCost) { if (worksheet[1, i].Value == split) { colName.rebookCost = en.TranslateIndex(i); break; } } } cleanColName = cleanUpNonExistingColumn(colName); string colNotFound = getColumnNotFoundWarning(cleanColName); //define new columns header worksheet[1, colCount + 1].Text = "Margin Amount"; worksheet[1, colCount + 2].Text = "Margin"; worksheet[1, colCount + 3].Text = "Status"; //calculate first non-header row & copy to the rest of sheet string formula = getAddingFormula(cleanColName, 2, false) + getSubtractFormula(cleanColName, 2, true); worksheet[2, colCount + 1, rowCount, colCount + 1].Formula = "=" + formula.Replace("#+", "").Replace("#", ""); string formula2 = getTaggingResult(2, en.TranslateIndex(colCount + 1)); worksheet[2, colCount + 2, rowCount, colCount + 2].Formula = "=" + formula2; worksheet[2, colCount + 3, rowCount, colCount + 3].Text = "ISSUED"; //conditional formatting /* * //prepare writing to compiled shit * List<string> listContractEntity = new List<string>(); * List<string> listCollectingCurrency = new List<string>(); * * //group contract entity * if (cleanColName.contractEntity != "") * { * var distinct = worksheet[cleanColName.contractEntity + "2:" + cleanColName.contractEntity + rowCount.ToString()].Columns.Distinct(); * int countContractEntity = distinct.Count(); * if (countContractEntity > 0) * { * foreach (var item in distinct) * { * listContractEntity.Add(item.Value); * } * } * } * * //group transaction currency * if (cleanColName.collectCurrency != "") * { * var distinct = worksheet[cleanColName.collectCurrency + "2:" + cleanColName.collectCurrency + rowCount.ToString()].Columns.Distinct(); * int countCollectCurrency = distinct.Count(); * if (countCollectCurrency > 0) * { * foreach (var item in distinct) * { * listCollectingCurrency.Add(item.Value); * } * } * } */ using (FileStream stream = new FileStream(@"" + file.Split('.')[0] + suffix + "." + file.Split('.')[1], FileMode.Create)) { workbook.SaveAs(stream); } workbook.Close(); excelEngine.Dispose(); processResult.Success = true; processResult.Message = file + " success"; if (colNotFound != "") { processResult.Message += " with " + colNotFound + " not found."; } } } catch (Exception ex) { processResult.Success = false; processResult.Message = file + " failed"; } inputModel.ProcessResult.Add(processResult); //write to compiled shit } } ViewData["ProcessResult"] = inputModel.ProcessResult; return(View(inputModel)); }
private bool ValidatemCountry(CountryEntry pModel, AuditColumns pAuditColumns, Boolean isUpdateValidation) { if (isUpdateValidation == true) { if (pModel.CountryId <= 0) { ModelState.AddModelError("", Messages.Blank("Country entry")); return(false); } } if (pModel.CountryCode.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("Country code")); return(false); } if (pModel.Country.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("Country")); return(false); } if (pModel.ISOAlpha2Code.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("ISO Alpha 2 Code")); return(false); } if (pModel.ISOAlpha3Code.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("ISO Alpha 3 Code")); return(false); } if (pModel.ISONumericCode.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("ISO Numeric Code")); return(false); } if (pAuditColumns.UserId <= 0) { ModelState.AddModelError("", Messages.Blank("User")); return(false); } if (pAuditColumns.MACAddress.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("MAC Address")); return(false); } if (pAuditColumns.HostName.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("Host Name")); return(false); } if (pAuditColumns.IPAddress.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("IP Address")); return(false); } if (pAuditColumns.DeviceType.Trim().Length == 0) { ModelState.AddModelError("", Messages.Blank("Device Type")); return(false); } return(true); }
public async Task <IActionResult> ChangePassword([FromBody] ChangePassword pModel) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } try { SignInUserProfile mLogin = await _repo.GetUserProfileAsync(pModel.Username); if (mLogin is null) { ModelState.AddModelError("", Messages.Invalid("username or password")); return(BadRequest(ModelState)); } if (mLogin.Active != true) { ModelState.AddModelError("", Messages.ShowErrorMessage("User not active, please contact your software support to activation!")); return(BadRequest(ModelState)); } AuditColumns mAuditColumns = new AuditColumns { UserId = 1, ApprovalStatusId = 0, CompanyId = 10001, DeviceType = "Undefined", FinancialYearId = 1, HostName = "Undefined", IPAddress = "Undefined", MACAddress = "Undefined" }; // Password generation byte[] passwordHash; byte[] passwordSalt; Functions.CreatePasswordHash(pModel.Password, out passwordHash, out passwordSalt); pModel.PasswordHash = passwordHash; pModel.PasswordSalt = passwordSalt; SQLResult result = await _repo.ChangePassword(pModel, mAuditColumns); if (result.ErrorNo == 0) { return(Ok(result)); } else { return(BadRequest(result)); } } catch (Exception ex) { SQLResult errorResult = new SQLResult { ErrorNo = 9999999999, ErrorMessage = ex.Message.ToString(), SQLErrorNumber = ex.HResult, SQLErrorMessage = ex.Source.ToString() }; return(BadRequest(errorResult)); } }