/// <summary> /// 导出代理商 /// </summary> /// <param name="pageSize"></param> /// <param name="currPage"></param> /// <param name="where"></param> /// <returns></returns> public DataTable ExportAgent(IDictionary <String, String> where) { string sql = @"SELECT [AGENT_NO] AS '编号',[AGENT_NAME] AS '代理商',[AGENT_CARD_NUMBER] AS '卡数量',[AGENT_AREA] AS '地区',[AGENT_CONTACTS] AS '联系人',[AGENT_TEL] AS '联系电话' ,CASE WHEN [STATUS]=1 THEN '启用' WHEN [STATUS]=0 THEN '禁用' ELSE '删除' END AS '状态',[CREATED_TIME] AS '创建时间'"; string othersql = " FROM [dbo].[TB_DATA_AGENT] WHERE STATUS>-1"; if (where.ContainsKey("AGENT_NAME")) { othersql = othersql + String.Format(" AND AGENT_NAME like @AGENT_NAME"); } sql = sql + othersql; //设定排序条件 sql = sql + String.Format(" ORDER BY {0} {1} ", "CREATED_TIME", "DESC"); CustomSqlSection custom = Gateway.Default.FromCustomSql(sql); if (where.ContainsKey("AGENT_NAME")) { custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["AGENT_NAME"] + "%"); } try { return(custom.ToDataSet().Tables[0]); } catch (Exception ex) { Log.WriteLog(ex.Message); return(null); } }
//Add by sunny 2017 0904 summar ma 要去search页面添加销售的评价 /// <param name="strQC"></param> /// <returns>通过QC获得销售评价</returns> public static string GetCustomerComment(string QC) { string sd = ""; if (QC != "") { CustomSqlSection css = DataAccess.DefaultDB.CustomSql(@"SELECT qcc.customer_comment FROM ppo_hd ph,gen_customer c,ppo_qcmaininfo qc, ppo_item pi, fab_combo fc, ppo_qccomment qcc,ppo_item_combo pic,QCMainInfo qm WHERE ph.ppo_no=pi.ppo_no and fc.fab_combo_id = qcc.fab_combo_id and qc.ppo_item_id=pi.ppo_item_id and pi.ppo_item_id=pic.ppo_item_id and pi.quality_code=qm.quality_code and qm.Material_Group<>'Flat Knit Fabric' AND pic.fab_combo_id=fc.fab_combo_id and qc.ppo_qc_id=qcc.ppo_qc_id and ph.customer_cd=c.customer_cd and pi.quality_code= @Qc "); css.AddInputParameter("Qc", DbType.String, QC); DataTable dt = css.ToDataSet().Tables[0]; string i = ""; if (dt.Rows.Count > 0) { i = css.ToDataSet().Tables[0].Rows[0][0].ToString(); } if (i != "") { sd = i; } } return(sd); }
/// <summary> /// 查询代理商 /// </summary> /// <param name="pageSize"></param> /// <param name="currPage"></param> /// <param name="where"></param> /// <returns></returns> public IDictionary <String, Object> QueryAgent(Int32 pageSize, Int32 currPage, IDictionary <String, String> where) { DataTable dtPage = null; Int32 totalRow = 0; Int32 totalPage = 0; string sqlCount = @"select count(1) totalrow "; string sql = @"SELECT [AGENT_ID],[USER_ID],[AGENT_NO],[AGENT_NAME],[AGENT_CARD_NUMBER],[AGENT_AREA],[AGENT_CONTACTS],[AGENT_TEL],[AGENT_BUSINESS_LICENSE],[AGENT_ID_CARD] ,[AGENT_COMMITMENT_LETTER],[STATUS],[CREATED_TIME]"; string othersql = " FROM [dbo].[TB_DATA_AGENT] WHERE STATUS>-1"; if (where.ContainsKey("AGENT_NAME")) { othersql = othersql + String.Format(" AND AGENT_NAME like @AGENT_NAME"); } sqlCount = sqlCount + othersql; sql = sql + othersql; //设定排序条件 sql = sql + String.Format(" ORDER BY {0} {1} ", "CREATED_TIME", "DESC"); CustomSqlSection customCount = Gateway.Default.FromCustomSql(sqlCount); CustomSqlSection custom = Gateway.Default.FromCustomSql(sql); if (where.ContainsKey("AGENT_NAME")) { customCount.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["AGENT_NAME"] + "%"); custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["AGENT_NAME"] + "%"); } try { totalRow = Convert.ToInt32(customCount.ToDataSet().Tables[0].Rows[0]["totalrow"]); totalPage = Convert.ToInt32(Math.Ceiling(totalRow * 1.0 / pageSize)); totalPage = totalPage == 0 ? 1 : totalPage; if (totalPage < currPage) { currPage = totalPage; } dtPage = custom.ToDataTable(pageSize, currPage); } catch (Exception ex) { Log.WriteLog(ex.Message); return(null); } Dictionary <String, Object> dic = new Dictionary <String, Object>(); dic.Add("table", dtPage); dic.Add("totalRow", totalRow); dic.Add("totalPage", totalPage); return(dic); }
public string GettingDifferentWidthTimer() { try { string sql = @" select distinct r.PPO_No,pp.GK_No,pp.Quality_Code,r.Gauge,r.Diameter,r.Total_Needles,convert(int,m.Width)Width,convert(int,m.Max_width) Max_width,pp.Usage,r.Designer from ArtDB.dbo.rtRawInfo r with(nolock) LEFT JOIN ArtDB.dbo.rtPdInfo AS rpi WITH (NOLOCK) ON rpi.Raw_NO = r.Raw_NO LEFT JOIN PlanningDB.dbo.pcPPOItem AS pp WITH (NOLOCK) ON pp.GK_NO = rpi.GK_NO AND pp.PPO_NO = rpi.PPO_NO LEFT JOIN ArtDB.dbo.rtFabricMixProduct m WITH (NOLOCK) on pp.GK_No=m.GK_NO where 1=1 AND pp.ApproveTime>=DATEADD(DAY,-3,GETDATE()) and not exists(select 1 from QUALITY_WidthCompare where ppo_no=r.ppo_no) and r.cancel_date is null and r.groups='C' and r.Art_Type<>'M' and r.PPO_No like 'PKGK%' AND pp.Quality_Code IS NOT NULL "; CustomSqlSection cuSQL = DataAccess.CreateSqlServerDatabase().CustomSql(sql); DataSet ds = cuSQL.ToDataSet(); DataTable dt = ds.Tables[0]; List <string> emailList = new List <string>(); emailList.Add("PPO_NO"); emailList.Add("quality_code"); emailList.Add("usage"); emailList.Add("gk_no"); emailList.Add("tech_user"); emailList.Add("tech_Gauge"); emailList.Add("Quality_Gauge"); emailList.Add("tech_Diameter"); emailList.Add("Quality_Diameter"); emailList.Add("tech_Total_Needles"); emailList.Add("Quality_Total_Needles"); emailList.Add("tech_width"); emailList.Add("Quality_width"); DataTable dtEmail = new DataTable(); if (dt.Rows.Count > 0) { List <string> qcList = new List <string>(); foreach (DataRow dr in dt.Rows) { if (!qcList.Contains(dr["Quality_Code"].ToString())) { qcList.Add(dr["Quality_Code"].ToString()); } } StringBuilder oracle_sql = new StringBuilder(); foreach (string qc in qcList) { if (oracle_sql.ToString() == "") { oracle_sql.Append(" select '" + qc + "' qc from dual"); } else { oracle_sql.Append(" union all select '" + qc + "' from dual"); } } string sql_oracle = "select b.quality_code,gauge,diameter,total_needles,width,max_width from (" + oracle_sql.ToString() + ") a inner join Qcavailablewidth b on a.qc=b.quality_code "; CustomSqlSection css_qcWidth = DataAccess.DefaultDB.CustomSql(sql_oracle); DataSet ds_qcWidth = css_qcWidth.ToDataSet(); DataTable dt_qcWidth = ds_qcWidth.Tables[0]; foreach (string str in emailList) { dtEmail.Columns.Add(str); } foreach (DataRow dr in dt.Rows) { DataRow[] drwidth = dt_qcWidth.Select("Quality_Code='" + dr["Quality_code"].ToString() + "' and width='" + dr["width"].ToString() + "' and max_width='" + dr["max_width"].ToString() + "' and ( gauge<>'" + dr["gauge"].ToString() + "' or diameter<>'" + dr["diameter"].ToString() + "' or total_needles<>'" + dr["total_needles"].ToString() + "' ) "); if (drwidth.Length > 0) { foreach (DataRow drInsert in drwidth) { DataRow drEmail = dtEmail.NewRow(); drEmail["PPO_NO"] = dr["PPO_NO"].ToString(); drEmail["quality_code"] = dr["Quality_code"].ToString(); drEmail["usage"] = dr["Usage"].ToString(); drEmail["gk_no"] = dr["Gk_no"].ToString(); drEmail["tech_user"] = dr["Designer"].ToString(); drEmail["tech_Gauge"] = dr["Gauge"].ToString(); drEmail["Quality_Gauge"] = drInsert["gauge"].ToString(); drEmail["tech_Diameter"] = dr["Diameter"].ToString(); drEmail["Quality_Diameter"] = drInsert["diameter"].ToString(); drEmail["tech_Total_Needles"] = dr["Total_Needles"].ToString(); drEmail["Quality_Total_Needles"] = drInsert["total_needles"].ToString(); drEmail["tech_width"] = dr["Width"].ToString() + " " + dr["Max_width"].ToString(); drEmail["Quality_width"] = drInsert["width"].ToString() + " " + drInsert["max_width"].ToString(); dtEmail.Rows.Add(drEmail); } } } if (dtEmail.Rows.Count == 0) { return("FAIL"); } string senderTo = GetEmailAddress(); string title = "针筒门幅不一致报表"; string Content = "针筒门幅不一致报表" + "</br>" + GetMailContent(dtEmail, emailList); string sqlStr = @"INSERT INTO SEND_EMAIL_TRANS (SEQ_NO,MAIL_FROM,MAIL_TO,MAIL_CC,MAIL_SUBJECT,REPORT_URL,MAIL_CONTENT,MAIL_ATTACH,CREATE_DATE) VALUES (SEQ_SEND_EMAIL_TRANS.NEXTVAL,@SDC,@MT,@BV,@SUB,'',@Content,'',SYSDATE)"; CustomSqlSection css = DataAccess.DefaultDB.CustomSql(sqlStr); css.AddInputParameter("SDC", DbType.String, "*****@*****.**"); css.AddInputParameter("BV", DbType.String, ""); css.AddInputParameter("MT", DbType.String, senderTo); css.AddInputParameter("SUB", DbType.String, title); css.AddInputParameter("Content", DbType.String, Content); int rt = css.ExecuteNonQuery(); if (rt > 0) { StringBuilder sqlInsert = new StringBuilder(); string remark = ""; foreach (DataRow dr in dtEmail.Rows) { remark = dr["quality_code"] + " " + dr["usage"].ToString() + " " + dr["gk_no"].ToString(); sqlInsert.Append("insert into PlanningDB.dbo.QUALITY_WidthCompare(Ppo_No,Remark,Create_date) values('" + dr["PPO_NO"].ToString() + "','" + remark + "',getdate())"); } if (sqlInsert.ToString() != "") { CustomSqlSection execSQL = DataAccess.CreateSqlServerDatabase().CustomSql(sqlInsert.ToString()); execSQL.ExecuteNonQuery(); return("OK"); } } } return("FAIL1"); } catch (Exception ex) { return(ex.Message); } }
/// <summary> /// 导出卡信息 /// </summary> /// <param name="pageSize"></param> /// <param name="currPage"></param> /// <param name="where"></param> /// <returns></returns> public DataTable ExportCard(Int32 pageSize, Int32 currPage, IDictionary <String, String> where) { string sql = @"SELECT d.BRAND_NAME AS '品牌',[SERVICE_PROVIDER] AS '运营商',[CARD_ICCID] AS 'ICCID',[CARD_NO] AS '卡号',b.AGENT_NAME AS '代理商',c.END_CUSTOMER_NAME AS '终端客户', CASE when a.[STATUS]=-1 THEN '违规封停' when a.[STATUS]=1 THEN '已激活' ELSE '未激活' END AS '状态',CASE WHEN [IS_COMPLAINT]=1 THEN '已被投诉' ELSE '未投诉' END AS '是否被投诉', [COMPLAINT_DATE] AS '投诉时间',[REAL_NAME_PERSON] AS '实名人',[REAL_NAME_ID_CARD] AS '身份证',[DISTRIBUTION_DATE] AS '分配时间',a.[LAST_UPDATED_TIME] AS '修改时间'"; string othersql = @" FROM [dbo].[TB_DATA_CARD] a left join TB_DATA_AGENT b on a.AGENT_ID = b.AGENT_ID left join TB_DATA_END_CUSTOMER c on a.[END_CUSTOMER_ID]=c.[END_CUSTOMER_ID] left join TB_DATA_BRAND d on a.[BRAND_ID]=d.[BRAND_ID] where 1=1"; if (where.ContainsKey("CARD_NO")) { othersql = othersql + String.Format(" AND CARD_NO like @CARD_NO"); } if (where.ContainsKey("BEGIN_NUM")) { othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))>=@BEGIN_NUM"); } if (where.ContainsKey("END_NUM")) { othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))<=@END_NUM"); } if (where.ContainsKey("AGENT_ID")) { othersql = othersql + String.Format(" AND a.AGENT_ID=@AGENT_ID"); } if (where.ContainsKey("END_CUSTOMER_ID")) { othersql = othersql + String.Format(" AND a.END_CUSTOMER_ID]=@END_CUSTOMER_ID"); } if (where.ContainsKey("BRAND_ID")) { othersql = othersql + String.Format(" AND a.BRAND_ID=@BRAND_ID"); } if (where.ContainsKey("COMPLAINT_BEGIN_DATE")) { othersql = othersql + String.Format(" AND a.COMPLAINT_DATE>=@COMPLAINT_BEGIN_DATE"); } if (where.ContainsKey("COMPLAINT_END_DATE")) { othersql = othersql + String.Format(" AND a.COMPLAINT_DATE<=@COMPLAINT_END_DATE"); } if (where.ContainsKey("UPDATED_BEGIN_DATE")) { othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME>=@UPDATED_BEGIN_DATE"); } if (where.ContainsKey("UPDATED_END_DATE")) { othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME<=@UPDATED_END_DATE"); } if (where.ContainsKey("STATUS")) { othersql = othersql + String.Format(" AND a.STATUS=@STATUS"); } sql = sql + othersql; //设定排序条件 sql = sql + String.Format(" ORDER BY {0} {1} ", "a.CREATED_TIME", "DESC"); CustomSqlSection custom = Gateway.Default.FromCustomSql(sql); if (where.ContainsKey("CARD_NO")) { custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["CARD_NO"] + "%"); } if (where.ContainsKey("BEGIN_NUM")) { custom.AddInputParameter("BEGIN_NUM", DbType.Int32, where["BEGIN_NUM"]); } if (where.ContainsKey("END_NUM")) { custom.AddInputParameter("END_NUM", DbType.Int32, where["END_NUM"]); } if (where.ContainsKey("AGENT_ID")) { custom.AddInputParameter("AGENT_ID", DbType.String, where["AGENT_ID"]); } if (where.ContainsKey("END_CUSTOMER_ID")) { custom.AddInputParameter("END_CUSTOMER_ID", DbType.String, where["END_CUSTOMER_ID"]); } if (where.ContainsKey("BRAND_ID")) { custom.AddInputParameter("BRAND_ID", DbType.String, where["BRAND_ID"]); } if (where.ContainsKey("COMPLAINT_BEGIN_DATE")) { custom.AddInputParameter("COMPLAINT_BEGIN_DATE", DbType.String, where["COMPLAINT_BEGIN_DATE"]); } if (where.ContainsKey("COMPLAINT_END_DATE")) { custom.AddInputParameter("COMPLAINT_END_DATE", DbType.String, where["COMPLAINT_END_DATE"]); } if (where.ContainsKey("UPDATED_BEGIN_DATE")) { custom.AddInputParameter("UPDATED_BEGIN_DATE", DbType.String, where["UPDATED_BEGIN_DATE"]); } if (where.ContainsKey("UPDATED_END_DATE")) { custom.AddInputParameter("UPDATED_END_DATE", DbType.String, where["UPDATED_END_DATE"]); } if (where.ContainsKey("STATUS")) { custom.AddInputParameter("STATUS", DbType.String, where["STATUS"]); } try { return(custom.ToDataSet().Tables[0]); } catch (Exception ex) { Log.WriteLog(ex.Message); return(null); } }
/// <summary> /// 查询卡信息 /// </summary> /// <param name="pageSize"></param> /// <param name="currPage"></param> /// <param name="where"></param> /// <returns></returns> public IDictionary <String, Object> QueryCard(Int32 pageSize, Int32 currPage, IDictionary <String, String> where) { DataTable dtPage = null; Int32 totalRow = 0; Int32 totalPage = 0; string sqlCount = @"select count(1) totalrow "; string sql = @"SELECT [CARD_ID],a.[AGENT_ID],b.AGENT_NAME,a.[END_CUSTOMER_ID],c.END_CUSTOMER_NAME,a.[BRAND_ID],d.BRAND_NAME, [CARD_ICCID],[CARD_NO],[SERVICE_PROVIDER],a.[STATUS],[IS_COMPLAINT],[COMPLAINT_DATE],[COMPLAINT_URL], [REAL_NAME_PERSON],[REAL_NAME_ID_CARD],[DISTRIBUTION_DATE],a.[LAST_UPDATED_TIME]"; string othersql = @" FROM [dbo].[TB_DATA_CARD] a left join TB_DATA_AGENT b on a.AGENT_ID = b.AGENT_ID left join TB_DATA_END_CUSTOMER c on a.[END_CUSTOMER_ID]=c.[END_CUSTOMER_ID] left join TB_DATA_BRAND d on a.[BRAND_ID]=d.[BRAND_ID] where 1=1"; if (where.ContainsKey("CARD_NO")) { othersql = othersql + String.Format(" AND CARD_NO like @CARD_NO"); } if (where.ContainsKey("BEGIN_NUM")) { othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))>=@BEGIN_NUM"); } if (where.ContainsKey("END_NUM")) { othersql = othersql + String.Format(" AND Convert(int, RIGHT(CARD_ICCID,6))<=@END_NUM"); } if (where.ContainsKey("AGENT_ID")) { othersql = othersql + String.Format(" AND a.AGENT_ID=@AGENT_ID"); } if (where.ContainsKey("END_CUSTOMER_ID")) { othersql = othersql + String.Format(" AND a.END_CUSTOMER_ID]=@END_CUSTOMER_ID"); } if (where.ContainsKey("BRAND_ID")) { othersql = othersql + String.Format(" AND a.BRAND_ID=@BRAND_ID"); } if (where.ContainsKey("COMPLAINT_BEGIN_DATE")) { othersql = othersql + String.Format(" AND a.COMPLAINT_DATE>=@COMPLAINT_BEGIN_DATE"); } if (where.ContainsKey("COMPLAINT_END_DATE")) { othersql = othersql + String.Format(" AND a.COMPLAINT_DATE<=@COMPLAINT_END_DATE"); } if (where.ContainsKey("UPDATED_BEGIN_DATE")) { othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME>=@UPDATED_BEGIN_DATE"); } if (where.ContainsKey("UPDATED_END_DATE")) { othersql = othersql + String.Format(" AND a.LAST_UPDATED_TIME<=@UPDATED_END_DATE"); } if (where.ContainsKey("STATUS")) { othersql = othersql + String.Format(" AND a.STATUS=@STATUS"); } sqlCount = sqlCount + othersql; sql = sql + othersql; //设定排序条件 sql = sql + String.Format(" ORDER BY {0} {1} ", "a.CREATED_TIME", "DESC"); CustomSqlSection customCount = Gateway.Default.FromCustomSql(sqlCount); CustomSqlSection custom = Gateway.Default.FromCustomSql(sql); if (where.ContainsKey("CARD_NO")) { customCount.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["CARD_NO"] + "%"); custom.AddInputParameter("AGENT_NAME", DbType.String, "%" + where["CARD_NO"] + "%"); } if (where.ContainsKey("BEGIN_NUM")) { customCount.AddInputParameter("BEGIN_NUM", DbType.Int32, where["BEGIN_NUM"]); custom.AddInputParameter("BEGIN_NUM", DbType.Int32, where["BEGIN_NUM"]); } if (where.ContainsKey("END_NUM")) { customCount.AddInputParameter("END_NUM", DbType.Int32, where["END_NUM"]); custom.AddInputParameter("END_NUM", DbType.Int32, where["END_NUM"]); } if (where.ContainsKey("AGENT_ID")) { customCount.AddInputParameter("AGENT_ID", DbType.String, where["AGENT_ID"]); custom.AddInputParameter("AGENT_ID", DbType.String, where["AGENT_ID"]); } if (where.ContainsKey("END_CUSTOMER_ID")) { customCount.AddInputParameter("END_CUSTOMER_ID", DbType.String, where["END_CUSTOMER_ID"]); custom.AddInputParameter("END_CUSTOMER_ID", DbType.String, where["END_CUSTOMER_ID"]); } if (where.ContainsKey("BRAND_ID")) { customCount.AddInputParameter("BRAND_ID", DbType.String, where["BRAND_ID"]); custom.AddInputParameter("BRAND_ID", DbType.String, where["BRAND_ID"]); } if (where.ContainsKey("COMPLAINT_BEGIN_DATE")) { customCount.AddInputParameter("COMPLAINT_BEGIN_DATE", DbType.String, where["COMPLAINT_BEGIN_DATE"]); custom.AddInputParameter("COMPLAINT_BEGIN_DATE", DbType.String, where["COMPLAINT_BEGIN_DATE"]); } if (where.ContainsKey("COMPLAINT_END_DATE")) { customCount.AddInputParameter("COMPLAINT_END_DATE", DbType.String, where["COMPLAINT_END_DATE"]); custom.AddInputParameter("COMPLAINT_END_DATE", DbType.String, where["COMPLAINT_END_DATE"]); } if (where.ContainsKey("UPDATED_BEGIN_DATE")) { customCount.AddInputParameter("UPDATED_BEGIN_DATE", DbType.String, where["UPDATED_BEGIN_DATE"]); custom.AddInputParameter("UPDATED_BEGIN_DATE", DbType.String, where["UPDATED_BEGIN_DATE"]); } if (where.ContainsKey("UPDATED_END_DATE")) { customCount.AddInputParameter("UPDATED_END_DATE", DbType.String, where["UPDATED_END_DATE"]); custom.AddInputParameter("UPDATED_END_DATE", DbType.String, where["UPDATED_END_DATE"]); } if (where.ContainsKey("STATUS")) { customCount.AddInputParameter("STATUS", DbType.String, where["STATUS"]); custom.AddInputParameter("STATUS", DbType.String, where["STATUS"]); } try { totalRow = Convert.ToInt32(customCount.ToDataSet().Tables[0].Rows[0]["totalrow"]); totalPage = Convert.ToInt32(Math.Ceiling(totalRow * 1.0 / pageSize)); totalPage = totalPage == 0 ? 1 : totalPage; if (totalPage < currPage) { currPage = totalPage; } dtPage = custom.ToDataTable(pageSize, currPage); } catch (Exception ex) { Log.WriteLog(ex.Message); return(null); } Dictionary <String, Object> dic = new Dictionary <String, Object>(); dic.Add("table", dtPage); dic.Add("totalRow", totalRow); dic.Add("totalPage", totalPage); return(dic); }