Hl.WebMain/Common/BizSelectHelper.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Hl.WebMain.Common
{
    public class BizSelectHelper
    {
        /// <summary>
        /// 查询车辆在当前门店里服务次数
        /// </summary>
        /// <param name="vehicleId">车辆主键</param>
        /// <param name="admshopId">当前门店</param>
        /// <returns></returns>
        public static string GetVehicleServiceCountCshop(string vehicleId)
        {
            return " (Select Count(*) From BizServiceInfoRecordLinks Where VehicleId=" + vehicleId + " And AdmShopId=" + DataBll.AdmSafe.ReadCurrUser().ShopId + ") As 本店来厂次数"
                 + ",(Select Count(*) From BizServiceInfoRecordLinks Where VehicleId=" + vehicleId + ") As 连锁来厂次数"; 
        }

        /// <summary>
        /// 查询配件库存信息
        /// </summary>
        /// <param name="partId">配件主键</param>
        /// <param name="shopId">所属门店</param>
        public static string GetPartStockInfoByShopKeyId(string partId,string shopId)
        {
            return " (Select Stock From BizServicePartStock Where BizServicePartStock.PartId=" + partId + " And BizServicePartStock.AdmShopId=" + shopId + ")  As 库存数量" //库存数量
                  + ",(Select Price From BizServicePartStock Where BizServicePartStock.PartId=" + partId + " And BizServicePartStock.AdmShopId=" + shopId + ") As 最近购价"//最近购价
                  + ",(Select StockUpperLimit From BizServicePartStock Where BizServicePartStock.PartId=" + partId + " And BizServicePartStock.AdmShopId=" + shopId + ") As 库存上限"//库存上限
                  + ",(Select StockUpperLimit From BizServicePartStock Where BizServicePartStock.PartId=" + partId + " And BizServicePartStock.AdmShopId=" + shopId + ") As 库存下限"//库存下限
                  + ",(Select WarehouseNo     From BizServicePartStock Where BizServicePartStock.PartId=" + partId + " And BizServicePartStock.AdmShopId=" + shopId + ") As 库存仓位" //库存仓位
                  + ",(Select SUM(PartCount-GuiKuCount) From BizServicePartStockChuKuDetail Where BizServicePartStockChuKuDetail.PartId=" + partId + " And BizServicePartStockChuKuDetail.CreateDate>='" + DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd HH:mm:ss") + "')  As 配件周用量 " //配件周用量
                  + ",(Select SUM(PartCount-GuiKuCount) From BizServicePartStockChuKuDetail Where BizServicePartStockChuKuDetail.PartId=" + partId + " And BizServicePartStockChuKuDetail.CreateDate>='" + DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd HH:mm:ss") + "') As 配件月用量" //配件月用量
                  + ",(Select SUM(PartCountSurplus*PartPrice) From BizServicePartOrderPurchaseDetail Left JOIN BizServicePartOrderPurchase On BizServicePartOrderPurchase.Id=BizServicePartOrderPurchaseDetail.OrderPurchaseId Where BizServicePartOrderPurchaseDetail.PartId=" + partId + " And BizServicePartOrderPurchaseDetail.AdmShopId=" + shopId + " And BizServicePartOrderPurchase.Status<>" + (int)Enum.BizServicePartOrderPurchaseStatus.待入库 + ") As 库存金额" //配件库存金额
                  + "";
        }

        /// <summary>
        /// 查询配件归库信息
        /// </summary>
        /// <param name="partId">配件主键</param>
        /// <param name="shopId">所属门店</param>
        /// <param name="dateStart">开始日期</param>
        /// <param name="dateClose">截止日期</param>
        /// <returns></returns>
        public static string GetPartStockGuiKuInfoByDate(string partId,string shopId,string dateStart, string dateClose)
        {
            string curTable = "BizServicePartOrderPurchaseDetail Left JOIN BizServicePartOrderPurchase On BizServicePartOrderPurchase.Id=BizServicePartOrderPurchaseDetail.OrderPurchaseId";
            string strWhere = "BizServicePartOrderPurchaseDetail.PartId=" + partId + " And BizServicePartOrderPurchase.AdmShopId=" + shopId
                            + Hl.Code.HtmlHelper.FieldWhereByRiqParm(dateStart, " And BizServicePartOrderPurchase.EnsureDate{0} ", true)
                            + Hl.Code.HtmlHelper.FieldWhereByRiqParm(dateClose, " And BizServicePartOrderPurchase.EnsureDate{0} ", false)
                            + " And BizServicePartOrderPurchase.Status<>" + (int)Enum.BizServicePartOrderPurchaseStatus.待入库 + " ";
            return " (Select SUM(PartCount)           From " + curTable + " Where " + strWhere + ") As 配件入库数量" //配件入库数量
                 + ",(Select AVG(PartPrice)           From " + curTable + " Where " + strWhere + ") As 配件入库单价" //配件入库单价
                 + ",(Select SUM(PartPrice*PartCount) From " + curTable + " Where " + strWhere + ") As 配件入库金额" //配件入库金额
                 + "";
        }