private bool UpdateVoucherDetails(VoucherRecievable voucher, Database db, DbTransaction pTransaction)
        {
            bool result = false;

            try
            {
                DbCommand insCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_Insert_Voucher_Details);

                db.AddInParameter(insCmd, "@iPaymentID", DbType.Int64, voucher.PaymentID);
                db.AddInParameter(insCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
                db.AddInParameter(insCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
                db.AddInParameter(insCmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);

                //DbCommand updCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Insert_Voucher_Details);

                //db.AddInParameter(updCmd, "@iPaymentDetailID", DbType.Int64, "PaymentDetailID",DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@iModifiedBy", DbType.Currency, "ModifiedBy", DataRowVersion.Current);

                int rowcount = 0;
                rowcount = db.UpdateDataSet(voucher.DsPaymentDetails, voucher.DsPaymentDetails.Tables[0].TableName, insCmd, null, null, pTransaction);

                result = true;
            }
            catch (System.Exception ex)
            {
                result = false;
                throw ex;
            }
            return(result);
        }
        public DataSet GetVoucherDetailsByID(VoucherRecievable voucher)
        {
            DataSet ds = null;

            try
            {
                Database  db  = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_GetVoucherDetailsByID);

                db.AddInParameter(cmd, "@iPaymentID", DbType.Int32, voucher.PaymentID);

                voucher.DsPaymentDetails = db.ExecuteDataSet(cmd);
            }
            catch (System.Exception ex)
            {
                ds = null;
                throw ex;
            }
            return(ds);
        }
        private bool UpdateVoucherDetails(VoucherRecievable voucher, Database db, DbTransaction pTransaction)
        {
            bool result = false;
            try
            {
                DbCommand insCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_Insert_Voucher_Details);

                db.AddInParameter(insCmd, "@iPaymentID", DbType.Int64, voucher.PaymentID);
                db.AddInParameter(insCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
                db.AddInParameter(insCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
                db.AddInParameter(insCmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);

                //DbCommand updCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Insert_Voucher_Details);

                //db.AddInParameter(updCmd, "@iPaymentDetailID", DbType.Int64, "PaymentDetailID",DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
                //db.AddInParameter(updCmd, "@iModifiedBy", DbType.Currency, "ModifiedBy", DataRowVersion.Current);

                int rowcount = 0;
                rowcount = db.UpdateDataSet(voucher.DsPaymentDetails, voucher.DsPaymentDetails.Tables[0].TableName, insCmd, null, null, pTransaction);

                result = true;

            }
            catch (System.Exception ex)
            {
                result = false;
                throw ex;
            }
            return result;
        }
        public bool UpdateVoucher(VoucherRecievable voucher)
        {
            bool result = false;
            try
            {
                Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                connection = db.CreateConnection();
                connection.Open();

                transaction = connection.BeginTransaction();

                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_InsVoucher);

                db.AddInParameter(cmd, "@mPaymentAmount", DbType.Currency, voucher.PaymentAmount);
                db.AddInParameter(cmd, "@dtPaymentDate", DbType.DateTime, voucher.PaymentDate);
                db.AddInParameter(cmd, "@iPaymentTypeId", DbType.Int32, (int)voucher.PaymentTypeId);
                switch (voucher.PaymentTypeId)
                {
                    case Structures.PaymentTypes.CASH:
                        db.AddInParameter(cmd, "@sChequeNo", DbType.String, DBNull.Value);//null
                        db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null
                        db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null
                        db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);
                        break;
                    case Structures.PaymentTypes.CHEQUE:
                        db.AddInParameter(cmd, "@sChequeNo", DbType.String, voucher.ChequeNo);
                        db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, voucher.ChequeDate);
                        db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null
                        db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);//null
                        break;
                    case Structures.PaymentTypes.CREDIT_CARD:
                        db.AddInParameter(cmd, "@sChequeNo", DbType.String,voucher.ChequeNo);
                        db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null
                        db.AddInParameter(cmd, "@sCardType", DbType.String, voucher.CardType);
                        db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, voucher.CardCommisionRate);
                        break;
                    default:
                        break;
                }

                db.AddInParameter(cmd, "@sComment", DbType.String, voucher.Comment);
                db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);
                db.AddInParameter(cmd, "@inewPaymentID", DbType.Int64, voucher.PaymentID);

                if (db.ExecuteNonQuery(cmd, transaction) > 0)
                {
                    Int64 newVoucherID = 0;
                    Int64.TryParse(db.GetParameterValue(cmd, "@inewPaymentID").ToString().Trim(), out newVoucherID);

                    if (newVoucherID > 0)
                    {
                        voucher.PaymentID = newVoucherID;
                        voucher.PaymentCode = db.GetParameterValue(cmd, "@snewPaymentID").ToString().Trim();

                        if (this.UpdateVoucherDetails(voucher, db, transaction))
                        {
                            transaction.Commit();
                            result = true;
                        }
                    }

                }
            }
            catch (System.Exception ex)
            {
                transaction.Rollback();
                result = false;
                throw ex;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return result;
        }
        public DataSet GetVoucherDetailsByID(VoucherRecievable voucher)
        {
            DataSet ds = null;
            try
            {
                Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_GetVoucherDetailsByID);

                db.AddInParameter(cmd, "@iPaymentID", DbType.Int32, voucher.PaymentID);

                voucher.DsPaymentDetails = db.ExecuteDataSet(cmd);
            }
            catch (System.Exception ex)
            {
                ds = null;
                throw ex;
            }
            return ds;
        }
        public bool GetVoucherByID(VoucherRecievable voucher)
        {
            bool result = false;
            try
            {
                Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_GetByID);

                db.AddInParameter(cmd, "@iPaymentID", DbType.Int64, voucher.PaymentID);

                IDataReader reader = db.ExecuteReader(cmd);

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        voucher.PaymentCode     = reader["PaymentCode"].ToString();
                        voucher.CustomerID      = Convert.ToInt32(reader["CustomerID"].ToString());
                        voucher.PaymentAmount   = reader["PaymentAmount"] != DBNull.Value ? Convert.ToDecimal(reader["PaymentAmount"].ToString()) :
                                                    Convert.ToDecimal("0");
                        voucher.PaymentDate     = reader["PaymentDate"] != DBNull.Value ? Convert.ToDateTime(reader["PaymentDate"].ToString()) :
                                                    DateTime.MinValue;

                        voucher.PaymentTypeId   = (Structures.PaymentTypes)Convert.ToInt32(reader["PaymentTypeId"].ToString());
                        voucher.ChequeNo        = reader["ChequeNo"].ToString();
                        voucher.ChequeDate      = reader["ChequeDate"] != DBNull.Value ? Convert.ToDateTime(reader["ChequeDate"].ToString()) :
                                                    DateTime.MinValue;
                        voucher.Comment         = reader["Comment"] != DBNull.Value ? reader["Comment"].ToString() : "";

                        string cardtype         = reader["CardType"] != DBNull.Value ? reader["CardType"].ToString().Trim() : Structures.CardTypes.NONE.ToString();

                        switch (cardtype)
                        {
                            case "NONE":
                                voucher.CardType = Structures.CardTypes.NONE;
                                break;
                            case "MASTER":
                                voucher.CardType = Structures.CardTypes.MASTER;
                                break;
                            case "VISA":
                                voucher.CardType = Structures.CardTypes.VISA;
                                break;
                            case "AMERICAN_EXPRESS":
                                voucher.CardType= Structures.CardTypes.AMERICAN_EXPRESS;
                                break;
                            default:
                                break;
                        }

                        result = true;
                    }
                }

                //if (result)
                //{
                    this.GetVoucherDetailsByID(voucher);
                //}
            }
            catch (System.Exception ex)
            {
                result = false;
                throw ex;
            }
            return result;
        }
        public bool GetVoucherByID(VoucherRecievable voucher)
        {
            bool result = false;

            try
            {
                Database  db  = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_GetByID);

                db.AddInParameter(cmd, "@iPaymentID", DbType.Int64, voucher.PaymentID);

                IDataReader reader = db.ExecuteReader(cmd);

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        voucher.PaymentCode   = reader["PaymentCode"].ToString();
                        voucher.CustomerID    = Convert.ToInt32(reader["CustomerID"].ToString());
                        voucher.PaymentAmount = reader["PaymentAmount"] != DBNull.Value ? Convert.ToDecimal(reader["PaymentAmount"].ToString()) :
                                                Convert.ToDecimal("0");
                        voucher.PaymentDate = reader["PaymentDate"] != DBNull.Value ? Convert.ToDateTime(reader["PaymentDate"].ToString()) :
                                              DateTime.MinValue;

                        voucher.PaymentTypeId = (Structures.PaymentTypes)Convert.ToInt32(reader["PaymentTypeId"].ToString());
                        voucher.ChequeNo      = reader["ChequeNo"].ToString();
                        voucher.ChequeDate    = reader["ChequeDate"] != DBNull.Value ? Convert.ToDateTime(reader["ChequeDate"].ToString()) :
                                                DateTime.MinValue;
                        voucher.Comment = reader["Comment"] != DBNull.Value ? reader["Comment"].ToString() : "";

                        string cardtype = reader["CardType"] != DBNull.Value ? reader["CardType"].ToString().Trim() : Structures.CardTypes.NONE.ToString();

                        switch (cardtype)
                        {
                        case "NONE":
                            voucher.CardType = Structures.CardTypes.NONE;
                            break;

                        case "MASTER":
                            voucher.CardType = Structures.CardTypes.MASTER;
                            break;

                        case "VISA":
                            voucher.CardType = Structures.CardTypes.VISA;
                            break;

                        case "AMERICAN_EXPRESS":
                            voucher.CardType = Structures.CardTypes.AMERICAN_EXPRESS;
                            break;

                        default:
                            break;
                        }

                        result = true;
                    }
                }

                //if (result)
                //{
                this.GetVoucherDetailsByID(voucher);
                //}
            }
            catch (System.Exception ex)
            {
                result = false;
                throw ex;
            }
            return(result);
        }
        public bool AddVoucher(VoucherRecievable voucher)
        {
            bool result = false;

            try
            {
                Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
                connection = db.CreateConnection();
                connection.Open();

                transaction = connection.BeginTransaction();

                DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_InsVoucher);

                db.AddInParameter(cmd, "@iCustomerID", DbType.Int32, voucher.CustomerID);
                db.AddInParameter(cmd, "@mPaymentAmount", DbType.Currency, voucher.PaymentAmount);
                db.AddInParameter(cmd, "@dtPaymentDate", DbType.DateTime, voucher.PaymentDate);
                db.AddInParameter(cmd, "@iPaymentTypeId", DbType.Int32, (int)voucher.PaymentTypeId);

                if (voucher.PaymentTypeId == Structures.PaymentTypes.CASH)
                {
                    db.AddInParameter(cmd, "@sChequeNo", DbType.String, DBNull.Value);
                    db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);
                    db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);
                    db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);
                }
                else if (voucher.PaymentTypeId == Structures.PaymentTypes.CHEQUE)
                {
                    db.AddInParameter(cmd, "@sChequeNo", DbType.String, voucher.ChequeNo);
                    db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, voucher.ChequeDate);
                    db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);
                    db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);
                }
                else if (voucher.PaymentTypeId == Structures.PaymentTypes.CREDIT_CARD)
                {
                    db.AddInParameter(cmd, "@sChequeNo", DbType.String, voucher.ChequeNo);
                    db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);
                    db.AddInParameter(cmd, "@sCardType", DbType.String, voucher.CardType);
                    db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, voucher.CardCommisionRate);
                }

                if (voucher.Comment == string.Empty)
                {
                    db.AddInParameter(cmd, "@sComment", DbType.String, DBNull.Value);
                }
                else
                {
                    db.AddInParameter(cmd, "@sComment", DbType.String, voucher.Comment);
                }

                db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);

                db.AddOutParameter(cmd, "@inewPaymentID", DbType.Int64, 8);
                db.AddOutParameter(cmd, "@snewPaymentCode", DbType.String, 20);

                if (db.ExecuteNonQuery(cmd, transaction) > 0)
                {
                    Int64 newVoucherID = 0;
                    Int64.TryParse(db.GetParameterValue(cmd, "@inewPaymentID").ToString().Trim(), out newVoucherID);

                    if (newVoucherID > 0)
                    {
                        voucher.PaymentID   = newVoucherID;
                        voucher.PaymentCode = db.GetParameterValue(cmd, "@snewPaymentCode").ToString().Trim();

                        if (this.UpdateVoucherDetails(voucher, db, transaction))
                        {
                            transaction.Commit();
                            result = true;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                transaction.Rollback();
                result = false;
                throw ex;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return(result);
        }