public DataTable GetCollectionListByStatus(int clientId, int collStatusId, string fromDate, string toDate, int userType, string branchCode)
{
StringBuilder sb = new StringBuilder();
try
{
_databaseManager = new MssqlDatabaseManager();
_databaseManager.SetConnectionString = MtbBillCollection.WebConfigManager.GetCollDBConnString();;
_databaseManager.OpenDatabaseConnection();
//Change date format
//We have read the date as dd/MM/yyyy. need to convert them in MM/dd/yyyy for sql date comparison
sb.Append("SELECT a.CollectionId,a.CollDate, a.ProductId, c.ProductName, b.branch_name,");
sb.Append(" a.CollFrom, d.InstTypeName,a.InstNumber, e.BankName, a.CollAmount,a.ApproveBy,a.ApproveDate FROM Collection a ");
if (collStatusId == MtbBillCollection.Global.Definitions.CollectionStatus.Value.SpeacialEdit)
{
sb.Append(" INNER JOIN SpecialEdit sp on sp.CollectionId=a.CollectionId AND Active=1");
}
sb.Append(" Left Join BranchList b On a.BranchCode=b.branch_code ");
sb.Append(" Left Join Product c on a.ProductId=c.ProductID ");
sb.Append(" Left Join InstrumentType d On a.InstTypeId = d.InstTypeId ");
sb.Append(" Left Join BankList e On a.InstBankCode = e.BankId ");
sb.Append(" Where a.clientId=");
sb.Append(clientId);
sb.Append(" and CollStatusId=");
sb.Append(collStatusId);
if (userType != MtbBillCollection.Global.Definitions.UserType.Value.Reviewer)
{
sb.Append(" and b.branch_code='" + branchCode + "' ");
}
if (!fromDate.Equals("") && !toDate.Equals(""))
{
DateTime CollFromDate, CollToDate;
if (!DateTime.TryParseExact(fromDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollFromDate))
{
// Parse failed
}
if (!DateTime.TryParseExact(toDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollToDate))
{
// Parse failed
}
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollFromDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)>=0");
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollToDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)<=0");
}
return(_databaseManager.GetDataTable(sb.ToString()));
}
catch
{
return(null);
}
finally
{
_databaseManager.CloseDatabaseConnection();
}
//return null;
}
public DataTable GetNonCashColllectionList(int collStatusId, int clientId, bool isCleared, int userType, string branchCode, string fromDate, string toDate)
{
StringBuilder sb = new StringBuilder();
try
{
_databaseManager = new MssqlDatabaseManager();
_databaseManager.SetConnectionString = MtbBillCollection.WebConfigManager.GetCollDBConnString();;
_databaseManager.OpenDatabaseConnection();
//Change date format
//We have read the date as dd/MM/yyyy. need to convert them in MM/dd/yyyy for sql date comparison
sb.Append("SELECT CollectionId,a.CollDate, c.ProductName, b.branch_name,InstRecvdBy,");
sb.Append(" a.CollFrom, d.InstTypeName,a.InstNumber, e.BankName, CONVERT(VARCHAR,[CollAmount],1) as 'CollAmount' FROM Collection a ");
sb.Append(" Left Join BranchList b On a.BranchCode=b.branch_code ");
sb.Append(" Left Join Product c on a.ProductId=c.ProductID ");
sb.Append(" Left Join InstrumentType d On a.InstTypeId = d.InstTypeId ");
sb.Append(" Left Join BankList e On a.InstBankCode = e.BankId ");
sb.Append("Left join Users u on u.UserId=a.InstRecvdBy");
sb.Append(" Where a.clientId=");
sb.Append(clientId);
sb.Append(" and CollStatusId=");
sb.Append(collStatusId);
sb.Append(" and a.InstTypeID <> ");
sb.Append(MtbBillCollection.Global.Definitions.InstType.Value.CASH);
sb.Append(" and a.InstrCLeared = ");
sb.Append((isCleared == true)?1:0);
if (userType != MtbBillCollection.Global.Definitions.UserType.Value.Reviewer)
{
sb.Append(" and b.branch_code='" + branchCode + "' ");
}
if (!fromDate.Equals("") && !toDate.Equals(""))
{
DateTime CollFromDate, CollToDate;
if (!DateTime.TryParseExact(fromDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollFromDate))
{
// Parse failed
}
if (!DateTime.TryParseExact(toDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollToDate))
{
// Parse failed
}
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollFromDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)>=0");
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollToDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)<=0");
}
return(_databaseManager.GetDataTable(sb.ToString()));
}
catch
{
return(null);
}
finally
{
_databaseManager.CloseDatabaseConnection();
}
}
public DataTable GetCollectionListForSpeacilEdit(int clientId, string fromDate, string toDate, string collStatusSrchCond, string instTypeSrchCond, string productTypeSrchCond, bool isBranchWise, string branchCode)
{
StringBuilder sb = new StringBuilder();
try
{
_databaseManager = new MssqlDatabaseManager();
_databaseManager.SetConnectionString = MtbBillCollection.WebConfigManager.GetCollDBConnString();;
_databaseManager.OpenDatabaseConnection();
//Change date format
//We have read the date as dd/MM/yyyy. need to convert them in MM/dd/yyyy for sql date comparison
DateTime CollFromDate, CollToDate;
if (!DateTime.TryParseExact(fromDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollFromDate))
{
// Parse failed
}
if (!DateTime.TryParseExact(toDate, "dd/MM/yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out CollToDate))
{
// Parse failed
}
sb.Append("SELECT a.CollectionId,a.CollDate, a.ProductID, c.ProductName, b.branch_name,");
sb.Append("a.CollFrom, d.InstTypeName,a.InstNumber, e.BankName, CollAmount FROM Collection a ");
sb.Append("Left Join BranchList b On a.BranchCode=b.branch_code ");
sb.Append("Left Join Product c on a.ProductId=c.ProductID ");
sb.Append("Left Join InstrumentType d On a.InstTypeId = d.InstTypeId ");
sb.Append("Left Join BankList e On a.InstBankCode = e.BankId ");
sb.Append("Where a.clientId=");
sb.Append(clientId);
if (isBranchWise == true)
{
sb.Append(" and b.branch_code='" + branchCode + "' ");
}
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollFromDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)>=0");
sb.Append(" and DATEDIFF(day, '");
sb.Append(CollToDate.ToString("MM/dd/yyyy"));
sb.Append("', colldate)<=0");
if (!collStatusSrchCond.Equals("0"))
{
if (collStatusSrchCond.Equals("6"))
{
sb.Append(" and (a.CollStatusId in (");
sb.Append(2);
sb.Append(")");
sb.Append(" OR a.CollStatusId in (");
sb.Append(5);
sb.Append("))");
}
else
{
sb.Append(" and a.CollStatusId in (");
sb.Append(collStatusSrchCond);
sb.Append(")");
}
}
if (!instTypeSrchCond.Equals(""))
{
sb.Append(" and a.InstTypeId in (");
sb.Append(instTypeSrchCond);
sb.Append(")");
}
if (!productTypeSrchCond.Equals(""))
{
sb.Append(" and a.ProductID in (");
sb.Append(productTypeSrchCond);
sb.Append(")");
}
return(_databaseManager.GetDataTable(sb.ToString()));
}
catch
{
return(null);
}
finally
{
_databaseManager.CloseDatabaseConnection();
}
//return null;
}