📄 dataservice.asmx.cs
字号:
SqlTxt=SqlTxt+" Where ID='"+OldKey+"'";
}
//生成更新查询命令
return RunSql(SqlTxt);
//执行之
}
catch(Exception error)
{
return error.Message;
}
}
//数据查询
[ WebMethod(Description="Per session Hit Counter",EnableSession=true)]
public System.Xml.XmlDocument FindData()
{
System.Xml.XmlDocument ReXmlDoc=new System.Xml.XmlDocument();
try
{
string FName=this.Context.Request["FName"];
System.Xml.XmlDocument MyXmlDoc=new System.Xml.XmlDocument();
MyXmlDoc.Load(this.Context.Request.InputStream);
System.Xml.XmlNode SqlValue=MyXmlDoc.SelectSingleNode("Root");
string SqlTxt="";
SqlTxt=ReturnSqlTxt(FName,SqlValue);
return RunReader(SqlTxt);
}
catch(Exception error)
{
ReXmlDoc.LoadXml("<Root><Record><![CDATA[Error,"+error.Message+"]]></Record></Root>");
return ReXmlDoc;
//返回系统错误
}
finally
{
Mysjk.Close();
//关闭数据库
}
}
private string ReturnSqlTxt(string TableName,System.Xml.XmlNode SqlValue)
{
int Datas=SqlValue.ChildNodes.Count;
string SqlTxt="";
string SqlHTxt="";
string WhereTxt="";
for(int i=0;i<=Datas-1;i++)
{
System.Xml.XmlNode SqlWhere=SqlValue.ChildNodes[i];
if (SqlWhere.ChildNodes[2].InnerText!="")
{
WhereTxt=WhereTxt+" "+SqlWhere.ChildNodes[0].InnerText+" ";
//逻辑连接符
WhereTxt=WhereTxt+SqlWhere.ChildNodes[1].InnerText;
//左括号
if(SqlWhere.ChildNodes[3].InnerText=="not is")
{
WhereTxt=WhereTxt+"not(["+SqlWhere.ChildNodes[2].InnerText+"])";
}
else
{
WhereTxt=WhereTxt+"["+SqlWhere.ChildNodes[2].InnerText+"]";
}
//条件项
if (SqlWhere.ChildNodes[3].InnerText=="包含")
{
WhereTxt=WhereTxt+" Like ";
//运算符
WhereTxt=WhereTxt+"'%"+SqlWhere.ChildNodes[4].InnerText+"%'";
//条件值
}
else
{
if(SqlWhere.ChildNodes[3].InnerText=="不包含")
{
WhereTxt=WhereTxt+" Not Like ";
//运算符
WhereTxt=WhereTxt+"'%"+SqlWhere.ChildNodes[4].InnerText+"%'";
//条件值
}
else
{
if(SqlWhere.ChildNodes[3].InnerText=="is" || SqlWhere.ChildNodes[3].InnerText=="not is")
{
WhereTxt=WhereTxt+" is ";
//运算符
WhereTxt=WhereTxt+SqlWhere.ChildNodes[4].InnerText;
//条件值
}
else
{
WhereTxt=WhereTxt+SqlWhere.ChildNodes[3].InnerText;
//运算符
string Lname=SqlWhere.ChildNodes[2].InnerText;
string Ltype=GetListType(TableName,Lname);
//return "SELECT "+Ltype;
switch (Ltype)
{
case "adVarWChar":
WhereTxt=WhereTxt+"'"+SqlWhere.ChildNodes[4].InnerText+"'";
//字符串类型的数据
break;
case "adDouble"://数字
case "adSingle":
case "adInteger":
case "adCurrency":
WhereTxt=WhereTxt+SqlWhere.ChildNodes[4].InnerText;
//数值类型的数据
break;
case "adDBTimeStamp"://日期
string DT=SqlWhere.ChildNodes[4].InnerText;
WhereTxt=WhereTxt+"datevalue('"+DT+"')";
//日期时间类型的数据
break;
default:
WhereTxt=WhereTxt+"'"+SqlWhere.ChildNodes[4].InnerText+"'";
//其他类型的数据
break;
}
}
}
}
WhereTxt=WhereTxt+SqlWhere.ChildNodes[5].InnerText;
//右括号
}
}
SqlHTxt="SELECT * From "+TableName+" where ( ";
SqlTxt=SqlHTxt+WhereTxt+" )";
return SqlTxt;
}
//数据查询
[ WebMethod(Description="Per session Hit Counter",EnableSession=true)]
public System.Xml.XmlDocument FindDataE()
{
System.Xml.XmlDocument ReXmlDoc=new System.Xml.XmlDocument();
try
{
string FName=this.Context.Request["FName"];
string Fmd=this.Context.Request["Fmd"];
string FNyear=this.Context.Request["FNyear"];
System.Xml.XmlDocument MyXmlDoc=new System.Xml.XmlDocument();
MyXmlDoc.Load(this.Context.Request.InputStream);
System.Xml.XmlNode SqlValue=MyXmlDoc.SelectSingleNode("Root");
string SqlTxt=ReturnSqlTxtE(FName,SqlValue,Fmd,FNyear);
return RunReader(SqlTxt);
}
catch(Exception error)
{
ReXmlDoc.LoadXml("<Root><Record><![CDATA[Error,"+error.Message+"]]></Record></Root>");
return ReXmlDoc;
//返回系统错误
}
finally
{
Mysjk.Close();
//关闭数据库
}
}
private string ReturnSqlTxtE(string TableName,System.Xml.XmlNode SqlValue,string Fmd,string FNyear)
{
int Datas=SqlValue.ChildNodes.Count;
string SqlTxt="";
string SqlHTxt="";
string WhereTxt="";
for(int i=0;i<=Datas-1;i++)
{
System.Xml.XmlNode SqlWhere=SqlValue.ChildNodes[i];
if (SqlWhere.ChildNodes[2].InnerText!="")
{
WhereTxt=WhereTxt+" "+SqlWhere.ChildNodes[0].InnerText+" ";
//逻辑连接符
WhereTxt=WhereTxt+SqlWhere.ChildNodes[1].InnerText;
string Ln="";
if((SqlWhere.ChildNodes[2].InnerText=="班组" || SqlWhere.ChildNodes[2].InnerText=="工种") && (TableName=="PayDataSum" || TableName=="WorksSum") && FNyear=="T")
{
Ln="EBy."+SqlWhere.ChildNodes[2].InnerText;
}
else
{
Ln=SqlWhere.ChildNodes[2].InnerText;
}
//左括号
if(SqlWhere.ChildNodes[3].InnerText=="not is")
{
WhereTxt=WhereTxt+"not(["+Ln+"])";
}
else
{
WhereTxt=WhereTxt+"["+Ln+"]";
}
//条件项
if (SqlWhere.ChildNodes[3].InnerText=="包含")
{
WhereTxt=WhereTxt+" Like ";
//运算符
WhereTxt=WhereTxt+"'%"+SqlWhere.ChildNodes[4].InnerText+"%'";
//条件值
}
else
{
if(SqlWhere.ChildNodes[3].InnerText=="不包含")
{
WhereTxt=WhereTxt+" Not Like ";
//运算符
WhereTxt=WhereTxt+"'%"+SqlWhere.ChildNodes[4].InnerText+"%'";
//条件值
}
else
{
if(SqlWhere.ChildNodes[3].InnerText=="is" || SqlWhere.ChildNodes[3].InnerText=="not is")
{
WhereTxt=WhereTxt+" is ";
//运算符
WhereTxt=WhereTxt+SqlWhere.ChildNodes[4].InnerText;
//条件值
}
else
{
WhereTxt=WhereTxt+SqlWhere.ChildNodes[3].InnerText;
//运算符
string Lname=SqlWhere.ChildNodes[2].InnerText;
string Ltype=GetListType(TableName,Lname);
//return "SELECT "+Ltype;
switch (Ltype)
{
case "adVarWChar":
WhereTxt=WhereTxt+"'"+SqlWhere.ChildNodes[4].InnerText+"'";
//字符串类型的数据
break;
case "adDouble"://数字
case "adSingle":
case "adInteger":
case "adCurrency":
WhereTxt=WhereTxt+SqlWhere.ChildNodes[4].InnerText;
//数值类型的数据
break;
case "adDBTimeStamp"://日期
string DT=SqlWhere.ChildNodes[4].InnerText;
WhereTxt=WhereTxt+"datevalue('"+DT+"')";
//日期时间类型的数据
break;
default:
WhereTxt=WhereTxt+"'"+SqlWhere.ChildNodes[4].InnerText+"'";
//其他类型的数据
break;
}
}
}
}
WhereTxt=WhereTxt+SqlWhere.ChildNodes[5].InnerText;
//右括号
}
}
if(FNyear=="T")
{
switch (TableName)
{
case "PayDataSum":
if(Fmd=="B")
{
WhereTxt=" ( " +WhereTxt+" ) and (结算方式)='按班组'";
}
if(Fmd=="E")
{
WhereTxt=" ( " +WhereTxt+" ) and (结算方式)='按个人'";
}
SqlTxt="SELECT * From (SELECT EBy.班组, EBy.工种, 年度, 类别,[1月份], [2月份], [3月份], [4月份], [5月份], [6月份],[7月份], [8月份], [9月份], [10月份],[11月份], [12月份],合计, FL AS 结算方式,0 as ID ";
SqlTxt=SqlTxt+" FROM EBy LEFT JOIN [SELECT * FROM PayDataSum WHERE PayDataSum.年度="+DateTime.Now.Year.ToString()+"]. AS Ny ON (EBy.工种 = Ny.工种) AND (EBy.班组 = Ny.班组) WHERE Ny.班组 Is Null OR Ny.工种 Is Null ";
SqlTxt=SqlTxt+" union SELECT * from PayDataSum where 年度="+DateTime.Now.Year.ToString()+")";
SqlTxt=SqlTxt+" where "+WhereTxt;
SqlTxt=SqlTxt+" ORDER BY 工种, 班组,年度, ID";
break;
case "WorksSum":
if(Fmd=="B")
{
WhereTxt=" ( " +WhereTxt+" ) and (EBy.FL)='按班组'";
}
if(Fmd=="E")
{
WhereTxt=" ( " +WhereTxt+" ) and (EBy.FL)='按个人'";
}
SqlTxt="SELECT * From (SELECT EBy.班组, EBy.工种, 年度, [1月份],[2月份],[3月份],[4月份],[5月份],[6月份],[7月份], [8月份],[9月份],[10月份],[11月份],[12月份],合计, EBy.FL";
SqlTxt=SqlTxt+" FROM EBy LEFT JOIN [select * from WorksSum where 年度="+DateTime.Now.Year.ToString()+"]. AS WK ON (EBy.工种 = WK.工种) AND (EBy.班组 = WK.班组) WHERE WK.班组 Is Null OR WK.工种 Is Null ";
SqlTxt=SqlTxt+" union SELECT * from WorksSum where 年度="+DateTime.Now.Year.ToString()+")";
SqlTxt=SqlTxt+" where "+WhereTxt;
SqlTxt=SqlTxt+" ORDER BY 工种, 班组,年度";
break;
case "JsbSum":
SqlTxt="SELECT PK.* From (Gsok INNER JOIN (SELECT * FROM (SELECT * FROM (SELECT K.ID,EBy.班组, K.年度, EBy.工种, K.工时数, K.总产值, K.应付工资, K.已付工资, K.实际应付工资,EBy.FL ";
SqlTxt=SqlTxt=SqlTxt+" FROM [SELECT * from EBy where EBy.FL='按班组']. AS EBy LEFT JOIN [Select * from JsbSum where 年度="+DateTime.Now.Year.ToString()+"]. AS K ON (EBy.班组 = K.班组) AND (EBy.工种 = K.工种) WHERE (((K.年度) Is Null))) ORDER BY 班组, 年度, 工种 ";
SqlTxt=SqlTxt=SqlTxt+" union SELECT null as ID,班组,年度, '小计' AS 工种, Sum(工时数), Sum(总产值), Sum(应付工资), Sum(已付工资) , Sum(实际应付工资) , FL FROM (SELECT * FROM (SELECT K.ID,EBy.班组, K.年度, EBy.工种, K.工时数, K.总产值, K.应付工资, K.已付工资, K.实际应付工资,EBy.FL ";
SqlTxt=SqlTxt=SqlTxt+" FROM [SELECT * from EBy where EBy.FL='按班组']. AS EBy LEFT JOIN [Select * from JsbSum where 年度="+DateTime.Now.Year.ToString()+"]. AS K ON (EBy.班组 = K.班组) AND (EBy.工种 = K.工种) WHERE (((K.年度) Is Null))) ORDER BY 班组, 年度, 工种) GROUP BY 班组, 年度, '小计', FL ";
SqlTxt=SqlTxt+"UNION Select * from JsbSum where 年度="+DateTime.Now.Year.ToString()+")) AS PK ON Gsok.工种名称 = PK.工种) ";
SqlTxt=SqlTxt+" where "+WhereTxt;
SqlTxt=SqlTxt+" ORDER BY 班组,年度,Gsok.ID";
break;
case "JseSum":
SqlTxt="SELECT JEK.* From (Gsok INNER JOIN (SELECT [JseSum].[ID], [WEp].[编号], [WEp].[姓名], [年度], [WEp].[班组], [WEp].[工种], [工时数], [单价], [应付工资], [已付工资], [对比] FROM WEp LEFT JOIN [Select * from JseSum where 年度="+DateTime.Now.Year.ToString()+"]. AS JE ON ([WEp].[编号]=JE.编号) AND ([WEp].[班组]=JE.班组) AND ([WEp].[工种]=JE.工种) WHERE JE.年度 Is Null ";
SqlTxt=SqlTxt=SqlTxt+" Union SELECT null as ID,编号,姓名, 年度,班组,'小计' as 工种, Sum(工时数), 单价, Sum(应付工资), Sum(已付工资), Sum(对比) FROM (SELECT [JseSum].[ID], [WEp].[编号], [WEp].[姓名], [年度], [WEp].[班组], [WEp].[工种], [工时数], [单价], [应付工资], [已付工资], [对比] ";
SqlTxt=SqlTxt=SqlTxt+" FROM WEp LEFT JOIN [Select * from JseSum where 年度="+DateTime.Now.Year.ToString()+"]. AS JE ON ([WEp].[编号]=JE.编号) AND ([WEp].[班组]=JE.班组) AND ([WEp].[工种]=JE.工种) WHERE JE.年度 Is Null) GROUP BY 编号, 姓名, 年度,班组,工种,单价 Union Select * from JseSum where 年度="+DateTime.Now.Year.ToString()+") as JEK ON Gsok.工种名称 = JEK.工种) ";
SqlTxt=SqlTxt+" where "+WhereTxt;
SqlTxt=SqlTxt+" ORDER BY 编号,班组,年度,Gsok.ID";
break;
default:
if(Fmd=="B")
{
WhereTxt=" ( " +WhereTxt+" ) and 结算方式='按班组'";
}
if(Fmd=="E")
{
WhereTxt=" ( " +WhereTxt+" ) and 结算方式='按个人'";
}
WhereTxt=" ( " +WhereTxt+" ) and 年度="+DateTime.Now.Year.ToString();
SqlHTxt="SELECT * From "+TableName+" where ";
SqlTxt=SqlHTxt+WhereTxt;
break;
}
}
else
{
if(Fmd=="B")
{
WhereTxt=" ( " +WhereTxt+" ) and 结算方式='按班组'";
}
if(Fmd=="E")
{
WhereTxt=" ( " +WhereTxt+" ) and 结算方式='按个人'";
}
SqlHTxt="SELECT * From "+TableName+" where ";
SqlTxt=SqlHTxt+WhereTxt;
}
return SqlTxt;
}
[ WebMethod(Description="Per session Hit Counter",EnableSession=true)]
public string DataSetup()
{
//添加记录且设置为自动生成ID值时,返回ID值
//除上述,修改成功时,返回-1
//设置不成功,返回错误字符串
try
{
string TableName=this.Context.Request["TableName"];
string OldKey=this.Context.Request["OldKey"];//ID键值
string Mode=this.Context.Request["Mode"];//操作模式
string AuKey=this.Context.Request["AutoKey"];//是否自动设置ID键值
string TextID=this.Context.Request["TextKey"];//ID键值是否为文本类型
string UpList=this.Context.Request["UpList"];//被编辑更新的字段列号
//读取客户端传来的参数
System.Xml.XmlDocument MyXmlDoc=new System.Xml.XmlDocument();
MyXmlDoc.Load(this.Context.Request.InputStream);
System.Xml.XmlNode SqlValue=MyXmlDoc.SelectSingleNode("Root/Record");
//定义XML对象,加载客户端传来的XML文档,取得Root/Record节点对象
int Datas=SqlValue.ChildNodes.Count;
//获得子节点个数
if(Datas>0 && Mode=="Add")
{
bool AutoKey;
if(AuKey=="True")
{
AutoKey=true;
}
else
{
AutoKey=false;
}
return SetAdd(AutoKey,TableName,MyXmlDoc);
//执行插入查询
}
if(Datas>0 && Mode=="Update")
{
//修改客户请求
int Ulist=int.Parse(UpList);
TableList[] TableH=ListNameGet(TableName);
bool TextKey;
if(TextID=="True")
{
TextKey=true;
}
else
{
TextKey=false;
}
return SetUpdate(TextKey,TableName,TableH[Ulist],OldKey,MyXmlDoc);
}
if(Mode=="Del")
{
//删除数据请求
if(TextID=="True")
{
return RunSql("DELETE from "+TableName+" WHERE ID='"+OldKey+"'");
}
else
{
return RunSql("DELETE from "+TableName+" WHERE ID="+OldKey);
}
//如果产品库存量不为0,应该不能删除该产品,但这是库存和成本核算的内容了.
}
return "操作类型的设置错误。";
//如果没有进入添加、修改或删除模块，则返回出错信息
}
catch(Exception error)
{
return error.Message;
//返回系统错误
}
finally
{
Mysjk.Close();
//关闭数据库
}
}
#region Component Designer generated code
//Web 服务设计器所必需的
private IContainer components = null;
private void InitializeComponent()
{
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -