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