public static string generateFormat31(UploadedFormat31File uploadedFormat31File) { int counter = 0; double totalcredit = 0.00; double totaldebit = 0.00; string Datestr = DateTime.Now.ToString("yyyyMMdd"); string Oseconds = DateTime.Now.ToString("hhmmss"); String bno = uploadedFormat31File.batchNumber; String header = "HDR" + "KE" + "KE1" + Right("0000000" + uploadedFormat31File.batchNumber, 7) + "001" + "0000031" + Datestr + Oseconds + Datestr + Right("0000" + uploadedFormat31File.batchNumber, 4) + "LCUP" + "LCUPTEMP " + "N " + "\r\n"; String body = ""; //String reference = ""; // String fName = uploadedFormat31File.fileName;// format31.getParFileStart().toUpperCase() + bno + "." + format31.getParFileExtension(); foreach (Format31FileData format31FileData in uploadedFormat31File.fileData) { if (String.IsNullOrEmpty(format31FileData.exchangeRate) || format31FileData.exchangeRate == "" || format31FileData.exchangeRate == null) { format31FileData.exchangeRate = "1"; } counter++; if (format31FileData.isCredit()) { totalcredit += Convert.ToDouble(format31FileData.LCYAmount); } else if (format31FileData.isDebit()) { totaldebit += Convert.ToDouble(format31FileData.LCYAmount); } else { //Record with neither debit or credit found string chara = format31FileData.DR_CR; } format31FileData.FCYAmount = String.Format("{0:0.00}", Convert.ToDouble(format31FileData.FCYAmount)); format31FileData.LCYAmount = String.Format("{0:0.00}", Convert.ToDouble(format31FileData.LCYAmount)); format31FileData.exchangeRate = String.Format("{0:0.0000}", Convert.ToDouble(format31FileData.exchangeRate)); format31FileData.valueDate = string.Format("{00:00:0000}", Convert.ToDateTime(format31FileData.valueDate).ToString("yyyyMMdd")); body += "ENT~KE~" + format31FileData.branchCode + "~" + format31FileData.accountNo + "~~" + format31FileData.valueDate + "~" + counter + "~" + format31FileData.referenceNumber + "~" + format31FileData.DR_CR + "~" + format31FileData.FCCY + "~" + format31FileData.FCYAmount + "~+~0~" + format31FileData.transactionCode + "~1~" + format31FileData.exchangeRate + "~0~" + format31FileData.LCCY + "~" + format31FileData.LCYAmount + "~+~0~" + format31FileData.referenceNumber + "~" + format31FileData.transOUC + "~" + format31FileData.relatedCustomer + "~~~~~~~~" + format31FileData.expenseOUC + "~~~" + counter + "~" + format31FileData.narrative + "~~~~~~~~~~~~~~~~~~~~~" + "\r\n"; } int totalDebit = (Int32)Math.Round(totaldebit); int totalCredit = (Int32)Math.Round(totalcredit); string tail = "TLR" + "KE" + "KE1" + Right("0000000" + uploadedFormat31File.batchNumber, 7) + "001" + Right("000000000" + (counter + 2), 9) + Right("000000000000000000000000" + totalDebit, 24) + "+" + Right("000000000000000000000000" + totalCredit, 24) + "+" + " " + "\r\n"; return header + body + tail; }
private UploadedFormat31File saveExcelFileToDb(string fullFilePath, UploadedFormat31File uploadedFormat31File) { uploadedFormat31File.fileName = "F" + uploadedFormat31File.batchNumber + "." + uploadedFormat31File.extension; uploadedFormat31File.uploadedFormat31FileId = Guid.NewGuid(); uploadedFormat31File.Maker = User.Identity.GetUserName(); uploadedFormat31File.StatusOptions = MyEnums.StatusOptions.Unapproved; //Verify that it is excel of the right format //Read all fields in first worksheet and save values to db //string excelFileConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\""; string excelFileConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties='Excel 8.0;IMEX=1';"; OleDbConnection excelConn = new OleDbConnection(excelFileConnectionString); excelConn.Open(); OleDbCommand excelOleDbCommand = new OleDbCommand("Select * From [Sheet1$]", excelConn); OleDbDataReader dReader = excelOleDbCommand.ExecuteReader(); int count = 0; while (dReader.Read()) { //Branch Code Account No FCCY LCCY FCY Amount Exchange Rate LCY Amount Value Date Transaction Code Reference Number DR CR Narrative RELATEDCUSTOMER TRSNSOUC EXPENSE_OUC Format31FileData format31FileData = new Format31FileData(); if (!(DBNull.Value == dReader.GetValue(0))) { format31FileData.branchCode = dReader.GetValue(0).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(1))) { format31FileData.accountNo = dReader.GetValue(1).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(2))) { format31FileData.FCCY = dReader.GetValue(2).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(3))) { format31FileData.LCCY = dReader.GetValue(3).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(4))) { format31FileData.FCYAmount = dReader.GetValue(4).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(5))) { format31FileData.exchangeRate = dReader.GetValue(5).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(6))) { format31FileData.LCYAmount = dReader.GetValue(6).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(7))) { format31FileData.valueDate = dReader.GetValue(7).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(8))) { format31FileData.transactionCode = dReader.GetValue(8).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(9))) { format31FileData.referenceNumber = dReader.GetValue(9).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(10))) { format31FileData.DR_CR = dReader.GetValue(10).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(11))) { format31FileData.narrative = dReader.GetValue(11).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(12))) { format31FileData.relatedCustomer = dReader.GetValue(12).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(13))) { format31FileData.transOUC = dReader.GetValue(13).ToString().Trim(); } if (!(DBNull.Value == dReader.GetValue(14))) { format31FileData.expenseOUC = dReader.GetValue(14).ToString().Trim(); } format31FileData.index = count; count++; format31FileData.Format31FileDataId = Guid.NewGuid(); uploadedFormat31File.fileData.Add(format31FileData); } dReader.Close(); //_repository.SaveUpdate(uploadedFormat31File); DbManager context = new DbManager(); context.UploadedFormat31File.Add(uploadedFormat31File); context.SaveChanges(); _sharedCls.LogAudit(User.Identity.GetUserName(), "Save", Request.UserHostName, "Saved Format31 File Data to Database", "Save", "Format31"); return uploadedFormat31File; }