public void InsertBudgetSalesFee(int CustomerID, int ExpenseItemID, DateTime Period, decimal OriginalBudget, decimal NormalBudget, decimal AdjustBudget, decimal TransferBudget, int UserID, int PositionID, String ModifyReason) { SqlTransaction transaction = null; try { ////事务开始 transaction = TableAdapterHelper.BeginTransaction(this.TABudgetSales); TableAdapterHelper.SetTransaction(this.TABudgetSalesHistory, transaction); //检验重复性 客户、费用项、费用期间三项不能重复 int iCount = this.TABudgetSales.SearchBudgetSalesByIns(CustomerID, ExpenseItemID, Period).GetValueOrDefault(); if (iCount > 0) { throw new ApplicationException("客户、费用项、费用期间三项不能重复!"); } // 父表 BudgetDS.BudgetSalesFeeDataTable table = new BudgetDS.BudgetSalesFeeDataTable(); BudgetDS.BudgetSalesFeeRow row = table.NewBudgetSalesFeeRow(); row.CustomerID = CustomerID; row.ExpenseItemID = ExpenseItemID; row.Period = Period; row.OriginalBudget = OriginalBudget; row.NormalBudget = NormalBudget; row.AdjustBudget = AdjustBudget; row.TransferBudget = TransferBudget; row.ModifyReason = ModifyReason; table.AddBudgetSalesFeeRow(row); this.TABudgetSales.Update(table); // 子表 BudgetDS.BudgetSalesFeeHistoryDataTable tableDetail = new BudgetDS.BudgetSalesFeeHistoryDataTable(); BudgetDS.BudgetSalesFeeHistoryRow rowDetail = tableDetail.NewBudgetSalesFeeHistoryRow(); rowDetail.BudgetSalesFeeID = row.BudgetSalesFeeID; rowDetail.UserID = UserID; rowDetail.PositionID = PositionID; rowDetail.ModifyDate = DateTime.Now; rowDetail.Action = "Create"; rowDetail.CustomerID = CustomerID; rowDetail.ExpenseItemID = ExpenseItemID; rowDetail.Period = Period; rowDetail.OriginalBudget = OriginalBudget; rowDetail.NormalBudget = NormalBudget; rowDetail.AdjustBudget = AdjustBudget; rowDetail.TransferBudget = TransferBudget; rowDetail.ModifyReason = ModifyReason; tableDetail.AddBudgetSalesFeeHistoryRow(rowDetail); this.TABudgetSalesHistory.Update(tableDetail); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { transaction.Dispose(); } }
public void UpdateBudgetSalesFee(int BudgetSalesFeeID, decimal NormalBudget, decimal AdjustBudget, decimal TransferBudget, int UserID, int PositionID, String ModifyReason) { SqlTransaction transaction = null; try { ////事务开始 transaction = TableAdapterHelper.BeginTransaction(this.TABudgetSales); TableAdapterHelper.SetTransaction(this.TABudgetSalesHistory, transaction); // 父表 BudgetDS.BudgetSalesFeeRow row = this.TABudgetSales.GetDataById(BudgetSalesFeeID)[0]; if (row.TotalBudget > (NormalBudget + AdjustBudget)) //如果是减少预算,要做检查 { decimal[] calculateAssistant = new decimal[14]; calculateAssistant = this.GetSalesBudgetByParameter(row.CustomerID, row.Period, new MasterDataBLL().GetExpenseItemByID(row.ExpenseItemID).ExpenseSubCategoryID); if (row.TotalBudget - NormalBudget - AdjustBudget - TransferBudget > calculateAssistant[12]) { throw new ApplicationException("本次修改导致原有记录超预算,不能修改"); } } row.NormalBudget = NormalBudget; row.AdjustBudget = AdjustBudget; row.TransferBudget = TransferBudget; row.ModifyReason = ModifyReason; this.TABudgetSales.Update(row); // 子表 BudgetDS.BudgetSalesFeeHistoryDataTable tableDetail = new BudgetDS.BudgetSalesFeeHistoryDataTable(); BudgetDS.BudgetSalesFeeHistoryRow rowDetail = tableDetail.NewBudgetSalesFeeHistoryRow(); rowDetail.BudgetSalesFeeID = row.BudgetSalesFeeID; rowDetail.UserID = UserID; rowDetail.PositionID = PositionID; rowDetail.ModifyDate = DateTime.Now; rowDetail.Action = "Modify"; rowDetail.CustomerID = row.CustomerID; rowDetail.ExpenseItemID = row.ExpenseItemID; rowDetail.Period = row.Period; rowDetail.OriginalBudget = row.OriginalBudget; rowDetail.NormalBudget = NormalBudget; rowDetail.AdjustBudget = AdjustBudget; rowDetail.TransferBudget = TransferBudget; rowDetail.ModifyReason = ModifyReason; tableDetail.AddBudgetSalesFeeHistoryRow(rowDetail); this.TABudgetSalesHistory.Update(tableDetail); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { transaction.Dispose(); } }
public void DeleteBudgetSalesFeeByID(int BudgetSalesFeeId) { SqlTransaction transaction = null; BudgetDS.BudgetSalesFeeRow row = TABudgetSales.GetDataById(BudgetSalesFeeId)[0]; try { decimal[] calculateAssistant = new decimal[14]; calculateAssistant = this.GetSalesBudgetByParameter(row.CustomerID, row.Period, new MasterDataBLL().GetExpenseItemByID(row.ExpenseItemID).ExpenseSubCategoryID); if (row.TotalBudget > calculateAssistant[12]) { throw new ApplicationException("本次修改导致原有记录超预算,不能修改"); } transaction = TableAdapterHelper.BeginTransaction(TABudgetSales); TableAdapterHelper.SetTransaction(TABudgetSalesHistory, transaction); TABudgetSalesHistory.DeleteByBudgetSalesFeeId(BudgetSalesFeeId); TABudgetSales.Delete(BudgetSalesFeeId); transaction.Commit(); } catch (SqlException ex) { if (transaction != null) { transaction.Rollback(); } if (ex.Class == 16) { throw new ApplicationException("不能删除,该销售预算已被引用"); } else { throw ex; } } finally { if (transaction != null) { transaction.Dispose(); } } this.TABudgetSales.Delete(BudgetSalesFeeId); }
public void SaveDataToDB(string FullPath, string FileName, string excelFileExtension) { SqlTransaction transaction = null; try { DataTable dt = null; dt = this.GetDataSet(FullPath, excelFileExtension).Tables[0]; if (dt.Rows.Count <= 1) { PageUtility.ShowModelDlg(this.Page, "文件中没有任何记录,请重新选择"); return; } BudgetSalesFeeTableAdapter TABudgetSalesFee = new BudgetSalesFeeTableAdapter(); ImportLogTableAdapter TAImportLog = new ImportLogTableAdapter(); ImportLogDetailTableAdapter TAImportLogDetail = new ImportLogDetailTableAdapter(); transaction = TableAdapterHelper.BeginTransaction(TABudgetSalesFee); TableAdapterHelper.SetTransaction(TAImportLog, transaction); TableAdapterHelper.SetTransaction(TAImportLogDetail, transaction); //存储log信息 ImportDS.ImportLogDataTable logTable = new ImportDS.ImportLogDataTable(); ImportDS.ImportLogRow logRow = logTable.NewImportLogRow(); int stuffUserID = ((AuthorizationDS.StuffUserRow)Session["StuffUser"]).StuffUserId; string fullname = this.fileUpLoad.PostedFile.FileName.ToString(); string tmpFile = fullname.Remove(0, fullname.LastIndexOf("\\") + 1); logRow.FileName = tmpFile; logRow.ImportDate = DateTime.Now; logRow.ImportUserID = stuffUserID; logRow.ImportType = 2; logRow.TotalCount = dt.Rows.Count - 1; logRow.SuccessCount = dt.Rows.Count - 1; logRow.FailCount = 0; logTable.AddImportLogRow(logRow); TAImportLog.Update(logTable); //处理每条明细 BudgetDS.BudgetSalesFeeDataTable BudgetSalesTable = new BudgetDS.BudgetSalesFeeDataTable(); ImportDS.ImportLogDetailDataTable ImportLogDetailTable = new ImportDS.ImportLogDetailDataTable(); int row_count = dt.Rows.Count; string errorInfor = string.Empty; // int expenseTypeID = int.Parse(ExpenseTypeDDL.SelectedValue); //开始处理每条明细 for (int i = 1; i <= row_count - 1; i++) { if (CheckData(dt.Rows[i]) != null) { errorInfor = "第" + (i + 1) + "行有错:" + CheckData(dt.Rows[i]); ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else { DataRow row = dt.Rows[i]; string CustomerName = row[0].ToString().Trim(); string CustomerNo = row[1].ToString().Trim(); string Period_Year = row[2].ToString().Trim(); string Period_Month = row[3].ToString().Trim(); string AccountingCode = row[4].ToString().Trim(); string ExpenseManageType = row[5].ToString().Trim(); string OriginalBudget = row[6].ToString().Trim(); string NormalBudget = row[7].ToString().Trim(); string AdjustBudget = row[8].ToString().Trim(); string TransferBudget = row[9].ToString().Trim(); string Remark = row[11].ToString().Trim(); ERS.CustomerDataTable customerTable = new MasterDataBLL().GetCustomerPaged(0, 20, string.Format("CustomerNo='{0}'", CustomerNo), null); if (customerTable == null || customerTable.Count == 0) { errorInfor = "第" + (i + 1) + "行有错:系统中找不到此客户《" + CustomerNo + "》"; ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else if (customerTable.Count > 1) { errorInfor = "第" + (i + 1) + "行有错:系统中找到多个此编号的客户《" + CustomerNo + "》"; ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else { ERS.ExpenseItemDataTable expenseItemTable = new MasterDataBLL().GetExpenseItemPaged(0, 20, null, string.Format("AccountingCode='{0}'", AccountingCode)); if (expenseItemTable == null || expenseItemTable.Count == 0) { errorInfor = "第" + (i + 1) + "行有错:系统中找不到此费用类别《" + AccountingCode + "》"; ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else if (expenseItemTable.Count > 1) { errorInfor = "第" + (i + 1) + "行有错:系统中找到多个此费用类别《" + AccountingCode + "》"; ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else { string temp = Period_Year + "-" + Period_Month + "-01"; string filter = string.Format("CustomerID = {0} and ExpenseItemID= {1} and Period = '{2}'", customerTable[0].CustomerID, expenseItemTable[0].ExpenseItemID, DateTime.Parse(Period_Year + "-" + Period_Month + "-01")); if (new BudgetBLL().GetPagedBudgetSalesFee(filter, 0, 20, null).Count > 0) { errorInfor = "第" + (i + 1) + "行有错:系统中已经存在本年月的预算信息,请在系统中直接更新"; ImportDS.ImportLogDetailRow ImportDetailRow = ImportLogDetailTable.NewImportLogDetailRow(); ImportDetailRow.LogID = logRow.LogID; ImportDetailRow.Line = i + 1; ImportDetailRow.Error = errorInfor; ImportLogDetailTable.AddImportLogDetailRow(ImportDetailRow); logRow.FailCount = logRow.FailCount + 1; logRow.SuccessCount = logRow.SuccessCount - 1; TAImportLog.Update(logRow); continue; } else { BudgetDS.BudgetSalesFeeRow BudgetSalesRow = BudgetSalesTable.NewBudgetSalesFeeRow(); BudgetSalesRow.CustomerID = customerTable[0].CustomerID; BudgetSalesRow.Period = DateTime.Parse(Period_Year + "-" + Period_Month + "-01"); BudgetSalesRow.ExpenseItemID = expenseItemTable[0].ExpenseItemID; BudgetSalesRow.OriginalBudget = decimal.Parse(OriginalBudget); BudgetSalesRow.NormalBudget = decimal.Parse(NormalBudget); BudgetSalesRow.AdjustBudget = decimal.Parse(AdjustBudget); BudgetSalesRow.TransferBudget = decimal.Parse(TransferBudget); BudgetSalesRow.TotalBudget = decimal.Round(BudgetSalesRow.NormalBudget + BudgetSalesRow.AdjustBudget, 2); BudgetSalesRow.ModifyReason = Remark; BudgetSalesTable.AddBudgetSalesFeeRow(BudgetSalesRow); } } } } } TAImportLog.Update(logRow); TAImportLogDetail.Update(ImportLogDetailTable); TABudgetSalesFee.Update(BudgetSalesTable); transaction.Commit(); string returnString = "成功导入" + logRow.SuccessCount.ToString() + "条信息"; PageUtility.ShowModelDlg(this.Page, returnString); } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } PageUtility.ShowModelDlg(this.Page, "Save Fail!" + ex.ToString()); } finally { if (transaction != null) { transaction.Dispose(); } } }