📄 conbrowse.pas
字号:
end;
//////////////////////////////////////////////////////////////////////////////////////////
if Edit12.Text ='' then begin
ShowMessage(' 请填写审计前金额！！！');
Exit;
end;
///////////////////////////////////////////////////////////////////////////////////////////
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select * from detail where conNumber='''+Edit18.Text+''' and marking=1') ;
DM.ADOQuery7.Open;
//字段 marking = 2 表示审计*后*价款
//字段 marking = 1 表示审计审计*前*金额
if DM.ADOQuery7.RecordCount>0 then
begin
if MessageDlg('已经添加过审计*前*金额，还要继续添加吗？',mtConfirmation, [mbYes, mbNo], 0)= mrNo then exit;
aa:=10;
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select sum(daifang) as dai from detail where conNumber='''+Edit18.Text+''' and marking=1') ;
DM.ADOQuery7.Open;
//把审计前金额求和
dai_bf:=DM.ADOQuery7.FieldValues['dai'];
//把审计*前*金额求和
end;
end ;
////////////////////////////////////////////////////////////////////////////////////////////////////
if radiobutton2.Checked=true then begin //如果添加过审计后价款
if Edit13.Text ='' then begin
ShowMessage(' 请填写审计*后*价款！！！');
Exit;
end;
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select * from detail where conNumber='''+Edit18.Text+''' and marking=2') ;
DM.ADOQuery7.Open;
//字段 marking = 2 表示审计*后*价款
//字段 marking = 1 表示审计审计*前*金额
///////////////////////////////////////////////////
if DM.ADOQuery7.RecordCount>0 then
begin
if MessageDlg('已经添加过审计*后*金额，还要继续添加吗？',mtConfirmation, [mbYes, mbNo], 0)= mrNo then exit;
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select sum(daifang) as sjh from detail where conNumber='''+Edit18.Text+''' and marking=2') ;
DM.ADOQuery7.Open;
//把审计*后*价款求和
shenjihou:=DM.ADOQuery7.FieldValues['sjh'];
end;
////////////////////////////////////////////////////
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select * from detail where conNumber='''+Edit18.Text+''' and marking=1') ;
DM.ADOQuery7.Open;
if DM.ADOQuery7.RecordCount=0 then exit;//如果没有添加审计*前*金额不能添加审计*后*价款
end;
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
DM.ADOQuery7.SQL.Add('select * from detail') ;
DM.ADOQuery7.Open;
DM.ADOQuery7.Append;
DM.ADOQuery7.Edit;
if radiobutton1.Checked=true then
begin //选择审计前金额
if aa=0 then begin
DM.ADOQuery7.FieldValues['daifang']:=Edit12.Text;
DM.ADOQuery7.FieldValues['marking']:='1';
DM.ADOQuery7.FieldValues['remain']:=Edit12.Text;
end else begin
DM.ADOQuery7.FieldValues['daifang']:=Edit12.Text;
DM.ADOQuery7.FieldValues['marking']:='1';
DM.ADOQuery7.FieldValues['MarkingContent']:='审计前金额';
DM.StructADOQuery.Close;
DM.StructADOQuery.SQL.Clear;
DM.StructADOQuery.SQL.Add('select * from detail where conNumber='''+Edit18.Text+''' and marking=0 ') ;
DM.StructADOQuery.Open;
if DM.StructADOQuery.RecordCount = 0 then
//如果没有还款就添加审计后金额，余额等于审计后价款
DM.ADOQuery7.FieldValues['remain']:=floattostr(strtofloat(Edit12.Text)+strtofloat(dai_bf))
else begin
DM.StructADOQuery.Close;
DM.StructADOQuery.SQL.Clear;
DM.StructADOQuery.SQL.Add('select sum(jiefang) as jie from detail where conNumber='''+Edit18.Text+''' and marking=0 ') ;
DM.StructADOQuery.Open;
fu:=DM.StructADOQuery.FieldValues['jie'];
//DM.ADOQuery7.FieldValues['remain']:=floattostr(strtofloat(Edit12.Text)+strtofloat(dai_bf)-strtofloat(fu));
DM.ADOQuery7.FieldValues['remain']:=Format('%8.2f',[strtofloat(Edit12.Text)+strtofloat(dai_bf)-strtofloat(fu)]);
end;
end;
end else begin
//选择审计价款
DM.StructADOQuery.Close;
DM.StructADOQuery.SQL.Clear;
DM.StructADOQuery.SQL.Add('select * from detail where conNumber='''+Edit18.Text+''' and marking=0 ') ;
DM.StructADOQuery.Open;
if DM.StructADOQuery.RecordCount=0 then
//如果没有还款就添加审计金额，余额等于审计后价款
DM.ADOQuery7.FieldValues['remain']:=floattostr(strtofloat(Edit13.Text)+strtofloat(shenjihou))
else begin
DM.StructADOQuery.Close;
DM.StructADOQuery.SQL.Clear;
DM.StructADOQuery.SQL.Add('select sum(jiefang) as jie from detail where conNumber='''+Edit18.Text+''' and marking=0 ') ;
DM.StructADOQuery.Open;
fu:=DM.StructADOQuery.FieldValues['jie'];
//DM.ADOQuery7.FieldValues['remain']:=floattostr(strtofloat(Edit13.Text)+strtofloat(shenjihou)-strtofloat(fu));
DM.ADOQuery7.FieldValues['remain']:=Format('%8.2f',[strtofloat(Edit13.Text)+strtofloat(shenjihou)-strtofloat(fu)]);
end;
DM.ADOQuery7.FieldValues['daifang']:=Edit13.Text;
DM.ADOQuery7.FieldValues['marking']:='2';
DM.ADOQuery7.FieldValues['MarkingContent']:='审计后价款';
end;
DM.ADOQuery7.FieldValues['ConNumber']:=Edit18.Text;
DM.ADOQuery7.FieldValues['JorD']:='贷';
DM.ADOQuery7.FieldValues['remark']:=Edit15.Text;
DM.ADOQuery7.FieldValues['adddate']:=DateToStr(DateTimePicker3.Date);
DM.ADOQuery7.Post;
ShowMessage('资料添加成功！！！');
Edit12.Text:='';
Edit13.Text:='';
DM.detail.Close;
DM.detail.Open;
end;
procedure TConForm.RzBitBtn3Click(Sender: TObject);
var
BIDD :integer;
begin
if edit2.Text='' then exit;
if (DM.detail.RecNo = 1) and (DM.detail.RecordCount>1) then
begin // 如果有其它记录不能把审记前金额都删除
ShowMessage('目前还不能删除此审记*前*金额记录！');
exit;
end;
if DBgrid2.SelectedField.DataSet.FieldValues['ID']<>null then begin
BIDD:=DBgrid2.SelectedField.DataSet.FieldValues['ID'];
with DM do
begin
ADOQuery7.Close;
ADOQuery7.SQL.Clear;
ADOQuery7.SQL.Add('delete detail where ID='+IntToStr(BIDD));
if MessageDlg('确定要删除吗？',mtConfirmation, [mbYes, mbNo], 0)= mrYes then begin
ADOQuery7.ExecSQL;
ShowMessage('数据删除成功！！！');
detail.Close;
detail.Open;
label13.Caption:='共计 '+inttostr(detail.RecordCount)+' 条记录 ';
end
else
Exit;
end;
end;
RefreshFinance;//更新财务记录
end;
procedure TConForm.RzBitBtn2Click(Sender: TObject);
var
dd:integer;
begin
if MessageDlg('确定要修改吗？',mtConfirmation, [mbYes, mbNo], 0)= mrNo then Exit;
DD:=dbgrid2.SelectedField.DataSet.FieldValues['ID'];
if Edit14.Text ='' then begin
ShowMessage(' 请选择列表！！！');
Exit;
end;
if Edit19.Text ='' then begin
ShowMessage(' 请选择列表！！！');
Exit;
end;
DM.ADOQuery7.Close;
DM.ADOQuery7.SQL.Clear;
if radiobutton3.Checked=true then begin
if Edit16.Text ='' then begin
ShowMessage(' 请选择列表！！！');
Exit;
end;
DM.ADOQuery7.SQL.Add('update detail set daifang='''+Edit16.Text+
''',remark='''+Edit19.Text+''',adddate='''+DateToStr(DateTimePicker4.Date)+''' where id='+inttostr(DD)) ;
end else begin
if Edit17.Text ='' then begin
ShowMessage(' 请选择列表！！！');
Exit;
end;
DM.ADOQuery7.SQL.Add('update detail set daifang='''+Edit17.Text+
''',remark='''+Edit19.Text+''',adddate='''+DateToStr(DateTimePicker4.Date)+''' where id='+inttostr(DD)) ;
end;
DM.ADOQuery7.ExecSQL;
ShowMessage('修改成功！！！');
DM.detail.Close;
DM.detail.Open;
RefreshFinance;//更新财务记录
end;
procedure TConForm.Button5Click(Sender: TObject);//财务表
var
i,j,m,n,h,g,d1,d2,s1,s2:integer;
s,bh1,bh2,bh3,bh4,bh5:string;
Excelid,xl: Variant;
begin
if edit2.Text='' then exit;
try
Excelid:=CreateOleObject( 'Excel.Application' );
except
on Exception do raise exception.Create('无法创建Xls文件，请确认是否安装EXCEL')
end;
Excelid.Visible := True;
Excelid.WorkBooks.Add;
Excelid.Caption :='工程款支付明细帐';
Excelid.worksheets[1].range['A1:H1'].Merge(True);
Excelid.worksheets[1].range['A3:H3'].Merge(True);
//DBGrid1.SelectedField.DataSet.FieldValues['RID'];
Excelid.WorkSheets[1].Cells[1,1].Value :=FormatDateTime('yyyy',DBGrid1.SelectedField.DataSet.FieldValues['signtime'])+'年工程款支付明细帐' ;
Excelid.WorkSheets[1].Cells[3,1].Value :=stitle+' (合同编号：'+Edit2.Text+')' ;
Excelid.worksheets[1].range['A1:H1'].HorizontalAlignment := $FFFFEFF4;
Excelid.worksheets[1].range['A4:H20'].HorizontalAlignment := $FFFFEFF4;
Excelid.worksheets[1].range['A1:H1'].VerticalAlignment := $FFFFEFF4;
Excelid.worksheets[1].range['A4:H20'].VerticalAlignment := $FFFFEFF4;
Excelid.worksheets[1].Columns[1].ColumnWidth := 10;
Excelid.worksheets[1].Columns[2].ColumnWidth := 15;
Excelid.worksheets[1].Columns[3].ColumnWidth := 10;
Excelid.worksheets[1].Columns[4].ColumnWidth := 20;
Excelid.worksheets[1].Columns[5].ColumnWidth := 15;
Excelid.worksheets[1].Columns[6].ColumnWidth := 15;
Excelid.worksheets[1].Columns[7].ColumnWidth := 10;
Excelid.worksheets[1].Columns[8].ColumnWidth := 15;
//**************************************************************************************************
i:=4;
Excelid.WorkSheets[1].Cells[i,1].Value := '序 号';
Excelid.WorkSheets[1].Cells[i,2].Value := '日 期';
Excelid.WorkSheets[1].Cells[i,3].Value := '记帐凭'+char(10)+'证号数';
Excelid.WorkSheets[1].Cells[i,4].Value := '摘 要';
Excelid.WorkSheets[1].Cells[i,5].Value := '借 方';
Excelid.WorkSheets[1].Cells[i,6].Value := '贷 方';
Excelid.WorkSheets[1].Cells[i,7].Value := '借或贷';
Excelid.WorkSheets[1].Cells[i,8].Value := '余 额';
Excelid.worksheets[1].Range['A1:H7'].Font.Name := '宋体';
Excelid.worksheets[1].Range['A1:H1'].Font.Size := 14;
Excelid.worksheets[1].range['A1:H4'].font.bold:=true;
Excelid.worksheets[1].Range['A2:H4'].Font.Size := 10;
// Excelid.worksheets[1].Range['A2:M3'].EntireColumn.Autofit;
// Excelid.worksheets[1].Range['A2:M3'].EntireColumn.OutLine;
// Excelid.worksheets[1].StyleColumnWidth := cwEnhAutoFit;
//*****************************************************************************************
Excelid.ActiveSheet.Rows[1].RowHeight := 1/0.035;
//*****************************************************************************************
i:=5;
DM.detail.First;
while not DM.detail.Eof do
begin
Excelid.WorkSheets[1].Cells[i,2].Value := char(10); //项目名称
Excelid.WorkSheets[1].Cells[i,2].Value := DM.detail.FieldByName('AddDate').AsString;
Excelid.WorkSheets[1].Cells[i,3].Value := char(10); //单位名称
Excelid.WorkSheets[1].Cells[i,3].Value := DM.detail.FieldByName('jzpz').AsString;
Excelid.WorkSheets[1].Cells[i,4].Value := DM.detail.FieldByName('remark').AsString;
Excelid.WorkSheets[1].Cells[i,5].Value := DM.detail.FieldByName('jiefang').AsString;
Excelid.WorkSheets[1].Cells[i,6].Value := DM.detail.FieldByName('daifang').AsString;
Excelid.WorkSheets[1].Cells[i,7].Value := DM.detail.FieldByName('jord').AsString;
Excelid.WorkSheets[1].Cells[i,8].Value := DM.detail.FieldByName('remain').AsString;
Excelid.WorkSheets[1].Cells[i,1].Value := i-4;
i:=i+1;
DM.detail.Next;
s := 'A4:H'+ IntToStr(i-1);
Excelid.worksheets[1].Range[s].Borders.LineStyle := 1; //添加边框
end;
//Excelid.ActiveSheet.PageSetup.FitToPagesWide;
Excelid.ActiveSheet.PageSetup.Orientation:=2; //页面方向:横向
Excelid.ActiveSheet.PageSetup.PrintTitleRows:='$1:$4'; //表头为1-3行
// Excelid.worksheets[1].Range['A2:M3'].HasArray ;
// Excelid.ActiveSheet.PageSetup.PaperSize:=5; //纸张大小
end;
procedure TConForm.RadioButton2Click(Sender: TObject);
begin
if radiobutton2.Checked=true then begin
Edit12.Enabled:=false;
Edit13.Enabled:=true;
end else begin
Edit13.Enabled:=false;
Edit12.Enabled:=true;
end;
end;
procedure TConForm.RadioButton1Click(Sender: TObject);
begin
if radiobutton2.Checked=true then begin
Edit12.Enabled:=false;
Edit13.Enabled:=true;
end else begin
Edit13.Enabled:=false;
Edit12.Enabled:=true;
end;
end;
procedure TConForm.DBGrid1TitleClick(Column: TColumn);
begin
if NOT arrange then begin
DM.AdoQuery2.Sort:=Column.FieldName+' ASC';//递增排列 ' DESC'; //递减排列
arrange:=TRUE;
end else begin
DM.AdoQuery2.Sort:=Column.FieldName+' DESC';
Arrange:=FALSE;
end;
m_Sort:= DM.AdoQuery2.Sort;
end;
procedure TConForm.ToolButton5Click(Sender: TObject);
begin
ConForm.FormShow(Sender);
end;
procedure TConForm.Label9Click(Sender: TObject);
begin
with DM do
begin
ADOQuery3.Close;
ADOQuery3.SQL.Clear;
ADOQuery3.SQL.Add('select * from detail' );
ADOQuery3.Open;
while not ADOQuery3.Eof do
begin
ADOQuery3.Prepared:=true;
ADOQuery3.Edit;
if ADOQuery3.FieldValues['Marking']='0' then
ADOQuery3.FieldValues['MarkingContent']:='还款';
if ADOQuery3.FieldValues['Marking']='1' then
ADOQuery3.FieldValues['MarkingContent']:='审计前金额';
if ADOQuery3.FieldValues['Marking']='2' then
ADOQuery3.FieldValues['MarkingContent']:='审计后价款';
ADOQuery3.Post;
ADOQuery3.Next;
end;
end;
ShowMessage('成功');
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -