private void GetTodoCount()
{
string sql = string.Format(" 1=1 and state='执行中'");
string UserId = Session["currentUserId"].ToString();
tb_EFormService eformSer = new tb_EFormService();
int allEForms = eformSer.GetListArray_ToDo_Count(sql, Convert.ToInt32(UserId));
if (allEForms > 0)
{
lblMessTodo.Text = string.Format("你有 {0} 个文件要审批", allEForms);
lblMessTodoOld.Text = string.Format("（原）你有 {0} 个文件要审批", allEForms);
imgMess.Visible = true;
}
else
{
lblMessTodoOld.Text = "";
lblMessTodo.Text = "";
imgMess.Visible = false;
}
//查询自己所有正在处理的单据
sql = string.Format("select count(*) from tb_EForm where state='执行中' and appPer={0}", UserId);
int zhixingzhongCount = Convert.ToInt32(DBHelp.ExeScalar(sql));
lblDoing.Text = string.Format("你有{0}个申请正在执行中....", zhixingzhongCount);
//查询今天 有多少单据审批成功
sql = string.Format(@"select count(*) from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id
where state='通过' and appPer={0} and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'", UserId, DateTime.Now.ToShortDateString());
int succsess = Convert.ToInt32(DBHelp.ExeScalar(sql));
lblSuccess.Text = string.Format("今天 你有{0}个申请审批 通过", succsess);
//查询今天 有多少单据审批失败
sql = string.Format(@"select count(*) from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id
where state='不通过' and appPer={0} and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'", UserId, DateTime.Now.ToShortDateString());
lblFail.Text = string.Format("今天 你有{0}个申请审批 没有通过审核", DBHelp.ExeScalar(sql));
try
{ //查询今天在销售订单中采购人是我的 所有通过的单子
sql = string.Format(@"select count(*) from tb_EForm left join (select e_id,Max(doTime)AS MaxTime from tb_EForms group by e_id) as newTable on tb_EForm.id=newTable.e_id
where state='通过' and MaxTime between '{1} 00:00:00' and '{1} 23:59:59'
and tb_EForm.alle_id in (select id from TB_POOrder where caigou='{0}')
and proid=(select pro_Id from A_ProInfo where pro_Type='订单报批表')", base.Session["LoginName"].ToString(), DateTime.Now.ToShortDateString());
int count = Convert.ToInt32(DBHelp.ExeScalar(sql));
if (count > 0)
{
lblCaiSuccess.Text = string.Format("今天 订单审批通过个数： {0} ", count);
lblCaiSuccess.Visible = true;
}
else
{
lblCaiSuccess.Visible = false;
}
}
catch (Exception)
{
}
try
{ //查询合同是否要过期
sql = "select count(*) from HR_Person where ContractCloseTime=convert(varchar(50),getdate(),23)";
int count = Convert.ToInt32(DBHelp.ExeScalar(sql));
if (count > 0 && Session["LoginName"] == "李琍")
{
lblContract.Text = string.Format("今天 合同到期个数： {0} ", count);
lblContract.Visible = true;
}
else
{
lblContract.Visible = false;
}
}
catch (Exception)
{
}
lbtnX.Text = string.Format("你有{0}条商品已入库需出库", new RuSellReportService().GetCountByAE(Session["LoginName"].ToString()));
}
protected void Button1_Click(object sender, EventArgs e)
{
List <int> ruids = new List <int>();
ruids.Add(9917);
//ruids.Add(14294);
//ruids.Add(14312);
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
foreach (var ids in ruids)
{
var selectInfo = string.Format(@"select avg(SupplierInvoiceNum) as avgSupplierInvoiceNum, sum(SupplierInvoiceTotal) as sumSupplierInvoiceTotal from TB_SupplierInvoices
left join TB_SupplierInvoice on TB_SupplierInvoice.id=TB_SupplierInvoices.id
where RuIds={0} and SupplierInvoiceTotal>0 and ((status='通过' and IsYuFu=0) or (Status<>'不通过' and IsYuFu=1)) ", ids);
SqlCommand objCommand = conn.CreateCommand();
objCommand.CommandText = selectInfo;
decimal avgSupplierInvoiceNum = 0;
decimal sumSupplierInvoiceTotal = 0;
using (var reader = objCommand.ExecuteReader())
{
if (reader.Read())
{
object ojb;
ojb = reader["avgSupplierInvoiceNum"];
if (ojb != null && ojb != DBNull.Value)
{
avgSupplierInvoiceNum = Convert.ToDecimal(ojb);
}
ojb = reader["sumSupplierInvoiceTotal"];
if (ojb != null && ojb != DBNull.Value)
{
sumSupplierInvoiceTotal = Convert.ToDecimal(ojb);
}
}
reader.Close();
}
TB_SupplierInvoice model = new TB_SupplierInvoice();
model.Status = "通过";
model.CreteTime = DateTime.Now;
model.CreateName = "姚世中";
model.CaiTuiProNo = "";
model.LastSupplier = "易佳通";
model.Remark = "全部退货";
var eform = new tb_EForm();
eform.appPer = 117;
eform.appTime = DateTime.Now;
eform.createPer = 117;
eform.createTime = DateTime.Now;
eform.proId = 31;
eform.state = "通过";
eform.toPer = 0;
eform.toProsId = 0;
var orders = new List <SupplierToInvoiceView>();
var invoiceModel = new SupplierToInvoiceView();
invoiceModel.Ids = ids;
//invoiceModel.IsYuFu = true;
invoiceModel.SupplierFPNo = "";
invoiceModel.SupplierInvoiceDate = DateTime.Now;
var AvgSupplierInvoicePrice = -(sumSupplierInvoiceTotal / avgSupplierInvoiceNum);
invoiceModel.SupplierInvoicePrice = AvgSupplierInvoicePrice;
invoiceModel.lastGoodNum = avgSupplierInvoiceNum;
invoiceModel.SupplierInvoiceTotal = -sumSupplierInvoiceTotal;
invoiceModel.FuShuTotal = invoiceModel.SupplierInvoiceTotal;
invoiceModel.ActPay = invoiceModel.SupplierInvoiceTotal;
invoiceModel.GuestName = "易佳通";
//invoiceModel.CaiTuiProNo = caiTuiProNo;
invoiceModel.RePayClear = 2;
invoiceModel.IsPayStatus = 0;//未支付
invoiceModel.IsHeBing = 1;
orders.Add(invoiceModel);
int mainId = 0;
new TB_SupplierInvoiceService().addTran(model, eform, orders, out mainId);
}
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (this.FormCheck())
{
try
{
User user = new User();
user.LoginUserNO = this.txtNo.Text;
//user.LoginAddress = this.txtAddress.Text;
//user.LoginRemark = this.txtCardNo.Text;
user.LoginIPosition = this.ddlDeptment.Text;
user.LoginPwd = MD5Util.Encrypt(this.txtPwd.Text);
user.LoginPhone = this.txtTel.Text;
user.LoginId = this.txtUserid.Text;
user.LoginName = this.txtUserName.Text;
//user.LoginStatus = this.ddlState.Text;
//user.LoginTmpPwd = this.ddlState.Text;
user.LoginStatus = this.ddlState.Text;
user.LoginTmpPwd = this.ddlSex.Text;
user.ReportTo = Convert.ToInt32(ddlReportTo.SelectedItem.Value);
user.Zhiwu = txtZhiwu.Text;
user.LoginMemo = txtEMail.Text;
user.CompanyCode = ddlCompany.Text;
user.SheBaoCode = ddlSheBao.Text;
user.IsSpecialUser = cbIsSpecialUser.Checked;
user.Mobile = txtMobile.Text;
user.CardNO = txtCardNO.Text;
user.CityNo = txtCityNo.Text;
user.Education = ddlEducation.Text;
user.School = txtSchool.Text;
user.SchoolDate = txtSchoolDate.Text;
user.Title = txtTitle.Text;
user.Political = ddlPolitical.Text;
user.HomeAdd = txtHomeAdd.Text;
user.WorkDate = txtWorkDate.Text;
int userId = this.UserSer.addUser(user);
if (userId > 0)
{
List <RoleUser> roleUserList = new List <RoleUser>();
for (int i = 0; i < this.gvList.Rows.Count; i++)
{
CheckBox cbSelect = this.gvList.Rows[i].Cells[0].FindControl("IfSelected") as CheckBox;
if (cbSelect.Checked)
{
RoleUser roleUser = new RoleUser();
roleUser.UserId = userId;
roleUser.RoleId = Convert.ToInt32(this.gvList.DataKeys[i].Value);
roleUserList.Add(roleUser);
}
}
new RoleUserService().addRole_User(0, userId, roleUserList);
//28 被派工人
//32 请假代理人 自动加载到审批流程中
string sql = string.Format(@"insert into A_Role_User values(28,{0},0,1);insert into A_Role_User values(32,{0},0,1);", userId);
DBHelp.ExeCommand(sql);
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('添加成功！');</script>");
this.clear();
IniData();
}
else
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('添加失败！');</script>");
}
}
catch (Exception ex)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('" + ex.Message + "！');</script>");
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!base.IsPostBack)
{
if (NewShowAll_textName("用户信息", "可记录权限"))
{
ViewState["ScanSpecGuests"] = true;
}
else
{
ViewState["ScanSpecGuests"] = false;
}
List <Role> roles1 = this.roleSer.getAllRoles("");
roles1.Insert(0, new Role {
RoleName = ""
});
this.ddlDeptment.DataTextField = "RoleName";
this.ddlDeptment.DataValueField = "RoleName";
this.ddlDeptment.DataSource = roles1;
this.ddlDeptment.DataBind();
TB_CompanyService comSer = new TB_CompanyService();
var comList = comSer.GetListArray("");
comList.Insert(0, new VAN_OA.Model.BaseInfo.TB_Company()
{
ComCode = "", ComName = ""
});
ddlCompany.DataSource = comList;
ddlCompany.DataBind();
ddlSheBao.DataSource = comList;
ddlSheBao.DataBind();
List <Role> roles = new List <Role>();
if (base.Request["UserId"] != null)
{
this.btnAdd.Visible = false;
User user = this.UserSer.getUserByUserId(Convert.ToInt32(base.Request["UserId"]));
this.txtNo.Text = user.LoginUserNO;
//this.txtAddress.Text = user.LoginAddress;
//this.txtCardNo.Text = user.LoginRemark;
this.txtCreateTime.Text = user.LoginCreateTime.ToShortDateString();
this.ddlDeptment.Text = user.LoginIPosition;
txtPwd.Attributes.Add("Value", user.LoginPwd);
lblPwd.Text = user.LoginPwd;
this.txtPwd.Text = user.LoginPwd;
this.txtTel.Text = user.LoginPhone;
this.txtUserid.Text = user.LoginId;
this.txtUserName.Text = user.LoginName;
this.ddlState.Text = user.LoginStatus;
this.ddlSex.Text = user.LoginTmpPwd;
ddlCompany.Text = user.CompanyCode;
try
{
ddlSheBao.Text = user.SheBaoCode;
}
catch (Exception)
{
}
txtZhiwu.Text = user.Zhiwu;
cbIsSpecialUser.Checked = user.IsSpecialUser;
txtEMail.Text = user.LoginMemo;
if (user.ReportTo != 0)
{
ddlReportTo.Text = user.ReportTo.ToString();
}
txtMobile.Text = user.Mobile;
txtCardNO.Text = user.CardNO;
txtCityNo.Text = user.CityNo;
ddlEducation.Text = user.Education;
txtSchool.Text = user.School;
txtSchoolDate.Text = user.SchoolDate;
txtTitle.Text = user.Title;
ddlPolitical.Text = user.Political;
txtHomeAdd.Text = user.HomeAdd;
txtWorkDate.Text = user.WorkDate;
roles = this.roleSer.getAllRolesByUseridExAdmin(user.Id);
string checkRole = string.Format("select count(*) from Role_User where roleId=1 and UserId=" + Session["currentUserId"].ToString());
bool ifAdmin = Convert.ToInt32(DBHelp.ExeScalar(checkRole)) >= 1 ? true : false;
if (ifAdmin)
{
txtPwd.Enabled = true;
}
else
{
txtPwd.Enabled = false;
}
}
else
{
roles = this.roleSer.getAllRolesByUseridExAdmin(0);
this.btnUpdate.Visible = false;
}
IniData();
this.gvList.DataSource = roles;
this.gvList.DataBind();
}
}
public bool FormCheck()
{
if (this.txtUserid.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写用户账号！');</script>");
this.txtUserid.Focus();
return(false);
}
if (this.txtUserName.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写用户名称！');</script>");
this.txtUserName.Focus();
return(false);
}
if (txtPwd.Enabled != false && this.txtPwd.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写用户密码！');</script>");
this.txtPwd.Focus();
return(false);
}
if (this.txtZhiwu.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写职务！');</script>");
this.txtZhiwu.Focus();
return(false);
}
if (this.ddlReportTo.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写所属上级！');</script>");
this.ddlReportTo.Focus();
return(false);
}
if (this.txtTel.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写电话！');</script>");
this.txtTel.Focus();
return(false);
}
if (this.txtEMail.Text.Trim() == "")
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('请填写E-Mail！');</script>");
this.txtEMail.Focus();
return(false);
}
if (!string.IsNullOrEmpty(txtSchoolDate.Text))
{
if (CommHelp.VerifesToDateTime_1(txtSchoolDate.Text) == false)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('毕业时间 格式错误！');</script>");
return(false);
}
}
if (!string.IsNullOrEmpty(txtWorkDate.Text))
{
if (CommHelp.VerifesToDateTime_1(txtWorkDate.Text) == false)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('参加工作时间 格式错误！');</script>");
return(false);
}
}
if (!string.IsNullOrEmpty(txtCreateTime.Text))
{
if (CommHelp.VerifesToDateTime_1(txtCreateTime.Text) == false)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('建档时间 格式错误！');</script>");
return(false);
}
}
if (base.Request["UserId"] != null)
{
if (Convert.ToInt32(DBHelp.ExeScalar(string.Format("select count(*) from tb_User where loginId='{0}' and id <>{1}", this.txtUserid.Text, base.Request["UserId"]))) > 0)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('用户账号" + this.txtUserid.Text + "已存在，请重新填写！');</script>");
this.txtUserid.Focus();
return(false);
}
if (Convert.ToInt32(DBHelp.ExeScalar(string.Format("select count(*) from tb_User where loginName='{0}' and id <>{1}", this.txtUserName.Text, base.Request["UserId"]))) > 0)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('用户名" + this.txtUserName.Text + "已存在，请重新填写！');</script>");
this.txtUserName.Focus();
return(false);
}
}
else
{
if (Convert.ToInt32(DBHelp.ExeScalar(string.Format("select count(*) from tb_User where loginId='{0}'", this.txtUserid.Text))) > 0)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('用户账号" + this.txtUserid.Text + "已存在，请重新填写！');</script>");
this.txtUserid.Focus();
return(false);
}
if (Convert.ToInt32(DBHelp.ExeScalar(string.Format("select count(*) from tb_User where loginName='{0}'", this.txtUserName.Text))) > 0)
{
base.ClientScript.RegisterStartupScript(base.GetType(), null, "<script>alert('用户名" + this.txtUserName.Text + "已存在，请重新填写！');</script>");
this.txtUserName.Focus();
return(false);
}
}
return(true);
}
private void LoadExcel(string fileAddress)
{
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int allSheets = 0;
foreach (DataRow rowTable in sheetNames.Rows)
{
string sheetName = rowTable["TABLE_NAME"].ToString();
conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn);
int rowIndex = 1;
BankFlowService bandFlowService = new BankFlowService();
PetitionsService petSer = new PetitionsService();
CG_POOrderService POSer = new CG_POOrderService();
CAI_POCaiService pocaiSer = new CAI_POCaiService();
using (SqlConnection sqlconn = DBHelp.getConn())
{
sqlconn.Open();
SqlCommand sqlCommand = sqlconn.CreateCommand();
using (OleDbDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
//if (rowIndex > 1)
//{
// Petitions model = new Petitions();
// model.Number = dataReader[0].ToString();
// model.Type = dataReader[1].ToString();
// model.OldIndex = dataReader[2].ToString();
// var ojb = dataReader[3];
// if (ojb != null && ojb != DBNull.Value)
// {
// model.SignDate = Convert.ToDateTime(ojb);
// }
// model.PoNo = dataReader[4].ToString();
// model.Summary = dataReader[5].ToString();
// ojb = dataReader[6];
// if (ojb != null && ojb != DBNull.Value)
// {
// model.SumPages = Convert.ToInt32(ojb);
// }
// model.Local = dataReader[7].ToString();
// model.L_Year = Convert.ToInt32(dataReader[8]);
// model.L_Month = Convert.ToInt32(dataReader[9]);
// model.Remark = dataReader[10].ToString();
// model.SumCount = 1;
// model.BCount = 1;
// model.Handler = "王汉中";
// var poList = POSer.GetSimpListArray(string.Format("and pono='{0}'", model.PoNo), sqlCommand);
// if (poList.Count > 0)
// {
// CG_POOrder poorderModel = poList[0];
// model.AE = poorderModel.AE;
// model.GuestName = poorderModel.GuestName;
// model.Name = poorderModel.POName;
// //var supplierList = pocaiSer.GetLastSupplier(string.Format("'{0}'", model.PoNo), sqlCommand);
// //model.SalesUnit = string.Join(",", supplierList.Select(t => t.Supplier).ToArray());
// //model.Total = supplierList.Sum(t => t.Total);
// }
// petSer.Add(model,sqlCommand);
//}
if (rowIndex > 8)
{
BankFlow model = new BankFlow();
model.TransactionType = dataReader[0].ToString();
if (model.TransactionType == "")
{
continue;
}
model.BusinessType = dataReader[1].ToString();
model.PayerAccountBank = dataReader[3].ToString();
model.DebitAccountNo = dataReader[4].ToString();
model.OutPayerName = dataReader[5].ToString();
model.BeneficiaryAccountBank = dataReader[7].ToString();
model.PayeeAccountNumber = dataReader[8].ToString();
model.InPayeeName = dataReader[9].ToString();
DateTime dt = DateTime.ParseExact(dataReader[10].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
model.TransactionDate = Convert.ToDateTime(dt.ToString("yyyy-MM-dd") + " " + dataReader[11]);
model.TradeCurrency = dataReader[12].ToString();
model.TradeAmount = Convert.ToDecimal(dataReader[13]);
model.AfterTransactionBalance = Convert.ToDecimal(dataReader[14]);
model.TransactionReferenceNumber = dataReader[17].ToString();
model.VoucherType = dataReader[20].ToString();
model.Reference = dataReader[23].ToString();
model.Purpose = dataReader[24].ToString();
model.Remark = dataReader[25].ToString();
model.Remarks = dataReader[26].ToString();
//判断是否重复
string sql = string.Format("SELECT top 1 ID FROM [BankFlow] WHERE TransactionReferenceNumber='{0}'", model.TransactionReferenceNumber);
sqlCommand.CommandText = sql;
var resultId = sqlCommand.ExecuteScalar();
if (!(resultId is DBNull) && resultId != null)
{
model.Id = Convert.ToInt32(resultId);
bandFlowService.Update(model, sqlCommand);
}
else
{
bandFlowService.Add(model, sqlCommand);
}
}
rowIndex++;
}
}
sqlconn.Close();
}
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (tvMain.SelectedNode == null || tvMain.SelectedNode.Depth <= 0)
{
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "a", string.Format("alert('请选择一个文件夹节点进行删除！');"), true);
return;
}
if (tvMain.SelectedNode.Depth == 0)
{
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "a", string.Format("alert('该文件夹节点无法删除！');"), true);
return;
}
string sql = "Folder_ID in (";
sql += string.Format("{0},", tvMain.SelectedNode.Target);
for (int i = 0; i < tvMain.SelectedNode.ChildNodes.Count; i++)
{
sql += string.Format("{0},", tvMain.SelectedNode.ChildNodes[i].Target);
}
sql = sql.Substring(0, sql.Length - 1);
sql += ")";
lblMess.Text = "";
//检测文件夹中是否有文件
string checkF = string.Format("select mainName,Folder_NAME from tb_Attachment left join tb_Folder on tb_Folder.Folder_ID=tb_Attachment.folder_Id where tb_Attachment." + sql);
DataTable dt = DBHelp.getDataTable(checkF);
if (dt.Rows.Count > 0)
{
string message = "<br/>提示：<br/>";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (tvMain.SelectedNode.Text == dt.Rows[i]["Folder_NAME"].ToString())
{
message += string.Format("文件夹：{1},存在文件：{0}<br/>", dt.Rows[i]["mainName"].ToString(), dt.Rows[i]["Folder_NAME"].ToString());
}
else
{
message += string.Format("子文件夹：{1},存在文件：{0}<br/>", dt.Rows[i]["mainName"].ToString(), dt.Rows[i]["Folder_NAME"].ToString());
}
}
message += string.Format("上述原因导致改文件夹（{0}）不能被删除", tvMain.SelectedNode.Text);
lblMess.Text = message;
// ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "a", string.Format("alert('{0}！');",message), true);
return;
}
else
{
string delete = "delete from tb_Folder where " + sql;
DBHelp.ExeCommand(delete);
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "a", string.Format("alert('删除成功！');"), true);
lblSonId.Visible = false;
lblPareId.Visible = false;
lblParent.Text = " ";
txtFolder.Text = "";
btnSave.Enabled = false;
btnCancel.Enabled = false;
IniData();
}
}
public void Execute()
{
//OA系统每天晚上23:55，准时发起，一个任务，就是点击缓存按钮的动作，这样我们可以获得 当天的数据的快速列表印象。
if (DateTime.Now.Hour == 22 && DateTime.Now.Minute == 00)
{
ServiceAppSetting.LoggerHander.Invoke(string.Format("执行-缓存! - {0}", DateTime.Now), "");
new JXC_REPORTService().CatchData();
ServiceAppSetting.LoggerHander.Invoke(string.Format("结束-缓存! - {0}", DateTime.Now), "");
}
ServiceAppSetting.LoggerHander.Invoke(string.Format("do! {0}", DateTime.Now), "");
//每天早上1，2，3点处理最高值
if ((DateTime.Now.Hour == 1 || DateTime.Now.Hour == 2 || DateTime.Now.Hour == 3) && DateTime.Now.Minute == 0)
{
ServiceAppSetting.LoggerHander.Invoke(string.Format("执行-最高金额! - {0}", DateTime.Now), "");
CashFlowService cashFlowService = new CashFlowService();
List <CashFlow> cashFlowList = cashFlowService.GetListArray(" AND IsClose=0");
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlCommand objCommand = conn.CreateCommand();
try
{
objCommand.Parameters.Clear();
foreach (var model in cashFlowList)
{
cashFlowService.Update(model, objCommand);
}
}
catch (Exception)
{
}
}
ServiceAppSetting.LoggerHander.Invoke(string.Format("结束-最高金额! - {0}", DateTime.Now), "");
}
if (DateTime.Now.Hour == 23 && DateTime.Now.Minute <= 5)
{
ServiceAppSetting.LoggerHander.Invoke(string.Format("执行一次! - {0}", DateTime.Now), "");
int currentYear = DateTime.Now.Year;
int currentMonth = DateTime.Now.Month;
int nextYear = DateTime.Now.AddMonths(1).Year;
int nextMonth = DateTime.Now.AddMonths(1).Month;
int currentZhangQi = GetZhouQi(currentMonth);
int nextZhangQi = GetZhouQi(nextMonth);
//if (currentZhangQi == nextZhangQi && currentYear == nextYear)
//{
// return;
//}
List <GuestProBaseInfo> guestProBaseInfos = new GuestProBaseInfoService().GetListArray("GuestPro=1");
TB_GuestTrackService GuestTrackSer = new TB_GuestTrackService();
string sql = string.Format(" 1=1 and QuartNo='{1}' and YearNo='{0}' ", currentYear, currentZhangQi);
sql += string.Format(@" and (TB_GuestTrack.id in (select allE_id from tb_EForm where proId in (
select pro_Id from A_ProInfo where pro_Type='客户联系跟踪表') and state='通过') or TB_GuestTrack.id not in (select allE_id from tb_EForm where proId in (
select pro_Id from A_ProInfo where pro_Type='客户联系跟踪表') ))");
List <TB_GuestTrack> GuestTracks = GuestTrackSer.GetListArray(sql);
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
SqlTransaction tan = conn.BeginTransaction();
SqlCommand objCommand = conn.CreateCommand();
objCommand.Transaction = tan;
try
{
objCommand.Parameters.Clear();
foreach (var model in GuestTracks)
{
//if (currentZhangQi == 4)
//{
// if (model.MyGuestProString == "自我开拓")
// {
// model.MyGuestPro = 2;
// }
//}
model.QuartNo = nextZhangQi.ToString();
model.YearNo = nextYear.ToString();
model.CreateTime = DateTime.Now;
string secondUpdate = GuestTrackSer.UpdateToString(model, model.GuestId, nextYear.ToString(), nextZhangQi.ToString());
if (model.MyGuestProString == "自我开拓")
{
//我们的系统在每个季度的最后一天的晚上12点会自动同步客户信息到下一个季度，我们定义
//． 如该客户目前的属性是自我开拓，该客户从登记之日起，加上自我开拓的有效期月数数字对应的日期，
//如小于（<） 新季度第一天，新季度的客户的属性变成 原有资源，否则 还是自我开拓
if (DateTime.Now.Month == 3 || DateTime.Now.Month == 6 || DateTime.Now.Month == 9 || DateTime.Now.Month == 12)
{
if (DateTime.Now.Day == DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month) &&
model.Time.AddMonths(guestProBaseInfos[0].GuestMonth) < Convert.ToDateTime(nextYear + "-" + nextMonth + "-01"))
{
model.MyGuestPro = 2;
}
}
}
string update = GuestTrackSer.AddToString(model);
string updateSql = string.Format("if not exists(select id from TB_GuestTrack where guestId='{0}' and yearNo='{1}' and QuartNo='{2}')begin {3} end else begin {4} end",
model.GuestId, nextYear.ToString(), nextZhangQi.ToString(), update, secondUpdate);
objCommand.CommandText = updateSql;
objCommand.ExecuteNonQuery();
}
tan.Commit();
ServiceAppSetting.LoggerHander.Invoke(string.Format("执行成功! - {0}", DateTime.Now), "");
}
catch (Exception)
{
tan.Rollback();
ServiceAppSetting.LoggerHander.Invoke(string.Format("账期执行失败! - {0}", DateTime.Now), "Error");
}
}
}
////增加金蝶发票处理工具
//var kISModel = new KISService().GetKIS();
//var invoiceDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + kISModel.InvoiceDate);
//var payableDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + kISModel.PayableDate);
//if (DateTime.Now.Hour == invoiceDate.Hour && DateTime.Now.Minute == invoiceDate.Minute)
//{
// var invoiceJob = new Thread(delegate ()
// {
// try
// {
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-开始! - {0}", DateTime.Now), "");
// var kisServer = new KISService();
// var invoicesList = kisServer.GetInvoiceErrorInfo(kISModel.AccountName);
// if (invoicesList.Count > 0)
// {
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-存在重复发票号数据，JOB 停止！", DateTime.Now), "");
// return;
// }
// //获取选中的数据库连接
// string newConn = KISService.KISDBConn.Replace("AcctCtl", kISModel.AccountName);
// KISService.ExeCommand("Invoice_Proc", newConn, Convert.ToDateTime(kISModel.InvoiceFrom), Convert.ToDateTime(kISModel.InvoiceTo));
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-结束! - {0}", DateTime.Now), "");
// }
// catch (Exception ex)
// {
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Invoice_Proc-结束! - {0}，执行异常，请检查！错误信息:{1}", DateTime.Now, ex.Message), "");
// }
// })
// { IsBackground = true };
// invoiceJob.Start();
//}
//if (DateTime.Now.Hour == payableDate.Hour && DateTime.Now.Minute == payableDate.Minute)
//{
// var payableJob = new Thread(delegate ()
// {
// try
// {
// var kisServer = new KISService();
// var payableList = kisServer.GetPayaleErrorInfo(kISModel.AccountName);
// if (payableList.Count > 0)
// {
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-存在重复发票号数据，JOB 停止！", DateTime.Now), "");
// return;
// }
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-开始! - {0}", DateTime.Now), "");
// //获取选中的数据库连接
// string newConn = KISService.KISDBConn.Replace("AcctCtl", kISModel.AccountName);
// KISService.ExeCommand("Payable_Proc", newConn, Convert.ToDateTime(kISModel.PayableFrom), Convert.ToDateTime(kISModel.PayableTo));
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-结束! - {0}", DateTime.Now), "");
// }
// catch (Exception ex)
// {
// ServiceAppSetting.LoggerHander.Invoke(string.Format("Payable_Proc-结束! - {0}，执行异常，请检查！错误信息:{1}", DateTime.Now, ex.Message), "");
// }
// })
// { IsBackground = true };
// payableJob.Start();
//}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DateTime fromDate = Convert.ToDateTime("2000-1-1");
DateTime toDate = Convert.ToDateTime("2036-1-1");
using (SqlConnection conn = DBHelp.getConn())
{
conn.Open();
var selectInfo = string.Format(@"select GoodId from TB_Good WHERE GoodStatus<>'不通过' and goodNo='12218'");
SqlCommand objCommand = conn.CreateCommand();
objCommand.CommandText = selectInfo;
List <int> goodIdList = new List <int>();
using (var reader = objCommand.ExecuteReader())
{
while (reader.Read())
{
goodIdList.Add(Convert.ToInt32(reader["GoodId"]));
}
reader.Close();
}
foreach (var goodId in goodIdList)
{
//查找进销存的所有信息
List <Pro_JSXDetailInfo> pOOrderList = this.jxcDetailSer.GetListArray(1, goodId, fromDate, toDate);
pOOrderList.Sort(delegate(Pro_JSXDetailInfo a, Pro_JSXDetailInfo b) { return(a.RuTime.CompareTo(b.RuTime)); });
jxcDetailSer.ReSetPro_JSXDetailInfo(pOOrderList);
decimal iniNum = 0;
for (int i = 0; i < pOOrderList.Count; i++)
{
var model = pOOrderList[i];
if (i == 0)
{
iniNum = model.GoodInNum - model.GoodOutNum;
//i++;
}
else
{
iniNum = iniNum + model.GoodInNum - model.GoodOutNum;
}
pOOrderList[i].GoodResultNum = iniNum;
}
//计算
Dictionary <int, decimal> inUpdatePrice = new Dictionary <int, decimal>();
Dictionary <int, decimal> outUpdatePrice = new Dictionary <int, decimal>();
Dictionary <int, decimal> sell_inUpdatePrice = new Dictionary <int, decimal>();
Dictionary <int, decimal> sell_outUpdatePrice = new Dictionary <int, decimal>();
Decimal avgPrice = 0;
int startIndex = 0;
foreach (var jxc in pOOrderList)
{
if (jxc.GoodResultNum != 0)
{
if (jxc.ProNo == "20120096")
{
}
decimal topNum = startIndex > 1 ? pOOrderList[startIndex - 1].GoodResultNum : 0;
if (jxc.TypeName == "采购入库")
{
avgPrice = (jxc.GoodInNum * jxc.avgHousePrice + avgPrice * topNum) / jxc.GoodResultNum;
inUpdatePrice.Add(jxc.Ids, avgPrice);
}
if (jxc.TypeName == "销售退货")
{
avgPrice = (jxc.GoodInNum * jxc.avgHousePrice + avgPrice * topNum) / jxc.GoodResultNum;
sell_outUpdatePrice.Add(jxc.Ids, avgPrice);
}
if (jxc.TypeName == "采购退货")
{
avgPrice = (avgPrice * topNum - jxc.GoodOutNum * jxc.avgHousePrice) / jxc.GoodResultNum;
outUpdatePrice.Add(jxc.Ids, avgPrice);
}
if (jxc.TypeName == "销售出库")
{
avgPrice = (avgPrice * topNum - jxc.GoodOutNum * jxc.avgHousePrice) / jxc.GoodResultNum;
sell_inUpdatePrice.Add(jxc.Ids, avgPrice);
}
}
else
{
avgPrice = 0;
}
startIndex++;
}
StringBuilder sbSql = new StringBuilder();
foreach (var m in inUpdatePrice)
{
sbSql.AppendFormat("update CAI_OrderInHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value);
}
foreach (var m in outUpdatePrice)
{
sbSql.AppendFormat("update CAI_OrderOutHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value);
}
foreach (var m in sell_inUpdatePrice)
{
sbSql.AppendFormat("update Sell_OrderOutHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value);
}
foreach (var m in sell_outUpdatePrice)
{
sbSql.AppendFormat("update Sell_OrderInHouses set TempHousePrice={1} where Ids={0}; ", m.Key, m.Value);
}
if (!string.IsNullOrEmpty(sbSql.ToString()))
{
objCommand.CommandText = sbSql.ToString();
objCommand.ExecuteNonQuery();
}
}
}
}
}