private void load() { string sql = "SELECT [PKEY],[TRADING_DATE],[BANK_ID], [BILL_NUMBER],[VOUCHERS_NUMBER],[BILL_NAME],[BILL_TYPE_ID],[SOURCE_BILL_NUMBER],[EXCHANGES_UNIT],"; sql += "[TURNOVER],[BALANCE],[MAKE_ORDER_STAFF],[ORDERR_EVIEW],[IS_REVIEW],[NOTE] FROM [dbo].[BANK_CASHSUB_LEDGER] ORDER BY TRADING_DATE"; m_bankCashsubLedgerList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { BankCashsubLedgerTable record = new BankCashsubLedgerTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd"); record.bankID = DbDataConvert.ToInt32(row["BANK_ID"]); record.bankName = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_BANK_LIST", record.bankID); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.vouchersNumber = DbDataConvert.ToString(row["VOUCHERS_NUMBER"]); record.billName = DbDataConvert.ToString(row["BILL_NAME"]); record.billTypeID = DbDataConvert.ToInt32(row["BILL_TYPE_ID"]); record.sourceBillNumber = DbDataConvert.ToString(row["SOURCE_BILL_NUMBER"]); record.exchangesUnit = DbDataConvert.ToInt32(row["EXCHANGES_UNIT"]); if (record.billName == "付款单") { record.billTypeName = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_PAYMENT_TYPE_LIST", record.billTypeID); record.exchangesUnitName = Supplier.getInctance().getSupplierNameFromPkey(record.exchangesUnit); } else { record.billTypeName = AuxiliaryMaterial.getInctance().getAuxiliaryMaterialNameFromPkey("BASE_RECEIVABLE_TYPE_LIST", record.billTypeID); record.exchangesUnitName = Customer.getInctance().getCustomerNameFromPkey(record.exchangesUnit); } record.turnover = DbDataConvert.ToDouble(row["TURNOVER"]); record.makeOrderStaff = DbDataConvert.ToInt32(row["MAKE_ORDER_STAFF"]); record.makeOrderStaffName = Staff.getInctance().getStaffNameFromPkey(record.makeOrderStaff); record.isReview = DbDataConvert.ToString(row["IS_REVIEW"]); record.note = DbDataConvert.ToString(row["NOTE"]); if (record.isReview == "1") { record.balance = DbDataConvert.ToDouble(row["BALANCE"]); record.orderReview = DbDataConvert.ToInt32(row["ORDERR_EVIEW"]); record.orderReviewName = Staff.getInctance().getStaffNameFromPkey(record.orderReview); } m_bankCashsubLedgerList.Add(m_bankCashsubLedgerList.Count, record); } } }
private void load() { string sql = "SELECT [PKEY],[SUPPLIER_ID],[TRADING_DATE],[BILL_NUMBER],[PURCHASE_TYPE],[DELIVERY_DATE],[PAYMENT_DATE],"; sql += "[EXCHANGES_UNIT],[BUSINESS_PEOPLE_ID],[SUM_VALUE],[SUM_MONEY],[SUM_TRANSPORTATION_COST],[SUM_OTHER_COST],"; sql += "[TOTAL_MONEY], [MAKE_ORDER_STAFF],[ORDERR_REVIEW],[REVIEW_DATE],[IS_REVIEW],[IS_IN_STORAGE],[ACTUAL_VALUE] "; sql += "FROM [dbo].[PURCHASE_ORDER] ORDER BY PKEY DESC"; m_tableDataList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { PurchaseOrderTable record = new PurchaseOrderTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]); record.supplierName = Supplier.getInctance().getSupplierNameFromPkey(record.supplierId); record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd"); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.purchaseType = DbDataConvert.ToString(row["PURCHASE_TYPE"]); record.deliveryDate = DbDataConvert.ToDateTime(row["DELIVERY_DATE"]).ToString("yyyy-MM-dd"); record.paymentDate = DbDataConvert.ToDateTime(row["PAYMENT_DATE"]).ToString("yyyy-MM-dd"); record.exchangesUnit = DbDataConvert.ToString(row["EXCHANGES_UNIT"]); record.businessPeopleId = DbDataConvert.ToInt32(row["BUSINESS_PEOPLE_ID"]); record.businessPeopleName = Staff.getInctance().getStaffNameFromPkey(record.businessPeopleId); record.makeOrderStaff = DbDataConvert.ToInt32(row["MAKE_ORDER_STAFF"]); record.makeOrderStaffName = Staff.getInctance().getStaffNameFromPkey(record.makeOrderStaff); 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.isReview = DbDataConvert.ToString(row["IS_REVIEW"]); record.sumValue = DbDataConvert.ToString(row["SUM_VALUE"]); record.sumMoney = DbDataConvert.ToString(row["SUM_MONEY"]); record.sumTransportationCost = DbDataConvert.ToString(row["SUM_TRANSPORTATION_COST"]); record.sumOtherCost = DbDataConvert.ToString(row["SUM_OTHER_COST"]); record.totalMoney = DbDataConvert.ToString(row["TOTAL_MONEY"]); // 是否已入库和实际入库数量 record.isInStorage = DbDataConvert.ToString(row["IS_IN_STORAGE"]); record.actualValue = DbDataConvert.ToString(row["ACTUAL_VALUE"]); m_tableDataList.Add(m_tableDataList.Count, record); } } }
public SortedDictionary <int, PurchaseInOrderTable> getPurchaseInOrderCountInfo(string startDate, string endDate, bool isAllBill = false, int countType = 1) { SortedDictionary <int, PurchaseInOrderTable> dataList = new SortedDictionary <int, PurchaseInOrderTable>(); string groupBySQL = "", querySQL = ""; // countType 等于1是按供应商汇总,countType等于2是采购员汇总 if (countType == 1) { querySQL = "SELECT [SUPPLIER_ID],SUM([TOTAL_MONEY]) AS TOTAL_MONEY FROM [dbo].[PURCHASE_IN_ORDER] WHERE "; groupBySQL += " GROUP BY SUPPLIER_ID"; } else if (countType == 2) { querySQL = "SELECT [BUSINESS_PEOPLE_ID],SUM([TOTAL_MONEY]) AS TOTAL_MONEY FROM [dbo].[PURCHASE_IN_ORDER] WHERE "; groupBySQL += " GROUP BY BUSINESS_PEOPLE_ID"; } string query = querySQL; query += " TRADING_DATE >= '" + startDate + "' AND TRADING_DATE <= '" + endDate + "' AND IS_RED_BILL = 0 "; if (!isAllBill) { query += " AND IS_REVIEW = 1"; } query += groupBySQL; query += " ORDER BY TOTAL_MONEY DESC"; using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, query)) { foreach (DataRow row in dataTable.Rows) { PurchaseInOrderTable record = new PurchaseInOrderTable(); if (countType == 1) { record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]); record.supplierName = Supplier.getInctance().getSupplierNameFromPkey(record.supplierId); } else if (countType == 2) { record.businessPeopleId = DbDataConvert.ToInt32(row["BUSINESS_PEOPLE_ID"]); record.businessPeopleName = Staff.getInctance().getStaffNameFromPkey(record.businessPeopleId); } record.totalMoney = DbDataConvert.ToString(row["TOTAL_MONEY"]); dataList.Add(dataList.Count, record); } } return(dataList); }
public SortedDictionary <int, SupplierPriceSheetTable> getAllSupplierPriceSheetInfo(int matetielID) { string sql = "SELECT [PKEY],[SUPPLIER_ID],[MATERIEL_ID] ,[ORNM_FROM_VALUE],[ORNM_TO_VALUE],[PRICE],[DATE],[NOTE] "; sql += "FROM [dbo].[PURCHASE_SUPPLIER_PRICE_SHEET] WHERE MATERIEL_ID = " + matetielID + " ORDER BY PKEY"; SortedDictionary <int, SupplierPriceSheetTable> tableDataList = new SortedDictionary <int, SupplierPriceSheetTable>(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { SupplierPriceSheetTable record = new SupplierPriceSheetTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]); if (record.supplierId != -1) { SupplierTable supplier = Supplier.getInctance().getSupplierInfoFromPkey(record.supplierId); record.supplierName = supplier.name; record.contact = supplier.contact; record.tel = supplier.tel; } record.matetielId = DbDataConvert.ToInt32(row["MATERIEL_ID"]); record.ORNMFromValue = DbDataConvert.ToString(row["ORNM_FROM_VALUE"]); record.ORNMToValue = DbDataConvert.ToString(row["ORNM_TO_VALUE"]); record.pirce = DbDataConvert.ToString(row["PRICE"]); record.date = DbDataConvert.ToDateTime(row["DATE"]).ToString("yyyy-MM-dd"); record.note = DbDataConvert.ToString(row["NOTE"]); tableDataList.Add(tableDataList.Count, record); } } return(tableDataList); }
private void load() { string sql = "SELECT [PKEY],[SUPPLIER_ID],[TRADING_DATE],[BILL_NUMBER],[PURCHASE_TYPE],[PAYMENT_DATE],"; sql += "[EXCHANGES_UNIT],[BUSINESS_PEOPLE_ID],[SUM_VALUE],[SUM_MONEY],[SUM_TRANSPORTATION_COST],[SUM_OTHER_COST],"; sql += "[TOTAL_MONEY], [MAKE_ORDER_STAFF],[ORDERR_REVIEW],[REVIEW_DATE],[IS_REVIEW],"; sql += "[SOURCE_BILL_TYPE], [SOURCE_BILL_NUMBER],[STAFF_SAVE_ID],[STAFF_CHECK_ID],[PAYMENT_OK],[PAYMENT_NO_OK],"; sql += "[IS_RED_BILL],[IS_IN_LEDGER],[ORDERR_IN_LEDGER],[IN_LEDGER_DATE] "; sql += "FROM [dbo].[PURCHASE_IN_ORDER] ORDER BY PKEY DESC"; m_tableDataList.Clear(); using (DataTable dataTable = DatabaseAccessFactoryInstance.Instance.QueryDataTable(FormMain.DB_NAME, sql)) { foreach (DataRow row in dataTable.Rows) { PurchaseInOrderTable record = new PurchaseInOrderTable(); record.pkey = DbDataConvert.ToInt32(row["PKEY"]); record.supplierId = DbDataConvert.ToInt32(row["SUPPLIER_ID"]); record.supplierName = Supplier.getInctance().getSupplierNameFromPkey(record.supplierId); record.tradingDate = DbDataConvert.ToDateTime(row["TRADING_DATE"]).ToString("yyyy-MM-dd"); record.billNumber = DbDataConvert.ToString(row["BILL_NUMBER"]); record.purchaseType = DbDataConvert.ToString(row["PURCHASE_TYPE"]); record.paymentDate = DbDataConvert.ToDateTime(row["PAYMENT_DATE"]).ToString("yyyy-MM-dd"); record.exchangesUnit = DbDataConvert.ToString(row["EXCHANGES_UNIT"]); record.businessPeopleId = DbDataConvert.ToInt32(row["BUSINESS_PEOPLE_ID"]); record.businessPeopleName = Staff.getInctance().getStaffNameFromPkey(record.businessPeopleId); record.makeOrderStaff = DbDataConvert.ToInt32(row["MAKE_ORDER_STAFF"]); record.makeOrderStaffName = Staff.getInctance().getStaffNameFromPkey(record.makeOrderStaff); 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.isReview = DbDataConvert.ToString(row["IS_REVIEW"]); record.sumValue = DbDataConvert.ToString(row["SUM_VALUE"]); record.sumMoney = DbDataConvert.ToString(row["SUM_MONEY"]); record.sumTransportationCost = DbDataConvert.ToString(row["SUM_TRANSPORTATION_COST"]); record.sumOtherCost = DbDataConvert.ToString(row["SUM_OTHER_COST"]); record.totalMoney = DbDataConvert.ToString(row["TOTAL_MONEY"]); // 采购入库单比采购订单中多出的字段 record.sourceBillType = DbDataConvert.ToString(row["SOURCE_BILL_TYPE"]); record.sourceBillNumber = DbDataConvert.ToString(row["SOURCE_BILL_NUMBER"]); record.staffSaveId = DbDataConvert.ToInt32(row["STAFF_SAVE_ID"]); record.staffSaveName = Staff.getInctance().getStaffNameFromPkey(record.staffSaveId); record.staffCheckId = DbDataConvert.ToInt32(row["STAFF_CHECK_ID"]); record.staffCheckName = Staff.getInctance().getStaffNameFromPkey(record.staffCheckId); // 已还款金额和未还款金额 record.paymentOk = DbDataConvert.ToString(row["PAYMENT_OK"]); record.paymentNoOk = DbDataConvert.ToString(row["PAYMENT_NO_OK"]); // 红字蓝字标示和是否入账标记 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.orderrReview); 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); } } }