dmodunit.cpp

该软件为计算机公司进销存管理系统。方便公司管理人员对进货、出货和库存产品的管理和维护。

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "DModUnit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TDMod *DMod;
//---------------------------------------------------------------------------
__fastcall TDMod::TDMod(TComponent* Owner)
    : TDataModule(Owner)
{
}
//---------------------------------------------------------------------------
//执行一个SQL语句,该语句的功能是增加修改删除数据
void __fastcall TDMod::ExecuteSQL(AnsiString SQLStr,AnsiString ErrorStr)
{
    try
    {
        ADOConnection1->BeginTrans();
        QueryTemp->Close();
        QueryTemp->SQL->Clear();
        QueryTemp->SQL->Add(SQLStr);
        QueryTemp->ExecSQL();
        ADOConnection1->CommitTrans() ;
    }
    catch(...)
    {
        ADOConnection1->RollbackTrans();
        ShowMessage(ErrorStr);
    }
}

//打开数据集 SQLStr 要执行的SQL语句 LQuery 打开数据集使用的Query ErrorStr 程序出现异常时的提示信息
void __fastcall TDMod::DisplayData(TADOQuery *LQuery,AnsiString SQLStr,AnsiString KeyField,AnsiString Bh,AnsiString ErrorStr)
{
    try
    {
        TLocateOptions Opts;

        Opts.Clear();
        Opts << loPartialKey;
        Variant locvalues[1];
        locvalues[0] = Variant(Bh);
        locvalues[1] = Variant("9");

        LQuery->Close();
        LQuery->SQL->Clear();
        LQuery->SQL->Add(SQLStr);
        LQuery->Open();

        if (! LQuery->Eof)
                LQuery->Locate("bh", VarArrayOf(locvalues,1), Opts);
    }
    catch(...)
    {
        ShowMessage(ErrorStr);
    }

}

//将数据库中的某个表的某个字段读出来放到指定的ComboBox中
void __fastcall TDMod::AddMcToComboBox(AnsiString TableName,AnsiString FieldName,AnsiString ErrorStr,TComboBox * TempComboBox)
{
    try
    {
        TempComboBox->Clear();
        QueryTemp->Close();
        QueryTemp->SQL->Clear();
        SQLStr = Format("select %s from %s",OPENARRAY(TVarRec,(FieldName,TableName)));
        QueryTemp->SQL->Add(SQLStr);
        QueryTemp->Open();
        while(! QueryTemp->Eof){
            TempComboBox->Items->Add(AnsiString(QueryTemp->FieldValues[FieldName]));
            QueryTemp->Next();
        }
        if (TempComboBox->Items->Count > 0)
            TempComboBox->ItemIndex = 0;

    }
    catch(...)
    {
        ShowMessage(ErrorStr);
    }
}




int __fastcall TDMod::GetSl(AnsiString SQLStr)
{
    try
    {
        int Sl=0;

        QueryTemp->Close();
        QueryTemp->SQL->Clear();
        QueryTemp->SQL->Add(SQLStr);
        QueryTemp->Open();
        if(!QueryTemp->Eof){
            Sl = QueryTemp->FieldByName("sl")->AsInteger;
        }
        return(Sl);
    }
    catch(...)
    {
        ShowMessage("计算产品数量出错");
        return(0);
    }

}

void __fastcall TDMod::WriteDataToRbb(AnsiString Today)
{
    try
    {
        AnsiString Str,Cpbh;
        int Sl,Dr,Dj,Diaoc,Duic,Jhsl,Xssl;
        float Je;
        SQLStr = "delete from rbb";
        DMod->ExecuteSQL(SQLStr,"清除旧数据出错");
        SQLStr = "insert into rbb(cpbh,cpjc,gjdj,xsdj)(select cpbh,jc,jj,sj from cpxxb)";
        DMod->ExecuteSQL(SQLStr,"写产品信息数据出错");
        //写进货数据 同一天相同的产品不能进两次 所以不用SUM(dr)
        SQLStr = Format("select cpbh,sl,je,dr,dj from jhb where rq='%s'",OPENARRAY(TVarRec,(Today)));
        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add(SQLStr);
        Query1->Open();
        while(!Query1->Eof){
            Cpbh = Query1->FieldByName("cpbh")->AsString;
            Sl = Query1->FieldByName("sl")->AsInteger;
            Je = Query1->FieldByName("je")->AsFloat;
            Dr = Query1->FieldByName("dr")->AsInteger;
            Dj = Query1->FieldByName("dj")->AsInteger;
            SQLStr = Format("update rbb set gjsl=%d,gjje=%f,dr=%d,dj=%d where cpbh='%s'",
                            OPENARRAY(TVarRec,(Sl,Je,Dr,Dj,Cpbh)));
            ExecuteSQL(SQLStr,"写进货数据出错");
            Query1->Next();
        }
        //写销售数据
        SQLStr = Format("select cpbh, sum(sl)sl,sum(ssje)je,sum(diaoc)diaoc,sum(duic)duic from shb where rq='%s' group by cpbh ",OPENARRAY(TVarRec,(Today)));
        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add(SQLStr);
        Query1->Open();
        while(!Query1->Eof){
            Cpbh = Query1->FieldByName("cpbh")->AsString;
            Sl = Query1->FieldByName("sl")->AsInteger;
            Je = Query1->FieldByName("je")->AsFloat;
            Diaoc = Query1->FieldByName("diaoc")->AsInteger;
            Duic = Query1->FieldByName("duic")->AsInteger;
            SQLStr = Format("update rbb set xssl=%d,xsje=%f,diaoc=%d,duic=%d where cpbh='%s'",
                        OPENARRAY(TVarRec,(Sl,Je,Diaoc,Duic,Cpbh)));
            ExecuteSQL(SQLStr,"写销货数据出错");
            Query1->Next();
        }
        //写库存数据，只写数量，单价等于售价，金额等于数量×单价
        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add("select cpbh from rbb ");
        Query1->Open();
        while(!Query1->Eof){
            Cpbh = Query1->FieldByName("cpbh")->AsString;
            SQLStr = Format("select sum(sl)sl from jhb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Jhsl = GetSl(SQLStr);
            SQLStr = Format("select sum(sl)sl from shb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Xssl = GetSl(SQLStr);
            SQLStr = Format("select sum(dr)sl from jhb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Dr = GetSl(SQLStr);
            SQLStr = Format("select sum(dj)sl from jhb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Dj = GetSl(SQLStr);
            SQLStr = Format("select sum(diaoc)sl from shb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Diaoc = GetSl(SQLStr);
            SQLStr = Format("select sum(duic)sl from shb where cpbh='%s'",OPENARRAY(TVarRec,(Cpbh)));
            Duic = GetSl(SQLStr);
            SQLStr = Format("update rbb set kcsl=%d,kcje=%d*gjdj where cpbh='%s'",
                        OPENARRAY(TVarRec,(Jhsl+Dr+Dj-Xssl-Diaoc-Duic,Jhsl+Dr+Dj-Xssl-Diaoc-Duic,Cpbh)));
            ExecuteSQL(SQLStr,"写库存数据出错");
            Query1->Next();
        }

    }
    catch(...)
    {
        ShowMessage("统计日报表数据出错");
    }

}


void __fastcall TDMod::WriteDataToYbb(int Year,int Month) 
{
    try
    {
        AnsiString Str,Cpbh,S1,S2;
        int Sl,Num1,Num2,Num,Dr,Dj,Diaoc,Duic;
        int Bqgjsl,Bqxssl;
        float Je,Jj,Sj,Pv;
        int Y1,Y2,M1,M2;
        AnsiString T1,T2;
        M1 = Month;
        Y1 = Year;
        if(M1 == 12){
            Y2 = Y1 + 1; M2 = 1;
        }
        else{
            Y2 = Y1;  M2 = M1 + 1;
        }
        S1 = IntToStr(M1); S2 = IntToStr(M2);
        if (S1.Length() == 1) S1 = "0" + S1;
        if (S2.Length() == 1) S1 = "0" + S2;

        T1 = IntToStr(Y1) + "-" + S1 + "-01";
        T2 = IntToStr(Y2) + "-" + S2 + "-01";


        SQLStr = "delete from ybb";
        DMod->ExecuteSQL(SQLStr,"清除旧数据出错");
        
        SQLStr = "insert into ybb(cpbh,cpjc)(select cpbh,jc from cpxxb)";
        DMod->ExecuteSQL(SQLStr,"写产品信息数据出错");

        Query1->Close();
        Query1->SQL->Clear();
        Query1->SQL->Add("select cpbh,jj,sj,pv from cpxxb ");
        Query1->Open();
        while(!Query1->Eof){
            Cpbh = Query1->FieldByName("cpbh")->AsString;
            Jj = Query1->FieldByName("jj")->AsFloat;
            Sj = Query1->FieldByName("sj")->AsFloat;
            Pv = Query1->FieldByName("pv")->AsFloat;
            //计算上期结存数量 金额
            SQLStr = Format("select sum(sl)sl from jhb where cpbh='%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1)));
            Num1 = GetSl(SQLStr);
            SQLStr = Format("select sum(sl)sl from shb where cpbh='%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1)));
            Num2 = GetSl(SQLStr);
            SQLStr = Format("select sum(dr)sl from jhb where cpbh='%s' and rq < '%s'",OPENARRAY(TVarRec,(Cpbh,T1)));
            Dr = GetSl(SQLStr);
            SQLStr = Format("select sum(dj)sl from jhb where cpbh='%s' and rq < '%s'",OPENARRAY(TVarRec,(Cpbh,T1)));
            Dj = GetSl(SQLStr);
            SQLStr = Format("select sum(diaoc)sl from shb where cpbh='%s' and rq < '%s'",OPENARRAY(TVarRec,(Cpbh,T1)));
            Diaoc = GetSl(SQLStr);
            SQLStr = Format("select sum(duic)sl from shb where cpbh='%s' and rq < '%s'",OPENARRAY(TVarRec,(Cpbh,T1)));
            Duic = GetSl(SQLStr);
            Je = (Num1+Dr+Dj - Num2-Diaoc-Duic)*Jj;
            SQLStr = Format("update ybb set sqjcsl=%d,sqjcje=%f where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num1+Dr+Dj - Num2-Diaoc-Duic,Je,Cpbh)));
            ExecuteSQL(SQLStr,"写上期结存数据出错");
            //本期购进数量 金额
            SQLStr = Format("select sum(sl)sl from jhb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Bqgjsl = Num;
            Je = Num*Jj;
            SQLStr = Format("update ybb set bqgjsl=%d,bqgjje=%f where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Je,Cpbh)));
            ExecuteSQL(SQLStr,"写本期购进数据出错");
            SQLStr = Format("select sum(dr)sl from jhb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Dr = Num;
            SQLStr = Format("update ybb set diaoru=%d where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Cpbh)));
            ExecuteSQL(SQLStr,"写本期购进数据出错");
            SQLStr = Format("select sum(dj)sl from jhb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Dj = Num;
            SQLStr = Format("update ybb set duijin=%d where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Cpbh)));
            ExecuteSQL(SQLStr,"写本期购进数据出错");
            //本月销售数量 金额
            SQLStr = Format("select sum(sl)sl from shb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Bqxssl = Num;
            Je = Num*Sj;
            SQLStr = Format("update ybb set bqxssl=%d,bqxsje=%f where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Je,Cpbh)));
            ExecuteSQL(SQLStr,"写本期销售数据出错");
            SQLStr = Format("select sum(diaoc)sl from shb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Diaoc = Num;
            SQLStr = Format("update ybb set diaochu=%d where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Cpbh)));
            ExecuteSQL(SQLStr,"写本期销售数据出错");
            SQLStr = Format("select sum(duic)sl from shb where cpbh='%s' and rq >= '%s' and rq < '%s'",
                            OPENARRAY(TVarRec,(Cpbh,T1,T2)));
            Num = GetSl(SQLStr);
            Duic = Num;
            SQLStr = Format("update ybb set duichu=%d where cpbh='%s'",
                        OPENARRAY(TVarRec,(Num,Cpbh)));
            ExecuteSQL(SQLStr,"写本期销售数据出错");
            //本月结存数量 金额
            SQLStr = Format("update ybb set bqjcsl=%d,bqjcje=%f,zpv=%f where cpbh='%s'",
                        OPENARRAY(TVarRec,(Bqgjsl+Dr+Dj-Bqxssl-Diaoc-Duic,(Bqgjsl+Dr+Dj-Bqxssl-Diaoc-Duic)*Jj,
                        (Bqgjsl+Dr+Dj-Bqxssl-Diaoc-Duic)*Pv,Cpbh)));
            ExecuteSQL(SQLStr,"写本期销售数据出错");
            //成本＝本期购进金额   利润＝本期销售金额－ 本期购进金额
            //总PV=本月结存数量×PV  费用 为空

            Query1->Next();
        }

    }
    catch(...)
    {
        ShowMessage("统计日报表数据出错");
    }

}

/*
    select s.name sname,c.name cname,c.sj,c.jc,c.pv,shb.* from cpxxb c,sqb s,shb
where c.cpbh=shb.cpbh and s.bh=shb.rybh

*/