Ejemplo n.º 1
0
        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() };
        }
Ejemplo n.º 2
0
        //单击取得材料库房分布明细
        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;
        }
Ejemplo n.º 3
0
        //收发-结存报表
        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;
        }
Ejemplo n.º 4
0
        //材料预警提示
        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;
        }
Ejemplo n.º 5
0
        //库存流水查询
        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;
        }
Ejemplo n.º 6
0
        //库存查询
        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;
        }
Ejemplo n.º 7
0
        //发料情况分析(领料单位角度)
        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;
        }
Ejemplo n.º 8
0
        //收料情况分析(供应商角度)
        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;
        }