// 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); }