/// <summary>
/// 売掛締チェック
/// </summary>
/// <param name="companyId"></param>
/// <param name="db"></param>
/// <param name="customerId"></param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static bool IsInvoiceClose(string companyId,
ExMySQLData db,
string invoiceId,
string ymd)
{
StringBuilder sb = new StringBuilder();
DataTable dt;
if (ymd.Length != 10) throw new Exception();
if (!ExCast.IsDate(ymd)) throw new Exception();
try
{
sb.Length = 0;
sb.Append("SELECT T.NO " + Environment.NewLine);
sb.Append(" FROM T_INVOICE AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.INVOICE_KBN = 0 " + Environment.NewLine); // 請求区分:締処理
sb.Append(" AND T.INVOICE_YYYYMMDD >= " + ExEscape.zRepStr(ymd) + Environment.NewLine);
if (invoiceId != "")
{
sb.Append(" AND T.INVOICE_ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(invoiceId)) + Environment.NewLine);
}
sb.Append(" LIMIT 0, 1");
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".IsInvoiceClose", ex);
throw;
}
}
/// <summary>
/// 最大ID取得
/// </summary>
/// <param name="db"></param>
/// <param name="id">ID</param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static void GetMaxId(ExMySQLData db,
string tbl_name,
string id_col_name,
out string id)
{
StringBuilder sb;
DataTable dt;
bool exist = false;
try
{
sb = new StringBuilder();
sb.Length = 0;
// 存在確認
sb.Append("SELECT " + id_col_name + " AS MAX_ID " + Environment.NewLine);
sb.Append(" FROM " + tbl_name + Environment.NewLine);
sb.Append(" ORDER BY " + id_col_name + " DESC " + Environment.NewLine);
sb.Append(" LIMIT 0, 1 " + Environment.NewLine);
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
id = (ExCast.zCLng(dt.DefaultView[0]["MAX_ID"]) + 1).ToString();
}
else
id = "1";
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetMaxId", ex);
id = "";
throw;
}
}
public string UpdatePayment(string random, int type, List<EntityPaymentClose> entity)
{
#region 認証処理
string companyId = "";
string groupId = "";
string userId = "";
string ipAdress = "";
string sessionString = "";
string personId = "";
try
{
companyId = ExCast.zCStr(HttpContext.Current.Session[ExSession.COMPANY_ID]);
groupId = ExCast.zCStr(HttpContext.Current.Session[ExSession.GROUP_ID]);
userId = ExCast.zCStr(HttpContext.Current.Session[ExSession.USER_ID]);
ipAdress = ExCast.zCStr(HttpContext.Current.Session[ExSession.IP_ADRESS]);
sessionString = ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]);
personId = ExCast.zCStr(HttpContext.Current.Session[ExSession.PERSON_ID]);
string _message = ExSession.SessionUserUniqueCheck(random, ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]), ExCast.zCInt(HttpContext.Current.Session[ExSession.USER_ID]));
if (_message != "")
{
return _message;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(認証処理)", ex);
return CLASS_NM + ".UpdatePayment : 認証処理に失敗しました。" + Environment.NewLine + ex.Message.ToString();
}
#endregion
#region Field
StringBuilder sb = new StringBuilder();
DataTable dt;
ExMySQLData db = null;
string _Id = "";
int _classKbn = 0;
#endregion
#region Databese Open
try
{
db = new ExMySQLData(ExCast.zCStr(HttpContext.Current.Session[ExSession.DB_CONNECTION_STR]));
db.DbOpen();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(DbOpen)", ex);
return CLASS_NM + ".UpdatePayment(DbOpen) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region BeginTransaction
try
{
db.ExBeginTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(BeginTransaction)", ex);
return CLASS_NM + ".UpdatePayment(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Delete
if (type == 2)
{
try
{
for (int i = 0; i <= entity.Count - 1; i++)
{
if (entity[i].exec_flg == true)
{
#region 支払データ存在確認
#region SQL
sb.Length = 0;
sb.Append("SELECT T.NO " + Environment.NewLine);
sb.Append(" FROM T_PAYMENT AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.PAYMENT_KBN = 0 " + Environment.NewLine); // 締支払分のみ
sb.Append(" AND T.PAYMENT_YYYYMMDD > " + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine);
sb.Append(" AND T.PURCHASE_ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].purchase_id)) + Environment.NewLine);
sb.Append(" LIMIT 0, 1");
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
db.ExRollbackTransaction();
return CLASS_NM + "仕入先 " + entity[i].purchase_id + "：" + entity[i].purchase_nm + Environment.NewLine +
"支払番号 : " + entity[i].no + "は" + Environment.NewLine +
"支払締日 : " + ExCast.zFormatToDate_YYYYMM(entity[0].payment_close_yyyymmdd) + " 以降のデータが存在する為、削除できません。";
}
#endregion
#region 出金データ存在確認
//#region SQL
//sb.Length = 0;
//sb.Append("SELECT T.ID " + Environment.NewLine);
//sb.Append(" FROM T_PAYMENT_CASH_H AS T" + Environment.NewLine);
//sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
//sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
//sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
//sb.Append(" AND T.PURCHASE_ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].purchase_id)) + Environment.NewLine);
//sb.Append(" AND T.PAYMENT_CASH_YMD <= " + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine);
//#endregion
//dt = db.GetDataTable(sb.ToString());
//if (dt.DefaultView.Count > 0)
//{
// return CLASS_NM + ".UpdatePayment(Delete Check) : 仕入先 " + entity[i].purchase_id + "：" + entity[i].purchase_nm +
// " 支払締日 : " + entity[0].payment_close_yyyymmdd + " 以前の出金データが存在する為、削除できません。";
//}
#endregion
#region 出金消込データ存在確認
#region SQL
sb.Length = 0;
sb.Append("SELECT T.ID " + Environment.NewLine);
sb.Append(" FROM T_PAYMENT_CASH_H AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.PAYMENT_NO = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].no)) + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
db.ExRollbackTransaction();
return "仕入先 " + entity[i].purchase_id + "：" + entity[i].purchase_nm + Environment.NewLine +
"支払番号 : " + entity[i].no + "は出金消込済の為、削除できません。";
}
#endregion
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM T_PAYMENT " + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND PAYMENT_KBN = 0 " + Environment.NewLine); // 締支払分のみ
sb.Append(" AND NO = " + ExCast.zCLng(entity[i].no) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Update Purchase
sb.Length = 0;
sb.Append("UPDATE T_PURCHASE_H " + Environment.NewLine);
sb.Append(" SET PAYMENT_NO = 0" + Environment.NewLine);
sb.Append(" WHERE DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND BUSINESS_DIVISION_ID = 1 " + Environment.NewLine); // 掛仕入のみ
sb.Append(" AND PURCHASE_YMD <= " + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine);
sb.Append(" AND PURCHASE_ID = " + ExEscape.zRepStr(entity[i].purchase_id) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(Delete)", ex);
return CLASS_NM + ".UpdatePayment(Delete) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region Insert
if (type == 1)
{
#region Get Max Payment No
long no = 0;
try
{
no = DataSlipNo.GetMaxSlipNo(companyId, groupId, db, "T_PAYMENT", "NO");
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(Get Max No)", ex);
return CLASS_NM + ".UpdatePayment(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
try
{
for (int i = 0; i <= entity.Count - 1; i++)
{
if (entity[i].exec_flg == true)
{
#region 支払データ存在確認
#region SQL
sb.Length = 0;
sb.Append("SELECT T.NO " + Environment.NewLine);
sb.Append(" FROM T_PAYMENT AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.PAYMENT_KBN = 0 " + Environment.NewLine); // 締支払分のみ
sb.Append(" AND T.PAYMENT_YYYYMMDD > " + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine);
sb.Append(" AND T.PURCHASE_ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].purchase_id)) + Environment.NewLine);
sb.Append(" LIMIT 0, 1");
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
db.ExRollbackTransaction();
return CLASS_NM + "仕入先 " + entity[i].purchase_id + "：" + entity[i].purchase_nm + "は" + Environment.NewLine +
"支払締日 : " + ExCast.zFormatToDate_YYYYMM(entity[0].payment_close_yyyymmdd) + " 以降のデータが存在する為、削除できません。";
}
#endregion
if (entity[i].no != "未")
{
#region 出金消込データ存在確認
#region SQL
sb.Length = 0;
sb.Append("SELECT T.ID " + Environment.NewLine);
sb.Append(" FROM T_PAYMENT_CASH_H AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.PAYMENT_NO = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].no)) + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
db.ExRollbackTransaction();
return "仕入先 " + entity[i].purchase_id + "：" + entity[i].purchase_nm + Environment.NewLine +
"支払番号 : " + entity[i].no + "は出金消込済の為、削除できません。";
}
#endregion
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM T_PAYMENT " + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND PAYMENT_KBN = 0 " + Environment.NewLine); // 締支払分のみ
sb.Append(" AND NO = " + ExCast.zCLng(entity[i].no) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
#region Insert SQL
sb.Length = 0;
sb.Append("INSERT INTO T_PAYMENT " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , GROUP_ID" + Environment.NewLine);
sb.Append(" , NO" + Environment.NewLine);
sb.Append(" , PURCHASE_ID" + Environment.NewLine);
sb.Append(" , PAYMENT_YYYYMMDD" + Environment.NewLine);
sb.Append(" , SUMMING_UP_GROUP_ID" + Environment.NewLine);
sb.Append(" , BEFORE_PAYMENT_YYYYMMDD" + Environment.NewLine);
sb.Append(" , PERSON_ID" + Environment.NewLine);
sb.Append(" , PAYMENT_CYCLE_ID" + Environment.NewLine);
sb.Append(" , PAYMENT_DAY" + Environment.NewLine);
sb.Append(" , PAYMENT_PLAN_DAY" + Environment.NewLine);
sb.Append(" , BEFORE_PAYMENT_PRICE" + Environment.NewLine);
sb.Append(" , PAYMENT_CASH_PRICE" + Environment.NewLine);
sb.Append(" , TRANSFER_PRICE" + Environment.NewLine);
sb.Append(" , PURCHASE_PRICE" + Environment.NewLine);
sb.Append(" , NO_TAX_PURCHASE_PRICE" + Environment.NewLine);
sb.Append(" , TAX" + Environment.NewLine);
sb.Append(" , PAYMENT_PRICE" + Environment.NewLine);
sb.Append(" , MEMO" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + groupId + Environment.NewLine); // GROUP_ID
if (entity[i].no != "未")
{
sb.Append(" ," + ExCast.zCLng(entity[i].no) + Environment.NewLine); // NO
}
else
{
sb.Append(" ," + no.ToString() + Environment.NewLine); // NO
}
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity[i].purchase_id)) + Environment.NewLine); // PURCHASE_ID
sb.Append(" ," + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine); // PAYMENT_YYYYMMDD
sb.Append(" ," + ExEscape.zRepStr(entity[i].summing_up_group_id) + Environment.NewLine); // SUMMING_UP_DAY
sb.Append(" ," + ExEscape.zRepStr(entity[i].before_payment_yyyymmdd) + Environment.NewLine); // BEFORE_PAYMENT_YYYYMMDD
sb.Append(" ," + entity[i].person_id + Environment.NewLine); // PERSON_ID
sb.Append(" ," + entity[i].payment_cycle_id + Environment.NewLine); // PAYMENT_CYCLE_ID
sb.Append(" ," + entity[i].payment_day + Environment.NewLine); // PAYMENT_DAY
sb.Append(" ," + ExEscape.zRepStr(entity[i].payment_plan_day) + Environment.NewLine); // PAYMENT_PLAN_DAY
sb.Append(" ," + entity[i].before_payment_price + Environment.NewLine); // BEFORE_PAYMENT_PRICE
sb.Append(" ," + entity[i].payment_cash_price + Environment.NewLine); // PAYMENT_CASH_PRICE
sb.Append(" ," + entity[i].transfer_price + Environment.NewLine); // TRANSFER_PRICE
sb.Append(" ," + entity[i].purchase_price + Environment.NewLine); // PURCHASE_PRICE
sb.Append(" ," + entity[i].no_tax_purchase_price + Environment.NewLine); // NO_TAX_PURCHASE_PRICE
sb.Append(" ," + entity[i].tax + Environment.NewLine); // TAX
sb.Append(" ," + entity[i].payment_price + Environment.NewLine); // PAYMENT_PRICE
sb.Append(" ," + ExEscape.zRepStr(entity[i].memo) + Environment.NewLine); // MEMO
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"T_PAYMENT",
ExCast.zCInt(personId),
"",
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Update Purchase
sb.Length = 0;
sb.Append("UPDATE T_PURCHASE_H " + Environment.NewLine);
if (entity[i].no != "未")
{
sb.Append(" SET PAYMENT_NO = " + ExCast.zCLng(entity[i].no) + Environment.NewLine);
}
else
{
sb.Append(" SET PAYMENT_NO = " + no.ToString() + Environment.NewLine);
}
sb.Append(" WHERE DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND BUSINESS_DIVISION_ID = 1 " + Environment.NewLine); // 掛仕入のみ
sb.Append(" AND PURCHASE_YMD <= " + ExEscape.zRepStr(entity[i].payment_close_yyyymmdd) + Environment.NewLine);
sb.Append(" AND PURCHASE_ID = " + ExEscape.zRepStr(entity[i].purchase_id) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
no += 1;
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(Insert)", ex);
return CLASS_NM + ".UpdatePayment(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region PG排他制御
//try
//{
// DataPgLock.DelLockPg(companyId, userId, PG_NM, "", ipAdress, false, db);
//}
//catch (Exception ex)
//{
// db.ExRollbackTransaction();
// CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(DelLockPg)", ex);
// return CLASS_NM + ".UpdatePayment(DelLockPg) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
//}
#endregion
#region CommitTransaction
try
{
db.ExCommitTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(CommitTransaction)", ex);
return CLASS_NM + ".UpdatePayment(CommitTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Database Close
try
{
db.DbClose();
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(DbClose)", ex);
return CLASS_NM + ".UpdatePayment(DbClose) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
finally
{
db = null;
}
#endregion
#region Add Evidence
try
{
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.DeleteAndInsert,
"");
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdatePayment(Add Evidence)", ex);
return CLASS_NM + ".UpdatePayment(Add Evidence) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
if (type == 2)
{
return "削除しました。";
}
else if (type == 1)
{
return "支払締切を実行しました。";
}
else
{
return "";
}
}
public string UpdateCustomer(string random, int type, string Id, EntityCustomer entity)
{
#region 認証処理
string companyId = "";
string groupId = "";
string userId = "";
string ipAdress = "";
string sessionString = "";
string personId = "";
try
{
companyId = ExCast.zCStr(HttpContext.Current.Session[ExSession.COMPANY_ID]);
groupId = ExCast.zCStr(HttpContext.Current.Session[ExSession.GROUP_ID]);
userId = ExCast.zCStr(HttpContext.Current.Session[ExSession.USER_ID]);
ipAdress = ExCast.zCStr(HttpContext.Current.Session[ExSession.IP_ADRESS]);
sessionString = ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]);
personId = ExCast.zCStr(HttpContext.Current.Session[ExSession.PERSON_ID]);
string _message = ExSession.SessionUserUniqueCheck(random, ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]), ExCast.zCInt(HttpContext.Current.Session[ExSession.USER_ID]));
if (_message != "")
{
return _message;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(認証処理)", ex);
return CLASS_NM + ".UpdateCustomer : 認証処理に失敗しました。" + Environment.NewLine + ex.Message.ToString();
}
#endregion
#region Field
StringBuilder sb = new StringBuilder();
DataTable dt;
ExMySQLData db = null;
string _Id = "";
#endregion
#region Databese Open
try
{
db = new ExMySQLData(ExCast.zCStr(HttpContext.Current.Session[ExSession.DB_CONNECTION_STR]));
db.DbOpen();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(DbOpen)", ex);
return CLASS_NM + ".UpdateCustomer(DbOpen) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region BeginTransaction
try
{
db.ExBeginTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(BeginTransaction)", ex);
return CLASS_NM + ".UpdateCustomer(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Get Max Master ID
if (type == 1 && (Id == "" || Id == "0"))
{
try
{
DataMasterId.GetMaxMasterId(companyId,
"",
db,
DataMasterId.geMasterMaxIdKbn.Customer,
out _Id);
if (_Id == "")
{
return "ID取得に失敗しました。";
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(GetMaxMasterId)", ex);
return CLASS_NM + ".UpdateCustomer(GetMaxMasterId) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
else
{
_Id = Id;
}
#endregion
#region Insert
if (type == 1)
{
try
{
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM M_CUSTOMER " + Environment.NewLine);
sb.Append(" WHERE DELETE_FLG = 1 " + Environment.NewLine);
sb.Append(" AND COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Insert SQL
string _invoice_id = entity.invoice_id;
if (ExCast.IsNumeric(_invoice_id)) _invoice_id = ExCast.zCDbl(_invoice_id).ToString();
sb.Length = 0;
sb.Append("INSERT INTO M_CUSTOMER " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , ID2" + Environment.NewLine);
sb.Append(" , NAME" + Environment.NewLine);
sb.Append(" , KANA" + Environment.NewLine);
sb.Append(" , ABOUT_NAME" + Environment.NewLine);
sb.Append(" , ZIP_CODE" + Environment.NewLine);
sb.Append(" , PREFECTURE_ID" + Environment.NewLine);
sb.Append(" , CITY_ID" + Environment.NewLine);
sb.Append(" , TOWN_ID" + Environment.NewLine);
sb.Append(" , ADRESS_CITY" + Environment.NewLine);
sb.Append(" , ADRESS_TOWN" + Environment.NewLine);
sb.Append(" , ADRESS1" + Environment.NewLine);
sb.Append(" , ADRESS2" + Environment.NewLine);
sb.Append(" , STATION_NAME" + Environment.NewLine);
sb.Append(" , POST_NAME" + Environment.NewLine);
sb.Append(" , PERSON_NAME" + Environment.NewLine);
sb.Append(" , TITLE_ID" + Environment.NewLine);
sb.Append(" , TITLE_NAME" + Environment.NewLine);
sb.Append(" , TEL" + Environment.NewLine);
sb.Append(" , FAX" + Environment.NewLine);
sb.Append(" , MAIL_ADRESS" + Environment.NewLine);
sb.Append(" , MOBILE_TEL" + Environment.NewLine);
sb.Append(" , MOBILE_ADRESS" + Environment.NewLine);
sb.Append(" , URL" + Environment.NewLine);
sb.Append(" , INVOICE_ID" + Environment.NewLine);
sb.Append(" , BUSINESS_DIVISION_ID" + Environment.NewLine);
sb.Append(" , UNIT_KIND_ID" + Environment.NewLine);
sb.Append(" , CREDIT_RATE" + Environment.NewLine);
sb.Append(" , TAX_CHANGE_ID" + Environment.NewLine);
sb.Append(" , SUMMING_UP_GROUP_ID" + Environment.NewLine);
sb.Append(" , PRICE_FRACTION_PROC_ID" + Environment.NewLine);
sb.Append(" , TAX_FRACTION_PROC_ID" + Environment.NewLine);
sb.Append(" , CREDIT_LIMIT_PRICE" + Environment.NewLine);
sb.Append(" , SALES_CREDIT_PRICE" + Environment.NewLine);
sb.Append(" , RECEIPT_DIVISION_ID" + Environment.NewLine);
sb.Append(" , COLLECT_CYCLE_ID" + Environment.NewLine);
sb.Append(" , COLLECT_DAY" + Environment.NewLine);
sb.Append(" , BILL_SITE" + Environment.NewLine);
sb.Append(" , GROUP1_ID" + Environment.NewLine);
sb.Append(" , GROUP2_ID" + Environment.NewLine);
sb.Append(" , GROUP3_ID" + Environment.NewLine);
sb.Append(" , MEMO" + Environment.NewLine);
sb.Append(" , DISPLAY_FLG" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine); // ID
sb.Append(" ," + ExCast.zIdForNumIndex(_Id) + Environment.NewLine); // ID2
sb.Append(" ," + ExEscape.zRepStr(entity.name) + Environment.NewLine); // NAME
sb.Append(" ," + ExEscape.zRepStr(entity.kana) + Environment.NewLine); // KANA
sb.Append(" ," + ExEscape.zRepStr(entity.about_name) + Environment.NewLine); // ABOUT_NAME
sb.Append(" ," + ExCast.zNullToZero(entity.zip_code_from + entity.zip_code_to) + Environment.NewLine); // ZIP_CODE
sb.Append(" ," + entity.prefecture_id + Environment.NewLine); // PREFECTURE_ID
sb.Append(" ," + entity.city_id + Environment.NewLine); // CITY_ID
sb.Append(" ," + entity.town_id + Environment.NewLine); // TOWN_ID
sb.Append(" ," + ExEscape.zRepStr(entity.adress_city) + Environment.NewLine); // ADRESS_CITY
sb.Append(" ," + ExEscape.zRepStr(entity.adress_town) + Environment.NewLine); // ADRESS_TOWN
sb.Append(" ," + ExEscape.zRepStr(entity.adress1) + Environment.NewLine); // ADRESS1
sb.Append(" ," + ExEscape.zRepStr(entity.adress2) + Environment.NewLine); // ADRESS2
sb.Append(" ," + ExEscape.zRepStr(entity.station_name) + Environment.NewLine); // STATION_NAME
sb.Append(" ," + ExEscape.zRepStr(entity.post_name) + Environment.NewLine); // POST_NAME
sb.Append(" ," + ExEscape.zRepStr(entity.person_name) + Environment.NewLine); // PERSON_NAME
sb.Append(" ," + entity.title_id + Environment.NewLine); // TITLE_ID
sb.Append(" ," + ExEscape.zRepStr(entity.title_name) + Environment.NewLine); // TITLE_NAME
sb.Append(" ," + ExEscape.zRepStr(entity.tel) + Environment.NewLine); // TEL
sb.Append(" ," + ExEscape.zRepStr(entity.fax) + Environment.NewLine); // FAX
sb.Append(" ," + ExEscape.zRepStr(entity.mail_adress) + Environment.NewLine); // MAIL_ADRESS
sb.Append(" ," + ExEscape.zRepStr(entity.mobile_tel) + Environment.NewLine); // MOBILE_TEL
sb.Append(" ," + ExEscape.zRepStr(entity.mobile_adress) + Environment.NewLine); // MOBILE_ADRESS
sb.Append(" ," + ExEscape.zRepStr(entity.url) + Environment.NewLine); // URL
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_invoice_id)) + Environment.NewLine); // INVOICE_ID
sb.Append(" ," + entity.business_division_id + Environment.NewLine); // BUSINESS_DIVISION_ID
sb.Append(" ," + entity.unit_kind_id + Environment.NewLine); // UNIT_KIND_ID
sb.Append(" ," + entity.credit_rate + Environment.NewLine); // CREDIT_RATE
sb.Append(" ," + entity.tax_change_id + Environment.NewLine); // TAX_CHANGE_ID
sb.Append(" ," + ExEscape.zRepStr(entity.summing_up_group_id) + Environment.NewLine); // SUMMING_UP_GROUP_ID
sb.Append(" ," + entity.price_fraction_proc_id + Environment.NewLine); // PRICE_FRACTION_PROC_ID
sb.Append(" ," + entity.tax_fraction_proc_id + Environment.NewLine); // TAX_FRACTION_PROC_ID
sb.Append(" ," + entity.credit_limit_price + Environment.NewLine); // CREDIT_LIMIT_PRICE
sb.Append(" ," + entity.sales_credit_price + Environment.NewLine); // SALES_CREDIT_PRICE
sb.Append(" ," + ExEscape.zRepStr(entity.receipt_division_id) + Environment.NewLine); // RECEIPT_DIVISION_ID
sb.Append(" ," + entity.collect_cycle_id + Environment.NewLine); // COLLECT_CYCLE_ID
sb.Append(" ," + entity.collect_day + Environment.NewLine); // COLLECT_DAY
sb.Append(" ," + entity.bill_site + Environment.NewLine); // BILL_SITE
sb.Append(" ," + ExEscape.zRepStr(entity.group1_id) + Environment.NewLine); // GROUP1_ID
sb.Append(" ," + ExEscape.zRepStr(entity.group2_id) + Environment.NewLine); // GROUP2_ID
sb.Append(" ," + ExEscape.zRepStr(entity.group3_id) + Environment.NewLine); // GROUP3_ID
sb.Append(" ," + ExEscape.zRepStr(entity.memo) + Environment.NewLine); // MEMO
sb.Append(" ," + entity.display_division_id + Environment.NewLine); // DISPLAY_FLG
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_CUSTOMER",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Sales Credit Balance Insert
try
{
if (ExCast.zNumZeroNothingFormat(_Id) == ExCast.zNumZeroNothingFormat(_invoice_id))
{
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM M_SALES_CREDIT_BALANCE " + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_invoice_id)) + Environment.NewLine); // ID
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region SQL
sb.Length = 0;
sb.Append("SELECT MT.* " + Environment.NewLine);
sb.Append(" FROM SYS_M_COMPANY_GROUP AS MT" + Environment.NewLine);
sb.Append(" WHERE MT.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND MT.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" ORDER BY MT.ID " + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
for (int i = 0; i <= dt.DefaultView.Count - 1; i++)
{
#region Insert SQL
sb.Length = 0;
sb.Append("INSERT INTO M_SALES_CREDIT_BALANCE " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , GROUP_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , SALES_CREDIT_INIT_PRICE" + Environment.NewLine);
sb.Append(" , SALES_CREDIT_PRICE" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + ExCast.zCStr(dt.DefaultView[i]["ID"]) + Environment.NewLine); // GROUP_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_invoice_id)) + Environment.NewLine); // ID
sb.Append(" ,0" + Environment.NewLine); // SALES_CREDIT_INIT_PRICE
sb.Append(" ,0" + Environment.NewLine); // SALES_CREDIT_PRICE
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_SALES_CREDIT_BALANCE",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Sales Credit Balance Insert)", ex);
return CLASS_NM + ".UpdateCustomer(Sales Credit Balance Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Insert)", ex);
return CLASS_NM + ".UpdateCustomer(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region Update
if (type == 0)
{
try
{
#region SQL
string _invoice_id = entity.invoice_id;
if (ExCast.IsNumeric(_invoice_id)) _invoice_id = ExCast.zCDbl(_invoice_id).ToString();
sb.Length = 0;
sb.Append("UPDATE M_CUSTOMER " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
0));
sb.Append(" ,NAME = " + ExEscape.zRepStr(entity.name) + Environment.NewLine);
sb.Append(" ,KANA = " + ExEscape.zRepStr(entity.kana) + Environment.NewLine);
sb.Append(" ,ABOUT_NAME = " + ExEscape.zRepStr(entity.about_name) + Environment.NewLine);
sb.Append(" ,ZIP_CODE = " + ExCast.zNullToZero(entity.zip_code_from + entity.zip_code_to) + Environment.NewLine);
sb.Append(" ,PREFECTURE_ID = " + entity.prefecture_id + Environment.NewLine);
sb.Append(" ,CITY_ID = " + entity.city_id + Environment.NewLine);
sb.Append(" ,TOWN_ID = " + entity.town_id + Environment.NewLine);
sb.Append(" ,ADRESS_CITY = " + ExEscape.zRepStr(entity.adress_city) + Environment.NewLine);
sb.Append(" ,ADRESS_TOWN = " + ExEscape.zRepStr(entity.adress_town) + Environment.NewLine);
sb.Append(" ,ADRESS1 = " + ExEscape.zRepStr(entity.adress1) + Environment.NewLine);
sb.Append(" ,ADRESS2 = " + ExEscape.zRepStr(entity.adress2) + Environment.NewLine);
sb.Append(" ,STATION_NAME = " + ExEscape.zRepStr(entity.station_name) + Environment.NewLine);
sb.Append(" ,POST_NAME = " + ExEscape.zRepStr(entity.post_name) + Environment.NewLine);
sb.Append(" ,PERSON_NAME = " + ExEscape.zRepStr(entity.person_name) + Environment.NewLine);
sb.Append(" ,TITLE_ID = " + entity.title_id + Environment.NewLine);
sb.Append(" ,TITLE_NAME = " + ExEscape.zRepStr(entity.title_name) + Environment.NewLine);
sb.Append(" ,TEL = " + ExEscape.zRepStr(entity.tel) + Environment.NewLine);
sb.Append(" ,FAX = " + ExEscape.zRepStr(entity.fax) + Environment.NewLine);
sb.Append(" ,MAIL_ADRESS = " + ExEscape.zRepStr(entity.mail_adress) + Environment.NewLine);
sb.Append(" ,MOBILE_TEL = " + ExEscape.zRepStr(entity.mobile_tel) + Environment.NewLine);
sb.Append(" ,MOBILE_ADRESS = " + ExEscape.zRepStr(entity.mobile_adress) + Environment.NewLine);
sb.Append(" ,URL = " + ExEscape.zRepStr(entity.url) + Environment.NewLine);
sb.Append(" ,INVOICE_ID = " + ExEscape.zRepStr(_invoice_id) + Environment.NewLine);
sb.Append(" ,BUSINESS_DIVISION_ID = " + entity.business_division_id + Environment.NewLine);
sb.Append(" ,UNIT_KIND_ID = " + entity.unit_kind_id + Environment.NewLine);
sb.Append(" ,CREDIT_RATE = " + entity.credit_rate + Environment.NewLine);
sb.Append(" ,TAX_CHANGE_ID = " + entity.tax_change_id + Environment.NewLine);
sb.Append(" ,SUMMING_UP_GROUP_ID = " + ExEscape.zRepStr(entity.summing_up_group_id) + Environment.NewLine);
sb.Append(" ,PRICE_FRACTION_PROC_ID = " + entity.price_fraction_proc_id + Environment.NewLine);
sb.Append(" ,TAX_FRACTION_PROC_ID = " + entity.tax_fraction_proc_id + Environment.NewLine);
sb.Append(" ,CREDIT_LIMIT_PRICE = " + entity.credit_limit_price + Environment.NewLine);
sb.Append(" ,SALES_CREDIT_PRICE = " + entity.sales_credit_price + Environment.NewLine);
sb.Append(" ,RECEIPT_DIVISION_ID = " + ExEscape.zRepStr(entity.receipt_division_id) + Environment.NewLine);
sb.Append(" ,COLLECT_CYCLE_ID = " + entity.collect_cycle_id + Environment.NewLine);
sb.Append(" ,COLLECT_DAY = " + entity.collect_day + Environment.NewLine);
sb.Append(" ,BILL_SITE = " + entity.bill_site + Environment.NewLine);
sb.Append(" ,GROUP1_ID = " + ExEscape.zRepStr(entity.group1_id) + Environment.NewLine);
sb.Append(" ,GROUP2_ID = " + ExEscape.zRepStr(entity.group2_id) + Environment.NewLine);
sb.Append(" ,GROUP3_ID = " + ExEscape.zRepStr(entity.group3_id) + Environment.NewLine);
sb.Append(" ,MEMO = " + ExEscape.zRepStr(entity.memo) + Environment.NewLine);
sb.Append(" ,DISPLAY_FLG = " + entity.display_division_id + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(Id)) + Environment.NewLine); // ID
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Sales Credit Balance Insert
try
{
if (ExCast.zNumZeroNothingFormat(_Id) == ExCast.zNumZeroNothingFormat(_invoice_id))
{
#region SQL
sb.Length = 0;
sb.Append("SELECT MT.* " + Environment.NewLine);
sb.Append(" FROM SYS_M_COMPANY_GROUP AS MT" + Environment.NewLine);
sb.Append(" WHERE MT.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND MT.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" ORDER BY MT.ID " + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
for (int i = 0; i <= dt.DefaultView.Count - 1; i++)
{
bool flg = DataExists.IsExistData(db,
companyId,
ExCast.zCStr(dt.DefaultView[i]["ID"]),
"M_SALES_CREDIT_BALANCE",
"ID",
ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_invoice_id)),
CommonUtl.geStrOrNumKbn.String);
if (flg == false)
{
#region Insert SQL
sb.Length = 0;
sb.Append("INSERT INTO M_SALES_CREDIT_BALANCE " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , GROUP_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , SALES_CREDIT_INIT_PRICE" + Environment.NewLine);
sb.Append(" , SALES_CREDIT_PRICE" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + ExCast.zCStr(dt.DefaultView[i]["ID"]) + Environment.NewLine); // GROUP_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_invoice_id)) + Environment.NewLine); // ID
sb.Append(" ,0" + Environment.NewLine); // SALES_CREDIT_INIT_PRICE
sb.Append(" ,0" + Environment.NewLine); // SALES_CREDIT_PRICE
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_SALES_CREDIT_BALANCE",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
}
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Sales Credit Balance Insert)", ex);
return CLASS_NM + ".UpdateCustomer(Sales Credit Balance Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Update)", ex);
return CLASS_NM + ".UpdateCustomer(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region Delete
if (type == 2)
{
#region Exist Data
try
{
bool _ret = false;
// 得意先チェック
_ret = DataExists.IsExistData(db, companyId, "", "T_ESTIMATE_H", "CUSTOMER_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は見積データの得意先に使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, "", "T_ORDER_H", "CUSTOMER_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は受注データの得意先に使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, "", "T_SALES_H", "CUSTOMER_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は売上データの得意先に使用されている為、削除できません。";
}
if (ExCast.zNumZeroNothingFormat(Id) != ExCast.zNumZeroNothingFormat(entity.invoice_id))
{
_ret = DataExists.IsExistData(db, companyId, "", "M_CUSTOMER", "INVOICE_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は得意先マスタの請求IDに使用されている為、削除できません。";
}
}
// 請求先チェック
if (ExCast.zNumZeroNothingFormat(Id) == ExCast.zNumZeroNothingFormat(entity.invoice_id))
{
_ret = DataExists.IsExistData(db, companyId, "", "T_SALES_H", "INVOICE_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は売上データの請求先に使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, "", "T_INVOICE", "INVOICE_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は請求データの請求先に使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, "", "T_RECEIPT_H", "INVOICE_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "ID : " + Id + " は入金データの請求先に使用されている為、削除できません。";
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Exist Data)", ex);
return CLASS_NM + ".UpdateCustomer(Exist Data) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Delete Sales Credit Balance
try
{
if (ExCast.zNumZeroNothingFormat(Id) == ExCast.zNumZeroNothingFormat(entity.invoice_id))
{
sb.Length = 0;
sb.Append("UPDATE M_SALES_CREDIT_BALANCE " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
1));
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(entity.invoice_id)) + Environment.NewLine); // ID
db.ExecuteSQL(sb.ToString(), false);
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Delete Sales Credit Balance)", ex);
return CLASS_NM + ".UpdateCustomer(Delete Sales Credit Balance) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Update
try
{
sb.Length = 0;
sb.Append("UPDATE M_CUSTOMER " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
1));
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(Id)) + Environment.NewLine); // ID
db.ExecuteSQL(sb.ToString(), false);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Delete)", ex);
return CLASS_NM + ".UpdateCustomer(Delete) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
}
#endregion
#region PG排他制御
if (type == 0 || type == 2)
{
try
{
DataPgLock.DelLockPg(companyId, userId, PG_NM, "", ipAdress, false, db);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(DelLockPg)", ex);
return CLASS_NM + ".UpdateCustomer(DelLockPg) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region CommitTransaction
try
{
db.ExCommitTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(CommitTransaction)", ex);
return CLASS_NM + ".UpdateCustomer(CommitTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Database Close
try
{
db.DbClose();
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(DbClose)", ex);
return CLASS_NM + ".UpdateCustomer(DbClose) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
finally
{
db = null;
}
#endregion
#region Add Evidence
try
{
switch (type)
{
case 0:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Update,
"ID:" + Id.ToString());
break;
case 1:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Insert,
"ID:" + _Id.ToString());
break;
case 2:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Delete,
"ID:" + Id.ToString());
break;
default:
break;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCustomer(Add Evidence)", ex);
return CLASS_NM + ".UpdateCustomer(Add Evidence) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Return
if (type == 1 && (Id == "0" || Id == ""))
{
return "Auto Insert success : " + "ID : " + _Id.ToString() + "で登録しました。";
}
else
{
return "";
}
#endregion
}
public string UpdateCondition(string random, List<EntityCondition> entity)
{
#region 認証処理
string companyId = "";
string groupId = "";
string userId = "";
string ipAdress = "";
string sessionString = "";
string personId = "";
try
{
companyId = ExCast.zCStr(HttpContext.Current.Session[ExSession.COMPANY_ID]);
groupId = ExCast.zCStr(HttpContext.Current.Session[ExSession.GROUP_ID]);
userId = ExCast.zCStr(HttpContext.Current.Session[ExSession.USER_ID]);
ipAdress = ExCast.zCStr(HttpContext.Current.Session[ExSession.IP_ADRESS]);
sessionString = ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]);
personId = ExCast.zCStr(HttpContext.Current.Session[ExSession.PERSON_ID]);
string _message = ExSession.SessionUserUniqueCheck(random, ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]), ExCast.zCInt(HttpContext.Current.Session[ExSession.USER_ID]));
if (_message != "")
{
return _message;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(認証処理)", ex);
return CLASS_NM + ".UpdateCondition : 認証処理に失敗しました。" + Environment.NewLine + ex.Message.ToString();
}
#endregion
#region Field
StringBuilder sb = new StringBuilder();
DataTable dt;
ExMySQLData db = null;
string _Id = "";
#endregion
#region Databese Open
try
{
db = new ExMySQLData(ExCast.zCStr(HttpContext.Current.Session[ExSession.DB_CONNECTION_STR]));
db.DbOpen();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(DbOpen)", ex);
return CLASS_NM + ".UpdateCondition(DbOpen) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region BeginTransaction
try
{
db.ExBeginTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(BeginTransaction)", ex);
return CLASS_NM + ".UpdateCondition(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Delete
try
{
#region データ存在チェック
sb = new StringBuilder();
#region SQL
sb.Length = 0;
sb.Append("SELECT MT.* " + Environment.NewLine);
sb.Append(" FROM M_CONDITION AS MT" + Environment.NewLine);
sb.Append(" WHERE MT.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND MT.CONDITION_DIVISION_ID = 1 " + Environment.NewLine);
sb.Append(" AND MT.DELETE_FLG = 0 " + Environment.NewLine);
#endregion
db = ExSession.GetSessionDb(ExCast.zCInt(HttpContext.Current.Session[ExSession.USER_ID]),
ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]));
dt = db.GetDataTable(sb.ToString());
string ret_msg = "";
if (dt.DefaultView.Count > 0)
{
for (int i = 0; i <= dt.DefaultView.Count - 1; i++)
{
long lngId = ExCast.zCLng(dt.DefaultView[i]["ID"]);
bool exists_flg = false;
for (int i_ = 0; i_ <= entity.Count - 1; i_++)
{
if (ExCast.zCLng(entity[i_].id) == lngId)
{
exists_flg = true;
}
}
string strId = string.Format("{0:00}", ExCast.zCDbl(lngId));
if (exists_flg == false)
{
bool _ret = false;
switch (strId)
{
case "05":
ret_msg += "締区分ID : " + strId + " は削除できません。" + Environment.NewLine;
break;
case "10":
ret_msg += "締区分ID : " + strId + " は削除できません。" + Environment.NewLine;
break;
case "15":
ret_msg += "締区分ID : " + strId + " は削除できません。" + Environment.NewLine;
break;
case "25":
ret_msg += "締区分ID : " + strId + " は削除できません。" + Environment.NewLine;
break;
case "31":
ret_msg += "締区分ID : " + strId + " は削除できません。" + Environment.NewLine;
break;
default:
_ret = DataExists.IsExistData(db, companyId, "", "M_CUSTOMER", "SUMMING_UP_GROUP_ID", strId, CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
ret_msg += "締区分ID : " + strId + " は得意先マスタで使用されている為、削除できません。" + Environment.NewLine;
}
break;
}
}
}
if (!string.IsNullOrEmpty(ret_msg))
{
return ret_msg;
}
}
#endregion
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM M_CONDITION " + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(Delete)", ex);
return CLASS_NM + ".UpdateCondition(Delete) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Insert
try
{
for (int i = 0; i <= entity.Count - 1; i++)
{
// 空行は無視する
if (!string.IsNullOrEmpty(entity[i].id))
{
#region Insert SQL
sb.Length = 0;
sb.Append("INSERT INTO M_CONDITION " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , CONDITION_DIVISION_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , NAME" + Environment.NewLine);
sb.Append(" , MEMO" + Environment.NewLine);
sb.Append(" , DISPLAY_FLG" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ,1" + Environment.NewLine); // CONDITION_DIVISION_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroFormat("{0:00}", entity[i].id)) + Environment.NewLine); // ID
sb.Append(" ," + ExEscape.zRepStr(entity[i].name) + Environment.NewLine); // NAME
sb.Append(" ," + ExEscape.zRepStr(entity[i].memo) + Environment.NewLine); // MEMO
sb.Append(" ," + entity[i].display_division_id + Environment.NewLine); // DISPLAY_FLG
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_Condition",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(Insert)", ex);
return CLASS_NM + ".UpdateCondition(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region CommitTransaction
try
{
db.ExCommitTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(CommitTransaction)", ex);
return CLASS_NM + ".UpdateCondition(CommitTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Database Close
try
{
db.DbClose();
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(DbClose)", ex);
return CLASS_NM + ".UpdateCondition(DbClose) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
finally
{
db = null;
}
#endregion
#region Add Evidence
try
{
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.DeleteAndInsert,
"");
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCondition(Add Evidence)", ex);
return CLASS_NM + ".UpdateCondition(Add Evidence) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
return "";
}
public static bool IsExistData(ExMySQLData db,
string companyId,
string groupId,
string tblName,
string col1Name,
string col1Value,
string col2Name,
string col2Value,
CommonUtl.geStrOrNumKbn col1Type)
{
StringBuilder sb;
DataTable dt;
try
{
sb = new StringBuilder();
#region SQL
sb.Append("SELECT TBL.* " + Environment.NewLine);
sb.Append(" FROM " + tblName + " AS TBL" + Environment.NewLine);
sb.Append(" WHERE TBL.DELETE_FLG = 0 " + Environment.NewLine);
if (companyId != "")
{
sb.Append(" AND TBL.COMPANY_ID = " + companyId + Environment.NewLine);
}
if (groupId != "")
{
sb.Append(" AND TBL.GROUP_ID = " + groupId + Environment.NewLine);
}
if (col1Type == CommonUtl.geStrOrNumKbn.String)
{
sb.Append(" AND TBL." + col1Name + " = " + ExEscape.zRepStr(col1Value) + Environment.NewLine);
sb.Append(" AND TBL." + col2Name + " <> " + ExEscape.zRepStr(col2Value) + Environment.NewLine);
}
else
{
sb.Append(" AND TBL." + col1Name + " = " + ExCast.zCDbl(col1Value) + Environment.NewLine);
sb.Append(" AND TBL." + col2Name + " <> " + ExCast.zCDbl(col2Value) + Environment.NewLine);
}
sb.Append(" LIMIT 0, 1" + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".IsExistData", ex);
throw;
}
finally
{
}
}
public static void GetData(ExMySQLData db,
string companyId,
string groupId,
string tblName,
string col1Name,
string col1Value,
CommonUtl.geStrOrNumKbn col1Type,
string get_col1Name,
string get_col2Name,
string get_col3Name,
string get_col4Name,
string get_col5Name,
string get_another_col1Name,
string get_another_col2Name,
string get_another_col3Name,
string get_another_col4Name,
string get_another_col5Name,
ref string get_col1Value,
ref string get_col2Value,
ref string get_col3Value,
ref string get_col4Value,
ref string get_col5Value)
{
StringBuilder sb;
DataTable dt;
try
{
sb = new StringBuilder();
#region SQL
sb.Append("SELECT " + get_col1Name + Environment.NewLine);
if (!string.IsNullOrEmpty(get_col2Name)) sb.Append(" ," + get_col2Name + Environment.NewLine);
if (!string.IsNullOrEmpty(get_col3Name)) sb.Append(" ," + get_col3Name + Environment.NewLine);
if (!string.IsNullOrEmpty(get_col4Name)) sb.Append(" ," + get_col4Name + Environment.NewLine);
if (!string.IsNullOrEmpty(get_col5Name)) sb.Append(" ," + get_col5Name + Environment.NewLine);
sb.Append(" FROM " + tblName + " AS TBL" + Environment.NewLine);
sb.Append(" WHERE TBL.DELETE_FLG = 0 " + Environment.NewLine);
if (companyId != "")
{
sb.Append(" AND TBL.COMPANY_ID = " + companyId + Environment.NewLine);
}
if (groupId != "")
{
sb.Append(" AND TBL.GROUP_ID = " + groupId + Environment.NewLine);
}
if (col1Type == CommonUtl.geStrOrNumKbn.String)
{
sb.Append(" AND TBL." + col1Name + " = " + ExEscape.zRepStr(col1Value) + Environment.NewLine);
}
else
{
sb.Append(" AND TBL." + col1Name + " = " + ExCast.zCDbl(col1Value) + Environment.NewLine);
}
sb.Append(" LIMIT 0, 1" + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
get_col1Value = ExCast.zCStr(dt.DefaultView[0][get_another_col1Name]);
if (!string.IsNullOrEmpty(get_col2Name)) get_col2Value = ExCast.zCStr(dt.DefaultView[0][get_another_col2Name]);
if (!string.IsNullOrEmpty(get_col3Name)) get_col3Value = ExCast.zCStr(dt.DefaultView[0][get_another_col3Name]);
if (!string.IsNullOrEmpty(get_col4Name)) get_col4Value = ExCast.zCStr(dt.DefaultView[0][get_another_col4Name]);
if (!string.IsNullOrEmpty(get_col5Name)) get_col5Value = ExCast.zCStr(dt.DefaultView[0][get_another_col5Name]);
}
else
{
get_col1Value = "";
get_col2Value = "";
get_col3Value = "";
get_col4Value = "";
get_col5Value = "";
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetData", ex);
throw;
}
finally
{
}
}
public static string SetLockPg(string companyId,
string userId,
string pgId,
string lockId,
string ipAdress,
ExMySQLData _db,
out geLovkFlg lockFlg)
{
StringBuilder sb;
DataTable dt;
ExMySQLData db = null;
string ret = "err";
lockFlg = geLovkFlg.UnLock;
if (CommonUtl.gDemoKbn == 1)
{
lockFlg = DataPgLock.geLovkFlg.UnLock;
return "";
}
#region Databese Open
try
{
db = new ExMySQLData(ExCast.zCStr(HttpContext.Current.Session[ExSession.DB_CONNECTION_STR]));
db.DbOpen();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".SetLockPg(DbOpen)", ex);
return CLASS_NM + ".SetLockPg(DbOpen) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region BeginTransaction
try
{
db.ExBeginTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".SetLockPg(BeginTransaction)", ex);
return CLASS_NM + ".SetLockPg(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
try
{
sb = new StringBuilder();
sb.Length = 0;
ret = DataPgLock.DelLockPg(companyId, userId, pgId, "", ipAdress, false, db);
if (ret != "") return ret;
#region SQL
// 存在確認
sb.Append("SELECT TBL.* " + Environment.NewLine);
sb.Append(" FROM S_PG_LOCK AS TBL" + Environment.NewLine);
sb.Append(" WHERE TBL.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND TBL.PG_ID = " + ExEscape.zRepStr(pgId) + Environment.NewLine);
sb.Append(" AND TBL.LOCK_ID = " + ExEscape.zRepStr(lockId) + Environment.NewLine);
sb.Append(" LIMIT 0, 1" + Environment.NewLine);
sb.Append(" FOR UPDATE");
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
if (!string.IsNullOrEmpty(userId))
{
if (userId != ExCast.zCStr(dt.DefaultView[0]["USER_ID"]))
{
lockFlg = DataPgLock.geLovkFlg.Lock;
}
else
{
lockFlg = DataPgLock.geLovkFlg.UnLock;
}
}
else
{
lockFlg = DataPgLock.geLovkFlg.Lock;
}
}
else
{
#region SQL
sb.Length = 0;
sb.Append("INSERT INTO S_PG_LOCK " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , USER_ID" + Environment.NewLine);
sb.Append(" , PG_ID" + Environment.NewLine);
sb.Append(" , LOCK_ID" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("VALUES (" + Environment.NewLine);
sb.Append(" " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + userId + Environment.NewLine); // USER_ID
sb.Append(" ," + ExEscape.zRepStr(pgId) + Environment.NewLine); // PG_ID
sb.Append(" ," + ExEscape.zRepStr(lockId) + Environment.NewLine); // LOCK_ID
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins, "pgId", "S_PG_LOCK", 9999, "0", ipAdress, userId));
sb.Append(")" + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
lockFlg = DataPgLock.geLovkFlg.UnLock;
}
ret = "";
}
catch (Exception ex)
{
db.ExRollbackTransaction();
ret = CLASS_NM + ".SetLockPg(Insert) : " + ex.Message;
CommonUtl.ExLogger.Error(CLASS_NM + ".SetLockPg(Insert)", ex);
throw;
}
#region CommitTransaction
try
{
db.ExCommitTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".SetLockPg(CommitTransaction)", ex);
return CLASS_NM + ".SetLockPg(CommitTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Database Close
try
{
db.DbClose();
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".SetLockPg(DbClose)", ex);
return CLASS_NM + ".SetLockPg(DbClose) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
finally
{
db = null;
}
#endregion
return ret;
}
/// <summary>
/// 伝票番号取得
/// </summary>
/// <param name="companyId">会社ID</param>
/// <param name="groupId">グループID</param>
/// <param name="db"></param>
/// <param name="kbn">伝票区分</param>
/// <param name="accountPeriod">会計年</param>
/// <param name="no">伝票番号</param>
/// <param name="id">伝票ID</param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static void GetSlipNo(string companyId,
string groupId,
ExMySQLData db,
geSlipKbn kbn,
string accountPeriod,
ref long no,
out long id,
string ipAdress,
string userId)
{
StringBuilder sb;
DataTable dt;
bool exist = false;
try
{
sb = new StringBuilder();
sb.Length = 0;
// 存在確認
sb.Append("SELECT SM.* " + Environment.NewLine);
sb.Append(" FROM M_SLIP_MANAGEMENT AS SM" + Environment.NewLine);
sb.Append(" WHERE SM.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND SM.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND SM.SLIP_DIVISION = " + ((int)kbn).ToString() + Environment.NewLine);
sb.Append(" AND SM.YEAR = " + ExCast.zCInt(accountPeriod).ToString() + Environment.NewLine);
sb.Append(" AND SM.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" LIMIT 0, 1" + Environment.NewLine);
sb.Append(" FOR UPDATE");
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
id = ExCast.zCLng(dt.DefaultView[0]["ID"]) + 1;
// 自動採番追加時
if (no == 0)
{
no = ExCast.zCLng(dt.DefaultView[0]["NO"]) + 1;
}
}
else
{
if (no == 0) no = 1;
id = 1;
sb.Length = 0;
sb.Append("INSERT INTO M_SLIP_MANAGEMENT " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , GROUP_ID" + Environment.NewLine);
sb.Append(" , SLIP_DIVISION" + Environment.NewLine);
sb.Append(" , YEAR" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , NO" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("VALUES (" + Environment.NewLine);
sb.Append(" " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + groupId + Environment.NewLine); // GROUP_ID
sb.Append(" ," + ((int)kbn).ToString() + Environment.NewLine); // SLIP_DIVISION
sb.Append(" ," + ExCast.zCInt(accountPeriod) + Environment.NewLine); // YEAR
sb.Append(" ," + id.ToString() + Environment.NewLine); // ID
sb.Append(" ," + no.ToString() + Environment.NewLine); // NO
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins, "OrderInp", "T_ORDER_H", 9999, "0", ipAdress, userId));
sb.Append(")" + Environment.NewLine);
db.ExecuteSQL(sb.ToString(), false);
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetSlipNo", ex);
no = 0;
id = 0;
throw;
}
}
/// <summary>
/// 伝票番号取得
/// </summary>
/// <param name="companyId">会社ID</param>
/// <param name="groupId">グループID</param>
/// <param name="db"></param>
/// <param name="kbn">伝票区分</param>
/// <param name="accountPeriod">会計年</param>
/// <param name="no">伝票番号</param>
/// <param name="id">伝票ID</param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static void GetInOutDeliveryNo(string companyId,
string groupId,
ExMySQLData db,
geInOutDeliverySlipKbn kbn,
long cause_no,
ref long _no,
ref long _id)
{
StringBuilder sb;
DataTable dt;
_no = 0;
_id = 0;
try
{
sb = new StringBuilder();
sb.Length = 0;
// 存在確認
sb.Append("SELECT ID " + Environment.NewLine);
sb.Append(" ,NO " + Environment.NewLine);
sb.Append(" FROM T_IN_OUT_DELIVERY_H AS T" + Environment.NewLine);
sb.Append(" WHERE T.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND T.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
sb.Append(" AND T.IN_OUT_DELIVERY_PROC_KBN = " + (int)kbn + Environment.NewLine);
sb.Append(" AND T.CAUSE_NO = " + cause_no + Environment.NewLine);
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
_no = ExCast.zCLng(dt.DefaultView[0]["NO"]);
_id = ExCast.zCLng(dt.DefaultView[0]["ID"]);
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetInOutDeliveryNo", ex);
throw;
}
}
/// <summary>
/// 伝票番号取得
/// </summary>
/// <param name="companyId">会社ID</param>
/// <param name="groupId">グループID</param>
/// <param name="db"></param>
/// <param name="kbn">伝票区分</param>
/// <param name="accountPeriod">会計年</param>
/// <param name="no">伝票番号</param>
/// <param name="id">伝票ID</param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static long GetMaxSlipNo(string companyId,
string groupId,
ExMySQLData db,
string tableName,
string columnName)
{
StringBuilder sb;
DataTable dt;
long no = 1;
try
{
sb = new StringBuilder();
sb.Length = 0;
// 存在確認
sb.Append("SELECT MAX(T." + columnName + ") AS MAX_NO " + Environment.NewLine);
sb.Append(" FROM " + tableName + " AS T" + Environment.NewLine);
sb.Append(" WHERE T.COMPANY_ID = " + companyId + Environment.NewLine);
if (groupId != "")
{
sb.Append(" AND T.GROUP_ID = " + groupId + Environment.NewLine);
}
sb.Append(" FOR UPDATE");
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
no = ExCast.zCLng(dt.DefaultView[0]["MAX_NO"]) + 1;
}
return no;
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetMaxSlipNo", ex);
throw;
}
}
public string UpdateCommodity(string random, int type, string Id, EntityCommodity entity)
{
#region 認証処理
string companyId = "";
string groupId = "";
string userId = "";
string ipAdress = "";
string sessionString = "";
string personId = "";
try
{
companyId = ExCast.zCStr(HttpContext.Current.Session[ExSession.COMPANY_ID]);
groupId = ExCast.zCStr(HttpContext.Current.Session[ExSession.GROUP_ID]);
userId = ExCast.zCStr(HttpContext.Current.Session[ExSession.USER_ID]);
ipAdress = ExCast.zCStr(HttpContext.Current.Session[ExSession.IP_ADRESS]);
sessionString = ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]);
personId = ExCast.zCStr(HttpContext.Current.Session[ExSession.PERSON_ID]);
string _message = ExSession.SessionUserUniqueCheck(random, ExCast.zCStr(HttpContext.Current.Session[ExSession.SESSION_RANDOM_STR]), ExCast.zCInt(HttpContext.Current.Session[ExSession.USER_ID]));
if (_message != "")
{
return _message;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(認証処理)", ex);
return CLASS_NM + ".UpdateCommodity : 認証処理に失敗しました。" + Environment.NewLine + ex.Message.ToString();
}
#endregion
#region Field
StringBuilder sb = new StringBuilder();
DataTable dt;
ExMySQLData db = null;
string _Id = "";
#endregion
#region Databese Open
try
{
db = new ExMySQLData(ExCast.zCStr(HttpContext.Current.Session[ExSession.DB_CONNECTION_STR]));
db.DbOpen();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(DbOpen)", ex);
return CLASS_NM + ".UpdateCommodity(DbOpen) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region BeginTransaction
try
{
db.ExBeginTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(BeginTransaction)", ex);
return CLASS_NM + ".UpdateCommodity(BeginTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Get Max Master ID
if (type == 1 && (Id == "" || Id == "0"))
{
try
{
DataMasterId.GetMaxMasterId(companyId,
"",
db,
DataMasterId.geMasterMaxIdKbn.Commodity,
out _Id);
if (_Id == "")
{
return "ID取得に失敗しました。";
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(GetMaxMasterId)", ex);
return CLASS_NM + ".UpdateCommodity(GetMaxMasterId) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
else
{
_Id = Id;
}
#endregion
#region Insert
if (type == 1)
{
try
{
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM M_COMMODITY " + Environment.NewLine);
sb.Append(" WHERE DELETE_FLG = 1 " + Environment.NewLine);
sb.Append(" AND COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine);
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Insert SQL
string _main_purchase_id = entity.main_purchase_id;
if (ExCast.IsNumeric(_main_purchase_id)) _main_purchase_id = ExCast.zCDbl(_main_purchase_id).ToString();
#region SQL
sb.Length = 0;
sb.Append("INSERT INTO M_COMMODITY " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , ID2" + Environment.NewLine);
sb.Append(" , NAME" + Environment.NewLine);
sb.Append(" , KANA" + Environment.NewLine);
sb.Append(" , UNIT_ID" + Environment.NewLine);
sb.Append(" , ENTER_NUMBER" + Environment.NewLine);
sb.Append(" , NUMBER_DECIMAL_DIGIT" + Environment.NewLine);
sb.Append(" , UNIT_DECIMAL_DIGIT" + Environment.NewLine);
sb.Append(" , TAXATION_DIVISION_ID" + Environment.NewLine);
sb.Append(" , INVENTORY_MANAGEMENT_DIVISION_ID" + Environment.NewLine);
sb.Append(" , PURCHASE_LOT" + Environment.NewLine);
sb.Append(" , LEAD_TIME" + Environment.NewLine);
sb.Append(" , JUST_INVENTORY_NUMBER" + Environment.NewLine);
sb.Append(" , INVENTORY_NUMBER" + Environment.NewLine);
sb.Append(" , INVENTORY_EVALUATION_ID" + Environment.NewLine);
sb.Append(" , MAIN_PURCHASE_ID" + Environment.NewLine);
sb.Append(" , RETAIL_PRICE_SKIP_TAX" + Environment.NewLine);
sb.Append(" , RETAIL_PRICE_BEFORE_TAX" + Environment.NewLine);
sb.Append(" , SALES_UNIT_PRICE_SKIP_TAX" + Environment.NewLine);
sb.Append(" , SALES_UNIT_PRICE_BEFORE_TAX" + Environment.NewLine);
sb.Append(" , SALES_COST_PRICE_SKIP_TAX" + Environment.NewLine);
sb.Append(" , SALES_COST_PRICE_BEFORE_TAX" + Environment.NewLine);
sb.Append(" , PURCHASE_UNIT_PRICE_SKIP_TAX" + Environment.NewLine);
sb.Append(" , PURCHASE_UNIT_PRICE_BEFORE_TAX" + Environment.NewLine);
sb.Append(" , GROUP1_ID" + Environment.NewLine);
sb.Append(" , GROUP2_ID" + Environment.NewLine);
sb.Append(" , GROUP3_ID" + Environment.NewLine);
sb.Append(" , MEMO" + Environment.NewLine);
sb.Append(" , DISPLAY_FLG" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine); // ID
sb.Append(" ," + ExCast.zIdForNumIndex(_Id) + Environment.NewLine); // ID2
sb.Append(" ," + ExEscape.zRepStr(entity.name) + Environment.NewLine); // NAME
sb.Append(" ," + ExEscape.zRepStr(entity.kana) + Environment.NewLine); // KANA
sb.Append(" ," + entity.unit_id + Environment.NewLine); // UNIT_ID
sb.Append(" ," + entity.enter_number + Environment.NewLine); // ENTER_NUMBER
sb.Append(" ," + entity.number_decimal_digit + Environment.NewLine); // NUMBER_DECIMAL_DIGIT
sb.Append(" ," + entity.unit_decimal_digit + Environment.NewLine); // UNIT_DECIMAL_DIGIT
sb.Append(" ," + entity.taxation_divition_id + Environment.NewLine); // TAXATION_DIVISION_ID
sb.Append(" ," + entity.inventory_management_division_id + Environment.NewLine); // INVENTORY_MANAGEMENT_DIVISION_ID
sb.Append(" ," + entity.purchase_lot + Environment.NewLine); // PURCHASE_LOT
sb.Append(" ," + entity.lead_time + Environment.NewLine); // LEAD_TIME
sb.Append(" ," + entity.just_inventory_number + Environment.NewLine); // JUST_INVENTORY_NUMBER
sb.Append(" ," + entity.inventory_number + Environment.NewLine); // INVENTORY_NUMBER
sb.Append(" ," + entity.inventory_evaluation_id + Environment.NewLine); // INVENTORY_EVALUATION_ID
sb.Append(" ," + ExEscape.zRepStr(_main_purchase_id) + Environment.NewLine); // MAIN_PURCHASE_ID
sb.Append(" ," + entity.retail_price_skip_tax + Environment.NewLine); // RETAIL_PRICE_SKIP_TAX
sb.Append(" ," + entity.retail_price_before_tax + Environment.NewLine); // RETAIL_PRICE_BEFORE_TAX
sb.Append(" ," + entity.sales_unit_price_skip_tax + Environment.NewLine); // SALES_UNIT_PRICE_SKIP_TAX
sb.Append(" ," + entity.sales_unit_price_before_tax + Environment.NewLine); // SALES_UNIT_PRICE_BEFORE_TAX
sb.Append(" ," + entity.sales_cost_price_skip_tax + Environment.NewLine); // SALES_COST_PRICE_SKIP_TAX
sb.Append(" ," + entity.sales_cost_price_before_tax + Environment.NewLine); // SALES_COST_PRICE_BEFORE_TAX
sb.Append(" ," + entity.purchase_unit_price_skip_tax + Environment.NewLine); // PURCHASE_UNIT_PRICE_SKIP_TAX
sb.Append(" ," + entity.purchase_unit_price_before_tax + Environment.NewLine); // PURCHASE_UNIT_PRICE_BEFORE_TAX
sb.Append(" ," + ExEscape.zRepStr(entity.group1_id) + Environment.NewLine); // GROUP1_ID
sb.Append(" ," + ExEscape.zRepStr(entity.group2_id) + Environment.NewLine); // GROUP2_ID
sb.Append(" ," + ExEscape.zRepStr(entity.group3_id) + Environment.NewLine); // GROUP3_ID
sb.Append(" ," + ExEscape.zRepStr(entity.memo) + Environment.NewLine); // MEMO
sb.Append(" ," + entity.display_division_id + Environment.NewLine); // DISPLAY_FLG
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_COMMODITY",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region Commodity Inventory Insert
try
{
#region Delete SQL
sb.Length = 0;
sb.Append("DELETE FROM M_COMMODITY_INVENTORY " + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine); // ID
#endregion
db.ExecuteSQL(sb.ToString(), false);
#region SQL
sb.Length = 0;
sb.Append("SELECT MT.* " + Environment.NewLine);
sb.Append(" FROM SYS_M_COMPANY_GROUP AS MT" + Environment.NewLine);
sb.Append(" WHERE MT.COMPANY_ID = " + companyId + Environment.NewLine);
sb.Append(" AND MT.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" ORDER BY MT.ID " + Environment.NewLine);
#endregion
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
for (int i = 0; i <= dt.DefaultView.Count - 1; i++)
{
#region Insert SQL
sb.Length = 0;
sb.Append("INSERT INTO M_COMMODITY_INVENTORY " + Environment.NewLine);
sb.Append(" ( COMPANY_ID" + Environment.NewLine);
sb.Append(" , GROUP_ID" + Environment.NewLine);
sb.Append(" , ID" + Environment.NewLine);
sb.Append(" , INVENTORY_NUMBER" + Environment.NewLine);
sb.Append(" , UPDATE_FLG" + Environment.NewLine);
sb.Append(" , DELETE_FLG" + Environment.NewLine);
sb.Append(" , CREATE_PG_ID" + Environment.NewLine);
sb.Append(" , CREATE_ADRESS" + Environment.NewLine);
sb.Append(" , CREATE_USER_ID" + Environment.NewLine);
sb.Append(" , CREATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , CREATE_DATE" + Environment.NewLine);
sb.Append(" , CREATE_TIME" + Environment.NewLine);
sb.Append(" , UPDATE_PG_ID" + Environment.NewLine);
sb.Append(" , UPDATE_ADRESS" + Environment.NewLine);
sb.Append(" , UPDATE_USER_ID" + Environment.NewLine);
sb.Append(" , UPDATE_PERSON_ID" + Environment.NewLine);
sb.Append(" , UPDATE_DATE" + Environment.NewLine);
sb.Append(" , UPDATE_TIME" + Environment.NewLine);
sb.Append(")" + Environment.NewLine);
sb.Append("SELECT " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" ," + ExCast.zCStr(dt.DefaultView[i]["ID"]) + Environment.NewLine); // GROUP_ID
sb.Append(" ," + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(_Id)) + Environment.NewLine); // ID
sb.Append(" ,0" + Environment.NewLine); // INVENTORY_NUMBER
sb.Append(CommonUtl.GetInsSQLCommonColums(CommonUtl.UpdKbn.Ins,
PG_NM,
"M_COMMODITY_INVENTORY",
ExCast.zCInt(personId),
_Id,
ipAdress,
userId));
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Commodity Inventory Insert)", ex);
return CLASS_NM + ".UpdateCommodity(Commodity Inventory Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Insert)", ex);
return CLASS_NM + ".UpdateCommodity(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region Update
if (type == 0)
{
try
{
#region SQL
string _main_purchase_id = entity.main_purchase_id;
if (ExCast.IsNumeric(_main_purchase_id)) _main_purchase_id = ExCast.zCDbl(_main_purchase_id).ToString();
sb.Length = 0;
sb.Append("UPDATE M_COMMODITY " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
0));
sb.Append(" ,NAME = " + ExEscape.zRepStr(entity.name) + Environment.NewLine);
sb.Append(" ,KANA = " + ExEscape.zRepStr(entity.kana) + Environment.NewLine);
sb.Append(" ,UNIT_ID = " + entity.unit_id + Environment.NewLine);
sb.Append(" ,ENTER_NUMBER = " + entity.enter_number + Environment.NewLine);
sb.Append(" ,NUMBER_DECIMAL_DIGIT = " + entity.number_decimal_digit + Environment.NewLine);
sb.Append(" ,UNIT_DECIMAL_DIGIT = " + entity.unit_decimal_digit + Environment.NewLine);
sb.Append(" ,TAXATION_DIVISION_ID = " + entity.taxation_divition_id + Environment.NewLine);
sb.Append(" ,INVENTORY_MANAGEMENT_DIVISION_ID = " + entity.inventory_management_division_id + Environment.NewLine);
sb.Append(" ,PURCHASE_LOT = " + entity.purchase_lot + Environment.NewLine);
sb.Append(" ,LEAD_TIME = " + entity.lead_time + Environment.NewLine);
sb.Append(" ,JUST_INVENTORY_NUMBER = " + entity.just_inventory_number + Environment.NewLine);
sb.Append(" ,INVENTORY_NUMBER = " + entity.inventory_number + Environment.NewLine);
sb.Append(" ,INVENTORY_EVALUATION_ID = " + entity.inventory_evaluation_id + Environment.NewLine);
sb.Append(" ,MAIN_PURCHASE_ID = " + ExEscape.zRepStr(_main_purchase_id) + Environment.NewLine);
sb.Append(" ,RETAIL_PRICE_SKIP_TAX = " + entity.retail_price_skip_tax + Environment.NewLine);
sb.Append(" ,RETAIL_PRICE_BEFORE_TAX = " + entity.retail_price_before_tax + Environment.NewLine);
sb.Append(" ,SALES_UNIT_PRICE_SKIP_TAX = " + entity.sales_unit_price_skip_tax + Environment.NewLine);
sb.Append(" ,SALES_UNIT_PRICE_BEFORE_TAX = " + entity.sales_unit_price_before_tax + Environment.NewLine);
sb.Append(" ,SALES_COST_PRICE_SKIP_TAX = " + entity.sales_cost_price_skip_tax + Environment.NewLine);
sb.Append(" ,SALES_COST_PRICE_BEFORE_TAX = " + entity.sales_cost_price_before_tax + Environment.NewLine);
sb.Append(" ,PURCHASE_UNIT_PRICE_SKIP_TAX = " + entity.purchase_unit_price_skip_tax + Environment.NewLine);
sb.Append(" ,PURCHASE_UNIT_PRICE_BEFORE_TAX = " + entity.purchase_unit_price_before_tax + Environment.NewLine);
sb.Append(" ,GROUP1_ID = " + ExEscape.zRepStr(entity.group1_id) + Environment.NewLine);
sb.Append(" ,GROUP2_ID = " + ExEscape.zRepStr(entity.group2_id) + Environment.NewLine);
sb.Append(" ,GROUP3_ID = " + ExEscape.zRepStr(entity.group3_id) + Environment.NewLine);
sb.Append(" ,MEMO = " + ExEscape.zRepStr(entity.memo) + Environment.NewLine);
sb.Append(" ,DISPLAY_FLG = " + entity.display_division_id + Environment.NewLine);
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(Id)) + Environment.NewLine); // ID
#endregion
db.ExecuteSQL(sb.ToString(), false);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Update)", ex);
return CLASS_NM + ".UpdateCommodity(Insert) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region Delete
if (type == 2)
{
#region Exist Data
try
{
bool _ret = false;
_ret = DataExists.IsExistData(db, companyId, groupId, "T_ESTIMATE_D", "COMMODITY_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "商品ID : " + Id + " は見積データに使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, groupId, "T_ORDER_D", "COMMODITY_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "商品ID : " + Id + " は受注データに使用されている為、削除できません。";
}
_ret = DataExists.IsExistData(db, companyId, groupId, "T_SALES_D", "COMMODITY_ID", ExCast.zNumZeroNothingFormat(Id), CommonUtl.geStrOrNumKbn.String);
if (_ret == true)
{
return "商品ID : " + Id + " は売上データに使用されている為、削除できません。";
}
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Exist Data)", ex);
return CLASS_NM + ".UpdateCommodity(Exist Data) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Delete Commodity Inventory
try
{
sb.Length = 0;
sb.Append("UPDATE M_COMMODITY_INVENTORY " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
1));
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(Id)) + Environment.NewLine); // ID
db.ExecuteSQL(sb.ToString(), false);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Delete Commodity Inventory)", ex);
return CLASS_NM + ".UpdateCommodity(Commodity Inventory) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Update
try
{
sb.Length = 0;
sb.Append("UPDATE M_COMMODITY " + Environment.NewLine);
sb.Append(CommonUtl.GetUpdSQLCommonColums(PG_NM,
ExCast.zCInt(personId),
ipAdress,
userId,
1));
sb.Append(" WHERE COMPANY_ID = " + companyId + Environment.NewLine); // COMPANY_ID
sb.Append(" AND ID = " + ExEscape.zRepStr(ExCast.zNumZeroNothingFormat(Id)) + Environment.NewLine); // ID
db.ExecuteSQL(sb.ToString(), false);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Delete)", ex);
return CLASS_NM + ".UpdateCommodity(Delete) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
}
#endregion
#region PG排他制御
if (type == 0 || type == 2)
{
try
{
DataPgLock.DelLockPg(companyId, userId, PG_NM, "", ipAdress, false, db);
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(DelLockPg)", ex);
return CLASS_NM + ".UpdateCommodity(DelLockPg) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
}
#endregion
#region CommitTransaction
try
{
db.ExCommitTransaction();
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(CommitTransaction)", ex);
return CLASS_NM + ".UpdateCommodity(CommitTransaction) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Database Close
try
{
db.DbClose();
}
catch (Exception ex)
{
db.ExRollbackTransaction();
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(DbClose)", ex);
return CLASS_NM + ".UpdateCommodity(DbClose) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
finally
{
db = null;
}
#endregion
#region Add Evidence
try
{
switch (type)
{
case 0:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Update,
"ID:" + Id.ToString());
break;
case 1:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Insert,
"ID:" + _Id.ToString());
break;
case 2:
svcPgEvidence.gAddEvidence(ExCast.zCInt(HttpContext.Current.Session[ExSession.EVIDENCE_SAVE_FLG]),
companyId,
userId,
ipAdress,
sessionString,
PG_NM,
DataPgEvidence.geOperationType.Delete,
"ID:" + Id.ToString());
break;
default:
break;
}
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".UpdateCommodity(Add Evidence)", ex);
return CLASS_NM + ".UpdateCommodity(Add Evidence) : 予期せぬエラーが発生しました。" + Environment.NewLine + ex.Message;
}
#endregion
#region Return
if (type == 1 && (Id == "0" || Id == ""))
{
return "Auto Insert success : " + "ID : " + _Id.ToString() + "で登録しました。";
}
else
{
return "";
}
#endregion
}
/// <summary>
/// 最大ID取得
/// </summary>
/// <param name="companyId">会社ID</param>
/// <param name="groupId">グループID</param>
/// <param name="db"></param>
/// <param name="kbn">伝票区分</param>
/// <param name="accountPeriod">会計年</param>
/// <param name="no">伝票番号</param>
/// <param name="id">伝票ID</param>
/// <param name="ipAdress"></param>
/// <param name="userId"></param>
public static void GetMaxMasterId(string companyId,
string groupId,
ExMySQLData db,
geMasterMaxIdKbn kbn,
out string id)
{
StringBuilder sb;
DataTable dt;
bool exist = false;
try
{
sb = new StringBuilder();
sb.Length = 0;
// 存在確認
sb.Append("SELECT MT.ID AS MAX_ID " + Environment.NewLine);
switch (kbn)
{
case geMasterMaxIdKbn.CompanyGroup:
sb.Append(" FROM SYS_M_COMPANY_GROUP AS MT" + Environment.NewLine);
break;
case geMasterMaxIdKbn.Person:
sb.Append(" FROM M_PERSON AS MT" + Environment.NewLine);
break;
case geMasterMaxIdKbn.Customer:
sb.Append(" FROM M_CUSTOMER AS MT" + Environment.NewLine);
break;
case geMasterMaxIdKbn.Commodity:
sb.Append(" FROM M_COMMODITY AS MT" + Environment.NewLine);
break;
case geMasterMaxIdKbn.Supplier:
sb.Append(" FROM M_SUPPLIER AS MT" + Environment.NewLine);
break;
case geMasterMaxIdKbn.Purchase:
sb.Append(" FROM M_PURCHASE AS MT" + Environment.NewLine);
break;
default:
break;
}
sb.Append(" WHERE MT.DELETE_FLG = 0 " + Environment.NewLine);
sb.Append(" AND MT.COMPANY_ID = " + companyId + Environment.NewLine);
switch (kbn)
{
case geMasterMaxIdKbn.Supplier:
sb.Append(" AND MT.CUSTOMER_ID = " + ExEscape.zRepStr(groupId) + Environment.NewLine);
break;
default:
if (groupId != "")
{
sb.Append(" AND MT.GROUP_ID = " + groupId + Environment.NewLine);
}
break;
}
switch (kbn)
{
case geMasterMaxIdKbn.Customer:
case geMasterMaxIdKbn.Commodity:
case geMasterMaxIdKbn.Supplier:
case geMasterMaxIdKbn.Purchase:
sb.Append(" AND CAST(MT.ID AS SIGNED) > 0 " + Environment.NewLine);
sb.Append(" ORDER BY CAST(MT.ID AS SIGNED) DESC " + Environment.NewLine);
sb.Append(" LIMIT 0, 1 " + Environment.NewLine);
break;
default:
sb.Append(" ORDER BY MT.ID DESC " + Environment.NewLine);
sb.Append(" LIMIT 0, 1 " + Environment.NewLine);
break;
}
dt = db.GetDataTable(sb.ToString());
if (dt.DefaultView.Count > 0)
{
id = (ExCast.zCLng(dt.DefaultView[0]["MAX_ID"]) + 1).ToString();
}
else
id = "1";
}
catch (Exception ex)
{
CommonUtl.ExLogger.Error(CLASS_NM + ".GetMaxMasterId", ex);
id = "";
throw;
}
}
