public DataTable getCar()
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sql = string.Format(@"
select ownercode as carid,intime from GpsBusQueue
where Isfactory=1 and istask=0
"
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable getRequire(string no_bill)
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sql = string.Format(@"
select * from ls_send_require
where no_bill='{0}'
",no_bill
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable getSanYCarInfo()
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
ConStr con = new ConStr();
string ConnString = con.getSANYConnectString();
dboperate.ConnectString = ConnString;
string sql = string.Format(@"
select b.companyname,b.deliveryname,b.truckid,b.quantity,b.itemno from curout a
inner join outmaster b on a.deliveryid=b.deliveryid
"
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable getCarInfo()
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sql = string.Format(@"
select a.id,a.carid,'厂内' as locationstatus from gpscartmp a
inner join (select max(id) as maxid,carid from gpscartmp
group by carid) b on a.id=b.maxid
where a.locationstatus=4
order by a.carid
"
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable GetScalar(string sql)
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable SelectSend(string sdate,string edate)
{
dboperate = new DataBase.DBOperate();
DataTable dt = new DataTable();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sql =string.Format(@"select top 200 sql_1.*,isnull(sql_total.qty_leiji,0) as qty_leiji from (select ls_productorder.no_bill as no_productorder,convert(bit,0) as
is_wancheng,ls_hetong.no_bill as no_hetong,
ls_hetong.no_bill_in as no_bill_in,ls_peibidan.no_bill as no_peibidan,ls_productorder.no_fahuolou,
ls_company.name_company,ls_hetong.name_product,ls_productorder.shigongbuwei,ls_productorder.qty_yuding,
ls_productorder.supply_time,ls_productorder.gongyingpinlv ,ls_productorder.no_qiangdu_ranks,
ls_qiangdu_ranks.name_qiangdu_ranks,ls_productorder.no_type_bengsong,ls_type_bengsong.name_type_bengsong,
ls_guanzhuang.name_guanzhuang,ls_productorder.linkman,ls_productorder.linktel,done_time,ls_productorder.no_benghao,
ls_productorder.no_bengrenyuan,chanheliao1.name_chanheliao as no_chanheliao1__1,chanheliao2.name_chanheliao as
no_chanheliao2__1,ls_productorder.qty_chanheliao1,ls_productorder.qty_chanheliao2,ls_hetong.distance_car
from ls_productorder
left join ls_hetong on ls_productorder.no_hetong=ls_hetong.no_bill
left join ls_peibidan on ls_peibidan.no_product=ls_productorder.no_bill
left join ls_company on ls_company.no_ls=ls_hetong.no_company
left join ls_qiangdu_ranks on ls_qiangdu_ranks.no_ls=ls_productorder.no_qiangdu_ranks
left join ls_type_bengsong on ls_type_bengsong.no_ls=ls_productorder.no_type_bengsong
left join ls_guanzhuang on ls_guanzhuang.no_ls=ls_productorder.no_guanzhuang
left join ls_chanheliao chanheliao1 on ls_productorder.no_chanheliao1=chanheliao1.no_ls
left join ls_chanheliao chanheliao2 on ls_productorder.no_chanheliao2=chanheliao1.no_ls
where 1=1 and done_state = 0 and done_time is not null and done_time is not null ) sql_1
left join (select ls_send.no_productorder,sum(isnull(qty_send,0)) as qty_leiji from ls_send where
ls_send.date_bill
between '{0}' and '{1}'
and no_fahuolou='1#' group by ls_send.no_productorder) sql_total on
sql_1.no_productorder=sql_total.no_productorder
order by done_time desc "
,sdate
,edate
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public DataTable SelectAll()
{
dboperate = new DataBase.DBOperate();
DataTable dt = new DataTable();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
StringBuilder sql = new StringBuilder(@"select distinct x.* from ( select 0 as AutoID,a.no_bill as no_bill,a.date_bill as date_bill,a.no_bill_in as no_bill_in,a.supply_time as
supply_time,b.no_hetong__1,b.no_hetong__2__1,b.no_hetong__2__2,b.no_hetong__3,b.no_hetong__4,e.no_fahuolou__1,a.shigongbuwei as
shigongbuwei,a.qty_yuding as qty_yuding,a.tongqiangdu_date as
tongqiangdu_date,g.no_qiangdu_ranks__1,i.no_type_bengsong__1,k.no_guliaomax__1,a.kangzhe as kangzhe,a.bengche as
bengche,m.no_tanluodu__1,a.gongyingpinlv as gongyingpinlv,o.no_guanzhuang__1,a.linkman as linkman,a.linktel as
linktel,q.no_chanheliao1__1,a.qty_chanheliao1 as qty_chanheliao1,s.no_chanheliao2__1,a.qty_chanheliao2 as
qty_chanheliao2,u.no_chanheliao3__1,a.qty_chanheliao3 as qty_chanheliao3,a.other as other,a.done_time as done_time,a.person_make as
person_make,a.person_check as person_check,a.person_modify as person_modify,a.date_make as date_make,a.date_check as date_check,a.date_modify
as date_modify,a.no_shengoudan as no_shengoudan,w.no_kangshen__1,a.isno_peibi as isno_peibi,a.check_bad_times as check_bad_times,a.check_remark
as check_remark,y.check_state__1,y.check_state__2,a.done_state as done_state,a.price_unit_chanheliao1 as
price_unit_chanheliao1,a.price_unit_chanheliao2 as price_unit_chanheliao2,a.price_unit_chanheliao3 as price_unit_chanheliao3,a.is_lingxing as
is_lingxing,a.checi as checi,a.yaoqiu as yaoqiu,a.qty_shajiang as qty_shajiang,a.no_gongkongji as no_gongkongji,a.is_guanzhuang as
is_guanzhuang,a1.no_benghao__1,c1.no_bengrenyuan__1,a.is_shajiang as is_shajiang,a.qty_rank as qty_rank,a.qty_rank_shajiang as
qty_rank_shajiang,e1.no_employee1__1,g1.no_employee2__1,i1.no_employee3__1,k1.no_employee4__1,m1.no_employee5__1,o1.no_employee6__1,q1.no_car1__1,s1.no_car2__1,u1.no_car3__1,w1.no_car4__1,y1.no_car5__1,a2.no_car6__1,a.qty1
as qty1,a.qty2 as qty2,a.qty3 as qty3,a.qty4 as qty4,a.qty5 as qty5,a.qty6 as qty6 from (select * from ls_productorder where check_state='2'
and isno_peibi='1' and done_state='0' and (supply_time between DATEADD(day,-2,getdate()) and DATEADD(day,0,getdate())) ) a left outer join (
select c.no_bill,c.no_bill as no_hetong__1,d.no_hetong__2__1 as no_hetong__2__1,d.no_hetong__2__2 as no_hetong__2__2,c.name_product as
no_hetong__3,c.no_bill_in as no_hetong__4 from ls_hetong c left outer join ( select e.no_ls,e.no_ls as no_hetong__2__1,e.name_company as
no_hetong__2__2 from ls_company e ) d on d.no_ls=c.no_company ) b on b.no_bill=a.no_hetong left outer join ( select
f.no_ls,f.name_fahuolou as no_fahuolou__1 from ls_fahuolou f ) e on e.no_ls=a.no_fahuolou left outer join ( select
h.no_ls,h.name_qiangdu_ranks as no_qiangdu_ranks__1 from ls_qiangdu_ranks h ) g on g.no_ls=a.no_qiangdu_ranks left outer join ( select
j.no_ls,j.name_type_bengsong as no_type_bengsong__1 from ls_type_bengsong j ) i on i.no_ls=a.no_type_bengsong left outer join ( select
l.no_ls,l.name_guliaomax as no_guliaomax__1 from ls_guliaomax l ) k on k.no_ls=a.no_guliaomax left outer join ( select
n.no_ls,n.name_tanluodu as no_tanluodu__1 from ls_tanluodu n ) m on m.no_ls=a.no_tanluodu left outer join ( select
p.no_ls,p.name_guanzhuang as no_guanzhuang__1 from ls_guanzhuang p ) o on o.no_ls=a.no_guanzhuang left outer join ( select
r.no_ls,r.name_chanheliao as no_chanheliao1__1 from ls_chanheliao r ) q on q.no_ls=a.no_chanheliao1 left outer join ( select
t.no_ls,t.name_chanheliao as no_chanheliao2__1 from ls_chanheliao t ) s on s.no_ls=a.no_chanheliao2 left outer join ( select
v.no_ls,v.name_chanheliao as no_chanheliao3__1 from ls_chanheliao v ) u on u.no_ls=a.no_chanheliao3 left outer join ( select
x.no_ls,x.name_kangshen as no_kangshen__1 from ls_kangshen x ) w on w.no_ls=a.no_kangshen left outer join ( select
z.check_state,z.check_state as check_state__1,z.name_check_state as check_state__2 from ls_sys_check_state z ) y on
y.check_state=a.check_state left outer join ( select b1.no_ls,b1.no_ls as no_benghao__1 from ls_car b1 ) a1 on a1.no_ls=a.no_benghao left
outer join ( select d1.no_ls,d1.name_driver as no_bengrenyuan__1 from ls_driver d1 ) c1 on c1.no_ls=a.no_bengrenyuan left outer join (
select f1.no_ls,f1.name_employee as no_employee1__1 from ls_employee f1 ) e1 on e1.no_ls=a.no_employee1 left outer join ( select
h1.no_ls,h1.name_employee as no_employee2__1 from ls_employee h1 ) g1 on g1.no_ls=a.no_employee2 left outer join ( select
j1.no_ls,j1.name_employee as no_employee3__1 from ls_employee j1 ) i1 on i1.no_ls=a.no_employee3 left outer join ( select
l1.no_ls,l1.name_employee as no_employee4__1 from ls_employee l1 ) k1 on k1.no_ls=a.no_employee4 left outer join ( select
n1.no_ls,n1.name_employee as no_employee5__1 from ls_employee n1 ) m1 on m1.no_ls=a.no_employee5 left outer join ( select
p1.no_ls,p1.name_employee as no_employee6__1 from ls_employee p1 ) o1 on o1.no_ls=a.no_employee6 left outer join ( select r1.no_ls,r1.no_ls
as no_car1__1 from ls_car r1 ) q1 on q1.no_ls=a.no_car1 left outer join ( select t1.no_ls,t1.no_ls as no_car2__1 from ls_car t1 ) s1 on
s1.no_ls=a.no_car2 left outer join ( select v1.no_ls,v1.no_ls as no_car3__1 from ls_car v1 ) u1 on u1.no_ls=a.no_car3 left outer join (
select x1.no_ls,x1.no_ls as no_car4__1 from ls_car x1 ) w1 on w1.no_ls=a.no_car4 left outer join ( select z1.no_ls,z1.no_ls as no_car5__1
from ls_car z1 ) y1 on y1.no_ls=a.no_car5 left outer join ( select b2.no_ls,b2.no_ls as no_car6__1 from ls_car b2 ) a2 on
a2.no_ls=a.no_car6 ) x order by x.date_bill desc");
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public string no_bill(string s, string tablename)
{
DataTable reader = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string date_now = DateTime.Now.ToString("yyyyMMdd");
string no_bill = "";
string sql = "select no_bill from " + tablename + " where substring(no_bill,1,8)='" + date_now + "' order by no_bill desc";
reader = dboperate.GetDataTable(sql);
try
{
if (reader != null)
{
no_bill = reader.Rows[0]["no_bill"].ToString().Trim();
if (no_bill.Length > 4)
{
string bill_1 = no_bill.Substring(0, no_bill.Length - 4);
if (bill_1 == DateTime.Now.ToString("yyyyMMdd"))
{
string bill_2 = no_bill.Substring(no_bill.Length - 3, 3);
int no = Convert.ToInt32(bill_2) + 1;
if (no < 10)
{
bill_2 = "00" + no.ToString();
}
else if (no < 100)
{
bill_2 = "0" + no.ToString();
}
no_bill = bill_1 + "-" + bill_2;
}
else
{
no_bill = DateTime.Now.ToString("yyyyMMdd") + "-001";
}
}
else
{
no_bill = DateTime.Now.ToString("yyyyMMdd") + "-001";
}
}
else
{
no_bill = DateTime.Now.ToString("yyyyMMdd") + "-001";
}
}
catch(Exception ex)
{
no_bill = DateTime.Now.ToString("yyyyMMdd") + "-001";
}
no_bill = s + no_bill;
return no_bill;
}
public int Login(string username,string password)
{
dboperate = new DataBase.DBOperate();
int count = 0;
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sql = string.Format(@"
select count(*) from ls_sys_account
where username='******'
and password='******'
"
, username
, password
);
count=Convert.ToInt32(dboperate.ExecuteScalar(sql.ToString().Trim()));
return count;
}
public DataTable getYupai()
{
DataTable dt = new DataTable();
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
string sdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
string edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
string sql = string.Format(@"
select (case no_type_bengsong when '01' then '非泵' when '02' then '泵送' end) as name_bengsong,* from ls_send_yupai
where date_bill between '{0}' and '{1}'
and isdel=0
", sdate,edate
);
dt = dboperate.GetDataTable(sql.ToString().Trim());
return dt;
}
public decimal GetScalar_d(string sql)
{
decimal dt;
try
{
dboperate = new DataBase.DBOperate();
string ConnString = getConnectString();
dboperate.ConnectString = ConnString;
dt = Convert.ToDecimal(dboperate.ExecuteScalar(sql.ToString().Trim()));
return dt;
}
catch (Exception ex)
{
dt = 0;
return dt;
}
}