// AirportLog 송장번호가 몇개 사용되고 몇개 남았는지를 리턴한다
public static List <InvoiceRangeUsedModels> GetInvoiceRangeUsed(string rainbow_code)
{
string sql1 = "select "
+ "RAINBOWCODE"
+ ", RANGE_SEQNO"
+ ", USE_YN"
+ ", count(*) as COUNT"
+ " from config_invoice_airportlog"
+ ((rainbow_code.Length > 0) ? string.Format(" where RAINBOWCODE='{0}'", rainbow_code) : "")
+ " group by RAINBOWCODE, RANGE_SEQNO, USE_YN"
+ "";
string err1 = "";
DataTable dt1 = DatabaseConnection.GetDataTableMySQL(sql1, out err1);
if (dt1 == null)
{
return(null);
}
List <InvoiceRangeUsedModels> INVOICE_RANGE_USED_LIST = new List <InvoiceRangeUsedModels>();
for (int i = 0; i < dt1.Rows.Count; i++)
{
InvoiceRangeUsedModels USED = new InvoiceRangeUsedModels();
USED.RAINBOWCODE = dt1.Rows[i]["RAINBOWCODE"].ToString().Trim(); // 지점코드
USED.RANGE_SEQNO = GlobalFunction.GetInt(dt1.Rows[i]["RANGE_SEQNO"].ToString().Trim()); // 송장번호 권역대 순번
USED.USE_YN = dt1.Rows[i]["USE_YN"].ToString().Trim().ToLower(); // y=사용함, n=사용안함
USED.COUNT = GlobalFunction.GetInt(dt1.Rows[i]["COUNT"].ToString().Trim()); // 갯수
INVOICE_RANGE_USED_LIST.Add(USED);
}
return(INVOICE_RANGE_USED_LIST);
}
// AirportLog 송장번호 가져오기
// 송장번호를 미리 생성해놓고 랜덤하게 설정한다
public static string[] GetInvoiceNo_AirportLogUsed(string rainbow_code, int cnt, out string error_str)
{
error_str = "";
if (cnt <= 0)
{
error_str = comm_global.Language.Resources.API_INVALID_PARAMETER;
return(null);
}
if (cnt > GlobalSettings.MAX_UPLOAD_EXCEL_COUNT)
{
error_str = comm_global.Language.Resources.API_MAXIMUM_UPLOAD_LIMIT + string.Format(" : {0}", GlobalSettings.MAX_UPLOAD_EXCEL_COUNT);
return(null);
}
// AirportLog 송장번호가 몇개 사용되고 몇개 남았는지를 리턴한다
List <InvoiceRangeUsedModels> USED_LIST = GetInvoiceRangeUsed(rainbow_code);
if (USED_LIST == null || USED_LIST.Count == 0)
{
error_str = comm_global.Language.Resources.API_YOU_HAVE_USED_ALL_THE_INVOICE_NUMBERS;
error_str += " " + comm_global.Language.Resources.API_REMAINING_NUMBER;
error_str += " : 0";
return(null);
}
string invoice_type = "CN_AIRPORTLOG";
string sql1 = "select"
+ " SEQNO"
+ ", RAINBOWCODE"
+ ", HBLNO_TYPE"
+ ", HBLNO_START"
+ ", HBLNO_END"
+ ", HBLNO_CURRENT"
+ ", DIGIT"
+ ", PREFIX"
+ ", POSTFIX"
+ " from " + GlobalSettings.INVOICE_RANGE_TABLE_COMM
+ string.Format(" where RAINBOWCODE='{0}'", rainbow_code)
+ string.Format(" and HBLNO_TYPE='{0}'", invoice_type)
+ " and USE_YN='y'"
+ " order by SEQNO"
+ "";
DataTable dtInvoice = new DataTable();
MySqlConnection conn = new MySqlConnection();
MySqlTransaction tran = null;
try
{
conn = new MySqlConnection(DatabaseConnection.GetDBConnStr());
conn.Open();
//OleDbDataAdapter sda = new OleDbDataAdapter(sql1, conn);
//sda.Fill(dtInvoice);
//conn.Close();
tran = conn.BeginTransaction(IsolationLevel.Serializable); // 트랜잭션시작
MySqlCommand cmd2 = new MySqlCommand(sql1, conn, tran);
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = cmd2;
sda.Fill(dtInvoice);
}
catch (MySqlException e)
{
error_str = e.Message;
conn.Close();
return(null);
}
if (dtInvoice == null)
{
error_str = comm_global.Language.Resources.API_DATABASE_ERROR;
conn.Close();
return(null);
}
if (dtInvoice.Rows.Count == 0)
{
error_str = comm_global.Language.Resources.API_YOU_HAVE_USED_ALL_THE_INVOICE_NUMBERS;
error_str += " " + comm_global.Language.Resources.API_REMAINING_NUMBER;
error_str += " : 0";
conn.Close();
return(null);
}
long TOTAL_NUM = 0;
try
{
for (int i = 0; i < dtInvoice.Rows.Count; i++)
{
int SEQNO = GlobalFunction.GetInt(dtInvoice.Rows[i]["SEQNO"].ToString().Trim());
long HBLNO_END = long.Parse(dtInvoice.Rows[i]["HBLNO_END"].ToString().Trim()); // 끝번호
long HBLNO_CURRENT = long.Parse(dtInvoice.Rows[i]["HBLNO_CURRENT"].ToString().Trim()); // 마지막사용번호
long REMAIN_COUNT = 0; // 남은갯수
InvoiceRangeUsedModels REMAIN = USED_LIST.Find(m => m.RANGE_SEQNO == SEQNO && m.USE_YN == "n");
if (REMAIN != null && REMAIN.RANGE_SEQNO > 0)
{
REMAIN_COUNT = REMAIN.COUNT;
}
if (REMAIN_COUNT > 0)
{
TOTAL_NUM += REMAIN_COUNT;
}
}
}
catch (MySqlException e)
{
error_str = e.Message;
conn.Close();
return(null);
}
if (cnt > TOTAL_NUM)
{
error_str = comm_global.Language.Resources.API_YOU_HAVE_USED_ALL_THE_INVOICE_NUMBERS;
error_str += " " + comm_global.Language.Resources.API_REMAINING_NUMBER;
error_str += string.Format(" : {0}", TOTAL_NUM);
conn.Close();
return(null);
}
string[] invoiceList = new string[cnt];
List <string> sql_list = new List <string>();
int num = 0;
try
{
for (int i = 0; i < dtInvoice.Rows.Count; i++)
{
int SEQNO = GlobalFunction.GetInt(dtInvoice.Rows[i]["SEQNO"].ToString().Trim());
long HBLNO_END = long.Parse(dtInvoice.Rows[i]["HBLNO_END"].ToString().Trim()); // 끝번호
long HBLNO_CURRENT = long.Parse(dtInvoice.Rows[i]["HBLNO_CURRENT"].ToString().Trim()); // 마지막사용번호
long REMAIN_COUNT = 0; // 남은갯수
InvoiceRangeUsedModels REMAIN = USED_LIST.Find(m => m.RANGE_SEQNO == SEQNO && m.USE_YN == "n");
if (REMAIN != null && REMAIN.RANGE_SEQNO > 0)
{
REMAIN_COUNT = REMAIN.COUNT;
}
if (REMAIN_COUNT <= 0)
{
continue;
}
// 사용하지 않은 송장번호를 가져온다
DataTable dtInvoiceAirportLog = new DataTable();
try
{
string sql3 = "select "
+ "SEQNO"
+ ", DELVNO"
+ " from config_invoice_airportlog"
+ string.Format(" where RAINBOWCODE='{0}'", rainbow_code)
+ string.Format(" and RANGE_SEQNO={0}", SEQNO)
+ " and USE_YN='n'"
+ "";
MySqlCommand cmd3 = new MySqlCommand(sql3, conn, tran);
MySqlDataAdapter sda3 = new MySqlDataAdapter();
sda3.SelectCommand = cmd3;
sda3.Fill(dtInvoiceAirportLog);
}
catch (MySqlException e)
{
error_str = e.Message;
conn.Close();
return(null);
}
if (dtInvoiceAirportLog == null || dtInvoiceAirportLog.Rows.Count == 0)
{
continue;
}
// 송장번호를 랜덤하게 섞는다
int[] SEQNO_LIST = new int[dtInvoiceAirportLog.Rows.Count];
string[] DELVNO_LIST = new string[dtInvoiceAirportLog.Rows.Count];
for (int k = 0; k < dtInvoiceAirportLog.Rows.Count; k++)
{
SEQNO_LIST[k] = GlobalFunction.GetInt(dtInvoiceAirportLog.Rows[k]["SEQNO"].ToString().Trim());
DELVNO_LIST[k] = dtInvoiceAirportLog.Rows[k]["DELVNO"].ToString().Trim();
}
Random rand = new Random((int)DateTime.Now.Ticks);
int idx, old_int;
string old_str;
for (int m = 0; m < dtInvoiceAirportLog.Rows.Count; m++)
{
idx = rand.Next(dtInvoiceAirportLog.Rows.Count);
if (idx == m)
{
continue;
}
old_int = SEQNO_LIST[m];
SEQNO_LIST[m] = SEQNO_LIST[idx];
SEQNO_LIST[idx] = old_int;
old_str = DELVNO_LIST[m];
DELVNO_LIST[m] = DELVNO_LIST[idx];
DELVNO_LIST[idx] = old_str;
}
// 송장번호 갯수만큼 리턴한다
for (int k = 0; k < DELVNO_LIST.Length; k++)
{
invoiceList[num++] = DELVNO_LIST[k];
string sql2 = "update config_invoice_airportlog set "
+ "USE_YN='y'"
+ string.Format(" where SEQNO={0}", SEQNO_LIST[k])
+ "";
sql_list.Add(sql2);
if (num == cnt)
{
break;
}
}
// 마지막 사용날짜 업데이트
string sql4 = "update config_invoice_range set "
+ "UPDATETIME=now()"
+ string.Format(" where SEQNO={0}", SEQNO)
+ "";
sql_list.Add(sql4);
if (num == cnt)
{
break;
}
}
}
catch (MySqlException e)
{
error_str = e.Message;
conn.Close();
return(null);
}
if (cnt > num)
{
error_str = comm_global.Language.Resources.API_YOU_HAVE_USED_ALL_THE_INVOICE_NUMBERS;
error_str += " " + comm_global.Language.Resources.API_REMAINING_NUMBER;
error_str += string.Format(" : {0}", num);
conn.Close();
return(null);
}
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran; // 현재사용할트랜잭션객체지정
for (int i = 0; i < sql_list.Count; i++)
{
cmd.CommandText = sql_list[i]; // 쿼리지정
cmd.ExecuteNonQuery(); // 실행
}
tran.Commit(); // 트랜잭션commit
conn.Close();
}
catch (MySqlException e)
{
tran.Rollback();
conn.Close();
error_str = e.Message;
return(null);
}
return(invoiceList);
}
