ZLPMS/PMS.DBService/Lteration/LterationService.cs

using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QWPlatform.DataIntface;
using QWPlatform.SystemLibrary;
using PMS.BusinessModels.ProManager;
using System.Data;
using PMS.EntityModels.PersonManager;
using QWPlatform.IService.ServiceInterface;
using PMS.BusinessModels.Product;
using QWPlatform.Models;
using PMS.BusinessModels.Project;
using PMS.BusinessModels;
using QWPlatform.SystemLibrary.LogManager;
using PMS.BusinessModels.Account;
using PMS.EntityModels.ProManager;
using PMS.EntityModels.LterationModel;

namespace PMS.DBService.ProManager
{
    public class LterationService : DataServiceBase
    {
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

        public string GetId(string tableName)
        {
            return SqlBuilder.SqlText("select ZLCHS.f_Next_Id(':Name') as ID from dual").Parameters("Name", tableName).Select().Rows[0]["ID"].ToString();
        }

        public int Save(LterationModel model, int type)
        {
            //生成简码
            var dt = SqlBuilder.SqlText("select zlSpellCode(:name) as 简码 from dual ").Parameters("name", model.MC).Select();
            if (type == 1)
            {
                model.ID = GetId("问题迭代信息");
                return InsertBuilder.Insert("问题迭代信息")
                    .Column("ID", model.ID)
                    .Column("名称", model.MC)
                    .Column("简码", dt.Rows[0]["简码"].ToString())
                    .Column("类型", model.LX)
                    .Column("渠道ID", model.QDID)
                    .Column("项目ID", model.XMID)
                    .Column("产品ID", model.CPID)
                    .Column("责任人ID", model.ZRRID)
                    .Column("开始日期", model.KSRJ)
                    .Column("结束日期", model.JSRJ)
                    .Column("描述", model.MS)
                    .Column("建立日期", DataBaseBuilder.GetDateTime)
                    .Column("状态", model.ZT)
                    .Column("进度", 0)
                    .Execute();
            }
            else
            {
                return UpdateBuilder.Update("问题迭代信息")
                    .Where("ID", model.ID)
                    .Column("名称", model.MC)
                    .Column("简码", dt.Rows[0]["简码"].ToString())
                    .Column("类型", model.LX)
                    .Column("渠道ID", model.QDID)
                    .Column("项目ID", model.XMID)
                    .Column("产品ID", model.CPID)
                    .Column("责任人ID", model.ZRRID)
                    .Column("开始日期", model.KSRJ)
                    .Column("结束日期", model.JSRJ)
                    .Column("描述", model.MS)
                     .Column("状态", model.ZT)
                    .Execute();
            }
        }

        public int Delete(string id)
        {
            return DeleteBulider.Delete("问题迭代信息").Where("id", id).Execute();
        }

        public string GetList(string cpid, int zt, string key, int start, int end)
        {
            string sql = @"select id,名称,类型,to_char(开始日期,'yyyy-MM-dd') 开始日期,to_char(结束日期,'yyyy-MM-dd') 结束日期,to_char(建立日期,'yyyy-MM-dd') 建立日期,进度,负责人姓名
                            From (Select Mx.*, Max(排序) Over(Order By 1) As Maxrow
                            From (Select Mx.*,
                            Row_Number() Over(Order By Mx.建立日期 Desc) As 排序
                            From (select d.* ,x.姓名 as 负责人姓名    
                            from 问题迭代信息 d left join 人员信息 x on d.责任人ID=x.id 
                            where d.名称 is not null ";
            if (zt != 0)
            {
                sql += $" and d.状态={zt} ";
            }
            if (!string.IsNullOrWhiteSpace(key))
            {
                sql += $@" and d.名称 like '%'||'{key}'||'%' ";
            }
            if (!string.IsNullOrWhiteSpace(cpid))
            {
                sql += $@" and d.产品ID='{cpid}'  ";
            }
            //状态根据日期判断
            //switch (zt)
            //{
            //    //未完成
            //    case 1:
            //        sql += $" and d.结束日期 >=TO_DATE('{DataBaseBuilder.GetDateTime.ToString("yyyy-MM-dd")}','yyyy-MM-dd') "; break;
            //    //未开始
            //    case 2:
            //        sql += $" and d.开始日期 >TO_DATE('{DataBaseBuilder.GetDateTime.ToString("yyyy-MM-dd")}','yyyy-MM-dd') "; break;
            //    //进行中
            //    case 3:
            //        sql += $" and d.开始日期 <=TO_DATE('{DataBaseBuilder.GetDateTime.ToString("yyyy-MM-dd")}','yyyy-MM-dd') and 结束日期 >=TO_DATE('{DataBaseBuilder.GetDateTime.ToString("yyyy-MM-dd")}','yyyy-MM-dd') "; break;
            //    //已关闭
            //    case 4:
            //        sql += $" and d.结束日期 <TO_DATE('{DataBaseBuilder.GetDateTime.ToString("yyyy-MM-dd")}','yyyy-MM-dd') "; break;
            //}
            sql += ") Mx) Mx) where 排序 >=:startrow And 排序 <=:endrow";
            var dt = SqlBuilder.SqlText(sql).Parameters("startrow", start).Parameters("endrow", end).Select();
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["开始日期"].ToDateTime() <= DataBaseBuilder.GetDateTime)
                {
                    if (dt.Rows[i]["结束日期"].ToDateTime() > DataBaseBuilder.GetDateTime)
                    {
                        decimal day = decimal.Parse((dt.Rows[i]["结束日期"].ToDateTime() - dt.Rows[i]["开始日期"].ToDateTime()).Days.ToString()) + decimal.Parse("1.0");
                        decimal currentday = decimal.Parse((DataBaseBuilder.GetDateTime - dt.Rows[i]["开始日期"].ToDateTime()).Days.ToString() + ".0") + decimal.Parse("1.0");
                        int count = (currentday / day * 100).ToString().LastIndexOf(".");
                        dt.Rows[i]["进度"] = (currentday / day * 100).ToString().Substring(0, count + 2);
                    }
                    else
                    {
                        dt.Rows[i]["进度"] = 100;
                    }
                }
                else
                {
                    dt.Rows[i]["进度"] = 0;
                }
            }
            return dt.ToEasyUIGridJson(dt.Rows.Count);
        }

        public DataTable GetDataById(string id)
        {
            return SqlBuilder.SqlText("select * from 问题迭代信息 where id=:id").Parameters("ID", id).Select();
        }
    }
}