public async Task <JsonResult> FindAbonents(string type, string abonent, int status, int?tower, int?receiver, string abonent_num,
MessageFilterBy finish_date, MessageFilterBy pause_date, MessageFilterBy credit_date, MessageFilterBy balance, MessageFilterBy discount, MessageFilterBy service, MessageFilterBy status2,
int abonent_type)
{
using (DataContext _db = new DataContext())
{
string where = type == "" ? "" : " AND " + type + "=N'" + abonent + "'";
where = where.Replace("+", "+' '+");
where += status == -1 ? "" : (status == 6 ? " AND cr.mode=1 AND cr.status=0" : " AND cr.status=" + status);
where += !tower.HasValue ? "" : " AND cr.tower_id=" + tower.Value;
where += !receiver.HasValue ? "" : " AND cr.receiver_id=" + receiver.Value;
where += abonent_type == -1 ? "" : " AND c.type=" + abonent_type;
where += abonent_num == "" ? "" : " AND cr.abonent_num+cr.card_num LIKE '%" + abonent_num + "%'";
string[] charge = _db.Params.Where(p => p.Name == "CardCharge").Select(c => c.Value).First().Split(':');
string today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, int.Parse(charge[0]), int.Parse(charge[1]), 0).ToString("yyyy-MM-dd HH:mm") + ":0.000";
if (!string.IsNullOrEmpty(finish_date.val))
{
where += " AND DATEDIFF(day,'" + today + "',cr.finish_date)" + finish_date.where + finish_date.val;
}
if (!string.IsNullOrEmpty(pause_date.val))
{
string p_date = "CAST(CAST(DATEPART(YEAR,cr.pause_date) as VARCHAR(4))+'-'+CAST(DATEPART(MONTH,cr.pause_date) as VARCHAR(2))+'-'+CAST(DATEPART(DAY,cr.pause_date) as VARCHAR(2))+' " + charge[0] + ":" + charge[1] + ":00.000' as DATETIME)";
where += " AND DATEDIFF(day,'" + today + "'," + p_date + ")" + pause_date.where + pause_date.val;
}
if (!string.IsNullOrEmpty(balance.val))
{
where += " AND ((SELECT ISNULL(SUM(amount),0) FROM doc.Payments WHERE card_id=cr.id) - (SELECT ISNULL(SUM(amount),0) FROM doc.CardCharges WHERE card_id=cr.id)) " + balance.where + balance.val;
}
if (!string.IsNullOrEmpty(credit_date.val))
{
where += " AND cr.mode=1 AND DATEDIFF(day,'" + today + "',cr.finish_date)" + credit_date.where + credit_date.val;
}
if (!string.IsNullOrEmpty(discount.val))
{
where += " AND cr.discount" + discount.where + discount.val;
}
if (!string.IsNullOrEmpty(service.val))
{
string s_date = "(SELECT TOP(1) CAST(CAST(DATEPART(YEAR,tdate) as VARCHAR(4))+'-'+CAST(DATEPART(MONTH,tdate) as VARCHAR(2))+'-'+CAST(DATEPART(DAY, tdate) as VARCHAR(2))+' " + charge[0] + ":" + charge[1] + ":00.000' as DATETIME) FROM doc.CardServices WHERE card_id=cr.id AND is_active=1)";
where += " AND (CASE WHEN DATEDIFF(day, cr.finish_date, '" + today + "') < DATEDIFF(day, " + s_date + ", '" + today + "') THEN DATEDIFF(day, cr.finish_date, '" + today + "') ELSE DATEDIFF(day, " + s_date + ", '" + today + "') END)" + service.where + service.val;
}
if (!string.IsNullOrEmpty(status2.val))
{
string res = "1=1";
switch (status)
{
case 0:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=0 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 1:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=1 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 2:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=2 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 3:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=3 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 5:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=6 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
}
where += " AND " + res;
}
if (where == "")
{
return(Json(new List <IdName>()));
}
string sql = @"SELECT cr.id AS Id, c.name+' '+c.lastname +' - ' + c.code + ' - ' + cr.abonent_num AS Name FROM book.Cards AS cr
INNER JOIN book.Customers AS c ON c.id=cr.customer_id WHERE cr.status!=4 " + where;
return(Json(await _db.Database.SqlQuery <IdName>(sql).ToListAsync()));
}
}
public JsonResult SaveAutoTemplate(string type, string abonent, int status, int?tower, int?receiver, string abonent_num,
MessageFilterBy finish_date, MessageFilterBy pause_date, MessageFilterBy credit_date, MessageFilterBy balance, MessageFilterBy discount,
MessageFilterBy service, MessageFilterBy status2, int abonent_type, string text, string types, bool is_disposable, string template_name)
{
string where = type == "" ? "" : " AND " + type + "=N'" + abonent + "'";
where = where.Replace("+", "+' '+");
where += status == -1 ? "" : " AND cr.status=" + status;
where += !tower.HasValue ? "" : " AND cr.tower_id=" + tower.Value;
where += abonent_type == -1 ? "" : " AND c.type=" + abonent_type;
where += !receiver.HasValue ? "" : " AND cr.receiver_id=" + receiver.Value;
where += abonent_num == "" ? "" : " AND cr.abonent_num+cr.card_num LIKE '%" + abonent_num + "%'";
using (DataContext _db = new DataContext())
{
string[] charge = _db.Params.Where(p => p.Name == "CardCharge").Select(c => c.Value).First().Split(':');
string today = "Convert(varchar(10), getdate(), 126)+' " + charge[0].PadLeft(2, '0') + ":" + charge[1].PadLeft(2, '0') + ":0.000'";
if (!string.IsNullOrEmpty(finish_date.val))
{
where += " AND DATEDIFF(day," + today + ",cr.finish_date)" + finish_date.where + finish_date.val;
}
if (!string.IsNullOrEmpty(pause_date.val))
{
string p_date = "CAST(CAST(DATEPART(YEAR,cr.pause_date) as VARCHAR(4))+'-'+CAST(DATEPART(MONTH,cr.pause_date) as VARCHAR(2))+'-'+CAST(DATEPART(DAY,cr.pause_date) as VARCHAR(2))+' " + charge[0] + ":" + charge[1] + ":00.000' as DATETIME)";
where += " AND DATEDIFF(day," + today + "," + p_date + ")" + pause_date.where + pause_date.val;
}
if (!string.IsNullOrEmpty(balance.val))
{
where += " AND ((SELECT ISNULL(SUM(amount),0) FROM doc.Payments WHERE card_id=cr.id) - (SELECT ISNULL(SUM(amount),0) FROM doc.CardCharges WHERE card_id=cr.id)) " + balance.where + balance.val;
}
if (!string.IsNullOrEmpty(credit_date.val))
{
where += " AND cr.mode=1 AND DATEDIFF(day," + today + ",cr.finish_date)" + credit_date.where + credit_date.val;
}
if (!string.IsNullOrEmpty(discount.val))
{
where += " AND cr.discount" + discount.where + discount.val;
}
if (!string.IsNullOrEmpty(service.val))
{
string s_date = "(SELECT TOP(1) CAST(CAST(DATEPART(YEAR,tdate) as VARCHAR(4))+'-'+CAST(DATEPART(MONTH,tdate) as VARCHAR(2))+'-'+CAST(DATEPART(DAY, tdate) as VARCHAR(2))+' " + charge[0] + ":" + charge[1] + ":00.000' as DATETIME) FROM doc.CardServices WHERE card_id=cr.id AND is_active=1)";
where += " AND (CASE WHEN DATEDIFF(day, cr.finish_date, " + today + ") < DATEDIFF(day, " + s_date + "," + today + ") THEN DATEDIFF(day, cr.finish_date, " + today + ") ELSE DATEDIFF(day, " + s_date + ", " + today + ") END)" + service.where + service.val;
}
if (!string.IsNullOrEmpty(status2.val))
{
string res = "1=1";
switch (status)
{
case 0:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=0 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 1:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=1 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 2:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=2 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 3:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=3 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
case 5:
res = "DATEDIFF(day,(SELECT TOP(1) close_tdate FROM doc.CardLogs WHERE status=6 AND card_id=cr.id ORDER BY id DESC),'" + today + "')" + status2.where + status2.val;
break;
}
where += " AND " + res;
}
if (where == "")
{
return(Json(0));
}
try
{
_db.AutoMessageTemplates.AddRange(types.Split(',').Select(c => new AutoMessageTemplate {
MessageText = text, MessageType = (MessageType)int.Parse(c), Query = where, IsDisposable = is_disposable, Name = template_name
}));
_db.SaveChanges();
}
catch
{
return(Json(0));
}
}
return(Json(1));
}
