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

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 IORequestTotalData : MySqlProcess

    {

        #region 定義部

        /// <summary>

        /// log4netログを使用する

        /// </summary>

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

        #endregion

        #region 定数

        /// <summary>

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

        /// </summary>

        public enum NameColumn

        {

            RequestNo = 0,

            OrderNo,

            OrderersDivision,

            OrderersCode,

            BillCreationDate,

            BillTotalPrice,

            BillPrice,

            TaxPrice,

            AmountBlance,

            AmountPaid,

            PricePaid,

            TaxPricePaid,

            PriceBalance,

            TaxBalance,

            AssignedFlg,

            ENTRY_DATE,

            UPDATE_DATE,

        }

        #endregion

        #region コンストラクタ

        public IORequestTotalData()

            : base(DBCommon.Instance.DBConnectString)

        {

        }

        #endregion

        #region 請求合計データ検索

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

        {

            //Oracle インターフェース

            string strcmd = "";

            ArrayList arData = new ArrayList();

            try

            {

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

                strcmd = "SELECT";

                strcmd += " RequestNo";

                strcmd += " ,OrderNo";

                strcmd += " ,OrderersDivision";

                strcmd += " ,OrderersCode";

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

                strcmd += " ,BillTotalPrice";

                strcmd += " ,BillPrice";

                strcmd += " ,TaxPrice";

                strcmd += " ,AmountBlance";

                strcmd += " ,AmountPaid";

                strcmd += " ,PricePaid";

                strcmd += " ,TaxPricePaid";

                strcmd += " ,PriceBalance";

                strcmd += " ,TaxBalance";

                strcmd += " ,AssignedFlg";

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

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

                strcmd += " FROM RequestTotalData";

                strcmd += AddSQLString;

                // SQL実行

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

                // データセット

                foreach (object[] objwrk in arData)

                {

                    RequestTotalData work = new RequestTotalData();

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

        {

            string strcmd = "";

            try

            {

                foreach (RequestTotalData 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(RequestTotalData data, bool bConnect = true)

        {

            string strcmd = "";

            try

            {

                strcmd = "INSERT INTO RequestTotalData";

                strcmd += " VALUES (";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                strcmd += string.Format(", {0}", (data.AssignedFlg ? 1 : 0).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, RequestTotalData data, bool bConnect = true)

        {

            string strcmd = "";

            try

            {

                strcmd = "UPDATE RequestTotalData";

                strcmd += " SET";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                strcmd += string.Format(",AssignedFlg = {0}", (data.AssignedFlg ? 1 : 0).ToString());

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

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

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

                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 RequestTotalData", 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 RequestTotalData wrk)

        {

            try

            {

                // データ取得

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                wrk.AssignedFlg = int.Parse(objwrk[(int)NameColumn.TaxBalance].ToString()) == 1 ? true : false;

                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 RequestTotalData wrk)

        {

            try

            {

                // データ取得

                wrk.RequestNo = 0;

                wrk.OrderNo = 0;

                wrk.OrderersDivision = 0;

                wrk.OrderersCode = 0;

                wrk.BillCreationDate = DateTime.MinValue;

                wrk.BillTotalPrice = 0;

                wrk.BillPrice = 0;

                wrk.TaxPrice = 0;

                wrk.AmountBlance = 0;

                wrk.AmountPaid = 0;

                wrk.PricePaid = 0;

                wrk.TaxPricePaid = 0;

                wrk.PriceBalance = 0;

                wrk.TaxBalance = 0;

                wrk.AssignedFlg = false;

                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="BusinessTypeCode"></param>

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

        public string CreatePrimarykeyString(int RequestNo, int OrderNo = 0)

        {

            string strWork = string.Empty;

            try

            {

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

                if (OrderNo != 0)

                    strWork += string.Format(" And OrderNo = {0}", OrderNo.ToString());

            }

            catch (Exception ex)

            {

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

            }

            return strWork;

        }

        #endregion

    }

}