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