private void button1_Click(object sender, EventArgs e)
{
prn.execute("delete from printing");
#region add table columns name
string qry = "INSERT INTO Printing(";
for (int j = 0; j < maindt.Columns.Count; j++)
{
if (j < 100)
{
if (j == 0)
{
qry += "T" + (j + 1);
}
else
{
qry += "," + "T" + (j + 1);
}
}
else
if (j == 0)
{
qry += "P" + (j + 1);
}
else
{
qry += "," + "P" + (j + 1);
}
}
qry += ")VALUES(";
string[] columnNames = maindt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
for (int k = 0; k < columnNames.Length; k++)
{
if (k == 0)
{
qry += "'" + columnNames[k] + "'";
}
else
{
qry += ",'" + columnNames[k] + "'";
}
}
qry += ")";
prn.execute(qry);
#endregion
#region add table data
for (int i = 0; i < maindt.Rows.Count; i++)
{
qry = "INSERT INTO Printing(";
for (int j = 0; j < maindt.Columns.Count; j++)
{
if (j < 100)
{
if (j == 0)
{
qry += "T" + (j + 1);
}
else
{
qry += "," + "T" + (j + 1);
}
}
else
if (j == 0)
{
qry += "P" + (j + 1);
}
else
{
qry += "," + "P" + (j + 1);
}
}
qry += ")VALUES(";
for (int j = 0; j < maindt.Columns.Count; j++)
{
if (j == 0)
{
qry += "'" + maindt.Rows[i][j].ToString() + "'";
}
else
{
qry += ",'" + maindt.Rows[i][j].ToString() + "'";
}
}
qry += ")";
prn.execute(qry);
}
#endregion
Print popup = new Print("ItemWiseSaleOrder");
popup.ShowDialog();
popup.Dispose();
}
private void btnsearch_Click(object sender, EventArgs e)
{
try
{
DialogResult dr1 = MessageBox.Show("Do you want to Print?", "POS", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr1 == DialogResult.Yes)
{
if (LVbill.Items.Count > 0)
{
prn.execute("delete from printing");
DataTable dt1 = sql.getdataset("select * from company WHERE isactive=1");
string BillListDate = "Bill List From " + DTPFrom.Text + " To " + DTPTo.Text;
// string date = "", type = "", Account = "", drAmount = "", crAmount="",balance="";
for (int i = 0; i < LVbill.Items.Count; i++)
{
string a = "", b = "", c = "", d = "", s = "", f = "", g = "", h = "", q = "", j = "", k = "";
a = LVbill.Items[i].SubItems[0].Text;
b = LVbill.Items[i].SubItems[1].Text;
c = LVbill.Items[i].SubItems[2].Text;
d = LVbill.Items[i].SubItems[3].Text;
s = LVbill.Items[i].SubItems[4].Text;
f = LVbill.Items[i].SubItems[5].Text;
g = LVbill.Items[i].SubItems[6].Text;
h = LVbill.Items[i].SubItems[7].Text;
q = LVbill.Items[i].SubItems[8].Text;
j = LVbill.Items[i].SubItems[9].Text;
k = LVbill.Items[i].SubItems[10].Text;
if (f == "" || f == null)
{
f = "0";
}
if (g == "" || g == null)
{
g = "0";
}
if (h == "" || h == null)
{
h = "0";
}
if (q == "" || q == null)
{
q = "0";
}
if (j == "" || j == null)
{
j = "0";
}
if (k == "" || k == null)
{
k = "0";
}
qty = qty + Convert.ToDouble(f);
tbill = tbill + Convert.ToDouble(g);
tcash = tcash + Convert.ToDouble(h);
tother = tother + Convert.ToDouble(q);
tnet = tnet + Convert.ToDouble(j);
tchanges = tchanges + Convert.ToDouble(k);
string qry = "INSERT INTO Printing(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T31,T32,T33,T34)VALUES";
qry += "('" + dt1.Rows[0][0].ToString() + "','" + dt1.Rows[0][1].ToString() + "','" + dt1.Rows[0][2].ToString() + "','" + dt1.Rows[0][3].ToString() + "','" + dt1.Rows[0][4].ToString() + "','" + dt1.Rows[0][5].ToString() + "','" + dt1.Rows[0][6].ToString() + "','" + dt1.Rows[0][7].ToString() + "','" + dt1.Rows[0][8].ToString() + "','" + dt1.Rows[0][9].ToString() + "','" + dt1.Rows[0][10].ToString() + "','" + dt1.Rows[0][11].ToString() + "','" + dt1.Rows[0][12].ToString() + "','" + dt1.Rows[0][13].ToString() + "','" + DTPFrom.Text + "','" + DTPTo.Text + "','" + a + "','" + b + "','" + c + "','" + d + "','" + s + "','" + f + "','" + g + "','" + h + "','" + q + "','" + j + "','" + k + "','" + qty + "','" + tbill + "','" + tcash + "','" + tother + "','" + tnet + "','" + tchanges + "','" + BillListDate + "')";
prn.execute(qry);
}
string update = "UPDATE [Printing] SET [T28]='" + qty + "',[T29]='" + tbill + "',[T30]='" + tcash + "',[T31]='" + tother + "',[T32]='" + tnet + "',[T33]='" + tchanges + "'";
prn.execute(update);
string reportName = "PosBillListThermalPrint";
Print popup = new Print(reportName);
popup.ShowDialog();
popup.Dispose();
qty = 0; tbill = 0; tcash = 0; tother = 0; tnet = 0; tchanges = 0;
}
}
}
catch
{
}
}
private void btngenrpt_Click(object sender, EventArgs e)
{
try
{
DialogResult dr = MessageBox.Show("Do you want to Generate Report?", "ItemList", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
//cmd2 = new SqlCommand("select ItemName,sum(Qty) as totalqty, Rate, sum(qty)*rate as TotalSale from BillProductMaster group by itemname, rate order by totalqty desc", con);
//sda2 = new SqlDataAdapter(cmd2);
//DataTable dt2 = new DataTable();
//sda2.Fill(dt2);
cmd3 = new SqlCommand("select * from company WHERE isactive=1", con);
sda3 = new SqlDataAdapter(cmd3);
DataTable dt1 = new DataTable();
sda3.Fill(dt1);
cmd = new SqlCommand("select BillId,ItemName,sum(Qty) as totalqty, Rate, sum(qty)*rate as TotalSale from BillPOSProductMaster where isactive=1 and BillRunDate>='" + Convert.ToDateTime(DTPFrom.Text).ToString("MM-dd-yyyy") + "' and BillRunDate<='" + Convert.ToDateTime(DTPTo.Text).ToString("MM-dd-yyyy") + "' group by itemname, rate,BillId order by totalqty desc", con);
sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
con.Open();
// cmd1 = new SqlCommand("delete from printing", con);
// cmd1.ExecuteNonQuery();
prn.execute("delete from printing");
//for (int i = 0; i < dt2.Rows.Count; i++)
//{
// string qry = "INSERT INTO [dbo].[Printing]([T1],[T2],[T3],[T4],[T5])VALUES";
// qry += "('" + DateTime.Now.ToShortDateString() + "','" + dt2.Rows[i][0].ToString() + "','" + dt2.Rows[i][1].ToString() + "','" + dt2.Rows[i][2].ToString() + "','" + dt2.Rows[i][3].ToString() + "')";
// cmd = new SqlCommand(qry, con);
// cmd.ExecuteNonQuery();
//}
for (int i = 0; i < dt.Rows.Count; i++)
{
string qry = "INSERT INTO Printing(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22)VALUES";
qry += "('" + dt.Rows[i][0].ToString() + "','" + DateTime.Now.ToShortDateString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt1.Rows[0][0].ToString() + "','" + dt1.Rows[0][1].ToString() + "','" + dt1.Rows[0][2].ToString() + "','" + dt1.Rows[0][3].ToString() + "','" + dt1.Rows[0][4].ToString() + "','" + dt1.Rows[0][5].ToString() + "','" + dt1.Rows[0][6].ToString() + "','" + dt1.Rows[0][7].ToString() + "','" + dt1.Rows[0][8].ToString() + "','" + dt1.Rows[0][9].ToString() + "','" + dt1.Rows[0][10].ToString() + "','" + dt1.Rows[0][11].ToString() + "','" + dt1.Rows[0][12].ToString() + "','" + dt1.Rows[0][13].ToString() + "','" + DTPFrom.Text + "','" + DTPTo.Text + "')";
prn.execute(qry);
// cmd = new SqlCommand(qry, con);
// cmd.ExecuteNonQuery();
}
string reportName = "Pos Item List";
Print popup = new Print(reportName);
popup.ShowDialog();
popup.Dispose();
con.Close();
// POSItemListPrintReport frm = new POSItemListPrintReport();
// frm.StartPosition = FormStartPosition.CenterScreen;
// frm.Show();
}
else
{
MessageBox.Show("Please select another date if you want to generate another Report.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error:", ex.Message);
}
finally
{
con.Close();
}
}