📄 inv_monthreport_c.pas
字号:
tableinfields:=tableinfields+'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' float null,';
if sqlinsum='' then
begin
//入方求和字段在SQL语名中的组合
sqlinsum:=' sum(isnull(R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
//入方求余总和
sqlinremant:='R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
//入方字段头
sqlinfields:='R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
' when R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end '
end
else
begin
sqlinsum:=sqlinsum+','+' sum(isnull(R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqlinremant:=sqlinremant+'+R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqlinfields:=sqlinfields+',R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
' when R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
'R'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
end;
//把入方的数据根据入方单据类型按物料汇总，插入到临时表 对委外入库，半成品入库，成品入库要排除仓库未审核的单据
//其他入库中，OPBill=1的属于委外入库
if (fieldbyname('BillTypeCode').asstring='0104') or
(fieldbyname('BillTypeCode').asstring='0105')
then
tmp_SqlBillTypeCode:=' and i.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''''+
' and i.InvBillwhchck<>0 '
else if (fieldbyname('BillTypeCode').asstring='0103') then
tmp_SqlBillTypeCode:=' and (i.BillTypeCode='+quotedstr(fieldbyname('BillTypeCode').asstring)+
' or (i.BillTypeCode=''0199'' and opBill=1)) '
else if (fieldbyname('BillTypeCode').asstring='0199') then
tmp_SqlBillTypeCode:=' and i.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''''+
' and opBill=0'
else
tmp_SqlBillTypeCode:=' and i.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+'''';
sql_SumInvInBillqty:=sql_SumInvInBillqty+
' insert #tmp1(ClassCode,'+
'R'+fieldbyname('BillTypeCode').asstring+')'+
' select #wtmp0.pClassCode,'+
' sum(isnull(il.'+Qry_Sumfields+',0)) as InvInBillqty '+
' from InvInBill i,InvInBillline il,Item,#wtmp0 '+
' where '+whCodecondition+
' i.InvBillMonth='''+medt_Date.text+''''+
tmp_SqlBillTypeCode+
' and i.InvBillid=il.InvBillid and il.ItemCode=Item.ItemCode'+
' and Item.ClassCode=#wtmp0.ClassCode'+
' group by #wtmp0.pClassCode ';
//结帐月份到查询起始日期的数量 入方
next;
end;
//出库
Close;
sql.clear;
sql.Add(' select BillTypeCode '+
' from BillType '+
' where io=1 '+
' and BillTypeCode not in (select BillTypeCode '+
' from BillType '+
' where BillTypeCode like ''1%'') '+
'Order by BillTypeCode');
open;
First;
if cmbbx_WhCode.text='全部仓库' then
begin
whCodecondition:='';
end
else
whCodecondition:=' o.whCode='''+getCode(cmbbx_WhCode.text)+''' and ';
while not eof do
begin
//产生出方的临时表字段
tableoutfields:= tableoutfields+'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' float null,';
if sqloutsum='' then
begin
sqloutsum:=' sum(isnull(C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqloutremant:='C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqloutfields:='C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
' when C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
end
else
begin
sqloutsum:=sqloutsum+','+' sum(isnull(C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+',0)) as C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqloutremant:=sqloutremant+'+C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring));
sqloutfields:=sqloutfields+',C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=case '+
' when C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+'=0 then null else '+
'C'+Trimleft(Trimright(fieldbyname('BillTypeCode').asstring))+' end ';
end;
//按单据类型出库按物料汇总，插入到临时表中 对生产领料单，材料出库单，产品销售出库单，其它出库单要排除仓库未审核的单据
//其他出库中OPBill=1的属于委外领料
If (fieldbyname('BillTypeCode').asstring='0201') then
tmp_SqlBillTypeCode:=' and ((o.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''' '+
' and o.InvBillwhchck<>0) or (o.BillTypeCode=''0299'' '+
' and o.AmountAdjust=1))'
else if (fieldbyname('BillTypeCode').asstring='0202') then
tmp_SqlBillTypeCode:=' and ((o.BillTypeCode='+quotedstr(fieldbyname('BillTypeCode').asstring)+' and InvBillqty>=0) '+
' or (o.BillTypeCode=''0299'' and opBill=1 and InvBillqty>=0))'
else if (fieldbyname('BillTypeCode').asstring='0203') or
(fieldbyname('BillTypeCode').asstring='0204') then
tmp_SqlBillTypeCode:=' and o.BillTypeCode='''+fieldbyname('BillTypeCode').asstring+''' '+
' and o.InvBillwhchck<>0 '
else if (fieldbyname('BillTypeCode').asstring='0299') then
tmp_SqlBillTypeCode:=' and o.BillTypeCode='+quotedstr(fieldbyname('BillTypeCode').asstring)+
' and opBill=0 and AmountAdjust<>1 '
else
tmp_SqlBillTypeCode:=' and o.BillTypeCode='+quotedstr(fieldbyname('BillTypeCode').asstring);
sql_SumInvOutBillqty:=sql_SumInvOutBillqty+
' insert #tmp1(ClassCode,'+
'C'+fieldbyname('BillTypeCode').asstring+')'+
' select #wtmp0.pClassCode,'+
' sum(isnull(ol.'+Qry_Sumfields+',0)) as InvOutBillqty '+
' from InvOutBill o,InvOutBillline ol,Item,#wtmp0 '+
' where '+whCodecondition+
' o.InvBillMonth='''+medt_Date.text+''''+
tmp_SqlBillTypeCode+
' and o.InvBillid=ol.InvBillid and ol.ItemCode=Item.ItemCode '+
' and Item.ClassCode=#wtmp0.ClassCode '+
' group by #wtmp0.pClassCode ';
//结帐月份到查询起始日期的数量 出方
next;
end;
//zcw
tableoutfields:= tableoutfields+'C9999 float null,';
sqloutsum:=sqloutsum+','+' sum(isnull(C9999,0)) as C9999 ';
sqloutremant:=sqloutremant+'+C9999';
sqloutfields:=sqloutfields+',C9999=case '+
' when C9999=0 then null else '+
'C9999 end ';
//其他出库中OPBill=1的属于委外领料
tmp_SqlBillTypeCode:=' and (o.BillTypeCode=''0202'' or (o.BillTypeCode=''0299'' and opBill=1)) and InvBillqty<0 ';
sql_SumInvOutBillqty:=sql_SumInvOutBillqty+
' insert #tmp1(ClassCode,'+
'C9999)'+
' select #wtmp0.pClassCode,'+
' sum(isnull(ol.'+Qry_Sumfields+',0)) as InvOutBillqty '+
' from InvOutBill o,InvOutBillline ol,Item,#wtmp0 '+
' where '+whCodecondition+
' o.InvBillMonth='''+medt_Date.text+''''+
tmp_SqlBillTypeCode+
' and o.InvBillid=ol.InvBillid and ol.ItemCode=Item.ItemCode '+
' and Item.ClassCode=#wtmp0.ClassCode '+
' group by #wtmp0.pClassCode ';
//zcw
end;
// 产生临时表的语句 ,一个用于存放出，入数量，一个用于计算期初数量的出入数量
sql_tmptable:=
' set noCount on '+
' create table #tmp1(ClassCode varchAr(16),'+tableinfields+tableoutfields+' remant float null,qichujiechun float null)'+
' create table #tmp3(ClassCode varchAr(16),'+tableinfields+tableoutfields+' qichu float null) ';
// 从InvMonthSum中取得当前结帐月份的结存数量增加到#TMP3，
// 结帐月份到查询起始日期的数量 入 增加到#TMP3，
// 结帐月份到查询起始日期的数量 出 增加到#TMP3，
//期初结存数从#TMP3中计算出来增加到TMP2表的 QICHUJIECHUN 字段中
// #tmp4为一个过渡的临时表
if cmbbx_WhCode.text='全部仓库' then
begin
whCodecondition:='';
end
else
whCodecondition:=' and InvMonthSum.whCode='''+getCode(cmbbx_WhCode.text)+'''';
sql_qichujiechun:=
sql_SumInvInBillqty_qichu+
sql_SumInvOutBillqty_qichu+
' insert #tmp3(ClassCode,qichu) '+
' select #wtmp0.pClassCode,'+Qry_Sumfields_InvMonthSum+
' from InvMonthSum,Item,#wtmp0 '+
' where InvMonthSum.ItemCode=Item.ItemCode'+
' and Item.ClassCode=#wtmp0.ClassCode '+
' and InvMonth='''+stArtMonth+''''+whCodecondition+
' select ClassCode,'+sqlinsum+','+sqloutsum+',sum(isnull(qichu,0)) as qichu'+
' into #tmp4 from #tmp3 '+
' group by ClassCode '+
' insert into #tmp1(ClassCode,qichujiechun) '+
' select ClassCode,qichu+'+sqlinremant+'-('+sqloutremant+')'+
' from #tmp4 ';
//按物料汇总临时表#tmp1数量
// ItemClass:=getCode(Trim(cmbbx_ItemClass.Text));
// ClassCodelen:=inttostr(length(ItemClass));
sql_Sum:=
' select ClassCode,'+
sqlinsum+','+
sqloutsum+
',sum(isnull(qichujiechun,0)) as qichujiechun '+
' into #tmp2 from #tmp1 '+
' group by ClassCode ';
//显示结果
sql_Result:=
'update #tmp2 set R1201=R1201-C9999 '+
'update #tmp2 set C9999=0 '+
'select #tmp2.ClassCode+'+''' '''+'+ItemClass.ClassName as ClassCodeName,'+
sqlinfields+','+
sqloutfields+
',remant=case when (qichujiechun+'+ sqlinremant+'-('+sqloutremant+'))=0 then null else (qichujiechun+'+ sqlinremant+'-('+sqloutremant+')) end '+
',insum=case when '+sqlinremant+'=0 then null else '+sqlinremant+' end '+
',outsum=case when '+sqloutremant+'=0 then null else '+sqloutremant+' end '+
',qichujiechun=case when qichujiechun=0 then null else qichujiechun end '+
' from #tmp2,ItemClass where #tmp2.ClassCode=ItemClass.ClassCode '+
' drop table #tmp1 '+
' drop table #tmp2 '+
' drop table #tmp3 '+
' drop table #tmp4 ';
//最后的SQL语句
sqltext:=sql_tmptable+
sql_qichujiechun+
sql_SumInvInBillqty+
sql_SumInvOutBillqty+
sql_Sum+
sql_Result;
condition:=sqltext;
modalResult:=mrok;
// Frm_Inv_MonthReport_C.cursor:=crdefault;
}
end;
procedure TFrm_Inv_MonthReport_C.FormActivate(Sender: TObject);
begin
inherited;
InitUsableWHCmBx(AdoQry_tmp,tmp_UserCode,cmbbx_WhCode,False);
cmbbx_WhCode.Items.insert(0,'全部仓库');
cmbbx_WhCode.ItemIndex:=0;
initcmbbx_ItemClass;
end;
procedure TFrm_Inv_MonthReport_C.cmbbx_WhCodeExit(Sender: TObject);
begin
inherited;
if activecontrol.Name='btn_Cancel' then
exit;
if cmbbx_WhCode.Text ='' then
begin
DispInfo('没有可用仓库！',1);
twincontrol(sender).setfocus;
abort;
end;
end;
procedure TFrm_Inv_MonthReport_C.MonthCheck(Sender: TObject);
begin
inherited;
// InvDataChangeCheck(AdoQry_tmp,medt_Date.text);
end;
procedure TFrm_Inv_MonthReport_C.cmbbx_ItemClassExit(Sender: TObject);
begin
inherited;
if activecontrol.Name='btn_Cancel' then
exit;
if cmbbx_ItemClass.text='' then
begin
DispInfo('物料类别不能为空！',1);
abort;
end;
end;
procedure TFrm_Inv_MonthReport_C.SetDBConnect(AdOConnection: TAdOConnection);
begin
inherited;
AdoQry_tmp1.Connection:=AdOConnection;
AdoQry_tmp2.Connection:=AdOConnection;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -