485 lines
22 KiB
C#
485 lines
22 KiB
C#
using System;
|
||
using System.Data;
|
||
using System.Configuration;
|
||
using System.Web;
|
||
using System.Web.Security;
|
||
using System.Web.UI;
|
||
using System.Web.UI.WebControls;
|
||
using System.Web.UI.WebControls.WebParts;
|
||
using System.Web.UI.HtmlControls;
|
||
using GThinking.N_MODEL.Page;
|
||
using GThinking.N_MISPRO.Utility;
|
||
using GThinking.N_MISPRO.Data;
|
||
using GThinking.N_MISPRO.TableCache;
|
||
using GThinking.N_PUBLIC2;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
#region 数据表级映射
|
||
[TableMap(DB = "DBERP", DbTableName = "XLXSWLHQ", Umid = 100860032, IsWorkFlow = false, IsAttach = false, UseWhr = true,
|
||
|
||
QuerySelSql = "select XLXSWLHQ_WLID, WL_MC,WL_GG,WL_TZ,WL_CL,WL_JLDW,XLXSBJCL_BJCLMC " + "\r\n"
|
||
+ " isnull(XLXSWLHQ_WSJG,0.0) as XLXSWLHQ_WSJG,isnull(XLXSWLHQ_HSJG,0.0) as XLXSWLHQ_HSJG, XLXSWLHQ_YXQS,"
|
||
+ " isnull(XLXSWLHQ_HSJGF,0.0) as XLXSWLHQ_HSJGF , isnull(XLXSWLHQ_WSJGF,0.0) as XLXSWLHQ_WSJGF, " + "\r\n"
|
||
+ " isnull(XLXSWLHQ_HSCGJG,0.0) as XLXSWLHQ_HSCGJG, isnull(XLXSWLHQ_WSCGJG,0.0) as XLXSWLHQ_WSCGJG," + "\r\n"
|
||
+ " XLXSWLHQ_YXQZ,isnull(XLXSWLHQ_BZ, ' ') as XLXSWLHQ_BZ,isnull(XLXSWLHQ_WHRID, ' ') as XLXSWLHQ_WHRID, isnull(XLXSWLHQ_WHR, ' ') as XLXSWLHQ_WHR, XLXSWLHQ_WHSJ " + "\r\n"
|
||
+ " from XLXSBJCLWL " + "\r\n"
|
||
+ " left join XLXSBJCLWL on XLXSBJCLWL_WLID = XLXSWLHQ_WLID" + "\r\n"
|
||
+ " left join WL on WL_WLID=XLXSBJCLWL_WLID" + "\r\n"
|
||
+ " left join XLXSBJCL on XLXSBJCL_BJCLID=XLXSBJCLWL_BJCLID" + "\r\n"
|
||
+ " where 1=1 and XLXSWLHQ_JGBID =' ' ",
|
||
|
||
EntitySelSql = " select XLXSWLHQ_JGBID, XLXSBJCLWL_WLID as XLXSWLHQ_WLID, XLXSWLHQ_WLID as OLD_XLXSWLHQ_WLID, WL_MC,WL_GG,WL_TZ,WL_CL,XLXSBJCLWL_BJCLID, XLXSBJCL_BJCLMC, "
|
||
+ " isnull(XLXSWLHQ_WSJG,0.0) as XLXSWLHQ_WSJG,isnull(XLXSWLHQ_HSJG,0.0) as XLXSWLHQ_HSJG, XLXSWLHQ_YXQS,XLXSWLHQ_YXQS as OLD_XLXSWLHQ_YXQS, " + "\r\n"
|
||
+ " isnull(XLXSWLHQ_HSJGF,0.0) as XLXSWLHQ_HSJGF , isnull(XLXSWLHQ_WSJGF,0.0) as XLXSWLHQ_WSJGF, " + "\r\n"
|
||
+ " isnull(XLXSWLHQ_HSCGJG,0.0) as XLXSWLHQ_HSCGJG, isnull(XLXSWLHQ_WSCGJG,0.0) as XLXSWLHQ_WSCGJG," + "\r\n"
|
||
+ " isnull(XLXSWLHQ_WSJG1,0.0) as XLXSWLHQ_WSJG1,isnull(XLXSWLHQ_HSJG1,0.0) as XLXSWLHQ_HSJG1, " + "\r\n"
|
||
+ " XLXSWLHQ_JGYBJCLID1,isnull(XLXSWLHQ_JGBL1,0.0) as XLXSWLHQ_JGBL1, " + "\r\n"
|
||
+ " XLXSWLHQ_YXQZ, isnull(XLXSWLHQ_BZ, ' ') as XLXSWLHQ_BZ, isnull(XLXSWLHQ_WHRID, ' ') as XLXSWLHQ_WHRID, isnull(XLXSWLHQ_WHR, ' ') as XLXSWLHQ_WHR, XLXSWLHQ_WHSJ, isnull(XLXSBJCL_TYBZ,'N') as XLXSBJCL_TYBZ " + "\r\n"
|
||
+ " from XLXSBJCLWL " + "\r\n"
|
||
+ " left join XLXSWLHQ on XLXSWLHQ_WLID = XLXSBJCLWL_WLID and XLXSWLHQ_YXQS =:OLD_XLXSWLHQ_YXQS and XLXSWLHQ_JGBID =' ' " + "\r\n"
|
||
+ " left join WL on WL_WLID=XLXSBJCLWL_WLID" + "\r\n"
|
||
+ " left join XLXSBJCL on XLXSBJCL_BJCLID=XLXSBJCLWL_BJCLID" + "\r\n"
|
||
+ " where XLXSBJCLWL_WLID =:OLD_XLXSWLHQ_WLID ",
|
||
|
||
UpdateSql = " update XLXSBJCLWL set XLXSBJCLWL_WLID = :OLD_XLXSWLHQ_WLID where XLXSBJCLWL_WLID = :OLD_XLXSWLHQ_WLID ",
|
||
|
||
InsertSql = " insert into XLXSWLHQ( " + "\r\n"
|
||
+ " XLXSWLHQ_WLID,XLXSWLHQ_JGBID, XLXSWLHQ_WSJG, XLXSWLHQ_HSJG, XLXSWLHQ_YXQS, XLXSWLHQ_YXQZ, " + "\r\n"
|
||
+ " XLXSWLHQ_BZ, XLXSWLHQ_WHRID, XLXSWLHQ_WHR, XLXSWLHQ_WHSJ, XLXSWLHQ_HSJGF, XLXSWLHQ_WSJGF " + "\r\n"
|
||
+ " XLXSWLHQ_HSCGJG,XLXSWLHQ_WSCGJG ) " + "\r\n"
|
||
+ " values( " + "\r\n"
|
||
+ " :XLXSWLHQ_WLID,' ', :XLXSWLHQ_WSJG, :XLXSWLHQ_HSJG, :XLXSWLHQ_YXQS, :XLXSWLHQ_YXQZ, " + "\r\n"
|
||
+ " :XLXSWLHQ_BZ, :XLXSWLHQ_WHRID, :XLXSWLHQ_WHR, :XLXSWLHQ_WHSJ,:XLXSWLHQ_HSJGF, :XLXSWLHQ_WSJGF " + "\r\n"
|
||
+ " :XLXSWLHQ_HSCGJG,:XLXSWLHQ_WSCGJG ) ",
|
||
|
||
DeleteSql = " delete from XLXSWLHQ where XLXSWLHQ_WLID =:OLD_XLXSWLHQ_WLID and XLXSWLHQ_YXQS =:OLD_XLXSWLHQ_YXQS and XLXSWLHQ_JGBID =' ' ")]
|
||
|
||
#endregion
|
||
|
||
public class XLXSWLHQ : EntityManager
|
||
{
|
||
#region 字段级映射
|
||
|
||
private string _XLXSWLHQ_JGBID;
|
||
[ColumnMap(DbField = "XLXSWLHQ_JGBID", CtrlName = "", DbTitle = "价格表", Type = FieldType.HiddenField, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = true, Length = 10)]
|
||
public string XLXSWLHQ_JGBID
|
||
{
|
||
get { return _XLXSWLHQ_JGBID; }
|
||
set { _XLXSWLHQ_JGBID = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WLID;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WLID", CtrlName = "txtWLID", DbTitle = "物料编码", Type = FieldType.C, DefaultValue = "",
|
||
IsAllowNull = false, IsKey = true, Length = 20)]
|
||
public string XLXSWLHQ_WLID
|
||
{
|
||
get { return _XLXSWLHQ_WLID; }
|
||
set { _XLXSWLHQ_WLID = value; }
|
||
}
|
||
|
||
|
||
private string _OLD_XLXSWLHQ_WLID;
|
||
[ColumnMap(DbField = "OLD_XLXSWLHQ_WLID", CtrlName = "txtOLD_WLID", DbTitle = "原物料编码", Type = FieldType.HiddenField, DefaultValue = "",
|
||
IsAllowNull = false, IsKey = false, Length = 20, PrintVisible = false)]
|
||
public string OLD_XLXSWLHQ_WLID
|
||
{
|
||
get { return _OLD_XLXSWLHQ_WLID; }
|
||
set { _OLD_XLXSWLHQ_WLID = value; }
|
||
}
|
||
|
||
private string _WL_MC;
|
||
[ColumnMap(DbField = "WL_MC", CtrlName = "txtWLMC", Type = FieldType.C, DbTitle = "物料名称", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 80)]
|
||
public string WL_MC
|
||
{
|
||
get { return _WL_MC; }
|
||
set { _WL_MC = value; }
|
||
}
|
||
|
||
private string _WL_GG;
|
||
[ColumnMap(DbField = "WL_GG", CtrlName = "txtWLGG", Type = FieldType.C, DbTitle = "规格型号", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 80)]
|
||
public string WL_GG
|
||
{
|
||
get { return _WL_GG; }
|
||
set { _WL_GG = value; }
|
||
}
|
||
|
||
private string _WL_TZ;
|
||
[ColumnMap(DbField = "WL_TZ", CtrlName = "txtWLTZ", Type = FieldType.C, DbTitle = "图号", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 50)]
|
||
public string WL_TZ
|
||
{
|
||
get { return _WL_TZ; }
|
||
set { _WL_TZ = value; }
|
||
}
|
||
|
||
private string _WL_CL;
|
||
[ColumnMap(DbField = "WL_CL", CtrlName = "txtWLCL", Type = FieldType.C, DbTitle = "材质", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 30)]
|
||
public string WL_CL
|
||
{
|
||
get { return _WL_CL; }
|
||
set { _WL_CL = value; }
|
||
}
|
||
|
||
private string _XLXSBJCLWL_BJCLID;
|
||
[ColumnMap(DbField = "XLXSBJCLWL_BJCLID", CtrlName = "cbxBJCL", DbTitle = "报价材料", Type = FieldType.CBX, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 20)]
|
||
public string XLXSBJCLWL_BJCLID
|
||
{
|
||
get { return _XLXSBJCLWL_BJCLID; }
|
||
set { _XLXSBJCLWL_BJCLID = value; }
|
||
}
|
||
|
||
|
||
|
||
private string _XLXSWLHQ_WSJG;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WSJG", CtrlName = "txtWSJG", DbTitle = "无税价格（元/Kg）", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_WSJG
|
||
{
|
||
get { return _XLXSWLHQ_WSJG; }
|
||
set { _XLXSWLHQ_WSJG = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_HSJG;
|
||
[ColumnMap(DbField = "XLXSWLHQ_HSJG", CtrlName = "txtHSJG", DbTitle = "材料价格（元/Kg）", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_HSJG
|
||
{
|
||
get { return _XLXSWLHQ_HSJG; }
|
||
set { _XLXSWLHQ_HSJG = value; }
|
||
}
|
||
|
||
//加工费
|
||
private string _XLXSWLHQ_HSJGF;
|
||
[ColumnMap(DbField = "XLXSWLHQ_HSJGF", CtrlName = "txtHSJGF", DbTitle = "含税加工费", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_HSJGF
|
||
{
|
||
get { return _XLXSWLHQ_HSJGF; }
|
||
set { _XLXSWLHQ_HSJGF = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WSJGF;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WSJGF", CtrlName = "txtWSJGF", DbTitle = "无税加工费", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_WSJGF
|
||
{
|
||
get { return _XLXSWLHQ_WSJGF; }
|
||
set { _XLXSWLHQ_WSJGF = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_HSCGJG;
|
||
[ColumnMap(DbField = "XLXSWLHQ_HSCGJG", CtrlName = "txtHSCGJG", DbTitle = "含税采购价格", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_HSCGJG
|
||
{
|
||
get { return _XLXSWLHQ_HSCGJG; }
|
||
set { _XLXSWLHQ_HSCGJG = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WSCGJG;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WSCGJG", CtrlName = "txtWSCGJG", DbTitle = "无税采购价格", Type = FieldType.F, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 0)]
|
||
public string XLXSWLHQ_WSCGJG
|
||
{
|
||
get { return _XLXSWLHQ_WSCGJG; }
|
||
set { _XLXSWLHQ_WSCGJG = value; }
|
||
}
|
||
|
||
|
||
private string _XLXSWLHQ_YXQS;
|
||
[ColumnMap(DbField = "XLXSWLHQ_YXQS", CtrlName = "datYXQS", Type = FieldType.D, DbTitle = "有效期始", DefaultValue = "",
|
||
IsAllowNull = false, IsKey = true, Length = 100)]
|
||
public string XLXSWLHQ_YXQS
|
||
{
|
||
get { return _XLXSWLHQ_YXQS; }
|
||
set { _XLXSWLHQ_YXQS = value; }
|
||
}
|
||
|
||
|
||
private string _OLD_XLXSWLHQ_YXQS;
|
||
[ColumnMap(DbField = "OLD_XLXSWLHQ_YXQS", CtrlName = "txtOLD_YXQS", Type = FieldType.HiddenField, DbTitle = "原有效期始", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 100, PrintVisible = false)]
|
||
public string OLD_XLXSWLHQ_YXQS
|
||
{
|
||
get { return _OLD_XLXSWLHQ_YXQS; }
|
||
set { _OLD_XLXSWLHQ_YXQS = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_YXQZ;
|
||
[ColumnMap(DbField = "XLXSWLHQ_YXQZ", CtrlName = "datYXQZ", Type = FieldType.D, DbTitle = "有效期止", DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 100)]
|
||
public string XLXSWLHQ_YXQZ
|
||
{
|
||
get { return _XLXSWLHQ_YXQZ; }
|
||
set { _XLXSWLHQ_YXQZ = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_BZ;
|
||
[ColumnMap(DbField = "XLXSWLHQ_BZ", CtrlName = "txtBZ", DbTitle = "备注", Type = FieldType.C, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 250)]
|
||
public string XLXSWLHQ_BZ
|
||
{
|
||
get { return _XLXSWLHQ_BZ; }
|
||
set { _XLXSWLHQ_BZ = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WHR;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WHR", CtrlName = "txtWHR", DbTitle = "维护人", Type = FieldType.C, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 30)]
|
||
public string XLXSWLHQ_WHR
|
||
{
|
||
get { return _XLXSWLHQ_WHR; }
|
||
set { _XLXSWLHQ_WHR = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WHRID;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WHRID", CtrlName = "txtWHRID", DbTitle = "维护人工号", Type = FieldType.HiddenField, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 10)]
|
||
public string XLXSWLHQ_WHRID
|
||
{
|
||
get { return _XLXSWLHQ_WHRID; }
|
||
set { _XLXSWLHQ_WHRID = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WHSJ;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WHSJ", CtrlName = "datWHSJ", DbTitle = "维护时间", Type = FieldType.DT, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 10)]
|
||
public string XLXSWLHQ_WHSJ
|
||
{
|
||
get { return _XLXSWLHQ_WHSJ; }
|
||
set { _XLXSWLHQ_WHSJ = value; }
|
||
}
|
||
|
||
private string _XLXSBJCL_TYBZ;
|
||
[ColumnMap(DbField = "XLXSBJCL_TYBZ", CtrlName = "txtTYBZ", DbTitle = "停用标志", Type = FieldType.HiddenField, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 1)]
|
||
public string XLXSBJCL_TYBZ
|
||
{
|
||
get { return _XLXSBJCL_TYBZ; }
|
||
set { _XLXSBJCL_TYBZ = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_WSJG1;
|
||
[ColumnMap(DbField = "XLXSWLHQ_WSJG1", CtrlName = "txtJGYLWSJG1", DbTitle = "加工原料无税价格1", Type = FieldType.HiddenField, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 10)]
|
||
public string XLXSWLHQ_WSJG1
|
||
{
|
||
get { return _XLXSWLHQ_WSJG1; }
|
||
set { _XLXSWLHQ_WSJG1 = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_HSJG1;
|
||
[ColumnMap(DbField = "XLXSWLHQ_HSJG1", CtrlName = "txtJGYLHSJG1", DbTitle = "加工原料含税价格1", Type = FieldType.HiddenField, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 10)]
|
||
public string XLXSWLHQ_HSJG1
|
||
{
|
||
get { return _XLXSWLHQ_HSJG1; }
|
||
set { _XLXSWLHQ_HSJG1 = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_JGBL1;
|
||
[ColumnMap(DbField = "XLXSWLHQ_JGBL1", CtrlName = "txtJGBL1", DbTitle = "价格比例1", Type = FieldType.HiddenField, DefaultValue = "0",
|
||
IsAllowNull = true, IsKey = false, Length = 10)]
|
||
public string XLXSWLHQ_JGBL1
|
||
{
|
||
get { return _XLXSWLHQ_JGBL1; }
|
||
set { _XLXSWLHQ_JGBL1 = value; }
|
||
}
|
||
|
||
private string _XLXSWLHQ_JGYBJCLID1;
|
||
[ColumnMap(DbField = "XLXSWLHQ_JGYBJCLID1", CtrlName = "txtJGYBJCLID1", DbTitle = "加工原报价材料1", Type = FieldType.HiddenField, DefaultValue = "",
|
||
IsAllowNull = true, IsKey = false, Length = 20)]
|
||
public string XLXSWLHQ_JGYBJCLID1
|
||
{
|
||
get { return _XLXSWLHQ_JGYBJCLID1; }
|
||
set { _XLXSWLHQ_JGYBJCLID1 = value; }
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 公用函数
|
||
|
||
/// <summary>
|
||
/// 判断质检单位是否被使用
|
||
/// </summary>
|
||
/// <param name="sJYDWID"></param>
|
||
/// <returns></returns>
|
||
public static bool isJYDWUsed(string sJYDWID)
|
||
{
|
||
if (string.IsNullOrEmpty(sJYDWID))
|
||
return false;
|
||
else
|
||
return CMISproDataBase.ExistsRecord("DBERP", string.Format("select 1 from RLMZCY where RLMZCY_CYDWID = '{0}'", sJYDWID));
|
||
}
|
||
|
||
/// <summary>
|
||
/// 判断模块是否存在并且是否有权限
|
||
/// </summary>
|
||
/// <param name="sUMID"></param>
|
||
/// <param name="sUserId"></param>
|
||
/// <returns>返回true 表示有权限</returns>
|
||
public static bool CheckModule(string sUMID, string sUserId)
|
||
{
|
||
int iUMID = CMISproConvert.StrToInt(sUMID);
|
||
UserRight UR = OpRightUtils.GetUserRight(sUserId, iUMID);
|
||
return (DSActiveModule.IsActiveModule(iUMID) && UR.Open);
|
||
}
|
||
|
||
/*
|
||
* 取“CGCX8-100486007|采购进价情况”模块下对应物料编码的“含税最新进价”、“无税最新进价”
|
||
*（查询条件“采购类型”选择“外购”、“查询对象”选择“结算价格”、“起始日期”“终止日期”设置为近一年
|
||
*/
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="bHSBZ"></param>
|
||
/// <param name="sWLID"></param>
|
||
/// <param name="sCGU009"></param>
|
||
/// <returns>bHSBZ为true表示含税，false表示无税</returns>
|
||
public static double GetCGPri(bool bHSBZ, string sWLID, string sCGU009)
|
||
{
|
||
//string sWhereJSD = @"
|
||
// and
|
||
//((CGJS_BMID='03')OR(CGJS_BMID='11')OR(CGJS_BMID='12')
|
||
//OR(CGJS_BMID='13')OR(CGJS_BMID='G2001')OR(CGJS_BMID='G2003')OR(CGJS_BMID='T0006')OR(CGJS_BMID='X1001')
|
||
//OR(CGJS_BMID='X1002')OR(CGJS_BMID='Z0000')OR(CGJS_BMID='ZB01')OR(CGJS_BMID='ZB02')OR(CGJS_BMID='ZB03')";
|
||
double fJG = 0;
|
||
string sHBID = "";
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Clear();
|
||
SQL.Add("select WLID,WLSX_SXXX,WL_MC,WL_GG,WL_TZ,WL_CL,JLDW,HBID,HB_MC,SHSL,SHJE,PJJG,ZJJG,ZGJG,ZXJG ");
|
||
SQL.Add(" from ( ");
|
||
|
||
SQL.Add("select X.WLID as WLID, WLSX_SXXX, WL_MC, WL_GG, WL_TZ, WL_CL, WL_CWJLDW as JLDW ,");
|
||
SQL.Add(" X.HBID as HBID, HB_MC, sum( X.SHSL) as SHSL ,");
|
||
SQL.Add(" sum( RoundFix(X.SHJE,2)) as SHJE ,");
|
||
SQL.Add(" Case RoundFix(sum( X.SHSL),3) When 0 Then 0 Else RoundFix(sum( X.SHJE) / sum( X.SHSL),4) End as PJJG ,");
|
||
SQL.Add(" min(Case X.SHSL When 0 Then 0 Else RoundFix(X.SHJE / X.SHSL,4) End) as ZJJG , ");
|
||
SQL.Add(" max(Case X.SHSL When 0 Then 0 Else RoundFix(X.SHJE / X.SHSL,4) End) as ZGJG ");
|
||
SQL.Add(" ,IsNull(A.ZXJJ,0) as ZXJG ");
|
||
SQL.Add("from (select CGJSMX_JSDID as SHDID, CGJSMX_WLID as WLID, CGJS_HBID as HBID, HB_MC , ");
|
||
SQL.Add(" sum(RoundFix(CGJSMX_FPSL * CGJSMX_HSXS,3)) as SHSL, ");
|
||
if (bHSBZ)
|
||
{
|
||
SQL.Add(" sum(RoundFix(CGJSMX_YBFPJE,2)) as SHJE ");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" sum(RoundFix(CGJSMX_YBWSJE,2)) as SHJE ");
|
||
}
|
||
SQL.Add(" from HB, CGJSMX ");
|
||
SQL.Add(" left join WL on WL_WLID=CGJSMX_WLID");
|
||
SQL.Add(" ,CGJS ");
|
||
SQL.Add(" left join GYS on GYS_GYSID=CGJS_GYSID ");
|
||
SQL.Add(" where CGJS_HBID = HB_HBID and CGJSMX_JSDID = CGJS_JSDID ");
|
||
SQL.Add(" and ( RoundFix(CGJSMX_BBRKJE,2) <> 0 or RoundFix(CGJSMX_RKSL,3) <> 0 ) ");
|
||
//
|
||
if (!sCGU009.Equals(""))
|
||
{
|
||
SQL.Add(" and " + CMISproDataBase.GetWhereEqual("CGJS_BMID", sCGU009));
|
||
}
|
||
SQL.Add(" and CGJSMX_KPRQ>=Convert(datetime,:pKSRQ)and CGJSMX_KPRQ<=Convert(datetime,:pJSRQ)and CGJS_LX='P'");
|
||
SQL.Add(" and WL_WLXT='K'");//只查实物库存物料
|
||
SQL.Add(" group by CGJSMX_JSDID, CGJSMX_WLID, CGJS_HBID, HB_MC ) X ");
|
||
SQL.Add("left join WL on WL_WLID = X.WLID");
|
||
SQL.Add("left join WLSX on WLSX_WLID = X.WLID");
|
||
SQL.Add("left join (select B.WLID as WLID ,B.HB as HB, ");
|
||
SQL.Add(" Max(Case RoundFix(SSSL,3) When 0 Then 0 Else RoundFix(JE / SSSL,4) End) as ZXJJ ");
|
||
SQL.Add(" from (select CGJSMX_JSDID, CGJSMX_WLID as WLID, CGJSMX_KPRQ as RQ , CGJS_HBID as HB, ");
|
||
SQL.Add(" sum(RoundFix(CGJSMX_FPSL * CGJSMX_HSXS,3)) as SSSL, ");
|
||
if (bHSBZ)
|
||
{
|
||
SQL.Add(" sum(RoundFix(CGJSMX_YBFPJE,2)) as JE");
|
||
}
|
||
else
|
||
{
|
||
SQL.Add(" sum(RoundFix(CGJSMX_YBWSJE,2)) as JE");
|
||
}
|
||
SQL.Add(" from CGJSMX, CGJS ");
|
||
SQL.Add(" left join GYS on GYS_GYSID=CGJS_GYSID ");
|
||
SQL.Add(" left join HB on HB_HBID=CGJS_HBID ");
|
||
SQL.Add(" where CGJS_JSDID = CGJSMX_JSDID ");
|
||
SQL.Add(" and ( RoundFix(CGJSMX_BBRKJE,2) <> 0 or RoundFix(CGJSMX_RKSL,3) <> 0 )");
|
||
//
|
||
if (!sCGU009.Equals(""))
|
||
{
|
||
SQL.Add(" and " + CMISproDataBase.GetWhereEqual("CGJS_BMID", sCGU009));
|
||
}
|
||
SQL.Add(" and CGJSMX_KPRQ>=Convert(datetime,:pKSRQ)and CGJSMX_KPRQ<=Convert(datetime,:pJSRQ)and CGJS_LX='P'");
|
||
SQL.Add(" group by CGJSMX_JSDID , CGJSMX_WLID , CGJSMX_KPRQ, CGJS_HBID ) C , ");
|
||
SQL.Add(" (select CGJSMX_WLID as WLID , max(CGJSMX_KPRQ) as RQ, CGJS_HBID as HB ");
|
||
SQL.Add(" from CGJSMX, CGJS ");
|
||
SQL.Add(" left join GYS on GYS_GYSID=CGJS_GYSID ");
|
||
SQL.Add(" left join HB on HB_HBID=CGJS_HBID ");
|
||
SQL.Add(" where CGJS_JSDID = CGJSMX_JSDID ");
|
||
//
|
||
if (!sCGU009.Equals(""))
|
||
{
|
||
SQL.Add(" and " + CMISproDataBase.GetWhereEqual("CGJS_BMID", sCGU009));
|
||
}
|
||
SQL.Add(" and CGJSMX_KPRQ>=Convert(datetime,:pKSRQ)and CGJSMX_KPRQ<=Convert(datetime,:pJSRQ)and CGJS_LX='P'");
|
||
SQL.Add(" group by CGJSMX_WLID, CGJS_HBID ) B ");
|
||
SQL.Add(" where C.WLID = B.WLID and C.RQ = B.RQ and C.HB = B. HB ");
|
||
SQL.Add(" group by B.WLID, B.HB ) A on A.WLID = X.WLID and A.HB = X.HBID ");
|
||
SQL.Add("where 1=1 ");
|
||
|
||
SQL.Add(" and WL_WLXT='K'");//只查实物库存物料
|
||
SQL.Add("group by X.WLID, WLSX_SXXX, WL_MC, WL_GG, WL_TZ, WL_CL, WL_CWJLDW, X.HBID, HB_MC, IsNull(A.ZXJJ,0)");
|
||
|
||
SQL.Add(" having ( sum(X.SHSL)<>0 or sum(RoundFix(X.SHJE ,2))<>0 ");
|
||
SQL.Add(" or min(Case X.SHSL When 0 Then 0 Else RoundFix(X.SHJE / X.SHSL,4) End)<>0 ");
|
||
SQL.Add(" or max(Case X.SHSL When 0 Then 0 Else RoundFix(X.SHJE / X.SHSL,4) End)<>0 ");
|
||
SQL.Add(" ) ");
|
||
|
||
SQL.Add(" ) c ");
|
||
SQL.Add(" where 1 = 1 ");
|
||
SQL.Add(" and c.WLID = :pWLID ");
|
||
SQL.Add("order by WLID");
|
||
SQL.ParamByName("pWLID").Value = sWLID;
|
||
SQL.ParamByName("pKSRQ").Value = CMISproConvert.ConvertDate(DateTime.Now.AddYears(-1));
|
||
SQL.ParamByName("pJSRQ").Value = CMISproConvert.ConvertDate(DateTime.Now);
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
//WLID WLSX_SXXX WL_MC WL_GG WL_TZ WL_CL JLDW HBID HB_MC SHSL SHJE PJJG ZJJG ZGJG ZXJG
|
||
fJG = CMISproConvert.StrToFloat(ds.Tables[0].Rows[0]["ZXJG"].ToString().Trim());
|
||
sHBID = ds.Tables[0].Rows[0]["HBID"].ToString().Trim();
|
||
}
|
||
//货币汇率
|
||
fJG = NumberUtils.RoundX(fJG * CGUtils.GetHBHL(sHBID), XLBJDUtils.BJCLPriceDecimal);
|
||
return fJG;
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
/****************************历史维护界面*****************************************/
|
||
#region 数据表级映射
|
||
[TableMap(DB = "DBERP", DbTableName = "XLXSWLHQ", Umid = 100860032, IsWorkFlow = false, IsAttach = false, UseWhr = true,
|
||
|
||
QuerySelSql = "",
|
||
|
||
EntitySelSql = " ",
|
||
|
||
UpdateSql = " ",
|
||
|
||
InsertSql = " ",
|
||
|
||
DeleteSql = " ")]
|
||
|
||
#endregion
|
||
|
||
public class XLXSWLHQ_HIS : EntityManager
|
||
{
|
||
|
||
}
|
||
/*********************************************************************************/
|
||
}
|
||
|