//����SQL����еij��������ʽ������,�������һ��SQL��䣬���м���ô洢���̣��ڴ洢����������SQL��䡣
private int DealCEF(int m_TypeNo, int m_SetTableNo, int m_TableNo, bool isStat, int isLikeZB0110)
{
//����
string m_strCON = "";
//���ʽ
string m_strEXP = "";
//����
string m_strFUN = "";
//������
string m_strTBL = "";
//�ֶ���
string m_strFLD = "";
//�����ֶ���
string m_strASS = "";
char[] a;
//�������ڷָ��
a = "(".ToCharArray();
string[] strTempwl;
//����������һ���ַ���������'AA','BB'
for (int i = 0; i <= mConst.Count - 1; i++)
{
if (m_strCON == "")
{
m_strCON = m_strCON + "'" + mConst[i].ToString() + "'";
}
else
{
m_strCON = m_strCON + ",'" + mConst[i].ToString() + "'";
}
}
//����������һ���ַ���������'AA','BB'
for (int i = 0; i <= mFunction.Count - 1; i++)
{
if (m_strFUN == "")
{
strTempwl = mFunction[i].ToString().Split(a);
m_strFUN = m_strFUN + "'" + strTempwl[0].ToString() + "'";
}
else
{
strTempwl = mFunction[i].ToString().Split(a);
m_strFUN = m_strFUN + ",'" + strTempwl[0].ToString() + "'";
}
}
//�����ʽ����һ���ַ���������'AA','BB'
for (int i = 0; i <= mExpress.Count - 1; i++)
{
if (m_strEXP == "")
{
m_strEXP = m_strEXP + "'" + mExpress[i].ToString() + "'";
}
else
{
m_strEXP = m_strEXP + ",'" + mExpress[i].ToString() + "'";
}
}
//����������һ���ַ���������AA,BB
for (int i = 0; i <= mTable.Count - 1; i++)
{
if (m_strTBL == "")
{
m_strTBL = mTable[i].ToString();
}
else
{
m_strTBL = m_strTBL + "," + mTable[i].ToString();
}
}
//���ֶ�������һ���ַ���������AA,BB
for (int i = 0; i <= mField.Count - 1; i++)
{
if (m_strFLD == "")
{
m_strFLD = mField[i].ToString();
}
else
{
m_strFLD = m_strFLD + "," + mField[i].ToString();
}
}
//�������ֶ�����һ���ַ���������AA,BB
for (int i = 0; i <= mAssicate.Count - 1; i++)
{
if (m_strASS == "")
{
m_strASS = mAssicate[i].ToString();
}
else
{
m_strASS = m_strASS + "," + mAssicate[i].ToString();
}
}
//���д洢���̴�����Ҫ���ñ��ʽ�ַ����������ַ����������ַ���������ǰ��SQL���������ֶ���������Ϊ������SQL���
//���ô洢��������SQL���洢���̷�Ϊ�����֣�һ�Ǵ���statSQL��䣬������������,ͬʱ�����ﴫ��ȫ������
//dealData createSQL = new dealData();
dealData RptLoad = new dealData();
string strBaseexpr = "";
string strBaseitem = "";
//�õ�ȫ������,ȫ������ͨ�����뵽�洢�����У����д���
SqlDataReader objRCReader;
objRCReader = RptLoad.getWhole_condition(m_TypeNo, m_SetTableNo, m_TableNo);
while (objRCReader.Read())
{
m_strMainObject = objRCReader["mainobject"].ToString();
strBaseexpr = objRCReader["Baseexpr"].ToString();
strBaseitem = objRCReader["Baseitem"].ToString();
}
objRCReader.Close();
//���ɿ�ִ�е�SQL���
m_TBL = m_strTBL;
m_FLD = m_strFLD;
m_ASS = m_strASS;
reQuery re = new reQuery();
if (isStat)
{
int icount1 = 0, icount2 = 0;
if (strBaseitem != "")
{
icount1 = strBaseitem.Split('~').Length;
}
if (m_Item != null && this.m_Item != "")
{
icount2 = m_Item.Split('~').Length;
}
if (icount1 == 0)
{
if (icount2 == 0)
{
strBaseitem = "";
strBaseexpr = "";
}
else
{
strBaseitem = m_Item;
strBaseexpr = m_Expr;
}
}
else
{
if (icount2 == 0)
{
strBaseitem = strBaseitem;
strBaseexpr = strBaseexpr;
}
else
{
strBaseexpr = re.gs_Comexpress(icount1, icount2, strBaseexpr, m_Expr);
strBaseitem = strBaseitem + " ~" + m_Item;
}
}
QueryDeal dealCondition = new QueryDeal(m_strMainObject, strBaseexpr, strBaseitem, "");
//string strCondition=dealCondition.QueryResult();
string strCondition = dealCondition.QueryResult(m_TypeNo, m_SetTableNo, m_TableNo, DMServer.Common.ConfigMy.strUserID);
//hjh 2005.10.14 �����ⲿ��������,����ͳ�Ʒ�Χ����
if (strCondition != "")
{
strCondition += " and " + this.gsstrwhere;
}
else
{
strCondition = "select za0100 from " + m_strMainObject + " where " + gsstrwhere;
}
RptLoad.StatTBL(m_strMainObject, m_B0110, m_TypeNo, m_SetTableNo, m_TableNo, m_strTBL, m_strFLD, m_strCON, m_strEXP, m_strFUN, "", "", strCondition, m_strASS, m_Userid, isLikeZB0110);
}
return 0;
}
//���ɺϲ����SQL���
public string GetSQl(out string strWhere)
{
strWhere = "";
try
{
dealData RptR_C = new dealData();
SqlDataReader objRCReader;
dealData Rpt4 = new dealData();
SqlDataReader objRPT4Reader;
objRCReader = RptR_C.getRC_R(m_TypeNo, m_SetTableNo, m_TableNo);
Sequence First = new Sequence(-1, -1, 1, 1, "#");
Sequence Second = new Sequence(-1, -1, 1, 1, "#");
Sequence Result = new Sequence(-1, -1, 1, 1, "#");
string RowExpr = "";//�������ı��ʽ
string ColExpr = "";//�������ı��ʽ
string RowItem = "";//����������
string ColItem = "";//����������
string RowChineseItem = "";//�е�����������ϵ
string ColChineseItem = "";//�е�����������ϵ
string ResultItem = "";//���Ľ��������
string ResultExpr = "";//���Ľ���ı��ʽ
string ResultChinese = "";//����������
int sequenceRow = -1;
int sequenceCol = -1;
int getcount1 = 0;
int getcount2 = 0;
string[] strTempwl;
char[] a;
a = "~".ToCharArray();
int i = 1;
if (m_strMainObject == "")
{
string strtmp = " select MainObject from r200 where typeno = " + m_TypeNo + " and settableno = " + m_SetTableNo + " and tableno = " + m_TableNo;
DataAccess.Data.GetSql getsql = new DataAccess.Data.GetSql();
DataTable dt = getsql.gsQuerySql(strtmp, "R200");
if (dt == null || dt.Rows.Count == 0)
{
m_strMainObject = "AA01";
}
else
{
m_strMainObject = dt.Rows[0]["MainObject"].ToString();
}
}
//������������
while (objRCReader.Read())
{
if (i == 1)
{
if ((int)(objRCReader["icount"]) != 0)
{
First.STARTROW = (int)(objRCReader["startrow"]) + 2;
First.ENDROW = (int)(objRCReader["startrow"]) + 2 + (int)(objRCReader["icount"]) - 1;
First.STARTCOL = (int)(objRCReader["startcol"]) + 2;
First.ENDCOL = (int)(objRCReader["startcol"]) + 2 + (int)(objRCReader["icount"]) - 1;
First.SEQUENCETYPE = objRCReader["serialtype"].ToString();
}
}
else
{
if ((int)(objRCReader["icount"]) != 0)
{
Second.STARTROW = (int)(objRCReader["startrow"]) + 2;
Second.ENDROW = (int)(objRCReader["startrow"]) + 2 + (int)(objRCReader["icount"]) - 1;
Second.STARTCOL = (int)(objRCReader["startcol"]) + 2;
Second.ENDCOL = (int)(objRCReader["startcol"]) + 2 + (int)(objRCReader["icount"]) - 1;
Second.SEQUENCETYPE = objRCReader["serialtype"].ToString();
}
}
i++;
}
objRCReader.Close();
objRCReader = null;
bool blnTemp;
blnTemp = true;
if (Second.STARTROW != -1 && First.STARTROW != -1)
{
blnTemp = true;
}
else if (Second.STARTROW == -1 && First.STARTROW != -1)
{
Result = First;
blnTemp = false;
}
else if (First.STARTROW == -1 && Second.STARTROW != -1)
{
Result = Second;
blnTemp = false;
}
else
{
sequenceRow = -1;
sequenceCol = -1;
}
//return "";
if (blnTemp == false)//һ�л�һ��
{
//Result.STARTROW = First.STARTROW;
//Result.ENDROW = First.ENDROW;
//Result.STARTCOL = First.STARTCOL;
//Result.ENDCOL = First.ENDCOL;
//Result.SEQUENCETYPE = First.SEQUENCETYPE;
sequenceRow = m_row - Result.STARTROW;
sequenceCol = m_col - Result.STARTCOL;
if (Result.SEQUENCETYPE == "1")
{
if (sequenceCol != 0)
{
sequenceCol = -1;
}
}
else
{
if (sequenceRow != 0)
{
sequenceRow = -1;
}
}
}
else if (First.STARTROW == Second.STARTROW && First.STARTCOL == Second.STARTCOL)//������
{
Result.STARTROW = First.STARTROW;
if (First.SEQUENCETYPE == "1")
{
Result.ENDROW = First.ENDROW;
}
else
{
Result.ENDROW = Second.ENDROW;
}
Result.STARTCOL = First.STARTCOL;
if (First.SEQUENCETYPE == "2")
{
Result.ENDCOL = First.ENDCOL;
}
else
{
Result.ENDCOL = Second.ENDCOL;
}
Result.SEQUENCETYPE = "1&2";
sequenceRow = m_row - Result.STARTROW;
sequenceCol = m_col - Result.STARTCOL;
if (m_row > Result.ENDROW || m_col > Result.ENDCOL)
{
sequenceRow = -1;
sequenceCol = -1;
}
}
else//������û�д���
{
Result.SEQUENCETYPE = "1|2";
}
//�ҳ����ڵ����е�λ��
//if (First.SEQUENCETYPE == "1" && m_row - First.STARTROW >= 0 && m_row <= First.ENDROW)
//{
// sequenceRow = m_row - First.STARTROW;
//}
//if (First.SEQUENCETYPE == "2" && m_col - First.STARTCOL >= 0 && m_col <= First.ENDCOL)
//{
// sequenceCol = m_col - First.STARTCOL;
//}
//if (Second.SEQUENCETYPE == "1" && m_row - Second.STARTROW >= 0 && m_row <= Second.ENDROW)
//{
// sequenceRow = m_row - Second.STARTROW;
//}
//if (Second.SEQUENCETYPE == "2" && m_col - Second.STARTCOL >= 0 && m_col <= Second.ENDCOL)
//{
// sequenceCol = m_col - Second.STARTCOL;
//}
bool nullSequenceRow = false;
bool nullSequenceCol = false;
if (sequenceRow > -1 && sequenceCol > -1)//��������
{
if (sequenceRow > -1)
{
dealData Rpt1 = new dealData();
SqlDataReader objRPT1Reader;
objRPT1Reader = Rpt1.getOneCondition(m_TypeNo, m_SetTableNo, m_TableNo, sequenceRow, "1");
while (objRPT1Reader.Read())
{
if (objRPT1Reader["sequenceType"].ToString() == "4")
{
return "";
}
else
{
RowExpr = objRPT1Reader["baseexpr"].ToString();
RowItem = objRPT1Reader["baseitem"].ToString();
RowChineseItem = objRPT1Reader["chineseitem"].ToString();
if (RowItem == null || RowItem == "")
{
nullSequenceRow = true;
}
}
}
objRPT1Reader.Close();
//-----------------------------------------
dealData Rpt2 = new dealData();
SqlDataReader objRPT2Reader;
objRPT2Reader = Rpt2.getOneCondition(m_TypeNo, m_SetTableNo, m_TableNo, sequenceCol, "2");
while (objRPT2Reader.Read())
{
if (objRPT2Reader["sequenceType"].ToString() == "4")
{
return "";
}
else
{
ColExpr = objRPT2Reader["baseexpr"].ToString();
ColItem = objRPT2Reader["baseitem"].ToString();
ColChineseItem = objRPT2Reader["chineseitem"].ToString();
if (ColItem == null || ColItem == "")
{
nullSequenceCol = true;
}
}
}
objRPT2Reader.Close();
//�������ڷָ��
if (RowItem != "")
{
strTempwl = RowItem.ToString().Split(a);
getcount1 = strTempwl.Length;
}
if (ColItem != "")
{
strTempwl = ColItem.ToString().Split(a);
getcount2 = strTempwl.Length;
}
//string ResultItem = "";//���Ľ��������
//string ResultExpr = "";//���Ľ���ı��ʽ
//string ResultChinese = "";//����������
if (getcount1 == 0 && getcount2 == 0 && nullSequenceRow == false && nullSequenceCol == false)
{//���뵥Ԫ����
//getUnitCondition
objRPT4Reader = Rpt4.getUnitCondition(m_TypeNo, m_SetTableNo, m_TableNo, "2", m_row, m_col);
while (objRPT4Reader.Read())
{
if (objRPT4Reader["sequenceType"].ToString() == "4")
{
return "";
}
else
{
ColExpr = objRPT4Reader["baseexpr"].ToString();
ColItem = objRPT4Reader["baseitem"].ToString();
ColChineseItem = objRPT4Reader["chineseitem"].ToString();
}
}
objRPT4Reader.Close();
if (ColExpr != "")
{
ResultItem = ColItem;
ResultExpr = ColExpr;
ResultChinese = ColChineseItem;
}
else
{
return "";
}
}
else if (getcount1 == 0 && getcount2 != 0)
{
ResultItem = ColItem;
ResultExpr = ColExpr;
ResultChinese = ColChineseItem;
}
else if (getcount1 != 0 && getcount2 == 0)
{
ResultItem = RowItem;
ResultExpr = RowExpr;
ResultChinese = RowChineseItem;
}
else
{
if (ColItem == "")
{
ColExpr = "";
}
if (RowItem == "")
{
RowExpr = "";
}
ResultExpr = gs_Comexpress(getcount1, getcount2, RowExpr, ColExpr);
ResultItem = RowItem + "~" + ColItem;
ResultChinese = RowChineseItem + "~" + ColChineseItem;
}
getcount1 = getcount2 + getcount1;
}
}
else
{//��Ԫ����
objRPT4Reader = Rpt4.getUnitCondition(m_TypeNo, m_SetTableNo, m_TableNo, "3", m_row, m_col);
while (objRPT4Reader.Read())
{
ColExpr = objRPT4Reader["baseexpr"].ToString();
ColItem = objRPT4Reader["baseitem"].ToString();
ColChineseItem = objRPT4Reader["chineseitem"].ToString();
}
objRPT4Reader.Close();
if (ColExpr != "")
{
ResultItem = ColItem;
ResultExpr = ColExpr;
ResultChinese = ColChineseItem;
strTempwl = ResultItem.ToString().Split(a);
getcount1 = strTempwl.Length;
}
else
{
return "";
}
}
//����ȫ������
dealData Rpt3 = new dealData();
SqlDataReader objRPT3Reader;
objRPT3Reader = Rpt3.getOneCondition(m_TypeNo, m_SetTableNo, m_TableNo, -1, "0");
while (objRPT3Reader.Read())
{
if (objRPT3Reader["sequenceType"].ToString() == "4")
{
return "";
}
else
{
ColExpr = objRPT3Reader["baseexpr"].ToString();
ColItem = objRPT3Reader["baseitem"].ToString();
ColChineseItem = objRPT3Reader["chineseitem"].ToString();
}
}
objRPT3Reader.Close();
if (ColItem != "")
{
if (ColItem == "")
{
ColExpr = "";
}
if (ResultItem == "")
{
ResultExpr = "";
}
strTempwl = ColItem.ToString().Split(a);
getcount2 = strTempwl.Length;
ResultExpr = gs_Comexpress(getcount1, getcount2, ResultExpr, ColExpr);
if (ResultItem != "" && ResultItem != "~")
{
ResultItem = ResultItem + "~" + ColItem;
}
else
{
ResultItem = ColItem;
}
if (ResultChinese != "" && ResultChinese != "~")
{
ResultChinese = ResultChinese + "~" + ColChineseItem;
}
else
{
ResultChinese = ColChineseItem;
}
getcount1 = getcount1 + getcount2;
}
//�����ⲿ����
if (m_Item != null && this.m_Item != "")
{
strTempwl = m_Item.ToString().Split(a);
getcount2 = strTempwl.Length;
ResultExpr = gs_Comexpress(getcount1, getcount2, ResultExpr, m_Expr);
ResultItem = ResultItem + "~" + m_Item;
//ResultChinese = ResultChinese + "~" + ColChineseItem;
//getcount1 = getcount1 + getcount2;
}
//if(ResultItem!="")
//{
QueryDeal dealCondition = new QueryDeal(m_strMainObject, ResultExpr, ResultItem, "");
string strCondition = dealCondition.QueryResult();
if (strCondition != "" && m_AB0110 != "-1")
{
if (m_AB0110 != "" && m_AB0110 != "0")
{
if (m_strMainObject.ToUpper() != "AB01")
{
strCondition = strCondition + " and " + m_strMainObject + ".ab0110 like '" + m_AB0110 + "%'";
}
else
{
strCondition = strCondition + " and " + m_strMainObject + ".ab0110 = '" + m_AB0110 + "'";
}
}
else
{
if (m_AB0110 != "" && m_AB0110 != "0")
{
if (m_strMainObject.ToUpper() != "AB01")
{
strCondition = m_strMainObject + ".ab0110 like '" + m_AB0110 + "%'";
}
else
{
strCondition = m_strMainObject + ".ab0110 = '" + m_AB0110 + "'";
}
}
}
}
m_strFLD = dealCondition.FLD;
Datastat RptLoad = new Datastat();
RptLoad.MainObject = m_strMainObject;
RptLoad.StatResult(m_TypeNo, m_SetTableNo, m_TableNo, false);
string strTBL;
string strFLD;
string strASS;
strTBL = RptLoad.TBL;
strFLD = RptLoad.FLD;
strASS = RptLoad.ASS;
dealData dealRequery = new dealData();
if (strCondition == "")
{
if (m_AB0110 != "-1" && m_AB0110 != "0" && m_AB0110 != "")
{
if (m_strMainObject.ToUpper() != "AB01")
{
strCondition = "select " + m_strMainObject + ".ZA0100 from " + m_strMainObject + " where " + m_strMainObject + ".ZC9993 = '1' and " + m_strMainObject + ".ab0110 like '" + m_AB0110 + "%'";
}
else
{
strCondition = "select " + m_strMainObject + ".ZA0100 from " + m_strMainObject + " where " + m_strMainObject + ".ZC9993 = '1' and " + m_strMainObject + ".ab0110 = '" + m_AB0110 + "'";
}
}
else
{
strCondition = "select " + m_strMainObject + ".ZA0100 from " + m_strMainObject + " where " + m_strMainObject + ".ZC9993 = '1' ";
}
}
strWhere = strCondition;
strCondition = dealRequery.ReQuery(m_strMainObject, strTBL, strFLD, strASS, strCondition);
return strCondition;
//�����Ԫ����
//sequenceRow
}
catch (Exception ee)
{
return "";
//return "getsql " + ee.Message;
}
}
/// <summary>
/// ���ɱ��ʽ����
/// </summary>
/// <param name="str"></param>
private string GetStrItemCh(string strItemCh, string strExpr)
{
QueryDeal QD = new QueryDeal("", strExpr, strItemCh, "");
return QD.CreatSQLdisplay(strItemCh);
}
//���ɺϲ����SQL���
public string GetSQl(string ColItem, string ColExpr, string RowItem, string RowExpr, string dealFieldname, out string reCondition)
{
int getcount1 = 0;
int getcount2 = 0;
string ResultItem = "";//���Ľ��������
string ResultExpr = "";//���Ľ���ı��ʽ
if (ColItem == "")
{
ColExpr = "";
}
if (RowItem == "")
{
RowExpr = "";
}
string[] strTempwl;
char[] a;
try
{
a = "~".ToCharArray();
if (RowItem != "")
{
strTempwl = RowItem.ToString().Split(a);
getcount1 = strTempwl.Length;
}
if (ColItem != "")
{
strTempwl = ColItem.ToString().Split(a);
getcount2 = strTempwl.Length;
}
if (ColItem == "")
{
ColExpr = "";
}
if (RowItem == "")
{
RowExpr = "";
}
ResultExpr = gs_Comexpress(getcount1, getcount2, RowExpr, ColExpr);
if (RowItem == "")
{
ResultItem = ColItem;
}
else if (ColItem == "")
{
ResultItem = RowItem;
}
else
{
ResultItem = RowItem + "~" + ColItem;
}
getcount1 = getcount2 + getcount1;
//--------------------------------------------
Datastat RptLoad = new Datastat();
RptLoad.MainObject = m_strMainObject;
RptLoad.StatResult(ResultItem);
string strTBL;
string strFLD;
string strASS;
strTBL = RptLoad.TBL;
strFLD = RptLoad.FLD;
strASS = RptLoad.ASS;
//-------------------------------------------------------------
QueryDeal dealCondition = new QueryDeal(m_strMainObject, ResultExpr, ResultItem, "", dealFieldname);
string strCondition = dealCondition.QueryResult();
//return strCondition;
dealData dealRequery = new dealData();
reCondition = dealRequery.ReQuery(m_strMainObject, strTBL, strFLD, strASS, strCondition);
return strCondition;
}
catch (Exception ee)
{
reCondition = "";
return ee.Message;
}
}