⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 dmsemiunit.~pas

📁 华海成本核算辅助系统是基于用友U8企业资源管理系统开发的一套半成品成本统计分析软件。本软件界面美观、初始化简单、操作快捷、功能全面、报表多样等优点
💻 ~PAS
📖 第 1 页 / 共 2 页
字号:
🔍
12 
        +'	[cInvDefine5] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL default ''0'', '
        +'	[cInvDefine6] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL default ''0'', '
        +'	[cInvDefine11] [nvarchar] (60) NULL default ''0'','
        +'	[cInvDefine12] [int] NULL default ''0'', '
        +'	[cInvDefine13] [float] NULL default ''0'','
        +'	[cInvDefine14] [float] NULL default ''0'','
        +'  [cplanvalue]  [numeric](18, 4)  null default ''0'' ,' //计划价值    净重*单价
        +'  [cdeprec]  [numeric](18, 4)  null default ''0'','     //折旧价值    系数*分钟
        +'  [cAlldeprec]  [numeric](18, 4)  null default ''0'','     //总折旧价值     折旧价值 *数量
        +'  [ccount] [int] null default ''0'','        //数量(产量)
        +'  [ccostvalue]  [numeric](18, 4) null default ''0'','  //成本价
        +'  [csingleallvalue]  [numeric](18, 4) null default ''0'', '  //单个总成本价
        +'  [cdatumallvalue]  [numeric](18, 4) null default ''0'',' //材料总成本  数量* 成本价
        +'  [callvalue]  [numeric](18, 4) null default ''0'',' //成本合计 数量* 单个总成本价
        +'  [cworkvalue]  [numeric](18, 4) null default ''0'', ' //工资价值  分钟*工分
        +'  [cALLworkvalue]  [numeric](18, 4) null default ''0'' ' //总工资价值  工资价值*数量
        +') ON [PRIMARY]';
  doExec(qryTool,sql);
  sql := 'CREATE TABLE [dbo].[AA_Aatmp_sum_Inventory] ( '
        +'[ctypeid] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,'
        +'[cInvName]  [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NOT NULL ,'
     {   +'[cInvDefine11] [numeric](18, 4)  null default ''0'','
        +'[cInvDefine13] [numeric](18, 4)  null default ''0'','
        +'[cInvDefine4]  [numeric](18, 4)  null default ''0'','
        +'[cInvDefine5]  [numeric](18, 4)  null default ''0'','
        +'[cInvDefine6]  [numeric](18, 4)  null default ''0'','
        +'[cdeprec] [numeric](18, 4)  null default ''0'','
        +'[cworkvalue] [numeric](18, 4)  null default ''0'','
        +'[ccostvalue] [numeric](18, 4)  null default ''0'','
        +'[ccount] [numeric](18, 4)  null default ''0'','
        +'[csingleallvalue] [numeric](18, 4)  null default ''0'','
        +'[callvalue] [numeric](18, 4)  null default ''0'','
        +'[cdatumallvalue][numeric](18, 4)  null default ''0'''}
        +'[ccheckoutvalue] [numeric](18, 4)  null default ''0'','
        +'[ccheckinvalue][numeric](18, 4)  null default ''0'' ,'
        +'[ccheckinvalue2][numeric](18, 4)  null default ''0'' '      //实际半成品入库总金额
        +') ON [PRIMARY]';
  doExec(qryTool,sql);
end;


function TDMSemi.doExec(aQuery: TADOQuery; aSql: string): Integer ;
begin
  try
    with aQuery do
    begin
      Close ;
      SQL.Text := aSql ;
    end;
    Result := aQuery.ExecSQL ;
  except
     raise EDBException.Create('更新出错' + aSql);
  end;
end;

function TDMSemi.doQuery(aQuery: TADOQuery; aSql: string): Integer;
begin
  try
    with aQuery do
    begin
      Close ;
      SQL.Text := aSql ;
      Open ;
    end;
    Result := aQuery.RecordCount ;
  except
     raise EDBException.Create('查询出错');
  end;
end;

function TDMSemi.DoSum(aCheckoutValue,aCheckinValue : Double): Boolean;
var
  sql : string ;
begin
  {sql := 'insert into AA_Aatmp_sum_Inventory '
        +'select ''%s'',''%s'',sum(cast(cInvDefine11 as float)) cInvDefine11,sum(cInvDefine13) cInvDefine13,'
        +'sum( cast(cInvDefine4 as float)) cInvDefine4,sum(cast(cInvDefine5 as '
        +'float)) cInvDefine5,sum( cast(cInvDefine6 as float)) cInvDefine6,sum(cdeprec) cdeprec,sum(cworkvalue) cworkvalue, '
        +'sum(ccostvalue) ccostvalue,sum(ccount) ccount,sum(csingleallvalue) '
        +'csingleallvalue,SUM(callvalue) callvalue ,SUM(cdatumallvalue)'
        +' cdatumallvalue from AA_Aatmp_Inventory where ctypeid=''%s''';}
  sql := 'insert into AA_Aatmp_sum_Inventory (ctypeid,cInvName,ccheckoutvalue,ccheckinvalue)'
        +' values(''%s'',''%s'',''%s'',''%s'')';
  sql := format(sql,[FDepCode,FDepName,FloatToStr(aCheckoutValue),FloatToStr(aCheckinValue)]);
  Result := doExec(qrySum,sql) >0 ;
end;

procedure TDMSemi.DropTable;
var
  sql : string ;
begin
  sql := 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[AA_Aatmp_Inventory]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '
         + 'drop table [dbo].[AA_Aatmp_Inventory] ' ;
  doExec(qryTool,sql);

  sql := 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[AA_Aatmp_sum_Inventory]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '
         + 'drop table [dbo].[AA_Aatmp_sum_Inventory] ' ;
  doExec(qryTool,sql);
end;

function TDMSemi.getReport: Boolean;
var
  sql : string ;
begin
  Result := False ;
  sql := 'select * from AA_Aatmp_sum_Inventory ' ;
  if not doQuery(qrySum,sql)>0 then
    Exit ;
  sql := 'select *,cast(cinvdefine4 as float) cinvdefine4cast,cast(cinvdefine5 as float) cinvdefine5cast,cast(cinvdefine6 as float) cinvdefine6cast, cast(cinvdefine11 as float) cinvdefine11cast from aa_aatmp_inventory where ctypeid =:ctypeid order by cinvcode' ;
  if not doQuery(qryDetail,sql)>0 then
    Exit ;
  Result := True ;
end;

function TDMSemi.GetResult(): Boolean ;
var
  sql : string ;
begin
  sql := 'select *,cast(cinvdefine4 as float) cinvdefine4cast,cast(cinvdefine5 as float) cinvdefine5cast,cast(cinvdefine6 as float) cinvdefine6cast, cast(cinvdefine11 as float) cinvdefine11cast from aa_aatmp_inventory order by cinvcode';
  Result := doQuery(qryTool,sql)>0;
end;

procedure TDMSemi.exlrprtFormatCell(Sender: TObject; Band: TEXLReportBand;
  RowInTemplate, RowInReport, ColumnInReport: Integer; Report: _Worksheet;
  const CellValue: OleVariant);
begin
  Report.Range['A3','A3'].Value := '会计区间：' + IniOptions.parambegindate
                             +' -- ' + IniOptions.paramenddate ;
  Report.Range['C3','C3'].Value := '材料出库金额：' + floattostr(IniOptions.paramCheckOut);
  Report.Range['E3','E3'].Value := '半成品入库金额：' + floattostr(IniOptions.paramCheckIn2 );
  Report.Range['G3','G3'].Value := '总折旧金额：' + floattostr(FAllDepreciation );
end;

function TDMSemi.CalculateCheckInValue2: boolean;
var
  sql : string ;
  theValue:Double;
begin
    sql := 'update AA_Aatmp_sum_Inventory set ccheckinvalue2 ='
           +'(select cast(sum(cdatumallvalue + cAlldeprec + cALLworkvalue + (cast(cInvDefine4 as float)*ccount)+(cast(cInvDefine5 as float)*ccount)+(cast(cInvDefine6 as float)*ccount)) as numeric(18,2)) '
           +' from aa_aatmp_inventory where ctypeid=''' + FDepCode  +''') where ctypeid=''' + FDepCode + '''';
    Result := doExec(qrySum,sql)>0;
end;

function TDMSemi.CalculateCheckInValue3: Double;
var
  sql:string;
begin
  sql := 'select  sum(ccheckinvalue2) as value from AA_Aatmp_sum_Inventory';
  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.CalculateTime: Double;
var
  sql : string ;
begin
  sql := 'select  sum(c.iQuantity * b.cinvdefine12) 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''  )) c, Inventory b where c.cinvcode=b.cinvcode and '
         + 'b.cInvDefine1=''自制'' and %s '; ;
  sql := Format(sql,[IniOptions.parambegindate,IniOptions.paramenddate,CONTENT_CHECKIN]);
  doQuery(qryTool,sql);
  if qryTool.FieldByName('value').IsNull then
    Result := 0
  else
    Result := qryTool.FieldByName('value').AsFloat ;
end;

procedure TDMSemi.exlrpt2FormatCell(Sender: TObject; Band: TEXLReportBand;
  RowInTemplate, RowInReport, ColumnInReport: Integer; Report: _Worksheet;
  const CellValue: OleVariant);
begin
  Report.Range['A3','A3'].Value := '会计区间：' + IniOptions.parambegindate
                             +' -- ' + IniOptions.paramenddate ;
  Report.Range['C3','C3'].Value := '材料出库金额：' + floattostr(IniOptions.paramCheckOut);
  Report.Range['E3','E3'].Value := '半成品入库金额：' + floattostr(IniOptions.paramCheckIn2 );
  Report.Range['G3','G3'].Value := '总折旧金额：' + floattostr(FAllDepreciation );
end;

function TDMSemi.setData(DateStr:string): Boolean;
var
  sqlStr:string;
  setDataQuery:TADOQuery;
begin
  setDataQuery:=TADOQuery.Create(Application);
  setDataQuery.Connection:= conDatabase;
  conDatabase.BeginTrans;
  try
    with setDataQuery do
    begin
      //回填含税单价
      sqlStr:='update b set b.iUnitCost = a.csingleallvalue from rdrecords b LEFT OUTER JOIN aa_aatmp_inventory a on a.cInvCode=b.cInvCode LEFT OUTER JOIN rdrecord c ON b.ID=c.ID  where c.cVouchType=''08'' and '+CONTENT_CHECKIN +DateStr;
      Close ;
      SQL.Text := sqlStr ;
      ExecSQL ;

      //回填含税金额
      sqlStr:='update b set b.iPrice = (b.iUnitCost*b.iQuantity) from rdrecords b LEFT OUTER JOIN aa_aatmp_inventory a on a.cInvCode=b.cInvCode LEFT OUTER JOIN rdrecord c ON b.ID=c.ID  where c.cVouchType=''08'' and '+CONTENT_CHECKIN +DateStr;
      Close ;
      SQL.Text := sqlStr ;
      ExecSQL ;

      //回填税额
      sqlStr:='update b set b.iTax = b.iPrice*(c.iTaxRate/100)  from rdrecords b LEFT OUTER JOIN aa_aatmp_inventory a on a.cInvCode=b.cInvCode LEFT OUTER JOIN rdrecord c ON b.ID=c.ID  where c.cVouchType=''08'' and '+CONTENT_CHECKIN +DateStr;
      Close ;
      SQL.Text := sqlStr ;
      ExecSQL ;
    end;
    conDatabase.CommitTrans;
    setDataQuery.close;
    setDataQuery.Destroy;
    result:=True;
  except
      conDatabase.RollbackTrans;
      setDataQuery.close;
      setDataQuery.Destroy;
      result:=false;
  end;
end;

function TDMSemi.setInitData():Boolean;
begin
  //初始化数据库
  if  conDatabase.Connected =false then
  begin
    try
     Connect;
    except
      result:=false;
    end;
  end;
  with qryInitData do
  begin
    sql.Text:='update Inventory set cinvdefine4=''0'' where cinvdefine1=''自制'' and (cinvdefine4='''' or cinvdefine4 is null)';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine5=''0'' where cinvdefine1=''自制'' and (cinvdefine5='''' or cinvdefine5 is null)';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine6=''0'' where cinvdefine1=''自制'' and (cinvdefine6='''' or cinvdefine6 is null)';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine7=''0'' where cinvdefine1=''自制'' and (cinvdefine7='''' or cinvdefine7 is null)';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine12=0 where cinvdefine1=''自制'' and cinvdefine12 is null';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine13=0.0 where cinvdefine1=''自制'' and cinvdefine13 is null';
    ExecSQL;
    sql.Text:='update Inventory set cinvdefine14=0.0 where cinvdefine1=''自制'' and cinvdefine14 is null';
    ExecSQL;
  end;
  result:=true;
end;

end.
12

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -