public static SQLiteDataReader getUserAllMsg(string date1, string date2)
{
string sql = string.Format(@"select DISTINCT
UserID,
UserName,
PayType,
Price,
FileCount,
`活动折扣`,
`附加费用`,
`起步价`,
`配送费`,
Time,
Aera,
State,
`Directory`,
LoadTime,
FinishTime,
Note,
Phone,
FileName,
VerForm,
FileSize,
Count from 订单信息 where FinishTime > '{0}' and FinishTime < '{1}' ", date1, date2);
return(SQLiteHelper.ExecuteDataReader(sql));
}
public static List <string> GetAddrList()
{
string sql2 = string.Format(@" SELECT * FROM 配送管理表 ");
SQLiteDataReader msdr2 = SQLiteHelper.ExecuteDataReader(sql2);
List <string> timelist = new List <string>();
while (msdr2.Read())
{
if (msdr2[1].ToString().Contains("地址"))
{
if (msdr2[2] != null && msdr2[2].ToString() != "")
{
timelist.Add(msdr2[2].ToString());
}
if (msdr2[3] != null && msdr2[3].ToString() != "")
{
timelist.Add(msdr2[3].ToString());
}
if (msdr2[4] != null && msdr2[4].ToString() != "")
{
timelist.Add(msdr2[4].ToString());
}
if (msdr2[5] != null && msdr2[5].ToString() != "")
{
timelist.Add(msdr2[5].ToString());
}
}
}
msdr2.Close();
return(timelist);
}
public static SQLiteDataReader getUserMsg(string username, string userid)
{
string sql = string.Format(@"select DISTINCT
UserID,
UserName,
PayType,
Price,
FileCount,
活动折扣,
附加费用,
起步价,
配送费,
Time,
Aera,
State,
Directory,
LoadTime,
FinishTime,
Note,
Phone,
FileName,
VerForm,
FileSize,
Count from 订单信息 where UserName = '******' and UserID = '{1}' ", username, userid);
return(SQLiteHelper.ExecuteDataReader(sql));
}
public static float GetPriceSum(UserDB userdb)
{
SQLiteDataReader msdr = null;
try
{
string sql1 = string.Format(@"SELECT DISTINCT UserID,Price FROM 订单信息 where UserName = '******' ", userdb.userName);
float f = 0;
msdr = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr.Read())
{
if (msdr[1] == null || msdr[1] == DBNull.Value || msdr[1].ToString() == "")
{
f += 0;
}
else
{
f += Convert.ToSingle(msdr[1]);
}
}
msdr.Close();
return(f);
}
catch (Exception ex)
{
if (msdr != null)
{
msdr.Close();
}
//object b =msdr[1];
MessageBox.Show(ex.Message);
return(0);
}
}
public static void getPrice()
{
lock (PriceList)
{
PriceList.Clear();
Dictionary <string, string> dic = new Dictionary <string, string>();
string sql = string.Format(@" SELECT 纸张类型, 黑白双面打印, 黑白单面打印,彩色打印 FROM 价格管理 ");
SQLiteDataReader msdr1 = SQLiteHelper.ExecuteDataReader(sql);
while (msdr1.Read())
{
PriceList.Add(new PriceClass()
{
ColorVerForm = "黑白正反", PaperType = msdr1[0].ToString(), price = msdr1[1].ToString()
});
PriceList.Add(new PriceClass()
{
ColorVerForm = "黑白单面", PaperType = msdr1[0].ToString(), price = msdr1[2].ToString()
});
PriceList.Add(new PriceClass()
{
ColorVerForm = "彩色单面", PaperType = msdr1[0].ToString(), price = msdr1[3].ToString()
});
}
msdr1.Close();
}
}
public static List <FileMsg> selectTmpFileMsg(string userid)
{
try
{
List <FileMsg> FileMsgList = new List <FileMsg>();
string sql1 = string.Format(@" select DISTINCT
UserID,
FullName,
FileName,
Count,
state,
LoadTime,
FinishTime,
DeleteTime,
VerForm,
PrintColor,
PaperType,
Printer,
PageCount,
Price,
isNormalFile,
isRemove,
InitPrice,
FileSize from tmpfilemsg where UserID = '{0}' ", userid);
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr.Read())
{
FileMsg fileMsg = new FileMsg();
fileMsg.UserID = msdr["UserID"] == DBNull.Value ? "" : msdr["UserID"].ToString();
fileMsg.FileName = msdr["FileName"] == DBNull.Value ? "" : msdr["FileName"].ToString();
fileMsg.FullName = msdr["FullName"] == DBNull.Value ? "" : msdr["FullName"].ToString();
fileMsg.Count = msdr["Count"] == DBNull.Value ? "" : msdr["Count"].ToString();
fileMsg.VerForm = msdr["VerForm"] == DBNull.Value ? "" : msdr["VerForm"].ToString();
fileMsg.PrintColor = msdr["PrintColor"] == DBNull.Value ? "" : msdr["PrintColor"].ToString();
fileMsg.PaperType = msdr["PaperType"] == DBNull.Value ? "" : msdr["PaperType"].ToString();
fileMsg.Printer = msdr["Printer"] == DBNull.Value ? "" : msdr["Printer"].ToString();
fileMsg.PageCount = msdr["PageCount"] == DBNull.Value ? "" : msdr["PageCount"].ToString();
fileMsg.Price = msdr["Price"] == DBNull.Value ? "" : msdr["Price"].ToString();
fileMsg.InitPrice = msdr["InitPrice"] == DBNull.Value ? "" : msdr["InitPrice"].ToString();
fileMsg.LoadTime = msdr["LoadTime"] == DBNull.Value ? "" : msdr["LoadTime"].ToString();
fileMsg.FinishTime = msdr["FinishTime"] == DBNull.Value ? "" : msdr["FinishTime"].ToString();
fileMsg.DeleteTime = msdr["DeleteTime"] == DBNull.Value ? "" : msdr["DeleteTime"].ToString();
fileMsg.state = msdr["State"] == DBNull.Value ? "" : msdr["State"].ToString();
string s = msdr["isRemove"] == DBNull.Value ? "0" : msdr["isRemove"].ToString();
fileMsg.isRemove = (s == "0" ? false : true);
s = msdr["isNormalFile"] == DBNull.Value ? "0" : msdr["isNormalFile"].ToString();
fileMsg.isNormalFile = (s == "0" ? false : true);
fileMsg.FileSize = (double)msdr["FileSize"];
FileMsgList.Add(fileMsg);
}
msdr.Close();
return(FileMsgList);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
private void RefreshDgv()
{
flag = false;
dataGridView1.Rows.Clear();
string sql = @"select a.纸张类型 ,a.`彩色打印`,a.`黑白单面打印`,a.`黑白双面打印`,b.`黑白可用`,b.`彩色可用`,b.纸张名称 from 价格管理 a left join 纸张管理 b on a.纸张类型 = b.纸张类型";
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql);
while (msdr.Read())
{
string Name = msdr[0].ToString();
string disName = msdr[6] == DBNull.Value?"":msdr[6].ToString();
string 彩色打印 = @"\";
string 黑白单面打印 = @"\";
string 黑白双面打印 = @"\";
int 黑白可用 = Convert.ToInt32(msdr[4].ToString());
int 彩色可用 = Convert.ToInt32(msdr[5].ToString());
if (彩色可用 == 1)
{
彩色打印 = Convert.ToSingle(msdr[1]).ToString("F2");
}
if (黑白可用 == 1)
{
黑白单面打印 = Convert.ToSingle(msdr[2]).ToString("F2");
黑白双面打印 = Convert.ToSingle(msdr[3]).ToString("F2");
}
dataGridView1.Rows.Add(Name, disName, 彩色打印, 黑白单面打印, 黑白双面打印);
}
msdr.Close();
string sql1 = "select * from 附加项表";
SQLiteDataReader msdr1 = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr1.Read())
{
switch (msdr1[1].ToString())
{
case "活动折扣":
txt_活动折扣.Text = msdr1[2].ToString();
break;
case "附加费用":
txt_附加费用.Text = msdr1[2].ToString();
break;
case "起步价":
txt_起步价.Text = msdr1[2].ToString();
break;
case "配送费":
txt_配送费.Text = msdr1[2].ToString();
break;
}
}
flag = true;
}
public static string GetPrinter(string paperColor)
{
string sql4 = string.Format(@" SELECT * FROM 机器管理 where {0}默认 = 1 ", paperColor);
SQLiteDataReader msdr4 = SQLiteHelper.ExecuteDataReader(sql4);
string printer = "----";
while (msdr4.Read())
{
printer = msdr4[1].ToString();
}
msdr4.Close();
return(printer);
}
public static List <string> GetPrinterList(string paperColor)
{
string sql4 = string.Format(@" SELECT * FROM 机器管理 where {0}可用 = 1 ", paperColor);
SQLiteDataReader msdr4 = SQLiteHelper.ExecuteDataReader(sql4);
List <string> printerList = new List <string>();
while (msdr4.Read())
{
printerList.Add(msdr4[1].ToString());
}
msdr4.Close();
return(printerList);
}
private void RefreshDgv()
{
dataGridView1.Rows.Clear();
string sql = " select * from 纸张管理 ";
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql);
bool flag1 = false;
bool flag2 = false;
while (msdr.Read())
{
string Name = msdr[1].ToString();
int 黑白可用 = Convert.ToInt32(msdr[2].ToString());
int 彩色可用 = Convert.ToInt32(msdr[3].ToString());
int defaulltSelect = Convert.ToInt32(msdr[4].ToString());
int defaulltSelect2 = Convert.ToInt32(msdr[5].ToString());
string papername = (msdr[6] == DBNull.Value?"": msdr[6].ToString());
dataGridView1.Rows.Add(Name, papername, 黑白可用 == 1 ? "可用" : "不可用", 彩色可用 == 1 ? "可用" : "不可用");
if (defaulltSelect == 1)
{
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["黑白可用"].Style.BackColor = Color.Green;
flag1 = true;
}
if (defaulltSelect2 == 1)
{
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["彩色可用"].Style.BackColor = Color.Green;
flag2 = true;
}
}
if (!flag1)
{
if (dataGridView1.Rows.Count > 0)
{
string[] columnName = new string[] { "黑白默认", "彩色默认" };
string printer = dataGridView1.Rows[0].Cells[0].Value.ToString();
string sql1 = string.Format(" update 纸张管理 set {0} = {1} where 纸张类型 = '{2}'", columnName[0], "1", printer);
SQLiteHelper.ExecuteNonQuery(sql1);
dataGridView1.Rows[0].Cells["黑白可用"].Style.BackColor = Color.Green;
}
}
if (!flag2)
{
if (dataGridView1.Rows.Count > 0)
{
string[] columnName = new string[] { "黑白默认", "彩色默认" };
string printer = dataGridView1.Rows[0].Cells[0].Value.ToString();
string sql1 = string.Format(" update 纸张管理 set {0} = {1} where 纸张类型 = '{2}'", columnName[1], "1", printer);
SQLiteHelper.ExecuteNonQuery(sql1);
dataGridView1.Rows[0].Cells["彩色可用"].Style.BackColor = Color.Green;
}
}
}
public static string GetDefaultPaper(string paperColor)
{
string sql3 = string.Format(@" SELECT * FROM 纸张管理 where {0}默认 = 1 ", paperColor);
#region 纸张信息
SQLiteDataReader msdr3 = SQLiteHelper.ExecuteDataReader(sql3);
string paper = "----";
while (msdr3.Read())
{
string s = (msdr3[6] == null || msdr3[6] == DBNull.Value) ? " " : msdr3[6].ToString();
paper = msdr3[1].ToString() + ":" + s;
}
msdr3.Close();
return(paper);
#endregion
}
public static List <string> GetPaperList(string paperColor)
{
string sql3 = string.Format(@" SELECT * FROM 纸张管理 where {0}可用 = 1 ", paperColor);
#region 纸张信息
SQLiteDataReader msdr3 = SQLiteHelper.ExecuteDataReader(sql3);
List <string> paperList = new List <string>();
while (msdr3.Read())
{
string s = (msdr3[6] == null || msdr3[6] == DBNull.Value) ? " " : msdr3[6].ToString();
paperList.Add(msdr3[1].ToString() + ":" + s);
}
msdr3.Close();
return(paperList);
#endregion
}
public static SQLiteDataReader GetAllUserMsg()
{
try
{
string sql1 = string.Format(@"SELECT DISTINCT
UserName,
LastAddr,
Phone,
LastTime,
Note FROM UserTable ");
return(SQLiteHelper.ExecuteDataReader(sql1));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
private void 配送管理_Load(object sender, EventArgs e)
{
string sql = " select * from 配送管理表 ";
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql);
while (msdr.Read())
{
if (msdr[1].ToString().Contains("时间"))
{
dgv_time.Rows.Add(msdr[2] == null ? "" : msdr[2].ToString(), msdr[3] == null ? "" : msdr[3].ToString()
, msdr[4] == null ? "" : msdr[4].ToString(), msdr[5] == null ? "" : msdr[5].ToString());
}
else if (msdr[1].ToString().Contains("地址"))
{
dgv_addr.Rows.Add(msdr[2] == null ? "" : msdr[2].ToString(), msdr[3] == null ? "" : msdr[3].ToString()
, msdr[4] == null ? "" : msdr[4].ToString(), msdr[5] == null ? "" : msdr[5].ToString());
}
}
flag = true;
}
public static SQLiteDataReader getFileMsg(string userID)
{
string sql = string.Format(@"select DISTINCT
UserID,
UserName,
FileName,
Count,
VerForm,
PrinterColor,
PaperType,
Printer,
PageCount,
PaperPrice,
Price,
State,
LoadTime,
FinishTime,
FileSize,
ModifyTime from 文件信息 where UserID = '{0}' ", userID);
return(SQLiteHelper.ExecuteDataReader(sql));
}
public static Dictionary <string, string> getMsg()
{
Dictionary <string, string> rowMsg = new Dictionary <string, string>();
string sql1 = string.Format(@" SELECT * FROM 附加项表 ");
SQLiteDataReader msdr1 = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr1.Read())
{
string key = msdr1[1].ToString();
string value = msdr1[2].ToString();
if (rowMsg.ContainsKey(key))
{
rowMsg[key] = value;
}
else
{
rowMsg.Add(key, value);
}
}
msdr1.Close();
return(rowMsg);
}
public static UserDB selectUserMsg(string UserName)
{
try
{
UserDB userDB = new UserDB();
string sql1 = string.Format(@"select * from usertable where UserName = '******' ORDER BY usertable.LastTime DESC ", UserName);
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr.Read())
{
userDB.userName = (msdr["UserName"] == DBNull.Value || msdr["UserName"] == null)?"": msdr["UserName"].ToString();
userDB.phone = (msdr["Phone"] == DBNull.Value || msdr["Phone"] == null) ? "" : msdr["Phone"].ToString();
userDB.lastAddr = (msdr["LastAddr"] == DBNull.Value || msdr["LastAddr"] == null) ? "" : msdr["LastAddr"].ToString();
break;
}
msdr.Close();
return(userDB);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(new UserDB());
}
}
public static List <DateTime> GetTimeList()
{
string sql2 = string.Format(@" SELECT * FROM 配送管理表 ");
SQLiteDataReader msdr2 = SQLiteHelper.ExecuteDataReader(sql2);
List <DateTime> timelist = new List <DateTime>();
while (msdr2.Read())
{
if (msdr2[1].ToString().Contains("时间"))
{
DateTime tmpDate = DateTime.Parse(frm_Main.CurrentDate);
if (msdr2[2] != null && msdr2[2].ToString() != "")
{
DateTime d1 = DateTime.Parse(tmpDate.Date.ToString().Split(' ')[0] + " " + msdr2[2].ToString());
timelist.Add(d1);
}
if (msdr2[3] != null && msdr2[3].ToString() != "")
{
DateTime d1 = DateTime.Parse(tmpDate.Date.ToString().Split(' ')[0] + " " + msdr2[3].ToString());
timelist.Add(d1);
}
if (msdr2[4] != null && msdr2[4].ToString() != "")
{
DateTime d1 = DateTime.Parse(tmpDate.Date.ToString().Split(' ')[0] + " " + msdr2[4].ToString());
timelist.Add(d1);
}
if (msdr2[5] != null && msdr2[5].ToString() != "")
{
DateTime d1 = DateTime.Parse(tmpDate.Date.ToString().Split(' ')[0] + " " + msdr2[5].ToString());
timelist.Add(d1);
}
}
}
msdr2.Close();
timelist.Sort();
return(timelist);
}
public static List <GroupMsg> selectTmpUserMsg()
{
try
{
List <GroupMsg> GroupMsgList = new List <GroupMsg>();
string s1 = DateTime.Now.ToString("yyyy-MM-dd");
string s2 = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
string s3 = DateTime.Now.AddDays(-2).ToString("yyyy-MM-dd");
string s4 = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd");
string s5 = DateTime.Now.AddDays(-4).ToString("yyyy-MM-dd");
string sql1 = string.Format(@" select DISTINCT
UserID,
FileName,
FullName,
GroupName,
FileDirectory,
Count,
VerForm,
PrintColor,
PaperType,
Printer,
PageCount,
InitPageCount,
Price,
InitPrice,
Time,
Area,
Phone,
SetTime,
LoadTime,
FinishTime,
DeleteTime,
CreateTime,
PayType,
State,
isRemove,
isManual,
Note,
FileSize from tmpusermsg where LoadTime like '{0}%' or LoadTime like '{1}%' or LoadTime like '{2}%' or LoadTime like '{3}%' or LoadTime like '{4}%' ", s1, s2, s3, s4, s5);
SQLiteDataReader msdr = SQLiteHelper.ExecuteDataReader(sql1);
while (msdr.Read())
{
GroupMsg groupMsg = new GroupMsg();
groupMsg.UserID = msdr["UserID"] == DBNull.Value ? "" : msdr["UserID"].ToString();
groupMsg.FileName = msdr["FileName"] == DBNull.Value ? "" : msdr["FileName"].ToString();
groupMsg.FullName = msdr["FullName"] == DBNull.Value ? "" : msdr["FullName"].ToString();
groupMsg.GroupName = msdr["GroupName"] == DBNull.Value ? "" : msdr["GroupName"].ToString();
groupMsg.FileDirectory = msdr["FileDirectory"] == DBNull.Value ? "" : msdr["FileDirectory"].ToString();
groupMsg.Count = msdr["Count"] == DBNull.Value ? "" : msdr["Count"].ToString();
groupMsg.VerForm = msdr["VerForm"] == DBNull.Value ? "" : msdr["VerForm"].ToString();
groupMsg.PrintColor = msdr["PrintColor"] == DBNull.Value ? "" : msdr["PrintColor"].ToString();
groupMsg.PaperType = msdr["PaperType"] == DBNull.Value ? "" : msdr["PaperType"].ToString();
groupMsg.Printer = msdr["Printer"] == DBNull.Value ? "" : msdr["Printer"].ToString();
groupMsg.PageCount = msdr["PageCount"] == DBNull.Value ? "" : msdr["PageCount"].ToString();
groupMsg.InitPageCount = msdr["InitPageCount"] == DBNull.Value ? "" : msdr["InitPageCount"].ToString();
groupMsg.Price = msdr["Price"] == DBNull.Value ? "" : msdr["Price"].ToString();
groupMsg.InitPrice = msdr["InitPrice"] == DBNull.Value ? "" : msdr["InitPrice"].ToString();
groupMsg.Time = msdr["Time"] == DBNull.Value ? "" : msdr["Time"].ToString();
groupMsg.Area = msdr["Area"] == DBNull.Value ? "" : msdr["Area"].ToString();
groupMsg.Phone = msdr["Phone"] == DBNull.Value ? "" : msdr["Phone"].ToString();
groupMsg.SetTime = msdr["SetTime"] == DBNull.Value ? "" : msdr["SetTime"].ToString();
groupMsg.LoadTime = msdr["LoadTime"] == DBNull.Value ? "" : msdr["LoadTime"].ToString();
groupMsg.FinishTime = msdr["FinishTime"] == DBNull.Value ? "" : msdr["FinishTime"].ToString();
groupMsg.DeleteTime = msdr["DeleteTime"] == DBNull.Value ? "" : msdr["DeleteTime"].ToString();
groupMsg.CreateTime = msdr["CreateTime"] == DBNull.Value ? "" : msdr["CreateTime"].ToString();
groupMsg.PayType = msdr["PayType"] == DBNull.Value ? "" : msdr["PayType"].ToString();
groupMsg.state = msdr["State"] == DBNull.Value ? "" : msdr["State"].ToString();
string s = msdr["isRemove"] == DBNull.Value ? "0": msdr["isRemove"].ToString();
groupMsg.isRemove = (s == "0" ? false: true);
s = msdr["isManual"] == DBNull.Value ? "0" : msdr["isManual"].ToString();
groupMsg.isManual = (s == "0" ? false : true);
groupMsg.Note = msdr["Note"] == DBNull.Value ? "" : msdr["Note"].ToString();
GroupMsgList.Add(groupMsg);
}
msdr.Close();
return(GroupMsgList);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}