using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace FileArchive.Debit
{
public partial class DebitForm : PettyCashBasic.FormBase
{
public DebitForm()
{
InitializeComponent();
dgvGrid1.bSubToal = true;
dateTimeInput2.Value = DateTime.Now;
dateTimeInput1.Value = new DateTime(DateTime.Now.Year, 1, 1);
cboYear.Text = DateTime.Now.Year.ToString();
// 允许用户删除行,包括右键行删除和Delete键
dgvGrid1.AllowUserToDeleteRows = true;
//this.btnSearch.Visible = IsSearch;
//this.btnSave.Visible = IsSave;
//this.btnDownload.Visible = IsDownLoad;
//this.btnExport.Visible = IsUpLoad;
GetGridData();
}
private DataSet gridSet = new DataSet();
public DataSet GridSet
{
get { return gridSet; }
set { gridSet = value; }
}
private void GetGridData()
{
try
{
System.Collections.Hashtable Para = new System.Collections.Hashtable();
Para.Add("@user_name", PettyCashBasic.Classes.PettyCashLogin.UserName);
Para.Add("@bill_date01", dateTimeInput1.Value.ToString("yyyy-MM-dd"));
Para.Add("@bill_date02", dateTimeInput2.Value.ToString("yyyy-MM-dd"));
Para.Add("@year", cboYear.Text);
Para.Add("@currency", cboCurrency.Text);
Para.Add("@cus_name", cboCustomer.Text);
Para.Add("@ins_user", cboInsertUser.Text);
GridSet = PettyCashBasic.Classes.PettyCashConnection.GetDataset("p_debit_info_sel", Para);
dgvGrid1.GetGridTable(GridSet.Tables[0]);
}
catch (Exception ex)
{ }
}
private void btnSearch_Click(object sender, EventArgs e)
{
GetGridData();
}
private void btnDownload_Click(object sender, EventArgs e)
{
DClasses.ExcelIO.ExportExcelEPPlus(GridSet, "Debit目录" + DateTime.Now.ToString(" yyyy-MM-dd HHmmss"), true);
}
private void btnExport_Click(object sender, EventArgs e)
{
try
{
System.Collections.Hashtable Para = new System.Collections.Hashtable();
Para.Add("@user_name", PettyCashBasic.Classes.PettyCashLogin.UserName);
Para.Add("@tb1", PettyCashBasic.Classes.PettyCashConnection.GetTable101(dgvGrid1.GetSelectRows()));
DataSet ds = PettyCashBasic.Classes.PettyCashConnection.GetDataset("p_debit_info_sel_download", Para);
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
DataTable exportList = new DataTable();
exportList.Columns.Add("文件名");
exportList.Columns.Add("导出状态");
string[] fileFields = new string[] { "电子附件", "电子附件01", "电子附件02", "电子附件03"};
string[] exportFields = new string[] { "导出标识", "导出标识01", "导出标识02", "导出标识03" };
foreach (DataRow dr in ds.Tables[0].Rows)
{
for (int i = 0;i < fileFields.Length; i++)
{
string fileName = dr[fileFields[i]].ToString();
if (!string.IsNullOrEmpty(fileName))
{
string filePath = string.Format(@"{0}\{1}", fbd.SelectedPath, fileName);
string sysPath = dr[exportFields[i]].ToString();
dr[exportFields[i]] = "N-未导出";
if (!string.IsNullOrEmpty(sysPath))
{
if (PettyCashBasic.Classes.RelFiles.FileCopy(
sysPath,
filePath,
true
))
{
dr[exportFields[i]] = "Y-已导出";
DataRow newRow = exportList.NewRow();
newRow["文件名"] = fileName;
newRow["导出状态"] = "成功导出";
exportList.Rows.Add(newRow);
}
}
}
}
}
DataSet exportDs = new DataSet();
exportDs.Tables.Add(exportList);
DClasses.ExcelIO.ExportExcelEPPlus(exportDs, string.Format(@"{0}\下载清单 {1}.xlsx", fbd.SelectedPath, DateTime.Now.ToString("yyMMdd HHmmss")), "", true);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
}
private void dgvGrid1_MouseUp(object sender, MouseEventArgs e)
{
labTotal.Text = ((DClasses.DgvGrid)sender).Totals;
}
private void dgvGrid1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
try
{
string headerText = dgvGrid1.Columns[e.ColumnIndex].HeaderText.ToString();
if (!headerText.StartsWith("电子附件"))
{
return;
}
Guid guid_file = Guid.NewGuid();
Guid guid_id = Guid.Parse(dgvGrid1.Rows[e.RowIndex].Cells["sys_guid"].Value.ToString());
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "上传文件(All)|*.*;";
ofd.Multiselect = false;
string sys_path = @"\\172.29.240.11\04_trade$\DEBIT";
string file_type = "DebitFiles";
string invc_no = Guid.NewGuid().ToString(); // dgvGrid1.Rows[e.RowIndex].Cells["文件号"].Value.ToString();
if (ofd.ShowDialog() == DialogResult.OK)
{
System.Collections.Hashtable Para = new System.Collections.Hashtable();
foreach (string filename in ofd.FileNames)
{
Para.Clear();
guid_id = Guid.NewGuid();
Para.Add("@guid_id", guid_id);
Para.Add("@guid_file", guid_file);
Para.Add("@file_name", System.IO.Path.GetFileNameWithoutExtension(filename));
Para.Add("@file_spec", System.IO.Path.GetExtension(filename));
Para.Add("@file_path", sys_path);
Para.Add("@file_type", file_type);
Para.Add("@file_rel_value", invc_no);
Para.Add("@user_name", PettyCashBasic.Classes.PettyCashLogin.UserName);
Para["@rel_guid"] = Guid.Parse(dgvGrid1.Rows[e.RowIndex].Cells["sys_guid"].Value.ToString());
int fileIndex = 0;
if (headerText.Contains("01")) fileIndex = 1;
else if (headerText.Contains("02")) fileIndex = 2;
else if (headerText.Contains("03")) fileIndex = 3;
Para.Add("@file_index", fileIndex);
string flag_save = "save";
if (PettyCashBasic.Classes.RelFiles.FileCopy(filename, string.Format(@"{0}\{1}", sys_path, guid_id.ToString()), true) && flag_save != "")
//if (PettyCashBasic.Classes.RelFiles.FileCopy(filename, string.Format(@"{0}\{1}", sys_path, guid_id.ToString()), true))
{
// 数据功能更新
if (!PettyCashBasic.Classes.PettyCashConnection.DataUpdate("p_debit_info_upload_guid", Para))
{
System.Windows.Forms.MessageBox.Show("文件上传失败.请重新上传");
return;
}
}
switch (fileIndex)
{
case 0:
dgvGrid1.Rows[e.RowIndex].Cells["sys_guid_file"].Value = guid_id;
dgvGrid1.Rows[e.RowIndex].Cells["电子附件"].Value = System.IO.Path.GetFileName(filename);
break;
case 1:
dgvGrid1.Rows[e.RowIndex].Cells["sys_guid_file01"].Value = guid_id;
dgvGrid1.Rows[e.RowIndex].Cells["电子附件01"].Value = System.IO.Path.GetFileName(filename);
break;
case 2:
dgvGrid1.Rows[e.RowIndex].Cells["sys_guid_file02"].Value = guid_id;
dgvGrid1.Rows[e.RowIndex].Cells["电子附件02"].Value = System.IO.Path.GetFileName(filename);
break;
case 3:
dgvGrid1.Rows[e.RowIndex].Cells["sys_guid_file03"].Value = guid_id;
dgvGrid1.Rows[e.RowIndex].Cells["电子附件03"].Value = System.IO.Path.GetFileName(filename);
break;
}
MessageBox.Show("文件上传成功!");
//dgvGrid1.Rows[e.RowIndex].Cells["文件名"].Value = System.IO.Path.GetFileNameWithoutExtension(filename);
if (!PettyCashBasic.Classes.PettyCashConnection.DataUpdate("p_debit_info_upload_guid", Para))
{
string cellValue = System.IO.Path.GetFileNameWithoutExtension(filename) + System.IO.Path.GetExtension(filename);
dgvGrid1.Rows[e.RowIndex].Cells[headerText].Value = cellValue;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show($"上传失败:{ex.Message}");
}
}
private void SaveData()
{
try
{
System.Collections.Hashtable Para = new System.Collections.Hashtable();
DataTable tb1 = (dgvGrid1.DataSource as DataTable) ?? new DataTable();
// 获取已删除的行(状态为Deleted)
DataTable deletedRows = tb1.Clone();
DataTable updatedRows = tb1.Clone();
foreach (DataColumn col in updatedRows.Columns)
{
if (col.DataType == typeof(Guid))
{
col.AllowDBNull = true;
}
}
foreach (DataRow row in tb1.Rows)
{
// 已删除的行用原始值(因为删除后当前值可能为DBNull)
if (row.RowState == DataRowState.Deleted)
{
DataRow newRow = deletedRows.NewRow();
foreach (DataColumn col in tb1.Columns)
{
object value = row[col.ColumnName, DataRowVersion.Original];
newRow[col.ColumnName] = value;
}
deletedRows.Rows.Add(newRow);
}
else
{
// 新增或修改的行用当前值
updatedRows.ImportRow(row);
}
}
DataTable changes = tb1.GetChanges();
if (changes != null || deletedRows.Rows.Count > 0)
{
Para.Add("@user_name", PettyCashBasic.Classes.PettyCashLogin.UserName);
Para.Add("@tb1", PettyCashBasic.Classes.PettyCashConnection.GetTable101(updatedRows));
Para.Add("@tbDelete", PettyCashBasic.Classes.PettyCashConnection.GetTable101(deletedRows));
if (PettyCashBasic.Classes.PettyCashConnection.DataUpdate("p_debit_info_sav", Para))
{
tb1.AcceptChanges();
GetGridData();
MessageBox.Show("保存成功。");
}
}
else
{
MessageBox.Show("没有需要保存的变更。");
}
}
catch (Exception ex)
{
MessageBox.Show("保存失败:" + ex.Message);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveData();
}
}
}
USE [pettycash]
GO
/ Object: StoredProcedure [dbo].[p_debit_info_sav] Script Date: 2025/8/27 9:49:44 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_debit_info_sav]
(
@user_name nvarchar(50) = '' ,
@tb1 diytbl readonly ,
@tbDelete diytbl readonly
)
AS
BEGIN
SET NOCOUNT ON;
-- 临时表 --
select
ISNULL(f0, '') guid_id , -- sys_guid --
ISNULL(f1, '') file_guid , -- sys_file_guid --
ISNULL(f2, '') file_01_guid ,
ISNULL(f3, '') file_02_guid ,
ISNULL(f4, '') file_03_guid ,
ISNULL(f5, '') [year] ,
--case
-- when ISNULL(f5, '') = ''then convert(nvarchar(50), year(getdate()))
-- else ISNULL(f5, '')
--end as [year] ,
ISNULL(f6, '') cus_name ,
ISNULL(f7, '') [no] ,
ISNULL(f8, '') currency ,
ISNULL(f9, '') amt ,
ISNULL(f10, '') rem ,
ISNULL(f11, 0) sam_fee ,
ISNULL(f12, 0) cus_dec_amt ,
ISNULL(f13, '') cus_dec_time ,
ISNULL(f14, '') [file] ,
ISNULL(f15, '') file_01 ,
ISNULL(f16, '') file_02 ,
ISNULL(f17, '') file_03 ,
ISNULL(f18, '') amt_status ,
--ISNULL(f19, '') u8_status ,
case
when ISNULL(f19, '') = ''then convert(nvarchar(50), getdate(),112)
else ISNULL(f19, '')
end as u8_status ,
ISNULL(f20, '') charger ,
--ISNULL(f21, '') end_date ,
case
when ISNULL(f21, '') = ''then convert(nvarchar(50), getdate(),112)
else ISNULL(f21, '')
end as end_date ,
ISNULL(f22, '') rem_01 ,
ISNULL(f23, '') rem_02 ,
GETDATE() ins_time ,
@user_name ins_user ,
GETDATE() upd_time ,
@user_name upd_user
into #TT
from @tb1
-- 处理删除逻辑 --
if exists (select 1 from @tbDelete )
begin
insert into debit_delete
(
src_guid_id ,rel_guid ,[year] ,cus_name ,[no] ,
currency ,amt ,rem ,sam_fee ,cus_dec_amt ,
cus_dec_time ,file_guid ,file_01_guid ,file_02_guid ,file_03_guid ,
[file] ,file_01 ,file_02 ,file_03 ,amt_status ,
u8_status ,charger ,end_date ,rem_01 ,rem_02 ,
version_id ,ins_time ,ins_user ,upd_time ,upd_user ,
del_time ,
del_user
)
select
di.guid_id ,di.rel_guid ,di.[year] ,di.cus_name ,di.[no] ,
di.currency ,di.amt ,di.rem ,di.sam_fee ,di.cus_dec_amt ,
di.cus_dec_time ,di.file_guid ,di.file_01_guid ,di.file_02_guid ,di.file_03_guid ,
di.[file] ,di.file_01 ,di.file_02 ,di.file_03 ,di.amt_status ,
di.u8_status ,di.charger ,di.end_date , di.rem_01 ,di.rem_02 ,
di.version_id ,di.ins_time ,di.ins_user ,di.upd_time ,di.upd_user ,
getdate() ,
@user_name
from debit_info di
inner join @tbDelete tbd on di.guid_id = tbd.f0
where tbd.f0 is not null
delete d
from debit_info d
inner join @tbDelete tbd on d.guid_id = tbd.f0
where tbd.f0 is not null
end
-- 附件文件名自动填充 --
update t
set [file] = f.file_name + f.file_spec
from slctrade.exp.files f
inner join #TT t on f.guid_id = t.file_guid
where ISNULL(t.[file], '') = '' and t.file_guid is not null and t.file_guid <> '';
update t
set file_01 = f.file_name + f.file_spec
from slctrade.exp.files f
inner join #TT t on f.guid_id = t.file_01_guid
where ISNULL(t.file_01, '') = '' and t.file_01_guid is not null and t.file_01_guid <> '';
update t
set file_02 = f.file_name + f.file_spec
from slctrade.exp.files f
inner join #TT t on f.guid_id = t.file_02_guid
where ISNULL(t.file_02, '') = '' and t.file_02_guid is not null and t.file_02_guid <> '';
update t
set file_03 = f.file_name + f.file_spec
from slctrade.exp.files f
inner join #TT t on f.guid_id = t.file_03_guid
where ISNULL(t.file_03, '') = '' and t.file_03_guid is not null and t.file_03_guid <> '';
-- 关键字段验证 --
if exists
(
select 1
from #TT
where [year] = ''
or cus_name = ''
or [no] = ''
or currency = ''
or amt = ''
or rem = ''
or [file] = ''
--or u8_status = ''
)
begin
raiserror('关键字段不能为空', 16, 1);
return
end
-- 重复数据验证 --
if exists (
select 1
from #TT T
where T.guid_id = '' -- 新增重复验证 --
and exists (
select 1
from debit_info d
where d.[year] = T.[year]
and d.cus_name = T.cus_name
and d.[no] = T.[no]
and d.currency = T.currency
and d.amt = T.amt
and d.rem = T.rem
and ISNULL(d.sam_fee, 0) = ISNULL(T.sam_fee, 0)
and ISNULL(d.cus_dec_amt, 0) = ISNULL(T.cus_dec_amt, 0)
and ISNULL(d.cus_dec_time, '') = ISNULL(T.cus_dec_time, '')
and d.[file] = T.[file]
and d.file_01 = T.file_01
and d.file_02 = T.file_02
and d.file_03 = T.file_03
and d.amt_status = T.amt_status
and d.u8_status = T.u8_status
and d.charger = T.charger
and d.end_date = T.end_date
and d.rem_01 = T.rem_01
and d.rem_02 = T.rem_02
)
)
begin
raiserror('存在重复的数据', 16, 1)
return;
end
if exists (
select 1
from #TT T
inner join debit_info di on di.guid_id = T.guid_id
where T.guid_id <> '' -- 修改重复验证 --
and exists (
select 1
from debit_info d
where d.[year] = T.[year]
and d.cus_name = T.cus_name
and d.[no] = T.[no]
and d.currency = T.currency
and d.amt = T.amt
and d.rem = T.rem
and ISNULL(d.sam_fee, 0) = ISNULL(T.sam_fee, 0)
and ISNULL(d.cus_dec_amt, 0) = ISNULL(T.cus_dec_amt, 0)
and ISNULL(d.cus_dec_time, '') = ISNULL(T.cus_dec_time, '')
and d.[file] = T.[file]
and d.file_01 = T.file_01
and d.file_02 = T.file_02
and d.file_03 = T.file_03
and d.amt_status = T.amt_status
and d.u8_status = T.u8_status
and d.charger = T.charger
and d.end_date = T.end_date
and d.rem_01 = T.rem_01
and d.rem_02 = T.rem_02
and d.guid_id <> T.guid_id
and (
di.[year] <> T.[year] or
di.cus_name <> T.cus_name or
di.[no] <> T.[no] or
di.currency <> T.currency or
di.amt <> T.amt or
di.rem <> T.rem or
ISNULL(di.sam_fee, 0) <> ISNULL(T.sam_fee, 0) or
ISNULL(di.cus_dec_amt, 0) <> ISNULL(T.cus_dec_amt, 0) or
ISNULL(di.cus_dec_time, '') <> ISNULL(T.cus_dec_time, '') or
di.[file] <> T.[file] or
di.file_01 <> T.file_01 or
di.file_02 <> T.file_02 or
di.file_03 <> T.file_03 or
di.amt_status <> T.amt_status or
di.u8_status <> T.u8_status or
di.charger <> T.charger or
di.end_date <> T.end_date or
di.rem_01 <> T.rem_01 or
di.rem_02 <> T.rem_02
)
)
)
begin
raiserror('存在重复的数据', 16, 1)
return;
end
-- 插入新纪录 --
insert into debit_info
(
[year] ,
cus_name ,
[no] ,
currency ,
amt ,
rem ,
sam_fee ,
cus_dec_amt ,
cus_dec_time ,
[file] ,
file_01 ,
file_02 ,
file_03 ,
amt_status ,
u8_status ,
charger ,
end_date ,
rem_01 ,
rem_02 ,
ins_time ,
ins_user ,
upd_time ,
upd_user
)
select
T.[year] ,
T.cus_name ,
T.[no] ,
T.currency ,
T.amt ,
T.rem ,
T.sam_fee ,
T.cus_dec_amt ,
T.cus_dec_time ,
T.[file] ,
T.file_01 ,
T.file_02 ,
T.file_03 ,
T.amt_status ,
T.u8_status ,
T.charger ,
T.end_date ,
T.rem_01 ,
T.rem_02 ,
T.ins_time ,
T.ins_user ,
T.upd_time ,
T.upd_user
from #TT T
left join debit_info d on d.guid_id = T.guid_id
where d.guid_id is null
-- 更新记录 --
update d
set
[year] = T.[year] ,
cus_name = T.cus_name ,
[no] = T.[no] ,
currency = T.currency ,
amt = T.amt ,
rem = T.rem ,
sam_fee = T.sam_fee ,
cus_dec_amt = T.cus_dec_amt ,
cus_dec_time = T.cus_dec_time ,
[file] = T.[file] ,
file_01 = T.file_01 ,
file_02 = T.file_02 ,
file_03 = T.file_03 ,
amt_status = T.amt_status ,
u8_status = T.u8_status ,
charger = T.charger ,
end_date = T.end_date ,
rem_01 = T.rem_01 ,
rem_02 = T.rem_02 ,
upd_time = T.upd_time ,
upd_user = T.upd_user
from #TT T
inner join debit_info d on d.guid_id = T.guid_id
where
d.[year] <> T.[year] or
d.cus_name <> T.cus_name or
d.[no] <> T.[no] or
d.currency <> T.currency or
d.amt <> T.amt or
d.rem <> T.rem or
ISNULL(d.sam_fee, 0) <> ISNULL(T.sam_fee, 0) or
ISNULL(d.cus_dec_amt, 0) <> ISNULL(T.cus_dec_amt, 0) or
ISNULL(d.cus_dec_time, '') <> ISNULL(T.cus_dec_time, '') or
d.[file] <> T.[file] or
d.file_01 <> T.file_01 or
d.file_02 <> T.file_02 or
d.file_03 <> T.file_03 or
d.amt_status <> T.amt_status or
d.u8_status <> T.u8_status or
d.charger <> T.charger or
d.end_date <> T.end_date or
d.rem_01 <> T.rem_01 or
d.rem_02 <> T.rem_02
drop table #TT
END
USE [pettycash]
GO
/ Object: StoredProcedure [dbo].[p_debit_info_upload_guid] Script Date: 2025/8/27 10:45:50 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_debit_info_upload_guid]
(
@file_path nvarchar(200) = '' ,
@file_type nvarchar(50) = '' ,
@file_rel_value nvarchar(100) = '' ,
@guid_file uniqueidentifier = null ,
@guid_id uniqueidentifier = null ,
@rel_guid uniqueidentifier = null ,
@file_name nvarchar(100) = '' ,
@file_spec nvarchar(100) = '' ,
@user_name nvarchar(50) = '' ,
@file_index int = 0 -- 附件索引参数(0:主附件,1:附件1,2:附件2,3:附件3)
)
AS
BEGIN
SET NOCOUNT ON;
if @file_name is not null and @file_name <> ''
begin
IF @file_index = 0
begin
update debit_info
set file_guid = @guid_id ,
[file] = case when [file] = '' then @file_name else [file] end
where guid_id = @rel_guid
end
else if @file_index = 1
begin
update debit_info
set file_01_guid = @guid_id ,
file_01 = case when file_01 = '' then @file_name else file_01 end
where guid_id = @rel_guid
end
else if @file_index = 2
begin
update debit_info
set file_02_guid = @guid_id ,
file_02 = case when file_02 = '' then @file_name else file_02 end
where guid_id = @rel_guid
end
else if @file_index = 3
begin
update debit_info
set file_03_guid = @guid_id ,
file_03 = case when file_03 = '' then @file_name else file_03 end
where guid_id = @rel_guid
end
IF @guid_file is null
begin
insert into slctrade.exp.files
(
guid_id ,file_path ,file_name ,file_spec ,
file_type ,file_rel_value ,ins_user ,ins_time
)
values
(
@guid_id ,@file_path ,@file_name ,@file_spec ,
@file_type ,@file_rel_value ,@user_name ,getdate()
)
end
else
begin
update slctrade.exp.files
set file_path = @file_path ,
file_name = @file_name ,
file_spec = @file_spec ,
file_type = @file_type ,
file_rel_value = @file_rel_value ,
mod_user = @user_name ,
mod_time = getdate()
where guid_id = @guid_file
end
end
END
插入记录且电子附件上传成功点击保存后,数据库表中电子附件列有内容,但页面刷新后的电子附件名称无数据
最新发布