public static string GetPaginationLimit(Objects.Pagination pagination)
{
var row = pagination.CurrentPage * pagination.MaxResults;
var limit = string.Format(" LIMIT {0}, {1}", row, pagination.MaxResults);
return(limit);
}
/// <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>
/// 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);
}
/// <summary>
/// Get Inventory Products
/// </summary>
/// <returns></returns>
public Response GetInventoryProducts(Objects.InventorySearchQuery searchQuery, Objects.Pagination pagination)
{
int statusCode = 200;
Response response = new Response();
List <object> products = new List <object>();
try
{
Database.Connection.Open();
var q = $"%{searchQuery.Query}%";
var query = GetInventorySearchQuery(searchQuery) + Func.GetPaginationLimit(pagination);
//Execute Query
MySqlCommand Command = new MySqlCommand(query, Database.Connection);
Command.Parameters.AddWithValue("SearchQuery", q);
Command.Parameters.AddWithValue("StockQuery", searchQuery.StockQuery);
Command.Parameters.AddWithValue("DateFrom", searchQuery.DateFrom);
Command.Parameters.AddWithValue("DateTo", searchQuery.DateTo);
Command.Parameters.AddWithValue("Status", searchQuery.Status);
MySqlDataReader DataReader = Command.ExecuteReader();
//Read Data
while (DataReader.Read())
{
Objects.Product product = new Objects.Product
{
Id = DataReader["ProductId"].ToString(),
Barcode = DataReader["Barcode"].ToString(),
Name = DataReader["Name"].ToString(),
Description = DataReader["Description"].ToString(),
Stock = Func.ToDecimal(DataReader["Stock"].ToString()),
IsBalanceRequired = Func.ToBoolean(DataReader["IsBalanceRequired"].ToString()),
SupplyPrice = Func.ToDecimal(DataReader["SupplyPrice"].ToString()),
RetailPrice = Func.ToDecimal(DataReader["RetailPrice"].ToString()),
Markup = Func.ToDecimal(DataReader["Markup"].ToString()),
ImageBlobId = DataReader["ImageBlobId"].ToString(),
Color = ColorUtil.HEXToColor(DataReader["Color"].ToString()),
UpdatedAt = Convert.ToDateTime(DataReader["UpdatedAt"].ToString()),
CreatedAt = Convert.ToDateTime(DataReader["CreatedAt"].ToString()),
Tax = new Objects.Tax {
Id = DataReader["TaxId"].ToString()
},
Brand = new Objects.ProductBrand {
Id = DataReader["BrandId"].ToString()
},
Supplier = new Objects.ProductSupplier {
Id = DataReader["SupplierId"].ToString()
},
Type = new Objects.ProductType {
Id = DataReader["TypeId"].ToString()
},
IsSearchItem = true,
};
products.Add(product);
}
if (!DataReader.HasRows)
{
// Nothing
statusCode = 204;
}
else
{
// OK
statusCode = 200;
}
Database.Connection.Close();
//Response
response.StatusCode = statusCode;
response.DataArray = products;
response.Pagination = pagination;
}
catch (MySqlException e)
{
Logger.QueryError(e, "Product", "Getting Products");
// Internal Error :
response.StatusCode = 500;
}
//Close Connection if Open
if (Database.Connection.State == ConnectionState.Open)
{
Database.Connection.Close();
}
return(response);
}