DAL/TCWL_D_ContractStatic.cs

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

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

namespace DAL
{
    using Common;
    using System;
    using System.Data;
    using System.Runtime.InteropServices;
    using System.Text;

    public class TCWL_D_ContractStatic
    {
        public DataTable CusTop(string where)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 5 isnull(sum(totalMoney),0) as AllMoney,k.KeHuMingCheng,ht.clientID  from  TCWL_Order ht inner join TCWL_KeHuXinXi k on ht.clientID=k.ID  where ht.state in (1,3,4)  ");
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                builder.Append(" and " + where);
            }
            builder.Append(" group by  k.KeHuMingCheng,ht.clientID  order by AllMoney desc");
            return DBHelper.GetDataTable(builder.ToString());
        }

        public DataTable GetCus(string where)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select isnull(sum(totalMoney),0) as AllMoney,k.KeHuMingCheng,ht.clientID  from  TCWL_Order ht inner join TCWL_KeHuXinXi k on ht.clientID=k.ID  where ht.state in (1,3,4)  ");
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                builder.Append(" and " + where);
            }
            builder.Append(" group by  k.KeHuMingCheng,ht.clientID  order by AllMoney desc");
            return DBHelper.GetDataTable(builder.ToString());
        }

        public DataTable GetCus(string where, int startIndex, int endIndex, out int total)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from (");
            builder.Append("select isnull(sum(totalMoney),0) as AllMoney,k.KeHuMingCheng,ht.clientID,ROW_NUMBER() OVER(order by isnull(sum(totalMoney),0) desc) as ROW from  TCWL_Order ht inner join TCWL_KeHuXinXi k on ht.clientID=k.ID  where ht.state in (1,3,4) ");
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                builder.Append(" and " + where);
            }
            builder.Append(" group by  k.KeHuMingCheng,ht.clientID) as T");
            DataSet set = DBHelper.Query(builder.ToString());
            total = set.Tables[0].Rows.Count;
            builder.AppendFormat(" WHERE T.Row between {0} and {1}", startIndex, endIndex);
            return DBHelper.GetDataTable(builder.ToString());
        }

        public DataTable getHTAllQuarter_cus(string where)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select isnull(sum(totalMoney),0) as AllMoney,isnull(sum(actualMoney),0) as RecMoney,isnull((sum(totalMoney)-sum(actualMoney)),0) as UncMoney,k.KeHuMingCheng,CONVERT(nvarchar(4),ht.timeStr,120)+'年'+datename(quarter,ht.timeStr) as Time,ht.clientID from  TCWL_Order ht inner join TCWL_KeHuXinXi k on ht.clientID=k.ID  where ht.state in (1,3,4)  ");
            if (!string.IsNullOrEmpty(where.Trim()))
            {
                builder.Append(" and " + where);
            }
            builder.Append(" group by  CONVERT(nvarchar(4),ht.timeStr,120)+'年'+datename(quarter,ht.timeStr) ,k.KeHuMingCheng,ht.clientID  order by CONVERT(nvarchar(4),ht.timeStr,120)+'年'+datename(quarter,ht.timeStr)");
            return DBHelper.GetDataTable(builder.ToString());