/** * @Author: zou.yingbin * @Create : 2021/2/4 11:27 * @Modify : 2021/2/4 11:27 */ package models import ( "fmt" "mtp2_if/db" "mtp2_if/mtpcache" "mtp2_if/utils" ) // ErmcpReportDayFR 财务日报表 type ErmcpReportDayFR struct { RECKONDATE string `json:"reckondate" xorm:"'RECKONDATE'"` // 日照时期(yyyyMMdd) AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'"` // 所属机构 COLLECTMONEYCOUNT int32 `json:"collectmoneycount" xorm:"'COLLECTMONEYCOUNT'"` // 今日收款笔数 COLLECTMONEYAMOUNT float64 `json:"collectmoneyamount" xorm:"'COLLECTMONEYAMOUNT'"` // 今日收款金额 PAYMONEYCOUNT int32 `json:"paymoneycount" xorm:"'PAYMONEYCOUNT'"` // 今日付款笔数 PAYMONEYAMOUNT float64 `json:"paymoneyamount" xorm:"'PAYMONEYAMOUNT'"` // 今日付款金额 COLLECTINVOICECOUNT int32 `json:"collectinvoicecount" xorm:"'COLLECTINVOICECOUNT'"` // 今日开票笔数 COLLECTINVOICEAMOUNT float64 `json:"collectinvoiceamount" xorm:"'COLLECTINVOICEAMOUNT'"` // 今日开票金额 PAYINVOICECOUNT int32 `json:"payinvoicecount" xorm:"'PAYINVOICECOUNT'"` // 今日收票笔数 PAYINVOICEAMOUNT float64 `json:"payinvoiceamount" xorm:"'PAYINVOICEAMOUNT'"` // 今日收票金额 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 BeginDate string `json:"-"` // 开始日期 EndDate string `json:"-"` // 结束日期 } // Calc 数据处理 func (r *ErmcpReportDayFR) Calc() { } func (r *ErmcpReportDayFR) buildSql() string { var sqlId utils.SQLVal = "SELECT t.RECKONDATE," + " t.AREAUSERID," + " t.COLLECTMONEYCOUNT," + " t.COLLECTMONEYAMOUNT," + " t.PAYMONEYCOUNT," + " t.PAYMONEYAMOUNT," + " t.COLLECTINVOICECOUNT," + " t.COLLECTINVOICEAMOUNT," + " t.PAYINVOICECOUNT," + " t.PAYINVOICEAMOUNT," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh:mi:ss') UPDATETIME" + " FROM RECKON_ERMCP_AREAFR t" + " WHERE 1 = 1" // 查询条件 sqlId.And("t.AREAUSERID", r.AREAUSERID) if r.RECKONDATE != "" { sqlId.And("t.RECKONDATE", r.RECKONDATE) } else if r.BeginDate != "" && r.BeginDate == r.EndDate { sqlId.And("t.RECKONDATE", r.BeginDate) } else { if r.BeginDate != "" { sqlId.BiggerOrEq("t.RECKONDATE", r.BeginDate) } if r.EndDate != "" { sqlId.LessOrEq("t.RECKONDATE", r.EndDate) } } return sqlId.String() } // GetDataEx 获取日报表 func (r *ErmcpReportDayFR) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportDayFR, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) return sData, err } // ErmcpReportDayFRKx 财务日报表/款项 type ErmcpReportDayFRKx struct { USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID TRADEDATE string `json:"tradedate" xorm:"'TRADEDATE'"` // 交易日(yyyyMMdd) } // GetDataEx 获取日报表款项记录 func (r *ErmcpReportDayFRKx) GetDataEx() (interface{}, error) { m := ErmcpReportOPLog{USERID: r.USERID, LogTypeFilter: "8,9,10", TRADEDATE: r.TRADEDATE} return m.GetDataEx() } // ErmcpReportOPLog 报表合同操作记录通用查询 type ErmcpReportOPLog struct { LOGID string `json:"logid" xorm:"'lOGID'"` // 流水ID(604+Unix秒时间戳(10位)+xxxxxx) BIZTYPE int32 `json:"biztype" xorm:"'BIZTYPE'"` // 业务类型 - 1:套保计划 2:现货合同 OPERATELOGTYPE int32 `json:"operatelogtype" xorm:"'OPERATELOGTYPE'"` // 操作流水类型 - RELATEDID string `json:"relatedid" xorm:"'RELATEDID'"` // 现货合同ID\套保计划 LOGVALUE string `json:"logvalue" xorm:"'LOGVALUE'"` // 数值 LOGDATETIME string `json:"logdatetime" xorm:"'LOGDATETIME'"` // 流水日期(时间) TRADEDATE string `json:"tradedate" xorm:"'TRADEDATE'"` // 交易日(yyyyMMdd) APPLYID int64 `json:"applyid" xorm:"'APPLYID'"` // 操作人 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 现货合同类型 - 1:采购 -1:销售 USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID RELATEDNO string `json:"relatedno" xorm:"'RELATEDNO'"` // 合同编号 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"-" xorm:"'UNITID'"` // 现货商品单位id ENUMDICNAME string `json:"enumdicName"` // 单位名称 OPTYPENAME string `json:"optypename"` // 流水类型名称 LOGTYPENAME string `json:"logtypename"` // 合同类型(名称) APPLYNAME string `json:"applyname"` // 操作人名称 BRANDNAME string `json:"brandname" xorm:"'brandname'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'modelname'"` // 型号名称 LogTypeFilter string `json:"-"` // 查询日志类型, 逗号隔开(如 1,2,4) } // Calc 处理数据 func (r *ErmcpReportOPLog) Calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.APPLYNAME = mtpcache.GetUserNameByUserId(r.APPLYID) if r.APPLYNAME == "" { // APPLYID 可能存的是loginId r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID) } //1:点价价格 2:点价数量 3:结算量 4:其它费用 5:追加保证金 6溢短金额 7:调整金额 8:付款金额 9:收款金额 10:退款金额 //11:收票金额 12:开票金额 13:提交审核(合同) 14:审核通过(合同) 15:审核拒绝(合同) 16:合同撤回 17:提交审核(计划) //18:审核通过(计划) 19:审核拒绝(计划) 20:计划撤回 21:正常完结(合同) 22:异常终止(合同) 23:退还保证金 // 数据库注释与返回值映身关系： 结算量->确定量, 收款->收款金额, 退款->退款金额, 付款->付款金额, 收票->收票金额, 开票->开票金额 sDes := []string{"点价价格", "点价数量", "确定量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款", "收款", "退款", "收票", "开票", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)", "审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金"} if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 23 { r.OPTYPENAME = sDes[r.OPERATELOGTYPE-1] } // 收款款项:采购合同的退款、销售合同的收款应用正数显示，付款款项：采购合同的付款、销售合同的退款应用负数显示 if r.CONTRACTTYPE == 1 { r.LOGTYPENAME = "采购" // 采购合同/退款 正数 if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] == '-' { r.LOGVALUE = r.LOGVALUE[1:] } } // 采购合同/付款 负数 if r.OPERATELOGTYPE == 8 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] != '-' { r.LOGVALUE = "-" + r.LOGVALUE } } } else if r.CONTRACTTYPE == -1 { r.LOGTYPENAME = "销售" // 销售合同/收款 正数 if r.OPERATELOGTYPE == 9 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] == '-' { r.LOGVALUE = r.LOGVALUE[1:] } } // 销售合同/退款 负数 if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 { if r.LOGVALUE[0] != '-' { r.LOGVALUE = "-" + r.LOGVALUE } } } // 去除多余的0,最后4个0 if r.LOGVALUE[len(r.LOGVALUE)-4:] == "0000" { r.LOGVALUE = r.LOGVALUE[:len(r.LOGVALUE)-4] } if r.BIZTYPE == 1 { r.LOGTYPENAME += "计划" } else if r.BIZTYPE == 2 { r.LOGTYPENAME += "合同" } } func (r *ErmcpReportOPLog) buildSql() string { var sqlId utils.SQLVal = "SELECT to_char(t.LOGID) LOGID," + " t.BIZTYPE," + " t.OPERATELOGTYPE," + " to_char(t.RELATEDID) RELATEDID," + " t.LOGVALUE," + " to_char(t.LOGDATETIME, 'yyyy-mm-dd hh24:mi:ss') LOGDATETIME," + " t.TRADEDATE," + " t.APPLYID," + " t.CONTRACTTYPE," + " t.USERID," + " t.WRSTANDARDID," + " s.contractno relatedno," + " s.spotgoodsbrandid," + " s.spotgoodsmodelid," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.brandname," + " gm.modelname" + " FROM ERMCP_CONTRACTOPERATELOG t" + " inner join ermcp_spotcontract s" + " on t.RELATEDID = s.spotcontractid" + " and t.userid = %v" + " left join wrstandard w" + " on t.wrstandardid = w.wrstandardid" + " left join spotgoodsbrand gb" + " on s.spotgoodsbrandid = gb.brandid" + " left join spotgoodsmodel gm" + " on s.spotgoodsmodelid = gm.modelid" + " WHERE t.biztype = 2" sqlId = utils.SQLVal(fmt.Sprintf(sqlId.String(), r.USERID)) // 筛选条件 sqlId.And("t.TRADEDATE", r.TRADEDATE) if r.WRSTANDARDID > 0 { sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID) } if r.LogTypeFilter != "" { sqlId.Join(fmt.Sprintf(" and t.OPERATELOGTYPE in(%v)", r.LogTypeFilter)) } return sqlId.String() } // GetDataEx 获取日志记录 func (r *ErmcpReportOPLog) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportOPLog, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].Calc() } return sData, err } // ErmcpReportMonthFR 财务月报表 type ErmcpReportMonthFR struct { CYCLETYPE int32 `json:"cycletype" xorm:"'cycletype'"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 CYCLETIME string `json:"cycletime" xorm:"'cycletime'"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 AREAUSERID int64 `json:"areauserid" xorm:"'areauserid'"` // 所属机构【原值】 COLLECTMONEYCOUNT int32 `json:"collectmoneycount" xorm:"'collectmoneycount'"` // 今日收款笔数 【汇总】 COLLECTMONEYAMOUNT float64 `json:"collectmoneyamount" xorm:"'collectmoneyamount'"` // 今日收款金额【汇总】 PAYMONEYCOUNT int32 `json:"paymoneycount" xorm:"'paymoneycount'"` // 今日付款笔数【汇总】 PAYMONEYAMOUNT float64 `json:"paymoneyamount" xorm:"'paymoneyamount'"` // 今日付款金额【汇总】 COLLECTINVOICECOUNT int32 `json:"collectinvoicecount" xorm:"'collectinvoicecount'"` // 今日开票笔数【汇总】 COLLECTINVOICEAMOUNT float64 `json:"collectinvoiceamount" xorm:"'collectinvoiceamount'"` // 今日开票金额【汇总】 PAYINVOICECOUNT int32 `json:"payinvoicecount" xorm:"'payinvoicecount'"` // 今日收票笔数【汇总】 PAYINVOICEAMOUNT float64 `json:"payinvoiceamount" xorm:"'payinvoiceamount'"` // 今日收票金额【汇总】 UPDATETIME string `json:"updatetime" xorm:"'updatetime'"` // 更新时间 DayFR []ErmcpReportDayFR `json:"dayFr"` // 日报表明细 } // Calc 数据处理 func (r *ErmcpReportMonthFR) Calc() { } func (r *ErmcpReportMonthFR) buildSql() string { var sqlId utils.SQLVal = "SELECT t.CYCLETYPE," + " t.CYCLETIME," + " t.AREAUSERID," + " t.COLLECTMONEYCOUNT," + " t.COLLECTMONEYAMOUNT," + " t.PAYMONEYCOUNT," + " t.PAYMONEYAMOUNT," + " t.COLLECTINVOICECOUNT," + " t.COLLECTINVOICEAMOUNT," + " t.PAYINVOICECOUNT," + " t.PAYINVOICEAMOUNT," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh:mi:ss') UPDATETIME" + " FROM REPORT_ERMCP_AREAFR t" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.And("t.CYCLETYPE", 1) sqlId.And("t.CYCLETIME", r.CYCLETIME) return sqlId.String() } // GetDataEx 获取月报表 func (r *ErmcpReportMonthFR) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportMonthFR, 0) // 月报表 err := db.GetEngine().SQL(r.buildSql()).Find(&sData) // 日报表明细 if len(r.CYCLETIME) != 6 { // 月报表的日期应是6位,如 202101 return nil, nil } t1 := r.CYCLETIME + "01" t2 := r.CYCLETIME + "31" for i := range sData { sData[i].Calc() dayM := ErmcpReportDayFR{AREAUSERID: r.AREAUSERID, BeginDate: t1, EndDate: t2} if dObj, err := dayM.GetDataEx(); err == nil { if d, ok := dObj.([]ErmcpReportDayFR); ok { sData[i].DayFR = d } } } return sData, err } // ErmcpReportDaySpot 现货日报表 type ErmcpReportDaySpot struct { BUYPRICEDQTY float64 `json:"-" xorm:"'BUYPRICEDQTY'"` // 期末采购定价量 SELLPRICEDQTY float64 `json:"-" xorm:"'SELLPRICEDQTY'"` // 期末销售定价量 ORIBUYPRICEDQTY float64 `json:"-" xorm:"'ORIBUYPRICEDQTY'"` // 期初采购定价量 ORISELLPRICEDQTY float64 `json:"-" xorm:"'ORISELLPRICEDQTY'"` // 期初销售定价量 TODAYBUYRECKONQTY float64 `json:"todaybuyreckonqty" xorm:"'TODAYBUYRECKONQTY'"` // 采购确定量 TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty" xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量 RECKONDATE string `json:"reckondate" xorm:"'RECKONDATE'"` // 日照时期(yyyyMMdd) WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'"` // 所属机构T WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"-" xorm:"'UNITID'"` // 现货商品单位id BUYINQTY float64 `json:"buyinqty" xorm:"'BUYINQTY'"` // 采购入库量 SELLOUTQTY float64 `json:"selloutqty" xorm:"'SELLOUTQTY'"` // 销售出库量 ENUMDICNAME string `json:"enumdicname"` // 现货商品单位名称 TOTALBUYPRICEDQTY float64 `json:"totalbuypricedqty"` // 采购定价量 = 期末 - 期初 TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初 BeginDate string `json:"-"` // 开始日期 EndDate string `json:"-"` // 结束日期 } // Calc 数据处理 func (r *ErmcpReportDaySpot) Calc() { r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) } func (r *ErmcpReportDaySpot) buildSql() string { var sqlId utils.SQLVal = "with tmp as" + " (select t.userid," + " t.wrstandardid," + " t.reckondate," + " sum(t.todaybuyinqty) todaybuyinqty," + " sum(t.todayselloutqty) todayselloutqty" + " from Reckon_ERMCP_AreaStock t" + " group by t.userid, t.wrstandardid,t.reckondate)" + "select t.reckondate," + " t.BUYPRICEDQTY," + " t.SELLPRICEDQTY," + " t.Oribuypricedqty," + " t.Orisellpricedqty," + " t.TODAYBUYRECKONQTY," + " t.TODAYSELLRECKONQTY," + " t.RECKONDATE," + " t.WRSTANDARDID," + " t.AREAUSERID," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " s.todaybuyinqty buyinqty," + " s.todayselloutqty selloutqty" + " from RECKON_ERMCP_AREASPOT t" + " left join tmp s" + " on t.reckondate = s.reckondate" + " and t.areauserid = s.userid" + " and t.wrstandardid = s.wrstandardid" + " left join wrstandard w" + " on t.wrstandardid = w.wrstandardid" + " where 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) if r.WRSTANDARDID > 0 { sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID) } if r.RECKONDATE != "" { sqlId.And("t.RECKONDATE", r.RECKONDATE) } else if r.BeginDate != "" && r.BeginDate == r.EndDate { sqlId.And("t.RECKONDATE", r.BeginDate) } else { if r.BeginDate != "" { sqlId.BiggerOrEq("t.RECKONDATE", r.BeginDate) } if r.EndDate != "" { sqlId.LessOrEq("t.RECKONDATE", r.EndDate) } } sqlId.Join(" order by t.RECKONDATE") return sqlId.String() } // GetDataEx 获取现货日报表 func (r *ErmcpReportDaySpot) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportDaySpot, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].Calc() } return sData, err } // ErmcpReportMonSpot 现货月报表 type ErmcpReportMonSpot struct { CYCLETYPE int32 `json:"cycletype" xorm:"'cycletype'"` // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】 CYCLETIME string `json:"cycletime" xorm:"'cycletime'"` // 周期时间 月(YYYYMM) 季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】 BUYPRICEDQTY float64 `json:"-" xorm:"'BUYPRICEDQTY'"` // 期末采购定价量 SELLPRICEDQTY float64 `json:"-" xorm:"'SELLPRICEDQTY'"` // 期末销售定价量 ORIBUYPRICEDQTY float64 `json:"-" xorm:"'ORIBUYPRICEDQTY'"` // 期初采购定价量 ORISELLPRICEDQTY float64 `json:"-" xorm:"'ORISELLPRICEDQTY'"` // 期初销售定价量 TODAYBUYRECKONQTY float64 `json:"todaybuyreckonqty" xorm:"'TODAYBUYRECKONQTY'"` // 采购确定量 TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty" xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量 WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'"` // 所属机构T WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"-" xorm:"'UNITID'"` // 现货商品单位id BUYINQTY float64 `json:"buyinqty" xorm:"'BUYINQTY'"` // 采购入库量 SELLOUTQTY float64 `json:"selloutqty" xorm:"'SELLOUTQTY'"` // 销售出库量 ENUMDICNAME string `json:"enumdicname"` // 现货商品单位名称 TOTALBUYPRICEDQTY float64 `json:"totalbuypricedqty"` // 采购定价量 = 期末 - 期初 TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初 } // Calc 数据处理 func (r *ErmcpReportMonSpot) Calc() { r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) } func (r *ErmcpReportMonSpot) buildSql() string { var sqlId utils.SQLVal = "with tmp as" + " (select t.userid," + " t.wrstandardid," + " t.cycletime," + " t.cycletype," + " sum(t.todaybuyinqty) todaybuyinqty," + " sum(t.todayselloutqty) todayselloutqty" + " from Report_ERMCP_AreaStock t" + " group by t.userid, t.wrstandardid, t.cycletime, t.cycletype)" + "select t.BUYPRICEDQTY," + " t.SELLPRICEDQTY," + " t.Oribuypricedqty," + " t.Orisellpricedqty," + " t.TODAYBUYRECKONQTY," + " t.TODAYSELLRECKONQTY," + " t.cycletype," + " t.cycletime," + " t.WRSTANDARDID," + " t.AREAUSERID," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " s.todaybuyinqty buyinqty," + " s.todayselloutqty selloutqty" + " from Report_ERMCP_AreaSpot t" + " left join tmp s" + " on t.cycletime = s.cycletime" + " and t.cycletype = s.cycletype" + " and t.areauserid = s.userid" + " and t.wrstandardid = s.wrstandardid" + " left join wrstandard w" + " on t.wrstandardid = w.wrstandardid" + " where t.cycletype = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) sqlId.And("t.cycletime", r.CYCLETIME) return sqlId.String() } // GetDataEx 获取现货月报表 func (r *ErmcpReportMonSpot) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportMonSpot, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].Calc() } return sData, err } // ErmcpReportAreaSpotPL 现货损益日/月表 type ErmcpReportAreaSpotPL struct { AREAUSERID int64 `json:"areauserid" xorm:"'AREAUSERID'"` // 所属机构 WRSTANDARDID int64 `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID SPOTGOODSMODELID int32 `json:"spotgoodsmodelid" xorm:"'SPOTGOODSMODELID'"` // 现货品类ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID ORIBUYQTY float64 `json:"oribuyqty" xorm:"'ORIBUYQTY'"` // 期初采购总量 ORIBUYAMOUNT float64 `json:"oribuyamount" xorm:"'ORIBUYAMOUNT'"` // 期初采购总额 ORISELLQTY float64 `json:"orisellqty" xorm:"'ORISELLQTY'"` // 期初销售总量 ORISELLAMOUNT float64 `json:"orisellamount" xorm:"'ORISELLAMOUNT'"` // 期初销售总额 ORIQTY float64 `json:"oriqty" xorm:"'ORIQTY'"` // 期初量 ORIAVERAGEPRICE float64 `json:"oriaverageprice" xorm:"'ORIAVERAGEPRICE'"` // 期初均价 ORIAMOUNT float64 `json:"oriamount" xorm:"'ORIAMOUNT'"` // 期初额 TODAYBUYQTY float64 `json:"todaybuyqty" xorm:"'TODAYBUYQTY'"` // 今日采购量(采购增量) TODAYBUYAMOUNT float64 `json:"todaybuyamount" xorm:"'TODAYBUYAMOUNT'"` // 今日采购额 TODAYBUYAVERAGEPRICE float64 `json:"todaybuyaverageprice" xorm:"'TODAYBUYAVERAGEPRICE'"` // 今日采购均价(采购均价) TODAYSELLQTY float64 `json:"todaysellqty" xorm:"'TODAYSELLQTY'"` // 今日销售量(销售增量) TODAYSELLAMOUNT float64 `json:"todaysellamount" xorm:"'TODAYSELLAMOUNT'"` // 今日销售额 TODAYSELLAVERAGEPRICE float64 `json:"todaysellaverageprice" xorm:"'TODAYSELLAVERAGEPRICE'"` // 今日销售均价(销售均价) CURBUYQTY float64 `json:"curbuyqty" xorm:"'CURBUYQTY'"` // 期末采购总量 CURBUYAMOUNT float64 `json:"curbuyamount" xorm:"'CURBUYAMOUNT'"` // 期末采购总额(采购额) CURSELLQTY float64 `json:"cursellqty" xorm:"'CURSELLQTY'"` // 期末销售总量 CURSELLAMOUNT float64 `json:"cursellamount" xorm:"'CURSELLAMOUNT'"` // 期末销售总额(销售额) CURQTY float64 `json:"curqty" xorm:"'CURQTY'"` // 期末量 CURAVERAGEPRICE float64 `json:"curaverageprice" xorm:"'CURAVERAGEPRICE'"` // 期末均价 CURAMOUNT float64 `json:"curamount" xorm:"'CURAMOUNT'"` // 期末额 CURSPOTPRICE float64 `json:"curspotprice" xorm:"'CURSPOTPRICE'"` // 参考市价 CURMARKETVALUE float64 `json:"curmarketvalue" xorm:"'CURMARKETVALUE'"` // 参考市值 ACTUALPL float64 `json:"actualpl" xorm:"'ACTUALPL'"` // 实际损益 FLOATPL float64 `json:"floatpl" xorm:"'FLOATPL'"` // 浮动损益 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 现货商品单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'MODELNAME'"` // 品类名称 GBUNITID int32 `json:"gbunitid" xorm:"'GBUNITID'"` // 品类单位id ENUMDICNAME string `json:"enumdicname"` // 现货商品单位名称 GBENUMDICNAME string `json:"gbenumdicname"` // 品类单位名称 ReportType int32 `json:"-"` // 报表类型 1-日报表 2-月报表 ReportDate string `json:"-"` // 格式 日报表(YYYYMMDD) 月报表(YYYYMM) } func (r *ErmcpReportAreaSpotPL) calc() { r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) r.GBENUMDICNAME = mtpcache.GetEnumDicitemName(r.GBUNITID) } func (r *ErmcpReportAreaSpotPL) buildSql() string { var sqlId utils.SQLVal = "SELECT t.AREAUSERID," + " t.WRSTANDARDID," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.ORIBUYQTY," + " t.ORIBUYAMOUNT," + " t.ORISELLQTY," + " t.ORISELLAMOUNT," + " t.ORIQTY," + " t.ORIAVERAGEPRICE," + " t.ORIAMOUNT," + " t.TODAYBUYQTY," + " t.TODAYBUYAMOUNT," + " t.TODAYBUYAVERAGEPRICE," + " t.TODAYSELLQTY," + " t.TODAYSELLAMOUNT," + " t.TODAYSELLAVERAGEPRICE," + " t.CURBUYQTY," + " t.CURBUYAMOUNT," + " t.CURSELLQTY," + " t.CURSELLAMOUNT," + " t.CURQTY," + " t.CURAVERAGEPRICE," + " t.CURAMOUNT," + " t.CURSPOTPRICE," + " t.CURMARKETVALUE," + " t.ACTUALPL," + " t.FLOATPL," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " w.wrstandardcode," + " w.wrstandardname," + " w.unitid," + " gb.brandname," + " gm.modelname," + " gm.unitid gbunitid" + " FROM %v t" + " left join wrstandard w" + " on t.wrstandardid = w.wrstandardid" + " left join spotgoodsbrand gb" + " on t.spotgoodsbrandid = gb.brandid" + " left join spotgoodsmodel gm" + " on t.spotgoodsmodelid = gm.modelid" + " WHERE 1 = 1" sqlId.And("t.AREAUSERID", r.AREAUSERID) if r.ReportType == 1 { // 日报表 sqlId.FormatParam("RECKON_ERMCP_AREASPOTPL") sqlId.And("t.reckondate", r.ReportDate) } else { // 月报表 sqlId.FormatParam("REPORT_ERMCP_AREASPOTPL") sqlId.And("t.cycletype", 1) sqlId.And("t.cycletime", r.ReportDate) } if r.WRSTANDARDID > 0 { sqlId.And("t.wrstandardid", r.WRSTANDARDID) } if r.SPOTGOODSBRANDID > 0 { sqlId.And("t.spotgoodsbrandid", r.SPOTGOODSBRANDID) } if r.SPOTGOODSMODELID > 0 { sqlId.And("t.spotgoodsmodelid", r.SPOTGOODSMODELID) } return sqlId.String() } // GetDataEx 获取现货损益日(月)报表 func (r *ErmcpReportAreaSpotPL) GetDataEx() (interface{}, error) { sData := make([]ErmcpReportAreaSpotPL, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) for i := range sData { sData[i].calc() } return sData, err }