private string GetSalesLegersSearchQuery(Objects.SaleSearchQuery searchQuery)
{
var query = " SELECT * FROM Sales ";
var append = " ORDER BY SoldAt DESC ";
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(query + append);
case Enums.SearchQueryType.Query:
return(query + " WHERE ( SaleId LIKE @SearchQuery OR NOTE LIKE @SearchQuery ) " + append);
case Enums.SearchQueryType.Customer:
return(query + " WHERE CustomerId=@CustomerId" + append);
case Enums.SearchQueryType.User:
return(query + " WHERE UserId=@UserId" + append);
case Enums.SearchQueryType.DateRange:
return(query + " WHERE SoldAt BETWEEN @DateFrom AND @DateTo " + append);
default:
return(query + append);
}
}
/// <summary>
/// Get Payments
/// </summary>
/// <returns></returns>
public Response GetPayments(Objects.SaleSearchQuery searchQuery, Objects.Pagination pagination)
{
int statusCode = 200;
Response response = new Response();
List <object> payments = new List <object>();
try
{
Database.Connection.Open();
var query = GetPaymentsSearchQuery(searchQuery) + Func.GetPaginationLimit(pagination);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("DateFrom", searchQuery.DateFrom);
Command.Parameters.AddWithValue("DateTo", searchQuery.DateTo);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.Payment payment = new Objects.Payment
{
PaidAmount = decimal.Parse(DataReader["PaidAmount"].ToString()),
Method = DataReader["PaymentMethod"].ToString(),
PaidAt = Convert.ToDateTime(DataReader["SoldAt"].ToString()),
};
payments.Add(payment);
}
if (!DataReader.HasRows)
{
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = payments;
response.Pagination = pagination;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Sale", "Getting Payments");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
/// <summary>
/// Sum Taxes
/// </summary>
/// <returns></returns>
public Response SumTaxes(Objects.SaleSearchQuery searchQuery)
{
int statusCode = 200;
Response response = new Response();
IDictionary <string, decimal> data = new Dictionary <string, decimal>();
try
{
Database.Connection.Open();
var query = GetTaxesSearchQuery(searchQuery);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("DateFrom", searchQuery.DateFrom);
Command.Parameters.AddWithValue("DateTo", searchQuery.DateTo);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
data.Add("TaxAmount", Func.ToDecimal(DataReader["TaxAmount"].ToString()));
data.Add("TotalPrice", Func.ToDecimal(DataReader["TotalPrice"].ToString()));
}
if (!DataReader.HasRows)
{
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.Data = data;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Sale", "Summing Taxes");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
private string GetTaxesSearchQuery(Objects.SaleSearchQuery searchQuery)
{
var query = " SELECT SUM(TaxAmount) AS TaxAmount, SUM(TotalPrice) AS TotalPrice FROM Sales ";
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(query);
case Enums.SearchQueryType.DateRange:
return(query + " WHERE SoldAt BETWEEN @DateFrom AND @DateTo ");
default:
return(query);
}
}
private string GetPaymentsSearchQuery(Objects.SaleSearchQuery searchQuery)
{
var query = " SELECT SUM(TotalPrice) AS PaidAmount, PaymentMethod, SoldAt FROM Sales ";
var append = " GROUP BY SoldAt";
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(query + append);
case Enums.SearchQueryType.DateRange:
return(query + " WHERE SoldAt BETWEEN @DateFrom AND @DateTo " + append);
default:
return(query + append);
}
}
private string GetSearchQuery(Objects.SaleSearchQuery searchQuery)
{
var query = " SELECT * FROM Sales ";
var append = " ORDER BY SoldAt DESC ";
switch (searchQuery.Type)
{
case Enums.SearchQueryType.None:
return(query + append);
case Enums.SearchQueryType.Id:
return(query + " WHERE UserId=@UserId" + append);
case Enums.SearchQueryType.DateRange:
return(query + " WHERE SoldAt BETWEEN @DateFrom AND @DateTo " + append);
default:
return(query + append);
}
}
/// <summary>
/// Get SaleLedgers
/// </summary>
/// <returns></returns>
public Response GetSaleLedgers(Objects.SaleSearchQuery searchQuery, Objects.Pagination pagination)
{
int statusCode = 200;
Response response = new Response();
List <object> sales = new List <object>();
try
{
Database.Connection.Open();
var q = "%" + searchQuery.Query + "%";
var query = GetSalesLegersSearchQuery(searchQuery) + Func.GetPaginationLimit(pagination);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SearchQuery", q);
Command.Parameters.AddWithValue("UserId", searchQuery.UserId);
Command.Parameters.AddWithValue("CustomerId", searchQuery.CustomerId);
Command.Parameters.AddWithValue("DateFrom", searchQuery.DateFrom);
Command.Parameters.AddWithValue("DateTo", searchQuery.DateTo);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.Sale sale = new Objects.Sale
{
Id = DataReader["SaleId"].ToString(),
Note = DataReader["Note"].ToString(),
DiscountName = DataReader["DiscountName"].ToString(),
DiscountAmount = decimal.Parse(DataReader["DiscountAmount"].ToString()),
DiscountPercentage = decimal.Parse(DataReader["DiscountPercentage"].ToString()),
ItemsCount = int.Parse(DataReader["ItemsCount"].ToString()),
SupplyPrice = decimal.Parse(DataReader["SupplyPrice"].ToString()),
SubTotalPrice = decimal.Parse(DataReader["SubTotalPrice"].ToString()),
DiscountPrice = decimal.Parse(DataReader["DiscountPrice"].ToString()),
TaxAmount = decimal.Parse(DataReader["TaxAmount"].ToString()),
TotalPrice = decimal.Parse(DataReader["TotalPrice"].ToString()),
PaymentMethod = DataReader["PaymentMethod"].ToString(),
Paid = decimal.Parse(DataReader["Paid"].ToString()),
Balance = decimal.Parse(DataReader["Balance"].ToString()),
IsDiscounted = Func.ToBoolean(DataReader["IsDiscounted"].ToString()),
IsGuest = Func.ToBoolean(DataReader["IsGuest"].ToString()),
User = new Objects.User {
Id = DataReader["UserId"].ToString()
},
Customer = new Objects.Customer {
Id = DataReader["CustomerId"].ToString()
},
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
SoldAt = Convert.ToDateTime(DataReader["SoldAt"].ToString()),
IsFetchItem = true,
};
sales.Add(sale);
}
if (!DataReader.HasRows)
{
// Nothing
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = sales;
response.Pagination = pagination;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Sale", "Getting Sale Ledgers");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}
