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)); }