mpsdatequery.cpp

来自「科思ERP部分源码及控件」· C++ 代码 · 共 377 行

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

#include <vcl.h>
#pragma hdrstop

#include "MpsDateQuery.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "DateEdit"
#pragma link "SDComboBox"
#pragma link "SDEdit"
#pragma resource "*.dfm"
TfrmMpsDateQuery *frmMpsDateQuery;
//---------------------------------------------------------------------------
__fastcall TfrmMpsDateQuery::TfrmMpsDateQuery(TComponent* Owner,HWND chWnd,AnsiString MidCode,AnsiString WhereStr)
        : TForm(Owner)
{
     g_ClientHandle=chWnd;
     l_ItemsCtrls=new TKSSet;
     l_ItemsCtrls->AddField("CtrFlag");
     l_ItemsCtrls->AddField("Ctrpara");
     l_ItemsCtrls->AddField("CtrWherField");
     l_ItemsCtrls->AddField("CtrDispField");
     l_ItemsCtrls->AddField("CtrGridCaption");
     l_ItemsCtrls->KeyField="CtrFlag";

     l_ResCtrls=new TKSSet;
     l_ResCtrls->AddField("CtrFlag");
     l_ResCtrls->AddField("CtrNo");
     l_ResCtrls->KeyField="CtrFlag";

     /*l_ResDate=new TKSSet;
     l_ResDate->AddField("CtrStat");
     l_ResDate->AddField("CtrEnd");
     l_ResDate->KeyField="CtrField"; */

     TKSSet *l_GlobeVars=new TKSSet;
     l_GlobeVars->LoadFromString(gGlobeVarsStr);
     InitSysGlobeVar(l_GlobeVars);
     delete l_GlobeVars;
     InitEditControl();
     sbBar->Panels->Items[0]->Text="操作员:"+g_sdUserCode;
     sbBar->Panels->Items[1]->Text=g_sdCompanyName;
     sbBar->Panels->Items[2]->Text=g_sdCurDate;
     AddGroupItem(ed_GoodsBool,ed_goods,"MpsGoods","MpsGoods,GOODSNAME","物料编码\t物料名称");
     l_DefField="select * from KSVW_Mps";
     l_DefDisp ="\t物料编码\t物料名称\t批量\t提前期\t当前库存,安全库存\t已分配量\t需求日期" ;
     s_DateField =" MpsdRDate ";
     FillterColumData();
}
//---------------------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::FormClose(TObject *Sender,
      TCloseAction &Action)
{
   CloseClientForm(g_ClientHandle,(int)this);
   Action=caFree;
   delete l_ItemsCtrls;
   delete l_ResCtrls;
}
//---------------------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::InitEditControl()
{
}
//-----------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::AddGroupItem(TControl *flag,TControl *para,AnsiString WherField,AnsiString DispField,AnsiString GridCaption)
{
    if(UpperCase((AnsiString)(flag)->ClassName())!="TSDEDIT")
      return ;
    dynamic_cast<TSDEdit *>(flag)->OnClick=OnFlagClick;
    dynamic_cast<TSDEdit *>(flag)->OnKeyPress=OnFlagPress;
    l_ItemsCtrls->AddNew();
    l_ItemsCtrls->FieldValues["CtrFlag"]=(int)flag;
    l_ItemsCtrls->FieldValues["Ctrpara"]=(int)para;
    l_ItemsCtrls->FieldValues["CtrWherField"]=WherField;
    l_ItemsCtrls->FieldValues["CtrDispField"]=DispField;
    l_ItemsCtrls->FieldValues["CtrGridCaption"]=GridCaption;
    l_ItemsCtrls->Update();
}
//-----------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::OnFlagClick(TObject *Sender)
{
  int l_GroupNo=0;
  l_ResCtrls->Last();
  if(l_ResCtrls->RecordCount>0)
    l_GroupNo=l_ResCtrls->FieldValues["CtrNo"].ToInt();
  if(l_ResCtrls->LocateByKey((int)Sender))
  {
    if(l_ResCtrls->FieldValues["CtrNo"].ToInt()!=l_GroupNo)
       l_GroupNo++;
    l_ResCtrls->Delete();
    l_ResCtrls->AddNew();
    l_ResCtrls->FieldValues["CtrFlag"]=(int)Sender;
    l_ResCtrls->FieldValues["CtrNo"]=l_GroupNo;
    l_ResCtrls->Update();
  }
  else
  {
    l_GroupNo++;
    l_ResCtrls->AddNew();
    l_ResCtrls->FieldValues["CtrFlag"]=(int)Sender;
    l_ResCtrls->FieldValues["CtrNo"]=l_GroupNo;
    l_ResCtrls->Update();
  }
  dynamic_cast<TSDEdit *>(Sender)->Text=l_GroupNo;
}
//-----------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::OnFlagPress(TObject *Sender, char &Key)
{
  if(Key>'9' || Key<'0')
    Key=0;
}
//---------------------------------------------------------------------------
AnsiString  __fastcall TfrmMpsDateQuery::MakeSQL()
{
   AnsiString sqlstr,CaptionStr,temstr,FieldStr,whereStr,groupStr;
   sqlstr="",CaptionStr="",FieldStr="",whereStr="",groupStr="";
   int index,col=0,row;
   TControl *ctl;
   AnsiString  TControlName;

   l_ResCtrls->First();
   while(!l_ResCtrls->Eof)
   {
     l_ItemsCtrls->LocateByKey(l_ResCtrls->FieldValues["CtrFlag"].ToInt());
     ctl=(TControl*)l_ItemsCtrls->FieldValues["Ctrpara"].ToInt();
     TControlName = UpperCase((AnsiString)(ctl)->ClassName());
     if (TControlName=="TSDEDIT")
     {
       if(!dynamic_cast<TSDEdit *>(ctl)->Text.IsEmpty())
         whereStr+=l_ItemsCtrls->FieldValues["CtrWherField"]+" like '"+dynamic_cast<TSDEdit *>(ctl)->Text+"'  and " ;
      }
     if (TControlName=="TSDCOMBOBOX")
     {
       if(!dynamic_cast<TSDEdit *>(ctl)->Text.IsEmpty())
         whereStr+=l_ItemsCtrls->FieldValues["CtrWherField"]+" like '"+dynamic_cast<TSDComboBox *>(ctl)->ItemData[0]+"'  and " ;
      }
       FieldStr+=l_ItemsCtrls->FieldValues["CtrDispField"]+",";
       CaptionStr+=l_ItemsCtrls->FieldValues["CtrGridCaption"];
       l_ResCtrls->Next();
   }

   AnsiString TDateStaValuer,TDateEndValuer;
   TDateStaValuer =AnsiString(dtDateBg->CDate);
   TDateEndValuer =AnsiString(dtDateEn->CDate);

   if (!s_DateField.IsEmpty())
   {
     if (TDateStaValuer.IsEmpty())
     {
       if (!TDateEndValuer .IsEmpty())
       { whereStr+=s_DateField+" > '"+TDateEndValuer+"'   and " ;}
     }
     if (TDateEndValuer.IsEmpty())
     {
       if (!TDateStaValuer.IsEmpty())
        {whereStr+=s_DateField+" < '"+TDateStaValuer+"'   and " ; }
     }
     if (!TDateEndValuer.IsEmpty() && !TDateStaValuer.IsEmpty())
        {whereStr+=s_DateField+" between '"+TDateStaValuer+"' and '"+TDateEndValuer+"'  and "  ; }
    }
   CaptionStr+=l_SumDisp;
   index=CaptionStr.Pos("\t");
   row=0;
   while(index!=0)
   {
     if(row==0)
       QueryGrid->Columns->Items[row]->TitleString="";
     else
       QueryGrid->Columns->Items[row]->TitleString=CaptionStr.SubString(1,index-1);
     CaptionStr.Delete(1,index);
     index=CaptionStr.Pos("\t");
     col++;
     row++;
     QueryGrid->ColCount=col+1;
   }
   QueryGrid->Columns->Items[row]->TitleString=CaptionStr;
   if(l_ResCtrls->RecordCount>0)    // 大于零分组
   {
     sqlstr=" Select "+FieldStr+l_SumField;
     if(!whereStr.IsEmpty())
        sqlstr+=" where "+whereStr.Delete(whereStr.Length()-5,5);
     sqlstr+=" group by "+FieldStr.Delete(FieldStr.Length(),1);
   }
   else
   {
     sqlstr=" Select "+l_DefField;//+l_SumField;
     if(!whereStr.IsEmpty())
        sqlstr+=" where "+whereStr.Delete(whereStr.Length()-5,5);
   }
   return sqlstr;
}
//---------------------------------------------------------------------------
 void __fastcall TfrmMpsDateQuery::FillGridWithData()//无分组
{
  TComResultSet *SelectQuery=new TComResultSet(Handle,g_ClientHandle);;
  QueryGrid->RowCount=1;
  AnsiString ItemStr="";
  AnsiString l_RDate,l_RGoodsCode,l_goods1 ;
  try
  {
    SelectQuery->Open(MakeSQL(),"");
    SelectQuery->MoveFirst();
    while( SelectQuery->Eof == 0)
    {
      ItemStr="";
      //l_RDate = AnsiString(SelectQuery->FieldByName("MpsdRDate");
      //l_RGoodsCode = AnsiString(SelectQuery->FieldByName("MpsGoods");

      l_RGoodsCode = SelectQuery->FieldByName("MRPGOODS");
      l_RDate = SelectQuery->FieldByName("MRPDRDATE");
       // for(int i=0;i<=SelectQuery->FieldCount-4;i++)
        //ItemStr+="\t"+AnsiString(SelectQuery->FieldByIndex(i)) ;
      if (l_RGoodsCode!=l_goods1)
      {
      l_goods1 = l_RGoodsCode;
      ItemStr="\t"+l_RGoodsCode+
          "\t"+SelectQuery->FieldByName("GOODSNAME")+
          "\t"+AnsiString(SelectQuery->FieldByName("MpsEOQ"))+
          "\t"+AnsiString(SelectQuery->FieldByName("MpsLT"))+
          "\t"+AnsiString(SelectQuery->FieldByName("MpsCIQ"))+
          "\t"+AnsiString(SelectQuery->FieldByName("MpsSIQ"))+
          "\t"+AnsiString(SelectQuery->FieldByName("MpsDIQ"))+
          ItemStr= ItemStr+"\t"+TitleColumn(l_RGoodsCode,l_RDate);
          QueryGrid->AddItem(ItemStr);
      }
      else
       {
        ItemStr="\t"+AnsiString(" ")+
               "\t"+AnsiString(" ")+
               "\t"+AnsiString(" ")+
               "\t"+AnsiString(" ")+
               "\t"+AnsiString(" ")+
               "\t"+AnsiString(" ")+
               "\t"+l_RDate;
          ItemStr= ItemStr+"\t"+TitleColumn(l_RGoodsCode,l_RDate);
          QueryGrid->AddItem(ItemStr);
        }
      SelectQuery->MoveNext();
    }
    SelectQuery->Close();
  }
  __finally
  {
    delete SelectQuery;
  }
}
//------------------------------------------------
AnsiString __fastcall TfrmMpsDateQuery::TitleColumn(AnsiString f_goods,AnsiString f_date)
 {
  TComResultSet *RsQueryQty;
 RsQueryQty=new  TComResultSet(Handle,g_ClientHandle);
 AnsiString l_str="";
 AnsiString  ItemStr="",m_SQL;
 AnsiString l_sqlgross,l_sqlpoh,l_sqlmacc,l_sqlclean,l_sqlSoh,l_sqlTake;
 m_SQL="select mpsdrqty from sdmpsd where mpsdgoods ='"+ f_goods+"' and mpsdrdate = '"+f_date+"'" ;

 int l_column=0;
   if(CB_Gross->Checked)
   {
      l_column++;
     l_sqlgross=m_SQL+ " and mpsdFrom =1" ;
     RsQueryQty->Open(l_sqlgross,"");
     l_str= l_str+ AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     RsQueryQty->Close();
   }
   if(CB_Poh->Checked)
   {
    AnsiString l_qty;
     l_sqlpoh=m_SQL+ " and mpsdFrom =2";
     RsQueryQty->Open(l_sqlpoh,"");
     l_qty= AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     l_str= l_str+"\t"+l_qty;
     RsQueryQty->Close();
     l_column++ ;
     }
   if(CB_Macc->Checked)
   {
     l_sqlmacc=m_SQL+ " and mpsdFrom =3";
     RsQueryQty->Open(l_sqlmacc,"");
     l_str= l_str+"\t"+AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     RsQueryQty->Close();
     l_column++;
    }
    if(CB_Clean->Checked)
   {
     l_sqlclean=m_SQL+ " and mpsdFrom =4";
     RsQueryQty->Open(l_sqlclean,"");
     l_str= l_str+"\t"+AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     l_column++;
     RsQueryQty->Close();
    }
    if(CB_Soh->Checked)
   {
     l_sqlSoh=m_SQL+ " and mpsdFrom =5";
     RsQueryQty->Open(l_sqlclean,"");
     l_str= l_str+"\t"+AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     l_column++;
     RsQueryQty->Close();
    }
     if(CB_Take->Checked)
   {

     l_sqlTake=m_SQL+ " and mpsdFrom =6";
     RsQueryQty->Open(l_sqlclean,"");
     l_str= l_str+"\t"+AnsiString(RsQueryQty->FieldByName("MPSDRQTY"));
     l_column++;
     RsQueryQty->Close();
    }
    return  l_str;
   }
//---------------------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::btFindClick(TObject *Sender)
{
  FillGridWithData();
  FillterColumData();
}
//---------------------------------------------------------------------------
void __fastcall TfrmMpsDateQuery::btRestClick(TObject *Sender)
{
  l_ResCtrls->Clear();
}
//---------------------------------------------------------------------------
 void __fastcall TfrmMpsDateQuery::FillterColumData()
{
    int l_column=0,l_Tcolumn;
  QueryGrid->Columns->Items[1]->TitleString ="物料编码";
  QueryGrid->Columns->Items[2]->TitleString ="物料名称";
  QueryGrid->Columns->Items[3]->TitleString ="批量";
  QueryGrid->Columns->Items[4]->TitleString ="提前期";
  QueryGrid->Columns->Items[5]->TitleString ="当前库存";
  QueryGrid->Columns->Items[6]->TitleString ="安全库存";
  QueryGrid->Columns->Items[7]->TitleString ="已分配量";
  QueryGrid->Columns->Items[8]->TitleString ="需求日期";
  QueryGrid->ColCount= 15;

   l_Tcolumn = 8;
   if(CB_Gross->Checked)
   {
      l_column++;
     // QueryGrid->ColCount= 11+l_column;
      QueryGrid->Columns->Items[l_Tcolumn+1]->TitleString ="毛需求";
   }
   if(CB_Poh->Checked)
   {
     l_column++ ;
    // QueryGrid->ColCount= 11+l_column;
     QueryGrid->Columns->Items[l_Tcolumn+l_column]->TitleString ="计划入库";
     }
   if(CB_Macc->Checked)
   {
     l_column++;
    // QueryGrid->ColCount= 11+l_column;
     QueryGrid->Columns->Items[l_Tcolumn+l_column]->TitleString ="计划库存";
    }
    if(CB_Clean->Checked)
   {
     l_column++;
    // QueryGrid->ColCount= 11+l_column;
     QueryGrid->Columns->Items[l_Tcolumn+l_column]->TitleString ="净需求";
    }
    if(CB_Soh->Checked)
   {
     l_column++;
    // QueryGrid->ColCount= 10+l_column;
     QueryGrid->Columns->Items[l_Tcolumn+l_column]->TitleString ="计划订单";
    }
     if(CB_Take->Checked)
   {
     l_column++;
     //QueryGrid->ColCount= 10+l_column;
     QueryGrid->Columns->Items[l_Tcolumn+l_column]->TitleString ="计划下达";
    }
    QueryGrid->ColCount= l_Tcolumn+l_column+1;
}