using System;
using Dapper;
using HNWD.Pregrant.Model;
using System.Collections.Generic;
using System.Text;

namespace HNWD.Pregrant.DataAccess
{
    public class WD_StaffInfoDataAccess : GenericDataAccess<WD_StaffInfo>
    {
        private WD_StaffInfo wd_StaffInfo = null;

        public WD_StaffInfoExt ext = new WD_StaffInfoExt();

        private readonly static WD_StaffInfoDataAccess wd_StaffInfoDataAccess = new WD_StaffInfoDataAccess(null);
        private readonly static object lockobj = new object();
        public static WD_StaffInfoDataAccess GetInstance()
        {
            lock (lockobj) 
            {
                return wd_StaffInfoDataAccess;
            }
        }

        private WD_StaffInfoDataAccess(WD_StaffInfo wd_StaffInfo)
        {
            this.wd_StaffInfo = wd_StaffInfo;
       }

        public List<WD_StaffInfo> Query()
        {
 
            string strSql = " select {0} from [WD_StaffInfo] where id = " +  this.wd_StaffInfo.ID;

            ext.messageDataList = this.Query(strSql, new WD_StaffInfo());
            ext.messageDataList.ForEach(f => f.bSuccess = this.bSuccess);
            ext.messageDataList.ForEach(f => f.Code = this.Code);
            ext.messageDataList.ForEach(f => f.Message = this.Message);
            ext.messageDataList.ForEach(f => f.Rows = this.Rows);
            ext.bSuccess = this.bSuccess;
            ext.Code = this.Code;
            ext.Message = this.Message;
            ext.Rows = this.Rows;

            return ext.messageDataList;

        }

        public List<WD_StaffInfo> QueryAll()
        {

            string strSql = " select {0} from [WD_StaffInfo] ";

            ext.messageDataList = this.Query(strSql, new WD_StaffInfo());
            ext.messageDataList.ForEach(f => f.bSuccess = this.bSuccess);
            ext.messageDataList.ForEach(f => f.Code = this.Code);
            ext.messageDataList.ForEach(f => f.Message = this.Message);
            ext.messageDataList.ForEach(f => f.Rows = this.Rows);
            ext.bSuccess = this.bSuccess;
            ext.Code = this.Code;
            ext.Message = this.Message;
            ext.Rows = this.Rows;

            return ext.messageDataList;

        }


        
        private WD_StaffInfoExt QueryMAXID()
        {

            string strSql = " select top 1 * from [WD_StaffInfo] order by ID desc ";

            ext.messageDataList = this.Query(strSql, new WD_StaffInfo());
            ext.messageDataList.ForEach(f => f.bSuccess = this.bSuccess);
            ext.messageDataList.ForEach(f => f.Code = this.Code);
            ext.messageDataList.ForEach(f => f.Message = this.Message);
            ext.messageDataList.ForEach(f => f.Rows = this.Rows);
            ext.bSuccess = this.bSuccess;
            ext.Code = this.Code;
            ext.Message = this.Message;
            ext.Rows = this.Rows;

            return ext;

        }



        public void Business(List<WD_StaffInfo> il, List<WD_StaffInfo> ul, List<WD_StaffInfo> dl)
        {
            StringBuilder sb_itemp = new StringBuilder();
            sb_itemp.Append(" INSERT INTO [WD_StaffInfo]( " );
            sb_itemp.Append(" [STAFF_ID],[STAFF_PASSWORDCARD],[STAFF_NAME],[STAFF_NAME_PY],[STAFF_TYPE], " );
            sb_itemp.Append(" [STAFF_SEX],[STAFF_BIRTHDAY],[STAFF_IDCARD],[STAFF_WORKDATE],[STAFF_PICTURE], ");
            sb_itemp.Append(" [STAFF_WORKTEL],[STAFF_MOBILE],[STAFF_CONTENT],[STAFF_DUTY],[STAFF_DATETIME]  ");
            sb_itemp.Append(" ) values (  ");
            sb_itemp.Append(" '{0}' , '{1}' , '{2}' ,'{3}' ,'{4}' , ");
            sb_itemp.Append(" '{5}' , '{6}' , '{7}' ,'{8}' ,'{9}' , ");
            sb_itemp.Append(" '{10}' , '{11}' , '{12}' ,'{13}' ,'{14}'  ");
            sb_itemp.Append(" ) ");


            StringBuilder sb_utemp = new StringBuilder();
            sb_utemp.Append(" UPDATE  [WD_StaffInfo] SET  " );
            sb_utemp.Append(" [STAFF_ID] = '{0}',[STAFF_PASSWORDCARD] = '{1}',[STAFF_NAME] = '{2}',[STAFF_NAME_PY] = '{3}',[STAFF_TYPE] = '{4}', " );
            sb_utemp.Append(" [STAFF_SEX] = '{5}',[STAFF_BIRTHDAY] = '{6}',[STAFF_IDCARD] = '{7}',[STAFF_WORKDATE] = '{8}',[STAFF_PICTURE] = '{9}', ");
            sb_utemp.Append(" [STAFF_WORKTEL] = '{10}',[STAFF_MOBILE] = '{11}',[STAFF_CONTENT] = '{12}',[STAFF_DUTY] = '{13}',[STAFF_DATETIME] = '{14}'  ");
            sb_utemp.Append(" WHERE ID = '{15}' "); 

            StringBuilder sb_dtemp = new StringBuilder();
            sb_dtemp.Append( " delete from [WD_StaffInfo] where [ID]= '{0}'" ); 
 
            StringBuilder sb_i = new StringBuilder();

            foreach (WD_StaffInfo wd in il)
            {
                WD_StaffInfoExt wd_extmax = this.QueryMAXID();
                if(wd_extmax.bSuccess && wd_extmax.Rows == 0)
                {
                    wd.STAFF_PICTURE = "Pic/1.png";
                }
                else
                {
                    wd.STAFF_PICTURE = "Pic/" + wd_extmax.messageDataList[0].ID + ".png";
                }

                sb_i.AppendFormat(sb_itemp.ToString(), 
                    wd.STAFF_ID , 
                    wd.STAFF_PASSWORDCARD ,
                    wd.STAFF_NAME ,
                    wd.STAFF_NAME_PY ,
                    wd.STAFF_TYPE ,
                    wd.STAFF_SEX ,
                    wd.STAFF_BIRTHDAY ,
                    wd.STAFF_IDCARD ,
                    wd.STAFF_WORKDATE ,
                    wd.STAFF_PICTURE ,
                    wd.STAFF_WORKTEL ,
                    wd.STAFF_MOBILE ,
                    wd.STAFF_CONTENT ,
                    wd.STAFF_DUTY ,
                    wd.STAFF_DATETIME

                    );
            }


            StringBuilder sb_u = new StringBuilder();

            foreach (WD_StaffInfo wd in ul)
            {
                sb_u.AppendFormat(sb_utemp.ToString(), 
                    wd.STAFF_ID , 
                    wd.STAFF_PASSWORDCARD ,
                    wd.STAFF_NAME ,
                    wd.STAFF_NAME_PY ,
                    wd.STAFF_TYPE ,
                    wd.STAFF_SEX ,
                    wd.STAFF_BIRTHDAY ,
                    wd.STAFF_IDCARD ,
                    wd.STAFF_WORKDATE ,
                    wd.STAFF_PICTURE ,
                    wd.STAFF_WORKTEL ,
                    wd.STAFF_MOBILE ,
                    wd.STAFF_CONTENT ,
                    wd.STAFF_DUTY ,
                    wd.STAFF_DATETIME,
                    wd.ID
                    );
            }

            StringBuilder sb_d = new StringBuilder();

            foreach (WD_StaffInfo wd in dl)
            {
                sb_d.AppendFormat(sb_dtemp.ToString(), wd.ID);
            }

            this.Execute(sb_i.ToString(), sb_u.ToString(), sb_d.ToString());

        } 
 
    }
}