static DataTable GetBankWDDet(string yrmo, Decimal DetWDTot)
{
DataTable dt = VWAImportDAL.getBOAWDDet(yrmo);
DataRow row = dt.NewRow();
row["Type"] = "Total:";
row["Amount"] = DetWDTot;
dt.Rows.Add(row);
return(dt);
}
public static DataSet GetBankValidData(string yrmo)
{
Decimal startBal, totalDep, totalWD, endBal;
Decimal detDepTot, detWDTot;
string _type;
DataSet dsResult = new DataSet();
DataSet ds = new DataSet();
ds = VWAImportDAL.getBOAData(yrmo);
startBal = 0;
totalDep = 0;
totalWD = 0;
endBal = 0;
detDepTot = decimal.Parse((ds.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] = 'DEP' OR [Type] = 'MSCC')")).ToString(), NumberStyles.Currency);
detWDTot = decimal.Parse((ds.Tables[0].Compute("SUM([Amount])", "[importType] = 'Detail' AND ([Type] <> 'DEP' AND [Type] <> 'MSCC')")).ToString(), NumberStyles.Currency);
foreach (DataRow dr in ds.Tables[0].Rows)
{
_type = dr["Type"].ToString();
switch (_type)
{
case "Start Balance":
startBal = decimal.Parse(dr["Amount"].ToString(), NumberStyles.Currency);
break;
case "Total Deposits":
totalDep = decimal.Parse(dr["Amount"].ToString(), NumberStyles.Currency);
break;
case "Total Withdrawls":
totalWD = (-1) * decimal.Parse(dr["Amount"].ToString(), NumberStyles.Currency);
break;
case "End Balance":
endBal = (-1) * decimal.Parse(dr["Amount"].ToString(), NumberStyles.Currency);
break;
}
}
dsResult.Tables.Add(GetBankSumData(startBal, totalDep, totalWD, endBal));
dsResult.Tables.Add(GetBankDepSum(totalDep, detDepTot));
dsResult.Tables.Add(GetBankDepDet(yrmo, detDepTot));
dsResult.Tables.Add(GetBankWDSum(totalWD, detWDTot));
dsResult.Tables.Add(GetBankWDDet(yrmo, detWDTot));
return(dsResult);
}
/// <summary>
/// Method to Import VWA Monthly file
/// </summary>
/// <param name="_yrmo">YRMO</param>
/// <param name="_fileName">VWA Monthly File FileName</param>
/// <returns></returns>
public int import_VWA_Trans(string _yrmo, string _fileName)
{
string _Reportyrmo = "";
int _count = 0;
int _recCount = 0;
string line = string.Empty;
string _PatternDate =
@"(?<year>\d{4})(?<month>\d{2})(_vwa)";
TextReader reader = new StreamReader(File.OpenRead(_fileName));
Regex r = new Regex(_PatternDate);
DataSet dsVWA = new DataSet();
dsVWA.Clear();
try
{
Match parsed = Regex.Match(_fileName, _PatternDate);
if (parsed.Success)
{
string _year = parsed.Groups["year"].Value.ToString();
string _month = parsed.Groups["month"].Value.ToString();
_Reportyrmo = _year + _month;
}
if (_Reportyrmo.Equals(_yrmo))
{
dsVWA = parseVWA_Trans(_fileName, _yrmo);
_recCount = VWAImportDAL.storeVWATransData(dsVWA);
}
else
{
throw (new Exception("YRMO entered doesnt match with Report, Please check the Report."));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
}
return(_recCount);
}
public static DataSet GetRemitDetData(string yrmo)
{
DataSet dsFinal = new DataSet(); dsFinal.Clear();
DataSet dsTemp;
DataTable tblDet = new DataTable(); dsFinal.Tables.Add(tblDet);
DataRow row;
string[] vendorCds = { "RemitOC", "RemitCigna", "RemitUHC", "RemitDisab", "RemitAnth" };
foreach (string vendorcd in vendorCds)
{
dsTemp = new DataSet(); dsTemp.Clear();
dsTemp = VWAImportDAL.GetRemitDetailData(yrmo, vendorcd);
if ((dsTemp != null) && (dsTemp.Tables.Count != 0))
{
tblDet.Merge(dsTemp.Tables[0]);
if (dsTemp.Tables[0].Rows.Count != 0)
{
row = tblDet.NewRow();
row["Beneficiary Name"] = "** SUB TOTAL:";
row["Paid VWA"] = dsTemp.Tables[0].Compute("SUM([Paid VWA])", String.Empty);
row["Paid Client"] = dsTemp.Tables[0].Compute("SUM([Paid Client])", String.Empty);
row["VWA Fees"] = dsTemp.Tables[0].Compute("SUM([VWA Fees])", String.Empty);
row["acme Remit"] = dsTemp.Tables[0].Compute("SUM([acme Remit])", String.Empty);
tblDet.Rows.Add(row);
}
}
}
if (tblDet.Rows.Count != 0)
{
row = tblDet.NewRow();
row["Beneficiary Name"] = "** GRAND TOTAL:";
row["Paid VWA"] = tblDet.Compute("SUM([Paid VWA])", "[Beneficiary Name] = '** SUB TOTAL:'");
row["Paid Client"] = tblDet.Compute("SUM([Paid Client])", "[Beneficiary Name] = '** SUB TOTAL:'");
row["VWA Fees"] = tblDet.Compute("SUM([VWA Fees])", "[Beneficiary Name] = '** SUB TOTAL:'");
row["acme Remit"] = tblDet.Compute("SUM([acme Remit])", "[Beneficiary Name] = '** SUB TOTAL:'");
tblDet.Rows.Add(row);
}
return(dsFinal);
}
public static string GetReportContent(string _source, string _filename, string yrmo)
{
DataSet ds = new DataSet();
DataSet dsTemp = new DataSet();
string[] sheetnames;
string[][] colsFormat, titles;
int[][] colsWidth;
string _content = "";
DataRow rowNew;
switch (_source)
{
case "RemitDisp":
sheetnames = new string[] { "Remit_Discrepancy" };
titles = new string[][] { new string[] { "VWA Remittance Discrepancy Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "string", "string", "string", "number", "string", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 65, 140, 65, 65, 65, 65, 65, 65, 65, 65, 50 } };
ds = VWAImportDAL.GetRemitDiscData(yrmo);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "RemitInput":
sheetnames = new string[] { "Remit_Summary", "Remit_Detail" };
titles = new string[][] { new string[] { "VWA Remittance Summary Report for YRMO : " + yrmo }, new string[] { "VWA Remittance Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "currency", "currency", "currency", "percent", "string" }, new string[] { "string", "string", "string", "string", "number", "string", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 120, 65, 65, 65, 65, 40 }, new int[] { 80, 130, 65, 65, 65, 65, 65, 65, 65, 65, 40 } };
dsTemp = GetRemitSumData(yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = GetRemitDetData(yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, "! VWA Fees(VWA) plus Due Client(acme) does not equals Paid VWA(Total)");
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "BankDisp":
ds = GetBankValidData(yrmo);
_content = VWAExcelReport.BankDispExcelRpt(ds, yrmo);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranMis":
sheetnames = new string[] { "VWA_DELETED" };
titles = new string[][] { new string[] { "Missing Transactions Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "string", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 40, 150, 65, 130, 65, 130, 45, 65, 65, 65, 65, 65, 65, 65, 65 } };
ds = VWA_ExportDAL.GetTranOutputData("TranMis", yrmo);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranClient":
sheetnames = new string[] { "VWA_CLIENT_SUM", "VWA_CLIENT_DET" };
titles = new string[][] { new string[] { "Change in Client Summary Report for YRMO : " + yrmo }, new string[] { "Change in Client Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 50, 80, 45 }, new int[] { 50, 80, 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranClientSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranClientDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranGrp":
sheetnames = new string[] { "VWA_GROUP_SUM", "VWA_GROUP_DET" };
titles = new string[][] { new string[] { "Change in Group Summary Report for YRMO : " + yrmo }, new string[] { "Change in Group Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 40, 80, 40, 80, 45 }, new int[] { 40, 80, 40, 80, 50, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranGrpSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranGrpDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranStat":
sheetnames = new string[] { "VWA_STATUS_SUM", "VWA_STATUS_OPEN", "VWA_STATUS_CLOSED" };
titles = new string[][] { new string[] { "Open, Closed, Pended Counts Summary Report for YRMO : " + yrmo }, new string[] { "New Cases Opened Detail Report for YRMO : " + yrmo }, new string[] { "New Cases Closed Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number", "number", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 50, 50, 50 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Group Name"] = "TOTAL:";
rowNew["Open Counter"] = dsTemp.Tables[0].Compute("SUM([Open Counter])", String.Empty);
rowNew["Closed Counter"] = dsTemp.Tables[0].Compute("SUM([Closed Counter])", String.Empty);
rowNew["Pending Counter"] = dsTemp.Tables[0].Compute("SUM([Pending Counter])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatOpenDET", yrmo); dsTemp.Tables[0].TableName = "Detail1Table";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "Detail1TableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranStatCloseDET", yrmo); dsTemp.Tables[0].TableName = "Detail2Table";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "Detail2TableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranACCTG":
sheetnames = new string[] { "VWA_ACCTG_SUM", "VWA_ACCTG_DET" };
titles = new string[][] { new string[] { "Financial Activity Summary Report for YRMO : " + yrmo }, new string[] { "Financial Activity Detail Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number", "currency", "currency", "currency" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency", "currency", "currency", "currency", "string" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 45, 65, 65, 65 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 70, 70, 70, 70, 70, 70, 70, 150 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranACCTGSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Group Name"] = "TOTAL:";
rowNew["Counter"] = dsTemp.Tables[0].Compute("SUM([Counter])", String.Empty);
rowNew["Recovery Amount"] = dsTemp.Tables[0].Compute("SUM([Recovery Amount])", String.Empty);
rowNew["Total Fees"] = dsTemp.Tables[0].Compute("SUM([Total Fees])", String.Empty);
rowNew["Net Amount"] = dsTemp.Tables[0].Compute("SUM([Net Amount])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranACCTGDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
rowNew = dsTemp.Tables[0].NewRow();
rowNew["Recovery Date"] = "TOTAL:";
rowNew["Recovered Amount Current"] = dsTemp.Tables[0].Compute("SUM([Recovered Amount Current])", String.Empty);
rowNew["TotFees Current"] = dsTemp.Tables[0].Compute("SUM([TotFees Current])", String.Empty);
rowNew["Net Amount Current"] = dsTemp.Tables[0].Compute("SUM([Net Amount Current])", String.Empty);
rowNew["Benefit Amount Total"] = dsTemp.Tables[0].Compute("SUM([Benefit Amount Total])", String.Empty);
rowNew["Recovered Amount Total"] = dsTemp.Tables[0].Compute("SUM([Recovered Amount Total])", String.Empty);
rowNew["TotFees Total"] = dsTemp.Tables[0].Compute("SUM([TotFees Total])", String.Empty);
rowNew["Net Amount Total"] = dsTemp.Tables[0].Compute("SUM([Net Amount Total])", String.Empty);
dsTemp.Tables[0].Rows.Add(rowNew);
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranAging":
if (yrmo == null || yrmo.Equals(String.Empty))
{
yrmo = "0";
}
sheetnames = new string[] { "VWA_AGING_SUM", "VWA_AGING_DET" };
titles = new string[][] { new string[] { "Aging Summary Report on " + DateTime.Today.ToString("MM/dd/yyyy") + " with aging days - " + yrmo }, new string[] { "Aging Detail Report on " + DateTime.Today.ToString("MM/dd/yyyy") + " with aging days - " + yrmo } };
colsFormat = new string[][] { new string[] { "number", "string", "number", "string", "number" }, new string[] { "number", "string", "number", "string", "number", "string", "string", "string", "string", "string", "string", "string", "string", "string", "string", "currency", "currency", "currency", "currency", "number", "string" } };
colsWidth = new int[][] { new int[] { 50, 80, 45, 80, 50 }, new int[] { 50, 80, 45, 80, 65, 130, 65, 130, 45, 40, 150, 65, 65, 65, 65, 65, 65, 65, 65, 45, 75 } };
dsTemp = VWA_ExportDAL.GetTranOutputData("TranAgingSUM", yrmo); dsTemp.Tables[0].TableName = "SummaryTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[0].TableName = "SummaryTableF"; dsTemp.Clear();
dsTemp = VWA_ExportDAL.GetTranOutputData("TranAgingDET", yrmo); dsTemp.Tables[0].TableName = "DetailTable";
ds.Tables.Add(dsTemp.Tables[0].Copy()); ds.Tables[1].TableName = "DetailTableF"; dsTemp.Clear();
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "TranStatCtr":
sheetnames = new string[] { "VWA_STATUSCTR_SUM" };
titles = new string[][] { new string[] { "Status Counter Report for YRMO : " + yrmo } };
colsFormat = new string[][] { new string[] { "string", "string", "number" } };
colsWidth = new int[][] { new int[] { 40, 200, 50 } };
ds = VWA_ExportDAL.GetTranOutputData("TranStatCtr", yrmo);
rowNew = ds.Tables[0].NewRow();
rowNew["Status Description"] = "TOTAL:";
rowNew["Counter"] = ds.Tables[0].Compute("SUM([Counter])", String.Empty);
ds.Tables[0].Rows.Add(rowNew);
_content = VWAExcelReport.ExcelXMLRpt(ds, _filename, sheetnames, titles, colsFormat, colsWidth, String.Empty);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "CaseInfo":
//yrmo is Contract number in this case
ds = GetCaseData(yrmo);
_content = VWAExcelReport.CaseExcelRpt(ds, yrmo);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
}
return(_content);
}
public static DataSet GetRemitSumData(string yrmo)
{
Decimal vwaAmt, acmeAmt, totalAmt, feePercent;
Decimal disabVWA, disabacme, disabTotal;
Decimal vwaGrandAmt, acmeGrandAmt, totalGrandAmt;
string[] vendorCds = { "RemitOC", "RemitCigna", "RemitUHC", "RemitDisab", "RemitAnth" };
DataSet ds = new DataSet(); ds.Clear();
DataTable dsTable; dsTable = ds.Tables.Add("RemitSum");
DataRow row;
DataColumn col;
col = new DataColumn("Vendor"); dsTable.Columns.Add(col);
col = new DataColumn("VWA"); dsTable.Columns.Add(col);
col = new DataColumn("acme"); dsTable.Columns.Add(col);
col = new DataColumn("Total"); dsTable.Columns.Add(col);
col = new DataColumn("fee %"); dsTable.Columns.Add(col);
col = new DataColumn("Flag"); dsTable.Columns.Add(col);
vwaGrandAmt = 0;
acmeGrandAmt = 0;
totalGrandAmt = 0;
disabVWA = 0;
disabacme = 0;
disabTotal = 0;
foreach (string vendorcd in vendorCds)
{
vwaAmt = VWAImportDAL.GetClientVWAFees(vendorcd, yrmo);
acmeAmt = VWAImportDAL.GetClientDueClient(vendorcd, yrmo);
totalAmt = VWAImportDAL.GetClientPaidVWA(vendorcd, yrmo);
if (totalAmt == 0)
{
feePercent = 0;
}
else
{
feePercent = (vwaAmt / totalAmt);
}
if (vendorcd.Equals("RemitDisab"))
{
disabVWA = vwaAmt; disabacme = acmeAmt; disabTotal = totalAmt;
}
vwaGrandAmt = vwaGrandAmt + vwaAmt;
acmeGrandAmt = acmeGrandAmt + acmeAmt;
totalGrandAmt = totalGrandAmt + totalAmt;
row = dsTable.NewRow();
row["Vendor"] = VWA.GetRemitClientCd(vendorcd);
row["VWA"] = vwaAmt;
row["acme"] = acmeAmt;
row["Total"] = totalAmt;
row["fee %"] = feePercent;
if ((vwaAmt + acmeAmt) != totalAmt)
{
row["Flag"] = "!";
}
dsTable.Rows.Add(row);
}
row = dsTable.NewRow();
row["Vendor"] = "Total:";
row["VWA"] = vwaGrandAmt;
row["acme"] = acmeGrandAmt;
row["Total"] = totalGrandAmt;
dsTable.Rows.Add(row);
//Non-disability row
row = dsTable.NewRow();
row["Vendor"] = "Non-Disability";
row["VWA"] = (vwaGrandAmt - disabVWA);
row["acme"] = (acmeGrandAmt - disabacme);
row["Total"] = (totalGrandAmt - disabTotal);
dsTable.Rows.Add(row);
//Disability row
row = dsTable.NewRow();
row["Vendor"] = "Disability";
row["VWA"] = disabVWA;
row["acme"] = disabacme;
row["Total"] = disabTotal;
dsTable.Rows.Add(row);
//Total row
row = dsTable.NewRow();
row["Vendor"] = "Total:";
row["VWA"] = vwaGrandAmt;
row["acme"] = acmeGrandAmt;
row["Total"] = totalGrandAmt;
dsTable.Rows.Add(row);
return(ds);
}
/// <summary>
/// Method to Import Bank Statement
/// </summary>
/// <param name="_yrmo">YRMO</param>
/// <param name="_fileName">Bank statement FileName</param>
/// <returns></returns>
public int importBOA_VWA(string _yrmo, string _fileName)
{
string _Reportyrmo = "";
int _count = 0;
int _recCount = 0;
string line = string.Empty;
string _PatternDate =
@"^\s+(Statement Period)\s+(?<smonth>\d{2})\/(?<sday>\d{2})\/(?<syear>\d{2})\s+(through)\s+(?<emonth>\d{2})\/(?<eday>\d{2})\/(?<eyear>\d{2})";
TextReader reader = new StreamReader(File.OpenRead(_fileName));
Regex r = new Regex(_PatternDate);
//string _prevyrmo = VWA.getPrevYRMO(_yrmo);
DataSet dsBOA = new DataSet();
dsBOA.Clear();
try
{
while ((line = reader.ReadLine()) != null && _count == 0)
{
Match parsed = Regex.Match(line, _PatternDate);
if (parsed.Success)
{
string _syear = parsed.Groups["syear"].Value.ToString();
string _smonth = parsed.Groups["smonth"].Value.ToString();
string _sday = parsed.Groups["sday"].Value.ToString();
string _eyear = parsed.Groups["eyear"].Value.ToString();
string _emonth = parsed.Groups["emonth"].Value.ToString();
string _eday = parsed.Groups["eday"].Value.ToString();
string _lastday = "";
int _lday = VWA.GetLastDayofYRMO("20" + _eyear + _emonth).Day;
if (_lday < 10)
{
_lastday = "0" + VWA.GetLastDayofYRMO("20" + _eyear + _emonth).Day.ToString();
}
else
{
_lastday = VWA.GetLastDayofYRMO("20" + _eyear + _emonth).Day.ToString();
}
if (_sday.Equals("01") && _eday.Equals(_lastday))
{
_Reportyrmo = "20" + _syear + _smonth;
}
_count++;
}
}
if (_Reportyrmo.Equals(_yrmo))
{
dsBOA = parseBOA_VWA(_fileName, _yrmo);
_recCount = VWAImportDAL.storeBOAData(dsBOA);
}
else
{
throw (new Exception("YRMO entered doesnt match with Report, Please check the Report."));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
}
return(_recCount);
}
public int parseRemittance(DataSet ds, string _yrmo, string source)
{
string benName, ssn, sts;
string accDtStr, vwaCtrlStr, dtPostedStr, paidVWAStr, paidClientStr, vwaFeesStr, dueClientStr;
SqlDateTime accDt, dtPosted;
int vwaCtrl;
Decimal paidVWA, paidClient, vwaFees, dueClient;
int _count = 0;
int[] cols = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 10 };
string _tableName = source;
for (int i = 0; i < (ds.Tables[_tableName].Rows.Count - 1); i++)
{
benName = ds.Tables[_tableName].Rows[i][cols[0]].ToString().Trim().Replace("'", "''");
ssn = ds.Tables[_tableName].Rows[i][cols[1]].ToString().Trim();
if (ssn.Contains("-"))
{
ssn = ssn.Replace("-", "");
}
accDtStr = ds.Tables[_tableName].Rows[i][cols[2]].ToString().Trim();
if (accDtStr != null && accDtStr != string.Empty)
{
accDt = Convert.ToDateTime(accDtStr);
}
else
{
accDt = SqlDateTime.Null;
}
vwaCtrlStr = ds.Tables[_tableName].Rows[i][cols[3]].ToString().Trim();
if (vwaCtrlStr != null && vwaCtrlStr != string.Empty)
{
vwaCtrl = Convert.ToInt32(vwaCtrlStr);
}
else
{
vwaCtrl = 0;
}
dtPostedStr = ds.Tables[_tableName].Rows[i][cols[4]].ToString().Trim();
if (dtPostedStr != null && dtPostedStr != string.Empty)
{
dtPosted = Convert.ToDateTime(dtPostedStr);
}
else
{
dtPosted = SqlDateTime.Null;
}
paidVWAStr = ds.Tables[_tableName].Rows[i][cols[5]].ToString().Trim();
if (paidVWAStr != null && paidVWAStr != string.Empty)
{
paidVWA = Decimal.Parse(paidVWAStr, NumberStyles.Currency);
}
else
{
paidVWA = 0;
}
paidClientStr = ds.Tables[_tableName].Rows[i][cols[6]].ToString().Trim();
if (paidClientStr != null && paidClientStr != string.Empty)
{
paidClient = Decimal.Parse(paidClientStr, NumberStyles.Currency);
}
else
{
paidClient = 0;
}
vwaFeesStr = ds.Tables[_tableName].Rows[i][cols[7]].ToString().Trim();
if (vwaFeesStr != null && vwaFeesStr != string.Empty)
{
vwaFees = Decimal.Parse(vwaFeesStr, NumberStyles.Currency);
}
else
{
vwaFees = 0;
}
dueClientStr = ds.Tables[_tableName].Rows[i][cols[8]].ToString().Trim();
if (dueClientStr != null && dueClientStr != string.Empty)
{
dueClient = Decimal.Parse(dueClientStr, NumberStyles.Currency);
}
else
{
dueClient = 0;
}
sts = ds.Tables[_tableName].Rows[i][cols[9]].ToString().Trim();
VWAImportDAL.insertRemittance(benName, ssn, accDt, vwaCtrl, dtPosted, paidVWA, paidClient, vwaFees, dueClient, sts, source, _yrmo);
_count++;
}
return(_count);
}