khsale.cpp

供销存软件

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

#include <vcl.h>
#pragma hdrstop

#include "KHsale.h"
#include "DataModule.h"
#include "login.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TKHsaleForm *KHsaleForm;
//---------------------------------------------------------------------------
__fastcall TKHsaleForm::TKHsaleForm(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TKHsaleForm::FormCreate(TObject *Sender)
{
         DM->ADOQ->SQL->Clear();
         DM->ADOQ->SQL->Add("select 名称 from 客户资料表 where 客户类型='供货客户'");
         DM->ADOQ->Open();
         GYS->Items->Add("全部");
         while(!DM->ADOQ->Eof)
         {
                  GYS->Items->Add(DM->ADOQ->FieldByName("名称")->AsString.Trim());
                  DM->ADOQ->Next();
         }
         DM->ADOQ->Close();
         GYS->ItemIndex=0;
}
//---------------------------------------------------------------------------

void __fastcall TKHsaleForm::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="select *  from (select c.出库时间,e.商品名称,e.条形码,e.商品规格,e.单位,round(d.出库数量/2) as 数量,d.价格 as 最新单价,数量*最新单价 as 零售金额,b.价格 as 入库单价,a.客户名称 as 供应商 from ";
                str+="(((入库信息表 as a inner join 入库商品表 as b on a.编号=b.入库单编号) inner join 出库商品表 as d on b.商品编号=d.商品编号) inner join 出库信息表 as c on c.编号=d.出库单编号) inner join 商品信息表 as e on e.编号=b.商品编号)";
                str+=" where 出库时间>=#";
                str+=Date1->Date.DateString();
                str+="# and ";
                str+="出库时间<=#";
                str+=Date2->Date.DateString();
                str+='#';
                if(GYS->ItemIndex!=0){
                         str+=" and 供应商='";
                         str+=GYS->Text;
                         str+="'";
                 }
                 DM->ADOQ->SQL->Add(str);
                 DM->ADOQ->Open();
        }
    }else{
        int n=Date2->Date-Date1->Date;
        if(n<0){
                ShowMessage("开始日期不得晚于结束日期，请重新查询。");
                Date1->SetFocus();
        }else{
                DM->ADOQ->Close();
                DM->ADOQ->SQL->Clear();
                AnsiString str="select *  from (select c.出库时间,e.商品名称,e.条形码,e.商品规格,e.单位,d.出库数量 as 数量,d.价格 as 最新单价,数量*最新单价 as 零售金额,b.价格 as 入库单价,a.客户名称 as 供应商 from ";
                str+="(((入库信息表 as a inner join 入库商品表 as b on a.编号=b.入库单编号) inner join 出库商品表 as d on b.商品编号=d.商品编号) inner join 出库信息表 as c on c.编号=d.出库单编号) inner join 商品信息表 as e on e.编号=b.商品编号)";
                str+=" where 出库时间>=#";
                str+=Date1->Date.DateString();
                str+="# and ";
                str+="出库时间<=#";
                str+=Date2->Date.DateString();
                str+='#';
                if(GYS->ItemIndex!=0){
                         str+=" and 供应商='";
                         str+=GYS->Text;
                         str+="'";
                 }
                 DM->ADOQ->SQL->Add(str);
                 DM->ADOQ->Open();
        }
    }
}
//---------------------------------------------------------------------------

void __fastcall TKHsaleForm::toCFBtnClick(TObject *Sender)
{
          //
}
//---------------------------------------------------------------------------

void __fastcall TKHsaleForm::printBtnClick(TObject *Sender)
{
           //ShowMessage("无法打印");
           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表，这里是用循环清空到第20行。对于一般的表格已经足够了。
                AnsiString strRowTemp;
                AnsiString strRange;
                int iCols,iRows;//记录列数和行数

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

                AnsiString strPtrDate,strlastDate;
                strPtrDate = DateToStr(Date1->Date);
                strlastDate = DateToStr(Date2->Date);
                strPtrDate="日期从"+strPtrDate+"至"+strlastDate;
                AnsiString strData = "小刀儿2007 供应商销售报表";//报表标题
                //将报表标题置于第一行第一列。在此之前，应将报表文件的标题格式设定好。
                Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value",strData.c_str());
                //将制表日期置于表格第二行的右侧。
                Sheet.OlePropertyGet("Cells",2,7).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","供应商");
                iRows++;
                //从数据库中取数据
                DM->ADOQ->First();
                //循环取数
                while(!DM->ADOQ->Eof)
                {
                        //循环取字段的数据放到Excel表对应的行列中
                        for(iCols=1;iCols<10;iCols++)
                        {
                                strRowTemp=DM->ADOQ->Fields->Fields[iCols]->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 TKHsaleForm::closeBtnClick(TObject *Sender)
{
       Close(); 
}
//---------------------------------------------------------------------------

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