Exemple #1
0
        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();
            }
        }
Exemple #2
0
        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();
            }
        }
Exemple #3
0
        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);
        }
Exemple #5
0
        //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);
        }