2

仲国强

2021-08-25 90b0bed54f740655aaea4236e342fbd21f2725d5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using WebAPI.Dapper;
 
namespace WebAPI.Service
{
    public class ProcessDal
    {
        public static List<Models.M_Process> GetProcessList(string hName, int hLevel, string hNumber, string hBillSubType, int page, int limit,ref int pageCount)
        {
          
            var whereBuilder = new StringBuilder();
            if (!string.IsNullOrEmpty(hName))
                whereBuilder.Append($" and a.HName like '%{hName}%'");
            if(!string.IsNullOrEmpty(hNumber))
                whereBuilder.Append($" and a.HNumber like '%{hNumber}%'");
            if (!string.IsNullOrEmpty(hBillSubType))
                whereBuilder.Append($" and a.HBillSubType like '%{hBillSubType}%'");
            if(hLevel!=0)
                whereBuilder.Append($" and a.HLevel={hLevel}");
            var sqlBuilder = new StringBuilder();
            sqlBuilder.Append($"select top {limit} a.HItemID,a.HNumber,a.HName ,a.HLevel,a.HTypeFlow ,a.HTypeCount ,a.HBillSubType ");
            sqlBuilder.Append(",a.HEndFlag,a.HStopFlag,b.hname,a.HSNo,a.HProcessID_K3,a.HFixPrice,a.HOverFixPrice ");
            sqlBuilder.Append(",a.HEndFlag,a.HStopFlag,b.hname,a.HSNo,a.HProcessID_K3,a.HFixPrice,a.HOverFixPrice ");
            sqlBuilder.Append($",a.HAutoTrunFlag,a.HRemark ,a.HProcessID_K3 from gy_Process a left join h_v_IF_Department b on a.HDeptID=b.hitemid where a.HItemID not in (select top {(page-1)*limit} a.HItemID from gy_Process a left join h_v_IF_Department b on a.HDeptID=b.hitemid where 1=1 { whereBuilder }) {whereBuilder}");
            var countSql = $"select count(*) from gy_Process a where 1=1 {whereBuilder}";
            pageCount = SqlPools.GetInstance("YqnConn").GetExecuteScalar(countSql);
            var list = SqlPools.GetInstance("YqnConn").GetModelList<Models.M_Process>(sqlBuilder.ToString());
            return list;
        }
        public static Models.M_Process GetProcessDetail(int hItemID)
        {
            var sql = "select HNumber,HHelpCode,HName,HNumber,HDeptID,HProcMulID,HRemark,HSNo,HFixPrice,HOverFixPrice,HBillSubType,HStopflag, HTypeFlow, HTypeCount, HAutoTrunFlag, HItemID from Gy_Process where HItemID=@hItemID";
            var model = SqlPools.GetInstance("YqnConn").GetModel<Models.M_Process>(sql, new { hItemID });
            return model;
        }
        public static int DeleteProcess(int hItemID)
        {
            var sql = "delete Gy_Process where HItemID=@hItemID";
            var result = SqlPools.GetInstance("YqnConn").ExecuteCommand(sql, new { hItemID });
            return result;
        }
        public static Models.M_Department GetDepartmentModel(int hItemID)
        {
            var sql = "select HName,HNumber,HItemID from  h_v_IF_Department where HItemID=@hItemID";
            var model = SqlPools.GetInstance("YqnConn").GetModel<Models.M_Department>(sql, new { hItemID });
            return model;
        }
        public static Models.M_ProcMul GetProcMulModel(int hItemID)
        {
            var sql = "Select HItemID,HNumber,HName from Gy_ProcMul where HItemID=@hItemID";
            var model = SqlPools.GetInstance("YqnConn").GetModel<Models.M_ProcMul>(sql, new { hItemID });
            return model;
        }
    }
}