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

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

                    DateTime dtStart = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate]);

                    DateTime dtLast = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate]);

                    DateTime stDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate]);

                    DateTime edDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate]);

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

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

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

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

                    int nPersonCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.PersonCode]);

                    int nPersonCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.PersonCode]);

                    ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate] = wrkDate;

                    ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate] = wrkDate;

        public static int s_SystemVersion = 181;

                if (DepartmentCode == PersonRec.DepartmentCode) return true;

                if (DepartmentCode == PersonRec.DepartmentCode) return true;

                if (!LedgerDetailDataSalarySet(LedgerDDB, LedgerEDB,ProcConstrCode, frm, TotalOrdersPrice,

                int TargetParson1 = CommonMotions.cnvInt(((ComboBox)m_DspCtrl[(int)DspCnt.ConstructionPersonCode]).SelectedValue);  // ?S????

                int TargetParson2 = CommonMotions.cnvInt(((ComboBox)m_DspCtrl[(int)DspCnt.ConstrSubPersonCode]).SelectedValue);     // ???S????

                int TargetParson3 = CommonMotions.cnvInt(((ComboBox)m_DspCtrl[(int)DspCnt.ConstructionInstructor]).SelectedValue);  // ?w????

                NewDetailRec.CompanyCode = ConstructorCode;                                         // ??????R?[?h

                        // 希望金額が実行金額より小さい場合は金額セット

                        if (nExecPrice == 0 || nHopePrice < nExecPrice)

                if (DepartmentCode == PersonRec.DepartmentCode) return true;

                string strSQL = DetailDB.CreatePrimarykeyString(m_ConstructionCode);

                strSQL += string.Format(" AND GROUPCOUNT IN ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", 

                strSQL += " ORDER BY GroupCount ASC, LineCount ASC";

                DetailDB.SelectAction(strSQL, ref DetailList);

                if (BreakLine == 0) BreakLine = dgv.Rows.Count;

                foreach (DataGridViewRow wrkRow in dgv.Rows)

                    // 純利益の前までで処理終了

                    if (wrkRow.Index >= BreakLine) break;

                    EstimateValue += CommonMotions.cnvInt(wrkRow.Cells[(int)GridColumn.EstimatePrice].Value);           // 積算時見積金額

                    ExecValue += CommonMotions.cnvInt(wrkRow.Cells[(int)GridColumn.ExecPrice].Value);                   // 実行金額

                    PercentValue += CommonMotions.cnvDouble(wrkRow.Cells[(int)GridColumn.Percent].Value);               // 構成比率

                    TotalValue += CommonMotions.cnvInt(wrkRow.Cells[TotalPoint].Value);                                 // 合計金額

                    ResultValue += CommonMotions.cnvInt(wrkRow.Cells[ResultPoint].Value);                               // 実績金額

                    // 月毎加算

                    int arrayCnt = 0;

                    for (int i = (int)GridColumn.Payment; i < dgv.ColumnCount; i++, arrayCnt++)

                    {

                        MTotal[arrayCnt] += CommonMotions.cnvInt(wrkRow.Cells[i].Value);

                    }

                }

                    for (int i = (int)GridColumn.Payment; i < dgv.ColumnCount; i++, arrayCnt++)

                        RowData[i] = MTotal[arrayCnt].ToString("#,0");

                    for (int i = (int)GridColumn.Payment; i < dgv.ColumnCount; i++, arrayCnt++)

                        dgv.Rows[LineCnt].Cells[i].Value = MTotal[arrayCnt].ToString("#,0");

                dgvAllDisplay.Rows.Clear();

                DataGridView dgv = dgvAllDisplay;

                int TotalPoint = dgv.ColumnCount + (int)ColPayTotal.Totals;

                //int Rowcnt1 = GetExpenss("会社", 3);

                //int Rowcnt2 = GetExpenss("部署", 2);

                //int Rowcnt3 = GetExpenss("営業", 1);

                int PayrollRow = m_CostsRowCount[(int)CostsRowLine.PayrollRowLine];

                if (PayrollRow > 0)

                {

                    int GroupNo = 0;

                    for (int i = PayrollRow; i < dgv.RowCount; i++)

                    {

                        GroupNo = CommonMotions.cnvInt(dgv.Rows[i].Cells[(int)GridColumn.GroupCount].Value);

                        if (GroupNo != (int)DataGroup.Payroll) break;

                        work.Allowance += CommonMotions.cnvDouble(dgv.Rows[i].Cells[TotalPoint].Value);

                    }

                }

                int AssistantRow = m_CostsRowCount[(int)CostsRowLine.AssistantRowLine];

                double bworkAssistant = 0;

                if (AssistantRow > 0)

                {

                    int GroupNo = 0;

                    for (int i = AssistantRow; i < dgv.RowCount; i++)

                    {

                        GroupNo = CommonMotions.cnvInt(dgv.Rows[i].Cells[(int)GridColumn.GroupCount].Value);

                        if (GroupNo != (int)DataGroup.Assistant) break;

                        bworkAssistant += CommonMotions.cnvDouble(dgv.Rows[i].Cells[TotalPoint].Value);

                    }

                }

                int InstructorRow = m_CostsRowCount[(int)CostsRowLine.InstructorRowLine];

                double bworkInstructor = 0;

                if (InstructorRow > 0)

                {

                    int GroupNo = 0;

                    for (int i = InstructorRow; i < dgv.RowCount; i++)

                    {

                        GroupNo = CommonMotions.cnvInt(dgv.Rows[i].Cells[(int)GridColumn.GroupCount].Value);

                        if (GroupNo != (int)DataGroup.Instructor) break;

                        bworkInstructor += CommonMotions.cnvDouble(dgv.Rows[i].Cells[TotalPoint].Value);

                    }

                }

                double work1 = (work.Allowance + bworkAssistant + bworkInstructor);

                double work2 = (TotalValue - (work.Allowance + bworkAssistant + bworkInstructor));

                // 画面を閉じる

                frm.Close();

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,

            /// <summary>

            /// 来季契約工期開始

            /// </summary>

            NextStartDate,

            /// <summary>

            /// 来季契約工期終了

            /// </summary>

            NextCompDate,

        }

        #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 = CommonMotions.GetOpeningEndDate(BusinessPeriod, true);

                DateTime dtDefaultEnd = CommonMotions.GetOpeningEndDate(BusinessPeriod, false);

                int nPrevYear = (BusinessPeriod - 1);

                if (nPrevYear < 0) nPrevYear = 0;

                DateTime dtPrevStart = CommonMotions.GetOpeningEndDate(nPrevYear, true);

                DateTime dtNextEnd = CommonMotions.GetOpeningEndDate((BusinessPeriod + 1), false);

                strSQL.Append("Select");

                strSQL.Append(" DepHis.DepartmentCode");

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

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

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

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

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

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

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

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

                strSQL.Append(", NextDetail.SDate AS NextStart");

                strSQL.Append(", NextDetail.EDate AS NextEnd");

                strSQL.Append(" From");

                strSQL.Append(" personinchargemaster As Person");

                strSQL.Append(" Left Join chgchargedep As DepHis");

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

                strSQL.AppendFormat(" AND ((DATE(DepHis.STARTDATE) <= DATE('{0}'))", dtDefaultEnd.ToShortDateString());

                strSQL.AppendFormat(" AND (DATE('{0}') <= DATE(DepHis.CompDate)", dtDefaultStart.ToShortDateString());

                strSQL.AppendFormat(" OR DATE('{0}') = DATE(DepHis.CompDate)))", DateTime.MinValue.ToShortDateString());

                // 今期の開始終了（工事開始年月日が先期開始年月日以降を検索する）

                strSQL.Append(" Left Join");

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

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

                strSQL.Append("            , MAX(Ledger.ConstructionEnd) As EDate");

                strSQL.Append("       From constructionledgerdetail As LDetail");

                strSQL.Append("            Inner Join constructionledger As Ledger");

                strSQL.Append("                  On Ledger.ConstructionCode = LDetail.ConstructionCode");

                strSQL.AppendFormat("             And DATE('{0}') <= DATE(Ledger.Constructionstart)", dtPrevStart.ToShortDateString());

                strSQL.Append("            Inner Join constructionbaseinfo As Base");

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

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

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

                strSQL.AppendFormat(" Where 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("       Group By PersonCode");

                strSQL.Append("       ) As Detail");

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

                // 来季の開始終了（工事開始年月日が今期開始年月日以降を検索する）

                strSQL.Append(" Left Join");

                strSQL.Append("      (Select LDetail2.CompanyCode As PersonCode");

                strSQL.Append("            , MIN(Ledger2.ConstructionStart) As SDate");

                strSQL.Append("            , MAX(Ledger2.ConstructionEnd) As EDate");

                strSQL.Append("       From constructionLedgerdetail As LDetail2");

                strSQL.Append("            Inner Join constructionLedger As Ledger2");

                strSQL.Append("                  On Ledger2.ConstructionCode = LDetail2.ConstructionCode");

                strSQL.AppendFormat("             And DATE('{0}') <= DATE(Ledger2.Constructionstart)", dtDefaultStart.ToShortDateString());

                strSQL.Append("            Inner Join constructionbaseinfo As Base");

                strSQL.Append("                  On Base.ConstructionCode = LDetail2.ConstructionCode");

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

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

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

                                                                            , (int)FrmConstructionLedger.DataGroup.Instructor

                                                                            , (int)FrmConstructionLedger.DataGroup.Assistant

                                                                            , (int)FrmConstructionLedger.DataGroup.Payroll);

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

                strSQL.Append("       Group By PersonCode");

                strSQL.Append("       ) As NextDetail");

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

                strSQL.AppendFormat(" Where Person.LEDGERFLG = {0}", (int)PersonInChargeMaster.LedgerDivNoDef.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.AppendFormat(" AND DepHis.DEPARTMENTCODE = {0}", DepartmentCode);

                }

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

                strSQL.AppendFormat(" AND ((DATE(Person.STARTDATE) <= DATE('{0}'))", dtDefaultEnd.ToShortDateString());

                strSQL.AppendFormat(" AND (DATE('{0}') <= DATE(Person.ENDDATE)", dtDefaultStart.ToShortDateString());

                strSQL.AppendFormat(" OR DATE('{0}') = DATE(Person.ENDDATE)))", DateTime.MinValue.ToShortDateString());

                strSQL.AppendFormat(" ORDER BY DepHis.DepartmentCode, 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>

        public static void GetElapsedSalary(IOMPersonInCharge SalaryDB, int TargetNum, ArrayList TargetList, ref List<SalaryClass> ArrarySalary)

        {

            try

            {

                // デフォルト開始・終了を取得する

                DateTime dtDefaultStart = CommonMotions.GetOpeningEndDate(TargetNum, true);

                DateTime dtDefaultEnd = CommonMotions.GetOpeningEndDate(TargetNum, false);

                DateTime stDate = DateTime.Today;

                DateTime edDate = DateTime.Today;

                // 給与を集計する

                foreach (object[] ObjRec in TargetList)

                {

                    // 開始・終了日が無い場合はデフォルトの期首・期末を使用する

                    stDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate]);

                    edDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate]);

                    CalcStartCompDate(TargetNum,

                                        dtDefaultStart, dtDefaultEnd,

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

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

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

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

                                        ref stDate, ref edDate);

                    int nDepartmentCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.DepartmentCode]);

                    int nPersonCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.PersonCode]);

                    // 零れた給与日数取得

                    int TargetY = TargetNum;

                    int NextDiffDay = 0;

                    int PrevDiffDay = 0;

                    CommonMotions.CalcSpilledSalary(SalaryDB, TargetY, stDate, edDate, nPersonCode, ref NextDiffDay, ref PrevDiffDay);

                    // 今期終了日変更

                    DateTime wrkDate = edDate.AddDays(NextDiffDay);

                    if (dtDefaultEnd.Date < wrkDate.Date) wrkDate = dtDefaultEnd;

                    ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate] = wrkDate;

                    // 今期開始日変更

                    wrkDate = stDate.AddDays(PrevDiffDay);

                    if (dtDefaultEnd.Date < wrkDate.Date) wrkDate = dtDefaultEnd;

                    ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate] = wrkDate;

                    stDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate]);

                    edDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate]);

                    // 給与累積取得

                    int workSalary = CommonMotions.CalcElapsedSalary(SalaryDB, stDate, edDate, nPersonCode);

                    // 配列セット

                    ArrarySalary.Add(new SalaryClass(nDepartmentCode, nPersonCode, workSalary, stDate, edDate));

                }

            }

            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,

                                        DateTime NextStartDate, DateTime NextCompDate,

                                        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 (dtDefaultStart < HireStartDate && HireStartDate < dtDefaultEnd) StartDate = HireStartDate;

                // ----- 計算終了日セット

                // Defaultは期末

                CompDate = dtDefaultEnd;

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

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

                // 来季の開始があって期末日より小さい場合は置き換える

                if (NextStartDate != DateTime.MinValue)

                {

                    if (StartDate < NextStartDate && NextStartDate < CompDate) CompDate = NextStartDate.AddDays(-1);

                }

            }

            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,

                                            bool bExec = true)

        {

            try

            {

                // 部署コード取得

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

                //  工事予算と詳細台帳のグループ番号差を取得する

                int nGroupDiff = (int)FrmConstructionBudget.DataGroup.InstructorCosts - (int)FrmConstructionLedger.DataGroup.Instructor;

                // 振分給与金額取得

                StringBuilder strSQL = new StringBuilder();

                strSQL.Append("SELECT");

                if (bExec)

                    strSQL.Append(" SUM(C2.PAYMENTAMOUNT)");

                else

                    strSQL.Append(" SUM(C3.PAYMENTAMOUNT)");

                strSQL.Append(" FROM");

                strSQL.Append(" CONSTRUCTIONLEDGER P");

                strSQL.Append(", CONSTRUCTIONBASEINFO B");

                strSQL.Append(", CONSTRUCTIONLEDGERDETAIL C");

                if (bExec)

                {

                    strSQL.Append(" LEFT JOIN");

                    strSQL.Append(" (Select");

                    strSQL.Append(" CC2.ConstructionCode AS ConstructionCode");

                    strSQL.Append(", CC2.GroupCount AS GroupCount");

                    strSQL.Append(", ifnull(SUM(CC2.PaymentAmount), 0) AS PaymentAmount");

                    strSQL.Append(" From constructionledgerexcute AS CC2");

                    strSQL.Append(" Group By CC2.ConstructionCode, CC2.GroupCount");

                    strSQL.Append(" ) AS C2");

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

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

                }

                else

                {

                    strSQL.Append(" LEFT JOIN");

                    strSQL.Append(" (Select");

                    strSQL.Append(" CC3.ConstructionCode AS ConstructionCode");

                    strSQL.Append(", CC3.GroupCount AS GroupCount");

                    strSQL.Append(", ifnull(SUM(CC3.OrderDesiredAmount), 0) AS PaymentAmount");

                    strSQL.Append(" From constructionbudgetdetail AS CC3");

                    strSQL.Append(" Group By CC3.ConstructionCode, CC3.GroupCount");

                    strSQL.Append(") AS C3");

                    strSQL.Append(" ON ROUND((C3.ConstructionCode / 100), 0) = ROUND((C.ConstructionCode / 100), 0)");

                    strSQL.AppendFormat(" And C3.GroupCount = (C.GroupCount + {0})", nGroupDiff);

                }

                // 振分無では無い

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

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

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

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

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

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

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

                strSQL.Append(" AND C.CompanyCode = B.ConstructionPersonCode");

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

                {

                    strSQL.Append(" AND B.ConstrDepCode IN (select PDep.DepartmentCode from PersonDepartmentMaster AS PDep");

                    strSQL.AppendFormat(" WHERE PDep.PersonCode = {0})", CommonMotions.LoginUserData.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");

                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);

                }

                if (strSub.ToString().Length > 0)

                {

                    strSQL.Append(" Where");

                    strSQL.Append(strSub.ToString());

                }

                strSQL.Append(")");

                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();

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

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

                foreach (object[] wrkobj in arList)

                {

                    for (int i = 1; i < CostDataOfPerson.DataNoStrArrary.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>

        public static void GetPersonExpensesCostValue(IOCostDataOfPerson CostDB,

                                                        int nNowYear,

                                                        List<SalaryClass> ArrarySalary,

                                                        ref List<PersonExpensesCost> CostValue)

        {

            try

            {

                // デフォルト開始・終了を取得する

                DateTime dtDefaultStart = CommonMotions.GetOpeningEndDate(nNowYear, true);

                DateTime dtDefaultEnd = CommonMotions.GetOpeningEndDate(nNowYear, false);

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

                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");

                int[] GetCostVal = new int[CostDataOfPerson.DataNoStrArrary.Length];

                ArrayList arList = new ArrayList();

                StringBuilder strWhere = new StringBuilder();

                StringBuilder strExec = new StringBuilder();

                foreach (SalaryClass CurData in ArrarySalary)

                {

                    strWhere.Clear();

                    strExec.Clear();

                    strWhere.AppendFormat(" B.PERSONCODE = {0}", CurData.PersonCode);

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

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

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

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

                    strExec.Append(strSQL.ToString());

                    strExec.Append(strWhere.ToString());

                    arList.Clear();

                    if (!CostDB.ExecuteReader(strExec.ToString(), ref arList)) continue;

                    Array.Clear(GetCostVal, 0, GetCostVal.Length);

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

                    foreach (object[] wrkobj in arList)

                    {

                        for (int i = 1; i < CostDataOfPerson.DataNoStrArrary.Length; i++)

                        {

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

                            {

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

                                break;

                            }

                        }

                    }

                    // データセット

                    CostValue.Add(new PersonExpensesCost(CurData.DepartmentCode

                                                            , CurData.PersonCode

                                                            , CurData.StartDate

                                                            , CurData.CompDate

                                                            , new int[] { GetCostVal[0], GetCostVal[1], GetCostVal[2],

                                                                            GetCostVal[3],GetCostVal[4],GetCostVal[5],

                                                                            GetCostVal[6] }));

                }

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #region 経過給与を一括取得する

        /// <summary>

        /// 経過給与を一括取得する

        /// </summary>

        public static void GetElapsedSalaryAll(IOMPersonInCharge SalaryDB, int TargetNum, ref ArrayList TargetList, ref List<SalaryClass> ArrarySalary)

        {

            try

            {

                // 零れた給与日数取得とデータテーブル加工

                CommonMotions.CalcSpilledSalaryAll(SalaryDB, TargetNum, ref TargetList);

                // 給与累積取得

                List<KeyValuePair<int, long>> SalDataList = new List<KeyValuePair<int, long>>();

                CommonMotions.CalcElapsedSalaryAll(SalaryDB, TargetList, ref SalDataList);

                foreach (object[] ObjRec in TargetList)

                {

                    int nDepartmentCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.DepartmentCode]);

                    int nPersonCode = CommonMotions.cnvInt(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.PersonCode]);

                    DateTime stDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.StartDate]);

                    DateTime edDate = CommonMotions.cnvDate(ObjRec[(int)CreateLedgerSQL.GetPersonTerm.CompDate]);

                    KeyValuePair<int, long> CurValuePair = SalDataList.Find(x => x.Key == nPersonCode);

                    // 配列セット

                    ArrarySalary.Add(new SalaryClass(nDepartmentCode, nPersonCode, (int)CurValuePair.Value, stDate, edDate));

                }

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

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

        /// <summary>

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

        /// </summary>

        public static void GetPersonExpensesCostValueAll(IOCostDataOfPerson CostDB,

                                                        int nNowYear,

                                                        List<SalaryClass> ArrarySalary,

                                                        ref List<PersonExpensesCost> CostValue)

        {

            try

            {

                // デフォルト開始・終了を取得する

                DateTime dtDefaultStart = CommonMotions.GetOpeningEndDate(nNowYear, true);

                DateTime dtDefaultEnd = CommonMotions.GetOpeningEndDate(nNowYear, false);

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

                bool bFirst = true;

                StringBuilder strSQL = new StringBuilder();

                foreach (SalaryClass CurData in ArrarySalary)

                {

                    if(!bFirst) strSQL.Append(" UNION ");

                    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.AppendFormat(" B.PERSONCODE = {0}", CurData.PersonCode);

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

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

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

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

                    bFirst = false;

                }

                // データ取得

                ArrayList arList = new ArrayList();

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

                // データセット

                int[] GetCostVal = new int[CostDataOfPerson.DataNoStrArrary.Length];

                foreach (SalaryClass CurData in ArrarySalary)

                {

                    Array.Clear(GetCostVal, 0, GetCostVal.Length);

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

                    foreach (object[] wrkobj in arList)

                    {

                        for (int i = (int)CostDataOfPerson.DataNoDef.Transport

                                    ; i <= (int)CostDataOfPerson.DataNoDef.DisposalCost

                                    ; i++)

                        {

                            GetCostVal[i] = arList.Cast<object[]>().Where(x => CommonMotions.cnvInt(x[0]) == CurData.PersonCode

                                                                            && CommonMotions.cnvInt(x[1]) == i)

                                                                    .Sum(y => CommonMotions.cnvInt(y[2]));

                        }

                    }

                    CostValue.Add(new PersonExpensesCost(CurData.DepartmentCode

                                                            , CurData.PersonCode

                                                            , CurData.StartDate

                                                            , CurData.CompDate

                                                            , new int[] { GetCostVal[0], GetCostVal[1], GetCostVal[2],

                                                                                GetCostVal[3],GetCostVal[4],GetCostVal[5],

                                                                                GetCostVal[6] }));

                }

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

    }

}