/// <summary>
/// 获取自由项
/// </summary>
/// <param name="C_CUST_TECH_PROT">客户协议</param>
/// <param name="stdCode">执行标准</param>
/// <param name="stlGrd">钢种</param>
/// <returns></returns>
public DataRow GetFREE2(string C_CUST_TECH_PROT, string stlGrd)
{
string[] arr = { C_CUST_TECH_PROT, stlGrd };
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"SELECT
T.C_ID,
T.C_CUST_TECH_PROT,
T.C_ZYX1,
T.C_ZYX2,
T.C_STL_GRD,
T.C_STD_CODE,
B.C_USES,
B.C_IS_BXG,
A.C_ID,
A.C_DESIGN_NO,
B.C_PROD_NAME,
B.C_PROD_KIND
FROM TB_STD_CONFIG T
INNER JOIN TQB_STD_MAIN B
ON T.C_STD_CODE=B.C_STD_CODE AND T.C_STL_GRD=B.C_STL_GRD
INNER JOIN TQB_DESIGN A
ON B.C_ID = A.C_STD_MAIN_ID
WHERE T.N_STATUS=1 AND B.N_IS_CHECK=1 AND B.N_STATUS=1 AND A.N_STATUS=1 AND T.C_CUST_TECH_PROT='{0}' AND T.C_STL_GRD='{1}'", arr);
return(DbHelperOra.GetDataRow(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataRow GetConsult_ResultList(string C_TECH_CONSULT_ID, string DEPTID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select C_ID,
C_TECH_CONSULT_ID,
C_DEPT,
(select a.c_name from ts_dept a where a.c_id = t.c_dept) dept_name,
C_EMP_ID,
(select b.c_name from ts_user b where b.c_id = t.c_emp_id) user_name,
D_PLAN_DT,
D_FINISH_DT,
C_REAL_TIME,
C_RESULT,
C_LEAVE_Q,
C_REMARK,
N_CUST_EVAL,
D_CUST_EVAL_DT,
N_XG_EVAL,
C_XG_EVAL_EMP,
D_XG_EVAL_DT
from TMC_TECH_CONSULT_RESULT t
where 1=1");
if (!string.IsNullOrEmpty(C_TECH_CONSULT_ID))
{
strSql.Append(" and C_TECH_CONSULT_ID='" + C_TECH_CONSULT_ID + "'");
}
if (!string.IsNullOrEmpty(DEPTID))
{
strSql.Append(" and C_DEPT='" + DEPTID + "'");
}
return(DbHelperOra.GetDataRow(strSql.ToString()));
}
/*
* /// <summary>
* /// 分页获取数据列表
* /// </summary>
* public DataSet GetList(int PageSize,int PageIndex,string strWhere)
* {
* OracleParameter[] parameters = {
* new OracleParameter(":tblName", OracleDbType.Varchar2, 255),
* new OracleParameter(":fldName", OracleDbType.Varchar2, 255),
* new OracleParameter(":PageSize", OracleDbType.Number),
* new OracleParameter(":PageIndex", OracleDbType.Number),
* new OracleParameter(":IsReCount", OracleDbType.Clob),
* new OracleParameter(":OrderType", OracleDbType.Clob),
* new OracleParameter(":strWhere", OracleDbType.Varchar2,1000),
* };
* parameters[0].Value = "TB_MATRL_GROUP";
* parameters[1].Value = "C_ID";
* parameters[2].Value = PageSize;
* parameters[3].Value = PageIndex;
* parameters[4].Value = 0;
* parameters[5].Value = 0;
* parameters[6].Value = strWhere;
* return DbHelperOra.RunProcedure("UP_GetRecordByPage",parameters,"ds");
* }*/
#endregion BasicMethod
/// <summary>
/// 获取物料组
/// </summary>
/// <returns></returns>
public DataSet GetMatrlGroup(string code)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select T.N_LEV, T.C_MAT_GROUP_NAME,T.C_MAT_GROUP_CODE
from TB_MATRL_GROUP t
where (t.c_mat_group_code like '8%'
or t.c_mat_group_code like '6%')");
if (!string.IsNullOrEmpty(code))
{
string str = string.Format(@"select decode (T.N_LEV,1,2,2,3,3,4,4,5) N_LEV, T.C_MAT_GROUP_NAME, T.C_MAT_GROUP_CODE
from TB_MATRL_GROUP t where t.C_MAT_GROUP_CODE = '{0}'", code);
DataRow dr = DbHelperOra.GetDataRow(str);
if (dr != null)
{
strSql.Append(" and T.C_MAT_GROUP_CODE like '" + code + "%' and T.N_LEV='" + dr["N_LEV"].ToString() + "'");
strSql.Append(" order by T.C_MAT_GROUP_CODE asc");
}
}
else
{
strSql.Append(" and T.C_MAT_GROUP_CODE like '" + code + "%' and T.N_LEV='1'");
strSql.Append(" order by T.C_MAT_GROUP_CODE desc");
}
return(DbHelperOra.Query(strSql.ToString()));
}
/// <summary>
/// 获取客户地址NC主键
/// </summary>
/// <param name="ncid"></param>
/// <param name="name"></param>
/// <returns></returns>
public DataRow GetAddr(string ncid, string name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_CGADDR from ts_custaddr where C_CUST_ID='" + ncid + "' and C_CGAREA='" + name + "' ");
return(DbHelperOra.GetDataRow(strSql.ToString()));
}
/// <summary>
/// 获取客户地址NC主键
/// </summary>
/// <param name="ncid"></param>
/// <param name="name"></param>
/// <returns></returns>
public DataRow GetAddrName(string C_CGADDR)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_CGAREA from ts_custaddr where C_CGADDR='" + C_CGADDR + "'");
return(DbHelperOra.GetDataRow(strSql.ToString()));
}
/// <summary>
/// 获取业务员姓名
/// </summary>
/// <param name="id">PKID</param>
/// <returns></returns>
public string GetSaleName(string id)
{
string result = "";
DataRow dr = DbHelperOra.GetDataRow("select c_id,c_name,c_code from ts_sale_emp where c_id='" + id + "'");
if (dr != null)
{
result = dr["c_name"].ToString();
}
return(result);
}
/// <summary>
/// 判定合同签单量是否超出低毛利限量指标
/// </summary>
/// <param name="date">合同生效起始日期</param>
/// <param name="matgroupcode">物料组编码</param>
/// <param name="spec">规格</param>
/// <param name="matCode">物料编码</param>
/// <returns></returns>
public bool GetCheckMAOLI(string date, string matgroupcode, string spec, string matCode)
{
bool result = true;
decimal sumwgt = 0; //合同有效签单量
decimal maoliwgt = 0; //低毛利限量指标
string strsql5 = $@"SELECT T.N_WGT
FROM TMB_STLGRD_PRICE_WGT T
WHERE to_char(to_date(T.D_MONTH, 'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM')= TO_CHAR(to_date('{date}', 'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM')
AND T.C_MAT_GROUP_CODE = '{matgroupcode}'
AND {spec} >= T.C_SPEC_MIN
AND {spec} <= T.C_SPEC_MAX";
DataRow dr5 = DbHelperOra.GetDataRow(strsql5);
if (dr5 != null)
{
maoliwgt = Convert.ToDecimal(dr5["N_WGT"]);
}
if (maoliwgt <= 0)
{
result = true;
}
else
{
#region //判定签单量是否超出低毛利限量指标
string strSql4 = $@"SELECT NVL(SUM(T.N_WGT),0) N_WGT
FROM TMO_CON_ORDER T
LEFT JOIN TMO_CON A
ON A.C_CON_NO = T.C_CON_NO
WHERE TO_CHAR(TO_DATE(A.D_CONEFFE_DT, 'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM') = TO_CHAR(to_date('{date}', 'yyyy-mm-dd hh24:mi:ss'), 'YYYY-MM')
AND T.N_STATUS IN (1, 2)
AND T.C_MAT_CODE = '{matCode}'";
DataRow dr4 = DbHelperOra.GetDataRow(strSql4);
if (dr4 != null)
{
sumwgt = Convert.ToDecimal(dr4["N_WGT"]);
}
if (sumwgt > maoliwgt)
{
result = false;
}
#endregion
}
return(result);
}
/// <summary>
/// 检测每条订单是否低毛利
/// </summary>
/// <param name="orderID">订单ID</param>
/// <param name="date">合同生效起始日期</param>
/// <returns></returns>
public bool CheckMAOLI(string orderID, string matCode, string date)
{
Dal_TMO_ORDER dal = new Dal_TMO_ORDER();
Mod_TMO_ORDER mod = dal.GetModel(orderID);
ArrayList arraySql = new ArrayList();
bool result = true;
string strSql = $@"SELECT T.C_MAT_GROUP_CODE,T.C_SPEC, T.N_THK,NVL(A.C_FLAG,'N')C_FLAG
FROM TB_MATRL_MAIN T LEFT JOIN TMB_MONI_STLGRD A ON A.C_STL_GRD = T.C_STL_GRD
WHERE T.C_MAT_CODE = '{matCode}'";
DataRow dr = DbHelperOra.GetDataRow(strSql);
if (dr != null)
{
string flag = dr["C_FLAG"].ToString() == "N" ? "01" : "02"; //判定是否监控钢种标识
string matgroupcode = dr["C_MAT_GROUP_CODE"].ToString(); //钢种物料组编码
decimal num = 0; //监控钢种260，其他50
decimal maoli = Convert.ToDecimal(mod.N_PROFIT); //毛利
string spec = dr["N_THK"].ToString(); //规格
//监控钢种260，其他50
string strSql2 = $"SELECT T.C_INDEX FROM TS_DIC T WHERE T.C_TYPECODE='MAOLI' AND T.C_DETAILCODE='{flag}'";
DataRow dr2 = DbHelperOra.GetDataRow(strSql2);
if (dr2 != null)
{
num = Convert.ToDecimal(dr2["C_INDEX"]);
}
if (maoli < num)//订单毛利低于配置毛利进行检测
{
result = GetCheckMAOLI(date, matgroupcode, spec, matCode);
}
}
return(result);
}
/// <summary>
/// 获取钢种单价-税率-折扣
/// </summary>
/// <param name="C_MAT_CODE">物料编码</param>
/// <param name="C_STL_GRD">钢种</param>
/// <param name="C_SPEC">规格</param>
/// <returns></returns>
public DataRow GetActivityRow(string c_mat_code, string c_stl_grd, string c_spec)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_MAT_CODE,C_MAT_NAME,C_STL_GRD,C_SPEC,N_PRICE,N_PRICE2,C_STATUS,C_REMARK,C_EMP_ID,C_EMP_NAME,D_MOD_DT,C_AREAMAX,C_PROD_NAME,D_START_DT,D_END_DT ");
strSql.Append(" FROM TMB_ACTIVITY where C_STATUS=1");
if (!string.IsNullOrEmpty(c_mat_code))
{
strSql.Append(" and C_MAT_CODE='" + c_mat_code + "'");
}
if (!string.IsNullOrEmpty(c_stl_grd))
{
strSql.Append(" and UPPER(C_STL_GRD) like '%" + c_stl_grd.ToUpper() + "%'");
}
if (!string.IsNullOrEmpty(c_spec))
{
strSql.Append(" and C_SPEC like '%" + c_spec + "%'");
}
strSql.Append(" and to_date('" + DateTime.Now + "', 'yyyy-MM-dd HH24:mi:ss') >=D_START_DT");
strSql.Append(" and to_date('" + DateTime.Now + "', 'yyyy-MM-dd HH24:mi:ss') <=D_END_DT");
return(DbHelperOra.GetDataRow(strSql.ToString()));
}
/// <summary>
/// 随机获取客服
/// </summary>
/// <returns></returns>
public DataRow GetChatEmp()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select t.c_user_id from ts_user_role t where t.c_role_id in (select t.c_user_id from tmc_chat_emp t) and rownum = 1 order by dbms_random.value");
return DbHelperOra.GetDataRow(strSql.ToString());
}
/// <summary>
/// 发送发运单到中间表
/// </summary>
/// <param name="dt">要发送的集合</param>
/// <returns>返回int类型 大于0为转入成功,等于0发送失败</returns>
public int ADDFYDToZJB(string sendcode)
{
//TransactionHelper_SQL.BeginTransaction();
List <Mod_TMD_DISPATCHDETAILS> item = new List <Mod_TMD_DISPATCHDETAILS>();
#region //实例化
Dal_TMD_DISPATCHDETAILS dalitem = new Dal_TMD_DISPATCHDETAILS();
Dal_TQB_CHECKSTATE daltqb = new Dal_TQB_CHECKSTATE();
Dal_TS_DEPT daldetp = new Dal_TS_DEPT();
Dal_TS_USER daluser = new Dal_TS_USER();
Dal_TMD_DISPATCH dalhead = new Dal_TMD_DISPATCH();
Dal_TS_DIC daldic = new Dal_TS_DIC();
#endregion
Mod_TMD_DISPATCH modTmd = dalhead.GetModel(sendcode); //发运单主表
item = DataTableToList(dalitem.GetList(sendcode).Tables[0]); //发运明细
StringBuilder strSql = new StringBuilder();
for (int i = 0; i < item.Count; i++)
{
#region //基础数据
Mod_TQB_CHECKSTATE modtqb = daltqb.GetModel(item[i].C_QUALIRY_LEV);
Mod_TS_DEPT moddept = daldetp.GetModel(modTmd.C_BUSINESS_DEPT); //业务部门
string saleemp = daluser.GetSaleName(modTmd.C_BUSINESS_ID); //业务人
Mod_TS_USER moduser = daluser.GetModel(modTmd.C_CREATE_ID); //制单人
//0主计量单位，1辅单位
DataTable dt = DbHelperOra.Query("SELECT T.C_ID,T.C_MEASNAME FROM TMB_MEAS T WHERE T.C_ID in('" + item[i].C_UNITIS + "','" + item[i].C_AU_UNITIS + "')").Tables[0];
DataRow drfyfs = DbHelperOra.GetDataRow("select T.C_INDEX from TS_DIC t where t.c_detailcode='" + modTmd.C_SHIPVIA + "'");
#endregion
#region //插入条码中间表
strSql.Append("insert into ReZJB_FYD(");
strSql.Append("fydh,"); //发运单号-
strSql.Append("ck,"); //仓库主键-
strSql.Append("khbm,"); //客户编码-
strSql.Append("yslb,"); //运输方式主键
strSql.Append("cph,"); //车牌号-
strSql.Append("wlh,"); //物料编码-
strSql.Append("wlmc,"); //物料名称-
strSql.Append("sx,"); //质量等级编码A,B-
strSql.Append("jhsl,"); //支数--
strSql.Append("jhzl,"); //重量--
strSql.Append("zjldw,"); //线材主计量单位名称（件【线材】）-
strSql.Append("fjldw,"); //吨--
strSql.Append("ywbm,"); //业务部门名称--
strSql.Append("ywry,"); //业务员名称--
strSql.Append("zdr,"); //制单人名称--
strSql.Append("zdrq,"); //制单时间--
strSql.Append("PH,"); //钢种--
strSql.Append("GG,"); //规格--
strSql.Append("ZJBstatus,"); //状态默认0--
strSql.Append("CAPPK,"); //发运单明细表主键--
strSql.Append("insertts,"); //插入时间--
strSql.Append("PCInfo,"); //其他要求名称--
strSql.Append("zyx1,"); //自由项1--
strSql.Append("zyx2,"); //自由项2--
strSql.Append("zyx3,"); //包装要求
strSql.Append("zyx4,"); //导入条码次数
strSql.Append("zldj"); //行备注
strSql.Append(")values(");
strSql.Append("'" + item[i].C_DISPATCH_ID + "',"); //发运单号
strSql.Append("'" + item[i].C_SEND_STOCK_PK + "',"); //发运仓库主键
strSql.Append("'" + item[i].C_CUSTNO + "',"); //客户编码
strSql.Append("'" + drfyfs["C_INDEX"].ToString() + "',"); //发运方式主键1汽运 2火运
strSql.Append("'" + modTmd.C_LIC_PLA_NO + "',"); //车牌号
strSql.Append("'" + item[i].C_MAT_CODE + "',"); //物料编码
strSql.Append("'" + item[i].C_MAT_NAME + "',"); //物料编码
strSql.Append("'" + modtqb.C_CHECKSTATE_NAME + "',"); //质量等级名称
strSql.Append("'" + item[i].N_FYZS + "',"); //发运支数
strSql.Append("'" + item[i].N_FYWGT + "',"); //发运重量
strSql.Append("'" + dt.Rows[1]["C_MEASNAME"] + "',"); //线材主计量单位名称（件【线材】）
strSql.Append("'" + dt.Rows[0]["C_MEASNAME"] + "',"); //线材主计量单位名称（吨）
strSql.Append("'" + moddept.C_NAME + "',"); //业务部门名称
strSql.Append("'" + saleemp + "',"); //业务员名称
strSql.Append("'" + moduser.C_NAME + "',"); //制单人名称
strSql.Append("'" + modTmd.D_CREATE_DT + "',"); //制单时间
strSql.Append("'" + item[i].C_STL_GRD + "',"); //钢种
strSql.Append("'" + item[i].C_SPEC + "',"); //规格
strSql.Append("0,"); //状态
strSql.Append("'" + item[i].C_ID + "',"); //发运单明细表主键
strSql.Append("'" + DateTime.Now.ToString() + "',"); //插入时间
strSql.Append("'" + item[i].C_ELSENEED + "',"); //其他要求
strSql.Append("'" + item[i].C_FREE_TERM + "',"); //自由项1
strSql.Append("'" + item[i].C_FREE_TERM2 + "',"); //自由项2
strSql.Append("'" + item[i].C_PACK + "',"); // 包装要求
strSql.Append("'" + modTmd.C_EXTEND1 + "',"); // 导入条码系统次数
strSql.Append("'" + item[i].C_REMARK + "'"); // 行备注
strSql.Append(");");
#endregion
}
if (DbHelper_SQL.ExecuteSql(strSql.ToString()) == 0)
{
return(-2);
}
return(1);
}