//To add data to the Invoice Table
public bool InsertPaymentDetails(PaymentDetailsEntity objPay)
{
objCommand.Parameters.Clear();
bool ii = false;
try
{
objCommand.CommandText = "sp_InsertPaymentRecord";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@transactionId", SqlDbType.VarChar).Value = objPay.TransactionId;
objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = objPay.InvoiceId;
objCommand.Parameters.Add("@clientName", SqlDbType.VarChar).Value = objPay.ClientName;
objCommand.Parameters.Add("@paymentType", SqlDbType.VarChar).Value = objPay.PaymentType;
objCommand.Parameters.Add("@currencyType", SqlDbType.VarChar).Value = objPay.CurrencyType;
objCommand.Parameters.Add("@amount", SqlDbType.Decimal).Value = objPay.Amount;
objCommand.Parameters.Add("@paymentStatus", SqlDbType.VarChar).Value =objPay.PaymentStatus;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
ii = DLCommand.ExecuteNonQuery(DLConfig);
}
catch (Exception)
{
throw;
}
return ii;
}
public DataTable getAllNonDeletedInvoiceRecs(string cName, out int count)
{
try
{
objCommand.Parameters.Clear();
objCommand.CommandText = "sp_GetAllInvoiceRecords";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 10).Value = cName;
SqlParameter totCount = objCommand.Parameters.Add("@totalRecs", SqlDbType.Int);
totCount.Direction = ParameterDirection.Output;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType);
count = Convert.ToInt32(totCount.Value);
if (ds.Tables[0].Rows.Count != 0)
{
return ds.Tables[0];
}
else { return null; }
}
catch (Exception)
{
throw;
}
}
public object ExecuteBatchUpdate(ConfigSettings configuration, DataSet ds)
{
Connection(configuration);
((IDbCommand)configuration.DBCommand).Connection = GenericConnection;
switch (configuration.DataStore)
{
case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break;
case DataProviderType.OleDb: GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break;
case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break;
}
GenericDataAdapter.UpdateCommand = (IDbCommand)configuration.DBCommand;
int i = GenericDataAdapter.Update(ds);
GenericConnection.Close();
GenericConnection.Dispose();
return (object)i;
}
public bool DeleteInvoiceRecs(string invoiceIds)
{
objCommand.Parameters.Clear();
bool ii = false;
try
{
objCommand.CommandText = "sp_DeleteInvoiceRecords";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@invoiceIds", SqlDbType.VarChar).Value = invoiceIds;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
ii = DLCommand.ExecuteNonQuery(DLConfig);
}
catch (Exception)
{
throw;
}
return ii;
}
public bool ExecuteNonQuery(ConfigSettings configuration)
{
try
{
Connection(configuration);
((IDbCommand)configuration.DBCommand).Connection = GenericConnection;
((IDbCommand)configuration.DBCommand).ExecuteNonQuery();
GenericConnection.Close();
return true;
}
catch (Exception Ex)
{
////To add additional information in the exception
//NameValueCollection colDataInfo = new NameValueCollection();
//colDataInfo.Add("Method","Unable to execute non query");
//BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex);
////Addiding additional information to the exception
//exBase.AdditionalInformation = colDataInfo;
//throw exBase;
return false;
}
}
public DataTable getAllNonDeletedInvoiceRecs_Pagewise(string cName, string orderBy, string dir, int pageIndex, int pageSize, out int count)
{
try
{
objCommand.Parameters.Clear();
objCommand.CommandText = "sp_GetAllNonDeletedInvoiceRecords_Pagewise";
objCommand.CommandType = CommandType.StoredProcedure;
if(cName.ToLower()=="--select--")
objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 50).Value = DBNull.Value;
else
objCommand.Parameters.Add("@ClientName", SqlDbType.VarChar, 50).Value = cName;
objCommand.Parameters.Add("@orderby", SqlDbType.VarChar, 50).Value = orderBy;
objCommand.Parameters.Add("@dir", SqlDbType.VarChar, 50).Value = dir;
objCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
objCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
SqlParameter totCount = objCommand.Parameters.Add("@totalRecs", SqlDbType.Int);
totCount.Direction = ParameterDirection.Output;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType);
count = Convert.ToInt32(totCount.Value);
//if (ds.Tables[0].Rows.Count != 0)
//{
return ds.Tables[0];
//}
//else { return null; }
}
catch (Exception)
{
throw;
}
}
public bool UpdateInvoiceRecByPaidType(int invoiceId, string PaidVia)
{
objCommand.Parameters.Clear();
bool status = false;
try
{
objCommand.CommandText = "sp_UpdateInvoiceRecordByPaidType";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = invoiceId;
objCommand.Parameters.Add("@PaidVia", SqlDbType.VarChar).Value = PaidVia;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
status = DLCommand.ExecuteNonQuery(DLConfig);
}
catch (Exception)
{
throw;
}
return status;
}
//To add data to the Invoice Table
public bool InsertInvoiceRec(InvoiceDetailsEntity objInvoice, out int PKid)
{
objCommand.Parameters.Clear();
bool ii = false;
try
{
objCommand.CommandText = "sp_InsertInvoiceRecord";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@invoiceId", SqlDbType.VarChar).Value = objInvoice.InvoiceNo;
objCommand.Parameters.Add("@invoiceDate", SqlDbType.DateTime).Value = objInvoice.InvoiceDate;
objCommand.Parameters.Add("@clientName", SqlDbType.VarChar).Value = objInvoice.ClientName;
objCommand.Parameters.Add("@clientAddress", SqlDbType.VarChar).Value = objInvoice.ClientAddress;
objCommand.Parameters.Add("@emailAddress", SqlDbType.VarChar).Value = objInvoice.EmailAddress;
objCommand.Parameters.Add("@NoOfEmails", SqlDbType.Int).Value = objInvoice.NoOfEmails;
objCommand.Parameters.Add("@discountAmount", SqlDbType.Decimal).Value = objInvoice.DiscountAmount;
objCommand.Parameters.Add("@taxPercentage", SqlDbType.Decimal).Value = objInvoice.TaxPercentage;
objCommand.Parameters.Add("@taxAmount", SqlDbType.Decimal).Value = objInvoice.TaxAmount;
objCommand.Parameters.Add("@priceRate", SqlDbType.Decimal).Value = objInvoice.PriceRate;
objCommand.Parameters.Add("@subAmount", SqlDbType.Decimal).Value = objInvoice.SubAmount;
objCommand.Parameters.Add("@totalAmount", SqlDbType.Decimal).Value = objInvoice.TotalAmount;
objCommand.Parameters.Add("@note", SqlDbType.VarChar).Value = objInvoice.Note;
objCommand.Parameters.Add("@status", SqlDbType.VarChar).Value = objInvoice.Status;
objCommand.Parameters.Add("@startDate", SqlDbType.DateTime).Value = objInvoice.StartDate;
objCommand.Parameters.Add("@endDate", SqlDbType.DateTime).Value = objInvoice.EndDate;
objCommand.Parameters.Add("@description", SqlDbType.VarChar).Value = objInvoice.Descripation;
objCommand.Parameters.Add("@OutPKid", SqlDbType.Int).Direction = ParameterDirection.Output;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
ii = DLCommand.ExecuteNonQuery(DLConfig);
PKid = Convert.ToInt32(objCommand.Parameters["@OutPKid"].Value);
}
catch (Exception)
{
throw;
}
return ii;
}
public bool InsertInvoiceDescription(List<InvoiceDetailsEntity> ListobjInvoice)
{
objCommand.Parameters.Clear();
bool ii = false;
try
{
DataTable dtDesc = new DataTable("TableType_InsertInvoiceDesc");
dtDesc.Columns.Add("InvoiceId", typeof(int));
dtDesc.Columns.Add("InvoiceNo", typeof(string));
dtDesc.Columns.Add("ClientName", typeof(string));
dtDesc.Columns.Add("Description", typeof(string));
dtDesc.Columns.Add("SubAmount", typeof(decimal));
for (int i = 0; i < ListobjInvoice.Count; i++)
{
dtDesc.Rows.Add(ListobjInvoice[i].InvoiceId, ListobjInvoice[i].InvoiceNo, ListobjInvoice[i].ClientName, ListobjInvoice[i].Descripation, ListobjInvoice[i].SubAmount);
}
objCommand.CommandText = "sp_InsertInvoiceDescription";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@DT_Table", dtDesc);
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
ii = DLCommand.ExecuteNonQuery(DLConfig);
}
catch (Exception)
{
throw;
}
return ii;
}
//Get From Level4 List
public DataTable getSuperAdminUserName()
{
try
{
objCommand.Parameters.Clear();
objCommand.CommandText = "sp_getSuperAdminUserName";
objCommand.CommandType = CommandType.StoredProcedure;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType);
if (ds.Tables[0].Rows.Count != 0)
{
return ds.Tables[0];
}
else { return null; }
}
catch (Exception)
{
throw;
}
}
public int getLastInvoiceID()
{
objCommand.Parameters.Clear();
try
{
objCommand.CommandText = "sp_GetInvoiceId";
objCommand.CommandType = CommandType.StoredProcedure;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
int Count = (int)DLCommand.ExecuteQuery(DLConfig, ReturnType.ScalarType);
return Count;
}
catch (Exception)
{
throw;
}
}
public DataTable getInvoiceRec(int invoiceId)
{
try
{
objCommand.Parameters.Clear();
objCommand.CommandText = "sp_GetInvoiceRecord";
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@invoiceId", SqlDbType.Int).Value = invoiceId;
DLConfig = new ConfigSettings(Globals.DBConnString, objCommand, DataProviderType.Sql);
DataSet ds = (DataSet)DLCommand.ExecuteQuery(DLConfig, ReturnType.DataSetType);
if (ds.Tables[0].Rows.Count != 0)
{
return ds.Tables[0];
}
else { return null; }
}
catch (Exception)
{
throw;
}
}
public object ExecuteQuery(ConfigSettings configuration, ReturnType returnType)
{
try
{
Connection(configuration);
((IDbCommand)configuration.DBCommand).Connection = GenericConnection;
object obj = null;
switch (returnType)
{
case ReturnType.DataSetType:
DSQueryReturn = new DataSet();
switch (configuration.DataStore)
{
case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break;
case DataProviderType.OleDb: GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break;
case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break;
}
GenericDataAdapter.Fill(DSQueryReturn);
GenericConnection.Close();
GenericConnection.Dispose();
obj = DSQueryReturn;
break;
case ReturnType.DataReaderType:
obj = ((IDbCommand)configuration.DBCommand).ExecuteReader(CommandBehavior.CloseConnection);
break;
case ReturnType.ScalarType:
obj = ((IDbCommand)configuration.DBCommand).ExecuteScalar();
GenericConnection.Close();
GenericConnection.Dispose();
break;
case ReturnType.XmlDocumentType:
switch (configuration.DataStore)
{
case DataProviderType.Sql: GenericDataAdapter = new SqlDataAdapter((SqlCommand)configuration.DBCommand); break;
case DataProviderType.OleDb : GenericDataAdapter = new OleDbDataAdapter((OleDbCommand)configuration.DBCommand); break;
case DataProviderType.Odbc: GenericDataAdapter = new OdbcDataAdapter((OdbcCommand)configuration.DBCommand); break;
}
GenericDataAdapter.Fill(DSQueryReturn);
XmlDataDocument XmlDDoc = new XmlDataDocument(DSQueryReturn);
obj = XmlDDoc;
GenericConnection.Close();
GenericConnection.Dispose();
break;
default:
throw new ApplicationException("Paramter Type are Incorrect");
break;
}
return obj;
}
catch (ApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
////To add additional information in the exception
//NameValueCollection colDataInfo = new NameValueCollection();
//colDataInfo.Add("Method","Unable to execute query");
//BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex);
////Addiding additional information to the exception
//exBase.AdditionalInformation = colDataInfo;
//throw exBase;
throw Ex;
}
finally
{
GenericConnection.Close();
GenericConnection.Dispose();
}
}
private void Connection(ConfigSettings configuration)
{
try
{
switch (configuration.DataStore)
{
case DataProviderType.Sql: GenericConnection = new SqlConnection(configuration.ConnectionString); break;
case DataProviderType.Odbc: GenericConnection = new OdbcConnection(configuration.ConnectionString); break;
case DataProviderType.OleDb: GenericConnection = new OleDbConnection(configuration.ConnectionString); break;
}
GenericConnection.Open();
}
catch (Exception Ex)
{
////To add additional information in the exception
//NameValueCollection colDataInfo = new NameValueCollection();
//colDataInfo.Add("ConnectionString",GenericConnection.ConnectionString);
//BaseApplicationException exBase = new BaseApplicationException(Ex.Message,Ex);
////Adding additional information to the exception
//exBase.AdditionalInformation = colDataInfo;
//throw exBase;
}
}
