public MaterialDataResult GetMaterial([FromQuery] int categoryId, int nameId, int specificationId, int siteId, int qId, bool menu, bool stock, string qIds)
{
var result = new MaterialDataResult();
var ids = !qIds.IsNullOrEmpty() ? qIds.Split(",").Select(int.Parse) : null;
if (stock)
{
var p = new List <string>();
if (qId != 0)
{
p.Add("BillId = @qId");
}
if (ids != null && ids.Any())
{
p.Add("BillId IN @ids");
}
var sql = $"SELECT * FROM `material_management`{(p.Any() ? $" WHERE {p.Join(" AND ")}" : "")};";
result.datas.AddRange(ServerConfig.ApiDb.Query <Material>(sql, new { qId, ids }).Select(x => new { x.Id, x.BillId, x.Number }));
return(result);
}
var data = MaterialHelper.GetDetails(categoryId, nameId, specificationId, siteId, qId, ids).OrderBy(x => x.Id);
if (qId != 0 && !data.Any())
{
return(Result.GenError <MaterialDataResult>(Error.MaterialBillNotExist));
}
result.Count = data.Sum(x => x.Number);
result.Sum = data.Sum(x => x.Number * x.Price);
if (menu)
{
result.datas.AddRange(data.Select(x => new
{
x.Id,
x.BillId,
x.Code,
x.InTime,
x.OutTime,
x.Number,
x.Unit,
x.Price,
x.Stock,
x.Category,
x.CategoryId,
x.Name,
x.NameId,
x.Specification,
x.SpecificationId,
x.Site,
x.SiteId,
x.Remark
}));
}
else
{
result.datas.AddRange(data);
}
return(result);
}
public MaterialDataResult GetMaterialLog([FromQuery] DateTime startTime, DateTime endTime, int isPlan, int planId, int billId, int qId, int type, string types, int purposeId, string purpose = "")
{
var typeList = !types.IsNullOrEmpty() ? types.Split(",").Select(int.Parse) : null;
var result = new MaterialDataResult();
var param = new List <string>();
if (startTime != default(DateTime) && endTime != default(DateTime))
{
param.Add("a.Time >= @startTime AND a.Time <= @endTime");
}
if (startTime != default(DateTime) && endTime == default(DateTime))
{
param.Add("a.Time >= @startTime AND a.Time <= @endTime");
startTime = startTime.StartOfMonth();
endTime = startTime.EndOfMonth();
}
if (isPlan != 0)
{
param.Add("a.PlanId != 0");
}
if (planId != 0)
{
param.Add("a.PlanId = @planId");
}
if (purposeId == 1)
{
param.Add("a.PlanId != 0");
}
else if (purposeId == 2)
{
param.Add("a.PlanId = 0");
}
if (billId != 0)
{
param.Add("a.BillId = @billId");
}
if (qId != 0)
{
param.Add("a.Id = @qId");
}
if (type != 0)
{
param.Add("a.Type = @type");
}
if (typeList != null && typeList.Any())
{
param.Add("a.Type IN @typeList");
}
if (purpose != "")
{
param.Add("a.Purpose = @purpose");
}
param.Add("a.Number != 0");
//var sql = "SELECT a.*, b.Plan, c.`Code`, c.`Price`, d.`Name`, e.Specification FROM `material_log` a " +
// "LEFT JOIN `production_plan` b ON a.PlanId = b.Id " +
// "LEFT JOIN `material_bill` c ON a.BillId = c.Id " +
// "LEFT JOIN `material_name` d ON a.NameId = d.Id " +
// "LEFT JOIN `material_specification` e ON a.SpecificationId = e.Id";
var sql =
"SELECT a.*, b.*, a.Id FROM `material_log` a " +
"JOIN `material_bill_info` b ON a.BillId = b.Id";
if (param.Any())
{
sql += " WHERE " + param.Join(" AND ");
}
var data = ServerConfig.ApiDb.Query <MaterialLog>(sql, new
{
startTime,
endTime,
planId,
billId,
qId,
type,
typeList,
purpose
}).OrderByDescending(x => x.Id);
result.Count = data.Sum(x => x.Number);
result.Sum = data.Sum(x => x.Number * x.Price);
result.datas.AddRange(data);
return(result);
}