//李兰兰专用，不能删
private DisNoteTB GetAllDisNoteTBBySql1(string strsql)
{
//填充数据集
DataTable dt = DBHelper.GetTable(strsql);
DisNoteTB DNte = new DisNoteTB();
//遍历数组
foreach (DataRow row in dt.Rows)
{
DNte.Nid = Convert.ToInt32(row["Nid"]);
DNte.CusID = Convert.ToInt32(row["CusID"]);
DNte.Cid = Convert.ToInt32(row["Cid"]);
DNte.BeginNo = (long)row["BeginNo"];
DNte.EndNo = (long)row["EndNo"];
DNte.Dtime = Convert.ToDateTime(row["Dtime"]);
DNte.Sum = Convert.ToDouble(row["Sum"]);
object obj = row["IsSet"];
int cnt = Convert.ToInt32(obj);
string isSets = "";
if (cnt == 0)
{
isSets = "否";
}
if (cnt == 1)
{
isSets = "是";
}
DNte.IsSet = isSets;
DNte.Remark = row["Remark"].ToString();
DNte.CarrieCompany = CCom.GetCompanyByid(Convert.ToInt32(row["Cid"]));
DNte.Customer = Cums.GetCusmoerByid(Convert.ToInt32(row["CusID"]));
}
return(DNte);
}
//修改表信息
public int UpdateDisNoteTB(DisNoteTB Disnote)
{
string strsql = "update DisNoteTB set CusID= @CusID,Cid=@Cid,BeginNO=@BeginNO,EndNo=@EndNo,Dtime=@Dtime,Sum=@Sum,IsSet=@IsSet,Remark=@Remark where Nid=@Nid";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@CusID", Disnote.CusID),
new SqlParameter("@Cid", Disnote.Cid),
new SqlParameter("@BeginNO", Disnote.BeginNo),
new SqlParameter("@EndNo", Disnote.EndNo),
new SqlParameter("@Dtime", Disnote.Dtime),
new SqlParameter("@Sum", Disnote.Sum),
new SqlParameter("@IsSet", Disnote.IsSet),
new SqlParameter("@Remark", Disnote.Remark),
new SqlParameter("@Nid", Disnote.Nid)
};
return(DBHelper.ExecuteCommand(strsql, para));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
//客户发件
//CustomerSentService css = new CustomerSentService();
//添加面单分配信息
public int InsertDisNoteTB(DisNoteTB Disnote)
{
string strsql = "insert into DisNoteTB values (@CusID,@Cid,@BeginNO,@EndNo,@Dtime,@Sum,@IsSet,@Remark)";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@CusID", Disnote.CusID),
new SqlParameter("@Cid", Disnote.Cid),
new SqlParameter("@BeginNO", Disnote.BeginNo),
new SqlParameter("@EndNo", Disnote.EndNo),
new SqlParameter("@Dtime", Disnote.Dtime),
new SqlParameter("@Sum", Disnote.Sum),
new SqlParameter("@IsSet", Disnote.IsSet),
new SqlParameter("@Remark", Disnote.Remark)
};
return(DBHelper.ExecuteCommand(strsql, para));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
protected void ImgbtnAdd_Click(object sender, ImageClickEventArgs e)
{
if (Page.IsValid)
{
if (Request.QueryString["sid"] == null)
{
//获取客户、公司
DisNoteTB nt = new DisNoteTB();
nt = dm.GetDisNoteTBByIds(txtNumbers.Text.ToString());
lblCus.Text = nt.Customer.CusName.ToString();
lblCom.Text = nt.CarrieCompany.CoName.ToString();
//插入数据
SentTB st = new SentTB();
st.CusID = nt.CusID;
st.CSid = Convert.ToInt64(txtNumbers.Text);
st.Cid = nt.Cid;
st.Kilo = Convert.ToDouble(txtWeight.Text);
st.Price = Convert.ToDouble(txtMoney.Text);
st.BeginDate = Convert.ToDateTime(txtDatetime.Text);
st.Remark = txtRemark.Text.ToString();
int count = sm.InsertSentTB(st);
if (count > 0)
{
lblMessage.Text = "插入成功！<br>客户：" + lblCus.Text + "<br>承运公司：" + lblCom.Text + "<br>面单号：" + txtNumbers.Text + "<br>重量：" + txtWeight.Text + "<br>金额：" + txtMoney.Text + "<br>起运时间：" + txtDatetime.Text + "<br>备注：" + txtRemark.Text;
}
else
{
lblMessage.Text = "添加失败！";
}
}
else
{
//修改信息
SentTB st = new SentTB();
st.Sid = Convert.ToInt32(Request.QueryString["sid"]);
st.CusID = Convert.ToInt32(lblCus.Text);
st.Cid = Convert.ToInt32(lblCom.Text);
st.CSid = Convert.ToInt64(txtNumbers.Text);
st.Kilo = Convert.ToDouble(txtWeight.Text);
st.Price = Convert.ToDouble(txtMoney.Text);
st.BeginDate = Convert.ToDateTime(txtDatetime.Text);
st.Remark = txtRemark.Text;
int count = sm.UpdateSentTB(st);
if (count > 0)
{
Response.Redirect("~/AcceptandSent/SentShow.aspx");
}
else
{
lblMessage.Text = "修改失败！";
}
}
}
}
protected void txtNumbers_TextChanged(object sender, EventArgs e)
{
//输入新的面单号，获取客户、公司
long number = Convert.ToInt64(txtNumbers.Text);
int cnt = dm.GetNoteIsDis(number);
if (cnt <= 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('该件没有分配信息！')</script>");
}
else
{
DisNoteTB nt = new DisNoteTB();
nt = dm.GetDisNoteTBByIds(txtNumbers.Text.ToString());
lblCus.Text = nt.Customer.CusName;
lblCom.Text = nt.CarrieCompany.CoName;
}
}
protected void txtRid_TextChanged(object sender, EventArgs e)
{
//根据面单号得到客户id和公司id
long rid = Convert.ToInt64(txtRid.Text);
int cnt = dm.GetNoteIsDis(rid);
if (cnt <= 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('该件为无头件！')</script>");
}
else
{
DisNoteTB dnt = new DisNoteTB();
dnt = dm.GetNoteByRid(rid);
ddlName.SelectedValue = dnt.CusID.ToString();
ddlCompany.SelectedValue = dnt.Cid.ToString();
}
}
private static List <DisNoteTB> GetDisNoteTBBySql(string strsql)
{
List <DisNoteTB> list = new List <DisNoteTB>();
DataTable table = DBHelper.GetTable(strsql);
foreach (DataRow row in table.Rows)
{
DisNoteTB dis = new DisNoteTB();
dis.Nid = Convert.ToInt32(row["nid"].ToString());
dis.CusID = Convert.ToInt32(row["cusid"]);
dis.Cid = Convert.ToInt32(row["cis"]);
dis.BeginNo = Convert.ToInt32(row["beginno"]);
dis.EndNo = Convert.ToInt32(row["endno"]);
dis.Dtime = Convert.ToDateTime(row["dtime"]);
dis.Sum = Convert.ToDouble(row["sum"]);
dis.IsSet = row["isset"].ToString();
dis.Remark = row["remark"].ToString();
list.Add(dis);
}
return(list);
}
protected void imgbtnsel_Click(object sender, ImageClickEventArgs e)
{
CustomerPianTB cp = new CustomerPianTB();
int cusid = Convert.ToInt32(ddlcus.SelectedValue);
DateTime qtime = Convert.ToDateTime(txtstime.Text);
DateTime ttime = Convert.ToDateTime(txtttime.Text);
try
{
if (customerpianmanager.GetCustomerPianTBById(cusid, qtime, ttime) != null)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('该用户信息已存在，请确认业务员及时间。')</script>");
return;
}
}
catch (Exception ex)
{
try
{
CustomersTB cus = customersmanager.GetCusmoerByid(cusid);
string name = cus.CusName; //获取客户名
DisNoteTB dis = disnotemanager.GetDisNoteTBById(cusid, qtime, ttime);
double oddmon = dis.Sum; //获取面单费用
string issrt = dis.IsSet; //获取面单费用是否已结算
if (issrt == "是")
{
cp.Remark = "面单费已结！";
}
else
{
cp.Remark = "面单费未结！";
}
CustomerSentTB cs = customersentmanager.GetCustomerSentTBById(cusid, qtime, ttime);
double sendmon = cs.Price;//获取发件费
CustomerSendTB cf = customersendmanager.GetCustomerSendTBById(cusid, qtime, ttime);
double givemon = cf.EAllPrice;//获取送件费
SentTB st = sentmanager.GetSentTBById(cusid, qtime, ttime);
double backmon = st.Price;//获取收到付件返利
AcceptTB at = acceptmanager.GetAcceptTBById(cusid, qtime, ttime);
double accmon = at.Price;//获取派收到付件款
IAEManagerTB ia = iaemanagermanager.GetIAEManagerTBById(cusid, qtime, ttime);
double othermon = ia.Price; //获取其他费用
double allmon = oddmon + sendmon + backmon - givemon - accmon + othermon; //总计
string ISsettle = "false";
cp.CusID = Convert.ToInt32(cusid);
cp.DateMon = ttime;
cp.OddMon = oddmon;
cp.SendMon = sendmon;
cp.GiveMon = givemon;
cp.BackMon = backmon;
cp.AccMon = accmon;
cp.OtherMon = othermon;
cp.AllMon = allmon;
cp.ISsettle = ISsettle;
int cou = customerpianmanager.AddCustomerPianTBInfo(cp);
if (cou == 1)
{
allInfo = customerpianmanager.GetAllCustomerPianTBInfo(cusid);
anpInfo.RecordCount = allInfo.Count();
BindData();
anpInfo.CurrentPageIndex = 1;
}
}
catch (Exception)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('无该用户信息，请确认业务员及时间。')</script>");
return;
}
}
}
public static string LoadDataFromExcel1(string filePath, string fileName, string tableName)
{
CustomerPriceService priceservce = new CustomerPriceService();
string Mess = string.Empty;
try
{
if (!tableName.EndsWith("$"))
{
tableName = tableName + "$";
}
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = "SELECT * FROM [" + tableName + "]";
OleDbCommand com = new OleDbCommand(sql, OleConn);
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds = new DataSet();
OleDaExcel.Fill(ds, tableName);
OleConn.Close();
int k = 0;
int Datacount = 0;
for (int n = 0; n < ds.Tables.Count; n++)
{
if (ds.Tables[n].Rows.Count < 1)
{
Mess = "没有数据！";
Datacount++;
}
}
for (int j = 0; j < ds.Tables.Count; j++)
{
for (int i = 0; i < ds.Tables[j].Rows.Count; i++)
{
long num = Convert.ToInt64(ds.Tables[j].Rows[i + 1][1]);
string sqlStr = "insert into CustomerSentTB(Rid,CusID,Cid,Destination,Kilo,Price,Resdate,IsSet) values";
sqlStr += " (" + ds.Tables[j].Rows[i + 1][1].ToString() + ","; //面单号
//面单分配
DisNoteManager numdis = new DisNoteManager();
DisNoteTB dnt = new DisNoteTB();
//根据面单号查客户ID和公司ID
dnt = numdis.GetNoteByRid(Convert.ToInt64(ds.Tables[j].Rows[i + 1][1]));
if (dnt.Customer != null)
{
sqlStr += dnt.Customer.CusID + ","; //客户ID
sqlStr += dnt.CarrieCompany.Cid + ",'"; //公司ID
}
else
{
sqlStr += "null" + ",";
sqlStr += "null" + ",'";
}
sqlStr += ds.Tables[j].Rows[i + 1][2].ToString() + "',"; //目的地
double kg = Convert.ToDouble(ds.Tables[j].Rows[i + 1][3]);
if (kg % 1 > 0)
{
kg = kg - kg % 1 + 1;
}
sqlStr += Convert.ToDouble(ds.Tables[j].Rows[i + 1][3]) + ","; //公斤数
if (dnt.Customer != null)
{
CustomerPriceTB tem = new CustomerPriceTB();
tem = priceservce.GetInfoByCusidAndCid(dnt.Customer.CusID, dnt.CarrieCompany.Cid);
PriceTB pt = new PriceTB();
PricesManager pm = new PricesManager();
pt = pm.GetPriceByInfo(tem.PNo, kg, ds.Tables[j].Rows[i + 1][2].ToString());
double price = pt.Price;
sqlStr += price + ",'"; //运费
}
else
{
sqlStr += 0 + ",'"; //运费
}
sqlStr += Convert.ToDateTime(ds.Tables[j].Rows[i + 1][4].ToString().Trim()) + "',"; //揽件时间
sqlStr += 0 + ")";
int row = DBHelper.ExecuteCommand(sqlStr);
k += row;
}
}
Mess = k.ToString();
return(Mess);
}
catch (Exception ex)
{
Mess = "" + ex.Message;
return(Mess);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Usermanager"] == null)
{
Response.Redirect("~/Login.aspx");
}
else
{
if (!IsPostBack)
{
//填充DropDownlist控件客户
this.ddlCustomer.DataSource = CusMan.GetAllCustomers();
this.ddlCustomer.DataTextField = "CusName";
this.ddlCustomer.DataValueField = "CusID";
ddlCustomer.DataBind();
//填充DropDownlist控件承运公司
this.ddlCompany.DataSource = ComMan.GetAllCarrieCompany();
this.ddlCompany.DataTextField = "CoName";
this.ddlCompany.DataValueField = "Cid";
ddlCompany.DataBind();
//控件窗体加载不显示
lblmessage.Visible = false;
lblerror.Visible = false;
//判断页面跳转传过来是否有Nid
if (Request.QueryString["Nid"] == null)
{
if (Request.QueryString["rid"] != null)
{
txtBeginNo.Text = Request.QueryString["rid"];
txtEndNo.Text = Request.QueryString["rid"];
RegisterTB rt = new RegisterTB();
rt = regMan.GetAllRegisterTBByRid(Convert.ToInt64(Request.QueryString["rid"]));
ddlCompany.SelectedValue = rt.Cid.ToString();
CarrieCompanyTB cct = new CarrieCompanyTB();
cct = ccm.GetCompanyByid(Convert.ToInt32(ddlCompany.SelectedValue));
txtmoney.Text = cct.Cmoney.ToString();
}
else
{
txtDate.Visible = false;
//清空所有的控件
txtBeginNo.Text = "";
txtEndNo.Text = "";
txtmoney.Text = "";
txtDate.Text = "";
txtRemark.Text = "";
}
}
else
{
//接受页面穿的值
int Nid = Convert.ToInt32(Request.QueryString["Nid"].ToString());
//实例化实体类
DisNoteTB disno = new DisNoteTB();
//调用方法查询记录
disno = DIsNtMan.GetByNidDisNoteTB(Nid);
ddlCustomer.SelectedValue = disno.CusID.ToString();
ddlCompany.SelectedValue = disno.Cid.ToString();
txtBeginNo.Text = disno.BeginNo.ToString();
txtEndNo.Text = disno.EndNo.ToString();
txtDate.Text = disno.Dtime.ToString();
txtmoney.Text = disno.Sum.ToString();
if (disno.IsSet == "是")
{
rdoNo.Checked = true;
}
if (disno.IsSet == "否")
{
rdoNo.Checked = true;
}
txtRemark.Text = disno.Remark;
}
}
}
}
protected void TmgBtnExit_Click(object sender, ImageClickEventArgs e)
{
if (Page.IsValid)
{
string kcid = ddlCustomer.SelectedValue;
string ccid = ddlCompany.SelectedValue;
string b = txtBeginNo.Text.Trim();
string ee = txtEndNo.Text.Trim();
int cuunt = DIsNtMan.numbers(kcid, ccid, b, ee);
if (cuunt > 0)
{
lblerror.Visible = true;
lblerror.Text = "该面单号已经被分配过！！！";
return;
}
//if (ddlCompany.SelectedValue == "0" || ddlCustomer.SelectedValue == "0")
//{
// Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('请选择承运公司或客户！！！')</script>");
// return;
//}
//实例化实体类
string time = DateTime.Now.ToString();
DisNoteTB disnote = new DisNoteTB();
disnote.CusID = Convert.ToInt32(ddlCustomer.SelectedValue);
disnote.Cid = Convert.ToInt32(ddlCompany.SelectedValue);
disnote.BeginNo = Convert.ToInt64(txtBeginNo.Text);
disnote.EndNo = Convert.ToInt64(txtEndNo.Text);
disnote.Dtime = Convert.ToDateTime(time);
int cid = Convert.ToInt32(ddlCompany.SelectedValue);
object obj = ComMan.GetCarrieCompanyMon(cid);
int cnt = Convert.ToInt32(obj);
long Begin = Convert.ToInt64(txtBeginNo.Text);
long End = Convert.ToInt64(txtEndNo.Text);
long sums = (End - Begin + 1) * cnt;
disnote.Sum = sums;
int iss = 0;
if (rdoNo.Checked == true)
{
iss = 0;
}
else if (rdoYes.Checked == true)
{
iss = 1;
}
else
{
iss = 0;
}
disnote.IsSet = iss.ToString();
disnote.Remark = txtRemark.Text;
disnote.Nid = Convert.ToInt32(Request.QueryString["Nid"]);
//添加的方法
if (Request.QueryString["Nid"] == null)
{
int cnnt = DIsNtMan.InsertDisNoteTB(disnote);
if (cnnt > 0)
{
if (Request.QueryString["rid"] != null)
{
//修改发件表里的数据
int cusid = Convert.ToInt32(ddlCustomer.SelectedValue);
int comid = Convert.ToInt32(ddlCompany.SelectedValue);
double price = Convert.ToDouble(txtmoney.Text);
long rid = Convert.ToInt64(txtBeginNo.Text);
int res = csm.UpdateWu(cusid, comid, price, rid);
if (res > 0)
{
Response.Redirect("~/PriceManager/CarriageMoney/CustomerSent.aspx");
}
else
{
lblmessage.Text = "重新分配失败！";
}
}
//添加成功后清空控件
else
{
lblmessage.Visible = true;
lblmessage.Text = "增加成功!!!!";
txtBeginNo.Text = "";
txtEndNo.Text = "";
txtmoney.Text = "";
txtDate.Text = "";
txtRemark.Text = "";
}
}
}
///修改的方法
if (Request.QueryString["Nid"] != null)
{
txtDate.Visible = true;
string times = txtDate.Text.Trim();
disnote.Dtime = Convert.ToDateTime(times);
int cnnnt = DIsNtMan.UpdateDisNoteTB(disnote);
if (cnnnt > 0)
{
lblmessage.Visible = true;
lblmessage.Text = "修改成功!!!";
}
}
}
}
//修改表信息
public int UpdateDisNoteTB(DisNoteTB Disnote)
{
return(DNTSe.UpdateDisNoteTB(Disnote));
}
//添加面单分配信息
public int InsertDisNoteTB(DisNoteTB Disnote)
{
return(DNTSe.InsertDisNoteTB(Disnote));
}
