spiofs.cpp

供销存软件

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

#include <vcl.h>
#pragma hdrstop

#include "SPiofs.h"
#include "DataModule.h"
#include "login.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TiofsForm *iofsForm;
//---------------------------------------------------------------------------
__fastcall TiofsForm::TiofsForm(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TiofsForm::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;
         ComboBox1->ItemIndex=0;
}
//---------------------------------------------------------------------------


void __fastcall TiofsForm::SpeedButton1Click(TObject *Sender)
{
        int n=Date2->Date-Date1->Date;
        if(n<0){
                ShowMessage("开始日期不得晚于结束日期，请重新查询。");
                Date1->SetFocus();
                return;
        }
       if(LoginForm->Edituser->Text=="admin")
       {    AnsiString str="select * from (";
          if(ComboBox1->ItemIndex==0){
                    str+="select a.入库时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.入库数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.接收仓库 as 仓库";
                    str+=" from (入库信息表 as a inner join 入库商品表 as b on a.编号=b.入库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //入库
          }else if(ComboBox1->ItemIndex==1){
                    str+="select a.出库时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.出库数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.出货仓库 as 仓库";
                    str+=" from (出库信息表 as a inner join 出库商品表 as b on a.编号=b.出库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //销售(出库)
          }else if(ComboBox1->ItemIndex==2){
                    str+="select a.退库时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.退库数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.接收仓库 as 仓库";
                    str+=" from (退库信息表 as a inner join 退库商品表 as b on a.编号=b.退库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //退库
          }else if(ComboBox1->ItemIndex==3){
                     str+="select a.退货时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.退货数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.出货仓库 as 仓库";
                    str+=" from (退货信息表 as a inner join 退货商品表 as b on a.编号=b.退货单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                    //退货
          }else if(ComboBox1->ItemIndex==4){
                     str+="select a.转库时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.转库数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.接收仓库 as 客户,a.出货仓库 as 仓库";
                    str+=" from (转库信息表 as a inner join 转库商品表 as b on a.编号=b.转库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                    //转库
          }else if(ComboBox1->ItemIndex==5){
                     str+="select a.报损时间 as 时间,c.商品名称,c.商品规格,c.单位,round(b.报损数量/2) as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.报损仓库 as 仓库";
                    str+=" from (报损信息表 as a inner join 报损商品表 as b on a.编号=b.报损单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                     //报损
          }
          str+=")";
          str+=" where ";
          str+="时间>=#";
          str+=Date1->Date.DateString();
          str+="# and ";
          str+="时间<=#";
          str+=Date2->Date.DateString();
          str+='#';
          if(CK->ItemIndex!=0){
                  str+=" and 仓库名称='";
                  str+=CK->Text;
                  str+="'";
          }
          DM->ADOQ->SQL->Clear();
          DM->ADOQ->SQL->Add(str);
          DM->ADOQ->Open();
     }else{
          AnsiString str="select * from (";
          if(ComboBox1->ItemIndex==0){
                    str+="select a.入库时间 as 时间,c.商品名称,c.商品规格,c.单位,b.入库数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.接收仓库 as 仓库";
                    str+=" from (入库信息表 as a inner join 入库商品表 as b on a.编号=b.入库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //入库
          }else if(ComboBox1->ItemIndex==1){
                    str+="select a.出库时间 as 时间,c.商品名称,c.商品规格,c.单位,b.出库数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.出货仓库 as 仓库";
                    str+=" from (出库信息表 as a inner join 出库商品表 as b on a.编号=b.出库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //销售(出库)
          }else if(ComboBox1->ItemIndex==2){
                    str+="select a.退库时间 as 时间,c.商品名称,c.商品规格,c.单位,b.退库数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.接收仓库 as 仓库";
                    str+=" from (退库信息表 as a inner join 退库商品表 as b on a.编号=b.退库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                   //退库
          }else if(ComboBox1->ItemIndex==3){
                     str+="select a.退货时间 as 时间,c.商品名称,c.商品规格,c.单位,b.退货数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.客户名称 as 客户,a.出货仓库 as 仓库";
                    str+=" from (退货信息表 as a inner join 退货商品表 as b on a.编号=b.退货单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                    //退货
          }else if(ComboBox1->ItemIndex==4){
                     str+="select a.转库时间 as 时间,c.商品名称,c.商品规格,c.单位,b.转库数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.接收仓库 as 客户,a.出货仓库 as 仓库";
                    str+=" from (转库信息表 as a inner join 转库商品表 as b on a.编号=b.转库单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                    //转库
          }else if(ComboBox1->ItemIndex==5){
                     str+="select a.报损时间 as 时间,c.商品名称,c.商品规格,c.单位,b.报损数量 as 数量,b.价格 as 单价,数量*单价 as 金额,a.编号 as 单号,a.报损仓库 as 仓库";
                    str+=" from (报损信息表 as a inner join 报损商品表 as b on a.编号=b.报损单编号) inner join 商品信息表 as c on b.商品编号=c.编号";
                     //报损
          }
          str+=")";
          str+=" where ";
          str+="时间>=#";
          str+=Date1->Date.DateString();
          str+="# and ";
          str+="时间<=#";
          str+=Date2->Date.DateString();
          str+='#';
          if(CK->ItemIndex!=0){
                  str+=" and 仓库名称='";
                  str+=CK->Text;
                  str+="'";
          }
          //ShowMessage(str);
          DM->ADOQ->SQL->Clear();
          DM->ADOQ->SQL->Add(str);
          DM->ADOQ->Open();
     }
}
//---------------------------------------------------------------------------

void __fastcall TiofsForm::BitBtn1Click(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表，这里是用循环清空到第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 TiofsForm::BitBtn2Click(TObject *Sender)
{
       Close(); 
}
//---------------------------------------------------------------------------

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