/** * @Author: zou.yingbin * @Create : 2021/3/10 9:13 * @Modify : 2021/3/10 9:13 */ package models import ( "fmt" "mtp2_if/db" "mtp2_if/mtpcache" "mtp2_if/utils" ) // ErmcpAreaStockApply 出入库申请 type ErmcpAreaStockApply struct { INOUTAPPLYID string `json:"inoutapplyid" xorm:"'INOUTAPPLYID'"` // 申请ID(607+Unix秒时间戳(10位)+xxxxxx) USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID INOUTTYPE int32 `json:"inouttype" xorm:"'INOUTTYPE'"` // 出入库类型 - 1:采购入库 2:销售出库 3:生产入库 4:生产出库 WRSTANDARDID string `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID SPOTGOODSMODELID int32 `json:"spotgoodsmodelid" xorm:"'SPOTGOODSMODELID'"` // 现货型号ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种ID SPOTCONTRACTID string `json:"spotcontractid" xorm:"'SPOTCONTRACTID'"` // 关联现货合同ID WAREHOUSEINFOID string `json:"warehouseinfoid" xorm:"'WAREHOUSEINFOID'"` // 现货仓库ID QTY float64 `json:"qty" xorm:"'QTY'"` // 数量 APPLYSTATUS int32 `json:"applystatus" xorm:"'APPLYSTATUS'"` // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回 APPLYSRC int32 `json:"applysrc" xorm:"'APPLYSRC'"` // 申请来源 - 1:管理端 2:终端 APPLYID int64 `json:"applyid" xorm:"'APPLYID'"` // 申请人 APPLYREMARK string `json:"applyremark" xorm:"'APPLYREMARK'"` // 申请备注 APPLYTIME string `json:"applytime" xorm:"'APPLYTIME'"` // 申请时间 AUDITSRC int32 `json:"auditsrc" xorm:"'AUDITSRC'"` // 审核来源 - 1:管理端 2:终端 AUDITID int64 `json:"auditid" xorm:"'AUDITID'"` // 审核人 AUDITTIME string `json:"audittime" xorm:"'AUDITTIME'"` // 审核时间 AUDITREMARK string `json:"auditremark" xorm:"'AUDITREMARK'"` // 审核备注 AUDITTRADEDATE string `json:"audittradedate" xorm:"'AUDITTRADEDATE'"` // 审核交易日(yyyyMMdd) WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'MODELNAME'"` // 型号名称 WAREHOUSENAME string `json:"warehousename" xorm:"'WAREHOUSENAME'"` // 仓库名称 WAREHOUSECODE string `json:"warehousecode" xorm:"'WAREHOUSECODE'"` // 仓库代码 WAREHOUSETYPE int32 `json:"warehousetype" xorm:"'WAREHOUSETYPE'"` // 仓库类型 - 1 厂库 2 自有库 3 合作库 CONTRACTNO string `json:"contractno" xorm:"'CONTRACTNO'"` // 合同编号 CONTRACTTYPE int32 `json:"contracttype" xorm:"'CONTRACTTYPE'"` // 现货合同类型 - 1:采购 -1:销售 BUYUSERID int64 `json:"buyuserid" xorm:"'BUYUSERID'"` // 采购方userid SELLUSERID int64 `json:"selluserid" xorm:"'SELLUSERID'"` // 销售方userid PRICETYPE int32 `json:"pricetype" xorm:"'PRICETYPE'"` // 定价类型 - 1:一口价 2:点价 3:暂定价 CONTRACTQTY float64 `json:"contractqty" xorm:"'CONTRACTQTY'"` // 合同量 BUYUSERNAME string `json:"buyusername"` // 采购方名称 SELLUSERNAME string `json:"sellusername"` // 销售方名称 APPLYNAME string `json:"applyname"` // 申请人名称 AUDITNAME string `json:"auditname"` // 审核人名称 ENUMDICNAME string `json:"enumdicname"` // 现货商品单位名称 FilterStatus string `json:"-"` // 查询条件, 申请状态, 逗号隔开 FilterType string `json:"-"` // 查询条件, 出入库类型, 逗号隔开 BeginDate string `json:"-"` // 开始日期 EndDate string `json:"-"` // 结束日期 } func (r *ErmcpAreaStockApply) calc() { // 采购方名称 r.BUYUSERNAME = mtpcache.GetUserNameByUserId(r.BUYUSERID) // 销售方名称 r.SELLUSERNAME = mtpcache.GetUserNameByUserId(r.SELLUSERID) // 申请人名称 if r.APPLYSRC == 1 { r.APPLYNAME = mtpcache.GetSystemmangerLoginCode(r.APPLYID) } else { r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID) } // 审核人名称 if r.AUDITSRC == 1 { r.AUDITNAME = mtpcache.GetSystemmangerLoginCode(r.AUDITID) } else { r.AUDITNAME = mtpcache.GetLoginCodeByLoginId(r.AUDITID) } // 单位名称 r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) } func (r *ErmcpAreaStockApply) buildSql() string { var sqlId utils.SQLVal = "SELECT to_char(t.INOUTAPPLYID) INOUTAPPLYID," + " t.USERID," + " t.INOUTTYPE," + " t.WRSTANDARDID," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.DELIVERYGOODSID," + " to_char(t.SPOTCONTRACTID) SPOTCONTRACTID," + " to_char(t.WAREHOUSEINFOID) WAREHOUSEINFOID," + " t.QTY," + " t.APPLYSTATUS," + " t.APPLYSRC," + " t.APPLYID," + " t.APPLYREMARK," + " to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME," + " t.AUDITSRC," + " t.AUDITID," + " to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," + " t.AUDITREMARK," + " t.AUDITTRADEDATE," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.brandname," + " gm.modelname," + " h.warehousename," + " h.warehousecode," + " h.warehousetype," + " s.contractno," + " s.contracttype," + " s.buyuserid," + " s.selluserid," + " s.pricetype," + " s.qty CONTRACTQTY" + " FROM ERMCP_AREAINOUTSTOCKAPPLY 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" + " left join warehouseinfo h" + " on t.warehouseinfoid = h.autoid" + " left join ermcp_spotcontract s" + " on t.spotcontractid = s.spotcontractid" + " WHERE 1 = 1" // 用户id(必要条件) sqlId.And("t.USERID", r.USERID) // 合同id if len(r.SPOTCONTRACTID) > 0 { sqlId.And("t.SPOTCONTRACTID", r.SPOTCONTRACTID) } // 现货商品id if len(r.WRSTANDARDID) > 0 { sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID) } // 品牌id if r.SPOTGOODSBRANDID > 0 { sqlId.And("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID) } // 型号id if r.SPOTGOODSMODELID > 0 { sqlId.And("t.SPOTGOODSMODELID", r.SPOTGOODSMODELID) } // 仓库id if len(r.WAREHOUSEINFOID) > 0 { sqlId.And("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID) } // 日期范围 if len(r.BeginDate) > 0 { if r.BeginDate == r.EndDate { sqlId.And("t.audittradedate", r.BeginDate) } else if r.EndDate > r.BeginDate { sqlId.BiggerOrEq("t.audittradedate", r.BeginDate) sqlId.LessOrEq("t.audittradedate", r.EndDate) } } // 出入库类型 if len(r.FilterType) > 0 { if len(r.FilterType) == 1 { sqlId.And("t.INOUTTYPE", r.FilterType) } else { sqlId.Join(fmt.Sprintf(" and t.INOUTTYPE in(%v)", r.FilterType)) } } // 出入库状态 if len(r.FilterStatus) > 0 { sqlId.Join(fmt.Sprintf(" and t.APPLYSTATUS in(%v)", r.FilterStatus)) } // 排序 sqlId.Join(" order by t.APPLYTIME desc") return sqlId.String() } // 查询库存申请(记录) func (r *ErmcpAreaStockApply) GetDataEx() (interface{}, error) { sData := make([]ErmcpAreaStockApply, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) if err == nil { for i := range sData { sData[i].calc() } } return sData, err } // ErmcpAreaStockApplySum 合同出入库数量汇总 type ErmcpAreaStockApplySum struct { INOUTTYPE int32 `json:"inouttype" xorm:"'INOUTTYPE'"` // 出入库类型 - 1:采购入库 2:销售出库 3:生产入库 4:生产出库 SPOTGOODSMODELID int32 `json:"spotgoodsmodelid" xorm:"'SPOTGOODSMODELID'"` // 型号id SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 品牌id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'MODELNAME'"` // 型号名称 TOTALQTY float64 `json:"totalqty" xorm:"'TOTALQTY'"` // 总数量 SPOTCONTRACTID string `json:"spotcontractid" xorm:"'SPOTCONTRACTID'"` // 关联现货合同ID } func (r *ErmcpAreaStockApplySum) buildSql() string { sqlId := "select a.*, gb.brandname, gm.modelname" + " from (SELECT t.INOUTTYPE," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.SPOTCONTRACTID," + " sum(t.QTY) TOTALQTY" + " FROM ERMCP_AREAINOUTSTOCKAPPLY t" + " WHERE t.applystatus= 2 and t.spotcontractid = %v" + " group by t.inouttype, t.spotgoodsbrandid, t.spotgoodsmodelid, t.spotcontractid) a" + " left join spotgoodsbrand gb" + " on a.spotgoodsbrandid = gb.brandid" + " left join spotgoodsmodel gm" + " on a.spotgoodsmodelid = gm.modelid" sqlId = fmt.Sprintf(sqlId, r.SPOTCONTRACTID) // 不关联合同id sqlId2 := "select a.*, gb.brandname, gm.modelname" + " from (SELECT t.INOUTTYPE," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.SPOTCONTRACTID," + " sum(t.QTY) TOTALQTY" + " FROM ERMCP_AREAINOUTSTOCKAPPLY t" + " WHERE t.applystatus= 2" + " group by t.inouttype, t.spotgoodsbrandid, t.spotgoodsmodelid, t.SPOTCONTRACTID) a" + " left join spotgoodsbrand gb" + " on a.spotgoodsbrandid = gb.brandid" + " left join spotgoodsmodel gm" + " on a.spotgoodsmodelid = gm.modelid" if len(r.SPOTCONTRACTID) == 0 { return sqlId2 } return sqlId } // GetDataEx 获取合同出入库汇总信息 func (r *ErmcpAreaStockApplySum) GetDataEx() (interface{}, error) { sData := make([]ErmcpAreaStockApplySum, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) return sData, err } // ErmcpAreaStock 机构库存表 type ErmcpAreaStock struct { TODAYBUYINQTY float64 `json:"todaybuyinqty" xorm:"'TODAYBUYINQTY'"` // 今日采购入库量 TODAYPRODUCEINQTY float64 `json:"todayproduceinqty" xorm:"'TODAYPRODUCEINQTY'"` // 今日生产入库量 TODAYSELLOUTQTY float64 `json:"todayselloutqty" xorm:"'TODAYSELLOUTQTY'"` // 今日销售出库量 TODAYPRODUCEOUTQTY float64 `json:"todayproduceoutqty" xorm:"'TODAYPRODUCEOUTQTY'"` // 今日生产出库量 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID WRSTANDARDID string `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID SPOTGOODSMODELID int32 `json:"spotgoodsmodelid" xorm:"'SPOTGOODSMODELID'"` // 现货型号ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID WAREHOUSEINFOID string `json:"warehouseinfoid" xorm:"'WAREHOUSEINFOID'"` // 仓库ID DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种ID ORISTOCK float64 `json:"oristock" xorm:"'ORISTOCK'"` // 期初库存量(昨日量) CURSTOCK float64 `json:"curstock" xorm:"'CURSTOCK'"` // 期末库存量(今日量) WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'MODELNAME'"` // 型号名称 WAREHOUSENAME string `json:"warehousename" xorm:"'WAREHOUSENAME'"` // 仓库名称 WAREHOUSECODE string `json:"warehousecode" xorm:"'WAREHOUSECODE'"` // 仓库代码 WAREHOUSETYPE int32 `json:"warehousetype" xorm:"'WAREHOUSETYPE'"` // 仓库类型 - 1 厂库 2 自有库 3 合作库 USERNAME string `json:"username"` // 机构名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称 } func (r *ErmcpAreaStock) calc() { r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) } func (r *ErmcpAreaStock) buildSql() string { var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," + " t.TODAYPRODUCEINQTY," + " t.TODAYSELLOUTQTY," + " t.TODAYPRODUCEOUTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.USERID," + " t.WRSTANDARDID," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.WAREHOUSEINFOID," + " t.DELIVERYGOODSID," + " t.ORISTOCK," + " t.CURSTOCK," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.brandname," + " gm.modelname," + " h.warehousename," + " h.warehousecode," + " h.warehousetype" + " FROM ERMCP_AREASTOCK 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" + " LEFT JOIN WAREHOUSEINFO h" + " on t.warehouseinfoid = h.autoid" + " WHERE 1 = 1" sqlId.And("t.USERID", r.USERID) sqlId.Join(" order by t.WRSTANDARDID, t.UPDATETIME desc") return sqlId.String() } // GetDataEx 获取机构库存信息 func (r *ErmcpAreaStock) GetDataEx() (interface{}, error) { sData := make([]ErmcpAreaStock, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) if err == nil { for i := range sData { sData[i].calc() } } return sData, err } // ErmcpAreaStockReport 库存报表 type ErmcpAreaStockReport struct { USERID int64 `json:"userid" xorm:"'USERID'"` // 机构ID WRSTANDARDID string `json:"wrstandardid" xorm:"'WRSTANDARDID'"` // 现货商品ID SPOTGOODSMODELID int32 `json:"spotgoodsmodelid" xorm:"'SPOTGOODSMODELID'"` // 现货型号ID SPOTGOODSBRANDID int32 `json:"spotgoodsbrandid" xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID WAREHOUSEINFOID string `json:"warehouseinfoid" xorm:"'WAREHOUSEINFOID'"` // 仓库ID DELIVERYGOODSID int64 `json:"deliverygoodsid" xorm:"'DELIVERYGOODSID'"` // 现货品种ID ORISTOCK float64 `json:"oristock" xorm:"'ORISTOCK'"` // 期初库存量 CURSTOCK float64 `json:"curstock" xorm:"'CURSTOCK'"` // 期末库存量 TODAYBUYINQTY float64 `json:"todaybuyinqty" xorm:"'TODAYBUYINQTY'"` // 今日采购入库量 TODAYPRODUCEINQTY float64 `json:"todayproduceinqty" xorm:"'TODAYPRODUCEINQTY'"` // 今日生产入库量 TODAYSELLOUTQTY float64 `json:"todayselloutqty" xorm:"'TODAYSELLOUTQTY'"` // 今日销售出库量 TODAYPRODUCEOUTQTY float64 `json:"todayproduceoutqty" xorm:"'TODAYPRODUCEOUTQTY'"` // 今日生产出库量 UPDATETIME string `json:"updatetime" xorm:"'UPDATETIME'"` // 更新时间 WRSTANDARDNAME string `json:"wrstandardname" xorm:"'WRSTANDARDNAME'"` // 现货商品名称 WRSTANDARDCODE string `json:"wrstandardcode" xorm:"'WRSTANDARDCODE'"` // 现货商品代码 UNITID int32 `json:"unitid" xorm:"'UNITID'"` // 单位id BRANDNAME string `json:"brandname" xorm:"'BRANDNAME'"` // 品牌名称 MODELNAME string `json:"modelname" xorm:"'MODELNAME'"` // 型号名称 WAREHOUSENAME string `json:"warehousename" xorm:"'WAREHOUSENAME'"` // 仓库名称 WAREHOUSECODE string `json:"warehousecode" xorm:"'WAREHOUSECODE'"` // 仓库代码 WAREHOUSETYPE int32 `json:"warehousetype" xorm:"'WAREHOUSETYPE'"` // 仓库类型 - 1 厂库 2 自有库 3 合作库 USERNAME string `json:"username"` // 机构名称 ENUMDICNAME string `json:"enumdicname"` // 单位名称 ReportType int32 `json:"-"` // 报表类型 1-日报表 2-月报表 ReportDate string `json:"-"` // 格式 日报表(YYYYMMDD) 月报表(YYYYMM) } func (r *ErmcpAreaStockReport) calc() { r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID) r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID) } func (r *ErmcpAreaStockReport) buildSql() string { var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," + " t.TODAYPRODUCEINQTY," + " t.TODAYSELLOUTQTY," + " t.TODAYPRODUCEOUTQTY," + " to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," + " t.USERID," + " t.WRSTANDARDID," + " t.SPOTGOODSMODELID," + " t.SPOTGOODSBRANDID," + " t.WAREHOUSEINFOID," + " t.DELIVERYGOODSID," + " t.ORISTOCK," + " t.CURSTOCK," + " w.wrstandardname," + " w.wrstandardcode," + " w.unitid," + " gb.brandname," + " gm.modelname," + " h.warehousename," + " h.warehousecode," + " h.warehousetype" + " 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" + " LEFT JOIN WAREHOUSEINFO h" + " on t.warehouseinfoid = h.autoid" + " WHERE 1 = 1" sqlId.And("t.USERID", r.USERID) if r.ReportType == 1 { // 日报表 sqlId.FormatParam("RECKON_ERMCP_AREASTOCK") sqlId.And("t.reckondate", r.ReportDate) } else { // 月报表 sqlId.FormatParam("REPORT_ERMCP_AREASTOCK") sqlId.And("t.cycletype", 1) sqlId.And("t.cycletime", r.ReportDate) } // 现货商品id if len(r.WRSTANDARDID) > 0 { sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID) } // 品牌id if r.SPOTGOODSBRANDID > 0 { sqlId.And("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID) } // 型号id if r.SPOTGOODSMODELID > 0 { sqlId.And("t.SPOTGOODSMODELID", r.SPOTGOODSMODELID) } // 仓库id if len(r.WAREHOUSEINFOID) > 0 { sqlId.And("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID) } return sqlId.String() } // GetDataEx 查询库存报表 func (r *ErmcpAreaStockReport) GetDataEx() (interface{}, error) { sData := make([]ErmcpAreaStockReport, 0) err := db.GetEngine().SQL(r.buildSql()).Find(&sData) if err == nil { for i := range sData { sData[i].calc() } } return sData, err }