everlight.eap/backend/DllEapDal/OFILM/PersonnelAttendanceDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapEntity.Dtos;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace DllEapDal.OFILM
{
    public class PersonnelAttendanceDal
    {
        private IDatabase db;
         

        public PersonnelAttendanceDal(IDatabase db)
        {
            this.db = db;
        }

        public AntdComplexTableDto Get(string name,string rate, string order, DateTime? start, DateTime? end, int pageIndex, int pageSize,ref int total)
        {
            DateTime starttime, endtime;
            if (!start.HasValue)
            {
                starttime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
            }
            else
            {
                starttime = start.Value;
            }
            if (!end.HasValue)
            {
                endtime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
            }
            else
            {
                endtime = end.Value;
            }
           int frist = (pageIndex - 1) * pageSize ;
            int last = frist + pageSize;
            List<string> list = new List<string>();
            string pdate = "";
            int s = 0;
            for (DateTime i = starttime; i <= endtime; i = i.AddDays(1))
            {
                if (SundayAsync(i).Result)
                {
                    continue;
                }
                s += 1;
                list.Add($@"TO_DATE('{i.ToString("yyyy-MM-dd 00:00:00")}', 'yyyy-mm-dd hh24:mi:ss')");
                var date = i.ToString("yyyy-MM-dd");
                pdate += $" ,max(CASE WHEN PDate=TO_DATE('{i.ToString("yyyy-MM-dd 00:00:00")}', 'yyyy-mm-dd hh24:mi:ss') THEN CLOCK_IN END) as \"{ date}\" ";
            }
            string js = string.Empty; ;
            string str = string.Empty;
            if (list != null && list.Count > 0)
            {
                 js = JsonConvert.SerializeObject(list).ToString().Replace("[", "(").Replace("]", ")").Replace("\"", "");
                 str = $@" and pdate in {js}";
            }
            
           
            

            string condition = string.Empty;
            if (!string.IsNullOrEmpty(name))
            {
                str += $@" and a.name like '%{name}%' ";
               
            }
            

            if (!string.IsNullOrEmpty(rate))
            {
                str += $@" and b.rate='{rate}' ";
                
            }
            string ss=string.Empty;
            
            if (!string.IsNullOrEmpty(order))
            {
                ss += $@"ORDER BY rate {order}";
            }
            
            string sql = $@"SELECT
	* 
FROM
	(
	SELECT
		t.*,
		ROWNUM AS rowno 
	FROM(select  a.name name,a.WORK_NUM WORK_NUM,b.rate rate{pdate}
from department_clock_in_off_543 a LEFT JOIN(SELECT

    WORK_NUM,
ROUND(COUNT(CLOCK_IN) / {s}, 2)*100 rate
FROM

    department_clock_in_off_543
WHERE

    PDATE IN {js}

    AND CLOCK_IN is NOT NULL

    GROUP BY WORK_NUM) b on a.WORK_NUM = b.WORK_NUM

    WHERE
    1=1 {str} GROUP BY a.WORK_NUM,a.name,b.rate {ss} )t)tt where tt.rowno between { frist } and {last}";
            string ssql = $@"select count(1)
            from department_clock_in_off_543 a LEFT JOIN(SELECT
            

                WORK_NUM,
            ROUND(COUNT(CLOCK_IN) / { s}, 2)*100 rate
FROM

    department_clock_in_off_543
WHERE

    PDATE IN { js}

            AND CLOCK_IN is NOT NULL

    GROUP BY WORK_NUM) b on a.WORK_NUM = b.WORK_NUM

    WHERE
    1 = 1 { str}
            ";
            total = Convert.ToInt32(db.FindObject(ssql));
            DataTable dt = db.FindTable(sql);
            AntdComplexTableDto data = new AntdComplexTableDto();
            var header = new List<AntdColumn>();
            if (dt != null && dt.Columns.Count > 0)
            {
                foreach (DataColumn item in dt.Columns)
                {
                    if(item.ColumnName!= "ROWNO")
                        header.Add(new AntdColumn { ColName = item.ColumnName });
                }

            }

            data.Columns = header;
            data.Datas = dt;
            return data;
        }
        private async Task<bool> SundayAsync(DateTime time)
        {
            /* var webClient = new System.Net.WebClient();
             var PostVars = new System.Collections.Specialized.NameValueCollection
             {
                 { "d", time.ToString("yyyyMMdd") }//参数
             };

                 var byteResult = await webClient.UploadValuesTaskAsync("http://tool.bitefu.net/jiari/", "POST", PostVars);//请求地址,传参方式,参数集合
                 var result = Encoding.UTF8.GetString(byteResult);//获取返回值
                 if (result == "1" || result == "2")
             {
                 return true;
             }
             else
             {
                 return false;
             }*/
            if (time.DayOfWeek==DayOfWeek.Sunday||time.DayOfWeek==DayOfWeek.Saturday)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}