public static DataTable getTransactionData_4Balancing(string yrmo)
{
DataTable dttrans = new DataTable();
string priyrmo = VWA.getPrevYRMO(yrmo);
try
{
if (connect != null && connect.State == ConnectionState.Closed)
{
connect.Open();
}
command = new SqlCommand("[dbo].[sp_VWA_Trans_Output]", connect);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@source", SqlDbType.VarChar).Value = "TransSum";
command.Parameters.Add("@yrmo", SqlDbType.VarChar).Value = yrmo;
command.Parameters.Add("@priyrmo", SqlDbType.VarChar).Value = priyrmo;
command.Parameters.Add("@agedDays", SqlDbType.Int).Value = 0;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(dttrans);
return(dttrans);
}
catch (Exception ex)
{
throw (new Exception("Error getting Transaction details"));
}
finally
{
connect.Close();
}
}
public static DataTable getHistory_4Cases(string _contract)
{
DataTable dtHistory = new DataTable();
DataTable dtTemp = new DataTable();
List <string> _yrmos = new List <string>();
SqlDataReader reader = null;
string _cmdstr = "SELECT DISTINCT yrmo FROM VWA_Trans1 WHERE ContractNo = @contract ORDER BY yrmo DESC";
string _yrmo;
string _prevyrmo = "";
try
{
if (connect != null && connect.State == ConnectionState.Closed)
{
connect.Open();
}
//Get All yrmos for given contract
command = new SqlCommand(_cmdstr, connect);
command.Parameters.AddWithValue("@contract", _contract);
reader = command.ExecuteReader();
while (reader.Read())
{
_yrmos.Add(reader[0].ToString());
}
reader.Close();
foreach (string _y in _yrmos)
{
_yrmo = _y;
_prevyrmo = VWA.getPrevYRMO(_yrmo);
command = new SqlCommand("[dbo].[sp_VWA_GetHistory]", connect);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@yrmo", _yrmo);
command.Parameters.AddWithValue("@pyrmo", _prevyrmo);
command.Parameters.AddWithValue("@contract", _contract);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(dtTemp);
dtHistory.Merge(dtTemp); dtTemp.Clear();
}
dtHistory.TableName = "History";
return(dtHistory);
}
catch (Exception ex)
{
throw (new Exception("Error getting History data"));
}
finally
{
reader.Close();
connect.Close();
}
}
public static DataSet GetTranOutputData(string _source, string _yrmo)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet(); ds.Clear();
string priyrmo;
int agedDays;
try
{
if (_source.Equals("TranAgingSUM") || _source.Equals("TranAgingDET"))
{
priyrmo = String.Empty;
agedDays = Convert.ToInt32(_yrmo);
}
else
{
agedDays = 0;
priyrmo = VWA.getPrevYRMO(_yrmo);
}
if (connect != null && connect.State == ConnectionState.Closed)
{
connect.Open();
}
command = new SqlCommand("[dbo].[sp_VWA_Trans_Output]", connect);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 50;
command.Parameters.Add("@yrmo", SqlDbType.VarChar).Value = _yrmo;
command.Parameters.Add("@source", SqlDbType.VarChar).Value = _source;
command.Parameters.Add("@priyrmo", SqlDbType.VarChar).Value = priyrmo;
command.Parameters.Add("@agedDays", SqlDbType.VarChar).Value = agedDays;
da.SelectCommand = command;
da.Fill(ds);
command.Dispose();
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connect.Close();
}
}
public static string GetBalReportContent(string _source, string _filename, string yrmo, DataTable dsAuditSum1, DataTable dsAuditSum2, DataTable dsSum)
{
string[] sheetnames, titles;
string[][] colsFormat;
int[][] colsWidth;
string _content = "";
HRAExcelReport xlObj = new HRAExcelReport();
switch (_source)
{
case "BalSumAudit":
DataSet ds1 = new DataSet(); ds1.Clear();
DataSet ds2 = new DataSet(); ds2.Clear();
sheetnames = new string[] { "VWA_BALSUM" };
titles = new string[] { "Balance Audit Report for YRMO : " + yrmo };
string[][] subtitles = { new string[] { "Bank Statement Summary", "Remittance Information Summary" } };
colsFormat = new string[][] { new string[] { "string", "number", "currency", "currency" }, new string[] { "string", "number", "string", "currency", "currency" } };
colsWidth = new int[][] { new int[] { 85, 85, 85, 85, 85 } };
dsAuditSum1.TableName = "BalAuditSum1"; ds1.Tables.Add(dsAuditSum1.Copy());
dsAuditSum2.TableName = "BalAuditSum2"; ds2.Tables.Add(dsAuditSum2.Copy());
_content = xlObj.ExcelXMLRpt(ds1, ds2, _filename, sheetnames, titles, subtitles, colsFormat, colsWidth);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
case "BalSum":
string sheetname = "VWA_BAL_XSUM";
titles = new string[] { "Balance Summary Report for YRMO : " + yrmo };
colsFormat = new string[][] { new string[] { "string", "currency", "string" } };
colsWidth = new int[][] { new int[] { 150, 90, 100 } };
_content = VWAExcelReport.ExcelXMLRpt(dsSum, _filename, sheetname, titles, colsFormat, colsWidth);
_content = VWA.replaceIllegalXMLCharacters(_content);
break;
}
return(_content);
}
//Checks if the output report has records
public static Boolean HasRecords(string yrmo, string source)
{
string priyrmo;
Boolean _result;
int count = 0;
string cmdstr = "";
if (source.Equals("TranAging"))
{
priyrmo = String.Empty;
}
else
{
priyrmo = VWA.getPrevYRMO(yrmo);
}
switch (source)
{
case "RemitDisp":
cmdstr = "SELECT COUNT(*) FROM VWA_remit WHERE yrmo = @yrmo AND pdClient > 0";
break;
case "RemitInput":
cmdstr = "SELECT COUNT(*) FROM [VWA_remit] WHERE yrmo = @yrmo";
break;
case "TranMis":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 WHERE yrmo = @priyrmo "
+ "AND ContractNo NOT IN "
+ "( "
+ "SELECT ContractNo FROM VWA_Trans1 "
+ "WHERE yrmo = @yrmo "
+ ")";
break;
case "TranClient":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 a, VWA_Trans1 b "
+ "WHERE a.yrmo = @yrmo AND b.yrmo = @priyrmo "
+ "AND (a.ContractNo = b.ContractNo) "
+ "AND (ISNULL(a.ClientId,0) <> ISNULL(b.ClientId,0)) "
+ "GROUP BY a.ClientId, b.ClientId ";
break;
case "TranGrp":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 a, VWA_Trans1 b "
+ "WHERE a.yrmo = @yrmo AND b.yrmo = @priyrmo "
+ "AND (a.ContractNo = b.ContractNo) "
+ "AND (ISNULL(a.GroupNo,0) <> ISNULL(b.GroupNo,0)) "
+ "GROUP BY a.GroupNo, b.GroupNo ";
break;
case "TranStat":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 "
+ "WHERE yrmo = @yrmo "
+ "GROUP BY ClientId, GroupNo";
break;
case "TranACCTG":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 a, VWA_Trans1 b "
+ "WHERE a.yrmo = @yrmo AND b.yrmo = @priyrmo "
+ "AND (a.ContractNo = b.ContractNo) "
+ "AND "
+ "( "
+ "(ISNULL(a.RecAmt,0) <> ISNULL(b.RecAmt,0)) OR "
+ "(ISNULL(a.NetAmt,0) <> ISNULL(b.NetAmt,0)) OR "
+ "(ISNULL(a.TotFees,0) <> ISNULL(b.TotFees,0)) "
+ ") "
+ "GROUP BY a.ClientId, a.GroupNo";
break;
case "TranAging":
if (yrmo == null || yrmo.Equals(String.Empty))
{
yrmo = "0";
}
int agedDays = Convert.ToInt32(yrmo);
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 "
+ "WHERE (OpenDt BETWEEN CONVERT(VARCHAR(15),(GetDate() - " + agedDays + "),101) And CONVERT(VARCHAR(15),GetDate(),101)) "
+ "AND CloseDt IS NULL";
break;
case "TranStatCtr":
cmdstr = "SELECT COUNT(*) FROM VWA_Trans1 "
+ "WHERE yrmo = @yrmo "
+ "GROUP BY StatusCd";
break;
}
try
{
if (connect != null && connect.State == ConnectionState.Closed)
{
connect.Open();
}
command = new SqlCommand(cmdstr, connect);
command.Parameters.AddWithValue("@yrmo", yrmo);
command.Parameters.AddWithValue("@priyrmo", priyrmo);
count = Convert.ToInt32(command.ExecuteScalar());
if (count != 0)
{
_result = true;
}
else
{
_result = false;
}
command.Dispose();
return(_result);
}
finally
{
connect.Close();
}
}
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 string GetFileName(string source, string yrmo)
{
string _filename = "";
switch (source)
{
case "BankDisp":
_filename = yrmo + "_vwaboa_validation.xls";
break;
case "RemitDisp":
_filename = yrmo + "_vwaremit_discrepancy.xls";
break;
case "RemitInput":
_filename = yrmo + "_vwaremit.xls";
break;
case "TranMis":
_filename = yrmo + "_VWA_DELETED.xls";
break;
case "TranClient":
_filename = yrmo + "_VWA_CLIENT.xls";
break;
case "TranGrp":
_filename = yrmo + "_VWA_GROUP.xls";
break;
case "TranStat":
_filename = yrmo + "_VWA_STATUS.xls";
break;
case "TranACCTG":
_filename = yrmo + "_VWA_ACCTG.xls";
break;
case "TranAging":
_filename = VWA.GetYRMO(DateTime.Today) + "_VWA_AGING.xls";
break;
case "TranStatCtr":
_filename = yrmo + "_VWA_STATUSCTR_SUM.xls";
break;
case "BalSumAudit":
_filename = yrmo + "_VWA_BALSUM.xls";
break;
case "BalSum":
_filename = yrmo + "_VWA_BAL_XSUM.xls";
break;
case "CaseInfo":
//yrmo is Contract number in this case
_filename = DateTime.Today.ToString("MMddyyyy") + "_CaseInfo_" + yrmo + ".xls";
break;
}
return(_filename);
}
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);
}