public void InsertBudgetManageFee(int OrganizationUnitID, DateTime Period, int ExpenseManageTypeID, decimal OriginalBudget, decimal NormalBudget, decimal?AdjustBudget, int UserID, int PositionID, string ModifyReason) { SqlTransaction transaction = null; try { ////事务开始 transaction = TableAdapterHelper.BeginTransaction(this.TABudgetManage); TableAdapterHelper.SetTransaction(this.TABudgetManageHistory, transaction); // 父表 BudgetDS.BudgetManageFeeDataTable table = new BudgetDS.BudgetManageFeeDataTable(); BudgetDS.BudgetManageFeeRow row = table.NewBudgetManageFeeRow(); row.OrganizationUnitID = OrganizationUnitID; row.Period = Period; row.ExpenseManageTypeID = ExpenseManageTypeID; row.OriginalBudget = OriginalBudget; row.NormalBudget = NormalBudget; row.AdjustBudget = AdjustBudget.GetValueOrDefault(); if (ModifyReason != null) { row.ModifyReason = ModifyReason; } table.AddBudgetManageFeeRow(row); this.TABudgetManage.Update(table); // 子表 BudgetDS.BudgetManageFeeHistoryDataTable tableDetail = new BudgetDS.BudgetManageFeeHistoryDataTable(); BudgetDS.BudgetManageFeeHistoryRow rowDetail = tableDetail.NewBudgetManageFeeHistoryRow(); rowDetail.OrganizationUnitID = OrganizationUnitID; rowDetail.Period = Period; rowDetail.ExpenseManageTypeID = ExpenseManageTypeID; rowDetail.OriginalBudget = OriginalBudget; rowDetail.NormalBudget = NormalBudget; rowDetail.AdjustBudget = AdjustBudget.GetValueOrDefault(); rowDetail.Action = "Create"; rowDetail.ModifyDate = DateTime.Now; rowDetail.PositionID = PositionID; rowDetail.UserID = UserID; if (ModifyReason != null) { rowDetail.ModifyReason = ModifyReason; } rowDetail.BudgetManageFeeID = row.BudgetManageFeeID; tableDetail.AddBudgetManageFeeHistoryRow(rowDetail); this.TABudgetManageHistory.Update(tableDetail); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { transaction.Dispose(); } }
public void InsertBudgetManageFee(int OrganizationUnitID, DateTime Period, int ExpenseManageTypeID, decimal OriginalBudget, decimal NormalBudget, decimal? AdjustBudget, int UserID, int PositionID, string ModifyReason) { SqlTransaction transaction = null; try { ////����ʼ transaction = TableAdapterHelper.BeginTransaction(this.TABudgetManage); TableAdapterHelper.SetTransaction(this.TABudgetManageHistory, transaction); // ���� BudgetDS.BudgetManageFeeDataTable table = new BudgetDS.BudgetManageFeeDataTable(); BudgetDS.BudgetManageFeeRow row = table.NewBudgetManageFeeRow(); row.OrganizationUnitID = OrganizationUnitID; row.Period = Period; row.ExpenseManageTypeID = ExpenseManageTypeID; row.OriginalBudget = OriginalBudget; row.NormalBudget = NormalBudget; row.AdjustBudget = AdjustBudget.GetValueOrDefault(); if (ModifyReason != null) { row.ModifyReason = ModifyReason; } table.AddBudgetManageFeeRow(row); this.TABudgetManage.Update(table); // �ӱ� BudgetDS.BudgetManageFeeHistoryDataTable tableDetail = new BudgetDS.BudgetManageFeeHistoryDataTable(); BudgetDS.BudgetManageFeeHistoryRow rowDetail = tableDetail.NewBudgetManageFeeHistoryRow(); rowDetail.OrganizationUnitID = OrganizationUnitID; rowDetail.Period = Period; rowDetail.ExpenseManageTypeID = ExpenseManageTypeID; rowDetail.OriginalBudget = OriginalBudget; rowDetail.NormalBudget = NormalBudget; rowDetail.AdjustBudget = AdjustBudget.GetValueOrDefault(); rowDetail.Action = "Create"; rowDetail.ModifyDate = DateTime.Now; rowDetail.PositionID = PositionID; rowDetail.UserID = UserID; if (ModifyReason != null) { rowDetail.ModifyReason = ModifyReason; } rowDetail.BudgetManageFeeID = row.BudgetManageFeeID; tableDetail.AddBudgetManageFeeHistoryRow(rowDetail); this.TABudgetManageHistory.Update(tableDetail); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { transaction.Dispose(); } }
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; } BudgetManageFeeTableAdapter TABudgetManageFee = new BudgetManageFeeTableAdapter(); ImportLogTableAdapter TAImportLog = new ImportLogTableAdapter(); ImportLogDetailTableAdapter TAImportLogDetail = new ImportLogDetailTableAdapter(); transaction = TableAdapterHelper.BeginTransaction(TABudgetManageFee); 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 = 1; logRow.TotalCount = dt.Rows.Count - 1; logRow.SuccessCount = dt.Rows.Count - 1; logRow.FailCount = 0; logTable.AddImportLogRow(logRow); TAImportLog.Update(logTable); //处理每条明细 BudgetDS.BudgetManageFeeDataTable BudgetManageTable = new BudgetDS.BudgetManageFeeDataTable(); 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 DepartmentName = row[0].ToString().Trim(); string Period_Year = row[1].ToString().Trim(); string Period_Month = row[2].ToString().Trim(); string AccountingCode = row[3].ToString().Trim(); string ExpenseManageType = row[4].ToString().Trim(); string OriginalBudget = row[5].ToString().Trim(); string NormalBudget = row[6].ToString().Trim(); string AdjustBudget = row[7].ToString().Trim(); string Remark = row[9].ToString().Trim(); AuthorizationDS.OrganizationUnitDataTable ouTable = new OUTreeBLL().GetDataByOrganizationUnitName(DepartmentName); if (ouTable == null || ouTable.Count == 0) { errorInfor = "第" + (i + 1) + "行有错:系统中找不到此部门《" + DepartmentName + "》"; 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 (ouTable.Count > 1) { errorInfor = "第" + (i + 1) + "行有错:系统中找到多个此名称的部门《" + DepartmentName + "》"; 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.ExpenseManageTypeDataTable emtTable = new MasterDataBLL().GetExpenseManageTypePaged(0, 20, string.Format("AccountingCode = '{0}'", AccountingCode), null); if (emtTable == null || emtTable.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 (emtTable.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("OrganizationUnitID = {0} and ExpenseManageTypeID= {1} and Period = '{2}'", ouTable[0].OrganizationUnitId, emtTable[0].ExpenseManageTypeID, DateTime.Parse(Period_Year + "-" + Period_Month + "-01")); if (new BudgetBLL().GetPagedBudgetManageFee(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.BudgetManageFeeRow BudgetManageRow = BudgetManageTable.NewBudgetManageFeeRow(); BudgetManageRow.OrganizationUnitID = ouTable[0].OrganizationUnitId; BudgetManageRow.Period = DateTime.Parse(Period_Year + "-" + Period_Month + "-01"); BudgetManageRow.ExpenseManageTypeID = emtTable[0].ExpenseManageTypeID; BudgetManageRow.OriginalBudget = decimal.Parse(OriginalBudget); BudgetManageRow.NormalBudget = decimal.Parse(NormalBudget); BudgetManageRow.AdjustBudget = decimal.Parse(AdjustBudget); BudgetManageRow.TotalBudget = decimal.Round(BudgetManageRow.NormalBudget + BudgetManageRow.AdjustBudget, 2); BudgetManageRow.ModifyReason = Remark; BudgetManageTable.AddBudgetManageFeeRow(BudgetManageRow); } } } } } TAImportLog.Update(logRow); TAImportLogDetail.Update(ImportLogDetailTable); TABudgetManageFee.Update(BudgetManageTable); 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(); } } }
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; } BudgetManageFeeTableAdapter TABudgetManageFee = new BudgetManageFeeTableAdapter(); ImportLogTableAdapter TAImportLog = new ImportLogTableAdapter(); ImportLogDetailTableAdapter TAImportLogDetail = new ImportLogDetailTableAdapter(); transaction = TableAdapterHelper.BeginTransaction(TABudgetManageFee); 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 = 1; logRow.TotalCount = dt.Rows.Count - 1; logRow.SuccessCount = dt.Rows.Count - 1; logRow.FailCount = 0; logTable.AddImportLogRow(logRow); TAImportLog.Update(logTable); //����ÿ����ϸ BudgetDS.BudgetManageFeeDataTable BudgetManageTable = new BudgetDS.BudgetManageFeeDataTable(); 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 DepartmentName = row[0].ToString().Trim(); string Period_Year = row[1].ToString().Trim(); string Period_Month = row[2].ToString().Trim(); string AccountingCode = row[3].ToString().Trim(); string ExpenseManageType = row[4].ToString().Trim(); string OriginalBudget = row[5].ToString().Trim(); string NormalBudget = row[6].ToString().Trim(); string AdjustBudget = row[7].ToString().Trim(); string Remark = row[9].ToString().Trim(); AuthorizationDS.OrganizationUnitDataTable ouTable = new OUTreeBLL().GetDataByOrganizationUnitName(DepartmentName); if (ouTable == null || ouTable.Count == 0) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���Ҳ����˲��š�" + DepartmentName + "��"; 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 (ouTable.Count > 1) { errorInfor = "��" + (i + 1) + "���д��ϵͳ���ҵ���������ƵIJ��š�" + DepartmentName + "��"; 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.ExpenseManageTypeDataTable emtTable = new MasterDataBLL().GetExpenseManageTypePaged(0, 20, string.Format("AccountingCode = '{0}'", AccountingCode), null); if (emtTable == null || emtTable.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 (emtTable.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("OrganizationUnitID = {0} and ExpenseManageTypeID= {1} and Period = '{2}'", ouTable[0].OrganizationUnitId, emtTable[0].ExpenseManageTypeID, DateTime.Parse(Period_Year + "-" + Period_Month + "-01")); if (new BudgetBLL().GetPagedBudgetManageFee(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.BudgetManageFeeRow BudgetManageRow = BudgetManageTable.NewBudgetManageFeeRow(); BudgetManageRow.OrganizationUnitID = ouTable[0].OrganizationUnitId; BudgetManageRow.Period = DateTime.Parse(Period_Year + "-" + Period_Month + "-01"); BudgetManageRow.ExpenseManageTypeID = emtTable[0].ExpenseManageTypeID; BudgetManageRow.OriginalBudget = decimal.Parse(OriginalBudget); BudgetManageRow.NormalBudget = decimal.Parse(NormalBudget); BudgetManageRow.AdjustBudget = decimal.Parse(AdjustBudget); BudgetManageRow.TotalBudget = decimal.Round(BudgetManageRow.NormalBudget + BudgetManageRow.AdjustBudget, 2); BudgetManageRow.ModifyReason = Remark; BudgetManageTable.AddBudgetManageFeeRow(BudgetManageRow); } } } } } TAImportLog.Update(logRow); TAImportLogDetail.Update(ImportLogDetailTable); TABudgetManageFee.Update(BudgetManageTable); 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(); } } }