private void load()
{
string sql = "SELECT [PKEY],[TRADING_DATE],[BILL_NUMBER],[EXCHANGES_UNIT],[SUM_VALUE],[SUM_MONEY],";
sql += "[MAKE_ORDER_STAFF],[STAFF_CHECK_ID],[ORDERR_REVIEW],[REVIEW_DATE],[IS_REVIEW],[STAFF_SAVE_ID],";
sql += "[IS_RED_BILL],[IS_IN_LEDGER],[ORDERR_IN_LEDGER],[IN_LEDGER_DATE] ";
sql += "FROM [dbo].[WAREHOUSE_MANAGEMENT_IN_OTHER] ORDER BY PKEY";
m_tableDataList.Clear();
using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql))
{
foreach (DataRow row in dataTable.Rows)
{
MaterielInOtherOrderTable record = new MaterielInOtherOrderTable();
record.pkey = DbDataConvert.ToInt32(row["PKEY"]);
record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd");
record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]);
record.exchangesUnit = DbDataConvert.ToString(row["EXCHANGES_UNIT"]);
record.sumValue = DbDataConvert.ToString(row["SUM_VALUE"]);
record.sumMoney = DbDataConvert.ToString(row["SUM_MONEY"]);
record.staffSaveId = DbDataConvert.ToInt32(row["STAFF_SAVE_ID"]);
record.staffSaveName = Staff.getInctance().getStaffNameFromPkey(record.staffSaveId);
record.orderReviewStaffId = DbDataConvert.ToInt32(row["STAFF_CHECK_ID"]);
record.orderReviewStaffName = Staff.getInctance().getStaffNameFromPkey(record.orderReviewStaffId);
record.makeOrderStaff = DbDataConvert.ToInt32(row["MAKE_ORDER_STAFF"]);
record.makeOrderStaffName = Staff.getInctance().getStaffNameFromPkey(record.makeOrderStaff);
record.isReview = DbDataConvert.ToString(row["IS_REVIEW"]);
if (DbDataConvert.ToString(row["ORDERR_REVIEW"]).Length > 0)
{
record.orderrReview = DbDataConvert.ToInt32(row["ORDERR_REVIEW"]);
record.orderrReviewName = Staff.getInctance().getStaffNameFromPkey(record.orderrReview);
record.reviewDate = DbDataConvert.ToDateTime(row["REVIEW_DATE"]).ToString("yyyy-MM-dd");
}
record.isRedBill = DbDataConvert.ToInt32(row["IS_RED_BILL"]);
// 记账相关信息
if (DbDataConvert.ToString(row["ORDERR_IN_LEDGER"]).Length > 0)
{
record.orderInLedger = DbDataConvert.ToInt32(row["ORDERR_IN_LEDGER"]);
record.orderInLedgerName = Staff.getInctance().getStaffNameFromPkey(record.orderInLedger);
record.inLedgerDate = DbDataConvert.ToDateTime(row["IN_LEDGER_DATE"]).ToString("yyyy-MM-dd");
record.isInLedger = DbDataConvert.ToInt32(row["IS_IN_LEDGER"]);
}
m_tableDataList.Add(m_tableDataList.Count, record);
}
}
}
public MaterielInOtherOrderTable getMaterielInOtherOrderInfoFromBillNumber(string billNumber)
{
MaterielInOtherOrderTable record = new MaterielInOtherOrderTable();
foreach (KeyValuePair <int, MaterielInOtherOrderTable> index in m_tableDataList)
{
if (index.Value.billNumber == billNumber)
{
record = index.Value;
break;
}
}
return(record);
}
public bool checkBillIsExist(string billNumber)
{
bool isRet = false;
foreach (KeyValuePair <int, MaterielInOtherOrderTable> index in m_tableDataList)
{
MaterielInOtherOrderTable record = new MaterielInOtherOrderTable();
if (index.Value.billNumber == billNumber)
{
isRet = true;
break;
}
}
return(isRet);
}
public SortedDictionary <int, MaterielInOtherOrderTable> getAllNotReviewMaterielInOtherOrderInfo()
{
SortedDictionary <int, MaterielInOtherOrderTable> list = new SortedDictionary <int, MaterielInOtherOrderTable>();
foreach (KeyValuePair <int, MaterielInOtherOrderTable> index in m_tableDataList)
{
MaterielInOtherOrderTable record = new MaterielInOtherOrderTable();
record = index.Value;
if (index.Value.isReview != "1")
{
list.Add(list.Count, index.Value);
}
}
return(list);
}
public void insert(MaterielInOtherOrderTable record, bool isDisplayMessageBox = true)
{
bool isExistReview = false;
MaterielInOtherOrderTable oldRecord = new MaterielInOtherOrderTable();
string insert = "INSERT INTO [dbo].[WAREHOUSE_MANAGEMENT_IN_OTHER]([TRADING_DATE],[BILL_NUMBER],";
insert += "[EXCHANGES_UNIT],[SUM_VALUE],[SUM_MONEY],[MAKE_ORDER_STAFF],[STAFF_SAVE_ID],[STAFF_CHECK_ID],";
insert += "[IS_RED_BILL],[IS_IN_LEDGER]";
// 根据单据编号，判断库中是否已经存在该单据 如果存在单据首先删除单据，然后再执行插入操作
if (checkBillIsExist(record.billNumber))
{
if (checkBillIsReview(record.billNumber))
{
isExistReview = true;
insert += ",[ORDERR_REVIEW], [REVIEW_DATE], [IS_REVIEW] ";
oldRecord = getMaterielInOtherOrderInfoFromBillNumber(record.billNumber);
}
delete(record.billNumber);
}
insert += ") VALUES(";
insert += "'" + record.tradingDate + "',";
insert += "'" + record.billNumber + "',";
insert += "'" + record.exchangesUnit + "',";
insert += "'" + record.sumValue + "',";
insert += "'" + record.sumMoney + "',";
insert += record.makeOrderStaff + ",";
insert += record.staffSaveId + ",";
insert += record.orderReviewStaffId + ",";
// 红字蓝字标示
insert += record.isRedBill + ",0";
// 如果单据已经审核过，把之前的审核信息写到该记录中
if (isExistReview)
{
insert += ",";
insert += oldRecord.orderrReview + ",";
insert += "'" + oldRecord.reviewDate + "', 1";
}
insert += ")";
try
{
DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert);
if (isDisplayMessageBox)
{
MessageBoxExtend.messageOK("数据保存成功");
}
load();
}
catch (Exception error)
{
MessageBoxExtend.messageWarning(error.Message);
return;
}
writeOperatorLog(303, OperatorLogType.Add, record.billNumber);
}