public async Task <ActionResult <IEnumerable <CustomerOut> > > GetCustomers()
{
var customersDto = await _context.Customers
.AsNoTracking().
ToListAsync();
List <CustomerOut> customers = new List <CustomerOut>();
foreach (var customer in customersDto)
{
var customerOut = new CustomerOut(_context, customer);
customers.Add(customerOut);
}
return(customers);
}
public static List <CustomerOut> Get(string text, int Discount, string by, int Provice)
{
string textsearch = '"' + text + '"';
var list = new List <CustomerOut>();
var sql = @"select c.ID, c.CustomerName, c.Nick, c.CustomerPhone, c.Zalo, c.Facebook, c.CreatedBy, c.CreatedDate, dg.DiscountName, dg.ID as DiscountID, c.ProvinceID as Province
from tbl_DiscountCustomer dc, tbl_DiscountGroup dg, tbl_Customer c
where dc.DiscountGroupID = dg.ID and c.ID = dc.UID";
if (!string.IsNullOrEmpty(textsearch))
{
sql += " And (CONTAINS(c.CustomerName,'" + textsearch + "') OR CONTAINS(c.Nick,'" + textsearch + "') OR c.CustomerPhone like '%" + text + "%' OR c.Facebook like '%" + text + "%' OR c.Zalo like '%" + text + "%')";
}
if (Discount > 0)
{
sql += " AND dg.ID = " + Discount;
}
if (Provice > 0)
{
sql += " AND c.ProvinceID = " + Provice;
}
if (!string.IsNullOrEmpty(by))
{
sql += " And c.CreatedBy = N'" + by + "'";
}
var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var entity = new CustomerOut();
if (reader["ID"] != DBNull.Value)
{
entity.ID = reader["ID"].ToString().ToInt(0);
}
if (reader["CustomerName"] != DBNull.Value)
{
entity.CustomerName = reader["CustomerName"].ToString();
}
if (reader["Nick"] != DBNull.Value)
{
entity.Nick = reader["Nick"].ToString();
}
if (reader["CustomerPhone"] != DBNull.Value)
{
entity.CustomerPhone = reader["CustomerPhone"].ToString();
}
if (reader["Zalo"] != DBNull.Value)
{
entity.Zalo = reader["Zalo"].ToString();
}
if (reader["Facebook"] != DBNull.Value)
{
entity.Facebook = reader["Facebook"].ToString();
}
if (reader["CreatedDate"] != DBNull.Value)
{
entity.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
}
if (reader["CreatedBy"] != DBNull.Value)
{
entity.CreatedBy = reader["CreatedBy"].ToString();
}
if (reader["DiscountName"] != DBNull.Value)
{
entity.DiscountName = reader["DiscountName"].ToString();
}
if (reader["Province"] != DBNull.Value)
{
entity.Province = reader["Province"].ToString();
}
list.Add(entity);
}
reader.Close();
return(list);
}
public static List <CustomerOut> Filter(string text, string by, int Provice, string CreatedDate)
{
string textsearch = '"' + text + '"';
var list = new List <CustomerOut>();
var sql = @"select c.ID, c.CustomerName, c.Nick, c.CustomerPhone, c.CustomerPhone2, c.Zalo, c.Facebook, c.CreatedBy, c.CreatedDate, c.Avatar, c.ShippingType, c.PaymentType, c.TransportCompanyID, c.TransportCompanySubID, c.ProvinceID as Province
from tbl_Customer c
WHERE 1 = 1";
if (!string.IsNullOrEmpty(textsearch))
{
sql += " AND (CONTAINS(c.CustomerName,'" + textsearch + "') OR CONTAINS(c.Nick,'" + textsearch + "') OR c.CustomerPhone like '%" + text + "%' OR c.CustomerPhone2 like '%" + text + "%' OR c.CustomerPhoneBackup like '%" + text + "%' OR c.Facebook like '%" + text + "%' OR c.Zalo like '%" + text + "%')";
}
if (Provice > 0)
{
sql += " AND c.ProvinceID = " + Provice;
}
if (!string.IsNullOrEmpty(by))
{
sql += " AND c.CreatedBy = N'" + by + "'";
}
if (CreatedDate != "")
{
DateTime fromdate = DateTime.Today;
DateTime todate = DateTime.Now;
switch (CreatedDate)
{
case "today":
fromdate = DateTime.Today;
todate = DateTime.Now;
break;
case "yesterday":
fromdate = fromdate.AddDays(-1);
todate = DateTime.Today;
break;
case "beforeyesterday":
fromdate = DateTime.Today.AddDays(-2);
todate = DateTime.Today.AddDays(-1);
break;
case "week":
int days = DateTime.Today.DayOfWeek == DayOfWeek.Sunday ? 7 : (int)DateTime.Today.DayOfWeek;
fromdate = fromdate.AddDays(-days + 1);
todate = DateTime.Now;
break;
case "month":
fromdate = new DateTime(fromdate.Year, fromdate.Month, 1);
todate = DateTime.Now;
break;
case "7days":
fromdate = DateTime.Today.AddDays(-6);
todate = DateTime.Now;
break;
case "30days":
fromdate = DateTime.Today.AddDays(-29);
todate = DateTime.Now;
break;
}
sql += " AND CONVERT(datetime, c.CreatedDate, 121) BETWEEN CONVERT(datetime, '"+ fromdate.ToString() + "', 121) AND CONVERT(datetime, '" + todate.ToString() + "', 121)";
}
var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var entity = new CustomerOut();
if (reader["ID"] != DBNull.Value)
{
entity.ID = reader["ID"].ToString().ToInt(0);
}
if (reader["CustomerName"] != DBNull.Value)
{
entity.CustomerName = reader["CustomerName"].ToString();
}
if (reader["CustomerPhone"] != DBNull.Value)
{
entity.CustomerPhone = reader["CustomerPhone"].ToString();
}
if (reader["CustomerPhone2"] != DBNull.Value)
{
entity.CustomerPhone2 = reader["CustomerPhone2"].ToString();
}
if (reader["Zalo"] != DBNull.Value)
{
entity.Zalo = reader["Zalo"].ToString();
}
if (reader["Facebook"] != DBNull.Value)
{
entity.Facebook = reader["Facebook"].ToString();
}
if (reader["CreatedDate"] != DBNull.Value)
{
entity.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
}
if (reader["CreatedBy"] != DBNull.Value)
{
entity.CreatedBy = reader["CreatedBy"].ToString();
}
if (reader["Nick"] != DBNull.Value)
{
entity.Nick = reader["Nick"].ToString();
}
if (reader["Province"] != DBNull.Value)
{
entity.Province = reader["Province"].ToString();
}
if (!string.IsNullOrEmpty(reader["Avatar"].ToString()))
{
entity.Avatar = reader["Avatar"].ToString();
}
else
{
entity.Avatar = "/uploads/avatars/no-avatar.png";
}
if (reader["ShippingType"] != DBNull.Value)
{
entity.ShippingType = reader["ShippingType"].ToString().ToInt(0);
}
if (reader["PaymentType"] != DBNull.Value)
{
entity.PaymentType = reader["PaymentType"].ToString().ToInt(0);
}
if (reader["TransportCompanyID"] != DBNull.Value)
{
entity.TransportCompanyID = reader["TransportCompanyID"].ToString().ToInt(0);
}
if (reader["TransportCompanySubID"] != DBNull.Value)
{
entity.TransportCompanySubID = reader["TransportCompanySubID"].ToString().ToInt(0);
}
list.Add(entity);
}
reader.Close();
return(list);
}
public static List <CustomerOut> Find(string text, string createdby = "")
{
string textsearch = '"' + text + '"';
var list = new List <CustomerOut>();
var sql = @"select c.ID, c.CustomerName, c.Nick, c.CustomerPhone, c.CustomerPhone2, c.Zalo, c.Facebook, c.CustomerAddress, c.CreatedBy, c.ProvinceID as Province
from tbl_Customer c
WHERE (CONTAINS(c.CustomerName,'" + textsearch + "') OR CONTAINS(c.Nick,'" + textsearch + "') OR c.CustomerPhone like '%" + text + "%' OR c.CustomerPhone2 like '%" + text + "%' OR c.CustomerPhoneBackup like '%" + text + "%' OR c.Facebook like '%" + text + "%' OR c.Zalo like '%" + text + "%')";
if (createdby != "")
{
sql += " And c.CreatedBy = N'" + createdby + "'";
}
var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql);
while (reader.Read())
{
var entity = new CustomerOut();
if (reader["ID"] != DBNull.Value)
{
entity.ID = reader["ID"].ToString().ToInt(0);
}
if (reader["CustomerName"] != DBNull.Value)
{
entity.CustomerName = reader["CustomerName"].ToString();
}
if (reader["CustomerPhone"] != DBNull.Value)
{
entity.CustomerPhone = reader["CustomerPhone"].ToString();
}
if (reader["CustomerPhone2"] != DBNull.Value)
{
entity.CustomerPhone2 = reader["CustomerPhone2"].ToString();
}
if (reader["CreatedBy"] != DBNull.Value)
{
entity.CreatedBy = reader["CreatedBy"].ToString();
}
if (reader["CustomerAddress"] != DBNull.Value)
{
entity.CustomerAddress = reader["CustomerAddress"].ToString();
}
if (reader["Zalo"] != DBNull.Value)
{
entity.Zalo = reader["Zalo"].ToString();
}
if (reader["Facebook"] != DBNull.Value)
{
entity.Facebook = reader["Facebook"].ToString();
}
if (reader["Nick"] != DBNull.Value)
{
entity.Nick = reader["Nick"].ToString();
}
if (reader["Province"] != DBNull.Value)
{
var provinceID = reader["Province"].ToString().ToInt();
var pro = ProvinceController.GetByID(provinceID);
if (pro != null)
{
entity.Province = pro.ProvinceName;
}
else
{
entity.Province = "";
}
}
list.Add(entity);
}
reader.Close();
return(list);
}
