private void LoadChartForItem() { string query = @"select I.ItemCode,I.ItemName +' OrderNo->'+ CONVERT(varchar(10),(D.OrderId)) as 'ItemName',Sum(D.Qty) as 'Counter' from ItemMaster I inner join OrderDetail D on D.ItemCode=I.ItemCode and D.OrderId in(select OrderId from OrderMain M where M.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"') group by I.ItemCode,I.ItemName,D.OrderId order by I.ItemCode,I.ItemName"; DataTable dt = new DataTable(); dt = DBClass.GetTableByQuery(query); ChartOrder.Update(); //if (dt.Rows.Count > 0) //{ ChartOrder.Series["Item"].XValueMember = dt.Columns[1].ColumnName; ChartOrder.Series["Item"].YValueMembers = dt.Columns[2].ColumnName; ChartOrder.DataSource = dt; ChartOrder.DataBind(); this.ChartOrder.Titles.Clear(); this.ChartOrder.Titles.Add("Item Chart for Order Booked"); ChartOrder.Series["Item"].ChartType = SeriesChartType.Doughnut; //chart1.Series["Item"]. ChartOrder.Series["Item"].IsValueShownAsLabel = true; //} }
private void DisplayView() { string Query = @"select o.Deliverydate,o.CustomerName,o.Place,i.ItemName,d.Qty from OrderMain o inner join OrderDetail d on o.OrderID=d.OrderId left join ItemMaster i on d.ItemCode=i.ItemCode where o.Deliverydate>=GETDATE()-1 order by o.Deliverydate"; DataTable dtview = new DataTable(); dtview = DBClass.GetTableByQuery(Query); dgvView.DataSource = null; dgvView.DataSource = dtview; }
private void LoadChartForMonthlyPayment() { DateTime d = monthCalendar1.SelectionRange.Start; string month = d.Month.ToString(); // string query = @"select Month(o.Deliverydate) as 'Order',sum(d.Qty*d.UnitPrice) as 'TotalAmt', // sum((d.Qty*d.UnitPrice)-o.Discount-o.Advance_Amt) as 'NetAmt',SUM(p.Paid_Amt) as 'PaidAmt' // from OrderMain o // inner join OrderDetail d on o.OrderID=d.OrderId // left join PaymentDetail p on o.OrderID=p.OrderId // where Month(o.Deliverydate)='" + month + @"' // group by Month(o.Deliverydate)"; string query = @"select '" + month + @"' as 'Month', (select sum(Qty*UnitPrice) from OrderDetail where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"')) as 'TotalAmt', ((select sum(Qty*UnitPrice) from OrderDetail where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"')) -(select SUM(M.Discount+M.Advance_Amt) from OrderMain M where month(Deliverydate)='" + month + @"')) as 'NetAmt', (select sum(Paid_Amt) from PaymentDetail where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"')) as 'PaidAmt'"; DataTable dt = new DataTable(); dt = DBClass.GetTableByQuery(query); //if (dt.Rows.Count > 0) //{ chartMonthlyPayment.DataSource = dt; chartMonthlyPayment.Series["NetAmt"].XValueMember = "Month"; chartMonthlyPayment.Series["NetAmt"].YValueMembers = "NetAmt"; chartMonthlyPayment.Series["PaidAmt"].XValueMember = "Month"; chartMonthlyPayment.Series["PaidAmt"].YValueMembers = "PaidAmt"; chartMonthlyPayment.DataBind(); this.chartMonthlyPayment.Titles.Clear(); this.chartMonthlyPayment.Titles.Add("Monthly Payment Chart"); // chart2.Series["NetAmt"].ChartType = SeriesChartType.Pie; chartMonthlyPayment.Series["NetAmt"].IsValueShownAsLabel = true; //chart2.Series["PaidAmt"].ChartType = SeriesChartType.Bar; chartMonthlyPayment.Series["PaidAmt"].IsValueShownAsLabel = true; //} }
private void DeleteRecord() { if (MessageBox.Show("Are you Sure to delete this record ? ", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes) { DataTable dt = DBClass.GetTableByQuery("Select count(*) from PaymentDetail where OrderId=" + Convert.ToInt16(txtOrderID.Text)); if (dt.Rows.Count > 0) { if (dt.Rows[0][0].ToString() != "0") { MessageBox.Show("Payment Detail Exist of this order,Can't be delete!"); return; } } DBClass.connection.Open(); _MainDTAdapter.DeleteCommand = new SqlCommand(@"Delete From OrderDetail where OrderID= @OrderID ", DBClass.connection); _MainDTAdapter.DeleteCommand.Parameters.AddWithValue("@OrderID", txtOrderID.Text); _MainDTAdapter.DeleteCommand.ExecuteNonQuery(); dsMain.Tables["OrderDetail"].Clear(); _MainDTAdapter.Fill(dsMain.Tables["OrderDetail"]); _MainAdapter.DeleteCommand = new SqlCommand(@"Delete From OrderMain where OrderID= @OrderID ", DBClass.connection); _MainAdapter.DeleteCommand.Parameters.AddWithValue("@OrderID", txtOrderID.Text); _MainAdapter.DeleteCommand.ExecuteNonQuery(); dsMain.Tables["OrderMain"].Clear(); _MainAdapter.Fill(dsMain.Tables["OrderMain"]); DBClass.connection.Close(); if (dsMain.Tables["OrderMain"].Rows.Count > 0) { FillOrders(dsMain.Tables["OrderMain"].Rows.Count - 1); } else { ClearRecord(); } DisplayView(); } }
private void LoadGrid() { string query = @"select o.CustomerName,o.OrderID,o.Deliverydate, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) as 'TotalAmt',o.Discount,o.Advance_Amt, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt as 'NetAmt', (select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID) as 'PaidAmount', 'Paid' as 'Select' from OrderMain o where (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt-(select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID)=0 and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"' union select o.CustomerName,o.OrderID,o.Deliverydate, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) as 'TotalAmt',o.Discount,o.Advance_Amt, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt as 'NetAmt', (select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID) as 'PaidAmount', 'Payment Due' as 'Select' from OrderMain o where (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt-(select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID)>0 and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"' union select o.CustomerName,o.OrderID,o.Deliverydate, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) as 'TotalAmt',o.Discount,o.Advance_Amt, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt as 'NetAmt', (select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID) as 'PaidAmount', 'InComplete' as 'Select' from OrderMain o where o.OrderId NOT IN (select OrderId from PaymentDetail) and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'"; DataTable dt = new DataTable(); dt = DBClass.GetTableByQuery(query); dataGridView1.DataSource = null; if (dt.Rows.Count > 0) { dataGridView1.DataSource = dt; dataGridView1.ReadOnly = true; } }
private void BindMonthCalendar() { string query = "select Deliverydate,YEAR(Deliverydate) as 'Year',MONTH(Deliverydate) as 'Month',DAY(Deliverydate) as 'Day' from OrderMain order by Deliverydate"; DataTable dt = new DataTable(); dt = DBClass.GetTableByQuery(query); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { DateTime DeliveryDate = new DateTime(int.Parse(row["Year"].ToString()), int.Parse(row["Month"].ToString()), int.Parse(row["Day"].ToString())); monthCalendar1.AddBoldedDate(DeliveryDate); monthCalendar1.UpdateBoldedDates(); } } monthCalendar1.TitleBackColor = System.Drawing.Color.Blue; monthCalendar1.TrailingForeColor = System.Drawing.Color.Red; monthCalendar1.TitleForeColor = System.Drawing.Color.Yellow; }
private void LoadChartForPayment() { string query = @"select o.OrderId, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) as 'TotalAmt', o.Discount,o.Advance_Amt, (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt as 'NetAmt', (select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID) as 'PaidAmount' from OrderMain o where o.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'"; DataTable dt = new DataTable(); dt = DBClass.GetTableByQuery(query); //if (dt.Rows.Count > 0) //{ chartPayment.DataSource = dt; chartPayment.Series["NetAmt"].XValueMember = "OrderId"; chartPayment.Series["NetAmt"].YValueMembers = "NetAmt"; chartPayment.Series["PaidAmt"].XValueMember = "OrderId"; chartPayment.Series["PaidAmt"].YValueMembers = "PaidAmount"; chartPayment.DataBind(); this.chartPayment.Titles.Clear(); this.chartPayment.Titles.Add("Payment Chart for Order Booked"); // chart2.Series["NetAmt"].ChartType = SeriesChartType.Pie; chartPayment.Series["NetAmt"].IsValueShownAsLabel = true; //chart2.Series["PaidAmt"].ChartType = SeriesChartType.Bar; chartPayment.Series["PaidAmt"].IsValueShownAsLabel = true; //} }
public DataTable ConstructSqlTable(string SelectColumns, string TableName, string JoinTables = "", string ItemFieldName = "", string ItemFieldValue = "", string CustomerFieldName = "", string CustomerFieldValue = "", string PlaceFieldName = "", string PlaceFieldValue = "", string OrderIdFieldName = "", string OrderIdFromFieldValue = "", string OrderIdToFieldValue = "", string DeliveryDateFieldName = "", string DeliveryFromDateFieldValue = "", string DeliveryToDateFieldValue = "", string OrderDateFieldName = "", string OrderFromDateFieldValue = "", string OrderToDateFieldValue = "", string PaymentdateFieldName = "", string PaymentFromdateFieldValue = "", string PaymentTodateFieldValue = "", string GroupBy = "", string OrderBy = "") { string strSql; strSql = @"select " + SelectColumns + " From " + TableName; if (JoinTables != "") { strSql = strSql + " " + JoinTables; } strSql = strSql + " Where 1=1"; string strCriteria = ""; if (ItemFieldName != "" && ItemFieldValue != "") { strCriteria = strCriteria + " And " + ItemFieldName + "='" + ItemFieldValue + "'"; } if (CustomerFieldName != "" && CustomerFieldValue != "") { strCriteria = strCriteria + " And " + CustomerFieldName + "='" + CustomerFieldValue + "'"; } if (PlaceFieldName != "" && PlaceFieldValue != "") { strCriteria = strCriteria + " And " + PlaceFieldName + "='" + PlaceFieldValue + "'"; } if (OrderIdFieldName != "" && OrderIdFromFieldValue != "") { strCriteria = strCriteria + " And " + OrderIdFieldName + ">='" + OrderIdFromFieldValue + "'"; } if (OrderIdFieldName != "" && OrderIdToFieldValue != "") { strCriteria = strCriteria + " And " + OrderIdFieldName + "<='" + OrderIdToFieldValue + "'"; } if (DeliveryDateFieldName != "" && DeliveryFromDateFieldValue != "") { strCriteria = strCriteria + " And " + DeliveryDateFieldName + ">='" + DeliveryFromDateFieldValue + "'"; } if (DeliveryDateFieldName != "" && DeliveryToDateFieldValue != "") { strCriteria = strCriteria + " And " + DeliveryDateFieldName + "<='" + DeliveryToDateFieldValue + "'"; } if (OrderDateFieldName != "" && OrderFromDateFieldValue != "") { strCriteria = strCriteria + " And " + OrderDateFieldName + ">='" + OrderFromDateFieldValue + "'"; } if (OrderDateFieldName != "" && OrderToDateFieldValue != "") { strCriteria = strCriteria + " And " + OrderDateFieldName + "<='" + OrderToDateFieldValue + "'"; } if (PaymentdateFieldName != "" && PaymentFromdateFieldValue != "") { strCriteria = strCriteria + " And " + PaymentdateFieldName + ">='" + PaymentFromdateFieldValue + "'"; } if (PaymentdateFieldName != "" && PaymentTodateFieldValue != "") { strCriteria = strCriteria + " And " + PaymentdateFieldName + "<='" + PaymentTodateFieldValue + "'"; } strSql = strSql + strCriteria; if (GroupBy != "") { strSql = strSql + " " + GroupBy; } if (OrderBy != "") { strSql = strSql + " " + OrderBy; } dt = DBClass.GetTableByQuery(strSql); return(dt); }