public void PrintSummaryReport()
{
Company oCompany = new Company();
oCompany.CalculateAllCustomerTotalsByBrochure();
frmViewReport oViewReport = new frmViewReport();
BrochureSummaryReport oRpt = new BrochureSummaryReport();
DataSet ds = new DataSet();
ds.Tables.Add(oMySql.GetDataTable("Select bc.CustomerID,b.Description,bc.BrochureID,sum(c.Signed) as Signed, sum(bc.Retail) as Retail,bc.Code,bc.ProfitPercent, Sum(bc.NoItems) as NoItems, Sum(bc.NoSellers) as NoSellers, count(bc.CustomerID) as Customers FROM BrochureByCustomer bc Left Join Customer c On bc.CustomerID=c.CustomerID And bc.CompanyID=c.CompanyID Left Join Brochure b On bc.BrochureID=b.BrochureID And bc.CompanyID=b.CompanyID Where bc.CompanyID='" + CompanyID + "' And b.Description is not null And bc.Retail >0 And Upper(Name) not like '%INTERNET%' Group By bc.BrochureID Order by bc.BrochureID", "Summary"));
ds.Tables.Add(oMySql.GetDataTable("Select bc.CustomerID,b.Description,bc.BrochureID,sum(c.Signed) as Signed, sum(bc.Retail) as Retail,bc.Code,bc.ProfitPercent, Sum(bc.NoItems) as NoItems, Sum(bc.NoSellers) as NoSellers, count(bc.CustomerID) as Customers FROM BrochureByCustomer bc Left Join Customer c On bc.CustomerID=c.CustomerID And bc.CompanyID=c.CompanyID Left Join Brochure b On bc.BrochureID=b.BrochureID And bc.CompanyID=b.CompanyID Where bc.CompanyID='" + CompanyID + "' And b.Description is not null And Upper(Name) not like '%INTERNET%' Group By bc.BrochureID Order by bc.BrochureID", "SummaryTotal"));
//Console.Write("Select bc.CustomerID,b.Description,bc.BrochureID,sum(c.Signed) as Signed, sum(bc.Retail) as Retail,bc.Code,bc.ProfitPercent, Sum(bc.NoItems) as NoItems, Sum(bc.NoSellers) as NoSellers FROM BrochureByCustomer bc Left Join Customer c On bc.CustomerID=c.CustomerID And bc.CompanyID=c.CompanyID Left Join Brochure b On bc.BrochureID=b.BrochureID And bc.CompanyID=b.CompanyID Where bc.CompanyID='" + CompanyID + "' And b.Description is not null Group By bc.BrochureID Order by bc.BrochureID");
//ds.Tables.Add(oMySql.GetDataTable("Select * From Customer Where CompanyID='" + CompanyID + "' And CustomerID='" + ID + "'", "Customer"));
ds.Tables.Add(oMySql.GetDataTable("Select * From Brochure Where CompanyID='" + CompanyID + "'", "Brochure"));
// ds.WriteXml("PrintSummaryReport", XmlWriteMode.WriteSchema);
oRpt.SetDataSource(ds);
oViewReport.cReport.ReportSource = oRpt;
oViewReport.ShowDialog();
/*
* oViewReport.sParameter_1 = ID;
* oViewReport.SetReport((int)Report.PurchaseOrder, CompanyID, "", true);
*/
ds.Dispose();
oRpt.Dispose();
oViewReport.Dispose();
}
public void PrintGACommissionReport()
{
// DataSet ds = Global.oMySql.GetDataset(String.Format("CALL GARepCommissions('{0}');", this.CompanyID));
DataSet ds = new DataSet();
DataTable table = Global.oMySql.GetDataTable(String.Format(@"Select c.RepID, r.Name as RepName, c.CustomerID, c.Name, c.SalesTax, sum(bc.Amount)*-1 aS TotalPayment, sum(bc.Amount)*-1*(1-c.SalesTax/100) as TotalNoTax,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)<=1000,0,if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=1001 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<25000 ,10,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=25000 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<75000 ,15,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=75000 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<125000 ,17,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=12500 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<250000 ,20,22
))))) as CommPercent,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)<=1000,0,if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=1001 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<25000 ,10,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=25000 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<75000 ,15,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=75000 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<125000 ,17,
if(sum(bc.Amount)*-1*(1-c.SalesTax/100)>=12500 And sum(bc.Amount)*-1*(1-c.SalesTax/100)<250000 ,20,22
)))))/100*sum(bc.Amount)*-1*(1-c.SalesTax/100) as Commission
from Customer c
Left Join Reps r On c.Rep_ID=r.ID
Left Join Payment bc On c.CompanyID=bc.CompanyID And c.CustomerID=bc.CustomerID Where c.CompanyID='{0}' And bc.Type='P' Group By c.RepID, c.CustomerID", this.CompanyID), "Commissions");
ds.Tables.Add(table);
frmViewReport oViewReport = new frmViewReport();
ds.WriteXml("GARepCommissionsReport.xml", XmlWriteMode.WriteSchema);
GARepCommissions oRpt = new GARepCommissions();
oRpt.SetDataSource(ds);
oRpt.SetParameterValue("CompanyName", "Signature Fundraising, Inc.");
oViewReport.cReport.ReportSource = oRpt;
oViewReport.ShowDialog();
ds.Dispose();
oRpt.Dispose();
oViewReport.Dispose();
}
public void Print(Boolean LocalPrint)
{
DataSet ds = new DataSet();
ds.Tables.Add(oMySql.GetDataTable(String.Format("Select *,'Text' From Shortage Where ShortageID='{0}'", ID), "Shortage"));
Product oProduct = new Product(this.CompanyID);
String SchoolName = "";
foreach (DataRow row in ds.Tables["Shortage"].Rows)
{
row["Text"] = Global.ByteToString((Byte[])(row["Detail"]));
row["Address"] = row["Address"].ToString() + row["Address_2"].ToString();
foreach (DataRow drow in Items.Detail.Rows)
{
oProduct.Find(drow["ProductID"].ToString());
row["Text"] += "\n" + oProduct.InvCode + " - " + drow["ProductID"].ToString() + " - " + drow["Description"].ToString() + " - " + drow["Quantity"].ToString();
}
if (row["CustomerID"].ToString().Trim() != "")
{
if (oCustomer.Find(row["CustomerID"].ToString()))
{
SchoolName = oCustomer.Name;
}
}
}
frmViewReport oViewReport = new frmViewReport();
//ds.WriteXml("dataset72.xml", XmlWriteMode.WriteSchema);
ShortageReport oRpt = new ShortageReport();
oRpt.SetDataSource(ds);
oRpt.SetParameterValue("CompanyName", "Signature Fundraising, Inc.");
oRpt.SetParameterValue("SchoolName", SchoolName);
oRpt.SetParameterValue("User", Global.CurrentUser);
oRpt.SetParameterValue("Dates", "");
oRpt.SetParameterValue("Chairperson", "");
oRpt.SetParameterValue("StudentName", "");
String sType = "Not specified";
switch (Type)
{
case "A":
sType = "Add";
break;
case "O":
sType = "0verage";
break;
case "X":
sType = "Delete";
break;
case "M":
sType = "Miscellaneous";
break;
case "R":
sType = "Refund";
break;
case "D":
sType = "Damaged";
break;
case "B":
sType = "Missing";
break;
case "I":
sType = "Internet";
break;
case "E":
sType = "Missing Entire Order";
break;
case "Y":
sType = "Discrepancy";
break;
}
oRpt.SetParameterValue("Type", sType);
oRpt.SetParameterValue("BarCode", (sType != "Add")? "*" + ID + "*":"");
if (OrderID.Trim() != "")
{
if (oCustomer.ShipDate != Global.DNull || oCustomer.DeliveryDate != Global.DNull)
{
String ShipDate = oCustomer.ShipDate == Global.DNull ? " " : oCustomer.ShipDate.ToString("MM/dd/yyyy");
String DeliveryDate = oCustomer.DeliveryDate == Global.DNull ? " " : oCustomer.DeliveryDate.ToString("MM/dd/yyyy");
oRpt.SetParameterValue("Dates", "Ship Date: " + ShipDate + " " + "Delivery Date:" + DeliveryDate);
}
if (oCustomer.Chairperson.Trim() != "")
{
oRpt.SetParameterValue("Chairperson", "Chairperson:" + oCustomer.Chairperson);
}
oOrder = new Order(CompanyID);
oOrder.Find(Convert.ToInt32(OrderID));
oRpt.SetParameterValue("StudentName", "Student: " + oOrder.Student);
}
if (Global.CurrentUser == "JOYCE" || Global.CurrentUser == "ALVARO" || LocalPrint || Type == "R")
{
oViewReport.cReport.ReportSource = oRpt;
oViewReport.cReport.PrintReport();
//oViewReport.ShowDialog();
}
else
{
oRpt.PrintOptions.PrinterName = "\\\\srv1\\Shortage";
oRpt.PrintToPrinter(1, false, 1, 100);
}
ds.Dispose();
oRpt.Dispose();
oViewReport.Dispose();
}
public Boolean PrintStatement(String PrinterName, PrinterDevice Device)
{
if (_Total == 0)
{
Total = GetTotal();
Update();
}
frmViewReport oViewReport = new frmViewReport();
//DataSet ds1 = oMySql.GetCustomerStatement(CompanyID, ID);
DataSet ds = new DataSet();
ds.Tables.Add(oMySql.GetDataTable(String.Format("Select * From Vendor Where CompanyID='{0}' And VendorID='{1}'", CompanyID, this.VendID), "Vendor"));
ds.Tables.Add(oMySql.GetDataTable(String.Format("Select * From Purchase Where CompanyID='{0}' And PurchaseID='{1}'", CompanyID, this.ID), "Purchase"));
ds.Tables.Add(oMySql.GetDataTable(String.Format("Select * From PaymentProvider Where CompanyID='{0}' And PurchaseID='{1}' Order by Date", CompanyID, ID), "Statement"));
PurchaseStatement oRpt = new PurchaseStatement();
// ds.WriteXml("PrintStatement1.xml", XmlWriteMode.WriteSchema);
oRpt.SetDataSource(ds);
oRpt.SetParameterValue("CompanyName", base.Name);
if (Device == PrinterDevice.Printer)
{
oRpt.PrintOptions.PrinterName = PrinterName;
oRpt.PrintToPrinter(1, true, 1, 100);
}
else if (Device == PrinterDevice.eMail)
{
PDF oPDF = new PDF();
oPDF.FileName = Application.StartupPath + "\\" + this.ID + ".pdf";
oPDF.ExportReport(oRpt, "pdf", Application.StartupPath + "\\", this.ID);
Smtp oSmtp = new Smtp();
oSmtp.Subject = base.ID + " - Statement " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "(" + this.ID + " - " + this.Name + ")";
if (PrinterName != "")
{
oSmtp.To = "\"" + this.Name + "\" <" + PrinterName + ">"; //this.eMail + ">";
}
/*
* else if (this.isEmail(this.eMail) && File.Exists(oPDF.FileName))
* {
*
* oSmtp.To = "\"" + this.Chairperson + "\" <" + this.eMail + ">";
* if (this.isEmail(this.oCustomerExtra.eMail))
* {
* oSmtp.To = "\"" + this.Chairperson + "\" <" + this.oCustomerExtra.eMail + ">";
* }
*
* oSmtp.To = "\"" + "Scott Elsbree" + "\" <" + "*****@*****.**" + ">"; //this.eMail + ">";
* }
*/
else
{
oSmtp.To = "\"" + "Scott Elsbree" + "\" <" + "*****@*****.**" + ">"; //this.eMail + ">";
}
oSmtp.From = "\"Signature Fundraising Customer Service\" <*****@*****.**>";
String strTitle = "\n\r";
strTitle += "Thank you for choosing Signature Fundraising. As of today we have not received complete payment for your account.\n\r";
strTitle += "We have attached a copy of your most recent statement showing the balance due. Please remember that according to\n\r";
strTitle += "the agreement we have with your organization interest will accrue on any unpaid balance after 20 days of delivery.\n\r";
strTitle += "We have also attached a check by fax form that will enable you to send payment to us right away. If you have any \n\r";
strTitle += "questions, you may reply to this e-mail or call us at 1-800-645-3863.\n\r";
strTitle += "\n\nThank you.\n\r";
strTitle += "Signature Fundraising\n\r";
//String strTitle = "This statement amount due is for a total of :\n\r $ " + this.StatementAmountDue.ToString() + " \n\r" ;
//This invoice is for a total of ::invoice amount::, of which ::payment amount:: has already been received.
/*
* if (PrinterName == "" && !this.isEmail(this.eMail))
* strTitle += " WRONG EMAIL ADDRESS: " + this.eMail + " of " + this.ID + " : " + this.Name;
*
* if (PrinterName == "" && !File.Exists(oPDF.FileName))
* strTitle += " NO PDF FILE : " + this.eMail + " of " + this.ID + " : " + this.Name;
* else
* oSmtp.Attachment = oPDF.FileName;
*/
oSmtp.Body = strTitle;
oSmtp.Attachment = "Check by Fax Form.pdf";
oSmtp.Credentials = new System.Net.NetworkCredential("*****@*****.**", "sigfund");
// oSmtp.BCC = "*****@*****.**";
if (!oSmtp.Send())
{
Console.WriteLine(oSmtp.Error);
oRpt.Dispose();
oViewReport.Dispose();
return(false);
}
//while (File.GetAttributes(oPDF.FileName) == FileAttributes.ReadOnly) ;
/*
* ReadFile:
* try
* {
* if (File.Exists(oPDF.FileName))
* File.Delete(oPDF.FileName);
* }
* catch (IOException ex)
* {
* Console.WriteLine(ex.Message);
* goto ReadFile;
* }
*/
}
else
{
oViewReport.cReport.ReportSource = oRpt;
oViewReport.ShowDialog();
}
oRpt.Dispose();
oViewReport.Dispose();
return(true);
}