public void insert(ArrayList orderDetailsRecords) { if (orderDetailsRecords.Count <= 0) { return; } else { try { for (int i = 0; i < orderDetailsRecords.Count; i++) { MaterielOutOtherOrderDetailsTable record = (MaterielOutOtherOrderDetailsTable)orderDetailsRecords[i]; if (i == 0 && checkBillIsExist(record.billNumber)) { // 根据单据编号,判断库中是否已经存在该单据 如果存在单据首先删除单据,然后再执行插入操作 delete(record.billNumber); } string insert = "INSERT INTO [dbo].[WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS]([ROW_NUMBER],[MATERIEL_ID],[BILL_NUMBER]"; insert += ",[PRICE],[VALUE],[NOTE])VALUES("; insert += "'" + record.rowNumber + "',"; insert += record.materielID + ","; insert += "'" + record.billNumber + "',"; insert += record.price + ","; insert += record.value + ","; insert += "'" + record.note + "'"; insert += ")"; try { DatabaseAccessFactoryInstance.Instance.ExecuteCommand(FormMain.DB_NAME, insert); } catch (Exception error) { MessageBoxExtend.messageWarning(error.Message); return; } } load(); } catch (Exception) { } } }
public bool checkBillIsExist(string billNumber) { bool isRet = false; foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in m_tableDataList) { MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable(); if (index.Value.billNumber == billNumber) { isRet = true; break; } } return(isRet); }
public SortedDictionary <int, MaterielOutOtherOrderDetailsTable> getMaterielOutOtherOrderCountInfo(string startDate, string endDate, bool isAllBill = false) { SortedDictionary <int, MaterielOutOtherOrderDetailsTable> dataList = new SortedDictionary <int, MaterielOutOtherOrderDetailsTable>(); string querySQL = "SELECT A1.[PKEY],A1.[ROW_NUMBER],A1.[MATERIEL_ID], A1.[BILL_NUMBER],A1.[PRICE],A1.[VALUE],A1.[NOTE] "; querySQL += "FROM [WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS] A1, WAREHOUSE_MANAGEMENT_OUT_OTHER A2"; querySQL += " WHERE A1.BILL_NUMBER = A2.BILL_NUMBER"; querySQL += " AND A2.TRADING_DATE >= '" + startDate + "' AND A2.TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 "; if (!isAllBill) { querySQL += " AND A2.IS_REVIEW = 1"; } querySQL += " ORDER BY A1.[PKEY]"; using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL)) { foreach (DataRow row in dataTable.Rows) { MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.rowNumber = DbDataConvert.ToString(row["ROW_NUMBER"]); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]); // 根据物料ID得到物料相关信息 MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID); record.materielName = materiel.name; record.materielModel = materiel.model; record.materielUnitSale = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitSale); record.price = DbDataConvert.ToDouble(row["PRICE"]); record.value = DbDataConvert.ToDouble(row["VALUE"]); record.sumMoney = record.price * record.value; record.billNumber = DbDataConvert.ToString(row["NOTE"]); dataList.Add(dataList.Count, record); } } return(dataList); }
private void load() { m_tableDataList.Clear(); string querySQL = "SELECT A1.[PKEY],A1.[ROW_NUMBER],A1.[MATERIEL_ID], A1.[BILL_NUMBER],A1.[PRICE],A1.[VALUE],A1.[NOTE] "; querySQL += " FROM [WAREHOUSE_MANAGEMENT_OUT_OTHER_DETAILS] A1, WAREHOUSE_MANAGEMENT_OUT_OTHER A2"; querySQL += " WHERE A1.BILL_NUMBER = A2.BILL_NUMBER"; querySQL += " ORDER BY A1.[PKEY]"; using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL)) { foreach (DataRow row in dataTable.Rows) { MaterielOutOtherOrderDetailsTable record = new MaterielOutOtherOrderDetailsTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.rowNumber = DbDataConvert.ToString(row["ROW_NUMBER"]); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]); // 根据物料ID得到物料相关信息 MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID); record.materielName = materiel.name; record.materielModel = materiel.model; record.materielUnitSale = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitSale); record.price = DbDataConvert.ToDouble(row["PRICE"]); record.value = DbDataConvert.ToDouble(row["VALUE"]); record.sumMoney = record.price * record.value; record.note = DbDataConvert.ToString(row["NOTE"]); m_tableDataList.Add(m_tableDataList.Count, record); } } }
private bool updateMaterielData(string billNumber) { bool isRet = true; SortedDictionary <int, MaterielOutOtherOrderDetailsTable> dataList = MaterielOutOtherOrderDetails.getInctance().getMaterielOutOtherInfoFromBillNumber(billNumber); if (!m_isRedBill) { // 首先检查物料库存余额是否大于本次出库单交易额,如果小于,则提示用户,但是审核失败 foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in dataList) { MaterielOutOtherOrderDetailsTable record = index.Value; InitMaterielTable materielRecord = new InitMaterielTable(); materielRecord.materielID = record.materielID; materielRecord.value = record.value; InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID); if (materielStorageData.value < materielRecord.value) { isRet = false; MessageBoxExtend.messageWarning("物料:[" + materielRecord.materielID + "]库存数量小于当前交易数量,单据审核失败"); break; } } } if (isRet) { foreach (KeyValuePair <int, MaterielOutOtherOrderDetailsTable> index in dataList) { MaterielOutOtherOrderDetailsTable record = index.Value; //InitMaterielTable materielRecord = new InitMaterielTable(); //materielRecord.materielID = record.materielID; //materielRecord.value = (int)record.value; //// 交易后库存数量等于当前仓库库存数减去本次交易数 //InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID); //if (m_isRedBill) //{ // // 如果是销售退货,库存数量应该增加 // materielRecord.value = materielStorageData.value + materielRecord.value; //} //else //{ // materielRecord.value = materielStorageData.value - materielRecord.value; //} //InitMateriel.getInctance().materielOutStorage(materielRecord.materielID, materielRecord.value); #region 更新库存汇总表(INIT_STORAGE_STOCK) // 交易完毕后库存数量 double tradingStocksValue = record.value; // 交易后库存数量等于当前仓库库存数减去本次交易数 InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID); if (m_isRedBill) { // 如果是销售退货,库存数量应该增加 tradingStocksValue = materielStorageData.value + tradingStocksValue; } else { tradingStocksValue = materielStorageData.value - tradingStocksValue; } InitMateriel.getInctance().materielOutStorage(record.materielID, tradingStocksValue); #endregion #region 更新存货明细账表(STORAGE_STOCK_DETAIL) 2012-1-16 00:56 StorageStockDetailTable storageStockDetailRecord = new StorageStockDetailTable(); storageStockDetailRecord.materielID = record.materielID; storageStockDetailRecord.tradingDate = DateTime.Now.ToString("yyyyMMdd"); storageStockDetailRecord.billNumber = billNumber; storageStockDetailRecord.thingsType = "其他出库"; storageStockDetailRecord.isIn = 0; // 本次交易数量和单价 if (m_isRedBill) { storageStockDetailRecord.value = record.value * -1; } else { storageStockDetailRecord.value = record.value; } storageStockDetailRecord.price = record.price; // 交易完毕后数量和单价 storageStockDetailRecord.storageValue = tradingStocksValue; storageStockDetailRecord.storagePrice = materielStorageData.price; StorageStockDetail.getInctance().insert(storageStockDetailRecord); #endregion } } return(isRet); }