非常感谢各位,下面是物品进仓的部分代码:
var
CP_IDS, CK_NameS1, Fu_Cha : string;
procedure TFL_Data.BJinCang2AfterDelete(DataSet: TDataSet);
begin
CK_IO_Refresh(CP_IDS, CK_NameS1, Fu_Cha);
end;
procedure TFL_Data.BJinCang2AfterPost(DataSet: TDataSet);
begin
BJinCang2.FlushBuffers;
CK_IO_Refresh(FL_Data.BJinCang2.FieldByName('CP_ID').AsString, FL_Data.BJinCang2.FieldByName('CK_Name').AsString, FL_Data.BJinCang2.FieldByName('FuCha').AsString);
end;
procedure TFL_Data.BJinCang2BeforeDelete(DataSet: TDataSet);
begin
CP_IDS := FL_Data.BJinCang2.FieldByName('CP_ID').AsString;
CK_NameS1 := FL_Data.BJinCang2.FieldByName('CK_Name').AsString;
Fu_Cha := FL_Data.BJinCang2.FieldByName('FuCha').AsString;
end;
procedure TFL_Data.BJinCang2BeforePost(DataSet: TDataSet);
begin
if FL_Data.BJinCang2.FieldByName('CK_Name').AsString = '' then
begin
Application.MessageBox('必需录入仓库名称! ','警告', MB_OK);
Abort;
end;
end;
procedure CK_IO_Refresh(CPID, CKName, FuChaS : String);
var
SC, SC1 : String;
CaiGouJinCangShuLiang, CaiGouJinCangJinE : Double ; //1 本期采购进仓数量/金额
LingLiaoChuCangShuLiang, LingLiaoChuCangJinE : Double ; //2 本期领料出仓数量/金额
ShengChanJInCangShuLiang, ShengChanJInCangJinE : Double ; //3 本期生产进仓数量/金额
XiaoShouChuCangShuLiang, XiaoShouChuCangJinE : Double ; //4 本期销售出仓数量/金额
ZhuanCangShuLiang1, ZhuanCangJinE1 : Double ; //5 本期转出仓数量/金额
// ZhuanCangShuLiang2, ZhuanCangJinE2 : Double ; //6 本期转进仓数量/金额
JinHuoTuiHuoShuLiang, JinHuoTuiHuoJinE : Double ; //7 进货退货数量/金额
ChuCangTuiCangShuLiang, ChuCangTuiCangJinE : Double ; //8 出仓退仓数量/金额
XiaoShouTuiHuoShuLiang, XiaoShouTuiHuoJinE, XiaoShouTuiHuoJinE2 : Double ; //9 销售退货数量/金额
JinCangShuLiang, JinCangJinE : Double ; //进仓数量/金额
ChuCangShuLiang, ChuCangJinE : Double ; //出仓数量/金额
JieCunShuLiang, JieCunJinE : Double ; //结存数量/金额
ShuLiangAA, JinEAA : Double ; //初始库存数量/金额
PingJuDanJia : Double ; //库存平均单价
begin
SC := ' SELECT ''采购入库'' AS 用途, SUM(JCD_ShuLiang) As 数量, SUM(JCD_ShuLiang * JCD_DanJia) As 金额 ';
SC := SC + ' FROM ":FL_Data:BJinCang1" , ":FL_Data:BJinCang2"';
SC := SC + ' Where (BJinCang1.JC_ID = BJinCang2.JC_ID) and JC_Type="采购" ';
SC := SC + ' and CP_ID ="' + CPID + '" and BJinCang2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, BJinCang2.CK_Name';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''领用出仓'' AS 用途, SUM(CCD_ShuLiang) As 数量, SUM(CCD_ShuLiang * CCD_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data
ChuCang1" , ":FL_Data
ChuCang2"';
SC := SC + ' Where (DChuCang1.CC_ID = DChuCang2.CC_ID) and CC_Type="领料"';
SC := SC + ' and CP_ID ="' + CPID + '" and DChuCang2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, DChuCang2.CK_Name';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''生产进仓'' AS 用途, SUM(JCD_ShuLiang) As 数量, SUM(JCD_ShuLiang * JCD_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data:BJinCang1" , ":FL_Data:BJinCang2"';
SC := SC + ' Where (BJinCang1.JC_ID = BJinCang2.JC_ID) and JC_Type="生产"';
SC := SC + ' and CP_ID ="' + CPID + '" and BJinCang2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, BJinCang2.CK_Name ';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''销售出货'' AS 用途, SUM(CCD_ShuLiang) As 数量, SUM(CCD_ShuLiang * CCD_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data
ChuCang1" , ":FL_Data
ChuCang2"';
SC := SC + ' Where (DChuCang1.CC_ID = DChuCang2.CC_ID) and CC_Type="销售"';
SC := SC + ' and CP_ID ="' + CPID + '" and DChuCang2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, DChuCang2.CK_Name';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''调货出仓'' AS 用途, SUM(KCZD_ShuLiang) As 数量, SUM(KCZD_ShuLiang * CP_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data:KCZhuanCang1" , ":FL_Data:KCZhuanCang2"';
SC := SC + ' Where (KCZhuanCang1.KCZ_ID = KCZhuanCang2.KCZ_ID) and BiaoZhi2="调货单"';
SC := SC + ' and CP_ID ="' + CPID + '" and KCZhuanCang2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, KCZhuanCang2.CK_Name ';
SC := SC + ' UNION All ';
SC := SC + ' SELECT ''采购退货'' AS 用途, SUM(DT2_ShuLiang) As 数量, SUM(DT2_ShuLiang * DT_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data
TuiHuo1" , ":FL_Data
TuiHuo2"';
SC := SC + ' Where (DTuiHuo1.DT_ID = DTuiHuo2.DT_ID) and DT_Type="采购"';
SC := SC + ' and CP_ID ="' + CPID + '" and DTuiHuo2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, DTuiHuo2.CK_Name';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''领用退仓'' AS 用途, SUM(DT2_ShuLiang) As 数量, SUM(DT2_ShuLiang * DT_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data
TuiHuo1" , ":FL_Data
TuiHuo2"';
SC := SC + ' Where (DTuiHuo1.DT_ID = DTuiHuo2.DT_ID) and DT_Type="领料"';
SC := SC + ' and CP_ID ="' + CPID + '" and DTuiHuo2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, DTuiHuo2.CK_Name';
SC := SC + ' UNION All';
SC := SC + ' SELECT ''销售退货'' AS 用途, SUM(DT2_ShuLiang) As 数量, SUM(DT2_ShuLiang * DT_DanJia) As 金额';
SC := SC + ' FROM ":FL_Data
TuiHuo1" , ":FL_Data
TuiHuo2"';
SC := SC + ' Where (DTuiHuo1.DT_ID = DTuiHuo2.DT_ID) and DT_Type="销售"';
SC := SC + ' and CP_ID ="' + CPID + '" and DTuiHuo2.CK_Name = "' + CKName + '"';
SC := SC + ' and FuCha = ' + FuChaS ;
SC := SC + ' GROUP BY CP_ID, DTuiHuo2.CK_Name';
SQL_PLAY(FL_Data.KuCun_RefreshQ , SC);
FL_Data.KuCun_RefreshQ.First;
while not FL_Data.KuCun_RefreshQ.eof do
begin
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '采购入库' then
begin
//1 本期采购进仓数量/金额
CaiGouJinCangShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
CaiGouJinCangJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '领用出仓' then
begin
//2 本期领料出仓数量/金额
LingLiaoChuCangShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
LingLiaoChuCangJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '生产进仓' then
begin
//3 本期生产进仓数量/金额
ShengChanJInCangShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
ShengChanJInCangJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '销售出货' then
begin
//4 本期销售出仓数量/金额
XiaoShouChuCangShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
XiaoShouChuCangJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '调货出仓' then
begin
//5 本期转出仓数量/金额
ZhuanCangShuLiang1 := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
ZhuanCangJinE1 := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '采购退货' then
begin
//7 进货退货数量/金额
JinHuoTuiHuoShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
JinHuoTuiHuoJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '领用退仓' then
begin
//8 出仓退仓数量/金额
ChuCangTuiCangShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
ChuCangTuiCangJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '销售退货' then
begin
//9 销售退货数量/金额
XiaoShouTuiHuoShuLiang := FL_Data.KuCun_RefreshQ.FieldByName('数量').AsFloat;
XiaoShouTuiHuoJinE := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
if FL_Data.KuCun_RefreshQ.FieldByName('用途').AsString = '销售退仓' then
begin
//9 销售退仓数量/金额
XiaoShouTuiHuoJinE2 := FL_Data.KuCun_RefreshQ.FieldByName('金额').AsFloat;
end;
FL_Data.KuCun_RefreshQ.next;
end;
condition := 'SELECT CP_ChuShi AS 初始库存, CP_DanJia AS 初始单价, CP_ChuShi * CP_DanJia AS 初始金额';
condition := condition + ' FROM ":' + DataName + ':CKXinXi"';
condition := condition + ' Where CP_ID="' + CPID + '"';
condition := condition + ' and CK_Name="' + CKName + '"';
SQL_PLAY(FL_Data.CKXinXiQ, condition);
ShuLiangAA := FL_Data.CKXinXiQ.FieldByName('初始库存').AsFloat;
JinEAA := FL_Data.CKXinXiQ.FieldByName('初始金额').AsFloat;
//****************************************************************************//
//进仓数量
JinCangShuLiang := CaiGouJinCangShuLiang - JinHuoTuiHuoShuLiang + ShengChanJInCangShuLiang + ChuCangTuiCangShuLiang;
//进仓金额
JinCangJinE := CaiGouJinCangJinE - JinHuoTuiHuoJinE + ShengChanJInCangJinE + ChuCangTuiCangJinE ;
//出仓数量
ChuCangShuLiang := LingLiaoChuCangShuLiang - ChuCangTuiCangShuLiang + XiaoShouChuCangShuLiang - XiaoShouTuiHuoShuLiang + ZhuanCangShuLiang1 ;
//出仓金额
ChuCangJinE := LingLiaoChuCangJinE + XiaoShouChuCangJinE - XiaoShouTuiHuoJinE2 + ZhuanCangJinE1 ;
//结存数量
JieCunShuLiang := JinCangShuLiang - ChuCangShuLiang + ShuLiangAA;
//库存平均单价
if JinCangShuLiang <> 0 then
begin
PingJuDanJia := ( ABS(JinEAA) + ABS(JinCangJinE) ) / (ABS(JinCangShuLiang) + ABS(ShuLiangAA) ); //库存平均单价
end
else
begin
PingJuDanJia := FL_Data.CKXinXiQ.fieldbyname('初始单价').AsFloat ; //库存平均单价
end;
//结存金额
JieCunJinE := JieCunShuLiang * PingJuDanJia ;
//***********************************更新库存数据 开始***********************************
condition := 'UPDATE ":' + DataName + ':CKXinXi" SET ';
condition := condition + ' CK_XiaoShouJinE = ' + FloatToStr(XiaoShouChuCangJinE - XiaoShouTuiHuoJinE) + ','; //销售数量
condition := condition + ' CK_XiaoShouShuLiang = ' + FloatToStr(XiaoShouChuCangShuLiang - XiaoShouTuiHuoShuLiang) + ','; //销售金额
condition := condition + ' CK_BenQiJinCang = ' + FloatToStr(JinCangShuLiang) + ','; //本期进仓
condition := condition + ' CK_JinCangJinE = ' + FloatToStr(JinCangJinE) + ','; //进仓金额
condition := condition + ' CK_BenQiChuCang = ' + FloatToStr(ChuCangShuLiang) + ','; //本期出仓
condition := condition + ' CK_ChuCangJInE = ' + FloatToStr(ChuCangJinE) + ','; //出仓金额
condition := condition + ' CK_JieCun = ' + FloatToStr(JieCunShuLiang) + ','; //结存数量
condition := condition + ' CK_JieCunJinE = ' + FloatToStr(JieCunJinE) + ','; //结存金额
condition := condition + ' CP_DanJia1 = ' + FloatToStr(PingJuDanJia); //库存平均单价
condition := condition + ' Where CP_ID="' + CPID + '"';
condition := condition + ' and CK_Name="' + CKName + '"';
condition := condition + ' and FuCha = ' + FuChaS ;
//ShowMessage(condition);
SQL_Updata(FL_Data.Delete_Note, condition);
//***********************************更新库存数据 结束***********************************
end;