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);
}