/// <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); }
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">查询条件</param> /// <param name="InterfaceType">接口类型</param> /// <param name="EACHDISTRICT">区域</param> /// <returns></returns> public DataTable ExportData(string whereSql, string InterfaceType, string EACHDISTRICT) { string errMsg = string.Empty; DataTable dt = null; sql.Length = 0; try { sql.Append(" SELECT "); sql.Append(" ResultType AS 检测手段,CheckNo AS 检测编号,SampleCode AS 样品编号,CheckPlaceCode AS 行政机构编号,CheckPlace AS 行政机构名称,FoodName AS 样品名称,FoodType AS 样品种类,TakeDate AS 抽检日期,CheckStartDate AS 检测时间,Standard AS 检测依据,CheckMachine AS 检测设备,CheckMachineModel AS 检测设备型号,MachineCompany AS 检测设备厂商, "); sql.Append(" CheckTotalItem AS 检测项目,CheckValueInfo AS 检测值,StandValue AS 检测标准值,Result AS 检测结论,ResultInfo AS 检测值单位,CheckUnitName AS 检测单位,CheckUnitInfo AS 检测人姓名,Organizer AS 抽样人,UpLoader AS 基层上传人, "); sql.Append(" ReportDeliTime AS 检测报告送达时间,IsReconsider AS 是否提出复议,ReconsiderTime AS 提出复议时间,ProceResults AS 处理结果,CheckedCompanyCode AS 被检单位编号,CheckedCompany AS 被检单位名称,CheckedComDis AS 档口、车牌号、门牌号,CheckPlanCode AS 任务编号, "); sql.Append(" DateManufacture AS 生产日期,CheckMethod AS 检测方法,APRACategory AS 单位类别,Hole AS 检测孔,SamplingPlace AS 抽样地点,CheckType AS 检测类型,ContrastValue AS 对照值,CKCKNAMEUSID,SysCode AS 系统编号 "); //if (InterfaceType.Equals("AH"))//如果是安徽版本 //{ // sql.Append(",fTpye AS 种类编号,testPro AS 项目编号,quanOrQual AS 结果类型,dataNum AS 结果编号,checkedUnit AS 被检单位编号"); //} //else if (InterfaceType.Equals("ZH"))//如果是广东省智慧云平台 //{ // sql.Append(",DeviceId AS 唯一设备ID,SampleId AS 快检单号"); //} //else if (InterfaceType.Equals("GS"))//如果是甘肃专用 //{ // sql.Append(",ProduceCompany AS 生产单位"); //} //else if (InterfaceType.Equals("KS")) //{ sql.Append(",DABH AS 经营户身份证号码,PositionNo AS 经营户摊位编号,DABHName AS 经营户姓名,SubItemCode AS 品种编码,SubItemName AS 品种名称,QuickCheckItemCode AS 项目大类编号,QuickCheckSubItemCode AS 项目小类编号"); //} sql.Append(" FROM ttResultSecond "); if (!whereSql.Equals(string.Empty)) { sql.AppendFormat(" WHERE {0}", whereSql); } string[] cmd = new string[1] { sql.ToString() }; string[] names = new string[1] { "Result" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sql.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 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="PageSize"></param> /// <param name="PageIndex"></param> /// <returns></returns> public DataTable GetAsDataTable(int PageSize, int PageIndex, string whereSql) { string errMsg = string.Empty; DataTable dt = null; sql.Length = 0; try { if (PageIndex == 1) { sql.AppendFormat(" SELECT top {0} ", PageSize); //sb.Append("ID,ResultType,CheckNo,SampleCode,CheckPlaceCode,CheckPlace,FoodName,TakeDate,CheckStartDate,Standard,CheckMachine,CheckMachineModel,MachineCompany,CheckTotalItem,CheckValueInfo,StandValue,Result,ResultInfo,CheckUnitName,CheckUnitInfo,Organizer,UpLoader,ReportDeliTime,IsReconsider,ReconsiderTime,ProceResults,CheckedCompanyCode,CheckedCompany,CheckedComDis,CheckPlanCode,DateManufacture,CheckMethod,APRACategory,Hole,SamplingPlace,CheckType,IsUpload,IsShow FROM ttResultSecond "); sql.Append("* FROM ttResultSecond "); if (!whereSql.Equals(string.Empty)) { sql.Append(" WHERE "); sql.Append(whereSql); } } else { sql.AppendFormat(" SELECT top {0} ", PageSize); sql.Append("* FROM ttResultSecond "); sql.Append(" Where ID Not In(Select top "); sql.Append(((PageIndex - 1) * PageSize).ToString()); sql.Append(" ID From ttResultSecond Order By ID Desc)"); if (!whereSql.Equals(string.Empty)) { sql.Append(" And "); sql.Append(whereSql); } } sql.Append(" Order by ID Desc "); string[] cmd = new string[1] { sql.ToString() }; string[] names = new string[1] { "Result" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sql.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
/// <summary> /// 插入一条报表记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public DataTable Insert(clsReport model, out string errMsg) { DataTable dt = null; errMsg = string.Empty; try { sql.Length = 0; sql.Append("Insert Into tReport (CheckUnitName,Trademark,Specifications,ProductionDate,QualityGrade,CheckedCompanyName,CheckedCompanyPhone,ProductionUnitsName,ProductionUnitsPhone,TaskSource,Standard,SamplingData,SampleNum,SamplingCode,SampleArrivalData,Notes,IsDeleted,CreateData) "); sql.Append("VALUES("); sql.AppendFormat("'{0}',", model.CheckUnitName); sql.AppendFormat("'{0}',", model.Trademark); sql.AppendFormat("'{0}',", model.Specifications); sql.AppendFormat("'{0}',", model.ProductionDate); sql.AppendFormat("'{0}',", model.QualityGrade); sql.AppendFormat("'{0}',", model.CheckedCompanyName); sql.AppendFormat("'{0}',", model.CheckedCompanyPhone); sql.AppendFormat("'{0}',", model.ProductionUnitsName); sql.AppendFormat("'{0}',", model.ProductionUnitsPhone); sql.AppendFormat("'{0}',", model.TaskSource); sql.AppendFormat("'{0}',", model.Standard); sql.AppendFormat("'{0}',", model.SamplingData); sql.AppendFormat("'{0}',", model.SampleNum); sql.AppendFormat("'{0}',", model.SamplingCode); sql.AppendFormat("'{0}',", model.SampleArrivalData); sql.AppendFormat("'{0}',", model.Notes); sql.AppendFormat("'{0}',", model.IsDeleted); sql.AppendFormat("'{0}'", model.CreateData); sql.Append(")"); DataBase.ExecuteCommand(sql.ToString(), out errMsg); sql.Length = 0; sql.Append("Select Top 1 * From tReport Order By ID Desc"); string[] cmd = new string[1] { sql.ToString() }; string[] names = new string[1] { "Result" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Result"]; sql.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }
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">查询条件串,不含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); }
/// <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="SampleName">样品名称</param> /// <param name="Name">项目名称</param> /// <param name="IsPreciseQuery">true 精确查找 | false 模糊查找</param> /// <returns></returns> public DataTable GetSampleByNameOrCode(string SampleName, string Name, bool IsPreciseQuery, bool IsFirst, int type) { string errMsg = string.Empty; DataTable dtbl = null; try { sb.Length = 0; if (type == 1) { sb.Append("Select ID,FtypeNmae,SampleNum,Name,ItemDes,StandardValue,Demarcate,Unit From ttStandDecide "); if (!SampleName.Equals(string.Empty) && !Name.Equals(string.Empty)) { sb.AppendFormat(" WHERE FtypeNmae Like '%{0}%'", SampleName); sb.AppendFormat(IsPreciseQuery ? " AND Name Like '{0}'" : " AND Name Like '%{0}%'", Name); } else if (SampleName.Equals(string.Empty) && !Name.Equals(string.Empty)) { sb.AppendFormat(IsPreciseQuery ? " WHERE Name Like '{0}'" : " WHERE Name Like '%{0}%'", Name); } else if (!SampleName.Equals(string.Empty) && Name.Equals(string.Empty)) { sb.AppendFormat(" WHERE FtypeNmae Like '%{0}%'", SampleName); } } else if (type == 2) { sb.AppendFormat("Select Top 1 ID,FtypeNmae,SampleNum,Name,ItemDes,StandardValue,Demarcate,Unit,UDate From ttStandDecide Where SampleNum Like'{0}'", SampleName); } string[] cmd = new string[1] { sb.ToString() }; string[] names = new string[1] { "tStandDecide" }; dtbl = DataBase.GetDataSet(cmd, names, out errMsg).Tables["tStandDecide"]; sb.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dtbl); }
/// <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); }
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); }
/// <summary> /// 查询报表 /// </summary> /// <param name="whereSql"></param> /// <param name="type">1 原始报表,2 甘肃报表</param> /// <param name="errMsg"></param> /// <returns></returns> public DataTable GetReport(string whereSql, int type, out string errMsg) { string strTb = errMsg = string.Empty; DataTable dt = null; sql.Length = 0; try { if (type == 1) { strTb = "tReport"; } else if (type == 2) { strTb = "tReportGS"; } sql.AppendFormat("Select * From {0} ", strTb); if (!whereSql.Equals(string.Empty)) { sql.Append(" WHERE "); sql.Append(whereSql); } sql.Append(" Order By ID Desc"); string[] cmd = new string[1] { sql.ToString() }; string[] names = new string[1] { "Report" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["Report"]; sql.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) { 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 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 CREDITLEVEL FROM TCREDITLEVEL"); 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] { "CreditLevel" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["CreditLevel"]; sb.Length = 0; } catch (Exception e) { //Log.WriteLog("查询clsCreditLevel",e); errMsg = e.Message; } return(dt); }
/// <summary> /// 查询报表子表 甘肃报表 /// </summary> /// <param name="whereSql"></param> /// <param name="orderBySql"></param> /// <returns></returns> public DataTable GetReportDetailGS(int ID) { string errMsg = string.Empty; DataTable dt = null; sql.Length = 0; try { sql.AppendFormat("Select * From tReportGSDetail Where ReportGSID Like '{0}'", ID.ToString()); string[] cmd = new string[1] { sql.ToString() }; string[] names = new string[1] { "ReportDetailGS" }; dt = DataBase.GetDataSet(cmd, names, out errMsg).Tables["ReportDetailGS"]; sql.Length = 0; } catch (Exception e) { errMsg = e.Message; } return(dt); }