/// <summary>
/// Получает всю сверку по партнерам или торговым точкам
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public BookkeepingsResponse GetAllBookkeeping(BookkeepingRequest request)
{
BookkeepingsResponse response = new BookkeepingsResponse();
try
{
var bookkeepings = new List <Bookkeeping>();
_cnn.Open();
var cmd = _cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = request.IsOperator ? "OperatorBookkeepingPaging" : "BookkeepingPaging";
cmd.CommandTimeout = 300;
if (request.Operator > 0)
{
cmd.Parameters.AddWithValue("@operator", request.Operator);
}
if (request.Partner > 0)
{
cmd.Parameters.AddWithValue("@partner", request.Partner);
}
if (request.Pos > 0)
{
cmd.Parameters.AddWithValue("@pos", request.Pos);
}
if (request.Page == 0)
{
request.Page++;
}
cmd.Parameters.AddWithValue("@start", request.Page);
//Если start = -1 и length = -1 это значит выгрузить все без пагинации
if (request.Page != -1)
{
cmd.Parameters.AddWithValue("@length", request.Page + request.PageSize);
}
else
{
cmd.Parameters.AddWithValue("@length", request.PageSize);
}
cmd.Parameters.Add("@errormessage", SqlDbType.NVarChar, 100);
cmd.Parameters["@errormessage"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@result", SqlDbType.Int);
cmd.Parameters["@result"].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@total_rows", SqlDbType.Int);
cmd.Parameters["@total_rows"].Direction = ParameterDirection.Output;
//Фильтр по дате (Верхний фильтр с диапазоном)
if (!string.IsNullOrEmpty(request.DateStart))
{
if (DateTime.TryParseExact(request.DateStart, new[] { "dd.MM.yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
{
cmd.Parameters.AddWithValue("@f_date_start", date.ToString("yyyy-MM-dd"));
}
}
//Фильтр по дате (Верхний фильтр с диапазоном)
if (!string.IsNullOrEmpty(request.DateEnd))
{
if (DateTime.TryParseExact(request.DateEnd, new[] { "dd.MM.yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
{
cmd.Parameters.AddWithValue("@f_date_end", date.ToString("yyyy-MM-dd"));
}
}
//Фильтр по названию
if (!string.IsNullOrEmpty(request.Name))
{
cmd.Parameters.AddWithValue("@f_name", request.Name);
}
if (request.IsOperator && !string.IsNullOrEmpty(request.PosName))
{
cmd.Parameters.AddWithValue("@pos_name", request.PosName);
}
//Фильтр по Покупкам
if (!string.IsNullOrEmpty(request.PurchasesMore))
{
cmd.Parameters.AddWithValue("@f_buy_more", Convert.ToInt64(request.PurchasesMore));
}
if (!string.IsNullOrEmpty(request.PurchasesLess))
{
cmd.Parameters.AddWithValue("@f_buy_less", Convert.ToInt64(request.PurchasesLess));
}
//Фильтр по Начислено
if (!string.IsNullOrEmpty(request.AddedMore))
{
cmd.Parameters.AddWithValue("@f_added_more", Convert.ToInt64(request.AddedMore));
}
if (!string.IsNullOrEmpty(request.AddedLess))
{
cmd.Parameters.AddWithValue("@f_added_less", Convert.ToInt64(request.AddedLess));
}
//Фильтр по Списано
if (!string.IsNullOrEmpty(request.RedeemedMore))
{
cmd.Parameters.AddWithValue("@f_redeemed_more", Convert.ToInt64(request.RedeemedMore));
}
if (!string.IsNullOrEmpty(request.RedeemedLess))
{
cmd.Parameters.AddWithValue("@f_redeemed_less", Convert.ToInt64(request.RedeemedLess));
}
//Фильтр по Клиентам
if (!string.IsNullOrEmpty(request.ClientsMore))
{
cmd.Parameters.AddWithValue("@f_clients_more", Convert.ToInt64(request.ClientsMore));
}
if (!string.IsNullOrEmpty(request.ClientsLess))
{
cmd.Parameters.AddWithValue("@f_clients_less", Convert.ToInt64(request.ClientsLess));
}
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Bookkeeping bookkeeping = new Bookkeeping();
try
{
var i = 0;
if (!reader.IsDBNull(0))
{
bookkeeping.Id = reader.GetInt16(0);
}
if (!reader.IsDBNull(1))
{
bookkeeping.Caption = reader.GetString(1);
}
if (request.Operator > 0 && request.Partner == 0)
{
i = 1;
if (!reader.IsDBNull(2))
{
bookkeeping.PosName = reader.GetString(2);
}
}
if (!reader.IsDBNull(2 + i))
{
bookkeeping.Gain = reader.GetDecimal(2 + i);
}
if (!reader.IsDBNull(3 + i))
{
bookkeeping.Added = reader.GetDecimal(3 + i);
}
if (!reader.IsDBNull(4 + i))
{
bookkeeping.Redeemed = reader.GetDecimal(4 + i);
}
if (!reader.IsDBNull(5 + i))
{
bookkeeping.Clients = reader.GetInt32(5 + i);
}
if (!reader.IsDBNull(6 + i))
{
bookkeeping.GainMonth1 = reader.GetInt64(6 + i);
}
if (!reader.IsDBNull(7 + i))
{
bookkeeping.AddedMonth1 = reader.GetInt64(7 + i);
}
if (!reader.IsDBNull(8 + i))
{
bookkeeping.RedeemedMonth1 = reader.GetInt64(8 + i);
}
if (!reader.IsDBNull(9 + i))
{
bookkeeping.ClientsMonth1 = reader.GetInt64(9 + i);
}
if (!reader.IsDBNull(10 + i))
{
bookkeeping.GainMonth2 = reader.GetInt64(10 + i);
}
if (!reader.IsDBNull(11 + i))
{
bookkeeping.AddedMonth2 = reader.GetInt64(11 + i);
}
if (!reader.IsDBNull(12 + i))
{
bookkeeping.RedeemedMonth2 = reader.GetInt64(12 + i);
}
if (!reader.IsDBNull(13 + i))
{
bookkeeping.ClientsMonth2 = reader.GetInt64(13 + i);
}
if (!reader.IsDBNull(14 + i))
{
bookkeeping.GainMonth3 = reader.GetInt64(14 + i);
}
if (!reader.IsDBNull(15 + i))
{
bookkeeping.AddedMonth3 = reader.GetInt64(15 + i);
}
if (!reader.IsDBNull(16 + i))
{
bookkeeping.RedeemedMonth3 = reader.GetInt64(16 + i);
}
if (!reader.IsDBNull(17 + i))
{
bookkeeping.ClientsMonth3 = reader.GetInt64(17 + i);
}
if (!reader.IsDBNull(18 + i))
{
bookkeeping.GainMonth4 = reader.GetInt64(18 + i);
}
if (!reader.IsDBNull(19 + i))
{
bookkeeping.AddedMonth4 = reader.GetInt64(19 + i);
}
if (!reader.IsDBNull(20 + i))
{
bookkeeping.RedeemedMonth4 = reader.GetInt64(20 + i);
}
if (!reader.IsDBNull(21 + i))
{
bookkeeping.ClientsMonth4 = reader.GetInt64(21 + i);
}
if (!reader.IsDBNull(22 + i))
{
bookkeeping.GainMonth5 = reader.GetInt64(22 + i);
}
if (!reader.IsDBNull(23 + i))
{
bookkeeping.AddedMonth5 = reader.GetInt64(23 + i);
}
if (!reader.IsDBNull(24 + i))
{
bookkeeping.RedeemedMonth5 = reader.GetInt64(24 + i);
}
if (!reader.IsDBNull(25 + i))
{
bookkeeping.ClientsMonth5 = reader.GetInt64(25 + i);
}
if (!reader.IsDBNull(26 + i))
{
bookkeeping.GainMonth6 = reader.GetInt64(26 + i);
}
if (!reader.IsDBNull(27 + i))
{
bookkeeping.AddedMonth6 = reader.GetInt64(27 + i);
}
if (!reader.IsDBNull(28 + i))
{
bookkeeping.RedeemedMonth6 = reader.GetInt64(28 + i);
}
if (!reader.IsDBNull(29 + i))
{
bookkeeping.ClientsMonth6 = reader.GetInt64(29 + i);
}
if (!reader.IsDBNull(30 + i))
{
bookkeeping.GainMonth7 = reader.GetInt64(30 + i);
}
if (!reader.IsDBNull(31 + i))
{
bookkeeping.AddedMonth7 = reader.GetInt64(31 + i);
}
if (!reader.IsDBNull(32 + i))
{
bookkeeping.RedeemedMonth7 = reader.GetInt64(32 + i);
}
if (!reader.IsDBNull(33 + i))
{
bookkeeping.ClientsMonth7 = reader.GetInt64(33 + i);
}
if (!reader.IsDBNull(34 + i))
{
bookkeeping.GainMonth8 = reader.GetInt64(34 + i);
}
if (!reader.IsDBNull(35 + i))
{
bookkeeping.AddedMonth8 = reader.GetInt64(35 + i);
}
if (!reader.IsDBNull(36 + i))
{
bookkeeping.RedeemedMonth8 = reader.GetInt64(36 + i);
}
if (!reader.IsDBNull(37 + i))
{
bookkeeping.ClientsMonth8 = reader.GetInt64(37 + i);
}
if (!reader.IsDBNull(38 + i))
{
bookkeeping.GainMonth9 = reader.GetInt64(38 + i);
}
if (!reader.IsDBNull(39 + i))
{
bookkeeping.AddedMonth9 = reader.GetInt64(39 + i);
}
if (!reader.IsDBNull(40 + i))
{
bookkeeping.RedeemedMonth9 = reader.GetInt64(40 + i);
}
if (!reader.IsDBNull(41 + i))
{
bookkeeping.ClientsMonth9 = reader.GetInt64(41 + i);
}
if (!reader.IsDBNull(42 + i))
{
bookkeeping.GainMonth10 = reader.GetInt64(42 + i);
}
if (!reader.IsDBNull(43 + i))
{
bookkeeping.AddedMonth10 = reader.GetInt64(43 + i);
}
if (!reader.IsDBNull(44 + i))
{
bookkeeping.RedeemedMonth10 = reader.GetInt64(44 + i);
}
if (!reader.IsDBNull(45 + i))
{
bookkeeping.ClientsMonth10 = reader.GetInt64(45 + i);
}
if (!reader.IsDBNull(46 + i))
{
bookkeeping.GainMonth11 = reader.GetInt64(46 + i);
}
if (!reader.IsDBNull(47 + i))
{
bookkeeping.AddedMonth11 = reader.GetInt64(47 + i);
}
if (!reader.IsDBNull(48 + i))
{
bookkeeping.RedeemedMonth11 = reader.GetInt64(48 + i);
}
if (!reader.IsDBNull(49 + i))
{
bookkeeping.ClientsMonth11 = reader.GetInt64(49 + i);
}
if (!reader.IsDBNull(50 + i))
{
bookkeeping.GainMonth12 = reader.GetInt64(50 + i);
}
if (!reader.IsDBNull(51 + i))
{
bookkeeping.AddedMonth12 = reader.GetInt64(51 + i);
}
if (!reader.IsDBNull(52 + i))
{
bookkeeping.RedeemedMonth12 = reader.GetInt64(52 + i);
}
if (!reader.IsDBNull(53 + i))
{
bookkeeping.ClientsMonth12 = reader.GetInt64(53 + i);
}
}
catch (Exception ex)
{
Log.Error(ex, "BookkeepingService read bookkeeping operator {0}, partner {1}, pos {2}", request.Operator, request.Partner, request.Pos);
}
bookkeepings.Add(bookkeeping);
}
reader.Close();
response.ErrorCode = Convert.ToInt32(cmd.Parameters["@result"].Value);
response.Message = Convert.ToString(cmd.Parameters["@errormessage"].Value);
response.RecordTotal = Convert.ToInt32(cmd.Parameters["@total_rows"].Value);
response.RecordFilterd = response.RecordTotal;
response.Bookkeepings = bookkeepings;
}
catch (Exception e)
{
response.ErrorCode = 10;
response.Message = e.Message;
}
finally
{
_cnn.Close();
}
return(response);
}
public string GetBookkeeping(JQueryDataTableParamModel param)
{
BookkeepingsResponse response = new BookkeepingsResponse();
BookkeepingRequest request = new BookkeepingRequest();
try { request.Operator = JwtProps.GetOperator(); } catch { }
try { request.Partner = JwtProps.GetPartner(); } catch { }
try { request.Pos = JwtProps.GetPos(); } catch { }
try
{
if (!string.IsNullOrEmpty(Request["columns[1][search][value]"]))
{
request.Name = Request["columns[1][search][value]"];
}
request.DateStart = Request["date_from"];
request.DateEnd = Request["date_to"];
request.Page = Convert.ToInt64(param.start);
request.PageSize = Convert.ToInt64(param.length);
var i = 0;
if (request.IsOperator)
{
i = 1;
if (!string.IsNullOrEmpty(Request["columns[2][search][value]"]))
{
request.PosName = Request["columns[2][search][value]"];
}
}
if (!string.IsNullOrEmpty(Request["columns[" + (2 + i) + "][search][value]"]))
{
var values = Request["columns[" + (2 + i) + "][search][value]"].Split('-');
try { request.PurchasesMore = values[0]; } catch { };
try { request.PurchasesLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(Request["columns[" + (3 + i) + "][search][value]"]))
{
var values = Request["columns[" + (3 + i) + "][search][value]"].Split('-');
try { request.AddedMore = values[0]; } catch { };
try { request.AddedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(Request["columns[" + (4 + i) + "][search][value]"]))
{
var values = Request["columns[" + (4 + i) + "][search][value]"].Split('-');
try { request.RedeemedMore = values[0]; } catch { };
try { request.RedeemedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(Request["columns[" + (5 + i) + "][search][value]"]))
{
var values = Request["columns[" + (5 + i) + "][search][value]"].Split('-');
try { request.ClientsMore = values[0]; } catch { };
try { request.ClientsLess = values[1]; } catch { };
}
try { request.Page++; } catch { }
HttpResponseMessage responseMessage = HttpClientService.PostAsync("api/bookkeeping/GetBookkeepings", request).Result;
if (responseMessage.IsSuccessStatusCode)
{
try { response = responseMessage.Content.ReadAsAsync <BookkeepingsResponse>().Result; } catch { }
if (response.ErrorCode == 0)
{
var longArrayByMonth = new long[12];
var decimalArrayByMonth = new decimal[12];
BookkeepingDataTableModel bookkeepings = new BookkeepingDataTableModel();
foreach (Bookkeeping item in response.Bookkeepings)
{
longArrayByMonth[0] = item.GainMonth1;
longArrayByMonth[1] = item.GainMonth2;
longArrayByMonth[2] = item.GainMonth3;
longArrayByMonth[3] = item.GainMonth4;
longArrayByMonth[4] = item.GainMonth5;
longArrayByMonth[5] = item.GainMonth6;
longArrayByMonth[6] = item.GainMonth7;
longArrayByMonth[7] = item.GainMonth8;
longArrayByMonth[8] = item.GainMonth9;
longArrayByMonth[9] = item.GainMonth10;
longArrayByMonth[10] = item.GainMonth11;
longArrayByMonth[11] = item.GainMonth12;
var buysArrayString = string.Join(",", longArrayByMonth);
longArrayByMonth[0] = item.ClientsMonth1;
longArrayByMonth[1] = item.ClientsMonth2;
longArrayByMonth[2] = item.ClientsMonth3;
longArrayByMonth[3] = item.ClientsMonth4;
longArrayByMonth[4] = item.ClientsMonth5;
longArrayByMonth[5] = item.ClientsMonth6;
longArrayByMonth[6] = item.ClientsMonth7;
longArrayByMonth[7] = item.ClientsMonth8;
longArrayByMonth[8] = item.ClientsMonth9;
longArrayByMonth[9] = item.ClientsMonth10;
longArrayByMonth[10] = item.ClientsMonth11;
longArrayByMonth[11] = item.ClientsMonth12;
var clientsArrayString = string.Join(",", longArrayByMonth);
longArrayByMonth[0] = item.AddedMonth1;
longArrayByMonth[1] = item.AddedMonth2;
longArrayByMonth[2] = item.AddedMonth3;
longArrayByMonth[3] = item.AddedMonth4;
longArrayByMonth[4] = item.AddedMonth5;
longArrayByMonth[5] = item.AddedMonth6;
longArrayByMonth[6] = item.AddedMonth7;
longArrayByMonth[7] = item.AddedMonth8;
longArrayByMonth[8] = item.AddedMonth9;
longArrayByMonth[9] = item.AddedMonth10;
longArrayByMonth[10] = item.AddedMonth11;
longArrayByMonth[11] = item.AddedMonth12;
var addedArrayString = string.Join(",", longArrayByMonth);
longArrayByMonth[0] = item.RedeemedMonth1;
longArrayByMonth[1] = item.RedeemedMonth2;
longArrayByMonth[2] = item.RedeemedMonth3;
longArrayByMonth[3] = item.RedeemedMonth4;
longArrayByMonth[4] = item.RedeemedMonth5;
longArrayByMonth[5] = item.RedeemedMonth6;
longArrayByMonth[6] = item.RedeemedMonth7;
longArrayByMonth[7] = item.RedeemedMonth8;
longArrayByMonth[8] = item.RedeemedMonth9;
longArrayByMonth[9] = item.RedeemedMonth10;
longArrayByMonth[10] = item.RedeemedMonth11;
longArrayByMonth[11] = item.RedeemedMonth12;
var redeemedArrayString = string.Join(",", longArrayByMonth);
BookkeepingViewModel bonus = new BookkeepingViewModel
{
id = item.Id,
caption = item.Caption,
posname = item.PosName,
purchases = item.Gain.ToString(CultureInfo.InvariantCulture),
added = item.Added.ToString(CultureInfo.InvariantCulture),
redeemed = item.Redeemed.ToString(CultureInfo.InvariantCulture),
clients = item.Clients.ToString(CultureInfo.InvariantCulture),
diagrams = @"
<div class='line-chart-bl' style='padding-bottom: 20px;'>
<div class='line-chart__head'>
<div id='line-chart-leg' class='line-chart-legend'></div>
</div>
<div class='line-chart__bottom'><canvas id='canvas' class='line-chart'></canvas>
<div id='chartjs-tooltip-1' class='linejs-tooltip'></div>
</div>
</div>
<script>
var lineChartData = {
labels: ['Январь', 'Февраль', 'Март', 'Апрель', 'Май', 'Июнь', 'Июль', 'Август', 'Сентябрь', 'Октябрь', 'Ноябрь', 'Декабрь'],
datasets: [
{
label: 'Выручка',
borderColor: '#58AEDC',
pointBackgroundColor: '#58AEDC',
pointRadius: 2,
backgroundColor: '#58AEDC',
data: [" + buysArrayString + @"],
fill: !1,
borderWidth: 2,
},
{
label: 'Начислено',
borderColor: '#11B9A3',
pointBackgroundColor: '#11B9A3',
pointRadius: 2,
backgroundColor: '#11B9A3',
data: [" + addedArrayString + @"],
fill: !1,
borderWidth: 2,
}, {
label: 'Списано',
borderColor: '#E5C861',
pointBackgroundColor: '#E5C861',
pointRadius: 2,
backgroundColor: '#E5C861',
data: [" + redeemedArrayString + @"],
fill: !1,
borderWidth: 2,
}, {
label: 'Клиенты',
borderColor: '#567BA5',
pointBackgroundColor: '#567BA5',
pointRadius: 2,
backgroundColor: '#567BA5',
data: [" + clientsArrayString + @"],
fill: !1,
borderWidth: 2,
}
]
};
typeDiagram = 'line';
</script>"
};
bookkeepings.data.Add(bonus);
}
bookkeepings.draw = param.draw;
bookkeepings.recordsTotal = response.RecordTotal;
bookkeepings.recordsFiltered = response.RecordFilterd;
var data = JsonConvert.SerializeObject(bookkeepings);
return(data);
}
return(responseMessage.ReasonPhrase);
}
response.ErrorCode = 10;
response.Message = "Ошибка получения данных";
}
catch (Exception ex)
{
response.ErrorCode = 2;
response.Message = ex.Message;
}
return(JsonConvert.SerializeObject(response));
}
