public List <PurchaseReport> GetPurchaseReport(DateTime startDate, DateTime endDate)
{
Connect();
query = "SELECT distinct( Product), P.Code, P.Category, (COALESCE((SELECT SUM(QtyIn) FROM StockIn WHERE PurchaseDate BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND Name = PurchaseView.Product), 0) - COALESCE((SELECT SUM(QtyOut) FROM StockOut WHERE SalesDate BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND Name = PurchaseView.Product),0) ) as Unsold, CostPrice = (COALESCE((SELECT AVG(UnitPrice) FROM PurchaseView AS P WHERE Date BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND P.Product = PurchaseView.Product Group by Product), 0)), MRP = (COALESCE((SELECT AVG(MRP) FROM PurchaseView AS P WHERE Date BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND P.Product = PurchaseView.Product Group by Product), 0)), (COALESCE((SELECT AVG(MRP) FROM PurchaseView AS P WHERE Date BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND P.Product = PurchaseView.Product Group by Product), 0) - (COALESCE((SELECT AVG(UnitPrice) FROM PurchaseView AS P WHERE Date BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND P.Product = PurchaseView.Product Group by Product), 0)) ) *(SELECT (COALESCE((SELECT SUM(QtyIn) FROM StockIn WHERE PurchaseDate BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND Name = PurchaseView.Product), 0) - COALESCE((SELECT SUM(QtyOut) FROM StockOut WHERE SalesDate BETWEEN ' " + startDate + "' AND ' " + endDate + "' AND Name = PurchaseView.Product), 0)) as Unsold) AS Profit FROM PurchaseView, ProductsView AS P WHERE P.Name = PurchaseView.Product group by Product, P.Code, P.Category";
_sqlCommand = new SqlCommand {
CommandText = query, Connection = DatabaseConnection.sqlConnection
};
_sqlDataReader = _sqlCommand.ExecuteReader();
List <PurchaseReport> purchaseReports = new List <PurchaseReport>();
while (_sqlDataReader.Read())
{
if (Convert.ToInt32(_sqlDataReader["Unsold"]) == 0)
{
continue;
}
else
{
PurchaseReport aPurchaseReport = new PurchaseReport
{
Category = _sqlDataReader["Category"].ToString(),
ProductCode = _sqlDataReader["Code"].ToString(),
ProductName = _sqlDataReader["Product"].ToString(),
AvailableQuantity = Convert.ToInt32(_sqlDataReader["Unsold"]),
CostPrice = Convert.ToDouble(_sqlDataReader["CostPrice"]),
MRP = Convert.ToDouble(_sqlDataReader["MRP"]),
Profit = Convert.ToDouble(_sqlDataReader["Profit"])
};
purchaseReports.Add(aPurchaseReport);
}
}
DatabaseConnection.sqlConnection.Close();
return(purchaseReports);
}
/// <summary>
/// Creates a new instance of the PurchaseReport class from a message body.
/// </summary>
/// <param name="message">The body of a message containing the parameters of a purchase report.</param>
public PurchaseReport(String message)
{
// Parse the fields out of the message.
this.Address = PurchaseReport.GetField("Address: ", message);
this.City = PurchaseReport.GetField("City: ", message);
this.Country = PurchaseReport.GetField("Country: ", message);
this.Email = PurchaseReport.GetField("Email: ", message);
this.FirstName = PurchaseReport.GetField("First Name: ", message);
this.LastName = PurchaseReport.GetField("Last Name: ", message);
this.Phone = PurchaseReport.GetField("Phone: ", message);
this.PostalCode = PurchaseReport.GetField("Postal Code: ", message);
this.State = PurchaseReport.GetField("State: ", message);
// Create a temporary directory where we can place the license file when it is generated.
DirectoryInfo directoryInfo = Directory.CreateDirectory(Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString()));
this.FileName = Path.Combine(directoryInfo.FullName, AddIn.licenseFileName);
// Parse the CPU type out of the product description.
this.CpuType = String.Empty;
if (message.IndexOf("Explorer Chrome Suite (x86)") != -1)
{
this.CpuType = "x86";
}
if (message.IndexOf("Explorer Chrome Suite (x64)") != -1)
{
this.CpuType = "x64";
}
}
/// <summary>
/// Handles a new item added to the folder.
/// </summary>
/// <param name="item">The item added to the folder.</param>
void OnItemAdded(Object item)
{
// Write about the progress.
AddIn.WriteEventLogInformation("OnItemAdded called");
// Extract the specific argument from the generic event argument.
MailItem mailItem = item as MailItem;
// Only handle the item if it is a purchase report from sales at Teraque.
if (mailItem != null && mailItem.Subject == AddIn.purchaseReportSubject && mailItem.SenderEmailAddress == AddIn.purchaseReportSender)
{
try
{
AddIn.WriteEventLogInformation("Generating purchase report.");
// This will parse out the fields from the body of the message.
PurchaseReport purchaseReport = new PurchaseReport(mailItem.Body);
// A new license file is required for the new purchase. This will call out to the license generator to create one using all the parameters
// extracted from the purchase report message. Note that the process is handled asynchronously and that when the license generator is
// finished a handler will be called to complete the processing of the receipt.
Process process = new Process();
process.EnableRaisingEvents = true;
#if DEBUG
process.StartInfo.FileName = @"C:\Users\Donald Roy Airey\Documents\Visual Studio 2010\Projects\Teraque\Main\License Generator\License Generator\bin\Debug\Teraque.LicenseGenerator.exe";
#else
process.StartInfo.FileName = @"C:\Program Files\Teraque\License Generator\Teraque.LicenseGenerator.exe";
#endif
process.StartInfo.Arguments += String.Format(" -Address \"{0}\"", purchaseReport.Address);
process.StartInfo.Arguments += String.Format(" -City \"{0}\"", purchaseReport.City);
process.StartInfo.Arguments += String.Format(" -Command \"{0}\"", "GenerateLicense");
process.StartInfo.Arguments += String.Format(" -Country \"{0}\"", purchaseReport.Country);
process.StartInfo.Arguments += String.Format(" -Email \"{0}\"", purchaseReport.Email);
process.StartInfo.Arguments += String.Format(" -FirstName \"{0}\"", purchaseReport.FirstName);
process.StartInfo.Arguments += String.Format(" -LastName \"{0}\"", purchaseReport.LastName);
process.StartInfo.Arguments += String.Format(" -Output \"{0}\"", purchaseReport.FileName);
process.StartInfo.Arguments += String.Format(" -Phone \"{0}\"", purchaseReport.Phone);
process.StartInfo.Arguments += String.Format(" -PostalCode \"{0}\"", purchaseReport.PostalCode);
process.StartInfo.Arguments += String.Format(" -Product \"{0}\"", "{9FD4FF74-CCAC-463E-922E-79613F6A7CD9}");
process.StartInfo.Arguments += String.Format(" -Province \"{0}\"", purchaseReport.State);
process.Start();
this.purchaseReports.Add(process.Id, purchaseReport);
process.Exited += new EventHandler(this.OnLicenseGeneratorExited);
AddIn.WriteEventLogInformation("Waiting for License Generation.");
}
catch (System.Exception exception)
{
// Make sure any errors trying to handle the incoming message are logged.
AddIn.WriteEventLogError(exception.Message);
}
}
else
{
AddIn.WriteEventLogInformation("Got an item but it wasn't a PurchaseReport.");
}
}
/// <summary>
/// Handles the completion of a license generation.
/// </summary>
/// <param name="sender">The object that originated the event.</param>
/// <param name="eventArgs">The event arguments</param>
void OnLicenseGeneratorExited(Object sender, EventArgs eventArgs)
{
// Extract the process from the generic event arguments.
Process process = sender as Process;
// If the license was generated properly then construct a receipt.
if (process.ExitCode == 0)
{
// This is the information associated with the recently created license file.
PurchaseReport purchaseReport = this.purchaseReports[process.Id];
// Create the receipt in HTML and fill in the header.
MailItem receipt = this.Application.CreateItem(OlItemType.olMailItem) as MailItem;
receipt.BodyFormat = OlBodyFormat.olFormatHTML;
receipt.Subject = AddIn.receiptSubject;
receipt.To = purchaseReport.Email;
receipt.SendUsingAccount = this.GetAccountForEmailAddress(AddIn.sendFromAddress);
receipt.Attachments.Add(purchaseReport.FileName);
// This will load the template of the receipt from the assembly resources.
String messageBody = String.Empty;
using (Stream stream = typeof(AddIn).Assembly.GetManifestResourceStream("Receipt_Generator_AddIn.Resources.ReceiptTemplate.htm"))
{
StreamReader streamReader = new StreamReader(stream);
messageBody = streamReader.ReadToEnd();
}
// This will replace the first name in the salutation (or with "Customer" if they want to remain anonymous).
messageBody = messageBody.Replace("%FirstName%", purchaseReport.FirstName == String.Empty ? "Customer" : purchaseReport.FirstName);
// This will replace the hyperlink in the message body with the proper URL depending on the CPU type ordered.
if (purchaseReport.CpuType == "x86")
{
messageBody = messageBody.Replace("%CpuDownload%", AddIn.x86DownloadUrl);
}
if (purchaseReport.CpuType == "x64")
{
messageBody = messageBody.Replace("%CpuDownload%", AddIn.x64DownloadUrl);
}
// The body of the receipt is now complete.
receipt.HTMLBody = messageBody;
// Everything is now ready and the receipt can be sent.
((_MailItem)receipt).Send();
// Log the fact that we sent the receipt.
AddIn.WriteEventLogInformation("Sending a message to {0}", purchaseReport.Email);
}
else
{
// Write out the reason the generator failed.
AddIn.WriteEventLogError("License Generator failed with error code {0}", process.ExitCode);
}
}
public HttpResponseMessage postall(PurchaseReport purchaseReport)
{
try
{
DataTable dt = objPurchaseReport.postAllData(purchaseReport);
return(Request.CreateResponse(HttpStatusCode.OK, dt));
}
catch (Exception ex)
{
return(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
public void approveRequest(int itemId, int itemPrice, int itemQuantity)
{
var itemName = (from x in db.PurchaseRequest where x.ItemId == itemId select x.ItemName).FirstOrDefault();
PurchaseReport pr = new PurchaseReport();
pr.PurchaseItem = itemName;
pr.PurchaseTotal = itemPrice;
pr.Deleted = 0;
db.PurchaseReport.Add(pr);
db.SaveChanges();
var obj = (from x in db.PurchaseRequest where x.ItemId == itemId select x).FirstOrDefault();
obj.Deleted = 2;
db.SaveChanges();
}
/// <summary>
/// Демо паттерна "Декоратор".
/// Паттерн Декоратор динамически наделяет объект новыми возможностями и является гибкой
/// альтернативой субклассированию в области расширения функциональности.
/// </summary>
public void DemoDecorator()
{
// Здесь происходит расширение класса FinancialReport дополнительной функциональностью в виде FinancialDailyReport
var dailyReport = new FinancialReport(new FinancialDailyReport());
dailyReport.ApplyStyles();
// Здесь происходит расширение класса FinancialReport дополнительной функциональностью в виде FinancialDailyReportWithCharts
var dailyReportWithCharts = new FinancialReport(new FinancialDailyReportWithCharts());
dailyReportWithCharts.ApplyStyles();
// Здесь происходит декорирование объекта FinancialDailyReport функциональностью из FinancialReport и PurchaseReport
IReport newDailyReport = new FinancialDailyReport();
newDailyReport = new FinancialReport(newDailyReport);
newDailyReport = new PurchaseReport(newDailyReport);
newDailyReport.ApplyStyles();
}
private void btn_PurchaseReport_Click(object sender, EventArgs e)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string s = "Select * FROM purchase_master";
SqlCommand cmd = new SqlCommand(s, conn);
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds, "purchase_master");
PurchaseReport cr1 = new PurchaseReport();
cr1.SetDataSource(ds);
CrisReport.ReportSource = cr1;
conn.Close();
CrisReport.Refresh();
}
public List <PurchaseReport> GetPurchaseReport(DateTime startDate, DateTime endDate)
{
List <PurchaseReport> purchaseReportViewModels = new List <PurchaseReport>();
using (var context = new SMSDbContext())
{
var allProduct = context.Products.Include(x => x.Category).ToList();
foreach (var product in allProduct)
{
int productId = product.Id;
int purchaseQty = _purchaseRepository.GetTotalProductByIdAndDate(productId, startDate);
int salesQty = _saledetailsRepository.GetTotalProductByIdAndDate(productId, startDate);
int inQty = _purchaseRepository.GetTotalProductByIdAndStartAndEndDate(productId, startDate, endDate);
int outQty = _saledetailsRepository.GetTotalProductByIdAndStartAndEndDate(productId, startDate, endDate);
PurchaseReport model = new PurchaseReport();
model.Code = product.Code;
model.Product = product.Name;
model.Category = product.Category.Name;
model.AvailableQty = (purchaseQty - salesQty) + inQty - outQty;
var pDetails = _purchaseDetailsRepository.GetPurchaseDetailByProductId(productId);
if (pDetails != null)
{
model.MRP = model.AvailableQty * pDetails.MRP;
model.CP = model.AvailableQty * pDetails.UnitPrice;
}
model.Profit = model.MRP - model.CP;
purchaseReportViewModels.Add(model);
}
}
return(purchaseReportViewModels);
}
private void purchaseDetailsToolStripMenuItem1_Click(object sender, EventArgs e)
{
PurchaseReport pur = new PurchaseReport();
pur.Show();
}
private void btnReportPurchase_Click(object sender, EventArgs e)
{
PurchaseReport PurReport = new PurchaseReport();
PurReport.ShowDialog();
}
private void PurchaseReport_Click(object sender, RoutedEventArgs e)
{
PurchaseReport purchaseReport = new PurchaseReport();
purchaseReport.ShowDialog();
}
private void button3_Click(object sender, EventArgs e)
{
PurchaseReport frmPur = new PurchaseReport();
frmPur.ShowDialog();
}
private void Init()
{
IsReportLoaded = true;
report = new PurchaseReport();
}
