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 UpdateBudgetManageFee(int BudgetManageFeeID, 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.BudgetManageFeeRow row = this.TABudgetManage.GetDataByID(BudgetManageFeeID)[0];
if (row.TotalBudget > (NormalBudget + AdjustBudget.GetValueOrDefault())) //如果是减少预算,要做检查
{
decimal[] calculateAssistant = new decimal[4];
calculateAssistant = this.GetPersonalBudgetByOUID(row.OrganizationUnitID, row.Period);
if (row.TotalBudget - NormalBudget - AdjustBudget.GetValueOrDefault() > calculateAssistant[3])
{
throw new ApplicationException("本次修改导致原有记录超预算，不能修改");
}
}
row.NormalBudget = NormalBudget;
row.AdjustBudget = AdjustBudget.GetValueOrDefault();
row.ModifyReason = ModifyReason;
this.TABudgetManage.Update(row);
// 子表
BudgetDS.BudgetManageFeeHistoryDataTable tableDetail = new BudgetDS.BudgetManageFeeHistoryDataTable();
BudgetDS.BudgetManageFeeHistoryRow rowDetail = tableDetail.NewBudgetManageFeeHistoryRow();
rowDetail.OrganizationUnitID = row.OrganizationUnitID;
rowDetail.Period = row.Period;
rowDetail.ExpenseManageTypeID = row.ExpenseManageTypeID;
rowDetail.OriginalBudget = row.OriginalBudget;
rowDetail.NormalBudget = row.NormalBudget;
rowDetail.AdjustBudget = row.AdjustBudget;
rowDetail.Action = "Modify";
rowDetail.ModifyDate = DateTime.Now;
rowDetail.PositionID = PositionID;
rowDetail.UserID = UserID;
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 DeleteBudgetManageFeeByID(int BudgetManageFeeID)
{
SqlTransaction transaction = null;
BudgetDS.BudgetManageFeeRow provRow = TABudgetManage.GetDataByID(BudgetManageFeeID)[0];
try {
BudgetDS.BudgetManageFeeRow row = this.TABudgetManage.GetDataByID(BudgetManageFeeID)[0];
decimal[] calculateAssistant = new decimal[4];
calculateAssistant = this.GetPersonalBudgetByOUID(row.OrganizationUnitID, row.Period);
if (row.TotalBudget > calculateAssistant[3])
{
throw new ApplicationException("本次修改导致原有记录超预算，不能删除");
}
transaction = TableAdapterHelper.BeginTransaction(TABudgetManage);
TableAdapterHelper.SetTransaction(TABudgetManageHistory, transaction);
TABudgetManageHistory.DeleteByBudgetManageFeeId(BudgetManageFeeID);
TABudgetManage.Delete(BudgetManageFeeID);
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(BudgetManageFeeID);
}
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();
}
}
}
