📄 querydb.cs
字号:
}
//得到所有可能的货物类型
public void GetGoodsTypesNameList( ArrayList alUserID)
{
ConnectDB();
string tstrQueryString="select GoodsTypeName from mGoodsType ;" ;
string tstrTable="GoodsType";
DataTable dt =ExeQuery(tstrQueryString,tstrTable);
alUserID.Add("所有货物");
for(int I=0;I<dt.Rows.Count;I++)
{
alUserID.Add(dt.Rows[I][0].ToString());
}
DisConnectDB();
}
//得到所有的用户代码
public void GetUserNameList(ArrayList alUserID)
{
ConnectDB();
string tstrQueryString="select UserID from mSysUsers ;";
string tstrTable="UserName";
DataTable dt =ExeQuery(tstrQueryString,tstrTable);
alUserID.Add("所有操作员");
for(int I=0;I<dt.Rows.Count;I++)
{
alUserID.Add(dt.Rows[I][0].ToString());
}
DisConnectDB();
}
//得到所有的卡口名称
public void GetGateWayNameList(ArrayList alUserID)
{
ConnectDB();
string tstrQueryString="select PlaceName from mPlaceName where (PlaceId>=100) and (PlaceId<=200) ";
string tstrTable="PlaceName";
DataTable dt =ExeQuery(tstrQueryString,tstrTable);
alUserID.Add("所有卡口");
for(int I=0;I<dt.Rows.Count;I++)
{
alUserID.Add(dt.Rows[I][0].ToString());
}
DisConnectDB();
}
//得到所有的查验方式
public void GetCheckTypeList(ArrayList alUserID)
{
alUserID.Add("所有查验方式");
alUserID.Add("卡口 查验");
alUserID.Add("人工 查验");
alUserID.Add("H986 查验");
}
//得到所有的放行方式
public void GetGoTypeList(ArrayList alUserID)
{
ConnectDB();
string tstrQueryString="select Demo from mCheckFlagInfo ;" ;
string tstrTable="mCheckFlagInfo";
DataTable dt =ExeQuery(tstrQueryString,tstrTable);
alUserID.Add("所有放行方式");
for(int I=0;I<dt.Rows.Count;I++)
{
alUserID.Add(dt.Rows[I][0].ToString());
}
DisConnectDB();
}
//得到一票多箱的详细信息
public DataTable GetOneBillMultiContaDetail(string sTableName,string strBillNo,string strVoyage_No)
{
string strQuery="";
ConnectDB();
strQuery=" select distinct a.Conta_id as CONTA_NO, '未过卡口' as PASS_STATUS,a.bill_no ";
strQuery+=" from sManifest_Conta_rel as a ";
strQuery+=" where a.bill_no='"+strBillNo+"'"+" and not exists (select contano ,bill_no from mbarConta where a.Bill_no=mBarconta.Bill_no and a.conta_id=mBarConta.contano) ";
strQuery+=" union select distinct a.Conta_id as CONTA_NO,'已过卡口' as PASS_STATUS,a.bill_no ";
strQuery+=" from sManifest_Conta_rel as a ";
strQuery+=(" where a.bill_no='"+strBillNo+"'"+" and a.Voyage_No='"+strVoyage_No+"'"+ " and exists (select contano ,bill_no from mbarConta where a.Bill_no=mBarconta.Bill_no and a.conta_id=mBarConta.contano) ");
strQuery+=(" order by Pass_status ");
DataTable dt =ExeQuery(strQuery,sTableName);
DisConnectDB();
return dt;
}
//得到指定报关单头的详细相关信息
public DataTable GetEntryInfo(string strEntryID)
{
string strQuery="";
ConnectDB();
strQuery=" select * from sENTRY_HEAD where sENTRY_HEAD.ENTRY_ID = '" ;
strQuery+=strEntryID;
strQuery+="'";
DataTable dt =ExeQuery(strQuery,"sENTRY_HEAD");
int iValue;
iValue=dt.Rows.Count;
if(iValue<=0)
{
DisConnectDB();
ConnectDB();
strQuery=" select * from sIM_DECL_CUR_HEAD where sIM_DECL_CUR_HEAD.TURN_NO = '" ;
strQuery+=strEntryID;
strQuery+="'";
dt =ExeQuery(strQuery,"sIM_DECL_CUR_HEAD");
}
DisConnectDB();
return dt;
}
//得到指定报关单体的详细相关信息
public DataTable GetEntryListInfo(string sTableName ,string strEntryID)
{
string strQuery="";
ConnectDB();
strQuery=" select * from sENTRY_LIST where sENTRY_LIST.ENTRY_ID = '" ;
strQuery+=strEntryID;
strQuery+="'";
DataTable dt =ExeQuery(strQuery,sTableName);
DisConnectDB();
return dt;
}
//得到指定提单的详细相关信息
public DataTable GetBillInfo(string sTableName ,string strBillNo,string strVoyage_No)
{
string strQuery="";
ConnectDB();
strQuery=" select * from sMANIFEST_LIST_REL as a where a.BILL_NO= '" ;
strQuery+=strBillNo;
strQuery+="' and a.Voyage_No='";
strQuery+=strVoyage_No;
strQuery+="' ";
DataTable dt =ExeQuery(strQuery,sTableName);
DisConnectDB();
return dt;
}
//得到指定箱号信息头的详细相关信息
public DataTable GetContaHead(string sTableName ,string strBillNo , string strVehi)
{
string strQuery="";
ConnectDB();
strQuery=(" Select distinct e.Vehi_No,e.VehiCurWeight,D_WT,e.GoodsTotalWT,f.CheckDemo ,i.GoodsTypeName,k.demo " );
strQuery+=(" from mBarConta as d ");
strQuery+=(" left join mBarWeight as e " );
strQuery+=(" on d.ConnectID=e.ConnectID ");
strQuery+=(" left join mCheckInfo as f ");
strQuery+=(" on d.ConnectID=f.ConnectID ");
strQuery+=(" left join mContaBillRetaType as g ");
strQuery+=(" on d.ContaBillRelaID=g.ContaBillRelaID ");
strQuery+=(" left join mPerFetchEntry as h ");
strQuery+=(" on h.VOYAGE_NO=d.VoyAge_No and d.Ship_id=h.Ship_id and h.Bill_No=d.Bill_No ");
strQuery+=(" left join mGoodsType as i ");
strQuery+=(" on i.GoodsType=h.GoodsType ");
strQuery+=(" left join mPlaceName as j ");
strQuery+=(" on j.PlaceId=f.PlaceId ");
strQuery+=(" left join mDischargePlace as k ");
strQuery+=(" on k.DISCHARGE_PLACE=h.DISCHARGE_PLACE ");
// strQuery+=(" left join mContaControlInfo as l ");
// strQuery+=(" on d.VOYAGE_NO=l.VoyAge_No and d.Bill_No=l.Bill_No and d.CONTANo=l.ContaNo ");
strQuery+=(" where d.Bill_No='");
strQuery+=strBillNo;
strQuery+="'";
strQuery+=(" and e.Vehi_No='");
strQuery+=strVehi;
strQuery+="';";
DataTable dt =ExeQuery(strQuery,sTableName);
DisConnectDB();
return dt;
}
//得到指定箱号信息的详细相关信息
public DataTable GetContaInfo(string sTableName ,string strBillNo,string strVehi)
{
string strQuery="";
ConnectDB();
strQuery=(" Select distinct d.CONTANo as Conta_No ,a.CONTA_MODEL,d.CurEmptyWT,e.CurrGoodsNetWT,k.ControlTypeName,g.ContaBillRelaName, d.Entry_Error,d.EntryLimitErr,d.MftError,d.MftLimitErr,0 as SingleWeight " );
strQuery+=(" from mBarConta as d ");
strQuery+=(" left join sMANIFEST_CONTA_REL as a " );
strQuery+=(" on a.VOYAGE_NO=d.VoyAge_No and a.SHIP_Name_En=d.SHIP_Name_En and a.Bill_No=d.Bill_No and a.CONTA_ID=d.ContaNo ");
strQuery+=(" left join mBarWeight as e ");
strQuery+=(" on d.ConnectID=e.ConnectID ");
strQuery+=(" left join mCheckInfo as f ");
strQuery+=(" on d.ConnectID=f.ConnectID ");
strQuery+=(" left join mContaBillRetaType as g ");
strQuery+=(" on d.ContaBillRelaID=g.ContaBillRelaID ");
strQuery+=(" left join mContaControlInfo as l ");
strQuery+=(" on a.VOYAGE_NO=l.VoyAge_No and a.Bill_No=l.Bill_No and a.CONTA_ID=l.ContaNo ");
strQuery+=(" left join mContaControlType as k ");
strQuery+=(" on l.ControlTypeID=k.ControlTypeID ");
strQuery+=(" where d.Bill_No='");
strQuery+=strBillNo;
strQuery+="'";
strQuery+=(" and e.Vehi_No='");
strQuery+=strVehi;
strQuery+="';";
DataTable dt =ExeQuery(strQuery,sTableName);
DisConnectDB();
return dt;
}
// public DataTable GetOneEntryMutiContaInfo(string sTableName ,string strBillNo)
// {
// DataTable dt;
// return dt;
// }
public void Dispose()
{
}
}
public class XmlDoc
{
// DBOperator DB=DBOperatorFactory.GetDBOperator();
private DataSet ds;
/// <summary>
/// 或取报关单头和体数据
/// </summary>
/// <returns>string:XML格式的字符串</returns>
///
public int GetEntryType(string strEntry)
{
QueryDB QueryEntry;
QueryEntry =new QueryDB();
int iResult=QueryEntry.GetEentryType(strEntry);
return iResult;
}
public string GetEntryHeadXML(string strEntry_ID)
{
StringBuilder code = new StringBuilder(2048);
DataTable dt ;
QueryDB QueryEntry;
QueryEntry =new QueryDB();
try
{
dt=QueryEntry.GetEntryInfo( strEntry_ID);
code.Append("<?xml version='1.0' encoding='gb2312'?>" );
//code.Append(" ");
code.Append("<?xml-stylesheet type='text/xsl' href='entry.xsl' ?>");
//code.Append(" ");
code.Append("<response>"+"\n");
code.Append(" ");
code.Append("<status>success:1 select is completed!</status>"+"\n");
code.Append(" ");
code.Append("<mEntry_head>"+"\n");
code.Append(" ");
code.Append("<result>"+"\n");
for(int i=0;i<dt.Columns.Count;i++)
{
if(dt.Rows[0][i].ToString().Length > 0)
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">");
code.Append(dt.Rows[0][i].ToString());
code.Append("</");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">"+"\n");
}
else
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append("/>"+"\n");
}
}
code.Append(" ");
code.Append("</result>"+"\n");
code.Append(" ");
code.Append("</mEntry_head>"+"\n");
dt.Dispose();
dt=QueryEntry.GetEntryListInfo("sENTRY_LIST", strEntry_ID);
code.Append(" ");
code.Append("<mEntry_list>"+"\n");
code.Append(" ");
code.Append("<results count='1'>"+"\n");
for(int j=0;j<dt.Rows.Count;j++)
{
code.Append(" ");
code.Append("<result>"+"\n");
for(int i=0;i<dt.Columns.Count;i++)
{
if(dt.Rows[j][i].ToString().Length > 0)
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">");
code.Append(dt.Rows[j][i].ToString());
code.Append("</");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">"+"\n");
}
else
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append("/>"+"\n");
}
}
code.Append(" ");
code.Append("</result>"+"\n");
}
code.Append(" ");
code.Append("</results>"+"\n");
code.Append(" ");
code.Append("</mEntry_list>"+"\n");
code.Append("</response>");
dt.Dispose();
return code.ToString();
}
catch(System.Exception err)
{
throw err;
}
finally
{
QueryEntry.DisConnectDB();
}
}
public string GetBillXML(string strBillNo,string strVoyage_No)
{
StringBuilder code = new StringBuilder(2048);
DataTable dt ;
QueryDB QueryEntry;
QueryEntry =new QueryDB();
try
{
dt=QueryEntry.GetBillInfo("sMANIFEST_LIST_REL", strBillNo,strVoyage_No);
code.Append("<?xml version='1.0' encoding='gb2312'?>" + "\n");
code.Append("<?xml-stylesheet type='text/xsl' href='Manifest.xsl' ?>"+"\n");
code.Append("<response>"+"\n");
// code.Append(" ");
// code.Append("<status>success:1 select is completed!</status>"+"\n");
code.Append(" ");
code.Append("<mManifest_List>>"+"\n");
code.Append(" ");
code.Append("<result>"+"\n");
for(int i=0;i<dt.Columns.Count;i++)
{
if(dt.Rows.Count>0 )
{
if (dt.Rows[0][i].ToString().Length > 0)
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">");
code.Append(dt.Rows[0][i].ToString());
code.Append("</");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append(">"+"\n");
}
else
{
code.Append(" ");
code.Append("<");
code.Append(dt.Columns[i].ColumnName.ToString());
code.Append("/>"+"\n");
}
}
}
code.Append(" ");
code.Append("</result>"+"\n");
code.Append(" ");
code.Append("</mManifest_List>"+"\n");
code.Append(" ");
code.Append("</response>");
dt.Dispose();
return code.ToString();
}
catch(System.Exception err)
{
throw err;
}
finally
{
QueryEntry.DisConnectDB();
}
}
public string GetOneBillMultiContaXML(string strBillNo,string strVoyage_No)
{
StringBuilder code = new StringBuilder(2048);
DataTable dt ;
QueryDB QueryEntry;
QueryEntry =new QueryDB();
try
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -