public void SaveTelephone(string telephone, int companyId)
{
string query = string.Format("INSERT INTO Telephone (CompanyId, TelNumber) VALUES ('{0}','{1}')",
companyId, telephone);
BaseCnn.Execute(query);
}
public List <CompanyReport> GetSumList(int companyId, DateTime start, DateTime end, bool fair, List <string> fairDates)
{
string sdate = start.ToString("yyyy-MM-dd");
string edate = end.ToString("yyyy-MM-dd");
string query;
if (fair)
{
query = string.Format("SELECT ma.MagazineName, COUNT(*) AdCount FROM MagazineDate md" +
" JOIN Advertisement ad ON md.MagazineDateId = ad.MagazineDateId" +
" JOIN Magazine ma ON ma.MagazineId = md.MagazineId" +
" WHERE ad.CompanyId = {0}" +
" AND md.Date IN @fairDates" +
" GROUP BY ma.MagazineId" +
" ORDER BY COUNT(*) DESC", companyId, sdate, edate);
var parameters = new DynamicParameters();
parameters.Add("fairDates", fairDates);
return(BaseCnn.Query <CompanyReport>(query, parameters).AsList());
}
else
{
query = string.Format("SELECT ma.MagazineName, COUNT(*) AdCount FROM MagazineDate md" +
" JOIN Advertisement ad ON md.MagazineDateId = ad.MagazineDateId" +
" JOIN Magazine ma ON ma.MagazineId = md.MagazineId" +
" WHERE ad.CompanyId = {0}" +
" AND md.Date BETWEEN '{1}' AND '{2}'" +
" GROUP BY ma.MagazineId" +
" ORDER BY COUNT(*) DESC", companyId, sdate, edate);
return(BaseCnn.Query <CompanyReport>(query).AsList());
}
}
public void SaveAddress(Address Obj, int CompanyId)
{
string query = string.Format("INSERT INTO Address (CompanyId, Town, AddressText) VALUES ('{0}','{1}','{2}')",
CompanyId, Obj.Town, Obj.AddressText);
BaseCnn.Execute(query);
}
public List <CompanyReport> GetList(DateTime start, DateTime end, List <int> statusList, bool fair, List <string> fairDates)
{
string sdate = start.ToString("yyyy-MM-dd");
string edate = end.ToString("yyyy-MM-dd");
string statuses = string.Join(",", statusList);
string query;
if (fair)
{
query = string.Format("SELECT co.CompanyName, co.CompanyId, COUNT(*) AdCount, st.StatusName FROM Advertisement ad" +
" JOIN Company co ON ad.CompanyId = co.CompanyId" +
" JOIN MagazineDate md ON ad.MagazineDateId = md.MagazineDateId" +
" JOIN Status st ON co.Status = st.StatusId " +
" WHERE md.Date IN @fairDates" +
" AND st.StatusId IN ({0})" +
" GROUP BY co.CompanyName" +
" ORDER by AdCount desc; ", statuses);
var parameters = new DynamicParameters();
parameters.Add("fairDates", fairDates);
return(BaseCnn.Query <CompanyReport>(query, parameters).AsList());
}
else
{
query = string.Format("SELECT co.CompanyName, co.CompanyId, COUNT(*) AdCount, st.StatusName FROM Advertisement ad" +
" JOIN Company co ON ad.CompanyId = co.CompanyId" +
" JOIN MagazineDate md ON ad.MagazineDateId = md.MagazineDateId" +
" JOIN Status st ON co.Status = st.StatusId " +
" WHERE md.Date BETWEEN '{0}' AND '{1}'" +
" AND st.StatusId IN ({2})" +
" GROUP BY co.CompanyName" +
" ORDER by AdCount desc; ", sdate, edate, statuses);
return(BaseCnn.Query <CompanyReport>(query).AsList());
}
}
