//利润报表 Ext.namespace('DsTruck'); DsTruck.RptProfitIndex = function (config) { Ext.applyIf(this, config); this.initUIComponents(); window.DsTruck.RptProfitIndex.superclass.constructor.call(this); }; Ext.extend(DsTruck.RptProfitIndex, Ext.Panel, { PageSize: 5000, OprationStatus: null, //仅当弹出界面时使用 SelectedRecord: null, initUIComponents: function () { //定义数据集 this.storeList = Ext.create('Ext.data.Store', { pageSize: this.PageSize, idProperty: 'Id', groupField: ' HTH ', fields: [ { name: 'gid', type: 'string' }, { name: 'ContractNo', type: 'string' }, { name: 'HTH', type: 'string' }, { name: 'CustomerName', type: 'string' }, { name: 'FeeType', type: 'string' }, { name: 'FeeName', type: 'string' }, { name: 'ys_ref', type: 'numeric' }, { name: 'yf_ref', type: 'numeric' }, { name: 'y_ref', type: 'numeric' }, { name: 'ss_ref', type: 'numeric' }, { name: 'sf_ref', type: 'numeric' }, { name: 's_ref', type: 'numeric' }, { name: 'FeeDate', type: 'string' }, { name: 'ArrivalDate', type: 'string' }, { name: 'Remark', type: 'string' }, { name: 'ZJY', type: 'string' } ], remoteSort: true, proxy: { type: 'ajax', url: '/Import/RptProfit/GetDataList', reader: { id: 'gid', root: 'data', totalProperty: 'totalCount' } } }); Ext.define('Tradermb', { extend: 'Ext.data.Model', idProperty: 'gid', fields: [ { name: 'gid', type: 'string' }, { name: 'name', type: 'string' }, { name: 'codename', type: 'string' } ] }); //#region 表参照 this.storeCustomer = Ext.create('DsExt.ux.RefTableStore', { model: 'Tradermb', proxy: { url: '/CommMng/BasicDataRef/GetTrader' } }); this.storeCustomer.load({ params: { condition: " 1=1 "} }); this.comboxCustomer = Ext.create('DsExt.ux.RefTableCombox', { fieldLabel: '结算对象', store: this.storeCustomer, name: 'CustomerName', valueField: 'name', displayField: 'codename' }); this.storeBuyer = Ext.create('DsExt.ux.RefTableStore', { model: 'Tradermb', proxy: { url: '/CommMng/BasicDataRef/GetTrader' } }); this.storeBuyer.load({ params: { condition: " isagentcn=1 "} }); this.comboxBuyer = Ext.create('DsExt.ux.RefTableCombox', { fieldLabel: '客户', store: this.storeBuyer, name: 'Buyer', valueField: 'name', displayField: 'codename' }); this.storeSeller = Ext.create('DsExt.ux.RefTableStore', { model: 'Tradermb', proxy: { url: '/CommMng/BasicDataRef/GetTrader' } }); this.storeSeller.load({ params: { condition: " isagent=1 "} }); this.comboxSeller = Ext.create('DsExt.ux.RefTableCombox', { fieldLabel: '贸易商', store: this.storeSeller, name: 'Seller', valueField: 'name', displayField: 'codename' }); // The data for all states var Locked = [ { "id": '0', "name": "未锁" }, { "id": '1', "name": "已锁定"}]; this.StoreLocked = Ext.create('Ext.data.Store', { model: 'CMBmb', data: Locked }); this.comboxLocked = Ext.create('DsExt.ux.RefTableCombox', { fieldLabel: '是否财务锁定', store: this.StoreLocked, name: 'ContractStatus', valueField: 'id', displayField: 'name' }); this.storeFeeName = Ext.create('DsExt.ux.RefTableStore', { model: 'DsTruckMng.ux.FeeTypeRefModel', proxy: { url: '/CommMng/BasicDataRef/GetFeeTypeRefList' } }); this.storeFeeName.load({ params: { condition: ""} }); this.comboxFeeName = Ext.create('Ext.ux.form.field.BoxSelect', { fieldLabel: '费用名称', //renderTo: 'basicBoxselect',height:60,pinList: false, //autoHeight:true,width: 500,stacked: true, autosize: true, bodyPadding: 7, flex: 3, labelWidth: 90, store: this.storeFeeName, queryMode: 'local', triggerOnClick: false, valueField: 'Name', displayField: 'CodeAndName' }); this.storeFeeGroupName = Ext.create('DsExt.ux.RefTableStore', { model: 'DsTruckMng.ux.FeeTypeRefModel', proxy: { url: '/CommMng/BasicDataRef/GetFeeGroupRefList' } }); this.storeFeeGroupName.load({ params: { condition: ""} }); this.comboxFeeGroupName = Ext.create('Ext.ux.form.field.BoxSelect', { fieldLabel: '费用分组', //renderTo: 'basicBoxselect',height:60,pinList: false, //autoHeight:true,width: 500,stacked: true, autosize: true, bodyPadding: 7, flex: 2, labelWidth: 90, store: this.storeFeeGroupName, queryMode: 'local', triggerOnClick: false, valueField: 'Name', displayField: 'CodeAndName' }); this.storeport = Ext.create('DsExt.ux.RefEnumStore', {}); this.storeport.load({ params: { enumTypeId: 10} }); this.comboxPort = Ext.create('Ext.ux.form.field.BoxSelect', { fieldLabel: '目的港', //renderTo: 'basicBoxselect', //autoHeight:true, autosize: true, bodyPadding: 1, flex: 2, //height:60, width: 80, labelWidth: 90, store: this.storeport, queryMode: 'local', //stacked: true, //pinList: false, triggerOnClick: false, valueField: 'EnumValueId', displayField: 'EnumValueName' //,value: ['TX', 'CA'] }); //#endregion //定义grid var _this = this; this.Pagenum = Ext.create('Ext.form.field.Number', { name: 'bottles', fieldLabel: '每页记录数', labelAlign: 'right', value: this.PageSize, maxValue: 100000, width: 180, minValue: 0, listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }); Ext.grid.RowNumberer = Ext.extend(Ext.grid.RowNumberer, { width: 30 }); this.column = [ { //sortable: true, dataIndex: 'gid', id: '', header: 'gid', hidden: true, width: 80 }, { //sortable: true, dataIndex: 'ContractNo', id: '', header: 'ContractNo', hidden: true, width: 80 }, { //sortable: true, dataIndex: 'HTH', id: '', header: '合同号', width: 120 }, { //sortable: true, dataIndex: 'CustomerName', id: '', header: '结算对象', width: 150 }, { //sortable: true, dataIndex: 'FeeType', id: '', header: 'feetype', hidden: true, width: 80 }, { //sortable: true, dataIndex: 'FeeName', id: '', header: '费用名称', width: 80 }, { //sortable: true, dataIndex: 'ys_ref', id: '', header: '应收', align: 'right', width: 80 }, { //sortable: true, dataIndex: 'yf_ref', id: '', header: '应付',align:'right', width: 80 }, { //sortable: true, dataIndex: 'y_ref', id: '', header: '利润', align: 'right', width: 80 }, { //sortable: true, dataIndex: 'ss_ref', id: '', header: '实收', align: 'right', width: 80 }, { //sortable: true, dataIndex: 'sf_ref', id: '', header: '实付', align: 'right', width: 80 }, { //sortable: true, dataIndex: 's_ref', id: '', header: '实收总计', align: 'right', width: 80 }, { //sortable: true, dataIndex: 'FeeDate', id: '', header: '费用时间', width: 140 }, { //sortable: true, dataIndex: 'Remark', id: '', header: '备注', width: 140 }, { //sortable: true, dataIndex: 'ZJY', id: '', header: '总结余', width: 70 }]; //定义Grid this.gridList = new Ext.grid.GridPanel({ store: this.storeList, enableHdMenu: false, region: 'center', loadMask: { msg: "数据加载中，请稍等..." }, trackMouseOver: true, disableSelection: false, stripeRows: true, viewConfig: { enableTextSelection: true }, columns: [new Ext.grid.RowNumberer()], // paging bar on the bottom bbar: [Ext.create('Ext.PagingToolbar', { store: this.storeList, displayInfo: true, displayMsg: '当前显示 {0} - {1}条记录 /共 {2}条记录', emptyMsg: "没有数据" }), this.Pagenum] }); /////////////以下部分为获取存储的gridpanel显示样式 if (GID == '') { alert('未检测到用户信息，请重新登陆') } else { this.column = DsTruck.GetGridPanel(GID, "RPTProfit", this.column); this.column.unshift(new Ext.grid.RowNumberer()); this.gridList.reconfigure(this.storeList, this.column); } //////////////////////////// this.gridList.addListener('itemdblclick', function (dataview, record, item, index, e, b) { // alert('0....' + dataview.toString() + ',' + record.toString() + ',' + item.toString() + ',' + index.toString() + ',' + e.toString() + ',' + b.toString()); this.SelectedRecord = record; this.OprationStatus = 'edit'; DsOpenEditWin('/Import/XXH/Edit', '', 650, 1150); }, this); //#region formSearch //#region formSearch枚举参照相关 //#endregion this.formSearch = Ext.widget('form', { frame: true, region: 'center', bodyPadding: 5, fieldDefaults: { margins: '2 2 2 2', labelAlign: 'right', flex: 1, labelWidth: 90, msgTarget: 'qtip' }, items: [ {//fieldset 1 xtype: 'container', defaultType: 'textfield', layout: 'anchor', defaults: { anchor: '100%' }, items: [{ xtype: 'container', layout: 'hbox', defaultType: 'textfield', items: [{ fieldLabel: '合同号', name: 'HTH', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, this.comboxCustomer, { fieldLabel: '从..到港日', format: 'Y-m-d', xtype: 'datefield', name: 'EnterDate_min', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, { fieldLabel: '至..到港日', format: 'Y-m-d', xtype: 'datefield', name: 'EnterDate_max', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, this.comboxBuyer ] } ]//end items(fieldset 1) }, //end fieldset 1 {//fieldset2 xtype: 'container', defaultType: 'textfield', layout: 'anchor', defaults: { anchor: '100%' }, items: [{ xtype: 'container', layout: 'hbox', defaultType: 'textfield', items: [ { fieldLabel: '从..接单日期', format: 'Y-m-d', xtype: 'datefield', name: 'Creattime_min', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, { fieldLabel: '至..接单日期', format: 'Y-m-d', xtype: 'datefield', name: 'Creattime_max', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, { fieldLabel: '从..会计期间', //allowBlank: false, xtype: 'monthfield', name: 'ACCDATE_min', flex: 1, listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, { fieldLabel: '到..会计期间', //allowBlank: false, xtype: 'monthfield', name: 'ACCDATE_max', flex: 1, listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, this.comboxSeller] } ]//end items(fieldset 2) } //end fieldset 2 , {//fieldset3 xtype: 'container', defaultType: 'textfield', layout: 'anchor', defaults: { anchor: '100%' }, items: [{ xtype: 'container', layout: 'hbox', defaultType: 'textfield', items: [this.comboxPort,this.comboxLocked, this.comboxFeeName] } ]//end items(fieldset 2) } //end fieldset 3 , {//fieldset 4 xtype: 'container', defaultType: 'textfield', layout: 'anchor', defaults: { anchor: '100%' }, items: [{ xtype: 'container', layout: 'hbox', defaultType: 'textfield', items: [{ fieldLabel: '提单号', //allowBlank: false, name: 'Billno', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, { fieldLabel: '箱号', //allowBlank: false, name: 'ContainerNo', listeners: { specialkey: function (field, e) { if (e.getKey() == e.ENTER) { _this.onRefreshClick(); } } } }, this.comboxFeeGroupName] } ]//end items(fieldset 4) } //end fieldset 4 ]//end root items }); //#endregion formSearch var menu1 = new Ext.menu.Menu({ id: 'basicMenu', items: [{ text: '账单汇总表', handler: clickHandler }, { text: '应收应付结余列表', handler: clickHandler_1 }, { text: '应收结余列表', handler: clickHandler_2 }, { text: '费用分组结余列表', handler: clickHandler_3 } ] }); function clickHandler() { panelTest.Print(); }; function clickHandler_1() { panelTest.Print_1(); }; function clickHandler_2() { panelTest.Print_2(); }; function clickHandler_3() { panelTest.Print_3(); }; //查询工具条 this.panelBtn = new Ext.Panel({ region: "north", tbar: [ { text: "执行查询", iconCls: "btnrefresh", handler: function (button, event) { this.onRefreshClick(button, event); }, scope: this }, { text: "导出Excel", id: "btnExportExcel", iconCls: 'btnexportexcel', handler: function (button, event) { this.onExportClick(button, event); }, scope: this }, { text: "保存列表样式", id: "btntest", handler: function (button, event) { //this.column = DsTruck.SaveGridPanel(GID, "RPTProfit", this.gridList.columns, this.column, 1); var tempcolumns = this.gridList.columns; DsTruck.SaveGridPanel(GID, "RPTProfit", tempcolumns, this.column, 1, false); }, scope: this }, '-', { text: "打印报表", menu: menu1, scope: this } ] }); this.panelTop = new Ext.Panel({ layout: "border", region: "north", height: 155, items: [this.formSearch, this.panelBtn] }); Ext.apply(this, { items: [this.panelTop, this.gridList] }); this.storeList.on('beforeload', function (store) { if (!this.checkSearchCondition()) return; var sql = this.getCondition(); Ext.apply(store.proxy.extraParams, { condition: sql }); }, this); this.storeList.on('refresh', function (store) { mergeCells(this.gridList, [3,4]); //alert('refresh'); }, this); }, //end initUIComponents onRefreshClick: function (button, event) { if (!this.checkSearchCondition()) return; var sql = this.getCondition(); this.PageSize = this.Pagenum.getValue(); this.storeList.pageSize = this.PageSize; this.storeList.load({ params: { start: 0, limit: this.PageSize, condition: sql }, waitMsg: "正在查询数据...", scope: this }); }, getStrValue: function (list) { var _list = []; for (var _i = 0; _i < list.length; _i++) { _list.push("'" + list[_i] + "'"); } return _list; }, getCondition: function () { var form = this.formSearch.getForm(); if (!form.isValid()) { Ext.Msg.alert('提示', '查询条件赋值错误，请检查。'); return ''; } //var sql = " tt.dotyperef in ('应收','实际结算 实收') "; var sql = ' M.ISDELETE=0 '; var HTH = form.findField('HTH').getValue(); sql = sql + getAndConSql(sql, HTH, " bsno in(select contractno from import_main where HTH like '%" + HTH + "%')"); var Customer = form.findField('CustomerName').getValue(); sql = sql + getAndConSql(sql, Customer, " (customername like '%" + Customer + "%' )"); var Buyer = form.findField('Buyer').getValue(); sql = sql + getAndConSql(sql, Buyer, "bsno in(select contractno from import_main where Buyer like '%" + Buyer + "%')"); var Seller = form.findField('Seller').getValue(); sql = sql + getAndConSql(sql, Seller, "bsno in(select contractno from import_main where Seller like '%" + Seller + "%')"); var Billno = form.findField('Billno').getValue(); sql = sql + getAndConSql(sql, Billno, "bsno in(select contractno from import_main where billno like '%" + Billno + "%')"); var ContainerNo = form.findField('ContainerNo').getValue(); sql = sql + getAndConSql(sql, ContainerNo, "bsno in(select contractno from import_main where ContainerNo like '%" + ContainerNo + "%')"); var EnterDate_min = form.findField('EnterDate_min').getRawValue(); sql = sql + getAndConSql(sql, EnterDate_min, " bsno in(select contractno from import_main where arrivaldate > '" + EnterDate_min + "')"); var EnterDate_max = form.findField('EnterDate_max').getRawValue(); sql = sql + getAndConSql(sql, EnterDate_max, " bsno in(select contractno from import_main where arrivaldate <= '" + EnterDate_max + "')"); var Creattime_min = form.findField('Creattime_min').getRawValue(); sql = sql + getAndConSql(sql, Creattime_min, " bsno in(select contractno from import_main where Creattime > '" + Creattime_min + "')"); var Creattime_max = form.findField('Creattime_max').getRawValue(); sql = sql + getAndConSql(sql, Creattime_max, " bsno in(select contractno from import_main where Creattime <= '" + Creattime_max + "')"); var ACCDATE_min = form.findField('ACCDATE_min').getRawValue(); sql = sql + getAndConSql(sql, ACCDATE_min, " (c.ACCDATE >= '" + ACCDATE_min + "')"); var ACCDATE_max = form.findField('ACCDATE_max').getRawValue(); sql = sql + getAndConSql(sql, ACCDATE_max, " (c.ACCDATE <= '" + ACCDATE_max + "')"); var _CS = form.findField('ContractStatus').getValue(); if (_CS == '0') { sql = sql + getAndConSql(sql, _CS, " bsno in(select contractno from import_main where Contractstatus = 0 or Contractstatus is null )"); } else if (_CS == '1') { sql = sql + getAndConSql(sql, _CS, " bsno in(select contractno from import_main where Contractstatus = 1 )"); } var port = this.comboxPort.getValue(); if (port != "") { sql = sql + getAndConSql(sql, port, " bsno in (select contractno from import_main where port in (" + port + "))"); } var FeeName = this.getStrValue(this.comboxFeeName.getValue()); sql = sql + getAndConSql(sql, FeeName, "FeeName in (" + FeeName + ")"); var FeeGroupName = this.getStrValue(this.comboxFeeGroupName.getValue()); sql = sql + getAndConSql(sql, FeeGroupName, "FeeName in (select name from code_fee where feegroup in(" + FeeGroupName + "))"); return sql; }, checkSearchCondition: function () { var form = this.formSearch.getForm(); if (!form.isValid()) { Ext.Msg.alert('提示', '查询条件赋值错误，请检查。'); return false; } return true; }, onExportClick: function (button, event) { GridExportExcelPage(this.gridList); }, OprationSwap: function () { var ret = new Array(); ret[0] = 'edit'; ret[1] = ""; ret[2] = this.SelectedRecord; return ret; }, Print: function (HTH) { //账单汇总表 function GetDateStr(AddDayCount) { var dd = new Date(); dd.setDate(dd.getDate() + AddDayCount); //获取AddDayCount天后的日期 var y = dd.getFullYear(); var m = dd.getMonth() + 1; //获取当前月份的日期 var d = dd.getDate(); return y + "-" + m + "-" + d; } //alert(GetDateStr(+5)); var printdate = GetDateStr(0); //var getmoneydate = GetDateStr(5); var printType = 'CW_ZDHZ'; var T = this.getCondition(); var form = this.formSearch.getForm(); var ACCDATE_min = form.findField('ACCDATE_min').getRawValue(); var ACCDATE_max = form.findField('ACCDATE_max').getRawValue(); if (ACCDATE_min == ACCDATE_max) { ACCDATE_max = ""; } else { ACCDATE_max = " 至 " + ACCDATE_max; } var sql1 = " select m.HTH,m.buyer,M.accdate,t1.* into #t from (select gid,bsno ContractNo,customername,feetype,feename, "; var sql1 = sql1 + " case feetype when 1 then convert(numeric(18,2),amount*c.exchangerate) else 0 end ys,case feetype when 2 then amount*c.exchangerate else 0 end yf,0 y,0 ysU,0 yfU,0 yU, "; var sql1 = sql1 + " case feetype when 1 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end ys_ref, "; var sql1 = sql1 + " case feetype when 2 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end yf_ref,'' y_ref, "; var sql1 = sql1 + " '' ysU_ref,'' yfU_ref,'' yU_ref, "; var sql1 = sql1 + " dbo.trimdate(enterdate) [feedate],c.remark,Currency,c.exchangerate from ch_fee c "; var sql1 = sql1 + " left join import_main m on m.contractno=c.bsno "; var sql1 = sql1 + " where c.currency='RMB' and feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select gid,bsno ContractNo,customername,feetype,feename, "; var sql1 = sql1 + " 0 ys,0 yf,0 y, "; var sql1 = sql1 + " case feetype when 1 then convert(numeric(18,2),amount) else 0 end ysU,case feetype when 2 then amount else 0 end yfU, "; var sql1 = sql1 + " 0 yU, '' ys_ref,'' yf_ref,'' y_ref, "; var sql1 = sql1 + " case feetype when 1 then convert(varchar,convert(numeric(18,2),amount)) else '' end ysU_ref, "; var sql1 = sql1 + " case feetype when 2 then convert(varchar,convert(numeric(18,2),amount)) else '' end yfU_ref,'' yU_ref, "; var sql1 = sql1 + " dbo.trimdate(enterdate) [feedate],c.remark,Currency,c.exchangerate from ch_fee c "; var sql1 = sql1 + " left join import_main m on m.contractno=c.bsno "; var sql1 = sql1 + " where c.currency<>'RMB' and feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " ) t1 left join import_main m on m.contractno=t1.ContractNo "; var sql1 = sql1 + " order by t1.ContractNo ,t1.[feedate] "; var sql1 = sql1 + " select #T.buyer,convert(numeric(18,2),sum(ys)) ys, "; var sql1 = sql1 + " convert(numeric(18,2),sum(yf)) yf, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(ys)-sum(yf))) lr, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ysU)) ysU, "; var sql1 = sql1 + " convert(numeric(18,2),sum(yfU)) yfU, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(ysU)-sum(yfU))) lrU, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ysU*#t.exchangerate)) ysUR, "; var sql1 = sql1 + " convert(numeric(18,2),sum(yfU*#t.exchangerate)) yfUR, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(ysU*#t.exchangerate)-sum(yfU*#t.exchangerate))) lrUR "; var sql1 = sql1 + " from #t left join import_main m on m.contractno=#t.ContractNo "; var sql1 = sql1 + " group by #t.buyer drop table #t "; var sql2 = "select '" + printdate + "' printdate,'" + ACCDATE_min + "' ACCDATE,'" + ACCDATE_max + "' ACCDATE_max"; var sql3 = ""; var sql4 = ""; var sql5 = ""; var sql6 = ""; PrintComm(printType, sql1, sql2, sql3, sql4, sql5, sql6); }, Print_1: function (HTH) { //应收应付结余 function GetDateStr(AddDayCount) { var dd = new Date(); dd.setDate(dd.getDate() + AddDayCount); //获取AddDayCount天后的日期 var y = dd.getFullYear(); var m = dd.getMonth() + 1; //获取当前月份的日期 var d = dd.getDate(); return y + "-" + m + "-" + d; } //alert(GetDateStr(+5)); var printdate = GetDateStr(0); //var getmoneydate = GetDateStr(5); var printType = 'CW_QKJS'; //财务_欠款计算_应收应付结余 var T = this.getCondition(); var form = this.formSearch.getForm(); var ACCDATE_min = form.findField('ACCDATE_min').getRawValue(); var ACCDATE_max = form.findField('ACCDATE_max').getRawValue(); if (ACCDATE_min == ACCDATE_max) { ACCDATE_max = ""; } else { ACCDATE_max = " 至 " + ACCDATE_max; } var sql1 = " select m.HTH,t1.* into #t from (select gid,bsno ContractNo,customername,feetype,feename, "; var sql1 = sql1 + " case feetype when 1 then convert(numeric(18,2),amount*c.exchangerate) else 0 end ys, "; var sql1 = sql1 + " case feetype when 2 then amount*c.exchangerate else 0 end yf,0 y,0 ss,0 sf,0 s, "; var sql1 = sql1 + " case feetype when 1 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end ys_ref, "; var sql1 = sql1 + " case feetype when 2 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end yf_ref, "; var sql1 = sql1 + " '' y_ref,'' ss_ref,'' sf_ref,'' s_ref, "; var sql1 = sql1 + " dbo.trimdate(enterdate) [feedate],c.remark,Currency from ch_fee c "; var sql1 = sql1 + " left join import_main m on m.contractno=c.bsno where feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select gid,bsno ContractNo,customername,feetype,feename, 0 ys,0 yf,0 y, "; var sql1 = sql1 + " case FEETYPE when 1 then convert(numeric(18,2),SETTLEMENT*c.exchangerate) else 0 end ss, "; var sql1 = sql1 + " case FEETYPE when 2 then convert(numeric(18,2),SETTLEMENT*c.exchangerate) else 0 end sf,0 s, "; var sql1 = sql1 + " '' ys_ref,'' yf_ref,'' y_ref, "; var sql1 = sql1 + " case FEETYPE when 1 then convert(varchar,convert(numeric(18,2),SETTLEMENT*c.exchangerate)) else '' end ss_ref, "; var sql1 = sql1 + " case FEETYPE when 2 then convert(varchar,convert(numeric(18,2),SETTLEMENT*c.exchangerate)) else '' end sf_ref, "; var sql1 = sql1 + " '' s_ref,dbo.trimdate(enterdate) [feedate],cd.remark,Currency "; var sql1 = sql1 + " from ch_fee cd "; var sql1 = sql1 + " left join import_main m on m.contractno=cd.bsno "; var sql1 = sql1 + " where feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " ) t1 left join import_main m on m.contractno=t1.ContractNo order by t1.ContractNo ,t1.[feedate] "; var sql1 = sql1 + " select #T.customername, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ys)) ys, "; var sql1 = sql1 + " convert(numeric(18,2),sum(yf)) yf, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ss)) ss, "; var sql1 = sql1 + " convert(numeric(18,2),sum(sf)) sf, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(ys)-sum(ss))) ysjy, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(yf)-sum(sf))) yfjy "; var sql1 = sql1 + " from #t left join import_main m on m.contractno=#t.ContractNo "; var sql1 = sql1 + " group by #t.customername drop table #t "; var sql2 = "select '" + printdate + "' printdate,'" + ACCDATE_min + "' ACCDATE,'" + ACCDATE_max + "' ACCDATE_max"; var sql3 = ""; var sql4 = ""; var sql5 = ""; var sql6 = ""; PrintComm(printType, sql1, sql2, sql3, sql4, sql5, sql6); }, Print_2: function (HTH) { //应收结余 function GetDateStr(AddDayCount) { var dd = new Date(); dd.setDate(dd.getDate() + AddDayCount); //获取AddDayCount天后的日期 var y = dd.getFullYear(); var m = dd.getMonth() + 1; //获取当前月份的日期 var d = dd.getDate(); return y + "-" + m + "-" + d; } //alert(GetDateStr(+5)); var printdate = GetDateStr(0); //var getmoneydate = GetDateStr(5); var printType = 'CW_QKJS_2'; //财务_欠款计算 var T = this.getCondition(); var form = this.formSearch.getForm(); var ACCDATE_min = form.findField('ACCDATE_min').getRawValue(); var ACCDATE_max = form.findField('ACCDATE_max').getRawValue(); if (ACCDATE_min == ACCDATE_max) { ACCDATE_max = ""; } else { ACCDATE_max = " 至 " + ACCDATE_max; } var sql1 = " select m.HTH,t1.* into #t from (select gid,bsno ContractNo,customername,feetype,feename, "; var sql1 = sql1 + " case feetype when 1 then convert(numeric(18,2),amount*c.exchangerate) else 0 end ys, "; var sql1 = sql1 + " case feetype when 2 then amount*c.exchangerate else 0 end yf,0 y,0 ss,0 sf,0 s, "; var sql1 = sql1 + " case feetype when 1 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end ys_ref, "; var sql1 = sql1 + " case feetype when 2 then convert(varchar,convert(numeric(18,2),amount*c.exchangerate)) else '' end yf_ref, "; var sql1 = sql1 + " '' y_ref,'' ss_ref,'' sf_ref,'' s_ref, "; var sql1 = sql1 + " dbo.trimdate(enterdate) [feedate],c.remark,Currency from ch_fee c "; var sql1 = sql1 + " left join import_main m on m.contractno=c.bsno where feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select gid,bsno ContractNo,customername,feetype,feename, 0 ys,0 yf,0 y, "; var sql1 = sql1 + " case FEETYPE when 1 then convert(numeric(18,2),SETTLEMENT*c.exchangerate) else 0 end ss, "; var sql1 = sql1 + " case FEETYPE when 2 then convert(numeric(18,2),SETTLEMENT*c.exchangerate) else 0 end sf,0 s, "; var sql1 = sql1 + " '' ys_ref,'' yf_ref,'' y_ref, "; var sql1 = sql1 + " case FEETYPE when 1 then convert(varchar,convert(numeric(18,2),SETTLEMENT*c.exchangerate)) else '' end ss_ref, "; var sql1 = sql1 + " case FEETYPE when 2 then convert(varchar,convert(numeric(18,2),SETTLEMENT*c.exchangerate)) else '' end sf_ref, "; var sql1 = sql1 + " '' s_ref,dbo.trimdate(enterdate) [feedate],cd.remark,Currency "; var sql1 = sql1 + " from ch_fee cd "; var sql1 = sql1 + " left join import_main m on m.contractno=cd.bsno "; var sql1 = sql1 + " where feestatus in(0,8,9,10,11) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " ) t1 left join import_main m on m.contractno=t1.ContractNo order by t1.ContractNo ,t1.[feedate] "; var sql1 = sql1 + " select #T.customername, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ys)) ys, "; var sql1 = sql1 + " convert(numeric(18,2),sum(yf)) yf, "; var sql1 = sql1 + " convert(numeric(18,2),sum(ss)) ss, "; var sql1 = sql1 + " convert(numeric(18,2),sum(sf)) sf, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(ys)-sum(ss))) ysjy, "; var sql1 = sql1 + " convert(numeric(18,2),(sum(yf)-sum(sf))) yfjy "; var sql1 = sql1 + " from #t left join import_main m on m.contractno=#t.ContractNo "; var sql1 = sql1 + " group by #t.customername drop table #t "; var sql2 = "select '" + printdate + "' printdate,'" + ACCDATE_min + "' ACCDATE,'" + ACCDATE_max + "' ACCDATE_max"; var sql3 = ""; var sql4 = ""; var sql5 = ""; var sql6 = ""; PrintComm(printType, sql1, sql2, sql3, sql4, sql5, sql6); }, Print_3: function (HTH) { //经过费用分组的应收应付结余 function GetDateStr(AddDayCount) { var dd = new Date(); dd.setDate(dd.getDate() + AddDayCount); //获取AddDayCount天后的日期 var y = dd.getFullYear(); var m = dd.getMonth() + 1; //获取当前月份的日期 var d = dd.getDate(); return y + "-" + m + "-" + d; } //alert(GetDateStr(+5)); var printdate = GetDateStr(0); //var getmoneydate = GetDateStr(5); var printType = 'CW_QKJS_3'; // var T = this.getCondition(); var form = this.formSearch.getForm(); var ACCDATE_min = form.findField('ACCDATE_min').getRawValue(); var ACCDATE_max = form.findField('ACCDATE_max').getRawValue(); if (ACCDATE_min == ACCDATE_max) { ACCDATE_max = ""; } else { ACCDATE_max = " 至 " + ACCDATE_max; } var sql1 = " select * into #t_fz from( "; var sql1 = sql1 + " select customername,feegroup,sum(isnull(ys,0)) ys,sum(isnull(ss,0)) ss,sum(isnull(yf,0)) yf,sum(isnull(sf,0)) sf from( "; var sql1 = sql1 + " select c.customername,(select feegroup from code_fee where name=c.FEENAME) feegroup "; var sql1 = sql1 + " ,c.amount*c.EXCHANGERATE ys,c.SETTLEMENT*c.EXCHANGERATE ss, 0 yf,0 sf "; var sql1 = sql1 + " from ch_Fee c "; var sql1 = sql1 + " left join import_main m on m.ContractNo=c.bsno "; var sql1 = sql1 + " where c.FEETYPE=1 and c.FEESTATUS in(0,8,9,10) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select c.customername,(select feegroup from code_fee where name=c.FEENAME) feegroup "; var sql1 = sql1 + " , 0 ys,0 ss,c.amount*c.EXCHANGERATE yf,c.SETTLEMENT*c.EXCHANGERATE sf "; var sql1 = sql1 + " from ch_Fee c "; var sql1 = sql1 + " left join import_main m on m.ContractNo=c.bsno "; var sql1 = sql1 + " where c.FEETYPE=2 and c.FEESTATUS in(0,8,9,10) "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select c.customername,(select feegroup from code_fee where name=c.FEENAME) feegroup "; var sql1 = sql1 + " ,c.amount*c.EXCHANGERATE ys,c.amount*c.EXCHANGERATE ss, 0 yf,0 sf "; var sql1 = sql1 + " from ch_Fee c "; var sql1 = sql1 + " left join import_main m on m.ContractNo=c.bsno "; var sql1 = sql1 + " where c.FEETYPE=1 and c.FEESTATUS =11 "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " union all "; var sql1 = sql1 + " select c.customername,(select feegroup from code_fee where name=c.FEENAME) feegroup "; var sql1 = sql1 + " , 0 ys,0 ss,c.amount*c.EXCHANGERATE yf,c.amount*c.EXCHANGERATE sf "; var sql1 = sql1 + " from ch_Fee c "; var sql1 = sql1 + " left join import_main m on m.ContractNo=c.bsno "; var sql1 = sql1 + " where c.FEETYPE=2 and c.FEESTATUS =11 "; var sql1 = sql1 + " and " + T; var sql1 = sql1 + " )t "; var sql1 = sql1 + " group by customername,feegroup)t1 "; var sql1 = sql1 + " select distinct customername "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='货款') 货款应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='货款') 货款实收 "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='代理费') 代理费应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='代理费') 代理费实收 "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='税金') 税金应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='税金') 税金实收 "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='融资款') 融资款应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='融资款') 融资款实收 "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='冷藏') 冷藏应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='冷藏') 冷藏实收 "; var sql1 = sql1 + " ,(select sum(ys) from #t_fz where customername=t1.customername and feegroup='其他') 其他应收 "; var sql1 = sql1 + " ,(select sum(ss) from #t_fz where customername=t1.customername and feegroup='其他') 其他实收 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='货款') 货款应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='货款') 货款实付 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='代理费') 代理费应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='代理费') 代理费实付 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='税金') 税金应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='税金') 税金实付 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='融资款') 融资款应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='融资款') 融资款实付 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='冷藏') 冷藏应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='冷藏') 冷藏实付 "; var sql1 = sql1 + " ,(select sum(yf) from #t_fz where customername=t1.customername and feegroup='其他') 其他应付 "; var sql1 = sql1 + " ,(select sum(sf) from #t_fz where customername=t1.customername and feegroup='其他') 其他实付 "; var sql1 = sql1 + " from #t_fz t1 order by customername "; var sql1 = sql1 + " drop table #t_fz "; var sql2 = "select '" + printdate + "' printdate,'" + ACCDATE_min + "' ACCDATE,'" + ACCDATE_max + "' ACCDATE_max"; var sql3 = ""; var sql4 = ""; var sql5 = ""; var sql6 = ""; PrintComm(printType, sql1, sql2, sql3, sql4, sql5, sql6); } });