public JsonResult GetCustomers(string filter)
{
JavaScriptSerializer serializer = new JavaScriptSerializer();
FilterCustomer model = serializer.Deserialize <FilterCustomer>(filter);
int totalCount = 0;
int pageCount = 0;
List <CustomerEntity> list = CustomBusiness.BaseBusiness.GetCustomers(model.SearchType, model.Type, model.SourceID, model.StageID, model.Status, model.Mark, model.ActivityID, model.UserID, model.TeamID, model.AgentID, model.BeginTime, model.EndTime, model.Keywords, model.PageSize, model.PageIndex, ref totalCount, ref pageCount, CurrentUser.UserID, CurrentUser.AgentID, CurrentUser.ClientID);
JsonDictionary.Add("items", list);
JsonDictionary.Add("totalCount", totalCount);
JsonDictionary.Add("pageCount", pageCount);
return(new JsonResult
{
Data = JsonDictionary,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public JsonResult InvoiceListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OINV
join t2 in db.OCRD on t1.CardCode equals t2.CardCode
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.DocStatus))
where t2.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.DocNum.ToString().Contains(model.Query))
)
select new
{
DocNum = t1.DocNum,
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
DocTotal = t1.DocTotal,
CreateDate = t1.CreateDate,
PaidToDate = t1.PaidToDate,
CancelDate = t1.CancelDate,
DocDueDate = t1.DocDueDate,
VatSum = t1.VatSum,
BeforeVat = t1.DocTotal - t1.VatSum,
DocStatus = t1.DocStatus ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 4: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocTotal) : query.OrderByDescending(r => r.DocTotal)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocDueDate) : query.OrderByDescending(r => r.DocDueDate)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
public ActionResult CustomerExport(FilterCustomer model)
{
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OCRD
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(0, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.frozenFor))
where t1.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.Cellular.Contains(model.Query)) ||
(t1.E_Mail.Contains(model.Query))
)
select new
{
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
CardNo = t1.LicTradNum ?? "",
Cellular = t1.Cellular ?? "",
CreateDate = t1.CreateDate,
EMail = t1.E_Mail ?? "",
Address = t1.Address ?? "",
District = t1.Block ?? "",
Amphur = t1.County ?? "",
Province = t1.City ?? "",
ZipCode = t1.ZipCode ?? "",
Status = t1.frozenFor ?? ""
});
var entity = query.ToList();
if (entity.Count > 0)
{
System.IO.MemoryStream streamExcel = new System.IO.MemoryStream();
using (var excel = new ExcelPackage(streamExcel))
{
System.IO.MemoryStream memStream = new System.IO.MemoryStream();
var sheets1 = excel.Workbook.Worksheets.Add("Sheet1");
sheets1.Cells[1, 1, 1, 1].Value = "รายชื่อลูกค้า";
string[] fieldHeader1 = { "รหัสลูกค้า", "ชื่อลูกค้า", "เบอร์โทรศัพท์", "Email", "ที่อยู่", "วันที่สร้างลูกค้า", "บัตรประชาชน/พาสปอร์ต/เลขที่นิติบุคคล", "สถานะ" };
for (int i = 0; i < fieldHeader1.Length; i++)
{
sheets1.Cells[3, i + 1].Value = fieldHeader1[i];
}
int startRows = 3;
foreach (var item in entity)
{
sheets1.Cells[startRows + 1, 1].Value = item.CardCode;
sheets1.Cells[startRows + 1, 2].Value = item.CardName;
sheets1.Cells[startRows + 1, 3].Value = item.Cellular;
sheets1.Cells[startRows + 1, 4].Value = item.EMail;
sheets1.Cells[startRows + 1, 5].Value = string.Format("{0} {1} {2} {3} {4}", item.Address ?? "", item.District ?? "", item.Amphur ?? "", item.Province ?? "", item.ZipCode ?? "");
sheets1.Cells[startRows + 1, 6].Value = item.CreateDate != null?item.CreateDate.Value.ToString("dd/MM/yyyy") : "";
sheets1.Cells[startRows + 1, 7].Value = item.CardNo;
sheets1.Cells[startRows + 1, 8].Value = item.Status == "Y" ? "Active" : "Inactive";
startRows++;
}
for (int i = 2; i <= fieldHeader1.Length; i++)
{
sheets1.Column(i).AutoFit();
}
excel.Save();
}
string attachment = "attachment; filename=CustomerExport-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
streamExcel.WriteTo(Response.OutputStream);
Response.End();
}
}
}
catch (Exception ex)
{
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(new EmptyResult());
}
public JsonResult CustomerListLoad(DatableOption option, FilterCustomer model)
{
var jsonReturn = new JsonDataTable();
try
{
using (var db = new FTTxEntities())
{
var endDate = DateTime.Now.Date;
if (model.EndDate != null)
{
endDate = model.EndDate.Value.AddDays(1);
}
var query = (from t1 in db.OCRD
where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2)))
where (model.StartDate == null || t1.CreateDate >= model.StartDate)
where (model.EndDate == null || t1.CreateDate < endDate)
where (model.Status.Count == 0 || model.Status.Contains(t1.frozenFor))
where t1.GroupCode == 103
where (string.IsNullOrEmpty(model.Query) ||
(t1.CardCode.Contains(model.Query)) ||
(t1.CardName.Contains(model.Query)) ||
(t1.Cellular.Contains(model.Query)) ||
(t1.E_Mail.Contains(model.Query))
)
select new
{
CardCode = t1.CardCode ?? "",
CardName = t1.CardName ?? "",
CardNo = t1.LicTradNum ?? "",
Cellular = t1.Cellular ?? "",
CreateDate = t1.CreateDate,
EMail = t1.E_Mail ?? "",
Address = t1.Address ?? "",
District = t1.Block ?? "",
Amphur = t1.County ?? "",
Province = t1.City ?? "",
ZipCode = t1.ZipCode ?? "",
Status = t1.frozenFor ?? ""
});
if (option.order.Count > 0)
{
var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value);
var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value;
switch (sorting)
{
case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break;
case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Cellular) : query.OrderByDescending(r => r.Cellular)); break;
case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.EMail) : query.OrderByDescending(r => r.EMail)); break;
case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break;
case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Status) : query.OrderByDescending(r => r.Status)); break;
default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break;
}
}
var data = query.Skip(option.start).Take(option.length).ToList();
var count = query.Count();
jsonReturn = new JsonDataTable {
status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count
};
}
}
catch (Exception ex)
{
jsonReturn = new JsonDataTable {
status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0
};
Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message);
}
return(Json(jsonReturn));
}
