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

        #region 資材返却期限

        /// <summary>

        /// 資材返却期限コントロールアイテム      // Add BIT 2017/03/18

        /// </summary>

        public static string[,] MaterualLimitItem = new string[2,4] {

                                                                      { "資材返却", "本日返却期限の資材があります。",   "0xFF0000", "0x000000" },

                                                                      { "資材返却", "返却期限を過ぎた資材があります。", "0x808080", "0xFF0000" }

                                                                    };

        public static List<KeyValuePair<int, int>> MaterualStatusCheckList = new List<KeyValuePair<int, int>>{

            new KeyValuePair<int, int>((int)CommonDefine.TermDateCheckTiming.CheckSchdule1, 0),

            new KeyValuePair<int, int>((int)CommonDefine.TermDateCheckTiming.CheckSchdule3, 1)};

        #endregion

        public static int CalcTargetMonthToColumn(DateTime StartDate, DateTime TargetMonth)

            SalesConfirm = 129,                 // 129:売上確認

                                                                                { (int)MaintenanceExecuteNo.SalesConfirm,               "売上確認"},              // 128

                        case (int)ProcessExecuteNo.RequestPrint:                        // 32：請求書印刷

                            ProcessExecuteNo_RequestPrint(m_ProcControlPara[NowPoint]);

                            break;

                        case (int)MaintenanceExecuteNo.SalesConfirm:                    // 129:売上確認

                            MaintenanceExecuteNo_SalesConfirm(m_ProcControlPara[NowPoint]);

                            break;

                int ProcessNo = (int)ProcessExecuteNo.RequestSummaryList;

                // 起動・編集フラグ

                int EditFlg = (int)CommonDefine.ProcessDataEdit.Reference;

                if (!ClsSecurityPermission.GetExecutePermission(ProcessNo, ref EditFlg))

                {

                    BackProcess();

                    return;

                }

                if (EditFlg == (int)CommonDefine.ProcessDataEdit.Reference) frm.EditLock = true;

            int nConstructionCode = 0;

            bool bEdit = true;

            if( CurrentPara.BoolExecParameter[0] == false )

            {

                // 工事案件選択画面

                FrmConstructionDiag frmCnstDlg = new FrmConstructionDiag();

                // 工事一覧の表示に必要なデータを設定(担当者コード、工事担当者検索)

                frmCnstDlg.PersonCode = CommonMotions.LoginUserData.PersonCode;

                frmCnstDlg.PersonCharge = (int)FrmConstructionDiag.PersonChargePoint.Construction;

                frmCnstDlg.SelectType = CurrentPara.IntExecParameter[3];

                // 工事案件選択画面のオープン

                frmCnstDlg.ShowDialog();

                nConstructionCode = frmCnstDlg.ConstructionCode;

                frmCnstDlg.Dispose();

                frmCnstDlg = null;

            }

            else{

                nConstructionCode = CurrentPara.IntExecParameter[0];

                bEdit = false;

            }

            if (0 == nConstructionCode)

                frm.ConstructionCode = nConstructionCode;

                frm.ApprovalCode = CurrentPara.IntExecParameter[1];

                frm.OrderNo = CurrentPara.IntExecParameter[2];

                frm.EditFlg = bEdit;

                int ProcessNo = (int)ProcessExecuteNo.PaymentConfirmation;

                // 起動・編集フラグ

                int EditFlg = (int)CommonDefine.ProcessDataEdit.Reference;

                if (!ClsSecurityPermission.GetExecutePermission(ProcessNo, ref EditFlg))

                {

                    BackProcess();

                    return;

                }

                // フォーム表示

        #region 請求書印刷

        /// <summary>

        /// 請求書印刷

        /// </summary>

        /// <param name="CurrentPara"></param>

        public static void ProcessExecuteNo_RequestPrint(ProcessParameter CurrentPara)

        {

            //　入金確認

            FrmEntryReqSelOrderer frm = new FrmEntryReqSelOrderer();

            try

            {

                int ProcessNo = (int)ProcessExecuteNo.PaymentConfirmation;

                // 起動・編集フラグ

                int EditFlg = (int)CommonDefine.ProcessDataEdit.Reference;

                if (!ClsSecurityPermission.GetExecutePermission(ProcessNo, ref EditFlg))

                {

                    BackProcess();

                    return;

                }

                // フォーム表示

                frm.ShowDialog();

            }

            catch (Exception ex)

            {

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

            }

            finally

            {

                frm.Dispose(); frm = null;

            }

        }

        #endregion

        #region 売上確認

        /// <summary>

        /// 売上確認

        /// </summary>

        /// <param name="CurrentPara"></param>

        public static void MaintenanceExecuteNo_SalesConfirm(ProcessParameter CurrentPara)

        {

            //　売上確認

            FrmSalesConfirm frm = new FrmSalesConfirm();

            try

            {

                int ProcessNo = (int)MaintenanceExecuteNo.SalesConfirm;

                // 起動・編集フラグ

                int EditFlg = (int)CommonDefine.ProcessDataEdit.Reference;

                if (!ClsSecurityPermission.GetExecutePermission(ProcessNo, ref EditFlg))

                {

                    BackProcess();

                    return;

                }

                // フォーム表示

                frm.ShowDialog();

            }

            catch (Exception ex)

            {

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

            }

            finally

            {

                frm.Dispose(); frm = null;

            }

        }

        #endregion

//                    if (ClsApprovalPermission.CheckApprovalData(m_ConstructionCode, (int)ClsExcute.ApprovalListNo.OrderBillingApproval, dt.OrderNo))

//                    {

//                        Approvedflg = true;

//                        break;

//                   }

        #region 保護区分

        /// <summary>

        /// 保護区分

        /// </summary>

        public enum SecRangeType

        {

            AllDepartment = 1,

            MultiDepartment = 2,

            OneDepartment = 3,

            InCharge = 4,

        }

        #endregion

        #region ユーザ情報

        /// <summary>

        /// ユーザ情報

        /// </summary>

        public class UserInfo

        {

            public CommonDefine.SecurityRankPos m_Type { get; set; }            // 権限

            public int m_GeneralAffairsFlg { get; set; }                        // 総務業務フラグ

            public SecRangeType m_SecRange { get; set; }                        // 保護区分

            public bool CanEditDeposit

            {

                get { return m_GeneralAffairsFlg == 1; }

            }

            public bool CanEditDepositDetail

            {

                get { return m_GeneralAffairsFlg == 0; }

            }

        }

        #endregion

        #region ユーザタイプ判定

        /// <summary>

        /// ユーザタイプ判定

        /// </summary>

        public static void SetUserType(UserInfo userInfo)

        {

            try

            {

                // 保護区分ランク

                // システム管理者・特別権限

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

                 || CommonMotions.LoginUserData.SecurityManagement == CommonDefine.SecurityRankList[(int)CommonDefine.SecurityRankPos.SpecialAuthority].Key)

                {

                    // 特別権限

                    userInfo.m_Type = CommonDefine.SecurityRankPos.SpecialAuthority;

                }

                // 統括者権限

                else if (CommonMotions.LoginUserData.SecurityManagement == CommonDefine.SecurityRankList[(int)CommonDefine.SecurityRankPos.FreeAuthority].Key)

                {

                    // 統括者権限

                    userInfo.m_Type = CommonDefine.SecurityRankPos.FreeAuthority;

                }

                // 所属長権限

                else if (CommonMotions.LoginUserData.SecurityManagement == CommonDefine.SecurityRankList[(int)CommonDefine.SecurityRankPos.LimitedAuthority].Key)

                {

                    // 所属長権限

                    userInfo.m_Type = CommonDefine.SecurityRankPos.LimitedAuthority;

                }

                // 一般権限

                else if (CommonMotions.LoginUserData.SecurityManagement == CommonDefine.SecurityRankList[(int)CommonDefine.SecurityRankPos.NormalAuthority].Key)

                {

                    // 一般権限

                    userInfo.m_Type = CommonDefine.SecurityRankPos.NormalAuthority;

                }

                // 保護区分範囲

                userInfo.m_SecRange = (SecRangeType)CommonMotions.LoginUserSecurity.SecRange;

                // 総務業務フラグ

                userInfo.m_GeneralAffairsFlg = CommonMotions.LoginUserSecurity.GeneralAffairs;

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

                // 資材返却期限のチェックを行う

                if (!(ProcessFlg = MaterialLimitCheck(m_lastsessionDate))) return;

        #region 資材返却ステータス取得処理

        /// <summary>

        /// 資材返却ステータス取得処理

        /// </summary>

        /// <returns></returns>

        public void MaterualStatusCheck(MaterialRecordInfo checkRec, DateTime CriteriaDate, ref TermMaster TermRec)

        {

            try

            {

                // 対象日付を設定

                DateTime Target = checkRec.RepayPlanDate;

                TermMaster TermMasterData = new TermMaster();

                // 当日のチェック 今日を基準に対象日と比較する

                if (Target.Date == CriteriaDate.Date)

                {

                    TermMasterData.CheckSchdule = (int)CommonDefine.TermDateCheckTiming.CheckSchdule1;

                    TermMasterData.TermDays     = CommonDefine.MaterualStatusCheckList[0].Value;

                    TermMasterData.SendTitle    = CommonDefine.MaterualLimitItem[0, 0];

                    TermMasterData.SendMessage  = CommonDefine.MaterualLimitItem[0, 1];

                    TermMasterData.BackColor    = CommonDefine.MaterualLimitItem[0, 2];

                    TermMasterData.ForeColor    = CommonDefine.MaterualLimitItem[0, 3];

                }

                // 後日以降継続のチェック 今日を基準に経過日（1日）が過ぎているか比較する

                if (Target.AddDays(1).Date <= CriteriaDate.Date)

                {

                    TermMasterData.CheckSchdule = (int)CommonDefine.TermDateCheckTiming.CheckSchdule3;

                    TermMasterData.TermDays     = CommonDefine.MaterualStatusCheckList[1].Value;

                    TermMasterData.SendTitle    = CommonDefine.MaterualLimitItem[1, 0];

                    TermMasterData.SendMessage  = CommonDefine.MaterualLimitItem[1, 1];

                    TermMasterData.BackColor    = CommonDefine.MaterualLimitItem[1, 2];

                    TermMasterData.ForeColor    = CommonDefine.MaterualLimitItem[1, 3];

                }

                TermRec = TermMasterData;

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #region 秘書からのメッセージ作成

        /// <summary>

        /// 秘書からのメッセージ作成

        /// </summary>

        private bool MakeSecretaryMassageData(IOMessageBoardData mbdDB,

                                        IOMessageBoardTerget mbtDB,

                                        IOConstructionBaseInfoDetail cbiDB,

                                        MaterialRecordInfo wrkRec, TermMaster CheckRec, DateTime CriteriaDate)

        {

            try

            {

                // 工事名称・担当者取得

                string strSQL = "SELECT A.DETAILSTRING, C.PERSONCODE CCODE, C.PERSONNAME CNAME";

                strSQL += " FROM ConstructionBaseInfo P";

                strSQL += string.Format(" LEFT JOIN PERSONINCHARGEMASTER C ON C.PERSONCODE = {0}", wrkRec.PersonCode);

                strSQL += " , CONSTRUCTIONBASEINFODETAIL A";

                strSQL += string.Format(" WHERE P.CONSTRUCTIONCODE = {0}", wrkRec.ConstructionCode);

                strSQL += string.Format(" AND A.CONSTRUCTIONCODE = P.CONSTRUCTIONCODE AND A.DETAILNO = {0}", (int)ConstructionBaseInfoDetail.DetailDataNo.ConstructionName);

                ArrayList arList = new ArrayList();

                if (!cbiDB.ExecuteReader(strSQL, ref arList)) return false;

                string DetailString = string.Empty;

                int SalesPersonCode = 0;

                string SalesPersonName = string.Empty;

                int ConstructionPersonCode = 0;

                string ConstructionPersonName = string.Empty;

                foreach (object[] onjwrk in arList)

                {

                    DetailString = CommonMotions.cnvString(onjwrk[0]);

                    ConstructionPersonCode = CommonMotions.cnvInt(onjwrk[1]);

                    ConstructionPersonName = CommonMotions.cnvString(onjwrk[2]);

                    break;

                }

                // 新規メッセージ作成

                MessageBoardData WriteData = new MessageBoardData();

                List<MessageBoardTerget> WriteDetail = new List<MessageBoardTerget>();

                // 掲示板メッセージ

                WriteData.FromCode = CommonDefine.s_MsgBoardSystemCode;	                                    // 書込み者コード

                WriteData.FromName = string.Format(CommonDefine.s_MsgBoardSystemNameFormat, "秘書");        // 書込み者名

                WriteData.MessageTitle = CheckRec.SendTitle;                                                // 伝言タイトル

                // リンク情報

                WriteData.LinkType = (int)CommonDefine.LinkType.ConstructionInfo;

                WriteData.LinkMessage = string.Format("工事番号：{0} 工事名称：{1}"

                                                                            , CommonMotions.cnvStringCodeFromInt(wrkRec.ConstructionCode, "-", 8)

                                                                            , DetailString);

                WriteData.LinkCode = string.Format("{0}", CommonMotions.cnvStringCodeFromInt(wrkRec.ConstructionCode, "-", 8));

                // 伝言内容

                WriteData.MessageContent = CheckRec.SendMessage;

                //WriteData.WritingDate = DateTime.Now;	                                                    // 書込み日付

                WriteData.WritingDate = CriteriaDate.Date + DateTime.Now.TimeOfDay;	                        // 書込み日付

                WriteData.PersonCode = CommonDefine.s_MsgBoardSystemCode;	                                // 書込み担当者コード

                WriteData.ShareFlag = 1;	                                                                // 管理者対象フラグ

                WriteData.BackColor = CheckRec.BackColor;                                                   // 背景色セット

                WriteData.MessageColor = CheckRec.ForeColor;                                                // 背景色セット

                // 掲示板対象者テーブル

                MessageBoardTerget DetailTbl = new MessageBoardTerget();

                DetailTbl.ToCode = ConstructionPersonCode;                                                  // 対象者コード

                DetailTbl.ToName = ConstructionPersonName;	                                                // 対象者

                WriteDetail.Add(DetailTbl);

                // メッセージフラグを自動に設定する

                WriteData.MessageFlag = (int)CommonDefine.MessageFlg.Secretary;

                // 掲示板へメッセージを載せる

                if (!ClsMessageBoradAccess.CreateMessage(mbdDB, mbtDB, WriteData, WriteDetail)) return false;

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 資材返却期限チェック処理

        /// <summary>

        /// チェック処理

        /// </summary>

        /// <returns></returns>

        public bool MaterialLimitCheck(DateTime StartDate)

        {

            IOConstructionBaseInfo cbiDB = new IOConstructionBaseInfo();

            IOConstructionBaseInfoDetail cbiDDB = new IOConstructionBaseInfoDetail();

            IOMessageBoardData mbdDB = new IOMessageBoardData();

            IOMessageBoardTerget mbtDB = new IOMessageBoardTerget();

            string strSQL = "SELECT";

            strSQL += " t1.MaterialItemCode";

            strSQL += ", t1.ProcessDate";

            strSQL += ", t1.SeqNo";

            strSQL += ", t1.RecKind";

            strSQL += ", t1.ConstructionCode";

            strSQL += ", t1.PersonCode";

            strSQL += ", t1.MaterialCount";

            strSQL += ", MAX(t1.REPAYPLANDATE) AS RepayPlanDate";

            strSQL += ", t1.CommentText";

            strSQL += ", t1.EntryDate";

            strSQL += ", t1.UpdateDate";

            strSQL += " FROM";

            strSQL += " MATERIALRECORDINFO AS t1";

            strSQL += " INNER JOIN CONSTRUCTIONMATERIALINFO AS t2";

            strSQL += " ON t1.CONSTRUCTIONCODE = t2.CONSTRUCTIONCODE";

            strSQL += " AND t1.MATERIALITEMCODE = t2.MATERIALITEMCODE";

            strSQL += " AND t2.COMPLETEFLG = 0";

            strSQL += " WHERE";

            strSQL += " t1.CONSTRUCTIONCODE > 0";

            strSQL += " AND t1.CONSTRUCTIONCODE IS NOT NULL";

            strSQL += " AND t1.RECKIND = 0";

            strSQL += " AND t1.REPAYPLANDATE <= DATE_FORMAT(NOW(), '%Y/%m/%d')";

            strSQL += " GROUP BY";

            strSQL += " t1.CONSTRUCTIONCODE";

            strSQL += ", t1.PERSONCODE";

            strSQL += " ORDER BY";

            strSQL += " t1.CONSTRUCTIONCODE";

            strSQL += " , t1.REPAYPLANDATE";

            try

            {

                cbiDB.connect(); cbiDB.beginTran();

                cbiDDB.connect(); cbiDDB.beginTran();

                mbdDB.connect(); mbdDB.beginTran();

                mbtDB.connect(); mbtDB.beginTran();

                bool procflg = true;

                // 資材履歴情報の返却未完了の貸出データを取得する

                IOMaterialRecordInfo mrDB = new IOMaterialRecordInfo();

                ArrayList mrData = new ArrayList();

                List<MaterialRecordInfo> data = new List<MaterialRecordInfo>();

                if (mrDB.ExecuteReader(strSQL, ref mrData))

                {

                    foreach (object[] objwrk in mrData)

                    {

                        MaterialRecordInfo wrkRec = new MaterialRecordInfo();

                        mrDB.Reader2Struct(objwrk, ref wrkRec);

                        // チェック

                        TermMaster term = new TermMaster();

                        MaterualStatusCheck(wrkRec, StartDate, ref term);

                        // メッセージ作成 掲示板にメッセージを載せる処理

                        if (!MakeSecretaryMassageData(mbdDB, mbtDB, cbiDDB, wrkRec, term, StartDate))

                        {

                            procflg = false;

                            break;

                        }

                    }

                    if (procflg)

                    {

                        cbiDB.commit();

                        cbiDDB.commit();

                        mbdDB.commit();

                        mbtDB.commit();

                    }

                    else

                    {

                        cbiDB.rollback();

                        cbiDDB.rollback();

                        mbdDB.rollback();

                        mbtDB.rollback();

                    }

                    return procflg;

                }

            }

            catch (Exception ex)

            {

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

                return false;

            }

            finally

            {

                cbiDB.close(); cbiDB = null;

                cbiDDB.close(); cbiDDB = null;

                mbdDB.close(); mbdDB = null;

                mbtDB.close(); mbtDB = null;

            }

            return true;

        }

        #endregion

                strSQL += " ) AS TMP )"; // MySql対応（Updateの副問合せ対応）

                strSQL += string.Format(" WHERE DATE(STARTDATE) <= STR_TO_DATE('{0}', '%Y/%m/%d') GROUP BY PERSONCODE) B,", DateTime.Today.ToShortDateString());

            BusinessPeriod,

            DepositAmount,

            strcmd += ",BusinessPeriod";

            strcmd += ",DepositAmount";

                    strcmd += string.Format(", {0}", data.BusinessPeriod.ToString());

                    strcmd += string.Format(", {0}", data.DepositAmount.ToString());

                strcmd += string.Format(",BusinessPeriod = {0}", data.BusinessPeriod.ToString());

                strcmd += string.Format(",DepositAmount = {0}", data.DepositAmount.ToString());

                wrk.BusinessPeriod = int.Parse(objwrk[(int)ColumnName.BusinessPeriod].ToString());

                wrk.DepositAmount = int.Parse(objwrk[(int)ColumnName.DepositAmount].ToString());

                strcmd += " ,CAST(TargetDate As Char)";

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Collections;

using log4net;

using MySql.Data.MySqlClient;

using ProcessManagement.DB.MySql;

using ProcessManagement.DB.Core;

using ProcessManagement.DataModel;

using ProcessManagement.Common;

namespace ProcessManagement.DB.IOAccess

{

    /// <summary>

    /// 請求書データアクセスクラス

    /// </summary>

    public class IOInvoiceData : MySqlProcess

    {

        #region 定義部

        /// <summary>

        /// log4netログを使用する

        /// </summary>

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

        #endregion

        #region 定数

        /// <summary>

        /// 請求データフィールド並び

        /// </summary>

        public enum NameColumn

        {

            InvoiceNo = 0,

            OrderersDivision,

            OrderersCode,

            RequestMonth,

            SeqNo,

            CretateDate,

            RequestDate,

            RequestName,

            TotalAmount,

            TaxAmount,

            Comment1,

            Comment2,

            Comment3,

            Comment4,

            Comment5,

            CompleteFlg,

            ENTRY_DATE,

            UPDATE_DATE,

        }

        #endregion

        #region コンストラクタ

        /// <summary>

        /// コンストラクタ

        /// </summary>

        /// <param name="connectionString"></param>

        public IOInvoiceData()

            : base(DBCommon.Instance.DBConnectString)

        {

        }

        #endregion

        #region 請求書データ検索(１件取得)

        /// <summary>

        /// 請求書データ検索(複数件取得)

        /// </summary>

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

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

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

        /// <returns></returns>

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

        {

            //Oracle インターフェース

            string strcmd = "";

            ArrayList arData = new ArrayList();

            try

            {

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

                strcmd = CreateSelectSQL();

                strcmd += AddSQLString;

                // SQL実行

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

                // データセット

                foreach (object[] objwrk in arData)

                {

                    Reader2Struct(objwrk, ref data);

                    break;

                }

                return true;

            }

            catch (Exception ex)

            {

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

                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 List<InvoiceData> data, bool bConnect = true)

        {

            //Oracle インターフェース

            string strcmd = "";

            ArrayList arData = new ArrayList();

            try

            {

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

                strcmd = CreateSelectSQL();

                strcmd += AddSQLString;

                // SQL実行

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

                // データセット

                foreach (object[] objwrk in arData)

                {

                    InvoiceData work = new InvoiceData();

                    Reader2Struct(objwrk, ref work);

                    data.Add(work);

                }

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 請求書データ複数追加

        /// <summary>

        /// 請求書データ複数追加

        /// </summary>

        /// <param name="data">請求データ</param>

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

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

        {

            string strcmd = "";

            try

            {

                foreach (InvoiceData work in data)

                {

                    if (!InsertAction(work, bConnect)) return false;

                }

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 請求書データ１件追加

        /// <summary>

        /// 請求書データ１件追加

        /// </summary>

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

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

        /// <returns></returns>

        public bool InsertAction(InvoiceData data, bool bConnect = true)

        {

            string strcmd = "";

            try

            {

                strcmd = "INSERT INTO InvoiceData";

                strcmd += " VALUES (";

                strcmd += string.Format(" {0}", data.InvoiceNo.ToString());

                strcmd += string.Format(", {0}", data.OrderersDivision.ToString());

                strcmd += string.Format(", {0}", data.OrderersCode.ToString());

                strcmd += string.Format(", {0}", data.RequestMonth.ToString());

                strcmd += string.Format(", {0}", data.SeqNo.ToString());

                strcmd += string.Format(", STR_TO_DATE('{0}','%Y/%m/%d')", data.CretateDate.ToShortDateString());

                strcmd += string.Format(", STR_TO_DATE('{0}','%Y/%m/%d')", data.RequestDate.ToShortDateString());

                strcmd += string.Format(", '{0}'", data.RequestName.ToString());

                strcmd += string.Format(", {0}", data.TotalAmount.ToString());    

                strcmd += string.Format(", {0}", data.TaxAmount.ToString());

                strcmd += string.Format(", '{0}'", data.Comment1.ToString());

                strcmd += string.Format(", '{0}'", data.Comment2.ToString());

                strcmd += string.Format(", '{0}'", data.Comment3.ToString());

                strcmd += string.Format(", '{0}'", data.Comment4.ToString());

                strcmd += string.Format(", '{0}'", data.Comment5.ToString());

                strcmd += string.Format(", {0}", data.CompleteFlg.ToString());

                strcmd += ", DATE_FORMAT(DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s'),'%Y/%m/%d %H:%i:%s')";

                strcmd += ", DATE_FORMAT(DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s'),'%Y/%m/%d %H:%i:%s')";

                strcmd = strcmd + ")";

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

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 請求書データ更新

        /// <summary>

        /// 請求データ更新

        /// </summary>

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

        /// <param name="data">請求書データ</param>

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

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

        {

            string strcmd = "";

            try

            {

                strcmd = "UPDATE InvoiceData";

                strcmd += " SET";

                strcmd += string.Format(" OrderersDivision = {0}", data.OrderersDivision.ToString());

                strcmd += string.Format(", OrderersCode = {0}", data.OrderersCode.ToString());

                strcmd += string.Format(", RequestMonth = {0}", data.RequestMonth.ToString());

                strcmd += string.Format(", SeqNo = {0}", data.SeqNo.ToString());

                strcmd += string.Format(", CretateDate = STR_TO_DATE('{0}','%Y/%m/%d')", data.CretateDate.ToShortDateString());

                strcmd += string.Format(", RequestDate = STR_TO_DATE('{0}','%Y/%m/%d')", data.RequestDate.ToShortDateString());

                strcmd += string.Format(", RequestName = '{0}'", data.RequestName.ToString());

                strcmd += string.Format(", TotalAmount = {0}", data.TotalAmount.ToString());

                strcmd += string.Format(", TaxAmount = {0}", data.TaxAmount.ToString());

                strcmd += string.Format(", Comment1 = '{0}'", data.Comment1.ToString());

                strcmd += string.Format(", Comment2 = '{0}'", data.Comment2.ToString());

                strcmd += string.Format(", Comment3 = '{0}'", data.Comment3.ToString());

                strcmd += string.Format(", Comment4 = '{0}'", data.Comment4.ToString());

                strcmd += string.Format(", Comment5 = '{0}'", data.Comment5.ToString());

                strcmd += string.Format(", CompleteFlg = {0}", data.CompleteFlg.ToString());

                strcmd += ", UpdateDate = DATE_FORMAT(DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s'),'%Y/%m/%d %H:%i:%s')";

                strcmd += AddSQLString;

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

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

        #region 請求書データ削除

        /// <summary>

        /// 請求データ削除

        /// </summary>

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

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

        /// <returns></returns>

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

        {

            //Oracle インターフェース

            string strcmd = "";

            try

            {

                strcmd = string.Format("{0}{1}", "DELETE FROM InvoiceData", AddSQLString);

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

                return true;

            }

            catch (Exception ex)

            {

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

                return false;

            }

        }

        #endregion

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

        /// <summary>

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

        /// </summary>

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

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

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

        {

            try

            {

                // データ取得

                wrk.InvoiceNo = int.Parse(objwrk[(int)NameColumn.InvoiceNo].ToString());

                wrk.OrderersDivision = int.Parse(objwrk[(int)NameColumn.OrderersDivision].ToString());

                wrk.OrderersCode = int.Parse(objwrk[(int)NameColumn.OrderersCode].ToString());

                wrk.RequestMonth = int.Parse(objwrk[(int)NameColumn.RequestMonth].ToString());

                wrk.SeqNo = int.Parse(objwrk[(int)NameColumn.SeqNo].ToString());

                wrk.CretateDate = DateTime.Parse(objwrk[(int)NameColumn.CretateDate].ToString());

                wrk.RequestDate = DateTime.Parse(objwrk[(int)NameColumn.RequestDate].ToString());

                wrk.RequestName = objwrk[(int)NameColumn.RequestName].ToString();

                wrk.TotalAmount = int.Parse(objwrk[(int)NameColumn.TotalAmount].ToString());

                wrk.TaxAmount = int.Parse(objwrk[(int)NameColumn.TaxAmount].ToString());

                wrk.Comment1 = objwrk[(int)NameColumn.Comment1].ToString();

                wrk.Comment2 = objwrk[(int)NameColumn.Comment2].ToString();

                wrk.Comment3 = objwrk[(int)NameColumn.Comment3].ToString();

                wrk.Comment4 = objwrk[(int)NameColumn.Comment4].ToString();

                wrk.Comment5 = objwrk[(int)NameColumn.Comment5].ToString();

                wrk.CompleteFlg = int.Parse(objwrk[(int)NameColumn.CompleteFlg].ToString());

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

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

            }

            catch (MySqlException myex)

            {

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

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

        #region 構造体クリア

        /// <summary>

        /// 構造体クリア

        /// </summary>

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

        public void ClearStruct(ref InvoiceData wrk)

        {

            try

            {

                // データ取得

                wrk.InvoiceNo = 0;

                wrk.OrderersDivision = 0;

                wrk.OrderersCode = 0;

                wrk.RequestMonth = 0;

                wrk.SeqNo = 0;

                wrk.CretateDate = DateTime.MinValue;

                wrk.RequestDate = DateTime.MinValue;

                wrk.RequestName = "";

                wrk.TotalAmount = 0;

                wrk.TaxAmount = 0;

                wrk.Comment1 = "";

                wrk.Comment2 = "";

                wrk.Comment3 = "";

                wrk.Comment4 = "";

                wrk.Comment5 = "";

                wrk.CompleteFlg = 0;

                wrk.EntryDate = DateTime.Now;

                wrk.UpdateDate = DateTime.Now;

            }

            catch (MySqlException myex)

            {

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

            }

            catch (Exception ex)

            {

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

            }

        }

        #endregion

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

        /// <summary>

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

        /// </summary>

        /// <param name="InvoiceNo">請求書No</param>

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

        public string CreatePrimarykeyString(int InvoiceNo)

        {

            string strWork = string.Empty;

            try

            {

                strWork = string.Format(" Where InvoiceNo = {0}", InvoiceNo.ToString());

            }

            catch (Exception ex)

            {

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

            }

            return strWork;

        }

        #endregion

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

        /// <summary>

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

        /// </summary>

        /// <param name="OrderersDivision">発注者区分</param>

        /// <param name="OrderersCode">発注者コード</param>

        /// <param name="RequestMonth">請求月</param>

        /// <param name="SeqNo">連番</param>

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

        public string CreateSubkeyString(int OrderersDivision, int OrderersCode, int RequestMonth = -1, int SeqNo = -1 )

        {

            string strWork = string.Empty;

            try

            {

                strWork = string.Format(" Where OrderersDivision = {0} and OrderersCode = {1}", OrderersDivision.ToString(),OrderersCode.ToString() );

                if( RequestMonth != -1 ){

                    strWork += string.Format(" and RequestMonth = {0}", RequestMonth.ToString() ) ;

                }

                if( SeqNo != -1 ){

                    strWork += string.Format(" and SeqNo = {0}", SeqNo.ToString() ) ;

                }

            }

            catch (Exception ex)

            {

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

            }

            return strWork;

        }

        #endregion

        #region 請求書Noの採番

        public int NumberingInvoiceNo(int RequestMonth, bool bConnect = true)

        {

            int ret = -1;

            string sql = "";

            ArrayList arData = new ArrayList();

            try

            {

                sql = string.Format("SELECT IFNULL(MAX(InvoiceNo),0) FROM InvoiceData WHERE RequestMonth = {0}", RequestMonth);

                // SQL実行

                if (!ExecuteReader(sql, ref arData, bConnect)) return -1;

                // データセット

                foreach (object[] objwrk in arData)

                {

                    ret = int.Parse(objwrk[0].ToString());

                    break;

                }

                if (ret > 0)

                {

                    ret++;

                }

                else

                {

                    ret = (RequestMonth * 1000) + 1;

                }

            }

            catch (Exception ex)

            {

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

            }

            return ret;

        }

        #endregion

        #region 連番の採番

        public int NumberingSeqNo(int nOrderersDivision, int nOrderersCode, int RequestMonth, bool bConnect = true)

        {

            int ret = -1;

            string sql = "";

            ArrayList arData = new ArrayList();

            try

            {

                sql = "SELECT IFNULL(MAX(SeqNo),0) FROM InvoiceData" ;

                sql += string.Format(" WHERE OrderersDivision = {0}", nOrderersDivision);

                sql += string.Format(" AND OrderersCode = {0}", nOrderersCode);

                sql += string.Format(" AND RequestMonth = {0}", RequestMonth);

                // SQL実行

                if (!ExecuteReader(sql, ref arData, bConnect)) return -1;

                // データセット

                foreach (object[] objwrk in arData)

                {

                    ret = int.Parse(objwrk[0].ToString());

                    break;

                }

                ret++;

            }

            catch (Exception ex)

            {

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

            }

            return ret;

        }

        #endregion

        #region SELECT　SQL作成

        private string CreateSelectSQL()

        {

            string strcmd = "SELECT";

            strcmd += " InvoiceNo";

            strcmd += " ,OrderersDivision";

            strcmd += " ,OrderersCode";

            strcmd += " ,RequestMonth";

            strcmd += " ,SeqNo";

            strcmd += " ,DATE_FORMAT(CretateDate, '%Y/%m/%d')";

            strcmd += " ,DATE_FORMAT(RequestDate, '%Y/%m/%d')";

            strcmd += " ,RequestName";

            strcmd += " ,TotalAmount";

            strcmd += " ,TaxAmount";

            strcmd += " ,Comment1";

            strcmd += " ,Comment2";

            strcmd += " ,Comment3";

            strcmd += " ,Comment4";

            strcmd += " ,Comment5";

            strcmd += " ,CompleteFlg";

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

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

            strcmd += " FROM InvoiceData";

            return strcmd;

        }

        #endregion

    }

}

            MainConstructionCode,

            ConstructionName,

            ContractAmount,

            PaidAmount,

            RequestAmount0,

            RequestAmount1,

            RequestAmount2,

            RequestAmount3,

            RequestAmount4,

            RequestAmount5,

            RequestAmount6,

            UnclaimedAmount,

            UnpaidAmount,

            TaxAmount,

            Note,

                strcmd += " ,MainConstructionCode";

                strcmd += " ,ConstructionName";

                strcmd += " ,ContractAmount";

                strcmd += " ,PaidAmount";

                strcmd += " ,RequestAmount0";

                strcmd += " ,RequestAmount1";

                strcmd += " ,RequestAmount2";

                strcmd += " ,RequestAmount3";

                strcmd += " ,RequestAmount4";

                strcmd += " ,RequestAmount5";

                strcmd += " ,RequestAmount6";

                strcmd += " ,UnclaimedAmount";

                strcmd += " ,UnpaidAmount";

                strcmd += " ,TaxAmount";

                strcmd += " ,Note";

                strcmd += string.Format(", {0}", data.MainConstructionCode.ToString());

                strcmd += string.Format(", '{0}'", data.ConstructionName.ToString());

                strcmd += string.Format(", {0}", data.ContractAmount.ToString());

                strcmd += string.Format(", {0}", data.PaidAmount.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount0.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount1.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount2.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount3.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount4.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount5.ToString());

                strcmd += string.Format(", {0}", data.RequestAmount6.ToString());

                strcmd += string.Format(", {0}", data.UnclaimedAmount.ToString());

                strcmd += string.Format(", {0}", data.UnpaidAmount.ToString());

                strcmd += string.Format(", {0}", data.TaxAmount.ToString());

                strcmd += string.Format(", '{0}'", data.Note.ToString());

                strcmd += string.Format(" ConstructionName = '{0}'", data.ConstructionName.ToString());

                strcmd += string.Format(",ContractAmount = {0}", data.ContractAmount.ToString());

                strcmd += string.Format(",PaidAmount = {0}", data.PaidAmount.ToString());

                strcmd += string.Format(",RequestAmount0 = {0}", data.RequestAmount0.ToString());

                strcmd += string.Format(",RequestAmount1 = {0}", data.RequestAmount1.ToString());

                strcmd += string.Format(",RequestAmount2 = {0}", data.RequestAmount2.ToString());

                strcmd += string.Format(",RequestAmount3 = {0}", data.RequestAmount3.ToString());

                strcmd += string.Format(",RequestAmount4 = {0}", data.RequestAmount4.ToString());

                strcmd += string.Format(",RequestAmount5 = {0}", data.RequestAmount5.ToString());

                strcmd += string.Format(",RequestAmount6 = {0}", data.RequestAmount6.ToString());

                strcmd += string.Format(",UnclaimedAmount = {0}", data.UnclaimedAmount.ToString());

                strcmd += string.Format(",UnpaidAmount = {0}", data.UnpaidAmount.ToString());

                strcmd += string.Format(",TaxAmount = {0}", data.TaxAmount.ToString());

                strcmd += string.Format(",Note = '{0}'", data.Note.ToString());

                strcmd += string.Format(" Where RequestNo = {0} And MainConstructionCode = {1}", data.RequestNo.ToString(), data.MainConstructionCode.ToString());

        #region 請求データ更新（複数件更新）

        /// <summary>

        /// 請求データ更新（複数件更新）

        /// </summary>

        /// <param name="lst">請求データリスト</param>

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

        public bool UpdateAction(List<RequestData> lst, bool bConnect = true)

        {

            string sql;

            foreach( RequestData data in lst )

            {

                sql = CreatePrimarykeyString( data.RequestNo, data.MainConstructionCode );

                if( UpdateAction( sql, data, bConnect ) == false)

                {

                    return false;

                }

            }

            return true;

        }

        #endregion

                wrk.MainConstructionCode = int.Parse(objwrk[(int)NameColumn.MainConstructionCode].ToString());

                wrk.ConstructionName = objwrk[(int)NameColumn.ConstructionName].ToString();

                wrk.ContractAmount = int.Parse(objwrk[(int)NameColumn.ContractAmount].ToString());

                wrk.PaidAmount = int.Parse(objwrk[(int)NameColumn.PaidAmount].ToString());

                wrk.RequestAmount0 = int.Parse(objwrk[(int)NameColumn.RequestAmount0].ToString());

                wrk.RequestAmount1 = int.Parse(objwrk[(int)NameColumn.RequestAmount1].ToString());

                wrk.RequestAmount2 = int.Parse(objwrk[(int)NameColumn.RequestAmount2].ToString());

                wrk.RequestAmount3 = int.Parse(objwrk[(int)NameColumn.RequestAmount3].ToString());

                wrk.RequestAmount4 = int.Parse(objwrk[(int)NameColumn.RequestAmount4].ToString());

                wrk.RequestAmount5 = int.Parse(objwrk[(int)NameColumn.RequestAmount5].ToString());

                wrk.RequestAmount6 = int.Parse(objwrk[(int)NameColumn.RequestAmount6].ToString());

                wrk.UnclaimedAmount = int.Parse(objwrk[(int)NameColumn.UnclaimedAmount].ToString());

                wrk.UnpaidAmount = int.Parse(objwrk[(int)NameColumn.UnpaidAmount].ToString());

                wrk.TaxAmount = int.Parse(objwrk[(int)NameColumn.TaxAmount].ToString());

                wrk.Note = objwrk[(int)NameColumn.Note].ToString();