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