//根据客户id来获取使用哪个公众号的key,错误返回clsNetExecute.Error +错误信息；正确返回key值
private string GetTokenKey(string khid)
{
string errInfo, rt;
string mysql = "select ccid+'-' from yx_t_khb where khid=@khid";
List <SqlParameter> para = new List <SqlParameter>();
para.Add(new SqlParameter("@khid", khid));
DataTable dt;
using (LiLanzDALForXLM dal = new LiLanzDALForXLM("server='192.168.35.10';uid=ABEASD14AD;pwd=+AuDkDew;database=tlsoft"))
{
errInfo = dal.ExecuteQuerySecurity(mysql, para, out dt);
}
if (errInfo != "")
{
rt = clsNetExecute.Error + errInfo;
}
else if (dt.Rows.Count > 0)
{
if (Convert.ToString(dt.Rows[0][0]).IndexOf("-17832-") > 0)
{
rt = "7";
}
else
{
rt = "5";
}
}
else
{
rt = clsNetExecute.Error + "系统未找到该客户信息";
}
return(rt);
}
/// <summary>
/// 通过协同userid，systemkey=1来转化成 单点登入统一唯一标识；
/// </summary>
/// <param name="erp_userid">协同userid</param>
/// <param name="systemkey">协同系统标识，暂为1</param>
/// <returns>返回单点登录的唯一用户表示</returns>
private static string getUserKey(string erp_userid, string systemkey)
{
object userKey;
SqlConnection sqlConnection = (SqlConnection)DatabaseConn.Connection("wechat");
using (LiLanzDALForXLM dal = new LiLanzDALForXLM(sqlConnection.ConnectionString))
{
sqlConnection.Close(); sqlConnection.Dispose();
string str_sql = @"select a.userid
from wx_t_appauthorized a
where a.systemid=@systemkey and a.systemkey=@userid";
List <SqlParameter> para = new List <SqlParameter>();
para.Add(new SqlParameter("@userid", erp_userid));
para.Add(new SqlParameter("@systemkey", systemkey));
if (dal.ExecuteQueryFastSecurity(str_sql, para, out userKey) == "")
{
return(userKey.ToString());
}
else
{
return("");
}
}
}
//核销Code接口
//检查跟核销接口中的ACCESSTOKEN都直接根据传入的TZID来判断如果是KHID=17832则使用轻商务的反之使用利郎男装
//由于不能保证CODE在本地一定存在
public ResponseModel ConsumeCode(string khid, string code, string configkey)
{
JObject ja = new JObject();
//我们强烈建议开发者在调用核销code接口之前调用查询code接口，并在核销之前对非法状态的code(如转赠中、已删除、已核销等)做出处理。
string url = string.Format("https://api.weixin.qq.com/card/code/consume?access_token={0}", GetToken(configkey));
string data = string.Format(@"{{""code"": ""{0}""}}", code);
string CheckCodeResult = CheckCode(code, configkey);
JObject ccrjo = JObject.Parse(CheckCodeResult);
if (Convert.ToBoolean(ccrjo["can_consume"]) && Convert.ToString(ccrjo["errmsg"]) == "")
{
string content = PostDataToWX(url, data);
JObject jo = JObject.Parse(content);
if (Convert.ToString(jo["errcode"]) == "0")
{
//核销成功更新本地数据库
using (LiLanzDALForXLM dal62 = new LiLanzDALForXLM(WXDBConstr))
{
string str_sql = @"update wx_t_CardCodes set usercardstatus='CONSUMED',canconsume=0,isconsume=1,consumetime=getdate() where cardcode=@cardcode;
insert wx_t_CodeConsume(codeid,cardcode,saleid,creater,createtime,khid)
select top 1 id,cardcode,0,0,getdate()," + khid + " from wx_t_CardCodes where cardcode=@cardcode;";
List <SqlParameter> paras = new List <SqlParameter>();
paras.Add(new SqlParameter("@cardcode", code));
string errinfo = dal62.ExecuteNonQuerySecurity(str_sql, paras);
if (errinfo != "")
{
//WriteLog("核销时提交微信成功，但是更新本地数据时失败！ INFOS:" + errinfo);
//clsSharedHelper.WriteErrorInfo("核销时提交微信成功，但是更新本地数据时失败！ INFOS:" + errinfo);
res = ResponseModel.setRes(1, "核销时提交微信成功，但是更新本地数据时失败！ INFOS:" + errinfo);
}
else
{
//clsSharedHelper.WriteSuccessedInfo("核销成功：" + Convert.ToString(jo["openid"]));
ja["openid"] = Convert.ToString(jo["openid"]);
}
res = ResponseModel.setRes(0, ja, "核销成功!");
}
}
else
{
//WriteLog("核销失败 INFOS:" + content);
//clsSharedHelper.WriteErrorInfo("核销失败 INFOS:" + content);
res = ResponseModel.setRes(1, "核销失败 INFOS:" + content);
}
}
else
{
//clsSharedHelper.WriteErrorInfo("检查CODE状态不通过！" + CheckCodeResult);
res = ResponseModel.setRes(1, "检查CODE状态不通过！" + CheckCodeResult);
}
return(res);
}
protected void Page_Load(object sender, EventArgs e)
{
bid.Value = ThreadOne().Tables[0].Rows[0]["value"].ToString();
string s = "<input type=\"text\" value=" + ThreadOne().Tables[0].Rows[0]["value"].ToString() + " />";
join.InnerHtml = s;
using (LiLanzDALForXLM dal = new LiLanzDALForXLM(1))
{
string mysql = @" exec yf_cx_xljx {0},{1},{2}";
DataSet ds;
string errInfo = dal.ExecuteQuery(string.Format(mysql, 1, "TEST", "test"), out ds);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string DBConnStr = "server='192.168.35.10';uid=abeasd14ad;pwd=+AuDkDew;database=tlsoft";
using (LiLanzDALForXLM dal = new LiLanzDALForXLM(DBConnStr))
{
string str_sql = @"select name as xmmc FROM yf_t_wjxmb where djlx=2411 and ty=0 AND lx in (0,1);
SELECT a.sxfs, a.mxid ,a.sphh AS chdm,kh.khmc,b.zdr
FROM yx_t_jdxymxb a
INNER JOIN dbo.yx_t_jdxyb b ON a.id=b.id AND b.kfbh='201931'
INNER JOIN dbo.yx_t_khb kh ON kh.khid=b.khid
WHERE b.djlx=388
";
string insertSql = "";
DataSet ds = new DataSet();
string errinfo = dal.ExecuteQuery(str_sql, out ds);
if (errinfo != "")
{
return;
}
DataTable xmmc = ds.Tables[0];
List <string> xmList = new List <string>();
foreach (DataRow dr in xmmc.Rows)
{
xmList.Add(dr["xmmc"].ToString());
}
DataTable sj = ds.Tables[1];
foreach (DataRow dr in sj.Rows)
{
string[] sxfs = dr["sxfs"].ToString().Split('+');
for (int i = 0; i < sxfs.Length; i++)
{
if (sxfs[i] != "")
{
if (xmList.IndexOf(sxfs[i]) < 0)
{
insertSql += " insert tmp20190603 (mxid,chdm,sxfs,ghsmc,zdr) values('" + dr["mxid"].ToString() + "','" + dr["chdm"] + "','" + dr["sxfs"].ToString() + "','" + dr["khmc"].ToString() + "','" + dr["zdr"].ToString() + "') ";
break;
}
}
}
}
if (!string.IsNullOrEmpty(insertSql))
{
DataSet ds2 = new DataSet();
dal.ExecuteQuery(insertSql, out ds2);
}
}
}
//获取ACCESS_TOKEN
public string GetToken(string configkey)
{
string _AT = "";
using (LiLanzDALForXLM dal23 = new LiLanzDALForXLM("server='192.168.35.62';uid=sa;pwd=ll=8727;database=weChatPromotion"))
{
string str_sql = "select top 1 accesstoken from wx_t_tokenconfiginfo where configkey='" + configkey + "'";
object scaler = null;
string errinfo = dal23.ExecuteQueryFast(str_sql, out scaler);
if (errinfo == "")
{
_AT = Convert.ToString(scaler);
//if (_AT == "")
//WriteLog("找不到ConfigKey的ACCESS_TOKEN！");
}
//else
//WriteLog("查询ACCESS_TOKEN时出错 ConfigKey:" + configkey + " " + errinfo);
}
return(_AT);
}
//微信卡券
//查询CODE接口--卡卷查询
public string CheckCode(string code, string configkey)
{
string rtJson = @"{{""can_consume"":{0},""card_status"":""{1}"",""card_description"":""{3}"",""card_discount"":""{4}"",""errmsg"":""{2}"",""localtype"":""{5}"",""leastcost"":""{6}"",""reducecost"":""{7}""}}";
using (LiLanzDALForXLM dal62 = new LiLanzDALForXLM(WXDBConstr))
{
string url = string.Format("https://api.weixin.qq.com/card/code/get?access_token={0}", GetToken(configkey));
List <SqlParameter> paras = new List <SqlParameter>();
string str_sql = "", errinfo = "";
string datas = @"{{
""code"" : ""{0}"",
""check_consume"" : false
}}";
datas = string.Format(datas, code);
string content = PostDataToWX(url, datas);
JObject jo = JObject.Parse(content);
if (Convert.ToString(jo["errcode"]) == "0")
{
string can_consume = Convert.ToString(jo["can_consume"]).ToLower();
string openid = Convert.ToString(jo["openid"]);
string status = Convert.ToString(jo["user_card_status"]);
string cardid = Convert.ToString(jo["card"]["card_id"]);
str_sql = @" if exists (select top 1 1 from wx_t_cardinfos where cardid=@cardid and configkey=@configkey and isdel=0)
begin
if exists (select top 1 1 from wx_t_cardcodes where cardid=@cardid and cardcode=@cardcode)
update wx_t_CardCodes set getuser=@openid,usercardstatus=@status,canconsume=@canconsume where cardid=@cardid and cardcode=@cardcode;
else
insert into wx_t_CardCodes(cardid,cardcode,isget,getuser,gettime,isconsume,usercardstatus,canconsume)
values(@cardid,@cardcode,1,@openid,getdate(),0,'NORMAL',@canconsume);
select top 1 '11',description,localdiscount,localcardtype,LeastCost,ReduceCost from wx_t_cardinfos where cardid=@cardid and configkey=@configkey and isdel=0;
end
else
select '00';";
paras.Clear();
paras.Add(new SqlParameter("@cardid", cardid));
paras.Add(new SqlParameter("@configkey", configkey));
paras.Add(new SqlParameter("@openid", openid));
paras.Add(new SqlParameter("@status", status));
paras.Add(new SqlParameter("@canconsume", can_consume));
paras.Add(new SqlParameter("@cardcode", code));
DataTable dt = null;
errinfo = dal62.ExecuteQuerySecurity(str_sql, paras, out dt);
if (errinfo != "")
{
//WriteLog("CheckCode WriteDB Is ERROR!" + errinfo);
rtJson = string.Format(rtJson, can_consume, status, errinfo, "", "", "", "", "");
}
else
{
if (Convert.ToString(dt.Rows[0][0]) == "00")
{
rtJson = string.Format(rtJson, can_consume, status, "该卡券已停用！", "", "", "", "", "");
}
else
{
rtJson = string.Format(rtJson, can_consume, status, "", Convert.ToString(dt.Rows[0][1]), Convert.ToString(dt.Rows[0][2]),
Convert.ToString(dt.Rows[0][3]), Convert.ToString(dt.Rows[0][4]), Convert.ToString(dt.Rows[0][5]));
}
}
}
else
{
rtJson = string.Format(rtJson, "false", "", jo["errmsg"], "", "", "", "", "");
}
}
return(rtJson);
}
public string orderDetail(string data)
{
//货号1|尺码1|尺码2|,货号2|尺码1|尺码2
//货号1,货号2,....
//string data = Context.Request.QueryString["data"].ToString();
string sphhSql = "";
//构造货号范围表 //
foreach (string item in data.Split(','))
{
if (item.Contains("|"))
{
for (int i = 1; i < item.Split('|').Length; i++)
{
sphhSql = sphhSql + " select '" + item.Split('|')[0] + "' as sphh,'cm" + item.Split('|')[i] + "' as cm union ";
}
}
else
{
sphhSql = sphhSql + " select '" + item + "' as sphh,'cm24' as cm union ";
}
}
string sql = "select a.sphh,a.cm into #sphh from (" + sphhSql.Substring(0, sphhSql.Length - 6) + ") a ;";
sql += " select distinct sphh.lydjid as xzid,sphh.sphh into #range ";
sql += " from yf_v_rinsing_sphh_all sphh ";
sql += " inner join (select distinct sphh from #sphh) hh on hh.sphh=sphh.sphh where sphh.djzt=0 ";
//构造货号范围表 end //
//合格证信息
sql += " select f.id,f.lydjid,f.dbhg,f.dbtg,f.ddh as '水洗材料',f.fk as '水洗材料下装',f.dbxx as '西服三件套马甲',pm.mc '品名',isnull(bsz.mc,'') '品名上装',isnull(bxz.mc,'') '品名下装',isnull(bmj.mc,'') as '品名西服三件套马甲' ,";
sql += " gb.dm '版型',yp.yphh '样号',case f.dsqk when '' then '' else f.dsqk+'：' end +f.shqk '洗涤方法',case f.dekz when '' then '' else f.dekz+'：' end +f.desz '洗涤方法上装',case f.jfk when '' then '' else f.jfk+'：' end+f.ghsyj '洗涤方法下装',xt.mc '警告语',g.mc '执行标准',f.jpg '等级',h.mc '安全技术类别',sphh.sphh '货号', m.notice '注意事项',m.store '使用和贮藏',";
sql += " sx.notice 'sx注意事项',sx.store 'sx使用和贮藏',kusx.notice 'kusx注意事项',kusx.store 'kusx使用和贮藏' ";
sql += " into #myzb ";
sql += " from yf_T_bjdlb f ";
sql += " inner join #range r on r.xzid=f.id ";
sql += " inner join yf_v_rinsing_sphh_all sphh on f.id=sphh.lydjid and sphh.sphh=r.sphh ";
sql += " inner join Yf_T_bjdbjzb pm on pm.id=f.tplx";
sql += " left join Yf_T_bjdbjzb bsz on f.dycs=bsz.id ";
sql += " left join Yf_T_bjdbjzb bxz on f.wtlx=bxz.id ";
sql += " left join Yf_T_bjdbjzb bmj on f.sftj=bmj.id ";
sql += " inner join Yf_T_bjdbjzb g on g.id=f.ddid";
sql += " inner join yx_T_spdmb sp on sp.sphh=sphh.sphh";
sql += " inner join yx_v_ypdmb yp on yp.yphh=sp.yphh ";
sql += " left join Yf_T_bjdbjzb gb on gb.id=yp.bhks ";
sql += " inner join Yf_T_bjdbjzb h on h.lx=905 and f.sylx=h.id and h.tzid=1 ";
sql += " left join ghs_t_xtdm xt on xt.id=isnull(f.kzx4,0) ";
sql += " inner join yf_v_rinsingtemplate m on m.id=f.lydjid ";
sql += " left join yf_v_rinsingtemplate sx on sx.id=f.dbhg ";
sql += " left join yf_v_rinsingtemplate kusx on kusx.id=f.dbtg ";
sql += " where f.lxid=903 and f.tzid='1' ; ";
//table0 标签信息,一个货号一条记录
sql += " select * from #myzb; ";
//table1 纤维含量
sql += " select zb.货号, ROW_NUMBER() OVER(PARTITION BY zb.货号 order by xw.sytjid) sytjid, ";
sql += " /*case when isnull(xw.sz,'')='/' or isnull(xw.pdjg,'')='' then xw.sz else xw.pdjg+':'+xw.sz end as mxsz*/xw.pdjg,xw.sz,xw.glz ";
sql += " from #myzb zb inner join yf_T_bjdmxb xw on zb.id=xw.mxid and xw.lxid=903 ; ";
//table2图标
sql += " select a.* from ( ";
sql += " SELECT '主模版' lx, zb.货号, b.path,b.mc,b.dm FROM yf_v_rinsingtemplateico a INNER JOIN yf_V_rinsingico b ON a.icodm=b.dm ";
sql += " inner join #myzb zb on zb.lydjid=a.mxid ";
sql += " union all";
sql += " SELECT '上装' lx ,zb.货号, b.path,b.mc,b.dm FROM yf_v_rinsingtemplateico a INNER JOIN yf_V_rinsingico b ON a.icodm=b.dm ";
sql += " inner join #myzb zb on zb.dbhg=a.mxid ";
sql += " union all";
sql += " SELECT '下装' lx,zb.货号, b.path,b.mc,b.dm FROM yf_v_rinsingtemplateico a INNER JOIN yf_V_rinsingico b ON a.icodm=b.dm ";
sql += " inner join #myzb zb on zb.dbtg=a.mxid ";
sql += " ) a order by a.lx, cast( a.dm as int) ";
//table3 各尺寸绒含量
sql += " SELECT b.lxbs,a.货号, hjyl=(mx.hsz+mx.bzsh),gg.hx crlhx,mx.cmdm ";
sql += " FROM #myzb a ";
sql += " inner join yx_T_spdmb sp on sp.sphh=a.货号";
sql += " INNER JOIN dbo.YX_T_Ypdmb yp ON sp.yphh=yp.yphh ";
sql += " INNER JOIN YF_T_Bom b ON b.yphh=yp.yphh AND b.cmfj=1 ";
sql += " inner join cl_v_chdmb_all ch on ch.chdm=b.chdm ";
sql += " inner join yf_T_bjdlb bj on bj.id=ch.bjid and bj.kzx1 =297";
sql += " INNER JOIN YF_T_Bomcmmx mx ON b.id=mx.id ";
sql += " inner JOIN yx_V_sphxggb gg ON 'cm'+mx.cmdm=gg.cmdm AND yp.yphh=gg.yphh";
//table4 水洗标材料
sql += " select b.货号,b.lx, a.* from YF_v_SXBCHDM a inner join ( select 货号, 水洗材料 chdm,'上装' lx from #myzb union select 货号, 水洗材料下装 chdm,'下装' lx from #myzb union select 货号, 西服三件套马甲 chdm,'西服三件套马甲' lx from #myzb ) b on a.chdm=b.chdm ;";
//5号型规格
sql += " select a.货号, zh.cmdm,isnull(k.hx,case when lw.id is not null then '不打印' else '未维护' end ) as hx, ";
sql += " isnull(k.hx2,case when lw.id is not null then '不打印' else '未维护' end) as hx2,";
sql += " hx2isExists= case isnull(k.hx2,'') when '' then 0 else 1 end , ";
sql += " isnull(k.gg,case when lw.id is not null then '不打印' else '未维护' end) as gg ";
sql += " from #myzb a";
sql += " inner join yx_T_spdmb sp on sp.sphh=a.货号";
sql += " inner join yx_v_ypdmb yp on yp.yphh=sp.yphh ";
sql += " inner join yx_t_cmzh zh on zh.tml=yp.tml ";
sql += " inner join (select distinct sphh from #sphh) kz on kz.sphh=a.货号 ";
sql += " left join yx_V_sphxggb k on k.yphh=yp.yphh and zh.cmdm=k.cmdm";
sql += " left join yx_V_noneedhxgg lw on lw.id=yp.splbid ";
//6要显示哪些尺码
sql += " select * from #sphh;";
sql += " drop table #myzb; drop table #sphh;drop table #range;";
DataSet htzinfoDs = null;
//string ConnectionString = "Server=192.168.35.10;Database=TLSOFT;Uid=ABEASD14AD;Pwd=+AuDkDew;";
using (LiLanzDALForXLM dal = new LiLanzDALForXLM(1))
{
dal.ConnectionString = "server='192.168.35.10';uid=ABEASD14AD;pwd=+AuDkDew;database=tlsoft ";;
dal.ExecuteQuery(sql, out htzinfoDs);
}
DataTable htzinfo = htzinfoDs.Tables[0].Copy(); //'水洗信息
DataTable hlinfo = htzinfoDs.Tables[1].Copy(); //纤维成份'
DataTable icoinfo = htzinfoDs.Tables[2].Copy(); // '图标'
DataTable crlinfo = htzinfoDs.Tables[3].Copy(); // '各尺寸绒含量
DataTable chdminfo = htzinfoDs.Tables[4].Copy(); // '水洗标材料
DataTable hxgginfo = htzinfoDs.Tables[5].Copy(); // '尺码表
DataTable showinfo = htzinfoDs.Tables[6].Copy(); // '要显示哪些尺码
List <SphhInfo> sphhInfoList = new List <SphhInfo>();
foreach (DataRow sphhdr in htzinfo.Rows)
{
SphhInfo sphhInfo = new SphhInfo();
if (string.Compare(sphhdr["洗涤方法"].ToString(), "/") == 0)
{
sphhInfo.Xdff = "";
}
else
{
sphhInfo.Xdff = sphhdr["洗涤方法"].ToString();
}
if (string.Compare(sphhdr["洗涤方法上装"].ToString(), "/") == 0)
{
sphhInfo.Xdff_sz = "";
}
else
{
sphhInfo.Xdff_sz = sphhdr["洗涤方法上装"].ToString();
}
if (string.Compare(sphhdr["洗涤方法下装"].ToString(), "/") == 0)
{
sphhInfo.Xdff_xz = "";
}
else
{
sphhInfo.Xdff_xz = sphhdr["洗涤方法下装"].ToString();
}
//成份
foreach (DataRow dr in hlinfo.Select("货号='" + sphhdr["货号"].ToString() + "' "))
{
MaterialInfo2 cf = new MaterialInfo2();
cf.Glz = int.Parse(dr["Glz"].ToString());
cf.Sytjid = int.Parse(dr["Sytjid"].ToString());
cf.Value = dr["sz"].ToString();
cf.Title = dr["Pdjg"].ToString();
sphhInfo.CfList.Add(cf);
}
//图标
foreach (DataRow dr in icoinfo.Select("货号='" + sphhdr["货号"].ToString() + "' "))
{
Ico ico = new Ico();
ico.Path = dr["path"].ToString();
ico.Mc = dr["mc"].ToString();
ico.Lx = dr["lx"].ToString();
sphhInfo.IcoList.Add(ico);
}
//水洗标材料
foreach (DataRow dr in chdminfo.Select("货号='" + sphhdr["货号"].ToString() + "' "))
{
SxChdmDataContent sx = new SxChdmDataContent();
sx.Lx = dr["lx"].ToString();
sx.Sm = dr["sm"].ToString();
sphhInfo.SxChdmList.Add(sx);
}
foreach (DataRow cmdr in hxgginfo.Select("货号='" + sphhdr["货号"].ToString() + "' "))
{
SphhCmInfo s = new SphhCmInfo();
s.Sphh = sphhdr["货号"].ToString();
s.Cm = cmdr["cmdm"].ToString();
s.Gg = cmdr["gg"].ToString();
DataRow[] clrdr = crlinfo.Select("货号='" + sphhdr["货号"].ToString() + "' and 'cm'+cmdm='" + cmdr["cmdm"].ToString() + "'");
if (clrdr.Length >= 1)
{
foreach (DataRow dr in clrdr)
{
if (Decimal.Parse(dr["hjyl"].ToString()) > 0)
{
Dictionary <string, string> g = new Dictionary <string, string>();
g.Add("Clr", String.Format("{0:####.#}", Math.Round(Decimal.Parse(dr["hjyl"].ToString()) * 1000, 1)) + "g");
g.Add("Clrgg", dr["crlhx"].ToString());
g.Add("lxbs", dr["lxbs"].ToString());
s.ClrInfo.Add(g);
}
else
{
Dictionary <string, string> g = new Dictionary <string, string>();
g.Add("Clr", "");
g.Add("Clrgg", "");
g.Add("lxbs", "0");
s.ClrInfo.Add(g);
}
}
}
else
{
Dictionary <string, string> g = new Dictionary <string, string>();
g.Add("Clr", "");
g.Add("Clrgg", "");
g.Add("lxbs", "0");
s.ClrInfo.Add(g);
}
s.Hx2isExists = int.Parse(cmdr["hx2isExists"].ToString());
s.Hx = cmdr["hx"].ToString();
s.Hx2 = cmdr["hx2"].ToString();
sphhInfo.SphhCmInfo.Add(s);
}
sphhInfo.Sphh = sphhdr["货号"].ToString();
foreach (DataRow dr in showinfo.Select("sphh='" + sphhdr["货号"].ToString() + "'"))
{
sphhInfo.Cm.Add(dr["cm"].ToString(), 1);
}
//sphhInfo.Cm = showinfo.Select("sphh='" + sphhdr["货号"].ToString() + "'")[0]["cm"].ToString();
sphhInfo.Pm = sphhdr["品名"].ToString();
sphhInfo.Pm_sz = sphhdr["品名上装"].ToString();
sphhInfo.Pm_xz = sphhdr["品名下装"].ToString();
sphhInfo.Pm_mj3 = sphhdr["品名西服三件套马甲"].ToString();
sphhInfo.Yphh = sphhdr["样号"].ToString();
sphhInfo.Bx = sphhdr["版型"].ToString();
sphhInfo.Aqjb = sphhdr["安全技术类别"].ToString();
sphhInfo.Jgy = sphhdr["警告语"].ToString();
sphhInfo.Zysx = sphhdr["注意事项"].ToString();
sphhInfo.Sycc = sphhdr["使用和贮藏"].ToString();
sphhInfo.Zysx_sx = sphhdr["sx注意事项"].ToString();
sphhInfo.Sycc_sx = sphhdr["sx使用和贮藏"].ToString();
sphhInfo.Zysx_kusx = sphhdr["kusx注意事项"].ToString();
sphhInfo.Sycc_kusx = sphhdr["kusx使用和贮藏"].ToString();
sphhInfo.Zxbz = sphhdr["执行标准"].ToString();
sphhInfoList.Add(sphhInfo);
}
return(JsonConvert.SerializeObject(sphhInfoList));
}
protected void Page_Load(object sender, EventArgs e)
{
string DBConnStr = "server='192.168.35.10';uid=abeasd14ad;pwd=+AuDkDew;database=tlsoft";
List <Dictionary <string, string> > xg = new List <Dictionary <string, string> >();
int errcode = 0;
using (LiLanzDALForXLM dal = new LiLanzDALForXLM(DBConnStr))
{
string str_sql = @"
DECLARE @ddbh VARCHAR(max);DECLARE @spkh VARCHAR(max);DECLARE @ddbh2 VARCHAR(max);
SET @ddbh='DJP19020002' ;SET @ddbh2='DJP19030761';
SET @spkh='19QXL314'
--标准量 table0
SELECT a.sphh,b.cmdm,SUM(b.sl0) sl0 --INTO #bz -- DROP TABLE #bz
FROM dbo.YX_T_Spcgjhb a
INNER JOIN dbo.YX_T_spcgjhcmmx b ON a.id=b.id
INNER JOIN zw_t_htdddjb ht ON ht.id=a.htddid AND ht.ddbh=@ddbh
GROUP BY a.sphh,b.cmdm
--入库量table1
SELECT a.id,a.mxid,a.sphh,e.cmdm,SUM(sl0) sl0 --INTO #al -- DROP TABLE #al
FROM dbo.yx_v_kcdjmx a
INNER JOIN zw_t_htdddjb b ON a.htddid=b.id AND b.ddbh=@ddbh
INNER JOIN dbo.YX_T_kcdjcmmx e ON a.mxid=e.mxid
WHERE a.djlx=141
GROUP BY a.sphh,e.cmdm,a.id,a.mxid
--table2
SELECT DISTINCT A.DJH,A.RQ,A.djlx INTO #DD -- DROP TABLE #DD
FROM dbo.yx_v_dddjmx A
INNER JOIN zw_t_htdddjb B ON A.htddid=B.ID AND B.DDBH=@ddbh
WHERE A.DJLX IN (221,225)
select * from #DD
--检查table3
SELECT a.*
FROM dbo.yx_v_kcdjmx A --入库
INNER JOIN #DD B ON A.lydjlx=B.DJLX AND CONVERT(VARCHAR(6),B.RQ,112)+B.DJH=A.cgdjh
left JOIN dbo.yx_v_kcdjmx C ON C.djlx=111 AND C.lydjid=a.id AND c.lydjlx=a.djlx AND a.sphh=c.sphh --出库
left JOIN dbo.YX_T_dddjb e ON e.djlx=224 AND c.zbyid=e.id--到货
LEFT JOIN YX_T_kcdjb f ON f.djlx=141 AND f.lydjid=e.id
WHERE A.DJLX=141 AND A.TZID=11360 AND f.id IS NULL
";
DataSet ds = new DataSet();
string errinfo = dal.ExecuteQuery(str_sql, out ds);
if (errinfo != "")
{
return;
}
DataTable 标准量 = ds.Tables[0];
DataTable rk = ds.Tables[1];
DataTable 单221225 = ds.Tables[2];
DataTable isrk = ds.Tables[3];
if (isrk.Rows.Count > 0)
{
Response.Write("有目标合同有没入库的刷吗");
return;
}
foreach (DataRow dr in 标准量.Rows)
{ //a.sphh,b.cmdm,SUM(b.sl0) sl0
int sl0 = int.Parse(dr["sl0"].ToString()); //要满足的量
string sphh = (dr["sphh"].ToString());
string cmdm = (dr["cmdm"].ToString());
DataRow[] rkdr = rk.Select("sphh='" + sphh + "' and cmdm='" + cmdm + "'");
if (rkdr.Length > 0)
{
for (int i = 0; i < rkdr.Length; i++)
{
if (int.Parse(rkdr[i]["sl0"].ToString()) >= sl0)
{
Dictionary <string, string> t = new Dictionary <string, string>();
t.Add("mxid", rkdr[i]["mxid"].ToString());
t.Add("sl0", (int.Parse(rkdr[i]["sl0"].ToString()) - sl0).ToString());
xg.Add(t);
rkdr[i]["sl0"] = (int.Parse(rkdr[i]["sl0"].ToString()) - sl0).ToString();
sl0 = 0;
}
else
{
rkdr[i]["sl0"] = "0";
sl0 = sl0 - int.Parse(rkdr[i]["sl0"].ToString());
}
if (sl0 == 0)
{
break;
}
}
if (sl0 > 0)
{
Response.Write(sphh + "|" + cmdm + "没数量不够");
errcode = 10;
break;
}
}
else
{
Response.Write(sphh + "|" + cmdm + "没数量");
errcode = 20;
break;
}
}
if (xg.Count > 0 && errcode == 0)
{
string insql = "";
for (int i = 0; i < xg.Count; i++)
{
insql += " isnert ht20190605 (mxid,sl0) values('" + xg[i]["mxid"] + "','" + xg[i]["sl0"] + "') ";
}
DataSet tmp = new DataSet();
dal.ExecuteQuery(insql, out tmp);
}
}
}
