/// <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");
}
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);
}
/// <summary>
/// 插入一条明细记录
/// </summary>
/// <param name="model">ID>0为修改 ID=0为新增</param>
/// <returns></returns>
public int Insert(clsCompany model, out string errMsg)
{
errMsg = string.Empty;
int rtn = 0;
try
{
sb.Length = 0;
if (model.ID > 0)
{
//修改
sb.AppendFormat("Update tCompany Set SysCode = '{0}',StdCode = '{1}',CAllow = '{2}',", model.SysCode, model.StdCode, model.CAllow);
sb.AppendFormat("ISSUEAGENCY = '{0}',ISSUEDATE = '{1}',PERIODSTART = '{2}',PERIODEND = '{3}',", model.ISSUEAGENCY, model.ISSUEDATE, model.PERIODSTART, model.PERIODEND);
sb.AppendFormat("VIOLATENUM = '{0}',LONGITUDE = '{1}',LATITUDE = '{2}',SCOPE = '{3}',", model.VIOLATENUM, model.LONGITUDE, model.LATITUDE, model.SCOPE);
sb.AppendFormat("PUNISH = '{0}',CompanyID = '{1}',OtherCodeInfo = '{2}',FullName = '{3}',", model.PUNISH, model.CompanyID, model.OtherCodeInfo, model.FullName);
sb.AppendFormat("ShortName = '{0}',DisplayName = '{1}',ShortCut = '{2}',Property = '{3}',", model.ShortName, model.DisplayName, model.ShortCut, model.Property);
sb.AppendFormat("KindCode = '{0}',RegCapital = '{1}',Unit = '{2}',Incorporator = '{3}',", model.KindCode, model.RegCapital, model.Unit, model.Incorporator);
//sb.AppendFormat("RegDate = '{0}',DistrictCode = '{1}',Address = '{2}',PostCode = '{3}',", model.RegDate, model.DistrictCode, model.Address, model.PostCode);
sb.AppendFormat("DistrictCode = '{0}',Address = '{1}',PostCode = '{2}',", model.DistrictCode, model.Address, model.PostCode);
sb.AppendFormat("LinkMan = '{0}',LinkInfo = '{1}',CreditLevel = '{2}',CreditRecord = '{3}',", model.LinkMan, model.LinkInfo, model.CreditLevel, model.CreditRecord);
sb.AppendFormat("ProductInfo = '{0}',OtherInfo = '{1}',CheckLevel = '{2}',FoodSafeRecord = '{3}',", model.ProductInfo, model.OtherInfo, model.CheckLevel, model.FoodSafeRecord);
sb.AppendFormat("IsReadOnly = {0},IsLock = {1},Remark = '{2}',ComProperty = '{3}',", model.IsReadOnly, model.IsLock, model.Remark, model.ComProperty);
sb.AppendFormat("TSign = '{0}' Where ID = {1}", model.TSign, model.ID);
}
else
{
//新增
sb.Append("INSERT INTO tCompany");
sb.Append("(SysCode,StdCode,CAllow,ISSUEAGENCY,ISSUEDATE,PERIODSTART,PERIODEND,VIOLATENUM,LONGITUDE,LATITUDE,SCOPE,PUNISH,CompanyID,OtherCodeInfo,FullName,");
sb.Append("ShortName,DisplayName,ShortCut,Property,KindCode,RegCapital,Unit,Incorporator,");
if (model.RegDate != null)
{
sb.Append("RegDate,");
}
sb.Append("DistrictCode,PostCode,Address,LinkMan,LinkInfo,");
sb.Append("CreditLevel,CreditRecord,ProductInfo,OtherInfo,CheckLevel,FoodSafeRecord,IsReadOnly,IsLock,Remark,ComProperty,TSign)");
sb.Append(" VALUES(");
sb.AppendFormat("'{0}',", model.SysCode);
sb.AppendFormat("'{0}',", model.StdCode);
sb.AppendFormat("'{0}',", model.CAllow);
sb.AppendFormat("'{0}',", model.ISSUEAGENCY);
sb.AppendFormat("'{0}',", model.ISSUEDATE);
sb.AppendFormat("'{0}',", model.PERIODSTART);
sb.AppendFormat("'{0}',", model.PERIODEND);
sb.AppendFormat("'{0}',", model.VIOLATENUM);
sb.AppendFormat("'{0}',", model.LONGITUDE);
sb.AppendFormat("'{0}',", model.LATITUDE);
sb.AppendFormat("'{0}',", model.SCOPE);
sb.AppendFormat("'{0}',", model.PUNISH);
sb.AppendFormat("'{0}',", model.CompanyID);
sb.AppendFormat("'{0}',", model.OtherCodeInfo);
sb.AppendFormat("'{0}',", model.FullName);
sb.AppendFormat("'{0}',", model.ShortName);
sb.AppendFormat("'{0}',", model.DisplayName);
sb.AppendFormat("'{0}',", model.ShortCut);
sb.AppendFormat("'{0}',", model.Property);
sb.AppendFormat("'{0}',", model.KindCode);
sb.AppendFormat("{0},", model.RegCapital);
sb.AppendFormat("'{0}',", model.Unit);
sb.AppendFormat("'{0}',", model.Incorporator);
if (model.RegDate != null)
{
sb.AppendFormat("'{0}',", model.RegDate);
}
sb.AppendFormat("'{0}',", model.DistrictCode);
sb.AppendFormat("'{0}',", model.PostCode);
sb.AppendFormat("'{0}',", model.Address);
sb.AppendFormat("'{0}',", model.LinkMan);
sb.AppendFormat("'{0}',", model.LinkInfo);
sb.AppendFormat("'{0}',", model.CreditLevel);
sb.AppendFormat("'{0}',", model.CreditRecord);
sb.AppendFormat("'{0}',", model.ProductInfo);
sb.AppendFormat("'{0}',", model.OtherInfo);
sb.AppendFormat("'{0}',", model.CheckLevel);
sb.AppendFormat("'{0}',", model.FoodSafeRecord);
sb.AppendFormat("{0},", model.IsReadOnly);
sb.AppendFormat("{0},", model.IsLock);
sb.AppendFormat("'{0}',", model.Remark);
sb.AppendFormat("'{0}',", model.ComProperty);
sb.AppendFormat("'{0}'", model.TSign);
sb.Append(")");
}
DataBase.ExecuteCommand(sb.ToString(), out errMsg);
sb.Length = 0;
rtn = 1;
}
catch (Exception e)
{
errMsg = e.Message;
}
return(rtn);
}
public static string[] ValueFromCode(string foodCode, string checkItemCode)
{
string[] rtn = new string[3];
rtn[0] = string.Empty;
rtn[1] = "0";
rtn[2] = string.Empty;
bool IsExist = false;
string errMsg = string.Empty;
if (foodCode.Equals(string.Empty) || checkItemCode.Equals(string.Empty))
{
return(rtn);
}
try
{
string sql = "SELECT CheckItemCodes FROM tfoodclass WHERE syscode='" + foodCode + "'";
Object o = DataBase.GetOneValue(sql, out errMsg);
if (o == null)
{
return(rtn);
}
else
{
string[,] result = new string [1, 2];// StringUtil.GetFoodAry(o.ToString());
if (result.GetLength(0) >= 1)
{
for (int i = 0; i < result.GetLength(0); i++)
{
if (result[i, 0].Equals(checkItemCode))
{
IsExist = true;
if (result[i, 1].Equals("-1"))
{
sql = "SELECT DemarcateInfo FROM tCheckItem WHERE SysCode='" + checkItemCode + "'";
o = DataBase.GetOneValue(sql, out errMsg);
if (o == null)
{
rtn[0] = string.Empty;
}
else
{
rtn[0] = o.ToString();
}
sql = "SELECT StandardValue FROM tCheckItem WHERE SysCode='" + checkItemCode + "'";
o = DataBase.GetOneValue(sql, out errMsg);
if (o == null)
{
rtn[1] = string.Empty;
}
else
{
rtn[1] = o.ToString();
}
sql = "SELECT Unit FROM tCheckItem WHERE SysCode='" + checkItemCode + "'";
o = DataBase.GetOneValue(sql, out errMsg);
if (o == null)
{
rtn[2] = string.Empty;
}
else
{
rtn[2] = o.ToString();
}
}
else
{
rtn[0] = result[i, 1];
rtn[1] = result[i, 2];
rtn[2] = result[i, 3];
}
break;
}
}
}
if (!IsExist)
{
rtn[0] = "-1";
rtn[1] = "0";
rtn[2] = "-1";
}
return(rtn);
}
}
catch (Exception e)
{
errMsg = e.Message;
return(rtn);
}
}
