var sqlstr,xsd,xst,xsh,jhd,jht,jhh:string;
dwbh,dwqm,dwdh,jsrbh,jsr,dq,bm,ck,rq,djlx,djbh,spbh,spqm,spdw,kmbh,kmqm,dzbj,bz:string;
spsl,hsj,yszj,ysjs,kpje,wkpje,yhje,fkje,b,s,sum:integer;
sqlDataSet:TSQLDataSet;
begin
DataSetBZSJJ1.Active := false;
xsd:='Select (select DWXX_DWBH from DWXX where DWXX_DWQM=XSDDGMDW) 单位编号,XSDDGMDW 单位名称,(select DWXX_DH from DWXX where DWXX_DWQM=XSDDGMDW) 电话,(Select ZYXX_ZYBH from ZYXX where ZYXX_ZYQM=XSDDJSR) 职员编号,XSDDJSR as 经手人,(select DWXX_DQQM from DWXX where DWXX_DWQM=XSDDGMDW) 地区,XSDDBM as 部门,XSDDFHCK as 仓库,XSDDJHRQ as 日期,'
+'''销售单'' 单据类型,XSDDDJBH as 编号,a.商品编号 as 商品编号,a.商品全名 as 商品全名,a.商品单位 as 商品单位,'
+'a.商品数量 as 商品数量,a.商品金额 含税价,(Select KMXX_KMBH from KMXX where KMXX_KMQM=XSDDFKZH) 科目编号,XSDDFKZH 科目全名,case when isnull(XSDDYHHJE,0)-isnull(XSDDFKJE,0)>0 then isnull(XSDDYHHJE,0)-isnull(XSDDFKJE,0) else 0 end 应收增加,'
+'case when isnull(XSDDYHHJE,0)-isnull(XSDDFKJE,0)<0 then isnull(XSDDYHHJE,0)-isnull(XSDDFKJE,0) else 0 end as 应收减少,a.开票金额 as 开票金额,a.商品金额-a.开票金额 as 未开票金额,a.备注 备注,isnull(XSDDFKJE,0) 付款金额,isnull(XSDDYHJE,0) 优惠金额 '
+'from XSDDB left join (Select XSDDMXDJBH,XSDDMXSPBH 商品编号,XSDDMXSPQM 商品全名,XSDDMXDW 商品单位,XSDDMXSL 商品数量,XSDDMXJE 商品金额,XSDDMXKPJE 开票金额,XSDDMXBZ 备注 from XSDDMXB) a on(a.XSDDMXDJBH=XSDDDJBH)';
sqlstr:=xsd;
DataSetBroker1.DataSet.Open;
sqlDataSet:= TSQLDataSet.Create(Self);
sqlDataSet.Database.URL := 'Biz:JXC_GJP_SJK.Database';
with sqlDataSet do
begin
sql.Text:=sqlstr;
open;
while not eof do
begin
dwbh:=FieldByName('单位编号').AsString.Trim();
dwqm:=FieldByName('单位名称').AsString.Trim();
dwdh:=FieldByName('电话').AsString.Trim();
jsrbh:=FieldByName('职员编号').AsString.Trim();
jsr:=FieldByName('经手人').AsString.Trim();
dq:=FieldByName('地区').AsString.Trim();
bm:=FieldByName('部门').AsString.Trim();
ck:=FieldByName('仓库').AsString.Trim();
rq:=FieldByName('日期').AsString.Trim();
djlx:=FieldByName('单据类型').AsString.Trim();
djbh:=FieldByName('编号').AsString.Trim();
spbh:=FieldByName('商品编号').AsString.Trim();
spqm:=FieldByName('商品全名').AsString.Trim();
spdw:=FieldByName('商品单位').AsString.Trim();
spsl:=FieldByName('商品数量').AsInteger;
hsj:=FieldByName('含税价').AsInteger;
kmbh:=FieldByName('科目编号').AsString.Trim();
kmqm:=FieldByName('科目全名').AsString.Trim();
yszj:=FieldByName('应收增加').AsInteger;
ysjs:=FieldByName('应收减少').AsInteger;
kpje:=FieldByName('开票金额').AsInteger;
wkpje:=FieldByName('未开票金额').AsInteger;
bz:=FieldByName('备注').AsString;
fkje:=FieldByName('付款金额').AsInteger;
yhje:=FieldByName('优惠金额').AsInteger;
if DataSetBroker1.DataSet.Locate('BH',Variants.VarArrayOf([djbh]),[])=false then
begin
Query1.Close;
Query1.ConnectionString:='DATABASEURL=Biz:JXC_GJP_SJK.Database';
Query1.CommandText:='insert into WLDZCXB(BH,DWBH,DWMC,DWDH,JSRBH,JSR,DQ,BM,CK,RQ,DJLX,SPBH,SPMC,SPDW,SPSL,HSJ,KMBH,KMQM,YSZJ,YSJS,KPJE,WKPJE,BZ)'
+' values('''+djbh+''','''+ dwbh +''','''+ dwqm +''','''+dwdh+''','''+jsrbh+''','''+jsr+''','''+dq+''','''+bm+''','''+ck+''','''+rq+''','''+djlx+''','''+spbh+''','''+spqm+''','''+spdw+''','+SysUtils.IntToStr(spsl)+','+SysUtils.IntToStr(hsj)+',''011'',''库存商品'','+SysUtils.IntToStr(yszj)+','+SysUtils.IntToStr(ysjs)+','+SysUtils.IntToStr(kpje)+','+SysUtils.IntToStr(wkpje)+','''+bz+''')';
Query1.Execute;
end;
if fkje>0 then
begin
if DataSetBroker1.DataSet.Locate('BH',Variants.VarArrayOf([djbh]),[])=false then
begin
if yszj>0 then
begin
Query1.Close;
Query1.ConnectionString:='DATABASEURL=Biz:JXC_GJP_SJK.Database';
Query1.CommandText:='insert into WLDZCXB(BH,DWBH,DWMC,DWDH,JSRBH,JSR,DQ,BM,CK,RQ,DJLX,KMBH,KMQM,YSZJ,YSJS,BZ)'
+' values('''+djbh+''','''+ dwbh +''','''+ dwqm +''','''+dwdh+''','''+jsrbh+''','''+jsr+''','''+dq+''','''+bm+''','''+ck+''','''+rq+''','''+djlx+''','''+kmbh+''','''+kmqm+''',0,'+SysUtils.IntToStr(fkje)+','''+bz+''')';
Query1.Execute;
end;
if ysjs>0 then
begin
Query1.Close;
Query1.ConnectionString:='DATABASEURL=Biz:JXC_GJP_SJK.Database';
Query1.CommandText:='insert into WLDZCXB(BH,DWBH,DWMC,DWDH,JSRBH,JSR,DQ,BM,CK,RQ,DJLX,KMBH,KMQM,YSZJ,YSJS,BZ)'
+' values('''+djbh+''','''+ dwbh +''','''+ dwqm +''','''+dwdh+''','''+jsrbh+''','''+jsr+''','''+dq+''','''+bm+''','''+ck+''','''+rq+''','''+djlx+''','''+kmbh+''','''+kmqm+''','+SysUtils.IntToStr(fkje)+',0,'''+bz+''')';
Query1.Execute;
end;
end;
end;
Next;
end;
close;
end;
|
发表于 2007-10-29 16:22:51
楼主