private void Ini_Base_Data()
{
string sql = "";
string HS = "[]";
sql = "SELECT HSCODE||EXTRACODE as CODE,HSNAME||'('||HSCODE||EXTRACODE||')' as NAME FROM base_insphs where HSCODE is not null and enabled=1";
HS = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string CIQ = "[]";//委托单位
sql = "SELECT CIQ as CODE,CIQNAME||'('||CIQ||')' as NAME FROM base_ciqcode where enabled=1";
CIQ = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{HS:" + HS + ",CIQ:" + CIQ + "}");
Response.End();
}
public void Ini_Base_Data()
{
string sql = "";
string DECLREGION = "[]";//报关
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_shipping_destination where CODE is not null and enabled=1";
DECLREGION = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string INSPREGION = "[]";//报检
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_withinregion where CODE is not null and enabled=1";
INSPREGION = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{DECLREGION:" + DECLREGION + ",INSPREGION:" + INSPREGION + "}");
Response.End();
}
public void Ini_Base_Data()
{
string sql = "";
string DECLTRADE = "[]";//报关
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_decltradeway where CODE is not null and enabled=1";
DECLTRADE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string INSPTRADE = "[]";//报检
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_tradeway where CODE is not null and enabled=1";
INSPTRADE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{DECLTRADE:" + DECLTRADE + ",INSPTRADE:" + INSPTRADE + "}");
Response.End();
}
public void Ini_Base_Data()
{
string sql = "";
string DECLPACKAGE = "[]";//报关包装类型
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_packing where CODE is not null and enabled=1";
DECLPACKAGE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string INSPPACKAGE = "[]";//报检包装类型
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_insppackage where CODE is not null and enabled=1";
INSPPACKAGE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{DECLPACKAGE:" + DECLPACKAGE + ",INSPPACKAGE:" + INSPPACKAGE + "}");
Response.End();
}
private void Ini_Base_Data()
{
string sql = "";
string DECLCOUNTRY = "[]";//报关国家码
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_country where CODE is not null and enabled=1";
DECLCOUNTRY = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string INSPCOUNTRY = "[]";//报检国家码
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_inspcountry where CODE is not null and enabled=1";
INSPCOUNTRY = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{DECLCOUNTRY:" + DECLCOUNTRY + ",INSPCOUNTRY:" + INSPCOUNTRY + "}");
Response.End();
}
public void Ini_Base_Data()
{
string sql = "";
string DECLPORT = "[]";//报关
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_customdistrict where CODE is not null and enabled=1";
DECLPORT = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string INSPPORT = "[]";//报检
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_port where CODE is not null and enabled=1";
INSPPORT = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{DECLPORT:" + DECLPORT + ",INSPPORT:" + INSPPORT + "}");
Response.End();
}
//获取国家
private void getCountry()
{
string sql = String.Empty;
sql = @"select t1.*, t2.name as createmanname, t3.name as stopmanname
from base_country t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
where t1.enabled = '1'";
DataTable dt = DBMgrBase.GetDataTable(sql);
string country = JsonConvert.SerializeObject(dt, iso);
Response.Write("{COUNTRY:" + country + "}");
Response.End();
}
public int insert_sys_recordinfo(JObject json)
{
bcm.getCommonInformation(out stopman, out createman, out startdate, out enddate, json);
string select_sql = @"select * from base_company where code = '" + json.Value <string>("RECEIVEUNIT") + "'";
DataTable dt = DBMgrBase.GetDataTable(select_sql);
string receiveunitname = dt.Rows[0]["name"].ToString();
string sql = @"insert into sys_recordinfo(id,code,bookattribute,busiunit,receiveunit,trade,exempting,ismodel,remark,enabled,createman,stopman,createdate,startdate,enddate,receiveunitname)
values(sys_recordinfo_id.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',sysdate,to_date('{11}','yyyy/mm/dd hh24:mi:ss'),
to_date('{12}','yyyy/mm/dd hh24:mi:ss'),'{13}')";
sql = String.Format(sql, json.Value <string>("CODE"), json.Value <string>("BOOKATTRIBUTE"), json.Value <string>("BUSIUNIT"), json.Value <string>("RECEIVEUNIT"),
json.Value <string>("TRADE"), json.Value <string>("EXEMPTING"), json.Value <string>("ISMODEL"), json.Value <string>("REMARK"), json.Value <string>("ENABLED"),
createman, stopman, startdate, enddate, receiveunitname);
int i = DBMgrBase.ExecuteNonQuery(sql);
return(i);
}
public DataTable export_rela_ftpsetting(string strWhere)
{
string sql = @"select t1.id
,t1.profilename
,t1.uri
,t1.port
,t1.username
,t1.password
,t1.enabled
,t1.channelname
,t1.filetype
,t1.customdistrictcode
,decode(t1.entrusttype,'01','报关','02','报检') as entrusttype
from sys_ftpsettings t1 {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public void getCombox()
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as yearname
from base_declproductunit t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_year t4
on t1.yearid = t4.id ";
DataTable dt = DBMgrBase.GetDataTable(sql);
string json_unit = JsonConvert.SerializeObject(dt, iso);
Response.Write("{UNIT:" + json_unit + "}");
Response.End();
}
public void Ini_Base_Data()
{
string sql = "";
string CONTAINERSIZE = "[]";
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_containersize where CODE is not null and enabled=1";
CONTAINERSIZE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string CONTAINERTYPE = "[]";
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_containertype where CODE is not null and enabled=1";
CONTAINERTYPE = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
string CONTAINERSTANDARD = "[]";
sql = "SELECT CODE as CODE,NAME||'('||CODE||')' as NAME FROM base_containerstandard where CODE is not null and enabled=1";
CONTAINERSTANDARD = JsonConvert.SerializeObject(DBMgrBase.GetDataTable(sql));
Response.Write("{CONTAINERSIZE:" + CONTAINERSIZE + ",CONTAINERTYPE:" + CONTAINERTYPE + ",CONTAINERSTANDARD:" + CONTAINERSTANDARD + "}");
Response.End();
}
//获取经营单位等下拉数据
private void getCheckBoxData()
{
string sql = String.Empty;
string jsonEXEMPTING = String.Empty;
string jsonTRADEWAY = String.Empty;
string jsonCOMPANY = String.Empty;
string json = String.Empty;
//征免性质
sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as yearname
from base_exemptingnature t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_year t4
on t1.yearid = t4.id";
DataTable EXEMPTING = DBMgrBase.GetDataTable(sql);
jsonEXEMPTING = JsonConvert.SerializeObject(EXEMPTING, iso);
//贸易方式
sql = @"select t1.*, t2.name as createmanname, t3.name as stopmanname
from base_decltradeway t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id";
DataTable tradeway = DBMgrBase.GetDataTable(sql);
jsonTRADEWAY = JsonConvert.SerializeObject(tradeway, iso);
//经营单位，收发货单位
sql = @"select t1.*,t2.name as createmanname,t3.name as stopmanname from base_company t1 left join sys_user t2 on t1.createman=t2.id
left join sys_user t3 on t1.stopman=t3.id ";
DataTable company = DBMgrBase.GetDataTable(sql);
jsonCOMPANY = JsonConvert.SerializeObject(company, iso);
Response.Write("{EXEMPTING:" + jsonEXEMPTING + ",TRADEWAY:" + jsonTRADEWAY + ",COMPANY:" + jsonCOMPANY + "}");
Response.End();
}
private void getRecordDetails_Hscode()
{
string hscode = Request["hscode"];
string additionalno = Request["additionalno"];
string JsonHscode = string.Empty;
string sql2 = @"select ID, HSCODE, EXTRACODE from BASE_COMMODITYHS where 1=1 and enabled = 1 ";
if (!string.IsNullOrEmpty(hscode))
{
sql2 += " and HSCODE = '" + hscode + "'";
}
if (!string.IsNullOrEmpty(additionalno))
{
sql2 += " and ExtraCode = '" + additionalno + "'";
}
DataTable hs = DBMgrBase.GetDataTable(sql2);
JsonHscode = JsonConvert.SerializeObject(hs, iso);
Response.Write("{HSCODE:" + JsonHscode + "}");
Response.End();
}
public DataTable LoaData(string strWhere, string order, string asc, ref int totalProperty, int start, int limit)
{
string sql = @"select t1.id
,t1.profilename
,t1.uri
,t1.port
,t1.username
,t1.password
,t1.enabled
,t1.channelname
,t1.filetype
,t1.customdistrictcode
,decode(t1.entrusttype,'01','报关','02','报检') as entrusttype
from sys_ftpsettings t1 {0}";
sql = string.Format(sql, strWhere);
sql = Extension.GetPageSql2(sql, "t1.profilename", "", ref totalProperty, start, limit);
DataTable loDataSet = DBMgrBase.GetDataTable(sql);
return(loDataSet);
}
public DataTable check_update_repeat_record_details(JObject json, string recordinfoid)
{
string strWhere = String.Empty;
string sql = @"select * from sys_recordinfo_detail where 1 = 1 and id not in ('" + json.Value <string>("ID") + "') ";
if (!string.IsNullOrEmpty(json.Value <string>("ITEMNO").ToString()))
{
strWhere += " and itemno = '" + json.Value <string>("ITEMNO").ToString() + "'";
}
if (!string.IsNullOrEmpty(recordinfoid))
{
strWhere += " and recordinfoid = '" + recordinfoid + "'";
}
if (!string.IsNullOrEmpty(json.Value <string>("ITEMNOATTRIBUTE").ToString()))
{
strWhere += " and itemnoattribute = '" + json.Value <string>("ITEMNOATTRIBUTE").ToString() + "'";
}
sql = sql + strWhere;
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//导出
public DataTable exoprt_base_ciqcode(string strWhere, string yearid)
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as yearname
from base_ciqcode t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_year t4
on t1.yearid = t4.id
where 1 = 1
and t1.yearid = '{0}' {1} order by t1.ciq asc
";
sql = String.Format(sql, yearid, strWhere);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//获取ICQ代码库的数据
public DataTable LoaData(string strWhere, string order, string asc, ref int totalProperty, int start, int limit)
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as customareaname,
s.name as stopmanName
from base_year t1
left join sys_user t2
on t1.createman = t2.id
left join BASE_CUSTOMDISTRICT t3
on t1.customarea = t3.code
left join sys_user s
on t1.stopman = s.id
where t1.kind ={0} {1}
";
sql = string.Format(sql, (int)Base_YearKindEnum.CIQ, strWhere);
sql = Extension.GetPageSql2(sql, "t1.name", "desc", ref totalProperty, start, limit);
DataTable loDataSet = DBMgrBase.GetDataTable(sql);
return(loDataSet);
}
//导出
public DataTable export(string strWhere)
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as unitname1,
t5.name as unitname2
from sys_unitconvert t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_declproductunit t4
on t1.unitcode1 = t4.code
left join base_declproductunit t5
on t1.unitcode2 = t5.code where 1 = 1 {0}";
sql = String.Format(sql, strWhere);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//获取HS代码的数据
public DataTable MaintainloadData(string id, string strWhere, string asc, ref int totalProperty, int start, int limit)
{
string sql = @"select t8.name as yearname,
t7.name as createmanname,
t6.name as typename,
t5.name as chaptername,
t4.name as classname,
t3.name as secondunitname,
t2.name as legalunitname,
t9.name as stopmanname,
t1.*
from BASE_COMMODITYHS t1
LEFT JOIN BASE_DECLPRODUCTUNIT t2
on T1.LEGALUNIT = T2.code
LEFT JOIN BASE_DECLPRODUCTUNIT t3
on t1.secondunit = t3.code
LEFT JOIN BASE_DECLHSCLASS t4
on t1.classcode = t4.code
LEFT JOIN BASE_DECLHSCHAPTER t5
on t4.chaptercode = t5.code
LEFT JOIN BASE_DECLHSTYPE t6
on t5.typecode = t6.code
LEFT JOIN sys_user t7
on t1.createman = t7.id
LEFT JOIN BASE_YEAR t8
on t1.yearid = t8.id
left join sys_user t9
on t1.stopman = t9.id
where 1 = 1
and t1.yearid = '{0}' {1}
";
sql = string.Format(sql, id, strWhere);
sql = Extension.GetPageSql2(sql, "t1.hscode", "", ref totalProperty, start, limit);
DataTable loDataSet = DBMgrBase.GetDataTable(sql);
return(loDataSet);
}
//数据加载
public DataTable LoaData(string table, string strWhere, string order, string asc, ref int totalProperty, int start, int limit)
{
string load = "load";
JObject json = null;
string sql = sbw.get_base_sql(load, table, json, "") + strWhere;
if (table == "base_booksdata")
{
sql = Extension.GetPageSql2(sql, "t1.trade", "", ref totalProperty, start, limit);
}
else if (table == "sys_declarationcar")
{
sql = Extension.GetPageSql2(sql, "t1.createdate", "", ref totalProperty, start, limit);
}
else
{
sql = Extension.GetPageSql2(sql, "t1.code", "", ref totalProperty, start, limit);
}
DataTable loDataSet = DBMgrBase.GetDataTable(sql);
return(loDataSet);
}
//获取CIQ代码的数据
public DataTable MaintainloadData(string id, string strWhere, string asc, ref int totalProperty, int start, int limit)
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as yearname
from base_ciqcode t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_year t4
on t1.yearid = t4.id
where 1 = 1
and t1.yearid = '{0}' {1}
";
sql = string.Format(sql, id, strWhere);
sql = Extension.GetPageSql2(sql, "t1.ciq", "", ref totalProperty, start, limit);
DataTable loDataSet = DBMgrBase.GetDataTable(sql);
return(loDataSet);
}
public DataTable load(string strWhere, string order, string asc, ref int totalProperty, int start, int limit)
{
string sql = @"select t1.*,
t2.name as createmanname,
t3.name as stopmanname,
t4.name as unitname1,
t5.name as unitname2
from sys_unitconvert t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
left join base_declproductunit t4
on t1.unitcode1 = t4.code
left join base_declproductunit t5
on t1.unitcode2 = t5.code where 1 = 1 {0}";
sql = String.Format(sql, strWhere);
sql = Extension.GetPageSql2(sql, "t1.unitcode1", "asc", ref totalProperty, start, limit);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//导出数据
public DataTable export_table(string yearid, string strwhere)
{
string sql = @"select t8.name as yearname,
t7.name as createmanname,
t6.name as typename,
t5.name as chaptername,
t4.name as classname,
t3.name as secondunitname,
t2.name as legalunitname,
t9.name as stopmanname,
t1.*
from BASE_COMMODITYHS t1
LEFT JOIN BASE_DECLPRODUCTUNIT t2
on T1.LEGALUNIT = T2.code
LEFT JOIN BASE_DECLPRODUCTUNIT t3
on t1.secondunit = t3.code
LEFT JOIN BASE_DECLHSCLASS t4
on t1.classcode = t4.code
LEFT JOIN BASE_DECLHSCHAPTER t5
on t4.chaptercode = t5.code
LEFT JOIN BASE_DECLHSTYPE t6
on t5.typecode = t6.code
LEFT JOIN sys_user t7
on t1.createman = t7.id
LEFT JOIN BASE_YEAR t8
on t1.yearid = t8.id
left join sys_user t9
on t1.stopman = t9.id
where 1 = 1
and t1.yearid = '{0}' {1}
";
sql = string.Format(sql, yearid, strwhere);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}