public dynamic GetSystemLog(RequestWrapper request)
{
var page = request.GetValue<int>("page", 1);
var rows = request.GetValue<int>("rows", 0);
var logDate = request.GetValue("LogDate");
var list = new List<dynamic>();
var basepath = HttpContext.Current.Server.MapPath("/logs/");
var di = new DirectoryInfo(basepath);
if (!di.Exists) di.Create();
string[] s = logDate.Split('到');
string s1 = "1990-01-01";
string s2 = DateTime.Now.Date.ToString();
switch (s.Length)
{
case 1:
if (logDate.Length > 0)
{
s1 = s[0];
s2 = s1;
}
break;
case 2:
s1 = s[0];
s2 = s[1];
break;
}
int t1 = Convert.ToInt32(Convert.ToDateTime(s1).ToString("yyyyMMdd"));
int t2 = Convert.ToInt32(Convert.ToDateTime(s2).ToString("yyyyMMdd"));
foreach (var fi in di.GetFiles().Where(x => (Convert.ToInt32(x.FullName.Replace(basepath, "").Substring(3,8)) >= t1 && Convert.ToInt32(x.FullName.Replace(basepath, "").Substring(3,8)) <= t2)))
{
dynamic item = new ExpandoObject();
item.filename = fi.FullName.Replace(basepath, "");
item.size = (fi.Length / 1024).ToString() + " KB";
item.time = fi.CreationTime.ToString();
item.id = item.filename.Replace(".txt", "");
list.Add(item);
}
var result = list.OrderByDescending(x => x.filename).Skip((page - 1) * rows).Take(rows);
return new { rows = result, total = list.Count() };
}
//单击取得材料库房分布明细
public dynamic GetMaterialDetail(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string MaterialCode = query.GetValue("MaterialCode");
var result = new List<dynamic>();
string sSql = string.Format(@"
Select *
From mms_warehouseStock
Left join mms_warehouse on mms_warehouse.WarehouseCode=mms_warehouseStock.WarehouseCode
Where mms_warehouse.ProjectCode='{0}' and mms_warehouseStock.MaterialCode='{1}'
", CurrentProjectCode, MaterialCode);
using (var db = Db.Context("Mms"))
{
result = db.Sql(sSql).QueryMany<dynamic>();
}
return result;
}
//收发-结存报表
public dynamic GetBalanceAccount(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string WarehouseCode = query.GetValue("WarehouseCode");
string BuildPartCode = query.GetValue("BuildPartCode");
string PickUnitName = query.GetValue("PickUnitName");
string MaterialName = query.GetValue("MaterialName");
string QueryDate = query.GetValue("QueryDate");
string sortField = query.GetValue("sort","MaterialName");
string order = query.GetValue("order");
int page = query.GetValue<int>("page",1);
int rows = query.GetValue<int>("rows",0);
string sTitle = "";
var result = new List<dynamic>();
var initResult = new List<dynamic>();
#region 条件
Dictionary<string, string> sCondition = new Dictionary<string, string>();
sCondition.Add("receive", string.Format(" And mms_receive.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("return", string.Format(" And mms_return.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("direct", string.Format(" And mms_direct.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("rentin", string.Format(" And mms_rentin.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("adjust", string.Format(" And mms_stockAdjust.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("send", string.Format(" And mms_send.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("refund", string.Format(" And mms_refund.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("transfer", string.Format(" And mms_transfer.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("lossReport", string.Format(" And mms_lossReport.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("init", string.Format(" And mms_warehouse.ProjectCode='{0}'", CurrentProjectCode));
if (WarehouseCode.Length > 0)
{
sCondition["receive"] += string.Format(" And mms_receive.WarehouseCode ='{0}'", WarehouseCode);
sCondition["return"] += string.Format(" And mms_return.WarehouseCode ='{0}'", WarehouseCode);
sCondition["direct"] += string.Format(" And 1=0");
sCondition["rentin"] += string.Format(" And 1=0");
sCondition["adjust"] += string.Format(" And mms_stockAdjust.WarehouseCode ='{0}'", WarehouseCode);
sCondition["send"] += string.Format(" And mms_send.WarehouseCode ='{0}'", WarehouseCode);
sCondition["refund"] += string.Format(" And mms_refund.WarehouseCode ='{0}'", WarehouseCode);
sCondition["transfer"] += string.Format(" And mms_transfer.WarehouseCode ='{0}'", WarehouseCode);
sCondition["lossReport"] += string.Format(" And mms_lossReport.WarehouseCode ='{0}'", WarehouseCode);
sCondition["init"] += string.Format(" And mms_warehouseStockHistory.WarehouseCode ='{0}'", WarehouseCode);
}
if (BuildPartCode.Length > 0)
{
sCondition["receive"] += string.Format(" And 1=0");
sCondition["return"] += string.Format(" And 1=0");
sCondition["direct"] += string.Format(" And mms_direct.BuildPartCode ='{0}'", BuildPartCode);
sCondition["rentin"] += string.Format(" And mms_rentin.BuildPartCode ='{0}'", BuildPartCode);
sCondition["adjust"] += string.Format(" And 1=0");
sCondition["send"] += string.Format(" And mms_send.BuildPartCode ='{0}'", BuildPartCode);
sCondition["refund"] += string.Format(" And 1=0");
sCondition["transfer"] += string.Format(" And 1=0");
sCondition["lossReport"] += string.Format(" And 1=0");
}
if (PickUnitName.Length > 0)
{
sCondition["receive"] += string.Format(" And 1=0");
sCondition["return"] += string.Format(" And 1=0");
sCondition["direct"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["rentin"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["adjust"] += string.Format(" And 1=0");
sCondition["send"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["refund"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["transfer"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["lossReport"] += string.Format(" And 1=0");
}
if (MaterialName.Length > 0)
{
sCondition["receive"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["return"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["direct"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["rentin"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["adjust"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["send"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["refund"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["transfer"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["lossReport"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["init"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
if (QueryDate.Length > 0)
{
sCondition["receive"] += GetDateRangeSql("mms_receive.ReceiveDate", QueryDate);
sCondition["return"] += GetDateRangeSql("mms_return.ReturnDate", QueryDate);
sCondition["direct"] += GetDateRangeSql("mms_direct.ArriveDate", QueryDate);
sCondition["rentin"] += GetDateRangeSql("mms_rentin.RentinDate", QueryDate);
sCondition["adjust"] += GetDateRangeSql("mms_stockAdjust.EffectDate", QueryDate);
sCondition["send"] += GetDateRangeSql("mms_send.SendDate", QueryDate);
sCondition["refund"] += GetDateRangeSql("mms_refund.RefundDate", QueryDate);
sCondition["transfer"] += GetDateRangeSql("mms_transfer.TransferDate", QueryDate);
sCondition["lossReport"] += GetDateRangeSql("mms_lossReport.LossReportDate", QueryDate);
sCondition["init"] += " and mms_warehouseStockHistory.CreateDate<'" + QueryDate.Split('到')[0].Trim(' ') + "'" ;
}
#endregion
//1.取得数据源的Sql
string sqlSource = GetTargetData(sCondition);
//2.取得发料的领料单位列表
string sqlPickList = string.Format(@"
Select PickUnitName
From ({0}) as t
Where PickUnitName<>'' and PickUnitName<>'其他' and PickUnitName<>'租赁进场'
group by PickUnitName
", sqlSource);
using (var db = Db.Context("Mms"))
{
//2.取得领料单位数据
string sDynamicColumn = "";
var returnPickList = db.Sql(sqlPickList).QueryMany<dynamic>();
for (int i = 0; i < returnPickList.Count; i++)
{
string sPickUnitName = returnPickList[i].PickUnitName;
sDynamicColumn += string.Format(" ,ISNULL(Sum(CASE WHEN PickUnitName='{0}' then Num else 0 end),0) as PickUnitNum{1}", sPickUnitName, i);
sDynamicColumn += string.Format(" ,ISNULL(Sum(CASE WHEN PickUnitName='{0}' then Money else 0 end),0) as PickUnitMoney{1}", sPickUnitName, i);
}
//3.取得外层Sql
string sql = string.Format(GetBalanceSql(), sDynamicColumn, sqlSource);
result = db.Sql(sql).QueryMany<dynamic>();
sTitle = GetBanlanceTitle(returnPickList);
//4.从履历表里取得期初的数据
sql = string.Format(@"
Select mms_warehouseStockHistory.MaterialCode,sum(Num) As InitNum,sum(UnitPrice*Num) As InitMoney
From mms_warehouseStockHistory
Left join mms_warehouse on mms_warehouse.warehouseCode=mms_warehouseStockHistory.warehouseCode
Left join mms_material on mms_material.MaterialCode=mms_warehouseStockHistory.MaterialCode
Where 1=1 {0}
group by mms_warehouseStockHistory.MaterialCode
"
, sCondition["init"]);
initResult = db.Sql(sql).QueryMany<dynamic>();
}
var list = from q in result
join p in initResult on q.MaterialCode equals p.MaterialCode into temp
from tt in temp.DefaultIfEmpty()
select Extend(q, new
{
InitNum = tt == null ? 0 : tt.InitNum,
InitMoney = tt == null ? 0 : tt.InitMoney,
TotalMoney = (tt == null ? 0 : tt.InitMoney) + ((q.ReceiveMoney == null) ? 0 : q.ReceiveMoney) - ((q.SendMoney == null) ? 0 : q.SendMoney),
TotalNum = (tt == null ? 0 : tt.InitNum) + ((q.ReceiveNum == null) ? 0 : q.ReceiveNum) - ((q.SendNum == null) ? 0 : q.SendNum)
});
if(order == "desc")
list = list.OrderByDescending(x => ((IDictionary<string, object>)x)[sortField]).Skip((page - 1) * rows).Take(rows);
else
list = list.OrderBy(x => ((IDictionary<string, object>)x)[sortField]).Skip((page-1) * rows).Take(rows);
dynamic rtnData = new ExpandoObject();
rtnData.Title = sTitle;
rtnData.rows = list.ToList();
return rtnData;
}
//材料预警提示
public dynamic GetMaterialWarnning(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string MaterialType = query.GetValue("MaterialType");
string MaterialName = query.GetValue("MaterialName");
string IsWarnning = query.GetValue("IsWarnning");
string sWhere = "";
string order = query.GetValue("order");
int page = query.GetValue<int>("page", 1);
int rows = query.GetValue<int>("rows", 0);
if (MaterialName.Length > 0)
{
sWhere += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
if (IsWarnning == "true")
{
sWhere += string.Format(" And (mms_materialWarnning.LowerNum>mms_materialWarnning.Num OR mms_materialWarnning.Num>mms_materialWarnning.UpperNum)");
}
string sSql = string.Format(@"
Select mms_materialWarnning.*,mms_material.MaterialName,mms_material.Model,mms_material.Unit
From mms_materialWarnning
Left join mms_material on mms_material.MaterialCode=mms_materialWarnning.MaterialCode
Where mms_materialWarnning.ProjectCode='{0}' {1}
", CurrentProjectCode, sWhere);
dynamic result = new ExpandoObject();
using (var db = Db.Context("Mms"))
{
var data = db.Sql(sSql).QueryMany<dynamic>();
result.rows = data.Skip((page - 1) * rows).Take(rows);
result.total = data.Count();
}
return result;
}
//库存流水查询
public dynamic GetWarehouseAccount(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string MaterialType = query.GetValue("MaterialType");
string CreatePerson = query.GetValue("CreatePerson");
string SrcBillType = query.GetValue("SrcBillType");
string SrcBillNo = query.GetValue("SrcBillNo");
string MaterialName = query.GetValue("MaterialName");
string QueryDate = query.GetValue("QueryDate");
string sWhere = "";
string order = query.GetValue("order");
int page = query.GetValue<int>("page", 1);
int rows = query.GetValue<int>("rows", 0);
if (CreatePerson.Length > 0)
{
sWhere += string.Format(" And mms_warehouseStockHistory.CreatePerson like '%{0}%'", CreatePerson);
}
if (MaterialName.Length > 0)
{
sWhere += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
if (SrcBillType.Length > 0)
{
sWhere += string.Format(" And mms_warehouseStockHistory.SrcBillType='{0}'", SrcBillType);
}
if (SrcBillNo.Length > 0)
{
sWhere += string.Format(" And mms_warehouseStockHistory.SrcBillNo like '%{0}%'", SrcBillNo);
}
if (QueryDate.Length > 0)
{
sWhere += GetDateRangeSql("mms_warehouseStockHistory.CreateDate", QueryDate); //" And " + Cp.DateRange(new WhereData() { Column = "", Value = QueryDate });
}
string sSql = string.Format(@"
Select mms_warehouseStockHistory.*,mms_material.MaterialName,mms_material.Model,mms_material.Unit
From mms_warehouseStockHistory
Left join mms_warehouse on mms_warehouse.WarehouseCode=mms_warehouseStockHistory.WarehouseCode
Left join mms_material on mms_material.MaterialCode=mms_warehouseStockHistory.MaterialCode
Where mms_warehouse.ProjectCode='{0}' {1}
order by mms_warehouseStockHistory.CreateDate desc
", CurrentProjectCode, sWhere);
dynamic result = new ExpandoObject();
using (var db = Db.Context("Mms"))
{
var data = db.Sql(sSql).QueryMany<dynamic>();
result.rows = data.Skip((page-1)*rows).Take(rows);
result.total = data.Count();
}
return result;
}
//库存查询
public dynamic GetWarehouseQuery(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string WarehouseCode = query.GetValue("WarehouseCode");
string MaterialName = query.GetValue("MaterialName");
string sWhere = "";
var result = new List<dynamic>();
if (WarehouseCode.Length > 0)
{
sWhere += string.Format(" And mms_warehouse.WarehouseCode='{0}'", WarehouseCode);
}
if (MaterialName.Length > 0)
{
sWhere += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
string sSql = string.Format(@"
Select mms_warehouseStock.*,mms_material.MaterialName,mms_material.Model,mms_warehouse.WarehouseName,
mms_warehouseStock.WarehouseCode+'_'+mms_material.MaterialCode as id,'' AS name
From mms_warehouseStock
Left join mms_warehouse on mms_warehouseStock.WarehouseCode=mms_warehouse.WarehouseCode
Left join mms_material on mms_material.MaterialCode=mms_warehouseStock.MaterialCode
Where mms_warehouse.ProjectCode='{0}' {1}
", CurrentProjectCode, sWhere);
using (var db = Db.Context("Mms"))
{
result = db.Sql(sSql).QueryMany<dynamic>();
var list = from q in result
group q by new { q.WarehouseName, q.WarehouseCode } into g
select new
{
name = g.Key.WarehouseName,
id = g.Key.WarehouseCode,
Money = g.Sum(x => (decimal?)x.Money),
UnitPrice = "",
Num = ""
};
result.AddRange(list);
}
return result;
}
//发料情况分析（领料单位角度）
public dynamic GetSendSummary(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string PickUnitName = query.GetValue("PickUnitName");
string BuildPartCode = query.GetValue("BuildPartCode");
string SrcBillType = query.GetValue("SrcBillType");
string MaterialType = query.GetValue("MaterialType");
string MaterialName = query.GetValue("MaterialName");
string QueryDate = query.GetValue("QueryDate");
string sWhere = "";
var result = new List<dynamic>();
Dictionary<string, string> sCondition = new Dictionary<string, string>();
sCondition.Add("send", string.Format(" And mms_send.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("direct", string.Format(" And mms_direct.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("refund", string.Format(" And mms_refund.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("rentin", string.Format(" And mms_rentin.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("lossReport", string.Format(" And mms_lossReport.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("transfer", string.Format(" And mms_transfer.ProjectCode='{0}'", CurrentProjectCode));
if (PickUnitName.Length > 0)
{
sCondition["send"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["direct"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["refund"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["rentin"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
sCondition["lossReport"] += string.Format(" And '其它'='{0}'", PickUnitName);
sCondition["transfer"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", PickUnitName);
}
if (BuildPartCode.Length > 0)
{
sCondition["send"] += string.Format(" And mms_send.BuildPartCode='{0}'", BuildPartCode);
sCondition["direct"] += string.Format(" And mms_direct.BuildPartCode='{0}'", BuildPartCode);
sCondition["refund"] += string.Format(" And 1=0", BuildPartCode);
sCondition["rentin"] += string.Format(" And mms_rentin.BuildPartCode='{0}'", BuildPartCode);
sCondition["lossReport"] += string.Format(" And 1=0", PickUnitName);
sCondition["transfer"] += string.Format(" And 1=0", PickUnitName);
}
if (MaterialName.Length > 0)
{
sCondition["send"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["direct"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["refund"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["rentin"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["lossReport"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["transfer"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
if (SrcBillType.Length > 0)
{
sWhere += string.Format(" And T.SrcBillType='{0}'", SrcBillType);
}
if (QueryDate.Length > 0)
{
sCondition["send"] += GetDateRangeSql("mms_send.SendDate", QueryDate);
sCondition["direct"] += GetDateRangeSql("mms_direct.ArriveDate", QueryDate);
sCondition["refund"] += GetDateRangeSql("mms_refund.RefundDate", QueryDate);
sCondition["rentin"] += GetDateRangeSql("mms_rentin.RentInDate", QueryDate);
sCondition["lossReport"] += GetDateRangeSql("mms_lossReport.LossReportDate", QueryDate);
sCondition["transfer"] += GetDateRangeSql("mms_transfer.TransferDate", QueryDate);
}
string sSql = string.Format(@"
Select T.*
From
(
--发料单
Select mms_send.BillNo As SrcBillNo,'send' As SrcBillType,mms_send.SendDate As TakeDate,
mms_send.PickUnit,mms_merchants.MerchantsName as PickUnitName,mms_buildPart.BuildPartName,
mms_sendDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_sendDetail.Unit,mms_sendDetail.UnitPrice,mms_sendDetail.Num,mms_sendDetail.Money,
mms_sendDetail.Remark,'' as name,(mms_send.BillNo+'send'+mms_sendDetail.MaterialCode) as id
From mms_sendDetail
Left join mms_send ON mms_sendDetail.BillNo=mms_send.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_send.PickUnit
left join mms_material on mms_material.MaterialCode=mms_sendDetail.MaterialCode
left join mms_buildPart on mms_buildPart.BuildPartCode=mms_send.BuildPartCode
Where mms_send.ApproveState='passed' {0}
UNION
--直入直出
Select mms_direct.BillNo As SrcBillNo,'direct' As SrcBillType,mms_direct.ArriveDate As TakeDate,
mms_direct.PickUnit,mms_merchants.MerchantsName as PickUnitName,mms_buildPart.BuildPartName,
mms_directDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_directDetail.Unit,mms_directDetail.UnitPrice,mms_directDetail.Num,mms_directDetail.Money,
mms_directDetail.Remark,'' as name,(mms_direct.BillNo+'direct'+mms_directDetail.MaterialCode) as id
From mms_directDetail
Left join mms_direct ON mms_directDetail.BillNo=mms_direct.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_direct.PickUnit
left join mms_material on mms_material.MaterialCode=mms_directDetail.MaterialCode
left join mms_buildPart on mms_buildPart.BuildPartCode=mms_direct.BuildPartCode
Where mms_direct.ApproveState='passed' {1}
UNION
--退库
Select mms_refund.BillNo As SrcBillNo,'refund' As SrcBillType,mms_refund.RefundDate As TakeDate,
mms_refund.RefundUnit as PickUnit,mms_merchants.MerchantsName as PickUnitName,NULL AS BuildPartName,
mms_refundDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_refundDetail.Unit,mms_refundDetail.UnitPrice,-mms_refundDetail.Num,-mms_refundDetail.Money,
mms_refundDetail.Remark,'' as name,(mms_refund.BillNo+'refund'+mms_refundDetail.MaterialCode) as id
From mms_refundDetail
Left join mms_refund ON mms_refundDetail.BillNo=mms_refund.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_refund.RefundUnit
left join mms_material on mms_material.MaterialCode=mms_refundDetail.MaterialCode
Where mms_refund.ApproveState='passed' {2}
UNION
--租赁进场
Select mms_rentin.BillNo As SrcBillNo,'rentin' As SrcBillType,mms_rentin.RentInDate As TakeDate,
mms_rentinDetail.UseUnit as PickUnit,mms_merchants.MerchantsName as PickUnitName,mms_buildPart.BuildPartName,
mms_rentinDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_rentinDetail.Unit,mms_rentinDetail.UnitPrice,mms_rentinDetail.Num,'0' As Money,
mms_rentinDetail.Remark,'' as name,(mms_rentin.BillNo+'rentin'+mms_rentinDetail.MaterialCode) as id
From mms_rentinDetail
Left join mms_rentin ON mms_rentinDetail.BillNo=mms_rentin.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_rentinDetail.UseUnit
left join mms_material on mms_material.MaterialCode=mms_rentinDetail.MaterialCode
left join mms_buildPart on mms_buildPart.BuildPartCode=mms_rentin.BuildPartCode
Where mms_rentin.ApproveState='passed' {3}
UNION
--报损
Select mms_lossReport.BillNo As SrcBillNo,'lossReport' As SrcBillType,mms_lossReport.LossReportDate As TakeDate,
'其他' as PickUnit,'其他' as PickUnitName,NULL AS BuildPartName,
mms_lossReportDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_lossReportDetail.Unit,mms_lossReportDetail.UnitPrice,mms_lossReportDetail.Num,mms_lossReportDetail.Money,
mms_lossReportDetail.Remark,'' as name,(mms_lossReport.BillNo+'lossReport'+mms_lossReportDetail.MaterialCode) as id
From mms_lossReportDetail
Left join mms_lossReport ON mms_lossReportDetail.BillNo=mms_lossReport.BillNo
left join mms_material on mms_material.MaterialCode=mms_lossReportDetail.MaterialCode
Where mms_lossReport.ApproveState='passed' {4}
UNION
--调拨
Select mms_transfer.BillNo As SrcBillNo,'transfer' As SrcBillType,mms_transfer.TransferDate As TakeDate,
mms_transfer.ReceiveUnit as PickUnit,mms_merchants.MerchantsName as PickUnitName,NULL AS BuildPartName,
mms_transferDetail.MaterialCode,mms_material.MaterialName,mms_material.Model,
mms_transferDetail.Unit,mms_transferDetail.UnitPrice,mms_transferDetail.Num,mms_transferDetail.Money,
mms_transferDetail.Remark,'' as name,(mms_transfer.BillNo+'transfer'+mms_transferDetail.MaterialCode) as id
From mms_transferDetail
Left join mms_transfer ON mms_transferDetail.BillNo=mms_transfer.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_transfer.ReceiveUnit
left join mms_material on mms_material.MaterialCode=mms_transferDetail.MaterialCode
Where mms_transfer.ApproveState='passed' {5}
) as T
Where 1=1 {6}
", sCondition["send"], sCondition["direct"], sCondition["refund"], sCondition["rentin"], sCondition["lossReport"], sCondition["transfer"], sWhere);
using (var db = Db.Context("Mms"))
{
result = db.Sql(sSql).QueryMany<dynamic>();
var list = from q in result
group q by new { q.PickUnitName, q.PickUnit } into g
select new
{
name = g.Key.PickUnitName,
id = g.Key.PickUnit,
Money = g.Sum(x => (decimal?)x.Money),
PickUnit = "",
UnitPrice = "",
Num = ""
};
result.AddRange(list);
}
return result;
}
//收料情况分析（供应商角度）
public dynamic GetReceiveSummary(RequestWrapper query)
{
var CurrentProjectCode = MmsHelper.GetCurrentProject();
string SupplierName = query.GetValue("SupplierName");
string ContractCode = query.GetValue("ContractCode");
string SrcBillType = query.GetValue("SrcBillType");
string MaterialType = query.GetValue("MaterialType");
string MaterialName = query.GetValue("MaterialName");
string QueryDate = query.GetValue("QueryDate");
string sWhere ="";
var result = new List<dynamic>();
Dictionary<string,string> sCondition = new Dictionary<string,string>();
sCondition.Add("receive", string.Format(" And mms_receive.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("return", string.Format(" And mms_return.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("rentin", string.Format(" And mms_rentin.ProjectCode='{0}'", CurrentProjectCode));
sCondition.Add("direct", string.Format(" And mms_direct.ProjectCode='{0}'", CurrentProjectCode));
if (SupplierName.Length > 0)
{
sCondition["receive"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", SupplierName);
sCondition["rentin"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", SupplierName);
sCondition["return"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", SupplierName);
sCondition["direct"] += string.Format(" And mms_merchants.MerchantsName like '%{0}%'", SupplierName);
}
if (ContractCode.Length > 0)
{
sCondition["receive"] += string.Format(" And mms_receive.ContractCode like '%{0}%'", ContractCode);
sCondition["rentin"] += string.Format(" And mms_rentin.ContractCode like '%{0}%'", ContractCode);
sCondition["return"] += string.Format(" And mms_return.ContractCode like '%{0}%'", ContractCode);
sCondition["direct"] += string.Format(" And mms_direct.ContractCode like '%{0}%'", ContractCode);
}
if (MaterialName.Length > 0)
{
sCondition["receive"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["rentin"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["return"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
sCondition["direct"] += string.Format(" And mms_material.MaterialName like '%{0}%'", MaterialName);
}
if (SrcBillType.Length > 0)
{
sWhere += string.Format(" And T.SrcBillType='{0}'", SrcBillType);
}
if (QueryDate.Length > 0)
{
sCondition["receive"] += GetDateRangeSql("mms_receive.ReceiveDate", QueryDate);
sCondition["rentin"] += GetDateRangeSql("mms_rentIn.RentInDate", QueryDate);
sCondition["return"] += GetDateRangeSql("mms_return.ReturnDate", QueryDate);
sCondition["direct"] += GetDateRangeSql("mms_direct.ArriveDate", QueryDate);
}
string sSql = string.Format(@"
Select T.*
From
(
--收料单
Select mms_receive.BillNo As SrcBillNo,'receive' As SrcBillType,mms_receive.ContractCode As ContractCode,
mms_receive.SupplierCode,mms_receive.OriginalNum,mms_receive.ReceiveDate As TakeDate,
mms_receiveDetail.MaterialCode,mms_receiveDetail.Unit,mms_receiveDetail.UnitPrice,
mms_receiveDetail.Num,mms_receiveDetail.Money,mms_receiveDetail.Remark,mms_merchants.MerchantsName AS SupplierName,
'' as name,(mms_receive.BillNo+'receive'+mms_receiveDetail.MaterialCode) as id,mms_material.MaterialName,mms_material.Model
From mms_receiveDetail
Left join mms_receive ON mms_receiveDetail.BillNo=mms_receive.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_receive.SupplierCode
left join mms_material on mms_material.MaterialCode=mms_receiveDetail.MaterialCode
Where mms_receive.ApproveState='passed' {0}
UNION
--直入直出
Select mms_direct.BillNo As SrcBillNo,'direct' As SrcBillType,mms_direct.ContractCode As ContractCode,
mms_direct.SupplierCode,mms_direct.OriginalNum,mms_direct.ArriveDate As TakeDate,
mms_directDetail.MaterialCode,mms_directDetail.Unit,mms_directDetail.UnitPrice,
mms_directDetail.Num,mms_directDetail.Money,mms_directDetail.Remark,mms_merchants.MerchantsName AS SupplierName,
'' as name,(mms_direct.BillNo+'direct'+mms_directDetail.MaterialCode) as id,mms_material.MaterialName,mms_material.Model
From mms_directDetail
Left join mms_direct ON mms_directDetail.BillNo=mms_direct.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_direct.SupplierCode
left join mms_material on mms_material.MaterialCode=mms_directDetail.MaterialCode
Where mms_direct.ApproveState='passed' {1}
UNION
--退货
Select mms_return.BillNo As SrcBillNo,'return' As SrcBillType,mms_return.ContractCode As ContractCode,
mms_return.SupplierCode,'' AS OriginalNum,mms_return.ReturnDate As TakeDate,
mms_returnDetail.MaterialCode,mms_returnDetail.Unit,mms_returnDetail.UnitPrice,
-mms_returnDetail.Num,-mms_returnDetail.Money,mms_returnDetail.Remark,mms_merchants.MerchantsName AS SupplierName,
'' as name,(mms_return.BillNo+'return'+mms_returnDetail.MaterialCode) as id,mms_material.MaterialName,mms_material.Model
From mms_returnDetail
Left join mms_return ON mms_returnDetail.BillNo=mms_return.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_return.SupplierCode
left join mms_material on mms_material.MaterialCode=mms_returnDetail.MaterialCode
Where mms_return.ApproveState='passed' {2}
UNION
--租赁进场
Select mms_rentIn.BillNo As SrcBillNo,'rentin' As SrcBillType,mms_rentIn.ContractCode As ContractCode,
mms_rentIn.SupplierCode,'' as OriginalNum,mms_rentIn.RentInDate As TakeDate,
mms_rentInDetail.MaterialCode,mms_rentInDetail.Unit,mms_rentInDetail.UnitPrice,
mms_rentInDetail.Num,'0' as Money,mms_rentInDetail.Remark,mms_merchants.MerchantsName AS SupplierName,
'' as name,(mms_rentIn.BillNo+'rentIn'+mms_rentInDetail.MaterialCode) as id,mms_material.MaterialName,mms_material.Model
From mms_rentInDetail
Left join mms_rentIn ON mms_rentInDetail.BillNo=mms_rentIn.BillNo
left join mms_merchants on mms_merchants.MerchantsCode=mms_rentIn.SupplierCode
left join mms_material on mms_material.MaterialCode=mms_rentInDetail.MaterialCode
Where mms_rentIn.ApproveState='passed' {3}
) as T
Where 1=1 {4}
", sCondition["receive"], sCondition["direct"], sCondition["return"], sCondition["rentin"], sWhere);
using (var db = Db.Context("Mms"))
{
result = db.Sql(sSql).QueryMany<dynamic>();
var list = from q in result
group q by new { q.SupplierName, q.SupplierCode } into g
select new
{
name = g.Key.SupplierName,
id = g.Key.SupplierCode,
Money = g.Sum(x => (decimal?)x.Money),
SupplierCode = "",
UnitPrice="",
Num=""
};
result.AddRange(list);
}
return result;
}