dmsemiunit.~pas

华海成本核算辅助系统是基于用友U8企业资源管理系统开发的一套半成品成本统计分析软件。本软件界面美观、初始化简单、操作快捷、功能全面、报表多样等优点

unit DMsemiUnit;

interface

uses
  SysUtils, Classes, DB, ADODB, EXLReportExcelTLB, EXLReportBand, EXLReport,Forms,Windows;

const
  CONTENT_GETALL  = '(cinvcode like ''01%'' or cinvcode like ''02%'' or cinvcode like ''03%'' or cinvcode like ''04%'' or cinvcode like ''13%'' or cinvcode like ''14%'')';
  CONTENT_CHECKIN = '(b.cinvcode like ''01%'' or b.cinvcode like ''02%'' or b.cinvcode like ''03%'' or b.cinvcode like ''04%'' or b.cinvcode like ''13%'' or b.cinvcode like ''14%'')';


type
  TDMSemi = class(TDataModule)
    conDatabase: TADOConnection;
    qryTool: TADOQuery;
    qrySum: TADOQuery;
    dsTool: TDataSource;
    dsSum: TDataSource;
    qryDetail: TADOQuery;
    dsDetail: TDataSource;
    exlrprt: TEXLReport;
    exlrpt2: TEXLReport;
    qryInitData: TADOQuery;
    procedure exlrprtFormatCell(Sender: TObject; Band: TEXLReportBand;
      RowInTemplate, RowInReport, ColumnInReport: Integer;
      Report: _Worksheet; const CellValue: OleVariant);
    procedure exlrpt2FormatCell(Sender: TObject; Band: TEXLReportBand;
      RowInTemplate, RowInReport, ColumnInReport: Integer;
      Report: _Worksheet; const CellValue: OleVariant);
  private
    { Private declarations }
    FDepCode : String ;
    FDepName : string ;
    FDepCodeContext : String ;
        //总折旧金额
    FAllDepreciation : Double ;
    function doExec(aQuery : TADOQuery ; aSql : string ) : Integer ;
    function CalculatePalneCost: Boolean;
  public
    { Public declarations }
    procedure Connect();
    procedure CreateTable();
    procedure DropTable();

    //给未付值的自定义字段附初值
    function setInitData():Boolean;
    function GetResult() : Boolean ;
    //计算出库总金额
    function CalculateCheckOutValue(): Double ;
    //计算入库总金额
    function CalculateCheckInValue(): Double ;
    //计算实际入库总金额
    function CalculateCheckInValue3(): Double ;
    //计算实际入库各自制件总金额
    function CalculateCheckInValue2(): Boolean ;
    //计算总工时
    function CalculateTime (): Double ;
    //计算各自制件总工时
    function CalculateAllTime (): Double ;
    //计算单个成本
    function CalculateSingleCost(aCoefficient : Double): Boolean ;
    //计算材数总成本
    function CalculateCdatumCost():Boolean ;
    //计算单个总成本
    function CalculateSingleAllCost(): Boolean ;
    //计算会计区间内总成本
    function CalculateAllCost():Boolean ;
    //计算单个折旧费用
    function CalculateSingleDepreciation(aCoefficient : Double): Boolean ;
    //计算工资成本
    function CalculateWorkValue(aCoefficient : Double) : Boolean ;
    //取得所有符合条件的自制件
    function CalculateGetAllThing(): Boolean ;
    //取得汇总信息
    function DoSum(aCheckoutValue,aCheckinValue : Double ): Boolean ;
    //取得报表数据
    function getReport():Boolean ;
    //数据回填
    function setData(DateStr:string):Boolean ;

    function doQuery(aQuery : TADOQuery ; aSql : string ) : Integer ;
    property DepCode : string  read FDepCode write FDepCode;
    property DepName : string   read FDepName write FDepName;
    property DepCodeContext : String  read FDepCodeContext write FDepCodeContext;
    property AllDepreciation : Double read FAllDepreciation write FAllDepreciation ;
  end;

var
  DMSemi: TDMSemi;

implementation

uses ConfigUnit, CommonUnit;

{$R *.dfm}

{ TDMSemi }

function TDMSemi.CalculateAllCost: Boolean;
var
  sql : string ;
begin
  sql := 'update aa_aatmp_inventory set callvalue = ccount *csingleallvalue  where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;
end;

function TDMSemi.CalculateAllTime: Double;
var
  sql : string ;
begin
  sql := 'select sum(ccount*cinvdefine12) as value from aa_aatmp_inventory  where ctypeid=''' + FDepCode + '''' ;
  sql := Format(sql,[IniOptions.parambegindate,IniOptions.paramenddate]);
  doQuery(qryTool,sql);
  if qryTool.FieldByName('value').IsNull then
    Result := 0
  else
    Result := qryTool.FieldByName('value').AsFloat ;
end;

function TDMSemi.CalculateCdatumCost: Boolean;
var
  sql : string ;
begin
  sql := 'update aa_aatmp_inventory set cdatumallvalue = ccount * ccostvalue  where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;

end;

function TDMSemi.CalculateCheckInValue: Double;
var
  sql : string ;
begin
{  sql := 'select sum(iPrice) as value from rdrecords where id in '
        +'(select id from rdrecord where cVouchType =''08'''
        +'and cRdCode=''13'' and '
        +'ddate is not null and ddate>=''%s'' and ddate<=''%s'')';}
   sql := 'select  sum(c.iQuantity * b.cinvdefine13 * cinvdefine7) as value from (select * from rdrecords where id in (select '
         +'a.id from rdrecord a  where a.cVouchType =''08'' and a.cRdCode=''13'' '
         +'and a.ddate is not null and a.ddate>=''%s'' and '
         +'a.ddate<=''%s''  and (%s))) c, Inventory b where c.cinvcode=b.cinvcode and '
         + 'b.cInvDefine1=''自制'' and %s ';
  sql := Format(sql,[IniOptions.parambegindate,IniOptions.paramenddate,FDepCodeContext,CONTENT_CHECKIN]);
  doQuery(qryTool,sql);
  if qryTool.FieldByName('value').IsNull then
    Result := 0
  else
    Result := qryTool.FieldByName('value').AsFloat ;
end;

function TDMSemi.CalculateCheckOutValue: Double;
var
  sql : string ;
begin
  sql := 'select sum(iPrice) as value from rdrecords where id in '
        +'(select id from rdrecord a where cVouchType =''11'''
        +'and (cRdCode=''21'' or cRdCode=''22'' or cRdCode=''23'' or cRdCode=''26'') and '
        +'ddate is not null and ddate>=''%s'' and ddate<=''%s'' and (%s))' ;
  sql := Format(sql,[IniOptions.parambegindate,IniOptions.paramenddate,FDepCodeContext]);
  doQuery(qryTool,sql);
  if qryTool.FieldByName('value').IsNull then
    Result := 0
  else
    Result := qryTool.FieldByName('value').AsFloat ;
end;

function TDMSemi.CalculateGetAllThing: Boolean;
var
  sql,sql2 : string ;
begin
  sql := 'insert into aa_aatmp_inventory (ctypeid,cinvcode ,cinvaddcode,cinvname,'
        +'cinvdefine4,cInvDefine5,cInvDefine6,cInvDefine11,cInvDefine12,'
        +'cInvDefine13,cInvDefine14,ccount,cplanvalue,cworkvalue) select ''%s'',cinvcode ,cinvaddcode,'
        +'cinvname,cinvdefine4,cInvDefine5,cInvDefine6,cInvDefine7,'
        +'cInvDefine12,cInvDefine13,cInvDefine14,iquantity,cInvDefine7*cInvDefine13,'
        +'cInvDefine12*cInvDefine14 from (select a.*'
        +',b.iquantity from (select cinvcode ,cinvaddcode,cinvname,cinvstd,'
        +'cinvccode,cinvdefine4,cInvDefine5,cInvDefine6,cInvDefine7,'
        +'cInvDefine12,cInvDefine13,cInvDefine14 from Inventory t where '
        +'t.cInvDefine1=''自制'' and %s  ) a,(select * from rdrecords  '
        +' where id in (select id from rdrecord a where cVouchType =''08'''
        +' and cRdCode=''13'' and ddate is not null and ddate>=''%s'' and '
        +' ddate<=''%s'' and (%s) ) ) b where a.cinvcode= b.cInvCode) c ';
  sql := Format(sql,[FDepCode,CONTENT_GETALL,IniOptions.parambegindate,IniOptions.paramenddate,FDepCodeContext]);
  Result := doExec(qryTool,sql)>0;
end;

function TDMSemi.CalculatePalneCost: Boolean;
begin

end;

function TDMSemi.CalculateSingleAllCost(): Boolean;
var
  sql : string ;
begin
  sql := 'update aa_aatmp_inventory set csingleallvalue =cdeprec + ccostvalue'
        +' + cworkvalue +cinvdefine4+cinvdefine5 + +cinvdefine6 where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;
end;

function TDMSemi.CalculateSingleCost(aCoefficient: Double): Boolean;
var
  sql : string ;
begin
  sql := 'update aa_aatmp_inventory set ccostvalue=cplanvalue* ' + FloatToStr(aCoefficient)+ ' where ctypeid=''' + FDepCode + '''' ; ;
  Result := doExec(qryTool,sql)>0;
end;

{-------------------------------------------------------------------------------
  过程名:    TDMSemi.CalculateSingleDepreciation
             计算单个自制件折旧额
  作者:      Administrator
  日期:      2007.06.23
  参数:      aCoefficient: Double
  返回值:    Boolean
-------------------------------------------------------------------------------}
function TDMSemi.CalculateSingleDepreciation(
  aCoefficient: Double): Boolean;
var
  sql : string ;
begin
  sql := 'update aa_aatmp_inventory set cdeprec=cInvDefine12* ' + FloatToStr(aCoefficient) + ' where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;
  sql := 'update aa_aatmp_inventory set cAlldeprec=cdeprec* ccount where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;
end;

function TDMSemi.CalculateWorkValue(aCoefficient: Double): Boolean;
var
  sql : string ;
begin
//  sql := 'update aa_aatmp_inventory set cworkvalue=cInvDefine12* ' + FloatToStr(aCoefficient) + '  where ctypeid=''' + FDepCode + '''' ;
 // Result := doExec(qryTool,sql)>0;
  sql := 'update aa_aatmp_inventory set callworkvalue=cworkvalue* ccount  where ctypeid=''' + FDepCode + '''' ;
  Result := doExec(qryTool,sql)>0;
end;

procedure TDMSemi.Connect;
begin
  with conDatabase do
  begin
    Close ;
    LoginPrompt := False ;
    ConnectionString := IniOptions.databasepath ;
    Open ;
  end;
end;

procedure TDMSemi.CreateTable;
var
  sql : string ;
begin
  DropTable ;
  sql :='CREATE TABLE [dbo].[AA_Aatmp_Inventory] ( '
        +'  [ctypeid] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,'
        +'	[cInvCode] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,'
        +'	[cInvAddCode] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL , '
        +'	[cInvName] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,'
        +'	[cInvDefine4] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL default ''0'', '