public LCManager.Infrastructure.Response.ReportResponse BonusesNoChequeReport(BonusesNotForPurchasesRequest request)
{
return(_bonusService.BonusesNoChequeReport(request));
}
public string GetBonuses(JQueryDataTableParamModel param)
{
BonusesNotForPurchasesResponse response = new BonusesNotForPurchasesResponse();
try
{
BonusesNotForPurchasesRequest request = new BonusesNotForPurchasesRequest
{
Operator = Implementation.JwtProps.GetOperator(),
Date = Request["columns[1][search][value]"],
DateStart = Request["date_from"],
DateEnd = Request["date_to"],
Phone = Request["columns[3][search][value]"],
Page = Convert.ToInt64(param.start),
PageSize = Convert.ToInt64(param.length)
};
if (!string.IsNullOrEmpty(Request["columns[2][search][value]"]))
{
request.Name = Request["columns[2][search][value]"];
}
if (!string.IsNullOrEmpty(Request["columns[5][search][value]"]))
{
var values = Request["columns[5][search][value]"].Split('-');
try { request.AddedMore = values[0]; } catch { };
try { request.AddedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(Request["columns[6][search][value]"]))
{
var values = Request["columns[6][search][value]"].Split('-');
try { request.RedeemedMore = values[0]; } catch { };
try { request.RedeemedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(Request["columns[7][search][value]"]))
{
var values = Request["columns[7][search][value]"].Split('-');
try { request.BurnMore = values[0]; } catch { };
try { request.BurnLess = values[1]; } catch { };
}
try { request.Page++; } catch { }
HttpResponseMessage responseMessage = HttpClientService.PostAsync("api/client/BonusesNotForPurchases", request).Result;
if (responseMessage.IsSuccessStatusCode)
{
try { response = responseMessage.Content.ReadAsAsync <BonusesNotForPurchasesResponse>().Result; } catch { }
if (response.ErrorCode == 0)
{
Bonuses bonuses = new Bonuses();
foreach (Bonus c in response.Bonuses)
{
BonusesViewModel bonus = new BonusesViewModel
{
date = "<p>" + c.BonusDate.ToString("dd.MM.yyyy") + "</p> <p>" +
c.BonusDate.ToString("HH:mm") + "</p>",
type = c.BonusSource,
added = c.BonusAdded.ToString(CultureInfo.InvariantCulture),
redeemed = c.BonusRedeemed.ToString(CultureInfo.InvariantCulture),
fireed = c.BonusBurn.ToString(CultureInfo.InvariantCulture),
card = c.BonusCard.ToString(CultureInfo.CurrentCulture),
phone = c.Phone,
reason = "Механика ПЛ",
lorem = ""
};
bonuses.data.Add(bonus);
}
bonuses.draw = param.draw;
bonuses.recordsTotal = response.RecordTotal;
bonuses.recordsFiltered = response.RecordFilterd;
var data = JsonConvert.SerializeObject(bonuses);
return(data);
}
return(responseMessage.ReasonPhrase);
}
response.ErrorCode = 10;
response.Message = "Ошибка получения данных";
}
catch (Exception ex)
{
response.ErrorCode = 2;
response.Message = ex.Message;
}
return(JsonConvert.SerializeObject(response));
}
public FileResult BonusNoChequeReport(
string bonus_from,
string bonus_to,
string bonus_date,
string bonus_type,
string bonus_added,
string bonus_redeemed,
string bonus_burned,
string bonus_phone)
{
try
{
BonusesNotForPurchasesRequest request = new BonusesNotForPurchasesRequest
{
Operator = Implementation.JwtProps.GetOperator(),
Date = bonus_date,
DateStart = bonus_from,
DateEnd = bonus_to,
Phone = bonus_phone,
Page = -1,
PageSize = -1
};
if (!string.IsNullOrEmpty(bonus_type))
{
request.Name = bonus_type.Substring(1, bonus_type.Length - 2);
}
if (!string.IsNullOrEmpty(bonus_added))
{
var values = bonus_added.Split('-');
try { request.AddedMore = values[0]; } catch { };
try { request.AddedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(bonus_redeemed))
{
var values = bonus_redeemed.Split('-');
try { request.RedeemedMore = values[0]; } catch { };
try { request.RedeemedLess = values[1]; } catch { };
}
if (!string.IsNullOrEmpty(bonus_burned))
{
var values = bonus_burned.Split('-');
try { request.BurnMore = values[0]; } catch { };
try { request.BurnLess = values[1]; } catch { };
}
ReportResponse response = new ReportResponse();
HttpResponseMessage responseMessage = HttpClientService.PostAsync("api/reports/BonusesNoChequeReport", request).Result;
if (responseMessage.IsSuccessStatusCode)
{
response = responseMessage.Content.ReadAsAsync <ReportResponse>().Result;
if (response.ErrorCode != 0 || !string.IsNullOrEmpty(response.Message))
{
return(null);
}
var reportName = "Отчёт по бонусам не за покупки";
if (!string.IsNullOrEmpty(bonus_from))
{
reportName = reportName + " с " + bonus_from;
}
if (!string.IsNullOrEmpty(bonus_to))
{
reportName = reportName + " по " + bonus_to;
}
reportName = reportName + ".xlsx";
return(File(response.Report, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", reportName));
}
return(null);
}
catch { return(null); }
}
public BonusesNotForPurchasesResponse BonusesNotForPurchases(BonusesNotForPurchasesRequest request)
{
return(_bonusService.GetBonusesNotForPurchases(request));
}
/// <summary>
/// Отчет по бонусам не за покупки
/// </summary>
/// <param name="cnn"></param>
/// <param name="request"></param>
/// <returns></returns>
public LCManager.Infrastructure.Response.ReportResponse BonusesNoChequeReport(BonusesNotForPurchasesRequest request)
{
var response = new LCManager.Infrastructure.Response.ReportResponse();
try
{
var data = this.GetBonusesNotForPurchases(request);
using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
string workSheetName = string.Format("с {0} по {1}", request.DateStart, request.DateEnd);
var worksheet = workbook.Worksheets.Add(workSheetName);
worksheet.View.FreezePanes(2, 1);
worksheet.Cells["A1"].Value = "Дата";
worksheet.Cells["A1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["B1"].Value = "Тип бонуса";
worksheet.Cells["B1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["C1"].Value = "Телефон";
worksheet.Cells["C1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["D1"].Value = "Основание";
worksheet.Cells["D1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["E1"].Value = "Начислено";
worksheet.Cells["E1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["F1"].Value = "Списано";
worksheet.Cells["F1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["G1"].Value = "Сгорело";
worksheet.Cells["G1"].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["A1:G1"].AutoFilter = true;
worksheet.Cells["A1:G1"].Style.WrapText = true;
worksheet.Cells["A1:G1"].Style.VerticalAlignment =
OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells["A1:G1"].Style.HorizontalAlignment =
OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
var color = System.Drawing.ColorTranslator.FromHtml("#0070C0");
worksheet.Cells["A1:G1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A1:G1"].Style.Fill.BackgroundColor.SetColor(color);
worksheet.Cells["A1:G1"].Style.Font.Color.SetColor(ColorTranslator.FromHtml("#ffffff"));
worksheet.Cells["A1:G1"].Style.Font.Size = 11;
for (var i = 1; i < 8; i++)
{
worksheet.Column(i).Width = 20;
}
var row = 1;
var cellNum = string.Empty;
foreach (var bonuse in data.Bonuses)
{
row++;
cellNum = row.ToString();
worksheet.Cells["A" + cellNum].Value = bonuse.BonusDate != null
? Convert.ToDateTime(bonuse.BonusDate).ToString("dd.MM.yyyy")
: string.Empty;
worksheet.Cells["A" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["B" + cellNum].Value = bonuse.BonusSource;
worksheet.Cells["B" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["C" + cellNum].Value = bonuse.Phone;
worksheet.Cells["C" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["C" + cellNum].Style.Numberformat.Format = "0";
worksheet.Cells["D" + cellNum].Value = "Механика ПЛ";
worksheet.Cells["D" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["E" + cellNum].Value = bonuse.BonusAdded;
worksheet.Cells["E" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["E" + cellNum].Style.Numberformat.Format = "0";
worksheet.Cells["F" + cellNum].Value = bonuse.BonusRedeemed;
worksheet.Cells["F" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["F" + cellNum].Style.Numberformat.Format = "0";
worksheet.Cells["G" + cellNum].Value = bonuse.BonusBurn;
worksheet.Cells["G" + cellNum].Style.Border.BorderAround(ExcelBorderStyle.Hair);
worksheet.Cells["G" + cellNum].Style.Numberformat.Format = "0";
}
worksheet.Cells["A1:G" + cellNum].Style.Font.Size = 10;
response.Report = package.GetAsByteArray();
}
return(response);
}
catch (Exception e)
{
response.ErrorCode = 10;
response.Message = e.Message;
}
return(response);
}
public BonusesNotForPurchasesResponse GetBonusesNotForPurchases(BonusesNotForPurchasesRequest request)
{
BonusesNotForPurchasesResponse response = new BonusesNotForPurchasesResponse();
try
{
var bonuses = new List <Bonus>();
_cnn.Open();
var cmd = _cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CardBonusesTypePaging";
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.Card > 0)
{
cmd.Parameters.AddWithValue("@card", request.Card);
}
if (!string.IsNullOrEmpty(request.CardStr))
{
cmd.Parameters.AddWithValue("@f_card", request.CardStr);
}
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.Date))
{
if (DateTime.TryParseExact(request.Date, new[] { "dd.MM.yyyy" }, CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out var date))
{
cmd.Parameters.AddWithValue("@f_date", date);
}
}
//Фильтр по дате (Верхний фильтр с диапазоном)
if (!string.IsNullOrEmpty(request.DateStart))
{
if (DateTime.TryParseExact(request.DateStart, new[] { "dd.MM.yyyy" }, CultureInfo.InvariantCulture, System.Globalization.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, System.Globalization.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 (!string.IsNullOrEmpty(request.Phone))
{
cmd.Parameters.AddWithValue("@f_phone", request.Phone);
}
//Фильтр по начислению
try { if (!string.IsNullOrEmpty(request.AddedMore))
{
cmd.Parameters.AddWithValue("@f_added_more", Convert.ToInt32(request.AddedMore));
}
} catch { }
try { if (!string.IsNullOrEmpty(request.AddedLess))
{
cmd.Parameters.AddWithValue("@f_added_less", Convert.ToInt32(request.AddedLess));
}
} catch { }
//Фильтр по списанию
try { if (!string.IsNullOrEmpty(request.RedeemedMore))
{
cmd.Parameters.AddWithValue("@f_redeemed_more", Convert.ToInt32(request.RedeemedMore));
}
} catch { }
try { if (!string.IsNullOrEmpty(request.RedeemedLess))
{
cmd.Parameters.AddWithValue("@f_redeemed_less", Convert.ToInt32(request.RedeemedLess));
}
} catch { }
//Фильтр по сгорело
try { if (!string.IsNullOrEmpty(request.BurnMore))
{
cmd.Parameters.AddWithValue("@f_burn_more", Convert.ToInt32(request.BurnMore));
}
} catch { }
try { if (!string.IsNullOrEmpty(request.BurnLess))
{
cmd.Parameters.AddWithValue("@f_burn_less", Convert.ToInt32(request.BurnLess));
}
} catch { }
SqlDataReader readerBonuses = cmd.ExecuteReader();
while (readerBonuses.Read())
{
Bonus bonus = new Bonus();
try
{
if (!readerBonuses.IsDBNull(0))
{
bonus.BonusSource = readerBonuses.GetString(0);
}
if (!readerBonuses.IsDBNull(1))
{
bonus.BonusDate = readerBonuses.GetDateTime(1);
}
if (!readerBonuses.IsDBNull(2))
{
bonus.BonusAdded = readerBonuses.GetDecimal(2);
}
if (!readerBonuses.IsDBNull(3))
{
bonus.BonusRedeemed = readerBonuses.GetDecimal(3);
}
if (!readerBonuses.IsDBNull(4))
{
bonus.BonusBurn = readerBonuses.GetDecimal(4);
}
if (!readerBonuses.IsDBNull(5))
{
bonus.BonusCard = readerBonuses.GetInt64(5);
}
if (!readerBonuses.IsDBNull(6))
{
bonus.Phone = readerBonuses.GetInt64(6).ToString();
}
}
catch (Exception ex)
{
Log.Error(ex, "ServerOperatorClientsManager getting bonus burn {0}", request.Card);
}
bonuses.Add(bonus);
}
readerBonuses.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.Bonuses = bonuses;
}
catch (Exception e)
{
response.ErrorCode = 10;
response.Message = e.Message;
}
finally
{
_cnn.Close();
}
return(response);
}
