/// <summary> /// 得到连续的序列号 /// </summary> /// <param name="TableName"></param> /// <param name="FieldName"></param> /// <param name="sID"></param> /// <returns></returns> public static string GetNewSerialNumberContinuous(string TableName, string FieldName) { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); string sSQL = "select * from _SerialNumberContinuous where TableID='" + TableName + "' and Code='" + FieldName + "'"; DataTable dts = clsSQLCommond.ExecQuery(sSQL); string left = ""; string middle = ""; string right = ""; if (dts.Rows[0]["LeftType"].ToString() != "") { left = dts.Rows[0]["LeftType"].ToString(); } if (dts.Rows[0]["MiddleType"].ToString() == "DateTime") { middle = DateTime.Now.ToString("yyMM"); } sSQL = "select isnull(max(" + FieldName + "),0) from " + TableName; DataTable dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows[0][0].ToString() != "0") { right = dt.Rows[0][0].ToString(); right = right.Substring(left.Length + middle.Length, dts.Rows[0]["RightType"].ToString().Length); int iright = int.Parse(right) + 1; right = GetIsEnoughNumber(iright, dts.Rows[0]["RightType"].ToString().Length); } else { right = GetIsEnoughNumber(1, dts.Rows[0]["RightType"].ToString().Length); } return(left + middle + right); }
public static decimal PriceAdjust(string cInvCode, string cCusCode) { string sSQL = ""; 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); sSQL = @"select b.D1 from PriceAdjust_Details b left join PriceAdjust_Main a on a.ID=b.ID left join Customer c on b.S2=c.S4 where b.B1=1 and b.S1='" + cInvCode + "' and c.cCusCode='" + cCusCode + "' and a.dVerifysysTime is not null order by a.dDate desc"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows.Count > 0) { return(规格化.ReturnDecimal(dt.Rows[0]["D1"])); } else { sSQL = @"select b.D1 from PriceAdjust_Details b left join PriceAdjust_Main a on a.ID=b.ID left join Customer c on b.S2=c.S4 where (b.B1<>1 or b.B1 is null ) and b.S1='" + cInvCode + "' and c.cCusCode='" + cCusCode + "' and a.dVerifysysTime is not null order by a.dDate desc"; DataTable dts = clsSQLCommond.ExecQuery(sSQL); if (dts.Rows.Count > 0) { return(规格化.ReturnDecimal(dts.Rows[0]["D1"])); } } return(0); }
private void GetInv() { try { string sSQL = "select cast(0 as bit) as ischk,* from @u8.Inventory a left join @u8.InventoryClass b on a.cInvCCode =b.cInvCCode where 1=1"; if (textInvNew.Text.Trim() != string.Empty) { sSQL = sSQL.Replace("1=1", "1=1 and (cInvCode like '%" + textInvNew.Text.Trim() + "%' or cInvName like '%" + textInvNew.Text.Trim() + "%') "); } if (treeView1.SelectedNode != null && treeView1.SelectedNode.Tag != "") { sSQL = sSQL.Replace("1=1", " 1=1 and b.cInvCCode like '%" + treeView1.SelectedNode.Tag + "%'"); } sSQL = sSQL + " order by cInvCode,a.cInvCCode"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); gridControl1.DataSource = dt; } catch (Exception ee) { MessageBox.Show("加载数据失败:" + ee.Message); } }
private void GetBtnGrid2(string sFrmNameID) { string sSQL = "select distinct f.vchrBtnID,f.vchrBtnText,f.intOrder,cast(case when isnull(f2.fchrFrmNameID,'0')='0' then 0 else 1 end as bit) as bChoose ,cast(isnull(f2.bEnable,0) as int) as bEnable,cast(isnull(f2.bBtnState,1) as bit) as bBtnState " + "from dbo._FormBtnInfo f left join _FormBtnInfo f2 on f.vchrBtnID = f2.vchrBtnID and f2.fchrFrmNameID = '" + sFrmNameID + "' " + "order by f.intOrder,f.vchrBtnID,f.vchrBtnText"; DataTable dt2 = clsSQL.ExecQuery(sSQL); gridControl1.DataSource = dt2; }
private void GetPer(string sPer) { string sSQL = "select * from WorkDepment order by FCode"; gridControl2.DataSource = clsSQL.ExecQuery(sSQL); sSQL = "select * from EmployeeInfo where FCode like '%" + sPer + "%' or FName like '%" + sPer + "%' order by FCode"; gridControl1.DataSource = clsSQL.ExecQuery(sSQL); }
private void GetAccInfo() { string sYear = Convert.ToDateTime(系统服务.ClsBaseDataInfo.sLogDate).ToString("yyyy"); string sSQL = "SELECT DISTINCT A.cAcc_Id,'[' + A.cAcc_Id + ']' + A.cAcc_Name as vchrText,'UFDATA_' +A.cAcc_Id + '_' + '" + sYear + "' as sUFDatabase " + "FROM UFSystem.dbo.UA_Account A,UFSystem.dbo.UA_period P " + "WHERE A.cAcc_Id=P.cAcc_Id AND (P.bIsDelete=0 OR P.bIsDelete IS NULL) " + "ORDER BY A.cAcc_Id,vchrText"; DataTable dt = clsSQL.ExecQuery(sSQL); gridControl2.DataSource = dt; }
private void GetVen() { string sSQL = "select * from @u8.Vendor where 1=1 "; if (txtVen.Text.Trim() != string.Empty) { sSQL = sSQL + " and (cVenCode like '%" + txtVen.Text.Trim() + "%' or cVenName like '%" + txtVen.Text.Trim() + "%') "; } sSQL = sSQL + " order by cVenCode,cVCCode"; DataTable dt = clsSQL.ExecQuery(sSQL); gridControl1.DataSource = dt; }
private void GetInv() { string sSQL = "select * from @u8.Inventory where 1=1 "; if (txtInv.Text.Trim() != string.Empty) { sSQL = sSQL + " and (cInvCode like '%" + txtInv.Text.Trim() + "%' or cInvName like '%" + txtInv.Text.Trim() + "%') "; } sSQL = sSQL + " order by cInvCode,cInvCCode"; DataTable dt = clsSQL.ExecQuery(sSQL); gridControl1.DataSource = dt; }
private void FrmTableCol_Load(object sender, EventArgs e) { try { SetConEnable(true); string sSQL = "Select Name FROM Master..SysDatabases order by Name "; DataTable dt = clsSQL.ExecQuery(sSQL); lookUpEditDataBase.Properties.DataSource = dt; lookUpEditDataBase.EditValue = 系统服务.ClsBaseDataInfo.sDataBaseName; GetlookUpEditDataBase(); GetlookUpEditSQLAdd(); GetlookUpEdibKey(); if (系统服务.ClsBaseDataInfo.sUid.ToLower() == "admin" || 系统服务.ClsBaseDataInfo.sUid.ToLower() == "system") { for (int i = 0; i < base.toolStripMenuBtn.Items.Count; i++) { if (base.toolStripMenuBtn.Items[i].Name.ToLower().Trim() == "save") { base.toolStripMenuBtn.Items[i].Enabled = true; } if (base.toolStripMenuBtn.Items[i].Name.ToLower().Trim() == "export") { base.toolStripMenuBtn.Items[i].Enabled = true; } } } else { for (int i = 0; i < base.toolStripMenuBtn.Items.Count; i++) { if (base.toolStripMenuBtn.Items[i].Name.ToLower().Trim() == "save") { base.toolStripMenuBtn.Items[i].Enabled = false; } if (base.toolStripMenuBtn.Items[i].Name.ToLower().Trim() == "export") { base.toolStripMenuBtn.Items[i].Enabled = false; } } } } catch (Exception ee) { MessageBox.Show("加载窗体失败!\n " + ee.Message); } }
/// <summary> /// 获得单据ID /// </summary> /// <param name="sType">单据类型 PuArrival,</param> public void GetID(string sType, out long iID, out long iIDDetail) { string sSQL = "select iFatherID,iChildID from UFSystem..UA_Identity with (nolock) where cAcc_Id = '" + 系统服务.ClsBaseDataInfo.sUFDataBaseName.Substring(7, 3) + "' and cVouchType = '" + sType + "'"; DataTable dt = clsSQL.ExecQuery(sSQL); if (dt == null || dt.Rows.Count < 1) { iID = 0; iIDDetail = 0; } else { iID = Convert.ToInt64(dt.Rows[0]["iFatherID"]); iIDDetail = Convert.ToInt64(dt.Rows[0]["iChildID"]); } }
/// <summary> /// 判断编码原则 /// </summary> /// <param name="TableID"></param> /// <param name="Code"></param> /// <param name="oldID"></param> /// <param name="thisID"></param> /// <returns></returns> public static string CheckSerialNumber(string TableID, string oldID, string thisID) { if (oldID == thisID.Substring(0, oldID.Length)) { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); string sSQL = "select * from SerialNumber where TableID='" + TableID + "'"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); int t = 0; string[] type = dt.Rows[0]["Type"].ToString().Split('-'); for (int i = 0; i < type.Length; i++) { if (t < oldID.Length) { t = t + type[i].Length; } else { if (t + type[i].Length != thisID.Length) { return("不符合编码原则,编码原则" + dt.Rows[0]["Type"].ToString()); } else { return(""); } } } } else { return("不符合编码原则,子类别编码左侧必须等于类别编码"); } return(""); }
private void GetInvClass() { try { string sSQL = "select cInvCCode,cInvCName,iInvCGrade,bInvCEnd from @u8.InventoryClass order by cInvCCode"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); TreeNode tn; TreeNode tn2; for (int i = 0; i < dt.Rows.Count; i++) { if (Convert.ToInt32(dt.Rows[i]["iInvCGrade"]) == 1) { tn = new TreeNode(); tn.Tag = dt.Rows[i]["cInvCCode"].ToString().Trim(); tn.Text = dt.Rows[i]["cInvCCode"].ToString().Trim() + " " + dt.Rows[i]["cInvCName"].ToString().Trim(); treeView1.Nodes.Add(tn); GetLev(tn, dt, 1); } } } catch (Exception ee) { throw new Exception("获得存货分类失败! " + ee.Message); } }
private void GetGrid() { if (供应商.Trim() == "") { return; } string sSQL = @" select '' as iChk,a.*,isnull(b.iDelQty,0) as iDelQty,i.cInvName ,a.id as RegistersID from Registers a left join (select rid,SUM(iDelQty) as iDelQty from ShipmentPlans group by rid) b on a.ID=b.rID left join @u8.Inventory i on a.InvCode=i.cInvCode where 1=1 and isnull(a.iSheQty,0)-isnull(b.iDelQty,0)>0 "; if (lookUpEdit供应商.Text.Trim() != "") { sSQL = sSQL.Replace("1=1", "1=1 and VenCode like '%" + lookUpEdit供应商.EditValue.ToString().Trim() + "%' "); } if (lookUpEdit定单编号.Text.Trim() != "") { sSQL = sSQL.Replace("1=1", "1=1 and OrderNo like '%" + lookUpEdit定单编号.EditValue.ToString().Trim() + "%' "); } if (dateEdit纳入指示日1.EditValue != null && dateEdit纳入指示日1.EditValue.ToString().Trim() != "") { sSQL = sSQL + " and a.iSheDate >= '" + dateEdit纳入指示日1.DateTime.ToString("yyyy-MM-dd").Trim() + "'"; } if (dateEdit纳入指示日2.EditValue != null && dateEdit纳入指示日2.EditValue.ToString().Trim() != "") { sSQL = sSQL + " and a.iSheDate<'" + dateEdit纳入指示日2.DateTime.AddDays(1).ToString("yyyy-MM-dd").Trim() + "'"; } if (dthas.Rows.Count != 0) { string s = ""; for (int i = 0; i < dthas.Rows.Count; i++) { if (dthas.Rows[i].RowState == DataRowState.Deleted) { continue; } if (dthas.Rows[i]["rID"].ToString() != "") { if (s != "") { s = s + ","; } s = s + dthas.Rows[i]["rID"].ToString(); } } if (s.Trim() != "") { sSQL = sSQL + " and ID not in (" + s + ")"; } } sSQL = sSQL + " order by ID"; dt = clsSQLCommond.ExecQuery(sSQL); gridControl1.DataSource = dt; }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "select vchrRoleID,vchrRoleText,vchrRemark,' ' as bChoosed from dbo._RoleInfo " + "where isnull(bClosed,0) <>1 " + "order by vchrRoleID"; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得角色信息失败!"); } return(dt); }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrUid, vchrRemark, dtmCreate, dtmClose, ' ' AS bChoosed " + "FROM _UserInfo " + "where dtmCreate <= getdate() and dtmClose >= getdate() " + "order by vchrUid"; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得角色信息失败!"); } return(dt); }
public static void Get(string SoAutoID, System.Collections.ArrayList aList) { string sSQL = ""; 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); sSQL = @"select 2 as iType,a.cSOCode as cTypeCode,a.dDate,a.cCusCode,a.cPersonCode,a.cDepCode,a.ECode,a.cPayCode,a.iMoney,sum(c.iReMoney) as iReMoney,AutoID ,订单利润,订单金额 ,最后收款时间,利润分成,a.ID from (select a.ID,a.cSOCode,a.dDate,a.cCusCode,a.cPersonCode,a.cDepCode,a.ECode,a.cPayCode,sum(b.iMoney+isnull(r.iMoney,0)-isnull(h.iMoney,0)) as iMoney,a.cSTCode,b.AutoID,a.D3 订单利润,b.iMoney as 订单金额,a.D2 as 利润分成 from dbo.SO_SOMain a inner join dbo.SO_SODetails b on a.id = b.id left join (select SoAutoID,sum(isnull(iQuantity,0)) as iQuantity,sum(isnull(iNum,0)) as iNum ,sum(isnull(iNatMoney,0)) as iNatMoney ,sum(isnull(iMoney,0)) as iMoney from SO_SOReturns group by SoAutoID) r on b.AutoID=r.SoAutoID left join (select SoAutoID,sum(isnull(iMoney,0)) as iMoney from SaleVerifications group by SoAutoID) h on b.AutoID=h.SoAutoID where isnull(a.dVerifysysPerson,'') <> '' and isnull(a.dClosesysPerson,'') = '' group by a.ID,b.AutoID,a.cSOCode,a.dDate,a.cCusCode,a.cPersonCode,a.cDepCode,a.ECode,a.cPayCode,a.cSTCode,a.D3,b.iMoney,a.D2) a left join (select sum(isnull(iMoneyNow,0)) as iReMoney,max(dDate) as 最后收款时间,cCusCode,cTypeCode from SO_CloseBill a inner join SO_CloseBillDetails b on a.id = b.id group by cCusCode,cTypeCode) c on c.cCusCode = a.cCusCode and c.cTypeCode = a.cSOCode where 1=1 and a.AutoID=" + SoAutoID + " group by a.cSOCode,a.dDate,a.cCusCode,a.cPersonCode,a.cDepCode,a.ECode,a.cPayCode,a.iMoney ,a.AutoID,订单利润,订单金额,最后收款时间,利润分成,a.ID having sum(isnull(a.iMoney,0)) <= sum(isnull(c.iReMoney,0))"; DataTable dtqty = clsSQLCommond.ExecQuery(sSQL); if (dtqty.Rows.Count > 0) { sSQL = @"select a.cSOCode,a.cDepCode,cPersonCode,a.cSTCode, (case when a.cSTCode='01' then 0.7 else 1 end)* ( b.iMoney-isnull(b.iQuantity*Cost,0)- (case when a.cSTCode='02' then 0.9 else 1 end)*ISNULL(e.iMoney,0)--子件金额 -isnull(f.iMoney*a.D5,0)--业务费用 )-isnull(r.F5,0) as iMoney from SO_SOMain a left join SO_SODetails b on a.ID=b.ID --left join (select SoAutoID,sum(-b.iQuantity) as iQuantity from SO_SOReturn a left join SO_SOReturns b on a.ID=b.ID where a.dVerifysysTime is not null group by SoAutoID) c on b.AutoID=c.SoAutoID left join (select sum(iQuantity) as iQuantity,sum(isnull(iMoney,0)) as iMoney,sum(case when B3=1 then -F5 when B3=1 then F5 end) as F5,SoAutoID from RdRecord a inner join RdRecords b on a.id = b.id left join RdStyle r on a.cRSCode=r.cRSCode where 收发标志=0 group by SoAutoID) r on r.SoAutoID = b.AutoID left join (select a.AutoID,sum(a.D1*a.iQuantity) as iMoney from SO_SOSublist a left join Inventory b on a.cInvCode=b.cInvCode where b.cInvClassCode<>'03' group by a.AutoID) e on b.AutoID=e.AutoID left join (select ID,sum((DD1*DD2)) as iMoney from SO_SOMainCommissiion group by ID) f on f.ID=a.ID left join Inventory i on b.cInvCode=i.cInvCode WHERE b.AutoID='" + SoAutoID + "'"; DataTable dt2 = clsSQLCommond.ExecQuery(sSQL); decimal 订单利润 = 系统服务.规格化.ReturnDecimal(dt2.Rows[0]["iMoney"]); decimal 订单金额 = 系统服务.规格化.ReturnDecimal(dtqty.Rows[0]["订单金额"]); decimal 收款金额 = 系统服务.规格化.ReturnDecimal(dtqty.Rows[0]["iMoney"]); decimal 利润分成 = 系统服务.规格化.ReturnDecimal(dtqty.Rows[0]["利润分成"]); string 最后收款时间 = dtqty.Rows[0]["最后收款时间"].ToString().Trim(); decimal 个人利润 = 系统服务.规格化.ReturnDecimal(订单利润 * 利润分成); string id = dtqty.Rows[0]["ID"].ToString().Trim(); sSQL = "update SO_SOMain set D4='" + 订单利润 + "',Date1='" + 最后收款时间 + "',D6='" + 个人利润 + "' where ID='" + id + "'"; aList.Add(sSQL); } }
/// <summary> /// 获得帐套数据, /// </summary> /// <param name="sSQL"></param> private void GetAccInfo() { try { string sSQL = "SELECT DISTINCT A.cAcc_Id,'[' + A.cAcc_Id + ']' + A.cAcc_Name as vchrText " + "FROM UFSystem.dbo.UA_Account A,UFSystem.dbo.UA_period P " + "WHERE A.cAcc_Id=P.cAcc_Id AND (P.bIsDelete=0 OR P.bIsDelete IS NULL) " + "ORDER BY A.cAcc_Id,vchrText"; DataTable dt = clsSQLCommond.ExecQuery(sSQL); //DataTable dt = clsWeb.dtGetAccInfo(); lookUpAcc.Properties.DataSource = dt; lookUpAcc.EditValue = GetConfigValue(sPathConfig, "AccountInfo"); } catch { throw new Exception("获得帐套信息失败!"); } }
/// <summary> /// 供应商价格表 /// </summary> /// <param name="isupplytype">供应类型:采购 1;委外 2</param> /// <returns></returns> public DataTable GetPrice(int isupplytype) { //string sSQL = "select a.*,b.iunitprice as iUnitPrice,b.itaxunitprice as iTaxPrice,b.itaxrate from " + // "(select max(dstartdate) as dstartdate,cvencode,cvenname,cInvcode,cinvname,cinvstd,ccomunitcode,ccomunitname,cexch_name from @u8.pu_veninvpricelst " + // "where isupplytype = " + isupplytype + " and '" + 系统服务.ClsBaseDataInfo.sLogDate + "' < isnull(ddisabledate,'2099-12-31') " + // "group by cvencode,cvenname,cInvcode,cinvname,cinvstd,ccomunitcode,ccomunitname,cexch_name " + // ") a " + // "inner join @u8.pu_veninvpricelst b on a.cInvCode=b.cInvCode and a.cvencode=b.cvencode and a.dstartdate = b.dstartdate " + // "where b.isupplytype = " + isupplytype + " " + // "order by a.cvencode,a.cInvcode"; string sSQL = " select *,v.iunitprice as iUnitPrice,v.itaxunitprice as iTaxPrice from " + "( " + "select max(Autoid) as Autoid from @u8.ven_inv_price group by cvencode,cInvCode " + ") vT left join @u8.ven_inv_price v on v.Autoid = vT.autoid " + " left join @u8.vendor vd on vd.cVenCode = v.cVenCode " + "where v.iSupplyType = '" + isupplytype + "' " + "order by v.iunitprice,v.Autoid"; return(clsSQL.ExecQuery(sSQL)); }
public DataTable GetSerHostName() { try { string sSQL = "select host_name() as sHostName,@@SERVERname as sSerName "; return(clsSQLCommond.ExecQuery(sSQL)); } catch { throw new Exception("获得服务器名称失败!"); } }
private void SetTree() { try { string sSQL2 = @"select count(*) from dbo._UserRoleInfo where vchrRoleID = 'administrator' and vchrUserID = '" + 系统服务.ClsBaseDataInfo.sUid + "'"; string sSQL; if (系统服务.ClsBaseDataInfo.sUid == "admin" || 系统服务.ClsBaseDataInfo.sUid == "system") { sSQL = "SELECT TOP 100 PERCENT * FROM dbo._Form WHERE (1 = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='" + 系统服务.ClsBaseDataInfo.sProForm + "' or vchrFormBel is null) ORDER BY fIntOrderID"; } else if (Convert.ToInt32(clsSQLCommond.ExecGetScalar(sSQL2)) != 0) { sSQL = "SELECT TOP 100 PERCENT * FROM dbo._Form WHERE (isnull(bUse,0) = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='" + 系统服务.ClsBaseDataInfo.sProForm + "' or vchrFormBel is null) ORDER BY fIntOrderID"; } else { sSQL = "SELECT DISTINCT " + " dbo._Form.fchrFrmNameID, dbo._Form.fchrFrmText, dbo._Form.fchrNameSpace, " + " dbo._Form.fchrFrmUpName, "+ " dbo._Form.fbitHide, dbo._Form.fbitNoUse, dbo._Form.fIntOrderID " + "FROM dbo._RoleInfo INNER JOIN " + " dbo._RoleRight ON dbo._RoleInfo.vchrRoleID = dbo._RoleRight.vchrRoleID INNER JOIN " + " dbo._UserRoleInfo ON dbo._RoleInfo.vchrRoleID = dbo._UserRoleInfo.vchrRoleID and dbo._UserRoleInfo.vchrUserID='" + 系统服务.ClsBaseDataInfo.sUid + "' INNER JOIN " + " dbo._Form ON 1=1 " + " AND dbo._Form.fchrFrmNameID +'/'+dbo._Form.fchrFrmText= RTRIM(LTRIM(RIGHT(dbo._RoleRight.vchrRoleRight, LEN(dbo._RoleRight.vchrRoleRight) - CHARINDEX('|', dbo._RoleRight.vchrRoleRight)))) "+ "WHERE (isnull(bUse,0) = 1) AND (fbitNoUse = 0) AND (fbitHide = 0) and (vchrFormBel='" + 系统服务.ClsBaseDataInfo.sProForm + "' or vchrFormBel is null) " + "ORDER BY fIntOrderID "; } clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); dt = clsSQLCommond.ExecQuery(sSQL); //ClsUseWebService clsWeb = new ClsUseWebService(); //dt = clsWeb.dtMainSetTree(系统服务.ClsBaseDataInfo.sUid, 系统服务.ClsBaseDataInfo.sProForm); if (iBtnType == 0) { InitTree(treView.Nodes, "业务工作"); } if (iBtnType == 1) { InitTree(treView.Nodes, "基础设置"); } if (iBtnType == 2) { InitTree(treView.Nodes, "系统服务"); } } catch (Exception ee) { throw new Exception(ee.Message); } }
private void GetGrid() { string sSQL = "select vchrCode,vchrName,vchrRemark,bClose,1 as bSave from dbo.QCBase1 where accid = '" + 系统服务.ClsBaseDataInfo.sUFDataBaseName.Substring(7, 3) + "' and accyear = '" + 系统服务.ClsBaseDataInfo.sUFDataBaseName.Substring(11, 4) + "' "; if (!chkClose.Checked) { sSQL = sSQL + " and isnull(bClose,0) = 0 order by vchrCode"; } gridControl1.DataSource = clsSQL.ExecQuery(sSQL); gridView1.AddNewRow(); gridControl1.Select(); gridView1.SelectRow(gridView1.RowCount - 1); gridView1.FocusedColumn = gridColumn2; }
/// <summary> /// 获得存货档案 /// </summary> private void GetInvGrid() { int iFocRow = gridView1.FocusedRowHandle; string sSQL = "select cast(0 as bit) as 选择,cInvCode as iID,cInvCode as 存货编码,cInvName as 存货名称,cInvAddCode as 存货代码,cInvStd as 规格型号 from @u8.Inventory where 1=1 order by cInvCode"; if (treeList1.FocusedNode != null && treeList1.FocusedNode.Tag.ToString().Trim() != "") { sSQL = sSQL.Replace("1=1", "1=1 and (cInvClassCode like '" + treeList1.FocusedNode.Tag.ToString().Trim() + "%' or cInvClassCode='" + treeList1.FocusedNode.Tag.ToString().Trim() + "')"); } if (textEdit1.EditValue != null && textEdit1.EditValue != "") { sSQL = sSQL.Replace("1=1", "1=1 and (cInvCode like '%" + textEdit1.EditValue.ToString().Trim() + "%' or cInvName like '%" + textEdit1.EditValue.ToString().Trim() + "%')"); } gridControl1.DataSource = clsSQLCommond.ExecQuery(sSQL); for (int i = 0; i < gridView1.Columns.Count; i++) { if (gridView1.Columns[i].FieldName == "选择") { gridView1.Columns[i].OptionsColumn.AllowEdit = true; } else { gridView1.Columns[i].OptionsColumn.AllowEdit = false; } if (gridView1.Columns[i].FieldName == "iID") { gridView1.Columns[i].Visible = false; } else { gridView1.Columns[i].Visible = true; } } }
public bool bchk时间锁() { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); DateTime d时间锁 = Convert.ToDateTime("2016-1-1"); string sSQL = "select getdate()"; DateTime d当前日期 = Convert.ToDateTime(clsSQLCommond.ExecQuery(sSQL).Rows[0][0]); if (d当前日期 > d时间锁) { return(false); } else { return(true); } }
private bool Login(out string sEr) { bool b = false; sEr = ""; try { string sSQL = ""; if (txtUID.Text.ToLower().Trim() != "admin" || txtUID.Text.ToLower().Trim() != "system") { sSQL = "select vchrUid, vchrPwd, vchrRemark, tstamp, dtmCreate, dtmClose from _UserInfo " + "where vchrUid = '" + txtUID.Text.Trim() + "' and vchrPwd = '" + clsDES.Encrypt(txtPWD.Text.Trim()) + "'"; } else { sSQL = "select vchrUid, vchrPwd, vchrRemark, tstamp, dtmCreate, dtmClose from _UserInfo " + "where vchrUid = '" + txtUID.Text.Trim() + "' and (vchrPwd = '" + (txtPWD.Text.Trim()) + "' or vchrPwd = '" + clsDES.Encrypt(txtPWD.Text.Trim()) + "')"; } DataTable dt = clsSQLCommond.ExecQuery(sSQL); int iCou = dt.Rows.Count; if (iCou == 1) { if (DateTime.Parse(Convert.ToDateTime(dt.Rows[0]["dtmClose"]).ToString("yyyy-MM-dd")) < DateTime.Today.AddDays(+1)) { sEr = "帐号已被停用!"; } else { sSQL = "insert into Define1(S1,s6,SysCreateDate)values('" + txtUID.Text.Trim() + "','系统登录',getdate())"; clsSQLCommond.ExecSql(sSQL); b = true; } } else { sEr = "帐号或者密码错误!"; } } catch (Exception ee) { sEr = ee.Message; b = false; } return(b); }
/// <summary> /// 存货档案 /// </summary> /// <param name="par">存货档案编码</param> /// <returns></returns> public static DataTable Inventory(string par) { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); string sSQL = @"select cInvCode,cInvName,cInvAddCode,cInvStd from @u8.Inventory where 1=1 "; if (par != "") { sSQL = sSQL + " and cInvCode='" + par + "'"; } sSQL = sSQL + " order by cInvCode "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); DataRow dr = dt.NewRow(); dt.Rows.InsertAt(dr, 0); return(dt); }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrUid, vchrPwd,vchrName, vchrRemark, tstamp, dtmCreate, dtmClose " + " FROM _UserInfo " + " order by vchrUid "; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得用户信息失败!"); } return(dt); }
public DataTable GetAllDt() { DataTable dt = null; try { string sSQL = "SELECT vchrRoleID, vchrRoleText, vchrRemark, bClosed, dtmCreate " + "FROM _RoleInfo " + "ORDER BY vchrRoleID "; dt = clsSQLCommond.ExecQuery(sSQL); } catch { throw new Exception("获得用户信息失败!"); } return(dt); }
/// <summary> /// 门号 /// </summary> /// <param name="par">门号</param> /// <returns></returns> public static DataTable Door(string par) { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); string sSQL = @"select DISTINCT iDoor from Gate where 1=1 and isnull(iDoor,'') <> '' "; if (par != "") { sSQL = sSQL + " and iDoor='" + par + "'"; } sSQL = sSQL + " order by iDoor "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); DataRow dr = dt.NewRow(); dt.Rows.InsertAt(dr, 0); return(dt); }
/// <summary> /// 供应商 /// </summary> /// <param name="par">供应商</param> /// <returns></returns> public static DataTable Vendor(string par) { 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); string sSQL = @"select DISTINCT VenCode from Registers where 1=1 "; if (par != "") { sSQL = sSQL + " and VenCode='" + par + "'"; } sSQL = sSQL + " order by VenCode "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); DataRow dr = dt.NewRow(); dt.Rows.InsertAt(dr, 0); return(dt); }
public void GetPeriod(string p, out string sdate, out string edate) { string sSQL = ""; 系统服务.ClsDataBase clsSQLCommond = 系统服务.ClsDataBaseFactory.Instance(); sSQL = @"select * from UfSystem.dbo.UA_Period where iYear='" + 系统服务.ClsBaseDataInfo.sUFDataBaseYear + "' and iId='" + p + "' "; DataTable dt = clsSQLCommond.ExecQuery(sSQL); if (dt.Rows.Count > 0) { sdate = DateTime.Parse(dt.Rows[0]["dBegin"].ToString()).ToString("yyyy-MM-dd"); edate = DateTime.Parse(dt.Rows[0]["dEnd"].ToString()).ToString("yyyy-MM-dd"); } else { sdate = ""; edate = ""; } }