public DataTable GetCheckItem(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT mid,device_type_id,item_id,project_type,detect_method,detect_unit,operation_password,food_code,invalid_value,check_hole1,check_hole2,"); sb.Append("wavelength,pre_time,dec_time,stdA0,stdA1,stdA2,stdA3,stdB0,stdB1,stdB2,stdB3,stdA,stdB,national_stdmin,national_stdmax,yin_min,yin_max,yang_min,yang_max,yinT,yangT,absX,ctAbsX"); sb.Append(",division,parameter,trailingEdgeC,trailingEdgeT,suspiciousMin,suspiciousMax,reserved1,reserved2,reserved3,reserved4,reserved5,m.remark,m.delete_flag,m.create_by,m.create_date,m.update_by,m.update_date,d.detect_item_name as item FROM MachineItem m,DetectItem d where d.cid=item_id and "); } else if (type == 1) { sb.Append("SELECT tid,sampling_id,sample_code,food_id,food_name,sample_number,purchase_amount,sample_date,purchase_date,item_id,item_name,origin,supplier,supplier_address,supplier_person,supplier_phone,batch_number,status,recevie_device,ope_shop_name,remark,param1,param2,param3,s_id,s_sampling_no,s_sampling_date,s_point_id,s_reg_id,s_reg_name,s_reg_licence,s_reg_link_person,s_reg_link_phone,s_ope_id,s_ope_shop_code,s_ope_shop_name,s_qrcode,s_task_id,s_status,s_place_x,s_place_y,s_sampling_userid,s_sampling_username,s_ope_signature,s_create_by,s_create_date,s_update_by,s_update_date,s_sheet_address,s_param1,s_param2,s_param3,t_id,t_task_code,t_task_title,t_task_content,t_task_detail_pId,t_project_id,t_task_type,t_task_source,t_task_status,t_task_total,t_sample_number,t_task_sdate,t_task_edate,t_task_pdate,t_task_fdate,t_task_departId,t_task_announcer,t_task_cdate,t_remark,t_view_flag,t_file_path,t_delete_flag,t_create_by,t_create_date,t_update_by,t_update_date,td_id,td_task_id,td_detail_code,td_sample_id,td_sample,td_item_id,td_item,td_task_fdate,td_receive_pointid,td_receive_point,td_receive_nodeid,td_receive_node,td_receive_userid,td_receive_username,td_receive_status,td_task_total,td_sample_number,td_remark,mokuai,Checktype FROM KTask"); } else if (type == 2) { sb.Append("select cid from DetectItem where "); } if (!whereSql.Equals(string.Empty)) { //sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); sb.Append(" desc "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "machineitem" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["machineitem"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <param name="type">查询不同情况</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; sb.Length = 0; try { if (type == 0) { // selectSql = sb.Append("SELECT "); sb.Append(" SysCode,MachineName,ShortCut,MachineModel,Company,Protocol"); sb.Append(",LinkComNo,IsSupport,TestValue,TestSign,LinkStdCode,IsShow"); sb.Append(" FROM tMachine"); } else if (type == 1) { sb.Append("SELECT MachineName,SysCode FROM tMachine"); } else if (type == 2) { sb.Append("SELECT SysCode,TestValue,LinkStdCode,LinkComNo,IsSupport FROM tMachine"); } if (whereSql != null && whereSql.Length > 0) { sb.Append(" WHERE "); sb.Append(whereSql); } if (orderBySql != null && orderBySql.Length != 0) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1]; cmd[0] = sb.ToString(); string[] names = new string[1]; names[0] = "Machine"; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Machine"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsMachine",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; sb.Length = 0; try { if (type == 0) { sb.Append("SELECT SYSCODE,STDCODE,ITEMDES,CHECKTYPE,"); sb.Append("StandardCode,StandardValue,Unit,DemarcateInfo,"); sb.Append("TestValue,OperateHelp,CheckLevel,IsReadOnly,IsLock,Remark"); sb.Append(" FROM TCHECKITEM"); } else if (type == 1) { sb.Append("SELECT ItemDes,StdCode,SysCode FROM tcheckitem");//SysCode } else if (type == 11) { sb.Append("SELECT ItemDes,SysCode FROM tcheckitem");//SysCode } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "CheckItem" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["CheckItem"]; sb.Length = 0; } catch (Exception e) { // Log.WriteLog("查询clsCheckItem",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetDataTable_ReportEx(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dtbl = null; sb.Length = 0; try { sb.Append("SELECT"); sb.Append(" (select ShortName from tUserUnit where sysCode='" + "') As ShortName,"); sb.Append("A.CheckNo,B.FullName As ComName,B.Address,(select Name from tCompanyKind where tCompanyKind.sysCode=B.KindCode) As CompanyKind,"); sb.Append("B.CompanyID,B.PostCode,B.LinkMan,B.LinkInfo,B.OtherCodeInfo,(select Name from tFoodClass where A.FoodCode=tFoodClass.SysCode) As FoodName,"); sb.Append("A.CheckType,A.SampleModel,A.SampleLevel,"); sb.Append("A.SampleState,A.Provider,A.SampleNum,A.SampleBaseNum,A.ImportNum,A.SentCompany,A.SaveNum,A.Remark,A.TakeDate,"); sb.Append("(select Name from tUserInfo where tUserInfo.UserCode=A.Checker) as Checker,"); sb.Append("(select ItemDes from tCheckItem where A.CheckTotalItem=tCheckItem.SysCode) as CheckItem,"); sb.Append("(select StdDes from tStandard where A.Standard=tStandard.SysCode) As ReferStandard,"); sb.Append("A.StandValue as StandardValue,"); sb.Append("A.CheckValueInfo,A.ResultInfo,A.SampleCode,A.Result,A.CheckStartDate,"); sb.Append("(select Name from tUserInfo where tUserInfo.UserCode=A.Assessor) as Assessor"); sb.Append(" from tResult As A left join tCompany As B on A.CheckedCompany=B.SysCode"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Result" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sb.Length = 0; } catch (Exception e) { // Log.WriteLog("查询clsResult",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <param name="type">查询类别</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT A.SYSCODE,A.STDCODE,A.FULLNAME,A.SHORTNAME,A.DISPLAYNAME,A.SHORTCUT,A.DISTRICTCODE,B.NAME AS DISTRICTNAME,A.POSTCODE,A.ADDRESS,A.LINKMAN,A.LINKINFO,A.WWWINFO,A.ISREADONLY,A.ISLOCK,A.REMARK,A.CompanyId FROM TUSERUNIT AS A LEFT JOIN TDISTRICT AS B ON A.DISTRICTCODE=B.SYSCODE "); } else if (type == 1) { sb.Append("SELECT FULLNAME,STDCODE,SYSCODE,CompanyID FROM TUSERUNIT"); } else if (type == 2) { sb.Append("SELECT A.DISTRICTCODE,B.[name] FROM TUSERUNIT AS A LEFT JOIN TDISTRICT AS B ON A.DISTRICTCODE=B.SYSCODE"); } else if (type == 3) { sb.Append("SELECT FULLNAME,STDCODE,ShortCut,DistrictCode FROM TUSERUNIT"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "UserUnit" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["UserUnit"]; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetDataTable_Report(string whereSql, string orderBySql) { string sErrMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; sb.Append("SELECT A.CheckedCompany & Year(Date()) & A.CheckNo AS CheckSheetNO,"); sb.Append("(SELECT Name FROM tFoodClass WHERE A.FoodCode=tFoodClass.SysCode) AS FoodName,"); sb.Append("A.SampleModel,A.SampleState,A.ProduceInfo,A.SampleNum,A.TakeDate,B.Address,"); sb.Append("(SELECT Name FROM tUserInfo WHERE tUserInfo.UserCode=A.Checker) AS Checker,"); sb.Append("(SELECT FullName FROM tCompany WHERE A.CheckedCompany=tCompany.SysCode) AS ComName,"); sb.Append("(SELECT ItemDes FROM tCheckItem WHERE A.CheckTotalItem=tCheckItem.SysCode) AS CheckItem,"); sb.Append("(SELECT StandardValue FROM tCheckItem WHERE A.CheckTotalItem=tCheckItem.SysCode) AS StandardValue,"); sb.Append("A.CheckValueInfo,A.ResultInfo,A.CheckStartDate,A.Result,A.Remark"); sb.Append(" FROM tResult AS A LEFT JOIN tCompany AS B on A.CheckedCompany=B.SysCode"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Result" }; dtbl = DataBase.GetDataSet(cmd, names, out sErrMsg).Tables["Result"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsResult",e); sErrMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { if (type == 0) { sb.Append("SELECT Cdcode,Cdbuslicence,CAllow,Ciid,Ciname,Cdname,Cdcardid,DisplayName,RegCapital,Unit,Incorporator,RegDate,PostCode,Address,LinkMan,LinkInfo,CreditLevel,CreditRecord,ProductInfo,OtherInfo,CheckLevel,FoodSafeRecord,IsReadOnly,Remark FROM tProprietors "); } else if (type == 1) { sb.Append("Select Ciname,Cdname,Incorporator,DisplayName from tProprietors "); } else if (type == 2) { sb.Append("SELECT c.fullname,t.[name] AS CompanyType,d.[name] AS OrganizationName,c.SysCode,c.Incorporator "); sb.Append("FROM (tcompany AS c INNER JOIN tcompanyKind AS t on c.kindcode=t.syscode) "); sb.Append("INNER JOIN tdistrict AS d on c.districtcode = d.syscode"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Proprietors" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Proprietors"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsProprietors",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; //string selectSql = string.Empty; if (type == 0) { sb.Append("SELECT SysCode,StdCode,Name,ShortCut,CheckLevel,CheckItemCodes"); sb.Append(",CheckItemValue,IsLock,IsReadOnly,Remark,FoodProperty FROM tfoodclass"); } else if (type == 1) { sb.Append("SELECT SysCode,Name,StdCode FROM tfoodclass"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); sb.Append(" ASC "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "foodclass" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["foodclass"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsFoodClass",e); errMsg = e.Message; } return(dtbl); }
public DataTable GetDataTable_ReportGZ(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { sb.Append("SELECT"); sb.AppendFormat(" (SELECT ShortName from tUserUnit where sysCode='{0}') As ShortName,A.CheckNo,", string.Empty); sb.Append("(select Name from tFoodClass where A.FoodCode=tFoodClass.SysCode) As FoodName,A.Provider,A.SampleModel,(select StdDes from tStandard where A.Standard=tStandard.SysCode) As ReferStandard,"); sb.Append("A.ProduceDate,A.Price,A.ImportNum+A.Unit As ImportNumUnit,A.SaveNum+A.Unit As SaveNumUnit,(select FullName from tCompany where A.ProduceCompany=tCompany.SysCode And tCompany.Property='生产单位') As ProduceCompanyName,"); sb.Append("(select LinkMan from tCompany where A.ProduceCompany=tCompany.SysCode And tCompany.Property='生产单位') As ProduceLinkMan,B.CompanyID,B.Incorporator,B.LinkInfo,B.PostCode,B.FullName As ComName,B.Address,"); sb.Append("A.SampleState,A.Provider,A.SampleNum,A.SampleBaseNum,A.ImportNum,A.SentCompany,A.SaveNum,A.Remark,A.TakeDate,"); sb.Append("(select Name from tDistrict where B.DistrictCode=tDistrict.SysCode) As DistrictName,(select Name from tCompanyKind where B.KindCode=tCompanyKind.SysCode) As KindName,"); sb.Append("B.ComProperty,(select FullName from tUserUnit where sysCode='0001') As CheckUnitFullName,(select LinkMan from tUserUnit where sysCode='0001') As CheckUnitLinkMan,"); sb.Append("(select ItemDes from tCheckItem where A.CheckTotalItem=tCheckItem.SysCode) as CheckItem,(select MachineName from tMachine where A.CheckMachine=tMachine.SysCode) as MachineName,A.TakeDate,str(A.SampleNum)+A.SampleUnit As SampleNumUnit,"); sb.Append("str(A.SampleBaseNum)+A.SampleUnit As SampleBaseNumUnit,A.StandValue+A.ResultInfo As StandValueInfo,A.CheckValueInfo+A.ResultInfo As CheckValueInfo,A.Result,A.CheckederVal,A.IsSentCheck,A.CheckederRemark,A.Remark "); sb.Append(" from tResult As A left join tCompany As B on A.CheckedCompany=B.SysCode"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Result" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsResult",e); errMsg = e.Message; } return(dt); }
public static int UpdateOldResult(out string sErrMsg) { sErrMsg = string.Empty; int rtn; try { string sql = "select A.SysCode,A.CheckedCompany,B.FullName As CheckedCompanyName,B.DisplayName As CheckedComDis,B.StdCode As CheckedComStdCode,A.UpperCompany from tResult As A Left Join tCompany As B On A.CheckedCompany=B.SysCode"; string[] sCmd = new string[1]; sCmd[0] = sql; string[] sNames = new string[1]; sNames[0] = "Result"; DataTable dt = DataBase.GetDataSet(sCmd, sNames, out sErrMsg).Tables["Result"]; for (int i = 0; i < dt.Rows.Count; i++) { string syscode = dt.Rows[i]["SysCode"].ToString(); string com_FullName = dt.Rows[i]["CheckedCompanyName"].ToString(); string comdis = dt.Rows[i]["CheckedComDis"].ToString(); string comstdcode = dt.Rows[i]["CheckedComStdCode"].ToString(); string upperCompanyName = string.Empty; string upperCompany = string.Empty; if (comstdcode.Length >= 6) { if (comstdcode.Length == 7) { upperCompanyName = com_FullName; upperCompany = dt.Rows[i]["CheckedCompany"].ToString();; } else { upperCompanyName = dt.Rows[i]["UpperCompany"].ToString(); upperCompany = clsCompanyOpr.CodeFromStdCode(comstdcode.Substring(0, 6)); } string updatesql = "Update tResult Set CheckedCompanyName='" + com_FullName + "',CheckedComDis='" + comdis + "',UpperCompany='" + upperCompany + "',UpperCompanyName='" + upperCompanyName + "' Where SysCode='" + syscode + "'"; DataBase.ExecuteCommand(updatesql, out sErrMsg); } } rtn = 1; } catch (Exception e) { sErrMsg = e.Message; rtn = -1; } return(rtn); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; sb.Length = 0; try { //string selectSql=string.Empty; if (type == 0) { sb.Append("SELECT SysCode,StdCode,Name,ShortCut,DistrictIndex,CheckLevel,"); sb.Append("IsLocal,IsLock,IsReadOnly,Remark"); sb.Append(" FROM tProduceArea"); } else if (type == 1) { sb.Append("SELECT SYSCODE,NAME,STDCODE FROM TPRODUCEAREA"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "ProduceArea" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["ProduceArea"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsProduceArea",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 获取用户实例 /// </summary> /// <param name="whereSql"></param> /// <returns></returns> public clsUserInfo GetInfo(string whereSql) { string errMsg = string.Empty; DataTable dt = null; clsUserInfo userInfo = null; try { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT UserCode,LoginID,Name,PassWord,UnitCode,WebLoginID,WebPassWord,IsLock,IsAdmin,Remark FROM tUserInfo"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "UserInfo" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["UserInfo"]; if (dt.Rows.Count > 0) { userInfo = new clsUserInfo(); userInfo.UserCode = dt.Rows[0]["UserCode"].ToString(); userInfo.LoginID = dt.Rows[0]["LoginID"].ToString(); userInfo.Name = dt.Rows[0]["Name"].ToString(); userInfo.PassWord = dt.Rows[0]["PassWord"].ToString(); userInfo.UnitCode = dt.Rows[0]["UnitCode"].ToString(); userInfo.WebLoginID = dt.Rows[0]["WebLoginID"].ToString(); userInfo.WebPassWord = dt.Rows[0]["WebPassWord"].ToString(); userInfo.Remark = dt.Rows[0]["Remark"].ToString(); userInfo.IsLock = Convert.ToBoolean(dt.Rows[0]["IsLock"]); userInfo.IsAdmin = Convert.ToBoolean(dt.Rows[0]["IsAdmin"]); } } catch (Exception e) { errMsg = e.Message; } return(userInfo); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT SYSCODE,STDCODE,NAME,COMPANYPROPERTY,ISLOCK,ISREADONLY,REMARK,Ksign FROM TCOMPANYKIND"); } else if (type == 1) { sb.Append("SELECT SYSCODE,NAME,STDCODE FROM TCOMPANYKIND"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; //cmd[0]=selectSql; string[] names = new string[1] { "CompanyKind" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["CompanyKind"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsCompanyKind",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT SysCode,StdCode,Name,ShortCut,DistrictIndex,CheckLevel,"); sb.Append("IsLocal,IsLock,IsReadOnly,Remark"); sb.Append(" FROM tDistrict"); } else if (type == 1) { sb.Append("SELECT SysCode,Name,StdCode FROM tDistrict"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "District" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["District"]; } catch (Exception e) { //Log.WriteLog("查询clsDistrict",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT NAME,ISLOCK,ISREADONLY,REMARK FROM TCHECKTYPE"); } else if (type == 1) { sb.Append("SELECT NAME FROM tchecktype"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "CheckType" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["CheckType"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsCheckType",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; if (type == 0) { sb.Append("SELECT STDNAME,ISLOCK,ISREADONLY,REMARK FROM TSTANDARDTYPE"); } else if (type == 1) { sb.Append("select StdName from tStandardType"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" where "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" order by "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "StandardType" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["StandardType"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsStandardType",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetTreeListTable(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; sb.Append("SELECT SysCode,Name FROM tfoodclass WHERE IsLock=false AND IsReadOnly=true AND LEN(SysCode)=10 AND LEN(CheckItemCodes)>0 "); sb.Append(" UNION "); sb.Append(" SELECT SysCode,Name FROM tfoodclass WHERE IsLock=false AND IsReadOnly=true AND LEN(SysCode)>10 AND LEN(CheckItemCodes)>0 "); if (!whereSql.Equals(string.Empty)) { sb.Append(" AND "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); sb.Append(" ASC "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "foodclass" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["foodclass"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsFoodClass",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; try { string selectSql = string.Empty; if (type == 0) { selectSql = "select UserCode,LoginID,Name,PassWord,UnitCode,WebLoginID,WebPassWord,IsLock,IsAdmin,Remark from tUserInfo"; } else if (type == 1) { selectSql = "select Name,UserCode from tUserInfo"; } if (!whereSql.Equals(string.Empty)) { selectSql += " where " + whereSql; } if (!orderBySql.Equals(string.Empty)) { selectSql += " order by " + orderBySql; } string[] cmd = new string[1] { selectSql }; string[] names = new string[1] { "UserInfo" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["UserInfo"]; } catch (Exception e) { errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; sb.Append("SELECT SysCode,OptDes,OptType,OptValue,IsReadOnly,IsDisplay,IsLock,Remark FROM tSysOpt"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] sCmd = new string[1] { sb.ToString() }; string[] sNames = new string[1] { "SysOpt" }; dt = DataBase.GetDataSet(sCmd, sNames, out errMsg).Tables["SysOpt"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsSysOpt",e); errMsg = e.Message; } return(dt); }
public DataTable GetCompany(string whereSql, string orderBySql, int type, out string errMsg) { errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; if (type == 0) { sb.Append("select b.bid as bid,b.reg_id as reg_id,b.ope_shop_name as ope_shop_name,b.ope_shop_code as ope_shop_code,r.rid as rid,r.reg_name,r.link_user,r.reg_address,r.link_phone,r.update_date,r.depart_id from regulardata r,business b "); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); sb.Append(" desc "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "business" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["business"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
public DataTable GetRegulator(string name, int type) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { if (type == 1) { sb.Append("select r.reg_name,r.link_user,r.reg_address,r.link_phone,r.update_date "); sb.Append(" from regulardata r ");//where b.reg_id=r.rid } else if (type == 2) { sb.Append("SELECT * FROM tCompany"); } if (!name.Equals(string.Empty)) { //sb.Append(" WHERE "); sb.Append(name); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Company" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Company"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="name">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string name) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { //if (type == 1) //{ // sb.Append("select b.ope_shop_name,* from regulardata r,buiness b where b.reg_id=r.rid"); //} //else if(type ==2) //{ // sb.Append("SELECT * FROM tCompany"); //} sb.Append("SELECT * FROM tCompany"); if (!name.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(name); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Company" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Company"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
public DataTable getlastoneItem(string where, string ordersql) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; sb.AppendFormat(" Select Top {0} ", 1); sb.Append(" * From KTask order by ID desc "); if (!where.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(where); } if (!ordersql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(ordersql); sb.Append(" ASC "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "task" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["task"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsTask",e); errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dt = null; try { sb.Length = 0; sb.Append("SELECT CHECKLEVEL FROM TCHECKLEVEL"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "CheckLevel" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["CheckLevel"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsCheckLevel",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 查询全部数据 /// </summary> /// <returns></returns> public DataTable GetAllAsDataTable() { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { sb.Append("SELECT * FROM tAtp"); string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Atp" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Atp"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; strB.Length = 0; try { string selectSql = string.Empty; if (type == 0) { strB.Append("Select FtypeNmae,Name,ItemDes,StandardValue,Demarcate,Unit From ttStandDecide "); } else if (type == 1) { strB.Append("Select FtypeNmae,SampleNum,Name,ItemDes,StandardValue,Demarcate,Unit From ttStandDecide "); } else if (type == 2)//获取样品标准库的最后更新时间 { strB.Append("Select Top 1 * From ttStandDecide Order By UDate Desc"); } else if (type == 3)//获取检测项目的最后更新时间 { strB.Append("Select Top 1 * From tCheckItem Order By UDate Desc"); } else if (type == 4)//获取检测标准的最后更新时间 { strB.Append("Select Top 1 * From tStandard Order By UDate Desc"); } else if (type == 5) { strB.Append("select IIF(s.use_default = '0',s.detect_sign,item.detect_sign) as detect_sign,IIF(s.use_default = '0',s.detect_value_unit,item.detect_value_unit) as detect_value_unit,IIF(s.use_default = '0',s.detect_value,item.detect_value) as detect_value,"); strB.Append("d.std_code,f.food_name,item.detect_item_name from SampleStandard s,DetectItem item,foodlist f,StandardList d"); } else if (type == 6)//查询父ID { strB.Append("select parent_id from foodlist "); } else if (type == 7)//根据父ID查询数据 { strB.Append("select IIF(s.use_default = '1',s.detect_sign,item.detect_sign) as detect_sign,IIF(s.use_default = '1',s.detect_value_unit,item.detect_value_unit) as detect_value_unit,IIF(s.use_default ='1',"); strB.Append("s.detect_value,item.detect_value) as detect_value,d.std_code,f.food_name,item.detect_item_name from SampleStandard s,DetectItem item,foodlist f,StandardList d "); } else if (type == 8) { strB.Append("select s.std_code, d.detect_item_name,d.detect_sign,d.detect_value,d.detect_value_unit from DetectItem d,StandardList s "); } if (!whereSql.Equals(string.Empty)) { strB.Append(" Where "); strB.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { strB.Append(" Order By "); strB.Append(orderBySql); } string[] cmd = new string[1] { strB.ToString() }; string[] names = new string[1] { "StandDecide" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["StandDecide"]; strB.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
/// <summary> /// 根据查询串条件查询记录 /// 注:所有字一段名称都是根据上传接口所定的字段来定义的 /// </summary> /// <param name="strWhere">查询条件串,不含Where</param> /// <param name="orderBy">排序串,不含Order</param> /// <returns></returns> public DataTable GetUploadDataTable(string strWhere, string orderBy, string appTag) { string errMsg = string.Empty; DataTable dtbl = null; sb.Length = 0; try { sb.Append("SELECT "); //新两个字段 sb.Append("A.SysCode,A.ResultType, A.CheckNo,A.SampleCode,A.StdCode,"); sb.Append("A.CheckedCompany AS CheckedCompanyCode,"); //企业编号 //如是是工商版/农检片本是两级 要查询"UpperCompanyName--CheckedCompany" //如果是食药 餐饮版要查询"CheckedCompanyName--CheckedCompany" sb.Append("A.UpperCompanyName AS CheckedCompany, "); sb.Append("A.CheckedCompanyName AS CheckedCompanyInfo,A.CheckedComDis,"); sb.Append("A.CheckPlaceCode,"); sb.Append("(SELECT J.Name FROM tDistrict AS J WHERE A.CheckPlaceCode =J.SysCode)AS CheckPlace,");//检测机构名称 //if( ) //{ sb.Append("(select z.Cdcode from tProprietors as z where z.Cdname=A.CheckedCompanyName and z.Ciname=A.UpperCompanyName)as CheckedDealerCode,"); //被检经营户编号 //} sb.Append("(SELECT B.Name FROM tFoodClass AS B WHERE A.FoodCode=B.SysCode)AS FoodName,"); //查询食品名称 A.FoodCode, sb.Append("A.SentCompany,A.Provider,A.ProduceDate,"); sb.Append("(SELECT L.FullName FROM tCompany AS L WHERE A.ProduceCompany=L.SysCode)AS ProduceCompany,"); //A.ProduceCompanyName sb.Append("(SELECT Q.Name FROM tProduceArea AS Q WHERE Q.SysCode=A.ProducePlace)AS ProducePlace,"); //查询产地名称A.ProducePlace, //区域目前只构造五级 sb.Append("( IIf(Len(A.ProducePlace)=6,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,6)),'')+ IIf(Len(A.ProducePlace)>6,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,9))+'/','') + IIf(Len(A.ProducePlace)>9,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,12))+'/','') + IIf(Len(A.ProducePlace)>12,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,15))+'/','')+ IIf(Len(A.ProducePlace)>15,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,18))+'/','')+IIf(Len(A.ProducePlace)>18,(SELECT R.Name FROM tProduceArea AS R WHERE R.SysCode=MID(A.ProducePlace,1,21))+'/','') ) AS ProducePlaceInfo,"); //ProducePlaceInfo 需要这个字段 sb.Append("A.ImportNum,A.SaleNum, A.SaveNum, A.Price,A.SampleNum, A.SampleBaseNum,"); sb.Append("A.TakeDate,"); sb.Append("A.CheckStartDate,A.CheckEndDate,"); sb.Append("A.Unit,A.SampleUnit,A.SampleModel, A.SampleLevel,A.SampleState,"); sb.Append("(SELECT S.StdDes FROM tStandard AS S WHERE A.Standard=S.SysCode)AS Standard,"); sb.Append("K.MachineName AS CheckMachine,K.MachineModel AS CheckMachineModel,K.Company AS MachineCompany,"); sb.Append("(SELECT O.ItemDes FROM tCheckItem AS O WHERE O.SysCode=A.CheckTotalItem)AS CheckTotalItem,");//查询检测项目名称 sb.Append("A.CheckValueInfo,A.StandValue, A.Result,A.ResultInfo,A.OrCheckNo,"); sb.Append("A.UpperCompany,A.UpperCompanyName,"); sb.Append("A.CheckType,"); sb.Append("H.FullName AS CheckUnitName,H.ShortName AS CheckUnitInfo,"); //联合新增加A.CheckUnitCode, sb.Append("(SELECT I.Name FROM tUserInfo AS I WHERE I.UserCode=A.Checker) AS Checker,"); //A.Organizer sb.Append("(SELECT M.Name FROM tUserInfo AS M WHERE M.UserCode=A.Assessor) AS Assessor,"); //A.Organizer Assessor sb.Append("(SELECT N.Name FROM tUserInfo AS N WHERE N.UserCode=A.Organizer) AS Organizer,"); //A.Organizer sb.Append("A.Remark,A.CheckPlanCode,A.CheckederVal,A.IsSentCheck,A.CheckederRemark,"); sb.Append("IIf(A.IsReSended, '是', '否')AS IsReSended,"); sb.Append("'1' AS UpLoader,A.Notes"); sb.Append(" FROM (tResult AS A LEFT JOIN tUserUnit AS H ON A.CheckUnitCode=H.SysCode)");//联合检测单位 sb.Append(" LEFT JOIN tMachine AS K On A.CheckMachine=K.SysCode"); if (!strWhere.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(strWhere); } if (!orderBy.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBy); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Result" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
/// <summary> /// 获取重传数据列表 /// </summary> /// <param name="whereSql"></param> /// <param name="orderBySql"></param> /// <returns></returns> public DataTable GetAsReSendDataTable(string whereSql, string orderBySql) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { sb.Append("SELECT A.IsSended, A.SendedDate, A.Sender, P.Name AS SenderName,"); sb.Append("A.IsReSended,A.SysCode, A.CheckNo,A.CheckPlanCode, A.CheckedCompany,"); sb.Append("A.CheckedCompanyName,A.CheckedComDis, A.UpperCompany,A.UpperCompanyName,A.FoodCode, A.FoodName,"); sb.Append("A.CheckType, A.SampleModel, A.SampleLevel, A.SampleState, A.Provider, A.StdCode, A.OrCheckNo,"); sb.Append("A.ProduceCompany, A.ProduceCompanyName,A.ProducePlace,A.ProducePlaceName, A.ProduceDate,"); sb.Append("A.ImportNum,A.SaleNum, A.SaveNum, A.Price,A.Unit, A.SampleNum, A.SampleBaseNum, A.SampleUnit,"); sb.Append("A.SentCompany, A.Remark,A.CheckederVal,A.CheckederRemark,A.Notes, A.TakeDate, A.OrganizerName,"); sb.Append("A.CheckTotalItem, A.CheckTotalItemName, A.Standard, A.StandardName, "); sb.Append("A.CheckValueInfo,A.ResultInfo,A.StandValue,A.SampleCode, A.Result,"); sb.Append("A.IsSentCheck, A.CheckStartDate, A.Checker, A.CheckerName, A.Assessor, A.AssessorName,"); sb.Append("A.CheckUnitCode, A.CheckUnitName,A.ResultType, A.MachineName, A.CheckPlaceCode, A.CheckPlace,"); sb.Append("A.CheckEndDate, A.CheckMachine, A.Organizer "); sb.Append(" FROM [SELECT T11.*,O.ItemDes As CheckTotalItemName "); sb.Append(" FROM (SELECT T10.*,S.StdDes As StandardName"); sb.Append(" FROM (SELECT T8.*,Q.Name As ProducePlaceName"); sb.Append(" FROM (SELECT T6.*,N.Name As OrganizerName"); sb.Append(" FROM (SELECT T5.*,M.Name As AssessorName"); sb.Append(" FROM (SELECT T4.*,L.FullName As ProduceCompanyName"); sb.Append(" FROM (SELECT T3.*,K.MachineName"); sb.Append(" FROM (SELECT T2.*,J.Name AS CheckPlace"); sb.Append(" FROM (SELECT C.*, B.Name AS FoodName,H.FullName AS CheckUnitName,I.Name AS CheckerName"); sb.Append(" FROM tResult AS C, tFoodClass AS B, tUserUnit AS H, tUserInfo AS I"); sb.Append(" WHERE C.FoodCode=B.SysCode And C.CheckUnitCode=H.SysCode And C.Checker=I.UserCode) As T2"); sb.Append(" LEFT JOIN tDistrict As J On T2.CheckPlaceCode =J.SysCode) As T3"); sb.Append(" LEFT JOIN tMachine As K On T3.CheckMachine=K.SysCode) As T4"); sb.Append(" LEFT JOIN tCompany As L On T4.ProduceCompany=L.SysCode) As T5"); sb.Append(" LEFT JOIN tUserInfo As M On T5.Assessor=M.UserCode) As T6"); sb.Append(" LEFT JOIN tUserInfo As N On T6.Organizer=N.UserCode) As T8"); sb.Append(" LEFT JOIN tProduceArea As Q On T8.ProducePlace=Q.SysCode) As T10"); sb.Append(" LEFT JOIN tStandard As S On T10.Standard=S.SysCode) As T11"); sb.Append(" LEFT JOIN tCheckItem As O On T11.CheckTotalItem=O.SysCode]. AS A"); sb.Append(" LEFT JOIN tUserInfo AS P ON A.Sender=P.UserCode"); if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Result" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsResult",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 根据查询串条件查询记录 /// </summary> /// <param name="whereSql">查询条件串,不含Where</param> /// <param name="orderBySql">排序串,不含Order</param> /// <returns></returns> public DataTable GetAsDataTable(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dt = null; sb.Length = 0; try { if (type == 0) { sb.Append("SELECT A.SysCode,A.CompanyID,A.FullName,A.StdCode,A.CAllow,A.ISSUEAGENCY,A.ISSUEDATE,A.PERIODSTART,A.PERIODEND,A.VIOLATENUM,A.LONGITUDE,A.LATITUDE,A.SCOPE,A.PUNISH,A.OtherCodeInfo,"); sb.Append("A.ShortName,A.DisplayName,A.ShortCut,A.Property,A.KindCode,A.KindName,A.RegCapital,A.Unit,A.Incorporator,"); sb.Append("A.RegDate,A.DistrictCode,C.Name As DistrictName,A.PostCode,A.Address,A.LinkMan,A.LinkInfo,"); sb.Append("A.CreditLevel,A.CreditRecord,A.ProductInfo,A.OtherInfo,A.CheckLevel,A.FoodSafeRecord,"); sb.Append("A.IsReadOnly,A.IsLock,A.ComProperty,A.Remark,A.TSign"); sb.Append(" FROM [SELECT D.SysCode,D.StdCode,D.CompanyID,d.CAllow,d.ISSUEAGENCY,d.ISSUEDATE,d.PERIODSTART,d.PERIODEND,d.VIOLATENUM,d.LONGITUDE,d.LATITUDE,d.SCOPE,d.PUNISH,d.OtherCodeInfo,d.FullName,d.ShortName,d.DisplayName,d.ShortCut,d.Property,d.KindCode,B.Name As KindName,d.RegCapital,d.Unit,d.Incorporator,d.RegDate,d.DistrictCode,d.PostCode,d.Address,d.LinkMan,d.LinkInfo,d.CreditLevel,d.CreditRecord,d.ProductInfo,d.OtherInfo,d.CheckLevel,d.FoodSafeRecord,d.IsReadOnly,d.IsLock,d.Remark,d.TSign,D.ComProperty FROM tCompany As d Left Join tCompanyKind As B On d.KindCode=B.SysCode]. AS A LEFT JOIN tDistrict AS C ON A.DistrictCode=C.SysCode"); } else if (type == 1) { sb.Append(" select fullname,syscode from tCompany where TSign<>'本地增' union Select Cdname,Ciid from tProprietors "); } else if (type == 2) { sb.Append("SELECT c.fullname,t.[name] AS CompanyType,d.[name] AS OrganizationName,c.SysCode,c.Incorporator "); sb.Append("FROM (tcompany AS c INNER JOIN tcompanyKind AS t on c.kindcode=t.syscode) "); sb.Append("INNER JOIN tdistrict AS d on c.districtcode = d.syscode"); } else if (type == 3) { sb.Append("SELECT FULLNAME,STDCODE,SYSCODE,KINDCODE,DISPLAYNAME,Property,IsReadOnly,DistrictCode FROM tCompany"); } else if (type == 4) { sb.Append("SELECT FULLNAME FROM tCompany"); } else if (type == 5) { sb.Append("SELECT FULLNAME,CompanyID FROM tCompany"); } else if (type == 6)//下载被检单位,判定是否已经存在 { sb.Append("SELECT * FROM tCompany"); } else if (type == 7)//查询最近一次更新时间 { sb.Append("Select Top 1 * from tCompany Order By UDate Desc"); } else if (type == 8)//下载检测项目,判定是否已经存在 { sb.Append("SELECT * FROM tCheckItem"); } else if (type == 9)//下载检测标准,判定是否已经存在 { sb.Append("SELECT * FROM tStandard"); } else if (type == 10)//下载检测项目,查看关联数据库表tStandardType是否已有记录tTask { sb.Append("SELECT * FROM tStandardType"); } else if (type == 11)//任务更新,判定是否已经存在 { sb.Append("SELECT * FROM tTask"); } else if (type == 12)//任务更新,获取最后一次更新时间 { sb.Append("SELECT Top 1 * FROM tTask Order By UDate Desc"); } else if (type == 13) { sb.Append("select r.reg_name,r.link_user,r.reg_address,r.link_phone,r.update_date "); sb.Append(" from regulardata r"); } else if (type == 14) { sb.Append("select r.reg_name,r.link_user,r.reg_address,r.link_phone,r.update_date "); sb.Append(" from regulardata r "); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "Company" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Company"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
public DataTable GetQtask(string whereSql, string orderBySql, int type) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; if (type == 0) { //sb.Append("select * from KTask "); sb.Append("SELECT taskid,taskname,taskdate,sampleid,sample,itemid,item,CompanyID,CheckCompany,shopkou,Testmokuai,Checktype,s_sampling_no FROM KTask"); //sb.Append("CPFROM,CPEDITOR,CPPORGID,CPPORG,CPEDDATE,CPMEMO,PLANDETAIL,PLANDCOUNT FROM tTask"); } else if (type == 1) { sb.Append("select * from KTask "); //sb.Append("SELECT tid,sampling_id,sample_code,food_id,food_name,sample_number,purchase_amount,sample_date,purchase_date,item_id,item_name,origin,supplier,supplier_address,supplier_person,supplier_phone,batch_number,status,recevie_device,ope_shop_name,remark,param1,param2,param3,s_id,s_sampling_no,s_sampling_date,s_point_id,s_reg_id,s_reg_name,s_reg_licence,s_reg_link_person,s_reg_link_phone,s_ope_id,s_ope_shop_code,s_ope_shop_name,s_qrcode,s_task_id,s_status,s_place_x,s_place_y,s_sampling_userid,s_sampling_username,s_ope_signature,s_create_by,s_create_date,s_update_by,s_update_date,s_sheet_address,s_param1,s_param2,s_param3,t_id,t_task_code,t_task_title,t_task_content,t_task_detail_pId,t_project_id,t_task_type,t_task_source,t_task_status,t_task_total,t_sample_number,t_task_sdate,t_task_edate,t_task_pdate,t_task_fdate,t_task_departId,t_task_announcer,t_task_cdate,t_remark,t_view_flag,t_file_path,t_delete_flag,t_create_by,t_create_date,t_update_by,t_update_date,td_id,td_task_id,td_detail_code,td_sample_id,td_sample,td_item_id,td_item,td_task_fdate,td_receive_pointid,td_receive_point,td_receive_nodeid,td_receive_node,td_receive_userid,td_receive_username,td_receive_status,td_task_total,td_sample_number,td_remark,mokuai,Checktype FROM KTask"); //sb.Append("SELECT tid,sampling_id,sample_code,food_id,food_name,sample_number,purchase_amount,sample_date,purchase_date,item_id,item_name,origin,supplier,supplier_address,supplier_person,supplier_phone,batch_number,status,recevie_device,ope_shop_name,remark,param1,param2,param3,s_id,s_sampling_no,convert(datetime,s_sampling_date) as dt,s_point_id,s_reg_id,s_reg_name,s_reg_licence,s_reg_link_person,s_reg_link_phone,s_ope_id,s_ope_shop_code,s_ope_shop_name,s_qrcode,s_task_id,s_status,s_place_x,s_place_y,s_sampling_userid,s_sampling_username,s_ope_signature,s_create_by,s_create_date,s_update_by,s_update_date,s_sheet_address,s_param1,s_param2,s_param3,t_id,t_task_code,t_task_title,t_task_content,t_task_detail_pId,t_project_id,t_task_type,t_task_source,t_task_status,t_task_total,t_sample_number,t_task_sdate,t_task_edate,t_task_pdate,t_task_fdate,t_task_departId,t_task_announcer,t_task_cdate,t_remark,t_view_flag,t_file_path,t_delete_flag,t_create_by,t_create_date,t_update_by,t_update_date,td_id,td_task_id,td_detail_code,td_sample_id,td_sample,td_item_id,td_item,td_task_fdate,td_receive_pointid,td_receive_point,td_receive_nodeid,td_receive_node,td_receive_userid,td_receive_username,td_receive_status,td_task_total,td_sample_number,td_remark,mokuai FROM KTask"); } else if (type == 2) { sb.Append("SELECT taskid,taskname,taskdate,sampleid,sample,itemid,item,CompanyID,CheckCompany,shopkou,Testmokuai,Checktype,s_sampling_no FROM KTask"); } if (type == 3) { sb.Append("SELECT ID FROM KTask"); //// SELECT CPCODE,CPTITLE,CPSDATE,CPEDATE,CPTPROPERTY,CPFROM,CPEDITOR,CPPORGID,CPPORG,CPEDDATE,PLANDETAIL, ////PLANDCOUNT,BAOJINGTIME,(SELECT COUNT(*) FROM ttResultSecond WHERE CheckPlanCode=m.CPCODE ) AS v30,'未完成' as finish //// FROM tTask as m where (cdate(BAOJINGTIME)>=#2015-10-26 22:21:23#) //sb.Append(" SELECT CPCODE,CPTITLE,CPSDATE,CPEDATE,CPTPROPERTY,CPFROM,CPEDITOR,CPPORGID,CPPORG,CPEDDATE,PLANDETAIL,"); //sb.Append("PLANDCOUNT,BAOJINGTIME,(SELECT COUNT(*) FROM ttResultSecond WHERE CheckPlanCode=m.CPCODE ) AS v30,'未完成' as finish"); //sb.Append(",(v30/PLANDCOUNT) as Num FROM tTask as m"); } if (!whereSql.Equals(string.Empty)) { sb.Append(" WHERE "); sb.Append(whereSql); } if (!orderBySql.Equals(string.Empty)) { sb.Append(" ORDER BY "); sb.Append(orderBySql); sb.Append(" desc "); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "task" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["task"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsTask",e); errMsg = e.Message; } return(dtbl); }