spkcseach.cpp

供销存软件

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

#include <vcl.h>
#pragma hdrstop

#include "SPkcSeach.h"
#include "DataModule.h"
#include "login.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TSPkcForm *SPkcForm;
//---------------------------------------------------------------------------
__fastcall TSPkcForm::TSPkcForm(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------

void __fastcall TSPkcForm::FormCreate(TObject *Sender)
{
         DM->ADOQ->Close();
         DM->ADOQ->SQL->Clear();
         DM->ADOQ->SQL->Add("select 仓库名称 from 仓库信息表");
         DM->ADOQ->Open();
         CK->Items->Add("全部");
         while(!DM->ADOQ->Eof)
         {
                  CK->Items->Add(DM->ADOQ->FieldByName("仓库名称")->AsString.Trim());
                  DM->ADOQ->Next();
         }
         DM->ADOQ->Close();
         CK->ItemIndex=0;

         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;

         DM->ADOQ->SQL->Clear();
         DM->ADOQ->SQL->Add("select 类别名称 from 商品类别表");
         DM->ADOQ->Open();
         LB->Items->Add("全部");
         while(!DM->ADOQ->Eof)
         {
                  LB->Items->Add(DM->ADOQ->FieldByName("类别名称")->AsString.Trim());
                  DM->ADOQ->Next();
         }
         DM->ADOQ->Close();
         LB->ItemIndex=0;
}
//---------------------------------------------------------------------------

void __fastcall TSPkcForm::searchButtonClick(TObject *Sender)
{
    if(LoginForm->Edituser->Text=="admin"){
         DM->ADOQ->Close();
         DM->ADOQ->SQL->Clear();
         AnsiString str="select 商品名称,单位,商品规格,类别,库存数量,零售单价,供应商 from (select 仓库名称,商品名称,单位,商品规格,类别,round(仓库商品表.库存数量/2) as 库存数量,单价 as 零售单价,供货商 as 供应商  from 商品信息表 inner join 仓库商品表 on 商品信息表.编号=仓库商品表.商品编号)";
         if(CK->ItemIndex!=0)
         {
                 str+=" where 仓库名称='";
                 str+=CK->Text;
                 str+="'";
                 if(GYS->ItemIndex!=0){
                         str+=" and 供应商='";
                         str+=GYS->Text;
                         str+="'";
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }else{
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }
         }else{
                 if(GYS->ItemIndex!=0){
                         str+=" where 供应商='";
                         str+=GYS->Text;
                         str+="'";
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }else{
                         if(LB->ItemIndex!=0){
                                 str+=" where 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }
         }
         //ShowMessage(str);
         DM->ADOQ->SQL->Add(str);
         DM->ADOQ->Open();
    }else{
         DM->ADOQ->Close();
         DM->ADOQ->SQL->Clear();
         AnsiString str="select 商品名称,单位,商品规格,类别,库存数量,零售单价,供应商 from (select 仓库名称,商品名称,单位,商品规格,类别,仓库商品表.库存数量,单价 as 零售单价,供货商 as 供应商  from 商品信息表 inner join 仓库商品表 on 商品信息表.编号=仓库商品表.商品编号)";
         if(CK->ItemIndex!=0)
         {
                 str+=" where 仓库名称='";
                 str+=CK->Text;
                 str+="'";
                 if(GYS->ItemIndex!=0){
                         str+=" and 供应商='";
                         str+=GYS->Text;
                         str+="'";
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }else{
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }
         }else{
                 if(GYS->ItemIndex!=0){
                         str+=" where 供应商='";
                         str+=GYS->Text;
                         str+="'";
                         if(LB->ItemIndex!=0){
                                 str+=" and 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }else{
                         if(LB->ItemIndex!=0){
                                 str+=" where 类别='";
                                 str+=LB->Text;
                                 str+="'";
                         }
                 }
         }
         //ShowMessage(str);
         DM->ADOQ->SQL->Add(str);
         DM->ADOQ->Open();
    }
}
//---------------------------------------------------------------------------

void __fastcall TSPkcForm::printBtnClick(TObject *Sender)
{
        Variant Ex,Wb,Sheet,ERange,EBorders;
          try
          {
                //取报表文件CardSend.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 <8; 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 ";
                strData+=CK->Text;
                strData+="库存报表";//报表标题
                //将报表标题置于第一行第一列。在此之前，应将报表文件的标题格式设定好。
                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","供应商");
                iRows++;
                //从数据库中取数据
                DM->ADOQ->First();
                //循环取数
                while(!DM->ADOQ->Eof)
                {
                        //循环取字段的数据放到Excel表对应的行列中
                        for(iCols=1;iCols<8;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 TSPkcForm::closeBtnClick(TObject *Sender)
{
         Close();
}
//---------------------------------------------------------------------------

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