outbg.cpp

供销存软件

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

#include <vcl.h>
#pragma hdrstop

#include "outBG.h"
#include "DataModule.h"
#include "login.h"
#include "ComObj.hpp"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"

ToutBGForm *outBGForm;
//---------------------------------------------------------------------------
__fastcall ToutBGForm::ToutBGForm(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall ToutBGForm::FormCreate(TObject *Sender)
{
         DM->ADOQ->Close();
         DM->ADOQ->SQL->Clear();
         DM->ADOQ->SQL->Add("select 仓库名称 from 仓库信息表");
         DM->ADOQ->Open();
         CBck->Items->Add("全部");
         while(!DM->ADOQ->Eof)
         {
                  CBck->Items->Add(DM->ADOQ->FieldByName("仓库名称")->AsString.Trim());
                  DM->ADOQ->Next();
         }
         DM->ADOQ->Close();
}
//---------------------------------------------------------------------------

void __fastcall ToutBGForm::searchButtonClick(TObject *Sender)
{
        if(LoginForm->Edituser->Text=="admin"){
               int n=date2->Date-date1->Date;
                if(n<0){
                        ShowMessage("开始日期不得晚于结束日期，请重新查询。");
                        date1->SetFocus();
                }else{

                        DM->ADOQ->Close();
                        DM->ADOQ->SQL->Clear();
                        AnsiString str;
                        str="select 时间,商品编码,商品名称,条形码,商品规格,单位,类别,销售单价,数量,销售额,利润,利润率,最新单价,最新供应商 from ";
                        str+="(select a.出库时间 as 时间,a.出货仓库,b.商品编号 as 商品编码,c.商品名称,c.商品规格,c.单位,b.价格 as 销售单价,round(b.出库数量/2) as 数量,销售单价*数量 as 销售额,b.单个利润*数量 as 利润,round(利润/销售额*1000)/10 as 利润率,c.类别,c.供货商 as 最新供应商,c.单价 as 最新单价,c.条形码 from (出库信息表 as a inner join 出库商品表 as b on a.编号=b.出库单编号) inner join 商品信息表 as c on b.商品编号=c.编号)";
                        str+=" where 时间>=#";
                        str+=date1->Date.DateString();
                        str+="# and ";
                        str+="时间<=#";
                        str+=date2->Date.DateString();
                        str+='#';
                        if(CBck->Text!="全部"){
                                str+=" and 出货仓库='";
                                str+=CBck->Text;
                                str+="'";
                        }
                        DM->ADOQ->SQL->Add(str);
                        DM->ADOQ->Open();

                        double sum1=0,sum2=0;
                        while(!DM->ADOQ->Eof)
                        {
                                sum1+=DM->ADOQ->FieldByName("销售额")->AsString.Trim().ToDouble();
                                sum2+=DM->ADOQ->FieldByName("利润")->AsString.Trim().ToDouble();
                                DM->ADOQ->Next();
                        }
                        XSsum->Text=sum1;
                        LRsum->Text=sum2;
                }
        }else{
                int n=date2->Date-date1->Date;
                if(n<0){
                        ShowMessage("开始日期不得晚于结束日期，请重新查询。");
                        date1->SetFocus();
                }else{

                        DM->ADOQ->Close();
                        DM->ADOQ->SQL->Clear();
                        AnsiString str;
                        str="SELECT 时间,商品编码,商品名称,条形码,商品规格,单位,类别,销售单价,数量,销售额,利润,利润率,最新单价,最新供应商 ";
                        str+="from (select a.出库时间 as 时间,a.出货仓库,b.商品编号 as 商品编码,c.商品名称,c.商品规格,c.单位,b.价格 as 销售单价,b.出库数量 as 数量,销售单价*数量 as 销售额,b.单个利润*数量 as 利润,round(利润/销售额*1000)/10 as 利润率,c.类别,c.供货商 as 最新供应商,c.单价 as 最新单价,c.条形码 from (出库信息表 as a inner join 出库商品表 as b on a.编号=b.出库单编号) inner join 商品信息表 as c on b.商品编号=c.编号)";
                        str+=" where 时间>=#";
                        str+=date1->Date.DateString();
                        str+="# and ";
                        str+="时间<=#";
                        str+=date2->Date.DateString();
                        str+='#';
                        if(CBck->Text!="全部"){
                                str+=" and 出货仓库='";
                                str+=CBck->Text;
                                str+="'";
                        }
                        DM->ADOQ->SQL->Add(str);
                        DM->ADOQ->Open();
                        double sum1=0,sum2=0;
                        while(!DM->ADOQ->Eof)
                        {
                                sum1+=DM->ADOQ->FieldByName("销售额")->AsString.Trim().ToDouble();
                                sum2+=DM->ADOQ->FieldByName("利润")->AsString.Trim().ToDouble();
                                DM->ADOQ->Next();
                        }
                        XSsum->Text=sum1;
                        LRsum->Text=sum2;
                }
        }
}
//---------------------------------------------------------------------------



void __fastcall ToutBGForm::printBtnClick(TObject *Sender)
{
          Variant Ex,Wb,Sheet,ERange,EBorders;
          try
          {
                //取报表文件*.xls的完整目录名
                AnsiString ExcelFileName = GetCurrentDir()+"\\out\\销售报表.xls";
                if(!FileExists(ExcelFileName))
                {
                        Application->MessageBox("报表模板文件不存在,无法打开！","错误",MB_ICONSTOP|MB_OK);
                        return;
                }
                //建立Excel的Ole对象Ex
                try
                {
                        Ex = Variant::CreateObject("Excel.Application");
                }
                catch(...)
                {
                        Application->MessageBox("无法启动Excel","错误",MB_ICONSTOP|MB_OK);
                        return;
                }
                //设置Excel为不可见
                Ex.OlePropertySet("Visible",false);
                //打开指定的Excel报表文件。报表文件中最好设定只有一个Sheet。
                Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",ExcelFileName.c_str());
                Wb = Ex.OlePropertyGet("ActiveWorkBook");
                Sheet=Wb.OlePropertyGet("ActiveSheet");//获得当前默认的Sheet
                //清空Excel表，这里是用循环清空到第100行。对于一般的表格已经足够了。
                AnsiString strRowTemp;
                AnsiString strRange;
                int iCols,iRows;//记录列数和行数

                /*从第三行开始，到第100行止。一般第一行是表标题，第二行是制表日期。*/
                for(iRows=3;iRows<20;iRows++)
                {
                        for (iCols = 1;iCols < 16; iCols++)
                        {
                                //清空行
                                Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value","");
                        }
                }

                AnsiString strPtrDate; //存放当前日期，作为制表日期
                DateSeparator = '-';
                ShortDateFormat = "yyyy/m/d";//设置为年/月/日格式
                strPtrDate = DateToStr(Date());//取当前日期
                AnsiString strYear = strPtrDate.SubString(1,4);
                strPtrDate = strPtrDate.SubString(6,strPtrDate.Length()-5);
                AnsiString strMonth = strPtrDate.SubString(1,strPtrDate.Pos("-")-1);
                AnsiString strDay =strPtrDate.SubString(strPtrDate.Pos("-")+1,strPtrDate.Length()-strPtrDate.Pos("-"));
                strPtrDate = strYear+"年"+strMonth+"月"+strDay+"日";

                AnsiString strData = "小刀儿2007 销售报表";//报表标题
                //将报表标题置于第一行第一列。在此之前，应将报表文件的标题格式设定好。
                Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value",strData.c_str());
                //将制表日期置于表格第二行的右侧。
                Sheet.OlePropertyGet("Cells",2,10).OlePropertySet("Value",strPtrDate.c_str());

                iRows = 3;//在第三行放置表格的列名
                Sheet.OlePropertyGet("Cells",iRows,1).OlePropertySet("Value","时间");
                Sheet.OlePropertyGet("Cells",iRows,2).OlePropertySet("Value","商品编码");
                Sheet.OlePropertyGet("Cells",iRows,3).OlePropertySet("Value","商品名称");
                Sheet.OlePropertyGet("Cells",iRows,4).OlePropertySet("Value","条形码");
                Sheet.OlePropertyGet("Cells",iRows,5).OlePropertySet("Value","商品规格");
                Sheet.OlePropertyGet("Cells",iRows,6).OlePropertySet("Value","单位");
                Sheet.OlePropertyGet("Cells",iRows,7).OlePropertySet("Value","类别");
                Sheet.OlePropertyGet("Cells",iRows,8).OlePropertySet("Value","销售单价");
                Sheet.OlePropertyGet("Cells",iRows,9).OlePropertySet("Value","数量");
                Sheet.OlePropertyGet("Cells",iRows,10).OlePropertySet("Value","销售额");
                Sheet.OlePropertyGet("Cells",iRows,11).OlePropertySet("Value","利润");
                Sheet.OlePropertyGet("Cells",iRows,12).OlePropertySet("Value","利润率");
                Sheet.OlePropertyGet("Cells",iRows,13).OlePropertySet("Value","最新单价");
                Sheet.OlePropertyGet("Cells",iRows,14).OlePropertySet("Value","最新供应商");
                iRows++;
                //从数据库中取数据
                DM->ADOQ->First();
                //循环取数
                while(!DM->ADOQ->Eof)
                {
                        //循环取字段的数据放到Excel表对应的行列中
                        for(iCols=1;iCols<15;iCols++)
                        {
                                strRowTemp=DM->ADOQ->Fields->Fields[iCols-1]->AsString;
                                Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value",strRowTemp.c_str());
                        }
                        iRows++;
                        DM->ADOQ->Next();
                }//while结束
                Ex.OlePropertySet("Visible",true);
        }//try结束
        catch(...)
        {
                Application->MessageBox("操作Excel表格失败！","错误",MB_ICONSTOP|MB_OK);
                Wb.OleProcedure("Close");
                Ex.OleFunction("Quit");
        }
}
//---------------------------------------------------------------------------

void __fastcall ToutBGForm::closeBtnClick(TObject *Sender)
{
         Close();
}
//---------------------------------------------------------------------------

void __fastcall ToutBGForm::FormClose(TObject *Sender,
      TCloseAction &Action)
{
         DM->ADOQ->Close();
}
//---------------------------------------------------------------------------