工事管理システム製品化対応

                string[] strArray = strwork.Split('/');

                Mounth = CommonMotions.cnvInt(strArray[0]);

                Days = CommonMotions.cnvInt(strArray[1]);

        #region 指定期・指定年度の期首日・期末日を取得する

        /// <summary>

        /// 指定期・指定年度の期首日・期末日を取得する

        /// </summary>

        /// <param name="nYear"></param>

        /// <param name="bOpen"></param>

        /// <param name="bPeriod"></param>

        /// <returns></returns>

        public static DateTime GetOpeningEndDate(int nYear, bool bOpen = true, bool bPeriod = true)

        {

            try

            {

                DateTime dtRet = DateTime.Today;

                string strwork = string.Empty;

                int TargetYear = DateTime.Today.Year;

                if (bPeriod)

                {   // 営業期

                    strwork = CommonMotions.SystemMasterData.BusinessBeginningDate;

                    TargetYear = BusinessPeriodToConstructionYears(nYear);

                }

                else

                {   // 工事年度

                    strwork = CommonMotions.SystemMasterData.ConstructionBeginningDate;

                    TargetYear = nYear;

                }

                // 月・日分割

                string[] DateArray = strwork.Split('/');

                // 年月日作成

                dtRet = new DateTime(TargetYear, int.Parse(DateArray[0]), int.Parse(DateArray[1]));

                // 期末取得時

                if (!bOpen)

                {

                    dtRet = dtRet.AddYears(1).AddDays(-1);

                }

                return dtRet;

            }

            catch (System.Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

                return DateTime.Today;

            }

        }

        #endregion

                strSQL.Append(" ) AS SAL");

        public static int s_SystemVersion = 71;

                frm.DivisionCode = (int)DivisionMaster.DivisionMasterCodeDefine.OrderDivision;

                frm.DivisionCode = (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses;

        #region 定数

        /// <summary>

        /// 区分マスタ区分

        /// </summary>

        public enum DivisionMasterCodeDefine

        {

            /// <summary>

            /// 発注者区分登録

            /// </summary>

            OrderDivision = 1,

            /// <summary>

            /// 工事種別登録

            /// </summary>

            ConstructionClass = 2,

            /// <summary>

            /// 工事経費名称登録

            /// </summary>

            ConstructionExpenses = 3,

        }

        /// <summary>

        /// 区分マスタ区分キー

        /// </summary>

        public static Dictionary<int, string> DivisionMasterCode = new Dictionary<int, string>(){

                                                                                {1, "発注者区分登録"},

                                                                                {2, "工事種別登録"},

                                                                                {3, "工事経費名称登録"},

                                                                                };

        #endregion

        private void cmbDepartment_SelectedIndexChanged(object sender, EventArgs e)

        private void cmbConstructionPerson_SelectedIndexChanged(object sender, EventArgs e)

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle27 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle28 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle39 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle29 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle30 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle31 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle32 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle33 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle34 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle35 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle36 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle37 = new System.Windows.Forms.DataGridViewCellStyle();

            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle38 = new System.Windows.Forms.DataGridViewCellStyle();

            this.cmbConstructionPerson.SelectedIndexChanged += new System.EventHandler(this.cmbConstructionPerson_SelectedIndexChanged);

            this.cmbDepartment.SelectedIndexChanged += new System.EventHandler(this.cmbDepartment_SelectedIndexChanged);

            dataGridViewCellStyle27.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(255)))), ((int)(((byte)(192)))));

            this.dgvMaster.AlternatingRowsDefaultCellStyle = dataGridViewCellStyle27;

            dataGridViewCellStyle28.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            dataGridViewCellStyle28.BackColor = System.Drawing.SystemColors.Control;

            dataGridViewCellStyle28.Font = new System.Drawing.Font("ＭＳ 明朝", 11.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(128)));

            dataGridViewCellStyle28.ForeColor = System.Drawing.SystemColors.WindowText;

            dataGridViewCellStyle28.SelectionBackColor = System.Drawing.SystemColors.Highlight;

            dataGridViewCellStyle28.SelectionForeColor = System.Drawing.SystemColors.HighlightText;

            dataGridViewCellStyle28.WrapMode = System.Windows.Forms.DataGridViewTriState.True;

            this.dgvMaster.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle28;

            dataGridViewCellStyle39.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;

            dataGridViewCellStyle39.BackColor = System.Drawing.SystemColors.Window;

            dataGridViewCellStyle39.Font = new System.Drawing.Font("ＭＳ 明朝", 11.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(128)));

            dataGridViewCellStyle39.ForeColor = System.Drawing.SystemColors.ControlText;

            dataGridViewCellStyle39.SelectionBackColor = System.Drawing.Color.LightSeaGreen;

            dataGridViewCellStyle39.SelectionForeColor = System.Drawing.SystemColors.HighlightText;

            dataGridViewCellStyle39.WrapMode = System.Windows.Forms.DataGridViewTriState.False;

            this.dgvMaster.DefaultCellStyle = dataGridViewCellStyle39;

            dataGridViewCellStyle29.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column1.DefaultCellStyle = dataGridViewCellStyle29;

            dataGridViewCellStyle30.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column9.DefaultCellStyle = dataGridViewCellStyle30;

            dataGridViewCellStyle31.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column5.DefaultCellStyle = dataGridViewCellStyle31;

            dataGridViewCellStyle32.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column10.DefaultCellStyle = dataGridViewCellStyle32;

            dataGridViewCellStyle33.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column6.DefaultCellStyle = dataGridViewCellStyle33;

            dataGridViewCellStyle34.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;

            this.Column4.DefaultCellStyle = dataGridViewCellStyle34;

            dataGridViewCellStyle35.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column7.DefaultCellStyle = dataGridViewCellStyle35;

            dataGridViewCellStyle36.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column8.DefaultCellStyle = dataGridViewCellStyle36;

            dataGridViewCellStyle37.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column3.DefaultCellStyle = dataGridViewCellStyle37;

            dataGridViewCellStyle38.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;

            this.Column2.DefaultCellStyle = dataGridViewCellStyle38;

                strSQL += string.Format(" LEFT JOIN DIVISIONMASTER E1 ON E1.DIVISIONCODE = {0} AND E1.NAMECODE = E.NAMECODE", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                strSQL += string.Format(" LEFT JOIN DIVISIONMASTER F1 ON F1.DIVISIONCODE = {0} AND F1.NAMECODE = F.NAMECODE", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                strSQL += string.Format(" LEFT JOIN DIVISIONMASTER G1 ON G1.DIVISIONCODE = {0} AND G1.NAMECODE = G.NAMECODE", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                strSQL += string.Format(" WHERE A.DIVISIONCODE = {0}", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                string strSQL = dvDB.CreatePrimarykeyString((int)DivisionMaster.DivisionMasterCodeDefine.OrderDivision) + " And DeleteFlg = 0 ORDER BY DISPLAYORDER";

                strSQL += string.Format(" WHERE A.DIVISIONCODE = {0}", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                                        , (int)DivisionMaster.DivisionMasterCodeDefine.OrderDivision

                strSQL += string.Format(" WHERE A.DIVISIONCODE = {0}", (int)DivisionMaster.DivisionMasterCodeDefine.ConstructionExpenses);

                int DivCode = DivisionMaster.DivisionMasterCode.First(x => x.Value.Equals("工事経費名称登録")).Key;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Collections;

using System.Diagnostics;

using System.Threading;

using log4net;

using log4net.Appender;

using log4net.Repository.Hierarchy;

using ProcessManagement.Common;

using ProcessManagement.DB.IOAccess;

using ProcessManagement.DataModel;

using ProcessManagement.DB.Core;

using ProcessManagement.Forms.CustomControls;

using ProcessManagement.Forms.ControlsAction;

using ProcessManagement.Forms.SubForms;

//*---------------------------- 台帳データ取得SQL作成クラス -------------------*

//  2017/08/21  Ver1.0.0.0      Create Source           

//

//

//

//*----------------------------------------------------------------------------*

namespace ProcessManagement.Forms.DataEntry

{

    /// <summary>

    /// 台帳データ取得SQL作成クラス

    /// </summary>

    public static class CreateLedgerSQL

    {

        #region ログ定義

        /// <summary>

        /// log4netログを使用する

        /// </summary>

        private static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        #endregion

        #region 期間データ取得順

        /// <summary>

        /// 期間データ取得順

        /// </summary>

        public enum GetPersonTerm

        {

            DepartmentCode = 0,

            /// <summary>

            /// 担当者コード

            /// </summary>

            PersonCode,

            /// <summary>

            /// 担当者名

            /// </summary>

            Personname,

            /// <summary>

            /// 表示順

            /// </summary>

            Displayorder,

            /// <summary>

            /// 月額給与

            /// </summary>

            MonthlySalary,

            /// <summary>

            /// 契約工期開始

            /// </summary>

            StartDate,

            /// <summary>

            /// 契約工期終了

            /// </summary>

            CompDate,

            /// <summary>

            /// 雇用開始日付

            /// </summary>

            HireStartDays,

            /// <summary>

            /// 雇用終了日付

            /// </summary>

            HireCompDays,

        }

        #endregion

        #region 部署内の担当者コードを一覧で返す

        /// <summary>

        /// 部署内の担当者コードを一覧で返す

        /// </summary>

        public static int GetPersonInDepartment(int BusinessPeriod, int DepartmentCode, int PersonCode, ref ArrayList TargetList)

        {

            IOMPersonInCharge PersonDB = new IOMPersonInCharge();

            try

            {

                int NotOrder = CommonDefine.ProjectsStatus.First(x => x.Value.Equals("非 受 注")).Key;

                StringBuilder strSQL = new StringBuilder();

                DateTime dtDefaultStart = DateTime.MinValue;

                DateTime dtDefaultEnd = DateTime.MinValue;

                CreateDefaultStartAndEndDate(BusinessPeriod, ref dtDefaultStart, ref dtDefaultEnd);

                strSQL.Append("Select");

                strSQL.Append(" Person.DepartmentCode");

                strSQL.Append(", Person.PersonCode");

                strSQL.Append(", Person.PersonName");

                strSQL.Append(", Person.DisplayOrder");

                strSQL.Append(", Person.MonthlySalary");

                strSQL.Append(", Detail.SDate");

                strSQL.Append(", Detail.EDate");

                strSQL.Append(", Person.StartDate");

                strSQL.Append(", Person.EndDate");

                strSQL.Append(" From");

                strSQL.Append(" personinchargemaster As Person");

                strSQL.Append(" Left Join (Select");

                strSQL.Append(" Ledger.PersonCode AS PersonCode");

                strSQL.Append(" , MIN(Ledger.Start0) As SDate");

                strSQL.Append(" , MAX(Ledger.Comp0) AS EDate");

                strSQL.Append(" From");

                strSQL.Append(" (Select");

                strSQL.Append(" LDetail.CompanyCode As PersonCode");

                strSQL.Append(", Ledger.ConstructionStart As Start0");

                strSQL.Append(", Ledger.ConstructionEnd As Comp0");

                strSQL.Append(", LDetail.GROUPCOUNT");

                strSQL.Append(", LDetail.ConstructionCode");

                strSQL.Append(" From");

                strSQL.Append(" constructionledgerdetail As LDetail");

                strSQL.Append(" Inner Join constructionledger As Ledger On Ledger.ConstructionCode = LDetail.ConstructionCode");

                strSQL.Append(", constructionbaseinfo As Base");

                strSQL.Append(" Where");

                strSQL.AppendFormat(" LDetail.GroupCount IN ({0}, {1}, {2})"

                                , (int)FrmConstructionLedger.DataGroup.Instructor

                                , (int)FrmConstructionLedger.DataGroup.Assistant

                                , (int)FrmConstructionLedger.DataGroup.Payroll);

                strSQL.Append(" And LDetail.CompanyCode <> 0");

                strSQL.Append(" And Base.ConstructionCode = LDetail.ConstructionCode");

                strSQL.AppendFormat(" And Base.ConstructionStatusFlg <> {0}", NotOrder);

                strSQL.AppendFormat(" And Base.ConstructionPeriod = {0}) As Ledger", BusinessPeriod);

                strSQL.Append(" Group By Ledger.PersonCode) As Detail");

                strSQL.Append(" ON Detail.PersonCode = Person.PersonCode");

                strSQL.AppendFormat(" Where Person.LEDGERFLG = {0}", (int)CommonDefine.PersonLedgerDivNo.CalcTarget);

                if (CommonMotions.LoginUserData.PersonCode != CommonDefine.AdminCode)

                {

                    strSQL.Append(" AND Person.DEPARTMENTCODE IN (select PDep.DepartmentCode from PersonDepartmentMaster AS PDep");

                    strSQL.AppendFormat(" WHERE PDep.PersonCode = {0})", CommonMotions.LoginUserData.PersonCode);

                }

                // 工事台帳データの部署だけが対象

                if (DepartmentCode == 0)

                {

                    strSQL.Append(" AND Person.DEPARTMENTCODE IN");

                    strSQL.Append(" (SELECT AA.DEPARTMENTCODE FROM (SELECT A.DEPARTMENTCODE, A.DEPARTMENTSTRING, A.DISPLAYORDER, COUNT(*) CNT");

                    strSQL.Append(" FROM DEPARTMENTMASTER A, PERSONINCHARGEMASTER B, CONSTRUCTIONBASEINFO C , CONSTRUCTIONLEDGERDETAIL D");

                    strSQL.Append(" WHERE A.DELETEFLG = 0");

                    strSQL.Append(" AND A.DEPARTMENTCODE = B.DEPARTMENTCODE");

                    strSQL.Append(" AND B.PERSONCODE = C.CONSTRUCTIONPERSONCODE");

                    strSQL.Append(" AND D.CONSTRUCTIONCODE = C.CONSTRUCTIONCODE");

                    strSQL.Append(" GROUP BY A.DEPARTMENTCODE, A.DEPARTMENTSTRING, A.DISPLAYORDER) AA)");

                }

                else

                {

                    // 部署コード選択時は部署を検索条件へ入れる

                    strSQL.AppendFormat(" AND Person.DEPARTMENTCODE = {0}", DepartmentCode);

                }

                if (PersonCode != 0) strSQL.AppendFormat(" AND Person.PERSONCODE = {0}", PersonCode);

                strSQL.AppendFormat(" And ((DATE(Person.StartDate) <= DATE ('{0}') And DATE (Person.EndDate) = DATE ('{1}'))"

                                    , dtDefaultStart.ToShortDateString()

                                    , DateTime.MinValue.ToShortDateString());

                strSQL.AppendFormat(" Or (DATE('{0}') <= DATE(Person.StartDate) And DATE(Person.StartDate) <= DATE('{1}'))"

                                    , dtDefaultStart.ToShortDateString()

                                    , dtDefaultEnd.ToShortDateString());

                strSQL.AppendFormat(" Or (DATE('{0}') <= DATE(Person.EndDate) And DATE(Person.EndDate) <= DATE('{1}')))"

                                    , dtDefaultStart.ToShortDateString()

                                    , dtDefaultEnd.ToShortDateString());

                strSQL.AppendFormat(" ORDER BY Person.EmployeeClassFlg, Person.DISPLAYORDER");

                if (!PersonDB.ExecuteReader(strSQL.ToString(), ref TargetList)) return 0;

                return TargetList.Count;

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

                return 0;

            }

            finally

            {

                PersonDB.close(); PersonDB = null;

            }

        }

        #endregion

        #region デフォルトの期首日・期末日を求める

        /// <summary>

        /// デフォルトの期首日・期末日を求める

        /// </summary>

        /// <param name="dtStart"></param>

        /// <param name="dtEnd"></param>

        public static void CreateDefaultStartAndEndDate(int BusinessPeriod, ref DateTime dtStart, ref DateTime dtEnd)

        {

            try

            {

                // 当期期首年月日を取得

                int iYear = 0;

                int month = 0;

                int day = 0;

                bool bPeriod = false;

                if (CommonMotions.SystemMasterData.ConstructionNoBase == (int)CommonDefine.SystemConstructionNoBase.BusinessPeriod)

                {   // 営業期

                    iYear = CommonMotions.PeriodCountToYear(BusinessPeriod);

                    bPeriod = true;

                }

                else

                {   // 工事年度

                    iYear = CommonMotions.SystemMasterData.ConstructionYear;

                }

                CommonMotions.GetBeginningMonthDay(bPeriod, ref month, ref day);

                dtStart = new DateTime(iYear, month, day);

                dtEnd = dtStart.AddYears(1).AddDays(-1);     // 終了は＋1年－1日

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

            }

        }

        #endregion

        #region 計算開始・終了日のチェック入れ替え

        /// <summary>

        /// 計算開始・終了日のチェック入れ替え

        /// </summary>

        public static void CalcStartCompDate(int BusinessPeriod,

                                        DateTime dtDefaultStart, DateTime dtDefaultEnd,

                                        DateTime HireStartDate, DateTime HireCompDate,

                                        ref DateTime StartDate, ref DateTime CompDate)

        {

            try

            {

                if (StartDate == DateTime.MinValue)

                {

                    StartDate = dtDefaultStart;

                }

                else if (dtDefaultStart <= HireStartDate && HireStartDate <= dtDefaultEnd)

                {

                    StartDate = HireStartDate;

                }

                else if (dtDefaultStart < StartDate)

                {

                    StartDate = dtDefaultStart;

                }

                // 最大日が今日より小さい場合以外は処理しない

                if (CompDate.Date > DateTime.Today.Date) return;

                if (BusinessPeriod != CommonMotions.SystemMasterData.BusinessPeriod)

                {   // 今期以外はは期末

                    CompDate = dtDefaultEnd;

                    // 退社日が期内ならば終了を退社日にする

                    if (dtDefaultStart < HireCompDate && HireCompDate < dtDefaultEnd) CompDate = HireCompDate;

                }

                else

                {   // 以外は今日

                    CompDate = DateTime.Today.Date;

                    // 退社日が期内ならば終了を退社日にする

                    if (dtDefaultStart < HireCompDate && HireCompDate < dtDefaultEnd) CompDate = HireCompDate;

                }

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}", ex.Message);

            }

        }

        #endregion

        #region 工事詳細台帳より振分給与金額・粗利益を取得する

        /// <summary>

        /// 工事詳細台帳より振分給与金額・粗利益を取得する

        /// </summary>

        public static void ProfitGeneral(IOConstructionLedger LedgerDB, int ConstructionPeriod,

                                            int DepartmentCode, int PersonCode, ref int OnSalary)

        {

            try

            {

                // 部署コード取得

                int NotOrder = CommonDefine.ProjectsStatus.First(x => x.Value.Equals("非 受 注")).Key;

                // 振分給与金額取得

                StringBuilder strSQL = new StringBuilder();

                //strSQL.Append("SELECT SUM(C.EXECUTIONAMOUNT) FROM");

                strSQL.Append("SELECT SUM(C2.PaymentAmount) FROM");

                strSQL.Append(" CONSTRUCTIONLEDGER P");

                strSQL.Append(", CONSTRUCTIONBASEINFO B");

                strSQL.Append(", CONSTRUCTIONLEDGERDETAIL C");

                strSQL.Append(" LEFT JOIN constructionledgerexcute C2");

                strSQL.Append(" ON C2.ConstructionCode = C.ConstructionCode");

                strSQL.Append(" And C2.GroupCount = C.GroupCount");

                strSQL.Append(" And C2.LineCount = C.LineCount");

                strSQL.Append(", PERSONINCHARGEMASTER D");

                // 振分無では無い

                strSQL.Append(" WHERE P.CONSTRUCTIONCODE IN (SELECT A.CONSTRUCTIONCODE FROM CONSTRUCTIONLEDGERDETAIL A");

                strSQL.AppendFormat(" WHERE A.GROUPCOUNT = {0} AND (A.SALARYFLG = {1} OR A.SALARYFLG = {2}))"

                                        , (int)FrmConstructionLedger.DataGroup.Payroll

                                        , (int)CommonDefine.SalaryDevision.All

                                        , (int)CommonDefine.SalaryDevision.DaysInput);

                strSQL.Append(" AND P.CONSTRUCTIONCODE = B.CONSTRUCTIONCODE");

                strSQL.AppendFormat(" AND B.CONSTRUCTIONPERIOD = {0}", ConstructionPeriod);

                strSQL.AppendFormat(" AND B.CONSTRUCTIONSTATUSFLG != {0}", NotOrder);

                strSQL.Append(" AND C.CONSTRUCTIONCODE = P.CONSTRUCTIONCODE");

                strSQL.AppendFormat(" AND C.GROUPCOUNT = {0}", (int)FrmConstructionLedger.DataGroup.Payroll);

                strSQL.Append(" AND D.PERSONCODE = C.CompanyCode");

                if (CommonMotions.LoginUserData.PersonCode != CommonDefine.AdminCode)

                {

                    strSQL.Append(" AND D.DEPARTMENTCODE IN (select PDep.DepartmentCode from PersonDepartmentMaster AS PDep");

                    strSQL.AppendFormat(" WHERE PDep.PersonCode = {0})", CommonMotions.LoginUserData.PersonCode);

                }

                if (DepartmentCode != 0) strSQL.AppendFormat(" AND D.DEPARTMENTCODE = {0}", DepartmentCode);

                if (PersonCode != 0) strSQL.AppendFormat(" AND D.PERSONCODE = {0}", PersonCode);

                ArrayList arList = new ArrayList();

                if (!LedgerDB.ExecuteReader(strSQL.ToString(), ref arList)) return;

                object[] objSalary = (object[])arList[0];

                OnSalary = CommonMotions.cnvInt(objSalary[0]);

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

            }

        }

        #endregion 

        #region 担当者毎割当無費目毎の経費を取得する

        /// <summary>

        /// 担当者毎割当無費目毎の経費を取得する

        /// </summary>

        public static void GetPersonExpensesCostValue(IOCostDataOfPerson CostDB,

                                                        int DepartmentCode, int PersonCode,

                                                        DateTime dtStart, DateTime dtEnd,

                                                        ref int[] CostValue)

        {

            try

            {

                // 担当者毎経費データよりどこの工事にも所属していない経費を取得する

                StringBuilder strSQL = new StringBuilder();

                strSQL.Append(" SELECT");

                strSQL.Append(" PERSONCODE");

                strSQL.Append(", B.DATATYPE AS DATATYPE");

                strSQL.Append(", SUM(B.ENTRYPRICE) AS SUMPRICE");

                strSQL.Append(" FROM");

                strSQL.Append(" COSTDATAOFPERSON AS B");

                strSQL.Append(" WHERE");

                strSQL.Append(" B.PERSONCODE IN (SELECT BPerson.PERSONCODE FROM personinchargemaster As BPerson");

                strSQL.Append(" Where");

                StringBuilder strSub = new StringBuilder();

                if (DepartmentCode != 0) strSub.AppendFormat(" BPerson.DepartmentCode = {0})", DepartmentCode);

                if (PersonCode != 0)

                {

                    if (strSub.ToString().Length > 0) strSub.Append(" And");

                    strSub.AppendFormat(" BPerson.PersonCode = {0})", PersonCode);

                }

                strSQL.Append(strSub.ToString());

                strSQL.AppendFormat(" AND (DATE('{0}') <= DATE(B.ACTIONDATE)", dtStart.ToShortDateString());

                strSQL.AppendFormat(" AND DATE(B.ACTIONDATE) <= DATE('{0}'))", dtEnd.ToShortDateString());

                strSQL.Append(" AND B.CONSTRUCTIONCODE = 0");

                strSQL.Append(" GROUP BY B.DATATYPE");

                ArrayList arList = new ArrayList();

                CostDB.ExecuteReader(strSQL.ToString(), ref arList);

                // 取得結果を振り分ける

                foreach (object[] wrkobj in arList)

                {

                    for (int i = 1; i < CommonDefine.CostDataNoString.Length; i++)

                    {

                        if (CommonMotions.cnvInt(wrkobj[1]) == i)

                        {

                            CostValue[i] += CommonMotions.cnvInt(wrkobj[2]);

                            break;

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

            }

        }

        #endregion

        #region 期間データ取得順 ※旧バージョン

        /// <summary>

        /// 期間データ取得順 ※旧バージョン

        /// </summary>

        private enum GetPersonTermOLD

        {

            DepartmentCode = 0,

            /// <summary>

            /// 担当者コード

            /// </summary>

            PersonCode,

            /// <summary>

            /// 担当者名

            /// </summary>

            Personname,

            /// <summary>

            /// 表示順

            /// </summary>

            Displayorder,

            /// <summary>

            /// 月額給与

            /// </summary>

            MonthlySalary,

            /// <summary>

            /// 雇用開始日付

            /// </summary>

            HireStartDays,

            /// <summary>

            /// 雇用終了日付

            /// </summary>

            HireCompDays,

            /// <summary>

            /// 担当者契約工期開始

            /// </summary>

            MainStart1,

            /// <summary>

            /// 担当者契約工期延長開始

            /// </summary>

            MainStart2,

            /// <summary>

            /// 副担当者契約工期開始

            /// </summary>

            AssistStart1,

            /// <summary>

            /// 副担当者契約工期延長開始

            /// </summary>

            AssistStart2,

            /// <summary>

            /// 指導員契約工期開始

            /// </summary>

            InstStart1,

            /// <summary>

            /// 指導員契約工期延長開始

            /// </summary>

            InstStart2,

            /// <summary>

            /// 担当者契約工期終了

            /// </summary>

            MainEnd1,

            /// <summary>

            /// 担当者契約工期延長終了

            /// </summary>

            MainEnd2,

            /// <summary>

            /// 副担当者契約工期終了

            /// </summary>

            AssistEnd1,

            /// <summary>

            /// 副担当者契約工期延長終了

            /// </summary>

            AssistEnd2,

            /// <summary>

            /// 指導員契約工期終了

            /// </summary>

            InstEnd1,

            /// <summary>

            /// 指導員契約工期延長終了

            /// </summary>

            InstEnd2,

            /// <summary>

            /// 担当者金額集計

            /// </summary>

            MainSumAmount,

            /// <summary>

            /// 副担当者金額集計

            /// </summary>

            AssistSumAmount,

            /// <summary>

            /// 指導員金額集計

            /// </summary>

            InstSumAmount,

            /// <summary>

            /// 担当者データ件数

            /// </summary>

            MainDataCount,

            /// <summary>

            /// 副担当者データ件数

            /// </summary>

            AssistDataCmount,

            /// <summary>

            /// 指導員データ件数

            /// </summary>

            InstDataCmount,

        }

        #endregion

        #region 部署内の担当者コードを一覧で返す　※旧バージョン

        /// <summary>

        /// 部署内の担当者コードを一覧で返す

        /// </summary>

        /// <param name="DepartmentCode">部署コード</param>

        /// <param name="PersonCodeList">担当者コード一覧</param>

        /// <returns>データ件数</returns>

        public static int GetPersonInDepartment_OLD(int BusinessPeriod, int DepartmentCode, int PersonCode, ref ArrayList TargetList)

        {

            IOMPersonInCharge PersonDB = new IOMPersonInCharge();

            try

            {

                int NotOrder = CommonDefine.ProjectsStatus.First(x => x.Value.Equals("非 受 注")).Key;

                StringBuilder strSQL = new StringBuilder();

                strSQL.Append("SELECT P.PERSONCODE, P.PERSONNAME, P.DISPLAYORDER, P.MonthlySalary,");

                strSQL.Append(" P.STARTDATE, P.ENDDATE,");

                strSQL.Append(" MIN(C.CONSTRUCTIONPERIODSTART) M1, MIN(C.CONSTRUCTIONPERIODSTART2) M2,");

                strSQL.Append(" MIN(D.CONSTRUCTIONPERIODSTART) S1, MIN(D.CONSTRUCTIONPERIODSTART2) S2,");

                strSQL.Append(" MIN(E.CONSTRUCTIONPERIODSTART) I1, MIN(E.CONSTRUCTIONPERIODSTART2) I2,");

                strSQL.Append(" MAX(C.CONSTRUCTIONPERIODEND) M3, MAX(C.CONSTRUCTIONPERIODEND2) M4,");

                strSQL.Append(" MAX(D.CONSTRUCTIONPERIODEND) S3, MAX(D.CONSTRUCTIONPERIODEND2) S4,");

                strSQL.Append(" MAX(E.CONSTRUCTIONPERIODEND) I3, MAX(E.CONSTRUCTIONPERIODEND2) I4,");

                strSQL.Append(" SUM(C.EXECUTIONAMOUNT) M5, SUM(D.EXECUTIONAMOUNT) S5, SUM(E.EXECUTIONAMOUNT) I5,");

                strSQL.Append(" COUNT(C.EXECUTIONAMOUNT) M6, COUNT(D.EXECUTIONAMOUNT) S6, COUNT(E.EXECUTIONAMOUNT) I6");

                strSQL.Append(" FROM PERSONINCHARGEMASTER P");

                // 工事担当者のデータ取得

                strSQL.Append(" LEFT JOIN (SELECT C1.CONSTRUCTIONPERSONCODE, C1.CONSTRUCTIONSTATUSFLG, C1.CONSTRUCTIONPERIOD");

                strSQL.Append(", C1.CONSTRUCTIONPERIODSTART, C1.CONSTRUCTIONPERIODSTART2, C1.CONSTRUCTIONPERIODEND, C1.CONSTRUCTIONPERIODEND2, C2.EXECUTIONAMOUNT");

                strSQL.Append(" FROM CONSTRUCTIONBASEINFO C1, CONSTRUCTIONLEDGERDETAIL C2, CONSTRUCTIONLEDGER C3");

                strSQL.Append(" WHERE C2.CONSTRUCTIONCODE = C1.CONSTRUCTIONCODE");

                strSQL.AppendFormat(" AND C2.GROUPCOUNT = {0}", (int)FrmConstructionLedger.DataGroup.Payroll);

                strSQL.Append(" AND C3.CONSTRUCTIONCODE = C2.CONSTRUCTIONCODE) C");

                strSQL.Append(" ON C.CONSTRUCTIONPERSONCODE = P.PERSONCODE");

                strSQL.AppendFormat(" AND C.CONSTRUCTIONSTATUSFLG <> {0}", NotOrder);

                strSQL.AppendFormat(" AND C.CONSTRUCTIONPERIOD = {0}", BusinessPeriod);

                // 工事副担当者のデータ取得

                strSQL.Append(" LEFT JOIN (SELECT D1.CONSTRSUBPERSONCODE CONSTRUCTIONPERSONCODE, D1.CONSTRUCTIONSTATUSFLG, D1.CONSTRUCTIONPERIOD");

                strSQL.Append(", D1.CONSTRUCTIONPERIODSTART, D1.CONSTRUCTIONPERIODSTART2, D1.CONSTRUCTIONPERIODEND, D1.CONSTRUCTIONPERIODEND2, D2.EXECUTIONAMOUNT");

                strSQL.Append(" FROM CONSTRUCTIONBASEINFO D1, CONSTRUCTIONLEDGERDETAIL D2, CONSTRUCTIONLEDGER D3");

                strSQL.Append(" WHERE D2.CONSTRUCTIONCODE = D1.CONSTRUCTIONCODE");

                strSQL.AppendFormat(" AND D2.GROUPCOUNT = {0}", (int)FrmConstructionLedger.DataGroup.Assistant);

                strSQL.Append(" AND D3.CONSTRUCTIONCODE = D2.CONSTRUCTIONCODE) D");

                strSQL.Append(" ON D.CONSTRUCTIONPERSONCODE = P.PERSONCODE");

                strSQL.AppendFormat(" AND D.CONSTRUCTIONSTATUSFLG <> {0}", NotOrder);

                strSQL.AppendFormat(" AND D.CONSTRUCTIONPERIOD = {0}", BusinessPeriod);

                // 工事指導員のデータ取得

                strSQL.Append(" LEFT JOIN (SELECT E1.CONSTRUCTIONINSTRUCTOR CONSTRUCTIONPERSONCODE, E1.CONSTRUCTIONSTATUSFLG, E1.CONSTRUCTIONPERIOD");

                strSQL.Append(", E1.CONSTRUCTIONPERIODSTART, E1.CONSTRUCTIONPERIODSTART2, E1.CONSTRUCTIONPERIODEND, E1.CONSTRUCTIONPERIODEND2, E2.EXECUTIONAMOUNT");

                strSQL.Append(" FROM CONSTRUCTIONBASEINFO E1, CONSTRUCTIONLEDGERDETAIL E2, CONSTRUCTIONLEDGER E3");

                strSQL.Append(" WHERE E2.CONSTRUCTIONCODE = E1.CONSTRUCTIONCODE");

                strSQL.AppendFormat(" AND E2.GROUPCOUNT = {0}", (int)FrmConstructionLedger.DataGroup.Instructor);

                strSQL.Append(" AND E3.CONSTRUCTIONCODE = E2.CONSTRUCTIONCODE) E");

                strSQL.Append(" ON E.CONSTRUCTIONPERSONCODE = P.PERSONCODE");

                strSQL.AppendFormat(" AND E.CONSTRUCTIONSTATUSFLG <> {0}", NotOrder);

                strSQL.AppendFormat(" AND E.CONSTRUCTIONPERIOD = {0}", BusinessPeriod);

                strSQL.AppendFormat(" WHERE P.LEDGERFLG = {0}", (int)CommonDefine.PersonLedgerDivNo.CalcTarget);

                // 当期期首日・期末日を取得

                DateTime dtDefaultStart = DateTime.MinValue;

                DateTime dtDefaultEnd = DateTime.MinValue;

                CreateDefaultStartAndEndDate(BusinessPeriod, ref dtDefaultStart, ref dtDefaultEnd);

                strSQL.AppendFormat(" AND (DATE(P.STARTDATE) <= STR_TO_DATE('{0}','%Y/%m/%d')", dtDefaultEnd.ToShortDateString());

                strSQL.AppendFormat(" AND (STR_TO_DATE('{0}','%Y/%m/%d') <= DATE(P.ENDDATE)", dtDefaultStart.ToShortDateString());

                strSQL.AppendFormat(" OR STR_TO_DATE('{0}','%Y/%m/%d') = DATE(P.ENDDATE)))", DateTime.MinValue.ToShortDateString());

                if (CommonMotions.LoginUserData.PersonCode != CommonDefine.AdminCode)

                {

                    strSQL.Append(" AND P.DEPARTMENTCODE IN (select PDep.DepartmentCode from PersonDepartmentMaster AS PDep");

                    strSQL.AppendFormat(" WHERE PDep.PersonCode = {0})", CommonMotions.LoginUserData.PersonCode);

                }

                // 工事台帳データの部署だけが対象

                if (DepartmentCode == 0)

                {

                    strSQL.Append(" AND P.DEPARTMENTCODE IN");

                    strSQL.Append(" (SELECT TMP.DEPARTMENTCODE FROM (SELECT A.DEPARTMENTCODE, A.DEPARTMENTSTRING, A.DISPLAYORDER, COUNT(*) CNT");

                    strSQL.Append(" FROM DEPARTMENTMASTER A, PERSONINCHARGEMASTER B, CONSTRUCTIONBASEINFO C , CONSTRUCTIONLEDGERDETAIL D");

                    strSQL.Append(" WHERE A.DELETEFLG = 0");

                    strSQL.Append(" AND A.DEPARTMENTCODE = B.DEPARTMENTCODE");

                    strSQL.Append(" AND B.PERSONCODE = C.CONSTRUCTIONPERSONCODE");

                    strSQL.Append(" AND D.CONSTRUCTIONCODE = C.CONSTRUCTIONCODE");

                    strSQL.Append(" GROUP BY A.DEPARTMENTCODE, A.DEPARTMENTSTRING, A.DISPLAYORDER) TMP)");

                }

                else

                {

                    // 部署コード選択時は部署を検索条件へ入れる

                    strSQL.AppendFormat(" AND P.DEPARTMENTCODE = {0}", DepartmentCode);

                }

                if (PersonCode != 0) strSQL.AppendFormat(" AND P.PERSONCODE = {0}", PersonCode);

                strSQL.Append(" GROUP BY P.PERSONCODE, P.PERSONNAME, P.DISPLAYORDER, P.MonthlySalary, P.STARTDATE, P.ENDDATE");

                strSQL.Append(" ORDER BY P.DISPLAYORDER");

                ArrayList arList = new ArrayList();

                if (!PersonDB.ExecuteReader(strSQL.ToString(), ref arList)) return 0;

                DateTime stDate = DateTime.MaxValue;

                DateTime edDate = DateTime.MinValue;

                DateTime maxDate = DateTime.MinValue;

                foreach (object[] objRec in arList)

                {

                    stDate = DateTime.MaxValue;

                    edDate = DateTime.MinValue;

                    maxDate = DateTime.MinValue;

                    // 担当者コードを取得する

                    int Person = CommonMotions.cnvInt(objRec[(int)GetPersonTermOLD.PersonCode]);

                    DateTime wrkDate = DateTime.MinValue;

                    // 最小の開始日を取得する

                    //stDate = dtDefaultStart;

                    wrkDate = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.MainStart1]).Date;

                    if (wrkDate != DateTime.MinValue && wrkDate < stDate) stDate = wrkDate;

                    wrkDate = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.MainStart2]).Date;

                    if (wrkDate != DateTime.MinValue && wrkDate < stDate) stDate = wrkDate;

                    // 最大の終了日を取得する

                    //maxDate = dtDefaultEnd;

                    wrkDate = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.MainEnd1]).Date;

                    if (wrkDate != DateTime.MinValue && wrkDate > maxDate) maxDate = wrkDate;

                    wrkDate = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.MainEnd2]).Date;

                    if (wrkDate != DateTime.MinValue && wrkDate > maxDate) maxDate = wrkDate;

                    // 計算の終了日を取得する

                    //edDate = dtDefaultEnd;

                    // 月額給与を取得する

                    int Salary = CommonMotions.cnvInt(objRec[(int)GetPersonTermOLD.MonthlySalary]);

                    // 雇用開始・終了日を取得する

                    DateTime dtHireStDay = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.HireStartDays]);

                    DateTime dtHireEdDay = CommonMotions.cnvDate(objRec[(int)GetPersonTermOLD.HireCompDays]);

                    TargetList.Add(new object[]{Person,         // 担当者コード

                                                stDate,         // 開始日

                                                edDate,         // 終了日

                                                maxDate,        // 最大終了日

                                                Salary,         // 給与金額

                                                DateTime.Now,

                                                DateTime.Now,

                                                dtHireStDay,    // 雇用開始日

                                                dtHireEdDay});  // 雇用終了日

                }

                return arList.Count;

            }

            catch (Exception ex)

            {

                logger.ErrorFormat("システムエラー：{0}：{1}", CommonMotions.GetMethodName(), ex.Message);

                return 0;

            }

            finally

            {

                PersonDB.close(); PersonDB = null;

            }

        }

        #endregion

    }

}

        private int SetOtherCost(DateTime dtDefaultStart, DateTime dtDefaultEnd, ArrayList TargetList, IOCostDataOfPerson CostDB)

                DateTime stDate = DateTime.MaxValue;

                DateTime edDate = DateTime.MaxValue;

                // for (int ii = 0; ii < PersonCDArray.Count; ii++)

                foreach (object[] ObjRec in TargetList)

                    CreateLedgerSQL.CalcStartCompDate((int)numUDConstPro.Value,

                                                        dtDefaultStart, dtDefaultEnd,

                                                        CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.HireStartDays]),

                                                        CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.HireCompDays]),

                                                        ref stDate, ref edDate);

                    CreateLedgerSQL.GetPersonExpensesCostValue(CostDB, 0,

                                                CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.PersonCode]),

                                                dtDefaultStart, dtDefaultEnd, ref CostValue);