private void updateMaterielData(string billNumber) { SortedDictionary <int, PurchaseInOrderDetailsTable> dataList = PurchaseInOrderDetails.getInctance().getPurchaseInfoFromBillNumber(billNumber); foreach (KeyValuePair <int, PurchaseInOrderDetailsTable> index in dataList) { PurchaseInOrderDetailsTable record = index.Value; #region 更新库存汇总表(INIT_STORAGE_STOCK) InitMaterielTable materielRecord = new InitMaterielTable(); materielRecord.materielID = record.materielID; materielRecord.value = record.value; materielRecord.price = record.price; if (record.sumMoney != record.totalMoney) { // 如果物料总费用和总金额不相等,代表有需要计入成本的费用发生,需要重新核算物料单价 materielRecord.price = (record.costApportionments + record.sumMoney) / record.value; } if (m_isRedBill) { InitMateriel.getInctance().materielOutStorage(materielRecord, false); } else { InitMateriel.getInctance().insert(materielRecord, false); } #endregion #region 更新存货明细账表(STORAGE_STOCK_DETAIL) 2012-1-16 01:22 StorageStockDetailTable storageStockDetailRecord = new StorageStockDetailTable(); storageStockDetailRecord.materielID = record.materielID; storageStockDetailRecord.tradingDate = DateTime.Now.ToString("yyyyMMdd"); storageStockDetailRecord.billNumber = billNumber; storageStockDetailRecord.thingsType = "采购入库"; storageStockDetailRecord.isIn = 1; // 本次交易数量和单价 if (m_isRedBill) { storageStockDetailRecord.value = record.value * -1; } else { storageStockDetailRecord.value = record.value; } storageStockDetailRecord.price = record.price; // 交易完毕后数量和单价 InitMaterielTable materielStorageData = InitMateriel.getInctance().getMaterielInfoFromMaterielID(record.materielID); storageStockDetailRecord.storageValue = materielStorageData.value; storageStockDetailRecord.storagePrice = materielStorageData.price; StorageStockDetail.getInctance().insert(storageStockDetailRecord); #endregion } }
public void insert(ArrayList purchaseOrderDetailsRecords) { if (purchaseOrderDetailsRecords.Count <= 0) { return; } else { try { for (int i = 0; i < purchaseOrderDetailsRecords.Count; i++) { PurchaseInOrderDetailsTable record = (PurchaseInOrderDetailsTable)purchaseOrderDetailsRecords[i]; if (i == 0 && checkBillIsExist(record.billNumber)) { // 根据单据编号,判断库中是否已经存在该单据 如果存在单据首先删除单据,然后再执行插入操作 delete(record.billNumber); } string insert = "INSERT INTO [dbo].[PURCHASE_IN_ORDER_DETAILS]([ROW_NUMBER],[MATERIEL_ID],[BILL_NUMBER]"; insert += ",[PRICE],[VALUE],[TRANSPORTATION_COST],[OTHER_COST])VALUES("; insert += "'" + record.rowNumber + "',"; insert += record.materielID + ","; insert += "'" + record.billNumber + "',"; insert += record.price + ","; insert += record.value + ","; insert += record.costApportionments + ","; insert += record.noCostApportionments; 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, PurchaseInOrderDetailsTable> index in m_tableDataList) { PurchaseInOrderDetailsTable record = new PurchaseInOrderDetailsTable(); if (index.Value.billNumber == billNumber) { isRet = true; break; } } return(isRet); }
private void load() { string sql = "SELECT [PKEY],[ROW_NUMBER],[MATERIEL_ID],[BILL_NUMBER],[PRICE],[VALUE]"; sql += ",[TRANSPORTATION_COST],[OTHER_COST] FROM [dbo].[PURCHASE_IN_ORDER_DETAILS] ORDER BY PKEY"; m_tableDataList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { PurchaseInOrderDetailsTable record = new PurchaseInOrderDetailsTable(); 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.brand = materiel.brand; record.materielUnitPurchase = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_UNIT_LIST", materiel.unitPurchase); record.price = DbDataConvert.ToDouble(row["PRICE"]); record.value = DbDataConvert.ToDouble(row["VALUE"]); record.sumMoney = record.price * record.value; record.costApportionments = DbDataConvert.ToDouble(row["TRANSPORTATION_COST"]); record.noCostApportionments = DbDataConvert.ToDouble(row["OTHER_COST"]); record.totalMoney = record.sumMoney + record.costApportionments + record.noCostApportionments; m_tableDataList.Add(m_tableDataList.Count, record); } } }
public SortedDictionary <int, PurchaseInOrderDetailsTable> getPurchaseInOrderCountInfo(string startDate, string endDate, bool isAllBill = false) { SortedDictionary <int, PurchaseInOrderDetailsTable> dataList = new SortedDictionary <int, PurchaseInOrderDetailsTable>(); string querySQL = "SELECT [MATERIEL_ID], SUM([PRICE]*[VALUE]+[TRANSPORTATION_COST]+[OTHER_COST]) AS TOTAL_MONEY "; querySQL += "FROM [PURCHASE_IN_ORDER_DETAILS] WHERE BILL_NUMBER IN("; querySQL += "SELECT BILL_NUMBER FROM [dbo].[PURCHASE_IN_ORDER] WHERE "; querySQL += "TRADING_DATE >= '" + startDate + "' AND TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 "; if (!isAllBill) { querySQL += " AND IS_REVIEW = 1"; } querySQL += ") GROUP BY [MATERIEL_ID]"; querySQL += " ORDER BY TOTAL_MONEY DESC"; using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, querySQL)) { foreach (DataRow row in dataTable.Rows) { PurchaseInOrderDetailsTable record = new PurchaseInOrderDetailsTable(); record.materielID = DbDataConvert.ToInt32(row["MATERIEL_ID"]); MaterielTable materiel = Materiel.getInctance().getMaterielInfoFromPkey(record.materielID); record.materielName = materiel.name; record.materielModel = materiel.model; record.totalMoney = DbDataConvert.ToDouble(row["TOTAL_MONEY"]); dataList.Add(dataList.Count, record); } } return(dataList); }