public DataSet GetInvoiceGeneralReportData(DateTime?fromDate, DateTime?toDate, string invoiceType = "", string partsDescription = "", string customerName = "")
{
try
{
SqlCommand command = new SqlCommand();
ISqlConnectionHelper sqlHelper = new SqlConnectionHelper(ConfigurationManager.ConnectionStrings["BrownsAppDBConnectionString"].ConnectionString);
DataTable dtResults = new DataTable();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Billing.GeneralReport";
command.Parameters.AddWithValue("@InvoiceType", string.IsNullOrEmpty(invoiceType) ? null : invoiceType.Trim());
command.Parameters.AddWithValue("@PartDescription", string.IsNullOrEmpty(partsDescription) ? null : partsDescription.Trim());
command.Parameters.AddWithValue("@CustomerName", string.IsNullOrEmpty(customerName) ? null : customerName.Trim());
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);
return(sqlHelper.ExecuteStoredProcedure(command));
}
catch (Exception ex)
{
ExceptionHandler exceptionHandler = new ExceptionHandler();
exceptionHandler.WrapLogException(ex);
return(null);
}
}
public List <SalesReportDTO> GetSalesReportData(string fromDate, string toDate)
{
try
{
SqlCommand command = new SqlCommand();
ISqlConnectionHelper sqlHelper = new SqlConnectionHelper(ConfigurationManager.ConnectionStrings["BrownsAppDBConnectionString"].ConnectionString);
DataTable dtResults = new DataTable();
DataSet dsResults = new DataSet();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "Billing.GetSalesReportData";
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);
dsResults = sqlHelper.ExecuteStoredProcedure(command);
if (dsResults != null && dsResults.Tables.Count > 0)
{
return(ConvertToList(dsResults));
}
else
{
return(null);
}
}
catch (Exception ex)
{
ExceptionHandler exceptionHandler = new ExceptionHandler();
exceptionHandler.WrapLogException(ex);
return(null);
}
}
private int UpdatePartsPrice(DataTable partsPriceDt)
{
// partsPriceDt.DefaultView.ToTable(true, "PartNumber");
int updatedCount = 0;
int Count = 0;
for (int i = 0; i <= partsPriceDt.Rows.Count; i++)
{
string partNumber = partsPriceDt.Rows[i]["PartNumber"].ToString();
string listPrice = partsPriceDt.Rows[i]["ListPrice"].ToString();
if (!string.IsNullOrEmpty(listPrice))
{
SqlCommand command = new SqlCommand();
ISqlConnectionHelper sqlHelper = new SqlConnectionHelper(ConfigurationManager.ConnectionStrings["BrownsAppDBConnectionString"].ConnectionString);
DataTable dtResults = new DataTable();
DataSet dsResults = new DataSet();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.UpdatePartsPrice";
command.Parameters.AddWithValue("@PartNumber", partNumber);
command.Parameters.AddWithValue("@ListPrice", listPrice);
dsResults = sqlHelper.ExecuteStoredProcedure(command);
Count = dsResults.Tables[0].Rows[0]["UPDATEDCOUNT"] != null?Convert.ToInt32(dsResults.Tables[0].Rows[0]["UPDATEDCOUNT"]) : 0;
updatedCount = updatedCount + Count;
}
}
return(updatedCount);
}
public List <PartDTO> GetPartData(PartDTO partDTO)
{
try
{
SqlCommand command = new SqlCommand();
ISqlConnectionHelper sqlHelper = new SqlConnectionHelper(ConfigurationManager.ConnectionStrings["BrownsAppDBConnectionString"].ConnectionString);
DataTable dtResults = new DataTable();
DataSet dsResults = new DataSet();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.GetPartReportData";
command.Parameters.AddWithValue("@Make", string.IsNullOrEmpty(partDTO.Make) ? null : partDTO.Make.Trim());
command.Parameters.AddWithValue("@Model", string.IsNullOrEmpty(partDTO.Model) ? null : partDTO.Model.Trim());
command.Parameters.AddWithValue("@SerialNumberRange", string.IsNullOrEmpty(partDTO.SerialNumberRange) ? null : partDTO.SerialNumberRange.Trim());
command.Parameters.AddWithValue("@PartNumber", string.IsNullOrEmpty(partDTO.PartNumber) ? null : partDTO.PartNumber.Trim());
command.Parameters.AddWithValue("@PartManual", string.IsNullOrEmpty(partDTO.PartManual) ? null : partDTO.PartManual.Trim());
command.Parameters.AddWithValue("@PartDescription", string.IsNullOrEmpty(partDTO.PartDescription) ? null : partDTO.PartDescription.Trim());
dsResults = sqlHelper.ExecuteStoredProcedure(command);
if (dsResults != null && dsResults.Tables.Count > 0)
{
return(ConvertToList(dsResults));
}
else
{
return(null);
}
}
catch (Exception ex)
{
ExceptionHandler exceptionHandler = new ExceptionHandler();
exceptionHandler.WrapLogException(ex);
return(null);
}
}