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();
}
}
//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();
}
}