public async Task <List <People> > GetPeoples()
{
string sql = "Select * From people";
var peoples = ReadConnection.Query <People>(sql).ToList();
return(peoples);
}
public async Task <Tuple <List <MaintenanceInfoResponse>, int> > GetPageListAsync(MaintenanceInfoPageRequest request)
{
StringBuilder sbSql = new StringBuilder();
StringBuilder sbCount = new StringBuilder();
DynamicParameters parameters = new DynamicParameters();
sbSql.Append(@" SELECT * FROM maintenanceinfo m
LEFT JOIN servicebusiness s ON m.RepairId = s.Id
LEFT JOIN attachment a ON m.Id = a.DataId AND a.IsDeleted = 0 AND a.DataType = 1
WHERE 1=1 ");
sbCount.Append(" SELECT COUNT(*) FROM maintenanceinfo m WHERE 1=1 ");
var where = this.GetWhere(request, parameters);
if (where?.Item1?.Length > 0)
{
sbSql.Append(where.Item1);
sbCount.Append(where.Item1);
}
//添加排序
sbSql.Append(" Order By m.CreationTime DESC ");
if (request.Count.HasValue && request.Count.Value > 0)
{
sbSql.Append(" LIMIT @Skip,@Count ");
parameters.Add("@Skip", request.Skip.Value);
parameters.Add("@Count", request.Count.Value);
}
using (ReadConnection)
{
var lookup = new Dictionary <Guid, MaintenanceInfoResponse>();
var query = ReadConnection.Query <MaintenanceInfoResponse, ServiceBusiness, Attachment, List <MaintenanceInfoResponse> >(sbSql.ToString(), (a, c, b) =>
{
if (!lookup.Keys.Contains(a.Id))
{
if (a != null && c != null)
{
a.ServiceBusiness = c;
}
lookup.Add(a.Id, a);
if (lookup[a.Id].Attachments == null)
{
lookup[a.Id].Attachments = new List <Attachment>();
}
lookup[a.Id].Attachments.Add(b);
}
else
{
lookup[a.Id].Attachments.Add(b);
}
return(lookup.Values.ToList());
}, parameters);
int r2 = 0;
if (request.Count.HasValue && request.Count.Value > 0)
{
r2 = await ReadConnection.QueryFirstAsync <int>(sbCount.ToString(), parameters);
}
return(new Tuple <List <MaintenanceInfoResponse>, int>(lookup.Values.ToList(), r2));
}
}
public async Task <int> GetCodeTotalCount(string tableName, string paramName)
{
int totalCount = 0;
StringBuilder codeSql = new StringBuilder();
codeSql.AppendFormat("SELECT {0} FROM {1} WHERE CreationTime >= @TodayStart AND CreationTime < @TodayEnd ORDER BY {0} DESC LIMIT 0,1;", paramName, tableName);
DateTime TodayStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
DateTime TodayEnd = TodayStart.AddDays(1);
var list = ReadConnection.Query <string>(codeSql.ToString(), new { TodayStart = TodayStart, TodayEnd = TodayEnd }).ToList();
if (list?.Count > 0)
{
totalCount = int.Parse(list.FirstOrDefault().Substring(7, 4));
}
return(totalCount);
}