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;
}
