protected override void FillUpdateParams(CustomSqlCommand in_sp)
{
base.FillUpdateParams(in_sp);
_modified = DateTime.Now;
in_sp.AddParameter("@in_modified", UnixEpoch.FromDateTime(_modified));
in_sp.AddParameter("@in_matrixId", _matrixId);
in_sp.AddParameter("@in_priceOfPurchase", _priceOfPurchase);
in_sp.AddParameter("@in_priceOfSell", _priceOfSell);
}
protected override void FillUpdateParams(CustomSqlCommand in_sp)
{
base.FillUpdateParams(in_sp);
_modified = DateTime.Now;
in_sp.AddParameter("@in_modified", UnixEpoch.FromDateTime(_modified));
in_sp.AddParameter("@in_articleId", _parent.Article.Id);
in_sp.AddParameter("@in_pointOfSaleId", _parent.PointOfSale.Id);
in_sp.AddParameter("@in_x", _x);
in_sp.AddParameter("@in_y", _y);
in_sp.AddParameter("@in_amount", _amount);
}
protected override void FillUpdateParams(CustomSqlCommand in_sp)
{
const string c_timeCancelledParam = "@in_timeCancelled";
base.FillUpdateParams(in_sp);
in_sp.AddParameter("@in_type", (long)_type);
if (_timeCancelled.HasValue)
{
in_sp.AddParameter(c_timeCancelledParam, UnixEpoch.FromDateTime(_timeCancelled.Value));
}
else
{
in_sp.AddParameter(c_timeCancelledParam, (long?)null);
}
}
protected override void FillUpdateParams(CustomSqlCommand in_sp)
{
base.FillUpdateParams(in_sp);
Id = _doc.Id;
in_sp.AddParameter("@in_docId", Id);
in_sp.AddParameter("@in_timeSold", UnixEpoch.FromDateTime(_timeSold));
in_sp.AddParameter("@in_articleId", _articleId);
in_sp.AddParameter("@in_articleName", _articleName);
in_sp.AddParameter("@in_articlePriceOfPurchase", _articlePriceOfPurchase);
in_sp.AddParameter("@in_articlePriceOfSell", _articlePriceOfSell);
in_sp.AddParameter("@in_pointOfSaleId", _pointOfSaleId);
in_sp.AddParameter("@in_pointOfSaleName", _pointOfSaleName);
in_sp.AddParameter("@in_unitPrice", _unitPrice);
in_sp.AddParameter("@in_unitCount", _unitCount);
in_sp.AddParameter("@in_cellX", _cellX);
in_sp.AddParameter("@in_cellY", _cellY);
in_sp.AddParameter("@in_paymentByCard", _paymentByCard ? 1 : 0);
}
public static DataTable ReadSalesJournal(PointOfSale in_pointOfSale, DateTime in_dateBegin, DateTime in_dateEnd, ShowRows in_displayMode)
{
using SQLiteConnection conn = ConnectionRegistry.Instance.OpenNewConnection();
using SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter("@in_pointOfSaleId", in_pointOfSale.Id));
cmd.Parameters.Add(new SQLiteParameter("@in_dateBegin", UnixEpoch.FromDateTime(in_dateBegin)));
cmd.Parameters.Add(new SQLiteParameter("@in_dateEndNext", UnixEpoch.FromDateTime(in_dateEnd.AddDays(1))));
string wherePayedByCard = string.Empty;
if (in_displayMode == ShowRows.OnlyCash || in_displayMode == ShowRows.OnlyCard)
{
cmd.Parameters.Add(new SQLiteParameter("@in_payedByCard", in_displayMode == ShowRows.OnlyCash ? 0 : 1));
wherePayedByCard = " and s.payment_by_card = @in_payedByCard";
}
cmd.CommandText = "select" +
" s.id," +
" date(s.time_sold, 'unixepoch', 'localtime') as time_sold," +
" s.art_name," +
" s.art_price_of_purchase," +
" s.art_price_of_sell," +
" s.unit_price," +
" s.unit_count," +
" (s.unit_price * s.unit_count) as price_sum," +
" s.cell_x," +
" s.cell_y" +
" from doc_sale s" +
" join doc d on d.id = s.id and d.time_cancelled is null" +
" where s.point_of_sale_id = @in_pointOfSaleId" +
" and @in_dateBegin <= s.time_sold and s.time_sold < @in_dateEndNext" +
wherePayedByCard +
" order by s.time_sold desc;";
DataTable table = new DataTable();
using (SQLiteDataAdapter a = new SQLiteDataAdapter(cmd))
a.Fill(table);
return(table);
}
public static Summary GetSummary(PointOfSale in_pointOfSale, DateTime in_dateBegin, DateTime in_dateEnd, ShowRows in_displayMode)
{
using SQLiteConnection conn = ConnectionRegistry.Instance.OpenNewConnection();
using SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SQLiteParameter("@in_pointOfSaleId", in_pointOfSale.Id));
cmd.Parameters.Add(new SQLiteParameter("@in_dateBegin", UnixEpoch.FromDateTime(in_dateBegin)));
cmd.Parameters.Add(new SQLiteParameter("@in_dateEndNext", UnixEpoch.FromDateTime(in_dateEnd.AddDays(1))));
string wherePayedByCard = string.Empty;
if (in_displayMode == ShowRows.OnlyCash || in_displayMode == ShowRows.OnlyCard)
{
cmd.Parameters.Add(new SQLiteParameter("@in_payedByCard", in_displayMode == ShowRows.OnlyCash ? 0 : 1));
wherePayedByCard = " and s.payment_by_card = @in_payedByCard";
}
cmd.CommandText = "select" +
" COUNT(s.id) as cnt," +
" IFNULL(SUM(s.unit_price * s.unit_count), 0) as sm," +
" IFNULL(SUM(s.unit_price * s.unit_count - s.art_price_of_purchase * s.unit_count), 0) as profit" +
" from doc_sale s" +
" join doc d on d.id = s.id and d.time_cancelled is null" +
" where s.point_of_sale_id = @in_pointOfSaleId" +
" and @in_dateBegin <= s.time_sold and s.time_sold < @in_dateEndNext" +
wherePayedByCard;
DataTable table = new DataTable();
using (SQLiteDataAdapter a = new SQLiteDataAdapter(cmd))
a.Fill(table);
DataRow row = table.Rows[0];
return(new Summary()
{
Count = (int)(long)row["cnt"],
Sum = (int)(long)row["sm"],
Profit = (int)(long)row["profit"]
});
}
