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

        #region オブジェクトを日付に変換する

        #region 対象日より営業期・工事年度を取得する

        /// 対象日より営業期・工事年度を取得する

            IOMBizPeriodHistory BizHisDB = new IOMBizPeriodHistory();

                int nRetVal = 0;

                // 範囲に入るデータを取得する

                StringBuilder strSQL = new StringBuilder();

                strSQL.AppendFormat(" WHERE (DATE(BeginDate) <= DATE('{0}'))", NowDay.ToShortDateString());

                strSQL.AppendFormat(" AND (DATE('{0}') <= DATE(CompleteDate))", NowDay.ToShortDateString());

                strSQL.AppendFormat(" And PeriodFlag = {0}", CommonMotions.SystemMasterData.ConstructionNoBase);

                strSQL.Append(" Order By PeriodFlag Asc, PeriodYear Asc");

                List<BizPeriodHistory> BizList = new List<BizPeriodHistory>();

                if (!BizHisDB.SelectAction(strSQL.ToString(), ref BizList)) return 0;

                nRetVal = BizList[0].PeriodYear;

                return 0;

            finally

            {

                BizHisDB.close(); BizHisDB = null;

            }

        #region 部署コンボボックスデータ取得ＳＱＬ作成

        /// <summary>

        /// 部署コンボボックスデータ取得ＳＱＬ作成

        /// </summary>

        /// <param name="strSQL"></param>

        public static void CreateDepCombBoxSQL(ref StringBuilder strSQL, int nTargetCount)

        {

            try

            {

                // 今期のデータかどうかを判定する

                bool bNowSeason = true;

                if (nTargetCount != CommonMotions.SystemMasterData.BusinessPeriod) bNowSeason = false;

                strSQL.Append("SELECT");

                strSQL.Append(" A.DEPARTMENTCODE");

                strSQL.Append(", A.DEPARTMENTSTRING");

                strSQL.Append(", A.DISPLAYORDER");

                strSQL.Append(", COUNT(*)");

                strSQL.Append(" FROM");

                if (bNowSeason)

                {

                    // 今期のプライマリテーブルは部署マスタ

                    strSQL.Append(" DEPARTMENTMASTER A");

                }

                else

                {   // 過去のデータは工事情報より取得する

                    strSQL.Append(" (");

                    strSQL.Append("Select");

                    strSQL.Append(" A1.SalesDepCode As DEPARTMENTCODE");

                    strSQL.Append(", B1.DetailString As DEPARTMENTSTRING");

                    strSQL.Append(", ifnull(C1.DisplayOrder, 10) As DISPLAYORDER");

                    strSQL.Append(" From Constructionbaseinfo As A1");

                    strSQL.Append("  Inner Join constructionbaseinfodetail As B1");

                    strSQL.Append("        On B1.ConstructionCode = A1.ConstructionCode");

                    strSQL.AppendFormat("  And B1.DetailNo = {0}", (int)ConstructionBaseInfoDetail.DataNoDef.SalesDepartmentName);

                    strSQL.Append("  Left Join departmentmaster As C1");

                    strSQL.Append("       On C1.DepartmentCode = A1.SalesDepCode");

                    strSQL.AppendFormat(" Where A1.ConstructionPeriod = {0}", nTargetCount);

                    strSQL.Append(" Union Select");

                    strSQL.Append(" A2.ConstrDepCode As DEPARTMENTCODE");

                    strSQL.Append(" , B2.DetailString As DEPARTMENTSTRING");

                    strSQL.Append(" , ifnull(C2.DisplayOrder, 10) As DISPLAYORDER");

                    strSQL.Append(" From Constructionbaseinfo As A2");

                    strSQL.Append(" Inner Join constructionbaseinfodetail As B2");

                    strSQL.Append("       On B2.ConstructionCode = A2.ConstructionCode");

                    strSQL.AppendFormat(" And B2.DetailNo = {0}", (int)ConstructionBaseInfoDetail.DataNoDef.ConstrDepartmentName);

                    strSQL.Append(" Left Join departmentmaster As C2");

                    strSQL.Append("      On C2.DepartmentCode = A2.ConstrDepCode");

                    strSQL.AppendFormat(" Where A2.ConstructionPeriod = {0}", nTargetCount);

                    strSQL.Append(" Union Select");

                    strSQL.Append(" A3.ConstrSubDepCode As DEPARTMENTCODE");

                    strSQL.Append(", B3.DetailString As DEPARTMENTSTRING");

                    strSQL.Append(", ifnull(C3.DisplayOrder, 10) As DISPLAYORDER");

                    strSQL.Append(" From Constructionbaseinfo As A3");

                    strSQL.Append(" Inner Join constructionbaseinfodetail As B3");

                    strSQL.Append("       On B3.ConstructionCode = A3.ConstructionCode");

                    strSQL.AppendFormat(" And B3.DetailNo = {0}", (int)ConstructionBaseInfoDetail.DataNoDef.ConstrSubDepartmentName);

                    strSQL.Append(" Left Join departmentmaster As C3");

                    strSQL.Append("      On C3.DepartmentCode = A3.ConstrSubDepCode");

                    strSQL.AppendFormat(" Where A3.ConstructionPeriod = {0}", nTargetCount);

                    strSQL.Append(" Union Select");

                    strSQL.Append(" A4.ConstrInstrDepCode As DEPARTMENTCODE");

                    strSQL.Append(", B4.DetailString As DEPARTMENTSTRING");

                    strSQL.Append(", ifnull(C4.DisplayOrder, 10) As DISPLAYORDER");

                    strSQL.Append(" From Constructionbaseinfo As A4");

                    strSQL.Append(" Inner Join constructionbaseinfodetail As B4");

                    strSQL.Append("       On B4.ConstructionCode = A4.ConstructionCode");

                    strSQL.AppendFormat(" And B4.DetailNo = {0}", (int)ConstructionBaseInfoDetail.DataNoDef.InstrDepartmentName);

                    strSQL.Append(" Left Join departmentmaster As C4");

                    strSQL.Append("      On C4.DepartmentCode = A4.ConstrInstrDepCode");

                    strSQL.AppendFormat(" Where A4.ConstructionPeriod = {0}", nTargetCount);

                    strSQL.Append(") As A");

                }

                strSQL.Append(", PERSONINCHARGEMASTER B");

                strSQL.Append(", CONSTRUCTIONBASEINFO C");

                strSQL.Append(" WHERE");

                if (bNowSeason)

                {   // 今期は削除されていないもの

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

                    // システム管理者以外は部署参照マスタよりデータ取得

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

                    {

                        // 参照部署マスタより取得する

                        strSQL.Append(" And A.DEPARTMENTCODE");

                        strSQL.Append(" IN (SELECT A1.DEPARTMENTCODE FROM PERSONDEPARTMENTMASTER A1");

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

                    }

                    strSQL.Append(" AND");

                }

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

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

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

                strSQL.Append(" ORDER BY A.DISPLAYORDER ASC");

            }

            catch (Exception ex)

            {

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

            }

        }

        #region 担当者コンボボックスデータ取得ＳＱＬ作成

        /// <summary>

        /// 担当者コンボボックスデータ取得ＳＱＬ作成

        /// </summary>

        /// <param name="strSQL"></param>

        /// <param name="nTargetYear"></param>

        public static void CreateManCombBoxSQL(ref StringBuilder strSQL, int nTargetYear, int nDepCode = 0, int nLEDGERFLG = -1)

        {

            try

            {

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

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

                strSQL.Append("Select A.* From");

                strSQL.Append(" personinchargemaster As A");

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

                strSQL.Append("       On B.PersonCode = A.PersonCode");

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

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

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

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

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

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

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

                if (nLEDGERFLG == -1) strSQL.AppendFormat(" AND A.LEDGERFLG = {0}", (int)PersonInChargeMaster.LedgerDivNoDef.CalcTarget);

                else if (nLEDGERFLG > -1) strSQL.AppendFormat(" AND A.LEDGERFLG = {0}", nLEDGERFLG);

                // 今期のデータかどうかを判定する

                bool bNowSeason = true;

                string FieldName = string.Empty;

                if (CommonMotions.SystemMasterData.ConstructionNoBase == (int)SystemMaster.ConstrNoBaseDef.BusinessPeriod)

                {

                    FieldName = "ConstructionPeriod";

                    if (nTargetYear != CommonMotions.SystemMasterData.BusinessPeriod) bNowSeason = false;

                }

                else

                {

                    FieldName = "ConstructionYear";

                    if (nTargetYear != CommonMotions.SystemMasterData.ConstrYear) bNowSeason = false;

                }

                if (!bNowSeason)

                {

                    strSQL.Append(" AND A.DepartmentCode IN(SELECT InDep.DEPARTMENTCODE FROM (");

                    strSQL.Append(" Select A1.SalesDepCode As DEPARTMENTCODE From Constructionbaseinfo As A1");

                    strSQL.AppendFormat(" Where A1.{0} = {1}", FieldName, nTargetYear);

                    strSQL.Append(" Union Select A2.ConstrDepCode As DEPARTMENTCODE From Constructionbaseinfo As A2");

                    strSQL.AppendFormat(" Where A2.{0} = {1}", FieldName, nTargetYear);

                    strSQL.Append(" Union Select A3.ConstrSubDepCode As DEPARTMENTCODE From Constructionbaseinfo As A3");

                    strSQL.AppendFormat(" Where A3.{0} = {1}", FieldName, nTargetYear);

                    strSQL.Append(" Union Select A4.ConstrInstrDepCode As DEPARTMENTCODE From Constructionbaseinfo As A4");

                    strSQL.AppendFormat(" Where A4.{0} = {1}", FieldName, nTargetYear);

                    strSQL.Append(" ) As InDep");

                    strSQL.Append(" GROUP BY InDep.DEPARTMENTCODE) ");

                }

                strSQL.Append(" Order By A.DeleteFlg ASC, A.EmployeeClassFlg ASC, A.DisplayOrder ASC");

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #region 担当者より指定年度の部署を取得するＳＱＬ作成

        /// <summary>

        /// 担当者より指定年度の部署を取得するＳＱＬ作成

        /// </summary>

        public static void CreateMan2DepSQL(ref StringBuilder strSQL, int nTargetYear, int nPersonCode)

        {

            try

            {

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

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

                strSQL.Append("Select");

                strSQL.Append(" A.PersonCode");

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

                strSQL.Append(", B.DepartmentCode");

                strSQL.Append(", B.DepartmentName");

                strSQL.Append(" From");

                strSQL.Append(" personinchargemaster As A");

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

                strSQL.Append("       On B.PersonCode = A.PersonCode");

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

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

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

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

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

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

                strSQL.AppendFormat(" AND A.PersonCode = {0}", nPersonCode);

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #endregion

        public static int s_SystemVersion = 155;

                if (DataList.Count < 1)

                    strSECSQL.AppendFormat(" A.DEPARTMENTCODE = {0}", FromCode);

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

                 || CommonMotions.LoginUserSecurity.SecRank == CommonDefine.SecurityRankList[SRank].Key)

                // 書込み者は最上位者か？

                if (!bProtect)

                    // 読込者は最上位者が含まれているか？

                    int ReadCode = 0;

                    foreach (object[] objRec in TargetList)

                    {

                        ReadCode = CommonMotions.cnvInt(objFirst[(int)IOMessageBoardTerget.NameColumn.ToCode]);

                        bProtect = DecideLockData(ReadCode);

                        if (bProtect) break;

                    }

                // ----- 書込み側対象者判定

                if (bProtect)

                {   // 最上位者ロック有

                    if (WriterCode < 99999)

                    {

                        // 対象者コードが5桁以下ならば部署コード

                        if (WriterCode == CommonMotions.LoginUserData.DepartmentCode) return true;

                    }

                    else

                    {

                        if (WriterCode == CommonMotions.LoginUserData.PersonCode) return true;

                    }

                }

                {   // 最上位者ロック無

                    if (DataReadPermission(WriterCode)) return true;

                // ----- 読込側対象者判定

                // 読込対象テーブルにあるか？ 読込対象者をチェックする

                    if (bProtect)

                        // ----- 最上位者ロック有（対象のみ）

                        if (wrkRec.ToCode < 99999)

                        {   // 対象者コードが5桁以下ならば部署コード

                            // 所属部署が対象ならばOK

                            if (wrkRec.ToCode == CommonMotions.LoginUserData.DepartmentCode) return true;

                        }

                        else

                        {   // 以外は担当者コード

                            // 全員の場合はOK

                            if (wrkRec.ToCode == CommonDefine.s_AllMembersCode) return true;

                            // 対象に自分があればOK

                            if (wrkRec.ToCode == CommonMotions.LoginUserData.PersonCode) return true;

                    {

                        // 最上位者ロック無（参照部署を含めて対象）

                        // 参照部署にあるかのチェック

                        bool bmatch = DataReadPermission(wrkRec.ToCode);

                        // 参照部署に無ければ次へ

                        if (!bmatch) continue;

                        int CurRecRank = CommonMotions.cnvInt(objRec[TergetCnt + (int)GetTarget.SecRank]);

                        // 書込み者が自分より上位者では無い場合は読み込める

                        if (CommonMotions.LoginUserSecurity.SecRank <= CurRecRank) return true;

                    PersonDepartmentMaster FindDepRec = CommonMotions.LoginUserRefDepartment.Find(x => x.DepartmentCode == SourceCode);

                    if (FindDepRec != null)

                        if (FindDepRec.DepartmentCode > 0) return true;

                // 全員の場合は無条件にOK

                if (SourceCode == CommonDefine.s_AllMembersCode) return true;

                if (SourceCode == CommonMotions.LoginUserData.PersonCode) return true;

                if (!PersonDB.SelectAction(strSQL.ToString(), ref PersonRec)) return false;

                PersonDepartmentMaster FindRec = CommonMotions.LoginUserRefDepartment.Find(x => x.DepartmentCode == PersonRec.DepartmentCode);

                if (FindRec != null)

                    if (FindRec.DepartmentCode > 0) return true;

            SalesDepCode,

            ConstrDepCode,

            ConstrSubDepCode,

            ConstrInstrDepCode,

        /// <summary>

        /// 複数データ読込み処理

        /// </summary>

        /// <returns></returns>

        /// <summary>

        /// 1件データ読込処理

        /// </summary>

        /// <returns></returns>

                strcmd.AppendFormat(", {0}", data.SalesDepCode);

                strcmd.AppendFormat(", {0}", data.ConstrDepCode);

                strcmd.AppendFormat(", {0}", data.ConstrSubDepCode);

                strcmd.AppendFormat(", {0}", data.ConstrInstrDepCode);

                    strcmd.AppendFormat(", {0}", work.SalesDepCode);

                    strcmd.AppendFormat(", {0}", work.ConstrDepCode);

                    strcmd.AppendFormat(", {0}", work.ConstrSubDepCode);

                    strcmd.AppendFormat(", {0}", work.ConstrInstrDepCode);

                strcmd.AppendFormat(", SalesDepCode = {0}", data.SalesDepCode);

                strcmd.AppendFormat(", ConstrDepCode = {0}", data.ConstrDepCode);

                strcmd.AppendFormat(", ConstrSubDepCode = {0}", data.ConstrSubDepCode);

                strcmd.AppendFormat(", ConstrInstrDepCode = {0}", data.ConstrInstrDepCode);

                wrk.SalesDepCode = int.Parse(objwrk[(int)TableColumn.SalesDepCode].ToString());

                wrk.ConstrDepCode = int.Parse(objwrk[(int)TableColumn.ConstrDepCode].ToString());

                wrk.ConstrSubDepCode = int.Parse(objwrk[(int)TableColumn.ConstrSubDepCode].ToString());

                wrk.ConstrInstrDepCode = int.Parse(objwrk[(int)TableColumn.ConstrInstrDepCode].ToString());

                    case (int)TableColumn.SalesDepCode:

                        strcmd.AppendFormat(" SalesDepCode = {0}", ((int)value).ToString());

                        break;

                    case (int)TableColumn.ConstrDepCode:

                        strcmd.AppendFormat(" ConstrDepCode = {0}", ((int)value).ToString());

                        break;

                    case (int)TableColumn.ConstrSubDepCode:

                        strcmd.AppendFormat(" ConstrSubDepCode = {0}", ((int)value).ToString());

                        break;

                    case (int)TableColumn.ConstrInstrDepCode:

                        strcmd.AppendFormat(" ConstrInstrDepCode = {0}", ((int)value).ToString());

                        break;

        #region 工事基本情報明細追加（1件）

        /// 工事基本情報明細追加（1件）

        #endregion

        #region 工事基本情報明細追加（複数）

        /// 工事基本情報明細追加（複数）

            CreatePersonCode = 0,

            PersonName,

            CreateDepartmentCode,

            CreateDepartmentName,

            strcmd.Append("  CreatePersonCode");

            strcmd.Append(", DATE_FORMAT(CreateDate, '%Y/%m/%d')");

            strcmd.Append(", SeqNo");

            strcmd.Append(", DataMode");

            strcmd.Append(", SourceCotegory");

            strcmd.Append(", SourceCode");

            strcmd.Append(", PetitionPeriod");

            strcmd.Append(", OrderFlg");

            strcmd.Append(", CorporateStatusName");

            strcmd.Append(", CorporateStatusPoint");

            strcmd.Append(", OrderersName1");

            strcmd.Append(", OrderersName2");

            strcmd.Append(", DepartmentName");

            strcmd.Append(", PersonName");

            strcmd.Append(", ZipCode");

            strcmd.Append(", Address1");

            strcmd.Append(", Address2");

            strcmd.Append(", Address3");

            strcmd.Append(", PhoneNumber");

            strcmd.Append(", FaxNumber");

            strcmd.Append(", MailAddress");

            strcmd.Append(", Note");

            strcmd.Append(", OrderCotegory");

            strcmd.Append(", DATE_FORMAT(OrderDate, '%Y/%m/%d')");

            strcmd.Append(", OrderNo");

            strcmd.Append(", CreateDepartmentCode");

            strcmd.Append(", CreateDepartmentName");

                strcmd.AppendFormat("  {0}", work.CreatePersonCode);

                strcmd.AppendFormat(", '{0}'", work.PersonName);

                strcmd.AppendFormat(", {0}", work.CreateDepartmentCode);

                strcmd.AppendFormat(", '{0}'", work.CreateDepartmentName);

                    strcmd.AppendFormat("  {0}", work.CreatePersonCode);

                    strcmd.AppendFormat(", '{0}'", work.PersonName);

                    strcmd.AppendFormat(", {0}", work.CreateDepartmentCode);

                    strcmd.AppendFormat(", '{0}'", work.CreateDepartmentName);

                strcmd.AppendFormat(" CreatePersonCode = {0}", data.CreatePersonCode);

                strcmd.AppendFormat(", PersonName = '{0}'", data.PersonName);

                strcmd.AppendFormat(", CreateDepartmentCode = '{0}'", data.CreateDepartmentCode);

                strcmd.AppendFormat(", CreateDepartmentName = '{0}'", data.CreateDepartmentName);

                    case (int)TableColumn.CreatePersonCode:

                        strcmd.AppendFormat(" CreatePersonCode = {0}", ((int)value).ToString());

                    case (int)TableColumn.PersonName:

                        strcmd.AppendFormat(" PersonName = '{0}'", value.ToString());

                    case (int)TableColumn.CreateDepartmentCode:

                        strcmd.AppendFormat(" CreateDepartmentCode = {0}", ((int)value).ToString());

                        break;

                    case (int)TableColumn.CreateDepartmentName:

                        strcmd.AppendFormat(" CreateDepartmentName = '{0}'", value.ToString());

                        break;

                wrk.CreatePersonCode = int.Parse(objwrk[(int)TableColumn.CreatePersonCode].ToString());

                wrk.PersonName = objwrk[(int)TableColumn.PersonName].ToString();

                wrk.CreateDepartmentCode = int.Parse(objwrk[(int)TableColumn.CreateDepartmentCode].ToString());

                wrk.CreateDepartmentName = objwrk[(int)TableColumn.CreateDepartmentName].ToString();

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Collections;

using log4net;

using MySql.Data.MySqlClient;           // Data Provider for MySql

using ProcessManagement.DB.MySql;

using ProcessManagement.DB.Core;

using ProcessManagement.DataModel;

using ProcessManagement.Common;

namespace ProcessManagement.DB.IOAccess

{

    /// <summary>

    /// 担当者部署履歴データＤＢアクセス

    /// </summary>

    public class IOMChgChargeDep : MySqlProcess

    {

        #region 定義部

        /// <summary>

        /// log4netログを使用する

        /// </summary>

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

        #endregion

        #region 定数

        /// <summary>

        /// 担当者部署履歴データフィールド並び

        /// </summary>

        public enum ColumnCnt

        {

            PersonCode = 0,

            StartDate,

            CompDate,

            DepartmentCode,

            DepartmentName,

            EntryDate,

            UpdateDate,

        }

        #endregion

        #region コンストラクタ

        /// <summary>

        /// コンストラクタ

        /// </summary>

        /// <param name="ConnectionString"></param>

        public IOMChgChargeDep()

            : base(DBCommon.Instance.DBConnectString)

        {

        }

        #endregion

        #region 担当者部署履歴データ検索文字列作成

        /// <summary>

        /// 担当者部署履歴データ検索文字列作成

        /// </summary>

        /// <param name="AddSQLString">検索条件SQL文字列</param>

        /// <param name="data">担当者部署履歴データデータ</param>

        /// <returns>true：成功 false：失敗</returns>

        private string CreateSelectSQL()

        {

            StringBuilder strcmd = new StringBuilder();

            strcmd.Append("SELECT");

            strcmd.Append("  PersonCode");

            strcmd.Append(", DATE_FORMAT(StartDate, '%Y/%m/%d')");

            strcmd.Append(", DATE_FORMAT(CompDate, '%Y/%m/%d')");

            strcmd.Append(", DepartmentCode");

            strcmd.Append(", DepartmentName");

            strcmd.Append(", DATE_FORMAT(EntryDate, '%Y/%m/%d %H:%i:%s')");

            strcmd.Append(", DATE_FORMAT(UpdateDate, '%Y/%m/%d %H:%i:%s')");

            strcmd.Append(" FROM ChgChargeDep");

            return strcmd.ToString();

        }

        #endregion

        #region 担当者部署履歴データ複数検索

        /// <summary>

        /// 担当者部署履歴データ複数検索

        /// </summary>

        /// <param name="AddSQLString"></param>

        /// <param name="data"></param>

        /// <param name="bConnect"></param>

        /// <returns></returns>

        public bool SelectAction(string AddSQLString, ref List<ChgChargeDep> data, bool bConnect = true)

        {

            // インターフェース

            StringBuilder strcmd = new StringBuilder();;

            ArrayList arData = new ArrayList();

            try

            {

                // SQL作成（DateTime型が変換できないのでCharに変換しておく）

                strcmd.Append(CreateSelectSQL());

                strcmd.Append(AddSQLString);

                // SQL実行

                if (!ExecuteReader(strcmd.ToString(), ref arData, bConnect)) return false;

                // データセット

                foreach (object[] objwrk in arData)

                {

                    ChgChargeDep work = new ChgChargeDep();

                    Reader2Struct(objwrk, ref work);

                    data.Add(work);

                }

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者部署履歴データ１件検索

        /// <summary>

        /// 担当者部署履歴データ１件検索

        /// </summary>

        /// <param name="AddSQLString"></param>

        /// <param name="data"></param>

        /// <param name="bConnect"></param>

        /// <returns></returns>

        public bool SelectAction(string AddSQLString, ref ChgChargeDep data, bool bConnect = true)

        {

            // インターフェース

            StringBuilder strcmd = new StringBuilder();;

            ArrayList arData = new ArrayList();

            try

            {

                // SQL作成（DateTime型が変換できないのでCharに変換しておく）

                strcmd.Append(CreateSelectSQL());

                strcmd.Append(AddSQLString);

                // SQL実行

                if (!ExecuteReader(strcmd.ToString(), ref arData, bConnect)) return false;

                if (arData.Count == 0) return false;

                // データセット

                object[] objwrk = (object[])arData[0];

                Reader2Struct(objwrk, ref data);

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者マスタ追加（1件）

        /// <summary>

        /// 担当者マスタ追加（1件）

        /// </summary>

        /// <param name="data">担当者マスタデータ</param>

        /// <returns>true：成功 false：失敗</returns>

        public bool InsertAction(ChgChargeDep work, bool bConnect = true)

        {

            StringBuilder strcmd = new StringBuilder();

            try

            {

                strcmd.Append("INSERT INTO ChgChargeDep");

                strcmd.Append(" (");

                bool bFirst = true;

                foreach (var gender in Enum.GetValues(typeof(ColumnCnt)))

                {

                    if (!bFirst) strcmd.Append(" ,");

                    strcmd.Append(gender.ToString());

                    bFirst = false;

                }

                strcmd.Append(") VALUES (");

                strcmd.AppendFormat("  {0}", work.PersonCode);

                strcmd.AppendFormat(", STR_TO_DATE('{0}', '%Y/%m/%d')", work.StartDate.ToShortDateString());

                strcmd.AppendFormat(", STR_TO_DATE('{0}', '%Y/%m/%d')", work.CompDate.ToShortDateString());

                strcmd.AppendFormat(", {0}", work.DepartmentCode);

                strcmd.AppendFormat(",'{0}'", work.DepartmentName);

                strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.EntryDate);

                strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.UpdateDate);

                strcmd.Append(")");

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者部署履歴データ追加（複数）

        /// <summary>

        /// 担当者部署履歴データ追加（複数）

        /// </summary>

        /// <param name="data">担当者部署履歴データデータ</param>

        /// <returns>true：成功 false：失敗</returns>

        public bool InsertAction(List<ChgChargeDep> data, bool bConnect = true)

        {

            StringBuilder strcmd = new StringBuilder();;

            try

            {

                bool bColFirst = true;

                strcmd.Append("INSERT INTO ChgChargeDep");

                strcmd.Append(" (");

                foreach (var gender in Enum.GetValues(typeof(ColumnCnt)))

                {

                    if (!bColFirst) strcmd.Append(", ");

                    strcmd.Append(gender.ToString());

                    bColFirst = false;

                }

                strcmd.Append(") VALUES");

                bool bDataFirst = true;

                foreach (ChgChargeDep work in data)

                {

                    if (bDataFirst) strcmd.Append(" (");

                    else strcmd.Append(", (");

                    strcmd.AppendFormat("  {0}", work.PersonCode);

                    strcmd.AppendFormat(", STR_TO_DATE('{0}', '%Y/%m/%d')", work.StartDate.ToShortDateString());

                    strcmd.AppendFormat(", STR_TO_DATE('{0}', '%Y/%m/%d')", work.CompDate.ToShortDateString());

                    strcmd.AppendFormat(", {0}", work.DepartmentCode);

                    strcmd.AppendFormat(",'{0}'", work.DepartmentName);

                    strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.EntryDate);

                    strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.UpdateDate);

                    strcmd.Append(")");

                    bDataFirst = false;

                }

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者部署履歴データ更新

        /// <summary>

        /// 担当者部署履歴データ更新

        /// </summary>

        /// <param name="AddSQLString">更新条件SQL文字列</param>

        /// <param name="data">担当者部署履歴データデータ</param>

        /// <returns>true：成功 false：失敗</returns>

        public bool UpdateAction(string AddSQLString, ChgChargeDep data, bool bConnect = true)

        {

            StringBuilder strcmd = new StringBuilder();;

            try

            {

                strcmd.Append("UPDATE ChgChargeDep");

                strcmd.Append(" SET");

                strcmd.AppendFormat("  PersonCode = {0}", data.PersonCode);

                strcmd.AppendFormat(", StartDate = STR_TO_DATE('{0}' ,'%Y/%m/%d')", data.StartDate.ToShortDateString());

                strcmd.AppendFormat(", CompDate = STR_TO_DATE('{0}' ,'%Y/%m/%d')", data.CompDate.ToShortDateString());

                strcmd.AppendFormat(", DepartmentCode = {0}", data.DepartmentCode);

                strcmd.AppendFormat(", DepartmentName = '{0}'", data.DepartmentName);

                strcmd.Append(", UpdateDate = NOW()");

                strcmd.Append(AddSQLString);

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者部署履歴データ削除

        /// <summary>

        /// 担当者部署履歴データ削除

        /// </summary>

        /// <param name="AddSQLString">削除条件SQL文字列</param>

        /// <param name="data">担当者部署履歴データデータ</param>

        /// <returns>true：成功 false：失敗</returns>

        public bool DeleteAction(string AddSQLString, bool bConnect = true)

        {

            // インターフェース

            StringBuilder strcmd = new StringBuilder();;

            try

            {

                strcmd.AppendFormat("{0}{1}", "DELETE FROM ChgChargeDep", AddSQLString);

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 1項目更新処理

        /// <summary>

        /// 1項目の更新を行う

        /// </summary>

        /// <param name="PersonCode"></param>

        /// <param name="FeildNo"></param>

        /// <param name="value"></param>

        /// <param name="bConnect"></param>

        /// <returns></returns>

        public bool UpdateFeild(int PersonCode, DateTime StartDate, int FeildNo, object value, bool bConnect = true)

        {

            StringBuilder strcmd = new StringBuilder();;

            try

            {

                strcmd.Append("UPDATE ChgChargeDep");

                strcmd.Append(" SET");

                switch (FeildNo)

                {

                    case (int)ColumnCnt.PersonCode:

                        strcmd.AppendFormat(" PersonCode = {0}", ((int)value).ToString());

                        break;

                    case (int)ColumnCnt.StartDate:

                        strcmd.AppendFormat(" StartDate = STR_TO_DATE('{0}','%Y/%m/%d')", ((DateTime)value).ToShortDateString());

                        break;

                    case (int)ColumnCnt.CompDate:

                        strcmd.AppendFormat(" CompDate = STR_TO_DATE('{0}','%Y/%m/%d')", ((DateTime)value).ToShortDateString());

                        break;

                    case (int)ColumnCnt.DepartmentCode:

                        strcmd.AppendFormat(" DepartmentCode = {0}", ((int)value).ToString());

                        break;

                    case (int)ColumnCnt.DepartmentName:

                        strcmd.AppendFormat(" DepartmentName = '{0}'", value.ToString());

                        break;

                }

                strcmd.Append(", UpdateDate = NOW()");

                strcmd.Append(CreatePrimarykeyString(PersonCode, StartDate));

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region MySQLDataReaderより構造体へセットする

        /// <summary>

        /// MySQLDataReaderより構造体へセットする

        /// </summary>

        /// <param name="reader">MySQLDataReader</param>

        /// <param name="wrk">構造体</param>

        public void Reader2Struct(object[] objwrk, ref ChgChargeDep wrk)

        {

            try

            {

                // データ取得

                wrk.PersonCode = int.Parse(objwrk[(int)ColumnCnt.PersonCode].ToString());

                wrk.StartDate = DateTime.Parse(objwrk[(int)ColumnCnt.StartDate].ToString());

                wrk.CompDate = DateTime.Parse(objwrk[(int)ColumnCnt.CompDate].ToString());

                wrk.DepartmentCode = int.Parse(objwrk[(int)ColumnCnt.DepartmentCode].ToString());

                wrk.DepartmentName = objwrk[(int)ColumnCnt.DepartmentName].ToString();

                wrk.EntryDate = DateTime.Parse(objwrk[(int)ColumnCnt.EntryDate].ToString());

                wrk.UpdateDate = DateTime.Parse(objwrk[(int)ColumnCnt.UpdateDate].ToString());

            }

            catch (MySqlException myex)

            {

                logger.ErrorFormat("ＭｙＳＱＬエラー：{0}：{1}", CommonMotions.GetMethodName(2), myex.Message);

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #region 構造体クリア

        /// <summary>

        /// 構造体クリア

        /// </summary>

        /// <param name="wrk"></param>

        public void ClearStruct(ref ChgChargeDep wrk)

        {

            wrk.PersonCode = 0;                     // 担当者コード

            wrk.StartDate = DateTime.MinValue;      // マスタ使用開始年月日付

            wrk.CompDate = DateTime.MinValue;       // マスタ使用終了年月日付

            wrk.DepartmentCode = 0;                 // 部署コード

            wrk.DepartmentName = string.Empty;      // 部署名

            wrk.EntryDate = DateTime.MinValue;      // 登録日付

            wrk.UpdateDate = DateTime.MinValue;     // 更新日付

        }

        #endregion

        #region 主キー検索の文字列を返す

        /// <summary>

        /// 主キー検索の文字列を返す

        /// </summary>

        /// <param name="ItemCode">大工種キー</param>

        /// <returns>Where文字列</returns>

        public string CreatePrimarykeyString(int PersonCode, DateTime StartDate)

        {

            StringBuilder strWork = new StringBuilder();

            try

            {

                strWork.AppendFormat(" Where PersonCode = {0}", PersonCode);

                if (StartDate != DateTime.MinValue)

                    strWork.AppendFormat(" And StartDate = STR_TO_DATE('{0}','%Y/%m/%d')", StartDate.ToShortDateString());

            }

            catch (Exception ex)

            {

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

            }

            return strWork.ToString();

        }

        #endregion

    }

}

        #region 担当者マスタ追加（1件）

        /// 担当者マスタ追加（1件）

        public bool InsertAction(PersonInChargeMaster work, bool bConnect = true)

        {

            StringBuilder strcmd = new StringBuilder();

            try

            {

                strcmd.Append("INSERT INTO PersonInChargeMaster");

                strcmd.Append(" (");

                bool bFirst = true;

                foreach (var gender in Enum.GetValues(typeof(ColumnCnt)))

                {

                    if (!bFirst) strcmd.Append(" ,");

                    strcmd.Append(gender.ToString());

                    bFirst = false;

                }

                strcmd.Append(") VALUES (");

                strcmd.AppendFormat("  {0}", work.PersonCode);

                strcmd.AppendFormat(", {0}", work.DisplayOrder);

                strcmd.AppendFormat(", '{0}'", work.PersonName);

                strcmd.AppendFormat(", STR_TO_DATE('{0}' ,'%Y/%m/%d')", work.StartDate.ToShortDateString());

                strcmd.AppendFormat(", STR_TO_DATE('{0}' ,'%Y/%m/%d')", work.EndDate.ToShortDateString());

                strcmd.AppendFormat(", '{0}'", work.PassWord);

                strcmd.AppendFormat(", {0}", work.SecurityManagement);

                strcmd.AppendFormat(", {0}", work.SecCode);

                strcmd.AppendFormat(", '{0}'", work.MsgBackColor);

                strcmd.AppendFormat(", '{0}'", work.DisplayString);

                strcmd.AppendFormat(", {0}", work.DepartmentCode);

                strcmd.AppendFormat(", {0}", work.MonthlySalary);

                strcmd.AppendFormat(", {0}", work.YearSalary);

                strcmd.AppendFormat(", '{0}'", work.Qualification);

                strcmd.AppendFormat(", '{0}'", work.SealPrintName);

                strcmd.AppendFormat(", {0}", work.EmployeeClassFlg);

                strcmd.AppendFormat(", {0}", work.LedgerFlg);

                strcmd.AppendFormat(", {0}", work.CommutingDistance);

                strcmd.AppendFormat(", {0}", work.CommuteCosts);

                strcmd.AppendFormat(", {0}", work.DeleteFlg);

                strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.EntryDate);

                strcmd.AppendFormat(", STR_TO_DATE('{0}','%Y/%m/%d %H:%i:%s')", work.UpdateDate);

                strcmd.Append(")");

                if (!ExecuteNonQuery(strcmd.ToString(), bConnect)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 担当者マスタ追加（複数）

        /// <summary>

        /// 担当者マスタ追加（複数）

        /// </summary>

        /// <param name="data">担当者マスタデータ</param>

        /// <returns>true：成功 false：失敗</returns>

            wrk.EndDate = DateTime.MinValue;        // マスタ使用終了年月日付

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace ProcessManagement.DataModel

{

    /// <summary>

    /// 担当者部署履歴データ

    /// </summary>

    public class ChgChargeDep

    {

        #region 定数

        #region 担当者マスタ：社員区分

        /// <summary>

        /// 社員区分

        /// </summary>

        public enum EmployeeClassFlgDef

        {